View: MSC_ITEM_TASK_SR_LEVELS_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_ITEM_TASK_SR_LEVELS_V

View Text

SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_U2) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U2) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 1 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID AND ITEMS.PLAN_ID = -1 AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */ AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 6) UNION ALL SELECT /*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_U2) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U2) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND ITEMS.PLAN_ID = -1
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 3) UNION ALL SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_U2) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U2) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, ITEMS
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, 8 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
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
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND ITEMS.PLAN_ID = -1
AND ITEMS.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 4) UNION ALL SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_U2) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U2) INDEX(CAT MSC_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, CAT
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS */ CAT.INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSRA.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, CAT.SR_CATEGORY_ID
, 3 SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, CAT.SR_INSTANCE_ID)
AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 5 /*ITEM CAT-ORG*/
AND MSRA.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSRA.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 5) UNION ALL SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_U2) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U2) INDEX(CAT MSC_ITEM_CATEGORIES_N1) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, MSR
, CAT
, ITEMS
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS */ CAT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CAT.ORGANIZATION_ID ORGANIZATION_ID
, CAT.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, CAT.SR_CATEGORY_ID
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SOURCING_RULES MSR
, MSC_ITEM_CATEGORIES CAT
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID= MSRA.CATEGORY_SET_ID
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID)
AND CAT.SR_INSTANCE_ID = NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, CAT.SR_INSTANCE_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */
AND CAT.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 2) UNION ALL SELECT /*+ ORDERED USE_NL(MSI
, PARAM
, COMP_ITEMS */ MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SR_INSTANCE_ID
, PLANS.PLAN_ID
, 6 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, MSI.SOURCE_ORG_ID
, MSI.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 2 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PARAM.ORGANIZATION_CODE
, TO_CHAR(NULL)
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, MSI.MRP_PLANNING_CODE
, MSI.BOM_ITEM_TYPE
, MSI.PLANNING_MAKE_BUY_CODE
, MSI.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS PARAM
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.ORGANIZATION_ID = MSI.SOURCE_ORG_ID
AND COMP_ITEMS.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.SOURCE_ORG_ID IS NOT NULL
AND MSI.SR_INSTANCE_ID = PARAM.SR_INSTANCE_ID
AND MSI.SOURCE_ORG_ID = PARAM.SR_TP_ID
AND PARAM.PARTNER_TYPE = 3
AND MSI.PLAN_ID = -1
AND MSI.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL SELECT/*+ ORDERED INDEX(PARAM MSC_TRADING_PARTNERS_U1) USE_NL(PARAM
, ITEMS
, PARAM1
, COMP_ITEMS ) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_ID
, 4 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORG_ID
, ITEMS.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, TO_DATE(1
, 'J')
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 9 SOURCING_LEVEL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PARAM1.ORGANIZATION_CODE
, TO_CHAR(NULL)
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_TRADING_PARTNERS PARAM
, MSC_SYSTEM_ITEMS ITEMS
, MSC_TRADING_PARTNERS PARAM1
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.ORGANIZATION_ID = PARAM.SOURCE_ORG_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND ITEMS.PLAN_ID = -1
AND PARAM.SOURCE_ORG_ID IS NOT NULL
AND PARAM.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND PARAM.SR_TP_ID = ITEMS.ORGANIZATION_ID
AND PARAM.PARTNER_TYPE = 3
AND PARAM.SR_INSTANCE_ID = PARAM1.SR_INSTANCE_ID
AND PARAM.SOURCE_ORG_ID = PARAM1.SR_TP_ID
AND PARAM1.PARTNER_TYPE = 3
AND PARAM1.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND PARAM1.SR_TP_ID = PLANS.PLANNED_ORGANIZATION UNION ALL SELECT/*+ ORDERED INDEX(MSRA MSC_SR_ASSIGNMENTS_U2) INDEX(MSR MSC_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MSC_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MSC_SR_SOURCE_ORG_U2) INDEX(COMP_ITEMS MSC_SYSTEM_ITEMS_U1) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS */ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, PLANS.PLAN_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, TO_NUMBER(NULL)
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10) SOURCING_LEVEL
, MSRA.ASSIGNMENT_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, SOURCE_ORG.SOURCE_TYPE
, TO_CHAR(NULL)
, MSR.DESCRIPTION
, PLANS.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.MRP_PLANNING_CODE
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MSC_PLAN_ORGANIZATIONS_V PLANS
, MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SOURCING_RULES MSR
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.PLAN_ID = -1
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.SR_INSTANCE_ID = NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID)
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 1 /* SITE */
AND NVL(RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID
, ITEMS.SR_INSTANCE_ID) = ITEMS.SR_INSTANCE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID)= ITEMS.ORGANIZATION_ID
AND ITEMS.PLAN_ID = -1
AND ITEMS.SR_INSTANCE_ID = PLANS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 1)

Columns