, , ,

General Ledger Interview Questions in R12 – Part 2

1.  What responsibility should I use when doing the set up for General Ledger?
 
Use a seeded responsibility like ‘Oracle General Ledger Super User’.
You may also need to use the System Administrator responsibility.
2.  What are the pre-requisites required to define a new calendar?
 
According to your business needs you need to decide the calendar
type required i.e. monthly, weekly or biweekly, the number of periods,
adjusting periods and the maximum number of periods within a fiscal year.
3. What are the pre-requisites required to define a new Ledger?
 
Define a Calendar, Chart of Accounts and enable the functional Currency  and convention of subledger accounting method.
 
4. Why must I check the calendar definition before assigning to a Ledger?
 
The calendar definition cannot be changed once it is assigned to a
set of books so it is very important to check that the calendar
definition is suitable to the specific business needs, has been defined
correctly without any gaps and that the period type is in accordance
with the business requirements.
5.  What General Ledger profile options need to be set up for using
    a new ledger with a responsibility.
 
The profile option GL Ledger Name.
Once this profile option is set.
 
You may also need to set up other profile options as mentioned in the
Oracle General Ledger User’s Guide.
 
6.  Can I define more than one Ledger?
 
Multiple Ledgers may be defined within Oracle General Ledger,
however for a given installation of Oracle subledgers you can use
only one Ledger (unless you are multi-org).
7.  What type of Ledger do I need: Primary, Reporting or None?
 
The Primary and Reporting options are used with the MRC feature of
Oracle Applications.  If you are not using MRC then you may choose
‘None’
 
8.  Are there any mandatory accounts that are required for the Ledger definition?
 
A valid Retained Earnings account needs to be assigned on the Ledger
form. Depending on the other options you may choose, such as
Translation, Suspense Accounting, Encumbrance, etc. you need to enter
valid accounts for those as well.
9.  What is Dynamic Insertion? Is it required during setup?
 
Dynamic insertion is an Accounting Flexfield feature whereby you can
allow users to create new combinations upon entering a flexfield
combination.
For every new flexfield combination entered, a unique code combination
id is also created which is used to enter and retrieve data.
You may enable or disable this Accounting Flexfield feature at any time,
on the Key Segments form.
10.  Do I need Cross Validation rules set up before entering journals in
     General Ledger?
 
If you plan to restrict certain accounts to be used only with certain
company or cost centers then you have to plan on this and define cross
validation rules before the code combination id is created.
Once the code combination id is created, new cross validation rules
will not restrict the account’s usage.
11.  Do I need to turn Suspense posting on?
 
It is not mandatory but if suspense posting is checked, a valid suspense
account must be entered in the Ledger form. It is used to balance
journal entries for which the entered amounts are out of balance.
12.  Is there an automated tool to load the account segment values
     in Oracle General Ledger?
 
No, the values have to be entered using the Segment Values form in
Oracle General Ledger.
 (N) Setup > Financials > Flexfields > Key > Values
References
Oracle General Ledger User Guide
13.  Do I need to set up Journal Approval?
 
It can be set up at any time and is not a mandatory step for completing
the General Ledger setup.
14.  What does the check box Balance Intercompany mean on the ledger form?
 
If journals are created for different balancing segment values, the
journal must be balanced by balancing segment value (debits must equal
credits for a particular balancing segment value). Checking this option
on the ledger form will allow GL to balance these journals by using
the default intercompany accounts set up in Oracle General Ledger.
If it is not checked, the journal must be balanced (by balancing segment
value) before it is imported or posted.
15. Is there any setup at the journal source level to view subledger data in GL?
 
Check the option ‘Import Journal Reference’ for each journal source/category
combination for which you wish to transfer the subledger reference data
from the subledgers during the Journal Import process.
Please note that the following may also have to be verified to view subledger
data in GL:
 
1. Subledger setup.
2. The parameters for the transfer process from the subledgers.
3. Journal import process parameters.
 
16.  What is the option Rounding Differences Account in the ledger form?
 
When transactions are entered in foreign currencies the conversion to
the functional currency may cause rounding differences.
The Rounding Differences account is used to balance any journal entry
for which the converted amounts are out of balance.
When this option is checked, a valid account combination must be
defined to track the rounding differences.
 
17.  Is there any additional maintenance for Sources and Categories in General
     Ledger after these items are initially setup?
 
Once General Ledger Sources and Categories are setup, there is no additional
maintenance.
 
18.  How to review GL: Ledger name  profile options at all levels ?
 
Responsibility = System Administrator
Navigation = Profile/System
 
  1. Check the desired display boxes:  Site, Application, Responsibility and User
    
  2. In the Profile field, enter GL Ledgers% and click on the
     [Find] button.
 
