VIEW: APPS.MSC_ITEM_SOURCING_V

Source

View Text - Preformatted

SELECT SRC.plan_id , ORGS.ORGANIZATION_ID , orgs.sr_instance_id , SRC.INVENTORY_ITEM_ID , ITEM.item_name , SRC.ORGANIZATION_ID , msc_get_name.org_code(src.organization_id,src.sr_instance_id) , SRC.SOURCE_ORGANIZATION_ID , src.sr_instance_id2 , msc_get_name.org_code(src.source_organization_id,src.sr_instance_id2) , SRC.ITEM_TYPE_VALUE , msc_get_name.lookup_meaning('MSC_PART_CONDITION',SRC.ITEM_TYPE_VALUE) , ven.partner_ID , VEN.partner_NAME , SRC.supplier_site_ID , SITE.tp_SITE_CODE , SRC.EFFECTIVE_DATE , SRC.LAST_UPDATED_BY , SRC.LAST_UPDATE_DATE , SRC.CREATED_BY , SRC.CREATION_DATE , SRC.LAST_UPDATE_LOGIN , SRC.DISABLE_DATE , SRC.ALLOCATION_PERCENT , SRC.RANK , SRC.conversion_factor , SRC.SOURCE_TYPE , msc_get_name.lookup_meaning('MRP_SOURCING_RULE_TYPE', src.sourcing_rule_type) , decode(src.ship_method, NULL, NULL, SRC.AVG_TRANSIT_LEAD_TIME) , SRC.SOURCING_RULE_ID , RULES.SOURCING_RULE_NAME , SRC.ASSIGNMENT_ID , SRC.ASSIGNMENT_SET_ID , SRC.ASSIGNMENT_TYPE , msc_get_name.lookup_meaning('MRP_SOURCE_TYPE',src.source_type) , SRC.SOURCING_RULE_TYPE , msc_get_name.lookup_meaning('MRP_SR_ASSIGNMENT_TYPE',src.assignment_type) ,src.SHIP_METHOD ,nvl(msc_get_name.lookup_meaning1('SHIP_METHOD',SRC.SHIP_METHOD,msc_get_name.get_application_id('AU'),0) , src.SHIP_METHOD) ,SRC.HISTORICAL_ALLOC_QTY ,src.sr_instance_id ,cat.category_set_id ,cat.sr_category_id ,src.assignment_set_type ,msc_get_name.lookup_meaning('MSC_ASSIGNMENT_USE_TYPE',src.ASSIGNMENT_SET_TYPE) ,src.supplier_site_id ,src.supplier_id ,src.circular_src ,decode(src.circular_src, 1, src.rank, to_number(null)) circular_src_rank FROM Msc_SOURCING_RULES RULES, msc_trading_partner_sites SITE, msc_trading_partners VEN, Msc_items ITEM, Msc_ITEM_SOURCING SRC, Msc_plans ORGS, msc_item_categories cat WHERE RULES.SOURCING_RULE_ID(+) = SRC.SOURCING_RULE_ID AND SITE.partner_SITE_ID(+) = SRC.supplier_site_ID and site.partner_id(+) = src.supplier_id and src.supplier_id = ven.partner_id(+) and ven.partner_type(+)=1 AND ITEM.INVENTORY_ITEM_ID = SRC.INVENTORY_ITEM_ID AND SRC.plan_id = ORGS.plan_id and cat.organization_id=src.organization_id and cat.sr_instance_id=src.sr_instance_id and cat.inventory_item_id=src.inventory_item_id and src.assignment_set_type <>2 union all select SRC.plan_id , ORGS.ORGANIZATION_ID , orgs.sr_instance_id , SRC.INVENTORY_ITEM_ID , ITEM.item_name , SRC.ORGANIZATION_ID , msc_get_name.org_code(src.organization_id,src.sr_instance_id) , SRC.SOURCE_ORGANIZATION_ID , src.sr_instance_id2 , msc_get_name.org_code(src.source_organization_id,src.sr_instance_id2) , SRC.ITEM_TYPE_VALUE , msc_get_name.lookup_meaning('MSC_PART_CONDITION',SRC.ITEM_TYPE_VALUE) , ven.partner_ID , VEN.partner_NAME , SRC.supplier_site_ID , SITE.tp_SITE_CODE , SRC.EFFECTIVE_DATE , SRC.LAST_UPDATED_BY , SRC.LAST_UPDATE_DATE , SRC.CREATED_BY , SRC.CREATION_DATE , SRC.LAST_UPDATE_LOGIN , SRC.DISABLE_DATE , SRC.ALLOCATION_PERCENT , SRC.RANK , SRC.conversion_factor , SRC.SOURCE_TYPE , msc_get_name.lookup_meaning('MRP_SOURCING_RULE_TYPE', src.sourcing_rule_type) , decode(src.ship_method, NULL, NULL, SRC.AVG_TRANSIT_LEAD_TIME) , SRC.SOURCING_RULE_ID , RULES.SOURCING_RULE_NAME , SRC.ASSIGNMENT_ID , SRC.ASSIGNMENT_SET_ID , SRC.ASSIGNMENT_TYPE , msc_get_name.lookup_meaning('MRP_SOURCE_TYPE',src.source_type) , SRC.SOURCING_RULE_TYPE , msc_get_name.lookup_meaning('MRP_SR_ASSIGNMENT_TYPE',src.assignment_type) ,SRC.SHIP_METHOD ,msc_get_name.lookup_meaning1('SHIP_METHOD',SRC.SHIP_METHOD,3,0) ,SRC.HISTORICAL_ALLOC_QTY ,src.sr_instance_id ,cat.category_set_id ,cat.sr_category_id ,src.assignment_set_type ,msc_get_name.lookup_meaning('MSC_ASSIGNMENT_USE_TYPE',src.ASSIGNMENT_SET_TYPE) ,src.supplier_site_id ,src.supplier_id ,src.circular_src ,decode(src.circular_src, 1, src.rank, to_number(null)) circular_src_rank FROM Msc_SOURCING_RULES RULES, msc_trading_partner_sites SITE, msc_trading_partners VEN, Msc_items ITEM, Msc_ITEM_SOURCING SRC, Msc_plans ORGS, msc_item_categories cat, msc_apps_instances inst where RULES.SOURCING_RULE_ID(+) = SRC.SOURCING_RULE_ID AND SITE.partner_SITE_ID(+) = SRC.supplier_site_ID and site.partner_id(+) = src.supplier_id and src.supplier_id = ven.partner_id(+) and ven.partner_type(+)=1 AND ITEM.INVENTORY_ITEM_ID = SRC.INVENTORY_ITEM_ID AND SRC.plan_id = ORGS.plan_id and cat.organization_id=inst.validation_org_id and cat.sr_instance_id=src.sr_instance_id and cat.inventory_item_id=src.inventory_item_id and inst.instance_id=src.sr_instance_id and src.assignment_set_type =2

