[Home] [Help]
PACKAGE BODY: APPS.WIP_EAMMTLPROC_PRIV
Source
1 package body wip_eamMtlProc_priv as
2 /* $Header: wipemppb.pls 120.11 2011/01/03 09:48:39 vchidura ship $ */
3
4 procedure fillIssueParamTbl(p_compRec IN wip_mtlTempProc_grp.comp_rec_t,
5 x_params OUT NOCOPY wip_logger.param_tbl_t);
6
7
8 procedure validateTxns(p_txnHdrID IN NUMBER,
9 x_returnStatus OUT NOCOPY VARCHAR2) is
10 l_params wip_logger.param_tbl_t;
11 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
12 l_returnStatus VARCHAR2(1);
13 l_errMsg VARCHAR2(240);
14
15
16 begin
17 if (l_logLevel <= wip_constants.trace_logging) then
18 l_params(1).paramName := 'p_txnHdrID';
19 l_params(1).paramValue := p_txnHdrID;
20 wip_logger.entryPoint(p_procName => 'wip_eamMtlProc_priv.validateTxns',
21 p_params => l_params,
22 x_returnStatus => x_returnStatus);
23 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
24 raise fnd_api.g_exc_unexpected_error;
25 end if;
26 end if;
27 x_returnStatus := fnd_api.g_ret_sts_success;
28
29
30
31 --rebuildable columns(rebuild_item_id, rebuild_serial_number, rebuild_activity_id, rebuild_job_name)
32 --not allowed unless jobs is eam
33 fnd_message.set_name('WIP', 'WIP_MTI_REB_COL_NOT_ALLOWED');
34 l_errMsg := substrb(fnd_message.get, 1, 240);
35 update mtl_transactions_interface mti
36 set last_update_date = sysdate,
37 last_updated_by = fnd_global.user_id,
38 last_update_login = fnd_global.login_id,
39 program_application_id = fnd_global.prog_appl_id,
40 program_id = fnd_global.conc_program_id,
41 program_update_date = sysdate,
42 request_id = fnd_global.conc_request_id,
43 process_flag = 3,
44 lock_flag = 2,
45 error_code = 'WIP_ENTITY_TYPE',
46 error_explanation = l_errMsg
47 where transaction_header_id = p_txnHdrID
48 and process_flag = 1
49 and transaction_source_type_id = 5
50 and wip_entity_type <> wip_constants.eam
51 and ( rebuild_item_id is not null
52 or rebuild_serial_number is not null
53 or rebuild_job_name is not null
54 or rebuild_activity_id is not null);
55
56
57
58 --rebuildable columns(rebuild_item_id, rebuild_serial_number, rebuild_activity_id, rebuild_job_name)
59 --not allowed unless item being issued is a rebuild item
60 fnd_message.set_name('WIP', 'WIP_MTI_INV_ITEM_NOT_REBLD');
61 l_errMsg := substrb(fnd_message.get, 1, 240);
62 update mtl_transactions_interface mti
63 set last_update_date = sysdate,
64 last_updated_by = fnd_global.user_id,
65 last_update_login = fnd_global.login_id,
66 program_application_id = fnd_global.prog_appl_id,
67 program_id = fnd_global.conc_program_id,
68 program_update_date = sysdate,
69 request_id = fnd_global.conc_request_id,
70 process_flag = 3,
71 lock_flag = 2,
72 error_code = 'INVENTORY_ITEM_ID',
73 error_explanation = l_errMsg
74 where transaction_header_id = p_txnHdrID
75 and process_flag = 1
76 and transaction_source_type_id = 5
77 and rebuild_item_id is not null
78 and not exists (select 1
79 from mtl_system_items msi
80 where msi.inventory_item_id = mti.inventory_item_id
81 and msi.organization_id = mti.organization_id
82 and msi.eam_item_type = 3);
83
84 --rebuild item must be populated if any other columns are populated
85 fnd_message.set_name('WIP', 'WIP_MTI_REB_ITEM_MISSING');
86 l_errMsg := substrb(fnd_message.get, 1, 240);
87 update mtl_transactions_interface mti
88 set last_update_date = sysdate,
89 last_updated_by = fnd_global.user_id,
90 last_update_login = fnd_global.login_id,
91 program_application_id = fnd_global.prog_appl_id,
92 program_id = fnd_global.conc_program_id,
93 program_update_date = sysdate,
94 request_id = fnd_global.conc_request_id,
95 process_flag = 3,
96 lock_flag = 2,
97 error_code = 'REBUILD_ITEM_ID',
98 error_explanation = l_errMsg
99 where transaction_header_id = p_txnHdrID
100 and process_flag = 1
101 and transaction_source_type_id = 5
102 and rebuild_item_id is null
103 and ( rebuild_serial_number is not null
104 or rebuild_job_name is not null
105 or rebuild_activity_id is not null);
106
107
108
109 --item must exist in organization
110 --item must be rebuildable
111 fnd_message.set_name('WIP', 'WIP_ML_EAM_REBUILD_ITEM');
112 l_errMsg := substrb(fnd_message.get, 1, 240);
113 update mtl_transactions_interface mti
114 set last_update_date = sysdate,
115 last_updated_by = fnd_global.user_id,
116 last_update_login = fnd_global.login_id,
117 program_application_id = fnd_global.prog_appl_id,
118 program_id = fnd_global.conc_program_id,
119 program_update_date = sysdate,
120 request_id = fnd_global.conc_request_id,
121 process_flag = 3,
122 lock_flag = 2,
123 error_code = 'REBUILD_ITEM_ID',
124 error_explanation = l_errMsg
125 where transaction_header_id = p_txnHdrID
126 and process_flag = 1
127 and transaction_source_type_id = 5
128 and rebuild_item_id is not null
129 and not exists(select 1
130 from mtl_system_items msi, mtl_parameters mp
131 where mti.rebuild_item_id = msi.inventory_item_id
132 and msi.organization_id = mp.organization_id
133 and mp.maint_organization_id = mti.organization_id
134 and msi.eam_item_type = 3
135 );
136
137
138 --item must exist in organization
139 --item must be an activity
140 fnd_message.set_name('WIP', 'WIP_ML_EAM_ACTIVITY');
141 l_errMsg := substrb(fnd_message.get, 1, 240);
142 update mtl_transactions_interface mti
143 set last_update_date = sysdate,
144 last_updated_by = fnd_global.user_id,
145 last_update_login = fnd_global.login_id,
146 program_application_id = fnd_global.prog_appl_id,
147 program_id = fnd_global.conc_program_id,
148 program_update_date = sysdate,
149 request_id = fnd_global.conc_request_id,
150 process_flag = 3,
151 lock_flag = 2,
152 error_code = 'REBUILD_ACTIVITY_ID',
153 error_explanation = l_errMsg
154 where transaction_header_id = p_txnHdrID
155 and process_flag = 1
156 and transaction_source_type_id = 5
157 and rebuild_activity_id is not null
158 and not exists(select 1
159 from mtl_system_items msi
160 where mti.rebuild_activity_id = msi.inventory_item_id
161 and mti.organization_id = msi.organization_id
162 and msi.eam_item_type = 2);
163
164
165
166 --txn type must be issue when rebuild columns are populated
167 fnd_message.set_name('WIP', 'WIP_MTI_REBUILD_TXN_TYPE');
168 l_errMsg := substrb(fnd_message.get, 1, 240);
169 update mtl_transactions_interface mti
170 set last_update_date = sysdate,
171 last_updated_by = fnd_global.user_id,
172 last_update_login = fnd_global.login_id,
173 program_application_id = fnd_global.prog_appl_id,
174 program_id = fnd_global.conc_program_id,
175 program_update_date = sysdate,
176 request_id = fnd_global.conc_request_id,
177 process_flag = 3,
178 lock_flag = 2,
179 error_code = 'TRANSACTION_TYPE_ID',
180 error_explanation = l_errMsg
181 where transaction_header_id = p_txnHdrID
182 and process_flag = 1
183 and transaction_source_type_id = 5
184 and transaction_action_id <> wip_constants.isscomp_action
185 and rebuild_item_id is not null;
186
187
188
189 --primary txn qty must be 1 when transacting rebuildable items
190 fnd_message.set_name('WIP', 'WIP_MTI_REBUILD_QTY');
191 l_errMsg := substrb(fnd_message.get, 1, 240);
192 update mtl_transactions_interface mti
193 set last_update_date = sysdate,
194 last_updated_by = fnd_global.user_id,
195 last_update_login = fnd_global.login_id,
196 program_application_id = fnd_global.prog_appl_id,
197 program_id = fnd_global.conc_program_id,
198 program_update_date = sysdate,
199 request_id = fnd_global.conc_request_id,
200 process_flag = 3,
201 lock_flag = 2,
202 error_code = 'PRIMARY_QUANTITY',
203 error_explanation = l_errMsg
204 where transaction_header_id = p_txnHdrID
205 and process_flag = 1
206 and transaction_source_type_id = 5
207 and wip_entity_type = wip_constants.eam -- bug 9312526, this validation is only for eAM WOs not WIP
208 and abs(primary_quantity) <> 1
209 and exists (select 1
210 from mtl_system_items msi
211 where msi.inventory_item_id = mti.inventory_item_id
212 and msi.organization_id = mti.organization_id
213 and msi.eam_item_type = 3);
214
215
216
217 --rebuild job name already exists in this organization
218 fnd_message.set_name('WIP', 'WIP_ML_JOB_NAME');
219 l_errMsg := substrb(fnd_message.get, 1, 240);
220 update mtl_transactions_interface mti
221 set last_update_date = sysdate,
222 last_updated_by = fnd_global.user_id,
223 last_update_login = fnd_global.login_id,
224 program_application_id = fnd_global.prog_appl_id,
225 program_id = fnd_global.conc_program_id,
226 program_update_date = sysdate,
227 request_id = fnd_global.conc_request_id,
228 process_flag = 3,
229 lock_flag = 2,
230 error_code = 'REBUILD_JOB_NAME',
231 error_explanation = l_errMsg
232 where transaction_header_id = p_txnHdrID
233 and process_flag = 1
234 and transaction_source_type_id = 5
235 and rebuild_item_id is not null
236 and rebuild_job_name is not null
237 and exists (select 1
238 from wip_entities we
239 where mti.rebuild_job_name = we.wip_entity_name
240 and mti.organization_id = we.organization_id);
241
242
243
244 --IB: anjgupta: check this Query one more time!!!!!!!!!!!!!!!!
245 --activity not valid for this rebuild item
246 fnd_message.set_name('WIP', 'WIP_MTI_NO_ACTIVITY_ASSOC');
247 l_errMsg := substrb(fnd_message.get, 1, 240);
248 update mtl_transactions_interface mti
249 set last_update_date = sysdate,
250 last_updated_by = fnd_global.user_id,
251 last_update_login = fnd_global.login_id,
252 program_application_id = fnd_global.prog_appl_id,
253 program_id = fnd_global.conc_program_id,
254 program_update_date = sysdate,
255 request_id = fnd_global.conc_request_id,
256 process_flag = 3,
257 lock_flag = 2,
258 error_code = 'REBUILD_ACTIVITY_ID',
259 error_explanation = l_errMsg
260 where transaction_header_id = p_txnHdrID
261 and process_flag = 1
262 and transaction_source_type_id = 5
263 and rebuild_item_id is not null
264 and rebuild_activity_id is not null
265 and not exists (select 1
266 from mtl_eam_asset_activities meaa, csi_item_instances cii, eam_org_maint_defaults eomd
267 where mti.rebuild_activity_id = meaa.asset_activity_id
268 and meaa.activity_association_id = eomd.object_id
269 and eomd.object_type = 60
270 and eomd.organization_id = mti.organization_id
271 and cii.inventory_item_id = mti.rebuild_item_id
272 and cii.serial_number = mti.rebuild_serial_number
273 and meaa.maintenance_object_id = cii.instance_id
274 and meaa.maintenance_object_type = 3
275 and nvl(meaa.start_date_active, mti.transaction_date - 1) <= mti.transaction_date
276 and nvl(meaa.end_date_active, mti.transaction_date + 1) >= mti.transaction_date);
277
278
279
280 --rebuild item must be serial controlled if rebuild serial number provided
281 fnd_message.set_name('WIP', 'WIP_MTI_REBUILD_SN_CNTRL');
282 l_errMsg := substrb(fnd_message.get, 1, 240);
283 update mtl_transactions_interface mti
284 set last_update_date = sysdate,
285 last_updated_by = fnd_global.user_id,
286 last_update_login = fnd_global.login_id,
287 program_application_id = fnd_global.prog_appl_id,
288 program_id = fnd_global.conc_program_id,
289 program_update_date = sysdate,
290 request_id = fnd_global.conc_request_id,
291 process_flag = 3,
292 lock_flag = 2,
293 error_code = 'REBUILD_ITEM_ID',
294 error_explanation = l_errMsg
295 where transaction_header_id = p_txnHdrID
296 and process_flag = 1
297 and transaction_source_type_id = 5
298 and rebuild_item_id is not null
299 and rebuild_serial_number is not null
300 and not exists(select 1
301 from mtl_system_items msi, mtl_parameters mp
302 where mti.rebuild_item_id = msi.inventory_item_id
303 and mti.organization_id = mp.maint_organization_id
304 and mp.organization_id = msi.organization_id
305 and msi.serial_number_control_code in (wip_constants.full_sn,
306 wip_constants.dyn_rcv_sn));
307
308
309
310
311
312 --serial number must exist in organization
313 -- must be:
314 -- + defined not used
315 -- + issued out and in the asset's genealogy
316 fnd_message.set_name('WIP', 'WIP_ML_EAM_REBUILD_SERIAL');
317 l_errMsg := substrb(fnd_message.get, 1, 240);
318 update mtl_transactions_interface mti
319 set last_update_date = sysdate,
320 last_updated_by = fnd_global.user_id,
321 last_update_login = fnd_global.login_id,
322 program_application_id = fnd_global.prog_appl_id,
323 program_id = fnd_global.conc_program_id,
324 program_update_date = sysdate,
325 request_id = fnd_global.conc_request_id,
326 process_flag = 3,
327 lock_flag = 2,
328 error_code = 'REBUILD_ITEM_ID',
329 error_explanation = l_errMsg
330 where transaction_header_id = p_txnHdrID
331 and process_flag = 1
332 and transaction_source_type_id = 5
333 and rebuild_item_id is not null
334 and rebuild_serial_number is not null
335 and not exists(select 1 --subquery verifies rebuild sn is predefined or (issued out/in stores and in the asset's genealogy)
336 from mtl_serial_numbers msn
337 where mti.rebuild_item_id = msn.inventory_item_id
338 and mti.rebuild_serial_number = msn.serial_number
339 and ( msn.current_status = 1 --defined not used
340 or ( msn.current_status in (3,4) --issued out or in stores
341 and exists(select 1
342 from wip_discrete_jobs wdj, mtl_object_genealogy mog,
343 csi_item_instances cii, mtl_serial_numbers msn_parent
344 where wdj.maintenance_object_id = cii.instance_id
345 and wdj.maintenance_object_type = 3
346 and wdj.wip_entity_id = mti.transaction_source_id
347 and cii.inventory_item_id = msn_parent.inventory_item_id
348 and cii.serial_number = msn_parent.serial_number
349 and msn_parent.gen_object_id = mog.parent_object_id --work order's gen_object_id
350 and msn.gen_object_id = mog.object_id --rebuild item's gen_object_id
351 and mog.start_date_active <= mti.transaction_date
352 and (mog.end_date_active is null or mog.end_date_active >= mti.transaction_date)))));
353
354 if (l_logLevel <= wip_constants.trace_logging) then
355 wip_logger.exitPoint(p_procName => 'wip_eamMtlProc_validateTxns',
356 p_procReturnStatus => x_returnStatus,
357 p_msg => 'success',
358 x_returnStatus => l_returnStatus); --discard logging return status
359 end if;
360 exception
361 when others then
362 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
363 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_eamMtlProc_priv',
364 p_procedure_name => 'validateTxns',
365 p_error_text => SQLERRM);
366
367 if (l_logLevel <= wip_constants.trace_logging) then
368 wip_logger.exitPoint(p_procName => 'wip_eamMtlProc_validateTxns',
369 p_procReturnStatus => x_returnStatus,
370 p_msg => 'unexp error:' || SQLERRM,
371 x_returnStatus => l_returnStatus); --discard logging return status
372 end if;
373 end validateTxns;
374
375 procedure processCompTxn(p_compRec IN wip_mtlTempProc_grp.comp_rec_t,
376 x_returnStatus OUT NOCOPY VARCHAR2) is
377 l_params wip_logger.param_tbl_t;
378 l_msgData VARCHAR2(2000);
379 l_returnStatus VARCHAR2(1);
380 l_maintObjID NUMBER;
381 l_maintGenObjID NUMBER;
382 l_maintObjType NUMBER;
383 l_maintObjSrc NUMBER;
384 l_errMsg VARCHAR2(2000);
385 l_msgCount NUMBER;
386 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
387
388 type char_tbl_t is table of varchar2(30);
389 l_serialNumTbl char_tbl_t := char_tbl_t(null);
390 --bug 10262178
391 l_to_serialNumTbl char_tbl_t := char_tbl_t(null);
392 l_count_temp NUMBER;
393 l_row_count_temp NUMBER;
394 l_nxt_srl_num VARCHAR2(80);
395 l_eam_itemType NUMBER;
396 --bug 10262178
397
398 begin
399 if (l_logLevel <= wip_constants.trace_logging) then
400 fillIssueParamTbl(p_compRec => p_compRec,
401 x_params => l_params);
402 wip_logger.entryPoint(p_procName => 'wip_eamMtlProc_priv.processCompTxn',
403 p_params => l_params,
404 x_returnStatus => x_returnStatus);
405 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
406 raise fnd_api.g_exc_unexpected_error;
407 end if;
408 end if;
409 x_returnStatus := fnd_api.g_ret_sts_success;
410
411 if(p_compRec.eamItemType is null or
412 ((p_compRec.eamItemType <> wip_constants.rebuild_item_type) and
413 (p_compRec.eamItemType <> 1))) then
414 if (l_logLevel <= wip_constants.trace_logging) then
415 wip_logger.exitPoint(p_procName => 'wip_eamMtlProc_priv.processCompTxn',
416 p_procReturnStatus => x_returnStatus,
417 p_msg => 'success(not an eam item)',
418 x_returnStatus => l_returnStatus); --discard logging return status
419 end if;
420 return;
421 end if;
422 --if the issued item is serial controlled, we need to insert a new row in the
423 --assets genealogy w/the serial number.
424 if(p_compRec.serialControlCode in (wip_constants.full_sn, wip_constants.dyn_rcv_sn)) then
425
426 --rebuildable item transactions must always only involve a qty of 1.
427 --we have already checked the serial_control_code so a serial number must be in MSNT
428 if(p_compRec.lotControlCode = wip_constants.no_lot) then
429 select fm_serial_number
430 bulk collect into l_serialNumTbl
431 from mtl_serial_numbers_temp
432 where transaction_temp_id = p_compRec.txnTmpID;
433
434 select to_serial_number
435 bulk collect into l_to_serialNumTbl
436 from mtl_serial_numbers_temp
437 where transaction_temp_id = p_compRec.txnTmpID; --bug 10262178
438
439 else
440 select fm_serial_number
441 bulk collect into l_serialNumTbl
442 from mtl_serial_numbers_temp
443 where transaction_temp_id = (select serial_transaction_temp_id
444 from mtl_transaction_lots_temp
445 where transaction_temp_id = p_compRec.txnTmpID);
446
447 select to_serial_number
448 bulk collect into l_to_serialNumTbl
449 from mtl_serial_numbers_temp
450 where transaction_temp_id = (select serial_transaction_temp_id
451 from mtl_transaction_lots_temp
452 where transaction_temp_id = p_compRec.txnTmpID); --bug 10262178
453
454 end if;
455
456 --We are inside the serial number loop, hence obj_type will be 3
457
458 select wdj.maintenance_object_id, wdj.maintenance_object_type,
459 wdj.maintenance_object_source, msn.gen_object_id
460 into l_maintObjID, l_maintObjType, l_maintObjSrc, l_maintGenObjID
461 from wip_discrete_jobs wdj, csi_item_instances cii, mtl_serial_numbers msn
462 where wdj.wip_entity_id = p_compRec.wipEntityID
463 and wdj.maintenance_object_type = 3
464 and wdj.maintenance_object_id = cii.instance_id
465 and msn.serial_number (+) = cii.serial_number --Modified outer join for bug 6892336
466 and msn.inventory_item_id (+) = cii.inventory_item_id; --Modified outer join for bug 6892336
467 --and msn.current_organization_id = cii.last_vld_organization_id;
468
469 IF l_maintGenObjID IS NULL THEN -- Added for bug 6892336
470 l_maintGenObjID := l_maintObjID;
471 END IF;
472
473 --obj type=3 means maintenance_object_id is a instance_id in CII
474 --obj src=1 to make sure WO is not from ASO.
475 --this check should be moved to an EAM package.
476 if(l_maintObjType = 3 and l_maintObjSrc = 1) then
477 if(p_compRec.txnActionID = wip_constants.isscomp_action) then
478 --insert the issued item into the asset's genealogy
479 if (l_logLevel <= wip_constants.full_logging) then
480 wip_logger.log('calling create gen', l_returnStatus);
481 end if;
482 x_returnStatus := fnd_api.g_ret_sts_success;
483
484 if (l_logLevel <= wip_constants.full_logging) then
485 wip_logger.log('maintenance object id is: ' || l_maintObjID, l_returnStatus);
486 wip_logger.log('maintenance object type is: ' || l_maintObjType, l_returnStatus);
487 wip_logger.log('maintenance object source is: ' || l_maintObjSrc, l_returnStatus);
488 wip_logger.log('gen object id is: ' || l_maintGenObjID, l_returnStatus);
489 end if;
490
491 l_eam_itemType := p_compRec.eamItemType;
492
493 IF(l_eam_itemType = 1) THEN --check for the capital item type
494
495 for i in 1..l_serialNumTbl.count
496 LOOP --LOOP FOR the Rows in the material Issue page
497
498 l_row_count_temp := inv_serial_number_pub.get_serial_diff(l_serialNumTbl(i), l_to_serialNumTbl(i)) ;
499
500 l_nxt_srl_num := l_serialNumTbl(i);
501
502 FOR j IN 1..l_row_count_temp --Loop for the serial_numbers in the one particular row of the transaction.
503 LOOP
504 wip_eam_genealogy_pvt.create_eam_genealogy(p_api_version => 1.0,
505 p_serial_number => l_nxt_srl_num,
506 p_inventory_item_id => p_compRec.itemID,
507 p_organization_id => p_compRec.orgID,
508 p_parent_object_id => l_maintGenObjID,
509 p_start_date_active => p_compRec.txnDate,
510 x_return_status => x_returnStatus,
511 x_msg_count => l_msgCount,
512 x_msg_data => l_msgData);
513
514 --If genealogy of one asset fails
515 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
516 l_errMsg := 'genealogy failed';
517 if(l_msgData is not null) then
518 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
519 fnd_message.set_token('MESSAGE', l_msgData);
520 fnd_msg_pub.add; --add the returned error message to the stack.
521 end if;
522 raise fnd_api.g_exc_unexpected_error;
523 end if;
524
525 l_nxt_srl_num := inv_serial_number_pub.increment_ser_num(l_nxt_srl_num,1);
526 END LOOP;
527 --end loop for a single row in the material issue page
528 END LOOP;
529 --end loop for the material issue page's multiple rows
530
531 -- END IF;
532 ELSE --if rebuild item
533 wip_eam_genealogy_pvt.create_eam_genealogy(p_api_version => 1.0,
534 p_serial_number => l_serialNumTbl(1),
535 p_inventory_item_id => p_compRec.itemID,
536 p_organization_id => p_compRec.orgID,
537 p_parent_object_id => l_maintGenObjID,
538 p_start_date_active => p_compRec.txnDate,
539 x_return_status => x_returnStatus,
540 x_msg_count => l_msgCount,
541 x_msg_data => l_msgData);
542 END IF; --end of check for the capital asset
543
544
545 if (l_logLevel <= wip_constants.full_logging) then
546 wip_logger.log('create gen returned: ' || x_returnStatus, l_returnStatus);
547 end if;
548 elsif(p_compRec.txnActionID = wip_constants.retcomp_action) then
549 if (l_logLevel <= wip_constants.full_logging) then
550 wip_logger.log('calling update gen', l_returnStatus);
551 end if;
552 x_returnStatus := fnd_api.g_ret_sts_success;
553 for i in 1..l_serialNumTbl.count loop
554 wip_eam_genealogy_pvt.update_eam_genealogy(p_api_version => 1.0,
555 p_object_type => 2, /* serial number */
556 p_serial_number => l_serialNumTbl(i),
557 p_inventory_item_id => p_compRec.itemID,
558 p_organization_id => p_compRec.orgID,
559 p_genealogy_type => 5, /* asset item relationship*/
560 p_end_date_active => p_compRec.txnDate,
561 x_return_status => x_returnStatus,
562 x_msg_count => l_msgCount,
563 x_msg_data => l_msgData);
564 end loop;
565 if (l_logLevel <= wip_constants.full_logging) then
566 wip_logger.log('upd gen returned: ' || x_returnStatus, l_returnStatus);
567 end if;
568 end if;
569
570 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
571 l_errMsg := 'genealogy failed';
572 if(l_msgData is not null) then
573 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
574 fnd_message.set_token('MESSAGE', l_msgData);
575 fnd_msg_pub.add; --add the returned error message to the stack.
576 end if;
577 raise fnd_api.g_exc_unexpected_error;
578 end if;
579 end if;
580 end if; --For bug 6892336, we do not deal with genealogy for non-serial item
581
582 if(p_compRec.rebuildItemID > 0 and
583 p_compRec.txnActionID = wip_constants.isscomp_action) then
584 ----------------------------------------------------------------------------------
585 -- This call:
586 -- + inserts a record into wjsi
587 -- + submits the mass load concurrent request (will run when final commit occurs)
588 -- + updates the genealogy (removes the rebuild item if under serial control)
589 ----------------------------------------------------------------------------------
590 eam_rebuild.create_rebuild_job(p_tempId => p_compRec.txnTmpID,
591 x_retVal => x_returnStatus,
592 x_errMsg => l_msgData);
593 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
594 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
595 fnd_message.set_token('MESSAGE', l_msgData);
596 fnd_msg_pub.add; --add the returned error message to the stack.
597 l_errMsg := 'rebuild job creation failed';
598 raise fnd_api.g_exc_unexpected_error;
599 end if;
600 if (l_logLevel <= wip_constants.full_logging) then
601 wip_logger.log('rebuildable job creation succeeded', l_returnStatus);
602 end if;
603 end if;
604 if (l_logLevel <= wip_constants.trace_logging) then
605 wip_logger.exitPoint(p_procName => 'wip_eamMtlProc_priv.processCompTxn',
606 p_procReturnStatus => x_returnStatus,
607 p_msg => 'procedure success.',
608 x_returnStatus => l_returnStatus); --discard logging return status
609 end if;
610 exception
611 when fnd_api.g_exc_unexpected_error then
612 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
613 if (l_logLevel <= wip_constants.trace_logging) then
614 wip_logger.exitPoint(p_procName => 'wip_eamMtlProc_priv.processCompTxn',
615 p_procReturnStatus => x_returnStatus,
616 p_msg => l_errMsg,
617 x_returnStatus => l_returnStatus); --discard logging return status
618 end if;
619 when others then
620 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
621 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_eamMtlProc_priv',
622 p_procedure_name => 'processCompTxn',
623 p_error_text => SQLERRM);
624 if (l_logLevel <= wip_constants.trace_logging) then
625 wip_logger.exitPoint(p_procName => 'wip_eamMtlProc_priv.processCompTxn',
626 p_procReturnStatus => x_returnStatus,
627 p_msg => 'unexpected error: ' || SQLERRM,
628 x_returnStatus => l_returnStatus); --discard logging return status
629 end if;
630 end processCompTxn;
631
632 procedure fillIssueParamTbl(p_compRec IN wip_mtlTempProc_grp.comp_rec_t,
633 x_params OUT NOCOPY wip_logger.param_tbl_t)
634 is begin
635 x_params(1).paramName := 'p_compRec.wipEntityId';
636 x_params(1).paramValue := p_compRec.wipEntityId;
637 x_params(2).paramName := 'p_compRec.repLineID';
638 x_params(2).paramValue := p_compRec.repLineID;
639 x_params(3).paramName := 'p_compRec.orgID';
640 x_params(3).paramValue := p_compRec.orgID;
641 x_params(4).paramName := 'p_compRec.itemID';
642 x_params(4).paramValue := p_compRec.itemID;
643 x_params(5).paramName := 'p_compRec.opSeqNum';
644 x_params(5).paramValue := p_compRec.opSeqNum;
645 x_params(6).paramName := 'p_compRec.primaryQty';
646 x_params(6).paramValue := p_compRec.primaryQty;
647 x_params(7).paramName := 'p_compRec.txnQty';
648 x_params(7).paramValue := p_compRec.txnQty;
649 x_params(8).paramName := 'p_compRec.negReqFlag';
650 x_params(8).paramValue := p_compRec.negReqFlag;
651 x_params(9).paramName := 'p_compRec.wipSupplyType';
652 x_params(9).paramValue := p_compRec.wipSupplyType;
653 x_params(10).paramName := 'p_compRec.wipEntityType';
654 x_params(10).paramValue := p_compRec.wipEntityType;
655 x_params(11).paramName := 'p_compRec.supplySub';
656 x_params(11).paramValue := p_compRec.supplySub;
657 x_params(12).paramName := 'p_compRec.supplyLocID';
658 x_params(12).paramValue := p_compRec.supplyLocID;
659 x_params(13).paramName := 'p_compRec.txnDate';
660 x_params(13).paramValue := p_compRec.txnDate;
661 x_params(14).paramName := 'p_compRec.txnHdrID';
662 x_params(14).paramValue := p_compRec.txnHdrID;
663 x_params(15).paramName := 'p_compRec.movTxnID';
664 x_params(15).paramValue := p_compRec.movTxnID;
665 x_params(16).paramName := 'p_compRec.cplTxnID';
666 x_params(16).paramValue := p_compRec.cplTxnID;
667 x_params(17).paramName := 'p_compRec.mtlTxnID';
668 x_params(17).paramValue := p_compRec.mtlTxnID;
669 x_params(18).paramName := 'p_compRec.qaCollectionID';
670 x_params(18).paramValue := p_compRec.qaCollectionID;
671 x_params(19).paramName := 'p_compRec.deptID';
672 x_params(19).paramValue := p_compRec.deptID;
673 x_params(20).paramName := 'p_compRec.txnActionID';
674 x_params(20).paramValue := p_compRec.txnActionID;
675 x_params(21).paramName := 'p_compRec.serialControlCode';
676 x_params(21).paramValue := p_compRec.serialControlCode;
677 x_params(22).paramName := 'p_compRec.lotControlCode';
678 x_params(22).paramValue := p_compRec.lotControlCode;
679 x_params(23).paramName := 'p_compRec.eamItemType';
680 x_params(23).paramValue := p_compRec.eamItemType;
681 x_params(24).paramName := 'p_compRec.rebuildItemID';
682 x_params(24).paramValue := p_compRec.rebuildItemID;
683 x_params(25).paramName := 'p_compRec.rebuildJobName';
684 x_params(25).paramValue := p_compRec.rebuildJobName;
685 x_params(26).paramName := 'p_compRec.rebuildActivityID';
686 x_params(26).paramValue := p_compRec.rebuildActivityID;
687 x_params(27).paramName := 'p_compRec.rebuildSerialNumber';
688 x_params(27).paramValue := p_compRec.rebuildSerialNumber;
689
690 end fillIssueParamTbl;
691
692 end wip_eamMtlProc_priv;