Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: r_print_vat_out_re_po_invoice(character varying, bigint, bigint, bigint, character varying, bigint)
- -- DROP FUNCTION r_print_vat_out_re_po_invoice(character varying, bigint, bigint, bigint, character varying, bigint);
- CREATE OR REPLACE FUNCTION r_print_vat_out_re_po_invoice(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;
- vYes character varying(1);
- BEGIN
- vClaimNoteDocTypeId := 511;
- vReportName := 'FormVatOutReturnPurchaseInvoice';
- vDiscPrice := 0;
- vNpwp := '00.000.000.0-000.00';
- 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 total_amount, vDiscPrice AS disc_amount, F.advance_amount AS dp_amount,
- F.gov_tax_amount AS ppn_amount, ((F.base_amount-vDiscPrice)-F.advance_amount) 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 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(character varying, bigint, bigint, bigint, character varying, bigint)
- OWNER TO sts;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement