• From: India
  • Registered: 08/18/2017
  • Posts: 6

Topic: Mapping Item Code with 0_gl_trans table

Hi,

In a custom report, I have requirement of mapping item_code with transactions happened in 0_gl_trans table.
Firstly I found if I could map 0_gl_trans and 0_stock_moves with type & type_no but it not working when there are multiple items under one type_no.

I would be very thankful if somebody could help me with the query which add one column item with 0_gl_trans where transaction is related to the item.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Mapping Item Code with 0_gl_trans table

gl_trans.type_no is retail (1) or wholesale (2) taken from sales_types table.

gl_trans.type = stock_moves.type which is the Transaction Type.

stock_moves.trans_no is the actual transaction number that does not have an equivalent directly in the gl_trans table.


The gl_trans table manages all types of transactions.

The supp_trans and debtor_trans tables handle purchase and sale transactions respectively and have trans_no and type fields in them.

The supp_invoice_items and debtor_trans_details tables have the item wise records for linking with the stock_moves table on both trans_no and type fields.

apmuthu's Website