VIEW: APPS.MSC_BOD_SO_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.organization_id 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.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, 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 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id region_id, 1 region_type FROM msc_plan_organizations_v plans, msc_sr_assignments msra, msc_sourcing_rules msr, msc_sr_receipt_org receipt_org, msc_sr_source_org source_org, msc_system_items items, msc_sales_order_items so WHERE msra.assignment_type = 6 AND /* ITEM-ORG */ 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 msra.partner_id IS NOT NULL AND msra.ship_to_site_id IS NOT NULL AND msra.partner_id = so.customer_id AND msra.ship_to_site_id = so.ship_to_site_use_id AND items.plan_id = plans.plan_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND items.inventory_item_id = msra.inventory_item_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 = 6) UNION SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR * MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX( * SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) * USE_NL(MSRA, TP, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) USE_HASH(REG) */ /* LEVEL = 2, ASSIGNMENT_TYPE = 9, ITEM-REGION */ items.inventory_item_id inventory_item_id, items.organization_id 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.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, 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, region_type FROM msc_plan_organizations_v plans, msc_sr_assignments msra, msc_sourcing_rules msr, msc_sr_receipt_org receipt_org, msc_sr_source_org source_org, msc_system_items items, msc_sales_order_items so, msc_tp_site_id_lid tp, msc_temp_region_locations reg 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.plan_id = plans.plan_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND items.inventory_item_id = msra.inventory_item_id AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND so.ship_to_site_use_id = tp.tp_site_id AND so.sr_instance_id = tp.sr_instance_id AND tp.location_id = reg.location_id AND tp.sr_instance_id = reg.sr_instance_id AND reg.region_id = msra.region_id AND reg.sr_instance_id = nvl(msra.sr_instance_id, reg.sr_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 = 9) UNION SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR * MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX( * SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(CAT MSC_ITEM_CATEGORIES_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, items.organization_id 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.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, 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, 1 region_type 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_sales_order_items so, msc_system_items items, msc_item_categories cat WHERE msra.assignment_type = 5 AND /* CATEGORY-ORG */ 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 msra.partner_id IS NOT NULL AND items.plan_id = plans.plan_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND cat.sr_instance_id = items.sr_instance_id AND cat.organization_id = items.organization_id AND cat.inventory_item_id = items.inventory_item_id AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND msra.partner_id = so.customer_id AND msra.ship_to_site_id = so.ship_to_site_use_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 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 = 4, ASSIGNMENT_TYPE = 3,ITEM */ items.inventory_item_id inventory_item_id, items.organization_id 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.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, 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, 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, 1 region_type FROM msc_plan_organizations_v plans, msc_sr_assignments msra, msc_sourcing_rules msr, msc_sr_receipt_org receipt_org, msc_sr_source_org source_org, msc_system_items items, msc_sales_order_items so 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_id = msra.sourcing_rule_id AND msr.sourcing_rule_type = 1 AND items.plan_id = plans.plan_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND items.inventory_item_id = msra.inventory_item_id AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_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) INDEX(TP MSC_TP_SITE_ID_LID_N1) */ /* USE_NL(PLANS, SO,ITEMS, MSRA, MSR, CAT, TP, RECEIPT_ORG, SOURCE_ORG) */ /* LEVEL = 5, ASSIGNMENT_TYPE = 8, CATG-REGION */ items.inventory_item_id inventory_item_id, items.organization_id organization_id, items.sr_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, source_org.ship_method ship_method, 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, region_type 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_sales_order_items so, msc_system_items items, msc_tp_site_id_lid tp, msc_temp_region_locations reg WHERE msra.assignment_type = 8 AND /* CATEGORY-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.plan_id = plans.plan_id AND cat.organization_id = plans.planned_organization AND cat.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND cat.sr_instance_id = so.sr_instance_id AND cat.organization_id = so.organization_id AND cat.inventory_item_id = so.inventory_item_id AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND so.ship_to_site_use_id = tp.tp_site_id AND so.sr_instance_id = tp.sr_instance_id AND tp.location_id = reg.location_id AND tp.sr_instance_id = reg.sr_instance_id AND msra.region_id = decode(cat.inventory_item_id, -1, -1, reg.region_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 = 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 = 6, ASSIGNMENT_TYPE = 2, CATG*/ items.inventory_item_id inventory_item_id, items.organization_id 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.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, 6 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, 1 region_type 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, msc_sales_order_items so WHERE msra.assignment_type = 2 AND /* CATEGORY */ 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 = plans.sr_instance_id AND cat.sr_instance_id = items.sr_instance_id AND cat.organization_id = items.organization_id AND cat.inventory_item_id = items.inventory_item_id AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND cat.category_set_id = msra.category_set_id AND cat.category_name = msra.category_name AND plans.include_salesorder = 1 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(CAT MSC_ITEM_CATEGORIES_N4) 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, items.organization_id 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.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, 7 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, 1 region_type FROM msc_plan_organizations_v plans, msc_sr_assignments msra, msc_sourcing_rules msr, msc_sr_receipt_org receipt_org, msc_sr_source_org source_org, msc_system_items items, msc_sales_order_items so WHERE msra.assignment_type = 4 AND /* ORG */ 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 msra.partner_id IS NOT NULL AND items.plan_id = plans.plan_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND msra.partner_id = so.customer_id AND msra.ship_to_site_id = so.ship_to_site_use_id AND msra.sr_instance_id = so.sr_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 = 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_HASH(SO) */ /* USE_NL(MSRA, REG, TP, MSR, RECEIPT_ORG, SOURCE_ORG, ITEMS) USE_HASH(SO) */ /* LEVEL = 8, ASSIGNMENT_TYPE = 7, REGION */ items.inventory_item_id inventory_item_id, items.organization_id 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.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, 8 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, region_type 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_temp_region_locations reg, msc_tp_site_id_lid tp, msc_sales_order_items so, msc_system_items items WHERE msra.assignment_type = 7 AND /* 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.plan_id = plans.plan_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND so.ship_to_site_use_id = tp.tp_site_id AND so.sr_instance_id = tp.sr_instance_id AND tp.location_id = reg.location_id AND tp.sr_instance_id = reg.sr_instance_id AND reg.region_id = msra.region_id AND reg.sr_instance_id = msra.sr_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 = 7) UNION SELECT /* ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) * INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX( SOURCE_ORG * MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA , MSR, * RECEIPT_ORG, SOURCE_ORG, ITEMS) */ /* LEVEL = 9, ASSIGNMENT_TYPE = 1, GLOBAL */ items.inventory_item_id inventory_item_id, items.organization_id 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.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, 9 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, so.customer_id customer_id, so.ship_to_site_use_id customer_site_id, msra.region_id, 1 region_type FROM msc_plan_organizations_v plans, msc_sr_assignments msra, msc_sourcing_rules msr, msc_sr_receipt_org receipt_org, msc_sr_source_org source_org, msc_system_items items, msc_sales_order_items so WHERE msra.assignment_type = 1 /* SITE */ 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 items.plan_id = plans.plan_id AND items.sr_instance_id = so.sr_instance_id AND items.organization_id = so.organization_id AND items.inventory_item_id = so.inventory_item_id AND items.organization_id = plans.planned_organization AND items.sr_instance_id = plans.sr_instance_id AND plans.include_salesorder = 1 AND items.organization_id = nvl(receipt_org.sr_receipt_org, items.organization_id) AND items.sr_instance_id = nvl(receipt_org.receipt_org_instance_id, items.sr_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 = 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.ORGANIZATION_ID 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.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
, 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 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID REGION_ID
, 1 REGION_TYPE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SALES_ORDER_ITEMS SO
WHERE MSRA.ASSIGNMENT_TYPE = 6
AND /* ITEM-ORG */ 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 MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.PARTNER_ID = SO.CUSTOMER_ID
AND MSRA.SHIP_TO_SITE_ID = SO.SHIP_TO_SITE_USE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_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 = 6) UNION SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR * MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX( * SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) * USE_NL(MSRA
, TP
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) USE_HASH(REG) */ /* LEVEL = 2
, ASSIGNMENT_TYPE = 9
, ITEM-REGION */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID 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.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
, 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, REGION_TYPE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SALES_ORDER_ITEMS SO
, MSC_TP_SITE_ID_LID TP
, MSC_TEMP_REGION_LOCATIONS REG
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.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND SO.SHIP_TO_SITE_USE_ID = TP.TP_SITE_ID
AND SO.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND TP.LOCATION_ID = REG.LOCATION_ID
AND TP.SR_INSTANCE_ID = REG.SR_INSTANCE_ID
AND REG.REGION_ID = MSRA.REGION_ID
AND REG.SR_INSTANCE_ID = NVL(MSRA.SR_INSTANCE_ID
, REG.SR_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 = 9) UNION SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR * MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX( * SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) INDEX(CAT MSC_ITEM_CATEGORIES_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
, ITEMS.ORGANIZATION_ID 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.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
, 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, 1 REGION_TYPE
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_SALES_ORDER_ITEMS SO
, MSC_SYSTEM_ITEMS ITEMS
, MSC_ITEM_CATEGORIES CAT
WHERE MSRA.ASSIGNMENT_TYPE = 5
AND /* CATEGORY-ORG */ 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 MSRA.PARTNER_ID IS NOT NULL
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND CAT.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND CAT.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND MSRA.PARTNER_ID = SO.CUSTOMER_ID
AND MSRA.SHIP_TO_SITE_ID = SO.SHIP_TO_SITE_USE_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 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 = 4
, ASSIGNMENT_TYPE = 3
, ITEM */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID 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.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
, 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
, 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, 1 REGION_TYPE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SALES_ORDER_ITEMS SO
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_ID = MSRA.SOURCING_RULE_ID
AND MSR.SOURCING_RULE_TYPE = 1
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_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) INDEX(TP MSC_TP_SITE_ID_LID_N1) */ /* USE_NL(PLANS
, SO
, ITEMS
, MSRA
, MSR
, CAT
, TP
, RECEIPT_ORG
, SOURCE_ORG) */ /* LEVEL = 5
, ASSIGNMENT_TYPE = 8
, CATG-REGION */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_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
, SOURCE_ORG.SHIP_METHOD SHIP_METHOD
, 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, REGION_TYPE
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_SALES_ORDER_ITEMS SO
, MSC_SYSTEM_ITEMS ITEMS
, MSC_TP_SITE_ID_LID TP
, MSC_TEMP_REGION_LOCATIONS REG
WHERE MSRA.ASSIGNMENT_TYPE = 8
AND /* CATEGORY-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.PLAN_ID = PLANS.PLAN_ID
AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND CAT.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND CAT.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND CAT.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND SO.SHIP_TO_SITE_USE_ID = TP.TP_SITE_ID
AND SO.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND TP.LOCATION_ID = REG.LOCATION_ID
AND TP.SR_INSTANCE_ID = REG.SR_INSTANCE_ID
AND MSRA.REGION_ID = DECODE(CAT.INVENTORY_ITEM_ID
, -1
, -1
, REG.REGION_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 = 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 = 6
, ASSIGNMENT_TYPE = 2
, CATG*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID 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.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
, 6 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, 1 REGION_TYPE
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
, MSC_SALES_ORDER_ITEMS SO
WHERE MSRA.ASSIGNMENT_TYPE = 2
AND /* CATEGORY */ 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 = PLANS.SR_INSTANCE_ID
AND CAT.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND CAT.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND PLANS.INCLUDE_SALESORDER = 1
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(CAT MSC_ITEM_CATEGORIES_N4) 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
, ITEMS.ORGANIZATION_ID 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.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
, 7 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, 1 REGION_TYPE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SALES_ORDER_ITEMS SO
WHERE MSRA.ASSIGNMENT_TYPE = 4
AND /* ORG */ 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 MSRA.PARTNER_ID IS NOT NULL
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND MSRA.PARTNER_ID = SO.CUSTOMER_ID
AND MSRA.SHIP_TO_SITE_ID = SO.SHIP_TO_SITE_USE_ID
AND MSRA.SR_INSTANCE_ID = SO.SR_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 = 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_HASH(SO) */ /* USE_NL(MSRA
, REG
, TP
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, ITEMS) USE_HASH(SO) */ /* LEVEL = 8
, ASSIGNMENT_TYPE = 7
, REGION */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID 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.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
, 8 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, REGION_TYPE
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_TEMP_REGION_LOCATIONS REG
, MSC_TP_SITE_ID_LID TP
, MSC_SALES_ORDER_ITEMS SO
, MSC_SYSTEM_ITEMS ITEMS
WHERE MSRA.ASSIGNMENT_TYPE = 7
AND /* 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.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND SO.SHIP_TO_SITE_USE_ID = TP.TP_SITE_ID
AND SO.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND TP.LOCATION_ID = REG.LOCATION_ID
AND TP.SR_INSTANCE_ID = REG.SR_INSTANCE_ID
AND REG.REGION_ID = MSRA.REGION_ID
AND REG.SR_INSTANCE_ID = MSRA.SR_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 = 7) UNION SELECT /* ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) * INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX( SOURCE_ORG * MSC_SR_SOURCE_ORG_U3 ) INDEX(ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, * RECEIPT_ORG
, SOURCE_ORG
, ITEMS) */ /* LEVEL = 9
, ASSIGNMENT_TYPE = 1
, GLOBAL */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID 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.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
, 9 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
, SO.CUSTOMER_ID CUSTOMER_ID
, SO.SHIP_TO_SITE_USE_ID CUSTOMER_SITE_ID
, MSRA.REGION_ID
, 1 REGION_TYPE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SALES_ORDER_ITEMS SO
WHERE MSRA.ASSIGNMENT_TYPE = 1 /* SITE */
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 ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.SR_INSTANCE_ID = SO.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = SO.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SO.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PLANS.INCLUDE_SALESORDER = 1
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_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 = 1)