[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select row_id, component_quantity, component_item_id,
component_code, operation_seq_num
from wip_explosions_v
where top_bill_sequence_id = p_bill_sequence_id
and organization_id = p_organization_id
and wip_supply_type = wip_constants.phantom
order by component_code;
IS select '1'
from bom_explosions
where top_bill_sequence_id = p_bill_sequence_id
and organization_id = p_organization_id
for update nowait;
delete bom_explosions
where top_bill_sequence_id = p_bill_sequence_id
and organization_id = p_organization_id;
update bom_explosions
set component_quantity = component_quantity * l_compRec.component_quantity,
operation_seq_num = l_compRec.operation_seq_num
where component_code <> l_compRec.component_code
and component_code like l_compRec.component_code || '%'
and top_bill_sequence_id = p_bill_sequence_id
and organization_id = p_organization_id;
close c_lock; -- done processing...remove the lock on the rows, however note that the delete statement
select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.repetitive_schedule_id = p_repetitive_schedule_id
and mtrl.txn_source_id = wro.wip_entity_id
and mtrl.reference_id = wro.repetitive_schedule_id
and mtrl.txn_source_line_id = wro.operation_seq_num
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK
and rownum = 1)
for update of wro.quantity_allocated, quantity_backordered nowait;
select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
from mtl_txn_request_lines mtrl, wip_requirement_operations wro, wip_entities we /* bug 4211266 */
where we.wip_entity_id = p_wip_entity_id
and wro.organization_id = we.organization_id
and wro.wip_entity_id = we.wip_entity_id
and mtrl.organization_id = wro.organization_id
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.txn_source_id = we.wip_entity_id
and mtrl.txn_source_line_id = wro.operation_seq_num
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK
and rownum = 1)
for update of wro.quantity_allocated, quantity_backordered nowait;
select line_id
from mtl_txn_request_lines mtrl, wip_entities we
where mtrl.TRANSACTION_TYPE_ID = INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR -- 51
and mtrl.TRANSACTION_SOURCE_TYPE_ID = INV_Globals.G_SOURCETYPE_INVENTORY --13
and mtrl.txn_source_id = we.wip_entity_id
and mtrl.organization_id = we.organization_id
and we.wip_entity_id = p_wip_entity_id
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK);
SAVEPOINT updateBackorder;
update wip_requirement_operations
set quantity_backordered = 0
where wip_entity_id = p_wip_entity_id;
ROLLBACK TO SAVEPOINT updateBackorder;
update wip_requirement_operations
set quantity_backordered = 0
where wip_entity_id = p_wip_entity_id
and repetitive_schedule_id = p_repetitive_schedule_id;
select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where mtrl.txn_source_id = p_wip_entity_id
and mtrl.reference_id = p_repetitive_schedule_id
and wro.wip_entity_id = p_wip_entity_id
and wro.repetitive_schedule_id = p_repetitive_schedule_id
and wro.operation_seq_num = p_operation_seq_num
and mtrl.txn_source_line_id = wro.operation_seq_num
and wro.inventory_item_id = p_inventory_item_id
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
for update of wro.quantity_allocated, quantity_backordered nowait;
select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where mtrl.txn_source_id = p_wip_entity_id
and wro.wip_entity_id = p_wip_entity_id
and wro.operation_seq_num = p_operation_seq_num
and mtrl.txn_source_line_id = wro.operation_seq_num
and wro.inventory_item_id = p_inventory_item_id
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
for update of wro.quantity_allocated, quantity_backordered nowait;
update wip_requirement_operations
set quantity_backordered = 0
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id;
update wip_requirement_operations
set quantity_backordered = 0
where wip_entity_id = p_wip_entity_id
and repetitive_schedule_id = p_repetitive_schedule_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id;
update wip_requirement_operations
set quantity_allocated = nvl(quantity_allocated,0) - p_openQty,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where rowid = p_rowId;
p_delete_reservations => 'N');
select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where mtrl.txn_source_id = v_wip_entity_id
and mtrl.reference_id = v_repetitive_schedule_id
and wro.wip_entity_id = v_wip_entity_id
and wro.repetitive_schedule_id = v_repetitive_schedule_id
and wro.operation_seq_num = v_operation_seq_num
and mtrl.txn_source_line_id = wro.operation_seq_num
and wro.inventory_item_id = v_inventory_item_id
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
order by mtrl.creation_date desc /* Traverse thru move orders in LIFO fashion */
for update of wro.quantity_allocated, quantity_backordered nowait;
select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where mtrl.txn_source_id = v_wip_entity_id
and wro.wip_entity_id = v_wip_entity_id
and wro.operation_seq_num = v_operation_seq_num
and mtrl.txn_source_line_id = wro.operation_seq_num
and wro.inventory_item_id = v_inventory_item_id
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
order by mtrl.creation_date desc /* Traverse thru move orders in LIFO fashion */
for update of wro.quantity_allocated, quantity_backordered nowait;
p_delete_reservations => 'N');
/*finally update backordered quantity and allocated quantity for this component*/
if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
update wip_requirement_operations
set quantity_backordered = greatest((quantity_backordered - p_comp_tbl(i).requested_quantity), 0),
quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
where wip_entity_id = p_comp_tbl(i).wip_entity_id
and operation_seq_num = p_comp_tbl(i).operation_seq_num
and inventory_item_id = p_comp_tbl(i).inventory_item_id;
update wip_requirement_operations
set quantity_backordered = greatest(quantity_backordered - p_comp_tbl(i).requested_quantity, 0),
quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
where wip_entity_id = p_comp_tbl(i).wip_entity_id
and repetitive_schedule_id = p_comp_tbl(i).repetitive_schedule_id
and operation_seq_num = p_comp_tbl(i).operation_seq_num
and inventory_item_id = p_comp_tbl(i).inventory_item_id;
procedure update_allocation_op_seqs(p_wip_entity_id IN NUMBER,
p_repetitive_schedule_id IN NUMBER := null,
p_operation_seq_num IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR c_repLines IS
select line_id
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where mtrl.txn_source_id = p_wip_entity_id
and mtrl.txn_source_line_id = p_repetitive_schedule_id
and wro.wip_entity_id = p_wip_entity_id
and wro.repetitive_schedule_id = p_repetitive_schedule_id
and wro.operation_seq_num = 1
and mtrl.txn_source_line_id = wro.operation_seq_num
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
and not exists (select 1 from mtl_material_transactions_temp mmtt --Bug#16041371:Check if there are any pending transactions in MMTT
where transaction_source_id=p_wip_entity_id and mmtt.organization_id=wro.organization_id and mmtt.repetitive_line_id=p_repetitive_schedule_id and
mmtt.operation_seq_num in (-1,1) and mmtt.inventory_item_id=wro.inventory_item_id AND mmtt.transaction_source_type_id=INV_Globals.G_SOURCETYPE_WIP)
for update of wro.quantity_allocated, wro.quantity_backordered nowait; --make sure no orders are transacted in the meantime
select line_id
from mtl_txn_request_lines mtrl, wip_requirement_operations wro
where mtrl.txn_source_id = p_wip_entity_id
and wro.wip_entity_id = p_wip_entity_id
and wro.operation_seq_num = 1
and mtrl.txn_source_line_id = wro.operation_seq_num
and mtrl.inventory_item_id = wro.inventory_item_id
and mtrl.organization_id = wro.organization_id
and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
and exists(select 1
from mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
and not exists (select 1 from mtl_material_transactions_temp mmtt --Bug#16041371:Check if there are any pending transactions in MMTT
where mmtt.transaction_source_id=p_wip_entity_id and mmtt.transaction_source_type_id=INV_Globals.G_SOURCETYPE_WIP and mmtt.organization_id=wro.organization_id
and mmtt.operation_seq_num in (-1,1) and mmtt.inventory_item_id=wro.inventory_item_id)
for update of wro.quantity_allocated, wro.quantity_backordered nowait;--make sure no orders are transacted in the meantime
SAVEPOINT WIP_UPDATE_ALLOCS_START;
inv_wip_picking_pvt.update_mol_for_wip(p_move_order_line_id => l_lineID,
p_op_seq_num => p_operation_seq_num,
x_msg_count => l_msgCount,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
ROLLBACK TO WIP_UPDATE_ALLOCS_START;
ROLLBACK TO WIP_UPDATE_ALLOCS_START;
fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.update_allocation_op_seqs: ' || SQLERRM);
end update_allocation_op_seqs;
select a.organization_id,
a.bill_sequence_id,
wfs.primary_item_id,
wfs.SCHEDULED_COMPLETION_DATE,
wfs.planned_quantity
from bom_bill_of_materials a, bom_bill_of_materials b, wip_flow_schedules wfs
where a.bill_sequence_id = b.common_bill_sequence_id
and b.assembly_item_id = wfs.primary_item_id
and wfs.wip_entity_id = v_wip_entity_id
and b.organization_id = wfs.organization_id
and ( nvl(b.alternate_bom_designator, 'none') = nvl(wfs.alternate_bom_designator, 'none')
or ( b.alternate_bom_designator IS NULL
and not exists(select 'x'
from bom_bill_of_materials c
where c.assembly_item_id = wfs.primary_item_id
and c.organization_id = wfs.organization_id
and c.alternate_bom_designator = wfs.alternate_bom_designator
)
)
)
for update of wfs.allocated_flag nowait;
select wro.operation_seq_num,
wro.inventory_item_id,
/*Component Yield Enhancement(Bug 4369064)-> Derive quantity to allocate based on yield parameter*/
(wro.required_quantity * decode(l_include_yield,2,nvl(wro.component_yield_factor,1),1)
- wro.quantity_issued
-- - nvl(wro.quantity_allocated, 0)
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
- nvl(wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY /
decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)), 0)) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo,wip_discrete_jobs wdj /*Bug 14050050 */
where wro.wip_entity_id = v_wip_entity_id
and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
and (
( 'Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
)
or wro.wip_supply_type = wip_constants.push
)
/*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
0.00001 (least amount inventory can transfer)*/
and (wro.required_quantity - (wro.quantity_issued
+ nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) >= 0.00001)
and wro.inventory_item_id = msi.inventory_item_id
and wro.wip_entity_id = wo.wip_entity_id (+)
and ( p_operation_seq_num_low IS NULL OR wro.operation_seq_num >= p_operation_seq_num_low)
and ( p_operation_seq_num_high IS NULL OR wro.operation_seq_num <= p_operation_seq_num_high)
and wro.operation_seq_num = wo.operation_seq_num (+)
and (wo.count_point_type is null or wo.count_point_type in (1,2))
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
/*Bug 14050050: excluded push type component without subinventory defined for serialized operation to avoid it issuing to the job.*/
and wro.wip_entity_id = wdj.wip_entity_id(+)
and (wro.operation_seq_num < nvl(wdj.serialization_start_op, wro.operation_seq_num+1) or wro.wip_supply_type <> 1 or wro.supply_subinventory is not null)
for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
select wro.operation_seq_num,
wro.inventory_item_id,
/*Component Yield Enhancement(Bug 4369064)-> Derive quantity to allocate based on yield parameter*/
(wro.required_quantity * decode(l_include_yield,2,nvl(wro.component_yield_factor,1),1)
- wro.quantity_issued
-- - nvl(wro.quantity_allocated, 0)
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
- nvl(wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY /
decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)), 0)) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
where wro.wip_entity_id = v_wip_entity_id
and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
/*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
0.00001 (least amount inventory can transfer)*/
and (wro.required_quantity - (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) >= 0.00001)
and wro.inventory_item_id = msi.inventory_item_id
and wro.wip_entity_id = wo.wip_entity_id (+)
and ( p_operation_seq_num_low IS NULL OR wro.operation_seq_num >= p_operation_seq_num_low) /* Enh#2824753: op seq converted to range for EAM work orders */
and ( p_operation_seq_num_high IS NULL OR wro.operation_seq_num <= p_operation_seq_num_high)
and wro.operation_seq_num = wo.operation_seq_num (+)
and (wo.count_point_type is null or wo.count_point_type in (1,2))
and wro.auto_request_material = 'Y'
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and wro.wip_supply_type <> wip_constants.bulk
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
select wro.operation_seq_num,
wro.inventory_item_id,
((decode (nvl(wro.basis_type,wip_constants.item_based_mtl),wip_constants.item_based_mtl,
(wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed),1) *
wro.quantity_per_assembly /
decode(l_include_yield,l_include_comp_yield_factor,nvl(wro.component_yield_factor,1),1))
- wro.quantity_issued
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
where wro.wip_entity_id = v_wip_entity_id
and wro.wip_entity_id = wo.wip_entity_id
and wro.operation_seq_num = wo.operation_seq_num
and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
and (
( 'Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
)
or wro.wip_supply_type = wip_constants.push
)
-- and (nvl(wro.quantity_allocated,0) + wro.quantity_issued) < ((wo.quantity_in_queue + wo.quantity_running +
-- wo.quantity_completed) * wro.quantity_per_assembly)
/*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
0.00001 (least amount inventory can transfer)*/
and (((wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed) * wro.quantity_per_assembly) -
(wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) >= 0.00001)
and wro.inventory_item_id = msi.inventory_item_id
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
select wro.operation_seq_num,
wro.inventory_item_id,
/*Component Yield Enhancement(Bug 4369064)-> Derive quantity to allocate based on yield parameter*/
least( --open qty is the smaller of the remaining quantity and the quantity determined by the #days to allocate for
(wro.required_quantity * decode(l_include_yield,2,nvl(wro.component_yield_factor,1),1)
- wro.quantity_issued
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID,WRO. REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
- nvl(wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY /
decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)),0)),
(wrs.daily_production_rate * wro.quantity_per_assembly * p_days_to_alloc) + nvl(wro.quantity_backordered, 0)) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
from wip_requirement_operations wro,
wip_repetitive_schedules wrs,
mtl_system_items_b msi,
wip_operations wo
where wro.repetitive_schedule_id = v_rep_sched_id
and wrs.repetitive_schedule_id = v_rep_sched_id
and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
and ((p_cutoff_date is null) OR (wro.date_required <= p_cutoff_date))
and (
( 'Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
)
or wro.wip_supply_type = wip_constants.push
)
/*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
0.00001 (least amount inventory can transfer)*/
and (wro.required_quantity - (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,WRO.QUANTITY_ISSUED),0)) >= 0.00001)
and wro.inventory_item_id = msi.inventory_item_id
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
and wro.operation_seq_num = wo.operation_seq_num (+)
and (wo.count_point_type is null or wo.count_point_type in (1,2))
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, quantity_issued nowait;
select default_pull_supply_subinv, default_pull_supply_locator_id
into l_defaultSub, l_defaultLocator
from wip_parameters
where organization_id = p_organization_id;
select nvl(include_component_yield,1)
into l_include_yield
from wip_parameters
where organization_id = p_organization_id;
select a.organization_id,
a.bill_sequence_id,
wfs.primary_item_id,
wfs.alternate_bom_designator,
wfs.alternate_routing_designator,
wfs.SCHEDULED_COMPLETION_DATE,
wfs.planned_quantity
into p_alloc_tbl(i).bill_org_id, p_alloc_tbl(i).bill_seq_id, l_itemID, l_abm,
p_alloc_tbl(i).alt_rtg_dsg, l_reqDate, l_assemblyQty
from bom_bill_of_materials a, bom_bill_of_materials b, wip_flow_schedules wfs
where a.bill_sequence_id = b.common_bill_sequence_id
and b.assembly_item_id = wfs.primary_item_id
and wfs.wip_entity_id = p_alloc_tbl(i).wip_entity_id
and b.organization_id = wfs.organization_id
and ( nvl(b.alternate_bom_designator, 'none') = nvl(wfs.alternate_bom_designator, 'none')
or ( b.alternate_bom_designator IS NULL
and not exists(select 'x'
from bom_bill_of_materials c
where c.assembly_item_id = wfs.primary_item_id
and c.organization_id = wfs.organization_id
and c.alternate_bom_designator = wfs.alternate_bom_designator
)
)
)
for update of wfs.allocated_flag nowait;
select a.routing_sequence_id
into l_routing_seq_id
from bom_operational_routings a, bom_operational_routings b, wip_flow_schedules wfs
where a.routing_sequence_id = b.common_routing_sequence_id
and b.assembly_item_id = wfs.primary_item_id
and wfs.wip_entity_id = p_alloc_tbl(i).wip_entity_id
and b.organization_id = wfs.organization_id
and ( nvl(b.alternate_routing_designator, 'none') = nvl(wfs.alternate_routing_designator, 'none')
or ( b.alternate_routing_designator IS NULL
and not exists(select 'x'
from bom_operational_routings c
where c.assembly_item_id = wfs.primary_item_id
and c.organization_id = wfs.organization_id
and c.alternate_routing_designator = wfs.alternate_routing_designator
)
)
);
select allocated_flag
into l_dummy
from wip_flow_schedules
where wip_entity_id = p_alloc_tbl(i).wip_entity_id
for update of allocated_flag nowait;
select wsh_delivery_group_s.nextval
into l_carton_grouping_id
from dual;
SELECT bos1.operation_seq_num
into l_operationSeqNum
FROM bom_operation_sequences bos1
WHERE bos1.operation_sequence_id = (select bos2.line_op_seq_id from bom_operation_sequences bos2
where bos2.routing_sequence_id = l_routing_seq_id
and bos2.operation_seq_num = l_flowCompTbl(j).operation_seq_num
and bos2.operation_type = 1 );
l_flowCompTbl.delete;
select we.wip_entity_name, msi.concatenated_segments
into l_jobname, l_item
from wip_entities we, mtl_system_items_vl msi
where we.wip_entity_id = x_MOLineErrTbl(i).txn_source_id
and msi.inventory_item_id = x_MOLineErrTbl(i).inventory_item_id
and msi.organization_id = x_MOLineErrTbl(i).organization_id;
/* This procedure is called by allocate to update WRO and fill in header and lines record appropriately */
procedure get_HdrLinesRec( p_wip_entity_id NUMBER,
p_project_id NUMBER,
p_task_id NUMBER,
p_wip_entity_type NUMBER,
p_repetitive_schedule_id NUMBER,
p_operation_seq_num NUMBER,
p_inventory_item_id NUMBER,
p_use_pickset_flag VARCHAR2,
p_pickset_id NUMBER,
p_open_qty NUMBER,
p_to_subinv VARCHAR2,
p_to_locator NUMBER,
p_default_subinv VARCHAR2,
p_default_locator NUMBER,
p_uom VARCHAR2 ,
p_supply_type NUMBER ,
p_req_date DATE,
p_rev_control_code VARCHAR2 ,
p_organization_id NUMBER,
p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
p_carton_grouping_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
p_hdrRec IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
p_linesRec IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_sysDate DATE := sysdate;
p_hdrRec.last_updated_by := l_userId;
p_hdrRec.last_update_date := l_sysDate;
p_hdrRec.last_update_login := l_loginId;
p_linesRec.last_updated_by := l_userId;
p_linesRec.last_update_date := l_sysDate;
p_linesRec.last_update_login := l_loginId;
/* for EAM entity type, select picksilp grouping rule from wip_parameters. If no rule is defined, raise an exception with suitable message */
if(p_wip_entity_type = wip_constants.eam) then
select pickslip_grouping_rule_id
into l_pick_grouping_rule_id
from wip_parameters
where organization_id = p_organization_id;
update wip_requirement_operations
set quantity_backordered = p_open_qty,
last_update_date = l_sysDate,
last_updated_by = l_userId,
last_update_login = l_loginId
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num;
select end_item_unit_number
into l_unit_number
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
select default_pull_supply_subinv, default_pull_supply_locator_id
into l_defaultSub, l_defaultLocator
from wip_parameters
where organization_id = p_organization_id;
select (wro.required_quantity - wro.quantity_issued
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
- wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
into l_openQty,
l_subinv,
l_locator,
l_uom,
l_supplyType,
l_reqDate,
l_revControlCode
from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
and (
( 'Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
)
or wro.wip_supply_type = wip_constants.push
)
and wro.required_quantity > (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
and wro.inventory_item_id = msi.inventory_item_id
and wro.wip_entity_id = wo.wip_entity_id (+)
and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
and wro.operation_seq_num = wo.operation_seq_num (+)
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
select (wro.required_quantity - wro.quantity_issued
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
- wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
into l_openQty,
l_subinv,
l_locator,
l_uom,
l_supplyType,
l_reqDate,
l_revControlCode
from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
and wro.required_quantity > (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
and wro.inventory_item_id = msi.inventory_item_id
and wro.wip_entity_id = wo.wip_entity_id (+)
and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
and wro.operation_seq_num = wo.operation_seq_num (+)
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
select (((wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed) * wro.quantity_per_assembly)
- wro.quantity_issued
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
into l_openQty,
l_subinv,
l_locator,
l_uom,
l_supplyType,
l_reqDate,
l_revControlCode
from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
and wro.wip_entity_id = wo.wip_entity_id
and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
and wro.operation_seq_num = wo.operation_seq_num
and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
and (
( 'Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
)
or wro.wip_supply_type = wip_constants.push
)
and ((wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed) * wro.quantity_per_assembly) > (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
and wro.inventory_item_id = msi.inventory_item_id
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
select least( --open qty is the smaller of the remaining quantity and the quantity determined by the #days to allocate for
(wro.required_quantity - wro.quantity_issued
- nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
- wo.CUMULATIVE_SCRAP_QUANTITY*wro. QUANTITY_PER_ASSEMBLY),
(wrs.daily_production_rate * wro.quantity_per_assembly * p_days_to_alloc) + nvl(wro.quantity_backordered, 0)) open_quantity,
wro.supply_subinventory,
wro.supply_locator_id,
msi.primary_uom_code,
wro.wip_supply_type,
wro.date_required,
msi.revision_qty_control_code
into l_openQty,
l_subinv,
l_locator,
l_uom,
l_supplyType,
l_reqDate,
l_revControlCode
from wip_requirement_operations wro,
wip_repetitive_schedules wrs,
mtl_system_items_b msi,
wip_operations wo
where wro.repetitive_schedule_id = p_alloc_comp_tbl(i).repetitive_schedule_id
and wrs.repetitive_schedule_id = p_alloc_comp_tbl(i).repetitive_schedule_id
and ((p_cutoff_date is null) OR (wro.date_required < p_cutoff_date))
and (
( 'Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
)
or wro.wip_supply_type = wip_constants.push
)
and wro.required_quantity > (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
and wro.inventory_item_id = msi.inventory_item_id
and wro.organization_id = msi.organization_id
and wro.organization_id = p_organization_id
and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
and wro.operation_seq_num = wo.operation_seq_num (+)
and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
for update of wro.quantity_allocated, wro.quantity_backordered, quantity_issued nowait;
select wsh_delivery_group_s.nextval
into l_carton_grouping_id
from dual;
select we.wip_entity_id,
we.wip_entity_name,
wdj.project_id,
wdj.task_id
from wip_entities we,
wip_discrete_jobs wdj,
wip_parameters wp
where we.wip_entity_id = wdj.wip_entity_id
and we.organization_id = wdj.organization_id
and wp.organization_id = wdj.organization_id
and wp.organization_id = we.organization_id
and we.entity_type = 1
and wdj.status_type in (3,4)
and wdj.job_type = decode(p_job_type, 4, wdj.job_type, p_job_type) /*Bug 5932126 (FP of 5880558): Added to check job type*/
and we.organization_id = p_organization_id
and exists(
select 1
from wip_requirement_operations wro,
wip_operations wo
where we.wip_entity_id = wro.wip_entity_id
and we.organization_id = wro.organization_id
and wro.wip_entity_id = wo.wip_entity_id(+)
and wro.operation_seq_num = wo.operation_seq_num(+)
and (wo.count_point_type in (1,2) or wo.count_point_type is null)
and wro.required_quantity > wro.quantity_issued /*Bug 7447655 (FP of 7495308) Fix*/
and ( ( wp.allocate_backflush_components = 'Y' and wro.wip_supply_type in (1,2,3) ) or ( ( wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null )
and wro.wip_supply_type = 1 ) )
and wro.date_required < sysdate + p_days_forward)
order by we.wip_entity_name;
select distinct we.wip_entity_id,
we.wip_entity_name,
wdj.project_id,
wdj.task_id
from wip_entities we,
wip_discrete_jobs wdj,
wip_requirement_operations wro,
wip_parameters wp,
wip_operations wo
where wo.wip_entity_id = we.wip_entity_id
and wo.operation_seq_num = wro.operation_seq_num
and we.wip_entity_id = wdj.wip_entity_id
and we.entity_type = 5
and wdj.status_type in (3,4)
and wro.wip_entity_id = we.wip_entity_id
and wp.organization_id = we.organization_id
and (wo.quantity_in_queue + quantity_running +
wo.quantity_waiting_to_move + wo.quantity_rejected +
wo.quantity_scrapped + wo.quantity_completed) *
wro.quantity_per_assembly > wro.quantity_issued
+ nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED), 0)
and (
( wp.allocate_backflush_components = 'Y'
and wro.wip_supply_type in (1,2,3)
)
or (
(wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null)
and wro.wip_supply_type = 1
)
)
and wro.date_required < sysdate + p_days_forward
and we.organization_id = p_organization_id
order by we.wip_entity_name;
SELECT DISTINCT * FROM ( select distinct we.wip_entity_id,
we.wip_entity_name,
wdj.project_id,
wdj.task_id,
min(wro.date_required) min_date_required
from wip_entities we,
wip_discrete_jobs wdj,
wip_parameters wp,
wip_requirement_operations wro,
wip_operations wo
where we.wip_entity_id = wdj.wip_entity_id
and we.organization_id = wdj.organization_id
and wp.organization_id = wdj.organization_id
and wp.organization_id = we.organization_id
AND we.wip_entity_id = wo.wip_entity_id
and wro.organization_id = wo.organization_id
and wro.wip_entity_id = wo.wip_entity_id
and wro.operation_seq_num = wo.operation_seq_num
and we.entity_type = 6 --only for non-closed workorders
and wdj.status_type = 3
and wdj.MATERIAL_ISSUE_BY_MO LIKE 'Y' --only for workorders that have Enable Material Request as Yes
and we.organization_id = p_organization_id
and exists(
select 1
from wip_requirement_operations wro,
wip_operations wo
where we.wip_entity_id = wro.wip_entity_id
and we.organization_id = wro.organization_id
and wro.wip_entity_id = wo.wip_entity_id(+)
and wro.operation_seq_num = wo.operation_seq_num(+)
and (wo.count_point_type in (1,2) or wo.count_point_type is null)
and wro.required_quantity > (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
and wro.date_required < sysdate + p_days_forward)
group by we.wip_entity_id,
we.wip_entity_name,
wdj.project_id,
wdj.task_id) order by min_date_required;
select distinct we.wip_entity_id,
(wl.line_code || ':' || msik.concatenated_segments || ':' || wrs.first_unit_start_date ) title,
wrs.repetitive_schedule_id
from wip_entities we,
mtl_system_items_kfv msik,
wip_repetitive_schedules wrs,
wip_repetitive_items wri,
wip_requirement_operations wro,
wip_parameters wp,
wip_lines wl,
wip_operations wo
where we.wip_entity_id = wrs.wip_entity_id
and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
and we.wip_entity_id = wri.wip_entity_id
and we.primary_item_id = msik.inventory_item_id
and wrs.status_type in (3,4)
and wrs.line_id = wl.line_id
and wri.line_id = wl.line_id
and msik.organization_id = we.organization_id
and wro.wip_entity_id = we.wip_entity_id
and wp.organization_id = we.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 (wo.count_point_type in (1,2) or wo.count_point_type is null)
and wro.required_quantity > (wro.quantity_issued +
nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
and ( ( wp.allocate_backflush_components = 'Y' and wro.wip_supply_type in (1,2,3) ) or ( ( wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null ) and wro.wip_supply_type = 1 ) )
and wro.date_required < sysdate + p_days_forward
and we.organization_id = p_organization_id
order by title;
select wip_entity_id,
(line_code || ':' || primary_item_id || ':' || schedule_number) title,
project_id,
task_id
from wip_flow_open_allocations_v
where scheduled_start_date < sysdate + p_days_forward
and organization_id = p_organization_id
order by title;
select meaning
into l_outBuffer
from mfg_lookups
where lookup_type = 'SYS_YES_NO'
and lookup_code = p_use_pickset_indicator;
select meaning
into l_outBuffer
from mfg_lookups
where lookup_type = 'SYS_YES_NO'
and lookup_code = p_print_pickslips;
select name
into l_outBuffer
from wsh_pick_grouping_rules
where pick_grouping_rule_id = p_pick_grouping_rule_id;
select meaning
into l_outBuffer
from mfg_lookups
where lookup_type = 'SYS_YES_NO'
and lookup_code = p_plan_tasks;
select organization_code
into l_outBuffer
from MTL_PARAMETERS
where organization_id = p_organization_id;
select meaning
into l_outBuffer
from mfg_lookups
where lookup_type = 'WIP_ENTITY'
and lookup_code = p_wip_entity_type;
select meaning
into l_outBuffer
from mfg_lookups
where lookup_type = 'WIP_ENTITIES'
and lookup_code = p_job_type ;
p_delete_stack => fnd_api.g_true);
select unique operation_seq_num
from wip_requirement_operations
where wip_entity_id = v_wip_entity_id
and organization_id = v_organization_id
and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);
select unique operation_seq_num
from wip_requirement_operations
where wip_entity_id = v_wip_entity_id
and organization_id = v_organization_id
and repetitive_schedule_id = v_repetitive_schedule_id
and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);
select br1.supply_subinventory, br1.supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from bom_resources br1, wip_operation_resources wor1
where br1.resource_id = wor1.resource_id
and br1.organization_id = wor1.organization_id
and wor1.wip_entity_id = p_wip_entity_id
and wor1.organization_id = p_org_id
and wor1.operation_seq_num = l_operation_seq_num
and wor1.resource_seq_num =
(select min(wor2.resource_seq_num)
from bom_resources br2, wip_operation_resources wor2
where wor2.wip_entity_id = wor1.wip_entity_id
and wor2.organization_id= wor1.organization_id
and wor2.operation_seq_num = wor1.operation_seq_num
and br2.supply_subinventory is not null
and br2.organization_id = wor2.organization_id
and br2.resource_id = wor2.resource_id
and br2.resource_type= 1); -- machine type
wip_picking_pub.Update_Requirement_SubinvLoc(p_wip_entity_id => p_wip_entity_id,
p_repetitive_schedule_id => p_repetitive_schedule_id,
p_operation_seq_num => l_operation_seq_num,
p_supply_subinventory => l_supply_subinventory,
p_supply_locator_id => l_supply_locator_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
'wip_picking_pub.Update_Requirement_SubinvLoc failed..', l_dummy2);
select br1.supply_subinventory, br1.supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from bom_resources br1, wip_operation_resources wor1
where br1.resource_id = wor1.resource_id
and br1.organization_id = wor1.organization_id
and wor1.wip_entity_id = p_wip_entity_id
and wor1.repetitive_schedule_id = p_repetitive_schedule_id
and wor1.organization_id = p_org_id
and wor1.operation_seq_num = l_operation_seq_num
and wor1.resource_seq_num =
(select min(wor2.resource_seq_num)
from bom_resources br2, wip_operation_resources wor2
where wor2.wip_entity_id = wor1.wip_entity_id
and wor2.organization_id= wor1.organization_id
and wor2.operation_seq_num = wor1.operation_seq_num
and br2.supply_subinventory is not null
and br2.organization_id = wor2.organization_id
and br2.resource_id = wor2.resource_id
and br2.resource_type= 1); -- machine type
wip_picking_pub.Update_Requirement_SubinvLoc(p_wip_entity_id => p_wip_entity_id,
p_repetitive_schedule_id => p_repetitive_schedule_id,
p_operation_seq_num => l_operation_seq_num,
p_supply_subinventory => l_supply_subinventory,
p_supply_locator_id => l_supply_locator_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
'wip_picking_pub.Update_Requirement_SubinvLoc failed..', l_dummy2);