19.  Can I change the functional currency for an existing Ledger?
 
It is neither recommended nor supported to change the currency of a

Ledger that is currently in use.

Questions and Answers

What is the best source of information regarding Definition Access Sets?

See the Release 12 General Ledger Implementation Guide, beginning on page 1-137: Definition Access Sets.

How to properly assign/remove Definition Access Sets in Oracle General Ledger?

Follow Document 415901.1 for steps on how to remove the Definition Acess Sets, but please note that DAS definitions cannot be deleted. These will remain in the Definition Access Sets -> Define form but will not have the definition name assigned to it (if all definitions were removed – in this example we only created one – then deleted it).

R12 Accounting Flexfield and GL Ledger Flexfield are not matching, is this a problem?

As documented in the General Ledger User Guide and Implementation Guide, the new GL Ledger Flexfield is automatically created and maintained. Users should ignore this Flexfield and never change it. Development has confirmed that the value for the dynamic insertion will not cause a problem. So if you notice any inconsistencies between Accounting Flexfield and GL Ledger Flexfield can be completely ignored.
See Document 418154.1 for more information.

How can I setup a Legal Entity country, if the country is not available in LOV?

Navigate into Legal Entity manager responsibility ->Setup -> Jurisdictions -> create identifying jurisdiction by setting identifying to Yes and territory to the country required, enter any name. Then enter registration codes. If the jurisdiction is already set to yes, then go to System Administrator Responsibility> go to Profile options> select the application name as General Ledger> then select the user by your user> in the profile choose <Default Country> , change the default country to the country required.
See Document 438089.1, Document 444633.1 for more information.

How do I remove Balancing Segment Value assignments from a Ledger?

Initially it was not possible to remove a BSV assigned to a Ledger once the setup has been complete, but it was delivered via recommended Patch 7529614:R12.GL.A.
However, this functionality does not apply to a Legal Entity. There is no possible way to remove a BSV assigned to a Legal Entity once the setup is complete.

Can I change the Ledger Currency after setup is complete?

No, it is not possible to change the ledger currency after the accounting setups have been completed. Same as in R11, in R12 the chart of accounts, calendar and currency cannot be changed after the setup has been completed and any attempt to perform such a change in the tables will not be supported and is highly un-recommended.
See Document 556220.1 for more information.

Can I enable Average Balances for a ledger after the ledger setup is complete?

No, it is not possible to enable average daily balances for a ledger once the ledger has been saved. Once the ledger setup is complete, the option to enable average daily balances is not available as part of the accounting setup manager.
See Document 734099.1 for more information.

Can I change the Subledger accounting method (SLAM) in the ledger definition after going live?

The accounting method should not be changed without advice from the subledger support team. Changing it can cause severe corruption, if, for example, you go from standard accrual to MFAR or the other way around. Please log a service request with the relevant subledger team to get further advice on whether the change is possible.

Can I delete, disable or end-date Primary Ledgers?

Document 782244.1 says that it is not possible to delete or end date the primary ledger whenever created and completed.
Yet, you can workaround this through the responsibilities – create a new Data Access Set, to prevent access to the ledgers that you wanted to delete and only allow access to the Primary Ledger required and attached this Data Access Set to the users responsibilities.

Can a Secondary Ledger be associated with more than one Primary Ledger?

No, a secondary ledger can only be associated with a single primary ledger.
See Document 735468.1 for more information.

How do I disable a Reporting Ledger?

Disable the Reporting currency from Accounting Setup Manager. In Accounting Setup Manager disable the conversions to the reporting ledger.
Create a new ledger set and a new data access set that contains only the Primary ledger
Update the GL: Data Access Set profile option at the responsibility level with this new data access set.See Document 563546.1 for more information.

How do I disable a Secondary Ledger already created?

Query for your Primary Ledger from the ASM and click on the “Disable” icon next to the secondary ledger you in the Secondary Ledger table.
See Document 761380.1 for more information.

How do I set up the Responsibilities and Data Access Sets needed for a Primary Ledger and Secondary Ledger, when the ledgers have different charts of accounts? Can a Ledger Set be used in this case?

All ledgers in a Ledger Set must share the same chart of accounts and accounting calendar/period type combination. So a Ledger Set cannot be used in this case, where the charts of accounts are different. Instead, separate responsibilities must be used for access to each ledger. See Document 460654.1 for more information.


Can I change Primary Ledger to Secondary and Secondary to Primary?

No, changing ledger types is not permitted for the time being. The only way is to create new ledger after R12 implementation and use consolidation to put the ‘history’.
See Document 603624.1 for more information


How to generate Reporting Sequence?

The ledger is setup to have Sequencing Context defined for GL Period Close – GL Journal Entry or GL Period Close – Subledger Journal Entry.
See Document 744962.1 for more information.

