Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: r_print_sales_invoice(character varying, bigint, bigint, bigint, character varying, bigint, character varying)
- -- DROP FUNCTION r_print_sales_invoice(character varying, bigint, bigint, bigint, character varying, bigint, character varying);
- CREATE OR REPLACE FUNCTION r_print_sales_invoice(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;
- vAll character varying(5);
- BEGIN
- vDoDocTypeId := 311;
- vReportName := 'FormSalesInvoice';
- vAll := 'ALL';
- 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;
- 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_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_id = pInvoiceId
- 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, warranty_type_code, warranty_type_name)
- 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(A.invoice_id,A.ref_id,A.ref_item_id) AS nett_sell_price,
- (G.price_so + f_get_warranty_sell_price_for_sales_invoice(A.invoice_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(A.invoice_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(A.invoice_id,A.ref_id,A.ref_item_id))) AS nett_item_amount,
- (G.price_so + f_get_warranty_sell_price_for_sales_invoice(A.invoice_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(A.invoice_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(A.invoice_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(A.invoice_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, COALESCE(K.warranty_type_code, ''), COALESCE(K.warranty_type_name, '')
- FROM sl_invoice_item A
- INNER JOIN sl_invoice H ON A.tenant_id = H.tenant_id AND A.invoice_id = H.invoice_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
- INNER JOIN sl_do_item I ON I.do_item_id = G.ref_item_id
- LEFT OUTER JOIN sl_so_warranty_item J ON J.so_item_id = I.ref_id
- LEFT OUTER JOIN m_warranty_type K ON K.warranty_type_id = J.warranty_type_id
- WHERE A.tenant_id = pTenantId
- AND A.invoice_id = pInvoiceId;
- /* get vFlgShowPpn */
- SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
- FROM sl_invoice A
- INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
- WHERE A.invoice_id = pInvoiceId;
- /* get additional amount*/
- SELECT COALESCE(SUM(A.add_amount), 0) INTO vAdditionalCost
- FROM sl_invoice_cost A
- WHERE A.tenant_id = pTenantId
- AND 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;
- IF(vFlgShowPpn='Y') THEN
- Open pRefHeader FOR
- SELECT A.invoice_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,
- COALESCE(f_get_report_partner_address_for_vat_out(A.tenant_id, A.partner_id), ' ') AS partner_addr,
- A.doc_type_id, A.doc_no AS so_invoice_no, A.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, COALESCE(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 A
- 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
- LEFT OUTER 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_id = pInvoiceId
- GROUP BY A.invoice_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_type_code, warranty_type_name
- 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_type_code, warranty_type_name
- HAVING SUM(qty_so) > 0
- ORDER BY product_code, product_name;
- RETURN NEXT pRefDetail;
- ELSE
- Open pRefHeader FOR
- SELECT A.invoice_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,
- COALESCE(f_get_report_partner_address_for_vat_out(A.tenant_id, A.partner_id), ' ') AS partner_addr,
- A.doc_type_id, A.doc_no AS so_invoice_no, A.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, COALESCE(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 A
- 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
- LEFT OUTER 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_id = pInvoiceId
- GROUP BY A.invoice_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_type_code, warranty_type_name
- 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_type_code, warranty_type_name
- 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(character varying, bigint, bigint, bigint, character varying, bigint, character varying)
- OWNER TO sts;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement