Journal Approver in GL

Can the preparer of the journal batch approve the journal?

Yes, preparer can approve the journal when below two conditions are met
1. When the Journals: Allow Preparer Approval profile option is set to ‘Yes’
2. Preparer authorization limit is more than maximum absolute amount of journal batch

Preparer authorization limit can be obtained using below query

R11:
SELECT nvl(authorization_limit, 0)
FROM GL_AUTHORIZATION_LIMITS a, fnd_user b,gl_set_of_books c
WHERE a.employee_id = b.employee_id
AND a.set_of_books_id =c.set_of_books_id
And b.user_name=’&peparer_id’
And c.name=’&set_of_books_name’;

R12:

SELECT nvl(authorization_limit, 0)
FROM GL_AUTHORIZATION_LIMITS a, fnd_user b,gl_ledgers c
WHERE a.employee_id = b.employee_id
AND a.ledger_id =c.ledger_id
And b.user_name=’&preparer_id’
And c.name=’&ledger_name’;

Maximum line amount of journal batch can be found using below query

SELECT max(abs(nvl(JEL.accounted_dr, 0) – nvl(JEL.accounted_cr, 0)))
FROM GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB
WHERE JEH.je_batch_id = JEB.je_batch_id
AND JEH.je_header_id = JEL.je_header_id
AND JEH.currency_code <> ‘STAT’
And JEB.name=&journal_batch_name;

Is preparer same as creator of journal batch?

No, Preparer is the one who initiates the journal batch approval process and he/she need not be the journal
batch creator.

How to find supervisor of the user and the authorization limit of  the user and supervisor?

Use below query to get the user’s supervisor details and authorization limits of the user and his supervisor.
R11:
select a.user_name user_id ,b.full_name user_full_name,
c.d_supervisor_id supervisor_user_id ,
d.authorization_limit User_auth_limit ,
e.authorization_limit Supervisor_auth_limit
from
fnd_user a,
per_all_people_f b,
PER_ASSIGNMENTS_V7 c,
gl_authorization_limits_v d,
gl_authorization_limits_v e
Where a.employee_id=b.person_id
and b.person_id=c.person_id(+)
and a.employee_id=d.employee_id(+)
and c.supervisor_id=e.employee_id(+)
and a.user_name=’&preparer_id’;

R12:
select a.user_name user_id ,b.full_name user_full_name,
c.d_supervisor_id supervisor_user_id ,
d.authorization_limit User_auth_limit ,
e.authorization_limit Supervisor_auth_limit
from
fnd_user a,
per_all_people_f b,
PER_ASSIGNMENTS_V7 c,
gl_authorization_limits_v d,
gl_authorization_limits_v e
Where a.employee_id=b.person_id
and b.person_id=c.person_id(+)
and a.employee_id=d.employee_id(+)
and c.supervisor_id=e.employee_id(+)
and a.user_name=’&preparer_id’;

How to find all work flow notification details of specific journal batch?

Use below queries to find work flow activity status & notification details of the journal batch

select * from wf_notifications where notification_id in
(select notification_id from WF_ITEM_ACTIVITY_STATUSES where
item_type=’GLBATCH’ and item_key like ‘&je_batch_id%’ );

Select * from WF_ITEM_ACTIVITY_STATUSES where
item_type=’GLBATCH’ and item_key like ‘&je_batch_id%’;

je_batch_id can be obtained using below query
select je_batch_id from gl_je_batches where name=&je_batch_name;

What are different types of approver methods and how to set approver method?

There are 3 types of approver methods

1.Go Up Management Chain –Goes up in HRMS manager hierarchy
2.Go Direct —Goes directly to the person with authorization limit
3.One Stop Then Go Direct – Goes to the immediate manager then directly to the person with authorization limit

T

Journal Approval in General Ledger

1. Enabled Journal Approval in Ledger Setup (Accounting Setup)
2. Defined journal sources that require journal approval
3. Entered all employees who are involved in preparing and approving journal entries.
4. Create an approval hierarchy:
- If using a shared install of Oracle Human Resources, use the Enter Person form of GL to enter all employees who are involved and preparing and approving journal entries and batches
- If using a full install of Oracle Human Resources, enter employees in the People window from the Human Resources Responsibility (required)
- When entering employee, enter the employee’s supervisor or manager name (this is the default next approver for journal entries and batches)
5. Defined Journal authorization limits

6. Add journals and approve, notifications will sent to next approval (Supervisor)

GL_IMPORT_HOOK_PKG in R12

GL_IMPORT_HOOK_PKG in R12

Have you ever needed something to be done after GL transfer from sources like Receivables, Payables or Inventory and before Journal Import is run? While the first requirement is to do something before journal import is run and the second one is after before GL posting is done.

