Sample Goods Receive Note + Auto Supplier Invoice

This document can be create under

Procurement → Transactions → Direct Receiving Stock

OR

Trading → Purchase → Direct Receiving Stock

Giving example , the GRN and SINV number is GRN1001 + SINV1003

select * from supp_grn_index where pkid = 1001;

select * from supp_grn_item where grn_id = 1001;

To trace movement in / out of the item (Stock Movement)

select * from inv_stock_delta where doc_table = 'supp_grn_item' and doc_key in (select pkid from supp_grn_item where grn_id = 1001);

To trace movement in / out of the serial number or batch and expiry (Trace Stock)

select * from inv_serial_number_delta where doc_table = 'supp_grn_item' and doc_key in (select pkid from supp_grn_item where grn_id = 1001);

Record in Financial Report (Balance Sheet / Trial Balance / P&L / GL Listing

select * from acc_journal_transaction where doc_ref = 'supp_grn_index' and doc_key = 1001;

select * from acc_journal_entry where journaltxnid in (select pkid from acc_journal_transaction where doc_ref = 'supp_grn_index' and doc_key = 1001);

If you know the SINV number , you can just select base on SINV number

select * from supp_invoice_index where pkid = 1003;

Or if you doesn't know the SINV number , can select base on GRN number .

select * from supp_invoice_index where grnid = 1001;

Then , proceed to check other tables .

select * from supp_invoice_item where index_id = 1003;

Record in AR / AP Transaction / Billing Statement / Historical AR / AP Balance

select * from acc_nominal_account_txn where foreign_table = 'supp_invoice_index' and foreign_key = 1003;

Settlement /  Supplier Historical Transaction Aging Analysis

select * from acc_doclink where tgt_docref = 'supp_invoice_index' and tgt_docid = 1003 ;

If the SINV has been filed , you may need to check these tables .

Record in Financial Report (Balance Sheet / Trial Balance / P&L / GL Listing

select * from acc_journal_transaction where doc_ref = 'supp_invoice_index' and doc_key = 1003 and description ilike '%Auto Created From Tax Filing%' ;

select * from acc_journal_entry where journaltxnid in (select pkid from acc_journal_transaction where doc_ref = 'supp_invoice_index' and doc_key = 1003 and description ilike '%Auto Created From Tax Filing%' );

**Some customer disable auto created SINV from GRN , on this case GRN will have temporary GL Code for accPayable (In Transit) and actual GL Code is in SINV journal .

select * from acc_journal_transaction where doc_ref = 'supp_invoice_index' and doc_key = 1003 ;

select * from acc_journal_entry where journaltxnid in (select pkid from acc_journal_transaction where doc_ref = 'supp_invoice_index' and doc_key = 1003);

GST Report / Filing

select * from acc_tax_transaction where doc_index_ref = 'supp_invoice_index' and doc_index_key = 1003;