Query For Customer Balances for specific GL Account

Download Query

Query For Customer Balances for specific GL Account

SELECT   glcc.concatenated_segments ACCOUNT,  account_name,
         SUM (xlal.unrounded_accounted_dr) xla_unrounded_accounted_dr,
         SUM (xlal.unrounded_accounted_cr) xla_unrounded_accounted_cr
    FROM apps.gl_je_batches b,
         apps.gl_je_headers h,
         apps.gl_je_lines l,
         apps.fnd_user u,
         apps.fnd_user u1,
         apps.gl_code_combinations_kfv glcc,
         apps.gl_import_references gir,
         apps.xla_ae_lines xlal,
         apps.xla_ae_headers xlah,
         apps.xla_events xlae,
         xla.xla_transaction_entities xlate,
         apps.ra_customer_trx_all rcta
        , apps.hz_cust_accounts_all hca
   WHERE b.created_by = u.user_id
     AND h.created_by = u1.user_id
     AND b.je_batch_id = h.je_batch_id
     AND h.je_header_id = l.je_header_id
     AND xlal.code_combination_id = glcc.code_combination_id
     AND l.je_header_id = gir.je_header_id
     AND l.je_line_num = gir.je_line_num
     AND gir.gl_sl_link_table = xlal.gl_sl_link_table
     AND gir.gl_sl_link_id = xlal.gl_sl_link_id
     AND xlal.ae_header_id = xlah.ae_header_id
     AND xlah.event_id = xlae.event_id
     AND xlae.entity_id = xlate.entity_id
     AND xlae.application_id = xlate.application_id
     AND h.je_source = 'Receivables'
     AND h.period_name like '%13'
     AND xlae.application_id = xlah.application_id
     AND xlal.application_id = xlah.application_id
     AND xlae.application_id = 222
     AND glcc.segment2 = '401217'
     AND glcc.segment1='10'
     --AND h.ledger_id = 2021
     AND xlate.source_id_int_1 = rcta.customer_trx_id(+)
     AND rcta.bill_to_customer_id = hca.cust_account_id(+)
GROUP BY glcc.concatenated_segments,  account_name

Download Query