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_SOURCING_LEVELS_V

Source

View Text - Preformatted


SELECT MSRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID ORGANIZATION_ID, MSRA.SR_INSTANCE_ID SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE SOURCING_RULE_TYPE, SOURCE_ORG.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID SOURCE_ORG_INSTANCE_ID, SOURCE_ORG.SOURCE_PARTNER_ID VENDOR_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, SHIP.INTRANSIT_TIME AVG_TRANSIT_LEAD_TIME, SHIP.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, ITEMS.SR_CATEGORY_ID CATEGORY_ID, DECODE (MSRA.ASSIGNMENT_TYPE, 6, 1, 3, decode(msr.sourcing_rule_type, 1,5,4)) SOURCING_LEVEL , MSRA.ASSIGNMENT_ID ASSIGNMENT_ID, MSR.SOURCING_RULE_ID SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE, TO_CHAR(NULL) SOURCE_ORG_CODE, MSR.DESCRIPTION SR_DESCRIPTION, DECODE (MSRA.ASSIGNMENT_TYPE, 6, MSRA.PARTNER_ID, 3, to_number(NULL)) CUSTOMER_ID , DECODE(MSRA.ASSIGNMENT_TYPE, 6, MSRA.SHIP_TO_SITE_ID, 3, to_number(NULL)) SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE TIME_UOM_CODE, source_org.circular_src 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 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 ) AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 6) UNION ALL SELECT MSRA.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MSRA.SR_INSTANCE_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.SOURCE_ORG_INSTANCE_ID , SOURCE_ORG.SOURCE_PARTNER_ID , SOURCE_ORG.SOURCE_PARTNER_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , ITEMS.SR_CATEGORY_ID , DECODE (MSRA.ASSIGNMENT_TYPE, 6, 1, 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 , TO_CHAR(NULL) , MSR.DESCRIPTION , DECODE(MSRA.ASSIGNMENT_TYPE, 6, MSRA.PARTNER_ID, 3, to_number(NULL)) CUSTOMER_ID , DECODE(MSRA.ASSIGNMENT_TYPE, 6, MSRA.SHIP_TO_SITE_ID, 3, to_number(NULL)) SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE, source_org.circular_src 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 = 3 AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID ) AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 3) UNION ALL SELECT /*+ ORDERED USE_NL (MSRA MSR RECEIPT_ORG SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , ITEMS.SR_INSTANCE_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.SOURCE_ORG_INSTANCE_ID , SOURCE_ORG.SOURCE_PARTNER_ID , SOURCE_ORG.SOURCE_PARTNER_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , ITEMS.SR_CATEGORY_ID , DECODE (MSRA.ASSIGNMENT_TYPE, 4, 8, 1, DECODE(MSR.SOURCING_RULE_TYPE, 1,11,10)) SOURCING_LEVEL , MSRA.ASSIGNMENT_ID , MSR.SOURCING_RULE_ID , MSR.SOURCING_RULE_NAME , SOURCE_ORG.SOURCE_TYPE , TO_CHAR(NULL) , MSR.DESCRIPTION , DECODE (MSRA.ASSIGNMENT_TYPE, 4, MSRA.PARTNER_ID, 1, TO_NUMBER(NULL) ) CUSTOMER_ID , DECODE(MSRA.ASSIGNMENT_TYPE, 4, MSRA.SHIP_TO_SITE_ID, 1, TO_NUMBER(NULL)) SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE, source_org.circular_src FROM MSC_SR_ASSIGNMENTS MSRA, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG, MSC_SYSTEM_ITEMS ITEMS, 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 = 4 AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID) AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 4) UNION ALL SELECT /*+ ORDERED USE_NL (MSRA MSR RECEIPT_ORG SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , ITEMS.SR_INSTANCE_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.SOURCE_ORG_INSTANCE_ID , SOURCE_ORG.SOURCE_PARTNER_ID , SOURCE_ORG.SOURCE_PARTNER_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , ITEMS.SR_CATEGORY_ID , DECODE (MSRA.ASSIGNMENT_TYPE, 4, 8, 1, DECODE(MSR.SOURCING_RULE_TYPE, 1,11,10)) SOURCING_LEVEL , MSRA.ASSIGNMENT_ID , MSR.SOURCING_RULE_ID , MSR.SOURCING_RULE_NAME , SOURCE_ORG.SOURCE_TYPE , TO_CHAR(NULL) , MSR.DESCRIPTION , DECODE (MSRA.ASSIGNMENT_TYPE, 4, MSRA.PARTNER_ID, 1, TO_NUMBER(NULL) ) CUSTOMER_ID , DECODE(MSRA.ASSIGNMENT_TYPE, 4, MSRA.SHIP_TO_SITE_ID, 1, TO_NUMBER(NULL)) SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE, source_org.circular_src FROM MSC_SR_ASSIGNMENTS MSRA, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG, MSC_SYSTEM_ITEMS ITEMS, 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 = 1 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 1) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_N3) USE_NL(MSRA, CAT, MSR, RECEIPT_ORG, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID, CAT.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, SHIP.INTRANSIT_TIME, SHIP.SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, CAT.SR_CATEGORY_ID, 3 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, MSRA.PARTNER_ID, MSRA.SHIP_TO_SITE_ID, SHIP.TIME_UOM_CODE, source_org.circular_src 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 CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_TYPE = 5 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 5) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_N3) USE_NL(MSRA, CAT, MSR, RECEIPT_ORG, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID, CAT.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, SHIP.INTRANSIT_TIME, SHIP.SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, CAT.SR_CATEGORY_ID, DECODE(MSR.SOURCING_RULE_TYPE,1,7,6) SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, TO_NUMBER(NULL), TO_NUMBER(NULL), SHIP.TIME_UOM_CODE, source_org.circular_src 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 CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_TYPE = 2 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 2) UNION ALL SELECT MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, MSI.SR_INSTANCE_ID, 6 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, MSI.SOURCE_ORG_ID, MSI.SR_INSTANCE_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), 100, 1, 0, TO_CHAR(NULL), TO_DATE(1,'J'), TO_DATE(NULL), TO_NUMBER(NULL), 2 SOURCING_LEVEL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), 1, PARAM.ORGANIZATION_CODE, TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), null FROM MSC_SYSTEM_ITEMS MSI, MSC_TRADING_PARTNERS PARAM WHERE MSI.PLAN_ID = -1 AND MSI.SOURCE_ORG_ID IS NOT NULL AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID AND 3 = PARAM.PARTNER_TYPE UNION ALL SELECT /*+ ORDERED */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, 4 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, PARAM.SOURCE_ORG_ID, PARAM.SR_INSTANCE_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), 100, 1, 0, TO_CHAR(NULL), TO_DATE(1,'J'), TO_DATE(NULL), TO_NUMBER(NULL), 9 SOURCING_LEVEL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), 1, PARAM1.ORGANIZATION_CODE, TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), null FROM MSC_TRADING_PARTNERS PARAM, MSC_TRADING_PARTNERS PARAM1, MSC_SYSTEM_ITEMS ITEMS, MSC_SYSTEM_ITEMS MSI2 WHERE PARAM.SOURCE_ORG_ID IS NOT NULL AND PARAM.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND PARAM.SR_TP_ID = ITEMS.ORGANIZATION_ID AND PARAM.PARTNER_TYPE = 3 AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID AND PARAM.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID AND PARAM.PARTNER_TYPE = PARAM1.PARTNER_TYPE AND ITEMS.PLAN_ID = -1 AND MSI2.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND MSI2.PLAN_ID = ITEMS.PLAN_ID

