ERPstuff - Oracle EBS Community

HI,

Vat Interim Liability always shows a balance and some of the entries has not got transferred to Vat Liability Account
As the ledger has got high volume of transactions and some entries are in summarised form to manually knock it off

Can some let me know how we can get the extract of such balance entries?

Rgds

Given below are sample queries used for IL reconciliation (Thanks to Vijay who shared this query with me). You may change the query according to your needs.

<u><b>GL Entries:</b></u>

SELECT jel.je_header_id ,
jeh.je_source,
jeh.je_category,
jeh.name,
jeh.description,
jel.period_name ,
jel.effective_date ,
jel.je_line_num ,
fndtl.description Account_Description,
jel.code_combination_id ,
gl.segment1 || '-' || gl.segment2 || '-' || gl.segment3 || '-' || gl.segment4 || '-' || gl.segment5 || '-' || gl.segment6 || '-' || gl.segment7 Account_Name,
jel.entered_dr ,
jel.entered_cr ,
jel.accounted_dr ,
jel.accounted_cr ,
jel.description Lines_Description,
jel.status status
FROM gl_je_lines jel,
gl_je_headers jeh,
gl_code_combinations gl,
fnd_flex_values fnd,
fnd_flex_values_tl fndtl
--, gl_weighted_avg_details wad
WHERE jeh.je_header_id = jel.je_header_id
AND gl.code_combination_id=jel.code_combination_id
AND fnd.flex_value_id = fndtl.flex_value_id
AND fnd.flex_value=gl.segment2
AND upper(fndtl.description) like upper('%VAT INTERIM LIABILITY%')
ORDER BY jeh.je_source desc;

==============================================

<u><b>Payables:</b></u>

SELECT
fndtl.description,
aid.invoice_id,
aid.invoice_distribution_id,
ai.invoice_num,
aid.dist_code_combination_id,
aid.amount,
aid.period_name,
aid.set_of_books_id,
gl.segment1 || '-' || gl.segment2 || '-' || gl.segment3 || '-' || gl.segment4 || '-' || gl.segment5 || '-' || gl.segment6 Account_Name
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid,
gl_code_combinations gl,
fnd_flex_values fnd,
fnd_flex_values_tl fndtl
WHERE ai.invoice_id= aid.invoice_id
AND aid.dist_code_combination_id=gl.code_combination_id
AND aid.period_name= <period name>
AND aid.set_of_books_id = <SOB ID>
AND fnd.flex_value_id = fndtl.flex_value_id
AND fnd.flex_value=gl.segment2
AND upper(fndtl.description) like upper('%vat%');

==============================================

<u><b>Receivables:</b></u>

SELECT fndtl.description,
ral.cust_trx_line_gl_dist_id,
ral.code_combination_id,
ral.amount,
ral.gl_date,
gl.segment1 || '-' || gl.segment2 || '-' || gl.segment3 || '-' || gl.segment4 || '-' || gl.segment5 || '-' || gl.segment6 || '-' || gl.segment7 account_name
FROM ra_cust_trx_line_gl_dist_all ral,
gl_code_combinations gl,
fnd_flex_values fnd,
fnd_flex_values_tl fndtl
WHERE ral.code_combination_id = gl.code_combination_id
AND set_of_books_id = <SOB ID>
AND gl_date Between <from period> AND <to period>
AND fnd.flex_value_id = fndtl.flex_value_id
AND fnd.flex_value = gl.segment2
AND UPPER(fndtl.description) like UPPER('%vat%');

==============================================

Cheers,
Saravanan

Hi,

I tried running the above query at two instances but it did not fetch any results

Rgds
Emanuel

I think you should modify this query according to your COA structure and other requirements. I don't have access to instances with India Localization data, hence i can not validate this query.

Cheers,
Saravanan