abirama62

Prepare update akses OU RC XX Penjualan

Feb 17th, 2021 (edited)
496
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. SASA-410
  3. Yang akan dilakukan:
  4. 1. perbaikan formula di laporan laba rugi detail [OK]
  5. 2. Menonaktifkan OU RC 28XX PENJUALAN dan mencabut hak akses dari semua user untuk akses data ke OU RC 28XX PENJUALAN
  6. */
  7. -- 1. Daftar OU RC 28XX Penjualan
  8.  
  9.     SELECT *
  10.     FROM t_ou
  11.     WHERE ou_type_id = 12 -- type ou Departement
  12.     --AND ou_code <> 'ALL_DEPT'
  13.     AND ou_code LIKE '28%'
  14.  
  15. -- 2. Update status active = 'N'
  16.  
  17.     UPDATE t_ou SET active = 'N'
  18.     WHERE ou_type_id = 12 -- type ou Departement
  19.     --AND ou_code <> 'ALL_DEPT'
  20.     AND ou_code LIKE '28%'
  21.  
  22. -- 3. Nonaktifkan policy untuk akses OU di atas
  23.  
  24.     DELETE FROM t_policy_ou
  25.     WHERE EXISTS(
  26.         SELECT 1
  27.         FROM t_ou A
  28.         WHERE A.ou_type_id = 12 -- type ou Departement
  29.         --AND ou_code <> 'ALL_DEPT'
  30.         AND A.ou_code LIKE '28%'
  31.         AND A.ou_id = t_policy_ou.ou_id
  32.     )
  33.  
  34. /*
  35. 3. data jurnal transaksi yg masih mengandugn OU RC 28XX PENJUALAN akan dipindahkan ke OU RC PENJUALAN mulai bulan Januari 2021 s/d sekarang
  36. */
  37. -- 1. Check data trx jurnal yang ada OU RC 28XX PENJUALAN
  38.     SELECT * FROM vw_gl_journal_trx_details B
  39.     WHERE EXISTS (
  40.         SELECT 1
  41.         FROM t_ou A
  42.         WHERE A.ou_type_id = 12 -- type ou Departement
  43.         AND A.ou_code LIKE '28%'
  44.         AND (A.ou_id = B.ou_rc_id)
  45.     )
  46.  
  47. -- 2. Update OU RC = 93 (ou_id PENJUALAN)
  48. -- table yg di update: gl_journal_trx_item, gl_journal_trx_mapping, gl_journal_trx_fx
  49.  
  50.  UPDATE gl_journal_trx_item SET ou_rc_id = 93 -- OU PENJUALAN
  51.  -- SELECT * FROM gl_journal_trx_item
  52.  WHERE EXISTS(
  53.         SELECT 1
  54.         FROM gl_journal_trx B
  55.         INNER JOIN t_ou C ON C.ou_id = gl_journal_trx_item.ou_rc_id
  56.         WHERE C.ou_type_id = 12 -- type ou Departement
  57.         AND C.ou_code LIKE '28%'
  58.         AND B.doc_date >= '20210101' -- periode >= '20210101'
  59.         AND gl_journal_trx_item.journal_trx_id = B.journal_trx_id
  60.  )
  61.  
  62.  UPDATE gl_journal_trx_mapping SET ou_rc_id = 93
  63.  -- SELECT * FROM gl_journal_trx_mapping
  64.  WHERE EXISTS(
  65.         SELECT 1
  66.         FROM gl_journal_trx B
  67.         INNER JOIN t_ou C ON C.ou_id = gl_journal_trx_mapping.ou_rc_id
  68.         WHERE C.ou_type_id = 12 -- type ou Departement
  69.         AND C.ou_code LIKE '28%'
  70.         AND B.doc_date >= '20210101'
  71.         AND gl_journal_trx_mapping.journal_trx_id = B.journal_trx_id
  72.  )
  73.  
  74.  UPDATE gl_journal_trx_fx SET ou_rc_id = 93
  75.  -- SELECT * FROM gl_journal_trx_fx
  76.  WHERE EXISTS(
  77.         SELECT 1
  78.         FROM gl_journal_trx B
  79.         INNER JOIN t_ou C ON C.ou_id = gl_journal_trx_fx.ou_rc_id
  80.         WHERE C.ou_type_id = 12 -- type ou Departement
  81.         AND C.ou_code LIKE '28%'
  82.         AND B.doc_date >= '20210101'
  83.         AND gl_journal_trx_fx.journal_trx_id = B.journal_trx_id
  84.  )
RAW Paste Data