Alets

You can use alerts simply to notify one person/multiple persons about an event or some data change. There are two type of alerts

  • Event based Alerts
  • Periodic Alerts

Event based Alerts: These Alerts are fired/triggered based on some change in data in the database.

Periodic Alerts: These Alerts are triggered hourly, daily, weekly, monthly or yearly based on your input.

What can be done with Alerts?

  • You can send notifications
  • You can send log files as attachments to notifications
  • You can call PL/SQL stored procedures.
  • You can send approval emails and get the results.
  • Print some content dynamically

Navigation:

  • Go to “Alert Manager” Responsibility
  • Alert >> Define

Transfer Alert from one instance/database to other:

  • Go to Alert Manager Responsibility
  • Alert >> Define
  • Go to “Tools” Menu on top
  • Click on “Transfer Alert”
  • Enter source and destination fields and click Transfer.

alert1

Defining an Alert :

Responsibility  –> Alert Manager.

Navigation

Alert –> Define

Define a Period Alert:

Application = Human Resources

Name =  xxscc_test1

Enable = Selected.

alert2

Event Alert

Application  = Human Resources

Table  = PER_ALL_PEOPLE_F

Enter the following SQL statement.

select employee_number

into &emp_num

from per_all_people_f

where rowid=:rowid

alert3

Verify, to check whether the syntax written is correct.

Save

Define Alert Actions:

After you define your alert you need to create the actions you want your alert to perform.There are four types of actions you can create:
• message actions
• concurrent program actions
• operating script actions
• SQL statement script actions

Action Name :  send_mail   (up to 80 characters)

Select a level for your action:Detail, Summary, or No Exception.
During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.

alert4

Define Action Details: 

Click on Action Details Button. Depending upon the action level and type you choose, different fields appear in the Action Details window.

TO :   Give you email ID here.      Note : You can also put here also bind variable with &email if that taken in select statement.

Subject :  Give Subject of you Email.

Text :  hello this is test email &emp_num

alert5

Define Action Sets

You can have multiple actions that follow one after the other. For this you need to define an Action Set that defines the flow of these actions.

Click on Action Sets Button.

Action Set Name =  Send Email Test

Go to Action Set Details –> Members.

Action =  send_email_notification  as shown bellow.

alert6

Save.

Now if you Update or Create New Employee you will received notification in given email ID.

Now comes the question, how do we test a  Periodic Alert?

Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time. It will submit a concurrent program.

Once concurrent program is successfully ran, we can verify the number of exception from

Alert Manager -> History and query for alert.

Query for your alert. If status is Complete it means that the alert is working.

Advertisements

SQL* Loader concurrent program

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle Database.

You can load data into an Oracle database by using the sqlldr command in UNIX.

sqlldr username@server/password control =loader.ctl data=data_file.csv

loader.ctl is the control file.

Following is the example of control file that needs to be  placed at the $XX_TOP/bin directory

Options (skip=1)
LOAD DATA
INFILE ‘/u02/oracle/VIS/apps/apps_st/appl/per/12.0.0/bin/data_file.csv’
APPEND
INTO TABLE XX_EMP_STG
fields terminated by “,” optionally enclosed by ‘”‘
TRAILING NULLCOLS          
(
TITLE,
FIRST_NAME,
HIRE_DATE,
PERSON_TYPE,
BUSINESS_GROUP_ID,
LAST_NAME,

–Tracking Columns
Last_Updated_By “FND_GLOBAL.USER_ID”,
Last_Update_Date SYSDATE,
Created_By “FND_GLOBAL.USER_ID”,
Creation_Date SYSDATE,
Last_Update_Login “FND_GLOBAL.LOGIN_ID”
)

Open the MS-Excel spreadsheet and save it as a CSV (Comma Separated Values) file. This file can now be copied to the Oracle machine and loaded using the SQL*Loader utility.

Possible problems and workarounds:

The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor):

' Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
 Dim TheCell As Range
 On Error Resume Next

 For Each TheCell In ActiveSheet.UsedRange
   With TheCell
     If .HasFormula = False Then
       .Value = Application.WorksheetFunction.Clean(.Value)
     End If
   End With
 Next TheCell
End Sub

Executable

exe

 

Note that the extension .ctl is not entered in the executable form.

The control file emp_ctl.ctl file should exist in $XXCUST_TOP/bin directory (XXCUST_TOP corresponds to Custom Applications).

Concurrent Definition:

proc

 

Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure’

 

1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));

2. Insert Values

INSERT INTO demo_products
     VALUES (100, ‘TEST DATA’);
3.Commit

