oracle-EBS-PL/sql AP GL总账对应的日记账科目明细

oracle-EBS-PL/sql AP GL总账对应的日记账科目明细

有时候财务需要批量查询每笔gl对应的日记账明细，就可以用下列sql

 select  gcck.segment1,gl.name ledger_name, gjh.status,gl.ledger_id 
,gjh.je_header_id,gjh.je_category,gjh.je_source, gjh.period_name, gjb.NAME batch_name,
gjh.name, gjh.currency_code, TO_CHAR(gjh.default_effective_date,'yyyy-mm-dd') default_effective_date , gjh.actual_flag,
gjl.description line_description
,gcck.concatenated_segments account_code, 
XLA_OA_FUNCTIONS_PKG.GET_CCID_DESCRIPTION(gcck.CHART_OF_ACCOUNTS_ID,gcck.CODE_COMBINATION_ID) COA_NAME ,
gjh.doc_sequence_value
,gjl.entered_dr, gjl.entered_cr, gjl.accounted_dr, gjl.accounted_cr 
, gjl.accounted_dr - gjl.accounted_cr  accounted_amount
,GJL.CREATION_DATE  LINE_CREATION_DATE
,GJL.LAST_UPDATE_DATE LINE_UPDATE_DATE
,GJL.CREATED_BY
from gl_je_batches gjb, gl_ledgers gl, gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations_kfv gcck
where  gl.ledger_id=gjh.ledger_id
and gjh.je_header_id=gjl.je_header_id
/*and gl.ledger_id = '2023'*/
AND gjh.Actual_flag = 'A'
and gjl.code_combination_id=gcck.code_combination_id
and gjh.JE_BATCH_ID = gjb.JE_BATCH_ID(+) and gjb.period_set_name   = 'DEC_Calendar'
 and gcck.segment1 = '2222'  --公司代码
and gcck.concatenated_segments in ('2222.000.111001.000.0000.00','2222.000.111002.000.0000.00')
and gjh.period_name in ('AUG-17')
and gl.name ='RMB Ledger'