CM-JOBS-ON-HOLD

 Put jobs on hold

----------------

Put Jobs on Hold

 

<<< Check Putty Spool Log >>>

SQL> spool Spool_Jobs_on_hold_09JAN2021.txt;

SQL> SELECT SYSTIMESTAMP FROM DUAL;

 

1.Connect as apps user, Drop old table (jobs_already_on_hold)

 

SQL> DROP TABLE jobs_already_on_hold;

 

2. List out the Jobs which were already on HOLD

 

SQL> SELECT request_id, phase_code, status_code

     FROM fnd_concurrent_requests

     WHERE hold_flag = 'Y';

 

3.Create backup of the table with the jobs which were already ON HOLD.

 

SQL> 

 

CREATE TABLE jobs_already_on_hold

AS

   (SELECT request_id, phase_code, status_code

      FROM fnd_concurrent_requests

     WHERE hold_flag = 'Y');

                

4. Verify the count from backup table with the above query output

 

SQL> SELECT COUNT (*) FROM jobs_already_on_hold;

 

SQL> SELECT * FROM jobs_already_on_hold;

 

5.Now, Place the pending jobs on HOLD using the below update command.

 

SQL> 

 

UPDATE fnd_concurrent_requests

   SET hold_flag = 'Y'

WHERE phase_code = 'P' AND hold_flag = 'N';

 

SQL> COMMIT;

 

6. After Putting the Jobs ON HOLD, now verify if any Running Requests with PAUSED Status.

   If there are any Running requests with PAUSED status, please verify the child requests status of this Program. 

   If there were any put on HOLD as part of the putting Jobs on Hold process.

   Release them from HOLD and process the requests.

  

7.Check again the pending scheduled program during the time frame 3 PM to 5 PM CST  

 

8.Please ensure to check the below query for every 5 minutes until it returns 0 rows.

  List the pending ,running and passed  requests

  

SQL> 

SELECT REQUEST_ID,

       PHASE_CODE,

       STATUS_CODE,

       HAS_SUB_REQUEST,

       IS_SUB_REQUEST,

       hold_flag,

       REQ_INFORMATION

  FROM apps.fnd_concurrent_requests

WHERE    (phase_code = 'P' AND hold_flag = 'N')

       OR phase_code = 'R'

       OR status_code = 'W';

##############################################USE THIS FOR RUNNIG REQUESTS##############################

SQL> col USER_CONCURRENT_PROGRAM_NAME for a30

SQL> col REQUESTOR for a10

SELECT request_id, --parent_request_id,      

         user_concurrent_program_name,

         --program,

        --(select responsibility_name from apps.fnd_responsibility_vl fr where fr.responsibility_id = fcs.responsibility_id)    responsibility_name,

      --   program_short_name,

         argument_text,

         requestor,

         phase_code,

         status_code,

      --   completion_text,

         to_char (actual_start_date, 'DD-MON-YY HH24:MI:SS') actual_start_date,

         to_char (actual_completion_date, 'DD-MON-YY HH24:MI:SS') actual_completion_date,

         NUMTODSINTERVAL (

            NVL (actual_completion_date, SYSDATE) - actual_start_date,

            'day')

            run_time,requested_start_date

    FROM apps.fnd_conc_req_summary_v fcs

WHERE 1=1

--and program like 'Requisition%Import%'

--AND FCS.REQUEST_ID = 99819130

--AND REQUESTOR = 'RMUTTA'

and PHASE_CODE = 'R'   -- Request Status

--AND STATUS_CODE <> 'Q'

order by  run_time desc;

 

** once activity is completed and start the application services and perform the sanity checks and release the on hold jobs

 

#####################################################################CHECK BEFORE RELEASE################

- Release Jobs from HOLD

connect as apps user and perform the below:

 

SQL> 

 

UPDATE fnd_concurrent_requests

   SET hold_flag = 'N'

WHERE     hold_flag = 'Y'

       AND request_id NOT IN (SELECT request_id FROM jobs_already_on_hold);

                   

SQL> COMMIT;

=====Cancel requests manually============================

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';

Release child requests

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

UPDATE fnd_concurrent_requests SET hold_flag = 'N' where REQUEST_ID='&REQUEST_ID';