Oracle eBusiness Suite R12: Initialize Application Context to debug SQLs inside a Concurrent program

Oracle concurrent programs initializes application context before running the actual code. Therefore, if we take out the SQLs from the concurrent program code (PLSQL or Reports) and execute in the development tools like Toad or SQL Developer, the results could be different. It could be due to 3 different kind of context variables

i.  NLS Context variables:- Many language specific views (Eg:- FND_FLEX_VALUES_VL) will be deepndendent on the the NLS language context variable.

ii. Multi Org context variables :- Org specific synonyms(Eg:-PO_HEADERS) in R12 will not return any data unless we initialize the Multi Org context variables.

iii.Security Context variables:- Access to many Application Object library features (Eg:- Profile option values) is possible only after setting the fnd_global security variables (user_id, responsibility_id etc)

The below script accepts concurrent request id as input and initializes the context variables accordingly. The intialized values can be found in DBMS OUTPUT.DECLARE CURSOR cur_requests IS SELECT req.request_id ,req.requested_by ,req.responsibility_id ,req.responsibility_application_id ,req.security_group_id ,req.conc_login_id ,req.program_application_id ,req.concurrent_program_id ,req.priority_request_id ,multi_org_category ,req.org_id FROM fnd_concurrent_requests req ,fnd_concurrent_programs prog WHERE request_id = &request_id AND req.concurrent_program_id = prog.concurrent_program_id; rec_requests cur_requests%ROWTYPE; l_session_id NUMBER; l_string VARCHAR2(4000); BEGIN OPEN cur_requests; FETCH cur_requests INTO rec_requests; CLOSE cur_requests; fnd_global.initialize(session_id => l_session_id ,user_id => rec_requests.requested_by ,resp_id => rec_requests.responsibility_id ,resp_appl_id => rec_requests.responsibility_application_id ,security_group_id => rec_requests.security_group_id ,site_id => NULL ,login_id => NULL ,conc_login_id => rec_requests.conc_login_id ,prog_appl_id => rec_requests.program_application_id ,conc_program_id => rec_requests.concurrent_program_id ,conc_request_id => rec_requests.request_id ,conc_priority_request => rec_requests.priority_request_id ,form_id => NULL ,form_appl_id => NULL ,conc_process_id => NULL ,conc_queue_id => NULL ,queue_appl_id => NULL ,server_id => NULL); -- initialize Multi-Org Context mo_global.init('M'); IF (rec_requests.multi_org_category = 'S') THEN mo_global.set_policy_context(rec_requests.multi_org_category ,rec_requests.org_id); ELSIF (rec_requests.multi_org_category = 'M') THEN mo_global.set_policy_context(rec_requests.multi_org_category ,NULL); END IF; ---Print the initialized values SELECT 'User :- ' || fnd_global.user_name || CHR(13) || 'Responsibility :- ' || fnd_global.resp_name || CHR(13) || 'Application :- ' || fnd_global.application_name || CHR(13) || 'Request ID :- ' || fnd_global.conc_request_id || CHR(13) || 'MO Global Access Mode :- ' || mo_global.get_access_mode || CHR(13) || 'Org Name :- ' || fnd_global.org_name || CHR(13) || 'HR Business Group :- ' || (SELECT NAME FROM hr_all_organization_units WHERE organization_id = fnd_global.per_business_group_id) || CHR(13) || 'Security Profile :- ' || (SELECT security_profile_name FROM per_security_profiles WHERE security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')) INTO l_string FROM dual; dbms_output.put_line(l_string); END;

Oracle XML Publisher: Generate Skeleton XML data file from SQL

The below steps generates an XML data definition data template from an SQL statement. 

1.Define new concurrent program in EBS with required parameters.

2.Develop the SQL to fetch the data for the report.

3.Download and compile the helper PLSQL package xxxdo_generate_xml.sql

4.Execute the below script with SQL and concurrent program short name as parameters.

BEGIN xxxdo_generate_xml.generate( p_sql => '&SQL', p_conc_program_short_name => '&Concurrent_program_short_name'); END;
5.Save the DBMS output as XML data template file.

6. Upload the data template to Oracle using XML Publisher reponsibility

7. Submit the concurrent request to make sure that XML output is being generated.

The next step is to develop the layout. This is quiet userful to rapidly develop XML publisher reports from relatively simpler SQL.

Identify the record causing an SQL statement to raise an Exception using LOG ERRORS feature

Oracle provides a powerful mechanism to uncover DML errors from version 10g Release 2 onwards. The LOG ERRORS clause is similar to SAVE EXCEPTIONS in PLSQL bulk operations. This feature can be used to identify the records the problematic records which cause an SQL statements to raise an exception.

SQL statements work in all or nothing principle. Even if a single record causes an ‘Invalid Number’ exception, the entire SQL fails. It could be a bit tricky to identify the record(s) which triggered the exception when the SQL is complex and the dataset is large.

As an example, if we run the below SQL against Oracle provided EMP table,

SELECT emp.*

      ,sal / comm sal_to_comm_ratio

FROM   scott.emp emp;

The result will be ORA-01476 since there is atleast one record where COMM is 0.

But the error message will not give a clue which record has caused this issue. Identifying the record is key to fixing this issue either in the SQL or in the database. Below steps can be performed to make use of the LOG ERRORS feature.

Step 1 : Create a table using the SQL in error. We just need the structure of the table. So please add a condition in the where clause (1=2).

create table emp_test as

SELECT emp.*

      ,sal / comm sal_to_comm_ratio

FROM   scott.emp emp

where 1=2;

Step 2: Create the error logging table for the new table created.

begin

    dbms_errlog.create_error_log(dml_table_name => 'EMP_TEST');

end;

This creates a table with name ERR$_EMP_TEST in the database.

Step 3: Execute the SQL in an INSERT statement with LOG ERRORS clause appended at the end.

INSERT INTO emp_test

  SELECT emp.*

        ,sal / comm sal_to_comm_ratio

  FROM   scott.emp emp LOG errors

  INTO   err$_emp_test reject LIMIT unlimited;

This inserts all records without error to EMP_TEST while the records in error will be kept in ERR$_EMP_TEST.

Step 4: Query up ERR$_EMP_TEST to see the error message and records in error.

select * from ERR$_EMP_TEST;

PLSQL Script

Steps 1 to 3 can be automated using the below PLSQL script. This script accepts the problem SQL as input and performs the steps.

DECLARE

  l_sql_statement    VARCHAR2(30000) := '&select';

  l_create_table_sql VARCHAR2(32767);

  l_insert_table_sql VARCHAR2(32767);

  l_table_name       VARCHAR2(30) := 'XXERR' || TO_CHAR(SYSDATE

                                                       ,'DDMMYYYYHH24MISS');

  l_error_table_name VARCHAR2(30) := 'ERR$_' || l_table_name;

BEGIN

  l_create_table_sql := 'CREATE TABLE ' || l_table_name ||  
                        ' AS SELECT * FROM (' || l_sql_statement ||
                        ') WHERE 1=2';

  
  dbms_output.put_line('Table name is ' || l_table_name);

  dbms_output.put_line(l_create_table_sql);

   
  EXECUTE IMMEDIATE l_create_table_sql;

  
  dbms_errlog.create_error_log(dml_table_name => l_table_name);

  
 l_insert_table_sql := 'INSERT INTO ' || l_table_name || ' SELECT * FROM (' || 
                        l_sql_statement || ') LOG ERRORS INTO err$_' 
                      ||l_table_name || ' (''INSERT'') REJECT LIMIT UNLIMITED';

   
  EXECUTE IMMEDIATE l_insert_table_sql;

  
  dbms_output.put_line('Error Messages are stored in table ' ||                           l_error_table_name);

END;

On executing the script with SQL as input, the DBMS output displays the name of the error table which can be queried to find the record(s) in error.

 Limitations

• The single quotes in the SQL needs to be escaped when running from SQL*Plus or another IDE. This can be done by replacing all single quotes with 2 quotes.

• All derived values in the select clause list need to be given explicit aliases. Otherwise, the table creation will fail.

• This method can uncover only those issues arising from SELECT clause. The exceptions raised from FROM and WHERE clause will still fail the entire SELECT statement.

Oracle Time and Labor: Identify Layouts used by a timecard

OTL layouts are selected for an employee based on OTL Preferences configuration.  There could be many custom layouts defined in the system.  The SQL below accepts the employee number as prameter and displays the layouts being used by the timecards entered for the employee.




SELECT

person.full_name,

       tc.start_time,

       tc.stop_time,

       tc.recorded_hours,

       tc.approval_status,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute1) timecard_entry_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute2) review_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute3) confirmation_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute4) detail_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute5) export_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute6) audit_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute7) approval_fragment_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute8) notification_layout

FROM hxc_timecard_summary      tc,

     per_all_people_f          person,

     hxc_time_attribute_usages usag,

     hxc_time_attributes       attr

WHERE tc.resource_id = person.person_id

AND SYSDATE BETWEEN person.effective_start_date AND person.effective_end_date

AND person.employee_number = '&Employee_number'

AND tc.timecard_id = usag.time_building_block_id

AND tc.timecard_ovn = usag.time_building_block_ovn

AND attr.time_attribute_id = usag.time_attribute_id

AND attr.attribute_category = 'LAYOUT'

ORDER BY tc.start_time DESC;

Oracle eBusiness Suite: FRM-40501: ORACLE error: unable to reserve record for update or delete

This error message appears when the user while tries to update a record in Oracle eBusiness suite forms screen. Most of us knows the reason behind is that the same record is being updated by another user and the modifications have not been saved yet.

 

The below SQL can be used to list the details of the session which has locked any database object. You should be able to find the details of your record in this list.

 

WITH sessions_inline AS

 (SELECT client_identifier user_name

        ,(SELECT responsibility_name

          FROM   fnd_responsibility_vl

          WHERE  responsibility_key = SUBSTR(action

                                            ,INSTR(action

                                                  ,'/'

                                                  ,1

                                                  ,1) + 1)) responsibility

        ,DECODE(SUBSTR(module

                      ,INSTR(module

                            ,':'

                            ,1

                            ,2) + 1

                      ,INSTR(module

                            ,':'

                            ,1

                            ,3) - INSTR(module

                                       ,':'

                                       ,1

                                       ,2) - 1)

               ,'frm'

               ,'Forms Screen'

               ,'wf'

               ,'Workflow'

               ,'cp'

               ,'Concurrent Process'

               ,'fwk'

               ,'Self Service Page'

               ,'bes'

               ,'Business Event'

               ,'gsm'

               ,'Workflow') usage_type

        ,SUBSTR(module

               ,INSTR(module

                     ,':'

                     ,1

                     ,3) + 1) ebs_module

        ,

        

         ses.*

  FROM   v$session ses

  WHERE  1 = 1)

SELECT obj.object_name

      ,user_name

      ,responsibility

      ,usage_type

      ,ebs_module

      ,DECODE(usage_type

             ,'Forms Screen'

             ,DECODE(ebs_module

                    ,'FNDSCSGN'

                    ,'EBS Navigator'

                    ,(SELECT user_form_name FROM fnd_form_vl WHERE form_name = ebs_module))) forms_screen

      ,DECODE(usage_type

             ,'Concurrent Process'

             ,(SELECT user_concurrent_program_name FROM fnd_concurrent_programs_vl WHERE concurrent_program_name = ebs_module)) concurrent_program

      ,DECODE(usage_type

             ,'Concurrent Process'

             ,(SELECT user_concurrent_queue_name FROM fnd_concurrent_queues_vl WHERE concurrent_queue_name = ebs_module)) concurrent_manager

FROM   sessions_inline ses

      ,v$locked_object lck

      ,all_objects     obj

WHERE  lck.session_id = ses.sid

AND    obj.object_id = lck.object_id

ORDER  BY ses.usage_type;

Note

The lock on database does not necessirily mean that all records are locked. So there could be multiple locks on the same table.