[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select entity_type
into l_entityType
from wip_entities
where wip_entity_id = p_wip_entity_id;
update wip_requirement_operations
set quantity_backordered = p_allocate_quantity,
last_update_date = l_sysDate,
last_updated_by = l_userId,
last_update_login = l_loginId
where inventory_item_id = p_inventory_item_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num;
update wip_requirement_operations
set quantity_backordered = p_allocate_quantity,
last_update_date = l_sysDate,
last_updated_by = l_userId,
last_update_login = l_loginId
where inventory_item_id = p_inventory_item_id
and wip_entity_id = p_wip_entity_id
and repetitive_schedule_id = p_repetitive_schedule_id
and operation_seq_num = p_operation_seq_num;
SELECT WRO.REPETITIVE_SCHEDULE_ID,
WRO.ORGANIZATION_ID,
WRO.ROWID,
LEAST(GREATEST((WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED), 0),
-- nvl(wro.quantity_allocated, 0)) open_quantity,
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.quantity_allocated
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) quantity_allocated
FROM WIP_REQUIREMENT_OPERATIONS WRO,
WIP_REPETITIVE_SCHEDULES WRS
WHERE WRO.WIP_ENTITY_ID = p_wip_entity_id
AND WRO.INVENTORY_ITEM_ID = p_inventory_item_id
AND WRO.OPERATION_SEQ_NUM = p_operation_seq_num
AND WRS.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID = wro.organization_id
AND WRS.WIP_ENTITY_ID = p_wip_entity_id
AND WRS.LINE_ID = p_repetitive_line_id
AND WRS.STATUS_TYPE in (3,4)
ORDER BY WRS.FIRST_UNIT_START_DATE
for update of wro.quantity_issued, wro.quantity_allocated;
select entity_type
into l_entityType
from wip_entities
where wip_entity_id = p_wip_entity_id;
select wdj.status_type
into l_status
from wip_discrete_jobs wdj, wip_requirement_operations wro
where wdj.wip_entity_id = p_wip_entity_id
and wro.wip_entity_id = wdj.wip_entity_id
and wro.inventory_item_id = p_inventory_item_id
and wro.operation_seq_num = p_operation_seq_num
and wro.wip_supply_type = 1
for update of wro.quantity_issued, wro.quantity_allocated nowait;
select meaning
into l_statusCode
from mfg_lookups
where lookup_type = 'WIP_JOB_STATUS'
and lookup_code = l_status;
update wip_requirement_operations
set quantity_issued = quantity_issued - p_primary_quantity,
quantity_allocated = greatest(0, quantity_allocated + p_primary_quantity)
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id
and wip_supply_type = 1; -- a push component
update wip_requirement_operations
set quantity_issued = quantity_issued + l_updQty,
quantity_allocated = greatest(0, quantity_allocated - l_updQty)
where repetitive_schedule_id = l_rec.repetitive_schedule_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id
and wip_supply_type = 1; -- a push component
insert into mtl_material_txn_allocations(
transaction_id,
repetitive_schedule_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
primary_quantity,
transaction_quantity,
transaction_date)
values(
p_transaction_id,
l_rec.repetitive_schedule_id,
l_rec.organization_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
-1 * round(l_updQty, 6),
-1 * round(l_updQty, 6),
sysdate);
update wip_requirement_operations
set quantity_issued = quantity_issued + l_rmnQty,
quantity_allocated = 0
where repetitive_schedule_id = l_repSchedID
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id
and wip_supply_type = 1; -- a push component
update mtl_material_txn_allocations
set primary_quantity = primary_quantity - ROUND(l_rmnQty, 6),
transaction_quantity = transaction_quantity - ROUND(l_rmnQty, 6)
where repetitive_schedule_id = l_repSchedID
and transaction_id = p_transaction_id;
select entity_type
into l_entityType
from wip_entities
where wip_entity_id = p_wip_entity_id;
select allocated_flag
into l_flow
from wip_flow_schedules
where wip_entity_id = p_wip_entity_id
for update of allocated_flag nowait; --set error code if row is locked
update wip_flow_schedules
set allocated_flag = 'Y'
where wip_entity_id = p_wip_entity_id;
select nvl(quantity_allocated, 0),
nvl(quantity_backordered,0),
p_primary_quantity,
-- above line replaces line below. quantity_allocated() is not equivalent
-- to wro.quantity_allocated column in this case. And the former was
-- used to replace the latter. The difference is the api is already
-- updated with the current pick release, and the column still shows
-- qty allocate before the current pick release.
/*least(greatest(required_quantity - quantity_issued - nvl(
quantity_allocated(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
INVENTORY_ITEM_ID, QUANTITY_ISSUED),0), 0),
p_primary_quantity), */
rowid
into l_allocated,
l_backordered,
l_openQty,
l_rowid
from wip_requirement_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id
for update of quantity_backordered, quantity_allocated nowait;
select nvl(quantity_allocated, 0),
nvl(quantity_backordered,0),
p_primary_quantity,
-- above line replaces line below. quantity_allocated() is not equivalent
-- to wro.quantity_allocated column in this case. And the former was
-- used to replace the latter. The difference is the api is already
-- updated with the current pick release, and the column still shows
-- qty allocate before the current pick release.
/*least(greatest(required_quantity - quantity_issued - nvl(
quantity_allocated(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
INVENTORY_ITEM_ID, QUANTITY_ISSUED),0), 0),
p_primary_quantity), */
rowid
into l_allocated,
l_backordered,
l_openQty,
l_rowid
from wip_requirement_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id
and repetitive_schedule_id = p_repetitive_schedule_id
for update of quantity_backordered, quantity_allocated nowait;
update wip_requirement_operations
set quantity_backordered = 0,
quantity_allocated = l_allocated + l_openQty
where rowid = l_rowid;
update wip_requirement_operations
set quantity_backordered = l_backordered - l_openQty,
quantity_allocated = decode(sign(l_allocated + l_openQty), -1, l_allocated, 0,
0,
(l_allocated + l_openQty))
where rowid = l_rowid;
select distinct mtrl.transaction_type_id
into l_txn_type_id
from MTL_TXN_REQUEST_LINES mtrl
where
mtrl.TXN_SOURCE_ID = p_wip_entity_id and
mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
mtrl.organization_id = p_organization_id and
mtrl.INVENTORY_ITEM_ID = p_inventory_item_id;
select quantity_issued into l_quantity_issued
from WIP_REQUIREMENT_OPERATIONS
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and repetitive_schedule_id = p_repetitive_schedule_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select sum(nvl(mtrl.quantity,0) - nvl(mtrl.quantity_delivered,0))
into l_quantity_allocated
from MTL_TXN_REQUEST_LINES mtrl
where
mtrl.TXN_SOURCE_ID = p_wip_entity_id and
mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
( p_repetitive_schedule_id is null or
mtrl.reference_id = p_repetitive_schedule_id) and
mtrl.organization_id = p_organization_id and
mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
-- preapproved status or open lines
mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
select sum(nvl(mtrl.quantity,0))
into l_mtrl_quantity
from MTL_TXN_REQUEST_LINES mtrl
where
mtrl.TXN_SOURCE_ID = p_wip_entity_id and
mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
( p_repetitive_schedule_id is null or
mtrl.reference_id = p_repetitive_schedule_id) and
mtrl.organization_id = p_organization_id and
mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
mtrl.line_status in (INV_GLOBALS.G_TO_STATUS_CLOSED,
INV_GLOBALS.G_TO_STATUS_PREAPPROVED) -- preapproved or closed
group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
select 1
into l_dummy
from dual
where exists
( select 1
from MTL_TXN_REQUEST_LINES mtrl
where
mtrl.backorder_delivery_detail_id = p_wip_entity_id and
mtrl.TXN_SOURCE_LINE_ID is null and
( p_repetitive_schedule_id is null or
mtrl.reference_id = p_repetitive_schedule_id) and
mtrl.crossdock_type = 2 and -- WIP
mtrl.organization_id = p_organization_id and
mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
mtrl.line_status in (INV_GLOBALS.G_TO_STATUS_CLOSED,
INV_GLOBALS.G_TO_STATUS_PREAPPROVED)) ; -- preapproved or closed
select quantity_allocated
into l_wro_quantity_allocated
from wip_requirement_operations
where wip_entity_id = p_wip_entity_id
and inventory_item_id = p_inventory_item_id
and operation_seq_num = p_operation_seq_num
and organization_id = p_organization_id
and nvl(repetitive_schedule_id, -1 ) = nvl(p_repetitive_schedule_id, -1) ;
Select 1 Into l_dummy
from wip_requirement_operations
Where wip_entity_id = p_wip_entity_id
And Organization_id = p_org_id
And operation_seq_num = p_operation_seq_num
And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
And quantity_backordered is not null;
Select 1 Into l_dummy
from wip_requirement_operations
Where wip_entity_id = p_wip_entity_id
And repetitive_schedule_id = p_repetitive_schedule_id
And Organization_id = p_org_id
And operation_seq_num = p_operation_seq_num
And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
And quantity_backordered is not null;
Select 1 Into l_dummy
From dual
Where exists
(select 1
from wip_requirement_operations
Where wip_entity_id = p_wip_entity_id
And Organization_id = p_org_id
And quantity_backordered is not null);
Select 1 Into l_dummy
From dual
Where exists
(select 1
from wip_requirement_operations
Where wip_entity_id = p_wip_entity_id
And repetitive_schedule_id = p_repetitive_schedule_id
And Organization_id = p_org_id
And quantity_backordered is not null);
Procedure Update_Requirement_SubinvLoc(p_wip_entity_id number,
p_repetitive_schedule_id in NUMBER DEFAULT NULL,
p_operation_seq_num in NUMBER,
p_supply_subinventory in VARCHAR2,
p_supply_locator_id in NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_entityType NUMBER;
SAVEPOINT WIP_UPDATE_REQ_SUBINVLOC_START;
update wip_requirement_operations
set supply_subinventory = p_supply_subinventory,
supply_locator_id = p_supply_locator_id
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and wip_supply_type in
(wip_constants.assy_pull, wip_constants.op_pull);
update wip_requirement_operations
set supply_subinventory = p_supply_subinventory,
supply_locator_id = p_supply_locator_id
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and p_repetitive_schedule_id = p_repetitive_schedule_id
and wip_supply_type in
(wip_constants.assy_pull, wip_constants.op_pull);
ROLLBACK TO WIP_UPDATE_REQ_SUBINVLOC_START;
ROLLBACK TO WIP_UPDATE_REQ_SUBINVLOC_START;
'wip_utilities.Update_Requirement_SubinvLoc: ' || SQLERRM);
End Update_Requirement_SubinvLoc;
Procedure Update_Component_BackOrdQty(p_wip_entity_id number,
p_repetitive_schedule_id in NUMBER DEFAULT NULL,
p_operation_seq_num in NUMBER,
p_new_component_qty in NUMBER,
p_inventory_item_id in NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_entityType NUMBER;
select entity_type
into l_entityType
from wip_entities
where wip_entity_id = p_wip_entity_id;
Update wip_requirement_operations wro
set quantity_backordered = GREATEST(p_new_component_qty
-- replaced wro.quantity_allocated with function quantity_allocated
-- - quantity_issued - quantity_allocated , 0)
- quantity_issued - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED) , 0)
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
and (
('Y' = (select allocate_backflush_components from wip_parameters wp
where organization_id = wro.organization_id)
and wip_supply_type in
(wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
or wip_supply_type = wip_constants.push
);
Update wip_requirement_operations wro
set quantity_backordered = GREATEST(p_new_component_qty
-- replaced wro.quantity_allocated with function quantity_allocated
-- - quantity_issued - quantity_allocated , 0)
- quantity_issued - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED) , 0)
where wip_entity_id=p_wip_entity_id
and operation_seq_num=p_operation_seq_num
and p_repetitive_schedule_id = p_repetitive_schedule_id
and inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
and (
('Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wip_supply_type in
(wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
or wip_supply_type = wip_constants.push
);
fnd_message.set_token('ERROR_TEXT', 'wip_picking_private.Update_Component_BackOrdQty: '
|| SQLERRM);
End Update_Component_BackOrdQty;
Procedure Update_Job_BackOrdQty(p_wip_entity_id number,
p_repetitive_schedule_id in NUMBER DEFAULT NULL,
p_new_job_qty in NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_entityType NUMBER;
select entity_type
into l_entityType
from wip_entities
where wip_entity_id = p_wip_entity_id;
Update wip_requirement_operations wro
set quantity_backordered = GREATEST(p_new_job_qty* quantity_per_assembly -
wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, QUANTITY_ISSUED)
- quantity_issued, 0)
where wip_entity_id=p_wip_entity_id
and quantity_backordered is not null
and (
('Y' = (select allocate_backflush_components
from wip_parameters wp
where organization_id = wro.organization_id)
and wip_supply_type in
(wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
or wip_supply_type = wip_constants.push
);
Update wip_requirement_operations wro
set quantity_backordered = GREATEST(p_new_job_qty* quantity_per_assembly
- wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED)
- quantity_issued, 0)
where wip_entity_id=p_wip_entity_id
and repetitive_schedule_id = p_repetitive_schedule_id
and quantity_backordered is not null
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
) ;
fnd_message.set_token('ERROR_TEXT', 'wip_picking_private.Update_Job_BackOrdQty: '
|| SQLERRM);
End Update_Job_BackOrdQty;