SELECT je_source, je_category, je_header_id, je_line_num, gl_amt, xla_amt FROM ( SELECT je_source, je_category, je_header_id, je_line_num, gl_amt, SUM(xla_amt) XLA_AMT FROM ( SELECT B.je_source, B.je_category, A.je_header_id, A.je_line_num, E.gl_sl_link_id, Nvl(A.accounted_dr,0)-Nvl(A.accounted_cr,0) GL_AMT, SUM(Nvl(E.accounted_dr,0)-Nvl(E.accounted_cr,0)) XLA_AMT FROM gl_je_headers B, gl_je_lines A, gl_code_combinations C, ( SELECT X.gl_sl_link_id, X.je_header_id, X.je_line_num, X.gl_sl_link_table FROM gl_import_references X GROUP BY X.gl_sl_link_id, X.je_header_id, X.je_line_num, X.gl_sl_link_table )D, xla_ae_lines E WHERE B.je_header_id = A.je_header_id AND A.je_header_id=D.je_header_id AND A.je_line_num=D.je_line_num AND B.je_header_id=D.je_header_id AND D.gl_sl_link_id=E.gl_sl_link_id AND D.gl_sl_link_table=E.gl_sl_link_table --AND E.APPLICATION_ID=200 --AND A.CODE_COMBINATION_ID=E.CODE_COMBINATION_ID AND A.code_combination_id=C.code_combination_id AND B.status='P' AND b.ledger_id= :P_LEDGER_ID AND c.segment1= :P_Balancing_Segment AND c.segment2= :P_Account_Segment --AND E.GL_SL_LINK_ID=1607140 AND B.default_effective_date BETWEEN '01-MAR-2019' AND '31-MAR-2019' GROUP BY B.je_source, B.je_category, B.je_source, B.je_category, A.je_header_id, A.je_line_num, E.gl_sl_link_id, Nvl(A.accounted_dr,0)-Nvl(A.accounted_cr,0) --ORDER BY JE_HEADER_ID, JE_LINE_NUM, GL_AMT ) GROUP BY je_source, je_category, je_header_id, je_line_num, gl_amt );WHERE nvl(gl_amt,0)!=nvl(xla_amt,0);
Thursday, 11 April 2019
Query to find DIfference Between XLA & GL in Oracle Apps R12
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment