Ap to gl reconciliation – oracle ebs apps r12

  1. PRAYAG VISWAN

    PRAYAG VISWAN Newly Initiated

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    India
    Code (SQL):
    SELECT XEP.NAME LEGAL_ENTITY,
            GJH.NAME JV_NAME,
            XL.MEANING,
            GJL.JE_LINE_NUM,
            GJL.ACCOUNTED_DR,
            GJL.ACCOUNTED_CR,
            GL.SEGMENT4 NATURAL_ACCOUNT,
            HOU.NAME OPERATING_UNT,
            HOU.ORGANIZATION_ID,
            XLATE.ENTITY_CODE,
            POV.VENDOR_NAME,
            AP.INVOICE_NUM,
            AP.INVOICE_DATE,
            XLAL.ACCOUNTING_DATE,
            XLAL.ACCOUNTED_DR,
            XLAL.ACCOUNTED_CR,
            XLAL.CODE_COMBINATION_ID,
            GL.SEGMENT1 || DECODE(GL.SEGMENT2, NULL, NULL, '.') || GL.SEGMENT2 ||
            DECODE(GL.SEGMENT3, NULL, NULL, '.') || GL.SEGMENT3 ||
            DECODE(GL.SEGMENT4, NULL, NULL, '.') || GL.SEGMENT4 ||
            DECODE(GL.SEGMENT5, NULL, NULL, '.') || GL.SEGMENT5 ||
            DECODE(GL.SEGMENT6, NULL, NULL, '.') || GL.SEGMENT6 ||
            DECODE(GL.SEGMENT7, NULL, NULL, '.') || GL.SEGMENT7 ||
            DECODE(GL.SEGMENT8, NULL, NULL, '.') || GL.SEGMENT8 GL_ACCT
       FROM GL_JE_HEADERS                GJH,
            GL_JE_LINES                  GJL,
            GL_IMPORT_REFERENCES         GIR,
            XLA.XLA_TRANSACTION_ENTITIES XLATE,
            AP_INVOICES_ALL              AP,
            XLA_EVENTS                   XLAE,
            XLA_AE_LINES                 XLAL,
            XLA_AE_HEADERS               XLAH,
            GL_CODE_COMBINATIONS         GL,
            PO_VENDORS                   POV,
            HR_OPERATING_UNITS           HOU,
            XLE_ENTITY_PROFILES          XEP,
            XLA_LOOKUPS                  XL
      WHERE 1 = 1
        AND XLATE.APPLICATION_ID = 200
        AND XLATE.SOURCE_ID_INT_1 = AP.INVOICE_ID
        AND POV.VENDOR_ID = AP.VENDOR_ID
        AND GL.CODE_COMBINATION_ID = XLAL.CODE_COMBINATION_ID
        AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
        AND XLATE.ENTITY_ID = XLAE.ENTITY_ID
        AND XLAH.EVENT_ID = XLAE.EVENT_ID
        AND XLAE.APPLICATION_ID = XLATE.APPLICATION_ID
        AND XLATE.ENTITY_CODE = 'AP_INVOICES'
        AND AP.ORG_ID = HOU.ORGANIZATION_ID
        AND XLAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
        AND XLAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
        AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
        AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
        AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
        AND XEP.LEGAL_ENTITY_ID = HOU.DEFAULT_LEGAL_CONTEXT_ID
        AND XLAL.ACCOUNTING_CLASS_CODE = XL.LOOKUP_CODE
        AND GL.CODE_COMBINATION_ID = :P_CODE_COMBINATION_ID
        AND HOU.ORGANIZATION_ID = :ORG_ID
    UNION ALL
    SELECT XEP.NAME LEGAL_ENTITY,
            GJH.NAME JV_NAME,
            XL.MEANING,
            GJL.JE_LINE_NUM,
            GJL.ACCOUNTED_DR,
            GJL.ACCOUNTED_CR,
            GL.SEGMENT4 NATURAL_ACCOUNT,
            HOU.NAME OPERATING_UNIT,
            HOU.ORGANIZATION_ID,
            XLATE.ENTITY_CODE,
            POV.VENDOR_NAME,
            TO_CHAR(ACA.CHECK_NUMBER),
            ACA.CHECK_DATE,
            XLAL.ACCOUNTING_DATE,
            XLAL.ACCOUNTED_DR,
            XLAL.ACCOUNTED_CR,
            XLAL.CODE_COMBINATION_ID,
            GL.SEGMENT1 || DECODE(GL.SEGMENT2, NULL, NULL, '.') || GL.SEGMENT2 ||
            DECODE(GL.SEGMENT3, NULL, NULL, '.') || GL.SEGMENT3 ||
            DECODE(GL.SEGMENT4, NULL, NULL, '.') || GL.SEGMENT4 ||
            DECODE(GL.SEGMENT5, NULL, NULL, '.') || GL.SEGMENT5 ||
            DECODE(GL.SEGMENT6, NULL, NULL, '.') || GL.SEGMENT6 ||
            DECODE(GL.SEGMENT7, NULL, NULL, '.') || GL.SEGMENT7 ||
            DECODE(GL.SEGMENT8, NULL, NULL, '.') || GL.SEGMENT8 GL_ACCT
       FROM GL_IMPORT_REFERENCES         GIR,
            XLA.XLA_TRANSACTION_ENTITIES XLATE,
            AP_CHECKS_ALL                ACA,
            XLA_EVENTS                   XLAE,
            XLA_AE_LINES                 XLAL,
            XLA_AE_HEADERS               XLAH,
            GL_JE_HEADERS                GJH,
            GL_JE_LINES                  GJL,
            GL_CODE_COMBINATIONS         GL,
            PO_VENDORS                   POV,
            HR_OPERATING_UNITS           HOU,
            XLE_ENTITY_PROFILES          XEP,
            XLA_LOOKUPS                  XL
      WHERE 1 = 1
        AND XLATE.APPLICATION_ID = 200
        AND XLATE.SOURCE_ID_INT_1 = ACA.CHECK_ID
        AND POV.VENDOR_ID = ACA.VENDOR_ID
        AND GL.CODE_COMBINATION_ID = XLAL.CODE_COMBINATION_ID
        AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
        AND XLATE.ENTITY_ID = XLAE.ENTITY_ID
        AND XLAH.EVENT_ID = XLAE.EVENT_ID
        AND XLAE.APPLICATION_ID = XLATE.APPLICATION_ID
        AND XLATE.ENTITY_CODE = 'AP_PAYMENTS'
        AND HOU.ORGANIZATION_ID = ACA.ORG_ID
        AND HOU.DEFAULT_LEGAL_CONTEXT_ID = XEP.LEGAL_ENTITY_ID
        AND XLAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
        AND XLAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
        AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
        AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
        AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
        AND XLAL.ACCOUNTING_CLASS_CODE = XL.LOOKUP_CODE
        AND GL.CODE_COMBINATION_ID = :P_CODE_COMBINATION_ID
        AND HOU.ORGANIZATION_ID = :ORG_ID
     

    Attached Files: