VIEW: APPS.MRP_ITEM_SOURCING_LEVELS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_N3) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA,ITEMS,MSR,RECEIPT_ORG, SOURCE_ORG, SHIP)*/ MSRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID ORGANIZATION_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.VENDOR_ID , SOURCE_ORG.VENDOR_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , MSRA.CATEGORY_ID , 1 SOURCING_LEVEL , MSRA.ASSIGNMENT_ID , MSR.SOURCING_RULE_ID , MSR.SOURCING_RULE_NAME , SOURCE_ORG.SOURCE_TYPE , TO_CHAR(NULL) , MSR.DESCRIPTION , MSRA.CUSTOMER_ID CUSTOMER_ID , MSRA.SHIP_TO_SITE_ID SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE 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 NVL(trunc(RECEIPT_ORG.DISABLE_DATE),trunc(sysdate) + 1) >= trunc(sysdate) 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 UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_N3) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA,ITEMS,MSR,RECEIPT_ORG, SOURCE_ORG, SHIP)*/ MSRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID ORGANIZATION_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.VENDOR_ID , SOURCE_ORG.VENDOR_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , MSRA.CATEGORY_ID , 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 , TO_NUMBER(NULL) CUSTOMER_ID , TO_NUMBER(NULL) SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE 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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE),TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE) 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 = 3 /* ITEM */ AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, SHIP, ITEMS) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID ORGANIZATION_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.VENDOR_ID , SOURCE_ORG.VENDOR_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , MSRA.CATEGORY_ID , 8 SOURCING_LEVEL , MSRA.ASSIGNMENT_ID , MSR.SOURCING_RULE_ID , MSR.SOURCING_RULE_NAME , SOURCE_ORG.SOURCE_TYPE , TO_CHAR(NULL) , MSR.DESCRIPTION , MSRA.CUSTOMER_ID CUSTOMER_ID , MSRA.SHIP_TO_SITE_ID SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE FROM MRP_SR_ASSIGNMENTS MSRA, MRP_SOURCING_RULES MSR, MRP_SR_RECEIPT_ORG RECEIPT_ORG, MRP_SR_SOURCE_ORG SOURCE_ORG, MTL_INTERORG_SHIP_METHODS SHIP, MTL_SYSTEM_ITEMS ITEMS 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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE),TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE) 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 = 4 /* ORG */ AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA,ITEMS,MSR,RECEIPT_ORG, SOURCE_ORG, SHIP)*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID ORGANIZATION_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , SOURCE_ORG.SOURCE_ORGANIZATION_ID , SOURCE_ORG.VENDOR_ID , SOURCE_ORG.VENDOR_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , SHIP.INTRANSIT_TIME , SHIP.SHIP_METHOD , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , MSRA.CATEGORY_ID , 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 , TO_NUMBER(NULL) CUSTOMER_ID , TO_NUMBER(NULL) SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE 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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE),TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE) 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 = 1 /* SITE */ UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA, CAT, MSR, RECEIPT_ORG, SOURCE_ORG, SHIP)*/ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID ORGANIZATION_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.VENDOR_ID, SOURCE_ORG.VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, SHIP.INTRANSIT_TIME, SHIP.SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, MSRA.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.CUSTOMER_ID , MSRA.SHIP_TO_SITE_ID , SHIP.TIME_UOM_CODE 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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE),TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE) 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 UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA, CAT, MSR, RECEIPT_ORG, SOURCE_ORG, SHIP)*/ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID ORGANIZATION_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.VENDOR_ID, SOURCE_ORG.VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, SHIP.INTRANSIT_TIME, SHIP.SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, MSRA.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 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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE),TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE) 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 = 2 /* ITEM CATEGORY */ UNION ALL SELECT /*+ ORDERED USE_NL(PARAM) */ MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, 6 ASSIGNMENT_TYPE, MAS.ASSIGNMENT_SET_ID, 3 SOURCING_RULE_TYPE, MSI.SOURCE_ORGANIZATION_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) FROM MTL_SYSTEM_ITEMS MSI, MTL_PARAMETERS PARAM, MRP_ASSIGNMENT_SETS MAS WHERE MSI.SOURCE_ORGANIZATION_ID IS NOT NULL AND MSI.SOURCE_ORGANIZATION_ID = PARAM.ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED INDEX(PARAM MTL_PARAMETERS_U1) USE_NL(ITEMS, MSI2, PARAM1)*/ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, 4 ASSIGNMENT_TYPE, MAS.ASSIGNMENT_SET_ID, 3 SOURCING_RULE_TYPE, PARAM.SOURCE_ORGANIZATION_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) FROM MTL_PARAMETERS PARAM, MTL_SYSTEM_ITEMS ITEMS, MTL_SYSTEM_ITEMS MSI2, MTL_PARAMETERS PARAM1, MRP_ASSIGNMENT_SETS MAS WHERE PARAM.SOURCE_ORGANIZATION_ID IS NOT NULL AND PARAM.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND PARAM.SOURCE_ORGANIZATION_ID = PARAM1.ORGANIZATION_ID AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORGANIZATION_ID AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID

