8vLTbTGH

1-- 1. Prepare temp table untuk simpan data AR
2DROP TABLE IF EXISTS temp_migrate_ar_info_ext;
3CREATE TABLE temp_migrate_ar_info_ext
4(
5    ou_code character varying,
6    doc_no character varying,
7    doc_date character varying,
8    salesman_code character varying,
9    parent_group_brand character varying
10)
11WITH (
12    OIDS = FALSE
13);
14
15-- 2. insert data ke table temp;
16INSERT INTO temp_migrate_ar_info_ext(ou_code, doc_no, doc_date, salesman_code, parent_group_brand) VALUES
17('2802','ATD0090','20180115','28-791-YSF','BE');
18
19-- 3. cek apakah dokumen ada di Taskhub;
20SELECT A.*
21FROM temp_migrate_ar_info_ext A
22INNER JOIN t_ou C ON A.ou_code = C.ou_code
23WHERE EXISTS (
24	SELECT 1
25	FROM fi_invoice_ar_balance X
26	WHERE A.doc_no = X.doc_no
27	AND A.doc_date = X.doc_date
28	AND C.ou_id = X.ou_id
29);
30
31SELECT B.doc_no, B.doc_date, B.doc_type_id, A.*
32FROM temp_migrate_ar_info_ext A
33INNER JOIN t_ou C ON A.ou_code = C.ou_code
34INNER JOIN fi_invoice_ar_balance B ON A.doc_no = B.doc_no AND A.doc_date = B.doc_date
35WHERE C.ou_id = B.ou_id;
36
37-- 4. cek data salesman apakah ada di Taskhub
38SELECT A.*
39FROM temp_migrate_ar_info_ext A
40WHERE NOT EXISTS (
41	SELECT 1
42	FROM m_partner B
43	WHERE A.salesman_code = B.partner_code
44	--AND A.salesman_code <> B.partner_name
45);
46
47-- 5. cek data keseluruhan
48SELECT A.*
49FROM temp_migrate_ar_info_ext A
50INNER JOIN t_ou C ON A.ou_code = C.ou_code
51WHERE EXISTS (
52	SELECT 1
53	FROM fi_invoice_ar_balance X
54	WHERE A.doc_no = X.doc_no
55	AND A.doc_date = X.doc_date
56	AND C.ou_id = X.ou_id
57) AND EXISTS (
58	SELECT 1
59	FROM m_partner B
60	WHERE A.salesman_code = B.partner_code
61);
62
63-- 6. insert data dari table temp ke fi_invoice_ar_balance_ext
64INSERT INTO fi_invoice_ar_balance_ext(
65            invoice_ar_balance_id, salesman_id, parent_group_brand,
66            version, create_datetime, create_user_id, update_datetime, update_user_id)
67SELECT X.invoice_ar_balance_id, B.partner_id, A.parent_group_brand, 0,
68to_char(current_timestamp, 'YYYYMMDDHH24IISS')::character varying AS create_datetime, -1 as create_user_id,
69to_char(current_timestamp, 'YYYYMMDDHH24IISS')::character varying AS update_datetime, -1 as update_user_id
70FROM temp_migrate_ar_info_ext A
71INNER JOIN m_partner B ON A.salesman_code = B.partner_code
72INNER JOIN t_ou C ON A.ou_code = C.ou_code
73INNER JOIN fi_invoice_ar_balance X ON A.doc_no = X.doc_no AND A.doc_date = X.doc_date
74WHERE C.ou_id = X.ou_id
75AND NOT EXISTS (
76	SELECT 1
77	FROM fi_invoice_ar_balance_ext Y
78	WHERE X.invoice_ar_balance_id = Y.invoice_ar_balance_id
79);
80
81-- 7. get data yang gagal ter-update
82SELECT A.*
83FROM temp_migrate_ar_info_ext A
84INNER JOIN t_ou C ON A.ou_code = C.ou_code
85WHERE (NOT EXISTS (
86	SELECT 1
87	FROM fi_invoice_ar_balance X
88	WHERE A.doc_no = X.doc_no
89	AND A.doc_date = X.doc_date
90	AND C.ou_id = X.ou_id
91) OR NOT EXISTS (
92	SELECT 1
93	FROM m_partner B
94	WHERE A.salesman_code = B.partner_code
95)); --AND A.parent_group_brand = 'POS'