Find out the URL of the Application in the database

If you want to find out the URL of the Application in the database in which table you can check ?

Option 1:
select * from icx_parameters

Option 2:

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

Thanks & Regards,
Anto Joe Natesh I

I'm getting a Yellow Warning Bar. How do I get rid of this?

1. Yellow Warning Banners
       a. What Does "Warning: Applet Window" Mean?
      Oracle Applications Release 11.5.1 (11i) requires that its code run in a trusted mode, and uses J-Initiator to run Java applets on a desktop client. If an applet is "trusted," however, Java will extend the privileges of the applet. The Yellow Warning Bar is a warning that your applet is not running in a trusted mode. To indicate that an applet is trusted, it must be digitally signed using a digital Certificate, so Oracle Applications requires that all Java archive files must be digitally signed.

      b. Who Does This Affect?

      This affects all users that try to access Oracle Applications Rel 11i using Jinitiator that have a different identitydb.obj on their client. Clients have an "identity database" that is maintained by J-Initiator called IDENTITYDB.obj.
      When a jar file is downloaded, the owner of the digital signature is compared against the entry in the identity databases. If they match, the code contained in the archive is allowed to run in a trusted mode. The users will need to fix their client PC in one of two ways:
        i.
           a. Uninstall Jinitiator and clear browser cache
           b. Log back into Applications to get the new plugin,(oajinit.exe) including the new identitydb.obj
           c. Install the Jinitiator on the Client PC and then Log into the Oracle Applications to download the new signed JAR files
       OR
        ii.
           a. Copy the IDENTITYDB.OBJ file to C:\Program Files\Oracle after saving the old one as IDENTITYDB.old.

Thanks & Regards,
Anto Joe Natesh I

Useful Queries

Friends, Here are some useful queries.

1. Database and Application Information?
http://retoscripts.blogspot.com/2009/09/database-and-application-information.html

2. Get onhand quantity at given date
http://retoscripts.blogspot.com/2009/09/onhand-quantity-at-given-date.html

3. Link Purchase Order and Requisition
http://retoscripts.blogspot.com/2009/09/link-purchase-order-and-requisition.html

4. Different Types of Table in Oracle Apps
http://retoscripts.blogspot.com/2009/09/different-types-of-table-in-oracle-apps.html

5. Which User is Locking the table?
http://retoscripts.blogspot.com/2009/09/which-user-is-locking-table.html

6. Query for Customer Receipt Details
http://retoscripts.blogspot.com/2009/09/query-for-customer-receipt-details.html

7. Query for Supplier Bank Details
http://retoscripts.blogspot.com/2009/09/query-for-supplier-bank-details.html

8. Query for Customer Address Details
http://retoscripts.blogspot.com/2009/09/query-for-customer-address-details.html

9. Query for Sales Order Details
http://retoscripts.blogspot.com/2009/09/query-for-sales-order-details.html

10. Query to get Request Group Details Responsibility wise
http://retoscripts.blogspot.com/2009/09/query-to-get-request-group-details.html

11. Query to find who and when update an Oracle Application user's profile
http://retoscripts.blogspot.com/2009/09/query-to-find-who-and-when-update.html

Thanks & Regards,
Anto Joe Natesh I

Query Tips and Tricks

Most of our technical consultants used to write query fastly to find out number of records in a table, and find out the number of records between.

here are some tips to find out from the front end itself.

Query Wild Card Characters using "%" and "_". Now we will see the difference between this.

% - one or more characters

_ - exactly one character

Examples:

'AS64%' returns AS64000 or AS64001

'AS6_4%' returns AS61400 or AS62400, but not AS64000 / AS64001

Query Operators

a) #between <> and <> 

b) #is null

c) #is not null

d) > 

e) >=

f) <

g) <=

h) =

i) !=

Examples: In sales order screen

a) #between 10 and 20 => (Query in Sales order number field) it will return the sales order number between 10 to 20

