Company: Link Technologies
Case No: L12903. Project: 14.90: LinkSOFT Version 14.90 - July 2023
Logged By: Vineet (Link Business Solutions) on behalf of Sanjay (Link Technologies) on 22 May 2023 09:20AM
Priority: High
Product: FMIS (Jiwa/Epicor/etc)
Group: Enhancement
Time Taken: 38.75 (Weight: 60.00)
Assigned To: Sanjay (Link Technologies)
Circulation: Development, Sanjay, Vineet
Resolve By: Wednesday, 21 June 2023 08:34 PM [331 days since logged date]
Status: Closed
Subject: JIWA integration with LinkSOFT - Payment amount greater than Invoice Total in Sales Order Gives Error when activating Cash Book Receipts
Summary:    

When activating Cash Books Receipts in Jiwa after activating all Sales Orders of a particular day, the below error message is displayed. We have noticed that this is happening for Sales Orders where the Payment amount is more than the Sales Invoice Total. In the below case the Payment Total was 3811.09 whereas Invoice Total was 3811.08

---------------------------
Error
---------------------------
Error : unable to update DB_Trans. Attempting to allocate 236.340000 to debtor transaction with InvRemitNo '0000000002-D01' for debtor 'POSCASH-202301 - Cash Sales (Line # 3 of the batch)'
Module : UpdateDebtorTransactions
---------------------------
OK
---------------------------

Jiwa has advised that they can make this change by setting the allocation to 0, however, they are billing us $700AUD as this transaction is not directly created in Jiwa but coming through automation by Link Technologies.

Section B. Relevant History Information: Any information that is relevant to this issue, or may have caused this issue.

OTGL is currently on version 6.5.13. We have upgraded their Test to version 7 and resent transactions for the year 2023. This is happening for all Cash Books receipts back created after the Sales Order has been activated.

Section C. The issue can be reproduced by performing the following steps: 
  1.  Login to QA8
  2.  Login to Jiwa connection Jiwa OTGL
  3. Username admin, password: Standard Admin password for Jiwa
  4. Navigate to Cash Books Receipt and Activate Batch 0000000001. The above message will be shown. 
Note that currently there is no allocation to invoice 0000000002-D01 for customer POSCASH-202301
Audit Notes:Edited by sanjay on 21/06/23 15:27. Edited by sanjay on 19/06/23 13:25. Edited by sanjay on 15/06/23 16:25. Edited by sanjay on 15/06/23 10:16. Edited by sanjay on 02/06/23 17:12. Edited by sanjay on 23/05/23 10:26. Edited by vineet on 22/05/23 09:20. Edited by vineet on 22/05/23 09:00. 
23 May 202310:33AM Comment 1 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 25-05-2023 02:26 PM Time Taken: 7.00 Notes: ETC extended from: 22/05/2023 to 25/05/2023

Hi Vineet, before I add this in as a Software Defect, can you confirm the following: I have tested this in DEMO and was not able to cause the JIWA activation to fail or for a reconciliation difference to occur. 

  1. Using the Standard Demo Database (10.0.0.18, LinkSOFT-DEMO)
    1. Create one transaction in POS with Rounding and send it to JIWA.
      1. Use a day where there are NO OTHER TRANSACTIONS so we can easily work out where the issue is.
      2. Activate and confirm if there is an issue with activation. Also, check the reconciliation report and confirm if it is OK. This will allow us to validate a single entry works correctly.
    2. Now perform 10 entries with a mix of Rounding and Non-Rounding transactions. 
    3. Repeat 1.1.1 and 1.1.2 above and confirm if this reconciles.
  2. Note that I did the above two steps in our DEV and was able to balance.

Once you confirm the above two items, we can look at the OTGL database and see what is missing.


23 May 202307:33PM Comment 2 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 26-05-2023 11:27 PM Time Taken: 3.00

Bula Sanjay

I have done 47 transactions in 3 different shifts using Linksoft-Demo on QA8. The rounding was spot on. See below transaction details. 

ShiftID

TransactionDate

ReferenceNo

Value

AmountReceived

Rounding

Change

Jiwa SO Number

Cash Books Receipt Number

SH000010

2023-05-23 18:36:43.013

RC000021

15.54

15.55

0.01

0

100507

0000000095

SH000010

2023-05-23 18:37:24.767

RC000022

44.31

44.3

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:37:41.407

RC000023

30.69

30.7

0.01

0

100507

0000000095

SH000010

2023-05-23 18:37:56.787

RC000024

4.99

5

0.01

0

100507

0000000095

SH000010

2023-05-23 18:38:09.517

RC000025

27.01

27

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:38:21.163

RC000026

17.59

17.6

0.01

0

100507

0000000095

SH000010

2023-05-23 18:38:32.533

RC000027

13.88

13.9

0.02

0

100507

0000000095

SH000010

2023-05-23 18:38:45.343

RC000028

4.99

5

0.01

0

100507

0000000095

SH000010

2023-05-23 18:38:56.317

RC000029

11.99

12

0.01

0

100507

0000000095

SH000010

2023-05-23 18:39:09.297

RC000030

36.28

36.3

0.02

0

100507

0000000095

SH000010

2023-05-23 18:40:07.797

RC000031

57.69

57.7

0.01

0

100507

0000000095

SH000010

2023-05-23 18:40:28.867

RC000032

52.02

52

-0.02

0

100507

0000000095

SH000010

2023-05-23 18:40:40.067

RC000033

6.09

6.1

0.01

0

100507

0000000095

SH000010

2023-05-23 18:40:52.433

RC000034

37.01

37

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:41:04.110

RC000035

9.84

9.85

0.01

0

100507

0000000095

SH000010

2023-05-23 18:41:15.090

RC000036

56.11

56.1

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:41:28.850

RC000037

30.02

30

-0.02

0

100507

0000000095

SH000010

2023-05-23 18:41:40.870

RC000038

17.01

17

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:41:58.573

RC000039

16.01

16

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:43:58.630

RC000040

4.99

5

0.01

0

100507

0000000095

SH000010

2023-05-23 18:44:14.080

RC000041

22.01

22

-0.01

0

100507

0000000095

SH000010

2023-05-23 18:56:22.287

RC000042

0

0

0

0

100507

0000000095

SH000011

2023-05-23 18:56:48.100

RC000043

15.54

15.55

0.01

4.45

100508

0000000095

SH000011

2023-05-23 18:57:09.820

RC000044

44.31

44.3

-0.01

0.7

100508

0000000095

SH000011

2023-05-23 18:57:33.527

RC000045

30.69

30.7

0.01

19.3

100508

0000000095

SH000011

2023-05-23 18:58:10.607

RC000046

4.99

5

0.01

5

100508

0000000095

SH000011

2023-05-23 18:58:32.590

RC000047

17.59

17.6

0.01

2.4

100508

0000000095

SH000011

2023-05-23 18:58:55.410

RC000048

13.88

13.9

0.02

36.1

100508

0000000095

SH000011

2023-05-23 18:59:14.440

RC000049

11.99

12

0.01

38

100508

0000000095

SH000011

2023-05-23 18:59:39.937

RC000050

36.28

36.3

0.02

3.7

100508

0000000095

SH000011

2023-05-23 19:00:01.860

RC000051

52.02

52

-0.02

3

100508

0000000095

SH000011

2023-05-23 19:00:19.233

RC000052

6.09

6.1

0.01

0.9

100508

0000000095

SH000011

2023-05-23 19:00:50.800

RC000053

9.84

9.85

0.01

10.15

100508

0000000095

SH000011

2023-05-23 19:01:14.097

RC000054

30.02

30

-0.02

10

100508

0000000095

SH000011

2023-05-23 19:01:30.097

RC000055

16.01

16

-0.01

4

100508

0000000095

SH000012

2023-05-23 19:05:16.153

RC000056

15.54

15.55

0.01

4.45

100509

0000000095

SH000012

2023-05-23 19:06:01.630

RC000057

44.31

44.3

-0.01

0.7

100509

0000000095

SH000012

2023-05-23 19:06:17.737

RC000058

30.69

30.7

0.01

19.3

100509

0000000095

SH000012

2023-05-23 19:06:30.940

RC000059

4.99

5

0.01

5

100509

0000000095

SH000012

2023-05-23 19:06:51.543

RC000060

17.59

17.6

0.01

2.4

100509

0000000095

SH000012

2023-05-23 19:07:14.387

RC000061

12.24

12.24

0

0

100509

0000000095

SH000012

2023-05-23 19:07:29.833

RC000062

10.1

10.1

0

0

100509

0000000095

SH000012

2023-05-23 19:07:57.723

RC000063

13.88

13.9

0.02

36.1

100509

0000000095

SH000012

2023-05-23 19:08:19.617

RC000064

11.99

12

0.01

38

100509

0000000095

SH000012

2023-05-23 19:10:59.143

RC000065

