Digression: Querying for Organizations, Operating Units, Legal Entities and Ledgers in an R12 Environment

Invariably in an Oracle Applications environment, one needs to be aware of and know the relationship between organizations, operating units, legal entities and ledgers.

This query is just a variation of the apps.org_organization_definitions Oracle Applications view whose results provide this insight.  I take no credit for this:

 select
 hou.organization_id organization_id,
    mp.organization_code organization_code,
    lgr.ledger_id,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information3), to_number(null)) operating_unit,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information2), null) legal_entity
  from hr.hr_all_organization_units hou,
    hr.hr_organization_information hoi1,
    hr.hr_organization_information hoi2,
    inv.mtl_parameters mp,
    gl.gl_ledgers lgr
  where hou.organization_id = hoi1.organization_id
  and hou.organization_id   = hoi2.organization_id
  and hou.organization_id   = mp.organization_id
  and hoi1.org_information1 = 'INV'
  and hoi1.org_information2 = 'Y'
  and ( hoi1.org_information_context || '') = 'CLASS'
  and ( hoi2.org_information_context || '') ='Accounting Information'
  and to_number(decode(rtrim(translate(hoi2.org_information1,'0123456789',' ')), null, hoi2.org_information1,-99999)) = lgr.ledger_id
  and lgr.object_type_code ='L'
  and nvl(lgr.complete_flag,'Y') ='Y'

Troublshooting The ORA-04021 Error in a Development Environment

I notice that my regeneration was stalling through the install4.sql script in a development environment. Finally, the regeneration timed out.

Next, I check for the last spool file and it is the file, ycrenvph.lst. This script creates the header for the package, noetix_env_pkg.

I notice the error, ORA-04021 being thrown. This is the timeout error.

I query the v$session sessions owned by apps or noetix_sys which could be locking this package. Nothing stands out.

Next, I query the V$access table and I notice that there is session that has a lock on this object.

SELECT * FROM V$ACCESS WHERE OBJECT = 'NOETIX_ENV_PKG';

It is important to be cognizant of what sessions you intend to kill or let run its course.  It is important to know the details regarding a specific session (who, what, where, how and why) before any course of action is taken.  No matter what environment one is in, it usually is better to let something run its course than to initiate terminating a session.   

Next, I find this session's detail:

SELECT * FROM V$SESSION WHERE SID = 1098;

and kill the session.

ALTER SYSTEM KILL SESSION '1098,61537';