Oracle APPS Important Tables

Oracle APPS Important Tables

ONT- Order Management

Table Name Description
OE_ORDER_HEADERS_ALL OE_ORDER_HEADERS_ALL stores header information for orders in Order Management.
OE_ORDER_LINES_ALL OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management.
OE_ORDER_SOURCES Feeder System Names that create orders in Order Management tables.
OE_ORDER_HOLDS_ALL This table stores information of all the orders and lines that are on hold and the link to hold sources and hold releases.
OE_SALES_CREDITS This table stores information about sales credits.
OE_TRANSACTION_TYPES_ALL This table stores information about the order and line transaction types
WSH_DELIVERY_ASSIGNMENTS Delivery Assignments
WSH_DELIVERY_DETAILS Delivery Details
WSH_NEW_DELIVERIES Deliveries
WSH_TRIPS Trips
WSH_TRIP_STOPS Trip Stops


PO - Purchasing

Table Name Description
PO_ACTION_HISTORY Document approval and control action history table
PO_AGENTS Buyers table
PO_DISTRIBUTIONS_ALL Purchase order distributions
PO_HEADERS_ALL Document headers (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINES_ALL Purchase document lines (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINE_LOCATIONS_ALL Document shipment schedules (for purchase orders, purchase agreements, quotations, RFQs)
PO_RELEASES_ALL Purchase order releases
PO_LINES_ARCHIVE_ALL Archived purchase order lines
PO_LINE_LOCATIONS_ARCHIVE_ALL Archived purchase order shipments
PO_HEADERS_ARCHIVE_ALL Archived purchase orders
PO_LINE_TYPES_B Line types
PO_RELEASES_ARCHIVE_ALL Archived releases
PO_REQUISITION_HEADERS_ALL Requisition headers
PO_REQUISITION_LINES_ALL Requisition lines
PO_REQ_DISTRIBUTIONS_ALL Requisition distributions
RCV_TRANSACTIONS Receiving transactions
RCV_SHIPMENT_HEADERS Shipment and receipt header information
RCV_SHIPMENT_LINES Receiving shipment line information

Oracle APPS Important Tables

ONT- Order Management

Table Name Description
OE_ORDER_HEADERS_ALL OE_ORDER_HEADERS_ALL stores header information for orders in Order Management.
OE_ORDER_LINES_ALL OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management.
OE_ORDER_SOURCES Feeder System Names that create orders in Order Management tables.
OE_ORDER_HOLDS_ALL This table stores information of all the orders and lines that are on hold and the link to hold sources and hold releases.
OE_SALES_CREDITS This table stores information about sales credits.
OE_TRANSACTION_TYPES_ALL This table stores information about the order and line transaction types
WSH_DELIVERY_ASSIGNMENTS Delivery Assignments
WSH_DELIVERY_DETAILS Delivery Details
WSH_NEW_DELIVERIES Deliveries
WSH_TRIPS Trips
WSH_TRIP_STOPS Trip Stops


PO - Purchasing

Table Name Description
PO_ACTION_HISTORY Document approval and control action history table
PO_AGENTS Buyers table
PO_DISTRIBUTIONS_ALL Purchase order distributions
PO_HEADERS_ALL Document headers (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINES_ALL Purchase document lines (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINE_LOCATIONS_ALL Document shipment schedules (for purchase orders, purchase agreements, quotations, RFQs)
PO_RELEASES_ALL Purchase order releases
PO_LINES_ARCHIVE_ALL Archived purchase order lines
PO_LINE_LOCATIONS_ARCHIVE_ALL Archived purchase order shipments
PO_HEADERS_ARCHIVE_ALL Archived purchase orders
PO_LINE_TYPES_B Line types
PO_RELEASES_ARCHIVE_ALL Archived releases
PO_REQUISITION_HEADERS_ALL Requisition headers
PO_REQUISITION_LINES_ALL Requisition lines
PO_REQ_DISTRIBUTIONS_ALL Requisition distributions
RCV_TRANSACTIONS Receiving transactions
RCV_SHIPMENT_HEADERS Shipment and receipt header information
RCV_SHIPMENT_LINES Receiving shipment line information

INV – Inventory

Table Name Description
MTL_CATEGORIES_B Code combinations table for Item Category
MTL_CATEGORY_SETS_B Category Sets
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_REVISIONS_B Item revisions
MTL_ITEM_TEMPLATES_B Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_LOT_NUMBERS Lot number definitions
MTL_MATERIAL_TRANSACTIONS Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP Temporary table for processing material transactions
MTL_ONHAND_QUANTITIES_DETAIL FIFO quantities by control level and receipt
MTL_PARAMETERS Inventory control options and defaults
MTL_RESERVATIONS Reservations
MTL_SECONDARY_INVENTORIES Subinventory definitions
MTL_SECONDARY_LOCATORS Item-subinventory-locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_SYSTEM_ITEMS_B Inventory item definitions
MTL_TRANSACTION_ACCOUNTS Material transaction distributions
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_UNIT_TRANSACTIONS Serial number transactions


GL- General Ledger

Table Name Description
GL_CODE_COMBINATIONS Stores valid account combinations
GL_SETS_OF_BOOKS Stores information about the sets of books
GL_IMPORT_REFERENCES Stores individual transactions from subledgers
GL_DAILY_RATES Stores the daily conversion rates for foreign currency 

Transactions
GL_PERIODS Stores information about the accounting periods
GL_JE_HEADERS Stores journal entries
GL_JE_LINES Stores the journal entry lines that you enter in the Enter Journals form
GL_JE_BATCHES Stores journal entry batches
GL_BALANCES Stores actual, budget, and encumbrance balances for detail and summary accounts
GL_BUDGETS Stores Budget definitions
GL_INTERFACE Import journal entry batches
GL_BUDGET_INTERFACE Upload budget data from external sources
GL_DAILY_RATES_INTERFACE Import daily conversion rates

AR- Accounts Receivables

Table Name Description
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments
RA_BATCHES_ALL
AR_CASH_RECEIPTS_ALL Detailed receipt information
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction
HZ_CUST_ACCOUNTS Stores information about customer accounts.
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts
HZ_CUST_CONTACT_POINTS This table is no longer used
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites.
HZ_LOCATIONS Physical addresses
HZ_ORG_CONTACTS People as contacts for parties
HZ_ORG_CONTACT_ROLES Roles played by organization contacts
HZ_PARTIES Information about parties such as organizations, people, and groups
HZ_PARTY_SITES Links party to physical locations
HZ_PARTY_SITE_USES The way that a party uses a particular site or address
HZ_RELATIONSHIPS Relationships between entities
HZ_RELATIONSHIP_TYPES Relationship types

CE- Cash Management

Table Name Description
CE_BANK_ACCOUNTS This table contains bank account information. Each bank account must be affiliated with one bank branch.
CE_BANK_ACCT_BALANCES This table stores the internal bank account balances
CE_BANK_ACCT_USES_ALL This table stores information about your bank account uses.
CE_STATEMENT_HEADERS Bank statements
CE_STATEMENT_LINES Bank statement lines
CE_STATEMENT_HEADERS_INT Open interface for bank statements
CE_STATEMENT_LINES_INTERFACE Open interface for bank statement lines
CE_TRANSACTION_CODES Bank transaction codes

AP- Accounts Payables

Table Name Description
AP_ACCOUNTING_EVENTS_ALL Accounting events table
AP_AE_HEADERS_ALL Accounting entry headers table
AP_AE_LINES_ALL Accounting entry lines table
AP_BANK_ACCOUNTS_ALL Bank Account Details
AP_BANK_ACCOUNT_USES_ALL Bank Account Uses Information
AP_BANK_BRANCHES Bank Branches
AP_BATCHES_ALL Summary invoice batch information
AP_CHECKS_ALL Supplier payment data
AP_HOLDS_ALL Invoice hold information
AP_INVOICES_ALL Detailed invoice records
AP_INVOICE_LINES_ALL AP_INVOICE_LINES_ALL contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice distribution line information
AP.AP_INVOICE_PAYMENTS_ALL Invoice payment records
AP_PAYMENT_DISTRIBUTIONS_ALL Payment distribution information
AP_PAYMENT_HISTORY_ALL Maturity and reconciliation history for payments
AP_PAYMENT_SCHEDULES_ALL Scheduled payment information on invoices
AP_INTERFACE_REJECTIONS Information about data that could not be loaded by Payables Open Interface Import
AP_INVOICES_INTERFACE Information used to create an invoice using Payables Open Interface Import
AP_INVOICE_LINES_INTERFACE Information used to create one or more invoice distributions
AP_SUPPLIERS AP_SUPPLIERS stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL AP_SUPPLIER_SITES_ALL stores information about your supplier site level attributes.
AP_SUPPLIER_CONTACTS Stores Supplier Contacts


FA – Fixed Assets

Table Name Description
FA_ADDITIONS_B Descriptive information about assets
FA_ADJUSTMENTS Information used by the posting program to generate journal entry lines in the general ledger
FA_ASSET_HISTORY Historical information about asset reclassifications and unit adjustments
FA_ASSET_INVOICES Accounts payable and purchasing information for each asset
FA_BOOKS Financial information of each asset
FA_BOOK_CONTROLS Control information that affects all assets in a depreciation book
FA_CALENDAR_PERIODS Detailed calendar information
FA_CALENDAR_TYPES General calendar information
FA_CATEGORIES_B Default financial information for asset categories
FA_CATEGORY_BOOKS Default financial information for an asset category and depreciation book combination
FA_DEPRN_DETAIL Depreciation amounts charged to the depreciation expense account in each distribution line
FA_DEPRN_PERIODS Information about each depreciation period
FA_DEPRN_EVENTS Information about depreciation accounting events.
FA_DEPRN_SUMMARY Depreciation information at the asset level
FA_DISTRIBUTION_ACCOUNTS Table to store account ccids for all distributions for a book
FA_DISTRIBUTION_DEFAULTS Distribution set information
FA_DISTRIBUTION_HISTORY Employee, location, and Accounting Flexfield values assigned to each asset
FA_DISTRIBUTION_SETS Header information for distribution sets
FA_FORMULAS Depreciation rates for formula-based methods
FA_LOCATIONS Location flexfield segment value combinations
FA_MASS_ADDITIONS Information about assets that you want to automatically add to Oracle Assets from another system
FA_METHODS Depreciation method information
FA_RETIREMENTS Information about asset retirements and reinstatements


HRMS- Human Resource Management System

Table Name Description
HR_ALL_ORGANIZATION_UNITS Organization unit definitions.
HR_ALL_POSITIONS_F Position definition information.
HR_LOCATIONS_ALL Work location definitions.
PER_ADDRESSES Address information for people
PER_ALL_PEOPLE_F DateTracked table holding personal information for employees, applicants and other people.
PER_ALL_ASSIGNMENTS_F Allocated Tasks
PER_ANALYSIS_CRITERIA Flexfield combination table for the personal analysis key flexfield.
PER_ASSIGNMENT_EXTRA_INFO Extra information for an assignment.
PER_ASSIGNMENT_STATUS_TYPES Predefined and user defined assignment status types.
PER_CONTRACTS_F The details of a persons contract of employment
PER_CONTACT_RELATIONSHIPS Contacts and relationship details for dependents, beneficiaries, emergency contacts, parents etc.
PER_GRADES Grade definitions for a business group.
PER_JOBS Jobs defined for a Business Group
PER_PAY_BASES Definitions of specific salary bases
PER_PAY_PROPOSALS Salary proposals and performance review information for employee assignments
PER_PEOPLE_EXTRA_INFO Extra information for a person
PER_PERIODS_OF_PLACEMENT Periods of placement details for a non-payrolled worker
PER_PERIODS_OF_SERVICE Period of service details for an employee.
PER_PERSON_ANALYSES Special information types for a person
PER_PERSON_TYPES Person types visible to specific Business Groups.
PER_PERSON_TYPE_USAGES_F Identifies the types a person may be.
PER_PHONES PER_PHONES holds phone numbers for current and ex-employees, current and ex-applicants and employee contacts.
PER_SECURITY_PROFILES Security profile definitions to restrict user access to specific HRMS records


PAY- Payroll

Table Name Description
PAY_ACTION_INFORMATION Archived data stored by legislation
PAY_ALL_PAYROLLS_F Payroll group definitions.
PAY_ASSIGNMENT_ACTIONS Action or process results, showing which assignments have been processed by a specific payroll action, or process.
PAY_ELEMENT_CLASSIFICATIONS Element classifications for legislation and information needs.
PAY_ELEMENT_ENTRIES_F Element entry list for each assignment.
PAY_ELEMENT_ENTRY_VALUES_F Actual input values for specific element entries.
PAY_ELEMENT_LINKS_F Eligibility rules for an element type.
PAY_ELEMENT_TYPES_F Element definitions.
PAY_ELEMENT_TYPE_USAGES_F Used to store elements included or excluded from a defined run type.
PAY_ORG_PAYMENT_METHODS_F Payment methods used by a Business Group.
PAY_PAYMENT_TYPES Types of payment that can be processed by the system.
PAY_PAYROLL_ACTIONS Holds information about a payroll process.
PAY_PEOPLE_GROUPS People group flexfield information.
PAY_PERSONAL_PAYMENT_METHODS_F Personal payment method details for an employee.
PAY_RUN_RESULTS Result of processing a single element entry.
PAY_RUN_RESULT_VALUES Result values from processing a single element entry.
PAY_SECURITY_PAYROLLS List of payrolls and security profile access rules.
PAY_INPUT_VALUES_F Input value definitions for specific elements.


BOM – Bills Of Material

Table Name Description
BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table




WIP – Work in Process

Table Name Description
WIP_DISCRETE_JOBS Discrete jobs
WIP_ENTITIES Information common to jobs and schedules
WIP_LINES Production lines
WIP_MOVE_TRANSACTIONS Shop floor move transactions
WIP_MOVE_TXN_ALLOCATIONS Move transaction allocations for repetitive schedules
WIP_OPERATIONS Operations necessary for jobs and schedules
WIP_OPERATION_NETWORKS Operation dependency
WIP_OPERATION_OVERHEADS Overheads for operations in an average costing organization
WIP_OPERATION_RESOURCES Resources necessary for operations
WIP_OPERATION_YIELDS This table keeps all costing information for operation yield costing.
WIP_TRANSACTIONS WIP resource transactions
WIP_TRANSACTION_ACCOUNTS Debits and credits due to resource transactions


FND – Appication Object Library

Table Name Description
FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS New menu tabl for Release 10SC
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_SETS Reports sets
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY Responsibilities
FND_RESP_FUNCTIONS Function Security
FND_USER Application users


JA - Asia/Pacific Localizations

Table Name Description
JAI_CMN_BOE_HDRS Stores BOE header info when a BOE Invoice is created through IL
JAI_CMN_BOE_DTLS Detail table for BOE Invoices
JAI_CMN_TAXES_ALL Master table for Localization Taxes
JAI_CMN_TAX_CTGS_ALL Stores tax categories and their link to excise ITEM classes.
JAI_CMN_TAX_CTG_LINES Stores the tax lines for defined tax categories
JAI_CMN_VENDOR_SITES Stores excise account related information about vendors.
JAI_RGM_DEFINITIONS Stores regime information.
JAI_RGM_TAXES This table stores tax details for transactions having TCS tax type.
JAI_CMN_RG_23AC_I_TRXS Stores Information of RG23A/C records and known as Quantity Register.
JAI_CMN_RG_23AC_II_TRXS Stores Information of RG23A/C Part II Details. Also known as Amount Register
JAI_CMN_RG_23D_TRXS Quantity register for Trading Organizations
JAI_CMN_RG_BALANCES Store the current balances of RG23A, RG23C and PLA Registers
JAI_CMN_RG_PLA_TRXS Stores the Transaction Information of PLA Register.
JAI_CMN_RG_PLA_HDRS Stores PLA header Infomation when a PLA invoice is created in AP module
JAI_CMN_RG_PLA_DTLS Stores PLA Detail Information when a PLA Invoice is created in AP Module


QP – Advanced Pricing

Table Name Description
QP_LIST_HEADERS_B QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.
QP_LIST_LINES QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B.
QP_PRICE_FORMULAS_B QP_PRICE_FORMULAS_B stores the pricing formula header information.
QP_PRICE_FORMULA_LINES QP_PRICE_FORMULA_LINES stores each component that makes up the formula.
QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES stores product information and pricing attributes.
QP_QUALIFIERS QP_QUALIFIERS stores qualifier attribute information.






XLA - Subledger Accounting

Table Name Description
XLA_EVENTS The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE.
XLA_TRANSACTION_ENTITIES The table XLA_ENTITIES contains information about sub-ledger document or transactions.
XLA_AE_HEADERS The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers.
XLA_AE_LINES The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines.
XLA_DISTRIBUTION_LINKS The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines.
XLA_ACCOUNTING_ERRORS The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program.
XLA_ACCTG_METHODS_B The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers.
XLA_EVENT_TYPES_B The XLA_EVENT_TYPES_B table stores all event types that belong to an event class.
XLA_GL_LEDGERS This table contains ledger information used by subledger accounting.

Table Name Description
MTL_CATEGORIES_B Code combinations table for Item Category
MTL_CATEGORY_SETS_B Category Sets
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_REVISIONS_B Item revisions
MTL_ITEM_TEMPLATES_B Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_LOT_NUMBERS Lot number definitions
MTL_MATERIAL_TRANSACTIONS Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP Temporary table for processing material transactions
MTL_ONHAND_QUANTITIES_DETAIL FIFO quantities by control level and receipt
MTL_PARAMETERS Inventory control options and defaults
MTL_RESERVATIONS Reservations
MTL_SECONDARY_INVENTORIES Subinventory definitions
MTL_SECONDARY_LOCATORS Item-subinventory-locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_SYSTEM_ITEMS_B Inventory item definitions
MTL_TRANSACTION_ACCOUNTS Material transaction distributions
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_UNIT_TRANSACTIONS Serial number transactions


GL- General Ledger

Table Name Description
GL_CODE_COMBINATIONS Stores valid account combinations
GL_SETS_OF_BOOKS Stores information about the sets of books
GL_IMPORT_REFERENCES Stores individual transactions from subledgers
GL_DAILY_RATES Stores the daily conversion rates for foreign currency 

Transactions
GL_PERIODS Stores information about the accounting periods
GL_JE_HEADERS Stores journal entries
GL_JE_LINES Stores the journal entry lines that you enter in the Enter Journals form
GL_JE_BATCHES Stores journal entry batches
GL_BALANCES Stores actual, budget, and encumbrance balances for detail and summary accounts
GL_BUDGETS Stores Budget definitions
GL_INTERFACE Import journal entry batches
GL_BUDGET_INTERFACE Upload budget data from external sources
GL_DAILY_RATES_INTERFACE Import daily conversion rates


AR- Accounts Receivables

Table Name Description
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments
RA_BATCHES_ALL
AR_CASH_RECEIPTS_ALL Detailed receipt information
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction
HZ_CUST_ACCOUNTS Stores information about customer accounts.
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts
HZ_CUST_CONTACT_POINTS This table is no longer used
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites.
HZ_LOCATIONS Physical addresses
HZ_ORG_CONTACTS People as contacts for parties
HZ_ORG_CONTACT_ROLES Roles played by organization contacts
HZ_PARTIES Information about parties such as organizations, people, and groups
HZ_PARTY_SITES Links party to physical locations
HZ_PARTY_SITE_USES The way that a party uses a particular site or address
HZ_RELATIONSHIPS Relationships between entities
HZ_RELATIONSHIP_TYPES Relationship types

CE- Cash Management

Table Name Description
CE_BANK_ACCOUNTS This table contains bank account information. Each bank account must be affiliated with one bank branch.
CE_BANK_ACCT_BALANCES This table stores the internal bank account balances
CE_BANK_ACCT_USES_ALL This table stores information about your bank account uses.
CE_STATEMENT_HEADERS Bank statements
CE_STATEMENT_LINES Bank statement lines
CE_STATEMENT_HEADERS_INT Open interface for bank statements
CE_STATEMENT_LINES_INTERFACE Open interface for bank statement lines
CE_TRANSACTION_CODES Bank transaction codes

AP- Accounts Payables

Table Name Description
AP_ACCOUNTING_EVENTS_ALL Accounting events table
AP_AE_HEADERS_ALL Accounting entry headers table
AP_AE_LINES_ALL Accounting entry lines table
AP_BANK_ACCOUNTS_ALL Bank Account Details
AP_BANK_ACCOUNT_USES_ALL Bank Account Uses Information
AP_BANK_BRANCHES Bank Branches
AP_BATCHES_ALL Summary invoice batch information
AP_CHECKS_ALL Supplier payment data
AP_HOLDS_ALL Invoice hold information
AP_INVOICES_ALL Detailed invoice records
AP_INVOICE_LINES_ALL AP_INVOICE_LINES_ALL contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice distribution line information
AP.AP_INVOICE_PAYMENTS_ALL Invoice payment records
AP_PAYMENT_DISTRIBUTIONS_ALL Payment distribution information
AP_PAYMENT_HISTORY_ALL Maturity and reconciliation history for payments
AP_PAYMENT_SCHEDULES_ALL Scheduled payment information on invoices
AP_INTERFACE_REJECTIONS Information about data that could not be loaded by Payables Open Interface Import
AP_INVOICES_INTERFACE Information used to create an invoice using Payables Open Interface Import
AP_INVOICE_LINES_INTERFACE Information used to create one or more invoice distributions
AP_SUPPLIERS AP_SUPPLIERS stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL AP_SUPPLIER_SITES_ALL stores information about your supplier site level attributes.
AP_SUPPLIER_CONTACTS Stores Supplier Contacts


FA – Fixed Assets

Table Name Description
FA_ADDITIONS_B Descriptive information about assets
FA_ADJUSTMENTS Information used by the posting program to generate journal entry lines in the general ledger
FA_ASSET_HISTORY Historical information about asset reclassifications and unit adjustments
FA_ASSET_INVOICES Accounts payable and purchasing information for each asset
FA_BOOKS Financial information of each asset
FA_BOOK_CONTROLS Control information that affects all assets in a depreciation book
FA_CALENDAR_PERIODS Detailed calendar information
FA_CALENDAR_TYPES General calendar information
FA_CATEGORIES_B Default financial information for asset categories
FA_CATEGORY_BOOKS Default financial information for an asset category and depreciation book combination
FA_DEPRN_DETAIL Depreciation amounts charged to the depreciation expense account in each distribution line
FA_DEPRN_PERIODS Information about each depreciation period
FA_DEPRN_EVENTS Information about depreciation accounting events.
FA_DEPRN_SUMMARY Depreciation information at the asset level
FA_DISTRIBUTION_ACCOUNTS Table to store account ccids for all distributions for a book
FA_DISTRIBUTION_DEFAULTS Distribution set information
FA_DISTRIBUTION_HISTORY Employee, location, and Accounting Flexfield values assigned to each asset
FA_DISTRIBUTION_SETS Header information for distribution sets
FA_FORMULAS Depreciation rates for formula-based methods
FA_LOCATIONS Location flexfield segment value combinations
FA_MASS_ADDITIONS Information about assets that you want to automatically add to Oracle Assets from another system
FA_METHODS Depreciation method information
FA_RETIREMENTS Information about asset retirements and reinstatements



HRMS- Human Resource Management System

Table Name Description
HR_ALL_ORGANIZATION_UNITS Organization unit definitions.
HR_ALL_POSITIONS_F Position definition information.
HR_LOCATIONS_ALL Work location definitions.
PER_ADDRESSES Address information for people
PER_ALL_PEOPLE_F DateTracked table holding personal information for employees, applicants and other people.
PER_ALL_ASSIGNMENTS_F Allocated Tasks
PER_ANALYSIS_CRITERIA Flexfield combination table for the personal analysis key flexfield.
PER_ASSIGNMENT_EXTRA_INFO Extra information for an assignment.
PER_ASSIGNMENT_STATUS_TYPES Predefined and user defined assignment status types.
PER_CONTRACTS_F The details of a persons contract of employment
PER_CONTACT_RELATIONSHIPS Contacts and relationship details for dependents, beneficiaries, emergency contacts, parents etc.
PER_GRADES Grade definitions for a business group.
PER_JOBS Jobs defined for a Business Group
PER_PAY_BASES Definitions of specific salary bases
PER_PAY_PROPOSALS Salary proposals and performance review information for employee assignments
PER_PEOPLE_EXTRA_INFO Extra information for a person
PER_PERIODS_OF_PLACEMENT Periods of placement details for a non-payrolled worker
PER_PERIODS_OF_SERVICE Period of service details for an employee.
PER_PERSON_ANALYSES Special information types for a person
PER_PERSON_TYPES Person types visible to specific Business Groups.
PER_PERSON_TYPE_USAGES_F Identifies the types a person may be.
PER_PHONES PER_PHONES holds phone numbers for current and ex-employees, current and ex-applicants and employee contacts.
PER_SECURITY_PROFILES Security profile definitions to restrict user access to specific HRMS records


PAY- Payroll

Table Name Description
PAY_ACTION_INFORMATION Archived data stored by legislation
PAY_ALL_PAYROLLS_F Payroll group definitions.
PAY_ASSIGNMENT_ACTIONS Action or process results, showing which assignments have been processed by a specific payroll action, or process.
PAY_ELEMENT_CLASSIFICATIONS Element classifications for legislation and information needs.
PAY_ELEMENT_ENTRIES_F Element entry list for each assignment.
PAY_ELEMENT_ENTRY_VALUES_F Actual input values for specific element entries.
PAY_ELEMENT_LINKS_F Eligibility rules for an element type.
PAY_ELEMENT_TYPES_F Element definitions.
PAY_ELEMENT_TYPE_USAGES_F Used to store elements included or excluded from a defined run type.
PAY_ORG_PAYMENT_METHODS_F Payment methods used by a Business Group.
PAY_PAYMENT_TYPES Types of payment that can be processed by the system.
PAY_PAYROLL_ACTIONS Holds information about a payroll process.
PAY_PEOPLE_GROUPS People group flexfield information.
PAY_PERSONAL_PAYMENT_METHODS_F Personal payment method details for an employee.
PAY_RUN_RESULTS Result of processing a single element entry.
PAY_RUN_RESULT_VALUES Result values from processing a single element entry.
PAY_SECURITY_PAYROLLS List of payrolls and security profile access rules.
PAY_INPUT_VALUES_F Input value definitions for specific elements.


BOM – Bills Of Material

Table Name Description
BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table




WIP – Work in Process

Table Name Description
WIP_DISCRETE_JOBS Discrete jobs
WIP_ENTITIES Information common to jobs and schedules
WIP_LINES Production lines
WIP_MOVE_TRANSACTIONS Shop floor move transactions
WIP_MOVE_TXN_ALLOCATIONS Move transaction allocations for repetitive schedules
WIP_OPERATIONS Operations necessary for jobs and schedules
WIP_OPERATION_NETWORKS Operation dependency
WIP_OPERATION_OVERHEADS Overheads for operations in an average costing organization
WIP_OPERATION_RESOURCES Resources necessary for operations
WIP_OPERATION_YIELDS This table keeps all costing information for operation yield costing.
WIP_TRANSACTIONS WIP resource transactions
WIP_TRANSACTION_ACCOUNTS Debits and credits due to resource transactions


FND – Appication Object Library

Table Name Description
FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS New menu tabl for Release 10SC
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_SETS Reports sets
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY Responsibilities
FND_RESP_FUNCTIONS Function Security
FND_USER Application users


JA - Asia/Pacific Localizations

Table Name Description
JAI_CMN_BOE_HDRS Stores BOE header info when a BOE Invoice is created through IL
JAI_CMN_BOE_DTLS Detail table for BOE Invoices
JAI_CMN_TAXES_ALL Master table for Localization Taxes
JAI_CMN_TAX_CTGS_ALL Stores tax categories and their link to excise ITEM classes.
JAI_CMN_TAX_CTG_LINES Stores the tax lines for defined tax categories
JAI_CMN_VENDOR_SITES Stores excise account related information about vendors.
JAI_RGM_DEFINITIONS Stores regime information.
JAI_RGM_TAXES This table stores tax details for transactions having TCS tax type.
JAI_CMN_RG_23AC_I_TRXS Stores Information of RG23A/C records and known as Quantity Register.
JAI_CMN_RG_23AC_II_TRXS Stores Information of RG23A/C Part II Details. Also known as Amount Register
JAI_CMN_RG_23D_TRXS Quantity register for Trading Organizations
JAI_CMN_RG_BALANCES Store the current balances of RG23A, RG23C and PLA Registers
JAI_CMN_RG_PLA_TRXS Stores the Transaction Information of PLA Register.
JAI_CMN_RG_PLA_HDRS Stores PLA header Infomation when a PLA invoice is created in AP module
JAI_CMN_RG_PLA_DTLS Stores PLA Detail Information when a PLA Invoice is created in AP Module


QP – Advanced Pricing

Table Name Description
QP_LIST_HEADERS_B QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.
QP_LIST_LINES QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B.
QP_PRICE_FORMULAS_B QP_PRICE_FORMULAS_B stores the pricing formula header information.
QP_PRICE_FORMULA_LINES QP_PRICE_FORMULA_LINES stores each component that makes up the formula.
QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES stores product information and pricing attributes.
QP_QUALIFIERS QP_QUALIFIERS stores qualifier attribute information.






XLA - Subledger Accounting

Table Name Description
XLA_EVENTS The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE.
XLA_TRANSACTION_ENTITIES The table XLA_ENTITIES contains information about sub-ledger document or transactions.
XLA_AE_HEADERS The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers.
XLA_AE_LINES The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines.
XLA_DISTRIBUTION_LINKS The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines.
XLA_ACCOUNTING_ERRORS The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program.
XLA_ACCTG_METHODS_B The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers.
XLA_EVENT_TYPES_B The XLA_EVENT_TYPES_B table stores all event types that belong to an event class.
XLA_GL_LEDGERS This table contains ledger information used by subledger accounting.

Order Booked Workflow not populated

Scenario - Order is in Booked status but order lines are not populated in WSH (shipping) tables. 

SET serveroutput ON
SET verify OFF
SPOOL om_14578684.lst;
DECLARE
  l_result    VARCHAR2(30);
  l_db_name   VARCHAR2(60);
  l_line_id   NUMBER;
  l_file_name VARCHAR2(255);
  CURSOR lines
  IS
    SELECT ool.line_id, ool.flow_status_code
    FROM oe_order_lines_all ool
    WHERE ool.header_id             IN (435881) -- header_id
    AND ool.open_flag               = 'Y'
    AND ool.booked_flag             = 'Y'
    AND NVL(ool.fulfilled_flag,'N') = 'N'
    AND ool.flow_status_code        = 'BOOKED'
    AND Nvl(ool.shipping_interfaced_flag,'N')    = 'N'
    AND EXISTS (SELECT 1
    FROM wf_items wfs
    WHERE wfs.item_key        = TO_CHAR(ool.line_id)
    AND wfs.item_type         = 'OEOL'
    AND wfs.end_date       IS NULL
    ) ;

BEGIN
  Oe_debug_pub.debug_ON;
  Oe_debug_pub.initialize;
  Oe_debug_pub.setdebuglevel(5);
  l_file_name := Oe_debug_pub.set_debug_mode('FILE');
  Dbms_Output.put_line('DEBUG FILE IS located AT :' || l_file_name);
  oe_debug_pub.ADD('DEBUG FILE IS located AT :' || l_file_name);

  select name INTO l_db_name from V$DATABASE;
  oe_debug_pub.ADD('Running for databse: ' || l_db_name);

  oe_debug_pub.add('Processing line info ....');
  FOR c IN lines
  LOOP
    l_line_id := TO_CHAR(c.line_id);
    ---
    UPDATE oe_ordeR_lines_all
       SET flow_status_code = 'AWAITING_SHIPPING',
           last_updated_by    = -1,
             last_update_date    = SYSDATE
     WHERE line_id = c.line_id;
    ---
    oe_debug_pub.add('Seting Context for line ' || c.line_id);
    Oe_standard_wf.OEOL_Selector(p_itemtype => 'OEOL', p_itemkey => l_line_id, p_actid => 12345, p_funcmode => 'SET_CTX', p_result => l_result);
    oe_debug_pub.add('Result: '||l_result );
    ---
    oe_debug_pub.add('Calling handleerror for Line...');
    --******* Set this flag to Y if Ship_line activity should be skipped without any checking
    --OE_SHIPPING_WF.G_DEV_SKIP := 'Y';
    wf_engine.HandleError('OEOL',l_line_id, 'SHIP_LINE', 'RETRY', NULL);
  END LOOP;
  Oe_debug_pub.debug_OFF;
  NULL;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(' Error in the base script : '||sqlerrm);
  oe_debug_pub.add('### Error in the base script : '||sqlerrm);
  Oe_debug_pub.debug_OFF;
END;
/
spool OFF;
COMMIT;

Internal Requisition Interface

When working with Internal Order Process, I had met the following problem:
1) Created Internal Requisition and Approved it.
2) Ran “Create Internal Order” concurrent request
3) Ran “Order Import” concurrent request in Oracle Order Management module.
4) Navigated to Order Organizer  to find Internal Sales Order that created, but can not found.
I had do step by step below to track and fix this issue:

