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_BOD_TASK_SR_LEVELS_V

Source

View Text - Preformatted


SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_N1 ) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_N1) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U3 ) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , ITEMS.SR_INSTANCE_ID , MSRA.ASSIGNMENT_TYPE , MSRA.ASSIGNMENT_SET_ID , MSR.SOURCING_RULE_TYPE , NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)) , NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)) , SOURCE_ORG.SOURCE_PARTNER_ID , SOURCE_ORG.SOURCE_PARTNER_SITE_ID , SOURCE_ORG.ALLOCATION_PERCENT , SOURCE_ORG.RANK , MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID,ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG) , SOURCE_ORG.SHIP_METHOD , MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID,ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG) , MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID,ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG) , RECEIPT_ORG.EFFECTIVE_DATE , RECEIPT_ORG.DISABLE_DATE , TO_NUMBER(NULL) , 1 SOURCING_LEVEL , MSRA.ASSIGNMENT_ID , MSR.SOURCING_RULE_ID , MSR.SOURCING_RULE_NAME , SOURCE_ORG.SOURCE_TYPE , TO_CHAR(NULL) , MSR.DESCRIPTION , PLANS.PLAN_ID , PLANS.ORGANIZATION_ID , ITEMS.FULL_PEGGING ,SOURCE_ORG.CIRCULAR_SRC ,MSRA.ITEM_TYPE_VALUE ,MSRA.REGION_ID FROM MSC_SR_ASSIGNMENTS MSRA, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG, MSC_PLAN_ORGANIZATIONS_V PLANS WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID) AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */ AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID 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 ) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), DECODE(MSR.SOURCING_RULE_TYPE, 1, 5, 4) SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */ AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSR.STATUS = 1 AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID 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 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 ) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), /* MSRA.CATEGORY_ID, MAKE IT NULL */ 8 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SOURCING_RULES MSR, MSC_SYSTEM_ITEMS ITEMS, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID) AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 4 /* ORG */ AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID 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(CAT MSC_ITEM_CATEGORIES_N4) USE_NL(MSRA, CAT, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID ORGANIZATION_ID, CAT.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, CAT.SR_CATEGORY_ID, 3 SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG, CAT.ORGANIZATION_ID) AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, CAT.SR_INSTANCE_ID) AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 5 /* ITEM CATEGORY-ORG */ AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID 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) USE_NL(MSRA, MSR, CAT, ITEMS, RECEIPT_ORG, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, CAT.ORGANIZATION_ID ORGANIZATION_ID, CAT.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), /* MSRA.CATEGORY_ID, */ DECODE(MSR.SOURCING_RULE_TYPE, 1, 7, 6) SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SOURCING_RULES MSR, MSC_ITEM_CATEGORIES CAT, MSC_SYSTEM_ITEMS ITEMS, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG,CAT.ORGANIZATION_ID) AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID,CAT.SR_INSTANCE_ID) AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */ AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND CAT.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 2) UNION ALL SELECT /*+ ORDERED USE_NL(PLANS, ITEMS, MSI, PARAM) */ MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, MSI.SR_INSTANCE_ID, 6 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, MSI.SOURCE_ORG_ID, MSI.SR_INSTANCE_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), 100, 1, 0, TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(1, 'J'), TO_DATE(NULL), TO_NUMBER(NULL), 2 SOURCING_LEVEL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), PARAM.ORGANIZATION_CODE, TO_CHAR(NULL), PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, 'N', 1, TO_NUMBER(NULL) FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SYSTEM_ITEMS ITEMS, /* org item */ MSC_SYSTEM_ITEMS MSI, /* system item */ MSC_TRADING_PARTNERS PARAM WHERE MSI.SOURCE_ORG_ID IS NOT NULL AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID AND PARAM.PARTNER_TYPE = 3 AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND MSI.PLAN_ID = -1 AND MSI.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND MSI.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID UNION ALL SELECT /*+ ORDERED USE_NL(PLANS,SYS, PARAM, ITEMS, MSI2, PARAM1) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, 4 ASSIGNMENT_TYPE, TO_NUMBER(NULL), 3 SOURCING_RULE_TYPE, PARAM.SOURCE_ORG_ID, PARAM.SR_INSTANCE_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), 100, 1, 0, TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(1, 'J'), TO_DATE(NULL), TO_NUMBER(NULL), 9 SOURCING_LEVEL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), PARAM1.ORGANIZATION_CODE, TO_CHAR(NULL), PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, SYS.FULL_PEGGING, 'N', 1, TO_NUMBER(NULL) FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SYSTEM_ITEMS SYS, MSC_TRADING_PARTNERS PARAM, MSC_SYSTEM_ITEMS ITEMS, MSC_SYSTEM_ITEMS MSI2, MSC_TRADING_PARTNERS PARAM1 WHERE PARAM.SOURCE_ORG_ID IS NOT NULL AND PARAM.SR_TP_ID = ITEMS.ORGANIZATION_ID AND PARAM.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND PARAM.PARTNER_TYPE = 3 AND SYS.BOM_ITEM_TYPE in (1, 4) AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.INVENTORY_ITEM_ID = SYS.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = SYS.SR_INSTANCE_ID AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID AND PARAM.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID AND PARAM1.PARTNER_TYPE = 3 AND SYS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND SYS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND SYS.PLAN_ID = PLANS.PLAN_ID AND PARAM.SR_TP_ID = PLANS.PLANNED_ORGANIZATION AND PARAM.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSI2.PLAN_ID = PLANS.PLAN_ID AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID AND MSI2.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND MSI2.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID 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 ) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), /* MSRA.CATEGORY_ID, */ DECODE(MSR.SOURCING_RULE_TYPE, 1, 11, 10) SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 1 /* SITE */ AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID AND NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID) = ITEMS.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 1) 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 ) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), /* MSRA.CATEGORY_ID, */ DECODE(MSR.SOURCING_RULE_TYPE, 1, 11, 10) SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND MSR.STATUS = 1 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSRA.ASSIGNMENT_TYPE = 7 /* REGION-INSTANCE */ AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND PLANS.PLAN_TYPE = 8 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 ) USE_NL(MSRA, ITEMS, MSR, RECEIPT_ORG, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID ORGANIZATION_ID, ITEMS.SR_INSTANCE_ID, MSRA.ASSIGNMENT_TYPE, MSRA.ASSIGNMENT_SET_ID, MSR.SOURCING_RULE_TYPE, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.ORGANIZATION_ID,NULL)), NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, DECODE(SOURCE_ORG.SOURCE_TYPE,2,ITEMS.SR_INSTANCE_ID,NULL)), SOURCE_ORG.SOURCE_PARTNER_ID, SOURCE_ORG.SOURCE_PARTNER_SITE_ID, SOURCE_ORG.ALLOCATION_PERCENT, SOURCE_ORG.RANK, MSC_SCATP_PUB.get_intransit_time( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), SOURCE_ORG.SHIP_METHOD, MSC_SCATP_PUB.get_weight_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), MSC_SCATP_PUB.get_transport_cost( SOURCE_ORG.SOURCE_ORGANIZATION_ID, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, NVL(RECEIPT_ORG.SR_RECEIPT_ORG, ITEMS.ORGANIZATION_ID), NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID, ITEMS.SR_INSTANCE_ID), SOURCE_ORG.SHIP_METHOD, RECEIPT_ORG.SR_RECEIPT_ORG), RECEIPT_ORG.EFFECTIVE_DATE, RECEIPT_ORG.DISABLE_DATE, TO_NUMBER(NULL), DECODE(MSR.SOURCING_RULE_TYPE, 1, 5, 4) SOURCING_LEVEL, MSRA.ASSIGNMENT_ID, MSR.SOURCING_RULE_ID, MSR.SOURCING_RULE_NAME, SOURCE_ORG.SOURCE_TYPE, TO_CHAR(NULL), MSR.DESCRIPTION, PLANS.PLAN_ID, PLANS.ORGANIZATION_ID, ITEMS.FULL_PEGGING, SOURCE_ORG.CIRCULAR_SRC, MSRA.ITEM_TYPE_VALUE, MSRA.REGION_ID FROM MSC_PLAN_ORGANIZATIONS_V PLANS, MSC_SR_ASSIGNMENTS MSRA, MSC_SYSTEM_ITEMS ITEMS, MSC_SOURCING_RULES MSR, MSC_SR_RECEIPT_ORG RECEIPT_ORG, MSC_SR_SOURCE_ORG SOURCE_ORG WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION AND ITEMS.PLAN_ID = PLANS.PLAN_ID AND ITEMS.BOM_ITEM_TYPE in (1, 4) AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID AND MSRA.ASSIGNMENT_TYPE = 9 /* ITEM-REGION */ AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND MSR.STATUS = 1 AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID 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 PLANS.PLAN_TYPE = 8 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 9)

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 ) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 1 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_PLAN_ORGANIZATIONS_V PLANS
WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
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 ) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
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 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 ) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, /* MSRA.CATEGORY_ID
, MAKE IT NULL */ 8 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
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(CAT MSC_ITEM_CATEGORIES_N4) USE_NL(MSRA
, CAT
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, CAT.SR_CATEGORY_ID
, 3 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID)
AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, CAT.SR_INSTANCE_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 5 /* ITEM CATEGORY-ORG */
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
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) USE_NL(MSRA
, MSR
, CAT
, ITEMS
, RECEIPT_ORG
, SOURCE_ORG) */ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, /* MSRA.CATEGORY_ID
, */ DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID)
AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, CAT.SR_INSTANCE_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */
AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND CAT.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 2) UNION ALL SELECT /*+ ORDERED USE_NL(PLANS
, ITEMS
, MSI
, PARAM) */ MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SR_INSTANCE_ID
, 6 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, MSI.SOURCE_ORG_ID
, MSI.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, 0
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 2 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PARAM.ORGANIZATION_CODE
, TO_CHAR(NULL)
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, 'N'
, 1
, TO_NUMBER(NULL)
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SYSTEM_ITEMS ITEMS
, /* ORG ITEM */ MSC_SYSTEM_ITEMS MSI
, /* SYSTEM ITEM */ MSC_TRADING_PARTNERS PARAM
WHERE MSI.SOURCE_ORG_ID IS NOT NULL
AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID
AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID
AND PARAM.PARTNER_TYPE = 3
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND MSI.PLAN_ID = -1
AND MSI.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID UNION ALL SELECT /*+ ORDERED USE_NL(PLANS
, SYS
, PARAM
, ITEMS
, MSI2
, PARAM1) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, 4 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORG_ID
, PARAM.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, 0
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 9 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PARAM1.ORGANIZATION_CODE
, TO_CHAR(NULL)
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, SYS.FULL_PEGGING
, 'N'
, 1
, TO_NUMBER(NULL)
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SYSTEM_ITEMS SYS
, MSC_TRADING_PARTNERS PARAM
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SYSTEM_ITEMS MSI2
, MSC_TRADING_PARTNERS PARAM1
WHERE PARAM.SOURCE_ORG_ID IS NOT NULL
AND PARAM.SR_TP_ID = ITEMS.ORGANIZATION_ID
AND PARAM.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND PARAM.PARTNER_TYPE = 3
AND SYS.BOM_ITEM_TYPE IN (1
, 4)
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.INVENTORY_ITEM_ID = SYS.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = SYS.SR_INSTANCE_ID
AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID
AND PARAM.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID
AND PARAM1.PARTNER_TYPE = 3
AND SYS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND SYS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND SYS.PLAN_ID = PLANS.PLAN_ID
AND PARAM.SR_TP_ID = PLANS.PLANNED_ORGANIZATION
AND PARAM.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSI2.PLAN_ID = PLANS.PLAN_ID
AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID
AND MSI2.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID
AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MSI2.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID 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 ) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, /* MSRA.CATEGORY_ID
, */ DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 1 /* SITE */
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID) = ITEMS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 1) 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 ) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, /* MSRA.CATEGORY_ID
, */ DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 7 /* REGION-INSTANCE */
AND ITEMS.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND PLANS.PLAN_TYPE = 8
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 ) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.ORGANIZATION_ID
, NULL))
, NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, DECODE(SOURCE_ORG.SOURCE_TYPE
, 2
, ITEMS.SR_INSTANCE_ID
, NULL))
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MSC_SCATP_PUB.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, SOURCE_ORG.SHIP_METHOD
, MSC_SCATP_PUB.GET_WEIGHT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, MSC_SCATP_PUB.GET_TRANSPORT_COST( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
, NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.SR_RECEIPT_ORG)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.PLAN_ID
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
, SOURCE_ORG.CIRCULAR_SRC
, MSRA.ITEM_TYPE_VALUE
, MSRA.REGION_ID
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND ITEMS.PLAN_ID = PLANS.PLAN_ID
AND ITEMS.BOM_ITEM_TYPE IN (1
, 4)
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.ASSIGNMENT_TYPE = 9 /* ITEM-REGION */
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
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 PLANS.PLAN_TYPE = 8
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 9)