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_SO_SR_LEVELS_V

Source

View Text - Preformatted


SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 1, assignment_type = 6, item-customer, no site */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID SR_INSTANCE_ID, PLANS.PLAN_ID PLAN_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.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, 1 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, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.COMPILE_DESIGNATOR COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID OWNING_ORG_ID, ITEMS.MRP_PLANNING_CODE COMP_MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE COMP_BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE COMP_PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE COMP_PRIMARY_UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID REGION_ID, ITEMS.DRP_PLANNED COMP_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 WHERE MSRA.ASSIGNMENT_TYPE = 6 AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NOT NULL AND ITEMS.PLAN_ID = -1 AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 6 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 2, assignment_type = 9, item-region */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID SR_INSTANCE_ID, PLANS.PLAN_ID PLAN_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.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, 2 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, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.COMPILE_DESIGNATOR COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID OWNING_ORG_ID, ITEMS.MRP_PLANNING_CODE MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE PRIMARY_UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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 WHERE MSRA.ASSIGNMENT_TYPE = 9 AND /* item-region */ MSR.STATUS = 1 AND MSR.SOURCING_RULE_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_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 = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 9) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG,CAT, ITEMS) */ /* level = 3, assignment_type = 5, catg-customer/site */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID SR_INSTANCE_ID, PLANS.PLAN_ID PLAN_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.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, 3 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, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.COMPILE_DESIGNATOR COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID OWNING_ORG_ID, ITEMS.MRP_PLANNING_CODE MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE PRIMARY_UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS WHERE MSRA.ASSIGNMENT_TYPE = 5 AND /* CATEGORY-ORG */ 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_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NOT NULL 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 = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 5 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 4, assignment_type = 3, item */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT, SOURCE_ORG.RANK, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, 4 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, MSR.DESCRIPTION, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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 WHERE MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */ 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 MSR.SOURCING_RULE_TYPE = 1 AND ITEMS.PLAN_ID = -1 AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 3 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, CAT, ITEMS) */ /* level = 5, assignment_type = 8, catg-region rather zone*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT, SOURCE_ORG.RANK, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, 5 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, MSR.DESCRIPTION, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_DRP_PLANNED FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SOURCING_RULES MSR, MSC_SR_SOURCE_ORG SOURCE_ORG, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS WHERE MSRA.ASSIGNMENT_TYPE = 8 AND /* CATEGORY-REGION */ 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_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND ITEMS.PLAN_ID = -1 AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 8) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, CAT, ITEMS) */ /* level = 6, assignment_type = 2, catg*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT, SOURCE_ORG.RANK, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, 6 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, MSR.DESCRIPTION, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS WHERE MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */ 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 MSR.SOURCING_RULE_TYPE = 1 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 SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME AND MSRA.CATEGORY_SET_ID= CAT.CATEGORY_SET_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 2 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 7, assignment_type = 4, customer site*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT, SOURCE_ORG.RANK, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, 7 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, MSR.DESCRIPTION, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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 WHERE MSRA.ASSIGNMENT_TYPE = 4 /* ORG */ 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_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NOT NULL AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.PLAN_ID = -1 AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 4) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 8, assignment_type = 7, region */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT, SOURCE_ORG.RANK, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, 8 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, MSR.DESCRIPTION, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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 WHERE MSRA.ASSIGNMENT_TYPE = 7 /* REGION */ AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_TYPE = 1 AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID AND ITEMS.PLAN_ID = -1 AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SOURCE_ORG.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND SOURCE_ORG.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL and exists (select 1 from msc_sr_assignments where ASSIGNMENT_TYPE = 7 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 9, assignment_type = 1, Global */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT, SOURCE_ORG.RANK, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, 9 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, MSR.DESCRIPTION, PLANS.COMPILE_DESIGNATOR, PLANS.ORGANIZATION_ID, ITEMS.MRP_PLANNING_CODE, ITEMS.BOM_ITEM_TYPE, ITEMS.PLANNING_MAKE_BUY_CODE, ITEMS.UOM_CODE, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID, ITEMS.DRP_PLANNED COMP_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 WHERE MSRA.ASSIGNMENT_TYPE = 1 /* SITE */ 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 MSR.SOURCING_RULE_TYPE = 1 AND ITEMS.PLAN_ID = -1 AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL 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(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 1
, ASSIGNMENT_TYPE = 6
, ITEM-CUSTOMER
, NO SITE */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID SR_INSTANCE_ID
, PLANS.PLAN_ID PLAN_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.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, 1 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
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.COMPILE_DESIGNATOR COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, ITEMS.MRP_PLANNING_CODE COMP_MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE COMP_BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE COMP_PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE COMP_PRIMARY_UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID REGION_ID
, ITEMS.DRP_PLANNED COMP_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
WHERE MSRA.ASSIGNMENT_TYPE = 6
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND ITEMS.PLAN_ID = -1
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 6 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 2
, ASSIGNMENT_TYPE = 9
, ITEM-REGION */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID SR_INSTANCE_ID
, PLANS.PLAN_ID PLAN_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.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, 2 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
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.COMPILE_DESIGNATOR COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, ITEMS.MRP_PLANNING_CODE MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE PRIMARY_UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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
WHERE MSRA.ASSIGNMENT_TYPE = 9
AND /* ITEM-REGION */ MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_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 = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 9) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, CAT
, ITEMS) */ /* LEVEL = 3
, ASSIGNMENT_TYPE = 5
, CATG-CUSTOMER/SITE */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID SR_INSTANCE_ID
, PLANS.PLAN_ID PLAN_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.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, 3 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
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.COMPILE_DESIGNATOR COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, ITEMS.MRP_PLANNING_CODE MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE PRIMARY_UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
WHERE MSRA.ASSIGNMENT_TYPE = 5
AND /* CATEGORY-ORG */ 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_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
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 = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 5 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 4
, ASSIGNMENT_TYPE = 3
, ITEM */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 4 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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
WHERE MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
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 MSR.SOURCING_RULE_TYPE = 1
AND ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 3 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, CAT
, ITEMS) */ /* LEVEL = 5
, ASSIGNMENT_TYPE = 8
, CATG-REGION RATHER ZONE*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 5 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_DRP_PLANNED
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
WHERE MSRA.ASSIGNMENT_TYPE = 8
AND /* CATEGORY-REGION */ 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_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ITEMS.PLAN_ID = -1
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 8) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, CAT
, ITEMS) */ /* LEVEL = 6
, ASSIGNMENT_TYPE = 2
, CATG*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 6 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
WHERE MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */
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 MSR.SOURCING_RULE_TYPE = 1
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 SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME
AND MSRA.CATEGORY_SET_ID= CAT.CATEGORY_SET_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 2 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 7
, ASSIGNMENT_TYPE = 4
, CUSTOMER SITE*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 7 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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
WHERE MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
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_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.PLAN_ID = -1
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 4) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 8
, ASSIGNMENT_TYPE = 7
, REGION */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 8 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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
WHERE MSRA.ASSIGNMENT_TYPE = 7 /* REGION */
AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID
AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID
AND ITEMS.PLAN_ID = -1
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SOURCE_ORG.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND SOURCE_ORG.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 7 ) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 9
, ASSIGNMENT_TYPE = 1
, GLOBAL */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, RECEIPT_ORG.SR_RECEIPT_ORG ORGANIZATION_ID
, RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID 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.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 9 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, ITEMS.DRP_PLANNED COMP_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
WHERE MSRA.ASSIGNMENT_TYPE = 1 /* SITE */
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 MSR.SOURCING_RULE_TYPE = 1
AND ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORG_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 1)