Sean Hoppe Consulting Group

Below are examples of JDE SQL statements that can be used to query PY and PD environments. These statements are ideal when dealing with inbound/outbound EDI (x12/EDIFACT) data.

Some SQL statements are for updating records to reprocess outbound EDI data.

OUTBOUND 810

Here we are searching for all in the PY Invoice header table based on the EDOC.

Select * from “CRPDTA”.“F47046” where SYEDOC = ‘1010065’;

Here we are searching for the Customer Item number in the PD Invoice detail table based on EDOC.

Select SZCITM from “PRODDTA”.“F47047” where SZEDOC = '1010065’;

This statement performs an update to unmark a single record in the PY Invoice header table based on the EDOC. This will allow the Invoice to be processed again.

Update “CRPDTA”.“F47046” set SYEDSP = ’ ’ where SYEDOC = '1010065’;

Here we are searching for the Carrier Code in the PD Invoice detail table based on the EDOC.

SELECT SZCARS FROM “PRODDTA”.“F47047” where SZEDOC = 1019880;

INBOUND 850

Here we are searching for all in the PY Purchase Order header table based on the PO Number.

Select * from “CRPDTA”.“F47011” where SYVR01 = '6593486’;

Here we are searching for all in the PY Purchase Order detail table based on the Date Updated in Julian

and ordered by the EDOC.

Select * from “CRPDTA”.“F47012” where SYUPMJ = 114147 ORDER BY SYEDOC;

Here we are searching for all in the PD Purchase Order header table based on the Transaction Set.

Select * from “PRODDTA”.“F47011” where SYEDST= '850’;

Here we are looking for all the records in the PD Address table based on the EDOC and Address Number

SELECT * FROM “PRODDTA”.“F4706” WHERE ZAEDOC = '1010065’ AND ZAANTY = ‘2’

OUTBOUND 856

Here we are searching for all the unprocessed records in the PD ASN header table.

SELECT * FROM “PRODDTA”.“F47036” where SYEDSP = ’ ’;

Here we are searching for a specific Trading Partner ID (Spring) records that are processed in the PD header table.

Select * from “PRODDTA”.”F47036” where SYPNID = 'SPRINT’ and SYEDSP = 'Y’;

This statement performs an update to unmark a single record in the PD Advanced Shipping Notice header table based on the EDOC. This will allow the ASN to be processed again.

Update “PRODDTA”.“F47036” set SYEDSP = ’ ’ where SYEDOC = '1010065’;


Here we are selecting all the unprocessed PD Advanced Shipping Notice header records that the Trading Partner ID is not equal to 'ACME’ and 'KMART’

Select * from “PRODDTA”.“F47036” where SYPNID != 'ACME’ and SYPNID !='KMART’ and SYEDSP = ’ ’;

OUTBOUND 855

Here we are searching for all the files in the PY Purchase Order Acknowledgement header table with the matching Order Number.

Select * from “CRPDTA”.“F47026” where SYDOCO = '5110144’;

Here we are searching for the Amount-Price per Unit in the PY Purchase Order Acknowledgement detail table based on the EDOC.

Select SZUPRC from “CRPDTA”.“F47027” where SZEDOC = ‘104555’;

This statement performs an update to unmark a single record in the PD Purchase Order  Acknowledgement header table based on the EDOC. This will allow the POA to be processed again.

Update “PRODDTA”.“F47026” set SYEDSP = ’ ’ where SYEDOC = '1010065’;

More information on JDE Best Practices and How-To’s