[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR c1 is select
organization_id OI, bill_sequence_id BSI,
assembly_item_id AII, common_bill_sequence_id CBSI,
common_assembly_item_id CAII, assembly_type AST,
common_organization_id COI,
alternate_bom_designator ABD, transaction_id TI
from bom_bill_of_mtls_interface
where process_flag = 2
and rownum < 500;
select organization_id
into dummy_id
from mtl_parameters
where organization_id = c1rec.OI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select 1
into dummy_id
from bom_alternate_designators
where organization_id = c1rec.OI
and alternate_designator_code = c1rec.ABD;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select inventory_item_id
into cnt
from mtl_system_items
where organization_id = org_id
and inventory_item_id = assy_id;
select bill_sequence_id
into cnt
from bom_bill_of_materials
where bill_sequence_id = bom_seq_id;
select count(*)
into cnt
from bom_bill_of_mtls_interface
where bill_sequence_id = bom_seq_id
and process_flag = 4;
select bill_sequence_id
into cnt
from bom_bill_of_materials
where organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE');
select bill_sequence_id
into cnt
from bom_bill_of_mtls_interface
where organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and rownum = 1
and process_flag = 4;
select bill_sequence_id
into cnt
from bom_bill_of_materials
where organization_id = org_id
and assembly_item_id = assy_id
and alternate_bom_designator is null
and ((assy_type = 2)
or
(assy_type =1 and assembly_type = 1)
);
select bill_sequence_id
into cnt
from bom_bill_of_mtls_interface
where organization_id = org_id
and assembly_item_id = assy_id
and alternate_bom_designator is null
and ((assy_type = 2)
or
(assy_type =1 and assembly_type = 1)
)
and process_flag = 4
and rownum = 1;
select 1
into cnt
from mtl_system_items
where organization_id = org_id
and inventory_item_id = assy_id
and bom_enabled_flag = 'Y'
and ((assy_type = 2)
or
(assy_type = 1 and
eng_item_flag = 'N')
);
select 1
into cnt
from mtl_parameters mp1, mtl_parameters mp2
where mp1.organization_id = org_id
and mp2.organization_id = cmn_org_id
and mp1.master_organization_id = mp2.master_organization_id;
select bill_sequence_id
into cnt
from bom_bill_of_materials
where bill_sequence_id = cmn_bom_id
and assembly_item_id = cmn_item_id
and organization_id = cmn_org_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and common_bill_sequence_id = bill_sequence_id
and (assembly_item_id <> item_id
or
organization_id <> org_id
)
and ((bom_type <> 1)
or
(bom_type = 1
and
assembly_type = 1
)
);
select bill_sequence_id
into cnt
from bom_bill_of_mtls_interface
where bill_sequence_id = cmn_bom_id
and assembly_item_id = cmn_item_id
and organization_id = cmn_org_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and common_bill_sequence_id = bill_sequence_id
and (assembly_item_id <> item_id
or
organization_id <> org_id
)
and process_flag = 4
and ((bom_type <> 1)
or
(bom_type = 1
and
assembly_type = 1
)
);
select bom_item_type, base_item_id, replenish_to_order_flag,
pick_components_flag
into bit, base_id, ato, pto
from mtl_system_items
where inventory_item_id = item_id
and organization_id = org_id;
select count(*)
into cnt
from bom_inventory_components bic
where bic.bill_sequence_id = cmn_bom_id
and NOT EXISTS
(SELECT 'x'
FROM MTL_SYSTEM_ITEMS S
WHERE S.ORGANIZATION_ID = org_id
AND S.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
AND ((bom_type = 1
AND S.ENG_ITEM_FLAG = 'N')
OR (bom_type = 2))
AND S.BOM_ENABLED_FLAG = 'Y'
AND S.INVENTORY_ITEM_ID <> item_id
AND ((bit = 1
AND S.BOM_ITEM_TYPE <> 3)
OR (bit = 2
AND S.BOM_ITEM_TYPE <> 3)
OR (bit = 3)
OR (bit = 4
AND (S.BOM_ITEM_TYPE = 4
OR (S.BOM_ITEM_TYPE IN (2, 1)
AND S.REPLENISH_TO_ORDER_FLAG = 'Y'
AND base_id IS NOT NULL
AND ato = 'Y')))
)
AND (bit = 3
OR
pto = 'Y'
OR
S.PICK_COMPONENTS_FLAG = 'N')
AND (bit = 3
OR
NVL(S.BOM_ITEM_TYPE, 4) <> 2
OR
(S.BOM_ITEM_TYPE = 2
AND ((pto = 'Y'
AND S.PICK_COMPONENTS_FLAG = 'Y')
OR (ato = 'Y'
AND S.REPLENISH_TO_ORDER_FLAG = 'Y'))))
AND NOT(bit = 4
AND pto = 'Y'
AND S.BOM_ITEM_TYPE = 4
AND S.REPLENISH_TO_ORDER_FLAG = 'Y')
);
select count(*)
into cnt
from bom_inventory_comps_interface bic
where bill_sequence_id = cmn_bom_id
and process_flag in (2, 4)
and NOT EXISTS
(SELECT 'x'
FROM MTL_SYSTEM_ITEMS S
WHERE S.ORGANIZATION_ID = org_id
AND S.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
AND ((bom_type = 1
AND S.ENG_ITEM_FLAG = 'N')
OR (bom_type = 2))
AND S.BOM_ENABLED_FLAG = 'Y'
AND S.INVENTORY_ITEM_ID <> item_id
AND ((bit = 1
AND S.BOM_ITEM_TYPE <> 3)
OR (bit = 2
AND S.BOM_ITEM_TYPE <> 3)
OR (bit = 3)
OR (bit = 4
AND (S.BOM_ITEM_TYPE = 4
OR (S.BOM_ITEM_TYPE IN (2, 1)
AND S.REPLENISH_TO_ORDER_FLAG = 'Y'
AND base_id IS NOT NULL
AND ato = 'Y')))
)
AND (bit = 3
OR
pto = 'Y'
OR
S.PICK_COMPONENTS_FLAG = 'N')
AND (bit = 3
OR
NVL(S.BOM_ITEM_TYPE, 4) <> 2
OR
(S.BOM_ITEM_TYPE = 2
AND ((pto = 'Y'
AND S.PICK_COMPONENTS_FLAG = 'Y')
OR (ato = 'Y'
AND S.REPLENISH_TO_ORDER_FLAG = 'Y'))))
AND NOT(bit = 4
AND pto = 'Y'
AND S.BOM_ITEM_TYPE = 4
AND S.REPLENISH_TO_ORDER_FLAG = 'Y')
);
select count(*)
into cnt
from bom_inventory_components bic,
bom_substitute_components bsc
where bic.bill_sequence_id = cmn_bom_id
and bic.component_sequence_id = bsc.component_sequence_id
and bsc.substitute_component_id not in
(select msi1.inventory_item_id
from mtl_system_items msi1, mtl_system_items msi2
where msi1.organization_id = org_id
and msi1.inventory_item_id = bsc.substitute_component_id
and msi2.organization_id = cmn_org_id
and msi2.inventory_item_id = msi1.inventory_item_id);
select count(*) /* Comp and sub comp in interface */
into cnt
from bom_inventory_comps_interface bic,
bom_sub_comps_interface bsc
where bic.bill_sequence_id = cmn_bom_id
and bic.process_flag in (2, 4)
and bsc.process_flag in (2, 4)
and bic.component_sequence_id = bsc.component_sequence_id
and bsc.substitute_component_id not in
(select msi1.inventory_item_id
from mtl_system_items msi1, mtl_system_items msi2
where msi1.organization_id = org_id
and msi1.inventory_item_id = bsc.substitute_component_id
and msi2.organization_id = cmn_org_id
and msi2.inventory_item_id = msi1.inventory_item_id);
select count(*) /* Comp in production and sub comp in interface */
into cnt
from bom_inventory_components bic,
bom_sub_comps_interface bsc
where bic.bill_sequence_id = cmn_bom_id
and bsc.process_flag in (2, 4)
and bic.component_sequence_id = bsc.component_sequence_id
and bsc.substitute_component_id not in
(select msi1.inventory_item_id
from mtl_system_items msi1, mtl_system_items msi2
where msi1.organization_id = org_id
and msi1.inventory_item_id = bsc.substitute_component_id
and msi2.organization_id = cmn_org_id
and msi2.inventory_item_id = msi1.inventory_item_id);
select 1
into cnt
from mtl_system_items msi1, mtl_system_items msi2
where msi1.organization_id = org_id
and msi1.inventory_item_id = item_id
and msi2.organization_id = cmn_org_id
and msi2.inventory_item_id = cmn_item_id
and msi2.bom_enabled_flag = 'Y'
and msi1.bom_item_type = msi2.bom_item_type
and msi1.pick_components_flag = msi2.pick_components_flag
and msi1.replenish_to_order_flag = msi2.replenish_to_order_flag;
update_comp exception;
select component_sequence_id CSI, bill_sequence_id BSI,
transaction_id TI,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED,
effectivity_date EDD,
to_char(disable_date,'YYYY/MM/DD HH24:MI') DD,
to_char(implementation_date,'YYYY/MM/DD HH24:MI') ID,
operation_seq_num OSN, supply_locator_id SLI,
supply_subinventory SS,
msic.organization_id OI, component_item_id CII,
assembly_item_id AII, alternate_bom_designator ABD,
planning_factor PF, optional O, check_atp CATP,
msic.atp_flag AF, so_basis SB, required_for_revenue RFR,
required_to_ship RTS, mutually_exclusive_options MEO,
low_quantity LQ, high_quantity HQ,
quantity_related QR, include_in_cost_rollup ICR,
shipping_allowed SA, include_on_ship_docs ISD,
component_yield_factor CYF, ici.wip_supply_type WST,
component_quantity CQ, msic.bom_item_type BITC,
msic.pick_components_flag PCF, msia.bom_item_type BITA,
msia.pick_components_flag PCFA,
msia.replenish_to_order_flag RTOF,
msic.replenish_to_order_flag RTOFC,
msia.atp_components_flag ACF,
msic.ato_forecast_control AFC
from mtl_system_items msic,
mtl_system_items msia,
bom_inventory_comps_interface ici
where process_flag = 2
and msic.organization_id = ici.organization_id
and msia.organization_id = ici.organization_id
and msic.inventory_item_id = ici.component_item_id
and msia.inventory_item_id = ici.assembly_item_id;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select 'Is pointing to a common'
into dummy
from bom_bill_of_materials
where bill_sequence_id = c1rec.BSI
and common_bill_sequence_id <> c1rec.BSI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select 'Is pointing to a common'
into dummy
from bom_bill_of_mtls_interface
where bill_sequence_id = c1rec.BSI
and process_flag = 4
and common_bill_sequence_id <> c1rec.BSI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select assembly_type
from bom_bill_of_materials
where bill_sequence_id = c1rec.BSI;
select assembly_type
from bom_bill_of_mtls_interface
where bill_sequence_id = c1rec.BSI
and process_flag = 4;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface
set component_yield_factor = 1,
check_atp = 2,
include_on_ship_docs = 2,
so_basis = 2,
mutually_exclusive_options = 2,
required_to_ship = 2,
required_for_revenue = 2,
low_quantity = NULL,
high_quantity = NULL
where transaction_id = c1rec.TI;
select distinct 'I'
into oe_install
from fnd_product_installations
where application_id = 300
and status = 'I';
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
raise update_comp;
select inventory_asset_flag,restrict_subinventories_code,
restrict_locators_code, location_control_code
into inv_asst, r_subinv, r_loc, loc_ctl
from mtl_system_items
where inventory_item_id = c1rec.CII
and organization_id = c1rec.OI;
select locator_type
into sub_loc_code
from mtl_secondary_inventories
where secondary_inventory_name = c1rec.SS
and organization_id = c1rec.OI
and nvl(disable_date,TRUNC(c1rec.EDD)+1) > TRUNC(c1rec.EDD)
and quantity_tracked = 1;
select locator_type
into sub_loc_code
from mtl_secondary_inventories
where secondary_inventory_name = c1rec.SS
and organization_id = c1rec.OI
and nvl(disable_date,TRUNC(c1rec.EDD)+1) > TRUNC(c1rec.EDD)
and quantity_tracked = 1
and ((inv_asst = 'Y' and asset_inventory = 1)
or
(inv_asst = 'N')
);
select locator_type
into sub_loc_code
from mtl_secondary_inventories sub,
mtl_item_sub_inventories item
where item.organization_id = sub.organization_id
and item.secondary_inventory = sub.secondary_inventory_name
and item.inventory_item_id = c1rec.CII
and sub.secondary_inventory_name = c1rec.SS
and sub.organization_id = c1rec.OI
and nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
TRUNC(c1rec.EDD)
and sub.quantity_tracked = 1;
select locator_type
into sub_loc_code
from mtl_secondary_inventories sub,
mtl_item_sub_inventories item
where item.organization_id = sub.organization_id
and item.secondary_inventory = sub.secondary_inventory_name
and item.inventory_item_id = c1rec.CII
and sub.secondary_inventory_name = c1rec.SS
and sub.organization_id = c1rec.OI
and nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
TRUNC(c1rec.EDD)
and sub.quantity_tracked = 1
and ((inv_asst = 'Y' and sub.asset_inventory = 1)
or
(inv_asst = 'N')
);
select stock_locator_control_code
into org_loc
from mtl_parameters
where organization_id = c1rec.OI;
select 'loc exists'
into dummy
from mtl_item_locations
where inventory_location_id = c1rec.SLI
and organization_id = c1rec.OI
and subinventory_code = c1rec.SS
and nvl(disable_date,trunc(c1rec.EDD)+1) > trunc(c1rec.EDD);
select 'restricted loc exists'
into dummy
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = c1rec.SLI
and loc.organization_id = c1rec.OI
and loc.subinventory_code = c1rec.SS
and nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = c1rec.CII;
select 'loc exists'
into dummy
from mtl_item_locations
where inventory_location_id = c1rec.SLI
and organization_id = c1rec.OI
and subinventory_code = c1rec.SS
and nvl(disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD);
select 'restricted loc exists'
into dummy
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = c1rec.SLI
and loc.organization_id = c1rec.OI
and loc.subinventory_code = c1rec.SS
and nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = c1rec.CII;
select 'loc exists'
into dummy
from mtl_item_locations
where inventory_location_id = c1rec.SLI
and organization_id = c1rec.OI
and subinventory_code = c1rec.SS
and nvl(disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD);
select 'restricted loc exists'
into dummy
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = c1rec.SLI
and loc.organization_id = c1rec.OI
and loc.subinventory_code = c1rec.SS
and nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = c1rec.CII;
raise update_comp;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
when update_comp then
update bom_inventory_comps_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select 1
into dummy
from mtl_system_items assy, mtl_system_items comp
where comp.organization_id = org_id
and assy.organization_id = org_id
and comp.inventory_item_id = cmp_id
and assy.inventory_item_id = assy_id
and comp.bom_enabled_flag = 'Y'
and comp.inventory_item_id <> assy.inventory_item_id
and ((eng_bill = 1 and comp.eng_item_flag = 'N')
or
(eng_bill = 2))
and ((assy.bom_item_type = 1 and comp.bom_item_type <> 3)
or
(assy.bom_item_type = 2 and comp.bom_item_type <> 3)
or
(assy.bom_item_type = 3)
or
(assy.bom_item_type = 4
and (comp.bom_item_type = 4
or (comp.bom_item_type in (2,1)
and comp.replenish_to_order_flag = 'Y'
and assy.base_item_id is not null
and assy.replenish_to_order_flag = 'Y')))
)
and (assy.bom_item_type = 3
or
assy.pick_components_flag = 'Y'
or
comp.pick_components_flag = 'N')
and (assy.bom_item_type = 3
or
comp.bom_item_type <> 2
or
(comp.bom_item_type = 2
and ((assy.pick_components_flag = 'Y'
and comp.pick_components_flag = 'Y')
or (assy.replenish_to_order_flag = 'Y'
and comp.replenish_to_order_flag = 'Y'))))
and not(assy.bom_item_type = 4
and assy.pick_components_flag = 'Y'
and comp.bom_item_type = 4
and comp.replenish_to_order_flag = 'Y');
select 1
into dummy
from mtl_system_items assy, mtl_system_items comp
where comp.organization_id = org_id
and assy.organization_id = org_id
and comp.inventory_item_id = cmp_id
and assy.inventory_item_id = assy_id
and (comp.atp_components_flag = 'Y' or
comp.atp_flag = 'Y')
and assy.atp_components_flag = 'N'
and (nvl(assy.wip_supply_type,1) = 6
or assy.replenish_to_order_flag = 'Y'
or assy.pick_components_flag = 'Y');
select bom_item_type
into dummy
from mtl_system_items
where organization_id = org_id
and inventory_item_id = assy_id;
select operation_seq_num
into dummy
from bom_operation_sequences a, bom_operational_routings b
where b.organization_id = org_id
and b.assembly_item_id = assy_id
and operation_seq_num = op_seq
and b.common_routing_sequence_id = a.routing_sequence_id
and ( (alt_desg is null and b.alternate_routing_designator is null)
or
(alt_desg is not null
and
( (b.alternate_routing_designator = alt_desg)
or
(b.alternate_routing_designator is null
and not exists (select 'No alt routing'
from bom_operational_routings c
where c.organization_id = org_id
and c.assembly_item_id = assy_id
and c.alternate_routing_designator = alt_desg)
)
)
)
);
select count(*)
into dummy
from bom_inventory_components
where bill_sequence_id = bom_id
and component_item_id = cmp_id
and operation_seq_num = op_num
and implementation_date is not null
and ((dis_date is null
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
or
(dis_date is not null
and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
);
select count(*)
into dummy
from bom_inventory_comps_interface
where bill_sequence_id = bom_id
and process_flag = 4
and component_item_id = cmp_id
and operation_seq_num = op_num
and implementation_date is not null
and ((dis_date is null
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
or
(dis_date is not null
and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
);
select 1
into dummy
from bom_inventory_components
where component_sequence_id = cmp_seq_id;
select count(*)
into dummy
from bom_inventory_comps_interface
where component_sequence_id = cmp_seq_id
and process_flag = 4;
select component_sequence_id
into cnt
from bom_inventory_components
where bill_sequence_id = bill_seq_id
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and component_item_id = cmp_item_id
and operation_seq_num = op_seq;
select component_sequence_id
into cnt
from bom_inventory_comps_interface
where bill_sequence_id = bill_seq_id
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and component_item_id = cmp_item_id
and operation_seq_num = op_seq
and rownum = 1
and process_flag = 4;
select component_sequence_id CSI, count(*) CNT,
transaction_id TI, component_item_id CII
from bom_ref_desgs_interface
where process_flag = 2
and rownum < 500
group by transaction_id, component_sequence_id, component_item_id;
select count(distinct component_sequence_id)
into total_recs
from bom_ref_desgs_interface
where process_flag = 2;
select count(*)
into dummy
from bom_ref_desgs_interface
where transaction_id = c1rec.TI
and component_reference_designator is null;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select bbom.organization_id,
bbom.assembly_item_id, bic.bom_item_type
into org_id_dummy, assy_id_dummy, comp_type
from bom_inventory_components bic,
bom_bill_of_materials bbom
where component_sequence_id = c1rec.CSI
and bbom.bill_sequence_id = bic.bill_sequence_id;
select bbom.organization_id,
bbom.assembly_item_id, bic.bom_item_type
into org_id_dummy, assy_id_dummy, comp_type
from bom_inventory_comps_interface bic,
bom_bill_of_mtls_interface bbom
where component_sequence_id = c1rec.CSI
and bic.process_flag = 4
and bbom.process_flag = 4
and bbom.bill_sequence_id = bic.bill_sequence_id;
select bbom.organization_id,
bbom.assembly_item_id, bic.bom_item_type
into org_id_dummy, assy_id_dummy, comp_type
from bom_inventory_comps_interface bic,
bom_bill_of_materials bbom
where component_sequence_id = c1rec.CSI
and bic.process_flag = 4
and bbom.bill_sequence_id = bic.bill_sequence_id;
select bom_item_type
into dummy
from mtl_system_items msi
where msi.organization_id = org_id_dummy
and msi.inventory_item_id = assy_id_dummy;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select 1
into dummy
from bom_reference_designators a, bom_ref_desgs_interface b
where b.transaction_id = trans_id
and a.component_sequence_id = b.component_sequence_id
and a.COMPONENT_REFERENCE_DESIGNATOR =
b.COMPONENT_REFERENCE_DESIGNATOR
and rownum = 1;
select count(*)
into dummy
from bom_ref_desgs_interface a
where transaction_id = trans_id
and exists (select 'same designator'
from bom_ref_desgs_interface b
where b.transaction_id = trans_id
and b.rowid <> a.rowid
and b.COMPONENT_REFERENCE_DESIGNATOR =
a.COMPONENT_REFERENCE_DESIGNATOR
and b.process_flag <> 3
and b.process_flag <> 7)
and process_flag <> 3
and process_flag <> 7;
select QUANTITY_RELATED, COMPONENT_QUANTITY
into qty_flag, cmp_qty
from bom_inventory_components
where component_sequence_id = cmp_seq_id;
** if no rows selected from prod table, then get from interface table
*/
if (qty_flag <> 1 and qty_flag <> 2) then
select QUANTITY_RELATED, COMPONENT_QUANTITY
into qty_flag, cmp_qty
from bom_inventory_comps_interface
where component_sequence_id = cmp_seq_id
and process_flag = 4;
select count(*)
into ref_qty
from bom_reference_designators
where component_sequencE_id = cmp_seq_id;
select count(*)
into int_ref_qty
from bom_ref_desgs_interface
where transaction_id = trans_id
and process_flag <> 3
and process_flag <> 7;
select component_sequence_id CSI, count(*) CNT,
transaction_id TI, assembly_item_id AII,
organization_id OI
from bom_sub_comps_interface
where process_flag = 2
and rownum < 500
group by transaction_id, component_sequence_id,
organization_id, assembly_item_id;
select count(distinct component_sequence_id)
into total_recs
from bom_sub_comps_interface
where process_flag = 2;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select bbom.assembly_item_id,bbom.organization_id,
bbom.assembly_type, bic.component_item_id,
bic.bom_item_type
into assy_id_dummy, org_id_dummy,assy_type_dummy,
comp_id_dummy, comp_type
from bom_inventory_components bic,
bom_bill_of_materials bbom
where component_sequence_id = c1rec.CSI
and bbom.bill_sequence_id = bic.bill_sequence_id;
select bbom.assembly_item_id,bbom.organization_id,
bbom.assembly_type, bic.component_item_id,
bic.bom_item_type
into assy_id_dummy, org_id_dummy,assy_type_dummy,
comp_id_dummy, comp_type
from bom_inventory_comps_interface bic,
bom_bill_of_mtls_interface bbom
where component_sequence_id = c1rec.CSI
and bic.process_flag = 4
and bbom.process_flag = 4
and bbom.bill_sequence_id = bic.bill_sequence_id;
select bbom.assembly_item_id,bbom.organization_id,
bbom.assembly_type, bic.component_item_id,
bic.bom_item_type
into assy_id_dummy, org_id_dummy,assy_type_dummy,
comp_id_dummy, comp_type
from bom_inventory_comps_interface bic,
bom_bill_of_materials bbom
where component_sequence_id = c1rec.CSI
and bic.process_flag = 4
and bbom.bill_sequence_id = bic.bill_sequence_id;
select bom_item_type
into dummy
from mtl_system_items msi
where msi.organization_id = org_id_dummy
and msi.inventory_item_id = assy_id_dummy;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into dummy
from bom_sub_comps_interface a
where transaction_id = c1rec.TI
and process_flag <> 3 and process_flag <> 7
and not exists (select 'items exist'
from mtl_system_items b
where b.organization_id = org_id_dummy
and b.inventory_item_id = a.substitute_component_id);
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into dummy
from bom_sub_comps_interface
where transaction_id = c1rec.TI
and (SUBSTITUTE_COMPONENT_ID = assy_id_dummy
or
SUBSTITUTE_COMPONENT_ID = comp_id_dummy)
and process_flag <> 3 and process_flag <> 7;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into dummy
from bom_sub_comps_interface bsc
where bsc.transaction_id = c1rec.TI
and not exists (select 'x'
from mtl_system_items msi
where organization_id = org_id_dummy
and inventory_item_id = bsc.substitute_component_id
and bom_enabled_flag = 'Y'
and bom_item_type = 4
and ((assy_type_dummy = 2)
or
(assy_type_dummy = 1
and eng_item_flag = 'N')));
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into dummy
from bom_sub_comps_interface
where transaction_id = c1rec.TI
and process_flag <> 3 and process_flag <> 7
and substitute_item_quantity = 0;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select 1
into dummy
from bom_substitute_components a, bom_sub_comps_interface b
where b.transaction_id = trans_id
and a.component_sequence_id = b.component_sequence_id
and a.SUBSTITUTE_COMPONENT_ID =
b.SUBSTITUTE_COMPONENT_ID
and rownum = 1;
select count(*)
into dummy
from bom_sub_comps_interface a
where transaction_id = trans_id
and exists (select 'same substitue'
from bom_sub_comps_interface b
where b.transaction_id = trans_id
and b.rowid <> a.rowid
and b.SUBSTITUTE_COMPONENT_ID =
a.SUBSTITUTE_COMPONENT_ID
and b.process_flag <> 3
and b.process_flag <> 7)
and process_flag <> 3
and process_flag <> 7;
select inventory_item_id AII, organization_id OI,
revision R, transaction_id TI
from mtl_item_revisions_interface
where process_flag = 2
and rownum < 500;
select inventory_item_id AII, organization_id OI
from mtl_item_revisions_interface
where process_flag = 99
and rownum < 500
group by organization_id, inventory_item_id;
select 'x'
from mtl_item_revisions_interface
where process_flag = 99
group by organization_id, inventory_item_id;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c0rec.TI;
select organization_id
into dummy_id
from mtl_parameters
where organization_id = c0rec.OI;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c0rec.TI;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c0rec.TI;
select count(*)
into dummy_bill
from bom_bill_of_materials
where organization_id = c0rec.OI
and assembly_item_id = c0rec.AII;
select count(*)
into dummy_bill
from bom_bill_of_mtls_interface
where process_flag = 4
and organization_id = c0rec.OI
and assembly_item_id = c0rec.AII;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c0rec.TI;
update mtl_item_revisions_interface set
process_flag = 99
where transaction_id = c0rec.TI;
select revision R, effectivity_date ED,
transaction_id TI
from mtl_item_revisions_interface
where organization_id = org_id
and inventory_item_id = assy_id
and process_flag = 99;
select count(*)
into err_cnt
from mtl_item_revisions_interface a
where transaction_id <> c1rec.TI
and inventory_item_id = assy_id
and organization_id = org_id
and process_flag = 4
and ( (revision = c1rec.R)
or
(effectivity_date > c1rec.ED
and revision < c1rec.R)
or
(effectivity_date < c1rec.ED
and revision > c1rec.R)
);
select count(*)
into err_cnt
from mtl_item_revisions
where inventory_item_id = assy_id
and organization_id = org_id
and ( (revision = c1rec.R)
or
(effectivity_date > c1rec.ED
and revision < c1rec.R)
or
(effectivity_date < c1rec.ED
and revision > c1rec.R)
);
update mtl_item_revisions_interface set
process_flag = 4
where transaction_id = c1rec.TI;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c1rec.TI;