Search This Blog

Thursday, 7 May 2015

Drilldown from GL, XLA to AP Payments Transaction

SELECT apca.check_number, OU.set_of_books_id , XLAH.LEDGER_ID XLAH_LEDGER_ID,XLAL.LEDGER_ID XLAL_LEDGER_ID, GLJH.LEDGER_ID GL_LEDGER_ID,


      GLJB.NAME je_batch_name,
       GLJB.description je_batch_description,
       GLJB.running_total_accounted_dr je_batch_total_dr,
       GLJB.running_total_accounted_cr je_batch_total_cr,
       GLJB.status je_batch_status,
       GLJB.default_effective_date je_batch_effective_date,
       GLJB.default_period_name je_batch_period_name,
       GLJB.creation_date je_batch_creation_date,
       
       FDU.user_name je_batch_created_by,

       GLJH.je_category je_header_category,
       GLJH.je_source je_header_source,
       GLJH.period_name je_header_period_name,
       GLJH.NAME je_header_journal_name,
       GLJH.status je_header_journal_status,
       GLJH.creation_date je_header_created_date,
       GLJH.description je_header_description,
       GLJH.running_total_accounted_dr je_header_total_acctd_dr,
       GLJH.running_total_accounted_cr je_header_total_acctd_cr,
       
       FDU1.user_name je_header_created_by,
       
       GLJL.je_line_num je_lines_line_number,
       GLJL.ledger_id je_lines_ledger_id,
       glcc.concatenated_segments je_lines_ACCOUNT,
       GLJL.entered_dr je_lines_entered_dr,
       GLJL.entered_cr je_lines_entered_cr,
       GLJL.accounted_dr je_lines_accounted_dr,
       GLJL.accounted_cr je_lines_accounted_cr,
       GLJL.description je_lines_description,
       glcc1.concatenated_segments xla_lines_account,
       
       xlal.accounting_class_code xla_lines_acct_class_code,
       xlal.accounted_dr xla_lines_accounted_dr,
       xlal.accounted_cr xla_lines_accounted_cr,
       xlal.description xla_lines_description,
       xlal.accounting_date xla_lines_accounting_date,

       xlate.entity_code xla_trx_entity_code,
       xlate.source_id_int_1 xla_trx_source_id_int_1,
       xlate.source_id_int_2 xla_trx_source_id_int_2,
       xlate.source_id_int_3 xla_trx_source_id_int_3,
       xlate.security_id_int_1 xla_trx_security_id_int_1,
       xlate.security_id_int_2 xla_trx_security_id_int_2,
       xlate.transaction_number xla_trx_transaction_number
       
       ,APCA.CHECK_NUMBER

       
  FROM gl_je_batches GLJB,
       gl_je_headers GLJH,
       gl_je_lines GLJL,
       fnd_user FDU,
       fnd_user FDU1,
       gl_code_combinations_kfv glcc,
       gl_code_combinations_kfv glcc1,
       gl_import_references gir,
       xla_ae_lines xlal,
       xla_ae_headers xlah,
       xla_events xlae,
       xla.xla_transaction_entities xlate,
       
       ap_checks_all apca,
        HR_OPERATING_UNITS OU

 WHERE 1=1

AND GLJB.created_by = FDU.user_id
 AND GLJH.created_by = FDU1.user_id
 AND GLJB.je_batch_id = GLJH.je_batch_id
 AND GLJH.je_header_id = GLJL.je_header_id
 AND GLJL.code_combination_id = glcc.code_combination_id

 AND GLJL.je_header_id = gir.je_header_id
 AND GLJL.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.application_id = xlah.application_id
 AND xlal.ae_header_id = xlah.ae_header_id
 AND xlal.code_combination_id = glcc1.code_combination_id

 AND xlah.application_id = xlae.application_id
 AND xlah.event_id = xlae.event_id

 AND xlae.application_id = xlate.application_id
 AND xlae.entity_id = xlate.entity_id
 AND xlate.source_id_int_1 = apca.check_id

AND GLJH.je_category  in ('Payments','Purchase Invoices')

AND APCA.ORG_ID = OU.organization_id

--AND b.default_period_name       = '01_APR-2009'

and GLJH.je_header_id = 85452

 ORDER BY GLJH.je_category

1 comment: