How To Delete A Concurrent Program And Executable From Back End In Oracle Apps

Here is a simple script to delete a concurrent program and an executable from oracle applications through back end.

Script to delete the concurrent program and executable from Oracle Apps:

Begin

fnd_program.delete_program('program short name','schema');

fnd_program.delete_executable('program short name','schema');

commit;

End; 

Example: If you have a concurrent Progam called "Employee Details Report" with the short name EMPDTLSREP and an Executable EMPDTLSEXE is associated with the concurrent program Employee Details Report in apps schema then use the script below to delete both the concurrent program and executable.

Begin

fnd_program.delete_program('EMPDTLSREP', 'apps' );

fnd_program.delete_executable('EMPDTLSEXE', 'apps' );

commit;

End; 

Note: The same concurrent program can be disable through from end if a user decides not to use it.

How To Copy Files Using a PL/SQL Script

Usually we get requirements to move files from one location to another. The most easy option we think of for this purpose is through shell script, but there is a simple pl/sql api which can do this job easily for us.

You must have heard about UTL_FILE utility provided by oracle. We can use this utility for moving our files from one locatio to another. It has some limitations as well, such as the directory structure used by it should be in the UTL_FILE_PATH in init.ora or Directory object should exist for that path

The following code/Script can be used for Copying the file:

Utl_File.Fcopy ( src_location => p_file_location

,src_filename => p_file_name

, dest_location => p_arch_location,

dest_filename => p_arch_file );

The following code/Script can be used for removing the file:

UTL_FILE.Fremove(p_file_location,lc_datafile_name);

PL/SQL Script To Change/Reset The Oracle Applications Password From Back End

Usually its not recommended to change the Oracle apps password from back end, but if you need to change or reset it then use the PL/SQL script below:

DECLARE
v_flag BOOLEAN;

BEGIN
v_flag := fnd_user_pkg.ChangePassword('OPERATIONS','123456');
END;

COMMIT;

SQL Query To Print The Calendar Of The Current Year

Run The Below SQL Query To Get The Calendar of the Current Year:


SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",

"Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,
MAX (DECODE (TO_CHAR (dt, 'd'),

'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sun",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mon",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tue",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Wed",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Thu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fri",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sat"
FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y')) GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

PL/SQL Script To Remove End Date From Responsibilities Assigned To A User

How To Remove End Date From Responsibilities Assigned To A User From Back End ?

For developing any application for a particular Module we are granted the access to the responsibilities for certain time period and once the time period is over we loose all the access. The following script can be very handy in such cases to remove the end date[specified during the assignment of the responsibility to the user] of the responsibility access.

DECLARE

p_user_name VARCHAR2 (50) := 'TECHTIPS';

p_resp_name VARCHAR2 (50) := 'Order Management Super User';

v_user_id NUMBER (10) := 0;

v_responsibility_id NUMBER (10) := 0;

v_application_id NUMBER (10) := 0;

BEGIN

BEGIN

SELECT user_id

INTO v_user_id

FROM fnd_user

WHERE UPPER (user_name) = UPPER (p_user_name);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line ('User not found');

RAISE;

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error finding User.');

RAISE;

END;

BEGIN

SELECT application_id, responsibility_id

INTO v_application_id, v_responsibility_id

FROM fnd_responsibility_vl

WHERE UPPER (responsibility_name) = UPPER (p_resp_name);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line ('Responsibility not found.');

RAISE;

WHEN TOO_MANY_ROWS

THEN

DBMS_OUTPUT.put_line

('More than one responsibility found with this name.');

RAISE;

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error finding responsibility.');

RAISE;

END;

BEGIN

DBMS_OUTPUT.put_line ('Initializing The Application');

fnd_global.apps_initialize (user_id => v_user_id,

resp_id => v_responsibility_id,

resp_appl_id => v_application_id

);

DBMS_OUTPUT.put_line

('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

fnd_user_resp_groups_api.update_assignment

(user_id => v_user_id,

responsibility_id => v_responsibility_id,

responsibility_application_id => v_application_id,

security_group_id => 0,

start_date => SYSDATE,

end_date => NULL,

description => NULL

);

DBMS_OUTPUT.put_line

('The End Date has been removed from responsibility');

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error calling the API');

RAISE;

END;

END;

SQL Query To Find Out The Oracle Application URL Through Database

Following Query can be used to find out the Oracle Application URL from the Apps database.

Select PROFILE_OPTION_VALUE
From   FND_PROFILE_OPTION_VALUES
WHERE  PROFILE_OPTION_ID =
       (SELECT PROFILE_OPTION_ID
        FROM FND_PROFILE_OPTIONS
        WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
        AND LEVEL_VALUE=0;

DBMS_OBFUSCATION_TOOLKIT | PLSQL Script To ENCRYPT/DECRYPT A String

Oracle has provided a very useful utility called DBMS_OBFUSCATION_TOOLKIT to encrypt or decrypt a character string.

Following points should be noted while running the provided script:

If the input data or key given to the PL/SQL DESDECRYPT function is empty, then Oracle raises ORA error 28231 "Invalid input to Obfuscation toolkit."

If the input data given to the DESDECRYPT function is not a multiple of 8 bytes, Oracle raises ORA error 28232 "Invalid input size for Obfuscation toolkit."

If the key length is missing or is less than 8 bytes, then the procedure raises the error ORA-28234 "Key length too short." Note that if larger keys are used, extra bytes are ignored. So a 9-byte key will not generate an exception.

SCRIPT:

SET serveroutput ON;

DECLARE

   lv_encrypted_data              VARCHAR2 (2000);

   lv_decrypted_data              VARCHAR2 (2000);

   piv_str                        VARCHAR2 (2000) :='www.sqlplsqloracleappstutorials.com';

   piv_pass_key                   VARCHAR2 (2000) := 'nisheethr';

   a_var                          VARCHAR2 (100);

   error_in_input_buffer_length   EXCEPTION;

   PRAGMA EXCEPTION_INIT (error_in_input_buffer_length, -28232);

   input_buffer_length_err_msg    VARCHAR2 (100)

                      := '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';

BEGIN

   DBMS_OUTPUT.put_line (   'Input_string->:'

                         || piv_str

                         || CHR (10)

                         || 'LENGTH OF STRING=>'

                         || LENGTH (piv_str)

                        );

--  Since DES needs data to be in multples of 8 bytes we had padded the data, if the

--  data did not meet the 8 bytes boundry requirment. So now after decrypting we

--  would need to remove the padding if it exists

   IF MOD (LENGTH (piv_str), 8) != 0

   THEN

      piv_str :=

         RPAD (piv_str,

               LENGTH (piv_str) + 8 - MOD (LENGTH (piv_str), 8),

               CHR (0)

              );

   END IF;

   lv_encrypted_data :=

      DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string      => piv_str,

                                           key_string        => piv_pass_key

                                          );

   DBMS_OUTPUT.put_line ('Encrypted Data: ' || lv_encrypted_data);

   lv_decrypted_data :=

      DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string      => lv_encrypted_data,

                                           key_string        => piv_pass_key

                                          );

   -- This is to remove the padded chracters

   lv_decrypted_data :=

       SUBSTR (lv_decrypted_data, 1, INSTR (lv_decrypted_data, CHR (0), 1) - 1);

   DBMS_OUTPUT.put_line (   'decrypt_string->:'

                         || lv_decrypted_data

                         || CHR (10)

                         || 'LENGTH OF STRING=>'

                         || LENGTH (lv_decrypted_data)

                        );

EXCEPTION

   WHEN error_in_input_buffer_length

   THEN

      DBMS_OUTPUT.put_line ('> ' || input_buffer_length_err_msg);

END;