[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
BET.TOP_BILL_SEQUENCE_ID TBSI,
BOM.BILL_SEQUENCE_ID BSI,
BOM.COMMON_BILL_SEQUENCE_ID CBSI,
BIC.COMPONENT_ITEM_ID CID,
BIC.COMPONENT_SEQUENCE_ID CSI,
BIC.BASIS_TYPE BT,
BIC.COMPONENT_QUANTITY CQ,
(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,
BET.TOP_ITEM_ID TID,
BET.TOP_ALTERNATE_DESIGNATOR TAD,
BIC.COMPONENT_YIELD_FACTOR CYF,
BOM.ORGANIZATION_ID OI,
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, BIC.WIP_SUPPLY_TYPE WST,
BIC.ITEM_NUM ITN,
BIC.EFFECTIVITY_DATE ED,
BIC.DISABLE_DATE DD,
BIC.IMPLEMENTATION_DATE ID,
BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
BIC.TO_END_ITEM_UNIT_NUMBER EUN,
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,
BOM.ALTERNATE_BOM_DESIGNATOR, -- for routing
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
FROM BOM_SMALL_EXPL_TEMP BET, BOM_BILL_OF_MATERIALS BOM,
MTL_SYSTEM_ITEMS SI,
BOM_INVENTORY_COMPONENTS BIC
WHERE BET.PLAN_LEVEL = c_level - 1
AND BET.GROUP_ID = c_grp_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 BOM.ORGANIZATION_ID = c_org_id
AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
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 )
)
OR
(
NVL(SI.EFFECTIVITY_CONTROL,1) =1
AND ( (c_explode_option = 1
AND (c_level = 1
or
( bic.effectivity_date <= nvl(bet.disable_date, bic.effectivity_date )
and nvl(bic.disable_date,bet.effectivity_date ) >= bet.effectivity_date
)
) -- c_level Bug 4721383
) -- ALL
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
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
) -- end of subquery
) -- CURRENT
OR
(c_explode_option = 3 AND not exists
(SELECT null
FROM BOM_INVENTORY_COMPONENTS CIB
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
) -- 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
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 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_kfv msi,
bom_bill_of_materials bom,
bom_small_expl_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;
l_TBSI.delete;
l_BSI.delete;
l_CBSI.delete;
l_CID.delete;
l_CSI.delete;
l_BT.delete;
l_CQ.delete;
l_EQ.delete;
l_SO.delete;
l_TID.delete;
l_TAD.delete;
l_CYF.delete;
l_OI.delete;
l_CC.delete;
l_IICR.delete;
l_LF.delete;
l_PF.delete;
l_OSN.delete;
l_BIT.delete;
l_PBIT.delete;
l_PAID.delete;
l_WST.delete;
l_ITN.delete;
l_ED.delete;
l_DD.delete;
l_ID.delete;
l_FUN.delete;
l_EUN.delete;
l_OPT.delete;
l_SS.delete;
l_SLI.delete;
l_CR.delete;
l_CN.delete;
l_OLTP.delete;
l_MEO.delete;
l_CATP.delete;
l_RTS.delete;
l_RFR.delete;
l_IOSD.delete;
l_LQ.delete;
l_HQ.delete;
l_SB.delete;
l_OPERATION_OFFSET.delete;
l_CURRENT_REVISION.delete;
l_LOCATOR.delete;
l_ALTERNATE_BOM_DESIGNATOR.delete;
l_ATTRIBUTE_CATEGORY.delete;
l_ATTRIBUTE1.delete;
l_ATTRIBUTE2.delete;
l_ATTRIBUTE3.delete;
l_ATTRIBUTE4.delete;
l_ATTRIBUTE5.delete;
l_ATTRIBUTE6.delete;
l_ATTRIBUTE7.delete;
l_ATTRIBUTE8.delete;
l_ATTRIBUTE9.delete;
l_ATTRIBUTE10.delete;
l_ATTRIBUTE11.delete;
l_ATTRIBUTE12.delete;
l_ATTRIBUTE13.delete;
l_ATTRIBUTE14.delete;
l_ATTRIBUTE15.delete;
exit; -- do not insert extra level
INSERT INTO BOM_SMALL_EXPL_TEMP (
TOP_BILL_SEQUENCE_ID,
BILL_SEQUENCE_ID,
COMMON_BILL_SEQUENCE_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,
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
) VALUES (
l_TBSI(i),
l_BSI(i),
l_CBSI(i),
l_OI(i),
l_CSI(i),
l_CID(i),
l_BT(i),
l_CQ(i),
cur_level,
l_EQ(i),
l_SO(i),
grp_id,
l_TAD(i),
l_CYF(i),
l_TID(i),
l_CC(i),
l_IICR(i),
l_LF(i),
l_PF(i),
l_OSN(i),
l_BIT(i),
l_PBIT(i),
l_PAID(i),
l_WST(i),
l_ITN(i),
l_ED(i),
l_DD(i),
l_FUN(i),
l_EUN(i),
l_ID(i),
l_OPT(i),
l_SS(i),
l_SLI(i),
l_CR(i),
l_CN(i),
l_OLTP(i),
l_MEO(i),
l_CATP(i),
l_RTS(i),
l_RFR(i),
l_IOSD(i),
l_LQ(i),
l_HQ(i),
l_SB(i),
l_OPERATION_OFFSET(i),
l_CURRENT_REVISION(i),
l_LOCATOR(i),
l_ATTRIBUTE_CATEGORY(i),
l_ATTRIBUTE1(i),
l_ATTRIBUTE2(i),
l_ATTRIBUTE3(i),
l_ATTRIBUTE4(i),
l_ATTRIBUTE5(i),
l_ATTRIBUTE6(i),
l_ATTRIBUTE7(i),
l_ATTRIBUTE8(i),
l_ATTRIBUTE9(i),
l_ATTRIBUTE10(i),
l_ATTRIBUTE11(i),
l_ATTRIBUTE12(i),
l_ATTRIBUTE13(i),
l_ATTRIBUTE14(i),
l_ATTRIBUTE15(i)
);