INTERVIEW QUESTIONS
Oracle Purchasing – Purchase Order FAQ
What is 2-way, 3-way, 4-way matching? How is this set-up?
Answer
2-way matching verifies that Purchase order and invoice information match within your tolerances as follows:
Quantity billed <= Quantity OrderedInvoice price <= Purchase order price(<= sign is used because of tolerances)
3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Quantity billed <= Quantity accepted.(Acceptance is done at the time of Inspecting goods).
Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character)
Receipt required Inspection required Matching
Yes Yes 4-way
Yes No 3-way
No No 2-way
In GUI, Shipments block, alternative region, click on More…this brings up a region with ‘Invoice matching’ column where you can choose 2-way, 3-way or 4-way match.
How do you reflect discount from a supplier on a Purchase orders?AnswerThe legal document is the Purchase order not the quote and therefore the purchase order should reflect agreed upon price. Secondly if the discount comes after the PO then AP should handle it when invoicing. If you are using a blanket Purchase order then you can use price breaks.How do you change the created date on a Purchase Order?AnswerThe 'Created' date on the Purchase order is not an updateable field, due to it being a legal document once approved.What is the sorting order for requisition lines in autocreation of a Purchase Order?AnswerRequisition lines first meet search criteria determined by "find" screen, and then are grouped together by item and item category, then by need-by date. After you choose an action, enter any search criteria to narrow the selection of requisition lines. Oracle Purchasing chooses requisition lines from the requisition pool that meet your search criteria. All requisition lines for the same item and item category are grouped together, but they are not necessarily in order alphabetically. Within each grouping of item and item category, requisition lines are ordered by need-by date.
What does the error ‘APP-50022: Oracle Human Resources could not retrieve a value for User Type profile option’ mean?AnswerThe profile HR: User Type needs to be set. Set the profile option ‘HR: User Type’ at the Responsibility level
When do you see the status of a Purchase order as Archived?AnswerYou must approve or print a specific purchase order before you can see it in the history information.In Oracle Purchasing, where is the automatic numbering for Purchase Order defined and maintained?AnswerThe navigation path for GUI is:Setup --> Organizations --> Purchasing Options, Numbering alternate region
Oracle Workflow – Account Generator Process
What does the Account Generator process do?The Account Generator process builds charge, budget, accrual, and variance accounts for each purchase order, release, and requisition distribution based on the distribution’s destination type. It is a synchronous Workflow process.
What are the Pre-requisites to use Account Generator?Before using the Account Generator you must:
– Define your Accounting flexfield structure for each set of books.
– Define flexfield segment values and validation rules.
– Set up Oracle Workflow.
– Decide whether you want to use the Account Generator processes as seeded in Oracle Purchasing, or you need to customize them to meet your accounting needs
– Define your Accounting flexfield structure for each set of books.
– Define flexfield segment values and validation rules.
– Set up Oracle Workflow.
– Decide whether you want to use the Account Generator processes as seeded in Oracle Purchasing, or you need to customize them to meet your accounting needs
What are the steps to generate the WFSTAT output for Account Generator?
A: 1. Set the following profiles:
– Account Generator: Run in Debug Mode=> Yes
– Account Generator: Purge Runtime Data=> No
PO: Set Debug Workflow On => Yes
2. Make sure that the concurrent program “Purge Workflow Obsolete Runtime Data” is NOT running.
3. Open Purchase Order/Requisitions form, go to the distributions window, enter necessary fields and click on charge account field to start generating the charge account. After the account generator has done building the account, or errors out, do the following from the toolbar (DO NOT exit the form or navigate to any other block or record, otherwise this information would be lost):
– Account Generator: Run in Debug Mode=> Yes
– Account Generator: Purge Runtime Data=> No
PO: Set Debug Workflow On => Yes
2. Make sure that the concurrent program “Purge Workflow Obsolete Runtime Data” is NOT running.
3. Open Purchase Order/Requisitions form, go to the distributions window, enter necessary fields and click on charge account field to start generating the charge account. After the account generator has done building the account, or errors out, do the following from the toolbar (DO NOT exit the form or navigate to any other block or record, otherwise this information would be lost):
Help=> Diagnostics => Examine
Enter in 1st field => parameter
Enter in 2nd field => charge_acc_wf_itemkey
Then tab out. The Item key will appear in the third field.
4. Now save the purchase order/requisition. If you are not able to save, then clear the distribution record and navigate back to the shipment window and save the form. Saving the form is must, because a commit is required to save the workflow information in tables, for generating the wfstat output.
5. If step#3 could not give you an item key, then use the following query to identify the relevant item key:
4. Now save the purchase order/requisition. If you are not able to save, then clear the distribution record and navigate back to the shipment window and save the form. Saving the form is must, because a commit is required to save the workflow information in tables, for generating the wfstat output.
5. If step#3 could not give you an item key, then use the following query to identify the relevant item key:
Select item_key, item_type, begin_date
from wf_items
where item_type = ‘&item_type’
order by begin_date desc;
For PO, use ‘POWFPOAG’ item type in above query, and for requisition, use ‘POWFRQAG’.
6. To generate the WFSTAT output,
Run the sql in $FND_TOP/sql/wfstat.sql with above item_type and item_key. Spool the output.
7. To get the wf debug information, run the following query:
6. To generate the WFSTAT output,
Run the sql in $FND_TOP/sql/wfstat.sql with above item_type and item_key. Spool the output.
7. To get the wf debug information, run the following query:
SELECT item_key, item_type, debug_message
FROM po_wf_debug
WHERE item_key = ‘&Item_key’
ORDER BY execution sequence;
How do you determine the WF Item Key value to run wfstatus.sql for Account Generator workflows?The item key is required in order to run wfstatus.sql for account generator. The wfstatus.sql gives important debug messages, including error messages for processes that are erroring out. This provides the same information that the Workflow Monitor does, if the Workflow Monitor is setup.
Steps to determine the WF Item Key value:
The item key can be found in the Workflow–>Status form in Projects (Querying by item type and dates) or, more generally, via SQL*Plus from the table WF_ITEMS. A sample script for getting the key is below:
Steps to determine the WF Item Key value:
The item key can be found in the Workflow–>Status form in Projects (Querying by item type and dates) or, more generally, via SQL*Plus from the table WF_ITEMS. A sample script for getting the key is below:
Select item_key, begin_date, end_date
from wf_items
where item_type = ‘PAAPINVW’
and begin_date between ‘&low_start’ and ‘&high_start’;
If this does not definitively identify your item you can query on the various items in the Workflow status form to give you more information about the activities associated with them.
How do I save my workflow definition to the database?There are three options to use when saving your workflow definition to the database:
Option #1
If you have the file open in workflow builder select File -> SaveAs -> Database. Enter the apps username and password and provide the connection string (from the tnsnames.ora in the %ORACLE_HOME%\network\admin directory).
Option #2
Option #2
The WFLOAD utility is used for loading and extracting workflow definition files to and from the database.
Command Line Syntax:
WFLOAD 0 Y {UPLOAD | DOWNLOAD | UPGRADE | FORCE}
[filepatch [item type]]</APPS pwd>
NOTE: The item type parameter is used only for the DOWNLOAD mode.
Option #3
Option #3
Run the concurrent program, Workflow Definitions Loader. The system administrator will need to add this concurrent program to a request security group for the responsibility that you want to run this program from.
How do I trace errors in my Workflow processes?A: If a customized workflow process errors you should check the error stack (using the Workflow Monitor and/or wfstatus.sql script), if the procedure in error does NOT begin with WF_.. OR _… then you need to debug your own pl/sql procedures.
If standards in the Workflow Guide for exception handling have not been followed, you can still tell whether the problem is with your code, because Oracle Workflow traps external exceptions and reports these as
If standards in the Workflow Guide for exception handling have not been followed, you can still tell whether the problem is with your code, because Oracle Workflow traps external exceptions and reports these as
Activity Status: ERROR
Activity Result: #EXCEPTION
This is what would show up in the workflow monitor or any status report or form inquiry for the failing activity.
How do I customize the Account Generator Workflow process for my company?A: Customers are expected to customize the Account Generator process. You can do this by editing the existing process or creating your own process, but this MUST be within the seeded ‘Item Type’. You should NOT create a NEW ‘Item Type’ as this would NEVER be run by Account Generator.
If you create a new process, you must ensure it is “Runnable.”
You must then assign the appropriate process to the Accounting Flexfield structure in the Account Generator Process window in Oracle Applications (login as System Administrator, Application -> Flexfields -> Key -> Accounts and query up the required Accounting flexfield structure).
If you create a new process, you must ensure it is “Runnable.”
You must then assign the appropriate process to the Accounting Flexfield structure in the Account Generator Process window in Oracle Applications (login as System Administrator, Application -> Flexfields -> Key -> Accounts and query up the required Accounting flexfield structure).
Accounts Payable Trial Balance – Reconciliation with GL
Q: What formula should I use to balance AP to GL?
A: Use the following as an example of how to balance. In this example, you are closing your accounting period for April and you have just posted your final invoice and payment batches to your general ledger system.
To reconcile your accounts payable activity for April, make the following calculation:
“Accounts Payable Trial Balance” as of March 31
+ “Posted Invoice Register” for the period between April 1 and April 30
– “Posted Payment Register” for the period between April 1 and April 30
= “Accounts Payable Trial Balance” as of April 30
+ “Posted Invoice Register” for the period between April 1 and April 30
– “Posted Payment Register” for the period between April 1 and April 30
= “Accounts Payable Trial Balance” as of April 30
Reconciling AP to GL is accomplished with the use of the following reports.
“Posted Invoice Register”
“Posted Payment Register”
“Accounts Payable Trial Balance” (current and last period)
These reports ensure that your Trial Balance accurately reflects your accounts payable liability by matching the Posted invoices and payments with the AP liability account. You can also compare your AP liability accounts to GL by doing a query of the accounts in GL to identify the account or accounts out of balance. The trial balance total should be the same as your GL liability account.
If not:
Run the GL “Account Analysis” report for the liability account and for the date range in question. Look for transactions with a source other than Payables. This can quickly pinpoint any transactions incorrectly charged to the account. Make sure that you have not made manual
journal entries to your liability account in General Ledger.When you identify the accounts you go back to AP and do a query on the account to find the invoices out of balance.
The last step is to create a journal entry in GL to balance the account or accounts that is out of balance.
Q: How is the as-of-date used in the “Accounts Payable Trial Balance” report?
A: The as-of-date is used to determine which invoices and payments should be included on the report. Any invoices or payments with an accounting date AFTER the entered as-of-date will not be displayed on this report.
A: The as-of-date is used to determine which invoices and payments should be included on the report. Any invoices or payments with an accounting date AFTER the entered as-of-date will not be displayed on this report.
Very Common Oracle Apps DBA Questions
I collection few regular asked question for Oracle Apps DBA to hire Apps DBA for my team, now publishing for reader’s use. These question are even relevant for Oracle Technical experts / developer also. I would like to request share your questions if you have any.
Q. What is difference between fresh database and vision database install types ?
Fresh Database – Database is installed with Apps but with no data
Vision Database – Database installed with Apps with dummy data
Fresh Database – Database is installed with Apps but with no data
Vision Database – Database installed with Apps with dummy data
Q. Whatis US directory in $AD_TOP or under various product TOP’s .US directory is default language directory in Oracle Applications. If you have multiple language Installed in your Applications then you will see other language directories beside US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.
Q. Whats main concurrent Manager types.ICM – Internal Concurrent Manager which manage concurrent Managers
Standard Managers – Which Manage processing of requests.
CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.
Standard Managers – Which Manage processing of requests.
CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.
Q. Where is Concurrent Manager log file location ?By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.
. List some ad utilities and their functions?adadmin, adctrl, adpatch, adlicmgr, admrgpch, adsplice and adworker
Q. List out the modules related to oracle Apps DBA ?
FND – Application Object Library, AU – Application Utilities, AD – Application DBA
Q. How many concurrent mangers can be used ? what is the limit ?
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).
Q. How would you check if the tablespace is in backup mode ?
– Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
– Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
Q. What is “Custom TOP” in apps ?
– Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP
– Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP
Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?– System picks up dbc file based on profile option value “Applications Database ID”
Q Where is applications start/stop scripts stored ?
Ans: $COMMON_TOP/admin/scripts/$CONTEXT_NAME
How to confirm if Report Server is Up & Running ?
Ans: Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like
ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_VISION
where VISION is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files
ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_VISION
where VISION is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files
How you will start Discoverer in Oracle Apps 11i ?
Ans: In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME
or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)
or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)
How to check number of forms users at any time ?
Ans: Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc –l
ps -ef | grep f60webmx | wc –l
What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?
Ans: 0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.
Q. What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?
Ans: .ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.
Q. What is dev60cgi & f60cgi ?
Ans: cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi
Oracle Interview Questions – Oracle Forms – Custom Development – FAQ
What is the ICX: Forms Launcher system profile option for?
This profile option is used by the Self Service Web Applications Personal Home Page (also known as ICX) to determine the base URL needed to launch an application, which in this case is a Forms application.
What should ICX: Forms Launcher be set to?
ICX: Forms Launcher –is set to ?http://machine_name:port/dev60cgi/f60cgi?. In Apps release 11i you can add some parameters to this URL to enable some Forms features like tracing.
What is FORMS60_TIMEOUT?
FORMS60_TIMEOUT is an environment setting that determines the maximum idle time (in minutes) before f60webmx shuts down. Note that it will only terminate an idle middle tier process, i.e. one which is waiting for the "next message" from the client. If the middle tier process is running a transaction or waiting for a query to complete it will not have take effect.
FORMS60_TIMEOUT is an environment setting that determines the maximum idle time (in minutes) before f60webmx shuts down. Note that it will only terminate an idle middle tier process, i.e. one which is waiting for the "next message" from the client. If the middle tier process is running a transaction or waiting for a query to complete it will not have take effect.
How do you enable Forms Runtime Diagnostics (FRD) in Apps? To enable Forms Runtime Diagnostics (FRD):
– login to Apps as sysadmin
– click on Profile/System
– query ICX%FORMS%LAUNCHER
– add the following to the end of its value at user level:
?record=collect&log=/tmp/forms_frd.log
– click on Profile/System
– query ICX%FORMS%LAUNCHER
– add the following to the end of its value at user level:
?record=collect&log=/tmp/forms_frd.log
Note that like any Apps profiles you can do this on various levels e.g. site, user, etc. so that you target your FRD accordingly. Also be aware that FRD incurs significant overhead to Apps processing so disable it when not in use
How do you generate Apps Forms modules or FMB files?
Oftentimes as part of upgrading Forms or modifying a Form module or applying a patch modifying a Form module, you would encounter an issue that would necessitate manually regenerating the Forms module executable or FMX file. To do this you issue the following command:
Oftentimes as part of upgrading Forms or modifying a Form module or applying a patch modifying a Form module, you would encounter an issue that would necessitate manually regenerating the Forms module executable or FMX file. To do this you issue the following command:
f60gen module=form_name.fmb userid=apps/apps output_file=form_name.fmx module_type=form batch=yes compile_all=special
How do you generate Apps Forms library modules or PLL files? To do this you issue the following command:
f60gen module=library_name.pll userid=apps/apps module_type=library batch=yes compile_all=special
How do you generate Apps Forms menu modules or MMB files? Note that there is only one menu for Apps so it is rare that you need to regenerate the menu. To do this you issue the following command:
f60gen module=FNDMENU.mmb userid=apps/apps output_file=FNDMENU.mmx module_type=menu batch=yes compile_all=special
What is the sequence in which Forms resolves program unit calls? Forms will attempt to resolve a program unit call in the following sequence:
Program Units node
Forms Library
Database
Forms Library
Database
Where can you find the source Forms modules for Apps? Apps places all its source (FMB) files in the $AU_TOP/forms/<language> directory whereas the FMX files reside in their respective $PRODUCT_TOP/forms/<language> directory. On the other hand, all menu (MMB, MMX) and library (PLL and PLX) files are copied to the $AU_TOP/resource directory.
How can you find the version of Forms in Apps? Once you are in an Apps form …
click on Help
click on About Oracle Applicationsinformation is under the Current Form section. The Form Name corresponds to the FMB and FMX file for that form. The Form Version corresponds to the release level of that form.
How can I open an Apps form in Forms Builder
If you want to open a product specific form, make sure you have all its associated forms and libraries copied over. Since it is quite difficult to pinpoint exactly what this is, its best to just copy over the entire contents of $AU_TOP/forms/<language> and $AU_TOP/resource.
How can you confirm the version of the FMX or FMB file? Oftentimes, once you determine the version and name of the current form you want to peek into the internal workings of the actual form itself. To ensure that you are peeking at the right release of the FMB file you can do the following:
$adident Header FNDSCSGN.fmb
If you simply copy the form over to a a machine that does not have Apps installed, you will run into is a lot of FRM-18108: "Failed to load the following objects" errors. This is because an Apps form is usually dependent on a host of other forms, libaries and menus.
If you want to open the TEMPLATE.fmb form, make sure you have the following forms, libraries, copied over as well from the $AU_TOP/forms/<language> and $AU_TOP/resource directories:
APPSTAND.fmb
APPCORE.pll
APPCORE2.pll
APPDAYPK.pll
CUSTOM.pll
FNDSQF.pll
GHR.pll
GLOBE.pll
GSM.pll
JA.pll
JE.pll
JL.pll
VERT.pll
OPM.pll
PQH_GEN.pll
PSA.pll
PSAC.pll
PSB.pll
IGILUTIL.pll
IGILUTIL2.pll
What is a Forms Builder node?
A Forms Builder node refers to the leaves and branches of its Object Navigator. The Object Navigator provides a hierarchical display of the objects in a Form modules.
+Forms
+Module
+Trigger
+Alerts
+Attached Libraries
+Data Blocks
+Canvases
+Editors
+LOVs
+Object Groups
+Parameters
+Pop Up Menus
+Program Units
+Property Classes
+Record Groups
+Reports
+Visual Attributes
+Windows
+Menu
+PL/SQL Libraries
+Object Libraries
+Built-In Packages
+Database Objects
+Module
+Trigger
+Alerts
+Attached Libraries
+Data Blocks
+Canvases
+Editors
+LOVs
+Object Groups
+Parameters
+Pop Up Menus
+Program Units
+Property Classes
+Record Groups
+Reports
+Visual Attributes
+Windows
+Menu
+PL/SQL Libraries
+Object Libraries
+Built-In Packages
+Database Objects
Objects are grouped under their respective nodes. For example, all of the windows defined in a form module appear under the Windows node; all the LOVs defined in the form module appearunder the LOVs node and all the program units appear under the Program Units node. It is important to familiarize yourself with this in case you need to navigate within an Apps form during the course of debugging a problem.
Template Form in Oracle Applications
Template Form in Oracle Apps
This document provides an overview of the template form. This form derives its importance from the fact that this form is the starting point of all development involving forms. The document highlights the importance of Template.fmb in forms development and also provides a detailed explanation of the various components of the Template form.
Overview of the Template Form
The TEMPLATE form is the starting point for all development of new forms. The first step in creating a form for use in Oracle Applications is to copy the template form from $AU_TOP/forms/US, to a local directory and renaming it.
The Template form is unique because it contains some special libraries and triggers that render the application using the template form some standard characteristics. The components of the template form are:
· References to object groups: The template form contains platform–independent references to predefined standard object groups in the APPSTAND form. STANDARD_PC_AND_VA, STANDARD_TOOLBAR, and STANDARD_CALENDAR).
· Libraries: The template form contains platform–independent attachments of several libraries (including FNDSQF, APPCORE, and APPDAYPK).
· Special triggers: The template form contains several form–level triggers with required code. These are responsible for standard the behavior of the form.
· Predefined Program Units: The template form contains predefined program units that include a spec and a body for the package APP_CUSTOM, which contains default behavior for window opening and closing events.
· Applications Color Palette: The template form contains the application color palette. This gives the forms developed using the template form the look and feel of Oracle Applications
· Many referenced objects (from the object groups) that support the Calendar, the toolbar, alternative regions, and the menu. These objects include LOVs, blocks, parameters, and property classes, and so on.
· The TEMPLATE form contains sample objects that can be seen as examples for the expected layout cosmetics. These samples can be completely removed from the form later as they are only examples and are not required. The following objects are the samples and can be removed:
§ Blocks: BLOCKNAME, DETAILBLOCK
§ Window: BLOCKNAME
§ Canvas–view: BLOCKNAME
Hence, the template form comes along with many attachments, predefined program units, and defined visual attributes as well as examples that not only give the forms that are developed using the template.fmb a standard look and feel, but also make t easier to develop forms with consistent and standard functionality.
Libraries in the Template form
As stated above, the template form contains platform–independent attachments of several libraries. These libraries are used while running the form as a part of Oracle Applications. Hence, these libraries should not be changed or modified. There are three main libraries that are attached to the template form:
APPCORE: APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc. APPCORE contains the procedures to support this standard runtime behavior. APPCORE also contains various other utilities for exceptional handling, message levels, and so on.
APPDAYPK: The APPDAYPK library contains the packages that control the Oracle Applications Calendar feature. The calendar (or the date picker) is a utility that oracle apps provide to pick the dates for a date type field.
FNDSQF: FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing. It also has various other utilities for navigation, multicurrency, WHO, etc.
Other Libraries à The template form also contains a few other libraries that are not linked directly to the template form, but are linked to the three libraries listed above. Although, while using the form it makes no difference whether the library is linked directly to template or to another library that is linked to template. These are discussed below.
CUSTOM library: The CUSTOM library (CUSTOM.pll) is probably the most widely used and customized in the libraries attached to the template form. This library allows extension of Oracle Applications forms without modification of Oracle Applications code. Any form goes to the CUSTOM.pll whenever any event fires on the form. Code can be written in theCUSTOM.pll with the logic branching based on the form, block and trigger on which you want it to run.
You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.
GLOBE: The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms
without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can
take effect based on these events. The GLOBE library calls routines in
the JA, JE, and JL libraries.
without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can
take effect based on these events. The GLOBE library calls routines in
the JA, JE, and JL libraries.
VERT: The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library. Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.
JA: The JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.
JE: The JE library contains code specific to the EMEA (Europe/ MiddleEast/ Africa) region and is called by the GLOBE library.
JL: The JL library contains code specific to the Latin America region and is called by the GLOBE library.
Oracle 11i Application Developer – Technical FAQ – Part 2
Hello Friends, here is the second set of Questions on Application Developers , more of general questions, i am not digging in particular areas , only trying to put question which i normally ask with candidates. If you want help in any specific areas , plz advise, i will try to do that.
Q11 ) What is SET-OF-BOOKS?
Collection of Chat of Accounts and Currency and Calendars is called SOB
Collection of Chat of Accounts and Currency and Calendars is called SOB
Q12 ) What is the interface?
Interface Table is a table which is used as medium for transfer of data between two systems.
Interface Table is a table which is used as medium for transfer of data between two systems.
Q13 Tell me what is the procedure to develop an interface?
a. First we will get the Requirement document.
b. We will create control file based on that plot file.
c. Then the control files which loads the data into staging tables.
d. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
e. Through the standard programs we will push the data from interface tables to Base tables.
a. First we will get the Requirement document.
b. We will create control file based on that plot file.
c. Then the control files which loads the data into staging tables.
d. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
e. Through the standard programs we will push the data from interface tables to Base tables.
Q14) What is multi org?
“Legal entity has more than one operating unit is called as multi org”
a) Business group — Human resources information is secured by Business group
b) Legal entity. — inter-company and fiscal/tax reporting.
c) Operating unit — secures AR, OE, AP, PA and PO Information.
d) Organizations — is a specialize unit of work at particular locations
“Legal entity has more than one operating unit is called as multi org”
a) Business group — Human resources information is secured by Business group
b) Legal entity. — inter-company and fiscal/tax reporting.
c) Operating unit — secures AR, OE, AP, PA and PO Information.
d) Organizations — is a specialize unit of work at particular locations
Q15) What are the User PARAMETERS in the Reports?
P_CONC_REQUEST_ID
P_FLEX_VALUE
P_CONC_REQUEST_ID
P_FLEX_VALUE
Q16) FND USER EXITS:-
FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.
FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.
FND FLEXIDVAL are used to display flex field information like prompt, value etc
FND FLEXSQL these user exits allow you to use flex fields in your reports
FND FORMAT_CURRENCY is used to print currency in various formats by using formula column
FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.
FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.
FND FLEXIDVAL are used to display flex field information like prompt, value etc
FND FLEXSQL these user exits allow you to use flex fields in your reports
FND FORMAT_CURRENCY is used to print currency in various formats by using formula column
Q17) what are the two parameters that are mandatory for pl/sql type concurrent program?
Procedure/function (ERRBUF OUT
RETCODE OUT
………………….)
ERRBUF :- Used to write the error message to log or request file.
RETCODE :- Populate log request file with program submission details info.
Procedure/function (ERRBUF OUT
RETCODE OUT
………………….)
ERRBUF :- Used to write the error message to log or request file.
RETCODE :- Populate log request file with program submission details info.
Q18.) What is Value Set?
–The value set is a collection (or) container of values.
–When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.
n If the list of values needed to be dynamic and ever changing and define a table based values set.
–The value set is a collection (or) container of values.
–When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.
n If the list of values needed to be dynamic and ever changing and define a table based values set.
Q19) What r the validation types in Value Set?
1) None ——– validation is minimal.
2) Independent ——input must exist on previously defined list of values
3) Dependent ——input is checked against a subset of values based on a
prior value.
3) Table —– input is checked against values in an application table
4) Special ——values set uses a flex field itself.
5) Pair —— two flex fields together specify a range of valid values.
6) Translatable independent —– input must exist on previously defined list
of values; translated values can be used.
7) Translatable dependent ——- input is checked against a subset of values
based on a prior values; translated value can be used.
1) None ——– validation is minimal.
2) Independent ——input must exist on previously defined list of values
3) Dependent ——input is checked against a subset of values based on a
prior value.
3) Table —– input is checked against values in an application table
4) Special ——values set uses a flex field itself.
5) Pair —— two flex fields together specify a range of valid values.
6) Translatable independent —– input must exist on previously defined list
of values; translated values can be used.
7) Translatable dependent ——- input is checked against a subset of values
based on a prior values; translated value can be used.
Q20) Who information’s?
1) Created by
2) Creation date
3) Last _updated by
4) last_update_date
1) Created by
2) Creation date
3) Last _updated by
4) last_update_date
ERP Strategy post Go Live
We have all seen lot of material available all over the internet in vendor white papers, research articles and independent blogs about the best practices on improving the ERP implementation cycle in terms of cost, cycle time and return on investment. Most of these articles stop at Go-Live and do not capture the challenges and the opportunities after Go-Live so that the envisaged benefits from ERP can be realized.
If you ask me, I would say that Going Live successfully with the application is the first milestone in the whole business transformation exercise and we still need to not only measure and benchmark the process efficiencies as well as automation levels at fixed intervals to continuously improve them, but also make sure that the changes that have been brought in the organization as a result of the ERP Application Implementation, have gone down well with the users and they also see long terms benefits in switching to this new ERP, leaving behind their favorite legacy systems.
I have tried to capture few challenges and opportunities that we stumble upon after Going Live with ERP that we all should consider so as to reap the benefits to the maximum. Let me first start with the challenges:
- The first and foremost challenge after Going Live is to stop running parallel systems. While few organizations have really matured on this part and wither switch off the entire legacy systems or those functionalities in them that are now available in the new ERP and users are expected to work on them only in ERP, but still a very large number of organizations still do not have a well charted process in this regard. This poses multiple issues, the biggest being division of data in 2 or more applications and the very objective of having one source of truth is defeated.
- Transition of Project Managers. While, all ERP projects have dedicated project managers who go well beyond the go-live to post production support period and make sure that the Live system is stable from bugs perspective before they off board, but often, role and responsibility clarity after that phase, dissolves and there are multiple owners (or shall I say Managers) and very less number of contributors that are needed to take it to the next level of change management, ongoing trainings, user access process, upgrade or enhancement decision processes etc.
- Over dependence on support teams for knowledge management. It is often seen that after setting up a vendor team for providing post production support, organizations depend totally on their teams for preparing and updating the user manuals and other soft training material for users and new employees. Though they claim to review the documents before releasing to the business teams but I have my doubts on the review process. Again, not only the ERP documentation but defining the complete process of knowledge management for the process improvements using the application is a challenge.
- Independent customizations in multi country rollouts. One of my friends, is working in an European company and they implemented ERP in 1999 and never upgraded after that. The implementation was done in 23 countries across the globe and finally when they thought of upgrading the ERP this year, they came to know that their ERP system is not a standard system any more. Every country has got done some or more country specific customizations in the ERP, making this a bunch of disparate systems that do not offer a standard process. The pain that they are going through now for upgrading the ERP is enormous and it has increased their costs multifold. The challenge here is to keep the standard system, standard enough so that future upgrades are done with minimum costs
- ERP is not a magic wand. As soon as we go live with ERP we start expecting it to take over the organization and transform it to a world class organization. We want to be Best in Class as soon as possible, being done so much hard work during the implementation period. Keeping the expectations of business users to realistic levels is another challengethat needs to be addressed.
Likewise there are more Post Go-Live challenges that we all need to consider while making a strategy to realize maximum benefits from your ERP. I do not want to paint a gloomy picture by just highlighting the challenges here. It is not that you only have challenges after Go-Live and ours lives will revolve around them forever. Successful ERP implementation offers multiple opportunities to business beyond the expected benefits. Some of them are:
- Improve the KPIs. Now that the as-is values of the KPIs are known to you, it is easier to make desired KPI model by benchmakring with the Best in Class and charting a roadmap to reach there
- Rationalize the staff. It provides you with an opportunity to relook at the staffing levels and use them in more productive areas as automation will free up some staffing hours/days
- Flank it with Best in Breed Systems. ERP might not be there in its present role for eternity. While ERP will give a standard platform and one source of truth, there are chances that it is not the best system in all areas. It might be the best transaction system (or financial reporting system) but still might need to be complimented with best in breed systems for niche areas like service parts planning, demand driven supply chain planning, procurement auctions or product lifecycle management for that matter. Having an ERP system tells you where you are currently and what do you still miss to reach the pinnacle of success
I have not captured the direct benefits that an ERP will provide to an organization but I understand that these opportunities sometimes look like the benefits (due to a thin line difference between them) only but believe me, you will still need to do some good amount of work to explore these as against the direct benefits that you will get after implementing ERP successfully. Essentially, the challenges are required to be part of our overall Benefit Realization Strategy from ERP and opportunities are the areas which show the way for reaping the by product benefits from ERP.
Oracle Reports (Report 6i) – Technical – Interview – Question Answers
Oracle Reports (Report 6i) – Technical – Interview – Question Answers
Question: How many types of columns are there and what are they
Formula columns: For doing mathematical calculations and returning one value
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable
Question: Can u have more than one layout in report
Answer: It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
Answer: Yes it is possible to run the report without parameter form by setting the PARAM value to Null
Question: What is the lock option in reports layout
Answer: By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.
Question: What is Flex
Answer: Flex is the property of moving the related fields together by setting the flex property on
Question: What is the minimum number of groups required for a matrix report
Question: How many different triggers are available in Report?
Answer: There are five types of triggers in report 6i
1) Before report trigger
2) After report trigger
3) Before Parameter trigger
4) After parameter trigger
5) Between pages trigger
Question: What is the Firing sequence of report trigger?
Answer: First the before parameter trigger will raise, after firing this trigger parameter form will displayed, after passing parameter after parameter trigger will fire query will parsed & then before report trigger will fired then if there are number of pages in your report then the between pages trigger will fired but it will fire between first & second & so on pages but it will not fired in reverse condition the after report trigger will fire after closing the runtime parameter form is closed.
Question: What is bind variables?
Answer: Bind variables are used in report 6i for replacing the single parameter in the select statement
Question: What is lexical parameter?
Answer: Lexical Parameter is used to replace the where, order by ………conditions at run time.
Question: What are different types of column in reports?
Answer: There are three types of columns in the report 6i these are:
1) Placeholder Column – Placeholder column is used to store a value for a variable.
2) Formula Column
Answer: The minimum of groups required for a matrix report are 4
Question: Can u run the report with out a parameter form
Oracle Application – Top useful SQL Queries
Friends, here some of quite useful regular sql queries in oracle applications.
Query 1: Select responsibility name along with application name
SELECT application_short_name ,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt , fnd_application fa
WHERE fa.application_id = frt.application_id;
Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1
SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = &resp_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';
Query 3: Get User name and related assigned responsibilities
SELECT distinct u.user_id, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name 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
order by 1;
Query 4: Get Request Group associate with Responsibility Name
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 5: Gets Form personalization listing
Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables
applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
Query 6: Query to view the patch level status of all modules
SELECT a.application_name,
DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
Query 7: SQL to view all request who have 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 8: SQL to view all requests who have 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 9: SQL to view all types of request Application wise
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
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,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
Query 10: SQL to view concurrent request processing time, quite useful
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;
Oracle Application – General Interview (Technical – Functional Questions)
Oracle Applications / Financials – General Questions for interview or to increase overall awareness on some of the concepts you already aware and worked upon. this is terms as refreshing some of those.
Oracle Application – General Interview (Technical – Functional Questions)
What are different period types?
You use accounting period types to define your accounting calendar. Different Accounting Periods are-
You use accounting period types to define your accounting calendar. Different Accounting Periods are-
- General Ledger Periods (attached to Set of Books),
- Purchasing Periods (Operating Unit Specific),
- Cost Periods (Inventory Organization Specific),
- AP Periods, and
- AR Periods
If it is accounting period types, you can define your own period types to use in addition to the General Ledger standard period types Month, Quarter and Year. You use these period types when you define the accounting calendar for your organization. However the year type should be either Calendar or Fiscal. We have different Period types-
1) 13 Month (13 Month Calendar with An Adjusting Period).
2) Annual.
3) Monthly.
4) Quarterly.
5) Semi Monthly.
6) Weekly.
What types of invoices are there in AP and AR?
Types of Invoices are:
Standard
Credit Memo
Debit Memo
Expenses Report
Prepayment
Mixed
AWT (Automatic Withholding Tax Invoice)
Interest Invoice
PO Default
Quick Match
Mixed
Recurring Invoice
Arrears Invoice
Advance Invoice
Guarantee
Charge Back
Deposit
What is the difference between cross-validation rules and security-rules?
Cross validation rules prevent all the responsibilities/users entering invalid account combinations. Security rules are attached to specific responsibilities to prevent using few of the segment values for a segment. Cross Validation Rule: Rules that define valid combinations of segment values a user can enter in an account. Cross-validation rules restrict users from entering invalid combinations of account segment values. Security Rule: It determines the accounting transaction user can view at different levels of hierarchy, such as at Site Level –>Application Level –> Responsibility Level –> User level. Cross Validation Rule applies across the chart of account where Security Rule is applicable at Responsibility Level or User Level. Cross Validation Rules are meant for defining the set of combinations that are excluded from the global set whereas Security Rules are to restrict Users/Responsibilities. Cross-Validation Rules are to control the certain code combinations. Security Rules are to control the certain segment values.
How many ways can you enter a journal in GL?
1. Manual entry 2. Subledger Entry 3. Spreadsheet Entry 4. Recurring Entry 5. Mass Allocation
What is a recurring invoice?
Recurring Invoice is a type of invoice which occurs at definite intervals of time. The best example for a recurring invoice is Rent paid to the Owner.
What are the general setup steps for AP, AR, and GL?
For GL:
1. Define Chart of Accounts2. Define Calendar
3. Define Currency
4. Create Set of Books
For AP:
1. Define Suppliers (Creditors)
2. Invoice
3. Look up codes
4. Selection of Set of Books
5. Payment Terms
6. Financial & Payable Options
7. Define Banks
For AR:
1. Flexifield
2. System Options
3. Payment Terms
4. Open period
5. Auto Accounting
6. Transaction Type
7. Transaction Source
How do we integrate AP or AR to GL ?
There is a program in payables to transfer AP to GL is “payables transfer to general ledger” GL is like AR->GL<-AP, AR and AP both transfer the data in GL. AR Contains all Invoices/Receipts /CM/DM and same way AP also have AP Vouchers. Yes, there is a clear Integration of AP/AR with GL.
The integration is like this: all the accounting created in subledgers (AP/AR) are transferred to Gl. The journal created from AP/AR are clearly identified in GL according to their batch names and journal names.
What is the difference between GL date and GL posted date in GL ?
GL date is the date used to determine the correct accounting period for your transactions where as the GL posting date is the date when the journal entry is posted the GL. GL date is the date used to determine the correct accounting period for your transactions where as the GL posting date is the date when the journal entry is posted the GL, also Called Transaction Date & Posted Date.
In GL there is no org id. So how can we differentiate the data different operating units when no other modules are given ?
HR data is at business group level. GL Data is differentiated based on set of books id. AP and AR data is mostly at operating unit level. Inventory, BOM, WIP data is at inventory organization level. In the gl_sets_of_books we have the set_of_books_id column. This column is enough to differentiate between one operating unit with the other. If you see the multiorg structure of Oracle Apps modules, we’ll see that GL is setup at set of books level. Now you generally won’t get data at OU level. OU data sums up at a higher SOB level. Please follow the below structure if you want more clarification top->bottom HR org->SOB->OU->inventory org
At what stage, the subledger data is posted to GL?
When Transactions are completed in subledgers data may be posted to GL Basically after entering the transactions, report will be taken to verify the transactions. In case, if approval is needed, it is approved after verifying the transactions. Once you are sure that the transactions are correct, the same can be posted to GL. Once it is posted, most of the information for the posted transaction can not be modified in the subledger. In case of any wrong entry, you need to follow the reversal procedure. Practically, the verification of transactions are done only during the initial stages after implementation. Once the system becomes stable, it is not followed strictly. Note: Make sure that GL period is open for the transaction GL date. Close all the periods in subledger after you reconciled all your transactions. Once you close the period, sweep program will run and all the un-posted and future entries will be transferred to next open period. Once this is done run the GL Transfer program and Journal import programs to complete the transaction transfer process. Once this is done you will find un-posted journal entries in GL you can post the same or reverse the same if you find something is missing. GL periods should also been opened and the GL period should be closed at the last.
Why cant interest rates are set uniquely supplier wise in payables module, whereas interest rate is applied to all suppliers the same rate?
Terms and conditions differ with each supplier.
What is FSG and its use?
Financial Statement Generator is a powerful report building tool for Oracle GL. FSG is used by the management for the decision making in the financial sector of the firm or an enterprise.
Uses of FSG :1. Generate financial reports such as income statements and balance based upon the data in your GL.
Note: If you have average balance processing enabled in your set of books, you can report on functional, foreign–entered, or translated average balances.
2. Define your reports with reusable report objects, making it easy to create new reports from the components of reports you’ve already defined. 3. Design custom financial reports to meet specific business needs.
4. Print as many reports as you need simultaneously.
5. Print the same report for multiple companies, cost centers, departments in the same report request.
6. Schedule reports to run automatically.
7. Produce ad-hoc reports whenever you need them.
8. Print reports to tab-delimited files for easy import into client-based spreadsheet programs. In addition, you can use the Report Wizard feature of Applications Desktop Integrator to design and submit your financial reports, as well as view the results, directly from a spreadsheet. 9. Define segment value security rules to restrict financial information contained in FSG report output generated by specific users and responsibilities. Note: To apply segment value security rules, the profile option FSG: Enforce Segment Value Security must be enabled
Explain ADI and its features?
ADI means application desktop integrator. It is a excel file which allows you to transfer the data pertaining to General Ledger, Fixed Assets and Budget to oracle apps and allows to run a request. ADI functionality provides an alternative to users who prefer to load information directly from Microsoft Excel rather than using the Oracle user interface. It should read Oracle Interface Programs (batch jobs) rather than Oracle User Interfaces. Broadly following are the feature / elements of ADI
1. Journal Wizard
2. Budget Wizard
3. Report Wizard.
4. Account Hierarchy Editor.
5. Analysis Wizard.
6. Request Center
ADI allows users take advantage of many of the data-entry shortcuts of a spreadsheet, such as copying and pasting cells, dragging and dropping ranges of cells and using formulas to calculate journal line amounts. ADI validates the data entered against the accounts, security rules and reference information that are defined in the General Ledger (GL).
What is EDI and its functions?
EDI – Electronic Data Interchange, to send the data to another server/destination via EDI server.E-Commerce Gate Way is the one of the Module in Oracle Apps. EDI (Electronic Data Interchange) is way of exchanging the Business documents like Sales Order, Invoice, PO etc., between two business entities in agreed standard format like ASCII X12 format. In oracle application, business documents may be referred as 850POI (purchase order Inbound), 810INO (Invoice Outbound) etc.. There are several third party sources are available which may be use in mapping of several documents from Oracle Format to X12 and vice versa. Some of them like Sterling Commerce, Klein Schmidt…. EDI is a toll where in whenever the customer is sending the PO it gets saved in this toll, again when the supplier after supplying the material will send an invoice through EDI, wherein the EDI of the customer will match the PO with the invoice and the invoice will get processed automatically, in case if it is not matching it will be in the error sheet
Oracle Financials Accounting – Interview Technical Fucntional Questions
1. What are the application objects that support View Accounting and Drilldown?GL_Import_Reference_Table (modified) For Example Invoices imported from Payables into GL goes to these tables from GL_Inerface table.
GL_SL_LINK_ID
GL_SL_LINK_TABLE
GL_JE_LINES (modified)
GL_SL_LINK_ID
GL_SL_LINK_TABLE
GL_JE_LINES (modified)
New views in the database:
FA_AEL_GL_V
FA_AEL_SL_MRC_V
FA_AEL_SL_V
FA_AEL_GL_V
FA_AEL_SL_MRC_V
FA_AEL_SL_V
2. Where in Oracle General Ledger 11i can Drilldown be accessed?
You can drilldown from GL Account Inquiry window and the GL Journal Entry and GL Journal Inquiry windows. (N) Tools -> Drilldown Open the Journal entry in GL and go to Tools – drilldown, its shows u the origin of the journal entry. It is used only for viewing the origin of the journal entry.
3. What are the Release 11i Sub-ledger drilldown features?Expanded Subledger drilldown to other subledgers. View Accounting Lines window.
4. What are recurring invoices? What are AP setup steps?
Some times suppliers would not be sending any invoices, but still the payment have to be made to him. Ex: rent, lease rentals. In this situation we have to create invoice every period wise. For that purpose we have to create one recurring invoice template. Template means with one master copy creating the multiple invoices. Here we are creating the one invoice master copy is formally known as recurring invoice or recurring invoice template.
Setup: 1) Create one special calendar
2) Create one full distribution set
3) Enter payment terms in the recurring invoice window
4) Enter the template no., first invoice amount, special invoice amounts.
2) Create one full distribution set
3) Enter payment terms in the recurring invoice window
4) Enter the template no., first invoice amount, special invoice amounts.
Recurring Entries are of 3 types-
1. Standard,
2. Skeleton,
3. Fornulae Based
1. Standard,
2. Skeleton,
3. Fornulae Based
In GL Module
1.Define Formula Batch (e.g. ABC Rent batch)
2.Enter Lines (Here u have both Debit as well as Credit lines)
3.Generate Recurring period
4.Review Journal Batch
5.Post the batch
1.Define Formula Batch (e.g. ABC Rent batch)
2.Enter Lines (Here u have both Debit as well as Credit lines)
3.Generate Recurring period
4.Review Journal Batch
5.Post the batch
1. Standard Recurring Journal: It is used for same accounts & same amounts e.g.
Utilities Dr
Cash Cr
Utilities Dr
Cash Cr
2. Skeleton Recurring Journal: It is used for same accounts but for different amounts, e.g.
Recurring Fee Dr
Cash Cr
Recurring Fee Dr
Cash Cr
3.Formule Based Journal: It is used for different accounts with different amounts, e.g.
Salaries Dr
Cash Cr
Salaries Dr
Cash Cr
5. If any conflict occurs in FSG who will override; Column Set or Row Set?
The override component is row set. However some times it depend on the column set also.
The override component is row set. However some times it depend on the column set also.
6. What is the difference between discounts and adjustments?
Discount refers to the payment terms or on quantity (bulk order discount) ordered. In the invoice received from supplier, discounts term is specifically stated, e.g. “If paid with in 15 days discount @ 2%”, what you need to do is pay 2% less then the original invoice amount, however caution should be taken that the freight amount should not be considered while calculating the discount. Adjustment could be for various reasons- Over priced invoice, short delivery of quantity as per goods received note, poor quality etc. Agreed upon amount will be deducted from the invoice, while making the payments. In AR, discount is given to promote the business and cash flow. Adjustments are made to adjust the account balances, which is not possible in other means.
Discount refers to the payment terms or on quantity (bulk order discount) ordered. In the invoice received from supplier, discounts term is specifically stated, e.g. “If paid with in 15 days discount @ 2%”, what you need to do is pay 2% less then the original invoice amount, however caution should be taken that the freight amount should not be considered while calculating the discount. Adjustment could be for various reasons- Over priced invoice, short delivery of quantity as per goods received note, poor quality etc. Agreed upon amount will be deducted from the invoice, while making the payments. In AR, discount is given to promote the business and cash flow. Adjustments are made to adjust the account balances, which is not possible in other means.
7. What are cycles of GL, AP, and AR?
GL Accounting cycle can be further elaborated like this…
1. Open Period.
2. Create Functional and foreign journal entries.(including the journal import from legacy systems and subledgers).
3. Reverse journal entries.
4. Post the journals.
5. Review and correct the balances.
6. Revalue foreign currency balances.
7. Translate foreign currency balances.
8. Consolidate set of books.
9. Run accounting reports.
10. Close the accounting periods.
GL Accounting cycle can be further elaborated like this…
1. Open Period.
2. Create Functional and foreign journal entries.(including the journal import from legacy systems and subledgers).
3. Reverse journal entries.
4. Post the journals.
5. Review and correct the balances.
6. Revalue foreign currency balances.
7. Translate foreign currency balances.
8. Consolidate set of books.
9. Run accounting reports.
10. Close the accounting periods.
AP cycle ——– Purchase Order –> Receipt –> AP voucher –> Payment made
AR Cycle ——–Sales Order –> Shipment –> Invoice –> Payment receipt
GL cycle ——– Direct GL JE / Transfer subledger data to GL –> Post
AR Cycle ——–Sales Order –> Shipment –> Invoice –> Payment receipt
GL cycle ——– Direct GL JE / Transfer subledger data to GL –> Post
8. What are Summary Accounts and Rollup groups?
Summary Account is an account whose balance represents consolidation of accounts. Rollup group is a collection / consolidation of parent accounts. e.g. Assets is a total of Current assets and Fixed Assets. Current assets / Fixed assets in turn are collection of assets.
Summary Account is an account whose balance represents the sum of other account balances. You can use summary accounts for faster reporting and inquiry as well as in formulas and allocations. Rollup group is a collection of parent segment values for a given segment. You use rollup groups to define summary accounts based on parents in the group. You can use letters as well as numbers to name your rollup groups.
Summary accounts are consolidated balances of accounts and rollup groups is collection of parent accounts.
9. What Sub-ledgers does Oracle General Ledger 11i Drilldown support?
Accounts Receivable, Accounts Payable and Cash Management. Drilldown from Oracle General Ledger 11i also supports Oracle Purchasing module (displays POs and Requisitions) from 11.5.9 version onwards. Drilldown from Oracle General Ledger 11i is supported for Oracle Payables, Oracle Receivables, Oracle Assets (except depreciation), Projects, Purchasing, Inventory, and Work in Process (WIP).
Accounts Receivable, Accounts Payable and Cash Management. Drilldown from Oracle General Ledger 11i also supports Oracle Purchasing module (displays POs and Requisitions) from 11.5.9 version onwards. Drilldown from Oracle General Ledger 11i is supported for Oracle Payables, Oracle Receivables, Oracle Assets (except depreciation), Projects, Purchasing, Inventory, and Work in Process (WIP).
Shivmohan Purohit
Oracle Payables Useful Tables
Hello Friends , here is some of quite commonly used AP (Payables) tables and their usage. There are many other tables also in AP but here i am putting only few commonly used tables. for other table if needed we can dig furthur. Let go through below article and let me know if it useful.
1- AP_INVOICES_ALL
2- AP_INVOICE_DISTRIBUTIONS_ALL
3- AP_PAYMENT_SCHEDULES_ALL
4- AP_HOLDS_ALL
5- AP_AE_LINES_ALL
6- AP_AE_HEADERS_ALL
AP_INVOICES_ALL
AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice you enter. An invoice can have one or more invoice distribution lines. An invoice can also have one or more scheduled payments. An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses the INTEREST type invoice for interest that itcalculates on invoices that are overdue. Your Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution line information that you enter for invoices. There is a row for each invoice distribution. A distribution line must be associated with an invoice. An invoice can have multiple distribution lines. Your Oracle Payables application automatically creates rows in this table when:
1) you choose a distribution set at the invoice level
2) you import expense reports
3) you match an invoice to a purchase order or receipt; ituses information from the matched purchase order or receipt
4) you import invoices via the Open Interface Import process
5) you select to automatically calculate tax
6) you select to automatically do withholding.
Each invoice distribution line has its own accounting date. When you account for an invoice, your OraclePayables application creates accounting events, accounting entry headers and accounting entry lines for those distribution lines that have accounting dates included in the selected accounting date range for the Payables Accounting Process.The accounting entries can then be transferred over to General Ledger by running the Transfer to General Ledger process which creates journal entries. Values for POSTED_FLAG may be Y for accounted distributions or N for distributions that have not been accounted. Values for ACCRUAL_POSTED_FLAG may be Y if distribution has been accounted and system is set up for accrual basis accounting or N if either distribution has not been accounted or accrual basis accounting is not used. Values for CASH_POSTED_FLAG may be Y if distribution has been accounted and system is set up for cash basis accounting, N if either distribution has not been accounted or system is not set up for cash basis accounting or P if distribution has been partially accounted in the cash set of books. The MATCH_STATUS_FLAG indicates the approval status for the distribution. Values for the MATCH_STATUS_FLAG can be null or N for invoice distributions that Approval has not tested or T for distributions that have been tested or A for distributions that have been tested and approved. Invoice distributions may be interfaced over/from Oracle Assets or Oracle Projects. Your Oracle Payables application sets the ASSETS_ADDITION_FLAG to U for distributions not tested by Oracle Assets; Oracle Assets then adjusts this flag after it tests a distribution for assignment as an asset.
To avoid the same invoice distribution being interfaced to both Oracle Projects and Oracle Assets, you must interface any project–related invoice distribution to Oracle Projects before you can interface it to Oracle Assets. If the project–related invoice distribution is charged to a capital project in Oracle Projects, Oracle Projects sets the ASSET_ADDITION_FLAG to P when the PA_ADDITION_FLAG is set to Y, Z or T. Oracle Assets only picks up invoice distributions with the ASSET_ADDITION_FLAG set to U and if project–related, with the PA_ADDITION_FLAG set to Y, Z, or T. PA_ADDITION_FLAG tracks the status of project–related supplier invoice distribution lines and expense report distribution lines.
For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project–related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Oracle Projects; see QuickCodes listing for all the errors. You must correct the rejection reason an try to retransfer the line. For supplier invoice adjustment lines interfaced from Oracle Projects to Oracle Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T. For expense report distributions interfaced from Oracle Projects to Oracle Payables via Invoice Import, this value is set to N. This row is never picked up by the Interface Supplier Invoices process based on the AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE = EXPENSEREPORT. For expense report adjustment lines interfaced from Oracle Projects to Oracle Payables which net to zero with another line, thisvalue is set to T. Both lines are associated with the original invoice by the Oracle Projects Interface Expense Reports to AP process. Values for the ENCUMBERED_FLAG are as follows:
– Y indicates aregular distribution that has been successfully encumbered by Payables;
– W indicates a regular distribution that has been encumbered in advisory mode even though insufficient funds existed;
– H indicates a regular distribution that has not been encumbered because it was put on hold;
– Nor null indicates a regular line that has not been encumbered because it has not been looked at yet;
– D is the same as Y for a reversal distribution line;
– X is the same as W for a reversal distribution line;
– P is the same as H for a reversal distribution line;
– R indicates a line to be ignored by encumbrance and approval code because neither the original nor the reversal distributions were looked at and they offset each other so, they can be ignored.
AP_PAYMENT_SCHEDULES_ALL
AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch. Values for HOLD_FLAG may be ’Y’ to place a hold on the scheduled payment, or ’N’ not to do so. Values for PAYMENT_STATUS_FLAG may be ’Y’ for fully paid payment schedules, ’N’ for unpaid scheduled payments, or ’P’ for partially paid scheduled payments. For converted records, enter a value for AMOUNT_REMAINING.
AP_HOLDS_ALL
AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non–matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice–shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table. This table holds information referenced by the Invoice Holds window. In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Approval), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID,and HOLD_LOOKUP_CODE.
AP_AE_LINES_ALL
An accounting entry line is an entity containing a proper accounting entry with debits or credits both in transaction currency as well as functional currency along with an account and other reference information pointing to the transaction data that originated the accounting entry line. An accounting entry line is grouped with other accounting entry lines for a specific accounting entry header. Any such group of accounting entry lines should result in balanced entries in the functional currency.
AP_AE_HEADERS_ALL
An accounting entry header is an entity grouping all accounting entry lines created for a given accounting event and a particular set of books. An accounting entry header can either be transferred over to GL or not at all. That is, either all its accounting entry lines are transferred or none at all. The transferred to GL status is marked in the GL_TRANSFER_FLAG. Possible values for GL_TRANSFER_FLAG are Y, N, or E. Y indicates that the accounting entry header has been transferred to GL. N indicates that the accounting entry header has not been transferred to GL due to 2 possible reasons: either the transfer process has not run or it has run but the accounting entry had an accounting error on it. E indicates that an error was encountered during the transfer to GL process.
Oracle General Ledger and Budgeting
What is planning budget ?
The plan for the future expenses is planning budget. It is a paper work. There is no funds requirement. It does not require journals. There are no restrictions for estimating of funds. It is a budget through which you cannot exercise budgetary control. But u can compare your actual with budgets through inquiry window.
What is Consolidation in GL?
It Consolidates the Subsidiaries & Parent financial information. At a point of time we can check the Performance of the Companies overall Performance. When we have multiple setup and combine primary and secondary books getting the financial reports is called consolidation We will use consolidation reports at the time of period end closing Consolidation is the Process of combining the Financial results of different companies typically combining subsidiary accounting information into a parent company.The main purpose of consolidation is for reporting purpose. Consolidation is the period-end process of combing the financials of separate subsidiary with the pay rent company to from single ,combine statement of final result consolidation is one of the reporting tool.consolidation may map the subsidiary set of books to parent set of books, even if the set of books values are different using segment rule,accounting rule It is the process of combining the financial results of multiple companies into one financial statement the company may have different sobs in different countries,so we will collect all the financialtransactions(sob) in to one set of book ,that is parent sob then the company easely findout all the finacial year endingresullts in their functional curency,that purpose multiple companies are using consolidation I think consolidation can be done with two ways that is FSG, GCSÂ for FSG it can be done when both parent SOB and Subsidary SOB have Currency, Calender, COA, same then we go for FSG.In Global consolidation System currency, coa, calender are different in parent sob and subsidary sob we have to go for GCS.I am Saikrishna actaully I am pursuing my oracle financials in hyd i just completed one of my module GL institute.
How to import data to General Ledger by Feeder System ?
There are three ways:
1- You can upload Journal Entries using ADI (Application Desktop Integrator) functionality. ADI is used to interface spreadsheet uploads with Oracle applications data. ADI allows users take advantage of many of the data-entry shortcuts of a spreadsheet, such as copying and pasting cells, dragging and dropping ranges of cells and using formulas to calculate journal line amounts. ADI validates the data entered against the accounts, security rules and reference information that are defined in the General Ledger (GL).
2- Using Journal Import, we can bring the data from the feeder system; to import the Transactions from External Feeder Systems following are the steps-1. Populate the data from External system to GL_Interface table (GL_INTERFACE table acts like a bridge between the external system and the Oracle General Ledger Base tables where journals are stored). 2. Run the Journal Import process to import the date to base tables (Errors in Journal Import are listed in Execution Report)
3. From any sub module run individual module transfer to GL program Ex: Payables transfer to General Ledger
After creating Journal Source how do we approve to the specific Set of Books ?
To approve journals from specific source, while creating the source ‘Require Journal Approval’ check box should be enabled. To approve all the journals that come from different sources In the Set Of Books window under ‘Journaling’ tab ‘journal approval’ should be enabled.
While creating combination of accounts what is the use of PRESERVED check box ?
If we check preserve for a account code combination then even if you disable this account segment value, the inherited (parent. child) segment values will not be disabled. If it is unchecked all the related segment values will be disabled. Preserved check box will help you to retain the original character of the General Ledger code combination, even after u change the nature of the segments during maintenance of the GLCC.
What is the MD form 50 ?
MD50 is a Functional design document, designed by the functional consultant. After interacting with the client, functional consultant will prepare this document. In this we will compare the present business process and client requirement. It is mainly used for Application Extensions and Interface functional design.
What are the journal entries in Procure to Pay Cycle ?
When the Material Received at the Gate-
Receiving Accrual A/C — DrMaterial AP Accrual A/C — Cr
When the Material delivered to The Inv Org Material
A/c Dr Purchase Price Variance A/c Dr Receiving A/c Cr
When the Invoice Is enterd into the Payable with Matching Of PO Material
Ap Accrual A/c Dr Invoice Price Variance A/c Dr AP Liability A/c Cr
Material Receipt
Receiving Accural Dr AP Accural Cr
On completion on inspection and accepting of goods
Inventory Dr Receiving Accural Cr The Above given 2 entries are in PO module
On entering Invoice in AP
AP Accural Dr Accounts Payable Cr
On Making payment
Accounts Payable Dr Cash/Bank Cr 1. On Entry of PO ________ No accounting impact On Material
On Payment
Accounts Payable Dr’ Cash/Bank Cr
When we raise the Purchase Order entry
Material in transit a/c Dr To AP Accrual A/c
Comments
Post a Comment