[Home] [Help]
PACKAGE BODY: APPS.WIP_MTL_TXNS_TEMP_CLEANUP
Source
1 PACKAGE BODY WIP_MTL_TXNS_TEMP_CLEANUP AS
2 /* $Header: wipmtclb.pls 115.7 2002/11/28 13:28:53 rmahidha ship $ */
3
4 procedure fetch_and_delete(
5 p_hdr_id in number,
6 p_act_id in number default NULL,
7 p_mtls in out nocopy mtl_transactions_temp_rec) is
8
9 i number := 0;
10
11 cursor get_materials(
12 c_hdr_id number,
13 c_act_id number) is
14 SELECT
15 TRANSACTION_HEADER_ID,
16 TRANSACTION_TEMP_ID,
17 SOURCE_CODE,
18 SOURCE_LINE_ID,
19 TRANSACTION_MODE,
20 LOCK_FLAG,
21 LAST_UPDATE_DATE,
22 LAST_UPDATED_BY,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_LOGIN,
26 REQUEST_ID,
27 PROGRAM_APPLICATION_ID,
28 PROGRAM_ID,
29 PROGRAM_UPDATE_DATE,
30 INVENTORY_ITEM_ID,
31 REVISION,
32 ORGANIZATION_ID,
33 SUBINVENTORY_CODE,
34 LOCATOR_ID,
35 TRANSACTION_QUANTITY,
36 PRIMARY_QUANTITY,
37 TRANSACTION_UOM,
38 TRANSACTION_COST,
39 TRANSACTION_TYPE_ID,
40 TRANSACTION_ACTION_ID,
41 TRANSACTION_SOURCE_TYPE_ID,
42 TRANSACTION_SOURCE_ID,
43 TRANSACTION_SOURCE_NAME,
44 TRANSACTION_DATE,
45 ACCT_PERIOD_ID,
46 DISTRIBUTION_ACCOUNT_ID,
47 TRANSACTION_REFERENCE,
48 REASON_ID,
49 LOT_NUMBER,
50 LOT_EXPIRATION_DATE,
51 SERIAL_NUMBER,
52 RECEIVING_DOCUMENT,
53 RCV_TRANSACTION_ID,
54 MOVE_TRANSACTION_ID,
55 COMPLETION_TRANSACTION_ID,
56 WIP_ENTITY_TYPE,
57 SCHEDULE_ID,
58 REPETITIVE_LINE_ID,
59 EMPLOYEE_CODE,
60 SCHEDULE_UPDATE_CODE,
61 SETUP_TEARDOWN_CODE,
62 ITEM_ORDERING,
63 NEGATIVE_REQ_FLAG,
64 OPERATION_SEQ_NUM,
65 PICKING_LINE_ID,
66 TRX_SOURCE_LINE_ID,
67 TRX_SOURCE_DELIVERY_ID,
68 PHYSICAL_ADJUSTMENT_ID,
69 CYCLE_COUNT_ID,
70 RMA_LINE_ID,
71 CUSTOMER_SHIP_ID,
72 CURRENCY_CODE,
73 CURRENCY_CONVERSION_RATE,
74 CURRENCY_CONVERSION_TYPE,
75 CURRENCY_CONVERSION_DATE,
76 USSGL_TRANSACTION_CODE,
77 VENDOR_LOT_NUMBER,
78 ENCUMBRANCE_ACCOUNT,
79 ENCUMBRANCE_AMOUNT,
80 SHIP_TO_LOCATION,
81 SHIPMENT_NUMBER,
82 TRANSFER_COST,
83 TRANSPORTATION_COST,
84 TRANSPORTATION_ACCOUNT,
85 FREIGHT_CODE,
86 CONTAINERS,
87 WAYBILL_AIRBILL,
88 EXPECTED_ARRIVAL_DATE,
89 TRANSFER_SUBINVENTORY,
90 TRANSFER_ORGANIZATION,
91 TRANSFER_TO_LOCATION,
92 NEW_AVERAGE_COST,
93 VALUE_CHANGE,
94 PERCENTAGE_CHANGE,
95 MATERIAL_ALLOCATION_TEMP_ID,
96 DEMAND_SOURCE_HEADER_ID,
97 DEMAND_SOURCE_LINE,
98 DEMAND_SOURCE_DELIVERY,
99 ITEM_SEGMENTS,
100 ITEM_DESCRIPTION,
101 ITEM_TRX_ENABLED_FLAG,
102 ITEM_LOCATION_CONTROL_CODE,
103 ITEM_RESTRICT_SUBINV_CODE,
104 ITEM_RESTRICT_LOCATORS_CODE,
105 ITEM_REVISION_QTY_CONTROL_CODE,
106 ITEM_PRIMARY_UOM_CODE,
107 ITEM_UOM_CLASS,
108 ITEM_SHELF_LIFE_CODE,
109 ITEM_SHELF_LIFE_DAYS,
110 ITEM_LOT_CONTROL_CODE,
111 ITEM_SERIAL_CONTROL_CODE,
112 ALLOWED_UNITS_LOOKUP_CODE,
113 DEPARTMENT_ID,
114 WIP_SUPPLY_TYPE,
115 SUPPLY_SUBINVENTORY,
116 SUPPLY_LOCATOR_ID,
117 VALID_SUBINVENTORY_FLAG,
118 VALID_LOCATOR_FLAG,
119 LOCATOR_SEGMENTS,
120 CURRENT_LOCATOR_CONTROL_CODE,
121 NUMBER_OF_LOTS_ENTERED,
122 WIP_COMMIT_FLAG,
123 NEXT_LOT_NUMBER,
124 LOT_ALPHA_PREFIX,
125 NEXT_SERIAL_NUMBER,
126 SERIAL_ALPHA_PREFIX,
127 POSTING_FLAG,
128 REQUIRED_FLAG,
129 PROCESS_FLAG,
130 ERROR_CODE,
131 ATTRIBUTE_CATEGORY,
132 ATTRIBUTE1,
133 ATTRIBUTE2,
134 ATTRIBUTE3,
135 ATTRIBUTE4,
136 ATTRIBUTE5,
137 ATTRIBUTE6,
138 ATTRIBUTE7,
139 ATTRIBUTE8,
140 ATTRIBUTE9,
141 ATTRIBUTE10,
142 ATTRIBUTE11,
143 ATTRIBUTE12,
144 ATTRIBUTE13,
145 ATTRIBUTE14,
146 ATTRIBUTE15,
147 PRIMARY_SWITCH,
148 DEPARTMENT_CODE,
149 ERROR_EXPLANATION,
150 DEMAND_ID,
151 ITEM_INVENTORY_ASSET_FLAG,
152 SHIPPABLE_FLAG,
153 REQUISITION_LINE_ID,
154 REQUISITION_DISTRIBUTION_ID,
155 MOVEMENT_ID,
156 RESERVATION_QUANTITY,
157 SHIPPED_QUANTITY,
158 TRANSACTION_LINE_NUMBER,
159 EXPENDITURE_TYPE,
160 FINAL_COMPLETION_FLAG,
161 MATERIAL_ACCOUNT,
162 MATERIAL_OVERHEAD_ACCOUNT,
163 OUTSIDE_PROCESSING_ACCOUNT,
164 OVERHEAD_ACCOUNT,
165 PA_EXPENDITURE_ORG_ID,
166 PROJECT_ID,
167 RESOURCE_ACCOUNT,
168 SOURCE_PROJECT_ID,
169 SOURCE_TASK_ID,
170 TASK_ID,
171 TO_PROJECT_ID,
172 TO_TASK_ID,
173 TRANSACTION_SEQUENCE_ID,
174 transfer_percentage,
175 qa_collection_id,
176 overcompletion_transaction_id,
177 overcompletion_transaction_qty,
178 overcompletion_primary_qty,
179 kanban_card_id
180 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
181 WHERE TRANSACTION_HEADER_ID = c_hdr_id
182 AND TRANSACTION_ACTION_ID = NVL(c_act_id, TRANSACTION_ACTION_ID);
183
184 mtl_rec get_materials%rowtype;
185 begin
186 -- initialize
187 if (p_mtls.numrecs is NULL) then
188 -- p_mtls is empty
189 p_mtls.numrecs := i;
190 else
191 -- p_mtls already has records
192 i := p_mtls.numrecs;
193 end if;
194
195 open get_materials(
196 c_hdr_id => p_hdr_id,
197 c_act_id => p_act_id);
198
199 loop
200 fetch get_materials into mtl_rec;
201
202 exit when (get_materials%NOTFOUND);
203
204 i := i + 1;
205 p_mtls.numrecs := i;
206 p_mtls.TRANSACTION_HEADER_ID(i) := mtl_rec.TRANSACTION_HEADER_ID;
207 p_mtls.TRANSACTION_TEMP_ID(i) := mtl_rec.TRANSACTION_TEMP_ID;
208 p_mtls.SOURCE_CODE(i) := mtl_rec.SOURCE_CODE;
209 p_mtls.SOURCE_LINE_ID(i) := mtl_rec.SOURCE_LINE_ID;
210 p_mtls.TRANSACTION_MODE(i) := mtl_rec.TRANSACTION_MODE;
211 p_mtls.LOCK_FLAG(i) := mtl_rec.LOCK_FLAG;
212 p_mtls.LAST_UPDATE_DATE(i) := mtl_rec.LAST_UPDATE_DATE;
213 p_mtls.LAST_UPDATED_BY(i) := mtl_rec.LAST_UPDATED_BY;
214 p_mtls.CREATION_DATE(i) := mtl_rec.CREATION_DATE;
215 p_mtls.CREATED_BY(i) := mtl_rec.CREATED_BY;
216 p_mtls.LAST_UPDATE_LOGIN(i) := mtl_rec.LAST_UPDATE_LOGIN;
217 p_mtls.REQUEST_ID(i) := mtl_rec.REQUEST_ID;
218 p_mtls.PROGRAM_APPLICATION_ID(i) := mtl_rec.PROGRAM_APPLICATION_ID;
219 p_mtls.PROGRAM_ID(i) := mtl_rec.PROGRAM_ID;
220 p_mtls.PROGRAM_UPDATE_DATE(i) := mtl_rec.PROGRAM_UPDATE_DATE;
221 p_mtls.INVENTORY_ITEM_ID(i) := mtl_rec.INVENTORY_ITEM_ID;
222 p_mtls.REVISION(i) := mtl_rec.REVISION;
223 p_mtls.ORGANIZATION_ID(i) := mtl_rec.ORGANIZATION_ID;
224 p_mtls.SUBINVENTORY_CODE(i) := mtl_rec.SUBINVENTORY_CODE;
225 p_mtls.LOCATOR_ID(i) := mtl_rec.LOCATOR_ID;
226 p_mtls.TRANSACTION_QUANTITY(i) := mtl_rec.TRANSACTION_QUANTITY;
227 p_mtls.PRIMARY_QUANTITY(i) := mtl_rec.PRIMARY_QUANTITY;
228 p_mtls.TRANSACTION_UOM(i) := mtl_rec.TRANSACTION_UOM;
229 p_mtls.TRANSACTION_COST(i) := mtl_rec.TRANSACTION_COST;
230 p_mtls.TRANSACTION_TYPE_ID(i) := mtl_rec.TRANSACTION_TYPE_ID;
231 p_mtls.TRANSACTION_ACTION_ID(i) := mtl_rec.TRANSACTION_ACTION_ID;
232 p_mtls.TRANSACTION_SOURCE_TYPE_ID(i) := mtl_rec.TRANSACTION_SOURCE_TYPE_ID;
233 p_mtls.TRANSACTION_SOURCE_ID(i) := mtl_rec.TRANSACTION_SOURCE_ID;
234 p_mtls.TRANSACTION_SOURCE_NAME(i) := mtl_rec.TRANSACTION_SOURCE_NAME;
235 p_mtls.TRANSACTION_DATE(i) := mtl_rec.TRANSACTION_DATE;
236 p_mtls.ACCT_PERIOD_ID(i) := mtl_rec.ACCT_PERIOD_ID;
237 p_mtls.DISTRIBUTION_ACCOUNT_ID(i) := mtl_rec.DISTRIBUTION_ACCOUNT_ID;
238 p_mtls.TRANSACTION_REFERENCE(i) := mtl_rec.TRANSACTION_REFERENCE;
239 p_mtls.REASON_ID(i) := mtl_rec.REASON_ID;
240 p_mtls.LOT_NUMBER(i) := mtl_rec.LOT_NUMBER;
241 p_mtls.LOT_EXPIRATION_DATE(i) := mtl_rec.LOT_EXPIRATION_DATE;
242 p_mtls.SERIAL_NUMBER(i) := mtl_rec.SERIAL_NUMBER;
243 p_mtls.RECEIVING_DOCUMENT(i) := mtl_rec.RECEIVING_DOCUMENT;
244 p_mtls.RCV_TRANSACTION_ID(i) := mtl_rec.RCV_TRANSACTION_ID;
245 p_mtls.MOVE_TRANSACTION_ID(i) := mtl_rec.MOVE_TRANSACTION_ID;
246 p_mtls.COMPLETION_TRANSACTION_ID(i) := mtl_rec.COMPLETION_TRANSACTION_ID;
247 p_mtls.WIP_ENTITY_TYPE(i) := mtl_rec.WIP_ENTITY_TYPE;
248 p_mtls.SCHEDULE_ID(i) := mtl_rec.SCHEDULE_ID;
249 p_mtls.REPETITIVE_LINE_ID(i) := mtl_rec.REPETITIVE_LINE_ID;
250 p_mtls.EMPLOYEE_CODE(i) := mtl_rec.EMPLOYEE_CODE;
251 p_mtls.SCHEDULE_UPDATE_CODE(i) := mtl_rec.SCHEDULE_UPDATE_CODE;
252 p_mtls.SETUP_TEARDOWN_CODE(i) := mtl_rec.SETUP_TEARDOWN_CODE;
253 p_mtls.ITEM_ORDERING(i) := mtl_rec.ITEM_ORDERING;
254 p_mtls.NEGATIVE_REQ_FLAG(i) := mtl_rec.NEGATIVE_REQ_FLAG;
255 p_mtls.OPERATION_SEQ_NUM(i) := mtl_rec.OPERATION_SEQ_NUM;
256 p_mtls.PICKING_LINE_ID(i) := mtl_rec.PICKING_LINE_ID;
257 p_mtls.TRX_SOURCE_LINE_ID(i) := mtl_rec.TRX_SOURCE_LINE_ID;
258 p_mtls.TRX_SOURCE_DELIVERY_ID(i) := mtl_rec.TRX_SOURCE_DELIVERY_ID;
259 p_mtls.PHYSICAL_ADJUSTMENT_ID(i) := mtl_rec.PHYSICAL_ADJUSTMENT_ID;
260 p_mtls.CYCLE_COUNT_ID(i) := mtl_rec.CYCLE_COUNT_ID;
261 p_mtls.RMA_LINE_ID(i) := mtl_rec.RMA_LINE_ID;
262 p_mtls.CUSTOMER_SHIP_ID(i) := mtl_rec.CUSTOMER_SHIP_ID;
263 p_mtls.CURRENCY_CODE(i) := mtl_rec.CURRENCY_CODE;
264 p_mtls.CURRENCY_CONVERSION_RATE(i) := mtl_rec.CURRENCY_CONVERSION_RATE;
265 p_mtls.CURRENCY_CONVERSION_TYPE(i) := mtl_rec.CURRENCY_CONVERSION_TYPE;
266 p_mtls.CURRENCY_CONVERSION_DATE(i) := mtl_rec.CURRENCY_CONVERSION_DATE;
267 p_mtls.USSGL_TRANSACTION_CODE(i) := mtl_rec.USSGL_TRANSACTION_CODE;
268 p_mtls.VENDOR_LOT_NUMBER(i) := mtl_rec.VENDOR_LOT_NUMBER;
269 p_mtls.ENCUMBRANCE_ACCOUNT(i) := mtl_rec.ENCUMBRANCE_ACCOUNT;
270 p_mtls.ENCUMBRANCE_AMOUNT(i) := mtl_rec.ENCUMBRANCE_AMOUNT;
271 p_mtls.SHIP_TO_LOCATION(i) := mtl_rec.SHIP_TO_LOCATION;
272 p_mtls.SHIPMENT_NUMBER(i) := mtl_rec.SHIPMENT_NUMBER;
273 p_mtls.TRANSFER_COST(i) := mtl_rec.TRANSFER_COST;
274 p_mtls.TRANSPORTATION_COST(i) := mtl_rec.TRANSPORTATION_COST;
275 p_mtls.TRANSPORTATION_ACCOUNT(i) := mtl_rec.TRANSPORTATION_ACCOUNT;
276 p_mtls.FREIGHT_CODE(i) := mtl_rec.FREIGHT_CODE;
277 p_mtls.CONTAINERS(i) := mtl_rec.CONTAINERS;
278 p_mtls.WAYBILL_AIRBILL(i) := mtl_rec.WAYBILL_AIRBILL;
279 p_mtls.EXPECTED_ARRIVAL_DATE(i) := mtl_rec.EXPECTED_ARRIVAL_DATE;
280 p_mtls.TRANSFER_SUBINVENTORY(i) := mtl_rec.TRANSFER_SUBINVENTORY;
281 p_mtls.TRANSFER_ORGANIZATION(i) := mtl_rec.TRANSFER_ORGANIZATION;
282 p_mtls.TRANSFER_TO_LOCATION(i) := mtl_rec.TRANSFER_TO_LOCATION;
283 p_mtls.NEW_AVERAGE_COST(i) := mtl_rec.NEW_AVERAGE_COST;
284 p_mtls.VALUE_CHANGE(i) := mtl_rec.VALUE_CHANGE;
285 p_mtls.PERCENTAGE_CHANGE(i) := mtl_rec.PERCENTAGE_CHANGE;
286 p_mtls.MATERIAL_ALLOCATION_TEMP_ID(i) := mtl_rec.MATERIAL_ALLOCATION_TEMP_ID;
287 p_mtls.DEMAND_SOURCE_HEADER_ID(i) := mtl_rec.DEMAND_SOURCE_HEADER_ID;
288 p_mtls.DEMAND_SOURCE_LINE(i) := mtl_rec.DEMAND_SOURCE_LINE;
289 p_mtls.DEMAND_SOURCE_DELIVERY(i) := mtl_rec.DEMAND_SOURCE_DELIVERY;
290 p_mtls.ITEM_SEGMENTS(i) := mtl_rec.ITEM_SEGMENTS;
291 p_mtls.ITEM_DESCRIPTION(i) := mtl_rec.ITEM_DESCRIPTION;
292 p_mtls.ITEM_TRX_ENABLED_FLAG(i) := mtl_rec.ITEM_TRX_ENABLED_FLAG;
293 p_mtls.ITEM_LOCATION_CONTROL_CODE(i) := mtl_rec.ITEM_LOCATION_CONTROL_CODE;
294 p_mtls.ITEM_RESTRICT_SUBINV_CODE(i) := mtl_rec.ITEM_RESTRICT_SUBINV_CODE;
295 p_mtls.ITEM_RESTRICT_LOCATORS_CODE(i) := mtl_rec.ITEM_RESTRICT_LOCATORS_CODE;
296 p_mtls.ITEM_REVISION_QTY_CONTROL_CODE(i) := mtl_rec.ITEM_REVISION_QTY_CONTROL_CODE;
297 p_mtls.ITEM_UOM_CLASS(i) := mtl_rec.ITEM_UOM_CLASS;
298 p_mtls.ITEM_PRIMARY_UOM_CODE(i) := mtl_rec.ITEM_PRIMARY_UOM_CODE;
299 p_mtls.ITEM_SHELF_LIFE_CODE(i) := mtl_rec.ITEM_SHELF_LIFE_CODE;
300 p_mtls.ITEM_SHELF_LIFE_DAYS(i) := mtl_rec.ITEM_SHELF_LIFE_DAYS;
301 p_mtls.ITEM_LOT_CONTROL_CODE(i) := mtl_rec.ITEM_LOT_CONTROL_CODE;
302 p_mtls.ITEM_SERIAL_CONTROL_CODE(i) := mtl_rec.ITEM_SERIAL_CONTROL_CODE;
303 p_mtls.ALLOWED_UNITS_LOOKUP_CODE(i) := mtl_rec.ALLOWED_UNITS_LOOKUP_CODE;
304 p_mtls.DEPARTMENT_ID(i) := mtl_rec.DEPARTMENT_ID;
305 p_mtls.WIP_SUPPLY_TYPE(i) := mtl_rec.WIP_SUPPLY_TYPE;
306 p_mtls.SUPPLY_SUBINVENTORY(i) := mtl_rec.SUPPLY_SUBINVENTORY;
307 p_mtls.SUPPLY_LOCATOR_ID(i) := mtl_rec.SUPPLY_LOCATOR_ID;
308 p_mtls.VALID_SUBINVENTORY_FLAG(i) := mtl_rec.VALID_SUBINVENTORY_FLAG;
309 p_mtls.VALID_LOCATOR_FLAG(i) := mtl_rec.VALID_LOCATOR_FLAG;
310 p_mtls.LOCATOR_SEGMENTS(i) := mtl_rec.LOCATOR_SEGMENTS;
311 p_mtls.CURRENT_LOCATOR_CONTROL_CODE(i) := mtl_rec.CURRENT_LOCATOR_CONTROL_CODE;
312 p_mtls.NUMBER_OF_LOTS_ENTERED(i) := mtl_rec.NUMBER_OF_LOTS_ENTERED;
313 p_mtls.WIP_COMMIT_FLAG(i) := mtl_rec.WIP_COMMIT_FLAG;
314 p_mtls.NEXT_LOT_NUMBER(i) := mtl_rec.NEXT_LOT_NUMBER;
315 p_mtls.LOT_ALPHA_PREFIX(i) := mtl_rec.LOT_ALPHA_PREFIX;
316 p_mtls.NEXT_SERIAL_NUMBER(i) := mtl_rec.NEXT_SERIAL_NUMBER;
317 p_mtls.SERIAL_ALPHA_PREFIX(i) := mtl_rec.SERIAL_ALPHA_PREFIX;
318 p_mtls.POSTING_FLAG(i) := mtl_rec.POSTING_FLAG;
319 p_mtls.REQUIRED_FLAG(i) := mtl_rec.REQUIRED_FLAG;
320 p_mtls.PROCESS_FLAG(i) := mtl_rec.PROCESS_FLAG;
321 p_mtls.ERROR_CODE(i) := mtl_rec.ERROR_CODE;
322 p_mtls.ATTRIBUTE_CATEGORY(i) := mtl_rec.ATTRIBUTE_CATEGORY;
323 p_mtls.ATTRIBUTE1(i) := mtl_rec.ATTRIBUTE1;
324 p_mtls.ATTRIBUTE2(i) := mtl_rec.ATTRIBUTE2;
325 p_mtls.ATTRIBUTE3(i) := mtl_rec.ATTRIBUTE3;
326 p_mtls.ATTRIBUTE4(i) := mtl_rec.ATTRIBUTE4;
327 p_mtls.ATTRIBUTE5(i) := mtl_rec.ATTRIBUTE5;
328 p_mtls.ATTRIBUTE6(i) := mtl_rec.ATTRIBUTE6;
329 p_mtls.ATTRIBUTE7(i) := mtl_rec.ATTRIBUTE7;
330 p_mtls.ATTRIBUTE8(i) := mtl_rec.ATTRIBUTE8;
331 p_mtls.ATTRIBUTE9(i) := mtl_rec.ATTRIBUTE9;
332 p_mtls.ATTRIBUTE10(i) := mtl_rec.ATTRIBUTE10;
333 p_mtls.ATTRIBUTE11(i) := mtl_rec.ATTRIBUTE11;
334 p_mtls.ATTRIBUTE12(i) := mtl_rec.ATTRIBUTE12;
335 p_mtls.ATTRIBUTE13(i) := mtl_rec.ATTRIBUTE13;
336 p_mtls.ATTRIBUTE14(i) := mtl_rec.ATTRIBUTE14;
337 p_mtls.ATTRIBUTE15(i) := mtl_rec.ATTRIBUTE15;
338 p_mtls.PRIMARY_SWITCH(i) := mtl_rec.PRIMARY_SWITCH;
339 p_mtls.DEPARTMENT_CODE(i) := mtl_rec.DEPARTMENT_CODE;
340 p_mtls.ERROR_EXPLANATION(i) := mtl_rec.ERROR_EXPLANATION;
341 p_mtls.DEMAND_ID(i) := mtl_rec.DEMAND_ID;
342 p_mtls.ITEM_INVENTORY_ASSET_FLAG(i) := mtl_rec.ITEM_INVENTORY_ASSET_FLAG;
343 p_mtls.SHIPPABLE_FLAG(i) := mtl_rec.SHIPPABLE_FLAG;
344 p_mtls.REQUISITION_LINE_ID(i) := mtl_rec.REQUISITION_LINE_ID;
345 p_mtls.REQUISITION_DISTRIBUTION_ID(i) := mtl_rec.REQUISITION_DISTRIBUTION_ID;
346 p_mtls.MOVEMENT_ID(i) := mtl_rec.MOVEMENT_ID;
347 p_mtls.RESERVATION_QUANTITY(i) := mtl_rec.RESERVATION_QUANTITY;
348 p_mtls.SHIPPED_QUANTITY(i) := mtl_rec.SHIPPED_QUANTITY;
349 p_mtls.TRANSACTION_LINE_NUMBER(i) := mtl_rec.TRANSACTION_LINE_NUMBER;
350 p_mtls.EXPENDITURE_TYPE(i) := mtl_rec.EXPENDITURE_TYPE;
351 p_mtls.FINAL_COMPLETION_FLAG(i) := mtl_rec.FINAL_COMPLETION_FLAG;
352 p_mtls.MATERIAL_ACCOUNT(i) := mtl_rec.MATERIAL_ACCOUNT;
353 p_mtls.MATERIAL_OVERHEAD_ACCOUNT(i) := mtl_rec.MATERIAL_OVERHEAD_ACCOUNT;
354 p_mtls.OUTSIDE_PROCESSING_ACCOUNT(i) := mtl_rec.OUTSIDE_PROCESSING_ACCOUNT;
355 p_mtls.OVERHEAD_ACCOUNT(i) := mtl_rec.OVERHEAD_ACCOUNT;
356 p_mtls.PA_EXPENDITURE_ORG_ID(i) := mtl_rec.PA_EXPENDITURE_ORG_ID;
357 p_mtls.PROJECT_ID(i) := mtl_rec.PROJECT_ID;
358 p_mtls.RESOURCE_ACCOUNT(i) := mtl_rec.RESOURCE_ACCOUNT;
359 p_mtls.SOURCE_PROJECT_ID(i) := mtl_rec.SOURCE_PROJECT_ID;
360 p_mtls.SOURCE_TASK_ID(i) := mtl_rec.SOURCE_TASK_ID;
361 p_mtls.TASK_ID(i) := mtl_rec.TASK_ID;
362 p_mtls.TO_PROJECT_ID(i) := mtl_rec.TO_PROJECT_ID;
363 p_mtls.TO_TASK_ID(i) := mtl_rec.TO_TASK_ID;
364 p_mtls.TRANSACTION_SEQUENCE_ID(i) := mtl_rec.TRANSACTION_SEQUENCE_ID;
365 p_mtls.TRANSFER_PERCENTAGE(i) := mtl_rec.TRANSFER_PERCENTAGE;
366 p_mtls.qa_collection_id(i) := mtl_rec.qa_collection_id;
367 p_mtls.overcompletion_transaction_id(i) := mtl_rec.overcompletion_transaction_id;
368 p_mtls.overcompletion_transaction_qty(i) := mtl_rec.overcompletion_transaction_qty;
369 p_mtls.overcompletion_primary_qty(i) := mtl_rec.overcompletion_primary_qty;
370 p_mtls.kanban_card_id(i) := mtl_rec.kanban_card_id;
371 end loop;
372
373 close get_materials;
374
375 if (p_mtls.numrecs > 0) then
376 DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
377 WHERE TRANSACTION_HEADER_ID = p_hdr_id
378 AND TRANSACTION_ACTION_ID = NVL(p_act_id, TRANSACTION_ACTION_ID);
379 end if;
380 end fetch_and_delete;
381
382 procedure fetch_and_delete(
383 p_hdr_id in number,
384 p_act_id in number,
385 p_lots in out nocopy wip_lot_temp_cleanup.mtl_transaction_lots_temp_rec) is
386
387 i number := 0;
388
389 cursor get_lots(
390 c_hdr_id number,
391 c_act_id number) is
392 select
393 transaction_temp_id,
394 last_update_date,
395 last_updated_by,
396 creation_date,
397 created_by,
398 last_update_login,
399 request_id,
400 program_application_id,
401 program_id,
402 program_update_date,
403 transaction_quantity,
404 primary_quantity,
405 lot_number,
406 lot_expiration_date,
407 error_code,
408 serial_transaction_temp_id,
409 group_header_id
410 from mtl_transaction_lots_temp
411 where transaction_temp_id in
412 (select transaction_temp_id
413 from mtl_material_transactions_temp
414 where transaction_header_id = c_hdr_id
415 and transaction_action_id = nvl(c_act_id, transaction_action_id));
416
417 lot_rec get_lots%rowtype;
418 begin
419 -- initialize
420 if (p_lots.numrecs is NULL) then
421 -- p_lots is empty
422 p_lots.numrecs := i;
423 else
424 -- p_lots already has records
425 i := p_lots.numrecs;
426 end if;
427
428 open get_lots(
429 c_hdr_id => p_hdr_id,
430 c_act_id => p_act_id);
431
432 loop
433 fetch get_lots into lot_rec;
434
435 exit when (get_lots%NOTFOUND);
436
437 i := i + 1;
438 p_lots.numrecs := i;
439 p_lots.transaction_temp_id(i) := lot_rec.transaction_temp_id;
440 p_lots.last_update_date(i) := lot_rec.last_update_date;
441 p_lots.last_updated_by(i) := lot_rec.last_updated_by;
442 p_lots.creation_date(i) := lot_rec.creation_date;
443 p_lots.created_by(i) := lot_rec.created_by;
444 p_lots.last_update_login(i) := lot_rec.last_update_login;
445 p_lots.request_id(i) := lot_rec.request_id;
446 p_lots.program_application_id(i) := lot_rec.program_application_id;
447 p_lots.program_id(i) := lot_rec.program_id;
448 p_lots.program_update_date(i) := lot_rec.program_update_date;
449 p_lots.transaction_quantity(i) := lot_rec.transaction_quantity;
450 p_lots.primary_quantity(i) := lot_rec.primary_quantity;
451 p_lots.lot_number(i) := lot_rec.lot_number;
452 p_lots.lot_expiration_date(i) := lot_rec.lot_expiration_date;
453 p_lots.error_code(i) := lot_rec.error_code;
454 p_lots.serial_transaction_temp_id(i) := lot_rec.serial_transaction_temp_id;
455 p_lots.group_header_id(i) := lot_rec.group_header_id;
456 end loop;
457
458 close get_lots;
459
460 if (p_lots.numrecs > 0) then
461 delete from mtl_transaction_lots_temp
462 where transaction_temp_id in
463 (select transaction_temp_id
464 from mtl_material_transactions_temp
465 where transaction_header_id = p_hdr_id
466 and transaction_action_id = nvl(p_act_id, transaction_action_id));
467 end if;
468 end fetch_and_delete;
469
470 procedure fetch_and_delete(
471 p_hdr_id in number,
472 p_act_id in number,
473 p_serials in out nocopy wip_serial_temp_cleanup.mtl_serial_numbers_temp_rec) is
474
475 i number := 0;
476
477 cursor get_serials(
478 c_hdr_id number,
479 c_act_id number) is
480 select
481 transaction_temp_id,
482 last_update_date,
483 last_updated_by,
484 creation_date,
485 created_by,
486 last_update_login,
487 request_id,
488 program_application_id,
489 program_id,
490 program_update_date,
491 vendor_serial_number,
492 vendor_lot_number,
493 fm_serial_number,
494 to_serial_number,
495 serial_prefix,
496 error_code error_code,
497 group_header_id
498 from mtl_serial_numbers_temp
499 where
500 transaction_temp_id in
501 (select transaction_temp_id
502 from mtl_material_transactions_temp
503 where transaction_header_id = c_hdr_id
504 and transaction_action_id = nvl(c_act_id, transaction_action_id))
505 or
506 transaction_temp_id in
507 (select serial_transaction_temp_id
508 from mtl_transaction_lots_temp
509 where transaction_temp_id in
510 (select transaction_temp_id
511 from mtl_material_transactions_temp
512 where transaction_header_id = c_hdr_id
513 and transaction_action_id = nvl(c_act_id, transaction_action_id)));
514
515 serial_rec get_serials%rowtype;
516 begin
517 -- initialize
518 if (p_serials.numrecs is NULL) then
519 -- p_serials is empty
520 p_serials.numrecs := i;
521 else
522 -- p_serials already has records
523 i := p_serials.numrecs;
524 end if;
525
526 open get_serials(
527 c_hdr_id => p_hdr_id,
528 c_act_id => p_act_id);
529
530 loop
531 fetch get_serials into serial_rec;
532
533 exit when (get_serials%NOTFOUND);
534
535 i := i + 1;
536 p_serials.numrecs := i;
537 p_serials.transaction_temp_id(i) := serial_rec.transaction_temp_id;
538 p_serials.last_update_date(i) := serial_rec.last_update_date;
539 p_serials.last_updated_by(i) := serial_rec.last_updated_by;
540 p_serials.creation_date(i) := serial_rec.creation_date;
541 p_serials.created_by(i) := serial_rec.created_by;
542 p_serials.last_update_login(i) := serial_rec.last_update_login;
543 p_serials.request_id(i) := serial_rec.request_id;
544 p_serials.program_application_id(i) := serial_rec.program_application_id;
545 p_serials.program_id(i) := serial_rec.program_id;
546 p_serials.program_update_date(i) := serial_rec.program_update_date;
547 p_serials.vendor_serial_number(i) := serial_rec.vendor_serial_number;
548 p_serials.vendor_lot_number(i) := serial_rec.vendor_lot_number;
549 p_serials.fm_serial_number(i) := serial_rec.fm_serial_number;
550 p_serials.to_serial_number(i) := serial_rec.to_serial_number;
551 p_serials.serial_prefix(i) := serial_rec.serial_prefix;
552 p_serials.error_code(i) := serial_rec.error_code;
553 p_serials.group_header_id(i) := serial_rec.group_header_id;
554 end loop;
555
556 close get_serials;
557
558 if (p_serials.numrecs > 0) then
559 delete from mtl_serial_numbers_temp
560 where
561 transaction_temp_id in
562 (select transaction_temp_id
563 from mtl_material_transactions_temp
564 where transaction_header_id = p_hdr_id
565 and transaction_action_id = nvl(p_act_id, transaction_action_id))
566 or
567 transaction_temp_id in
568 (select serial_transaction_temp_id
569 from mtl_transaction_lots_temp
570 where transaction_temp_id in
571 (select transaction_temp_id
572 from mtl_material_transactions_temp
573 where transaction_header_id = p_hdr_id
574 and transaction_action_id = nvl(p_act_id, transaction_action_id)));
575 end if;
576 end fetch_and_delete;
577
578 procedure fetch_and_delete(
579 p_hdr_id in number,
580 p_act_id in number,
581 p_dyn_sns in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_rec) is
582
583 i number := 0;
584
585 cursor get_serials(
586 c_hdr_id number,
587 c_act_id number) is
588 select
589 INVENTORY_ITEM_ID,
590 SERIAL_NUMBER,
591 LAST_UPDATE_DATE,
592 LAST_UPDATED_BY,
593 CREATION_DATE,
594 CREATED_BY,
595 LAST_UPDATE_LOGIN,
596 REQUEST_ID,
597 PROGRAM_APPLICATION_ID,
598 PROGRAM_ID,
599 PROGRAM_UPDATE_DATE,
600 INITIALIZATION_DATE,
601 COMPLETION_DATE,
602 SHIP_DATE,
603 CURRENT_STATUS,
604 REVISION,
605 LOT_NUMBER,
606 FIXED_ASSET_TAG,
607 RESERVED_ORDER_ID,
608 PARENT_ITEM_ID,
609 PARENT_SERIAL_NUMBER,
610 ORIGINAL_WIP_ENTITY_ID,
611 ORIGINAL_UNIT_VENDOR_ID,
612 VENDOR_SERIAL_NUMBER,
613 VENDOR_LOT_NUMBER,
614 LAST_TXN_SOURCE_TYPE_ID,
615 LAST_TRANSACTION_ID,
616 LAST_RECEIPT_ISSUE_TYPE,
617 LAST_TXN_SOURCE_NAME,
618 LAST_TXN_SOURCE_ID,
619 DESCRIPTIVE_TEXT,
620 CURRENT_SUBINVENTORY_CODE,
621 CURRENT_LOCATOR_ID,
622 CURRENT_ORGANIZATION_ID,
623 ATTRIBUTE_CATEGORY,
624 ATTRIBUTE1,
625 ATTRIBUTE2,
626 ATTRIBUTE3,
627 ATTRIBUTE4,
628 ATTRIBUTE5,
629 ATTRIBUTE6,
630 ATTRIBUTE7,
631 ATTRIBUTE8,
632 ATTRIBUTE9,
633 ATTRIBUTE10,
634 ATTRIBUTE11,
635 ATTRIBUTE12,
636 ATTRIBUTE13,
637 ATTRIBUTE14,
638 ATTRIBUTE15,
639 GROUP_MARK_ID,
640 LINE_MARK_ID,
641 LOT_LINE_MARK_ID
642 from mtl_serial_numbers
643 where current_status = 6
644 and group_mark_id = c_hdr_id
645 and (line_mark_id in
646 (select transaction_temp_id
647 from mtl_material_transactions_temp
648 where transaction_header_id = c_hdr_id
649 and transaction_action_id = nvl(c_act_id, transaction_action_id))
650 or
651 lot_line_mark_id in
652 (select serial_transaction_temp_id
653 from mtl_transaction_lots_temp
654 where transaction_temp_id in
655 (select transaction_temp_id
656 from mtl_material_transactions_temp
657 where transaction_header_id = c_hdr_id
658 and transaction_action_id = nvl(c_act_id, transaction_action_id))));
659
660 serial_rec get_serials%rowtype;
661 begin
662 -- initialize
663 if (p_dyn_sns.numrecs is NULL) then
664 -- p_dyn_sns is empty
665 p_dyn_sns.numrecs := i;
666 else
667 -- p_dyn_sns already has records
668 i := p_dyn_sns.numrecs;
669 end if;
670
671 open get_serials(
672 c_hdr_id => p_hdr_id,
673 c_act_id => p_hdr_id);
674
675 loop
676 fetch get_serials into serial_rec;
677
678 exit when (get_serials%NOTFOUND);
679
680 i := i + 1;
681 p_dyn_sns.numrecs := i;
682 p_dyn_sns.INVENTORY_ITEM_ID(i) := serial_rec.INVENTORY_ITEM_ID;
683 p_dyn_sns.SERIAL_NUMBER(i) := serial_rec.SERIAL_NUMBER;
684 p_dyn_sns.LAST_UPDATE_DATE(i) := serial_rec.LAST_UPDATE_DATE;
685 p_dyn_sns.LAST_UPDATED_BY(i) := serial_rec.LAST_UPDATED_BY;
686 p_dyn_sns.CREATION_DATE(i) := serial_rec.CREATION_DATE;
687 p_dyn_sns.CREATED_BY(i) := serial_rec.CREATED_BY;
688 p_dyn_sns.LAST_UPDATE_LOGIN(i) := serial_rec.LAST_UPDATE_LOGIN;
689 p_dyn_sns.REQUEST_ID(i) := serial_rec.REQUEST_ID;
690 p_dyn_sns.PROGRAM_APPLICATION_ID(i) := serial_rec.PROGRAM_APPLICATION_ID;
691 p_dyn_sns.PROGRAM_ID(i) := serial_rec.PROGRAM_ID;
692 p_dyn_sns.PROGRAM_UPDATE_DATE(i) := serial_rec.PROGRAM_UPDATE_DATE;
693 p_dyn_sns.INITIALIZATION_DATE(i) := serial_rec.INITIALIZATION_DATE;
694 p_dyn_sns.COMPLETION_DATE(i) := serial_rec.COMPLETION_DATE;
695 p_dyn_sns.SHIP_DATE(i) := serial_rec.SHIP_DATE;
696 p_dyn_sns.CURRENT_STATUS(i) := serial_rec.CURRENT_STATUS;
697 p_dyn_sns.REVISION(i) := serial_rec.REVISION;
698 p_dyn_sns.LOT_NUMBER(i) := serial_rec.LOT_NUMBER;
699 p_dyn_sns.FIXED_ASSET_TAG(i) := serial_rec.FIXED_ASSET_TAG;
700 p_dyn_sns.RESERVED_ORDER_ID(i) := serial_rec.RESERVED_ORDER_ID;
701 p_dyn_sns.PARENT_ITEM_ID(i) := serial_rec.PARENT_ITEM_ID;
702 p_dyn_sns.PARENT_SERIAL_NUMBER(i) := serial_rec.PARENT_SERIAL_NUMBER;
703 p_dyn_sns.ORIGINAL_WIP_ENTITY_ID(i) := serial_rec.ORIGINAL_WIP_ENTITY_ID;
704 p_dyn_sns.ORIGINAL_UNIT_VENDOR_ID(i) := serial_rec.ORIGINAL_UNIT_VENDOR_ID;
705 p_dyn_sns.VENDOR_SERIAL_NUMBER(i) := serial_rec.VENDOR_SERIAL_NUMBER;
706 p_dyn_sns.VENDOR_LOT_NUMBER(i) := serial_rec.VENDOR_LOT_NUMBER;
707 p_dyn_sns.LAST_TXN_SOURCE_TYPE_ID(i) := serial_rec.LAST_TXN_SOURCE_TYPE_ID;
708 p_dyn_sns.LAST_TRANSACTION_ID(i) := serial_rec.LAST_TRANSACTION_ID;
709 p_dyn_sns.LAST_RECEIPT_ISSUE_TYPE(i) := serial_rec.LAST_RECEIPT_ISSUE_TYPE;
710 p_dyn_sns.LAST_TXN_SOURCE_NAME(i) := serial_rec.LAST_TXN_SOURCE_NAME;
711 p_dyn_sns.LAST_TXN_SOURCE_ID(i) := serial_rec.LAST_TXN_SOURCE_ID;
712 p_dyn_sns.DESCRIPTIVE_TEXT(i) := serial_rec.DESCRIPTIVE_TEXT;
713 p_dyn_sns.CURRENT_SUBINVENTORY_CODE(i) := serial_rec.CURRENT_SUBINVENTORY_CODE;
714 p_dyn_sns.CURRENT_LOCATOR_ID(i) := serial_rec.CURRENT_LOCATOR_ID;
715 p_dyn_sns.CURRENT_ORGANIZATION_ID(i) := serial_rec.CURRENT_ORGANIZATION_ID;
716 p_dyn_sns.ATTRIBUTE_CATEGORY(i) := serial_rec.ATTRIBUTE_CATEGORY;
717 p_dyn_sns.ATTRIBUTE1(i) := serial_rec.ATTRIBUTE1;
718 p_dyn_sns.ATTRIBUTE2(i) := serial_rec.ATTRIBUTE2;
719 p_dyn_sns.ATTRIBUTE3(i) := serial_rec.ATTRIBUTE3;
720 p_dyn_sns.ATTRIBUTE4(i) := serial_rec.ATTRIBUTE4;
721 p_dyn_sns.ATTRIBUTE5(i) := serial_rec.ATTRIBUTE5;
722 p_dyn_sns.ATTRIBUTE6(i) := serial_rec.ATTRIBUTE6;
723 p_dyn_sns.ATTRIBUTE7(i) := serial_rec.ATTRIBUTE7;
724 p_dyn_sns.ATTRIBUTE8(i) := serial_rec.ATTRIBUTE8;
725 p_dyn_sns.ATTRIBUTE9(i) := serial_rec.ATTRIBUTE9;
726 p_dyn_sns.ATTRIBUTE10(i) := serial_rec.ATTRIBUTE10;
727 p_dyn_sns.ATTRIBUTE11(i) := serial_rec.ATTRIBUTE11;
728 p_dyn_sns.ATTRIBUTE12(i) := serial_rec.ATTRIBUTE12;
729 p_dyn_sns.ATTRIBUTE13(i) := serial_rec.ATTRIBUTE13;
730 p_dyn_sns.ATTRIBUTE14(i) := serial_rec.ATTRIBUTE14;
731 p_dyn_sns.ATTRIBUTE15(i) := serial_rec.ATTRIBUTE15;
732 p_dyn_sns.GROUP_MARK_ID(i) := serial_rec.GROUP_MARK_ID;
733 p_dyn_sns.LINE_MARK_ID(i) := serial_rec.LINE_MARK_ID;
734 p_dyn_sns.LOT_LINE_MARK_ID(i) := serial_rec.LOT_LINE_MARK_ID;
735 end loop;
736
737 close get_serials;
738
739 if (p_dyn_sns.numrecs > 0) then
740 delete mtl_serial_numbers
741 where current_status = 6
742 and group_mark_id = p_hdr_id
743 and (line_mark_id in
744 (select transaction_temp_id
745 from mtl_material_transactions_temp
746 where transaction_header_id = p_hdr_id
747 and transaction_action_id = nvl(p_act_id, transaction_action_id))
748 or
749 lot_line_mark_id in
750 (select serial_transaction_temp_id
751 from mtl_transaction_lots_temp
752 where transaction_temp_id in
753 (select transaction_temp_id
754 from mtl_material_transactions_temp
755 where transaction_header_id = p_hdr_id
756 and transaction_action_id = nvl(p_act_id, transaction_action_id))));
757 end if;
758 end fetch_and_delete;
759
760 procedure fetch_and_unmark(
761 p_hdr_id in number,
762 p_act_id in number,
763 p_serials in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_mark_rec) is
764 i number := 0;
765
766 cursor get_serials(
767 c_hdr_id number,
768 c_act_id number) is
769 select
770 SERIAL_NUMBER,
771 INVENTORY_ITEM_ID,
772 GROUP_MARK_ID,
773 LINE_MARK_ID,
774 LOT_LINE_MARK_ID
775 from mtl_serial_numbers
776 where group_mark_id = c_hdr_id
777 and (line_mark_id in
778 (select transaction_temp_id
779 from mtl_material_transactions_temp
780 where transaction_header_id = c_hdr_id
781 and transaction_action_id = nvl(c_act_id, transaction_action_id))
782 or
783 lot_line_mark_id in
784 (select serial_transaction_temp_id
785 from mtl_transaction_lots_temp
786 where transaction_temp_id in
787 (select transaction_temp_id
788 from mtl_material_transactions_temp
789 where transaction_header_id = c_hdr_id
790 and transaction_action_id = nvl(c_act_id, transaction_action_id))));
791
792 serial_rec get_serials%rowtype;
793 begin
794 -- initialize
795 if (p_serials.numrecs is NULL) then
796 -- p_serials is empty
797 p_serials.numrecs := i;
798 else
799 -- p_serials already has records
800 i := p_serials.numrecs;
801 end if;
802
803 open get_serials(
804 c_hdr_id => p_hdr_id,
805 c_act_id => p_act_id);
806
807 loop
808 fetch get_serials into serial_rec;
809
810 exit when (get_serials%NOTFOUND);
811
812 i := i + 1;
813 p_serials.numrecs := i;
814 p_serials.SERIAL_NUMBER(i) := serial_rec.SERIAL_NUMBER;
815 p_serials.INVENTORY_ITEM_ID(i) := serial_rec.INVENTORY_ITEM_ID;
816 p_serials.GROUP_MARK_ID(i) := serial_rec.GROUP_MARK_ID;
817 p_serials.LINE_MARK_ID(i) := serial_rec.LINE_MARK_ID;
818 p_serials.LOT_LINE_MARK_ID(i) := serial_rec.LOT_LINE_MARK_ID;
819 end loop;
820
821 close get_serials;
822
823 if (p_serials.numrecs > 0) then
824 update mtl_serial_numbers
825 set group_mark_id = null,
826 line_mark_id = null,
827 lot_line_mark_id = null
828 where group_mark_id = p_hdr_id
829 and (line_mark_id in
830 (select transaction_temp_id
831 from mtl_material_transactions_temp
832 where transaction_header_id = p_hdr_id
833 and transaction_action_id = nvl(p_act_id, transaction_action_id))
834 or
835 lot_line_mark_id in
836 (select serial_transaction_temp_id
837 from mtl_transaction_lots_temp
838 where transaction_temp_id in
839 (select transaction_temp_id
840 from mtl_material_transactions_temp
841 where transaction_header_id = p_hdr_id
842 and transaction_action_id = nvl(p_act_id, transaction_action_id))));
843 end if;
844 end fetch_and_unmark;
845
846 procedure fetch_and_delete(
847 p_hdr_id in number,
848 p_act_id in number default NULL,
849 p_materials in out nocopy mtl_transactions_temp_rec,
850 p_lots in out nocopy wip_lot_temp_cleanup.mtl_transaction_lots_temp_rec,
851 p_serials in out nocopy wip_serial_temp_cleanup.mtl_serial_numbers_temp_rec,
852 p_dyn_serials in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_rec,
853 p_ser_marks in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_mark_rec) is
854 begin
855 -- get marked serial numbers
856 fetch_and_unmark(
857 p_hdr_id => p_hdr_id,
858 p_act_id => p_act_id,
859 p_serials => p_ser_marks);
860
861 -- get dynamic serial numbers
862 fetch_and_delete(
863 p_hdr_id => p_hdr_id,
864 p_act_id => p_act_id,
865 p_dyn_sns => p_dyn_serials);
866
867 -- get serial records
868 fetch_and_delete(
869 p_hdr_id => p_hdr_id,
870 p_act_id => p_act_id,
871 p_serials => p_serials);
872
873 -- get lot records
874 fetch_and_delete(
875 p_hdr_id => p_hdr_id,
876 p_act_id => p_act_id,
877 p_lots => p_lots);
878
879 -- get material records
880 fetch_and_delete(
881 p_hdr_id => p_hdr_id,
882 p_act_id => p_act_id,
883 p_mtls => p_materials);
884 end fetch_and_delete;
885
886 procedure insert_rows(
887 p_mtls in mtl_transactions_temp_rec) is
888 i number := 1;
889 begin
890 while (i <= nvl(p_mtls.numrecs, 0)) loop
891 insert into mtl_material_transactions_temp (
892 TRANSACTION_HEADER_ID,
893 TRANSACTION_TEMP_ID,
894 SOURCE_CODE,
895 SOURCE_LINE_ID,
896 TRANSACTION_MODE,
897 LOCK_FLAG,
898 LAST_UPDATE_DATE,
899 LAST_UPDATED_BY,
900 CREATION_DATE,
901 CREATED_BY,
902 LAST_UPDATE_LOGIN,
903 REQUEST_ID,
904 PROGRAM_APPLICATION_ID,
905 PROGRAM_ID,
906 PROGRAM_UPDATE_DATE,
907 INVENTORY_ITEM_ID,
908 REVISION,
909 ORGANIZATION_ID,
910 SUBINVENTORY_CODE,
911 LOCATOR_ID,
912 TRANSACTION_QUANTITY,
913 PRIMARY_QUANTITY,
914 TRANSACTION_UOM,
915 TRANSACTION_COST,
916 TRANSACTION_TYPE_ID,
917 TRANSACTION_ACTION_ID,
918 TRANSACTION_SOURCE_TYPE_ID,
919 TRANSACTION_SOURCE_ID,
920 TRANSACTION_SOURCE_NAME,
921 TRANSACTION_DATE,
922 ACCT_PERIOD_ID,
923 DISTRIBUTION_ACCOUNT_ID,
924 TRANSACTION_REFERENCE,
925 REASON_ID,
926 LOT_NUMBER,
927 LOT_EXPIRATION_DATE,
928 SERIAL_NUMBER,
929 RECEIVING_DOCUMENT,
930 RCV_TRANSACTION_ID,
931 MOVE_TRANSACTION_ID,
932 COMPLETION_TRANSACTION_ID,
933 WIP_ENTITY_TYPE,
934 SCHEDULE_ID,
935 REPETITIVE_LINE_ID,
936 EMPLOYEE_CODE,
937 SCHEDULE_UPDATE_CODE,
938 SETUP_TEARDOWN_CODE,
939 ITEM_ORDERING,
940 NEGATIVE_REQ_FLAG,
941 OPERATION_SEQ_NUM,
942 PICKING_LINE_ID,
943 TRX_SOURCE_LINE_ID,
944 TRX_SOURCE_DELIVERY_ID,
945 PHYSICAL_ADJUSTMENT_ID,
946 CYCLE_COUNT_ID,
947 RMA_LINE_ID,
948 CUSTOMER_SHIP_ID,
949 CURRENCY_CODE,
950 CURRENCY_CONVERSION_RATE,
951 CURRENCY_CONVERSION_TYPE,
952 CURRENCY_CONVERSION_DATE,
953 USSGL_TRANSACTION_CODE,
954 VENDOR_LOT_NUMBER,
955 ENCUMBRANCE_ACCOUNT,
956 ENCUMBRANCE_AMOUNT,
957 SHIP_TO_LOCATION,
958 SHIPMENT_NUMBER,
959 TRANSFER_COST,
960 TRANSPORTATION_COST,
961 TRANSPORTATION_ACCOUNT,
962 FREIGHT_CODE,
963 CONTAINERS,
964 WAYBILL_AIRBILL,
965 EXPECTED_ARRIVAL_DATE,
966 TRANSFER_SUBINVENTORY,
967 TRANSFER_ORGANIZATION,
968 TRANSFER_TO_LOCATION,
969 NEW_AVERAGE_COST,
970 VALUE_CHANGE,
971 PERCENTAGE_CHANGE,
972 MATERIAL_ALLOCATION_TEMP_ID,
973 DEMAND_SOURCE_HEADER_ID,
974 DEMAND_SOURCE_LINE,
975 DEMAND_SOURCE_DELIVERY,
976 ITEM_SEGMENTS,
977 ITEM_DESCRIPTION,
978 ITEM_TRX_ENABLED_FLAG,
979 ITEM_LOCATION_CONTROL_CODE,
980 ITEM_RESTRICT_SUBINV_CODE,
981 ITEM_RESTRICT_LOCATORS_CODE,
982 ITEM_REVISION_QTY_CONTROL_CODE,
983 ITEM_PRIMARY_UOM_CODE,
984 ITEM_UOM_CLASS,
985 ITEM_SHELF_LIFE_CODE,
986 ITEM_SHELF_LIFE_DAYS,
987 ITEM_LOT_CONTROL_CODE,
988 ITEM_SERIAL_CONTROL_CODE,
989 ALLOWED_UNITS_LOOKUP_CODE,
990 DEPARTMENT_ID,
991 WIP_SUPPLY_TYPE,
992 SUPPLY_SUBINVENTORY,
993 SUPPLY_LOCATOR_ID,
994 VALID_SUBINVENTORY_FLAG,
995 VALID_LOCATOR_FLAG,
996 LOCATOR_SEGMENTS,
997 CURRENT_LOCATOR_CONTROL_CODE,
998 NUMBER_OF_LOTS_ENTERED,
999 WIP_COMMIT_FLAG,
1000 NEXT_LOT_NUMBER,
1001 LOT_ALPHA_PREFIX,
1002 NEXT_SERIAL_NUMBER,
1003 SERIAL_ALPHA_PREFIX,
1004 POSTING_FLAG,
1005 REQUIRED_FLAG,
1006 PROCESS_FLAG,
1007 ERROR_CODE,
1008 ATTRIBUTE_CATEGORY,
1009 ATTRIBUTE1,
1010 ATTRIBUTE2,
1011 ATTRIBUTE3,
1012 ATTRIBUTE4,
1013 ATTRIBUTE5,
1014 ATTRIBUTE6,
1015 ATTRIBUTE7,
1016 ATTRIBUTE8,
1017 ATTRIBUTE9,
1018 ATTRIBUTE10,
1019 ATTRIBUTE11,
1020 ATTRIBUTE12,
1021 ATTRIBUTE13,
1022 ATTRIBUTE14,
1023 ATTRIBUTE15,
1024 PRIMARY_SWITCH,
1025 DEPARTMENT_CODE,
1026 ERROR_EXPLANATION,
1027 DEMAND_ID,
1028 ITEM_INVENTORY_ASSET_FLAG,
1029 SHIPPABLE_FLAG,
1030 REQUISITION_LINE_ID,
1031 REQUISITION_DISTRIBUTION_ID,
1032 MOVEMENT_ID,
1033 RESERVATION_QUANTITY,
1034 SHIPPED_QUANTITY,
1035 TRANSACTION_LINE_NUMBER,
1036 EXPENDITURE_TYPE,
1037 FINAL_COMPLETION_FLAG,
1038 MATERIAL_ACCOUNT,
1039 MATERIAL_OVERHEAD_ACCOUNT,
1040 OUTSIDE_PROCESSING_ACCOUNT,
1041 OVERHEAD_ACCOUNT,
1042 PA_EXPENDITURE_ORG_ID,
1043 PROJECT_ID,
1044 RESOURCE_ACCOUNT,
1045 SOURCE_PROJECT_ID,
1046 SOURCE_TASK_ID,
1047 TASK_ID,
1048 TO_PROJECT_ID,
1049 TO_TASK_ID,
1050 TRANSACTION_SEQUENCE_ID,
1051 transfer_percentage,
1052 qa_collection_id,
1053 overcompletion_transaction_id,
1054 overcompletion_transaction_qty,
1055 overcompletion_primary_qty,
1056 kanban_card_id
1057 ) values (
1058 p_mtls.TRANSACTION_HEADER_ID(i),
1059 p_mtls.TRANSACTION_TEMP_ID(i),
1060 p_mtls.SOURCE_CODE(i),
1061 p_mtls.SOURCE_LINE_ID(i),
1062 p_mtls.TRANSACTION_MODE(i),
1063 p_mtls.LOCK_FLAG(i),
1064 p_mtls.LAST_UPDATE_DATE(i),
1065 p_mtls.LAST_UPDATED_BY(i),
1066 p_mtls.CREATION_DATE(i),
1067 p_mtls.CREATED_BY(i),
1068 p_mtls.LAST_UPDATE_LOGIN(i),
1069 p_mtls.REQUEST_ID(i),
1070 p_mtls.PROGRAM_APPLICATION_ID(i),
1071 p_mtls.PROGRAM_ID(i),
1072 p_mtls.PROGRAM_UPDATE_DATE(i),
1073 p_mtls.INVENTORY_ITEM_ID(i),
1074 p_mtls.REVISION(i),
1075 p_mtls.ORGANIZATION_ID(i),
1076 p_mtls.SUBINVENTORY_CODE(i),
1077 p_mtls.LOCATOR_ID(i),
1078 p_mtls.TRANSACTION_QUANTITY(i),
1079 p_mtls.PRIMARY_QUANTITY(i),
1080 p_mtls.TRANSACTION_UOM(i),
1081 p_mtls.TRANSACTION_COST(i),
1082 p_mtls.TRANSACTION_TYPE_ID(i),
1083 p_mtls.TRANSACTION_ACTION_ID(i),
1084 p_mtls.TRANSACTION_SOURCE_TYPE_ID(i),
1085 p_mtls.TRANSACTION_SOURCE_ID(i),
1086 p_mtls.TRANSACTION_SOURCE_NAME(i),
1087 p_mtls.TRANSACTION_DATE(i),
1088 p_mtls.ACCT_PERIOD_ID(i),
1089 p_mtls.DISTRIBUTION_ACCOUNT_ID(i),
1090 p_mtls.TRANSACTION_REFERENCE(i),
1091 p_mtls.REASON_ID(i),
1092 p_mtls.LOT_NUMBER(i),
1093 p_mtls.LOT_EXPIRATION_DATE(i),
1094 p_mtls.SERIAL_NUMBER(i),
1095 p_mtls.RECEIVING_DOCUMENT(i),
1096 p_mtls.RCV_TRANSACTION_ID(i),
1097 p_mtls.MOVE_TRANSACTION_ID(i),
1098 p_mtls.COMPLETION_TRANSACTION_ID(i),
1099 p_mtls.WIP_ENTITY_TYPE(i),
1100 p_mtls.SCHEDULE_ID(i),
1101 p_mtls.REPETITIVE_LINE_ID(i),
1102 p_mtls.EMPLOYEE_CODE(i),
1103 p_mtls.SCHEDULE_UPDATE_CODE(i),
1104 p_mtls.SETUP_TEARDOWN_CODE(i),
1105 p_mtls.ITEM_ORDERING(i),
1106 p_mtls.NEGATIVE_REQ_FLAG(i),
1107 p_mtls.OPERATION_SEQ_NUM(i),
1108 p_mtls.PICKING_LINE_ID(i),
1109 p_mtls.TRX_SOURCE_LINE_ID(i),
1110 p_mtls.TRX_SOURCE_DELIVERY_ID(i),
1111 p_mtls.PHYSICAL_ADJUSTMENT_ID(i),
1112 p_mtls.CYCLE_COUNT_ID(i),
1113 p_mtls.RMA_LINE_ID(i),
1114 p_mtls.CUSTOMER_SHIP_ID(i),
1115 p_mtls.CURRENCY_CODE(i),
1116 p_mtls.CURRENCY_CONVERSION_RATE(i),
1117 p_mtls.CURRENCY_CONVERSION_TYPE(i),
1118 p_mtls.CURRENCY_CONVERSION_DATE(i),
1119 p_mtls.USSGL_TRANSACTION_CODE(i),
1120 p_mtls.VENDOR_LOT_NUMBER(i),
1121 p_mtls.ENCUMBRANCE_ACCOUNT(i),
1122 p_mtls.ENCUMBRANCE_AMOUNT(i),
1123 p_mtls.SHIP_TO_LOCATION(i),
1124 p_mtls.SHIPMENT_NUMBER(i),
1125 p_mtls.TRANSFER_COST(i),
1126 p_mtls.TRANSPORTATION_COST(i),
1127 p_mtls.TRANSPORTATION_ACCOUNT(i),
1128 p_mtls.FREIGHT_CODE(i),
1129 p_mtls.CONTAINERS(i),
1130 p_mtls.WAYBILL_AIRBILL(i),
1131 p_mtls.EXPECTED_ARRIVAL_DATE(i),
1132 p_mtls.TRANSFER_SUBINVENTORY(i),
1133 p_mtls.TRANSFER_ORGANIZATION(i),
1134 p_mtls.TRANSFER_TO_LOCATION(i),
1135 p_mtls.NEW_AVERAGE_COST(i),
1136 p_mtls.VALUE_CHANGE(i),
1137 p_mtls.PERCENTAGE_CHANGE(i),
1138 p_mtls.MATERIAL_ALLOCATION_TEMP_ID(i),
1139 p_mtls.DEMAND_SOURCE_HEADER_ID(i),
1140 p_mtls.DEMAND_SOURCE_LINE(i),
1141 p_mtls.DEMAND_SOURCE_DELIVERY(i),
1142 p_mtls.ITEM_SEGMENTS(i),
1143 p_mtls.ITEM_DESCRIPTION(i),
1144 p_mtls.ITEM_TRX_ENABLED_FLAG(i),
1145 p_mtls.ITEM_LOCATION_CONTROL_CODE(i),
1146 p_mtls.ITEM_RESTRICT_SUBINV_CODE(i),
1147 p_mtls.ITEM_RESTRICT_LOCATORS_CODE(i),
1148 p_mtls.ITEM_REVISION_QTY_CONTROL_CODE(i),
1149 p_mtls.ITEM_PRIMARY_UOM_CODE(i),
1150 p_mtls.ITEM_UOM_CLASS(i),
1151 p_mtls.ITEM_SHELF_LIFE_CODE(i),
1152 p_mtls.ITEM_SHELF_LIFE_DAYS(i),
1153 p_mtls.ITEM_LOT_CONTROL_CODE(i),
1154 p_mtls.ITEM_SERIAL_CONTROL_CODE(i),
1155 p_mtls.ALLOWED_UNITS_LOOKUP_CODE(i),
1156 p_mtls.DEPARTMENT_ID(i),
1157 p_mtls.WIP_SUPPLY_TYPE(i),
1158 p_mtls.SUPPLY_SUBINVENTORY(i),
1159 p_mtls.SUPPLY_LOCATOR_ID(i),
1160 p_mtls.VALID_SUBINVENTORY_FLAG(i),
1161 p_mtls.VALID_LOCATOR_FLAG(i),
1162 p_mtls.LOCATOR_SEGMENTS(i),
1163 p_mtls.CURRENT_LOCATOR_CONTROL_CODE(i),
1164 p_mtls.NUMBER_OF_LOTS_ENTERED(i),
1165 p_mtls.WIP_COMMIT_FLAG(i),
1166 p_mtls.NEXT_LOT_NUMBER(i),
1167 p_mtls.LOT_ALPHA_PREFIX(i),
1168 p_mtls.NEXT_SERIAL_NUMBER(i),
1169 p_mtls.SERIAL_ALPHA_PREFIX(i),
1170 p_mtls.POSTING_FLAG(i),
1171 p_mtls.REQUIRED_FLAG(i),
1172 p_mtls.PROCESS_FLAG(i),
1173 p_mtls.ERROR_CODE(i),
1174 p_mtls.ATTRIBUTE_CATEGORY(i),
1175 p_mtls.ATTRIBUTE1(i),
1176 p_mtls.ATTRIBUTE2(i),
1177 p_mtls.ATTRIBUTE3(i),
1178 p_mtls.ATTRIBUTE4(i),
1179 p_mtls.ATTRIBUTE5(i),
1180 p_mtls.ATTRIBUTE6(i),
1181 p_mtls.ATTRIBUTE7(i),
1182 p_mtls.ATTRIBUTE8(i),
1183 p_mtls.ATTRIBUTE9(i),
1184 p_mtls.ATTRIBUTE10(i),
1185 p_mtls.ATTRIBUTE11(i),
1186 p_mtls.ATTRIBUTE12(i),
1187 p_mtls.ATTRIBUTE13(i),
1188 p_mtls.ATTRIBUTE14(i),
1189 p_mtls.ATTRIBUTE15(i),
1190 p_mtls.PRIMARY_SWITCH(i),
1191 p_mtls.DEPARTMENT_CODE(i),
1192 p_mtls.ERROR_EXPLANATION(i),
1193 p_mtls.DEMAND_ID(i),
1194 p_mtls.ITEM_INVENTORY_ASSET_FLAG(i),
1195 p_mtls.SHIPPABLE_FLAG(i),
1196 p_mtls.REQUISITION_LINE_ID(i),
1197 p_mtls.REQUISITION_DISTRIBUTION_ID(i),
1198 p_mtls.MOVEMENT_ID(i),
1199 p_mtls.RESERVATION_QUANTITY(i),
1200 p_mtls.SHIPPED_QUANTITY(i),
1201 p_mtls.TRANSACTION_LINE_NUMBER(i),
1202 p_mtls.EXPENDITURE_TYPE(i),
1203 p_mtls.FINAL_COMPLETION_FLAG(i),
1204 p_mtls.MATERIAL_ACCOUNT(i),
1205 p_mtls.MATERIAL_OVERHEAD_ACCOUNT(i),
1206 p_mtls.OUTSIDE_PROCESSING_ACCOUNT(i),
1207 p_mtls.OVERHEAD_ACCOUNT(i),
1208 p_mtls.PA_EXPENDITURE_ORG_ID(i),
1209 p_mtls.PROJECT_ID(i),
1210 p_mtls.RESOURCE_ACCOUNT(i),
1211 p_mtls.SOURCE_PROJECT_ID(i),
1212 p_mtls.SOURCE_TASK_ID(i),
1213 p_mtls.TASK_ID(i),
1214 p_mtls.TO_PROJECT_ID(i),
1215 p_mtls.TO_TASK_ID(i),
1216 p_mtls.TRANSACTION_SEQUENCE_ID(i),
1217 p_mtls.TRANSFER_PERCENTAGE(i),
1218 p_mtls.qa_collection_id(i),
1219 p_mtls.overcompletion_transaction_id(i),
1220 p_mtls.overcompletion_transaction_qty(i),
1221 p_mtls.overcompletion_primary_qty(i),
1222 p_mtls.kanban_card_id(i)
1223 );
1224
1225 i := i + 1;
1226 end loop;
1227 end insert_rows;
1228
1229 procedure insert_rows(
1230 p_materials in mtl_transactions_temp_rec,
1231 p_lots in wip_lot_temp_cleanup.mtl_transaction_lots_temp_rec,
1232 p_serials in wip_serial_temp_cleanup.mtl_serial_numbers_temp_rec,
1233 p_dyn_serials in wip_serial_number_cleanup.mtl_serial_numbers_rec,
1234 p_ser_marks in wip_serial_number_cleanup.mtl_serial_numbers_mark_rec,
1235 p_retcode out nocopy number,
1236 p_app out nocopy varchar2,
1237 p_msg out nocopy varchar2) is
1238 x_retcode number;
1239 begin
1240 -- insert material transaction records
1241 insert_rows(p_mtls => p_materials);
1242
1243 -- insert lot records
1244 wip_lot_temp_cleanup.insert_rows(p_lots => p_lots);
1245
1246 -- insert serial records
1247 wip_serial_temp_cleanup.insert_rows(p_serials => p_serials);
1248
1249 -- insert dynamic serial records
1250 wip_serial_number_cleanup.insert_rows(p_serials => p_dyn_serials);
1251
1252 -- mark serial numbers
1253 wip_serial_number_cleanup.mark(
1254 p_serials => p_ser_marks,
1255 p_retcode => x_retcode);
1256
1257 if (x_retcode <> wip_serial_number_cleanup.SUCCESS) then
1258 p_app := 'INV';
1259 p_msg := 'INV_RPC_CLEANUP_ERROR';
1260 else
1261 p_app := NULL;
1262 p_msg := NULL;
1263 end if;
1264 p_retcode := x_retcode;
1265
1266 exception
1267 when others then
1268 p_retcode := 1;
1269 p_msg := FND_API.G_RET_STS_UNEXP_ERROR;
1270 end insert_rows;
1271
1272 END WIP_MTL_TXNS_TEMP_CLEANUP;