Usually we resort to solutions like creating a new program and sandwiching that program between GL transfer and Journal Import program. It works, but again, we need run the Gl interface program setting the value for the parameter Journal Import as ‘No’. This approach has inherent issues. What should we do if the custom program fails? Should we let the journal import run?  If it runs, the objective is failed. On the other hand if you want to stop the journal import from picking the rows, you need to manage the status in the interface table (one way of doing it). This gets complicated as you need to manage all the rows that go in as pair of accounting entries. On top of it the issue gets complicated based on summary posting versus detail posting. So let the journal import manage the business of balancing of accounting entries and let us do our part of managing the rows in the gl_interface table the way we want it.

So this post is to address the first one where the requirement is to update one of the references in gl_interface with the comments from the Miscellaneous Cash Receipts coming from Receivables. This update will happen after your transfer to GL from Receivables and before Journal Import is run. I can understand why this is a common requirement. Usually people who use GL as well as auditors require reasons why the miscellaneous cash was posted. Updating these reference columns makes those comments visible in journal entries screen. Just to warn you, this works only if you transfer from source in Detail and not in Summary mode. See the images of miscellaneous cash receipt and journal entries screen.

This is just an example on one use of this package. It can be used for any of your needs either be pre or post journal import program.

Code:

CREATE OR REPLACE PACKAGE test_je_import_hook
AS
   FUNCTION update_gl_interface (
      sob_id   IN              NUMBER,
      run_id   IN              NUMBER,
      errbuf   IN OUT NOCOPY   VARCHAR2
   )
      RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY test_je_import_hook
AS
   FUNCTION update_gl_interface (
      sob_id   IN              NUMBER,
      run_id   IN              NUMBER,
      errbuf   IN OUT NOCOPY   VARCHAR2
   )
      RETURN BOOLEAN
   IS
      l_run_id            NUMBER;
      l_sob_id            NUMBER;
      l_cash_receipt_id   NUMBER;
      l_comments          VARCHAR2 (240);

      CURSOR int_control (p_run_id IN NUMBER, p_sob_id IN NUMBER)
      IS
         SELECT GROUP_ID, set_of_books_id sob_id
           FROM gl_interface_control
          WHERE interface_run_id = p_run_id AND set_of_books_id = p_sob_id;

      CURSOR gl_int (p_group_id IN NUMBER, p_sob_id IN NUMBER)
      IS
         SELECT reference10, reference22, GROUP_ID
           FROM gl_interface
          WHERE GROUP_ID = p_group_id
            AND user_je_source_name = 'Receivables'
            AND user_je_category_name = 'Misc Receipts'
            AND reference29 = 'MISC_MISCCASH'
            AND reference28 = 'MISC'
            AND reference30 = 'AR_MISC_CASH_DISTRIBUTIONS'
            AND GROUP_ID = p_group_id
            AND set_of_books_id = p_sob_id;
   BEGIN
      --fnd_global.apps_initialize (2154, 51067, 101);
      --fnd_client_info.set_org_context (649);
      l_run_id := run_id;
      l_sob_id := sob_id;

      FOR i IN int_control (l_run_id, l_sob_id)
      LOOP
         FOR j IN gl_int (i.GROUP_ID, i.sob_id)
         LOOP
            SELECT comments
              INTO l_comments
              FROM ar_cash_receipts
             WHERE cash_receipt_id = TO_NUMBER (j.reference22);

            IF l_comments IS NOT NULL
            THEN
               IF j.reference10 <> l_comments
               THEN
                  UPDATE gl_interface
                     SET reference10 = l_comments
                   WHERE user_je_source_name = 'Receivables'
                     AND user_je_category_name = 'Misc Receipts'
                     AND reference29 = 'MISC_MISCCASH'
                     AND reference28 = 'MISC'
                     AND reference30 = 'AR_MISC_CASH_DISTRIBUTIONS'
                     AND GROUP_ID = j.GROUP_ID
                     AND reference22 = j.reference22
                     AND set_of_books_id = i.sob_id;
               END IF;
            END IF;
         END LOOP;
      END LOOP;

      RETURN TRUE;
   EXCEPTION
      WHEN OTHERS
      THEN
         errbuf :=
            'User Defined Exception in test_je_import_hook.update_gl_interface';
         RETURN TRUE;
   END;
END test_je_import_hook;
/

SHOW errors;
/

======================================================================

CREATE OR REPLACE PACKAGE BODY APPS.gl_import_hook_pkg AS
/* $Header: glujihkb.pls 115.4 2002/11/13 20:08:17 djogg ship $ */

--
-- PUBLIC FUNCTIONS
--

  --
  -- Procedure
  --   pre_module_hook
  -- Purpose
  --   Hook into journal import for other products.
  --   This procedure is called after journal import has selected
  --   the sources to process, but before it has started processing the data.
  --   If you need to use this hook, please add a call to your own
  --   package before the return statement.  Please do NOT commit
  --   your changes in your package.
  -- Returns
  --   TRUE - upon success (allows journal import to continue)
  --   FALSE - upon failure (causes journal import to abort and display the
  --        error in errbuf)
  -- History
  --   19-JUN-95  D. J. Ogg    Created
  -- Arguments
  --   sob_id           The set of books id
  --   run_id  The import run id
  --   errbuf  The error message printed upon error
  -- Example
  --   gl_import_hook_pkg.pre_module_hook(2, 100, errbuf);
  -- Notes
  --
  FUNCTION pre_module_hook(sob_id    IN     NUMBER,
      run_id    IN     NUMBER,
      errbuf    IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
  BEGIN
    -- AX section
    IF ax_setup_pkg.ax_installed THEN
      IF NOT ax_ezl_filter_pkg.EZLFilter(sob_id, run_id, errbuf) THEN
        RETURN FALSE;
      END IF;
    END IF;

    -- Please put your function call here.  Make it the following format:
    --    IF (NOT dummy(sob_id, run_id, errbuf)) THEN
    --      RETURN(FALSE);
    --    END IF;
    
        IF (NOT test_je_import_hook.update_gl_interface(sob_id, run_id, errbuf)) THEN
          RETURN(FALSE);
        END IF;
    RETURN(TRUE);
  END pre_module_hook;


  --
  -- Procedure
  --   post_module_hook
  -- Purpose
  --   Hook into journal import for other products.
  --   This procedure is called after journal import has inserted all of the
  --   data into gl_je_batches, gl_je_headers, and gl_je_lines, but before
  --   it does the final commit.
  --   This routine is called once per 100 batches.
  --   If you need to use this hook, please add a call to your own
  --   package before the return statement.  Please do NOT commit
  --   your changes in your package.
  -- Returns
  --   TRUE - upon success (allows journal import to continue)
  --   FALSE - upon failure (causes journal import to abort and display the
  --        error in errbuf)
  -- History
  --   28-FEB-00  D. J. Ogg    Created
  -- Arguments
  --   batch_ids        A list of batch ids, separated by the separator
  --   separator        The separator
  --   last_set         Indicates whether or not this is the last set
  --   errbuf        The error message printed upon error
  -- Example
  --   gl_import_hook_pkg.post_module_hook(2, 100, errbuf);
  -- Notes
  --
  FUNCTION post_module_hook(batch_ids  IN     VARCHAR2,
                            separator  IN     VARCHAR2,
                            last_set   IN     BOOLEAN,
                errbuf     IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
  BEGIN

    gl_ip_process_batches_pkg.process_batches(batch_ids, separator, last_set);

    -- Please put your function call here.  Make it the following format:
    --    IF (NOT dummy(sob_id, run_id, errbuf)) THEN
    --      RETURN(FALSE);
    --    END IF;

    RETURN(TRUE);
  END post_module_hook;
END gl_import_hook_pkg;
/

=============================================================

Overview of Oracle General Ledger

Oracle General ledger is the core module concerned with the financial reporting of information .All the financial information will mostly end up in General Ledger from where final reporting will be done. General ledger module willRecord , Analyze , Manipulate and Report the information.

General Ledger will receive information from all the oracle modules

1. Order Management
2. Purchasing
3. Receivables
4. Payables
5. HRMS
6. Projects
7. CRM etc.

All the information is recorded , analyzed and then reported by General Ledger

The Basic Accounting cycle of General Ledger is 

1. Open the period
2. Receive data
3. Create Journals/Reverse Journals
4. Post Journals
5. Review
6. Revalue
7. Consolidate
8. Reconcile
9. Run Reports
10. Close Accounting Period

General Ledger Module will record all the financial information in the form of journal entries

It will create journal entries for all the information coming in an then post it to the final set of books.

General Ledger System can receive information from Oracle Systems and Non-Oracle Systems.

Form Oracle Systems:

Data Processing Cycle

From Non Oracle Systems

1. Data Will Be uploaded via Spreadsheets
2. Journals Will be created
3. Journals will be posted to General Ledger

Query for Ledger, OU, Legal Enity, balancing segment

SELECT DISTINCT hrl.country,
  hroutl_bg.NAME bg,
  hroutl_bg.organization_id,
  lep.legal_entity_id,
  lep.NAME legal_entity,
  hroutl_ou.NAME ou_name,
  hroutl_ou.organization_id org_id,
  hrl.location_id,
  hrl.location_code,
  glev.FLEX_SEGMENT_VALUE
FROM xle_entity_profiles lep,
  xle_registrations reg,
  hr_locations_all hrl,
  hz_parties hzp,
  fnd_territories_vl ter,
  hr_operating_units hro,
  hr_all_organization_units_tl hroutl_bg,
  hr_all_organization_units_tl hroutl_ou,
  hr_organization_units hou,
  gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag      = 'Y'
AND lep.party_id                       = hzp.party_id
AND lep.legal_entity_id                = reg.source_id
AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
AND hrl.location_id                    = reg.location_id
AND reg.identifying_flag               = 'Y'
AND ter.territory_code                 = hrl.country
AND lep.legal_entity_id                = hro.default_legal_context_id
AND hou.organization_id = hro.organization_id
AND hroutl_bg.organization_id          = hro.business_group_id
AND hroutl_ou.organization_id          = hro.organization_id
AND glev.legal_entity_id               = lep.legal_entity_id
AND hou.organization_id                =241