How to cancel Scheduled Concurrent Programs in Oracle apps

From Front End:

1) Login with the same user, who scheduled the concurrent program
2) Go to view requests from the responsibility it's been scheduled -->View Requests--> Query for the program
3) You should get one record in scheduled state
4) Click on view details end date the schedule and cancel the request.


Select * from fnd_concurrent_programs
WHERE CONCURRENT_PROGRAM_ID = ----------------

Select * from fnd_concurrent_programs_VL
WHERE USER_CONCURRENT_PROGRAM_NAME = 'Name'

Select * from fnd_concurrent_requests
WHERE CONCURRENT_PROGRAM_ID = ----------------




From Back End
Use below query to cancel scheduled concurrent program.

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=&P_CONCURRENT_PROGRAM_ID;

COMMIT;

How to Purge Debug Log, Closed Notifications and Concurrent Program Log/out files in Oracle Applications

Concurrent program “Purge Debug Log and System Alerts” in Release 11i and “Purge Logs and Closed System Alerts” in Release 12 is recommended way to purge messages.

 This program purges all messages up to the specified date,except messages for active transactions (new or open alerts, active ICX sessions, concurrent requests, and so on). This program is by default scheduled to run daily and purge messages older than 7 days. Internally this concurrent program invokes FND_LOG_ADMIN APIs.

Data from the following tables will be deleted when you run “Purge Logs and Closed System Alerts'” or “Purge Debug Log and System Alerts” program :

select * from FND_EXCEPTION_NOTES;

select * from FND_OAM_BIZEX_SENT_NOTIF;

select * from FND_LOG_METRICS;

select * from FND_LOG_UNIQUE_EXCEPTIONS;

select * from FND_LOG_EXCEPTIONS;

select * from FND_LOG_MESSAGES;

select * from FND_LOG_TRANSACTION_CONTEXT;

select * from FND_LOG_ATTACHMENTS

These tables contain debug & error messages.

Purge Concurrent Request and/or Manager Data Program

This concurrent program is used to purge concurrent requests log/out file, and/or CM log files.

How to create concurrent programs from database in oracle apps

1)    Registering the Executable from back-end:
Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is the PL/SQL code to create an executable from back-end.

/* Formatted on 3/27/2017 4:02:08 PM (QP5 v5.114.809.3010) */
BEGIN
   FND_PROGRAM.executable (
      executable            => 'XXFIN TEST EXECUTABLE',     -- Executable Name
      application           => 'XXFIN',              -- Application Short Name
      short_name            => 'XXFINTSTEXE',         -- Executable Short Name
      description           => 'Test Executable created from Backend', -- Description,DEFAULT NULL
      execution_method      => 'PL/SQL Stored Procedure',  -- Execution Method
      execution_file_name   => 'XXFIN_TEST_PROC', -- Execution File Name,DEFAULT NULL
      subroutine_name       => NULL,           -- Subroutine Name,DEFAULT NULL
      icon_name             => NULL,                 -- Icon Name,DEFAULT NULL
      language_code         => 'US',             -- Language Code,DEFAULT 'US'
      execution_file_path   => NULL       -- Execution File Path, DEFAULT NULL
   );
   COMMIT;
END;


View from Front end:








Notes:
1] The above API inserts the new records in FND_EXECUTABLES and FND_EXECUTABLES_TL table.
2] You can use the below query to get all the Execution Methods available:

SELECT MEANING “Execution Method”
FROM fnd_lookup_values
WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND enabled_flag  = ‘Y’;

2)    Registering the Concurrent program from back-end:      

Usually we create Concurrent program in the front-end, but this can be done from the database tier too. Below is the program to create a Concurrent program from back-end.

/* Formatted on 3/27/2017 4:04:29 PM (QP5 v5.114.809.3010) */
BEGIN
   FND_PROGRAM.register (program                    => 'Test CP from DB', -- CP Name
                         application                => 'XXFIN', -- Application Short Name
                         enabled                    => 'Y',
                         -- Flag to Enable/Disable a CP
                         short_name                 => 'XXFINTSTCPDB', -- CP Short Name
                         description                => 'Test CP created from Backend',
                         -- Description,DEFAULT NULL
                         executable_short_name      => 'XXFINTSTEXE', -- Executable Short Name
                         executable_application     => 'XXFIN', -- Executable Application Short Name
                         execution_options          => NULL,
                         -- Execution Options,DEFAULT NULL,
                         priority                   => NULL, -- Priority,DEFAULT NULL,
                         save_output                => 'Y',
                         -- Save Output,DEFAULT 'Y',
                         PRINT                      => 'Y', -- Print,DEFAULT 'Y',
                         cols                       => NULL,  -- DEFAULT NULL,
                         rows                       => NULL,  -- DEFAULT NULL,
                         style                      => NULL,  -- DEFAULT NULL,
                         style_required             => 'N',    -- DEFAULT 'N',
                         printer                    => NULL,  -- DEFAULT NULL,
                         request_type               => NULL,  -- DEFAULT NULL,
                         request_type_application   => NULL,  -- DEFAULT NULL,
                         use_in_srs                 => 'N',    -- DEFAULT 'N',
                         allow_disabled_values      => 'N',    -- DEFAULT 'N',
                         run_alone                  => 'N',    -- DEFAULT 'N',
                         output_type                => 'TEXT', -- DEFAULT 'TEXT'
                         enable_trace               => 'N',    -- DEFAULT 'N',
                         restart                    => 'Y',    -- DEFAULT 'Y',
                         nls_compliant              => 'Y',    -- DEFAULT 'Y',
                         icon_name                  => NULL,  -- DEFAULT NULL,
                         language_code              => 'US',  -- DEFAULT 'US',
                         mls_function_short_name    => NULL,  -- DEFAULT NULL,
                         mls_function_application   => NULL,  -- DEFAULT NULL,
                         incrementor                => NULL,  -- DEFAULT NULL,
                         refresh_portlet            => NULL   -- DEFAULT NULL,
                                                           );
   COMMIT;
