, ,

How to Select the Nth highest / lowest value from a table

I am posting this script as lot of my friends have asked about this. They are lot of ways to do this. I am showing you in two best ways.

Note:- In ‘N’ place, provide your Nth number you want to findout.

How to Select the Nth highest value from a table:-
————————————————-

select level, max(sal) from scott.emp
where level = ‘&n’
connect by prior (sal) > sal
group by level;

How to select the Nth lowest value from a table:-
——————————————————-
select level, min(sal) from scott.emp
where level = ‘&n’
connect by prior (sal) < style=”font-weight: bold;”>N th Top Salary
——————-

select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b where a.sal <= b.sal) N th Least Salary
———————
select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b
where a.sal >= b.sal)
/by
, , ,

To know the Application version before upgrade and current version

If there is some oracle Aplication Instance say (11.5.10) got upgrade to R12. After upgarding the Instance/Application, If I want to know the older version. Then use the following select statement.

SELECT SUBSTR(snapshot_name,12,8)
FROM ad_snapshots
WHERE comments = ‘Preseeded’
AND REPLACE(SUBSTR(snapshot_name,12,8),’.’) =
(
SELECT max(to_number(replace(SUBSTR(snapshot_name,12,8),’.’)))
FROM ad_snapshots
WHERE comments = ‘Preseeded’
);

Use the following to know the current Oracle Apps Version.
———————————————————————

SELECT release_name FROM fnd_product_groups;

/by
, ,

Integration with Oracle Subledger Accounting

Release 12 introduces a new module, Subledger Accounting (SLA), for managing
accounting across subledger transactions. With the introduction of SLA, Payables will
no longer create accounting entries, but will instead rely on the central SLA engine to
do so. During the upgrade, accounting options and their settings, and the existing
accounting entries in the Payables data model are moved to the new SLA accounting
data model. Also during the upgrade, Payables sets up SLA to replicate the accounting
created by Payables in Release 11i.

The new SLA architecture requires Payables to maintain specific data relating to
transactions. SLA uses this data to generate accounting entries. In order to achieve this
it was determined that both payment distributions and prepayment application
distributions would be introduced into the Payables data model. Unlike invoice
distributions that can be entered by the user, payment distributions will be generated
automatically and will be associated with each accounted payment.

During the upgrade, all accounting events, headers and lines from the 11i data model
are upgraded to the new Subledger Accounting events, headers and lines data model,
regardless of the number of periods you specify when submitting the upgrade. If the
Global Accounting Engine (AX) is enabled for the set of books associated with a given
Operating Unit, then the upgrade migrates the AX accounting events, headers and lines
to SLA instead of those in Payables. The payment distributions and prepayment
application distributions are upgraded based on time periods you specify during the
submission of the upgrade. During the upgrade, Payables creates payment distributions
and prepayment application distributions for existing transactions in the periods you
specify for upgrade and creates links between these new distributions and the original
invoice distributions.

If you have customizations based on the 11i AP accounting tables, you need to
transition them to use the SLA data model. Also note, if you use Oracle Projects,
Projects uses SLA in Release 12 and creates accounting entries for adjustments rather
than using Payables to create those entries as in prior releases. If you have any
customizations based on Project adjustments, you will need to transition them to the
SLA data model.

The Deferred Expenses feature, supported with Global Descriptive Flexfields at the
invoice distribution level in Release 11i, has been replaced by the Multi-Period
Accounting feature in SLA.

Upgrading Payables Accounting Entries to Subledger Accounting

The following table displays the mapping from 11i entities to new Release 12 entities.

Source 11i Entity
Release 12 Entity
AP/AX Accounting Events, Headers, Lines
SLA Accounting Events, Headers, Lines
AP Payment History, Invoice Payments and
Invoice Distributions
AP Payment History and AP Payment
Distributions
AP Prepayment History and Invoice
Distributions
AP Prepayment Application Distributions
AP Accounting Lines and Invoice
Distributions
AP Distribution Links

Upgrading Payables System Options to SLA

The following table displays the mapping from 11i system option settings to the new
accounting setup entities and settings.

Source 11i Window and Field
Release 12 Window and Field
Payables Options: Primary Accounting
Method
GL Accounting Setup: Sub-ledger Accounting
Method
Payables Options: Secondary Accounting
Method
GL Accounting Setup: Sub-ledger Accounting
Method
Payables Options: Primary Set of Books
GL Accounting Setup: Primary Ledger
Payables Options: Secondary Set of Books
GL Accounting Setup: Secondary Ledger
Payables Options: Prevent Prepayment
Application Across Balancing Segment
Obsolete. Supported by SLA inter-company
Balancing.
Payables Options: Relieve Future Dated
Payment Liability When:
• Payment is Issued
• Payment Matures
• Payment Clears
Obsolete. Supported by Payments bills
payable feature.

Creating Payment Distributions and Prepayment Application Distributions

