[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select p_txnTmpID,
mmtt.material_allocation_temp_id,
mmtt.transaction_source_id,
mmtt.wip_entity_type,
mmtt.organization_id, --5
mmtt.repetitive_line_id,
mmtt.inventory_item_id,
mmtt.operation_seq_num,
-1 * mmtt.primary_quantity, --qty is relative to inv, make it relative to wip
-1 * mmtt.transaction_quantity, --10
mmtt.negative_req_flag,
mmtt.wip_supply_type,
msi.wip_supply_subinventory, /* Bug 5918149 : Pick subinventory from msi instead of mmtt. FP for bug 5895215 */
msi.wip_supply_locator_id, /* Bug 5918149 : Pick locator from msi instead of mmtt. FP for bug 5895215 */
mmtt.transaction_date, --15
mmtt.transaction_header_id,
mmtt.move_transaction_id,
mmtt.completion_transaction_id,
mmtt.qa_collection_id,
mmtt.department_id,
mmtt.transaction_action_id,
msi.serial_number_control_code,
msi.lot_control_code,
msi.eam_item_type,
mmtt.rebuild_item_id,
mmtt.rebuild_job_name,
mmtt.rebuild_activity_id,
mmtt.rebuild_serial_number
into l_issueRec.txnTmpID,
l_issueRec.mtlTxnID,
l_issueRec.wipEntityID,
l_issueRec.wipEntityType,
l_issueRec.orgID,
l_issueRec.repLineID,--5
l_issueRec.itemID,
l_issueRec.opSeqNum,
l_issueRec.primaryQty,
l_issueRec.txnQty,
l_issueRec.negReqFlag, --10
l_issueRec.wipSupplyType,
l_issueRec.supplySub,
l_issueRec.supplyLocID,
l_issueRec.txnDate,
l_issueRec.txnHdrID, --15
l_issueRec.movTxnID,
l_issueRec.cplTxnID,
l_issueRec.qaCollectionID,
l_issueRec.deptID,
l_issueRec.txnActionID,
l_issueRec.serialControlCode,
l_issueRec.lotControlCode,
l_issueRec.eamItemType,
l_issueRec.rebuildItemID,
l_issueRec.rebuildJobName,
l_issueRec.rebuildActivityID,
l_issueRec.rebuildSerialNumber
from mtl_material_transactions_temp mmtt, mtl_system_items_b msi
where transaction_temp_id = p_txnTmpID
and mmtt.inventory_item_id = msi.inventory_item_id
and mmtt.organization_id = msi.organization_id
and nvl(flow_schedule, 'N') <> 'Y';
select status_type
into l_jobStatus
from wip_discrete_jobs
where wip_entity_id = l_issueRec.wipEntityID;
wip_logger.log('selected job status:' || l_jobStatus, l_returnStatus);
select meaning
into l_JobStatus
from mfg_lookups
where lookup_type = 'WIP_JOB_STATUS'
and lookup_code = l_jobStatus;
writeError(p_txnTmpID); --update the MMTT line to error for wip failures
l_doUpdate boolean := true;
select rowid
into l_rowid
from wip_requirement_operations
where inventory_item_id = p_issueRec.itemID
and wip_entity_id = p_issueRec.wipEntityID
and operation_seq_num = p_issueRec.opSeqNum
for update of quantity_issued, quantity_allocated nowait;
l_doUpdate := false;
if(l_doUpdate) then
if (l_logLevel <= wip_constants.full_logging) then
wip_logger.log('do update is true', l_returnStatus);
update wip_requirement_operations --try to update an existing requirement
set quantity_issued = quantity_issued + nvl(p_issueQty, p_issueRec.primaryQty),
quantity_allocated = greatest(0, least(quantity_allocated, quantity_allocated - nvl(p_issueQty, p_issueRec.primaryQty))),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id
where rowid = l_rowid returning quantity_issued, required_quantity into l_newIssuedQty, l_newRequiredQty;
select rowid
into l_rowid
from wip_requirement_operations
where inventory_item_id = p_issueRec.itemID
and wip_entity_id = p_issueRec.wipEntityID
and repetitive_schedule_id = p_repSchedID
and operation_seq_num = p_issueRec.opSeqNum
for update of quantity_issued, quantity_allocated nowait;
when no_data_found then --no existing requirement, will have to insert one
l_doUpdate := false;
if(l_doUpdate) then
--below, quantity_allocated must be >= 0. At the same time, it must never increase via a return, negative issue.
--only the component picking process should increase the quantity_allocated column
update wip_requirement_operations --try to update an existing requirement
set quantity_issued = quantity_issued + nvl(p_issueQty, p_issueRec.primaryQty),
quantity_allocated = greatest(0, least(quantity_allocated, quantity_allocated - nvl(p_issueQty, p_issueRec.primaryQty))),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id
where rowid = l_rowid returning quantity_issued, required_quantity into l_newIssuedQty, l_newRequiredQty;
if(not l_doUpdate) then --create the requirement since we could not find an existing one
if (l_logLevel <= wip_constants.full_logging) then
wip_logger.log('about to do insert', l_returnStatus);
select department_id
into l_dept_id
from wip_operations wo
where wip_entity_id = p_issueRec.wipEntityID
and operation_seq_num = p_issueRec.opSeqNum;
insert into wip_requirement_operations
(inventory_item_id,
organization_id,
wip_entity_id,
operation_seq_num,
repetitive_schedule_id, --5
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by, --10
department_id,
date_required,
required_quantity,
quantity_issued,
quantity_per_assembly, --15
wip_supply_type,
mrp_net_flag,
request_id,
program_application_id,
program_id, --20
program_update_date,
supply_subinventory,
supply_locator_id,
mps_date_required,
mps_required_quantity, --25
segment1,
segment2,
segment3,
segment4,
segment5, --30
segment6,
segment7,
segment8,
segment9,
segment10, --35
segment11,
segment12,
segment13,
segment14,
segment15, --40
segment16,
segment17,
segment18,
segment19,
segment20,
component_yield_factor -- Added for bug 4703470
)
select p_issueRec.itemID,
p_issueRec.orgID,
p_issueRec.wipEntityID,
p_issueRec.opSeqNum,
p_repSchedID, --5
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id, --10
nvl(p_issueRec.deptID, l_dept_id), /* Bugfix 5401362 l_dept_id if null */
p_issueRec.txnDate,
0, --required_quantity
nvl(p_issueQty, p_issueRec.primaryQty),
0, --quantity_per_assembly 15
nvl(p_issueRec.wipSupplyType, wip_constants.push),
wip_constants.yes,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id, --20
sysdate,
p_issueRec.supplySub,
p_issueRec.supplyLocID,
p_issueRec.txnDate,
0, --mps_required_quantity??? 25
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5, --30
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10, --35
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15, --40
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
1 -- Added for Bug 4703470
FROM MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = p_issueRec.orgID
AND INVENTORY_ITEM_ID = p_issueRec.itemID;
wip_logger.log('inserted ' || SQL %ROWCOUNT, l_returnStatus);
select wro.repetitive_schedule_id,
wro.required_quantity - wro.quantity_issued
bulk collect into x_schedTbl, x_qtyTbl
from wip_requirement_operations wro,
wip_repetitive_schedules wrs
where wro.wip_entity_id = p_issueRec.wipEntityID
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and sign(wro.quantity_per_assembly) = sign(p_issueRec.negReqFlag)
and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
and wrs.line_id = p_issueRec.repLineID
and sign(wro.required_quantity) = p_issueRec.negReqFlag
and wrs.status_type in (3,4) /* bug3338344*/
and wrs.date_released < p_issueRec.txnDate
order by wrs.first_unit_start_date;
select wro.repetitive_schedule_id,--same as issue cursor above except for order by
wro.quantity_issued
bulk collect into x_schedTbl, x_qtyTbl
from wip_requirement_operations wro,
wip_repetitive_schedules wrs
where wro.wip_entity_id = p_issueRec.wipEntityID
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and sign(wro.quantity_per_assembly) = sign(p_issueRec.negReqFlag)
and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
and wrs.line_id = p_issueRec.repLineID
and sign(wro.required_quantity) = p_issueRec.negReqFlag
and wrs.status_type in (3,4) /* bug3338344*/
and wrs.date_released < p_issueRec.txnDate
order by wrs.first_unit_start_date desc;
select nvl(include_component_yield,1)
into l_include_yield
from wip_parameters
where organization_id = p_issueRec.orgID ;
select wro.repetitive_schedule_id,
-- bug 5491202 changed the next line to include component yield
-- wro.quantity_per_assembly * wmta.primary_quantity
round( wro.quantity_per_assembly * wmta.primary_quantity
/ decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)),
wip_constants.inv_max_precision)
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
wip_move_txn_allocations wmta
where wmta.transaction_id = p_issueRec.movTxnID
and wro.repetitive_schedule_id = wmta.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
/* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
order by wrs.first_unit_start_date;
select wro.repetitive_schedule_id,
-- bug 5491202 changed the next line to include component yield
-- wro.quantity_per_assembly * wmta.primary_quantity schedQty
round( wro.quantity_per_assembly * wmta.primary_quantity
/ decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)),
wip_constants.inv_max_precision)
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
wip_move_txn_allocations wmta
where wmta.transaction_id = p_issueRec.movTxnID
and wro.repetitive_schedule_id = wmta.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
/* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
order by wrs.first_unit_start_date desc;
select count(*)
into l_mmttCount
from mtl_material_transactions_temp
where completion_transaction_id = p_issueRec.cplTxnID
and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
select wro.repetitive_schedule_id,
wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
mtl_material_txn_allocations mmta,
mtl_material_transactions_temp mmtt --the MMTT row(s) are the assy rows
where mmta.transaction_id = mmtt.material_allocation_temp_id
and mmtt.completion_transaction_id = p_issueRec.cplTxnID
and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
/* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
order by wrs.first_unit_start_date;
select wro.repetitive_schedule_id,
wro.quantity_per_assembly * sum(wmat.primary_quantity) schedQty
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
wip_mtl_allocations_temp wmat,
mtl_material_transactions_temp mmtt --the MMTT row(s) are the assy rows
where wmat.transaction_temp_id = mmtt.transaction_temp_id
and mmtt.completion_transaction_id = p_issueRec.cplTxnID
and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.repetitive_schedule_id = wmat.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
order by wrs.first_unit_start_date;
select wro.repetitive_schedule_id,
wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
mtl_material_txn_allocations mmta,
mtl_material_transactions_temp mmtt
where mmta.transaction_id = mmtt.material_allocation_temp_id
and mmtt.completion_transaction_id = p_issueRec.cplTxnID
and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
/* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
order by wrs.first_unit_start_date desc;
select wro.repetitive_schedule_id,
wro.quantity_per_assembly * sum(wmat.primary_quantity) schedQty
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
wip_mtl_allocations_temp wmat,
mtl_material_transactions_temp mmtt --the MMTT row(s) are the assy rows
where wmat.transaction_temp_id = mmtt.transaction_temp_id
and mmtt.completion_transaction_id = p_issueRec.cplTxnID
and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.repetitive_schedule_id = wmat.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
order by wrs.first_unit_start_date desc;
select wro.repetitive_schedule_id,
wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
mtl_material_txn_allocations mmta,
mtl_material_transactions mmt
where mmta.transaction_id = mmt.transaction_id
and mmt.completion_transaction_id = p_issueRec.cplTxnID
and mmt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
/* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
order by wrs.first_unit_start_date;
select wro.repetitive_schedule_id,
wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
bulk collect into x_schedTbl, x_qtyTbl
from wip_repetitive_schedules wrs,
wip_requirement_operations wro,
mtl_material_txn_allocations mmta,
mtl_material_transactions mmt
where mmta.transaction_id = mmt.transaction_id
and mmt.completion_transaction_id = p_issueRec.cplTxnID
and mmt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and wro.wip_entity_id = p_issueRec.wipEntityID
and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
and wro.inventory_item_id = p_issueRec.itemID
and wro.operation_seq_num = p_issueRec.opSeqNum
and wro.wip_supply_type = p_issueRec.wipSupplyType
and wro.quantity_per_assembly <> 0
and sign(wro.required_quantity) = p_issueRec.negReqFlag
/* and wrs.status_type in (3,4)*/ /* bug3338344 removed for bug5137228 (fp5015515) */
group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
order by wrs.first_unit_start_date desc;
update mtl_material_transactions_temp
set material_allocation_temp_id = mtl_material_transactions_s.nextval
where transaction_temp_id = p_issueRec.txnTmpID returning material_allocation_temp_id into p_issueRec.mtlTxnID;
wip_logger.log('inserted sched:' || l_schedTbl(i) || '; qty:' || l_mmta_priQtyTbl(l_mmtaRowCount), l_returnStatus);
select repetitive_schedule_id --the requirement doesn't exist. Find the earliest open schedule
into l_excessQtySchedID
from wip_repetitive_schedules wrs
where wrs.wip_entity_id = p_issueRec.wipEntityID
and wrs.line_id = p_issueRec.repLineID
and wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
and wrs.date_released < p_issueRec.txnDate
and first_unit_start_date = (select min(first_unit_start_date)
from wip_repetitive_schedules
where wip_entity_id = p_issueRec.wipEntityID
and line_id = p_issueRec.repLineID
and status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
and date_released < p_issueRec.txnDate)
order by wrs.first_unit_start_date;
insert into mtl_material_txn_allocations
(transaction_id,
repetitive_schedule_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
primary_quantity,
transaction_quantity,
transaction_date)
values
(p_issueRec.mtlTxnID,
l_mmta_schedIdTbl(i),
p_issueRec.orgID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
l_mmta_priQtyTbl(i),
l_mmta_txnQtyTbl(i),
p_issueRec.txnDate);
wip_logger.log(SQL%ROWCOUNT || ' row inserted into MMTA', l_returnStatus);
wip_utilities.get_message_stack(p_delete_stack => fnd_api.g_false,
p_msg => l_errExpl);
update mtl_material_transactions_temp
set error_code = substr(l_errCode, 1, 240),
error_explanation = substr(l_errExpl, 1, 240),
process_flag = 'E'
where transaction_temp_id = p_txnTmpID;