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.

VIEW: APPS.MSC_PHUB_CUSTOMERS_MV#

Source

View Text - Preformatted


select     to_number(-23453) customer_id,     to_number(-23453) customer_site_id,     mr.sr_instance_id,     mr.region_id,     to_char(null) customer_name,     to_char(null) customer_site,     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,         zone) zone,     to_char(null) address,     mr.country_code,     mr.state_code,     mr.city_code,     mr.country,     mr.state,     mr.city from msc_regions mr union all select     mtp.partner_id customer_id,     mtps.partner_site_id customer_site_id,     to_number(-23453) sr_instance_id,     to_number(-23453) region_id,     mtp.partner_name customer_name,     decode(mtps.tp_site_code, null,         mtp.partner_name||' ()',         mtps.tp_site_code||':'||mtps.location||':'||mtps.operating_unit_name) customer_site,     r.zone,     mtps.partner_address address,     r.country_code,     r.state_code,     r.city_code,     nvl(r.country, mtps.country) country,     nvl(r.state, mtps.state) state,     nvl(r.city, mtps.city) city from     msc_trading_partners mtp,     msc_trading_partner_sites mtps,     (select         mtsil.sr_tp_site_id,         mtsil.tp_site_id,         mrl.region_type,         rank() over(partition by mtsil.sr_tp_site_id, mtsil.tp_site_id             order by mrl.region_type desc, mtsil.sr_instance_id) n,         mr.country,         mr.state,         mr.city,         mr.country_code,         mr.state_code,         mr.city_code,         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     from         msc_tp_site_id_lid mtsil,         msc_region_locations mrl,         msc_regions mr     where mtsil.sr_instance_id=mrl.sr_instance_id         and mtsil.partner_type=2         and mtsil.location_id=mrl.location_id         and mr.region_id=mrl.region_id         and mr.sr_instance_id=mrl.sr_instance_id     ) r where mtp.partner_id=mtps.partner_id     and mtp.partner_type=2     and mtps.sr_tp_site_id=r.sr_tp_site_id(+)     and mtps.partner_site_id=r.tp_site_id(+)     and r.n(+)=1 union all select     mtp.partner_id customer_id,     to_number(-23453)  customer_site_id,     to_number(-23453) sr_instance_id,     to_number(-23453) region_id,     mtp.partner_name customer_name,     mtp.partner_name||' ()' customer_site,     to_char(null) zone,     to_char(null) address,     to_char(null) country_code,     to_char(null) state_code,     to_char(null) city_code,     to_char(null) country,     to_char(null) state,     to_char(null) city from msc_trading_partners mtp where mtp.partner_type=2 union all select     to_number(-23453) customer_id,     to_number(-23453) customer_site_id,     to_number(-23453) sr_instance_id,     to_number(-23453) region_id,     to_char(null) customer_name,     to_char(null) customer_site,     to_char(null) zone,     to_char(null) address,     to_char(null) country_code,     to_char(null) state_code,     to_char(null) city_code,     to_char(null) country,     to_char(null) state,     to_char(null) city from dual

View Text - HTML Formatted

