View: MSC_BOD_SOURCING_RULES_V

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

View Text

SELECT MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 1 SOURCING_LEVEL
FROM MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_ASSIGNMENTS MSRA
WHERE SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND RECEIPT_ORG.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND 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.BOM_ITEM_TYPE = 4
AND ITEMS.MRP_PLANNING_CODE <> 6
AND ( ITEMS.INVENTORY_ITEM_FLAG = 1 OR ITEMS.ENGINEERING_ITEM_FLAG = 1 )
AND ITEMS.PLANNING_MAKE_BUY_CODE IN ( 1
, 2 )
AND ITEMS.UOM_CODE IS NOT NULL
AND ITEMS.PLAN_ID= -1
AND MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */ UNION ALL SELECT MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 3 SOURCING_LEVEL
FROM MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SYSTEM_ITEMS ITEMS
, MSC_ITEM_CATEGORIES CAT
, MSC_SR_ASSIGNMENTS MSRA
WHERE SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID) = CAT.ORGANIZATION_ID
AND RECEIPT_ORG.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.MRP_PLANNING_CODE <> 6
AND ( ITEMS.INVENTORY_ITEM_FLAG = 1 OR ITEMS.ENGINEERING_ITEM_FLAG = 1 )
AND ITEMS.PLANNING_MAKE_BUY_CODE IN ( 1
, 2 )
AND ITEMS.UOM_CODE IS NOT NULL
AND ITEMS.PLAN_ID= -1
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 5 /* ITEM CATEGORY-ORG */ UNION ALL SELECT MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 5
, 4) SOURCING_LEVEL
FROM MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_PARAMETERS ORG
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_ASSIGNMENTS MSRA
WHERE SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND RECEIPT_ORG.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.MRP_PLANNING_CODE <> 6
AND ( ITEMS.INVENTORY_ITEM_FLAG = 1 OR ITEMS.ENGINEERING_ITEM_FLAG = 1 )
AND ITEMS.PLANNING_MAKE_BUY_CODE IN ( 1
, 2 )
AND ITEMS.UOM_CODE IS NOT NULL
AND ITEMS.PLAN_ID= -1
AND ORG.SR_INSTANCE_ID= MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */ UNION ALL SELECT MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 7
, 6) SOURCING_LEVEL
FROM MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SYSTEM_ITEMS ITEMS
, MSC_ITEM_CATEGORIES CAT
, MSC_PARAMETERS ORG
, MSC_SR_ASSIGNMENTS MSRA
WHERE SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, CAT.ORGANIZATION_ID) = CAT.ORGANIZATION_ID
AND RECEIPT_ORG.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ITEMS.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = CAT.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.MRP_PLANNING_CODE <> 6
AND ( ITEMS.INVENTORY_ITEM_FLAG = 1 OR ITEMS.ENGINEERING_ITEM_FLAG = 1 )
AND ITEMS.PLANNING_MAKE_BUY_CODE IN ( 1
, 2 )
AND ITEMS.UOM_CODE IS NOT NULL
AND ITEMS.PLAN_ID= -1
AND CAT.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ORG.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 2 /* ITEM CATEGORY */ UNION ALL SELECT MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, 8 SOURCING_LEVEL
FROM MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SYSTEM_ITEMS ITEMS
, MSC_SR_ASSIGNMENTS MSRA
WHERE SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND RECEIPT_ORG.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.MRP_PLANNING_CODE <> 6
AND ( ITEMS.INVENTORY_ITEM_FLAG = 1 OR ITEMS.ENGINEERING_ITEM_FLAG = 1 )
AND ITEMS.PLANNING_MAKE_BUY_CODE IN ( 1
, 2 )
AND ITEMS.UOM_CODE IS NOT NULL
AND ITEMS.PLAN_ID= -1
AND MSRA.ASSIGNMENT_TYPE = 4 /* ORG */ UNION ALL SELECT MSRA.ASSIGNMENT_SET_ID
, MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SR_INSTANCE_ID
, SOURCE_ORG.SOURCE_ORGANIZATION_ID
, SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
, SOURCE_ORG.SOURCE_PARTNER_ID
, SOURCE_ORG.SOURCE_PARTNER_SITE_ID
, RECEIPT_ORG.EFFECTIVE_DATE
, RECEIPT_ORG.DISABLE_DATE
, DECODE(MSR.SOURCING_RULE_TYPE
, 1
, 11
, 10) SOURCING_LEVEL
FROM MSC_SR_SOURCE_ORG SOURCE_ORG
, MSC_SR_RECEIPT_ORG RECEIPT_ORG
, MSC_SOURCING_RULES MSR
, MSC_SYSTEM_ITEMS ITEMS
, MSC_PARAMETERS ORG
, MSC_SR_ASSIGNMENTS MSRA
WHERE 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 NVL(RECEIPT_ORG.SR_RECEIPT_ORG
, ITEMS.ORGANIZATION_ID) = ITEMS.ORGANIZATION_ID
AND RECEIPT_ORG.SR_INSTANCE_ID = ITEMS.SR_INSTANCE_ID
AND ITEMS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = ORG.SR_INSTANCE_ID
AND ITEMS.BOM_ITEM_TYPE = 4
AND ITEMS.MRP_PLANNING_CODE <> 6
AND ( ITEMS.INVENTORY_ITEM_FLAG = 1 OR ITEMS.ENGINEERING_ITEM_FLAG = 1 )
AND ITEMS.PLANNING_MAKE_BUY_CODE IN ( 1
, 2 )
AND ITEMS.UOM_CODE IS NOT NULL
AND ITEMS.PLAN_ID= -1
AND ORG.SR_INSTANCE_ID= MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 1 /* SITE */

Columns