TAAS

Solutions

GUI Change
- Exclude Items (The changed all sale and refund data included report)
Against Sold(Main)
Against Sale
Against Refund(Main)
Against Refund
Document Reversal(Main)
Document Reversal
Inter Branch Sale(Main)
Inter Branch Sale
Inter Branch Refund(Main)
Inter Branch Refund
1) Sales/Refund SQL
SELECT doc.pk_documents_id,
    mofsm.sin_mod_of_sale_type,
    spd.pk_supp_doc_no_id,
    spd.fk_service_id,
    spd.dat_issue,
    spd.dat_sale,
    spd.sin_alpo,
    spd.arb_fk_region_ids,
    spdf.dbl_published_fare,
    spdf.dbl_fare,
    spdf.dbl_fare_paid_agency_cc,
    spdf.dbl_fare_paid_cust_cc,
    spdf.dbl_fare_paid_pax_cc,
    spdf.dbl_fare_shared_supplier,
    spdf.dbl_fare_shared_account,
    spdf.dbl_queue_tax,
    spdf.dbl_queue_tax_actual_fare,
    spdf.dbl_tax,
    spdf.dbl_tax_paid_agency_cc,
    spdf.dbl_tax_paid_cust_cc,
    spdf.dbl_tax_paid_pax_cc,
    spdf.dbl_tax_shared_supplier,
    spdf.dbl_tax_shared_account,
    spdf.dbl_commission,
    spdf.dbl_commission_agency_cc,
    spdf.dbl_commission_cust_cc,
    spdf.dbl_commission_pax_cc,
    spdf.dbl_commission_shared_supplier,
    spdf.dbl_commission_shared_account,
    spdf.dbl_expected_commission,
    spdf.dbl_expected_commission_agency_cc,
    spdf.dbl_expected_commission_cust_cc,
    spdf.dbl_expected_commission_pax_cc,
    spdf.dbl_expected_commission_shared_supplier,
    spdf.dbl_expected_commission_shared_account,
    spdf.dbl_paid_commission_tax,
    spdf.dbl_tour_commission,
    spdf.dbl_expected_tour_comm,
    spdf.dbl_plb_commission,
    spdf.dbl_expected_plb_comm,
    spdf.dbl_sup_fee,
    spdf.dbl_sup_fee_paid_agency_cc,
    spdf.dbl_sup_fee_paid_cust_cc,
    spdf.dbl_sup_fee_paid_pax_cc,
    spdf.dbl_sup_fee_shared_supplier,
    spdf.dbl_sup_fee_shared_account,
    spdf.dbl_sup_cc_charge,
    spdf.dbl_sup_cc_charge_paid_agency_cc,
    spdf.dbl_sup_cc_charge_paid_cust_cc,
    spdf.dbl_sup_cc_charge_paid_pax_cc,
    spdf.dbl_sup_cc_charge_shared_supplier,
    spdf.dbl_sup_cc_charge_shared_account,
    spdf.dbl_sup_insurance_charge,
    spdf.dbl_sup_insurance_charge_paid_agency_cc,
    spdf.dbl_sup_insurance_charge_paid_cust_cc,
    spdf.dbl_sup_insurance_charge_paid_pax_cc,
    spdf.dbl_sup_insurance_charge_shared_supplier,
    spdf.dbl_sup_insurance_charge_shared_account,
    spdf.dbl_adm_expect,
    spdf.dbl_acm_expect,
    spdf.dbl_adm,
    spdf.dbl_acm,
    spdf.dbl_sup_rfd_charge,
    spdf.dbl_sup_rfd_charge_paid_agency_cc,
    spdf.dbl_sup_rfd_charge_paid_cust_cc,
    spdf.dbl_sup_rfd_charge_paid_pax_cc,
    spdf.dbl_sup_rfd_charge_shared_supplier,
    spdf.dbl_sup_rfd_charge_shared_account,
    spdf.dbl_sup_tax,dbl_sup_tax_paid_agency_cc,
    spdf.dbl_sup_tax_paid_cust_cc,
    spdf.dbl_sup_tax_paid_pax_cc,
    spdf.dbl_sup_tax_shared_supplier,
    spdf.dbl_sup_tax_shared_account,
    spdf.dbl_supplier_amount,
    spdf.dbl_supplier_paid_agency_cc,
    spdf.dbl_supplier_paid_cust_cc,
    spdf.dbl_supplier_paid_pax_cc,
    spdf.dbl_supplier_payable_shared,
    spdf.dbl_expense_tax,
    spdf.dbl_service_fee,
    spdf.dbl_management_fee,
    spdf.dbl_payback,
    spdf.dbl_agent_commission,
    spdf.dbl_discount,
    spdf.dbl_tour_discount,
    spdf.dbl_plb_discount,
    spdf.dbl_cust_cc_charge,
    spdf.dbl_cust_insurance_charge,
    spdf.dbl_agency_rfd_charge,
    spdf.dbl_cust_tax,
    spdf.dbl_rounding_amount,
    spdf.dbl_customer_price,
    spdf.dbl_customer_paid_cust_cc,
    spdf.dbl_customer_paid_pax_cc,
    spdf.dbl_actual_cost,
    spdf.dbl_normal_profit,
    spdf.dbl_alpo_profit,
        spdf.dbl_alpo_additional_profit,
    spdf.dbl_actual_profit,
    spdf.dbl_doc_cc_charge,
    spdf.dbl_doc_discount,
    spdf.dbl_doc_extra_collection