During the upgrade, Payables creates payment distributions for existing payments,
links those distributions with the original invoice distributions and adds payment,
payment adjustment and payment cancellation information to the payment history
records. Since you control the periods that are upgraded (by setting them during the
SLA upgrade), Payables also adds an indicator to mark which historical data has been
upgraded.

Also during the upgrade, Payables creates prepayment application distributions for
existing prepayment invoices, links those distributions with the original prepayment
distributions and adds a prepayment history entity to track historical prepayment
application and non-application entries. Since you control the periods that are
upgraded (by setting them during the SLA upgrade), Payables also adds an indicator to
mark which historical data has been upgraded.

After the upgrade, if you find that you need to adjust a historical payment or need to
unapply a prepayment application that did not have its data upgraded, you can run the
SLA postupgrade process to upgrade the entries for that record.

Creating Distribution Links

During the upgrade, Payables migrates invoice distribution links, prepayment
application distribution links and payment distribution links into the SLA distribution
links entity for the data that has been populated in the payment distributions and
prepayment application distributions table for the periods you selected to upgrade.

Populating the Initial Balances for the Open Account Balances Listing Report

As part of the Subledger Accounting introduction, a new report, the Open Account
Balances Listing, replaces the 11i Payables Trial Balance. During the upgrade, Payables
and SLA populate the initial liability balances by ledger, formerly “set of books,” based
on Payables transactions as of the periods you selected to upgrade.

The following table displays the mapping from 11i standard reports to the new
SLA-based reports.

Obsolete 11i Standard Reports
Release 12 SLA Report
Accounts Payable Trial Balance
Accounts Payable Trial Balance
Payables Accounting Entries Report
Journal Entries Report (SLA)
Payables Account Analysis Report
Account Analysis Report (SLA)
/by
, ,

Integration with Oracle E-Business Tax

In Release 12, Oracle E-Business Tax, a new product, will manage transaction tax across
the E-Business Suite. In prior releases, the setup, defaulting and calculation of
transaction tax for Payables was managed within Payables using tax codes, their
associated rates and a hierarchy of defaulting options. This method of managing tax is
still available to you in Release 12. During the upgrade, E-Business Tax migrates the tax
codes and their rates to corresponding tax rules so that your tax processing can get the
same results after the upgrade as it did before. If you choose to use the features of
E-Business Tax, you can make the transition at your own pace, incrementally adding
E-Business Tax rules to meet your requirements.

In Release 12, there are new fields added to the supplier, invoice, and related entities
that track tax attributes used by E-Business Tax. Many of these attributes were
implemented with Global Descriptive Flexfields in prior releases and are upgraded to
regular fields on these entities.

Also during the upgrade, E-Business Tax takes information from the AP invoice lines
and creates summary and detail tax lines in the E-Business Tax repository. The tax lines
are upgraded based on the time period you specify during the submission of the
upgrade. During the upgrade, Payables creates payment distributions and prepayment
application distributions for existing transactions and creates links between these new
distributions and the original invoice distributions. After the upgrade, if you adjust a
historical transaction that was not upgraded, E-Business Tax automatically upgrades
the transaction to the Release 12 entities.

Tax Attributes Controlled by Global Descriptive Flexfields Migrated to Core Payables and E-Business Tax Entities

The following tax attributes were implemented using descriptive flexfields on the
invoice entities in Release 11i and are now implemented using named columns. The
Invoice Lines upgrade will upgrade the values from the descriptive flexfields segments
to the new columns.

The following are new fields on the invoice header and in the invoice interface:
• Business Category
• Fiscal Classification
• Invoice Sub-type
• Port of Entry
• Supplier Exchange Rate
• Supplier Tax Invoice Date
• Supplier Tax Invoice Number
• Tax Date
• Tax Reference Number

The following are new fields on the invoice line and in the invoice lines interface:

• Assessable Value
• Business Category
• Deferred Option, Distribution Account
• Fiscal Classification
• Intended Use
• Product Category
• Ship-To Location
• Supplier Exchange Rate
• User Defined Fiscal Classification

The following are new fields on the invoice distribution:

• Fiscal Classification
• Distribution Account
• Intended Use

/by
, , ,

Multiple Organizations Access Control

Multiple Organizations Access Control is an enhancement to the Multiple
Organizations feature of Oracle Applications. Multiple Organizations Access Control
allows a user to access data from one or many Operating Units while within a given
responsibility. Data security is maintained using the Multiple Organizations Security
Profile, defined in Oracle HRMS, which specifies a list of operating units and
determines the data access privileges for a user.

In Release 12, several controls are moved from the Payables Options or Financials Options forms to a new setup form that is common for Oracle Payables across all
operating units, the Payables System Setup form. If the upgrade finds conflicts in the
settings across multiple operating units, it will choose the most frequently occurring
setting.

Oracle Applications will not automatically create security profiles during the Release 12
upgrade. If you want to use Multiple Organizations Access Control, you will first need
to define security profiles, then link them to responsibilities or users.
/by