TAAS

Summary Query

                                              SELECT SUM(mt.int_service_count) AS int_service_count,
                                              MAX(mt.pk_supp_doc_no_id) AS pk_supp_doc_no_id,
                                              mt.fk_service_id AS fk_service_id,
                                              svc.vhr_service_code,
                                              svc.vhr_service_name,
                                              svc.sin_sales_mapping,
                                              SUM(mt.dbl_supplier_payable) AS dbl_supplier_payable,
                                              SUM(mt.dbl_price) AS dbl_price,
                                              SUM(mt.dbl_profit) AS dbl_profit
                                                  FROM (SELECT COUNT(pk_supp_doc_no_id) AS int_service_count,
                                                           MAX(pk_supp_doc_no_id) AS pk_supp_doc_no_id,
                                                           fk_service_id,
                                                           SUM(COALESCE(tktfare.dbl_supplier_amount, 0.00)) AS dbl_supplier_payable,
                                                           SUM(COALESCE(tktfare.dbl_customer_price, 0.00)) AS dbl_price,
                                                           SUM(COALESCE(tktfare.dbl_actual_profit, 0.00)) AS dbl_profit
                                                        FROM service.tbl_supp_doc_no AS supp
                                                        LEFT JOIN airticket.tbl_ticket AS tkt
                                                            ON supp.pk_supp_doc_no_id = tkt.fk_supp_doc_no_id AND tkt.sin_record_type = 1
                                                        LEFT JOIN airticket.tbl_ticket_gen_data AS tktgen
                                                            ON supp.pk_supp_doc_no_id = tktgen.fk_supp_doc_no_id 
                                                        LEFT JOIN service.tbl_supp_doc_fare AS tktfare
                                                            ON tktfare.pk_supp_doc_fare_id = tkt.fk_ticket_base_fare_id
                                                        WHERE tkt.int_sys_action_id != -1
                                                        AND supp.sin_supp_doc_category = 1
                                                        AND supp.sin_sys_sale_side_status IN (0, 1)
                                                        -- WHERE CONDITIONS HERE
                                                        GROUP BY fk_service_id

                                                        UNION ALL 

                                                        SELECT COUNT(pk_supp_doc_no_id) AS int_service_count,
                                                            MAX(pk_supp_doc_no_id) AS pk_supp_doc_no_id,
                                                            fk_service_id,
                                                            SUM(COALESCE(hotfare.dbl_supplier_amount)) AS dbl_supplier_payable,
                                                            SUM(COALESCE(hotfare.dbl_customer_price)) as dbl_price,
                                                            SUM(COALESCE(hotfare.dbl_actual_profit)) as dbl_profit
                                                        FROM service.tbl_supp_doc_no AS supp
                                                        LEFT JOIN hotel.tbl_hotel AS hotel
                                                            ON supp.pk_supp_doc_no_id = hotel.fk_supp_doc_no_id AND hotel.sin_record_type = 1
                                                        LEFT JOIN hotel.tbl_hotel_gen_data AS hotgen
                                                            ON supp.pk_supp_doc_no_id = hotgen.fk_supp_doc_no_id 
                                                        LEFT JOIN service.tbl_supp_doc_fare AS hotfare
                                                            ON hotfare.pk_supp_doc_fare_id = hotel.fk_hotel_base_fare_id
                                                        LEFT JOIN hotel.tbl_hotel_master AS hm
                                                            ON hm.pk_hotel_master_id = hotgen.fk_hotel_master_id 
                                                        --WHERE %s
                                                        GROUP BY fk_service_id

                                                        UNION ALL

                                                        SELECT COUNT(pk_supp_doc_no_id) as int_service_count,
                                                                MAX(pk_supp_doc_no_id) as pk_supp_doc_no_id,
                                                                fk_service_id,
                                                                SUM(COALESCE(otherfare.dbl_supplier_amount, 0.00)) as dbl_supplier_payable,
                                                                SUM(COALESCE(otherfare.dbl_customer_price, 0.00)) as dbl_price,
                                                                SUM(COALESCE(otherfare.dbl_actual_profit, 0.00)) as dbl_profit
                                                        FROM service.tbl_supp_doc_no AS supp
                                                        LEFT JOIN otherservice.tbl_other_service AS other
                                                            ON supp.pk_supp_doc_no_id = other.fk_supp_doc_no_id AND other.sin_record_type = 1
                                                        LEFT JOIN otherservice.tbl_other_service_gen_data AS othgen
                                                            ON supp.pk_supp_doc_no_id = othgen.fk_supp_doc_no_id  
                                                        LEFT JOIN service.tbl_supp_doc_fare AS otherfare
                                                            ON otherfare.pk_supp_doc_fare_id = other.fk_other_service_base_fare_id
                                                        --WHERE %s
                                                        GROUP BY (fk_service_id)

                                                        UNION ALL 

                                                        SELECT COUNT(pk_supp_doc_no_id) AS int_service_count,
                                                            MAX(pk_supp_doc_no_id) AS pk_supp_doc_no_id,
                                                            fk_service_id,
                                                            SUM(COALESCE(visafare.dbl_supplier_amount, 0.00)) AS dbl_supplier_payable,
                                                            SUM(COALESCE(visafare.dbl_customer_price, 0.00)) AS dbl_price,
                                                            SUM(COALESCE(visafare.dbl_actual_profit, 0.00)) AS dbl_profit
                                                        FROM service.tbl_supp_doc_no AS supp
                                                        LEFT JOIN visa.tbl_visa AS visa
                                                            ON supp.pk_supp_doc_no_id = visa.fk_supp_doc_no_id AND visa.sin_record_type = 1
                                                        LEFT JOIN visa.tbl_visa_gen_data AS visagen
                                                            ON supp.pk_supp_doc_no_id = visagen.fk_supp_doc_no_id 
                                                        LEFT JOIN service.tbl_supp_doc_fare AS visafare
                                                            ON visafare.pk_supp_doc_fare_id = visa.fk_visa_sup_ledg_fare_id
                                                        --WHERE %s
                                                        GROUP BY fk_service_id

                                                         UNION ALL 

                                                        SELECT COUNT(pk_supp_doc_no_id) AS int_service_count,
                                                            MAX(pk_supp_doc_no_id) AS pk_supp_doc_no_id,
                                                            fk_service_id,
                                                            SUM(COALESCE(transferfare.dbl_supplier_amount, 0.00)) AS dbl_supplier_payable,
                                                            SUM(COALESCE(transferfare.dbl_customer_price, 0.00)) AS dbl_price,
                                                            SUM(COALESCE(transferfare.dbl_actual_profit, 0.00)) AS dbl_profit
                                                        FROM service.tbl_supp_doc_no AS supp
                                                        LEFT JOIN transfer.tbl_transfer AS transfer
                                                            ON supp.pk_supp_doc_no_id = transfer.fk_supp_doc_no_id AND transfer.sin_record_type = 1
                                                        LEFT JOIN transfer.tbl_transfer_gen_data AS transfergen
                                                            ON supp.pk_supp_doc_no_id = transfergen.fk_supp_doc_no_id 
                                                        LEFT JOIN service.tbl_supp_doc_fare AS transferfare
                                                            ON transferfare.pk_supp_doc_fare_id = transfer.fk_transfer_sup_ledg_fare_id
                                                        --WHERE %s
                                                        GROUP BY fk_service_id

                                                        ) AS mt
                                                        LEFT JOIN service.tbl_service AS svc
                                                        ON svc.pk_service_id = mt.fk_service_id
                                                        GROUP BY fk_service_id, svc.vhr_service_code, svc.vhr_service_name, svc.sin_sales_mapping;

