Retrieve Middle N records from a query

---

Question: How can I retrieve the Middle N records from a query?
For example, what if I wanted to retrieve records 3 to 5 from my query results. How can I do this?

Answer: To retrieve the Middle N records from a query, you can use the following syntax:

SELECT *
FROM	(select alias_name.*, rownum rnum from
		(-- your ordered query --) alias_name
	where rownum <= UPPER_BOUND )
WHERE rnum >= LOWER_BOUND;

For example, if you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_name) suppliers2
	where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_name DESC) suppliers2
	where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_id) suppliers2
	where rownum <= 4 )
WHERE rnum >= 2;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM	(select suppliers2.*, rownum rnum from
		(select * from suppliers ORDER BY supplier_id DESC) suppliers2
	where rownum <= 4 )
WHERE rnum >= 2;

Retrieve third lowest value from a table

---

Question: How can I retrieve the third lowest salary amount from a salary table?

Answer: To retrieve the third lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT salary_amount
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount) salary2
	where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT *
FROM	(select salary2.*, rownum rnum from
		(select * from salary ORDER BY salary_amount) salary2
	where rownum <= 3 )
WHERE rnum >= 3;

Insert multiple rows with a single INSERT statement

Oracle/PLSQL: Insert multiple rows with a single INSERT statement

---

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: You can insert multiple rows using the following syntax:

INSERT ALL
   INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
   INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
   INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

EXAMPLE #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

EXAMPLE #2

You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.

GL Tables

General Ledger tables can be grossly classified into following 5 categories. Here are few important tables in each category.

Ledgers Tables:

GL_LEDGERS: Stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information.

GL_CODE_COMBINATIONS: Stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application.

Period Tables:

GL_PERIODS: Stores information about the accounting periods you define using the Accounting Calendar form.

GL_PERIOD_SETS: Stores the calendars you define using the Accounting Calendar form.

GL_PERIOD_TYPES: Stores the period types you define using the Period Types form. Each row includes the period type name, the number of periods per fiscal year, and other information.

Journal Tables:

GL_JE_BATCHES: Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information.

GL_JE_HEADERS: Stores journal entries. There is a one-to-many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry.

GL_JE_LINES: Stores the journal entry lines that you enter in the Enter Journals form. There is a one-to-many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line.

GL_JE_SOURCES: Stores journal entry source names and descriptions. Each journal entry in your Oracle General Ledger application is assigned a source name to indicate how it was created. This table corresponds to the Journal Sources form.

GL_JE_CATEGORIES: Stores journal entry categories. Each row includes the category name and description.

Conversion and consolidation tables:

GL_CONSOLIDATION: Stores information about your consolidation mappings. Each row includes a mapping’s ID, name, description, and other information. This table corresponds to the first window of the Consolidation Mappings form. You need one row for each consolidation mapping you define.

GL_CONSOLIDATION_ACCOUNTS: Stores the account ranges that you enter when you consolidate balances using the Transfer Consolidation Data form. This table corresponds to the Account Ranges window of the Transfer Consolidation Data form.

GL_DAILY_RATES: Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type.

GL_DAILY_BALANCES: Stores daily aggregate balances for detail and summary balance sheet accounts in sets of books with average balances enabled.

Budgeting tables:

GL_BUDGET_TYPES: Stores information about budget types. Oracle General Ledger supports only one budget type, ‘STANDARD’. Therefore, this table always contains only one row.

GL_BUDGET_ASSIGNMENTS: Stores the accounts that are assigned to each budget organization. Each row includes the currency assigned to the account and the entry code for the account. The entry code is either ‘E’ for entered or ‘C’ for calculated. This table corresponds to the Account Assignments window of the Define Budget Organization form.

GL_BUDGET_INTERIM: It is used internally by Oracle General Ledger applications to post budget balances to the GL_BALANCES table. Rows are added to this table whenever you run the budget posting program. The budget posting program updates the appropriate budget balances in GL_BALANCES based on the rows in this table, and then deletes the rows in this table that it used.

Interface Tables:

GL_INTERFACE: It is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches.

GL_INTERFACE_CONTROL: It is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group id that you specified. When Journal Import completes, it deletes these rows from the table.

GL_BUDGET_INTERFACE: It is used to upload budget data into your Oracle General Ledger application from a spreadsheet program or other external source. Each row includes one fiscal year’s worth of budget amounts for an account.

HZ(TCA) tables in Oracle Receivables

This article describes few important HZ tables in AR and their relationships with each other.

HZ_PARTIES:

The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.

Few Important Columns are

• PARTY_ID: Party identifier
• PARTY_NUMBER: Unique identification number for this party
• PARTY_NAME: Name of the party
• PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:

The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.

Few Important Columns are

• PARTY_SITE_ID: Party site identifier.
• PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
• LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
• PARTY_SITE_NUMBER: Party site number.
• PARTY_SITE_NAME: User-defined name for the site.
• ADDRESSEE: Addressee information.

HZ_LOCATIONS:

The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.

Few Important Columns are

• LOCATION_ID: Unique identifier for this location
• COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
• ADDRESS1: First line for address
• ADDRESS2: Second line for address
• ADDRESS3: Third line for address
• ADDRESS4: Fourth line for address
• CITY: City
• POSTAL_CODE: Postal Code
• STATE: State
• ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:

The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.

Few Important Columns are

• CUST_ACCOUNT_ID: Customer account identifier
• PARTY_ID: A foreign key to the HZ_PARTY table.
• ACCOUNT_NUMBER: Account Number
• CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
• CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

Few Important Columns are

• CUST_ACCT_SITE_ID: Customer site identifier
• CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
• PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
• BILL_TO_FLAG: Indicates if this is a Bill-To site.
• SHIP_TO_FLAG: Indicates if this is a Ship-To site.
• MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:

