eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.MSC_PHUB_SUPPLIERS_MV

Source


select distinct
  mr.sr_instance_id,
  mr.region_id,
  decode(mr.region_type,
    0, mr.country,
    1, mr.country || '-' || mr.state,
    2, mr.country || '-' || mr.state || '-' || mr.city,
    3, mr.country || '-' || mr.state || '-' || mr.city || '-' ||
mr.postal_code_from || '-' || mr.postal_code_to,
    mr.zone) zone,
  mr.country_code,
  mr.state_code,
  -23453 supplier_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') supplier_name,
  -23453 supplier_site_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED')
supplier_site_code,
  to_char(null) location,
  to_char(null) address,
  to_char(null) city,
  to_char(null) state,
  to_char(null) country
from
  msc_regions mr where
  mr.sr_instance_id = decode(
               fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	       null,(select max(instance_id) from msc_apps_instances),
	       fnd_profile.value('MSC_HUB_REGION_INSTANCE'))
-------------------------------------------------------------------------------
--- 2nd union. to get region for supplier/supplier_site, which has region
--- defined
------------------------------------------------------------------------------
union all
select  distinct
   mr.sr_instance_id,
  mrl.region_id,
  decode(mr.region_type,
    0, mr.country,
    1, mr.country || '-' || mr.state,
    2, mr.country || '-' || mr.state || '-' || mr.city,
    3, mr.country || '-' || mr.state || '-' || mr.city || '-' ||
mr.postal_code_from || '-' || mr.postal_code_to,
    mr.zone) zone,
  mr.country_code,
  mr.state_code,
  mtp.partner_id supplier_id,
  mtp.partner_name supplier_name,
  nvl(mtps.partner_site_id,-23453) supplier_site_id,
  nvl(mtps.tp_site_code,
    msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED'))
supplier_site_code,
  mtps.location location,
  mtps.partner_address address,
  mtps.city,
  mtps.state,
  mtps.country
from
  msc_trading_partners mtp,
  msc_trading_partner_sites mtps,
  msc_tp_site_id_lid mtsil,
  msc_regions mr,
  msc_region_locations mrl,
  (	       select mtp.partner_id,mtps.partner_site_id tp_site_id, max(mrl.region_type) region_type
  from
    msc_tp_site_id_lid mtsil,
    msc_region_locations mrl,
    msc_regions mr,msc_trading_partner_sites mtps,msc_trading_partners mtp
  where mtsil.sr_instance_id = mrl.sr_instance_id
    and mtsil.location_id = mrl.location_id
    and mr.region_id = mrl.region_id
    and mr.sr_instance_id=mrl.sr_instance_id               --- new condition
    and mtp.partner_id=mtps.partner_id
    and mtps.sr_tp_site_id = mtsil.sr_tp_site_id
    and mtps.partner_site_id = mtsil.tp_site_id
    and mtp.partner_type=1
    and mr.sr_instance_id = decode(
               fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	       null,(select max(instance_id) from msc_apps_instances),
	       fnd_profile.value('MSC_HUB_REGION_INSTANCE'))
    group by mtp.partner_id,mtps.partner_site_id) t1                  --- make changes
where mtp.partner_type = 1
  and mtp.partner_id = mtps.partner_id
  and mtps.partner_site_id = t1.tp_site_id
  and mtp.partner_id = t1.partner_id
  and mtps.partner_site_id = mtsil.tp_site_id
  and mtps.sr_tp_Site_id = mtsil.sr_tp_Site_id   ---###
  and mtsil.sr_instance_id = mrl.sr_instance_id   -- keep this as otherwise, it returns multiple rows for same supplier/supplier_site
  and mtsil.location_id = mrl.location_id
  and mrl.region_type = t1.region_type
  and mr.region_id = mrl.region_id
  and mr.region_Type=mrl.region_type
  and mr.sr_instance_id  =mrl.sr_instance_id            -- new condition
  and mr.sr_instance_id=decode(
               fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	       null,(select max(instance_id) from msc_apps_instances),
	       fnd_profile.value('MSC_HUB_REGION_INSTANCE'))
--------------------------------------------------------------
--- 3rd union to pick out supplier/supplier_site which do not have
--- location mapping in msc_tp_site_id_lid
--------------------------------------------------------------
union all
select  distinct
  decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	 null,(select max(instance_id) from msc_apps_instances),
	 fnd_profile.value('MSC_HUB_REGION_INSTANCE')) sr_instance_id,
  -23453 region_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') zone,
  to_char(null) country_code,
  to_char(null) state_code,
  mtp.partner_id supplier_id,
  mtp.partner_name supplier_name,
  nvl(mtps.partner_site_id,-23453) supplier_site_id,
  nvl(mtps.tp_site_code,
    msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED'))
  supplier_site_code,
  mtps.location location,
  mtps.partner_address address,
  mtps.city,
  mtps.state,
  mtps.country
from
  msc_trading_partners mtp,
  msc_trading_partner_sites mtps
 where mtp.partner_id = mtps.partner_id
  and mtp.partner_type = 1
  and  mtps.partner_site_id not in
          (select tp_site_id from  msc_tp_site_id_lid mtsil
	     where mtsil.location_id is not null and
	       mtsil.sr_instance_id =
	           decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	           null,(select max(instance_id) from msc_apps_instances),
	           fnd_profile.value('MSC_HUB_REGION_INSTANCE')))
