Thursday 26 July 2012

Return for correction in Oracle Notifications R12



Return for correction is the link in the notification page to send/return the control to the submitter for correction. This link navigates to the some other OAF page. It is not the button in the notification page (which comes from workflow lookup code of result type/lookup type).


If we click on the return for correction link the below page appears:






The return for correction link can be found in HR approval notifications like Leave approval, Competency approval, adieu/termination approval, PMS (Performance Management System) reviewer notification etc…

Issue:
Generally we may face issue whenever we click on the return for correction link; it navigates to the open notifications instead of opening the new page to submit for correction.


1. Causes of the issue:
  • Submitter and the Approver are same (say if a person submits his/her leave to  himself/herself)
  • Delegates/reassign the ownership of the notification (It depends on the original recipient of the notification)
  • Request for more info (may work, it depends on customizations on original recipient)

NOTE:
It is perfect perception from Oracle side because there is no point doing return for correction when the submitter and approver are same. But this issue may occur whenever delegations/reassigning the ownerships/request for more info and customizations are done.


2. Issue when the standard Oracle product is customized:
Example: Performance Management System (PMS)
  As per Oracle set KRA/appraisals goes to the supervisor of the employee. If the product is customized in such a way that all set KRAs/appraisals should go for approval on the basis of employee selection from the adhoc approver.
 
     In order to achieve the above requirement, we need to write custom logic and should customize the workflow (HR-Appraisal process).

   Process:  The employee sends his/her appraisal to Main Appraiser and if he/she is not the right person, Main appraiser sends it back to the employee to select the new main appraiser. If the employee selects the correct approver (new main appraiser), approver sends it to the reviewer to review the appraisal. Issue may occur in the reviewer notification* when reviewer tries to click on return for correction.

*In R12, Self Service Generic Notification Process is common for most of the HR approvals.
  As this is common for all, issue may occur in various processes like Leave, Termination, Competencies, PMS reviewer etc…


Solution for the Issue1:

1. Get the notification ID of the oracle notification in which you are facing problem return for correction.
2. Updating original recipient column in wf_notifications table to any other user name for the noted notification id.

Explanation:
 Whenever you click on return for correction link it internally calls the queries (a and d below) in the ReturnForCorrectionCO. Query a should return ‘N’. Query d editallowed field should return ‘Y’. If the query returns null or ‘N’ the page navigates to the work list i.e notifications.


Solution for the Issue2 when customization is done:

Check the values of the below workflow attributes of HR workflow.

Workflow Attributes:

Approval Creator Username   
Approval Creator ID                
Approval Creator Display Name   

Forward From Display Name 
Forward From ID                   
Forward From Username       

Creator Person ID                  
Creator Person Username 
Creator Person Display Name 

Main Appraiser Person Id   
Main Appraiser User Name   
Main Appraiser Display Name  

All attributes should have the new main appraiser person ID, User Name and Full Name values.
If not, Update attribute values in the workflow.
Steps to update attribute values:
1.       Get the item key using notification id from wf_notifications table
2.       Open status monitor using item type HR(HRSSA) and item key
3.       Select workflow details à Update attributes at the bottom of the page
4.       Change the values and apply


OAF:
oracle.apps.pqh.selfservice.workflow.webui.ReturnForCorrectionCO
oracle.apps.pqh.selfservice.workflow.server.ReturnForCorrectionAM


Queries:

a)
select
pqh_ss_workflow.complete_custom_rfc(14747838) from dual

b)
select transaction_id,item_key from hr_api_transactions where creator_person_id=35406
and transaction_ref_table='PER_APPRAISALS'

c)
select * from wf_notifications where notification_id =20386415 -- Get Item Key and compare with the above item key

Parameters for the query:
1,4 – Trasnaction ID
2,3 –  Notification ID
5    – Orig System (Ex: PER)
         select hr_approval_ss.getuserorigsystemid(null,:Notification_id) from dual
6    –  Orig system ID
         Person ID of the notification owner
         select hr_approval_ss.getuserorigsystemid(null,:Notification_id) from dual


d)
SELECT   ROWNUM - 1 seq, rfc.*
    FROM (SELECT   pah.action test_action, hat.transaction_id, hat.item_type,
                   hat.item_key, pah.approval_history_id, pah.user_name,
                   NVL
                      (pah.orig_system,
                       hr_approval_ss.getuserorigsystem (pah.user_name)
                      ) orig_system,
                   NVL
                      (pah.orig_system_id,
                       hr_approval_ss.getuserorigsystemid (pah.user_name)
                      ) orig_system_id,
                   hr_approval_ss.getroledisplayname
                                              (pah.user_name,
                                               pah.orig_system,
                                               pah.orig_system_id
                                              ) person_name,
                   hl.meaning action,
                   hr_approval_ss.isapprovereditallowed
                                             (hat.transaction_id,
                                              pah.user_name,
                                              pah.orig_system,
                                              pah.orig_system_id
                                             ) editallowed
              FROM hr_api_transactions hat,
                   pqh_ss_approval_history pah,
                   hr_lookups hl
             WHERE hat.transaction_id = :1
               AND pah.transaction_history_id = hat.transaction_id
               AND pah.action IN
                          ('APPROVED', 'SUBMIT', 'APPROVED_EDIT', 'RESUBMIT')
               AND pah.approval_history_id <> 0
               AND pah.approval_history_id <=
                      NVL
                         ((SELECT MAX (approval_history_id) + 1
                             FROM pqh_ss_approval_history ph
                            WHERE ph.transaction_history_id =
                                                    pah.transaction_history_id
                              AND ph.orig_system =
                                     hr_approval_ss.getuserorigsystem (NULL,
                                                                       :2
                                                                      )
                              AND ph.orig_system_id =
                                     hr_approval_ss.getuserorigsystemid (NULL,
                                                                         :3
                                                                        )
                              AND ph.approval_history_id <> 0),
                          999999999
                         )
               AND hl.lookup_type = 'PQH_SS_APPROVAL_STATUS'
               AND hl.lookup_code = pah.action
               AND pah.approval_history_id IN (
                      SELECT MAX (p1.approval_history_id)
                        FROM pqh_ss_approval_history p1
                       WHERE p1.transaction_history_id = :4
                         AND p1.action <> 'RFC')
               AND pah.orig_system_id <>
                                  hr_approval_ss.getuserorigsystemid (NULL,:5) – This condition may not satisfy whenever the submitter and approver are same
          ORDER BY approval_history_id) rfc
   WHERE rfc.orig_system = NVL (:6, rfc.orig_system)
     AND rfc.orig_system_id = NVL (:7, rfc.orig_system_id)
     AND rfc.editallowed = 'Y'
ORDER BY approval_history_id DESC;






Thanks,
Rajesh





Wednesday 4 January 2012

Project Events API with prerequisites


Hi,

HAPPY NEW YEAR 2012...

This post is going to explain you about the Project Events Loading using API.

API Name:  pa_event_pub.create_event

How to create Events in Oracle Apps?
  Navigation: Oracle Project User Responsibility -- Billing -- Events -- Project (Enter Project Name)  -- New

Event Type, Organization, Description, Currency, Bill Amount and Revenue Amount are mandatory fields.


How to create Events using API?

Most of the steps are common as compared to the normal data loading process using APIs. But there are few mandatory steps to be noted before loading the project events data.

Those are
  1. Initialize the apps using mo_global or fnd_global, If the package/procedure is calling from sql
  2. Make sure you use the same responsibility from the back end which you used while successfully creating project from front end.

    But if you have to use custom responsibility,
    then make sure you have the following sub-functions attached to the menu which is attached to your custom responsibility

    These are standard sub-functions attached to the Project responsibility.

    PA_PM_ADD_TASK  -- Activity Management Gateway:Add Task 
    PA_PM_CREATE_PROJECT -- Activity Management Gateway:Create Project
    PA_PM_UPDATE_PROJECT -- Activity Management Gateway:Update Project
    PA_PM_UPDATE_TASK -- Activity Management Gateway:Update Task

           Until unless the above functions are attached to the responsibility we can not create events using API. We may get the return_status as ‘E’ (which refers to the error in API).

  1. Event Loader program needs to be run separately for each and every operating unit. The events related to the respective operating unit’s data are only uploaded.
  2. Enable Operating Unit LOV (List of Values) while submitting the concurrent program. [Explained in the post “Setup to be done to support Multi-Org Views” – Published on 22nd November, 2011]
  3. If you enable/use descriptive flex field while creating the event, then you should pass the DFF name and attribute values for the API.
Ex: I have used two attributes in the ‘Events’ DFF.
So p_desc_flex_name := 'PA_EVENTS_DESC_FLEX'; -- Check it in Sample code

To get the above value, Click on the DFF, get the title of the DFF and execute the following query:

select DESCRIPTIVE_FLEXFIELD_NAME from FND_DESCRIPTIVE_FLEXS_TL where title = 'Events';      



Sample Code:

/*********************************************************************
   --      CUSTOM PROCEDURE TO CREATE PROJECT EVENTS
   ***********************************************************************/
   PROCEDURE create_events (
      errbuf    OUT NOCOPY   VARCHAR2,
      retcode   OUT NOCOPY   NUMBER
   )
   IS
      CURSOR cur(p_org_id IN NUMBER)
      IS
         SELECT   sped.*
             FROM son_project_events_data sped
            WHERE sped.attribute4 IS NULL
                  and   sped.org_id = p_org_id
         ORDER BY sped.sno;


      l_cur_rec             son_project_events_data%rowtype;
      l_error               VARCHAR2 (300);
      l_msg_count           NUMBER;
      l_msg_data            VARCHAR2 (2000);
      l_return_status       VARCHAR2 (10);
      l_data                VARCHAR2 (2000);
      l_msg_index_out       NUMBER;
      l_event_in_tbl        pa_event_pub.event_in_tbl_type;
      l_event_out_tbl       pa_event_pub.event_out_tbl_type;
      ln_line_no            NUMBER                                := 0;
      l_ref_no              NUMBER;
      l_rowcount            NUMBER;
      l_index               NUMBER                                := 0;
      l_reference           VARCHAR2 (100);
      l_event_id            NUMBER;
      l_status              VARCHAR2 (100);
      l_event_type          pa_event_types.event_type%TYPE;
      l_project_number      pa_projects_all.segment1%TYPE;
      l_org_name            hr_all_organization_units.NAME%TYPE;
      l_currency            fnd_currencies.currency_code%TYPE;
      l_task_number         pa_tasks.task_number%TYPE:= NULL;
      l_validation_flag     VARCHAR2 (1);
      l_validation_errors   NUMBER  := 0;
        l_cursor_rowcount         NUMBER;

        ln_user_id                      NUMBER    := fnd_profile.VALUE ('USER_ID');
        ln_user_name                     fnd_user.user_name%TYPE;
      ln_responsibility_id            NUMBER    := fnd_profile.VALUE ('RESP_ID');
        ln_responsibility_name          fnd_responsibility_tl.RESPONSIBILITY_NAME%TYPE;
      ln_resp_appl_id                 NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
      ln_org_id                       NUMBER := NVL (mo_global.get_current_org_id, fnd_profile.VALUE ('ORG_ID'));
        ln_org_name                     hr_operating_units.name%TYPE;
        l_project_currency_code         fnd_currencies.currency_code%TYPE;


   BEGIN

            -- This is not required when you are registering the code as concurrent program 
              fnd_global.apps_initialize (ln_user_id,
                                  ln_responsibility_id,
                                  ln_resp_appl_id
                                 );


        begin

              select user_name
              into   ln_user_name
              from   fnd_user
              where  user_id = ln_user_id;


               select responsibility_name
               into   ln_responsibility_name
               from   fnd_responsibility_tl
               where  responsibility_id = ln_responsibility_id;

               select name
               into   ln_org_name
               from   hr_operating_units
               where  organization_id = ln_org_id;

             exception when others then
           fnd_file.put_line(fnd_file.LOG,'Error while getting profile option values-User Name/Responsibility Name/Operating Unit for the Org ID: '||ln_org_id);
             end;

                  fnd_file.put_line(fnd_file.LOG,'Logged in User: '||ln_user_name);
                  fnd_file.put_line(fnd_file.LOG,'Responsibility Name: '||ln_responsibility_name);
                  fnd_file.put_line(fnd_file.LOG,'Appl ID: '||ln_resp_appl_id);
                  fnd_file.put_line(fnd_file.LOG,'Operating Unit: '||ln_org_name);
                  fnd_file.put_line(fnd_file.LOG,'Org ID: '||ln_org_id);

                          -- This is not required when you are registering the code as concurrent program
                          mo_global.set_policy_context ('S',ln_org_id);
                  commit;

      OPEN cur(ln_org_id);
      LOOP
             fetch cur into l_cur_rec;

                --  fnd_file.put_line(fnd_file.LOG,'Number of rows fetched: '||cur%rowcount);
                          IF cur%rowcount = 0 THEN
                           -- l_cursor_rowcount := 0;
                    fnd_file.put_line
                               (fnd_file.LOG,'There are no records to fetch hence events have not created');
                            RETURN;
                          END IF;

                          exit when cur%notfound;

         SELECT son_proj_event_ref_s.NEXTVAL
           INTO l_ref_no
           FROM DUAL;

         ------------------------ Event Type Validation --------------------------------------------------------------
         IF l_cur_rec.event_type IS NOT NULL
         THEN
            BEGIN
               SELECT event_type
                 INTO l_event_type
                 FROM pa_event_types
                WHERE event_type = l_cur_rec.event_type
                  AND TRUNC (SYSDATE) BETWEEN start_date_active
                                          AND NVL (end_date_active, SYSDATE);

               l_validation_flag := 'Y';
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  fnd_file.put_line(fnd_file.LOG,
                         'Event type validation is failed for the Event Type: '
                      || l_cur_rec.event_type||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno
                     );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
               WHEN OTHERS
               THEN
                  fnd_file.put_line
                     (fnd_file.LOG,
                         'Other error occurred while validating Event Type: '
                      || l_cur_rec.event_type||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno
                     );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Event Type is NULL in the line: ' || l_cur_rec.sno
                              );
            l_validation_flag := 'N';
            l_validation_errors := l_validation_errors + 1;
         END IF;

         ------------------------ Project Number Validation -------------------------------------------------------------
         IF l_cur_rec.project_number IS NOT NULL
         THEN
            BEGIN
               SELECT segment1,project_currency_code
                 INTO l_project_number,l_project_currency_code
                 FROM pa_projects_all
                WHERE segment1 = l_cur_rec.project_number
                        and   org_id = ln_org_id
                        and  project_status_code in ('APPROVED','1002')
                  AND TRUNC (SYSDATE) BETWEEN start_date
                                          AND NVL (completion_date, SYSDATE);

               l_validation_flag := 'Y';
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  fnd_file.put_line
                             (fnd_file.LOG,
                                 'Validation failed for the Project Number: '
                              || l_cur_rec.project_number
                             );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
               WHEN OTHERS
               THEN
                  fnd_file.put_line
                     (fnd_file.LOG,
                         'Other error occured while validating Project Number: '
                      || l_cur_rec.project_number
                     );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Project Number is NULL in the line: ' || l_cur_rec.sno
                              );
            l_validation_flag := 'N';
            l_validation_errors := l_validation_errors + 1;
         END IF;

         ------------------------ Organixation Name Validation ------------------------------------------------------------
         IF l_cur_rec.org_name IS NOT NULL
         THEN
            BEGIN
               SELECT NAME
                 INTO l_org_name
                 FROM hr_all_organization_units
                WHERE TRUNC (SYSDATE) BETWEEN date_from AND NVL (date_to,
                                                                 SYSDATE
                                                                )
                  AND NAME = l_cur_rec.org_name;

               l_validation_flag := 'Y';
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  fnd_file.put_line
                               (fnd_file.LOG,
                                   'Validation failed for the Organization: '
                                || l_cur_rec.org_name||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno
                               );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
               WHEN OTHERS
               THEN
                  fnd_file.put_line
                     (fnd_file.LOG,
                         'Other error occured while validating Organization: '
                      || l_cur_rec.org_name||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno
                     );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                                  'Organization Name is NULL in the line: '
                               || l_cur_rec.sno
                              );
            l_validation_flag := 'N';
            l_validation_errors := l_validation_errors + 1;
         END IF;

------------------------- Currency Validation -------------------------------------------------------------
         IF l_cur_rec.currency IS NOT NULL
         THEN
               -- Not validating billing currency with project functional currency because there may be cases
            BEGIN
               SELECT currency_code
                 INTO l_currency
                 FROM fnd_currencies
                WHERE TRUNC (SYSDATE) BETWEEN NVL(start_date_active,SYSDATE-1)
                                          AND NVL (end_date_active, SYSDATE)
                  AND currency_code = l_cur_rec.currency;
                         
                         
                  -- Commented because project functional currency need not be same as the project event billing currency
                          /*IF l_project_currency_code = l_cur_rec.currency THEN
                        l_validation_flag := 'Y';
                          ELSE
                                fnd_file.put_line
                                   (fnd_file.LOG,
                                       'Validation mismatch for the Currency: '
                                    || l_cur_rec.currency || ' with Project Currency: '||l_project_currency_code||' for the project: '||l_cur_rec.project_number||'in SNo: '||l_cur_rec.sno
                                   );
                     l_validation_flag := 'N';
                     l_validation_errors := l_validation_errors + 1;
                          END IF;*/

            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  fnd_file.put_line
                                   (fnd_file.LOG,
                                       'Validation failed for the Currency: '
                                    || l_cur_rec.currency||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno
                                   );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
               WHEN OTHERS
               THEN
                  fnd_file.put_line
                        (fnd_file.LOG,
                            'Other error occured while validating Currency: '
                         || l_cur_rec.currency||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno
                        );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Currency  is NULL in the line: ' || l_cur_rec.sno
                              );
            l_validation_flag := 'N';
            l_validation_errors := l_validation_errors + 1;
         END IF;

