View: MRP_SCATP_ITEM_SR_LEVELS_V

Product:	MRP - Master Scheduling/MRP
Description:	Supply Chain ATP item sourcing levels view
Implementation/DBA Data:	APPS.MRP_SCATP_ITEM_SR_LEVELS_V

View Text

SELECT ITEM.INVENTORY_ITEM_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ORG.LOCATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, DECODE(PO.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, MRP_SCATP_PVT.GET_DEFAULT_INTRANSIT_TIME(ORG.LOCATION_ID
, PO.LOCATION_ID)))
, DECODE(PO.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, SUBSTR(MRP_SCATP_PVT.GET_DEFAULT_SHIP_METHOD(ORG.LOCATION_ID
, PO.LOCATION_ID)
, 1
, 30)))
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 6
, 1
, 4
, 8) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, MSRA.CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID
FROM MTL_SYSTEM_ITEMS ITEM
, HR_ORGANIZATION_UNITS ORG
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SOURCING_RULES MSR
, MRP_SR_ASSIGNMENTS MSRA
WHERE MSRA.CUSTOMER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND PO.CUSTOMER_ID (+) = MSRA.CUSTOMER_ID
AND PO.SITE_USE_ID (+) = MSRA.SHIP_TO_SITE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ORG.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ( (MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG*/
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID ) OR (MSRA.ASSIGNMENT_TYPE = 4 /* ORG */ ) ) UNION ALL SELECT /* LEVEL 3
, ASSIGNMENT_TYPE 5: ITEM CATEGORY ORG */ CAT.INVENTORY_ITEM_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ORG.LOCATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, DECODE(PO.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, MRP_SCATP_PVT.GET_DEFAULT_INTRANSIT_TIME(ORG.LOCATION_ID
, PO.LOCATION_ID)))
, DECODE(PO.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, SUBSTR(MRP_SCATP_PVT.GET_DEFAULT_SHIP_METHOD(ORG.LOCATION_ID
, PO.LOCATION_ID)
, 1
, 30)))
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 3 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, MSRA.CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID
FROM MTL_ITEM_CATEGORIES CAT
, HR_ORGANIZATION_UNITS ORG
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SOURCING_RULES MSR
, MRP_SR_ASSIGNMENTS MSRA
WHERE MSRA.ASSIGNMENT_TYPE = 5 /* ITEM CATEGORY-ORG */
AND MSRA.CUSTOMER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND PO.CUSTOMER_ID (+) = MSRA.CUSTOMER_ID
AND PO.SITE_USE_ID (+) = MSRA.SHIP_TO_SITE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ORG.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID UNION ALL SELECT ITEM.INVENTORY_ITEM_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ORG.LOCATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, DECODE(PO.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, MRP_SCATP_PVT.GET_DEFAULT_INTRANSIT_TIME(ORG.LOCATION_ID
, PO.LOCATION_ID)))
, DECODE(PO.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, SUBSTR(MRP_SCATP_PVT.GET_DEFAULT_SHIP_METHOD(ORG.LOCATION_ID
, PO.LOCATION_ID)
, 1
, 30)))
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 1
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10)
, 3
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4)) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PO.CUSTOMER_ID
, PO.SITE_USE_ID
FROM MTL_SYSTEM_ITEMS ITEM
, HR_ORGANIZATION_UNITS ORG
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SOURCING_RULES MSR
, MRP_SR_ASSIGNMENTS MSRA
WHERE ( ( MSRA.ASSIGNMENT_TYPE = 1 /* GLOBAL */) OR (MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID))
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND PO.SITE_USE_ID > 0
AND PO.CUSTOMER_ID IS NOT NULL UNION ALL SELECT /* LEVEL 6
, 7 */ CAT.INVENTORY_ITEM_ID
, PO.LOCATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ORG.LOCATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, DECODE(PO.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, MRP_SCATP_PVT.GET_DEFAULT_INTRANSIT_TIME(ORG.LOCATION_ID
, PO.LOCATION_ID)))
, DECODE(PO.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, DECODE(ORG.LOCATION_ID
, NULL
, TO_CHAR(NULL)
, SUBSTR(MRP_SCATP_PVT.GET_DEFAULT_SHIP_METHOD(ORG.LOCATION_ID
, PO.LOCATION_ID)
, 1
, 30)))
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6)
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PO.CUSTOMER_ID
, PO.SITE_USE_ID
FROM MTL_ITEM_CATEGORIES CAT
, HR_ORGANIZATION_UNITS ORG
, PO_LOCATION_ASSOCIATIONS_ALL PO
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SOURCING_RULES MSR
, MRP_SR_ASSIGNMENTS MSRA
WHERE MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE
, TRUNC(SYSDATE)+1)) >TRUNC(SYSDATE)
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= TRUNC(SYSDATE)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ORG.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND PO.SITE_USE_ID > 0
AND PO.CUSTOMER_ID IS NOT NULL

Columns