r_print_vat_out_re_po_invoice_with_valas_20171114172301

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

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

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


    vClaimNoteDocTypeId     bigint;
    vReportName             character varying;
    vDiscPrice              numeric;
    vNpwp                   character varying;
    vTempAmount             character varying;
    vYes                    character varying(1);
BEGIN
    vClaimNoteDocTypeId := 511;
    vReportName := 'FormVatOutReturnPurchaseInvoice';
    vDiscPrice := 0;
    vNpwp := '00.000.000.0-000.00';
    vTempAmount := ' ';
    vYes := 'Y';

    Open pRefHeader FOR
    SELECT A.invoice_id, A.ou_id, A.ou_legal_id, B.legal_name AS ou_name, COALESCE(B.address1 || ' ' || B.address2 || ' ' || B.address3, '-') AS ou_addr_1,
        COALESCE(B.city || ', ' || B.state_or_province || ', ' || B.zip_code, '-') AS ou_addr_2, B.npwp_no AS ou_npwp,
        A.partner_id, COALESCE(D.npwp_name,C.partner_name) AS cust_name,
        f_get_report_partner_address_for_vat_out(A.tenant_id, A.partner_id) AS cust_addr,
        COALESCE(D.npwp_no,vNpwp) AS cust_npwp, F.tax_no AS tax_number, F.tax_date, A.curr_code AS curr_code_amount,
        f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'approval_name') AS approval_name,
        F.base_amount AS valas_total_amount, vDiscPrice AS valas_disc_amount, F.advance_amount AS valas_dp_amount,
        ((F.base_amount-vDiscPrice)-F.advance_amount) AS valas_dpp_amount, F.tax_amount AS valas_ppn_amount,
        F.gov_tax_amount AS ppn_amount, vTempAmount AS total_amount, vTempAmount AS disc_amount, vTempAmount AS dp_amount,
        f_get_amount_in_gov_tax_rate(pTenantId,((F.base_amount-vDiscPrice)-F.advance_amount),F.tax_date,A.curr_code) AS dpp_amount
    FROM pu_invoice 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
        LEFT OUTER JOIN m_partner_npwp D ON C.tenant_id = D.tenant_id AND C.partner_id = D.partner_id AND D.active = vYes
        INNER JOIN pu_po E ON A.tenant_id = E.tenant_id AND A.ref_id = E.po_id
        INNER JOIN pu_invoice_tax F ON A.tenant_id = F.tenant_id AND A.invoice_id = F.invoice_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT C.product_id, D.product_code, D.product_name, G.curr_code, SUM(A.ref_item_amount)AS valas_amount, vTempAmount AS amount
    FROM pu_invoice_item A
        INNER JOIN pu_invoice H ON A.tenant_id = H.tenant_id AND A.invoice_id = H.invoice_id
        INNER JOIN in_inventory B ON A.tenant_id = B.tenant_id AND A.ref_id = B.inventory_id
        INNER JOIN pu_receive_goods_item C ON A.tenant_id = C.tenant_id AND A.ref_item_id = C.receive_goods_item_id
        INNER JOIN m_product D ON A.tenant_id = D.tenant_id AND C.product_id = D.product_id
        INNER JOIN pu_po_balance_invoice_tax G ON A.tenant_id = G.tenant_id AND G.ou_id = H.ou_id AND G.ref_doc_type_id = vClaimNoteDocTypeId AND G.ref_id = B.inventory_id AND G.po_id = B.ref_id AND G.ref_item_id = A.ref_item_id
    WHERE A.tenant_id = pTenantId
        AND A.invoice_id = pInvoiceId
    GROUP BY C.product_id, D.product_code, D.product_name, G.curr_code
    ORDER BY C.product_id;
    RETURN NEXT pRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_print_vat_out_re_po_invoice_with_valas(character varying, bigint, bigint, bigint, character varying, bigint)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION r_print_vat_out_re_po_invoice_with_valas(character varying, bigint, bigint, bigint, character varying, bigint) TO public;
GRANT EXECUTE ON FUNCTION r_print_vat_out_re_po_invoice_with_valas(character varying, bigint, bigint, bigint, character varying, bigint) TO sts;