· 4 years ago · Aug 25, 2021, 08:20 AM
1Cek progress SO
2
3-- init table temp
4CREATE TABLE IF NOT EXISTS temp_sl_so_progress_data_20210825(
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_20210825(
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_20210825(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-SO21.08.0020',
48'MEL-SO21.08.0055',
49'MEL-SO21.08.0056',
50'MEL-SO21.08.0076',
51'MEL-SO21.08.0085',
52'MEL-SO21.08.0100',
53'MEL-SO21.08.0105',
54'MEL-SO21.08.0128',
55'MEL-SO21.08.0132',
56'MEL-SO21.08.0142',
57'MEL-SO21.08.0194',
58'MEL-SO21.08.0240',
59'MEL-SO21.08.0245',
60'MEL-SO21.08.0264',
61'MEL-SO21.08.0266',
62'MEL-SO21.08.0288',
63'MEL-SO21.08.0422',
64'MEL-SO21.08.0424',
65'MEL-SO21.08.0435',
66'MEL-SO21.08.0440',
67'MEL-SO21.08.0444',
68'MEL-SO21.08.0453',
69'MEL-SO21.08.0472',
70'MEL-SO21.08.0473',
71'MEL-SO21.08.0482',
72'MEL-SO21.08.0491',
73'MEL-SO21.08.0527',
74'MEL-SO21.08.0557',
75'MEL-SO21.08.0579',
76'MEL-SO21.08.0626',
77'MEL-SO21.08.0683',
78'MEL-SO21.08.0719',
79'MEL-SO21.08.0727',
80'MEL-SO21.08.0728',
81'MEL-SO21.08.0731',
82'MEL-SO21.08.0740',
83'MEL-SO21.08.0741',
84'MEL-SO21.08.0755',
85'MEL-SO21.08.0756',
86'MEL-SO21.08.0782');
87
88
89-- get data progress_so
90-- data SO yang belum diapa2in
91INSERT INTO temp_sl_so_progress_show_data_20210825(
92 so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id
93)
94SELECT A.*
95FROM temp_sl_so_progress_data_20210825 A
96WHERE NOT EXISTS (select 1 FROM pu_po_item B where B.ref_id=A.so_item_id) AND
97 NOT EXISTS (select 1 FROM sl_do_item C where C.ref_id=A.so_item_id);
98
99
100-- data so po gr
101WITH data_so_po AS (
102 SELECT row_number() over (partition by A.doc_no ORDER BY A.doc_no ASC) as line_no,
103 A.so_id, A.doc_no, A.doc_date, A.partner_id, A.ou_id, A.so_item_id, A.product_id,
104 B.po_id, B.doc_no AS po_no, B.doc_date AS po_date
105 FROM temp_sl_so_progress_data_20210825 A
106 INNER JOIN pu_po B ON A.so_id = B.ref_id AND B.ref_doc_type_id = 301
107 INNER JOIN pu_po_item C ON B.po_id = C.po_id AND C.ref_id = A.so_item_id
108)
109INSERT INTO temp_sl_so_progress_show_data_20210825(
110 so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id,
111 po_id, po_doc_no, po_doc_date,
112 gr_id, gr_doc_no, gr_doc_date
113)
114SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, A.so_item_id, A.product_id,
115 A.po_id, A.po_no, A.po_date,
116 B.receive_goods_id, B.doc_no, B.doc_date
117FROM data_so_po A
118INNER JOIN pu_receive_goods B ON A.po_id = B.ref_id
119WHERE B.ref_doc_type_id = 101; --> docType PO
120
121-- data so -> do
122UPDATE temp_sl_so_progress_show_data_20210825 A
123 SET do_id = C.do_id,
124 do_doc_no = C.doc_no,
125 do_doc_date = C.doc_date
126FROM sl_do C
127INNER JOIN sl_do_item D ON C.do_id = D.do_id
128WHERE A.so_id = C.ref_id AND C.ref_doc_type_id = 301
129AND D.ref_id = A.so_item_id;
130
131-- data so -> do -> si
132UPDATE temp_sl_so_progress_show_data_20210825 A
133 SET invoice_id = B.invoice_id, invoice_doc_no = B.doc_no, invoice_doc_date = B.doc_date
134FROM sl_invoice B
135INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id AND C.ref_doc_type_id = 311
136WHERE B.ref_doc_type_id = 301
137AND A.do_id = C.ref_id
138AND A.so_id = B.ref_id;
139
140-- Get data final
141 SELECT doc_no AS so_no, doc_date AS so_no, f_get_ou_code(ou_id) AS ou_code,
142 f_get_partner_name(partner_id) AS customer, f_get_product_code(product_id), f_get_product_name(product_id),
143 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,
144 invoice_doc_date AS sales_invoice_date
145 -- SELECT *
146 FROM temp_sl_so_progress_show_data_20210825 ORDER BY so_id;