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_TASK_SR_LEVELS_V

Source

View Text - Preformatted


SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , ITEMS.SR_INSTANCE_ID , PLANS.PLAN_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 , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , TO_NUMBER(NULL) , 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.BOM_ITEM_TYPE , ITEMS.PLANNING_MAKE_BUY_CODE , ITEMS.UOM_CODE , COMP_ITEMS.MRP_PLANNING_CODE ,ITEMS.DRP_PLANNED ,COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_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.PLAN_ID = -1 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 MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND exists (select 1 from msc_sr_assignments where assignment_type = 6) UNION ALL /* 2nd Union */ SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(COMP_ITEMS MSC_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, ITEMS.SR_INSTANCE_ID, PLANS.PLAN_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, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), 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.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, ITEMS.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_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.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 /* ITEM */ AND ITEMS.PLAN_ID = -1 AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND exists (select 1 from msc_sr_assignments where assignment_type = 3) UNION ALL /* 3rd Union */ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, PLANS.PLAN_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, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), 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.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, ITEMS.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND ITEMS.PLAN_ID = -1 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 /* ORG */ AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND exists (select 1 from msc_sr_assignments where assignment_type = 4) UNION ALL /* 4th Union */ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, CAT, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG, COMP_ITEMS) */ CAT.INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID, CAT.SR_INSTANCE_ID, PLANS.PLAN_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSRA.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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, ITEMS.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG, MSC_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND ITEMS.PLAN_ID = -1 AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME 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.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, CAT.SR_INSTANCE_ID) AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG,CAT.ORGANIZATION_ID) AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 5 /*ITEM CAT-ORG*/ AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND exists (select 1 from msc_sr_assignments where assignment_type = 5) UNION ALL /* 5th Union */ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MSC_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, CAT.SR_INSTANCE_ID, PLANS.PLAN_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, 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, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, ITEMS.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SOURCING_RULES MSR, MSC_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG, MSC_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND ITEMS.PLAN_ID = -1 AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME 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.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 = 2 /* ITEM CATEGORY */ AND CAT.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND exists (select 1 from msc_sr_assignments where assignment_type = 2) UNION ALL /* 6th Union */ SELECT /*+ ORDERED INDEX(PARAM MSC_TRADING_PARTNERS_U2) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) */ MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, MSI.SR_INSTANCE_ID, PLANS.PLAN_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, 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.BOM_ITEM_TYPE, MSI.PLANNING_MAKE_BUY_CODE, MSI.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, MSI.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SYSTEM_ITEMS MSI, MSC_TRADING_PARTNERS PARAM, MSC_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.ORGANIZATION_ID = MSI.SOURCE_ORG_ID AND COMP_ITEMS.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID AND COMP_ITEMS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.SOURCE_ORG_ID IS NOT NULL AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID AND PARAM.PARTNER_TYPE = 3 AND MSI.PLAN_ID = -1 AND MSI.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSI.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL /* 7th Union */ SELECT/*+ ORDERED INDEX(PARAM MSC_TRADING_PARTNERS_U2) INDEX(PARAM MSC_TRADING_PARTNERS_U2) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(PARAM1, PARAM, ITEMS, COMP_ITEMS ) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, PLANS.PLAN_ID, 4 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, PARAM.SOURCE_ORG_ID, ITEMS.SR_INSTANCE_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.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, ITEMS.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_TRADING_PARTNERS PARAM, MSC_SYSTEM_ITEMS ITEMS, MSC_TRADING_PARTNERS PARAM1, MSC_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND ITEMS.PLAN_ID = -1 AND 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.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID AND PARAM1.PARTNER_TYPE = 3 AND PARAM1.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND PARAM1.SR_TP_ID = PLANS.PLANNED_ORGANIZATION UNION ALL /* 8th Union*/ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG, COMP_ITEMS) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, PLANS.PLAN_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, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), 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.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, COMP_ITEMS.MRP_PLANNING_CODE, ITEMS.DRP_PLANNED, COMP_ITEMS.DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS , 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_SYSTEM_ITEMS COMP_ITEMS WHERE COMP_ITEMS.PLAN_ID = -1 AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, ITEMS.ORGANIZATION_ID) AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND COMP_ITEMS.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_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_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) = ITEMS.SR_INSTANCE_ID AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID)= ITEMS.ORGANIZATION_ID AND ITEMS.PLAN_ID = -1 AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND exists (select 1 from msc_sr_assignments where assignment_type = 1)

View Text - HTML Formatted

SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_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
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 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.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_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.PLAN_ID = -1
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 MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 6) UNION ALL /* 2ND UNION */ SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(COMP_ITEMS MSC_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
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_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
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 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.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_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.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 /* ITEM */
AND ITEMS.PLAN_ID = -1
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 3) UNION ALL /* 3RD UNION */ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_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
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 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.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND ITEMS.PLAN_ID = -1
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 /* ORG */
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 4) UNION ALL /* 4TH UNION */ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, CAT
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS) */ CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSRA.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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
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.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, CAT.SR_INSTANCE_ID)
AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 5 /*ITEM CAT-ORG*/
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 5) UNION ALL /* 5TH UNION */ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MSC_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
, CAT.SR_INSTANCE_ID
, PLANS.PLAN_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
, 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
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
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.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 = 2 /* ITEM CATEGORY */
AND CAT.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 2) UNION ALL /* 6TH UNION */ SELECT /*+ ORDERED INDEX(PARAM MSC_TRADING_PARTNERS_U2) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) */ MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SR_INSTANCE_ID
, PLANS.PLAN_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
, 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.BOM_ITEM_TYPE
, MSI.PLANNING_MAKE_BUY_CODE
, MSI.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, MSI.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS PARAM
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.ORGANIZATION_ID = MSI.SOURCE_ORG_ID
AND COMP_ITEMS.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.SOURCE_ORG_ID IS NOT NULL
AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID
AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID
AND PARAM.PARTNER_TYPE = 3
AND MSI.PLAN_ID = -1
AND MSI.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL /* 7TH UNION */ SELECT/*+ ORDERED INDEX(PARAM MSC_TRADING_PARTNERS_U2) INDEX(PARAM MSC_TRADING_PARTNERS_U2) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(PARAM1
, PARAM
, ITEMS
, COMP_ITEMS ) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_ID
, 4 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORG_ID
, ITEMS.SR_INSTANCE_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.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_TRADING_PARTNERS PARAM
, MSC_SYSTEM_ITEMS ITEMS
, MSC_TRADING_PARTNERS PARAM1
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND ITEMS.PLAN_ID = -1
AND 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.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID
AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID
AND PARAM1.PARTNER_TYPE = 3
AND PARAM1.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PARAM1.SR_TP_ID = PLANS.PLANNED_ORGANIZATION UNION ALL /* 8TH UNION*/ SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_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
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 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.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
, ITEMS.DRP_PLANNED
, COMP_ITEMS.DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, 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_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_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_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID) = ITEMS.SR_INSTANCE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)= ITEMS.ORGANIZATION_ID
AND ITEMS.PLAN_ID = -1
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 1)