Useful Workflow scripts

WF_WORKLIST_V

WF_WORKLIST_V is a view of WF_NOTIFICATIONS that is used to display messages in a work list.

View Definition:

CREATE VIEW WF_WORKLIST_V

AS

Readily available Oralce apps SQL scripts under $AD_TOP/sql

Oracle Applications SQL scripts

Oracle Applications has useful collection of ready SQL scripts under $AD_TOP/sql. The following list shows the description of each script. For more details like if a specific script is available on a specific Apps version you need to check metalink note 108207.1 you will find the script and its description below.

Query To get List of Employees which has Supervisors

SELECT ppf.full_name emp_name
      ,ppf1.full_name supervisor_name
FROM per_all_people_f ppf
,per_all_assignments_f paaf
,per_all_people_f ppf1
WHERE 1=1
AND ppf.person_id=paaf.person_id
AND ppf.employee_number IS NOT NULL
AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.supervisor_id=ppf1.person_id
ORDER BY 1

APPEND THE DATE AND TIME TO A SQL*PLUS OUTPUT FILENAME

APPEND THE DATE AND TIME TO A SQL*PLUS OUTPUT FILENAME
======================================================

    If you've got an output file that you SPOOL to regularly from SQL*Plus,
but you don't want to overwrite the file, you can uniquely identify it by
appending the date and time to the filename.  Here's an example:

    column date_column new_value var1
    select to_char (sysdate, 'YYYY-MM-DD-HH24MISS') || '.txt' date_column
    from   dual
    /
    spool c:\temp\filename-&var1

        < stuff you want to spool >

    spool off