Thursday, 11 April 2019

Query to find DIfference Between XLA & GL in Oracle Apps R12

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);

No comments:

Post a Comment