[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mti.inventory_item_id itemID,
mti.transaction_interface_id txnIntID,
mti.transaction_action_id txnActionID,
mti.operation_seq_num opSeqNum,
-1 * mti.primary_quantity priQty, --make qty relative to wip (relative to inv in table)
-1 * mti.transaction_quantity txnQty, --make qty relative to wip (relative to inv in table)
msi.lot_control_code lotControlCode,
msi.serial_number_control_code serialNumControlCode
from mtl_transactions_interface mti,
mtl_system_items_b msi
where mti.transaction_header_id = p_txnHdrID
and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
wip_constants.issnegc_action, wip_constants.retnegc_action)
and mti.operation_seq_num between p_firstOp and p_lastOp
and msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and ( ( p_cplTxnID is null
and p_movTxnID is null)
or mti.move_transaction_id = p_movTxnID
or mti.completion_transaction_id = p_cplTxnID
)
/* group by mmtt.inventory_item_id,
mmtt.transaction_action_id,
mmtt.operation_seq_num,
msi.lot_control_code,
msi.serial_number_control_code
*/ order by mti.inventory_item_id, mti.operation_seq_num;
select wro.inventory_item_id,
msi.concatenated_segments,
wro.department_id,
wro.organization_id,
wro.operation_seq_num,
wo.count_point_type,
wro.supply_subinventory,
wro.supply_locator_id,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
wro.quantity_per_assembly,
/* begin LBM Project */
wro.required_quantity,
wro.quantity_issued,
wo.quantity_completed,
wdj.quantity_completed,
wdj.quantity_scrapped,
wro.basis_type,
/* end LBM Project */
nvl(wro.component_yield_factor,1), /* ER 4369064 */
msi.primary_uom_code,
msi.lot_control_code,
msi.serial_number_control_code,
msi.revision_qty_control_code,
mil.segment19,
mil.segment20,
wdj.project_id,
wdj.task_id,
msi.description,
decode(mp.project_reference_enabled,
null,milk.concatenated_segments,
2,milk.concatenated_segments,
1, inv_project.get_pjm_locsegs(milk.concatenated_segments)),
msi.revision_qty_control_code,
msi.location_control_code,
mil.project_id,
mil.task_id
from wip_requirement_operations wro,
mtl_system_items_kfv msi,
wip_operations wo,
mtl_item_locations_kfv milk,
wip_discrete_jobs wdj,
mtl_parameters mp,
-- Fixed bug 4692413. We should not refer to column in kfv directly.
mtl_item_locations mil
where wro.inventory_item_id = msi.inventory_item_id
and wro.organization_id = msi.organization_id
and wro.organization_id = mp.organization_id
and wro.wip_entity_id = p_wipEntityID
and wro.wip_supply_type = p_wipSupplyType
and wro.quantity_per_assembly <> 0
and wro.operation_seq_num between p_firstOp and p_lastOp
and wro.wip_entity_id = wdj.wip_entity_id
and wro.organization_id = wdj.organization_id
and wro.wip_entity_id = wo.wip_entity_id (+)
and wro.operation_seq_num = wo.operation_seq_num (+)
/* added for OSFM jump enhancement 2541431 */
and nvl(wo.skip_flag, WIP_CONSTANTS.NO) <> WIP_CONSTANTS.YES
and wro.supply_locator_id = mil.inventory_location_id (+)
and wro.organization_id = mil.organization_id (+)
and wro.supply_locator_id = milk.inventory_location_id (+)
and wro.organization_id = milk.organization_id (+)
order by wro.inventory_item_id, wro.operation_seq_num;
select wro.inventory_item_id,
msi.concatenated_segments,
wro.department_id,
wro.organization_id,
wro.operation_seq_num,
wo.count_point_type,
wro.supply_subinventory,
wro.supply_locator_id,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
wro.quantity_per_assembly,
/* LBM Project */
wro.required_quantity,
wro.quantity_issued,
wo.quantity_completed,
wrs.quantity_completed,
0, -- quantity_scrapped
wro.basis_type,
/* LBM Project */
nvl(wro.component_yield_factor,1), /* ER 4369064 */
msi.primary_uom_code,
msi.lot_control_code,
msi.serial_number_control_code,
msi.revision_qty_control_code,
mil.segment19,
mil.segment20,
null,
null,
msi.description,
decode(mp.project_reference_enabled,
null,milk.concatenated_segments,
2,milk.concatenated_segments,
1, inv_project.get_pjm_locsegs(milk.concatenated_segments)),
msi.revision_qty_control_code,
msi.location_control_code,
mil.project_id,
mil.task_id
from wip_requirement_operations wro,
wip_repetitive_schedules wrs,
wip_repetitive_items wri,
mtl_system_items_kfv msi,
wip_operations wo,
mtl_item_locations_kfv milk,
mtl_parameters mp,
-- Fixed bug 4692413. We should not refer to column in kfv directly.
mtl_item_locations mil
where wro.wip_entity_id = p_wipEntityID
and wro.repetitive_schedule_id = p_repSchedID
and wro.wip_supply_type = p_wipSupplyType
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.quantity_per_assembly <> 0
and wro.operation_seq_num between p_firstOp and p_lastOp
and wri.wip_entity_id = wrs.wip_entity_id
and wri.line_id = wrs.line_id
and msi.inventory_item_id = wro.inventory_item_id /* Fix bug#4233474 */
and msi.organization_id = wro.organization_id /* Fix bug#4233474 */
and msi.organization_id = mp.organization_id
and wro.wip_entity_id = wo.wip_entity_id (+)
and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
and wro.operation_seq_num = wo.operation_seq_num (+)
and wro.supply_locator_id = mil.inventory_location_id (+)
and wro.organization_id = mil.organization_id (+)
and wro.supply_locator_id = milk.inventory_location_id (+)
and wro.organization_id = milk.organization_id (+)
order by wro.inventory_item_id, wro.operation_seq_num;
select 1
from mtl_transactions_interface mti
where mti.transaction_source_id = p_wipEntityID
and mti.organization_id = p_orgID
and mti.operation_seq_num = x_opSeqNum
and mti.inventory_item_id = x_inventoryItemID
and mti.transaction_action_id = x_txnActionID
and mti.transaction_type_id = x_txnTypeID;
select 1
from mtl_material_transactions_temp mmtt
where mmtt.transaction_source_id = p_wipEntityID
and mmtt.organization_id = p_orgID
and mmtt.operation_seq_num = x_opSeqNum
and mmtt.inventory_item_id = x_inventoryItemID
and mmtt.transaction_action_id = x_txnActionID
and mmtt.transaction_type_id = x_txnTypeID;
select nvl(include_component_yield,1)
into l_include_yield
from wip_parameters
where organization_id = p_orgID;
/*select include_component_yield
into l_include_yield
from wip_parameters
where organization_id = p_orgID; -- moved above */
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
transaction_quantity = transaction_quantity - l_compQty, --subtract b/c l_compQty is relative to WIP
primary_quantity = primary_quantity - l_compQty --subtract b/c l_compQty is relative to WIP
where transaction_interface_id = l_popRec.txnIntID;
wip_logger.log('inserting item:' || l_reqRec.itemID || ' opSeq:' || l_reqRec.opSeqNum || ' qty:' || l_compQty * -1,l_returnStatus);
SELECT created_by
INTO l_created_by
FROM wip_move_transactions
WHERE TRANSACTION_ID = p_movTxnID;
insert into mtl_transactions_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
transaction_header_id,
transaction_interface_id,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
transaction_action_id,
transaction_date,
transaction_quantity,
transaction_uom,
primary_quantity,
wip_supply_type,
wip_entity_type,
inventory_item_id,
revision,
operation_seq_num,
department_id,
organization_id,
process_flag,
-- posting_flag,
subinventory_code,
locator_id,
acct_period_id,
completion_transaction_id,
move_transaction_id,
repetitive_line_id,
negative_req_flag,
-- item_serial_control_code,
-- item_lot_control_code,
source_code,
source_header_id,
source_line_id,
project_id,
task_id,
source_project_id,
source_task_id,
transaction_mode,
transaction_batch_id,
transaction_batch_seq,
lock_flag,
reason_id,
transaction_reference)
values
(sysdate,
fnd_global.user_id,
sysdate,
l_created_by,/* Fix for Bug 5444243 */
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
p_txnHdrID,
mtl_material_transactions_s.nextval,
p_wipEntityID,
5,
l_txnTypeID,
l_txnActionID,
p_txnDate,
-1 * l_compQty, --make quantity relative to inventory
l_reqRec.priUomCode,
-1 * l_compQty,
p_wipSupplyType,
p_wipEntityType,
l_reqRec.itemID,
l_revision,
l_reqRec.opSeqNum,
l_reqRec.deptID,
l_reqRec.orgID,
wip_constants.mti_inventory,
-- 'Y',
l_reqRec.supplySub,
l_reqRec.supplyLocID,
l_acctPeriodID,
p_cplTxnID,
p_movTxnID,
p_repLineID,
decode(l_txnActionID,
wip_constants.isscomp_action, 1,
wip_constants.retcomp_action, 1,
wip_constants.issnegc_action, -1,
wip_constants.retnegc_action, -1),
-- l_reqRec.serialNumControlCode,
-- l_reqRec.lotControlCode,
nvl(p_srcCode, 'WIP Backflush'),
p_wipEntityID,
l_reqRec.opSeqNum,
l_reqRec.projectID,
l_reqRec.taskID,
l_reqRec.srcProjectID,
l_reqRec.srcTaskID,
p_mtlTxnMode,
p_batchID,
NVL(p_batchSeq,wip_constants.component_batch_seq),
p_lockFlag,
p_reasonID,
p_reference);
select be.operation_seq_num opSeqNum,
be.component_item_id itemID,
be.component_quantity priQty,
be.component_yield_factor compYield,
msi.primary_uom_code priUomCode,
msi.mtl_transactions_enabled_flag txnsEnabledFlag,
decode(msi.shrinkage_rate, 1, 0, null, 0, msi.shrinkage_rate) shrinkageRate,
decode(be.organization_id, v_orgID, bic.supply_subinventory, null) supplySubinv,
decode(be.organization_id, v_orgID, bic.supply_locator_id, null) supplyLocID,
nvl(bic.wip_supply_type , msi.wip_supply_type) wipSupplyType, /* 2695355 */
be.component_code compCode,
be.rowid beRowID,
be.plan_level bomLevel, --level of nesting. 1 is a top level component
bic.basis_type /* LBM Project */
from bom_explosion_temp be,
bom_inventory_components bic,
mtl_system_items msi
where be.group_id = v_grpID
and be.component_sequence_id = bic.component_sequence_id
and be.component_item_id = msi.inventory_item_id
and be.component_item_id <> p_itemID --exclude assy if it is in the table
and msi.bom_item_type not in (wip_constants.model_type,
wip_constants.option_class_type) /* Fix for 4575119 */
and msi.organization_id = v_orgID
order by be.component_code;
select be.operation_seq_num opSeqNum,
msi.concatenated_segments itemName,
be.component_item_id itemID,
sum(be.component_quantity) priQty,/*For Component Yield Enhancement(Bug 4369064)->Removed yield consideration */
msi.primary_uom_code priUomCode,
msi.restrict_subinventories_code restrictSubs,
msi.restrict_locators_code restrictLocs,
decode(msi.shrinkage_rate, 1, 0, null, 0, msi.shrinkage_rate) shrinkageRate,
decode(be.organization_id, v_orgID, bic.supply_subinventory, null) supplySubinv,
decode(be.organization_id, v_orgID, bic.supply_locator_id, null) supplyLocID,
bic.component_sequence_id componentSeqID,
nvl(bic.wip_supply_type , msi.wip_supply_type) wipSupplyType, /* 2695355 */
msi.mtl_transactions_enabled_flag txnsEnabledFlag,
msi.revision_qty_control_code revControlCode,
msi.serial_number_control_code serialNumControlCode,
msi.lot_control_code lotControlCode,
msi.end_assembly_pegging_flag pegFlag,
be.component_yield_factor compYield, /*For Component Yield Enhancement(Bug 4369064) */
bic.basis_type, /* LBM Project */
/* Add more item for flow OA project */
msi.description itemDesc,
msi.location_control_code locControlCode,
decode(mp.project_reference_enabled,
null,milk.concatenated_segments,
2,milk.concatenated_segments,
1, inv_project.get_pjm_locsegs(milk.concatenated_segments)) locatorName
from bom_explosion_temp be,
bom_inventory_components bic,
mtl_system_items_kfv msi,
mtl_item_locations_kfv milk,
mtl_parameters mp
where be.group_id = v_grpID
and be.component_sequence_id = bic.component_sequence_id
and be.component_item_id = msi.inventory_item_id
and be.component_item_id <> p_itemID --exclude assy if it is in the table
and msi.bom_item_type not in (wip_constants.model_type,
wip_constants.option_class_type) /* Fix for 4575119 */
and msi.organization_id = v_orgID
and msi.organization_id = mp.organization_id
and bic.supply_locator_id = milk.inventory_location_id(+)
group by be.operation_seq_num,
msi.concatenated_segments,
be.component_item_id,
msi.primary_uom_code,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
decode(msi.shrinkage_rate, 1, 0, null, 0, msi.shrinkage_rate),
decode(be.organization_id, v_orgID, bic.supply_subinventory, null),
decode(be.organization_id, v_orgID, bic.supply_locator_id, null),
bic.component_sequence_id,
nvl(bic.wip_supply_type, msi.wip_supply_type),
msi.mtl_transactions_enabled_flag,
msi.revision_qty_control_code,
msi.serial_number_control_code,
msi.lot_control_code,
msi.end_assembly_pegging_flag,
be.component_yield_factor,
bic.basis_type, /* LBM Project */
msi.description,
msi.location_control_code,
decode(mp.project_reference_enabled,
null,milk.concatenated_segments,
2,milk.concatenated_segments,
1, inv_project.get_pjm_locsegs(milk.concatenated_segments))
order by be.component_item_id;--be.operation_seq_num, msi.concatenated_segments;
select a.organization_id, a.bill_sequence_id
into l_cmnOrgID, l_cmnBillID
from bom_bill_of_materials a
where a.bill_sequence_id = (select b.common_bill_sequence_id
from bom_bill_of_materials b
where b.assembly_item_id = p_itemID
and b.organization_id = p_orgID
and nvl(b.alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@')
and (b.assembly_type = wip_constants.manufacturing_bill or --FP Bug 6502612
to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS')) = wip_constants.yes )); --FP Bug 6502612
select a.organization_id, a.bill_sequence_id
into l_cmnOrgID, l_cmnBillID
from bom_bill_of_materials a, bom_bill_of_materials b
where a.bill_sequence_id = b.common_bill_sequence_id
and b.assembly_item_id = p_itemID
and b.organization_id = p_orgID
and nvl(b.alternate_bom_designator, '@@@@@') = nvl(p_altBomDesig, '@@@@@');
select a.organization_id, a.bill_sequence_id
into l_cmnOrgID, l_cmnBillID
from bom_bill_of_materials a, bom_bill_of_materials b
where a.bill_sequence_id = b.common_bill_sequence_id
and b.assembly_item_id = p_itemID
and b.organization_id = p_orgID
and ( nvl(b.alternate_bom_designator, '@@@@@') = nvl(p_altBomDesig , '@@@@@')
or
( b.alternate_bom_designator is null
and
not exists (select 'x'
from bom_bill_of_materials c
where c.assembly_item_id = p_itemID
and c.organization_id = p_orgID
and c.alternate_bom_designator = p_altBomDesig)
)
);
select bp.inherit_phantom_op_seq,
bom_explosion_temp_s.nextval,
msi.bom_item_type,
bp.maximum_bom_level
into l_inheritPhOpSeq,
l_grpID,
l_bomItemType,
l_maxBomLevel
from bom_parameters bp, mtl_system_items_b msi
where bp.organization_id = p_orgID
and bp.organization_id = msi.organization_id
and msi.inventory_item_id = p_itemID;
select assembly_type into l_bom_or_eng_flag
from bom_structures_b
where assembly_item_id = p_itemID
and organization_id = p_orgID
and nvl(alternate_bom_designator, '@@@@@') = NVL(p_altBomDesig, '@@@@@'); /* Bug 5139022 Added NVL functions */
select count(1) into l_errCode from bom_explosion_temp
where group_id = l_grpID and loop_flag=1;
delete bom_explosion_temp
where group_id = l_grpID
and plan_level > 2;
select operation_seq_num
into l_opSeqNum
from bom_explosion_temp
where rowid = l_compRec.beRowID;
update bom_explosion_temp
set operation_seq_num = l_opSeqNum
where group_id = l_grpID
and operation_seq_num = 1
and component_code like l_compRec.compCode || '-%';
wip_logger.log('--updated qty by ' || l_compRec.priQty,l_returnStatus);
update bom_explosion_temp
set component_quantity = (component_quantity * decode (basis_type , wip_constants.lot_based_mtl,1,l_compRec.priQty) ) / (1 - l_compRec.shrinkageRate),
/* For phantoms, recalcuate component_yield_factor by multiplying it with child yield only for components
having basis_type as item. Bug fix 5524603. */
component_yield_factor = decode(basis_type,wip_constants.lot_based_mtl,component_yield_factor,component_yield_factor * l_compRec.compYield),
operation_seq_num = nvl(l_opSeqNum, operation_seq_num)
where group_id = l_grpID
and component_code like l_compRec.compCode || '-%';
delete bom_explosion_temp
where group_id = l_grpID
and component_code like l_compRec.compCode || '-%';
delete bom_explosion_temp
where group_id = l_grpID;
SELECT repetitive_schedule_id scheID,
primary_quantity primaryQty
FROM wip_mtl_allocations_temp
WHERE completion_transaction_id = p_cplTxnID;
SELECT wmta.primary_quantity txn_qty,
wmta.repetitive_schedule_id rep_id
FROM wip_move_txn_interface wmti,
wip_move_txn_allocations wmta
WHERE wmti.organization_id = wmta.organization_id
AND wmti.transaction_id = wmta.transaction_id
AND wmti.transaction_id = p_txn_id;
SELECT MIN(wo.operation_seq_num),
MAX(wo.operation_seq_num)
INTO l_first_op,
l_last_op
FROM wip_operations wo,
wip_repetitive_schedules wrs
WHERE wrs.organization_id = wo.organization_id
AND wrs.wip_entity_id = wo.wip_entity_id
AND wrs.repetitive_schedule_id = wo.repetitive_schedule_id
AND wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
AND wrs.wip_entity_id = p_wipEntityID
AND wrs.organization_id = p_orgID;
SELECT MIN(wo.operation_seq_num),
MAX(wo.operation_seq_num)
INTO l_first_op,
l_last_op
FROM wip_operations wo,
wip_discrete_jobs wdj
WHERE wdj.organization_id = wo.organization_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND wdj.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
AND wdj.wip_entity_id = p_wipEntityID
AND wdj.organization_id = p_orgID;
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa.
* This is only for operation pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => l_rsa(i).scheID,
p_repLineID => p_lineID,
p_cplTxnID => null,
p_movTxnID => p_movTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_rsa(i).scheQty * l_forward,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_lockFlag => p_lockFlag,
p_batchSeq => l_batch_seq,
p_mergeMode => fnd_api.g_true,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa
* This is only for assembly pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => l_rsa(i).scheID,
p_repLineID => p_lineID,
p_cplTxnID => null,
p_movTxnID => p_movTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_rsa(i).scheQty * l_forward,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_lockFlag => p_lockFlag,
p_batchSeq => l_batch_seq,
p_mergeMode => fnd_api.g_true,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa
* This is only for assembly pull components(Completion/Return).
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => l_rsa(i).scheID,
p_repLineID => p_lineID,
p_cplTxnID => p_cplTxnID,
-- Fixed bug 5014211. Stamp move_transaction_id for assembly
-- pull components so that we will have a link if component
-- records fail inventory validation.
p_movTxnID => p_movTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_rsa(i).scheQty * l_forward,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => -1,
p_lastOP => l_last_op,
p_firstMoveOp => null,
p_lastMoveOp => null,
p_lockFlag => p_lockFlag,
p_batchSeq => l_batch_seq,
p_mergeMode => fnd_api.g_true,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa.
* This is only for operation pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => l_rsa(i).scheID,
p_repLineID => p_lineID,
p_cplTxnID => null,
p_movTxnID => p_childMovTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_rsa(i).scheQty * l_forward,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_batchSeq => l_batch_seq,
p_lockFlag => p_lockFlag,
p_mergeMode => fnd_api.g_true,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa
* This is only for assembly pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => l_rsa(i).scheID,
p_repLineID => p_lineID,
p_cplTxnID => null,
p_movTxnID => p_childMovTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_rsa(i).scheQty * l_forward,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_lockFlag => p_lockFlag,
p_batchSeq => l_batch_seq,
p_mergeMode => fnd_api.g_true,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa
* This is only for assembly pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => null,
p_repLineID => null,
p_cplTxnID => null,
p_movTxnID => p_movTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_bf_qty,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_batchSeq => l_batch_seq,
p_lockFlag => p_lockFlag,
p_mergeMode => fnd_api.g_false,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa.
* This is only for operation pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => null,
p_repLineID => null,
p_cplTxnID => null,
p_movTxnID => p_childMovTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_bf_qty,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_batchSeq => l_batch_seq,
p_lockFlag => p_lockFlag,
p_mergeMode => fnd_api.g_false,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
* Call backflush processor to insert record into MMTT
* for each schedule found in l_rsa
* This is only for assembly pull components.
**/
wip_bflProc_priv.processRequirements
(p_wipEntityID => p_wipEntityID,
p_wipEntityType => p_entityType,
p_repSchedID => null,
p_repLineID => null,
p_cplTxnID => null,
p_movTxnID => p_childMovTxnID,
p_batchID => p_batchID,
p_orgID => p_orgID,
p_assyQty => l_bf_qty,
p_txnDate => p_txnDate,
p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
p_txnHdrID => p_txnHdrID,
p_firstOp => l_first_bf_op,
p_lastOP => l_last_bf_op,
p_firstMoveOp => l_fm_op,
p_lastMoveOp => l_to_op,
p_lockFlag => p_lockFlag,
p_batchSeq => l_batch_seq,
p_mergeMode => fnd_api.g_false,
p_reasonID => p_reasonID,
p_reference => p_reference,
p_initMsgList => fnd_api.g_false,
p_endDebug => fnd_api.g_false,
p_mtlTxnMode => p_mtlTxnMode,
x_compTbl => l_compTbl,
x_returnStatus => l_returnStatus);
SELECT COUNT(*)
INTO l_bf_count
FROM mtl_transactions_interface
WHERE transaction_header_id = p_txnHdrID
AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION);
SELECT COUNT(*)
INTO l_lot_ser_count
FROM mtl_transactions_interface mti,
mtl_system_items msi
WHERE mti.organization_id = msi.organization_id
AND mti.inventory_item_id = msi.inventory_item_id
AND (msi.lot_control_code = WIP_CONSTANTS.LOT
OR
msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
WIP_CONSTANTS.DYN_RCV_SN))
AND transaction_header_id = p_txnHdrID
AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION);
SELECT backflush_lot_entry_type
INTO l_lot_entry_type
FROM wip_parameters
WHERE organization_id = p_orgID;
UPDATE mtl_material_transactions_temp mmtt
SET (mmtt.item_segments,
mmtt.item_description,
mmtt.item_trx_enabled_flag,
mmtt.item_location_control_code,
mmtt.item_restrict_subinv_code,
mmtt.item_restrict_locators_code,
mmtt.item_revision_qty_control_code,
mmtt.item_primary_uom_code,
mmtt.item_uom_class,
mmtt.item_shelf_life_code,
mmtt.item_shelf_life_days,
mmtt.item_lot_control_code,
mmtt.item_serial_control_code,
mmtt.item_inventory_asset_flag,
mmtt.number_of_lots_entered)
=
(SELECT msik.concatenated_segments,
msik.description,
msik.mtl_transactions_enabled_flag,
msik.location_control_code,
msik.restrict_subinventories_code,
msik.restrict_locators_code,
msik.revision_qty_control_code,
msik.primary_uom_code,
muom.uom_class,
msik.shelf_life_code,
msik.shelf_life_days,
msik.lot_control_code,
msik.serial_number_control_code,
msik.inventory_asset_flag,
mmtt.transaction_quantity
FROM mtl_system_items_kfv msik,
mtl_units_of_measure muom
WHERE mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id
AND msik.primary_uom_code = muom.uom_code)
WHERE mmtt.transaction_header_id = p_txnHdrID
-- Added the check below because OSFM will call this API twice.
AND mmtt.number_of_lots_entered IS NULL;
* from statement to update other columns.
*/
-- set department code if mmtt.department_id is not null
UPDATE mtl_material_transactions_temp mmtt
SET (mmtt.department_code)
=
(SELECT bd.department_code
FROM bom_departments bd
WHERE bd.organization_id = mmtt.organization_id
AND bd.department_id = mmtt.department_id
)
WHERE mmtt.transaction_header_id = p_txnHdrID
AND mmtt.department_id IS NOT NULL;
/*Update MMTT Lot number if only one lot is derived */
IF(l_lot_entry_type IN (WIP_CONSTANTS.RECDATE_FULL,
WIP_CONSTANTS.EXPDATE_FULL,
WIP_CONSTANTS.TXNHISTORY_FULL)) THEN
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.lot_number =
( SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND 1 = ( SELECT count(*)
FROM mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
)
WHERE mmtt.transaction_header_id = p_txnHdrID
AND mmtt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION);
/* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement from below */
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE EXISTS
(SELECT 'x'
FROM mtl_material_transactions_temp mmtt,
wip_entities we
WHERE mmtt.transaction_header_id = p_txnHdrID
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.transaction_source_id = we.wip_entity_id
AND we.entity_type = wip_constants.lotbased
AND 1 = (SELECT count(*)
FROM mtl_transaction_lots_temp mtlt2
WHERE mtlt2.transaction_temp_id =
mtlt.transaction_temp_id
));
This cause select count(*) to throw single-row subquery returns more
than one row exception. */
/*
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id =
( SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt,
wip_entities we
WHERE mmtt.transaction_header_id = p_txnHdrID
AND 1 = ( SELECT count(*)
FROM mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id =
mtlt.transaction_temp_id
)
AND mmtt.transaction_source_id = we.wip_entity_id
AND we.entity_type = wip_constants.lotbased
) ;
/* Bug 6342487 - FP of Bug 6111292 - Moved this delete statement above inside the IF condition. The rows should be deleted from MTLT
only for the case of Lot Verification=All after the lot has been stamped on MMTT. For Lot Verification=Exception Only
there is no need to delete since the component rows will not be visible in the UI.
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE EXISTS
(SELECT 'x'
FROM mtl_material_transactions_temp mmtt,
wip_entities we
WHERE mmtt.transaction_header_id = p_txnHdrID
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.transaction_source_id = we.wip_entity_id
AND we.entity_type = wip_constants.lotbased
AND 1 = (SELECT count(*)
FROM mtl_transaction_lots_temp mtlt2
WHERE mtlt2.transaction_temp_id =
mtlt.transaction_temp_id
));
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_quantity =
(SELECT NVL(SUM(mtlt.transaction_quantity),0)
FROM mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
WHERE mmtt.transaction_header_id = p_txnHdrID
AND (mmtt.item_serial_control_code IN(WIP_CONSTANTS.FULL_SN,
WIP_CONSTANTS.DYN_RCV_SN)
OR
mmtt.item_lot_control_code = WIP_CONSTANTS.LOT);
p_msg => 'Succesfully inserted components into MMTT',
x_returnStatus => l_returnStatus);
SELECT MIN(wo.operation_seq_num),
MAX(wo.operation_seq_num)
INTO l_first_op,
l_last_op
FROM wip_operations wo,
wip_discrete_jobs wdj
WHERE wdj.organization_id = wo.organization_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND wdj.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
AND wdj.wip_entity_id = p_wipEntityID
AND wdj.organization_id = p_orgID;
p_msg => 'Succesfully inserted components into PL/SQL object',
x_returnStatus => l_returnStatus);