[Home] [Help]
| Product: | MSC - Advanced Supply Chain Planning |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ITEM.ITEM_NAME || '/' || PT1.ORGANIZATION_CODE
, SOURCES.SR_INSTANCE_ID
, SOURCES.ASSIGNMENT_SET_ID
, SOURCES.ASSIGNMENT_TYPE
, SOURCES.SOURCING_RULE_NAME
, SOURCES.ORGANIZATION_ID
, PT1.ORGANIZATION_CODE
, SOURCES.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, SOURCES.SOURCE_ORGANIZATION_ID
, SOURCES.SOURCE_ORG_INSTANCE_ID
, PT2.ORGANIZATION_CODE
, SOURCES.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, CAL.CALENDAR_DATE
, TO_NUMBER(NULL)
, SOURCES.ALLOCATION_PERCENT
, SOURCES.SHIP_METHOD
, SOURCES.AVG_TRANSIT_LEAD_TIME
, SOURCES.RANK
, PT.PARTNER_NAME
, PTS.TP_SITE_CODE
, SOURCES.VENDOR_ID
, SOURCES.VENDOR_SITE_ID
FROM MSC_TRADING_PARTNERS PT
, MSC_TRADING_PARTNER_SITES PTS
, MSC_TRADING_PARTNERS PT2
, MSC_ITEMS ITEM
, MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS PT1
, MSC_SOURCES_V SOURCES
WHERE PT.PARTNER_ID(+) = SOURCES.VENDOR_ID
AND PT.SR_INSTANCE_ID(+) = SOURCES.SR_INSTANCE_ID
AND PTS.SR_TP_SITE_ID(+) = SOURCES.VENDOR_SITE_ID
AND PTS.SR_INSTANCE_ID(+) = SOURCES.SR_INSTANCE_ID
AND PT2.SR_TP_ID(+) = SOURCES.SOURCE_ORGANIZATION_ID
AND PT2.SR_INSTANCE_ID(+) = SOURCES.SR_INSTANCE_ID
AND PT2.PARTNER_TYPE(+) = 3
AND ITEM.INVENTORY_ITEM_ID = SOURCES.INVENTORY_ITEM_ID
AND CAL.CALENDAR_CODE = PT1.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = PT1.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = PT1.SR_INSTANCE_ID
AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE
AND NVL(SOURCES.DISABLE_DATE
, TO_DATE(2634525
, 'J'))
AND PT1.SR_TP_ID = SOURCES.ORGANIZATION_ID
AND PT1.SR_INSTANCE_ID = SOURCES.SR_INSTANCE_ID
AND PT1.PARTNER_TYPE = 3
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 ITEM1.ITEM_NAME || '/' || PT.ORGANIZATION_CODE
, PT.SR_INSTANCE_ID
, SETS.ASSIGNMENT_SET_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, PT.SR_TP_ID
, PT.ORGANIZATION_CODE
, BBM.ASSEMBLY_ITEM_ID
, ITEM1.ITEM_NAME
, BBM.ORGANIZATION_ID
, BBM.ORGANIZATION_ID
, PT.ORGANIZATION_CODE
, BIC.INVENTORY_ITEM_ID
, ITEM2.ITEM_NAME
, CAL.CALENDAR_DATE
, BIC.USAGE_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 MSC_ASSIGNMENT_SETS SETS
, MSC_TRADING_PARTNERS PT
, MSC_BOM_COMPONENTS BIC
, MSC_BOMS BBM
, MSC_CALENDAR_DATES CAL
, MSC_ITEMS ITEM1
, MSC_ITEMS ITEM2
WHERE ITEM2.INVENTORY_ITEM_ID = BIC.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = BBM.BILL_SEQUENCE_ID
AND BIC.SR_INSTANCE_ID = BBM.SR_INSTANCE_ID
AND BIC.PLAN_ID = BBM.PLAN_ID
AND ITEM1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND CAL.CALENDAR_CODE = PT.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = PT.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND CAL.CALENDAR_DATE BETWEEN BIC.EFFECTIVITY_DATE
AND NVL(BIC.DISABLE_DATE
, TO_DATE(2634525
, 'J'))
AND BBM.ORGANIZATION_ID = PT.SR_TP_ID
AND BBM.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND PT.PARTNER_TYPE = 3
AND NOT EXISTS (SELECT NULL
FROM MSC_SOURCES_V SOURCES
WHERE SOURCES.ASSIGNMENT_SET_ID = SETS.ASSIGNMENT_SET_ID
AND SOURCES.ORGANIZATION_ID = PT.SR_TP_ID
AND SOURCES.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND PT.PARTNER_TYPE = 3
AND SOURCES.SOURCE_ORGANIZATION_ID = SOURCES.ORGANIZATION_ID
AND SOURCES.INVENTORY_ITEM_ID = BIC.INVENTORY_ITEM_ID
AND CAL.CALENDAR_DATE BETWEEN SOURCES.EFFECTIVE_DATE
AND NVL(SOURCES.DISABLE_DATE
, CAL.CALENDAR_DATE))