Top
Top
LibraryEASYCommerce Reference ManualOrder History

Order History Overview

Find and Track Orders

There is one main location in EASYCommerce where a logged in user’s order history is shown: “Find and Track Orders”. This is a real-time query to JDE filtering on the billing address number (SHAN8)  so that only orders matching the logged in user’s billing address show. This page shows all the order headers (F4201/F42019) and displays mainly header level information. Each order has a hyperlink to direct to the “Order Details” page and view the lines on the order.

Order Details

From the “Find and Track Orders” page, the user can click on a hyperlink to be redirected to the “Order Details” page. This is a real-time query to JDE filtering on the OrderNumber (SDDOCO), OrderType (SDDCTO), and OrderCompany (SDKCOO) of the order selected on the previous page. The user can view the lines on an order from this page. This page displays everything the user should want to know about an order: header (F4201/F42019) and detail (F4211/F42119) level information.

Back Order Summary

The “Back Order Summary” page is an order history page that shows all of the user’s back-ordered lines. A line would not show on this page if the corresponding order header would not show in the user’s “Find and Track Orders” page. This is a real-time JDE filtering on the user’s billing address number (SDAN8) and where the back-ordered quantity is greater than 0 (SDSOBK >0). This page displays detail level information for orders that meet this criteria (F4211).


Find and Track Orders
Order Details
Back Order Summary

Find and Track Orders

Features

Download to CSV

All the results returned can be downloaded to a CSV. These are the full results that can be paged through, not just the initial subset that is shown on the grid’s page 1 on page load.

Filters

At the top of the page, the user has access to some textboxes to filter the results returned by the query to JDE that are displayed in the grid on the page. Some internal filters are applied and cannot be removed or seen by the user on the page, but are applied to the query against JDE.

Filter

JDE Logic

Ignore Case

Order Number

SHDOCO LIKE ‘[OrderNumberFilter]%’

No

PO #

SHVR01 LIKE ‘%[PoNumberFilter]%’

Yes

Item Number

SDLITM = ‘[ItemNumberFilter]’

Yes

Requested Date

SHDRQJ >= [FromDate] AND [ToDate] >= SHDRQJ

No

Order Status

Admin Defined. Described in another Document

No

Credit Status (Paid)

SHHOLD != ‘CA’

No

OrderType (Internal Filter)

SHDCTO IN (‘SO’)

No

Billing Address (Internal Filter)

SDAN8 = [User’sBillingAddress]

No

Sortable Columns in Grid

When the column header is clicked, it sorts the grid by the clicked on column. This is added to the ORDER BY clause of the query to JDE.

Column in Grid

JDE Column

Order Number

SHDOCO

Status

Admin Defined. Described in another Document

Order Type

SHDCTO

PO #

SHVR01

Requested Date

SHDRQJ

Created On

SHTRDJ

Actions

Each order shown in the “Find and Track Orders” page has actions available for each line. Some of these options may be unavailable depending on the details of the order, but “Order Details” will always be available since.

  • Order Details: View more information about the order.
  • Payment: If EASYPay is used, this allows the user to choose a payment for the order
  • Change: If Change Order is used, this allows the user to change the order information (F4201/F4211) in JDE after order creation.

How it Works

The data that displays in the grid on this page comes from the results of a query to JDE. This query is usually against the Order Header tables (F4201/F42019), but when the user filters by the Item Number, the query uses a join against the Order Detail tables to filter the Order Header results.

The Join between the Details and the Header is an “INNER JOIN” to select only the intersection of the result set from the Header table and the details table. This filters the selection further to only find the orders with the item number we are filtering on.

The Details subquery has a WHERE clause which filters on the item number (SDLITM) as well as the billing address (SDAN8). The billing address filter is added to speed up this join. The Details subquery is grouping on SDDOCO, SDDCTO, and SDKCOO so that it is only selecting the orders that contain the item we are filtering on, but not duplicating that order record if the item appears on that order multiple times.

Usual Query Structure

Query Structure When Filtering on ItemNumber

Simplified Query

SELECT [SelectClause]

FROM

Header Tables

WHERE [WhereClause]

ORDER BY [OrderByClause]

SELECT [SelectClause]

FROM

(Header Tables

INNER JOIN

Detail Tables)

WHERE [WhereClause]

ORDER BY [OrderByClause]

Actual Query

SELECT [SelectClause]

FROM

(SELECT  * FROM F4201

UNION SELECT  *  FROM F42019) as Header

WHERE [WhereClause]

ORDER BY [OrderByClause]

SELECT [SelectClause]

