VIEW: APPS.MSC_SUP_DEM_ENTRIES_VMI_V

Source

View Text - Preformatted

SELECT items.plan_id, items.sr_instance_id, sites.company_id customer_id, sites.company_site_id customer_site_id , cp.company_name customer_name, sites.company_site_name customer_site_name, sites1.company_id supplier_id, sites1.company_site_id supplier_site_id, cp1.company_name supplier_name, sites1.company_site_name supplier_site_name, items.inventory_item_id, items.item_name, items.description, msc_x_vmi_util_new.vmi_details_supplier( items.sr_instance_id, items.inventory_item_id, sites.company_id, sites.company_site_id, sites1.company_id, sites1.company_site_id, items.organization_id, items.supplier_id, items.supplier_site_id ) order_details, items.planner_code, items.organization_id, 'SUPPLIER_VMI' AS vmi_type, items.supplier_id AS aps_supplier_id, items.supplier_site_id AS aps_supplier_site_id, to_number(NULL),to_number(NULL), items.buyer_code AS BUYER_CODE from msc_vmi_item_v items, msc_trading_partners tp, msc_company_sites sites, msc_companies cp, msc_trading_partner_maps map, msc_trading_partner_maps map1, msc_company_sites sites1, msc_companies cp1, msc_trading_partner_maps map2 where items.organization_id = tp.sr_tp_id and items.sr_instance_id = tp.sr_instance_id and tp.partner_id = map.tp_key and map.map_type = 2 and map.company_key = sites.company_site_id and sites.company_id = cp.company_id and items.supplier_site_id = map1.tp_key and map1.map_type = 3 and map1.company_key = sites1.company_site_id and sites1.company_id = cp1.company_id and map2.tp_key = items.supplier_id and map2.map_type = 1 and EXISTS ( select 1 from msc_company_relationships rel where map2.company_key = rel.relationship_id and rel.relationship_type = 2 and rel.subject_id = cp.company_id and rel.object_id = cp1.company_id ) UNION ALL SELECT msi.plan_id AS plan_id, msi.sr_instance_id AS sr_instance_id, mcs2.company_id AS customer_id, mcs2.company_site_id AS customer_site_id, cp2.company_name AS customer_name, mcs2.company_site_name AS customer_site_name, mcs1.company_id AS supplier_id, mcs1.company_site_id AS supplier_site_id, cp1.company_name supplier_name, NULL AS supplier_site_name, msi.inventory_item_id, msi.item_name, msi.description, msc_x_vmi_util_new.vmi_details_customer(msi.inventory_item_id, msi.organization_id, msi.sr_instance_id, mcs2.company_id, mcs2.company_site_id, mcs1.company_id, mcs1.company_site_id) AS order_details, msi.planner_code, msi.organization_id, 'CUSTOMER_VMI' AS vmi_type, to_number(NULL), to_number(NULL), mtp.modeled_customer_id AS aps_customer_id, mtp.modeled_customer_site_id AS aps_customer_site_id, msi.buyer_name AS BUYER_CODE FROM msc_system_items msi, msc_trading_partners mtp, msc_trading_partner_maps map1, msc_company_sites mcs1, msc_companies cp1, msc_trading_partner_sites mtps, msc_trading_partner_maps map2, msc_company_sites mcs2, msc_companies cp2 WHERE msi.organization_id = mtp.sr_tp_id AND msi.sr_instance_id = mtp.sr_instance_id AND msi.inventory_planning_code = 7 AND msi.plan_id = -1 AND mtp.partner_type = 3 AND mtp.partner_id = map1.tp_key AND map1.map_type = 2 AND map1.company_key = mcs1.company_site_id AND mcs1.company_id = cp1.company_id AND mtp.modeled_customer_site_id = mtps.partner_site_id AND mtps.partner_site_id = map2.tp_key AND map2.map_type = 3 AND map2.company_key = mcs2.company_site_id AND mcs2.company_id = cp2.company_id

View Text - HTML Formatted

