Configuring Projects GL Reconciliation Manual Journal Entries

In addition to the default supported use cases, the Projects GL Reconciliation solution in this release supports an additional use case to identify manually created project journals in GL.

For EBS R11.5.10 and R12.x, most often customers configure a descriptive flexfield in source to populate it with a project number or identifier when creating manual journals to associate a project to that journal. In such cases, the ETL code can be customized to extract and report these manually created journals. So in case if a customer has created a descriptive flexfield in EBS Apps GL Journal header or Lines table and they use it to populate Project Number for manually created journals, the Projects GL Reconciliation ODI interfaces can identify those transactions after doing some customizations to the expression where JOURNAL_SOURCE field is populated during extract.

By default, JOURNAL_SOURCE is populated directly from GL_JE_HEADERS, it will not be linked with Project Sub Ledger and in that case, such transactions will be filtered out.

You customize the ETL code in ODI interfaces to identify manually created journal entries. The descriptive flexfield ATTRIBUTE1 in table GL_JE_HEADERS is used to store the Project Number (or some other Project key).

To Configure Projects GL Reconciliation Manual Journal Entries:

  1. In ODI Designer Navigator, connect your ODI repository.
  2. Navigate to BIApps Project, then Mappings, then <EBS adaptor folder> - SDE_ORA_GLJournalsFact - SDE_ORA_GLJournalsFact.W_GL_OTHER_FS_SQ_GL_JE_LINES.
  3. Open the temp interface and check the expression for JE_SOURCE.

    The expression mapped by default is GL_JE_HEADERS.JOURNAL_SOURCE.

  4. Modify JE_SOURCE field to the following value: 
    GL_JE_HEADERS.JE_SOURCE || (CASE WHEN GL_JE_HEADERS.CONTEXT = 'Project Context' AND GL_JE_HEADERS.ATTRIBUTE1 IS NOT NULL THEN '~PA' ELSE NULL END)

    In general, you need to customize this expression to:

    GL_JE_HEADERS.JOURNAL_SOURCE || (CASE WHEN <Manual Entries for Projects> THEN '~PA' ELSE NULL END CASE)
  5. Save the interface.
  6. Re-generate the scenario for SDE_ORA_GLJOURNALSFACT.

    The next time the ETL is run, JOURNAL_SOURCE field in the W_GL_OTHER_F table will be populated with the value "Manual~PA" for manually created journals for Projects and GL Reconciliation ODI interfaces will identify them as manual journals related to Projects.