Thursday 5 September 2013

How to check Profile Option Values using SQL Queries ?

SQL Queries for checking Profile Option Values
The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level
1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro1.user_profile_option_name like ('%Ledger%')
and  pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%'
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;


Tuesday 6 August 2013

Drilldown from GL to Receiving Transactions

This query gives you information from GL, XLA and Receiving Transactions Table. I used this query when I was debugging reconciliation issues between GL and SLA entries and this query was really very very useful.


SELECT b.NAME                   je_batch_name,
b.description                   je_batch_description,
b.running_total_accounted_dr    je_batch_total_dr,
b.running_total_accounted_cr    je_batch_total_cr,
b.status                        je_batch_status,
b.default_effective_date        je_batch_effective_date,
b.default_period_name           je_batch_period_name,
b.creation_date                 je_batch_creation_date,
u.user_name                     je_batch_created_by,
h.je_category                   je_header_category,
h.je_source                     je_header_source,
h.period_name                   je_header_period_name,
h.NAME                          je_header_journal_name,
h.status                        je_header_journal_status,
h.creation_date                 je_header_created_date,
u1.user_name                    je_header_created_by,
h.description                   je_header_description,
h.running_total_accounted_dr    je_header_total_acctd_dr,
h.running_total_accounted_cr    je_header_total_acctd_cr,
l.je_line_num                   je_lines_line_number,
l.ledger_id                     je_lines_ledger_id,

glcc.concatenated_segments      je_lines_ACCOUNT,

l.entered_dr                    je_lines_entered_dr,

l.entered_cr                    je_lines_entered_cr,

l.accounted_dr                  je_lines_accounted_dr,

l.accounted_cr                  je_lines_accounted_cr,

l.description                   je_lines_description,

glcc1.concatenated_segments     xla_lines_account,

xlal.accounting_class_code      xla_lines_acct_class_code,

xlal.accounted_dr               xla_lines_accounted_dr,

xlal.accounted_cr               xla_lines_accounted_cr,

xlal.description                xla_lines_description,

xlal.accounting_date            xla_lines_accounting_date,

xlate.entity_code               xla_trx_entity_code,

xlate.source_id_int_1           xla_trx_source_id_int_1,

xlate.source_id_int_2           xla_trx_source_id_int_2,

xlate.source_id_int_3           xla_trx_source_id_int_3,

xlate.security_id_int_1         xla_trx_security_id_int_1,

xlate.security_id_int_2         xla_trx_security_id_int_2,

xlate.transaction_number        xla_trx_transaction_number,

rcvt.transaction_type           rcv_trx_transaction_type,

rcvt.transaction_date           rcv_trx_transaction_date,

rcvt.quantity                   rcv_trx_quantity,

rcvt.shipment_header_id         rcv_trx_shipment_header_id,

rcvt.shipment_line_id           rcv_trx_shipment_line_id,

rcvt.destination_type_code      rcv_trx_destination_type_code,

rcvt.po_header_id               rcv_trx_po_header_id,

rcvt.po_line_id                 rcv_trx_po_line_id,

rcvt.po_line_location_id        rcv_trx_po_line_location_id,

rcvt.po_distribution_id         rcv_trx_po_distribution_id,

rcvt.vendor_id                  rcv_trx_vendor_id,

rcvt.vendor_site_id             rcv_trx_vendor_site_id

FROM                           

gl_je_batches                   b,

gl_je_headers                   h,

gl_je_lines                     l,

fnd_user                        u,

fnd_user                        u1,

gl_code_combinations_kfv        glcc,

gl_code_combinations_kfv        glcc1,

gl_import_references            gir,

xla_ae_lines                    xlal,

xla_ae_headers                  xlah,

xla_events                      xlae,

xla.xla_transaction_entities    xlate,

rcv_transactions                rcvt

WHERE

b.created_by                    = u.user_id

AND h.created_by                = u1.user_id

AND b.je_batch_id               = h.je_batch_id

AND h.je_header_id              = l.je_header_id

AND l.code_combination_id       = glcc.code_combination_id

AND l.je_header_id              = gir.je_header_id

AND l.je_line_num               = gir.je_line_num

AND gir.gl_sl_link_table        = xlal.gl_sl_link_table

AND gir.gl_sl_link_id           = xlal.gl_sl_link_id

AND xlal.application_id         = xlah.application_id

AND xlal.ae_header_id           = xlah.ae_header_id

AND xlal.code_combination_id    = glcc1.code_combination_id

AND xlah.application_id         = xlae.application_id

