select 'SAFCOM',trim(rownum),trim(EXTRACT(year FROM tbk_date))||','||trim(EXTRACT(month FROM tbk_date))||','||TBK_CUSTOMER||','||(SELECT AR_NAME FROM ke074t3.ID_AR_MASTER WHERE AR_CODE=TBK_CUSTOMER AND ROWNUM=1)||','||
trim(TBK_DATE)||','||trim(TBK_EXECUTIVE_CODE)||','||replace(trim((select upper( EXEC_NAME)  from ke074t3.ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ')||','||DECODE((SELECT USER_STATUS FROM ke074t3.ID_USER_MASTER WHERE USER_CODE=TBK_EXECUTIVE_CODE AND ROWNUM=1),'A','ACTIVE','NOT ACTIVE')||','||
trim(TBK_BOOKING_TYPE)||','||trim(TBK_BOOKING_NUMBER)||','||trim(TBSVH_SV_NUMBER)||','||(select apt_number from ke074t3.id_ap_trans where apt_detail_1=TBK_BOOKING_NUMBER and apt_detail_2=TBSVH_SV_NUMBER and rownum=1)||','||
replace(trim(TBSVH_PROVIDER),',',' ')||','||replace(trim((select tbpm_name from ke074t3.id_tb_provider_master where tbpm_provider=tbsvh_provider and rownum=1)),',',' ')||','||

TBSVH_AP_CODE||','||replace(replace((Select AP_NAME from ke074t3.id_ap_master where ap_code=tbsvh_ap_code and rownum=1),',','-'),'/','-')||','||d.AP_VAT_REGISTRATION_NO||','||
replace(trim(TBSVD_SERVICE),',',' ')||','||replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') ||','||(SELECT TBSM_SERVICE_TYPE FROM KE074T3.id_tb_service_master WHERE TBSM_SERVICE_CODE=TBSVD_SERVICE AND ROWNUM=1)||','||
trim(TBSVD_CURRENCY)||','||TBSVD_FOREIGN_AMOUNT||','||TBSVD_LOCAL_AMOUNT||','||(select -APT_LOCAL_AMOUNT from ke074t3.id_ap_trans where apt_detail_1=TBK_BOOKING_NUMBER and apt_detail_2=TBSVH_SV_NUMBER and rownum=1)||','||
(TBSVD_LOCAL_AMOUNT+nvl((select -APT_LOCAL_AMOUNT from ke074t3.id_ap_trans where apt_detail_1=TBK_BOOKING_NUMBER and apt_detail_2=TBSVH_SV_NUMBER and rownum=1),0))||','||
trim(TBSVD_FROM_DATE)||','||trim(TBSVD_TO_DATE)||','||(select Inv_nr from ke074t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)||','||

(select SUB_AR_code  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1))||','||
(select SUB_AR_DATE  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1))||','||
(select SUB_AR_USER  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1))||','||
(select SUB_AR_status  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1))||','||

Ke074t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number)||','||replace(trim(TBK_TMP_CUST_NAME),',',' ')||','||decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced')||','||
decode(tbsvh_status,'T','NOTPOSTED','POSTED')||','||(select apt_Description from ke074t3.id_ap_trans where apt_detail_1=TBK_BOOKING_NUMBER and apt_detail_2=TBSVH_SV_NUMBER and rownum=1)||','||
(select ART_NUMBER from ke074t3.id_ar_trans where art_type='TADN' and art_detail_3=TBK_BOOKING_NUMBER and art_detail_4=TBSVH_SV_NUMBER)||','||(select ART_LOCAL_AMOUNT from ke074t3.id_ar_trans where art_type='TADN' and art_detail_3=TBK_BOOKING_NUMBER and art_detail_4=TBSVH_SV_NUMBER)

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 where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and tbk_status<>'F' --and a.tbk_customer IN ('S0171','S0175','S0177','S0171A','S0171B','S0171C') and TBSVH_AP_CODE=AP_CODE and TBSVD_SV_SERIAL=TBSVH_SERIAL and a.tbk_date between '01-JUN-2018' and sysdate and TBSVH_STATUS in('P','T')
order by tbk_date,tbk_booking_number;