FROM (

(SELECT * FROM F4201

UNION SELECT * FROM F42019) as Header

INNER JOIN

(SELECT SDDOCO, SDDCTO, SDKCOO FROM F4211

WHERE SDLITM=[ItemNumber] AND SDAN8=[User’sBillTo]

GROUP BY SDDOCO, SDDCTO, SDKCOO

UNION

SELECT SDDOCO, SDDCTO, SDKCOO FROM F42119

WHERE SDLITM=[ItemNumber] AND SDAN8=[User’sBillTo]

GROUP BY SDDOCO, SDDCTO, SDKCOO) Detail

ON Header.SHKCOO = Detail.SDKCOO

AND Header.SHDOCO = Detail.SDDOCO

AND Header.SHDCTO = Detail.SDDCTO

)

WHERE [WhereClause]

ORDER BY [OrderByClause]


Order Details

Features

Reorder

All lines on the order can be placed into the shopping cart for the user to reorder the currently viewed order. Any items no longer available will not be added and an error message will display to the user explaining how many were added/failed.

Summary

Although all the information is displayed in the grid on the “Order Details” page, certain information like Extended Price and Weight are summed and displayed in a Summary box

Tracking Links

The tracking number is retrieved by a query to JDE. This is displayed in the grid for the line. Logic can be added, which is custom for each client’s carriers, to append this tracking number to a url and form the tracking link.

Invoices

If the order has any associated invoices, they are displayed in a grid as well. The invoice can then be generated by K-Rise and downloaded as a PDF based on queries to the JDE Customer Ledger.

Order Documents

If an order has associated documents and it can be arranged so that EASYProcess can access the files, the order documents could be displayed in a grid available for download. This is shown in base functionality as a demo of the feature that could be added. Each implementation varies in where to look for the files and needs to be configured depending on the situation.

How it Works

Order Details (Lines on the Order)

The data that displays in the grid on this page comes from the results of a query to JDE. This query is against the Order Detail tables (F4211/F42119) filtered to show only the lines associated with the order that the user requested to see (SDDOCO, SDDCTO, SDKCOO). The user arrives on this page by clicking a hyperlink from the “Find and Track Orders” page or immediately after creating an order.

Order Header

This page also displays header level information which comes from the results of a query to JDE against the Order Header tables (F4201/F42019) filtered to show only the lines associated with the order that the user requested to see (SHDOCO, SHDCTO, SHKCOO).

Order Addresses

If the order has a drop ship or a drop bill address (the user checked out with an address other than what is in JDE in the address book master), the address information is retrieved from the JDE Order Address Information table (F4006). This is the table where the address information is inserted into upon order creation with a drop bill or drop ship.

If the order did not have a drop bill or drop ship, the order’s Billing Address (SHAN8) and Shipping Address (SHSHAN) are used to retrieve information from the JDE Address Book Master (F0101).

Summary Box

Summary Label

Calculation Used

Weight

The sum of the Weight of all lines on the order. Weight is stored in F4211.SDITWT and this is the value that is summed.

Ship Weight

The sum of the Ship Weight of all lines on the order. This uses the same F42.11.SDITWT, but it first divides by the SDUORG (Quantity Ordered) to get the weight of a single item, then multiplies it back out by the SDSOQS (Quantity Shipped). This is the shippable quantity for each item and it is this value which is summed to retrieve the total ship weight for the order.

Order Total

The sum of the Extended Price of all lines on the order. This is not JDE’s extended price because backorder lines have a JDE extended price of 0. Instead, this is manually calculated using the SDUORG, Quantity Ordered, and SDUPRC, Price per unit (or SDFUP, Foreign Price per unit, if foreign currency).

Taxable Amount

The sum of the Extended Price of all taxable lines on the order (where F4211.SDTAX1 = ‘Y’).

Tax

Tax as returned from the GetTax process call for the given order. For more information see the Tax documentation.

Order/Invoice Total

“Order Total” + “Tax”

Tracking Links

Order Detail tables (F4211/F42119) has an associated Shipment Number (SDSHPN). This is used to query the Shipment Reference Numbers table (F4217) to retrieve the Tracking Number.

Query to Retrieve Tracking Number

SELECT XIREFN as TRACKINGNUMBER, XISHPN as SHIPMENTNUMBER

FROM F4217

WHERE XISHPN IN ([LIST OF SHIPMENT NUMBERS]) AND XIREFQ = 'CN'

Delivery Instructions