The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.

Few Important Columns are

• SITE_USE_ID: Site use identifier
• CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
• SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
• PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:

The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

Few Important Columns are

• CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
• CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
• STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:

The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.

HZ_PARTY_RELATIONSHIPS:

The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.

PAYABLES TABLES

AP_SUPPLIERS:

• This table replaces the old PO_VENDORS table.
• It stores information about your supplier level attributes.
• Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
• Oracle Purchasing uses this information to determine active suppliers.
• The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

AP_SUPPLIER_SITES_ALL:

• This table replaces the old PO_VENDOR_SITES_ALL table.
• It stores information about your supplier site level attributes.
• There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
• The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
• Each row includes the supplier reference, purchasing, invoice, and general information.

AP_INVOICES_ALL:

• It 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 and can have one or more scheduled payments.  

AP_INVOICE_LINES_ALL:

• It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
• An invoice can have one or more invoice lines.
• An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
• An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.

AP_INVOICE_DISTRIBUTIONS_ALL:

• It holds the distribution information that is manually entered or system-generated.
• There is one row for each invoice distribution and a distribution must be associated with an invoice.
• An invoice can have multiple distributions.

AP_INVOICE_PAYMENTS_ALL:

• It contains records of invoice payments that you made to suppliers.
• There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
• Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
• When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.

AP_PAYMENT_SCHEDULES_ALL:

• This table stores information about scheduled payment information on invoices.

AP_PAYMENT_HISTORY_ALL:

• It stores the clearing/unclearing history for payments.
• It also stores the maturity history for future dated payments.
• The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
• Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.

AP_BATCHES_ALL:

• It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
• There is one row for each batch of invoices you enter.
•  If you enable Batch Control, each invoice must correspond to a record in this table.
•  Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.

AP_CHECKS_ALL:

• It stores information about payments issued to suppliers or refunds received from suppliers. 
• There is one row for each payment you issue to a supplier or refund received from a supplier.
• Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
• Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.

AP_HOLDS_ALL:

• It 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.

AP_BANK_ACCOUNTS_ALL:

• It contains information about your bank accounts.
• There is one row for each bank account you define and each bank account must be affiliated with one bank branch.

AP_BANK_ACCOUNT_USES_ALL:

• It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.

AP_CARDS_ALL:

• It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.

AP_TRIAL_BALANCE:

• It contains denormalized information about invoices and payments posted to the accrual set of books.

KEY FND TABLES IN ORACLE APPLICATION

KEY FND TABLES IN ORACLE APPLICATION

Here there are few key FND tables that we use in our AOL queries.

FND_APPLICATION:

 Stores applications registered with Oracle Application Object Library.

FND_APPLICATION_TL:

Stores translated information about all the applications registered with Oracle Application Object Library.

FND_APP_SERVERS:

This table will track the servers used by the E-Business Suite system.

FND_ATTACHED_DOCUMENTS:

Stores information relating a document to an application entity.

FND_CONCURRENT_PROCESSES:

Stores information about concurrent managers.

FND_CONCURRENT_PROCESSORS:

Stores information about immediate (subroutine) concurrent program libraries.

FND_CONCURRENT_PROGRAMS:

Stores information about concurrent programs. Each row includes a name and description of the concurrent program.

FND_CONCURRENT_PROGRAMS_TL:

Stores translated information about concurrent programs in each of the installed languages.

FND_CONCURRENT_QUEUES:

Stores information about concurrent managers.

FND_CONCURRENT_QUEUE_SIZE:

Stores information about the number of requests a concurrent manager can process at once, according to its work shift.

FND_CONCURRENT_REQUESTS:

Stores information about individual concurrent requests.

FND_CONCURRENT_REQUEST_CLASS:

Stores information about concurrent request types.

FND_CONC_REQ_OUTPUTS:

This table stores output files created by Concurrent Request.

FND_CURRENCIES:

Stores information about currencies.

FND_DATABASES:

It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.

FND_DATABASE_INSTANCES:

Stores instance specific information. Every database has one or more instance.

FND_DESCRIPTIVE_FLEXS:

Stores setup information about descriptive flexfields.

FND_DESCRIPTIVE_FLEXS_TL:

Stores translated setup information about descriptive flexfields.

FND_DOCUMENTS:

Stores language-independent information about a document.

FND_EXECUTABLES:

Stores information about concurrent program executables.

FND_FLEX_VALUES:

Stores valid values for key and descriptive flexfield segments.

FND_FLEX_VALUE_SETS:

Stores information about the value sets used by both key and descriptive flexfields.

FND_LANGUAGES:

Stores information regarding languages and dialects.

FND_MENUS:

It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.

FND_MENUS_TL:

Stores translated information about the menus in FND_MENUS.

FND_MENU_ENTRIES:

Stores information about individual entries in the menus in FND_MENUS.

FND_PROFILE_OPTIONS:

Stores information about user profile options.

FND_REQUEST_GROUPS:

Stores information about report security groups.

FND_REQUEST_SETS:

Stores information about report sets.

FND_RESPONSIBILITY:

Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.

FND_RESPONSIBILITY_TL:

Stores translated information about responsibilities.

FND_RESP_FUNCTIONS:

Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.

FND_SECURITY_GROUPS:

Stores information about security groups used to partition data in a Service Bureau architecture.

FND_SEQUENCES:

Stores information about the registered sequences in your applications.

FND_TABLES:

Stores information about the registered tables in your applications.

FND_TERRITORIES:

Stores information for countries, alternatively known as territories.

FND_USER:

Stores information about application users.

FND_VIEWS:

Stores information about the registered views in your applications.