[Home] [Help]
PACKAGE BODY: APPS.WIP_BFLPROC_PRIV
Source
1 package body wip_bflProc_priv as
2 /* $Header: wipbflpb.pls 120.75.12020000.7 2013/02/18 09:48:36 akuppa ship $ */
3
4 ---------------
5 --private types
6 ---------------
7
8 type component_rec_t is record(itemID NUMBER,
9 itemName VARCHAR2(2000),
10 deptID NUMBER,
11 orgID NUMBER,
12 opSeqNum NUMBER,
13 countPointType NUMBER,
14 supplySub VARCHAR2(10),
15 supplyLocID NUMBER,
16 restrictSubsCode NUMBER,
17 restrictLocsCode NUMBER,
18 qtyPerAssy NUMBER,
19 /* begin LBM Project added new members */
20 requiredQty NUMBER,
21 qtyIssued NUMBER,
22 opAblyQtyCompleted NUMBER,
23 jobAblyQtyCompleted NUMBER,
24 jobAblyQtyScrapped NUMBER,
25 basisType NUMBER,
26 /* end LBM Project */
27 componentYieldFactor NUMBER, /* ER 4369064 */
28 priUomCode VARCHAR2(3),
29 lotControlCode NUMBER,
30 serialNumControlCode NUMBER,
31 revControlCode VARCHAR2(3),
32 projectID NUMBER,
33 taskID NUMBER,
34 srcProjectID NUMBER,
35 srcTaskID NUMBER,
36 itemDescription VARCHAR2(240),
37 locatorName VARCHAR2(2000),
38 revisionControlCode NUMBER,
39 locationControlCode NUMBER,
40 locatorProjectID NUMBER,
41 locatorTaskID NUMBER);
42
43
44 ----------------------
45 --forward declarations
46 ----------------------
47
48 function findTxnTypeID(p_txnActionID IN NUMBER) return NUMBER;
49
50 ---------------------------
51 --public/private procedures
52 ---------------------------
53
54 procedure processRequirements(p_wipEntityID IN NUMBER,
55 p_wipEntityType IN NUMBER,
56 p_repSchedID IN NUMBER := null,
57 p_repLineID IN NUMBER := null,
58 p_cplTxnID IN NUMBER := null,
59 p_movTxnID IN NUMBER := null,
60 p_batchID IN NUMBER := null,
61 p_orgID IN NUMBER,
62 p_assyQty IN NUMBER, --relative to wip (positive means pull material)
63 p_txnDate IN DATE,
64 p_wipSupplyType IN NUMBER,
65 p_txnHdrID IN NUMBER,
66 p_firstOp IN NUMBER,
67 p_lastOp IN NUMBER,
68 p_firstMoveOp IN NUMBER := null,
69 p_lastMoveOp IN NUMBER := null,
70 p_srcCode IN VARCHAR2 := null,
71 p_batchSeq IN NUMBER := null,
72 p_lockFlag IN NUMBER := null,
73 p_mergeMode IN VARCHAR2,
74 p_reasonID IN NUMBER := null,
75 p_reference IN VARCHAR2 := null,
76 p_initMsgList IN VARCHAR2,
77 p_endDebug IN VARCHAR2,
78 p_mtlTxnMode IN NUMBER,
79 x_compTbl IN OUT NOCOPY system.wip_component_tbl_t,
80 x_returnStatus OUT NOCOPY VARCHAR2) is
81 cursor c_populatedReqs IS
82 select mti.inventory_item_id itemID,
83 mti.transaction_interface_id txnIntID,
84 mti.transaction_action_id txnActionID,
85 mti.operation_seq_num opSeqNum,
86 -1 * mti.primary_quantity priQty, --make qty relative to wip (relative to inv in table)
87 -1 * mti.transaction_quantity txnQty, --make qty relative to wip (relative to inv in table)
88 msi.lot_control_code lotControlCode,
89 msi.serial_number_control_code serialNumControlCode
90 from mtl_transactions_interface mti,
91 mtl_system_items_b msi
92 where mti.transaction_header_id = p_txnHdrID
93 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
94 wip_constants.issnegc_action, wip_constants.retnegc_action)
95 and mti.operation_seq_num between p_firstOp and p_lastOp
96 and msi.inventory_item_id = mti.inventory_item_id
97 and msi.organization_id = mti.organization_id
98 and ( ( p_cplTxnID is null
99 and p_movTxnID is null)
100 or mti.move_transaction_id = p_movTxnID
101 or mti.completion_transaction_id = p_cplTxnID
102 )
103 /* group by mmtt.inventory_item_id,
104 mmtt.transaction_action_id,
105 mmtt.operation_seq_num,
106 msi.lot_control_code,
107 msi.serial_number_control_code
108 */ order by mti.inventory_item_id, mti.operation_seq_num;
109
110 cursor c_discReqs return component_rec_t IS
111 select wro.inventory_item_id,
112 msi.concatenated_segments,
113 wro.department_id,
114 wro.organization_id,
115 wro.operation_seq_num,
116 wo.count_point_type,
117 wro.supply_subinventory,
118 wro.supply_locator_id,
119 msi.restrict_subinventories_code,
120 msi.restrict_locators_code,
121 wro.quantity_per_assembly,
122 /* begin LBM Project */
123 wro.required_quantity,
124 wro.quantity_issued,
125 wo.quantity_completed,
126 wdj.quantity_completed,
127 wdj.quantity_scrapped,
128 wro.basis_type,
129 /* end LBM Project */
130 nvl(wro.component_yield_factor,1), /* ER 4369064 */
131 msi.primary_uom_code,
132 msi.lot_control_code,
133 msi.serial_number_control_code,
134 msi.revision_qty_control_code,
135 mil.segment19,
136 mil.segment20,
137 wdj.project_id,
138 wdj.task_id,
139 msi.description,
140 decode(mp.project_reference_enabled,
141 null,milk.concatenated_segments,
142 2,milk.concatenated_segments,
143 1, inv_project.get_pjm_locsegs(milk.concatenated_segments)),
144 msi.revision_qty_control_code,
145 msi.location_control_code,
146 mil.project_id,
147 mil.task_id
148 from wip_requirement_operations wro,
149 mtl_system_items_kfv msi,
150 wip_operations wo,
151 mtl_item_locations_kfv milk,
152 wip_discrete_jobs wdj,
153 mtl_parameters mp,
154 -- Fixed bug 4692413. We should not refer to column in kfv directly.
155 mtl_item_locations mil
156 where wro.inventory_item_id = msi.inventory_item_id
157 and wro.organization_id = msi.organization_id
158 and wro.organization_id = mp.organization_id
159 and wro.wip_entity_id = p_wipEntityID
160 and wro.wip_supply_type = p_wipSupplyType
161 and wro.quantity_per_assembly <> 0
162 and wro.operation_seq_num between p_firstOp and p_lastOp
163 and wro.wip_entity_id = wdj.wip_entity_id
164 and wro.organization_id = wdj.organization_id
165 and wro.wip_entity_id = wo.wip_entity_id (+)
166 and wro.operation_seq_num = wo.operation_seq_num (+)
167 /* added for OSFM jump enhancement 2541431 */
168 and nvl(wo.skip_flag, WIP_CONSTANTS.NO) <> WIP_CONSTANTS.YES
169 and wro.supply_locator_id = mil.inventory_location_id (+)
170 and wro.organization_id = mil.organization_id (+)
171 and wro.supply_locator_id = milk.inventory_location_id (+)
172 and wro.organization_id = milk.organization_id (+)
173 order by wro.inventory_item_id, wro.operation_seq_num;
174
175 cursor c_repReqs return component_rec_t IS
176 select wro.inventory_item_id,
177 msi.concatenated_segments,
178 wro.department_id,
179 wro.organization_id,
180 wro.operation_seq_num,
181 wo.count_point_type,
182 wro.supply_subinventory,
183 wro.supply_locator_id,
184 msi.restrict_subinventories_code,
185 msi.restrict_locators_code,
186 wro.quantity_per_assembly,
187 /* LBM Project */
188 wro.required_quantity,
189 wro.quantity_issued,
190 wo.quantity_completed,
191 wrs.quantity_completed,
192 0, -- quantity_scrapped
193 wro.basis_type,
194 /* LBM Project */
195 nvl(wro.component_yield_factor,1), /* ER 4369064 */
196 msi.primary_uom_code,
197 msi.lot_control_code,
198 msi.serial_number_control_code,
199 msi.revision_qty_control_code,
200 mil.segment19,
201 mil.segment20,
202 null,
203 null,
204 msi.description,
205 decode(mp.project_reference_enabled,
206 null,milk.concatenated_segments,
207 2,milk.concatenated_segments,
208 1, inv_project.get_pjm_locsegs(milk.concatenated_segments)),
209 msi.revision_qty_control_code,
210 msi.location_control_code,
211 mil.project_id,
212 mil.task_id
213 from wip_requirement_operations wro,
214 wip_repetitive_schedules wrs,
215 wip_repetitive_items wri,
216 mtl_system_items_kfv msi,
217 wip_operations wo,
218 mtl_item_locations_kfv milk,
219 mtl_parameters mp,
220 -- Fixed bug 4692413. We should not refer to column in kfv directly.
221 mtl_item_locations mil
222 where wro.wip_entity_id = p_wipEntityID
223 and wro.repetitive_schedule_id = p_repSchedID
224 and wro.wip_supply_type = p_wipSupplyType
225 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
226 and wro.quantity_per_assembly <> 0
227 and wro.operation_seq_num between p_firstOp and p_lastOp
228 and wri.wip_entity_id = wrs.wip_entity_id
229 and wri.line_id = wrs.line_id
230 and msi.inventory_item_id = wro.inventory_item_id /* Fix bug#4233474 */
231 and msi.organization_id = wro.organization_id /* Fix bug#4233474 */
232 and msi.organization_id = mp.organization_id
233 and wro.wip_entity_id = wo.wip_entity_id (+)
234 and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
235 and wro.operation_seq_num = wo.operation_seq_num (+)
236 and wro.supply_locator_id = mil.inventory_location_id (+)
237 and wro.organization_id = mil.organization_id (+)
238 and wro.supply_locator_id = milk.inventory_location_id (+)
239 and wro.organization_id = milk.organization_id (+)
240 order by wro.inventory_item_id, wro.operation_seq_num;
241
242 /* BUG 4712505 */
243 cursor c_checkExistingMTI(x_opSeqNum NUMBER, x_inventoryItemID NUMBER,
244 x_txnActionID NUMBER, x_txnTypeID NUMBER) IS
245 select 1
246 from mtl_transactions_interface mti
247 where mti.transaction_source_id = p_wipEntityID
248 and mti.organization_id = p_orgID
249 and mti.operation_seq_num = x_opSeqNum
250 and mti.inventory_item_id = x_inventoryItemID
251 and mti.transaction_action_id = x_txnActionID
252 and mti.transaction_type_id = x_txnTypeID;
253
254 cursor c_checkExistingMMTT(x_opSeqNum NUMBER, x_inventoryItemID NUMBER,
255 x_txnActionID NUMBER, x_txnTypeID NUMBER) IS
256 select 1
257 from mtl_material_transactions_temp mmtt
258 where mmtt.transaction_source_id = p_wipEntityID
259 and mmtt.organization_id = p_orgID
260 and mmtt.operation_seq_num = x_opSeqNum
261 and mmtt.inventory_item_id = x_inventoryItemID
262 and mmtt.transaction_action_id = x_txnActionID
263 and mmtt.transaction_type_id = x_txnTypeID;
264
265 l_reqRec component_rec_t;
266 l_popRec c_populatedReqs%ROWTYPE;
267 l_txnActionID NUMBER;
268 l_txnTypeID NUMBER;
269 l_compQty NUMBER;
270 l_openPastPeriod boolean := false;
271 l_acctPeriodID NUMBER;
272 l_deriveStatus VARCHAR2(1);
273 l_errMsg VARCHAR2(240);
274 l_params wip_logger.param_tbl_t;
275 l_returnStatus VARCHAR2(1);
276 l_extendCount NUMBER := 0;
277 l_index NUMBER;
278 l_revision VARCHAR2(3);
279 l_dummy NUMBER;
280 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
281 l_include_yield NUMBER; /* ER 4369064 */
282
283
284 /* Fix for 5160604/5004291 */
285 l_released_revs_type NUMBER ;
286 l_released_revs_meaning Varchar2(30);
287 l_created_by NUMBER ;/* Fix for #Bug 5444243 */
288 l_batch_id NUMBER := null; /*Fix bug 9968707 (FP of 9765009)*/
289 l_batch_seq NUMBER := null; /*Fix bug 9968707 (FP of 9765009)*/
290 begin
291 savepoint wipbflpb20;
292 if(fnd_api.to_boolean(p_initMsgList)) then
293 fnd_msg_pub.initialize;
294 end if;
295
296 if (l_logLevel <= wip_constants.trace_logging) then
297 l_params(1).paramName := 'p_wipEntityID';
298 l_params(1).paramValue := p_wipEntityID;
299 l_params(2).paramName := 'p_wipEntityType';
300 l_params(2).paramValue := p_wipEntityType;
301 l_params(3).paramName := 'p_repSchedID';
302 l_params(3).paramValue := p_repSchedID;
303 l_params(4).paramName := 'p_repLineID';
304 l_params(4).paramValue := p_repLineID;
305 l_params(5).paramName := 'p_cplTxnID';
306 l_params(5).paramValue := p_cplTxnID;
307 l_params(6).paramName := 'p_movTxnID';
308 l_params(6).paramValue := p_movTxnID;
309 l_params(7).paramName := 'p_batchID';
310 l_params(7).paramValue := p_batchID;
311 l_params(8).paramName := 'p_orgID';
312 l_params(8).paramValue := p_orgID;
313 l_params(9).paramName := 'p_assyQty';
314 l_params(9).paramValue := p_assyQty;
315 l_params(10).paramName := 'p_txnDate';
316 l_params(10).paramValue := to_char(p_txnDate, 'MM/DD/YYYY HH24:MI:SS');
317 l_params(11).paramName := 'p_wipSupplyType';
318 l_params(11).paramValue := p_wipSupplyType;
319 l_params(12).paramName := 'p_txnHdrID';
320 l_params(12).paramValue := p_txnHdrID;
321 l_params(13).paramName := 'p_firstOp';
322 l_params(13).paramValue := p_firstOp;
323 l_params(14).paramName := 'p_lastOp';
324 l_params(14).paramValue := p_lastOp;
325 l_params(15).paramName := 'p_firstMoveOp';
326 l_params(15).paramValue := p_firstMoveOp;
327 l_params(16).paramName := 'p_lastMoveOp';
328 l_params(16).paramValue := p_lastMoveOp;
329 l_params(17).paramName := 'p_batchSeq';
330 l_params(17).paramValue := p_batchSeq;
331 l_params(18).paramName := 'p_lockFlag';
332 l_params(18).paramValue := p_lockFlag;
333 l_params(19).paramName := 'p_mergeMode';
334 l_params(19).paramValue := p_mergeMode;
335 l_params(20).paramName := 'p_reasonID';
336 l_params(20).paramValue := p_reasonID;
337 l_params(21).paramName := 'p_reference';
338 l_params(21).paramValue := p_reference;
339 l_params(22).paramName := 'p_mtlTxnMode';
340 l_params(22).paramValue := p_mtlTxnMode;
341 wip_logger.entryPoint(p_procName => 'wip_bflProc_priv.processRequirements',
342 p_params => l_params,
343 x_returnStatus => x_returnStatus);
344 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
345 raise fnd_api.g_exc_unexpected_error;
346 end if;
347 end if;
348
349 /* Fix for bug # 5160604/5004291:
350 Determine whether to backflush unimplemented revisions */
351 wip_common.Get_Released_Revs_Type_Meaning (l_released_revs_type,
352 l_released_revs_meaning
353 );
354
355 if(p_wipEntityType = wip_constants.repetitive) then
356 if (l_logLevel <= wip_constants.full_logging) then
357 wip_logger.log('processing repetitive...',l_returnStatus);
358 end if;
359 --select the highest operation where the backflush flag is set
360 open c_repReqs;
361 else
362 if (l_logLevel <= wip_constants.full_logging) then
363 wip_logger.log('processing discrete...',l_returnStatus);
364 end if;
365 open c_discReqs;
366 end if;
367 --no routing
368
369 open c_populatedReqs;
370 invttmtx.tdatechk(org_id => p_orgID,
371 transaction_date => p_txnDate,
372 period_id => l_acctPeriodID,
373 open_past_period => l_openPastPeriod);
374
375 if(l_acctPeriodID is null or
376 l_acctPeriodID <= 0) then
377 fnd_message.set_name('INV', 'INV_NO_OPEN_PERIOD');
378 fnd_msg_pub.add;
379 l_errMsg := 'acct period id could not be derived.';
380 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
381 raise fnd_api.g_exc_unexpected_error;
382 end if;
383
384 if(x_compTbl is not null) then
385 l_index := nvl(x_compTbl.last, 0) + 1;
386 x_compTbl.extend(g_compTblExtendSize);
387 end if;
388
389 /*Fix bug 9968707 (FP of 9765009)*/
390 if(p_movTxnID is null) then
391 l_batch_id := p_batchID;
392 l_batch_seq := nvl(p_batchSeq,wip_constants.component_batch_seq);
393 end if;
394
395 loop
396 if(p_repSchedID is not null) then
397 fetch c_repReqs into l_reqRec;
398 exit when c_repReqs%NOTFOUND;
399 else
400 fetch c_discReqs into l_reqRec;
401 exit when c_discReqs%NOTFOUND;
402 end if;
403 exit when l_reqRec.opSeqNum > p_lastOp;
404
405 if(p_assyQty > 0) then --completion, forward move
406 if(l_reqRec.qtyPerAssy > 0) then
407 l_txnActionID := wip_constants.isscomp_action;
408 else
409 l_txnActionID := wip_constants.issnegc_action;
410 end if;
411 else --return, backward move
412 if(l_reqRec.qtyPerAssy > 0) then
413 l_txnActionID := wip_constants.retcomp_action;
414 else
415 l_txnActionID := wip_constants.retnegc_action;
416 end if;
417 end if;
418
419 --this loop tries to find an existing record for the backflush component. It exits when it finds one (= condition) or
420 --can't find one (gets to end of cursor or itemID > the current requirement's itemID)
421 loop
422 exit when not c_populatedReqs%ISOPEN;
423 exit when l_popRec.opSeqNum > l_reqRec.opSeqNum and l_popRec.itemID > l_reqRec.itemID; --still haven't found a txn
424 exit when l_popRec.txnActionID = l_txnActionID
425 and l_popRec.itemID = l_reqRec.itemID
426 and l_popRec.opSeqNum = l_reqRec.opSeqNum;
427
428 fetch c_populatedReqs into l_popRec;
429 if(c_populatedReqs%NOTFOUND) then
430 close c_populatedReqs;
431 l_popRec.itemID := null; --indicate no item exists
432 exit;
433 end if;
434 end loop;
435
436 --if this isn't an autocharge operation and the operation seq num isn't the one being moved into, then
437 --skip inserting requirements.
438 --This is the only check we have to do since operations w/assy pull requirements must be autocharge and
439 --moves will always pass p_firstMoveOp and p_lastMoveOp
440 if(l_reqRec.countPointType = wip_constants.no_direct and
441 l_reqRec.opSeqNum not in (p_firstMoveOp, p_lastMoveOp)) then
442 goto end_of_loop;
443 end if;
444
445 /* begin LBM Project code here to set the component quantity */
446 --set the component quantity
447 l_compQty := 0;
448
449 /* Bug 4712535 -- need to check for include component yield flag even for lot based items */
450 select nvl(include_component_yield,1)
451 into l_include_yield
452 from wip_parameters
453 where organization_id = p_orgID;
454
455
456 if (l_reqRec.basisType = WIP_CONSTANTS.LOT_BASED_MTL) then
457
458 -- forward move
459
460 if (l_reqRec.qtyIssued = 0 and p_assyQty > 0) then
461
462 /* Bug 4712535 */
463 if (l_include_yield = 1) then
464 -- backflush the entire quantity stored in wro, since it is calculated based on
465 -- comp yield at job component definition time
466
467 l_compQty := round(l_reqRec.qtyPerAssy / NVL(l_reqRec.componentYieldFactor,1),
468 wip_constants.inv_max_precision);
469 else
470 -- otherwise just backflush the qty per assembly, not including the comp yield */
471 l_compQty := round(l_reqRec.qtyPerAssy, wip_constants.inv_max_precision);
472 end if;
473
474 -- backward move
475
476 elsif (p_assyQty < 0) then
477 --bug 5285593 The following condition works only for online txns since for background qty completed in WO
478 --has already been updated to 0 before reaching here. Hence changing the conditions so that it will work for both
479 --online and background cases
480
481 /*
482 if (p_wipSupplyType = WIP_CONSTANTS.OP_PULL and (l_reqRec.opAblyQtyCompleted + p_assyQty) = 0) or
483 (p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL and
484 (l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty) = 0) then
485 */
486
487 -- bug 5524972 rewritten the following if clause. made logic for assembly pull components similar to
488 -- that for operation pull
489
490 /*
491
492 if (((p_wipSupplyType = WIP_CONSTANTS.OP_PULL)
493 and (LEAST(l_reqRec.opAblyQtyCompleted, ABS(l_reqRec.opAblyQtyCompleted + p_assyQty)) = 0)) or
494 ((p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL)
495 and (LEAST(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped,
496 ABS(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty)) = 0)))
497 */
498
499 if (((p_wipSupplyType = WIP_CONSTANTS.OP_PULL) or (p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL))
500 and (LEAST(l_reqRec.opAblyQtyCompleted, ABS(l_reqRec.opAblyQtyCompleted + p_assyQty)) = 0))
501 then
502
503 /* Bug 4712535 */
504
505 if (l_include_yield = 1) then
506 l_compQty := - round(l_reqRec.qtyPerAssy / NVL(l_reqRec.componentYieldFactor,1),
507 wip_constants.inv_max_precision);
508 else
509 l_compQty := - round(l_reqRec.qtyPerAssy, wip_constants.inv_max_precision);
510 end if;
511
512 end if;
513
514 end if;
515
516 /* below code changed due to comp yield project. */
517
518 else
519
520 /* ER 4369064: Component quantity will depend on yield factor, if the parameter is set */
521 /*select include_component_yield
522 into l_include_yield
523 from wip_parameters
524 where organization_id = p_orgID; -- moved above */
525
526 if (l_include_yield = 1) then
527 l_compQty := round(l_reqRec.qtyPerAssy * p_assyQty/NVL(l_reqRec.componentYieldFactor,1),
528 wip_constants.inv_max_precision);
529 else
530 l_compQty := round(l_reqRec.qtyPerAssy * p_assyQty,
531 wip_constants.inv_max_precision);
532 end if;
533
534 end if;
535
536 /* end LBM Project code here to set the component quantity */
537
538
539
540 --this if gets executed, say, if the Q/A is something like 1 X 10E-100 or something. it's not in the
541 --cursor where clause b/c we actually need to multiply by txn qty since if the assy qty is large, the required
542 --quantity for the component could be large enough to transact. in the above case, let's say the assy qty was
543 --1 X 10E100. The required qty for the component would be 1 and thus we would populate the requirement.
544 if(l_compQty = 0) then
545 goto end_of_loop;
546 end if;
547
548
549 if(l_popRec.itemID = l_reqRec.itemID) then
550 if (l_logLevel <= wip_constants.full_logging) then
551 wip_logger.log('found existing requirement',l_returnStatus);
552 end if;
553 if(fnd_api.to_boolean(p_mergeMode)) then
554 --user wants to merge requirements. Update the txn qty
555 update mtl_transactions_interface
556 set last_update_date = sysdate,
557 last_updated_by = fnd_global.user_id,
558 last_update_login = fnd_global.login_id,
559 request_id = fnd_global.conc_request_id,
560 program_application_id = fnd_global.prog_appl_id,
561 program_id = fnd_global.conc_program_id,
562 program_update_date = sysdate,
563 transaction_quantity = transaction_quantity - l_compQty, --subtract b/c l_compQty is relative to WIP
564 primary_quantity = primary_quantity - l_compQty --subtract b/c l_compQty is relative to WIP
565 where transaction_interface_id = l_popRec.txnIntID;
566 end if;
567 --in either mode, skip the MTI insert.
568 goto end_of_loop;
569 end if;
570
571 l_txnTypeID := findTxnTypeID(p_txnActionID => l_txnActionID);
572
573 /* Fix for bug# 5160604/5004291:
574 Added eco_status parameter to ensure that the revision being
575 backflushed will be determind by the profile WIP_RELEASED_REVS */
576
577 if(l_reqRec.revControlCode = wip_constants.revision_controlled) then
578 bom_revisions.get_revision(examine_type => 'ALL',
579 eco_status => l_released_revs_meaning,
580 org_id => p_orgID,
581 item_id => l_reqRec.itemID,
582 rev_date => p_txnDate,
583 itm_rev => l_revision);
584 else
585 l_revision := null;
586 end if;
587
588 /* Bug 4712505 */
589 -- For lot based components we need to check if there are existing records in MTI or MMTT.
590 -- If there are existing records then we don't want to backflush again, since the quantity
591 -- should only be backflushed once.
592 if (l_reqRec.basisType = WIP_CONSTANTS.LOT_BASED_MTL) then
593 open c_checkExistingMTI(l_reqRec.opSeqNum, l_reqRec.itemID, l_txnActionID, l_txnTypeID);
594 fetch c_checkExistingMTI into l_dummy;
595 -- Fixed bug 4755034. This is a regression from 4712505 bug fix.
596 -- Cursor should be close no matter pending record found or not.
597 if c_checkExistingMTI%found then
598 close c_checkExistingMTI;
599 goto end_of_loop;
600 else
601 close c_checkExistingMTI;
602 end if;
603
604 open c_checkExistingMMTT(l_reqRec.opSeqNum, l_reqRec.itemID, l_txnActionID, l_txnTypeID);
605 fetch c_checkExistingMMTT into l_dummy;
606 -- Fixed bug 4755034. This is a regression from 4712505 bug fix.
607 -- Cursor should be close no matter pending record found or not.
608 if c_checkExistingMMTT%found then
609 close c_checkExistingMMTT;
610 goto end_of_loop;
611 else
612 close c_checkExistingMMTT;
613 end if;
614 end if;
615
616
617 if (l_logLevel <= wip_constants.full_logging) then
618 wip_logger.log('inserting item:' || l_reqRec.itemID || ' opSeq:' || l_reqRec.opSeqNum || ' qty:' || l_compQty * -1,l_returnStatus);
619 wip_logger.log('txnAction:' || l_txnActionID || ' txnType:' || l_txnTypeID,l_returnStatus);
620 end if;
621 if(x_compTbl is null) then
622
623 /* Fix for Bug 5444243 */
624 Begin
625 SELECT created_by
626 INTO l_created_by
627 FROM wip_move_transactions
628 WHERE TRANSACTION_ID = p_movTxnID;
629 exception when others then
630 l_created_by:= fnd_global.user_id;
631 end;
632 /* End of fix for Bug 5444243 */
633
634 insert into mtl_transactions_interface
635 (last_update_date,
636 last_updated_by,
637 creation_date,
638 created_by,
639 last_update_login,
640 request_id,
641 program_application_id,
642 program_id,
643 program_update_date,
644 transaction_header_id,
645 transaction_interface_id,
646 transaction_source_id,
647 transaction_source_type_id,
648 transaction_type_id,
649 transaction_action_id,
650 transaction_date,
651 transaction_quantity,
652 transaction_uom,
653 primary_quantity,
654 wip_supply_type,
655 wip_entity_type,
656 inventory_item_id,
657 revision,
658 operation_seq_num,
659 department_id,
660 organization_id,
661 process_flag,
662 -- posting_flag,
663 subinventory_code,
664 locator_id,
665 acct_period_id,
666 completion_transaction_id,
667 move_transaction_id,
668 repetitive_line_id,
669 negative_req_flag,
670 -- item_serial_control_code,
671 -- item_lot_control_code,
672 source_code,
673 source_header_id,
674 source_line_id,
675 project_id,
676 task_id,
677 source_project_id,
678 source_task_id,
679 transaction_mode,
680 transaction_batch_id, /*Fix bug 9968707 (FP of 9765009)*/
681 transaction_batch_seq, /*Fix bug 9968707 (FP of 9765009)*/
682 lock_flag,
683 reason_id,
684 transaction_reference)
685 values
686 (sysdate,
687 fnd_global.user_id,
688 sysdate,
689 l_created_by,/* Fix for Bug 5444243 */
690 fnd_global.login_id,
691 fnd_global.conc_request_id,
692 fnd_global.prog_appl_id,
693 fnd_global.conc_program_id,
694 sysdate,
695 p_txnHdrID,
696 mtl_material_transactions_s.nextval,
697 p_wipEntityID,
698 5,
699 l_txnTypeID,
700 l_txnActionID,
701 p_txnDate,
702 -1 * l_compQty, --make quantity relative to inventory
703 l_reqRec.priUomCode,
704 -1 * l_compQty,
705 p_wipSupplyType,
706 p_wipEntityType,
707 l_reqRec.itemID,
708 l_revision,
709 l_reqRec.opSeqNum,
710 l_reqRec.deptID,
711 l_reqRec.orgID,
712 wip_constants.mti_inventory,
713 -- 'Y',
714 l_reqRec.supplySub,
715 l_reqRec.supplyLocID,
716 l_acctPeriodID,
717 p_cplTxnID,
718 p_movTxnID,
719 p_repLineID,
720 decode(l_txnActionID,
721 wip_constants.isscomp_action, 1,
722 wip_constants.retcomp_action, 1,
723 wip_constants.issnegc_action, -1,
724 wip_constants.retnegc_action, -1),
725 -- l_reqRec.serialNumControlCode,
726 -- l_reqRec.lotControlCode,
727 nvl(p_srcCode, 'WIP Backflush'),
728 p_wipEntityID,
729 l_reqRec.opSeqNum,
730 l_reqRec.projectID,
731 l_reqRec.taskID,
732 l_reqRec.srcProjectID,
733 l_reqRec.srcTaskID,
734 p_mtlTxnMode,
735 l_batch_id, /*Fix bug 9968707 (FP of 9765009),modified the fix for bug 8663842 because it causes bad data corruption */
736 l_batch_seq, /*Fix bug 9968707 (FP of 9765009),modified the fix for bug 8663842 because it causes bad data corruption */
737 p_lockFlag,
738 p_reasonID,
739 p_reference);
740 --must be after completion
741 else
742 if(floor(l_extendCount/g_compTblExtendSize) = 1) then
743 x_compTbl.extend(g_compTblExtendSize);
744 l_extendCount := 0;
745 end if;
746 if (l_logLevel <= wip_constants.full_logging) then
747 wip_logger.log('serial control code is ' || l_reqRec.serialNumControlCode,l_returnStatus);
748 wip_logger.log('lot control code is ' || l_reqRec.lotControlCode,l_returnStatus);
749 end if;
750
751 --pass movTxnID as the move needs to distinguish between the mtl requirements for the child
752 --move transaction vs. the parent move transaction in the over move case. Over-completions do
753 --not need this as we can just use supply type to distinguish between the move and completion
754 --requirements
755 x_compTbl(l_index) := system.wip_component_obj_t(
756 operation_seq_num => l_reqRec.opSeqNum,
757 inventory_item_id => l_reqRec.itemID,
758 item_name => l_reqRec.itemName,
759 primary_quantity => l_compQty,
760 primary_uom_code => l_reqRec.priUomCode,
761 supply_subinventory => l_reqRec.supplySub,
762 supply_locator_id => l_reqRec.supplyLocID,
763 wip_supply_type => p_wipSupplyType,
764 transaction_action_id => l_txnActionID,
765 --don't populate txns enabled flag
766 mtl_transactions_enabled_flag => null,
767 serial_number_control_code => l_reqRec.serialNumControlCode,
768 lot_control_code => l_reqRec.lotControlCode,
769 revision => l_revision,
770 first_lot_index => null,
771 last_lot_index => null,
772 first_serial_index => null,
773 last_serial_index => null,
774 generic_id => p_movTxnID,
775 department_id => l_reqRec.deptID,
776 restrict_subinventories_code => l_reqRec.restrictSubsCode,
777 restrict_locators_code => l_reqRec.restrictLocsCode,
778 --FP of Bug11690909 The following Project and Task ID once returned to Java Code are used as
779 --SourceProject/TaskID for Components, thus need to use the corresponding source ids.
780 project_id => l_reqRec.srcProjectID,
781 task_id => l_reqRec.srcTaskID,
782 component_sequence_id => null,
783 completion_transaction_id => p_cplTxnID,
784 item_description => l_reqRec.itemDescription,
785 locator_name => l_reqRec.locatorName,
786 revision_qty_control_code => l_reqRec.revisionControlCode,
787 location_control_code => l_reqRec.locationControlCode,
788 component_yield_factor => null,/*Component Yield Enhancement(Bug 4369064)->wip_component_obj_t structure has been changed,
789 its value assigened to null to compile the structure..*/
790 basis_type => l_reqRec.basisType,
791 locator_project_id => l_reqRec.locatorProjectID,
792 locator_task_id => l_reqRec.locatorTaskID
793 );
794 l_index := l_index + 1;
795 l_extendCount := l_extendCount + 1;
796 end if;
797
798 <<end_of_loop>>
799 null;
800 end loop;
801 if(x_compTbl is not null) then
802 --trim any trailing null entries
803 x_compTbl.trim(g_compTblExtendSize - l_extendCount);
804 end if;
805
806 if(c_populatedReqs%ISOPEN) then
807 close c_populatedReqs;
808 end if;
809
810 if(c_discReqs%ISOPEN) then
811 close c_discReqs;
812 end if;
813 if(c_repReqs%ISOPEN) then
814 close c_repReqs;
815 end if;
816
817 x_returnStatus := fnd_api.g_ret_sts_success;
818 if (l_logLevel <= wip_constants.trace_logging) then
819 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.processRequirements',
820 p_procReturnStatus => x_returnStatus,
821 p_msg => 'procedure succeeded',
822 x_returnStatus => l_returnStatus); --discard logging return status
823 end if;
824
825 if(fnd_api.to_boolean(p_endDebug)) then
826 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
827 end if;
828 exception
829 when fnd_api.g_exc_unexpected_error then
830 rollback to wipbflpb20;
831 if (l_logLevel <= wip_constants.trace_logging) then
832 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.processRequirements',
833 p_procReturnStatus => x_returnStatus,
834 p_msg => l_errMsg,
835 x_returnStatus => l_returnStatus); --discard logging return status
836 end if;
837
838 if(fnd_api.to_boolean(p_endDebug)) then
839 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
840 end if;
841 when others then
842 rollback to wipbflpb20;
843 x_returnStatus := fnd_api.g_ret_sts_unexp_error;--unexpec error if exception occurs
844 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_bflProc_priv',
845 p_procedure_name => 'processRequirements',
846 p_error_text => SQLERRM);
847 if (l_logLevel <= wip_constants.trace_logging) then
848 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.processRequirements',
849 p_procReturnStatus => x_returnStatus,
850 p_msg => 'unexpected error: ' || SQLERRM,
851 x_returnStatus => l_returnStatus); --discard logging return status
852 end if;
853
854 if(fnd_api.to_boolean(p_endDebug)) then
855 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
856 end if;
857 end processRequirements;
858
859
860 procedure explodeRequirements(p_itemID IN NUMBER,
861 p_orgID IN NUMBER,
862 p_qty IN NUMBER,
863 p_altBomDesig IN VARCHAR2,
864 p_altOption IN NUMBER,
865 /* Fix for bug#3423629 */ p_bomRevDate IN DATE DEFAULT NULL,
866 p_txnDate IN DATE,
867 /* Fix for bug 5383135 */ p_implFlag IN NUMBER,
868 p_projectID IN NUMBER,
869 p_taskID IN NUMBER,
870 /* added for bug 5332615 */ p_unitNumber in varchar2 DEFAULT '',
871 p_initMsgList IN VARCHAR2,
872 p_endDebug IN VARCHAR2,
873 x_compTbl OUT NOCOPY system.wip_component_tbl_t,
874 x_returnStatus OUT NOCOPY VARCHAR2) is
875 l_grpID NUMBER; --bom table identifier
876 l_cmnBillID NUMBER;
877 l_cmnOrgID NUMBER;
878 l_isPhantom boolean := false;
879 l_phantomRowID rowid;
880 l_opSeqNum NUMBER;
881 l_qtyMultiplier NUMBER := 1;
882 l_index NUMBER := 1;
883 l_params wip_logger.param_tbl_t;
884 l_errMsg VARCHAR2(240);
885 l_returnStatus VARCHAR2(1);
886 l_errCode NUMBER;
887 l_inheritPhOpSeq NUMBER;
888 l_bomItemType NUMBER;
889 l_maxBomLevel NUMBER;
890 l_extendCount NUMBER := 0;
891 l_bomLevel NUMBER;
892 l_dummy NUMBER;
893 l_msgData VARCHAR2(8000);
894 l_msgCount NUMBER;
895 l_locatorControl NUMBER;
896 l_locID NUMBER;
897 l_revision VARCHAR2(3);
898 l_txnActionID NUMBER;
899 l_success boolean;
900 l_projectID NUMBER;
901 l_taskID NUMBER;
902 l_bom_or_eng_flag NUMBER := 1;
903 l_released_revs_type NUMBER ;
904 l_released_revs_meaning Varchar2(30);
905 l_componentSeqID NUMBER;
906
907 cursor c_components(v_cmnOrgID NUMBER, v_orgID NUMBER, v_grpID NUMBER) is
908 select be.operation_seq_num opSeqNum,
909 be.component_item_id itemID,
910 be.component_quantity priQty,
911 be.component_yield_factor compYield,
912 msi.primary_uom_code priUomCode,
913 msi.mtl_transactions_enabled_flag txnsEnabledFlag,
914 be.sort_order, --Bug#13455457: To update the correct explosion
915 decode(msi.shrinkage_rate, 1, 0, null, 0, msi.shrinkage_rate) shrinkageRate,
916 /*decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_subinventory, null) supplySubinv,Fix bug 7609139 (FP bug 7415520)*/
917 /*decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_locator_id, null) supplyLocID,Fix bug 7609139 (FP bug 7415520)*/
918 decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_subinventory, decode(be.common_organization_id, v_orgID, bic.supply_subinventory, null )) supplySubinv,/*Bug 12972794*/
919 decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_locator_id, decode(be.common_organization_id, v_orgID, bic.supply_locator_id, null )) supplyLocID,/*Bug 12972794*/
920 nvl(bic.wip_supply_type , msi.wip_supply_type) wipSupplyType, /* 2695355 */
921 be.component_code compCode,
922 be.rowid beRowID,
923 be.plan_level bomLevel, --level of nesting. 1 is a top level component
924 bic.basis_type /* LBM Project */
925 from bom_explosion_temp be,
926 bom_inventory_components bic,
927 mtl_system_items msi
928 where be.group_id = v_grpID
929 and be.component_sequence_id = bic.component_sequence_id
930 and be.component_item_id = msi.inventory_item_id
931 and be.component_item_id <> p_itemID --exclude assy if it is in the table
932 and msi.bom_item_type not in (wip_constants.model_type,
933 wip_constants.option_class_type) /* Fix for 4575119 */
934 and msi.organization_id = v_orgID
935 order by be.component_code;
936
937 cursor c_groupedComponents(v_cmnOrgID NUMBER, v_orgID NUMBER, v_grpID NUMBER) is
938 select be.operation_seq_num opSeqNum,
939 msi.concatenated_segments itemName,
940 be.component_item_id itemID,
941 sum(be.component_quantity) priQty,/*For Component Yield Enhancement(Bug 4369064)->Removed yield consideration */
942 msi.primary_uom_code priUomCode,
943 msi.restrict_subinventories_code restrictSubs,
944 msi.restrict_locators_code restrictLocs,
945 decode(msi.shrinkage_rate, 1, 0, null, 0, msi.shrinkage_rate) shrinkageRate,
946 /*decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_subinventory, null) supplySubinv,Fix bug 7609139 (FP bug 7415520)*/
947 /*decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_locator_id, null) supplyLocID,Fix bug 7609139 (FP bug 7415520)*/
948 decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_subinventory, decode(be.common_organization_id, v_orgID, bic.supply_subinventory, null )) supplySubinv,/*Bug 12972794*/
949 decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_locator_id, decode(be.common_organization_id, v_orgID, bic.supply_locator_id, null )) supplyLocID,/*Bug 12972794*/
950 --bic.component_sequence_id componentSeqID, /*Fix bug 11904037*/
951 nvl(bic.wip_supply_type , msi.wip_supply_type) wipSupplyType, /* 2695355 */
952 msi.mtl_transactions_enabled_flag txnsEnabledFlag,
953 msi.revision_qty_control_code revControlCode,
954 msi.serial_number_control_code serialNumControlCode,
955 msi.lot_control_code lotControlCode,
956 msi.end_assembly_pegging_flag pegFlag,
957 be.component_yield_factor compYield, /*For Component Yield Enhancement(Bug 4369064) */
958 bic.basis_type, /* LBM Project */
959 /* Add more item for flow OA project */
960 msi.description itemDesc,
961 msi.location_control_code locControlCode,
962 decode(mp.project_reference_enabled,
963 null,milk.concatenated_segments,
964 2,milk.concatenated_segments,
965 1, inv_project.get_pjm_locsegs(milk.concatenated_segments)) locatorName
966 from bom_explosion_temp be,
967 bom_inventory_components bic,
968 mtl_system_items_kfv msi,
969 mtl_item_locations_kfv milk,
970 mtl_parameters mp
971 where be.group_id = v_grpID
972 and be.component_sequence_id = bic.component_sequence_id
973 and be.component_item_id = msi.inventory_item_id
974 and be.component_item_id <> p_itemID --exclude assy if it is in the table
975 and msi.bom_item_type not in (wip_constants.model_type,
976 wip_constants.option_class_type) /* Fix for 4575119 */
977 and msi.organization_id = v_orgID
978 and msi.organization_id = mp.organization_id
979 and bic.supply_locator_id = milk.inventory_location_id(+)
980 and milk.organization_id(+) = v_orgid /*Bug#14735094:Added the condition for orgid as same locator_id can be present for different orgs */
981 group by be.operation_seq_num,
982 msi.concatenated_segments,
983 be.component_item_id,
984 msi.primary_uom_code,
985 msi.restrict_subinventories_code,
986 msi.restrict_locators_code,
987 decode(msi.shrinkage_rate, 1, 0, null, 0, msi.shrinkage_rate),
988 /*decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_subinventory, null),Fix bug 7609139 (FP bug 7415520)*/
989 /*decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_locator_id, null),Fix bug 7609139 (FP bug 7415520)*/
990 decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_subinventory, decode(be.common_organization_id, v_orgID, bic.supply_subinventory, null )),/*Bug 12972794*/
991 decode(be.common_bill_sequence_id, be.bill_sequence_id, bic.supply_locator_id, decode(be.common_organization_id, v_orgID, bic.supply_locator_id, null )),/*Bug 12972794*/
992 -- bic.component_sequence_id, /*Fix bug 11904037*/
993 nvl(bic.wip_supply_type, msi.wip_supply_type),
994 msi.mtl_transactions_enabled_flag,
995 msi.revision_qty_control_code,
996 msi.serial_number_control_code,
997 msi.lot_control_code,
998 msi.end_assembly_pegging_flag,
999 be.component_yield_factor,
1000 bic.basis_type, /* LBM Project */
1001 msi.description,
1002 msi.location_control_code,
1003 decode(mp.project_reference_enabled,
1004 null,milk.concatenated_segments,
1005 2,milk.concatenated_segments,
1006 1, inv_project.get_pjm_locsegs(milk.concatenated_segments))
1007 order by msi.serial_number_control_code desc,msi.lot_control_code desc,be.component_item_id;--be.operation_seq_num, msi.concatenated_segments;
1008
1009 l_compRec c_components%ROWTYPE;
1010 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
1011
1012 multiple_factor number ; /* LBM Project */
1013
1014 begin
1015 savepoint wipbflpb30;
1016
1017 if(fnd_api.to_boolean(p_initMsgList)) then
1018 fnd_msg_pub.initialize;
1019 end if;
1020
1021 if (l_logLevel <= wip_constants.trace_logging) then
1022 l_params(1).paramName := 'p_itemID';
1023 l_params(1).paramValue := p_itemID;
1024 l_params(2).paramName := 'p_orgID';
1025 l_params(2).paramValue := p_orgID;
1026 l_params(3).paramName := 'p_altBomDesig';
1027 l_params(3).paramValue := p_altBomDesig;
1028 l_params(4).paramName := 'p_altOption';
1029 l_params(4).paramValue := p_altOption;
1030 l_params(5).paramName := 'p_txnDate';
1031 l_params(5).paramValue := to_char(p_txnDate, 'MM/DD/YYYY HH24:MI:SS');
1032 l_params(6).paramName := 'p_qty';
1033 l_params(6).paramValue := p_qty;
1034 l_params(7).paramName := 'p_bomRevDate';
1035 l_params(7).paramValue := to_char(p_bomRevDate,'MM/DD/YYYY HH24:MI:SS');
1036 l_params(8).paramName := 'p_implFlag';
1037 l_params(8).paramValue := p_implFlag;
1038
1039
1040 wip_logger.entryPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1041 p_params => l_params,
1042 x_returnStatus => x_returnStatus);
1043 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1044 raise fnd_api.g_exc_unexpected_error;
1045 end if;
1046 end if;
1047
1048 begin
1049 --get the common bill info.
1050 if(p_altOption = 2) then
1051
1052 -- FP Bug 4643335 : Rewrote the query by adding a subquery to improve performance.
1053 --FP Bug 6502612 : Added Check for Engineering BOMs .
1054 select a.organization_id, a.bill_sequence_id
1055 into l_cmnOrgID, l_cmnBillID
1056 from bom_bill_of_materials a
1057 where a.bill_sequence_id = (select b.common_bill_sequence_id
1058 from bom_bill_of_materials b
1059 where b.assembly_item_id = p_itemID
1060 and b.organization_id = p_orgID
1061 and nvl(b.alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@')
1062 and (b.assembly_type = wip_constants.manufacturing_bill or --FP Bug 6502612
1063 to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes )); --FP Bug 6502612
1064
1065 /******
1066 select a.organization_id, a.bill_sequence_id
1067 into l_cmnOrgID, l_cmnBillID
1068 from bom_bill_of_materials a, bom_bill_of_materials b
1069 where a.bill_sequence_id = b.common_bill_sequence_id
1070 and b.assembly_item_id = p_itemID
1071 and b.organization_id = p_orgID
1072 and nvl(b.alternate_bom_designator, '@@@@@') = nvl(p_altBomDesig, '@@@@@');
1073 ******/
1074 else
1075 select a.organization_id, a.bill_sequence_id
1076 into l_cmnOrgID, l_cmnBillID
1077 from bom_bill_of_materials a, bom_bill_of_materials b
1078 where a.bill_sequence_id = b.common_bill_sequence_id
1079 and b.assembly_item_id = p_itemID
1080 and b.organization_id = p_orgID
1081 and ( nvl(b.alternate_bom_designator, '@@@@@') = nvl(p_altBomDesig , '@@@@@')
1082 or
1083 ( b.alternate_bom_designator is null
1084 and
1085 not exists (select 'x'
1086 from bom_bill_of_materials c
1087 where c.assembly_item_id = p_itemID
1088 and c.organization_id = p_orgID
1089 and c.alternate_bom_designator = p_altBomDesig)
1090 )
1091 );
1092 end if;
1093 exception
1094 when no_data_found then
1095 x_returnStatus := fnd_api.g_ret_sts_success;
1096 x_compTbl := system.wip_component_tbl_t();
1097
1098 if (l_logLevel <= wip_constants.trace_logging) then
1099 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1100 p_procReturnStatus => x_returnStatus,
1101 p_msg => 'no bom for this item!',
1102 x_returnStatus => l_returnStatus); --discard logging return status
1103 end if;
1104
1105 if(fnd_api.to_boolean(p_endDebug)) then
1106 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1107 end if;
1108 return;
1109 end;
1110
1111 if (l_logLevel <= wip_constants.full_logging) then
1112 wip_logger.log('cmn bill id: ' || l_cmnBillID,l_returnStatus);
1113 wip_logger.log('cmn org id: ' || l_cmnOrgID,l_returnStatus);
1114 end if;
1115
1116 begin
1117 select bp.inherit_phantom_op_seq,
1118 bom_explosion_temp_s.nextval,
1119 msi.bom_item_type,
1120 bp.maximum_bom_level
1121 into l_inheritPhOpSeq,
1122 l_grpID,
1123 l_bomItemType,
1124 l_maxBomLevel
1125 from bom_parameters bp, mtl_system_items_b msi
1126 where bp.organization_id = p_orgID
1127 and bp.organization_id = msi.organization_id
1128 and msi.inventory_item_id = p_itemID;
1129 exception
1130 when no_data_found then --assume bom parameters not defined. the item_id should be valid.
1131 fnd_message.set_name('BOM', 'BOM_PARAMETER_SETUP');
1132 fnd_msg_pub.add;
1133 l_errMsg := 'no bom parameters';
1134 raise fnd_api.g_exc_unexpected_error;
1135 end;
1136
1137 /* Fix for bug 5383135. To honour the profile 'WIP:Exclude ECOs',
1138 pass its value to bom exploder */
1139 wip_common.Get_Released_Revs_Type_Meaning (l_released_revs_type,
1140 l_released_revs_meaning
1141 );
1142
1143 /* Fix for bug 4771231. Get bill type(bom or eng bill) and pass it to
1144 bom exploder */
1145
1146 select assembly_type into l_bom_or_eng_flag
1147 from bom_structures_b
1148 where assembly_item_id = p_itemID
1149 and organization_id = p_orgID
1150 and nvl(alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@'); /* Bug 5139022 Added NVL functions */
1151
1152 /*Fix for bug 10245572, if WIP: See Engineering Item set to Yes, always set l_bom_or_eng_flag to be 2 so that
1153 bom explosion code can handle the explosion for a mixture of BOM and Engineering Bill Structure.*/
1154 if (to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes) then
1155 l_bom_or_eng_flag := WIP_CONSTANTS.ENGINEERING_BILL;
1156 end if;
1157
1158 --explode the bom. This API has a few shortcomings. Namely:
1159 -- it will not prune exploded components for subassemblies
1160 -- it will not set phantom component quantities properly
1161 -- it will not set phantom component op_seq's correctly
1162 --This is why we need the first loop.
1163 /* Modified following call for bug#3423629. Pass p_bomRevDate as revision date
1164 instead of p_txnDate */
1165 /*Bug#14813027- Changed the revdate to NVL(p_bomRevDate,p_txnDate) as bomRevDate can be null, if null is passed,BOM is exploded as on sysdate .
1166 Now, explosion will be done as on bomrevDate or p_txndate */
1167 bompexpl.exploder_userexit(org_id => p_orgID,
1168 grp_id => l_grpID,
1169 rev_date => to_char(NVL(p_bomRevDate,p_txnDate), wip_constants.datetime_fmt),
1170 explode_option => 2,
1171 -- order_by => 2,
1172 levels_to_explode => l_maxBomLevel,
1173 module => 5,
1174 item_id => p_itemID,
1175 bom_or_eng => l_bom_or_eng_flag,
1176 err_msg => l_msgData,
1177 error_code => l_errCode,
1178 alt_desg => p_altBomDesig,
1179 unit_number => p_unitNumber, /* Fix for bug 5332615 */
1180 release_option => l_released_revs_type, /* Fix for bug 5383135 */
1181 impl_flag => p_implFlag); /* Fix for bug 5383135 */
1182
1183
1184
1185 if (l_logLevel <= wip_constants.full_logging) then
1186 wip_logger.log(l_errCode,l_returnStatus);
1187 end if;
1188 if(l_errCode <> 0) then
1189 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1190 fnd_message.set_token('MESSAGE', l_msgData);
1191 fnd_msg_pub.add;
1192 l_errMsg := 'BOM exploder failed';
1193 raise fnd_api.g_exc_unexpected_error;
1194 end if;
1195
1196 /* Fix for Bug 5646262. Added code to check if there are any LOOP in BOM */
1197 select count(1) into l_errCode from bom_explosion_temp
1198 where group_id = l_grpID and loop_flag=1;
1199
1200 if(l_errCode <> 0) then
1201 fnd_message.set_name('WIP', 'WIP_BOM_LOOP');
1202 fnd_msg_pub.add;
1203 l_errMsg := 'Loop In BOM Encountered';
1204 raise fnd_api.g_exc_unexpected_error;
1205 end if;
1206
1207 --don't explode, just delete all exploded components
1208 if(l_bomItemType in (wip_constants.option_class_type, wip_constants.model_type)) then
1209 delete bom_explosion_temp
1210 where group_id = l_grpID
1211 and plan_level > 2;
1212 else
1213 --first pass: update quantities and delete components
1214 open c_components(v_cmnOrgID => l_cmnOrgID, v_orgID => p_orgID, v_grpID => l_grpID);
1215 loop
1216 fetch c_components into l_compRec;
1217 <<start_loop_processing>>
1218 if(c_components%NOTFOUND) then
1219 close c_components;
1220 exit;
1221 end if;
1222 if (l_logLevel <= wip_constants.full_logging) then
1223 wip_logger.log('process item ' || l_compRec.itemID || ' w/compCode= ' || l_compRec.compCode,l_returnStatus);
1224 end if;
1225 --The op seq of a phantom component should be the op seq of it's ancestor that's on the assy_item's bom,
1226 --or in other words, a direct child of the assembly. If this check is not done, multiple levels of nesting
1227 --will cause problems.
1228 if(l_inheritPhOpSeq = wip_constants.yes) then
1229 if(l_compRec.bomLevel = 2) then
1230 l_opSeqNum := l_compRec.opSeqNum;
1231 if (l_logLevel <= wip_constants.full_logging) then
1232 wip_logger.log('--set all descendant op seqs to' || l_opSeqNum,l_returnStatus);
1233 end if;
1234 end if;
1235 else
1236 if (l_logLevel <= wip_constants.full_logging) then
1237 wip_logger.log('--retain op seqs',l_returnStatus);
1238 end if;
1239 l_opSeqNum := null;
1240 end if;
1241
1242 if(l_compRec.wipSupplyType = wip_constants.phantom) then --if item is a phantom
1243 if(l_inheritPhOpSeq = wip_constants.no) then
1244 select operation_seq_num
1245 into l_opSeqNum
1246 from bom_explosion_temp
1247 where rowid = l_compRec.beRowID;
1248
1249 --Bug#13455457: Added sort_order condition. '_' has been added to ensure that only the childs are updated
1250 update bom_explosion_temp
1251 set operation_seq_num = l_opSeqNum
1252 where group_id = l_grpID
1253 and operation_seq_num = 1
1254 and component_code like l_compRec.compCode || '-%'
1255 and sort_order like l_compRec.sort_order||'_%';
1256
1257 --only needed to use l_opSeqNum for the above update
1258 l_opSeqNum := null;
1259 end if;
1260 if (l_logLevel <= wip_constants.full_logging) then
1261 wip_logger.log('--item is a phantom',l_returnStatus);
1262 end if;
1263 --update all the descendants quantity to reflect the phantom parent
1264 if(l_inheritPhOpSeq = wip_constants.yes and l_compRec.bomLevel = 1) then
1265 l_opSeqNum := l_compRec.opSeqNum;
1266 else
1267 l_opSeqNum := null;
1268 end if;
1269 if (l_logLevel <= wip_constants.full_logging) then
1270 wip_logger.log('--updated qty by ' || l_compRec.priQty,l_returnStatus);
1271 wip_logger.log('shrinkage_rate: ' || l_compRec.shrinkageRate,l_returnStatus);
1272 end if;
1273
1274 /*For Component Yield Enhancement(Bug 4369064)
1275 *Keep component_quantity free from component yield
1276 *For phantoms, recalcuate component_yield_factor by multiplying it with child yield */
1277 /* Fix for bug 5221306 lot basis was taken care for children of phantom
1278 Added decode in basis_type of component
1279 Bug#13455457: Added sort_order condition. '_' has been added to ensure that only the childs are updated*/
1280 update bom_explosion_temp
1281 set component_quantity = (component_quantity * decode (basis_type , wip_constants.lot_based_mtl,1,l_compRec.priQty) ) / (1 - l_compRec.shrinkageRate),
1282 /* For phantoms, recalcuate component_yield_factor by multiplying it with child yield only for components
1283 having basis_type as item. Bug fix 5524603. */
1284 component_yield_factor = decode(basis_type,wip_constants.lot_based_mtl,component_yield_factor,component_yield_factor * l_compRec.compYield),
1285 operation_seq_num = nvl(l_opSeqNum, operation_seq_num)
1286 where group_id = l_grpID
1287 and component_code like l_compRec.compCode || '-%'
1288 and sort_order like l_compRec.sort_order||'_%';
1289
1290
1291 else
1292 --delete all descendants of the subassembly
1293 delete bom_explosion_temp
1294 where group_id = l_grpID
1295 and component_code like l_compRec.compCode || '-%';
1296
1297 l_bomLevel := l_compRec.bomLevel;
1298 --this loop skips processing for all the 'poisonous' descendants of the subassembly
1299 loop
1300 fetch c_components into l_compRec;
1301 if(c_components%NOTFOUND) then
1302 goto start_loop_processing; --outer loop will check notfound and exit the outer loop
1303 end if;
1304 if(l_compRec.bomLevel <= l_bomLevel) then
1305 --found an item that is not a descendant of the sub assy. reset the comp code and exit the inner loop
1306 --if the item is a phantom and has any children w/op seq 1 and not inheriting op-seqs, update the op seq
1307 --to the parent's. we need to do a db sub-query b/c the phantom op may be out of sync w/the cursor if there
1308 --are consecutive levels with op_seq = 1. i.e. Assy -> Ph(20) -> Ph(1) -> Comp(1). In this case the cursor record
1309 --for the phantom item would have an op-seq of 1, while the db will have an op_seq of 20. However, we can not
1310 --blindly update all descendant's b/c one of the levels may not have an op-seq of one,
1311 --e.g. Assy -> Ph(20) -> Ph(1) -> Ph(10) -> Comp(1). In this case, we would want to set the comp's op seq to 10, not 20.
1312 goto start_loop_processing; --outer loop will check notfound, but will continue processing since the attribute will be false
1313 end if;
1314 if (l_logLevel <= wip_constants.full_logging) then
1315 wip_logger.log('--skipping item' || l_compRec.compCode,l_returnStatus);
1316 end if;
1317 end loop;
1318 end if; ----if item is a phantom
1319 end loop;
1320 end if;
1321
1322 x_compTbl := system.wip_component_tbl_t();
1323 x_compTbl.extend(g_compTblExtendSize);
1324
1325 --second pass: select all the remaining records into a pl/sql table
1326 for l_compRec in c_groupedComponents(v_cmnOrgID => l_cmnOrgID, v_orgID => p_orgID, v_grpID => l_grpID) loop
1327 if(p_qty > 0) then
1328 if(l_compRec.priQty > 0) then
1329 l_txnActionID := wip_constants.isscomp_action;
1330 else
1331 l_txnActionID := wip_constants.issnegc_action;
1332 end if;
1333 else
1334 if(l_compRec.priQty > 0) then
1335 l_txnActionID := wip_constants.retcomp_action;
1336 else
1337 l_txnActionID := wip_constants.retnegc_action;
1338 end if;
1339 end if;
1340 /*Fix for bug#14536153: To pass the component_seqid so that DFFs are copied correctly to WRO
1341 Added rownum=1 because a combination of operation_seq_num and item_id can appear twice because of phantom items. In that case, either of the component_seq_id is picked */
1342
1343 select COMPONENT_SEQUENCE_ID
1344 into l_componentSeqID
1345 from bom_explosion_temp
1346 WHERE group_id = l_grpID AND operation_seq_num=l_compRec.opSeqNum AND component_item_id=l_compRec.itemID AND rownum=1;
1347
1348 if(floor(l_extendCount/g_compTblExtendSize) = 1) then
1349 x_compTbl.extend(g_compTblExtendSize);
1350 l_extendCount := 0;
1351 end if;
1352 if(l_compRec.wipSupplyType = wip_constants.phantom) then
1353 --phantom is supposedly an implicit conversion from a supply to a demand. Thus we need to factor by the
1354 --'shrinkage rate' item attribute.
1355 l_compRec.priQty := l_compRec.priQty / (1 - l_compRec.shrinkageRate);
1356 end if;
1357
1358 if(l_compRec.revControlCode = wip_constants.revision_controlled) then --Bug#14629377:Passing TxnDate when BomRevDate is null,bomrevdate is null when wip_entity_id is null for WOLC
1359 bom_revisions.get_revision(examine_type => 'ALL',
1360 eco_status => l_released_revs_meaning,/*Added for bug fix: 7721526 (FP of bug 7553760)*/
1361 org_id => p_orgID,
1362 item_id => l_compRec.itemID,
1363 --rev_date => p_txnDate,
1364 rev_date => NVL(p_bomRevDate,p_txnDate), --8830234 (FP of 8810786): passing BOM Revision Date instead of Transaction Date
1365 itm_rev => l_revision);
1366 else
1367 l_revision := null;
1368 end if;
1369 l_locID := l_compRec.supplyLocID;
1370 l_projectID := null;
1371 l_taskID := null;
1372
1373 --if a project is provided, get the proj/task locator if necessary. If no
1374 --project/task provided, still call this API to make sure the BOM locator
1375 --is the common locator.
1376 l_success := pjm_project_locator.get_component_projectSupply(p_organization_id => p_orgID,
1377 p_project_id => p_projectID,
1378 p_task_id => p_taskID,
1379 p_wip_entity_id => null,--unused
1380 p_supply_sub => l_compRec.supplySubinv,
1381 p_supply_loc_id => l_locID,
1382 p_item_id => l_compRec.itemID,
1383 p_org_loc_control => null); --unused
1384 if(not l_success) then
1385 l_errMsg := 'PJM locator logic failed';
1386 raise fnd_api.g_exc_unexpected_error;
1387 end if;
1388
1389 --if we are using a project/task locator, then set the project/task IDs
1390 if(p_projectID is not null and
1391 l_compRec.pegFlag in (wip_constants.peg_hard, wip_constants.peg_end_assm_hard)) then
1392 l_projectID := p_projectID;
1393 l_taskID := p_taskID;
1394 end if;
1395
1396 -- locator id could be not null for item not under locator control. We should check whether
1397 -- it is under locator ctl. For bug 3885878
1398 if(l_compRec.supplySubinv is not null) then
1399 wip_globals.get_locator_control(p_orgID,
1400 l_compRec.supplySubinv,
1401 l_compRec.itemID,
1402 l_returnStatus,
1403 l_msgCount,
1404 l_msgData,
1405 l_locatorControl);
1406 if ( l_returnStatus <> fnd_api.g_ret_sts_success ) then
1407 l_errMsg := substr(l_msgData, 1, 240);
1408 raise fnd_api.g_exc_unexpected_error;
1409 end if;
1410
1411 if ( l_locatorControl = 1 ) then
1412 l_locID := null;
1413 end if;
1414 end if;
1415
1416 /* LBM Project */
1417
1418 -- set multiplication factor for lot based component
1419 if( l_compRec.basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
1420 if ((l_txnActionID = wip_constants.retcomp_action) or
1421 (l_txnActionID = wip_constants.retnegc_action)) then
1422 multiple_factor := -1;
1423 else
1424 multiple_factor := 1;
1425 end if;
1426 else
1427 multiple_factor := p_qty ;
1428 end if;
1429
1430 /* LBM Project */
1431
1432
1433 x_compTbl(l_index) := system.wip_component_obj_t(
1434 operation_seq_num => l_compRec.opSeqNum,
1435 inventory_item_id => l_compRec.itemID,
1436 item_name => l_compRec.itemName,
1437 --adjust for assy qty
1438 primary_quantity => l_compRec.priQty * multiple_factor, /* LBM Project */
1439 primary_uom_code => l_compRec.priUomCode,
1440 supply_subinventory => l_compRec.supplySubinv,
1441 supply_locator_id => l_locID,
1442 wip_supply_type => l_compRec.wipSupplyType,
1443 transaction_action_id => l_txnActionID,
1444 mtl_transactions_enabled_flag => l_compRec.txnsEnabledFlag,
1445 serial_number_control_code => l_compRec.serialNumControlCode,
1446 lot_control_code => l_compRec.lotControlCode,
1447 revision => l_revision,
1448 first_lot_index => null,
1449 last_lot_index => null,
1450 first_serial_index => null,
1451 last_serial_index => null,
1452 generic_id => null,
1453 department_id => null,
1454 restrict_subinventories_code => l_compRec.restrictSubs,
1455 restrict_locators_code => l_compRec.restrictLocs,
1456 project_id => l_projectID,
1457 task_id => l_taskID,
1458 component_sequence_id =>l_componentSeqID,/*Bug#14536153- null has been replaced with componentseqid */-- l_compRec.componentSeqID,/*Fix bug 11904037*/
1459 completion_transaction_id => null,
1460 item_description => l_compRec.itemDesc,
1461 locator_name => l_compRec.locatorName,
1462 revision_qty_control_code => l_compRec.revControlCode,
1463 location_control_code =>l_compRec.locControlCode,
1464 component_yield_factor => l_compRec.compYield,/*For Component Yield Enhancement(Bug 4369064) */
1465 basis_type => l_compRec.basis_type,
1466 locator_project_id => null,
1467 locator_task_id => null
1468 );
1469 l_index := l_index + 1;
1470 l_extendCount := l_extendCount + 1;
1471 /*Bug 5255566 (FP Bug 5504661) */
1472 <<end_of_groupcomp_loop>>
1473 null;
1474 end loop;
1475
1476 --trim any trailing null entries
1477 x_compTbl.trim(g_compTblExtendSize - l_extendCount);
1478
1479 --finally bom doesn't want us to leave anything in there temp table. delete the rows.
1480
1481 delete bom_explosion_temp
1482 where group_id = l_grpID;
1483
1484 x_returnStatus := fnd_api.g_ret_sts_success;
1485 if (l_logLevel <= wip_constants.trace_logging) then
1486 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1487 p_procReturnStatus => x_returnStatus,
1488 p_msg => 'procedure success',
1489 x_returnStatus => l_returnStatus); --discard logging return status
1490 end if;
1491
1492 if(fnd_api.to_boolean(p_endDebug)) then
1493 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1494 end if;
1495
1496 exception
1497 when fnd_api.g_exc_unexpected_error then
1498 rollback to wipbflpb30;
1499 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1500 if (l_logLevel <= wip_constants.trace_logging) then
1501 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1502 p_procReturnStatus => x_returnStatus,
1503 p_msg => l_errMsg,
1504 x_returnStatus => l_returnStatus); --discard logging return status
1505 end if;
1506
1507 if(fnd_api.to_boolean(p_endDebug)) then
1508 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1509 end if;
1510
1511 when others then
1512 rollback to wipbflpb30;
1513 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1514 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
1515 fnd_message.set_token('ERROR_TEXT', SQLERRM);
1516 fnd_msg_pub.add;
1517 if (l_logLevel <= wip_constants.trace_logging) then
1518 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1519 p_procReturnStatus => x_returnStatus,
1520 p_msg => 'unexpected error: ' || SQLERRM,
1521 x_returnStatus => l_returnStatus); --discard logging return status
1522 end if;
1523
1524 if(fnd_api.to_boolean(p_endDebug)) then
1525 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1526 end if;
1527 end explodeRequirements;
1528
1529
1530 function findTxnTypeID(p_txnActionID IN NUMBER) return NUMBER is begin
1531 if(p_txnActionID = wip_constants.isscomp_action) then
1532 return wip_constants.isscomp_type;
1533 elsif(p_txnActionID = wip_constants.issnegc_action) then
1534 return wip_constants.issnegc_type;
1535 elsif(p_txnActionID = wip_constants.retcomp_action) then
1536 return wip_constants.retcomp_type;
1537 elsif(p_txnActionID = wip_constants.retnegc_action) then
1538 return wip_constants.retnegc_type;
1539 end if;
1540 end findTxnTypeID;
1541
1542 PROCEDURE backflush(p_wipEntityID IN NUMBER,
1543 p_orgID IN NUMBER,
1544 p_primaryQty IN NUMBER,
1545 p_txnDate IN DATE,
1546 p_txnHdrID IN NUMBER,
1547 p_batchID IN NUMBER,
1548 p_txnType IN NUMBER,
1549 p_entityType IN NUMBER,
1550 p_tblName IN VARCHAR2,
1551 p_lineID IN NUMBER:= NULL,
1552 p_fmOp IN NUMBER:= NULL,
1553 p_fmStep IN NUMBER:= NULL,
1554 p_toOp IN NUMBER:= NULL,
1555 p_toStep IN NUMBER:= NULL,
1556 p_ocQty IN NUMBER:= NULL,
1557 p_childMovTxnID IN NUMBER:= NULL,
1558 p_movTxnID IN NUMBER:= NULL,
1559 p_cplTxnID IN NUMBER:= NULL,
1560 p_batchSeq IN NUMBER:= NULL,
1561 p_fmMoveProcessor IN NUMBER:= NULL,
1562 p_lockFlag IN NUMBER:= NULL,
1563 p_mtlTxnMode IN NUMBER,
1564 p_reasonID IN NUMBER := null,
1565 p_reference IN VARCHAR2 := null,
1566 x_lotSerRequired OUT NOCOPY NUMBER,
1567 x_bfRequired OUT NOCOPY NUMBER,
1568 x_returnStatus OUT NOCOPY VARCHAR2) IS
1569
1570 CURSOR c_repAssyPull IS
1571 SELECT repetitive_schedule_id scheID,
1572 primary_quantity primaryQty
1573 FROM wip_mtl_allocations_temp
1574 WHERE completion_transaction_id = p_cplTxnID;
1575
1576 CURSOR c_wmta (p_txn_id NUMBER) IS
1577 SELECT wmta.primary_quantity txn_qty,
1578 wmta.repetitive_schedule_id rep_id
1579 FROM wip_move_txn_interface wmti,
1580 wip_move_txn_allocations wmta
1581 WHERE wmti.organization_id = wmta.organization_id
1582 AND wmti.transaction_id = wmta.transaction_id
1583 AND wmti.transaction_id = p_txn_id;
1584
1585 l_rsa wip_movProc_priv.rsa_tbl_t;
1586 l_params wip_logger.param_tbl_t;
1587 l_compTbl system.wip_component_tbl_t:=NULL;
1588 l_repAssyPull c_repAssyPull%ROWTYPE;
1589 l_wmta c_wmta%ROWTYPE;
1590 l_returnStatus VARCHAR(1);
1591 l_errMsg VARCHAR2(240);
1592 l_sche_count NUMBER;
1593 l_proc_status NUMBER;
1594 l_fm_op NUMBER;
1595 l_fm_step NUMBER;
1596 l_to_op NUMBER;
1597 l_to_step NUMBER;
1598 l_first_op NUMBER;
1599 l_last_op NUMBER;
1600 l_oc_txn_type NUMBER;
1601 l_first_bf_op NUMBER;
1602 l_last_bf_op NUMBER;
1603 l_bf_qty NUMBER;
1604 l_forward NUMBER;
1605 l_bf_count NUMBER;
1606 l_lot_ser_count NUMBER;
1607 l_lot_entry_type NUMBER;
1608 l_batch_seq NUMBER;
1609 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1610 TVE_NO_MOVE_ALLOC CONSTANT NUMBER := -5;
1611 TVE_OVERCOMPLETION_MISMATCH CONSTANT NUMBER:= -6;
1612
1613 BEGIN
1614 SAVEPOINT s_backflush;
1615 -- write parameter value to log file
1616 IF (l_logLevel <= wip_constants.trace_logging) THEN
1617 l_params(1).paramName := 'p_wipEntityID';
1618 l_params(1).paramValue := p_wipEntityID;
1619 l_params(2).paramName := 'p_orgID';
1620 l_params(2).paramValue := p_orgID;
1621 l_params(3).paramName := 'p_primaryQty';
1622 l_params(3).paramValue := p_primaryQty;
1623 l_params(4).paramName := 'p_txnDate';
1624 l_params(4).paramValue := p_txnDate;
1625 l_params(5).paramName := 'p_txnHdrID';
1626 l_params(5).paramValue := p_txnHdrID;
1627 l_params(6).paramName := 'p_batchID';
1628 l_params(6).paramValue := p_batchID;
1629 l_params(7).paramName := 'p_txnType';
1630 l_params(7).paramValue := p_txnType;
1631 l_params(8).paramName := 'p_entityType';
1632 l_params(8).paramValue := p_entityType;
1633 l_params(9).paramName := 'p_tblName';
1634 l_params(9).paramValue := p_tblName;
1635 l_params(10).paramName := 'p_lineID';
1636 l_params(10).paramValue := p_lineID;
1637 l_params(11).paramName := 'p_fmOp';
1638 l_params(11).paramValue := p_fmOp;
1639 l_params(12).paramName := 'p_fmStep';
1640 l_params(12).paramValue := p_fmStep;
1641 l_params(13).paramName := 'p_toOp';
1642 l_params(13).paramValue := p_toOp;
1643 l_params(14).paramName := 'p_toStep';
1644 l_params(14).paramValue := p_toStep;
1645 l_params(15).paramName := 'p_ocQty';
1646 l_params(15).paramValue := p_ocQty;
1647 l_params(16).paramName := 'p_childMovTxnID';
1648 l_params(16).paramValue := p_childMovTxnID;
1649 l_params(17).paramName := 'p_movTxnID';
1650 l_params(17).paramValue := p_movTxnID;
1651 l_params(18).paramName := 'p_cplTxnID';
1652 l_params(18).paramValue := p_cplTxnID;
1653 l_params(19).paramName := 'p_batchSeq';
1654 l_params(19).paramValue := p_batchSeq;
1655 l_params(20).paramName := 'p_mtlTxnMode';
1656 l_params(20).paramValue := p_mtlTxnMode;
1657 l_params(21).paramName := 'p_fmMoveProcessor';
1658 l_params(21).paramValue := p_fmMoveProcessor;
1659 l_params(22).paramName := 'p_lockFlag';
1660 l_params(22).paramValue := p_lockFlag;
1661 l_params(23).paramName := 'p_mtlTxnMode';
1662 l_params(23).paramValue := p_mtlTxnMode;
1663 l_params(24).paramName := 'p_reasonID';
1664 l_params(24).paramValue := p_reasonID;
1665 l_params(25).paramName := 'p_reference';
1666 l_params(25).paramValue := p_reference;
1667
1668 wip_logger.entryPoint(p_procName => 'wip_bflProc_priv.backflush',
1669 p_params => l_params,
1670 x_returnStatus => l_returnStatus);
1671 END IF;
1672 IF(p_batchSeq IS NULL) THEN
1673 l_batch_seq := WIP_CONSTANTS.COMPONENT_BATCH_SEQ;
1674 ELSE
1675 l_batch_seq := p_batchSeq;
1676 END IF;
1677
1678 IF(p_entityType = WIP_CONSTANTS.REPETITIVE) THEN
1679 SELECT MIN(wo.operation_seq_num),
1680 MAX(wo.operation_seq_num)
1681 INTO l_first_op,
1682 l_last_op
1683 FROM wip_operations wo,
1684 wip_repetitive_schedules wrs
1685 WHERE wrs.organization_id = wo.organization_id
1686 AND wrs.wip_entity_id = wo.wip_entity_id
1687 AND wrs.repetitive_schedule_id = wo.repetitive_schedule_id
1688 AND wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
1689 AND wrs.wip_entity_id = p_wipEntityID
1690 AND wrs.organization_id = p_orgID;
1691 ELSE -- Discrete and Lotbased jobs
1692 SELECT MIN(wo.operation_seq_num),
1693 MAX(wo.operation_seq_num)
1694 INTO l_first_op,
1695 l_last_op
1696 FROM wip_operations wo,
1697 wip_discrete_jobs wdj
1698 WHERE wdj.organization_id = wo.organization_id
1699 AND wdj.wip_entity_id = wo.wip_entity_id
1700 AND wdj.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
1701 AND wdj.wip_entity_id = p_wipEntityID
1702 AND wdj.organization_id = p_orgID;
1703 END IF;
1704
1705 IF(l_first_op IS NULL) THEN
1706 -- Routingless schedules
1707 l_first_op := 0;
1708 END IF;
1709 IF(l_last_op IS NULL) THEN
1710 -- Routingless schedules
1711 l_last_op := 1.1;
1712 END IF;
1713
1714 IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
1715 p_toOp IS NULL OR p_toStep IS NULL) THEN
1716 -- Call from WIP Completion form
1717 IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
1718 -- Ccmpletion transaction
1719 l_fm_op := l_last_op;
1720 l_fm_step := WIP_CONSTANTS.TOMOVE;
1721 l_to_op := NULL;
1722 l_to_step := NULL;
1723 ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
1724 -- Return transaction
1725 l_fm_op := NULL;
1726 l_fm_step := NULL;
1727 l_to_op := l_last_op;
1728 l_to_step := WIP_CONSTANTS.TOMOVE;
1729 END IF;
1730 ELSE -- call from WIP Move form
1731 l_fm_op := p_fmOp;
1732 l_fm_step := p_fmStep;
1733 l_to_op := p_toOp;
1734 l_to_step := p_toStep;
1735 END IF; -- Call from WIP Completion form
1736
1737 -- Check if repetitive schedule
1738 IF(p_entityType = WIP_CONSTANTS.REPETITIVE) THEN -- Repetitive schedule
1739 -- Fixed bug 5056289.Basically, we should not rely on the fact that p_fmOp
1740 -- will be null if call from Completion form. We should also check
1741 -- p_fmMoveProcessor. Since move processor will also pass null for p_fmOp
1742 -- for assembly pull item.
1743 IF((p_fmOp IS NULL OR p_fmStep IS NULL OR
1744 p_toOp IS NULL OR p_toStep IS NULL) AND
1745 (p_fmMoveProcessor IS NULL OR
1746 p_fmMoveProcessor = WIP_CONSTANTS.NO)) THEN
1747 -- Call from WIP Completion form, so use the allocation information in
1748 -- wip_mtl_allocations_temp. There is no need to call schedule_alloc
1749 -- again.
1750
1751 /*Backflush all assembly pull component*/
1752 FOR l_repAssyPull IN c_repAssyPull LOOP
1753 wip_bflProc_priv.processRequirements
1754 (p_wipEntityID => p_wipEntityID,
1755 p_wipEntityType => p_entityType,
1756 p_repSchedID => l_repAssyPull.scheID,
1757 p_repLineID => p_lineID,
1758 p_cplTxnID => p_cplTxnID,
1759 -- Fixed bug 5014211. Stamp move_transaction_id for assembly
1760 -- pull components so that we will have a link if component
1761 -- records fail inventory validation.
1762 p_movTxnID => p_movTxnID,
1763 p_batchID => p_batchID,
1764 p_orgID => p_orgID,
1765 p_assyQty => l_repAssyPull.primaryQty,
1766 p_txnDate => p_txnDate,
1767 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
1768 p_txnHdrID => p_txnHdrID,
1769 p_firstOp => -1,
1770 p_lastOP => l_last_op,
1771 p_firstMoveOp => null,
1772 p_lastMoveOp => null,
1773 p_lockFlag => p_lockFlag,
1774 p_batchSeq => l_batch_seq,
1775 p_mergeMode => fnd_api.g_true,
1776 p_reasonID => p_reasonID,
1777 p_reference => p_reference,
1778 p_initMsgList => fnd_api.g_false,
1779 p_endDebug => fnd_api.g_false,
1780 p_mtlTxnMode => p_mtlTxnMode,
1781 x_compTbl => l_compTbl,
1782 x_returnStatus => l_returnStatus);
1783
1784 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
1785 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
1786 raise fnd_api.g_exc_unexpected_error;
1787 END IF;
1788 END LOOP;
1789
1790 ELSE
1791 IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
1792 -- If call from move processor, no need to do schedule allocation again
1793 -- Instead, we should use the value in WMTA table.
1794 l_sche_count := 0;
1795 FOR l_wmta IN c_wmta (p_txn_id => p_movTxnID) LOOP
1796 l_sche_count := l_sche_count + 1;
1797 l_rsa(l_sche_count).scheID := l_wmta.rep_id;
1798 l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
1799 END LOOP;
1800 ELSIF(p_fmMoveProcessor IS NULL OR
1801 p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
1802 -- Check whether overcompletion transaction
1803 IF(p_ocQty IS NOT NULL) THEN
1804 l_oc_txn_type := WIP_CONSTANTS.PARENT_TXN;
1805 ELSE
1806 l_oc_txn_type := WIP_CONSTANTS.NORMAL_TXN;
1807 END IF;
1808 wip_movProc_priv.schedule_alloc(p_org_id => p_orgID,
1809 p_wip_id => p_wipEntityID,
1810 p_line_id => p_lineID,
1811 p_quantity => p_primaryQty,
1812 p_fm_op => l_fm_op,
1813 p_fm_step => l_fm_step,
1814 p_to_op => l_to_op,
1815 p_to_step => l_to_step,
1816 p_oc_txn_type => l_oc_txn_type,
1817 p_txnType => p_txnType,
1818 p_fm_form => WIP_CONSTANTS.YES,
1819 p_comp_alloc => WIP_CONSTANTS.NO,
1820 p_txn_date => p_txndate, /* bug 5373061 */
1821 x_proc_status => l_proc_status,
1822 x_sche_count => l_sche_count,
1823 x_rsa => l_rsa,
1824 x_returnStatus => l_returnStatus);
1825
1826 IF (l_logLevel <= wip_constants.full_logging) THEN
1827 wip_logger.log(p_msg => 'l_proc_status = ' || l_proc_status,
1828 x_returnStatus => l_returnStatus);
1829 wip_logger.log(p_msg => 'l_sche_count = ' || l_sche_count,
1830 x_returnStatus => l_returnStatus);
1831 END IF;
1832
1833 IF(l_proc_status = TVE_OVERCOMPLETION_MISMATCH) THEN
1834 fnd_message.set_name('WIP', 'WIP_OVERCOMPLETION_MISMATCH');
1835 fnd_msg_pub.add;
1836 l_errMsg := 'parent txn is not really overcompletion txn';
1837 raise fnd_api.g_exc_unexpected_error;
1838 ELSIF(l_proc_status = TVE_NO_MOVE_ALLOC) THEN
1839 fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
1840 fnd_message.set_token('ENTITY1', 'transaction quantity');
1841 fnd_message.set_token('ENTITY2', 'quantity available to move');
1842 fnd_msg_pub.add;
1843 l_errMsg := 'available qty is not enough to fullfill move txn';
1844 raise fnd_api.g_exc_unexpected_error;
1845 ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
1846 l_errMsg := 'wip_movProc_priv.schedule_alloc failed';
1847 raise fnd_api.g_exc_unexpected_error;
1848 END IF; -- check l_proc_status
1849 END IF; -- check p_fmMoveProcessor
1850
1851 IF (l_logLevel <= wip_constants.full_logging) THEN
1852 FOR i IN 1..l_sche_count LOOP
1853 wip_logger.log(p_msg => 'sche_id = ' || l_rsa(i).scheID,
1854 x_returnStatus => l_returnStatus);
1855 wip_logger.log(p_msg => 'txn_qty = ' || l_rsa(i).scheQty,
1856 x_returnStatus => l_returnStatus);
1857 END LOOP;
1858 END IF;
1859 -- Check whether call from Completion form or not
1860
1861 IF(l_fm_op IS NOT NULL AND l_to_op IS NOT NULL) THEN
1862
1863 -- set l_first_bf_op and l_last_bf_op back to -1
1864 l_first_bf_op := -1;
1865 l_last_bf_op := -1;
1866
1867 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty
1868 -- before call wip_bflProc_priv.processRequirements for
1869 -- Operation Pull components
1870 wma_move.bf_require(p_jobID => p_wipEntityID,
1871 p_fm_op => l_fm_op,
1872 p_fm_step => l_fm_step,
1873 p_to_op => l_to_op,
1874 p_to_step => l_to_step,
1875 p_moveQty => p_primaryQty,
1876 x_first_bf_op => l_first_bf_op,
1877 x_last_bf_op => l_last_bf_op,
1878 x_bf_qty => l_bf_qty,
1879 x_returnStatus => l_returnStatus,
1880 x_errMessage => l_errMsg);
1881 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
1882 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1883 fnd_message.set_token('MESSAGE', l_errMsg);
1884 fnd_msg_pub.add;
1885 raise fnd_api.g_exc_unexpected_error;
1886 END IF;
1887
1888 IF(l_first_bf_op <> -1) THEN
1889 -- check forward transactions
1890 IF(l_bf_qty > 0) THEN
1891 l_forward := 1;
1892 ELSE
1893 l_forward := -1;
1894 END IF;
1895 FOR i IN 1..l_sche_count LOOP
1896 /**
1897 * Call backflush processor to insert record into MMTT
1898 * for each schedule found in l_rsa.
1899 * This is only for operation pull components.
1900 **/
1901 wip_bflProc_priv.processRequirements
1902 (p_wipEntityID => p_wipEntityID,
1903 p_wipEntityType => p_entityType,
1904 p_repSchedID => l_rsa(i).scheID,
1905 p_repLineID => p_lineID,
1906 p_cplTxnID => null,
1907 p_movTxnID => p_movTxnID,
1908 p_batchID => p_batchID,
1909 p_orgID => p_orgID,
1910 p_assyQty => l_rsa(i).scheQty * l_forward,
1911 p_txnDate => p_txnDate,
1912 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
1913 p_txnHdrID => p_txnHdrID,
1914 p_firstOp => l_first_bf_op,
1915 p_lastOP => l_last_bf_op,
1916 p_firstMoveOp => l_fm_op,
1917 p_lastMoveOp => l_to_op,
1918 p_lockFlag => p_lockFlag,
1919 p_batchSeq => l_batch_seq,
1920 p_mergeMode => fnd_api.g_true,
1921 p_reasonID => p_reasonID,
1922 p_reference => p_reference,
1923 p_initMsgList => fnd_api.g_false,
1924 p_endDebug => fnd_api.g_false,
1925 p_mtlTxnMode => p_mtlTxnMode,
1926 x_compTbl => l_compTbl,
1927 x_returnStatus => l_returnStatus);
1928
1929 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
1930 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
1931 raise fnd_api.g_exc_unexpected_error;
1932 END IF;
1933 END LOOP;
1934 END IF; -- l_first_bf_op <> -1
1935
1936 -- Call assy_pull_bf to derive first_bf_op, last_bf_op,
1937 -- and bf_qty before call wip_bflProc_priv.processRequirements
1938 -- for Assembly Pull components. This is only for Scrap txns
1939
1940 -- set l_first_bf_op and l_last_bf_op back to -1
1941 l_first_bf_op := -1;
1942 l_last_bf_op := -1;
1943
1944 wma_move.assy_pull_bf(p_jobID => p_wipEntityID,
1945 p_fm_op => l_fm_op,
1946 p_fm_step => l_fm_step,
1947 p_to_op => l_to_op,
1948 p_to_step => l_to_step,
1949 p_moveQty => p_primaryQty,
1950 x_first_bf_op => l_first_bf_op,
1951 x_last_bf_op => l_last_bf_op,
1952 x_bf_qty => l_bf_qty,
1953 x_returnStatus => l_returnStatus,
1954 x_errMessage => l_errMsg);
1955
1956 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
1957 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1958 fnd_message.set_token('MESSAGE', l_errMsg);
1959 fnd_msg_pub.add;
1960 raise fnd_api.g_exc_unexpected_error;
1961 END IF;
1962
1963 IF(l_first_bf_op <> -1) THEN
1964 -- check forward transactions
1965 IF(l_bf_qty > 0) THEN
1966 l_forward := 1;
1967 ELSE
1968 l_forward := -1;
1969 END IF;
1970 FOR i IN 1..l_sche_count LOOP
1971 /**
1972 * Call backflush processor to insert record into MMTT
1973 * for each schedule found in l_rsa
1974 * This is only for assembly pull components.
1975 **/
1976 wip_bflProc_priv.processRequirements
1977 (p_wipEntityID => p_wipEntityID,
1978 p_wipEntityType => p_entityType,
1979 p_repSchedID => l_rsa(i).scheID,
1980 p_repLineID => p_lineID,
1981 p_cplTxnID => null,
1982 p_movTxnID => p_movTxnID,
1983 p_batchID => p_batchID,
1984 p_orgID => p_orgID,
1985 p_assyQty => l_rsa(i).scheQty * l_forward,
1986 p_txnDate => p_txnDate,
1987 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
1988 p_txnHdrID => p_txnHdrID,
1989 p_firstOp => l_first_bf_op,
1990 p_lastOP => l_last_bf_op,
1991 p_firstMoveOp => l_fm_op,
1992 p_lastMoveOp => l_to_op,
1993 p_lockFlag => p_lockFlag,
1994 p_batchSeq => l_batch_seq,
1995 p_mergeMode => fnd_api.g_true,
1996 p_reasonID => p_reasonID,
1997 p_reference => p_reference,
1998 p_initMsgList => fnd_api.g_false,
1999 p_endDebug => fnd_api.g_false,
2000 p_mtlTxnMode => p_mtlTxnMode,
2001 x_compTbl => l_compTbl,
2002 x_returnStatus => l_returnStatus);
2003
2004 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2005 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2006 raise fnd_api.g_exc_unexpected_error;
2007 END IF;
2008 END LOOP;
2009 END IF; -- l_first_bf_op <> -1
2010 END IF; -- call from Move form
2011
2012 -- only do schedule allocation for completion if not call from move
2013 -- processor
2014 IF(p_cplTxnID IS NOT NULL AND
2015 (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2016 p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2017 IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
2018 -- If call from move processor, no need to do schedule allocation
2019 -- again Instead, we should use the value in WMTA table.
2020 l_sche_count := 0;
2021 FOR l_wmta IN c_wmta (p_txn_id => p_movTxnID) LOOP
2022 l_sche_count := l_sche_count + 1;
2023 l_rsa(l_sche_count).scheID := l_wmta.rep_id;
2024 l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
2025 END LOOP;
2026 ELSIF(p_fmMoveProcessor IS NULL OR
2027 p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
2028 wip_movProc_priv.schedule_alloc(
2029 p_org_id => p_orgID,
2030 p_wip_id => p_wipEntityID,
2031 p_line_id => p_lineID,
2032 p_quantity => p_primaryQty,
2033 p_fm_op => l_fm_op,
2034 p_fm_step => l_fm_step,
2035 p_to_op => l_to_op,
2036 p_to_step => l_to_step,
2037 p_oc_txn_type => l_oc_txn_type,
2038 p_txnType => p_txnType,
2039 p_fm_form => WIP_CONSTANTS.YES,
2040 p_comp_alloc => WIP_CONSTANTS.YES,
2041 p_txn_date => p_txndate, /* bug 5373061 */
2042 x_proc_status => l_proc_status,
2043 x_sche_count => l_sche_count,
2044 x_rsa => l_rsa,
2045 x_returnStatus => l_returnStatus);
2046
2047 IF (l_logLevel <= wip_constants.full_logging) THEN
2048 wip_logger.log(p_msg => 'l_proc_status = ' ||
2049 l_proc_status,
2050 x_returnStatus => l_returnStatus);
2051 wip_logger.log(p_msg => 'l_sche_count = ' ||
2052 l_sche_count,
2053 x_returnStatus => l_returnStatus);
2054 END IF;
2055
2056 IF(l_proc_status = TVE_OVERCOMPLETION_MISMATCH) THEN
2057 fnd_message.set_name('WIP', 'WIP_OVERCOMPLETION_MISMATCH');
2058 fnd_msg_pub.add;
2059 l_errMsg := 'parent txn is not really overcompletion txn';
2060 raise fnd_api.g_exc_unexpected_error;
2061 ELSIF(l_proc_status = TVE_NO_MOVE_ALLOC) THEN
2062 fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
2063 fnd_message.set_token('ENTITY1', 'transaction quantity');
2064 fnd_message.set_token('ENTITY2', 'quantity available to move');
2065 fnd_msg_pub.add;
2066 l_errMsg := 'available qty is not enough to fullfill move txn';
2067 raise fnd_api.g_exc_unexpected_error;
2068 ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
2069 l_errMsg := 'wip_movProc_priv.schedule_alloc failed';
2070 raise fnd_api.g_exc_unexpected_error;
2071 END IF; -- check l_proc_status
2072 END IF; -- check p_fmMoveProcessor
2073
2074 IF (l_logLevel <= wip_constants.full_logging) THEN
2075 FOR i IN 1..l_sche_count LOOP
2076 wip_logger.log(p_msg => 'sche_id = ' || l_rsa(i).scheID,
2077 x_returnStatus => l_returnStatus);
2078 wip_logger.log(p_msg => 'txn_qty = ' || l_rsa(i).scheQty,
2079 x_returnStatus => l_returnStatus);
2080 END LOOP;
2081 END IF;
2082
2083 -- If Completion or Return txns, we have to backflush Assembly
2084 -- pull components too
2085 IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2086 l_forward := 1;
2087 ELSE
2088 l_forward := -1;
2089 END IF;
2090
2091 FOR i IN 1..l_sche_count LOOP
2092 /**
2093 * Call backflush processor to insert record into MMTT
2094 * for each schedule found in l_rsa
2095 * This is only for assembly pull components(Completion/Return).
2096 **/
2097 wip_bflProc_priv.processRequirements
2098 (p_wipEntityID => p_wipEntityID,
2099 p_wipEntityType => p_entityType,
2100 p_repSchedID => l_rsa(i).scheID,
2101 p_repLineID => p_lineID,
2102 p_cplTxnID => p_cplTxnID,
2103 -- Fixed bug 5014211. Stamp move_transaction_id for assembly
2104 -- pull components so that we will have a link if component
2105 -- records fail inventory validation.
2106 p_movTxnID => p_movTxnID,
2107 p_batchID => p_batchID,
2108 p_orgID => p_orgID,
2109 p_assyQty => l_rsa(i).scheQty * l_forward,
2110 p_txnDate => p_txnDate,
2111 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2112 p_txnHdrID => p_txnHdrID,
2113 p_firstOp => -1,
2114 p_lastOP => l_last_op,
2115 p_firstMoveOp => null,
2116 p_lastMoveOp => null,
2117 p_lockFlag => p_lockFlag,
2118 p_batchSeq => l_batch_seq,
2119 p_mergeMode => fnd_api.g_true,
2120 p_reasonID => p_reasonID,
2121 p_reference => p_reference,
2122 p_initMsgList => fnd_api.g_false,
2123 p_endDebug => fnd_api.g_false,
2124 p_mtlTxnMode => p_mtlTxnMode,
2125 x_compTbl => l_compTbl,
2126 x_returnStatus => l_returnStatus);
2127
2128 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2129 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2130 raise fnd_api.g_exc_unexpected_error;
2131 END IF;
2132 END LOOP;
2133 END IF; -- Completion/return txns
2134 END IF; -- call from completion form
2135 -- Check whether overcompletion
2136 IF(p_childMovTxnID IS NOT NULL AND p_ocQty IS NOT NULL) THEN
2137 -- overmove/overcomplete
2138 IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
2139 -- If call from move processor, no need to do schedule allocation
2140 -- again. Instead, we should use the value in WMTA table.
2141 l_sche_count := 0;
2142 FOR l_wmta IN c_wmta (p_txn_id => p_childMovTxnID) LOOP
2143 l_sche_count := l_sche_count + 1;
2144 l_rsa(l_sche_count).scheID := l_wmta.rep_id;
2145 l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
2146 END LOOP;
2147 ELSIF(p_fmMoveProcessor IS NULL OR
2148 p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
2149 l_oc_txn_type := WIP_CONSTANTS.CHILD_TXN;
2150 l_fm_op := l_first_op;
2151 l_fm_step := WIP_CONSTANTS.QUEUE;
2152 IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2153 p_toOp IS NULL OR p_toStep IS NULL) THEN
2154 -- Call from Completion form
2155 l_to_op := l_last_op;
2156 l_to_step := WIP_CONSTANTS.TOMOVE;
2157 ELSE -- Call from WIP Move or OSFM Move forms
2158 l_to_op := p_fmOp;
2159 l_to_step := p_fmStep;
2160 END IF;
2161 wip_movProc_priv.schedule_alloc(
2162 p_org_id => p_orgID,
2163 p_wip_id => p_wipEntityID,
2164 p_line_id => p_lineID,
2165 p_quantity => p_ocQty,
2166 p_fm_op => l_fm_op,
2167 p_fm_step => l_fm_step,
2168 p_to_op => l_to_op,
2169 p_to_step => l_to_step,
2170 p_oc_txn_type => l_oc_txn_type,
2171 p_txnType => p_txnType,
2172 p_fm_form => WIP_CONSTANTS.YES,
2173 p_comp_alloc => WIP_CONSTANTS.NO,
2174 p_txn_date => p_txndate, /* bug 5373061 */
2175 x_proc_status => l_proc_status,
2176 x_sche_count => l_sche_count,
2177 x_rsa => l_rsa,
2178 x_returnStatus => l_returnStatus);
2179
2180 IF (l_logLevel <= wip_constants.full_logging) THEN
2181 wip_logger.log(p_msg => 'l_proc_status = ' ||
2182 l_proc_status,
2183 x_returnStatus => l_returnStatus);
2184 wip_logger.log(p_msg => 'l_sche_count = ' ||
2185 l_sche_count,
2186 x_returnStatus => l_returnStatus);
2187 END IF;
2188
2189 IF(l_proc_status = TVE_OVERCOMPLETION_MISMATCH) THEN
2190 fnd_message.set_name('WIP', 'WIP_OVERCOMPLETION_MISMATCH');
2191 fnd_msg_pub.add;
2192 l_errMsg := 'parent txn is not really overcompletion txn';
2193 raise fnd_api.g_exc_unexpected_error;
2194 ELSIF(l_proc_status = TVE_NO_MOVE_ALLOC) THEN
2195 fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
2196 fnd_message.set_token('ENTITY1', 'transaction quantity');
2197 fnd_message.set_token('ENTITY2', 'quantity available to move');
2198 fnd_msg_pub.add;
2199 l_errMsg := 'available qty is not enough to fullfill move txn';
2200 raise fnd_api.g_exc_unexpected_error;
2201 ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
2202 l_errMsg := 'wip_movProc_priv.schedule_alloc failed';
2203 raise fnd_api.g_exc_unexpected_error;
2204 END IF; -- check l_proc_status
2205 END IF; -- check p_fmMoveProcessor
2206
2207 IF (l_logLevel <= wip_constants.full_logging) THEN
2208 FOR i IN 1..l_sche_count LOOP
2209 wip_logger.log(p_msg => 'sche_id = ' || l_rsa(i).scheID,
2210 x_returnStatus => l_returnStatus);
2211 wip_logger.log(p_msg => 'txn_qty = ' || l_rsa(i).scheQty,
2212 x_returnStatus => l_returnStatus);
2213 END LOOP;
2214 END IF;
2215 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty
2216 -- before call wip_bflProc_priv.processRequirements for
2217 -- Operation Pull components
2218
2219 -- set l_first_bf_op and l_last_bf_op back to -1
2220 l_first_bf_op := -1;
2221 l_last_bf_op := -1;
2222 wma_move.bf_require(p_jobID => p_wipEntityID,
2223 p_fm_op => l_fm_op,
2224 p_fm_step => l_fm_step,
2225 p_to_op => l_to_op,
2226 p_to_step => l_to_step,
2227 p_moveQty => p_ocQty,
2228 x_first_bf_op => l_first_bf_op,
2229 x_last_bf_op => l_last_bf_op,
2230 x_bf_qty => l_bf_qty,
2231 x_returnStatus => l_returnStatus,
2232 x_errMessage => l_errMsg);
2233 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2234 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2235 fnd_message.set_token('MESSAGE', l_errMsg);
2236 fnd_msg_pub.add;
2237 raise fnd_api.g_exc_unexpected_error;
2238 END IF;
2239 IF(l_first_bf_op <> -1) THEN
2240 -- check forward transactions
2241 IF(l_bf_qty > 0) THEN
2242 l_forward := 1;
2243 ELSE
2244 l_forward := -1;
2245 END IF;
2246 FOR i IN 1..l_sche_count LOOP
2247 /**
2248 * Call backflush processor to insert record into MMTT
2249 * for each schedule found in l_rsa.
2250 * This is only for operation pull components.
2251 **/
2252 wip_bflProc_priv.processRequirements
2253 (p_wipEntityID => p_wipEntityID,
2254 p_wipEntityType => p_entityType,
2255 p_repSchedID => l_rsa(i).scheID,
2256 p_repLineID => p_lineID,
2257 p_cplTxnID => null,
2258 p_movTxnID => p_childMovTxnID,
2259 p_batchID => p_batchID,
2260 p_orgID => p_orgID,
2261 p_assyQty => l_rsa(i).scheQty * l_forward,
2262 p_txnDate => p_txnDate,
2263 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
2264 p_txnHdrID => p_txnHdrID,
2265 p_firstOp => l_first_bf_op,
2266 p_lastOP => l_last_bf_op,
2267 p_firstMoveOp => l_fm_op,
2268 p_lastMoveOp => l_to_op,
2269 p_batchSeq => l_batch_seq,
2270 p_lockFlag => p_lockFlag,
2271 p_mergeMode => fnd_api.g_true,
2272 p_reasonID => p_reasonID,
2273 p_reference => p_reference,
2274 p_initMsgList => fnd_api.g_false,
2275 p_endDebug => fnd_api.g_false,
2276 p_mtlTxnMode => p_mtlTxnMode,
2277 x_compTbl => l_compTbl,
2278 x_returnStatus => l_returnStatus);
2279
2280 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2281 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2282 raise fnd_api.g_exc_unexpected_error;
2283 END IF;
2284 END LOOP;
2285 END IF; -- l_first_bf_op <> -1
2286 -- Call assy_pull_bf to derive first_bf_op, last_bf_op,
2287 -- and bf_qty before call wip_bflProc_priv.processRequirements
2288 -- for Assembly Pull components. This is only for Scrap txns
2289
2290 -- set l_first_bf_op and l_last_bf_op back to -1
2291 l_first_bf_op := -1;
2292 l_last_bf_op := -1;
2293
2294 wma_move.assy_pull_bf(p_jobID => p_wipEntityID,
2295 p_fm_op => l_fm_op,
2296 p_fm_step => l_fm_step,
2297 p_to_op => l_to_op,
2298 p_to_step => l_to_step,
2299 p_moveQty => p_ocQty,
2300 x_first_bf_op => l_first_bf_op,
2301 x_last_bf_op => l_last_bf_op,
2302 x_bf_qty => l_bf_qty,
2303 x_returnStatus => l_returnStatus,
2304 x_errMessage => l_errMsg);
2305
2306 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2307 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2308 fnd_message.set_token('MESSAGE', l_errMsg);
2309 fnd_msg_pub.add;
2310 raise fnd_api.g_exc_unexpected_error;
2311 END IF;
2312
2313 IF(l_first_bf_op <> -1) THEN
2314 -- check forward transactions
2315 IF(l_bf_qty > 0) THEN
2316 l_forward := 1;
2317 ELSE
2318 l_forward := -1;
2319 END IF;
2320 FOR i IN 1..l_sche_count LOOP
2321 /**
2322 * Call backflush processor to insert record into MMTT
2323 * for each schedule found in l_rsa
2324 * This is only for assembly pull components.
2325 **/
2326 wip_bflProc_priv.processRequirements
2327 (p_wipEntityID => p_wipEntityID,
2328 p_wipEntityType => p_entityType,
2329 p_repSchedID => l_rsa(i).scheID,
2330 p_repLineID => p_lineID,
2331 p_cplTxnID => null,
2332 p_movTxnID => p_childMovTxnID,
2333 p_batchID => p_batchID,
2334 p_orgID => p_orgID,
2335 p_assyQty => l_rsa(i).scheQty * l_forward,
2336 p_txnDate => p_txnDate,
2337 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2338 p_txnHdrID => p_txnHdrID,
2339 p_firstOp => l_first_bf_op,
2340 p_lastOP => l_last_bf_op,
2341 p_firstMoveOp => l_fm_op,
2342 p_lastMoveOp => l_to_op,
2343 p_lockFlag => p_lockFlag,
2344 p_batchSeq => l_batch_seq,
2345 p_mergeMode => fnd_api.g_true,
2346 p_reasonID => p_reasonID,
2347 p_reference => p_reference,
2348 p_initMsgList => fnd_api.g_false,
2349 p_endDebug => fnd_api.g_false,
2350 p_mtlTxnMode => p_mtlTxnMode,
2351 x_compTbl => l_compTbl,
2352 x_returnStatus => l_returnStatus);
2353
2354 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2355 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2356 raise fnd_api.g_exc_unexpected_error;
2357 END IF;
2358 END LOOP;
2359 END IF; -- l_first_bf_op <> -1
2360 END IF;-- -- overmove/overcomplete
2361 ELSE -- Discrete and Lotbased Job
2362 -- Check whether call from Completion form or not
2363 IF(l_fm_op IS NOT NULL AND l_to_op IS NOT NULL) THEN
2364
2365 -- set l_first_bf_op and l_last_bf_op back to -1
2366 l_first_bf_op := -1;
2367 l_last_bf_op := -1;
2368 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty
2369 -- before call wip_bflProc_priv.processRequirements for
2370 -- Operation Pull components
2371 wma_move.bf_require(p_jobID => p_wipEntityID,
2372 p_fm_op => l_fm_op,
2373 p_fm_step => l_fm_step,
2374 p_to_op => l_to_op,
2375 p_to_step => l_to_step,
2376 p_moveQty => p_primaryQty,
2377 x_first_bf_op => l_first_bf_op,
2378 x_last_bf_op => l_last_bf_op,
2379 x_bf_qty => l_bf_qty,
2380 x_returnStatus => l_returnStatus,
2381 x_errMessage => l_errMsg);
2382 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2383 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2384 fnd_message.set_token('MESSAGE', l_errMsg);
2385 fnd_msg_pub.add;
2386 raise fnd_api.g_exc_unexpected_error;
2387 END IF;
2388 IF(l_first_bf_op <> -1) THEN
2389 wip_bflProc_priv.processRequirements
2390 (p_wipEntityID => p_wipEntityID,
2391 p_wipEntityType => p_entityType,
2392 p_repSchedID => null,
2393 p_repLineID => null,
2394 p_cplTxnID => null,
2395 p_movTxnID => p_movTxnID,
2396 p_batchID => p_batchID,
2397 p_orgID => p_orgID,
2398 p_assyQty => l_bf_qty,
2399 p_txnDate => p_txnDate,
2400 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
2401 p_txnHdrID => p_txnHdrID,
2402 p_firstOp => l_first_bf_op,
2403 p_lastOP => l_last_bf_op,
2404 p_firstMoveOp => l_fm_op,
2405 p_lastMoveOp => l_to_op,
2406 p_batchSeq => l_batch_seq,
2407 p_lockFlag => p_lockFlag,
2408 p_mergeMode => fnd_api.g_false,
2409 p_reasonID => p_reasonID,
2410 p_reference => p_reference,
2411 p_initMsgList => fnd_api.g_false,
2412 p_endDebug => fnd_api.g_false,
2413 p_mtlTxnMode => p_mtlTxnMode,
2414 x_compTbl => l_compTbl,
2415 x_returnStatus => l_returnStatus);
2416
2417 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2418 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2419 raise fnd_api.g_exc_unexpected_error;
2420 END IF;
2421 END IF; -- l_first_bf_op <> -1
2422
2423 -- Call assy_pull_bf to derive first_bf_op, last_bf_op,
2424 -- and bf_qty before call wip_bflProc_priv.processRequirements
2425 -- for Assembly Pull components. This is only for Scrap txns
2426
2427 -- set l_first_bf_op and l_last_bf_op back to -1
2428 l_first_bf_op := -1;
2429 l_last_bf_op := -1;
2430
2431 wma_move.assy_pull_bf(p_jobID => p_wipEntityID,
2432 p_fm_op => l_fm_op,
2433 p_fm_step => l_fm_step,
2434 p_to_op => l_to_op,
2435 p_to_step => l_to_step,
2436 p_moveQty => p_primaryQty,
2437 x_first_bf_op => l_first_bf_op,
2438 x_last_bf_op => l_last_bf_op,
2439 x_bf_qty => l_bf_qty,
2440 x_returnStatus => l_returnStatus,
2441 x_errMessage => l_errMsg);
2442
2443 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2444 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2445 fnd_message.set_token('MESSAGE', l_errMsg);
2446 fnd_msg_pub.add;
2447 raise fnd_api.g_exc_unexpected_error;
2448 END IF;
2449 IF(l_first_bf_op <> -1) THEN
2450
2451 /**
2452 * Call backflush processor to insert record into MMTT
2453 * for each schedule found in l_rsa
2454 * This is only for assembly pull components.
2455 **/
2456 wip_bflProc_priv.processRequirements
2457 (p_wipEntityID => p_wipEntityID,
2458 p_wipEntityType => p_entityType,
2459 p_repSchedID => null,
2460 p_repLineID => null,
2461 p_cplTxnID => null,
2462 p_movTxnID => p_movTxnID,
2463 p_batchID => p_batchID,
2464 p_orgID => p_orgID,
2465 p_assyQty => l_bf_qty,
2466 p_txnDate => p_txnDate,
2467 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2468 p_txnHdrID => p_txnHdrID,
2469 p_firstOp => l_first_bf_op,
2470 p_lastOP => l_last_bf_op,
2471 p_firstMoveOp => l_fm_op,
2472 p_lastMoveOp => l_to_op,
2473 p_batchSeq => l_batch_seq,
2474 p_lockFlag => p_lockFlag,
2475 p_mergeMode => fnd_api.g_false,
2476 p_reasonID => p_reasonID,
2477 p_reference => p_reference,
2478 p_initMsgList => fnd_api.g_false,
2479 p_endDebug => fnd_api.g_false,
2480 p_mtlTxnMode => p_mtlTxnMode,
2481 x_compTbl => l_compTbl,
2482 x_returnStatus => l_returnStatus);
2483
2484 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2485 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2486 raise fnd_api.g_exc_unexpected_error;
2487 END IF;
2488 END IF; -- l_first_bf_op <> -1
2489 END IF; -- call from Move form
2490
2491 IF(p_cplTxnID IS NOT NULL AND
2492 (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2493 p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2494
2495 IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2496 l_bf_qty := p_primaryQty;
2497 ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
2498 l_bf_qty := -1 * p_primaryQty;
2499 END IF;
2500 wip_bflProc_priv.processRequirements
2501 (p_wipEntityID => p_wipEntityID,
2502 p_wipEntityType => p_entityType,
2503 p_repSchedID => null,
2504 p_repLineID => null,
2505 p_cplTxnID => p_cplTxnID,
2506 -- Fixed bug 5014211. Stamp move_transaction_id for assembly
2507 -- pull components so that we will have a link if component
2508 -- records fail inventory validation.
2509 p_movTxnID => p_movTxnID,
2510 p_batchID => p_batchID,
2511 p_orgID => p_orgID,
2512 p_assyQty => l_bf_qty,
2513 p_txnDate => p_txnDate,
2514 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2515 p_txnHdrID => p_txnHdrID,
2516 p_firstOp => -1,
2517 p_lastOP => l_last_op,
2518 p_firstMoveOp => null,
2519 p_lastMoveOp => null,
2520 p_lockFlag => p_lockFlag,
2521 p_batchSeq => l_batch_seq,
2522 p_mergeMode => fnd_api.g_false,
2523 p_reasonID => p_reasonID,
2524 p_reference => p_reference,
2525 p_initMsgList => fnd_api.g_false,
2526 p_endDebug => fnd_api.g_false,
2527 p_mtlTxnMode => p_mtlTxnMode,
2528 x_compTbl => l_compTbl,
2529 x_returnStatus => l_returnStatus);
2530
2531 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2532 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2533 raise fnd_api.g_exc_unexpected_error;
2534 END IF;
2535 END IF; -- Completion/Return/EZ Completion/EZ Return
2536
2537 -- Check whether overcompletion
2538 IF(p_childMovTxnID IS NOT NULL AND p_ocQty IS NOT NULL) THEN
2539 -- overmove/overcomplete
2540 l_fm_op := l_first_op;
2541 l_fm_step := WIP_CONSTANTS.QUEUE;
2542 IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2543 p_toOp IS NULL OR p_toStep IS NULL) THEN
2544 -- Call from Completion form
2545 l_to_op := l_last_op;
2546 l_to_step := WIP_CONSTANTS.TOMOVE;
2547 ELSE -- Call from WIP Move or OSFM Move forms
2548 l_to_op := p_fmOp;
2549 l_to_step := p_fmStep;
2550 END IF;
2551 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty
2552 -- before call wip_bflProc_priv.processRequirements for
2553 -- Operation Pull components
2554
2555 -- set l_first_bf_op and l_last_bf_op back to -1
2556 l_first_bf_op := -1;
2557 l_last_bf_op := -1;
2558 wma_move.bf_require(p_jobID => p_wipEntityID,
2559 p_fm_op => l_fm_op,
2560 p_fm_step => l_fm_step,
2561 p_to_op => l_to_op,
2562 p_to_step => l_to_step,
2563 p_moveQty => p_ocQty,
2564 x_first_bf_op => l_first_bf_op,
2565 x_last_bf_op => l_last_bf_op,
2566 x_bf_qty => l_bf_qty,
2567 x_returnStatus => l_returnStatus,
2568 x_errMessage => l_errMsg);
2569 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2570 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2571 fnd_message.set_token('MESSAGE', l_errMsg);
2572 fnd_msg_pub.add;
2573 raise fnd_api.g_exc_unexpected_error;
2574 END IF;
2575 IF(l_first_bf_op <> -1) THEN
2576 /**
2577 * Call backflush processor to insert record into MMTT
2578 * for each schedule found in l_rsa.
2579 * This is only for operation pull components.
2580 **/
2581 wip_bflProc_priv.processRequirements
2582 (p_wipEntityID => p_wipEntityID,
2583 p_wipEntityType => p_entityType,
2584 p_repSchedID => null,
2585 p_repLineID => null,
2586 p_cplTxnID => null,
2587 p_movTxnID => p_childMovTxnID,
2588 p_batchID => p_batchID,
2589 p_orgID => p_orgID,
2590 p_assyQty => l_bf_qty,
2591 p_txnDate => p_txnDate,
2592 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
2593 p_txnHdrID => p_txnHdrID,
2594 p_firstOp => l_first_bf_op,
2595 p_lastOP => l_last_bf_op,
2596 p_firstMoveOp => l_fm_op,
2597 p_lastMoveOp => l_to_op,
2598 p_batchSeq => l_batch_seq,
2599 p_lockFlag => p_lockFlag,
2600 p_mergeMode => fnd_api.g_false,
2601 p_reasonID => p_reasonID,
2602 p_reference => p_reference,
2603 p_initMsgList => fnd_api.g_false,
2604 p_endDebug => fnd_api.g_false,
2605 p_mtlTxnMode => p_mtlTxnMode,
2606 x_compTbl => l_compTbl,
2607 x_returnStatus => l_returnStatus);
2608 --Bug#16315556(FP of bug#16343002): Transaction_Batch_id is set for overcompletion as it is set to null in processRequirements
2609 -- This will ensure that components are not transacted if the assembly transaction fails
2610 UPDATE MTL_TRANSACTIONS_INTERFACE
2611 SET TRANSACTION_BATCH_ID=p_batchID,transaction_Batch_seq=l_batch_seq
2612 WHERE TRANSACTION_HEADER_ID=p_txnHdrID and transaction_source_type_id=5 and transaction_source_id=p_wipEntityID
2613 AND transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
2614 wip_constants.issnegc_action, wip_constants.retnegc_action);
2615
2616 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2617 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2618 raise fnd_api.g_exc_unexpected_error;
2619 END IF;
2620 END IF; -- l_first_bf_op <> -1
2621
2622 -- Call assy_pull_bf to derive first_bf_op, last_bf_op,
2623 -- and bf_qty before call wip_bflProc_priv.processRequirements
2624 -- for Assembly Pull components. This is only for Scrap txns
2625
2626 -- set l_first_bf_op and l_last_bf_op back to -1
2627 l_first_bf_op := -1;
2628 l_last_bf_op := -1;
2629
2630 wma_move.assy_pull_bf(p_jobID => p_wipEntityID,
2631 p_fm_op => l_fm_op,
2632 p_fm_step => l_fm_step,
2633 p_to_op => l_to_op,
2634 p_to_step => l_to_step,
2635 p_moveQty => p_ocQty,
2636 x_first_bf_op => l_first_bf_op,
2637 x_last_bf_op => l_last_bf_op,
2638 x_bf_qty => l_bf_qty,
2639 x_returnStatus => l_returnStatus,
2640 x_errMessage => l_errMsg);
2641
2642 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2643 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2644 fnd_message.set_token('MESSAGE', l_errMsg);
2645 fnd_msg_pub.add;
2646 raise fnd_api.g_exc_unexpected_error;
2647 END IF;
2648 IF(l_first_bf_op <> -1) THEN
2649 /**
2650 * Call backflush processor to insert record into MMTT
2651 * for each schedule found in l_rsa
2652 * This is only for assembly pull components.
2653 **/
2654 wip_bflProc_priv.processRequirements
2655 (p_wipEntityID => p_wipEntityID,
2656 p_wipEntityType => p_entityType,
2657 p_repSchedID => null,
2658 p_repLineID => null,
2659 p_cplTxnID => null,
2660 p_movTxnID => p_childMovTxnID,
2661 p_batchID => p_batchID,
2662 p_orgID => p_orgID,
2663 p_assyQty => l_bf_qty,
2664 p_txnDate => p_txnDate,
2665 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2666 p_txnHdrID => p_txnHdrID,
2667 p_firstOp => l_first_bf_op,
2668 p_lastOP => l_last_bf_op,
2669 p_firstMoveOp => l_fm_op,
2670 p_lastMoveOp => l_to_op,
2671 p_lockFlag => p_lockFlag,
2672 p_batchSeq => l_batch_seq,
2673 p_mergeMode => fnd_api.g_false,
2674 p_reasonID => p_reasonID,
2675 p_reference => p_reference,
2676 p_initMsgList => fnd_api.g_false,
2677 p_endDebug => fnd_api.g_false,
2678 p_mtlTxnMode => p_mtlTxnMode,
2679 x_compTbl => l_compTbl,
2680 x_returnStatus => l_returnStatus);
2681
2682 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2683 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2684 raise fnd_api.g_exc_unexpected_error;
2685 END IF;
2686 END IF; -- l_first_bf_op <> -1
2687 END IF; -- Overmove/ Overcompletion
2688 END IF; -- Repetive schedule
2689
2690
2691 --Updated as per Bug#9544120
2692 SELECT COUNT(*)
2693 INTO l_bf_count
2694 FROM mtl_transactions_interface mti
2695 WHERE mti.transaction_header_id = p_txnHdrID
2696 AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2697 WIP_CONSTANTS.RETCOMP_ACTION,
2698 WIP_CONSTANTS.ISSNEGC_ACTION,
2699 WIP_CONSTANTS.RETNEGC_ACTION)
2700 AND (mti.MOVE_TRANSACTION_ID = p_movTxnID OR mti.COMPLETION_TRANSACTION_ID = p_cplTxnID)
2701 AND EXISTS (SELECT 1
2702 FROM wip_requirement_operations wro
2703 ,wip_entities we
2704 WHERE wro.wip_entity_id = mti.transaction_source_id
2705 AND wro.organization_id = mti.organization_id
2706 AND wro.wip_entity_id = we.wip_entity_id
2707 AND wro.inventory_item_id = mti.inventory_item_id
2708 AND wro.operation_seq_num = mti.operation_seq_num
2709 AND wro.organization_id = we.organization_id
2710 AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2711 we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2712 (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2713 );
2714
2715 -- Bug 9907143: updated for serial tagging enhancement for SUN
2716 /*
2717 SELECT COUNT(*)
2718 INTO l_lot_ser_count
2719 FROM mtl_transactions_interface mti,
2720 mtl_system_items msi
2721 WHERE mti.organization_id = msi.organization_id
2722 AND mti.inventory_item_id = msi.inventory_item_id
2723 AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2724 OR
2725 msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2726 WIP_CONSTANTS.DYN_RCV_SN))
2727 AND transaction_header_id = p_txnHdrID
2728 AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729 WIP_CONSTANTS.RETCOMP_ACTION,
2730 WIP_CONSTANTS.ISSNEGC_ACTION,
2731 WIP_CONSTANTS.RETNEGC_ACTION);
2732 */
2733 --Updated as per Bug#9544120
2734 SELECT COUNT(*)
2735 INTO l_lot_ser_count
2736 FROM mtl_transactions_interface mti,
2737 mtl_system_items msi
2738 WHERE mti.organization_id = msi.organization_id
2739 AND mti.inventory_item_id = msi.inventory_item_id
2740 AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2741 OR
2742 msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2743 WIP_CONSTANTS.DYN_RCV_SN)
2744 OR
2745 inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746 AND mti.transaction_header_id = p_txnHdrID
2747 AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2748 WIP_CONSTANTS.RETCOMP_ACTION,
2749 WIP_CONSTANTS.ISSNEGC_ACTION,
2750 WIP_CONSTANTS.RETNEGC_ACTION)
2751 AND EXISTS (SELECT 1
2752 FROM wip_requirement_operations wro
2753 ,wip_entities we
2754 WHERE wro.wip_entity_id = mti.transaction_source_id
2755 AND wro.organization_id = mti.organization_id
2756 AND wro.wip_entity_id = we.wip_entity_id
2757 AND wro.inventory_item_id = mti.inventory_item_id
2758 AND wro.operation_seq_num = mti.operation_seq_num
2759 AND wro.organization_id = we.organization_id
2760 AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2761 we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2762 (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2763 );
2764
2765 SELECT backflush_lot_entry_type
2766 INTO l_lot_entry_type
2767 FROM wip_parameters
2768 WHERE organization_id = p_orgID;
2769
2770 IF(l_bf_count = 0) THEN
2771 -- There is no backflush components required for this transaction
2772 x_bfRequired := WIP_CONSTANTS.WBF_NOBF;
2773 x_lotSerRequired := WIP_CONSTANTS.NO;
2774 ELSE
2775 IF(l_lot_ser_count = 0) THEN
2776 -- no component under lot/serial control
2777 x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2778 x_lotSerRequired := WIP_CONSTANTS.NO;
2779 ELSE
2780 IF(l_lot_entry_type = WIP_CONSTANTS.MAN_ENTRY) THEN
2781 -- If backflush lot entry type is set to manual, no need to do lot
2782 -- derivation
2783 x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2784 x_lotSerRequired := WIP_CONSTANTS.YES;
2785 ELSE
2786 -- derive lot for both Operation Pull and Assembly Pull components
2787 wip_autoLotProc_priv.deriveLotsFromMTI
2788 (p_orgID => p_orgID,
2789 p_wipEntityID => p_wipEntityID,
2790 p_txnHdrID => p_txnHdrID,
2791 p_cplTxnID => p_cplTxnID,
2792 p_movTxnID => p_movTxnID,
2793 p_childMovTxnID => p_childMovTxnID,
2794 p_initMsgList => fnd_api.g_false,
2795 p_endDebug => fnd_api.g_false,
2796 x_returnStatus => l_returnStatus);
2797 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
2798 l_errMsg := 'wip_autoLotProc_priv.deriveLotsFromMTI failed';
2799 raise fnd_api.g_exc_unexpected_error;
2800 ELSIF(l_returnStatus = fnd_api.g_ret_sts_error) THEN
2801 x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2802 x_lotSerRequired := WIP_CONSTANTS.YES;
2803 ELSE -- succesfully derived lot
2804 IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805 WIP_CONSTANTS.EXPDATE_FULL,
2806 /* Added for Wilson Greatbatch Enhancement */
2807 WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808 x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809 x_lotSerRequired := WIP_CONSTANTS.NO;
2810 ELSE -- backflush lot entry page is exception only
2811 x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2812 x_lotSerRequired := WIP_CONSTANTS.NO;
2813 END IF;
2814 END IF; -- check return status
2815 END IF; -- check lot entry type
2816 END IF; -- l_lot_ser_count = 0
2817 END IF; -- l_bf_count = 0
2818
2819 IF(p_tblName = WIP_CONSTANTS.MMTT_TBL) THEN
2820 -- Move record from mti to mmtt
2821 wip_mtlTempProc_priv.validateInterfaceTxns(
2822 p_txnHdrID => p_txnHdrID,
2823 p_addMsgToStack => fnd_api.g_true,
2824 p_initMsgList => fnd_api.g_true, /* Bug 5017345/5079379 - to initialize the message stack. */
2825 p_rollbackOnErr => fnd_api.g_false,
2826 x_returnStatus => x_returnStatus);
2827
2828 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
2829 l_errMsg := 'wip_mtlTempProc_priv.validateInterfaceTxns failed' ;
2830 raise fnd_api.g_exc_unexpected_error;
2831 END IF;
2832
2833 -- Insert all necessary info that need to be used by inventory form to
2834 -- gather lot/serial from the user
2835 IF(x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE)THEN
2836 UPDATE mtl_material_transactions_temp mmtt
2837 SET (mmtt.item_segments,
2838 mmtt.item_description,
2839 mmtt.item_trx_enabled_flag,
2840 mmtt.item_location_control_code,
2841 mmtt.item_restrict_subinv_code,
2842 mmtt.item_restrict_locators_code,
2843 mmtt.item_revision_qty_control_code,
2844 mmtt.item_primary_uom_code,
2845 mmtt.item_uom_class,
2846 mmtt.item_shelf_life_code,
2847 mmtt.item_shelf_life_days,
2848 mmtt.item_lot_control_code,
2849 mmtt.item_serial_control_code,
2850 mmtt.item_inventory_asset_flag,
2851 mmtt.number_of_lots_entered)
2852 =
2853 (SELECT msik.concatenated_segments,
2854 msik.description,
2855 msik.mtl_transactions_enabled_flag,
2856 msik.location_control_code,
2857 msik.restrict_subinventories_code,
2858 msik.restrict_locators_code,
2859 msik.revision_qty_control_code,
2860 msik.primary_uom_code,
2861 muom.uom_class,
2862 msik.shelf_life_code,
2863 msik.shelf_life_days,
2864 msik.lot_control_code,
2865 msik.serial_number_control_code,
2866 msik.inventory_asset_flag,
2867 mmtt.transaction_quantity
2868 FROM mtl_system_items_kfv msik,
2869 mtl_units_of_measure muom
2870 WHERE mmtt.organization_id = msik.organization_id
2871 AND mmtt.inventory_item_id = msik.inventory_item_id
2872 AND msik.primary_uom_code = muom.uom_code)
2873 WHERE mmtt.transaction_header_id = p_txnHdrID
2874 -- Added the check below because OSFM will call this API twice.
2875 AND mmtt.number_of_lots_entered IS NULL;
2876
2877 /* Fixed bug 3771273. mmtt.department_id may be null if a job has no
2878 * routing. We should seperate statement to set mmtt.department_code
2879 * from statement to update other columns.
2880 */
2881 -- set department code if mmtt.department_id is not null
2882 UPDATE mtl_material_transactions_temp mmtt
2883 SET (mmtt.department_code)
2884 =
2885 (SELECT bd.department_code
2886 FROM bom_departments bd
2887 WHERE bd.organization_id = mmtt.organization_id
2888 AND bd.department_id = mmtt.department_id
2889 )
2890 WHERE mmtt.transaction_header_id = p_txnHdrID
2891 AND mmtt.department_id IS NOT NULL;
2892
2893 /*Update MMTT Lot number if only one lot is derived */
2894 IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2895 WIP_CONSTANTS.EXPDATE_FULL,
2896 WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2897
2898 UPDATE mtl_material_transactions_temp mmtt
2899 SET mmtt.lot_number =
2900 ( SELECT mtlt.lot_number
2901 FROM mtl_transaction_lots_temp mtlt
2902 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
2903 AND 1 = ( SELECT count(*)
2904 FROM mtl_transaction_lots_temp mtlt
2905 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
2906 )
2907 WHERE mmtt.transaction_header_id = p_txnHdrID
2908 AND mmtt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2909 WIP_CONSTANTS.RETCOMP_ACTION,
2910 WIP_CONSTANTS.ISSNEGC_ACTION,
2911 WIP_CONSTANTS.RETNEGC_ACTION);
2912
2913 /* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement from below */
2914 DELETE FROM mtl_transaction_lots_temp mtlt
2915 WHERE EXISTS
2916 (SELECT 'x'
2917 FROM mtl_material_transactions_temp mmtt,
2918 wip_entities we
2919 WHERE mmtt.transaction_header_id = p_txnHdrID
2920 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
2921 AND mmtt.transaction_source_id = we.wip_entity_id
2922 AND we.entity_type = wip_constants.lotbased
2923 AND 1 = (SELECT count(*)
2924 FROM mtl_transaction_lots_temp mtlt2
2925 WHERE mtlt2.transaction_temp_id =
2926 mtlt.transaction_temp_id
2927 ));
2928 END IF;
2929 /* Fixed bug 4121977. One transaction header ID can have multiple temp ID.
2930 This cause select count(*) to throw single-row subquery returns more
2931 than one row exception. */
2932 /*
2933 DELETE FROM mtl_transaction_lots_temp mtlt
2934 WHERE mtlt.transaction_temp_id =
2935 ( SELECT mmtt.transaction_temp_id
2936 FROM mtl_material_transactions_temp mmtt,
2937 wip_entities we
2938 WHERE mmtt.transaction_header_id = p_txnHdrID
2939 AND 1 = ( SELECT count(*)
2940 FROM mtl_transaction_lots_temp mtlt
2941 WHERE mmtt.transaction_temp_id =
2942 mtlt.transaction_temp_id
2943 )
2944 AND mmtt.transaction_source_id = we.wip_entity_id
2945 AND we.entity_type = wip_constants.lotbased
2946 ) ;
2947 */
2948 /* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement above inside the IF condition. The rows should be deleted from MTLT
2949 only for the case of Lot Verification=All after the lot has been stamped on MMTT. For Lot Verification=Exception Only
2950 there is no need to delete since the component rows will not be visible in the UI.
2951 DELETE FROM mtl_transaction_lots_temp mtlt
2952 WHERE EXISTS
2953 (SELECT 'x'
2954 FROM mtl_material_transactions_temp mmtt,
2955 wip_entities we
2956 WHERE mmtt.transaction_header_id = p_txnHdrID
2957 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
2958 AND mmtt.transaction_source_id = we.wip_entity_id
2959 AND we.entity_type = wip_constants.lotbased
2960 AND 1 = (SELECT count(*)
2961 FROM mtl_transaction_lots_temp mtlt2
2962 WHERE mtlt2.transaction_temp_id =
2963 mtlt.transaction_temp_id
2964 ));
2965 */
2966 -- Comment out statement below because we will use color to determine whether
2967 -- user need to provide more lot/serial information or not from J onward.
2968 -- In the past, we use transaction quantity to represent the lot quantity
2969 -- we can derive. If it is not equal to required quantity, user need to provide
2970 -- more lot/serial information.
2971 /*
2972 -- Only update transaction_quantity for item under lot/serial.
2973 UPDATE mtl_material_transactions_temp mmtt
2974 SET mmtt.transaction_quantity =
2975 (SELECT NVL(SUM(mtlt.transaction_quantity),0)
2976 FROM mtl_transaction_lots_temp mtlt
2977 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
2978 WHERE mmtt.transaction_header_id = p_txnHdrID
2979 AND (mmtt.item_serial_control_code IN(WIP_CONSTANTS.FULL_SN,
2980 WIP_CONSTANTS.DYN_RCV_SN)
2981 OR
2982 mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
2983 */
2984 END IF; -- x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE
2985 END IF; -- WIP_CONSTANTS.MMTT_TBL
2986
2987 x_returnStatus := fnd_api.g_ret_sts_success;
2988 IF (l_logLevel <= wip_constants.trace_logging) THEN
2989 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
2990 p_procReturnStatus => x_returnStatus,
2991 p_msg => 'Succesfully inserted components into MMTT',
2992 x_returnStatus => l_returnStatus);
2993 END IF;
2994 EXCEPTION
2995 WHEN fnd_api.g_exc_unexpected_error THEN
2996 ROLLBACK TO SAVEPOINT s_backflush;
2997 x_returnStatus := fnd_api.g_ret_sts_error;
2998 IF (l_logLevel <= wip_constants.trace_logging) THEN
2999 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3000 p_procReturnStatus => x_returnStatus,
3001 p_msg => l_errMsg,
3002 x_returnStatus => l_returnStatus);
3003 END IF;
3004 WHEN others THEN
3005 ROLLBACK TO SAVEPOINT s_backflush;
3006 x_returnStatus := fnd_api.g_ret_sts_error;
3007 IF (l_logLevel <= wip_constants.trace_logging) THEN
3008 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3009 p_procReturnStatus => x_returnStatus,
3010 p_msg => 'Unexpected error : ' || SQLERRM,
3011 x_returnStatus => l_returnStatus);
3012 END IF;
3013 END backflush;
3014
3015 FUNCTION NegLSCompExist(p_compInfo IN OUT NOCOPY system.wip_lot_serial_obj_t)
3016 RETURN NUMBER IS
3017
3018 l_curItem system.wip_component_obj_t;
3019 BEGIN
3020 LOOP
3021 IF(p_compInfo.getCurrentItem(l_curItem)) THEN
3022 IF(l_curItem.transaction_action_id = WIP_CONSTANTS.RETNEGC_ACTION
3023 AND
3024 (l_curItem.lot_control_code = WIP_CONSTANTS.LOT
3025 OR
3026 l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
3027 WIP_CONSTANTS.DYN_RCV_SN))
3028 ) THEN
3029 -- Return after the first negative lot/serial component found.
3030 RETURN WIP_CONSTANTS.YES;
3031 END IF;
3032 END IF; -- getCurrentItem
3033 EXIT WHEN NOT p_compInfo.setNextItem;
3034 END LOOP;
3035 -- No negative lot/serial component.
3036 RETURN WIP_CONSTANTS.NO;
3037 END NegLSCompExist;
3038
3039 PROCEDURE backflush(p_wipEntityID IN NUMBER,
3040 p_orgID IN NUMBER,
3041 p_primaryQty IN NUMBER,
3042 p_txnDate IN DATE,
3043 p_txnHdrID IN NUMBER,
3044 p_txnType IN NUMBER,
3045 p_entityType IN NUMBER,
3046 p_fmOp IN NUMBER:= NULL,
3047 p_fmStep IN NUMBER:= NULL,
3048 p_toOp IN NUMBER:= NULL,
3049 p_toStep IN NUMBER:= NULL,
3050 p_ocQty IN NUMBER:= NULL,
3051 p_childMovTxnID IN NUMBER:= NULL,
3052 p_movTxnID IN NUMBER:= NULL,
3053 p_cplTxnID IN NUMBER:= NULL,
3054 p_objectID IN NUMBER:= NULL,
3055 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
3056 x_lotSerRequired OUT NOCOPY NUMBER,
3057 x_returnStatus OUT NOCOPY VARCHAR2) IS
3058
3059 l_params wip_logger.param_tbl_t;
3060 l_returnStatus VARCHAR(1);
3061 l_errMsg VARCHAR2(240);
3062 l_fm_op NUMBER;
3063 l_fm_step NUMBER;
3064 l_to_op NUMBER;
3065 l_to_step NUMBER;
3066 l_first_op NUMBER;
3067 l_last_op NUMBER;
3068 l_first_bf_op NUMBER;
3069 l_last_bf_op NUMBER;
3070 l_bf_qty NUMBER;
3071 l_compTbl system.wip_component_tbl_t;
3072 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3073 l_backwardMove NUMBER := WIP_CONSTANTS.NO;
3074 BEGIN
3075 SAVEPOINT s_backflush2;
3076 -- write parameter value to log file
3077 IF (l_logLevel <= wip_constants.trace_logging) THEN
3078 l_params(1).paramName := 'p_wipEntityID';
3079 l_params(1).paramValue := p_wipEntityID;
3080 l_params(2).paramName := 'p_orgID';
3081 l_params(2).paramValue := p_orgID;
3082 l_params(3).paramName := 'p_primaryQty';
3083 l_params(3).paramValue := p_primaryQty;
3084 l_params(4).paramName := 'p_txnDate';
3085 l_params(4).paramValue := p_txnDate;
3086 l_params(5).paramName := 'p_txnHdrID';
3087 l_params(5).paramValue := p_txnHdrID;
3088 l_params(6).paramName := 'p_txnType';
3089 l_params(6).paramValue := p_txnType;
3090 l_params(7).paramName := 'p_entityType';
3091 l_params(7).paramValue := p_entityType;
3092 l_params(8).paramName := 'p_fmOp';
3093 l_params(8).paramValue := p_fmOp;
3094 l_params(9).paramName := 'p_fmStep';
3095 l_params(9).paramValue := p_fmStep;
3096 l_params(10).paramName := 'p_toOp';
3097 l_params(10).paramValue := p_toOp;
3098 l_params(11).paramName := 'p_toStep';
3099 l_params(11).paramValue := p_toStep;
3100 l_params(12).paramName := 'p_ocQty';
3101 l_params(12).paramValue := p_ocQty;
3102 l_params(13).paramName := 'p_childMovTxnID';
3103 l_params(13).paramValue := p_childMovTxnID;
3104 l_params(14).paramName := 'p_movTxnID';
3105 l_params(14).paramValue := p_movTxnID;
3106
3107 wip_logger.entryPoint(p_procName => 'wip_bflProc_priv.backflush',
3108 p_params => l_params,
3109 x_returnStatus => l_returnStatus);
3110 END IF;
3111
3112 SELECT MIN(wo.operation_seq_num),
3113 MAX(wo.operation_seq_num)
3114 INTO l_first_op,
3115 l_last_op
3116 FROM wip_operations wo,
3117 wip_discrete_jobs wdj
3118 WHERE wdj.organization_id = wo.organization_id
3119 AND wdj.wip_entity_id = wo.wip_entity_id
3120 AND wdj.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
3121 AND wdj.wip_entity_id = p_wipEntityID
3122 AND wdj.organization_id = p_orgID;
3123
3124 IF(l_first_op IS NULL) THEN
3125 -- Routingless job
3126 l_first_op := 0;
3127 END IF;
3128 IF(l_last_op IS NULL) THEN
3129 -- Routingless job
3130 l_last_op := 1.1;
3131 END IF;
3132
3133 -- Initialized component object
3134 l_compTbl := system.wip_component_tbl_t();
3135
3136 IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
3137 p_toOp IS NULL OR p_toStep IS NULL) THEN
3138 -- Completion/return transactions
3139 IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3140 -- Completion transaction
3141 l_fm_op := l_last_op;
3142 l_fm_step := WIP_CONSTANTS.TOMOVE;
3143 l_to_op := NULL;
3144 l_to_step := NULL;
3145 ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3146 -- Return transaction
3147 l_fm_op := NULL;
3148 l_fm_step := NULL;
3149 l_to_op := l_last_op;
3150 l_to_step := WIP_CONSTANTS.TOMOVE;
3151 END IF;
3152 ELSE -- Move related transactions
3153 l_fm_op := p_fmOp;
3154 l_fm_step := p_fmStep;
3155 l_to_op := p_toOp;
3156 l_to_step := p_toStep;
3157 -- Check whether it is a backward move
3158 IF (l_fm_op > l_to_op OR
3159 (l_fm_op = l_to_op AND l_fm_step > l_to_step)) THEN
3160 l_backwardMove := WIP_CONSTANTS.YES;
3161 ELSE
3162 l_backwardMove := WIP_CONSTANTS.NO;
3163 END IF;
3164 END IF;
3165
3166 -- Check whether it is move related transactions or not
3167 IF(l_fm_op IS NOT NULL AND l_to_op IS NOT NULL) THEN
3168 -- set l_first_bf_op and l_last_bf_op back to -1
3169 l_first_bf_op := -1;
3170 l_last_bf_op := -1;
3171 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty
3172 -- before call wip_bflProc_priv.processRequirements for
3173 -- Operation Pull components
3174 wma_move.bf_require(p_jobID => p_wipEntityID,
3175 p_fm_op => l_fm_op,
3176 p_fm_step => l_fm_step,
3177 p_to_op => l_to_op,
3178 p_to_step => l_to_step,
3179 p_moveQty => p_primaryQty,
3180 x_first_bf_op => l_first_bf_op,
3181 x_last_bf_op => l_last_bf_op,
3182 x_bf_qty => l_bf_qty,
3183 x_returnStatus => l_returnStatus,
3184 x_errMessage => l_errMsg);
3185 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3186 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
3187 fnd_message.set_token('MESSAGE', l_errMsg);
3188 fnd_msg_pub.add;
3189 raise fnd_api.g_exc_unexpected_error;
3190 END IF;
3191 IF(l_first_bf_op <> -1) THEN
3192 wip_bflProc_priv.processRequirements
3193 (p_wipEntityID => p_wipEntityID,
3194 p_wipEntityType => p_entityType,
3195 p_cplTxnID => null,
3196 p_movTxnID => p_movTxnID,
3197 p_orgID => p_orgID,
3198 p_assyQty => l_bf_qty,
3199 p_txnDate => p_txnDate,
3200 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
3201 p_txnHdrID => p_txnHdrID,
3202 p_firstOp => l_first_bf_op,
3203 p_lastOP => l_last_bf_op,
3204 p_firstMoveOp => l_fm_op,
3205 p_lastMoveOp => l_to_op,
3206 p_mergeMode => fnd_api.g_false,
3207 p_initMsgList => fnd_api.g_false,
3208 p_endDebug => fnd_api.g_false,
3209 p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3210 x_compTbl => l_compTbl,
3211 x_returnStatus => l_returnStatus);
3212
3213 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3214 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
3215 raise fnd_api.g_exc_unexpected_error;
3216 END IF;
3217 END IF; -- l_first_bf_op <> -1
3218
3219 -- Call assy_pull_bf to derive first_bf_op, last_bf_op,
3220 -- and bf_qty before call wip_bflProc_priv.processRequirements
3221 -- for Assembly Pull components. This is only for Scrap txns
3222
3223 -- set l_first_bf_op and l_last_bf_op back to -1
3224 l_first_bf_op := -1;
3225 l_last_bf_op := -1;
3226
3227 wma_move.assy_pull_bf(p_jobID => p_wipEntityID,
3228 p_fm_op => l_fm_op,
3229 p_fm_step => l_fm_step,
3230 p_to_op => l_to_op,
3231 p_to_step => l_to_step,
3232 p_moveQty => p_primaryQty,
3233 x_first_bf_op => l_first_bf_op,
3234 x_last_bf_op => l_last_bf_op,
3235 x_bf_qty => l_bf_qty,
3236 x_returnStatus => l_returnStatus,
3237 x_errMessage => l_errMsg);
3238
3239 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3240 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
3241 fnd_message.set_token('MESSAGE', l_errMsg);
3242 fnd_msg_pub.add;
3243 raise fnd_api.g_exc_unexpected_error;
3244 END IF;
3245 IF(l_first_bf_op <> -1) THEN
3246 wip_bflProc_priv.processRequirements
3247 (p_wipEntityID => p_wipEntityID,
3248 p_wipEntityType => p_entityType,
3249 p_cplTxnID => null,
3250 p_movTxnID => p_movTxnID,
3251 p_orgID => p_orgID,
3252 p_assyQty => l_bf_qty,
3253 p_txnDate => p_txnDate,
3254 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
3255 p_txnHdrID => p_txnHdrID,
3256 p_firstOp => l_first_bf_op,
3257 p_lastOP => l_last_bf_op,
3258 p_firstMoveOp => l_fm_op,
3259 p_lastMoveOp => l_to_op,
3260 p_mergeMode => fnd_api.g_false,
3261 p_initMsgList => fnd_api.g_false,
3262 p_endDebug => fnd_api.g_false,
3263 p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3264 x_compTbl => l_compTbl,
3265 x_returnStatus => l_returnStatus);
3266
3267 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3268 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
3269 raise fnd_api.g_exc_unexpected_error;
3270 END IF;
3271 END IF; -- l_first_bf_op <> -1
3272 END IF; -- Move related transactions
3273
3274 IF(p_txnType = WIP_CONSTANTS.COMP_TXN OR
3275 p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3276
3277 IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3278 l_bf_qty := p_primaryQty;
3279 ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3280 l_bf_qty := -1 * p_primaryQty;
3281 END IF;
3282 wip_bflProc_priv.processRequirements
3283 (p_wipEntityID => p_wipEntityID,
3284 p_wipEntityType => p_entityType,
3285 p_cplTxnID => p_cplTxnID,
3286 -- Fixed bug 5014211. Stamp move_transaction_id for assembly
3287 -- pull components so that we will have a link if component
3288 -- records fail inventory validation.
3289 p_movTxnID => p_movTxnID,
3290 p_orgID => p_orgID,
3291 p_assyQty => l_bf_qty,
3292 p_txnDate => p_txnDate,
3293 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
3294 p_txnHdrID => p_txnHdrID,
3295 p_firstOp => -1,
3296 p_lastOP => l_last_op,
3297 p_firstMoveOp => null,
3298 p_lastMoveOp => null,
3299 p_mergeMode => fnd_api.g_false,
3300 p_initMsgList => fnd_api.g_false,
3301 p_endDebug => fnd_api.g_false,
3302 p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3303 x_compTbl => l_compTbl,
3304 x_returnStatus => l_returnStatus);
3305
3306 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3307 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
3308 raise fnd_api.g_exc_unexpected_error;
3309 END IF;
3310 END IF; -- Completion/Return/EZ Completion/EZ Return
3311
3312 -- Check whether overcompletion
3313 IF(p_childMovTxnID IS NOT NULL AND p_ocQty IS NOT NULL) THEN
3314 -- overmove/overcomplete
3315 l_fm_op := l_first_op;
3316 l_fm_step := WIP_CONSTANTS.QUEUE;
3317 IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
3318 p_toOp IS NULL OR p_toStep IS NULL) THEN
3319 -- Call from Completion form
3320 l_to_op := l_last_op;
3321 l_to_step := WIP_CONSTANTS.TOMOVE;
3322 ELSE -- Call from WIP Move or OSFM Move forms
3323 l_to_op := p_fmOp;
3324 l_to_step := p_fmStep;
3325 END IF;
3326 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty
3327 -- before call wip_bflProc_priv.processRequirements for
3328 -- Operation Pull components
3329
3330 -- set l_first_bf_op and l_last_bf_op back to -1
3331 l_first_bf_op := -1;
3332 l_last_bf_op := -1;
3333 wma_move.bf_require(p_jobID => p_wipEntityID,
3334 p_fm_op => l_fm_op,
3335 p_fm_step => l_fm_step,
3336 p_to_op => l_to_op,
3337 p_to_step => l_to_step,
3338 p_moveQty => p_ocQty,
3339 x_first_bf_op => l_first_bf_op,
3340 x_last_bf_op => l_last_bf_op,
3341 x_bf_qty => l_bf_qty,
3342 x_returnStatus => l_returnStatus,
3343 x_errMessage => l_errMsg);
3344 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3345 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
3346 fnd_message.set_token('MESSAGE', l_errMsg);
3347 fnd_msg_pub.add;
3348 raise fnd_api.g_exc_unexpected_error;
3349 END IF;
3350 IF(l_first_bf_op <> -1) THEN
3351 wip_bflProc_priv.processRequirements
3352 (p_wipEntityID => p_wipEntityID,
3353 p_wipEntityType => p_entityType,
3354 p_cplTxnID => null,
3355 p_movTxnID => p_childMovTxnID,
3356 p_orgID => p_orgID,
3357 p_assyQty => l_bf_qty,
3358 p_txnDate => p_txnDate,
3359 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
3360 p_txnHdrID => p_txnHdrID,
3361 p_firstOp => l_first_bf_op,
3362 p_lastOP => l_last_bf_op,
3363 p_firstMoveOp => l_fm_op,
3364 p_lastMoveOp => l_to_op,
3365 p_mergeMode => fnd_api.g_false,
3366 p_initMsgList => fnd_api.g_false,
3367 p_endDebug => fnd_api.g_false,
3368 p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3369 x_compTbl => l_compTbl,
3370 x_returnStatus => l_returnStatus);
3371
3372 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3373 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
3374 raise fnd_api.g_exc_unexpected_error;
3375 END IF;
3376 END IF; -- l_first_bf_op <> -1
3377
3378 -- Call assy_pull_bf to derive first_bf_op, last_bf_op,
3379 -- and bf_qty before call wip_bflProc_priv.processRequirements
3380 -- for Assembly Pull components. This is only for Scrap txns
3381
3382 -- set l_first_bf_op and l_last_bf_op back to -1
3383 l_first_bf_op := -1;
3384 l_last_bf_op := -1;
3385
3386 wma_move.assy_pull_bf(p_jobID => p_wipEntityID,
3387 p_fm_op => l_fm_op,
3388 p_fm_step => l_fm_step,
3389 p_to_op => l_to_op,
3390 p_to_step => l_to_step,
3391 p_moveQty => p_ocQty,
3392 x_first_bf_op => l_first_bf_op,
3393 x_last_bf_op => l_last_bf_op,
3394 x_bf_qty => l_bf_qty,
3395 x_returnStatus => l_returnStatus,
3396 x_errMessage => l_errMsg);
3397
3398 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3399 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
3400 fnd_message.set_token('MESSAGE', l_errMsg);
3401 fnd_msg_pub.add;
3402 raise fnd_api.g_exc_unexpected_error;
3403 END IF;
3404 IF(l_first_bf_op <> -1) THEN
3405 wip_bflProc_priv.processRequirements
3406 (p_wipEntityID => p_wipEntityID,
3407 p_wipEntityType => p_entityType,
3408 p_cplTxnID => null,
3409 p_movTxnID => p_childMovTxnID,
3410 p_orgID => p_orgID,
3411 p_assyQty => l_bf_qty,
3412 p_txnDate => p_txnDate,
3413 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
3414 p_txnHdrID => p_txnHdrID,
3415 p_firstOp => l_first_bf_op,
3416 p_lastOP => l_last_bf_op,
3417 p_firstMoveOp => l_fm_op,
3418 p_lastMoveOp => l_to_op,
3419 p_mergeMode => fnd_api.g_false,
3420 p_initMsgList => fnd_api.g_false,
3421 p_endDebug => fnd_api.g_false,
3422 p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3423 x_compTbl => l_compTbl,
3424 x_returnStatus => l_returnStatus);
3425
3426 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3427 l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
3428 raise fnd_api.g_exc_unexpected_error;
3429 END IF;
3430 END IF; -- l_first_bf_op <> -1
3431 END IF; -- Overmove/ Overcompletion
3432
3433 if (l_logLevel <= wip_constants.full_logging) then
3434 wip_logger.log(p_msg => 'before system.wip_lot_serial_obj_t',
3435 x_returnStatus => l_returnStatus);
3436 end if;
3437 x_compInfo := system.wip_lot_serial_obj_t(null, null, null, l_compTbl,
3438 null, null);
3439 if (l_logLevel <= wip_constants.full_logging) then
3440 wip_logger.log(p_msg => 'after system.wip_lot_serial_obj_t',
3441 x_returnStatus => l_returnStatus);
3442 end if;
3443 x_compInfo.initialize;
3444 if (l_logLevel <= wip_constants.full_logging) then
3445 wip_logger.log(p_msg => 'after x_compInfo.initialize',
3446 x_returnStatus => l_returnStatus);
3447 end if;
3448
3449 -- If serialized return or serialized backward move, we have to derive
3450 -- lot and serial information from genealogy.
3451 IF (p_objectID IS NOT NULL AND
3452 (p_txnType = WIP_CONSTANTS.RET_TXN OR
3453 l_backwardMove = WIP_CONSTANTS.YES)) THEN
3454 -- Derive lot control only from genealogy
3455 wip_autoLotProc_priv.deriveLotsFromMOG(x_compLots => x_compInfo,
3456 p_orgID => p_orgID,
3457 p_objectID => p_objectID,
3458 p_initMsgList => fnd_api.g_true,
3459 x_returnStatus => x_returnStatus);
3460
3461 IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
3462 raise fnd_api.g_exc_unexpected_error;
3463 END IF;
3464 -- Derive serial, and lot and serial from genealogy
3465 wip_autoSerialProc_priv.deriveSerial(x_compLots => x_compInfo,
3466 p_orgID => p_orgID,
3467 p_objectID => p_objectID,
3468 p_initMsgList => fnd_api.g_true,
3469 x_returnStatus => x_returnStatus);
3470
3471 IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
3472 raise fnd_api.g_exc_unexpected_error;
3473 END IF;
3474
3475 IF(NegLSCompExist(p_compInfo => x_compInfo) = WIP_CONSTANTS.YES) THEN
3476 x_lotSerRequired := WIP_CONSTANTS.YES;
3477 ELSE
3478 x_lotSerRequired := WIP_CONSTANTS.NO;
3479 END IF;
3480 ELSE
3481 -- derive lot if the component under lot control, if return status
3482 -- is 'E' mean cannot derive lot, so the user need to provide more
3483 -- info.
3484 wip_autoLotProc_priv.deriveLots(
3485 x_compLots => x_compInfo,
3486 p_orgID => p_orgID,
3487 p_wipEntityID => p_wipEntityID,
3488 p_initMsgList => fnd_api.g_true,
3489 p_endDebug => fnd_api.g_false,
3490 p_destroyTrees => fnd_api.g_true,
3491 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
3492 p_treeSrcName => null,
3493 x_returnStatus => x_returnStatus);
3494
3495 IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
3496 l_errMsg := 'wip_autoLotProc_priv.deriveLots failed';
3497 raise fnd_api.g_exc_unexpected_error;
3498 ELSIF(x_returnStatus = fnd_api.g_ret_sts_error) THEN
3499 x_lotSerRequired := WIP_CONSTANTS.YES;
3500 ELSE -- succesfully derived lot
3501 x_lotSerRequired := WIP_CONSTANTS.NO;
3502 END IF;-- check return status
3503 END IF; -- check serialized return or serialized backward move
3504
3505 x_returnStatus := fnd_api.g_ret_sts_success;
3506 IF (l_logLevel <= wip_constants.trace_logging) THEN
3507 wip_logger.exitPoint(
3508 p_procName => 'wip_bflProc_priv.backflush',
3509 p_procReturnStatus => x_returnStatus,
3510 p_msg => 'Succesfully inserted components into PL/SQL object',
3511 x_returnStatus => l_returnStatus);
3512 END IF;
3513 EXCEPTION
3514 WHEN fnd_api.g_exc_unexpected_error THEN
3515 ROLLBACK TO SAVEPOINT s_backflush2;
3516 x_returnStatus := fnd_api.g_ret_sts_error;
3517 IF (l_logLevel <= wip_constants.trace_logging) THEN
3518 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3519 p_procReturnStatus => x_returnStatus,
3520 p_msg => l_errMsg,
3521 x_returnStatus => l_returnStatus);
3522 END IF;
3523 WHEN others THEN
3524 ROLLBACK TO SAVEPOINT s_backflush2;
3525 x_returnStatus := fnd_api.g_ret_sts_error;
3526 IF (l_logLevel <= wip_constants.trace_logging) THEN
3527 wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3528 p_procReturnStatus => x_returnStatus,
3529 p_msg => 'Unexpected error : ' || SQLERRM,
3530 x_returnStatus => l_returnStatus);
3531 END IF;
3532 END backflush;
3533 end wip_bflProc_priv;