Monday, May 9, 2011
Common Errors in Oracle Reports
REP-1433: 'r_entered_tot_dr_dspformula': Error running user exit 'FND'.
REP-1249: Column 'CF_1' has no PL/SQL formula.
REP-1203: Field 'F_TOT_CR1' references invalid column.
REP-1517: Column 'B_ENTERED_TOT_DR_DSP' references column 'Currency_Code', which has incompatible frequency.
REP-1401: 'r_entered_tot_cr_dspformula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error
REP-1213: Field 'F_BAT_22' references column 'B_TOT_DR_DSP' at a frequency below its group.
REP-1814: Report cannot be formatted. Object 'horizontally' can never fit within 'F_BATCH_POSTED_DATE1'
REP-1212: Object 'Margin' is not fully enclosed by its enclosing object 'SET_OF_BOOKS_NAME'.
Thursday, April 21, 2011
R12 installation Steps - Video
http://www.youtube.com/watch?v=Ou-PM3YSU9o&feature=player_detailpage
Unix Commands
Files
ls --- lists your files
ls -l --- lists your files in 'long format', which contains lots of useful information, ls -a --- lists all files, including the ones whose filenames begin in a dot,
more filename --- shows the first part of a file
emacs filename --- is an editor that lets you create and edit a file
mv filename1 filename2 --- moves a file
cp filename1 filename2 --- copies a file
rm filename --- removes a file.
diff filename1 filename2 --- compares files, and shows where they differ
wc filename --- tells you how many lines, words, and characters there are in a file
chmod options filename --- lets you change the read, write, and execute permissions on your files.
File Compression
gzip filename --- compresses files, so that they take up much less space.
gunzip filename --- uncompresses files compressed by gzip.
printing
lpr filename --- print. Use the -P option to specify the printer name if you want to use a printer other than your default printer.
lpq --- check out the printer queue, e.g. to get the number needed for removal, or to see how many other files will be printed before yours will come out
lprm jobnumber --- remove something from the printer queue. You can find the job number by using lpq. Theoretically you also have to specify a printer name, but this isn't necessary as long as you use your default printer in the department.
genscript --- converts plain text files into postscript for printing, and gives you some options for formatting.
dvips filename --- print .dvi files (i.e. files produced by LaTeX). You can use dviselect to print only selected pages.
Directories
mkdir dirname --- make a new directory
cd dirname --- change directory. You basically 'go' to another directory, and you will
Wednesday, April 20, 2011
Error with materialized views
ORA-12014: 'XXX.CUSTOM_TABLE' does not contain any primary key constraint.
Solution - Create view with ROW ID option should be used with the refresh option
Tuesday, April 19, 2011
ERP??
Some of the bigger players in the ERP outsourcing market are SAP, Peoplesoft, and J. D. Edwards. New comers include Oracle, IBM, and Microsoft.
Now we know what is ERP and ERP softwares availables. Most of the top, mid level companies use oracle as SAP is costly comparatively.
What Oracle provides ??
Oracle's Complete, Open and Integrated applications strategy helps customers transform their business—creating value and reducing cost and risk through: The most complete portfolio of core business and industry applications The most open application offerings and architecture The most integrated applications, business intelligence and infrastructure solutions
Sunday, October 18, 2009
Saturday, October 17, 2009
Oracle Discoverer
Business intelligence is the ability to analyze data to answer business questions and predict future trends.
Oracle Business Intelligence Discoverer is a great business intelligence tool because it enables users to analyze data in an ad hoc way. Instead of relying on IT specialists to pre-define queries and reports, Discoverer users can choose the data to analyze and can continue manipulating results until they have the necessary information to take business decisions. Oracle Business Intelligence Discoverer also enables users to share the results of their data analysis with their colleagues in different formats (including charts and Excel spreadsheets).
How does Discoverer work?
Oracle Discoverer is an intuitive ad hoc reporting tool. It enables a developer or knowledgeable end user to create reports with impressive formatting and attractive exporting and publishing functionality. Oracle Discoverer empowers your user population by placing business and decision critical data at their fingertips
Users' requests for information from the database are in the form of worksheets.
When a user creates or opens a worksheet, Discoverer converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively) sends the SQL statements to the database displays the result set that is returned from the database
In the case of Discoverer Plus, Discoverer Desktop and Discoverer Viewer, the SQL statements are routed to the database via Discoverer processes running on an application server machine
Purchasing Document Open Interface (PDOI)
After validating the price/sales catalog information
1. PDOI program converts the information, including price break information, in the interface tables into blanket purchase agreements.
2. You can also choose to update your item master and apply sourcing rules and release generation methods to the imported item for blanket purchase agreements.
3. Blanket purchase agreements can also be replaced with the latest price/sales catalog information when your supplier sends a replacement catalog, or updated when the supplier sends an updated catalog.
How to import data - One way to import price/sales catalog data is through Electronic Data Interchange (EDI). PDOI supports the EDI transmissions of the price/sales catalogs (ANSI X12 832 or EDIFACT PRICAT).
FUNCTIONAL OVERVIEW - There are different ways that a supplier sends the price/sales catalog to its client. They may use EDI’s, interface, mail etc., The main aim with supplier information is to load the interface tables. we can load them manually or depending on the interface the information is coming through. Flow of price /sales catalog from supplier to client:

supplier or trading partner, to Oracle e-Commerce Gateway, to the Purchasing
Documents Open Interface, and finally into Purchasing.
Interface tables - PO_HEADERS_INTERFACE and PO_LINES_INTERFACE
How the tables are loaded
- Can be done manually
- e-Commerce Gateway ( supplier provides information as flat file using EDI translator according to EDI interface file definitions, then , the EDI Catalog Inbound program loads the information into the interface tables.
How it works - The EDI inbound program and the PDOI program are run as a request set when you choose Submit Request in the EDI import programs window. The EDI inbound program loads the interface tables. Later on, PDOI program validates the data and loads the validated data into Purchasing.
The PDOI program receives the data, derives and defaults any missing data, and validates the data. If no errors are found in the submission process, the data in the PDOI tables is loaded into the PO_HEADERS, PO_LINES, and PO_LINE_LOCATIONS tables in Purchasing to create the blanket purchase agreement
How to check status – first make note of the Request ID number and then select View My Requests from the Help menu.
If PDOI program finds any error in the information, the record identification number and details of error are send to PO_INTERFACE_ERRORS table.
RECORD AND ERROR PROCESSING
Record Processing - PDOI processes a record from parent PO_HEADERS_INTERFACE table then to child PO_LINES_INTERFACE table before going on to the next record. Partial documents are accepted as PDOI saves or errors out line by line.
Error Processing –
1. If an error is found in a header, none of its lines will be processed and sets the column PROCESS_CODE to REJECTED in the PO_HEADERS_INTERFACE table.
2. If no errors are found, then the PROCESS_CODE is set as ACCEPTED.
3. If the supplier sends an updated price/sales catalog, the PDOI sets the PROCESS_CODE column to NOTIFIED for those lines with prices that exceed your price tolerance.
Friday, October 16, 2009
Order to Cash
How to create Purchase Order

1ST STEP – CREATE AND APPROVE PURCHASE ORDER
Create your purchase order by entering header and line information. You can specify the shipment dates and quantities for each item, and how the shipment should be distributed across different accounts. Submit your order for approval when it is complete.
2ND STEP – COMMUNICATE PURCHASE ORDER TO SUPPLIER
Once your purchase order has been approved, send it to the supplier by Electronic Data Interchange (EDI), the Web, printed hard copy, or fax. If you communicate your purchase order by the Web, Oracle Supplier Management Portal allows your supplier to view and download the purchase order into their system.
3RD STEP – ACKNOWLEDGE PURCHASE ORDER
Enter the supplier's acceptance of the purchase order. Use the Purchase Order Summary window to query your purchase order. Choose Acceptances from the Tools menu and enter the supplier's acceptance of the purchase order.
4TH STEP – CHANGE AND REAPPROVE PURCHASE ORDER
Make changes to a purchase order, even after approval, by querying on the purchase order number and then making the necessary changes. Through the use of Oracle Workflow, Oracle Purchasing can then route the changed purchase order through a different approval hierarchy if necessary.
5TH STEP – COMMUNICATES PURCHASE ORDER CHANGES TO SUPPLIER
Once your changed order has been approved, communicate the change to the supplier by Electronic Data Interchange (EDI), the Web, printed hard copy, or fax. If you communicate your purchase order by the Web, Oracle Supplier Management Portal allows your supplier to download the purchase order into their system.
6TH STEP – ACKNOWLEDGE PURCHASE ORDER CHANGES
Enter your supplier's acceptance of the purchase order changes. Use the Purchase Order Summary window to query your purchase order. Choose Acceptances from the Tools menu and enter the supplier's acceptance of the purchase order changes.
Purchase to Pay
Thursday, October 15, 2009
Why EDI Evolved ?
Now It clears how much of information needs to be transferred and tracked to run the business, which in reality it is called trading partner. This simple scenario requires the exchange of various documents between several business partners at different times.
All this electronic data was exchanged using floppy disks and other secondary devices. So, ANSI committee was formed to define the standards. Hence the electronic exchange of business documents in a standard format gave birth to what is known as EDI, today the business world had accepted as one of most important entity.
Wednesday, October 14, 2009
Multi Org
1. You can use a single installation of the oracle applications to support any number of business organizations using multiple set of books
2. You can define different organization models.
3. You can have any number of legal entities within a single installation of the applications.
4. You can implement organization security by responsibility so that information is available only to users linked to an organization;
5. You can automatically make intercompany transactions across set of books and legal entities.
Data Migration, Conversion, and Interfaces
Data conversion can be defined as a process of converting data from one structural form to another to suit the requirements of the system to which it is migrated. It is a process where existing data from the client’s old system is extracted, cleansed, formatted, and installed into a new system. These can be manual or automated. The big difference is that these are One-time only process that requires extensive testing and preparation. They must be executed and performed before a system goes into production.
Interfaces are programs for connection between Two Systems In Order To Synchronize the Data. They can be Manual, Batch or Real-Time. Used Repeatedly and Should Therefore Be Designed and Constructed In the Most Efficient Manner Possible. These can Be Triggered by an Event (Such As Running A Concurrent Program) Or It Can Be Scheduled To Run At A Certain Time. Can Be Very Costly To Construct And Maintain.
An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables. A typical inbound interface would follow these steps: Extract data from legacy system into a flat file. Use SQL*Loader or equivalent tool to upload information into a temporary table. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.
Outbound Interfaces
An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file). A typical outbound interface would follow these steps:- Write a PL/SQL program to extract data from Oracle base tables into a flat file.- Use a custom program to read that data and post it into the legacy system
Monday, October 12, 2009
FND_FILE
Internally its uses the oracle supplied database package UTL_FILE
utl_file_dir parameter must be set to include the log and out directories for the applications that will run concurrent requests using FND_FILE
Database owner must have write privileges on those directories
Take a note that FND_FILE supports a maximum buffer line size of 32K for both log and output files.
FND_FILE.PUT - Useful for writing text to a file without a new line character. Multiple calls will concatenate the text. Does not include new line character
FND_FILE.PUT_LINE This is used to write a line of text to a file followed by a new line character
FND_FILE.NEW_LINE FND_FILE.NEW_LINE( which IN NUMBER, LINES IN NATURAL:= 1) Writes line terminators (new line characters) to a file.Eg. FND_FILE.NEW_LINE(FND_FILE.LOG,2)
FND_File.PUT_NAMES This is used to sets temporary log and out file names and directory, which normally used during test and debug phase.
FND_FILE.CLOSE This will closes open files.
This is normally used only during test and debug phase
Sunday, October 11, 2009
Friday, October 9, 2009
Flex Fields
Key Flexfield - A Key Flexfield represents an intelligent key that uniquely identifies an application entity. These keys identify accounts, part numbers etc. Each key flexfield has an assigned name and a specified set of valid values. Examples: in G/L we have the Accounting Flexfield (Chart of Accounts); in Inventory it is the Item Flexfield, in Projects the Job Flexfield.
Descriptive Flexfield - Allows for the capture of additional information not stored in the default application, or strictly for reference when viewed on a screen. You may create a Flexfield using any of “Attribute(n)” columns. Oracle reserved “Attribute(n)” columns on most of the tables to be setup during installation. These columns are not used by Oracle standard functionality. You may expand the number of Flexfield values by using “context sensitive” feature.
Flexfield Key Terms
Accounting Flexfield : The code you use to identify a general ledger (GL) account in Oracle.
Segment: A subcomponent of a flexfield. Company and natural account are examples of possible segments in the accounting flexfield.
Flexfield Qualifiers : Balancing segment, cost center segment, natural account, inter-company segment.
Segment Values : Numbers or codes attributed to each segment.Value Set: A list of values for one segment, such as the list of companies, list of accounts, or the list of departments.
Code Combination : One full accounting flexfield with all segment values.
Dynamic Insertion / Cross-Validation : Automatic creation of account code combination based on rules defined with code combinations.
GL
REFERENCE1 User Entered or System Auto Generated Batch Name for Every Run
REFERENCE2 User Entered or System Auto Generated Batch Name for Every Run
REFERENCE7 ‘Yes’. This means the following Journal Entry could be reversed
REFERENCE8 Journal Entry Reversal Period in Case it needs to be reversed. This should be the same as Period Name
REFERENCE9 ‘Yes’. To change the Sign for Reversal
Wednesday, October 7, 2009
AIM
It has six phases
Definition phase
Plan the project
Determine business objectives
Operations analysis phase
Documents, and business requirements
Gaps in the software and system architecture requirements.
Results of the analysis should provide a proposal for future business processes
Transition strategy to migrate to the new systems.
Mapping of legacy data to oracle application APIs or open interfaces
Solution design phase
Create designs for solution that meet future business requirements
Design of your future organization comes alive during this phase as customizations and module configurations are finalized
Build phase
Coding and testing of customizations enhancements, interfaces, and data conversions.
One or more conference room pilots test the integrated enterprise system.
Results of the build phase should be a working, tested business system solution.
Transition phase
Project team delivers the finished solution to the enterprise.
End user training and support, management of change, and data conversions are major activities of this phase.
Production phase
Starts when the system goes live.
Technical people work to stabilize and maintain the system under full transaction loads.
Users and the implementation team begin a series of refinements to minimize unfavorable impacts and realize the business objectives identified in the definition phase.


