Upload Produk Stok

CREATE OR REPLACE FUNCTION f_upload_product_stock(character varying, bigint, character varying, character varying, character varying)
    RETURNS bigint AS
    $BODY$
DECLARE

  pSessionId            ALIAS FOR $1;
  pRecordOwnerId        ALIAS FOR $2;
  pDocDate              ALIAS FOR $3;
  pUsername             ALIAS FOR $4;
  pDatetime             ALIAS FOR $5;

  vEmptyId              bigint := -99;
  vProductId            bigint;
  vUomId                bigint;
  vFindProductId        bigint;
  vResult               bigint;

  vFail                 character varying := 'FAIL';
  vOk                   character varying := 'OK';

  vYes                  character varying := 'Y';
  vNo                   character varying := 'N';
  vCurrentDateTime      character varying;
  vEmpty                character varying := ' ';
  vInventoryId          bigint;
  vWarehouseId          bigint;
  vDocumentNo           character varying;
  vPrefixDocNo          character varying := 'ADJ';
  vDocTypeId            bigint := 521;
  vReleased             character varying := 'R';
  vProductStatusGood    character varying := 'GOOD';
  vModeActionAdd        character varying := 'A';
  vModeActionUpdate     character varying := 'U';

BEGIN

    SELECT warehouse_id INTO vWarehouseId FROM m_warehouse WHERE record_owner_id = pRecordOwnerId;

    -- Validasi produk tidak boleh duplikate saat upload
    WITH duplicateCode AS (
        SELECT product_code, COUNT(product_code)
        FROM tt_ul_product_stock
        WHERE session_id = pSessionId
            AND record_owner_id = pRecordOwnerId
        GROUP BY product_code
        HAVING COUNT(product_code) > 1
    )
    UPDATE tt_ul_product_stock Z
        SET status = vFail, message = concat(Z.message, 'Kode Produk ', A.product_code, ' Duplikat, ')
    FROM tt_ul_product_stock A
    INNER JOIN duplicateCode B ON A.product_code = B.product_code
    WHERE A.session_id = pSessionId
        AND Z.product_code = A.product_code
        AND Z.record_owner_id = pRecordOwnerId;

    UPDATE tt_ul_product_stock Z
        SET status = vFail, message = concat(Z.message, 'Kode Produk ', A.product_code, ' Tidak terdaftar, ')
    FROM tt_ul_product_stock A
    LEFT JOIN m_product B ON A.product_code = B.product_code
        AND A.record_owner_id = B.record_owner_id
    WHERE A.session_id = pSessionId
        AND Z.product_code = A.product_code
        AND B.product_id IS NULL;

    SELECT COUNT(1) INTO vResult
    FROM tt_ul_product_stock
    WHERE session_id = pSessionId
        AND status = vFail;

    IF vResult = 0 THEN

        SELECT nextval('in_inventory_seq') INTO vInventoryId;
        SELECT f_gen_autonum(concat(vPrefixDocNo,'/',substring(pDocDate, 3, 4),'/'), vPrefixDocNo) INTO vDocumentNo;

        INSERT INTO tt_in_inventory (
            session_id, inventory_id, record_owner_id, doc_type_id, doc_no,
            doc_date, ext_doc_date, ext_doc_no, remark, warehouse_id,
            status_doc, version, create_datetime, create_username, update_datetime,
            update_username
        ) VALUES (
            pSessionId, vInventoryId, pRecordOwnerId, vDocTypeId, vDocumentNo,
            pDocDate, vEmpty, vEmpty, 'Penyesuaian Stok Dari Upload', vWarehouseId,
            vReleased, 0 , pDatetime, pUsername, pDatetime,
            pUsername
        );

        -- Insert Produk yg terdaftar di in_product_balance
        INSERT INTO tt_in_inventory_item(
            inventory_id, product_id, product_balance_id, qty, version,
            create_datetime, create_username, update_datetime, update_username
        )
        SELECT vInventoryId, B.product_id, C.product_balance_id, qty, 0,
            A.create_datetime, A.create_username, A.update_datetime, A.update_username
        FROM tt_ul_product_stock A
        INNER JOIN m_product B ON A.product_code = B.product_code
            AND A.record_owner_id = B.record_owner_id
        INNER JOIN in_product_balance C ON B.product_id = C.product_id
        WHERE A.session_id = pSessionId;

        -- Insert Produk yg tidak terdaftar di in_product_balance
        INSERT INTO tt_in_inventory_item(
            inventory_id, product_id, product_balance_id, qty, version,
            create_datetime, create_username, update_datetime, update_username
        )
        SELECT vInventoryId, B.product_id, vEmptyId, qty, 0,
            A.create_datetime, A.create_username, A.update_datetime, A.update_username
        FROM tt_ul_product_stock A
        INNER JOIN m_product B ON A.product_code = B.product_code
            AND A.record_owner_id = B.record_owner_id
        WHERE A.session_id = pSessionId
            AND NOT EXISTS (SELECT 1 FROM in_product_balance Z WHERE B.product_id = Z.product_id);

        INSERT INTO in_inventory (
            inventory_id, record_owner_id, doc_type_id, doc_no,
            doc_date, ext_doc_date, ext_doc_no, remark, warehouse_id,
            status_doc, version, create_datetime, create_username, update_datetime,
            update_username
        )
        SELECT
            inventory_id, record_owner_id, doc_type_id, doc_no,
            doc_date, ext_doc_date, ext_doc_no, remark, warehouse_id,
            status_doc, version, create_datetime, create_username, update_datetime,
            update_username
        FROM tt_in_inventory
        WHERE session_id = pSessionId;

        INSERT INTO in_inventory_item(
            inventory_item_id, inventory_id, product_id, product_balance_id, qty, version,
            create_datetime, create_username, update_datetime, update_username
        )
        SELECT
            inventory_item_id, inventory_id, product_id, product_balance_id, qty, version,
            create_datetime, create_username, update_datetime, update_username
        FROM tt_in_inventory_item
        WHERE inventory_id = vInventoryId;

        -- Insert ke table in_product_balance dengan product_balance_id = -99 dari in_inventory_item
        INSERT INTO in_product_balance(
            record_owner_id, product_id, serial_number, lot_number, expired_date, year_made,
            version, create_datetime, create_username, update_datetime, update_username)
        SELECT pRecordOwnerId, product_id, vEmpty, vEmpty, vEmpty, vEmpty,
            0, pDatetime, pUsername, pDatetime, pUsername
        FROM in_inventory_item
        WHERE inventory_id = vInventoryId
            AND product_balance_id = vEmptyId;

        INSERT INTO tt_product_balance(
            session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
            product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
        SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.inventory_id, B.inventory_item_id, A.doc_no, A.doc_date, C.product_balance_id,
            -99, B.product_id, B.qty, E.warehouse_id, vEmpty, vEmpty, vEmpty, vEmpty, vProductStatusGood, vModeActionAdd
        FROM in_inventory A
        INNER JOIN in_inventory_item B ON B.inventory_id = A.inventory_id
        INNER JOIN in_product_balance C ON C.product_id = B.product_id
            AND C.record_owner_id = pRecordOwnerId
        INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
        WHERE A.inventory_id = vInventoryId
        AND NOT EXISTS (
            SELECT 1
            FROM in_product_balance_stock F
            WHERE F.record_owner_id = A.record_owner_id AND
                F.product_balance_id = C.product_balance_id AND
                F.product_id = C.product_id AND
                F.warehouse_id = A.warehouse_id);

        INSERT INTO tt_product_balance(
            session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
            product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
        SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.inventory_id, B.inventory_item_id, A.doc_no, A.doc_date, C.product_balance_id,
            D.product_balance_stock_id, B.product_id, B.qty, E.warehouse_id, vEmpty, vEmpty, vEmpty, vEmpty, vProductStatusGood, vModeActionUpdate
        FROM in_inventory A
        INNER JOIN in_inventory_item B ON B.inventory_id = A.inventory_id
        INNER JOIN in_product_balance C ON C.product_id = B.product_id
        INNER JOIN in_product_balance_stock D ON D.product_balance_id = C.product_balance_id
        INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
        WHERE A.inventory_id = vInventoryId
            AND A.record_owner_id = D.record_owner_id;

        -- insert ke table in_product_balance_stock
        INSERT INTO in_product_balance_stock(
            record_owner_id, product_balance_id, product_id, warehouse_id, product_status, qty,
            version, create_datetime, create_username, update_datetime, update_username)
        SELECT record_owner_id, product_balance_id, product_id, warehouse_id, vProductStatusGood, qty,
            0, pDatetime, pUsername, pDatetime, pUsername
        FROM tt_product_balance
        WHERE session_id = pSessionId
            AND mode_action = vModeActionAdd;

        -- update ke table in_product_balance_stock
        UPDATE in_product_balance_stock A SET qty = A.qty + B.qty , version = version+1, update_datetime = pDateTime, update_username = pUsername
        FROM tt_product_balance B
        WHERE B.session_id = pSessionId
            AND A.product_balance_id = B.product_balance_id
            AND A.product_id = B.product_id
            AND B.mode_action = vModeActionUpdate;

        -- insert log
        INSERT INTO in_log_product_balance_stock(
            record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_id,
            ref_doc_no, ref_doc_date, qty, version, create_datetime, create_username, update_datetime, update_username)
        SELECT record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_doc_id,
            ref_doc_no, ref_doc_date, qty, 0,  pDateTime, pUsername, pDateTime, pUsername
        FROM tt_product_balance
        WHERE session_id = pSessionId;

        -- delete table temp
        DELETE FROM tt_product_balance WHERE session_id = pSessionId;

    END IF;

    return vResult;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, batch
  COST 100;
  /