VIEW: APPS.MSC_SOURCES_V

Source

View Text - Preformatted

SELECT MISLV. INVENTORY_ITEM_ID , MISLV.ORGANIZATION_ID , MISLV.SR_INSTANCE_ID , MISLV.ASSIGNMENT_TYPE , MISLV.ASSIGNMENT_SET_ID , MISLV.SOURCING_RULE_TYPE , MISLV.SOURCE_ORGANIZATION_ID , MISLV.SOURCE_ORG_INSTANCE_ID , MISLV.VENDOR_ID , MISLV.VENDOR_SITE_ID , MISLV.ALLOCATION_PERCENT , MISLV.RANK , MISLV.AVG_TRANSIT_LEAD_TIME , MISLV.SHIP_METHOD , MISLV.EFFECTIVE_DATE , MISLV.DISABLE_DATE , MISLV.CATEGORY_ID , MISLV.ASSIGNMENT_ID , MISLV.SOURCING_RULE_ID , MISLV.SOURCE_TYPE , MISLV.SOURCING_RULE_NAME , MISLV.CUSTOMER_ID , MISLV.SHIP_TO_SITE_ID , MISLV.TIME_UOM_CODE , MISLV.CIRCULAR_SRC FROM ( select MISLV1. INVENTORY_ITEM_ID , MISLV1.ORGANIZATION_ID , MISLV1.SR_INSTANCE_ID , MISLV1.ASSIGNMENT_TYPE , MAS.ASSIGNMENT_SET_ID , MISLV1.SOURCING_RULE_TYPE , MISLV1.SOURCE_ORGANIZATION_ID , MISLV1.SOURCE_ORG_INSTANCE_ID , MISLV1.VENDOR_ID , MISLV1.VENDOR_SITE_ID , MISLV1.ALLOCATION_PERCENT , MISLV1.RANK , MISLV1.AVG_TRANSIT_LEAD_TIME , MISLV1.SHIP_METHOD , MISLV1.EFFECTIVE_DATE , MISLV1.DISABLE_DATE , MISLV1.CATEGORY_ID , MISLV1.ASSIGNMENT_ID , MISLV1.SOURCING_RULE_ID , MISLV1.SOURCE_TYPE , MISLV1.SOURCING_RULE_NAME , MISLV1.CUSTOMER_ID , MISLV1.SHIP_TO_SITE_ID , MISLV1.TIME_UOM_CODE , MISLV1.CIRCULAR_SRC , MISLV1.sourcing_level , min(MISLV1.sourcing_level) over ( PARTITION BY MISLV1.ORGANIZATION_ID , MISLV1. INVENTORY_ITEM_ID , MISLV1.SR_INSTANCE_ID , MAS.ASSIGNMENT_SET_ID ) as min_src_level FROM MSC_ITEM_SOURCING_LEVELS_V MISLV1, MSC_ASSIGNMENT_SETS MAS WHERE MAS.ASSIGNMENT_SET_ID =nvl( MISLV1.ASSIGNMENT_SET_ID ,mas.assignment_set_id) ) MISLV where MISLV.min_src_level =MISLV.sourcing_level

View Text - HTML Formatted

SELECT MISLV. INVENTORY_ITEM_ID
, MISLV.ORGANIZATION_ID
, MISLV.SR_INSTANCE_ID
, MISLV.ASSIGNMENT_TYPE
, MISLV.ASSIGNMENT_SET_ID
, MISLV.SOURCING_RULE_TYPE
, MISLV.SOURCE_ORGANIZATION_ID
, MISLV.SOURCE_ORG_INSTANCE_ID
, MISLV.VENDOR_ID
, MISLV.VENDOR_SITE_ID
, MISLV.ALLOCATION_PERCENT
, MISLV.RANK
, MISLV.AVG_TRANSIT_LEAD_TIME
, MISLV.SHIP_METHOD
, MISLV.EFFECTIVE_DATE
, MISLV.DISABLE_DATE
, MISLV.CATEGORY_ID
, MISLV.ASSIGNMENT_ID
, MISLV.SOURCING_RULE_ID
, MISLV.SOURCE_TYPE
, MISLV.SOURCING_RULE_NAME
, MISLV.CUSTOMER_ID
, MISLV.SHIP_TO_SITE_ID
, MISLV.TIME_UOM_CODE
, MISLV.CIRCULAR_SRC
FROM ( SELECT MISLV1. INVENTORY_ITEM_ID
, MISLV1.ORGANIZATION_ID
, MISLV1.SR_INSTANCE_ID
, MISLV1.ASSIGNMENT_TYPE
, MAS.ASSIGNMENT_SET_ID
, MISLV1.SOURCING_RULE_TYPE
, MISLV1.SOURCE_ORGANIZATION_ID
, MISLV1.SOURCE_ORG_INSTANCE_ID
, MISLV1.VENDOR_ID
, MISLV1.VENDOR_SITE_ID
, MISLV1.ALLOCATION_PERCENT
, MISLV1.RANK
, MISLV1.AVG_TRANSIT_LEAD_TIME
, MISLV1.SHIP_METHOD
, MISLV1.EFFECTIVE_DATE
, MISLV1.DISABLE_DATE
, MISLV1.CATEGORY_ID
, MISLV1.ASSIGNMENT_ID
, MISLV1.SOURCING_RULE_ID
, MISLV1.SOURCE_TYPE
, MISLV1.SOURCING_RULE_NAME
, MISLV1.CUSTOMER_ID
, MISLV1.SHIP_TO_SITE_ID
, MISLV1.TIME_UOM_CODE
, MISLV1.CIRCULAR_SRC
, MISLV1.SOURCING_LEVEL
, MIN(MISLV1.SOURCING_LEVEL) OVER ( PARTITION BY MISLV1.ORGANIZATION_ID
, MISLV1. INVENTORY_ITEM_ID
, MISLV1.SR_INSTANCE_ID
, MAS.ASSIGNMENT_SET_ID ) AS MIN_SRC_LEVEL
FROM MSC_ITEM_SOURCING_LEVELS_V MISLV1
, MSC_ASSIGNMENT_SETS MAS
WHERE MAS.ASSIGNMENT_SET_ID =NVL( MISLV1.ASSIGNMENT_SET_ID
, MAS.ASSIGNMENT_SET_ID) ) MISLV
WHERE MISLV.MIN_SRC_LEVEL =MISLV.SOURCING_LEVEL