How to delete records from wf_notifications table

DECLARE

CURSOR csr_transaction_id IS
SELECT   hat.transaction_id,
         hat.item_type,
         hat.item_key,
         ppx.employee_number,
         hat.section_display_name
  FROM   hr_api_transactions hat, per_people_x ppx
 WHERE   hat.process_name = 'HR_PERSONAL_INFO_JSP_PRC'
         AND hat.selected_person_id = ppx.person_id
         AND ppx.employee_number IN
                  ('100024773',
                   '100024820',
                   '100024859',
                   '100024879',
                   '100024902',
                   '100024937',
                   '100025137',
                   '100026470',
                   '610014755',
                   '610017039')
 order by  ppx.employee_number;
BEGIN
  dbms_output.put_line('***Deleted all Transactions  and Notifications of below Employee Personals Tranactions ***'); 
   FOR my_cur_v IN csr_transaction_id
   LOOP
   /*Delete all Transaction_id's in hr_api_transactions,hr_api_transaction_steps,hr_api_transaction_values and hr_api_transaction_steps_bk tables */
    hr_transaction_swi.delete_transaction
                          (
                          p_transaction_id =>my_cur_v.transaction_id,
                          p_validate => hr_api.g_false_num
                          );
                                   

    wf_engine.abortprocess  (
                              itemtype => my_cur_v.item_type,
                             itemkey => my_cur_v.item_key
                             );
     /* Deleted all Notification_id's and item_key's in wf_item_activity_statuses,wf_items,wf_item_attribute_values,wf_notifications     Table */                      
     wf_purge.items (
                      itemtype => my_cur_v.item_type,
                     itemkey => my_cur_v.item_key
                          );                                    
    dbms_output.put_line('Emp No --'||my_cur_v.employee_number||'Transaction_id :'||my_cur_v.transaction_id||'Emp Personal Info :'||my_cur_v.section_display_name||
                          'Item Type :'||my_cur_v.item_type|| 'Item Key :'||my_cur_v.item_key);  
                               
   END LOOP;
   commit;
EXCEPTION 
WHEN OTHERS THEN
  dbms_output.put_line('hr_transaction_swi.delete_transaction api goest to exception block'    ||sqlcode|| '  '||sqlerrm);
END;   
/

Retrieve active directory information from Oracle database using LDAP protocol

Here is an example of code to retrieve information from Active Directory, it will retrieve 2 attributes -email and employee number- from server. The reason for an alphabet loop is because Active Directory has an 1000 records limit and my original script recieved an error:

ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded

I use this alphabet loop so I split records into 26 parts, which avoid the returned records exceed 1000 records. You don't have to do that if your AD team can increase the limit in their side. There is a concept of "paged" result sets in LDAP, but I don't see mention of that functionality in the DBMS_LDAP documentation.

______________________________________________________________________________

declare

--credentials

  l_ldap_host    VARCHAR2(256) := 'ldap.myserver.domain';

  l_ldap_port    VARCHAR2(256) := '1234';

  l_ldap_user    VARCHAR2(256) := 'username';

  l_ldap_passwd  VARCHAR2(256) := 'password';

  -- AD server has limit for 1000 records, I use a loop to go through 26 letters to get small group of people

  --to do this, I assume all employees' email address start with a letter, AD query is NOT case sensitive

  l_alphabet    varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

  l_retval       PLS_INTEGER;

  l_session      DBMS_LDAP.session;

  l_attrs        DBMS_LDAP.string_collection;

  l_attr_empno  varchar2(100) := 'empno';

  l_attr_email varchar2(100) := 'email';

  l_message      DBMS_LDAP.message;

  l_entry        DBMS_LDAP.message;

  l_attr_name    VARCHAR2(256);

  l_ber_element  DBMS_LDAP.ber_element;

  l_vals         DBMS_LDAP.string_collection;

  l_empnum   varchar2(256);

  l_email   varchar2(256);