11.99

12

0.01

38

100509

0000000095

SH000012

2023-05-23 19:11:17.940

RC000066

36.28

36.3

0.02

3.7

100509

0000000095

SH000012

2023-05-23 19:11:35.903

RC000067

52.02

52

-0.02

3

100509

0000000095


I am unsure what has happened in the OTGL database. However, note that this is not an issue with their Jiwa 6513 integrated into Linksoft at the moment. We have faced this in Jiwa 7. I have tried to study the data for shift SRSH0000012939 but all lines seem to have correct rounding.

Please review this on the QA4 setup to see what we can do to resolve this on the client database.

Thanks
Vineet Ram
222 0085


26 May 202312:22PM Comment 3 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 31-05-2023 02:23 PM Time Taken: 6.00 Notes: ETC extended from: 25/05/2023 to 31/05/2023

Hi Vineet, discussion notes:

  1. Using JIWA and OTGL Database, the analysis was done on JIWA Sales Order: 0000000002, Shift: SRSH0000012939
  2. We found that JIWA is recalculating the TAX and Extended Line Totals to 6 decimal places, causing there to be a difference between LinkSOFT and JIWA
  3. LinkSOFT sends the following information to JIWA:
    1. Unit Price (4dp)
    2. Line Tax Charged (2dp)
    3. Line Total (4DP)

The analysis can be seen on This Google Sheet.

Use OTGL JIWA Database provided:                                    
"Using data from the JIWA Database, below is the extraction of data for Sales Order number: 0000000002"                                  
Using the screenshot of the Sales Order form in JIWA, we can see that the Ordered Total is $3493.86, however, the "This Delivery" is $3493.85.  The Database shows $3493.845658  

Next Step: Can we get JIWA to correct this display before we look into this further?

31 May 202308:48AM Comment 4 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 09-06-2023 12:44 PM Time Taken: 0.25

Bula Guys

We have logged case JIWA-10774 Sales Order Ordered Ex-GST is greater than the Delivered Ex-GST. We will monitor this with Jiwa for now

Thanks
Vineet Ram


02 Jun 202308:41AM Comment 5 by Sanjay (Link Technologies) Case L12903 added to project 14.90
02 Jun 202305:12PM Comment 6 by Sanjay (Link Technologies) Case L12903 removed from project 14.90
14 Jun 202304:36PM Comment 7 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 16-06-2023 07:57 PM Time Taken: 0.50



Link and Jiwa Correspondence

Julie Hanlon


07/Jun/23 10:57 AM

Hi Vineet,

I will put this case to Programming to respond on why Ordered Total Ex GST is different from Delivered Total Ex GST. However, there is a limit to what we can replicate here, because of the data that is being sent from Linksoft.

For example, row 93 of that order. If you use SQL to interrogate the actual data being sent, then you will see Price Ex is not 0.73 as it appears on the screen, it’s 0.7333. And therefore Tax should be 0.7333 x 0.09 x 3 = 0.197991. Rounded up is 0.20. However that’s not what Lifksoft has sent Jiwa. It looks like that on the screen but what it's sent in TaxToCharge is 0.200001.

And so LineTotal should be 2.1999 + 0.200001 = 2.399901. Rounded up is 2.40. Same end answer but a different way of getting there.

If I can replicate the issue by keying the order, then we might be able to go deeper with this. Do you have an order with a small number of lines that displays the problem?

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

Julie Hanlon
Friday 12:22 PM

Hi Vineet,

I can confirm that the issue is caused by the underlying data sent by the Linksoft application. The reason is that there is in fact a slight difference in the calculation between Ordered Totals and Delivered Totals. Delivered Totals are calculated by summing the Line Totals on the lines. The Lines Totals are rounded in each step of their calculation. When looking at Ordered Totals, we don’t yet have a Line Total (items may be on back order). Ordered Totals are calculated on the go, they’re not saved and they’re not editable, they’re still rounded each step of the calculation.

In the order you have given us, the Line Totals are not rounded. So summing them to get a Delivered Total is going to give us a different answer than if we let Jiwa calculate the Line Total.

Take row 3 in the sales order. The Line Total you have given us is 15.00006. Your PriceIncGST is 2.759900. We would have rounded that to 2.76 using “money decimal places”. And the result of every calculation from that point is rounded resulting in a Jiwa Line Total of 15.00.

See the difference between summing 15.00006 and 15.00000?

There are 259 rows with scraps of decimal places in Line Total. If we sum those up we get a 1 cent difference than if Jiwa calculated the total.