How can I post a journal in an error status? It does not show in the post journal batches screen.

The Posting Journal Batches screen will only display journals available for posting. A batch in error is not available for posting. If you cannot clear the error you need to post from the journal entry screen.
For more information see :
    • Document 1390899.1 Troubleshooting Journal Batch Posting Status with Error%
    • GL Users Guide: Reviewing the Batch Posting Status
    • GL Users Guide: Correcting Batch Posting Errors

What are the batch posting error statuses?

Error1: The batch has a control total violation
Error2: Selected for posting to a period that is not open
Error3: Showing no journal entries for this batch
Error4: Showing journal control total violation
Error5: Showing multiple problems preventing posting of batch
Error6: Showing an unbalanced journal entry, and suspense posting is not allowed
Error7: Showing invalid journal entry lines or no journal entry lines for this batch
Error8: Showing unbalanced encumbrance entry without reserve account
Error9: Showing an encumbrance journal entry with no encumbrance type
Error10: Showing unbalanced intercompany journal entry
Error11: Showing unbalanced journal entry by account category
Error12: Funds reservation failed
Error13: Showing invalid period and conversion information for this batch
Error14: Showing journal entry with invalid or inactive suspense account
Error15: Showing encumbrance entry with invalid or inactive reserve account
Error16: Showing journal entry with invalid or inactive intercompany account
Error17 – Showing untaxed journal entry
Error18 – Showing unapproved journal batch
Error19 – Unopened reporting currency period
Error20 – Unopened reporting currency encumbrance year
Error21 – Unable to determine conversion rate to replicate journal
Error22 – Invalid or inactive rounding differences account in journal entry
Error23 – Showing sequence assignment failure
Error24 – Showing cutoff rule violation
Error25 – Unable to validate or create CTA
Error26 – Showing insufficient access to ledger or segment value
Error27 – Showing insufficient access to generated accounts defined for ledger
Error28 – Showing invalid balancing or management segment value for the ledger
Error29 – Showing insufficient access to reporting currency or segment value
Error30 – Showing invalid balancing segment value for generated accounts
Error31 – Unopened secondary ledger period
Error32 – Unopened secondary ledger encumbrance year
Error33 – Unable to retrieve accounts from chart of accounts mapping
Error34 – Unable to determine journal effective date
You can find the possible known causes and solutions for each error code in Document 1390899.1 Troubleshooting Journal Batch Posting Status with Error%.

Why is the Post button grayed out on the Enter Journals form?

In Release 10.7, you must set the profile option ‘Journals:Allow Posting During Journal Entry’.
In Release 11, 11i and 12 this is controlled with Function Security.
See Document 1051909.6: Enable/Disable Posting From Enter Journals Form In General Ledger (GLXJEETN), for more information.Also, the Post button is grayed out also when journal approval is required. This is indicated when the Approve button is enabled for the journal.

Can I delete a batch from the Post Journals form?

No. This form will show all the batches with a status allowing to post.

Is it possible to select all batches for posting on post journal form ?

There is currently no select all option on the Post Journals (GLXJEPST) form to select all batches for posting. An alternative would be to setup Autopost which would select multiple batches but not manually from the form.
See Document 1136093.1

What reports are available for journal posting?

A report is available on the Requests form for the journal post concurrent request run. This report summarizes the status of the request and reports on any errors. A log of the concurrent request run is also available.
The Journals General report can be run for Posted, Unposted or Error status batches.

Can I post out-of-balance journal entries?

Yes, if you turned on suspense posting for the set of books or ledger.
If you enabled suspense posting when you defined the set of books/ledger, General Ledger automatically balances each out-of-balance journal entry against a suspense account you specify for your set of books/ledger.
In R12, you can define additional suspense accounts if you want to balance journal entries with specific sources and categories to corresponding suspense accounts automatically.
References: Oracle General Ledger User’s Guide, Release 12, page 1-192 and Defining Suspense Accounts, Oracle General Ledger Implementation Guide (R12)

Can I post to a parent account?

No, you can only post to detail level accounts. Parent accounts do not hold balances.

How do I find out who posted a journal for internal control and audit purposes?

In 11i, an enhancement request bug:3613631 added a new column POSTED_BY in the gl_je_batches table since 11i.FIN_FP.G. Fixed file versions are :

GLXJEENT.fmb 115.71
glpubr.lpc 115.15
glmcje.lpc 115.7

This is included in base R12 version.

Are security rules enforced in posting?

No. Posting is controlled at the user level. A user can post all journals for a set of books/ledger, or none at all.
This is controlled by function security in Release 11, 11i . In Release 10.7, you can remove the Post form from the user’s menu, to restrict posting.
Posting in R12 will check on the security rules assigned to the user (see Bug:12433242), in particular regarding the intercompany/intracompany account combinations required for balancing.

