Oracle Attachments in EBS

Attaching Tax PDFs to respective Parties using DB Object

Below steps include bulk attachment of files from a location to the respective parties,

Step 1 - Create a staging table with file details,

DROP TABLE Stag_w9_party_det CASCADE CONSTRAINTS;

CREATE TABLE Stag_w9_party_det
(
  PDF_NAME        VARCHAR2(200 BYTE),
  TAX_PAYER_NAME  VARCHAR2(200 BYTE),
  TAX_PAYER_ID    VARCHAR2(20 BYTE),
  HAND_WRTN_NO    VARCHAR2(15 BYTE),
  STATUS          VARCHAR2(15 BYTE),
  STATUS_MSG      VARCHAR2(400 BYTE),
  PARTY_NAME      VARCHAR2(360 BYTE)            NOT NULL,
  PARTY_NUMBER    VARCHAR2(30 BYTE)             NOT NULL
);

Step 2 - Load excel data to above staging table using SQL*Loader,

OPTIONS (SKIP=1, BINDSIZE=50000, DIRECT=FALSE, ERRORS=99999, ROWS=100000, READSIZE=65536)

load data

TRUNCATE

into table Stag_w9_party_det

fields terminated by ',' optionally enclosed by '"'

trailing nullcols (

PDF_NAME "trim(:PDF_NAME)",

TAX_PAYER_NAME "trim(:TAX_PAYER_NAME)",

TAX_PAYER_ID "trim(:TAX_PAYER_ID)",

HAND_WRTN_NO "regexp_replace( trim(:HAND_WRTN_NO), '[[:space:]]+', NULL)",

PARTY_NAME "trim(:PARTY_NAME)",

PARTY_NUMBER "regexp_replace( trim(:PARTY_NUMBER), '[[:space:]]+', NULL)"

)

Step 3 - Create a concurrent program upon the below procedure, and capture all the invalid parties and process all valid parties by attaching the Tax PDF Files to the respective parties 

CREATE OR REPLACE PROCEDURE apps.w9fileattach (
   p_errbuff         OUT      VARCHAR2,
   p_retcode         OUT      NUMBER,
   v_user_name       IN       VARCHAR2,
   v_resp_name       IN       VARCHAR2,
   v_category_name   IN       VARCHAR2,
   v_entity_name     IN       VARCHAR2
)
AS
/*###################################################################
File Name :  apps.w9FileAttach
Description : Attaches the Tax pdf file to the corresponding Parties
/*################################################################### */
   l_rowid                   ROWID;
   l_attached_document_id    NUMBER;
   l_document_id             NUMBER;
   l_document_tl_id          NUMBER;
   l_media_id                NUMBER;
   l_category_id             NUMBER;
   l_pk1_value               fnd_attached_documents.pk1_value%TYPE;
   l_description             fnd_documents_tl.description%TYPE;
   l_title                   fnd_documents_tl.title%TYPE;
   l_filename                fnd_documents_tl.file_name%TYPE;
   l_seq_num                 NUMBER                                  := NULL;
   l_appl_id                 NUMBER;
   l_resp_id                 NUMBER;
   l_fnd_user_id             NUMBER;
   l_instance                VARCHAR2 (20);
   v_conc_req_id             NUMBER;
   l_dir_name                VARCHAR2 (20);
   l_short_datatype_id       NUMBER;
   x_blob                    BLOB;
   fils                      BFILE;
   blob_length               INTEGER;
   l_entity_name             VARCHAR2 (100);
   l_format                  VARCHAR2 (100);
   l_concurrent_program_id   VARCHAR2 (100);
   l_application_id          VARCHAR2 (100);
   l_last_update_date        VARCHAR2 (100);
   l_file_content_type       VARCHAR2 (100);
   v_pty_id                  VARCHAR2 (100);
   v_pty_no                  VARCHAR2 (100);
   v_pty_nme                 VARCHAR2 (100);
   v_tax_code                VARCHAR2 (100);
   v_pk2_val                 VARCHAR2 (20)                       DEFAULT NULL;
   v_pk3_val                 VARCHAR2 (20)                       DEFAULT NULL;
   v_pk4_val                 VARCHAR2 (20)                       DEFAULT NULL;
   v_pk5_val                 VARCHAR2 (20)                       DEFAULT NULL;
   v_doc_id                  NUMBER;
   v_att_doc_id              NUMBER;
   l_pdf_name                VARCHAR2 (100);
   file_exists               BOOLEAN                                 := FALSE;
   v_file_name_cnt           NUMBER                                  := NULL;
   v_dir_cnt                 NUMBER                                  := NULL;
   v_pty_val                 NUMBER;
   v_flag                    VARCHAR2 (1);
   v_status                  VARCHAR2 (50)                           := NULL;
   v_status_msg              VARCHAR2 (400)                          := NULL;

   CURSOR c1
   IS
      SELECT   *
          FROM Stag_w9_party_det
         WHERE status IS NULL
      ORDER BY pdf_name DESC;
BEGIN
   BEGIN
      SELECT instance_name
        INTO l_instance
        FROM v$instance;
   EXCEPTION
      WHEN OTHERS
      THEN
         apps.fnd_file.put_line (fnd_file.LOG,
                                    'Error in Instance select statment - '
                                 || SQLERRM
                                );
   END;

   IF l_instance IN ('DEV', 'TEST', 'PREPROD')
   THEN
      SELECT COUNT (1)
        INTO v_dir_cnt
        FROM all_directories
       WHERE directory_name = 'FILES_ATTACH_DIR'
         AND directory_path =
                  '/interface_j/' || l_instance || '/incoming/W9_PARTIES';
   ELSE
      SELECT COUNT (1)
        INTO v_dir_cnt
        FROM all_directories
       WHERE directory_name = 'FILES_ATTACH_DIR'
         AND directory_path =
                  '/interface_i/' || l_instance || '/incoming/W9_PARTIES';
   END IF;

   IF v_dir_cnt = 0
   THEN
      fnd_file.put_line (fnd_file.LOG,
                         '==========Directory Creation....==========='
                        );

      -- Directory Creation
      IF l_instance IN ('DEV', 'TEST', 'PREPROD')
      THEN
         EXECUTE IMMEDIATE    'create or replace directory FILES_ATTACH_DIR AS
                             ''/interface_j/'
                           || l_instance
                           || '/incoming/W9_PARTIES''';
      ELSE
         EXECUTE IMMEDIATE    'create or replace directory FILES_ATTACH_DIR AS
                             ''/interface_i/'
                           || l_instance
                           || '/incoming/W9_PARTIES''';
      END IF;
   ELSE
      fnd_file.put_line (fnd_file.LOG, 'Directory already created');
   END IF;

   fnd_file.put_line (fnd_file.LOG,
                      '==========User/Responsibility Details....==========='
                     );

   -- User Details
   SELECT user_id
     INTO l_fnd_user_id
     FROM apps.fnd_user
    WHERE UPPER (user_name) = UPPER (v_user_name);

   SELECT application_id, responsibility_id
     INTO l_appl_id, l_resp_id
     FROM apps.fnd_responsibility_vl
    WHERE UPPER (responsibility_name) = UPPER (v_resp_name);

   -- Category_id
   SELECT category_id
     INTO l_category_id
     FROM fnd_document_categories_vl
    WHERE user_name LIKE v_category_name;

   fnd_file.put_line (fnd_file.LOG,
                      'Processing with Category Name - ' || v_category_name
                     );
   fnd_file.put_line (fnd_file.output,
                      'Processing with Category Name - ' || v_category_name
                     );

-- Fetching and processing staging table data
   FOR i IN c1
   LOOP
      fnd_file.put_line
                  (fnd_file.output,
                   '########################################################'
                  );
      fnd_file.put_line (fnd_file.LOG,
                            'Processing party from staging table - '
                         || i.party_name
                        );
      v_flag := 'Y';
      v_pty_val := NULL;

      -- Validation Parties
      IF v_flag = 'Y'
      THEN
         BEGIN
            SELECT COUNT (1)
              INTO v_pty_val
              FROM hz_parties
             WHERE UPPER (party_name) LIKE UPPER (i.party_name);
         EXCEPTION
            WHEN OTHERS
            THEN
               v_flag := 'N';
               v_status := 'INVALID';
               v_status_msg := 'Invalid Party Name';
         END;
      END IF;

      v_pty_val := NULL;

      IF v_flag = 'Y'
      THEN
         -- Validation
         BEGIN
            SELECT COUNT (1)
              INTO v_pty_val
              FROM hz_parties
             WHERE (    UPPER (party_name) LIKE UPPER (i.party_name)
                    AND jgzz_fiscal_code IN (i.tax_payer_id)
                   );
         EXCEPTION
            WHEN OTHERS
            THEN
               v_flag := 'N';
               v_status := 'INVALID';
               v_status_msg := 'Invalid Party Name';
         END;
      END IF;

      v_pty_val := NULL;

      IF v_flag = 'Y'
      THEN
         -- Validation
         BEGIN
            SELECT COUNT (1)
              INTO v_pty_val
              FROM hz_parties
             WHERE (    UPPER (party_name) LIKE UPPER (i.party_name)
                    AND jgzz_fiscal_code IN (i.tax_payer_id)
                   )
               AND TRIM (REGEXP_REPLACE (party_number, '[[:space:]]+', NULL)) IN
                                                                                --(i.party_number)
                                                                                TRIM
                                                                                   (REGEXP_REPLACE
                                                                                       (i.party_number,
                                                                                        '[[:space:]]+',
                                                                                        NULL
                                                                                       )
                                                                                   );
         EXCEPTION
            WHEN OTHERS
            THEN
               v_flag := 'N';
               v_status := 'INVALID';
               v_status_msg := 'Invalid Party Name';
         END;
      END IF;

      IF v_pty_val IS NULL
      THEN
         UPDATE Stag_w9_party_det
            SET status = v_status,
                status_msg = v_status_msg
          WHERE party_name = i.party_name
            AND party_number = i.party_number
            AND tax_payer_id = i.tax_payer_id
            AND pdf_name = i.pdf_name;

         fnd_file.put_line
                        (fnd_file.LOG,
                            'Error through validation of parties for party - '
                         || i.party_name
                        );
         COMMIT;
      ELSE
-- Fetching and processing Party Information from DB
         FOR rec_mpty IN
         
            --c2(i.party_name,i.tax_payer_id, i.party_number)
            (SELECT party_id, party_number, party_name, jgzz_fiscal_code
               FROM hz_parties
              WHERE (    UPPER (party_name) LIKE UPPER (i.party_name)
                     AND jgzz_fiscal_code IN (i.tax_payer_id)
                    )
                AND TRIM (REGEXP_REPLACE (party_number, '[[:space:]]+', NULL)) IN
                                                                                 --(i.party_number)
                                                                                 TRIM
                                                                                    (REGEXP_REPLACE
                                                                                        (i.party_number,
                                                                                         '[[:space:]]+',
                                                                                         NULL
                                                                                        )
                                                                                    ))
         LOOP
            --v_cnt := 1;
            v_pty_id := rec_mpty.party_id;
            v_pty_no := rec_mpty.party_number;
            v_pty_nme := rec_mpty.party_name;
            v_tax_code := rec_mpty.jgzz_fiscal_code;
            fnd_file.put_line (fnd_file.LOG,
                                  'DB Processing Party Name - '
                               || v_pty_nme
                               || ' and Party Id - '
                               || v_pty_id
                              );
            fnd_file.put_line (fnd_file.output,
                                  'DB Processing Party Name - '
                               || v_pty_nme
                               || ' and Party Id - '
                               || v_pty_id
                              );

            SELECT SUBSTR (i.pdf_name, 1, LENGTH (i.pdf_name) - 4)
              --INSTR (i.pdf_name, '.', 1) - 1)
            INTO   l_pdf_name
              FROM DUAL;

-- Fetching and processing PDF files as per table data
            FOR k IN (SELECT   l_pdf_name || '.pdf' l_pdf_name
                          FROM DUAL
                      UNION
                      SELECT   l_pdf_name || ' (2).pdf' l_pdf_name
                          FROM DUAL
                      ORDER BY 1 DESC)
            LOOP
               l_filename := k.l_pdf_name;
               fnd_file.put_line (fnd_file.output,
                                  'Processing PDF Name - ' || l_filename
                                 );
               v_doc_id := NULL;
               v_att_doc_id := NULL;

               -- Validation to verify file name attachment
               BEGIN
                  SELECT COUNT (DISTINCT file_name)
                    INTO v_file_name_cnt
                    FROM fnd_documents
                   WHERE document_id IN (
                            SELECT document_id
                              FROM fnd_attached_documents
                             WHERE pk1_value = v_pty_id
                               AND entity_name = v_entity_name
                               AND category_id = l_category_id)
                     AND category_id = l_category_id
                     AND file_name = l_filename;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     v_file_name_cnt := 0;
               END;

               IF v_file_name_cnt >= 1
               THEN
                  fnd_file.put_line (fnd_file.LOG,
                                        'There exists "'
                                     || l_filename
                                     || '" already attached to the party - '
                                     || v_pty_nme
                                    );
               ELSE
                  fnd_file.put_line
                                (fnd_file.LOG,
                                    'Attaching PDF to the Party with Name - '
                                 || l_filename
                                );
                  fnd_file.put_line (fnd_file.LOG,
                                     'Attachment Process Started '
                                    );

-- Creating Document Ids
                  SELECT fnd_documents_s.NEXTVAL
                    INTO l_document_id
                    FROM DUAL;

                  SELECT fnd_documents_s.CURRVAL
                    INTO l_document_tl_id
                    FROM DUAL;

                  SELECT fnd_attached_documents_s.NEXTVAL
                    INTO l_attached_document_id
                    FROM DUAL;

-- Seq Num
                  SELECT NVL (MAX (seq_num), 0) + 10
                    INTO l_seq_num
                    FROM fnd_attached_documents
                   WHERE pk1_value = v_pty_id AND entity_name = v_entity_name;

-- Get Data type id for Short Text types of attachments
                  SELECT datatype_id
                    INTO l_short_datatype_id
                    FROM apps.fnd_document_datatypes
                   WHERE NAME = 'FILE';

                  SELECT MAX (file_id) + 1
                    INTO l_media_id
                    FROM fnd_lobs;

                  SELECT SUBSTR (i.pdf_name, LENGTH (i.pdf_name) - 2)
                    INTO l_format
                    FROM DUAL;

                  -- Condition to get file_content_type
                  IF UPPER (l_format) = 'PDF'
                  THEN
                     l_file_content_type := 'application/pdf';
                  ELSIF UPPER (l_format) = 'XLS'
                  THEN
                     l_file_content_type := 'application/vnd.ms-excel';
                  ELSE
                     l_file_content_type := 'application/pdf';
                  END IF;

                  fnd_file.put_line (fnd_file.LOG,
                                        'File Content Type - '
                                     || l_file_content_type
                                    );

                  -- Fetching the concurrent program details
                  SELECT   MAX (request_id), concurrent_program_id
                      INTO v_conc_req_id, l_concurrent_program_id
                      FROM fnd_concurrent_requests
                     WHERE concurrent_program_id =
                              (SELECT concurrent_program_id
                                 FROM fnd_concurrent_programs_vl
                                WHERE user_concurrent_program_name LIKE
                                         'W9 Party Pdf Attachment Program')
                  GROUP BY concurrent_program_id;

                  SELECT SUBSTR (user_concurrent_program_name, 1, 75),
                         application_id, last_update_date
                    INTO l_description,
                         l_application_id, l_last_update_date
                    FROM fnd_concurrent_programs_vl
                   WHERE concurrent_program_id = l_concurrent_program_id;

                  l_description := 'W9 - ' || v_pty_nme;
                  l_title := 'W9 - ' || v_pty_nme;
                  fnd_file.put_line (fnd_file.LOG,
                                     'Description - ' || l_description
                                    );
                  fnd_file.put_line (fnd_file.LOG, 'Title - ' || l_title);
                  l_dir_name := 'FILES_ATTACH_DIR';  -- Directory from which files to be read and attached
                  fnd_file.put_line (fnd_file.LOG,
                                     '==========File...==========='
                                    );
-- Reading and verifying the file across directory
--   fils := BFILENAME (l_dir_name, l_filename);
                  fils := BFILENAME (l_dir_name, l_filename);
                  file_exists := DBMS_LOB.fileexists (fils) = 1;

                  IF file_exists
                  THEN
-- Obtain the size of the blob file
                     DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
                     blob_length := DBMS_LOB.getlength (fils);
                     DBMS_LOB.fileclose (fils);
                     fnd_file.put_line
                               (fnd_file.LOG,
                                '=========File read completed....==========='
                               );

-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.
                     INSERT INTO fnd_lobs
                                 (file_id, file_name,
                                  file_content_type, upload_date,
                                  expiration_date, program_name, program_tag,
                                  file_data, LANGUAGE, oracle_charset,
                                  file_format
                                 )
                          VALUES (l_media_id, l_filename,
                                  l_file_content_type, SYSDATE,
                                  NULL, 'FNDATTCH', NULL,
                                  EMPTY_BLOB (), 'US', 'UTF8',
                                  'binary'
                                 )
                       RETURNING file_data
                            INTO x_blob;

                     fnd_file.put_line
                        (fnd_file.LOG,
                         '==========Record inserted in fnd_lob table....==========='
                        );
-- Load the file into the database as a BLOB
                     DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
                     DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
                     DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
                     DBMS_LOB.CLOSE (x_blob);
                     DBMS_LOB.CLOSE (fils);
                     --fnd_file.put_line(fnd_file.LOG,'FND_LOBS Created with media_id - ' || l_media_id);
                     COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org
                     BEGIN
                        fnd_documents_pkg.insert_row
                           (x_rowid                       => l_rowid,
                            x_document_id                 => l_document_id,
                            x_creation_date               => SYSDATE,
                            x_created_by                  => l_fnd_user_id,
                            x_last_update_date            => SYSDATE,
                            x_last_updated_by             => l_fnd_user_id,
                            x_last_update_login           => fnd_profile.VALUE
                                                                   ('LOGIN_ID'),
                            x_datatype_id                 => l_short_datatype_id,
                            x_security_id                 => NULL,
                            x_publish_flag                => 'Y',
                            --This flag allow the file to share across multiple organization
                            x_category_id                 => l_category_id,
                            x_security_type               => 4,
                            x_usage_type                  => 'O',
                            x_language                    => 'US',
                            x_request_id                  => v_conc_req_id,
                            x_program_application_id      => l_application_id,
                            x_program_id                  => l_concurrent_program_id,
                            x_program_update_date         => l_last_update_date,
                            x_description                 => l_description,
                            x_file_name                   => l_filename,
                            x_media_id                    => l_media_id,
                            x_title                       => l_title
                           );
                        COMMIT;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           fnd_file.put_line
                              (fnd_file.LOG,
                                  'Error while inserting in fnd_documents table and fnd_documents_tl table - '
                               || SQLERRM
                              );
                     END;

                     fnd_file.put_line
                        (fnd_file.LOG,
                         '==========Record inserted in fnd_documents table and fnd_documents_tl table....==========='
                        );

                     BEGIN
                        fnd_attached_documents_pkg.insert_row
                           (x_rowid                         => l_rowid,
                            x_attached_document_id          => l_attached_document_id,
                            x_document_id                   => l_document_id,
                            x_creation_date                 => SYSDATE,
                            x_created_by                    => l_fnd_user_id,
                            x_last_update_date              => SYSDATE,
                            x_last_updated_by               => l_fnd_user_id,
                            x_last_update_login             => fnd_profile.VALUE
                                                                   ('LOGIN_ID'),
                            x_seq_num                       => l_seq_num,
                            x_entity_name                   => v_entity_name,
                            x_column1                       => NULL,
                            x_pk1_value                     => v_pty_id,
                            x_pk2_value                     => v_pk2_val,
                            x_pk3_value                     => v_pk3_val,
                            x_pk4_value                     => v_pk4_val,
                            x_pk5_value                     => v_pk5_val,
                            x_automatically_added_flag      => 'Y',
                            x_datatype_id                   => l_short_datatype_id,
                            x_category_id                   => l_category_id,
                            x_security_type                 => 4,
                            x_security_id                   => NULL,
                            x_publish_flag                  => 'Y',
                            x_language                      => 'US',
                            x_request_id                    => v_conc_req_id,
                            x_program_application_id        => l_application_id,
                            x_program_id                    => l_concurrent_program_id,
                            x_program_update_date           => l_last_update_date,
                            x_description                   => l_description,
                            x_file_name                     => l_filename,
                            x_media_id                      => l_media_id,
                            x_title                         => l_title
                           );
                        COMMIT;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           fnd_file.put_line
                              (fnd_file.LOG,
                                  'Error while inserting in fnd_attached_documents table, fnd_documents table and fnd_documents_tl table - '
                               || SQLERRM
                              );
                     END;

                     fnd_file.put_line
                        (fnd_file.LOG,
                         '==========Record inserted in fnd_attached_documents table, fnd_documents table and fnd_documents_tl table....==========='
                        );

-- Updating status as PDF attachment is completed
                     UPDATE Stag_w9_party_det
                        SET status = 'COMPLETED',
                            status_msg = 'PDFs Attached successfully'
                      WHERE party_name = i.party_name
                        AND party_number = i.party_number
                        AND tax_payer_id = i.tax_payer_id
                        AND pdf_name = l_filename;

                     fnd_file.put_line
                                   (fnd_file.output,
                                       'PDF Successfully attached to party - '
                                    || i.party_name
                                   );
                     COMMIT;
                  ELSE
                     UPDATE Stag_w9_party_det
                        SET status = 'ERROR',
                            status_msg = 'PDF doesn''t exist in directory'
                      WHERE party_name = i.party_name
                        AND party_number = i.party_number
                        AND tax_payer_id = i.tax_payer_id
                        AND pdf_name = l_filename;

                     fnd_file.put_line (fnd_file.LOG,
                                           l_filename
                                        || ' does not exist in directory'
                                       );
                     COMMIT;
                  END IF;
               END IF;
            END LOOP;
         END LOOP;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line
                   (fnd_file.LOG,
                       'Error while processing the attachment process - Main'
                    || SQLERRM
                   );
END;
/

Oracle Alerts in R12

1  Overview of Oracle Alerts
2  Defining Alerts
3  Type of Alerts
4  Check the Alerts

Overview of Oracle Alerts:

• Keep you informed of critical activity in your database
• Deliver key information from your applications, in the format you choose
• Provide you with regular reports on your database information
• Automate system maintenance, and routine online tasks

Overview:

Oracle Alert is your complete exception control solution. Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.
• A SQL Select statement that retrieves specific database information as a result of the database event.
• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

• A SQL Select statement that retrieves specific database information.
• The frequency that you want the periodic alert to run the SQL statement.
• Actions that you want Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a
• SQL statement script.

Defining Alerts:

Navigator: Alert Vision Manager USA

To define Alerts

Types of Alerts:

1. Periodic Alerts

Periodic alerts periodically report key information according to a schedule you define.

To define a periodic alert:
1. Navigate to the Alerts form.
2. Enter the name of the application that owns the alert in the
Application field. This application must reside in the same Oracle database as Oracle Alert.
3. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
4.Enter a name for the alert that is unique within the application. Use an initial character other than a pound sign (#), a colon (:), or a percentage sign (%).

1. Select a frequency for your periodic alert. You can choose from nine frequency options:
2. Frequency :

On Demand             :-We have to run the alert to fire.

Enter which Application u want to specify alerts, name of the alert,
Click whether its periodic or Event

On Day of Month: It will fire last day of every month.
It will start from 12 AM up to 11 AM morning and each 2 hours interval the alert will fire once.
           Ex: 12AM, 2 AM, 4 AM … … … … 8am, 10am.



On Day of Week : Same as On day of Month.

Every N Calendar day: It will fire in each two days.

Every N Business Day: It will fire once in each three business day.(It will not count the holidays)

Every Day:  It will fire daily

Every Other Day :  It will fire in a alternative day.

Every Business Day: It will fire every day except holiday.

Every Other Business Day: It will fire in an alternative business (working) day.
i.e  It will not consider holidays

Import :  We can import the files(which contain the query) if the query size is big, if it exceed the memory.






After writing the Query in Select Statement area click on Verify  to verify the query and


After Click on Verify Button the following message will come.
Click on OK and Save

Then click on Run  to execute the query.


Click on Run  button , the following screen will come. And click on OK.

2 . Click on Actions  Button and enter the Action information  like following screen

• Details Action perform once for each exception found,
• Summary Action perform once for all exception found
• No exception performs when no exception was found.

2.Click on Action Details  and enter the value  and save.
Concurrent Program :  Used to execute the Concurrent program.


Message : Used to send the message via mail.


Operating System Script:  Used to run the OS Script.


SQL Statement Script : Used to run the SQL statement Script.


3. Click on Action Sets   button   from Alert window


Then click on Action Set Details





4. Then click on Alert Details from the Alert window and enter the value and save.

Event Alerts:        

Creating an Event Alert

To create an event alert, you perform the following tasks in the order listed:
• Define the database events that will trigger your alert
• Specify the details for your alert
• Define actions for your alert
• Create action sets containing the actions you want your alert to perform
This section focuses on the first task of defining the database events that trigger your event alert and divides the task into smaller sub–tasks.

To define an event alert:
1. Navigate to the Alerts form.
2. In the Application field, use the list of values to choose the name of the application that owns the alert. This application must reside in the same Oracle database as Oracle Alert.
3. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
Enter a name for the alert that is unique within the application. Use an initial character other than a pound sign (#), a colon (:), or a percentage sign (%).
4. In the Type field, choose Event.
5. Check Enabled to enable your event alert.



To specify the alert details for an event or periodic alert:
1. With your alert definition displayed in the Alerts form, choose Alert Details. The Alert Details window that appears contains three tabbed regions: Installations, Inputs, and Outputs.

2. In the Inputs tabbed region, Oracle Alert automatically displays the inputs used in your Select statement, unless they are the implicit

inputs: :ROWID, :MAILID, :ORG_ID and :DATE_LAST_CHECKED.

The values of the implicit inputs are as follows:

• ROWID—Contains the ID number of the row where the insert or update that triggers an event alert occurs.
• MAILID—Contains the email username of the person who enters an insert or update that triggers an event alert.
• ORG_ID—Contains the organization ID that is selected when the alert runs.
• DATE_LAST_CHECKED—Contains the date and time that the alert was most recently checked.

3. You can optionally add a description for each input, but you must specify the data type (either character, number, or date) for the input, because Oracle Alert uses the data type to validate the default values for inputs you enter in the Default Values field and in the Action Set Inputs tabbed region of the Action Sets block.

4. Enter a default value for your input. You can enter up to 240  characters. This value automatically becomes the default value for your input in each action set you define.

5. In the Outputs tabbed region, Oracle Alert automatically displays the outputs used in your alert Select statement without the ampersand (&) and any numbering format. You can add meaningful descriptions for the outputs.

6. You can specify the maximum number of output characters you want to display in your detail or summary message actions. See: Formatting Summary Message Actions:

7. If your output value is numeric, enter the SQL*Plus format mask in the Number Format field.

8. You can also check the Check for Duplicates check box to customize the combination of outputs you want to verify as a possible duplicate exception each time the alert is checked. A duplicate exception is an exception that existed in your database during previous alert checks. You can define Oracle Alert to perform certain actions based on the presence of these duplicate exceptions.

9. In the Installations tabbed region, specify an Oracle ID if you want Oracle Alert to check your alert against that specific Oracle ID. You can select only those Oracle IDs that are associated with the application that owns your alert.
If you do not specify an Oracle ID in this region, Oracle Alert checks your alert against all installations in your database of the application that owns your alert.

10. If you have multiple organizations defined in your Oracle Applications schema, you must specify the organization you want the alert to run against in the Operating Unit field.

Click Action details,

In Action details Navigator
They are four types of Actions

Concurrent Program :       Used to execute the Concurrent program.
Message :                             Used to send the message via mail.
Operating System Script:  Used to run the OS Script.
SQL Statement Script :      Used to run the SQL statement Script.

Check the Alert:

1. Go to Alert Manager(N) Request>Check



Then View> Request >Find from Alert Manager responsibility.


After completed Normal check the mail.

Query to Find Out Oracle Alerts

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
alr.alert_id,
alr.alert_name,
alr.start_date_active,
alr.description,
alr.sql_statement_text
FROM alr.alr_alerts alr
WHERE 1=1
AND alr.created_by <> 1 -- show only custom alerts
AND alr.enabled_flag = 'Y'; -- show only enabled alerts

Oracle Apps Alerts Tables:

Oracle Apps Alerts Table

ALR_ACTION_DATA

ALR_ACTION_GROUP_MEMBERS

ALR_ACTION_GROUPS

ALR_ACTION_HISTORY

ALR_ACTION_OUTPUTS

ALR_ACTION_SET_CHECKS

ALR_ACTION_SET_INPUTS

ALR_ACTION_SET_MEMBERS

ALR_ACTION_SET_OUTPUTS

ALR_ACTION_SETS

ALR_ACTIONS

ALR_ACTUAL_RESPONSES

ALR_ALERT_CHECKS

ALR_ALERT_INPUTS

ALR_ALERT_INSTALLATIONS

ALR_ALERT_OUTPUTS

ALR_ALERTS

ALR_ALERTS_A

ALR_DISTRIBUTION_LISTS

ALR_LOOKUPS

ALR_MESSAGE_SYSTEMS

ALR_ORACLE_MAIL_ACCOUNTS

ALR_OUTPUT_HISTORY

ALR_PERIODIC_SET_MEMBERS

ALR_PERIODIC_SETS

ALR_PROFILE_OPTIONS

ALR_RESPONSE_ACTION_HISTORY

ALR_RESPONSE_ACTIONS

ALR_RESPONSE_MESSAGES

ALR_RESPONSE_SETS

ALR_RESPONSE_VARIABLE_VALUES

ALR_RESPONSE_VARIABLES

ALR_VALID_RESPONSES

Query to find whether Event Alert fired or not:

SELECT REQUEST_ID

, REQUEST_DATE

, PHASE_CODE

, STATUS_CODE

, ORACLE_ID

, PROGRAM_APPLICATION_ID

, CONCURRENT_PROGRAM_ID

FROM APPLSYS.FND_CONCURRENT_REQUESTS

where Concurrent_program_id = (select Concurrent_program_id from fnd_concurrent_programs_tl where

USER_CONCURRENT_PROGRAM_NAME = 'Check Event Alert') 

order by request_date desc