Table of Contents
- WORK ORDERS VALIDATION (JD EDWARDS)
- Connection to JD Edwards Financials Data Base
- Test JD Edwards Financial Data Base Link
- Potential Errors to avoid
- JD Edwards Financials Data Base Work Order Table – F4801
- Personality GL Company Set up – IDGC Screen
- How Personality validate Work Order in JD Edwards Database
- Personality SELECT Statement to JD Edwards Database
- Personality Programs that invoked JD Edwards Work Orders logic
- Personality GL Interface File with JD Edwards Work Order Number
- Notes
WORK ORDERS VALIDATION (JD EDWARDS)#
The system provides a Work Order Validation from Personality directly to the JD Edwards Financials data base. The JD Edwards Financials Database contains an Oracle Table F4801 that is used for editing from Personality to JD Edwards. The JD Edwards Work Order Validation will directly validate the Work Order Number from the JD Edwards Financials Data Base at the time the transactions are being entered to reduce errors.
Personality invokes the JD Edwards Work Order Validation when the GL Company from IDGC screen is marked with a ‘Work Order Validation’ of ‘JD Edwards Financials’.
The One Year previous rule is a rule that looks back 365 days prior to the leave line being evaluated to check the amount available for the employee to take in the current period. The accrual end date is the end of the pay period being evaluated and the accrual start date is 365 days prior to the end date. The system will look at all leave lines taken in the 365 day period and check to see what the hours are compared to the accrual defined in the Service levels tab if there is time available the employee will be able to take the time otherwise it will go to LWOP.
Connection to JD Edwards Financials Data Base#
A one time set up for the Database Link MUST be established by the DBA at the client site to the JD Edwards Financials data base from the current P2K database. If this Database Link is already set up for the GL Interface, then there is no need to repeat this set up.In SQL+, the DBA will connect to the P2K database and then create the database link from the current P2K data base to the JD Edwards Financials data base as follows:
SQL> drop public database link jde_financials;
SQL> create public database link jde_financials connect to USERNAME identified by USERPASSWORD using ‘DBALIAS’;
- USERNAME - is a database level user in the JD Edwards Financial system with proper permission, e.g. gluser
- USERPASSWORD - is the password for the above username, e.g. p2kpass
- DBALIAS - is the database alias for the JD Edwards financial system
- - the JD Edwards financials database alias must be in the TNSNAMES.ora file in the database’s ORACLE_HOME\NETWORK\ADMIN directory
For example: In SQL+, the DBA will first connect to P2K database, then use the following command: > create public database link jde_financials connect to gluser identified by p2kpass using 'JDEFin' where ‘JDEFin’ is the jde_financials data base that contains Oracle Tables F4801
Test JD Edwards Financial Data Base Link#
After the Database Link has been established, the DBA should test to ensure the JD Edwards Financials Tables are available for P2K as follows: In SQL+, connect to P2K database, check the following JD Edwards Financials Tables:SQL>SELECT count(*) FROM F4801@jde_financials;
- this ‘SELECT’ statement should return the number of records in this F4801 Table.
Potential Errors to avoid#
1. On IDGC screen, the ‘Work Order Validation’ must be ‘JD Edwards Financials’ for the GL Company.2. The user may encounter the following error: ‘ORA-00942 – table or view does not exist’
- this error occurs when the Database link is not established properly or the Table does not exist in the JD Edwards Financials data base, please check database link and the JD Edwards Financials database Tables to ensure all Tables exist
- the actual name of the JD Edwards Financials Table may need to be referenced by the username, e.g. gluser.F4801, if this is the case, the DBA need to create a public synonym in the JD Edwards Financials Table for the Table.
JD Edwards Financials Data Base Work Order Table – F4801#
From F4801 Table, the following Column Names are used for validation: WADOCO number - Work Order
WASRST char(2) - Work Order Status
Personality GL Company Set up – IDGC Screen#
The Work Order Validation must be ‘JD Edwards Validation’ for the G/L Company.
How Personality validate Work Order in JD Edwards Database#
WADCTO | WADOCO | WALNID | WAPTWO | WAPARS | WATYPS | WADL01 | WASRST |
WO | 130 | 0 | 0 | 00000128 | R | Rebels Ticket Operations | 60 |
WO | 131 | 0 | 0 | 00000128 | R | Lounge Operations Rebels | 60 |
WO | 132 | 0 | 0 | 00000128 | R | Field Operations set up/down | 60 |
WO | 133 | 0 | 0 | 00000128 | R | per service pac20 | 60 |
WO | 134 | 0 | 0 | 00000133 | R | repair of waterline | 60 |
WO | 135 | 0 | 0 | 00000133 | R | repair concrete sidewalk panel | 60 |
WO | 129 | 0 | 0 | 00000128 | R | Concession Rebels Ftbl | 60 |
WO | 119 | 0 | 0 | 00000115 | R | Repairs to Sidewalk | 60 |
WO | 120 | 0 | 0 | 00000120 | R | Parks TestWO – Turf Mowing | 60 |
WO | 121 | 0 | 0 | 00000121 | R | Parks TestWO – turf trimming | 60 |
WO | 122 | 0 | 0 | 00000107 | R | Roadside | 60 |
WO | 123 | 0 | 0 | 00000123 | R | 5,000K LD PM | 90 |
WO | 124 | 0 | 0 | 00000124 | R | 10,000K LD PM | 80 |
WO | 125 | 0 | 0 | 00000125 | R | CHANGE ZAMBONI BLADE | 60 |
WO | 126 | 0 | 0 | 00000126 | R | 5,000K LD PM | 80 |
WO | 127 | 0 | 0 | 00000127 | R | Rebels Football | 90 |
WO | 128 | 0 | 0 | 00000128 | R | Rebels Football | 60 |
WO | 118 | 0 | 0 | 00000115 | R | Replacement of Traffic Control | 99 |
WO | 108 | 0 | 0 | 00000108 | R | Roadside#2 | 60 |
WO | 109 | 0 | 0 | 00000109 | R | Pothole Patching | 60 |
WO | 110 | 0 | 0 | 00000110 | R | Repair Street Light - Glanford | 60 |
WO | 111 | 0 | 0 | 00000111 | R | Budget Testing | 60 |
WO | 112 | 0 | 0 | 00000112 | R | Third Party Work | 60 |
WO | 113 | 0 | 0 | 00000112 | R | Repair of Waterline | 99 |
WO | 114 | 0 | 0 | 00000112 | R | Repair of Concrete Sidewalk | 99 |
In order to validate between the 2 systems, P2K uses an Oracle SELECT statement to read the JD Edwards Financial F4801 table and do an exact match to the Work Order Number.
The Work Order Number must be Active in order to be valid. Active Work Order Numbers are with WASRST = ‘60’.
Personality SELECT Statement to JD Edwards Database#
The following SELECT statement is used to retrieve Work Order Number from the JD Edwards Financials database:SELECT *
FROM F4801@jde_financials
WHERE WADOCO = 130
AND WASRST IN (60);
The above SELECT statement validate the Work Order Number: 130.
The user can replace the Work Order Number: 130 with other Work Order Number to test, e.g. 120.