Finance | Project Operations, Human Resources, ...

SQL query to fetch Taxcode based on Voucher

(0) Share

Report

Posted on by PK69

177

Hi there, am trying to develop an SQL query to fetch TaxCode (TaxTrans.Taxcode) based on voucher (SubLedgerVouchergeneralJournalEntry.Voucher) as per my requirement. I cant able to find the relation between these 2 tables. Initially i tried joining with voucher but then the query fetching duplicate values which is not as expected. Pls suggest the needful to fetch Taxcode based on voucher. If there are multiple Taxcodes attched to an voucher, we need to fetch all the taxcodes accordingly. Thanks in advance.

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Prem,

TaxTrans has a relation defined to SubLedgerVoucherGeneralJournalEntry. You can use this in the SQL query.

Was this reply helpful? Yes No
PK69 177 on at

Like (0)

Report

Hi Gunjan, I had already tried adding that relation in the Query but it was returning duplicate values as well. In TaxTrans table Recid is the primary index so I was looking to call taxtrans based on recid relation but I couldn't able to do so

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Could you please share the query you tried? And a few examples of the duplicate values you are getting.

Was this reply helpful? Yes No
PK69 177 on at

Like (0)

Report

Here am attaching the screenshots of the Query executions with results

Case 1 : when i execute the SQL query (Lines 1,2,3,4) i can see total 131 rows

case 2 : when i execute Line 3 only the SUBLEDGERVOUCHERGENERALJOURNALENTRY table, i can see only 116 records.

So ultimately my requirement is if any Sales Tax code was attached to voucher then i need to fetch that Tax Code Value. So case1 should also fetch 116 but in return its fetching 131.

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

The query looks right. If it returns 131 records, not all the records are getting duplicated. Have you tried to identify the voucher numbers of the duplicated records and checked the origin of the transactions?

Was this reply helpful? Yes No
PK69 177 on at

Like (0)

Report

Hi Gunjan, I think its not about the duplicate records, I think its about the relation we are calling the TaxTrans. I am looking for a relation to call TaxTrans based on TaxTrans.RecId. Pls Suggest accordingly

Was this reply helpful? Yes No