[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
source_subinventory,
source_locator_id
FROM
mtl_kanban_pull_sequences
WHERE
organization_id = i_org_id AND
kanban_plan_id = -1 AND
source_type = 3 AND
inventory_item_id = p_item_id AND
subinventory_name = p_subinv AND
nvl(locator_id,-1) = nvl(p_loc_id,-1);
SELECT DISTINCT
bic.component_item_id,
decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type),
decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory),
decode(bic.supply_subinventory,null,msi.wip_supply_locator_id,bic.supply_locator_id)
FROM bom_bill_of_materials bbom,
bom_inventory_components bic,
mtl_system_items msi
WHERE bbom.organization_id = i_org_id AND
bbom.alternate_bom_designator is null AND
bbom.assembly_item_id = l_q_item_id AND
bbom.common_bill_sequence_id = bic.bill_sequence_id AND
(6=decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type) OR
i_backflush_sub IS NULL OR
i_backflush_sub=decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory)) AND
bic.effectivity_date < SYSDATE AND
(bic.disable_date > sysdate-1 or bic.disable_date is null) AND
msi.organization_id = i_org_id AND
msi.inventory_item_id = bic.component_item_id
ORDER BY bic.component_item_id;
SELECT DISTINCT
bic.component_item_id,
decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type),
decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory),
decode(bic.supply_subinventory,null,msi.wip_supply_locator_id,bic.supply_locator_id)
FROM bom_bill_of_materials bbom,
bom_inventory_components bic,
mtl_system_items msi
WHERE bbom.organization_id = i_org_id AND
-- nvl(bbom.alternate_bom_designator, 'NONE') = nvl(l_alt, 'NONE') AND
bbom.assembly_item_id = l_q_item_id AND
bbom.common_bill_sequence_id = bic.bill_sequence_id AND
(6=decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type) OR
i_backflush_sub IS NULL OR
i_backflush_sub=decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory)) AND
bic.effectivity_date < SYSDATE AND
(bic.disable_date > sysdate-1 or bic.disable_date is null) AND
msi.organization_id = i_org_id AND
msi.inventory_item_id = bic.component_item_id
ORDER BY bic.component_item_id;
SELECT DISTINCT
bic.component_item_id,
decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type),
decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory),
decode(bic.supply_subinventory,null,msi.wip_supply_locator_id,bic.supply_locator_id)
FROM bom_bill_of_materials bbom,
bom_inventory_components bic,
mtl_system_items msi
WHERE bbom.organization_id = i_org_id AND
-- bbom.alternate_bom_designator IS NULL AND
bbom.common_bill_sequence_id = bic.bill_sequence_id AND
(6=decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type) OR
i_backflush_sub IS NULL OR
i_backflush_sub=decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory)) AND
bic.effectivity_date < SYSDATE AND
(bic.disable_date > sysdate-1 or bic.disable_date is null) AND
msi.organization_id = i_org_id AND
msi.inventory_item_id = bic.component_item_id
ORDER BY bic.component_item_id;
select DISTINCT
bom.assembly_item_id top_assembly_item_id,
bom.organization_id,
bom.assembly_item_id,
bom.alternate_bom_designator,
bic.component_item_id,
bic.operation_seq_num,
decode(bic.wip_supply_type, null, msi.wip_supply_type, bic.wip_supply_type),
decode(bic.supply_subinventory, null, msi.wip_supply_subinventory, bic.supply_subinventory),
decode(bic.supply_subinventory, null, msi.wip_supply_locator_id, bic.supply_locator_id),
-1,
-1
from
bom_bill_of_materials bom,
bom_operational_routings bor,
bom_inventory_components bic,
mtl_system_items msi
where
bom.organization_id = p_org_id and
bom.organization_id = bor.organization_id and
bom.assembly_item_id = bor.assembly_item_id and
((bom.alternate_bom_designator = bor.alternate_routing_designator) or
(bom.alternate_bom_designator is null and bor.alternate_routing_designator is null)) and
bor.line_id = P_LINE_ID and
bom.common_bill_sequence_id = bic.bill_sequence_id and
bic.effectivity_date < sysdate and
(bic.disable_date > sysdate - 1 or bic.disable_date is null) and
-- bic.wip_supply_type in (2,3,6) and
msi.organization_id = p_org_id and
msi.inventory_item_id = bic.component_item_id
order by
bic.component_item_id;
select DISTINCT
p_top_assembly_item_id,
bom.organization_id,
bom.assembly_item_id,
bom.alternate_bom_designator,
bic.component_item_id,
bic.operation_seq_num,
decode(bic.wip_supply_type, null, msi.wip_supply_type, bic.wip_supply_type),
decode(bic.supply_subinventory, null, msi.wip_supply_subinventory, bic.supply_subinventory),
decode(bic.supply_subinventory, null, msi.wip_supply_locator_id, bic.supply_locator_id),
-1,
-1
from
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items msi
where
bom.organization_id = p_org_id and
bom.assembly_item_id = p_assembly_item_id and
((bom.alternate_bom_designator = p_alt) or
(bom.alternate_bom_designator is null and p_alt is null)) and
bom.common_bill_sequence_id = bic.bill_sequence_id and
bic.effectivity_date < sysdate and
(bic.disable_date > sysdate - 1 or bic.disable_date is null) and
-- bic.wip_supply_type in (2,3,6) and
msi.organization_id = p_org_id and
msi.inventory_item_id = bic.component_item_id
order by
bic.component_item_id;
Select
bos2.standard_operation_id
From
bom_operational_routings bor,
bom_operation_sequences bos1,
bom_operation_sequences bos2
Where
bor.organization_id = p_org_id and
bor.assembly_item_id = p_assembly_item_id and
nvl(bor.alternate_routing_designator,'NONE') = nvl(p_alt,'NONE') and
bos1.routing_sequence_id = bor.common_routing_sequence_id and
bos1.line_op_seq_id = bos2.operation_sequence_id and
bos2.operation_type = 3 and
bos1.operation_type = 1 and
bos1.operation_seq_num = p_op_seq_num and
bos1.effectivity_date < sysdate and
(bos1.disable_date > sysdate - 1 or bos1.disable_date is null);
Select
bos2.standard_operation_id
From
bom_operational_routings bor,
bom_operation_sequences bos1,
bom_operation_sequences bos2
Where
bor.organization_id = p_org_id and
bor.assembly_item_id = p_assembly_item_id and
nvl(bor.alternate_routing_designator,'NONE') = nvl(p_alt,'NONE') and
bos1.routing_sequence_id = bor.common_routing_sequence_id and
bos1.process_op_seq_id = bos2.operation_sequence_id and
bos2.operation_type = 2 and
bos1.operation_type = 1 and
bos1.operation_seq_num = p_op_seq_num and
bos1.effectivity_date < sysdate and
(bos1.disable_date > sysdate - 1 or bos1.disable_date is null);
select INHERIT_PHANTOM_OP_SEQ
into L_INHERIT_PHANTOM_OP_SEQ
from bom_parameters
where organization_id = p_org_id;
G_COMPONENTS.DELETE(l_index);
p_line_items.DELETE(l_index);
l_items.DELETE(l_index);
l_cat_where_clause := ' msi.inventory_item_id IN (select '||
' inventory_item_id from mtl_item_categories mic, '||
' mtl_categories cat where ' ||
' cat.category_id = mic.category_id' ||
' AND mic.organization_id = :org_id' ||
' AND mic.category_set_id = :category_set_id' ||
' AND ' || l_cat_where_clause || ')';
l_cat_where_clause := ' msi.inventory_item_id IN (select '||
' inventory_item_id from mtl_item_categories mic ' ||
' where mic.organization_id = :org_id' ||
' AND mic.category_set_id = :category_set_id' || ')';
l_sql_stmt := 'SELECT DISTINCT inventory_item_id ' ||
'FROM mtl_system_items msi ' ||
'WHERE organization_id = :org_id' || ' ';
SELECT msi1.segment1 item,
mkd.inventory_item_id item_id,
mkd.subinventory item_sub,
substr(mil1.concatenated_segments, 0, 5) item_loc,
msi2.segment1 de_item,
mkd.assembly_item_id de_item_id,
mkd.assembly_subinventory de_item_sub,
substr(mil2.concatenated_segments, 0, 5) de_item_loc,
demand_quantity quantity
FROM mrp_kanban_demand mkd, mtl_system_items msi1, mtl_system_items msi2,
mtl_item_locations_kfv mil1, mtl_item_locations_kfv mil2
WHERE mkd.kanban_plan_id = l_kp_id
AND mkd.inventory_item_id = l_des_compid
AND mkd.subinventory = l_des_sub
AND (substr(mil1.concatenated_segments, 0, 5) = l_des_loc OR
(l_des_loc IS NULL AND
substr(mil1.concatenated_segments, 0, 5) IS NULL))
AND mkd.organization_id = msi1.organization_id
AND mkd.organization_id = msi2.organization_id
AND mkd.inventory_item_id = msi1.inventory_item_id
AND mkd.assembly_item_id = msi2.inventory_item_id
AND mkd.locator_id = mil1.inventory_location_id(+)
AND mkd.assembly_locator_id = mil2.inventory_location_id(+)
ORDER BY msi1.segment1, mkd.subinventory, msi2.segment1, mkd.assembly_subinventory;
SELECT msi1.segment1,
mkd.inventory_item_id, mkd.subinventory,
substr(mil1.concatenated_segments, 0, 5),
msi2.segment1, mkd.assembly_item_id,
mkd.assembly_subinventory,
substr(mil2.concatenated_segments, 0, 5),
demand_quantity
FROM mrp_kanban_demand mkd, mtl_system_items msi1, mtl_system_items msi2,
mtl_item_locations_kfv mil1, mtl_item_locations_kfv mil2
WHERE mkd.kanban_plan_id = l_kp_id
AND mkd.organization_id = msi1.organization_id
AND mkd.organization_id = msi2.organization_id
AND mkd.inventory_item_id = msi1.inventory_item_id
AND mkd.assembly_item_id = msi2.inventory_item_id
AND mkd.locator_id = mil1.inventory_location_id(+)
AND mkd.assembly_locator_id = mil2.inventory_location_id(+)
AND mkd.inventory_item_id = l_relevant(l_cnt).de_item_id -- id match
AND (mkd.subinventory = l_relevant(l_cnt).de_item_sub OR
((mkd.subinventory IS NULL) AND
(l_relevant(l_cnt).de_item_sub IS NULL)))
AND (substr(mil1.concatenated_segments, 0, 5) = l_relevant(l_cnt).de_item_loc OR
((mil1.concatenated_segments IS NULL) AND
(l_relevant(l_cnt).de_item_loc IS NULL)))
AND (demand_quantity*l_relevant(l_cnt).unit_qty)=l_relevant(l_cnt).quantity; -- quantity match
SELECT kanban_plan_id, subinventory_name, locator_id, inventory_item_id, organization_id
INTO l_kp_id, l_des_sub, l_des_locid, l_des_compid, l_org_id
FROM mtl_kanban_pull_sequences
WHERE pull_sequence_id = i_pull_sequence_id;
SELECT substr(concatenated_segments, 0, 5)
INTO l_des_loc
FROM mtl_item_locations_kfv
WHERE inventory_location_id = l_des_locid;
SELECT plan_start_date, plan_cutoff_date
INTO l_sdate, l_edate
FROM mrp_kanban_plans
WHERE kanban_plan_id = l_kp_id;
SELECT sum(bic.component_quantity)
INTO l_relevant(l_j).unit_qty
FROM bom_inventory_components bic, bom_bill_of_materials bbom
WHERE l_relevant(l_j).de_item_id = bbom.assembly_item_id
and bbom.organization_id = l_org_id
and bbom.alternate_bom_designator is null
AND bbom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_item_id = l_relevant(l_j).item_id
AND bic.supply_subinventory = l_relevant(l_j).item_sub;
select sum(bic.COMPONENT_QUANTITY)
into l_relevant(l_total).unit_qty
from BOM_INVENTORY_COMPONENTS bic, BOM_BILL_OF_MATERIALS bbom
where l_relevant(l_total).de_item_id = bbom.ASSEMBLY_ITEM_ID
and bbom.organization_id = l_org_id
and bbom.alternate_bom_designator is null
AND bbom.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
AND bic.COMPONENT_ITEM_ID = l_relevant(l_total).item_id
AND (bic.SUPPLY_SUBINVENTORY = l_relevant(l_total).item_sub OR
((bic.SUPPLY_SUBINVENTORY IS NULL) AND
(l_relevant(l_total).item_sub IS NULL)));
SELECT msi.segment1 item,
mkd.inventory_item_id item_id,
mkd.subinventory item_sub,
substr(mil.concatenated_segments, 0, 5) item_loc,
trunc(demand_date) d,
demand_quantity quantity
FROM mrp_kanban_demand mkd, mtl_system_items msi,
mtl_item_locations_kfv mil
WHERE mkd.kanban_plan_id = l_kp_id
AND (mkd.subinventory = l_des_sub OR
(mkd.subinventory IS NULL AND
l_des_sub IS NULL))
AND (substr(mil.concatenated_segments, 0, 5) = l_des_loc OR
(l_des_loc IS NULL AND
substr(mil.concatenated_segments, 0, 5) IS NULL))
AND mkd.organization_id = msi.organization_id
AND mkd.inventory_item_id = msi.inventory_item_id
AND msi.segment1 = l_des_comp
AND mkd.locator_id = mil.inventory_location_id(+)
ORDER BY demand_date, msi.segment1, mkd.subinventory;
SELECT kanban_plan_id, subinventory_name, locator_id, inventory_item_id, organization_id
INTO l_kp_id, l_des_sub, l_des_locid, l_des_compid, l_org_id
FROM mtl_kanban_pull_sequences
WHERE pull_sequence_id = i_pull_sequence_id;
SELECT nvl(plan_start_date,sysdate), nvl(plan_cutoff_date,sysdate)
INTO l_sdate, l_edate
FROM mrp_kanban_plans
WHERE kanban_plan_id = l_kp_id;
SELECT DISTINCT segment1
INTO l_des_comp
FROM mtl_system_items
WHERE inventory_item_id = l_des_compid AND
organization_id = l_org_id;
SELECT substr(concatenated_segments, 0, 5)
INTO l_des_loc
FROM mtl_item_locations_kfv
WHERE inventory_location_id = l_des_locid;