r_print_sales_invoice_temporary_INDOCOM_20170912

-- Function: r_print_sales_invoice_temporary(character varying, bigint, bigint, bigint, character varying, bigint, character varying)

-- DROP FUNCTION r_print_sales_invoice_temporary(character varying, bigint, bigint, bigint, character varying, bigint, character varying);

CREATE OR REPLACE FUNCTION r_print_sales_invoice_temporary(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;

    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;
    vAll                        character varying(5);
BEGIN
    vDoDocTypeId := 311;
    vReportName := 'FormTemporarySalesInvoice';
    vAll := 'ALL';

    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;

    DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;

    /* get delivery order number */
    SELECT STRING_AGG(Z.doc_no, ' , ' ) INTO vDoNo
    FROM (
        SELECT B.doc_no
        FROM sl_invoice_temp_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_temp_id = pInvoiceTempId
        GROUP BY B.do_id
        ORDER BY B.doc_no) 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)
    SELECT pSessionId, pTenantId, D.product_code, D.product_name, G.qty_dlv_so, E.uom_name,
        G.price_so + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id) AS nett_sell_price,
        (G.price_so + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id) + f_tax_rounding(G.tenant_id, G.price_so + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id), 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 gross_sell_price,
        (G.qty_dlv_so * (G.price_so + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id))) AS nett_item_amount,
        (f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id) + G.price_so + f_tax_rounding(G.tenant_id, G.price_so + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id), 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 + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id), 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,
        f_tax_rounding(G.tenant_id, G.price_so + f_get_warranty_sell_price_for_sales_invoice_temp(A.invoice_temp_id,A.ref_id,A.ref_item_id), 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
    FROM sl_invoice_temp_item A
        INNER JOIN sl_invoice_temp H ON A.tenant_id = H.tenant_id AND A.invoice_temp_id = H.invoice_temp_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
        INNER JOIN m_uom E ON A.tenant_id = E.tenant_id AND G.so_uom_id = E.uom_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_temp_id = pInvoiceTempId;

    /* get vFlgShowPpn */
    SELECT (CASE WHEN (pFlgPkp = 'Y' AND 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;

    /* get additional cost in invoice */
    SELECT COALESCE(SUM(A.add_amount), 0) INTO vAdditionalCost
    FROM sl_invoice_temp_cost A
    WHERE A.tenant_id = pTenantId
        AND A.invoice_temp_id = pInvoiceTempId;

    /* 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_temp_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_temp_id = pInvoiceTempId;

    IF(vFlgShowPpn='Y') THEN
        Open pRefHeader FOR
            SELECT A.invoice_temp_id, A.ou_id, A.ou_legal_id, COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-') AS ou_addr_1,
                COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-') AS ou_addr_2, A.partner_id, C.partner_name,
                f_get_report_partner_address_for_vat_out(A.tenant_id, A.partner_id) AS partner_addr,
                A.doc_type_id, A.inv_doc_no AS so_invoice_no, A.inv_doc_date AS so_invoice_date, A.due_date, A.ref_id, E.doc_no AS so_no,
                E.ext_doc_no AS po_no, A.salesman_id, F.partner_name AS sales_name, A.curr_code AS currency,
                A.remark, (CASE WHEN A.curr_code = 'IDR' THEN 'rupiah' ELSE A.curr_code END) AS curr_name, vFlgShowPpn AS flg_show_ppn,
                SUM(G.nett_item_amount) AS sub_total, vAdditionalCost AS additional_cost, (SUM(G.tax_amount)-vTaxDownPaymentAmount) AS ppn, vDownPaymentAmount AS dp_amount,
                (((SUM(G.nett_item_amount)+vAdditionalCost)-(vDownPaymentAmount+vTaxDownPaymentAmount)) + SUM(G.tax_amount)) AS total_with_tax,
                ((SUM(G.nett_item_amount)+vAdditionalCost)-vDownPaymentAmount) AS total_no_tax, vDoNo AS do_no,
                lower(terbilang(((SUM(G.nett_item_amount)+vAdditionalCost)-(vDownPaymentAmount+vTaxDownPaymentAmount)) + SUM(G.tax_amount))) AS total_name,
                f_get_report_parameter_config_value(pTenantId, vAll, A.ou_id, 'printed_ou_name') AS ou_name,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_desc_1') AS bank_acc_desc_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_desc_2') AS bank_acc_desc_2,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_1') AS bank_acc_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_2') AS bank_acc_2,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_swift_code_1') AS bank_swift_code_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_swift_code_2') AS bank_swift_code_2,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_branch_1') AS bank_branch_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_branch_2') AS bank_branch_2,
                vOuBuInfoReport.image_url AS path_logo_ou
            FROM sl_invoice_temp A
                --INNER JOIN t_ou_legal B ON A.tenant_id = B.tenant_id AND A.ou_legal_id = B.ou_legal_id
                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
                INNER JOIN m_partner F ON A.tenant_id = F.tenant_id AND A.salesman_id = F.partner_id
                INNER JOIN tr_sl_invoice_item G ON A.tenant_id = G.tenant_id AND G.session_id = pSessionId
            WHERE A.tenant_id = pTenantId
                AND A.invoice_temp_id = pInvoiceTempId
            GROUP BY A.invoice_temp_id, A.ou_id, A.ou_legal_id, vOuBuInfoReport.address1, vOuBuInfoReport.address2, vOuBuInfoReport.address3, vOuBuInfoReport.city, vOuBuInfoReport.state_or_province, vOuBuInfoReport.zip_code,
                    A.partner_id, C.partner_name, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.due_date, A.ref_id, E.doc_no,
                    E.ext_doc_no, A.salesman_id, F.partner_name, A.curr_code, A.remark, vFlgShowPpn, vAdditionalCost, vDownPaymentAmount, vDoNo;
        RETURN NEXT pRefHeader;

        Open pRefDetail FOR
            SELECT product_code, product_name, SUM(qty_so) AS qty, uom_name,
               nett_sell_price AS unit_price, SUM(nett_item_amount) AS amount, warranty
            FROM tr_sl_invoice_item
            WHERE session_id = pSessionId
                AND tenant_id = pTenantId
            GROUP BY product_code, product_name, uom_name, nett_sell_price, warranty
            HAVING SUM(qty_so) > 0
            ORDER BY product_code, product_name;
        RETURN NEXT pRefDetail;
    ELSE
        Open pRefHeader FOR
            SELECT A.invoice_temp_id, A.ou_id, A.ou_legal_id, COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-') AS ou_addr_1,
                COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-') AS ou_addr_2, A.partner_id, C.partner_name,
                f_get_report_partner_address_for_vat_out(A.tenant_id, A.partner_id) AS partner_addr,
                A.doc_type_id, A.inv_doc_no AS so_invoice_no, A.inv_doc_date AS so_invoice_date, A.due_date, A.ref_id, E.doc_no AS so_no,
                E.ext_doc_no AS po_no, A.salesman_id, F.partner_name AS sales_name, A.curr_code AS currency,
                A.remark, (CASE WHEN A.curr_code = 'IDR' THEN 'rupiah' ELSE A.curr_code END) AS curr_name, vFlgShowPpn AS flg_show_ppn,
                SUM(G.gross_item_amount) AS sub_total, vAdditionalCost AS additional_cost, SUM(G.tax_amount) AS ppn, vDownPaymentAmountWithTax AS dp_amount,
                (SUM(G.gross_item_amount) - vDownPaymentAmountWithTax) AS total_with_tax, SUM(G.nett_item_amount) AS total_no_tax, vDoNo AS do_no,
                lower(terbilang(SUM(G.gross_item_amount) - vDownPaymentAmountWithTax)) AS total_name,
                f_get_report_parameter_config_value(pTenantId, vAll, A.ou_id, 'printed_ou_name') AS ou_name,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_desc_1') AS bank_acc_desc_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_desc_2') AS bank_acc_desc_2,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_1') AS bank_acc_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_acc_2') AS bank_acc_2,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_swift_code_1') AS bank_swift_code_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_swift_code_2') AS bank_swift_code_2,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_branch_1') AS bank_branch_1,
                f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_branch_2') AS bank_branch_2,
                vOuBuInfoReport.image_url AS path_logo_ou
            FROM sl_invoice_temp A
                --INNER JOIN t_ou_legal B ON A.tenant_id = B.tenant_id AND A.ou_legal_id = B.ou_legal_id
                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
                INNER JOIN m_partner F ON A.tenant_id = F.tenant_id AND A.salesman_id = F.partner_id
                INNER JOIN tr_sl_invoice_item G ON A.tenant_id = G.tenant_id AND G.session_id = pSessionId
            WHERE A.tenant_id = pTenantId
                AND A.invoice_temp_id = pInvoiceTempId
            GROUP BY A.invoice_temp_id, A.ou_id, A.ou_legal_id, vOuBuInfoReport.address1, vOuBuInfoReport.address2, vOuBuInfoReport.address3, vOuBuInfoReport.city, vOuBuInfoReport.state_or_province, vOuBuInfoReport.zip_code,
                    A.partner_id, C.partner_name, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.due_date, A.ref_id, E.doc_no,
                    E.ext_doc_no, A.salesman_id, F.partner_name, A.curr_code, A.remark, vFlgShowPpn, vAdditionalCost, vDownPaymentAmountWithTax, vDoNo;
        RETURN NEXT pRefHeader;

        Open pRefDetail FOR
            SELECT product_code, product_name, SUM(qty_so) AS qty, uom_name,
               gross_sell_price AS unit_price, SUM(gross_item_amount) AS amount, warranty
            FROM tr_sl_invoice_item
            WHERE session_id = pSessionId
                AND tenant_id = pTenantId
            GROUP BY product_code, product_name, uom_name, gross_sell_price, warranty
            HAVING SUM(qty_so) > 0
            ORDER BY product_code, product_name;
        RETURN NEXT pRefDetail;
    END IF;

    DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_print_sales_invoice_temporary(character varying, bigint, bigint, bigint, character varying, bigint, character varying)
  OWNER TO sts;