[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO BOM_IMPLOSION_TEMP
( SEQUENCE_ID,
LOWEST_ITEM_ID,
CURRENT_ITEM_ID,
PARENT_ITEM_ID,
ALTERNATE_DESIGNATOR,
CURRENT_LEVEL,
SORT_CODE,
CURRENT_ASSEMBLY_TYPE,
COMPONENT_SEQUENCE_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (sequence_id,
item_id,
item_id,
item_id,
NULL,
0,
-- '0000001',
Bom_Common_Definitions.G_Bom_Init_SortCode,
NULL,
NULL,
org_id,
sysdate,
-1,
sysdate,
-1);
SELECT /*+ ordered first_rows */
BITT.LOWEST_ITEM_ID LID,
BITT.PARENT_ITEM_ID PID,
BBM.ASSEMBLY_ITEM_ID AID,
BBM.ALTERNATE_BOM_DESIGNATOR ABD,
BITT.SORT_CODE SC,
BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
BBM.ASSEMBLY_TYPE CAT,
BIC.COMPONENT_SEQUENCE_ID CSI,
BIC.OPERATION_SEQ_NUM OSN,
BIC.EFFECTIVITY_DATE ED,
BIC.DISABLE_DATE DD,
BIC.BASIS_TYPE BT,
BIC.COMPONENT_QUANTITY CQ,
BIC.REVISED_ITEM_SEQUENCE_ID RISD,
BIC.CHANGE_NOTICE CN,
DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
BBM.ORGANIZATION_ID OI
FROM
BOM_IMPLOSION_TEMP BITT,
BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BBM
where bitt.current_level = 0
and bitt.organization_id = c_org_id
and bitt.sequence_id = c_sequence_id
and bitt.parent_item_id = bic.component_item_id
and bic.bill_sequence_id = bbm.common_bill_sequence_id
and bbm.organization_id = c_org_id
and NVL(bic.ECO_FOR_PRODUCTION,2) = 2
and (
( c_eng_mfg_flag = 1
and bbm.assembly_type = 1
) /* get only Mfg boms */
or
(c_eng_mfg_flag = 2
) /*both Mfg-Eng BOMs in ENG mode*/
) /* end of entire and predicate */
and (
(nvl(bbm.alternate_bom_designator,'none') = /*Pickup match par*/
nvl(bitt.lowest_alternate_designator,'none')
)
or /* Pickup par with spec alt only, if start alt is null,*/
( bitt.lowest_alternate_designator is null /*and bill with spec*/
and bbm.alternate_bom_designator is not null
/* alt doesnt exist */
and not exists (select NULL /*for current item */
from bom_bill_of_materials bbm2
where bbm2.organization_id = c_org_id
and bbm2.assembly_item_id = bitt.parent_item_id
and bbm2.alternate_bom_designator =
bbm.alternate_bom_designator
and (
(bitt.current_assembly_type = 1
and bbm2.assembly_type = 1)
or
(bitt.current_assembly_type = 2)
)
) /* end of subquery */
) /* end of parent with specific alt */
or /* Pickup prim par only if start alt is not null and bill 4*/
( bitt.lowest_alternate_designator is not null
/* same par doesnt */
and bbm.alternate_bom_designator is null
/* exist with this alt */
and not exists (select NULL
from bom_bill_of_materials bbm2
where bbm2.organization_id = c_org_id
and bbm2.assembly_item_id = bbm.assembly_item_id
and bbm2.alternate_bom_designator =
bitt.lowest_alternate_designator
and (
(bitt.current_assembly_type = 1
and bbm2.assembly_type = 1)
or
(bitt.current_assembly_type = 2)
)
) /* end of subquery */
) /* end of parent with null alt */
)/* end of all alternate logic */
and ( /* start of all display options */
( c_display_option = 2
and bic.effectivity_date
<= to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
and ( bic.disable_date is null
or bic.disable_date >
to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
)
) /* end of CURRENT */
or
c_display_option = 1
or
( c_display_option = 3
and ( bic.disable_date is null
or bic.disable_date >
to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
)
) /* end of CURRENT_AND_FUTURE */
) /* end of all display options */
and ( /* start of implemented yes/no logic */
( c_implemented_only_option = 1
and bic.implementation_date is not null
)
or
( c_implemented_only_option = 2
and ( /* start of all display */
( c_display_option = 2
and
bic.effectivity_date =
(select max(effectivity_date)
from bom_inventory_components bic2
where bic2.bill_sequence_id = bic.bill_sequence_id
and bic2.component_item_id = bic.component_item_id
and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
and decode(bic.implementation_date, NULL,
bic.old_component_sequence_id,
bic.component_sequence_id) =
decode(bic2.implementation_date, NULL,
bic2.old_component_sequence_id,
bic2.component_sequence_id)
and bic2.effectivity_date <=
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
--* AND Clause added for Bug 3085543
and NOT EXISTS (SELECT null
FROM bom_inventory_components bic3
WHERE bic3.bill_sequence_id =
bic.bill_sequence_id
AND bic3.old_component_sequence_id =
bic.component_sequence_id
AND NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
AND bic3.acd_type in (2,3)
AND bic3.disable_date <=
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS'))
--* End of Bug 3085543
and ( bic2.disable_date >
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
or bic2.disable_date is null )
) /* end of subquery */
) /* end of CURRENT */
or
( c_display_option = 3
and bic.effectivity_date =
(select max(effectivity_date)
from bom_inventory_components bic2
where bic2.bill_sequence_id = bic.bill_sequence_id
and bic2.component_item_id = bic.component_item_id
and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
and nvl(bic2.old_component_sequence_id,
bic2.component_sequence_id) =
nvl(bic.old_component_sequence_id,
bic.component_sequence_id)
and bic2.effectivity_date <=
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
--* AND Clause added for Bug - 3155946
AND NOT EXISTS ( SELECT Null
FROM Bom_Inventory_Components bic4
WHERE bic4.bill_sequence_id =
bic.bill_sequence_id
AND bic4.old_component_sequence_id =
bic.component_sequence_id
AND Nvl(bic4.eco_for_production,2) = 2
AND bic4.acd_type in (2,3)
AND bic4.disable_date <=
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS') )
--* End of Bug - 3155946
and ( bic2.disable_date >
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
or bic2.disable_date is null )
) /* end of subquery */
or
bic.effectivity_date > to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
) /* end of current and future */
or
( c_display_option = 1)
) /* end of all display */
) /* end of impl = no */
) /* end of impl = yes-no */
order by bitt.parent_item_id,
bbm.assembly_item_id, bic.operation_seq_num;
Select 1 dummy
From mtl_system_items msi1,
mtl_system_items msi2
Where msi1.inventory_item_id = P_Parent_Item
And msi1.organization_id = org_id
And msi2.inventory_item_id = P_Comp_Item
And msi2.organization_id = org_id
And msi1.bom_item_type = 4 -- Standard
And msi1.replenish_to_order_flag = 'Y'
And msi1.base_item_id is not null -- configured item
And msi2.bom_item_type in (1, 2); -- model or option class
Select 1 dummy
From mtl_system_items msi
Where msi.inventory_item_id = P_Parent_Item
And msi.organization_id = org_id
And msi.bom_enabled_flag = 'N';
INSERT INTO BOM_IMPLOSION_TEMP
(LOWEST_ITEM_ID,
CURRENT_ITEM_ID,
PARENT_ITEM_ID,
ALTERNATE_DESIGNATOR,
CURRENT_LEVEL,
SORT_CODE,
LOWEST_ALTERNATE_DESIGNATOR,
CURRENT_ASSEMBLY_TYPE,
SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
DISABLE_DATE,
BASIS_TYPE,
COMPONENT_QUANTITY,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
IMPLEMENTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY) VALUES (
impl_row.LID,
impl_row.PID,
impl_row.AID,
impl_row.ABD,
1,
impl_row.SC,
impl_row.LAD,
impl_row.CAT,
sequence_id,
impl_row.CSI,
impl_row.OI,
impl_row.OSN,
impl_row.ED,
impl_row.DD,
impl_row.BT,
impl_row.CQ,
impl_row.RISD,
impl_row.CN,
impl_row.IMPF,
sysdate,
-1,
sysdate,
-1);
SELECT /*+ ordered first_rows */
BITT.LOWEST_ITEM_ID LID,
BITT.PARENT_ITEM_ID PID,
BBM.ASSEMBLY_ITEM_ID AID,
BBM.ALTERNATE_BOM_DESIGNATOR ABD,
BITT.SORT_CODE SC,
BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
BBM.ASSEMBLY_TYPE CAT,
BIC.COMPONENT_SEQUENCE_ID CSI,
BIC.OPERATION_SEQ_NUM OSN,
BIC.EFFECTIVITY_DATE ED,
BIC.DISABLE_DATE DD,
BIC.BASIS_TYPE BT,
BIC.COMPONENT_QUANTITY CQ,
BIC.REVISED_ITEM_SEQUENCE_ID RISD,
BIC.CHANGE_NOTICE CN,
DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
BBM.ORGANIZATION_ID OI
FROM
BOM_IMPLOSION_TEMP BITT,
BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BBM
where
bitt.current_level = c_current_level
and bitt.organization_id = c_org_id
and bitt.sequence_id = c_sequence_id
and bitt.parent_item_id = bic.component_item_id
and bic.bill_sequence_id = bbm.common_bill_sequence_id
and bbm.organization_id = c_org_id
and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
and (
( c_current_level = 0
and
( (c_eng_mfg_flag = 1
and bbm.assembly_type = 1
) /* get only Mfg boms */
or
(c_eng_mfg_flag = 2
) /*both Mfg-Eng BOMs in ENG mode*/
) /* eng or mfg */
) /* end of current_level = 0 */
or
( c_current_level <> 0
and
( (bitt.current_assembly_type = 1
and bbm.assembly_type = 1
and c_eng_mfg_flag = 1
)
or
(c_eng_mfg_flag = 2
)
) /* eng or mfg */
) /* end of current level <> 0 */
) /* end of entire and predicate */
and ( c_current_level = 0
or /* start of all alternate logic */
( nvl(bbm.alternate_bom_designator,'none') =
nvl(bitt.lowest_alternate_designator,'none')
)
or /* Pickup par with spec alt only, if start alt is null,*/
( bitt.lowest_alternate_designator is null
and bbm.alternate_bom_designator is not null
/* alt doesnt exist */
and not exists (select NULL /*for current item */
from bom_bill_of_materials bbm2
where bbm2.organization_id = c_org_id
and bbm2.assembly_item_id = bitt.parent_item_id
and bbm2.alternate_bom_designator =
bbm.alternate_bom_designator
and (
(bitt.current_assembly_type = 1
and bbm2.assembly_type = 1
and c_eng_mfg_flag = 1)
or
(c_eng_mfg_flag = 2)
)
) /* end of subquery */
) /* end of parent with specific alt */
or /* Pickup prim par only if starting alt is not
null and bill for .. */
(bitt.lowest_alternate_designator is not null
/* .. same par doesnt */
and bbm.alternate_bom_designator is null
/* .. exist with this alt */
and not exists (select NULL
from bom_bill_of_materials bbm2
where bbm2.organization_id = c_org_id
and bbm2.assembly_item_id = bbm.assembly_item_id
and bbm2.alternate_bom_designator =
bitt.lowest_alternate_designator
and (
(bitt.current_assembly_type = 1
and bbm2.assembly_type = 1
and c_eng_mfg_flag = 1)
or
(c_eng_mfg_flag = 2)
)
) /* end of subquery */
) /* end of parent with null alt */
)/* end of all alternate logic */
and bic.effectivity_date <= to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
and ( bic.disable_date is null
or
bic.disable_date > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
)
and ( /* start of implemented yes-no logic */
( c_implemented_only_option = 1
and bic.implementation_date is not null
)
or
( c_implemented_only_option = 2
and bic.effectivity_date =
(select max(effectivity_date)
from bom_inventory_components bic2
where bic.bill_sequence_id = bic2.bill_sequence_id
and bic.component_item_id = bic2.component_item_id
and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
and decode(bic.implementation_date, NULL,
bic.old_component_sequence_id,
bic.component_sequence_id) =
decode(bic2.implementation_date, NULL,
bic2.old_component_sequence_id,
bic2.component_sequence_id)
and bic2.effectivity_date <= to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
--* AND Clause added for Bug 3085543
and NOT EXISTS (SELECT null
FROM bom_inventory_components bic3
WHERE bic3.bill_sequence_id =
bic.bill_sequence_id
AND bic3.old_component_sequence_id =
bic.component_sequence_id
and NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
AND bic3.acd_type in (2,3)
AND bic3.disable_date <= to_date(c_implosion_date,'YYYY/MM/DD HH24:MI:SS'))
--* End of Bug 3085543
and ( bic2.disable_date is null
or
( bic2.disable_date is not null
and bic2.disable_date >
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI:SS')
)
)
) /* end of select (max) */
) /* end of impl_only = no */
) /* end of implemented yes-no logic */
order by bitt.parent_item_id,
bbm.assembly_item_id, bic.operation_seq_num;
Select 1 dummy
From mtl_system_items msi1,
mtl_system_items msi2
Where msi1.inventory_item_id = P_Parent_Item
And msi1.organization_id = org_id
And msi2.inventory_item_id = P_Comp_Item
And msi2.organization_id = org_id
And msi1.bom_item_type = 4 -- Standard
And msi1.replenish_to_order_flag = 'Y'
And msi1.base_item_id is not null -- configured item
And msi2.bom_item_type in (1, 2); -- model or option class
Select 1 dummy
From mtl_system_items msi
Where msi.inventory_item_id = P_Parent_Item
And msi.organization_id = org_id
And msi.bom_enabled_flag = 'N';
SELECT max(MAXIMUM_BOM_LEVEL)
INTO max_level
FROM BOM_PARAMETERS
WHERE ORGANIZATION_ID = org_id;
INSERT INTO BOM_IMPLOSION_TEMP
(LOWEST_ITEM_ID,
CURRENT_ITEM_ID,
PARENT_ITEM_ID,
ALTERNATE_DESIGNATOR,
CURRENT_LEVEL,
SORT_CODE,
LOWEST_ALTERNATE_DESIGNATOR,
CURRENT_ASSEMBLY_TYPE,
SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
ORGANIZATION_ID,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
DISABLE_DATE,
BASIS_TYPE,
COMPONENT_QUANTITY,
IMPLEMENTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY) VALUES (
impl_row.LID,
impl_row.PID,
impl_row.AID,
impl_row.ABD,
cur_level + 1,
impl_row.SC,
impl_row.LAD,
impl_row.CAT,
sequence_id,
impl_row.CSI,
impl_row.OI,
impl_row.RISD,
impl_row.CN,
impl_row.OSN,
impl_row.ED,
impl_row.DD,
impl_row.BT,
impl_row.CQ,
impl_row.IMPF,
sysdate,
-1,
sysdate,
-1);