a)Check the data of Internal Requisition in interface table, I use these SQL:

SELECT orig_sys_document_ref
  FROM oe_headers_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

SELECT orig_sys_document_ref
  FROM oe_lines_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

After run those SQL, the result is no row, so there is no date on interface table

b)I run the following SQL to check the Internal Requisition already import to Sales Order or not?

SELECT 'Exists in OM main'
  FROM oe_order_headers_all
 WHERE order_source_id = 10
   AND source_document_id IN (SELECT requisition_header_id
                                FROM po_requisition_headers_all
                               WHERE segment1 = '&Internal_Req_num');

SELECT porh.segment1 purchase_req, ooh.order_number sales_order,
       ool.line_number so_line_no, porl.line_num pur_req_line_no
  FROM oe_order_headers_all ooh,
       po_requisition_headers_all porh,
       po_requisition_lines_all porl,
       po_req_distributions_all pord,
       oe_order_lines_all ool,
       po_system_parameters_all posp
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND ooh.org_id = posp.org_id
   AND ool.header_id = ooh.header_id
   AND ool.orig_sys_line_ref NOT LIKE '%OE_ORDER_LINES_ALL%'
   AND ool.source_document_line_id IS NOT NULL
   AND porh.segment1 = '&Internal_Req_num';

Its still no record, that mean this Internal Requisition has not been imported and not in interface table yet.

c)I don’t know why this internal requisition can not import but the transfer flag is set to ‘Y’. I tried to update this Flag and run process again (Recommend do this in Test Instance first):

UPDATE po_requisition_headers_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

UPDATE po_requisition_lines_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

Ran the ‘Create Internal Orders’ again.
Ran the ‘Order Import Process’ then check the Sales Order had been created.
No related posts.

Internal Requisition Interface

When working with Internal Order Process, I had met the following problem:
1) Created Internal Requisition and Approved it.
2) Ran “Create Internal Order” concurrent request
3) Ran “Order Import” concurrent request in Oracle Order Management module.
4) Navigated to Order Organizer  to find Internal Sales Order that created, but can not found.
I had do step by step below to track and fix this issue:

a)Check the data of Internal Requisition in interface table, I use these SQL:

SELECT orig_sys_document_ref
  FROM oe_headers_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

SELECT orig_sys_document_ref
  FROM oe_lines_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

After run those SQL, the result is no row, so there is no date on interface table

b)I run the following SQL to check the Internal Requisition already import to Sales Order or not?

SELECT 'Exists in OM main'
  FROM oe_order_headers_all
 WHERE order_source_id = 10
   AND source_document_id IN (SELECT requisition_header_id
                                FROM po_requisition_headers_all
                               WHERE segment1 = '&Internal_Req_num');

