Function Report Sales Invoice MF

CREATE OR REPLACE FUNCTION r_print_sales_invoice_for_mf(character varying, bigint, bigint, bigint, character varying, bigint)
  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;


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

    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;
    vOuPkp                      bigint;
    vGovTaxAmount               numeric;




BEGIN
    vDoDocTypeId := 311;
    vReportName := 'FormSalesInvoiceForMF';

     --> Ambil Ou PkP
    SELECT COUNT(1) INTO vOuPkp
    FROM sl_invoice A
    INNER JOIN t_ou_legal B ON A.ou_id = B.ou_id
    WHERE A.invoice_id = pInvoiceId
        AND B.flg_pkp = 'Y' ;

    --> Ambil total amount
    SELECT SUM(F.qty_dlv_so * F.price_so) AS total_amount INTO vTotalAmount
    FROM sl_invoice_item  Z
    INNER JOIN sl_invoice A ON Z.invoice_id = A.invoice_id
    INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
    INNER JOIN sl_do C ON B.so_id = C.ref_id
    INNER JOIN sl_so_balance_invoice F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = vDoDocTypeId
                AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id
                AND F.do_receipt_item_id = Z.do_receipt_item_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;

    Open pRefHeader FOR
    SELECT  CASE WHEN vOuPkp = 1 THEN f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_name_pkp')
                    ELSE f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_name_non_pkp') END AS ou_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_phone') AS ou_phone,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_mail') AS ou_mail,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_web') AS ou_web,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark1') AS remark1,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark2') AS remark2,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark3') AS remark3,
            f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark4') AS remark4,
            A.doc_no, A.doc_date, f_get_partner_name(C.partner_bill_to_id) AS customer_name, B.payment_amount AS payment,
            (vTotalAmount - B.payment_amount) AS total_due
    FROM sl_invoice A
    INNER JOIN fi_allocation_ar_invoice B ON A.invoice_id = B.ref_id AND B.ref_doc_type_id = A.doc_type_id
    INNER JOIN sl_so C ON A.ref_id = C.so_id
    WHERE A.invoice_id = pInvoiceId;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT E.product_desc AS order, F.qty_dlv_so AS qty, F.price_so AS amount, (F.qty_dlv_so * F.price_so) AS sub_total,
        D.partner_name AS partner_name, D.partner_address1 AS cust_address,
        E.remark, C.doc_date AS doc_date, E.remark
    FROM sl_invoice_item  Z
    INNER JOIN sl_invoice A ON Z.invoice_id = A.invoice_id
    INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
    INNER JOIN sl_do C ON B.so_id = C.ref_id
    INNER JOIN sl_quotation D ON B.ref_id = D.quotation_id
    INNER JOIN sl_quotation_item E ON D.quotation_id = E.quotation_id
    INNER JOIN sl_so_balance_invoice F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = vDoDocTypeId
                AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id
                AND F.do_receipt_item_id = Z.do_receipt_item_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;

    RETURN NEXT pRefDetail;


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