public int GetMaxApprovalQueuesID()
        {
            StringBuilder sql = new StringBuilder();

            //sql.Append(" SELECT COUNT(Company) ");
            sql.Append(" Select MaxQueId ");
            sql.Append(" FROM vwGetMaxApprovalQueId ");

            SqlClass.Conn = ConfigurationManager.ConnectionStrings["Finsoft"].ToString();

            string errMsg = null;
            int    count  = (int)SqlClass.ExecutScalarQry(sql.ToString(), ref errMsg);

            return(count);
        }
        public EpiResponse CreateSO(int COTOrderNo)
        {
            EpiResponse objEpiRes = null;

            SqlClass.Conn = ConfigurationManager.ConnectionStrings["Finsoft"].ToString();
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.AppendLine("Select a.OrderHedID as COTOrderNum,b.CompanyName as Company,a.CustNum,a.CustID,c.ShipToNum ");
            strBuilder.AppendLine(" ,a.[Owner] as SalesRep ,a.OrderComments,a.InternalRemarks,a.PromotionRemarks,a.ManualSO,d.Title as Promotion,a.OrderType as PONum,a.OrderDate,a.ReqShipDate,a.BulkOrder,isnull(a.EpiOrderNum,0) as EpiOrderNum from OrderHed a ");
            strBuilder.AppendLine(" left join Company b on a.CompanyID=b.CompanyID left join vwShipTo c on a.ShipToSysRowID=c.SysRowID left join Promotion d on a.PromotionID=d.PromotionID ");
            strBuilder.AppendLine($" Where a.OrderHedID={COTOrderNo} ");

            string errMsg = string.Empty;
            SOHd   SOHd   = SqlClass.SelectQry <SOHd>(strBuilder.ToString(), ref errMsg).FirstOrDefault();

            strBuilder.Clear();

            strBuilder.AppendLine(" select a.[Description] + ', ' AS 'data()' ");
            strBuilder.AppendLine(" from Promotion a inner join OrderPromotions b ");
            strBuilder.AppendLine($" on a.PromotionID=b.PromotionID where b.OrderHedID = {COTOrderNo} FOR XML PATH('') ");
            object promoRem = SqlClass.ExecutScalarQry(strBuilder.ToString(), ref errMsg);

            if (promoRem != null)
            {
                SOHd.Promotion = promoRem.ToString();
                SOHd.Promotion.Trim(' ', ',');
            }
            strBuilder.Clear();
            strBuilder.AppendLine("Select a.OrderHedID as COTOrderNum,a.OrderDtlID as COTOrderLineNum ");
            strBuilder.AppendLine(" ,a.PartNum,a.OrderQty,a.FOCQty,a.UOM,a.BasePrice,a.SellingPrice,a.ProposedBasePrice,a.ProposedSellingPrice ");
            strBuilder.AppendLine($" from OrderDtl a Where a.OrderHedID={COTOrderNo} ");

            IEnumerable <SODtl> SODtl = SqlClass.SelectQry <SODtl>(strBuilder.ToString(), ref errMsg);


            foreach (var dtl in SODtl)
            {
                strBuilder.Clear();
                strBuilder.AppendLine("Select a.OrderDtlID as COTOrderLineNum,a.OrderRelID as COTOrderRelNum,b.ShipToNum ");
                strBuilder.AppendLine(" ,a.OrderRelQty,a.FOCRelQty,a.ShipByDate ");
                strBuilder.AppendLine(" from OrderRel a left join vwShipTo b on a.ShipToSysRowID=b.SysRowID ");
                strBuilder.AppendLine($" Where a.OrderDtlID={dtl.COTOrderLineNum} ");
                if (SOHd.BulkOrder)
                {
                    strBuilder.AppendLine($" Order by a.ShipByDate desc ");
                }

                IEnumerable <SORel> SORel = SqlClass.SelectQry <SORel>(strBuilder.ToString(), ref errMsg);
                dtl.SORel = SORel.ToList();
            }
            SOHd.SODtl = SODtl.ToList();

            //Call Epicor BO
            if (SOHd != null)
            {
                Update upd = new Update();
                objEpiRes = upd.Process(SOHd);

                //
                if (string.IsNullOrEmpty(objEpiRes.ErrMsg) && objEpiRes.EpiOrderNum > 0)
                {
                    int maxCount = GetMaxApprovalQueuesID();

                    maxCount += 1;

                    ApprovalQueue approvalQueue = new ApprovalQueue();

                    approvalQueue.CompanyID       = SOHd.Company;
                    approvalQueue.ApprovalQueueID = maxCount.ToString();
                    approvalQueue.ModuleID        = "SO";
                    approvalQueue.DocumentNo      = objEpiRes.EpiOrderNum.ToString();
                    approvalQueue.ApprovalStatus  = "Approved";
                    approvalQueue.ApprovalStage   = "Queue";
                    approvalQueue.SupplierID      = "";
                    approvalQueue.Remarks         = "COT Order Approved";
                    approvalQueue.SubmitDate      = DateTime.Now;
                    approvalQueue.ProceedDate     = Convert.ToDateTime("01/01/1900");

                    string ErMsg = InsertApprovalQueue(approvalQueue);
                    if (!string.IsNullOrEmpty(ErMsg))
                    {
                        objEpiRes.ErrMsg = string.Format("Not Inserted to Approval Queue due to {0}", ErMsg);
                    }
                }
            }

            return(objEpiRes);
        }