TAAS

Controller:- AirticketController

Genaral Table Query -> Refer Ticket/Voucher Search Report Module Query
Issue Tab  :- 

SELECT sd.pk_supp_doc_no_id,
                sd.vhr_supp_doc_no,
                sd.dat_issue,
                sd.dat_cancel,                                                                                               
                sd.vhr_pnr_number,
                sd.vhr_pax_name,
                sd.fk_pax_profile_id,
                sd.dat_refund,
                sd.sin_sys_current_status,
                sd.fk_branch_id,
                sd.dat_from,
                sd.dat_to,
                sd.sin_no_of_pax,
                sd.sin_sys_sale_side_status,
                sd.sin_sys_refund_side_status,

                tkt.vhr_last_conjuction_ticket_no,
                tkt.fk_ticket_type_id,
                tkt.vhr_class_chr,
                tkt.sin_class,
                tkt.vhr_sector,
                tkt.txt_remarks,
                tkt.sin_feeding_channel,
                tkt.sin_gds_company,

                tktgen.fk_airline_master_id,
                tktgen.vhr_tour_code,
                tktgen.vhr_original_issue_ticket,
                tktgen.vhr_re_issue_ticket,
                tktgen.vhr_fare_basis,
                tktgen.fk_ticketing_counter_staff_id,
                tktgen.fk_booking_counter_staff_id,
                tktgen.vhr_booking_agency_office_id,
                tktgen.vhr_air_ticketing_agency_office_id,

                tfd.dbl_fare,
                tfd.dbl_tax,
                tfd.dbl_supplier_amount,
                tfd.dbl_normal_profit,
                tfd.dbl_customer_price

            FROM service.tbl_supp_doc_no AS sd  
            LEFT JOIN airticket.tbl_ticket_gen_data AS tktgen
                ON sd.pk_supp_doc_no_id = tktgen.fk_supp_doc_no_id
            LEFT JOIN airticket.tbl_ticket AS tkt
                ON sd.pk_supp_doc_no_id = tkt.fk_supp_doc_no_id
            LEFT JOIN service.tbl_supp_doc_fare AS tfd
                ON tkt.fk_ticket_cus_ledg_fare_id=tfd.pk_supp_doc_fare_id
            WHERE sd.sin_last_action != -1 AND sd.vhr_supp_doc_no = '?';                                                                                                             

Query -> Original Issue Re-Issue Ticket List
-------------------------------------
SELECT   sd.pk_supp_doc_no_id,
                sd.vhr_supp_doc_no,
                sd.dat_issue,
                sd.dat_cancel,       

                tfd.dbl_fare,
                tfd.dbl_tax,
                tfd.dbl_supplier_amount,
                tfd.dbl_normal_profit,
                tfd.dbl_customer_price

FROM airticket.tbl_ticket_gen_data tgen
LEFT JOIN service.tbl_supp_doc_no sd
    ON sd.pk_supp_doc_no_id = tgen.fk_supp_doc_no_id
LEFT JOIN service.tbl_supp_doc_fare AS tfd
    ON sd.fk_issue_cus_ledg_fare_id = tfd.pk_supp_doc_fare_id 
WHERE tgen.vhr_original_issue_ticket = '?'

Query - Invoice Posting
----------------------------------
SELECT
    tr.pk_transaction_id,
    tr.dat_transaction,

    tr.fk_ledger_id,
    ac.vhr_account_code,
    ac.vhr_account_name,

    tr.vhr_ledg_currency,
    tr.vhr_base_currency,
    tr.dbl_ledg_cur_debit,
    tr.dbl_ledg_cur_credit,

    tr.dbl_ledg_cur_outstanding,
    tr.dbl_base_cur_outstanding,
    tr.fk_branch_id,
    tr.vhr_reference,
    tr.txt_narration

FROM service.tbl_supp_doc_no AS supp 
LEFT JOIN transaction.tbl_transaction AS tr
    ON tr.fk_supp_doc_no_id = supp.pk_supp_doc_no_id
LEFT JOIN accounts.tbl_account AS ac
     ON tr.fk_ledger_id = ac.pk_account_id
WHERE supp.vhr_supp_doc_no = '?' --ticket/voucher no
AND tr.sin_transaction_status = 1
AND tr.vhr_sys_module_name = 'SALE'

Query Refund Posting
-----------------------
SELECT
    tr.pk_transaction_id,
    tr.dat_transaction,

    tr.fk_ledger_id,
    ac.vhr_account_code,
    ac.vhr_account_name,

    tr.vhr_ledg_currency,
    tr.vhr_base_currency,
    tr.dbl_ledg_cur_debit,
    tr.dbl_ledg_cur_credit,

    tr.dbl_ledg_cur_outstanding,
    tr.dbl_base_cur_outstanding,
    tr.fk_branch_id,
    tr.vhr_reference,
    tr.txt_narration

FROM service.tbl_supp_doc_no AS supp 
LEFT JOIN transaction.tbl_transaction AS tr
    ON tr.fk_supp_doc_no_id = supp.pk_supp_doc_no_id
LEFT JOIN accounts.tbl_account AS ac
     ON tr.fk_ledger_id = ac.pk_account_id
WHERE supp.vhr_supp_doc_no = '?' --ticket/voucher no
AND tr.sin_transaction_status = 1
AND tr.vhr_sys_module_name = 'REFUND'

Query - Log
----------------
SELECT 
    dtm_last_update,
    sin_last_action, --0=>Created, Above 0=> Modified, -1=>Deleted
    fk_last_action_user_id --Join to User table for name
    FROM log.tbl_supp_doc_no_log
WHERE supp.vhr_supp_doc_no = '?'