MSC_ITEM_SOURCING

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
SOURCING_RULE_NAME PLAN_ID INVENTORY_ITEM_ID ORGANIZATION_ID ORGANIZATION_CODE SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_CODE VENDOR_ID VENDOR_NAME VENDOR_SITE_ID VENDOR_SITE_CODE EFFECTIVE_DATE DISABLE_DATE ALLOCATION_PERCENT RANK CONVERSION_FACTOR SOURCE_TYPE SOURCING_TYPE_TEXT AVG_TRANSIT_LEAD_TIME SOURCING_RULE_ID SOURCE_TYPE_2 SOURCING_RULE_TYPE SOURCING_RULE_TYPE_TEXT SHIP_METHOD SHIP_METHOD_TEXT ASSIGNMENT_SET_TYPE ASIGNMENT_SET_TYPE_TEXT SUPPLIER_ID LAST_UPDATE_DATE LAST_UPDATED_BY CUSTOMER_ID CUSTOMER_SITE_ID ZONE_ID CIRCULAR_SRC

Name

SOURCING_RULE_NAME

PLAN_ID

INVENTORY_ITEM_ID

ORGANIZATION_ID

ORGANIZATION_CODE

SOURCE_ORGANIZATION_ID

SOURCE_ORGANIZATION_CODE

VENDOR_ID

VENDOR_NAME

VENDOR_SITE_ID

VENDOR_SITE_CODE

EFFECTIVE_DATE

DISABLE_DATE

ALLOCATION_PERCENT

RANK

CONVERSION_FACTOR

SOURCE_TYPE

SOURCING_TYPE_TEXT

AVG_TRANSIT_LEAD_TIME

SOURCING_RULE_ID

SOURCE_TYPE_2

SOURCING_RULE_TYPE

SOURCING_RULE_TYPE_TEXT

SHIP_METHOD

SHIP_METHOD_TEXT

ASSIGNMENT_SET_TYPE

ASIGNMENT_SET_TYPE_TEXT

SUPPLIER_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

CUSTOMER_ID

CUSTOMER_SITE_ID

ZONE_ID

CIRCULAR_SRC

Query

SQL_Statement
SELECT SOURCINGRULES.SOURCING_RULE_NAME , ITEMSOURCING.plan_id , ITEMSOURCING.INVENTORY_ITEM_ID , ITEMSOURCING.ORGANIZATION_ID , msc_get_name.org_code(ITEMSOURCING.organization_id) AS ORGANIZATION_CODE, ITEMSOURCING.SOURCE_ORGANIZATION_ID , msc_get_name.org_code(ITEMSOURCING.source_organization_id) AS SOURCE_ORGANIZATION_CODE, VEN.tp_id AS VENDOR_ID, VEN.partner_NAME AS VENDOR_NAME, ITEMSOURCING.supplier_site_ID as VENDOR_SITE_ID, SITE.tp_SITE_CODE AS VENDOR_SITE_CODE, ITEMSOURCING.EFFECTIVE_DATE , ITEMSOURCING.DISABLE_DATE , ITEMSOURCING.ALLOCATION_PERCENT , ITEMSOURCING.RANK , ITEMSOURCING.conversion_factor , ITEMSOURCING.SOURCE_TYPE , msc_get_name.lookup_meaning('MRP_SOURCING_RULE_TYPE', ITEMSOURCING.sourcing_rule_type) as SOURCING_TYPE_TEXT, decode(ITEMSOURCING.ship_method, NULL, NULL, ITEMSOURCING.AVG_TRANSIT_LEAD_TIME) AS AVG_TRANSIT_LEAD_TIME, ITEMSOURCING.SOURCING_RULE_ID , msc_get_name.lookup_meaning('MRP_SOURCE_TYPE',ITEMSOURCING.source_type) as SOURCE_TYPE_2, ITEMSOURCING.SOURCING_RULE_TYPE , msc_get_name.lookup_meaning('MRP_SR_ASSIGNMENT_TYPE',ITEMSOURCING.assignment_type) AS SOURCING_RULE_TYPE_TEXT, ITEMSOURCING.SHIP_METHOD , nvl(msc_get_name.lookup_meaning1('SHIP_METHOD',ITEMSOURCING.SHIP_METHOD,msc_get_name.get_application_id('AU'),0) , ITEMSOURCING.SHIP_METHOD) as SHIP_METHOD_TEXT, ITEMSOURCING.assignment_set_type , msc_get_name.lookup_meaning('MSC_ASSIGNMENT_USE_TYPE',ITEMSOURCING.ASSIGNMENT_SET_TYPE) as ASIGNMENT_SET_TYPE_TEXT, ITEMSOURCING.supplier_id , (case when SOURCINGRULES.last_update_date > ITEMSOURCING.last_update_date then SOURCINGRULES.last_update_date else ITEMSOURCING.last_update_date end) as LAST_UPDATE_DATE, (case when SOURCINGRULES.last_update_date > ITEMSOURCING.last_update_date then SOURCINGRULES.last_updated_by else ITEMSOURCING.last_updated_by end) as LAST_UPDATED_BY, ITEMSOURCING.customer_id , ITEMSOURCING.customer_site_id , ITEMSOURCING.zone_id , ITEMSOURCING.circular_src FROM MSC_SOURCING_RULES SOURCINGRULES, MSC_ITEM_SOURCING ITEMSOURCING, msc_trading_partner_sites SITE, msc_global_trading_partners VEN WHERE SOURCINGRULES.SOURCING_RULE_ID(+) = ITEMSOURCING.SOURCING_RULE_ID AND SITE.partner_SITE_ID(+) = ITEMSOURCING.supplier_site_ID and site.partner_id(+) = ITEMSOURCING.supplier_id and VEN.tp_id(+)= ITEMSOURCING.supplier_id and VEN.partner_type(+)=1

SQL_Statement

SELECT

SOURCINGRULES.SOURCING_RULE_NAME ,

ITEMSOURCING.plan_id ,

ITEMSOURCING.INVENTORY_ITEM_ID ,

ITEMSOURCING.ORGANIZATION_ID ,

msc_get_name.org_code(ITEMSOURCING.organization_id) AS ORGANIZATION_CODE,

ITEMSOURCING.SOURCE_ORGANIZATION_ID ,

msc_get_name.org_code(ITEMSOURCING.source_organization_id) AS SOURCE_ORGANIZATION_CODE,

VEN.tp_id AS VENDOR_ID,

VEN.partner_NAME AS VENDOR_NAME,

ITEMSOURCING.supplier_site_ID as VENDOR_SITE_ID,

SITE.tp_SITE_CODE AS VENDOR_SITE_CODE,

ITEMSOURCING.EFFECTIVE_DATE ,

ITEMSOURCING.DISABLE_DATE ,

ITEMSOURCING.ALLOCATION_PERCENT ,

ITEMSOURCING.RANK ,

ITEMSOURCING.conversion_factor ,

ITEMSOURCING.SOURCE_TYPE ,

msc_get_name.lookup_meaning('MRP_SOURCING_RULE_TYPE', ITEMSOURCING.sourcing_rule_type) as SOURCING_TYPE_TEXT,

decode(ITEMSOURCING.ship_method, NULL, NULL, ITEMSOURCING.AVG_TRANSIT_LEAD_TIME) AS AVG_TRANSIT_LEAD_TIME,

ITEMSOURCING.SOURCING_RULE_ID ,

msc_get_name.lookup_meaning('MRP_SOURCE_TYPE',ITEMSOURCING.source_type) as SOURCE_TYPE_2,

ITEMSOURCING.SOURCING_RULE_TYPE ,

msc_get_name.lookup_meaning('MRP_SR_ASSIGNMENT_TYPE',ITEMSOURCING.assignment_type) AS SOURCING_RULE_TYPE_TEXT,

ITEMSOURCING.SHIP_METHOD ,

nvl(msc_get_name.lookup_meaning1('SHIP_METHOD',ITEMSOURCING.SHIP_METHOD,msc_get_name.get_application_id('AU'),0) , ITEMSOURCING.SHIP_METHOD) as SHIP_METHOD_TEXT,

ITEMSOURCING.assignment_set_type ,

msc_get_name.lookup_meaning('MSC_ASSIGNMENT_USE_TYPE',ITEMSOURCING.ASSIGNMENT_SET_TYPE) as ASIGNMENT_SET_TYPE_TEXT,

ITEMSOURCING.supplier_id ,

(case when SOURCINGRULES.last_update_date > ITEMSOURCING.last_update_date then SOURCINGRULES.last_update_date else ITEMSOURCING.last_update_date end) as LAST_UPDATE_DATE,

(case when SOURCINGRULES.last_update_date > ITEMSOURCING.last_update_date then SOURCINGRULES.last_updated_by else ITEMSOURCING.last_updated_by end) as LAST_UPDATED_BY,

ITEMSOURCING.customer_id ,

ITEMSOURCING.customer_site_id ,

ITEMSOURCING.zone_id ,

ITEMSOURCING.circular_src

FROM

MSC_SOURCING_RULES SOURCINGRULES,

MSC_ITEM_SOURCING ITEMSOURCING,

msc_trading_partner_sites SITE,

msc_global_trading_partners VEN

WHERE

SOURCINGRULES.SOURCING_RULE_ID(+) = ITEMSOURCING.SOURCING_RULE_ID

AND SITE.partner_SITE_ID(+) = ITEMSOURCING.supplier_site_ID

and site.partner_id(+) = ITEMSOURCING.supplier_id

and VEN.tp_id(+)= ITEMSOURCING.supplier_id

and VEN.partner_type(+)=1