SELECT ITEMS.PLAN_ID
, ITEMS.SR_INSTANCE_ID
, SITES.COMPANY_ID CUSTOMER_ID
, SITES.COMPANY_SITE_ID CUSTOMER_SITE_ID
, CP.COMPANY_NAME CUSTOMER_NAME
, SITES.COMPANY_SITE_NAME CUSTOMER_SITE_NAME
, SITES1.COMPANY_ID SUPPLIER_ID
, SITES1.COMPANY_SITE_ID SUPPLIER_SITE_ID
, CP1.COMPANY_NAME SUPPLIER_NAME
, SITES1.COMPANY_SITE_NAME SUPPLIER_SITE_NAME
, ITEMS.INVENTORY_ITEM_ID
, ITEMS.ITEM_NAME
, ITEMS.DESCRIPTION
, MSC_X_VMI_UTIL_NEW.VMI_DETAILS_SUPPLIER( ITEMS.SR_INSTANCE_ID
, ITEMS.INVENTORY_ITEM_ID
, SITES.COMPANY_ID
, SITES.COMPANY_SITE_ID
, SITES1.COMPANY_ID
, SITES1.COMPANY_SITE_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.SUPPLIER_ID
, ITEMS.SUPPLIER_SITE_ID ) ORDER_DETAILS
, ITEMS.PLANNER_CODE
, ITEMS.ORGANIZATION_ID
, 'SUPPLIER_VMI' AS VMI_TYPE
, ITEMS.SUPPLIER_ID AS APS_SUPPLIER_ID
, ITEMS.SUPPLIER_SITE_ID AS APS_SUPPLIER_SITE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ITEMS.BUYER_CODE AS BUYER_CODE
FROM MSC_VMI_ITEM_V ITEMS
, MSC_TRADING_PARTNERS TP
, MSC_COMPANY_SITES SITES
, MSC_COMPANIES CP
, MSC_TRADING_PARTNER_MAPS MAP
, MSC_TRADING_PARTNER_MAPS MAP1
, MSC_COMPANY_SITES SITES1
, MSC_COMPANIES CP1
, MSC_TRADING_PARTNER_MAPS MAP2
WHERE ITEMS.ORGANIZATION_ID = TP.SR_TP_ID
AND ITEMS.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND TP.PARTNER_ID = MAP.TP_KEY
AND MAP.MAP_TYPE = 2
AND MAP.COMPANY_KEY = SITES.COMPANY_SITE_ID
AND SITES.COMPANY_ID = CP.COMPANY_ID
AND ITEMS.SUPPLIER_SITE_ID = MAP1.TP_KEY
AND MAP1.MAP_TYPE = 3
AND MAP1.COMPANY_KEY = SITES1.COMPANY_SITE_ID
AND SITES1.COMPANY_ID = CP1.COMPANY_ID
AND MAP2.TP_KEY = ITEMS.SUPPLIER_ID
AND MAP2.MAP_TYPE = 1
AND EXISTS ( SELECT 1
FROM MSC_COMPANY_RELATIONSHIPS REL
WHERE MAP2.COMPANY_KEY = REL.RELATIONSHIP_ID
AND REL.RELATIONSHIP_TYPE = 2
AND REL.SUBJECT_ID = CP.COMPANY_ID
AND REL.OBJECT_ID = CP1.COMPANY_ID ) UNION ALL SELECT MSI.PLAN_ID AS PLAN_ID
, MSI.SR_INSTANCE_ID AS SR_INSTANCE_ID
, MCS2.COMPANY_ID AS CUSTOMER_ID
, MCS2.COMPANY_SITE_ID AS CUSTOMER_SITE_ID
, CP2.COMPANY_NAME AS CUSTOMER_NAME
, MCS2.COMPANY_SITE_NAME AS CUSTOMER_SITE_NAME
, MCS1.COMPANY_ID AS SUPPLIER_ID
, MCS1.COMPANY_SITE_ID AS SUPPLIER_SITE_ID
, CP1.COMPANY_NAME SUPPLIER_NAME
, NULL AS SUPPLIER_SITE_NAME
, MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, MSC_X_VMI_UTIL_NEW.VMI_DETAILS_CUSTOMER(MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SR_INSTANCE_ID
, MCS2.COMPANY_ID
, MCS2.COMPANY_SITE_ID
, MCS1.COMPANY_ID
, MCS1.COMPANY_SITE_ID) AS ORDER_DETAILS
, MSI.PLANNER_CODE
, MSI.ORGANIZATION_ID
, 'CUSTOMER_VMI' AS VMI_TYPE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MTP.MODELED_CUSTOMER_ID AS APS_CUSTOMER_ID
, MTP.MODELED_CUSTOMER_SITE_ID AS APS_CUSTOMER_SITE_ID
, MSI.BUYER_NAME AS BUYER_CODE
FROM MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_TRADING_PARTNER_MAPS MAP1
, MSC_COMPANY_SITES MCS1
, MSC_COMPANIES CP1
, MSC_TRADING_PARTNER_SITES MTPS
, MSC_TRADING_PARTNER_MAPS MAP2
, MSC_COMPANY_SITES MCS2
, MSC_COMPANIES CP2
WHERE MSI.ORGANIZATION_ID = MTP.SR_TP_ID
AND MSI.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MSI.INVENTORY_PLANNING_CODE = 7
AND MSI.PLAN_ID = -1
AND MTP.PARTNER_TYPE = 3
AND MTP.PARTNER_ID = MAP1.TP_KEY
AND MAP1.MAP_TYPE = 2
AND MAP1.COMPANY_KEY = MCS1.COMPANY_SITE_ID
AND MCS1.COMPANY_ID = CP1.COMPANY_ID
AND MTP.MODELED_CUSTOMER_SITE_ID = MTPS.PARTNER_SITE_ID
AND MTPS.PARTNER_SITE_ID = MAP2.TP_KEY
AND MAP2.MAP_TYPE = 3
AND MAP2.COMPANY_KEY = MCS2.COMPANY_SITE_ID
AND MCS2.COMPANY_ID = CP2.COMPANY_ID