Most of the developers are like 'accidental developers' and they identify the database performance problems only when the client reports the issues. If you give importants to these guidelines, you can standardise the performance to a stable level. I wrote this article from my own experience and knowledge. Since internet is a wide resource, like most of the professionals I also got such information from the contributions which I read from browsing.
The statements provided here may not be applicable to all the databases since all the features (Eg: Stored Procedures) are not available on all the database systems.
Good Practices for Beginners
1. Divide your large table to small multiple tables. (In SQL terminology we call it as Normalization)
2. Use the lookup tables. This will help you reduce the overload of tables. Eg: if you have a product-sales table and want to store 3 pictures of the same product, rather than adding three columns to the table use one lookup table. This way you have the freedom to add any number of photos.
3. Also, use only necessary columns. Eg: If you have columns A & B and you have the sum in another column C. Simply, you don't need that C since we can re produce the same table effect with statement SELECT A, B, (A+B) AS C FROM TABLE1.
4. The performance of your database increases if the primary key is numeric or small in data width.
5. Do not use database to store your images. Good approach is store only URL in the table and store the image file in desk itself.
6. But sometimes the Normalization may fail if you have a large database, much calculations and frequent calls if the output table is almost static in result. (Eg: salary details of employees for years old data). In this case you can improve the performance by using a De-normalized table. But that too have much demerits like very large database etc.
7. Use the right data types and widths when defining columns. Eg: If you want to store the 'age', then you don't need to use the VARCHAR field since a INT can do the job. (INT can store integer data from 0 to 255. You know no 'age' value exceeds 255)
8. Start! If you don't have the practice of using Primary Key, Foreign Key etc.
9. Use stored procedures and functions instead of writing all the messy code in the program itself. It not only gives you the performance but also a matter of security.
10. Always try to keep one error table to log all the errors which comes from the stored procedures or functions. It helps you save time and avoid tension for finding 'Where is the bug?'
11. Use transactions to avoid loss of data while the execution of a stored procedure. The possibility of failure is high. It can be a data truncation problem, network problem etc. In MSSQL we have BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION available. Use this along with the error handling methods. Regarding performance TRANSACTION is one step down when comparing with ordinary statements but when considering quality of the product it is very high.
12. Avoid using CURSORs. Use only when there is no other way exists. In most cases CURSOR consumes much time since it is a record-by-record process.
13. Avoid using GOTOs. This is not just for SQL but for all the programming languages. GOTO statement is considered unstructured and it have the possibility of jumping out of our hands. But there are circumstances which we cannot avoid GOTO.
14. Avoid IF and start using CASE.
15. Write your code readable.
a. User proper indents.
b. Keep all the statements in upper case.
c. Use lowercase for data-types
d. Use Upper Camel notations (also known as Pascal Casing) for all user created objects. Eg: @EmployeeCode
e. Use meaningful user-defined identifiers. Use only names which contain A-Z, a Z, 0-9 and underscore character. Do not use regional special characters.
f. Specify operation performed also in the name of stored procedure. Eg: spEmployeeUpdate
g. Always use the schema also while calling sql objects. Eg: EXEC dbo.spEmployeeUpdate
h. Use BEGIN and END to specify block of statements
i. User alias where we need operations including multiple tables
j. Even though the AS keyword is optional, use always.
16. Use proper commenting. Also add the purpose, author, date, version etc. details on top of all the procedures, functions etc.
17. Add test data with statements inside stored procedures and comment it. This helps you in future debugging. Also this helps another developer to get a quick start.
18. Do proper INDEX-ing. There will be noticeable difference when applying INDEX on very large tables.
19. Always use column names in SELECT, INSERT, UPDATE statements. Avoid using asterisks –(*).
20. Avoid using dynamic SQL statements inside stored procedures. i.e., Do not use SQL statements to create SQL statements.
21. Be careful while SELECT-ing strings with LIKE clause. If it is not used wisely, it will arise performance problems.
References:
http://www.codeproject.com/KB/architecture/SQLGoodPractices.aspx
Saturday, September 27, 2008
Tuesday, September 23, 2008
Bounce Apache Server in Oracle Apps 11i
Please find the two commands that are used for bouncing the Apache below:
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start
Profile Options in HRMS
Profile Options provide flexibility to Oracle Apps, they act like global variables .It eliminates the need to hard code the logic.
What is profile option?
The profile option acts like a Global Variable in Oracle.
Why does Oracle provide profile options?
These are provided to keep the application flexible. The business rules in various countries and various companies can be different. Hence the profile options are delivered by Oracle in such a manner to avoid hard-coding of logic, and to let the implementation team at site decide the values of those variables.
Example:-
An organization have two departments say D1 and D2.We want the Manager of D1 to restrict viewing employees of D2 and vice versa. Then we will set the profile options MO:Department name or the organization name against the profile option.
Some of the scenarios where profile options can be used by Oracle are as follows:
1. There are profile options which can turn the debugging on, to generate debug messages. Say one of 1000 users reports a problem, and hence you wish to enable debugging against just that specific user. In this case you can “Turn On” the debugging profile option "again that specific user".
2. There are profile options that control which user can give discount to their customers at the time of data entry. You can set profile option "Discount Allowed" to a value of either Yes or No against each Order Entry user.
3. Lets assume an Organization has department D1 and D2. Managers of both the Departments have "HRMS Employee View" responsibility. But you do not want Manager of D2 to be able to see the list of Employees in Organization D1. Hence you can set a profile option against the username of each of these users. The value assigned to such profile option will be "Name of the Organization" for which they can see the employees. Of course, the SQL in screen that displays list of employees will filter off the data based on “logged in users profile option value”.
Profile options determines the behaviour of our application in oracle hrms. There are many profile values in Oracle HRMS, But basically we need to set 4 profile values only.
1. HR:Security Profile
2. HR:User Type as Only HR/HR with Payroll
3. HR:Cross Business Group as yes/no, if yes.. then user can access data from other Business group too.
4. MO:Operating Unit, if Multi Org is set up for a BG then
this is mandatory to setup ow optional.
What is profile option?
The profile option acts like a Global Variable in Oracle.
Why does Oracle provide profile options?
These are provided to keep the application flexible. The business rules in various countries and various companies can be different. Hence the profile options are delivered by Oracle in such a manner to avoid hard-coding of logic, and to let the implementation team at site decide the values of those variables.
Example:-
An organization have two departments say D1 and D2.We want the Manager of D1 to restrict viewing employees of D2 and vice versa. Then we will set the profile options MO:Department name or the organization name against the profile option.
Some of the scenarios where profile options can be used by Oracle are as follows:
1. There are profile options which can turn the debugging on, to generate debug messages. Say one of 1000 users reports a problem, and hence you wish to enable debugging against just that specific user. In this case you can “Turn On” the debugging profile option "again that specific user".
2. There are profile options that control which user can give discount to their customers at the time of data entry. You can set profile option "Discount Allowed" to a value of either Yes or No against each Order Entry user.
3. Lets assume an Organization has department D1 and D2. Managers of both the Departments have "HRMS Employee View" responsibility. But you do not want Manager of D2 to be able to see the list of Employees in Organization D1. Hence you can set a profile option against the username of each of these users. The value assigned to such profile option will be "Name of the Organization" for which they can see the employees. Of course, the SQL in screen that displays list of employees will filter off the data based on “logged in users profile option value”.
Profile options determines the behaviour of our application in oracle hrms. There are many profile values in Oracle HRMS, But basically we need to set 4 profile values only.
1. HR:Security Profile
2. HR:User Type as Only HR/HR with Payroll
3. HR:Cross Business Group as yes/no, if yes.. then user can access data from other Business group too.
4. MO:Operating Unit, if Multi Org is set up for a BG then
this is mandatory to setup ow optional.
CRP (Conference Room Pilot)
The definition of prototyping, or a conference room pilot (CRP) is “a product model constructed for testing and evaluation to see how the product performs before releasing the product to manufacture.” A conference room pilot is exactly that, a prototyping of the software functionality and customizations prior to releasing the product to the end user.
Each customization should be prototyped and executed during a series of detailed pilots or module walk-throughs.Each pilot should be designed to target a specific stage, event, or business process of the enterprise during an implementation and should determine success or failure as the outcome.
A successful CRP can be divided into three stages, namely Identification, Definition, and Execution.
Identification: -
The first step in most projects is to understand and document the current organization business processes, or the creation of the “as is business model”. Understanding the organizations business processes helps lay the foundation required for a successful implementation.
Definition:-
The Definition stage provides some of the keys to executing the CRP – one of them being the end user testing documentation. This stage consists primarily of documenting the testing scenarios and scripts for execution during the CRP. These scenarios will consist of real organization processes and data. Remember the CRP is a prototype of the production environment and all testing needs to be as realistic as possible.
Business scenarios are your organizations required future business process based on Oracle Application terminology and processes.
Execution:-
During this stage of the project normal implementation activities of creating the master testing plan, scheduling CRP resources and executing the CRP tasks place.This stage is where the rubber meets the road. All testing documents have been approved, the CRP instance is ready and waiting, and all we need to do to get on with the CRP is to organize the testing documentation and assign testing resources.
The CRP should be performed at least three times, or should simulate three business closings. During the execution of the CRPs you will find that problems and issues will arise, sometimes over and over again. Executing the CRP three times will help flush out application issues, train end users, and solidify customizations and data conversion
Original content taken from 'OAUG Insight March 2008' by 'Bill Dunham'. All credits on this document should go to 'Bill Dunham'
Each customization should be prototyped and executed during a series of detailed pilots or module walk-throughs.Each pilot should be designed to target a specific stage, event, or business process of the enterprise during an implementation and should determine success or failure as the outcome.
A successful CRP can be divided into three stages, namely Identification, Definition, and Execution.
Identification: -
The first step in most projects is to understand and document the current organization business processes, or the creation of the “as is business model”. Understanding the organizations business processes helps lay the foundation required for a successful implementation.
Definition:-
The Definition stage provides some of the keys to executing the CRP – one of them being the end user testing documentation. This stage consists primarily of documenting the testing scenarios and scripts for execution during the CRP. These scenarios will consist of real organization processes and data. Remember the CRP is a prototype of the production environment and all testing needs to be as realistic as possible.
Business scenarios are your organizations required future business process based on Oracle Application terminology and processes.
Execution:-
During this stage of the project normal implementation activities of creating the master testing plan, scheduling CRP resources and executing the CRP tasks place.This stage is where the rubber meets the road. All testing documents have been approved, the CRP instance is ready and waiting, and all we need to do to get on with the CRP is to organize the testing documentation and assign testing resources.
The CRP should be performed at least three times, or should simulate three business closings. During the execution of the CRPs you will find that problems and issues will arise, sometimes over and over again. Executing the CRP three times will help flush out application issues, train end users, and solidify customizations and data conversion
Original content taken from 'OAUG Insight March 2008' by 'Bill Dunham'. All credits on this document should go to 'Bill Dunham'
Wednesday, September 17, 2008
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06502: PL/SQL: numeric or value error: host bind array too small
Solution:
255 characters via DBMS_OUTPUT.PUT_LINE results in the following error when run from 10.1 SQL*Plus or prior, when connected to a 10.2 database
We need to substring of 255 characters for the value passed in the dbms_output statement.
Solution:
255 characters via DBMS_OUTPUT.PUT_LINE results in the following error when run from 10.1 SQL*Plus or prior, when connected to a 10.2 database
We need to substring of 255 characters for the value passed in the dbms_output statement.
Monday, September 15, 2008
Oracle 6i Reports/Report Builder - Part I
About Report Builder
Report Builder enables you to develop production-quality reports in a client/server or Web environment. You can deploy reports on the Web using the Reports Server and Reports Web CGI or Reports Cartridge. Reports can be run standalone, or you can run them from forms or Graphics Builder displays.
Major concepts
A report is a collection of objects defining its data, layout, and runtime interface. To quickly create new reports, you use the Report Wizard. The wizard guides you through the process of choosing a report type, defining a data model, and laying out the data. You can reenter the Report Wizard after the initial creation of a report, so you may re-invoke it on an existing report, make changes, and re default the report, even if the report was not originally created with the Report Wizard.
Major Features
1. Data model and layout editors in which you can create the structure and format of your report
2. Object navigator to help you navigate among the data and layout objects in your report
3. Packaged functions for creating computations
4. Fully-integrated Previewer for viewing your report output
5. Support for fonts, colors, and graphics
Additional Features :
1. Context-sensitive online help system
2. Conditional printing capabilities
3. Non-procedural Approach: The unique non-procedural approach of Oracle Reports lets you concentrate on design improvements instead of programming. Its' easy- to-use, fill-in-the-form interface and powerful defaults make developing and maintaining even the most complex reports fast and simple.
4. Full Integration with Other Oracle Products: One can integrate Oracle Reports with other Oracle products such as Oracle Forms, Oracle Graphics, and Oracle Mail.
5. Open Architecture: Oracle Reports' open architecture enables incorporation of user-defined routines written in COBOL, C, and most other programming languages, as well as the powerful PL/SQL language.
Object Navigator
1. The Data Model editor, in which you define the data for the report.
Data model is composed of some or all of the following data definition objects:
a. Queries: Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database.
b. Groups: Groups determine the hierarchy of the data appearing in a report, and are used primarily to create breaks. In addition to break reports, you also create groups to define the cross products of matrix reports.
c. Columns: Columns contain the data values for a report. Default report columns corresponding to the table columns included in each query's SELECT list are automatically created by Oracle Reports, then each column is placed in the group associated with the query that selected the column. If you want to perform summaries and computations on database column values, you can create new columns.
d. Parameters: Parameters are variables for your report that enable you to change selection criteria at runtime.
e. Links: Data links are used to establish parent-child relationships between queries and groups via column-matching. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. You can create links with various SQL clauses (i.e., WHERE, HAVING, or START WITH) and conditions. When a report with a data link is executed, the data link is converted into a SQL clause and appended to the child query. The child query is executed with the values of the primary key used by the parent.
2. The Layout editor, in which you create the report layout. Layout objects define a report's format; i.e., the positioning and appearance of data, text, and graphics in the report output.
3. The Parameter Form editor, in which you customize the appearance of the Runtime Parameter Form, a window that optionally appears at runtime and enables you to enter parameter values that affect report execution.
4. The Report Editor's Live Previewer view enables you to preview and inspect your report output page by page.
5. Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report.
6. Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
7. Attached libraries are external PL/SQL libraries that you have associated with a report or another external library.
Data Model – Groups
There are two types of groups in Oracle Reports:
1. Default (created by Oracle Reports)
2. User-created (created by you)
Break groups are created to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records Cross product groups perform mathematical cross products, which are generally used to create matrix reports.
Data Model – Parameter
Parameter values can be specified in these ways:
1. Accepting the default parameter values (default values are set in the Parameter property sheet, and you can control whether the values are displayed at runtime on the Runtime Parameter Form)
2. Typing the parameter value(s) as arguments on the command line (where applicable)
3. Choosing from a list or entering the parameter value(s) in the Runtime Parameter Form
User Parameters
1. Bind References: Use a bind reference when you want the parameter to substitute only one value at runtime. Precede a bind reference with a colon (:).
2. Lexical References: Use a lexical reference when you want the parameter to substitute multiple values at runtime. Precede a lexical reference with an ampersand (&).
The remaining part would be continued in Part II shortly
Report Builder enables you to develop production-quality reports in a client/server or Web environment. You can deploy reports on the Web using the Reports Server and Reports Web CGI or Reports Cartridge. Reports can be run standalone, or you can run them from forms or Graphics Builder displays.
Major concepts
A report is a collection of objects defining its data, layout, and runtime interface. To quickly create new reports, you use the Report Wizard. The wizard guides you through the process of choosing a report type, defining a data model, and laying out the data. You can reenter the Report Wizard after the initial creation of a report, so you may re-invoke it on an existing report, make changes, and re default the report, even if the report was not originally created with the Report Wizard.
Major Features
1. Data model and layout editors in which you can create the structure and format of your report
2. Object navigator to help you navigate among the data and layout objects in your report
3. Packaged functions for creating computations
4. Fully-integrated Previewer for viewing your report output
5. Support for fonts, colors, and graphics
Additional Features :
1. Context-sensitive online help system
2. Conditional printing capabilities
3. Non-procedural Approach: The unique non-procedural approach of Oracle Reports lets you concentrate on design improvements instead of programming. Its' easy- to-use, fill-in-the-form interface and powerful defaults make developing and maintaining even the most complex reports fast and simple.
4. Full Integration with Other Oracle Products: One can integrate Oracle Reports with other Oracle products such as Oracle Forms, Oracle Graphics, and Oracle Mail.
5. Open Architecture: Oracle Reports' open architecture enables incorporation of user-defined routines written in COBOL, C, and most other programming languages, as well as the powerful PL/SQL language.
Object Navigator
1. The Data Model editor, in which you define the data for the report.
Data model is composed of some or all of the following data definition objects:
a. Queries: Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database.
b. Groups: Groups determine the hierarchy of the data appearing in a report, and are used primarily to create breaks. In addition to break reports, you also create groups to define the cross products of matrix reports.
c. Columns: Columns contain the data values for a report. Default report columns corresponding to the table columns included in each query's SELECT list are automatically created by Oracle Reports, then each column is placed in the group associated with the query that selected the column. If you want to perform summaries and computations on database column values, you can create new columns.
d. Parameters: Parameters are variables for your report that enable you to change selection criteria at runtime.
e. Links: Data links are used to establish parent-child relationships between queries and groups via column-matching. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. You can create links with various SQL clauses (i.e., WHERE, HAVING, or START WITH) and conditions. When a report with a data link is executed, the data link is converted into a SQL clause and appended to the child query. The child query is executed with the values of the primary key used by the parent.
2. The Layout editor, in which you create the report layout. Layout objects define a report's format; i.e., the positioning and appearance of data, text, and graphics in the report output.
3. The Parameter Form editor, in which you customize the appearance of the Runtime Parameter Form, a window that optionally appears at runtime and enables you to enter parameter values that affect report execution.
4. The Report Editor's Live Previewer view enables you to preview and inspect your report output page by page.
5. Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report.
6. Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
7. Attached libraries are external PL/SQL libraries that you have associated with a report or another external library.
Data Model – Groups
There are two types of groups in Oracle Reports:
1. Default (created by Oracle Reports)
2. User-created (created by you)
Break groups are created to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records Cross product groups perform mathematical cross products, which are generally used to create matrix reports.
Data Model – Parameter
Parameter values can be specified in these ways:
1. Accepting the default parameter values (default values are set in the Parameter property sheet, and you can control whether the values are displayed at runtime on the Runtime Parameter Form)
2. Typing the parameter value(s) as arguments on the command line (where applicable)
3. Choosing from a list or entering the parameter value(s) in the Runtime Parameter Form
User Parameters
1. Bind References: Use a bind reference when you want the parameter to substitute only one value at runtime. Precede a bind reference with a colon (:).
2. Lexical References: Use a lexical reference when you want the parameter to substitute multiple values at runtime. Precede a lexical reference with an ampersand (&).
The remaining part would be continued in Part II shortly
Tips in Oracle 6i report customizations
1. Setup client info if views are involved, setup organization context if inventory items are involved.
2. Ensure that report builder/designer is in character mode.
3. Ensure constraints are off for stretching, grid is on. , snap to grid is on/off
4. use srw.message frequently
5. Port the rdf in Binary Mode.
6. Set the trace on for the concurrent program to actually see the sql code executed.
7. Bring the query out and changes should be tested in simple SQLs
8. Set the trace on if there are &lexical parameters if you need to check the query that is changing dynamically.
9. Use srw.message to print the value of lexical parameters after their evaluation
10. Use of Concsub to submit your report concurrent program from UNIX faster.
2. Ensure that report builder/designer is in character mode.
3. Ensure constraints are off for stretching, grid is on. , snap to grid is on/off
4. use srw.message frequently
5. Port the rdf in Binary Mode.
6. Set the trace on for the concurrent program to actually see the sql code executed.
7. Bring the query out and changes should be tested in simple SQLs
8. Set the trace on if there are &lexical parameters if you need to check the query that is changing dynamically.
9. Use srw.message to print the value of lexical parameters after their evaluation
10. Use of Concsub to submit your report concurrent program from UNIX faster.
Some of the Key Flexfields in Oracle Apps
General Ledger
1. Accounting KFF(GL_CODE_COMBINATIONS)
Accounts Receivables
1. Sales Tax KFF(AR_SALES_TAX)
2. Territory KFF(AR_TERRITORY)
Fixed Assets
1. Asset KFF (FA_KEYWORDS)
2. Category KFF
3. Location KFF(FA_LOCATION)
HRMS
1. Grade(PER_GRADE)
2. Item
3. Job(PER_JOB_DEFINITIONS)
4. Position(PER_POSITION)
5. Personal Analysis
6. Soft Coded
Inventory
1. Account Aliases
2. Item Catalogs
3. Item Categories(MTL_ITEM_CATEGORY)
4. Sales Order
5. Stock Location
6. System Item(MTL_SYSTEM_ITEM)
Pay Roll
1. Bank Details
2. Cost Allocation
3. People Group
1. Accounting KFF(GL_CODE_COMBINATIONS)
Accounts Receivables
1. Sales Tax KFF(AR_SALES_TAX)
2. Territory KFF(AR_TERRITORY)
Fixed Assets
1. Asset KFF (FA_KEYWORDS)
2. Category KFF
3. Location KFF(FA_LOCATION)
HRMS
1. Grade(PER_GRADE)
2. Item
3. Job(PER_JOB_DEFINITIONS)
4. Position(PER_POSITION)
5. Personal Analysis
6. Soft Coded
Inventory
1. Account Aliases
2. Item Catalogs
3. Item Categories(MTL_ITEM_CATEGORY)
4. Sales Order
5. Stock Location
6. System Item(MTL_SYSTEM_ITEM)
Pay Roll
1. Bank Details
2. Cost Allocation
3. People Group
Understanding the importance of Object Version Number in HRMS
Nearly every row in every database table is assigned an object_version_number. When a new row is inserted, the API usually sets the object version number to 1. Whenever that row is updated in the database, the object version number is incremented. The row keeps that object version number until it is next updated or deleted. The number is not decremented or reset to a previous value.
Note: The object version number is not unique and does not replace the primary key. There can be many rows in the same table with the same version number. The object version number indicates the version of a specific primary key row. Whenever a database row is transferred (queried) to a client, the existing object version number is always transferred with the other attributes. If the object is modified by the client and saved back to the server, then the current server object version number is compared with the value passed from the client.
• If the two object version number values are the same, then the row on the server is in the same state as when the attributes were transferred to the client. As no other changes have occurred, the current change request can continue and the object version number is incremented.
• If the two values are different, then another user has already changed and committed the row on the server. The current change request is not allowed to continue because the modifications the other user made may be overwritten and lost. (Database locks are used to prevent another user from overwriting uncommitted changes.) The object version number provides similar validation comparison to the online system.
Forms interactively compare all the field values and displays the "Record has been modified by another user" error message if any differences are found. Object version numbers allow transactions to occur across longer periods of time without holding long term database locks. For example, the client application may save the row locally, disconnect from the server and reconnect at a later date to save the change to the database. Additionally, you do not need to check all the values on the client and the server.
Example
Consider creating a new address for a Person. The create_person_address API automatically sets the object_version_number to 1 on the new database row. Then, two separate users query this address at the same time.
User A and user B will both see the same address details with the current object_version_number equal to 1. User A updates the Town field to a different value and calls the update_person_address API passing the current object_version_number equal to 1. As this object_version_number is the same as the value on the database row the update is allowed and the object_version_number is incremented to 2. The new object_version_number is returned to user A and the row is committed in the database.
User B, who has details of the original row, notices that first line of the address is incorrect. User B calls the update_person_address API, passing the new first line and what he thinks is the current object_version_number (1). The API compares this value with the current value on the database row (2). As there is a difference the update is not allowed to continue and an error is returned to user B.
To correct the problem, user B then re-queries this address, seeing the new town and obtains the object_version_number 2. The first line of the address is updated and the update_person_address API is called again. As the object_version_number is the same as the value on the database row the update is allowed to continue.
Therefore both updates have been applied without overwriting the first change.
Note: The object version number is not unique and does not replace the primary key. There can be many rows in the same table with the same version number. The object version number indicates the version of a specific primary key row. Whenever a database row is transferred (queried) to a client, the existing object version number is always transferred with the other attributes. If the object is modified by the client and saved back to the server, then the current server object version number is compared with the value passed from the client.
• If the two object version number values are the same, then the row on the server is in the same state as when the attributes were transferred to the client. As no other changes have occurred, the current change request can continue and the object version number is incremented.
• If the two values are different, then another user has already changed and committed the row on the server. The current change request is not allowed to continue because the modifications the other user made may be overwritten and lost. (Database locks are used to prevent another user from overwriting uncommitted changes.) The object version number provides similar validation comparison to the online system.
Forms interactively compare all the field values and displays the "Record has been modified by another user" error message if any differences are found. Object version numbers allow transactions to occur across longer periods of time without holding long term database locks. For example, the client application may save the row locally, disconnect from the server and reconnect at a later date to save the change to the database. Additionally, you do not need to check all the values on the client and the server.
Example
Consider creating a new address for a Person. The create_person_address API automatically sets the object_version_number to 1 on the new database row. Then, two separate users query this address at the same time.
User A and user B will both see the same address details with the current object_version_number equal to 1. User A updates the Town field to a different value and calls the update_person_address API passing the current object_version_number equal to 1. As this object_version_number is the same as the value on the database row the update is allowed and the object_version_number is incremented to 2. The new object_version_number is returned to user A and the row is committed in the database.
User B, who has details of the original row, notices that first line of the address is incorrect. User B calls the update_person_address API, passing the new first line and what he thinks is the current object_version_number (1). The API compares this value with the current value on the database row (2). As there is a difference the update is not allowed to continue and an error is returned to user B.
To correct the problem, user B then re-queries this address, seeing the new town and obtains the object_version_number 2. The first line of the address is updated and the update_person_address API is called again. As the object_version_number is the same as the value on the database row the update is allowed to continue.
Therefore both updates have been applied without overwriting the first change.
Friday, September 12, 2008
Tuesday, September 9, 2008
All about the DUAL table
Found this intresting topic on Dual table. Check these out.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388
http://www.jlcomp.demon.co.uk/dual.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388
http://www.jlcomp.demon.co.uk/dual.html
Run a UNIX shell script as a concurrent program
To run a UNIX shell script as a concurrent program, we need to register the shell script as a Host Concurrent Program. Example: Say we register an executable named XXCONC within XXPO Module.
on unix, do
1. cd $XXPO_TOP/bin
2. vi XXCONC.prog and edit and write script and save
3. ln -s $FND_TOP/bin/fndcpesr XXCONC
4. ls -l XXCONC
Also we need to ensure that there is execute permission for executable
Once done register the program with Host and assign to a request group.
on unix, do
1. cd $XXPO_TOP/bin
2. vi XXCONC.prog and edit and write script and save
3. ln -s $FND_TOP/bin/fndcpesr XXCONC
4. ls -l XXCONC
Also we need to ensure that there is execute permission for executable
Once done register the program with Host and assign to a request group.
Commonly used scripts
Query to get responsibility and attached request groups
SELECT responsibility_name responsibility, request_group_name, frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
Query to get all Request attached to a responsibility
SELECT responsibility_name, frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
Query to Count Module Wise Report
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method, COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1;
Query to calculate request time
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
, DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
Query to check responsibility assigned to a specific USER
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
Query to print the oracle apps version
SELECT substr(a.application_short_name, 1, 5) application_short_name,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')
Script used to pick up the recent EBS users and their email addresses
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')
Script to assign access to Oracle e-Business Suite responsoibilities via Workflow
DECLARE
v_user fnd_user.user_name%TYPE;
v_user_id PLS_INTEGER;
CURSOR c_get_user (cp_username IN VARCHAR2)
IS
SELECT user_orig_system_id user_id, user_orig_system orig_system
FROM wf_local_user_roles
WHERE user_name = cp_username;
CURSOR c_get_resp
IS
SELECT NAME, orig_system, orig_system_id, partition_id
FROM wf_roles
WHERE display_name = 'System Administrator'
AND orig_system = 'FND_RESP';
r_resp c_get_resp%ROWTYPE;
r_user c_get_user%ROWTYPE;
BEGIN
v_user := UPPER ('&username');
OPEN c_get_user (cp_username => v_user);
FETCH c_get_user
INTO r_user;
CLOSE c_get_user;
OPEN c_get_resp;
FETCH c_get_resp
INTO r_resp;
CLOSE c_get_resp;
INSERT INTO wf_local_user_roles
(user_name, role_name, user_orig_system, user_orig_system_id,
role_orig_system, role_orig_system_id, start_date,
expiration_date, security_group_id, partition_id, owner_tag,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, assignment_type,
parent_orig_system, parent_orig_system_id, user_start_date,
role_start_date, user_end_date, role_end_date,
effective_start_date, effective_end_date
)
VALUES (v_user, r_resp.NAME, r_user.orig_system, r_user.user_id,
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
NULL, 0, r_resp.partition_id, 'SYSADMIN',
0, SYSDATE - 1, 0,
SYSDATE - 1, 0, 'D',
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
TO_DATE ('01/01/1951', 'DD/MM/YYYY'), NULL, NULL,
SYSDATE - 1, NULL
);
END;
Query to fetch the managers managers information in HRMS
select
papf.employee_number
,papf.person_id employee_person_id
,papf.full_name employee_name
,papf.email_address emp_email_address
,papf.employee_number mgr_employee_number
,papf_mgr.person_id manager_person_id
,papf_mgr.FULL_NAME manager_full_name
,papf_mgr.email_address manager_email_address
from
per_all_people_f papf,
per_all_people_f papf_mgr
where
(papf.person_id,papf_mgr.person_id) in
( select
pasf.person_id,
pasf.supervisor_id
from
per_all_Assignments_f pasf
start with
( pasf.person_id = :x_person_id and
sysdate between pasf.effective_start_date and pasf.effective_end_date )
connect by prior
pasf.supervisor_id = pasf.person_id
and sysdate between pasf.effective_start_date and pasf.effective_end_date
)
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between papf_mgr.effective_start_date and papf_mgr.effective_end_date
SELECT responsibility_name responsibility, request_group_name, frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
Query to get all Request attached to a responsibility
SELECT responsibility_name, frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
Query to Count Module Wise Report
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method, COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1;
Query to calculate request time
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
, DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
Query to check responsibility assigned to a specific USER
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
Query to print the oracle apps version
SELECT substr(a.application_short_name, 1, 5) application_short_name,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')
Script used to pick up the recent EBS users and their email addresses
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')
Script to assign access to Oracle e-Business Suite responsoibilities via Workflow
DECLARE
v_user fnd_user.user_name%TYPE;
v_user_id PLS_INTEGER;
CURSOR c_get_user (cp_username IN VARCHAR2)
IS
SELECT user_orig_system_id user_id, user_orig_system orig_system
FROM wf_local_user_roles
WHERE user_name = cp_username;
CURSOR c_get_resp
IS
SELECT NAME, orig_system, orig_system_id, partition_id
FROM wf_roles
WHERE display_name = 'System Administrator'
AND orig_system = 'FND_RESP';
r_resp c_get_resp%ROWTYPE;
r_user c_get_user%ROWTYPE;
BEGIN
v_user := UPPER ('&username');
OPEN c_get_user (cp_username => v_user);
FETCH c_get_user
INTO r_user;
CLOSE c_get_user;
OPEN c_get_resp;
FETCH c_get_resp
INTO r_resp;
CLOSE c_get_resp;
INSERT INTO wf_local_user_roles
(user_name, role_name, user_orig_system, user_orig_system_id,
role_orig_system, role_orig_system_id, start_date,
expiration_date, security_group_id, partition_id, owner_tag,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, assignment_type,
parent_orig_system, parent_orig_system_id, user_start_date,
role_start_date, user_end_date, role_end_date,
effective_start_date, effective_end_date
)
VALUES (v_user, r_resp.NAME, r_user.orig_system, r_user.user_id,
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
NULL, 0, r_resp.partition_id, 'SYSADMIN',
0, SYSDATE - 1, 0,
SYSDATE - 1, 0, 'D',
r_resp.orig_system, r_resp.orig_system_id, SYSDATE - 1,
TO_DATE ('01/01/1951', 'DD/MM/YYYY'), NULL, NULL,
SYSDATE - 1, NULL
);
END;
Query to fetch the managers managers information in HRMS
select
papf.employee_number
,papf.person_id employee_person_id
,papf.full_name employee_name
,papf.email_address emp_email_address
,papf.employee_number mgr_employee_number
,papf_mgr.person_id manager_person_id
,papf_mgr.FULL_NAME manager_full_name
,papf_mgr.email_address manager_email_address
from
per_all_people_f papf,
per_all_people_f papf_mgr
where
(papf.person_id,papf_mgr.person_id) in
( select
pasf.person_id,
pasf.supervisor_id
from
per_all_Assignments_f pasf
start with
( pasf.person_id = :x_person_id and
sysdate between pasf.effective_start_date and pasf.effective_end_date )
connect by prior
pasf.supervisor_id = pasf.person_id
and sysdate between pasf.effective_start_date and pasf.effective_end_date
)
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between papf_mgr.effective_start_date and papf_mgr.effective_end_date
Steps for finding the Request Group of Concurrent Programs
Step 1: Ensure that profile option "Utilities:Diagnostics" is set to Yes at the user level.
Note: We can skip this step in case we have the apps password.
Step 2: Navigate to the responsibility from where we will submit the concurrent program.
Step 3: Navigate to the "Submit New Request" screen, where we will enter the name of the concurrent program to run.
Step 4: Now we must do examine using the help menu.
Step 5: In the block field, we need to enter PARAMETER, and in the field name we need to enter REQUEST_GROUP_CODE. Note down this value
Step 6: Now in the value field enter REQUEST_GROUP_APPL_SHORT_NAME.
Note down the two values from Step 5 and Step 6.
These will indicate the request group to which you must add your concurrent program.
Note: In the submit request screen, if the REQUEST_GROUP_CODE is blank then we need to use the Request group specified in the "Define Responsibility Screen"
Note: We can skip this step in case we have the apps password.
Step 2: Navigate to the responsibility from where we will submit the concurrent program.
Step 3: Navigate to the "Submit New Request" screen, where we will enter the name of the concurrent program to run.
Step 4: Now we must do examine using the help menu.
Step 5: In the block field, we need to enter PARAMETER, and in the field name we need to enter REQUEST_GROUP_CODE. Note down this value
Step 6: Now in the value field enter REQUEST_GROUP_APPL_SHORT_NAME.
Note down the two values from Step 5 and Step 6.
These will indicate the request group to which you must add your concurrent program.
Note: In the submit request screen, if the REQUEST_GROUP_CODE is blank then we need to use the Request group specified in the "Define Responsibility Screen"
Saturday, September 6, 2008
Friday, September 5, 2008
Key EUL tables for the different Discoverer components.
List of Business Areas
select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'
List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name
List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name
List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027
(substitute the numeric id from the Folders obj_id (see the List of Folder query)
List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul4_documents
Note: In the above examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples
select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'
List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name
List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name
List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027
(substitute the numeric id from the Folders obj_id (see the List of Folder query)
List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul4_documents
Note: In the above examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples
Thursday, September 4, 2008
Flow of Setup in Order Management
Step 1
1. Flex fields
Define key and descriptive flex fields to capture additional information about orders and transactions.
This step is required for Key Flex fields, and optional if you plan on using the functionality surrounding Descriptive Flex fields. Several defaulting values are provided.
Step 2
2. Multiple Organizations
Define multiple organizations in Oracle Inventory.
This step is optional.
Step 3
3. Inventory Organizations
Define inventory organizations (warehouses), parameters, sub inventories, and Picking rules in Oracle Inventory. You must define at least one item validation organization and at least one organization that acts as an inventory source for orders fulfilled internally. If you plan to drop ship some orders, you must also define at least one logical organization for receiving purposes. Your item validation organization can be the same as your inventory source or your logical receiving organization, but you cannot use one organization for all three purposes. See Step 5 for setting your item validation organization.
This step is required.
Step 4
4. Profile Options
Define profile options to specify certain implementation parameters, processing options, and system options.
This step is required.
Step 5
5. Parameters
Set your Order Management Parameters to validate items, enable customer relationships, and operating unit defaults.
This step is required.
Step 6
6. Invoicing
Define invoicing information, including payment terms, invoicing and accounting rules, Auto accounting parameters, territories, and invoice sources.
This step is required if you plan on transferring invoicing information to Oracle Receivables. Several defaulting values are provided.
Step 7
7. Salespersons
Define information on your sales representatives.
This step is optional.
Step 8
8. Tax
Define tax features, such as codes, rates, exceptions, and exemptions.
This step is required.
Step 9
9. Quick Codes
Define Quick Codes that provide custom values for many lists of values throughout Order Management.
This step is required if you plan on creating user defined Quick codes for utilization within Order Management. Defaulting values are provided.
Step 10
10. Workflow
Define order and line processing flows to meet different order and line type requirements.
This step is required.
Step 11
11. Document Sequences (Order Numbering)
Define Document Sequences for automatic or manual numbering of orders.
This step is required.
Step 12
12. Order Import Sources
Define sources for importing orders into Order Management.
This step is required if you plan on importing orders or returns into Order Management.
Step 13
13. Units of Measure
Define the units of measure in which you supply items.
This step is required.
Step 14
14. Item Information
Define item information, including item attribute controls, categories, and statuses.
This step is required.
Step 15
15. Items
Define the items that you sell, as well as container items.
This step is required.
Step 16
16. Configurations
Define the configurations that you sell.
This step is required if you plan on generating orders or returns for configured items. Several defaulting values are provided.
Step 17
17. Pricing
Define price lists for each combination of item and unit of measure that you sell. Optionally, you can define pricing rules and parameters to add flexibility.
This step is required.
Step 18
18. Customer Classes
Define customer profile classes.
This step is required if you plan on using the functionality surrounding Customer Profiles. Several defaulting values are provided.
Step 19
19. Customers
Define information on your customers.
This step is required.
Step 20
20. Item Cross References
Define item cross references for ordering by customer part number, UPC, or any generic item number.
This step is required if you plan on using the functionality surrounding item cross referencing. Several defaulting values have been provided.
Step 21
21. Sourcing
Define your sourcing rules for scheduling supply chain ATP functions.
This step is optional.
Step 22
22. Order Management Transaction Types (Order and Line Types)
Define Order Management transaction types to classify orders and returns. For each order type, you can assign a default price list, defaulting rules, order lines, return lines, line types, workflow assignments, payment terms, and freight terms.
This step is required.
Note: Previous versions of this user’s guide referred to Order Management Transaction Types as either transaction types or order or line transaction types. In an effort to distinguish between the various Oracle Transaction types available, Order Management Transaction types will now be referred to as either OM Order or Line Transaction Types.
Note: Order Management provides NO seeded OM transaction types. For existing Oracle Order Entry customers, Order Management will update existing Order Types to OM transaction type during the upgrade process.
Step 23
23. Cost of Goods Sold (COGS)
Set up your Cost of Goods Sold Accounting Flex field combination (COGS Account) in Oracle Inventory.
This step is required if you plan on utilizing the functionality surrounding COGS.
Step 24
24. Processing Constraints
Define processing constraints to prevent users from adding updating, deleting, splitting lines, and canceling order or return information beyond certain points in your order cycles. Use the constraints Order Management provides, which prevent data integrity violations, or create your own.
This step is optional. Several default values for processing constraints have been defined.
Step 25
25. Defaulting Rules
Define defaulting rules to determine the source and prioritization for defaulting order information to reduce the amount of information you must enter manually in the Sales Orders window.
This step is optional. Several Defaulting rules and corresponding values for have been defined.
Step 26
26. Credit Checking
Define your credit checking rules.
This step is required if you plan on performing any type of order credit checking.
Step 27
27. Holds
Define automatic holds to apply to orders and returns.
This step is required if you plan on performing automatic hold for orders or returns.
Step 28
28. Attachments
Define standard documents to attach automatically to orders and returns.
This step is optional.
Step 29
29. Freight Charges and Carriers
Define freight charges and freight carriers to specify on orders.
This step is required if you plan on charging customers for freight or additional order charges.
Step 30
30. Shipping
Define shipping parameters in Oracle Shipping Execution.
This step is required.
----------------------
THE ORDER OF SET UP FOR ORDER MANAGEMENT
1. WEARHOUSE
2. PAYMENT TERMS/TERMS
3. SALES PERSON
4. PRICELIST
5. CUSTOMER
6. CUST PO
7. ORDER TYPES
1. Flex fields
Define key and descriptive flex fields to capture additional information about orders and transactions.
This step is required for Key Flex fields, and optional if you plan on using the functionality surrounding Descriptive Flex fields. Several defaulting values are provided.
Step 2
2. Multiple Organizations
Define multiple organizations in Oracle Inventory.
This step is optional.
Step 3
3. Inventory Organizations
Define inventory organizations (warehouses), parameters, sub inventories, and Picking rules in Oracle Inventory. You must define at least one item validation organization and at least one organization that acts as an inventory source for orders fulfilled internally. If you plan to drop ship some orders, you must also define at least one logical organization for receiving purposes. Your item validation organization can be the same as your inventory source or your logical receiving organization, but you cannot use one organization for all three purposes. See Step 5 for setting your item validation organization.
This step is required.
Step 4
4. Profile Options
Define profile options to specify certain implementation parameters, processing options, and system options.
This step is required.
Step 5
5. Parameters
Set your Order Management Parameters to validate items, enable customer relationships, and operating unit defaults.
This step is required.
Step 6
6. Invoicing
Define invoicing information, including payment terms, invoicing and accounting rules, Auto accounting parameters, territories, and invoice sources.
This step is required if you plan on transferring invoicing information to Oracle Receivables. Several defaulting values are provided.
Step 7
7. Salespersons
Define information on your sales representatives.
This step is optional.
Step 8
8. Tax
Define tax features, such as codes, rates, exceptions, and exemptions.
This step is required.
Step 9
9. Quick Codes
Define Quick Codes that provide custom values for many lists of values throughout Order Management.
This step is required if you plan on creating user defined Quick codes for utilization within Order Management. Defaulting values are provided.
Step 10
10. Workflow
Define order and line processing flows to meet different order and line type requirements.
This step is required.
Step 11
11. Document Sequences (Order Numbering)
Define Document Sequences for automatic or manual numbering of orders.
This step is required.
Step 12
12. Order Import Sources
Define sources for importing orders into Order Management.
This step is required if you plan on importing orders or returns into Order Management.
Step 13
13. Units of Measure
Define the units of measure in which you supply items.
This step is required.
Step 14
14. Item Information
Define item information, including item attribute controls, categories, and statuses.
This step is required.
Step 15
15. Items
Define the items that you sell, as well as container items.
This step is required.
Step 16
16. Configurations
Define the configurations that you sell.
This step is required if you plan on generating orders or returns for configured items. Several defaulting values are provided.
Step 17
17. Pricing
Define price lists for each combination of item and unit of measure that you sell. Optionally, you can define pricing rules and parameters to add flexibility.
This step is required.
Step 18
18. Customer Classes
Define customer profile classes.
This step is required if you plan on using the functionality surrounding Customer Profiles. Several defaulting values are provided.
Step 19
19. Customers
Define information on your customers.
This step is required.
Step 20
20. Item Cross References
Define item cross references for ordering by customer part number, UPC, or any generic item number.
This step is required if you plan on using the functionality surrounding item cross referencing. Several defaulting values have been provided.
Step 21
21. Sourcing
Define your sourcing rules for scheduling supply chain ATP functions.
This step is optional.
Step 22
22. Order Management Transaction Types (Order and Line Types)
Define Order Management transaction types to classify orders and returns. For each order type, you can assign a default price list, defaulting rules, order lines, return lines, line types, workflow assignments, payment terms, and freight terms.
This step is required.
Note: Previous versions of this user’s guide referred to Order Management Transaction Types as either transaction types or order or line transaction types. In an effort to distinguish between the various Oracle Transaction types available, Order Management Transaction types will now be referred to as either OM Order or Line Transaction Types.
Note: Order Management provides NO seeded OM transaction types. For existing Oracle Order Entry customers, Order Management will update existing Order Types to OM transaction type during the upgrade process.
Step 23
23. Cost of Goods Sold (COGS)
Set up your Cost of Goods Sold Accounting Flex field combination (COGS Account) in Oracle Inventory.
This step is required if you plan on utilizing the functionality surrounding COGS.
Step 24
24. Processing Constraints
Define processing constraints to prevent users from adding updating, deleting, splitting lines, and canceling order or return information beyond certain points in your order cycles. Use the constraints Order Management provides, which prevent data integrity violations, or create your own.
This step is optional. Several default values for processing constraints have been defined.
Step 25
25. Defaulting Rules
Define defaulting rules to determine the source and prioritization for defaulting order information to reduce the amount of information you must enter manually in the Sales Orders window.
This step is optional. Several Defaulting rules and corresponding values for have been defined.
Step 26
26. Credit Checking
Define your credit checking rules.
This step is required if you plan on performing any type of order credit checking.
Step 27
27. Holds
Define automatic holds to apply to orders and returns.
This step is required if you plan on performing automatic hold for orders or returns.
Step 28
28. Attachments
Define standard documents to attach automatically to orders and returns.
This step is optional.
Step 29
29. Freight Charges and Carriers
Define freight charges and freight carriers to specify on orders.
This step is required if you plan on charging customers for freight or additional order charges.
Step 30
30. Shipping
Define shipping parameters in Oracle Shipping Execution.
This step is required.
----------------------
THE ORDER OF SET UP FOR ORDER MANAGEMENT
1. WEARHOUSE
2. PAYMENT TERMS/TERMS
3. SALES PERSON
4. PRICELIST
5. CUSTOMER
6. CUST PO
7. ORDER TYPES
Flow of Setup in Pricing
Step 1: Perform System Administration Steps
Default: None
Assign users who set up Oracle Advanced Pricing to the Oracle Pricing Manager responsibility.
Step 2: Set Profile Options
Required
You set a value for each user profile option to specify how Oracle Advanced Pricing controls access to and processes data.
Step 3: Set Multi-Currency Profile Options
Default: None
The profile option QP: Multi-Currency Installed enables the multi-currency price list feature. Multi Currency Price Lists enables you to maintain a single price list for multiple currencies.
Step 4: Perform System Sourcing
Default: Predefined Oracle Advanced Pricing record
This step is required if:
■ Your pricing data application source is anything other than Oracle Advanced Pricing.
■ You are integrating Oracle Advanced Pricing with an application other than Oracle Order Management.
Warning: Changing the system source code can severely affect pricing engine behavior.
Step 5: Verify or Create PTE
Default: Order Fulfillment
Required
A Pricing Transaction Entity (PTE) is an ordering structure that has associated Request Types and Source Systems. Request Types and Source Systems in the same PTE share pricing setup data. Additional Source System and Request Types can be added to existing PTE's.
There are very rare instances where it is necessary to create a new PTE. A new PTE needs to be created only if the new request type uses a different ordering structure and a different set of source systems that is not already predefined.
Step 6: Create Qualifier Contexts and Qualifier Attributes
Default: Predefined Oracle Advanced Pricing qualifier contexts
Optional
If you skip this step, users will be able to chose only predefined Oracle qualifier contexts and qualifier attributes for price and modifier eligibility.
Qualifiers provide a highly configurable and flexible method of defining the rules which your business uses to manage pricing. Qualifiers are used by the pricing engine to determine eligibility for price lists and modifiers.
Step 7: Create Pricing Contexts, Pricing Attributes, and Product Attributes
Default: Predefined Oracle Advanced Pricing/Product attribute contexts
Optional
Pricing/Product attributes are a feature of Oracle Advanced Pricing which enables you to define necessary item attributes in order to price or apply a modifier and attributes used in formulas.
If you do not perform this step, users will be able to select only predefined Oracle Advanced Pricing contexts and associated attribute values for benefit options.
Step 8: Perform Attribute Mapping
Default: Predefined Oracle Advanced Pricing attribute mapping rules.
Optional
Step 9: Define Unit of Measure
Default: None
Optional
Units of measure (UOM) are used in Oracle Advanced Pricing to determine the unit value for what thepricing engine is pricing, modifying, returning a benefit, or creating an accrual.
Do this step if you have not installed and set up Oracle Inventory or performed this common-applications setup for another Oracle Product.
Step 10: Define Unit of Measure Conversions
Default: None
Optional
You must define conversion rates between the base unit of measure and other units of measure within a UOM class if you want to price and discount an item in a UOM other than its primary UOM. Oracle Advanced Pricing uses these conversions to automatically convert transaction quantities to the primary pricing unit of measure defined on the price list when pricing cannot find a price in the transaction unit of measure. In addition, all price adjustments, benefits, and charges need to be defined in the same unit of measure as the unit of measure used on the price list.
Step 11: Define Item Category Sets and Item Categories
Default: Seeded structure name of item categories, category set inventory items, and associated default seeded category code combination MISC
Optional
Step 12: Set Up Inventory Organization
Default: None
Required with defaults
You must define at least one item validation organization in Oracle Inventory. This is the organization that items are validated and viewed against when entering items in the Price List and Modifier Setup forms.
Perform this step if you have not installed and set up Oracle Inventory or performed a common-applications setup.
Step 13: Define Item Information
Default: None
Optional
Define the items that you wish to price and discount and assign them to the validation organizations defined in step 10. If you want to define qualifier rules which include the seeded qualifiers Line Volume or Line Weight you must set the
volume or weight attributes of each item as this is used by attribute mapping to derive the transaction line, weight, or volume.
Perform this step if you have not installed and set up Oracle Inventory or performed this common-applications setup for another Oracle product.
Step 14: Create Item Relationships
Default: None
Optional
This step is required if you wish to give item upgrade benefits. You must define a Promotional Upgrade item relationship from the ordered item to the item you wish to give as an upgrade. Define your item relationships for the item validation organization.
Set up promotional upgrade items as follows:
■ The ordered item and the promotional item need to have the same base unit of measure and unit of measure conversions.
■ The modifier unit of measure and the pricing unit of measure on the order line need to be the same.
If those entities are not the same, the substitution can fail.
Step 15: Define Pricing Lookups
Default: Lookup type dependent
Optional
Lookup codes supply many of the lists of values in Oracle Advanced Pricing. Lookup code values are the valid entries that appear in the list of values. They simplify information selection, and ensure that users enter only valid data into
Oracle Advanced Pricing. You can add new lookup values at any time. You can set the Enable flag to No so that it will no longer appear in the list of values, or you can use start and end dates to control when a value will appear in a list.
Step 16: Define Oracle Order Management Lookups
Default: Lookup type dependent
Optional
Perform this step if you have not installed and set up Oracle Order Management or performed a common-applications setup.
Step 17: Define Shipping Lookups
Default: Lookup type dependent
Optional
Perform this step if you have not installed and set up Oracle Order Management or performed a common-applications setup.
Step 18: Set Up Customer Class and Profile Class
Set Up Customer Class
Default: None
Required if you price, give benefits or charge by customer class.
Do this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Set Up Profile Classes
Default: None
Required if you price, give benefits or charge by customer account type.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Step 19: Define Customers and Customer Sites
Default: None
Required if you price, give benefits or charge by customer.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Define Customer Sites
Default: None
Required if you price, give benefits or charge by customer site.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup
Step 20: Define Order Type
Default: None
Required if you price, give benefits or charge by order type.
Step 21: Define Line Type
Default: None
Required if you price, give benefits, or charge by order line type.
Step 22: Define Freight Terms
Default: None
Required if you price, give benefits, including upgrading freight terms, or charge by freight terms.
Step 23: Define Freight Cost Type
Default: None
Required if you price, give benefits or calculate charges using freight cost types.
Step 24: Define Payment Terms
Default: None
This step is required if you price, give benefits, or charge by payment terms.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Step 25: Enable Currencies
Default: All major currencies predefined with Oracle Applications
Optional
The system administrator performs this step. The codes are ISO standard codes for currencies. You must enable the specific currencies you want to use on your price and modifier lists.
Perform this step if you have not installed and set up Oracle General Ledger or performed a common-applications setup.
Step 26: Perform System Sourcing
Default: Predefined Oracle Advanced Pricing record
This step is required if:
■ Your pricing data application source is anything other than Oracle Advanced Pricing.
■ You are integrating Oracle Advanced Pricing with an application other than Oracle Order Management.
Step 27: Create Events and Phases
Default: Seeded Oracle Advanced Pricing phases
Step 28: Set Profile Options
Required
Default: None
Assign users who set up Oracle Advanced Pricing to the Oracle Pricing Manager responsibility.
Step 2: Set Profile Options
Required
You set a value for each user profile option to specify how Oracle Advanced Pricing controls access to and processes data.
Step 3: Set Multi-Currency Profile Options
Default: None
The profile option QP: Multi-Currency Installed enables the multi-currency price list feature. Multi Currency Price Lists enables you to maintain a single price list for multiple currencies.
Step 4: Perform System Sourcing
Default: Predefined Oracle Advanced Pricing record
This step is required if:
■ Your pricing data application source is anything other than Oracle Advanced Pricing.
■ You are integrating Oracle Advanced Pricing with an application other than Oracle Order Management.
Warning: Changing the system source code can severely affect pricing engine behavior.
Step 5: Verify or Create PTE
Default: Order Fulfillment
Required
A Pricing Transaction Entity (PTE) is an ordering structure that has associated Request Types and Source Systems. Request Types and Source Systems in the same PTE share pricing setup data. Additional Source System and Request Types can be added to existing PTE's.
There are very rare instances where it is necessary to create a new PTE. A new PTE needs to be created only if the new request type uses a different ordering structure and a different set of source systems that is not already predefined.
Step 6: Create Qualifier Contexts and Qualifier Attributes
Default: Predefined Oracle Advanced Pricing qualifier contexts
Optional
If you skip this step, users will be able to chose only predefined Oracle qualifier contexts and qualifier attributes for price and modifier eligibility.
Qualifiers provide a highly configurable and flexible method of defining the rules which your business uses to manage pricing. Qualifiers are used by the pricing engine to determine eligibility for price lists and modifiers.
Step 7: Create Pricing Contexts, Pricing Attributes, and Product Attributes
Default: Predefined Oracle Advanced Pricing/Product attribute contexts
Optional
Pricing/Product attributes are a feature of Oracle Advanced Pricing which enables you to define necessary item attributes in order to price or apply a modifier and attributes used in formulas.
If you do not perform this step, users will be able to select only predefined Oracle Advanced Pricing contexts and associated attribute values for benefit options.
Step 8: Perform Attribute Mapping
Default: Predefined Oracle Advanced Pricing attribute mapping rules.
Optional
Step 9: Define Unit of Measure
Default: None
Optional
Units of measure (UOM) are used in Oracle Advanced Pricing to determine the unit value for what thepricing engine is pricing, modifying, returning a benefit, or creating an accrual.
Do this step if you have not installed and set up Oracle Inventory or performed this common-applications setup for another Oracle Product.
Step 10: Define Unit of Measure Conversions
Default: None
Optional
You must define conversion rates between the base unit of measure and other units of measure within a UOM class if you want to price and discount an item in a UOM other than its primary UOM. Oracle Advanced Pricing uses these conversions to automatically convert transaction quantities to the primary pricing unit of measure defined on the price list when pricing cannot find a price in the transaction unit of measure. In addition, all price adjustments, benefits, and charges need to be defined in the same unit of measure as the unit of measure used on the price list.
Step 11: Define Item Category Sets and Item Categories
Default: Seeded structure name of item categories, category set inventory items, and associated default seeded category code combination MISC
Optional
Step 12: Set Up Inventory Organization
Default: None
Required with defaults
You must define at least one item validation organization in Oracle Inventory. This is the organization that items are validated and viewed against when entering items in the Price List and Modifier Setup forms.
Perform this step if you have not installed and set up Oracle Inventory or performed a common-applications setup.
Step 13: Define Item Information
Default: None
Optional
Define the items that you wish to price and discount and assign them to the validation organizations defined in step 10. If you want to define qualifier rules which include the seeded qualifiers Line Volume or Line Weight you must set the
volume or weight attributes of each item as this is used by attribute mapping to derive the transaction line, weight, or volume.
Perform this step if you have not installed and set up Oracle Inventory or performed this common-applications setup for another Oracle product.
Step 14: Create Item Relationships
Default: None
Optional
This step is required if you wish to give item upgrade benefits. You must define a Promotional Upgrade item relationship from the ordered item to the item you wish to give as an upgrade. Define your item relationships for the item validation organization.
Set up promotional upgrade items as follows:
■ The ordered item and the promotional item need to have the same base unit of measure and unit of measure conversions.
■ The modifier unit of measure and the pricing unit of measure on the order line need to be the same.
If those entities are not the same, the substitution can fail.
Step 15: Define Pricing Lookups
Default: Lookup type dependent
Optional
Lookup codes supply many of the lists of values in Oracle Advanced Pricing. Lookup code values are the valid entries that appear in the list of values. They simplify information selection, and ensure that users enter only valid data into
Oracle Advanced Pricing. You can add new lookup values at any time. You can set the Enable flag to No so that it will no longer appear in the list of values, or you can use start and end dates to control when a value will appear in a list.
Step 16: Define Oracle Order Management Lookups
Default: Lookup type dependent
Optional
Perform this step if you have not installed and set up Oracle Order Management or performed a common-applications setup.
Step 17: Define Shipping Lookups
Default: Lookup type dependent
Optional
Perform this step if you have not installed and set up Oracle Order Management or performed a common-applications setup.
Step 18: Set Up Customer Class and Profile Class
Set Up Customer Class
Default: None
Required if you price, give benefits or charge by customer class.
Do this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Set Up Profile Classes
Default: None
Required if you price, give benefits or charge by customer account type.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Step 19: Define Customers and Customer Sites
Default: None
Required if you price, give benefits or charge by customer.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Define Customer Sites
Default: None
Required if you price, give benefits or charge by customer site.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup
Step 20: Define Order Type
Default: None
Required if you price, give benefits or charge by order type.
Step 21: Define Line Type
Default: None
Required if you price, give benefits, or charge by order line type.
Step 22: Define Freight Terms
Default: None
Required if you price, give benefits, including upgrading freight terms, or charge by freight terms.
Step 23: Define Freight Cost Type
Default: None
Required if you price, give benefits or calculate charges using freight cost types.
Step 24: Define Payment Terms
Default: None
This step is required if you price, give benefits, or charge by payment terms.
Perform this step if you have not installed and set up Oracle Receivables or performed a common-applications setup.
Step 25: Enable Currencies
Default: All major currencies predefined with Oracle Applications
Optional
The system administrator performs this step. The codes are ISO standard codes for currencies. You must enable the specific currencies you want to use on your price and modifier lists.
Perform this step if you have not installed and set up Oracle General Ledger or performed a common-applications setup.
Step 26: Perform System Sourcing
Default: Predefined Oracle Advanced Pricing record
This step is required if:
■ Your pricing data application source is anything other than Oracle Advanced Pricing.
■ You are integrating Oracle Advanced Pricing with an application other than Oracle Order Management.
Step 27: Create Events and Phases
Default: Seeded Oracle Advanced Pricing phases
Step 28: Set Profile Options
Required
Attachments in Oracle
Attachments are used to link data such as images and word processing documents to your application data. To link non–structured data such as images, word processing documents, spreadsheets, web pages, or video to more structured application data.
fig(a)
The fig(a) shows the toolbar Attachment icon if it is enabled it will allow you attach data to a particular record.
The fig(b) shows the Attachment window. The Attachments window can be used to view or delete existing attachments, and create new attachments.
fig(b)
As we ca see in the figure there are many fields which need to be filled while creating an attachment.
Category
The category controls which forms can access the document. Each form that enables Attachments must list which document categories it can access. A “Miscellaneous” category will be assigned to each form registered for Attachments to facilitate
“cross–form” visibility of Attachments.
Datatype
An attached document can be:
1. Document Reference: A reference to any type of document stored in a database that is accessed through a Document Management system.
2. Short Text: Text stored in the database containing less than 2000 characters.
3. Long Text: Text stored in the database containing 2000 characters or more.
4. File: A file that requires other server applications to view, such as Microsoft Word or Microsoft Excel, image files such as .JPG files, or other types of files. When you attach or create a File type document, the document is loaded into the database. When a File document is selected, the lower half of the Attachments window displays an ”Open Document” button that invokes a web browser and passes the file to the browser.
5. Web Page: A URL reference to a web page that you can view with your web browser. You must define your web browser in the profile option Web Browser to view a web page attachment.
File or URL
If the document is a file, specify the location of the document. The document will then be loaded into the database. If the document is a web page, specify the web page URL, such as http://www.google.co.in/.
May be Changed
The May be Changed checkbox indicates if you are able to edit the attachment.
Document Block
The document block is the section of the window below the multi–line attachment block. The document block displays the current document in the attachments block, or it displays an Open Document button that opens your document for viewing with a browser.
Adding an Attachment
In order to add an attachment you can either click on the toolbar icon shown in fig(a) or you could go to View->Attachments.
fig(c)
To attach a new document to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Enter a category, a description, and a data type.
4. Type in your document text, or specify a file name or web page URL.
5. Save your work.
To attach an existing document to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Choose the Document Catalog... button. The Document Catalog window opens.
4. Query an existing document.
5. Check one or more documents.
6. Choose the Attach (number of documents) button.
7. Save your work.
Viewing an Attached Document
1. Query a record.
2. Press the Attachment toolbar icon.
3. Move your cursor to the document you want to view. The attachment appears in the document block or you must press the Open Document button to see the document in a browser.
Deleting an Attachment
When you delete an attachment, you merely remove the association between a record and a document. The document itself is not removed from your file system or database. But the option of removing both the record and the attachment is also given.
fig(d)
To delete a record’s attachment:
1. In an application window, query a data record.
2. Choose the Attachment toolbar icon.
3. Move your cursor to the document you want to delete.
4. Choose Delete from the Edit menu.
5. Save your work.
Copying an Attachment from Another Record
Oracle Applications keeps a catalog of documents that have been attached to applications data records so far; therefore, you can take advantage of this catalog if you want to attach an existing document to another data record.
fig(e)
Steps to attach an existing document:
Document Catalog -> Find -> Attach 1
To copy an attachment from another record:
1. In an application window, query the record that you want to attach a document to.
( Don’t query the record that already has the attachment.)
2. Choose the Attachments toolbar icon.
3. Choose Document Catalog....
4. Enter query criteria in the Attached To: field for the application object you want to copy attachments from.
5. Choose Find.
6. Check one or more documents.
7. Choose the Attach (number of documents) button.
8. Save your work.
Uploading and Downloading Attachment Files
Steps to follow:
Select data-type file -> Browse -> OK/Cancel
fig(f)
To attach an existing file to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Use the List of Values to choose a Category.
4. Optionally enter a Description.
5. Use the List of Values to choose a Data Type.
6. Use the Tab key to open the Upload window in a separate browser window.
7. You can either enter the path for the file you want to attach or use the Browse button to choose a file.
8. Choose OK and follow the instructions in the browser window.
9. Click on Yes in the Decision window as seen in the figure fig(g)
fig(g)
To open an uploaded attachment:
Choose the Open Document ... button
Attachments as seen from backend ( In Order-Management)
When you attach a document to an Order it can be done so at the header level or the line level. The tables/views which are related are
FND_ATTACHED_DOCS_FORM_VL
This is a view which has all the details pertaining to an attached document. It gets populated by the following tables :
FND_DOCUMENT_DATATYPES,
FND_DOCUMENT_ENTITIES_TL,
FND_DOCUMENTS_TL,
FND_DOCUMENTS,
FND_DOCUMENT_CATEGORIES_TL,
FND_DOC_CATEGORY_USAGES,
FND_ATTACHMENT_FUNCTIONS,
The important columns of the table are:
1. SEQ_NUM: Gives the sequence of the attachment it follows like 10,20,30..
2. CATEGORY_DESCRIPTION: Gives the description of the category.
3. DOCUMENT_DESCRIPTION: Gives the unique description of the attachment.
4. FILE_NAME: Name of the attached file.
5. ATTACHED_DOCUMENT_ID: Identifier.
6. DOCUMENT_ID: Identifier.
7. ENTITY_NAME: 'OE_ORDER_HEADERS' or 'OE_ORDER_LINES'
6. PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level attachment.
7. CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S sequence
8. FUNCTION_NAME: Name of the function used while attaching
9. FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM, depending on the value of FUNCTION_TYPE.
FND_ATTACHED_DOCUMENTS
This table stores information relating a document to an application entity. Its important columns include:
ATTACHED_DOCUMENT_ID: Identifier.
DOCUMENT_ID: Document identifier
PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level
attachment.
ENTITY_NAME: 'OE_ORDER_HEADERS' or 'OE_ORDER_LINES'
FND_ATTACHMENT_FUNCTIONS
This table stores information about those forms and form functions for which the attach-ment feature is enabled.
ATTACHMENT_FUNCTION_ID: Attachment function identifier
FUNCTION_NAME: Name of the function used while attaching
FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM,
depending on the value of FUNCTION_TYPE.
FND_DOCUMENTS
This table stores language-independent information about a document
DOCUMENT_ID : Document identifier
CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S
sequence
FND_LOBS:
FND_LOBS stores information about all LOBs managed by the Generic File Manager(GFM).Each row includes the file identifier, name, content-type, and actual data.
FILE_ID: Identifier that uniquely identifies the file
FILE_NAME: User's name for the file as provided during the uploading process
FILE_CONTENT_TYPE:
FILE_DATA: The uploaded data itself, which is a binary LOB
OE_ATCHMT_UTIL
This is an API which can be used to add, copy and delete attachments it has procedures
Add_Attachment: Can be used to attach a new attachment to an order
It’s parameters are:
P_API_VERSION IN (Mandatory)
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_DOCUMENT_DESC IN default null,
P_DOCUMENT_TEXT IN default null,
P_CATEGORY_ID IN default null,
P_DOCUMENT_ID IN default null,
X_ATTACHMENT_ID OUT
X_RETURN_STATUS OUT
X_MSG_COUNT OUT
X_MSG_DATA OUT
Copy_Attachments : Can be used to attach an existing attachment to an
Order.
It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_FROM_ENTITY_ID IN (Mandatory)
P_TO_ENTITY_ID IN (Mandatory)
P_MANUAL_ATTACHMENTS_ONLY IN default 'N'
X_RETURN_STATUS OUT
Delete_Attachments: Can be used to delete an attachment of an order
It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
X_RETURN_STATUS OUT
Apply_Automatic_Attachments :
It’s parameters are:
P_INIT_MSG_LIST IN default fnd_api.g_false
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_IS_USER_ACTION IN default 'Y'
X_ATTACHMENT_COUNT OUT
X_RETURN_STATUS OUT
X_MSG_COUNT OUT
X_MSG_DATA OUT
fig(a)
The fig(a) shows the toolbar Attachment icon if it is enabled it will allow you attach data to a particular record.
The fig(b) shows the Attachment window. The Attachments window can be used to view or delete existing attachments, and create new attachments.
fig(b)
As we ca see in the figure there are many fields which need to be filled while creating an attachment.
Category
The category controls which forms can access the document. Each form that enables Attachments must list which document categories it can access. A “Miscellaneous” category will be assigned to each form registered for Attachments to facilitate
“cross–form” visibility of Attachments.
Datatype
An attached document can be:
1. Document Reference: A reference to any type of document stored in a database that is accessed through a Document Management system.
2. Short Text: Text stored in the database containing less than 2000 characters.
3. Long Text: Text stored in the database containing 2000 characters or more.
4. File: A file that requires other server applications to view, such as Microsoft Word or Microsoft Excel, image files such as .JPG files, or other types of files. When you attach or create a File type document, the document is loaded into the database. When a File document is selected, the lower half of the Attachments window displays an ”Open Document” button that invokes a web browser and passes the file to the browser.
5. Web Page: A URL reference to a web page that you can view with your web browser. You must define your web browser in the profile option Web Browser to view a web page attachment.
File or URL
If the document is a file, specify the location of the document. The document will then be loaded into the database. If the document is a web page, specify the web page URL, such as http://www.google.co.in/.
May be Changed
The May be Changed checkbox indicates if you are able to edit the attachment.
Document Block
The document block is the section of the window below the multi–line attachment block. The document block displays the current document in the attachments block, or it displays an Open Document button that opens your document for viewing with a browser.
Adding an Attachment
In order to add an attachment you can either click on the toolbar icon shown in fig(a) or you could go to View->Attachments.
fig(c)
To attach a new document to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Enter a category, a description, and a data type.
4. Type in your document text, or specify a file name or web page URL.
5. Save your work.
To attach an existing document to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Choose the Document Catalog... button. The Document Catalog window opens.
4. Query an existing document.
5. Check one or more documents.
6. Choose the Attach (number of documents) button.
7. Save your work.
Viewing an Attached Document
1. Query a record.
2. Press the Attachment toolbar icon.
3. Move your cursor to the document you want to view. The attachment appears in the document block or you must press the Open Document button to see the document in a browser.
Deleting an Attachment
When you delete an attachment, you merely remove the association between a record and a document. The document itself is not removed from your file system or database. But the option of removing both the record and the attachment is also given.
fig(d)
To delete a record’s attachment:
1. In an application window, query a data record.
2. Choose the Attachment toolbar icon.
3. Move your cursor to the document you want to delete.
4. Choose Delete from the Edit menu.
5. Save your work.
Copying an Attachment from Another Record
Oracle Applications keeps a catalog of documents that have been attached to applications data records so far; therefore, you can take advantage of this catalog if you want to attach an existing document to another data record.
fig(e)
Steps to attach an existing document:
Document Catalog -> Find -> Attach 1
To copy an attachment from another record:
1. In an application window, query the record that you want to attach a document to.
( Don’t query the record that already has the attachment.)
2. Choose the Attachments toolbar icon.
3. Choose Document Catalog....
4. Enter query criteria in the Attached To: field for the application object you want to copy attachments from.
5. Choose Find.
6. Check one or more documents.
7. Choose the Attach (number of documents) button.
8. Save your work.
Uploading and Downloading Attachment Files
Steps to follow:
Select data-type file -> Browse -> OK/Cancel
fig(f)
To attach an existing file to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Use the List of Values to choose a Category.
4. Optionally enter a Description.
5. Use the List of Values to choose a Data Type.
6. Use the Tab key to open the Upload window in a separate browser window.
7. You can either enter the path for the file you want to attach or use the Browse button to choose a file.
8. Choose OK and follow the instructions in the browser window.
9. Click on Yes in the Decision window as seen in the figure fig(g)
fig(g)
To open an uploaded attachment:
Choose the Open Document ... button
Attachments as seen from backend ( In Order-Management)
When you attach a document to an Order it can be done so at the header level or the line level. The tables/views which are related are
FND_ATTACHED_DOCS_FORM_VL
This is a view which has all the details pertaining to an attached document. It gets populated by the following tables :
FND_DOCUMENT_DATATYPES,
FND_DOCUMENT_ENTITIES_TL,
FND_DOCUMENTS_TL,
FND_DOCUMENTS,
FND_DOCUMENT_CATEGORIES_TL,
FND_DOC_CATEGORY_USAGES,
FND_ATTACHMENT_FUNCTIONS,
The important columns of the table are:
1. SEQ_NUM: Gives the sequence of the attachment it follows like 10,20,30..
2. CATEGORY_DESCRIPTION: Gives the description of the category.
3. DOCUMENT_DESCRIPTION: Gives the unique description of the attachment.
4. FILE_NAME: Name of the attached file.
5. ATTACHED_DOCUMENT_ID: Identifier.
6. DOCUMENT_ID: Identifier.
7. ENTITY_NAME: 'OE_ORDER_HEADERS' or 'OE_ORDER_LINES'
6. PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level attachment.
7. CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S sequence
8. FUNCTION_NAME: Name of the function used while attaching
9. FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM, depending on the value of FUNCTION_TYPE.
FND_ATTACHED_DOCUMENTS
This table stores information relating a document to an application entity. Its important columns include:
ATTACHED_DOCUMENT_ID: Identifier.
DOCUMENT_ID: Document identifier
PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level
attachment.
ENTITY_NAME: 'OE_ORDER_HEADERS' or 'OE_ORDER_LINES'
FND_ATTACHMENT_FUNCTIONS
This table stores information about those forms and form functions for which the attach-ment feature is enabled.
ATTACHMENT_FUNCTION_ID: Attachment function identifier
FUNCTION_NAME: Name of the function used while attaching
FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM,
depending on the value of FUNCTION_TYPE.
FND_DOCUMENTS
This table stores language-independent information about a document
DOCUMENT_ID : Document identifier
CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S
sequence
FND_LOBS:
FND_LOBS stores information about all LOBs managed by the Generic File Manager(GFM).Each row includes the file identifier, name, content-type, and actual data.
FILE_ID: Identifier that uniquely identifies the file
FILE_NAME: User's name for the file as provided during the uploading process
FILE_CONTENT_TYPE:
FILE_DATA: The uploaded data itself, which is a binary LOB
OE_ATCHMT_UTIL
This is an API which can be used to add, copy and delete attachments it has procedures
Add_Attachment: Can be used to attach a new attachment to an order
It’s parameters are:
P_API_VERSION IN (Mandatory)
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_DOCUMENT_DESC IN default null,
P_DOCUMENT_TEXT IN default null,
P_CATEGORY_ID IN default null,
P_DOCUMENT_ID IN default null,
X_ATTACHMENT_ID OUT
X_RETURN_STATUS OUT
X_MSG_COUNT OUT
X_MSG_DATA OUT
Copy_Attachments : Can be used to attach an existing attachment to an
Order.
It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_FROM_ENTITY_ID IN (Mandatory)
P_TO_ENTITY_ID IN (Mandatory)
P_MANUAL_ATTACHMENTS_ONLY IN default 'N'
X_RETURN_STATUS OUT
Delete_Attachments: Can be used to delete an attachment of an order
It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
X_RETURN_STATUS OUT
Apply_Automatic_Attachments :
It’s parameters are:
P_INIT_MSG_LIST IN default fnd_api.g_false
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_IS_USER_ACTION IN default 'Y'
X_ATTACHMENT_COUNT OUT
X_RETURN_STATUS OUT
X_MSG_COUNT OUT
X_MSG_DATA OUT
Important table information in Oracle
CONCURRENT MANAGER
FND_CONCURRENT_REQUEST
FND_CONC_REQ_SUMMARY_V
FND_CONCURRENT_PROGRAMS_VL
FND_REQUEST_GROUP_UNITS
FND_REQUEST_GROUPS
GL Tables
GL_CODE_COMBINATIONS
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
ORACLE FINANCIALS – PAYABLES - INVOICES
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
ORACLE FINANCIALS – RECEIVABLES - CUSTOMERS
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE
ORACLE FINANCIALS – RECEIVABLES - INVOICES
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
ORACLE FINANCIALS – RECEIVABLES – LOCK BOX
AR_PAYMENTS_INTERFACE_ALL
ORACLE FINANCIALS – RECEIVABLES - TAX
AR_TAX_INTERFACE
ORACLE FINANCIALS - CASH MANAGEMENT – BANK STATEMENTS
CE_STATEMENT_HEADERS_INT_ALL
CE_STATEMENT_LINES_INTERFACE
ORACLE FINANCIALS - FIXED ASSETS
FA_INV_INTERFACE
ORACLE FINANCIALS - GENERAL LEDGER – JOURNAL ENTRY
GL_INTERFACE
ORACLE MANUFACTURING – COST MANAGEMENT
CST_PC_ITEM_COST_INTERFACE
CST_PC_COST_DET_INTERFACE
ORACLE MANUFACTURING - INVENTORY
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
ORACLE MANUFACTURING – PURCHASING – PURCHASE ORDERS
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
ORACLE MANUFACTURING – PURCHASING - RECEIVING
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
ORACLE MANUFACTURING - ORDER ENTRY – SALES ORDERS
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE
ORACLE MANUFACTURING - MASTER SCHEDULING/MRP
MRP_FORECAST_INTERFACE
MRP_SCHEDULE_INTERFACE
ORACLE MANUFACTURING - SHOP FLOOR MANAGEMENT
WSM_LOT_JOB_INTERFACE
WSM_STARTING_LOTS_INTERFACE
WSM_LOT_MOVE_TXN_INTERFACE
ORACLE MANUFACTURING - QUALITY
QA_RESULTS_INTERFACE
ORACLE MANUFACTURING - WORK IN PROCESS
WIP_MOVE_TXN_INTERFACE
CST_COMP_SNAP_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE
VERSION INFORMATION
V$VERSION
DETAILED PATCH INFORMATION
APPS.FND_PRODUCT_INSTALLATIONS
APPS.FND_APPLICATION_ALL_VIEW
GENERIC PATCH INFORMATION
AD_APPLIED_PATCHES
TABLE IS REFERENCED BY
USER_CONSTRAINTS
FIND THE # OF COLUMNS
INFORMATION_SCHEMA
USER_TAB_COLUMNS
SYSCOLUMNS
DATABASE NAME
GLOBAL_NAME
V$INSTANCE
V$DATABASE
ALL TABLES
ALL_OBJECTS
PL/SQL
USER_SOURCE
USER_OBJECTS
ALL_SOURCE
USER_SOURCE
DB LINKS
ALL_DB_LINKS
RA_ TABLES MOSTLY INVOICE RELATED .
AR_ TABLES MOSTLY RELATED TO RECEIPTS.
FND_CONCURRENT_REQUEST
FND_CONC_REQ_SUMMARY_V
FND_CONCURRENT_PROGRAMS_VL
FND_REQUEST_GROUP_UNITS
FND_REQUEST_GROUPS
GL Tables
GL_CODE_COMBINATIONS
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
ORACLE FINANCIALS – PAYABLES - INVOICES
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
ORACLE FINANCIALS – RECEIVABLES - CUSTOMERS
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE
ORACLE FINANCIALS – RECEIVABLES - INVOICES
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
ORACLE FINANCIALS – RECEIVABLES – LOCK BOX
AR_PAYMENTS_INTERFACE_ALL
ORACLE FINANCIALS – RECEIVABLES - TAX
AR_TAX_INTERFACE
ORACLE FINANCIALS - CASH MANAGEMENT – BANK STATEMENTS
CE_STATEMENT_HEADERS_INT_ALL
CE_STATEMENT_LINES_INTERFACE
ORACLE FINANCIALS - FIXED ASSETS
FA_INV_INTERFACE
ORACLE FINANCIALS - GENERAL LEDGER – JOURNAL ENTRY
GL_INTERFACE
ORACLE MANUFACTURING – COST MANAGEMENT
CST_PC_ITEM_COST_INTERFACE
CST_PC_COST_DET_INTERFACE
ORACLE MANUFACTURING - INVENTORY
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
ORACLE MANUFACTURING – PURCHASING – PURCHASE ORDERS
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
ORACLE MANUFACTURING – PURCHASING - RECEIVING
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
ORACLE MANUFACTURING - ORDER ENTRY – SALES ORDERS
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE
ORACLE MANUFACTURING - MASTER SCHEDULING/MRP
MRP_FORECAST_INTERFACE
MRP_SCHEDULE_INTERFACE
ORACLE MANUFACTURING - SHOP FLOOR MANAGEMENT
WSM_LOT_JOB_INTERFACE
WSM_STARTING_LOTS_INTERFACE
WSM_LOT_MOVE_TXN_INTERFACE
ORACLE MANUFACTURING - QUALITY
QA_RESULTS_INTERFACE
ORACLE MANUFACTURING - WORK IN PROCESS
WIP_MOVE_TXN_INTERFACE
CST_COMP_SNAP_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE
VERSION INFORMATION
V$VERSION
DETAILED PATCH INFORMATION
APPS.FND_PRODUCT_INSTALLATIONS
APPS.FND_APPLICATION_ALL_VIEW
GENERIC PATCH INFORMATION
AD_APPLIED_PATCHES
TABLE IS REFERENCED BY
USER_CONSTRAINTS
FIND THE # OF COLUMNS
INFORMATION_SCHEMA
USER_TAB_COLUMNS
SYSCOLUMNS
DATABASE NAME
GLOBAL_NAME
V$INSTANCE
V$DATABASE
ALL TABLES
ALL_OBJECTS
PL/SQL
USER_SOURCE
USER_OBJECTS
ALL_SOURCE
USER_SOURCE
DB LINKS
ALL_DB_LINKS
RA_ TABLES MOSTLY INVOICE RELATED .
AR_ TABLES MOSTLY RELATED TO RECEIPTS.
Script to print the Oracle Apps Version Number
SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')
Script to display status of all the Concurrent Managers
SELECT DISTINCT concurrent_process_id "Concurrent Process ID",
pid "System Process ID", os_process_id "Oracle Process ID",
q.concurrent_queue_name "Concurrent Manager Name",
p.process_status_code "Status of Concurrent Manager",
TO_CHAR
(p.process_start_date,
'MM-DD-YYYY HH:MI:SSAM'
) "Concurrent Manager Started at"
FROM fnd_concurrent_processes p,
fnd_concurrent_queues q,
fnd_v$process
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND spid = os_process_id
AND process_status_code NOT IN ('K', 'S')
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name
pid "System Process ID", os_process_id "Oracle Process ID",
q.concurrent_queue_name "Concurrent Manager Name",
p.process_status_code "Status of Concurrent Manager",
TO_CHAR
(p.process_start_date,
'MM-DD-YYYY HH:MI:SSAM'
) "Concurrent Manager Started at"
FROM fnd_concurrent_processes p,
fnd_concurrent_queues q,
fnd_v$process
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND spid = os_process_id
AND process_status_code NOT IN ('K', 'S')
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name
Commit every X records for UPDATE/DELETE in a loop
Generally Huge updates/deletes causes rollback segment or data-files related errors.
Below is the code which is used to comming after every 1000 rows of update/delete to over come the problems. Place the code in below the update or delete statement in the procedure:
if mod(i, 1000)
commit;
dbms_output.put_line('Commit issued for rows up to: 'c1%rowcount);
end if;
Below is the code which is used to comming after every 1000 rows of update/delete to over come the problems. Place the code in below the update or delete statement in the procedure:
if mod(i, 1000)
commit;
dbms_output.put_line('Commit issued for rows up to: 'c1%rowcount);
end if;
Script to Encrypt and Decrypt using PL/SQL
CREATE OR REPLACE PACKAGE XX_PASSWORD AS
function encrypt(i_password varchar2) return varchar2;
function decrypt(i_password varchar2) return varchar2;
END XX_PASSWORD;
/
show errors
CREATE OR REPLACE PACKAGE BODY XX_PASSWORD AS
-- key must be exactly 8 bytes long
c_encrypt_key varchar2(8) := 'key45678';
function encrypt (i_password varchar2) return varchar2 is
v_encrypted_val varchar2(38);
v_data varchar2(38);
begin
-- Input data must have a length divisible by eight
v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
input_string => v_data,
key_string => c_encrypt_key,
encrypted_string => v_encrypted_val);
return v_encrypted_val;
end encrypt;
function decrypt (i_password varchar2) return varchar2 is
v_decrypted_val varchar2(38);
begin
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
input_string => i_password,
key_string => c_encrypt_key,
decrypted_string => v_decrypted_val);
return v_decrypted_val;
end decrypt;
end XX_PASSWORD;
/
show errors
-- Test if it is working...
select xx_password.encrypt('PASSWORD1') from dual;
select xx_password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select xx_password.encrypt('PSW2') from dual;
select xx_password.decrypt(app_password.encrypt('PSW2')) from dual;
function encrypt(i_password varchar2) return varchar2;
function decrypt(i_password varchar2) return varchar2;
END XX_PASSWORD;
/
show errors
CREATE OR REPLACE PACKAGE BODY XX_PASSWORD AS
-- key must be exactly 8 bytes long
c_encrypt_key varchar2(8) := 'key45678';
function encrypt (i_password varchar2) return varchar2 is
v_encrypted_val varchar2(38);
v_data varchar2(38);
begin
-- Input data must have a length divisible by eight
v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
input_string => v_data,
key_string => c_encrypt_key,
encrypted_string => v_encrypted_val);
return v_encrypted_val;
end encrypt;
function decrypt (i_password varchar2) return varchar2 is
v_decrypted_val varchar2(38);
begin
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
input_string => i_password,
key_string => c_encrypt_key,
decrypted_string => v_decrypted_val);
return v_decrypted_val;
end decrypt;
end XX_PASSWORD;
/
show errors
-- Test if it is working...
select xx_password.encrypt('PASSWORD1') from dual;
select xx_password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select xx_password.encrypt('PSW2') from dual;
select xx_password.decrypt(app_password.encrypt('PSW2')) from dual;
Monday, September 1, 2008
Apps 11i Keys shortcut
F4 Exit
F5 Clear Field
F6 Clear Record
F7 Clear Block
F8 Clear Form
F11 Query Enter
F12 Count Query
Ctrl + S Save
Ctrl + L List of Values
Ctrl + F11 Query Run
Ctrl + E Edit
Ctrl + Up Delete Record
Ctrl + Down Insert Record
Ctrl + P Print
Ctrl + U Update Record
Ctrl + B Block Menu
Ctrl + K Display list of Keys
Shift + F5 Duplicate Field
Shift + F6 Duplicate Record
Shift + F8 Next Set of Records (Same as Page Down)
Shift + Page Down Next Block
Shift + Tab Previous Field
Shift + Page Up Previous Block
Shift + Ctrl + E Display Error
Page Down Scroll Down (Same as Shift + F8)
Page Up Scroll Up
Tab Next Field
Down Arrow Next Record
Up Arrow Previous Record
Return Return
F5 Clear Field
F6 Clear Record
F7 Clear Block
F8 Clear Form
F11 Query Enter
F12 Count Query
Ctrl + S Save
Ctrl + L List of Values
Ctrl + F11 Query Run
Ctrl + E Edit
Ctrl + Up Delete Record
Ctrl + Down Insert Record
Ctrl + P Print
Ctrl + U Update Record
Ctrl + B Block Menu
Ctrl + K Display list of Keys
Shift + F5 Duplicate Field
Shift + F6 Duplicate Record
Shift + F8 Next Set of Records (Same as Page Down)
Shift + Page Down Next Block
Shift + Tab Previous Field
Shift + Page Up Previous Block
Shift + Ctrl + E Display Error
Page Down Scroll Down (Same as Shift + F8)
Page Up Scroll Up
Tab Next Field
Down Arrow Next Record
Up Arrow Previous Record
Return Return
Subscribe to:
Posts (Atom)