[{TableOfContents }]

!!!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
    [{Image src=’WorkOrdersVal_JDE_01.jpg' width='700'}]

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|Edit:Internal.WORK ORDERS VALIDATION (JD EDWARDS)]
[{InsertPage page='Internal.WORK ORDERS VALIDATION (JD EDWARDS)' default='Click to create a new notes page'}]