10 Replies Latest reply: Oct 30, 2013 6:27 AM by Dan Mason RSS

    GL to AR sql including transaction detail

    Dan Mason

      Hi,

       

      we have jsut upgradede to r12 and i am having to write a view that can get information from receivables that has been posted to GL

       

      i am using the following query:

       

      SELECT DISTINCT GJH.JE_HEADER_ID,

                      GJH.PERIOD_NAME,

                      --      CTL.LINE_TYPE,

                      --   CTL.CUSTOMER_TRX_ID,

                      -- xal.ae_header_id,

                      XE.EVENT_ID,

                      GJH.NAME HEADER_NAME,

                      GJH.DESCRIPTION JE_HEADER_DESCRIPTION,

                      GJH.ACTUAL_FLAG,

                      GJL.DESCRIPTION JE_LINE_DESCRIPTION,

                      RCT.TRX_DATE,

                      XAL.ACCOUNTED_CR ACC_CR,

                      XAL.ACCOUNTED_DR ACC_DR,

                      GJL.JE_LINE_NUM,

                      GJH.JE_CATEGORY,

                      GJH.JE_SOURCE,

                      PARTY.PARTY_NAME PARTY,

                      RCT.TRX_NUMBER TRX_NUM,

                      CTL.DESCRIPTION INV_LINE_DESC,

                      RCT.COMMENTS INV_COMMENTS,

                      'AR' TRX_TYPE,

                      GJL.STATUS,

                      ('.') VARIANCE,

                      ('.') PO_NUM,

                      GJL.CREATION_DATE CREATION_DATE_IN_GLL,

                      GJL.CREATED_BY CREATED_BY_IN_GLL,

                      GJL.LAST_UPDATE_DATE LAST_UPDATE_DATE_IN_GLL,

                      GJL.LAST_UPDATED_BY LAST_UPDATED_BY_IN_GLL,

                      SYSDATE DATE_CREATED_IN_REPORT_TAB,

                      ('00/00/0000 00:00:00') DATE_UPDATED_IN_REPORT_TAB

        FROM GL.GL_JE_HEADERS GJH,

             GL.GL_JE_LINES GJL,

             GL.GL_CODE_COMBINATIONS GCC,

             GL.GL_PERIODS GLP,

             GL.GL_IMPORT_REFERENCES IMP,

             XLA.XLA_AE_LINES XAL,

             XLA.XLA_AE_HEADERS XAH,

             XLA.XLA_EVENTS XE,

             XLA.XLA_TRANSACTION_ENTITIES XTE,

             RA_CUSTOMER_TRX_ALL RCT,

             HZ_PARTIES PARTY,

             AR.HZ_CUST_ACCOUNTS CA,

             GL_CODE_COMBINATIONS_KFV CC,

             AR.RA_CUSTOMER_TRX_LINES_ALL CTL,

             AR.RA_CUST_TRX_LINE_GL_DIST_ALL CTLD

            

      WHERE     1 = 1

             AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

             --AND GJL.STATUS || '' = 'P'

             AND GCC.CODE_COMBINATION_ID = CTLD.CODE_COMBINATION_ID

             AND GJH.PERIOD_NAME = GLP.PERIOD_NAME

             AND GJH.PERIOD_NAME = 'M04-14'

             AND RCT.CUSTOMER_TRX_ID = CTLD.CUSTOMER_TRX_ID

             AND CTLD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID

             --       AND GLP.ADJUSTMENT_PERIOD_FLAG <> 'Y'

             AND GJH.JE_SOURCE = 'Receivables'

             AND GJL.JE_HEADER_ID = IMP.JE_HEADER_ID

             AND GJL.JE_LINE_NUM = IMP.JE_LINE_NUM

             AND IMP.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

             AND IMP.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

             AND XAL.APPLICATION_ID = XAH.APPLICATION_ID

             AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID

             AND XAH.APPLICATION_ID = XE.APPLICATION_ID

             AND XAH.EVENT_ID = XE.EVENT_ID

             AND XE.APPLICATION_ID = XTE.APPLICATION_ID

             AND XTE.APPLICATION_ID = 222

             AND XE.ENTITY_ID = XTE.ENTITY_ID

             AND XTE.ENTITY_CODE = 'TRANSACTIONS'

             AND XTE.SOURCE_ID_INT_1 = RCT.CUSTOMER_TRX_ID

             AND RCT.BILL_TO_CUSTOMER_ID = CA.CUST_ACCOUNT_ID

             AND CA.PARTY_ID = PARTY.PARTY_ID

             AND rcT.CUSTOMER_TRX_ID = ctl.CUSTOMER_TRX_Id

             AND CTL.LINE_TYPE = 'LINE'

             AND XAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

                   AND RCT.TRX_NUMBER = '1000178867'

       

       

      If i put the CTL.DESCRIPTION INV_LINE_DESC in the code it brings back 8 rows when there are only 4 in AR.RA_CUSTOMER_TRX_LINES_ALL for the trx named at the bottom of the code.

       

      the inv line desc is blank for 2 rows but has a value in for two. if i remove the line desc from the select the query returns the correct amount of rows.

       

      can anyone offer any help?