Cetak Invoice JRP

CREATE OR REPLACE FUNCTION r_print_sales_invoice_escp_for_jrp(character varying, bigint, bigint, bigint, character varying, bigint, 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;
    pUserId                 ALIAS FOR $3;
    pRoleId                 ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;
    pInvoiceId              ALIAS FOR $6;
    pFlgPkp                 ALIAS FOR $7;
    pNamaPenanggungJwb      ALIAS FOR $8;
    pSikaPenanggungJwb      ALIAS FOR $9;

    vDoDocTypeId                bigint;
    vReportName                 character varying;
    vDoNo                       character varying;
    vAdditionalCost             numeric;
    vDownPaymentAmount          numeric;
    vDownPaymentAmountWithTax   numeric;
    vTaxDownPaymentAmount       numeric;
    vFlgShowPpn                 character varying := 'Y';
    vOuBuInfoReport             OU_INFO_REPORT%ROWTYPE;
    result                      record;

    vSubTotal                   numeric;
    vDiscount                   numeric;
    vDpp                        numeric;
    vPpn                        numeric;
    vTaxPercentage              numeric := 100.00;
    vRoundingModeTax            character varying(5);
    vRoundingModeNonTax         character varying(5);
    vRoundingScalePrintTotal    integer;
    vNullRefId                  bigint := -99;
    vEmptyString                character varying(1) :='';
    vCtgrProductCode                character varying;
    vCtgrProductName                character varying;
    vYes                        character varying(1) :='Y';
    vAll                        character varying;
    vOuBuId                     bigint;
    vGovTaxAmount               numeric;

BEGIN
    vDoDocTypeId := 311;
    vReportName := 'FormSalesInvoiceForJrp';
    vAll := 'ALL';

    DELETE FROM tt_cetak_invoice_jrp WHERE session_id = pSessionId;
    DELETE FROM tt_final_cetak_invoice_jrp WHERE session_id = pSessionId;

    select (f_get_ou_bu_structure(A.ou_id)).ou_bu_id
    FROM sl_invoice A
    WHERE A.invoice_id = pInvoiceId INTO vOuBuId ;

    SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
    FROM sl_invoice A
    WHERE A.invoice_id = pInvoiceId INTO result ;
    vOuBuInfoReport := result.info;

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.tax') INTO vRoundingModeTax;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.print.total') AS integer) INTO vRoundingScalePrintTotal;

    vRoundingScalePrintTotal := COALESCE(vRoundingScalePrintTotal,2);


    /* get delivery order number */
    SELECT STRING_AGG(Z.doc_no, ' , ' ) INTO vDoNo
    FROM (
        SELECT B.doc_no
        FROM sl_invoice_item A
          INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ref_id = B.do_id
        WHERE A.tenant_id = pTenantId
          AND A.invoice_id = pInvoiceId
        GROUP BY B.do_id
        ORDER BY B.doc_no) Z;
    SELECT ctgr_product_code, ctgr_product_name INTO vCtgrProductCode, vCtgrProductName
    FROM (
        SELECT f_get_ctgr_product_code(C.ctgr_product_id) AS ctgr_product_code,
               f_get_ctgr_product_name(C.ctgr_product_id) AS ctgr_product_name
        FROM sl_invoice_item A
        INNER JOIN sl_do_item B ON A.tenant_id = B.tenant_id AND A.ref_item_id = B.do_item_id
        INNER JOIN m_product C ON B.product_id = C.product_id
        WHERE A.tenant_id = pTenantId
          AND A.invoice_id = pInvoiceId
        limit 1) Z;



    /* insert into table tr_sl_invoice_item*/
    INSERT INTO tr_sl_invoice_item(
        session_id, tenant_id, product_code, product_name,
        qty_so,
        uom_name,
        nett_sell_price,
        gross_sell_price,
        nett_item_amount,
        gross_item_amount,
        tax_price, tax_amount, warranty,
        discount_pct_so,
        serial_number,
        product_expired_date,
        regular_disc_amount,
        promo_disc_amount,
        adj_regular_disc_amount,
        adj_promo_disc_amount,
        dpp_ppn_in_so,
        item_amount_in_so_balance,
        regular_disc_amount_in_so_balance,
        promo_disc_amount_in_so_balance,
        adj_regular_disc_amount_in_so_balance,
        adj_promo_disc_amount_in_so_balance,
        tax_amount_in_so_balance,
        so_balance_invoice_id,
        line_no)
    SELECT pSessionId, pTenantId, D.product_code, D.product_name,
    CASE WHEN G.do_receipt_item_id = vNullRefId THEN
        COALESCE(F.qty_dlv_int, G.qty_dlv_so) ELSE COALESCE(K.qty_return, 0)*-1
    END AS qty_so,
    E.uom_name,
    G.price_so AS nett_sell_price,
    (G.item_amount + COALESCE(L.tax_amount, 0)) / G.qty_dlv_so AS gross_sell_price,
    0 as nett_item_amount,--(G.qty_dlv_so * G.price_so) AS nett_item_amount,
    0 AS gross_item_amount,--(G.price_so + f_tax_rounding(G.tenant_id, G.price_so, sl_get_tax_pct_from_so_balance_inv_tax(pTenantId, G.ou_id, G.partner_id, G.ref_doc_type_id, G.ref_id, G.ref_item_id, G.do_receipt_item_id))) * G.qty_dlv_so AS gross_item_amount,
    f_tax_rounding(G.tenant_id, G.price_so, sl_get_tax_pct_from_so_balance_inv_tax(pTenantId, G.ou_id, G.partner_id, G.ref_doc_type_id, G.ref_id, G.ref_item_id, G.do_receipt_item_id)) AS tax_price,
    COALESCE(L.tax_amount, 0) / G.qty_dlv_so AS tax_amunt,--f_tax_rounding(G.tenant_id, G.price_so, sl_get_tax_pct_from_so_balance_inv_tax(pTenantId, G.ou_id, G.partner_id, G.ref_doc_type_id, G.ref_id, G.ref_item_id, G.do_receipt_item_id)) * G.qty_dlv_so AS tax_amount,
    (f_get_product_warranty_service(C.product_id) :: integer) AS warranty,
    J.discount_percentage,
    COALESCE (I.serial_number, vEmptyString) as serial_number,
    COALESCE (I.product_expired_date, vEmptyString) as product_expired_date,
    G.regular_disc_amount/G.qty_dlv_so AS regular_disc_amount,
    G.promo_disc_amount/G.qty_dlv_so AS promo_disc_amount,
    G.adj_regular_disc_amount/G.qty_dlv_so AS adj_regular_disc_amount,
    G.adj_promo_disc_amount/G.qty_dlv_so AS adj_promo_disc_amount,
    f_calculate_dpp_amount_from_gross_price(J.gross_sell_price, 1, 0,J.flg_tax_amount,J.tax_percentage,vRoundingScalePrintTotal,vRoundingModeNonTax) +
    f_calculate_tax_amount_from_gross_price(J.gross_sell_price, 1, 0,J.flg_tax_amount,J.tax_percentage,vRoundingScalePrintTotal,vRoundingModeNonTax) AS dpp_ppn_in_so,
    G.item_amount AS item_amount_in_so_balance,
    G.regular_disc_amount AS regular_disc_amount_in_so_balance,
    G.promo_disc_amount AS promo_disc_amount_in_so_balance,
    G.adj_regular_disc_amount AS adj_regular_disc_amount_in_so_balance,
    G.adj_promo_disc_amount AS adj_promo_disc_amount_in_so_balance,
    COALESCE(L.tax_amount, 0) AS tax_amount_in_so_balance,
    G.so_balance_invoice_id,
    row_number() OVER(PARTITION BY G.so_balance_invoice_id ORDER BY (G.item_amount + COALESCE(L.tax_amount, 0)) / G.qty_dlv_so * CASE WHEN G.do_receipt_item_id = vNullRefId THEN COALESCE(F.qty_dlv_int, G.qty_dlv_so) ELSE COALESCE(K.qty_return, G.qty_dlv_so)*-1 END DESC,
                                                                     D.product_code, COALESCE (I.serial_number, vEmptyString))
    FROM sl_invoice_item A
    INNER JOIN sl_invoice H ON A.tenant_id = H.tenant_id AND A.invoice_id = H.invoice_id
    INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ref_id = B.do_id
    INNER JOIN sl_do_item C ON A.tenant_id = C.tenant_id AND A.ref_item_id = C.do_item_id
    INNER JOIN m_product D ON A.tenant_id = D.tenant_id AND C.product_id = D.product_id
    INNER JOIN sl_so_balance_invoice G ON A.tenant_id = G.tenant_id AND G.ou_id = H.ou_id AND G.ref_doc_type_id = vDoDocTypeId AND G.ref_id = B.do_id AND G.so_id = B.ref_id AND G.ref_item_id = A.ref_item_id AND G.do_receipt_item_id = A.do_receipt_item_id
    LEFT JOIN sl_so_balance_invoice_tax L ON L.tenant_id = G.tenant_id AND
                                              L.ou_id = G.ou_id AND
                                              L.partner_id = G.partner_id AND
                                              L.ref_doc_type_id = G.ref_doc_type_id AND
                                              L.ref_id = G.ref_id AND
                                              L.ref_item_id = G.ref_item_id AND
                                              L.do_receipt_item_id = G.do_receipt_item_id
    INNER JOIN m_uom E ON A.tenant_id = E.tenant_id AND G.so_uom_id = E.uom_id
    INNER JOIN sl_so_item J ON J.so_item_id = C.ref_id
    LEFT JOIN sl_do_product F ON F.do_item_id = C.do_item_id
    LEFT JOIN in_do_receipt_product K ON K.do_receipt_item_id = G.do_receipt_item_id AND F.product_balance_id = K.product_balance_id
    LEFT JOIN in_product_balance I ON I.product_balance_id = F.product_balance_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;

    UPDATE tr_sl_invoice_item
    SET nett_item_amount = ROUND(qty_so * nett_sell_price,vRoundingScalePrintTotal),
        gross_item_amount = ROUND(qty_so * gross_sell_price,vRoundingScalePrintTotal),
        regular_disc_amount = ROUND(regular_disc_amount * qty_so,vRoundingScalePrintTotal),
        promo_disc_amount  = ROUND(promo_disc_amount* qty_so,vRoundingScalePrintTotal),
        adj_regular_disc_amount  = ROUND(adj_regular_disc_amount* qty_so,vRoundingScalePrintTotal),
        adj_promo_disc_amount  = ROUND(adj_promo_disc_amount* qty_so,vRoundingScalePrintTotal)
    WHERE session_id = pSessionId;

    --hitung selisih regular_disc_amount dari data balance SO, dan dimasukan ke
    -- harga paling tingi (line no nya 1)
    WITH data_selisih AS (
        SELECT so_balance_invoice_id, (regular_disc_amount_in_so_balance) - sum(regular_disc_amount) AS selisih
        FROM tr_sl_invoice_item
        WHERE session_id = pSessionId
        GROUP BY so_balance_invoice_id, regular_disc_amount_in_so_balance
    )
    UPDATE tr_sl_invoice_item Z
    SET regular_disc_amount = Z.regular_disc_amount + A.selisih
    FROM data_selisih A
    WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
          Z.line_no = 1 AND
          Z.session_id = pSessionId;

    --hitung selisih promo_disc_amount dari data balance SO, dan dimasukan ke
    -- harga paling tingi (line no nya 1)
    WITH data_selisih AS (
        SELECT so_balance_invoice_id, (promo_disc_amount_in_so_balance) - sum(promo_disc_amount) AS selisih
        FROM tr_sl_invoice_item
        WHERE session_id = pSessionId
        GROUP BY so_balance_invoice_id, promo_disc_amount_in_so_balance
    )
    UPDATE tr_sl_invoice_item Z
    SET promo_disc_amount = Z.promo_disc_amount + A.selisih
    FROM data_selisih A
    WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
          Z.line_no = 1 AND
          Z.session_id = pSessionId;

    --hitung selisih item amount dari data balance SO, dan dimasukan ke
    -- harga paling tingi (line no nya 1)
    WITH data_selisih AS (
        SELECT so_balance_invoice_id, (item_amount_in_so_balance + tax_amount_in_so_balance) - sum(gross_item_amount) AS selisih
        FROM tr_sl_invoice_item
        WHERE session_id = pSessionId
        GROUP BY so_balance_invoice_id, item_amount_in_so_balance + tax_amount_in_so_balance
    )
    UPDATE tr_sl_invoice_item Z
    SET gross_item_amount = Z.gross_item_amount + A.selisih
    FROM data_selisih A
    WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
          Z.line_no = 1 AND
          Z.session_id = pSessionId;

    --hitung selisih nett item amount dari data balance SO, dan dimasukan ke
    -- harga paling tingi (line no nya 1)
    WITH data_selisih AS (
        SELECT so_balance_invoice_id, (item_amount_in_so_balance - regular_disc_amount_in_so_balance - promo_disc_amount_in_so_balance) - sum(nett_item_amount) AS selisih
        FROM tr_sl_invoice_item
        WHERE session_id = pSessionId
        GROUP BY so_balance_invoice_id, item_amount_in_so_balance - regular_disc_amount_in_so_balance - promo_disc_amount_in_so_balance
    )
    UPDATE tr_sl_invoice_item Z
    SET nett_item_amount = Z.nett_item_amount + A.selisih
    FROM data_selisih A
    WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
          Z.line_no = 1 AND
          Z.session_id = pSessionId;

    --hitung selisih tax amount dari data balance SO, dan dimasukan ke
    -- harga paling tingi (line no nya 1)

    SELECT gov_tax_amount INTO vGovTaxAmount
    FROM sl_invoice_tax
    WHERE invoice_id = pInvoiceId;

    vGovTaxAmount := COALESCE(vGovTaxAmount, 0);

    SELECT tax_percentage INTO vTaxPercentage
    FROM sl_invoice_tax
    WHERE invoice_id = pInvoiceId;


    /* get vFlgShowPpn */
    SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
    FROM sl_invoice A
        INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
    WHERE A.invoice_id = pInvoiceId;

    /* get additional amount*/
    SELECT CASE WHEN vRoundingModeNonTax = 'RD' THEN
                TRUNC(COALESCE(SUM(A.add_amount), 0), vRoundingScalePrintTotal)
            ELSE
                ROUND(COALESCE(SUM(A.add_amount), 0), vRoundingScalePrintTotal)
            END
    INTO vAdditionalCost
    FROM sl_invoice_cost A
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;

    /* get down payment amount in invoice */
    SELECT CASE WHEN vRoundingModeNonTax = 'RD' THEN
                TRUNC(COALESCE(SUM(A.alloc_amount), 0), vRoundingScalePrintTotal)
            ELSE
                ROUND(COALESCE(SUM(A.alloc_amount), 0), vRoundingScalePrintTotal)
            END,
            CASE WHEN vRoundingModeNonTax = 'RD' THEN
                TRUNC(COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
            ELSE
                ROUND(COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
            END,
            CASE WHEN vRoundingModeTax = 'RD' THEN
                TRUNC(COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
            ELSE
                ROUND(COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
            END
    INTO vDownPaymentAmount, vDownPaymentAmountWithTax, vTaxDownPaymentAmount
    FROM sl_invoice_advance A
    INNER JOIN sl_so_balance_advance_invoice B ON A.tenant_id = B.tenant_id AND A.ref_doc_type_id = B.ref_doc_type_id AND A.ref_id = B.ref_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;

    --insert yg do receipt id nya  -99
    INSERT INTO tt_cetak_invoice_jrp
    SELECT pSessionId, A.tenant_id,A.ref_item_id,C.product_id,f_get_uom_name(C.base_uom_id) unit,
    D.serial_number batch,D.product_expired_date ed,C.qty_dlv_int
    from  sl_so_balance_invoice A
    INNER  JOIN sl_do_product C ON A.ref_item_id = C.do_item_id
    INNER JOIN in_product_balance D ON C.product_balance_id = D.product_balance_id
    where flg_invoice = 'Y'
    AND A.tenant_id =  pTenantId
    AND A.do_receipt_item_id = -99
    AND invoice_id = pInvoiceId;

    --insert yg do receipt id nya bukan -99
    INSERT INTO tt_cetak_invoice_jrp
    SELECT pSessionId, A.tenant_id,A.ref_item_id,C.product_id,f_get_uom_name(A.so_uom_id) unit,
    D.serial_number batch,D.product_expired_date ed,C.qty_return*-1
    from  sl_so_balance_invoice A
    INNER JOIN in_do_receipt_item B ON A.do_receipt_item_id = B.do_receipt_item_id
    INNER  JOIN in_do_receipt_product C ON A.do_receipt_item_id = C.do_receipt_item_id
    INNER JOIN in_product_balance D ON C.product_balance_id = D.product_balance_id
    where flg_invoice = 'Y'
    AND A.tenant_id =  pTenantId
    AND A.do_receipt_item_id <> -99
    AND invoice_id = pInvoiceId;

    -- insert ke final table temp
    WITH temp_final AS (
    SELECT A.session_id,A.tenant_id,MAX(A.item_id) item_id ,f_get_product_name(A.product_id) nama_barang,SUM(A.qty) qty,A.unit,A.batch,A.ed
    FROM tt_cetak_invoice_jrp A
    WHERE A.session_id = pSessionId
    AND A.tenant_id =  pTenantId
    GROUP BY A.session_id,A.tenant_id,A.product_id,A.unit,A.batch,A.ed
    HAVING SUM(A.qty) > 0
    )
    INSERT INTO tt_final_cetak_invoice_jrp(session_id,tenant_id,nama_barang,qty,unit,batch,ed,
                                           harga,discount_percentage,discount_amount,total_amount)
    SELECT A.session_id,A.tenant_id,A.nama_barang,A.qty,A.unit,A.batch,A.ed,
    f_get_sell_price_wo_tax_for_efaktur(B.gross_sell_price_so, B.tax_percentage, B.flg_tax_amount) AS harga,B.discount_percentage,
    TRUNC((f_get_sell_price_wo_tax_for_efaktur(B.gross_sell_price_so, B.tax_percentage, B.flg_tax_amount)*A.qty)*(B.discount_percentage/100.0),0) AS discount_amount,
    f_get_sell_price_wo_tax_for_efaktur(B.gross_sell_price_so, B.tax_percentage, B.flg_tax_amount)*A.qty AS total
    FROM temp_final A
    INNER JOIN sl_so_balance_invoice B ON A.item_id = B.ref_item_id AND B.do_receipt_item_id = -99
    WHERE A.session_id = pSessionId
    AND A.tenant_id =  pTenantId;

    SELECT COALESCE(SUM(total_amount),0) INTO vSubTotal
    FROM tt_final_cetak_invoice_jrp
    WHERE session_id =  pSessionId;

    SELECT COALESCE(SUM(discount_amount),0) INTO vDiscount
    FROM tt_final_cetak_invoice_jrp
    WHERE session_id =  pSessionId;

    vDpp := COALESCE(vSubTotal-vDiscount,0);
    vPpn := COALESCE((vSubTotal-vDiscount)*(vTaxPercentage/100.00),0);

    Open pRefHeader FOR
        SELECT A.invoice_id, A.ou_id, A.ou_legal_id,
            UPPER(vOuBuInfoReport.printed_ou_name) AS ou_name,
            UPPER(COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-')) AS ou_addr_1,
            UPPER(COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-')) AS ou_addr_2,
            UPPER(vOuBuInfoReport.npwp_no) AS ou_npwp,
            UPPER(vOuBuInfoReport.image_url) AS path_logo_ou,
            (vOuBuInfoReport.phone1) AS ou_phone,
            (vOuBuInfoReport.fax1) AS ou_fax,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_email')) AS ou_email,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ijin_pbf')) AS ijin_pbf,
            UPPER(vCtgrProductCode) AS ctgr_product_code,
            UPPER(vCtgrProductName) AS ctgr_product_name,
            A.partner_id,
            UPPER(C.partner_name) as customer_name,
            UPPER(COALESCE(H.address1 || ' ' || H.address2 || ' ' || H.address3, '-')) AS customer_address_1,
            UPPER(COALESCE(H.city || ' ' || H.state_or_province || ' ' || H.zip_code, '-')) AS customer_address_2,
            UPPER(COALESCE(I.npwp_no, vEmptyString)) AS customer_npwp,
            A.doc_type_id,
            UPPER(A.doc_no) AS invoice_no, A.doc_date AS invoice_date,
            TO_DATE(A.due_date, 'YYYYMMDD') - TO_DATE(A.doc_date, 'YYYYMMDD') AS top,
            A.due_date,
            COALESCE(J.tax_no, vEmptyString) AS tax_no,
            UPPER(E.doc_no) AS so_no,
            UPPER(vDoNo) AS do_no,
            UPPER(E.ext_doc_no) AS po_no, A.salesman_id,
            UPPER(COALESCE(F.partner_name, ' ')) AS salesman_name,
            UPPER(A.curr_code) AS currency,
            UPPER(A.remark) AS remark,
            UPPER((CASE WHEN A.curr_code = 'IDR' THEN 'rupiah' ELSE A.curr_code END)) AS curr_name, vFlgShowPpn AS flg_show_ppn,
            ROUND(vSubTotal, 0)::character varying AS sub_total,
            --SUM(regular_disc_amount + promo_disc_amount + adj_regular_disc_amount + adj_promo_disc_amount)::character varying AS total_disc,
            vDiscount::character varying AS total_disc,
            vAdditionalCost::character varying AS additional_cost,
            ROUND( vPpn, 0)::character varying AS ppn,
            vDownPaymentAmountWithTax::character varying AS dp_amount,
            ROUND(vDpp, 0)::character varying AS dpp,
            (ROUND(vDpp, 0)+ROUND( vPpn, 0))::character varying AS total,
            UPPER(terbilang(
                ROUND(vDpp+vPpn, 0)
            )) AS terbilang,
            UPPER(f_get_report_parameter_config_value(pTenantId, vAll, vOuBuId, 'email')) AS ou_email,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note1')) AS footer_note_1,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note2')) AS footer_note_2,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note3')) AS footer_note_3,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note4')) AS footer_note_4,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note5')) AS footer_note_5,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note6')) AS footer_note_6,
            UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note7')) AS footer_note_7,

            UPPER(f_get_username(pUserId)) AS username,
            pDatetime AS datetime,
            UPPER(pNamaPenanggungJwb) AS penanggung_jawab_name,
            UPPER(pSikaPenanggungJwb) AS penanggung_jawab_sika
        FROM sl_invoice A
            INNER JOIN m_partner C ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
            INNER JOIN sl_so E ON A.tenant_id = E.tenant_id AND A.ref_id = E.so_id
            LEFT OUTER JOIN m_partner F ON A.tenant_id = F.tenant_id AND A.salesman_id = F.partner_id

            LEFT JOIN m_partner_address H ON H.partner_id = A.partner_id AND H.flg_official = vYes
            LEFT JOIN m_partner_npwp I ON I.partner_id = A.partner_id AND I.active = vYes
            LEFT JOIN sl_invoice_tax J ON J.invoice_id = A.invoice_id
        WHERE A.tenant_id = pTenantId
            AND A.invoice_id = pInvoiceId
        GROUP BY A.invoice_id, C.partner_id, E.so_id, F.partner_id, H.partner_address_id, I.npwp_id, J.tax_no;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
        SELECT row_number() OVER(order by nama_barang ASC) as line_no,
        nama_barang product_name,COALESCE(qty,0)::character varying AS qty,unit uom_name,batch serial_number,ed product_expired_date,
        harga::character varying AS unit_price,discount_percentage AS discount_pct_so,discount_amount::character varying AS unit_price_rp,
        total_amount::character varying AS amount
        FROM tt_final_cetak_invoice_jrp
        WHERE session_id = pSessionId
        ORDER BY product_name ASC;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_cetak_invoice_jrp WHERE session_id = pSessionId;
    DELETE FROM tt_final_cetak_invoice_jrp WHERE session_id = pSessionId;

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