VIEW: APPS.MRP_SC_BILL_WU_V

Source

View Text - Preformatted

SELECT kfv1.CONCATENATED_SEGMENTS || '/' || param1.organization_code , SOURCES.ASSIGNMENT_SET_ID , SOURCES.ASSIGNMENT_TYPE , SOURCES.SOURCING_RULE_NAME , SOURCES.ORGANIZATION_ID , PARAM1.ORGANIZATION_CODE , SOURCES.INVENTORY_ITEM_ID , KFV1.CONCATENATED_SEGMENTS , SOURCES.SOURCE_ORGANIZATION_ID , PARAM2.ORGANIZATION_CODE , SOURCES.INVENTORY_ITEM_ID , KFV1.CONCATENATED_SEGMENTS , CAL.CALENDAR_DATE , TO_NUMBER(NULL) , SOURCES.ALLOCATION_PERCENT , SOURCES.SHIP_METHOD , SOURCES.AVG_TRANSIT_LEAD_TIME , SOURCES.RANK , PO_V.VENDOR_NAME , PO_V_S.VENDOR_SITE_CODE , SOURCES.VENDOR_ID , SOURCES.VENDOR_SITE_ID FROM PO_VENDORS PO_V, PO_VENDOR_SITES_ALL PO_V_S, MTL_PARAMETERS PARAM2, MTL_SYSTEM_ITEMS_KFV KFV1, BOM_CALENDAR_DATES CAL, MTL_PARAMETERS PARAM1, MRP_SOURCES_V SOURCES WHERE PO_V.VENDOR_ID(+) = SOURCES.VENDOR_ID AND PO_V_S.VENDOR_SITE_ID(+) = SOURCES.VENDOR_SITE_ID AND PARAM2.ORGANIZATION_ID (+) = SOURCES.SOURCE_ORGANIZATION_ID AND KFV1.ORGANIZATION_ID = SOURCES.ORGANIZATION_ID AND KFV1.INVENTORY_ITEM_ID = SOURCES.INVENTORY_ITEM_ID AND CAL.CALENDAR_CODE = PARAM1.CALENDAR_CODE AND CAL.EXCEPTION_SET_ID = PARAM1.CALENDAR_EXCEPTION_SET_ID AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE AND NVL(SOURCES.DISABLE_DATE, TO_DATE(2634525, 'J')) AND PARAM1.ORGANIZATION_ID = SOURCES.ORGANIZATION_ID AND ((SOURCES.ORGANIZATION_ID != SOURCES.SOURCE_ORGANIZATION_ID) OR (SOURCES.SOURCE_ORGANIZATION_ID IS NULL) ) UNION ALL SELECT /*+ ORDERED INDEX(BBM BOM_BILL_OF_MATERIALS_N1) USE_NL(PARAM, BIC, BBM, ERI, CAL, KFV1, KFV2) */ DISTINCT kfv1.CONCATENATED_SEGMENTS || '/' || param.organization_code, SETS.ASSIGNMENT_SET_ID, TO_NUMBER(NULL), TO_CHAR(NULL), PARAM.ORGANIZATION_ID, PARAM.ORGANIZATION_CODE, BBM.ASSEMBLY_ITEM_ID, KFV1.CONCATENATED_SEGMENTS, BBM.ORGANIZATION_ID, PARAM.ORGANIZATION_CODE, BIC.COMPONENT_ITEM_ID, KFV2.CONCATENATED_SEGMENTS, CAL.CALENDAR_DATE, BIC.COMPONENT_QUANTITY, TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL) FROM MRP_ASSIGNMENT_SETS SETS, MTL_PARAMETERS PARAM, BOM_INVENTORY_COMPONENTS BIC, BOM_BILL_OF_MATERIALS BBM, ENG_REVISED_ITEMS ERI, BOM_CALENDAR_DATES CAL, MTL_SYSTEM_ITEMS_KFV KFV1, MTL_SYSTEM_ITEMS_KFV KFV2 WHERE KFV2.ORGANIZATION_ID = BBM.ORGANIZATION_ID AND KFV2.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND KFV1.ORGANIZATION_ID = BBM.ORGANIZATION_ID AND KFV1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID AND ERI.REVISED_ITEM_SEQUENCE_ID (+) = BIC.REVISED_ITEM_SEQUENCE_ID AND (NVL(ERI.MRP_ACTIVE, 1) != 2 OR DECODE(ERI.REVISED_ITEM_ID, NULL, SYSDATE, ERI.IMPLEMENTATION_DATE) IS NOT NULL) AND CAL.CALENDAR_CODE = PARAM.CALENDAR_CODE AND CAL.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID AND CAL.CALENDAR_DATE BETWEEN BIC.EFFECTIVITY_DATE AND NVL(BIC.DISABLE_DATE, TO_DATE(2634525, 'J')) AND BIC.BILL_SEQUENCE_ID = BBM.COMMON_BILL_SEQUENCE_ID AND BBM.ORGANIZATION_ID = PARAM.ORGANIZATION_ID AND NOT EXISTS (SELECT NULL FROM MRP_ITEM_LOWEST_SR_LEVEL_V SOURCES WHERE SOURCES.ASSIGNMENT_SET_ID = SETS.ASSIGNMENT_SET_ID AND SOURCES.ORGANIZATION_ID = PARAM.ORGANIZATION_ID AND SOURCES.SOURCE_ORGANIZATION_ID = SOURCES.ORGANIZATION_ID AND SOURCES.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE AND NVL(SOURCES.DISABLE_DATE, CAL.CALENDAR_DATE))

