Oracle Payroll Queries

Oracle Payroll Queries,AskHareesh Blog for OracleApps

Oracle Receivables Module Technical Details
Oracle Accounts Receivable uses the following tables for recording customer account information:

The major tables containing parties and customer accounts information in Oracle Receivables are grouped by business function.

Customer and related tables:

Transaction Tables:

  • A party is an entity that can enter into a business relationship.
  • This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.
  • Parties can be one of four types:
    • Organization - Oracle Corporation
    • Person - Jane Doe
    • Group – Doc Household
    • Relationship - Jane Doe at Oracle Corporation
  • A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
  • This table stores information about an address such as: street address and postal code.
  • This table provides physical location information about parties (organizations and people) and customer accounts.
  • Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.
  • This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
  • One party can point to one or more party sites.
  • One location can point to one or more party site.
  • Party sites serve as the intersection between parties and locations, allowing for a many-to-many relationship between the two.
  • This table stores information about relationships between two entities, for example, one party and another party.
  • The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship type is “Parent Of,” then a holding company could be the “SUBJECT_ID” in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created.
  • A party can have different relationships with one or more other parties that can change over time.
  • This table stores a variety of information about a party of type Organization.
  • This table gets populated when a party of type ORGANIZATION is created.
  • Historical data is also stored in this table.
  • This table stores a variety of information about a party of type Person.
  • For example, this table could contain the correct spelling and phonetic pronunciation of the person’s name.
  • Some information in this table may also be entered into the HZ_PARTIES table.
  • This table stores a variety of information about an organization contact.
  • The records in this table provide information about a contact position such as job title, rank, and department.
  • This table is not used to store information about a specific person or organization. For example, this table may include a record for the position of Vice President of Manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position.
  • This table stores information about customer/financial relationships established between a Party and the deploying company.
  • Because a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.
  • This table stores information about customer/financial account sites information.
  • Stores information about customer sites. One customer account can have multiple sites.
  • This table stores information about the business purposes assigned to a customer account site.
  • A customer account site can have multiple purposes, but each record in this table only specifies one purpose for a customer account site. For example, a customer account site may be assigned as a ship-to site in one record and as a bill-to site in another record.
  • This table stores information about a role or function that a party performs as related to a customer account. For example, Jane Doe might be the Legal Contact for a specific customer account of Corporation ABC.
  • Note that account ownership such as financial responsibility for an account is determined by the single party ID that is stored directly on the HZ_CUST_ACCOUNTS table.
  • This table stores information about how to communicate with parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
  • Each medium or method should be stored as a separate method in this table. For example, the attributes of a complete telephone number connection should be stored in a record, while EDI information should be stored in a different record.
  • This table stores invoice, debit memo, commit-ment, chargeback, bills receivable, and credit memo header information.
  • Each row includes general invoice information such as customer, transaction type, and printing instructions.
  • You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables and these are all distinguished by their transaction types stored in RA_CUST_ TRX_ TYPES_ALL.
  • This table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. It describes to the customer the charges that appear on these documents.
  • This table stores all transactions except adjust-ments and miscellaneous cash receipts. A miscellaneous cash receipt is one that is not connected to a customer.
  • All customer-related activity is logged in this table.
  • This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable,
  • receipt, or commitments.
  • This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
  • Each row includes Auto Accounting information
  • as well as standard defaults for the resulting invoices. The primary key for this table is CUST_TRX_TYPE_ID.
  • This table is a Bills Receivable-specific table containing the history of a transaction’s lifecycle.
  • A new row is created each time there is activity on the transaction or the status of the transaction has changed.
  • This table stores the header for the Receivables posting information.
  • This table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions.
  • This table stores one record for each receipt entry.
  • All cash receipts are logged in this table.
  • Oracle Receivables creates records concurrently in the AR_CASH _RECEIPT_ HISTORY_ ALL, AR_PAYMENT_ SCHEDULES_ ALL, AR_DISTRI-BUTIONS_ALL, and AR_ RECEIVABLE_ APPLICA-TIONS_ALL tables for invoice-related receipts.
  • For receipts that are not related to invoices, records are created in the AR_MISC_CASH_ DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ ALL table.
  • This table stores all of the activity that is contained for the life cycle of a receipt.
  • Each row represents one step.
  • The status field for that row tells you which step the receipt has reached.
  • Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.
  • This table stores all accounting entries for cash and credit memo applications.
  • Each row includes the amount applied, status, and accounting flex field information.
  • This table stores all accounting entries for miscellaneous cash applications.
  • Miscellaneous cash cannot be invoiced, such as stock revenue, interest income, and investment income.
  • AR_CASH_RECEIPTS_ALL stores one record for each payment, and this table stores one record for each distribution of the receipt.
  • This table stores the different receipt classes that you define.
  • Receipt classes determine whether the receipt[s] belonging to this class are created manually or automatically, and whether the receipts go through the different steps in a receipt’s life-cycle.
  • This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications.
  • For automatically created receipts, a Payment Method defines the rules for creating these receipts.
  • For manually created receipts, a Payment Method defines a user-definable type for the receipt.
  • Each Payment Method is associated with a set of bank accounts, which forms the set of bank accounts you can assign to your receipt.
  • This table stores information about the adjustment applied to the Invoices.