abirama62

script update journal pos

Dec 2nd, 2020 (edited)
853
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- 1.  query untuk i_trx_pos_item.tax_percentage ubah menjadi 10,00 & update nilai nett_amount_item
  2. UPDATE i_trx_pos_item SET tax_percentage = 10.00, nett_amount_item = ROUND((gross_sell_price * qty) / 1.1, 2);
  3.  
  4. -- 2. update nilai tax_amount di i_trx_pos_item
  5. UPDATE i_trx_pos_item SET tax_amount = (gross_sell_price * qty) - nett_amount_item;
  6.  
  7. -- 3. hapus, lalu generate ulang i_trx_pos_tax
  8. WITH data_tax AS (
  9.     SELECT process_no, tenant_id, trx_pos_id, flg_tax_amount,
  10.         tax_percentage, SUM(nett_amount_item) AS base_amount , SUM(tax_amount) AS tax_amount,
  11.         '' AS remark, tax_id
  12.     FROM i_trx_pos_item
  13.     GROUP BY process_no, tenant_id, trx_pos_id, flg_tax_amount, tax_percentage, tax_id
  14.     UNION ALL
  15.     -- kalau ada POS yang isinya cuma barang nempil
  16.     SELECT process_no, tenant_id, trx_pos_id, 'Y',
  17.         0.00, 0, 0,
  18.         '', -99
  19.     FROM i_trx_pos_item_nempil_barang A
  20.     WHERE NOT EXISTS (
  21.         SELECT 1 FROM i_trx_pos_item X WHERE A.process_no = X.process_no
  22.     )
  23. )
  24. INSERT INTO i_trx_pos_tax(process_no, tenant_id, trx_pos_id, flg_amount,
  25.     tax_percentage, base_amount, tax_amount,
  26.     remark, tax_id,
  27.     version, create_datetime, create_user_id,
  28.     update_datetime, update_user_id)
  29. SELECT A.process_no, A.tenant_id, A.trx_pos_id, A.flg_tax_amount,
  30.     A.tax_percentage, SUM(base_amount), SUM(tax_amount),
  31.     A.remark, A.tax_id,
  32.     0 AS version, TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS create_datetime, -1 AS create_user_id,
  33.     TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS update_datetime, -1 AS update_user_id
  34. FROM data_tax A
  35.     GROUP BY process_no, tenant_id, trx_pos_id, flg_tax_amount, tax_percentage, remark, tax_id;
  36.  
  37. -- 4. regenerate journal pos
  38. SELECT f_regenerate_journal_trx_pos_shop(
  39.     'REGENERATE-JOURNALPOS.SHOP', tenant_id, ou_id, trx_pos_id, process_no, -3,
  40.     TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING)
  41. FROM i_trx_pos A;
  42.  
  43. -- 5. regenerate journal void pos
  44. SELECT f_regenerate_journal_trx_void_pos_shop(
  45.     'REGENERATE-JOURNAL-VOIDPOS.SHOP', tenant_id, ou_id, trx_pos_id, process_no, -3,
  46.     TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING)
  47. FROM i_trx_pos A;
RAW Paste Data