TAAS

GUI Changes:-
-> NO data then hide tabs

-> Tab Order
General Tab
Sub Documents
Supporting Documents
Posting
Settlement 
Attachment -> Ref Account Query
Notes[New tab -> Table Columns ref Document Note]
Related Documents
Log 

General Tab:-

-Label Table
Document                                File No
Doc Date                                Enquiry No
Branch                                  Department
Cash Counter                            Cost Center
Posting Status                          Posting Approved By[With Date and Time]
Document Status                         Document Approved By[With Date and Time]
Party                                   Sub Customer
Amount[With Currency Code]              Reference
Narration  [Span]                                               
Created By[With Date and Time]          Modified By[With Date and Time and No of modified]
Delete Reason                           Document Action[Added/Edited/Deleted]
Responsible User/Employee               Document Locked

Sub Documents:-

-> Table Columns
# | Doc No | Doc Date | Currency | Amount | ROE | Amouni In [Base1]
->SQL:-
SELECT
 ...
FROM document.tbl_documents
WHERE vhr_main_document_no = [Input Document]
ORDER BY date

Supporting Documents:-

-> Table Columns
# | Ticket/Voucher | Issue Date | Service | Pax | Currency | Cost| Price| Profit
->SQL:-
SELECT
 ...
FROM  FROM airticket.tbl_ticket tkt
    LEFT JOIN service.tbl_supp_doc_no supp
       ON tkt.fk_supp_doc_no_id = supp.pk_supp_doc_no_id
    LEFT JOIN service.tbl_supp_doc_fare suppfare
       ON tkt.fk_ticket_cus_ledg_fare_id = suppfare.pk_supp_doc_fare_id
WHERE supp.vhr_invoice_doc_no = [Input Document] OR supp.vhr_refund_doc_no = [Input Document]

UNION ALL

SELECT
 ...
FROM  FROM hotel.tbl_hotel hotel
    LEFT JOIN service.tbl_supp_doc_no supp
       ON hotel.fk_supp_doc_no_id = supp.pk_supp_doc_no_id
    LEFT JOIN service.tbl_supp_doc_fare suppfare
       ON hotel.fk_hotel_cus_ledg_fare_id = suppfare.pk_supp_doc_fare_id
WHERE supp.vhr_invoice_doc_no = [Input Document] OR supp.vhr_refund_doc_no = [Input Document]

UNION ALL
....

ORDER BY date

Posting:-

-> Table Columns
*Check View Posting Details
->SQL:-
SELECT 
  mt.fk_main_ledger_id, 
  mt.vhr_ledg_currency, 
  mt.fk_documents_id, 
  mt.fk_ledger_id, 
  mt.pk_transaction_id, 
  mt.dat_transaction, 
  mt.fk_sub_ledger_id, 
  mt.dbl_ledg_cur_debit, 
  mt.dbl_ledg_cur_credit, 
  mt.vhr_base_currency, 
  mt.dbl_base_cur_debit, 
  mt.dbl_base_cur_credit, 
  mt.vhr_cons_currency, 
  mt.dbl_cons_cur_debit, 
  mt.dbl_cons_cur_credit, 
  mt.vhr_reference, 
  mt.txt_narration, 
  mt.txt_build_narration, 
  doc.vhr_document_no, 
  mt.fk_branch_id, 
  mt.fk_department_id, 
  mt.sin_transaction_status, 
  mt.sin_group_id, 
  supp.vhr_supp_doc_no, 
  ac.vhr_account_code, 
  ac.vhr_account_name, 
  br.vhr_branch_code, 
  br.vhr_branch_name, 
  dp.vhr_department_code, 
  dp.vhr_department_name, 
  ca.vhr_code, 
  ca.vhr_name, 
  '' AS vhr_linked_docs 
FROM 
  transaction.tbl_transaction AS mt 
  LEFT JOIN document.tbl_documents AS doc ON mt.fk_documents_id = doc.pk_documents_id 
  LEFT JOIN accounts.tbl_account AS ac ON mt.fk_ledger_id = ac.pk_account_id 
  LEFT JOIN accounts.tbl_chart_of_ac AS ca ON mt.fk_main_ledger_id = ca.pk_chart_of_ac_id 
  LEFT JOIN service.tbl_supp_doc_no AS supp ON supp.pk_supp_doc_no_id = mt.fk_supp_doc_no_id 
  LEFT JOIN organization.tbl_branch AS br ON mt.fk_branch_id = br.pk_branch_id 
  LEFT JOIN organization.tbl_department AS dp ON mt.fk_department_id = dp.pk_department_id 
WHERE 
  doc.int_sys_action_id != -1 
  --AND mt.sin_posting_status = 1  ???
  --AND mt.sin_document_status = 1 ???
  AND mt.sin_transaction_status = 1 
  AND UPPER(doc.vhr_document_no) = UPPER(CAST('?' AS VARCHAR)) 
ORDER BY 
  mt.dat_transaction ASC

Settlement:-