AND xlah.event_id               = xlae.event_id

AND xlae.application_id         = xlate.application_id

AND xlae.entity_id              = xlate.entity_id

AND xlate.source_id_int_1       = rcvt.transaction_id

AND h.je_category               = 'Receiving'

AND b.default_period_name       = '01_APR-2009'


ORDER BY h.je_category;

[Keywords: gl_import_references, xla_ae_lines, xla_ae_headers, xla_events, xla_transaction_entities, SLA, rcv_transactions]

Related Posts

Tuesday 23 July 2013

How to join GL tables with XLA (SubLedger Accounting) tables

GL_JE_BATCHES (je_batch_id)
              => GL_JE_HEADERS (je_batch_id)

GL_JE_HEADERS (je_header_id)
              => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,je_line_num)
              => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) 
              => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)
              => XLA_AE_HEADERS (application_id, ae_header_id) 
XLA_AE_HEADERS (application_id, event_id)
              => XLA_EVENTS (application_id, event_id)   
XLA_EVENTS (application_id, entity_id)
       => XLA.XLA_TRANSACTION_ENTITIES(application_id,entity_id) 
_____________________________________________________________________________________________

The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.
The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

What is Revenue Recognition ?

Revenue Recognition:
Revenue Recognition principle is one of the important principles of Accrual Accounting. According to this principle, revenue must be recognized when
(1) They are realized or realizable and
(2) They are earned
Revenue is realized when products are exchanged for cash or claims to cash (Receivable).
Revenue is realizable when related assets received are readily convertible to cash or claims to cash.
Revenue is earned when the products are delivered or services are performed.
Recognizing the revenue means recording the amount as revenue in the financial statements.
Realization is the process of converting non-cash resources into cash.
In the Revenue Recognition principle, it does not matter when cash is received. (In Cash Basis Accounting, revenue is recognized when cash is received no matter when goods or services are sold).
For revenue to be recognized, both the above conditions must be met. In other words for revenue to be recognized, final delivery must be completed (of goods or services) and there has to be a payment assurance.
Let us have a look at the timing of Revenue Recognition
1) For sale of finished goods (Inventory Items), revenue is recognized at the date of sale (some interpret this as the date of shipping or the date of delivery)
2) For sale of services (e.g. support services), revenue is recognized when the services are performed (delivered)
3) For sale of Asset Items (other than inventory items like finished goods), revenue is recognized at the point of sale (i.e. when the customer is invoiced)
4) For revenue from other activities like rent for using company’s Fixed Assets, revenue is recognized as time passes or as assets are used.
Examples:
1) If a company invoices its customer for 100 units of item ‘A’, and ships (delivers) only 25 units, the company cannot recognize revenue for entire 100 items. It can only recognize revenue equivalent to the number of units delivered (Revenue is earned only when the products are delivered). Similarly, let’s say you pay $120 in advance to company ‘ABC’ for magazine subscription for one full year. The fact that company ‘ABC’ received money for one full year does not mean that they can record the entire amount as Revenue. In-fact the amount received in advance is a Liability to the company because they have to deliver magazines to their customer every month and if they fail to do so, they are liable to refund the amount received in advance. In this scenario, the company will recognize 1/12th of the entire amount every month as earned revenue after they deliver the magazine.
2) Company ‘ZXC’ signs a 3 year support contract with its client for a total amount of 3 million. This amount cannot be recorded as revenue unless the Company provides the support services to the client. Assuming the company is following a monthly calendar accounting period, the company will recognize 1/36th of the entire support contract deal amount every month. (Revenue is recognized when services are performed)
There are few exceptions to the timing of revenue recognition for sale of inventory items. Under normal scenario, revenue is recognizes at the point of sale, however if there are return policies, and if the company cannot reasonably estimate the amount of future returns, the revenue should be recognized only after the expiration of the return policy period.
Revenue Recognition Accounting:
If revenue is not recognized immediately, what is the accounting entry for the Sales Invoice? Let’s have a look
Let’s say, you invoice the Customer in Advance for the annual support contract of $12000. Since, you are invoicing the customer in Advance, you debit your Receivables. But then if you are not crediting the revenue right away, where do you account for the credit side of the accounting entry? You credit, what is called as Deferred Revenue (or Unearned Revenue). Deferred Revenue is actually a liability for the company. (The company is liable to provide the goods or services for which cash is received or will be received in advance). As and when the goods or services are delivered, the Deferred Revenue is reduced (debited) and revenue is recognized.
Accounting when the Invoice is created in Jan
Date
Accounting Class
Debit
Credit
Comments
1-Jan
Receivables
12000

