abirama62

prepare_script_update_journal_trx

Mar 12th, 2021
775
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Create temp table
  2.  
  3. DROP TABLE IF EXISTS temp_fix_coa_trx;
  4.  
  5. CREATE TABLE temp_fix_coa_trx(
  6.     main_acc CHARACTER VARYING(50),
  7.     sub_acc CHARACTER VARYING(50),
  8.     sign_coa CHARACTER VARYING(1)
  9. );
  10.  
  11. -- insert coa needed
  12. INSERT INTO temp_fix_coa_trx
  13. VALUES
  14. ('610200', '100', 'D'),
  15. ('610100', '200', 'D'),
  16. ('620100', '300', 'D'),
  17. ('620100', '200', 'D'),
  18. ('620100', '400', 'D'),
  19. ('620500', '300', 'D'),
  20. ('620800', '100', 'D'),
  21. ('620700', '200', 'D'),
  22. ('620700', '300', 'D'),
  23. ('620500', '110', 'D'),
  24. ('620500', '200', 'D'),
  25. ('620500', '210', 'D'),
  26. ('620500', '301', 'D'),
  27. ('620100', '600', 'D');
  28.  
  29. -- Check data journal
  30. WITH get_data AS (
  31.     SELECT CASE WHEN X.ou_branch_id = -99 THEN
  32.             CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
  33.         ELSE f_get_ou_code(X.ou_branch_id)
  34.     END AS ou_code,
  35.     X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date, f_get_doc_desc(X.doc_type_id) AS doc_type,
  36.     LEFT(A.remark, 4) AS ou_from_remark, A.remark,
  37.     B.main_acc, B.sub_acc, B.coa_desc, A.sign_journal,
  38.     A.ou_branch_id AS item_ou_branch_id, A.ou_sub_bu_id AS item_ou_sub_bu_id, X.ou_branch_id, X.ou_sub_bu_id
  39.     FROM gl_journal_trx_item A
  40.     INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
  41.     INNER JOIN m_coa B ON A.coa_id = B.coa_id
  42.     INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
  43.     INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
  44.     WHERE A.sign_journal = 'D'
  45.     AND X.doc_date BETWEEN '20201001' AND '20210310'
  46.     AND A.remark <> ''
  47. ) SELECT *
  48. FROM get_data A
  49. WHERE A.ou_code IN ('2801', '2803')
  50. AND A.ou_code <> A.ou_from_remark
  51. ORDER BY A.ou_code, A.doc_date;
  52.  
  53. -- 1. Update gl_journal_trx_item, where ou_from_remark = 2803
  54. WITH prepare_data AS (
  55.     WITH get_data AS (
  56.         SELECT CASE WHEN X.ou_branch_id = -99 THEN
  57.                 CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
  58.             ELSE f_get_ou_code(X.ou_branch_id)
  59.         END AS ou_code,
  60.         X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
  61.         LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
  62.         A.ou_branch_id AS item_ou_branch_id, A.ou_sub_bu_id AS item_ou_sub_bu_id
  63.         FROM gl_journal_trx_item A
  64.         INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
  65.         INNER JOIN m_coa B ON A.coa_id = B.coa_id
  66.         INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
  67.         INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
  68.         WHERE A.sign_journal = 'D'
  69.         AND X.doc_date BETWEEN '20201001' AND '20210310'
  70.         AND A.remark <> ''
  71.     ) SELECT *
  72.     FROM get_data A
  73.     WHERE A.ou_code IN ('2801', '2803')
  74.     AND A.ou_code <> A.ou_from_remark
  75.     AND A.ou_from_remark IN = '2803' -- khusus ou_from_remark = 2803
  76. )
  77. UPDATE gl_journal_trx_item X SET ou_branch_id = A.ou_from_remark_id
  78. FROM prepare_data A
  79. WHERE X.journal_trx_item_id = A.journal_trx_item_id
  80. AND X.journal_trx_id = A.journal_trx_id;
  81.  
  82. -- 2. Update gl_journal_trx_item, where ou_from_remark NOT IN (2801, 2803)
  83. WITH prepare_data AS (
  84.     WITH get_data AS (
  85.         SELECT CASE WHEN X.ou_branch_id = -99 THEN
  86.                 CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
  87.             ELSE f_get_ou_code(X.ou_branch_id)
  88.         END AS ou_code,
  89.         X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
  90.         LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
  91.         A.ou_branch_id AS item_ou_branch_id, A.ou_sub_bu_id AS item_ou_sub_bu_id
  92.         FROM gl_journal_trx_item A
  93.         INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
  94.         INNER JOIN m_coa B ON A.coa_id = B.coa_id
  95.         INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
  96.         INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
  97.         WHERE A.sign_journal = 'D'
  98.         AND X.doc_date BETWEEN '20201001' AND '20210310'
  99.         AND A.remark <> ''
  100.     ) SELECT *
  101.     FROM get_data A
  102.     WHERE A.ou_code IN ('2801', '2803')
  103.     AND A.ou_code <> A.ou_from_remark
  104.     AND A.ou_from_remark NOT IN ('2801', '2803') -- ou_from_remark NOT IN (2801, 2803)
  105. )
  106. UPDATE gl_journal_trx_item X SET ou_sub_bu_id = A.ou_from_remark_id
  107. FROM prepare_data A
  108. WHERE X.journal_trx_item_id = A.journal_trx_item_id
  109. AND X.journal_trx_id = A.journal_trx_id;
  110.  
  111. -- 3. Update gl_journal_trx, where ou_from_remark = 2803
  112. WITH prepare_data AS (
  113.     WITH get_data AS (
  114.         SELECT CASE WHEN X.ou_branch_id = -99 THEN
  115.                 CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
  116.             ELSE f_get_ou_code(X.ou_branch_id)
  117.         END AS ou_code,
  118.         X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
  119.         LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
  120.         X.ou_branch_id, X.ou_sub_bu_id
  121.         FROM gl_journal_trx_item A
  122.         INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
  123.         INNER JOIN m_coa B ON A.coa_id = B.coa_id
  124.         INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
  125.         INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
  126.         WHERE A.sign_journal = 'D'
  127.         AND X.doc_date BETWEEN '20201001' AND '20210310'
  128.         AND A.remark <> ''
  129.     ) SELECT *
  130.     FROM get_data A
  131.     WHERE A.ou_code IN ('2801', '2803')
  132.     AND A.ou_code <> A.ou_from_remark
  133.     AND A.ou_from_remark IN = '2803' -- khusus ou_from_remark = 2803
  134. )
  135. UPDATE gl_journal_trx X SET ou_branch_id = A.ou_from_remark_id
  136. FROM prepare_data A
  137. WHERE X.journal_trx_id = A.journal_trx_id;
  138.  
  139. -- 4. Update gl_journal_trx, where ou_from_remark NOT IN (2801, 2803)
  140. WITH prepare_data AS (
  141.     WITH get_data AS (
  142.         SELECT CASE WHEN X.ou_branch_id = -99 THEN
  143.                 CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
  144.             ELSE f_get_ou_code(X.ou_branch_id)
  145.         END AS ou_code,
  146.         X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
  147.         LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
  148.         X.ou_branch_id, X.ou_sub_bu_id
  149.         FROM gl_journal_trx_item A
  150.         INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
  151.         INNER JOIN m_coa B ON A.coa_id = B.coa_id
  152.         INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
  153.         INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
  154.         WHERE A.sign_journal = 'D'
  155.         AND X.doc_date BETWEEN '20201001' AND '20210310'
  156.         AND A.remark <> ''
  157.     ) SELECT *
  158.     FROM get_data A
  159.     WHERE A.ou_code IN ('2801', '2803')
  160.     AND A.ou_code <> A.ou_from_remark
  161.     AND A.ou_from_remark NOT IN ('2801', '2803') -- ou_from_remark NOT IN (2801, 2803)
  162. )
  163. UPDATE gl_journal_trx X SET ou_sub_bu_id = A.ou_from_remark_id
  164. FROM prepare_data A
  165. WHERE X.journal_trx_id = A.journal_trx_id;
RAW Paste Data