Can I post to a period prior to my latest open period? Are the balances rolled forward? Is retained earnings account updated?

Yes, this is valid for all versions. From Oracle General Ledger User’s Guide, Release 12, page 1-188:

When you post to an earlier open period, actual balances roll forward through the latest open period; budget balances roll forward through the end of the latest open budget year; and encumbrance balances roll forward through the end of the latest open encumbrance year.

If you post a journal entry into a prior year, General Ledger adjusts your retained earnings balance for the effect on your revenue and expense accounts.

Note: the budgets and encumbrances balances do not automatically roll forward into a new year. This is accomplished by the Year-End Carry Forward program.

I opened the first period of the new year without closing the prior year. Will this cause a problem if posting is still required to the last year?

No. In general, closing a period in General Ledger does nothing more than close that period. There is no additional processing that goes on behind the scenes when you close a period.
Care should be taken in case of using document sequences, as the numbers could start to be consumed in the new period journals.
In R12 the closing period program performs additional work (for example accounting and reporting sequences generation).

Can I drop the GL_POSTING_INTERIM_XX tables?

Yes you can. However, before doing so, you should check the posting, translation, open period or summarization processes that created these files.
The GL_POSTING_INTERIM_XX tables are temporary tables and are normally dropped after the process has completed successfully.TIP: Wait a week or so before you delete the table manually, just in case your processes did not complete.

WARNING: Do not drop the GL_POSTING_INTERIM and GL_SUMMARY_INTERIM tables (i.e., the tables without the numbers at the end of their names), as these tables are used by the General Ledger system.

See the following notes for more information:

Document 416542.1: Is there any Program to Drop Temporary tables which are no more required?,
Document 364040.1: GL_POSTING_INTERIM_xxxx Tables are Not Being Dropped after Successful Posting.

How do I run posting from the command line in debug mode?

You can run GL programs from the command line.
See Document 1031719.6 for more information.

How can I find and delete a batch that is shown on the Posting execution report with the error ‘No journal entry lines for this batch’?

A batch is available for posting on the Post Journals form that has no debit or credit amounts shown for it.
You post the batch and the Posting execution report contains the error ‘No journal entry lines for this batch’.
Now the batch is greyed out on the Post Journals form and I can not find it on the Enter Journals form.The reason for this error is that this batch does not have any journals.
You need to use the Find Batches window to find the Batch on the Enter Journals form. From there, you can delete the batch.
The first window that is displayed when you navigate to the Enter Journals form is the Find Journals window. You can not find the batch using this window, since there are no journals associated with the batch.
See Document 275992.1 for more information.

I submitted posting and receive the following ORA-01403: no data found error in the log file. How do I correct this?

The log file shows the following errors :
glpmpi() 04-MAR-1998 08:00:00
SHRD0030: glpmpi() encountered an error returning from errexit:SHRD0042: Error in ROUTINE: glpmpi, status: 0
SHRD0043: ERROR:
*****************************************************
sqlcaid: sqlabc: 0 sqlcode: 0 sqlerrml: 0
sqlerrmc:
ORA-01403: no

  data found
These errors occur frequently after you apply a major patch or upgrade your database.
See Document 1050587.6: ORA-1403 in glpmpi when posting in General Ledger, for more information.

When I submit a batch for posting I receive the error ‘APP-8058: This form failed to submit your posting concurrent request. Please ensure that your concurrent manager is running.’ What is the cause?

Set the ‘Printer’ profile option. This profile option must be set to successfully submit most concurrent requests.

Posting fails with Error 10: Showing unbalanced intercompany journal entry, or Error 16: Showing journal entry with invalid or inactive intercompany account. How can I correct this and resubmit the posting?

This is an unbalanced intercompany journal.
If automatic intercompany balancing is not enabled, you must manually add lines to the journal to balance the companies (balancing segment values).
If automatic intercompany balancing is enabled, then one of the following is true:An intercompany account has not been defined for one or more balancing segments and dynamic insertion is not enabled.
The system is trying to create an account code combination that violates a cross-validation or security rule.
The system is trying to create an account code combination that contains a segment that is disabled or not postable.
An intercompany account combination is disabled or not postable.

See Document 1012982.102: How to Check The Correct Setup of Intercompany Accounts to Avoid the Error16, for more information on how to resolve this.

Note: Error16 can also be caused by a cross validation rule preventing the creation of a suspense account. Suspense logic is always applied before intercompany balancing in posting. If you have unbalanced journal headers in your batch, suspense posting will first try to balance them with the suspense accounts, i.e. making the total header debit = total header credit, and also making the journal entries balanced within the balancing segment values.

