create or replace view bcd_ict_Ticket_invoice_status
as
(
select AIRLINE, TKT_NO, extract (year from TKT_DATE) Year,
extract (month from TKT_DATE) Month,
(
case when extract(day from TKT_DATE)<16 then 'FN1' else 'FN2' end
) Fortnight,
(
case when INV_STATUS='C' then 'AIRLINECARD' 
when CUST_CODE='C0075' then 'CASH'
when CUST_CODE='C0076' then 'CREDITCARD'
when cust_code is null then 'NOCUSTCODE'
else 'CREDIT'
end
) PAY_Mode,
TKT_DATE,
TC, TC_Name, INV_TYPE, INV_NO, INV_DATE,CUST_CODE, INV_CCY, INV_VALUE, AR_NAME, Invoiced_By, User_Name,Invoiced_date, INV_STATUS
from
(
select a.stock_airline AIRLINE,a.ticket_Number TKT_NO, a.Ticket_date TKT_DATE, ticket_booking_clerk TC,
(select exec_name from id_executive_master where exec_code=a.ticket_booking_clerk and rownum=1) TC_Name,
(select AR_DOC_TYPE from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1) INV_TYPE,
(select AR_DOC_number from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1) INV_NO,
(select AR_DATE from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1) INV_DATE,
(select AR_CODE from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1) CUST_CODE,
(select ar_name from id_ar_master where ar_code=
(select AR_CODE from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1)
) AR_NAME,
ticket_currency INV_CCY,
ticket_net_receivable INV_VALUE,
(select SUB_AR_USER from id_travel_ar_header where
(
(select AR_DOC_number from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1)=
sub_ar_doc_number and rownum=1
)
and rownum=1) Invoiced_By,
(select user_name from id_user_master where user_code=
(select SUB_AR_USER from id_travel_ar_header where
(
(select AR_DOC_number from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1)=
sub_ar_doc_number and rownum=1
)
and rownum=1)) User_Name,
(select SUB_AR_CREATED from id_travel_ar_header where
(
(select AR_DOC_number from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1)=
sub_ar_doc_number and rownum=1
)
and rownum=1) Invoiced_Date, 
((select SUB_AR_status from id_travel_ar_header where
(
(select AR_DOC_number from id_ticket_invoice_details where ticket_number=a.ticket_number and rownum=1)=
sub_ar_doc_number and rownum=1
)
and rownum=1)) INV_STATUS
from
ticket_view_t3 a
where
a.ticket_status='S'
and a.ticket_date>'31-JUL-2019'
)
where inv_type in ('TIN','UIN') or inv_type is null
)
