Oracle Apps Technical

Oracle Apps Technical Blog

eBIZ Technics. Powered by Blogger.

Featured Posts

Sunday, August 16, 2015

Oracle APPS posts

Ø  Oracle Inventory Questions and Answers                         OracleInventory Q&A
Ø  Oracle Receivables Questions and Answers  Part1         OracleReceivables Q&A Part1
Ø  Oracle Receivables Questions and Answers  Part2         OracleReceivables Q&A Part2
Ø  Oracle Constraints                                                            OracleConstraints
Ø  Oracle APPS techno functional Q and A                          Oracle  APPS tech- Fun Q&A
Ø  Oracle Forms Creation (From Procedure)                        OracleForms Creation
Ø  Oracle Forms issues and Solution                                    FormsIssues and Solutions
Removing of Duplicate ROWS:                             
Oracle Order Import  Part 2                                            :
Orace Order Import Part 1                                     
Oracle Order Holds Release                                   

Thursday, May 28, 2015

Oracle APPS:Procedure Registration

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.
Create Or Replace Procedure <ProcedureName> (Errbuf OUT varchar2,
    Retcode OUT varchar2,
    P1    IN NUMBER,
    P2    IN VARCHAR2,
    P3    IN DATE) AS
Local variable,Cursor,Collections Declare;
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);
When Other then
-Exception Statements;
End <Procedure Name>;
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.

Oracle APPS Reports

Oracle Apps Standards in Reports Registration:
1)Define the Mandatroy parameter called P_CONC_REQUEST_ID
2)Call the Following User exit from the Before report Triggers
3)Call another User Exit from After Report Trigger
User Exit:
It is one of the Oracle  Reports 6i Built In program which will be used to stop the report execution and pass the control into some other 3GL and retrieves the data then complete the remaining report execution Process.

We have 5 User Exits in Oracle Applications:

FND SRWINIT : This User Exit we will initialize user Profile values we will call this
    user exit from Before Report Trigger.
FND SRWEXIT: This User Exit will frees the Memory which is Occupied by the User Profile
             values in the server. we will call this from After Report Trigger.
We have Five types of Report Triggers :
Before Parameter Form
After Parameter Form
Before Report Trigger
Between Pages
After Report Trigger
We are using Before Report trigger before data is retrieving  from data base system
will capture the user profiles data and as per that data will be retrieved from
Once Output is generated we do not require the Information in the system for that
we are using after Report Trigger call the another User Exit.
P_CONC_REQUEST_ID : This is One of the mandatory parameter we have to define for every
        concurrent Program.when we submit C.P from SRS window which will generate the
        REQUEST_ID that request ID will be passed into this variable.
Reports parameter (value sets, default types,token)
Request group
user exits.

Purchasing   = functionality  500
      Imp tables Join


Oracle APPS :Value sets

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
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.

System administrator => Application=>Validation=>set=>
Enter value set name
   format type
   max size
Select validation type = "None" to create None type of Value sets.


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
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

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

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
4.Use the ID column to pass the internally 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.

Oracle APPS: what is Mutli ORG?

Multiorg :
Use a single installation of any Oracle Applications product to
support any number of organizations, even if those organizations
use different sets of books.

BusinessGroup (HRMS=>Workstructure=>Organization=Description)
Set of Books  (GL=>Setup=>Financials=>Books=>Define)
Legal Entity
Operating Unit
Inventory Organization
Sub Inventory     (Inventory=.Setup=>organizations=>SubInventories)
Stock Locations   (Inventory=.Setup=>organizations=>Stock locatiors)

Major Features
1)Multiple Organizations in a Single Installation

2)Secure Access
    You can assign users to particular organizations. This ensures accurate
    transactions in the correct operating unit.

3)Multiple Organizations Reporting

You can set up your Oracle Applications implementation to allow
reporting across operating units by setting up the top reporting level.
You can run your reports at the set of books level, legal entity level, or
operating unit level

Business Group:
The business group represents the highest level in the organization
structure, such as the consolidated enterprise, a major division, or an
operation company. The business group secures human resources
information. For example, when you request a list of employees, you
see all employees assigned to the business group of which your
organization is a part.

Set Of Books:
A financial reporting entity that uses a particular chart of accounts,
functional currency, and accounting calendar. Oracle General Ledger
secures transaction information (such as journal entries and balances)
by set of books. When you use Oracle General Ledger, you choose a
responsibility that specifies a set of books. You then see information for
that set of books only.

Legal Entity:
A legal company for which you prepare fiscal or tax reports. You
assign tax identifiers and other legal entity information to this type of

Operating unit:
An organization that uses Oracle Cash Management, Order
Management and Shipping Execution, Oracle Payables, Oracle
Purchasing, and Oracle Receivables. It may be a sales office, a division,
or a department. An operating unit is associated with a legal entity.
Information is secured by operating unit for these applications. Each
user sees information only for their operating unit.

Inventory Organization:
An organization for which you track inventory transactions and
balances, and/or an organization that manufactures or distributes
products. Examples include (but are not limited to) manufacturing
plants, warehouses, distribution centers, and sales offices. The
following applications secure information by inventory organization:
Oracle Inventory, Bills of Material, Engineering, Work in Process,
Master Scheduling/MRP, Capacity, and Purchasing receiving functions.

Which is another organization inside of the Inventory oganization will
be used to define the locations under these location items will be placed.

Multiorg Table:
It is a table contains the data which is relted to multiple operating units
all the multiorg table names will be end with '_ALL'.

      AP_CHECKS_ALL  and so on

Note: In all these tables we will find one common column called "ORG_ID"
  This column will be populated internally by the system as per the User Operating
  Unit ID

It is one  the RDBMS vaiabel which contains the User Operating Unit value (ORG_ID)

Multiorg View:
It is a view which is created based on the Multiorg table which contains the WHERE
clause WHERE Org_ID = :Client_Info.

Note: While development of RICE Components we are suppose to Use Multiorg Views
      not Multi Org Tables.

Because if we use Multiorg tables we will get all the operating units data
if we use multiorg view we will get the operating units data which is related for that
perticular user .

1)What is the Diff between ORG_ID and ORGANIZATION_ID?
Ans)ORG_ID              is at Operating Unit Level
    ORGANIZATION_ID     is at Inventory Organization level

2)Why the PO Receipt functionality will come at Inventory organization level?
Ans)user is creating receipt means indirectley he will be receiving the materials from
    suppliers.materials will be received at Gowdons ,Warehouses,manufcaturing plants
    all these organizations will come at Inventory Organization level.

3)how the System Will Identify user is working for so and so operating Unit?
Ans ) By using following Profile called
      MO:Operating Unit

4)What is Multiorg?
5)What is Client_info?
6)how to Implement Multiorg in Reports and at SQL prompt?
7)What is Business group, Legal Entity,Operating Unit,Inventory Organizations?
8)What are the Modules will come at operating Unit level?
9)What is the flow of Multiorg?
10)How to Identify the Multiorg Table?
11)Wat is the Diff between Multiorg Table and Multiorg View?
12)While Developing RICE Components we will use Multiorg Table or Multiorg View?
13)Why there is no _ALL for PO_VENDORS(11i),R12(AP_suppliers)
   and there is    _ALL for PO_VENDOR_SITES_ALL(11i)/AP_SUPPlIER_SITES?
Ans)Supplier is global
    Supplier site is Org Specific.
14)How will you findout Multiorg Succesfully Implemented?


Y - Succesfully Implemented
N - Not Implemented Succesfully.

Oracle APPS:Profiles

Profile :
Profile is one of the changable option it will change the way of
application execution.
When User Log in to the application and select the the resp or Appl
system will automatically captures all the profile value as per the
profile values application will run.
Ex: If client have three Organizations 1)Hyd
If "hyd" users connect to the Application system will retrive the
data from database which is related to the Hyderabad branch.
If user is working for 'CHN' brnach then chennai branch setups or data
will be retrieved.
For every user we will assign the Profile value
Ex: Operation
Position - Profile Name
Profile Values
When we want assign any profile value we have four levels
we have to select any one of the level.
Profile Level Profile  Profile Value
------------- -------  --------------
User - OPERATIONS -  Print  - 10(This is for for Operations)
Responsibility  - 22Responsi -  Print  -  5(This is for 22resp users)
Application     - GL Applica -  Print  -  4(This is for GL App Users)
Site - ---        -  Print  -  2(This is for ALL Users)
Site : this is lowest level to assign the Profile values site values
are applicable for all the users.when we install Application by default
site level values will be assigned.
Application: These values are applicable for the users who are having
the access for the application. If user is eligible for both
application and site level values then application level value will
override the site level value.
Responsibility:We will select the responsibility name assign the value
which is applicable only for the users who are having the access for
specified responsibility.
Responsibility level value will override both application and site
level values.
User: This is highest level in the profile option.
we will select the user name and assign the profile value which is
applicable only for this user.
User level value will override all other profile level values.

Diff between Application and Responsibility:
Both are Group of Forms(Menu)
         Group of ConcurrentPrograms(Request Group)
         Group of Users (Data group)
But Application as per the Business functionality requirement
Responsibility will group as per the position requirement.
Some of the Imp Profile Names:
MO:Operating Unit
Hr:Business Groups
RESP_NAME and so on..........

User Creation

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