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