EXTOL/JDEdwards Sample Ruleset Assessment



Ruleset Suggestions:

·         For the Outbound 856 Rulesets, set up SQL views in the Database environment:

Ψ  CREATE VIEW VW_F470371S AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘S’;

Ψ  CREATE VIEW VW_F470371O AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘O’;

Ψ  CREATE VIEW VW_F470371T AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘T’;

Ψ  CREATE VIEW VW_F470371P AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘P’;

Ψ  CREATE VIEW VW_F470371I AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘I’;

·         For all Rulesets:

Ψ  Populate all JDE character fields with blanks and all JDE implied decimal fields with 0’s when initially creating the record, rather than doing it individually.

Ψ  For all Rulesets, delete disabled rules and variables that are not needed or are not being used.

Ψ  Utilize better organization when mapping segments/elements.

·         For Inbound 850 Rulesets, Node Grouping can be applied to the SDQ Segment.

·         Implement SQL Insert Data references after the installation of EBI 2.6

·         Continue to use environmental User Reference Variables consistently throughout Rulesets.  For example:

Ψ  EDOC number - env.var.User_Reference_1

Ψ  PO Number/Invoice Number - env.var.User_Reference_2

Ψ  PO Date/Invoice Date - env.var.User_Reference_3


Lowes_I850_4010_to_JDE version X12-004010

To prevent errors, EBI Users can populate all character fields with blanks and all implied decimal fields with 0’s when initially creating the record, rather than doing it individually.

EBI Client - Populating character fields with blanks


Suggested Mapping -  JDE character fields and JDE Implied Decimal fields populated with blanks and zeros when the record is initially created


Notes Regarding the EBI Client Inbound 850:

EBI Client: Tables Being Used for Inbound 850 Target Schema:

All SQL Actions are disabled in Lowes_I850_4010_to_JDE

·         Exception:  Rule - Select AXANS8, AXAN8 from PRODDTA.F4780 where AXEXRA = ${AXEXRA - SDQ03 value} and AXALPH = ${AXALPH - Sender ID}

o   The AXANS8 value is returned to variable JDE_Store_Temp


Values are being put into PO107_Temp but it doesn't look like this variable is being used anywhere (as conditions or otherwise)


Is the F5547PO1 Table just used to store the values of the elements below? There is node grouping and conditioning assigned to the values going into the F47012 table.



EBI Client - SYEDBT populated with the EDOC as seen below. Does EBI Client want to use EDOC or perhaps populate SYEDBT with an ISA Control Number?


Suggested Mapping - EBI Users can populate SYEDBT as seen below:

Wayfair_I850_4030_to_JDE version X12-004030

In the Inbound 850 Ruleset for Wayfair as well as other Trading Partners, testing is being done on each SDQ pair rather than using Node Grouping.

EBI Client – Current Inbound SDQ Mapping

In other words, testing is being done individually for SDQ elements. For example in the screen shot below, Rule 891 is executed if SDQ03 is not equal to blank, Rule 922 is executed if SDQ05 is not equal to blank, Rule 953 is executed if SDQ07 is not equal to blank, etc.

EBI Client – Current Inbound SDQ Mapping


Node Grouping on the SDQ can be used to make the Ruleset more efficient and less redundant by eliminating the number of rules needed.

Suggested Mapping - Example of Node Grouping

With the update to EBI 2.6 EBI Users can Insert SQL References as seen below:

Home_Depot_O810_4010_from_JDE version X12-004010

Notes Regarding the EBI Client Outbound 810:

EBI Client - Tables Being Used  in Outbound 810 Source Schema:

·         All SQL Actions are disabled in Home_Depot_O810_4010_from_JDE version X12-004010

Home_Depot_O856_4010_from_JDE 2 version X12-004010

EBI Client -  HL Counter

Suggested Mapping - Sample  HL Counter -:

Notes Regarding the EBI Client Outbound 856:

EBI Client - Tables Being Used in Outbound 856 Source Schema:

 

SQL Actions:

·         Select SYEXVAR5 from PRODDTA.F47011 where SYDOCO = ${DOCO_Input - SYSPID value}

o   When the resulting SYEXVAR5 isn't blank, IA is moved into REF01 and the SYEXVAR5 value is moved into REF02. If SYEXVAR5 is blank, URAB_output is moved into REF02 – Not sure how the URAB_Output value is being used here as I don’t see that variable getting populated until later in the Ruleset.

·         Select AXAX05, AXURAB, AXAX01, AXEXRA, AXALPH from PRODDTA.F4780 where AXANS8 = ${SHAN_Input - SZSHAN}

o   Go out with SZSHAN and return AXURAB into URAB_Output variable

