Oracle Financials
Compiled By: Terry Sanchez-Clark
Oracle Financials
ISBN 978-1-60332-277-5
Smashwords Edition
Copyright© 2008 Equity Press all rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without either the prior written permission of the publisher or a license permitting restricted copying in the United States or abroad.
The scanning, uploading and distribution of this book via the internet or via any other means without the permission of the publisher is illegal and punishable by law. Please purchase only authorized electronic editions, and do not participate in or encourage piracy of copyrighted materials.
The programs in this book have been included for
instructional value only. They have been tested with
care but are not guaranteed for any particular purpose.
The publisher does not offer any warranties or
representations and does not accept any liabilities with
respect to the programs.
Trademarks: All trademarks are the property of their respective owners. Equity Press is not associated with any product or vend0r mentioned in this book.
Please visit our website at www.oracookbook.com
Question 1: Create a new GL module in the same machine
Question 2: Oracle Receivables - Interface
Question 3: Oracle Financial problem
Question 4: Customer vs. Journal Entries Balances
Question 5: Oracle audit possibilities
Question 6: Asset number generation
Question 7: Discoverer 4.1 on multiple homes
Question 8: DBA and Financials
Question 9: Oracle interface programs
Question 10: Tutorials for newbie
Question 11: Oracle Financial Analyzer
Question 12: Showing the PO and Invoice Number along with Distribution Line Info
Question 13: Inventory Calendar & Accounting Calendar
Question 14: Date and time issue for multi-organizations
Question 15: Oracle financials passwords storage
Question 16: APP-FND-01516 LOG ON ERROR in ORACLE 11i
Question 18: Submission problem in Oracle Financial 11i
Question 19: Multi-organization inventory set up
Question 20: Deferred Revenue Report
Question 21: Applications DBA duties
Question 22: Oracle applications financial query
Question 23: Doubts in Accounts Payables
Question 24: Implementing Cash Management
Question 25: Removing a posted journal
Question 27: OFSA: Balance and Control
Question 28: Table relationships
Question 29: Impact of transaction date
Question 30: Account Status Report
Question 31: Invoice problems in accounts payables
Question 32: Oracle Applications Financial for small business
Question 33: AIM in Oracle financial applications
Question 34: Account Inquiry export
Question 35: OFA User creation
Question 36: Accounting Rule and Invoicing Rule
Question 37: Manual Wire Payment in Batch
Question 38: Database of Error Codes in Oracle Financials
Question 39: Unable to reflect TX CODE in AP Distributions
Question 40: Migration of data of GL 10.7 for 11i
Question 41: Simple News Center Database
Part II: ORACLE FUSION MIDDLEWARE
INTRODUCTION: ORACLE FUSION MIDDLEWARE
Question 1: Installing Oracle Fusion Middleware for PeopleSoft
Question 2: Fusion BPEL over Oracle Workflow
Question 4: Installing both adapters
Question 5: Function of a Middleware Product
Question 6: Oracle Data integration offerings
Question 7: Oracle Internet Directory
Question 8: Integrate 2 Instances of Oracle Apps 11.5.10
Question 9: Password to the Portal User
Question 11: Oracle 9i as Portal downloads
Question 12: Deployment of Java to Oracle Database
Question 13: Difference between Oracle Fusion Middleware, Enterprise Service Bus and Interconnect
Question 14: JMX/SNMP support for Oracle AS
Question 15: Future Development Tools used to customize Siebel Applications
Question 16: Oracle Instant Portal
Question 17: Application Server 9i download
Question 19: OAS Web Cache as Load Balancer
Question 20: Oracle 9i Reports
Question 22: Installing Application Server 10g Release 3
Question 23: Oracle Application Version
Question 24: Wireless as an option
Question 25: Web Center Integration with e-Business Suite
Question 25b: OA Framework continuity
Question 26: Business Rule Engine vs. Rules Manager and Expression Filter
Question 27: Writing an anonymous pl/sql block
Question 28: ODI vs. Integration InterConnect
Question 31: Purpose of Oracle BAM client
Question 32: DST 2007 changes and Oracle9iAS
Question 33: Single Sign On work with SSO SDK
Question 34: Standards for Oracle Portlets
Question 35: iFS and Oracle Text searching
Question 36: Oracle Interconnect/Oracle ESB
Question 37: Oracle AS with Oracle Forms possibility
Question 38: Oracle Discoverer
Question 40: Oracle Application Server on Oracle 10g DB server
Question 41: Structure of Oracle
Question 42: Oracle Content Management SDK
Question 43: Oracle Startup Services
Question 44: Oracle Workflow in Oracle 9iAS
Question 45: Top Link Essentials
Question 46: Oracle Forms and Oracle Reports as Portlets
Question 47: Starting Oracle HTTP Server
Question 48: Oracle Fusion Middleware work with open source
Question 49: Oracle Fusion Middleware integrate with Spring
Question 50: Oracle Fusion Middleware support PHP
Question 51: Oracle AS and OC4J
Question 52: Oracle Production Application Database
Question 53: Functionality depth of BPA Suite(demo)
Question 53b: Using Process Interface
Question 53c: Process Publishing
Question 54: Error in Using Oracle Result Set
Question 55: Oracle iFS compatibility with Oracle Jserver
Question 56: Oracle AS release 3 with Oracle XE
Question 57: Batch interface design
Question 58: Sharing one Application in Oracle
Question 60: Find the unit cost for an item in the past date
Question 61: Creating 2 different users
Question 64: Oracle Jinitiator
Question 65: Difference between Oracle Portal & Oracle Forms
Question 66: Error in multiple nested select SQL statement
Question 67: Oracle 9iAS and Oracle Home
Question 68: Multiple Oracle Homes
Question 69: Oracle Portal and Portal Development Kit
Question 70: ORACLE Tags in Dynamic Pages
Question 71: Fusion Middleware Platform
Question 72: loading BFILE-stored picture into HTML
Question 74: Oracle Application Server versus Oracle Apps
Question 75: Auto sign-on for Oracle Portal
Question 76: Oracle 8.1.7 database to 9.2
Oracle Applications is a collection of business ERP (Enterprise Resource Planning) applications developed by Oracle Corporation, which utilize their core RDBM database technology. Sometimes referred to as Oracles "E-Business Suite", Oracle Applications (currently Release 12, released 31st January 2007), contains several product lines such as Oracle Financials, Oracle Logistics, Oracle HR, Oracle Sales, and others. Within each product, there are several modules, each to be separately licensed.
Key technologies incorporated into the applications are the Oracle database technologies, (RDBMS, PL/SQL, java, html, xml, engines), the "technology stack" (Oracle Forms Server, Reports Server, Apache Web Server, Discoverer, Jinitiator and (Sun Java).
Oracle developed the applications from a terminal based VT220 interface to a client server model in 10.7, only to drop this within the space of a year for the Network Computing Architecture (NCA). This was a revolutionary concept exploiting the internet/network and thin client computers.
Oracle Financials includes several dozens of "modules", each which is separately licensed within the E-Business Suite. The modules include:
* Advanced Collections
* Assets
* Balanced Scorecard
* Cash Management
* Daily Business Intelligence for Financials
* Enterprise Planning and Budgeting
* Financial Consolidation Hub
* Financials Centralized Solution Set ( FINS)
* General Ledger
* Internal Controls Manager Internet Expenses
* Payments
* iReceivables
* Lease Management
* Loans
* Payables
* Property Manager
* Receivables
* Treasury
Oracle Ledger includes ERP features like Intercompany transactions, Consolidation etc.
The Oracle E-Business Suite Financials family of applications automates and streamlines all your financial business processes, for enterprise-wide daily business intelligence that lets you make more informed decisions, improve operations, and reduce costs. A unified data model provides a single accurate view of all your financial information, including a 360-degree view of your customers. And Oracle Financials, running on Oracle technology, gives you industry-leading performance and scalability.
http://en.wikipedia.org/wiki/Oracle_Financials
http://www.oracle.com/applications/financials/intro.html
Question 1: Create a new GL module in the same machine
I need to create a new set of database Oracle Finance and program in the same machine having the Sun Solaris system. Can I just copy the volume of program and data? Or do I need to make a new installation?
If I need to make a copy of the GL module set database, is it possible if I only copy the data, and make a new pointing map from the program?
A: In order to create a new set of database Oracle Finance and program in the same machine, you will need to create a new instance. You can create the new instance in the same machine.
Question 2: Oracle Receivables - Interface
Can you tell me which tables get hit in the ‘Receivables’ module when the receivables interface is run from Order Entry?
A: We have the RA_INTERFACE_LINES_ALL table to be populated. From there, AR picks data for creating an invoice using “autoinvoice”.
While Autoinvoice gets data from ra_interface_lines_all, it also picks data from ra_interface_salescredits_all and ra_interface_distributions_all if it is present.
Question 3: Oracle Financial problem
I have installed two instances (test, vision) of Oracle Financial 11i for vision instance and everything seems ok. But in testing an instance after logging using sysadmin/sysadmin ini IE5.0, an error comes after the applet has started.
I find this error:
“APP-FND-01516: invalid username or password or database
The tns80listnerAPP_TEST is not starting.”
How can I solve this?
A: You have just found out that database would not start correctly in getting the error in identifying control file;
Go to the COMMON_TOP/admin/install directory
/u202/train/d01/oracle/prodcomn/admin/create
To solve this, run “adcrdb.sh”. This created the control file.
Restart the database, and then try connecting to applications which will complete the connection.
Question 4: Customer vs. Journal Entries Balances
This is the scenario I have:
When an invoice is created in AR AND the user changes to the AR (1070), it debits to something other than 1070.
The customer balance is increased to reflect that he owes us money but the Journal entries for 1070 remains at 0.
I assume the Aged VS and the journal entries for a customer should be equal. I am aware the invoice should have used 1070 but it didn't, which means a user error.
Do you know how to undo this type of error?
Please note that it is too late to cancel the invoice. There are other types of user mistakes that can throw out the JE VS AGED as well.
A: The situation you mentioned is the reasons why you should not change invoices that are accounted for. This can also happen when the J/E is changed since the AR transfer to GL creates unnoted J/E. Changing the said invoices puts GLA and the sub ledger out of sync. You need to set the journal source for receivables so that the update flag is not enabled.
In AR, don’t change invoices that are there. Instead use DM and CM functionality to adjust invoices or create new ones.
Question 5: Oracle audit possibilities
I like to know if there are functionalities in Oracle Applications that will allow me to audit the access users to have certain forms.
Are there specific requests that will allow me to look up all the users that can define vendors, or do I have to define queries on the database tables?
A: You can select distinct user_id from fnd_user where user_id in (select created_by from po_vendor_sites_all).
Alternatively, you can play with the user and responsibility tables to see who has access to the responsibilities. You need to PO super user and PO user, and other responsibilities that have access to the form to enter vendors. If a user does not have access to a ‘responsibility used’ to enter vendors, he cannot enter vendors using his login.
Note that po_vendors is at a generic level (no_org_id) while po_vendor_sites is at org_id level.
created_by and Updated_by are who columns that allow you to see who created or updated the data.
Question 6: Asset number generation
In our system, asset numbers are automatic generated one by one. But sometimes, it skips the sequence. For example, we have closed a period with a last asset number 326, and when we open the next period, it is gives an asset number of 329. It means we missed asset number 327 & 328. Sometimes, it also happens even without closing the period.
How do I fix this?
A: You will need to check your Fa_distribution_history table to see if you are missing gaps there and then trace it to where assets where created, either in FA or in PA.
Question 7: Discoverer 4.1 on multiple homes
I have some of the Oracle tools installed on my ‘mech’ in "Oracle" home directory. When I tried to install Discoverer 4.1 Admin, it says the Oracle Home already exists, so I renamed my original "Oracle" directory to "Oracle1" and installed the Administration Edition. But when I try to run the ‘Discoverer Administration tool’ it does not work. It says that it cannot resolve the TNS names. SQL Plus from the old directory works fine. I even copied the TNSNAMES.ORA file in to the New "Oracle" dir.
Can you give some ideas on how to deal with this?
A: This is a known bug with Discoverer. There is an update from Oracle which they will send to you with the necessary fix so that it will not conflict with your other Oracle based files. You will need to submit a TAR to Oracle to get the fix.
Question 8: DBA and Financials
What is the difference between Oracle DBA and Oracle Financials?
A: Oracle DBA is the Oracle Database while Oracle Financials is part of the Oracle Applications. Oracle Applications is a suite of products that sits on the database like Oracle Purchase or Oracle General Ledger. In these cases, one is a purchasing program and one runs your GL.
Question 9: Oracle interface programs
Are there any inbuilt interface programs in the Oracle financials?
Why are we creating interface programs even when there are inbuilt interface programs?
A: Yes, there are standard interface programs between standard modules, like AP to GL, AR to GL and the likes.
We write them when we need the transfer data customized or if data is being transferred from a non- Oracle sub-ledger into Oracle.
Question 10: Tutorials for newbie
I’m new to Oracle financials. Are there any sites through which I can gain a brief knowledge in oracle financials which provides white papers, pdf’s or tutorials?
A: Try visiting the Oracle documentation site at:
http://download-east.oracle.com/docs/cd/B25516_16/current/html/docset.html
And if you have access, the metalink documents,
Question 11: Oracle Financial Analyzer
Can you point me to some good links for Oracle Financial Analyzer? I need links which deals with the problems faced by end user while working with Oracle Financial Analyzer.
A: There are lots of documents available on Financial Analyzer in the Oracle Documentation site:
http://download-east.oracle.com/docs/cd/B25516_16/current/html/docset.html
Question 12: Showing the PO and Invoice Number along with Distribution Line Info
Do you know the code that can show the PO and Invoice number along with the distribution line detail?
I'm trying to get to the po.po_vendors for segment1 and po.po_distributions_all but it screws up at the po.po_lines_all table which needs outer joins and it is simply not working.
Can you help me out?
A: Yes, I can. You will need to look for the code in ‘Supplier Paid Invoice History Report’ or try AP_INVOICES_V table.
Question 13: Inventory Calendar & Accounting Calendar
What is the difference between accounting calendar and inventory calendar?
A: Inventory Calendar is a transaction calendar used for inventory transaction and shipping while accounting calendar is used for financial purposes like invoicing, cash dealing, creating journal, month end and year end process.
Question 14: Date and time issue for multi-organizations
How can I manage the date and time, if my organizations have different plant and locations?
For example, the database server is located in the Pacific Time Zone and the application may access from the new plant using German Time Zone. Whenever the German business center enters any sales transaction, it should take German current date and time instead of Pacific Time.
A: In defining a location, you need to mention the time zone for that location and also the inventory organization to which the location belongs. This location can be assigned to your organizations. You can then access the time zone according to the location/s you select.
Question 15: Oracle financials passwords storage
Where does Oracle financials 11 store the application usernames and passwords? In which table are they stored, and are they encrypted?
A: You can start with FND_USERS. They are encrypted. Have a look at the FNDPASS utility that can be used to set application and schema passwords.
Question 16: APP-FND-01516 LOG ON ERROR in ORACLE 11i
I have installed APPS 11.5.7 in Windows 2000 Professional. A few days later I tried to login to the Application, and I got this error:
APP-FND-01516: Invalid application username, password, or database. User name : APPLSYSPUB, password: PUB and Database : VIS
How do I fix this?
A: In order for you to deal with the problem, you need to have access to metalink (https://metalink.oracle.com/). You can find information about this problem under different situations. As an example, say:
PROBLEM DESCRIPTION:
You are doing a new installation of the Release 11 Vision Demo. While trying to start the AppletViewer from a DOS command window on the client, you get the following error:
“APP-1516 invalid username, password or database”;
PROBLEM EXPLANATION:
The GWYUID is not defined correctly in the initial HTML signon file (example, afsamples.htm).
SEARCH WORDS:
sign on afsamples static
SOLUTION DESCRIPTION:
The default GWYUID (Gateway User ID) username/password for Release 11 Vision Demo is APPLSYSPUB/PUB. For 10.7, Global Demo, it was APPDEMOPUB/PUB.
Make sure that the GWYUID in the initial html file (afsamples.htm), is set to APPLSYSPUB/PUB.
I want to enter to the OFSA world. I need a guide as to where I should start, which document to read, the architecture of the solution and where can I get the product?
In short, I need the works.
A: There are many modules in Oracle Financials. The best way is for you to study each module's user guide. You can get this user guides at this address:
http://c311.c3biz.com/appsdocs/1152/html/erpset.html
The documents are big files for download but you can find all the information that you need to know there.
Question 18: Submission problem in Oracle Financial 11i
I am facing a problem in Oracle Financial 11i. Whenever I submit my request, I am getting errors. In Windows we are able to print that report.
Error:
+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
FNDSCURS module: Active Users
+---------------------------------------------------------------------------+
Current system time is 13-SEP-2001 11:19:24