Error10 is received due to Cross Currency line added by Journal Import having the wrong balancing segment value. How can this be resolved?

If Journal Import is not able to create the appropriate account (due to security rules, cross validation rules, disabled account combination, etc.), it will use the original value for the balancing segment value. This results in the unbalanced intercompany journal – Error10. To resolve this, ensure the account combination, if already defined, is enabled. If it does not exist, dynamic insertion must be enabled, and have no security or cross validation rules that would prevent the creation. Bug:1617219 has this information.
/by
, , , ,

General Ledger Interview Questions in R12 – Part 1

1) Can a flexfield qualifier be changed after it has been created?
Ans)  No.

Once a segment qualifier has been designated for a specific segment and has been saved, it will permanently have the attributes with that qualifier.
For example, you accidentally designate the cost center segment as the natural account segment. Even though you do not compile this, the system saves the changes. And once it has been saved, it will have all the attributes designated for the natural account qualifier, even after it has been changed back, resaved with the correct qualifier and compiled. This is the inherent functionality of the software.
Unfortunately, there is no real easy solution for this issue. The only option is to create a new chart of accounts and attach a new set of books.

2) How to delete a segment value?
Ans) There is no supported way to delete a segment value. Segment values can only be disabled not deleted.

3) Is there a way to load values for a specific segment outside of the form?
Ans) iSetup is the Oracle product that provides supported APIs to load values into Oracle Applications flexfields.
To load code combinations ADI may be used. Uploading zero amount journals will create new code combinations.
In this case Dynamic Insertion should be enabled and all account segment values need to exist before the new account code combinations will be dynamically created.


4) What are the different types of Journals in General Ledger ?
Ans)  

1. Functional Currency Jv: This Journal, we enter Local Currency transaction purpose.

2. Foreign Currency Jv: this Journal, we enter other than local currency transaction purpose…before we define exchange rates

3.Suspense Jv: this Journal, whenever debit is not equal to credit that time, we enable in set of books window Suspense button, then it works otherwise it’s not working

4.Tax Jv: this Journal, calculate taxation of Purchased items

5.Reverse Jv: this Journal whenever we enter recurring journal, at the time of we using..We have two methods…one is Debit to Credit and second one is sign (+ to -)

6.Recurring Jv :this one is We define one template, we use Periodically, these are 3 types
1.Standard 2.Skeleton 3.Formula

7.Mass Allocation Jv :Set of Expenses or Set of Revenue allocate different parts using Formula A*B/C 
A is Total Cost Pool..B is Usage Factor…C is Total Usage Factor…

8. Batch JV: Group of Journal we enter at a time, We Define Control Amount

9. Stat JV: This JV we have one side of Amount either debit or Credit…..
 
5)  What is average Balance In Oracle Financials? 
Ans) The Average Balance feature of Oracle General Ledger provides organizations with the ability to track average and end-of-day balances, report average balance sheets, and create custom reports using both standard and average balances. Average balance processing is particularly important for financial institutions, since average balance sheets are required, in addition to standard balance sheets, by many regulatory agencies. Many organizations also use average balances for internal management reporting and
Profitability analysis.
The difference between an average and standard balance sheet is that balances are expressed as average amounts rather Than actual period-end amounts. An average balance is computed as the sum of the actual daily closing balance for a balance sheet account, divided by the number of calendar Days in the reporting period .


6) Is there a limit to the number of periods in a budget year or how many years a budget can span?
Ans) One can define budgetary control for n number of years however, one year can have maximum of 60 fiscal periods)
7) What is a funding budget?
Ans) A budget against which accounting transactions are checked for available funds when budgetary control is enable for your set of books.
8) What is planning budget
Ans) 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.
9) I was able to post a budget journal to a closed period, why? 
Ans) Yes you can do so, reason being budget journal is not linked with your accounting period. Once you have open the budget period then you can book budget journal for that whole period.
 10) What is the specific purpose of assigning Balancing Segment Values to the Legal Entity in Accounting Manager Setup (as once assigned, the same value is not allowed to be selected for any other Legal Entity), if this value is usable for the Operating Unit(s) that does not have this Legal Entity Context? 
Ans) Summary of key facts:

1. Common COA Structure used for Primary and Secondary Ledgers
2. Ledger shared by Multiple Legal Entities
3. Specific Balancing Segment Values assigned to Specific Legal Entity (Overlap not allowed)
4. Specific Legal Entity Vision Operations Assigned to Payables Manager OU for Legal Entity Context
5. User preference set to Access Vision Operations OU by Default in Payables

