The interface table that contains data for sublines from non-Oracle sources is the PSP_PAYROLL_INTERFACE table. Table 1 describes the various fields in this table and their data types.
NAME | NULL? | DATA TYPE |
---|---|---|
PAYROLL_INTERFACE_ID | NOT NULL | NUMBER(10) |
PAYROLL_ID | NOT NULL | NUMBER(9) |
PAYROLL_PERIOD_ID | NOT NULL | NUMBER(9) |
PERSON_ID | NOT NULL | NUMBER(9) |
ASSIGNMENT_ID | NOT NULL | NUMBER(9) |
ELEMENT_TYPE_ID | NOT NULL | NUMBER(9) |
PAY_AMOUNT | NOT NULL | NUMBER |
EARNED_DATE | DATE | |
CHECK_DATE | DATE | |
EFFECTIVE_DATE | NOT NULL | DATE |
PAYROLL_SOURCE_CODE | NOT NULL | VARCHAR2(30) |
FTE | NUMBER | |
REASON_CODE | VARCHAR2(30) | |
SUB_LINE_START_DATE | NOT NULL | DATE |
SUB_LINE_END_DATE | NOT NULL | DATE |
DAILY_RATE | NOT NULL | NUMBER |
SALARY_USED | NOT NULL | NUMBER |
DR_CR_FLAG | NOT NULL | VARCHAR2(1) |
STATUS_CODE | NOT NULL | VARCHAR2(1) |
BATCH_NAME | NOT NULL | VARCHAR2(30) |
ERROR_CODE | VARCHAR2(30) | |
LAST_UPDATE_DATE | NOT NULL | DATE |
LAST_UPDATED_BY | NOT NULL | NUMBER(15) |
LAST_UPDATE_LOGIN | NOT NULL | NUMBER(15) |
CREATED_BY | NOT NULL | NUMBER(15) |
CREATION_DATE | NOT NULL | DATE |
GL_POSTING_OVERRIDE_DATE | DATE | |
GMS_POSTING_OVERRIDE_DATE | NOT CURRENTLY USED | |
ATTRIBUTE1 through ATTRIBUTE15 |
This section describes the validation checks that Labor Distribution performs when it validates and imports data from the interface tables. When you write programs to load the interface table, you must ensure that the data you enter must comply with the rules. If there is invalid data in the interface table, then Labor Distribution detects the errors and enables you to correct the errors using the Non-Oracle Sub-lines Maintenance window.
Table 2 describes the validation checks that the application performs before it imports data.
Validation Name | Description |
1. BATCH_NAME | The application imports sublines from non-Oracle sources into Labor Distribution based on the batches in which you loaded the sublines into the interface table. Ensure that the batch names in the interface table are unique. You must not reuse the batch names even after you load the batches into Labor Distribution and remove them from the interface table. |
2. PAYROLL_INTERFACE_ID | The PAYROLL_INTERFACE_ID field identifies records within any batch. The loader program must use a sequence that you created during the installation process of Labor Distribution. The sequence you must use for this purpose is PSP_PAYROLL_INTERFACE_S. |
3. EFFECTIVE_DATE | You can use the EFFECTIVE_DATE field when you post a transaction to Oracle Grants Accounting or Oracle Public Sector General Ledger. You can use the date field to check the validity of number of other fields. |
4. PERSON_ID | The PERSON_ID field refers to the person for whom the subline is created. The PERSON_ID must be a valid ID in Oracle Human Resource Management Systems for an active employee for the effective date specified in the record. The person is defined in the PER_PEOPLE_F table. |
5. ASSIGNMENT_ID | The ASSIGNMENT_ID field must be a valid assignment for the PERSON_ID specified in the record. The field must also be defined in Human Resource Management Systems. The assignment is defined in the PER_ASSIGNMENTS_F table. |
6. PAYROLL_ID | The PAYROLL_ID field must refer to a valid payroll identifier in Human Resource Management Systems. The payroll name referred to by this field must also be a valid payroll for the ASSIGNMENT_ID and EFFECTIVE_DATE specified by the record. The payroll is defined in the PAY_PAYROLLS_F table. |
7. PAYROLL_PERIOD_ID | The PAYROLL_PERIOD_ID field must refer to a valid payroll period identifier in Human Resource Management Systems. The payroll period must be linked to the PAYROLL_ID on the effective date selected. The payroll period is defined in the PER_TIME_PERIODS table. |
8. ELEMENT_TYPE_ID | The ELEMENT_TYPE_ID field must refer to a valid element selected in the Elements Imported window in Labor Distribution for the dates of the payroll period. The elements selected in the setup window are valid element types in Human Resource Management Systems. The element type is defined in the PAY_ELEMENT_TYPES_F table. |
9. PAY_AMOUNT | PAY_AMOUNT is the amount that is to be paid to an employee over the subline date range specified. Pay amount = daily rate multiplied by number of business days between subline Start and End dates |
10. EARNED_DATE | EARNED_DATE is the date that pay amount is earned. |
11. CHECK_DATE | CHECK_DATE is the date that the check is made. |
12. PAYROLL_SOURCE_CODE | The PAYROLL_SOURCE_CODE field must refer to a valid non-Oracle source as specified in the Payroll Sources window in Labor Distribution. The payroll batch source is defined in the PSP_PAYROLL_SOURCES table. |
13. DR_CR_FLAG | The DR_CR_FLAG field must contain either a D for debit amount or C for credit amount. |
14. STATUS_CODE | All records loaded into the interface table by an external loader program must contain a status code of N for New. |
15. ERROR_CODE | The ERROR_CODE field must not contain any value. This is filled by Labor Distribution when validating and importing records. |
16. SUB_LINE_START_DATE | The SUB_LINE_START_DATE field must fall within the valid date range for the specified payroll period in the PAYROLL_PERIOD_ID field. The date cannot fall before the assignment start date, and it must be before the subline end date. |
17. SUB_LINE_END_DATE | The SUB_LINE_END_DATE field must fall within the valid date range for the specified payroll period in the PAYROLL_PERIOD_ID field. The date cannot be after the assignment end date, and it must be greater than the SUB_LINE_START_DATE. |
18. GL_POSTING_OVERRIDE_DATE | General Ledger override date for posting. If defined, this date overrides the effective date for posting to General Ledger. |
19. GMS_POSTING_OVERRIDE_DATE | Not currently used |
20. Invalid records | No employee can be paid more than once for the same assignment, element, payroll period, and payroll source for overlapping subline date ranges. This rule ensures that there are no two records with the same ASSIGNMENT_ID, ELEMENT_TYPE_ID, PAYROLL_PERDIOD_ID, and PAYROLL_SOURCE_CODE for subline start and end dates that overlap each other. |
This rule is restricted to records in the same batch. | |
Table 3 shows an example of two records in a batch that would result in an error when importing the two records because of overlapping date ranges. | |
21. DAILY_RATE | The external loader program must ensure that the PAY_AMOUNT field equals the product of DAILY_RATE and the number of working days, excluding Saturday and Sunday in every week, between the subline start and subline end dates for each subline. |
Note: Labor Distribution does not perform any validation on the DAILY_RATE field. Ensure that you enforce this rule. |
|
22. BUSINESS_GROUP_ID | Business group identifier. |
23. LEDGER_ID | Ledger identifier |
24. LAST_UPDATE_DATE | Standard Who Column |
25. LAST_UPDATED_BY | Standard Who Column |
26. LAST_UPDATE_LOGIN | Standard Who Column |
27. CREATED_BY | Standard Who Column |
28. CREATION_DATE | Standard Who Column |
Table 3 shows an example of invalid records in a batch with overlapping date ranges.
Assgn_ID | Elmnt_ID | Period_ID | Pay_ID | Srce_cde | Start_Date | End_Date | Pay_amnt |
---|---|---|---|---|---|---|---|
10 | 201 | 220 | 22 | NON_1 | 01-jan-97 | 10-jan-97 | 100 |
10 | 201 | 220 | 22 | NON_1 | 20-dec-96 | 05-jan-97 | 200 |