· 3 years ago · Feb 18, 2022, 04:40 AM
1--Cek progress SO
2
3-- init table temp
4CREATE TABLE IF NOT EXISTS temp_sl_so_progress_data_20220218(
5 so_id BIGINT NOT NULL,
6 doc_no CHARACTER varying(30),
7 doc_date CHARACTER varying(8),
8 ou_id BIGINT,
9 partner_id BIGINT,
10 so_item_id BIGINT,
11 product_id BIGINT
12)WITH (
13 OIDS=FALSE
14);
15
16-- table unutk view
17CREATE TABLE IF NOT EXISTS temp_sl_so_progress_show_data_20220218(
18 so_id BIGINT NOT NULL,
19 doc_no CHARACTER varying(30),
20 doc_date CHARACTER varying(8),
21 ou_id BIGINT,
22 partner_id BIGINT,
23 so_item_id BIGINT,
24 product_id BIGINT,
25 po_id BIGINT DEFAULT -99,
26 po_doc_no CHARACTER varying(30) DEFAULT '',
27 po_doc_date CHARACTER varying(8) DEFAULT '',
28 gr_id BIGINT DEFAULT -99,
29 gr_doc_no CHARACTER varying(30) DEFAULT '',
30 gr_doc_date CHARACTER varying(8) DEFAULT '',
31 do_id BIGINT DEFAULT -99,
32 do_doc_no CHARACTER varying(30) DEFAULT '',
33 do_doc_date CHARACTER varying(8) DEFAULT '',
34 invoice_id BIGINT DEFAULT -99,
35 invoice_doc_no CHARACTER varying(30) DEFAULT '',
36 invoice_doc_date CHARACTER varying(8) DEFAULT ''
37)WITH (
38 OIDS=FALSE
39);
40
41-- insert data ke temp table
42INSERT INTO temp_sl_so_progress_data_20220218(so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id)
43SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, B.so_item_id, B.product_id
44FROM sl_so A
45INNER JOIN sl_so_item B ON A.so_id = B.so_id
46WHERE B.flg_tax_amount <> 'Y' AND
47A.doc_no IN ('MEL-SO22.01.0605');
48
49-- get data progress_so
50-- data SO yang belum diapa2in
51INSERT INTO temp_sl_so_progress_show_data_20220218(
52 so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id
53)
54SELECT A.*
55FROM temp_sl_so_progress_data_20220218 A
56WHERE NOT EXISTS (SELECT 1 FROM pu_po_item B WHERE B.ref_id=A.so_item_id) AND
57 NOT EXISTS (SELECT 1 FROM sl_do_item C WHERE C.ref_id=A.so_item_id);
58
59
60-- data so po gr
61WITH data_so_po AS (
62 SELECT ROW_NUMBER() OVER (PARTITION BY A.doc_no ORDER BY A.doc_no ASC) AS line_no,
63 A.so_id, A.doc_no, A.doc_date, A.partner_id, A.ou_id, A.so_item_id, A.product_id,
64 B.po_id, B.doc_no AS po_no, B.doc_date AS po_date
65 FROM temp_sl_so_progress_data_20220218 A
66 INNER JOIN pu_po B ON A.so_id = B.ref_id AND B.ref_doc_type_id = 301
67 INNER JOIN pu_po_item C ON B.po_id = C.po_id AND C.ref_id = A.so_item_id
68)
69INSERT INTO temp_sl_so_progress_show_data_20220218(
70 so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id,
71 po_id, po_doc_no, po_doc_date,
72 gr_id, gr_doc_no, gr_doc_date
73)
74SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, A.so_item_id, A.product_id,
75 A.po_id, A.po_no, A.po_date,
76 B.receive_goods_id, B.doc_no, B.doc_date
77FROM data_so_po A
78INNER JOIN pu_receive_goods B ON A.po_id = B.ref_id
79WHERE B.ref_doc_type_id = 101; --> docType PO
80
81-- data so -> do
82UPDATE temp_sl_so_progress_show_data_20220218 A
83 SET do_id = C.do_id,
84 do_doc_no = C.doc_no,
85 do_doc_date = C.doc_date
86FROM sl_do C
87INNER JOIN sl_do_item D ON C.do_id = D.do_id
88WHERE A.so_id = C.ref_id AND C.ref_doc_type_id = 301
89AND D.ref_id = A.so_item_id;
90
91-- data so -> do -> si
92UPDATE temp_sl_so_progress_show_data_20220218 A
93 SET invoice_id = B.invoice_id, invoice_doc_no = B.doc_no, invoice_doc_date = B.doc_date
94FROM sl_invoice B
95INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id AND C.ref_doc_type_id = 311
96WHERE B.ref_doc_type_id = 301
97AND A.do_id = C.ref_id
98AND A.so_id = B.ref_id;
99
100-- Get data final
101 SELECT doc_no AS so_no, doc_date AS so_date, f_get_ou_code(ou_id) AS ou_code,
102 f_get_partner_name(partner_id) AS customer, f_get_product_code(product_id), f_get_product_name(product_id),
103 po_doc_no, po_doc_date, gr_doc_no, gr_doc_date, do_doc_no, do_doc_date, invoice_doc_no AS sales_invoice_no,
104 invoice_doc_date AS sales_invoice_date
105 -- SELECT *
106 FROM temp_sl_so_progress_show_data_20220218 ORDER BY so_id;
107