View: MRP_BOD_TASK_SR_LEVELS_V

Product:	MRP - Master Scheduling/MRP
Description:	BOD task sourcing rule levels view
Implementation/DBA Data:	APPS.MRP_BOD_TASK_SR_LEVELS_V

View Text

SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG)*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MRP_SCATP_PVT.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, MRP_SCATP_PVT.GET_SHIP_METHOD( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 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.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
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.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.BOM_ITEM_TYPE = 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 UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2)) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG)*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MRP_SCATP_PVT.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, MRP_SCATP_PVT.GET_SHIP_METHOD( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 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.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND ITEMS.BOM_ITEM_TYPE = 4
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.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2)) USE_NL(MSRA
, MSR
, ITEMS
, RECEIPT_ORG
, SOURCE_ORG)*/ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MRP_SCATP_PVT.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, MRP_SCATP_PVT.GET_SHIP_METHOD( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.CATEGORY_ID
, 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.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MRP_SYSTEM_ITEMS ITEMS
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
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.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_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.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(CAT MTL_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
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MRP_SCATP_PVT.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, MRP_SCATP_PVT.GET_SHIP_METHOD( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, MSRA.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.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MTL_ITEM_CATEGORIES CAT
, MRP_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
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.RECEIPT_ORGANIZATION_ID
, CAT.ORGANIZATION_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.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) INDEX(CAT MTL_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
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MRP_SCATP_PVT.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, MRP_SCATP_PVT.GET_SHIP_METHOD( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 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.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MTL_ITEM_CATEGORIES CAT
, MRP_SYSTEM_ITEMS ITEMS
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND CAT.CATEGORY_ID = MSRA.CATEGORY_ID
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.RECEIPT_ORGANIZATION_ID
, CAT.ORGANIZATION_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 MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID UNION ALL SELECT /*+ ORDERED USE_NL(ITEMS
, MSI
, PARAM) */ MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, 6 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, MSI.SOURCE_ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, 0
, TO_CHAR(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.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SYSTEM_ITEMS ITEMS
, MTL_SYSTEM_ITEMS MSI
, MTL_PARAMETERS PARAM
WHERE MSI.SOURCE_ORGANIZATION_ID IS NOT NULL
AND MSI.SOURCE_ORGANIZATION_ID = PARAM.ORGANIZATION_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND MSI.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL SELECT /*+ ORDERED INDEX(PARAM MTL_PARAMETERS_U1) USE_NL(SYS
, PARAM
, ITEMS
, MSI2
, PARAM1)*/ ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, 4 ASSIGNMENT_TYPE
, TO_NUMBER(NULL)
, 3 SOURCING_RULE_TYPE
, PARAM.SOURCE_ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 100
, 1
, 0
, TO_CHAR(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.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, SYS.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SYSTEM_ITEMS SYS
, MTL_PARAMETERS PARAM
, MTL_SYSTEM_ITEMS ITEMS
, MTL_SYSTEM_ITEMS MSI2
, MTL_PARAMETERS PARAM1
WHERE PARAM.SOURCE_ORGANIZATION_ID IS NOT NULL
AND PARAM.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SYS.BOM_ITEM_TYPE = 4
AND ITEMS.INVENTORY_ITEM_ID = SYS.INVENTORY_ITEM_ID
AND PARAM.SOURCE_ORGANIZATION_ID = PARAM1.ORGANIZATION_ID
AND SYS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND SYS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND PARAM.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSI2.ORGANIZATION_ID = PARAM.SOURCE_ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID UNION ALL SELECT /*+ ORDERED INDEX(MSRA MRP_SR_ASSIGNMENTS_U2) INDEX(MSR MRP_SOURCING_RULES_U1) INDEX(RECEIPT_ORG MRP_SR_RECEIPT_ORG_U2) INDEX(SOURCE_ORG MRP_SR_SOURCE_ORG_U2) USE_NL(MSRA
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG) */ ITEMS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID ORGANIZATION_ID
, MSRA.ASSIGNMENT_TYPE
, MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_TYPE
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.VENDOR_ID
, SOURCE_ORG.VENDOR_SITE_ID
, SOURCE_ORG.ALLOCATION_PERCENT
, SOURCE_ORG.RANK
, MRP_SCATP_PVT.GET_INTRANSIT_TIME( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, MRP_SCATP_PVT.GET_SHIP_METHOD( SOURCE_ORG.SOURCE_ORGANIZATION_ID
, NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
, SOURCE_ORG.SHIP_METHOD
, RECEIPT_ORG.RECEIPT_ORGANIZATION_ID)
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 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.COMPILE_DESIGNATOR
, PLANS.ORGANIZATION_ID
, ITEMS.FULL_PEGGING
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
WHERE ITEMS.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND ITEMS.BOM_ITEM_TYPE = 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.RECEIPT_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND ITEMS.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION
AND MSRA.ASSIGNMENT_SET_ID = PLANS.ASSIGNMENT_SET_ID

Columns