How to Link an Alert with a simple Workflow to send notifications

Salam Alaikum,

   There is a needed requirement in many projects for the users to get notified once a business event occurs. And that can be easily covered by an Alert, if the business is for the users to receive emails once the event happens, explaining the event and providing needed information about it to the users.

   But another popular requirement is for the user to receive notifications via a workflow (Worklist notifications).

   It can be done easily using an Alert along with a simple Workflow. And here is how....

   First Step:
   Create a simple Workflow like the one shown in the screenshot below.
(the workflow should at least have one process, one notification , one message and two attributes)

   The workflow should have the attributes (P_NOTE_TITLE,XX_MESSAGE_BODY - both are the internal names of the attributes) to fill the notification with the business case information that the user will receive.

   And of course, you have to save the workflow on the database.

   Second Step:
   We should create the alert that should trigger once the business event occurs.

Please note that the alert select statement should include the subject of the notification and notification body (message) you want to be sent to the users.


Third Step:
In the created Alert you should create an action of type SQL statement script that calls the procedure that runs the workflow created in first step.

And the script should be a call to a function on the database that start the workflow process.

execute XX_SIMPLE_NOTIFICATION.XX_SEND_MULTI_NOTES (   'send Not'  , '&p_message'  ,  '&p_title' );
/

And on the database level, the procedure should look like this:

 procedure  XX_SEND_MULTI_NOTES (     P_action_name varchar2 ,
                                       P_MESSAGE varchar2  ,
                                       P_TITLE varchar2 )
Is
    V_SEQ NUMBER DEFAULT 0;
  Begin

    SELECT XX_SIMPLENOTE_WF_S.NEXTVAL INTO V_SEQ FROM DUAL;
 
    wf_engine.CreateProcess(itemtype => 'XXZNOTE',
                            itemkey  => 'SIMPLE_NTF' || V_SEQ,
                            process  => 'SEND_TO_WAREHOUSE_TEAM');

    wf_engine.SetItemAttrTEXT(itemtype => 'XXZNOTE',
                              itemkey  => 'SIMPLE_NTF' || V_SEQ,
                              aname    => 'XX_MESSAGE_BODY',
                              avalue   =>  replace(P_MESSAGE,'@',chr(10)));
 

    wf_engine.SetItemAttrTEXT(itemtype => 'XXZNOTE',
                              itemkey  => 'SIMPLE_NTF' || V_SEQ,
                              aname    => 'ACTION_NAME',
                              avalue   => P_action_name);

    wf_engine.SetItemAttrTEXT(itemtype => 'XXZNOTE',
                              itemkey  => 'SIMPLE_NTF' || V_SEQ,
                              aname    => 'P_NPTE_TITLE',
                              avalue   => P_TITLE);

 
    wf_engine.StartProcess(itemtype => 'XXZNOTE',
                           itemkey  => 'SIMPLE_NTF' || V_SEQ);
  commit;
 
End;

   And each time the alert gets triggered a notification should be sent to the predefined user that for example in a different attribute with constant value.

   Hope you found it useful guys, feel free to leave a comment if have any note on the subject.        

API To Create Employee Phone

Salam Alaikum,

   This is a very simple API that can be used to create phone details for employees.

DECLARE
   ln_phone_id                per_phones.phone_id%TYPE;
   ln_object_version_number   per_phones.object_version_number%TYPE;
BEGIN
-- Create or Update Employee Phone Detail
-- -----------------------------------------------------------
   hr_phone_api.create_or_update_phone
                         (                   -- Input data elements
                                             -- -----------------------------
                          p_date_from                  => TO_DATE('01-JAN-1900'),
                          p_phone_type                 => 'M',
                          p_phone_number               => '0566106406',
                          p_parent_id                  => 1102,
                          p_parent_table               => 'PER_ALL_PEOPLE_F',
                          p_effective_date             => TO_DATE('01-JAN-1900'),
-- Output data elements
-- --------------------------------
                          p_phone_id                   => ln_phone_id,
                          p_object_version_number      => ln_object_version_number
                         );
   DBMS_OUTPUT.put_line ('ln_phone_id ' || ln_phone_id);
--COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;

  Hope you found it useful guys, feel free to leave a comment if have any note on the subject.        

API To Create Establishment (School/College)

Salam Alaikum,

   Following the API that can be used for creating a Establishment in HR. That Can either be a School or college... etc. Please note that some attributes given in the API may deffer based on the setup.

DECLARE
   CURSOR schools
   IS
      SELECT xx_sch.ROWID,
             xx_sch.*
        FROM xxx_schools_info xx_sch
        WHERE 1 = 1 AND NVL (upload_status, 'N') = 'N';

   l_establishment_id        per_establishments.establishment_id%TYPE;
   l_object_version_number   per_establishments.object_version_number%TYPE;
BEGIN
   FOR i_rec IN schools
   LOOP
      per_est_ins.ins (p_establishment_id           => l_establishment_id,
                       p_name                       => i_rec.establishment_name,
                       p_location                   => i_rec.LOCATION,
                       p_attribute_category         => NULL,
                       p_attribute1                 => i_rec.country,
                       p_attribute2                 => i_rec.area,
                       p_attribute3                 => i_rec.city,
                       p_object_version_number      => l_object_version_number,
                       p_validate                   => FALSE
                      );

      UPDATE xxx_schools_info
         SET upload_status = 'Y',
             establishment_id = l_establishment_id
       WHERE ROWID = i_rec.ROWID;
   END LOOP;
END;

  As always, feel free to leave a comment if have any note on the subject.        

API To Create Employee Qualification

Salam Alaikum,

   I share this API, that creates an employee qualification, but please note that it needs that some prerequisites for this API is needed. Like qualification type should be already created and qualification_type_id should be input to the create_qualification API. As for establishment and establishment_id to be input for the API. And lastly, the employee should be an attendant of the establishment and attendance_id should be input to the API.

The following queries might help to understand the API more clearly:

SELECT qualification_type_id, NAME
  FROM per_qualification_types_tl
 WHERE 1 = 1 AND LANGUAGE = 'US';

SELECT establishment_id,NAME
  FROM per_establishments
 WHERE 1 = 1;

SELECT attendance_id
  FROM per_establishment_attend_v
 WHERE     1 = 1
       AND person_id = 'xx'
       AND establishment_id = 'establishment_id';

   And this is the API:

DECLARE
   l_qualification_id        NUMBER;
   l_object_version_number   NUMBER;
  
   CURSOR per_edu
   IS
      SELECT xx_edu.ROWID,
             xx_edu.*
        FROM xxx_emp_edu_school_info xx_edu
       WHERE 1 = 1 AND NVL (upload_status, 'N') = 'N';

BEGIN
   FOR i_rec IN per_edu
   LOOP
      fnd_profile.put ('PER_PERSON_ID', i_rec.person_id);
      per_qualifications_api.create_qualification
          (p_validate                      => FALSE,
           p_effective_date                => SYSDATE,
           p_qualification_type_id         => i_rec.qualification_type_id,
           p_attendance_id                 => i_rec.attendance_id,
           p_business_group_id             => fnd_profile.VALUE
                                                      ('PER_BUSINESS_GROUP_ID'),
           p_person_id                     => i_rec.person_id,
           p_awarded_date                  => SYSDATE,
           p_start_date                    => SYSDATE,
           p_qua_information_category      => 'SA',
           p_qua_information1              => 'EARENED',
           p_qualification_id              => l_qualification_id,
           p_object_version_number         => l_object_version_number
          );

      UPDATE xxx_emp_edu_school_info
         SET upload_status = 'Y',
             qualification_id = l_qualification_id
       WHERE ROWID = i_rec.ROWID;
      
       commit;
   END LOOP;
END;

  Hope you found it useful guys, feel free to leave a comment if have any note on the subject.       

API to Create External Bank Branch

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks Branches , please note that external bank should exist and bank_id should be passed to this API.

DECLARE
   p_api_version           NUMBER := 1.0;
   p_init_msg_list         VARCHAR2 (1) := 'F';
   x_return_status         VARCHAR2 (2000);
   x_msg_count             NUMBER (5);
   x_msg_data              VARCHAR2 (2000);
   x_response              iby_fndcpt_common_pub.result_rec_type;
   p_ext_bank_branch_rec   iby_ext_bankacct_pub.extbankbranch_rec_type;
   x_branch_id             NUMBER;
   p_count                 NUMBER;

   CURSOR C1
   IS
 SELECT DISTINCT BANK_COUNTRY, BANK_NUMBER, BANK_NAME
                FROM xx_customer_tmp_amm_t
               WHERE     BANK_COUNTRY IS NOT NULL
                     AND BANK_NUMBER IS NOT NULL
                     AND BANK_NAME IS NOT NULL
                     and O_BANK_UPLOAD_STATUS is null;
BEGIN
   FOR REC1 IN C1
   LOOP
      p_ext_bank_branch_rec.bch_object_version_number := 1.0;
      p_ext_bank_branch_rec.branch_name := rec1.BANK_BRANCH_NAME;
      p_ext_bank_branch_rec.branch_type := 'ABA';
      p_ext_bank_branch_rec.branch_number := rec1.BRANCH_NUMBER;
      p_ext_bank_branch_rec.bank_party_id := rec1.NEW_BANK_ID;


      APPS.FND_MSG_PUB.DELETE_MSG (NULL);
      APPS.FND_MSG_PUB.INITIALIZE ();


      IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH (
         p_api_version           => p_api_version,
         p_init_msg_list         => p_init_msg_list,
         p_ext_bank_branch_rec   => p_ext_bank_branch_rec,
         x_branch_id             => x_branch_id,
         x_return_status         => x_return_status,
         x_msg_count             => x_msg_count,
         x_msg_data              => x_msg_data,
         x_response              => x_response);

      DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
      DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
      DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);
      DBMS_OUTPUT.put_line ('x_branch_id = ' || x_branch_id);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Code = ' || x_response.result_code);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Category = ' || x_response.result_category);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Message = ' || x_response.result_message);

      IF x_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
      ELSIF x_msg_count > 1
      THEN
         LOOP
            p_count := p_count + 1;
            x_msg_data :=
               fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

            IF x_msg_data IS NULL
            THEN
               EXIT;
            END IF;

            DBMS_OUTPUT.put_line (
               'Message' || p_count || ' ---' || x_msg_data);
         END LOOP;
      END IF;

      IF (x_return_status = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
END;

     And  Here is a Query that you can use to see uploaded bank branches.

SELECT BranchParty.PARTY_ID Branch_Party_Id,
       BranchParty.PARTY_NAME Bank_Branch_Name,
       BranchParty.ORGANIZATION_NAME_PHONETIC Bank_Branch_Name_Alt,
       BranchCA.start_date_active Start_Date,
       BranchCA.end_date_active End_Date
  FROM HZ_PARTIES BranchParty, HZ_CODE_ASSIGNMENTS BranchCA
 WHERE     1 = 1
       AND BranchParty.PARTY_TYPE = 'ORGANIZATION'
       AND BranchParty.status = 'A'
       AND BranchCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BranchCA.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH')
       AND BranchCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND (BranchCA.STATUS = 'A' OR BranchCA.STATUS IS NULL)
       AND BranchCA.OWNER_TABLE_ID = BranchParty.PARTY_ID
       AND BranchParty.created_by = -1;

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.       

API to Create External Bank

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks that then can be assigned to customers (AR) or Suppliers (AP).

DECLARE
   LC_OUTPUT          VARCHAR2 (3000);
   LC_MSG_DUMMY       VARCHAR2 (3000);
   LC_RETURN_STATUS   VARCHAR2 (3000);
   LC_MSG_DATA        VARCHAR2 (3000);

   LN_BANK_ID         NUMBER;
   LN_MSG_COUNT       NUMBER;
   LR_EXTBANK_REC     APPS.IBY_EXT_BANKACCT_PUB.EXTBANK_REC_TYPE;
   LR_RESPONSE_REC    APPS.IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;

   V_COUNTRY_CODE     VARCHAR2 (100);

   CURSOR C1
   IS
      SELECT DISTINCT BANK_COUNTRY, BANK_NUMBER, BANK_NAME
                FROM xx_customer_tmp_amm_t
               WHERE     BANK_COUNTRY IS NOT NULL
                     AND BANK_NUMBER IS NOT NULL
                     AND BANK_NAME IS NOT NULL
                     and O_BANK_UPLOAD_STATUS is null;

BEGIN
   LC_RETURN_STATUS := '';
   LN_MSG_COUNT := '';
   LC_MSG_DATA := '';


   APPS.FND_MSG_PUB.DELETE_MSG (NULL);
   APPS.FND_MSG_PUB.INITIALIZE ();

   FOR REC1 IN C1
   LOOP
      BEGIN
         SELECT LOOKUP_CODE
           INTO V_COUNTRY_CODE
           FROM FND_LOOKUP_VALUES
          WHERE     LOOKUP_TYPE = 'GHR_US_POSTAL_COUNTRY_CODE'
                AND LANGUAGE = 'US'
                AND MEANING = TRIM (REC1.BANK_COUNTRY);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('Bank_Country name is not Correct ');
      END;

      LR_EXTBANK_REC.BANK_NAME := REC1.BANK_NAME;
      LR_EXTBANK_REC.BANK_NUMBER := REC1.BANK_NUMBER;
      LR_EXTBANK_REC.COUNTRY_CODE := V_COUNTRY_CODE;
      IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK ( -- ------------------------------
         -- Input data elements
         -- ------------------------------
         P_API_VERSION     => 1.0,
         P_INIT_MSG_LIST   => FND_API.G_TRUE,
         P_EXT_BANK_REC    => LR_EXTBANK_REC,
         -- --------------------------------
         -- Output data elements
         -- --------------------------------
         X_BANK_ID         => LN_BANK_ID,
         X_RETURN_STATUS   => LC_RETURN_STATUS,
         X_MSG_COUNT       => LN_MSG_COUNT,
         X_MSG_DATA        => LC_MSG_DATA,
         X_RESPONSE        => LR_RESPONSE_REC);

      LC_OUTPUT := ' ';

      IF (LC_RETURN_STATUS <> 'S')
      THEN
         FOR I IN 1 .. LN_MSG_COUNT
         LOOP
            APPS.FND_MSG_PUB.GET (I,
                                  APPS.FND_API.G_FALSE,
                                  LC_MSG_DATA,
                                  LC_MSG_DUMMY);

            LC_OUTPUT :=
               LC_OUTPUT
               || (TO_CHAR (I) || ': ' || SUBSTR (LC_MSG_DATA, 1, 250));
         END LOOP;

         APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.OUTPUT,
                                 'Error Occured while Creating Bank: ');
      END IF;

      IF (LC_RETURN_STATUS = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

   And  Here is a Query that u can use to see uploaded banks.

SELECT BankParty.*
  FROM HZ_PARTIES BankParty, HZ_CODE_ASSIGNMENTS BankCA
 WHERE     BankParty.PARTY_TYPE = 'ORGANIZATION'
       AND NVL (BankParty.status, 'A') = 'A'
       AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')
       AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
       AND NVL (BankCA.STATUS, 'A') = 'A'
       AND bankparty.created_by = -1;

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.