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_TASK_SR_LEVELS_V

Source

View Text - Preformatted


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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG, COMP_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 , 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 , PLANS.COMPILE_DESIGNATOR , PLANS.ORGANIZATION_ID , ITEMS.MRP_PLANNING_CODE , ITEMS.INVENTORY_ITEM_FLAG , ITEMS.ENG_ITEM_FLAG , ITEMS.BOM_ITEM_TYPE , ITEMS.PLANNING_MAKE_BUY_CODE , ITEMS.PRIMARY_UOM_CODE , COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID 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 MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION 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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG, COMP_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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.INVENTORY_ITEM_FLAG, ITEMS.ENG_ITEM_FLAG, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID 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 = 3 /* ITEM */ AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS, COMP_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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.INVENTORY_ITEM_FLAG, ITEMS.ENG_ITEM_FLAG, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, MRP_SR_ASSIGNMENTS MSRA, MRP_SOURCING_RULES MSR, MRP_SR_RECEIPT_ORG RECEIPT_ORG, MRP_SR_SOURCE_ORG SOURCE_ORG, MTL_SYSTEM_ITEMS ITEMS, MTL_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID , ITEMS.ORGANIZATION_ID) AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID 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 = 4 /* ORG */ AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_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(CAT MTL_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, CAT, ITEMS,COMP_ITEMS) */ 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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.INVENTORY_ITEM_FLAG, ITEMS.ENG_ITEM_FLAG, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, MRP_SR_ASSIGNMENTS MSRA, MRP_SOURCING_RULES MSR, MRP_SR_RECEIPT_ORG RECEIPT_ORG, MRP_SR_SOURCE_ORG SOURCE_ORG, MTL_ITEM_CATEGORIES CAT, MTL_SYSTEM_ITEMS ITEMS, MTL_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID 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 MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_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(CAT MTL_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, CAT, ITEMS, RECEIPT_ORG, SOURCE_ORG, COMP_ITEMS) */ 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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.INVENTORY_ITEM_FLAG, ITEMS.ENG_ITEM_FLAG, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, MRP_SR_ASSIGNMENTS MSRA, MRP_SOURCING_RULES MSR, MTL_ITEM_CATEGORIES CAT, MTL_SYSTEM_ITEMS ITEMS, MRP_SR_RECEIPT_ORG RECEIPT_ORG, MRP_SR_SOURCE_ORG SOURCE_ORG, MTL_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID 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 = 2 /* ITEM CATEGORY */ AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID UNION ALL SELECT /*+ ORDERED USE_NL(MSI, PARAM, COMP_ITEMS) */ MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, 6 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, MSI.SOURCE_ORGANIZATION_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), 100, 1, TO_DATE(1, 'j'), TO_DATE(NULL), TO_NUMBER(NULL), 2 SOURCING_LEVEL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), PARAM.ORGANIZATION_CODE, TO_CHAR(NULL), PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, MSI.MRP_PLANNING_CODE, MSI.INVENTORY_ITEM_FLAG, MSI.ENG_ITEM_FLAG, MSI.BOM_ITEM_TYPE, MSI.PLANNING_MAKE_BUY_CODE, MSI.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, MTL_SYSTEM_ITEMS MSI, MTL_PARAMETERS PARAM, MTL_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.ORGANIZATION_ID = MSI.SOURCE_ORGANIZATION_ID AND COMP_ITEMS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.SOURCE_ORGANIZATION_ID IS NOT NULL AND MSI.SOURCE_ORGANIZATION_ID = PARAM.ORGANIZATION_ID AND MSI.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL SELECT /*+ ORDERED INDEX(PARAM MTL_PARAMETERS_U1) USE_NL(PARAM, ITEMS, PARAM1, COMP_ITEMS) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, 4 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, PARAM.SOURCE_ORGANIZATION_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), 100, 1, TO_DATE(1, 'j'), TO_DATE(NULL), TO_NUMBER(NULL), 9 SOURCING_LEVEL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), PARAM1.ORGANIZATION_CODE, TO_CHAR(NULL), PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.INVENTORY_ITEM_FLAG, ITEMS.ENG_ITEM_FLAG, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS, MTL_PARAMETERS PARAM, MTL_SYSTEM_ITEMS ITEMS, MTL_PARAMETERS PARAM1, MTL_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.ORGANIZATION_ID = PARAM.SOURCE_ORGANIZATION_ID AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND PARAM.SOURCE_ORGANIZATION_ID IS NOT NULL AND PARAM.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND PARAM.SOURCE_ORGANIZATION_ID = PARAM1.ORGANIZATION_ID AND PARAM1.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION 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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG, COMP_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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.INVENTORY_ITEM_FLAG, ITEMS.ENG_ITEM_FLAG, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.PRIMARY_UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE FROM MRP_PLAN_ORGANIZATIONS_V PLANS , 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 1 /* SITE */ AND NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID

View Text - HTML Formatted

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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
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 MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION 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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
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 = 3 /* ITEM */
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS
, COMP_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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS ITEMS
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
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 = 4 /* ORG */
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_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(CAT MTL_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, CAT
, ITEMS
, COMP_ITEMS) */ 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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_ITEM_CATEGORIES CAT
, MTL_SYSTEM_ITEMS ITEMS
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
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 MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_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(CAT MTL_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, CAT
, ITEMS
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS) */ 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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MTL_ITEM_CATEGORIES CAT
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
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 = 2 /* ITEM CATEGORY */
AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID UNION ALL SELECT /*+ ORDERED USE_NL(MSI
, PARAM
, COMP_ITEMS) */ MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, 6 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, MSI.SOURCE_ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 2 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PARAM.ORGANIZATION_CODE
, TO_CHAR(NULL)
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, MSI.MRP_PLANNING_CODE
, MSI.INVENTORY_ITEM_FLAG
, MSI.ENG_ITEM_FLAG
, MSI.BOM_ITEM_TYPE
, MSI.PLANNING_MAKE_BUY_CODE
, MSI.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MTL_SYSTEM_ITEMS MSI
, MTL_PARAMETERS PARAM
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.ORGANIZATION_ID = MSI.SOURCE_ORGANIZATION_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.SOURCE_ORGANIZATION_ID IS NOT NULL
AND MSI.SOURCE_ORGANIZATION_ID = PARAM.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL SELECT /*+ ORDERED INDEX(PARAM MTL_PARAMETERS_U1) USE_NL(PARAM
, ITEMS
, PARAM1
, COMP_ITEMS) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, 4 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 9 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PARAM1.ORGANIZATION_CODE
, TO_CHAR(NULL)
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MTL_PARAMETERS PARAM
, MTL_SYSTEM_ITEMS ITEMS
, MTL_PARAMETERS PARAM1
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.ORGANIZATION_ID = PARAM.SOURCE_ORGANIZATION_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND PARAM.SOURCE_ORGANIZATION_ID IS NOT NULL
AND PARAM.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND PARAM.SOURCE_ORGANIZATION_ID = PARAM1.ORGANIZATION_ID
AND PARAM1.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION 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(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 1 /* SITE */
AND NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID