select Branch, Seq, Year, Month, CUSTMER_CODE, CUSTOMER_NAME, BK_Date,TC , TC_Name, EMP_STATUS, PR, PO, Booker, BK_TYPE, BK_No, SV_NO,
Provider, Provider_Name, AP_Code, AP_NAme, SUP_PIN, Service, Service_Name, Service_Class, CCY, F_Amount, KES_AMOUNT, CHECKIN, CHECKOUT,
Invoice, Invoice_Status, Pax_Name, BK_Status, SV_Status, Supplier_Code, Supplier_Name,Nights, PROV_CCY, Unit_Rate,Units,Markup_Method,
Markup_Value,Disc_Method,Discount,INV_Value,Sell_CCY,Cost_F_Amount,EX_Rate,Local_amount,Invoiced_By, Invoiced_Date,BPV_NUMBER
from
(select 'HO' BRANCH,trim(rownum) Seq,trim(EXTRACT(year FROM tbk_date)) Year, trim(EXTRACT(month FROM tbk_date)) Month,
TBK_CUSTOMER CUSTMER_CODE, (SELECT AR_NAME FROM ID_AR_MASTER WHERE AR_CODE=TBK_CUSTOMER AND ROWNUM=1) CUSTOMER_NAME,
trim(TBK_DATE) BK_Date,trim(TBK_EXECUTIVE_CODE) TC,
replace(trim((select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
DECODE((SELECT USER_STATUS FROM KE014T3_SUPPORT.ID_USER_MASTER WHERE USER_CODE=TBK_EXECUTIVE_CODE AND ROWNUM=1),'A','ACTIVE','NOT ACTIVE') EMP_STATUS,
replace(trim(TBK_FLEX_2),',','') PR,replace(trim(TBK_FLEX_3),',','') PO,replace(trim(TBK_FLEX_4),',',' ') Booker,trim(TBK_BOOKING_TYPE) BK_TYPE,trim(TBK_BOOKING_NUMBER)BK_No,trim(TBSVH_SV_NUMBER) SV_NO,
replace(trim(TBSVH_PROVIDER),',',' ') Provider,replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbsvh_provider and rownum=1)),',',' ') Provider_Name,
TBSVH_AP_CODE AP_Code, replace(replace((Select AP_NAME from ke014t3_support.id_ap_master where ap_code=tbsvh_ap_code and rownum=1),',','-'),'/','-') AP_Name,
d.AP_VAT_REGISTRATION_NO SUP_PIN,
replace(trim(TBSVD_SERVICE),',',' ') Service,replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') Service_Name,
(SELECT TBSM_SERVICE_TYPE FROM KE014T3_SUPPORT.id_tb_service_master WHERE TBSM_SERVICE_CODE=TBSVD_SERVICE AND ROWNUM=1) SERVICE_CLASS,
trim(TBSVD_CURRENCY) CCY, TBSVD_FOREIGN_AMOUNT F_AMOUNT, TBSVD_LOCAL_AMOUNT KES_AMOUNT,trim(TBSVD_FROM_DATE) CHECKIN,trim(TBSVD_TO_DATE) CHECKOUT,
(select Inv_nr from ke014t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) invoice,
Ke014t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) Invoice_Status,
replace(trim(TBK_TMP_CUST_NAME),',',' ') PAX_NAME,
decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced') BK_Status,
decode(tbsvh_status,'T','NOTPOSTED','P','POSTED','VOIDED') SV_STATUS,
-- New Fields
--
Tbrd_supplier Supplier_Code,
replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbrd_supplier and rownum=1)),',',' ') Supplier_Name,
((Tbsvd_to_date)-(tbsvd_from_date)) Nights,
TBRD_PROVIDER_CURRENCY PROV_CCY,
TBRD_PROVIDER_RATE Unit_Rate,
TBRD_NO_OF_UNITS Units,
TBRD_MARKUP_METHOD Markup_Method,
TBRD_MARKUP_VALUE Markup_Value,
TBRD_DISCOUNT_METHOD Disc_Method,
TBRD_DISCOUNT_VALUE Discount,
TBRD_INVOICE_VALUE INV_Value,
TBRD_CURRENCY Sell_CCY,
TBRD_COST_F_AMOUNT Cost_F_Amount,
TBRD_EX_RATE EX_Rate,
TBRD_COST_L_AMOUNT Local_amount,
(select TBT_CREATED_BY from ke014t3.id_tb_trans where TBT_DOC_NUMBER=(select Inv_nr from ke014t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)and rownum=1) Invoiced_By,
(select TBT_CREATED_ON from ke014t3.id_tb_trans where TBT_DOC_NUMBER=(select Inv_nr from ke014t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)and rownum=1) Invoiced_Date
--
-- End of New Fields
from
ke014t3.id_tb_booking_header a,
ke014t3.ID_TB_SERVICE_VOUCHER_DETAILS B, 
ke014t3.id_tb_service_voucher_header c,
ke014t3.id_ap_master d,
Ke014t3.id_tb_booking_rate_details e
where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and tbk_status<>'F' and TBK_NEXT_PROCESS_NUMBER<7
and a.tbk_customer IN ('S0171','S0175','S0177')
and TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
And TBRD_REFERENCE=TBK_REFERENCE
and a.tbk_date between '01-JUN-2017' and sysdate and TBSVH_STATUS in('P','T','V')
and tbrd_system=tbsvh_sv_number
and tbrd_system is not null
union all
select 'SAFCOM' BRANCH, trim(rownum) Seq,trim(EXTRACT(year FROM tbk_date)) Year, trim(EXTRACT(month FROM tbk_date)) Month,
TBK_CUSTOMER CUSTMER_CODE, (SELECT AR_NAME FROM ID_AR_MASTER WHERE AR_CODE=TBK_CUSTOMER AND ROWNUM=1) CUSTOMER_NAME,
trim(TBK_DATE) BK_Date,trim(TBK_EXECUTIVE_CODE) TC,
replace(trim((select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
DECODE((SELECT USER_STATUS FROM KE014T3_SUPPORT.ID_USER_MASTER WHERE USER_CODE=TBK_EXECUTIVE_CODE AND ROWNUM=1),'A','ACTIVE','NOT ACTIVE') EMP_STATUS,
replace(trim(TBK_FLEX_2),',','') PR,replace(trim(TBK_FLEX_3),',','') PO,replace(trim(TBK_FLEX_4),',',' ') Booker,trim(TBK_BOOKING_TYPE) BK_TYPE,trim(TBK_BOOKING_NUMBER)BK_No,trim(TBSVH_SV_NUMBER) SV_NO,
replace(trim(TBSVH_PROVIDER),',',' ') Provider,replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbsvh_provider and rownum=1)),',',' ') Provider_Name,
TBSVH_AP_CODE AP_Code, replace(replace((Select AP_NAME from ke074t3_support.id_ap_master where ap_code=tbsvh_ap_code and rownum=1),',','-'),'/','-') AP_Name,
d.AP_VAT_REGISTRATION_NO SUP_PIN,
replace(trim(TBSVD_SERVICE),',',' ') Service,replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') Service_Name,
(SELECT TBSM_SERVICE_TYPE FROM KE074T3_SUPPORT.id_tb_service_master WHERE TBSM_SERVICE_CODE=TBSVD_SERVICE AND ROWNUM=1) SERVICE_CLASS,
trim(TBSVD_CURRENCY) CCY, TBSVD_FOREIGN_AMOUNT F_AMOUNT, TBSVD_LOCAL_AMOUNT KES_AMOUNT,trim(TBSVD_FROM_DATE) CHECKIN,trim(TBSVD_TO_DATE) CHECKOUT,
(select Inv_nr from ke074t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) invoice,
Ke074t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) Invoice_Status,
replace(trim(TBK_TMP_CUST_NAME),',',' ') PAX_NAME,
decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced') BK_Status,
decode(tbsvh_status,'T','NOTPOSTED','P','POSTED','V','VOIDED') SV_STATUS,
-- New Fields
--
Tbrd_supplier Supplier_Code,
replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbrd_supplier and rownum=1)),',',' ') Supplier_Name,
((Tbsvd_to_date)-(tbsvd_from_date)) Nights,
TBRD_PROVIDER_CURRENCY PROV_CCY,
TBRD_PROVIDER_RATE Unit_Rate,
TBRD_NO_OF_UNITS Units,
TBRD_MARKUP_METHOD Markup_Method,
TBRD_MARKUP_VALUE Markup_Value,
TBRD_DISCOUNT_METHOD Disc_Method,
TBRD_DISCOUNT_VALUE Discount,
TBRD_INVOICE_VALUE INV_Value,
TBRD_CURRENCY Sell_CCY,
TBRD_COST_F_AMOUNT Cost_F_Amount,
TBRD_EX_RATE EX_Rate,
TBRD_COST_L_AMOUNT Local_amount,
(select TBT_CREATED_BY from ke074t3.id_tb_trans where TBT_DOC_NUMBER=(select Inv_nr from ke074t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)and rownum=1) Invoiced_By,
(select TBT_CREATED_ON from ke074t3.id_tb_trans where TBT_DOC_NUMBER=(select Inv_nr from ke074t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)and rownum=1) Invoiced_Date
--
-- End of New Fields
from
ke074t3.id_tb_booking_header a,
ke074t3.ID_TB_SERVICE_VOUCHER_DETAILS B, 
ke074t3.id_tb_service_voucher_header c,
ke074t3.id_ap_master d,
Ke074t3.id_tb_booking_rate_details e
where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and tbk_status<>'F' and TBK_NEXT_PROCESS_NUMBER<7
and a.tbk_customer IN ('S0171','S0175','S0177')
and TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
And TBRD_REFERENCE=TBK_REFERENCE
and a.tbk_date between '01-JUN-2017' and sysdate and TBSVH_STATUS in('P','T','V')
and tbrd_system=tbsvh_sv_number
and tbrd_system is not null
)
left join 
ke074t3_support.bcd_audit_paid_sv_view
on sv_number=sv_no

(select ar_name from id_ar_master where ar_code=art_code and rownum=1) AR_Name,
select * from id_tb_service_voucher_header

select art_code,
sum(ART_LOCAL_AMOUNT) from id_ar_trans where art_doc_date<'01-MAR-2019'
group by art_code
having sum(ART_LOCAL_AMOUNT)>2000000
order by (sum(ART_LOCAL_AMOUNT)) 

select * from id_ar_master

select * from id_tb_ar_header where SUB_AR_DOC_NUMBER='TIN18110042'

select * from id_tb_service_voucher_header where tbsvh_sv_number='SV18110012'

select * from ftms_etravel.ftms_in where FTMS_ETRAVEL_DATE>'13-FEB-2019'

select ARM_M_AR,sum(ARM_M_AMOUNT * ARM_M_EX_RATE) from id_ar_match_details where ARM_M_AR in
('A0023','T0054','A0034','S0171C','W0016','R0002','Z0003','A0194','S0001','S0171','C0007','S0007','P0079','N0002','S0185','B0093','W0015','Z0017','S0171A','R0077','D0126','S0142','C0172','I0008','T0010','W0014','I0046','N0032','K0131','C0070','V0027','S0012','W0084','S0157','P0044','M0246')
and ARM_M_CURRENCY in ('USD','KES') and ARM_T_DATE<'01-JAN-2019' and ARM_M_DATE>'01-MAR-2019'
group by ARM_M_AR

