Debugging made easy with Autonomous Transaction

Autonomous transaction is a very useful feature of PL/SQL, specially when debugging apps issues. 
At times, traditional apps logging like GMFZT log, FND log etc.. may not be of much help.
Not many debugging statements in your flow is one such example.

You can use the autonomous transaction with the code sample as given below.

Pros:

• Won't disrupt the flow.
• No change to transactional data that is waiting for commits in the flow.
• Logs are inserted into table, so simple to see the log
• Best method to know variable values at a specific point in code

Cons:

• Has to modify the Package/procedure/function and recompile, which sometimes invalidate the dependent packages.

Code:
Run the following code only once to create the required table/sequence/procedure

CREATE TABLE oradebug_tbl 
(id NUMBER, 
message VARCHAR2(1000), 
edate DATE); CREATE SEQUENCE oradebug_seq 
START WITH 1 INCREMENT BY 1; CREATE OR REPLACE PROCEDURE insert_into_oradebug
(message IN VARCHAR2) AS pragma autonomous_transaction; BEGIN INSERT INTO oradebug_tbl VALUES (oradebug_seq.NEXTVAL, message,SYSDATE); COMMIT; END;

Sample Usage:

            DECLARE 

             l_temp date; 

     BEGIN 

             l_temp := TRUNC(SYSDATE); 

             insert_into_oradebug('Value of l_temp='||l_temp); 

     END;

     / 

Check the log:

SELECT * 
     FROM oradebug_tbl

ORDER BY id DESC

RetroPay Series - Basics & Evaluation

In a nutshell, RetroPay manages back-dated or late notifications of changes to payroll processing.

Technically, Retropay will bring forward the late adjusted entries of payroll runs.
It make
- Accurate re-calculations
- Bring forward the delta payments or deductions in the current pay period

Examples of RetroPay Scenarios
1) Monthly Bonus declared in middle of the year, for the entire year.
2) RetroActive Promotion, salary increase or absence details

Evaluation of RetroPay Process

• RetroPay (by Aggregate)
• Standard UK process until 11i, now Obsolete
• RetroPay by Run
• Japanese Market Only, now Obsolete
• RetroPay by Element
• Current supporting version by Oracle in R11i, R12.0, R12.1, Obsolete in R12.2.
• Optional RetroNotification Report to view the assignments to be processed.
• Enhanced Features compared to earlier versions (correction/back dated awards)
• Can process one assignment or a group, using an Assignment Set.
• New from HR_PF.H
• "Start date" from which assignments needs to be back-payed should be provided by user and it is based on assignment set. In case of uneven back dates awards, or in case of larger assignment sets, this leads to performance issues.
•  RetroPay (Enhanced)
• A step forward to automatic RetroPay
• Current Supporting versions R11i, R12
• Addresses the main performance problem which exists with RetroPay by Element
• Mandatory to run RetroNotification Report, which picks up the assignments to be processed and also calculate the date from which each assignment to be back-payed.
•  QuickRetroPay 
• Retropay for single assignment, currently supported as concurrent program.
• Inspired by QuickPay
• Available from R12.1 RUP5 and R12.2.
• RetrNotification is automatically run by this process, so no need to run it explicitly.

What is Retro Notification?

1. It is the process which checks, if historical entries have been changed.
2. It will decide, the list of assignments for whom RetroPay should be run
3. Retro Notification works on Event Model in Oracle apps. 
4. List of events to be tracked should be included in event group and attach it to element.
5. For RetroPay by Element, Retro Notification is optional and it works just as reporting tool. But for RetroPay (enhanced), it is mandatory to run Retro Notification.

Following example explains the way RetroPay works.
It shows a sample calculation of, how it internally calculates and populates the results.

Enable FND Logging

Follow the steps to enable FND Logging.


 A. Set the system profiles 
          FND: Debug Log Enabled -> Yes 
     FND: Debug Log Level   -> Statement 
     FND: Debug Log Module  -> % 

  B. Submit Create Accounting, or any process which has FND log statements.

  C. Run the following SQL after completion of CP using the CP request id 

       SELECT log_sequence, module,message_text 
       FROM fnd_log_messages 
       WHERE transaction_context_id in 
            (select transaction_context_id 
             from fnd_log_transaction_context 
             where transaction_id = <CP request id>) 
       ORDER BY log_sequence; 

