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.MSC_ITEM_SR_LEVELS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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