[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_quantity_of_children_tbl.DELETE;
g_total_qty_at_next_level_tbl.DELETE;
g_parent_sort_order_tbl.DELETE;
SELECT
BET.TOP_BILL_SEQUENCE_ID TBSI,
BOM.BILL_SEQUENCE_ID BSI,
BOM.COMMON_BILL_SEQUENCE_ID CBSI,
BOM.COMMON_ORGANIZATION_ID COI,
BOM.ORGANIZATION_ID OI,
BIC.COMPONENT_SEQUENCE_ID CSI,
BIC.COMPONENT_ITEM_ID CID,
BIC.BASIS_TYPE BT,
BIC.COMPONENT_QUANTITY CQ,
c_level,
(BIC.COMPONENT_QUANTITY *
DECODE(BIC.BASIS_TYPE, null,BET.EXTENDED_QUANTITY,1) *
decode(c_plan_factor_flag, 1, BIC.PLANNING_FACTOR/100, 1) /
decode(BIC.COMPONENT_YIELD_FACTOR, 0, 1,
BIC.COMPONENT_YIELD_FACTOR)) EQ,
BET.SORT_ORDER SO,
c_grp_id,
BET.TOP_ALTERNATE_DESIGNATOR TAD,
BIC.COMPONENT_YIELD_FACTOR CYF,
BET.TOP_ITEM_ID TID,
BET.COMPONENT_CODE CC,
BIC.INCLUDE_IN_COST_ROLLUP IICR,
BET.LOOP_FLAG LF,
BIC.PLANNING_FACTOR PF,
BIC.OPERATION_SEQ_NUM OSN,
BIC.BOM_ITEM_TYPE BIT,
BET.BOM_ITEM_TYPE PBIT,
BET.COMPONENT_ITEM_ID PAID,
BOM.ALTERNATE_BOM_DESIGNATOR,
BIC.WIP_SUPPLY_TYPE WST,
BIC.ITEM_NUM ITN,
DECODE(G_Allow_Date_Trimming_Flag,'N',BIC.EFFECTIVITY_DATE,Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE))) ED,
DECODE(G_Allow_Date_Trimming_Flag,'N',BIC.DISABLE_DATE,Least(Nvl(BIC.DISABLE_DATE,BET.DISABLE_DATE),Nvl(BET.DISABLE_DATE,BIC.DISABLE_DATE))) DD,
--BIC.EFFECTIVITY_DATE ED,
--BIC.DISABLE_DATE DD,
BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
BIC.TO_END_ITEM_UNIT_NUMBER EUN,
BIC.IMPLEMENTATION_DATE ID,
BIC.OPTIONAL OPT,
BIC.SUPPLY_SUBINVENTORY SS,
BIC.SUPPLY_LOCATOR_ID SLI,
BIC.COMPONENT_REMARKS CR,
BIC.CHANGE_NOTICE CN,
BIC.OPERATION_LEAD_TIME_PERCENT OLTP,
BIC.MUTUALLY_EXCLUSIVE_OPTIONS MEO,
BIC.CHECK_ATP CATP,
BIC.REQUIRED_TO_SHIP RTS,
BIC.REQUIRED_FOR_REVENUE RFR,
BIC.INCLUDE_ON_SHIP_DOCS IOSD,
BIC.LOW_QUANTITY LQ,
BIC.HIGH_QUANTITY HQ,
BIC.SO_BASIS SB,
--BET.OPERATION_OFFSET,
--BET.CURRENT_REVISION,
--BET.LOCATOR,
BIC.ATTRIBUTE_CATEGORY,
BIC.ATTRIBUTE1,
BIC.ATTRIBUTE2,
BIC.ATTRIBUTE3,
BIC.ATTRIBUTE4,
BIC.ATTRIBUTE5,
BIC.ATTRIBUTE6,
BIC.ATTRIBUTE7,
BIC.ATTRIBUTE8,
BIC.ATTRIBUTE9,
BIC.ATTRIBUTE10,
BIC.ATTRIBUTE11,
BIC.ATTRIBUTE12,
BIC.ATTRIBUTE13,
BIC.ATTRIBUTE14,
BIC.ATTRIBUTE15,
BET.SORT_ORDER PARENT_SORT_ORDER,
BIC.AUTO_REQUEST_MATERIAL
FROM BOM_EXPLOSION_TEMP BET, BOM_BILL_OF_MATERIALS BOM,
MTL_SYSTEM_ITEMS_B SI,
BOM_INVENTORY_COMPONENTS BIC,
ENG_REVISED_ITEMS ERI
WHERE BET.PLAN_LEVEL = c_level - 1
AND BET.GROUP_ID = c_grp_id
AND BET.TOP_BILL_SEQUENCE_ID = top_bill_id
AND BOM.ASSEMBLY_ITEM_ID = SI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = SI.ORGANIZATION_ID
AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BET.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND BET.ORGANIZATION_ID = BOM.ORGANIZATION_ID -- Bug 7159394 .. Reverting fix 6707314
--AND BOM.ORGANIZATION_ID = decode(BET.COMMON_BILL_SEQUENCE_ID,BET.BILL_SEQUENCE_ID,BET.ORGANIZATION_ID,BET.COMMON_ORGANIZATION_ID) /* Bug: 6707314 */
AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
AND (G_Module <> 5 OR (G_Module = 5 AND (nvl(BET.wip_supply_type, si.wip_supply_type) = 6 /*Added nvl for bug 7700219 (FP of 7638607)*/
OR BET.PLAN_LEVEL = 0
)
)
)
--Explode only Phantom components when module=5
AND ( (c_std_comp_flag = 1 -- only std components
AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2
)
OR
(c_std_comp_flag = 2)
OR
(c_std_comp_flag = 3 AND nvl(BET.BOM_ITEM_TYPE, 1) IN (1,2)
AND (BIC.BOM_ITEM_TYPE IN (1,2)
OR
(BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)
)
)
)
AND ( (c_bom_or_eng = 1 and BOM.ASSEMBLY_TYPE = 1)
OR
(c_bom_or_eng = 2)
)
AND (
(BET.TOP_ALTERNATE_DESIGNATOR IS NULL
AND
BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)
OR
(BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
AND
BOM.ALTERNATE_BOM_DESIGNATOR=BET.TOP_ALTERNATE_DESIGNATOR
)
OR
( BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND NOT EXISTS
(SELECT 'X'
FROM BOM_BILL_OF_MATERIALS BOM2
WHERE BOM2.ORGANIZATION_ID = c_org_id
AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND BOM2.ALTERNATE_BOM_DESIGNATOR =
BET.TOP_ALTERNATE_DESIGNATOR
AND ((c_bom_or_eng = 1 and BOM2.ASSEMBLY_TYPE = 1)
OR c_bom_or_eng = 2
)
) -- subquery
)
) -- end of alt logic
-- whether to include option classes and models under a standard item
-- special logic added at CST request
AND ( (c_incl_oc = 1)
or
(c_incl_oc = 2 AND
( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4)
OR
( BET.BOM_ITEM_TYPE <> 4)
)
)
-- do not explode if immediate parent is standard and current
-- component is option class or model - special logic for config items
AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4
AND
BET.BOM_ITEM_TYPE IN (1,2)
)
AND (
( NVL(SI.EFFECTIVITY_CONTROL,1) = 2
AND ((c_explode_option = 1) -- ALL
OR (c_explode_option IN (2,3) AND BIC.DISABLE_DATE IS NULL)
)
/*
AND unit_number_from <=
NVL(BIC.TO_END_ITEM_UNIT_NUMBER,unit_number_from)
AND unit_number_to >= BIC.FROM_END_ITEM_UNIT_NUMBER
AND BIC.FROM_END_ITEM_UNIT_NUMBER <=
NVL(BET.TO_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)
AND
NVL(BIC.TO_END_ITEM_UNIT_NUMBER,
NVL(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
NVL(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)
AND
( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
OR
c_impl_flag = 2 )*/
AND BIC.from_end_item_unit_number IS NOT NULL
AND ( (c_explode_option = 2
AND unit_number >= BIC.from_end_item_unit_number
AND unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number))
OR
(c_explode_option = 3
AND unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number))
)
AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
OR c_impl_flag = 2 )
)
OR
(
NVL(SI.EFFECTIVITY_CONTROL,1) =1
AND
( --(c_explode_option = 1 ) -- ALL
(c_explode_option = 1 /* ALL */ /* When option is all, level 0 should pick all comps */
AND ( (c_level-1 = 0) OR /* but the subsequent levels should continue to narrow */
( bic.effectivity_date <= nvl(bet.disable_date, bic.effectivity_date)
AND NVL(bic.disable_date, bet.effectivity_date) >= bet.effectivity_date) ) )
OR
(c_explode_option = 2 AND -- CURRENT
c_rev_date >=
BIC.EFFECTIVITY_DATE AND
c_rev_date < -- Bug #3138456
nvl(BIC.DISABLE_DATE,
c_rev_date+1)
) -- CURRENT
OR
(c_explode_option = 3 -- CURRENT AND FUTURE
AND nvl(BIC.DISABLE_DATE, c_rev_date + 1) > c_rev_date
/* Modified above line for Bug #3138456 */
) -- CURRENT AND FUTURE
)
AND ( (c_impl_flag = 2 AND
( c_explode_option = 1
OR
(c_explode_option = 2 AND not exists
(SELECT null
FROM BOM_INVENTORY_COMPONENTS CIB,
ENG_REVISED_ITEMS ERI2
WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
CIB.OLD_COMPONENT_SEQUENCE_ID,
CIB.COMPONENT_SEQUENCE_ID) =
decode(BIC.IMPLEMENTATION_DATE, NULL,
BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID)
OR
CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
) -- decode
AND CIB.EFFECTIVITY_DATE <=
c_rev_date
AND BIC.EFFECTIVITY_DATE < cib.EFFECTIVITY_DATE
AND CIB.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID (+)
AND ( ( release_option = 1 AND nvl(ERI2.STATUS_TYPE,6) IN (4,6,7) )
OR
( release_option = 2 AND nvl(ERI2.STATUS_TYPE,6) IN (1,4,6,7))
OR
( release_option = 0 AND nvl(ERI2.STATUS_TYPE,6) = 6 )
OR
(release_option = 3)
)
) -- end of subquery
) -- CURRENT
OR
(c_explode_option = 3 AND not exists
(SELECT null
FROM BOM_INVENTORY_COMPONENTS CIB,
ENG_REVISED_ITEMS ERI2
WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
CIB.OLD_COMPONENT_SEQUENCE_ID,
CIB.COMPONENT_SEQUENCE_ID) =
decode(BIC.IMPLEMENTATION_DATE, NULL,
BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID)
OR
CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
) -- decode
AND CIB.EFFECTIVITY_DATE <=
c_rev_date
AND BIC.EFFECTIVITY_DATE < cib.EFFECTIVITY_DATE
AND CIB.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID (+)
AND ( ( release_option = 1 AND nvl(ERI2.STATUS_TYPE,6) IN (4,6,7) )
OR
( release_option = 2 AND nvl(ERI2.STATUS_TYPE,6) IN (1,4,6,7))
OR
( release_option = 0 AND nvl(ERI2.STATUS_TYPE,6) = 6 )
OR
(release_option = 3)
)
) -- end of subquery
OR BIC.EFFECTIVITY_DATE >
c_rev_date
) -- CURRENT AND FUTURE
) -- explode_option
) -- impl_flag = 2
OR
(c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
) -- explode option
)
)
AND BET.LOOP_FLAG = 2
AND BIC.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID (+)
AND (
( release_option = 1
AND nvl(ERI.STATUS_TYPE,6) IN (4,6,7)
)
OR
( release_option = 2
AND nvl(ERI.STATUS_TYPE,6) IN (1,4,6,7)
)
OR
(
release_option = 0
AND nvl(ERI.STATUS_TYPE,6) = 6
)
OR
(release_option = 3)
)
ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER,
decode(c_order_by, 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
decode(c_order_by, 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM);
Select mil.concatenated_segments
From mtl_item_locations_kfv mil
Where mil.inventory_location_id = P_Locator;
Select round(bos.operation_lead_time_percent, 2) oltp
From Bom_Operation_Sequences bos,
Bom_Operational_Routings bor
Where bor.assembly_item_id = P_Assembly
And bor.organization_Id = org_id
And (bor.alternate_routing_designator = P_Alternate
or
(bor.alternate_routing_designator is null AND not exists (
SELECT null
FROM bom_operational_routings bor2
WHERE bor2.assembly_item_id = P_Assembly
AND bor2.organization_id = org_id
AND bor2.alternate_routing_designator = P_Alternate)
))
And bor.common_routing_sequence_id = bos.routing_sequence_id
And bos.operation_seq_num = P_Operation
And bos.effectivity_date <=
trunc(rev_date)
And nvl(bos.disable_date,
rev_date + 1) >=
trunc(rev_date);
Select P_Percent/100 * msi.full_lead_time offset
From mtl_system_items_b msi
Where msi.inventory_item_id = P_ParentItem
And msi.organization_id = Org_Id;
Select msi.concatenated_segments,
bom.alternate_bom_designator
From mtl_system_items_b_kfv msi,
bom_bill_of_materials bom,
BOM_EXPLOSION_TEMP bet
Where msi.inventory_item_id = bom.assembly_item_id
And msi.organization_id = bom.organization_id
And bom.bill_sequence_id = bet.top_bill_sequence_id
And bet.group_id = grp_id
And rownum = 1;
SELECT revision_id, revision, revision_label FROM mtl_item_revisions_vl WHERE revision_id = p_revision_id;
SELECT revision,revision_label,revision_id
FROM mtl_item_revisions_b MIR
WHERE mir.inventory_item_id = p_inventory_item_id
AND mir.organization_id = p_organization_id
AND mir.effectivity_date <= p_revision_date
AND (p_impl_flag = 2 OR (p_impl_flag = 1 AND mir.implementation_date IS NOT NULL) )
ORDER BY mir.effectivity_date DESC;
/* Declare pl/sql tables for all coulmns in the select list. BULK BIND and INSERT with
pl/sql table of records work fine in 9i releases but not in 8i. So, the only option is
to use individual pl/sql table for each column in the cursor select list */
TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
last_update_date_tbl DATE_TBL_TYPE;
last_updated_by_tbl NUMBER_TBL_TYPE;
exit; -- do not insert extra level
--dbms_output.put_line('O.K. enough. insert now');
INSERT INTO bom_explosion_temp
(
TOP_BILL_SEQUENCE_ID ,
BILL_SEQUENCE_ID ,
COMMON_BILL_SEQUENCE_ID ,
COMMON_ORGANIZATION_ID ,
ORGANIZATION_ID ,
COMPONENT_SEQUENCE_ID ,
COMPONENT_ITEM_ID ,
BASIS_TYPE ,
COMPONENT_QUANTITY ,
PLAN_LEVEL ,
EXTENDED_QUANTITY ,
SORT_ORDER ,
GROUP_ID ,
TOP_ALTERNATE_DESIGNATOR ,
COMPONENT_YIELD_FACTOR ,
TOP_ITEM_ID ,
COMPONENT_CODE ,
INCLUDE_IN_ROLLUP_FLAG ,
LOOP_FLAG ,
PLANNING_FACTOR ,
OPERATION_SEQ_NUM ,
BOM_ITEM_TYPE ,
PARENT_BOM_ITEM_TYPE ,
ASSEMBLY_ITEM_ID ,
--ALTERNATE_BOM_DESIGNATOR ,
WIP_SUPPLY_TYPE ,
ITEM_NUM ,
EFFECTIVITY_DATE ,
DISABLE_DATE ,
FROM_END_ITEM_UNIT_NUMBER ,
TO_END_ITEM_UNIT_NUMBER ,
IMPLEMENTATION_DATE ,
OPTIONAL ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
COMPONENT_REMARKS ,
CHANGE_NOTICE ,
OPERATION_LEAD_TIME_PERCENT ,
MUTUALLY_EXCLUSIVE_OPTIONS ,
CHECK_ATP ,
REQUIRED_TO_SHIP ,
REQUIRED_FOR_REVENUE ,
INCLUDE_ON_SHIP_DOCS ,
LOW_QUANTITY ,
HIGH_QUANTITY ,
SO_BASIS ,
--OPERATION_OFFSET ,
--CURRENT_REVISION ,
--LOCATOR ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
--PARENT_SORT_ORDER ,
AUTO_REQUEST_MATERIAL )
VALUES
(
top_bill_sequence_id_tbl(i) ,
bill_sequence_id_tbl(i) ,
common_bill_sequence_id_tbl(i) ,
common_organization_id_tbl(i) ,
organization_id_tbl(i) ,
component_sequence_id_tbl(i) ,
component_item_id_tbl(i) ,
basis_type_tbl(i) ,
component_quantity_tbl(i) ,
plan_level_tbl(i) ,
extended_quantity_tbl(i) ,
sort_order_tbl(i) ,
group_id_tbl(i) ,
top_alternate_designator_tbl(i) ,
component_yield_factor_tbl(i) ,
top_item_id_tbl(i) ,
component_code_tbl(i) ,
include_in_cost_rollup_tbl(i) ,
loop_flag_tbl(i) ,
planning_factor_tbl(i) ,
operation_seq_num_tbl(i) ,
bom_item_type_tbl(i) ,
parent_bom_item_type_tbl(i) ,
parent_item_id_tbl(i) ,
--alternate_bom_designator_tbl(i) ,
wip_supply_type_tbl(i) ,
item_num_tbl(i) ,
effectivity_date_tbl(i) ,
disable_date_tbl(i) ,
from_end_item_unit_number_tbl(i) ,
to_end_item_unit_number_tbl(i) ,
implementation_date_tbl(i) ,
optional_tbl(i) ,
supply_subinventory_tbl(i) ,
supply_locator_id_tbl(i) ,
component_remarks_tbl(i) ,
change_notice_tbl(i) ,
operation_leadtime_percent_tbl(i) ,
mutually_exclusive_options_tbl(i) ,
check_atp_tbl(i) ,
required_to_ship_tbl(i) ,
required_for_revenue_tbl(i) ,
include_on_ship_docs_tbl(i) ,
low_quantity_tbl(i) ,
high_quantity_tbl(i) ,
so_basis_tbl(i) ,
--operation_offset_tbl(i) ,
--Current_revision_tbl(i) ,
--locator_tbl(i) ,
attribute_category_tbl(i) ,
attribute1_tbl(i) ,
attribute2_tbl(i) ,
attribute3_tbl(i) ,
attribute4_tbl(i) ,
attribute5_tbl(i) ,
attribute6_tbl(i) ,
attribute7_tbl(i) ,
attribute8_tbl(i) ,
attribute9_tbl(i) ,
attribute10_tbl(i) ,
attribute11_tbl(i) ,
attribute12_tbl(i) ,
attribute13_tbl(i) ,
attribute14_tbl(i) ,
attribute15_tbl(i) ,
--parent_sort_order_tbl(i),
auto_request_material_tbl(i) );
--dbms_output.put_line('O.K. insert done. now what?');
/* Update the quantity of children for every parent, total quantity for every parent */
/*
FORALL i IN 1..g_parent_sort_order_tbl.COUNT
UPDATE bom_explosion_temp
SET quantity_of_children = g_quantity_of_children_tbl(i),
total_qty_at_next_level = g_total_qty_at_next_level_tbl(i)
WHERE group_id = grp_id AND sort_order = g_parent_sort_order_tbl(i);
Select bl.assembly_item_id,
bl.alternate_designator,
bl.conc_flex_string,
bom.bill_sequence_id,
bom.common_bill_sequence_id,
msi.bom_item_type
From mtl_system_items msi,
Bom_Bill_Of_Materials bom,
Bom_Lists bl
Where bl.sequence_id = list_id
And bom.assembly_item_id = bl.assembly_item_id
And bom.organization_id = org_id
And nvl(bom.alternate_bom_designator, 'PRIMARY ALTERNATE') =
nvl(bl.alternate_designator, 'PRIMARY ALTERNATE')
And msi.inventory_item_id = bom.assembly_item_id
And msi.organization_id = bom.organization_id;*/
SELECT
bl.assembly_item_id,
bl.alternate_designator,
bl.conc_flex_string,
bom.bill_sequence_id,
bom.common_bill_sequence_id,
bom.common_organization_id,
msi.bom_item_type
FROM
mtl_system_items msi,
Bom_Bill_Of_Materials bom,
Bom_Lists bl
WHERE
bl.sequence_id = list_id And
bom.assembly_item_id = bl.assembly_item_id And
bom.organization_id = org_id And
bom.alternate_bom_designator = bl.alternate_designator And
BOM.alternate_bom_designator IS NOT NULL And
bl.alternate_designator is NOT null And
msi.inventory_item_id = bom.assembly_item_id And
msi.organization_id = bom.organization_id
UNION ALL
SELECT
bl.assembly_item_id,
bl.alternate_designator,
bl.conc_flex_string,
bom.bill_sequence_id,
bom.common_bill_sequence_id,
bom.common_organization_id,
msi.bom_item_type
FROM
mtl_system_items msi,
Bom_Bill_Of_Materials bom,
Bom_Lists bl
WHERE
bl.sequence_id = list_id And
bom.assembly_item_id = bl.assembly_item_id And
bom.organization_id = org_id And
bom.alternate_bom_designator is null And
bl.alternate_designator is null And
msi.inventory_item_id = bom.assembly_item_id And
msi.organization_id = bom.organization_id;
Select bom.bill_sequence_id,
bom.common_bill_sequence_id,
bom.common_organization_id,
msi.bom_item_type,
msi.item_number
From mtl_item_flexfields msi,
bom_bill_of_materials bom
Where bom.assembly_item_id = p_ItemId
And bom.organization_id = P_OrgId
And nvl(alternate_bom_designator, 'PRIMARY ALTERNATE') =
nvl(p_alternate, 'PRIMARY ALTERNATE')
And msi.inventory_item_id = bom.assembly_item_id
And msi.organization_id = bom.organization_id;
SELECT max(MAXIMUM_BOM_LEVEL)
INTO max_level
FROM BOM_PARAMETERS
WHERE (org_id = -1
or
(org_id <> -1 and ORGANIZATION_ID = org_id)
);
insert into bom_explosion_temp(
group_id,
bill_sequence_id,
common_bill_sequence_id,
component_sequence_id,
organization_id,
common_organization_id,
top_item_id,
component_item_id,
plan_level,
extended_quantity,
basis_type,
component_quantity,
sort_order,
request_id,
program_application_id,
program_id,
program_update_date,
top_bill_sequence_id,
component_code,
loop_flag,
top_alternate_designator,
bom_item_type,
parent_bom_item_type,
auto_request_material
)
values(
grp_id,
l_bill_rec.bill_sequence_id,
l_bill_rec.common_bill_sequence_id,
NULL,
org_id,
l_bill_rec.common_organization_id,
item_id,
item_id,
0,
expl_qty,
NULL,
1,
lpad('1', G_SortWidth, '0'),
req_id,
prgm_appl_id,
prgm_id,
sysdate,
l_bill_rec.bill_sequence_id,
item_id,
--to_char(item_id),
--l_LoopFlag,
g_no,
alternate,
l_bill_rec.bom_item_type,
l_bill_rec.bom_item_type,
'Y'
);
insert into bom_explosion_temp(
group_id,
bill_sequence_id,
common_bill_sequence_id,
component_sequence_id,
organization_id,
common_organization_id,
top_item_id,
component_item_id,
plan_level,
extended_quantity,
basis_type,
component_quantity,
sort_order,
request_id,
program_application_id,
program_id,
program_update_date,
top_bill_sequence_id,
component_code,
loop_flag,
top_alternate_designator,
bom_item_type,
parent_bom_item_type,
auto_request_material
)
values(
grp_id,
l_list_rec.bill_sequence_id,
l_list_rec.common_bill_sequence_id,
NULL,
org_id,
l_list_rec.common_organization_id,
l_list_rec.assembly_item_id,
l_list_rec.assembly_item_id,
0,
expl_qty,
1,
1,
lpad('1', G_SortWidth, '0'),
req_id,
prgm_appl_id,
prgm_id,
sysdate,
l_list_rec.bill_sequence_id,
l_list_rec.assembly_item_id,
--l_LoopFlag,
g_no,
l_list_rec.alternate_designator,
l_list_rec.bom_item_type,
l_list_rec.bom_item_type,
'Y'
);
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
into expl_date
from dual;
select fnd_date.date_to_canonical(fnd_date.displayDT_to_date(rev_date))
into expl_date
from dual;
select to_char(to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')
into expl_date
from dual;
SELECT SORT_ORDER
FROM BOM_EXPLOSION_TEMP
WHERE
LOOP_FLAG = 1
AND GROUP_ID = c_group_id;
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
into expl_date
from dual;
select fnd_date.date_to_canonical(fnd_date.displayDT_to_date(rev_date))
into expl_date
from dual;
select to_char(to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')
into expl_date
from dual;
INSERT INTO BOM_EXPLOSION_TEMP
(
GROUP_ID,
BILL_SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
ORGANIZATION_ID,
COMPONENT_ITEM_ID,
PLAN_LEVEL,
EXTENDED_QUANTITY,
BASIS_TYPE,
COMPONENT_QUANTITY,
SORT_ORDER,
PROGRAM_UPDATE_DATE,
TOP_BILL_SEQUENCE_ID,
TOP_ITEM_ID,
TOP_ALTERNATE_DESIGNATOR,
COMPONENT_CODE,
LOOP_FLAG
)
SELECT grp_id,
0,
NULL,
nvl(BL.ORGANIZATION_ID, org_id),
BL.ASSEMBLY_ITEM_ID,
0,
expl_qty,
1,
1,
lpad('1', G_SortWidth, '0'),
sysdate,
0,
BL.ASSEMBLY_ITEM_ID,
NULL,
to_char(BL.ASSEMBLY_ITEM_ID),
2
FROM BOM_LISTS BL
WHERE BL.SEQUENCE_ID = list_id
AND BL.ALTERNATE_DESIGNATOR IS NULL
AND NOT EXISTS (SELECT 'NO BILL' FROM BOM_BILL_OF_MATERIALS BOM
WHERE BOM.ORGANIZATION_ID =
nvl(BL.ORGANIZATION_ID, org_id)
AND BOM.ASSEMBLY_ITEM_ID = BL.ASSEMBLY_ITEM_ID
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL);
SELECT PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
REQUESTED_BY
INTO prgm_appl_id, prg_id, user_id
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = req_id;
UPDATE BOM_EXPLOSION_TEMP bet_update
SET bet_update.LOOP_FLAG = 1
WHERE bet_update.SORT_ORDER IN
( SELECT bet.SORT_ORDER
FROM BOM_EXPLOSION_TEMP bet
WHERE
bet.GROUP_ID = grp_id
CONNECT BY PRIOR
SubStr( bet.SORT_ORDER, 1, (bet.PLAN_LEVEL * 7) ) = bet.SORT_ORDER
START WITH bet.SORT_ORDER = l_loop_flag_row_rec.sort_order )
AND bet_update.GROUP_ID = grp_id;
** insert low level codes from the explosion that was
** just performed
*/
stmt_num := 5;
INSERT INTO CST_LOW_LEVEL_CODES
(ROLLUP_ID, INVENTORY_ITEM_ID, LOW_LEVEL_CODE,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY)
SELECT cst_rlp_id, COMPONENT_ITEM_ID, max(PLAN_LEVEL),
sysdate, user_id, sysdate, user_id
FROM BOM_EXPLOSION_TEMP
WHERE GROUP_ID = grp_id
GROUP BY COMPONENT_ITEM_ID;
** if single level rollup, delete items that do not exist in bom_lists
*/
IF (rollup_option = 1) THEN
stmt_num := 6;
DELETE CST_LOW_LEVEL_CODES CLLC
WHERE NOT EXISTS (SELECT 'Item in list'
FROM BOM_LISTS BL
WHERE SEQUENCE_ID = list_id
AND BL.ASSEMBLY_ITEM_ID = CLLC.INVENTORY_ITEM_ID)
AND ROLLUP_ID = cst_rlp_id;
l_last_updated_by => user_id,
conc_flag => 1,
unimp_flag => unimpl_flag,
locking_flag => lock_flag,
rollup_date => rollup_date,
revision_date => expl_date,
alt_bom_designator => alt_desg,
alt_rtg_designator => alt_rtg_desg,
rollup_option => rollup_option,
report_option => report_option,
l_mfg_flag => bom_or_eng,
err_buf => out_message);
** delete low level codes
*/
/* delete from cst_low_level_codes
where rollup_id = cst_rlp_id;
** do the post explosion updates for costing attributes only if no
** report is selected
*/
IF ((module = 1 or module = 4) and report_option <> 2) THEN
BOMPCEXP.cst_exploder(
grp_id => grp_id,
org_id => org_id,
cst_type_id => cst_type_id,
err_msg => out_message,
error_code => out_code);