Conclusion and Findings:
1. Balancing Segment Value Assignment to the Multiple Legal Entities, sharing the same Ledger does not seem to restrict the user of these Balancing Segment Values in the Feeder, Operating Unit specific Modules Like AP, wherein Legal Entity Context is passed to the OU through the link of the Primary Ledger.

2. However, access to these Balancing Segment Values could be controlled through Security Rules being assigned to the Value Set and the Respective Responsibility

3. The Key question is: If Legal Entity having the context to the Operating Unit that shares the common Ledger does not have assignment to it, what impact it has on the integrity of data when this access is otherwise allowed, except through Security Rules?
11) What are the interface tables in General Ledger ?
Ans)
GL_BUDGET_INTERFACE 
GL_DAILY_RATES_INTERFACE 
GL_IEA_INTERFACE 
GL_INTERFACE 
GL_INTERFACE_CONTROL 
GL_INTERFACE_HISTORY 
 12) What is DFF.
Question: What does DFF mean?
Answer: DFF is a mechanism that lets us create new fields in screens that are delivered by Oracle. 

Question: Oh good, but can these new fields be added without modifying/customization of the screen?.
Answer: Yes, certainly. Only some setup is needed, but no programmatic change is needed to setup DFF.

Question: Why the word Descriptive in Name DFF?
Answer: I think Oracle used this terminology because by means of setup…you are describing the structure of these new fields. Or may be Oracle simply used a silly word to distinguish DFF from KFF(discussed in latter training lesson).

Question: Are these DFF’s flexible?
Answer: A little flexible, for example, depending upon the value in a field, we can make  either Field1 or Field2  to appear in DFF. 

Question: So we create new fields in existing screen, but why the need of doing so?
Answer: Oracle delivers a standard set of fields for each screen, but different customers have different needs, hence Oracle lets us create new fields to the screen.

Question: Are these new fields that get created as a result of DFF free text?
I mean, can end user enter any junk into the new fields that are added via DFF?
Answer: If you attach a value set to the field(at time of setup of dff), then field will no longer be free text. The entered value in the field will be validated, also a list of valid values will be provided in LOV. 

Question : Will the values that get entered by the user in dff fields be updated to database?
Answer: Indeed, this happens because for each field that you create using DFF will be mapped to  a column in Oracle Applications.

Question: Can I create a DFF on any database column?
Answer: Not really. Oracle delivers a predefined list of columns for each table that are meant for DFF usage. Only those columns can be mapped to DFF segments. These columns are named similar to ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3 ETC. Usually Oracle provides upto 15 columns, but this number can vary.  

Question: Can I add hundreds of fields to a given screen?
Answer: This depends on the number of attribute columns in the table that screen uses. Also, those columns must be flagged as DFF enabled in DFF Registration screen. Don’t need to worry much about this because all the ATTRIBUTE columns are by default flagged for their DFF usage.

Question: Hmmm, I can see that DFFs are related to table and columns…
Answer: Yes correct. Each DFF is mapped to one table. And also each segment(or call it field) is mapped to one of the attribute columns in that table.

Question: I want these fields to appear in screen only when certain conditions are met. Is it possible?
Answer: Yes, we have something known as Context Sensitive Descriptive Flexfields.

In Order to do this, we will follow the below steps(screenshots will follow) :- 
1.    Navigate to the DFF Registration screen in Oracle Apps and query on Table AP_BANK_BRANCES. Now click on Reference Field
2.    Navigate to DFF Segments screen and query on the Title of the “Bank Branch” and Unfreeze the Flexfield and add segments as to Section “GLOBAL Data Elements” as shown in screenshots.
13)   What is Journal Import?
Ans) Journal import is an interface used to bring journal entries from legacy systems and other modules into the General Ledger.(Specifically Journal Import gets entries from legacy data into the GL base tables.

The tables populated during journal Import are
GL_JE_BATCHES,
GL_JE_HEADERS,
GL_JE_LINES,
GL_IMPORT_REFERENCES


14) What is the use of GL_Interface?
Ans) Gl_Interface is the primary interface table of General ledger. It acts as an interface between data originating from other modules such as AP,AR, Legacy data and the Gl Base tables.

15) What is Actual Flag?
Ans) Actual flag represents the Journal type.
A-Actual
B-Budget
E- Encumbrance.

16) What is Encumbrance?
Ans) It is a process of Reservation of funds for anticipated expenditure from a budget. Encumbrance integrates GL, Purchasing and Payables modules.

17) How many Key Flex Fields are there in General Ledger?
Ans)  One. Accounting Key Flex Field.

18) How many types of Budgets are there?
Ans) Two Types.
Expenditure Budgets
Revenue Budgets.

19)What are Spot Rate, Corporate Rate, Transaction Calendar and Accounting Calendar?
Ans) Spot Rate:
An exchange rate which you enter to perform conversion based on the rate on a specific date. It applies to the immediate delivery of currency.

 Corporate Rate:
An Exchange rate that we define to standardize rates for our company. This rate is the standard market rate determined by the senior financial management for use through out the organization.

 User Rate:
Conversion rate that is defined by the user.
EMU Fixed Rate: An exchange rate that is provided automatically by the General Ledger while entering journals. It uses a foreign currency that has a fixed relationship with the euro.
Transaction Calendar: Defines the business days and holidays for any calendar.
Accounting Calendar: Defines different types of calendars namely Fiscal, Federal Fiscal, Month etc.

20)What is Security Rule?
Ans) Security Rules are defined to control the access of a flexfield segment value (Financial information) at a responsibility level.

21) What are Cross Validation & ADI?
Ans) CVS – Cross validate segments – Allows only valid code combinations.
ADI – Allow dynamic inserts. – Allows any code combination irrespective of validity.
ADI would prevail if both of CVS and ADI are checked.

22)What is Translation?
Ans) Translation is a process used to convert functional currency to other reporting currencies at the account balances level.

23)What is Revaluation?
Ans) It is process used to revalue assets and liabilities denominated in foreign currency into functional currency based on period end exchange rate we specify. Unrealized gains/losses are resulted because of exchange rate fluctuations which are recorded in unrealized gain/loss account in GL.

24)What is FSG (Financial Statement Generator)?
Ans) Financial statement generator feature helps us to generate reports such as balance sheets and income statements with out programming. It also provides a high degree of control on the rows, columns, contents and calculations on the report. Different components such as row set, column set, content set, row order, display set have to be defined before a statement is generated, of which row set and column set are mandatory.

25) What is Consolidation?
Ans) Consolidation is a period-end process of combining the financial results of separate business subsidiaries with the parent company to form a single combined statement of financial results.

26) At what level General Ledger data is secured?
Ans) GL data is secured at Set of Book level. Subledger module data is secured at Responsibility level (i.e., at Operating Unit Level).

27) Difference between Primary Ledger and Secondary Ledger in R12 ?
Ans) Primary ledger:
The primary ledger acts as the primary accounting representation

Secondary Leger:
Secondary ledgers represent the primary ledger’s accounting data in another accounting representation that differs in one or more of the following ways: 
chart of accounts
accounting calendar/period type combination
currency
subledger accounting method
ledger processing options
Use secondary ledgers for supplementary purposes, such as consolidation, statutory reporting, or adjustments for one or more legal entities within the same accounting setup. For example, use a primary ledger for corporate accounting purposes that uses the corporate chart of accounts and subledger accounting method, and use a secondary ledger for statutory reporting purposes that uses the statutory chart of accounts and subledger accounting method. This allows you to maintain both a corporate and statutory representation of the same legal entity’s transactions in parallel. 
Assign one or more secondary ledgers to each primary ledger for an accounting setup. 
The secondary ledgers assigned can only perform the accounting for the legal entities within the same accounting setup. 

/by
, , , , , ,

FND Messages In Oracle HRMS

FND MESSAGES:
Here i am Explaining  how to create Fnd Messages  via  E-Business suite  and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message

To create a message in the E-Business suite message library you will need the “Application Developer” responsibility.
Navigate to Application Developer > Application > Messages. This will launch a form

Enter a unique name for your message
Eg: XX_CUSTOMER_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my first message
Click the save icon.

Retrieving a message using PL/SQL:

In order to retrieve the message from the database we need to use a standard API’s in the FND_MESSAGE package.
An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Optional)

Below is the PL/SQL Block to retrive the message

DECLARE
 my_message VARCHAR2(100);

 BEGIN

  –Initialize Apps Session
  fnd_global.apps_initialize( user_id      => 1234
                             ,resp_id      => 1235
                             ,resp_appl_id => 1236
                           );
                         
  /*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
    select fnd.user_id ,
         fresp.responsibility_id,
         fresp.application_id
  from   fnd_user fnd,
         fnd_responsibility_tl fresp
  where  fnd.user_name = ‘OEAG’
  and    fresp.responsibility_name = ‘Custom HRMS Responsibility‘;
  */
 
  –Clear the existing session
  FND_MESSAGE.CLEAR;

  –Tell e business suite which message you want (custom application short name/message name) 
  FND_MESSAGE.SET_NAME(‘XXERP’,’XX_CUSTOMER_MSG’);

  –Retrieve the message
  my_message := FND_MESSAGE.GET;  

  –Output the message
  DBMS_OUTPUT.PUT_LINE(my_message);
 END;

Output for the Above Block Is : This is my first message

Using Tokens in the message:

The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time.
Open the E-Business Suite message create a New Message
Navigate to Application Developer > Application > Messages. This will launch a form

