WMS - Pack Shipment - Billing - Update Macola - Data Type Error

End result - I rebuilt the indexes on oeshptcl and then could not replicate the error.
Hopefully this may help someone else running into a similar issue.

Correction to original post (NOTES) - it was the second update statement.

I started working backwards replicating the data into temp tables and removing as much unnecessary data as possible. When I did this for all but oeshptcl the error was consistently reproducible. When moving oeshptcl over the error went away, yet I was using the same data.

-----------------------------------

IF OBJECT_ID('tempdb..#OrderList') IS NOT NULL
DROP TABLE #orderlist;

/* OEPCKHDR */
IF OBJECT_ID('tempdb..#tmpOEPCKHDR') IS NOT NULL
DROP TABLE #tmpOEPCKHDR;

CREATE TABLE #tmpOEPCKHDR (ord_type char(1), ord_no char(8), cus_no char(20), cus_name char(40), oe_po_no char(25), ship_no char(17), ship_name char(40), mark_no char(17), mark_name char(40), ord_comp char(1), loc char(3), release_type char(1))

INSERT INTO #tmpOEPCKHDR (ord_type, ord_no, cus_no, cus_name, oe_po_no, ship_no, ship_name, mark_no, mark_name, ord_comp, loc, release_type)
/* step 1 - replicate all data */
--SELECT ord_type, ord_no, cus_no, cus_name, oe_po_no, ship_no, ship_name, mark_no, mark_name, ord_comp, loc, release_type FROM OEPCKHDR where ord_comp = 'y' and loc = '1'
/* step 2 - remove unnecessary data */
SELECT '', ord_no, '', '', '', '', '', '', '', ord_comp, loc, '' FROM OEPCKHDR where ord_comp = 'y' and loc = '1'
/* step 3 - remove database from picture */
--SELECT '', ' 6', '', '', '', '', '', '', '', 'y','1 ', ''
--UNION
--SELECT '', ' 10507', '', '', '', '', '', '', '', 'y','1 ', ''
--UNION
--SELECT '', ' 18575', '', '', '', '', '', '', '', 'y','1 ', ''


/* OESHPTTL */
IF OBJECT_ID('tempdb..#tmpOESHPTTL') IS NOT NULL
DROP TABLE #tmpOESHPTTL;

CREATE TABLE #tmpOESHPTTL (pallet_no char(10) NOT NULL, attached_bol char(20))

INSERT INTO #tmpOESHPTTL (pallet_no, attached_bol)
/* step 1 - replicate all data */
/* step 2 - remove unncessary data */
SELECT pallet_no, attached_bol FROM oeshpttl_vw
/* step 3 - remove database from picture */


/* OESHPTTL */
IF OBJECT_ID('tempdb..#tmpOESHPTCL') IS NOT NULL
DROP TABLE #tmpOESHPTCL;
/* step 1 - replicate all data */
--CREATE TABLE #tmpOESHPTCL ([carton_no] [char](10) NULL, [ord_type] [char](1) NULL, [ord_no] [char](8) NOT NULL, [cus_no] [char](20) NULL, [ship_to] [char](20) NULL, [cd_qual] [char](2) NULL,
-- [ship_code] [char](30) NULL, [ucc_128_lbl_prtd] [char](1) NULL, [sec_lbl_prtd] [char](1) NULL, [cart_posted] [char](1) NULL, [filler02] [char](10) NULL,
-- [package_code] [char](5) NULL, [atchd_type] [char](1) NULL, [atchd_code] [char](20) NULL, [loc] [char](3) NULL, [ord_carton_no] [numeric](6, 0) NULL,
-- [manifest_update] [char](1) NULL, [filler03] [char](19) NULL, [carton_weight] [decimal](11, 3) NULL, [carton_freight] [decimal](15, 2) NULL, [ground_track_no] [char](40) NULL,
-- [filler01] [char](37) NULL, [user_name] [char](20) NULL, [dateTime_Created] [datetime] NULL, [id] [numeric](9, 0) IDENTITY(1,1) NOT NULL)

--INSERT INTO #tmpOESHPTCL ([carton_no],[ord_type],[ord_no],[cus_no],[ship_to],[cd_qual],[ship_code],[ucc_128_lbl_prtd],[sec_lbl_prtd],[cart_posted],[filler02],[package_code],[atchd_type]
-- ,[atchd_code],[loc],[ord_carton_no],[manifest_update],[filler03],[carton_weight],[carton_freight],[ground_track_no],[filler01],[user_name],[dateTime_Created])

-- select carton_no, ord_type, ord_no, cus_no, ship_to, cd_qual, ship_code, ucc_128_lbl_prtd, sec_lbl_prtd, cart_posted,
-- filler02, package_code, atchd_type, atchd_code, loc, ord_carton_no, manifest_update, filler03, carton_weight, carton_freight,
-- ground_track_no, filler01, user_name as user_name, dateTime_created as dateTime_created
-- from oeshptcl


CREATE TABLE #tmpOESHPTCL (ord_no char(8) NOT NULL,cart_posted char(1), atchd_type char(1), atchd_code char(20))

INSERT INTO #tmpOESHPTCL (ord_no, cart_posted, atchd_type, atchd_code)
/* step 2 - remove unncessary data */
SELECT ord_no, cart_posted, atchd_type, atchd_code FROM oeshptcl_vw


/* stored procedure */
DECLARE @sloc char(3) = '1'

CREATE TABLE [#OrderList] ( [ord_type] [char] (1) NOT NULL , [ord_no] [numeric](8, 0) NOT NULL , [cus_no] [char] (20) NULL , [cus_name] [char] (40) NULL , [oe_po_no] [char] (25) NULL , [ship_via] [varchar] (3) NOT NULL , [ship_date] [varchar] (8) NOT NULL , [ship_no] [char] (17) NULL , [ship_name] [char] (40) NULL , [mark_no] [char] (17) NULL , [mark_name] [char] (40) NULL , [header_status] [varchar] (4) NOT NULL , [message] [varchar] (60) NULL , [okaytopost] [varchar] (1) NOT NULL , [bill_of_lading] [varchar] (8) NULL , [ship_count] [int] NOT NULL , [last_bol] [varchar] (8) NULL, [release_type] [char] (1) Null ) ON [PRIMARY]


/* original statement */
truncate table #orderlist

/* original statement */
/*
insert into #OrderList (ord_type, ord_no,cus_no, cus_name, oe_po_no, ship_via,
ship_date, ship_no, ship_name, mark_no, mark_name, header_status, message, okaytopost,
bill_of_lading, ship_count, last_bol, release_type)

select ord_type, ord_no,cus_no, cus_name, oe_po_no, ' ' as ship_via,
' ' as ship_date, ship_no, ship_name, mark_no, mark_name, ' ' as header_status, space(60) as message, ' ' as okaytopost,
space(8) as bill_of_lading, 0 as ship_count, space(8) as last_bol, release_type
from
oepckhdr_vw where ord_comp = 'Y' and loc = @sloc
*/

/* reroute data source and control number of records to produce error */
insert into #OrderList (ord_type, ord_no,cus_no, cus_name, oe_po_no, ship_via,
ship_date, ship_no, ship_name, mark_no, mark_name, header_status, message, okaytopost,
bill_of_lading, ship_count, last_bol, release_type)

select /**/ TOP 2 /**/ ord_type, ord_no,cus_no, cus_name, oe_po_no, ' ' as ship_via,
' ' as ship_date, ship_no, ship_name, mark_no, mark_name, ' ' as header_status, space(60) as message, ' ' as okaytopost,
space(8) as bill_of_lading, 0 as ship_count, space(8) as last_bol, release_type
from
/* oepckhdr_vw*/ #tmpOEPCKHDR where ord_comp = 'Y' and loc = @sloc


/* original statement - this statement is a non issue - remove from executing */
/*
update #orderlist set header_status = status, ship_via = h.ship_via_cd, ship_date = h.shipping_dt
from #orderlist l, oeordhdr_sql h
where l.ord_no = h.ord_no and
l.ord_type = h.ord_type
*/


/* replace update with select to replicate issue */
select *
/* update #orderlist set ship_count = b.shipment_count, bill_of_lading = b.first_bol, last_bol = b.last_bol */
from #orderlist a,
(SELECT ord_no, count(bill_of_lading) as Shipment_Count, min(bill_of_lading) as first_bol, max(bill_of_lading) as last_bol
from
(
Select o.ord_no as Ord_no, c.atchd_code as bill_of_lading from #orderlist o, oeshptcl_vw c where o.ord_no = c.ord_no and c.cart_posted = 'C' and c.atchd_type = 'S' group by o.ord_no, c.atchd_code
--Select o.ord_no as Ord_no, c.atchd_code as bill_of_lading from #orderlist o, /* oeshptcl_vw */ #tmpOESHPTCL c where o.ord_no = c.ord_no and c.cart_posted = 'C' and c.atchd_type = 'S' group by o.ord_no, c.atchd_code
union
select o.ord_no, t.attached_bol as bill_of_lading from #orderlist o, oeshptcl_vw c, oeshpttl_vw t where o.ord_no = c.ord_no and c.cart_posted = 'C' and c.atchd_type = 'T' and c.atchd_code = t.pallet_no group by o.ord_no, t.attached_bol)
--select o.ord_no, t.attached_bol as bill_of_lading from #orderlist o, /* oeshptcl_vw */ #tmpOESHPTCL c, oeshpttl_vw t where o.ord_no = c.ord_no and c.cart_posted = 'C' and c.atchd_type = 'T' and c.atchd_code = t.pallet_no group by o.ord_no, t.attached_bol)
--select o.ord_no, t.attached_bol as bill_of_lading from #orderlist o, oeshptcl_vw c, /* oeshpttl_vw */ #tmpOESHPTTL t where o.ord_no = c.ord_no and c.cart_posted = 'C' and c.atchd_type = 'T' and c.atchd_code = t.pallet_no group by o.ord_no, t.attached_bol)
--select o.ord_no, t.attached_bol as bill_of_lading from #orderlist o, /* oeshptcl_vw */ #tmpOESHPTCL c, /* oeshpttl_vw */ #tmpOESHPTTL t where o.ord_no = c.ord_no and c.cart_posted = 'C' and c.atchd_type = 'T' and c.atchd_code = t.pallet_no group by o.ord_no, t.attached_bol)

as tmp group by ord_no
) b
where a.ord_no = b.ord_no

I spoke too soon.
As soon as the shipping department did their thing the error returned.
I rebuilt the indexes and the error went away.
There is still some issue going on.

Further testing confirms at least a couple of bugs
BUG 1) Each of the tables involved call out the ord_no field as char(8), yet the temp table that gets created during this process creates the ord_no field as numeric
BUG 2) Somewhere earlier on in other process occasionally allows entries in oeshptcl without an actual ord_no (thus leave the ord_no field as an empty string) - granted it is not supposed to, but it does - potentially even through an app crash
Still some oddities with the testing that I can't explain - especially with the reindexing as that does not change anything as outlined in 1 or 2.

What has Exact said about this issue?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

Support denied any bugs then consultancy confirmed the issues.