View: MRP_ITEM_SR_LEVELS_V

Product:	MRP - Master Scheduling/MRP
Description:	Inventory items sources view
Implementation/DBA Data:	APPS.MRP_ITEM_SR_LEVELS_V

View Text

SELECT /*+ ORDERED USE_NL(MSRA MSR RECEIPT_ORG SOURCE_ORG) INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_SET_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_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 MRP_SR_ASSIGNMENTS MSRA
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_INTERORG_SHIP_METHODS SHIP
WHERE ITEMS.ORGANIZATION_ID = NVL(SHIP.TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_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.ORGANIZATION_ID = NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, 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.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID) OR (MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID) OR (MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID) OR (MSRA.ASSIGNMENT_TYPE = 1 /* SITE */ )) UNION ALL SELECT /*+ ORDERED USE_NL(MSRA MSR RECEIPT_ORG SOURCE_ORG) INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) */ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_SET_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_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 MRP_SR_ASSIGNMENTS MSRA
, MTL_ITEM_CATEGORIES CAT
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_INTERORG_SHIP_METHODS SHIP
WHERE CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.ORGANIZATION_ID = NVL(SHIP.TO_ORGANIZATION_ID
, CAT.ORGANIZATION_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.RECEIPT_ORGANIZATION_ID
, CAT.ORGANIZATION_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) OR (MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */ ))

Columns