Script for Creating sales person(CRM)

DECLARE

CURSOR get_credit_id(v_name VARCHAR2)
IS
select sales_credit_type_id
FROM oe_sales_credit_types
WHERE name = v_name;

l_Credit_id NUMBER;
l_salesrep_id NUMBER;
l_msg_count NUMBER;
l_msg_Data varchar2(2000);
l_return_status varchar2(1);

l_msg_index_out  NUMBER;

BEGIN

OPEN get_credit_id('Quota Sales Credit');  ----Pass the Sales Quota Type
FETCH get_Credit_id INTO l_credit_id;
CLOSE get_Credit_id;

dbms_application_info.set_client_info('240'); ---Set the ORG if being run from SQLPLUS

jtf_rs_salesreps_pub.CREATE_SALESREP(
 P_API_VERSION                  => 1.0,
 P_INIT_MSG_LIST                => 'T',
 P_COMMIT                       => 'T',
 P_RESOURCE_ID                  => 100000020,          ----Get the resource id from JTF_RS_RESOURCE_EXTNS
 P_SALES_CREDIT_TYPE_ID         => l_credit_id,
 P_NAME                         => 'Kanti Jinger',  ----Name of the resource
 P_STATUS                       => NULL,
 P_START_DATE_ACTIVE            => sysdate,
 P_END_DATE_ACTIVE              => NULL,
 P_GL_ID_REV                    => NULL,
 P_GL_ID_FREIGHT                => NULL,
 P_GL_ID_REC                    => NULL,
 P_SET_OF_BOOKS_ID              => 1001,          ------Replace with your set of Books ID
 P_SALESREP_NUMBER              => 'ABCD00991',
 P_EMAIL_ADDRESS                =>  ----Replace with Email ID of the user
 P_WH_UPDATE_DATE               => sysdate,
 P_SALES_TAX_GEOCODE            => NULL,
 P_SALES_TAX_INSIDE_CITY_LIMITS => NULL,
 X_RETURN_STATUS                => l_return_status,
 X_MSG_COUNT                    => l_msg_count,
 X_MSG_DATA                     => l_msg_data,
 X_SALESREP_ID                  => l_salesrep_id);

dbms_output.put_line('return status is ' || l_return_Status);

 FND_MSG_PUB.GET(p_msg_index     => 1,
                 p_encoded       => 'F',          
                 p_data          => l_msg_data,
                 p_msg_index_out => l_msg_index_out);  
                         
 DBMS_OUTPUT.put_line('API Error Message : '||l_msg_data);

dbms_output.put_line('msg data is  ' || l_msg_data);
dbms_output.put_line('Sales Rep id is ' || l_salesrep_id);

END;

Users

Any person who needs to access the Oracle Applications should have a user id. A user is registered by System Administrator (or anybody having System Administrator responsibility). Often, first the person is registered in HR module as an employee or contractor and then associated with the user. The following screen shows the user definition screen.

A user is assigned a user name and password. The password needs to be reset on the first login of the user.

The person field is a list of value coming from HR. If the person is already registered in HR, his name is selected in the Person field.

User can be assigned the access for a limited period by giving Effective Dates.

User can be forced to change password periodically by giving password expiration date values.

User is also assigned the responsibilities. The responsibilities can be assigned for a particular period by giving effective dates.

Oracle Applications comes with default some default users like SYSADMIN, APPSMGR, and AUTOINSTALL. These users are used for carrying out administrative tasks in Oracle Applications.

Technical Details on User Definition

User definition is stored in FND_USER table. This table is owned by FND module. The main fields of this table are as follows:

User_ID Automatically generated unique number

User_Name Name as entered on the user definition screen

Employee_ID Employee ID from HR module. Populated if the Employee name is associated with the user.

Party_ID Party_ID from HZ_PARTIES table. Each user is also created in HZ_PARTIES table as a party.

  

Whenever user creates or updates a transaction in any module, the transaction record is stamped with the user id. The user id is usually stored as CREATED_BY or LAST_UPDATED_BY field in the respective transaction table.

User ID of the current user can be accessed using a package FND_GLOBAL. Suppose, we need to get the current user id in a custom program. We can get the user id using the following code:

