vGuLgeC3

1--fitra 2017 03 20
2
3-- cek status po
4-- status_po = N --> belum ada dokumen PO
5UPDATE temp_finalization_rgto_manual A
6SET status_po = C.status_doc
7FROM in_inventory B
8INNER JOIN pu_po C ON C.ref_id = B.inventory_id AND C.ref_doc_type_id = B.doc_type_id
9WHERE B.doc_no = A.doc_no AND 
10	  B.doc_date = A.doc_date AND
11	  B.doc_type_id = 538 AND
12	  A.session_id ='FITRA_20180320';
13
14-- cek status gto
15--status_gto = N --> belum ada dokumen gto
16UPDATE temp_finalization_rgto_manual A
17SET status_gto = C.status_doc
18FROM in_inventory B
19INNER JOIN in_inventory C ON C.ref_id = B.inventory_id AND C.ref_doc_type_id = B.doc_type_id
20WHERE B.doc_no = A.doc_no AND
21	  B.doc_date = A.doc_date AND
22      B.doc_type_id=538 AND
23      C.doc_type_id=533 AND
24	  A.session_id ='FITRA_20180320';
25
26-- cek status correction
27--status_correction = N --> belum ada dokumen correction
28UPDATE temp_finalization_rgto_manual A
29SET status_correction = C.status_doc
30FROM in_inventory B
31INNER JOIN in_inventory C ON C.ref_id = B.inventory_id AND C.ref_doc_type_id = B.doc_type_id
32WHERE B.doc_no = A.doc_no AND 
33	  B.doc_date = A.doc_date AND
34      B.doc_type_id=538 AND
35      C.doc_type_id=590 AND
36	  A.session_id ='FITRA_20180320';
37	  
38-- cek status finalization
39--status_finalization = N --> belum ada dokumen finalisasi
40UPDATE temp_finalization_rgto_manual A
41SET status_finalization = C.status_doc
42FROM in_inventory B
43INNER JOIN in_inventory C ON C.ref_id = B.inventory_id AND C.ref_doc_type_id = B.doc_type_id
44WHERE B.doc_no = A.doc_no AND 
45	  B.doc_date = A.doc_date AND
46      B.doc_type_id=538 AND
47      C.doc_type_id=589 AND
48	  A.session_id ='FITRA_20180320';
49
50-- cek status item RGTO
51UPDATE temp_finalization_rgto_manual A
52SET flg_item_in_progress = 'Y'
53FROM in_inventory B
54INNER JOIN in_balance_req_transfer_out C ON C.inventory_id = B.inventory_id
55WHERE B.doc_no = A.doc_no AND
56	  B.doc_date = A.doc_date AND
57	  B.doc_type_id=538 AND
58	  A.session_id ='FITRA_20180320' AND
59	  EXISTS (SELECT 1 
60	  		  FROM in_req_trf_out_po_balance_item D 
61	  		  WHERE D.inventory_id = B.inventory_id AND D.status_item NOT IN ('F', 'R'));
62----------------------------------------------------------------------------------------------------
63
64----------------------BACK UP DATA------------------------------------------------------------------	  		  
65--backup in_balance_req_transfer_out
66--DROP TABLE IF EXISTS in_balance_req_transfer_out_20180320;
67CREATE TABLE in_balance_req_transfer_out_20180320 AS
68  SELECT C.session_id, A.* 
69  FROM in_balance_req_transfer_out A
70  INNER JOIN in_inventory B ON B.inventory_id = A.inventory_id AND B.doc_type_id=538
71  INNER JOIN temp_finalization_rgto_manual C ON B.doc_no = C.doc_no AND B.doc_date = C.doc_date
72  WHERE C.session_id ='FITRA_20180320';
73
74--backup in_req_trf_out_po_balance_item
75--DROP TABLE IF EXISTS in_req_trf_out_po_balance_item_20180320;
76CREATE TABLE in_req_trf_out_po_balance_item_20180320 AS
77  SELECT C.session_id, A.* 
78  FROM in_req_trf_out_po_balance_item A
79  INNER JOIN in_inventory B ON B.inventory_id = A.inventory_id AND B.doc_type_id=538
80  INNER JOIN temp_finalization_rgto_manual C ON B.doc_no = C.doc_no AND B.doc_date = C.doc_date
81  WHERE C.session_id ='FITRA_20180320';
82----------------------------------------------------------------------------------------------------        
83        
84	  		  
85----------------PROCESS FINALISASI MANUAL-----------------------------------------------------------	  		  
86--update flg_real balance RGTO menjadi V
87--untuk yg belum dibuatkan finalisasi dan item tidak in progress
88UPDATE in_balance_req_transfer_out A
89SET flg_real = 'V'
90FROM in_inventory B
91INNER JOIN temp_finalization_rgto_manual C ON B.doc_no = C.doc_no AND B.doc_date = C.doc_date
92WHERE B.inventory_id = A.inventory_id AND
93      B.doc_type_id=538 AND
94      C.session_id ='FITRA_20180320' AND
95      C.status_finalization = 'N' AND
96      EXISTS (SELECT 1 
97	      FROM in_req_trf_out_po_balance_item C
98	      WHERE c.inventory_id = B.inventory_id AND (C.status_item ='R' OR C.status_item ='F'));
99
100-- update balance item po RGTO sudah GTO dan belum PO menjadi V
101-- hanya untuk yg masih outstanding (flg_real ='R')
102UPDATE in_req_trf_out_po_balance_item A
103SET status_item ='V'
104FROM in_balance_req_transfer_out B
105INNER JOIN in_inventory C ON B.inventory_id = C.inventory_id AND C.doc_type_id=538
106INNER JOIN temp_finalization_rgto_manual D ON D.doc_no = C.doc_no AND D.doc_date = C.doc_date
107WHERE A.status_item ='R' AND
108      B.inventory_id = A.inventory_id AND
109      D.status_finalization = 'N' AND
110      D.session_id ='FITRA_20180320';