Join between ap_checks_all and xla tables

Join between AP_Checks_All and xla tables

Here in this post , we will be discuss about the SQL join between the Payments and the XLA tables. In this we will try to join the Oracle Payments/Check accounting with the XLA Tables in oracle apps. If you want to drill the Payments or check from Payables to the General ledger you can use these join to write the sql query to extract the information's from Payables to Xla and General Ledger.

 

6 Important Tables for Join between ap_checks_all and xla tables

 

1.XLA_DISTRIBUTION_LINKS

2.AP_PAYMENT_HIST_DISTS

3.AP_PAYMENT_HISTORY_ALL

4.AP_CHECKS_ALL

5.xla_ae_headers

6.xla_ae_lines

 

SQL Join between AP_Checks_All and xla tables

 

select aca.CHECKRUN_NAME, aca.CHECK_NUMBER, Aca.AMOUNT,

 APHa.TRANSACTION_TYPE, APHa.TRX_PMT_AMOUNT,

 APHD.AMOUNT PAY_DIST_AMOUNT,

 xdl.ACCOUNTING_LINE_CODE, XDL.LINE_DEFINITION_CODE, XDL.EVENT_CLASS_CODE,

 nvl(xal.accounted_dr,0) accounted_dr, nvl(xal.accounted_cr,0) accounted_cr

 from apps.XLA_DISTRIBUTION_LINKS xdl,apps.AP_PAYMENT_HIST_DISTS  aphd,

apps.AP_PAYMENT_HISTORY_ALL APHA,apps.AP_CHECKS_ALL aca,apps.xla_ae_headers xah,apps.xla_ae_lines xal

where xdl.SOURCE_DISTRIBUTION_TYPE = 'AP_PMT_DIST'

and xdl.APPLICATION_ID = 200

and xdl.SOURCE_DISTRIBUTION_ID_NUM_1 in (1213175,1213176)

and aphd.PAYMENT_HIST_DIST_ID=xdl.SOURCE_DISTRIBUTION_ID_NUM_1

and apha.PAYMENT_HISTORY_ID=aphd.PAYMENT_HISTORY_ID

and aca.check_id=apha.check_id

and xdl.AE_LINE_NUM = xal.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID