(SELECT
replace(replace(replace(replace(replace(TBK_TMP_CUST_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') Traveller_Name,
(replace(replace(replace(replace(replace((select tbpm_name from id_tb_provider_master where tbpm_provider=tbrd_provider and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-')||' '||
replace(replace(replace(replace(replace(tbrd_supplier,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-'))Hotel_Name,
replace(replace(replace(replace(replace((decode(tbk_booking_Type,'IB','Domestic : ','International: ')||' City: '||(tbrd_city||'-'||TBRD_FLEX_V_1||' CID: '||TBRD_FROM_DATE||' COD: '||TBRD_TO_DATE||' - Nights : '||
to_char((to_date(TBRD_TO_DATE,'dd-MM-yyyy'))-(to_date(TBRD_from_DATE,'dd-MM-yyyy')))||' Units: '||TBRD_NO_OF_UNITS)),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') Service_Description,
TBT_DOC_NUMBER Invoice_No,
tbt_doc_date Invoice_Date,
tbt_currency,
((round((TBRD_INVOICE_VALUE/tbrd_ex_rate),2))-TBRD_MARKUP_VALUE ) Service_Cost,
TBRD_MARKUP_VALUE Markup,
round((TBRD_INVOICE_VALUE/tbrd_ex_rate),2) Invoice_Amount,
TBT_OTHER_REFERENCE LPO,
TBK_DATE Booking_Date,
TBK_CUSTOMER Customer_Code,
TBT_CREATED_ON Created_On,
tbrd_service
FROM
ID_TB_TRANS,
ID_TB_BOOKING_HEADER,
id_tb_provider_master,
id_tb_booking_rate_details,
id_ar_trans
where
TBRD_REFERENCE=TBK_REFERENCE
and TBT_BOOK_NUMBER = TBK_BOOKING_NUMBER
and TBPM_PROVIDER = TBT_PROVIDER
AND TBK_CUSTOMER in ('S0185')
and TBT_DOC_TYPE = 'TIN'
and tbt_status='P'
and TBRD_SYS_TYPE='SV'
and tbrd_status<>'V'
and TBT_FOREIGN_AMOUNT=round((TBRD_INVOICE_VALUE/tbrd_ex_rate),2)
and art_number=TBT_DOC_NUMBER
--and art_foreign_balance<>0 and ART_BALANCE_STATUS<>'C'
--and TBRD_FLEX_V_1 not in ('CAR','TRANSFERS')
--and tbrd_service not in ('CAR','TRANSFERS')
and tbk_booking_number='OB18110061'
union all
SELECT
replace(replace(replace(replace(replace(TBK_TMP_CUST_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') Traveller_Name,
(replace(replace(replace(replace(replace((select tbpm_name from id_tb_provider_master where tbpm_provider=tbrd_provider and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-')||' '||tbrd_supplier) Hotel_Name,
replace(replace(replace(replace(replace((decode(tbk_booking_Type,'IB','Domestic : ','International: ')||' City: '||(tbrd_city||'-'||TBRD_FLEX_V_1||' ON : '||TBRD_FROM_DATE||' Till : '||TBRD_TO_DATE||' Units: '||TBRD_NO_OF_UNITS)),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') Service_Description,
TBT_DOC_NUMBER Invoice_No,
tbt_doc_date Invoice_Date,
tbt_currency,
((round((TBRD_INVOICE_VALUE/tbrd_ex_rate),2))-TBRD_MARKUP_VALUE ) Service_Cost,
TBRD_MARKUP_VALUE Markup,
round((TBRD_INVOICE_VALUE/tbrd_ex_rate),2) Invoice_Amount,
TBT_OTHER_REFERENCE LPO,
TBK_DATE Booking_Date,
TBK_CUSTOMER Customer_Code,
TBT_CREATED_ON Created_On
FROM
ID_TB_TRANS,
ID_TB_BOOKING_HEADER,
id_tb_provider_master,
id_tb_booking_rate_details,
id_ar_trans
where
TBRD_REFERENCE=TBK_REFERENCE
and TBT_BOOK_NUMBER = TBK_BOOKING_NUMBER
and TBPM_PROVIDER = TBT_PROVIDER
AND TBK_CUSTOMER in ('S0185')
and TBT_DOC_TYPE = 'TIN'
and tbt_status='P'
and TBRD_SYS_TYPE='SV'
and tbrd_status<>'V'
and TBT_FOREIGN_AMOUNT=round((TBRD_INVOICE_VALUE/tbrd_ex_rate),2)
and art_number=TBT_DOC_NUMBER
--and art_foreign_balance<>0 and ART_BALANCE_STATUS<>'C'
--and TBRD_FLEX_V_1 in ('CAR','TRANSFERS')
and tbrd_service in ('CAR','TRANSFERS')
and tbk_booking_number='OB18110061'
)
order by Invoice_date, Invoice_no


select * from id_ar_trans where ART_NUMBER='TIN19040646'
select * from id_tb_trans where TBT_DOC_NUMBER='TIN19040646'

select * from id_tb_booking_rate_details 
where TBRD_SYS_TYPE='SV'
and tbrd_status<>'V'
and tbrd_reference='38026'

select * from id_tb_booking_header where tbk_booking_number='OB19040002'
