r_print_sales_invoice_with_down_payment_xc

/**
 * Adrian, Dec 22, 2017
 */

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

            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;
            vTotal                                          numeric;
            vTotalDisc                                      numeric;
            vTotalCost                                      numeric;
            vTaxAmount                                      numeric;
            vGrandTotal                                     numeric;
            vTotalWithTax                           numeric;
    BEGIN
            vDoDocTypeId := 311;
            vReportName := 'FormSalesInvoice';
            vTotal := 0;
            vTotalCost := 0;
            vTaxAmount := 0;
            vGrandTotal := 0;
            vTotalDisc := 0;
            vTotalWithTax := 0;

            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 COALESCE(SUM(A.ref_item_amount), 0) INTO vTotal
            FROM sl_invoice_item A WHERE A.invoice_id = pInvoiceId;

            SELECT COALESCE(SUM(A.add_amount), 0) INTO vTotalCost
            FROM sl_invoice_cost A WHERE A.invoice_id = pInvoiceId;

            SELECT COALESCE(SUM(A.tax_amount), 0) INTO vTaxAmount
            FROM sl_invoice_tax A WHERE A.invoice_id = pInvoiceId;

            /* get down payment amount in invoice */
            SELECT COALESCE(SUM(A.alloc_amount), 0), COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0) 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;

            vTotalWithTax = vTotal - vDownPaymentAmount;

            vGrandTotal = vTotal + vTaxAmount - vDownPaymentAmount + vTotalCost;

            Open pRefHeader FOR
            SELECT vOuBuInfoReport.printed_ou_name AS main_ou, COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-') AS main_address,
                       COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-') AS main_address2,
                       vOuBuInfoReport.npwp_no AS npwp, A.doc_no AS invoice_no, A.doc_date AS invoice_date,
                       B.doc_no AS so_no, B.doc_date AS so_date, B.ext_doc_no AS po_no, B.ext_doc_date AS po_date,
                       A.due_date AS due_date, A.curr_code AS curr_code, A.remark AS remark,
                       COALESCE(C.npwp_name, f_get_partner_name(A.partner_id)) AS bill_to, terbilang(vGrandTotal) || ' ' || A.curr_code AS say,
                       f_get_user_fullname(pUserId) AS print_by, pDatetime AS print_time,
                       vTotal AS sub_total,  vTotalDisc AS disc_total, vTaxAmount AS tax_amount, vTotalWithTax AS invoice_total,
                       vTotalCost AS add_cost, vGrandTotal AS grand_total,
                       vDownPaymentAmount AS dp_amount,
                       --f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'approval_name') AS user, f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'approval_role') AS role,
                       f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'bank_acc_1') || ' CABANG ' || f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'bank_branch_1') AS bu_bank,
                       COALESCE(C.npwp_no, ' ') AS cust_tax_info, COALESCE(C.address1, D.address1) AS bill_to_address_1, COALESCE(C.address2 || ' ' || C.address3, D.address2 || ' ' || D.address3) AS bill_to_address_2, COALESCE(C.city || ' ' || C.zip_code, D.city || ' ' || D.zip_code) AS bill_to_address_3
            FROM sl_invoice A
            INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
            LEFT OUTER JOIN m_partner_npwp C ON A.partner_id = C.partner_id
            INNER JOIN m_partner_address D ON A.partner_id = D.partner_id AND D.flg_official = 'Y'
            WHERE A.tenant_id = pTenantId
                      AND A.invoice_id = pInvoiceId;

            RETURN NEXT pRefHeader;

            Open pRefDetail FOR
            SELECT B.doc_no AS do_no, B.doc_date AS do_date, f_get_product_name(C.product_id) AS product_name,
                       SUM(E.qty_dlv_so) AS qty, f_get_uom_name(C.base_uom_id) AS uom_code,
                       E.price_so AS price, SUM(A.ref_item_amount) AS amount, 0 AS disc
            FROM sl_invoice_item A
            INNER JOIN sl_do B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.do_id
            INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id AND A.ref_id = C.do_id
            INNER JOIN sl_invoice D ON A.invoice_id = D.invoice_id
            INNER JOIN sl_so_balance_invoice E ON A.tenant_id = E.tenant_id AND D.ou_id = E.ou_id AND E.ref_doc_type_id = vDoDocTypeId AND D.invoice_id = E.invoice_id AND E.ref_id = B.do_id AND E.so_id = B.ref_id AND E.ref_item_id = A.ref_item_id AND E.do_receipt_item_id = A.do_receipt_item_id
            WHERE A.tenant_id = pTenantId
            AND A.invoice_id = pInvoiceId
            GROUP BY B.doc_no, B.doc_date, C.product_id, E.price_so, C.base_uom_id
            HAVING SUM(E.qty_dlv_so) > 0
            ORDER BY product_name;

            RETURN NEXT pRefDetail;

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