Jiwa

Got an error while trying to create a sales order and got an error saying "Cannot insert duplicate key row in object 'dbo.SO_Main' with unique index 'SO_MainInvoiceNo'. The duplicate key value is (S260992).\r\nThe statement has been terminated.". Im not sending the invoice ID in the request.

Here is the request body im sending:

[POST] https://japitest.cedarhospitality.com:5452//SalesOrders

{"DebtorID":"e3cc538b3ccf471992ae","DebtorAccountNo":"ONLINECEDAR","SenderEDIAddress":"","InitiatedDate":"2022-07-08T00:27:39","InvoiceInitDate":"2022-07-08T00:27:39","SalesOrderType":"e_SalesOrderNormalSalesOrder","OrderType":"e_SalesOrderOrderTypeActiveOrder","Status":"e_SalesOrderEntered","EDIStatus":"e_SalesOrderHistoryEDIPickStatusNone","BillType":"e_SalesOrderShipAndBill","Delivered":false,"EDIPickStatus":"e_SalesOrderHistoryEDIPickStatusNone","EDIOrderType":"e_SalesOrderEDIOrderTypeNormal","DropShipment":false,"LogicalID":"ZZZZZZZZZZ0000000000","CreditNote":false,"DeliveryAddress1":"1 Test St","DeliveryAddress2":"","DeliveryAddressSuburb":"Bulleen","DeliveryAddressState":"VIC","DeliveryAddressContactName":"Test","DeliveryAddressPostcode":"3105","DeliveryAddressCountry":"","DeliveryAddressNotes":"","Lines":[{"QuantityOrdered":6,"PriceExGst":4.05,"PriceIncGst":3.26,"DiscountedPrice":2.96,"TaxToCharge":"1.78","LineTotal":19.56,"QuantityOriginalOrdered":6,"InventoryID":"98e2f7b6b87644dc97ef","PartNo":"GROS42321600"}],"Payments":[{"AmountPaid":19.56,"PaymentDate":"2022-07-08T00:27:39","ProcessPayment":false,"Processed":true}]}

and here is the response:
{"ResponseStatus":{"ErrorCode":"SqlException","Message":"Cannot insert duplicate key row in object 'dbo.SO_Main' with unique index 'SO_MainInvoiceNo'. The duplicate key value is (S260992).\r\nThe statement has been terminated.","StackTrace":"[SalesOrderPOSTRequest: 7/8/2022 12:27:40 AM]:\n[REQUEST: {SystemSettings:{},InitiatedDate:2022-07-08T00:27:39+10:00,InvoiceInitDate:2022-07-08T00:27:39+10:00,SalesOrderType:e_SalesOrderNormalSalesOrder,OrderType:e_SalesOrderOrderTypeActiveOrder,Status:e_SalesOrderEntered,EDIStatus:e_SalesOrderHistoryEDIPickStatusNone,BillType:e_SalesOrderShipAndBill,Delivered:False,EDIPickStatus:e_SalesOrderHistoryEDIPickStatusNone,EDIOrderType:e_SalesOrderEDIOrderTypeNormal,CashSales:{},DropShipment:False,JobCosting:{},LogicalID:ZZZZZZZZZZ0000000000,CreditNote:False,SenderEDIAddress:\"\",DebtorID:e3cc538b3ccf471992ae,DebtorAccountNo:ONLINECEDAR,DeliveryAddress1:1 Test St,DeliveryAddress2:\"\",DeliveryAddressSuburb:Bulleen,DeliveryAddressState:VIC,DeliveryAddressContactName:Test,DeliveryAddressPostcode:3105,DeliveryAddressCountry:\"\",DeliveryAddressNotes:\"\",CustomFieldValues:[],Notes:[],Documents:[],Payments:[{PaymentType:{GeneralLedgerAccount:{}},AmountPaid:19.56,PaymentDate:2022-07-08T00:27:39+10:00,ProcessPayment:False,Processed:True}],Lines:[{QuantityOrdered:6,PriceExGst:4.05,PriceIncGst:3.26,DiscountedPrice:2.96,TaxToCharge:1.78,LineTotal:19.56,QuantityOriginalOrdered:6,InventoryID:98e2f7b6b87644dc97ef,PartNo:GROS42321600,CustomFieldValues:[],LineDetails:[],ShippingLabels:[]}],Histories:[],ASNs:[]}]\nSystem.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.SO_Main' with unique index 'SO_MainInvoiceNo'. The duplicate key value is (S260992).\r\nThe statement has been terminated.\r\n at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at JiwaFinancials.Jiwa.JiwaODBC.database.ExecuteNonQuery(SqlCommand SQLCommand) in C:\\agent\\_work\\1\\s\\Jiwa\\JiwaODBC\\database.vb:line 2401\r\n at JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder.iSave(Boolean SavingForPrint, Boolean SavingForSplit) in C:\\agent\\_work\\1\\s\\Jiwa\\JiwaSales\\SalesOrder\\SalesOrder.vb:line 7724\r\n at JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder.iSave() in C:\\agent\\_work\\1\\s\\Jiwa\\JiwaSales\\SalesOrder\\SalesOrder.vb:line 6781\r\n at JiwaFinancials.Jiwa.JiwaApplication.BusinessLogic.Maintenance.Save() in C:\\agent\\_work\\1\\s\\Jiwa\\JiwaApplication\\BusinessLogic\\Maintenance.vb:line 433\r\n at JiwaFinancials.Jiwa.JiwaServiceModel.SalesOrderServices.Post(SalesOrderPOSTRequest request) in c:\\ProgramData\\Jiwa Financials\\Jiwa 7\\7.2.0\\JAPITest\\cedar-srv-dc\\TestJiwaCedarHosp\\Plugins\\APIUser\\Compile\\REST API\\REST API.cs:line 17822\r\n at lambda_method(Closure , Object , Object )\r\n at ServiceStack.Host.ServiceRunner`1.d__13.MoveNext()\r\nClientConnectionId:c13aa958-1911-4d62-91ba-0543028a8d79\r\nError Number:2601,State:1,Class:14","Errors":[]}}

For some reason the system is generating the InvoiceNo which conflicts with an existing InvoiceNo in the system.

You can provide the InvoiceNo in the request, and we'll use that instead - that might be the easiest way forward for you - just try with InvoiceNo of S260993.

You can also change the configuration in Jiwa to set what the last InvoiceNo generated was so that the next generated one does not conflict. You do this by opening the System Numbers Maintenance form, from the System Settings Menu and changing the "Last Number" for the invoice number sequence:


The Setting Description by default is "Central" for the invoice numbers - but can be configured different per user - so don't be alarmed if it's different - where this is set per user is the Staff Maintenance form on the User Settings tab: