BOM Structure Report for Lead Times (3)

Description
Categories: Concurrent Program
Imported from Concurrent Program
Application: Bills of Material
Source: BOM Structure Report for Lead Times
Short Name: BOMRBOMSS
Run BOM Structure Report for Lead Times (3) and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT /*+ index(BET BOM_EXPLOSION_TEMP_N2) */ 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, :B6 , (BIC.COMPONENT_QUANTITY * DECODE(BIC.BASIS_TYPE, NULL,BET.EXTENDED_QUANTITY,1) * DECODE(:B15 , 1, BIC.PLANNING_FACTOR/100, 1) / DECODE(BIC.COMPONENT_YIELD_FACTOR, 0, 1, BIC.COMPONENT_YIELD_FACTOR)) EQ, BET.SORT_ORDER SO, :B13 , 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(:B14 ,'N',BIC.EFFECTIVITY_DATE,GREATEST(BIC.EFFECTIVITY_DATE,NVL(BET.EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE))) ED, DECODE(:B14 ,'N',BIC.DISABLE_DATE,LEAST(NVL(BIC.DISABLE_DATE,BET.DISABLE_DATE),NVL(BET.DISABLE_DATE,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, 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 = :B6 - 1 AND BET.GROUP_ID = :B13 AND BET.TOP_BILL_SEQUENCE_ID = :B12 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 AND NOT EXISTS (SELECT /*+ push_subq no_unnest */NULL FROM MTL_SYSTEM_ITEMS_B SIC WHERE SIC.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND SIC.ORGANIZATION_ID = :B16 AND SIC.SERVICE_ITEM_FLAG = 'Y' AND :B11 = 5 ) AND (:B11 <> 5 OR (:B11 = 5 AND (NVL(BET.WIP_SUPPLY_TYPE, SI.WIP_SUPPLY_TYPE) = 6 OR BET.PLAN_LEVEL = 0 ) ) ) AND ( (:B10 = 1 AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2 ) OR (:B10 = 2) OR (:B10 = 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 ( (:B9 = 1 AND BOM.ASSEMBLY_TYPE = 1) OR (:B9 = 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 /*+ push_subq */'X' FROM BOM_BILL_OF_MATERIALS BOM2 WHERE BOM2.ORGANIZATION_ID = :B16 AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID AND BOM2.ALTERNATE_BOM_DESIGNATOR = BET.TOP_ALTERNATE_DESIGNATOR AND ((:B9 = 1 AND BOM2.ASSEMBLY_TYPE = 1) OR :B9 = 2 ) ) ) ) AND ( (:B8 = 1) OR (:B8 = 2 AND ( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4) OR ( BET.BOM_ITEM_TYPE <> 4) ) ) AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4 AND BET.BOM_ITEM_TYPE IN (1,2) ) AND ( ( NVL(SI.EFFECTIVITY_CONTROL,1) = 2 AND ((:B5 = 1) OR (:B5 IN (2,3) AND BIC.DISABLE_DATE IS NULL) ) AND BIC.FROM_END_ITEM_UNIT_NUMBER IS NOT NULL AND ( (:B5 = 2 AND :B7 >= BIC.FROM_END_ITEM_UNIT_NUMBER AND :B7 <= NVL( BIC.TO_END_ITEM_UNIT_NUMBER, :B7 )) OR (:B5 = 3 AND :B7 <= NVL( BIC.TO_END_ITEM_UNIT_NUMBER, :B7 )) ) AND ( (:B3 = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL) OR :B3 = 2 ) ) OR ( NVL(SI.EFFECTIVITY_CONTROL,1) =1 AND ( (:B5 = 1 AND ( (:B6 -1 = 0) OR ( BIC.EFFECTIVITY_DATE <= NVL(BET.DISABLE_DATE, BIC.EFFECTIVITY_DATE) AND NVL(BIC.DISABLE_DATE, BET.EFFECTIVITY_DATE) >= BET.EFFECTIVITY_DATE) ) ) OR (:B5 = 2 AND :B4 >= BIC.EFFECTIVITY_DATE AND :B4 < NVL(BIC.DISABLE_DATE, :B4 +1) ) OR (:B5 = 3 AND NVL(BIC.DISABLE_DATE, :B4 + 1) > :B4 ) ) AND ( (:B3 = 2 AND ( :B5 = 1 OR (:B5 = 2 AND NOT EXISTS (SELECT /*+ push_subq */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 ( 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 ) AND CIB.EFFECTIVITY_DATE <= :B4 AND BIC.EFFECTIVITY_DATE < CIB.EFFECTIVITY_DATE AND CIB.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID (+) AND ( ( :B2 = 1 AND NVL(ERI2.STATUS_TYPE,6) IN (4,6,7) ) OR ( :B2 = 2 AND NVL(ERI2.STATUS_TYPE,6) IN (1,4,6,7)) OR ( :B2 = 0 AND NVL(ERI2.STATUS_TYPE,6) = 6 ) OR (:B2 = 3) ) ) ) OR (:B5 = 3 AND NOT EXISTS (SELECT /*+ push_subq */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 ( 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 ) AND CIB.EFFECTIVITY_DATE <= :B4 AND BIC.EFFECTIVITY_DATE < CIB.EFFECTIVITY_DATE AND CIB.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID (+) AND ( ( :B2 = 1 AND NVL(ERI2.STATUS_TYPE,6) IN (4,6,7) ) OR ( :B2 = 2 AND NVL(ERI2.STATUS_TYPE,6) IN (1,4,6,7)) OR ( :B2 = 0 AND NVL(ERI2.STATUS_TYPE,6) = 6 ) OR (:B2 = 3) ) ) OR BIC.EFFECTIVITY_DATE > :B4 ) ) ) OR (:B3 = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL) ) ) ) AND BET.LOOP_FLAG = 2 AND BIC.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID (+) AND ( ( :B2 = 1 AND NVL(ERI.STATUS_TYPE,6) IN (4,6,7) ) OR ( :B2 = 2 AND NVL(ERI.STATUS_TYPE,6) IN (1,4,6,7) ) OR ( :B2 = 0 AND NVL(ERI.STATUS_TYPE,6) = 6 ) OR (:B2 = 3) ) ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER, DECODE(:B1 , 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM), DECODE(:B1 , 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM)
Parameter Name SQL text Validation
BOM_SRS_NONE_CHAR
 
Char
BOM_SRS_ORG_ID
 
Number
Item Selection
 
LOV Oracle
BOM_SRS_SPECIFIC_BILL
 
Char
Revision
 
LOV Oracle
Date
 
DateTime
Items From
 
Char
To
 
Char
Category Set
 
LOV Oracle
Categories From
 
Char
To 2
 
Char
Blitz Report™