b) #is null => (Query in Customer Contact field) it will return the sales order which do not have customer contact information

c) #is null => (Query in Customer Contact field) it will return the sales order which have customer contact information

d) >20 => (Query in Sales order number field) it will return the sales order number greater than 20

e) >=20 => (Query in Sales order number field) it will return the sales order number 20 and greater than 20

f) <20 => (Query in Sales order number field) it will return the sales order number less than 20

g) >=20 => (Query in Sales order number field) it will return the sales order number 20 and less than 20

h) =20 => (Query in Sales order number field) it will return the sales order number 20

i) !=20 => (Query in Sales order number field) it will return the sales order number except 20

Count Record: 

After you execute the query. Now you want to find out the number of records in the last query.

Example:

First press F11 in sales order screen then enter this in sales order number field #between 10 and 20. execute using CONTROL + F11, It will return the sales order number between 10 to 20. Now press F12, this will return number of records parsed now.

Last Query:

You gave some input and query the records in the form. Now you want to find out, which query executed to get this details.

   Help : Diagnostics : Examine

   Block = SYSTEM

   Field = last_query

(requires apps password if the Profile Option Utilities:Diagnostics is set to Yes)

Other ways to determine the underlying table structure:

1) Help : Record History

2) Trace the form (requires apps password and DBA to retrieve trace file)

Uploading Images using sql loader

Dears,


Here i am going to explain about how to import images using sqlloader. 
Let me take the HRMS employee images load into per_images.


SQL> desc per_images

Name                            Null?    Type
——————————- ——– —-
IMAGE_ID                        NOT NULL NUMBER(15)
IMAGE                           NOT NULL BLOB
PARENT_ID                       NOT NULL NUMBER(15)
TABLE_NAME                      NOT NULL VARCHAR2(30)
--===============================================

Assume, we are importing image for the employee number 20063. image name is 20063.jpg

IMAGE_ID   – Next value in sequence(per_images_s.nextval)
IMAGE      – Binary Image(20063.jpg)
PARENT_ID  – PERSON_ID (select person_id from PER_ALL_PEOPLE_F where employee_number = 20063 and sysdate between effective_start_date and effective_end_date)
TABLE_NAME – PER_PEOPLE_F (default)

First let us make the data file for this employee 20063. 
-------------------------------------------

SELECT  PER_IMAGES_S.nextval
       || ','
       || person_id
       || ','

       || 'd:\images\'

       || employee_number
       || '.jpg'
  FROM per_all_people_f
 WHERE  EFFECTIVE_END_DATE >= sysdate-1
 AND employee_number IN ('20063')


Now the data file look likes:-
6482,101,PER_ALL_PEOPLE_F,d:\images\20063.jpg


Creating Control file for our data file:-
load data

 infile *
 replace
 into table per_images
 fields terminated by ","
 ( IMAGE_ID,PARENT_ID,TABLE_NAME,IMAGE LOBFILE (TABLE_NAME) TERMINATED BY EOF  )
begindata
3061,101,d:\images\20063.jpg

Here, I have used the TABLE_NAME column for storing my image file path. After successful completion of the sql loader execution update the column value into PER_PEOPLE_F.


client machine they have already installed sqlloader. So i refer the local machine path. Suppose if you are accessing the server through putty or some other tool to access server. Use server folder location instead.


If you have any clarifications please let me know.


Cool,
AntO.

Calculating Weekdays between two dates

Calculating total number of Week Days between two given dates is most common requirement. 


This link shows one methods to achieve this.
http://retoscripts.blogspot.com/2009/09/calculating-weekdays-between-two-dates.html


Cool,
AntO.

Difference Between Application and Responsibility

Applciation is nothing Colletion of Forms,Reports and Program which are related for
specific business functionality.

Responsibility is nothing but Colletion of Forms,Reports and Program which are related for
specific Position in the Organization.

For Ex : We have to create One Responsibility For the Finance Asst. Which is accesable by all
the Finance Assts.
It Contains the Forms and Reports which are required for the Finance Asst.

