Why It's A Bad Idea to Update Elliott Data from External Source
Q - Do we have a query to update the order header sales amount based on the line items for unposted orders (assuming no partial posting)? I tried the one below but got no results:
UPDATE "CPORDHDR" Cset C.order_total_sale_amt =
(select Sum(L.line_itm_qty_to_ship * L.line_itm_unit_price)
from cpordlin L where C.Order_no = L.line_itm_order_no
and C.order_selection_code <> 'Z' group by L.line_itm_order_no)
A - Just as a general support policy, we do not support anyone that updates the Elliott database through external tool like this. As a matter of fact, our policy is to advise our users that if they do that, then we are not responsible for supporting them because their action can often cause Elliott database corruption.
But we did take a look of your request anyway. First, we modified your query as follows:
UPDATE "CPORDHDR" C
set C.order_total_sale_amt =
(select Sum(L.line_itm_qty_to_ship * L.line_itm_unit_price)
from cpordlin L where C.Order_no = L.line_itm_order_no and
L.LINE_ITM_SELECT_CD <> 'P'
group by L.line_itm_order_no)
WHERE C.ORDER_TYPE = 'O' AND C.ORDER_SELECTION_CODE = 'C';
If I follow along with your original script, the C.ORDER_SELECTION_COD <> ‘Z’ should be moved out of the parenthesis instead of inside. But I think the proper logic is we should only update ‘O’ type orders because we should not touch ‘I’ or ‘C’ type orders. Furthermore, we should not touch any order that has been selected, invoiced but not posted, or in the middle of editing (‘I’). So the only ‘O’ type of orders that should be updated are those orders C.ORDER_SELECTION_CODE = ‘C.’
Inside the parenthesis, we should not include any line item that has been posted and hence L.LINE_ITM_SELECT_CD <> ‘P.’
This SQL statement is not perfect because it does not consider the discount percent and the selling vs. stocking UOM conversion issue. Furthermore, it does not consider credit line item where the quantity order is negative. We can assume those issues are not applicable. In any case, we tested this SQL statement and verified it by exporting my CPORDHDR.BTR to CPORDHDR.TXT before updating. Then we exported it again to CPORDHDR.TX2. We did a comparison and found two orders in our sample database have some discrepancy where the order total sales amount before the update was “000000000+.” After the update, it beaome “ “ (e.g. blank?) See sample screen below:
We verified that these are the orders that do not have any line items. Then we used Notepad++ to look at the CPORDHDR.TX2 and found that the blank spaces are actually NULL characters. See sample screen below:
That is really bad news because Elliott's database does not like NULL characters. NULL characters are considered corrupt! So I used a SQL script that I thought would work and I ended up corrupting the Elliott database. Wow!
To solve this problem, we have to modified the script as follows:
UPDATE "CPORDHDR" C
set C.order_total_sale_amt =
(select Sum(L.line_itm_qty_to_ship * L.line_itm_unit_price)
from cpordlin L where C.Order_no = L.line_itm_order_no and
L.LINE_ITM_SELECT_CD <> 'P'
group by L.line_itm_order_no)
WHERE C.ORDER_TYPE = 'O' AND C.ORDER_SELECTION_CODE = 'C'
AND (SELECT COUNT(*) FROM CPORDLIN WHERE ORDER_NO = LINE_ITM_ORDER_NO AND
LINE_ITM_SELECT_CD <> 'P') > 0;
The last condition will exclude the orders that do not have any updated line items.
So as you can see in this exercise, performing a SQL update is complicated and can easily corrupt Elliott's database. That is the reason why we don’t support it. It is a bad idea for anyone to take this on and do it themselves. To update the Elliott database from an external source by using ODBC or ADO.NET, you should either use the CSV or ASCII import utility provided by Elliott, or Elliott Web Services. If such tool does not exist to fit your particular purpose, talk to us.
To solve this problem, we have to modified the script as follows:
UPDATE "CPORDHDR" C
set C.order_total_sale_amt =
(select Sum(L.line_itm_qty_to_ship * L.line_itm_unit_price)
from cpordlin L where C.Order_no = L.line_itm_order_no and
L.LINE_ITM_SELECT_CD <> 'P'
group by L.line_itm_order_no)
WHERE C.ORDER_TYPE = 'O' AND C.ORDER_SELECTION_CODE = 'C'
AND (SELECT COUNT(*) FROM CPORDLIN WHERE ORDER_NO = LINE_ITM_ORDER_NO AND
LINE_ITM_SELECT_CD <> 'P') > 0;
The last condition will exclude the orders that do not have any updated line items.
So as you can see in this exercise, performing a SQL update is complicated and can easily corrupt Elliott's database. That is the reason why we don’t support it. It is a bad idea for anyone to take this on and do it themselves. To update the Elliott database from an external source by using ODBC or ADO.NET, you should either use the CSV or ASCII import utility provided by Elliott, or Elliott Web Services. If such tool does not exist to fit your particular purpose, talk to us.
By the way, we do have a utility to update order total sales amount for open orders. See the following KB article:
EMK