Suresh : A Blog on Oracle Applications

Wednesday, March 13, 2013

Script to find Application Login URL from backend

select home_url from icx_parameters;

Wednesday, February 27, 2013

Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running

A concurrent program can be canceled either from "Submit Request Submission" form or from database side also. In case of custom concurrent programs, sometimes concurrent program do not release database session and process even though it has canceled from "Submit Request Submission" form. Active database process can be seen in running status. In those cases there is a need to manually kill that process to release CPU memory.

Take the "request_id" of a Concurrent Program which is currently running or which is to be canceled from the database side.
Connect to SQLPLUS as APPS User :

SQL> SELECT ses.sid,
            ses.serial#
       FROM v$session ses,
            v$process pro
           WHERE ses.paddr = pro.addr
                AND pro.spid IN (SELECT oracle_process_id
                                   FROM fnd_concurrent_requests
                                  WHERE request_id = &request_id);

Note: oracle_process_id is Unix PID and request_id is running concurrent program's request ID. If "sid" and "serial#" value is returning then it means that process is running at database level. When canceling a request from the "Submit Request Submission" form, then it should release associated database process and session but it doesn't mean that it will kill database process immediately. Database process will take their own time to validate concurrent program execution process that has been canceled and then it will kill database process. So ideally when canceling a request from "Submit Request Submission", wait for some time and then check associated database process.

3. Connect to SQLPLUS as the SYSTEM user:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Note the "sid" and "serial#" value returned from step 1.

Tuesday, February 12, 2013

How to use Dynamic Cursor in a PL/SQL

PL/SQL script to use a dynamic cursor.

CREATE OR REPLACE PROCEDURE XXX_dynamic_cursor (p_segment IN VARCHAR2)
IS
v_sql VARCHAR2 (2000) := NULL;
v_segment VARCHAR2 (50);
l_segment_desc VARCHAR2 (100);

TYPE cur_typ IS REF CURSOR;

c_seg_cur cur_typ;
BEGIN
v_sql :=
'SELECT distinct ss.'
|| p_segment
|| ' , ffvl.description
FROM XXXX_TEMP_GL ss,
fnd_id_flex_segments fifs,
fnd_flex_value_sets ffvs,
fnd_flex_values ffv,
fnd_flex_values_tl ffvl
WHERE ss.chart_of_accounts_id = fifs.id_flex_num
AND fifs.application_column_name = :p_segment AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvl.flex_value_id
AND ss.'
|| p_segment
|| ' = ffv.flex_value
order by 1';

OPEN c_seg_cur FOR v_sql USING p_segment;

LOOP
FETCH c_seg_cur
INTO v_segment, l_segment_desc;

EXIT WHEN c_seg_cur%NOTFOUND;
DBMS_OUTPUT.put_line (v_segment || l_segment_desc);
-- process row here
END LOOP;

CLOSE c_seg_cur;
END;
/

Useful SQL queries for support

Useful SQL queries for support

1. Delete a Concurrent Program from back end.

BEGIN
apps.fnd_program.remove_from_group (
program_short_name => 'CONCURRENT_PROGRAM_SHORT_NAME',
program_application => 'PROGRAM_APPLICATION_SHORT_NAME',
request_group => 'REQUEST_GROUP_NAME',
group_application => 'REQUEST_GROUP_APPICATION');
fnd_program.delete_program ('CONCURRENT_PROGRAM_SHORT_NAME', 'PROGRAM_APPLICATION_SHORT_NAME');
fnd_program.delete_executable ('EXECUTABLE_SHORT_NAME', 'EXECUTABLE_APPLICATION_SHORT_NAME');
COMMIT;
END;

2. To get the Concurrent requests details :

