Home » Applications » Oracle Fusion Apps & E-Business Suite » Doubt in AR/XLA Query (R12)
Doubt in AR/XLA Query [message #528172] Sat, 22 October 2011 10:22 Go to next message
appidi
Messages: 2
Registered: October 2011
Location: US
Junior Member
Hi,

I was working on a custom report which has AR/AP requirement , I wrote the code for AR part but not sure whether logic is right, please let me know if i need to make any changes to the code, also I'm not sure whether i'm missing any joins because the cost of the query is high, i guess mainly because of xla_distribution_links.

:=Basically i have to get the Amount. Attached the requirement file.

Requirement:
===============
Receipts:
1.Within the receipts section there are 6 rows.
a.The first 5 rows are determined by the division segment of the credit side (Receivable Account) of the accounting entry for Standard Receipts.
i.If it is a reversal, then need to look at the debit side (Receivable Account)
b.The 6th row "Other" is determined by the credit side of the accounting entry for Standard Receipts that have a division other than that of the first 5 rows (i.e. Division 00, which would cover Unapplied, OnAccount, and Unidentified receipts) and for Positive Miscellaneous Receipts and any journal entry that is not source of "Receivables" hitting one of the cash accounts



Code:
======

SELECT DISTINCT acr.receipt_number
,ard.code_combination_id,gcc.segment2, (NVL(SUM(gb.period_net_dr),0)-NVL(SUM(gb.period_net_cr),0))
FROM ar_cash_receipts_all acr
,ar_cash_receipt_history_all acrh
,ar_distributions_all ard
,xla_ae_headers xah
,xla_ae_lines xal
,xla_distribution_links xdl
,gl_import_references glimp
,gl_je_batches glb
,gl_je_headers glh
,gl_je_lines gll
,gl_code_combinations gcc
,gl_balances gb
WHERE acr.cash_receipt_id = acrh.cash_receipt_id
AND acrh.cash_receipt_history_id = ard.source_id
AND ard.source_table ='CRH'
--AND acr.receipt_number ='2418224'
AND xal.application_id = '222'
AND xah.ae_header_id = xal.ae_header_id
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.application_id = '222'
AND xah.application_id = '222'
AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xdl.source_distribution_id_num_1 = ard.line_id
AND glimp.je_header_id = glh.je_header_id
AND glimp.je_line_num = gll.je_line_num
AND glimp.je_batch_id = glb.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND glh.je_batch_id = glb.je_batch_id
AND glimp.gl_sl_link_table = xal.gl_sl_link_table
AND glimp.gl_sl_link_id = xal.gl_sl_link_id
AND glimp.reference_5 = xah.entity_id
AND glimp.reference_6 = xah.event_id
AND glimp.reference_7 = xah.ae_header_id
AND gcc.code_combination_id = xal.code_combination_id
AND gll.code_combination_id = gcc.code_combination_id
AND gb.code_combination_id = gcc.code_combination_id
AND gcc.enabled_flag = 'Y'
AND gcc.end_date_active is NULL
AND gb.actual_flag ='A'
AND ((nvl(gb.PERIOD_NET_DR,0) != 0) or (nvl(gb.PERIOD_NET_CR,0) != 0))
AND glh.je_source = 'Receivables'
AND gll.effective_date BETWEEN :begin_date AND :end_date
GROUP BY
acr.receipt_number,ard.code_combination_id,gcc.segment2;

Report message to a moderator

Re: Doubt in AR/XLA Query [message #528596 is a reply to message #528172] Tue, 25 October 2011 22:19 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Don't use a direct join between XLA tables to GL tables.
xla_distribution_links may have detailed level information wrt merging lines in xla_ae_lines.
Same way gl_import_references will have derailed level information wrt merging lines in gl_je_lines.
Moreover you are using sum on gl_batches.
So, all the joins will give you the amount Ntimes.

By
Vamsi

Report message to a moderator

Re: Doubt in AR/XLA Query [message #528597 is a reply to message #528596] Tue, 25 October 2011 22:59 Go to previous message
appidi
Messages: 2
Registered: October 2011
Location: US
Junior Member
Thanks for your response.

I did modify the Query for AR part (below), please let me know if there is any error.Not sure if i need to add any more joins (since cost is high).

I have 2 Questions on AR (Receipts):
==================================
1. Requirement says, If it is a reversal, then need to look at the debit side (Receivable Account).
--> Not sure about this Technically , also how will i know that it is reversed.

2. I was told to get the amount for below query either from gl_balances (OR) from AR itself.
if it is gl_balances i can get PTD amount (dr - cr) , but to avoid going to balances table (performance) i was told to get the Amount from AR, from which table i should get the amount?

Modified Code (AR)
===================

SELECT * -- not sure whether Amt frm gl_balances (or) from AR
FROM ar_payment_schedules_all PS
,ar_receivable_applications_all arr
,ar_cash_receipts_all acr
,ar_cash_receipt_history_all acrh
,ar_distributions_all ard
,xla_ae_headers xah
,xla_ae_lines xal
,xla_distribution_links xdl
,gl_code_combinations gcc
WHERE TRUNC (ps.gl_date) BETWEEN :p_begin_date AND :p_end_date
AND ps.cash_receipt_id = arr.cash_receipt_id
AND ps.status = 'OP'
AND ps.class = 'PMT'
AND acr.cash_receipt_id = acrh.cash_receipt_id
AND ps.cash_receipt_id = acr.cash_receipt_id
AND ard.source_id = arr.receivable_application_id
AND xdl.application_id = 222
AND xah.application_id = xdl.application_id
AND xah.application_id = xal.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.event_id = xah.event_id
AND xdl.source_distribution_id_num_1 = ard.line_id
AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xah.ae_header_id = xal.ae_header_id
AND ard.code_combination_id = gcc.code_combination_id
HAVING NVL(SUM (arr.amount_applied), 0) > 0;

Thanks
Praveen

Report message to a moderator

Previous Topic: Receiving responsibility in purchasing
Next Topic: I-Expenses Help - buttons greyed out
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 02:59:29 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.