SAP b1 production order Batch Quantity Update Issue

We have a custom add-on development, replacing batch allocation SAP b1 window with our custom batch allocation window add-on development (control+Tab).

When I try to update Batch in production order by windows service with DI-API code, previous batch quantity added becomes minus with a new entry automatically added in SAP b1 batch number transaction report.

I'm not allowed to use Issue for production as customer expects us to add & update batches from Production order.

Advice me what need to be corrected.

private bool postProductionToSAP(DataTable refTable/*excelTable*/, string docNumber, string docEntry/*, string filename*/, DBCon objDBCon, ref string errDescription, ref string _PORefDocNum) { bool errorDetect = false; try { Library.WriteErrorLogProduction("Production " + docNumber + " Post Start"); clsCompany objclsCompany = new clsCompany(); _company = new SAPbobsCOM.Company(); if (_company.Connected) _company.Disconnect(); _company = objclsCompany.ConnectDI(SystemSetting.server, SystemSetting.sapdatabase, SystemSetting.LicenseServer, SystemSetting.username, SystemSetting.password); //int sapUserId = _company.UserSignature; if (_company.Connected) { try { Library.WriteErrorLogProduction("Company Connected"); DataTable dtDetail = new DataTable(); DataTable dtProductionData = new DataTable(); // Pull component lines from WOR1 (your original query) string _sqlStmtD = "SELECT * FROM " + SystemSetting.database + ".WOR1 WHERE \"DocEntry\" = '" + docEntry + "'"; objDBCon.Execute(_sqlStmtD, ref dtDetail); SAPbobsCOM.ProductionOrders objInv = (SAPbobsCOM.ProductionOrders)_company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); if (!objInv.GetByKey(Convert.ToInt32(docEntry))) { errDescription = "Production Order not found"; return true; } for (int c = 0; c < dtDetail.Rows.Count; c++) { try { string _tmpItemCode = (dtDetail.Rows[c]["ItemCode"].ToString() ?? "").Trim(); string _tmpWhsCode = (dtDetail.Rows[c]["wareHouse"].ToString() ?? "").Trim(); if (string.IsNullOrWhiteSpace(_tmpItemCode) || string.IsNullOrWhiteSpace(_tmpWhsCode)) continue; // Check if item is batch-managed SAPbobsCOM.Items oItemN = (SAPbobsCOM.Items)_company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems); if (!oItemN.GetByKey(_tmpItemCode)) continue; if (oItemN.ManageBatchNumbers != SAPbobsCOM.BoYesNoEnum.tYES) continue; // Get selected batches for THIS doc + item + whs StringBuilder _sqlStmt = new StringBuilder(); _sqlStmt.Append("SELECT * FROM " + SystemSetting.database + ".ADDON_TBL_BATCH_SELECTION_DETAIL "); _sqlStmt.Append("WHERE \"DOCNO\" = '" + docNumber + "' "); _sqlStmt.Append("AND \"ISALLOCATED\" = 1 "); _sqlStmt.Append("AND \"SAPPOSTSTATUS\" = 0 "); _sqlStmt.Append("AND \"ITEMNO\" = '" + _tmpItemCode + "' "); _sqlStmt.Append("AND \"WHSCODE\" = '" + _tmpWhsCode + "' "); dtProductionData.Clear(); objDBCon.Execute(_sqlStmt.ToString(), ref dtProductionData); if (dtProductionData.Rows.Count == 0) continue; // Find correct SAP line int sapLine = FindSapLineIndex(_tmpItemCode, _tmpWhsCode, objInv); if (sapLine < 0) { Library.WriteErrorLogProduction("Production {docNumber}: No matching SAP line for Item={_tmpItemCode}, Whs={_tmpWhsCode}"); continue; } objInv.Lines.SetCurrentLine(sapLine); // Build a set of existing batches already allocated on this SAP line var existing = new HashSet<string>(StringComparer.OrdinalIgnoreCase); for (int b = 0; b < objInv.Lines.BatchNumbers.Count; b++) { objInv.Lines.BatchNumbers.SetCurrentLine(b); string existBatch = (objInv.Lines.BatchNumbers.BatchNumber ?? "").Trim(); if (!string.IsNullOrEmpty(existBatch)) existing.Add(existBatch); } // Add missing batches for (int j = 0; j < dtProductionData.Rows.Count; j++) { string batchNo = (dtProductionData.Rows[j]["NEWBATCH"].ToString() ?? "").Trim(); string qtyStr = (dtProductionData.Rows[j]["SELECTEDQTY"].ToString() ?? "0").Trim(); if (string.IsNullOrWhiteSpace(batchNo)) continue; double qty = 0; if (!double.TryParse(qtyStr, out qty) || qty <= 0) continue; // Only add if not already present if (!existing.Contains(batchNo)) { objInv.Lines.BatchNumbers.BatchNumber = batchNo; objInv.Lines.BatchNumbers.Quantity = qty; objInv.Lines.BatchNumbers.Add(); existing.Add(batchNo); } } } catch (Exception exLine) { errorDetect = true; Library.WriteErrorLogProduction("Production LINE " + c + " Error...." + exLine.Message); } } #region Document Post int retval = objInv.Update(); if (retval != 0) { errDescription = _company.GetLastErrorDescription(); Library.WriteErrorLogProduction("Production " + docNumber + " Unsucessfull...." + _company.GetLastErrorDescription()); #region Removed //StringBuilder _sqlStmt1 = new StringBuilder(); //_sqlStmt1.Append("UPDATE " + SystemSetting.database + ".ADDON_TBL_BATCH_SELECTION_DETAIL "); //_sqlStmt1.Append("SET \"DOCSTATUS\" = 'POST' "); //_sqlStmt1.Append("WHERE \"DOCNO\" = '" + docNumber + "' "); //_sqlStmt1.Append("AND \"DOCSTATUS\" = 'DRAFT' AND \"ISALLOCATED\" = 1 AND \"SAPPOSTSTATUS\" = 0 "); //objDBCon.Execute(_sqlStmt1.ToString(), ref dtBatchUpdate1); #endregion errorDetect = true; } else { string objCode = ""; int lastNo; _company.GetNewObjectCode(out objCode); lastNo = Convert.ToInt32(objCode); objInv.GetByKey(lastNo); int DocNum = lastNo; string sql = ""; sql = sql + "select \"DocNum\" from " + SystemSetting.database + ".OWOR where \"DocEntry\"='" + DocNum + "'"; DataTable refer = new DataTable(); objDBCon.Execute(sql, ref refer); string DocEntry = (refer.Rows[0]["DocNum"].ToString()); _PORefDocNum = DocEntry; StringBuilder _sqlStmt1 = new StringBuilder(); _sqlStmt1.Append("UPDATE " + SystemSetting.database + ".ADDON_TBL_BATCH_SELECTION_DETAIL "); _sqlStmt1.Append("SET \"DOCSTATUS\" = 'POST',\"SAPPOSTSTATUS\"=1 "); _sqlStmt1.Append("WHERE \"DOCNO\" = '" + docNumber + "' "); _sqlStmt1.Append("AND \"DOCSTATUS\" = 'DRAFT' AND \"ISALLOCATED\" = 1 AND \"SAPPOSTSTATUS\" = 0 "); objDBCon.Execute(_sqlStmt1.ToString(), ref dtBatchUpdate2); //string Key = (headerNum + "," + DocEntry); Library.WriteErrorLogProduction("Production " + docNumber + " Post Sucessfull...."); } #endregion //Marshal.ReleaseComObject(objInv); //} } catch (Exception ex) { errorDetect = true; Library.WriteErrorLogProduction("Production " + docNumber + " Error...." + ex.Message); } } else { errorDetect = true; errDescription = _company.GetLastErrorDescription(); Library.WriteErrorLogProduction("Company Not Connected" + _company.GetLastErrorDescription()); } } catch (Exception ex) { errorDetect = true; Library.WriteErrorLogProduction("Production " + docNumber + " Error...." + ex); } return errorDetect; }

The Table structure

CREATE COLUMN TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ( "ROWNO" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, "DOCNO" VARCHAR(10) NOT NULL, "NEWBATCH" VARCHAR(500) NOT NULL, "ITEMNO" VARCHAR(500) NOT NULL, "WHSCODE" VARCHAR(500) NOT NULL, "REMARK" VARCHAR(500), "UNITCOST" DECIMAL(19,6), "AVAILABLEQTY" DECIMAL(19,6), "SELECTEDQTY" DECIMAL(19,6), "CREATEDDATE" TIMESTAMP, PRIMARY KEY ("ROWNO") ); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("SAPPOSTSTATUS" int DEFAULT 0); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("ADMDATE" DATE ); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("EXPDATE" DATE ); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("MANDATE" DATE ); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("UNITCOSTFC" DECIMAL(19,6) DEFAULT 1); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("BATCHCOSTFC" DECIMAL(19,6) DEFAULT 1); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("ISALLOCATED" TINYINT DEFAULT 0); UPDATE "ADDON_TBL_BATCH_SELECTION_DETAIL" SET "ISALLOCATED" =1 ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("BATCHCOST" DECIMAL(19,6) DEFAULT 1); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("DOCSTATUS" VARCHAR(10) DEFAULT 'DRAFT'); ALTER TABLE "ADDON_TBL_BATCH_SELECTION_DETAIL" ADD ("HASHREF" VARCHAR(100) DEFAULT '');