Advertisement
samuel025

Form Cetak TSI JRP

May 21st, 2021
1,022
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION r_print_sales_invoice_temporary_escp_for_jrp(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader              REFCURSOR := 'refHeader';
  6.     pRefDetail              REFCURSOR := 'refDetail';
  7.     pSessionId              ALIAS FOR $1;
  8.     pTenantId               ALIAS FOR $2;
  9.     pUserId                 ALIAS FOR $3;
  10.     pRoleId                 ALIAS FOR $4;
  11.     pDatetime               ALIAS FOR $5;
  12.     pInvoiceId              ALIAS FOR $6;
  13.     pFlgPkp                 ALIAS FOR $7;
  14.     pNamaPenanggungJwb      ALIAS FOR $8;
  15.     pSikaPenanggungJwb      ALIAS FOR $9;
  16.    
  17.     vDoDocTypeId                bigint;
  18.     vReportName                 character varying;
  19.     vDoNo                       character varying;
  20.     vAdditionalCost             numeric;
  21.     vDownPaymentAmount          numeric;
  22.     vDownPaymentAmountWithTax   numeric;
  23.     vTaxDownPaymentAmount       numeric;
  24.     vFlgShowPpn                 character varying := 'Y';
  25.     vOuBuInfoReport             OU_INFO_REPORT%ROWTYPE;
  26.     result                      record;
  27.    
  28.     vRoundingModeTax            character varying(5);
  29.     vRoundingModeNonTax         character varying(5);
  30.     vRoundingScalePrintTotal    integer;
  31.     vNullRefId                  bigint := -99;
  32.     vEmptyString                character varying(1) :='';
  33.     vCtgrProductCode                character varying;
  34.     vCtgrProductName                character varying;
  35.     vYes                        character varying(1) :='Y';
  36.     vAll                        character varying;
  37.     vOuBuId                     bigint;
  38.     vGovTaxAmount               numeric;
  39.    
  40. BEGIN
  41.     vDoDocTypeId := 311;
  42.     vReportName := 'FormTemporarySalesInvoiceForJrp';  
  43.     vAll := 'ALL';
  44.    
  45.     select (f_get_ou_bu_structure(A.ou_id)).ou_bu_id
  46.     FROM sl_invoice_temp A
  47.     WHERE A.invoice_temp_id = pInvoiceId INTO vOuBuId ;
  48.    
  49.     SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
  50.     FROM sl_invoice_temp A
  51.     WHERE A.invoice_temp_id = pInvoiceId INTO result ;
  52.     vOuBuInfoReport := result.info;
  53.    
  54.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.tax') INTO vRoundingModeTax;
  55.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  56.     SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.print.total') AS integer) INTO vRoundingScalePrintTotal;
  57.    
  58.     vRoundingScalePrintTotal := COALESCE(vRoundingScalePrintTotal,2);
  59.    
  60.     DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;
  61.    
  62.     /* get delivery order number */
  63.     SELECT STRING_AGG(Z.doc_no, ' , ' ) INTO vDoNo
  64.     FROM (
  65.         SELECT B.doc_no
  66.         FROM sl_invoice_temp_item A
  67.           INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ref_id = B.do_id
  68.         WHERE A.tenant_id = pTenantId
  69.           AND A.invoice_temp_id = pInvoiceId
  70.         GROUP BY B.do_id
  71.         ORDER BY B.doc_no) Z;
  72.     SELECT ctgr_product_code, ctgr_product_name INTO vCtgrProductCode, vCtgrProductName
  73.     FROM (
  74.         SELECT f_get_ctgr_product_code(C.ctgr_product_id) AS ctgr_product_code,
  75.                f_get_ctgr_product_name(C.ctgr_product_id) AS ctgr_product_name
  76.         FROM sl_invoice_temp_item A
  77.         INNER JOIN sl_do_item B ON A.tenant_id = B.tenant_id AND A.ref_item_id = B.do_item_id
  78.         INNER JOIN m_product C ON B.product_id = C.product_id
  79.         WHERE A.tenant_id = pTenantId
  80.           AND A.invoice_temp_id = pInvoiceId
  81.         limit 1) Z;
  82.    
  83.     /* insert into table tr_sl_invoice_item*/
  84.     INSERT INTO tr_sl_invoice_item(
  85.         session_id, tenant_id, product_code, product_name,
  86.         qty_so,
  87.         uom_name,
  88.         nett_sell_price,
  89.         gross_sell_price,
  90.         nett_item_amount,
  91.         gross_item_amount,
  92.         tax_price,
  93.         tax_amount,
  94.         warranty,
  95.         discount_pct_so,
  96.         serial_number,
  97.         product_expired_date,
  98.         regular_disc_amount,
  99.         promo_disc_amount,
  100.         adj_regular_disc_amount,
  101.         adj_promo_disc_amount,
  102.         dpp_ppn_in_so,
  103.         item_amount_in_so_balance,
  104.         regular_disc_amount_in_so_balance,
  105.         promo_disc_amount_in_so_balance,
  106.         adj_regular_disc_amount_in_so_balance,
  107.         adj_promo_disc_amount_in_so_balance,
  108.         tax_amount_in_so_balance,
  109.         so_balance_invoice_id,
  110.         line_no
  111.         )
  112.     SELECT pSessionId, pTenantId, D.product_code, D.product_name,
  113.     CASE WHEN G.do_receipt_item_id = vNullRefId THEN
  114.         COALESCE(F.qty_dlv_int, G.qty_dlv_so) ELSE COALESCE(K.qty_return, 0)*-1
  115.     END AS qty_so,
  116.     E.uom_name,
  117.     G.price_so AS nett_sell_price,
  118.     (G.item_amount + COALESCE(L.tax_amount, 0)) / G.qty_dlv_so AS gross_sell_price,
  119.     0 as nett_item_amount,--(G.qty_dlv_so * G.price_so) AS nett_item_amount,
  120.     0 AS gross_item_amount,--(G.price_so + f_tax_rounding(G.tenant_id, G.price_so, 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,
  121.     f_tax_rounding(G.tenant_id, G.price_so, 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,
  122.     COALESCE(L.tax_amount, 0) / G.qty_dlv_so AS tax_amunt,--f_tax_rounding(G.tenant_id, G.price_so, 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,
  123.     (f_get_product_warranty_service(C.product_id) :: integer) AS warranty,
  124.     J.discount_percentage,
  125.     COALESCE (I.serial_number, vEmptyString) as serial_number,
  126.     COALESCE (I.product_expired_date, vEmptyString) as product_expired_date,
  127.     G.regular_disc_amount/G.qty_dlv_so AS regular_disc_amount,
  128.     G.promo_disc_amount/G.qty_dlv_so AS promo_disc_amount,
  129.     G.adj_regular_disc_amount/G.qty_dlv_so AS adj_regular_disc_amount,
  130.     G.adj_promo_disc_amount/G.qty_dlv_so AS adj_promo_disc_amount,
  131.     f_calculate_dpp_amount_from_gross_price(J.gross_sell_price, 1, 0,J.flg_tax_amount,J.tax_percentage,vRoundingScalePrintTotal,vRoundingModeNonTax) +
  132.     f_calculate_tax_amount_from_gross_price(J.gross_sell_price, 1, 0,J.flg_tax_amount,J.tax_percentage,vRoundingScalePrintTotal,vRoundingModeNonTax) AS dpp_ppn_in_so,
  133.     G.item_amount AS item_amount_in_so_balance,
  134.     G.regular_disc_amount AS regular_disc_amount_in_so_balance,
  135.     G.promo_disc_amount AS promo_disc_amount_in_so_balance,
  136.     G.adj_regular_disc_amount AS adj_regular_disc_amount_in_so_balance,
  137.     G.adj_promo_disc_amount AS adj_promo_disc_amount_in_so_balance,
  138.     COALESCE(L.tax_amount, 0) AS tax_amount_in_so_balance,
  139.     G.so_balance_invoice_id,
  140.     row_number() OVER(PARTITION BY G.so_balance_invoice_id ORDER BY (G.item_amount + COALESCE(L.tax_amount, 0)) / G.qty_dlv_so * CASE WHEN G.do_receipt_item_id = vNullRefId THEN COALESCE(F.qty_dlv_int, G.qty_dlv_so) ELSE COALESCE(K.qty_return, G.qty_dlv_so)*-1 END DESC,
  141.                                                                      D.product_code, COALESCE (I.serial_number, vEmptyString))
  142.     FROM sl_invoice_temp_item A
  143.     INNER JOIN sl_invoice_temp H ON A.tenant_id = H.tenant_id AND A.invoice_temp_id = H.invoice_temp_id  
  144.     INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ref_id = B.do_id
  145.     INNER JOIN sl_do_item C ON A.tenant_id = C.tenant_id AND A.ref_item_id = C.do_item_id
  146.     INNER JOIN m_product D ON A.tenant_id = D.tenant_id AND C.product_id = D.product_id
  147.     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
  148.                 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
  149.     LEFT JOIN sl_so_balance_invoice_tax L ON L.tenant_id = G.tenant_id AND
  150.                                               L.ou_id = G.ou_id AND
  151.                                               L.partner_id = G.partner_id AND
  152.                                               L.ref_doc_type_id = G.ref_doc_type_id AND
  153.                                               L.ref_id = G.ref_id AND
  154.                                               L.ref_item_id = G.ref_item_id AND
  155.                                               L.do_receipt_item_id = G.do_receipt_item_id
  156.     INNER JOIN m_uom E ON A.tenant_id = E.tenant_id AND G.so_uom_id = E.uom_id
  157.     INNER JOIN sl_so_item J ON J.so_item_id = C.ref_id
  158.     LEFT JOIN sl_do_product F ON F.do_item_id = C.do_item_id --AND G.do_receipt_item_id = vNullRefId
  159.     LEFT JOIN in_do_receipt_product K ON K.do_receipt_item_id = G.do_receipt_item_id AND F.product_balance_id = K.product_balance_id
  160.     LEFT JOIN in_product_balance I ON I.product_balance_id = F.product_balance_id
  161.     WHERE A.tenant_id = pTenantId
  162.         AND A.invoice_temp_id = pInvoiceId;
  163.        
  164.     UPDATE tr_sl_invoice_item
  165.     SET nett_item_amount = ROUND(qty_so * nett_sell_price,vRoundingScalePrintTotal),
  166.         gross_item_amount = ROUND(qty_so * gross_sell_price,vRoundingScalePrintTotal),
  167.         regular_disc_amount = ROUND(regular_disc_amount * qty_so,vRoundingScalePrintTotal),
  168.         promo_disc_amount  = ROUND(promo_disc_amount* qty_so,vRoundingScalePrintTotal),
  169.         adj_regular_disc_amount  = ROUND(adj_regular_disc_amount* qty_so,vRoundingScalePrintTotal),
  170.         adj_promo_disc_amount  = ROUND(adj_promo_disc_amount* qty_so,vRoundingScalePrintTotal)
  171.     WHERE session_id = pSessionId;
  172.    
  173.     --hitung selisih regular_disc_amount dari data balance SO, dan dimasukan ke
  174.     -- harga paling tingi (line no nya 1)
  175.     WITH data_selisih AS (
  176.         SELECT so_balance_invoice_id, (regular_disc_amount_in_so_balance) - sum(regular_disc_amount) AS selisih
  177.         FROM tr_sl_invoice_item
  178.         WHERE session_id = pSessionId
  179.         GROUP BY so_balance_invoice_id, regular_disc_amount_in_so_balance
  180.     )
  181.     UPDATE tr_sl_invoice_item Z
  182.     SET regular_disc_amount = Z.regular_disc_amount + A.selisih
  183.     FROM data_selisih A
  184.     WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
  185.           Z.line_no = 1 AND
  186.           Z.session_id = pSessionId;
  187.    
  188.     --hitung selisih promo_disc_amount dari data balance SO, dan dimasukan ke
  189.     -- harga paling tingi (line no nya 1)
  190.     WITH data_selisih AS (
  191.         SELECT so_balance_invoice_id, (promo_disc_amount_in_so_balance) - sum(promo_disc_amount) AS selisih
  192.         FROM tr_sl_invoice_item
  193.         WHERE session_id = pSessionId
  194.         GROUP BY so_balance_invoice_id, promo_disc_amount_in_so_balance
  195.     )
  196.     UPDATE tr_sl_invoice_item Z
  197.     SET promo_disc_amount = Z.promo_disc_amount + A.selisih
  198.     FROM data_selisih A
  199.     WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
  200.           Z.line_no = 1 AND
  201.           Z.session_id = pSessionId;
  202.          
  203.     --hitung selisih item amount dari data balance SO, dan dimasukan ke
  204.     -- harga paling tingi (line no nya 1)
  205.     WITH data_selisih AS (
  206.         SELECT so_balance_invoice_id, (item_amount_in_so_balance + tax_amount_in_so_balance) - sum(gross_item_amount) AS selisih
  207.         FROM tr_sl_invoice_item
  208.         WHERE session_id = pSessionId
  209.         GROUP BY so_balance_invoice_id, item_amount_in_so_balance + tax_amount_in_so_balance
  210.     )
  211.     UPDATE tr_sl_invoice_item Z
  212.     SET gross_item_amount = Z.gross_item_amount + A.selisih
  213.     FROM data_selisih A
  214.     WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
  215.           Z.line_no = 1 AND
  216.           Z.session_id = pSessionId;
  217.    
  218.     --hitung selisih nett item amount dari data balance SO, dan dimasukan ke
  219.     -- harga paling tingi (line no nya 1)
  220.     WITH data_selisih AS (
  221.         SELECT so_balance_invoice_id, (item_amount_in_so_balance - regular_disc_amount_in_so_balance - promo_disc_amount_in_so_balance) - sum(nett_item_amount) AS selisih
  222.         FROM tr_sl_invoice_item
  223.         WHERE session_id = pSessionId
  224.         GROUP BY so_balance_invoice_id, item_amount_in_so_balance - regular_disc_amount_in_so_balance - promo_disc_amount_in_so_balance
  225.     )
  226.     UPDATE tr_sl_invoice_item Z
  227.     SET nett_item_amount = Z.nett_item_amount + A.selisih
  228.     FROM data_selisih A
  229.     WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
  230.           Z.line_no = 1 AND
  231.           Z.session_id = pSessionId;
  232.          
  233.     --hitung selisih tax amount dari data balance SO, dan dimasukan ke
  234.     -- harga paling tingi (line no nya 1)
  235.    
  236.     SELECT gov_tax_amount INTO vGovTaxAmount
  237.     FROM sl_invoice_temp_tax
  238.     WHERE invoice_temp_id = pInvoiceId;
  239.    
  240.     vGovTaxAmount := COALESCE(vGovTaxAmount, 0);
  241.      
  242.     WITH data_yg_diupdate AS (
  243.         SELECT so_balance_invoice_id
  244.         FROM tr_sl_invoice_item
  245.         WHERE session_id = pSessionId
  246.         ORDER BY gross_item_amount DESC, line_no
  247.         LIMIT 1
  248.     )
  249.     ,data_selisih AS (
  250.         SELECT B.so_balance_invoice_id, vGovTaxAmount - sum(+A.tax_amount) AS selisih
  251.         FROM tr_sl_invoice_item A , data_yg_diupdate B
  252.         WHERE A.session_id = pSessionId
  253.         GROUP BY B.so_balance_invoice_id
  254.     )
  255.     UPDATE tr_sl_invoice_item Z
  256.     SET tax_amount = Z.tax_amount + A.selisih
  257.     FROM data_selisih A
  258.     WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
  259.           Z.line_no = 1 AND
  260.           Z.session_id = pSessionId;
  261.          
  262.     -- penyamaan gross amount dengan gross amount yg ada di invoice
  263.     WITH data_yg_diupdate AS (
  264.         SELECT so_balance_invoice_id
  265.         FROM tr_sl_invoice_item
  266.         WHERE session_id = pSessionId
  267.         ORDER BY gross_item_amount DESC, line_no
  268.         LIMIT 1
  269.     )
  270.     ,data_selisih AS (
  271.         SELECT B.so_balance_invoice_id,
  272.             sum(A.nett_item_amount + A.tax_amount + A.regular_disc_amount + A.promo_disc_amount) - sum(A.gross_item_amount)  AS selisih
  273.         FROM tr_sl_invoice_item A , data_yg_diupdate B
  274.         WHERE A.session_id = pSessionId
  275.         GROUP BY B.so_balance_invoice_id
  276.     )
  277.     UPDATE tr_sl_invoice_item Z
  278.     SET gross_item_amount = Z.gross_item_amount + A.selisih
  279.     FROM data_selisih A
  280.     WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
  281.           Z.line_no = 1 AND
  282.           Z.session_id = pSessionId;
  283.    
  284.     /* get vFlgShowPpn */
  285.     SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
  286.     FROM sl_invoice_temp A
  287.         INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
  288.     WHERE A.invoice_temp_id = pInvoiceId;
  289.    
  290.     /* get additional amount*/
  291.     SELECT CASE WHEN vRoundingModeNonTax = 'RD' THEN
  292.                 TRUNC(COALESCE(SUM(A.add_amount), 0), vRoundingScalePrintTotal)
  293.             ELSE
  294.                 ROUND(COALESCE(SUM(A.add_amount), 0), vRoundingScalePrintTotal)
  295.             END
  296.     INTO vAdditionalCost
  297.     FROM sl_invoice_temp_cost A
  298.     WHERE A.tenant_id = pTenantId
  299.         AND A.invoice_temp_id = pInvoiceId;
  300.        
  301.     /* get down payment amount in invoice */
  302.     SELECT CASE WHEN vRoundingModeNonTax = 'RD' THEN       
  303.                 TRUNC(COALESCE(SUM(A.alloc_amount), 0), vRoundingScalePrintTotal)
  304.             ELSE
  305.                 ROUND(COALESCE(SUM(A.alloc_amount), 0), vRoundingScalePrintTotal)
  306.             END,
  307.             CASE WHEN vRoundingModeNonTax = 'RD' THEN
  308.                 TRUNC(COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
  309.             ELSE
  310.                 ROUND(COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
  311.             END,
  312.             CASE WHEN vRoundingModeTax = 'RD' THEN
  313.                 TRUNC(COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
  314.             ELSE
  315.                 ROUND(COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
  316.             END
  317.     INTO vDownPaymentAmount, vDownPaymentAmountWithTax, vTaxDownPaymentAmount
  318.     FROM sl_invoice_temp_advance A
  319.     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
  320.     WHERE A.tenant_id = pTenantId
  321.         AND A.invoice_temp_id = pInvoiceId;
  322.    
  323.     Open pRefHeader FOR
  324.         SELECT A.invoice_temp_id, A.ou_id, A.ou_legal_id,
  325.             UPPER(vOuBuInfoReport.printed_ou_name) AS ou_name,
  326.             UPPER(COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-')) AS ou_addr_1,
  327.             UPPER(COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-')) AS ou_addr_2,
  328.             UPPER(vOuBuInfoReport.npwp_no) AS ou_npwp,
  329.             UPPER(vOuBuInfoReport.image_url) AS path_logo_ou,
  330.             (vOuBuInfoReport.phone1) AS ou_phone,
  331.             (vOuBuInfoReport.fax1) AS ou_fax,
  332.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_email')) AS ou_email,
  333.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ijin_pbf')) AS ijin_pbf,
  334.             UPPER(vCtgrProductCode) AS ctgr_product_code,
  335.             UPPER(vCtgrProductName) AS ctgr_product_name,
  336.             A.partner_id,
  337.             UPPER(C.partner_name) as customer_name,
  338.             UPPER(COALESCE(H.address1 || ' ' || H.address2 || ' ' || H.address3, '-')) AS customer_address_1,
  339.             UPPER(COALESCE(H.city || ' ' || H.state_or_province || ' ' || H.zip_code, '-')) AS customer_address_2,
  340.             UPPER(COALESCE(I.npwp_no, vEmptyString)) AS customer_npwp,
  341.             A.doc_type_id,
  342.             UPPER(A.inv_doc_no) AS invoice_no, A.inv_doc_date AS invoice_date,
  343.             TO_DATE(A.due_date, 'YYYYMMDD') - TO_DATE(A.doc_date, 'YYYYMMDD') AS top,
  344.             A.due_date,
  345.             COALESCE(J.tax_no, vEmptyString) AS tax_no,
  346.             UPPER(E.doc_no) AS so_no,
  347.             UPPER(vDoNo) AS do_no,
  348.             UPPER(E.ext_doc_no) AS po_no, A.salesman_id,
  349.             UPPER(COALESCE(F.partner_name, ' ')) AS salesman_name,
  350.             UPPER(A.curr_code) AS currency,
  351.             UPPER(A.remark) AS remark,
  352.             UPPER((CASE WHEN A.curr_code = 'IDR' THEN 'rupiah' ELSE A.curr_code END)) AS curr_name, vFlgShowPpn AS flg_show_ppn,
  353.             CASE WHEN vRoundingModeNonTax = 'RD' THEN
  354.                 TRUNC(SUM(gross_item_amount - (regular_disc_amount + promo_disc_amount)), vRoundingScalePrintTotal)::character varying
  355.             ELSE
  356.                 ROUND(SUM(gross_item_amount - (regular_disc_amount + promo_disc_amount)), vRoundingScalePrintTotal)::character varying
  357.             END AS sub_total,
  358.             --SUM(regular_disc_amount + promo_disc_amount + adj_regular_disc_amount + adj_promo_disc_amount)::character varying AS total_disc,
  359.             0::character varying AS total_disc,
  360.             vAdditionalCost::character varying AS additional_cost,
  361.             CASE WHEN vRoundingModeNonTax = 'RD' THEN
  362.                 TRUNC(SUM(G.tax_amount), vRoundingScalePrintTotal)::character varying
  363.             ELSE
  364.                 ROUND(SUM(G.tax_amount), vRoundingScalePrintTotal)::character varying
  365.             END AS ppn,
  366.             vDownPaymentAmountWithTax::character varying AS dp_amount,
  367.             CASE WHEN vRoundingModeNonTax = 'RD' THEN
  368.                 TRUNC(SUM(G.nett_item_amount), vRoundingScalePrintTotal)::character varying
  369.             ELSE
  370.                 ROUND(SUM(G.nett_item_amount), vRoundingScalePrintTotal)::character varying
  371.             END AS dpp,
  372.             CASE WHEN vRoundingModeNonTax = 'RD' THEN
  373.                 TRUNC(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)::character varying
  374.             ELSE
  375.                 ROUND(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)::character varying
  376.             END AS total,
  377.             UPPER(terbilang(
  378.                 CASE WHEN vRoundingModeNonTax = 'RD' THEN
  379.                     TRUNC(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)
  380.                 ELSE
  381.                     ROUND(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)
  382.                 END
  383.             )) AS terbilang,
  384.             UPPER(f_get_report_parameter_config_value(pTenantId, vAll, vOuBuId, 'email')) AS ou_email,
  385.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note1')) AS footer_note_1,
  386.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note2')) AS footer_note_2,
  387.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note3')) AS footer_note_3,
  388.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note4')) AS footer_note_4,
  389.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note5')) AS footer_note_5,
  390.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note6')) AS footer_note_6,
  391.             UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note7')) AS footer_note_7,
  392.            
  393.             UPPER(f_get_username(pUserId)) AS username,
  394.             pDatetime AS datetime,
  395.             UPPER(pNamaPenanggungJwb) AS penanggung_jawab_name,
  396.             UPPER(pSikaPenanggungJwb) AS penanggung_jawab_sika
  397.         FROM sl_invoice_temp A
  398.             INNER JOIN m_partner C ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
  399.             INNER JOIN sl_so E ON A.tenant_id = E.tenant_id AND A.ref_id = E.so_id
  400.             LEFT OUTER JOIN m_partner F ON A.tenant_id = F.tenant_id AND A.salesman_id = F.partner_id
  401.             INNER JOIN tr_sl_invoice_item G ON A.tenant_id = G.tenant_id AND G.session_id = pSessionId
  402.             LEFT JOIN m_partner_address H ON H.partner_id = A.partner_id AND H.flg_official = vYes
  403.             LEFT JOIN m_partner_npwp I ON I.partner_id = A.partner_id AND I.active = vYes
  404.             LEFT JOIN sl_invoice_temp_tax J ON J.invoice_temp_id = A.invoice_temp_id
  405.         WHERE A.tenant_id = pTenantId
  406.             AND A.invoice_temp_id = pInvoiceId
  407.         GROUP BY A.invoice_temp_id, C.partner_id, E.so_id, F.partner_id, H.partner_address_id, I.npwp_id, J.tax_no;
  408.     RETURN NEXT pRefHeader;
  409.    
  410.     Open pRefDetail FOR
  411.         SELECT row_number() OVER(order by product_code, product_name) as line_no,
  412.             UPPER(product_code) AS product_code,
  413.             UPPER(product_name) AS product_name, SUM(qty_so)::character varying AS qty, UPPER(uom_name) uom_name,
  414.            dpp_ppn_in_so::character varying AS unit_price, SUM(gross_item_amount - (regular_disc_amount + promo_disc_amount))::character varying AS amount,
  415.            UPPER(serial_number) as serial_number, product_expired_date,
  416.            discount_pct_so
  417.         FROM tr_sl_invoice_item
  418.         WHERE session_id = pSessionId
  419.             AND tenant_id = pTenantId
  420.         GROUP BY product_code, product_name, uom_name, dpp_ppn_in_so, serial_number, product_expired_date,
  421.            discount_pct_so
  422.         HAVING SUM(qty_so) > 0
  423.         ORDER BY product_code, product_name;
  424.     RETURN NEXT pRefDetail;
  425.    
  426.     DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;
  427.    
  428. END;
  429. $BODY$
  430.   LANGUAGE plpgsql VOLATILE
  431.   COST 100
  432.   ROWS 1000;
  433.   /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement