1 PACKAGE WIP_MOVE_TXN_INTERFACE_CLEANUP AUTHID CURRENT_USER AS
2 /* $Header: wipmvcls.pls 115.7 2002/11/28 13:38:28 rmahidha ship $ */
3
4 TYPE TRANSACTION_ID_T IS TABLE OF
5 WIP_MOVE_TXN_INTERFACE.TRANSACTION_ID%TYPE
6 INDEX BY BINARY_INTEGER;
7
8 TYPE LAST_UPDATE_DATE_T IS TABLE OF
9 WIP_MOVE_TXN_INTERFACE.LAST_UPDATE_DATE%TYPE
10 INDEX BY BINARY_INTEGER;
11
12 TYPE LAST_UPDATED_BY_T IS TABLE OF
13 WIP_MOVE_TXN_INTERFACE.LAST_UPDATED_BY%TYPE
14 INDEX BY BINARY_INTEGER;
15
16 TYPE LAST_UPDATED_BY_NAME_T IS TABLE OF
17 WIP_MOVE_TXN_INTERFACE.LAST_UPDATED_BY_NAME%TYPE
18 INDEX BY BINARY_INTEGER;
19
20 TYPE CREATION_DATE_T IS TABLE OF
21 WIP_MOVE_TXN_INTERFACE.CREATION_DATE%TYPE
22 INDEX BY BINARY_INTEGER;
23
24 TYPE CREATED_BY_T IS TABLE OF
25 WIP_MOVE_TXN_INTERFACE.CREATED_BY%TYPE
26 INDEX BY BINARY_INTEGER;
27
28 TYPE CREATED_BY_NAME_T IS TABLE OF
29 WIP_MOVE_TXN_INTERFACE.CREATED_BY_NAME%TYPE
30 INDEX BY BINARY_INTEGER;
31
32 TYPE LAST_UPDATE_LOGIN_T IS TABLE OF
33 WIP_MOVE_TXN_INTERFACE.LAST_UPDATE_LOGIN%TYPE
34 INDEX BY BINARY_INTEGER;
35
36 TYPE REQUEST_ID_T IS TABLE OF
37 WIP_MOVE_TXN_INTERFACE.REQUEST_ID%TYPE
38 INDEX BY BINARY_INTEGER;
39
40 TYPE PROGRAM_APPLICATION_ID_T IS TABLE OF
41 WIP_MOVE_TXN_INTERFACE.PROGRAM_APPLICATION_ID%TYPE
42 INDEX BY BINARY_INTEGER;
43
44 TYPE PROGRAM_ID_T IS TABLE OF
45 WIP_MOVE_TXN_INTERFACE.PROGRAM_ID%TYPE
46 INDEX BY BINARY_INTEGER;
47
48 TYPE PROGRAM_UPDATE_DATE_T IS TABLE OF
49 WIP_MOVE_TXN_INTERFACE.PROGRAM_UPDATE_DATE%TYPE
50 INDEX BY BINARY_INTEGER;
51
52 TYPE GROUP_ID_T IS TABLE OF
53 WIP_MOVE_TXN_INTERFACE.GROUP_ID%TYPE
54 INDEX BY BINARY_INTEGER;
55
56 TYPE SOURCE_CODE_T IS TABLE OF
57 WIP_MOVE_TXN_INTERFACE.SOURCE_CODE%TYPE
58 INDEX BY BINARY_INTEGER;
59
60 TYPE SOURCE_LINE_ID_T IS TABLE OF
61 WIP_MOVE_TXN_INTERFACE.SOURCE_LINE_ID%TYPE
62 INDEX BY BINARY_INTEGER;
63
64 TYPE PROCESS_PHASE_T IS TABLE OF
65 WIP_MOVE_TXN_INTERFACE.PROCESS_PHASE%TYPE
66 INDEX BY BINARY_INTEGER;
67
68 TYPE PROCESS_STATUS_T IS TABLE OF
69 WIP_MOVE_TXN_INTERFACE.PROCESS_STATUS%TYPE
70 INDEX BY BINARY_INTEGER;
71
72 TYPE TRANSACTION_TYPE_T IS TABLE OF
73 WIP_MOVE_TXN_INTERFACE.TRANSACTION_TYPE%TYPE
74 INDEX BY BINARY_INTEGER;
75
76 TYPE ORGANIZATION_ID_T IS TABLE OF
77 WIP_MOVE_TXN_INTERFACE.ORGANIZATION_ID%TYPE
78 INDEX BY BINARY_INTEGER;
79
80 TYPE ORGANIZATION_CODE_T IS TABLE OF
81 WIP_MOVE_TXN_INTERFACE.ORGANIZATION_CODE%TYPE
82 INDEX BY BINARY_INTEGER;
83
84 TYPE WIP_ENTITY_ID_T IS TABLE OF
85 WIP_MOVE_TXN_INTERFACE.WIP_ENTITY_ID%TYPE
86 INDEX BY BINARY_INTEGER;
87
88 TYPE WIP_ENTITY_NAME_T IS TABLE OF
89 WIP_MOVE_TXN_INTERFACE.WIP_ENTITY_NAME%TYPE
90 INDEX BY BINARY_INTEGER;
91
92 TYPE ENTITY_TYPE_T IS TABLE OF
93 WIP_MOVE_TXN_INTERFACE.ENTITY_TYPE%TYPE
94 INDEX BY BINARY_INTEGER;
95
96 TYPE PRIMARY_ITEM_ID_T IS TABLE OF
97 WIP_MOVE_TXN_INTERFACE.PRIMARY_ITEM_ID%TYPE
98 INDEX BY BINARY_INTEGER;
99
100 TYPE LINE_ID_T IS TABLE OF
101 WIP_MOVE_TXN_INTERFACE.LINE_ID%TYPE
102 INDEX BY BINARY_INTEGER;
103
104 TYPE LINE_CODE_T IS TABLE OF
105 WIP_MOVE_TXN_INTERFACE.LINE_CODE%TYPE
106 INDEX BY BINARY_INTEGER;
107
108 TYPE REPETITIVE_SCHEDULE_ID_T IS TABLE OF
109 WIP_MOVE_TXN_INTERFACE.REPETITIVE_SCHEDULE_ID%TYPE
110 INDEX BY BINARY_INTEGER;
111
112 TYPE TRANSACTION_DATE_T IS TABLE OF
113 WIP_MOVE_TXN_INTERFACE.TRANSACTION_DATE%TYPE
114 INDEX BY BINARY_INTEGER;
115
116 TYPE ACCT_PERIOD_ID_T IS TABLE OF
117 WIP_MOVE_TXN_INTERFACE.ACCT_PERIOD_ID%TYPE
118 INDEX BY BINARY_INTEGER;
119
120 TYPE FM_OPERATION_SEQ_NUM_T IS TABLE OF
121 WIP_MOVE_TXN_INTERFACE.FM_OPERATION_SEQ_NUM%TYPE
122 INDEX BY BINARY_INTEGER;
123
124 TYPE FM_OPERATION_CODE_T IS TABLE OF
125 WIP_MOVE_TXN_INTERFACE.FM_OPERATION_CODE%TYPE
126 INDEX BY BINARY_INTEGER;
127
128 TYPE FM_DEPARTMENT_ID_T IS TABLE OF
129 WIP_MOVE_TXN_INTERFACE.FM_DEPARTMENT_ID%TYPE
130 INDEX BY BINARY_INTEGER;
131
132 TYPE FM_DEPARTMENT_CODE_T IS TABLE OF
133 WIP_MOVE_TXN_INTERFACE.FM_DEPARTMENT_CODE%TYPE
134 INDEX BY BINARY_INTEGER;
135
136 TYPE FM_INTRAOPERATION_STEP_TYPE_T IS TABLE OF
137 WIP_MOVE_TXN_INTERFACE.FM_INTRAOPERATION_STEP_TYPE%TYPE
138 INDEX BY BINARY_INTEGER;
139
140 TYPE TO_OPERATION_SEQ_NUM_T IS TABLE OF
141 WIP_MOVE_TXN_INTERFACE.TO_OPERATION_SEQ_NUM%TYPE
142 INDEX BY BINARY_INTEGER;
143
144 TYPE TO_OPERATION_CODE_T IS TABLE OF
145 WIP_MOVE_TXN_INTERFACE.TO_OPERATION_CODE%TYPE
146 INDEX BY BINARY_INTEGER;
147
148 TYPE TO_DEPARTMENT_ID_T IS TABLE OF
149 WIP_MOVE_TXN_INTERFACE.TO_DEPARTMENT_ID%TYPE
150 INDEX BY BINARY_INTEGER;
151
152 TYPE TO_DEPARTMENT_CODE_T IS TABLE OF
153 WIP_MOVE_TXN_INTERFACE.TO_DEPARTMENT_CODE%TYPE
154 INDEX BY BINARY_INTEGER;
155
156 TYPE TO_INTRAOPERATION_STEP_TYPE_T IS TABLE OF
157 WIP_MOVE_TXN_INTERFACE.TO_INTRAOPERATION_STEP_TYPE%TYPE
158 INDEX BY BINARY_INTEGER;
159
160 TYPE TRANSACTION_QUANTITY_T IS TABLE OF
161 WIP_MOVE_TXN_INTERFACE.TRANSACTION_QUANTITY%TYPE
162 INDEX BY BINARY_INTEGER;
163
164 TYPE TRANSACTION_UOM_T IS TABLE OF
165 WIP_MOVE_TXN_INTERFACE.TRANSACTION_UOM%TYPE
166 INDEX BY BINARY_INTEGER;
167
168 TYPE PRIMARY_QUANTITY_T IS TABLE OF
169 WIP_MOVE_TXN_INTERFACE.PRIMARY_QUANTITY%TYPE
170 INDEX BY BINARY_INTEGER;
171
172 TYPE PRIMARY_UOM_T IS TABLE OF
173 WIP_MOVE_TXN_INTERFACE.PRIMARY_UOM%TYPE
174 INDEX BY BINARY_INTEGER;
175
176 TYPE SCRAP_ACCOUNT_ID_T IS TABLE OF
177 WIP_MOVE_TXN_INTERFACE.SCRAP_ACCOUNT_ID%TYPE
178 INDEX BY BINARY_INTEGER;
179
180 TYPE REASON_ID_T IS TABLE OF
181 WIP_MOVE_TXN_INTERFACE.REASON_ID%TYPE
182 INDEX BY BINARY_INTEGER;
183
184 TYPE REASON_NAME_T IS TABLE OF
185 WIP_MOVE_TXN_INTERFACE.REASON_NAME%TYPE
186 INDEX BY BINARY_INTEGER;
187
188 TYPE REFERENCE_T IS TABLE OF
189 WIP_MOVE_TXN_INTERFACE.REFERENCE%TYPE
190 INDEX BY BINARY_INTEGER;
191
192 TYPE ATTRIBUTE_CATEGORY_T IS TABLE OF
193 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE_CATEGORY%TYPE
194 INDEX BY BINARY_INTEGER;
195
196 TYPE ATTRIBUTE1_T IS TABLE OF
197 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE1%TYPE
198 INDEX BY BINARY_INTEGER;
199
200 TYPE ATTRIBUTE2_T IS TABLE OF
201 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE2%TYPE
202 INDEX BY BINARY_INTEGER;
203
204 TYPE ATTRIBUTE3_T IS TABLE OF
205 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE3%TYPE
206 INDEX BY BINARY_INTEGER;
207
208 TYPE ATTRIBUTE4_T IS TABLE OF
209 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE4%TYPE
210 INDEX BY BINARY_INTEGER;
211
212 TYPE ATTRIBUTE5_T IS TABLE OF
213 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE5%TYPE
214 INDEX BY BINARY_INTEGER;
215
216 TYPE ATTRIBUTE6_T IS TABLE OF
217 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE6%TYPE
218 INDEX BY BINARY_INTEGER;
219
220 TYPE ATTRIBUTE7_T IS TABLE OF
221 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE7%TYPE
222 INDEX BY BINARY_INTEGER;
223
224 TYPE ATTRIBUTE8_T IS TABLE OF
225 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE8%TYPE
226 INDEX BY BINARY_INTEGER;
227
228 TYPE ATTRIBUTE9_T IS TABLE OF
229 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE9%TYPE
230 INDEX BY BINARY_INTEGER;
231
232 TYPE ATTRIBUTE10_T IS TABLE OF
233 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE10%TYPE
234 INDEX BY BINARY_INTEGER;
235
236 TYPE ATTRIBUTE11_T IS TABLE OF
237 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE11%TYPE
238 INDEX BY BINARY_INTEGER;
239
240 TYPE ATTRIBUTE12_T IS TABLE OF
241 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE12%TYPE
242 INDEX BY BINARY_INTEGER;
243
244 TYPE ATTRIBUTE13_T IS TABLE OF
245 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE13%TYPE
246 INDEX BY BINARY_INTEGER;
247
248 TYPE ATTRIBUTE14_T IS TABLE OF
249 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE14%TYPE
250 INDEX BY BINARY_INTEGER;
251
252 TYPE ATTRIBUTE15_T IS TABLE OF
253 WIP_MOVE_TXN_INTERFACE.ATTRIBUTE15%TYPE
254 INDEX BY BINARY_INTEGER;
255
256 TYPE QA_COLLECTION_ID_T IS TABLE OF
257 WIP_MOVE_TXN_INTERFACE.QA_COLLECTION_ID%TYPE
258 INDEX BY BINARY_INTEGER;
259
260 TYPE OVERCOMPLETION_TXN_ID_T IS TABLE OF
261 MTL_MATERIAL_TRANSACTIONS_TEMP.OVERCOMPLETION_TRANSACTION_ID%TYPE
262 INDEX BY BINARY_INTEGER;
263
264 TYPE OVERCOMPLETION_TXN_QTY_T IS TABLE OF
265 MTL_MATERIAL_TRANSACTIONS_TEMP.OVERCOMPLETION_TRANSACTION_QTY%TYPE
266 INDEX BY BINARY_INTEGER;
267
268 TYPE OVERCOMPLETION_PRIMARY_QTY_T IS TABLE OF
269 MTL_MATERIAL_TRANSACTIONS_TEMP.OVERCOMPLETION_PRIMARY_QTY%TYPE
270 INDEX BY BINARY_INTEGER;
271
272 TYPE WIP_MOVE_TXN_INTERFACE_REC IS RECORD (
273 NUMRECS NUMBER,
274 TRANSACTION_ID TRANSACTION_ID_T,
275 LAST_UPDATE_DATE LAST_UPDATE_DATE_T,
276 LAST_UPDATED_BY LAST_UPDATED_BY_T,
277 LAST_UPDATED_BY_NAME LAST_UPDATED_BY_NAME_T,
278 CREATION_DATE CREATION_DATE_T,
279 CREATED_BY CREATED_BY_T,
280 CREATED_BY_NAME CREATED_BY_NAME_T,
281 LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_T,
282 REQUEST_ID REQUEST_ID_T,
283 PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID_T,
284 PROGRAM_ID PROGRAM_ID_T,
285 PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE_T,
286 GROUP_ID GROUP_ID_T,
287 SOURCE_CODE SOURCE_CODE_T,
288 SOURCE_LINE_ID SOURCE_LINE_ID_T,
289 PROCESS_PHASE PROCESS_PHASE_T,
290 PROCESS_STATUS PROCESS_STATUS_T,
291 TRANSACTION_TYPE TRANSACTION_TYPE_T,
292 ORGANIZATION_ID ORGANIZATION_ID_T,
293 ORGANIZATION_CODE ORGANIZATION_CODE_T,
294 WIP_ENTITY_ID WIP_ENTITY_ID_T,
295 WIP_ENTITY_NAME WIP_ENTITY_NAME_T,
296 ENTITY_TYPE ENTITY_TYPE_T,
297 PRIMARY_ITEM_ID PRIMARY_ITEM_ID_T,
298 LINE_ID LINE_ID_T,
299 LINE_CODE LINE_CODE_T,
300 REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID_T,
301 TRANSACTION_DATE TRANSACTION_DATE_T,
302 ACCT_PERIOD_ID ACCT_PERIOD_ID_T,
303 FM_OPERATION_SEQ_NUM FM_OPERATION_SEQ_NUM_T,
304 FM_OPERATION_CODE FM_OPERATION_CODE_T,
305 FM_DEPARTMENT_ID FM_DEPARTMENT_ID_T,
306 FM_DEPARTMENT_CODE FM_DEPARTMENT_CODE_T,
307 FM_INTRAOPERATION_STEP_TYPE FM_INTRAOPERATION_STEP_TYPE_T,
308 TO_OPERATION_SEQ_NUM TO_OPERATION_SEQ_NUM_T,
309 TO_OPERATION_CODE TO_OPERATION_CODE_T,
310 TO_DEPARTMENT_ID TO_DEPARTMENT_ID_T,
311 TO_DEPARTMENT_CODE TO_DEPARTMENT_CODE_T,
312 TO_INTRAOPERATION_STEP_TYPE TO_INTRAOPERATION_STEP_TYPE_T,
313 TRANSACTION_QUANTITY TRANSACTION_QUANTITY_T,
314 TRANSACTION_UOM TRANSACTION_UOM_T,
315 PRIMARY_QUANTITY PRIMARY_QUANTITY_T,
316 PRIMARY_UOM PRIMARY_UOM_T,
317 SCRAP_ACCOUNT_ID SCRAP_ACCOUNT_ID_T,
318 REASON_ID REASON_ID_T,
319 REASON_NAME REASON_NAME_T,
320 REFERENCE REFERENCE_T,
321 ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY_T,
322 ATTRIBUTE1 ATTRIBUTE1_T,
323 ATTRIBUTE2 ATTRIBUTE2_T,
324 ATTRIBUTE3 ATTRIBUTE3_T,
325 ATTRIBUTE4 ATTRIBUTE4_T,
326 ATTRIBUTE5 ATTRIBUTE5_T,
327 ATTRIBUTE6 ATTRIBUTE6_T,
328 ATTRIBUTE7 ATTRIBUTE7_T,
329 ATTRIBUTE8 ATTRIBUTE8_T,
330 ATTRIBUTE9 ATTRIBUTE9_T,
331 ATTRIBUTE10 ATTRIBUTE10_T,
332 ATTRIBUTE11 ATTRIBUTE11_T,
333 ATTRIBUTE12 ATTRIBUTE12_T,
334 ATTRIBUTE13 ATTRIBUTE13_T,
335 ATTRIBUTE14 ATTRIBUTE14_T,
336 ATTRIBUTE15 ATTRIBUTE15_T,
337 QA_COLLECTION_ID qa_collection_id_t,
338 OVERCOMPLETION_TRANSACTION_ID OVERCOMPLETION_TXN_ID_T,
339 OVERCOMPLETION_TRANSACTION_QTY OVERCOMPLETION_TXN_QTY_T,
340 OVERCOMPLETION_PRIMARY_QTY OVERCOMPLETION_PRIMARY_QTY_T
341 );
342
343 procedure fetch_and_delete(
344 p_grp_id in number,
345 p_moves in out nocopy wip_move_txn_interface_rec);
346
347 procedure insert_rows(
348 p_moves in wip_move_txn_interface_rec);
349
350 END WIP_MOVE_TXN_INTERFACE_CLEANUP;