SELECT PARENT_REQUEST_ID, request_id, PHASE_CODE, STATUS_CODE, ARGUMENT_TEXT, req.logfile_name,req.outfile_name,
ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, RESP.RESPONSIBILITY_NAME, PGM.CONCURRENT_PROGRAM_NAME, FUS.USER_NAME
FROM APPS.FND_CONCURRENT_REQUESTS REQ, APPS.FND_CONCURRENT_PROGRAMS PGM, APPS.FND_RESPONSIBILITY_VL RESP, APPS.FND_USER FUS
WHERE REQ.CONCURRENT_PROGRAM_ID = PGM.CONCURRENT_PROGRAM_ID
AND PGM.APPLICATION_ID = REQ.PROGRAM_APPLICATION_ID
AND REQ.RESPONSIBILITY_ID = RESP.RESPONSIBILITY_ID
AND REQ.REQUESTED_BY = FUS.USER_ID
--AND PGM.CONCURRENT_PROGRAM_NAME = 'CONCURRENT_PROGRAM_SHORT_NAME'
--and req.request_id = REQUEST_ID
and FUS.USER_NAME = 'XXXXX'
order by req.request_id desc

3. Add Responsiblity to a User from backend :

BEGIN
fnd_user_pkg.addresp (username => 'USER_NAME',
resp_app => 'RESPONSIBLITY_APP_SHORT_NAME',
resp_key => 'RESPONSIBILITY_KEY',
security_group => 'STANDARD',
description => 'Auto Assignment',
start_date => SYSDATE - 10,
end_date => SYSDATE + 1000);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
' Responsibility is not added due to '
|| SQLCODE
|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;

4. Active Users with Active responsiblities:

SELECT fu.user_name,
frv.responsibility_name,
TO_CHAR (furgd.start_date, 'DD-MON-RRRR') "START_DATE",
TO_CHAR (furgd.end_date, 'DD-MON-RRRR') "END_DATE"
FROM fnd_user fu,
fnd_user_resp_groups_direct furgd,
fnd_responsibility_vl frv
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.end_date IS NULL
-- AND fu.user_name = :user_name
AND furgd.start_date <= SYSDATE
AND NVL (furgd.end_date, SYSDATE + 1) > SYSDATE
AND fu.start_date <= SYSDATE
AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE
AND frv.start_date <= SYSDATE
AND NVL (frv.end_date, SYSDATE + 1) > SYSDATE
AND FRV.RESPONSIBILITY_NAME = 'Application Developer'
ORDER BY FRV.RESPONSIBILITY_NAME ;

5.Request Group of a Concurrent Program :

SELECT fa.application_short_name,
frg.request_group_name,
fe.execution_file_name,
fe.executable_name
FROM fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_request_groups frg,
fnd_executables fe,
fnd_application fa
WHERE frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.request_group_id = frg.request_group_id
AND fe.executable_id = fcp.executable_id
AND FRG.application_id = fa.application_id
AND fe.executable_name = '&CP_EXEC_NAME';

Friday, February 8, 2013

Enable Diagnostics in Oracle apps

How to enable Oracle apps Diagnostics-> Examine, for certain users?

Steps 1

Navigate to System Administrator responsibility> Profile> System>

Steps 2

Enter profile name: Utilities:Diagnostics

Enter Application User for whom you want to enable Diagnostics-> Examine

Steps 3

Give Yes at User level and Save the Changes

Note –

You can set Yes at Site level also if you want to enable this option for all Oracle application users

Steps 4

Again navigate to System Administrator responsibility> Profile> System>

Enter profile name: Hide Diagnostics menu entry

Enter Application User for whom you do not want to hide Diagnostics menu entry

Steps 5

Give No at User level and Save the Changes

Note –

You can set No at Site level also if you do not want to hide menu entry option for all Oracle application users

Steps 6

Congratulations you have successfully enabled Diagnostics-> Examine

Logout from Oracle Application and login again. Now can see Diagnostics-> Examine option

Monday, January 14, 2013

How To Add System Administrator Responsibility To A User from backend

Most of the time oracle apps developers are given only limited access to the front end application for development, in such cases you would like to add a responsibility from back end.

By using the below PL/SQL script/code you can add any responsibility to a user.

Syntax:

BEGIN

fnd_user_pkg.addresp (username => 'SXMARGAM',

resp_app => 'SYSADMIN',

resp_key => 'SYSTEM_ADMINISTRATOR',

security_group => 'STANDARD',

description => 'Auto Assignment',

start_date => SYSDATE - 10,

end_date => SYSDATE + 1000);

COMMIT;