View Text - HTML Formatted

SELECT SRC.PLAN_ID
, ORGS.ORGANIZATION_ID
, ORGS.SR_INSTANCE_ID
, SRC.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, SRC.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SRC.ORGANIZATION_ID
, SRC.SR_INSTANCE_ID)
, SRC.SOURCE_ORGANIZATION_ID
, SRC.SR_INSTANCE_ID2
, MSC_GET_NAME.ORG_CODE(SRC.SOURCE_ORGANIZATION_ID
, SRC.SR_INSTANCE_ID2)
, SRC.ITEM_TYPE_VALUE
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, SRC.ITEM_TYPE_VALUE)
, VEN.PARTNER_ID
, VEN.PARTNER_NAME
, SRC.SUPPLIER_SITE_ID
, SITE.TP_SITE_CODE
, SRC.EFFECTIVE_DATE
, SRC.LAST_UPDATED_BY
, SRC.LAST_UPDATE_DATE
, SRC.CREATED_BY
, SRC.CREATION_DATE
, SRC.LAST_UPDATE_LOGIN
, SRC.DISABLE_DATE
, SRC.ALLOCATION_PERCENT
, SRC.RANK
, SRC.CONVERSION_FACTOR
, SRC.SOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_SOURCING_RULE_TYPE'
, SRC.SOURCING_RULE_TYPE)
, DECODE(SRC.SHIP_METHOD
, NULL
, NULL
, SRC.AVG_TRANSIT_LEAD_TIME)
, SRC.SOURCING_RULE_ID
, RULES.SOURCING_RULE_NAME
, SRC.ASSIGNMENT_ID
, SRC.ASSIGNMENT_SET_ID
, SRC.ASSIGNMENT_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_SOURCE_TYPE'
, SRC.SOURCE_TYPE)
, SRC.SOURCING_RULE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_SR_ASSIGNMENT_TYPE'
, SRC.ASSIGNMENT_TYPE)
, SRC.SHIP_METHOD
, NVL(MSC_GET_NAME.LOOKUP_MEANING1('SHIP_METHOD'
, SRC.SHIP_METHOD
, MSC_GET_NAME.GET_APPLICATION_ID('AU')
, 0)
, SRC.SHIP_METHOD)
, SRC.HISTORICAL_ALLOC_QTY
, SRC.SR_INSTANCE_ID
, CAT.CATEGORY_SET_ID
, CAT.SR_CATEGORY_ID
, SRC.ASSIGNMENT_SET_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ASSIGNMENT_USE_TYPE'
, SRC.ASSIGNMENT_SET_TYPE)
, SRC.SUPPLIER_SITE_ID
, SRC.SUPPLIER_ID
, SRC.CIRCULAR_SRC
, DECODE(SRC.CIRCULAR_SRC
, 1
, SRC.RANK
, TO_NUMBER(NULL)) CIRCULAR_SRC_RANK
FROM MSC_SOURCING_RULES RULES
, MSC_TRADING_PARTNER_SITES SITE
, MSC_TRADING_PARTNERS VEN
, MSC_ITEMS ITEM
, MSC_ITEM_SOURCING SRC
, MSC_PLANS ORGS
, MSC_ITEM_CATEGORIES CAT
WHERE RULES.SOURCING_RULE_ID(+) = SRC.SOURCING_RULE_ID
AND SITE.PARTNER_SITE_ID(+) = SRC.SUPPLIER_SITE_ID
AND SITE.PARTNER_ID(+) = SRC.SUPPLIER_ID
AND SRC.SUPPLIER_ID = VEN.PARTNER_ID(+)
AND VEN.PARTNER_TYPE(+)=1
AND ITEM.INVENTORY_ITEM_ID = SRC.INVENTORY_ITEM_ID
AND SRC.PLAN_ID = ORGS.PLAN_ID
AND CAT.ORGANIZATION_ID=SRC.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID=SRC.SR_INSTANCE_ID
AND CAT.INVENTORY_ITEM_ID=SRC.INVENTORY_ITEM_ID
AND SRC.ASSIGNMENT_SET_TYPE <>2 UNION ALL SELECT SRC.PLAN_ID
, ORGS.ORGANIZATION_ID
, ORGS.SR_INSTANCE_ID
, SRC.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, SRC.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SRC.ORGANIZATION_ID
, SRC.SR_INSTANCE_ID)
, SRC.SOURCE_ORGANIZATION_ID
, SRC.SR_INSTANCE_ID2
, MSC_GET_NAME.ORG_CODE(SRC.SOURCE_ORGANIZATION_ID
, SRC.SR_INSTANCE_ID2)
, SRC.ITEM_TYPE_VALUE
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, SRC.ITEM_TYPE_VALUE)
, VEN.PARTNER_ID
, VEN.PARTNER_NAME
, SRC.SUPPLIER_SITE_ID
, SITE.TP_SITE_CODE
, SRC.EFFECTIVE_DATE
, SRC.LAST_UPDATED_BY
, SRC.LAST_UPDATE_DATE
, SRC.CREATED_BY
, SRC.CREATION_DATE
, SRC.LAST_UPDATE_LOGIN
, SRC.DISABLE_DATE
, SRC.ALLOCATION_PERCENT
, SRC.RANK
, SRC.CONVERSION_FACTOR
, SRC.SOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_SOURCING_RULE_TYPE'
, SRC.SOURCING_RULE_TYPE)
, DECODE(SRC.SHIP_METHOD
, NULL
, NULL
, SRC.AVG_TRANSIT_LEAD_TIME)
, SRC.SOURCING_RULE_ID
, RULES.SOURCING_RULE_NAME
, SRC.ASSIGNMENT_ID
, SRC.ASSIGNMENT_SET_ID
, SRC.ASSIGNMENT_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_SOURCE_TYPE'
, SRC.SOURCE_TYPE)
, SRC.SOURCING_RULE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_SR_ASSIGNMENT_TYPE'
, SRC.ASSIGNMENT_TYPE)
, SRC.SHIP_METHOD
, MSC_GET_NAME.LOOKUP_MEANING1('SHIP_METHOD'
, SRC.SHIP_METHOD
, 3
, 0)
, SRC.HISTORICAL_ALLOC_QTY
, SRC.SR_INSTANCE_ID
, CAT.CATEGORY_SET_ID
, CAT.SR_CATEGORY_ID
, SRC.ASSIGNMENT_SET_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ASSIGNMENT_USE_TYPE'
, SRC.ASSIGNMENT_SET_TYPE)
, SRC.SUPPLIER_SITE_ID
, SRC.SUPPLIER_ID
, SRC.CIRCULAR_SRC
, DECODE(SRC.CIRCULAR_SRC
, 1
, SRC.RANK
, TO_NUMBER(NULL)) CIRCULAR_SRC_RANK
FROM MSC_SOURCING_RULES RULES
, MSC_TRADING_PARTNER_SITES SITE
, MSC_TRADING_PARTNERS VEN
, MSC_ITEMS ITEM
, MSC_ITEM_SOURCING SRC
, MSC_PLANS ORGS
, MSC_ITEM_CATEGORIES CAT
, MSC_APPS_INSTANCES INST
WHERE RULES.SOURCING_RULE_ID(+) = SRC.SOURCING_RULE_ID
AND SITE.PARTNER_SITE_ID(+) = SRC.SUPPLIER_SITE_ID
AND SITE.PARTNER_ID(+) = SRC.SUPPLIER_ID
AND SRC.SUPPLIER_ID = VEN.PARTNER_ID(+)
AND VEN.PARTNER_TYPE(+)=1
AND ITEM.INVENTORY_ITEM_ID = SRC.INVENTORY_ITEM_ID
AND SRC.PLAN_ID = ORGS.PLAN_ID
AND CAT.ORGANIZATION_ID=INST.VALIDATION_ORG_ID
AND CAT.SR_INSTANCE_ID=SRC.SR_INSTANCE_ID
AND CAT.INVENTORY_ITEM_ID=SRC.INVENTORY_ITEM_ID
AND INST.INSTANCE_ID=SRC.SR_INSTANCE_ID
AND SRC.ASSIGNMENT_SET_TYPE =2