select mtp.partner_id supplier_id, mtps.partner_site_id supplier_site_id, mtp.partner_name supplier_name, decode(mtps.tp_site_code, null, mtp.partner_name||' ()', mtps.tp_site_code||':'||mtps.location||':'||mtps.operating_unit_name) supplier_site_code, r.zone, mtps.location, mtps.partner_address address, r.country_code, r.state_code, r.city_code, mtps.country, mtps.state, mtps.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_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=1 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=1 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 supplier_id, to_number(-23453) supplier_site_id, mtp.partner_name supplier_name, mtp.partner_name||' ()' supplier_site_code, to_char(null) zone, to_char(null) location, 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=1 union all select to_number(-23453) supplier_id, to_number(-23453) supplier_site_id, to_char(null) supplier_name, to_char(null) supplier_site_code, to_char(null) zone, to_char(null) location, 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
SELECT MTP.PARTNER_ID SUPPLIER_ID
, MTPS.PARTNER_SITE_ID SUPPLIER_SITE_ID
, MTP.PARTNER_NAME SUPPLIER_NAME
, DECODE(MTPS.TP_SITE_CODE
, NULL
, MTP.PARTNER_NAME||' ()'
, MTPS.TP_SITE_CODE||':'||MTPS.LOCATION||':'||MTPS.OPERATING_UNIT_NAME) SUPPLIER_SITE_CODE
, R.ZONE
, MTPS.LOCATION
, MTPS.PARTNER_ADDRESS ADDRESS
, R.COUNTRY_CODE
, R.STATE_CODE
, R.CITY_CODE
, MTPS.COUNTRY
, MTPS.STATE
, MTPS.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_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=1
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=1
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 SUPPLIER_ID
, TO_NUMBER(-23453) SUPPLIER_SITE_ID
, MTP.PARTNER_NAME SUPPLIER_NAME
, MTP.PARTNER_NAME||' ()' SUPPLIER_SITE_CODE
, TO_CHAR(NULL) ZONE
, TO_CHAR(NULL) LOCATION
, 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=1 UNION ALL SELECT TO_NUMBER(-23453) SUPPLIER_ID
, TO_NUMBER(-23453) SUPPLIER_SITE_ID
, TO_CHAR(NULL) SUPPLIER_NAME
, TO_CHAR(NULL) SUPPLIER_SITE_CODE
, TO_CHAR(NULL) ZONE
, TO_CHAR(NULL) LOCATION
, 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
|
|
|