l_user_id := FND_GLOBAL.USER_ID;

When a user record is created, it is also automatically inserted in the workflow role table (WF_ROLES). This table is used for sending workflow notification to the user whenever a workflow requires such notification to be sent.

Menus

When different functions are organized in a hierarchical fashion, it is called a menu. Menu is a component of oracle applications which is used for navigation purpose. A menu can be made of different functions or it can have sub-menus under it. A menu is a tree kind of structure, the lowest node being a function. Top menu is usually assigned to a responsibility. The following figure shows menu structure for GL Budget Super Menu:

A custom menu can also be created and assigned to a custom responsibility. When creating a new custom menu, one should start at the bottom, assigning functions to sub-menus and then finally assigning sub-menus and functions to a top menu.

The following screen is used for defining new menu:

The important fields in this screen are described below:

Menu:  Short Name of the Menu

User Menu Name: User defined name of the Menu

Menu Type:  Choose ‘Standard’

Description:  Description of the Menu.

The menu items appear in the sequence given. The sequence can be 1,2,3..or 10,20,30…etc. For each menu item, a prompt should be given if we want that menu item prompt to appear. Then we can either specify a Submenu or a Function. The sub-menu should have been already defined as a menu before using in the menu.

By Clicking ‘View Tree’ button, it is possible to see the menu in graphical format.

Technical Details of the Menu

A menu detail is stored in the following tables:

FND_MENUS: This table contains Menu Header information.

FND_MENU_ENTRIES: This table contains detail of all the entries in the menu. It contains columns Entry Sequence, Sub-Menu ID and Function ID.

Attachments

Oracle Applications provide a functionality to attach not-structured data, such as image, document, URLs and files, to a record/form/function. This functionality is already enabled for some standard forms, and can be enabled for other forms by defining meta-data. The attachment functionality is invoked by clicking on the Clip button as shown in the figure (1).

Using attachment functionality, it is possible to attach:

• Long Text (Oracle LONG data type, 2GB)
• Short Text (up to 4000 Bytes)
• File  (Any kind of file like image, word doc, excel, PDF etc.)
• Web Page (URL of a web page)

The attached document can be associated with a category. This helps organizing various documents attached to a particular record.

Whenever a record is queried, the corresponding attached document can be retrieved by clicking on the clip button.

Uses

Attachment functionality is useful for attaching non-structured data with a particular record. For example, one may want to associate a scanned copy of purchase order with the PO record, or a copy of physical invoice with the invoice record. In case of contracts, we may want to associate a scanned copy of the contract with the contract record.

How to Enable an attachment

Enabling an attachment consists of defining the following things:

• Defining an entity (Table or view)
• Defining a category (we may also use pre-defined categories, like Miscellaneous)
• Defining attachment function

We will explain the enabling of attachment by taking an example. Suppose, we want to enable attachment for User Definition Screen (System Administrator => Security => User => Define). The screen is shown below (Figure 2):

1. Identify the name of the form. This can be done by clicking Help => About Oracle Applications. In this case, the form name is FNDSCAUS.
2. Identify the name of the base table which stores the data. In our example, the base table name is FND_USER. This can be identified by doing a query (F11 => Ctrl F11) and then doing Help => Diagnostics => Examine, Block = System, field = Last_Query.
3. Identify the primary key of the table. In our case, the primary key is USER_ID.
4. Identify the block name where we want to associate the attachment. In the example, block name is USER.
5. Navigate to Application Developer => Attachments => Document entities and enter the details as given below. Click on Save:

1. Now go to Application Developer => Attachments => Document categories and define a new category as below. Click on SAVE:

1. Go to Application Developer => Attachments => Attachment functions and enter the detail as below:

1. Now click on Categories button, and enter the category we just defined. Click on Save.

1. Click on Blocks button and enter the information as given below. Click on SAVE:

1.  Click on Entities button and enter the entity information as given below:

1. Click on Primary Key fields tab and enter the primary key information as given below. Click on SAVE.
   
   

We are now done with the setup required for enable the attachment in the user definition screen. Now go to the user screen and see if the click button is enabled.

How to use the attachment feature

For attaching a document, first query a record in User screen and then click on the Clip button.

