eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MRP_ITEM_SR_LEVELS_V

Source

View Text - Preformatted


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 */ ))

View Text - HTML Formatted

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 */ ))