1. Create a Package Spec & Body with a single Procedure

Spec:

CREATE OR REPLACE PACKAGE APPS.XX_REPORT_PKG

AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);
END MY_PACKAGE;

 

Body:

CREATE OR REPLACE PACKAGE BODY APPS.XX_REPORT_PKG
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32000);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := ‘SELECT product_code, product_name
         FROM demo_products
       WHERE product_code = ‘ || p_product_id;

      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      — set rowset tag to PRODUCTS and row tag to PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, ‘PRODUCTS’);
      DBMS_XMLGEN.setRowTag (l_qryCtx, ‘PRO_DETAILS’);

      — now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, ‘No of rows processed= ‘ || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, ‘Length= ‘ || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length – l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, ‘Error in procedure XX_REPORT_PKG.report’);
   END REPORT;
END XX_REPORT_PKG;

2. Define Executable

Navigation: Application Developer > Concurrent > Executable

Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: XX_REPORT_PKG.REPORT

3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program

Provide Program, Short name, Application, Description & also
– Executable Name as defined in the above step

– Output Format should be XML
– Define a Parameter p_product_id
– Associate Concurrent Program to the Request Group.

4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

RTF can be created using the XML file from the Output of the concurrent Program.


6. Run the Concurrent Program to see the output

Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure

 

How to Clear Cache from Oracle Application without Bouncing Listener?

Caching Framework in oracle apps R12 comes with an administration User interface and it is available under the Functional Administrator responsibility. This interface can be used to perform administrative operations including changing the time-out values for cache components, looking at cache usage statistics, and clearing caches.

Here are the steps to clear all Global Cache:

1] Login to Functional Administrator responsibility – then choose Home.

2] Choose the ‘Core Services’ Tab – then the “Caching Framework” Sub-Menu.

3] Proceed to choose ‘Global Configuration’ from the left hand side menu.

4] In the far right choose ‘Clear all Cache’ button.

4

5] A screen prompts and confirms that the action will clear all cache on the middle tier server – choose Yes. Essentially, this just forces all user sessions to engage and validate – rather than using cached values.

5

6] A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.

7] Proceed to test and confirm whatever change was made to the preference, profile, etc….

Note : Clearing the OA Framework cache can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared. Please use this utility with proper care.

You can also clear cache for specific component. To do that you need to go to Core Services -> Caching Framework -> Tuning. Query the application (for example iProcurement) or by Name or code.  Select the component and clear the cache.

Enabling trace in DBMS OUTPUT for HR APIs

While doing conversion,interface or any extension in HR sometime we have to debug the API to analyze the transaction flow.Below code snippet will be useful for such debugging (only for HR APIs).The below debug message we can view in the editor console itself.

Steps

1) Enable the DBMS OUTPUT.
2) EXEC DBMS_OUTPUT.ENABLE(1000000);
3) DECLARE

BEGIN
HR_UTILITY.SET_TRACE_OPTIONS(‘TRACE_DEST:DBMS_OUTPUT’);
HR_UTILITY.TRACE_ON(NULL, ‘XX_TRACE’) ;

—-
HR_UTILITY.TRACE_OFF;
END;

Changing Employee Number Generation from Manual to Automatic

In Oracle HR, once a Business Group has employee number generation set to manual, Oracle does not provide a means of changing it to automatic.

Consider the following business requirements:
1. Convert legacy system into Oracle HR via API’s
2. Keep current employee numbers unchanged
3. Provide automatic employee numbering for all new employees

The business requirements can be met by completing the following:

1. Establish Business Group with manual employee number generation
2. Convert legacy HR data via API’s
3. Determine a starting number for automatic employee numbering that is greater than the largest numeric converted employee
number
4.Run the following :

update per_number_generation_controls
set next_value = (The starting number from #3 above)
where type = ‘EMP’
and business_group_id = (The organization_id from
hr_organization_units of the Business Group in question)

5.
update hr_organization_information
set org_information2 = ‘A’
where org_information_context = ‘Business Group Information’
and orgainzation_id =  (Same as business_group_id from above SQL)

Bouncing Apps Server

It is often necessary to bounce the Apps Server. And always is it feasible to ask the DBAs for doing so.

Being a technical person you could also do so if you have the WINSCP access.

cd APPS/apps/apps_st/appl/

. ./ APPSPROD_korbsbvmlx04.env  (run the environment variable)

cd /u01/oracle/APPS/inst/apps/PROD_korbsbvmlx04/admin/scripts/

To Stop the Instance: adstpall.sh apps/apps

To Start the instance: adstrtal.sh apps/apps