[Home] [Help]
PACKAGE BODY: APPS.WIP_MTI_PUB
Source
1 package body wip_mti_pub as
2 /* $Header: wipmtivb.pls 120.11.12020000.5 2013/02/22 17:46:20 sjallipa ship $ */
3
4 type num_tbl_t is table of number;
5 type date_tbl_t is table of date;
6 type char_tbl_t is table of varchar2(3);
7 type rowid_tbl_t is table of varchar2(18);
8 type big_char_tbl_t is table of varchar2(2000);
9
10 type mti_recTbl_t is record(wipEntityID num_tbl_t,
11 orgID num_tbl_t,
12 itemID num_tbl_t,
13 txnQty num_tbl_t,
14 priQty num_tbl_t,
15 txnDate date_tbl_t,
16 txnTypeID num_tbl_t,
17 txnActionID num_tbl_t,
18 txnIntID num_tbl_t,
19 txnBatchID num_tbl_t,
20 txnSeqNum num_tbl_t,
21 repLineID num_tbl_t,
22 cplTxnID num_tbl_t,
23 overCplTxnID num_tbl_t,
24 overCplTxnQty num_tbl_t,
25 overCplPriQty num_tbl_t,
26 movTxnID num_tbl_t,
27 wipEntityType num_tbl_t,
28 txnUom char_tbl_t,
29 priUom char_tbl_t,
30 locatorID num_tbl_t,
31 projectID num_tbl_t,
32 taskID num_tbl_t,
33 reasonID num_tbl_t,
34 reference big_char_tbl_t);
35
36 type mti_err_recTbl_t is record(txnIntID num_tbl_t,
37 errCode big_char_tbl_t,
38 errExpl big_char_tbl_t);
39
40 /*Bug 9758848*/
41 type mti_sf_recTbl_t is record(txnIntID num_tbl_t,
42 orgID num_tbl_t,
43 txnSrcID num_tbl_t,
44 lineID num_tbl_t,
45 opSeqNum num_tbl_t);
46
47 procedure preInvProcessFlow(p_txnHeaderID in number,
48 x_returnStatus out nocopy varchar2);
49
50
51
52 function extendErrTbls(p_errTbl in out nocopy mti_err_recTbl_t) return number;
53
54 procedure doPreProcessingValidations(p_txnHeaderID in number,
55 x_returnStatus out nocopy varchar2);
56
57 procedure preInvProcessWorkOrder(p_txnHeaderID in number,
58 p_tbls in mti_recTbl_t,
59 p_index in number,
60 p_errTbls in out nocopy mti_err_recTbl_t,
61 x_returnStatus OUT NOCOPY VARCHAR2);
62
63 --This procedure should eventually do component explosion for work order-less
64 --as well as component backflushing for discrete
65 procedure preInvWIPProcessing(p_txnHeaderID in number,
66 x_returnStatus out nocopy varchar2) is
67 cursor c_assyRecs is
68 select mti.transaction_source_id,
69 mti.organization_id,
70 mti.inventory_item_id,
71 mti.transaction_quantity,
72 mti.primary_quantity,
73 mti.transaction_date,
74 mti.transaction_type_id,
75 mti.transaction_action_id,
76 mti.transaction_interface_id,
77 mti.transaction_batch_id,
78 mti.transaction_batch_seq,
79 mti.repetitive_line_id,
80 mti.completion_transaction_id,
81 mti.overcompletion_transaction_id,
82 mti.overcompletion_transaction_qty,
83 mti.overcompletion_primary_qty,
84 mti.move_transaction_id,
85 decode(upper(mti.flow_schedule),
86 'Y', wip_constants.flow,
87 we.entity_type),
88 mti.transaction_uom,
89 msi.primary_uom_code,
90 mti.locator_id,
91 mti.project_id,
92 mti.task_id,
93 mti.reason_id,
94 mti.transaction_reference
95 from mtl_transactions_interface mti,
96 wip_entities we,
97 mtl_system_items msi
98 where mti.transaction_header_id = p_txnHeaderID
99 and mti.transaction_source_type_id = 5
100 and mti.transaction_action_id in (wip_constants.cplassy_action,
101 wip_constants.retassy_action,
102 wip_constants.scrassy_action)
103 and mti.transaction_source_id = we.wip_entity_id (+)
104 and mti.organization_id = we.organization_id (+)
105 and mti.inventory_item_id = msi.inventory_item_id
106 and mti.organization_id = msi.organization_id;
107
108 l_tbls mti_recTbl_t;
109 l_errTbls mti_err_recTbl_t;
110 l_count NUMBER;
111 l_returnStatus VARCHAR2(1);
112 l_params wip_logger.param_tbl_t;
113 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
114 l_errExp VARCHAR2(240);
115 l_convPriQty NUMBER;
116 l_convOverCplPriQty NUMBER;
117 l_convErrExists boolean;
118 begin
119 if (l_logLevel <= wip_constants.trace_logging) then
120 l_params(1).paramName := 'p_txnHeaderID';
121 l_params(1).paramValue := p_txnHeaderID;
122 wip_logger.entryPoint(p_procName => 'wip_mti_pub.preInvWipProcessing',
123 p_params => l_params,
124 x_returnStatus => x_returnStatus);
125 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
126 raise fnd_api.g_exc_unexpected_error;
127 end if;
128 end if;
129
130 --This procedure must be done before opening the cursor!
131 doPreProcessingValidations(p_txnHeaderID,
132 x_returnStatus);
133 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
134 raise fnd_api.g_exc_unexpected_error;
135 end if;
136
137 open c_assyRecs;
138 fetch c_assyRecs
139 bulk collect into l_tbls.wipEntityID,
140 l_tbls.orgID,
141 l_tbls.itemID,
142 l_tbls.txnQty,
143 l_tbls.priQty,
144 l_tbls.txnDate,
145 l_tbls.txnTypeID,
146 l_tbls.txnActionID,
147 l_tbls.txnIntID,
148 l_tbls.txnBatchID,
149 l_tbls.txnSeqNum,
150 l_tbls.repLineID,
151 l_tbls.cplTxnID,
152 l_tbls.overCplTxnID,
153 l_tbls.overCplTxnQty,
154 l_tbls.overCplPriQty,
155 l_tbls.movTxnID,
156 l_tbls.wipEntityType,
157 l_tbls.txnUom,
158 l_tbls.priUom,
159 l_tbls.locatorID,
160 l_tbls.projectID,
161 l_tbls.taskID,
162 l_tbls.reasonID,
163 l_tbls.reference;
164 close c_assyRecs;
165
166 for i in 1..l_tbls.wipEntityID.count loop
167 --derive primary quantity if necessary. This is a line level derivation
168 --and thus is not done in doPreProcessingValidations()
169 -- Bug 5411401. passing from_qty as absolute for UOM convert in below calls
170 --saugupta 31-July-06
171 l_convPriQty:= inv_convert.inv_um_convert(item_id => l_tbls.itemID(i),
172 precision => null,
173 from_quantity => abs(l_tbls.txnQty(i)),
174 from_unit => l_tbls.txnUOM(i),
175 to_unit => l_tbls.priUOM(i),
176 from_name => null,
177 to_name => null);
178
179 if(l_tbls.overCplTxnQty(i) is not null) then
180 l_convOverCplPriQty := inv_convert.inv_um_convert(item_id => l_tbls.itemID(i),
181 precision => null,
182 from_quantity => abs(l_tbls.overCplTxnQty(i)),
183 from_unit => l_tbls.txnUOM(i),
184 to_unit => l_tbls.priUOM(i),
185 from_name => null,
186 to_name => null);
187 end if;
188
189 -- -99999 is weird inv uom error code.
190 l_convErrExists := l_convPriQty = -99999 or nvl(l_convOverCplPriQty,0) = -99999;
191
192 /* Fix for Bug 5411401 */
193 if (l_tbls.txnQty(i) < 0 and l_convPriQty >= 0) then
194 l_convPriQty := -1 * l_convPriQty;
195 end if;
196
197 if (l_tbls.overCplTxnQty(i) is not null
198 and l_tbls.overCplTxnQty(i) < 0
199 and nvl(l_convOverCplPriQty,0) >= 0) then
200 l_convOverCplPriQty := -1 * l_convOverCplPriQty ;
201 end if;
202 /* End of fix for Bug 5411401 */
203
204 if(l_convErrExists) then
205 if(l_logLevel <= wip_constants.full_logging) then
206 wip_logger.log('uom conversion failed.', l_returnStatus);
207 wip_logger.log('l_convPriQty' || l_convPriQty, l_returnStatus);
208 wip_logger.log('l_convOverCplPriQty' || l_convOverCplPriQty, l_returnStatus);
209 end if;
210 fnd_message.set_name('INV', 'INV_INT_UOMEXP');
211 l_errExp := fnd_message.get;
212 update mtl_transactions_interface
213 set last_update_date = sysdate,
214 last_updated_by = fnd_global.user_id,
215 last_update_login = fnd_global.login_id,
216 program_application_id = fnd_global.prog_appl_id,
217 program_id = fnd_global.conc_program_id,
218 program_update_date = sysdate,
219 request_id = fnd_global.conc_request_id,
220 process_flag = 3,
221 lock_flag = 2,
222 error_code = 'wip_mti_pub.preInvWIPProcessing',
223 error_explanation = l_errExp
224 where transaction_interface_id = l_tbls.txnIntID(i);
225
226 elsif(l_convPriQty <> l_tbls.priQty(i) or l_tbls.priQty(i) is null or
227 (l_convOverCplPriQty is not null and
228 (l_tbls.overCplPriQty(i) is null or l_tbls.overCplPriQty(i) <> l_convOverCplPriQty))) then
229 l_tbls.priQty(i) := l_convPriQty;
230 l_tbls.overCplPriQty(i) := l_convOverCplPriQty;
231
232 update mtl_transactions_interface
233 set last_update_date = sysdate,
234 last_updated_by = fnd_global.user_id,
235 last_update_login = fnd_global.login_id,
236 program_application_id = fnd_global.prog_appl_id,
237 program_id = fnd_global.conc_program_id,
238 program_update_date = sysdate,
239 request_id = fnd_global.conc_request_id,
240 primary_quantity = l_tbls.priQty(i),
241 overcompletion_primary_qty = l_tbls.overCplPriQty(i)
242 where transaction_interface_id = l_tbls.txnIntID(i);
243 if(l_logLevel <= wip_constants.full_logging) then
244 wip_logger.log('pri qty:' || l_tbls.priQty(i), l_returnStatus);
245 wip_logger.log('ovcpl pri qty:' || l_tbls.overCplPriQty(i), l_returnStatus);
246 end if;
247 end if;
248
249 if(l_tbls.wipEntityType(i) <> wip_constants.flow and not l_convErrExists) then
250 if(l_logLevel <= wip_constants.full_logging) then
251 wip_logger.log('calling work order row-by-row processor', l_returnStatus);
252 end if;
253 preInvProcessWorkOrder(p_txnHeaderID => p_txnHeaderID,
254 p_tbls => l_tbls,
255 p_index => i,
256 p_errTbls => l_errTbls,
257 x_returnStatus => l_returnStatus);
258 end if;
259 end loop;
260
261 if ( l_errTbls.txnIntID is not null ) then
262 if(l_logLevel <= wip_constants.full_logging) then
263 wip_logger.log(l_errTbls.txnIntID.count || ' records failed work order pre-processing', l_returnStatus);
264 for i in 1..l_errTbls.txnIntID.count loop
265 wip_logger.log('txnIntID: ' || l_errTbls.txnIntID(i), l_returnStatus);
266 wip_logger.log(' errCode: ' || l_errTbls.errCode(i), l_returnStatus);
267 wip_logger.log(' errExpl: ' || l_errTbls.errExpl(i), l_returnStatus);
268 end loop;
269 end if;
270
271 forall i in 1..l_errTbls.txnIntID.count
272 update mtl_transactions_interface
273 set last_update_date = sysdate,
274 last_updated_by = fnd_global.user_id,
275 last_update_login = fnd_global.login_id,
276 program_application_id = fnd_global.prog_appl_id,
277 program_id = fnd_global.conc_program_id,
278 program_update_date = sysdate,
279 request_id = fnd_global.conc_request_id,
280 process_flag = wip_constants.mti_error,
281 error_code = l_errTbls.errCode(i),
282 error_explanation = l_errTbls.errExpl(i),
283 lock_flag = 2 --unlock the record so it can be re-submitted
284 where transaction_interface_id = l_errTbls.txnIntID(i);
285 end if;
286
287 if(l_logLevel <= wip_constants.full_logging) then
288 wip_logger.log('calling flow group processor', l_returnStatus);
289 end if;
290
291 preInvProcessFlow(p_txnHeaderID => p_txnHeaderID,
292 x_returnStatus => l_returnStatus);
293
294
295 if (l_logLevel <= wip_constants.trace_logging) then
296 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvWIPProcessing',
297 p_procReturnStatus => x_returnStatus,
298 p_msg => 'success',
299 x_returnStatus => l_returnStatus);
300 wip_logger.cleanup(l_returnStatus);
301 end if;
302 exception
303 when others then
304 rollback to wipmtivb1;
305 l_errExp := substrb(SQLERRM, 1, 240);
306 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
307
308 update mtl_transactions_interface
309 set last_update_date = sysdate,
310 last_updated_by = fnd_global.user_id,
311 last_update_login = fnd_global.login_id,
312 program_application_id = fnd_global.prog_appl_id,
313 program_id = fnd_global.conc_program_id,
314 program_update_date = sysdate,
315 request_id = fnd_global.conc_request_id,
316 process_flag = 3,
317 lock_flag = 2,
318 error_code = 'wip_mti_pub.preInvWIPProcessing',
319 error_explanation = l_errExp
320 where transaction_header_id = p_txnHeaderID
321 and transaction_source_type_id = 5
322 and process_flag = wip_constants.mti_inventory;
323
324 if(l_logLevel <= wip_constants.trace_logging) then
325 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvWIPProcessing',
326 p_procReturnStatus => x_returnStatus,
327 p_msg => 'unexpected error: ' || SQLERRM,
328 x_returnStatus => l_returnStatus); --discard logging return status
329 wip_logger.cleanup(l_returnStatus);
330 end if;
331 end preInvWIPProcessing;
332
333 procedure preInvProcessWorkOrder(p_txnHeaderID in number,
334 p_tbls in mti_recTbl_t,
335 p_index in number,
336 p_errTbls in out nocopy mti_err_recTbl_t,
337 x_returnStatus OUT NOCOPY VARCHAR2) is
338 l_txnType NUMBER;
339 l_bfRequired NUMBER;
340 l_lsRequired NUMBER;
341 l_msg VARCHAR2(2000);
342 l_errCount NUMBER;
343 l_count NUMBER;
344 l_params wip_logger.param_tbl_t;
345 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
346 l_returnStatus VARCHAR2(1);
347 begin
348 savepoint wipmtivb10;
349
350 if(l_logLevel <= wip_constants.trace_logging) then
351 l_params(1).paramName := 'p_txnHeaderID';
352 l_params(1).paramValue := p_txnHeaderID;
353 l_params(2).paramName := 'p_index';
354 l_params(2).paramValue := p_index;
355
356 wip_logger.entryPoint(p_procName => 'wip_mti_pub.preInvProcessWorkOrder',
357 p_params => l_params,
358 x_returnStatus => x_returnStatus);
359 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
360 raise fnd_api.g_exc_unexpected_error;
361 end if;
362 end if;
363
364 x_returnStatus := fnd_api.g_ret_sts_success;
365
366 if(p_tbls.txnActionID(p_index) = wip_constants.cplassy_action) then
367 l_txnType := wip_constants.comp_txn;
368 elsif(p_tbls.txnActionID(p_index) = wip_constants.retassy_action) then
369 l_txnType := wip_constants.ret_txn;
370 else --scrap. don't do anything
371 if (l_logLevel <= wip_constants.trace_logging) then
372 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvWIPProcessing',
373 p_procReturnStatus => x_returnStatus,
374 p_msg => 'success (scrap txn)',
375 x_returnStatus => l_returnStatus);
376 end if;
377 return;
378 end if;
379
380 --if repetitive cpl, check allocation table to see if schedule has already been pre-processed
381 if(p_tbls.repLineID(p_index) is not null) then
382 if (l_logLevel <= wip_constants.full_logging) then
383 wip_logger.log('doing repetitive processing', l_returnStatus);
384 end if;
385 select count(*)
386 into l_count
387 from wip_mtl_allocations_temp
388 where transaction_temp_id = p_tbls.txnIntID(p_index);
389
390 if(l_count = 0) then
391 if (l_logLevel <= wip_constants.full_logging) then
392 wip_logger.log('found unallocated schedule', l_returnStatus);
393 end if;
394 wip_cplProc_priv.preAllocateSchedules(p_txnHdrID => p_txnHeaderID,
395 p_cplTxnID => p_tbls.cplTxnID(p_index),
396 p_txnActionID => p_tbls.txnActionID(p_index),
397 p_wipEntityID => p_tbls.wipEntityID(p_index),
398 p_repLineID => p_tbls.repLineID(p_index),
399 p_tblName => wip_constants.MTI_TBL,
400 p_endDebug => fnd_api.g_false,
401 x_returnStatus => x_returnStatus);
402 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
403 raise fnd_api.g_exc_error;
404 end if;
405
406 end if;
407 end if;
408
409 select count(*)
410 into l_count
411 from mtl_transactions_interface
412 where transaction_header_id = p_txnHeaderID
413 and completion_transaction_id = p_tbls.cplTxnID(p_index)
414 and transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
415 wip_constants.issnegc_action, wip_constants.retnegc_action);
416 if(l_count = 0) then
417 if (l_logLevel <= wip_constants.full_logging) then
418 wip_logger.log('backflushing cpl', l_returnStatus);
419 end if;
420 wip_bflProc_priv.backflush(p_wipEntityID => p_tbls.wipEntityID(p_index),
421 p_orgID => p_tbls.orgID(p_index),
422 p_primaryQty => abs(p_tbls.priQty(p_index)),
423 p_txnDate => p_tbls.txnDate(p_index),
424 p_txnHdrID => p_txnHeaderID,
425 p_batchID => p_tbls.txnBatchID(p_index),
426 p_batchSeq => p_tbls.txnSeqNum(p_index) + 1,
427 p_txnType => l_txnType,
428 p_entityType => p_tbls.wipEntityType(p_index),
429 p_tblName => wip_constants.MTI_TBL,
430 p_lineID => p_tbls.repLineID(p_index),
431 p_ocQty => p_tbls.overCplPriQty(p_index),
432 p_childMovTxnID => p_tbls.movTxnID(p_index),
433 p_cplTxnID => p_tbls.cplTxnID(p_index),
434 p_mtlTxnMode => inv_txn_manager_grp.PROC_MODE_MTI,
435 p_lockFlag => wip_constants.yes,
436 p_reasonID => p_tbls.reasonID(p_index),
437 p_reference => p_tbls.reference(p_index),
438 x_bfRequired => l_bfRequired,
439 x_lotSerRequired => l_lsRequired,
440 x_returnStatus => x_returnStatus);
441
442 if (l_logLevel <= wip_constants.full_logging) then
443 wip_logger.log('bf required:' || l_bfRequired, l_returnStatus);
444 wip_logger.log('ls required:' || l_lsRequired, l_returnStatus);
445 end if;
446 --if the procedure fails or some lot/serial info for backflush components can not
447 --be derived then error
448 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
449 raise fnd_api.g_exc_error;
450 end if;
451 if(l_lsRequired = wip_constants.yes) then
452 fnd_message.set_name('WIP', 'WIP_NO_LOT_SER_COMP_BKGND');
453 fnd_msg_pub.add;
454 raise fnd_api.g_exc_error;
455 end if;
456
457 end if;
458 if (l_logLevel <= wip_constants.trace_logging) then
459 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvWIPProcessing',
460 p_procReturnStatus => x_returnStatus,
461 p_msg => 'success',
462 x_returnStatus => l_returnStatus);
463 end if;
464 exception
465 when fnd_api.g_exc_error then
466 rollback to wipmtivb10;
467 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
468 l_errCount := extendErrTbls(p_errTbls);
469 p_errTbls.txnIntID(l_errCount) := p_tbls.txnIntID(p_index);
470 p_errTbls.errCode(l_errCount) := 'WIP_PREPROCESSING';
471 wip_utilities.get_message_stack(p_msg => l_msg);
472 if(l_logLevel <= wip_constants.trace_logging) then
473 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvWIPProcessing',
474 p_procReturnStatus => x_returnStatus,
475 p_msg => 'error:' || l_msg,
476 x_returnStatus => l_returnStatus);
477 end if;
478 p_errTbls.errExpl(l_errCount) := substrb(l_msg, 1, 240);
479 when others then
480 if(l_logLevel <= wip_constants.trace_logging) then
481 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvWIPProcessing',
482 p_procReturnStatus => x_returnStatus,
483 p_msg => 'unexp error:' || SQLERRM,
484 x_returnStatus => l_returnStatus);
485 end if;
486 rollback to wipmtivb10;
487 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
488 l_errCount := extendErrTbls(p_errTbls);
489 p_errTbls.txnIntID(l_errCount) := p_tbls.txnIntID(p_index);
490 p_errTbls.errCode(l_errCount) := 'WIP_PREPROCESSING';
491 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mti_pub',
492 p_procedure_name => 'preInvProcessWorkOrder',
493 p_error_text => SQLERRM);
494 wip_utilities.get_message_stack(p_msg => l_msg);
495 p_errTbls.errExpl(l_errCount) := substrb(l_msg, 1, 240);
496 end preInvProcessWorkOrder;
497
498 function extendErrTbls(p_errTbl in out nocopy mti_err_recTbl_t) return number is
499 begin
500 if(p_errTbl.txnIntID is null) then
501 p_errTbl.txnIntID := num_tbl_t();
502 p_errTbl.errCode := big_char_tbl_t();
503 p_errTbl.errExpl := big_char_tbl_t();
504 end if;
505 p_errTbl.txnIntID.extend(1);
506 p_errTbl.errCode.extend(1);
507 p_errTbl.errExpl.extend(1);
508 return p_errTbl.txnIntId.count;
509 end extendErrTbls;
510
511 /**
512 * This procedure do the general validation for the interface rows
513 * under the given header id.
514 */
515 procedure validateInterfaceRows(p_txnHeaderID in number,
516 x_returnStatus out nocopy varchar2) is
517 cursor cpl_c is
518 select organization_id,
519 transaction_source_id,
520 transaction_interface_id
521 from mtl_transactions_interface
522 where transaction_header_id = p_txnHeaderID
523 and process_flag = 1
524 and transaction_source_type_id = 5
525 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
526 WIP_CONSTANTS.CPLASSY_ACTION,
527 WIP_CONSTANTS.RETASSY_ACTION);
528 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
529 l_params wip_logger.param_tbl_t;
530 l_returnStatus varchar2(1);
531 l_msgCount number;
532 l_msgData varchar2(240);
533
534 begin
535 null;
536 end validateInterfaceRows;
537
538 /**
539 * Flow transaction is a bit different than others. It has substitution records. Those
540 * records can only be validated after we have routing information about the assembly to
541 * complete/return/scrap. Also, we can only validate material backflush record after the
542 * explosion. Originally, in inltev, inltvu and inltwv, we only validate the parent record.
543 * After doing the explosion, merge, etc, we assign the child record a new header id and
544 * validate those child records by calling inltev, inltvu and inltwv again. After that,
545 * we merge the parent and child records again under one header id.
546 * Instead of calling it twice, for flow transactions, we will do some
547 * minimum validation and then explode the bom, merge backflush records. Then we call
548 * INV validation and then call wip validation.
549 * This procedure is used to do some minimum validation, explode the BOM, validate/merge
550 * substitute records.
551 * After calling this, at least for flow, all the backflush records and parent records
552 * should be grouped nicely into one group to be validated by inv and other wip logic.
553 *
554 * *** we need to call inv procedure to convert name to id and also get the primary
555 * item id *****
556 */
557 procedure preInvProcessFlow(p_txnHeaderID in number,
558 x_returnStatus out nocopy varchar2) is
559
560
561 l_primaryUOM varchar2(3);
562 l_primaryQty number;
563
564 l_params wip_logger.param_tbl_t;
565 l_returnStatus varchar2(1);
566 l_errCode varchar2(240);
567 l_errMsg varchar2(240);
568 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
569 l_bigErrMsg VARCHAR2(2000);
570 begin
571
572 if (l_logLevel <= wip_constants.trace_logging) then
573 l_params(1).paramName := 'p_txnHeaderID';
574 l_params(1).paramValue := p_txnHeaderID;
575 wip_logger.entryPoint(p_procName => 'wip_mti_pub.preInvProcessFlow',
576 p_params => l_params,
577 x_returnStatus => x_returnStatus);
578 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
579 raise fnd_api.g_exc_unexpected_error;
580 end if;
581 end if;
582
583 -- do derivation and validation only for flow records
584 wip_flowUtil_priv.processFlowInterfaceRecords(p_txnHeaderID);
585
586 if (l_logLevel <= wip_constants.trace_logging) then
587 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvProcessFlow',
588 p_procReturnStatus => x_returnStatus,
589 p_msg => 'Finished pre inventory flow processing!',
590 x_returnStatus => l_returnStatus); --discard logging return status
591 end if;
592 exception
593 when others then
594 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
595 if (l_logLevel <= wip_constants.trace_logging) then
596 wip_logger.exitPoint(p_procName => 'wip_mti_pub.preInvProcessFlow',
597 p_procReturnStatus => x_returnStatus,
598 p_msg => 'unexpected error: ' || SQLERRM,
599 x_returnStatus => l_returnStatus); --discard logging return status
600 end if;
601 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mti_pub',
602 p_procedure_name => 'preInvProcessFlow',
603 p_error_text => SQLERRM);
604 wip_utilities.get_message_stack(p_msg => l_bigErrMsg);
605 l_errMsg := substrb(l_bigErrMsg, 1, 240);
606 end preInvProcessFlow;
607
608
609 /**
610 * check whether locator is under project mfg. constraint. validate it
611 * for the parent record. We don''t do that for scrap.
612 */
613 procedure validateLocatorForProject(p_txnHeaderID in number) is
614 cursor pt_c is
615 select transaction_interface_id,
616 organization_id,
617 inventory_item_id,
618 subinventory_code,
619 transaction_source_id,
620 locator_id,
621 source_project_id,
622 source_task_id,
623 nvl(flow_schedule, 'N') flow_schedule,
624 scheduled_flag,
625 transaction_action_id
626 from mtl_transactions_interface
627 where transaction_header_id = p_txnHeaderID
628 and process_flag = 1
629 and transaction_source_type_id = 5
630 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
631 WIP_CONSTANTS.RETASSY_ACTION,
632 WIP_CONSTANTS.ISSCOMP_ACTION,
633 WIP_CONSTANTS.RETCOMP_ACTION,
634 WIP_CONSTANTS.ISSNEGC_ACTION,
635 WIP_CONSTANTS.RETNEGC_ACTION);
636 l_locatorCntlCode number;
637 l_projRefEnabled number;
638 l_projID number;
639 l_taskID number;
640
641 l_success boolean;
642 begin
643 for pt_rec in pt_c loop
644 -- check for item locator control type
645 select decode(p.stock_locator_control_code,
646 4, decode(s.locator_type,
647 5, i.location_control_code,
648 s.locator_type),
649 p.stock_locator_control_code),
650 nvl(project_reference_enabled, 2)
651 into l_locatorCntlCode,
652 l_projRefEnabled
653 from mtl_parameters p,
654 mtl_secondary_inventories s,
655 mtl_system_items i
656 where i.inventory_item_id = pt_rec.inventory_item_id
657 and i.organization_id = pt_rec.organization_id
658 and s.secondary_inventory_name = pt_rec.subinventory_code
659 and s.organization_id = pt_rec.organization_id
660 and p.organization_id = pt_rec.organization_id;
661 -- Bug 15834372
662 -- if ( l_locatorCntlCode <> 1 and l_projRefEnabled = 1 ) then
663 if ( l_projRefEnabled = 1 ) then
664
665 if(l_locatorCntlCode = 1 or pt_rec.locator_id is null) then
666 if (pt_rec.transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
667 WIP_CONSTANTS.RETASSY_ACTION)) then
668 l_success := FALSE;
669 end if;
670
671 else
672 if ( upper(pt_rec.flow_schedule) = 'Y' ) then
673 if ( pt_rec.scheduled_flag = 1 ) then
674 select project_id, task_id
675 into l_projID, l_taskID
676 from wip_flow_schedules
677 where wip_entity_id = pt_rec.transaction_source_id;
678 else
679 l_projID := pt_rec.source_project_id;
680 l_taskID := pt_rec.source_task_id;
681 end if; -- end of scheduled_flag = 1
682 else
683 select project_id, task_id
684 into l_projID, l_taskID
685 from wip_discrete_jobs
686 where wip_entity_id = pt_rec.transaction_source_id;
687 end if; -- end of flow_schedule = 'Y'
688
689 if (pt_rec.transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
690 WIP_CONSTANTS.RETASSY_ACTION)) then
691
692 l_success := pjm_project_locator.check_project_references(
693 p_organization_id => pt_rec.organization_id,
694 p_locator_id => pt_rec.locator_id,
695 p_validation_mode => 'SPECIFIC',
696 p_required_flag => 'Y',
697 p_project_id => l_projID,
698 p_task_id => l_taskID);
699
700 else
701
702 -- material transaction
703 l_success := pjm_project_locator.check_project_references(
704 p_organization_id => pt_rec.organization_id,
705 p_locator_id => pt_rec.locator_id,
706 p_validation_mode => 'SPECIFIC',
707 p_required_flag => 'N',
708 p_project_id => l_projID,
709 p_task_id => l_taskID);
710
711 end if;
712
713 end if;
714
715
716 if ( not l_success ) then
717 fnd_message.set_name('INV', 'INV_INT_LOCSEGCODE');
718 setMtiError(pt_rec.transaction_interface_id,
719 'locator_id',
720 fnd_message.get);
721 end if;
722
723 end if;
724
725 end loop;
726
727 end validateLocatorForProject;
728
729
730 /**
731 * This should be called after calling the inventory validation logic. It
732 * does the wip specific validation.
733 */
734 procedure postInvWIPValidation(p_txnHeaderID in number,
735 x_returnStatus out nocopy varchar2) is
736
737 /*Bug 5708242 - Cursor and variables no longer needed*/
738 /*Replacing back the variables and cursor for bug 7300614(FP 7281109) so that validation can be called*/
739 cursor nonCfm_c is
740 select transaction_interface_id,
741 organization_id,
742 inventory_item_id,
743 transaction_quantity,
744 transaction_uom
745 from mtl_transactions_interface
746 where transaction_header_id = p_txnHeaderID
747 and process_flag = 1
748 and transaction_source_type_id = 5
749 and upper(nvl(flow_schedule, 'N')) = 'N'
750 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
751 WIP_CONSTANTS.CPLASSY_ACTION,
752 WIP_CONSTANTS.RETASSY_ACTION);
753
754 l_primaryCostMethod number;
755 l_cstRetVal number;
756
757 l_priUOM varchar2(3);
758 l_priQty number;
759 l_errNum number;
760 l_errCode varchar2(240);
761 l_poExpToAssetTnsf number;
762 l_params wip_logger.param_tbl_t;
763 l_returnStatus varchar2(1);
764 l_errMsg varchar2(240);
765 l_engItemFlag number := 2;
766 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
767 l_sfRecTbl mti_sf_recTbl_t; /*Fix Bug 9758848*/
768 l_sf_status varchar2(100); /*Fix Bug 9758848*/
769
770 begin
771
772 if (l_logLevel <= wip_constants.trace_logging) then
773 l_params(1).paramName := 'p_txnHeaderID';
774 l_params(1).paramValue := p_txnHeaderID;
775 wip_logger.entryPoint(p_procName => 'wip_mti_pub.postInvWIPValidation.',
776 p_params => l_params,
777 x_returnStatus => x_returnStatus);
778 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
779 raise fnd_api.g_exc_unexpected_error;
780 end if;
781 end if;
782
783
784 -- check whether locator is under project mfg. constraint. validate it
785 -- for the parent record. We don't do that for scrap
786 validateLocatorForProject(p_txnHeaderID);
787
788 -- check if it is an assembly completion/return for lot based job. If so error out
789 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
790 fnd_message.set_token('ENTITY', 'Transactions');
791 l_errMsg := substrb(fnd_message.get, 1, 240);
792 update mtl_transactions_interface mti
793 set last_update_date = sysdate,
794 last_updated_by = fnd_global.user_id,
795 last_update_login = fnd_global.login_id,
796 program_application_id = fnd_global.prog_appl_id,
797 program_id = fnd_global.conc_program_id,
798 program_update_date = sysdate,
799 request_id = fnd_global.conc_request_id,
800 process_flag = 3,
801 lock_flag = 2,
802 error_code = 'Transaction',
803 error_explanation = l_errMsg
804 where transaction_header_id = p_txnHeaderID
805 and process_flag = 1
806 and transaction_source_type_id = 5
807 and transaction_type_id in (44, 17)
808 and wip_entity_type = 5;
809
810 -- check existence of line for repetitive schedules, job and flow schedule
811 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
812 fnd_message.set_token('ENTITY', 'line');
813 l_errMsg := substrb(fnd_message.get, 1, 240);
814 update mtl_transactions_interface mti
815 set last_update_date = sysdate,
816 last_updated_by = fnd_global.user_id,
817 last_update_login = fnd_global.login_id,
818 program_application_id = fnd_global.prog_appl_id,
819 program_id = fnd_global.conc_program_id,
820 program_update_date = sysdate,
821 request_id = fnd_global.conc_request_id,
822 process_flag = 3,
823 lock_flag = 2,
824 error_code = 'repetitive_line_id',
825 error_explanation = l_errMsg
826 where transaction_header_id = p_txnHeaderID
827 and transaction_source_type_id = 5
828 and process_flag = 1
829 and ( ( wip_entity_type = 2
830 and not exists(select 'X'
831 from wip_lines wl
832 where wl.line_id = mti.repetitive_line_id
833 and wl.organization_id = mti.organization_id))
834 or( wip_entity_type in (1, 4, 5)
835 and repetitive_line_id is not null
836 and not exists(select 'X'
837 from wip_lines wl
838 where wl.line_id = mti.repetitive_line_id
839 and wl.organization_id = mti.organization_id)));
840
841 -- check valid line for assembly
842 fnd_message.set_name('WIP', 'WIP_INVALID_LINE');
843 l_errMsg := substrb(fnd_message.get, 1, 240);
844 update mtl_transactions_interface mti
845 set last_update_date = sysdate,
846 last_updated_by = fnd_global.user_id,
847 last_update_login = fnd_global.login_id,
848 program_application_id = fnd_global.prog_appl_id,
849 program_id = fnd_global.conc_program_id,
850 program_update_date = sysdate,
851 request_id = fnd_global.conc_request_id,
852 process_flag = 3,
853 lock_flag = 2,
854 error_code = 'repetitive_line_id',
855 error_explanation = l_errMsg
856 where transaction_header_id = p_txnHeaderID
857 and process_flag = 1
858 and transaction_source_type_id = 5
859 and wip_entity_type = 2
860 and not exists (select 'X'
861 from wip_repetitive_items wri
862 where wri.wip_entity_id = mti.transaction_source_id
863 and wri.line_id = mti.repetitive_line_id
864 and wri.organization_id = mti.organization_id);
865
866
867 -- check that job/schedule, etc. is transactable, flow is checked before
868 fnd_message.set_name('WIP', 'WIP_NO_CHARGES_ALLOWED');
869 l_errMsg := substrb(fnd_message.get, 1, 240);
870 update mtl_transactions_interface mti
871 set last_update_date = sysdate,
872 last_updated_by = fnd_global.user_id,
873 last_update_login = fnd_global.login_id,
874 program_application_id = fnd_global.prog_appl_id,
875 program_id = fnd_global.conc_program_id,
876 program_update_date = sysdate,
877 request_id = fnd_global.conc_request_id,
878 process_flag = 3,
879 lock_flag = 2,
880 error_code = 'transaction_source_id',
881 error_explanation = l_errMsg
882 where transaction_header_id = p_txnHeaderID
883 and process_flag = 1
884 and transaction_source_type_id = 5
885 and ( ( mti.wip_entity_type in (1, 5, 6)
886 and not exists (select 'X'
887 from wip_discrete_jobs wdj
888 where wdj.wip_entity_id = mti.transaction_source_id
889 and wdj.organization_id = mti.organization_id
890 and wdj.status_type in (3,4)))
891 or ( mti.wip_entity_type = 2
892 and not exists (select 'X'
893 from wip_repetitive_schedules wrs
894 where wrs.wip_entity_id = mti.transaction_source_id
895 and wrs.organization_id = mti.organization_id
896 and wrs.line_id = mti.repetitive_line_id
897 and wrs.status_type in (3,4)))
898 or mti.wip_entity_type not in (1, 2, 4, 5, 6));
899
900
901 -- check to see if job/flow has an assembly associated with it
902 -- validate this only for completion transactions
903 fnd_message.set_name('WIP', 'WIP_NO_ASSY_NO_TXN');
904 l_errMsg := substrb(fnd_message.get, 1, 240);
905 update mtl_transactions_interface mti
906 set last_update_date = sysdate,
907 last_updated_by = fnd_global.user_id,
908 last_update_login = fnd_global.login_id,
909 program_application_id = fnd_global.prog_appl_id,
910 program_id = fnd_global.conc_program_id,
911 program_update_date = sysdate,
912 request_id = fnd_global.conc_request_id,
913 process_flag = 3,
914 lock_flag = 2,
915 error_code = 'transaction_source_id',
916 error_explanation = l_errMsg
917 where transaction_header_id = p_txnHeaderID
918 and process_flag = 1
919 and transaction_source_type_id = 5
920 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
921 WIP_CONSTANTS.CPLASSY_ACTION,
922 WIP_CONSTANTS.RETASSY_ACTION)
923 and ( (exists (select 'X'
924 from wip_discrete_jobs wdj
925 where wdj.wip_entity_id = mti.transaction_source_id
926 and wdj.organization_id = mti.organization_id
927 and wdj.primary_item_id is null))
928 or ( upper(nvl(mti.flow_schedule, 'N')) = 'Y'
929 and exists (select 'X'
930 from wip_flow_schedules wfs
931 where wfs.wip_entity_id = mti.transaction_source_id
932 and wfs.organization_id = mti.organization_id
933 and primary_item_id is null)));
934
935 -- derive earliest valid schedule
936 update mtl_transactions_interface mti
937 set last_update_date = sysdate,
938 last_updated_by = fnd_global.user_id,
939 last_update_login = fnd_global.login_id,
940 program_application_id = fnd_global.prog_appl_id,
941 program_id = fnd_global.conc_program_id,
942 program_update_date = sysdate,
943 request_id = fnd_global.conc_request_id,
944 schedule_id = (select repetitive_schedule_id
945 from wip_repetitive_schedules wrs1
946 where wrs1.organization_id = mti.organization_id
947 and wrs1.wip_entity_id = mti.transaction_source_id
948 and wrs1.line_id = mti.repetitive_line_id
949 and wrs1.status_type in (3, 4)
950 and wrs1.first_unit_start_date =
951 (select min(wrs2.first_unit_start_date)
952 from wip_repetitive_schedules wrs2
953 where wrs2.organization_id = mti.organization_id
954 and wrs2.wip_entity_id = mti.transaction_source_id
955 and wrs2.line_id = mti.repetitive_line_id
956 and wrs2.status_type in (3,4)))
957 where transaction_header_id = p_txnHeaderID
958 and transaction_source_type_id = 5
959 and process_flag = 1
960 and wip_entity_type = 2;
961
962
963 -- derive op seq num for completions
964 update mtl_transactions_interface mti
965 set last_update_date = sysdate,
966 last_updated_by = fnd_global.user_id,
967 last_update_login = fnd_global.login_id,
968 program_application_id = fnd_global.prog_appl_id,
969 program_id = fnd_global.conc_program_id,
970 program_update_date = sysdate,
971 request_id = fnd_global.conc_request_id,
972 operation_seq_num = (select max(operation_seq_num)
973 from wip_operations wo
974 where wo.organization_id = mti.organization_id
975 and wo.wip_entity_id = mti.transaction_source_id
976 and ( mti.wip_entity_type in (1,5)
977 or ( mti.wip_entity_type = 2
978 and wo.repetitive_schedule_id = mti.schedule_id))
979 and wo.next_operation_seq_num is null)
980 where transaction_header_id = p_txnHeaderID
981 and transaction_source_type_id = 5
982 and process_flag = 1
983 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
984 WIP_CONSTANTS.RETASSY_ACTION);
985
986 -- derive op seq num for wip component issue/return
987 update mtl_transactions_interface mti
988 set last_update_date = sysdate,
989 last_updated_by = fnd_global.user_id,
990 last_update_login = fnd_global.login_id,
991 program_application_id = fnd_global.prog_appl_id,
992 program_id = fnd_global.conc_program_id,
993 program_update_date = sysdate,
994 request_id = fnd_global.conc_request_id,
995 operation_seq_num = (select nvl(max(operation_seq_num), 1)
996 from wip_operations wo
997 where wo.organization_id = mti.organization_id
998 and wo.wip_entity_id = mti.transaction_source_id
999 and ( mti.wip_entity_type in (1,5,6)
1000 or ( mti.wip_entity_type = 2
1001 and wo.repetitive_schedule_id = mti.schedule_id))
1002 and wo.next_operation_seq_num is null)
1003 where transaction_header_id = p_txnHeaderID
1004 and transaction_source_type_id = 5
1005 and process_flag = 1
1006 and operation_seq_num is null
1007 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1008 WIP_CONSTANTS.RETCOMP_ACTION);
1009
1010 -- Added Additional Validation of Operation Disable Date for Lot based jobs for Bug 14065582.
1011 -- validate operation seq num
1012 fnd_message.set_name('WIP', 'WIP_INVALID_OPERATION');
1013 l_errMsg := substrb(fnd_message.get, 1, 240);
1014 update mtl_transactions_interface mti
1015 set last_update_date = sysdate,
1016 last_updated_by = fnd_global.user_id,
1017 last_update_login = fnd_global.login_id,
1018 program_application_id = fnd_global.prog_appl_id,
1019 program_id = fnd_global.conc_program_id,
1020 program_update_date = sysdate,
1021 request_id = fnd_global.conc_request_id,
1022 process_flag = 3,
1023 lock_flag = 2,
1024 error_code = 'operation_seq_num',
1025 error_explanation = l_errMsg
1026 where transaction_header_id = p_txnHeaderID
1027 and process_flag = 1
1028 and transaction_source_type_id = 5
1029 and wip_entity_type in (1, 2, 5, 6)
1030 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1031 WIP_CONSTANTS.RETCOMP_ACTION,
1032 WIP_CONSTANTS.ISSNEGC_ACTION,
1033 WIP_CONSTANTS.RETNEGC_ACTION)
1034 and operation_seq_num is not null
1035 and 0 =(select decode(count(wo.operation_seq_num),
1036 0,
1037 decode(mti.operation_seq_num, 1, 1, 0),
1038 decode(sum(decode(sign(mti.operation_seq_num-wo.operation_seq_num),0,1,0)),0,0,1))
1039 from wip_operations wo
1040 where wo.wip_entity_id = mti.transaction_source_id
1041 and wo.organization_id = mti.organization_id
1042 and ((mti.wip_entity_type in (1, 5, 6) and (mti.wip_entity_type <> 5 or (mti.wip_entity_type = 5 and nvl(wo.disable_date,mti.transaction_date+1) > mti.transaction_date)))
1043 or ( mti.wip_entity_type = 2 and wo.repetitive_schedule_id = mti.schedule_id)));
1044
1045
1046 -- check item transactable
1047 fnd_message.set_name('WIP', 'WIP_ITEM_NOT_TRANSACTABLE');
1048 l_errMsg := substrb(fnd_message.get, 1, 240);
1049 begin
1050 l_engItemFlag := to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS'));
1051 exception
1052 when others then
1053 l_engItemFlag := 2; -- default to not an engineering item
1054 end;
1055 update mtl_transactions_interface mti
1056 set last_update_date = sysdate,
1057 last_updated_by = fnd_global.user_id,
1058 last_update_login = fnd_global.login_id,
1059 program_application_id = fnd_global.prog_appl_id,
1060 program_id = fnd_global.conc_program_id,
1061 program_update_date = sysdate,
1062 request_id = fnd_global.conc_request_id,
1063 process_flag = 3,
1064 lock_flag = 2,
1065 error_code = 'inventory_item_id',
1066 error_explanation = l_errMsg
1067 where transaction_header_id = p_txnHeaderID
1068 and process_flag = 1
1069 and transaction_source_type_id = 5
1070 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1071 WIP_CONSTANTS.RETCOMP_ACTION,
1072 WIP_CONSTANTS.ISSNEGC_ACTION,
1073 WIP_CONSTANTS.RETNEGC_ACTION)
1074 and not exists (select 'X'
1075 from mtl_system_items msi
1076 where msi.organization_id = mti.organization_id
1077 and msi.inventory_item_id = mti.inventory_item_id
1078 and msi.mtl_transactions_enabled_flag = 'Y'
1079 and msi.bom_enabled_flag = 'Y'
1080 and msi.eng_item_flag = decode(l_engItemFlag,
1081 1,
1082 msi.eng_item_flag,
1083 'N'))
1084 and ( ( mti.wip_entity_type in (1,5)
1085 and not exists(select 'X'
1086 from wip_requirement_operations wro
1087 where wro.organization_id = mti.organization_id
1088 and wro.wip_entity_id = mti.transaction_source_id
1089 and wro.inventory_item_id = mti.inventory_item_id
1090 and wro.operation_seq_num = mti.operation_seq_num))
1091 or ( mti.wip_entity_type = 2
1092 and not exists(select 'X'
1093 from wip_requirement_operations wro,
1094 wip_repetitive_schedules wrs
1095 where wro.organization_id = mti.organization_id
1096 and wro.wip_entity_id = mti.transaction_source_id
1097 and wro.inventory_item_id = mti.inventory_item_id
1098 and wro.operation_seq_num = mti.operation_seq_num
1099 and wrs.organization_id = wro.organization_id
1100 and wrs.wip_entity_id = wro.wip_entity_id
1101 and wrs.line_id = mti.repetitive_line_id
1102 and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
1103 and wrs.status_type in (3,4))));
1104
1105 /* Added the following for SUN enhancement in Bug 9758848*/
1106 -- check for shop floor status
1107 select mti.transaction_interface_id,
1108 mti.organization_id,
1109 mti.transaction_source_id,
1110 mti.repetitive_line_id,
1111 mti.operation_seq_num
1112 BULK COLLECT INTO l_sfRecTbl
1113 from mtl_transactions_interface mti
1114 where transaction_header_id = p_txnHeaderID
1115 and process_flag = 1
1116 and transaction_source_type_id = 5
1117 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1118 and upper(nvl(mti.flow_schedule, 'N')) = 'N'
1119 and operation_seq_num is not null
1120 and exists (select 'X'
1121 from wip_shop_floor_status_codes wsfsc,
1122 wip_shop_floor_statuses wsfs
1123 where wsfs.wip_entity_id = mti.transaction_source_id
1124 and wsfs.organization_id = mti.organization_id
1125 and nvl(wsfs.line_id, -1) = nvl(mti.repetitive_line_id, -1)
1126 and wsfs.operation_seq_num = mti.operation_seq_num
1127 and wsfs.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1128 and wsfs.shop_floor_status_code = wsfsc.shop_floor_status_code
1129 and wsfsc.organization_id = mti.organization_id
1130 and wsfsc.status_move_flag = WIP_CONSTANTS.NO
1131 and nvl(wsfsc.disable_date, sysdate+1) > sysdate);
1132
1133 if l_sfRecTbl.txnIntID.count > 0 then
1134 for i in 1..l_sfRecTbl.txnIntID.count loop
1135
1136 select wsfs.shop_floor_status_code
1137 into l_sf_status
1138 from wip_shop_floor_status_codes wsfsc,
1139 wip_shop_floor_statuses wsfs
1140 where wsfs.wip_entity_id = l_sfRecTbl.txnSrcID(i)
1141 and wsfs.organization_id = l_sfRecTbl.orgID(i)
1142 and nvl(wsfs.line_id, -1) = nvl(l_sfRecTbl.lineID(i) , -1)
1143 and wsfs.operation_seq_num = l_sfRecTbl.opSeqNum(i)
1144 and wsfs.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1145 and wsfs.shop_floor_status_code = wsfsc.shop_floor_status_code
1146 and wsfsc.organization_id = l_sfRecTbl.orgID(i)
1147 and wsfsc.status_move_flag = WIP_CONSTANTS.NO
1148 and nvl(wsfsc.disable_date, sysdate+1) > sysdate
1149 and rownum = 1;
1150
1151 fnd_message.set_name('WIP', 'WIP_SF_STATUS_NO_TXN2');
1152 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
1153 l_errMsg := substrb(fnd_message.get, 1, 240);
1154
1155 update mtl_transactions_interface mti
1156 set last_update_date = sysdate,
1157 last_updated_by = fnd_global.user_id,
1158 last_update_login = fnd_global.login_id,
1159 program_application_id = fnd_global.prog_appl_id,
1160 program_id = fnd_global.conc_program_id,
1161 program_update_date = sysdate,
1162 request_id = fnd_global.conc_request_id,
1163 process_flag = 3,
1164 lock_flag = 2,
1165 error_code = 'operation_seq_num',
1166 error_explanation = l_errMsg
1167 where transaction_interface_id = l_sfRecTbl.txnIntID(i);
1168
1169 end loop;
1170 end if;
1171
1172 -- check for valid final completion flag, this is not required for flow schedules
1173 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1174 fnd_message.set_token('ENTITY', 'final_completion_flag');
1175 l_errMsg := substrb(fnd_message.get, 1, 240);
1176 update mtl_transactions_interface mti
1177 set last_update_date = sysdate,
1178 last_updated_by = fnd_global.user_id,
1179 last_update_login = fnd_global.login_id,
1180 program_application_id = fnd_global.prog_appl_id,
1181 program_id = fnd_global.conc_program_id,
1182 program_update_date = sysdate,
1183 request_id = fnd_global.conc_request_id,
1184 process_flag = 3,
1185 lock_flag = 2,
1186 error_code = 'final_completion_flag',
1187 error_explanation = l_errMsg
1188 where transaction_header_id = p_txnHeaderID
1189 and process_flag = 1
1190 and transaction_source_type_id = 5
1191 and upper(nvl(flow_schedule, 'N')) = 'N'
1192 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1193 and upper(nvl(final_completion_flag, 'E')) not in ('Y', 'N');
1194
1195
1196 -- derive item revision for completion txns for discrete jobs
1197 update mtl_transactions_interface mti
1198 set last_update_date = sysdate,
1199 last_updated_by = fnd_global.user_id,
1200 last_update_login = fnd_global.login_id,
1201 program_application_id = fnd_global.prog_appl_id,
1202 program_id = fnd_global.conc_program_id,
1203 program_update_date = sysdate,
1204 request_id = fnd_global.conc_request_id,
1205 revision = (select nvl(mti.revision, wdj.bom_revision)
1206 from wip_discrete_jobs wdj
1207 where wdj.organization_id = mti.organization_id
1208 and wdj.wip_entity_id = mti.transaction_source_id)
1209 where transaction_header_id = p_txnHeaderID
1210 and transaction_source_type_id = 5
1211 and process_flag = 1
1212 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
1213 WIP_CONSTANTS.RETASSY_ACTION)
1214 and wip_entity_type in (1,5)
1215 and exists(select 'X'
1216 from mtl_system_items msi
1217 where msi.organization_id = mti.organization_id
1218 and msi.inventory_item_id = mti.inventory_item_id
1219 and msi.revision_qty_control_code = 2);
1220
1221
1222 -- derive item revision for completion txns for repetitive
1223 update mtl_transactions_interface mti
1224 set last_update_date = sysdate,
1225 last_updated_by = fnd_global.user_id,
1226 last_update_login = fnd_global.login_id,
1227 program_application_id = fnd_global.prog_appl_id,
1228 program_id = fnd_global.conc_program_id,
1229 program_update_date = sysdate,
1230 request_id = fnd_global.conc_request_id,
1231 revision = (select nvl(mti.revision, wrs.bom_revision)
1232 from wip_repetitive_schedules wrs
1233 where wrs.organization_id = mti.organization_id
1234 and wrs.repetitive_schedule_id = mti.schedule_id)
1235 where transaction_header_id = p_txnHeaderID
1236 and transaction_source_type_id = 5
1237 and process_flag = 1
1238 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
1239 WIP_CONSTANTS.RETASSY_ACTION)
1240 and wip_entity_type = 2
1241 and exists(select 'X'
1242 from mtl_system_items msi
1243 where msi.organization_id = mti.organization_id
1244 and msi.inventory_item_id = mti.inventory_item_id
1245 and msi.revision_qty_control_code = 2);
1246
1247 -- derive item revision for flow
1248 update mtl_transactions_interface mti
1249 set last_update_date = sysdate,
1250 last_updated_by = fnd_global.user_id,
1251 last_update_login = fnd_global.login_id,
1252 program_application_id = fnd_global.prog_appl_id,
1253 program_id = fnd_global.conc_program_id,
1254 program_update_date = sysdate,
1255 request_id = fnd_global.conc_request_id,
1256 revision = (select nvl(mti.revision, wfs.bom_revision)
1257 from wip_flow_schedules wfs
1258 where wfs.organization_id = mti.organization_id
1259 and wfs.wip_entity_id = mti.transaction_source_id)
1260 where transaction_header_id = p_txnHeaderID
1261 and transaction_source_type_id = 5
1262 and process_flag = 1
1263 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
1264 WIP_CONSTANTS.RETASSY_ACTION,
1265 WIP_CONSTANTS.SCRASSY_ACTION)
1266 and wip_entity_type = 4
1267 and exists(select 'X'
1268 from mtl_system_items msi
1269 where msi.organization_id = mti.organization_id
1270 and msi.inventory_item_id = mti.inventory_item_id
1271 and msi.revision_qty_control_code = 2);
1272
1273 -- derive completion_transaction_id for flow components
1274 update mtl_transactions_interface mti
1275 set last_update_date = sysdate,
1276 last_updated_by = fnd_global.user_id,
1277 last_update_login = fnd_global.login_id,
1278 program_application_id = fnd_global.prog_appl_id,
1279 program_id = fnd_global.conc_program_id,
1280 program_update_date = sysdate,
1281 request_id = fnd_global.conc_request_id,
1282 completion_transaction_id = (select completion_transaction_id
1283 from mtl_transactions_interface mti2
1284 where mti.parent_id = mti2.transaction_interface_id)
1285 where transaction_header_id = p_txnHeaderID
1286 and transaction_source_type_id = 5
1287 and process_flag = 1
1288 -- Fixed bug 4405815. We should only update completion_transaction_id
1289 -- if it is null.
1290 and completion_transaction_id is null
1291 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1292 WIP_CONSTANTS.RETCOMP_ACTION,
1293 WIP_CONSTANTS.ISSNEGC_ACTION,
1294 WIP_CONSTANTS.RETNEGC_ACTION)
1295 and wip_entity_type = 4
1296 and flow_schedule = 'Y';
1297
1298
1299 -- validate revision for completion/return/scrap
1300 fnd_message.set_name('WIP', 'WIP_BOM_ITEM_REVISION');
1301 l_errMsg := substrb(fnd_message.get, 1, 240);
1302 update mtl_transactions_interface mti
1303 set last_update_date = sysdate,
1304 last_updated_by = fnd_global.user_id,
1305 last_update_login = fnd_global.login_id,
1306 program_application_id = fnd_global.prog_appl_id,
1307 program_id = fnd_global.conc_program_id,
1308 program_update_date = sysdate,
1309 request_id = fnd_global.conc_request_id,
1310 process_flag = 3,
1311 lock_flag = 2,
1312 error_code = 'revision',
1313 error_explanation = l_errMsg
1314 where transaction_header_id = p_txnHeaderID
1315 and process_flag = 1
1316 and transaction_source_type_id = 5
1317 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
1318 WIP_CONSTANTS.RETASSY_ACTION,
1319 WIP_CONSTANTS.SCRASSY_ACTION)
1320 and exists (select 'X'
1321 from mtl_system_items msi
1322 where msi.organization_id = mti.organization_id
1323 and msi.inventory_item_id = mti.inventory_item_id
1324 and msi.revision_qty_control_code = 2 )
1325 and not exists (select 'X'
1326 from mtl_item_revisions mir
1327 where mir.organization_id = mti.organization_id
1328 and mir.inventory_item_id = mti.inventory_item_id
1329 and mir.revision = mti.revision);
1330
1331
1332 -- derive revision for material issue if not supplied.
1333 -- **** double-check after merge ********
1334 -- if we explode the BOM and do the merge before calling validation,
1335 -- then we should include flow entities as well. Otherwise, we should do
1336 -- that especially for it alone.
1337 update mtl_transactions_interface mti
1338 set last_update_date = sysdate,
1339 last_updated_by = fnd_global.user_id,
1340 last_update_login = fnd_global.login_id,
1341 program_application_id = fnd_global.prog_appl_id,
1342 program_id = fnd_global.conc_program_id,
1343 program_update_date = sysdate,
1344 request_id = fnd_global.conc_request_id,
1345 revision = (select nvl(mti.revision, max(mir.revision))
1346 from mtl_item_revisions mir
1347 where mir.organization_id = mti.organization_id
1348 and mir.inventory_item_id = mti.inventory_item_id
1349 and mir.effectivity_date <= sysdate
1350 and mir.effectivity_date =
1351 (select max(mir2.effectivity_date)
1352 from mtl_item_revisions mir2
1353 where mir2.organization_id = mti.organization_id
1354 and mir2.inventory_item_id = mti.inventory_item_id
1355 and mir2.effectivity_date <= sysdate))
1356 where transaction_header_id = p_txnHeaderID
1357 and transaction_source_type_id = 5
1358 and process_flag = 1
1359 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1360 WIP_CONSTANTS.RETCOMP_ACTION,
1361 WIP_CONSTANTS.ISSNEGC_ACTION,
1362 WIP_CONSTANTS.RETNEGC_ACTION)
1363 and revision is null
1364 and exists (select 'X'
1365 from mtl_system_items msi
1366 where msi.organization_id = mti.organization_id
1367 and msi.inventory_item_id = mti.inventory_item_id
1368 and msi.revision_qty_control_code = 2);
1369
1370
1371 -- validate item revision for material issue, this is applicable to flow as well
1372 -- **** double-check after merge *******
1373 /* Fixed Performance bug 4890679 -
1374 Replaced bom_bill_released_revisions_v with base tables
1375 by removing the group by clauses
1376 which was causing non mergeable views */
1377 fnd_message.set_name('WIP', 'INV_INT_REVCODE');
1378 l_errMsg := substrb(fnd_message.get, 1, 240);
1379 update mtl_transactions_interface mti
1380 set last_update_date = sysdate,
1381 last_updated_by = fnd_global.user_id,
1382 last_update_login = fnd_global.login_id,
1383 program_application_id = fnd_global.prog_appl_id,
1384 program_id = fnd_global.conc_program_id,
1385 program_update_date = sysdate,
1386 request_id = fnd_global.conc_request_id,
1387 process_flag = 3,
1388 lock_flag = 2,
1389 error_code = 'revision',
1390 error_explanation = l_errMsg
1391 where transaction_header_id = p_txnHeaderID
1392 and process_flag = 1
1393 and transaction_source_type_id = 5
1394 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1395 WIP_CONSTANTS.RETCOMP_ACTION,
1396 WIP_CONSTANTS.ISSNEGC_ACTION,
1397 WIP_CONSTANTS.RETNEGC_ACTION)
1398 and ( ( revision is not null
1399 and ( ( exists(select 'item under rev ctl'
1400 from mtl_system_items msi
1401 where msi.organization_id = mti.organization_id
1402 and msi.inventory_item_id = mti.inventory_item_id
1403 and msi.revision_qty_control_code = 2)
1404 and not exists(select 'rev effective and not an open/hold eco'
1405 FROM ENG_REVISED_ITEMS ERI2,
1406 MTL_ITEM_REVISIONS_B MIR ,
1407 ENG_REVISED_ITEMS ERI,
1408 MTL_ITEM_REVISIONS_B MIR2
1409 WHERE MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
1410 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
1411 AND MIR2.ORGANIZATION_ID(+) = MIR.ORGANIZATION_ID
1412 AND MIR2.INVENTORY_ITEM_ID(+) = MIR.INVENTORY_ITEM_ID
1413 AND MIR2.EFFECTIVITY_DATE(+) > MIR.EFFECTIVITY_DATE
1414 AND MIR2.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID(+)
1415 and MIR.organization_id = mti.organization_id
1416 and MIR.inventory_item_id = mti.inventory_item_id
1417 and MIR.revision = mti.revision
1418 and MIR.effectivity_date <= sysdate))
1419 or (exists (select 'item not under rev ctl'
1420 from mtl_system_items msi
1421 where msi.organization_id = mti.organization_id
1422 and msi.inventory_item_id = mti.inventory_item_id
1423 and msi.revision_qty_control_code = 1))))
1424 or ( revision is null
1425 and ( exists(select 'item is under rev control'
1426 from mtl_system_items msi
1427 where msi.organization_id = mti.organization_id
1428 and msi.inventory_item_id = mti.inventory_item_id
1429 and msi.revision_qty_control_code = 2)
1430 and not exists (select 'any effective rev'
1431 FROM ENG_REVISED_ITEMS ERI2,
1432 MTL_ITEM_REVISIONS_B MIR,
1433 ENG_REVISED_ITEMS ERI,
1434 MTL_ITEM_REVISIONS_B MIR2
1435 WHERE MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
1436 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
1437 AND MIR2.ORGANIZATION_ID(+) = MIR.ORGANIZATION_ID
1438 AND MIR2.INVENTORY_ITEM_ID(+) = MIR.INVENTORY_ITEM_ID
1439 AND MIR2.EFFECTIVITY_DATE(+) > MIR.EFFECTIVITY_DATE
1440 AND MIR2.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID(+)
1441 and MIR.organization_id = mti.organization_id
1442 and MIR.inventory_item_id = mti.inventory_item_id
1443 and MIR.effectivity_date <= sysdate))));
1444
1445 -- sign of transaction qty is validated already in inv
1446
1447
1448 -- validate transaction qty for wip completions.
1449 -- if there is no routing, then can over complete
1450 -- we do allow overcomplete for flow schedules, so we don't need to validation
1451 -- it for flow.
1452 fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
1453 fnd_message.set_token('ENTITY1', 'total txn qty-cap');
1454 fnd_message.set_token('ENTITY2', 'qty avail to complete');
1455 l_errMsg := substrb(fnd_message.get, 1, 240);
1456 update mtl_transactions_interface mti
1457 set last_update_date = sysdate,
1458 last_updated_by = fnd_global.user_id,
1459 last_update_login = fnd_global.login_id,
1460 program_application_id = fnd_global.prog_appl_id,
1461 program_id = fnd_global.conc_program_id,
1462 program_update_date = sysdate,
1463 request_id = fnd_global.conc_request_id,
1464 process_flag = 3,
1465 lock_flag = 2,
1466 error_code = 'transaction_quantity',
1467 error_explanation = l_errMsg
1468 where transaction_header_id = p_txnHeaderID
1469 and process_flag = 1
1470 and transaction_source_type_id = 5
1471 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1472 and wip_entity_type is not null
1473 and wip_entity_type <> 4
1474 and operation_seq_num is not null
1475 and (primary_quantity - nvl(overcompletion_primary_qty, 0)) >
1476 (select sum(quantity_waiting_to_move)
1477 from wip_operations wo
1478 where wo.wip_entity_id = mti.transaction_source_id
1479 and wo.organization_id = mti.organization_id
1480 and wo.operation_seq_num = mti.operation_seq_num
1481 and ( mti.wip_entity_type in (1,5)
1482 or ( mti.wip_entity_type = 2
1483 and wo.repetitive_schedule_id in
1484 (select repetitive_schedule_id
1485 from wip_repetitive_schedules
1486 where wip_entity_id = mti.transaction_source_id
1487 and organization_id = mti.organization_id
1488 and line_id = mti.repetitive_line_id
1489 and status_type in (3,4)))));
1490
1491
1492 -- validate transaction qty for returns against jobs.
1493 -- 1. This is done only for scheduled flow completions
1494 -- 2. according to the comments in inltwv, mmodi, jgu, nsyed, dssosai decided to
1495 -- drive the completed qty negative for flow schedules
1496 fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
1497 fnd_message.set_token('ENTITY1', 'total txn qty-cap');
1498 fnd_message.set_token('ENTITY2', 'job compelete quantity');
1499 l_errMsg := substrb(fnd_message.get, 1, 240);
1500 update mtl_transactions_interface mti
1501 set last_update_date = sysdate,
1502 last_updated_by = fnd_global.user_id,
1503 last_update_login = fnd_global.login_id,
1504 program_application_id = fnd_global.prog_appl_id,
1505 program_id = fnd_global.conc_program_id,
1506 program_update_date = sysdate,
1507 request_id = fnd_global.conc_request_id,
1508 process_flag = 3,
1509 lock_flag = 2,
1510 error_code = 'transaction_quantity',
1511 error_explanation = l_errMsg
1512 where transaction_header_id = p_txnHeaderID
1513 and process_flag = 1
1514 and transaction_source_type_id = 5
1515 and transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
1516 and ( wip_entity_type in (1,5)
1517 and (-1*primary_quantity > (select wdj.quantity_completed
1518 from wip_discrete_jobs wdj
1519 where wdj.organization_id = mti.organization_id
1520 and wdj.wip_entity_id = mti.transaction_source_id)));
1521
1522 -- validate if asset item then cannot complete to expense sub.
1523 -- if profile is set then only check quantity_tracked, disable_date.
1524 -- this is applicable to flow as well
1525 begin
1526 l_poExpToAssetTnsf := fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER');
1527 exception
1528 when others then
1529 l_poExpToAssetTnsf := 2;
1530 end;
1531 fnd_message.set_name('WIP', 'WIP_NO_ASSET_ITEM_MOVE');
1532 l_errMsg := substrb(fnd_message.get, 1, 240);
1533 update mtl_transactions_interface mti
1534 set last_update_date = sysdate,
1535 last_updated_by = fnd_global.user_id,
1536 last_update_login = fnd_global.login_id,
1537 program_application_id = fnd_global.prog_appl_id,
1538 program_id = fnd_global.conc_program_id,
1539 program_update_date = sysdate,
1540 request_id = fnd_global.conc_request_id,
1541 process_flag = 3,
1542 lock_flag = 2,
1543 error_code = 'subinventory_code',
1544 error_explanation = l_errMsg
1545 where transaction_header_id = p_txnHeaderID
1546 and process_flag = 1
1547 and transaction_source_type_id = 5
1548 and transaction_action_id <> WIP_CONSTANTS.SCRASSY_ACTION
1549 and ( ( l_poExpToAssetTnsf = 2
1550 and not exists(select 'X'
1551 from mtl_secondary_inventories sub,
1552 mtl_system_items msi
1553 where msi.organization_id = mti.organization_id
1554 and msi.inventory_item_id = msi.inventory_item_id
1555 and sub.organization_id = mti.organization_id
1556 and sub.secondary_inventory_name = mti.subinventory_code
1557 and nvl(sub.disable_date, trunc(sysdate)+1) > trunc(sysdate)
1558 and ( ( msi.inventory_asset_flag = 'Y'
1559 and sub.asset_inventory = 1
1560 and sub.quantity_tracked =1 )
1561 or msi.inventory_asset_flag = 'N')))
1562 or ( l_poExpToAssetTnsf <> 2
1563 and not exists (select 'X'
1564 from mtl_secondary_inventories sub
1565 where sub.organization_id = mti.organization_id
1566 and nvl(sub.disable_date, trunc(sysdate)+1) > trunc(sysdate)
1567 and sub.quantity_tracked = 1 )));
1568
1569
1570 -- transaction must occure after job/schedule is released.
1571 fnd_message.set_name('WIP', 'WIP_RELEASE_DATE');
1572 l_errMsg := substrb(fnd_message.get, 1, 240);
1573 update mtl_transactions_interface mti
1574 set last_update_date = sysdate,
1575 last_updated_by = fnd_global.user_id,
1576 last_update_login = fnd_global.login_id,
1577 program_application_id = fnd_global.prog_appl_id,
1578 program_id = fnd_global.conc_program_id,
1579 program_update_date = sysdate,
1580 request_id = fnd_global.conc_request_id,
1581 process_flag = 3,
1582 lock_flag = 2,
1583 error_code = 'transaction_date',
1584 error_explanation = l_errMsg
1585 where transaction_header_id = p_txnHeaderID
1586 and process_flag = 1
1587 and transaction_source_type_id = 5
1588 and ( ( mti.wip_entity_type = 2
1589 and mti.transaction_date <
1590 (select min(wrs.date_released)
1591 from wip_repetitive_schedules wrs
1592 where wrs.line_id = mti.repetitive_line_id
1593 and wrs.organization_id = mti.organization_id
1594 and wrs.wip_entity_id = mti.transaction_source_id
1595 and wrs.status_type in (3,4)))
1596 or ( mti.wip_entity_type in (1, 5, 6)
1597 and mti.transaction_date <
1598 (select wdj.date_released
1599 from wip_discrete_jobs wdj
1600 where wdj.wip_entity_id = mti.transaction_source_id
1601 and wdj.organization_id = mti.organization_id
1602 and wdj.status_type in (3, 4))));
1603
1604
1605 -- validate sales order demand for completions and returns
1606 -- **** do we need to do that for flow? ****
1607 fnd_message.set_name('WIP', 'WIP_INVALID_SO');
1608 l_errMsg := substrb(fnd_message.get, 1, 240);
1609 update mtl_transactions_interface mti
1610 set last_update_date = sysdate,
1611 last_updated_by = fnd_global.user_id,
1612 last_update_login = fnd_global.login_id,
1613 program_application_id = fnd_global.prog_appl_id,
1614 program_id = fnd_global.conc_program_id,
1615 program_update_date = sysdate,
1616 request_id = fnd_global.conc_request_id,
1617 process_flag = 3,
1618 lock_flag = 2,
1619 error_code = 'demand_source_header_id',
1620 error_explanation = l_errMsg
1621 where transaction_header_id = p_txnHeaderID
1622 and process_flag = 1
1623 and transaction_source_type_id = 5
1624 and wip_entity_type in (1, 2, 5)
1625 and demand_source_header_id is not null
1626 and not exists (select 'X'
1627 from mtl_reservations
1628 where organization_id = mti.organization_id
1629 and inventory_item_id = mti.inventory_item_id
1630 and nvl(revision, '--1') = nvl(mti.revision, '--1')
1631 and demand_source_type_id = inv_reservation_global.g_source_type_oe
1632 and demand_source_header_id = mti.demand_source_header_id
1633 and ( ( mti.transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1634 and supply_source_header_id = mti.transaction_source_id
1635 and supply_source_type_id =
1636 inv_reservation_global.g_source_type_wip)
1637 or ( mti.transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
1638 and supply_source_type_id =
1639 inv_reservation_global.g_source_type_inv
1640 and subinventory_code = mti.subinventory_code
1641 and nvl(locator_id, -1) = nvl(mti.locator_id, -1) )));
1642
1643 -- validate demand so line
1644 fnd_message.set_name('WIP', 'WIP_INVALID_SO_LINE');
1645 l_errMsg := substrb(fnd_message.get, 1, 240);
1646 update mtl_transactions_interface mti
1647 set last_update_date = sysdate,
1648 last_updated_by = fnd_global.user_id,
1649 last_update_login = fnd_global.login_id,
1650 program_application_id = fnd_global.prog_appl_id,
1651 program_id = fnd_global.conc_program_id,
1652 program_update_date = sysdate,
1653 request_id = fnd_global.conc_request_id,
1654 process_flag = 3,
1655 lock_flag = 2,
1656 error_code = 'demand_source_line',
1657 error_explanation = l_errMsg
1658 where transaction_header_id = p_txnHeaderID
1659 and process_flag = 1
1660 and transaction_source_type_id = 5
1661 and wip_entity_type in (1, 2, 5)
1662 and demand_source_header_id is not null
1663 and not exists (select 'X'
1664 from mtl_reservations
1665 where organization_id = mti.organization_id
1666 and inventory_item_id = mti.inventory_item_id
1667 and nvl(revision, '--1') = nvl(mti.revision, '--1')
1668 and demand_source_type_id =
1669 inv_reservation_global.g_source_type_oe
1670 and demand_source_header_id = mti.demand_source_header_id
1671 and nvl(demand_source_line_id, -1) =
1672 nvl(to_number(mti.demand_source_line), -1)
1673 and ( ( mti.transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1674 and supply_source_header_id = mti.transaction_source_id
1675 and supply_source_type_id =
1676 inv_reservation_global.g_source_type_wip)
1677 or ( mti.transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
1678 and supply_source_type_id =
1679 inv_reservation_global.g_source_type_inv
1680 and subinventory_code = mti.subinventory_code
1681 and nvl(locator_id, -1) = nvl(mti.locator_id, -1) )));
1682
1683
1684 -- validate demand so shipment number for completions
1685 fnd_message.set_name('WIP', 'WIP_INVALID_SO_SHIPNO_COMP');
1686 l_errMsg := substrb(fnd_message.get, 1, 240);
1687 update mtl_transactions_interface mti
1688 set last_update_date = sysdate,
1689 last_updated_by = fnd_global.user_id,
1690 last_update_login = fnd_global.login_id,
1691 program_application_id = fnd_global.prog_appl_id,
1692 program_id = fnd_global.conc_program_id,
1693 program_update_date = sysdate,
1694 request_id = fnd_global.conc_request_id,
1695 process_flag = 3,
1696 lock_flag = 2,
1697 error_code = 'demand_source_delivery',
1698 error_explanation = l_errMsg
1699 where transaction_header_id = p_txnHeaderID
1700 and process_flag = 1
1701 and transaction_source_type_id = 5
1702 and wip_entity_type in (1, 2, 5)
1703 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1704 and demand_source_header_id is not null
1705 and not exists (select 'X'
1706 from mtl_reservations
1707 where organization_id = mti.organization_id
1708 and inventory_item_id = mti.inventory_item_id
1709 and nvl(revision, '--1') = nvl(mti.revision, '--1')
1710 and demand_source_type_id =
1711 inv_reservation_global.g_source_type_oe
1712 and demand_source_header_id = mti.demand_source_header_id
1713 and nvl(demand_source_line_id, -1) =
1714 nvl(to_number(mti.demand_source_line), -1)
1715 and supply_source_type_id =
1716 inv_reservation_global.g_source_type_wip
1717 and supply_source_header_id = mti.transaction_source_id
1718 and primary_reservation_quantity >= mti.primary_quantity);
1719
1720
1721 -- validate demand so shipment number for returns
1722 fnd_message.set_name('WIP', 'WIP_INVALID_SO_SHIPNO_RET');
1723 l_errMsg := substrb(fnd_message.get, 1, 240);
1724 update mtl_transactions_interface mti
1725 set last_update_date = sysdate,
1726 last_updated_by = fnd_global.user_id,
1727 last_update_login = fnd_global.login_id,
1728 program_application_id = fnd_global.prog_appl_id,
1729 program_id = fnd_global.conc_program_id,
1730 program_update_date = sysdate,
1731 request_id = fnd_global.conc_request_id,
1732 process_flag = 3,
1733 lock_flag = 2,
1734 error_code = 'demand_source_delivery',
1735 error_explanation = l_errMsg
1736 where transaction_header_id = p_txnHeaderID
1737 and process_flag = 1
1738 and transaction_source_type_id = 5
1739 and wip_entity_type in (1, 2, 5)
1740 and transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
1741 and demand_source_header_id is not null
1742 and not exists (select 'X'
1743 from mtl_reservations
1744 where organization_id = mti.organization_id
1745 and inventory_item_id = mti.inventory_item_id
1746 and nvl(revision, '--1') = nvl(mti.revision, '--1')
1747 and demand_source_type_id =
1748 inv_reservation_global.g_source_type_oe
1749 and demand_source_header_id = mti.demand_source_header_id
1750 and nvl(demand_source_line_id, -1) =
1751 nvl(to_number(mti.demand_source_line), -1)
1752 and supply_source_type_id =
1753 inv_reservation_global.g_source_type_inv
1754 and subinventory_code = mti.subinventory_code
1755 and nvl(locator_id, -1) = nvl(mti.locator_id, -1)
1756 and primary_reservation_quantity >= -1*mti.primary_quantity);
1757
1758
1759 -- validate the kanban card
1760 -- check that a completion txn does not have both, a kanban card and a sales
1761 -- order attached. Also only a completion txn can have a kanban card attached
1762 fnd_message.set_name('WIP', 'WIP_KB_ILLEGAL_CARD');
1763 l_errMsg := substrb(fnd_message.get, 1, 240);
1764 update mtl_transactions_interface mti
1765 set last_update_date = sysdate,
1766 last_updated_by = fnd_global.user_id,
1767 last_update_login = fnd_global.login_id,
1768 program_application_id = fnd_global.prog_appl_id,
1769 program_id = fnd_global.conc_program_id,
1770 program_update_date = sysdate,
1771 request_id = fnd_global.conc_request_id,
1772 process_flag = 3,
1773 lock_flag = 2,
1774 error_code = 'kanban_card',
1775 error_explanation = l_errMsg
1776 where transaction_header_id = p_txnHeaderID
1777 and process_flag = 1
1778 and transaction_source_type_id = 5
1779 and ( ( transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1780 and demand_source_header_id is not null
1781 and kanban_card_id is not null)
1782 or( transaction_action_id <> WIP_CONSTANTS.CPLASSY_ACTION
1783 and kanban_card_id is not null));
1784
1785
1786 -- validate the kanban card
1787 -- check that the completion subinv, locator, inventory_item_id of the
1788 -- completion txn against the kanban card
1789 fnd_message.set_name('WIP', 'WIP_KB_CPL_SUB_LOC_MISMATCH');
1790 l_errMsg := substrb(fnd_message.get, 1, 240);
1791 update mtl_transactions_interface mti
1792 set last_update_date = sysdate,
1793 last_updated_by = fnd_global.user_id,
1794 last_update_login = fnd_global.login_id,
1795 program_application_id = fnd_global.prog_appl_id,
1796 program_id = fnd_global.conc_program_id,
1797 program_update_date = sysdate,
1798 request_id = fnd_global.conc_request_id,
1799 process_flag = 3,
1800 lock_flag = 2,
1801 error_code = 'kanban_card',
1802 error_explanation = l_errMsg
1803 where transaction_header_id = p_txnHeaderID
1804 and process_flag = 1
1805 and transaction_source_type_id = 5
1806 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1807 and kanban_card_id is not null
1808 and not exists (select 'X'
1809 from mtl_kanban_cards mkc
1810 where mkc.kanban_card_id = mti.kanban_card_id
1811 and mkc.source_type = 4
1812 and mkc.organization_id = mti.organization_id
1813 and mkc.subinventory_name = mti.subinventory_code
1814 and ( mti.locator_id is null
1815 or mkc.locator_id = mti.locator_id));
1816
1817
1818 -- validate the kanban card.
1819 -- check the status of the kanban card
1820 fnd_message.set_name('WIP', 'WIP_KB_CPL_STATUS_ILLEGAL');
1821 l_errMsg := substrb(fnd_message.get, 1, 240);
1822 update mtl_transactions_interface mti
1823 set last_update_date = sysdate,
1824 last_updated_by = fnd_global.user_id,
1825 last_update_login = fnd_global.login_id,
1826 program_application_id = fnd_global.prog_appl_id,
1827 program_id = fnd_global.conc_program_id,
1828 program_update_date = sysdate,
1829 request_id = fnd_global.conc_request_id,
1830 process_flag = 3,
1831 lock_flag = 2,
1832 error_code = 'kanban_card',
1833 error_explanation = l_errMsg
1834 where transaction_header_id = p_txnHeaderID
1835 and process_flag = 1
1836 and transaction_source_type_id = 5
1837 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1838 and kanban_card_id is not null
1839 and not exists (select 'X'
1840 from mtl_kanban_cards mkc
1841 where mkc.kanban_card_id = mti.kanban_card_id
1842 and mkc.organization_id = mti.organization_id
1843 and ( mkc.supply_status in (4, 5)
1844 or ( mkc.supply_status = 2
1845 and exists
1846 (select 'X'
1847 from mtl_kanban_card_activity mkca
1848 where mkca.kanban_card_id = mti.kanban_card_id
1849 and mkca.organization_id = mti.organization_id
1850 and mkca.document_header_id =
1851 mti.transaction_source_id))));
1852
1853 -- validate negative requirement flag for rep scheds
1854 -- this is not required for flow
1855 fnd_message.set_name('WIP', 'WIP_INVALID_NEG_REQ_FLAG');
1856 l_errMsg := substrb(fnd_message.get, 1, 240);
1857 update mtl_transactions_interface mti
1858 set last_update_date = sysdate,
1859 last_updated_by = fnd_global.user_id,
1860 last_update_login = fnd_global.login_id,
1861 program_application_id = fnd_global.prog_appl_id,
1862 program_id = fnd_global.conc_program_id,
1863 program_update_date = sysdate,
1864 request_id = fnd_global.conc_request_id,
1865 process_flag = 3,
1866 lock_flag = 2,
1867 error_code = 'negative_req_flag',
1868 error_explanation = l_errMsg
1869 where transaction_header_id = p_txnHeaderID
1870 and process_flag = 1
1871 and transaction_source_type_id = 5
1872 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
1873 WIP_CONSTANTS.RETCOMP_ACTION)
1874 and wip_entity_type = 2
1875 and negative_req_flag is not null
1876 and negative_req_flag not in (1, -1);
1877
1878 if(l_logLevel <= wip_constants.full_logging) then
1879 wip_logger.log('about to call EAM validations', l_returnStatus);
1880 end if;
1881
1882 -- for discrete job, we do not support background transactions for
1883 -- serialized job
1884 fnd_message.set_name('WIP', 'WIP_NO_SERIALIZED_JOB_ALLOW');
1885 l_errMsg := substrb(fnd_message.get, 1, 240);
1886 update mtl_transactions_interface mti
1887 set last_update_date = sysdate,
1888 last_updated_by = fnd_global.user_id,
1889 last_update_login = fnd_global.login_id,
1890 program_application_id = fnd_global.prog_appl_id,
1891 program_id = fnd_global.conc_program_id,
1892 program_update_date = sysdate,
1893 request_id = fnd_global.conc_request_id,
1894 process_flag = 3,
1895 lock_flag = 2,
1896 error_code = 'transaction_source_id',
1897 error_explanation = l_errMsg
1898 where transaction_header_id = p_txnHeaderID
1899 and process_flag = 1
1900 and transaction_source_type_id = 5
1901 and wip_entity_type = 1 -- Discrete Jobs
1902 and transaction_source_id is not null
1903 and not exists
1904 (select 'x'
1905 from wip_discrete_jobs wdj,
1906 wip_entities we
1907 where wdj.wip_entity_id = mti.transaction_source_id
1908 and wdj.organization_id = mti.organization_id
1909 and wdj.wip_entity_id = we.wip_entity_id
1910 and (we.entity_type = wip_constants.lotbased or
1911 wdj.serialization_start_op is null));
1912
1913
1914 --for assy completions and returns, project/task must match
1915 --source project/task
1916 update mtl_transactions_interface mti
1917 set last_update_date = sysdate,
1918 last_updated_by = fnd_global.user_id,
1919 last_update_login = fnd_global.login_id,
1920 program_application_id = fnd_global.prog_appl_id,
1921 program_id = fnd_global.conc_program_id,
1922 program_update_date = sysdate,
1923 request_id = fnd_global.conc_request_id,
1924 project_id = source_project_id,
1925 task_id = source_task_id
1926 where transaction_header_id = p_txnHeaderID
1927 and process_flag = 1
1928 and transaction_source_type_id = 5
1929 and transaction_action_id in (wip_constants.cplassy_action,
1930 wip_constants.retassy_action);
1931
1932
1933 --call eam specific validations. if it errors, just return error status
1934 wip_eamMtlProc_priv.validateTxns(p_txnHdrID => p_txnHeaderID,
1935 x_returnStatus => x_returnStatus);
1936
1937 if(l_logLevel <= wip_constants.full_logging) then
1938 wip_logger.log('EAM validation routine returned:' || x_returnStatus, l_returnStatus);
1939 end if;
1940
1941 /* Fix for bug 5708242: Call to cstpacms.validate_move_snap_to_temp has been moved to
1942 wip_cplProc_priv.processTemp (wipcplpb.pls). Calling it here will commit records to
1943 CST_COMP_SNAP_TEMP even if error occurs later, and rollback is issued */
1944 -- do the snapshot moves for non-CFM WIP completions,returns, scraps
1945 /*Bug 7300614(FP 7281109) Replacing back the code commented in bug 5708242
1946 Now this code calls cstpacms.validate_snap_interface which would validate the CCSI record
1947 before going for inventory processing. Hence the inventory transaction does not happen
1948 unless the CCSI is validate. */
1949 for nonCfm_rec in nonCfm_c loop
1950 select primary_cost_method
1951 into l_primaryCostMethod
1952 from mtl_parameters
1953 where organization_id = nonCfm_rec.organization_id;
1954
1955 if ( l_primaryCostMethod in (2, 5, 6) ) then
1956 select primary_uom_code
1957 into l_priUOM
1958 from mtl_system_items
1959 where organization_id = nonCfm_rec.organization_id
1960 and inventory_item_id = nonCfm_rec.inventory_item_id;
1961
1962 l_priQty := inv_convert.inv_um_convert(
1963 item_id => nonCfm_rec.inventory_item_id,
1964 precision => NULL,
1965 from_quantity => nonCfm_rec.transaction_quantity,
1966 from_unit => nonCfm_rec.transaction_uom,
1967 to_unit => l_priUOM,
1968 from_name => NULL,
1969 to_name => NULL);
1970
1971 l_cstRetVal := cstpacms.validate_snap_interface(
1972 nonCfm_rec.transaction_interface_id,
1973 1, -- for inventory interface
1974 l_priQty,
1975 l_errNum,
1976 l_errCode,
1977 l_errMsg);
1978 if ( l_cstRetVal <> 1 ) then
1979 setMtiError(nonCfm_rec.transaction_interface_id,
1980 l_errCode,
1981 l_errMsg);
1982 end if;
1983 end if;
1984 end loop;
1985
1986 if(l_logLevel <= wip_constants.trace_logging) then
1987 wip_logger.exitPoint(p_procName => 'wip_mti_pub.postInvWIPValidation',
1988 p_procReturnStatus => x_returnStatus,
1989 p_msg => 'Finished validating interface rows!',
1990 x_returnStatus => l_returnStatus); --discard logging return status
1991 end if;
1992 exception
1993 when others then
1994 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1995 if (l_logLevel <= wip_constants.trace_logging) then
1996 wip_logger.exitPoint(p_procName => 'wip_mti_pub.postInvWIPValidation',
1997 p_procReturnStatus => x_returnStatus,
1998 p_msg => 'unexpected error: ' || SQLERRM,
1999 x_returnStatus => l_returnStatus); --discard logging return status
2000 end if;
2001 update mtl_transactions_interface
2002 set last_update_date = sysdate,
2003 last_updated_by = fnd_global.user_id,
2004 last_update_login = fnd_global.login_id,
2005 program_application_id = fnd_global.prog_appl_id,
2006 program_id = fnd_global.conc_program_id,
2007 program_update_date = sysdate,
2008 request_id = fnd_global.conc_request_id,
2009 process_flag = 3,
2010 lock_flag = 2
2011 where transaction_header_id = p_txnHeaderID;
2012 end postInvWIPValidation;
2013
2014
2015 /**
2016 * This procedure sets the error status to the mti. It sets the error
2017 * for the given interface id as well as the child records.
2018 */
2019 procedure setMtiError(p_txnInterfaceID in number,
2020 p_errCode in varchar2,
2021 p_msgData in varchar2) is
2022
2023 begin
2024 update mtl_transactions_interface
2025 set last_update_date = sysdate,
2026 last_updated_by = fnd_global.user_id,
2027 last_update_login = fnd_global.login_id,
2028 program_application_id = fnd_global.prog_appl_id,
2029 program_id = fnd_global.conc_program_id,
2030 program_update_date = sysdate,
2031 request_id = fnd_global.conc_request_id,
2032 process_flag = 3,
2033 lock_flag = 2,
2034 error_code = substrb(p_errCode, 1, 240),
2035 error_explanation = substrb(p_msgData, 1, 240)
2036 where transaction_interface_id = p_txnInterfaceID
2037 or parent_id = p_txnInterfaceID;
2038 end setMtiError;
2039
2040 procedure doPreProcessingValidations(p_txnHeaderID in number,
2041 x_returnStatus out nocopy varchar2) is
2042 l_returnStatus VARCHAR2(1);
2043 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2044 l_errCode VARCHAR2(240);
2045 l_errMsg VARCHAR2(240);
2046 l_params wip_logger.param_tbl_t;
2047 l_orgIDTbl num_tbl_t;
2048 l_rowidTbl rowid_tbl_t;
2049 l_subCodeTbl big_char_tbl_t;
2050 l_itemIDTbl num_tbl_t;
2051 /* FP bug 5708701 (base bug 5046732) - commenting the following variables
2052 l_locIDTbl num_tbl_t;
2053 l_locSegTbl big_char_tbl_t;
2054 l_locCtrl NUMBER;
2055 */
2056 cursor c_ItemTxns is
2057 select organization_id,
2058 rowidtochar(rowid),
2059 subinventory_code,
2060 inventory_item_id
2061 /* FP bug 5708701 (base bug 5046732) - Modified cursor. Need not fetch locator_id and locator segments
2062 locator_id,
2063 nvl(loc_segment1, nvl(loc_segment2, nvl(loc_segment3, nvl(loc_segment4,
2064 nvl(loc_segment5, nvl(loc_segment6, nvl(loc_segment7, nvl(loc_segment8,
2065 nvl(loc_segment9, nvl(loc_segment10, nvl(loc_segment11, nvl(loc_segment12,
2066 nvl(loc_segment13, nvl(loc_segment14, nvl(loc_segment15, nvl(loc_segment16,
2067 nvl(loc_segment17, nvl(loc_segment18, nvl(loc_segment19, loc_segment20)))))))))))))))))))
2068 */
2069 from mtl_transactions_interface
2070 where transaction_header_id = p_txnHeaderID
2071 and transaction_source_type_id = 5
2072 and process_flag = wip_constants.mti_inventory
2073 and ( inventory_item_id is null
2074 or ( locator_id is null
2075 and ( loc_segment1 is not null
2076 or loc_segment2 is not null
2077 or loc_segment3 is not null
2078 or loc_segment4 is not null
2079 or loc_segment5 is not null
2080 or loc_segment6 is not null
2081 or loc_segment7 is not null
2082 or loc_segment8 is not null
2083 or loc_segment9 is not null
2084 or loc_segment10 is not null
2085 or loc_segment11 is not null
2086 or loc_segment12 is not null
2087 or loc_segment13 is not null
2088 or loc_segment14 is not null
2089 or loc_segment15 is not null
2090 or loc_segment16 is not null
2091 or loc_segment17 is not null
2092 or loc_segment18 is not null
2093 or loc_segment19 is not null
2094 or loc_segment20 is not null
2095 )
2096 )
2097 );
2098 begin
2099 if (l_logLevel <= wip_constants.trace_logging) then
2100 l_params(1).paramName := 'p_txnHeaderID';
2101 l_params(1).paramValue := p_txnHeaderID;
2102 wip_logger.entryPoint(p_procName => 'wip_mti_pub.doPreProcessingValidations',
2103 p_params => l_params,
2104 x_returnStatus => l_returnStatus);
2105 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2106 raise fnd_api.g_exc_unexpected_error;
2107 end if;
2108 end if;
2109 x_returnStatus := fnd_api.g_ret_sts_success;
2110
2111 -- derive transaction action id and transaction source type id,
2112 -- we need that for the logic that follows. Even though inv validation does
2113 -- the deriviation, we still needs to do that since we call inv afterwards.
2114 update mtl_transactions_interface mti
2115 set last_update_date = sysdate,
2116 last_updated_by = fnd_global.user_id,
2117 last_update_login = fnd_global.login_id,
2118 program_application_id = fnd_global.prog_appl_id,
2119 program_id = fnd_global.conc_program_id,
2120 program_update_date = sysdate,
2121 request_id = fnd_global.conc_request_id,
2122 transaction_action_id = (select mtt.transaction_action_id
2123 from mtl_transaction_types mtt
2124 where mtt.transaction_type_id =
2125 mti.transaction_type_id),
2126 transaction_source_type_id = (select mtt.transaction_source_type_id /*bug 4236301 -> changed table alias to mtt */
2127 from mtl_transaction_types mtt
2128 where mtt.transaction_type_id =
2129 mti.transaction_type_id)
2130 where transaction_header_id = p_txnHeaderID
2131 and process_flag = 1;
2132
2133 --make sure the completions have a cpl id and batch id
2134 --make sure overcompletions have a move id and overcpl id.
2135 update mtl_transactions_interface
2136 set completion_transaction_id = nvl(completion_transaction_id, mtl_material_transactions_s.nextval),
2137 transaction_batch_id = nvl(transaction_batch_id, nvl(completion_transaction_id, mtl_material_transactions_s.nextval)),
2138 transaction_batch_seq = nvl(transaction_batch_seq, wip_constants.ASSY_BATCH_SEQ),
2139 overcompletion_transaction_id = nvl(overcompletion_transaction_id, decode(overcompletion_transaction_qty,
2140 null, overcompletion_transaction_id,
2141 wip_transactions_s.nextval)),
2142 move_transaction_id = nvl(move_transaction_id, decode(overcompletion_transaction_qty,
2143 null, move_transaction_id,
2144 wip_transactions_s.nextval))
2145 where transaction_header_id = p_txnHeaderID
2146 and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
2147 and transaction_source_type_id = 5;
2148
2149 --make sure flow scrap transactions have a cpl id and batch id
2150 update mtl_transactions_interface
2151 set completion_transaction_id = nvl(completion_transaction_id, mtl_material_transactions_s.nextval),
2152 transaction_batch_id = nvl(transaction_batch_id, nvl(completion_transaction_id, mtl_material_transactions_s.nextval)),
2153 transaction_batch_seq = nvl(transaction_batch_seq, wip_constants.ASSY_BATCH_SEQ)
2154 where transaction_header_id = p_txnHeaderID
2155 and transaction_action_id = wip_constants.scrassy_action
2156 and upper(nvl(flow_schedule, 'N')) = 'Y'
2157 and transaction_source_type_id = 5;
2158
2159
2160 -- validate organization id
2161 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
2162 l_errCode := substrb(fnd_message.get, 1, 240);
2163 fnd_message.set_name('INV', 'INV_INT_ORGEXP');
2164 l_errMsg := substrb(fnd_message.get, 1, 240);
2165 update mtl_transactions_interface mti
2166 set last_update_date = sysdate,
2167 last_updated_by = fnd_global.user_id,
2168 last_update_login = fnd_global.login_id,
2169 program_application_id = fnd_global.prog_appl_id,
2170 program_id = fnd_global.conc_program_id,
2171 program_update_date = sysdate,
2172 request_id = fnd_global.conc_request_id,
2173 process_flag = 3,
2174 lock_flag = 2,
2175 error_code = l_errCode,
2176 error_explanation = l_errMsg
2177 where transaction_header_id = p_txnHeaderID
2178 and process_flag = 1
2179 and transaction_source_type_id = 5
2180 and not exists (select 'X'
2181 from org_organization_definitions ood
2182 where ood.organization_id = mti.organization_id
2183 and nvl(ood.disable_date, sysdate+1) > sysdate);
2184
2185
2186 --validate scheduled_flag
2187 fnd_message.set_name('WIP', 'WIP_INVALID_FLOW_SCHED_FLAG');
2188 l_errMsg := substrb(fnd_message.get, 1, 240);
2189 update mtl_transactions_interface mti
2190 set last_update_date = sysdate,
2191 last_updated_by = fnd_global.user_id,
2192 last_update_login = fnd_global.login_id,
2193 program_application_id = fnd_global.prog_appl_id,
2194 program_id = fnd_global.conc_program_id,
2195 program_update_date = sysdate,
2196 request_id = fnd_global.conc_request_id,
2197 process_flag = 3,
2198 lock_flag = 2,
2199 error_code = 'Invalid Scheduled Flag',
2200 error_explanation = l_errMsg
2201 where transaction_header_id = p_txnHeaderID
2202 and process_flag = 1
2203 and transaction_source_type_id = 5
2204 and upper(nvl(flow_schedule, 'N')) = 'Y'
2205 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
2206 WIP_CONSTANTS.CPLASSY_ACTION,
2207 WIP_CONSTANTS.RETASSY_ACTION)
2208 and nvl(scheduled_flag, -1) not in (1, 2);
2209
2210
2211 -- validate transaction source name if provided, we won't do
2212 -- the validation for that if id is provided since id overrides name anyway
2213 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
2214 fnd_message.set_token('ENTITY', 'transaction_source_name');
2215 l_errMsg := substrb(fnd_message.get, 1, 240);
2216 update mtl_transactions_interface mti
2217 set last_update_date = sysdate,
2218 last_updated_by = fnd_global.user_id,
2219 last_update_login = fnd_global.login_id,
2220 program_application_id = fnd_global.prog_appl_id,
2221 program_id = fnd_global.conc_program_id,
2222 program_update_date = sysdate,
2223 request_id = fnd_global.conc_request_id,
2224 process_flag = 3,
2225 lock_flag = 2,
2226 error_code = 'transaction_source_name',
2227 error_explanation = l_errMsg
2228 where transaction_header_id = p_txnHeaderID
2229 and process_flag = 1
2230 and transaction_source_type_id = 5
2231 and transaction_source_id is null
2232 and transaction_source_name is not null
2233 and not exists (select 'X'
2234 from wip_entities we
2235 where we.organization_id = mti.organization_id
2236 and we.wip_entity_name = mti.transaction_source_name);
2237
2238
2239 -- derive transaction source id from transaction source name
2240 update mtl_transactions_interface mti
2241 set last_update_date = sysdate,
2242 last_updated_by = fnd_global.user_id,
2243 last_update_login = fnd_global.login_id,
2244 program_application_id = fnd_global.prog_appl_id,
2245 program_id = fnd_global.conc_program_id,
2246 program_update_date = sysdate,
2247 request_id = fnd_global.conc_request_id,
2248 transaction_source_id = (select we.wip_entity_id
2249 from wip_entities we
2250 where we.organization_id = mti.organization_id
2251 and we.wip_entity_name = mti.transaction_source_name)
2252 where transaction_header_id = p_txnHeaderID
2253 and process_flag = 1
2254 and transaction_source_type_id = 5
2255 and transaction_source_name is not null
2256 and transaction_source_id is null;
2257
2258
2259 -- validate transaction action
2260 fnd_message.set_name('INV', 'INV_INT_TRXACTCODE');
2261 l_errCode := substrb(fnd_message.get, 1, 240);
2262 fnd_message.set_name('INV', 'INV_INT_TRXACTEXP');
2263 l_errMsg := substrb(fnd_message.get, 1, 240);
2264 update mtl_transactions_interface mti
2265 set last_update_date = sysdate,
2266 last_updated_by = fnd_global.user_id,
2267 last_update_login = fnd_global.login_id,
2268 program_application_id = fnd_global.prog_appl_id,
2269 program_id = fnd_global.conc_program_id,
2270 program_update_date = sysdate,
2271 request_id = fnd_global.conc_request_id,
2272 process_flag = 3,
2273 lock_flag = 2,
2274 error_code = l_errCode,
2275 error_explanation = l_errMsg
2276 where transaction_header_id = p_txnHeaderID
2277 and process_flag = 1
2278 and transaction_source_type_id = 5
2279 and transaction_action_id = WIP_CONSTANTS.SCRASSY_ACTION
2280 and upper(nvl(flow_schedule, 'N')) <> 'Y';
2281
2282
2283 -- validate transaction source id
2284 fnd_message.set_name('INV', 'INV_INT_SRCCODE');
2285 l_errCode := substrb(fnd_message.get, 1, 240);
2286 fnd_message.set_name('INV', 'INV_INT_SRCWIPEXP');
2287 l_errMsg := substrb(fnd_message.get, 1, 240);
2288 update mtl_transactions_interface mti
2289 set last_update_date = sysdate,
2290 last_updated_by = fnd_global.user_id,
2291 last_update_login = fnd_global.login_id,
2292 program_application_id = fnd_global.prog_appl_id,
2293 program_id = fnd_global.conc_program_id,
2294 program_update_date = sysdate,
2295 request_id = fnd_global.conc_request_id,
2296 process_flag = 3,
2297 lock_flag = 2,
2298 error_code = l_errCode,
2299 error_explanation = l_errMsg
2300 where transaction_header_id = p_txnHeaderID
2301 and process_flag = 1
2302 and transaction_source_type_id = 5
2303 and ( (upper(nvl(flow_schedule, 'N')) = 'N'
2304 and not exists (select null
2305 from wip_entities we
2306 where we.organization_id = mti.organization_id
2307 and we.wip_entity_id = mti.transaction_source_id))
2308 or (upper(nvl(flow_schedule, 'N')) = 'Y'
2309 and scheduled_flag = 1
2310 and not exists (select null
2311 from wip_entities we
2312 where we.organization_id = mti.organization_id
2313 and we.wip_entity_id = mti.transaction_source_id
2314 and we.entity_type = 4)));
2315
2316 /* Fix for Bug#4893215 . Make sure that Flow and Work Order-less transaction
2317 * is processed as one batch - parent+components
2318 * */
2319
2320 fnd_message.set_name('INV', 'INV_INT_PROCCODE');
2321 l_errCode := substrb(fnd_message.get, 1, 240);
2322
2323 fnd_message.set_name('WIP', 'WIP_NO_PARENT_TRANSACTION');
2324 l_errMsg := substrb(fnd_message.get, 1, 240);
2325
2326 update mtl_transactions_interface mti
2327 set last_update_date = sysdate,
2328 last_updated_by = fnd_global.user_id,
2329 last_update_login = fnd_global.login_id,
2330 program_application_id = fnd_global.prog_appl_id,
2331 program_id = fnd_global.conc_program_id,
2332 program_update_date = sysdate,
2333 request_id = fnd_global.conc_request_id,
2334 process_flag = 3,
2335 lock_flag = 2,
2336 error_code = l_errCode,
2337 error_explanation = l_errMsg
2338 where transaction_header_id = p_txnHeaderID
2339 and process_flag = 1
2340 and transaction_source_type_id = 5
2341 and transaction_type_id not in (17, 44, 90, 91) -- No Parent transactions
2342 and upper(nvl(flow_schedule, 'N')) = 'Y'
2343 and not exists (select 1
2344 from mtl_transactions_interface mti2
2345 where mti2.transaction_header_id = p_txnHeaderID
2346 and mti2.transaction_source_type_id = 5
2347 and upper(nvl(flow_schedule, 'N')) = 'Y'
2348 and mti2.transaction_interface_id = mti.parent_id
2349 and mti2.transaction_type_id in (17, 44, 90, 91) -- Parent Transaction
2350 ) ;
2351
2352 /* Bug 5306902 - Parent WOLC resubmitted without all child component transactions. */
2353
2354 fnd_message.set_name('INV', 'INV_INT_PROCCODE');
2355 l_errCode := substrb(fnd_message.get, 1, 240);
2356
2357 fnd_message.set_name('WIP', 'WIP_PENDING_CHILD_TRANSACTION');
2358 l_errMsg := substrb(fnd_message.get, 1, 240);
2359
2360 update mtl_transactions_interface mti
2361 set last_update_date = sysdate,
2362 last_updated_by = fnd_global.user_id,
2363 last_update_login = fnd_global.login_id,
2364 program_application_id = fnd_global.prog_appl_id,
2365 program_id = fnd_global.conc_program_id,
2366 program_update_date = sysdate,
2367 request_id = fnd_global.conc_request_id,
2368 process_flag = 3,
2369 lock_flag = 2,
2370 error_code = l_errCode,
2371 error_explanation = l_errMsg
2372 where transaction_header_id = p_txnHeaderID
2373 and process_flag = 1
2374 and transaction_source_type_id = 5
2375 and upper(nvl(flow_schedule, 'N')) = 'Y'
2376 and exists (select 1
2377 from mtl_transactions_interface mti2
2378 where mti2.transaction_source_type_id = 5
2379 and upper(nvl(mti2.flow_schedule, 'N')) = 'Y'
2380 and mti2.parent_id = nvl(mti.parent_id,mti.transaction_interface_id)
2381 and mti2.process_flag = 3
2382 ) ;
2383
2384 -- derive inventory item id if transaction source id is provided
2385 update mtl_transactions_interface mti
2386 set last_update_date = sysdate,
2387 last_updated_by = fnd_global.user_id,
2388 last_update_login = fnd_global.login_id,
2389 program_application_id = fnd_global.prog_appl_id,
2390 program_id = fnd_global.conc_program_id,
2391 program_update_date = sysdate,
2392 request_id = fnd_global.conc_request_id,
2393 inventory_item_id = (select primary_item_id
2394 from wip_entities we
2395 where we.organization_id = mti.organization_id
2396 and we.wip_entity_id = mti.transaction_source_id)
2397 where transaction_header_id = p_txnHeaderID
2398 and transaction_source_type_id = 5
2399 and process_flag = 1
2400 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
2401 WIP_CONSTANTS.CPLASSY_ACTION,
2402 WIP_CONSTANTS.RETASSY_ACTION)
2403 and transaction_source_id is not null;
2404
2405
2406 if(l_logLevel <= wip_constants.full_logging) then
2407 wip_logger.log('about to fetch records for name -> item derivation', l_returnStatus);
2408 end if;
2409 --item id from name
2410 open c_itemTxns;
2411 fetch c_itemTxns
2412 bulk collect into l_orgIDTbl,
2413 l_rowidTbl,
2414 l_subCodeTbl,
2415 l_itemIDTbl;
2416 /* FP bug 5708701 (base bug 5046732) Need not collect data for l_locIDTbl l_locSegTbl
2417 l_locIDTbl,
2418 l_locSegTbl;
2419 */
2420 close c_itemTxns;
2421 --set up key flex package for flex -> id derivations
2422 if(l_orgIDTbl.count > 0) then
2423 fnd_flex_key_api.set_session_mode('seed_data');
2424 end if;
2425
2426 for i in 1..l_orgIDTbl.count loop
2427 if(l_itemIDTbl(i) is null) then
2428 if(l_logLevel <= wip_constants.full_logging) then
2429 wip_logger.log('about to derive item id from name', l_returnStatus);
2430 end if;
2431 if(not inv_txn_manager_grp.getitemid(x_itemID => l_itemIDTbl(i),
2432 p_orgID => l_orgIDTbl(i),
2433 p_rowid => l_rowidTbl(i))) then
2434 l_itemIDTbl(i) := null;--let inventory error out later
2435 if(l_logLevel <= wip_constants.full_logging) then
2436 wip_logger.log('item id from name derivation failed', l_returnStatus);
2437 end if;
2438 elsif(l_logLevel <= wip_constants.full_logging) then
2439 wip_logger.log('item id' || l_itemIDTbl(i), l_returnStatus);
2440 end if;
2441 end if;
2442
2443 /* FP bug 5708701 (base bug 5046732) - Removed the code to derive locator id. We do not require locator_id.
2444 if(l_locIDTbl(i) is null and l_locSegTbl(i) is not null) then
2445 select decode(mp.stock_locator_control_code,
2446 4, decode(sub.locator_type,
2447 5, it.location_control_code,
2448 sub.locator_type),
2449 mp.stock_locator_control_code)
2450 into l_locctrl
2451 from mtl_parameters mp,
2452 mtl_secondary_inventories sub,
2453 mtl_system_items it
2454 where it.inventory_item_id = l_itemIDTbl(i)
2455 and sub.secondary_inventory_name = l_subCodeTbl(i)
2456 and mp.organization_id = l_orgIDTbl(i)
2457 and it.organization_id = sub.organization_id
2458 and mp.organization_id = sub.organization_id
2459 and mp.organization_id = it.organization_id;
2460
2461 if(l_logLevel <= wip_constants.full_logging) then
2462 wip_logger.log('about to derive locator id from name', l_returnStatus);
2463 end if;
2464 if(not inv_txn_manager_grp.getlocid(x_locID => l_locIDTbl(i),
2465 p_org_id => l_orgIDTbl(i),
2466 p_subinv => l_subCodeTbl(i),
2467 p_rowid => l_rowidTbl(i),
2468 p_locCtrl => l_locCtrl)) then
2469 l_locIDTbl(i) := null; --let inventory error out later
2470 if(l_logLevel <= wip_constants.full_logging) then
2471 wip_logger.log('locator id from name derivation failed', l_returnStatus);
2472 end if;
2473 end if;
2474 end if;
2475 */
2476
2477 end loop;
2478 --now do a bulk update
2479 forall i in 1..l_orgIDTbl.count
2480 /* FP bug 5708701 (base bug 5046732) - No need update locator id on MTI. It will be done in inventory code.*/
2481 update mtl_transactions_interface mti
2482 set last_update_date = sysdate,
2483 last_updated_by = fnd_global.user_id,
2484 last_update_login = fnd_global.login_id,
2485 program_application_id = fnd_global.prog_appl_id,
2486 program_id = fnd_global.conc_program_id,
2487 program_update_date = sysdate,
2488 request_id = fnd_global.conc_request_id,
2489 inventory_item_id = l_itemIDTbl(i)
2490 --locator_id = l_locIdTbl(i)
2491 where rowid = chartorowid(l_rowidTbl(i));
2492
2493 /* FP bug 5708701 (base bug 5046732) No need update project id/task id on MTI. It will be done in inventory code.
2494 --update the project/task based on the derived locator
2495 update mtl_transactions_interface mti
2496 set last_update_date = sysdate,
2497 last_updated_by = fnd_global.user_id,
2498 last_update_login = fnd_global.login_id,
2499 program_application_id = fnd_global.prog_appl_id,
2500 program_id = fnd_global.conc_program_id,
2501 program_update_date = sysdate,
2502 request_id = fnd_global.conc_request_id,
2503 (project_id, task_id) = (select project_id, task_id
2504 from mtl_item_locations mil
2505 where inventory_location_id = mti.locator_id
2506 and organization_id = mti.organization_id)
2507 where transaction_header_id = p_txnHeaderID
2508 and transaction_source_type_id = 5
2509 and locator_id is not null
2510 and project_id is null
2511 and process_flag = wip_constants.mti_inventory;
2512 */
2513
2514 -- validate inventory item id
2515 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
2516 l_errCode := substrb(fnd_message.get, 1, 240);
2517 fnd_message.set_name('INV', 'INV_INT_ITMEXP');
2518 l_errMsg := substrb(fnd_message.get, 1, 240);
2519 update mtl_transactions_interface mti
2520 set last_update_date = sysdate,
2521 last_updated_by = fnd_global.user_id,
2522 last_update_login = fnd_global.login_id,
2523 program_application_id = fnd_global.prog_appl_id,
2524 program_id = fnd_global.conc_program_id,
2525 program_update_date = sysdate,
2526 request_id = fnd_global.conc_request_id,
2527 process_flag = 3,
2528 lock_flag = 2,
2529 error_code = l_errCode,
2530 error_explanation = l_errMsg
2531 where transaction_header_id = p_txnHeaderID
2532 and process_flag = 1
2533 and transaction_source_type_id = 5
2534 and not exists (select 'X'
2535 from mtl_system_items msi
2536 where msi.inventory_item_id = mti.inventory_item_id
2537 and msi.organization_id = mti.organization_id
2538 and msi.inventory_item_flag = 'Y');
2539
2540
2541 -- derive wip_entity_type if transaction source id is provided
2542 update mtl_transactions_interface mti
2543 set last_update_date = sysdate,
2544 last_updated_by = fnd_global.user_id,
2545 last_update_login = fnd_global.login_id,
2546 program_application_id = fnd_global.prog_appl_id,
2547 program_id = fnd_global.conc_program_id,
2548 program_update_date = sysdate,
2549 request_id = fnd_global.conc_request_id,
2550 wip_entity_type = (select entity_type
2551 from wip_entities we
2552 where we.organization_id = mti.organization_id
2553 and we.wip_entity_id = mti.transaction_source_id)
2554 where transaction_header_id = p_txnHeaderID
2555 and transaction_source_type_id = 5
2556 and process_flag = 1
2557 and transaction_source_id is not null;
2558
2559 --derive the source project id/task id for jobs
2560 update mtl_transactions_interface mti
2561 set last_update_date = sysdate,
2562 last_updated_by = fnd_global.user_id,
2563 last_update_login = fnd_global.login_id,
2564 program_application_id = fnd_global.prog_appl_id,
2565 program_id = fnd_global.conc_program_id,
2566 program_update_date = sysdate,
2567 request_id = fnd_global.conc_request_id,
2568 (source_project_id, source_task_id) =
2569 (select project_id,
2570 task_id
2571 from wip_discrete_jobs
2572 where wip_entity_id = mti.transaction_source_id
2573 and organization_id = mti.organization_id)
2574 where transaction_header_id = p_txnHeaderID
2575 and process_flag = 1
2576 and transaction_source_type_id = 5
2577 and wip_entity_type in (wip_constants.discrete,
2578 wip_constants.lotbased,
2579 wip_constants.eam);
2580
2581 --derive the source project id/task id for flow schedules
2582 update mtl_transactions_interface mti
2583 set last_update_date = sysdate,
2584 last_updated_by = fnd_global.user_id,
2585 last_update_login = fnd_global.login_id,
2586 program_application_id = fnd_global.prog_appl_id,
2587 program_id = fnd_global.conc_program_id,
2588 program_update_date = sysdate,
2589 request_id = fnd_global.conc_request_id,
2590 (source_project_id, source_task_id) =
2591 (select project_id,
2592 task_id
2593 from wip_flow_schedules
2594 where wip_entity_id = mti.transaction_source_id
2595 and organization_id = mti.organization_id)
2596 where transaction_header_id = p_txnHeaderID
2597 and process_flag = 1
2598 and transaction_source_type_id = 5
2599 and transaction_source_id is not null
2600 and wip_entity_type = wip_constants.flow;
2601
2602
2603
2604 -- make sure that flow_schedule flag is 'N' or NULL for job/repetitive and
2605 -- is Y for flow schedules
2606 fnd_message.set_name('WIP', 'WIP_FLOW_FLAG_ERROR');
2607 l_errMsg := substrb(fnd_message.get, 1, 240);
2608 update mtl_transactions_interface mti
2609 set last_update_date = sysdate,
2610 last_updated_by = fnd_global.user_id,
2611 last_update_login = fnd_global.login_id,
2612 program_application_id = fnd_global.prog_appl_id,
2613 program_id = fnd_global.conc_program_id,
2614 program_update_date = sysdate,
2615 request_id = fnd_global.conc_request_id,
2616 process_flag = 3,
2617 lock_flag = 2,
2618 error_code = 'Invalid flow schedule flag',
2619 error_explanation = l_errMsg
2620 where transaction_header_id = p_txnHeaderID
2621 and process_flag = 1
2622 and transaction_source_type_id = 5
2623 and ( ( wip_entity_type <> 4
2624 and upper(nvl(flow_schedule, 'N')) = 'Y')
2625 or ( wip_entity_type = 4
2626 and upper(nvl(flow_schedule, 'N')) <> 'Y') );
2627
2628 -- for flow schedule, we can't do complete/scrap against a closed schedule
2629 fnd_message.set_name('WIP', 'WIP_NO_CHARGES_ALLOWED');
2630 l_errMsg := substrb(fnd_message.get, 1, 240);
2631 update mtl_transactions_interface mti
2632 set last_update_date = sysdate,
2633 last_updated_by = fnd_global.user_id,
2634 last_update_login = fnd_global.login_id,
2635 program_application_id = fnd_global.prog_appl_id,
2636 program_id = fnd_global.conc_program_id,
2637 program_update_date = sysdate,
2638 request_id = fnd_global.conc_request_id,
2639 process_flag = 3,
2640 lock_flag = 2,
2641 error_code = 'transaction_source_id',
2642 error_explanation = l_errMsg
2643 where transaction_header_id = p_txnHeaderID
2644 and process_flag = 1
2645 and transaction_source_type_id = 5
2646 and wip_entity_type = 4
2647 and transaction_source_id is not null
2648 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
2649 WIP_CONSTANTS.CPLASSY_ACTION)
2650 and exists (select 1
2651 from wip_flow_schedules wfs
2652 where wfs.organization_id = mti.organization_id
2653 and wfs.wip_entity_id = mti.transaction_source_id
2654 and wfs.status = 2);
2655
2656 if (l_logLevel <= wip_constants.trace_logging) then
2657 wip_logger.exitPoint(p_procName => 'wip_mti_pub.doPreProcessingValidations',
2658 p_procReturnStatus => x_returnStatus,
2659 p_msg => 'normal completion',
2660 x_returnStatus => l_returnStatus);
2661 end if;
2662 exception
2663 when others then
2664 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2665 l_errMsg := substrb(SQLERRM, 1, 240);
2666 update mtl_transactions_interface
2667 set last_update_date = sysdate,
2668 last_updated_by = fnd_global.user_id,
2669 last_update_login = fnd_global.login_id,
2670 program_application_id = fnd_global.prog_appl_id,
2671 program_id = fnd_global.conc_program_id,
2672 program_update_date = sysdate,
2673 request_id = fnd_global.conc_request_id,
2674 process_flag = 3,
2675 lock_flag = 2,
2676 error_code = 'wip_mti_pub.doPreProcessingValidations',
2677 error_explanation = l_errMsg
2678 where transaction_header_id = p_txnHeaderID
2679 and transaction_source_type_id = 5
2680 and process_flag = wip_constants.mti_inventory;
2681
2682 if(l_logLevel <= wip_constants.trace_logging) then
2683 wip_logger.exitPoint(p_procName => 'wip_mti_pub.doPreProcessingValidations',
2684 p_procReturnStatus => x_returnStatus,
2685 p_msg => 'unexpected error: ' || SQLERRM,
2686 x_returnStatus => l_returnStatus); --discard logging return status
2687 end if;
2688 end doPreProcessingValidations;
2689
2690 procedure postInvWIPProcessing(p_txnHeaderID IN NUMBER,
2691 p_txnBatchID IN NUMBER,
2692 x_returnStatus OUT NOCOPY VARCHAR2) is
2693
2694 l_cplTxnIDTbl num_tbl_t;
2695 l_movTxnIDTbl num_tbl_t;
2696 l_errExplTbl big_char_tbl_t;
2697 l_itemIDTbl num_tbl_t;
2698 l_orgIDTbl num_tbl_t;
2699 l_itemNameTbl big_char_tbl_t;
2700 l_retStatus VARCHAR2(1);
2701 l_params wip_logger.param_tbl_t;
2702 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2703 begin
2704 x_returnStatus := fnd_api.g_ret_sts_success;
2705
2706 if(l_logLevel <= wip_constants.trace_logging) then
2707 l_params(1).paramName := 'p_txnHeaderID';
2708 l_params(1).paramValue := p_txnHeaderID;
2709 l_params(2).paramName := 'p_txnBatchID';
2710 l_params(2).paramValue := p_txnBatchID;
2711 wip_logger.entryPoint(p_procName => 'wip_mti_pub.postInvWipProcessing',
2712 p_params => l_params,
2713 x_returnStatus => l_retStatus);
2714 end if;
2715 --delete any pre-allocations that occurred for errored records
2716 delete wip_mtl_allocations_temp
2717 where transaction_temp_id in (select transaction_interface_id
2718 from mtl_transactions_interface
2719 where transaction_header_id = p_txnHeaderID
2720 and transaction_batch_id = p_txnBatchID
2721 and process_flag = wip_constants.mti_error);
2722
2723 if(l_logLevel <= wip_constants.full_logging) then
2724 wip_logger.log('deleted ' || SQL%ROWCOUNT || ' pre-allocations', l_retStatus);
2725 end if;
2726
2727 --fetch all errored components
2728 select mti.completion_transaction_id,
2729 mti.move_transaction_id,
2730 mti.error_explanation,
2731 mti.inventory_item_id,
2732 mti.organization_id,
2733 msik.concatenated_segments
2734 bulk collect into l_cplTxnIDTbl,
2735 l_movTxnIDTbl,
2736 l_errExplTbl,
2737 l_itemIDTbl,
2738 l_orgIDTbl,
2739 l_itemNameTbl
2740 from mtl_transactions_interface mti,
2741 mtl_system_items_kfv msik
2742 where mti.transaction_header_id = p_txnHeaderID
2743 and mti.transaction_batch_id = p_txnBatchID
2744 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
2745 wip_constants.issnegc_action, wip_constants.retnegc_action)
2746 and upper(nvl(mti.flow_schedule,'N')) <> 'Y'
2747 and ( mti.completion_transaction_id is not null
2748 or mti.move_transaction_id is not null)
2749 and mti.process_flag = wip_constants.mti_error
2750 and mti.error_explanation is not null --records that caused errors have err expl
2751 and mti.inventory_item_id = msik.inventory_item_id
2752 and mti.organization_id = msik.organization_id;
2753
2754 --delete all errored backflush components
2755 delete mtl_transactions_interface
2756 where transaction_header_id = p_txnHeaderID
2757 and transaction_batch_id = p_txnBatchID
2758 and transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
2759 wip_constants.issnegc_action, wip_constants.retnegc_action)
2760 and upper(nvl(flow_schedule,'N')) <> 'Y'
2761 and ( completion_transaction_id is not null
2762 or move_transaction_id is not null)
2763 and process_flag = wip_constants.mti_error;
2764
2765 if(l_logLevel <= wip_constants.full_logging) then
2766 wip_logger.log('deleted ' || SQL%ROWCOUNT || ' backflush components', l_retStatus);
2767 end if;
2768
2769 --if any components failed update the parent with the error
2770 forall i in 1..l_errExplTbl.count
2771 update mtl_transactions_interface mti
2772 set last_update_date = sysdate,
2773 last_updated_by = fnd_global.user_id,
2774 last_update_login = fnd_global.login_id,
2775 program_application_id = fnd_global.prog_appl_id,
2776 program_id = fnd_global.conc_program_id,
2777 program_update_date = sysdate,
2778 request_id = fnd_global.conc_request_id,
2779 error_code = substrb(l_itemNameTbl(i), 1, 240),
2780 error_explanation = l_errExplTbl(i)
2781 where transaction_header_id = p_txnHeaderID
2782 and transaction_batch_id = p_txnBatchID
2783 and transaction_source_type_id = 5
2784 and ( completion_transaction_id = l_cplTxnIDTbl(i)
2785 or move_transaction_id = l_movTxnIDTbl(i))
2786 and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
2787
2788 /*Bug 14802139: Calling below inventory API to release locks. this is an inventory request*/
2789 if(l_logLevel <= wip_constants.full_logging) then
2790 wip_logger.log('calling inventory api to release locks on MOQD', l_retStatus);
2791 end if;
2792 inv_table_lock_pvt.release_locks(p_header_id => p_txnHeaderID);
2793 if(l_logLevel <= wip_constants.full_logging) then
2794 wip_logger.log('done calling inventory api to release locks on MOQD', l_retStatus);
2795 end if;
2796
2797 if (l_logLevel <= wip_constants.trace_logging) then
2798 wip_logger.exitPoint(p_procName => 'wip_mti_pub.postInvWIPProcessing',
2799 p_procReturnStatus => x_returnStatus,
2800 p_msg => 'success',
2801 x_returnStatus => l_retStatus); --discard logging return status
2802 wip_logger.cleanup(l_retStatus);
2803 end if;
2804
2805 exception
2806 when others then
2807 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2808 if(l_logLevel <= wip_constants.trace_logging) then
2809 wip_logger.exitPoint(p_procName => 'wip_mti_pub.postInvWIPProcessing',
2810 p_procReturnStatus => x_returnStatus,
2811 p_msg => 'unexpected error: ' || SQLERRM,
2812 x_returnStatus => l_retStatus); --discard logging return status
2813 wip_logger.cleanup(l_retStatus);
2814 end if;
2815 end postInvWIPProcessing;
2816 end wip_mti_pub;