[Home] [Help]
89: msi.serial_number_control_code serialNumControlCode
90: from mtl_transactions_interface mti,
91: mtl_system_items_b msi
92: where mti.transaction_header_id = p_txnHdrID
93: and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
94: wip_constants.issnegc_action, wip_constants.retnegc_action)
95: and mti.operation_seq_num between p_firstOp and p_lastOp
96: and msi.inventory_item_id = mti.inventory_item_id
97: and msi.organization_id = mti.organization_id
90: from mtl_transactions_interface mti,
91: mtl_system_items_b msi
92: where mti.transaction_header_id = p_txnHdrID
93: and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
94: wip_constants.issnegc_action, wip_constants.retnegc_action)
95: and mti.operation_seq_num between p_firstOp and p_lastOp
96: and msi.inventory_item_id = mti.inventory_item_id
97: and msi.organization_id = mti.organization_id
98: and ( ( p_cplTxnID is null
164: and wro.organization_id = wdj.organization_id
165: and wro.wip_entity_id = wo.wip_entity_id (+)
166: and wro.operation_seq_num = wo.operation_seq_num (+)
167: /* added for OSFM jump enhancement 2541431 */
168: and nvl(wo.skip_flag, WIP_CONSTANTS.NO) <> WIP_CONSTANTS.YES
169: and wro.supply_locator_id = mil.inventory_location_id (+)
170: and wro.organization_id = mil.organization_id (+)
171: and wro.supply_locator_id = milk.inventory_location_id (+)
172: and wro.organization_id = milk.organization_id (+)
292: if(fnd_api.to_boolean(p_initMsgList)) then
293: fnd_msg_pub.initialize;
294: end if;
295:
296: if (l_logLevel <= wip_constants.trace_logging) then
297: l_params(1).paramName := 'p_wipEntityID';
298: l_params(1).paramValue := p_wipEntityID;
299: l_params(2).paramName := 'p_wipEntityType';
300: l_params(2).paramValue := p_wipEntityType;
351: wip_common.Get_Released_Revs_Type_Meaning (l_released_revs_type,
352: l_released_revs_meaning
353: );
354:
355: if(p_wipEntityType = wip_constants.repetitive) then
356: if (l_logLevel <= wip_constants.full_logging) then
357: wip_logger.log('processing repetitive...',l_returnStatus);
358: end if;
359: --select the highest operation where the backflush flag is set
352: l_released_revs_meaning
353: );
354:
355: if(p_wipEntityType = wip_constants.repetitive) then
356: if (l_logLevel <= wip_constants.full_logging) then
357: wip_logger.log('processing repetitive...',l_returnStatus);
358: end if;
359: --select the highest operation where the backflush flag is set
360: open c_repReqs;
358: end if;
359: --select the highest operation where the backflush flag is set
360: open c_repReqs;
361: else
362: if (l_logLevel <= wip_constants.full_logging) then
363: wip_logger.log('processing discrete...',l_returnStatus);
364: end if;
365: open c_discReqs;
366: end if;
388:
389: /*Fix bug 9968707 (FP of 9765009)*/
390: if(p_movTxnID is null) then
391: l_batch_id := p_batchID;
392: l_batch_seq := nvl(p_batchSeq,wip_constants.component_batch_seq);
393: end if;
394:
395: loop
396: if(p_repSchedID is not null) then
403: exit when l_reqRec.opSeqNum > p_lastOp;
404:
405: if(p_assyQty > 0) then --completion, forward move
406: if(l_reqRec.qtyPerAssy > 0) then
407: l_txnActionID := wip_constants.isscomp_action;
408: else
409: l_txnActionID := wip_constants.issnegc_action;
410: end if;
411: else --return, backward move
405: if(p_assyQty > 0) then --completion, forward move
406: if(l_reqRec.qtyPerAssy > 0) then
407: l_txnActionID := wip_constants.isscomp_action;
408: else
409: l_txnActionID := wip_constants.issnegc_action;
410: end if;
411: else --return, backward move
412: if(l_reqRec.qtyPerAssy > 0) then
413: l_txnActionID := wip_constants.retcomp_action;
409: l_txnActionID := wip_constants.issnegc_action;
410: end if;
411: else --return, backward move
412: if(l_reqRec.qtyPerAssy > 0) then
413: l_txnActionID := wip_constants.retcomp_action;
414: else
415: l_txnActionID := wip_constants.retnegc_action;
416: end if;
417: end if;
411: else --return, backward move
412: if(l_reqRec.qtyPerAssy > 0) then
413: l_txnActionID := wip_constants.retcomp_action;
414: else
415: l_txnActionID := wip_constants.retnegc_action;
416: end if;
417: end if;
418:
419: --this loop tries to find an existing record for the backflush component. It exits when it finds one (= condition) or
436: --if this isn't an autocharge operation and the operation seq num isn't the one being moved into, then
437: --skip inserting requirements.
438: --This is the only check we have to do since operations w/assy pull requirements must be autocharge and
439: --moves will always pass p_firstMoveOp and p_lastMoveOp
440: if(l_reqRec.countPointType = wip_constants.no_direct and
441: l_reqRec.opSeqNum not in (p_firstMoveOp, p_lastMoveOp)) then
442: goto end_of_loop;
443: end if;
444:
452: from wip_parameters
453: where organization_id = p_orgID;
454:
455:
456: if (l_reqRec.basisType = WIP_CONSTANTS.LOT_BASED_MTL) then
457:
458: -- forward move
459:
460: if (l_reqRec.qtyIssued = 0 and p_assyQty > 0) then
464: -- backflush the entire quantity stored in wro, since it is calculated based on
465: -- comp yield at job component definition time
466:
467: l_compQty := round(l_reqRec.qtyPerAssy / NVL(l_reqRec.componentYieldFactor,1),
468: wip_constants.inv_max_precision);
469: else
470: -- otherwise just backflush the qty per assembly, not including the comp yield */
471: l_compQty := round(l_reqRec.qtyPerAssy, wip_constants.inv_max_precision);
472: end if;
467: l_compQty := round(l_reqRec.qtyPerAssy / NVL(l_reqRec.componentYieldFactor,1),
468: wip_constants.inv_max_precision);
469: else
470: -- otherwise just backflush the qty per assembly, not including the comp yield */
471: l_compQty := round(l_reqRec.qtyPerAssy, wip_constants.inv_max_precision);
472: end if;
473:
474: -- backward move
475:
478: --has already been updated to 0 before reaching here. Hence changing the conditions so that it will work for both
479: --online and background cases
480:
481: /*
482: if (p_wipSupplyType = WIP_CONSTANTS.OP_PULL and (l_reqRec.opAblyQtyCompleted + p_assyQty) = 0) or
483: (p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL and
484: (l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty) = 0) then
485: */
486:
479: --online and background cases
480:
481: /*
482: if (p_wipSupplyType = WIP_CONSTANTS.OP_PULL and (l_reqRec.opAblyQtyCompleted + p_assyQty) = 0) or
483: (p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL and
484: (l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty) = 0) then
485: */
486:
487: -- bug 5524972 rewritten the following if clause. made logic for assembly pull components similar to
488: -- that for operation pull
489:
490: /*
491:
492: if (((p_wipSupplyType = WIP_CONSTANTS.OP_PULL)
493: and (LEAST(l_reqRec.opAblyQtyCompleted, ABS(l_reqRec.opAblyQtyCompleted + p_assyQty)) = 0)) or
494: ((p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL)
495: and (LEAST(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped,
496: ABS(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty)) = 0)))
490: /*
491:
492: if (((p_wipSupplyType = WIP_CONSTANTS.OP_PULL)
493: and (LEAST(l_reqRec.opAblyQtyCompleted, ABS(l_reqRec.opAblyQtyCompleted + p_assyQty)) = 0)) or
494: ((p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL)
495: and (LEAST(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped,
496: ABS(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty)) = 0)))
497: */
498:
495: and (LEAST(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped,
496: ABS(l_reqRec.jobAblyQtyCompleted + l_reqRec.jobAblyQtyScrapped + p_assyQty)) = 0)))
497: */
498:
499: if (((p_wipSupplyType = WIP_CONSTANTS.OP_PULL) or (p_wipSupplyType = WIP_CONSTANTS.ASSY_PULL))
500: and (LEAST(l_reqRec.opAblyQtyCompleted, ABS(l_reqRec.opAblyQtyCompleted + p_assyQty)) = 0))
501: then
502:
503: /* Bug 4712535 */
503: /* Bug 4712535 */
504:
505: if (l_include_yield = 1) then
506: l_compQty := - round(l_reqRec.qtyPerAssy / NVL(l_reqRec.componentYieldFactor,1),
507: wip_constants.inv_max_precision);
508: else
509: l_compQty := - round(l_reqRec.qtyPerAssy, wip_constants.inv_max_precision);
510: end if;
511:
505: if (l_include_yield = 1) then
506: l_compQty := - round(l_reqRec.qtyPerAssy / NVL(l_reqRec.componentYieldFactor,1),
507: wip_constants.inv_max_precision);
508: else
509: l_compQty := - round(l_reqRec.qtyPerAssy, wip_constants.inv_max_precision);
510: end if;
511:
512: end if;
513:
524: where organization_id = p_orgID; -- moved above */
525:
526: if (l_include_yield = 1) then
527: l_compQty := round(l_reqRec.qtyPerAssy * p_assyQty/NVL(l_reqRec.componentYieldFactor,1),
528: wip_constants.inv_max_precision);
529: else
530: l_compQty := round(l_reqRec.qtyPerAssy * p_assyQty,
531: wip_constants.inv_max_precision);
532: end if;
527: l_compQty := round(l_reqRec.qtyPerAssy * p_assyQty/NVL(l_reqRec.componentYieldFactor,1),
528: wip_constants.inv_max_precision);
529: else
530: l_compQty := round(l_reqRec.qtyPerAssy * p_assyQty,
531: wip_constants.inv_max_precision);
532: end if;
533:
534: end if;
535:
546: end if;
547:
548:
549: if(l_popRec.itemID = l_reqRec.itemID) then
550: if (l_logLevel <= wip_constants.full_logging) then
551: wip_logger.log('found existing requirement',l_returnStatus);
552: end if;
553: if(fnd_api.to_boolean(p_mergeMode)) then
554: --user wants to merge requirements. Update the txn qty
573: /* Fix for bug# 5160604/5004291:
574: Added eco_status parameter to ensure that the revision being
575: backflushed will be determind by the profile WIP_RELEASED_REVS */
576:
577: if(l_reqRec.revControlCode = wip_constants.revision_controlled) then
578: bom_revisions.get_revision(examine_type => 'ALL',
579: eco_status => l_released_revs_meaning,
580: org_id => p_orgID,
581: item_id => l_reqRec.itemID,
588: /* Bug 4712505 */
589: -- For lot based components we need to check if there are existing records in MTI or MMTT.
590: -- If there are existing records then we don't want to backflush again, since the quantity
591: -- should only be backflushed once.
592: if (l_reqRec.basisType = WIP_CONSTANTS.LOT_BASED_MTL) then
593: open c_checkExistingMTI(l_reqRec.opSeqNum, l_reqRec.itemID, l_txnActionID, l_txnTypeID);
594: fetch c_checkExistingMTI into l_dummy;
595: -- Fixed bug 4755034. This is a regression from 4712505 bug fix.
596: -- Cursor should be close no matter pending record found or not.
613: end if;
614: end if;
615:
616:
617: if (l_logLevel <= wip_constants.full_logging) then
618: wip_logger.log('inserting item:' || l_reqRec.itemID || ' opSeq:' || l_reqRec.opSeqNum || ' qty:' || l_compQty * -1,l_returnStatus);
619: wip_logger.log('txnAction:' || l_txnActionID || ' txnType:' || l_txnTypeID,l_returnStatus);
620: end if;
621: if(x_compTbl is null) then
708: l_revision,
709: l_reqRec.opSeqNum,
710: l_reqRec.deptID,
711: l_reqRec.orgID,
712: wip_constants.mti_inventory,
713: -- 'Y',
714: l_reqRec.supplySub,
715: l_reqRec.supplyLocID,
716: l_acctPeriodID,
717: p_cplTxnID,
718: p_movTxnID,
719: p_repLineID,
720: decode(l_txnActionID,
721: wip_constants.isscomp_action, 1,
722: wip_constants.retcomp_action, 1,
723: wip_constants.issnegc_action, -1,
724: wip_constants.retnegc_action, -1),
725: -- l_reqRec.serialNumControlCode,
718: p_movTxnID,
719: p_repLineID,
720: decode(l_txnActionID,
721: wip_constants.isscomp_action, 1,
722: wip_constants.retcomp_action, 1,
723: wip_constants.issnegc_action, -1,
724: wip_constants.retnegc_action, -1),
725: -- l_reqRec.serialNumControlCode,
726: -- l_reqRec.lotControlCode,
719: p_repLineID,
720: decode(l_txnActionID,
721: wip_constants.isscomp_action, 1,
722: wip_constants.retcomp_action, 1,
723: wip_constants.issnegc_action, -1,
724: wip_constants.retnegc_action, -1),
725: -- l_reqRec.serialNumControlCode,
726: -- l_reqRec.lotControlCode,
727: nvl(p_srcCode, 'WIP Backflush'),
720: decode(l_txnActionID,
721: wip_constants.isscomp_action, 1,
722: wip_constants.retcomp_action, 1,
723: wip_constants.issnegc_action, -1,
724: wip_constants.retnegc_action, -1),
725: -- l_reqRec.serialNumControlCode,
726: -- l_reqRec.lotControlCode,
727: nvl(p_srcCode, 'WIP Backflush'),
728: p_wipEntityID,
742: if(floor(l_extendCount/g_compTblExtendSize) = 1) then
743: x_compTbl.extend(g_compTblExtendSize);
744: l_extendCount := 0;
745: end if;
746: if (l_logLevel <= wip_constants.full_logging) then
747: wip_logger.log('serial control code is ' || l_reqRec.serialNumControlCode,l_returnStatus);
748: wip_logger.log('lot control code is ' || l_reqRec.lotControlCode,l_returnStatus);
749: end if;
750:
814: close c_repReqs;
815: end if;
816:
817: x_returnStatus := fnd_api.g_ret_sts_success;
818: if (l_logLevel <= wip_constants.trace_logging) then
819: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.processRequirements',
820: p_procReturnStatus => x_returnStatus,
821: p_msg => 'procedure succeeded',
822: x_returnStatus => l_returnStatus); --discard logging return status
827: end if;
828: exception
829: when fnd_api.g_exc_unexpected_error then
830: rollback to wipbflpb20;
831: if (l_logLevel <= wip_constants.trace_logging) then
832: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.processRequirements',
833: p_procReturnStatus => x_returnStatus,
834: p_msg => l_errMsg,
835: x_returnStatus => l_returnStatus); --discard logging return status
843: x_returnStatus := fnd_api.g_ret_sts_unexp_error;--unexpec error if exception occurs
844: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_bflProc_priv',
845: p_procedure_name => 'processRequirements',
846: p_error_text => SQLERRM);
847: if (l_logLevel <= wip_constants.trace_logging) then
848: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.processRequirements',
849: p_procReturnStatus => x_returnStatus,
850: p_msg => 'unexpected error: ' || SQLERRM,
851: x_returnStatus => l_returnStatus); --discard logging return status
928: where be.group_id = v_grpID
929: and be.component_sequence_id = bic.component_sequence_id
930: and be.component_item_id = msi.inventory_item_id
931: and be.component_item_id <> p_itemID --exclude assy if it is in the table
932: and msi.bom_item_type not in (wip_constants.model_type,
933: wip_constants.option_class_type) /* Fix for 4575119 */
934: and msi.organization_id = v_orgID
935: order by be.component_code;
936:
929: and be.component_sequence_id = bic.component_sequence_id
930: and be.component_item_id = msi.inventory_item_id
931: and be.component_item_id <> p_itemID --exclude assy if it is in the table
932: and msi.bom_item_type not in (wip_constants.model_type,
933: wip_constants.option_class_type) /* Fix for 4575119 */
934: and msi.organization_id = v_orgID
935: order by be.component_code;
936:
937: cursor c_groupedComponents(v_cmnOrgID NUMBER, v_orgID NUMBER, v_grpID NUMBER) is
971: where be.group_id = v_grpID
972: and be.component_sequence_id = bic.component_sequence_id
973: and be.component_item_id = msi.inventory_item_id
974: and be.component_item_id <> p_itemID --exclude assy if it is in the table
975: and msi.bom_item_type not in (wip_constants.model_type,
976: wip_constants.option_class_type) /* Fix for 4575119 */
977: and msi.organization_id = v_orgID
978: and msi.organization_id = mp.organization_id
979: and bic.supply_locator_id = milk.inventory_location_id(+)
972: and be.component_sequence_id = bic.component_sequence_id
973: and be.component_item_id = msi.inventory_item_id
974: and be.component_item_id <> p_itemID --exclude assy if it is in the table
975: and msi.bom_item_type not in (wip_constants.model_type,
976: wip_constants.option_class_type) /* Fix for 4575119 */
977: and msi.organization_id = v_orgID
978: and msi.organization_id = mp.organization_id
979: and bic.supply_locator_id = milk.inventory_location_id(+)
980: and milk.organization_id(+) = v_orgid /*Bug#14735094:Added the condition for orgid as same locator_id can be present for different orgs */
1017: if(fnd_api.to_boolean(p_initMsgList)) then
1018: fnd_msg_pub.initialize;
1019: end if;
1020:
1021: if (l_logLevel <= wip_constants.trace_logging) then
1022: l_params(1).paramName := 'p_itemID';
1023: l_params(1).paramValue := p_itemID;
1024: l_params(2).paramName := 'p_orgID';
1025: l_params(2).paramValue := p_orgID;
1058: from bom_bill_of_materials b
1059: where b.assembly_item_id = p_itemID
1060: and b.organization_id = p_orgID
1061: and nvl(b.alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@')
1062: and (b.assembly_type = wip_constants.manufacturing_bill or --FP Bug 6502612
1063: to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes )); --FP Bug 6502612
1064:
1065: /******
1066: select a.organization_id, a.bill_sequence_id
1059: where b.assembly_item_id = p_itemID
1060: and b.organization_id = p_orgID
1061: and nvl(b.alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@')
1062: and (b.assembly_type = wip_constants.manufacturing_bill or --FP Bug 6502612
1063: to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes )); --FP Bug 6502612
1064:
1065: /******
1066: select a.organization_id, a.bill_sequence_id
1067: into l_cmnOrgID, l_cmnBillID
1094: when no_data_found then
1095: x_returnStatus := fnd_api.g_ret_sts_success;
1096: x_compTbl := system.wip_component_tbl_t();
1097:
1098: if (l_logLevel <= wip_constants.trace_logging) then
1099: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1100: p_procReturnStatus => x_returnStatus,
1101: p_msg => 'no bom for this item!',
1102: x_returnStatus => l_returnStatus); --discard logging return status
1107: end if;
1108: return;
1109: end;
1110:
1111: if (l_logLevel <= wip_constants.full_logging) then
1112: wip_logger.log('cmn bill id: ' || l_cmnBillID,l_returnStatus);
1113: wip_logger.log('cmn org id: ' || l_cmnOrgID,l_returnStatus);
1114: end if;
1115:
1150: and nvl(alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@'); /* Bug 5139022 Added NVL functions */
1151:
1152: /*Fix for bug 10245572, if WIP: See Engineering Item set to Yes, always set l_bom_or_eng_flag to be 2 so that
1153: bom explosion code can handle the explosion for a mixture of BOM and Engineering Bill Structure.*/
1154: if (to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes) then
1155: l_bom_or_eng_flag := WIP_CONSTANTS.ENGINEERING_BILL;
1156: end if;
1157:
1158: --explode the bom. This API has a few shortcomings. Namely:
1151:
1152: /*Fix for bug 10245572, if WIP: See Engineering Item set to Yes, always set l_bom_or_eng_flag to be 2 so that
1153: bom explosion code can handle the explosion for a mixture of BOM and Engineering Bill Structure.*/
1154: if (to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes) then
1155: l_bom_or_eng_flag := WIP_CONSTANTS.ENGINEERING_BILL;
1156: end if;
1157:
1158: --explode the bom. This API has a few shortcomings. Namely:
1159: -- it will not prune exploded components for subassemblies
1165: /*Bug#14813027- Changed the revdate to NVL(p_bomRevDate,p_txnDate) as bomRevDate can be null, if null is passed,BOM is exploded as on sysdate .
1166: Now, explosion will be done as on bomrevDate or p_txndate */
1167: bompexpl.exploder_userexit(org_id => p_orgID,
1168: grp_id => l_grpID,
1169: rev_date => to_char(NVL(p_bomRevDate,p_txnDate), wip_constants.datetime_fmt),
1170: explode_option => 2,
1171: -- order_by => 2,
1172: levels_to_explode => l_maxBomLevel,
1173: module => 5,
1181: impl_flag => p_implFlag); /* Fix for bug 5383135 */
1182:
1183:
1184:
1185: if (l_logLevel <= wip_constants.full_logging) then
1186: wip_logger.log(l_errCode,l_returnStatus);
1187: end if;
1188: if(l_errCode <> 0) then
1189: fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1204: raise fnd_api.g_exc_unexpected_error;
1205: end if;
1206:
1207: --don't explode, just delete all exploded components
1208: if(l_bomItemType in (wip_constants.option_class_type, wip_constants.model_type)) then
1209: delete bom_explosion_temp
1210: where group_id = l_grpID
1211: and plan_level > 2;
1212: else
1218: if(c_components%NOTFOUND) then
1219: close c_components;
1220: exit;
1221: end if;
1222: if (l_logLevel <= wip_constants.full_logging) then
1223: wip_logger.log('process item ' || l_compRec.itemID || ' w/compCode= ' || l_compRec.compCode,l_returnStatus);
1224: end if;
1225: --The op seq of a phantom component should be the op seq of it's ancestor that's on the assy_item's bom,
1226: --or in other words, a direct child of the assembly. If this check is not done, multiple levels of nesting
1224: end if;
1225: --The op seq of a phantom component should be the op seq of it's ancestor that's on the assy_item's bom,
1226: --or in other words, a direct child of the assembly. If this check is not done, multiple levels of nesting
1227: --will cause problems.
1228: if(l_inheritPhOpSeq = wip_constants.yes) then
1229: if(l_compRec.bomLevel = 2) then
1230: l_opSeqNum := l_compRec.opSeqNum;
1231: if (l_logLevel <= wip_constants.full_logging) then
1232: wip_logger.log('--set all descendant op seqs to' || l_opSeqNum,l_returnStatus);
1227: --will cause problems.
1228: if(l_inheritPhOpSeq = wip_constants.yes) then
1229: if(l_compRec.bomLevel = 2) then
1230: l_opSeqNum := l_compRec.opSeqNum;
1231: if (l_logLevel <= wip_constants.full_logging) then
1232: wip_logger.log('--set all descendant op seqs to' || l_opSeqNum,l_returnStatus);
1233: end if;
1234: end if;
1235: else
1232: wip_logger.log('--set all descendant op seqs to' || l_opSeqNum,l_returnStatus);
1233: end if;
1234: end if;
1235: else
1236: if (l_logLevel <= wip_constants.full_logging) then
1237: wip_logger.log('--retain op seqs',l_returnStatus);
1238: end if;
1239: l_opSeqNum := null;
1240: end if;
1238: end if;
1239: l_opSeqNum := null;
1240: end if;
1241:
1242: if(l_compRec.wipSupplyType = wip_constants.phantom) then --if item is a phantom
1243: if(l_inheritPhOpSeq = wip_constants.no) then
1244: select operation_seq_num
1245: into l_opSeqNum
1246: from bom_explosion_temp
1239: l_opSeqNum := null;
1240: end if;
1241:
1242: if(l_compRec.wipSupplyType = wip_constants.phantom) then --if item is a phantom
1243: if(l_inheritPhOpSeq = wip_constants.no) then
1244: select operation_seq_num
1245: into l_opSeqNum
1246: from bom_explosion_temp
1247: where rowid = l_compRec.beRowID;
1256:
1257: --only needed to use l_opSeqNum for the above update
1258: l_opSeqNum := null;
1259: end if;
1260: if (l_logLevel <= wip_constants.full_logging) then
1261: wip_logger.log('--item is a phantom',l_returnStatus);
1262: end if;
1263: --update all the descendants quantity to reflect the phantom parent
1264: if(l_inheritPhOpSeq = wip_constants.yes and l_compRec.bomLevel = 1) then
1260: if (l_logLevel <= wip_constants.full_logging) then
1261: wip_logger.log('--item is a phantom',l_returnStatus);
1262: end if;
1263: --update all the descendants quantity to reflect the phantom parent
1264: if(l_inheritPhOpSeq = wip_constants.yes and l_compRec.bomLevel = 1) then
1265: l_opSeqNum := l_compRec.opSeqNum;
1266: else
1267: l_opSeqNum := null;
1268: end if;
1265: l_opSeqNum := l_compRec.opSeqNum;
1266: else
1267: l_opSeqNum := null;
1268: end if;
1269: if (l_logLevel <= wip_constants.full_logging) then
1270: wip_logger.log('--updated qty by ' || l_compRec.priQty,l_returnStatus);
1271: wip_logger.log('shrinkage_rate: ' || l_compRec.shrinkageRate,l_returnStatus);
1272: end if;
1273:
1277: /* Fix for bug 5221306 lot basis was taken care for children of phantom
1278: Added decode in basis_type of component
1279: Bug#13455457: Added sort_order condition. '_' has been added to ensure that only the childs are updated*/
1280: update bom_explosion_temp
1281: set component_quantity = (component_quantity * decode (basis_type , wip_constants.lot_based_mtl,1,l_compRec.priQty) ) / (1 - l_compRec.shrinkageRate),
1282: /* For phantoms, recalcuate component_yield_factor by multiplying it with child yield only for components
1283: having basis_type as item. Bug fix 5524603. */
1284: component_yield_factor = decode(basis_type,wip_constants.lot_based_mtl,component_yield_factor,component_yield_factor * l_compRec.compYield),
1285: operation_seq_num = nvl(l_opSeqNum, operation_seq_num)
1280: update bom_explosion_temp
1281: set component_quantity = (component_quantity * decode (basis_type , wip_constants.lot_based_mtl,1,l_compRec.priQty) ) / (1 - l_compRec.shrinkageRate),
1282: /* For phantoms, recalcuate component_yield_factor by multiplying it with child yield only for components
1283: having basis_type as item. Bug fix 5524603. */
1284: component_yield_factor = decode(basis_type,wip_constants.lot_based_mtl,component_yield_factor,component_yield_factor * l_compRec.compYield),
1285: operation_seq_num = nvl(l_opSeqNum, operation_seq_num)
1286: where group_id = l_grpID
1287: and component_code like l_compRec.compCode || '-%'
1288: and sort_order like l_compRec.sort_order||'_%';
1310: --blindly update all descendant's b/c one of the levels may not have an op-seq of one,
1311: --e.g. Assy -> Ph(20) -> Ph(1) -> Ph(10) -> Comp(1). In this case, we would want to set the comp's op seq to 10, not 20.
1312: goto start_loop_processing; --outer loop will check notfound, but will continue processing since the attribute will be false
1313: end if;
1314: if (l_logLevel <= wip_constants.full_logging) then
1315: wip_logger.log('--skipping item' || l_compRec.compCode,l_returnStatus);
1316: end if;
1317: end loop;
1318: end if; ----if item is a phantom
1325: --second pass: select all the remaining records into a pl/sql table
1326: for l_compRec in c_groupedComponents(v_cmnOrgID => l_cmnOrgID, v_orgID => p_orgID, v_grpID => l_grpID) loop
1327: if(p_qty > 0) then
1328: if(l_compRec.priQty > 0) then
1329: l_txnActionID := wip_constants.isscomp_action;
1330: else
1331: l_txnActionID := wip_constants.issnegc_action;
1332: end if;
1333: else
1327: if(p_qty > 0) then
1328: if(l_compRec.priQty > 0) then
1329: l_txnActionID := wip_constants.isscomp_action;
1330: else
1331: l_txnActionID := wip_constants.issnegc_action;
1332: end if;
1333: else
1334: if(l_compRec.priQty > 0) then
1335: l_txnActionID := wip_constants.retcomp_action;
1331: l_txnActionID := wip_constants.issnegc_action;
1332: end if;
1333: else
1334: if(l_compRec.priQty > 0) then
1335: l_txnActionID := wip_constants.retcomp_action;
1336: else
1337: l_txnActionID := wip_constants.retnegc_action;
1338: end if;
1339: end if;
1333: else
1334: if(l_compRec.priQty > 0) then
1335: l_txnActionID := wip_constants.retcomp_action;
1336: else
1337: l_txnActionID := wip_constants.retnegc_action;
1338: end if;
1339: end if;
1340: /*Fix for bug#14536153: To pass the component_seqid so that DFFs are copied correctly to WRO
1341: Added rownum=1 because a combination of operation_seq_num and item_id can appear twice because of phantom items. In that case, either of the component_seq_id is picked */
1348: if(floor(l_extendCount/g_compTblExtendSize) = 1) then
1349: x_compTbl.extend(g_compTblExtendSize);
1350: l_extendCount := 0;
1351: end if;
1352: if(l_compRec.wipSupplyType = wip_constants.phantom) then
1353: --phantom is supposedly an implicit conversion from a supply to a demand. Thus we need to factor by the
1354: --'shrinkage rate' item attribute.
1355: l_compRec.priQty := l_compRec.priQty / (1 - l_compRec.shrinkageRate);
1356: end if;
1354: --'shrinkage rate' item attribute.
1355: l_compRec.priQty := l_compRec.priQty / (1 - l_compRec.shrinkageRate);
1356: end if;
1357:
1358: if(l_compRec.revControlCode = wip_constants.revision_controlled) then --Bug#14629377:Passing TxnDate when BomRevDate is null,bomrevdate is null when wip_entity_id is null for WOLC
1359: bom_revisions.get_revision(examine_type => 'ALL',
1360: eco_status => l_released_revs_meaning,/*Added for bug fix: 7721526 (FP of bug 7553760)*/
1361: org_id => p_orgID,
1362: item_id => l_compRec.itemID,
1387: end if;
1388:
1389: --if we are using a project/task locator, then set the project/task IDs
1390: if(p_projectID is not null and
1391: l_compRec.pegFlag in (wip_constants.peg_hard, wip_constants.peg_end_assm_hard)) then
1392: l_projectID := p_projectID;
1393: l_taskID := p_taskID;
1394: end if;
1395:
1415:
1416: /* LBM Project */
1417:
1418: -- set multiplication factor for lot based component
1419: if( l_compRec.basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
1420: if ((l_txnActionID = wip_constants.retcomp_action) or
1421: (l_txnActionID = wip_constants.retnegc_action)) then
1422: multiple_factor := -1;
1423: else
1416: /* LBM Project */
1417:
1418: -- set multiplication factor for lot based component
1419: if( l_compRec.basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
1420: if ((l_txnActionID = wip_constants.retcomp_action) or
1421: (l_txnActionID = wip_constants.retnegc_action)) then
1422: multiple_factor := -1;
1423: else
1424: multiple_factor := 1;
1417:
1418: -- set multiplication factor for lot based component
1419: if( l_compRec.basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
1420: if ((l_txnActionID = wip_constants.retcomp_action) or
1421: (l_txnActionID = wip_constants.retnegc_action)) then
1422: multiple_factor := -1;
1423: else
1424: multiple_factor := 1;
1425: end if;
1481: delete bom_explosion_temp
1482: where group_id = l_grpID;
1483:
1484: x_returnStatus := fnd_api.g_ret_sts_success;
1485: if (l_logLevel <= wip_constants.trace_logging) then
1486: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1487: p_procReturnStatus => x_returnStatus,
1488: p_msg => 'procedure success',
1489: x_returnStatus => l_returnStatus); --discard logging return status
1496: exception
1497: when fnd_api.g_exc_unexpected_error then
1498: rollback to wipbflpb30;
1499: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1500: if (l_logLevel <= wip_constants.trace_logging) then
1501: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1502: p_procReturnStatus => x_returnStatus,
1503: p_msg => l_errMsg,
1504: x_returnStatus => l_returnStatus); --discard logging return status
1513: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1514: fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
1515: fnd_message.set_token('ERROR_TEXT', SQLERRM);
1516: fnd_msg_pub.add;
1517: if (l_logLevel <= wip_constants.trace_logging) then
1518: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.explodeRequirements',
1519: p_procReturnStatus => x_returnStatus,
1520: p_msg => 'unexpected error: ' || SQLERRM,
1521: x_returnStatus => l_returnStatus); --discard logging return status
1527: end explodeRequirements;
1528:
1529:
1530: function findTxnTypeID(p_txnActionID IN NUMBER) return NUMBER is begin
1531: if(p_txnActionID = wip_constants.isscomp_action) then
1532: return wip_constants.isscomp_type;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1528:
1529:
1530: function findTxnTypeID(p_txnActionID IN NUMBER) return NUMBER is begin
1531: if(p_txnActionID = wip_constants.isscomp_action) then
1532: return wip_constants.isscomp_type;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1529:
1530: function findTxnTypeID(p_txnActionID IN NUMBER) return NUMBER is begin
1531: if(p_txnActionID = wip_constants.isscomp_action) then
1532: return wip_constants.isscomp_type;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1537: elsif(p_txnActionID = wip_constants.retnegc_action) then
1530: function findTxnTypeID(p_txnActionID IN NUMBER) return NUMBER is begin
1531: if(p_txnActionID = wip_constants.isscomp_action) then
1532: return wip_constants.isscomp_type;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1537: elsif(p_txnActionID = wip_constants.retnegc_action) then
1538: return wip_constants.retnegc_type;
1531: if(p_txnActionID = wip_constants.isscomp_action) then
1532: return wip_constants.isscomp_type;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1537: elsif(p_txnActionID = wip_constants.retnegc_action) then
1538: return wip_constants.retnegc_type;
1539: end if;
1532: return wip_constants.isscomp_type;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1537: elsif(p_txnActionID = wip_constants.retnegc_action) then
1538: return wip_constants.retnegc_type;
1539: end if;
1540: end findTxnTypeID;
1533: elsif(p_txnActionID = wip_constants.issnegc_action) then
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1537: elsif(p_txnActionID = wip_constants.retnegc_action) then
1538: return wip_constants.retnegc_type;
1539: end if;
1540: end findTxnTypeID;
1541:
1534: return wip_constants.issnegc_type;
1535: elsif(p_txnActionID = wip_constants.retcomp_action) then
1536: return wip_constants.retcomp_type;
1537: elsif(p_txnActionID = wip_constants.retnegc_action) then
1538: return wip_constants.retnegc_type;
1539: end if;
1540: end findTxnTypeID;
1541:
1542: PROCEDURE backflush(p_wipEntityID IN NUMBER,
1612:
1613: BEGIN
1614: SAVEPOINT s_backflush;
1615: -- write parameter value to log file
1616: IF (l_logLevel <= wip_constants.trace_logging) THEN
1617: l_params(1).paramName := 'p_wipEntityID';
1618: l_params(1).paramValue := p_wipEntityID;
1619: l_params(2).paramName := 'p_orgID';
1620: l_params(2).paramValue := p_orgID;
1669: p_params => l_params,
1670: x_returnStatus => l_returnStatus);
1671: END IF;
1672: IF(p_batchSeq IS NULL) THEN
1673: l_batch_seq := WIP_CONSTANTS.COMPONENT_BATCH_SEQ;
1674: ELSE
1675: l_batch_seq := p_batchSeq;
1676: END IF;
1677:
1674: ELSE
1675: l_batch_seq := p_batchSeq;
1676: END IF;
1677:
1678: IF(p_entityType = WIP_CONSTANTS.REPETITIVE) THEN
1679: SELECT MIN(wo.operation_seq_num),
1680: MAX(wo.operation_seq_num)
1681: INTO l_first_op,
1682: l_last_op
1684: wip_repetitive_schedules wrs
1685: WHERE wrs.organization_id = wo.organization_id
1686: AND wrs.wip_entity_id = wo.wip_entity_id
1687: AND wrs.repetitive_schedule_id = wo.repetitive_schedule_id
1688: AND wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
1689: AND wrs.wip_entity_id = p_wipEntityID
1690: AND wrs.organization_id = p_orgID;
1691: ELSE -- Discrete and Lotbased jobs
1692: SELECT MIN(wo.operation_seq_num),
1696: FROM wip_operations wo,
1697: wip_discrete_jobs wdj
1698: WHERE wdj.organization_id = wo.organization_id
1699: AND wdj.wip_entity_id = wo.wip_entity_id
1700: AND wdj.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
1701: AND wdj.wip_entity_id = p_wipEntityID
1702: AND wdj.organization_id = p_orgID;
1703: END IF;
1704:
1713:
1714: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
1715: p_toOp IS NULL OR p_toStep IS NULL) THEN
1716: -- Call from WIP Completion form
1717: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
1718: -- Ccmpletion transaction
1719: l_fm_op := l_last_op;
1720: l_fm_step := WIP_CONSTANTS.TOMOVE;
1721: l_to_op := NULL;
1716: -- Call from WIP Completion form
1717: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
1718: -- Ccmpletion transaction
1719: l_fm_op := l_last_op;
1720: l_fm_step := WIP_CONSTANTS.TOMOVE;
1721: l_to_op := NULL;
1722: l_to_step := NULL;
1723: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
1724: -- Return transaction
1719: l_fm_op := l_last_op;
1720: l_fm_step := WIP_CONSTANTS.TOMOVE;
1721: l_to_op := NULL;
1722: l_to_step := NULL;
1723: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
1724: -- Return transaction
1725: l_fm_op := NULL;
1726: l_fm_step := NULL;
1727: l_to_op := l_last_op;
1724: -- Return transaction
1725: l_fm_op := NULL;
1726: l_fm_step := NULL;
1727: l_to_op := l_last_op;
1728: l_to_step := WIP_CONSTANTS.TOMOVE;
1729: END IF;
1730: ELSE -- call from WIP Move form
1731: l_fm_op := p_fmOp;
1732: l_fm_step := p_fmStep;
1734: l_to_step := p_toStep;
1735: END IF; -- Call from WIP Completion form
1736:
1737: -- Check if repetitive schedule
1738: IF(p_entityType = WIP_CONSTANTS.REPETITIVE) THEN -- Repetitive schedule
1739: -- Fixed bug 5056289.Basically, we should not rely on the fact that p_fmOp
1740: -- will be null if call from Completion form. We should also check
1741: -- p_fmMoveProcessor. Since move processor will also pass null for p_fmOp
1742: -- for assembly pull item.
1742: -- for assembly pull item.
1743: IF((p_fmOp IS NULL OR p_fmStep IS NULL OR
1744: p_toOp IS NULL OR p_toStep IS NULL) AND
1745: (p_fmMoveProcessor IS NULL OR
1746: p_fmMoveProcessor = WIP_CONSTANTS.NO)) THEN
1747: -- Call from WIP Completion form, so use the allocation information in
1748: -- wip_mtl_allocations_temp. There is no need to call schedule_alloc
1749: -- again.
1750:
1763: p_batchID => p_batchID,
1764: p_orgID => p_orgID,
1765: p_assyQty => l_repAssyPull.primaryQty,
1766: p_txnDate => p_txnDate,
1767: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
1768: p_txnHdrID => p_txnHdrID,
1769: p_firstOp => -1,
1770: p_lastOP => l_last_op,
1771: p_firstMoveOp => null,
1787: END IF;
1788: END LOOP;
1789:
1790: ELSE
1791: IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
1792: -- If call from move processor, no need to do schedule allocation again
1793: -- Instead, we should use the value in WMTA table.
1794: l_sche_count := 0;
1795: FOR l_wmta IN c_wmta (p_txn_id => p_movTxnID) LOOP
1797: l_rsa(l_sche_count).scheID := l_wmta.rep_id;
1798: l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
1799: END LOOP;
1800: ELSIF(p_fmMoveProcessor IS NULL OR
1801: p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
1802: -- Check whether overcompletion transaction
1803: IF(p_ocQty IS NOT NULL) THEN
1804: l_oc_txn_type := WIP_CONSTANTS.PARENT_TXN;
1805: ELSE
1800: ELSIF(p_fmMoveProcessor IS NULL OR
1801: p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
1802: -- Check whether overcompletion transaction
1803: IF(p_ocQty IS NOT NULL) THEN
1804: l_oc_txn_type := WIP_CONSTANTS.PARENT_TXN;
1805: ELSE
1806: l_oc_txn_type := WIP_CONSTANTS.NORMAL_TXN;
1807: END IF;
1808: wip_movProc_priv.schedule_alloc(p_org_id => p_orgID,
1802: -- Check whether overcompletion transaction
1803: IF(p_ocQty IS NOT NULL) THEN
1804: l_oc_txn_type := WIP_CONSTANTS.PARENT_TXN;
1805: ELSE
1806: l_oc_txn_type := WIP_CONSTANTS.NORMAL_TXN;
1807: END IF;
1808: wip_movProc_priv.schedule_alloc(p_org_id => p_orgID,
1809: p_wip_id => p_wipEntityID,
1810: p_line_id => p_lineID,
1814: p_to_op => l_to_op,
1815: p_to_step => l_to_step,
1816: p_oc_txn_type => l_oc_txn_type,
1817: p_txnType => p_txnType,
1818: p_fm_form => WIP_CONSTANTS.YES,
1819: p_comp_alloc => WIP_CONSTANTS.NO,
1820: p_txn_date => p_txndate, /* bug 5373061 */
1821: x_proc_status => l_proc_status,
1822: x_sche_count => l_sche_count,
1815: p_to_step => l_to_step,
1816: p_oc_txn_type => l_oc_txn_type,
1817: p_txnType => p_txnType,
1818: p_fm_form => WIP_CONSTANTS.YES,
1819: p_comp_alloc => WIP_CONSTANTS.NO,
1820: p_txn_date => p_txndate, /* bug 5373061 */
1821: x_proc_status => l_proc_status,
1822: x_sche_count => l_sche_count,
1823: x_rsa => l_rsa,
1822: x_sche_count => l_sche_count,
1823: x_rsa => l_rsa,
1824: x_returnStatus => l_returnStatus);
1825:
1826: IF (l_logLevel <= wip_constants.full_logging) THEN
1827: wip_logger.log(p_msg => 'l_proc_status = ' || l_proc_status,
1828: x_returnStatus => l_returnStatus);
1829: wip_logger.log(p_msg => 'l_sche_count = ' || l_sche_count,
1830: x_returnStatus => l_returnStatus);
1841: fnd_message.set_token('ENTITY2', 'quantity available to move');
1842: fnd_msg_pub.add;
1843: l_errMsg := 'available qty is not enough to fullfill move txn';
1844: raise fnd_api.g_exc_unexpected_error;
1845: ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
1846: l_errMsg := 'wip_movProc_priv.schedule_alloc failed';
1847: raise fnd_api.g_exc_unexpected_error;
1848: END IF; -- check l_proc_status
1849: END IF; -- check p_fmMoveProcessor
1847: raise fnd_api.g_exc_unexpected_error;
1848: END IF; -- check l_proc_status
1849: END IF; -- check p_fmMoveProcessor
1850:
1851: IF (l_logLevel <= wip_constants.full_logging) THEN
1852: FOR i IN 1..l_sche_count LOOP
1853: wip_logger.log(p_msg => 'sche_id = ' || l_rsa(i).scheID,
1854: x_returnStatus => l_returnStatus);
1855: wip_logger.log(p_msg => 'txn_qty = ' || l_rsa(i).scheQty,
1908: p_batchID => p_batchID,
1909: p_orgID => p_orgID,
1910: p_assyQty => l_rsa(i).scheQty * l_forward,
1911: p_txnDate => p_txnDate,
1912: p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
1913: p_txnHdrID => p_txnHdrID,
1914: p_firstOp => l_first_bf_op,
1915: p_lastOP => l_last_bf_op,
1916: p_firstMoveOp => l_fm_op,
1983: p_batchID => p_batchID,
1984: p_orgID => p_orgID,
1985: p_assyQty => l_rsa(i).scheQty * l_forward,
1986: p_txnDate => p_txnDate,
1987: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
1988: p_txnHdrID => p_txnHdrID,
1989: p_firstOp => l_first_bf_op,
1990: p_lastOP => l_last_bf_op,
1991: p_firstMoveOp => l_fm_op,
2011:
2012: -- only do schedule allocation for completion if not call from move
2013: -- processor
2014: IF(p_cplTxnID IS NOT NULL AND
2015: (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2016: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2017: IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
2018: -- If call from move processor, no need to do schedule allocation
2019: -- again Instead, we should use the value in WMTA table.
2012: -- only do schedule allocation for completion if not call from move
2013: -- processor
2014: IF(p_cplTxnID IS NOT NULL AND
2015: (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2016: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2017: IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
2018: -- If call from move processor, no need to do schedule allocation
2019: -- again Instead, we should use the value in WMTA table.
2020: l_sche_count := 0;
2013: -- processor
2014: IF(p_cplTxnID IS NOT NULL AND
2015: (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2016: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2017: IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
2018: -- If call from move processor, no need to do schedule allocation
2019: -- again Instead, we should use the value in WMTA table.
2020: l_sche_count := 0;
2021: FOR l_wmta IN c_wmta (p_txn_id => p_movTxnID) LOOP
2023: l_rsa(l_sche_count).scheID := l_wmta.rep_id;
2024: l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
2025: END LOOP;
2026: ELSIF(p_fmMoveProcessor IS NULL OR
2027: p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
2028: wip_movProc_priv.schedule_alloc(
2029: p_org_id => p_orgID,
2030: p_wip_id => p_wipEntityID,
2031: p_line_id => p_lineID,
2035: p_to_op => l_to_op,
2036: p_to_step => l_to_step,
2037: p_oc_txn_type => l_oc_txn_type,
2038: p_txnType => p_txnType,
2039: p_fm_form => WIP_CONSTANTS.YES,
2040: p_comp_alloc => WIP_CONSTANTS.YES,
2041: p_txn_date => p_txndate, /* bug 5373061 */
2042: x_proc_status => l_proc_status,
2043: x_sche_count => l_sche_count,
2036: p_to_step => l_to_step,
2037: p_oc_txn_type => l_oc_txn_type,
2038: p_txnType => p_txnType,
2039: p_fm_form => WIP_CONSTANTS.YES,
2040: p_comp_alloc => WIP_CONSTANTS.YES,
2041: p_txn_date => p_txndate, /* bug 5373061 */
2042: x_proc_status => l_proc_status,
2043: x_sche_count => l_sche_count,
2044: x_rsa => l_rsa,
2043: x_sche_count => l_sche_count,
2044: x_rsa => l_rsa,
2045: x_returnStatus => l_returnStatus);
2046:
2047: IF (l_logLevel <= wip_constants.full_logging) THEN
2048: wip_logger.log(p_msg => 'l_proc_status = ' ||
2049: l_proc_status,
2050: x_returnStatus => l_returnStatus);
2051: wip_logger.log(p_msg => 'l_sche_count = ' ||
2064: fnd_message.set_token('ENTITY2', 'quantity available to move');
2065: fnd_msg_pub.add;
2066: l_errMsg := 'available qty is not enough to fullfill move txn';
2067: raise fnd_api.g_exc_unexpected_error;
2068: ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
2069: l_errMsg := 'wip_movProc_priv.schedule_alloc failed';
2070: raise fnd_api.g_exc_unexpected_error;
2071: END IF; -- check l_proc_status
2072: END IF; -- check p_fmMoveProcessor
2070: raise fnd_api.g_exc_unexpected_error;
2071: END IF; -- check l_proc_status
2072: END IF; -- check p_fmMoveProcessor
2073:
2074: IF (l_logLevel <= wip_constants.full_logging) THEN
2075: FOR i IN 1..l_sche_count LOOP
2076: wip_logger.log(p_msg => 'sche_id = ' || l_rsa(i).scheID,
2077: x_returnStatus => l_returnStatus);
2078: wip_logger.log(p_msg => 'txn_qty = ' || l_rsa(i).scheQty,
2081: END IF;
2082:
2083: -- If Completion or Return txns, we have to backflush Assembly
2084: -- pull components too
2085: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2086: l_forward := 1;
2087: ELSE
2088: l_forward := -1;
2089: END IF;
2107: p_batchID => p_batchID,
2108: p_orgID => p_orgID,
2109: p_assyQty => l_rsa(i).scheQty * l_forward,
2110: p_txnDate => p_txnDate,
2111: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2112: p_txnHdrID => p_txnHdrID,
2113: p_firstOp => -1,
2114: p_lastOP => l_last_op,
2115: p_firstMoveOp => null,
2134: END IF; -- call from completion form
2135: -- Check whether overcompletion
2136: IF(p_childMovTxnID IS NOT NULL AND p_ocQty IS NOT NULL) THEN
2137: -- overmove/overcomplete
2138: IF(p_fmMoveProcessor = WIP_CONSTANTS.YES) THEN
2139: -- If call from move processor, no need to do schedule allocation
2140: -- again. Instead, we should use the value in WMTA table.
2141: l_sche_count := 0;
2142: FOR l_wmta IN c_wmta (p_txn_id => p_childMovTxnID) LOOP
2144: l_rsa(l_sche_count).scheID := l_wmta.rep_id;
2145: l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
2146: END LOOP;
2147: ELSIF(p_fmMoveProcessor IS NULL OR
2148: p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
2149: l_oc_txn_type := WIP_CONSTANTS.CHILD_TXN;
2150: l_fm_op := l_first_op;
2151: l_fm_step := WIP_CONSTANTS.QUEUE;
2152: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2145: l_rsa(l_sche_count).scheQty := l_wmta.txn_qty;
2146: END LOOP;
2147: ELSIF(p_fmMoveProcessor IS NULL OR
2148: p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
2149: l_oc_txn_type := WIP_CONSTANTS.CHILD_TXN;
2150: l_fm_op := l_first_op;
2151: l_fm_step := WIP_CONSTANTS.QUEUE;
2152: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2153: p_toOp IS NULL OR p_toStep IS NULL) THEN
2147: ELSIF(p_fmMoveProcessor IS NULL OR
2148: p_fmMoveProcessor = WIP_CONSTANTS.NO) THEN
2149: l_oc_txn_type := WIP_CONSTANTS.CHILD_TXN;
2150: l_fm_op := l_first_op;
2151: l_fm_step := WIP_CONSTANTS.QUEUE;
2152: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2153: p_toOp IS NULL OR p_toStep IS NULL) THEN
2154: -- Call from Completion form
2155: l_to_op := l_last_op;
2152: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2153: p_toOp IS NULL OR p_toStep IS NULL) THEN
2154: -- Call from Completion form
2155: l_to_op := l_last_op;
2156: l_to_step := WIP_CONSTANTS.TOMOVE;
2157: ELSE -- Call from WIP Move or OSFM Move forms
2158: l_to_op := p_fmOp;
2159: l_to_step := p_fmStep;
2160: END IF;
2168: p_to_op => l_to_op,
2169: p_to_step => l_to_step,
2170: p_oc_txn_type => l_oc_txn_type,
2171: p_txnType => p_txnType,
2172: p_fm_form => WIP_CONSTANTS.YES,
2173: p_comp_alloc => WIP_CONSTANTS.NO,
2174: p_txn_date => p_txndate, /* bug 5373061 */
2175: x_proc_status => l_proc_status,
2176: x_sche_count => l_sche_count,
2169: p_to_step => l_to_step,
2170: p_oc_txn_type => l_oc_txn_type,
2171: p_txnType => p_txnType,
2172: p_fm_form => WIP_CONSTANTS.YES,
2173: p_comp_alloc => WIP_CONSTANTS.NO,
2174: p_txn_date => p_txndate, /* bug 5373061 */
2175: x_proc_status => l_proc_status,
2176: x_sche_count => l_sche_count,
2177: x_rsa => l_rsa,
2176: x_sche_count => l_sche_count,
2177: x_rsa => l_rsa,
2178: x_returnStatus => l_returnStatus);
2179:
2180: IF (l_logLevel <= wip_constants.full_logging) THEN
2181: wip_logger.log(p_msg => 'l_proc_status = ' ||
2182: l_proc_status,
2183: x_returnStatus => l_returnStatus);
2184: wip_logger.log(p_msg => 'l_sche_count = ' ||
2197: fnd_message.set_token('ENTITY2', 'quantity available to move');
2198: fnd_msg_pub.add;
2199: l_errMsg := 'available qty is not enough to fullfill move txn';
2200: raise fnd_api.g_exc_unexpected_error;
2201: ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
2202: l_errMsg := 'wip_movProc_priv.schedule_alloc failed';
2203: raise fnd_api.g_exc_unexpected_error;
2204: END IF; -- check l_proc_status
2205: END IF; -- check p_fmMoveProcessor
2203: raise fnd_api.g_exc_unexpected_error;
2204: END IF; -- check l_proc_status
2205: END IF; -- check p_fmMoveProcessor
2206:
2207: IF (l_logLevel <= wip_constants.full_logging) THEN
2208: FOR i IN 1..l_sche_count LOOP
2209: wip_logger.log(p_msg => 'sche_id = ' || l_rsa(i).scheID,
2210: x_returnStatus => l_returnStatus);
2211: wip_logger.log(p_msg => 'txn_qty = ' || l_rsa(i).scheQty,
2259: p_batchID => p_batchID,
2260: p_orgID => p_orgID,
2261: p_assyQty => l_rsa(i).scheQty * l_forward,
2262: p_txnDate => p_txnDate,
2263: p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
2264: p_txnHdrID => p_txnHdrID,
2265: p_firstOp => l_first_bf_op,
2266: p_lastOP => l_last_bf_op,
2267: p_firstMoveOp => l_fm_op,
2333: p_batchID => p_batchID,
2334: p_orgID => p_orgID,
2335: p_assyQty => l_rsa(i).scheQty * l_forward,
2336: p_txnDate => p_txnDate,
2337: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2338: p_txnHdrID => p_txnHdrID,
2339: p_firstOp => l_first_bf_op,
2340: p_lastOP => l_last_bf_op,
2341: p_firstMoveOp => l_fm_op,
2396: p_batchID => p_batchID,
2397: p_orgID => p_orgID,
2398: p_assyQty => l_bf_qty,
2399: p_txnDate => p_txnDate,
2400: p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
2401: p_txnHdrID => p_txnHdrID,
2402: p_firstOp => l_first_bf_op,
2403: p_lastOP => l_last_bf_op,
2404: p_firstMoveOp => l_fm_op,
2463: p_batchID => p_batchID,
2464: p_orgID => p_orgID,
2465: p_assyQty => l_bf_qty,
2466: p_txnDate => p_txnDate,
2467: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2468: p_txnHdrID => p_txnHdrID,
2469: p_firstOp => l_first_bf_op,
2470: p_lastOP => l_last_bf_op,
2471: p_firstMoveOp => l_fm_op,
2488: END IF; -- l_first_bf_op <> -1
2489: END IF; -- call from Move form
2490:
2491: IF(p_cplTxnID IS NOT NULL AND
2492: (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2493: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2494:
2495: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2496: l_bf_qty := p_primaryQty;
2489: END IF; -- call from Move form
2490:
2491: IF(p_cplTxnID IS NOT NULL AND
2492: (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2493: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2494:
2495: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2496: l_bf_qty := p_primaryQty;
2497: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
2491: IF(p_cplTxnID IS NOT NULL AND
2492: (p_txnType = WIP_CONSTANTS.COMP_TXN OR
2493: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2494:
2495: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2496: l_bf_qty := p_primaryQty;
2497: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
2498: l_bf_qty := -1 * p_primaryQty;
2499: END IF;
2493: p_txnType = WIP_CONSTANTS.RET_TXN)) THEN
2494:
2495: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
2496: l_bf_qty := p_primaryQty;
2497: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
2498: l_bf_qty := -1 * p_primaryQty;
2499: END IF;
2500: wip_bflProc_priv.processRequirements
2501: (p_wipEntityID => p_wipEntityID,
2510: p_batchID => p_batchID,
2511: p_orgID => p_orgID,
2512: p_assyQty => l_bf_qty,
2513: p_txnDate => p_txnDate,
2514: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2515: p_txnHdrID => p_txnHdrID,
2516: p_firstOp => -1,
2517: p_lastOP => l_last_op,
2518: p_firstMoveOp => null,
2537: -- Check whether overcompletion
2538: IF(p_childMovTxnID IS NOT NULL AND p_ocQty IS NOT NULL) THEN
2539: -- overmove/overcomplete
2540: l_fm_op := l_first_op;
2541: l_fm_step := WIP_CONSTANTS.QUEUE;
2542: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2543: p_toOp IS NULL OR p_toStep IS NULL) THEN
2544: -- Call from Completion form
2545: l_to_op := l_last_op;
2542: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
2543: p_toOp IS NULL OR p_toStep IS NULL) THEN
2544: -- Call from Completion form
2545: l_to_op := l_last_op;
2546: l_to_step := WIP_CONSTANTS.TOMOVE;
2547: ELSE -- Call from WIP Move or OSFM Move forms
2548: l_to_op := p_fmOp;
2549: l_to_step := p_fmStep;
2550: END IF;
2588: p_batchID => p_batchID,
2589: p_orgID => p_orgID,
2590: p_assyQty => l_bf_qty,
2591: p_txnDate => p_txnDate,
2592: p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
2593: p_txnHdrID => p_txnHdrID,
2594: p_firstOp => l_first_bf_op,
2595: p_lastOP => l_last_bf_op,
2596: p_firstMoveOp => l_fm_op,
2609: -- This will ensure that components are not transacted if the assembly transaction fails
2610: UPDATE MTL_TRANSACTIONS_INTERFACE
2611: SET TRANSACTION_BATCH_ID=p_batchID,transaction_Batch_seq=l_batch_seq
2612: WHERE TRANSACTION_HEADER_ID=p_txnHdrID and transaction_source_type_id=5 and transaction_source_id=p_wipEntityID
2613: AND transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
2614: wip_constants.issnegc_action, wip_constants.retnegc_action);
2615:
2616: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2617: l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2610: UPDATE MTL_TRANSACTIONS_INTERFACE
2611: SET TRANSACTION_BATCH_ID=p_batchID,transaction_Batch_seq=l_batch_seq
2612: WHERE TRANSACTION_HEADER_ID=p_txnHdrID and transaction_source_type_id=5 and transaction_source_id=p_wipEntityID
2613: AND transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
2614: wip_constants.issnegc_action, wip_constants.retnegc_action);
2615:
2616: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2617: l_errMsg := 'wip_bflProc_priv.procesRequirements failed' ;
2618: raise fnd_api.g_exc_unexpected_error;
2661: p_batchID => p_batchID,
2662: p_orgID => p_orgID,
2663: p_assyQty => l_bf_qty,
2664: p_txnDate => p_txnDate,
2665: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
2666: p_txnHdrID => p_txnHdrID,
2667: p_firstOp => l_first_bf_op,
2668: p_lastOP => l_last_bf_op,
2669: p_firstMoveOp => l_fm_op,
2692: SELECT COUNT(*)
2693: INTO l_bf_count
2694: FROM mtl_transactions_interface mti
2695: WHERE mti.transaction_header_id = p_txnHdrID
2696: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2697: WIP_CONSTANTS.RETCOMP_ACTION,
2698: WIP_CONSTANTS.ISSNEGC_ACTION,
2699: WIP_CONSTANTS.RETNEGC_ACTION)
2700: AND (mti.MOVE_TRANSACTION_ID = p_movTxnID OR mti.COMPLETION_TRANSACTION_ID = p_cplTxnID)
2693: INTO l_bf_count
2694: FROM mtl_transactions_interface mti
2695: WHERE mti.transaction_header_id = p_txnHdrID
2696: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2697: WIP_CONSTANTS.RETCOMP_ACTION,
2698: WIP_CONSTANTS.ISSNEGC_ACTION,
2699: WIP_CONSTANTS.RETNEGC_ACTION)
2700: AND (mti.MOVE_TRANSACTION_ID = p_movTxnID OR mti.COMPLETION_TRANSACTION_ID = p_cplTxnID)
2701: AND EXISTS (SELECT 1
2694: FROM mtl_transactions_interface mti
2695: WHERE mti.transaction_header_id = p_txnHdrID
2696: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2697: WIP_CONSTANTS.RETCOMP_ACTION,
2698: WIP_CONSTANTS.ISSNEGC_ACTION,
2699: WIP_CONSTANTS.RETNEGC_ACTION)
2700: AND (mti.MOVE_TRANSACTION_ID = p_movTxnID OR mti.COMPLETION_TRANSACTION_ID = p_cplTxnID)
2701: AND EXISTS (SELECT 1
2702: FROM wip_requirement_operations wro
2695: WHERE mti.transaction_header_id = p_txnHdrID
2696: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2697: WIP_CONSTANTS.RETCOMP_ACTION,
2698: WIP_CONSTANTS.ISSNEGC_ACTION,
2699: WIP_CONSTANTS.RETNEGC_ACTION)
2700: AND (mti.MOVE_TRANSACTION_ID = p_movTxnID OR mti.COMPLETION_TRANSACTION_ID = p_cplTxnID)
2701: AND EXISTS (SELECT 1
2702: FROM wip_requirement_operations wro
2703: ,wip_entities we
2706: AND wro.wip_entity_id = we.wip_entity_id
2707: AND wro.inventory_item_id = mti.inventory_item_id
2708: AND wro.operation_seq_num = mti.operation_seq_num
2709: AND wro.organization_id = we.organization_id
2710: AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2711: we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2712: (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2713: );
2714:
2707: AND wro.inventory_item_id = mti.inventory_item_id
2708: AND wro.operation_seq_num = mti.operation_seq_num
2709: AND wro.organization_id = we.organization_id
2710: AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2711: we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2712: (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2713: );
2714:
2715: -- Bug 9907143: updated for serial tagging enhancement for SUN
2708: AND wro.operation_seq_num = mti.operation_seq_num
2709: AND wro.organization_id = we.organization_id
2710: AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2711: we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2712: (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2713: );
2714:
2715: -- Bug 9907143: updated for serial tagging enhancement for SUN
2716: /*
2719: FROM mtl_transactions_interface mti,
2720: mtl_system_items msi
2721: WHERE mti.organization_id = msi.organization_id
2722: AND mti.inventory_item_id = msi.inventory_item_id
2723: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2724: OR
2725: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2726: WIP_CONSTANTS.DYN_RCV_SN))
2727: AND transaction_header_id = p_txnHdrID
2721: WHERE mti.organization_id = msi.organization_id
2722: AND mti.inventory_item_id = msi.inventory_item_id
2723: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2724: OR
2725: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2726: WIP_CONSTANTS.DYN_RCV_SN))
2727: AND transaction_header_id = p_txnHdrID
2728: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729: WIP_CONSTANTS.RETCOMP_ACTION,
2722: AND mti.inventory_item_id = msi.inventory_item_id
2723: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2724: OR
2725: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2726: WIP_CONSTANTS.DYN_RCV_SN))
2727: AND transaction_header_id = p_txnHdrID
2728: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729: WIP_CONSTANTS.RETCOMP_ACTION,
2730: WIP_CONSTANTS.ISSNEGC_ACTION,
2724: OR
2725: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2726: WIP_CONSTANTS.DYN_RCV_SN))
2727: AND transaction_header_id = p_txnHdrID
2728: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729: WIP_CONSTANTS.RETCOMP_ACTION,
2730: WIP_CONSTANTS.ISSNEGC_ACTION,
2731: WIP_CONSTANTS.RETNEGC_ACTION);
2732: */
2725: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2726: WIP_CONSTANTS.DYN_RCV_SN))
2727: AND transaction_header_id = p_txnHdrID
2728: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729: WIP_CONSTANTS.RETCOMP_ACTION,
2730: WIP_CONSTANTS.ISSNEGC_ACTION,
2731: WIP_CONSTANTS.RETNEGC_ACTION);
2732: */
2733: --Updated as per Bug#9544120
2726: WIP_CONSTANTS.DYN_RCV_SN))
2727: AND transaction_header_id = p_txnHdrID
2728: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729: WIP_CONSTANTS.RETCOMP_ACTION,
2730: WIP_CONSTANTS.ISSNEGC_ACTION,
2731: WIP_CONSTANTS.RETNEGC_ACTION);
2732: */
2733: --Updated as per Bug#9544120
2734: SELECT COUNT(*)
2727: AND transaction_header_id = p_txnHdrID
2728: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2729: WIP_CONSTANTS.RETCOMP_ACTION,
2730: WIP_CONSTANTS.ISSNEGC_ACTION,
2731: WIP_CONSTANTS.RETNEGC_ACTION);
2732: */
2733: --Updated as per Bug#9544120
2734: SELECT COUNT(*)
2735: INTO l_lot_ser_count
2736: FROM mtl_transactions_interface mti,
2737: mtl_system_items msi
2738: WHERE mti.organization_id = msi.organization_id
2739: AND mti.inventory_item_id = msi.inventory_item_id
2740: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2741: OR
2742: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2743: WIP_CONSTANTS.DYN_RCV_SN)
2744: OR
2738: WHERE mti.organization_id = msi.organization_id
2739: AND mti.inventory_item_id = msi.inventory_item_id
2740: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2741: OR
2742: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2743: WIP_CONSTANTS.DYN_RCV_SN)
2744: OR
2745: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746: AND mti.transaction_header_id = p_txnHdrID
2739: AND mti.inventory_item_id = msi.inventory_item_id
2740: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
2741: OR
2742: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2743: WIP_CONSTANTS.DYN_RCV_SN)
2744: OR
2745: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746: AND mti.transaction_header_id = p_txnHdrID
2747: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2741: OR
2742: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2743: WIP_CONSTANTS.DYN_RCV_SN)
2744: OR
2745: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746: AND mti.transaction_header_id = p_txnHdrID
2747: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2748: WIP_CONSTANTS.RETCOMP_ACTION,
2749: WIP_CONSTANTS.ISSNEGC_ACTION,
2743: WIP_CONSTANTS.DYN_RCV_SN)
2744: OR
2745: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746: AND mti.transaction_header_id = p_txnHdrID
2747: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2748: WIP_CONSTANTS.RETCOMP_ACTION,
2749: WIP_CONSTANTS.ISSNEGC_ACTION,
2750: WIP_CONSTANTS.RETNEGC_ACTION)
2751: AND EXISTS (SELECT 1
2744: OR
2745: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746: AND mti.transaction_header_id = p_txnHdrID
2747: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2748: WIP_CONSTANTS.RETCOMP_ACTION,
2749: WIP_CONSTANTS.ISSNEGC_ACTION,
2750: WIP_CONSTANTS.RETNEGC_ACTION)
2751: AND EXISTS (SELECT 1
2752: FROM wip_requirement_operations wro
2745: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
2746: AND mti.transaction_header_id = p_txnHdrID
2747: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2748: WIP_CONSTANTS.RETCOMP_ACTION,
2749: WIP_CONSTANTS.ISSNEGC_ACTION,
2750: WIP_CONSTANTS.RETNEGC_ACTION)
2751: AND EXISTS (SELECT 1
2752: FROM wip_requirement_operations wro
2753: ,wip_entities we
2746: AND mti.transaction_header_id = p_txnHdrID
2747: AND mti.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2748: WIP_CONSTANTS.RETCOMP_ACTION,
2749: WIP_CONSTANTS.ISSNEGC_ACTION,
2750: WIP_CONSTANTS.RETNEGC_ACTION)
2751: AND EXISTS (SELECT 1
2752: FROM wip_requirement_operations wro
2753: ,wip_entities we
2754: WHERE wro.wip_entity_id = mti.transaction_source_id
2756: AND wro.wip_entity_id = we.wip_entity_id
2757: AND wro.inventory_item_id = mti.inventory_item_id
2758: AND wro.operation_seq_num = mti.operation_seq_num
2759: AND wro.organization_id = we.organization_id
2760: AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2761: we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2762: (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2763: );
2764:
2757: AND wro.inventory_item_id = mti.inventory_item_id
2758: AND wro.operation_seq_num = mti.operation_seq_num
2759: AND wro.organization_id = we.organization_id
2760: AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2761: we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2762: (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2763: );
2764:
2765: SELECT backflush_lot_entry_type
2758: AND wro.operation_seq_num = mti.operation_seq_num
2759: AND wro.organization_id = we.organization_id
2760: AND ((wro.wip_supply_type IN (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) AND /*Bug 10434581*/
2761: we.entity_type = WIP_CONSTANTS.DISCRETE) OR
2762: (we.entity_type <> WIP_CONSTANTS.DISCRETE))
2763: );
2764:
2765: SELECT backflush_lot_entry_type
2766: INTO l_lot_entry_type
2768: WHERE organization_id = p_orgID;
2769:
2770: IF(l_bf_count = 0) THEN
2771: -- There is no backflush components required for this transaction
2772: x_bfRequired := WIP_CONSTANTS.WBF_NOBF;
2773: x_lotSerRequired := WIP_CONSTANTS.NO;
2774: ELSE
2775: IF(l_lot_ser_count = 0) THEN
2776: -- no component under lot/serial control
2769:
2770: IF(l_bf_count = 0) THEN
2771: -- There is no backflush components required for this transaction
2772: x_bfRequired := WIP_CONSTANTS.WBF_NOBF;
2773: x_lotSerRequired := WIP_CONSTANTS.NO;
2774: ELSE
2775: IF(l_lot_ser_count = 0) THEN
2776: -- no component under lot/serial control
2777: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2773: x_lotSerRequired := WIP_CONSTANTS.NO;
2774: ELSE
2775: IF(l_lot_ser_count = 0) THEN
2776: -- no component under lot/serial control
2777: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2778: x_lotSerRequired := WIP_CONSTANTS.NO;
2779: ELSE
2780: IF(l_lot_entry_type = WIP_CONSTANTS.MAN_ENTRY) THEN
2781: -- If backflush lot entry type is set to manual, no need to do lot
2774: ELSE
2775: IF(l_lot_ser_count = 0) THEN
2776: -- no component under lot/serial control
2777: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2778: x_lotSerRequired := WIP_CONSTANTS.NO;
2779: ELSE
2780: IF(l_lot_entry_type = WIP_CONSTANTS.MAN_ENTRY) THEN
2781: -- If backflush lot entry type is set to manual, no need to do lot
2782: -- derivation
2776: -- no component under lot/serial control
2777: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2778: x_lotSerRequired := WIP_CONSTANTS.NO;
2779: ELSE
2780: IF(l_lot_entry_type = WIP_CONSTANTS.MAN_ENTRY) THEN
2781: -- If backflush lot entry type is set to manual, no need to do lot
2782: -- derivation
2783: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2784: x_lotSerRequired := WIP_CONSTANTS.YES;
2779: ELSE
2780: IF(l_lot_entry_type = WIP_CONSTANTS.MAN_ENTRY) THEN
2781: -- If backflush lot entry type is set to manual, no need to do lot
2782: -- derivation
2783: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2784: x_lotSerRequired := WIP_CONSTANTS.YES;
2785: ELSE
2786: -- derive lot for both Operation Pull and Assembly Pull components
2787: wip_autoLotProc_priv.deriveLotsFromMTI
2780: IF(l_lot_entry_type = WIP_CONSTANTS.MAN_ENTRY) THEN
2781: -- If backflush lot entry type is set to manual, no need to do lot
2782: -- derivation
2783: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2784: x_lotSerRequired := WIP_CONSTANTS.YES;
2785: ELSE
2786: -- derive lot for both Operation Pull and Assembly Pull components
2787: wip_autoLotProc_priv.deriveLotsFromMTI
2788: (p_orgID => p_orgID,
2797: IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
2798: l_errMsg := 'wip_autoLotProc_priv.deriveLotsFromMTI failed';
2799: raise fnd_api.g_exc_unexpected_error;
2800: ELSIF(l_returnStatus = fnd_api.g_ret_sts_error) THEN
2801: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2802: x_lotSerRequired := WIP_CONSTANTS.YES;
2803: ELSE -- succesfully derived lot
2804: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805: WIP_CONSTANTS.EXPDATE_FULL,
2798: l_errMsg := 'wip_autoLotProc_priv.deriveLotsFromMTI failed';
2799: raise fnd_api.g_exc_unexpected_error;
2800: ELSIF(l_returnStatus = fnd_api.g_ret_sts_error) THEN
2801: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2802: x_lotSerRequired := WIP_CONSTANTS.YES;
2803: ELSE -- succesfully derived lot
2804: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805: WIP_CONSTANTS.EXPDATE_FULL,
2806: /* Added for Wilson Greatbatch Enhancement */
2800: ELSIF(l_returnStatus = fnd_api.g_ret_sts_error) THEN
2801: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2802: x_lotSerRequired := WIP_CONSTANTS.YES;
2803: ELSE -- succesfully derived lot
2804: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805: WIP_CONSTANTS.EXPDATE_FULL,
2806: /* Added for Wilson Greatbatch Enhancement */
2807: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2801: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2802: x_lotSerRequired := WIP_CONSTANTS.YES;
2803: ELSE -- succesfully derived lot
2804: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805: WIP_CONSTANTS.EXPDATE_FULL,
2806: /* Added for Wilson Greatbatch Enhancement */
2807: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809: x_lotSerRequired := WIP_CONSTANTS.NO;
2803: ELSE -- succesfully derived lot
2804: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805: WIP_CONSTANTS.EXPDATE_FULL,
2806: /* Added for Wilson Greatbatch Enhancement */
2807: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809: x_lotSerRequired := WIP_CONSTANTS.NO;
2810: ELSE -- backflush lot entry page is exception only
2811: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2804: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2805: WIP_CONSTANTS.EXPDATE_FULL,
2806: /* Added for Wilson Greatbatch Enhancement */
2807: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809: x_lotSerRequired := WIP_CONSTANTS.NO;
2810: ELSE -- backflush lot entry page is exception only
2811: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2812: x_lotSerRequired := WIP_CONSTANTS.NO;
2805: WIP_CONSTANTS.EXPDATE_FULL,
2806: /* Added for Wilson Greatbatch Enhancement */
2807: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809: x_lotSerRequired := WIP_CONSTANTS.NO;
2810: ELSE -- backflush lot entry page is exception only
2811: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2812: x_lotSerRequired := WIP_CONSTANTS.NO;
2813: END IF;
2807: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809: x_lotSerRequired := WIP_CONSTANTS.NO;
2810: ELSE -- backflush lot entry page is exception only
2811: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2812: x_lotSerRequired := WIP_CONSTANTS.NO;
2813: END IF;
2814: END IF; -- check return status
2815: END IF; -- check lot entry type
2808: x_bfRequired := WIP_CONSTANTS.WBF_BF_PAGE;
2809: x_lotSerRequired := WIP_CONSTANTS.NO;
2810: ELSE -- backflush lot entry page is exception only
2811: x_bfRequired := WIP_CONSTANTS.WBF_BF_NOPAGE;
2812: x_lotSerRequired := WIP_CONSTANTS.NO;
2813: END IF;
2814: END IF; -- check return status
2815: END IF; -- check lot entry type
2816: END IF; -- l_lot_ser_count = 0
2815: END IF; -- check lot entry type
2816: END IF; -- l_lot_ser_count = 0
2817: END IF; -- l_bf_count = 0
2818:
2819: IF(p_tblName = WIP_CONSTANTS.MMTT_TBL) THEN
2820: -- Move record from mti to mmtt
2821: wip_mtlTempProc_priv.validateInterfaceTxns(
2822: p_txnHdrID => p_txnHdrID,
2823: p_addMsgToStack => fnd_api.g_true,
2831: END IF;
2832:
2833: -- Insert all necessary info that need to be used by inventory form to
2834: -- gather lot/serial from the user
2835: IF(x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE)THEN
2836: UPDATE mtl_material_transactions_temp mmtt
2837: SET (mmtt.item_segments,
2838: mmtt.item_description,
2839: mmtt.item_trx_enabled_flag,
2890: WHERE mmtt.transaction_header_id = p_txnHdrID
2891: AND mmtt.department_id IS NOT NULL;
2892:
2893: /*Update MMTT Lot number if only one lot is derived */
2894: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2895: WIP_CONSTANTS.EXPDATE_FULL,
2896: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2897:
2898: UPDATE mtl_material_transactions_temp mmtt
2891: AND mmtt.department_id IS NOT NULL;
2892:
2893: /*Update MMTT Lot number if only one lot is derived */
2894: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2895: WIP_CONSTANTS.EXPDATE_FULL,
2896: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2897:
2898: UPDATE mtl_material_transactions_temp mmtt
2899: SET mmtt.lot_number =
2892:
2893: /*Update MMTT Lot number if only one lot is derived */
2894: IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
2895: WIP_CONSTANTS.EXPDATE_FULL,
2896: WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
2897:
2898: UPDATE mtl_material_transactions_temp mmtt
2899: SET mmtt.lot_number =
2900: ( SELECT mtlt.lot_number
2904: FROM mtl_transaction_lots_temp mtlt
2905: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
2906: )
2907: WHERE mmtt.transaction_header_id = p_txnHdrID
2908: AND mmtt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2909: WIP_CONSTANTS.RETCOMP_ACTION,
2910: WIP_CONSTANTS.ISSNEGC_ACTION,
2911: WIP_CONSTANTS.RETNEGC_ACTION);
2912:
2905: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
2906: )
2907: WHERE mmtt.transaction_header_id = p_txnHdrID
2908: AND mmtt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2909: WIP_CONSTANTS.RETCOMP_ACTION,
2910: WIP_CONSTANTS.ISSNEGC_ACTION,
2911: WIP_CONSTANTS.RETNEGC_ACTION);
2912:
2913: /* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement from below */
2906: )
2907: WHERE mmtt.transaction_header_id = p_txnHdrID
2908: AND mmtt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2909: WIP_CONSTANTS.RETCOMP_ACTION,
2910: WIP_CONSTANTS.ISSNEGC_ACTION,
2911: WIP_CONSTANTS.RETNEGC_ACTION);
2912:
2913: /* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement from below */
2914: DELETE FROM mtl_transaction_lots_temp mtlt
2907: WHERE mmtt.transaction_header_id = p_txnHdrID
2908: AND mmtt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2909: WIP_CONSTANTS.RETCOMP_ACTION,
2910: WIP_CONSTANTS.ISSNEGC_ACTION,
2911: WIP_CONSTANTS.RETNEGC_ACTION);
2912:
2913: /* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement from below */
2914: DELETE FROM mtl_transaction_lots_temp mtlt
2915: WHERE EXISTS
2918: wip_entities we
2919: WHERE mmtt.transaction_header_id = p_txnHdrID
2920: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
2921: AND mmtt.transaction_source_id = we.wip_entity_id
2922: AND we.entity_type = wip_constants.lotbased
2923: AND 1 = (SELECT count(*)
2924: FROM mtl_transaction_lots_temp mtlt2
2925: WHERE mtlt2.transaction_temp_id =
2926: mtlt.transaction_temp_id
2941: WHERE mmtt.transaction_temp_id =
2942: mtlt.transaction_temp_id
2943: )
2944: AND mmtt.transaction_source_id = we.wip_entity_id
2945: AND we.entity_type = wip_constants.lotbased
2946: ) ;
2947: */
2948: /* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement above inside the IF condition. The rows should be deleted from MTLT
2949: only for the case of Lot Verification=All after the lot has been stamped on MMTT. For Lot Verification=Exception Only
2955: wip_entities we
2956: WHERE mmtt.transaction_header_id = p_txnHdrID
2957: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
2958: AND mmtt.transaction_source_id = we.wip_entity_id
2959: AND we.entity_type = wip_constants.lotbased
2960: AND 1 = (SELECT count(*)
2961: FROM mtl_transaction_lots_temp mtlt2
2962: WHERE mtlt2.transaction_temp_id =
2963: mtlt.transaction_temp_id
2975: (SELECT NVL(SUM(mtlt.transaction_quantity),0)
2976: FROM mtl_transaction_lots_temp mtlt
2977: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
2978: WHERE mmtt.transaction_header_id = p_txnHdrID
2979: AND (mmtt.item_serial_control_code IN(WIP_CONSTANTS.FULL_SN,
2980: WIP_CONSTANTS.DYN_RCV_SN)
2981: OR
2982: mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
2983: */
2976: FROM mtl_transaction_lots_temp mtlt
2977: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
2978: WHERE mmtt.transaction_header_id = p_txnHdrID
2979: AND (mmtt.item_serial_control_code IN(WIP_CONSTANTS.FULL_SN,
2980: WIP_CONSTANTS.DYN_RCV_SN)
2981: OR
2982: mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
2983: */
2984: END IF; -- x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE
2978: WHERE mmtt.transaction_header_id = p_txnHdrID
2979: AND (mmtt.item_serial_control_code IN(WIP_CONSTANTS.FULL_SN,
2980: WIP_CONSTANTS.DYN_RCV_SN)
2981: OR
2982: mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
2983: */
2984: END IF; -- x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE
2985: END IF; -- WIP_CONSTANTS.MMTT_TBL
2986:
2980: WIP_CONSTANTS.DYN_RCV_SN)
2981: OR
2982: mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
2983: */
2984: END IF; -- x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE
2985: END IF; -- WIP_CONSTANTS.MMTT_TBL
2986:
2987: x_returnStatus := fnd_api.g_ret_sts_success;
2988: IF (l_logLevel <= wip_constants.trace_logging) THEN
2981: OR
2982: mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
2983: */
2984: END IF; -- x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE
2985: END IF; -- WIP_CONSTANTS.MMTT_TBL
2986:
2987: x_returnStatus := fnd_api.g_ret_sts_success;
2988: IF (l_logLevel <= wip_constants.trace_logging) THEN
2989: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
2984: END IF; -- x_bfRequired = WIP_CONSTANTS.WBF_BF_PAGE
2985: END IF; -- WIP_CONSTANTS.MMTT_TBL
2986:
2987: x_returnStatus := fnd_api.g_ret_sts_success;
2988: IF (l_logLevel <= wip_constants.trace_logging) THEN
2989: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
2990: p_procReturnStatus => x_returnStatus,
2991: p_msg => 'Succesfully inserted components into MMTT',
2992: x_returnStatus => l_returnStatus);
2994: EXCEPTION
2995: WHEN fnd_api.g_exc_unexpected_error THEN
2996: ROLLBACK TO SAVEPOINT s_backflush;
2997: x_returnStatus := fnd_api.g_ret_sts_error;
2998: IF (l_logLevel <= wip_constants.trace_logging) THEN
2999: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3000: p_procReturnStatus => x_returnStatus,
3001: p_msg => l_errMsg,
3002: x_returnStatus => l_returnStatus);
3003: END IF;
3004: WHEN others THEN
3005: ROLLBACK TO SAVEPOINT s_backflush;
3006: x_returnStatus := fnd_api.g_ret_sts_error;
3007: IF (l_logLevel <= wip_constants.trace_logging) THEN
3008: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3009: p_procReturnStatus => x_returnStatus,
3010: p_msg => 'Unexpected error : ' || SQLERRM,
3011: x_returnStatus => l_returnStatus);
3018: l_curItem system.wip_component_obj_t;
3019: BEGIN
3020: LOOP
3021: IF(p_compInfo.getCurrentItem(l_curItem)) THEN
3022: IF(l_curItem.transaction_action_id = WIP_CONSTANTS.RETNEGC_ACTION
3023: AND
3024: (l_curItem.lot_control_code = WIP_CONSTANTS.LOT
3025: OR
3026: l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
3020: LOOP
3021: IF(p_compInfo.getCurrentItem(l_curItem)) THEN
3022: IF(l_curItem.transaction_action_id = WIP_CONSTANTS.RETNEGC_ACTION
3023: AND
3024: (l_curItem.lot_control_code = WIP_CONSTANTS.LOT
3025: OR
3026: l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
3027: WIP_CONSTANTS.DYN_RCV_SN))
3028: ) THEN
3022: IF(l_curItem.transaction_action_id = WIP_CONSTANTS.RETNEGC_ACTION
3023: AND
3024: (l_curItem.lot_control_code = WIP_CONSTANTS.LOT
3025: OR
3026: l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
3027: WIP_CONSTANTS.DYN_RCV_SN))
3028: ) THEN
3029: -- Return after the first negative lot/serial component found.
3030: RETURN WIP_CONSTANTS.YES;
3023: AND
3024: (l_curItem.lot_control_code = WIP_CONSTANTS.LOT
3025: OR
3026: l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
3027: WIP_CONSTANTS.DYN_RCV_SN))
3028: ) THEN
3029: -- Return after the first negative lot/serial component found.
3030: RETURN WIP_CONSTANTS.YES;
3031: END IF;
3026: l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
3027: WIP_CONSTANTS.DYN_RCV_SN))
3028: ) THEN
3029: -- Return after the first negative lot/serial component found.
3030: RETURN WIP_CONSTANTS.YES;
3031: END IF;
3032: END IF; -- getCurrentItem
3033: EXIT WHEN NOT p_compInfo.setNextItem;
3034: END LOOP;
3032: END IF; -- getCurrentItem
3033: EXIT WHEN NOT p_compInfo.setNextItem;
3034: END LOOP;
3035: -- No negative lot/serial component.
3036: RETURN WIP_CONSTANTS.NO;
3037: END NegLSCompExist;
3038:
3039: PROCEDURE backflush(p_wipEntityID IN NUMBER,
3040: p_orgID IN NUMBER,
3069: l_last_bf_op NUMBER;
3070: l_bf_qty NUMBER;
3071: l_compTbl system.wip_component_tbl_t;
3072: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3073: l_backwardMove NUMBER := WIP_CONSTANTS.NO;
3074: BEGIN
3075: SAVEPOINT s_backflush2;
3076: -- write parameter value to log file
3077: IF (l_logLevel <= wip_constants.trace_logging) THEN
3073: l_backwardMove NUMBER := WIP_CONSTANTS.NO;
3074: BEGIN
3075: SAVEPOINT s_backflush2;
3076: -- write parameter value to log file
3077: IF (l_logLevel <= wip_constants.trace_logging) THEN
3078: l_params(1).paramName := 'p_wipEntityID';
3079: l_params(1).paramValue := p_wipEntityID;
3080: l_params(2).paramName := 'p_orgID';
3081: l_params(2).paramValue := p_orgID;
3116: FROM wip_operations wo,
3117: wip_discrete_jobs wdj
3118: WHERE wdj.organization_id = wo.organization_id
3119: AND wdj.wip_entity_id = wo.wip_entity_id
3120: AND wdj.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
3121: AND wdj.wip_entity_id = p_wipEntityID
3122: AND wdj.organization_id = p_orgID;
3123:
3124: IF(l_first_op IS NULL) THEN
3135:
3136: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
3137: p_toOp IS NULL OR p_toStep IS NULL) THEN
3138: -- Completion/return transactions
3139: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3140: -- Completion transaction
3141: l_fm_op := l_last_op;
3142: l_fm_step := WIP_CONSTANTS.TOMOVE;
3143: l_to_op := NULL;
3138: -- Completion/return transactions
3139: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3140: -- Completion transaction
3141: l_fm_op := l_last_op;
3142: l_fm_step := WIP_CONSTANTS.TOMOVE;
3143: l_to_op := NULL;
3144: l_to_step := NULL;
3145: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3146: -- Return transaction
3141: l_fm_op := l_last_op;
3142: l_fm_step := WIP_CONSTANTS.TOMOVE;
3143: l_to_op := NULL;
3144: l_to_step := NULL;
3145: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3146: -- Return transaction
3147: l_fm_op := NULL;
3148: l_fm_step := NULL;
3149: l_to_op := l_last_op;
3146: -- Return transaction
3147: l_fm_op := NULL;
3148: l_fm_step := NULL;
3149: l_to_op := l_last_op;
3150: l_to_step := WIP_CONSTANTS.TOMOVE;
3151: END IF;
3152: ELSE -- Move related transactions
3153: l_fm_op := p_fmOp;
3154: l_fm_step := p_fmStep;
3156: l_to_step := p_toStep;
3157: -- Check whether it is a backward move
3158: IF (l_fm_op > l_to_op OR
3159: (l_fm_op = l_to_op AND l_fm_step > l_to_step)) THEN
3160: l_backwardMove := WIP_CONSTANTS.YES;
3161: ELSE
3162: l_backwardMove := WIP_CONSTANTS.NO;
3163: END IF;
3164: END IF;
3158: IF (l_fm_op > l_to_op OR
3159: (l_fm_op = l_to_op AND l_fm_step > l_to_step)) THEN
3160: l_backwardMove := WIP_CONSTANTS.YES;
3161: ELSE
3162: l_backwardMove := WIP_CONSTANTS.NO;
3163: END IF;
3164: END IF;
3165:
3166: -- Check whether it is move related transactions or not
3196: p_movTxnID => p_movTxnID,
3197: p_orgID => p_orgID,
3198: p_assyQty => l_bf_qty,
3199: p_txnDate => p_txnDate,
3200: p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
3201: p_txnHdrID => p_txnHdrID,
3202: p_firstOp => l_first_bf_op,
3203: p_lastOP => l_last_bf_op,
3204: p_firstMoveOp => l_fm_op,
3205: p_lastMoveOp => l_to_op,
3206: p_mergeMode => fnd_api.g_false,
3207: p_initMsgList => fnd_api.g_false,
3208: p_endDebug => fnd_api.g_false,
3209: p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3210: x_compTbl => l_compTbl,
3211: x_returnStatus => l_returnStatus);
3212:
3213: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3250: p_movTxnID => p_movTxnID,
3251: p_orgID => p_orgID,
3252: p_assyQty => l_bf_qty,
3253: p_txnDate => p_txnDate,
3254: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
3255: p_txnHdrID => p_txnHdrID,
3256: p_firstOp => l_first_bf_op,
3257: p_lastOP => l_last_bf_op,
3258: p_firstMoveOp => l_fm_op,
3259: p_lastMoveOp => l_to_op,
3260: p_mergeMode => fnd_api.g_false,
3261: p_initMsgList => fnd_api.g_false,
3262: p_endDebug => fnd_api.g_false,
3263: p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3264: x_compTbl => l_compTbl,
3265: x_returnStatus => l_returnStatus);
3266:
3267: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3270: END IF;
3271: END IF; -- l_first_bf_op <> -1
3272: END IF; -- Move related transactions
3273:
3274: IF(p_txnType = WIP_CONSTANTS.COMP_TXN OR
3275: p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3276:
3277: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3278: l_bf_qty := p_primaryQty;
3271: END IF; -- l_first_bf_op <> -1
3272: END IF; -- Move related transactions
3273:
3274: IF(p_txnType = WIP_CONSTANTS.COMP_TXN OR
3275: p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3276:
3277: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3278: l_bf_qty := p_primaryQty;
3279: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3273:
3274: IF(p_txnType = WIP_CONSTANTS.COMP_TXN OR
3275: p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3276:
3277: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3278: l_bf_qty := p_primaryQty;
3279: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3280: l_bf_qty := -1 * p_primaryQty;
3281: END IF;
3275: p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3276:
3277: IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
3278: l_bf_qty := p_primaryQty;
3279: ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
3280: l_bf_qty := -1 * p_primaryQty;
3281: END IF;
3282: wip_bflProc_priv.processRequirements
3283: (p_wipEntityID => p_wipEntityID,
3289: p_movTxnID => p_movTxnID,
3290: p_orgID => p_orgID,
3291: p_assyQty => l_bf_qty,
3292: p_txnDate => p_txnDate,
3293: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
3294: p_txnHdrID => p_txnHdrID,
3295: p_firstOp => -1,
3296: p_lastOP => l_last_op,
3297: p_firstMoveOp => null,
3298: p_lastMoveOp => null,
3299: p_mergeMode => fnd_api.g_false,
3300: p_initMsgList => fnd_api.g_false,
3301: p_endDebug => fnd_api.g_false,
3302: p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3303: x_compTbl => l_compTbl,
3304: x_returnStatus => l_returnStatus);
3305:
3306: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3312: -- Check whether overcompletion
3313: IF(p_childMovTxnID IS NOT NULL AND p_ocQty IS NOT NULL) THEN
3314: -- overmove/overcomplete
3315: l_fm_op := l_first_op;
3316: l_fm_step := WIP_CONSTANTS.QUEUE;
3317: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
3318: p_toOp IS NULL OR p_toStep IS NULL) THEN
3319: -- Call from Completion form
3320: l_to_op := l_last_op;
3317: IF(p_fmOp IS NULL OR p_fmStep IS NULL OR
3318: p_toOp IS NULL OR p_toStep IS NULL) THEN
3319: -- Call from Completion form
3320: l_to_op := l_last_op;
3321: l_to_step := WIP_CONSTANTS.TOMOVE;
3322: ELSE -- Call from WIP Move or OSFM Move forms
3323: l_to_op := p_fmOp;
3324: l_to_step := p_fmStep;
3325: END IF;
3355: p_movTxnID => p_childMovTxnID,
3356: p_orgID => p_orgID,
3357: p_assyQty => l_bf_qty,
3358: p_txnDate => p_txnDate,
3359: p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
3360: p_txnHdrID => p_txnHdrID,
3361: p_firstOp => l_first_bf_op,
3362: p_lastOP => l_last_bf_op,
3363: p_firstMoveOp => l_fm_op,
3364: p_lastMoveOp => l_to_op,
3365: p_mergeMode => fnd_api.g_false,
3366: p_initMsgList => fnd_api.g_false,
3367: p_endDebug => fnd_api.g_false,
3368: p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3369: x_compTbl => l_compTbl,
3370: x_returnStatus => l_returnStatus);
3371:
3372: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3409: p_movTxnID => p_childMovTxnID,
3410: p_orgID => p_orgID,
3411: p_assyQty => l_bf_qty,
3412: p_txnDate => p_txnDate,
3413: p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
3414: p_txnHdrID => p_txnHdrID,
3415: p_firstOp => l_first_bf_op,
3416: p_lastOP => l_last_bf_op,
3417: p_firstMoveOp => l_fm_op,
3418: p_lastMoveOp => l_to_op,
3419: p_mergeMode => fnd_api.g_false,
3420: p_initMsgList => fnd_api.g_false,
3421: p_endDebug => fnd_api.g_false,
3422: p_mtlTxnMode => WIP_CONSTANTS.ONLINE,
3423: x_compTbl => l_compTbl,
3424: x_returnStatus => l_returnStatus);
3425:
3426: IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
3429: END IF;
3430: END IF; -- l_first_bf_op <> -1
3431: END IF; -- Overmove/ Overcompletion
3432:
3433: if (l_logLevel <= wip_constants.full_logging) then
3434: wip_logger.log(p_msg => 'before system.wip_lot_serial_obj_t',
3435: x_returnStatus => l_returnStatus);
3436: end if;
3437: x_compInfo := system.wip_lot_serial_obj_t(null, null, null, l_compTbl,
3435: x_returnStatus => l_returnStatus);
3436: end if;
3437: x_compInfo := system.wip_lot_serial_obj_t(null, null, null, l_compTbl,
3438: null, null);
3439: if (l_logLevel <= wip_constants.full_logging) then
3440: wip_logger.log(p_msg => 'after system.wip_lot_serial_obj_t',
3441: x_returnStatus => l_returnStatus);
3442: end if;
3443: x_compInfo.initialize;
3440: wip_logger.log(p_msg => 'after system.wip_lot_serial_obj_t',
3441: x_returnStatus => l_returnStatus);
3442: end if;
3443: x_compInfo.initialize;
3444: if (l_logLevel <= wip_constants.full_logging) then
3445: wip_logger.log(p_msg => 'after x_compInfo.initialize',
3446: x_returnStatus => l_returnStatus);
3447: end if;
3448:
3448:
3449: -- If serialized return or serialized backward move, we have to derive
3450: -- lot and serial information from genealogy.
3451: IF (p_objectID IS NOT NULL AND
3452: (p_txnType = WIP_CONSTANTS.RET_TXN OR
3453: l_backwardMove = WIP_CONSTANTS.YES)) THEN
3454: -- Derive lot control only from genealogy
3455: wip_autoLotProc_priv.deriveLotsFromMOG(x_compLots => x_compInfo,
3456: p_orgID => p_orgID,
3449: -- If serialized return or serialized backward move, we have to derive
3450: -- lot and serial information from genealogy.
3451: IF (p_objectID IS NOT NULL AND
3452: (p_txnType = WIP_CONSTANTS.RET_TXN OR
3453: l_backwardMove = WIP_CONSTANTS.YES)) THEN
3454: -- Derive lot control only from genealogy
3455: wip_autoLotProc_priv.deriveLotsFromMOG(x_compLots => x_compInfo,
3456: p_orgID => p_orgID,
3457: p_objectID => p_objectID,
3471: IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
3472: raise fnd_api.g_exc_unexpected_error;
3473: END IF;
3474:
3475: IF(NegLSCompExist(p_compInfo => x_compInfo) = WIP_CONSTANTS.YES) THEN
3476: x_lotSerRequired := WIP_CONSTANTS.YES;
3477: ELSE
3478: x_lotSerRequired := WIP_CONSTANTS.NO;
3479: END IF;
3472: raise fnd_api.g_exc_unexpected_error;
3473: END IF;
3474:
3475: IF(NegLSCompExist(p_compInfo => x_compInfo) = WIP_CONSTANTS.YES) THEN
3476: x_lotSerRequired := WIP_CONSTANTS.YES;
3477: ELSE
3478: x_lotSerRequired := WIP_CONSTANTS.NO;
3479: END IF;
3480: ELSE
3474:
3475: IF(NegLSCompExist(p_compInfo => x_compInfo) = WIP_CONSTANTS.YES) THEN
3476: x_lotSerRequired := WIP_CONSTANTS.YES;
3477: ELSE
3478: x_lotSerRequired := WIP_CONSTANTS.NO;
3479: END IF;
3480: ELSE
3481: -- derive lot if the component under lot control, if return status
3482: -- is 'E' mean cannot derive lot, so the user need to provide more
3495: IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
3496: l_errMsg := 'wip_autoLotProc_priv.deriveLots failed';
3497: raise fnd_api.g_exc_unexpected_error;
3498: ELSIF(x_returnStatus = fnd_api.g_ret_sts_error) THEN
3499: x_lotSerRequired := WIP_CONSTANTS.YES;
3500: ELSE -- succesfully derived lot
3501: x_lotSerRequired := WIP_CONSTANTS.NO;
3502: END IF;-- check return status
3503: END IF; -- check serialized return or serialized backward move
3497: raise fnd_api.g_exc_unexpected_error;
3498: ELSIF(x_returnStatus = fnd_api.g_ret_sts_error) THEN
3499: x_lotSerRequired := WIP_CONSTANTS.YES;
3500: ELSE -- succesfully derived lot
3501: x_lotSerRequired := WIP_CONSTANTS.NO;
3502: END IF;-- check return status
3503: END IF; -- check serialized return or serialized backward move
3504:
3505: x_returnStatus := fnd_api.g_ret_sts_success;
3502: END IF;-- check return status
3503: END IF; -- check serialized return or serialized backward move
3504:
3505: x_returnStatus := fnd_api.g_ret_sts_success;
3506: IF (l_logLevel <= wip_constants.trace_logging) THEN
3507: wip_logger.exitPoint(
3508: p_procName => 'wip_bflProc_priv.backflush',
3509: p_procReturnStatus => x_returnStatus,
3510: p_msg => 'Succesfully inserted components into PL/SQL object',
3513: EXCEPTION
3514: WHEN fnd_api.g_exc_unexpected_error THEN
3515: ROLLBACK TO SAVEPOINT s_backflush2;
3516: x_returnStatus := fnd_api.g_ret_sts_error;
3517: IF (l_logLevel <= wip_constants.trace_logging) THEN
3518: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3519: p_procReturnStatus => x_returnStatus,
3520: p_msg => l_errMsg,
3521: x_returnStatus => l_returnStatus);
3522: END IF;
3523: WHEN others THEN
3524: ROLLBACK TO SAVEPOINT s_backflush2;
3525: x_returnStatus := fnd_api.g_ret_sts_error;
3526: IF (l_logLevel <= wip_constants.trace_logging) THEN
3527: wip_logger.exitPoint(p_procName => 'wip_bflProc_priv.backflush',
3528: p_procReturnStatus => x_returnStatus,
3529: p_msg => 'Unexpected error : ' || SQLERRM,
3530: x_returnStatus => l_returnStatus);