aadddrr

r_print_sales_invoice_temporary_xc_ORI

Dec 21st, 2017
43
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: r_print_sales_invoice_temporary_xc(character varying, bigint, bigint, bigint, character varying, bigint, character varying)
  2.  
  3. -- DROP FUNCTION r_print_sales_invoice_temporary_xc(character varying, bigint, bigint, bigint, character varying, bigint, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION r_print_sales_invoice_temporary_xc(character varying, bigint, bigint, bigint, character varying, bigint, character varying)
  6.   RETURNS SETOF refcursor AS
  7. $BODY$
  8.     DECLARE
  9.             pRefHeader                              REFCURSOR := 'refHeader';
  10.             pRefDetail                              REFCURSOR := 'refDetail';
  11.             pSessionId                              ALIAS FOR $1;
  12.             pTenantId                               ALIAS FOR $2;
  13.             pUserId                                 ALIAS FOR $3;
  14.             pRoleId                                 ALIAS FOR $4;
  15.             pDatetime                               ALIAS FOR $5;
  16.             pInvoiceTempId                  ALIAS FOR $6;
  17.             pFlgPkp                                 ALIAS FOR $7;
  18.            
  19.             vDoDocTypeId                            bigint;
  20.             vReportName                                     character varying;
  21.             vDoNo                                           character varying;
  22.             vAdditionalCost                         numeric;
  23.             vDownPaymentAmount                      numeric;
  24.             vDownPaymentAmountWithTax       numeric;
  25.             vTaxDownPaymentAmount           numeric;
  26.             vFlgShowPpn                                     character varying := 'Y';
  27.             vOuBuInfoReport                         OU_INFO_REPORT%ROWTYPE;
  28.             result                          record;
  29.             vTotal                                          numeric;
  30.             vTotalDisc                                      numeric;
  31.             vTotalCost                                      numeric;
  32.             vTaxAmount                                      numeric;
  33.             vGrandTotal                                     numeric;
  34.             vTotalWithTax                           numeric;
  35.     BEGIN
  36.             vDoDocTypeId := 311;
  37.             vReportName := 'FormTemporarySalesInvoice';
  38.             vTotal := 0;
  39.             vTotalCost := 0;
  40.             vTaxAmount := 0;
  41.             vGrandTotal := 0;
  42.             vTotalDisc := 0;
  43.             vTotalWithTax := 0;
  44.            
  45.             SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
  46.             FROM sl_invoice_temp A
  47.             WHERE A.invoice_temp_id = pInvoiceTempId INTO result ;
  48.             vOuBuInfoReport := result.info;
  49.                    
  50.             SELECT COALESCE(SUM(A.ref_item_amount), 0) INTO vTotal
  51.             FROM sl_invoice_temp_item A WHERE A.invoice_temp_id = pInvoiceTempId;
  52.            
  53.             SELECT COALESCE(SUM(A.add_amount), 0) INTO vTotalCost
  54.             FROM sl_invoice_temp_cost A WHERE A.invoice_temp_id = pInvoiceTempId;
  55.            
  56.             SELECT COALESCE(SUM(A.tax_amount), 0) INTO vTaxAmount
  57.             FROM sl_invoice_temp_tax A WHERE A.invoice_temp_id = pInvoiceTempId;
  58.            
  59.             vTotalWithTax = vTotal + vTaxAmount;
  60.            
  61.             vGrandTotal = vTotal + vTaxAmount + vTotalCost;
  62.            
  63.             Open pRefHeader FOR
  64.             SELECT vOuBuInfoReport.printed_ou_name AS main_ou, COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-') AS main_address,
  65.                        COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-') AS main_address2,
  66.                        vOuBuInfoReport.npwp_no AS npwp, A.inv_doc_no AS invoice_no, A.inv_doc_date AS invoice_date,
  67.                        B.doc_no AS so_no, B.doc_date AS so_date, B.ext_doc_no AS po_no, B.ext_doc_date AS po_date,
  68.                        A.due_date AS due_date, A.curr_code AS curr_code, A.remark AS remark,
  69.                        COALESCE(C.npwp_name, f_get_partner_name(A.partner_id)) AS bill_to, terbilang(vGrandTotal) || ' ' || A.curr_code AS say,
  70.                        f_get_user_fullname(pUserId) AS print_by, pDatetime AS print_time,
  71.                        vTotal AS sub_total,  vTotalDisc AS disc_total, vTaxAmount AS tax_amount, vTotalWithTax AS invoice_total,
  72.                        vTotalCost AS add_cost, vGrandTotal AS grand_total,
  73.                        --f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'approval_name') AS user, f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'approval_role') AS role,
  74.                        f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'bank_acc_1') || ' CABANG ' || f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuInfoReport.ou_id,  'bank_branch_1') AS bu_bank,
  75.                        COALESCE(C.npwp_no, ' ') AS cust_tax_info, COALESCE(C.address1, D.address1) AS bill_to_address_1, COALESCE(C.address2 || ' ' || C.address3, D.address2 || ' ' || D.address3) AS bill_to_address_2, COALESCE(C.city || ' ' || C.zip_code, D.city || ' ' || D.zip_code) AS bill_to_address_3
  76.             FROM sl_invoice_temp A
  77.             INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
  78.             LEFT OUTER JOIN m_partner_npwp C ON A.partner_id = C.partner_id
  79.             INNER JOIN m_partner_address D ON A.partner_id = D.partner_id AND D.flg_official = 'Y'
  80.             WHERE A.tenant_id = pTenantId
  81.                       AND A.invoice_temp_id = pInvoiceTempId;
  82.                      
  83.             RETURN NEXT pRefHeader;
  84.            
  85.             Open pRefDetail FOR
  86.             SELECT B.doc_no AS do_no, B.doc_date AS do_date, f_get_product_name(C.product_id) AS product_name,
  87.                        SUM(E.qty_dlv_so) AS qty, f_get_uom_name(C.base_uom_id) AS uom_code,
  88.                        E.price_so AS price, SUM(A.ref_item_amount) AS amount, 0 AS disc
  89.             FROM sl_invoice_temp_item A
  90.             INNER JOIN sl_do B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.do_id
  91.             INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id AND A.ref_id = C.do_id
  92.             INNER JOIN sl_invoice_temp D ON A.invoice_temp_id = D.invoice_temp_id
  93.             INNER JOIN sl_so_balance_invoice E ON A.tenant_id = E.tenant_id AND D.ou_id = E.ou_id AND E.ref_doc_type_id = vDoDocTypeId AND D.invoice_temp_id = E.invoice_id AND E.ref_id = B.do_id AND E.so_id = B.ref_id AND E.ref_item_id = A.ref_item_id AND E.do_receipt_item_id = A.do_receipt_item_id
  94.             WHERE A.tenant_id = pTenantId
  95.             AND A.invoice_temp_id = pInvoiceTempId
  96.             GROUP BY B.doc_no, B.doc_date, C.product_id, E.price_so, C.base_uom_id
  97.             HAVING SUM(E.qty_dlv_so) > 0
  98.             ORDER BY product_name;
  99.            
  100.             RETURN NEXT pRefDetail;
  101.      
  102.     END;
  103.     $BODY$
  104.   LANGUAGE plpgsql VOLATILE
  105.   COST 100
  106.   ROWS 1000;
  107. ALTER FUNCTION r_print_sales_invoice_temporary_xc(character varying, bigint, bigint, bigint, character varying, bigint, character varying)
  108.   OWNER TO sts;
Add Comment
Please, Sign In to add comment