View Text - HTML Formatted

SELECT MSRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, SHIP.INTRANSIT_TIME AVG_TRANSIT_LEAD_TIME
, SHIP.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, ITEMS.SR_CATEGORY_ID CATEGORY_ID
, DECODE (MSRA.ASSIGNMENT_TYPE
, 6
, 1
, 3
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4)) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE
, TO_CHAR(NULL) SOURCE_ORG_CODE
, MSR.DESCRIPTION SR_DESCRIPTION
, DECODE (MSRA.ASSIGNMENT_TYPE
, 6
, MSRA.PARTNER_ID
, 3
, TO_NUMBER(NULL)) CUSTOMER_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 6
, MSRA.SHIP_TO_SITE_ID
, 3
, TO_NUMBER(NULL)) SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE TIME_UOM_CODE
, SOURCE_ORG.CIRCULAR_SRC
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
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 )
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 6) UNION ALL SELECT MSRA.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MSRA.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, ITEMS.SR_CATEGORY_ID
, DECODE (MSRA.ASSIGNMENT_TYPE
, 6
, 1
, 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
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, DECODE(MSRA.ASSIGNMENT_TYPE
, 6
, MSRA.PARTNER_ID
, 3
, TO_NUMBER(NULL)) CUSTOMER_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 6
, MSRA.SHIP_TO_SITE_ID
, 3
, TO_NUMBER(NULL)) SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
, SOURCE_ORG.CIRCULAR_SRC
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 = 3
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID )
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 3) UNION ALL SELECT /*+ ORDERED USE_NL (MSRA MSR RECEIPT_ORG SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, ITEMS.SR_CATEGORY_ID
, DECODE (MSRA.ASSIGNMENT_TYPE
, 4
, 8
, 1
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10)) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, DECODE (MSRA.ASSIGNMENT_TYPE
, 4
, MSRA.PARTNER_ID
, 1
, TO_NUMBER(NULL) ) CUSTOMER_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 4
, MSRA.SHIP_TO_SITE_ID
, 1
, TO_NUMBER(NULL)) SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
, SOURCE_ORG.CIRCULAR_SRC
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, 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 = 4
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID)
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 4) UNION ALL SELECT /*+ ORDERED USE_NL (MSRA MSR RECEIPT_ORG SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, ITEMS.SR_CATEGORY_ID
, DECODE (MSRA.ASSIGNMENT_TYPE
, 4
, 8
, 1
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10)) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, DECODE (MSRA.ASSIGNMENT_TYPE
, 4
, MSRA.PARTNER_ID
, 1
, TO_NUMBER(NULL) ) CUSTOMER_ID
, DECODE(MSRA.ASSIGNMENT_TYPE
, 4
, MSRA.SHIP_TO_SITE_ID
, 1
, TO_NUMBER(NULL)) SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
, SOURCE_ORG.CIRCULAR_SRC
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, 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 = 1
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 1) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_N3) USE_NL(MSRA
, CAT
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, CAT.SR_CATEGORY_ID
, 3 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, MSRA.PARTNER_ID
, MSRA.SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
, SOURCE_ORG.CIRCULAR_SRC
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 CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 5
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 5) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_N3) USE_NL(MSRA
, CAT
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, CAT.SR_CATEGORY_ID
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SHIP.TIME_UOM_CODE
, SOURCE_ORG.CIRCULAR_SRC
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 CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 2
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 2) UNION ALL SELECT MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SR_INSTANCE_ID
, 6 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, MSI.SOURCE_ORG_ID
, MSI.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, 0
, TO_CHAR(NULL)
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 2 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, 1
, PARAM.ORGANIZATION_CODE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, NULL
FROM MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS PARAM
WHERE MSI.PLAN_ID = -1
AND MSI.SOURCE_ORG_ID IS NOT NULL
AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID
AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID
AND 3 = PARAM.PARTNER_TYPE UNION ALL SELECT /*+ ORDERED */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, 4 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORG_ID
, PARAM.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, 0
, TO_CHAR(NULL)
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 9 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, 1
, PARAM1.ORGANIZATION_CODE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, NULL
FROM MSC_TRADING_PARTNERS PARAM
, MSC_TRADING_PARTNERS PARAM1
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SYSTEM_ITEMS MSI2
WHERE PARAM.SOURCE_ORG_ID IS NOT NULL
AND PARAM.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND PARAM.SR_TP_ID = ITEMS.ORGANIZATION_ID
AND PARAM.PARTNER_TYPE = 3
AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID
AND PARAM.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID
AND PARAM.PARTNER_TYPE = PARAM1.PARTNER_TYPE
AND ITEMS.PLAN_ID = -1
AND MSI2.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID
AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID
AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MSI2.PLAN_ID = ITEMS.PLAN_ID