Define New List Of Value (LOV)

Create List Of Value (LOV)

Navigation: Login into Oracle Applications –> Go to Application Developer Responsibility –> Application –> Validation  –> Set 

 Here we will register oracle LOV

1- Set Name XXX
2- Maximum Size 25
3- Validation Type=Table
4- Edit Information

1-Table Name = Table or View
2- Value = Field Name
3- Meaning = Field Name
4- ID =Field Name
5- Where /Order By =Validations

Define New Report On EBS

Overview:

• Develop a report to register as a concurrent program
• Create Executable: Link it to Report file(.rdf) created
• Create Concurrent Program: Link to it executable defined in previous step
• Enter Parameters and link Value Sets
• Assign the registered Concurrent Program to a request group

Creating Executable:

Navigation: Login into Oracle Applications –> Go to Application Developer Responsibility –> Concurrent –> Executable

Here we will register oracle report as a concurrent program

FIELDS:

• Executable: This is User Understandable Name
• Short Name: This is Unique and for system reference
• Application: Under which application you want to register this Conc. Program
• Description: Description
• Execution Method: Based on this field, your file has to be placed in respective directory or database.
• Execution File Name: This is the actual Report file name. If you register a PL/SQL Procedure in a package you have to give the packagename.procedure. You don’t need to specify any parameters in procedure here.

Action: Save

Create Concurrent Program:

Navigation: Application Developer –> Concurrent –> Program

FIELDS:

• Program: User Understandable Program Name
• Short Name: This should be unique name and for system reference
• Application: Enter the application under which you want to register this conc.prog
• Executable Name: Enter the User Understandable Executable Name
• Method: This will be populated automatically from Executable Definition
• Output Format: Select the format of the output you want
• Output Style: Select A4 to print on A4 Paper
• Printer: You can default any printer or you can enter while submitting concurrent program.

DEFINE PARAMETERS AND VALUE SETS

 Navigation: click on Parameters button in above screen

FIELDS:

• Seq: It’s always better to enter sequences in multiple of 5 or 10. So that you can insert any additional parameters if you want later in middle.
• Parameter: Name the Parameter Field. This is for system reference
• Description: You can see this description while submitting the conc.prog.
• Value set: “15 Characters” is the standard value set for Character input parameters
• Default Type: This field is not mandatory. If you want to default any particular value to save time while submitting the concurrent program you can do so here.

Current Date : Will be used to pass the Sysdate as default value
Current Time : Will be used to pass the Systime as default value
Constant     : Will be used to pass the constant number or date or string as default value
Segment      : will be used to get the Previous parameter value as default to the next parameter
SQL Statement: We can pass the SELECT statement result as default value
Profile : This will be used to pass the user profile value as default like userid ,username,respid, respname and so on.

• Required CheckBox : This will be used to make the parameter as mandatory or optional
• Enabled  CheckBox: will be used to enable or disable the parameter
• Display  CheckBox: will be used to hide or display the parameter
• Prompt: This is the actual message displayed while submitting the conc.prog
• Token: This is used to link this parameter to the parameter defined in actual report file(.rdf)

Action: Save

Assign this Concurrent Program to a request group:

Navigation: Switch to “System Administrator” Responsibility –> Security –> Responsibility –> Request

Action: Open/Double click

Action: CTRL+F11

Action: Select the First record in the “Requests” and then File > New

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;