Useful Tips for Technical Consultants

This is a placeholder for miscellaneous tips that are specially useful for apps technical consultants.

1) Useful queries to better know specific file and patch versions on an instance

select * from ad_bugs where bug_number=5356467;

select * from ad_files where filename='pyautfn.pkb';

select * from ad_file_versions where file_id=78626;

2) Query to know the list of routes that are using a specific balance dimension
      SELECT *
     FROM ff_routes
WHERE route_id IN (
 SELECT pdr.route_id
FROM pay_balance_dimensions pbd, 
               pay_dimension_routes pdr
       WHERE pbd.dimension_name =
'Assignment within Government Reporting Entity Inception To Date'
       AND pbd.legislation_code = 'US'
      AND pdr.balance_dimension_id = pbd.balance_dimension_id);

3) How to kill existing idle database sessions

  ALTER SYSTEM DISABLE RESTRICTED SESSION;
  /
  BEGIN   
FOR x IN (  
 select Sid, Serial#, machine, program  
 from v$session  
 where  
  STATUS <> 'ACTIVE'  
) LOOP  
execute immediate 'Alter System Kill Session '''|| x.Sid  
|| ',' || x.Serial# || ''' IMMEDIATE';  
END LOOP;  
  END; 
  /
  COMMIT;

4) How to gather Table statistics for a specific table.
begin 
dbms_stats.gather_table_stats('HR','PER_ALL_ASSIGNMENTS_F');
end;

To verify whether a Table is Analyzed or not
SELECT last_analyzed analyzed, sample_size, 
              monitoring, table_name
FROM dba_tables
WHERE table_name='PER_ALL_ASSIGNMENTS_F';

For a specific high percentage gather stats
exec fnd_stats.gather_table_stats('HR','PER_ALL_ASSIGNMENTS_F',40);

5) How to find out internal query that is being executed for a Database Item in payroll (DBIs)
SELECT 'SELECT ' || definition_text || ' FROM ' AS select_clause_text,
  text AS from_clause_text
 FROM ff_database_items fdi, ff_user_entities fue, ff_routes fr
WHERE fdi.user_name = 'NSW_STATE_TAX_RATE'
  AND fue.user_entity_id = fdi.user_entity_id
  AND fr.route_id = fue.route_id;

6) How to see for existing DB pipes
SELECT * FROM v$db_pipes;

To remove a specific DB pipe
DECLARE
        v_pipe_rm NUMBER;
BEGIN
         DBMS_PIPE.PURGE('PID95');
   v_pipe_rm := DBMS_PIPE.REMOVE_PIPE('PID95');
         DBMS_PIPE.RESET_BUFFER;
END;

7) How to know the list of indexes and index columns on a specific table.
              SELECT * FROM all_indexes
      WHERE table_name='PER_ALL_ASSIGNMENTS_F'

      SELECT * FROM all_ind_columns
  WHERE table_name='PER_ALL_ASSIGNMENTS_F'

8) Scripts to 
Bounce all the services of a running instance
R11i
To Stop ->  $COMMON_TOP/admin/scripts/adstpall.sh apps/<apps_passwd>
To Start -> $COMMON_TOP/admin/scripts/adstrtal.sh apps/<apps_passwd>
R12
To Stop ->  $INST_TOP/admin/scripts/adstpall.sh apps/<apps_passwd>
To Start -> $INST_TOP/admin/scripts/adstrtal.sh apps/<apps_passwd>

Bounce only Middle tier Services
R11i
To Stop ->  $COMMON_TOP/admin/scripts/adopmnctl.sh stopall
To Start -> $COMMON_TOP/admin/scripts/adopmnctl.sh startall
R12 
To Stop ->  $INST_TOP/admin/scripts/adopmnctl.sh stopall
To Start -> $INST_TOP/admin/scripts/adopmnctl.sh startall

9) Debug statement in fast formula, this debug statment is then visible in GMFZT log of payroll run.
x=IN_DEBUG('prorate_start',TO_CHAR(prorate_start))

10) How to generate formatted tkprof from a raw trace file.
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>@instance sort='(prsela,exeela,fchela)'

11) How to upload a payroll formula from Unix

     $FF_TOP/bin/FFXMLC apps/pwd@dbname 0 Y LOCAL pyffupload.hct

  Compile the uploaded formula

     $FF_TOP/bin/FFXBCP apps/pwd@dbname 0 Y %% <Formula Name>

