Oracle HRMS Payroll Tables


Oracle HRMS Payroll Tables

The tables that are date-tracked will have two columns:-
Effective_start_date
Effective_end_date

All the objects in Oracle HRMS or Payroll that end with _x have a where clause where sysdate between Effective_start_date AND Effective_end_date

Hence these are views that return records as of SYSDATE.

The primary keys of the date tracked columns includes Effective_start_date & Effective_end_date.

PAY_ELEMENT_TYPES_F - Payroll Elements
Firstly, we create some elements, which get created in table PAY_ELEMENT_TYPES_F. The primary key is a combination of element_type_Id along with Date Track columns.

When will you join to pay_element_types_f ?
1. To display the name of Element in Reports
2. When payroll runs, the results are stored in PAY_RUN_RESULTS, which stores a reference to element_type_Id.

PAY_ELEMENT_LINKS_F - Payroll Element Links
To make payroll elements eligible to a group of people, you create Element Links.

The Primary key is ELEMENT_LINK_ID with date-track columns.

When will you commonly use element_link_Id ?
1. When querying on Element Entry[PAY_ELEMENT_ENTRIES_F], a join can be made using ELEMENT_LINK_ID
2. The reason Oracle uses ELEMENT_LINK_ID in Element Entry to work out Costing Segments based on Payroll Costing Hierarchy.

PER_ALL_PEOPLE_F - Employee record
It is well known that Employee records are stored in PER_ALL_PEOPLE_F. Its a date track table with primary key being person_Id. This table also has party_Id, because Oracle creates a party in TCA as soon as a record in per_all_people_f gets created.

Main usage of per_all_people_f:-
1. To get the name of the person

2. To get the date of birth or tax Id of the person

Note: - The application uses PER_PEOPLE_F, as that is a secured view layer on top of PER_ALL_PEOPLE_F

PER_ALL_ASSIGNMENTS_F - Assignment table
This is the most central table in Oracle Payroll. Payroll engine uses this table as the main driver.

Why so: Because Element Entries are stored against Assignment record.

This table is date-tracked, with primary key being assignment_Id

Usage of per_all_assignments_f?

1. Find position_Id, hence position, or grade, the organization for the persons assignment.

2. It has foreign key to person_id. Each person Id can have no more than one primary assignment at any given point in time.

3. Pay run results and also the pay_assignment actions refers to this table.

PER_PERSON_TYPES - Person type
This is the master table for Person Types. Some examples of Person Types are Employees, Casuals, Applicants etc.

The primary key is person_type_id.

But please do not try joining this with person_type_id in per_all_people_f.

Instead join that to per_person_type_usages_f_x will give you person_type usage as of SYSDATE.

For any other date, use the classic p_date between effective_start_date and effective_end_date.
PAY_ELEMENT_ENTRIES_F & PAY_ELEMENT_ENTRY_VALUES_F - Tables effected when element entry is done
These two tables are inserted into when fresh Element Entries are created.


PAY_ELEMENT_ENTRIES_F

Each Element that gets attached to an Assignment will have an entry in PAY_ELEMENT_ENTRIES_F.

For each assignment you will have one or more records in PAY_ELEMENT_ENTRIES_F table.

It is logical that PAY_ELEMENT_ENTRIES_F has following columns

Assignment_id

Element_link_id

ELEMENT_TYPE_ID

This table is date-tracked too. Please do not ask my where there was a need to store both ELEMENT_TYPE_ID and also ELEMENT_LINK_ID in this table.

Just storing the ELEMENT_LINK_ID could suffice. However, i guess Oracle did so for Performance reasons.

PAY_ELEMENT_ENTRY_VALUES_F

This table stores a reference to PAY_ELEMENT_ENTRIES_F. In plain English, this table captures the entry value for the elements.

The Input Value is stored in SCREEN_ENTRY_VALUE. The name suggests that it stores the Formatted Screen value. However, I can assure you that SCREEN_ENTRY_VALUE stores the non formatted value. For example screen might show HH:MM as 03:30, but SCREEN_ENTRY_VALUE will have 3.5

This table is date-tracked, and its primary key is INPUT_VALUE_ID.

Where can I commonly join INPUT_VALUE_ID to ?

To the payroll run results value table, i.e. PAY_RUN_RESULT_VALUES

You can also join to PAY_COSTS, if you wish to work out which input value contributed to a specific Payroll Costed Amount.

Query for AR Customer in Oracle Apps 11i

AR Customer

SELECT rc.customer_number,
       rc.customer_name,
       hp.party_id,
       hp.party_type,
       rc.tax_reference,
       hca.cust_account_id,
       hca.payment_term_id,
       hps.location_id,
       hcsu.LOCATION,
       hcsu.site_use_code,
       hcsa.bill_to_flag,
       hcsu.gl_id_rec receiving_acc_ccid,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,hl.state,
       hl.country,
       hl.postal_code
  FROM ra_customers rc,
       hz_parties hp,
       hz_party_sites hps,
       hz_locations hl,
       hz_cust_accounts_all hca,
       hz_cust_acct_sites_all hcsa,
       hz_cust_site_uses_all hcsu
 WHERE rc.party_id = hp.party_id
   AND hp.party_id = hps.party_id
   AND hps.location_id = hl.location_id
   AND hp.party_id = hca.party_id
   AND hcsa.party_site_id = hps.party_site_id
   AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
   AND hca.cust_account_id = hcsa.cust_account_id

   AND rc.customer_number = '&Customer_Number';

NOTE: Enter Customer Number it gives all the information of that customer.

Assigning Default Template for XML Publisher Report

Navigation:

1. System Administrator > System Administration > Concurrent > Programs

2. Query your Program Name

3. Click on Onsite Setting

4. From the LOV for the "Template" field select a template as default.



Query For find Last Query executed on the form

List the invoices for Trading Partner 'CDS, Inc' from the application.

Payables Manager > Invoices > Inquiry > Invoices > give "CDS, Inc" in the Trading Partner Name field > Find

Now we want to find the database query executed in the backend to show this data for you. Then goto

NAVIGATION:

Help > Diagnostics > Examine
Enter the following values:
Block: SYSTEM
Field: LAST_QUERY

Value filed displays the Query for you.
Copy the query and executed in SQL Developer/TOAD/SQL Plus to check the same data!


How to Print XML Publisher report SQL and the values of the parameters

How to Print XML Publisher report SQL and the values of the parameters?

When we enabled the DebugFlag (P_DEBUG_FLAG) the log file will provides the following:
Ø  Begin and End time of the report
Ø  Time taken to execute report SQL and generate XML
Ø  Prints the report SQL and the values of the parameters.

How do I set the DebugFlag (P_DEBUG_FLAG)?
Set P_DEBUG_FLAG when P_DEBUG_FLAG is not already set for the report.
Navigate to System Administrator > Concurrent > Program > Define.
Ø  Search for the report (e.g., Account Analysis Report), "XLNBRPT" is the short name in the Concurrent Program window.
Ø  Click on Parameters button.
Ø  Move to the last parameter.
Ø  Add a new parameter called DebugFlag with the following:
ü  Sequence - 910 (If it exists, give a different number)
ü  Parameter- P_DEBUG_FLAG
ü  Description - DebugFlag
ü  Enabled - Check
ü  Value Set - XLA_SRS_NO_VALIDATION
ü  Default Type - Constant
ü  Default Value - Y
ü  Required - Uncheck
ü  Display - Check Prompt
ü  P_DEBUG_FLAG
ü  Token - DebugFlag (Case sensitive. Give this exact value)

Submit the report with P_DEBUG_FLAG as Y and the log file should include the report SQL.





How to check total outstanding you need to pay to a Supplier

Use the Find Invoices window and choose the Calculate Balance Owed button to see how much you owe a supplier and how many unpaid invoices you have in the system for the supplier.
To calculate the balance owed, Payables subtracts available prepayments from the unpaid invoice amount. Payables does not use unpaid prepayments when it calculates the balance.

If you have foreign currency invoices that do not yet have exchange rates, you can calculate the balance with an exchange rate you enter in the window. The exchange rate you enter will not update the invoices; you are still required to submit Auto Rate or enter an exchange rate in the Invoices window.

How To calculate a supplier or supplier site balance:

Navigation:

Invoices > Inquiry > Invoices
Enter Supplier Name or Supplier Number
and Click on 'Calculate Balance Owed' on the bottom left

1. Open the Find Invoices window from the Invoices window, either by choosing the Find icon from the Toolbar or by selecting Find from the Query menu. Enter the supplier name, site name, or other unique supplier or supplier site information. Optionally restrict the balance to invoices of a particular status or hold. Payables calculate the balance based on the combination of criteria you enter.



It opens "Calculate Balance Owed" form Select the Operating Unit and click on "Calculate" that shows Unpaid Amount.


2. Choose Calculate Balance Owed to have Payables navigate to the Calculate Balance Owed window. Optionally check Take Discount to have Payables reduce the balance by available discounts. Choose Calculate to have Payables display the total due amount and the total number of unpaid invoices that match the criteria you entered. Payables also display and include in its calculation any prepayments.

How To calculate a supplier or supplier site balance in a foreign currency:

1. Open the Find Invoices window from the Invoices window, either by choosing the Find icon from the Toolbar or by selecting Find from the Query menu. Enter the supplier name, site name, or other unique supplier or supplier site information. Enter the currency for which you would like to calculate the balance. Optionally restrict the balance to invoices of a particular status or hold. Payables calculate the balance based on the combination of criteria you enter.

2. Choose Calculate Balance Owed to have Payables navigate to the Calculate Balance Owed window.
Enter a default exchange rate for Payables to use to convert into your functional currency your foreign currency invoices that do not have exchange rates. Choose Calculate to have Payables display for the invoices that match your criteria the Balance Owed in both the foreign and functional currency, and the total number of unpaid invoices. Payable also displays and includes in its calculation any prepayments.







Register Discoverer Report in Oracle Applications

Steps for Register a Discoverer report in Oracle Apps.

1. Login to Application Developer-->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.
    E.g:
  • Function: TEST_DISCO
  • User Function Name: Discoverer Report
  • Description: Discoverer Report
3. In Properties Tab
  • Select Type “SSWA plsql function”
  • Maintenance Mode Support – None
  • Context Dependence - Responsibility
4. In Form Tab Parameters --> Enter the Workbook name created previously
     E.g:
  • workbook=TEST_WORKBOOK
  • In case you want to give some parameters then you can use:
workbook=<workbook_name>&parameters=<Parameter_nam e1>~<value1>*<Parameter_name2>~<value2>*<Parameter _name3>~<value3>*
 
Example
workbook=<workbook_name>&Parameters=age~26*salary~ 1000*

5. In Web HTML Tab

Enter HTML Call - OracleOasis.RunDiscoverer

6. Now you can add this Function in any existing or new Menu

Also Note that Discoverer Report cannot be invoked when you’ve direct login into Oracle Applications through http://<url>:<port number>/dev60cgi/f60cgi
You need to access through logging in from the browser web page. Otherwise it will give an Error.

7. Now navigate to Responsibility which contain the Menu to which you added this function.

8. Click the Function Name to Display the report.


How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...