Base functionality retrieves the Delivery Instructions from the order header’s “Delivery Instructions Line1” and “Delivery Instructions Line 2” columns (SHDEL1, SHDEL2). Each column has a 30 character limit, so the two are concatenated to form a 60 character string which is displayed on the page.

Alternatively, if the Delivery Instructions are stored as an order media attachment, a query could be added to the Media Objects Storage table (F00165). This is not set up in the base functionality and requires additional changes to achieve this method.

Query Used

Comments

Base Functionality

SELECT

SHDEL1 + SHEDEL2 AS DELIVERYINSTRUCTION

FROM F4201

Media Attachment

SELECT * FROM F00165

WHERE SDOBCM=’GT4201A’ AND

GDTXKY = ‘[OrderNumber]|[OrderType]|[OrderCompany]’

The Generic Text Key (GDTXKY) takes a string formed by the primary key of the table, separated by “|”.

This query needs OLEDB connection string and type to work.

Carrier Name

Carrier Names are retrieved from the Address Book Master’s “Name - Alpha” column (F0101.ABALPH) where the Address Number (ABAN8) is equal to the Carrier Number from the order (SHCARS or SDCARS). Base functionality shows only the Order Header Carrier Number (F4201.SHCARS), but the Carrier Number is retrieved for the Order Details (F4211.SDCARS) as well and could be displayed in the grid if needed.

Query to Retrieve Carrier Name

SELECT ABAN8 as CARRIER, ABALPH as CARRIERNAME

FROM F0101

WHERE ABAN8 = [SHCARS] OR ABAN8 IN ([LIST OF SDCARS])

Invoices

Invoices are found by joining the Order Details tables (F4211/F42119) on the Customer Ledger (F03B11). If this query returns no results, the grid to display the invoices is hidden. If results are found, each invoice is displayed in the grid and is available for download. Each invoice is considered a record from F03B11 with a distinct Document Number (RPDOC), Document Type (RPDCT), Document Company (RPKCO), and Document Pay Item (RPSFX).

Query to Retrieve Invoices

SELECT t1.*

FROM F03B11 t1

INNER JOIN

    (SELECT DISTINCT SDDOC, SDDCT, SDKCO

    FROM F4211) t2

    ON SDDOC = RPDOC AND SDDCT = RPDCT AND SDKCO = RPKCO

UNION

SELECT t1.*

FROM F03B11 t1

INNER JOIN

    (SELECT DISTINCT SDDOC, SDDCT, SDKCO

    FROM F42119) t2

    ON SDDOC = RPDOC AND SDDCT = RPDCT AND SDKCO = RPKCO


Back Order Summary

How it Works

The data that displays in the grid on this page comes from the results of a query to JDE. This query is against the Order Detail table (F4211) filtered to show only the user’s orders and only back-ordered lines.

Features

Request Cancellation

Each back-ordered line in the grid has a checkbox where the user can select the row and click the “Request Cancellation” button. By default this sends an email to the admin notifying which lines on the order the user requests to cancel.

Download to CSV

All the results returned can be downloaded to a CSV. These are the full results that can be paged through, not just the initial subset that is shown on the grid’s page 1 on page load.

Filters

At the top of the page, the user has access to some textboxes to filter the results returned by the query to JDE that are displayed in the grid on the page. Some internal filters are applied and cannot be removed or seen by the user on the page, but are applied to the query against JDE.

Filter

JDE Logic

Ignore Case?

Order Number

SDDOCO LIKE ‘%[OrderNumberFilter]%’

No

PO #

SDVR01 LIKE ‘%[PoNumberFilter]%’

Yes

Item Number

SDLITM LIKE ‘%[ItemNumberFilter]%’

Yes

Item Description

SDDSC1 LIKE ‘%[ItemDescFilter]%’

Yes

Order Date

SDTRDJ = [OrderDateFilter]

No

ETA Date

SDPPDJ = [ETADateFilter]

No

Requested Date

SDDRQJ = [ReqDateFilter]

No

Backordered Qty (Internal Filter)

SDSOBK > ‘0’

No

Billing Address (Internal Filter)

SDAN8 = [User’sBillingAddress]

No

Sortable Columns in Grid

When the column header is clicked, it sorts the grid by the clicked on column. This is added to the ORDER BY clause of the query to JDE.

Column in Grid

JDE Column

Order Number

SHDOCO

Line Number

SDLNID

Item Number

SDLITM

Item Description

SDDSC1

PO #

SDVR01

Qty Ordered

SDUORG

Qty Backordered

SDSOBK

Unit Price

SDUPRC

Order Date

SDTRDJ

Requested Date

SDDRQJ

ETA Date

SDPPDJ


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