[Home] [Help]
PACKAGE BODY: APPS.GML_LOG
Source
1 PACKAGE BODY GML_LOG AS
2 /* $Header: GMLTRLGB.pls 115.4 2002/12/04 23:32:05 uphadtar ship $ */
3
4 /*========================================================================
5 | PROCEDURE: po_tran |
6 | |
7 | DESCRIPTION: This procedure selects all the rows in the purchasing |
8 | interface table which are successfully processed |
9 | or errored out and writes them into the purchasing |
10 | transaction log table. Then it deletes all those |
11 | rows from the purchasing interface table. This procedure|
12 | also incrementally inserts into cpg_oragems_arch table |
13 | rows from cpg_oragems_mapping |
14 | |
15 | MODIFICATION: Kenny Jiang 11/24/97 Created |
16 ========================================================================*/
17
18
19 PROCEDURE po_tran
20 IS
21 err_num NUMBER;
22 err_msg VARCHAR2(100);
23 v_archival_date DATE;
24
25 CURSOR archival_date_cur IS
26 SELECT MAX(time_stamp)
27 FROM cpg_oragems_arch;
28 BEGIN
29
30 /* INSERT INTO cpg_purchasing_arch
31 SELECT *
32 FROM cpg_purchasing_interface
33 WHERE invalid_ind IN ('Y', 'P'); */
34
35 INSERT INTO cpg_purchasing_arch(
36 TRANSACTION_ID ,
37 TRANSACTION_TYPE ,
38 ORGN_CODE ,
39 PO_NO ,
40 BPO_NO ,
41 PO_STATUS ,
42 PO_HEADER_ID ,
43 PO_LINE_ID ,
44 PO_LINE_LOCATION_ID ,
45 PO_DISTRIBUTION_ID ,
46 PO_RELEASE_ID ,
47 RELEASE_NUM ,
48 BUYER_CODE ,
49 PO_ID ,
50 BPO_ID ,
51 BPO_RELEASE_NUMBER ,
52 OF_PAYVEND_SITE_ID ,
53 OF_SHIPVEND_SITE_ID ,
54 PO_DATE ,
55 PO_TYPE ,
56 FROM_WHSE ,
57 TO_WHSE ,
58 RECV_DESC ,
59 RECV_LOCT ,
60 RECVADDR_ID ,
61 SHIP_MTHD ,
62 SHIPPER_CODE ,
63 OF_FRTBILL_MTHD ,
64 OF_TERMS_CODE ,
65 BILLING_CURRENCY ,
66 PURCHASE_EXCHANGE_RATE ,
67 MUL_DIV_SIGN ,
68 CURRENCY_BGHT_FWD ,
69 POHOLD_CODE ,
70 CANCELLATION_CODE ,
71 FOB_CODE ,
72 ICPURCH_CLASS ,
73 VENDSO_NO ,
74 PROJECT_NO ,
75 REQUESTED_DLVDATE ,
76 SCHED_SHIPDATE ,
77 REQUIRED_DLVDATE ,
78 AGREED_DLVDATE ,
79 DATE_PRINTED ,
80 EXPEDITE_DATE ,
81 REVISION_COUNT ,
82 IN_USE ,
83 PRINT_COUNT ,
84 LINE_NO ,
85 LINE_STATUS ,
86 LINE_ID ,
87 BPO_LINE_ID ,
88 APINV_LINE_ID ,
89 ITEM_NO ,
90 GENERIC_ID ,
91 ITEM_DESC ,
92 ORDER_QTY1 ,
93 ORDER_QTY2 ,
94 ORDER_UM1 ,
95 ORDER_UM2 ,
96 RECEIVED_QTY1 ,
97 RECEIVED_QTY2 ,
98 NET_PRICE ,
99 EXTENDED_PRICE ,
100 PRICE_UM ,
101 QC_GRADE_WANTED ,
102 MATCH_TYPE ,
103 TEXT_CODE ,
104 TRANS_CNT ,
105 EXPORTED_DATE ,
106 LAST_UPDATE_DATE ,
107 CREATED_BY ,
108 CREATION_DATE ,
109 LAST_UPDATED_BY ,
110 LAST_UPDATE_LOGIN ,
111 DELETE_MARK ,
112 INVALID_IND ,
113 REL_COUNT ,
114 CONTRACT_NO ,
115 CONTRACT_VALUE ,
116 CONTRACT_END_DATE ,
117 CONTRACT_START_DATE ,
118 AMOUNT_PURCHASED ,
119 ACTIVITY_IND ,
120 CONTRACT_QTY ,
121 ITEM_UM ,
122 QTY_PURCHASED ,
123 STD_QTY ,
124 RELEASE_INTERVAL ,
125 BPO_STATUS ,
126 BPOHOLD_CODE ,
127 CLOSURE_CODE ,
128 MAX_RELS_QTY ,
129 ORGNADDR_ID ,
130 SOURCE_SHIPMENT_ID ,
131 PURCH_CATEGORY_ID )
132 SELECT *
133 FROM cpg_purchasing_interface
134 WHERE invalid_ind IN ('Y', 'P');
135
136 DELETE FROM cpg_purchasing_interface
137 WHERE invalid_ind IN ('Y', 'P');
138
139 /* Insert rows from cpg_oragems_mapping to cpg_oragems_arch*/
140
141 OPEN archival_date_cur;
142 FETCH archival_date_cur INTO v_archival_date;
143 IF (v_archival_date IS NOT NULL) THEN
144 -- BEGIN - Bug 2100687 - Put explicit INSERT and SELECT clause
145 INSERT INTO cpg_oragems_arch
146 (
147 PO_HEADER_ID ,
148 PO_LINE_ID ,
149 PO_LINE_LOCATION_ID ,
150 PO_ID ,
151 LINE_ID ,
152 PO_NO ,
153 BPO_ID ,
154 BPO_LINE_ID ,
155 RELEASE_NUM ,
156 PO_RELEASE_ID ,
157 PO_STATUS ,
158 TRANSACTION_TYPE ,
159 TIME_STAMP ,
160 LAST_UPDATE_LOGIN ,
161 LAST_UPDATE_DATE ,
162 LAST_UPDATED_BY ,
163 CREATED_BY ,
164 CREATION_DATE
165 )
166 SELECT
167 PO_HEADER_ID ,
168 PO_LINE_ID ,
169 PO_LINE_LOCATION_ID ,
170 PO_ID ,
171 LINE_ID ,
172 PO_NO ,
173 BPO_ID ,
174 BPO_LINE_ID ,
175 RELEASE_NUM ,
176 PO_RELEASE_ID ,
177 PO_STATUS ,
178 TRANSACTION_TYPE ,
179 TIME_STAMP ,
180 LAST_UPDATE_LOGIN ,
181 LAST_UPDATE_DATE ,
182 LAST_UPDATED_BY ,
183 CREATED_BY ,
184 CREATION_DATE
185 FROM cpg_oragems_mapping
186 WHERE time_stamp > nvl(v_archival_date,sysdate);
187 ELSE
188 INSERT INTO cpg_oragems_arch
189 (
190 PO_HEADER_ID ,
191 PO_LINE_ID ,
192 PO_LINE_LOCATION_ID ,
193 PO_ID ,
194 LINE_ID ,
195 PO_NO ,
196 BPO_ID ,
197 BPO_LINE_ID ,
198 RELEASE_NUM ,
199 PO_RELEASE_ID ,
200 PO_STATUS ,
201 TRANSACTION_TYPE ,
202 TIME_STAMP ,
203 LAST_UPDATE_LOGIN ,
204 LAST_UPDATE_DATE ,
205 LAST_UPDATED_BY ,
206 CREATED_BY ,
207 CREATION_DATE
208 )
209 SELECT
210 PO_HEADER_ID ,
211 PO_LINE_ID ,
212 PO_LINE_LOCATION_ID ,
213 PO_ID ,
214 LINE_ID ,
215 PO_NO ,
216 BPO_ID ,
217 BPO_LINE_ID ,
218 RELEASE_NUM ,
219 PO_RELEASE_ID ,
220 PO_STATUS ,
221 TRANSACTION_TYPE ,
222 TIME_STAMP ,
223 LAST_UPDATE_LOGIN ,
224 LAST_UPDATE_DATE ,
225 LAST_UPDATED_BY ,
226 CREATED_BY ,
227 CREATION_DATE
228 FROM cpg_oragems_mapping;
229 -- END - Bug 2100687
230 END IF;
231
232 CLOSE archival_date_cur;
233
234 EXCEPTION
235
236 WHEN OTHERS THEN
237 err_num := SQLCODE;
238 err_msg := SUBSTRB(SQLERRM, 1, 100);
239 RAISE_APPLICATION_ERROR(-20000, err_msg);
240
241 END po_tran;
242
243 END GML_LOG;