a

-- Mendapatkan nilai pembelian
    INSERT INTO tt_fab_product(
    session_id,product_id,tenant_id
    )
    SELECT pSessionId ,A.product_id,A.tenant_id
        FROM m_product A
        INNER JOIN m_product_custom_for_sasa B ON A.product_id = B.product_id
        INNER JOIN m_group_brand C ON B.group_brand_id = C.group_brand_id
        WHERE C.group_brand_code = 'FAB'
        AND A.tenant_id = 10 ;

    -- FX
    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vPembelianAmountFx2801
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    AND A.ref_id = C.receive_goods_id
    LEFT OUTER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 111
    AND f_get_ou_code(COALESCE(D.ou_id,-99)) NOT IN ('2803','2808')
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 131
        AND E.curr_code = 'IDR'   )
    AND NOT EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE C.product_id = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vRetPembelianAmountFx2801
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN in_inventory E ON E.inventory_id = A.ref_id AND E.doc_type_id = A.ref_doc_type_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    LEFT OUTER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 511
    AND f_get_ou_code(COALESCE(D.ou_id,-99)) NOT IN ('2803','2808')
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 141
        AND E.curr_code = 'IDR'  )
    AND NOT EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE COALESCE(C.product_id,-99) = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    vPembelianAmountFx2801 := vPembelianAmountFx2801 - vRetPembelianAmountFx2801;

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vPembelianAmountFx2803
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    AND A.ref_id = C.receive_goods_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 111
    AND f_get_ou_code(D.ou_id) ='2803'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 131
        AND E.curr_code = 'IDR' )
    AND NOT EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE C.product_id = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vRetPembelianAmountFx2803
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN in_inventory E ON E.inventory_id = A.ref_id AND E.doc_type_id = A.ref_doc_type_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 511
    AND f_get_ou_code(D.ou_id) = '2803'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 141
        AND E.curr_code = 'IDR'  )
    AND NOT EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE COALESCE(C.product_id,-99) = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    vPembelianAmountFx2803 := vPembelianAmountFx2803 - vRetPembelianAmountFx2803;

    --BUSI
    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vPembelianAmountBusi2801
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    AND A.ref_id = C.receive_goods_id
    LEFT OUTER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 111
    AND f_get_ou_code(COALESCE(D.ou_id,-99)) NOT IN ('2803','2808')
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 131
        AND E.curr_code = 'IDR'   )
    AND EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE C.product_id = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vRetPembelianAmountBusi2801
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN in_inventory E ON E.inventory_id = A.ref_id AND E.doc_type_id = A.ref_doc_type_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    LEFT OUTER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 511
    AND f_get_ou_code(COALESCE(D.ou_id,-99)) NOT IN ('2803','2808')
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 141
        AND E.curr_code = 'IDR'  )
    AND EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE COALESCE(C.product_id,-99) = X.product_id
        AND A.tenant_id = X.tenant_id
    );


    vPembelianAmountBusi2801  := vPembelianAmountBusi2801 - vRetPembelianAmountBusi2801;

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vPembelianAmountBusi2808
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    AND A.ref_id = C.receive_goods_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 111
    AND f_get_ou_code(D.ou_id) ='2808'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 131
        AND E.curr_code = 'IDR' )
    AND EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE C.product_id = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vRetPembelianAmountBusi2808
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN in_inventory E ON E.inventory_id = A.ref_id AND E.doc_type_id = A.ref_doc_type_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 511
    AND f_get_ou_code(D.ou_id) = '2808'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 141
        AND E.curr_code = 'IDR'  )
    AND EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE COALESCE(C.product_id,-99) = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    vPembelianAmountBusi2808  := vPembelianAmountBusi2808 - vRetPembelianAmountBusi2808;

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vPembelianAmountBusi2803
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    AND A.ref_id = C.receive_goods_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 111
    AND f_get_ou_code(D.ou_id) ='2803'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 131
        AND E.curr_code = 'IDR' )
    AND EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE C.product_id = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vRetPembelianAmountBusi2803
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN in_inventory E ON E.inventory_id = A.ref_id AND E.doc_type_id = A.ref_doc_type_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 511
    AND f_get_ou_code(D.ou_id) = '2803'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 141
        AND E.curr_code = 'IDR'  )
    AND EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE COALESCE(C.product_id,-99) = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    vPembelianAmountBusi2803  := vPembelianAmountBusi2803 - vRetPembelianAmountBusi2803;
    -- F4
    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vPembelianAmountF42808
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    AND A.ref_id = C.receive_goods_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 111
    AND f_get_ou_code(D.ou_id) ='2808'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 131
        AND E.curr_code = 'IDR' )
    AND NOT EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE C.product_id = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    SELECT COALESCE(SUM(A.item_amount + COALESCE(B.tax_amount,0)),0) INTO vRetPembelianAmountF42808
    FROM pu_po_balance_invoice A
    LEFT OUTER JOIN pu_po_balance_invoice_tax B ON A.tenant_id = B.tenant_id
    AND A.ou_id = B.ou_id AND A.partner_id = B.partner_id
    AND A.po_id = B.po_id AND A.ref_doc_type_id = B.ref_doc_type_id
    AND A.ref_id = B.ref_id AND A.ref_item_id = B.ref_item_id
    INNER JOIN in_inventory E ON E.inventory_id = A.ref_id AND E.doc_type_id = A.ref_doc_type_id
    INNER JOIN pu_receive_goods_item C ON A.ref_item_id = C.receive_goods_item_id
    INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
    WHERE A.invoice_id <> -99
    AND A.tenant_id = 10
    AND A.ref_doc_type_id = 511
    AND f_get_ou_code(D.ou_id) = '2808'
    AND EXISTS (
        SELECT 1 FROM pu_invoice E
        WHERE A.invoice_id = E.invoice_id
        AND LEFT(E.doc_date,6) = '202010'
        AND E.doc_type_id  = 141
        AND E.curr_code = 'IDR'  )
    AND NOT EXISTS (
        SELECT 1 FROM tt_fab_product X
        WHERE COALESCE(C.product_id,-99) = X.product_id
        AND A.tenant_id = X.tenant_id
    );

    vPembelianAmountF42808   := vPembelianAmountF42808 - vRetPembelianAmountF42808;

    DELETE FROM tt_fab_product WHERE session_id = pSessionId ;