[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MASTER_ORGANIZATION_ID L_MASTER_ORG
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = c_organization_id;
/* SELECT organization_name into l_org_name
FROM org_organization_definitions
WHERE organization_id = org_id;
for C1 in ( SELECT orgs.ORGANIZATION_ID
FROM ORG_ACCESS_VIEW oav, MTL_SYSTEM_ITEMS_B msi,
MTL_PARAMETERS orgs, MTL_PARAMETERS child_org
WHERE orgs.ORGANIZATION_ID = oav.ORGANIZATION_ID
AND msi.ORGANIZATION_ID = orgs.ORGANIZATION_ID
AND orgs.MASTER_ORGANIZATION_ID = child_org.MASTER_ORGANIZATION_ID
AND oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
AND oav.RESP_APPLICATION_ID = FND_PROFILE.value('RESP_APPL_ID')
AND msi.INVENTORY_ITEM_ID = item_id
AND child_org.ORGANIZATION_ID = org_id
)
LOOP
N:=N+1;
select count(*) into dummy from mtl_system_items where
organization_id = t_org_code_list(I) and
inventory_item_id = item_id;
/*INSERT INTO BOM_SMALL_IMPL_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,
implosion_date)
VALUES (sequence_id,
item_id,
item_id,
item_id,
NULL,
0,
-- '0000001',
Bom_Common_Definitions.G_Bom_Init_SortCode,
NULL,
NULL,
t_org_code_list(I),
sysdate,
-1,
sysdate,
-1,
to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
INSERT INTO BOM_SMALL_IMPL_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,
IMPLOSION_DATE)
(
SELECT
sequence_id,
item_id,
item_id,
item_id,
NULL,
0,
--'0001',
Bom_Common_Definitions.G_Bom_Init_SortCode,
NULL,
NULL,
t_org_code_list(I),
sysdate,
-1,
sysdate,
-1,
to_date(impl_date, 'YYYY/MM/DD HH24:MI')
FROM DUAL
WHERE NOT EXISTS
( SELECT 'X'
FROM BOM_SMALL_IMPL_TEMP
WHERE SEQUENCE_ID = sequence_id
AND LOWEST_ITEM_ID = item_id
AND CURRENT_ITEM_ID = item_id
AND PARENT_ITEM_ID = item_id
AND ALTERNATE_DESIGNATOR IS NULL
AND CURRENT_LEVEL = 0
AND SORT_CODE = Bom_Common_Definitions.G_Bom_Init_SortCode
AND CURRENT_ASSEMBLY_TYPE IS NULL
AND COMPONENT_SEQUENCE_ID IS NULL
AND ORGANIZATION_ID = t_org_code_list(I)
)
);
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,
BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
BIC.TO_END_ITEM_UNIT_NUMBER TUN
FROM
BOM_SMALL_IMPL_TEMP BITT,
BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BBM,
MTL_SYSTEM_ITEMS MSI
where bitt.current_level = 0
and bitt.organization_id = c_org_id
and MSI.ORGANIZATION_ID = BBM.ORGANIZATION_ID
and MSI.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_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 ( /* match par alt */
((bbm.alternate_bom_designator is null and
bitt.lowest_alternate_designator is null)
or
(bbm.alternate_bom_designator =
bitt.lowest_alternate_designator))
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 ( /* Effectivity_control */
( msi.effectivity_control =1 -- Date Effectivity
AND
( /* start of all display options */
( c_display_option = 2
and bic.effectivity_date
<= to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
and ( bic.disable_date is null
or bic.disable_date >
to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
)
) /* 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')
)
) /* end of CURRENT_AND_FUTURE */
) /* end of all display options */
) /* msi.effectivity_control =1 */
OR (
msi.effectivity_control =2 -- Unit Number Effectivity
AND nvl(msi.eam_item_type,0) <> 1 -- do not include serial eff EAM items
AND
c_unit_number_from is NOT NULL -- Profile Model/Unit Eff=YES
AND
(c_display_option = 1
OR (c_display_option in (2,3) AND bic.disable_date is null))
AND
BIC.FROM_END_ITEM_UNIT_NUMBER <= c_unit_number_to
AND
NVL(BIC.TO_END_ITEM_UNIT_NUMBER,c_unit_number_from) >= c_unit_number_from
)
OR (
msi.effectivity_control =2 -- Unit Number Effectivity
AND nvl(msi.eam_item_type,0) = 1 -- include only serial eff EAM items
AND
c_serial_number_from is NOT NULL -- Serial Effectivity for EAM items
AND
(c_display_option = 1
OR (c_display_option in (2,3) AND bic.disable_date is null))
AND
BIC.FROM_END_ITEM_UNIT_NUMBER <= c_serial_number_to
AND
NVL(BIC.TO_END_ITEM_UNIT_NUMBER,c_serial_number_from) >= c_serial_number_from
)
) /* end of effectivity control */
and ( /* effectivity_control */
( msi.effectivity_control =1 -- Date Effectivity
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 trunc(bic2.effectivity_date, 'MI') <=
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI')
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'))
and ( bic2.disable_date >
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI')
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')
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'))
and ( bic2.disable_date >
to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI')
or bic2.disable_date is null )
) /* end of subquery */
or
bic.effectivity_date > to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI')
) /* end of current and future */
or
( c_display_option = 1)
) /* end of all display */
) /* end of impl = no */
) /* end of impl = yes-no */
) /* effectivity_control = 1 */
OR /* serial effectivity control */
( MSI.effectivity_control=2 -- Unit Effectivity
AND nvl(msi.eam_item_type,0) <> 1 -- do not include serial eff EAM items
AND
c_unit_number_from is NOT NULL
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 )
)
) /* effectivity_control = 2 */
OR /* serial effectivity control */
( MSI.effectivity_control=2 -- Serial Effectivity for EAM items
AND nvl(msi.eam_item_type,0) = 1 -- include only serial eff EAM items
AND
c_serial_number_from is NOT NULL
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 )
)
) /* effectivity_control = 2 */
) /* effectivity_control*/
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';
l_lid1.delete;
l_pid1.delete;
l_aid1.delete;
l_abd1.delete;
l_sc1.delete;
l_lad1.delete;
l_cat1.delete;
l_csi1.delete;
l_oi1.delete;
l_osn1.delete;
l_ed1.delete;
l_dd1.delete;
l_fun1.delete;
l_tun1.delete;
l_bt1.delete;
l_cq1.delete;
l_risd1.delete;
l_cn1.delete;
l_impf1.delete;
l_lid.delete;
l_pid.delete;
l_aid.delete;
l_abd.delete;
l_sc.delete;
l_lad.delete;
l_cat.delete;
l_csi.delete;
l_oi.delete;
l_osn.delete;
l_ed.delete;
l_dd.delete;
l_fun.delete;
l_tun.delete;
l_bt.delete;
l_cq.delete;
l_risd.delete;
l_cn.delete;
l_impf.delete;
l_lid.delete(i);
l_pid.delete(i);
l_aid.delete(i);
l_abd.delete(i);
l_sc.delete(i);
l_lad.delete(i);
l_cat.delete(i);
l_csi.delete(i);
l_oi.delete(i);
l_osn.delete(i);
l_ed.delete(i);
l_dd.delete(i);
l_fun.delete(i);
l_tun.delete(i);
l_bt.delete(i);
l_cq.delete(i);
l_risd.delete(i);
l_cn.delete(i);
l_impf.delete(i);
/*INSERT INTO BOM_SMALL_IMPL_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,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
COMPONENT_QUANTITY,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
IMPLEMENTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_SORT_CODE,
implosion_date) VALUES (
l_lid1(i),
l_pid1(i),
l_aid1(i),
l_abd1(i),
1,
l_sc1(i),
l_lad1(i),
l_cat1(i),
sequence_id,
l_csi1(i),
l_oi1(i),
l_osn1(i),
l_ed1(i),
l_dd1(i),
l_fun1(i),
l_tun1(i),
l_cq1(i),
l_risd1(i),
l_cn1(i),
l_impf1(i),
sysdate,
-1,
sysdate,
-1,
decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
INSERT INTO BOM_SMALL_IMPL_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,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
BASIS_TYPE,
COMPONENT_QUANTITY,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
IMPLEMENTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_SORT_CODE,
IMPLOSION_DATE)
( SELECT
l_lid1(i),
l_pid1(i),
l_aid1(i),
l_abd1(i),
1,
l_sc1(i),
l_lad1(i),
l_cat1(i),
sequence_id,
l_csi1(i),
l_oi1(i),
l_osn1(i),
l_ed1(i),
l_dd1(i),
l_fun1(i),
l_tun1(i),
l_bt1(i),
l_cq1(i),
l_risd1(i),
l_cn1(i),
l_impf1(i),
sysdate,
-1,
sysdate,
-1,
decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
to_date(impl_date, 'YYYY/MM/DD HH24:MI')
FROM DUAL
WHERE NOT EXISTS
( SELECT 'X'
FROM BOM_SMALL_IMPL_TEMP
WHERE LOWEST_ITEM_ID = l_lid1(i)
AND CURRENT_ITEM_ID = l_pid1(i)
AND PARENT_ITEM_ID = l_aid1(i)
AND ALTERNATE_DESIGNATOR = l_abd1(i)
AND CURRENT_LEVEL = 1
AND SORT_CODE = l_sc1(i)
AND SEQUENCE_ID = sequence_id
AND COMPONENT_SEQUENCE_ID = l_csi1(i)
AND ORGANIZATION_ID = l_oi1(i)
AND PARENT_SORT_CODE = decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7))
)
);
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,
BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
BIC.TO_END_ITEM_UNIT_NUMBER TUN
FROM
BOM_SMALL_IMPL_TEMP BITT,
BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BBM,
MTL_SYSTEM_ITEMS MSI
where
bitt.current_level = c_current_level
and bitt.organization_id = c_org_id
and msi.organization_id = BBM.organization_id
and msi.inventory_item_id = BBM.assembly_item_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 = 2 or c_eng_mfg_flag = 1 and
( c_current_level = 0
and bbm.assembly_type = 1
or c_current_level <> 0 and bitt.current_assembly_type = 1
and bbm.assembly_type = 1))
and ( c_current_level = 0
or /* start of all alternate logic */
bbm.alternate_bom_designator is null and
bitt.lowest_alternate_designator is null
or bbm.alternate_bom_designator = bitt.lowest_alternate_designator
or ( bitt.lowest_alternate_designator is null
and bbm.alternate_bom_designator is not null
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 = 2
or bbm2.assembly_type = 1
and bitt.current_assembly_type = 1)
)
)
or /* Pickup prim par only if starting alt is not
null and bill for .. */
(bitt.lowest_alternate_designator is not null
and bbm.alternate_bom_designator is null
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)
)
)
)
and (( msi.effectivity_control=1 -- Date Effectivity Control
and bic.effectivity_date <= to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI')
and ( bic.disable_date is null or
bic.disable_date > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI'))
and ( 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,
decode(bic.old_component_sequence_id,null,
bic.component_sequence_id,
bic.old_component_sequence_id)
,bic.component_sequence_id) =
decode(bic2.implementation_date,NULL,
decode(bic2.old_component_sequence_id,null,
-- bic2.component_sequence_id,bic.old_component_sequence_id)
bic2.component_sequence_id,bic2.old_component_sequence_id) -- For FP Bug 6134733 (Base Bug : 5405194 )
, bic2.component_sequence_id)
and bic2.effectivity_date <=
to_date(c_implosion_date,'YYYY/MM/DD HH24:MI')
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'))
and (bic2.disable_date is null
or bic2.disable_date > to_date(c_implosion_date,
'YYYY/MM/DD HH24:MI')))
)))
OR
( msi.effectivity_control = 2
AND
BIC.FROM_END_ITEM_UNIT_NUMBER <= NVL(BITT.TO_END_ITEM_UNIT_NUMBER,
BIC.FROM_END_ITEM_UNIT_NUMBER)
AND
NVL(BIC.TO_END_ITEM_UNIT_NUMBER,
NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
BIC.FROM_END_ITEM_UNIT_NUMBER)
AND(c_implemented_only_option=1 and bic.implementation_date is not null
or c_implemented_only_option = 2)
AND bic.from_end_item_unit_number <= decode(msi.eam_item_type,1,c_serial_number_to,c_unit_number_to)
AND decode(msi.eam_item_type,1,c_serial_number_from,c_unit_number_from) is not null -- exclude serial eff EAM items
AND bic.to_end_item_unit_number is null
OR bic.to_end_item_unit_number >= decode(msi.eam_item_type,1,c_serial_number_from,c_unit_number_from)))
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;
l_lid1.delete;
l_pid1.delete;
l_aid1.delete;
l_abd1.delete;
l_sc1.delete;
l_lad1.delete;
l_cat1.delete;
l_csi1.delete;
l_oi1.delete;
l_osn1.delete;
l_ed1.delete;
l_dd1.delete;
l_fun1.delete;
l_tun1.delete;
l_bt1.delete;
l_cq1.delete;
l_risd1.delete;
l_cn1.delete;
l_impf1.delete;
l_lid.delete;
l_pid.delete;
l_aid.delete;
l_abd.delete;
l_sc.delete;
l_lad.delete;
l_cat.delete;
l_csi.delete;
l_oi.delete;
l_osn.delete;
l_ed.delete;
l_dd.delete;
l_fun.delete;
l_tun.delete;
l_bt.delete;
l_cq.delete;
l_risd.delete;
l_cn.delete;
l_impf.delete;
l_lid.delete(i);
l_pid.delete(i);
l_aid.delete(i);
l_abd.delete(i);
l_sc.delete(i);
l_lad.delete(i);
l_cat.delete(i);
l_csi.delete(i);
l_oi.delete(i);
l_osn.delete(i);
l_ed.delete(i);
l_dd.delete(i);
l_fun.delete(i);
l_tun.delete(i);
l_bt.delete(i);
l_cq.delete(i);
l_risd.delete(i);
l_cn.delete(i);
l_impf.delete(i);
l_lid.delete(i);
l_pid.delete(i);
l_aid.delete(i);
l_abd.delete(i);
l_sc.delete(i);
l_lad.delete(i);
l_cat.delete(i);
l_csi.delete(i);
l_oi.delete(i);
l_osn.delete(i);
l_ed.delete(i);
l_dd.delete(i);
l_fun.delete(i);
l_tun.delete(i);
l_bt.delete(i);
l_cq.delete(i);
l_risd.delete(i);
l_cn.delete(i);
l_impf.delete(i);
/*INSERT INTO BOM_SMALL_IMPL_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,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
COMPONENT_QUANTITY,
IMPLEMENTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_SORT_CODE,
implosion_date) VALUES (
l_lid1(i),
l_pid1(i),
l_aid1(i),
l_abd1(i),
cur_level + 1,
l_sc1(i),
l_lad1(i),
l_cat1(i),
sequence_id,
l_csi1(i),
l_oi1(i),
l_risd1(i),
l_cn1(i),
l_osn1(i),
l_ed1(i),
l_dd1(i),
l_fun1(i),
l_tun1(i),
l_cq1(i),
l_impf1(i),
sysdate,
-1,
sysdate,
-1,
decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
INSERT INTO BOM_SMALL_IMPL_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,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
BASIS_TYPE,
COMPONENT_QUANTITY,
IMPLEMENTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_SORT_CODE,
IMPLOSION_DATE )
( SELECT
l_lid1(i),
l_pid1(i),
l_aid1(i),
l_abd1(i),
(cur_level + 1),
l_sc1(i),
l_lad1(i),
l_cat1(i),
sequence_id,
l_csi1(i),
l_oi1(i),
l_risd1(i),
l_cn1(i),
l_osn1(i),
l_ed1(i),
l_dd1(i),
l_fun1(i),
l_tun1(i),
l_bt1(i),
l_cq1(i),
l_impf1(i),
sysdate,
-1,
sysdate,
-1,
decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
to_date(impl_date, 'YYYY/MM/DD HH24:MI')
FROM DUAL
WHERE NOT EXISTS
( SELECT 'X'
FROM BOM_SMALL_IMPL_TEMP
WHERE LOWEST_ITEM_ID = l_lid1(i)
AND CURRENT_ITEM_ID = l_pid1(i)
AND PARENT_ITEM_ID = l_aid1(i)
AND ALTERNATE_DESIGNATOR = l_abd1(i)
AND CURRENT_LEVEL = (cur_level + 1)
AND SORT_CODE = l_sc1(i)
AND SEQUENCE_ID = sequence_id
AND COMPONENT_SEQUENCE_ID = l_csi1(i)
AND ORGANIZATION_ID = l_oi1(i)
AND PARENT_SORT_CODE = decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7))
)
);