The entire receivables is recognized in advance. How this receivable is collected will depend on the payment terms of the Invoice
1-Jan
Deferred Revenue

12000


End of Jan, Revenue is recognized for 1/12th of the entire amount, because the company has provided one month’s service to its client. To that effect, Deferred Revenue will be reduced and revenue will be recognized
Date
Accounting Class
Debit
Credit
Comments
31-Jan
Deferred Revenue
1000

Deferred Revenue reduced
31-Jan
Earned Revenue

1000
Earned Revenue amount for one month

End of Feb, another months revenue is recognized
Date
Accounting Class
Debit
Credit
Comments
28-Feb
Deferred Revenue
1000

Deferred Revenue reduced
28-Feb
Earned Revenue

1000
Earned Revenue amount for one month

The company will have similar accounting entry each month till Dec. At the end of Dec, the Deferred Revenue will be Zero and the entire amount will be reported as Revenue earned.


Thursday 13 June 2013

Oracle Apps R12 Purchasing New Features

Oracle Apps R12 Purchasing New Features

The Professional· Buyer’s Work Center speeds up daily purchasing tasks by providing buyers with a central launch pad from where they can efficiently perform their daily tasks, such as:
o Viewing and acting upon requisition demand
o Creating and managing orders and agreements
o Managing contract deliverables
o Running negotiation events, including auctions and RFxs
o Managing supplier information

 The Professional· Buyer’s Work Center tightly weaves Oracle Purchasing with other products in the procurement family, such as Oracle Sourcing, Oracle Procurement Contracts, and Oracle Services Procurement. Buyers can now easily navigate from purchasing to sourcing documents, and vice-versa, using a single, friendly user interface.

 From the· Demand Workbench, buyers can access the catalog and favorites to find negotiated alternatives to non-catalog requests. While authoring orders and agreements, buyers can use the catalog or favorites to quickly add items, thereby accelerating the document creation process. The enhanced catalog access provides easy access to pre-negotiated items. This reduces spending by leveraging negotiated pricing.

· Document Styles is a new feature in R12. It allows buying organizations to control the look and feel of the application to match the needs of different purchasing documents. Through reusable document styles, deploying organizations can enable or disable various Oracle Purchasing features, thereby simplifying the user interface. This helps people to use familiar name to Purchasing documents.
o When a purchasing document is created using a style, disabled features are hidden to simplify the user interface. For example, organizations can create a document style for a specific commodity, such as temporary labor. This document style optimizes field labels and display for that commodity, simplifying purchase order entry by hiding regions/attributes that are only relevant for goods purchases.

· Procurement of Complex Services is an integrated solution for Oracle Services Procurement used to model complex work contracts that involve advanced payment terms. These contracts tend to have high dollar values, often running into several millions of dollars. They also tend to be long lead time contracts, sometimes extending over multiple years. These contracts are characterized by progress payments that are governed by the complex payment terms, and which are released based on completion of work.
o The business flow can originate with a services request which the buying organization determines needs complex payment terms to manage and fulfill. The sourcing process allows negotiation of the complex payment terms and these are carried forward to the Contract after the award.
o The Contract, once approved and signed off by the involved parties is now in a state ready for execution. The Supplier can report progress on the work specified on the Contract, which the buying organization can certify as complete. Subsequently, payments can be processed for the progress made on the contract. The payment terms specified on the contract are used to compute the payment due to the supplier.

· Public APIs are modified to support Multi-Org Access Controls (MOAC). Public APIs take Operating Unit as a parameter. Operating Unit is an optional parameter. If it is not provided, the default operating unit from setup is used to set the desired context.
o PO Change API – POXCHN1B.pls
o Cancel PO API – POXPDCOB.pls
o Price Control API – POXPCPRB.pls

· Oracle E-Business Tax is a new infrastructure for tax knowledge management and delivery using a global system architecture that is configurable and scalable for adding country specific tax content.
o As the single point solution for managing transaction-based tax, Oracle E-Business Tax delivers tax services uniformly to Oracle E-Business Suite business flows through one application interface.
o Oracle E-Business Tax provides a comprehensive solution to configure, maintain, manage, and access transaction-based taxes. Organizations will have the ability to effectively address multiple tax needs, from global tax requirements to local compliance.
o Previously, only basic tax code and tax group based tax computation capabilities were supported. By integrating with Oracle E-Business Tax, the Procure to Pay business flow will be able to address complex tax requirements. Another benefit of the integration is the ability to utilize a single point tax solution across the E-Business Suite, which greatly simplifies maintenance and tax management.
o Tax is calculated for the following purchase documents:
 Purchase and Internal Requisitions, at the requisition line level§
 Standard and Planned Purchase Orders, at the PO shipment level§
 Blanket and Scheduled Releases, at the Release shipment level§
