Project

General

Profile

Edit Copy Actions

Feature #215

open

Document Query

Added by Junaid M over 1 year ago. Updated 8 days ago.

Status:
Reopen Bugs Coding Done
Priority:
Normal
Assignee:
Target version:
Start date:
06/27/2023
Due date:
% Done:

70%

Estimated time:
Owner(Agency):
Travvise
Time Taken(HH):
Module:
Document Query
Tested By:
unnikannan S
Code Reviewed By:

Description

Coding Document Query


Files


Add

Subtasks


Add

Related issues

Updated by Anil KV 8 months ago

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 

Updated by Anil KV 8 months ago

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

Updated by Anil KV 8 months ago

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

Updated by Anil KV 8 months ago

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

Updated by Anil KV 8 months ago

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

Updated by Anil KV 8 months ago

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

Updated by Anil KV 8 months ago

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

Updated by Anil KV 8 months ago

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

Actions #9

Updated by Anil KV 8 months ago

  • Status changed from New to Ready for Coding
  • % Done changed from 0 to 20
Actions #10

Updated by Anil KV 8 months ago

  • Assignee changed from travvise Admin to Bertin Joseph
Actions #11

Updated by Bertin Joseph 7 months ago

  • Status changed from Ready for Coding to Coding Started
  • % Done changed from 20 to 30
Actions #12

Updated by Bertin Joseph 7 months ago

  • Status changed from Coding Started to Coding Done
  • % Done changed from 30 to 60
Actions #13

Updated by unnikannan S 7 months ago

  • Status changed from Coding Done to Testing Started

Updated by unnikannan S 7 months ago

  • Status changed from Testing Started to Reopen Bugs
  • % Done changed from 60 to 50
  • Tested By set to unnikannan S

ISSUE FOUNDED

1.Fixed - Add Red Star On Doc No
2.Show Deleted Doc Data Highlight As Red
3.Fix Bugs On Tool Bars In Grid
4.Fixed - Currency Code Allign As Side
5.Fixed - Clear Default Columns From Modified By And Cost Centre

Updated by Arathy PS 7 months ago

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:

Actions #16

Updated by Anonymous 6 months ago

  • Assignee changed from Bertin Joseph to Sreeranjini T
Actions #17

Updated by Sreeranjini T 5 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by unnikannan S 5 months ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50

ISSUE FOUNDED

1.Add Expand/Collapse Button In (Settlement Grid)
2.Correct Currency Code Allign On Log Tab

Actions #19

Updated by Sreeranjini T 5 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Theja Ponon 5 months ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50

2. FIXED - when loading a document supported documents shows the same ticket/voucher repeatedly
steps:1.load the sale invoice in document query
steps:2.refund the ticket and load the sale invoice again in document query and check the supported documents

3. Error When enter a invalid document number.
4.FIXED - Place the reset button above new.

Actions #21

Updated by Sreeranjini T 5 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70
Actions #22

Updated by Theja Ponon 4 months ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs Testing Done
  • % Done changed from 70 to 90

Updated by Theja Ponon 4 months ago

  • Status changed from Reopen Bugs Testing Done to Reopen Bugs
  • % Done changed from 90 to 50

ISSUE FOUND
----------
1)DOCUMENT MODIFIED DATE IS NOT UPDATING CORRECTLY ON ACTION DATE IN LOG TAB

Actions #24

Updated by Sreeranjini T 4 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS 4 months ago

ISSUE FOUND
--------------------------------------------
1. Wrong currency data in the log.
2. No need to show document itself in the sub document field.

3. In the log tab Roe value not changing when used another currency in the document.

Actions #26

Updated by Sreeranjini T 4 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70
Actions #27

Updated by Sreeranjini T 4 months ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50
Actions #28

Updated by Sreeranjini T 4 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Theja Ponon 4 months ago

ISSUE FOUND
------------------
1)ERROR ON UPDATING THE NOTE

