VIEW: APPS.MSC_BOD_SOURCING_RULES_V

Source

View Text - Preformatted

SELECT /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 */

View Text - HTML Formatted

SELECT /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 /*+ LEADING(MSRA) */ 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 */