×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
ENVIRONMENT:
Macola ES 9.5.300
WMS 9.6.203
----------------------------------------
ISSUE:

We are experiencing an error when attempting to run the Update Macola function within WMS - Billing, after doing Pack Shipments.
This error seems to present itself when there is only one or two orders set to be processed (oepckhdr.ord_comp = 'y')

I am wondering 1) anyone else experience this and/or 2) anyone else able to replicate?

----------------------------------------
ERROR DETAILS:

Attempting to perform the Update Macola function from WMS - Billing and receive the following error:

Error in doPostSelections2
Error.# -2147217913
Error converting data type varchar to numeric
wms115-update_macola_proc1 '1','ALL','ALL','ALL','ALL','ALL','ALL','ALL','ALL','ALL'

followed by:

Error.# 3704
Operation is not allowed when the object is closed

and an apparent endless loop of:

Error.# -2147217913
Incorrect syntax near 'wms115_update_macola_proc1'.

finally selecting Cancel provides:

Stop statement encountered

and the application closes.
----------------------------------------
NOTES:

SQL Profiler shows the failure occuring at the third update statement in wms115-update_macola_proc1 :
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
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)
as tmp group by ord_no
) b
where a.ord_no = b.ord_no


Prior to this the CREATE TABLE [#OrderList] statement is issued :
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]
----------------------------------------
ADDRESSING THE ERROR:

Note that ord_no is being created in the temp table as numeric and oepckhdr.ord_no is char(8), and/or lack of any explicit conversion - which seems to be a bug in itself.

----------------------------------------
STEPS TO TEST AND REPLICATE:

/* remove update safegaurds (1 = 2) to actually perform updates */

/* capture records before update */
SELECT * FROM oepckhdr p WHERE p.ord_comp = 'y' AND p.loc = '1'
/* !!! capture these records or send these to a temp table to come back and fix after testing !!! */

/* update records to clear flag */
UPDATE p SET p.ord_comp = '' FROM oepckhdr p WHERE p.ord_comp = 'y' AND p.loc = '1' AND 1 = 2

/* test billing screen - okay */

/* let's get one order in here */
UPDATE p SET p.ord_comp = 'Y' FROM oepckhdr p WHERE p.id IN (SELECT TOP 1 s.id FROM OEPCKHDR s WHERE s.ord_comp = '' AND s.loc = '1') AND 1 = 2

/* test billing screen - error */

/* let's get another order in here */
UPDATE p SET p.ord_comp = 'Y' FROM oepckhdr p WHERE p.id IN (SELECT TOP 1 s.id FROM OEPCKHDR s WHERE s.ord_comp = '' AND s.loc = '1') AND 1 = 2

/* test billing screen - error */

/* let's get another order in here */
UPDATE p SET p.ord_comp = 'Y' FROM oepckhdr p WHERE p.id IN (SELECT TOP 1 s.id FROM OEPCKHDR s WHERE s.ord_comp = '' AND s.loc = '1') AND 1 = 2

/* test billing screen - okay */

/* update ord_comp back to original values */

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

(OP)
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

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

(OP)
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.

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

(OP)
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.

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

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: www.gainfocus.biz/exceladdin.html

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

(OP)
Support denied any bugs then consultancy confirmed the issues.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close