abirama62

update_tax_so

Aug 25th, 2021 (edited)
786
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Update data SO -  SI
  2.  
  3. /* A. Update data SO Item */
  4.  
  5. -- tax_id = 10 (Ppn)
  6. -- Update flg_tax
  7. UPDATE sl_so_item B
  8.     SET flg_tax_amount = 'Y', tax_id = 10, tax_percentage = 10.00
  9. FROM sl_so A
  10. WHERE A.so_id = B.so_id
  11. AND A.doc_no = 'XXXX';
  12.  
  13. -- get data rounding mode
  14. SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax'); --> vRoundingMode
  15.  
  16. -- get data rounding scale
  17. SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.print.total') AS integer); --> vRoundingScale
  18.  
  19. -- update dpp dan ppn
  20. UPDATE sl_so_item B
  21.     SET tax_price = f_calculate_tax_amount_from_gross_price(B.gross_sell_price, 1, 0, B.flg_tax_amount, B.tax_percentage, vRoundingScale, vRoundingMode),
  22.     net_sell_price = f_calculate_dpp_amount_from_gross_price(B.gross_sell_price, 1, 0, B.flg_tax_amount, B.tax_percentage, vRoundingScale, vRoundingMode)
  23. FROM sl_so A
  24. INNER JOIN temp_sl_so_progress_show_data_20210825 C ON A.so_id = C.so_id
  25. WHERE B.so_item_id = C.so_item_id;
  26.  
  27. -- update sub total tax + nett sub total
  28. UPDATE sl_so_item B
  29.     SET tax_amount = B.tax_price * B.qty_so,
  30.     nett_item_amount = B.net_sell_price * B.qty_so
  31. FROM sl_so A
  32. INNER JOIN temp_sl_so_progress_show_data_20210825 C ON A.so_id = C.so_id
  33. WHERE B.so_item_id = C.so_item_id;
  34.  
  35. /* B. Update data SO Tax */
  36.  
  37. -- get data rounding mode
  38. SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax'); --> 'RHU'
  39.  
  40. WITH get_data_tax AS (
  41.     SELECT Z.so_id, Z.doc_type_id, SUM(f_get_amount_before_tax(
  42.                 (A.qty_so * (A.gross_sell_price - A.discount_amount)), A.flg_tax_amount,
  43.                 A.tax_percentage,f_get_digit_decimal_doc_curr(Z.doc_type_id, A.curr_code),
  44.                 'RHU')) AS data_base_amount,
  45.         f_tax_rounding(
  46.                 Z.tenant_id, SUM(f_get_amount_before_tax(
  47.                     (A.qty_so * (A.gross_sell_price - A.discount_amount)), A.flg_tax_amount,
  48.                     A.tax_percentage,f_get_digit_decimal_doc_curr(Z.doc_type_id, A.curr_code),
  49.                     'RHU')),
  50.                 A.tax_percentage) AS data_tax_amount
  51.     -- SELECT Z.doc_no, Z.doc_date,
  52.     FROM sl_so Z
  53.     INNER JOIN sl_so_item A ON A.so_id = Z.so_id
  54.     INNER JOIN m_tax B ON B.tax_id = A.tax_id
  55.     WHERE Z.doc_no = 'XXXX'
  56.     GROUP BY Z.so_id, Z.doc_type_id, Z.tenant_id, A.tax_percentage
  57. )
  58. UPDATE sl_so_tax X
  59.         SET base_amount = A.data_base_amount,
  60.         tax_amount = data_tax_amount
  61.     FROM get_data_tax A
  62.     INNER JOIN temp_sl_so_progress_show_data_20210825 C ON A.so_id = C.so_id
  63.     WHERE A.so_id = X.so_id
  64.     AND A.tenant_id = X.tenant_id;
  65.    
  66. /* C. Update data DO */
  67.  
  68. -- update sl_so_balance_invoice
  69.     -- vDeliveryOrderDocTypeId --> 311
  70.     -- vRoundingModeNonTax --> 'RHU'
  71.     UPDATE sl_so_balance_invoice X
  72.         SET price_so = C.item_amount,
  73.             item_amount = f_get_amount_before_tax_and_disc(
  74.             B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount,
  75.             C.flg_tax_amount, C.tax_percentage,
  76.             f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
  77.             vRoundingModeNonTax),
  78.         regular_disc_amount = C.discount_amount * B.qty_dlv_so
  79.        
  80.     SELECT A.*
  81.     FROM temp_sl_so_progress_show_data_20210825 A
  82.     INNER JOIN sl_do_item B ON A.do_id = B.do_id AND A.so_item_id = B.ref_id
  83.     INNER JOIN sl_so_item C ON A.so_item_id = C.so_item_id
  84.     -- WHERE A.doc_no = 'MEL-SO21.08.0240';
  85.     WHERE X.so_id = A.so_id AND X.ref_id = A.do_id
  86.     AND X.ref_doc_type_id = vDeliveryOrderDocTypeId
  87.     AND X.ref_item_id = B.do_item_id;
  88.    
  89. /* -- Tidak jadi dipakai, karena item SO yang tidak ada tax, tidak akan ada data di sl_so_balance_invoice_tax
  90. -- update sl_so_balance_invoice_tax
  91.     -- vDeliveryOrderDocTypeId --> 311
  92.     -- vRoundingModeNonTax --> 'RHU'
  93.     UPDATE sl_so_balance_invoice_tax X
  94.         SET tax_id = C.tax_id,
  95.             flg_amount = D.flg_amount,
  96.             tax_percentage = C.tax_percentage,
  97.             base_amount = f_get_amount_before_tax_and_disc(
  98.                 B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount,
  99.                 C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
  100.                 vRoundingModeNonTax),
  101.             tax_amount = f_tax_rounding(
  102.                 A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * (C.gross_sell_price - C.discount_amount),
  103.                     C.flg_tax_amount, C.tax_percentage,
  104.                     f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
  105.                     vRoundingModeNonTax),
  106.                 C.tax_percentage)
  107.     -- SELECT A.*
  108.     FROM temp_sl_so_progress_show_data_20210825 A
  109.     INNER JOIN sl_do_item B ON A.do_id = B.do_id AND A.so_item_id = B.ref_id
  110.     INNER JOIN sl_so_item C ON A.so_item_id = C.so_item_id
  111.     INNER JOIN m_tax D ON C.tax_id = D.tax_id
  112.     -- WHERE A.doc_no = 'MEL-SO21.08.0240';
  113.     WHERE X.so_id = A.so_id AND X.ref_id = A.do_id
  114.     AND X.ref_doc_type_id = vDeliveryOrderDocTypeId
  115.     AND X.ref_item_id = B.do_item_id;
  116. */ 
  117.  
  118. -- insert data ke sl_so_balance_invoice_tax
  119.     INSERT INTO sl_so_balance_invoice_tax
  120.         (tenant_id, ou_id, partner_id, so_id,
  121.         ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  122.         tax_percentage, curr_code,
  123.         base_amount,
  124.         tax_amount,
  125.         flg_invoice, invoice_id,
  126.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  127.     SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, A.ref_id,
  128.         A.doc_type_id, A.do_id, B.do_item_id, C.tax_id, D.flg_amount,
  129.         C.tax_percentage, C.curr_code,
  130.         f_get_amount_before_tax_and_disc(
  131.             B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount,
  132.             C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
  133.             vRoundingModeNonTax),
  134.         f_tax_rounding(
  135.             A.tenant_id, f_get_amount_before_tax(
  136.                 B.qty_dlv_so * (C.gross_sell_price - C.discount_amount),
  137.                 C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
  138.                 vRoundingModeNonTax),
  139.             C.tax_percentage),
  140.         vFlagInvoice, vEmptyId,
  141.         0, vDatetime, vUserId, vDatetime, vUserId
  142.     FROM sl_do A
  143.     INNER JOIN sl_do_item B ON A.do_id = B.do_id
  144.     INNER JOIN sl_so_item C ON B.ref_id = C.so_item_id
  145.     INNER JOIN m_tax D ON C.tax_id = D.tax_id
  146.     INNER JOIN sl_so E ON C.so_id = E.so_id
  147.     WHERE EXISTS (
  148.         SELECT 1
  149.         FROM temp_sl_so_progress_show_data_20210825 X
  150.         WHERE X.so_id = E.so_id AND C.so_item_id = X.so_item_id
  151.         AND X.do_id = A.do_id
  152.     );
  153.    
  154. /* D. Update data Sales Invoice */
  155.  
  156. -- update sl_invoice_item
  157.     UPDATE sl_invoice_item X
  158.         SET ref_item_amount = A.item_amount
  159.     -- SELECT A.*
  160.     FROM sl_so_balance_invoice A
  161.     INNER JOIN sl_do B ON A.ref_id = B.do_id AND B.doc_type_id = A.ref_doc_type_id AND A.so_id = B.ref_id
  162.     INNER JOIN sl_do_item C ON C.do_item_id = A.ref_item_id AND B.do_id = C.do_id
  163.     INNER JOIN temp_sl_so_progress_show_data_20210825 D ON D.do_id = B.do_id AND D.so_item_id = C.ref_id
  164.     -- WHERE D.doc_no = 'MEL-SO21.08.0240';
  165.     WHERE X.invoice_id = D.invoice_id AND X.ref_id = B.do_id AND A.ref_item_id = C.do_item_id;
  166.    
  167.  
  168. -- update sl_invoice_tax
  169.     UPDATE sl_invoice_tax X
  170.         SET base_amount = SUM(A.base_amount),
  171.         tax_amount = SUM(A.tax_amount),
  172.         gov_tax_amount = SUM(A.tax_amount),
  173.         total_tax_disc_amount = ((SUM(C.regular_disc_amount)+SUM(C.promo_disc_amount))*A.tax_percentage/100)
  174.     FROM sl_so_balance_invoice_tax A
  175.     INNER JOIN sl_do_item B ON B.tenant_id = A.tenant_id AND B.do_item_id = A.ref_item_id
  176.     INNER JOIN sl_so_balance_invoice C ON A.so_id = C.so_id AND A.ref_doc_type_id = C.ref_doc_type_id
  177.         AND A.ref_id = C.ref_id AND A.ref_item_id= C.ref_item_id
  178.     WHERE EXISTS (
  179.         SELECT 1
  180.         FROM temp_sl_so_progress_show_data_20210825 Z
  181.         WHERE B.do_id = Z.do_id AND B.ref_id = Z.so_item_id
  182.         AND X.invoice_id = Z.invoice_id AND A.so_id = Z.so_id
  183.     )
  184.     GROUP BY A.tax_percentage;
  185.    
  186. -- update sl_invoice
  187.     WITH tax_amount AS (
  188.         SELECT A.ref_id AS so_id, SUM(B.tax_amount) AS total_tax_amount
  189.         FROM sl_do A
  190.         INNER JOIN sl_so_balance_invoice_tax B ON A.do_id = B.ref_id
  191.             AND A.doc_type_id = B.ref_doc_type_id   AND A.ref_id = B.so_id
  192.         WHERE EXISTS (
  193.             SELECT 1
  194.             FROM temp_sl_so_progress_show_data_20210825 X
  195.             WHERE X.so_id = A.ref_id
  196.         )
  197.         GROUP BY A.ref_id
  198.     ), data_amount AS (
  199.         SELECT A.ref_id AS so_id, ((SUM(B.item_amount)) - (SUM(B.regular_disc_amount)) - (SUM(B.promo_disc_amount))) AS total_amount,
  200.         SUM(B.regular_disc_amount) AS total_reg_disc_amount,
  201.         SUM(B.promo_disc_amount) AS total_promo_disc_amount,
  202.         ((SUM(B.item_amount)) - (SUM(B.regular_disc_amount)) - (SUM(B.promo_disc_amount))) AS gross_amount_exclude_tax
  203.         FROM sl_do A
  204.         INNER JOIN sl_so_balance_invoice B ON A.do_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id AND A.ref_id = B.so_id
  205.         WHERE EXISTS (
  206.             SELECT 1
  207.             FROM temp_sl_so_progress_show_data_20210825 X
  208.             WHERE X.so_id = A.ref_id
  209.         )
  210.         GROUP BY A.ref_id
  211.     )
  212.     UPDATE sl_invoice X
  213.         SET gross_amount = B.gross_amount_exclude_tax + A.total_tax_amount,
  214.         tax_amount = C.total_tax_amount,
  215.         total_amount = B.total_amount,
  216.         discount_amount = B.total_reg_disc_amount + B.total_promo_disc_amount
  217.     FROM temp_sl_so_progress_show_data_20210825 A
  218.     INNER JOIN data_amount B ON A.so_id = B.so_id
  219.     INNER JOIN tax_amount C ON A.so_id = C.so_id
  220.     WHERE X.ref_id = A.so_id AND X.ref_doc_type_id = 301
  221.     AND X.invoice_id = A.invoice_id
  222.    
  223. -- update sl_invoice_cost
  224. /* tidak perlu update, karena semua dokumen invoice yang berkaitan tidak ada data invoice cost. Bisa cek di:
  225. *   SELECT A.doc_no, A.invoice_doc_no, B.*
  226. *   FROM temp_sl_so_progress_show_data_20210825 A
  227. *   INNER JOIN sl_invoice_cost B ON A.invoice_id = B.invoice_id;
  228. *
  229. */
  230.  
  231. -- update fi_invoice_ar_balance
  232.  
  233.     UPDATE fi_invoice_ar_balance X
  234.         SET amount = A.total_amount + COALESCE(B.gov_tax_amount, 0)
  235.     FROM sl_invoice A
  236.     LEFT OUTER JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
  237.     WHERE EXISTS (
  238.         SELECT 1
  239.         FROM temp_sl_so_progress_show_data_20210825 Z
  240.         WHERE Z.invoice_id = A.invoice_id
  241.         AND A.ref_id = Z.so_id
  242.     ) AND A.invoice_id = X.invoice_ar_id AND A.doc_type_id = X.doc_type_id;
  243.    
  244.  
  245. /* E. Update data jurnal Sales Invoice */
  246. /*
  247. * update gl_journal_trx untuk yang tipenya `SALES_INCOOME` dan `VAT_OUT`
  248. */
  249.  
  250. -- update gl_journal_trx_item untuk item yang tidak ada pajak
  251.  
  252.     UPDATE gl_journal_trx_item X
  253.         SET amount = C.ref_item_amount,
  254.         update_datetime = vDateTime,
  255.         update_user_id = vUserId
  256.     --SELECT A.doc_no, C.*
  257.     FROM gl_journal_trx A
  258.     INNER JOIN sl_invoice B ON A.doc_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
  259.     INNER JOIN sl_invoice_item C ON C.invoice_id = B.invoice_id
  260.     WHERE EXISTS (
  261.         SELECT 1
  262.         FROM temp_sl_so_progress_show_data_20210825 D
  263.         INNER JOIN sl_do_item E ON D.do_id = E.do_id AND E.ref_id = D.so_item_id
  264.         WHERE B.invoice_id = D.invoice_id AND C.ref_id = E.do_item_id
  265.     ) AND X.journal_trx_id = A.journal_trx_id AND X.ref_id = C.invoice_item_id
  266.     AND X.journal_desc = 'SALES_INCOME';
  267.    
  268. -- update gl_journal_trx_item untuk data pajak (vat out)
  269.     UPDATE gl_journal_trx_item X
  270.         SET amount = C.gov_tax_amount,
  271.         update_datetime = vDateTime,
  272.         update_user_id = vUserId   
  273.     --SELECT B.doc_no, COALESCE(C.invoice_tax_id, -99) AS invoice_tax_id, A.*
  274.     FROM gl_journal_trx A
  275.     INNER JOIN sl_invoice B ON A.doc_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
  276.     INNER JOIN sl_invoice_tax C ON C.invoice_id = B.invoice_id
  277.     INNER JOIN temp_sl_so_progress_show_data_20210825 D ON B.invoice_id = D.invoice_id AND B.ref_id = D.so_id
  278.     WHERE X.journal_trx_id = A.journal_trx_id AND X.ref_id = C.invoice_tax_id
  279.     AND X.journal_desc = 'VAT_OUT';
  280.  
  281. -- update fi_vat_out_balance_efaktur_gunggung
  282.     UPDATE fi_vat_out_balance_efaktur_gunggung X
  283.         SET invoice_dpp_amount = B.base_amount,
  284.         invoice_tax_amount = B.tax_amount,
  285.         invoice_gov_tax_amount = B.gov_tax_amount
  286.     FROM sl_invoice A, sl_invoice_tax B
  287.     WHERE A.invoice_id = B.invoice_id
  288.     AND X.invoice_doc_type_id = A.doc_type_id
  289.     AND X.invoice_id = A.invoice_id AND X.invoice_tax_id = B.invoice_tax_id
  290.     AND EXISTS (
  291.         SELECT 1
  292.         FROM temp_sl_so_progress_show_data_20210825 C
  293.         WHERE C.invoice_id = A.invoice_id
  294.         AND A.ref_id = C.so_id
  295.     );
  296.        
  297.        
  298. -- hapus & generate ulang vat out efaktur
  299.  
  300. /* tidak jadi dipakai, ada func yang bisa auto backup + generate ulang
  301. *   WITH data_invoice_ar AS (
  302. *       SELECT A.invoice_ar_id, A.doc_type_id, A.ou_id, A.doc_no
  303. *       -- SELECT A.*
  304. *       FROM fi_invoice_ar_balance A --ON A.invoice_ar_id = X.doc_id AND X.doc_type_id = A.doc_type_id
  305. *       INNER JOIN sl_invoice B ON B.doc_type_id = A.doc_type_id AND B.invoice_id = A.invoice_ar_id
  306. *       WHERE EXISTS (
  307. *           SELECT 1
  308. *           FROM temp_sl_so_progress_show_data_20210825 C
  309. *           WHERE C.invoice_id = B.invoice_id
  310. *       )
  311. *   ) DELETE FROM fi_vat_out_for_efaktur A
  312. *   WHERE EXISTS (
  313. *       SELECT 1
  314. *       FROM data_invoice_ar B
  315. *       WHERE A.doc_id = B.invoice_ar_id AND A.doc_type_id = B.doc_type_id
  316. *   );
  317. */
  318.    
  319.     SELECT fi_check_insert_vat_out_for_efaktur(A.tenant_id, 'regenerate_vat_out', A.invoice_ar_balance_id, vUserId, vDatetime)
  320.     -- SELECT A.*
  321.     FROM fi_invoice_ar_balance A --ON A.invoice_ar_id = X.doc_id AND X.doc_type_id = A.doc_type_id
  322.     INNER JOIN sl_invoice B ON B.doc_type_id = A.doc_type_id AND B.invoice_id = A.invoice_ar_id
  323.     WHERE EXISTS (
  324.         SELECT 1
  325.         FROM temp_sl_so_progress_show_data_20210825 C
  326.         WHERE C.invoice_id = B.invoice_id
  327.     );
  328.    
  329.  
RAW Paste Data