------------sin_sales_mapping-----------
--      1 = Other Service Tab
--      2 = Ticket Service Tab
--      3 = Hotel Service Tab
--      4 = Tranfer Service Tab
--      5 = Visa Service Tab                                          

TICKET TAB DATA QUERY

SELECT   supp.pk_supp_doc_no_id,
                                                 tkt.pk_ticket_id,
                                                 supp.dat_issue,
                                                 supp.vhr_supp_doc_no,
                                                 tkt.vhr_pax_name,
                                                 tkt.vhr_sector,
                                                 tkt.vhr_class_chr,
                                                 tktgen.fk_ticketing_counter_staff_id,
                                                 cs_t.vhr_counter_staff_code,
                                                 cs_t.vhr_counter_staff_name,
                                                 tktgen.fk_booking_counter_staff_id,
                                                 cs_b.vhr_counter_staff_code,
                                                 cs_b.vhr_counter_staff_name,
                                                 tktfare.vhr_currency,
                                                 tktfare.dbl_fare,
                                                 tktfare.dbl_tax,
                                                 tktfare.dbl_supplier_amount,
                                                 tktfare.dbl_normal_profit,
                                                 tktfare.dbl_customer_price
                                            FROM  airticket.tbl_ticket AS tkt 
                                            LEFT JOIN service.tbl_supp_doc_no AS supp
                                                ON supp.pk_supp_doc_no_id = tkt.fk_supp_doc_no_id AND tkt.sin_record_type = 1 AND supp.sin_last_action != -1
                                            LEFT JOIN airticket.tbl_ticket_gen_data AS tktgen 
                                                ON supp.pk_supp_doc_no_id = tktgen.fk_supp_doc_no_id
                                            LEFT JOIN service.tbl_supp_doc_fare AS tktfare 
                                                ON tktfare.pk_supp_doc_fare_id = tkt.fk_ticket_base_fare_id-- %s--dynamic column based on currency
                                            LEFT JOIN airticket.tbl_airline_master AS am 
                                                ON am.pk_airline_master_id = tktgen.fk_airline_master_id  
                                            LEFT JOIN counterstaff.tbl_counter_staff AS cs_t 
                                                ON cs_t.pk_counter_staff_id = tktgen.fk_ticketing_counter_staff_id
                                            LEFT JOIN counterstaff.tbl_counter_staff AS cs_b
                                                ON cs_b.pk_counter_staff_id = tktgen.fk_booking_counter_staff_id
                                            WHERE tkt.int_sys_action_id != -1--%s --WHERE CONDITION TICKET 
                                            ORDER BY dat_issue, vhr_supp_doc_no 