SELECT TO_NUMBER(-23453) CUSTOMER_ID
, TO_NUMBER(-23453) CUSTOMER_SITE_ID
, MR.SR_INSTANCE_ID
, MR.REGION_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) CUSTOMER_SITE
, 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
, ZONE) ZONE
, TO_CHAR(NULL) ADDRESS
, MR.COUNTRY_CODE
, MR.STATE_CODE
, MR.CITY_CODE
, MR.COUNTRY
, MR.STATE
, MR.CITY
FROM MSC_REGIONS MR UNION ALL SELECT MTP.PARTNER_ID CUSTOMER_ID
, MTPS.PARTNER_SITE_ID CUSTOMER_SITE_ID
, TO_NUMBER(-23453) SR_INSTANCE_ID
, TO_NUMBER(-23453) REGION_ID
, MTP.PARTNER_NAME CUSTOMER_NAME
, DECODE(MTPS.TP_SITE_CODE
, NULL
, MTP.PARTNER_NAME||' ()'
, MTPS.TP_SITE_CODE||':'||MTPS.LOCATION||':'||MTPS.OPERATING_UNIT_NAME) CUSTOMER_SITE
, R.ZONE
, MTPS.PARTNER_ADDRESS ADDRESS
, R.COUNTRY_CODE
, R.STATE_CODE
, R.CITY_CODE
, NVL(R.COUNTRY
, MTPS.COUNTRY) COUNTRY
, NVL(R.STATE
, MTPS.STATE) STATE
, NVL(R.CITY
, MTPS.CITY) CITY
FROM MSC_TRADING_PARTNERS MTP
, MSC_TRADING_PARTNER_SITES MTPS
, (SELECT MTSIL.SR_TP_SITE_ID
, MTSIL.TP_SITE_ID
, MRL.REGION_TYPE
, RANK() OVER(PARTITION BY MTSIL.SR_TP_SITE_ID
, MTSIL.TP_SITE_ID ORDER BY MRL.REGION_TYPE DESC
, MTSIL.SR_INSTANCE_ID) N
, MR.COUNTRY
, MR.STATE
, MR.CITY
, MR.COUNTRY_CODE
, MR.STATE_CODE
, MR.CITY_CODE
, 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
FROM MSC_TP_SITE_ID_LID MTSIL
, MSC_REGION_LOCATIONS MRL
, MSC_REGIONS MR
WHERE MTSIL.SR_INSTANCE_ID=MRL.SR_INSTANCE_ID
AND MTSIL.PARTNER_TYPE=2
AND MTSIL.LOCATION_ID=MRL.LOCATION_ID
AND MR.REGION_ID=MRL.REGION_ID
AND MR.SR_INSTANCE_ID=MRL.SR_INSTANCE_ID ) R
WHERE MTP.PARTNER_ID=MTPS.PARTNER_ID
AND MTP.PARTNER_TYPE=2
AND MTPS.SR_TP_SITE_ID=R.SR_TP_SITE_ID(+)
AND MTPS.PARTNER_SITE_ID=R.TP_SITE_ID(+)
AND R.N(+)=1 UNION ALL SELECT MTP.PARTNER_ID CUSTOMER_ID
, TO_NUMBER(-23453) CUSTOMER_SITE_ID
, TO_NUMBER(-23453) SR_INSTANCE_ID
, TO_NUMBER(-23453) REGION_ID
, MTP.PARTNER_NAME CUSTOMER_NAME
, MTP.PARTNER_NAME||' ()' CUSTOMER_SITE
, TO_CHAR(NULL) ZONE
, TO_CHAR(NULL) ADDRESS
, TO_CHAR(NULL) COUNTRY_CODE
, TO_CHAR(NULL) STATE_CODE
, TO_CHAR(NULL) CITY_CODE
, TO_CHAR(NULL) COUNTRY
, TO_CHAR(NULL) STATE
, TO_CHAR(NULL) CITY
FROM MSC_TRADING_PARTNERS MTP
WHERE MTP.PARTNER_TYPE=2 UNION ALL SELECT TO_NUMBER(-23453) CUSTOMER_ID
, TO_NUMBER(-23453) CUSTOMER_SITE_ID
, TO_NUMBER(-23453) SR_INSTANCE_ID
, TO_NUMBER(-23453) REGION_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) CUSTOMER_SITE
, TO_CHAR(NULL) ZONE
, TO_CHAR(NULL) ADDRESS
, TO_CHAR(NULL) COUNTRY_CODE
, TO_CHAR(NULL) STATE_CODE
, TO_CHAR(NULL) CITY_CODE
, TO_CHAR(NULL) COUNTRY
, TO_CHAR(NULL) STATE
, TO_CHAR(NULL) CITY
FROM DUAL