View: MSC_BOD_TASK_SR_LEVELS_V

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

View Text

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)
, MSC_SCATP_PUB.GET_SHIP_METHOD( 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_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
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
, 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)
, MSC_SCATP_PUB.GET_SHIP_METHOD( 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_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
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)
, MSC_SCATP_PUB.GET_SHIP_METHOD( 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_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
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_N1) 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)
, MSC_SCATP_PUB.GET_SHIP_METHOD( 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_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
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_N1) 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)
, MSC_SCATP_PUB.GET_SHIP_METHOD( 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_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
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
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
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)
, MSC_SCATP_PUB.GET_SHIP_METHOD( 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_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
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)

Columns