View Text - HTML Formatted

SELECT KFV1.CONCATENATED_SEGMENTS || '/' || PARAM1.ORGANIZATION_CODE
, SOURCES.ASSIGNMENT_SET_ID
, SOURCES.ASSIGNMENT_TYPE
, SOURCES.SOURCING_RULE_NAME
, SOURCES.ORGANIZATION_ID
, PARAM1.ORGANIZATION_CODE
, SOURCES.INVENTORY_ITEM_ID
, KFV1.CONCATENATED_SEGMENTS
, SOURCES.SOURCE_ORGANIZATION_ID
, PARAM2.ORGANIZATION_CODE
, SOURCES.INVENTORY_ITEM_ID
, KFV1.CONCATENATED_SEGMENTS
, CAL.CALENDAR_DATE
, TO_NUMBER(NULL)
, SOURCES.ALLOCATION_PERCENT
, SOURCES.SHIP_METHOD
, SOURCES.AVG_TRANSIT_LEAD_TIME
, SOURCES.RANK
, PO_V.VENDOR_NAME
, PO_V_S.VENDOR_SITE_CODE
, SOURCES.VENDOR_ID
, SOURCES.VENDOR_SITE_ID
FROM PO_VENDORS PO_V
, PO_VENDOR_SITES_ALL PO_V_S
, MTL_PARAMETERS PARAM2
, MTL_SYSTEM_ITEMS_KFV KFV1
, BOM_CALENDAR_DATES CAL
, MTL_PARAMETERS PARAM1
, MRP_SOURCES_V SOURCES
WHERE PO_V.VENDOR_ID(+) = SOURCES.VENDOR_ID
AND PO_V_S.VENDOR_SITE_ID(+) = SOURCES.VENDOR_SITE_ID
AND PARAM2.ORGANIZATION_ID (+) = SOURCES.SOURCE_ORGANIZATION_ID
AND KFV1.ORGANIZATION_ID = SOURCES.ORGANIZATION_ID
AND KFV1.INVENTORY_ITEM_ID = SOURCES.INVENTORY_ITEM_ID
AND CAL.CALENDAR_CODE = PARAM1.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = PARAM1.CALENDAR_EXCEPTION_SET_ID
AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE
AND NVL(SOURCES.DISABLE_DATE
, TO_DATE(2634525
, 'J'))
AND PARAM1.ORGANIZATION_ID = SOURCES.ORGANIZATION_ID
AND ((SOURCES.ORGANIZATION_ID != SOURCES.SOURCE_ORGANIZATION_ID) OR (SOURCES.SOURCE_ORGANIZATION_ID IS NULL) ) UNION ALL SELECT /*+ ORDERED INDEX(BBM BOM_BILL_OF_MATERIALS_N1) USE_NL(PARAM
, BIC
, BBM
, ERI
, CAL
, KFV1
, KFV2) */ DISTINCT KFV1.CONCATENATED_SEGMENTS || '/' || PARAM.ORGANIZATION_CODE
, SETS.ASSIGNMENT_SET_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, PARAM.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, BBM.ASSEMBLY_ITEM_ID
, KFV1.CONCATENATED_SEGMENTS
, BBM.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, BIC.COMPONENT_ITEM_ID
, KFV2.CONCATENATED_SEGMENTS
, CAL.CALENDAR_DATE
, BIC.COMPONENT_QUANTITY
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MRP_ASSIGNMENT_SETS SETS
, MTL_PARAMETERS PARAM
, BOM_INVENTORY_COMPONENTS BIC
, BOM_BILL_OF_MATERIALS BBM
, ENG_REVISED_ITEMS ERI
, BOM_CALENDAR_DATES CAL
, MTL_SYSTEM_ITEMS_KFV KFV1
, MTL_SYSTEM_ITEMS_KFV KFV2
WHERE KFV2.ORGANIZATION_ID = BBM.ORGANIZATION_ID
AND KFV2.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND KFV1.ORGANIZATION_ID = BBM.ORGANIZATION_ID
AND KFV1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND ERI.REVISED_ITEM_SEQUENCE_ID (+) = BIC.REVISED_ITEM_SEQUENCE_ID
AND (NVL(ERI.MRP_ACTIVE
, 1) != 2 OR DECODE(ERI.REVISED_ITEM_ID
, NULL
, SYSDATE
, ERI.IMPLEMENTATION_DATE) IS NOT NULL)
AND CAL.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND CAL.CALENDAR_DATE BETWEEN BIC.EFFECTIVITY_DATE
AND NVL(BIC.DISABLE_DATE
, TO_DATE(2634525
, 'J'))
AND BIC.BILL_SEQUENCE_ID = BBM.COMMON_BILL_SEQUENCE_ID
AND BBM.ORGANIZATION_ID = PARAM.ORGANIZATION_ID
AND NOT EXISTS (SELECT NULL
FROM MRP_ITEM_LOWEST_SR_LEVEL_V SOURCES
WHERE SOURCES.ASSIGNMENT_SET_ID = SETS.ASSIGNMENT_SET_ID
AND SOURCES.ORGANIZATION_ID = PARAM.ORGANIZATION_ID
AND SOURCES.SOURCE_ORGANIZATION_ID = SOURCES.ORGANIZATION_ID
AND SOURCES.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE
AND NVL(SOURCES.DISABLE_DATE
, CAL.CALENDAR_DATE))