[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*) into x_valid_ez_complete
from WIP_ENTITIES WE
where we.wip_entity_id = p_wip_entity_id
and we.organization_id = p_org_id
and (((we.entity_type = WIP_CONSTANTS.DISCRETE)
and not exists (select 'X'
from WIP_DISCRETE_JOBS DJ
where DJ.WIP_ENTITY_ID = p_wip_entity_id
and DJ.ORGANIZATION_ID = p_org_id
and DJ.COMPLETION_SUBINVENTORY IS NOT NULL))
or (we.entity_type = WIP_CONSTANTS.REPETITIVE)
and not exists (select 'X' from WIP_REPETITIVE_ITEMS WRI
where WRI.WIP_ENTITY_ID = p_wip_entity_id
and WRI.ORGANIZATION_ID = p_org_id
and WRI.LINE_ID = p_line_id
and WRI.COMPLETION_SUBINVENTORY IS NOT NULL));
select count(*) into x_valid_ez_complete
from wip_entities we, mtl_system_items msi
where we.wip_entity_id = p_wip_entity_id
and we.organization_id = p_org_id
and msi.inventory_item_id = we.primary_item_id
and msi.organization_id = we.organization_id
and msi.lot_control_code = WIP_CONSTANTS.LOT
and ( ( we.entity_type = WIP_CONSTANTS.DISCRETE
and exists (select 'X'
from wip_discrete_jobs wdj
where wdj.wip_entity_id = we.wip_entity_id
and wdj.organization_id = we.organization_id
and wdj.lot_number is null))
or (we.entity_type = WIP_CONSTANTS.REPETITIVE) );
select count(*) into x_valid_ez_complete
from MTL_SYSTEM_ITEMS msi, WIP_ENTITIES we
where we.organization_id = p_org_id
and we.wip_entity_id = p_wip_entity_id
and msi.inventory_item_id = we.primary_item_id
and msi.organization_id = we.organization_id
and msi.serial_number_control_code in (2,5);
select count(*) into x_valid_ez_complete
from WIP_SHOP_FLOOR_STATUSES wsfs, WIP_OPERATIONS wo,
WIP_SHOP_FLOOR_STATUS_CODES wsfsc
where wsfs.wip_entity_id = p_wip_entity_id
and wsfs.organization_id = p_org_id
and nvl (wsfs.line_id, -1) = nvl (p_line_id, -1)
and wo.wip_entity_id = wsfs.wip_entity_id
and nvl (wo.repetitive_schedule_id, -1) = nvl (p_rep_sched_id, -1)
and wo.organization_id = wsfs.organization_id
and wo.next_operation_seq_num is null
and wo.operation_seq_num = wsfs.operation_seq_num
and wsfs.intraoperation_step_type=WIP_CONSTANTS.TOMOVE
and wsfsc.shop_floor_status_code = wsfs.shop_floor_status_code
and wsfsc.organization_id = wsfs.organization_id
and wsfsc.status_move_flag = 2
and nvl(wsfsc.disable_date, sysdate + 1) > sysdate;
select count(*) into x_valid_ez_complete
from wip_entities we
where we.wip_entity_id = p_wip_entity_id
and we.organization_id = p_org_id
and exists (
select 'X'
from MTL_SYSTEM_ITEMS msi
where msi.organization_id = p_org_id
and msi.inventory_item_id = we.primary_item_id
and msi.revision_qty_control_code=2)
and ( ( we.entity_type = WIP_CONSTANTS.DISCRETE
and not exists (
select 'X'
from WIP_DISCRETE_JOBS wdj, MTL_ITEM_REVISIONS MIR
where wdj.organization_id = we.organization_id
and wdj.wip_entity_id = we.wip_entity_id
and mir.organization_id = wdj.organization_id
and mir.inventory_item_id = we.primary_item_id
and ( wdj.common_bom_sequence_id is null
or ( wdj.common_bom_sequence_id is not null
and mir.revision = wdj.bom_revision
)
)
))
or (we.entity_type = WIP_CONSTANTS.REPETITIVE
and not exists (
select 'X'
from WIP_REPETITIVE_SCHEDULES WRS, MTL_ITEM_REVISIONS MIR
where wrs.organization_id = we.organization_id
and wrs.repetitive_schedule_id = p_rep_sched_id
and mir.inventory_item_id = we.primary_item_id
and mir.organization_id = we.organization_id
and mir.inventory_item_id = we.primary_item_id
and ( wrs.common_bom_sequence_id is null
or ( wrs.common_bom_sequence_id is not null
and mir.revision = wrs.bom_revision
)
)
)) );
SELECT Decode(msi.outside_operation_uom_type,
'ASSEMBLY' , rti.primary_quantity,
Decode(Nvl(g_OSP_rec.usage_rate_or_amount,0) ,
0 , 0,
(rti.primary_quantity/g_OSP_rec.usage_rate_or_amount)))
INTO
l_primary_quantity
FROM
mtl_system_items msi,
rcv_transactions_interface rti
WHERE rti.interface_transaction_id = g_OSP_rec.source_line_id
AND msi.inventory_item_id = rti.item_id
AND msi.organization_id = g_OSP_rec.organization_id;
SELECT wop.operation_seq_num,
wop.department_id,
nvl(nwop.operation_seq_num, -1),
nvl(nwop.department_id, -1),
wop.operation_seq_num,
wop.quantity_in_queue,
decode(wop.quantity_waiting_to_move, 0,
wop.quantity_completed,
wop.quantity_waiting_to_move),
nvl(nwop.operation_seq_num, -1),
nwop.quantity_in_queue,
decode(nwop.quantity_waiting_to_move, 0,
nwop.quantity_completed,
nwop.quantity_waiting_to_move)
INTO l_fm_operation_seq_num,
l_fm_department_id,
l_to_operation_seq_num,
l_to_department_id,
l_cur_op_seq,
l_cur_qty_in_queue,
l_cur_qty_to_move,
l_next_op_seq,
l_next_qty_in_queue,
l_next_qty_to_move
/*Bug 6146597 Start*/
FROM (select wip_entity_id,
organization_id,
operation_seq_num,
department_id,
quantity_in_queue,
quantity_waiting_to_move,
quantity_completed,
repetitive_schedule_id
from wip_operations
where wip_entity_id= l_wip_entity_id
and nvl (repetitive_schedule_id, -1) = nvl (l_rep_sch_id, -1)
and organization_id= l_organization_id
and operation_seq_num > l_osp_op_seq
and count_point_type = 1
and ROWNUM=1
ORDER BY operation_seq_num) nwop,
/*Bug 6146597 End*/
wip_operations wop
WHERE wop.organization_id = l_organization_id
and wop.wip_entity_id = l_wip_entity_id
and wop.operation_seq_num = l_osp_op_seq
and nvl (wop.repetitive_schedule_id, -1) =
nvl (l_rep_sch_id, -1)
and wop.organization_id = nwop.organization_id(+)
and wop.wip_entity_id = nwop.wip_entity_id(+)
/*Bug 6146597 and wop.next_operation_seq_num = nwop.operation_seq_num(+)*/
and nvl(wop.repetitive_schedule_id, -1) =
nvl(nwop.repetitive_schedule_id(+), -1);
SELECT department_code
INTO g_ShopFloorMove_rec.fm_department_code
FROM bom_departments
WHERE department_id = g_ShopFloorMove_rec.fm_department_id;
SELECT department_id
INTO g_ShopFloorMove_rec.fm_department_id
FROM wip_operations
WHERE wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
AND organization_id = g_ShopFloorMove_rec.organization_id
AND operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
AND (repetitive_schedule_id IS NULL
OR repetitive_schedule_id = g_ShopFloorMove_rec.repetitive_schedule_id);
FUNCTION Get_Last_Updated_By_Name
RETURN VARCHAR2
IS
BEGIN
IF g_ShopFloorMove_rec.last_updated_by_name IS NOT NULL THEN
RETURN g_ShopFloorMove_rec.last_updated_by_name;
IF g_OSP_rec.last_updated_by_name IS NOT NULL THEN
RETURN g_OSP_rec.last_updated_by_name;
END Get_Last_Updated_By_Name;
FUNCTION Get_Last_Update_Login
RETURN VARCHAR2
IS
BEGIN
IF g_ShopFloorMove_rec.last_update_login IS NOT NULL THEN
RETURN g_ShopFloorMove_rec.last_update_login;
IF g_OSP_rec.last_update_login IS NOT NULL THEN
RETURN g_OSP_rec.last_update_login;
END Get_Last_Update_Login;
FUNCTION Get_Program_Update_Date
RETURN DATE
IS
BEGIN
IF g_ShopFloorMove_rec.program_update_date IS NOT NULL THEN
RETURN g_ShopFloorMove_rec.program_update_date;
IF g_OSP_rec.program_update_date IS NOT NULL THEN
RETURN g_OSP_rec.program_update_date;
END Get_Program_Update_Date;
FUNCTION Get_Last_Updated_By
RETURN VARCHAR2
IS
BEGIN
IF g_ShopFloorMove_rec.last_updated_by IS NOT NULL THEN
RETURN g_ShopFloorMove_rec.last_updated_by;
IF g_OSP_rec.last_updated_by IS NOT NULL THEN
RETURN g_OSP_rec.last_updated_by;
END Get_Last_Updated_By;
SELECT line_code
INTO l_line_code
FROM wip_lines
WHERE line_id = g_ShopFloorMove_rec.line_id
AND organization_id = g_ShopFloorMove_rec.organization_id;
SELECT organization_code
INTO g_ShopFloorMove_rec.organization_code
FROM mtl_parameters
WHERE organization_id = g_ShopFloorMove_rec.organization_id;
select nvl(sum(nvl(quantity_delivered,0)),0)
into l_total_po_qty_delivered
from po_distributions_all
where po_distribution_id in
( select distinct rt.po_distribution_id
from rcv_transactions rt
where rt.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
/*and nvl(rt.wip_repetitive_schedule_id, -1)
= nvl(g_ShopFloorMove_rec.repetitive_schedule_id, -1) */
and nvl(rt.wip_line_id,-1) = nvl(g_osp_rec.line_id,-1)
and rt.organization_id = g_ShopFloorMove_rec.organization_id
and rt.wip_operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
and rt.wip_resource_seq_num = g_OSP_rec.resource_seq_num
and rt.transaction_type = 'DELIVER'
and rt.po_distribution_id is not null) ;
SELECT wor.usage_rate_or_amount
INTO l_usage_rate_or_amount
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
and NVL(wor.repetitive_schedule_id, -1) = NVL(g_ShopFloorMove_rec.repetitive_schedule_id, -1)
and wor.operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
and wor.organization_id = g_ShopFloorMove_rec.organization_id
and wor.resource_seq_num = g_osp_rec.Resource_Seq_Num;
fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from WOR');
select uom.uom_code
into l_po_uom_code
from mtl_units_of_measure_vl uom
where uom.unit_of_measure =
( select PL.unit_meas_lookup_code
from po_lines_all Pl
where (pl.po_header_id,pl.po_line_id) =
( select pd.po_header_id, pd.po_line_id
from po_distributions_all pd
where pd.po_distribution_id in
( select distinct rt.po_distribution_id
from rcv_transactions rt
where rt.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
and nvl(rt.wip_repetitive_schedule_id, -1)
= nvl(g_ShopFloorMove_rec.repetitive_schedule_id, -1)
and rt.organization_id = g_ShopFloorMove_rec.organization_id
and rt.wip_operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
and rt.po_distribution_id is not null
)
and rownum = 1
)
);
fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from UOM');
SELECT interface_transaction_id
INTO l_interface_txn_id
FROM rcv_transactions
WHERE transaction_id = g_osp_rec.rcv_transaction_id;
fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from RT');
SELECT item_id
INTO l_osp_item_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_interface_txn_id;
fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from RTI');
select decode (msi.outside_operation_uom_type,
'ASSEMBLY',
inv_convert.inv_um_convert(
l_osp_item_id, -- item_id
NULL, -- precision
l_total_po_qty_delivered, -- from_quantity
l_po_uom_code, -- from_unit
g_ShopFloorMove_rec.primary_uom, -- to_unit
NULL, -- from_name
NULL -- to_name
),
decode (nvl(l_usage_rate_or_amount, 0) ,
0, 0,
l_total_po_qty_delivered/l_usage_rate_or_amount))
into l_total_pri_qty_delivered
from mtl_system_items msi
where msi.inventory_item_id = l_osp_item_id
and msi.organization_id = g_ShopFloorMove_rec.organization_id;
fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from MSI');
select quantity_in_queue,
quantity_running,
quantity_completed
into l_quantity_in_queue,
l_quantity_running,
l_quantity_completed
from wip_operations wo
where wo.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
and nvl(wo.repetitive_schedule_id, -1) = nvl(g_ShopFloorMove_rec.repetitive_schedule_id, -1)
and wo.organization_id = g_ShopFloorMove_rec.organization_id
and wo.operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num;
select sum(quantity_in_queue) ,
sum(wo.quantity_running) ,
sum(wo.quantity_completed)
into l_quantity_in_queue,
l_quantity_running,
l_quantity_completed
from wip_operations wo,
wip_repetitive_schedules wrs
where wo.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
and wrs.wip_entity_id = wo.wip_entity_id
and wo.organization_id = wrs.organization_id
and wo.repetitive_schedule_id = wrs.repetitive_schedule_id
and wrs.line_id = g_osp_rec.line_id
and wrs.status_type = WIP_CONSTANTS.RELEASED
and wo.organization_id = g_ShopFloorMove_rec.organization_id
and wo.operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num;
fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from WO');
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = g_ShopFloorMove_rec.primary_item_id
AND organization_id = g_ShopFloorMove_rec.organization_id;
SELECT department_code
INTO g_ShopFloorMove_rec.to_department_code
FROM bom_departments
WHERE department_id = g_ShopFloorMove_rec.to_department_id;
SELECT department_id
INTO g_ShopFloorMove_rec.to_department_id
FROM wip_operations
WHERE wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
AND operation_seq_num = g_ShopFloorMove_rec.to_operation_seq_num
AND organization_id = g_ShopFloorMove_rec.organization_id
AND (repetitive_schedule_id IS NULL
OR repetitive_schedule_id = g_ShopFloorMove_rec.repetitive_schedule_id);
g_ShopFloorMove_rec.last_updated_by_name := Get_Last_Updated_By_Name;
g_ShopFloorMove_rec.last_updated_by := Get_Last_Updated_By;
g_ShopFloorMove_rec.last_update_login := Get_Last_Update_Login;
g_ShopFloorMove_rec.program_update_date := Get_Program_Update_Date;
IF g_ShopFloorMove_rec.last_update_date IS NULL THEN
g_ShopFloorMove_rec.last_update_date := Sysdate;