Function Report 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;
    vTotalPayment               numeric;
    vTotalTax                   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';
    vNo                         character varying(1) :='N';
    vAll                        character varying;
    vOuBuId                     bigint;
    vOuPkp                      bigint;
    vGovTaxAmount               numeric;
    vZero                       numeric;

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

     --> 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;

    -- Ambil total payment from mapping cb in to so
    -- Note: ambil dari nilai di SI-nya
    SELECT CASE WHEN COALESCE (D.payment_amount,0) <> 0 THEN D.payment_amount
        WHEN COALESCE(C.amount, 0) = 0 THEN 0
            ELSE A.gross_amount
            END AS payment INTO vTotalPayment
    FROM sl_invoice A
    INNER JOIN sl_so B ON A.ref_id = B.so_id
    LEFT JOIN cb_mapping_cbin_so_balance_item C ON B.so_id = C.so_id AND B.doc_no = C.doc_no
    LEFT JOIN fi_invoice_ar_balance D ON B.so_id = D.ref_id AND D.ref_doc_type_id = B.doc_type_id
    WHERE A.tenant_id = pTenantId
            AND A.invoice_id = pInvoiceId;

    --> Ambil Total Tax
    SELECT SUM(L.tax_amount) AS total_amount INTO vTotalTax
    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
    INNER JOIN sl_so_balance_invoice_tax L ON L.tenant_id = F.tenant_id AND
                                              L.ou_id = F.ou_id AND
                                              L.partner_id = F.partner_id AND
                                              L.ref_doc_type_id = F.ref_doc_type_id AND
                                              L.ref_id = F.ref_id AND
                                              L.ref_item_id = F.ref_item_id AND
                                              L.do_receipt_item_id = F.do_receipt_item_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;


    IF(vOuPkp = 1) THEN

     Open pRefHeader FOR
            SELECT  f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_name_pkp') AS ou_name,
                    f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_address') AS ou_address,
                    f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_npwp') 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, 'remark5') 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,
                    f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_account1') AS bank_account1,
                    f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_account2') AS bank_account2,
                    A.doc_no, A.doc_date, f_get_partner_name(B.partner_bill_to_id) AS customer_name, vTotalPayment AS payment,
                    (vTotalAmount + vTotalTax - vTotalPayment) AS total_due, vTotalAmount AS sub_total, vTotalTax AS tax,
                    (vTotalAmount + vTotalTax) AS total_amount, vYes AS Pkp, A.due_date AS terms
            FROM sl_invoice A
            INNER JOIN sl_so B ON A.ref_id = B.so_id
            WHERE A.invoice_id = pInvoiceId;

        RETURN NEXT pRefHeader;

        Open pRefDetail FOR
            SELECT COALESCE(E.product_desc,f_get_product_name(G.product_id)) AS ORDER, F.qty_dlv_so AS qty, F.price_so AS price, (F.qty_dlv_so * F.price_so) AS amount,
                COALESCE(D.partner_name,f_get_partner_name(A.partner_id)) AS partner_name, COALESCE(D.partner_address1,'') AS cust_address,
                COALESCE(E.remark,'No. order : ' || B.doc_no) AS remark, C.doc_date AS doc_date
            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
            LEFT JOIN sl_quotation D ON B.ref_id = D.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 = 311
                        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
            INNER JOIN sl_do_item G ON Z.ref_item_id = G.do_item_id
            INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
            LEFT JOIN sl_quotation_item E ON H.ref_id = E.quotation_item_id
            WHERE A.tenant_id = pTenantId
                AND A.invoice_id = pInvoiceId;

        RETURN NEXT pRefDetail;

    ELSE

        Open pRefHeader FOR
            SELECT  f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_name_non_pkp') 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(B.partner_bill_to_id) AS customer_name, vTotalPayment AS payment,
                    (vTotalAmount - vTotalPayment) AS total_due, vNo AS Pkp, A.due_date AS terms
            FROM sl_invoice A
            INNER JOIN sl_so B ON A.ref_id = B.so_id
            WHERE A.invoice_id = pInvoiceId;

        RETURN NEXT pRefHeader;

        Open pRefDetail FOR
           SELECT COALESCE(E.product_desc,f_get_product_name(G.product_id)) AS ORDER, F.qty_dlv_so AS qty, F.price_so AS price, (F.qty_dlv_so * F.price_so) AS amount,
                COALESCE(D.partner_name,f_get_partner_name(A.partner_id)) AS partner_name, COALESCE(D.partner_address1,'') AS cust_address,
                COALESCE(E.remark,'No. order : ' || B.doc_no) AS remark, C.doc_date AS doc_date
            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
            LEFT JOIN sl_quotation D ON B.ref_id = D.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 = 311
                        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
            INNER JOIN sl_do_item G ON Z.ref_item_id = G.do_item_id
            INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
            LEFT JOIN sl_quotation_item E ON H.ref_id = E.quotation_item_id
            WHERE A.tenant_id = pTenantId
                AND A.invoice_id = pInvoiceId;

        RETURN NEXT pRefDetail;

    END IF;


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