[Workflow] Purge => WFERROR

 [Script] 

select wit.name "[WF] Name",

       wit.display_name "[WF] Display Name",

       wit.persistence_type "Persistence Type",

       decode(wi.end_date, null, 'OPEN', 'CLOSED') "Status",

       count(*) "Count"

from   wf_items_v       wi,

       wf_item_types_vl wit

where  wi.item_type = wit.name

and    wi.item_type = 'WFERROR'

group  by wit.name,

          wit.display_name,

          wit.persistence_type,

          wit.persistence_days,

          decode(wi.end_date, null, 'OPEN', 'CLOSED')

[Respond for open notifications]

SET serveroutput ON;

DECLARE

  vCommitCount    NUMBER := 1;

  vCommitInterval NUMBER := 5000;

  CURSOR curWF

  (

    pMessageType       IN VARCHAR2 DEFAULT 'WFERROR',

    pStatus            IN VARCHAR2 DEFAULT 'OPEN',

    pOriginalRecipient IN VARCHAR2 DEFAULT 'SYSADMIN',

    pDaysBefore        IN NUMBER DEFAULT 60

  ) IS

    SELECT notification_id

    FROM   wf_notifications

    WHERE  message_type = pMessageType

    AND    status = pStatus

    AND    original_recipient = pOriginalRecipient

    AND    begin_date < SYSDATE - pDaysBefore;

BEGIN

  FOR recWF IN curWF

  LOOP

    wf_notification.respond(nid => recWF.notification_id);

    IF vCommitCount = vCommitInterval THEN

      COMMIT;

      vCommitCount := 1;

    ELSE

      vCommitCount := vCommitCount + 1;

    END IF;

  END LOOP;

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    dbms_output.put_line(dbms_utility.format_error_stack);

END;

[Purge] 

--Concurrent : Purge Obsolete Workflow Runtime Data

[User] Create user by api

 DECLARE

  vEmpNum    hr_employees.employee_num%TYPE := 'xxx';

  vStartDate fnd_user.start_date%TYPE := SYSDATE;

  vEndDate   fnd_user.end_date%TYPE := NULL;

  vPassword  fnd_user.web_password%TYPE;

  vSessionId fnd_user.session_number%TYPE := userenv('sessionid');

  vEmp       hr_employees%ROWTYPE;

BEGIN

  SELECT *

  INTO   vEmp

  FROM   hr_employees he

  WHERE  he.employee_num = vEmpNum;

  vPassword := dbms_random.string('p', 10);

  fnd_user_pkg.CreateUser(x_user_name => vEmp.employee_num, x_owner => NULL,

                          x_unencrypted_password => vPassword,

                          x_session_number => vSessionId,

                          x_start_date => vStartDate, x_end_date => vEndDate,

                          x_employee_id => vEmp.employee_id);

  dbms_output.put_line('Password : ' || vPassword);

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line(dbms_utility.format_error_stack);

END;

Profile Script

SELECT fpo.profile_option_name SHORT_NAME,

         fpot.user_profile_option_name NAME,

         DECODE (fpov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 'UnDef')

            LEVEL_SET,

         DECODE (TO_CHAR (fpov.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.responsibility_key,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', fu.user_name,

                 'UnDef')

            "CONTEXT",

         fpov.profile_option_value VALUE

    FROM fnd_profile_options fpo,

         fnd_profile_option_values fpov,

         fnd_profile_options_tl fpot,

         fnd_user fu,

         fnd_application fap,

         fnd_responsibility frsp,

         fnd_nodes fnod,

         hr_operating_units hou

   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)

         AND fpo.profile_option_name = fpot.profile_option_name

         AND fu.user_id(+) = fpov.level_value

         AND frsp.application_id(+) = fpov.level_value_application_id

         AND frsp.responsibility_id(+) = fpov.level_value

         AND fap.application_id(+) = fpov.level_value

         AND fnod.node_id(+) = fpov.level_value

         AND hou.organization_id(+) = fpov.level_value

         AND fpot.user_profile_option_name IN ('FND: NATIVE CLIENT ENCODING')

ORDER BY short_name;

How can I get column names from a table in Oracle? (EBS)

參考網址

SELECT *

FROM   (SELECT 'DB' defineby,

               all_tab_columns.table_name,

               all_tab_columns.column_name,

               all_tab_columns.data_type,

               all_tab_columns.data_length

        FROM   all_tab_columns,

               all_tables

        WHERE  all_tab_columns.owner = all_tables.owner

        AND    all_tab_columns.table_name = all_tables.table_name

        AND    column_name = 'PO_HEADER_ID'

        

        UNION ALL

        SELECT 'EBS',

               tab.table_name,

               col.column_name,

               col.column_type,

               col.width

        FROM   fnd_tables  tab,

               fnd_columns col

        WHERE  tab.table_id = col.table_id

        AND    (col.column_name = 'PO_HEADER_ID' OR

              col.user_column_name = 'PO_HEADER_ID'))

WHERE  table_name LIKE '%PO_HEADERS_ALL%'

FND Tables for login details

參考網址

There are three primary tables which are involved in using the system profile ‘Sign-On:Audit Level’:

1) FND_LOGINS

2) FND_LOGIN_RESPONSIBILITIES

3) FND_LOGIN_RESP_FORMS

- When the profile is set to “User”, the only table that gets updated is the table FND_LOGINS and only one record per user session

- When the profile is set to “Responsibility”, both FND_LOGINS and FND_LOGIN_RESPONSIBILITIES will be updated:

a) FND_LOGINS gets only one record per user session

b) FND_LOGIN_RESPONSIBILITIES will be updated with one record for each responsibility selected during the session

- When the profile is set to “Form” all three tables are involved:

a) FND_LOGINS gets only one record per user session

b) FND_LOGIN_RESPONSIBILITIES will be updated with one record for each responsibility selected during the session

c) FND_LOGIN_RESP_FORMS will be updated with one record for each form selected during the session

In terms of performance, the data capture has negligible overhead.