Top
Top
LibraryEASYCommerce Reference ManualCatalogItem Import

Item Import Overview

The item import is a process to create a K-Rise copy of the items from JDE which can be referenced as users browse the site to speed up page load times and reduce the number of direct queries to JDE. This means that a copy of the item in JDE is taken and stored in the K-Rise item tables. Not all items in F4101 (Item Master) are imported, instead a subset of the table is targeted. This is determined by filters provided by the client that are stored in a configuration variable ($EP_Profile_ItemImport_Default_AdditionalWhereClause). This configuration variable is appended at the end of the existing Item Import query so it must contain the correct SQL syntax.

What Starts the Item Import?

EASYCommerce nightly runs a batch process to import the items from JDE. This can be configured to run on a schedule by the site admin from “Work with Batch Jobs”. When the process is scheduled to run, EASYProcess begins the action and the progress can be monitored from the “Work with Batch Jobs” admin panel by viewing the history.

Alternatively, the item import process could also be used to refresh single items from JDE. The admin of the site can view all the items in KRise through the “Work with Products” admin panel. This is intended for use when an item has been changed in JDE and that single item needs to be updated in the middle of the day. Instead of waiting until the next Item Import Batch Job to run, it can be started from the site.


How to Use Item Import Related Admin Pages
Item Master Column Mapping (K-Rise to JDE)
Item Import Process Flow
Item Import Configuration Variables
Item Import SQL Query

Item Master Column Mapping (K-Rise to JDE)

K-Rise Table

K-Rise Column

JDE Column

EC_Item_Master

ItemNumber

IMLITM

EC_Item_Master

ShortItemNumber

IMITM

EC_Item_Master

LongItemNumber

IMLITM

EC_Item_Master

AlternateItemNumber

IMAITM

EC_Item_Master

Description

IMDSC1

EC_Item_Master

UOMPrimary

IMUOM1

EC_Item_Master

UOMSecondary

IMUOM2

EC_Item_Master

UOMPurchasing

IMUOM3

EC_Item_Master

UOMPricing

IMUOM4

EC_Item_Master

UOMShipping

IMUOM6

EC_Item_Master

UOMWeight

IMUWUM

EC_Item_Master

UOMVolume

IMUVM1

EC_Item_Master

CatalogName

Hard Coded to the Configuration Variable

EC_Item_Branch

ItemNumber

IMLITM

EC_Item_Branch

ShortItemNumber

IMITM

EC_Item_Branch

LongItemNumber

IMLITM

EC_Item_Branch

AlternateItemNumber

IMAITM

EC_Item_Branch

CategoryCode1

IMSRP1

EC_Item_Branch

CategoryCode2

IMSRP2

EC_Item_Branch

CategoryCode3

IMSRP3

EC_Item_Branch

CategoryCode4

IMSRP4

EC_Item_Branch

CategoryCode5

IMSRP5

EC_Item_Branch

CategoryCode6

IMSRP6

EC_Item_Branch

CategoryCode7

IMSRP7

EC_Item_Branch

CategoryCode8

IMSRP8

EC_Item_Branch

CategoryCode9

IMSRP9

EC_Item_Branch

CategoryCode10

IMSRP0

EC_Item_Branch_Price

ItemNumber

IMLITM

EC_Item_Branch_Price

ShortItemNumber

IMITM

EC_Item_Branch_Price

LongItemNumber

IMLITM

EC_Item_Branch_Price

AlternateItemNumber

IMAITM

EC_Item_Branch_Price

CurrencyCode

When BPCRCD=’’ Then “USD”

Else BPCRCD

EC_Item_Branch_Price

UnitPrice

BPUPRC/10000

EC_Item_Branch_Price

BranchPlant

IBMCU


Item Import Process Flow

The Item Import is EASYProcess Process 287. The Item Import starts when this Process is called either by a batch job or a button.

When the item import first starts, it collects all the variables to be used for its run. This information can answer questions like “is the item import running for all items or just a single item refresh?” or “is the item import running to refresh all the items or just the ones that have changed in JDE recently?”. These sort of variables are defined in the Configuration Variables in EASYProcess for the environment.

The Item Import then calls EASYProcess Process 574. This is responsible for retrieving the list of changed items. If the Item Import is running a full import, PRC574 tells PRC287 that the query to JDE should not be filtered by this criteria. If a Net Change Import is running, PRC574 then gets the list of items that have changed. It retrieves this list by looking in the date updated column in either the Item Master (F4101) or the Item Branch File (F4102). Whether it looks in one, or both of these tables for their corresponding date updated column to check for a change is determined by a configuration variable.