DBMS_OUTPUT.put_line ('Responsibility Added Successfully');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (‘Responsibility is not added due to ' || SQLCODE || SUBSTR (SQLERRM, 1, 100));

ROLLBACK;

END;

Thursday, November 1, 2012

How to Delete XML Publisher Definition and Template

How often do you create a XML publisher definition with a wrong Codes (Template or Data Definition)? Or you want to change the Code so that it is more meaningful?

In the XML Publisher's OA Framework pages, both Template and Data Definition pages do not provide an option to delete anything. Moreover, the Template Code or Definition Code is not allowed to be updated.

The reason is that: concurrent program with XML output matches the Short Name with the template Code to find out which XML Publisher template to use for post processing. If you delete this template, the Post Processor cannot find the template, and then give errors.

You cannot change the Concurrent Program Short Name in the Form, and you cannot change the XML Template Code, and you cannot change the Data definition Code. If you make a typo in any one, disable it and create another one with the correct name. That's what Oracle suggests.

Come on...I WANT TO DELETE THEM, rather than recreating everything, and leave the wrong stuff in the system.

In another blog I show the way to delete concurrent program, and in here I will show you how to delete XML publisher template and the definition associated with this template. Change the parameters to fit your needs.

PL/SQL CODE:

SET SERVEROUTPUT ON

DECLARE
   -- Change the following two parameters
   var_templateCode    VARCHAR2 (100) := 'SYMPLIK-TEST2';     -- Template Code
   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.
BEGIN
   FOR RS
      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
                 T1.DATA_SOURCE_CODE,
                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME
            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
           WHERE T1.TEMPLATE_CODE = var_templateCode
                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
   LOOP
      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS
            WHERE     LOB_CODE = var_templateCode
                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES
            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND TEMPLATE_CODE = var_templateCode
                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef
      THEN
         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS
               WHERE LOB_CODE = RS.DATA_SOURCE_CODE
                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND LOB_TYPE IN
                            ('XML_SCHEMA',
                             'DATA_TEMPLATE',
                             'XML_SAMPLE',
                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES
               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (
            'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');
      END IF;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      'Issue a COMMIT to make the changes or ROLLBACK to revert.');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'Unable to delete XML Publisher Template ' || var_templateCode);
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

Thursday, October 25, 2012

How to find first and last date of a month in SQL

Working with dates is fun. In this post we are going to show a method to easily find the first and last date of the current month, previous month, and next month.

Knowledge about this will come in handy if you i.e. want to do a year-over-year sales analysis, or maybe you would like to take the current month’s sales figures and compare them to what is budgeted for next month’s sale.

Depending on how your data is organized or partitioned, there are time you want to use the last date of a month in i.e. a BETWEEN statement in SQL.

Here is a query that selects the first and last date of a month, for last month, current month, and next month.

SELECT TRUNC (SYSDATE) today -- Last Month,

TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM'), 'YYYYMM')

first_date_last_month,

TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM') - 1

last_date_last_month -- Current Month

TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM')

first_date_this_month,

TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM') - 1

last_date_this_month -- Next Month

TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM')

first_date_next_month,

TO_DATE (TO_CHAR (ADD_MONTHS (:P_CURR_BEGIN_DATE, +2), 'YYYYMM'), 'YYYYMM') - 1

last_date_next_month

FROM DUAL;

Tuesday, October 9, 2012

Oracle Procedure to send emails to single or multiple receipients

Oracle Procedure to send emails to single or multiple recipients:

1. The below code is used to send email to single recipient using oracle Store Procedure:

/********************************************************************************

Function Name: sendmail_from_plsql
Description: This function sends and Email through the system mail host
to the address in the "p_rcptaddr" input parameter.
Input Params: p_sendaddr : Email address of the sender.
p_rcptaddr : Email address of the receipient.
p_ccaddr : Email address of the cc address.
p_subject: Subject title of the Email message.
p_body : Body of the mail message.
Output Params: return boolean value, TRUE if sendmail successful else FALSE
p_error_msg - Error msg out, If there is error in sending mail
********************************************************************************/