Enter sequence Number (any unique number), Category (which is ‘User Application’) and data type.

Technical Details of the Attachment Functionality

Attachment Setup Tables

Document categories defined using Application Developer => Attachment => Categories are stored in the table FND_DOCUMENT_CATEGORIES

Document entities defined using Application Developer => Attachment => Entities are stored in the table FND_DOCUMENT_ENTITIES.

Attachment functions are stored in the following tables:

· FND_ATTACHMENT_FUNCTIONS

· FND_ATTACHMENT_BLOCKS

Attachment Usage tables

FND_ATTACHED_DOCUMENTS – stores information on entity, key and the associated attachment information.

FND_DOCUMENT_SHORT_TEXT – stores the details of short text type of an attachment.

FND_DOCUMENT_LONG_TEXT – stores the details of long text type of an attachment.

FND_DOCUMENTS/FND_DOCUMENTS_TL – stores the details of attached documents.

FND_LOBS – stores the actual File for attached documents

Responsibilities

To achieve security in Oracle Applications, related functions are organized in the form of a responsibility. For example, AR Super User responsibility will have all the functions required for Receivable super user. This responsibility is then assigned to users. A responsibility can be assigned to many users and a user can be assigned many responsibilities.

Responsibility is assigned to user in the user definition screen. Oracle comes with many pre-defined responsibilities. During implementation, often we need many custom responsibilities to be defined. The following figure shows the responsibility definition screen.

Important fields in this screen are described below:

Responsibility Name:  Name of responsibility. It can be any user understandable name.

Application:  Application Associated with the responsibility. If it is a custom responsibility, we usually associate custom application.

Responsibility Key:  It is a short name for responsibility

Available From:  Default value is ‘Oracle Applications’. In case a responsibility invokes HTML based forms, then one can choose ‘Oracle Self Service Web Applications.

Data Group:  Choose ‘Standard’.

Data Group Application: Application associated with the data group. In case of custom responsibilities, we choose custom application.

Menu:  Top menu which will be shown when user goes to the responsibility. In case we have a custom menu that can also be assigned here.

Request Group Name:  Name of the request group (Discussed in Concurrent program section).

Request Group Application: This is defaulted based on request Group.

Technical Details on Responsibility

Responsibility is stored in the following tables:

FND_RESPONSIBILITY

FND_RESPONSIBILITY_TL

Important fields in FND_RESPONSIBILITY table are as follows:

Application ID Application ID of the Application chosen

Responsibility ID Unique ID generated automatically

Menu ID Menu ID of the top menu chosen

Data Group ID ID of the Data group chosen

Request Group ID ID of the request group chosen

Responsibility Key Short unique name of responsibility entered

Name and Description of the responsibility is stored in FND_RESPONSIBILITY_TL table.

A view FND_RESPONSIBILITY_VL is available which a join between FND_RESPONSIBILITY and FND_RESPONSIBILITY_TL table. This view can be used to get responsibility name and description based on responsibility ID.

  

User - Responsibility Association

A view FND_USER_RESP_GROUPS_ALL can be used to find out the association of the user with the responsibility. This view contains user id and responsibility id. This view can be joined with FND_USER and FND_RESPONSIBILITY_TL table to select all the responsibilities assigned to an user.

Profiles

Oracle Applications provides a way to alter the behavior of a process/business flow during run time by setting up a Profile option. The standard Oracle Applications code look for the value of profile option and depending upon the value, it takes appropriate decision. For example, we may want to setup different limits for different users for approval of a purchase order. In this case, we set the amount for each user in a standard profile option. During approval business process, Oracle Applications can look at this limit and take appropriate action.

Oracle Applications uses a set of profile options that are common to all the application products. Further each module of Oracle Applications comes with several pre-defined profile options. These profile options are set with appropriate values during the module configuration by functional expert. The value to be set for different standard profile options depends on the business requirement.  

The profile options can be setup at Site, Application, Responsibility and User level. If a profile is setup at multiple levels, the lowest level value takes precedence over the higher level. For example, a profile value setup at user level takes precedence over the profile value setup at Responsibility level and so on.