It is base functionality to run a full Item Import once, then each subsequent Item Import run a Net Change Import. This is because each Net Change Import looks for any changes since the last Import was run. For this reason, the first import must be a full one. Also, if for any reason a full import is needed, a configuration variable can be set to force a full Item Import, but during this run, the configuration variable is updated so that the next Item Import will be a Net Change Import.

At this point, PRC287 looks at PRC574’s output and determines if the output should be used as an additional filter for the query to JDE. It then uses that filter and an additional where clause from the configuration variables, if it is not blank, and builds the query it will use to for the Item Import. So far, this is just stored in an evaluate service. The query is written, but has not been run against the JDE database.

Next, PRC287 runs the query against JDE, but places it in a subquery and performs a COUNT(*) on it so that an estimate of records returned can be recorded. This value is recorded in the Item Import Batch Job’s history.

Before beginning, if a full Item Import is about to begin, all the records that could be imported are updated with an Imported=”No” flag. This is done for all records in all three tables updated by the import: EC_Item_Master, EC_Item_Branch, EC_Item_Branch_Price. This is done because it is risky to delete the records and assume the Item Import is successful. Instead, we mark all the “old” records as Imported=”No”. Then, as an item is imported and records in these three tables are updated, the flag is changed to imported=”Yes”. At the end, all of the records still with a “No” flag are deleted. The exception to this is if the number of records to be deleted is too great. For example, if the Item Import ran for a few minutes, but then the connection to JDE was lost, that might result in the Item Import ending. At the end, it might try to delete thousands of item records. This protects against that. Also, since all the “old” records were never deleted them to begin with, the K-Rise Item tables are still intact with records.

Now, the query to begin the Item Import is run. This is a ReadQuery. These query JDE, but only return one result at a time. This way, the process can retrieve the one result, perform an action on it (in our case, update our Items tables with it), then loop back around and when ready ask JDE for the next result. This is done until there are no more records returned by the query.

Immediately after running this query, we check if the query returned an exception. This would happen if the connection to JDE were down or the query was in an incorrect syntax. If an exception were returned, the Item Import would stop here. The connection to the JDE database would be closed and the status would be updated in the Item Import Batch Job’s history.

If there was no exception returned by the query to JDE, we next check if we have reached the end of our returned results. Since we are requesting one record at a time from JDE, in a loop, eventually we will reach the end of the results. At this point, the connection to the JDE database would be closed and the status would be updated in the Item Import Batch Job’s history.

If the query had not reached the end of the results (or end of file), three evaluate services would run. These would define the association between the JDE columns and the K-Rise columns. For instance, if only three category codes are used in JDE, but they were not IMSRP1, IMSRP2, IMSRP3, the mapping could be changed so that the ones that are used map to the K-Rise Category Codes 1, 2, 3 anyway, just for ease.

This data is then fed into PRC357 which takes it and either updates the existing records in the K-Rise tables, or creates new ones if they do not exist. It does this for all three of the K-Rise tables used for the Item Import. It is during this process the configuration variable is used to ask if the Item Import uses JDE to populate the Category Codes. If so, the mapping from JDE to the K-Rise tables that was defined back in PRC287’s evaluates is used. If not, the Category Codes are not included in the Upsert service. This configuration variable might be used if the EASYCommerce site were configured to maintain the Category Codes manually in the K-Rise portal. This way an Item Import does not accidentally override these values. Next, PRC357 saves the price. However, if the JDE to K-Rise mapping in PRC287’s evaluate was left blank or after that mapping the price field is blank because a price record did not exist in JDE, then the K-Rise record is not upserted.

After PRC357 is finished, PRC287 records the output of it in the Item Import Batch Job history. At this point, if more records have been processed than the original estimate, the Item Import ends, the connection to the JDE database would be closed and the status would be updated in the Item Import Batch Job’s history.

Now PRC287 begins the loop and asks JDE for the next record returned by the query and does this same action again. This continues until either an error occurs or all the records are processed. As some finishing actions, if the Item Import was not a Net Change Import, the records still set to Imported=”No” would be deleted and the Solr catalog would be reindexed.


Item Import Configuration Variables

Configuration Variable

Sample Value

Comments

$EP_Profile_ItemImport_Default_DownloadCatCodeFromJDE

True/False

If this is set to “True”, a mapping can be defined from JDE columns to the K-Rise Category Codes columns. If this is set to “False”, the mapping will not be used an value will be inserted into the K-Rise Category Codes columns during the Item Import.

$EP_Profile_ItemImport_Default_SelectColumnForChanges

