[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_COMP_UTILITY_PVT
Source
1 PACKAGE BODY EAM_WO_COMP_UTILITY_PVT AS
2 /* $Header: EAMVWCUB.pls 120.16 2006/08/21 09:21:03 sshahid noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWCUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_COMP_UTILITY_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 14-FEB-2005 mmaduska Initial Creation
21 ***************************************************************************/
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_WO_COMP_UTILITY_PVT';
23
24
25 PROCEDURE Perform_Writes
26 (
27 p_eam_wo_comp_rec IN EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
28 , x_return_status OUT NOCOPY VARCHAR2
29 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
30 )
31 IS
32 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
33 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
34 BEGIN
35
36 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion processing Perform Writes'); END IF;
37
38
39 IF p_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_COMPLETE OR
40 p_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE
41 THEN
42 Insert_Row
43 ( p_eam_wo_comp_rec => p_eam_wo_comp_rec
44 , x_mesg_token_Tbl => l_mesg_token_tbl
45 , x_return_Status => l_return_status
46 );
47 END IF;
48
49 x_return_status := l_return_status;
50 x_mesg_token_tbl := l_mesg_token_tbl;
51
52 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
53 EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion completed Perform Writes');
54 END IF;
55
56 END Perform_Writes;
57
58 PROCEDURE insert_row
59 (
60 p_eam_wo_comp_rec IN EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
61 , x_return_status OUT NOCOPY VARCHAR2
62 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
63 )
64 IS
65 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
66 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
67 BEGIN
68
69 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion processing insert row'); END IF;
70
71 begin
72 INSERT INTO EAM_JOB_COMPLETION_TXNS (
73 transaction_id,
74 transaction_date,
75 transaction_type,
76 wip_entity_id,
77 organization_id,
78 parent_wip_entity_id,
79 reference,
80 reconciliation_code,
81 acct_period_id,
82 qa_collection_id,
83 actual_start_date,
84 actual_end_date,
85 actual_duration,
86 created_by,
87 creation_date,
88 last_updated_by,
89 last_update_date,
90 last_update_login,
91 request_id,
92 program_application_id,
93 program_id,
94 program_update_date,
95 completion_subinventory,
96 completion_locator_id,
97 lot_number,
98 attribute_category,
99 attribute1,
100 attribute2,
101 attribute3,
102 attribute4,
103 attribute5,
104 attribute6,
105 attribute7,
106 attribute8,
107 attribute9,
108 attribute10,
109 attribute11,
110 attribute12,
111 attribute13,
112 attribute14,
113 attribute15
114 )
115 VALUES (
116 p_eam_wo_comp_rec.transaction_id,
117 p_eam_wo_comp_rec.transaction_date,
118 decode(p_eam_wo_comp_rec.transaction_type,EAM_PROCESS_WO_PVT.G_OPR_COMPLETE,1,EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE,2),
119 p_eam_wo_comp_rec.wip_entity_id,
120 p_eam_wo_comp_rec.organization_id,
121 p_eam_wo_comp_rec.parent_wip_entity_id,
122 p_eam_wo_comp_rec.reference,
123 p_eam_wo_comp_rec.reconciliation_code,
124 p_eam_wo_comp_rec.acct_period_id,
125 p_eam_wo_comp_rec.qa_collection_id,
126 p_eam_wo_comp_rec.actual_start_date,
127 p_eam_wo_comp_rec.actual_end_date,
128 p_eam_wo_comp_rec.actual_duration,
129 FND_GLOBAL.user_id,
130 sysdate,
131 FND_GLOBAL.user_id,
132 sysdate,
133 FND_GLOBAL.login_id,
134 p_eam_wo_comp_rec.request_id,
135 p_eam_wo_comp_rec.program_application_id,
136 p_eam_wo_comp_rec.program_id,
137 p_eam_wo_comp_rec.program_update_date,
138 p_eam_wo_comp_rec.completion_subinventory,
139 p_eam_wo_comp_rec.completion_locator_id,
140 p_eam_wo_comp_rec.lot_number,
141 p_eam_wo_comp_rec.attribute_category,
142 p_eam_wo_comp_rec.attribute1,
143 p_eam_wo_comp_rec.attribute2,
144 p_eam_wo_comp_rec.attribute3,
145 p_eam_wo_comp_rec.attribute4,
146 p_eam_wo_comp_rec.attribute5,
147 p_eam_wo_comp_rec.attribute6,
148 p_eam_wo_comp_rec.attribute7,
149 p_eam_wo_comp_rec.attribute8,
150 p_eam_wo_comp_rec.attribute9,
151 p_eam_wo_comp_rec.attribute10,
152 p_eam_wo_comp_rec.attribute11,
153 p_eam_wo_comp_rec.attribute12,
154 p_eam_wo_comp_rec.attribute13,
155 p_eam_wo_comp_rec.attribute14,
156 p_eam_wo_comp_rec.attribute15
157 );
158 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
159 EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion completed insert row');
160 END IF;
161
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163
164
165 EXCEPTION
166 WHEN OTHERS THEN
167
168 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
169 EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion : insert row : inside exception : ' || SQLERRM);
170 END IF;
171
172 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
173 ( p_message_name => NULL
174 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
175 , x_mesg_token_Tbl => x_mesg_token_tbl
176 );
177 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
178 END;
179 END insert_row;
180
181 PROCEDURE process_shutdown(s_asset_group_id NUMBER,
182 s_organization_id NUMBER,
183 s_asset_number VARCHAR2,
184 s_start_date DATE,
185 s_end_date DATE,
186 s_user_id NUMBER,
187 s_maintenance_object_type NUMBER,
188 s_maintenance_object_id NUMBER,
189 s_wip_entity_id NUMBER DEFAULT NULL ) IS
190
191 i_asset_status_id NUMBER;
192 BEGIN
193
194 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
195 EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion processing Shutdown info ');
196 END IF;
197
198 -- get the asset_status_id from eam_asset_status_history_s sequence
199 SELECT eam_asset_status_history_s.nextval
200 INTO i_asset_status_id
201 FROM dual;
202
203 -- Enhancement Bug 3852846
204 UPDATE eam_asset_status_history
205 SET enable_flag = 'N'
206 , last_update_date = SYSDATE
207 , last_updated_by = FND_GLOBAL.user_id
208 , last_update_login = FND_GLOBAL.login_id
209 WHERE organization_id = s_organization_id
210 AND wip_entity_id = s_wip_entity_id
211 AND operation_seq_num IS NULL
212 AND (enable_flag is NULL OR enable_flag = 'Y')
213 AND maintenance_object_type = s_maintenance_object_type
214 AND maintenance_object_id = s_maintenance_object_id;
215
216 INSERT INTO eam_asset_status_history
217 (asset_status_id,
218 organization_id,
219 asset_group_id,
220 asset_number,
221 start_date,
222 end_date,
223 wip_entity_id, -- Fix for Bug 3448770
224 maintenance_object_type,
225 maintenance_object_id,
226 created_by,
227 creation_date,
228 last_updated_by,
229 last_update_date,
230 enable_flag) -- Enhancement Bug 3852846
231 VALUES (i_asset_status_id,
232 s_organization_id,
233 s_asset_group_id,
234 s_asset_number,
235 s_start_date,
236 s_end_date,
237 s_wip_entity_id, -- Fix for Bug 3448770
238 s_maintenance_object_type,
239 s_maintenance_object_id,
240 s_user_id,
241 sysdate,
242 s_user_id,
243 sysdate,
244 'Y'); -- Enhancement Bug 3852846
245
246 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
247 EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion completed Shutdown info processing');
248 END IF;
249
250 END process_shutdown;
251
252 PROCEDURE process_lot_serial(
253 s_subinventory VARCHAR2,
254 s_locator_id VARCHAR2,
255 s_lot_serial_tbl Lot_Serial_Tbl_Type,
256 s_org_id NUMBER,
257 s_wip_entity_id NUMBER,
258 s_qa_collection_id NUMBER,
259 s_rebuild_item_id NUMBER,
260 s_acct_period_id NUMBER,
261 s_user_id NUMBER,
262 s_transaction_type NUMBER,
263 s_project_id NUMBER,
264 s_task_id NUMBER,
265 s_commit VARCHAR2,
266 errCode OUT NOCOPY NUMBER,
267 errMsg OUT NOCOPY VARCHAR2
268 ) IS
269
270 i_transaction_header_id NUMBER;
271 i_transaction_temp_id NUMBER;
272 i_serial_transaction_temp_id NUMBER;
273 i_transaction_temp_id_s NUMBER;
274 i_transaction_quantity NUMBER;
275 i_primary_quantity NUMBER;
276 i_transaction_action_id NUMBER;
277 i_transaction_type_id NUMBER;
278 i_transaction_source_type_id NUMBER;
279 i_project_id NUMBER;
280 i_task_id NUMBER;
281 i_revision VARCHAR2(3) := null;
282 item wma_common.Item;
283 l_revision_control_code NUMBER := 1;
284 l_transaction_quantity NUMBER;
285 l_initial_msg_count NUMBER := 0;
286
287 BEGIN
288
289 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
290 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : Start ');
291 END IF;
292
293 -- prepare the data to insert into MTL_MATERIAL_TRANSACTIONS_TEMP,
294 -- MTL_SERIAL_NUMBERS_TEMP, and MTL_TRANSACTION_LOTS_TEMP
295 select mtl_material_transactions_s.nextval into i_transaction_header_id
296 from dual;
297
298
299 -- get the item info
300 item := wma_derive.getItem(s_rebuild_item_id, s_org_id, s_locator_id);
301 if (item.invItemID is null) then
302 fnd_message.set_name ('EAM', 'EAM_ITEM_DOES_NOT_EXIST');
303 errCode := 1;
304 errMsg := fnd_message.get;
305 return;
306 end if; -- end item info check
307
308 begin
309 select nvl(revision_qty_control_code,1)
310 into l_revision_control_code
311 from mtl_system_items
312 where inventory_item_id = s_rebuild_item_id
313 and organization_id = s_org_id;
314
315 exception
316 when others then
317 null;
318 end;
319
320 if (l_revision_control_code = 2) then
321
322 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
323 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before bom_revisions.get_revision ');
324 END IF;
325
326 -- get bom_revision
327 bom_revisions.get_revision (examine_type => 'ALL',
328 org_id => s_org_id,
329 item_id => s_rebuild_item_id,
330 rev_date => sysdate,
331 itm_rev => i_revision);
332
333 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
334 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after bom_revisions.get_revision ');
335 END IF;
336 end if;
337
338 -- get transaction source type id
339 i_transaction_source_type_id := inv_reservation_global.g_source_type_wip;
340
341 -- set i_transaction_quantity and i_primary_quantity to be the sum of all
342 -- quantities in the lot_serial_tbl
343 -- also verify all quantities are greater than 0
344 i_transaction_quantity := 0;
345
346
347 FOR i IN 1..s_lot_serial_tbl.COUNT LOOP
348 IF s_lot_serial_tbl(i).quantity is null or s_lot_serial_tbl(i).quantity < 1 THEN
349 errCode := 1;
350 fnd_message.set_name('EAM', 'EAM_NEGATIVE_TXN_QUANTITY');
351 errMsg := fnd_message.get;
352
353 return;
354 ELSE
355 i_transaction_quantity := i_transaction_quantity + s_lot_serial_tbl(i).quantity;
356 END IF;
357 END LOOP;
358 i_primary_quantity := i_transaction_quantity;
359
360 -- prepare the rest data
361 if(s_transaction_type = 1) then -- Complete Transaction
362 i_transaction_action_id := WIP_CONSTANTS.CPLASSY_ACTION;
363 i_transaction_type_id := WIP_CONSTANTS.CPLASSY_TYPE;
364 elsif(s_transaction_type = 2) then -- Uncomplete Transaction
365 i_transaction_quantity := - i_transaction_quantity;
366 i_primary_quantity := - i_primary_quantity;
367 i_transaction_action_id := WIP_CONSTANTS.RETASSY_ACTION;
368 i_transaction_type_id := WIP_CONSTANTS.RETASSY_TYPE;
369 else
370 fnd_message.set_name('EAM','EAM_INVALID_TRANSACTION_TYPE');
371 errCode := 1;
372 errMsg := fnd_message.get;
373 return;
374 end if; -- end prepare data
375
376 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
377 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_trx_util_pub.insert_line_trx');
378 END IF;
379
380
381 -- call inventory API to insert data to mtl_material_transactions_temp
382 -- the spec file is INVTRXUS.pls
383 errCode := inv_trx_util_pub.insert_line_trx(
384 p_trx_hdr_id => i_transaction_header_id,
385 p_item_id => s_rebuild_item_id,
386 p_revision => i_revision,
387 p_org_id => s_org_id,
388 p_trx_action_id => i_transaction_action_id,
389 p_subinv_code => s_subinventory,
390 p_locator_id => s_locator_id,
391 p_trx_type_id => i_transaction_type_id,
392 p_trx_src_type_id => i_transaction_source_type_id,
393 p_trx_qty => i_transaction_quantity,
394 p_pri_qty => i_primary_quantity,
395 p_uom => item.primaryUOMCode,
396 p_date => sysdate,
397 p_user_id => s_user_id,
398 p_trx_src_id => s_wip_entity_id,
399 x_trx_tmp_id => i_transaction_temp_id,
400 x_proc_msg => errMsg);
401 if (errCode <> 0) then
402 return;
403 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
404 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : inv_trx_util_pub.insert_line_trx : ' || errMsg);
405 END IF;
406 end if;
407
408 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
409 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after inv_trx_util_pub.insert_line_trx');
410 END IF;
411
412 FOR i IN 1..s_lot_serial_tbl.COUNT LOOP
413
414 if(s_transaction_type = 1) then -- Complete Transaction
415 l_transaction_quantity := s_lot_serial_tbl(i).quantity;
416 else -- Uncomplete Transaction
417 l_transaction_quantity := - s_lot_serial_tbl(i).quantity;
418 end if;
419
420 -- Check whether the item is under lot or serial control or not
421 -- If it is, insert the data to coresponding tables
422 if(item.lotControlCode = WIP_CONSTANTS.LOT) then
423
424 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
425 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_trx_util_pub.insert_lot_trx');
426 END IF;
427
428 -- the item is under lot control
429
430 -- call inventory API to insert data to mtl_transaction_lots_temp
431 -- the spec file is INVTRXUS.pls
432 errCode := inv_trx_util_pub.insert_lot_trx(
433 p_trx_tmp_id => i_transaction_temp_id,
434 p_user_id => s_user_id,
435 p_lot_number => s_lot_serial_tbl(i).lot_number,
436 p_trx_qty => l_transaction_quantity,
437 p_pri_qty => l_transaction_quantity,
438 x_ser_trx_id => i_serial_transaction_temp_id,
439 x_proc_msg => errMsg);
440
441 if (errCode <> 0) then
442 return;
443 end if;
444
445 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
446 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after inv_trx_util_pub.insert_lot_trx');
447 END IF;
448
449 else
450 null;
451 end if; -- end lot control check
452
453 -- Check if the item is under serial control or not
454 if(item.serialNumberControlCode in (WIP_CONSTANTS.FULL_SN,
455 WIP_CONSTANTS.DYN_RCV_SN)) then
456 -- item is under serial control
457
458 -- Check if the item is under lot control or not
459 if(item.lotControlCode = WIP_CONSTANTS.LOT) then
460
461 -- under lot control
462 i_transaction_temp_id_s := i_serial_transaction_temp_id;
463 else
464 i_transaction_temp_id_s := i_transaction_temp_id;
465 end if; -- end lot control check
466
467 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
468 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_trx_util_pub.insert_ser_trx');
469 END IF;
470
471 -- call inventory API to insert data to mtl_serial_numbers_temp
472 -- the spec file is INVTRXUS.pls
473 errCode := inv_trx_util_pub.insert_ser_trx(
474 p_trx_tmp_id => i_transaction_temp_id_s,
475 p_user_id => s_user_id,
476 p_fm_ser_num => s_lot_serial_tbl(i).serial_number,
477 p_to_ser_num => s_lot_serial_tbl(i).serial_number,
478 x_proc_msg => errMsg);
479 if (errCode <> 0) then
480 return;
481 end if;
482
483 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
484 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after inv_trx_util_pub.insert_ser_trx');
485 END IF;
486
487 else
488 null;
489 end if; -- end serial control check
490
491 end LOOP;
492
493 l_initial_msg_count := FND_MSG_PUB.count_msg;
494
495 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
496 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_lpn_trx_pub.process_lpn_trx');
497 END IF;
498 -- Call Inventory API to process to item
499 -- the spec file is INVTRXWS.pls
500 errCode := inv_lpn_trx_pub.process_lpn_trx(
501 p_trx_hdr_id => i_transaction_header_id,
502 p_commit => s_commit,
503 x_proc_msg => errMsg);
504 /* Added as a FIX for the Issue 1 of bug:2881879 */
505 if(FND_MSG_PUB.count_msg> 0) then
506 if(l_initial_msg_count = 0 and errCode = 0) then
507 FND_MSG_PUB.Delete_msg;
508 end if;
509 end if;
510 /* Added for bug no :2911698
511 Since the error message is not getting added into the message stack
512 */
513 /* need to work on this
514 if(errCode <> 0 and errMsg is not null) then
515 eam_execution_jsp.add_message(p_app_short_name => 'EAM',p_msg_name =>
516 'EAM_RET_MAT_PROCESS_MESSAGE',p_token1=> 'ERRMESSAGE',
517 p_value1 => errMsg);
518 end if;
519 */
520 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
521 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : End');
522 END IF;
523 END process_lot_serial;
524
525 PROCEDURE process_item(
526 s_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type,
527 s_org_id NUMBER,
528 s_wip_entity_id NUMBER,
529 s_qa_collection_id NUMBER,
530 s_rebuild_item_id NUMBER,
531 s_acct_period_id NUMBER,
532 s_user_id NUMBER,
533 s_transaction_type NUMBER,
534 s_project_id NUMBER,
535 s_task_id NUMBER,
536 s_commit VARCHAR2,
537 errCode OUT NOCOPY NUMBER,
538 errMsg OUT NOCOPY VARCHAR2)IS
539 -- x_statement OUT NOCOPY NUMBER) IS
540 l_subinventory VARCHAR2(30);
541 l_locator VARCHAR2(30);
542 l_lot_serial_rec Lot_Serial_Rec_Type;
543 l_lot_serial_tbl Lot_Serial_Tbl_Type;
544 BEGIN
545
546 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
547 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_item : Start ');
548 END IF;
549
550 IF s_inventory_item_tbl.COUNT = 0 THEN
551 RETURN;
552 END IF;
553
554 l_subinventory := s_inventory_item_tbl(1).subinventory;
555 l_locator := s_inventory_item_tbl(1).locator;
556 l_lot_serial_rec.lot_number := s_inventory_item_tbl(1).lot_number;
557 l_lot_serial_rec.serial_number := s_inventory_item_tbl(1).serial_number;
558 l_lot_serial_rec.quantity := s_inventory_item_tbl(1).quantity;
559 l_lot_serial_tbl(1) := l_lot_serial_rec;
560
561 FOR i in 2..(s_inventory_item_tbl.COUNT+1) LOOP
562 IF (i > s_inventory_item_tbl.COUNT
563 OR s_inventory_item_tbl(i).subinventory <> l_subinventory
564 OR s_inventory_item_tbl(i).locator <> l_locator) THEN
565
566 process_lot_serial(l_subinventory,
567 l_locator,
568 l_lot_serial_tbl,
569 s_org_id,
570 s_wip_entity_id,
571 s_qa_collection_id,
572 s_rebuild_item_id,
573 s_acct_period_id,
574 s_user_id,
575 s_transaction_type,
576 s_project_id,
577 s_task_id,
578 s_commit,
579 errCode,
580 errMsg);
581 if (errCode <> 0) then
582 return;
583 end if;
584 l_lot_serial_tbl.DELETE;
585 END IF;
586 IF (i <= s_inventory_item_tbl.COUNT) THEN
587 l_subinventory := s_inventory_item_tbl(i).subinventory;
588 l_locator := s_inventory_item_tbl(i).locator;
589 l_lot_serial_rec.lot_number := s_inventory_item_tbl(i).lot_number;
590 l_lot_serial_rec.serial_number := s_inventory_item_tbl(i).serial_number;
591 l_lot_serial_rec.quantity := s_inventory_item_tbl(i).quantity;
592 l_lot_serial_tbl(l_lot_serial_tbl.COUNT+1) := l_lot_serial_rec;
593 END IF;
594 END LOOP;
595
596 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
597 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_item : End ');
598 END IF;
599
600 END process_item;
601
602 PROCEDURE update_row (
603 p_eam_wo_comp_rec IN EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
604 , x_return_status OUT NOCOPY VARCHAR2
605 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
606 )
607 IS
608 l_asset_group_id NUMBER;
609 l_asset_number VARCHAR2(30);
610 i_maintenance_object_type NUMBER;
611 i_maintenance_object_id NUMBER;
612 i_work_request_status NUMBER;
613 i_status_type NUMBER;
614 i_completion_date DATE;
615 l_msg_count NUMBER;
616 l_msg_data VARCHAR2(2000);
617
618 x_inventory_item_rec EAM_WorkOrderTransactions_PUB.Inventory_Item_Rec_Type;
619 x_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type;
620
621 i_parent_wip_entity_id NUMBER;
622 i_asset_group_id NUMBER;
623 i_asset_number VARCHAR2(30);
624 i_asset_activity_id NUMBER;
625 i_manual_rebuild_flag VARCHAR2(1);
626 i_rebuild_item_id NUMBER;
627 i_rebuild_serial_number VARCHAR2(30);
628 i_project_id NUMBER;
629 i_task_id NUMBER;
630 i_maintenance_source_id NUMBER;
631 i_open_past_period BOOLEAN;
632
633 i_acct_period_id NUMBER;
634 x_commit VARCHAR2(1) := fnd_api.g_false;
635 errCode NUMBER;
636 errMsg VARCHAR2(2000);
637 l_statement NUMBER := 0;
638 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
639 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
640 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
641 l_txn_type NUMBER;
642
643 l_cmpl_sub VARCHAR2(10);
644 l_locator NUMBER;
645 l_lot VARCHAR2(80);
646 BEGIN
647 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion processing update_row'); END IF;
648
649 -- get some value from wip_discrete_jobs table
650 select parent_wip_entity_id,
651 asset_group_id,
652 asset_number,
653 primary_item_id,
654 manual_rebuild_flag,
655 rebuild_item_id,
656 rebuild_serial_number,
657 project_id,
658 task_id,
659 maintenance_object_source, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
660 maintenance_object_type, -- Fix for Bug 3448770
661 maintenance_object_id
662 into i_parent_wip_entity_id,
663 i_asset_group_id,
664 i_asset_number,
665 i_asset_activity_id,
666 i_manual_rebuild_flag,
667 i_rebuild_item_id,
668 i_rebuild_serial_number,
669 i_project_id,
670 i_task_id,
671 i_maintenance_source_id, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
672 i_maintenance_object_type, -- Fix for Bug 3448770
673 i_maintenance_object_id
674 from wip_discrete_jobs
675 where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;
676
677 IF p_eam_wo_comp_rec.transaction_type = 4 THEN
678 l_txn_type:=1;
679 ELSE
680 l_txn_type:=2;
681 END IF;
682
683 invttmtx.tdatechk(p_eam_wo_comp_rec.organization_id,p_eam_wo_comp_rec.transaction_date,
684 i_acct_period_id,i_open_past_period);
685
686 IF p_eam_wo_comp_rec.rebuild_job <> 'Y' THEN
687 i_rebuild_serial_number := i_asset_number;
688 i_rebuild_item_id := i_asset_group_id;
689 END IF;
690
691 x_inventory_item_rec.subinventory := p_eam_wo_comp_rec.completion_subinventory;
692 x_inventory_item_rec.locator := p_eam_wo_comp_rec.completion_locator_id;
693 x_inventory_item_rec.lot_number := p_eam_wo_comp_rec.lot_number;
694 x_inventory_item_rec.serial_number := i_rebuild_serial_number;
695 x_inventory_item_rec.quantity := 1;
696 x_inventory_item_tbl(1) := x_inventory_item_rec;
697
698 IF p_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_COMPLETE THEN
699 -- if (p_eam_wo_comp_rec.rebuild_job = 'Y') then -- Rebuild Work Order
700
701 -- Check whether there is material issue or not
702
703 -- if (x_inventory_item_tbl(1).subinventory is not null) then -- there is material issue
704
705 if (EAM_COMMON_UTILITIES_PVT.showCompletionFields(p_eam_wo_comp_rec.wip_entity_id) = 'Y'
706 and x_inventory_item_tbl(1).subinventory is not null) then -- there is material issue
707
708 -- return item back to inventory
709 process_item(s_inventory_item_tbl => x_inventory_item_tbl,
710 s_org_id => p_eam_wo_comp_rec.organization_id,
711 s_wip_entity_id => p_eam_wo_comp_rec.wip_entity_id,
712 s_qa_collection_id => p_eam_wo_comp_rec.qa_collection_id,
713 s_rebuild_item_id => i_rebuild_item_id,
714 s_acct_period_id => i_acct_period_id,
715 s_user_id => fnd_global.user_id,
716 s_transaction_type => l_txn_type,
717 s_project_id => i_project_id,
718 s_task_id => i_task_id,
719 s_commit => x_commit,
720 errCode => errCode,
721 errMsg => errMsg);
722 -- x_statement => l_statement);
723
724 if(errCode <> 0) then
725 /* l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
726 l_token_tbl(1).token_value := p_eam_wo_comp_rec.wip_entity_id;
727
728 l_out_mesg_token_tbl := l_mesg_token_tbl;
729 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
730 ( p_message_name => 'EAM_WO_COMPL_RETURN_ITEM'
731 , p_token_tbl => l_Token_tbl
732 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
733 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
734 );
735 l_mesg_token_tbl := l_out_mesg_token_tbl;
736 x_mesg_token_tbl := l_mesg_token_tbl ;
737
738 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
739 return; */
740
741 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
742 ( p_message_name => NULL
743 , p_message_text => errMsg
744 , x_mesg_token_Tbl => x_mesg_token_tbl
745 );
746 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
747 return;
748
749 end if;
750
751 end if; -- end material issue check
752 -- -- end if;
753 ELSE
754 -- if (p_eam_wo_comp_rec.rebuild_job = 'Y') then -- Rebuild Work Order
755
756 -- Check whether there is material issue or not
757 /* Bug 3637201 */
758 /* Subinventory check is added for bug no :2911698 */
759 begin
760
761 select
762 completion_subinventory,
763 completion_locator_id,
764 lot_number
765 into l_cmpl_sub,
766 l_locator,
767 l_lot
768
769 from EAM_JOB_COMPLETION_TXNS
770 where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
771 and transaction_type = 1
772 and transaction_date = (select max(transaction_date) from EAM_JOB_COMPLETION_TXNS
773 where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
774 and transaction_type =1);
775
776 x_inventory_item_rec.subinventory := l_cmpl_sub;
777 x_inventory_item_rec.locator := l_locator;
778 x_inventory_item_rec.lot_number := l_lot;
779
780 x_inventory_item_tbl(1) := x_inventory_item_rec;
781 exception when others then
782 null;
783 end;
784
785 if (x_inventory_item_tbl(1).subinventory is not null) then -- there is material issue
786 -- get item back from inventory
787
788 process_item(s_inventory_item_tbl => x_inventory_item_tbl,
789 s_org_id => p_eam_wo_comp_rec.organization_id,
790 s_wip_entity_id => p_eam_wo_comp_rec.wip_entity_id,
791 s_qa_collection_id => p_eam_wo_comp_rec.qa_collection_id,
792 s_rebuild_item_id => i_rebuild_item_id,
793 s_acct_period_id => i_acct_period_id,
794 s_user_id => fnd_global.user_id,
795 s_transaction_type => l_txn_type,
796 s_project_id => i_project_id,
797 s_task_id => i_task_id,
798 s_commit => x_commit,
799 errCode => errCode,
800 errMsg => errMsg);
801 -- x_statement => l_statement);
802
803 -- Check if there is an error to return item or not
804 /* if(errCode <> 0) then
805 ROLLBACK TO job_comp;
806 return;
807 end if; --end errCode check */
808 if(errCode <> 0) then
809 /* l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
810 l_token_tbl(1).token_value := p_eam_wo_comp_rec.wip_entity_id;
811
812 l_out_mesg_token_tbl := l_mesg_token_tbl;
813 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
814 ( p_message_name => 'EAM_WO_COMPL_GET_ITEM'
815 , p_token_tbl => l_Token_tbl
816 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
817 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
818 );
819 l_mesg_token_tbl := l_out_mesg_token_tbl;
820 x_mesg_token_tbl := l_mesg_token_tbl ;
821
822 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
823 return;
824 */
825 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
826 ( p_message_name => NULL
827 , p_message_text => errMsg
828 , x_mesg_token_Tbl => x_mesg_token_tbl
829 );
830
831 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
832
833 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
834 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.update_row : Exception1 : '|| errMsg);
835 END IF;
836 return;
837 end if;
838 end if;
839 -- end if;
840 END IF;
841
842 select system_status into i_status_type
843 from eam_wo_statuses_V
844 where status_id = p_eam_wo_comp_rec.user_status_id
845 and enabled_flag = 'Y';
846
847 IF p_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_COMPLETE THEN
848 i_work_request_status := 6; -- Work request complete
849 i_completion_date := p_eam_wo_comp_rec.actual_end_date;
850 ELSE
851 i_work_request_status := 4; -- Work request in process
852 i_completion_date := null;
853 END IF;
854
855
856 SELECT NVL(asset_group_id,rebuild_item_id),
857 NVL(asset_number,rebuild_serial_number),
858 maintenance_object_type,
859 maintenance_object_id
860 INTO l_asset_group_id ,
861 l_asset_number,
862 i_maintenance_object_type,
863 i_maintenance_object_id
864 FROM WIP_DISCRETE_JOBS
865 WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;
866
867
868 IF p_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_COMPLETE THEN
869 eam_meters_util.update_last_service_dates_wo(
870 p_wip_entity_id => p_eam_wo_comp_rec.wip_entity_id,
871 p_start_date => p_eam_wo_comp_rec.actual_start_date,
872 p_end_date => p_eam_wo_comp_rec.actual_end_date,
873 x_return_status => x_return_status,
874 x_msg_count => l_msg_count,
875 x_msg_data => l_msg_data);
876
877 if(x_return_status <> 'S') then
878 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
879 ( p_message_name => NULL
880 , p_message_text => l_msg_data
881 , x_mesg_token_Tbl => x_mesg_token_tbl
882 );
883 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
884 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
885 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.update_row : Exception2 : '|| l_msg_data);
886 END IF;
887 return;
888 end if;
889
890 --Bug#5451093..Moved code inside check for completion transaction as shutdown dates have to be processed
891 -- only during completion
892 /* Bug # 5165813 : Allow shutdown info for rebuild wo also */
893 IF p_eam_wo_comp_rec.shutdown_start_date IS NOT NULL AND
894 p_eam_wo_comp_rec.shutdown_end_date IS NOT NULL THEN
895 if (l_asset_number is not null) then --If not Non-Serialized Rebuild
896 process_shutdown
897 ( s_asset_group_id => l_asset_group_id,
898 s_organization_id => p_eam_wo_comp_rec.organization_id,
899 s_asset_number => l_asset_number,
900 s_start_date => p_eam_wo_comp_rec.shutdown_start_date,
901 s_end_date => p_eam_wo_comp_rec.shutdown_end_date,
902 s_user_id => fnd_global.user_id,
903 s_maintenance_object_type => i_maintenance_object_type, -- Fix for Bug 3448770
904 s_maintenance_object_id => i_maintenance_object_id,
905 s_wip_entity_id => p_eam_wo_comp_rec.wip_entity_id
906 );
907 End IF;
908 END IF;
909 END IF; --end of check for transaction type=completion
910
911
912 UPDATE WIP_DISCRETE_JOBS
913 SET last_update_date = sysdate,
914 last_updated_by = FND_GLOBAL.user_id,
915 last_update_login = FND_GLOBAL.login_id,
916 status_type = i_status_type,
917 date_completed = i_completion_date,
918 request_id = p_eam_wo_comp_rec.request_id,
919 program_application_id = p_eam_wo_comp_rec.program_application_id,
920 program_id = p_eam_wo_comp_rec.program_id,
921 program_update_date = p_eam_wo_comp_rec.program_update_date
922 WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
923 AND organization_id = p_eam_wo_comp_rec.organization_id;
924
925 UPDATE EAM_WORK_ORDER_DETAILS
926 SET USER_DEFINED_STATUS_ID = p_eam_wo_comp_rec.user_status_id
927 WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
928 AND organization_id = p_eam_wo_comp_rec.organization_id ;
929
930 -- Update wip_eam_work_requests table
931 UPDATE WIP_EAM_WORK_REQUESTS
932 SET work_request_status_id = i_work_request_status,
933 last_update_date = sysdate,
934 last_updated_by = fnd_global.user_id,
935 last_update_login = fnd_global.login_id
936 WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;
937
938
939
940 x_return_status := FND_API.G_RET_STS_SUCCESS;
941 EXCEPTION
942 WHEN OTHERS THEN
943 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
944 ( p_message_name => NULL
945 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
946 , x_mesg_token_Tbl => x_mesg_token_tbl
947 );
948
949 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_UNEXPECTED;
950 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
951 EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.update_row : Exception3 : '|| SQLERRM);
952 END IF;
953
954 END update_row;
955
956
957 END EAM_WO_COMP_UTILITY_PVT;