FROM sale.tbl_mod_of_sale mofsm
    LEFT JOIN sale.tbl_sale_master sm
        ON mofsm.fk_sale_master_id = sm.pk_sale_master_id
    LEFT JOIN document.tbl_documents doc
        ON sm.fk_documents_id = doc.pk_documents_id
    LEFT JOIN sale.tbl_mod_of_sale_supp_doc mofsd
        ON mofsm.pk_mod_of_sale_id = mofsd.fk_mod_of_sale_id
    LEFT JOIN service.tbl_supp_doc_no spd
        ON mofsd.fk_supp_doc_no_id = spd.pk_supp_doc_no_id
    LEFT JOIN service.tbl_supp_doc_fare spdf 
        ON mofsd.fk_base_fare_id = spdf.pk_supp_doc_fare_id
WHERE doc.int_sys_action_id != -1
ORDER BY spd.dat_sale

UNION ALL

*REFUND...
*.....
*....
* WHERE sin_sys_refund_side_status = 4 and/or 5
--       4 = Void(From Refund GUI)
--       5 = Refund

- Base1 Currency
LEFT JOIN service.tbl_supp_doc_fare spdf 
        ON mofsd.fk_base_fare_id = spdf.pk_supp_doc_fare_id

- Other Currency Case
LEFT JOIN service.tbl_supp_doc_fare spdf 
        ON mofsd.fk_cus_ledg_fare_id = spdf.pk_supp_doc_fare_id

- Ticket/Voucher Count
distinct pk_supp_doc_no_id count

- Document Count
distinct pk_documents_id

- Amount
dbl_customer_price

2) Components List - based on - 1) Sales/Refund SQL
- 0 Aount Components not show

3) Sales Flow Tab Items
Price
Supplier Credit (-)
Supplier Paid (Customer Card - UCCF) (-)
Supplier Paid(Pax Card - UCCF) (-)
Supplier Paid(Agency Card - UCCF) (-)
Payback Amount (-)
Customer Card (-)
Tax on Commission(-)
Input Tax (+)
Output Tax (-)
Supplier Sharing Difference(-)
Normal Profit
Agent Commission (-)
Expected Commission (+)
Actual Profit
-admin setting base actions:-
*If AGENT_COMMISSION_AMOUNT_POSTING_METHOD_IN_SALE_AND_REFUND != No Posting then Subtract Agt Commsn from Normal Profit
*if EXPECTED_COMMISSION_POSTING_METHOD != No Posting then Add Agent Commsn to Normal Profit

4) Posting
SELECT vhr_code,
    MIN(vhr_name) AS vhr_name,
    MIN(vhr_display_name) AS vhr_display_name,
    SUM(dbl_sale_debit) AS dbl_sale_debit,
    SUM(dbl_sale_credit) AS dbl_sale_credit,
    MIN(dbl_refund_debit) AS dbl_refund_debit,
    MIN(dbl_refund_credit) AS dbl_refund_credit 
FROM (SELECT cofa.vhr_code,
    cofa.vhr_name,
    cofa.vhr_display_name,
    tr.dbl_ledg_cur_debit AS dbl_sale_debit,
    tr.dbl_ledg_cur_credit AS dbl_sale_credit,
    0.00 AS dbl_refund_debit,
    0.00 AS dbl_refund_credit
FROM transaction.tbl_transaction tr
LEFT JOIN accounts.tbl_chart_of_ac cofa
    ON tr.fk_main_ledger_id = cofa.pk_chart_of_ac_id
WHERE tr.vhr_sys_module_name = 'SALE'
    AND tr.fk_supp_doc_no_id = ANY('{1,2,3}'::BIGINT[])

UNION ALL 

SELECT cofa.vhr_code,
    cofa.vhr_name,
    cofa.vhr_display_name,
    0.00 AS dbl_sale_debit,
    0.00 AS dbl_sale_credit,
    tr.dbl_ledg_cur_debit AS dbl_refund_debit,
    tr.dbl_ledg_cur_credit AS dbl_refund_credit
FROM transaction.tbl_transaction tr
LEFT JOIN accounts.tbl_chart_of_ac cofa
    ON tr.fk_main_ledger_id = cofa.pk_chart_of_ac_id
WHERE tr.vhr_sys_module_name = 'REFUND'
    AND tr.fk_supp_doc_no_id = ANY('{1,2,3}'::BIGINT[])) AS TMP

GROUP BY vhr_code

--AND fk_supp_doc_no_id = ANY('{1,2,3}'::BIGINT[]) -> - 1) Sales/Refund SQL items
--Currency based field changed
5) alpo tab case
- Count - sin_alpo_adjustment
- Alpo profit = dbl_alpo_profit + dbl_alpo_additional_profit
- Amount = dbl_customer_price
- Profit = dbl_normal_profit

GUI CHANGES
------------
Set Default currency as Base1
Component tab-> Correct order, Add publish fare, normal profit, actual profit
Posting Tab:- Add Coa like categorygrouping
Sales flow:- Bold Normal profit, Actual profit

TESTING DONE

TESTING DONE

TESTING DONE

ISSUE FOUNDED

1.Allow Act As Client,Supplier Accounts In Auto Completion Field
2.Freeze The Sub Customer Code Field After Showing Data In List
3.Clear Invalid Pax Profile Name On Focus Out