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