· 4 years ago · Mar 12, 2021, 11:04 AM
1-- Create temp table
2
3DROP TABLE IF EXISTS temp_fix_coa_trx;
4
5CREATE 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
12INSERT INTO temp_fix_coa_trx
13VALUES
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
30WITH 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 *
48FROM get_data A
49WHERE A.ou_code IN ('2801', '2803')
50AND A.ou_code <> A.ou_from_remark
51ORDER BY A.ou_code, A.doc_date;
52
53-- 1. Update gl_journal_trx_item, where ou_from_remark = 2803
54WITH 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)
77UPDATE gl_journal_trx_item X SET ou_branch_id = A.ou_from_remark_id
78FROM prepare_data A
79WHERE X.journal_trx_item_id = A.journal_trx_item_id
80AND X.journal_trx_id = A.journal_trx_id;
81
82-- 2. Update gl_journal_trx_item, where ou_from_remark NOT IN (2801, 2803)
83WITH 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)
106UPDATE gl_journal_trx_item X SET ou_sub_bu_id = A.ou_from_remark_id
107FROM prepare_data A
108WHERE X.journal_trx_item_id = A.journal_trx_item_id
109AND X.journal_trx_id = A.journal_trx_id;
110
111-- 3. Update gl_journal_trx, where ou_from_remark = 2803
112WITH 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)
135UPDATE gl_journal_trx X SET ou_branch_id = A.ou_from_remark_id
136FROM prepare_data A
137WHERE X.journal_trx_id = A.journal_trx_id;
138
139-- 4. Update gl_journal_trx, where ou_from_remark NOT IN (2801, 2803)
140WITH 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)
163UPDATE gl_journal_trx X SET ou_sub_bu_id = A.ou_from_remark_id
164FROM prepare_data A
165WHERE X.journal_trx_id = A.journal_trx_id;