Creating Formula in Oracle Process Manufacturing(OPM)

http://dj-oracleapps.blogspot.in/2014/02/creating-formula-in-oracle-process.html

Write a query for to disply 1st day of the last month in sql

select trunc(trunc(to_date('12-apr-2013','dd-mon-yyyy'),'mm')-1,'mm') from dual;

How to create Profile ?

Profile Option in Oracle Apps:

Profile Option values control the behavior of Oracle Apps, in other words they determine how Oracle Apps should run. The value for a profile option can be changed any time.

For Example we have a profile option called MO: Operating Unit. Assigning a value to this profile option will determine what operating unit it should use when a user gets into a particular responsibility.

We have two types of Profile options – System and Personal profile options depending on to whom they are visible and who can update their values.

System Profile options are visible and can be updated only in System Administrator responsibility. In short they will be maintained by a System Administrator only.

User Profile Options are visible and can be updated by any end user of Oracle Apps.

Profile Options can be set at different levels. Site level being the highest and User being the lowest in the heirarchy. If a profile option is assigned at two levels, then value assigned at lowest level takes the precedence.

• Site (restricted to the whole of Apps)
• Application ( restricted only to a particular application like Payables, Receivables)
• Responsibility (restricted only to a particular responsibility)
• Organization (restricted to a particular organization)
• User (restricted to a user)

Now let’s see how to create a new profile option and assign a value to it.
Say you want to create a profile option called erpSchools Debug and the possible values that the user can choose are Yes, No. If the user chooses Yes, then the custom concurrent program will print all the debug messages otherwise the debug messages will not printed in the log file of the concurrent request.

Creating a Profile Option:
Navigation: Application Developer/system administration > Profile

Profiles window will open up where you enter the below details:
Name: Give a meaningful and unique name for this profile option

Application: Enter the application for which this profile option will be used like Payables, Receivables etc. If it is for a custom program then choose the custom application.

User Profile Name: Again enter a meaningful and unique name. This is the name that the users will see in the Profile Options window.

Description: Enter the purpose or usage of this option.

Heirarchy Type Access Levels: At what levels this profile option should be visible and updatable.

Active dates: Enter the dates on which the profile option becomes active/inactive
User Access:

Visible – Determines whether your end users can see and query this profile option in their personal profiles. Otherwise, they cannot query or update values for this option.

Updatable – Determines whether your end users can change the value of this profile option using their Profile Values window. Otherwise, your system administrator must set values for this profile option.

SQL Validation: You can write up a SQL statement to display the selected rows in LOV. If this section is left blank, there will no LOV instead the user can enter any free text as value.

Save the work.

Assign a value to the profile option:
Navigation: System Administrator > Profile > System
Enter the below when the window opens and click on Find button
Under Display section, Choose at what level you want to assign a value.
In the Find field, enter the profile option name to which you want to assign the value.

Enter the value that you want to assign to profile option at the appropriate level and save.

To derive a value of profile option from PL/SQL procedure:
We have seen how to create and assign value to profile option. Most of the times, custom concurrent program will need to get the value of a profile option that will determine its course of actions. You will use API called FND_PROFILE.

1.To get the value of profile option:

select FND_PROFILE.value('ERPSCHOOLS DEBUG') from dual;

Remember to pass on the name of the profile option and not the user profile name here.

2. To set value to profile option from PL/SQL procedure:

Begin

FND_PROFILE.put ('ERPSCHOOLS DEBUG','No');

end;