Advertisement
samuel025

Script Update Issue JEN-6

Jan 19th, 2022 (edited)
1,273
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --> pu_po
  2. UPDATE pu_po SET ou_id = 10
  3. WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  4. AND doc_type_id = 160;
  5. --> 5 Rows Data
  6.  
  7. --> pu_po_balance_invoice
  8. UPDATE pu_po_balance_invoice SET ou_id = 10
  9. WHERE ref_doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  10. AND ref_doc_type_id = 160;
  11. --> 9 Rows Data
  12.  
  13. --> in_product_price_balance
  14. UPDATE in_product_price_balance SET ou_id = 10
  15. WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  16. AND doc_type_id = 160;
  17. --> 9 Rows Data
  18.  
  19. --> in_log_product_balance_stock
  20. UPDATE in_log_product_balance_stock SET ou_id = 10
  21. WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  22. AND doc_type_id = 160;
  23. --> 9 Rows Data
  24.  
  25. --> in_log_product_price_balance_stock
  26. UPDATE in_log_product_price_balance_stock SET ou_id = 10
  27. WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  28. AND doc_type_id = 160;
  29. --> 9 Rows Data
  30.  
  31. --> in_balance_receive_goods_item
  32. UPDATE in_balance_receive_goods_item A SET ou_id = 10
  33. WHERE EXISTS (SELECT 1 FROM pu_po B
  34.               WHERE A.po_id = B.po_id
  35.               AND A.po_no = B.doc_no
  36.               AND B.doc_type_id = 160
  37.               AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'));
  38. --> 9 Rows Data
  39.  
  40. --> pu_monthly_price_product
  41. UPDATE pu_monthly_price_product SET ou_id = 10
  42. WHERE ref_doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  43. AND ref_doc_type_id = 160;
  44. --> 5 Rows Data
  45.  
  46. --> pu_log_monthly_price_product
  47. UPDATE pu_log_monthly_price_product SET ou_id = 10
  48. WHERE ref_doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  49. AND ref_doc_type_id = 160;
  50. --> 9 Rows Data
  51.  
  52. --> fi_invoice_ap_balance
  53. UPDATE fi_invoice_ap_balance SET ou_id = 10
  54. WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
  55. AND doc_type_id = 160;
  56. --> 5 Rows Data
  57.  
  58. --> gl_admin_journal_trx OU Jenindo
  59. --> Insert gl_admin_journal_trx OU Jenindo
  60. INSERT INTO gl_admin_journal_trx
  61.         (tenant_id, ou_id, sub_ou_id, journal_type, ledger_code,
  62.         journal_process_periode, journal_year_month, journal_counter,
  63.         process_datetime, process_user_id, create_datetime, create_user_id,
  64.         update_datetime, update_user_id)
  65. SELECT tenant_id, 10, 10, journal_type, ledger_code,
  66.         journal_process_periode, journal_year_month, 0,
  67.         process_datetime, process_user_id, create_datetime, create_user_id,
  68.         update_datetime, update_user_id
  69. FROM gl_admin_journal_trx A
  70. WHERE A.tenant_id = 10
  71. AND A.ou_id = 11
  72. AND A.journal_type IN ('AP.PURCH','STOCK.PURCH')
  73. AND A.journal_year_month = '202108'
  74. AND A.journal_process_periode = 'DAILY'
  75. AND NOT EXISTS (SELECT 1 FROM gl_admin_journal_trx B
  76.                 WHERE B.tenant_id = 10
  77.                 AND A.admin_journal_trx_id = B.admin_journal_trx_id
  78.                 AND B.ou_id = 10
  79.                 AND B.journal_type IN ('AP.PURCH','STOCK.PURCH')
  80.                 AND B.journal_year_month = '202108'
  81.                 AND B.journal_process_periode = 'DAILY');        
  82.  
  83. --> Update gl_admin_journal_trx OU Jenindo
  84. WITH temp_data_admin_journal AS (
  85.     SELECT A.tenant_id, A.ou_bu_id, A.journal_type, LEFT(A.doc_date,6) AS journal_year_month,
  86.            COUNT(A.journal_trx_id) AS total_journal
  87.     FROM gl_journal_trx A  
  88.     WHERE EXISTS (SELECT 1 FROM pu_po B
  89.                   WHERE A.doc_id = B.po_id
  90.                   AND A.doc_no = B.doc_no
  91.                   AND B.doc_type_id = 160
  92.                   AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'))
  93.     GROUP BY A.tenant_id, A.ou_bu_id, A.journal_type, journal_year_month
  94. )
  95. UPDATE gl_admin_journal_trx A
  96. SET journal_counter = A.journal_counter + B.total_journal
  97. FROM temp_data_admin_journal B
  98. WHERE A.tenant_id = B.tenant_id
  99. AND A.ou_id = 10
  100. AND A.journal_type = B.journal_type
  101. AND A.journal_year_month = B.journal_year_month
  102. AND EXISTS (SELECT 1 FROM gl_admin_journal_trx C
  103.                WHERE C.tenant_id = 10
  104.                AND A.admin_journal_trx_id = C.admin_journal_trx_id
  105.                AND C.ou_id = 10
  106.                AND C.journal_type IN ('AP.PURCH','STOCK.PURCH')
  107.                AND C.journal_year_month = '202108'
  108.                AND C.journal_process_periode = 'DAILY');
  109. --> 2 Rows Data
  110.  
  111. --> gl_admin_journal_trx OU HB
  112. --> Update gl_admin_journal_trx OU HB
  113. WITH temp_data_admin_journal AS (
  114.     SELECT A.tenant_id, A.ou_bu_id, A.journal_type, LEFT(A.doc_date,6) AS journal_year_month,
  115.            COUNT(A.journal_trx_id) AS total_journal
  116.     FROM gl_journal_trx A  
  117.     WHERE EXISTS (SELECT 1 FROM pu_po B
  118.                   WHERE A.doc_id = B.po_id
  119.                   AND A.doc_no = B.doc_no
  120.                   AND B.doc_type_id = 160
  121.                   AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'))
  122.     GROUP BY A.tenant_id, A.ou_bu_id, A.journal_type, journal_year_month
  123. )
  124. UPDATE gl_admin_journal_trx A
  125. SET journal_counter = A.journal_counter - B.total_journal
  126. FROM temp_data_admin_journal B
  127. WHERE A.tenant_id = B.tenant_id
  128. AND A.ou_id = B.ou_bu_id
  129. AND A.journal_type = B.journal_type
  130. AND A.journal_year_month = B.journal_year_month;
  131. --> 2 Rows Data
  132.  
  133. --> gl_journal_trx
  134. UPDATE gl_journal_trx A SET ou_bu_id = 10
  135. WHERE EXISTS (SELECT 1 FROM pu_po B
  136.               WHERE A.doc_id = B.po_id
  137.               AND A.doc_no = B.doc_no
  138.               AND B.doc_type_id = 160
  139.               AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'));
  140. --> 10 Rows Data
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement