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.

Personality Programs that invoked JD Edwards Work Orders logic#

TRANSACTION ENTRY TIME At transaction entry time (IPTR, IPPH etc), whenever a Work Order Number is entered, the Work Order is validated from the JD Edwards Financials data base Table F4801.

Personality GL Interface File with JD Edwards Work Order Number#

The Work Order Number for each Transaction is required to be sent to the JD Edwards Financials system. To do this, the user need to set up IDIF screen Record 50 for the Work Order field.

---

Notes#

Click to create a new notes page