FUNCTION sendmail_from_plsql (p_sendaddr IN VARCHAR2,
p_rcptaddr IN VARCHAR2,
p_ccaddr IN VARCHAR2,
p_subject IN VARCHAR2,
p_body IN VARCHAR2,
p_error_msg OUT VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
mailhost := 'xxxxxxxxxx';-- give your mail host name over here
mail_conn := UTL_SMTP.open_connection (mailhost, 25);
UTL_SMTP.helo (mail_conn, mailhost);
UTL_SMTP.mail (mail_conn, p_sendaddr);
UTL_SMTP.rcpt (mail_conn, p_rcptaddr);

IF p_ccaddr IS NOT NULL
THEN
UTL_SMTP.rcpt (mail_conn, p_ccaddr);
END IF;

v_mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| v_crlf
|| 'From: '
|| p_sendaddr
|| v_crlf
|| 'To: '
|| p_rcptaddr
|| v_crlf
|| 'Cc: '
|| p_ccaddr
|| v_crlf
|| 'Subject: '
|| p_subject
|| v_crlf
|| p_body
|| v_crlf;
UTL_SMTP.data (mail_conn, v_mesg);
UTL_SMTP.quit (mail_conn);
RETURN (TRUE);
EXCEPTION
WHEN OTHERS
THEN
p_error_msg := SQLERRM;
RETURN (FALSE);
END sendmail_from_plsql;

call the above procedure using the below code:

Here v_mail_sent must be a boolean datatype

DECLARE
v_errmsg VARCHAR2 (2000);
v_true boolean;
BEGIN
v_true:= ctar_utility_pkg.sendmail_from_plsql (' sender email_id',
'To email_id',
'CC email_id',
'Hello , this is a test mail',
'Hello , this is a test mail',
v_errmsg);
END;

2. The below code is used to send email to multiple recipients:

FUNCTION address_email (p_string IN VARCHAR2, p_recipients IN array)
RETURN VARCHAR2
IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT
LOOP
UTL_SMTP.rcpt (mail_conn, p_recipients (i));

IF (l_recipients IS NULL)
THEN
l_recipients := p_string || p_recipients (i);
ELSE
l_recipients := l_recipients || ', ' || p_recipients (i);
END IF;
END LOOP;

RETURN l_recipients;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
' address_email error message is ' || SQLERRM);
END address_email;
/*********************************************************************************
Function Name: sendmail_to_multireceipents
Description: This function sends and Email through the system mail host
to multiple address in the "p_rcptaddr" input parameter.
Input Params: p_sendaddr : Email address of the sender.
p_to : Long List of Email address of the receipients.
p_cc :Long list of Email address of the cc addresses.
p_bcc :Long list of Email address of the bcc addresses.
p_subject: Subject title of the Email message.
p_body : Body of the mail message.
*******************************************************************************/

PROCEDURE sendmail_to_multireceipents (
p_sendaddr IN VARCHAR2,
p_to IN array DEFAULT array (),
p_cc IN array DEFAULT array (),
p_bcc IN array DEFAULT array (),
p_subject IN VARCHAR2,
p_body IN LONG)
AS
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;

PROCEDURE writeData (p_text IN VARCHAR2)
AS
BEGIN
IF (p_text IS NOT NULL)
THEN
UTL_SMTP.write_data (mail_conn, p_text || v_crlf);
END IF;
END;

BEGIN
mailhost := 'XXXXX'; -- mail host name
mail_conn := UTL_SMTP.open_connection (mailhost, 25);

UTL_SMTP.helo (mail_conn, mailhost);
UTL_SMTP.mail (mail_conn, p_sendaddr);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
UTL_SMTP.open_data (mail_conn);
writeData ('Date: ' || execution_date);
writeData ('From: ' || p_sendaddr);
writeData ('Subject: ' || NVL (p_subject, '(no subject)'));

writeData (l_to_list);
writeData (l_cc_list);

UTL_SMTP.write_data (mail_conn, '' || v_crlf);
UTL_SMTP.write_data (mail_conn, p_body);
UTL_SMTP.close_data (mail_conn);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
' sendmail_to_multireceipents error message is ' || SQLERRM);
END sendmail_to_multireceipents;

Code to call the above procedure is :

BEGIN
ctar_utility_pkg.sendmail_to_multireceipents (
p_sendaddr => 'Sender email id',
p_to => ctar_utility_pkg.array ('To email id's'),
p_cc => ctar_utility_pkg.array ('CC email id'),
p_bcc => NULL,
p_subject => 'Test Mail',
p_body => 'Hello , this is a test mail');
END;
/