r_print_sales_invoice_escp_for_tsi

-- modified for dialogue
CREATE OR REPLACE FUNCTION r_print_sales_invoice_escp_for_tsi(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;
    pInvoiceTempId          ALIAS FOR $6;
    pFlgPkp                 ALIAS FOR $7;

    vReportName             character varying;
    vDoNo                   character varying;
    vFlgShowPpn             character varying;
    vDocTypeDO              bigint;

    vOuBuInfoReport         OU_INFO_REPORT%ROWTYPE;
    result                  record;

BEGIN
    vReportName := 'FormSalesInvoiceForDlg';
    vDocTypeDO := 311;

    SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
    FROM sl_invoice_temp A
    WHERE A.invoice_temp_id = pInvoiceTempId INTO result ;
    vOuBuInfoReport := result.info;

    --vDoNo
    SELECT STRING_AGG(z.doc_no,', ') INTO vDoNo
        FROM (
        SELECT ac.doc_no
        FROM sl_invoice_temp_item ab
        LEFT JOIN sl_do ac ON ac.do_id = ab.ref_id
        WHERE ab.invoice_temp_id = pInvoiceTempId
        GROUP BY ac.doc_no
    ) z;

    --vFlgShowPpn
    --SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
    SELECT (CASE WHEN (B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
        FROM sl_invoice_temp A
        INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
        WHERE A.invoice_temp_id = pInvoiceTempId;

    IF(vFlgShowPpn = 'Y') THEN
        Open pRefHeader FOR
            SELECT vOuBuInfoReport.printed_ou_name AS ou_name, c.address1 AS ou_addr, a.inv_doc_date AS inv_date,
            a.inv_doc_no AS inv_no, vDoNo AS do_no, e.partner_code AS sls_code, f.partner_code AS cus_code,
            f.partner_name, g.address_desc AS partner_addr, g.city AS partner_city,
            a.due_date, a.total_amount AS dpp_amount, a.discount_amount, sum(h.base_amount) AS total_amount,
            a.tax_amount AS ppn_amount, a.gross_amount AS grand_total_amount,
            terbilang(a.gross_amount), pDatetime AS datetime,
            (CASE WHEN a.curr_code = 'IDR' THEN 'RUPIAH' ELSE a.curr_code END) AS curr_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, a.ou_id, 'bank_name') AS bank_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, a.ou_id, 'bank_acc_name') AS bank_acc_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, a.ou_id, 'bank_acc_no') AS bank_acc_no
            FROM sl_invoice_temp a
            LEFT JOIN t_ou b ON b.ou_id = a.ou_id
            LEFT JOIN t_ou_legal c ON c.ou_legal_id = a.ou_legal_id
            LEFT JOIN sl_so d ON d.so_id = a.ref_id
            LEFT JOIN m_partner e ON e.partner_id = a.salesman_id
            LEFT JOIN m_partner f ON f.partner_id = a.partner_id
            LEFT JOIN m_partner_address g ON g.partner_address_id = d.partner_ship_address_id
            LEFT JOIN sl_invoice_temp_tax h ON a.invoice_temp_id = h.invoice_temp_id
            WHERE a.invoice_temp_id = pInvoiceTempId
            GROUP BY a.inv_doc_date, a.inv_doc_no, a.due_date, a.total_amount, a.discount_amount, a.tax_amount,
            a.gross_amount, a.curr_code, c.address1, d.doc_no, e.partner_code, f.partner_code,
            f.partner_name, g.address_desc, g.city, d.flg_show_inv_tax, a.ou_id;
        RETURN NEXT pRefHeader;

        Open pRefDetail FOR
            SELECT
            e.product_code, e.product_name, sum(d.qty_dlv_so) AS qty, d.price_so AS price,
            sum(d.item_amount) AS amount
            FROM sl_invoice_temp a
            INNER JOIN sl_invoice_temp_item b ON b.invoice_temp_id=a.invoice_temp_id
            INNER JOIN sl_do_item c ON c.do_item_id=b.ref_item_id
            INNER JOIN sl_so_balance_invoice d ON d.tenant_id=b.tenant_id
                AND d.ou_id=a.ou_id AND d.ref_item_id=b.ref_item_id AND d.ref_doc_type_id=vDocTypeDO
                AND d.do_receipt_item_id=b.do_receipt_item_id
            INNER JOIN m_product e ON e.product_id = c.product_id
            WHERE a.invoice_temp_id = pInvoiceTempId
            GROUP BY e.product_code, e.product_name, d.price_so;
        RETURN NEXT pRefDetail;

    ELSE
        Open pRefHeader FOR
            SELECT vOuBuInfoReport.printed_ou_name AS ou_name, c.address1 AS ou_addr, a.inv_doc_date AS inv_date,
            a.inv_doc_no AS inv_no, vDoNo AS do_no, e.partner_code AS sls_code, f.partner_code AS cus_code,
            f.partner_name, g.address_desc AS partner_addr, g.city AS partner_city,
            a.due_date, a.total_amount+a.tax_amount+a.discount_amount as total_amount, a.discount_amount,
            a.gross_amount AS grand_total_amount,
            terbilang(a.gross_amount), pDatetime AS datetime,
            (CASE WHEN a.curr_code = 'IDR' THEN 'RUPIAH' ELSE a.curr_code END) AS curr_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, a.ou_id, 'bank_name') AS bank_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, a.ou_id, 'bank_acc_name') AS bank_acc_name,
            f_get_report_parameter_config_value(pTenantId, vReportName, a.ou_id, 'bank_acc_no') AS bank_acc_no
            FROM sl_invoice_temp a
            LEFT JOIN t_ou b ON b.ou_id = a.ou_id
            LEFT JOIN t_ou_legal c ON c.ou_legal_id = a.ou_legal_id
            LEFT JOIN sl_so d ON d.so_id = a.ref_id
            LEFT JOIN m_partner e ON e.partner_id = a.salesman_id
            LEFT JOIN m_partner f ON f.partner_id = a.partner_id
            LEFT JOIN m_partner_address g ON g.partner_address_id = d.partner_ship_address_id
            LEFT JOIN sl_invoice_temp_tax h ON a.invoice_temp_id = h.invoice_temp_id
            WHERE a.invoice_temp_id = pInvoiceTempId
            GROUP BY a.inv_doc_date, a.inv_doc_no, a.due_date, a.total_amount, a.discount_amount, a.tax_amount,
            a.gross_amount, a.curr_code, c.address1, d.doc_no, e.partner_code, f.partner_code,
            f.partner_name, g.address_desc, g.city, d.flg_show_inv_tax, a.ou_id;
        RETURN NEXT pRefHeader;

        Open pRefDetail FOR
            SELECT
            e.product_code, e.product_name, sum(d.qty_dlv_so) AS qty, d.price_so+dd.tax_amount AS price,
            sum(d.qty_dlv_so)*d.price_so+dd.tax_amount AS amount
            FROM sl_invoice_temp a
            INNER JOIN sl_invoice_temp_item b ON b.invoice_temp_id=a.invoice_temp_id
            INNER JOIN sl_do_item c ON c.do_item_id=b.ref_item_id
            INNER JOIN sl_so_balance_invoice d ON d.tenant_id=b.tenant_id
                AND d.ou_id=a.ou_id AND d.ref_item_id=b.ref_item_id AND d.ref_doc_type_id=vDocTypeDO
                AND d.do_receipt_item_id=b.do_receipt_item_id
            INNER JOIN sl_so_balance_invoice_tax dd ON dd.tenant_id = d.tenant_id AND dd.partner_id = d.partner_id
                AND dd.ref_doc_type_id = d.ref_doc_type_id
                AND dd.ref_id = d.ref_id
                AND dd.ref_item_id = d.ref_item_id
                AND dd.do_receipt_item_id = d.do_receipt_item_id
            INNER JOIN m_product e ON e.product_id = c.product_id
            WHERE a.invoice_temp_id = pInvoiceTempId
            GROUP BY e.product_code, e.product_name, d.price_so, dd.tax_amount;
        RETURN NEXT pRefDetail;

    END IF;

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