关于ORACLE ERP中日记帐的借、贷对应的问题-CSDN博客

对于来源于其他模块的总帐日记帐，经过多次的测试，可以由以下代码来查找：

select decode(gjl_t.accounted_cr,gjl_f.accounted_cr,'你所输入的日记帐行','对应的借或贷') descc,
       GJB_t.ORG_ID,
       gjh_t.je_source,
       gjh_t.name,
       GJH_t.DEFAULT_EFFECTIVE_DATE,
       gjh_t.currency_code,
       --decode(gjl_t.accounted_cr,gjl_f.accounted_cr,'你所输入的日记帐行','对应的借或贷') descc,
       gjl_f.je_line_num line_f,
       gjl_f.code_combination_id,
       nvl(gjl_f.accounted_dr,gjl_f.accounted_cr) ,
       gjl_t.je_line_num line_t,
       gjl_t.code_combination_id,
       gjl_t.accounted_dr,
       gjl_t.accounted_cr
from gl_import_references GIR_f,
       gl_je_lines          gjl_f,
       gl_je_headers        gjh_f,
       GL_JE_BATCHES        GJB_f,
       gl_import_references GIR_t,
       gl_je_lines          gjl_t,
       gl_je_headers        gjh_t,
       GL_JE_BATCHES        GJB_t
where gjl_f.je_header_id = gir_f.je_header_id
   and gjl_t.je_header_id = gir_f.je_header_id
   and gjh_f.je_header_id = gir_f.je_header_id
   and gjh_t.je_header_id = gir_f.je_header_id
   and gjl_f.je_line_num = gir_f.je_line_num
   and gjl_t.je_line_num = gir_t.je_line_num
   AND GJB_f.JE_BATCH_ID = GIR_f.JE_BATCH_ID
   AND GJB_t.JE_BATCH_ID = GIR_t.JE_BATCH_ID
   and gir_f.reference_1=gir_t.reference_1
   and gir_f.reference_2=gir_t.reference_2
   and nvl(gir_f.reference_4,'aaa')=nvl(gir_t.reference_4,'aaa')
   and nvl(gir_f.reference_5,'aaa')=nvl(gir_t.reference_5,'aaa')
   --and gjl_f.je_line_num<>gjl_t.je_line_num
   and gjh_f.je_header_id=18--所要查找的日记帐头ID
   and gjl_f.je_line_num=444--所要查找的日记帐下的行号