r_purch_invoice_list

CREATE OR REPLACE FUNCTION r_purch_invoice_list(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;
    pOuId               ALIAS FOR $6;
    pPartnerId          ALIAS FOR $7;
    pCtgrPartnerId      ALIAS FOR $8;
    pCtgrProductId      ALIAS FOR $9;
    pSubCtgrProductId   ALIAS FOR $10;
    pBrandId            ALIAS FOR $11;
    pDateFrom           ALIAS FOR $12;
    pDateTo             ALIAS FOR $13;


    vEmptyId                bigint := -99;
    vReceiveGoodsDocTypeId  bigint := -99;
    vFilterPartner          text := '';
    vFilterCtgrPartner      text := '';
    vFilterCtgrProduct      text := '';
    vFilterSubCtgrProduct   text := '';
    vFilterBrand            text := '';
    vEmptyValue             character varying(1) := '';
    vRoundingModeNonTax     character varying(5) := '';
    vStatusDocRelease       character varying := 'R';
    vYes                    character varying := 'Y';
    vNo                     character varying := 'N';
    vAlocTypeAmount         character varying := 'AMOUNT';
    vActivityCodeAddAmount  character varying := 'PURCHASING_DISC';
    vZeroValue              numeric := 0;
    vRoundingDigit          integer;
BEGIN

    DELETE FROM tt_purchasing_invoice_list WHERE session_id = pSessionId;

    vReceiveGoodsDocTypeId := 111;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    IF (pPartnerId <> vEmptyId) THEN
        vFilterPartner := ' AND B.partner_id = ' || pPartnerId;
    END IF;

    IF (pCtgrPartnerId <> vEmptyId) THEN
        vFilterCtgrPartner := ' AND M.ctgr_partner_id = ' || pCtgrPartnerId;
    END IF;

    IF (pCtgrProductId <> vEmptyId) THEN
        vFilterCtgrProduct := ' AND I.ctgr_product_id = ' || pCtgrProductId;
    END IF;

    IF (pSubCtgrProductId <> vEmptyId) THEN
        vFilterSubCtgrProduct := ' AND J.sub_ctgr_product_id = ' || pSubCtgrProductId;
    END IF;

    IF (pBrandId <> vEmptyId) THEN
        vFilterBrand := ' AND O.brand_id = ' || pBrandId;
    END IF;

    vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);

    Open pRefHeader FOR
        SELECT pPartnerId AS partner_id, CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pPartnerId) END AS partner_name,
            pCtgrPartnerId AS ctgr_partner_id, pCtgrProductId AS ctgr_product_id, pSubCtgrProductId AS sub_ctgr_product_id,
            CASE WHEN pCtgrPartnerId = vEmptyId THEN '(All)' ELSE f_get_ctgr_partner_name(pCtgrPartnerId) END AS ctgr_partner_name,
            CASE WHEN pCtgrProductId = vEmptyId THEN '(All)' ELSE f_get_ctgr_product_name(pCtgrProductId) END AS ctgr_product_name,
            CASE WHEN pSubCtgrProductId = vEmptyId THEN '(All)' ELSE f_get_sub_ctgr_product_name(pSubCtgrProductId) END AS sub_ctgr_product_name,
            pBrandId AS brand_id, CASE WHEN pBrandId = vEmptyId THEN '(All)' ELSE f_get_brand_name(pBrandId) END AS brand_name,
            pDateFrom AS date_from, pDateTo AS date_to, pDatetime AS print_datetime, pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name;
    RETURN NEXT pRefHeader;

    -- Masukan data yang diperlukan ke temporer
    EXECUTE '
    INSERT INTO tt_purchasing_invoice_list (
        session_id, ou_id, partner_id, invoice_item_id, ctgr_product_id,
        sub_ctgr_product_id, curr_code, partner_name, doc_no, doc_date,
        ref_no, ref_date, po_no, po_date, product_code, product_name,
        qty_po, uom_po, price, item_amount,
        tax_amount, invoice_id,
        flg_discount, gross_price_before_disc, discount_amount, discount_percentage,
        no_faktur, tgl_faktur,
        add_amount, alloc_type, flg_tax_amount, tax_percentage,
        flg_update
    )
    SELECT $9 AS session_id, A.ou_id, B.partner_id, D.invoice_item_id, I.ctgr_product_id,
        J.sub_ctgr_product_id, A.curr_code, B.partner_name, A.doc_no, A.doc_date,
        A.ext_doc_no AS ref_no, A.ext_doc_date AS ref_date, C.doc_no AS po_no, C.doc_date AS po_date, G.product_code, G.product_name,
        SUM(E.qty_rcv_po) AS qty_po, H.uom_code AS uom_po, F.nett_price_po AS price, SUM(Q.item_amount) AS item_amount,
        SUM(COALESCE(R.tax_amount, 0)) AS tax_amount, A.invoice_id,
        P.flg_discount, P.gross_price_before_disc, F.discount_amount, F.discount_percentage,
        COALESCE(S.tax_no, $7) AS no_faktur, COALESCE(S.tax_date, $7) AS tgl_faktur,
        AY.add_amount, AX.alloc_type, F.flg_tax_amount, F.tax_percentage,
        CASE WHEN AY.add_amount = 0 OR SUM(Q.item_amount) = 0
            THEN $11
            ELSE $10
        END AS flg_update
    FROM pu_invoice A
      INNER JOIN pu_invoice_additional_for_dlg AX ON A.invoice_id = AX.invoice_id
      INNER JOIN pu_invoice_cost AY ON A.invoice_id = AY.invoice_id
      INNER JOIN m_partner B ON A.partner_id = B.partner_id
      INNER JOIN pu_po C ON A.ref_id = C.po_id
      INNER JOIN pu_invoice_item D ON A.invoice_id = D.invoice_id
      INNER JOIN pu_receive_goods_item E ON D.ref_item_id = E.receive_goods_item_id
      INNER JOIN pu_po_item F ON E.ref_id = F.po_item_id
      INNER JOIN pu_po_item_additional_for_dlg P ON P.po_item_id = F.po_item_id
      INNER JOIN m_product G ON F.product_id = G.product_id
      INNER JOIN m_uom H ON F.po_uom_id = H.uom_id
      INNER JOIN m_ctgr_product I ON G.ctgr_product_id = I.ctgr_product_id
      INNER JOIN m_sub_ctgr_product J ON G.sub_ctgr_product_id = J.sub_ctgr_product_id
      INNER JOIN t_user_role K ON K.user_id = $1 AND K.role_id = $2
      INNER JOIN m_policy_product L ON J.sub_ctgr_product_id = L.sub_ctgr_product_id AND K.user_role_id = L.user_role_id
      INNER JOIN m_ctgr_partner M ON B.ctgr_partner_id = M.ctgr_partner_id
      INNER JOIN m_policy_partner N ON M.ctgr_partner_id = N.ctgr_partner_id AND K.user_role_id = N.user_role_id
      INNER JOIN m_brand O ON G.brand_id = O.brand_id
      INNER JOIN pu_po_balance_invoice Q
            ON A.tenant_id = Q.tenant_id AND A.ou_id = Q.ou_id
                AND C.po_id = Q.po_id AND D.ref_doc_type_id = Q.ref_doc_type_id
                AND E.receive_goods_id = Q.ref_id AND E.receive_goods_item_id = Q.ref_item_id
      LEFT JOIN pu_po_balance_invoice_tax R
            ON Q.tenant_id = R.tenant_id AND Q.ou_id = R.ou_id
                AND Q.po_id = R.po_id AND Q.ref_doc_type_id = R.ref_doc_type_id
                AND Q.ref_id = R.ref_id AND Q.ref_item_id = R.ref_item_id AND F.tax_id = R.tax_id
      LEFT JOIN pu_invoice_tax S ON A.invoice_id = S.invoice_id
      WHERE A.tenant_id = $3
        AND A.ou_id = $4
        AND A.doc_date BETWEEN $5 AND $6
        AND A.status_doc = $8 ' ||
        vFilterPartner  ||
        vFilterCtgrPartner  ||
        vFilterCtgrProduct  ||
        vFilterSubCtgrProduct   ||
        vFilterBrand ||'
    GROUP BY A.ou_id, B.partner_id, D.invoice_item_id, I.ctgr_product_id,
        J.sub_ctgr_product_id, A.curr_code, B.partner_name, A.doc_no, A.doc_date,
        A.ext_doc_no, A.ext_doc_date, C.doc_no, C.doc_date, G.product_code, G.product_name,
        H.uom_code, F.nett_price_po, A.invoice_id, F.line_no,
        P.flg_discount, P.gross_price_before_disc, F.discount_amount, F.discount_percentage,
        S.tax_no, S.tax_date, AY.add_amount, AX.alloc_type, F.flg_tax_amount, F.tax_percentage
    ORDER BY F.line_no, A.curr_code, B.partner_name, A.invoice_id, A.doc_no'
    USING pUserId, pRoleId, pTenantId, pOuId, pDateFrom, pDateTo, vEmptyValue, vStatusDocRelease, pSessionId, vYes, vNo;

    -- Hitung prorate amount
    WITH data_for_calc_prorate_amount AS (
        SELECT session_id, invoice_id, invoice_item_id,
                CASE WHEN alloc_type = vAlocTypeAmount
                    THEN ROUND((item_amount * add_amount ) / SUM(item_amount) OVER (PARTITION BY invoice_id), vRoundingDigit)
                    ELSE ROUND((qty_po * add_amount ) / SUM(qty_po) OVER (PARTITION BY invoice_id), vRoundingDigit)
                END prorate_amount
        FROM tt_purchasing_invoice_list
        WHERE session_id = pSessionId
        AND flg_update = vYes
    )
    UPDATE tt_purchasing_invoice_list A SET
        prorate_amount = B.prorate_amount
    FROM data_for_calc_prorate_amount B
    WHERE A.session_id = B.session_id
    AND A.invoice_id = B.invoice_id
    AND A.invoice_item_id = B.invoice_item_id;

    -- Hitung prorate amount dengan seslisih dari total prorate dan additional discount
    WITH data_invoice_item_dan_selisih_prorate AS (
        SELECT session_id, invoice_id, invoice_item_id, prorate_amount,
        row_number() OVER(PARTITION BY invoice_id ORDER BY invoice_id, prorate_amount DESC) AS line_no,
        SUM(prorate_amount) OVER (PARTITION BY invoice_id) - add_amount AS selisih
        FROM tt_purchasing_invoice_list
        WHERE session_id = pSessionId
        AND flg_update = vYes
        ORDER BY invoice_id, prorate_amount DESC
    ), perhitungan_selisih_prorate AS (
        SELECT session_id, invoice_id, invoice_item_id,
        CASE WHEN line_no = 1
            THEN prorate_amount - selisih
            ELSE prorate_amount
        END prorate_amount
        FROM data_invoice_item_dan_selisih_prorate
    )

    -- Update nilai prorate_amount, tax_amount, item_amount, dan discount amount
    UPDATE tt_purchasing_invoice_list A SET
        prorate_amount = B.prorate_amount,
        tax_amount = f_tax_rounding(
                    pTenantId,
                    f_get_amount_before_tax(
                            A.item_amount+B.prorate_amount,
                            A.flg_tax_amount,
                            A.tax_percentage,
                            f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code),
                            vRoundingModeNonTax),
                    A.tax_percentage),
        item_amount = A.item_amount+B.prorate_amount,
        discount_amount = A.discount_amount-B.prorate_amount
    FROM perhitungan_selisih_prorate B
    WHERE A.session_id = B.session_id
    AND A.invoice_id = B.invoice_id
    AND A.invoice_item_id = B.invoice_item_id;

    Open pRefDetail FOR
        SELECT A.ou_id, A.partner_id, A.invoice_item_id, A.ctgr_product_id,
            A.sub_ctgr_product_id, A.curr_code, A.partner_name, A.doc_no, A.doc_date,
            A.ref_no, A.ref_date, A.po_no, A.po_date, A.product_code, A.product_name,
            A.qty_po, A.uom_po, A.price, A.item_amount, A.tax_amount, A.invoice_id,
            ROUND((CASE WHEN (A.flg_discount = 'A') THEN
                (CASE WHEN (A.gross_price_before_disc = 0)
                    THEN 0
                    ELSE (A.discount_amount*100/A.gross_price_before_disc)
                END)
            ELSE A.discount_percentage END), 2) AS disc_percentage,
            A.no_faktur, A.tgl_faktur
        FROM tt_purchasing_invoice_list A
        WHERE A.session_id = pSessionId;
    RETURN NEXT pRefDetail ;

    DELETE FROM tt_purchasing_invoice_list WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;