select ARM_M_AR,(ARM_M_AMOUNT * ARM_M_EX_RATE) from id_ar_match_details where ARM_M_AR in
('W0015')
and ARM_M_CURRENCY in ('USD','KES') and ARM_T_DATE<'01-JAN-2019' and ARM_M_DATE>'01-MAR-2019'

select art_code,sum(-art_local_amount) from id_ar_trans where art_type in('BRV','BRVS','RBRV') and art_doc_date>'28-FEB-2019'
and art_code in 
('A0023','T0054','A0034','S0171C','W0016','R0002','Z0003','A0194','S0001','S0171','C0007','S0007','P0079','N0002','S0185','B0093','W0015','Z0017','S0171A','R0077','D0126','S0142','C0172','I0008','T0010','W0014','I0046','N0032','K0131','C0070','V0027','S0012','W0084','S0157','P0044','M0246')
group by art_code
order by art_code

select art_code,sum(ART_LOCAL_AMOUNT) from id_ar_trans where art_type in ('TIN','TCN','RTIN','RTCN','RIN','RRIN')
and art_doc_date>'28-FEB-2019'
and art_code in 
('A0023','T0054','A0034','S0171C','W0016','R0002','Z0003','A0194','S0001','S0171','C0007','S0007','P0079','N0002','S0185','B0093','W0015','Z0017','S0171A','R0077','D0126','S0142','C0172','I0008','T0010','W0014','I0046','N0032','K0131','C0070','V0027','S0012','W0084','S0157','P0044','M0246')
group by art_code
order by art_code


select * from tab where tname like '%NUMBER%'

select * from id_number_gen

select ART_CODE, art_number,art_doc_date, ART_LOCAL_AMOUNT from id_ar_trans where art_type='TIN' and art_doc_date between '01-JAN-2019' and '31-JAN-2019'
order by art_number, art_doc_date

select * from id_ar_match_details where ARM_M_AR in 
('NU0078-UN','NU0001-UN','U0032-UN','NF0008-UN','NU0007-UN','W0014-UN','I0001-UN','NW0015U-','NU0010-UN','NU0083-UN','NU0081-UN','NU0080-UN','W0012-UN','W0005-UN','NU0086UN','U0021-UN','K001UN  ','NU0004-UN','G0008-UN ')
and ARM_M_CURRENCY in ('USD','KES') and ARM_T_DATE<'01-JAN-2019' and ARM_M_DATE>'31-DEC-2018'