Actions #30

Updated by Sreeranjini T 3 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70
Actions #31

Updated by Theja Ponon 3 months ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs Testing Done
  • % Done changed from 70 to 90

Updated by Arathy PS 3 months ago

  • Status changed from Reopen Bugs Testing Done to Reopen Bugs
  • % Done changed from 90 to 50

ISSUE FIXED
------------------------------------------------
1. fixed - Not showing document status while the document status is changed to 'not active (need approval)
2. fixed - Document approved by fields not disappearing when document status changed to another.
3. Sub customer is not showing.

Actions #33

Updated by Sreeranjini T 3 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS 2 months ago

  • Status changed from Reopen Bugs Coding Done to New Changes
  • % Done changed from 70 to 50

Changes
----------------------------------------------
1. Add redirection to the document and voucher numbers.

Actions #35

Updated by Sreeranjini T 2 months ago

  • Status changed from New Changes to New Changes Coding Done
  • % Done changed from 50 to 70

Updated by Theja Ponon 2 months ago

  • Status changed from New Changes Coding Done to Reopen Bugs
  • % Done changed from 70 to 50

ISSUE FOUND
------------
1)Ticket/vouchers are not redirecting
2)document number on redirection not loading in corresponding modules

Actions #37

Updated by Sreeranjini T 2 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS 2 months ago

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

Actions #39

Updated by Arathy PS 2 months ago

  • Status changed from Reopen Bugs Coding Done to New Changes
  • % Done changed from 70 to 50
Actions #40

Updated by Sreeranjini T 2 months ago

  • Status changed from New Changes to New Changes Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS about 2 months ago

  • Status changed from New Changes Coding Done to New Changes
  • % Done changed from 70 to 50

NEW CHANGES DONE
-------------------------
1.In the related documents tab show the cheques related documents here when we are loading a document which have a cheque in it.

Actions #42

Updated by Sreeranjini T about 2 months ago

  • Status changed from New Changes to New Changes Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS about 2 months ago

ISSUE FOUND
-----------------------
1. Wrong data in the RoE column-- need to fix in receipt and payment

Actions #44

Updated by Sreeranjini T about 2 months ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS about 2 months ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50

ISSUE FIXED
---------------------
1. No field to show outstanding amount of the document after settlement.
2. Not showing linked documents in the posting tab.

Actions #46

Updated by Sreeranjini T about 1 month ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Theja Ponon about 1 month ago

ISSUE FIXED
-------------
1)FIXED - Issue in print of attachment tab
2) - Attachment added is not showing
3)FIXED - Without entering any document number noteis shoing

4)FIXED - Enquiry No is not showing

Actions #48

Updated by Theja Ponon about 1 month ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50
Actions #49

Updated by Sreeranjini T about 1 month ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70
Actions #50

Updated by Sreeranjini T about 1 month ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50
Actions #51

Updated by Sreeranjini T about 1 month ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS 27 days ago

ISSUE FIXED
--------------------------------
1. While searching received cheque deposit document no:

Actions #53

Updated by Sreeranjini T 27 days ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70
Actions #54

Updated by Theja Ponon 20 days ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50

Updated by Arathy PS 19 days ago

Issue fixed
-----------
1)show documents which are submitted through document submission in supportive document tab on searching document submission doc no
2)Notes not related to the documents are showing in the note tab.

Actions #56

Updated by Sreeranjini T 19 days ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70

Updated by Arathy PS 11 days ago

  • Status changed from Reopen Bugs Coding Done to Reopen Bugs
  • % Done changed from 70 to 50

ISSUE FIXED
----------------------------
1. Linked documents in quotation ae not showing while searching.

Actions #58

Updated by Sreeranjini T 8 days ago

  • Status changed from Reopen Bugs to Reopen Bugs Coding Done
  • % Done changed from 50 to 70
Edit Copy Actions

Also available in: Atom PDF