Creation of New User:
1)Open the internet Explorer connect to Oracle Applications
2)Enter the User Name :OPERATIONS
Password :WELCOME
3)Select the Responsibility called 'System Administrator'
4)Open the User form.
Security => User =>Define
5)Enter User Name and Password attach the Responsibilities whatever we required
for ex System Administrator
Application Developer
6)Exit from the Appication
File => Exit Oracle Applications
7)Connect to Oracle apps enter new user name password system will shows the message
like 'Password Has Expired'
8)Enter the New Password Press Ok Button
Short Cuts:
===========
1)To Query All the Records Press CTRL+F11
2)To Query Specific Records
i)Open the Form
ii)Press F11 (Form will comes into Query mode)
iii)Enter Search Criteria in any field
iV)Press CTRL+F11
3)To Close Form = F4
5)To Save the Records CTRL+S
Effective Date From and To:
===========================
In most of the Oracle Application forms we will find two field like
Effective Date From
Effective Date To
In some of the forms once we create records and save. We can not delete from database
that time we can go for Disable/Enable the record by using these two fields
Finding Table NameS/Column Names:
=================================
1)Help => Record History which will shows the Table Name
2)Help Menu=>Diagnastics=>Examine=>Enter the Password(APPS)=>We can find the Column Name
WHO Columns:
=============
WHO Column Will be used to find out the History of the record
we can find from front End Also
Help=>Record History
CREATED_BY - Which User has created the Record(Userid)
CREATION_DATE - at what time user has created (SYSDATE)
LAST_UPATED_BY -Which User has updated recentley(UserID)
LAST_UPDATE_DATE -at what time user has Updated (SYSDATE)
LAST_LOGON_DATE - At what time user last Login Time
Find the Login UserName:
==========================
Help Menu=>About Oracle Applications
Report Registration Steps:
-------------------
1)Develop the report (.rdf)as per client Requirement by using reports 6i Builder
2)Move the report from Local mechine into Server
CUS_TOP\11.5.0\Reports\US\ .rdf
PO_TOP\11.5.0\Reports\US\ .rdf
3)Select System Administrator and create EXECUTABLE
1)Executable Name
2)ApplicationName
3)Execution Method
4)Report(.rdf) file Name
4)Create Concurrent Program and attach 1)EXECUTABLE
2)PARAMETERS
3)INCOMPATIBILITIES
5)Create Request Group and attach Concurrent Program
6)Create Responsibility 1)Request Group
2)Data Group
3)Menu
7)Create user, attach Responsibility to the user.
8)User will select the Responsibility and goto SRS(Standard Request Submission) Window
submit the Request.
=======================
Executable : We can enter any User Defined Name in this field
ShortName : This is Primary key we have to enter Unique value
ApplicationName : Enter The Applications Name where the (.rdf) file is located
Description :It is Optional we can enter any valid Information
Execution Method :we have total 11 types of Execution methods select
"Oracle Reports" for Report Development.
Execution File Name : Enter the (.rdf) File Name without extension Name.
If we know the Application Short Name we can find the Application name
by using following Navigation
Application =>Register=>Press F11=> Enter short Name => Press CTRL+F11
Concurrent Program Window:
=============================
Program : We can enter any User defined Program Name which is meaningful
Enabled Check Box : Once the Concurrent Program is created and saved We can not delete
Instead of delete we can disable by unchecking this check box.
ShortName : This is Primary key we have to enter Unique value
Application Name : We can enter Any Valid Application Name
Description : It is Optional we can enter any valid Information
EXECUTABLE : Enter the "Executable Short Name" whatever we have created
Method : System will automatically retrives the Execution MEthod.
Format : we can select whatever the format we would like to print
Ex: HTML (.html)Internet Explorer
pdf (.pdf) Acrobat Reader
Postscript (.ps) GhostView
text (.txt) Text Pad
PCL HP Printer Language
XML (.xml) XML Publisher
Save check Box : System will save the Output and Log file details in the server
COLUMNS : No of Columns to print per page in the Output file
ROWS : No of Rows to print per page in the Output file
SELECT LOGFILE_NAME,
OUTFILE_NAME
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID =1424277
Request Group :
================
Request group is nothing but collection of Concurrent Programs and Reports we can group
All concurrent Programs.
Group :Enter Any User Defined Name (This is Case sensitive we have to maintain Capital and Small Letters)
Application Name :Enter Any Valid Application Name
Attach Concurrent Program by select type as Program.
Responsibility Window:
======================
Responsibility is nothing but Level of Authority which Combines Forms,Report and Application
User ID's.
Reponsibility Name : we can enter any meaningful Name
ApplicatioName :Enter any Valid Application Name
ResponsibilityKey :This is Primary enter the Unique Value
Data Group:
===========
data Group is nothing but Collection of Oracle Application UserID's.
it will be for Security purpose.(STANDARD will be used)
Application NAme :Enter any Valid Application Name
Request Group:
===============
Requset group is nothing but colletion of Concurrent Program
add the request group whatever we have created.
it will automatically retrieves the Application NAme.
MENU:
----
Menu : Menu is nothing but collection of Forms and Sub Menus
select any one of Existing Menu.
Note : Once we create the Responsibility we can not delete instead of that we can disable
by using Efective start Date and End date.
Reports With Parameters:
========================
1)If WE have parameters in the report we are suppose to define those parameters
while defining the Concurrent Program
2)Select Parameters button enter the following Fields
Seqno:Any +ve number no duplicates are allowed
ParameterName:Any User defined parameter name
Value set:10/NUMBER or 100 Charachters or FND_DATE
Token:Enter the Bind Variable name
Token : It is one of the field in parameter form to map the Concurrent Program
parameter with Report bind Variable.
Value Set: Is nothing but list of values with validations which will be used to
restrict the user without entering the invalid data.
Required CheckBox: When we want to make the parameter as mandatory or Optional we will
use this check box. If we enable it will be mandatory
disable it will be optional.
Display CheckBox: When we want to hide the parameter we will use this check box
user can not find the parameter at SRS window.
we can pass default values internally by using Default types.
Enabled CheckBox: We can delete the parameter.
instead of deletion we can also disable the parameter. Whenever we required again
we can enable
Range: This option will be used to set the values as Low and as well as High.
From Date
To Date
Title
UserID UserName Cdate RespName Applicationname Datagroupname StartDate Enddate
------ ------- ----- -------- --------------- ------------- --------- -------
1545 25USER 10-JAN-07 25ResponsibiltiyOracle Purchasing Standard 10-JAN-07
Systemadminist System admin STANDARDA 15-JAN-07
FND_USER
-USer_id
FND_USER_RESP_GROUPS
FND_RESPONSIBILITY_VL
FND_APPLICATION_VL
FND_DATA_GROUPS
From Date
To Date
Title
RespName Applicationname Respkey Menu Requestgroup Datagroup End date
======== =============== ======= ==== ============ ========= ========
FND_RESPONSIBILITY_VL
FND_APPLICATION_VL
FND_DATA_GROUPS
FND_MENUS_vl
FND_REQUEST_GROUPS
Default Types :
================
When we have the Parameters to the Concurrnet Program we would like to pass some
default values we will use the Default types we have 6 types of Default types.
Constant
Segment
Profile
Current Date
Current Time
SQL Statement
Note: While passing the default values normally we will hide the field by using Displayed
check box .So that User can not change the Value.
Attach the Concurrent Program to other Responsibilities:
======================================================
1)Open the Responsibility Form Query Resp name , Copy the Request Group
2)Goto Request group Form query the Request group name . Attach the Concurrent Program
name.
3)Goto SRS Window and submit the Program.
Default type
Current Date : Will be used to pass the Sysdate as default value
Current Time : Will be used to pass the Systime as default value
Constant : Will be used to pass the constant number or date or
string as default value
Segment : will be used to get the Previous parameter value
default to the next parameter then we will use this.
Default type : segment Default Value :ParameterName
SQL Statement: We can pass the SELECT statement result as default
value
Default type : SQl Statement Default Value : SELECT MIN(USERID) FROM
Profile : This will be used to pass the user profile value as default
like userid ,username,respid,respname and so on.
Required ChekBox : This will be used to make the parameter as mandatory
or optional
Enabled CheckBox: will be used to enable or disable the parameter
Display CheckBox: will be used to hide or display the parameter.
Value Set
=============================
Value Set: Value set is nothing but list of values with validations which will be
used to to restrict the user without entering the invalid data in the Parameters
we will use value sets in two locations.
1)Concurrent Progam parameters
2)Flexfields
NONE:
---------------------------------------------------
We are not providing any LOV, we can apply some format conditions as per that
conditions user should enter the data
Notes: 1)Once we create the Value set we can not Delete if we would like to delete
we have to release the value set from the all the concurrent program
parameters then only we can delete.
2)Value set name is case sensitive
3)Once we create Value set we can use for multiple Program parameters.
Navigation:
--------------
System administrator => Application=>Validation=>set=>
Enter value set name
format type
max size
Select validation type = "None" to create None type of Value sets.
Independent:
----------------
When we would like to provide list of values to the user then we will go for selection
of Independent value set.where we will provide LOV.
User must select the Value from the list otherwise values are not accepted.
Open the Value set form create value set by selecting the validation type=Independnent
Goto Values screen enter the value set name , Select Find Buttion
enter the values whatever we would like to display as LOV.
attach the value set to the Parameter.
Note:1)Once we enter the values we can not delete instead of that we can disable by
selecting the Enabled check box or Effective Dates.
Dependent value Set:
====================
This is another LOV which will be used to displays the
list of values which are depending on the previous parameter value.
Before going to create Dependent first we have to create Independent
then we have to create Dependent
First parameter will be Independent
Second parameter will be Dependent.
Note:Without Independent we can not create Dependent Value set.
Country: IND
US
UK
City Banglore Chennai Delhi Mumbai Pune
Chikago California Anderson
London Hungrant
1)We have to create Independent value set and enter the values.
2)Create Dependent value set attach independent and then enter values.
Job Manager
Developer
Programmer
Position Delivery Manager Project manager Financce manager
Software Developer Test Developer
Trainee Fresher
==========
1)Open the Value set form create Value set by selecting the validation type =Independent
2)Open the Values screen enter the VAlues .
3)Open the value set form enter Dependent value set by select validation type=Dependent
Select the Button called Edit Information button enter the Independent value set
4)open the values form enter the Dependent value set=>Find
enter the values based on the Independent values.
Table Value set :
=================
Table value set will be used to displays the list of values from the
oracle apps base tables.
we have to give the table name and column name which will automatically
displays the values.
Note: If values are not stored in the database table then we have to
go for Independent value set.
If values are there in the table then we will create table value
set.
1.Open the value set form Select validation type as table select the
button called Edit Information enter table name and column name
in the value field
2.Use where/Order By clause to implement Where/Order By clause.
3.Use Additional Columns field to displays extra columns for reference
purpose.
4.Use the ID column to pass the ineternally other columns data
for ex displaying username to the user and pass userID internally.
5.If multiple tables are required then enter the table names in the
table name field with alias name and enter the Join Condition in the
Where clause field.
6.If we know the table name we can find the Table application name from
Application Developer responsibility
Application Developer => Application => Database => table
Query the records based on the table Name.
Note: If we are displaying additional Columns we are suppose to give the Alias Name
Translated Independent and Translated Dependent:
================================================
Both value sets will work like Independent and Dependent value sets
will be used to displays the transalation values which will be enabled
if there is multilanguage implementation.
Special and Pair:
=================
Both Value sets will be used to displays the Flexfield data as LOV to
the User.
Procedure Registration Steps:
1)Develop the procedure and compile at SQL prompt
2)Connect application select system administrator Responsbility create
Executable by selecting the Execution Method as 'PL/SQL Stored Procedure'
3)Create Concurrent Program and attach Executable to the Program and add the
Parameters and incompatible programs.
4)Create Request group attach concurrent Progrtam
5)Attach Request group to the reponsibility
6)Responsiboility will be added to the user.
User will submit the request from SRS Window.
Syntax:
Create Or Replace Procedure (Errbuf OUT varchar2,
Retcode OUT varchar2,
P1 IN NUMBER,
P2 IN VARCHAR2,
P3 IN DATE) AS
Local variable,Cursor,Collections Declare;
Begin
If statement
For Loop
Procedure Calling
DBMS_OUTPUT.Put_Line - This will not be used instead of this Fnd_File API will be used
FND_FILE.PUT_LINE(Fnd_File.Log ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output,'Message'||Variable Name);
Exception
When Other then
-Exception Statements;
End ;
PL/SQL Procedure with Parameter:
================================
If we have any user defined Parameters then we have to register these parameters at the
time of Creating the COncurrent Program by selecting the Parameter button
enter the Seqno
Parameter Name
Value Set
Note : Token Field will be disabled.
Here First Parameter value will be passed to the first variable
Second Parameter will be passed to the second variable and so on........
When we are registering the report as C.P then only we required TOKEN field.
Because report builder bind variables may or may not be in the sequence that's why
we have to map with Token field.
Where as in Procedure variables position is fixed then TOKEN field will be disabled.
SQL * Loader :
==================
It is one of the Oracle Tool will be used to transfer the data from Flat files into
Oracle Base tables.
When we are developing the Interfaces or Conversion program in Oracle Applications
then we will be using the SQL Loader Programs.
while working with SQL * Loader we will come across 5 Files.
1)Flat File OR data File:
-----------------------------
Data file contains the data in the specific format which we will receive from the
client. this file will be generated by the legacy system(Other Systems) program.
It may be either .txt format
.dat
.csv(Comma Separated View) Excell sheet.
Most of the times we will receive in the (.csv) format
2)Control File :
-----------------
Is nothing but a SQL*Loader Program will be used to load the data from file into table.
we will develop the control File
Execute the Control File.
While developing the Control file we will specify follwoing things.
1)Data File path
2)Database Table Name
3)Column Mapping.
Control file Extension is (.ctl)
3)Bad File:
-----------
when we execute the COntrol file if any records are rejected by SQL *Loader then we
will find those rejecteds record in the Bad file.
SQl loader recjects the records if there are any datatype mismatch
Internal Errors
Data file Format problem
Bad File Extension is (.bad)
4)Discard File:
---------------
when we execute the COntrol file if any records are rejected by Control File then we
will find those rejecteds record in the Discard file.
Control file Recjects the record when the record is not satisfying the record which
are not satisfied the condition specified in the Control file.
Discard File Extension is (.dis)
5)Log File :
-------------
It contains the Log information like Program start time,End Time
No of succesfully records upload
Rejected Records into the Bad file
Rejected records into the Discard file
Any errors are there in the Control file those error message details we will find.
Log file Extension is (.log)
Control File Syntax:
----------------------
LOAD DATA
INFILE 'Data File Path'
Insert into table
FIELDS TERMINATED by ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(Column1 ,
column2 ,
column3 ,......)
Execution of Control File :
-----------------------------
Goto Command Prompt where the SQl loader is Installed.
D:Oracle\Proddb\8.1.7\bin\ SQLLDR Username/Password@PROD
Control = Control file Path.
SQL * Loader execution :
1)Connect to Scott schema create temp table
2)Prepare the data file having some sample records save in the local mechine.
3)Develop the control file like follows.
load data
infile 'C:\22\LOAD\EMP.txt'
INSERT INTO TABLE EMP_DETAILS22
fields terminated by ','
(empno ,
ename ,
sal ,
job ,
comm ,
tax ,
deptno ,
jdate)
Note: Save the file extension as ".ctl".
4)Goto Command Propmt through Start Menu = Run = > CMD
5)Connect to following path and execute the control file
D:\Oracle\Proddb\8.1.7\Bin\SQLLDR Scott/Tiger@PROD
Control = C:\22\LOAD\emp_details.ctl
6)test the data in the Table.
PL/SQL,SQL,Reports Triggers
Declare
l_request_id number(10);
begin
l_request_id:= FND_REQUEST.SUBMIT_REQUEST ('PO',
'USERSLIST',
'Test Program',
'',
'',
1500,
1600,
'EMAIL_ADDRESS');
Commit;
if l_request_id != 0 then
Fnd_File.Put_line(Fnd_file.log,'Program succesfullt submitteed');
else
Fnd_File.Put_line(Fnd_file.log,'Program Not succesfull submitteed There are some errors');
End If;
Exception
when others then
Fnd_File.Put_line(Fnd_file.log,'Exception raised at the time of submission');
End;
Form Triggers
==============
Declare
l_request_id number(10);
begin
l_request_id:= FND_REQUEST.SUBMIT_REQUEST ('PO',
'USERSLIST',
'Test Program',
'',
'',
1500,
1600,
'EMAIL_ADDRESS',
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','',''
'','','','','','','','','','');
Commit;
if l_request_id != 0 then
Fnd_File.Put_line(Fnd_file.log,'Program succesfullt submitteed');
else
Fnd_File.Put_line(Fnd_file.log,'Program Not succesfull submitteed There are some errors');
End If;
Exception
when others then
Fnd_File.Put_line(Fnd_file.log,'Exception raised at the time of submission');
End;
Backend Program Registration:
==============================
fnd_program.executable
fnd_program.register
fnd_program.parameter
fnd_program.request_group
fnd_program.add_to_group - Add the C.P to the Request Group
fnd_program.delete_executable
fnd_program.incompatibility -Add Incompatibility Porogram
Create Executable from Backend:
==============================
Begin
fnd_program.executable('ERPREPORT',
'PO',
'AAAAA',
'This is Optional',
'Oracle Reports',
'USER_REPORT',
'',
'',
'US',
'');
Commit;
End;
PO SET UP ITEM_SUPLIER_BUYER
Item Creation:
====================
1)Attach the Responsibility called "Inventory Vision Operations (USA)"
2)Open the Items form
Items=>Master Item
3)Select the Organization name - Vision Operations
4)Enter the Item Name , Item Description
goto Inventory tab check the checkbox called Inventory
goto purchasing tab check the check box called Purchasing
5)Save
6)Goto Tools Menu => Organization Assignment option to assign for the multiple
organizations.
Supplier Creation:
========================
1)Attach the Responsibility called "Purchasing Vision Operations (USA)"
2)Goto the Supplier Form
Supply Base=>Suppliers
3)Enter the Supplier Name . Save supplier number will be created automatically.
4)Select the Sites button enter the supplier site address and other details
5)Goto the Contacts tab enter the Contact details
Name
Phno
Postion and so on.....
Buyer Creation:
==================
1)attach the Responsibility called "US HRMS Manager"
2)Create Employee
People => Enter and Maintain=>Select New button
3)Enter Emp name
select action option select "create Employement" select the optiona s "Buyer"
4)enter Data of Birth
save => Ok = > Empoyee number will be generated.
5)Goto System Administrator open the User form create or query user
select the Person field attach the emp name (Which we have created)
6)Save.
7)Goto Purchasing Responsibility
open the Buyers form
enter the employee name and save the transactions.
Setup => Personnal =>Buyers
Ex:
Suppliers Information Report
From Date FND_DATE
To Date FND_DATE
From Supplierno TABLE
To SupplierNo TABLE
Title 100 CHARACTERS
Sno Sname Cdate SiteCode Cdate ConctactPerson Phno
=== ==== ==== ======= ===== ============= ====
No of Sites : 2
Grand Totals : Total No of Suppliers
Total no of Sites
Toatl No of Contacts
Validation:
All parameters are optional if user not enter parameter then we have to retrieve all the
records.