View Text - HTML Formatted

SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_N3) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, SHIP)*/ MSRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 1 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, MSRA.CUSTOMER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE)
, TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE)
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 UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_N3) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, SHIP)*/ MSRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 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
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_NUMBER(NULL) SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE)
, TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE)
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 = 3 /* ITEM */
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, SHIP
, ITEMS) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 8 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, MSRA.CUSTOMER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
FROM MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_INTERORG_SHIP_METHODS SHIP
, MTL_SYSTEM_ITEMS ITEMS
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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE)
, TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE)
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 = 4 /* ORG */
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, SHIP)*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 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
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_NUMBER(NULL) SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE)
, TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE)
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 = 1 /* SITE */ UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA
, CAT
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, SHIP)*/ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.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.CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID
, SHIP.TIME_UOM_CODE
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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE)
, TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE)
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 UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(SHIP MTL_INTERORG_SHIP_METHODS_N2) USE_NL(MSRA
, CAT
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, SHIP)*/ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, SHIP.INTRANSIT_TIME
, SHIP.SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.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
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 NVL(TRUNC(RECEIPT_ORG.DISABLE_DATE)
, TRUNC(SYSDATE) + 1) >= TRUNC(SYSDATE)
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 = 2 /* ITEM CATEGORY */ UNION ALL SELECT /*+ ORDERED USE_NL(PARAM) */ MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, 6 ASSIGNMENT_TYPE
, MAS.ASSIGNMENT_SET_ID
, 3 SOURCING_RULE_TYPE
, MSI.SOURCE_ORGANIZATION_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)
FROM MTL_SYSTEM_ITEMS MSI
, MTL_PARAMETERS PARAM
, MRP_ASSIGNMENT_SETS MAS
WHERE MSI.SOURCE_ORGANIZATION_ID IS NOT NULL
AND MSI.SOURCE_ORGANIZATION_ID = PARAM.ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED INDEX(PARAM MTL_PARAMETERS_U1) USE_NL(ITEMS
, MSI2
, PARAM1)*/ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, 4 ASSIGNMENT_TYPE
, MAS.ASSIGNMENT_SET_ID
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORGANIZATION_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)
FROM MTL_PARAMETERS PARAM
, MTL_SYSTEM_ITEMS ITEMS
, MTL_SYSTEM_ITEMS MSI2
, MTL_PARAMETERS PARAM1
, MRP_ASSIGNMENT_SETS MAS
WHERE PARAM.SOURCE_ORGANIZATION_ID IS NOT NULL
AND PARAM.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND PARAM.SOURCE_ORGANIZATION_ID = PARAM1.ORGANIZATION_ID
AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID