How To Run Concurrent Program From Back end (PLSQL) and notify to USER by FND_REQUEST.ADD_NOTIFICATION

/*How To Run Concurrent Program From Back end (PLSQL) and notify to USER by  FND_REQUEST.ADD_NOTIFICATION*/

PROCEDURE submit_appl_host_prgm_p (
   errbuf         OUT          VARCHAR2,
   retcode        OUT          NUMBER,
   p_request_id   OUT NOCOPY   NUMBER
)
IS
   lv_request_id    NUMBER := 0;
   l_user_id        NUMBER;
   l_resp_id        NUMBER;
   l_resp_appl_id   NUMBER;
   l_notify_user  boolean;
BEGIN
   xxpk_debug_prc ('xxeaa_qnb_host2host_pkg.submit_appl_host_prgm_p',
                   'Enterred',
                   '00',
                   NULL,
                   SUBSTR (SQLERRM, 1, 299)
                  );

   SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
     INTO l_user_id, l_resp_id, l_resp_appl_id
     FROM fnd_user fnd, fnd_responsibility_tl fresp
    WHERE fnd.user_name = 'MANNAI.TECH'                             --SYSADMIN
      AND fresp.responsibility_name = 'Application Developer';
                               --Check Before MTP Valid Resposibility and User

   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => l_resp_id,
                               resp_appl_id      => l_resp_appl_id
                              );
  l_notify_user := FND_REQUEST.ADD_NOTIFICATION ('MANNAI.TECH');                           
   lv_request_id :=
      fnd_request.submit_request (application      => 'SQLAP',
                                  program          => 'XXEAA_SEND_PAYFILE2QNB',
                                  description      => NULL,
                                  start_time       => NULL,
                                  sub_request      => FALSE,
                                  argument1        => 'BLANK',
                                  argument2        => CHR (0),
                                  argument3        => CHR (0),
                                  argument4        => CHR (0),
                                  argument5        => CHR (0),
                                  argument6        => CHR (0),
                                  argument7        => CHR (0),
                                  argument8        => CHR (0),
                                  argument9        => CHR (0),
                                  argument10       => CHR (0),
                                  argument11       => CHR (0),
                                  argument12       => CHR (0),
                                  argument13       => CHR (0),
                                  argument14       => CHR (0),
                                  argument15       => CHR (0),
                                  argument16       => CHR (0),
                                  argument17       => CHR (0),
                                  argument18       => CHR (0),
                                  argument19       => CHR (0),
                                  argument20       => CHR (0),
                                  argument21       => CHR (0),
                                  argument22       => CHR (0),
                                  argument23       => CHR (0),
                                  argument24       => CHR (0),
                                  argument25       => CHR (0),
                                  argument26       => CHR (0),
                                  argument27       => CHR (0),
                                  argument28       => CHR (0),
                                  argument29       => CHR (0),
                                  argument30       => CHR (0),
                                  argument31       => CHR (0),
                                  argument32       => CHR (0),
                                  argument33       => CHR (0),
                                  argument34       => CHR (0),
                                  argument35       => CHR (0),
                                  argument36       => CHR (0),
                                  argument37       => CHR (0),
                                  argument38       => CHR (0),
                                  argument39       => CHR (0),
                                  argument40       => CHR (0),
                                  argument41       => CHR (0),
                                  argument42       => CHR (0),
                                  argument43       => CHR (0),
                                  argument44       => CHR (0),
                                  argument45       => CHR (0),
                                  argument46       => CHR (0),
                                  argument47       => CHR (0),
                                  argument48       => CHR (0),
                                  argument49       => CHR (0),
                                  argument50       => CHR (0),
                                  argument51       => CHR (0),
                                  argument52       => CHR (0),
                                  argument53       => CHR (0),
                                  argument54       => CHR (0),
                                  argument55       => CHR (0),
                                  argument56       => CHR (0),
                                  argument57       => CHR (0),
                                  argument58       => CHR (0),
                                  argument59       => CHR (0),
                                  argument60       => CHR (0),
                                  argument61       => CHR (0),
                                  argument62       => CHR (0),
                                  argument63       => CHR (0),
                                  argument64       => CHR (0),
                                  argument65       => CHR (0),
                                  argument66       => CHR (0),
                                  argument67       => CHR (0),
                                  argument68       => CHR (0),
                                  argument69       => CHR (0),
                                  argument70       => CHR (0),
                                  argument71       => CHR (0),
                                  argument72       => CHR (0),
                                  argument73       => CHR (0),
                                  argument74       => CHR (0),
                                  argument75       => CHR (0),
                                  argument76       => CHR (0),
                                  argument77       => CHR (0),
                                  argument78       => CHR (0),
                                  argument79       => CHR (0),
                                  argument80       => CHR (0),
                                  argument81       => CHR (0),
                                  argument82       => CHR (0),
                                  argument83       => CHR (0),
                                  argument84       => CHR (0),
                                  argument85       => CHR (0),
                                  argument86       => CHR (0),
                                  argument87       => CHR (0),
                                  argument88       => CHR (0),
                                  argument89       => CHR (0),
                                  argument90       => CHR (0),
                                  argument91       => CHR (0),
                                  argument92       => CHR (0),
                                  argument93       => CHR (0),
                                  argument94       => CHR (0),
                                  argument95       => CHR (0),
                                  argument96       => CHR (0),
                                  argument97       => CHR (0),
                                  argument98       => CHR (0),
                                  argument99       => CHR (0),
                                  argument100      => CHR (0)
                                 );
   COMMIT;
    xxpk_debug_prc ('xxeaa_qnb_host2host_pkg.submit_appl_host_prgm_p',
                      'lv_request_id -> '||to_char(lv_request_id),
                      '01',
                      NULL,
                      SUBSTR (SQLERRM, 1, 299)
                     );
   p_request_id := lv_request_id;
