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)