We have to Create new Responsibility for the Manager,Which is accesable by all the Managers.
It Contains the Forms and Reports which are required for the manager.

Where as Application includes all the Forms,Reports and Programs.If we assign the application to the user he will access all the forms and Reports.
Instead of that we will create the responsibility and we will assign to the User.

Cool,
AntO.

Personalization & Profiles

What Profiles Must Be Set For Personalizations Links To Appear And Take Effect In Self Service?

The following profiles must be set to the following values for personalization links to appear in Self Service

FND: PERSONALIZATION REGION LINK ENABLED SET YES

PERSONALIZE SELF-SERVICE DEFN SET TO YES

DISABLE SELF-SERVICE PERSONAL SET TO NO

If any of the profile values need to be changed, once you set them the correct values please: Log out of applications completely and login again.

You can enable personalizations at any level.
Normally you should enable for a particular person who maintains the personalizations.
Set  following profile option to ‘Yes’ to enable personalizations

Personalize Sefl-Service Defn

Cool,
AntO.

Registering Custom Table

You register your custom application tables using a PL/SQL procedure in the AD_DD package. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables. If you alterthe table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, and then re-register the table or column. Remember, you should delete the column registration first, then the table registration. You should include calls to the tableregistration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that thetables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.


See this Link for the script: http://retoscripts.blogspot.com/2009/09/to-register-your-custom-table-under-fnd.html

Oracle Applications Multiple Organizations Access Control for Custom Code

How to update the customization code that is affected by the access by the access control feature in Oracle applications multiple organizations?

Follow this document id in metalink. 420787.1

Sending EMail from oracle application

Scenario: After ran a RFQ Report, I have to send the output of a RFQ report to the suppliers mail id automatically.

I have used the below step to achieve this.

Technology: Unix Shell Script is used as the base technology in the design of the global program. A shell script which would send email notifications to email ids is developed first. A Concurrent Program then wraps the shell script, acting as the user interface for the same. This concurrent program can be called from Forms, Reports, PL/SQL Packages etc with appropriate parameters to send email notifications

System Requirements:  

The following is the system requirements for the global program.

Operating System: HP–UX. Any other flavour of Unix which supports mailx command.

Oracle Applications Version: R12 / 11i

Setups:

The complete setup can be summarized in three steps:

1.    Placing the script in the bin directory,

2.    Creating Value Set

                  3.  Creating Apps Executable & Concurrent program

Steps in detail:

1. Shell Script: Place the shell script mentioned in the “appropriate top”/bin folder in the Unix Box. (For Example, $CUSTOM_TOP/bin, Unix code given below in this post).

2.  Create Value Set: Navigate to the following path

    Responsibility: System Administrator

    Navigation: Application à Validation à Set

    Create a value with the following information: 

    Format Type: Char

    Maximum Size: 225

    Validation Type: None.

3.Create Apps Executable: Create an Oracle Apps Executable (Responsibility: System Administrator Navigation: Concurrent à Program à Executable) for the shell script 

4. Create Concurrent Program: Create an Oracle Apps Concurrent Program (Responsibility: System Administrator Navigation: Concurrent à Program à Define) linking it to the executable created in Step 3

5. Concurrent Program Parameter: Create the following parameters to the concurrent program and attach the valueset created in Step 2 :

S.No	Parameter	Description
1	TO Mail Id	List of comma separated TO email ids
2	CC Mail Id	List of comma separated CC email ids
3	Subject	Subject of the mail
4	Body-1	Max 225 Char - If Body has more than 225 characters, then the remaining characters has to be populated in Body – 2
5	Body-2	Max 225 Char - If Body has more than 225 characters, then the first 225 characters should be populated in Body-1 and remaining in Body – 2.
5	File Path	File path of the file to be attached (Can have environment variables like $APPLCSF/$APPLOUT)
6	File Name	Name of the file to be attached
7	File Display Name	Display name of the file in the email.