SELECT porh.segment1 purchase_req, ooh.order_number sales_order,
       ool.line_number so_line_no, porl.line_num pur_req_line_no
  FROM oe_order_headers_all ooh,
       po_requisition_headers_all porh,
       po_requisition_lines_all porl,
       po_req_distributions_all pord,
       oe_order_lines_all ool,
       po_system_parameters_all posp
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND ooh.org_id = posp.org_id
   AND ool.header_id = ooh.header_id
   AND ool.orig_sys_line_ref NOT LIKE '%OE_ORDER_LINES_ALL%'
   AND ool.source_document_line_id IS NOT NULL
   AND porh.segment1 = '&Internal_Req_num';

Its still no record, that mean this Internal Requisition has not been imported and not in interface table yet.

c)I don’t know why this internal requisition can not import but the transfer flag is set to ‘Y’. I tried to update this Flag and run process again (Recommend do this in Test Instance first):

UPDATE po_requisition_headers_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

UPDATE po_requisition_lines_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

Ran the ‘Create Internal Orders’ again.
Ran the ‘Order Import Process’ then check the Sales Order had been created.
No related posts.

Payroll - Element Updation ( Basic/HRA etc)

STEP I  -- Insert into XYKA_SALARY_API

STEP II -- Call Procedure import_records

        -- Records will be populated into xyka_salary_proposal
       
