Oracle FNDLOAD Scripts

Oracle FNDLOAD Scripts

Download Script

Upload Script

1. Lookups
-- ------------- FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program
-- ----------------------------- 
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile
-- --------- 
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link
-- ------------------------------ 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message
-- --------------------- FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS
-- ------------------ 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME" 
       

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function
-- --------------------- 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts
-- --------- 
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set
-- -------------- 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template
--- ---------------------------------------------------------- 
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

11. DATA_TEMPLATE (Data Source .xml file)
--- ---------------------------------------------------------- 
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

12. RTF TEMPLATE (Report Layout .rtf file)
--- ------------------------------------------------------- 
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

Reference :https://blogs.oracle.com/prajkumar/entry/oracle_fndload_scripts

Oracle Payroll Balances

arjoon_gupta@tjx.com

Short Writeup: Oracle Payroll Balances Balances in oracle payroll is nothing but addition/subtraction of 'run result values'. Considering a lay man example of showing year to date salary payment made for particular employee, it may be computed through summing

2:46 PM

 

Balances in oracle payroll is nothing but addition/subtraction of 'run result values'. Considering a lay man example of showing year to date salary payment made for particular employee, it may be computed through summing up run result values generated after each payroll through specified time period or one could use Balances.

We could define our own balance or use one of the seeded balance to take particular element feeds.

Navigation -> Total Compensation -> Basic -> Banlances

Again, balances can be fed by individual elements or through element classification(at the larger level). We should note that both are mutually exclusive. Basic balance information is stored in PAY_BALANCE_TYPES table in R12 .

Dimension:
Dimensions talk about period context of balance i.e. in our previous example, 'one financial year' forms the dimension. similarly dimension can be quarter to date, month to date types. Dimension details are stored in PAY_BALANCE_DIMENSIONS.

Balance Feeds:
As we saw previously, balance feed is nothing but how element types are used to feed balances. The input values of element type is linked to balance type via PAY_BALANCE_FEEDS_F table.

Defined Balances:
Balances by itself does not hold the accrued balance values. Because the context in which balance is going to be used may change depending on the dimension with which we query it. Hence PAY_DEFINED_BALANCES links the valid combination of balance types and dimension types. This table has defined_balance_id as the primary key which will be extensively referred in latest balances.

Balance Dimension Types:
Usually balances are associated at the assignment level. But there are five different types of dimensions based on which the balances may be used differs.

1. N type - Not Fed, Not stored
2. F type - Fed, but not stored
3. A type - stored at assignment level
4. P type - stored at person level
5. R type - Run level balances

The balances for N and F types are not stored in the database tables whereas it is stored for other types.

Latest Balance:
In R12, the latest balances, be it in assignment level or at person level are stored commonly in a table called PAY_LATEST_BALANCES. The latest balances at assignment level are stored in PAY_ASSIGNMENT_LATEST_BALANCES and linked to PAY_LATEST_BALANCES through latest_balance_id. This table also holds assignment_id and assignment_action_id also. Similarly PAY_PERSON_LATEST_BALANCES is also linked to PAY_LATEST_BALANCES

Oracle Retropay Fails With: APP-PAY-06881: Error ORA-01403: No Data Found On Table pay_element_types_f (Doc ID 1075583.1)

SYMPTOMS

Problem Statement: 

When executing a Retro Process (RetroPay Enhanced or RetroPay by Element) the following error occurs:

SQL_NO 507
TABLE_NAME pay_element_types_f
APP-PAY-06881: Error ORA-01403: no data found
has occurred in table pay_element_types_f at location 507

Cause: an oracle error has occurred. The failure was reported on table pay_element_types_f at location 507 with the error text ORA-01403: no data found

CAUSE

The issue can be caused by one of the following setup issues :

1. RetroPay is not supported for the associated Legislation.
2. An element is included in the RetroPay set, but has no RetroPay element defined on the element setup. 
3. Effective Start Date for the one of the elements in Element Set is after the Start Date parameter passed for the 'Retropay by Element' process.

SOLUTION

