Showing posts with label Basics. Show all 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: http://goo.gl/wOyzJP
Oracle Order Import Part 2 : http://goo.gl/cTPkoe
Orace Order Import Part 1 http://goo.gl/xDfVYG
Oracle Order Holds Release http://goo.gl/E8Hvsy
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.
Syntax:
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;
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 <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.
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 <ProcedureName> (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 <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
SRW.USER_EXIT('FND SRWINIT');
3)Call another User Exit from After Report Trigger
SRW.USR_EXIT('FND SRWEXIT');
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
FND SRWEXIT
FND FLEXSQL
FND FLEXIDVAL
FND FORMATCURRENCY
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
database.
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
Responsibility
user
SRS
user exits.
FND_USER
FND_USER_REPS
Purchasing = functionality 500
Imp tables Join
01243992026
CARD NO
NAME
CONTACT NO
REASON
12307RS
==============================================
1)Define the Mandatroy parameter called P_CONC_REQUEST_ID
2)Call the Following User exit from the Before report Triggers
SRW.USER_EXIT('FND SRWINIT');
3)Call another User Exit from After Report Trigger
SRW.USR_EXIT('FND SRWEXIT');
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
FND SRWEXIT
FND FLEXSQL
FND FLEXIDVAL
FND FORMATCURRENCY
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
database.
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
Responsibility
user
SRS
user exits.
FND_USER
FND_USER_REPS
Purchasing = functionality 500
Imp tables Join
01243992026
CARD NO
NAME
CONTACT NO
REASON
12307RS
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
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
Navigation:
==========
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 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.
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
Navigation:
==========
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 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)
Items
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
organization.
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.
Subinventory:
=============
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'.
like PO_HEADER_ALL
PO_LINES_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
Client_Info:
===========
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?
Ans)
SELECT MULTI_ORG_FLAG ,RELEASE_NAME FROM FND_PRODUCT_GROUPS
Y - Succesfully Implemented
N - Not Implemented Succesfully.
=============
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)
Items
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
organization.
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.
Subinventory:
=============
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'.
like PO_HEADER_ALL
PO_LINES_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
Client_Info:
===========
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?
Ans)
SELECT MULTI_ORG_FLAG ,RELEASE_NAME FROM FND_PRODUCT_GROUPS
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
2)Ban
3)Chn
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
--------------
Manager
Supervisior
Clerk
Operator
Trainess
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
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
RESP_NAME and so on..........
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
2)Ban
3)Chn
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
--------------
Manager
Supervisior
Clerk
Operator
Trainess
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
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
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
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
Wednesday, May 6, 2015
Who Columns In Oracle Apps
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
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
Tuesday, April 28, 2015
Oracle Joins
Type of joins
-------------
(8i and Prior) Compliant
Joins
------------------------ -----------------
- Equijoin -
Cross Joins
- Non-Equijoin -
Natural Joins
- Outer Join -
Using Clause
- Self Join - Full or Two sided outer joins
-
Arbitrary join conditions for outer joins
1. Use a join to
query data from more than one table
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2]|
[NATURAL JOIN table2] |
[JOIN table2 USING { column_name } ]
|
[JOIN table2 ON (table1.column_name=
table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN
table2 ON (table1.column_name = table2.column_name ) ];
2. Eg Cross
join:
SELECT ename,dname
FROM emp
CROSS JOIN DEPT;
3. NATURAL JOIN
---------------
- NATURAL JOIN
clause is based on all columns in the two tables that have the same name.
- It selects
rows from the two tables that have equal values in all matched columns
- If the columns
having the same names have different data types , an error is returned
In oracle 9i it
is possible to let the join be completed automatically based on columns in the
two tables which
have matching data types and names using the keywords NATURAL JOIN.
Eg: SELECT deptno,dname,ename,empno
FROM DEPT
NATURAL JOIN EMP
OR
SELECT deptno,dname,empno,ename
FROM EMP , DEPT
WHERE EMP.DEPTNO= DEPT.DEPTNO
Natural Joins
with WHERE clause
Additional restrictions on a natural
join are implemented by using a WHERE clause. The
example
SELECT
deptno,dname,ename,sal
FROM DEPT
NATURAL JOIN emp
WHERE deptno IN
(20,30)
4. NATURAL JOINS
WITH THE USING CLAUSE
---------------------------------------
- If several
columns have the same names but the data types do not match, The NATURAL JOIN
clause can be
modified with the USING clause to specify the columns that should be used for
an equijoin
- Use the USING
clause to match only one column when more thatn one column matches
- Do not use
table name or alias in the referenced columns
- The NATURAL
JOIN and USING clauses are mutually exclusive.
- The columns
referenced in the USING clause should not have a qualifier (table name or alias)
anywhere in the
SQL statement
SELECT
E.ENAME,D.DNAME
FROM EMP E JOIN
DEPT D USING (Deptno)
WHERE Deptno=30;
error coz we can
use alias or depts refered in join in either select or where clause
SELECT
E.ENAME,D.DNAME
FORM EMP E JOIN
DEPT D
USING (DEPTNO);
or
SELECT ENAME,DNAME
FORM EMP , DEPT
WHERE
EMP.DEPTNO=DEPT.DEPTNO
5. CREATING
JOINS WITH THE ON CLAUSE
--------------------------------------
- The join
condition for the natural join is basically an equi join of all columns with the
same
name
- To specify
arbitrary conditions or specify columns to join, the ON clause is used.
- The ON clause
makes code easy to understand
Eg
SQL> SELECT
E.ENAME,E.EMPNO,D.DNAME
2 FROM EMP E
3 JOIN DEPT D
4 USING (DEPTNO);
or
SQL> ed
1 SELECT ENAME,DNAME
2 FROM EMP
3 JOIN DEPT
4* USING (DEPTNO)
THREE WAY JOINS
WITH THE ON CLAUSE
----------------------------------
SELECT
EMPNO,ENAME,DNAME,SALGRADE
FROM EMP E
JOIN DEPT D
ON D.DEPTNO =
E.DEPTNO
JOIN DEPT1 D1
ON
D1.DEPTNO=E.DEPTNO
JOINS COMPARING
SQL to oracle syntax
----------------------------------------------------
ORACLE SQL
1999
---------------------------------------------------
EQUI-JOIN NATURAL/INNER JOIN
OUTER-JOIN LEFT OUTER
JOIN
SELF-JOIN JOIN ON
NON-EQUI-JOIN JOIN USING
CARTESIAN
PRODUCT CROSS JOIN
---------------------------------------------------
6. LEFT OUTER
JOIN
-------------------
SELECT
E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
LEFT OUTER JOIN DEPT D-> (+) will on this
table
ON (E.DEPTNO=D.DEPTNO)
OR
1
SELECT E.ENAME,E.DEPTNO,D.DNAME
2 FROM
EMP E,DEPT D
3* WHERE E.DEPTNO=D.DEPTNO(+)
OR
1
SELECT E.ENAME,E.DEPTNO,D.DNAME
2 FROM
EMP E,DEPT D
3* WHERE D.DEPTNO(+)=E.DEPTNO
7. RIGHT OUTER
JOIN:
---------------------
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E->(+) will b on this table
RIGHT OUTER JOIN DEPT D
ON(E.DEPTNO=D.DEPTNO)
or
1
SELECT E.ENAME,E.DEPTNO,D.DNAME
2 FROM
EMP E,DEPT D
3* WHERE D.DEPTNO=E.DEPTNO(+)
SQL> /
OUTER-JOIN:
-------------
-> To join n
tables together, you need a minimum of n-1 join conditions. This rule may not
apply if your
table has a concatenated primary key, in which case more than one column
is required to
uniquely identify each row.
-> The operator
is a plus sign enclosed in brackets and "it is placed on the side of the
join
that is
deficient in information" ie OUTER JOIN
This operator
has the effect of creating one or more null rows, to which one or more row from
the nondeficient
table can be joined.
Table.column =
is the condition that joins the tables together
table.column(+) is the outer join symbol, which can be placed
on either side of the where
clause condition
but not on both sides. place the outer join symbol follwing the name of the
column in the
table without the matching rows.
- the outer join
operator can appear on only one side of the expression the side that has
information
missing. It returns those rows from one table that have no direct match in the
other table.
- A condition
involving an outer join cannot use the IN operator or b linked to another
by the OR operator.
Eg: SELECT
E.ENAME,E.EMPNO,E.MGR,M.EMPNO,M.ENAME
FROM EMP E,EMP M
WHERE
E.MGR=M.EMPNO(+)
--The above
query gives all the employee details whose empno = mgrno which implies they are
--managers and
we want the details of all employee (empno) who have mgr no or not ie whether
-- that empno
has mgr or not it displays all the empno's and if any empno doesnt have mgr
then
-- implies that
employee doesnt have manager like the empno=7839 ename =KING
8. FULL OUTER
JOIN:
--------------------
SELECT
E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
FULL OUTER JOIN
DEPT D
ON
(E.DEPTNO=D.DEPTNO)
Sunday, April 26, 2015
FlexField Concepts
OVERVIEW OF FLEXFIELD CONCEPTS
A flexfield is a field made up of sub-fields, or segments. A flexfield appears on your form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values. There are two types of flexfields: key flexfields and descriptive flexfields.
BASIC FLEXFIELD CONCEPTS
Segment - A segment is a single sub-field within a flexfield. You define the appearance and meaning of individual segments when customizing a flexfield. A segment is represented in your database as a single table column.
For a key flexfield, a segment usually describes a particular characteristic of the entity identified by the flexfield. For example, you can have a key flex field that stores part numbers. The key flexfield can contain the part number PAD-YEL-NR-8 1/2x14, which represents a yellow, narrow ruled, 8 1/2" x 14" note pad. Each section in the part number, separated by a hyphen, describes a characteristic of the part. The first segment describes the object, a note pad, the second segment describes the color of the object, yellow, and so on.
Note that we also refer to the fields in a descriptive flexfield pop-up window as segments even though they do not necessarily make up meaningful codes like the segments in key flexfields. However, they do often describe a particular characteristic of the entity identified elsewhere on the form you are using.
Values, Validation and Value Sets - Your end user enters a segment value into a segment while using an application. Generally, the flexfield validates each segment against a set of valid values (a "value set") that are usually predefined. To "validate a segment" means that the flexfield compares the value a user enters in the segment against the values in the value set for that segment.
You can set up your flexfield so that it automatically validates segment values your end user enters against a table of valid values. If your end user enters an invalid segment value, a list of valid values appears automatically so that the user can choose a valid value. You can think of a value set as a "container" for your values. You choose what types of values can fit into your value set: their length, format, and so on.
A segment is usually validated, and usually each segment in a given flexfield uses a different value set. You can assign a single value set to more than one segment, and you can even share value sets among different flexfields. For most value sets, when you enter values into a flexfield segment, you can enter only values that already exist in the value set assigned to the segment.
Structure - A flexfield structure is a specific configuration of segments. If you add or remove segments, or rearrange the order of segments in a flexfield, you get a different structure.
You can define multiple segment structures for the same flexfield (if that flexfield has been built to support more than one structure). Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data. Both key and descriptive flexfields may allow more than one structure.
In some applications, different users may need a different arrangement of the segments in a flexfield (key or descriptive). Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.
Your Oracle General Ledger application, for example, provides different Accounting Flexfield (Chart of Accounts) structures for users of different sets of books. The Oracle General Ledger application determines which flexfield structure to use based on the value of the GL Set of Books Name user profile option.
A flexfield is a field made up of sub-fields, or segments. A flexfield appears on your form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values. There are two types of flexfields: key flexfields and descriptive flexfields.
BASIC FLEXFIELD CONCEPTS
Segment - A segment is a single sub-field within a flexfield. You define the appearance and meaning of individual segments when customizing a flexfield. A segment is represented in your database as a single table column.
For a key flexfield, a segment usually describes a particular characteristic of the entity identified by the flexfield. For example, you can have a key flex field that stores part numbers. The key flexfield can contain the part number PAD-YEL-NR-8 1/2x14, which represents a yellow, narrow ruled, 8 1/2" x 14" note pad. Each section in the part number, separated by a hyphen, describes a characteristic of the part. The first segment describes the object, a note pad, the second segment describes the color of the object, yellow, and so on.
Note that we also refer to the fields in a descriptive flexfield pop-up window as segments even though they do not necessarily make up meaningful codes like the segments in key flexfields. However, they do often describe a particular characteristic of the entity identified elsewhere on the form you are using.
Values, Validation and Value Sets - Your end user enters a segment value into a segment while using an application. Generally, the flexfield validates each segment against a set of valid values (a "value set") that are usually predefined. To "validate a segment" means that the flexfield compares the value a user enters in the segment against the values in the value set for that segment.
You can set up your flexfield so that it automatically validates segment values your end user enters against a table of valid values. If your end user enters an invalid segment value, a list of valid values appears automatically so that the user can choose a valid value. You can think of a value set as a "container" for your values. You choose what types of values can fit into your value set: their length, format, and so on.
A segment is usually validated, and usually each segment in a given flexfield uses a different value set. You can assign a single value set to more than one segment, and you can even share value sets among different flexfields. For most value sets, when you enter values into a flexfield segment, you can enter only values that already exist in the value set assigned to the segment.
Structure - A flexfield structure is a specific configuration of segments. If you add or remove segments, or rearrange the order of segments in a flexfield, you get a different structure.
You can define multiple segment structures for the same flexfield (if that flexfield has been built to support more than one structure). Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data. Both key and descriptive flexfields may allow more than one structure.
In some applications, different users may need a different arrangement of the segments in a flexfield (key or descriptive). Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.
Your Oracle General Ledger application, for example, provides different Accounting Flexfield (Chart of Accounts) structures for users of different sets of books. The Oracle General Ledger application determines which flexfield structure to use based on the value of the GL Set of Books Name user profile option.
Friday, April 24, 2015
SUBSTR INSTR
SUBSTR
Oracle/PLSQL: SUBSTR Function
This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.
Description
The Oracle/PLSQL SUBSTR functions allow you to extract a substring from a string.
Syntax
The syntax for the SUBSTR function in Oracle/PLSQL is:
SUBSTR( string, start_position [, length ] )
Parameters or Arguments
string
The source string.
start_position
The starting position for extraction. The first position in the string is always 1.
length
Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.
Note:
If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
If length is a negative number, then the SUBSTR function will return a NULL value.
Applies To
The SUBSTR function can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.
For example:
SUBSTR('This is a test', 6, 2)
Write Output
SUBSTR('This is a test', 6)
Write Output
SUBSTR('Chandra Sekhar', 1, 4)
Write Output
SUBSTR('Chadra Sekhar', -3, 3)
Write Output
SUBSTR('Chandra Sekhar', -6, 3)
Write ouput
SUBSTR('Chandra Sekhar', -8, 2)
Result: 'On'
INSTR
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating the position of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.
Example Syntax:
INSTR( source_string, substring [, start_position [, occurrence ] ] )
Source_string is the string to be searched.
substring is the character string to be searched for inside of source_string.
Start_position is an optional argument. It is an integer value that tells Oracle where to start searching in the source_string. If the start_position is negative, then Oracle counts back that number of characters from the end of the source_string and then searches backwards from that position. If omitted, this defaults to 1.
Occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.
If the sub string is not found in source string, the Oracle INSTR function will return 0.
INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
Question on Instr/Sub string
There is a path in obiee where it is showing as /shared/financials/. In this string, want to get the value after second special character ('/') to before third special character ('/').
That means i should get the value "financials".
Solution
select substr(:a,instr(:a,'/',1,2)+1,
Notes On Date Data Type
YEAR Year, spelled out
YYYY 4-digit year
YYY
YY
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.
ex:
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL
It will return Month is this Format JAN,FEB,MAR,,,
Note while comparing need to TRUNC(SYSDATE) otherwise data has Time stamp also
SQL functions
MIN
SELECT MIN(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT min(sal)
FROM emp
it will return Minimum salary of the Employee table
MaX
SELECT MAX(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT MAX(sal)
FROM emp
it will return Maximum salary of the Employee table
AVG
SELECT AVG(aggregate_expression)
FROM tables
WHERE conditions;
Example
SELECT AVG(sal)
FROM emp
it will return Average salary of the Employees table
COUNT
Example
SELECT count(*)
FROM emp
it will return no of employee of the Employees table
Oracle/PLSQL: SUBSTR Function
This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.
Description
The Oracle/PLSQL SUBSTR functions allow you to extract a substring from a string.
Syntax
The syntax for the SUBSTR function in Oracle/PLSQL is:
SUBSTR( string, start_position [, length ] )
Parameters or Arguments
string
The source string.
start_position
The starting position for extraction. The first position in the string is always 1.
length
Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.
Note:
If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
If length is a negative number, then the SUBSTR function will return a NULL value.
Applies To
The SUBSTR function can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.
For example:
SUBSTR('This is a test', 6, 2)
Write Output
SUBSTR('This is a test', 6)
Write Output
SUBSTR('Chandra Sekhar', 1, 4)
Write Output
SUBSTR('Chadra Sekhar', -3, 3)
Write Output
SUBSTR('Chandra Sekhar', -6, 3)
Write ouput
SUBSTR('Chandra Sekhar', -8, 2)
Result: 'On'
INSTR
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating the position of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.
Example Syntax:
INSTR( source_string, substring [, start_position [, occurrence ] ] )
Source_string is the string to be searched.
substring is the character string to be searched for inside of source_string.
Start_position is an optional argument. It is an integer value that tells Oracle where to start searching in the source_string. If the start_position is negative, then Oracle counts back that number of characters from the end of the source_string and then searches backwards from that position. If omitted, this defaults to 1.
Occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.
If the sub string is not found in source string, the Oracle INSTR function will return 0.
INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
Question on Instr/Sub string
There is a path in obiee where it is showing as /shared/financials/. In this string, want to get the value after second special character ('/') to before third special character ('/').
That means i should get the value "financials".
Solution
select substr(:a,instr(:a,'/',1,2)+1,
Pass your string for variable you can get the desired results
Notes On Date Data Type
YEAR Year, spelled out
YYYY 4-digit year
YYY
YY
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.
ex:
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL
It will return Month is this Format JAN,FEB,MAR,,,
Note while comparing need to TRUNC(SYSDATE) otherwise data has Time stamp also
SQL functions
MIN
SELECT MIN(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT min(sal)
FROM emp
it will return Minimum salary of the Employee table
MaX
SELECT MAX(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT MAX(sal)
FROM emp
it will return Maximum salary of the Employee table
AVG
SELECT AVG(aggregate_expression)
FROM tables
WHERE conditions;
Example
SELECT AVG(sal)
FROM emp
it will return Average salary of the Employees table
COUNT
Example
SELECT count(*)
FROM emp
it will return no of employee of the Employees table
Wednesday, April 22, 2015
Oracle Dual Table
Oracle, DUAL is a table which is created with every installation of Oracle
The DUAL table's column, the column name, its datatype and even its value are not important. Technically it could be any datatype. DUAL exists just so we have a 1 row table we can reliably select from.
Don't EVER add rows to DUAL, fiddle with its column, or alter it in any way. If you do, you can expect some very strange and potentially destructive things to happen in your database, assuming the database doesn't just crash and burn completely
select sysdate from emp
1) select 1 from DUAL;
returns 1
2) select 1+2 from DUAL;
returns 3
3) select sysdate from DUAL;
returns system date of oracle database.
4) select `sql` from DUAL;
returns`sql`
5) select user from DUAL;
returns oracle user logged in.
6) select
(select empno from emp where empno = 10)
from DUAL;
returns 10
DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)
7) select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.
8) select LOWER(`ORAclE`) from DUAL;
returns`oracle` where LOWER is a in-built SQL function.
Interview tips
If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!
According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.
Is it valid or not??
CREATE TABLE xxemp1
AS
SELECT * FROM DUAL
*Read*Explore*Succeed*
The DUAL table's column, the column name, its datatype and even its value are not important. Technically it could be any datatype. DUAL exists just so we have a 1 row table we can reliably select from.
Don't EVER add rows to DUAL, fiddle with its column, or alter it in any way. If you do, you can expect some very strange and potentially destructive things to happen in your database, assuming the database doesn't just crash and burn completely
select sysdate from emp
1) select 1 from DUAL;
returns 1
2) select 1+2 from DUAL;
returns 3
3) select sysdate from DUAL;
returns system date of oracle database.
4) select `sql` from DUAL;
returns`sql`
5) select user from DUAL;
returns oracle user logged in.
6) select
(select empno from emp where empno = 10)
from DUAL;
returns 10
DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)
7) select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.
8) select LOWER(`ORAclE`) from DUAL;
returns`oracle` where LOWER is a in-built SQL function.
Interview tips
If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!
According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.
Is it valid or not??
CREATE TABLE xxemp1
AS
SELECT * FROM DUAL
*Read*Explore*Succeed*
Tuesday, April 21, 2015
Oracle Apps Basics
ERPs available in market
- SAP
- Oracle Applications
- People soft
- Siebel
- JD Edwards
Oracle
Applications are used to capture Business functionality information of the
organization.
- Instances
- Development - Development
- Development stage 1
- Development stage 2
- Testing - Testing
- Production – Client testing
Involved personalities of Oracle Applications
and their roles and responsibilities in brief
1)
DBA
– Database Administrator
a.
Installation
of Oracle Applications
b.
Applying
patches
c.
Maintaining
Multiple Instances
2)
Functional
Consultant
a.
Interaction
with the client
b.
Gathering
the requirements for development / customization
c.
About
oracle applications what available and what wasn’t available.
d.
Example
invoice form – fields - Or develop a
invoice form from scratch
e.
Preparation
of FDD (Functional Design Document)
3)
Technical
Consultant – Input FDD
a.
Go
through the FDD
b.
Prepare
TDD (Technical Design Document) – Logic, Tables, Procedures, Forms, Menus,
Packages – approval
c.
Development
of Component
d.
Test
the component
e.
Deliver
to the client
All these sessions will go trough the below
mentioned components
R I C
E -
Components for development 70%
- R - Reports - Reports 6i/10g
- I - Interfaces - Programs, SQL, PL/SQL
- C - Conversion - Programs, SQL, PL/SQL
- E - Extensions - Forms 6i/10g
Functions
Knowledge 30%
- Purchase Order Module
- Accounts Payable Module
- Order Management Module
- Inventory Module
Subscribe to:
Posts
(
Atom
)