12) Query to check if Concurrent manager is running or down.
        SELECT    'The ICM is Running on '
                  || node_name || '   -  since '
                  || TO_CHAR (last_update_date, 'MON/DD/YY HH:MI:SS AM') "CM Details"
        FROM fnd_concurrent_processes, fnd_v$process
        WHERE (os_process_id = spid 
        AND oracle_process_id = pid)
        AND (concurrent_queue_id = 1 
        AND queue_application_id = 0)

Backtrack FF Route name from route text

Sometimes, there can be a need to find out the FF route name given the route query.
This is the usual case while debugging Oracle Payroll performance issues.
If the expensive query that is found from the raw/tkprof formatted trace files is 
route query, it needs to be backtracked to find the FF route name.

FF Route text is stored in "text" column of ff_routes_f table.
But it's a long column, so it can't be matched with LIKE keyword.

Using the following code snippet a long column can be searched with LIKE keyword.

create global temporary table pv_test_temp(route_id number, text clob);

insert into pv_test_temp
select route_id,TO_LOB(text) text from ff_routes;

select * from ff_routes where route_id in 
(select route_id from pv_test_temp where text LIKE '%PBF%')

Useful queries in debugging Payroll issues

Few queries that are useful in debugging Payroll or Retropay issues in HRMS.

1) To get all the actions for an assignment
     SELECT   paa.assignment_id, ppa.payroll_id, ppa.business_group_id,
         hrl.meaning, ppa.action_status, ppa.effective_date, ppa.date_earned,
         ppa.payroll_action_id, paa.assignment_action_id, paa.action_status,
         paa.action_sequence, paa.source_action_id, paa.run_type_id
    FROM pay_assignment_actions paa, pay_payroll_actions ppa, hr_lookups hrl
   WHERE paa.payroll_action_id = ppa.payroll_action_id
     AND ppa.action_type = hrl.lookup_code
     AND hrl.lookup_type = 'ACTION_TYPE'
     AND paa.assignment_id = <<assignment_id>>
ORDER BY paa.action_sequence DESC;


2) To get all the Element Entries for an Assignment
    SELECT DISTINCT pet.element_name, pet.element_type_id, piv.NAME input_value,
                piv.input_value_id, pee.element_entry_id, pee.creator_type,
                pee.entry_type, pev.element_entry_value_id,
                pev.screen_entry_value, pev.effective_start_date,
                pev.effective_end_date, pee.creator_id, pee.source_id,
                pee.source_asg_action_id, pee.source_start_date,
                pee.source_end_date
           FROM pay_element_types_f pet,
                pay_input_values_f piv,
                pay_element_entries_f pee,
                pay_element_entry_values_f pev
          WHERE pee.assignment_id = <<assignment_id>>
            AND piv.element_type_id = pet.element_type_id
            AND pee.element_type_id = pet.element_type_id
            AND pee.element_entry_id = pev.element_entry_id
            AND pev.input_value_id = piv.input_value_id
       ORDER BY 1, 6, 5;


3) To get Run Results for an Assignment
     SELECT DISTINCT pet.element_name, piv.NAME, ppa.action_type,
                ppa.effective_date payroll_run_date, 
                paa.assignment_action_id,
                ppa.payroll_action_id, 
                prr.run_result_id, prv.result_value,
                prr.entry_type, prr.source_id, prr.source_type,
                prr.start_date, prr.end_date, prr.element_entry_id,
                prv.formula_result_flag
           FROM pay_element_types_f pet,
                pay_input_values_f piv,
                pay_run_results prr,
                pay_run_result_values prv,
                pay_assignment_actions paa,
                pay_payroll_actions ppa
          WHERE paa.assignment_id = <<assignment_id>>
            AND paa.payroll_action_id = ppa.payroll_action_id
            AND paa.assignment_action_id = prr.assignment_action_id
            AND prr.run_result_id = prv.run_result_id
            AND prv.input_value_id = piv.input_value_id
            AND pet.element_type_id = prr.element_type_id
            AND piv.element_type_id = pet.element_type_id
            AND ppa.action_type IN ('Q', 'R', 'V', 'B')
       ORDER BY 1, 2, 3, 4, 9, 10;