Saturday, May 17, 2014

Drill Down from AR Cash Receipts to GL Ledger R12


Query:

select distinct t.* from
(select gl.name Ledger_Name,
       (gcc.segment1||'-'||
       gcc.segment2||'-'||
       gcc.segment3||'-'||
       gcc.segment4||'-'||
       gcc.segment5||'-'||
       gcc.segment6)Account,
       araa.gl_date Gl_Date,
       xal.accounting_class_code Class,
       xal.accounted_dr,
       xal.accounted_cr
from GL_LEDGERS gl,
     XLA_DISTRIBUTION_LINKS  xdl,
     XLA_AE_LINES            xal,
     gl_code_combinations    gcc,
     ar_cash_receipts_all    acra,
     ar_payment_schedules_all apsa,
     AR_RECEIVABLE_APPLICATIONS_ALL araa,
     AR_DISTRIBUTIONS_ALL           ada
where xal.ledger_id = gl.ledger_id
and      acra.cash_receipt_id = apsa.cash_receipt_id
and      apsa.payment_schedule_id = araa.payment_schedule_id
and      araa.payment_schedule_id = apsa.payment_schedule_id
and      ada.source_id = araa.receivable_application_id -----------
--and      ada.source_type = 'CASH'
--and      ada.code_combination_id = gcc.code_combination_id
and      ada.third_party_id = acra.pay_from_customer
and      ada.third_party_sub_id = acra.customer_site_use_id
and      xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =  ada.line_id
and      ada.source_table = 'RA'
and      xdl.event_id = araa.event_id
--and      xdl.event_class_code like 'RECEIPT' ---------
--and      xdl.event_type_code like 'RECEIPT_ALL'------------
and      xdl.source_distribution_type like 'AR_DISTRIBUTIONS_ALL'
--and      xdl.rounding_class_code = 'RECEIVABLE'
--and      xdl.application_id = 222
and      xal.ae_header_id = xdl.ae_header_id
and      xal.code_combination_id = gcc.code_combination_id
and      acra.receipt_number = &Recipt_Number ) t


select * from ar_distributions_all;

select * from XLA_DISTRIBUTION_LINKS where source_distribution_type LIKE 'AR_DISTRIBUTIONS_ALL';


Reactions:

0 comments:

Post a Comment