------------------------Task Validation --------------------------------------------------------------
-- Commented because Task is not mandatory for creating project events.
-- Client is not using task while creating project events, so made it as null while initiating l_task_number
-- Enable this code depends on the requirement

 /*        IF l_cur_rec.task_number IS NOT NULL
         THEN
            BEGIN
               SELECT task_number
                 INTO l_task_number
                 FROM pa_tasks
                WHERE TRUNC (SYSDATE) BETWEEN start_date
                                          AND NVL (completion_date, SYSDATE)
                  AND task_number = l_cur_rec.task_number;

               l_validation_flag := 'Y';
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  fnd_file.put_line
                                (fnd_file.LOG,
                                    'Validation failed for the Task Number: '
                                 || l_cur_rec.task_number
                                );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
               WHEN OTHERS
               THEN
                  fnd_file.put_line
                     (fnd_file.LOG,
                         'Other error occured while validating Task Number: '
                      || l_cur_rec.task_number
                     );
                  l_validation_flag := 'N';
                  l_validation_errors := l_validation_errors + 1;
            END;
         ELSE
            l_task_number := NULL;
         END IF;*/

--------------------------------------------------------------------------------------
         IF l_validation_flag = 'Y'
         THEN



                dbms_output.put_line('Task:'||l_task_number);
                  dbms_output.put_line('Event Type: '||l_event_type);
                  dbms_output.put_line('Completion date: '||l_cur_rec.event_date);
                  dbms_output.put_line('Project Number: '||l_project_number);
                  dbms_output.put_line('Org Name: '||l_org_name);
                  dbms_output.put_line('Currency: '||l_currency);
                  dbms_output.put_line('l_ref_no: '||l_ref_no);


            ln_line_no := ln_line_no + 1;
            l_event_in_tbl (ln_line_no).p_pm_event_reference := l_ref_no;
            l_event_in_tbl (ln_line_no).p_task_number := l_task_number;
            l_event_in_tbl (ln_line_no).p_event_type := l_event_type;
            l_event_in_tbl (ln_line_no).p_description := l_cur_rec.description;
            l_event_in_tbl (ln_line_no).p_completion_date := l_cur_rec.event_date;
                  l_event_in_tbl (ln_line_no).p_desc_flex_name := 'PA_EVENTS_DESC_FLEX';
                  --l_event_in_tbl (ln_line_no).P_attribute_category := 'Events';
                  l_event_in_tbl (ln_line_no).p_attribute1 := l_cur_rec.attribute1;
                  l_event_in_tbl (ln_line_no).p_attribute2 := l_cur_rec.attribute2;
            l_event_in_tbl (ln_line_no).p_project_number := l_project_number;
            l_event_in_tbl (ln_line_no).p_organization_name := l_org_name; -- Project owning organization
            l_event_in_tbl (ln_line_no).p_bill_trans_bill_amount :=  l_cur_rec.bill_amount;
            l_event_in_tbl (ln_line_no).p_bill_trans_rev_amount := l_cur_rec.revenue_amount;
            l_event_in_tbl (ln_line_no).p_bill_trans_currency_code :=  l_currency;
         -- Project Functional currency
         END IF;

     END LOOP;
       close cur;

      -- IF l_cursor_rowcount <> 0 THEN
      IF l_validation_errors = 0
      THEN
           fnd_file.put_line(fnd_file.LOG,'All the records are validated successfully');
         fnd_file.put_line(fnd_file.LOG,'Calling API....');
         pa_event_pub.init_event;
         pa_event_pub.create_event (p_api_version_number      => 1.0,
                                    p_commit                  => fnd_api.g_false,
                                    p_init_msg_list           => fnd_api.g_true,
                                    p_pm_product_code         => 'DATAMIG', -- Data Migration
                                    p_event_in_tbl            => l_event_in_tbl,
                                    p_event_out_tbl           => l_event_out_tbl,
                                    p_msg_count               => l_msg_count,
                                    p_msg_data                => l_msg_data,
                                    p_return_status           => l_return_status
                                   );
         fnd_file.put_line (fnd_file.LOG,
                            'API message: ' || NVL(l_msg_data,'Nothing to display')
                           );
         fnd_file.put_line (fnd_file.LOG,
                            'Return Status of API is: ' || l_return_status
                           );
         DBMS_OUTPUT.put_line ('After API: ' || l_msg_data);
         DBMS_OUTPUT.put_line ('Return Status: ' || l_return_status);

         IF l_return_status = 'S'
         THEN

                  UPDATE son_project_events_data
                  SET attribute4 = 'Event Created'
                WHERE attribute4 IS NULL
                        and   org_id = ln_org_id;

               fnd_file.put_line (fnd_file.LOG,'Events created successfully and updated the custom table');

         ELSE

                  UPDATE son_project_events_data
            SET attribute4 = 'API Error'
            WHERE attribute4 IS NULL
                  and   org_id = ln_org_id;

            fnd_file.put_line (fnd_file.LOG, 'Program ends up with Error');
            DBMS_OUTPUT.put_line ('Program ends up with Error');

         END IF;                                       --l_return_status = 'S'
      ELSE  -- l_validation_errors =0

                  UPDATE son_project_events_data
            SET attribute4 = 'Validation Failed'
            WHERE attribute4 IS NULL
                  and   org_id = ln_org_id;


         fnd_file.put_line
                    (fnd_file.LOG,
                     'Events have not created because of validation failures'
                    );
      END IF;     -- l_validation_errors = 0

      COMMIT;


      -- ELSE --l_rowcount <> 0
     --    fnd_file.put_line(fnd_file.LOG,'There are no records to fetch hence events have not created');
      -- END IF; --l_rowcount <> 0
  /* FOR i IN 1 .. l_msg_count
   LOOP
      apps.pa_interface_utils_pub.get_messages
                                          (p_encoded            => 'F',
                                           p_msg_count          => l_msg_count,
                                           p_msg_data           => l_msg_data,
                                           p_data               => l_data,
                                           p_msg_index          => i,
                                           p_msg_index_out      => l_msg_index_out
                                          );

   END LOOP;
  fnd_file.put_line(fnd_file.LOG,'From pa_interface_utils_pub.get_messages: ' || l_data);
  DBMS_OUTPUT.put_line ('From pa_interface_utils_pub.get_messages: ' || l_data);   */
--   dbms_output.put_line('Before Commit');
   EXCEPTION
      WHEN OTHERS
      THEN
         l_error := SUBSTR (SQLERRM, 1, 200);
         fnd_file.put_line (fnd_file.LOG,
                            'Other Error while creating event: ' || l_error
                           );
   END create_events;


 *********************************************************************************





Thanks,
Rajesh