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_FCST_BOD_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(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, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1 ORGANIZATION_ID, MSRA.SR_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.SOURCE_PARTNER_ID VENDOR_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, -23453 CATEGORY_ID, 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, TO_CHAR(-23453) SOURCE_ORG_CODE, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.ORGANIZATION_ID OWNING_ORG_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 /* ITEM-ORG */ MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NULL 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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 6, item-customer, with site */ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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.SOURCE_PARTNER_ID VENDOR_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, -23453 CATEGORY_ID, 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, TO_CHAR(-23453) SOURCE_ORG_CODE, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.ORGANIZATION_ID OWNING_ORG_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 /* ITEM-ORG */ MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NOT NULL 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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, REG, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 3, assignment_type = 9, item-region, rather zone*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1 ORGANIZATION_ID, ITEMS.SR_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.SOURCE_PARTNER_ID VENDOR_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, -23453 CATEGORY_ID, 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, TO_CHAR(-23453) SOURCE_ORG_CODE, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.ORGANIZATION_ID OWNING_ORG_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_REGIONS REG, 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 */ SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.REGION_ID = REG.REGION_ID AND REG.REGION_TYPE IN (10,11) AND REG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND REG.ZONE_USAGE = 1 /* For DP Forecast */ AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 4, assignment_type = 5, catg-customer/site */ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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.SOURCE_PARTNER_ID VENDOR_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, -23453 CATEGORY_ID, 4 SOURCING_LEVEL , MSRA.ASSIGNMENT_ID ASSIGNMENT_ID, MSR.SOURCING_RULE_ID SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE, TO_CHAR(-23453) SOURCE_ORG_CODE, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.ORGANIZATION_ID OWNING_ORG_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 */ MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NOT NULL AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_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_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ ITEMS.PLAN_ID = PLANS.PLAN_ID 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_SET_ID = MSRA.CATEGORY_SET_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 5, catg-customer */ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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.SOURCE_PARTNER_ID VENDOR_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT, SOURCE_ORG.RANK RANK, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE, -23453 CATEGORY_ID, 5 SOURCING_LEVEL , MSRA.ASSIGNMENT_ID ASSIGNMENT_ID, MSR.SOURCING_RULE_ID SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE, TO_CHAR(-23453) SOURCE_ORG_CODE, MSR.DESCRIPTION SR_DESCRIPTION, PLANS.ORGANIZATION_ID OWNING_ORG_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 */ MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NULL AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_TYPE = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_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_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ ITEMS.PLAN_ID = PLANS.PLAN_ID 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_SET_ID = MSRA.CATEGORY_SET_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 6, assignment_type = 3, item */ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, -23453, 6 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, REG, MSR, RECEIPT_ORG, SOURCE_ORG, CAT, ITEMS) */ /* level = 7, assignment_type = 8, catg-region rather zone*/ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, -23453, 7 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_REGIONS REG, 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 = 8 AND /* CATEGORY-REGION */ SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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 ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND CAT.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND CAT.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND CAT.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND MSRA.CATEGORY_SET_ID = CAT.CATEGORY_SET_ID AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.REGION_ID = REG.REGION_ID AND REG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND REG.REGION_TYPE IN (10,11) AND REG.ZONE_USAGE = 1 AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 8, assignment_type = 2, catg*/ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, CAT.SR_CATEGORY_ID, 8 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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 = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_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 CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 4, customer no site*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, -23453, 9 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NULL AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL /*AND SOURCE_ORG.SOURCE_PARTNER_ID IS NULL*/ 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 ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 10, assignment_type = 4, customer with site*/ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, -23453, 10 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 MSRA.PARTNER_ID IS NOT NULL AND MSRA.SHIP_TO_SITE_ID IS NOT NULL AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL 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 ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, REG, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 11, assignment_type = 7, region rather zone*/ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, -23453, 11 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_REGIONS REG, 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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL /*AND SOURCE_ORG.SOURCE_PARTNER_ID IS NULL*/ AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.REGION_ID = REG.REGION_ID AND REG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND REG.REGION_TYPE IN (10,11) AND REG.ZONE_USAGE = 1 AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* level = 12, assignment_type = 1, Global */ ITEMS.INVENTORY_ITEM_ID, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID, -1, MSRA.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, SOURCE_ORG.SHIP_METHOD SHIP_METHOD, RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, -23453, 12 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(-23453), MSR.DESCRIPTION, PLANS.ORGANIZATION_ID, MSRA.PARTNER_ID CUSTOMER_ID, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID, MSRA.REGION_ID ZONE_ID 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 AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND nvl(MSRA.ITEM_TYPE_VALUE, 1) = 1 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(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
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1 ORGANIZATION_ID
, MSRA.SR_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.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, -23453 CATEGORY_ID
, 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
, TO_CHAR(-23453) SOURCE_ORG_CODE
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 /* ITEM-ORG */ MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NULL
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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 6
, ITEM-CUSTOMER
, WITH SITE */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, -23453 CATEGORY_ID
, 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
, TO_CHAR(-23453) SOURCE_ORG_CODE
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 /* ITEM-ORG */ MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, REG
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 3
, ASSIGNMENT_TYPE = 9
, ITEM-REGION
, RATHER ZONE*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1 ORGANIZATION_ID
, ITEMS.SR_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.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, -23453 CATEGORY_ID
, 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
, TO_CHAR(-23453) SOURCE_ORG_CODE
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_REGIONS REG
, 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 */ SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.REGION_ID = REG.REGION_ID
AND REG.REGION_TYPE IN (10
, 11)
AND REG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND REG.ZONE_USAGE = 1 /* FOR DP FORECAST */
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 4
, ASSIGNMENT_TYPE = 5
, CATG-CUSTOMER/SITE */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, -23453 CATEGORY_ID
, 4 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE
, TO_CHAR(-23453) SOURCE_ORG_CODE
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 */ MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_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_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ ITEMS.PLAN_ID = PLANS.PLAN_ID
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_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 5
, CATG-CUSTOMER */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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.SOURCE_PARTNER_ID VENDOR_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT ALLOCATION_PERCENT
, SOURCE_ORG.RANK RANK
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE DISABLE_DATE
, -23453 CATEGORY_ID
, 5 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE SOURCE_TYPE
, TO_CHAR(-23453) SOURCE_ORG_CODE
, MSR.DESCRIPTION SR_DESCRIPTION
, PLANS.ORGANIZATION_ID OWNING_ORG_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 */ MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NULL
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_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_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL AND*/ ITEMS.PLAN_ID = PLANS.PLAN_ID
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_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 6
, ASSIGNMENT_TYPE = 3
, ITEM */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, -23453
, 6 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(CAT MSC_ITEM_CATEGORIES_N4) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, REG
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, CAT
, ITEMS) */ /* LEVEL = 7
, ASSIGNMENT_TYPE = 8
, CATG-REGION RATHER ZONE*/ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, -23453
, 7 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_REGIONS REG
, 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 = 8
AND /* CATEGORY-REGION */ SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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 ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND CAT.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND CAT.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND CAT.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MSRA.CATEGORY_SET_ID = CAT.CATEGORY_SET_ID
AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.REGION_ID = REG.REGION_ID
AND REG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND REG.REGION_TYPE IN (10
, 11)
AND REG.ZONE_USAGE = 1
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 8
, ASSIGNMENT_TYPE = 2
, CATG*/ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, CAT.SR_CATEGORY_ID
, 8 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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 = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_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 CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) 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 = 4
, CUSTOMER NO SITE*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, -23453
, 9 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NULL
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL /*AND SOURCE_ORG.SOURCE_PARTNER_ID IS NULL*/
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 ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 10
, ASSIGNMENT_TYPE = 4
, CUSTOMER WITH SITE*/ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, -23453
, 10 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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 MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
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 ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, REG
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 11
, ASSIGNMENT_TYPE = 7
, REGION RATHER ZONE*/ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, -23453
, 11 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_REGIONS REG
, 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 SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL /*AND SOURCE_ORG.SOURCE_PARTNER_ID IS NULL*/
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.REGION_ID = REG.REGION_ID
AND REG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND REG.REGION_TYPE IN (10
, 11)
AND REG.ZONE_USAGE = 1
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
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(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 12
, ASSIGNMENT_TYPE = 1
, GLOBAL */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, -1
, MSRA.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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, -23453
, 12 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(-23453)
, MSR.DESCRIPTION
, PLANS.ORGANIZATION_ID
, MSRA.PARTNER_ID CUSTOMER_ID
, MSRA.SHIP_TO_SITE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID ZONE_ID
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
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND /*SOURCE_ORG.SOURCE_PARTNER_ID IS NULL 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.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND NVL(MSRA.ITEM_TYPE_VALUE
, 1) = 1
AND EXISTS (SELECT '1'
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 1)