Hotel Tab Query

SELECT  supp.pk_supp_doc_no_id,
hotel.pk_hotel_id,
supp.dat_issue,
supp.vhr_supp_doc_no,
hm.pk_hotel_master_id,
hm.vhr_hotel_code,
hm.vhr_hotel_name,
hm.vhr_display_name,
hotel.vhr_supplier_confirm_no,
hotel.vhr_pax_name,
hotel.fk_room_type_id,
mp.vhr_meals_code,
mp.vhr_meals_plan,
hotel.fk_meals_plan_id,
rt.vhr_room_type_code,
rt.vhr_room_type,
hotel.sin_no_of_rooms,
hotel.sin_no_of_nights,
hotel.dat_check_in,
hotel.tim_check_in,
hotel.dat_check_out,
hotel.tim_check_out,
hotfare.vhr_currency,
hotfare.dbl_fare,
hotfare.dbl_tax,
hotfare.dbl_supplier_amount,
hotfare.dbl_normal_profit,
hotfare.dbl_customer_price
FROM  hotel.tbl_hotel AS hotel 
LEFT JOIN  service.tbl_supp_doc_no AS supp  
    ON supp.pk_supp_doc_no_id = hotel.fk_supp_doc_no_id AND hotel.sin_record_type = 1
LEFT JOIN hotel.tbl_hotel_gen_data AS hotgen 
    ON supp.pk_supp_doc_no_id = hotgen.fk_supp_doc_no_id
LEFT JOIN service.tbl_supp_doc_fare AS hotfare 
    ON hotfare.pk_supp_doc_fare_id = hotel.fk_hotel_base_fare_id
LEFT JOIN hotel.tbl_hotel_master AS hm
    ON hm.pk_hotel_master_id = hotgen.fk_hotel_master_id 
LEFT JOIN hotel.tbl_meals_plan AS mp
    ON mp.pk_meals_plan_id = hotel.fk_meals_plan_id
LEFT JOIN hotel.tbl_room_type AS rt 
    ON rt.pk_room_type_id = hotel.fk_room_type_id
--WHERE %s 
ORDER BY dat_issue, vhr_supp_doc_no

To Do ***
Change query to union all

unnikannan S wrote in #note-15:

ISSUE FOUNDED

1.ERROR WHILE ADDING AIRLINE NAME
2.REMOVE CODE AND COLUMN FROM CITY AND COUNTRY
3.REMOVE COUNTER STAFF FROM REFERENCE EMPLOYEE AND ADD EMPLOYEE AS REFERENCE EMPLOYEE
4.NOT SHOWING CONSIDER VOUCHER STOCK
5.DATA SHOWING WITHOUT ADDING MANDATORY FIELD (SERVICE)
6.DATA SHOWING WHILE CLEARING PEROD
7.ERROR WHILE ADDING PAX NAME~
8.ERROR WHILE ADDING ENQUIRY NO

unnikannan S wrote in #note-16:

unnikannan S wrote in #note-15:

ISSUE FOUNDED

1.ERROR WHILE ADDING AIRLINE NAME
2.REMOVE CODE AND COLUMN FROM CITY AND COUNTRY
3.REMOVE COUNTER STAFF FROM REFERENCE EMPLOYEE AND ADD EMPLOYEE AS REFERENCE EMPLOYEE
4.NOT SHOWING CONSIDER VOUCHER STOCK
5.DATA SHOWING WITHOUT ADDING MANDATORY FIELD (SERVICE)
6.DATA SHOWING WHILE CLEARING PEROD
7.ERROR WHILE ADDING PAX NAME~
8.ERROR WHILE ADDING ENQUIRY NO
9.ERROR WHILE ADDING BOOKING STAFF

TESTING DONE

TESTING DONE

TESTING DONE

TESTING DONE

Summary tab-Amounts are shown in base currency
Details Tab-Amounts are shown in customer currency

TESTING DONE