Just throwing this out there to see if we are the only ones. We have logged a case.
Sorry it is lengthy.
Environment
Windows Server standard 2016
SQL Server standard 2017
Mix of Win 10/11 clients
All kept patched up to date
ESET AV with exclusions
Defender disabled
SOP batch processing NOT exclusive.
Issue on Delivery
The issue was first noticed 8+ months ago and we have been trying to isolate the cause since.
Issue
Randomly the running totals in CNAME and CSTWH are not being updated when stock is issued from SOP.
This happens to a handful of transactions per week.
In general orders are raised and then SOP batch processed to deliveries. We initially thought the issue was related to SOP batch processing not being exclusive, but we have seen the problem with at least one Order with a single line for a product only sold to one customer, which was progressed in the normal way from Order to Invoice when there was very little other activity in SOP or Stock.
CTRAN appears to update correctly. We do have a separate problem which seems to be related to editing orders.
There is bespoke much of which has been working since Opera DOS. We have only noticed this problem since Opera SE has been live. Palmers operate a rolling stock take, so it would have been identified.
We have replicated the problem in a test environment without the bespoke, but cannot come up with a script which will make it happen.
The customer is currently running a daily report based on the following SQL to keep tabs and then update CNAME and CSTWH as required.
I have also attached a couple of the update queries which will give an idea of the differences.
Thanks
Colin
SELECT *
FROM (SELECT *
, Ret+Rec+Adj+Iss+Tfer as cInStock_a
, Quan_Sum-Quan_Moved as cInStock_b
, Quan_Sum-Quan_Moved-Alloc as cFreeStock
FROM (Select [ct_ref],[cn_desc],[cn_instock],[cn_alloc],[cn_freest],[cs_freest],cs_instock,cs_alloc,saleord
,Sum(Case when [ct_type]= 'I' then [ct_quan]else 0 end) As 'Iss'
,Sum(Case when [ct_type]= 'J' then [ct_quan]else 0 end) As 'Adj'
,Sum(Case when [ct_type]= 'T' then [ct_quan]else 0 end) As 'Ret'
,Sum(Case when [ct_type]= 'A' then [ct_quan]-[ct_moved] else 0 end) As 'Alloc'
,Sum(Case when [ct_type]= 'D' then [ct_quan]else 0 end) As 'DeAlloc'
,Sum(Case when [ct_type]= 'R' then [ct_quan]else 0 end) As 'Rec'
,Sum(Case when [ct_type]= 'N' then [ct_quan]else 0 end) As 'Neg'
,Sum(Case when [ct_type]= 'N' then [ct_moved]else 0 end) As 'NegM'
,Sum(Case when [ct_type]= 'X' then [ct_quan]else 0 end) As 'Tfer'
,Sum(Case when [ct_type]In ('N','R','T') or ([ct_type] in ('J','X') and [ct_quan]>0) then [ct_quan]else 0 end) As 'Quan_Sum'
,Sum(Case when [ct_type]In ('I') or ([ct_type] in ('J','X') and [ct_quan]>0) then [ct_quan]else 0 end) As 'Quan_Out'
,Sum(Case when [ct_type]In ('N','R','T') or ([ct_type] in ('J','X') and [ct_quan]>0) then [ct_moved]else 0 end) As 'Quan_Moved'
From [Opera3SECompany00A].[dbo].[ctran]
Join [Opera3SECompany00A].[dbo].[cname] on [ct_ref] = [cn_ref]
Join [Opera3SECompany00A].[dbo].[cstwh] on [ct_ref] = [cs_ref] and ct_loc = cs_whar
join (SELECT it_stock, it_cwcode, (it_stock+it_cwcode) as ref, SUM(it_quan-it_qtydelv-it_qtyinv) AS saleord
FROM [Opera3SECompany00A].[dbo].[itran] LEFT JOIN [Opera3SECompany00A].[dbo].[ihead] ON it_doc=ih_doc
WHERE it_cwcode='YARD' AND it_status='A' AND ih_sorder<>' ' AND itran.state=1
GROUP BY it_stock, it_cwcode) Q3 ON ct_ref=it_stock and ct_loc=it_cwcode
where ct_loc = 'YARD'
group by ct_ref, cn_desc,cn_instock,cn_alloc,cn_freest,cs_freest,cs_instock,cs_alloc,saleord) Q1) Q2
WHERE [cs_alloc]<>Alloc
order by ct_ref
