r_report_pemenuhan_stok_LIVE_20180509

-- Function: r_report_pemenuhan_stok(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)

-- DROP FUNCTION r_report_pemenuhan_stok(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION r_report_pemenuhan_stok(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';

    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pOuId                   ALIAS FOR $3;
    pCtgrProductId          ALIAS FOR $4;
    pSubCtgrProductId       ALIAS FOR $5;
    pStyleProduct           ALIAS FOR $6;
    pProductCodeName        ALIAS FOR $7;
    pPeriodFrom             ALIAS FOR $8;
    pPeriodTo               ALIAS FOR $9;
    pRgtoNo                 ALIAS FOR $10;
    pRgtoRemark             ALIAS FOR $11;
    pPoNo                   ALIAS FOR $12; -- diisi string kosong karena di UI filter dihilangkan
    pPoRemark               ALIAS FOR $13; -- diisi string kosong karena di UI filter dihilangkan
    pGtoNo                  ALIAS FOR $14;
    pGtoRemark              ALIAS FOR $15;
    pGtiNo                  ALIAS FOR $16;
    pGtiRemark              ALIAS FOR $17;
    pGtirNo                 ALIAS FOR $18;
    pGtirRemark             ALIAS FOR $19;

    vFilterCtgrProduct      character varying= '';
    vFilterSubCtgrProduct   character varying= '';
    vFilterGolonganProduct  character varying= '';
    vFilterProductCodeName  character varying= '';
    vFilterRgtoNo   character varying= '';
    vFilterRgtoRemark   character varying= '';
    vFilterPoNo     character varying= '';
    vFilterPoRemark     character varying= '';
    vFilterPoNotExists      boolean;
    vFilterGto      character varying= '';
    vFilterGti      character varying= '';
    vFilterGtir     character varying= '';
    vFilterGtoForDataGto        character varying= '';
    vFilterGtiForDataGto        character varying= '';
    vFilterGtirForDataGto       character varying= '';

    vPeriodFrom         text;
    vPeriodYearMonth        text;
    vOuBuId             bigint;
    vStockOpnameActivityId      bigint;

    vEmptyId            bigint := -99;
    vEmptyString            text := '';
    vZero               bigint := 0;
    vFlgApproved            text := 'R';
    vFlgRejected            text := 'V';
    vFlgQtyMissing          text := 'M';
    vFlgQtyRejected         text := 'R';
    vFlgQtyCorrect          text := 'C';
    vFlgQtyLost         text := 'L';

    vDocTypeRgto        bigint := 538;
    vDocTypeCorrQtyRgto     bigint := 590;
    vDocTypePurchaseOrder       bigint := 101;
    vDocTypeReceiveGoods        bigint := 111;
    vDocTypeGoodsTransferOut    bigint := 533;
    vDocTypeGoodsTransferIn     bigint := 535;
    vDocTypeGoodsTransferInReceipt      bigint := 536;
    vYes                        character varying := 'Y';
    vEmptyAmount                numeric := 0;

BEGIN
    DELETE FROM tt_pemenuhan_stok WHERE session_id = pSessionId;

    SELECT (f_get_ou_bu_structure(pOuId)).ou_bu_id INTO vOuBuId;

    --A. filter terhadap ctgr product
    IF pCtgrProductId != vEmptyId THEN
        vFilterCtgrProduct = ' AND D.ctgr_product_id = '||pCtgrProductId ;
    END IF;

    --B. filter terhadap sub ctgr product
    IF pSubCtgrProductId != vEmptyId THEN
        vFilterSubCtgrProduct = ' AND D.sub_ctgr_product_id = '||pSubCtgrProductId;
    END IF;

    --C. filter terhadap golongan
    IF pStyleProduct != vEmptyString THEN
        vFilterGolonganProduct = ' AND E.style_product = '''||pStyleProduct||'''';
    END IF;

    --D. filter terhadap product code name
    IF pProductCodeName != vEmptyString THEN
        vFilterProductCodeName = ' AND (UPPER(D.product_code) LIKE UPPER('''||'%'||pProductCodeName||'%'||''') OR UPPER(D.product_name) LIKE UPPER('''||'%'||pProductCodeName||'%'||''')) ';
    END IF;

    --E. filter terhadap RGTO no dan remark
    IF pRgtoNo != vEmptyString THEN
        vFilterRgtoNo = ' AND (UPPER(B.doc_no) LIKE UPPER('''||'%'||pRgtoNo||'%'||''')) ';
    END IF;

    IF pRgtoRemark != vEmptyString THEN
        vFilterRgtoRemark = ' AND (UPPER(B.remark) LIKE UPPER('''||'%'||pRgtoRemark||'%'||''')) ';
    END IF;

--  --F. filter terhadap PO no dan remark
--  -- sementara dicomment karena filter PO di UI dihilangkan
--
--  IF pPoNo != vEmptyString THEN
--      vFilterPoNo = ' AND (UPPER(I.doc_no) LIKE UPPER('''||'%'||pPoNo||'%'||''')) ';
--      vFilterPoNotExists = false;
--  END IF;
--
--  IF pPoRemark != vEmptyString THEN
--      vFilterPoRemark = ' AND (UPPER(I.remark) LIKE UPPER('''||'%'||pPoRemark||'%'||''')) ';
--      vFilterPoNotExists = false;
--  END IF;

    --G. filter terhadap GTO no dan remark
    IF(pGtoNo != vEmptyString OR pGtoRemark != vEmptyString) THEN
        vFilterGto = ' AND EXISTS (
                    SELECT 1
                    FROM in_inventory BB
                    WHERE B.inventory_id = BB.ref_id
                    AND BB.doc_type_id = '|| vDocTypeGoodsTransferOut;
        IF(pGtoNo != vEmptyString) THEN
            vFilterGto = vFilterGto || ' AND (UPPER(BB.doc_no) LIKE UPPER('''||'%'||pGtoNo||'%'||''')) ';
        END IF;

        IF(pGtoRemark != vEmptyString) THEN
            vFilterGto = vFilterGto || ' AND (UPPER(BB.remark) LIKE UPPER('''||'%'||pGtoRemark||'%'||''')) ';
        END IF;

        vFilterGto = vFilterGto || ' ) ';

    END IF;

    --H. filter terhadap GTI no dan remark
    IF(pGtiNo != vEmptyString OR pGtiRemark != vEmptyString) THEN
        vFilterGti = ' AND EXISTS (
                    SELECT 1
                    FROM in_inventory BB
                    JOIN in_inventory GTI ON BB.inventory_id = GTI.ref_id
                    WHERE B.inventory_id = BB.ref_id
                        AND BB.doc_type_id = '|| vDocTypeGoodsTransferOut ||'
                        AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn;
        IF(pGtiNo != vEmptyString) THEN
            vFilterGti = vFilterGti || ' AND (UPPER(GTI.doc_no) LIKE UPPER('''||'%'||pGtiNo||'%'||''')) ';
        END IF;

        IF(pGtiRemark != vEmptyString) THEN
            vFilterGti = vFilterGti || ' AND (UPPER(GTI.remark) LIKE UPPER('''||'%'||pGtiRemark||'%'||''')) ';
        END IF;

        vFilterGti = vFilterGti || ' ) ';

    END IF;

    --I. filter terhadap GTIR no dan remark
    IF(pGtirNo != vEmptyString OR pGtirRemark != vEmptyString) THEN
        vFilterGtir = ' AND EXISTS (
                    SELECT 1
                    FROM in_inventory BB
                    JOIN in_inventory GTI ON BB.inventory_id = GTI.ref_id
                    JOIN in_inventory GTIR ON GTI.inventory_id = GTIR.ref_id
                    WHERE B.inventory_id = BB.ref_id
                        AND BB.doc_type_id = '|| vDocTypeGoodsTransferOut ||'
                        AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn ||'
                        AND GTIR.doc_type_id = '|| vDocTypeGoodsTransferInReceipt;
        IF(pGtirNo != vEmptyString) THEN
            vFilterGtir = vFilterGtir || ' AND (UPPER(GTIR.doc_no) LIKE UPPER('''||'%'||pGtirNo||'%'||''')) ';
        END IF;

        IF(pGtirRemark != vEmptyString) THEN
            vFilterGtir = vFilterGtir || ' AND (UPPER(GTIR.remark) LIKE UPPER('''||'%'||pGtirRemark||'%'||''')) ';
        END IF;

        vFilterGtir = vFilterGtir || ' ) ';

    END IF;

    RAISE NOTICE '%', vFilterGolonganProduct;
    RAISE NOTICE '%', vFilterProductCodeName;
    RAISE NOTICE '%', vFilterGto;
    RAISE NOTICE '%', pGtoNo;
    RAISE NOTICE '%', vFilterGti;
    RAISE NOTICE '%', pGtiNo;
    RAISE NOTICE '%', vFilterGtir;
    RAISE NOTICE '%', pGtirNo;
    RAISE NOTICE '%', vFilterPoNo;
    RAISE NOTICE '%', vFilterPoRemark;

    --1. Insert data RGTO ke tt untuk yang belum dibuat PO
         /* Modify by Henik (04-01-2018), untuk RGTO yg belum dibuat PO gr_qty nya langsung diset 0 */
    IF( pPoNo = vEmptyString AND pPoRemark = vEmptyString ) THEN
        EXECUTE '
        INSERT INTO tt_pemenuhan_stok(
            session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
            rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty, gr_qty, purch_price)
        SELECT $1, C.product_id, A.inventory_id, C.inventory_item_id, B.ou_to_id, B.doc_type_id,
            F.warehouse_name, G.warehouse_name, B.doc_no, B.doc_date, COALESCE(N.qty_request,C.qty_request, 0), $9 AS gr_qty, J.purch_price + J.tax_amount
        FROM in_balance_req_transfer_out A
        JOIN in_inventory B ON A.inventory_id = B.inventory_id
        JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
        LEFT JOIN in_req_trf_out_po_balance_item I ON B.inventory_id = I.inventory_id AND C.inventory_item_id =  I.inventory_item_id
        JOIN m_product D ON C.product_id = D.product_id
        JOIN m_product_custom E ON D.product_id = E.product_id
        JOIN m_product_custom_for_dlg K ON E.product_id = K.product_id
        JOIN m_warehouse F ON B.warehouse_from_id =  F.warehouse_id
        JOIN m_warehouse G ON B.warehouse_to_id =  G.warehouse_id
        JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
        LEFT JOIN m_purch_price_product J ON C.product_id = J.product_id
                             AND J.ou_id = H.ou_bu_id
                             AND $4 BETWEEN J.date_from AND J.date_to
                             AND J.partner_id = K.supplier_id
        LEFT JOIN in_req_trf_out_correction_item N ON I.inventory_item_id = N.ref_item_id  AND I.flg_correction = $8
        WHERE A.tenant_id = $2
            AND B.doc_type_id = $3
            AND B.doc_date BETWEEN $4 AND $5
            AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
            AND NOT EXISTS (
                    SELECT 1
                    FROM pu_po z
                    JOIN pu_po_item Y ON Z.po_id = Y.po_id AND Y.product_id = C.product_id AND Y.ref_id = C.inventory_item_id
                    JOIN pu_po_balance_item X ON Y.po_item_id = X.po_item_id
                    WHERE Z.ref_id = A.inventory_id
                        AND Z.ref_doc_type_id = $7
                    )' ||
              vFilterCtgrProduct ||
              vFilterSubCtgrProduct ||
              vFilterGolonganProduct ||
              vFilterProductCodeName ||
              vFilterRgtoNo ||
              vFilterRgtoRemark ||
--            vFilterPoNo ||
--            vFilterPoRemark ||
              vFilterGto ||
              vFilterGti ||
              vFilterGtir
        USING pSessionId, pTenantId, vDocTypeRgto, pPeriodFrom, pPeriodTo,
              pOuId, vDocTypeRgto, vYes, vEmptyAmount;
    END IF;

    --2. Insert data RGTO dan PO
         /* Modify by Henik (04-01-2018), gr_qty diubah berdasarkan pu_po_balance_item.qty_int_rcv bukan dari in_req_trf_out_po_balance_item.qty_rcv_int
          * Alasannya, karna yg di in_req_trf_out_po_balance_item.qty_rcv_int nilainya sudah terakumulasi dari GR beberapa PO */
    EXECUTE '
    INSERT INTO tt_pemenuhan_stok(
            session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
            rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
            po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date,po_qty, gr_qty, purch_price)
    SELECT $1, C.product_id, A.inventory_id, C.inventory_item_id, B.ou_to_id, B.doc_type_id,
        F.warehouse_name, G.warehouse_name, B.doc_no, B.doc_date,  COALESCE(O.qty_request,C.qty_request, 0) AS qty_request,
        I.po_id, J.po_item_id, I.doc_type_id, I.doc_no, I.doc_date, J.qty_int, K.qty_int_rcv, M.purch_price + M.tax_amount
    FROM in_balance_req_transfer_out A
    JOIN in_inventory B ON A.inventory_id = B.inventory_id
    JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
    JOIN pu_po I ON A.inventory_id = I.ref_id AND I.ref_doc_type_id = $3
    JOIN pu_po_item J ON I.po_id = J.po_id AND C.inventory_item_id = J.ref_id
    JOIN pu_po_balance_item K ON J.po_item_id = K.po_item_id
    JOIN in_req_trf_out_po_balance_item L ON B.inventory_id = L.inventory_id AND C.inventory_item_id =  L.inventory_item_id
    JOIN m_product D ON C.product_id = D.product_id
    JOIN m_product_custom E ON D.product_id = E.product_id
    JOIN m_product_custom_for_dlg N ON  E.product_id = N.product_id
    JOIN m_warehouse F ON B.warehouse_from_id =  F.warehouse_id
    JOIN m_warehouse G ON B.warehouse_to_id =  G.warehouse_id
    JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
    LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
                        AND M.ou_id = H.ou_bu_id
                        AND $4 BETWEEN M.date_from AND M.date_to
                        AND M.partner_id = N.supplier_id
    LEFT JOIN in_req_trf_out_correction_item O ON L.inventory_item_id = O.ref_item_id AND L.flg_correction = $9
    WHERE A.tenant_id = $2
        AND B.doc_type_id = $3
        AND B.doc_date BETWEEN $4 AND $5
        AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
        AND I.doc_type_id = $7
        AND I.status_doc != $8 '||
          vFilterCtgrProduct ||
          vFilterSubCtgrProduct ||
          vFilterGolonganProduct ||
          vFilterProductCodeName ||
          vFilterRgtoNo ||
          vFilterRgtoRemark ||
--        vFilterPoNo ||
--        vFilterPoRemark ||
          vFilterGto ||
          vFilterGti ||
          vFilterGtir
    USING pSessionId, pTenantId, vDocTypeRgto, pPeriodFrom, pPeriodTo,
          pOuId, vDocTypePurchaseOrder, vFlgRejected, vYes;

    --Insert Data GTO tanpa referensi jika tidak ada filter terhadap RGTO no dan remark
    IF (pRgtoNo = vEmptyString AND pRgtoRemark = vEmptyString) THEN
        -- Membuat Filter GTO
        IF(pGtoNo != vEmptyString) THEN
            vFilterGtoForDataGto = ' AND (UPPER(B.doc_no) LIKE UPPER('''||'%'||pGtoNo||'%'||''')) ';
        END IF;

        IF(pGtoRemark != vEmptyString) THEN
            vFilterGtoForDataGto = vFilterGtoForDataGto || ' AND (UPPER(B.remark) LIKE UPPER('''||'%'||pGtoRemark||'%'||''')) ';
        END IF;

        -- Membuat Filter GTI
        IF(pGtiNo != vEmptyString OR pGtiRemark != vEmptyString) THEN
            vFilterGtiForDataGto = ' AND EXISTS (
                        SELECT 1
                        FROM in_inventory GTI
                        WHERE B.inventory_id = GTI.ref_id
                            AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn;
            IF(pGtiNo != vEmptyString) THEN
                vFilterGtiForDataGto = vFilterGtiForDataGto || ' AND (UPPER(GTI.doc_no) LIKE UPPER('''||'%'||pGtiNo||'%'||''')) ';
            END IF;

            IF(pGtiRemark != vEmptyString) THEN
                vFilterGtiForDataGto = vFilterGtiForDataGto || ' AND (UPPER(GTI.remark) LIKE UPPER('''||'%'||pGtiRemark||'%'||''')) ';
            END IF;
            vFilterGtiForDataGto = vFilterGtiForDataGto || ' ) ';
        END IF;

        -- Membuat Filter GTIR
        IF(pGtirNo != vEmptyString OR pGtirRemark != vEmptyString) THEN
            vFilterGtirForDataGto = ' AND EXISTS (
                        SELECT 1
                        FROM in_inventory GTI
                        JOIN in_inventory GTIR ON GTI.inventory_id = GTIR.ref_id
                        WHERE B.inventory_id = GTI.ref_id
                            AND GTI.doc_type_id = '|| vDocTypeGoodsTransferIn ||'
                            AND GTIR.doc_type_id = '|| vDocTypeGoodsTransferInReceipt;
            IF(pGtirNo != vEmptyString) THEN
                vFilterGtirForDataGto = vFilterGtirForDataGto || ' AND (UPPER(GTIR.doc_no) LIKE UPPER('''||'%'||pGtirNo||'%'||''')) ';
            END IF;

            IF(pGtirRemark != vEmptyString) THEN
                vFilterGtirForDataGto = vFilterGtirForDataGto || ' AND (UPPER(GTIR.remark) LIKE UPPER('''||'%'||pGtirRemark||'%'||''')) ';
            END IF;
            vFilterGtirForDataGto = vFilterGtirForDataGto || ' ) ';

        END IF;

        EXECUTE '
            INSERT INTO tt_pemenuhan_stok(
                    session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
                    rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
                    po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date, gr_qty, purch_price,
                    gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date, gto_rgto_date_diff,
                    gto_qty)

            SELECT $1, C.product_id, $8, $8, B.ou_to_id, $8,
                F.warehouse_name, G.warehouse_name, $9, $9, 0 AS qty_request,
                $8, $8, $8, $9, $9, 0, M.purch_price + M.tax_amount,
                B.inventory_id, C.inventory_item_id, B.doc_type_id, B.doc_no, B.doc_date, 0,
                C.qty_realization
            FROM in_balance_transfer_out A
            JOIN in_inventory B ON A.inventory_id = B.inventory_id
            JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
            JOIN m_product D ON C.product_id = D.product_id
            JOIN m_product_custom E ON D.product_id = E.product_id
            JOIN m_product_custom_for_dlg N ON  E.product_id = N.product_id
            JOIN m_warehouse F ON B.warehouse_from_id =  F.warehouse_id
            JOIN m_warehouse G ON B.warehouse_to_id =  G.warehouse_id
            JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
            LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
                                AND M.ou_id = H.ou_bu_id
                                AND $4 BETWEEN M.date_from AND M.date_to
                                AND M.partner_id = N.supplier_id
            WHERE A.tenant_id = $2
                AND B.doc_type_id = $3
                AND B.doc_date BETWEEN $4 AND $5
                AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
                AND B.ref_id = $8 '||
                vFilterCtgrProduct ||
                vFilterSubCtgrProduct ||
                vFilterGolonganProduct ||
                vFilterProductCodeName ||
                vFilterGtoForDataGto ||
                vFilterGtiForDataGto ||
                vFilterGtirForDataGto
        USING pSessionId, pTenantId, vDocTypeGoodsTransferOut, pPeriodFrom, pPeriodTo,
              pOuId, vYes,vEmptyId,vEmptyString;


        -- insert untuk GTI item baru dari GTO non reference
        EXECUTE '
            INSERT INTO tt_pemenuhan_stok(
                    session_id, product_id, rgto_id,rgto_item_id, rgto_ou_id, rgto_doc_type_id,
                    rgto_whs_from, rgto_whs_to, rgto_doc_no, rgto_doc_date, rgto_qty,
                    po_id, po_item_id, po_doc_type_id, po_doc_no, po_doc_date, gr_qty, purch_price,
                    gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date, gto_rgto_date_diff,
                    gto_qty, gti_id, gti_item_id, gti_doc_type_id, gti_doc_no, gti_doc_date, gti_qty)
            SELECT $1, C.product_id, $8, $8, B.ou_to_id, $8,
                F.warehouse_name, G.warehouse_name, $9, $9, 0 AS qty_request,
                $8, $8, $8, $9, $9, 0, M.purch_price + M.tax_amount,
                B.inventory_id, $8, B.doc_type_id, B.doc_no, B.doc_date, 0,
                C.qty_out, Z.inventory_id, C.inventory_item_id, Z.doc_type_id, Z.doc_no, Z.doc_date,
                C.qty_in
            FROM in_balance_transfer_out A
            JOIN in_inventory B ON A.inventory_id = B.inventory_id
            JOIN in_inventory Z ON Z.ref_id = B.inventory_id AND Z.ref_doc_type_id = B.doc_type_id AND Z.doc_type_id = $10
            JOIN in_balance_transfer_in_item C ON Z.inventory_id = C.inventory_id
            JOIN m_product D ON C.product_id = D.product_id
            JOIN m_product_custom E ON D.product_id = E.product_id
            JOIN m_product_custom_for_dlg N ON  E.product_id = N.product_id
            JOIN m_warehouse F ON B.warehouse_from_id =  F.warehouse_id
            JOIN m_warehouse G ON B.warehouse_to_id =  G.warehouse_id
            JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
            LEFT JOIN m_purch_price_product M ON C.product_id = M.product_id
                                AND M.ou_id = H.ou_bu_id
                                AND $4 BETWEEN M.date_from AND M.date_to
                                AND M.partner_id = N.supplier_id
            WHERE A.tenant_id = $2
                AND B.doc_type_id = $3
                AND B.doc_date BETWEEN $4 AND $5
                AND (H.ou_bu_id = $6 OR H.ou_branch_id = $6)
                AND B.ref_id = $8
                AND C.ref_item_id = $8 '||
                vFilterCtgrProduct ||
                vFilterSubCtgrProduct ||
                vFilterGolonganProduct ||
                vFilterProductCodeName ||
                vFilterGtoForDataGto ||
                vFilterGtiForDataGto ||
                vFilterGtirForDataGto
        USING pSessionId, pTenantId, vDocTypeGoodsTransferOut, pPeriodFrom, pPeriodTo,
              pOuId, vYes,vEmptyId,vEmptyString, vDocTypeGoodsTransferIn;
    END IF;

    --3. update qty_correction
    UPDATE tt_pemenuhan_stok A
    SET rgto_qty_correction = C.qty_request_correction
    FROM in_inventory B, in_req_trf_out_correction_item C
    WHERE  A.rgto_id = B.ref_id
        AND A.rgto_item_id = C.ref_item_id
        AND A.session_id = pSessionId
        AND B.doc_type_id =  vDocTypeCorrQtyRgto
        AND B.ref_doc_type_id = A.rgto_doc_Type_id
        AND B.status_doc = vFlgApproved;

    --4. Update data GR
    WITH get_last_rg AS(
        SELECT A.po_id, MAX(C.doc_date) AS doc_date, C.ref_doc_type_id
        FROM tt_pemenuhan_stok A
        INNER JOIN pu_receive_goods C ON C.ref_id = A.po_id
        WHERE  C.ref_doc_type_id = A.po_doc_Type_id
            AND A.session_id = pSessionId
            AND C.doc_type_id = vDocTypeReceiveGoods
            AND C.status_doC = vFlgApproved
        GROUP BY A.po_id,C.ref_doc_type_id
    )
    UPDATE tt_pemenuhan_stok A
    SET gr_doc_date = B.doc_date,
        gr_rgto_date_diff = ((B.doc_date)::date - A.rgto_doc_date::date)::numeric
    FROM get_last_rg B
    WHERE B.po_id = A.po_id
        AND B.ref_doc_type_id = A.po_doc_Type_id;

    --5. Update data GTO
    -- jika terjadi GTO maka qty RGTO = qty GTO
    WITH sum AS(
        select B.inventory_id,C.product_id, SUM(C.qty_realization) AS qty
        from tt_pemenuhan_stok A, in_inventory B, in_inventory_item C
        WHERE A.rgto_id = B.ref_id
            AND B.inventory_id = C.inventory_id
            AND A.rgto_item_id = C.ref_id
            AND B.doc_type_id = vDocTypeGoodsTransferOut
            AND B.ref_doc_type_id = A.rgto_doc_type_id
            AND A.session_id = pSessionId
            AND B.status_doc = vFlgApproved
        GROUP BY B.inventory_id, C.product_id
    )
    UPDATE tt_pemenuhan_stok A
    SET gto_id = B.inventory_id,
        gto_item_id = C.inventory_item_id,
        gto_doc_type_id= B.doc_type_id,
        gto_doc_no = B.doc_no,
        gto_doc_date = B.doc_date,
        gto_rgto_date_diff = (B.doc_date::date - A.rgto_doc_date::date)::numeric,
        gto_qty = D.qty,
        rgto_qty = D.qty
    FROM in_inventory B
    INNER JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
    INNER JOIN sum D ON B.inventory_id = D.inventory_id AND c.product_id = D.product_id
    WHERE A.rgto_id = B.ref_id
        AND B.ref_doc_type_id = vDocTypeRgto
        AND A.rgto_item_id = C.ref_id;

    --update RGTO yg kena GTO untuk item yg tidak ikut GTO (qty rgto = 0),
    UPDATE tt_pemenuhan_stok A
    SET rgto_qty = 0
    FROM in_inventory B
    WHERE A.rgto_id = B.ref_id
        AND B.ref_doc_type_id = vDocTypeRgto
        AND B.doc_type_id = vDocTypeGoodsTransferOut
        AND NOT EXISTS (select 1 FROM in_inventory_item C
                        WHERE B.inventory_id = C.inventory_id AND
                              A.rgto_item_id = C.ref_id)
        AND B.status_doc = vFlgApproved;

    --6.a Update data GTI
    UPDATE tt_pemenuhan_stok A
    SET gti_id  = B.inventory_id,
        gti_item_id = C.inventory_item_id,
        gti_doc_type_id = B.doc_type_id,
        gti_doc_no = B.doc_no,
        gti_doc_date = B.doc_date,
        gti_qty = C.qty_realization
    FROM in_inventory B
    INNER JOIN in_inventory_item C ON B.inventory_id = C.inventory_id
    WHERE A.gto_id = B.ref_id
        AND A.gto_item_id = C.ref_item_id
        AND A.session_id = pSessionId
        AND B.doc_type_id = vDocTypeGoodsTransferIn
        AND B.ref_doc_type_id = A.gto_doc_Type_id
        AND B.status_doc = vFlgApproved;

    --6.b insert data GTI untuk item yang dimasukan saat buat GTI
    EXECUTE '
        INSERT INTO tt_pemenuhan_stok(
                session_id, product_id, purch_price, rgto_id, rgto_doc_type_id,
                rgto_item_id, rgto_ou_id, rgto_whs_from, rgto_whs_to, rgto_doc_no,
                rgto_doc_date, rgto_qty, rgto_qty_correction,
                gto_id, gto_item_id, gto_doc_type_id, gto_doc_no, gto_doc_date,
                gto_rgto_date_diff, gto_qty, gti_id, gti_item_id, gti_doc_type_id,
                gti_doc_no, gti_doc_date, gti_qty)
        SELECT $1, N.product_id, J.purch_price + J.tax_amount, B.inventory_id, B.doc_type_id,
            $2, B.ou_to_id, F.warehouse_name, G.warehouse_name, B.doc_no,
            B.doc_date, $3, $3,
            L.inventory_id, $2, L.doc_type_id, L.doc_no, L.doc_date,
            (L.doc_date::date - B.doc_date::date)::numeric, N.qty_out, M.inventory_id, N.inventory_item_id, M.doc_type_id,
            M.doc_no, M.doc_date, N.qty_in
        FROM in_balance_req_transfer_out A
        JOIN in_inventory B ON A.inventory_id = B.inventory_id  AND B.doc_type_id = $4
        JOIN in_inventory L ON B.inventory_id = L.ref_id AND L.doc_type_id = $5
        JOIN in_inventory M ON L.inventory_id = M.ref_id AND M.doc_type_id = $6
        JOIN in_balance_transfer_in_item N ON M.inventory_id = N.inventory_id
        JOIN m_product D ON N.product_id = D.product_id
        JOIN m_product_custom E ON D.product_id = E.product_id
        JOIN m_product_custom_for_dlg K ON E.product_id = K.product_id
        JOIN m_warehouse F ON B.warehouse_from_id =  F.warehouse_id
        JOIN m_warehouse G ON B.warehouse_to_id =  G.warehouse_id
        JOIN m_ou_structure H ON B.ou_to_id = H.ou_id
        LEFT JOIN m_purch_price_product J ON D.product_id = J.product_id
                            AND J.ou_id = H.ou_bu_id
                            AND $7 BETWEEN J.date_from AND J.date_to
                            AND J.partner_id = K.supplier_id
        WHERE A.tenant_id = $8
            AND B.doc_date BETWEEN $7 AND $9
            AND (H.ou_bu_id = $10 OR H.ou_branch_id = $10)
            AND N.ref_item_id = $2 ' ||
          vFilterCtgrProduct ||
          vFilterSubCtgrProduct ||
          vFilterGolonganProduct ||
          vFilterProductCodeName ||
          vFilterRgtoNo ||
          vFilterRgtoRemark ||
--        vFilterPoNo ||
--        vFilterPoRemark ||
          vFilterGto ||
          vFilterGti ||
          vFilterGtir
        USING pSessionId, vEmptyId, vZero, vDocTypeRgto, vDocTypeGoodsTransferOut, vDocTypeGoodsTransferIn, pPeriodFrom, pTenantId, pPeriodTo, pOuId;

    --7.a. Update data GTIR
    UPDATE tt_pemenuhan_stok A
    SET gtir_id = B.inventory_id,
          gtir_doc_type_id = B.doc_type_id,
          gtir_doc_no = B.doc_no,
          gtir_doc_date = B.doc_date
    FROM in_inventory B
    WHERE A.gti_id = B.ref_id
        AND A.session_id = pSessionId
        AND B.doc_type_id = vDocTypeGoodsTransferInReceipt
        AND B.ref_doc_type_id = A.gti_doc_Type_id
        AND B.status_doc = vFlgApproved
        AND EXISTS (
            SELECT 1
            FROM in_inventory_item_receipt C
            WHERE B.inventory_id = C.inventory_id
                AND A.gti_item_id = C.ref_item_id
        );

    --7.b. Update data GTIR qty_missing
    UPDATE tt_pemenuhan_stok A
    SET gtir_qty_missing = qty_realization
    FROM in_inventory_item_receipt C
    WHERE A.gtir_id = C.inventory_id
        AND A.product_id = C.product_id
        AND A.session_id = pSessionId
        AND C.reason_receipt_code = vFlgQtyMissing;

    UPDATE tt_pemenuhan_stok A
    SET gtir_qty_rejected = qty_realization
    FROM in_inventory_item_receipt C
    WHERE A.gtir_id = C.inventory_id
        AND A.product_id = C.product_id
        AND A.session_id = pSessionId
        AND C.reason_receipt_code = vFlgQtyRejected;

    UPDATE tt_pemenuhan_stok A
    SET gtir_qty_correct = qty_realization
    FROM in_inventory_item_receipt C
    WHERE A.gtir_id = C.inventory_id
        AND A.product_id = C.product_id
        AND A.session_id = pSessionId
        AND C.reason_receipt_code = vFlgQtyCorrect;

    UPDATE tt_pemenuhan_stok A
    SET gtir_qty_lost = qty_realization
    FROM in_inventory_item_receipt C
    WHERE A.gtir_id = C.inventory_id
        AND A.product_id = C.product_id
        AND A.session_id = pSessionId
        AND C.reason_receipt_code = vFlgQtyLost;


    Open pRefHeader FOR
    SELECT f_get_ou_name(vOuBuId) AS ou_bu_name, pPeriodFrom AS period_from,  pPeriodTo AS period_to,
            pPeriodTo AS period_to, f_get_ou_name(pOuId) AS ou_name,
            CASE WHEN pCtgrProductId != vEmptyId THEN f_get_ctgr_product_code(pCtgrProductId)||' - '||f_get_ctgr_product_name(pCtgrProductId) ELSE 'ALL' END AS ctgr_product,
            CASE WHEN pSubCtgrProductId != vEmptyId THEN f_get_sub_ctgr_product_code(pSubCtgrProductId)||' - '||f_get_sub_ctgr_product_name(pSubCtgrProductId) ELSE 'ALL' END AS sub_ctgr_product,
            CASE WHEN pStyleProduct != vEmptyString THEN pStyleProduct ELSE ' - ' END AS style_product,
            CASE WHEN pProductCodeName != vEmptyString THEN pProductCodeName ELSE ' - ' END AS product_code_name,
            CASE WHEN pRgtoNo != vEmptyString THEN pRgtoNo ELSE ' - ' END AS rgto_no,
            CASE WHEN pRgtoRemark != vEmptyString THEN pRgtoRemark ELSE ' - ' END AS rgto_remark,
            CASE WHEN pPoNo != vEmptyString THEN pPoNo ELSE ' - ' END AS po_no,
            CASE WHEN pPoRemark != vEmptyString THEN pPoRemark ELSE ' - ' END AS po_remark,
            CASE WHEN pGtoNo != vEmptyString THEN pGtoNo ELSE ' - ' END AS gto_no,
            CASE WHEN pGtoRemark != vEmptyString THEN pGtoRemark ELSE ' - ' END AS gto_remark,
            CASE WHEN pGtiNo != vEmptyString THEN pGtiNo ELSE ' - ' END AS gti_no,
            CASE WHEN pGtiRemark != vEmptyString THEN pGtiRemark ELSE ' - ' END AS gti_remark,
            CASE WHEN pGtirNo != vEmptyString THEN pGtirNo ELSE ' - ' END AS gtir_no,
            CASE WHEN pGtirRemark != vEmptyString THEN pGtirRemark ELSE ' - ' END AS gtir_remark;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT A.product_id, B.product_code, B.product_name, C.ctgr_product_name, D.sub_ctgr_product_name,
        E.style_product, A.purch_price, f_get_ou_name(A.rgto_ou_id) AS rgto_ou_name, A.rgto_whs_from, A.rgto_whs_to,
        A.rgto_doc_no, A.rgto_doc_date, A.rgto_qty, CASE WHEN A.rgto_qty_correction IS NOT NULL THEN A.rgto_qty_correction ELSE 0 END AS rgto_qty_correction,
        A.po_doc_no, A.po_doc_date, A.po_qty, A.gr_doc_date, A.gr_rgto_date_diff,
        CASE WHEN gr_doc_date != vEmptyString THEN A.gr_qty END AS gr_qty,
        A.gto_doc_no, A.gto_doc_date, A.gto_rgto_date_diff, A.gto_qty, A.gti_doc_no, A.gti_doc_date, A.gti_qty, A.gto_qty - A.gti_qty AS gto_gti_qty_diff,
        A.gtir_doc_no, A.gtir_doc_date, A.gtir_qty_missing, A.gtir_qty_rejected, A.gtir_qty_correct, A.gtir_qty_lost,
        COALESCE(G.doc_no, vEmptyString) AS rgto_no_induk, COALESCE(G.doc_date, vEmptyString) AS rgto_date_induk
    FROM tt_pemenuhan_stok A
    INNER JOIN m_product B ON A.product_id = B.product_id
    INNER JOIN m_ctgr_product C ON B.ctgr_product_id = C.ctgr_product_id
    INNER JOIN m_sub_ctgr_product D ON B.sub_ctgr_product_id = D.sub_ctgr_product_id
    INNER JOIN m_product_custom E ON B.product_id = E.product_id
    LEFT JOIN in_inventory F ON F.inventory_id = A.rgto_id
    LEFT JOIN in_inventory G ON G.inventory_id = F.ref_id AND G.doc_type_id = F.ref_doc_type_id AND G.doc_type_id = vDocTypeRgto
    WHERE A.session_id = pSessionId
    ORDER BY A.rgto_doc_no, B.product_code;

    RETURN NEXT pRefDetail ;
    DELETE FROM tt_pemenuhan_stok WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_report_pemenuhan_stok(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
  OWNER TO sts;