END;

View from Front end:



Notes:

1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.
2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

3)    Attaching the concurrent program to the request group

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too. Below is the program to Attach Concurrent program to the request group from back-end.

/* Formatted on 3/27/2017 4:06:15 PM (QP5 v5.114.809.3010) */
BEGIN
   FND_PROGRAM.add_to_group ('XXFINTSTCPDB',  -- Concurrent Program Short Name
                             'XXFIN',                -- Application Short Name
                             'All Reports',               -- Report Group Name
                             'SQLAP');             -- Report Group Application
   COMMIT;
END;

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all. 

concurrent program output to email in oracle apps

How to send output of concurrent program to email?

 Set the profile options "FND:SMTP Host" and "FND:SMTP Port" by providing the right values 
---------------------------------------------------------------
a. Log into System Administrator responsibility.
b. Navigate to Profile - System.
c. Query up the %SMTP% profiles.
d. Set the following profile values to the defined host and port:

FND:SMTP Host
FND:SMTP Port

Example
FND:SMTP Host = mail.ebiztechnics.com
FND:SMTP Port = 25

e. Resubmit request and monitor email delivery. 

SQL query for Concurrent Request Paused Jobs

SELECT DISTINCT cr.request_id, cr.description,
                cp.user_concurrent_program_name, u.user_name,
                cr.actual_start_date
           FROM apps.fnd_lookup_values lv,
                apps.fnd_concurrent_programs_vl cp,
                apps.fnd_executables e,
                apps.fnd_concurrent_requests cr,
                apps.fnd_user u
          WHERE e.executable_id = cp.executable_id
            AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
            AND lv.lookup_code = e.execution_method_code
            AND cr.concurrent_program_id = cp.concurrent_program_id
            AND cr.requested_by = u.user_id
            AND u.user_name IN ('TFADMIN', 'BIADMIN', 'SYSADMIN')
            AND cr.status_code = 'W'
            AND cr.phase_code = 'R'
            AND TRUNC (cr.actual_start_date) >= TRUNC (SYSDATE - 1)
       ORDER BY cr.actual_start_date DESC, cr.request_id;

SQL Query for Concurrent Request Failures

--Admin Concurrent Request Failures

SELECT DISTINCT cr.request_id, cr.description,
                cp.user_concurrent_program_name, u.user_name,
                cr.actual_start_date
           FROM apps.fnd_lookup_values lv,
                apps.fnd_concurrent_programs_vl cp,
                apps.fnd_executables e,
                apps.fnd_concurrent_requests cr,
                apps.fnd_user u
          WHERE e.executable_id = cp.executable_id
            AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
            AND lv.lookup_code = e.execution_method_code
            AND cr.concurrent_program_id = cp.concurrent_program_id
            AND cr.requested_by = u.user_id
            AND u.user_name IN
                             ('TFADMIN', 'BIADMIN', 'SYSADMIN', 'SHAZ052036')
            AND cr.status_code = 'E'
            AND cr.phase_code = 'C'
            AND TRUNC (cr.actual_start_date) >= TRUNC (SYSDATE - 1)
       ORDER BY cr.actual_start_date DESC, cr.request_id;

HOW TO SEND CONCURRENT PROGRAM OUTPUT AS AN EMAIL

There are two Methods to send Concurrent Program Output as Email

Method 1:

• The printer styles have to be configured to execute a shell script instead of sending the output to a physical printer. 
• The shell script then emails the concurrent program output to the intended recipient. 

Method 2: (Applicable R12.1.3 onwards)

With the R12.1.3 release, a hook to the BI Publisher Delivery Manager is available on the SRS window to send the output to IPP Printers, Email, Fax and FTP destinations. 


Steps for Method 1 
Step 1: Define/Query Printer Type: Define a new Printer Type or Query for an existing one. Responsibility: System Administrator Navigation: Install->Printer->Types

Step 2: Define Printer Style: Click on the ‘Style’ button in the Printer Types form (as seen in the previous screenshot). Create a new style and save it. 

Step 3: Define Printer Driver: Click on ‘ Driver’ button in the Printer Types form. Specify Driver method as command. Specify the following in the arguments section: $XXCUST_TOP/bin/EMAIL.sh $PROFILES$.CONC_REQUEST_ID 30

Step 4: Mapping: Map the Printer Style and Printer Driver to the Printer Type.

Step 5: Install the Printer and map the Printer Type : Create a new printer and map the Printer Type created in the previous step.

Step 6: Attach the defined printer to the concurrent program: Navigation: Concurrent > Program > Define .Specify printer in Output section

Step 7: Running the Concurrent Program: Before clicking on the Submit button in the SRS window, specify non-zero value of no. of copies by clicking on the Options button. 
This will invoke Printer.

Step 8: The sample shell script (email_conc_prog_output.sh) that is executed after the completion of the above steps is attached with the article. 


Steps for Method 2

This feature is available in the new button ‘Delivery Opts’ as seen in the screenshot below. 

On clicking the ‘Delivery Opts’ Button, a new form will pop up which will allow users to specificemail addresses of the sender, recipient and carbon copy recipient (multiple emails can be specified separated by commas), subject of the email. This can be seen in the screenshot below.

The Email tab (as seen in the screenshot above) utilizes SMTP to communicate to a mail hostwhich in turn sends the email to the recipients. The hostname and port of the mail host is configured via the profile values: 
FND: SMTP Host and FND: SMTP Port