Enter a unique name for your message
Eg: XX_UNAME_TOKEN_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my second message and the Token User name is &USERNAME
Click the save icon.
Note: In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. &USERNAME
Retrieving message With Token Substitution
Here USERNAME is called as TOKEN, we will Add the value dynamically

Example Block  is below:

DECLARE
 my_message VARCHAR2(100);
BEGIN
 –Initialize Apps Session
 fnd_global.apps_initialize( user_id      => 1234
                            ,resp_id      => 1235
                            ,resp_appl_id => 1236
                           );
                         
  /*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
    select fnd.user_id ,
         fresp.responsibility_id,
         fresp.application_id
  from   fnd_user fnd,
         fnd_responsibility_tl fresp
  where  fnd.user_name = ‘OEAG’
  and    fresp.responsibility_name = ‘Custom HRMS Responsibility’;
  */
 
 –Clear the existing session
 FND_MESSAGE.CLEAR;

 –Tell e business suite which message you want (Application short name/message name)
 FND_MESSAGE.SET_NAME(‘XXERP‘,’XX_UNAME_TOKEN_MSG’);

  –Set the username message token with the current applications user
 FND_MESSAGE.SET_TOKEN(‘USERNAME’,FND_GLOBAL.USER_NAME);

 –Retrieve the message
 my_message := FND_MESSAGE.GET;

 –Output the message
 DBMS_OUTPUT.PUT_LINE(my_message);
END;

Out put for above block is : 

This is my second message and the Token User name is IAMKRISHNA

Downloading and Uploading Messages using the Generic Loader

To download our example message we would use the following command at the Unix prompt on the mid-tier:
 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’PER’ MESSAGE_NAME=”XX_UNAME_TOKEN_MSG”

To Upload our example message we would use the following command at the Unix prompt on the mid-tier:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt

/by
, , , , , ,

How to find INV Onhand Quantity at given date?

–The query inputs the Item ID, organization ID and date.

  SELECT   SUM (target_qty), item_id
    FROM   (  SELECT   moqv.subinventory_code subinv,
                       moqv.inventory_item_id item_id,
                       SUM (transaction_quantity) target_qty
                FROM   mtl_onhand_qty_cost_v moqv
               WHERE   moqv.organization_id = :org_id
                       AND moqv.inventory_item_id = :item_id
            GROUP BY   moqv.subinventory_code,
                       moqv.inventory_item_id,
                       moqv.item_cost
            UNION
              SELECT   mmt.subinventory_code subinv,
                       mmt.inventory_item_id item_id,
                       -SUM (primary_quantity) target_qty
                FROM   mtl_material_transactions mmt, mtl_txn_source_types mtst
               WHERE   mmt.organization_id = :org_id
                       AND transaction_date >= TO_DATE (:hist_date) + 1
                       AND mmt.transaction_source_type_id =
                             mtst.transaction_source_type_id
                       AND mmt.inventory_item_id = :item_id
            GROUP BY   mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY   oq.item_id
/by
, , , , , , ,

Cursors In Oracle

CURSOR : A cursors is a pointer used to fetch rows from a result set 
Two types of classification s:


I.STATIC CURSOR S: 
Static : Normal cursor (implicit or explicit)

Cursor attributes  for implicit and explicit:

%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open

a. Implicit : 
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)

Example Implicit Cursor:

select * from emp

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;


b. Explicit : 2 network round trips. Store data first then retrieve data. 
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.

Syntax:
open c1; — cursor c1 is select <stmt>

fetch <>

exit when c1%NOTFOUND

Example Explicit cursor:

Without Using Loop s
Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

open cur1;

fetch cur1 into v_ename,v_empno,v_sal;

exit when cur1%notfound;

—<do processing>

close cur1;

end;

Using Loops:

Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

Using Loops with Cursor Parameters:

Declare

Cursor cur1( cp_deptNo Number) 
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo

l_deptNo Number :=50;
begin

For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

II. DYNAMIC CURSOR s : 

Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures. 

a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.

Example For the Ref Cursor :

–SPECK PACKAGE 
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
 AS
  TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

END REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:


–BODY PACKAGE 
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor: 
PROCEDURE 
WEAK_REF_CUR_PRC( p_deptno IN number,
                  p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
                  —- From 9i (p_cursor OUT SYS_REFCURSOR )—-
                 )
IS

BEGIN

  OPEN p_cursor FOR
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
end WEAK_REF_CUR_PRC;

— For Strong Ref Cursor: 
PROCEDURE 
STRONG_REF_CUR_PRC( p_deptno IN number,
                    p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
                  )
IS

BEGIN
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
  end STRONG_REF_CUR_PRC;
 END REFCURSOR_PKG;
/by