TriniDBA

Problem: The following code runs in SQL Developer and SQL*Plus from the server:

SELECT person_name,
  employee_number,
  date_from,
  date_to,
  segment_details
FROM apps.hrfg_detailed_special_info
WHERE to_date('&1', 'RRRR/MM/DD HH24:MI:SS') BETWEEN date_from AND date_to
 AND information_type = 'ABC Full Time Payroll Remarks'
 AND segment_name = 'Remarks'
ORDER BY person_name
/

However, when it is registered as a concurrent program and run, no output appears.

Solution: The apps_initialize subroutine must be called before running the script.

BEGIN
  fnd_global.apps_initialize(0, 50272, 800);
END;
/

The entire script appears as follows:

SET echo OFF
SET head ON
SET feedback OFF
SET linesize 1000
SET trimspool ON
SET pagesize 10000

SET verify off
WHENEVER oserror EXIT failure
ROLLBACK;
WHENEVER SQLERROR EXIT failure
ROLLBACK;

COL person_name FORMAT a60
COL employee_number FORMAT a20
COL date_from FORMAT a15
COL date_to FORMAT a15
COL segment_details FORMAT a120

BEGIN
  fnd_global.apps_initialize(0, 50272, 800);
END;
/

SELECT person_name,
  employee_number,
  date_from,
  date_to,
  segment_details
FROM apps.hrfg_detailed_special_info
WHERE to_date('&1',   'RRRR/MM/DD HH24:MI:SS') BETWEEN date_from AND date_to
 AND information_type = 'ABC Full Time Payroll Remarks'
 AND segment_name = 'Remarks'
ORDER BY person_name
/

EXIT;

Note that you must populate the parameters for the apps_initialize routine with values from your environment. This script will help extract the values required for the routine:

SELECT fnd.user_id,
  fresp.responsibility_id,
  fresp.application_id,
  fresp.responsibility_name
FROM fnd_user fnd,
  fnd_responsibility_tl fresp
WHERE fnd.user_name = 'SYSADMIN'
 AND fresp.responsibility_name LIKE 'ABC%'
ORDER BY fresp.responsibility_name;