View: MSC_ITEM_SR_LEVELS_V

Product:	MSC - Advanced Supply Chain Planning
Description:	Inventory items sources view
Implementation/DBA Data:	APPS.MSC_ITEM_SR_LEVELS_V

View Text

SELECT /*+ ORDERED USE_NL(MSRA MSR RECEIPT_ORG SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_SET_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, DECODE (MSRA.ASSIGNMENT_TYPE
, 6
, 1
, 4
, 8
, 3
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4)
, 1
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10)) SOURCING_LEVEL
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_INTERORG_SHIP_METHODS SHIP
WHERE ITEMS.PLAN_ID = -1
AND ITEMS.SR_INSTANCE_ID = NVL(SHIP.SR_INSTANCE_ID2
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = NVL(SHIP.TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND SHIP.PLAN_ID (+) = -1 AND SHIP.SR_INSTANCE_ID (+) = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SHIP.FROM_ORGANIZATION_ID (+) = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND SHIP.SHIP_METHOD (+) = SOURCE_ORG.SHIP_METHOD
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ( ( MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID ) OR ( MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID ) OR ( MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID ) OR ( MSRA.ASSIGNMENT_TYPE = 1 /* SITE */ )) UNION ALL SELECT /*+ ORDERED USE_NL(MSRA MSR RECEIPT_ORG SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_SET_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, DECODE (MSRA.ASSIGNMENT_TYPE
, 5
, 3
, 2
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6)) SOURCING_LEVEL
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_ITEM_CATEGORIES CAT
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_INTERORG_SHIP_METHODS SHIP
WHERE CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.ORGANIZATION_ID = NVL(SHIP.TO_ORGANIZATION_ID
, CAT.ORGANIZATION_ID)
AND CAT.SR_INSTANCE_ID = NVL(SHIP.SR_INSTANCE_ID2
, CAT.SR_INSTANCE_ID)
AND SHIP.PLAN_ID (+) = -1
AND SHIP.SR_INSTANCE_ID (+) = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SHIP.FROM_ORGANIZATION_ID (+) = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND SHIP.SHIP_METHOD (+) = SOURCE_ORG.SHIP_METHOD
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID)
AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, CAT.SR_INSTANCE_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ( ( MSRA.ASSIGNMENT_TYPE = 5 /* ITEM CATEGORY-ORG */
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID ) OR ( MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID ) )

Columns