The setup is now complete and the concurrent program can now be used to send email with attachments.

Mail Text Formating: The following points needs to be taken care while formatting the body of the email.

   (i)             To have a “New Line” character (“Enter” Key Character in the body ), four backward slash with the character “n” should be used, i.e. \\\\n

   (ii)      To get additional formatting like Bold, Underline etc, the body of the mail can be sent with HTML tags and the Email Client would display it appropriately (If HTML is supported by the email client)

Sample Code: 

Attached is a sample code making a call to the Concurrent Program to send email with attachments. 

The following sample code would send email notification with the report generated in pdf format, as attachment from a RDF report.

              Code in “After Report Trigger”

Click here for code: http://retoscripts.blogspot.com/2009/09/sending-email-after-report-code.html

Limitations: Due to restrictions imposed by Oracle Apps and Unix, there are few limitations to this approach.

a. Oracle Apps Limitation: Oracle Apps has a limitation of the length of concurrent program parameter which cannot exceed 240 character. But since the body might exceed this length,  a workaround was needed to send the body of the email as it can exceed 240 characters

The workaround is: Two “Body” parameters have been created: Body-1 and Body-2. If the length of Body-1 exceeds 225 character, it should be put in Body-2. The gobal notification program would ensure that the two body parts are concatenated and sent in one email

It is presumed, that in general, the body of an automated email would not exceed more than 450 characters.

b. Unix Limitation: Email to only those ids can be sent as per the configuration of the Unix Mailx utility. If the Unix box is configured to not to send email to ids such as hotmail and yahoo, then email cannot be sent to those ids.

Unix Code:- 

Below is the shell script code. File Name XXCUST_SEND_MAIL (without extension). copy paste the code into a file and save it as XXCUST_SEND_MAIL.

Move to $XXCUST_TOP/bin

Click here for code: http://retoscripts.blogspot.com/2009/09/sending-email-unix-code.html

Migrating Reports from 11i to R12

I had a big challenge to migrate the oracle rdf reports from version 11i to R12. Most of our consultants used RA_CUSTOMERS, RA_ADDRESSES, RA_CUST_SITES views to get the customer related information in the report. As you know, these tables/views are no longer used in R12.

Here, This is the trick i have used. I have created 3 views, which is based on the HZ_% tables like XX_RA_CUSTOMERS, XX_RA_ADDRESSES and XX_RA_CUST_SITES. Now, in all old rdf reports i haved included XX_ infront of RA_ and solved the issue.

There is one more easy way to solve this issue. But I have one question in this? Tell me, shall we go in this way.

Question is... Instead of making XX_RA_CUSTOMERS, why cannot create a view in the same name as RA_CUSTOMERS and drop the RA_CUSTOMERS table? I know we should not touch the oracle seeded objects. But, As per oracle R12 technical design this tables are no more...!

Sending Email Through Unix (HP-UX)

Environment: HP-UX
Application Version: 12.0.6

The below is the code to send mail with attachment from unix:

mailx -s "Subject" to_mail_address

example:
mailx -s "Summary Report" anto.natesh@abc.com

Here,
mailx: is a utility to sending email from unix.
-s: denoting as subject
"Summary Report" - Subject of the email
anto.natesh@abc.com - to address

The below is the code to send mail with attachment from unix:

uuencode "File_Name" "New_File_Name") | mailx -s "Subject" to_mail_address

Example:
uuencode "abcd.pdf" "summary_report.pdf") | mailx -s "Summary Report" anto.natesh@abc.com

Here,

uuencode: Unix to Unix Encoding, is basically used to send a mail with attachment.
abcd.pdf: Original File Name to send over mail.
summary_report.pdf: New Name
|: Pipe symbol to execute 2 statement from one line.

I will explain how to create and use this functionality as a concurrent program in oracle applications.

If you need any clarifications, mail me @ anto.natesh@gmail.com