IMITM,IBITM

Used in combination with the next two configuration variables. This feature runs a query to find the “changed” items. This configuration variable is used in the select clause of that query. The values here are comma separated and are expected to return short item numbers.

$EP_Profile_ItemImport_Default_CheckColumnForChanges

IMUPMJ, IBUPMJ

In the feature to find the “changed” items, these are the columns that are compared against the date the last item import was run to find the items that have since been changed. The values here are expected to be comma separated. These are expected to be date updated columns.

$EP_Profile_ItemImport_Default_CheckTableForChanges

F4101,F4102

In the feature to find the “changed items, these are the tables that are queried to look for changed items. The values here are expected to be comma separated table names.

$EP_Profile_ItemImport_Default_AdditionalWhereClause

IMLITM!=’12255’

The additional where clause is used as a filter that requires an AND before it.

$EP_Profile_ItemImport_Default_CatalogName

Default

As the item import runs, it retrieves this value and applies it to all items imported.

$EP_Profile_ItemImport_Default_RunMode

ImportAll/NetChange

In the configuration variables, there are only two options: Import All or Net Change. If the Import All option is selected, the Item Import will run a full item import, but it will change this value so the next time it runs, it will be a Net Change. Net Change only updates items that have changed since the last Item Import run.

$EP_Profile_ItemImport_Default_ImportType

Item

PRC287 could be configured to also import addresses. This specifies that we are importing items.


Item Import SQL Query

Base EASYCommerce Item Import Query

SELECT DISTINCT

t1.*, t2.*, t3.BPCRCD,

t3.BPUPRC/10000 as UnitPrice,  

CC1.DRDL01+CC1.DRDL02 as CatCode1,

CC2.DRDL01+CC2.DRDL02 as CatCode2,

CC3.DRDL01+CC3.DRDL02 as CatCode3,

CC4.DRDL01+CC4.DRDL02 as CatCode4,

CC5.DRDL01+CC5.DRDL02 as CatCode5,

CC6.DRDL01+CC6.DRDL02 as CatCode6,

CC7.DRDL01+CC7.DRDL02 as CatCode7,

CC8.DRDL01+CC8.DRDL02 as CatCode8,

CC9.DRDL01+CC9.DRDL02 as CatCode9,

CC10.DRDL01+CC10.DRDL02 as CatCode10

FROM F4101 t1

INNER  JOIN F4102 t2 on t1.IMITM=t2.IBITM

LEFT JOIN F4106 t3 on t1.IMITM=t3.BPITM and t2.IBMCU = t3.BPMCU and t3.BPAN8 = 0 and t1.IMUOM1 = t3.BPUOM  and BPEXDJ >= [CurrentJDEDate]

LEFT JOIN F0005 CC1 on t1.IMSRP1=ltrim(CC1.DRKY) and CC1.DRSY='41' and CC1.DRRT='S1'

LEFT JOIN F0005 CC2 on t1.IMSRP2=ltrim(CC2.DRKY) and CC2.DRSY='41' and CC2.DRRT='S2'

LEFT JOIN F0005 CC3 on t1.IMSRP3=ltrim(CC3.DRKY) and CC3.DRSY='41' and CC3.DRRT='S3'

LEFT JOIN F0005 CC4 on t1.IMSRP4=ltrim(CC4.DRKY) and CC4.DRSY='41' and CC4.DRRT='S4'

LEFT JOIN F0005 CC5 on t1.IMSRP5=ltrim(CC5.DRKY) and CC5.DRSY='41' and CC5.DRRT='S5'

LEFT JOIN F0005 CC6 on t1.IMSRP6=ltrim(CC6.DRKY) and CC6.DRSY='41' and CC6.DRRT='06'

LEFT JOIN F0005 CC7 on t1.IMSRP7=ltrim(CC7.DRKY) and CC7.DRSY='41' and CC7.DRRT='07'

LEFT JOIN F0005 CC8 on t1.IMSRP8=ltrim(CC8.DRKY) and CC8.DRSY='41' and CC8.DRRT='08'

LEFT JOIN F0005 CC9 on t1.IMSRP9=ltrim(CC9.DRKY) and CC9.DRSY='41' and CC9.DRRT='09'

LEFT JOIN F0005 CC10 on t1.IMSRP0=ltrim(CC10.DRKY) and CC10.DRSY='41' and CC10.DRRT='10'

Where IMITM != 0 AND [AdditionalWhereClause]

Order By t1.IMITM


Powered by EASYProcess (© 2019 K-Rise Systems, Inc).