
select distinct TBPL_PROVIDER, count(TBPL_PROVIDER)  from ID_TB_PROVIDER_LINKS
group by TBPL_PROVIDER
order by TBPL_PROVIDER 

select tbpm_provider from id_tb_provider_master where TBPM_STATUS='A' and tbpm_provider not in (select tbpl_provider from ID_TB_PROVIDER_LINKS) 

select * from id_tb_provider_master



select distinct TBPL_PROVIDER, count(TBPL_PROVIDER)  from ID_TB_PROVIDER_LINKS
group by TBPL_PROVIDER
having count(TBPL_PROVIDER)>20
order by select * from tab where upper(tname) like '%LINK%'

select distinct TBPL_PROVIDER, count(TBPL_PROVIDER)  from ID_TB_PROVIDER_LINKS
group by TBPL_PROVIDER
order by TBPL_PROVIDER 

select tbpm_provider from id_tb_provider_master where TBPM_STATUS='A' and tbpm_provider not in (select tbpl_provider from ID_TB_PROVIDER_LINKS) 

select * from id_tb_provider_master

--stop tbo, dotw, gullivers, amd cont store being hotel, make them tour opeator and add all suppliers under them


select distinct TBPL_PROVIDER, count(TBPL_PROVIDER)  from ID_TB_PROVIDER_LINKS
group by TBPL_PROVIDER
having count(TBPL_PROVIDER)>20
order by count(TBPL_PROVIDER) desc

select TBPM_SUPPLY_CITY, TBPM_PROVIDER from ke074t3.id_tb_provider_master where tbpm_provider
not in (select tbsvh_provider from ke074t3.id_tb_service_voucher_header)
and TBPM_CREATED_ON>'01-JAN-2019'

select * from id_tb_city_master


select * from id_tb_service_voucher_header where tbsvh_provider in ('GULLIVERS','AMD STORE','DESTINATIO') and TBSVH_DATE>'01-JAN-2019'

select *  from id_tb_provider_links where TBPL_PROVIDER=''



select *  from id_tb_provider_links 

select * from id_tb_provider_links where TBPL_PROVIDER=''

select * from id_tb_provider_master where tbpm_ap_link=''
select * from id_tb_city_master
select * from id_tb_country_master

-- List for Sharon
select tbpm_supply_city,
replace(replace(replace(replace(replace((select tbcm_city_name from id_tb_city_master where tbpm_supply_city=TBCM_CITY and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') City_name,
(select TBCM_COUNTRY from id_tb_city_master where tbpm_supply_city=TBCM_CITY and rownum=1) country_code,
replace(replace(replace(replace(replace((select TBCM_COUNTRY_NAME from id_tb_country_master where TBCM_COUNTRY_CODE=(select TBCM_COUNTRY from id_tb_city_master where tbpm_supply_city=TBCM_CITY and rownum=1) and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') Country_name,
TBPM_AP_LINK,
replace(replace(replace(replace(replace((select ap_name from id_ap_master where TBPM_AP_LINK=ap_code and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ap_name,
TBPM_PROVIDER,
replace(replace(replace(replace(replace((TBPM_NAME),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') Provider_Name,
(select max(tbsvh_date) from id_tb_service_voucher_header where tbsvh_provider=tbpm_provider) last_trans_date
from id_tb_provider_master where TBPM_AP_LINK is not null and tbpm_status='A'
order by upper(trim(TBPM_name))
-- List for Sharon end

-- Make Excel Sheet

--Go to TBPM
-- Check teh status in TBPM whether Tour Operator , if not change
-- Make Type as Provider
-- See the Linked SUppliers Count and derrive the Excel insert sheet count starter

select 'TRAVEL ETH',TBPM_PROVIDER,TBPM_SUPPLY_CITY from id_tb_provider_master where TBPM_ap_link='PT035' and tbpm_provider
not in (select tbpl_provider from id_tb_provider_links where
TBPL_PROVIDER='TRAVEL ETH') and TBPM_STATUS='A' and tbpm_provider<>'TRAVEL ETH'

-- Run teh excel sheet with update sttement

--Update TBPM make AP_Link null
update id_tb_provider_master set tbpm_ap_link='' where tbpm_ap_link='PT035' and tbpm_provider<>'TRAVEL ETH'

--looking for hoels we never made bookings and block them

select * from id_tb_service_voucher_header where tbsvh_supplier='LONRHO' or tbsvh_provider='LONRHO'

update id_tb_provider_master set tbpm_status='B' where tbpm_provider in
(select tbpm_provider from id_tb_provider_master 
where tbpm_status<>'A' and tbpm_provider not in
(select distinct tbsvh_provider from id_tb_service_voucher_header))

update id_tb_provider_master set tbpm_status='B' where tbpm_provider in
(select distinct tbsvh_provider from id_tb_service_voucher_header
group by tbsvh_provider
having max(TBSVH_DATE)<'31-MAY-2017')

update id_tb_provider_master set tbpm_status='B' where tbpm_provider in
(select distinct TBPM_PROVIDER
from id_tb_provider_master where TBPM_AP_LINK is not null and tbpm_status='A'
and
(select max(tbsvh_date) from id_tb_service_voucher_header where tbsvh_provider=tbpm_provider) is null)

select count(*) from id_tb_provider_masterke074t3_support.BCD_SAFCOM_TOURS_SLS_NEW_COMBI