In addition to the standard profile options, it is also possible to define custom profile options. The custom profiles can be used in custom code developed during Oracle applications implementations. A custom profile option is defined using Application developer responsibility.

Profile values are maintained by system administrator.

Business Uses Scenarios

Some of the real life business scenarios where profile options are used are as follows:

1. A Profile option ‘Max Discount Percentage’ can be set against each Order entry clerk. Depending upon this value, Order entry clerk can give the discount to a customer.
2. A profile option ‘Debug ON’ can be set to Yes or No. When it is set to Yes, program will run in the debug mode.
3. A profile option ‘MO: Operating Unit’ can be set for each responsibility. When a user logs to that responsibility, he will see data only for that particular operating unit.

How to create a new profile

Go to Application Developer Responsibility and navigate to Profile function. Press F11 to enter query and enter ‘CONC_SAVE_OUTPUT’. Press Control F11 to execute the query. The following screen comes up:

The various fields used in the above screen are explained below:

Name: Profile Code. This has to be unique in the system. This code is normally used in the FND functions to derive the value of a profile option in PL/SQL programs.

Application: Application Name to which profile is attached.

User Profile Name: Descriptive name of the profile

Description: Description of the profile

Hierarchy Type: Hierarchy type defines the applicable levels where profiles can be set. Profiles Hierarchy type and the corresponding hierarchy levels are given in the below table:

Hierarchy Type	Applicable Levels
Security	Site, Application, Responsibility, User
Server	Site, Server, User
Server-Responsibility	Site, Server, Responsibility, User
Organization	Site, Organization, User

When a hierarchy type is selected, only the corresponding levels are enabled. Further, the profile values are evaluated from bottom to top. That means, for example, for a ‘Security’ hierarchy type, a profile value set at user level will take precedence over the value set at responsibility level, and the value set at responsibility level will take precedence over the application level and so on.  

Active Dates:

 Start and end date of the profile. By Default, start date is system date and end date is NULL. A profile can be disabled by putting an end date.

  

User Access:

A profile value can be changed by user’s personal profile window also. In these fields we decide if the end user can view the profile value and if the value of the profile can be changed.

Visible : If checked, Profile will be visible to the end user

Updatable : If checked, Profile can be updated by the end user. If not checked, then only system administrator can set the value of the profile at user level.

SQL Validation

Sometime it is required to provide a list of values for setting the profile option values. In this case, we can provide a SQL command to select the values. For example, in the screen above, we have the following SQL:

SQL="select meaning \"Concurrent:Save Output\",lookup_code

 into :visible_option_value,:profile_option_value

 from fnd_lookups

 where lookup_type = 'YES_NO'"

COLUMN="\"Concurrent:Save Output\"(*)"

In the above SQL, :visible_option_value is the profile option value visible to the user and :profile_option_value is the profile option value that will be internally stored.

The column alias is enclosed with slash and double quote at the beginning and end of the column name if there is space in the name. The keyword COLUMN is used to indicate which columns to show in the LOV. Column length can be explicitly given or it can be dynamically derived by using (*) after the column name. The following example shows the use of COLUMN keyword.

COLUMN="Department(20), LOCATION(*)"

  

How to setup a profile value

Profile values are setup using System Administrator responsibility. Navigate to Profile => System, and Enter ‘Concurrent:Save Output’ in profile filed. Click on FIND button. (If value needs to be setup at other levels, corresponding level values can be entered before clicking on FIND button).

In the below screen, profile values can be setup at the appropriate value.

Technical details

Profile definition is stored in the following tables:

FND_PROFILE_OPTIONS

FND_PROFILE_OPTIONS_TL

These tables can be joined by column PROFILE_OPTION_NAME.

The value for a profile is stored in the following table:

FND_PROFILE_OPTION_VALUES

We can use the following statement to retrieve the value of a profile during run time:

l_profile_value := FND_PROFILE.VALUE(‘<Profile Short Name>’);

One of the most widely used profile is ‘MO: Operating Unit’ Profile. This profile has a code of ORG_ID. To get the value of current operating unit, use the following statement:

l_org_id := FND_PROFILE.VALUE(‘ORG_ID’);

To set a particular operating unit (for example, in SQLPLUS or TOAD), use the following PL/SQL code:

