DBMS Scheduler Schedule your Jobs from the database

How create job works.

BEGIN
   DBMS_SCHEDULER.create_job
                     (job_name             => 'XX_HR_TRAINING_PLANS',
                      job_type             => 'PLSQL_BLOCK',
                      job_action           => 'BEGIN XXHR_TRAINING_PLANS_HIST; END;',
                      start_date           => '07-Oct-11 05.52.00PM Asia/dubai',
                      repeat_interval      => 'FREQ=SECONDLY;INTERVAL=25',
                     -- end_date             => '15-SEP-08 1.00.00AM US/Pacific',
                      enabled              => TRUE,
                      comments             => 'XX Job Training Plans'
                     );
END;

Create Job can be done from apps user.

Purge Log:

BEGIN
DBMS_SCHEDULER.PURGE_LOG(WHICH_LOG => 'JOB_LOG',JOB_NAME => 'APPS.XX_HR_TRAINING_PLANS');
END;

Drop Job:

begin
 DBMS_SCHEDULER.drop_job('XX_HR_TRAINING_PLANS');
end;
Table to check the scheduled Job entry
select * from USER_SCHEDULER_JOBS or
select * from DBA_SCHEDULER_JOBS

 

How to extract more that one records from xml file

Following is the example where vacancy has two recruiters.

select x.*,x1.*
from hr_api_transactions t,
     xmltable('//PerAllVacanciesEORow'
              passing xmltype(transaction_document)
              columns requisition_id  number        path 'RequisitionId',
                      organization_id number        path 'OrganizationId',
                      name            varchar2(100) path 'Name',
                      creation_date   varchar2(30)  path 'CreationDate'                     
             ) x,
      xmltable('//IrcRecTeamMembersEORow'
              passing xmltype(transaction_document)
              columns PersonId  varchar2(10)        path 'PersonId'                                        
             ) x1
where t.transaction_ref_table = 'PER_ALL_VACANCIES'
and t.transaction_ref_id = 146

How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent

Goal

The Workflow mailer has not been running. Which may have caused  a large number of e-mail notifications to accumulate in the queue.  How does one prevent these from being sent when the mailer is started.

Solution

Please take a backup before making any of these changes and try this on a Test instance first.

1. Update the notifications you do not want sent, in the WF_NOTIFICATIONS table.  Check the WF_NOTIFICATIONS table. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';

This should show which notifications are waiting to be e-mailed.
One can use the BEGIN_DATE column to help narrow down the ones not to send if one only wants to stop the e-mails from a specific date range.

To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The
mailer will think the e-mail has already been sent and it will not send it again.. (Users can
still reply to the notification from the worklist page in the applications).

e.g. SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';
This will update all notifications waiting to be sent by the mailer.

2. Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

sqlplus usr/passwd@db @wfntfqup APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps@db @wfntfqup apps apps applsys

3. Now start the mailer.

Reference : Metalink Note : 372933.1

How to setup a forms function in R12 to launch an URL

First set the Profile 'Restricted Text Input' to 'No' for your User.

Then create a form function like below:

Functional Administrator > Core Services > Function > Create (button)

Name: XX_GOOGLE
Code: XX_GOOGLE
Type: SSWA jsp function
Maintenance Mode Support : none
Context Dependance: Responsibility
(press continue button)
HTML Call: javascript:void window.open("http://www.google.com")
Leave other fields blank

Add to HR_EMPLOYEE_DIRECT_ACCESS_V4.0 menu:

500 XX Google XX_GOOGLE

This will pop up your external webpage in a new window.

The issue is that it only works from the Navigator framework page (not from within Core Forms)

Cheerssss!!!

Bulk Password Change Script

After running below script, system will ask you to change the password.

begin
fnd_user_pkg.updateuser(
x_user_name => '204020779'
, x_owner => 'CUST'
, x_unencrypted_password => 'change123',
x_password_date => to_date('2','J')
);
commit;
end;

After running below script, system will not ask you to change the password.

BEGIN

FND_USER_PKG.UPDATEUSER(x_user_name            => 'teamsearch'

                       ,x_owner                => 'SEED'

                       ,x_unencrypted_password => 'oracle@123'

                       ,x_password_date        => SYSDATE + 500);

END;

Self - Service Persanalization Profiles

1. Personalize Self-Service Defn
2. FND: Personalization Region Link Enabled

Changing Hire Date of Employee

Scenario: Current emp data in the system has wrong DOJ, need to correct for all the employees:

CREATE TABLE XXHR_EMP_DOJ(PERSON_ID NUMBER,ORACLE_START_DATE DATE,ACTUAL_START_DATE DATE);
DECLARE
CURSOR cur_dataISSELECT papf.employee_number, dej.person_id,papf

.start_date oracle_start_date, dej.actual_start_dateFROM xxhr_emp_doj dej, per_all_people_f papfWHERE papf.person_id = dej.person_id;o_warn_ee
BEGINVARCHAR2 (20);
FOR i IN cur_dataLOOPBEGIN
hr_change_start_date_api.update_start_date(p_validate => FALSE,p_person_id => i.person_id,p_old_start_date => i.oracle_start_date,p_new_start_date => i.actual_start_date,p_update_type => 'E',p_applicant_number => NULL,p_warn_ee
=> o_warn_ee);DBMS_OUTPUT.put_line ( 'Success - Employee Number:'|| i
.employee_number);EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.put_line ( 'Error Employee Number:'|| i
|| .employee_number' Message -'|| SQLERRM);END;
END;END LOOP;