Bouncing Apache for OAF deployment in R12

To clear HTML cache and bounce Apache at R12 level :

1.
cd $INST_TOP/admin/scripts
dierctory is :
/global/oracle/your_sid/inst/apps/sid_server/admin/scripts

here are the scripts :
adalnctl.sh adexecsql.pl adoafmctl.sh adstrtal.sh jtffmctl.sh
adapcctl.sh adformsctl.sh adopmnctl.sh gsmstart.sh mwactl.sh
adautocfg.sh adformsrvctl.sh adpreclone.pl ieo mwactlwrpr.sh
adcmctl.sh adoacorectl.sh adstpall.sh java.sh

2.

adapcctl.sh stop

3.

To clear HTML cache :

cd $COMMON_TOP/_pages

Direcetory is :

/global/oracle/your_sid/apps/apps_st/comn/_pages

Here are the java classes to clear.

Do rm * in _pages directory after having checked there are only compiled classes in this directory

4.

cd $INST_TOP/admin/scripts
dierctory is :
/global/oracle/your_sid/inst/apps/sid_server/admin/scripts

adapcctl.sh start

Registering Discoverer Workbook in Oracle Applications R12

1) Create the workbook

2) Open the workbook in the Discoverer Desktop or Plus edition and go to
'File->Manage Workbooks->Properties' look for the value for 'Identifier'. Save this value.

3) Create a form function. The form function definition includes the properties listed in these tabs:

3.1 Description tab:
3.1.1 Function Name: XX_[FUNCTION_NAME] (it is accepted practice to identify customizations with an XX prefix)
3.1.2 User Function Name: This is the name that will show in the menu
3.1.3 Description: Add a description of the function if you want.

3.2 Properties tab:
3.2.1 Type : SSWA jsp function
3.2.2 Maintenance Mode Support: Leave as "None"
3.2.3 Context Dependence: Leave as "Responsibility"

3.3 Form tab:
3.3.1 Form: Leave the field blank.
3.3.2 Application: Leave the field blank.
3.3.3 Parameters: mode=DISCO&workbook=(workbook identifier from step2)&parameters=(Disco parameters name/values)

3.4 Web HTML tab:
3.4.1 HTML call : OracleOasis.jsp

3.5 Web Host tab:
3.5.1 Leave all fields blank.

3.6 Region tab:
3.6.1 Leave all fields blank.

3.7 Save the form.

4) Open the menu form as sysadmin.

4.1 Search for the main menu under which you want the link to appear.
4.2 Add the information you need such as prompt, submenu, description etc.
4.3 Enter into the Function field the name of the function you created in step 3.
4.4 Save the menu form.

A message will appear saying that a concurrent program will run to regenerate the menus.

5) Set below Profile options, if your End User Layer Name = EUL_US
ICX: Discoverer Default End User Layer Schema Prefix = EUL
ICX: Discoverer EDW End User Layer Schema Prefix = US

6) Bounce Apache and Forms.

Personalization of OAF Page LOV Column with Default Value

Personalization of OAF Page LOV Column with Default Value in Contracts Module

In the current example, we will see how to personalize the Contracts -> Contracts -> Create contract OAF page from the responsibility Contracts Workbench Administrator.

The actual screen looks like below:


We would like to change the column Organization to a Constant / Hardcoded value as ‘Vision Operations’
Let us perform the following steps in achieving the task:

1. Enable the following profile values to YES
Personalize Self-Service Defn – Yes
FND:OA:Enable Defaults – Yes

2. Click on the Personalize Page from the above visible contracts screen

3. Select Complete View and Press Expand All



4. Click on the TORCH Icon available in Personalize Tab, for the Message LOV Input : Organization column.





5. You view the above screen, and change the value for Initial Value, Default to ‘Vision Operations’ at all the 4 levels
Function, Site, Organization and Responsibility

6. Now Apply the changes and relogin to the Contract Screen



You the see the above screen with default value ‘Vision Operations’ for the Organization column.

But the users can navigate to the column, and they can change the default value to something else.

Inorder to restrict the Users to enter to the Organization column and maintain the same value, we need to Personalize the above page again.

Click on Personalize Page, Complete View and Expand All, and Click on Personalize Torch for Message LOV Input : Organization



Change the Value for Read Only to TRUE at Function Level (here you can change the value as per the client requirement)



Save the changes and relogin to the Contracts Page.



You could see the Organization column value ‘Vision Operations’ and the column Organization is restricted to user entry.

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!!!