View: MRP_ITEM_TASK_SR_LEVELS_V

Product:	MRP - Master Scheduling/MRP
Description:	Inventory item sources view
Implementation/DBA Data:	APPS.MRP_ITEM_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) INDEX(COMP_ITEMS MTL_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
, 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
, 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.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_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.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 = 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) INDEX(COMP_ITEMS MTL_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
, 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
, 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.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_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.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) INDEX(COMP_ITEMS MTL_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
, 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
, 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.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_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.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) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) USE_NL(MSRA
, CAT
, ITEMS
, MSR
, RECEIPT_ORG
, SOURCE_ORG
, COMP_ITEMS) */ 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
, 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.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MTL_ITEM_CATEGORIES CAT
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
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) INDEX(COMP_ITEMS MTL_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
, 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
, 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.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MRP_SOURCING_RULES MSR
, MTL_ITEM_CATEGORIES CAT
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_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 = 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(MSI
, PARAM
, COMP_ITEMS) */ 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
, 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.INVENTORY_ITEM_FLAG
, MSI.ENG_ITEM_FLAG
, MSI.BOM_ITEM_TYPE
, MSI.PLANNING_MAKE_BUY_CODE
, MSI.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MTL_SYSTEM_ITEMS MSI
, MTL_PARAMETERS PARAM
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.ORGANIZATION_ID = MSI.SOURCE_ORGANIZATION_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.SOURCE_ORGANIZATION_ID IS NOT NULL
AND MSI.SOURCE_ORGANIZATION_ID = PARAM.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = PLANS.PLANNED_ORGANIZATION UNION ALL SELECT /*+ ORDERED INDEX(PARAM MTL_PARAMETERS_U1) USE_NL(PARAM
, ITEMS
, PARAM1
, COMP_ITEMS) INDEX(COMP_ITEMS MTL_SYSTEM_ITEMS_U1) */ 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
, 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.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MTL_PARAMETERS PARAM
, MTL_SYSTEM_ITEMS ITEMS
, MTL_PARAMETERS PARAM1
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.ORGANIZATION_ID = PARAM.SOURCE_ORGANIZATION_ID
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND PARAM.SOURCE_ORGANIZATION_ID IS NOT NULL
AND PARAM.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND PARAM.SOURCE_ORGANIZATION_ID = PARAM1.ORGANIZATION_ID
AND PARAM1.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) INDEX(COMP_ITEMS MTL_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
, 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
, 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.MRP_PLANNING_CODE
, ITEMS.INVENTORY_ITEM_FLAG
, ITEMS.ENG_ITEM_FLAG
, ITEMS.BOM_ITEM_TYPE
, ITEMS.PLANNING_MAKE_BUY_CODE
, ITEMS.PRIMARY_UOM_CODE
, COMP_ITEMS.MRP_PLANNING_CODE
FROM MRP_PLAN_ORGANIZATIONS_V PLANS
, MRP_SR_ASSIGNMENTS MSRA
, MTL_SYSTEM_ITEMS ITEMS
, MRP_SOURCING_RULES MSR
, MRP_SR_RECEIPT_ORG RECEIPT_ORG
, MRP_SR_SOURCE_ORG SOURCE_ORG
, MTL_SYSTEM_ITEMS COMP_ITEMS
WHERE COMP_ITEMS.ORGANIZATION_ID = NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID)
AND COMP_ITEMS.INVENTORY_ITEM_ID = ITEMS.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 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