EXCEPTION
   WHEN OTHERS
   THEN
      xxpk_debug_prc ('xxeaa_qnb_host2host_pkg.submit_appl_host_prgm_p',
                      'Main Exception',
                      'ZZ',
                      NULL,
                      SUBSTR (SQLERRM, 1, 299)
                     );
      p_request_id := 0;
END submit_appl_host_prgm_p;

Some Imp Table

1) GL_AUTHORIZATION_LIMITS--GL Approval Limits

AR Invoice distribution line update API

/*AR Invoice distribution's Revenue Account Update API*/
/* Formatted on 2020/07/19 19:54 (Formatter Plus v4.8.8) */
DECLARE
   l_backout_flag               BOOLEAN;
   lout_backout_done_flag       BOOLEAN;
   p_cust_trx_line_gl_dist_id   NUMBER;
   l_customer_trx_id            NUMBER;
   l_header_gl_date             DATE;
   l_trx_date                   DATE;
   l_invoicing_rule_id          NUMBER;
   l_user_id                    NUMBER;
   l_resp_id                    NUMBER;
   l_resp_appl_id               NUMBER;
   r_rctgld                     ra_cust_trx_line_gl_dist%ROWTYPE;
l_old_ccid number;  
l_new_required_ccid number;
   CURSOR c_dist_line
   IS
      SELECT rctgd.*
        FROM ra_cust_trx_line_gl_dist_all rctgd
       WHERE rctgd.cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id;
BEGIN
   BEGIN
      SELECT rctgd.created_by, rcta.customer_trx_id, rcta.trx_date,
             rctgd.gl_date, rcta.invoicing_rule_id
             ,rctgd.CODE_COMBINATION_ID
        INTO l_user_id, l_customer_trx_id, l_trx_date,
             l_header_gl_date, l_invoicing_rule_id,l_old_ccid
        FROM ra_cust_trx_line_gl_dist_all rctgd, ra_customer_trx_all rcta
       WHERE 1 = 1
         AND rctgd.customer_trx_id = rcta.customer_trx_id
         AND rctgd.cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
         and rctgd.ACCOUNT_CLASS='REV' ;
   END;

   BEGIN
      SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
        INTO l_user_id, l_resp_id, l_resp_appl_id
        FROM fnd_user fnd,
             fnd_responsibility_tl fresp,
             fnd_user_resp_groups_direct furg
       WHERE 1 = 1
         AND furg.user_id = fnd.user_id
         AND furg.responsibility_id = fresp.responsibility_id
         AND fnd.user_id = l_user_id
         AND UPPER (fresp.responsibility_name) LIKE '%MTC%REC%USE%'
         AND TRUNC (SYSDATE) BETWEEN furg.start_date
                                 AND NVL (furg.end_date, TRUNC (SYSDATE + 2))
         AND furg.start_date IS NOT NULL
         AND ROWNUM < 2;
--select * from fnd_user_resp_groups_direct        furg where furg.START_DATE is null

   --select * from fnd_responsibility_tl fresp where fresp.responsibility_name  like 'MTC%Rec%Use%'
   END;
  


   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => l_resp_id,
                               resp_appl_id      => l_resp_appl_id
                              );

   FOR i IN c_dist_line
   LOOP
   begin
  
   select gcc_all segments as itis and seg3 is required Account into l_new_required_ccid
   from gl_code_combinations gcc
   where gcc.CODE_COMBINATION_ID=l_old_ccid;
   end;
      r_rctgld := i.*;
      r_rctgld.CODE_COMBINATION_ID:=l_new_required_ccid--
      arp_process_dist.update_dist
         (p_form_name                     => 'ARXTWMAI',
          p_form_version                  => NULL,
          p_backout_flag                  => l_backout_flag,
                       --looks have the relation with posting , have to verify
          p_cust_trx_line_gl_dist_id      => p_cust_trx_line_gl_dist_id,
          p_customer_trx_id               => l_customer_trx_id,
          p_dist_rec                      => r_rctgld,
                             --IN OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype,
          p_header_gl_date                => l_header_gl_date,
          p_trx_date                      => l_trx_date,
          p_invoicing_rule_id             => l_invoicing_rule_id,
          p_backout_done_flag             => lout_backout_done_flag,
          p_exchange_rate                 => NULL,
          p_currency_code                 => NULL,
          p_precision                     => NULL,
          p_mau                           => NULL
         );
      COMMIT;
   END LOOP;
END;

PMS Objectives Mandatory R12 User Hook Implementation Steps

PMS Objectives Mandatory R12 User Hook Implementation Steps

1) Create package with the required procedure XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B
--The parameters must be same with same sequence (as it is with seeded hook API pkg.procedure
--here seeded pkg procedure is HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B)

2)--Get API_MODULE_ID
-- Choose the exact Module 
SELECT *
  FROM  hr_api_modules
WHERE  api_module_type = 'BP'
     AND  module_name LIKE '%UPDATE_APPRAISAL%'; --1297

3)-- get API_HOOK_ID
 -- Choose w.r.t. the exact API_Module_ID  

select * from  hr_api_hooks ahk where API_HOOK_TYPE='BP' AND API_MODULE_ID=1297--2925

4) --create_api_hook_call

DECLARE
   l_api_hook_call_id        NUMBER;
   l_object_version_number   NUMBER;
BEGIN
   hr_api_hook_call_api.create_api_hook_call (
      p_validate                => FALSE,
      p_effective_date          => TO_DATE (SYSDATE),
      p_api_hook_id             => 2925,                                                
      p_api_hook_call_type      => 'PP',--PKG-Procedure
      p_sequence                => 3000,--always keep this 3000
      p_enabled_flag            => 'Y',
      p_call_package            => 'XXEAA_PMS_USERHOOK_PKG',
      p_call_procedure          => 'XXUPDATE_APPRAISAL_B',             
      p_api_hook_call_id        => l_api_hook_call_id,
      p_object_version_number   => l_object_version_number);
      COMMIT;
     
      dbms_output.put_line('l_api_hook_call_id:-'||l_api_hook_call_id);
      dbms_output.put_line(' l_object_version_number:-'|| l_object_version_number);
END;
/* Output
l_api_hook_call_id:-1180
 l_object_version_number:-1
*/

5) --Check Wheather Attached Or Not:
--ENABLED_FLAG must be Y and Status =
--------------------------------------------------------------------------------

select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'

6)
-- Run Pre-Processor:
--------------------------------------------------------------------------------

DECLARE
BEGIN
   hr_api_user_hooks_utility.create_hooks_one_module(1297); -- (Pass Application Module ID )  API_MODULE_ID==1297
  dbms_output.put_line('Pre-Processor Run Successfully');
  commit;
   exception
   when others then
  dbms_output.put_line(sqlerrm);
END;

/*Output
Pre-Processor Run Successfully
*/


7)
--Check status
--Preproceddor_date must not be null after run pre-processor
--and Status must be V & ENCODED_ERROR must be null

select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'
=================================================================
/* Formatted on 2020/07/14 23:35 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE xxeaa_pms_userhook_pkg
IS
   PROCEDURE xxupdate_appraisal_b (
      p_effective_date                 IN   DATE,
      p_appraisal_id                   IN   NUMBER,
      p_object_version_number          IN   NUMBER,
      p_appraiser_person_id            IN   NUMBER,
      p_appraisal_date                 IN   DATE,
      p_appraisal_period_end_date      IN   DATE,
      p_appraisal_period_start_date    IN   DATE,
      p_type                           IN   VARCHAR2,
      p_next_appraisal_date            IN   DATE,
      p_status                         IN   VARCHAR2,
      p_comments                       IN   VARCHAR2,
      p_overall_performance_level_id   IN   NUMBER,
      p_open                           IN   VARCHAR2,
      p_attribute_category             IN   VARCHAR2,
      p_attribute1                     IN   VARCHAR2,
      p_attribute2                     IN   VARCHAR2,
      p_attribute3                     IN   VARCHAR2,
      p_attribute4                     IN   VARCHAR2,
      p_attribute5                     IN   VARCHAR2,
      p_attribute6                     IN   VARCHAR2,
      p_attribute7                     IN   VARCHAR2,
      p_attribute8                     IN   VARCHAR2,
      p_attribute9                     IN   VARCHAR2,
      p_attribute10                    IN   VARCHAR2,
      p_attribute11                    IN   VARCHAR2,
      p_attribute12                    IN   VARCHAR2,
      p_attribute13                    IN   VARCHAR2,
      p_attribute14                    IN   VARCHAR2,
      p_attribute15                    IN   VARCHAR2,
      p_attribute16                    IN   VARCHAR2,
      p_attribute17                    IN   VARCHAR2,
      p_attribute18                    IN   VARCHAR2,
      p_attribute19                    IN   VARCHAR2,
      p_attribute20                    IN   VARCHAR2,
      p_system_type                    IN   VARCHAR2,
      p_system_params                  IN   VARCHAR2,
      p_appraisee_access               IN   VARCHAR2,
      p_main_appraiser_id              IN   NUMBER,
      p_assignment_id                  IN   NUMBER,
      p_assignment_start_date          IN   DATE,
      p_asg_business_group_id          IN   NUMBER,
      p_assignment_organization_id     IN   NUMBER,
      p_assignment_job_id              IN   NUMBER,
      p_assignment_position_id         IN   NUMBER,
      p_assignment_grade_id            IN   NUMBER,
      p_appraisal_system_status        IN   VARCHAR2,
      p_potential_readiness_level      IN   VARCHAR2,
      p_potential_short_term_workopp   IN   VARCHAR2,
      p_potential_long_term_workopp    IN   VARCHAR2,
      p_potential_details              IN   VARCHAR2,
      p_event_id                       IN   NUMBER,
      p_show_competency_ratings        IN   VARCHAR2,
      p_show_objective_ratings         IN   VARCHAR2,
      p_show_questionnaire_info        IN   VARCHAR2,
      p_show_participant_details       IN   VARCHAR2,
      p_show_participant_ratings       IN   VARCHAR2,
      p_show_participant_names         IN   VARCHAR2,
      p_show_overall_ratings           IN   VARCHAR2,
      p_show_overall_comments          IN   VARCHAR2,
      p_update_appraisal               IN   VARCHAR2,
      p_provide_overall_feedback       IN   VARCHAR2,
      p_appraisee_comments             IN   VARCHAR2,
      p_plan_id                        IN   NUMBER,
      p_offline_status                 IN   VARCHAR2,
      p_retention_potential            IN   VARCHAR2,
      p_show_participant_comments      IN   VARCHAR2
   );
END xxeaa_pms_userhook_pkg;
/

CREATE OR REPLACE PACKAGE BODY xxeaa_pms_userhook_pkg
IS
   PROCEDURE xxupdate_appraisal_b
--HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B (1297,2925)
                                 (
      p_effective_date                 IN   DATE,
      p_appraisal_id                   IN   NUMBER,
      p_object_version_number          IN   NUMBER,
      p_appraiser_person_id            IN   NUMBER,
      p_appraisal_date                 IN   DATE,
      p_appraisal_period_end_date      IN   DATE,
      p_appraisal_period_start_date    IN   DATE,
      p_type                           IN   VARCHAR2,
      p_next_appraisal_date            IN   DATE,
      p_status                         IN   VARCHAR2,
      p_comments                       IN   VARCHAR2,
      p_overall_performance_level_id   IN   NUMBER,
      p_open                           IN   VARCHAR2,
      p_attribute_category             IN   VARCHAR2,
      p_attribute1                     IN   VARCHAR2,
      p_attribute2                     IN   VARCHAR2,
      p_attribute3                     IN   VARCHAR2,
      p_attribute4                     IN   VARCHAR2,
      p_attribute5                     IN   VARCHAR2,
      p_attribute6                     IN   VARCHAR2,
      p_attribute7                     IN   VARCHAR2,
      p_attribute8                     IN   VARCHAR2,
      p_attribute9                     IN   VARCHAR2,
      p_attribute10                    IN   VARCHAR2,
      p_attribute11                    IN   VARCHAR2,
      p_attribute12                    IN   VARCHAR2,
      p_attribute13                    IN   VARCHAR2,
      p_attribute14                    IN   VARCHAR2,
      p_attribute15                    IN   VARCHAR2,
      p_attribute16                    IN   VARCHAR2,
      p_attribute17                    IN   VARCHAR2,
      p_attribute18                    IN   VARCHAR2,
      p_attribute19                    IN   VARCHAR2,
      p_attribute20                    IN   VARCHAR2,
      p_system_type                    IN   VARCHAR2,
      p_system_params                  IN   VARCHAR2,
      p_appraisee_access               IN   VARCHAR2,
      p_main_appraiser_id              IN   NUMBER,
      p_assignment_id                  IN   NUMBER,
      p_assignment_start_date          IN   DATE,
      p_asg_business_group_id          IN   NUMBER,
      p_assignment_organization_id     IN   NUMBER,
      p_assignment_job_id              IN   NUMBER,
      p_assignment_position_id         IN   NUMBER,
      p_assignment_grade_id            IN   NUMBER,
      p_appraisal_system_status        IN   VARCHAR2,
      p_potential_readiness_level      IN   VARCHAR2,
      p_potential_short_term_workopp   IN   VARCHAR2,
      p_potential_long_term_workopp    IN   VARCHAR2,
      p_potential_details              IN   VARCHAR2,
      p_event_id                       IN   NUMBER,
      p_show_competency_ratings        IN   VARCHAR2,
      p_show_objective_ratings         IN   VARCHAR2,
      p_show_questionnaire_info        IN   VARCHAR2,
      p_show_participant_details       IN   VARCHAR2,
      p_show_participant_ratings       IN   VARCHAR2,
      p_show_participant_names         IN   VARCHAR2,
      p_show_overall_ratings           IN   VARCHAR2,
      p_show_overall_comments          IN   VARCHAR2,
      p_update_appraisal               IN   VARCHAR2,
      p_provide_overall_feedback       IN   VARCHAR2,
      p_appraisee_comments             IN   VARCHAR2,
      p_plan_id                        IN   NUMBER,
      p_offline_status                 IN   VARCHAR2,
      p_retention_potential            IN   VARCHAR2,
      p_show_participant_comments      IN   VARCHAR2
   )
   IS
      lv_objectives_cnt   NUMBER := 0;
   BEGIN
      xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
                      'Enterred',
                      NULL,
                      '00',
                      SUBSTR (SQLERRM, 1, 399)
                     );

      BEGIN
         SELECT COUNT (1)
           INTO lv_objectives_cnt
           FROM per_objectives poe
          WHERE poe.appraisal_id = p_appraisal_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_objectives_cnt := 0;
      END;

      xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
                         'P_APPRAISAL_ID - P_ASSIGNMENT_ID :-> '
                      || TO_CHAR (p_appraisal_id)
                      || ' - '
                      || TO_CHAR (p_assignment_id),
                      'lv_objectives_cnt - ' || TO_CHAR (lv_objectives_cnt),
                      '01',
                      SUBSTR (SQLERRM, 1, 399)
                     );

      IF NVL (lv_objectives_cnt, 0) = 0
      THEN
         fnd_message.set_name ('PER', 'Objectives are mandatory !!');
         fnd_message.raise_error;
      END IF;
   END xxupdate_appraisal_b;
END xxeaa_pms_userhook_pkg;
/

Meaning of Statuses in HR_API_TRANSACTIONS Table

 Doc ID 438346.1
Here is a list of statuses and their meanings in hr_api_transactions table.

D - Deleted
C - Cancelled
E - Error
RI - Return for Correction
RIS - Return for Correction + Save For Later
S - Save for Later
W - Review page
Y - Pending Approval
YS - Pending Approval + Save for Later
AC - Approval Complete