--------------------------------------------------------------------------
--- 4th union to pick out row which has mapping in msc_tp_site_id_lid
--- but the location_id is not mapping to any region
--------------------------------------------------------------------------
union all
select  distinct
  decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	 null,(select max(instance_id) from msc_apps_instances),
	 fnd_profile.value('MSC_HUB_REGION_INSTANCE')) sr_instance_id,
  -23453 region_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') zone,
  to_char(null) country_code,
  to_char(null) state_code,
  mtp.partner_id supplier_id,
  mtp.partner_name supplier_name,
  nvl(mtps.partner_site_id,-23453) supplier_site_id,
  nvl(mtps.tp_site_code,
    msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED'))
  supplier_site_code,
  mtps.location location,
  mtps.partner_address address,
  mtps.city,
  mtps.state,
  mtps.country
from
  msc_trading_partners mtp,
  msc_trading_partner_sites mtps,msc_tp_site_id_lid mtsil
 where mtp.partner_id = mtps.partner_id
  and mtp.partner_type = 1
  and mtps.partner_site_id=mtsil.tp_site_id
  and mtps.sr_tp_site_id=mtsil.sr_tp_site_id
  and mtsil.sr_instance_id =decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	           null,(select max(instance_id) from msc_apps_instances),
	           fnd_profile.value('MSC_HUB_REGION_INSTANCE'))
  and  mtsil.location_id not in
          (select location_id from msc_region_locations
	     where sr_instance_id =
	           decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	           null,(select max(instance_id) from msc_apps_instances),
	           fnd_profile.value('MSC_HUB_REGION_INSTANCE')))
  --------------------------------------------------------
  ---5th union to pick out rows which do not have partner_site defined
  --------------------------------------------------------
union all
select  distinct
  decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	 null,(select max(instance_id) from msc_apps_instances),
	 fnd_profile.value('MSC_HUB_REGION_INSTANCE')) sr_instance_id,
  -23453 region_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') zone,
  to_char(null) country_code,
  to_char(null) state_code,
  mtp.partner_id supplier_id,
  mtp.partner_name supplier_name,
  nvl(mtps.partner_site_id,-23453) supplier_site_id,
  nvl(mtps.tp_site_code,
    msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED'))
supplier_site_code,
  mtps.location location,
  mtps.partner_address address,
  mtps.city,
  mtps.state,
  mtps.country
from
  msc_trading_partners mtp,
  msc_trading_partner_sites mtps
where mtp.partner_id = mtps.partner_id (+)
  and mtp.partner_type = 1
  and mtps.partner_site_id is null
---------------------------------------------------------------------
--- 6th union to handle case where supplier is not null
--- while supplier_site_id is null. it should use union
--- instead of union all since othewise, it may cause duplicated
--- rows
-----------------------------------------------------------------------
union
select  distinct
  decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	 null,(select max(instance_id) from msc_apps_instances),
	 fnd_profile.value('MSC_HUB_REGION_INSTANCE')) sr_instance_id,
  -23453 region_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') zone,
  to_char(null) country_code,
  to_char(null) state_code,
  mtp.partner_id supplier_id,
  mtp.partner_name supplier_name,
  -23453  supplier_site_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') supplier_site_code,
  to_char(null) location,
  to_char(null) address,
  to_char(null) city,
  to_char(null) state,
  to_char(null) country
from
  msc_trading_partners mtp
 where mtp.partner_type = 1
----------------------------------------------------------------------------------------
--- 7th union to handle supplier/supplier_site is null
---------------------------------------------------------------------------------------
union all
select  distinct
  decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	 null,(select max(instance_id) from msc_apps_instances),
	 fnd_profile.value('MSC_HUB_REGION_INSTANCE')) sr_instance_id,
  -23453 region_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') zone,
  to_char(null) country_code,
  to_char(null) state_code,
  -23453 supplier_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') supplier_name,
  -23453 supplier_site_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED')
supplier_site_code,
  to_char(null) location,
  to_char(null) address,
  to_char(null) city,
  to_char(null) state,
  to_char(null) country
from
  dual
  -----------------------------------------------------------------------------------
  --- 8th union to handle supplier/supplier_site is 0
  -----------------------------------------------------------------------------------
  union all
select  distinct
  decode(fnd_profile.value('MSC_HUB_REGION_INSTANCE'),
	 null,(select max(instance_id) from msc_apps_instances),
	 fnd_profile.value('MSC_HUB_REGION_INSTANCE')) sr_instance_id,
  0 region_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') zone,
  to_char(null) country_code,
  to_char(null) state_code,
  0 supplier_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED') supplier_name,
  0 supplier_site_id,
  msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED')
supplier_site_code,
  to_char(null) location,
  to_char(null) address,
  to_char(null) city,
  to_char(null) state,
  to_char(null) country
from
  dual