URAB_Output being moved into REF02

URAB_Output is being populated later on in the Ruleset

·         SELECT ALADD1, ALADD2, ALADD3, ALADD4, ALADDZ, ALCTY1, ALADDS, ALCTR FROM PRODDTA.F0116 WHERE ALAN8 = ${ALAN8 - ALAN8 = 10} - Pulls back address information for N1 (Ship From)

·         Select SZEDOC, SZEDLN from PRODDTA.F47012 where SZDOCO = ${SYSPID_Input - Shipment ID} and SZEDLN = ${EDLN_Input - EDLN}

o   Go out with Shipment ID and EDLN and pull back EDLN and EDOC

·         Select IPACCD06DS, IPACCD02DS, IPACCD01DS from PRODDTA.F5547PO1 where IPEDOC = ${EDOC_Input} and IPBEST = ${SZVR02_Input}

o   Go out with EDOC_Output from previous SQL query and SZVR02 and pull back IPACCD06DS, IPACCD02DS, IPACCD01DS

·         Select IVCITM from PRODDTA.F4104 where IVLITM = ${LITM_Input - SZLITM} and IVAN8 = ${SYAN8_Input - SZAN8}

o   Go out with SZLITM and SZAN8 and pull back IVCITM

S Level:

In the Ruleset, there is a composite rule within the S Level with a condition If HL03 = “P” (Rule 45 in the screen shot below) and SZLNTY is not equal to “F” (Rule 46) then add 1 to SZEDLN_Temp2 if SZPAK is not equal to blank.

EBI Client – Current Mapping of S Level

Suggested Mapping - Sample of SQL Views:

Creating Views:

CREATE VIEW VW_F470371S AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘S’;

CREATE VIEW VW_F470371O AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘O’;

CREATE VIEW VW_F470371T AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘T’;

CREATE VIEW VW_F470371P AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘P’;

CREATE VIEW VW_F470371I AS SELECT * FROM PRODDTA.F470371 WHERE SZHLO3 = ‘I’;

Lowes_O856_4010_from_JDE version X12-004010

In this Ruleset, EBI Client is initially setting the variable HL_Check to “FALSE”.

EBI Client – HL Check

In the screen shot below, Rule 252 is executed if SZHL03 = “P” and “TRUE” is moved into HL_Check. Rule 254 is executed if SZHL03 = “T” and “TRUE” is moved into HL_Check. Rule 256 is executed when HL_Check = “TRUE”.

EBI Client – Current Mapping of “P” Leve

*The HL_Check variable is also declared in the Outbound 856 Ruleset for Home Depot, however it is not used. This would be deleted to keep the Ruleset clean.

In the screen shot below, if SZPAK is equal to blank (Rule 277) and SZLNTY = “F” (Rule 278) and SZHL03 = “I” (Rule 279) an HL segment group is created and ‘I” is moved into HL03 (Rule 287).

EBI Client – Current Mapping of “ I” Level

In the screen shot below, Rule 316 is executed if SZHL03 = “I”. A new Segment Group HL is created if MAN_SEG_Counter is equal to 0 (Rule 318). In Rule 327 “T” is hard-coded in HL03 and Rule 328 SZHL03 is moved into HL03. Which value should be populating HL03?

EBI Client – “I” Level HL03 Population

Wayfair_O856_4010_from_JDE version X12-004030

SQL Actions:

·         select ALADD1, ALADD2, ALADD3, ALADD4, ALADDS, ALADDZ, ALCTY1, ALCTR from PRODDTA.F0116 where ALAN8 = ${ALAN8_Input, ALAN = 10}

o   Pulls back address values

o   Values are used for N3 and N4 segments

·         select OAMLNM, OAADD3, OAADD4, OACTY1, OAADDS, OAADDZ, OACTR from PRODDTA.F4006 where OADOCO = ${F4706_DOCO_Input, DOCO = SYDOCO} and OAANTY = ${F4006_ANTY_Input, ANTY_Input = “2”}

o   Pulls back address values

o   Values are used for N3 and N4 segments

·         Select SYEXVAR5 from PRODDTA.F47011 where SYDOCO = ${DOCO_Input - SYSPID value}

o   When the resulting SYEXVAR5 isn't blank (Rule 212),” VR” is moved into REF01 and the SYEXVAR5 value is moved into REF02. If SYPNID = LB (Rule 216), “715” is moved into REF02. If SYPNID = “TE” (Rule 218), “1805” is moved into REF02.

·         There are a lot of disabled rules in this Ruleset. Also there are some variables that are declared but never used, for example REF02_BM (usage is disabled).  If these rules and variables are not needed, they should be deleted.

Lowes_I820_4010_to_JDE version X12-004010

EBI Client Inbound 820 Ruleset,  - Populating User_Reference_5 with the Interchange Control Number.

Notes Regarding the EBI Client Inbound 820:

EBI Client - Tables Being Used in Inbound 820 Target Schema:

Lowes_I852_4010_to_JDE version X12-004010

Notes Regarding the EBI Client Inbound 852:

 EBI Client- Tables Being Used in Inbound 852 Target Schema:

Lamps_Plus_I860_4010_to_JDE version X12-004010

The Inbound 860 Ruleset as well as other Rulesets has instances where segments/elements are scattered throughout the Ruleset. For example, notice in the screen shot below. There is a CUR02, BCH01 and BCH03 mixed amongst other rules:

EBI Client - LampsPlusI860 Ruleset

An example of a more organized approach to inbound 860 mapping can be seen in the screen shot below:

Suggested Mapping -  Inbound 860 Ruleset

Notes Regarding the EBI Client Inbound 860:

This Ruleset is very similar to the Inbound 850. There are a lot of disabled rules, if these rules are not needed they should be deleted.

 EBI Client Tables Being Used in Inbound 860 Target Schema:

Lamps_Plus_O855_4010_from_JDE version X12-004010

 EBI Client Tables being used in Outbound 855 Source Schema

SQL Actions

·         select AXAX05, AXURAB, AXAX01, AXEXRA, AXALPH from PRODDTA.F4780 where AXANS8 = ${SHAN_Input - SYSHAN}

o   Go out with SYSHAN and return AXAX05 and AXURAB

o   Values are used for REF segment

·         select ALADD1, ALADD2, ALADD3, ALADD4, ALADDS, ALADDZ, ALCTY1, ALCTR from PRODDTA.F0116 where ALAN8 = ${ALAN8_Input, ALAN = 2}

o   Pulls back address values

o   Values are used for N3 and N4 segments

·         select ALADD1, ALADD2, ALADD3, ALADD4, ALADDS, ALADDZ, ALCTY1, ALCTR from PRODDTA.F0116 where ALAN8 = ${ALAN8_Input, ALAN = 3}

o   Pulls back address values

o   Values are used for N3 and N4 segments

·         Select SZCITM, SZURRF from PRODDTA.F47012 where SZVR01 = ${F47027_SZVR01_Input – SZVR01} and SZVR02 = ${F47027_SZVR02_Input – SZVR02} and SZDOCO = ${F47027_SZDOCO_Input - SZDOCO}

o   Returns the Item Number (SZCITM) and the SZURRF value from table F47012

o   The SZURRF Value is used as a condition on the ACK segment.

 

Wayfair_O855_4030_from_JDE version X12-004030

Screen Shot below shows N1 logic being used in the Ruleset. Rule 51 will execute if F4006.OAANTY is equal to “2”.  Rule 65 will execute if N1_Check is not equal to “YES”.

EBI Client – N1 Logic used in Outbound 855 Mapping

SQL Actions

·         select AXAX05, AXURAB, AXAX01, AXEXRA, AXALPH from PRODDTA.F4780 where AXANS8 = ${SHAN_Input - SYSHAN}

o   Go out with SYSHAN and return AXAX05, AXURAB, AXAX01, and AXEXRA

o   AXAX05 and AXURAB Values are used for REF segment

o   AXAX01 and AXEXRA Values are used for N103 and N104 values when N1_Check = “YES”.

Rulesets Reviewed:

·         Lowes_I850_4010_to_JDE version X12-004010

·         Home_Depot_O810_4010_from_JDE version X12-004010

·         Home_Depot_O856_4010_from_JDE 2 version X12-004010

·         Lowes_O856_4010_from_JDE version X12-004010

·         Lowes_I820_4010_to_JDE version X12-004010

·         Lowes_I852_4010_to_JDE version X12-004010

·         Lamps_Plus__I860_4010_to_JDE version X12-004010

·         Lamps_Plus_O855_4010_from_JDE version X12-004010

·         Wayfair_O855_4030_from_JDE version X12-004030

·         Wayfair_O856_4030_from_JDE version X12-004030

·         Wayfair_I850_4030_to_JDE version X12-004030

·         Home_Depot_Outbound_846_LBL_Lighting

·         Lamps_Plus_Outbound_846_LBL_Lighting

·         Lamps_Plus_Outbound_846_Tech_Lighting

·         Wayfair_Outbound_846_LBL_Lighting

·         Wayfair_Outbound_846_Tech_Lighting

·         824_Dummy_Transformation version X12-004060

·         869_Dummy_Transformation version X12-004010