Oracle Apps Purchasing (PO) Key Tables and their functionalities

Oracle Purchasing Key Tables and their functionalities

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description. REQUISITION_HEADER_ID is the unique system–generated requisition number. REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you use to identify the requisition in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate requisition numbers for you. PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition information. PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window.SEGMENT1 provides unique values for each row in the table in addition to REQUISITION_HEADER_ID.

PO_REQUISITION_LINES_ALL

PO_REQUISITION_LINES stores information about requisition lines. You need one row for each requisition line you create. Each row contains the line number, item number, item category, item description, need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line. LINE_LOCATION_ID identifies the purchase order shipment line on which you placed the requisition. LINE_LOCATION_ID is null if you have not placed the requisition line on a purchase order. BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for the requisition line. PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multisourced this requisition line.
PO_REQUISITION_LINES is one of three tables storing requisition information. This table corresponds to the Lines region of the Requisitions window.

PO_REQ_DISTRIBUTIONS_ALL

PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution. You need one row for each requisition distribution you create. Each row includes the Accounting Flexfield ID and requisition line quantity. PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information. This table corresponds to the requisition Distributions window, accessible through the Requisitions window.

PO_DISTRIBUTIONS_ALL

PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment. There are four types of documents using distributions in Oracle Purchasing:

• Standard Purchase Orders
• Planned Purchase Orders
• Planned Purchase Order Releases
• Blanket Purchase Order Releases

Each row includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases. PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order and release information. Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:

• If you autocreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution you copy on the purchase order.
• If you use a foreign currency on your purchase order,Oracle Purchasing stores currency conversion information in RATE and RATE_DATE.
• If you use encumbrance, GL_ENCUMBERED_DATE and GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create journal entries in Oracle General Ledger.
• If you do not autocreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table.
• If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release.
• If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release. If you cancel the distribution, Oracle Purchasing automatically updates QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also enters UNENCUMBERED_AMOUNT if you use encumbrance.

PO_HEADERS_ALL PO_HEADERS_ALL contains header information for your purchasing documents. You need one row for each document you create. There are six types of documents that use PO_HEADERS_ALL: • RFQs • Quotations • Standard purchase orders • Planned purchase orders • Blanket purchase orders • Contracts Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information, and the status of the document. Oracle Purchasing uses this information to record information that is related to a complete document. PO_HEADER_ID is the unique system–generated primary key and is invisible to the user. SEGMENT1 is the system–assigned number you use to identify the document in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate document numbers for you. SEGMENT1 is not unique for the entire table. Different document types can share the same numbers. You can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID. If APPROVED_FLAG is ’Y’, the purchase order is approved. If your document type is a blanket purchase order, contract purchase order,RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE to store the valid date range for the document. Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders. If you autocreate a quotation from an RFQ using the Copy Document window, Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID. Oracle Purchasing also uses FROM_TYPE_LOOKUP_CODE to indicate that you copied the quotation from an RFQ. PO_LINES_ALL PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. Thereare five document types that use lines: • RFQs • Quotations • Standard purchase orders • Blanket purchase orders • Planned purchase orders Each row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs. PO_LINE_ID is the unique system–generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order. Oracle Purchasing uses CONTRACT_NUM to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned purchase order lines. The QUANTITY field stores the total quantity of all purchase ordershipment lines (found in PO_LINE_LOCATIONS_ALL).

PO_VENDORS PO_VENDORS stores information about your suppliers. You need one row for each supplier you define. Each row includes the supplier name as well as purchasing, receiving, payment, accounting, tax, classification, and general information. Oracle Purchasing uses this information to determine active suppliers. VENDOR_ID is the unique system–generated receipt header number invisible to the user. SEGMENT1 is the system–generated or manually assigned number you use to identify the supplier in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate supplier numbers for you. This table is one of three tables that store supplier information. PO_VENDORS corresponds to the Suppliers window. PO_VENDOR_SITES_ALL PO_VENDOR_SITES_ALL stores information about your supplier sites. You need a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store supplier address information. This table is one of three tables that store supplier information. PO_VENDOR_SITES_ALL corresponds to the Sites region of the Suppliers window.

Source : http://www.club-oracle.com/forums/oracle-apps-purchasing-po-key-tables-and-their-functionalities-t286/

Read More

Oracle Apps General Ledger (GL) KEY Tables

Oracle Apps General Ledger (GL) KEY Tables

GL_SETS_OF_BOOKS

GL_SETS_OF_BOOKS stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES

GL_IMPORT_REFERENCES stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES

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. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.

In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table

GL_PERIODS

GL_PERIODS stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows in this table.

GL_JE_HEADERS

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. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_LINES

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. STATUS is ’U’ for unposted or ’P’ for posted.

GL_JE_BATCHES

GL_JE_BATCHES stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_BALANCES

GL_BALANCES stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.

GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance.Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is not used.

GL_CODE_COMBINATIONS

GL_CODE_COMBINATIONS stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting or detail budgeting is allowed, and others.
Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order.

The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.

Source:
http://www.club-oracle.com/forums/oracle-apps-general-ledger-gl-key-tables-t302/

Read More