BEGIN

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘204’);

--204 is the ORG_ID value.

END;

Value Sets

Value-set is a group of values. It can also be thought of as a container of values. The values could be of any data type (Char, Number etc.) A value set is used in Oracle Applications to restrict the values entered by a user. For example, when submitting a concurrent program, we would like user to enter only valid values in the parameter. This is achieved by associating a value set to a concurrent program parameter.

A Value Set is characterized by value set name and validation. There are two kinds of validations, format validation and Value validation. In the format validation, we decide the data type, length and range of the values. In the value validation, we define the valid values.

The valid values could be defined explicitly, or could come implicitly from different source (like table, another value-set etc.)

Uses

Value-set is an important component of Oracle Applications used in defining Concurrent program parameters, Key Flex field and descriptive flex field set of values.

Some of the scenarios where value-set is used are given below:

1. In a concurrent program, we may want users to enter only number between 1 and 100 for a particular parameter.
2. In a concurrent program, we may want users to enter only Yes or No for a particular parameter.
3. Suppose a concurrent program has two parameters. First parameter is department and second parameter is employee name. On selecting a particular department, we want to show only those employee names which belongs to the selected department.
4. In a descriptive flex field enabled on a particular screen, we want to show only a designated list of values for selection by a user.
5. In case of accounting reports, we may want users to enter a range of key flex field values (concatenated segments).

How to create Value-Set in Oracle Applications

Go to System Administrator Responsibility and Navigate to Application => Validation => Set.

The following screen shows the Value-Set definition screen:

The various fields are explained below:

Value Set Name:  Any user defined unique name

Description:  Description of the value set

List type:  Three choices are available for this field:

· List of Values

· Long List of Values

· Pop-List

The list type defines how the values will appear when this value set is used. Choosing ‘List of values’ displays the values as LOV (showing all the values at once). Choosing ‘Long List of Values’ displays the values as Long List where Search facility will be available. This is used when numbers of values are expected to be large. Choosing pop-list displays the values as pop-list.

Security type: Three choices are available for this field:

§ No Security

§ Hierarchical Security

§ Non-Hierarchical Security

Format Validation

Format Type

 Possible values for this field are:

 Char

 Date

 Date Time

 Number

 Standard Date

 Standard Date Time

 Time

Maximum Size: Maximum size of the value

Precision: Applicable when format type is number

Numbers Only: When this is checked, only numbers are allowed

Upper Case Only: This is applicable when Format type is Char

Right Justify and Zero-Fill Numbers: Applicable only for Numbers

Min Value: Min Value allowed

Max Value: Max Value Allowed

Value Validation

Possible values of the value validations are as follows:

None: when this is chosen, no value-validation is done, only format validation is done. For example, we want a user to enter a value between 1 and 100. In this case, we can set the format validation (by setting format type as Number and Min and Max value of 1 and 100 respectively).

Independent: When this is chosen, the individual values are defined using the navigation system administrator => Application => Validation => Values. For example, suppose we want user to select values of Yes or No for a parameter. We can define Yes and No values for this case.

Table: When table validation is chosen, the values for the value-set comes from an oracle application table. After choosing this value, click on ‘Edit Information’ button to enter table name, column name and WHERE condition as show in the below screen:

Dependant: This type of validation is chosen when value of this value-set is dependant on some other independent value set. After choosing this validation type, click on Edit Information button to enter the independent value-set as given in the below figure.

Special: Special validation value sets allow you to call key flex field user exits to validate a flex field segment or report parameter using a flex field within flex field mechanism. You can call flex field routines and use a complete flex field as the value passed by this value set.

Pair: Pair validation value set allows user to pass a range of concatenated Flex field segments as parameters to a report.

Translatable In-dependant and Translatable dependant Value Sets: These value sets are similar to in-dependant and dependant value sets respectively. Only difference is that this type allows values to be translated and shown to the user in the user’s language.

Technical Details of the Value-Sets

Value-set definition is stored in FND_FLEX_VALUE_SETS table. The independent values are stored in the FND_FLEX_VALUES. The two tables can be joined together by FLEX_VALUE_SET_ID.

FNDLOAD utility can be used to migrate a value-set from one instance to another instance.