begin

    /*connect to ldap server*/

    dbms_output.put_line('Start loading records from Active Directory server');

    DBMS_LDAP.USE_EXCEPTION := TRUE;

    l_session := DBMS_LDAP.init(hostname => l_ldap_host,

                                portnum  => l_ldap_port);

    l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session, ---if success, it = 0

                                        dn     => l_ldap_user,

                                        passwd => l_ldap_passwd);

        /*if connect successfully*/

    l_attrs(1) := l_attr_empno;

    l_attrs(2) := l_attr_email;

    FOR alphaindex in 1..26 LOOP

        l_retval := DBMS_LDAP.search_s(ld       => l_session,

                                 base     => 'OU=MyOrg,DC=ldap,DC=myserver,DC=domain',

                                 scope    => DBMS_LDAP.SCOPE_SUBTREE,

                                 filter   => '(&(objectclass=user)(objectclass=top)('||l_attr_email||'='||substr(l_alphabet,alphaindex,1)||'*))',

                                 attrs    => l_attrs,

                                 attronly => 0,

                                 res      => l_message);

        IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN

        -- Get all the entries returned by our search.

            l_entry := DBMS_LDAP.first_entry(ld  => l_session, msg => l_message);

        --<< entry_loop >>

            WHILE l_entry IS NOT NULL LOOP

                -- Get all the attributes for this entry.

                l_empnum:=null;

                l_email:=null;

                l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,

                                                       ldapentry => l_entry,

                                                       ber_elem  => l_ber_element);

                --<< attributes_loop >>

                WHILE l_attr_name IS NOT NULL LOOP

                    -- Get all the values for this attribute.

                    l_vals := DBMS_LDAP.get_values (ld        => l_session,

                                                    ldapentry => l_entry,

                                                    attr      => l_attr_name);

                    if l_attr_name = l_attr_empno then

                        l_empnum := l_vals(l_vals.FIRST);

                    elsif l_attr_name = l_attr_email then

                        l_email := l_vals(l_vals.FIRST);

                    end if;

                    --get next attribute

                    l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,

                                                            ldapentry => l_entry,

                                                            ber_elem  => l_ber_element);

                END LOOP attibutes_loop;

                --only the value that is associated to an employee number will be considerred valid record

                if l_empnum is not null then

                    --process database tasks

                end if;

                l_entry := DBMS_LDAP.next_entry(ld  => l_session,

                                              msg => l_entry);

            END LOOP entry_loop;

      END IF;

  end loop;

  commit;

  -- Disconnect from the LDAP server.

  l_retval := DBMS_LDAP.unbind_s(ld => l_session);

  dbms_output.put_line('Complete loading records from Active Directory server');

end;

_______________________________________________________________________________

jazn-data.xml for webcenter

The system-jazn-data.xml is an XML file which is configured by the user to use as an ID store and/or policy store. The file is located in $DOMAIN_HOME/config/fmwconfig.

Sample system-jazn-data.xml file:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<jazn-data>
  <jazn-realm>
    <realm>
      <name>jazn.com</name>
        <users>
          <user>
            <name>weblogic</name>
            <guid>23AAB190021911DDBF86C74F01C202FB</guid>
            <credentials>PN0Qr+/dpDRV+jSWP378EdjxWDS0PuAs=</credentials>
          </user>
          </users>
      </realm>
    </jazn-realm>
    <policy-store>
      <applications>
        <application>
          <name>reports</name>
            <app-roles>
              <app-role>
                <name>rw_administrator</name>
                <display-name>Reports Administrator</display-name>
                <class>oracle.security.jps.service.policystore.ApplicationRole</class>
                <members>
                  <member>
                    <class>oracle.security.jps.internal.core.principals.JpsXmlUserImpl</class>
                    <name>weblogic</name>
                  </member>
                  <member>
                    <class> weblogic.security.principal.WLSUserImpl </class>
                    <name>weblogic</name>
                  </member>
                </members>
              </app-role>
              <app-role>
                <name>rw_operator</name>
                <display-name>Reports Operator</display-name>
                <class>oracle.security.jps.service.policystore.ApplicationRole</class>
              </app-role>
              <app-role>
                <name>rw_monitor</name>
                <display-name>Reports Monitor</display-name>
                <class>oracle.security.jps.service.policystore.ApplicationRole</class>
              </app-role>
            </app-roles>
            <jazn-policy>
              <grant>
              <grantee>
                <principals>
                  <principal>
                    <class>oracle.security.jps.service.policystore.ApplicationRole</class>
                    <name>rw_administrator</name>
                  </principal>
                </principals>
              </grantee>
              <permissions>
                <permission>
                  <class>oracle.reports.server.ReportsPermission</class>
                  <name>report=* server=* destype=* desformat=* allowcustomargs=true</name>
                  <actions>*</actions>
                </permission>
                <permission>
                  <class>oracle.reports.server.WebCommandPermission</class>
                  <name>webcommands=* server=*</name>
                  <actions>execute</actions>
                </permission>
              </permissions>
            </grant>
            <grant>
              <grantee>
                <principals>
                  <principal>
                    <class>oracle.security.jps.service.policystore.ApplicationRole</class>
                    <name>RW_BASIC_USER</name>
                  </principal>
                </principals>
              </grantee>
              <permissions>
                <permission>
                  <class>oracle.reports.server.ReportsPermission</class>
                  <name>report=test.rdf server=* destype=* desformat=* allowcustomargs=true</name>
                  <actions>*</actions>
                </permission>
                <permission>
                  <class>oracle.reports.server.WebCommandPermission</class>
                  <name>webcommands=showmyjobs,getjobid,showjobid server=*</name>
                  <actions>execute</actions>
                </permission>
              </permissions>
            </grant>
          </jazn-policy>
        </application>