o Tax is calculated whenever a purchase transaction is created and tax is recalculated when any tax determination attributes on the transaction is updated. So any saved purchase document should have its applicable tax already calculated.
o In case of upgrading to R12 client may opt to keep using Pre-R12 tax setup or can do some additional setups for using new functionalities.

 In release 11i, Oracle Advanced Procurement Suite supported XSL-FO stylesheet based layout templates. In release 12, you can use layout templates created in Microsoft RTF and Adobe PDF formats. With Release 12, buyers can print purchasing documents on demand using any of the pre-defined layout templates. It also allows buyers to print a purchasing document in multiple formats·
o Attachment type supported
 Notes to Supplier (R12, 11i)§
 Long Short text (R12, 11i)§
 Files (R12)§
 URL (R12)§

· Maintain Sourcing Rules/ASLs for Agreement Items: Prior to this release, system-generated sourcing rules and ASLs were only created at the time of blanket agreement approval. These rules were enabled for all organizations, which might not have mirrored actual business processes.
In this release, you create sourcing rules and ASL entries for a specific inventory organization. You can create these rules for agreements that you manually create, or for agreements that you import through the open interface.
In addition, the dependency of rules creation with the approval process has been removed. As a result, you can create rules for existing agreements that have already been approved. This concurrent program allows you to automatically create rules for all of the operating units in which a blanket agreement is enabled.
· Support for Contract Purchasing Users: In this release, contingent workers can perform the same actions as requesters and buyers who are employees. These actions include creating requisitions, purchase orders, and receipts. In addition, contingent workers can approve both requisitions and purchase orders.
Now the question is what the benefit of this:
• Companies can outsource their procurement functions to third parties, allowing them to focus on their core competencies.
• For the many companies that utilize the services of contingent workers, these workers can raise requisitions on their own behalf.
• Companies can meet compliance requirements because they no longer have to define contingent workers as employees for contingent workers to perform their jobs.

· Auto-Approval Tolerances for Change Orders: (Purchasing : Tolerances and Routing > Change Order) Auto-Approval Tolerances are defined on Setup screen or in Workflow Attributes. Once a Purchasing Document is submitted for approval, in the Approval Workflow a check is done to ensure that all changes are within tolerance. If they are then main approval is bypassed otherwise the document is routed through main approval process in workflow.

· Model Complex Pricing for Blanket Line Items: R12, Oracle Purchasing provides an additional option to model complex pricing for Blanket line items. In this release Oracle Purchasing has built an integration with the Oracle Advanced Pricing product. Oracle Advanced Pricing provides a set of tools in the form of price lists, formulas and modifiers to model complex pricing scenarios. This integration may eliminate the need for existing Oracle Purchasing customers to incorporate custom code into the product in the form of the custom pricing hook.
The integration with advanced pricing allows dynamic calculation of prices providing significant additional flexibility to complement the existing blanket agreement functionality. Companies can now accurately price all requests and purchases, thus reducing their dependence on the supplier’s billing capabilities.
Pricing Hierarchy
• If the transaction references an item from the item master, the list price of the item in the item master is first defaulted into the transaction.
• If the transaction references an approved and active blanket agreement, the pricing engine then determines the price from the applicable agreement price breaks if there’s any.
• If there isn’t any price break defined, it picks up the price from the agreement line.
• If there’s pricing information available in Oracle Advanced Pricing in the form of price list lines and/or modifiers, these pricing rules are then applied to arrive at the net price.
• It is important to note that if you have defined custom pricing rules in the custom hookprovided, those rules override any other pricing that the system may have derived.

· Mass Re-pricing of Purchasing Documents was availabel from 11i9, in R12 it is improved a lot. You can now be able to re-price orders in batch using the existing “Retroactive Price Update” concurrent program even if the orders were not sourced from Blankets.
This Concurrent Program is now capable of re-pricing transactions based on pricing rules defined:
o On Oracle Advanced Pricing Price Lists/Modifiers
o And on Custom Pricing Hooks
· Advanced Approval Support for Requisitions:
o Parallel Approvals
o Support for Viewers

o Position Hierarchy Support