I can’t even demonstrate to you how Jiwa would calculate that line, because RGTULP is not configured to order less than 1. Decimal places is 0 so if I try and order 5.435000 it simply rounds to 5.

Perhaps you might examine Linksoft to see if 2.759900 is actually the correct price to be using. We would also like to know how you are importing this data into Jiwa. If you were using the correct objects the rounding might be better taken care of. ------------------------------------------------------

ineet Ram
Friday 12:54 PM

Hi Julie

This is what the developers have explained to us. We are sending data to Jiwa in 2 decimal places. Just because the part numbers being used has “Sales Price Inclusive of Tax” enabled, Jiwa does a back calculation to get the GSTEX figure and Tax figure. If we keep the rounding to 2 decimal places in Jiwa, we will not have this issue. Are your engineers able to handle this?

Thanks
Vineet Ram ------------------------------------------------------

Julie Hanlon
Friday 2:53 PM

Sorry Vineet, I don’t understand your question. Linksoft is NOT sending the data in 2 decimal places. A simple query on SO_Lines will confirm that. Did you read and understand my explanation?

Please answer the question about HOW you are “sending data”.

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

Vineet Ram
Monday 7:54 AM

Hi Julie

Have a look at the LinkSalesData Sheet on the provided spreadsheet. That is the data that Linksoft Application is sending. When Jiwa receives these data, it is recalculating Tax

Now there are 2 things we can do.

  1. Stop Jiwa from recalculating Tax when Linksoft sends the data

  2. Or Round dollar values to 2 decimal places instead of 6 decimal places.

Can we discuss this over teams meeting to be able to answer all your queries?

 OTGL JIWA Sales Order Issue



15 Jun 202310:15AM Comment 8 by Sanjay (Link Technologies) Case L12903 added to project 14.90
15 Jun 202304:50PM Comment 9 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 21-06-2023 08:34 PM Time Taken: 19.00 Notes: ETC extended from: 31/05/2023 to 21/06/2023

Teams Meeting - Vineet and Sanjay. 9 AM to 6 PM AEST.

Meeting Notes:

  1. Analysis shows that the Sales Order created in JIWA is correct and matches between LinkSOFT and JIWA, however, when the order is amended in JIWA, all the Tax and Line Total numbers changes. It is crucial, therefore to make sure users DO NOT edit the Sales Orders in JIUWA before Activating in JIWA as the amounts in LinkSOFT have been signed in VMS.
  2. There are some instances where the JIWA TAX amount and "This Delivery" amounts are different to LinkSOFT by a few cents. This seems to be caused by JIWA Recalculating the Tax Amount and then adding this to the existing "Line Total Without Tax" to calculate the "Line Total With Tax".
  3. Jiwa "This Delivery" Before GST + GST does not equal the "This Delivery INC GST". We were able to make this number match when we rounded the Sales Order Line "CurrentLineTotal" Field to 2 decimal places. This amendment is in LinkSOFT Version 14.90 BETA2 +
    1. JIWA Sales Order Line UPDATE: UPDATE SO_Lines SET CurrentLineTotal = round(CurrentLineTotal,2) WHERE InvoiceHistoryID = 'XYZ'
  4. After applying this change, we performed several tests for entire Shifts to try and cause the JIWA Sales Order Line Totals and Tax (for This Delivery Amounts) to deviate from LinkSOFT. We were not able to cause a difference between LinkSOFT And JIWA. We will perform further tests in the release phase.

Next Step: UAT and Volume Tests


19 Jun 202306:02PM Comment 10 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 22-06-2023 09:55 PM Time Taken: 3.00 Notes: Edited by vineet on 19/06/23 18:03. 

Bula 

This has been tested on the below database with the following steps:

  1. Activated Sales Order for 01/01/2023 to 05/01/2023
  2. Activated the Cash Books Receipts created. (~130 Cash Book Batches)
  3. All were activated without allocation errors.

Test Environment Details:

  1. LINKQA4\CP1
  2. CLIENT-OTGL-LinkSOFT-HQ
  3. http://10.0.0.18/Linksoft

You can review and close this case.

Thanks
Vineet Ram


20 Jun 202309:11AM Comment 11 by Sanjay (Link Technologies) Assigned To: Sanjay (Link Technologies) Followup Date: 22-06-2023 01:11 PM
Thanks Vineet.

If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12903 in the subject line of all emails regarding this issue.

Document size: 156.7 KB
For call complaints, please contact the Managing Director of the company using this form