This issue can be encountered if RetroPay (Enhanced) or RetroPay by Element is being executed on a Legislation for which it is not Supported.  Before any further debugging is done, confirm that your Legislation supports the functionality.  Review the following note:

• Document: 1118267.1 What localizations support Retropay By Element and Retropay Enhanced?

If the Legislation supports the functionality continue with the steps to Diagnose and Resolve this issue:

Debugging:

To assist in determining the issue the process should be rerun with LOGGING turned on.  The Logging vales should be 'GMFZT'

• For assistance with logging setup and obtaining the log file it is recommended that the following note be utilized: 

NOTE:1076458.6 - Logging For Use With Payroll / PYUGEN Process: How to Use and Settings For The Pay_Action_Parameters Table

Reminder:  Make sure to turn LOGGING off when the process has completed.

Review the Log file

Log File Output Example:

Retro Element: 192
Out } pybkecrec
Out } pybkegre
RETRO ELEMENT: 192
In { pycload
In { pycfind
object not found
Out } pycfind

object NOT already present in cache: creating
In { pyccret
object type: [ET] object id: [192]
In { pycfind
object not found
Out } pycfind
Out } pyccret
In { pyclet
Oracle error occurred
Table=pay_element_types_f
sqlno=507
ORA-01403: no data found

Exiting without success

object type: [ET] object id: [192] - this equates to the Retro Element Type ID value of 192

Problem Analysis: 

Cause 2: Confirm that Retro Element is defined

Run the following SQL using the element Type ID value for the input parameter &ET_ID:

select *
from pay_element_entries_f
where element_type_id = &ET_ID;

If a record is returned, then this is not the problem, skip to Cause 2 Problem Analysis.

If no record is returned then the run the following SQL to determine which 'Regular' Element has this Retro Element associated with it, using the element Type ID value for the input parameter &ET_ID:

select prcu.creator_id,
       prcu.RETRO_COMPONENT_USAGE_ID,
       pesu.RETRO_ELEMENT_TYPE_ID
from   pay_retro_component_usages prcu
      ,pay_element_span_usages pesu
where  pesu.RETRO_COMPONENT_USAGE_ID = prcu.RETRO_COMPONENT_USAGE_ID
and    pesu.RETRO_ELEMENT_TYPE_ID = &ET_ID;

Note:  The Creator_id is the element_type_id for the 'Regular' Element

Solution: 

First check that no element_entries have been created for the Retro Element, using the element Type ID value for the input parameter &ET_ID:

Select *
from   pay_element_entries_f
where  element_type_id = &ET_ID;

If no rows are selected, proceed with replacing the Retro Element that doesn't exist in Pay_Element_Types_F with one that does.

Using the following:

1. Total Compensation
2. Basic
3. Element Description (query up Element Name)
4. Click on Recalculation tab
5. Retro Components

Cause 3: Element Effective Start Date after the Start Date parameter passed for the Retropay

Run the following SQL using the element Type ID value for the input parameter &ET_ID:

select element_type_id,
       effective_start_date,
       effective_end_date,
       element_name
from   pay_element_types_f
where element_type_id = &ET_ID;

Review the output of the SQL to see if the element(s) have effective dates that are prior to the Start Date of the Retropay.  The RetroElement MUST BE active within the range of the Start and End dates used when executing the Retro Process.

Solution: 

1. Rollback the RetroPay Process
2. Correct either the
• Start Date of the RetroPay Process
• Effectives Date associated with the Retro Element
3. Retry the RetroPay Process

If neither of these solutions address the issue then please log a Service request with Support Services.  At the time that the SR is created, please provide the following:

NOTE:1075583.1 - Oracle Retropay Fails With: APP-PAY-06881: Error ORA-01403: No Data Found On Table pay_element_types_f


*****************************************************************************

Retro-Notifications Report Enhanced (PDF) Error: ORA-01422: exact fetch returns more than requested number of rows (Doc ID 2212566.1)

When attempting to Retro-Notifications Report Enhanced (PDF), the following error occurs:


HR_6881_HRPROC_ORA_ERR
SQLERRMC ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.HR_NONRUN_ASACT", line 5289
ORA-06512: at line 2

SQL_NO 435
TABLE_NAME hr_nonrun_asact.asact
APP-PAY-06881: Error ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.HR_NONRUN_ASACT", line 5289
ORA-06512: at line 2
has occurred in table hr_nonrun_asact.asact at location 435

Cause: an oracle error has occurred. The failure was reported on table hr_nonrun_asact.asact at location 435 with the error text ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.HR_NONRUN_ASACT", line 5289
ORA-06512: at line 2

CAUSE

There are duplicate unprocessed rows in pay_retro_assignments table for one or more employees.  Only one unprocessed row can exist at any given time for a single assignment.

To confirm that you have duplicated rows, run this query:

select assignment_id, count(*)
from pay_retro_assignments
where retro_assignment_action_id is null
group by assignment_id
having count(*) > 1

For each assignment_id identified, find the related assignment number (or employee name) and delete one of the duplicate rows from the Retropay Status form.

STEPS:

1. Navigate: View > Retropay Status
2. Query for unprocessed retro assignments for the assignment identified in the query
3. Change the status of one of the lines to Deferred (it doesn't matter which one you choose)
4. Requery for the deferred line and then delete it.

Repeat the steps for all assignments identified by the query.

Retro Pay in Oracle Payroll

What is Retro?
Retro means an out-dated thing from the past which is active now.

What is Retro Pay?
Retro Pay stands for some old Payment due for the employee

How can we land in such scenario?
There are two things which can happen..

1. Incorrect Element Entry Value-Some times we realise after some time that the element entry entered and processed in the past is not correct.
2. Back Dated Salary Increments: The union agreement or the Higher Management decides to revise salary effective from a date in the past

So what shall we do now?

Now that we have corrected the old Element Entry, the next Step is how to give this amount to the employee.

What Oracle Gives me for this?
Oracle gives the RetroPay process but in four flavours leading to confusions for the unarmed.

What are those four?

Here is the list 
1. RetroPay by Run
2. RetroPay by Aggregate 
3. RetroPay by Element
4. Enhanced RetroPay

Out of these four which one we are going to see in this?
In this entry, I am going to cover Retro Pay by Element. A new element entry will be created with the difference amount.

Sample Scenario
Assume an employee gets paid 1,500 for August. 
Now payroll comes to know that his salary is actually 2,000 and not 1,500. 

The following are the steps required to be performed.

1. Define a new element which can be used to pay the Retro amount
2. Make sure that this element is Non-recurring and Multiple Entries allowed.
3. Attach this Element to the retro tab of the Salary element
4. Define the Assignment Set

An assignment set needs to be defined with the affected Employees

5. Element Set
An element set needs to be defined with the Salary element(Don't include the Retro Element here.You will get an Error while submitting the Concurrent Request) .

6. Now run the Retro Pay by Element with the following parameters

a. Effective Start Date: This is the date from which we want to compute the difference
b. Effective End Date: A date in the latest Pay Period. The retro entries get created in this period.
c. Assignment Set: The Assignment Set we created
d. Element Set: The element set we created

Submit the request.. And check from the log that the number of assignments processed is correct.

Now if we check the element entries screen, we can see the retro elements getting created.

Step 1. You will first run "Retro-Notifications Report (Enhanced) - PDF". This will read the records from PAY_PROCESS_EVENTS.
This process will insert eligible records into PAY_RETRO_ASSIGNMENTS and  PAY_RETRO_ENTRIES .

Step 2. Run process Retropay (Enhanced). This process will do the require calculations to differential payments.


Why is the Retropay process picking up Entries from pay periods that have either been skipped or
not processed?

It all depends on whether the employee was picked up for processing when Payroll Run originally
processed that pay period.

If the assignment was picked up but nothing found to process so that the assignment is marked as
'Skipped', that assignment will be picked up by RetroPay if changes are subsequently entered into
that pay period.

This is a fundamental part of RetroPay processing and the reason the Skipped actions are created.

If the employee was never processed in the original pay period (no assignment action exists for
them in the Run) then RetroPay cannot process them.

RetroPay reprocesses employees that were included in the original pay period.

RetroPay (Enhanced) replaces RetroPay by Element but retains its original characteristics.

Retropay (Enhanced) is a two-stage process:

First process:

Run the “Retro-Notification Report (Enhanced) - PDF” concurrent request. The process creates data that stores details of all assignments and entries that require retropay because they have had retrospective changes affecting them. This created data is known as Retro-Assignments and Retro-Entries. These entries can be viewed in the PDF file produced by the process and/or through the new “RetroPay Status” window, which has a self-service user interface.

Second process:

Run the Retropay (Enhanced) process. This process drives off the new data tables to only re-process those assignments with changed data and only re-process historical data back to the required effective date.

The Retropay (Enhanced) process has the following features:

- Users are provided the additional ability to setup Retropay summary elements for seeded elements. You are not currently able to do this with RetroPay by Element.

- The process reprocesses based on the earliest assignment reprocess date (as opposed to the user supplied date). Hence the process runs for as far back in time as is necessary for each individual assignment. This change enhances the performance of the process.

retro element entries in pay_element_entries_f have creator_type = RR
There are more creator_type values than just �RR� for element entries created by the Retropay process. 

The Application Utilities Lookup CREATOR_TYPE has the full list of values and includes both the code (which is stored in the creator_type field in pay_element_entries_f) and also the description. I�ve just had a quick look and the following values exist for Retropay:

EE - Retropay by Element, Source id points to Element Entry
NR - Retropay By Element, Negative Replacement
PR - Retropay By Element, Positive Replacement
R - Retropay by Action
RR - Retropay by Element, Source id points to Run Result



You can track down the orginal element by Sourec_id(element_entry_id of parent element) in pay_element_entries_f table.

Running the Processes

The Retropay (Enhanced) process uses a different mechanism for identifying the assignments to be processed. Retropay (by Aggregate) and RetroPay by Element previously identified assignments by an Assignment Set and recalculated all the payroll runs between the supplied start and end date.

This approach is inefficient because each assignment should only be recalculated for the period that they need to be. For example, if Assignment A needs to be recalculated from 16th March and Assignment B from the 16th April, currently both were processed from the 16th March.

With RetroPay (Enhanced) an assignment set is no longer used. The concept of creating a request to retropay an assignment is now introduced. The request takes the form of identifying the assignment to process and the date from which the recalculation will take place.

There are three ways to make a request for an assignment to be retrospectively paid:

1) Retro-Notification Report (Enhanced) – PDF. This process generates the requests.
2) Details can be entered manually in the new self-service (framework) form
3) API

Note:  Retropay (Enhanced) no longer uses an assignment set.  You must use either Retro-Notification Report (Enhanced), the new RetroPay Status form or the API.

Retro-Notification (Enhanced) – PDF Parameters:

Payroll (required)
Overriding Event Group
Template Name (required) – there is one template currently called Retro-Notifications Report (Enhanced)

As with the previous version (Retro-Notification Report provided several years ago), the process creates a report that displays all assignments that have had a Datetracked Event identified in the Retro Event Group you defined. Retro-Notification can be run more than once for the same time frame. Assignments are not dropped off this report until the Retropay (Enhanced) process is run.

This report has been added for the appropriate seeded payroll request groups. If you have a custom request group, you must add this new request.

Retropay Status Window

This form appears on the navigator, but you must be logged into the E-Business Suite Home page to access it. This form has been added for all seeded responsibilities. If you have custom menus or responsibilities, you must add this form.

The assignments identified by the Retro-Notification Report (Enhanced) – PDF process will be displayed. You can further enter, update and delete retropay assignment requests. The form also shows a status indicating whether the request has been processed. The retropay assignment requests will be processed when the next Retropay (Enhanced) process is run.

If you manually enter an assignment, you must also include the Retro entries/component for that assignment in this window.

Retropay entry details are linked to retropay assignment requests. This indicates which components should be processed by the Retropay (Enhanced) for a particular element entry.

Retropay (Enhanced)

The RetroPay by Element process used an Element Set to indicate which element changes should be brought forward. An Element Set is no longer required. The setup of the Retro Components now identifies the elements to be used by the retropay process.

Parameters:

Effective Date (required)
Payroll (required)

Retropay (Enhanced) is a sequenced action. This means the Effective Date must be later than your last event. For example: your current payroll period is April 16 to 29. Your previous payroll's check date was April 22. The Effective Date would be any date between April 23 and the end date of the current payroll run, which is April 29.

Retropay (Enhanced) produces multiple retroactive element entries for each element entry, which changed for each payroll period. Elements with exact names will be combined on the statement of earnings (SOE), check, deposit advice and online payslip.

The detail of the period the retro element was generated as a result of is displayed on the Entries window in the current payroll period. The element entry's Original Date Earned is populated with the pay period that the difference is from. There is a 'Retrospective' check box also. This check box will be checked for any entry created by the Retropay (Enhanced) process.

This process has been added for the appropriate seeded payroll request groups. If you have a custom request group, you must add this new request.

Employee Process In Retro Pay Enhanced Report but not in Retro Pay Process

The reason being Retropay(Enhanced) will NOT pick entries if there are future dated payroll action which are either in status 'Complete' or 'Skipped'.

Example : customer had Balance Adjustment and hence the entries were not picked up.

BUG:13965053 - RETRO ENAHANCED PROCESS DID NOT PICKUP AN EMPLOYEE HAVING BALANCE ADJUSTMENTS

It all depends on whether the employee was picked up for processing when Payroll Run originally
processed that pay period.

If the assignment was picked up but nothing found to process so that the assignment is marked as
'Skipped', that assignment will be picked up by RetroPay if changes are subsequently entered into
that pay period.

This is a fundamental part of RetroPay processing and the reason the Skipped actions are created.

If the employee was never processed in the original pay period (no assignment action exists for
them in the Run) then RetroPay cannot process them.

RetroPay reprocesses employees that were included in the original pay period

Back End Tables to check

PAY_RETRO_ASSIGNMENTS

Identifies assignment for reprocessing

PAY_RETRO_COMPONENTS

PAY_RETRO_COMPONENT_USAGES

PAY_RETRO_DEFINITIONS

PAY_RETRO_DEFN_COMPONENTS

PAY_RETRO_ENTRIES

Identifies the Entries required for re-processing.

PAY_RETRO_NOTIF_REPORTS

Populated and used in the RetroNotification Report

 "Retro-Notifications Report (Enhanced) - PDF". This will read the records from PAY_PROCESS_EVENTS.
This process will insert eligible records into PAY_RETRO_ASSIGNMENTS and PAY_RETRO_ENTRIES .

Which process do we run to initiate RetroPay processing for our backdated changes?That there can be various "Event Groups" defined(some for proration and some for Retro[event group type being retro].
All the changes pertaining to all Event Groups would have created entries in PAY_PROCESS_EVENTS.
Retro Notification report will filter the events applicable to RetroPay and will populate PAY_RETRO_ASSIGNMENTS and  PAY_RETRO_ENTRIES .

What are the processes that one needs to run?
Step a. You will first run "Retro-Notifications Report (Enhanced) - PDF". This will read the records from PAY_PROCESS_EVENTS.
This process will insert eligible records into PAY_RETRO_ASSIGNMENTS and  PAY_RETRO_ENTRIES .
Step b. Run process Retropay (Enhanced). This process will do the require calculations to differential payments.

There are duplicate unprocessed rows in pay_retro_assignments table for one or more employees.  Only one unprocessed row can exist at any given time for a single assignment.It may causeand error in the Retro Notification Report 

To identify this use the following query

select assignment_id, count(*)
from pay_retro_assignments
where retro_assignment_action_id is null
group by assignment_id
having count(*) > 1

For each assignment_id identified, find the related assignment number (or employee name) and delete one of the duplicate rows from the Retropay Status form.

STEPS:

1. Navigate: View > Retropay Status
2. Query for unprocessed retro assignments for the assignment identified in the query
3. Change the status of one of the lines to Deferred (it doesn't matter which one you choose)
4. Requery for the deferred line and then delete it.

Repeat the steps for all assignments identified by the query.

The Retro-Notifications Report (Enhanced) PDF process should now complete successfully.

User Define Table In Oracle Apps Query

Query to Fetch UDT Value

SELECT pucif.VALUE
          FROM apps.pay_user_column_instances_f pucif
               ,pay_user_tables put
               ,pay_user_rows_f purf
               ,pay_user_columns puc
          WHERE put.user_table_name = p_table_name
            AND put.business_group_id = p_business_group_id
            AND put.user_table_id = purf.user_table_id
            AND purf.row_low_range_or_name = p_row_name
            AND put.user_table_id = puc.user_table_id
            AND puc.user_column_name = p_column_name
            AND pucif.user_row_id = purf.user_row_id
            AND pucif.user_column_id = puc.user_column_id
            AND p_effective_date BETWEEN purf.effective_start_date AND purf.effective_end_date
            AND p_effective_date BETWEEN pucif.effective_start_date AND pucif.effective_end_date;
     

FUNCTION SET_GLOBAL_USER_TABLES(p_fiscal_year IN VARCHAR2)
RETURN VARCHAR2 is

l_us_user_table_id number;
l_user_column_id number;
l_rowid varchar2(200);
l_ovn_Number number;
l_user_col_inst_id number;
l_eff_st_dt date;
l_eff_end_dt date;
l_update varchar2(1);
l_ret_val varchar2(1) := 'N';
l_fiscal_start_date date;
l_fiscal_end_date date;

CURSOR CUR_TABLES IS
SELECT *
FROM PAY_USER_TABLES
WHERE USER_TABLE_NAME = 'UDT NAME'
AND BUSINESS_GROUP_ID <> 81;

CURSOR CUR_TARGET_TABLES IS
SELECT *
FROM PAY_USER_TABLES
WHERE USER_TABLE_NAME = 'UDT NAME'
AND BUSINESS_GROUP_ID <> 81;

CURSOR cur_rows(P_USER_TABLE_ID NUMBER, P_FISCAL_YEAR VARCHAR2,P_END_DATE DATE) IS
SELECT A.*,
       C.ROW_LOW_RANGE_OR_NAME,
       B.EFFECTIVE_START_DATE,
       B.EFFECTIVE_END_DATE, B.USER_ROW_ID,
       B.VALUE
FROM   PAY_USER_COLUMNS A,
       PAY_USER_COLUMN_INSTANCES_F B,
       PAY_USER_ROWS_F C
WHERE C.USER_TABLE_ID = P_USER_TABLE_ID
  AND A.USER_COLUMN_NAME = NVL(P_FISCAL_YEAR, A.USER_COLUMN_NAME)
  AND A.USER_COLUMN_ID = B.USER_COLUMN_ID
  AND P_END_DATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
  AND P_END_DATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE
  AND A.USER_TABLE_ID = C.USER_TABLE_ID
  AND C.USER_ROW_ID = B.USER_ROW_ID;

CURSOR cur_row_name(P_USER_TABLE_ID NUMBER, ROW_NAME VARCHAR2) IS
SELECT *
FROM PAY_USER_ROWS_F
WHERE USER_TABLE_ID = P_USER_TABLE_ID
  AND ROW_LOW_RANGE_OR_NAME = ROW_NAME
  AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

BEGIN
    SELECT USER_TABLE_ID
    INTO L_US_USER_TABLE_ID
    FROM PAY_USER_TABLES
    WHERE USER_TABLE_NAME = 'UDT Name'
    AND BUSINESS_GROUP_ID = 81;

  FOR i IN cur_tables LOOP
    BEGIN
    l_rowid := NULL;
    l_ovn_number := NULL;
    l_user_column_id := NULL;
    l_update := 'N';

        BEGIN
            SELECT user_column_id
            INTO l_user_column_id
            FROM PAY_USER_COLUMNS
            WHERE user_table_id = i.user_table_id
            AND user_column_name = p_fiscal_year;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
              l_update := 'Y';
        WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.LOG,'Unknown Error while getting User Column ' || substr(SQLERRM, 1, 512));
        END;

         IF(l_update = 'Y') THEN
         PAY_USER_COLUMN_API.create_user_column
          (p_validate                    => false
          ,p_business_group_id           => i.business_group_id
          ,p_user_table_id               => i.user_table_id
          ,p_user_column_name            => p_fiscal_year
          ,p_user_column_id              => l_user_column_id
          ,p_object_version_number       => l_ovn_number
          );
          END IF;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        fnd_file.put_line(fnd_file.LOG,'Unknown Error while creating User Column ' || substr(SQLERRM, 1, 512));
    END;
  END LOOP;

FOR j IN cur_rows(l_us_user_table_id, p_fiscal_year,l_fiscal_end_date) LOOP
BEGIN
FOR k IN cur_tables LOOP
BEGIN
SELECT user_column_id
INTO l_user_column_id
FROM PAY_USER_COLUMNS
WHERE user_table_id = k.user_table_id
AND user_column_name = p_fiscal_year;

   FOR l IN cur_row_name(k.user_table_id, j.row_low_range_or_name) LOOP
   BEGIN
   l_ovn_number := NULL;
   l_user_col_inst_id := NULL;
   l_eff_st_dt := null;
   l_eff_end_dt := null;
   l_update := 'N';

   BEGIN
   SELECT user_column_instance_id
   INTO l_user_col_inst_id
   FROM PAY_USER_COLUMN_INSTANCES_f
   WHERE user_row_id = l.user_row_id AND user_column_id = l_user_column_id
   AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

   FOR m IN (SELECT *
             FROM PAY_USER_COLUMN_INSTANCES_f
             WHERE user_column_instance_id  = l_user_col_inst_id
             AND  TRUNC(SYSDATE) BETWEEN effective_start_date and effective_end_date)
   LOOP
   BEGIN

   IF (nvl(m.value, '~~') != nvl(j.value, '~~')) THEN
    PAY_USER_COLUMN_INSTANCE_API.update_user_column_instance
    (
         p_validate                   => false
        ,p_effective_date             => m.effective_start_date
        ,p_value                      => j.value
        ,p_datetrack_update_mode      => 'CORRECTION'
        ,p_user_column_instance_id    => l_user_col_inst_id
        ,p_object_version_number      => m.object_version_number
        ,p_effective_start_date       => l_eff_st_dt
        ,p_effective_end_date         => l_eff_end_dt
    );
    COMMIT;
   END IF;
  EXCEPTION
     WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG, 'Error While Updating Table Values ' || substr(SQLERRM,1,512));
   END;
   END LOOP;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       l_update := 'Y';
     WHEN OTHERS THEN
     fnd_file.put_line(fnd_file.LOG,'UnKnown Error while Getting table values ' || substr(SQLERRM, 1, 512));
   END;
    IF (l_update = 'Y') THEN
   

 PAY_USER_COLUMN_INSTANCE_API.create_user_column_instance
        (p_validate                   => false
        ,p_effective_date             => j.effective_start_date
        ,p_user_row_id                => l.user_row_id
        ,p_user_column_id             => l_user_column_id
        ,p_value                      => j.value
        ,p_business_group_id          => k.business_group_id
        ,p_user_column_instance_id    => l_user_col_inst_id
        ,p_object_version_number      => l_ovn_number
        ,p_effective_start_date       => l_eff_st_dt
        ,p_effective_end_date         => l_eff_end_dt
        );
        l_ret_val := 'Y';
        COMMIT;
    END IF;
      EXCEPTION
       WHEN OTHERS THEN
     fnd_file.put_line(fnd_file.LOG,'UnKnown Error while Updating table values ' || substr(SQLERRM, 1, 512));
   END;
   END LOOP;
   END;
END LOOP;
END;
END LOOP;