STEP III  -- process_salary_proposal_api ( Main call to hr_maintain_proposal_api.insert_salary_proposal )

CREATE SEQUENCE XYKA_SALARY_PROPOSAL_IDS
  START WITH 1000
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

CREATE TABLE XYKA_SALARY_PROPOSAL
(
  X_PROPOSAL_ID      NUMBER,
  X_EMPLOYEE_NUMBER  NUMBER,
  X_EFFECTIVE_DATE   DATE,
  X_NET_AMOUNT       NUMBER,
  X_PROCESS_FLAG     CHAR(1 BYTE),
  X_APPROVED_FLAG    CHAR(1 BYTE),
  X_REMARKS          VARCHAR2(240 BYTE),
  CREATION_DATE      DATE,
  CREATED_BY         NUMBER,
  LAST_UPDATE_DATE   DATE,
  LAST_UPDATED_BY    NUMBER,
  LAST_UPDATE_LOGIN  NUMBER
);


CREATE TABLE XYKA_SALARY_API
(
  X_EMPLOYEE_NUMBER  NUMBER,
  X_EFFECTIVE_DATE   DATE,
  X_NET_AMOUNT       NUMBER,
  X_TRANSFER_DATE    DATE,
  X_REMARKS          VARCHAR2(240 BYTE)
)

select * from XYKA_SALARY_PROPOSAL  

--drop table xyka.XYKA_SALARY_PROPOSAL 

commit

-------------------------------------------------

select ELEMENT_NAME , REPORTING_NAME , decode (PROCESSING_TYPE ,'R','REC','N','NREC') from PAY_ELEMENT_TYPES_F

select * from PAY_ELEMENT_TYPES_F 

CREATE OR REPLACE FORCE VIEW APPS.XYKA_SALARY_PROPOSAL_V
(
   SALARY_API_ROWID,
   PROPOSAL_ID,
   EMPLOYEE_NUMBER,
   EMPLOYEE_NAME,
   BUSINESS_GROUP_ID,
   GRADE,
   ASSIGNMENT_ID,
   ELEMENT_ENTRY_ID,
   DEPARTMENT,
   EFFECTIVE_DATE,
   PERIOD_NAME,
   NET_AMOUNT,
   REMARKS,
   APPROVED_FLAG,
   PROCESS_FLAG,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_LOGIN
)
AS
   SELECT   ROWID salary_api_rowid,
            xsp.x_proposal_id proposal_id,
            xsp.x_employee_number employee_number,
            (SELECT   last_name
               FROM   per_people_f
              WHERE   employee_number = TO_CHAR (xsp.x_employee_number)
                      AND ROWNUM = 1)
               employee_name,
            (SELECT   business_group_id
               FROM   per_people_f
              WHERE   employee_number = TO_CHAR (xsp.x_employee_number)
                      AND ROWNUM = 1)
               business_group_id,
            (SELECT   pg.NAME
               FROM   per_people_f ppf, per_assignments_f paf, per_grades pg
              WHERE   ppf.person_id = paf.person_id
                      AND paf.grade_id = pg.grade_id
                      AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                   AND  ppf.effective_end_date
                      AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                   AND  paf.effective_end_date
                      AND (pg.date_to IS NULL
                           OR xsp.x_effective_date BETWEEN pg.date_from
                                                       AND  pg.date_to)
                      AND ppf.employee_number =
                            TO_CHAR (xsp.x_employee_number))
               grade,
            (SELECT   paf.assignment_id
               FROM   per_people_f ppf, per_assignments_f paf
              WHERE   ppf.person_id = paf.person_id
                      AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                   AND  ppf.effective_end_date
                      AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                   AND  paf.effective_end_date
                      AND ppf.employee_number =
                            TO_CHAR (xsp.x_employee_number))
               assignment_id,
            (SELECT   element_entry_id
               FROM   pay_element_entries_f
              WHERE   assignment_id =
                         (SELECT   paf.assignment_id
                            FROM   per_people_f ppf, per_assignments_f paf
                           WHERE   ppf.person_id = paf.person_id
                                   AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                                AND  ppf.effective_end_date
                                   AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                                AND  paf.effective_end_date
                                   AND ppf.employee_number =
                                         TO_CHAR (xsp.x_employee_number))
                      AND SYSDATE BETWEEN effective_start_date
                                      AND  effective_end_date
                      AND element_type_id =
                            (SELECT   element_type_id
                               FROM   pay_element_types_f_tl
                              WHERE   element_name = 'Basic'
                                      AND element_type_id = 92))  --change element as per requirement
               element_entry_id,
            (SELECT   NAME
               FROM   hr_all_organization_units_tl
              WHERE   organization_id =
                         (SELECT   organization_id
                            FROM   per_people_f ppf, per_assignments_f paf
                           WHERE   ppf.person_id = paf.person_id
                                   AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                                AND  ppf.effective_end_date
                                   AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                                AND  paf.effective_end_date
                                   AND ppf.employee_number =
                                         TO_CHAR (xsp.x_employee_number)))
               department,
            xsp.x_effective_date effective_date,
            TO_CHAR (xsp.x_effective_date, 'MON-RR') period_name,
            xsp.x_net_amount net_amount,
            xsp.x_remarks remarks,
            xsp.x_approved_flag approved_flag,
            xsp.x_process_flag process_flag,
            xsp.creation_date,
            xsp.created_by,
            xsp.last_update_date,
            xsp.last_updated_by,
            xsp.last_update_login
     FROM   xyka_salary_proposal xsp;
    
---------------------------------------------------------------------

CREATE OR REPLACE PACKAGE APPS.xyka_salary_proposal_api
AS
--## ##################################################################
--#
--# Script        :   xyka_salary_api.sql
--# Author        :   Rushikesh Ranade
--# System        :   YK Almoayyed and Sons
--# Date          :   12 Jul 2012 11:54
--# Version       :   1.0
--# Description   :   Customized Package to process HR Salary APIs
--#
--# Modif Hist :
--# -------------
--#
--# Usage Instructions:
--# -------------------
--# The Objects has to be called from the Salary API Form and Salary
--# API Requests
--#
--## ##################################################################

   /* Package Objects Definition */
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- l_status specifies the Process Status
-- L_message specifies the processing messages
-- ------------------
   PROCEDURE import_records (
      p_out1       OUT      VARCHAR2,
      p_out2       OUT      VARCHAR2,
      p_period     IN       VARCHAR2,
      p_user_id    IN       NUMBER,
      p_login_id   IN       NUMBER
   );

-- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
   PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2);
END xyka_salary_proposal_api;

--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.xyka_salary_proposal_api
AS
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- ------------------
   PROCEDURE import_records (
      p_out1       OUT      VARCHAR2,
      p_out2       OUT      VARCHAR2,
      p_period     IN       VARCHAR2,
      p_user_id    IN       NUMBER,
      p_login_id   IN       NUMBER
   )
   IS
      /* Local Variables */
      l_success_count   NUMBER         := 0;
      l_rec_count       NUMBER         := 0;
      l_status          VARCHAR2 (20);
      l_message         VARCHAR2 (240);

      CURSOR salary_api (c_period_name IN VARCHAR2)
      IS
         SELECT x_employee_number, x_effective_date, x_net_amount
           FROM xyka_salary_api
          WHERE TO_CHAR (x_effective_date, 'MON-RR') = c_period_name
            AND x_transfer_date IS NULL;
   BEGIN
      /* Transfers the records into the Salary API Tables */
      fnd_file.put_line
         (fnd_file.output,
             'YKA - Upload Salary Proposal for a Period (XYKASALP) Started on : '
          || TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
         );
      fnd_file.put_line
         (fnd_file.output,
          '-----------------------------------------------------------------------------'
         );
      fnd_file.put_line (fnd_file.output, '         ');

      FOR r IN salary_api (p_period)
      LOOP
         fnd_file.put_line (fnd_file.output,
                               'Processing for Employee Number # '
                            || r.x_employee_number
                           );
         /* Re-Sets the Variables */
         l_status := NULL;
         l_message := NULL;
         l_rec_count := l_rec_count + 1;

         /* Start populating the Salary API Main table */
         BEGIN
            INSERT INTO xyka_salary_proposal
                        (x_proposal_id,
                         x_employee_number, x_effective_date,
                         x_net_amount, x_process_flag, x_approved_flag,
                         x_remarks, creation_date, created_by,
                         last_update_date, last_updated_by, last_update_login
                        )
                 VALUES (xyka_salary_proposal_ids.NEXTVAL,
                         r.x_employee_number, r.x_effective_date,
                         r.x_net_amount, 'N', 'N',
                         NULL, SYSDATE, p_user_id,
                         SYSDATE, p_user_id, p_login_id
                        );
         EXCEPTION
            WHEN OTHERS
            THEN
               l_status := 'ERROR';
               l_message := SQLERRM;
               fnd_file.put_line (fnd_file.output,
                                  ' *** Error Message *** : ' || SQLERRM
                                 );
         END;

         /* If any error occured while transferring the record */
         IF l_status = 'ERROR'
         THEN
            /* Updates the Base Table with the error message */
            UPDATE xyka_salary_api
               SET x_remarks = l_message
             WHERE x_employee_number = r.x_employee_number
               AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
         ELSIF l_status IS NULL
         THEN
            l_success_count := l_success_count + 1;

            /* If no processing errors occurred then, updates the Transfer Date */
            UPDATE xyka_salary_api
               SET x_remarks = 'TRANSFERRED',
                   x_transfer_date = SYSDATE
             WHERE x_employee_number = r.x_employee_number
               AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
         END IF;

         /* Commits the Changes */
         COMMIT;
      END LOOP;

      fnd_file.put_line (fnd_file.output, '         ');
      fnd_file.put_line (fnd_file.output,
                            'Total Records processed by the Upload program : '
                         || l_rec_count
                        );
      fnd_file.put_line (fnd_file.output,
                            'Total Records Uploaded by the Upload program : '
                         || l_success_count
                        );
      fnd_file.put_line (fnd_file.output, '         ');
      fnd_file.put_line
         (fnd_file.output,
             'YKA - Upload Salary Proposal for a Period (XYKASALP) Completed on : '
          || TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
         );
      fnd_file.put_line
         (fnd_file.output,
          '-----------------------------------------------------------------------------'
         );
   END;

  -- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
   PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2)
   IS
      CURSOR salary_api (c_period_name IN VARCHAR2)
      IS
         SELECT proposal_id, employee_number, net_amount, effective_date,
                business_group_id, assignment_id, element_entry_id
           FROM xyka_salary_proposal_v
          WHERE TO_CHAR (effective_date, 'MON-RR') = c_period_name
            AND approved_flag = 'Y'
            AND process_flag = 'N';

      /* Local Variables */
      l_row_id                       VARCHAR2 (30);
      l_pay_proposal_id              NUMBER;
      l_element_entry_id             NUMBER;
      l_ovn                          NUMBER;
      l_inv_next_sal_date_warning    BOOLEAN;
      l_inv_next_perf_date_warning   BOOLEAN;
      l_proposed_salary_warning      BOOLEAN;
      l_approved_warning             BOOLEAN;
      l_payroll_warning              BOOLEAN;
      l_dummy                        BOOLEAN;
      l_approved                     VARCHAR2 (1);
      l_status                       VARCHAR2 (20);
      l_message                      VARCHAR2 (240);
   BEGIN
      FOR r IN salary_api (p_period_name)
      LOOP
         l_status := NULL;
         l_message := NULL;
     l_element_entry_id:=NULL;

         BEGIN
            /* Start processing the API */
            hr_maintain_proposal_api.insert_salary_proposal
                 (p_pay_proposal_id                => l_pay_proposal_id,
                  p_assignment_id                  => r.assignment_id,
                  p_business_group_id              => r.business_group_id,
                  p_change_date                    => r.effective_date,
                  p_comments                       => NULL,
                  p_next_sal_review_date           => NULL,
                  p_proposal_reason                => NULL,
                  p_proposed_salary_n              => r.net_amount,
                  p_forced_ranking                 => NULL,
                  p_performance_review_id          => NULL,
                  p_attribute_category             => NULL,
                  p_attribute1                     => NULL,
                  p_attribute2                     => NULL,
                  p_attribute3                     => NULL,
                  p_attribute4                     => NULL,
                  p_attribute5                     => NULL,
                  p_attribute6                     => NULL,
                  p_attribute7                     => NULL,
                  p_attribute8                     => NULL,
                  p_attribute9                     => NULL,
                  p_attribute10                    => NULL,
                  p_attribute11                    => NULL,
                  p_attribute12                    => NULL,
                  p_attribute13                    => NULL,
                  p_attribute14                    => NULL,
                  p_attribute15                    => NULL,
                  p_attribute16                    => NULL,
                  p_attribute17                    => NULL,
                  p_attribute18                    => NULL,
                  p_attribute19                    => NULL,
                  p_attribute20                    => NULL,
                  p_object_version_number          => l_ovn,
                  p_multiple_components            => 'N',
                  p_approved                       => 'Y',
                  p_validate                       => FALSE,
                  p_element_entry_id               => r.element_entry_id,
                  p_inv_next_sal_date_warning      => l_inv_next_sal_date_warning,
                  p_proposed_salary_warning        => l_proposed_salary_warning,
                  p_approved_warning               => l_approved_warning,
                  p_payroll_warning                => l_payroll_warning
                 );
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               /* Catches the Exceptions */
               l_message := SQLERRM;
               l_status := 'ERROR';
         END;

         /* If any error occured then */
         IF l_status IS NOT NULL
         THEN
            UPDATE xyka_salary_proposal
               SET x_remarks = l_message,
                   x_approved_flag = 'N',
                   x_process_flag = 'N'
             WHERE x_proposal_id = r.proposal_id;

            COMMIT;
         /* If the proposal is processed then */
         ELSIF l_status IS NULL
         THEN
            UPDATE xyka_salary_proposal
               SET x_remarks = 'PROCESSED',
                   x_process_flag = 'Y'
             WHERE x_proposal_id = r.proposal_id;

            COMMIT;
         END IF;
      END LOOP;
   END process_salary_proposal_api;
END xyka_salary_proposal_api;
/
------------------ Execute --------------
INSERT INTO XYKA_SALARY_API (X_EMPLOYEE_NUMBER,X_EFFECTIVE_DATE,X_NET_AMOUNT,X_TRANSFER_DATE,X_REMARKS)VALUES(14699,TO_DATE (SYSDATE - 14, 'DD-MM-YY'),1200,NULL,'5% salary hike JUL-12');

 select to_date(sysdate-14,'DD-MM-YY') from dual

 commit 


 select * from XYKA_SALARY_API

 delete from XYKA_SALARY_API
 --edit XYKA_SALARY_API

 SELECT x_employee_number, x_effective_date, x_net_amount
           FROM xyka_salary_api
          WHERE TO_CHAR (x_effective_date, 'MON-RR') = :c_period_name
            AND x_transfer_date IS NULL
           
select * from fnd_user where user_name = '99999'
                
------------------ Procedure 1 -----------------------                 

DECLARE
   p_out1       VARCHAR2 (240);
   p_out2       VARCHAR2 (240);
   p_period     VARCHAR2 (10) := 'JUL-12';
   p_user_id    number := 1295;
   p_login_id   number := 1295;
BEGIN
   xyka_salary_proposal_api.import_records (p_out1,
                                            p_out2,
                                            p_period,
                                            p_user_id,
                                            p_login_id);
END;           

commit

-- Record gets added into

select * from xyka_salary_proposal

delete from xyka_salary_proposal  where X_APPROVED_FLAG = 'Y'

-- on success x_remark gets updated to TRANSFERRED  and trnsfer_date to SYSDATE

select * from xyka_salary_api

UPDATE   xyka_salary_proposal
   SET   X_APPROVED_FLAG = 'Y' ,
         X_EFFECTIVE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
         LAST_UPDATE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
         CREATION_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY')
 --WHERE   X_EMPLOYEE_NUMBER = 14699

commit ;

select * from xyka_salary_proposal

--delete from xyka_salary_proposal where X_REMARKS  like 'ORA-20001: This date already exists. Please enter a new date.'


SELECT proposal_id, employee_number, net_amount, effective_date,
                business_group_id, assignment_id, element_entry_id
           FROM xyka_salary_proposal_v
          WHERE TO_CHAR (effective_date, 'MON-RR') = :c_period_name
            AND approved_flag = 'Y'
            AND process_flag = 'N';
           
           
-- call process_salary_proposal_api

DECLARE
   p_period_name   VARCHAR2 (10) := 'JUL-22';
BEGIN
   xyka_salary_proposal_api.process_salary_proposal_api (p_period_name);
END;

COMMIT

/* If any error occured then */
        x_approved_flag = 'N',
        x_process_flag = 'N'
       
ORA-20001: The effective date for all DateTracked operations must not include a time component.

/* If the proposal is processed then */
        x_remarks = 'PROCESSED',
        x_process_flag = 'Y'

select * from xyka_salary_proposal

select * from XYKA_SALARY_PROPOSAL_V

PO - Action History

********** PO - Action History **********

  SELECT   pah.object_id,
           pah.object_type_code,
           pah.object_sub_type_code,
           pah.sequence_num,
           pah.last_update_date,
           pah.last_updated_by,
           pah.creation_date,
           pah.created_by,
           pah.action_code,
           plc1.displayed_field Action,
           pah.action_date,
           pah.employee_id,
           hr.full_name Employee,
           pah.approval_path_id,
           pah.note,
           pah.object_revision_num,
           pah.offline_code,
           pah.last_update_login,
           pah.request_id,
           pah.program_application_id,
           pah.program_id,
           pah.program_update_date,
           pah.program_date,
           pah.approval_group_id
    FROM   po_action_history pah,
           po_lookup_codes plc1,
           po_lookup_codes plc2,
           per_all_people_f hr
   WHERE       pah.object_id = 176977  -- po_header_id
           AND pah.object_type_code <> 'RELEASE'
           AND pah.object_sub_type_code = 'STANDARD'
           AND hr.person_id = pah.employee_id
           AND pah.action_date BETWEEN hr.effective_start_date
                                   AND  hr.effective_end_date
           AND plc1.lookup_code(+) = pah.action_code
           AND plc1.lookup_type(+) = 'APPROVER ACTIONS'
           AND plc2.lookup_code(+) = pah.action_code
           AND plc2.lookup_type(+) = 'CONTROL ACTIONS'
ORDER BY   pah.sequence_num


********* PO - Approval Hierarchy *******
SELECT   det.lev "Level",
         det.subordinate_POSITION_ID "Subordinate Position Id",
         det.NAME "Name",
         DECODE (
            (SELECT   COUNT ( * ) cnt
               FROM   PER_ALL_ASSIGNMENTS_F paa
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)),
            1,
            (SELECT   full_name
               FROM   PER_ALL_ASSIGNMENTS_F paa, per_all_people_f pp
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)
                      AND SYSDATE BETWEEN pp.effective_start_date
                                      AND  NVL (pp.effective_end_date,
                                                SYSDATE)
                      AND paa.PERSON_ID = pp.person_id),
            'Multiple Employees attached to this Position'
         )
            Employee_Name
  FROM   (SELECT   ps.lev, PS.subordinate_POSITION_ID, pos.NAME
            FROM   (    SELECT   LEVEL lev, PS.subordinate_POSITION_ID
                          FROM   per_pos_structure_elements PS
                    START WITH   PS.POS_STRUCTURE_VERSION_ID = NVL (62, 0)
                                 AND PS.SUBORDINATE_POSITION_ID = 4087
                    CONNECT BY   PRIOR PS.PARENT_POSITION_ID =
                                    PS.SUBORDINATE_POSITION_ID
                                 AND PS.POS_STRUCTURE_VERSION_ID =
                                       NVL (62, 0)) ps,
                   per_positions POS
           WHERE   ps.subordinate_POSITION_ID = pos.POSITION_ID) det

********* PO - AP *********

SELECT   ih.invoice_num "Invoice Num",
         pla.line_num "PO Line Num",
         plla.shipment_num "PO Shipment Num",
         plla.quantity_billed "Shipment Qty Billed"
  FROM   ap_invoices ih,
         ap_invoice_distributions id,
         po_headers ph,
         po_distributions pd,
         po_lines pla,
         po_line_locations plla
 WHERE       ih.invoice_id(+) = id.invoice_id
         AND id.po_distribution_id(+) = pd.po_distribution_id
         AND ph.po_header_id = pd.po_header_id
         AND ph.po_header_id = pla.po_header_id
         AND pla.po_line_id = plla.po_line_id
         AND plla.line_location_id = pd.line_location_id
         AND ih.invoice_num IS NOT NULL
         AND NVL (plla.po_release_id, 0) = 0
         AND ph.po_header_id = 176977