</applications>
</policy-store>

Workflow related tables and basic on how fix HR related workflow issue

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modelled workflow process, which can be included as an activity in other processes to represent a sub process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.

The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.

hr_api_transactions is a place to hold the HR workflows in process

To remove a workflow do the following
execute hr_transaction_api.rollback_transaction( p_transaction_id => 3350154, p_validate => false);
commit;
execute wf_engine.abortprocess  (itemtype => 'HRSSA',  itemkey => '235642');
commit;
execute wf_purge.items (itemtype => 'HRSSA',itemkey => '235642' );
commit;

Find true field start/end date from date tracked tables like per_people_f, per_assignments_f

There's time you want to know what are the start date/end date an employee stay within a job, sometimes it is not an easy task because an employee may have multiple assignments records because of org change, supervisor change or so, even though all assignments records have same job. Here is a way to find out what is the true job (or other fields) start/end date which is using analytic function. 

Here is the example of data in Assginments table with a group id. The group id is a date, it is calculated using This analytic function: 

--+++++++++++++++++++++++++++++++++++++++++++++
effective_end_date-sum(effective_end_date-effective_start_date+1) over(partition by person_id, job_id order by effective_start_date) +1 group_id

--+++++++++++++++++++++++++++++++++++++++++++++


id        job_id    date_start    date_end      group_id

814    12219    8/22/2005    3/31/2006    8/22/2005

814    12579    7/1/2007    9/30/2007    7/1/2007

814    12579    10/1/2007    10/21/2007    7/1/2007

814    12579    10/22/2007    3/31/2008    7/1/2007

814    12579    4/1/2008    1/7/2010    7/1/2007

814    12579    1/8/2010    2/21/2010    7/1/2007

814    12579    2/22/2010    5/31/2010    7/1/2007

814    12579    6/1/2010    6/23/2010    7/1/2007

814    12579    6/24/2010    3/31/2012    7/1/2007

814    12579    4/1/2012    5/6/2012    7/1/2007

814    12579    5/7/2012    10/1/2013    7/1/2007

814    12579    5/5/2014    12/31/4712    2/1/2008

814    12622    4/1/2006    3/31/2007    4/1/2006

814    12622    4/1/2007    4/5/2007    4/1/2006

814    12623    4/6/2007    6/30/2007    4/6/2007

814    12623    2/18/2014    5/4/2014    11/24/2013


Please pay attention to that the same job ids 12579 is in different groups, one group is 7/1/2007 and another is 2/1/2008. The group id does not mean a real date, it is just the result of a calculation and is used to differenciate job range. 

As soon as you generate the list, you can now using group by to generate the range of each job group: 

814    12219    8/22/2005    3/31/2006
814    12622    4/1/2006    4/5/2007

814    12623    4/6/2007    6/30/2007

814    12579    7/1/2007    10/1/2013

814    12623    2/18/2014    5/4/2014
814    12579    5/5/2014    12/31/4712


Here is the query: 

select person_id,job_id,min(effective_start_date) job_start_date, max(effective_end_date) job_end_date

from    (select     person_id, job_id, effective_start_date, effective_end_date

                       , effective_end_date-sum(effective_end_date-effective_start_date+1) over (partition by person_id, job_id order by effective_start_date) +1 group_id

            from     per_assignments_f

            where   assignment_status_type_id in

                                   --only pick up those assignments active assignments

                                                 (1 --Active Assignment

                                                 ,2 --Active Contingent Assignment

                                                 ,3 --Leave with Pay Assignment

                                                 ,4 --Leave without Pay Assignment

                                                 ,5)

             and person_id=814

             )TMP

group by person_id,job_id,group_id