Oracle Alerts is

Basic Definition of Oracle Alert is :

1.      To Monitor Activity of Business Needs. e.g Sending Email ,calling Concurrent Program,SQL Scripts and Operating System Scripts.

Types of Oracle Alerts

Periodic Alert: Periodic Alert Fires at pre defined set of Time.e.g suppose you want to Email all Order Booked today We can Use Periodic Alerts.

Event Alerts: Event Alerts Fires when particular database activity occurs e.g inserting record and updating record of tables.

How to Design Periodic Alerts:

Design of Periodic Alert is explained from scratch with the help of Test Alert ““Items with zero weight” .

We have to design periodic alert “Items with zero weight” for our Client.Basic Business need of this alerts is for Email to MIS User for List of item having Zero Weight saved in Oracle Inventory.

STEP1: Navigate To Alert Manager Form ..

NàAlert ManageràAlertsàDefine

·         Application Field: Name of Application e.g Oracle Payable

·         Name : User defined Name of Alerts.

·         Choose Period tab

·         Frequency :Choose Frequency Accordingly.

·         Days: Choose days according to Frequency. Suppose you Chosse Frequency “Every N Business Days” Then Enter Value “1” in Day Field.

·         Start Time: Time You want to Fire periodic alerts suppose you want to fire at 6:30 A.M Write “06:30:00” in Start Time Field.

·         Keep: How many days You Want to Mainitain History.

·         Select Statement: Write Query in select Statement . Here is an Exapmle of Select Statement for Test Periodic Alert “Items with zero weight”. Select statement must include an INTO clause that contains one output for each column selected by your Select statement.In Example all input Column like Orgaization_code,tem_number(segment1),Description,Creation_date have Output Variable ORG,ITEM,DESCR,Create_date preceded by Amperstand(&).

Query is: Test Query for “Items with zero weight” Alert is

SELECT

distinct p.organization_code ,

substr(i.segment1,1,20) ,

substr(i.description,1,50),

i.creation_date,

INTO

&org

, &item

, &descr

, &create_date

FROM

mtl_system_items i,

mtl_parameters p

where i.organization_id = p.organization_id

and p.organization_code in ('INF','ENF')

and i.INVENTORY_ITEM_STATUS_CODE||'' = 'Active'

and i.unit_weight is null

and I.ITEM_TYPE = 'P'

order by 1,2

·         Verify: Click on Verify Button to Verify your Query. This message will populate if Query is Right.

·         Run: To Check Record Count of Your Query Click on Run Button. Suppose Your Query Written Zero Rows This Message will populate.

STEP2 : Define Action:

Click on Action Button(Marked With Circle). This Form (Action Window 1.0) Will Poulate..

Explaination of Field on this Form :

·         Action Name:Specify Name of your Action. For Example Our Alert ““Items with zero weight”. is for Email.we specify name “EMAIL” in Action Name.

·         Action Level: Action level should be Summary.

Three are three Action Level

·         Detail - action is performed for each exception returned.

·         Summary - action performed once for each exception.

·         No Exception - action performed when no exceptions returned.

(Action Window 1.0)

Action Detail: By Click on Action Detail Button this Window (Action Detail Window 1.1)

Action Detail Window 1.1

Action Type: Four Action type.

·         Message - send message

·         Concurrent Program Request - submit concurrent program

·         SQL script - execute a SQL script

·         OS script - execute an OS script

We Choose Action Type “Message” because “Items with zero weight” Alert is for Email Purpose.

Enter Email Addrees in To Field.

Text : In Text field design Layout of your Periodic Alerts. “Items with zero weight” Alert Layout is this:

Important thing is that Output Variable &org,&item etc should be placed with in template like this…

=**= Enter summary template below this line =**=

**&org &item &descr &create_date

=**= Enter summary template above this line =**=

Layout Sample of Test Periodic Alert “Items with zero weight”:

The following items currently have no weight maintained against them in the system.

Org Item Description Creation Date

=== ==================== ============================== =============

=**= Enter summary template below this line =**=

**&org &item &descr &create_date

=**= Enter summary template above this line =**=

Column OverFlow: ‘Wrap’

Max Width: Choose According to Requirments.

·         80 Characters

·         132 Characters

·         180 Characters

STEP3 : Define Action Sets:

Enter the groups of action to be run. Click on Action Sets Button.

Action Set Window (Shown in Pictiure “Action set Window 1.2”) will populate.

·         Action set Name: Enter Action Set Name. In our Test Alert we enter Action set Name “EMAIL”.

Note : Action set Name should be same as in Action Name. Otherwise Periodic Alert will not Fire.

Action Set Window 1.2

·         Action set Name: Enter Action Set Name. In our Test Alert we enter Action set Name “EMAIl”.

·         Output Tab: Output tab Contain List of Output Variable defoen in select Statement as shown in Window 1.4.Note that if you Output tab is Blank Requery the Alert then it will show output Variable.

This problem we generally faced when designing periodic Alerts.

(Window 1.4)

Member Tab: Enter Name of Action .

 

 

 

 

 

 

 

 

 

 

 

How to Design Event Alerts:

Procedure of design of Event Alert is same as Periodic Alert.Just you Have to choose
Event Tab and give Event Details. Application Name and Table Name and Click on Check box “After Insert” and “ After Update” according to Requirment and rest of Procedure is same.

Practical Probem and Solution During Design Event Alerts:

We have to Design “Item Attribute Update” in Toshiba, Sydney Projects.Requirment of Alert is that if any item Comes in Inventory Email should sent to MIS USER. We developed Event Alerts. But when we are inserting new record in MTL_SYSTEM_ITEMS_B our Alert is Not Fired.

Solution: Solution of Problem is :
GO TO Alert Details and in Alert Details Forms Click on Installation Tab and give the name of Operating Units for which You Want to Design Alerts.

Query Event Alert:

SELECT :ROWID,

segment1,

organization_id,

inventory_item_id

into

&rowid,

&segment1,

&organization_id,

&inventory_item_id

from mtl_system_items_b

where rowid= :ROWID

Query Periodic Alert with time interval:

select

rsh.receipt_num, msi.segment1, rsl.quantity_shipped, sum(rcv.QUANTITY),

decode(msi.organization_id,184,

'RBatra@toshiba-tap.com jdunnicliff@toshiba-tap.com dmorris@toshiba-tap.com nhirapetians@toshiba-tap.com', 195,'wzarb@Toshiba-Tap.com pbarker@Toshiba-Tap.com kwaterstone@Toshiba-Tap.com smarffy@toshiba-tap.com')

INTO

&RECEIPT_NUM,

&SEGMENT1,

&QUANTITY_SHIPPED,

&QUANTITY_RECEIVED,

&TO_RCP

from

mtl_system_items msi,

rcv_shipment_lines rsl,

RCV_TRANSACTIONS rcv,

rcv_shipment_headers rsh

where

trunc(rsh.creation_date) > '01-jan-90'

and rsh.SHIPMENT_HEADER_ID =rcv.SHIPMENT_HEADER_ID

and rsl.shipment_header_id = rsh.shipment_header_id

and rsl.SHIPMENT_LINE_ID =rcv.SHIPMENT_LINE_ID

and rcv.TRANSACTION_TYPE ='DELIVER'

and msi.organization_id = rsl.to_organization_id

and msi.inventory_item_id = rsl.item_id

and msi.organization_id in(184,195)

and rcv.quantity >0

and rcv.last_update_date >=

to_date(:DATE_LAST_CHECKED, 'DD-MON-YYYY HH24:MI:SS') - (30/(24*60*60))

group by rsh.receipt_num, msi.segment1, rsl.quantity_shipped,msi.organization_id

CREATE OR REPLACE PACKAGE BODY cinv_update_msib AS

PROCEDURE update_row( errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_organization_id NUMBER,

p_inventory_item_id NUMBER) AS

l_serial_control_code NUMBER:=NULL;

BEGIN

IF p_organization_id IN (195, 200, 184, 192) THEN

SELECT msib.serial_number_control_code

INTO l_serial_control_code

FROM mtl_system_items_b msib,

mtl_parameters mp

WHERE msib.inventory_item_id = p_inventory_item_id

AND msib.organization_id = mp.master_organization_id

AND mp.organization_id = p_organization_id;

IF l_serial_control_code <> 1 THEN

UPDATE mtl_system_items_b

SET serial_number_control_code = 6

WHERE organization_id = p_organization_id

AND inventory_item_id = p_inventory_item_id;

END IF;

ELSE

NULL;

END IF;

IF p_organization_id IN (195, 200, 184, 187, 189, 188, 190, 192) THEN

UPDATE mtl_system_items_b msib

SET msib.atp_flag = 'Y'

WHERE msib.organization_id = p_organization_id

AND msib.inventory_item_id = p_inventory_item_id;

ELSE

NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'Process Failed:');

--NULL;

END update_row;

END;

/

FAQS:

1. What are Oracle Alerts?
Answer------Oracle Alerts are used to monitor unusual or critical activity withina designated database. The flexibility of ALERTS allows a database administrator the ability to monitor activities from tablespace sizingto activities associated with particular applications (i.e. AP, GL, FA).Alerts can be created to monitor a process in the database and to notifya specific individual of the status of the process.

2. What types of Oracle Alerts are there?
Answer------There are 2 types of alerts: Event Alerts and Periodic Alerts
a) EVENT ALERTS are triggered when an event or change is made to a table in the database.
b) PERIODIC ALERTS are activated on a scheduled basis to monitor database activities or changes.

3. What types of actions can be generated when an Alert is triggered?
Answer------When an alert is triggered or the event is true, the alert can Email, send or print a message. Alerts also have the ability to execute a SQL script to perform an action. Using Response Processing, Oracle Alerts can solicit a response from a specific individual and perform an action based on the response that it receives.

4. Can I build an Alert to run with my custom applications or tables?
Answer------Event or Periodic Alerts can work with any custom application, as long as the application is properly registered within the Oracle Applications package.

5. Which Email packages work with Alerts?
Answer------Oracle Alert is designed to work with Oracle Office, Oracle Interoffice, UNIX Sendmail, and VMS Mail.

6. Can Alerts be triggered by other Tools? (i.e. other than Oracle Forms and concurrent programs)
Answer------Oracle Alerts can only be triggered from an application that has been registered in Oracle Applications. Alerts cannot be triggered via SQL updates or deletes to an Alert activated trigger.

7. What is Response Processing?
Answer------Response processing is a component of Alerts which allows the recipients of an alert to reply with a message and have the applications take some action based on the response. Response Processing only works with Oracle Mailproducts.

8. Do I need Oracle Applications to use Alerts?
Answer------No. The following are the only components required to use Oracle Alerts. The components must be certified versions for your hardware platform and operating system.
- RDBMS
- SQL*PLUS
- FORMS
- ORACLE MAIL product (i.e. Oracle Office or InterOffice)
- SQL*NET..