Download - Oracle Apps AOL
Author : Amit R Tiwari
AOL is a collection of pre-built application components and facilities and it consists of forms, subroutines, concurrent programs and reports, database tables and objects, messages, menus, responsibilities, flexfield definitions, various guides and library functions.
What Is AOL?
6/19/20122
Author : Amit R Tiwari 4
AOL-Integration
Application User
UserName
Password
Responsibilty
Main Menu
Menu
Form
Menu
Form
Request Security Group
Request Set
Concurrent Program
Security Rules
FlexFields Value
Profile Option Assignment
6/19/2012
Author : Amit R Tiwari 6
User(who uses the Application) Responsibility(Assigned to User) Profile(Level of Access) Concurrent Programs(To Perform Various Task) Valueset Requestset Request group Flexfield( To capture info) Etc.
What AOL consist of ?
6/19/2012
Author : Amit R Tiwari 9
Users and Responsibilities are created in oracle applications in order to control the access to the data.
Users are assigned a set of responsibilities through which they can retrieve or modify certain set of data.
Responsibility is a grouping of access in a logical manner.
Accounts payable responsibility would contain all the privileges to create invoices, make payments etc. All the users from the payables department would be given this responsibility.
We can create users, responsibility through system administrator responsibility in oracle applications.
Overview
6/19/2012
Author : Amit R Tiwari 10
1. Login to the E-Business Suite Home Page2. Be connected as sysadmin3. Choose the "System Administrator" from the Navigator4. Choose from "Security: User" Define option5. Enter the information for this user and add some
responsibilities to the user if you want to do it now
Creation of User Navigation – System Administrator ->Security -> User -> Define
Creation of Responsibility Navigation – System Administrator ->Security -> Responsibility ->
Define
User & Responsibility Creation Navigation steps
6/19/2012
Author : Amit R Tiwari 11
Creation of User
Navigation – System Administrator ->Security -> User -> Define
Navigation
Figure 1. Create User Navigation
6/19/2012
Author : Amit R Tiwari 14
UserName Give the username in this field. Password Enter desired password twice in this field. You would be
required to change the password when you login first time with this username.
Description Provide brief description of the user in this field.
Password Expiration
Days . NoAccesses .NoNone
Person You can attach employee to this user. This is used for HRMS applications
Customer To attach customer to this user.
Supplier To attach supplier to this user.
Email Provide email address for the user. Its use to send notifications to the user through oracle applications.
Fax Fax number for the user.
Effective Dates From and To dates between which the user would be active.
Responsibilities List of responsibilities assigned to the user.
Fields
6/19/2012
Author : Amit R Tiwari 17
Attribute : Select an attribute you want used to determine which records this user can access. You can select from any of the attributes assigned to the user's responsibility.Value : Enter the value for the attribute you want used to determine which records this user can access. Securing attributes are used by Oracle HTML-based applications to
allow rows (records) of data to be visible to specified users or responsibilities based on the specific data (attribute values) contained in the row. You may assign one or more values for any of the securing attributes assigned to the user. If a securing attribute is assigned to both a responsibility and to a user, but the user does not have a value for that securing attribute, no information is returned for that attribute.
For example, to allow a user in the ADMIN responsibility to see rows containing a CUSTOMER_ID value of 1000, assign the securing attribute of CUSTOMER_ID to the ADMIN responsibility. Then give the user a security attribute CUSTOMER_ID value of 1000.
When the user logs into the Admin responsibility, the only customer data they have access to has a CUSTOMER_ID value of 1000.
Fields
6/19/2012
Author : Amit R Tiwari 18
Creation of Responsibility
Navigation – System Administrator ->Security -> Responsibility -> Define
6/19/2012
Author : Amit R Tiwari 21
Responsibility Name - If you have multiple responsibilities, a pop-up window includes this name after you sign on.
Application - This application name does not prevent the user of this responsibility from accessing other applications' forms and functions if you define the menu to access other applications.
Responsibility Key - This is a unique name for a responsibility that is used by loader programs. Loaders are concurrent programs used to "load" such information as messages, user profiles and user profile values into your Oracle Applications tables. To help ensure that your responsibility key is unique throughout your system, begin each Responsibility Key name with the application short name associated with this responsibility.
Effective Dates From/To - Enter the start/end dates on which the responsibility becomes active/inactive. The default value for the start date is the current date, and if you do not enter an end date, the responsibility is valid indefinitely.
You cannot delete a responsibility because its information helps to provide an audit trail. You can deactivate a responsibility at any time by setting the end date to the current date. If you wish to reactivate the responsibility, change the end date to a date after the current date, or clear the end date.
Available From - A responsibility may be associated with only one applications system. Select between Oracle Self-Service Web Applications or Oracle Applications.
Data Group - Name/Application The data group defines the pairing of application and ORACLE username. Select the application whose ORACLE username forms connect to when you choose this responsibility. The ORACLE username determines the database tables and table privileges accessible by your responsibility. Transaction managers can only process requests from responsibilities assigned the same data group as the transaction manager.
Menu - The menu whose name you enter must already be defined with Oracle Applications.
Web Host Name - If your Web Server resides on a different machine from your database, you must designate the host name (URL) here. Otherwise, the Web Host Name defaults to the current database host server.
Web Agent Name - Enter the PL/SQL Agent Name for the database used by this responsibility. If you do not specify an Agent Name, the responsibility defaults to the agent name current at log-on.
Request Group Name/Application - If you do not assign a request security group to this responsibility, a user with this responsibility cannot run requests, request sets, or concurrent programs from the Submit Requests window, except for request sets owned by the user. The user can access requests from a Submit Requests window you customize with a request group code through menu parameters.
Menu Exclusions Block
Define function and menu exclusion rules to restrict the application functionality accessible to a responsibility.
Type : Select either Function or Menu as the type of exclusion rule to apply against this responsibility.
When you exclude a function from a responsibility, all occurrences of that function throughout the responsibility's menu structure are excluded. When you exclude a menu, all of its menu entries, that is, all the functions and menus of functions that it selects are excluded. Name : Select the name of the function or menu you wish to exclude from this responsibility. The function or menu you specify must already be defined in Oracle Applications.
Fields
6/19/2012
Author : Amit R Tiwari 24
API is fnd_user_pkg.createuser()
How Do we use this API to create user:
we create a script for creation of user;
Why Needed ?When Multiple User is need to be created we use script file.
Creating User From Backend Using API
6/19/2012
Author : Amit R Tiwari 25
DECLARE v_session_id INTEGER := userenv('sessionid'); v_user_name VARCHAR2(30) := upper('&Enter_User_Name');BEGIN --Note, can be executed only when you have apps password. -- Call the procedure to Create FND User fnd_user_pkg.createuser(x_user_name => v_user_name ,x_owner => '' ,x_unencrypted_password => 'oracle' ,x_session_number => v_session_id ,x_start_date => SYSDATE - 10 ,x_end_date => SYSDATE + 100 ,x_last_logon_date => SYSDATE - 10 ,x_description => 'appstechnical.blogspot.com' ,x_password_date => SYSDATE - 10 ,x_password_accesses_left => 10000 ,x_password_lifespan_accesses => 10000 ,x_password_lifespan_days => 10000 ,x_employee_id => 30 /*Change this id by running below SQL*/ /* SELECT person_id ,full_name FROM per_all_people_f WHERE upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%' GROUP BY person_id ,full_name */
,x_email_address => '[email protected]' ,x_fax => '' ,x_customer_id => '' ,x_supplier_id => ''); fnd_user_pkg.addresp(username => v_user_name ,resp_app => 'SYSADMIN' ,resp_key => 'SYSTEM_ADMINISTRATOR' ,security_group => 'STANDARD' ,description => 'Auto Assignment' ,start_date => SYSDATE - 10 ,end_date => SYSDATE + 1000);END;/
6/19/2012
Author : Amit R Tiwari 28
API is◦ fnd_user_resp_groups_api.insert_assignment
How Do we use this API :
we create a script.
Why Needed ?When Multiple User is need to be Assigned responsibility
we use script file.
Assigning Responsibility From Backend Using API
6/19/2012
Author : Amit R Tiwari 29
DECLARE CURSOR user_csr(v_user_name VARCHAR2) IS
SELECT user_idFROM FND_USERWHERE user_name = v_user_name;
CURSOR resp_csr(v_resp_name VARCHAR2) ISSELECT responsibility_id, application_idFROM fnd_responsibility_tlWHERE responsibility_name = v_resp_name;
error_excp EXCEPTION;l_user_id NUMBER;l_responsibility_id NUMBER;l_application_id NUMBER;
BEGIN
OPEN user_csr('XXX901'); ----Pass the user name here FETCH user_csr INTO l_user_id; IF (user_csr%NOTFOUND) THEN DBMS_OUTPUT.PUT_LINE('Invalid User'); CLOSE user_csr; RAISE error_excp; END IF; CLOSE user_csr;
DBMS_OUTPUT.PUT_LINE('User ID is ' || l_user_id); OPEN resp_csr('System Administrator'); ----Pass the responsibility name here
FETCH resp_csr INTO l_responsibility_id, l_application_id; IF (resp_csr%NOTFOUND) THEN DBMS_OUTPUT.PUT_LINE('Invalid Responsibility'); CLOSE resp_csr; RAISE error_excp; END IF; CLOSE resp_csr; DBMS_OUTPUT.PUT_LINE('Responsibility ID is ' || l_responsibility_id);
FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(user_id => l_user_id ,responsibility_id => l_responsibility_id ,responsibility_application_id => l_application_id ,security_group_id => 0 ,start_date => SYSDATE - 1 ,end_date => NULL ,description => NULL);
COMMIT; DBMS_OUTPUT.PUT_LINE('Successfully Assigned Responsibility'); EXCEPTION
WHEN error_excp THEN NULL; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;/ 6/19/2012
Author : Amit R Tiwari 30
Where the detail of Responsibility is stored?
- FND_RESPONSIBILITY
-FND_RESPONSIBILITY_TL
6/19/2012
Author : Amit R Tiwari 33
Definition:
Profiles are the changeable options which affect the way Oracle Application runs.
PROFILE
6/19/2012
Author : Amit R Tiwari 34
The profiles are of two types those are given below.
1. System Profile and 2. User Profile.
Types:
6/19/2012
Author : Amit R Tiwari 35
The Profile values will be set in different levels those are given below.
1. Site 2. Application 3. Responsibility 4. User 5. Server 6. Organization
DIFFERENT LEVELS
6/19/2012
Author : Amit R Tiwari 36
Site: This field displays the current value, if set, for all users at the installation site.
Application: This field displays the current value, if set, for all users working under responsibilities owned by the application identified in the Find Profile Values block.
Responsibility: This field displays the current value, if set, for all users working under the responsibility identified in the Find Profile Values block.
User: This field displays the current value, if set, for the application user identified in the Find Profile Values block.
DIFFERENT LEVELS
6/19/2012
Author : Amit R Tiwari 45
Form Registration
Registering a FormNavigation – Application Developer -> Application->Form
6/19/2012
Author : Amit R Tiwari 46
Registering a FormNavigation – Application Developer -> Application->Form
Click on Form and you will see Next screen.
6/19/2012
Author : Amit R Tiwari 47
Form : Enter the file name of your form (the name of the .fmx file) without extension. Your form filename must be all uppercase, and its .fmx file must be located in your application directory structure.Application : Enter the name of the application which will own this form.User Form Name : This is the form name you see when selecting a form using the Functions window.Description : Enter a suitable description for your form.
6/19/2012
Author : Amit R Tiwari 48
Register Form FunctionsNavigation – Application Developer -> Application->Function
Click on Function and you will see Next screen
6/19/2012
Author : Amit R Tiwari 50
FieldsFunction : Enter a unique function name for your function. This function name can be used while calling this program programmatically. This name is not visible to the user through other forms.Form : Select the form name which you have registered.Application : Select the application name for your form.Parameters : Enter the parameters that you want to pass to your form function. E.g. Query_only.
6/19/2012
Author : Amit R Tiwari 51
Creating Menu of FunctionsNavigation – Application Developer -> Application->Menu
Click on Menu and you will see Next screen
6/19/2012
Author : Amit R Tiwari 52
FieldsMenu : Enter the descriptive name for the menu. This name is not visible to the user.User Menu Name : The user menu name is used when a responsibility calls a menu.Menu Type : The options in menu type include:Standard - for menus that would be used in the Navigator formTab - for menus used in self-service applications tabsSecurity - for menus that are used to aggregate functions for data security or specific function security purposes, but would not be used in the Navigator formSeq : Enter a sequence number.Prompt : Enter the prompt that the users will see for the menu.Submenu : If you want another menu to be called from this menu, then enter this menu name in this field.Function : Enter the form function name in this field.Description : Enter a suitable description for the menu.Grant :The Grant check box should usually be checked. Checking this box indicates that this function is automatically enabled for the user. If this is not checked then the function must be enabled using additional data security rules.View Tree :Click on View Tree Button and you will see following screen with the full hierarchy of the menu.
6/19/2012
Author : Amit R Tiwari 55
Creating ResponsibilitiesNavigation – System Administrator -> Security->Responsibility->Define
6/19/2012
Author : Amit R Tiwari 57
FieldsResponsibility Name : Enter the responsibility name for the new responsibility.Application : Enter the application name you want to associate the new responsibility to.Responsibility Key : The responsibility key is a unique identification for the responsibility. This is used in the loader programs for internal purposes.Description : Enter a suitable description for the new responsibility.Effective Dates : Enter the date range in which the responsibility will be active in the From and To fields.Available From : In the available from field box, select whether you want this responsibility to be available from Oracle applications or from Oracle Self Service Web Applications or from Oracle mobile Applications.Data Group : The data group defines the database user name that oracle applications use to connect to the database when you connect to applications using this responsibility.Menu Name : Enter the menu name that you want to associate with this responsibility.Web Host Name : If your Web Server resides on a different machine from your database, you must designate the host name (URL) here. Otherwise, the Web Host Name defaults to the current database host server.Web Agent Name : Enter the PL/SQL Agent Name for the database used by this responsibility. If you do not specify an Agent Name, the responsibility defaults to the agent name current at log-on.Request Group : The request group would define which requests the users with this responsibility can run. If no request group is assigned to this responsibility then the users with this responsibility will not be able to run requests for which he is not the owner.
Enter the ‘Request group’ name and ‘Application’ name for the request group that you want to assign to this responsibility.
6/19/2012
Author : Amit R Tiwari 58
User◦ Creation Of User
Responsibility◦ Creation of Responsibility◦ Assigning Responsibility To User
Profile◦ Assigning Profile
Form Registration◦ Menu◦ Function◦ Responsibility
Brief Summary Till Now
6/19/2012
Author : Amit R Tiwari 60
A concurrent program is an instance of an execution file, along with parameter definitions and incompatibilities.
Concurrent Programs
6/19/2012
Author : Amit R Tiwari 61
Oracle Reports – used for the RDF reports Host – used for shell scripts, basically the language of the host operating
system PL/SQL Stored procedure – used to run the stored procedure through oracle
applications SQL*Loader – used to run the sql loader programs SQL*Plus - used to run the anonymous PL/SQL blocks. It will get executed in the
same fashion as you are running on SQL Plus. Java Stored Procedure – The execution file is a Java stored procedure. Java Concurrent Program – Used for program written in Java. Spawned – used for c or pro*c Program. Mainly used by standard oracle
interfaces. Perl Concurrent Program – used for programs written in CGI Perl. Request Set Stage Function – PL/SQL stored function that can be used to
calculate the completion statuses of request set stages. Immediate – execution file is a program written to run as subroutine of the
concurrent manager. Oracle doesn’t recommend use of this executable type. Multi-Language function – execution file is an MLS function that supports
running concurrent program in multiple languages.
Q: What are the different types of scripts/programs that can be attached to concurrent programs?
6/19/2012
Author : Amit R Tiwari 62
Answer: Broadly speaking there are three steps when developing a concurrent program in Oracle Apps
Step 1. Make Oracle Apps identify the executable
Step 2. Provide a handle to the executable by means of defining a concurrent program
Step 3. Make this concurrent program accesible to selected users via their responsibility.
Question: What are the basic steps when defining a concurrent program.
6/19/2012
Author : Amit R Tiwari 63
Step 1. Make Oracle Apps identify the executable
In Oracle Apps we have something called as concurrent program executable. Concurrent program executable is defined to register a script or a procedure for its usage within oracle apps.
Step 2. Provide a handle to the executable by means of defining a Concurrent Program.
We need to define a concurrent program and attach that to the executable defined in above step.
Step 3. Make this concurrent program accesible to selected users via their responsibility.
We do this by adding the concurrent program to something called as request group. The request group is either associated with a responsibility or is passed in as a parameter to the function request form function.
In Descriptive Way: Concurrent Program steps
6/19/2012
Author : Amit R Tiwari 64
Step 1. Make Oracle Apps identify the executable
Creating Executable:
Navigation:
Login into Oracle Applications >Go to Application Developer >Responsibility >Concurrent >Executable
6/19/2012
Author : Amit R Tiwari 67
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
FIELDS:
6/19/2012
Author : Amit R Tiwari 68
Navigation: Application Developer –> Concurrent –> Program
Create Concurrent Program:
6/19/2012
Author : Amit R Tiwari 71
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.
FIELDS:
6/19/2012
Author : Amit R Tiwari 72
DEFINE PARAMETERS AND VALUE SETS
Navigation: click on Parameters button in above screen 6/19/2012
Author : Amit R Tiwari 74
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.
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
FIELDS:
6/19/2012
Author : Amit R Tiwari 75
Assign this Concurrent Program to a request group:
Navigation:
Switch to “System Administrator” Responsibility –> Security –> Responsibility –> Request
6/19/2012
Author : Amit R Tiwari 79
Action: Select the First record in the “Requests” and then File –> New
6/19/2012
Author : Amit R Tiwari 80
Action: Enter Your User Readable Name of your concurrent program
6/19/2012
Author : Amit R Tiwari 82
Now our Concurrent Program is ready to use from the responsibilities having” Purchasing Reports” as Request Group.
6/19/2012
Author : Amit R Tiwari 83
The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.
Oracle Concurrent Manager
6/19/2012
Author : Amit R Tiwari 84
The Oracle e-Business suite has three important master Concurrent Managers:
Concurrent Manager: Concurrent Managers start concurrent programs running.
Internal Monitor: Internal Monitors monitor the Internal concurrent manager in a parallel concurrent processing environment. If the Internal Concurrent Manager exits abnormally (for example, because its node or its database instance goes down), an Internal Monitor restarts it on another node.
Transaction Manager: Transaction managers handle synchronous requests from client machines.
The Master Concurrent Managers
6/19/2012
Author : Amit R Tiwari 85
A concurrent request proceeds through three, possibly four, life cycle stages or phases:
Pending Request is waiting to be run Running Request is running Completed Request has finished Inactive Request cannot be run
Within each phase, a request's condition or status may change
Life cycle of a concurrent request
6/19/2012
Author : Amit R Tiwari 86
Tables Involved In Concurrent program:Stores information relating a document to an application entity.
•FND_CONCURRENT_PROCESSES: Stores information about concurrent managers.
•FND_CONCURRENT_PROCESSORS: Stores information about immediate (subroutine) concurrent program
libraries.•FND_CONCURRENT_PROGRAMS: Stores information about concurrent programs. Each row includes a name and description of the concurrent program.•FND_CONCURRENT_PROGRAMS_TL:Stores translated information about concurrent programs in each of the installed languages.•FND_CONCURRENT_QUEUES: Stores information about concurrent managers.•FND_CONCURRENT_QUEUE_SIZE: Stores information about the number of requests a concurrent manager can process at once, according to its work shift.•FND_CONCURRENT_REQUESTS: Stores information about individual concurrent requests.•FND_CONCURRENT_REQUEST_CLASS: Stores information about concurrent request types.•FND_CONC_REQ_OUTPUTS: This table stores output files created by Concurrent Request.
6/19/2012
Author : Amit R Tiwari 92
Name Datatype Length
Mandatory
Comments
APPLICATION_ID NUMBER (15) Yes Application identifierCONCURRENT_QUEUE_ID NUMBER (15) Yes Concurrent manager identifierCONCURRENT_QUEUE_NAME
VARCHAR2 (30) Yes Concurrent manager name
LAST_UPDATE_DATE DATE Yes Standard Who column
LAST_UPDATED_BY NUMBER (15) Yes Standard Who columnCREATION_DATE DATE Yes Standard Who column
CREATED_BY NUMBER (15) Yes Standard Who columnLAST_UPDATE_LOGIN NUMBER (15) Yes Standard Who columnPROCESSOR_APPLICATION_ID
NUMBER (15) Yes Application identifier of the program library
CONCURRENT_PROCESSOR_ID
NUMBER (15) Yes Program library identifier
MAX_PROCESSES NUMBER (4) Yes The maximum number of concurrent requests the manager can run at a timeRUNNING_PROCESSES NUMBER (4) Yes The number of currently active manager processesCACHE_SIZE NUMBER (3) Buffer size of the concurrent manager
MIN_PROCESSES NUMBER (4) Not currently used
TARGET_PROCESSES NUMBER (4) Not currently used
TARGET_NODE VARCHAR2 (30) Target machine
TARGET_QUEUE VARCHAR2 (30) Reserved for future use
SLEEP_SECONDS NUMBER (4) The number of seconds the concurrent manager waits between checking the list of pending requests
CONTROL_CODE VARCHAR2 (1) Concurrent manager control code
DIAGNOSTIC_LEVEL VARCHAR2 (1) For internal use only
MANAGER_TYPE VARCHAR2 (30) Type of concurrent manager
NODE_NAME VARCHAR2 (30) Machine name
NODE_NAME2 VARCHAR2 (30) Secondary machine name
OS_QUEUE VARCHAR2 (30) Platform-specific operating system queue name
OS_QUEUE2 VARCHAR2 (30) Secondary platform-specific operating system queue name
DATA_GROUP_ID NUMBER (15) Data group used for transaction managers only
RESTART_TYPE VARCHAR2 (1) Not currently used
RESTART_INTERVAL NUMBER (15) Not currently used
ATTRIBUTE_CATEGORY VARCHAR2 (30) Attribute category
ATTRIBUTE VARCHAR2 (2000) Descriptive flexfield segment
ATTRIBUTE1 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE2 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE3 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE4 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE5 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE6 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE7 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE8 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE9 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE10 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE11 VARCHAR2 (30) Descriptive flexfield segment
ATTRIBUTE12 VARCHAR2 (30) Descriptive flexfield segment
ENABLED_FLAG VARCHAR2 (1) Yes Enabled flag
RESOURCE_CONSUMER_GROUP
VARCHAR2 (30) Resource consumer group
PMON_STAT NUMBER Count of dead processes found by the ICM during the PMON cycle
LAST_VERIFIED DATE Time of last verify command for this manager
WORK_START DATE Time processing cycle began
WORK_END DATE Time processing cycle ended
PCP_FLAG VARCHAR2 (1) Is PCP enabled? Y or N (PCP - Parallel Concurrent Processing)
INSTANCE_NUMBER NUMBER Oracle Parallel System instance identifier; used for parallel concurrent processing
CONTEXT_BUFFER VARCHAR2 (2000) Service instance cartridge data buffer
SERVICE_PARAMETERS VARCHAR2 (2000) Service instance specific parameter
TIME_PERIOD_ID NUMBER (15) Not in use
TIME_PERIOD_APP_ID NUMBER (15) Not in use
CONCURRENT_TIME_PERIOD_ID
NUMBER (15) Indicates current workshift id
PERIOD_APPLICATION_ID NUMBER (15) Indicates current workshift application id
6/19/2012
Author : Amit R Tiwari 93
FND_CONCURRENT_QUEUE_SIZE
Name Datatype Length Mandatory CommentsQUEUE_APPLICATION_ID NUMBER (15) Yes Application identifier of the concurrent manager
CONCURRENT_QUEUE_ID NUMBER (15) Yes Concurrent manager identifier
PERIOD_APPLICATION_ID NUMBER (15) Yes Application identifier of the concurrent manager work shift
CONCURRENT_TIME_PERIOD_ID
NUMBER (15) Yes Concurrent manager work shift identifier
LAST_UPDATE_DATE DATE Yes Standard Who column
LAST_UPDATED_BY NUMBER (15) Yes Standard Who column
CREATION_DATE DATE Yes Standard Who column
CREATED_BY NUMBER (15) Yes Standard Who column
LAST_UPDATE_LOGIN NUMBER (15) Yes Standard Who column
MIN_PROCESSES NUMBER (4) Yes The maximum number of request the manager can process at one time during the work shift
MAX_PROCESSES NUMBER (4) Not currently used
SLEEP_SECONDS NUMBER (4) The number of seconds the concurrent manager waits between checking the list of pending requests
SERVICE_PARAMETERS VARCHAR2 (2000) Service parameter for this work shift
FAILOVER_MIN NUMBER (5) Minimum processes to be running during failover
FAILOVER_MAX NUMBER (4) Minimum processes to be running during failover
6/19/2012
Author : Amit R Tiwari 96
FND_CONC_REQ_OUTPUTS
Name Datatype Length Mandatory
Comments
CONCURRENT_REQUEST_ID NUMBER Yes Concurrent Request_ID
OUTPUT_ID NUMBER Yes Sequence number generated for this output file
FILE_TYPE VARCHAR2 (30) Yes Represents the output file typeFILE_NAME VARCHAR2 (255) Yes File name with full pathFILE_NODE_NAME VARCHAR2 (255) Yes File Node NameFILE_SIZE NUMBER File Size
ACTION_TYPE NUMBER PP_ACTION type will store 6 for XML Publisher output.
FILE_CREATION_DATE DATE Yes File Creation date
Columns
6/19/2012
Author : Amit R Tiwari 97
1) Registering the Executable from back end
Below is the PL/SQL code to create an executable from back-end.
BEGIN
FND_PROGRAM.executable('XXMZ_EMPLOYEE' -- executable
, 'XXMZ Custom' -- application
, 'XXMZ_EMPLOYEE' -- short_name
, 'Executable for Employee INFORMATION' --description
, 'PL/SQL Stored Procedure' -- execution_method
, 'XXMZ_EMPLOYEE' -- execution_file_name
, '' -- subroutine_name
, '' -- Execution File Path
, 'US' -- language_code
, '');
COMMIT;
END;
Submitting Concurrent Program From PLSQL
6/19/2012
Author : Amit R Tiwari 98
2) Registering the Concurrent program from back end
Below is the program to create a Concurrent program from back-end.
BEGIN
FND_PROGRAM.register('Concurrent program for Employee Information' -- program
, 'XXMZ Custom' -- application
, 'Y' -- enabled
, 'XXMZ_EMPLOYEE' -- short_name
, ‘ Employee Information' -- description
, 'XXMZ_EMPLOYEE' -- executable_short_name
, 'XXMZ Custom' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, '' -- style
, 'N' -- style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' – output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
COMMIT;
END;
Submitting Concurrent Program From PLSQL
6/19/2012
Author : Amit R Tiwari 99
3) Attaching the concurrent program to the request group
Below is the program to Attach Concurrent program to the request group from back-end.
BEGIN
FND_PROGRAM.add_to_group('XXMZ_EMPLOYEE' -- program_short_name
, 'XXMZ Custom' -- application
, 'xxmz Request Group' -- Report Group Name
, 'XXMZ'); -- Report Group Application
COMMIT;
END;
Submitting Concurrent Program From PLSQL
6/19/2012
Author : Amit R Tiwari 100
4) Submitting Concurrent Program from Back-end
We first need to initialize oracle applications session using
fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id) and then run fnd_request.submit_request
DECLARE
l_request_id NUMBER(30);
Begin
FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);
l_request_id:= FND_REQUEST.SUBMIT_REQUEST ('XXMZ' --Application Short name,
'VENDOR_FORM'-- Concurrent Program Short Name );
DBMS_OUTPUT.PUT_LINE(l_request_id);
commit;
End;
Submitting Concurrent Program From PLSQL
6/19/2012
Author : Amit R Tiwari 101
Once the concurrent program is submitted from back-end, status of the concurrent program can be checked using below query.
SELECT * FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= l_request_id;
Submitting Concurrent Program From PLSQL
6/19/2012
Author : Amit R Tiwari 102
We use following code to wait for the request. It will return Boolean value.
FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id IN number default NULL,
interval IN number default 60,
max_wait IN number default 0,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2);
Submitting Concurrent Program From PLSQL
6/19/2012
Author : Amit R Tiwari 104
Question: What is value set?Answer: It is a set of values
Question: Why do we need value sets?Answer: We always want a user to enter junk free text into all the fields. Hence, Oracle Apps uses value set to validate that correct data is being entered in the fields in screen.
Question: Is value set attached to all the fields that require validations?Answer : NO
Question: Then where lies the usage of value sets?Answer: Broadly speaking, value sets are attached to segments in Flexfields.
ValueSets
6/19/2012
Author : Amit R Tiwari 105
Question: Any examples?
Answer: For the namesake, lets add a Parameter to the concurrent program that we defined in “Concurrent Program Training Lesson link”. Lets add a parameter named “cost centre”, the values to this parameter must be restricted to one of the three values, i.e. HR, SEC, IT.
At the time of submission of the concurrent program the user should be able to pick a cost centre from a list. This is where value set gets used.
Lets now define a simple value set as described above.
ValueSets
6/19/2012
Author : Amit R Tiwari 106
Step 1. Go to Application Developer, and select menu /Validation/Set
ValueSets
6/19/2012
Author : Amit R Tiwari 107
Step 2. Now define a value set of type Independent. We will cover the other most widely used Type “Table” latter.
6/19/2012
Author : Amit R Tiwari 108
Step 3. Now, lets add three independent values to the value set for this Cost Centre list. Hence click on menu Values within Validation
6/19/2012
Author : Amit R Tiwari 109
Step 4. Here we add the values for IT, HR, SEC to this independent value set.
CONTROL-S” to save the data
6/19/2012
Author : Amit R Tiwari 110
Step 5. Now let us go back to Concurrent Program that we created in earlier training lesson and Click on Parameters
6/19/2012
Author : Amit R Tiwari 111
Step 6. Now lets create a parameter, and attach the value set that we created to this parameter.
6/19/2012
Author : Amit R Tiwari 112
Step 7.Now to test this, lets go to receivables manager and click on Requests.
Click on Request,
6/19/2012
Author : Amit R Tiwari 114
Step 9 Now, we can see the values defined in the value set here.
6/19/2012
Author : Amit R Tiwari 115
1. Independent 2. Dependent 3. None 4. Special 5. Table 6. Pair 7. Translatable Independent 8. Translatable dependent
The Validation type are 8 types
6/19/2012
Author : Amit R Tiwari 116
The API has seperate procedures for creating different types of value sets e.g.
FND_FLEX_VAL_API.CREATE_VALUESET_TABLE for table value set and
Fnd_flex_val_api.create_valueset_independent for independent value set.
FND_FLEX_VAL_API.VALUESET_EXISTS can be used to check if value set already exists.
API to create ValueSets
6/19/2012
Author : Amit R Tiwari 117
ScenarioYou are an employee in the College of Science. You want to create a Request Set for your monthly department and project statements, and detail reports. This will reduce the amount of time it takes for you to run your department’s reports each month.
Request Set in Oracle Apps
6/19/2012
Author : Amit R Tiwari 118
Objective: we will create a Request Set to run all monthly
statements and detail reports at one time.
Request Set
6/19/2012
Author : Amit R Tiwari 121
In the Request Set screen click on the “Request Set Wizard” button.
6/19/2012
Author : Amit R Tiwari 122
The first step of setting up a Request Set through the Request Set Wizard is to determine if you want your group of reports to run sequentially or parallel.
Sequentially – To run one after another. This is generally used for programs and not reports. One program can be dependant upon another, but your reports will not depend on each other for their values.
In Parallel – Since your reports are not dependant upon one another it is possible to run them all at the same time.
6/19/2012
Author : Amit R Tiwari 123
Select the “In Parallel” option by clicking on the circle to the left, and then the“Next >” button.
6/19/2012
Author : Amit R Tiwari 124
In the next step enter a name for your Request Set in the “Name” field and tab to the “Application” field.
6/19/2012
Author : Amit R Tiwari 125
• In the “Application” field type “General” and hit the tab key. The Applicationname “General Ledger” will fill in the field.• Enter a description of the Request Set in the “Description” field and click the“Next” button.
6/19/2012
Author : Amit R Tiwari 126
We can choose whether you want the reports to print as each one completes or after all of them have completed. Choose “As Each Request in the Set Completes”, and click the “Next” button.
6/19/2012
Author : Amit R Tiwari 127
• In the last step of the Request Set Wizard you will select the reports you want to include in your Request Set.
o Type “RIT” in the first row and hit the tab key. The field will fill in with the “RIT Account Analysis-(180 Char)” report for your operating accounts detail. Repeat this process in the second row for your project accounts detail.
6/19/2012
Author : Amit R Tiwari 129
o For your FSG reports, such as your Department or Project statements, select the “Program – Run Financial Statement Generator” for each report you want to run.
6/19/2012
Author : Amit R Tiwari 130
o When finished selecting reports click on the “Finish” button.
6/19/2012
Author : Amit R Tiwari 131
A Note window will open letting you know that your Request Set has been saved, click on the “OK” button.
6/19/2012
Author : Amit R Tiwari 132
A request security group is the collection of requests, request sets, and concurrent programs that a user, operating under a given responsibility, can select from the Submit Requests window.
Request group
6/19/2012
Author : Amit R Tiwari 133
System Administrators: ◦ Assign a request security group to a responsibility
when defining that responsibility. A responsibility without a request security group cannot run any requests using the Submit Requests window.
◦ Can add any request set to a request security group. Adding a private request set to a request security group allows other users to run that request set using the Submit Requests window.
6/19/2012
Author : Amit R Tiwari 134
Users: ◦ Can create their own private request sets using
the Request Sets window. In a private request set, users can include only the requests you assign to their request security group.
◦ Cannot update another user's private request set using the Request Sets window.
◦ Cannot delete a private request set if it is assigned to a request security group.
6/19/2012
Author : Amit R Tiwari 136
FieldsGroup Use the request group's name to assign the request group to a responsibility on the Responsibilities window. An application name and request group name uniquely identify a request group. Application Select the name of the application you wish to associate with your request group. An application name and a request security group name uniquely identify a request security group. This application name does not prevent you from assigning requests and request sets from other applications to this request group. Code Assign a code to this request group. Some products use the request group code as a parameter that identifies the requests a customized standard submission form can select. See: Customizing the Submit Requests Window using Codes. Specify the requests and request sets in the request group. Type Choose program or set to add one item, or choose application to include all requests in an application
6/19/2012
Author : Amit R Tiwari 137
Request Groups and Request Sets in Concurrent Programs Grouping concurrent programs and other requests together allows us to control access and streamline processing. In Oracle Applications, we group programs and requests into request groups and request sets.
Request GroupsA request group is a collection of reports and other concurrent programs. You use request groups to implement security at the responsibility level. Request groups are normally associated with a responsibility, in which case they are referred to as request security groups. Any user of a responsibility has access to the reports in that responsibility’s request security group. Additionally, you can define a request group to have an access code. You can then define the form so that it allows users to select only those reports or concurrent programs belonging to a request group with an assigned code.
Request SetsA request set is a collection of concurrent programs set up to run in a specified sequence from a single transaction. Request sets can also have run and/or print options, which apply to everymember of the set. Programs in a request set can share parameters; therefore a parameter value needs to be entered only once for multiple programs. Any user can create a request set.
6/19/2012
Author : Amit R Tiwari 138
fnd_set.add_set_to_group .
API for attaching request set to request group
API for Creating Request Group
fnd_program.add_to_group.
6/19/2012
Author : Amit R Tiwari 139
BEGIN-- Add Request Set to request group.BEGINfnd_set.add_set_to_group (request_set => 'XXX_TEST_REQUEST_SET',set_application => 'XXX', --REQUEST SET APPLICATION SHORT NAMErequest_group => 'KNOWORACLE',---REQUEST GROUP NAMEgroup_application => 'XXX'--REQUEST GROUP APPLICATION SHORT NAME);DBMS_OUTPUT.PUT_LINE ('"XXX_TEST_REQUEST_SET" attached to request group Succesfully ');EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.PUT_LINE ('Error in attaching "XXX_TEST_REQUEST_SET" to Request Group ' || SQLERRM);END;
Sample Code: attaching request set to request group
6/19/2012
Author : Amit R Tiwari 140
FND_REQUEST_GROUPS: Stores information about report security groups.
FND_REQUEST_SETS: Stores information about report sets.
Tables Involved
6/19/2012
Author : Amit R Tiwari 143
There are two type of alerts
Event based Alerts
Periodic Alerts
Alerts in Oracle Applications
6/19/2012
Author : Amit R Tiwari 144
These Alerts are fired/triggered based on some change in data in the database. Ex: If you want to notify your manager when you create an item in the inventory you can use Event based alerts. When you create an item in the inventory it will create a new record in mtl_system_items_b, here inserting a record in the table is an event so whenever a new record is inserted it will send the alert. In same alert you can also send the information related to that particular item.
Event based Alerts:
6/19/2012
Author : Amit R Tiwari 145
These Alerts are triggered hourly, daily, weekly, monthly or yearly based on your input. Ex: If you want to know list of all items created on that day at the end of day you can use periodic alerts repeating periodically by single day. This alert is not based on any changes to database. This alert will notify you everyday regardless of data exists or not that means even if no items are created you will get a blank notification.
Periodic Alerts:
6/19/2012
Author : Amit R Tiwari 146
You can send notifications
You can send log files as attachments to notifications
You can call PL/SQL stored procedures.
You can send approval emails and get the results.
Print some content dynamically
What can be done with Alerts?
6/19/2012
Author : Amit R Tiwari 147
Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
If you are going for periodic alert decide the frequency
If you have chosen event based alert then find out on what event (insert, update, delete) you want to fire the alert.
Decide what data need to be included in the alert
Based on the data you want in the alert write a SELECT SQL Statement to pull the data.
Create a distribution list grouping all the people to whom you want to send the alert.
How to create an Alert?
6/19/2012
Author : Amit R Tiwari 148
Go to “Alert Manager” ResponsibilityAlert >> Define
Navigation:
6/19/2012
Author : Amit R Tiwari 149
Go to Alert Manager Responsibility
Alert >> Define
Go to “Tools” Menu on top
Click on “Transfer Alert”
Enter source and destination fields and click Transfer.
Transfer Alert from one instance/database to other:
6/19/2012
Author : Amit R Tiwari 154
ALR_ACTIONS ALR_ACTION_DATA ALR_ACTION_GROUPS ALR_ACTION_GROUP_MEMBERS ALR_ACTION_HISTORY ALR_ACTION_OUTPUTS ALR_ACTION_SETS ALR_ACTION_SET_CHECKS ALR_ACTION_SET_INPUTS ALR_ACTION_SET_MEMBERS ALR_ACTION_SET_OUTPUTS ALR_ACTUAL_RESPONSES ALR_ALERTS ALR_ALERT_CHECKS ALR_ALERT_INPUTS ALR_ALERT_INSTALLATIONS ALR_ALERT_OUTPUTS
ALR_DISTRIBUTION_LISTS ALR_LOOKUPS ALR_MESSAGE_SYSTEMS ALR_ORACLE_MAIL_ACCOUNTS ALR_OUTPUT_HISTORY ALR_PERIODIC_SETS ALR_PERIODIC_SET_MEMBERS ALR_PROFILE_OPTIONS ALR_RESPONSE_ACTIONS ALR_RESPONSE_ACTION_HISTORY ALR_RESPONSE_MESSAGES ALR_RESPONSE_SETS ALR_RESPONSE_VARIABLES ALR_RESPONSE_VARIABLE_VALUES ALR_VALID_RESPONSES
Oracle Alerts Module important tables
6/19/2012
Author : Amit R Tiwari 156
Question : What is a lookup in Oracle AppsAnswer: It is a set of codes and their meanings.
Question: Any examples?Answer: The simplest example is say a lookup type of Gender.This will have definitions as belowCode Meaning------ -------------M MaleF FemaleU Unknown
Lookup in Oracle Apps
6/19/2012
Author : Amit R Tiwari 157
Question: But where is it used, any examples of its usages?Answer: Let us say that there is a table for employees, and this table named PER_PEOPLE_F & has following columns----FIRST_NAMELAST_NAMEDATE_OF_BIRTHGENDERQuestion: Will the gender column in above table hold the value of M or F or U?Answer: Correct, and the screen that displays people details will in reality display the meaning of those respective codes (i.e. Male, Female, Unknown etc) instead of displaying the code of M or F or U
Ex:
6/19/2012
Author : Amit R Tiwari 158
Question: hmmm...so are lookups used to save the bytes space on database machine?
Answer: Noooo. Imagine a situation as belowa. There are 30,000 records in people table of which 2000 records have gender value = U. In the screen, their Gender is being displayed as "Unknown". Now let’s say you want this to be changed to "Undisclosed". To implement this change, all you have to do is to change the meaning of the lookup codes for lookup type GENDER. Hence it will look likeCode Meaning------ -------------M MaleF FemaleU Undisclosed
Here lies the beauty of lookups, you do not need to modify 2000 odd records in this case.
6/19/2012
Author : Amit R Tiwari 159
Question : Any other usage of lookups?Answer : Sure, lets take another example. In HRMS, there is a field named Ethnicity. By default Oracle ERO delivers the below valuesLookup code lookup meaning---------------- ---------------------AS AsianEU European
Now, if your client wants to track Ethnicity at a granular level, they can amend the Oracle delivered lookup definition as below
Lookup code lookup meaning---------------- ---------------------ASI Asian-IndianASP Asian-PakistaniEU European
Hence these values will then be available in the list of values for Ethnicity field.
More examples
6/19/2012
Author : Amit R Tiwari 161
Once in the screen, you can define your lookup type and lookup codes as below.
6/19/2012
Author : Amit R Tiwari 163
Fnd_lookup_typesName Datatype Length Mandatory CommentsAPPLICATION_ID NUMBER (15) Yes Application identifierLOOKUP_TYPE VARCHAR2 (30) Yes QuickCode lookup type
CUSTOMIZATION_LEVEL VARCHAR2 (1) Yes Customization level
CREATED_BY NUMBER (15) Yes Standard Who column
CREATION_DATE DATE Yes Standard Who column
LAST_UPDATED_BY NUMBER (15) Yes Standard Who column
LAST_UPDATE_DATE DATE Yes Standard Who column
LAST_UPDATE_LOGIN NUMBER (15) Standard Who column
SECURITY_GROUP_ID NUMBER (15) Yes Security group identifier
VIEW_APPLICATION_ID NUMBER (15) Yes Application identifier of view (_LOOKUPS) through which the lookup type will be exposed
ASSIGN_LEAF_ONLY VARCHAR2 (1) Assign Leaf Only Flag
Columns
Fnd_lookup_valuespto6/19/2012
Author : Amit R Tiwari 164
Name Datatype Length Mandatory CommentsLOOKUP_TYPE VARCHAR2 (30) Yes QuickCode lookup typeLANGUAGE VARCHAR2 (30) Yes LanguageLOOKUP_CODE VARCHAR2 (30) Yes QuickCode codeMEANING VARCHAR2 (80) Yes QuickCode meaningDESCRIPTION VARCHAR2 (240) Description
ENABLED_FLAG VARCHAR2 (1) Yes Enabled flagSTART_DATE_ACTIVE DATE The date when the QuickCode becomes active
END_DATE_ACTIVE DATE The date when the QuickCode becomes inactive
CREATED_BY NUMBER (15) Yes Standard Who columnCREATION_DATE DATE Yes Standard Who column
LAST_UPDATED_BY NUMBER (15) Yes Standard Who columnLAST_UPDATE_LOGIN NUMBER (15) Standard Who column
LAST_UPDATE_DATE DATE Yes Standard Who column
SOURCE_LANG VARCHAR2 (4) Yes The Language the text will mirror. If text is not yet translated into LANGUAGE then any changes to the text in the source language row will be reflected here as well.
SECURITY_GROUP_ID NUMBER (15) Yes Security group identifierVIEW_APPLICATION_ID NUMBER (15) Yes Identifies which application's view will include the lookup valuesTERRITORY_CODE VARCHAR2 (2) Territory code of territory using the language
ATTRIBUTE_CATEGORY VARCHAR2 (30) Attribute category
ATTRIBUTE1 VARCHAR2 (150) Attribute
ATTRIBUTE2 VARCHAR2 (150) Attribute
ATTRIBUTE3 VARCHAR2 (150) Attribute
ATTRIBUTE4 VARCHAR2 (150) Attribute
ATTRIBUTE5 VARCHAR2 (150) Attribute
ATTRIBUTE6 VARCHAR2 (150) Attribute
ATTRIBUTE7 VARCHAR2 (150) Attribute
ATTRIBUTE8 VARCHAR2 (150) Attribute
ATTRIBUTE9 VARCHAR2 (150) Attribute
ATTRIBUTE10 VARCHAR2 (150) Attribute
ATTRIBUTE11 VARCHAR2 (150) Attribute
ATTRIBUTE12 VARCHAR2 (150) Attribute
ATTRIBUTE13 VARCHAR2 (150) Attribute
ATTRIBUTE14 VARCHAR2 (150) Attribute
ATTRIBUTE15 VARCHAR2 (150) Attribute
TAG VARCHAR2 (150) Optional additional category for lookup values
LEAF_NODE VARCHAR2 (1) Leaf Node Flag
6/19/2012
Author : Amit R Tiwari 166
A flexfield is a field made up of segments. Each segment has a name you or your endusers assign, and a set of valid values. There are two types of flexfields:◦ Key flexfields and ◦ Descriptive flexfields.
Overview of Flexfields
6/19/2012
Author : Amit R Tiwari 167
A key flexfield represents an intelligent key that uniquely identifies an application entity. Each key flexfield segment has a name you assign, and a set of valid values you specify. Each value has a meaning you also specify. Oracle General Ledger's Accounting Flexfield is an example of a key flexfield used to uniquely identify a general ledger account.
Key Flexfields
6/19/2012
Author : Amit R Tiwari 168
Oracle General Ledger uses a key flexfield called the Accounting Flexfield to uniquely identify a general ledger account. At Oracle, we have customized this Accounting Flexfield to include six segments: company code, cost center, account, product, product line, and sub- account. We have also defined valid values for each segment, as well as cross-validation rules to describe valid segment combinations. However, other companies might structure their general ledger account fields differently. By including the Accounting Flexfield key flexfield, Oracle General Ledger can accommodate the needs of different companies. One company can customize the Accounting Flexfield to include six segments, while another company includes twelve segments, all without programming.
example,
6/19/2012
Author : Amit R Tiwari 169
Descriptive flexfields let you satisfy different groups of users without having to reprogram your application, by letting you provide customizable "expansion space" on your forms. For example, suppose you have a retail application that keeps track of customers. Your Customers form would normally include fields such as Name, Address, State, Customer Number, and so on. However, your form might not include extra fields to keep track of customer clothing size and color preferences, or regular salesperson, since these are attributes of the customer entity that depend on how your users use your application.
Descriptive Flexfields
6/19/2012
Author : Amit R Tiwari 170
If your retail application is used for a toolcompany, a field for clothing size would be undesirable. Even if you initially provide all the fields your users need, your users might later identify even more customer attributes that they want to keep track of. You add a descriptive flexfield to your form so that your users have the desired expansion space. Your users can also take advantage of the fact that descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.
For example,
6/19/2012
Author : Amit R Tiwari 171
A descriptive flexfield describes an application entity, providing form and database expansion space that you can customize. Each descriptive segment has a name you assign. You can specify valid segment values or set up criteria to validate the entry of any value.
Oracle General Ledger includes a descriptive flexfield in its journal entry form to allow end users to add information of their own choosing. For example, end users might want to capture additional information about each journal entry, such as source document number or the name of the person who prepared the entry
6/19/2012
Author : Amit R Tiwari 172
Flexfields provide you with the features you need to satisfy the following business needs:
Customize your applications to conform to your current business practice for accounting codes, product codes, and other codes.
Customize your applications to capture data that would not otherwise be tracked by your application.
Have ”intelligent fields” that are fields comprised of one or more segments, where each segment has both a value and a meaning.
Rely upon your application to validate the values and the combination of values that you enter in intelligent fields.
Have the structure of an intelligent field change depending on data in your form or application data.
Customize data fields to your meet your business needs without programming.
Query intelligent fields for very specific information.
Benefits of Flexfields
6/19/2012
Author : Amit R Tiwari 173
The Accounting Flexfield in your Oracle Purchasing application is an example of a key flexfield that identifies a unique chart of accounts. One organization may choose to customize the Accounting Flexfield to have three segments called Company, Department, and Account, while another organization may choose to customize the flexfield to have five segments called Company, Cost Center, Account, Sub–Account, and Product.
KFF:
6/19/2012
Author : Amit R Tiwari 176
Descriptive flexfields like the key flexfields provides further scope of customization in Oracle Applications.
Descriptive flexfields provide customizable ”expansion space” on your forms. Though the fields on an Oracle Applications form are more than enough to capture all the possible information from the user perspective, but still the users can feel the need of capturing additional information. A descriptive flexfield gives you room to expand your forms for capturing such additional information.
A descriptive flexfield appears on a form as a single–character, unnamed field enclosed in brackets ([ ])
Descriptive Flexfields
6/19/2012
Author : Amit R Tiwari 178
Oracle Applications has provided space for descriptive flexfields on almost all the forms. Just like in a key flexfield, a pop–up window appears when you move your cursor into a customized descriptive flexfield. And like a key flexfield, the pop–up window has as many fields as your organization needs. Each field or segment in a descriptive flexfield has a prompt (figure 4), just like ordinary fields, and can have a set of valid values. The pop-up as shown in the figure 4 comes up when you click on the [ ] icon on the form in the figure3 above.
6/19/2012
Author : Amit R Tiwari 180
FND_FLEX_VALUES_TL FND_ID_FLEXS FND_FLEX_VALUES FND_FLEX_VALUE_SETS FND_ID_FLEX_STRUCTURES
The tables involved are
6/19/2012
Author : Amit R Tiwari 181
Crating Descriptive Flexfield: 1) Most important thing to know that you are not going to create new field in database. Basically You are going to configure DFFs already provided by Oracle, The descriptive flexfield columns are usually named ATTRIBUTEn where n is a number.For example, if you want to configure a descriptive flexfield on an Purchase Order (Header) form and want to associate it with Attribute1First of all we need to know the Title of DFF which need to configure. Follow the following steps.
6/19/2012
Author : Amit R Tiwari 182
Navigation:Steps
1) Find out the Table Name used for PO Header. Navigate to Help –> Record History and copy the table name as shown in image bellow.
6/19/2012
Author : Amit R Tiwari 183
Navigation:Steps2) Now in my case PO_HEADERS_V is not a table, its a view. We need to find out underline Table Name of this view. You can use following query to find out the table name. Use Toad, Pl-SQL Developer or SPL Plus.select name , type , referenced_name , referenced_typefrom user_dependencieswhere name = ‘PO_HEADERS_V’and ( type = ‘VIEW’ or referenced_type = ‘TABLE’)
6/19/2012
Author : Amit R Tiwari 184
Navigation:Steps
3) Now in this particular case “PO_HEADERS_V” again consists on views. You can see the “PO_HEADERS” View in above query result. Now query again but change ”PO_HEADERS_V” with ”PO_HEADERS” in where clause like following.select name , type , referenced_name , referenced_typefrom user_dependencieswhere name = ‘PO_HEADERS’and (type = ‘VIEW’ or referenced_type = ‘TABLE’)Now in result you can see Table Name “ PO_HEADERS_ALL”
6/19/2012
Author : Amit R Tiwari 185
Navigation:Steps4) Once having noted down the table, Try to find the Title of the DFF for that Table. We go to Flexfield/Register in Application Developer Responsibility as shown in figure bellow.
Now note down the Application and Title of DFF.
6/19/2012
Author : Amit R Tiwari 186
Navigation:Steps5) Now navigate to Segments and Query entering Application Name = Purchasing and Title = “PO Headers” as shown bellow.
Before you being configure the DFF make sure to uncheck “Freeze Flexfield Definition” Check Box.
6/19/2012
Author : Amit R Tiwari 187
Navigation:Steps6) Now click on the Segments, here you can define segments or cells.
7) You can attach LOV/ List by Pressing Value Set Button.8) Once you finalize the changes, you will be prompted to Freeze the DFF definition. Click on OK
6/19/2012
Author : Amit R Tiwari 188
Navigation: Steps9) Now navigate to Purchase Order Screen and click on [ ] in header. You can see DFF as configured.
6/19/2012
Author : Amit R Tiwari 190
FNDLOAD to transfer AOL Objects from one instance to other
In simple words FNDLOAD is used to transfer entity data from one instance/database to other. For example if you want to move a concurrent program/menu/value sets developed in DEVELOPMENT instance to PRODUCTION instance you can use this command.
6/19/2012
FNDLOAD
Author : Amit R Tiwari 191
These are the extensive list which can be done through FNDLOAD
Concurrent Programs, Executables Request Groups, Request Sets Profile Options Key and Descriptive Flexfields Menus and Responsibilities Forms and Form Functions Attachments Messages Value Sets and Values Lookup Types User Responsibilities Printer Definitions FND Dictionary Help Configuration Document Sequences Concurrent Manager Schedules
6/19/2012
Author : Amit R Tiwari 192
Define your concurrent program and save it in first instance Connect to your UNIX box on first instance and run the
following command to download the .ldt fileFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”Concurrent program application short name” CONCURRENT_PROGRAM_NAME=”concurrent program short name”
Move the downloaded .ldf file to new instance(Use FTP) Connect to your UNIX box on second instance and run the
following command to upload the .ldt fileFNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt
6/19/2012
Steps to Move a Concurrent program from one instance(Database) to other
Author : Amit R Tiwari 193
LookupsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”FND”LOOKUP_TYPE=”lookup name”
Descriptive Flexfield with all of specific ContextsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALL APPLICATION_SHORT_NAME=”FND” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”
Key Flexfield StructuresFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”
6/19/2012
These following are the other entity data types that we can move with FNDLOAD
Author : Amit R Tiwari 194
Concurrent ProgramsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”FND” CONCURRENT_PROGRAM_NAME=”concurrent name”
Value SetsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”
Value Sets with valuesFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”
Profile OptionsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”FND”
Request GroupsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”FND”
6/19/2012
Author : Amit R Tiwari 195
Request SetsFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”FND” REQUEST_SET_NAME=”request set”
ResponsibilitiesFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility”
MenusFNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”
Forms PersonalizationFNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME
Note: UPLOAD command is same for all except replacing the .lct
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/corresponding.lct upload_file.ldt
6/19/2012