[Home] [Help]
PACKAGE BODY: APPS.WIP_MTLTEMPPROC_PRIV
Source
1 package body wip_mtlTempProc_priv as
2 /* $Header: wiptmpvb.pls 120.5.12020000.2 2012/07/18 18:16:51 ankohli ship $ */
3
4 non_txactable_value CONSTANT VARCHAR2(1) := 'N';
5
6 g_pkgName VARCHAR2(30) := 'wip_mtlTempProc_priv';
7
8 procedure processTemp(p_initMsgList IN VARCHAR2,
9 p_txnHdrID IN NUMBER,
10 p_txnMode IN NUMBER,
11 p_destroyQtyTrees IN VARCHAR2 := null,
12 p_endDebug IN VARCHAR2 := null,
13 x_returnStatus OUT NOCOPY VARCHAR2,
14 x_errorMsg OUT NOCOPY VARCHAR2) IS
15 l_errMsg VARCHAR2(2000);
16 l_errCode VARCHAR2(2000);
17 l_params wip_logger.param_tbl_t;
18 l_retStatus VARCHAR2(1);
19 l_logRetStatus VARCHAR2(1);
20 l_retCode NUMBER;
21 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
22 l_flowSchedCount NUMBER;
23 l_invStatus NUMBER;
24 l_count NUMBER;
25 cursor c_rows is
26 select transaction_temp_id
27 from mtl_material_transactions_temp
28 where transaction_header_id = p_txnHdrID;
29
30 begin
31 -------------------------------------------------------------
32 --Initial Preprocessing
33 -------------------------------------------------------------
34
35 savepoint wiptmpvb0;
36 if (l_logLevel <= wip_constants.trace_logging) then
37 l_params(1).paramName := 'p_initMsgList';
38 l_params(1).paramValue := p_initMsgList;
39 l_params(2).paramName := 'p_txnHdrID';
40 l_params(2).paramValue := p_txnHdrID;
41 l_params(3).paramName := 'p_txnMode';
42 l_params(3).paramValue := p_txnMode;
43 l_params(4).paramName := 'p_destroyQtyTrees';
44 l_params(4).paramValue := p_destroyQtyTrees;
45 wip_logger.entryPoint(p_procName => 'wip_mtlTempProc_priv.processTemp',
46 p_params => l_params,
47 x_returnStatus => x_returnStatus);
48 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
49 raise fnd_api.g_exc_unexpected_error;
50 end if;
51 end if;
52 x_returnStatus := fnd_api.g_ret_sts_success;
53
54 if(fnd_api.to_boolean(p_initMsgList)) then
55 fnd_msg_pub.initialize;
56 end if;
57
58 l_retCode := inv_lpn_trx_pub.process_lpn_trx(p_trx_hdr_id => p_txnHdrID,
59 p_proc_mode => p_txnMode,
60 x_proc_msg => l_errMsg);
61
62 if(l_logLevel <= wip_constants.full_logging) then
63 wip_logger.log('inv returned:' || l_retCode, l_retStatus);
64 wip_logger.log('inv errmsg:' || l_errMsg, l_retStatus);
65 end if;
66
67 --for some reason TM errors clear the stack so we must put the
68 --error message back, but successful txns do not clear the stack
69 if(l_retCode <> 0) then
70 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
71 fnd_message.set_token('MESSAGE', l_errMsg);
72 fnd_msg_pub.add;
73 wip_utilities.get_message_stack(p_msg => x_errorMsg,
74 p_delete_stack => fnd_api.g_false);
75 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
76 else
77 wip_utilities.get_message_stack(p_msg => x_errorMsg,
78 p_delete_stack => fnd_api.g_false);
79 end if;
80
81
82 -------------------------------------------------------------
83 --Cleanup Processing
84 -------------------------------------------------------------
85 if(fnd_api.to_boolean(nvl(p_destroyQtyTrees, fnd_api.g_false))) then
86 inv_quantity_tree_pub.clear_quantity_cache;
87 end if;
88
89 if (l_logLevel <= wip_constants.trace_logging) then
90 wip_logger.exitPoint(p_procName => 'wip_mtlTempProc_priv.processTemp',
91 p_procReturnStatus => x_returnStatus,
92 p_msg => 'success',
93 x_returnStatus => l_retStatus);
94 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
95 wip_logger.cleanup(l_retStatus);
96 end if;
97 end if;
98 exception
99 when fnd_api.g_exc_unexpected_error then
100 rollback to wiptmpvb0;
101 wip_utilities.get_message_stack(p_msg => x_errorMsg,
102 p_delete_stack => fnd_api.g_false,
103 p_separator => ' ');
104 if (l_logLevel <= wip_constants.trace_logging) then
105 wip_logger.exitPoint(p_procName => 'wip_mtlTempProc_priv.processTemp',
106 p_procReturnStatus => x_returnStatus,
107 p_msg => 'unhandled exception: ' || SQLERRM,
108 x_returnStatus => l_retStatus);
109 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
110 wip_logger.cleanup(l_retStatus);
111 end if;
112 end if;
113 when others then
114 rollback to wiptmpvb0;
115 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
116 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlTempProc_priv',
117 p_procedure_name => 'processTemp',
118 p_error_text => SQLERRM);
119 wip_utilities.get_message_stack(p_msg => x_errorMsg,
120 p_delete_stack => fnd_api.g_false,
121 p_separator => ' ');
122 fnd_message.set_name('WIP', 'MTL_PROC_FAIL');
123 l_errCode := fnd_message.get;
124
125 update mtl_material_transactions_temp
126 set process_flag = 'E',
127 error_code = substr(l_errCode,1,240),
128 error_explanation = substr(x_errorMsg,1,240)
129 where transaction_header_id = p_txnHdrID
130 and process_flag in ('Y', 'W')
131 and transaction_source_type_id = 5
132 and transaction_action_id in (wip_constants.isscomp_action,
133 wip_constants.retcomp_action,
134 wip_constants.issnegc_action,
135 wip_constants.retnegc_action);
136
137 if (l_logLevel <= wip_constants.trace_logging) then
138 wip_logger.exitPoint(p_procName => 'wip_mtlTempProc_priv.processTemp',
139 p_procReturnStatus => x_returnStatus,
140 p_msg => 'unhandled exception: ' || SQLERRM,
141 x_returnStatus => l_retStatus);
142 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
143 wip_logger.cleanup(l_retStatus);
144 end if;
145 end if;
146 end processTemp;
147
148 procedure processWIP(p_txnTmpID IN NUMBER,
149 p_processLpn IN VARCHAR2,
150 p_endDebug in VARCHAR2 := null,
151 x_returnStatus OUT NOCOPY VARCHAR2,
152 x_errorMsg OUT NOCOPY VARCHAR2) is
153 l_retStatus VARCHAR2(1);
154 l_params wip_logger.param_tbl_t;
155 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
156 l_procName VARCHAR2(20) := 'processWIP';
157 l_txnActionID NUMBER;
158 l_wipEntityType NUMBER;
159 l_orgID NUMBER;
160 l_wipEntityID NUMBER;
161 l_lpnID NUMBER;
162 l_repLineID NUMBER;
163 l_postingFlag VARCHAR2(1);
164 l_flowSchedule VARCHAR2(1);
165 l_opSeq number;
166 l_supplyType number;
167 -- Bug 13986251
168 l_parent_inv_id number ; /* 5949023 */
169 l_comp_txn_id number ; /* 5949023 */
170 l_inv_id number ; /* 5949023 */
171 /*Bug 6417742 (FP of 6342851): Added following variables*/
172 l_transaction_header_id number;
173 l_transaction_type_id number;
174 returnStatus varchar2(1);
175 l_msg_count number;
176 error varchar2(241);
177 labelStatus varchar2(1);
178 l_msg_stack VARCHAR2(2000);
179 dummyErrCode VARCHAR2(1);
180 -- Bug 13986251
181 l_assy_processed number := 0; /*Added for bug 7009282 */
182 begin
183 savepoint wiptmpvb100;
184
185 if (l_logLevel <= wip_constants.trace_logging) then
186 l_params(1).paramName := 'p_txnTmpID';
187 l_params(1).paramValue := p_txnTmpID;
188 l_params(2).paramName := 'p_processLpn';
189 l_params(2).paramValue := p_processLpn;
190 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
191 p_params => l_params,
192 x_returnStatus => x_returnStatus);
193 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
194 raise fnd_api.g_exc_unexpected_error;
195 end if;
196 end if;
197
198 x_returnStatus := fnd_api.g_ret_sts_success;
199
200 select transaction_action_id,
201 transaction_header_id, /*Added for bug 6417742(FP of 6342851)*/
202 transaction_type_id, /*Added for bug 6417742(FP of 6342851)*/
203 decode(flow_schedule, --treat wol cpls as flow
204 'Y', wip_constants.flow,
205 wip_entity_type),
206 nvl(content_lpn_id, lpn_id),
207 transaction_source_id,
208 organization_id,
209 posting_flag,
210 flow_schedule,
211 operation_seq_num,
212 wip_supply_type,
213 inventory_item_id,
214 completion_transaction_id
215 into l_txnActionID,
216 l_transaction_header_id, /*Added for bug 6417742(FP of 6342851)*/
217 l_transaction_type_id, /*Added for bug 6417742(FP of 6342851)*/
218 l_wipEntityType,
219 l_lpnID,
220 l_wipEntityID,
221 l_orgID,
222 l_postingFlag,
223 l_flowSchedule,
224 l_opSeq,
225 l_supplyType,
226 l_inv_id,
227 l_comp_txn_id
228 from mtl_material_transactions_temp
229 where transaction_temp_id = p_txnTmpID
230 and transaction_source_type_id = 5;
231
232 -- delete the record if it is for phantom
233 if ( l_opSeq < 0 and l_supplyType = 6 ) then
234 -- Bug 13986251
235 /*Bug 7009282: Added exception handling block for below SQL*/
236 BEGIN
237 /* Fix for Bug#5949023 */
238 -- Get Inventory item id of parent
239 select inventory_item_id
240 into l_parent_inv_id
241 from mtl_material_transactions_temp
242 where transaction_action_id in (30, 31, 32)
243 and transaction_source_type_id = 5
244 and transaction_source_id = l_wipEntityID
245 and completion_transaction_id = l_comp_txn_id
246 and flow_schedule = 'Y' ;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 /*If assy record is not found that means assy record is already processed
250 and phantom record can be deleted. Set the flag accordingly.*/
251 l_assy_processed := 1;
252 END;
253
254 -- Bug 13986251
255 -- delete only if inv_id of phantom is less than Assembly
256 -- or if parent record is processed. (Added as part of fix for bug 7009282*/
257 if (l_parent_inv_id < l_inv_id OR l_assy_processed = 1) then
258 delete mtl_material_transactions_temp
259 where transaction_temp_id = p_txnTmpID;
260
261 if(l_logLevel <= wip_constants.trace_logging) then
262 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
263 p_procReturnStatus => x_returnStatus,
264 p_msg => 'success: phantom record deleted without any action',
265 x_returnStatus => l_retStatus);
266 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
267 wip_logger.cleanup(l_retStatus);
268 end if;
269 end if;
270 end if;
271 return;
272 end if;
273
274 --wip entity type should be populated. do this as an added check
275 if(l_wipEntityType is null) then
276 select entity_type
277 into l_wipEntityType
278 from wip_entities
279 where wip_entity_id = l_wipEntityID
280 and organization_id = l_orgID;
281 end if;
282
283 if(l_logLevel <= wip_constants.full_logging) then
284 wip_logger.log('txnActID: ' || l_txnActionID, l_retStatus);
285 wip_logger.log('wipEntityType: ' || l_wipEntityType, l_retStatus);
286 wip_logger.log('lpnID: ' || l_lpnID, l_retStatus);
287 wip_logger.log('wipEntityID: ' || l_wipEntityID, l_retStatus);
288 wip_logger.log('orgID: ' || l_orgID, l_retStatus);
289 wip_logger.log('postingFlag: ' || l_postingFlag, l_retStatus);
290 wip_logger.log('FlowSchedule: ' || l_flowSchedule, l_retStatus);
291 end if;
292
293 --component issues
294 if(l_txnActionID in (wip_constants.isscomp_action, wip_constants.retcomp_action,
295 wip_constants.issnegc_action, wip_constants.retnegc_action)) then
296 --no action is necessary for flow txns
297 if(l_wipEntityType <> wip_constants.flow) then
298 if(l_logLevel <= wip_constants.full_logging) then
299 wip_logger.log('calling component processor', l_retStatus);
300 end if;
301 wip_mtlProc_priv.processTemp(p_initMsgList => fnd_api.g_false,
302 p_endDebug => fnd_api.g_false,
303 p_txnTmpID => p_txnTmpID,
304 x_returnStatus => x_returnStatus);
305 end if;
306
307 elsif(l_txnActionID in (wip_constants.cplassy_action, wip_constants.retassy_action)) then
308 --no action is necessary for lpn completions
309 if(l_lpnID is null or fnd_api.to_boolean(p_processLpn)) then
310 if(l_wipEntityType = wip_constants.flow) then
311 if(l_logLevel <= wip_constants.full_logging) then
312 wip_logger.log('calling flow/wol processor', l_retStatus);
313 end if;
314 wip_cfmProc_priv.processTemp(p_initMsgList => fnd_api.g_false,
315 p_txnTempID => p_txnTmpID,
316 x_returnStatus => x_returnStatus);
317 else --non flow completion
318 if(l_logLevel <= wip_constants.full_logging) then
319 wip_logger.log('calling cpl processor', l_retStatus);
320 end if;
321 wip_cplProc_priv.processTemp(p_txnTmpID => p_txnTmpID,
322 p_initMsgList => fnd_api.g_false,
323 p_endDebug => fnd_api.g_false,
324 x_returnStatus => x_returnStatus);
325 end if;
326 end if;
327 elsif(l_txnActionID = wip_constants.scrassy_action) then
328 --for repetitive, must do allocation
329 if(l_wipEntityType = wip_constants.repetitive) then
330 if(l_logLevel <= wip_constants.full_logging) then
331 wip_logger.log('calling rep scrap processor', l_retStatus);
332 end if;
333 wip_movProc_priv.repetitive_scrap(p_tmp_id => p_txnTmpID,
334 x_returnStatus => x_returnStatus);
335 elsif (l_wipEntityType = wip_constants.flow) then
336 if(l_logLevel <= wip_constants.full_logging) then
337 wip_logger.log('calling flow/wol processor', l_retStatus);
338 end if;
339 wip_cfmProc_priv.processTemp(p_initMsgList => fnd_api.g_false,
340 p_txnTempID => p_txnTmpID,
341 x_returnStatus => x_returnStatus);
342 end if;
343 end if;
344
345 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
346 raise fnd_api.g_exc_unexpected_error;
347 end if;
348
349 if(l_logLevel <= wip_constants.trace_logging) then
350 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
351 p_procReturnStatus => x_returnStatus,
352 p_msg => 'success',
353 x_returnStatus => l_retStatus);
354 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
355 wip_logger.cleanup(l_retStatus);
356 end if;
357 end if;
358
359 /*Start - Bug 6417742(FP of 6342851): Added call label printing*/
360 /* Fix bug 8901142 (FP of 8585291) Added criteria to prevent printing label
361 twice for LPN completion/drop transaction */
362 if ( l_txnActionID = WIP_CONSTANTS.CPLASSY_ACTION and
363 l_flowSchedule = 'Y' and
364 l_transaction_type_id = WIP_CONSTANTS.CPLASSY_TYPE and
365 (l_lpnID is null or fnd_api.to_boolean(p_processLpn))) then
366 -- print label
367 wip_utilities.print_label(p_txn_id => l_transaction_header_id, -- should be transaction header id
368 p_table_type => 2, --MMTT
369 p_ret_status => returnStatus,
370 p_msg_count => l_msg_count,
371 p_msg_data => error,
372 p_label_status => labelStatus,
373 p_business_flow_code => 33); -- discrete business flow code
374
375 -- do not error out if label printing, only put warning message in log
376 if(returnStatus <> fnd_api.g_ret_sts_success) then
377 WIP_UTILITIES.get_message_stack(p_msg => l_msg_stack);
378 if (l_logLevel <= wip_constants.full_logging) then
379 wip_logger.log(p_msg => 'An error has occurred with label printing.\n' ||
380 'The following error has occurred during ' ||
381 'printing: ' || l_msg_stack || '\n' ||
382 'Please check the Inventory log file for more ' ||
383 'information.',
384 x_returnStatus =>dummyErrCode);
385 end if;
386 end if;
387 if (l_logLevel <= wip_constants.full_logging) then
388 wip_logger.log(p_msg => 'Label printing returned with status ' || returnStatus,
389 x_returnStatus => dummyErrCode);
390 end if;
391 end if;
392 /*End - Bug 6417742(FP of 6342851): Added call label printing*/
393
394
395 exception
396 when fnd_api.g_exc_unexpected_error then
397 rollback to wiptmpvb100;
398 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
399 wip_utilities.get_message_stack(p_msg => x_errorMsg,
400 p_delete_stack => fnd_api.g_false);
401 if(l_logLevel <= wip_constants.trace_logging) then
402 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
403 p_procReturnStatus => x_returnStatus,
404 p_msg => 'error:' || x_errorMsg,
405 x_returnStatus => l_retStatus);
406 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
407 wip_logger.cleanup(l_retStatus);
408 end if;
409 end if;
410 when others then
411 rollback to wiptmpvb100;
412 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
413 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
414 p_procedure_name => l_procName,
415 p_error_text => SQLERRM);
416 wip_utilities.get_message_stack(p_msg => x_errorMsg,
417 p_delete_stack => fnd_api.g_false);
418 if(l_logLevel <= wip_constants.trace_logging) then
419 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
420 p_procReturnStatus => x_returnStatus,
421 p_msg => 'unexp error:' || x_errorMsg,
422 x_returnStatus => l_retStatus);
423 if(fnd_api.to_boolean(nvl(p_endDebug, fnd_api.g_false))) then
424 wip_logger.cleanup(l_retStatus);
425 end if;
426 end if;
427 end processWIP;
428
429 procedure validateInterfaceTxns(p_txnHdrID in NUMBER,
430 p_initMsgList in VARCHAR2 := null,
431 p_endDebug in VARCHAR2 := null,
432 p_numRows IN NUMBER := null,
433 p_addMsgToStack IN VARCHAR2 := null,
434 p_rollbackOnErr IN VARCHAR2 := null,
435 x_returnStatus out nocopy VARCHAR2) is
436 l_retCode NUMBER;
437 l_numIntRows NUMBER := p_numRows;
438 l_numTempRows NUMBER;
439 l_numErrRows NUMBER;
440 l_msgCount NUMBER;
441 l_msgData VARCHAR2(2000);
442 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
443 l_params wip_logger.param_tbl_t;
444 type err_tbl_t is table of varchar2(240);
445 type item_tbl_t is table of varchar2(2000);
446 l_errExplTbl err_tbl_t;
447 l_itemNameTbl item_tbl_t;
448 l_procName VARCHAR2(30) := 'validateInterfaceTxns';
449 l_endDebug VARCHAR2(1) := nvl(p_endDebug, fnd_api.g_false);
450 l_retStatus VARCHAR2(1);
451 begin
452 savepoint wiptmpvb200;
453 x_returnStatus := fnd_api.g_ret_sts_success;
454
455 if (l_logLevel <= wip_constants.trace_logging) then
456 l_params(1).paramName := 'p_txnHdrID';
457 l_params(1).paramValue := p_txnHdrID;
458
459 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
460 p_params => l_params,
461 x_returnStatus => x_returnStatus);
462 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
463 raise fnd_api.g_exc_unexpected_error;
464 end if;
465 end if;
466
467 if(fnd_api.to_boolean(nvl(p_initMsgList, fnd_api.g_false))) then
468 fnd_msg_pub.initialize;
469 end if;
470
471 if(l_numIntRows is null) then
472 select count(*)
473 into l_numIntRows
474 from mtl_transactions_interface
475 where transaction_header_id = p_txnHdrID
476 and process_flag = wip_constants.mti_inventory;
477 end if;
478
479 if(l_numIntRows = 0) then
480 if(l_logLevel <= wip_constants.trace_logging) then
481 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
482 p_procReturnStatus => x_returnStatus,
483 p_msg => 'no rows to backflush',
484 x_returnStatus => l_retStatus);
485 if(fnd_api.to_boolean(l_endDebug)) then
486 wip_logger.cleanup(l_retStatus);
487 end if;
488 end if;
489 return;
490 end if;
491
492 l_retCode := inv_txn_manager_grp.validate_transactions(p_api_version => 1.0,
493 p_init_msg_list => nvl(p_initMsgList, fnd_api.g_false),
494 p_validation_level => fnd_api.g_valid_level_none,
495 p_header_id => p_txnHdrID,
496 x_trans_count => l_numTempRows,
497 x_return_status => x_returnStatus,
498 x_msg_count => l_msgCount,
499 x_msg_data => l_msgData);
500
501 --x_trans_count out param is not supported yet
502 select count(*)
503 into l_numErrRows
504 from mtl_transactions_interface
505 where transaction_header_id = p_txnHdrID;
506
507 select count(*)
508 into l_numTempRows
509 from mtl_material_transactions_temp
510 where transaction_header_id = p_txnHdrID;
511
512 --after inv supports x_trans_count, change if below
513 -- if(l_numTempRows = l_numIntRows) then
514 if(l_numErrRows = 0) then
515 if(fnd_api.to_boolean(nvl(p_initMsgList, fnd_api.g_false))) then
516 fnd_msg_pub.initialize;
517 end if;
518 if(l_logLevel <= wip_constants.trace_logging) then
519 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
520 p_procReturnStatus => x_returnStatus,
521 p_msg => 'normal completion. ' || l_numTempRows || ' rows processed.',
522 x_returnStatus => l_retStatus);
523 if(fnd_api.to_boolean(l_endDebug)) then
524 wip_logger.cleanup(l_retStatus);
525 end if;
526 end if;
527 return;
528 end if;
529
530 --if any rows errored...
531 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
532
533 if(fnd_api.to_boolean(nvl(p_addMsgToStack, fnd_api.g_true))) then
534 --re-initialize message stack
535 if(fnd_api.to_boolean(nvl(p_initMsgList, fnd_api.g_false))) then
536 fnd_msg_pub.initialize;
537 end if;
538
539 select msik.concatenated_segments,
540 mti.error_explanation
541 bulk collect into l_itemNameTbl,
542 l_errExplTbl
543 from mtl_transactions_interface mti,
544 mtl_system_items_kfv msik
545 where mti.transaction_header_id = p_txnHdrID
546 and mti.error_explanation is not null
547 and mti.inventory_item_id = msik.inventory_item_id
548 and mti.organization_id = msik.organization_id;
549
550 for i in 1..l_itemNameTbl.count loop
551 fnd_message.set_name('WIP', 'WIP_TMPINSERT_ERR');
552 fnd_message.set_token('ITEM_NAME', l_itemNameTbl(i));
553 fnd_message.set_token('ERR_MSG', l_errExplTbl(i));
554 if(l_logLevel <= wip_constants.trace_logging) then
555 wip_logger.log('item ' || l_itemNameTbl(i) || ': ' || l_errExplTbl(i), l_retStatus);
556 end if;
557 fnd_msg_pub.add;
558 end loop;
559 end if;
560
561 if(fnd_api.to_boolean(nvl(p_rollBackOnErr, fnd_api.g_true))) then
562 rollback to wiptmpvb200;
563 end if;
564
565 if(l_logLevel <= wip_constants.trace_logging) then
566 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
567 p_procReturnStatus => x_returnStatus,
568 p_msg => to_char(l_numIntRows - l_numTempRows) || ' records errored',
569 x_returnStatus => l_retStatus);
570 if(fnd_api.to_boolean(l_endDebug)) then
571 wip_logger.cleanup(l_retStatus);
572 end if;
573 end if;
574
575 exception
576 when fnd_api.g_exc_unexpected_error then
577 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
578 if(fnd_api.to_boolean(nvl(p_rollBackOnErr, fnd_api.g_true))) then
579 rollback to wiptmpvb200;
580 else --caller wants error in MTI
581 wip_utilities.get_message_stack(p_msg => l_msgData,
582 p_delete_stack => fnd_api.g_false);
583
584 update mtl_transactions_interface
585 set last_update_date = sysdate,
586 last_update_login = fnd_global.login_id,
587 program_application_id = fnd_global.prog_appl_id,
588 program_id = fnd_global.conc_program_id,
589 program_update_date = sysdate,
590 request_id = fnd_global.conc_request_id,
591 process_flag = wip_constants.mti_error,
592 error_code = substrb(g_pkgName || '.' || l_procName, 1, 240),
593 error_explanation = substrb(l_msgData, 1, 240)
594 where transaction_header_id = p_txnHdrID;
595 end if;
596
597 if(l_msgData is not null) then
598 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
599 fnd_message.set_token('MESSAGE', l_msgData);
600 fnd_msg_pub.add;
601 end if;
602 if(l_logLevel <= wip_constants.trace_logging) then
603 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
604 p_procReturnStatus => x_returnStatus,
605 p_msg => 'error:' || l_msgData,
606 x_returnStatus => l_retStatus);
607 if(fnd_api.to_boolean(l_endDebug)) then
608 wip_logger.cleanup(l_retStatus);
609 end if;
610 end if;
611 when others then
612 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
613 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
614 p_procedure_name => l_procName,
615 p_error_text => SQLERRM);
616
617 if(fnd_api.to_boolean(nvl(p_rollBackOnErr, fnd_api.g_true))) then
618 rollback to wiptmpvb200;
619 else
620 wip_utilities.get_message_stack(p_msg => l_msgData,
621 p_delete_stack => fnd_api.g_false);
622
623 update mtl_transactions_interface
624 set last_update_date = sysdate,
625 last_update_login = fnd_global.login_id,
626 program_application_id = fnd_global.prog_appl_id,
627 program_id = fnd_global.conc_program_id,
628 program_update_date = sysdate,
629 request_id = fnd_global.conc_request_id,
630 process_flag = wip_constants.mti_error,
631 error_code = substrb(g_pkgName || '.' || l_procName, 1, 240),
632 error_explanation = substrb(l_msgData, 1, 240)
633 where transaction_header_id = p_txnHdrID;
634 end if;
635
636 if(l_logLevel <= wip_constants.trace_logging) then
637 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
638 p_procReturnStatus => x_returnStatus,
639 p_msg => 'unexp error:' || l_msgData,
640 x_returnStatus => l_retStatus);
641 if(fnd_api.to_boolean(l_endDebug)) then
642 wip_logger.cleanup(l_retStatus);
643 end if;
644 end if;
645 end validateInterfaceTxns;
646
647 end wip_mtlTempProc_priv;