-> Account Wise Grouping
-> Table Columns
Document No | Date | Currency | Debit | Credit | Matched Amount | Outstanding | Reference | Narration
->SQL:-

SELECT 
    doc.vhr_document_no,
    doc.dat_document,
    tr.vhr_ledg_currency,
    tr.dbl_ledg_cur_debit,
    tr.dbl_ledg_cur_credit,
    st.dbl_ledg_cur_matched,
    tr.dbl_ledg_cur_outstanding,
    tr.vhr_reference,
    tr.txt_narration,
    tr.fk_ledger_id,
    ac.vhr_account_code,
    ac.vhr_account_name,
    'Dr' AS vhr_type
FROM settlement.tbl_settlement AS st
LEFT JOIN transaction.tbl_transaction AS tr
    ON st.fk_transaction_dr_id = tr.pk_transaction_id
LEFT JOIN document.tbl_documents AS doc
    ON doc.pk_documents_id = st.fk_documents_dr_id
LEFT JOIN accounts.tbl_account AS ac
    ON tr.fk_ledger_id = ac.pk_account_id
WHERE fk_documents_dr_id = (
     SELECT pk_documents_id FROM document.tbl_documents WHERE int_sys_action_id != -1 AND vhr_document_no = '?' -- Doc No Input
)

UNION ALL

SELECT 
    doc.vhr_document_no,
    doc.dat_document,
    tr.vhr_ledg_currency,
    tr.dbl_ledg_cur_debit,
    tr.dbl_ledg_cur_credit,
    st.dbl_ledg_cur_matched,
    tr.dbl_ledg_cur_outstanding,
    tr.vhr_reference,
    tr.txt_narration,
    tr.fk_ledger_id,
    ac.vhr_account_code,
    ac.vhr_account_name,
    'Cr' AS vhr_type
FROM settlement.tbl_settlement AS st
LEFT JOIN transaction.tbl_transaction AS tr
    ON st.fk_transaction_cr_id = tr.pk_transaction_id
LEFT JOIN document.tbl_documents AS doc
    ON doc.pk_documents_id = st.fk_documents_cr_id
LEFT JOIN accounts.tbl_account AS ac
    ON tr.fk_ledger_id = ac.pk_account_id
WHERE fk_documents_cr_id = (
     SELECT pk_documents_id FROM document.tbl_documents WHERE int_sys_action_id != -1 AND vhr_document_no = '?' -- Doc No Input
)

Settlement->Settled Tab sql - Discuss Junide

Related Documents:-

-> Table Columns
# | Doc No | Doc Date | Currency | Amount | ROE | Amount In [Base1] | Created By | Reference | Narration
->SQL:-
SELECT
 doc2...
trf...
FROM transaction.tbl_transaction_references trf
    INNER JOIN transaction.tbl_transaction tr
        ON 
    INNER JOIN document.tbl_documents doc1
        ON tr.fk_documents_id = doc1.pk_documents_id
    INNER JOIN document.tbl_documents doc2
        ON trf.fk_record_ref_doc_id = doc2.pk_documents_id
WHERE doc1.vhr_main_document_no = [Input Document]

UNION

SELECT
 doc2...
trf...
FROM tbl_rec_cheque_details trf
    INNER JOIN transaction.tbl_transaction tr
        ON 
    INNER JOIN document.tbl_documents doc1
        ON tr.fk_documents_id = doc1.pk_documents_id
    INNER JOIN document.tbl_documents doc2
        ON trf.fk_record_ref_doc_id = doc2.pk_documents_id
WHERE doc1.vhr_main_document_no = [Input Document]

UNION

SELECT
 doc2...
trf...
FROM tbl_paid_cheque_details trf
    INNER JOIN transaction.tbl_transaction tr
        ON 
    INNER JOIN document.tbl_documents doc1
        ON tr.fk_documents_id = doc1.pk_documents_id
    INNER JOIN document.tbl_documents doc2
        ON trf.fk_record_ref_doc_id = doc2.pk_documents_id
WHERE doc1.vhr_main_document_no = [Input Document]

ORDER BY date

Log :-

-> Table Columns
# | Date | Party | Currency | Amount | ROE | Amount In [Base1] | Action Date| Action | User 
->SQL:-
SELECT
 ...
FROM tbl_documents_log
WHERE fk_documents_id = [Input]

ORDER BY pk_documents_log_id

ISSUE FOUND
--------------
1)FIXED - Posting status approved by and document status approved data not showing(ID not receiving )
2)FIXED - Deleted attachment is also showing in the table
3)FIXED - Responsible user/employee data not showing
4)FIXED - Document locked status not changing after unlocking
5)FIXED - In print of attachment tab 'type' is not showing
6)FIXED - Related documents print change ticket/voucher number to doc no:

New Changes
----------------------------
1. In posting tab add an additional column 'outstanding' after credit/debit column
2. In posting tab make linked column before reference.
3. Sub doc no: should show only when the document is sale/refund.
4. In settlement tab show them in account wise grouping.
5. Tab order last three in note, attachment,log