Error while creating jobsite

dsalhotra · February 16, 2022, 12:46pm

Hello,

We have new Quantify database created. Now there is below mentioned error started coming while creating new jobsite (StockingLocation) in Quantify. The code is working fine in old database. Please advise.

Thanks

ClientConnectionId:a683651c-ede3-47ef-aa2a-8781c4df6ecd
Error Number:547,State:0,Class:16) at 15-02-2022 12:50:29 PM
Project syncing started. at 15-02-2022 12:55:26 PM
Project syncing failed .DataPortal.Update failed (System.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint “FK_TradingPartner_ParentTradingPartnerID”. The conflict occurred in database “ASIRentalManager”, table “dbo.TradingPartner”, column ‘TradingPartnerID’.
The statement has been terminated.
** at Avontus.Rental.Library.TradingPartner.DoInsertUpdate(SqlCommand cmd)**
** at Avontus.Rental.Library.TradingPartner.Update()**
** at Avontus.Rental.Library.StockingLocation.DataPortal_Update()**
** at dm(Object , Object[] )**
** at Avontus.Core.Reflection.MethodCaller.CallMethod(Object obj, DynamicMethodHandle methodHandle, Object[] parameters)**
ClientConnectionId:40837668-ed0a-4c08-a46e-6b33cc19621e
Error Number:547,State:0,Class:16) at 15-02-2022 12:55:28 PM

AvontusDev · February 18, 2022, 4:56am

Hi, dsalhotra.

Can you please post the relevant code and database bacpac for us to be able to review and reproduce the exception. Thanks!

Thank you for posting.

dsalhotra · February 22, 2022, 7:51am

Hi - Used code is mentioned below.

            AvontusPrincipal.Logout();
            bool success = AvontusPrincipal.Login(Utility._API_Login, Utility._API_Pwd);
            if (success)
                Console.WriteLine("Login successful");
            else
            {
                Console.WriteLine("Login failed");
                Console.WriteLine("Press any key to continue...");
                Console.ReadLine();
                return;
            }

            foreach (DataRow item in dt.Rows)
            {
                var profiles = RateProfileList.GetRateProfileList(ActiveStatus.Active, false, Guid.Empty, false);
                //System.Windows.Forms.MessageBox.Show(profiles[0].Name);
                Guid profileID = Guid.Empty;
                foreach (var prof in profiles)
                {
                    if (prof.Name == item["U_RP"].ToString().Trim())
                    {
                        profileID = prof.RateProfileID;
                        break;
                    }
                }

                BusinessPartner customer = BusinessPartner.GetBusinessPartnerByNumber(item["U_Customer"].ToString().Trim());

                Guid custID = customer.BusinessPartnerID;
                var parentTradingPartner = TradingPartner.GetTradingPartner("Dubai");

                StockingLocation newJob = StockingLocation.GetStockingLocation(item["U_PrjName"].ToString().Trim(), false);
                if (string.IsNullOrEmpty(newJob.Number))
                {
                    newJob = StockingLocation.NewStockingLocation(PartnerTypes.JobSite, parentTradingPartner.StockingLocationID);
                }

                newJob.ParentTradingPartnerID = parentTradingPartner.TradingPartnerID;
                newJob.Number = item["U_PrjCode"].ToString().Trim();
                newJob.Name = item["U_PrjName"].ToString().Trim();
                newJob.Description = item["U_PrjDesc"].ToString().Trim();

                newJob.IsActive = item["U_Active"].ToString().Trim() == "Y" ? true : false;
                if (!string.IsNullOrEmpty(item["U_StartDate"].ToString().Trim()))
                {
                    newJob.StartDate = item["U_StartDate"].ToString().Trim();
                }

                if (!string.IsNullOrEmpty(item["U_EndDate"].ToString().Trim()))
                {
                    newJob.StopDate = item["U_EndDate"].ToString().Trim();
                }

                if (!string.IsNullOrEmpty(item["U_Customer"].ToString().Trim()))
                {
                    newJob.BusinessPartnerID = custID;
                }

                if (Guid.Empty != profileID)
                {
                    newJob.DefaultRateProfileID = profileID;
                }
                newJob.BillingMethod = BillingMethodType.Arrears;
                if (!string.IsNullOrEmpty(item["U_InvDate"].ToString().Trim()))
                {
                    newJob.FirstInvoiceDate = item["U_InvDate"].ToString().Trim();
                }

                if (!string.IsNullOrEmpty(item["U_InvEve"].ToString().Trim()))
                {
                    newJob.CycleDaysArrears = Convert.ToInt32(item["U_InvEve"].ToString().Trim());
                }

                TaxRateCollection oTaxCollection = TaxRateCollection.GetTaxRateCollection(ActiveStatus.Active, false, Guid.Empty);
                Guid oOtherTaxRateID = Guid.Empty;
                foreach (var oTax in oTaxCollection)
                {
                    if (oTax.Name == item["U_OT"].ToString())
                    { oOtherTaxRateID = oTax.TaxRateID; break; }
                }
                newJob.JobTax2ID = oOtherTaxRateID;
                newJob.RentIsTaxable = item["U_CTR"].ToString().Trim() == "Y" ? true : false;
                if (!string.IsNullOrEmpty(item["U_IsBillable"].ToString().Trim()) && item["U_IsBillable"].ToString().Trim() == "Y")
                {
                    newJob.IsBillable = true;
                }
                else
                    newJob.IsBillable = false;

                if (!string.IsNullOrEmpty(item["U_BillCycle"].ToString().Trim()) && item["U_BillCycle"].ToString().Trim() == "D")
                { newJob.ArrearsBillingCycle = ArrearsBillingCycleType.Daily; }
                else if (!string.IsNullOrEmpty(item["U_BillCycle"].ToString().Trim()) && item["U_BillCycle"].ToString().Trim() == "M")
                { 
                    newJob.ArrearsBillingCycle = ArrearsBillingCycleType.Monthly; 
                }

                if (!string.IsNullOrEmpty(item["U_MI"].ToString().Trim()) && item["U_MI"].ToString().Trim() == "L")
                { newJob.MonthlyBillingCycle = MonthlyBillingCycleType.LastDay; }
                else if (!string.IsNullOrEmpty(item["U_MI"].ToString().Trim()) && item["U_MI"].ToString().Trim() == "S")
                {
                    newJob.MonthlyBillingCycle = MonthlyBillingCycleType.SpecificDay;
                   
                    if (!string.IsNullOrEmpty(item["U_DOM"].ToString().Trim()))
                    {
                        newJob.DayOfMonth = Convert.ToInt32(item["U_DOM"].ToString().Trim());
                    }
                }

                if (newJob.IsSavable)
                {
                    newJob.Save();

                    query = "Call WMS_QNTFY_UPDATE_PROJ_HIST_TAB('" + item["U_PrjCode"].ToString().Trim() + "')";
                    OdbcCommand oCommand = new OdbcCommand(query, oHanaConn);
                    oCommand.ExecuteNonQuery();
                    oCommand.Dispose();
                    Console.WriteLine("Project [" + newJob.Name.ToString() + "] has been saved.");
                }
                else
                {
                    Utility.WriteLogFile("Project", "Project syncing failed. [" + item["U_PrjCode"].ToString().Trim() + "]");
                    foreach (var rule in newJob.BrokenRulesCollection)
                    {
                        string ss = rule.Description;
                        Utility.WriteLogFile("Project", "Error : " + rule.Description);
                    }
                }
            }

AvontusDev · February 23, 2022, 11:34am

Hi, dsalhotra. We’re currently reviewing your code; Thank you for posting.

AvontusDev · February 24, 2022, 3:48pm

Hi, dsalhotra. Can you give the below code a try and see if it works for you. This is derived from your code by removing dependency on DataRow for us to be able to test it. This works for us against the database that you’ve sent.

    AvontusPrincipal.Logout();
    AvontusPrincipal.Login("Admin", "password");

    var profiles = RateProfileList.GetRateProfileList(ActiveStatus.Active, false, Guid.Empty, false);
    BusinessPartner customer = BusinessPartner.GetBusinessPartnerByNumber("CUS-0000115");

    Guid custID = customer.BusinessPartnerID;
    var parentTradingPartner = TradingPartner.GetTradingPartner("Dubai Branch");

    StockingLocation newJob = StockingLocation.NewStockingLocation(PartnerTypes.JobSite, parentTradingPartner.StockingLocationID);

    newJob.ParentTradingPartnerID = parentTradingPartner.TradingPartnerID;
    newJob.Number = "JB012";
    newJob.Name = "JOB-012";
    newJob.Description = "JOBSITE 12";

    newJob.IsActive = true;
    newJob.StartDate = DateTime.Now.ToShortDateString();
    newJob.StopDate = DateTime.Now.AddMonths(3).ToShortDateString();
    newJob.BusinessPartnerID = custID;

    newJob.DefaultRateProfileID = profiles[0].RateProfileID;
    newJob.BillingMethod = BillingMethodType.Arrears;
    newJob.ArrearsBillingCycle = ArrearsBillingCycleType.Monthly;
    newJob.MonthlyBillingCycle = MonthlyBillingCycleType.LastDay;
    newJob.FirstInvoiceDate = (new DateTime(DateTime.Now.Year, DateTime.Now.Month + 1, 1).AddDays(-1)).ToShortDateString();

    TaxRateCollection oTaxCollection = TaxRateCollection.GetTaxRateCollection(ActiveStatus.Active, false, Guid.Empty);
    Guid oOtherTaxRateID = Guid.Empty;
    oOtherTaxRateID = oTaxCollection[0].TaxRateID;
    newJob.JobTax1ID = oOtherTaxRateID;
    newJob.RentIsTaxable = true;
    newJob.IsBillable = true;

    newJob.Save();

Thanks for posting.

dsalhotra · March 7, 2022, 2:09pm

AvontusDev:

AvontusPrincipal.Logout(); AvontusPrincipal.Login(“Admin”, “password”); var profiles = RateProfileList.GetRateProfileList(ActiveStatus.Active, false, Guid.Empty, false); BusinessPartner customer = BusinessPartner.GetBusinessPartnerByNumber(“CUS-0000115”); Guid custID = customer.BusinessPartnerID; var parentTradingPartner = TradingPartner.GetTradingPartner(“Dubai Branch”); StockingLocation newJob = StockingLocation.NewStockingLocation(PartnerTypes.JobSite, parentTradingPartner.StockingLocationID); newJob.ParentTradingPartnerID = parentTradingPartner.TradingPartnerID; newJob.Number = “JB012”; newJob.Name = “JOB-012”; newJob.Description = “JOBSITE 12”; newJob.IsActive = true; newJob.StartDate = DateTime.Now.ToShortDateString(); newJob.StopDate = DateTime.Now.AddMonths(3).ToShortDateString(); newJob.BusinessPartnerID = custID; newJob.DefaultRateProfileID = profiles[0].RateProfileID; newJob.BillingMethod = BillingMethodType.Arrears; newJob.ArrearsBillingCycle = ArrearsBillingCycleType.Monthly; newJob.MonthlyBillingCycle = MonthlyBillingCycleType.LastDay; newJob.FirstInvoiceDate = (new DateTime(DateTime.Now.Year, DateTime.Now.Month + 1, 1).AddDays(-1)).ToShortDateString(); TaxRateCollection oTaxCollection = TaxRateCollection.GetTaxRateCollection(ActiveStatus.Active, false, Guid.Empty); Guid oOtherTaxRateID = Guid.Empty; oOtherTaxRateID = oTaxCollection[0].TaxRateID; newJob.JobTax1ID = oOtherTaxRateID; newJob.RentIsTaxable = true; newJob.IsBillable = true; newJob.Save();

Hi - Code worked.
@Functional’s Comments: All values are replicated except tax code which are posted to Sales Tax.
Can you elaborate what I needs to change in the my original code I had given as that was working earlier in previous database.

Thanks,

AvontusDev · March 15, 2022, 10:37am

Hi, dsalhotra. JobTax1ID & JobTax2ID corresponds to the below comboboxes in UI respectively, you chose the relevant property to assign the tax. Sales tax corresponds to JobTax1ID and Other tax corresponds to JobTax2ID.

           var profiles = RateProfileList.GetRateProfileList(ActiveStatus.Active, false, Guid.Empty, false);
            BusinessPartner customer = BusinessPartner.GetBusinessPartnerByNumber("CUS-0000115");

            Guid custID = customer.BusinessPartnerID;
            var parentTradingPartner = TradingPartner.GetTradingPartner("Dubai Branch");

            StockingLocation newJob = StockingLocation.NewStockingLocation(PartnerTypes.JobSite, parentTradingPartner.StockingLocationID);

            newJob.ParentTradingPartnerID = parentTradingPartner.TradingPartnerID;
            newJob.Number = "JB0121";
            newJob.Name = "JOB-0121";
            newJob.Description = "JOBSITE 121";

            newJob.IsActive = true;
            newJob.StartDate = DateTime.Now.ToShortDateString();
            newJob.StopDate = DateTime.Now.AddMonths(3).ToShortDateString();
            newJob.BusinessPartnerID = custID;

            newJob.DefaultRateProfileID = profiles[0].RateProfileID;
            newJob.BillingMethod = BillingMethodType.Arrears;
            newJob.ArrearsBillingCycle = ArrearsBillingCycleType.Monthly;
            newJob.MonthlyBillingCycle = MonthlyBillingCycleType.LastDay;
            newJob.FirstInvoiceDate = (new DateTime(DateTime.Now.Year, DateTime.Now.Month + 1, 1).AddDays(-1)).ToShortDateString();

            TaxRateCollection oTaxCollection = TaxRateCollection.GetTaxRateCollection(ActiveStatus.Active, false, Guid.Empty);
            newJob.JobTax1ID = oTaxCollection[0].TaxRateID;
            newJob.JobTax2ID = oTaxCollection[1].TaxRateID;
            newJob.RentIsTaxable = true;
            newJob.IsBillable = true;

            newJob.Save();

Thanks for posting!

dsalhotra · March 20, 2022, 3:27pm

Hi - I am getting same error while creating same JobSite which I was trying to create through my original code. Only I passed same values to your code which I was passing to my code.
I don’t think there is anything wrong in my original code as it was working without any issue in previous database. Something is wrong with this new database when creating this new jobsite. Now you have to check the database why it is trying to insert duplicate record when there is no such jobsite available.

FYI I have give your modified code hereunder.

public static void Sample(OdbcConnection oHanaConn)
{
AvontusPrincipal.Logout();
AvontusPrincipal.Login(“Admin”, “password”);

        var profiles = RateProfileList.GetRateProfileList(ActiveStatus.Active, false, Guid.Empty, false);
        BusinessPartner customer = BusinessPartner.GetBusinessPartnerByNumber("C-AUH-01018102");

        Guid custID = customer.BusinessPartnerID;
        var parentTradingPartner = TradingPartner.GetTradingPartner("Dubai");

        StockingLocation newJob = StockingLocation.NewStockingLocation(PartnerTypes.JobSite, parentTradingPartner.StockingLocationID);

        newJob.ParentTradingPartnerID = parentTradingPartner.TradingPartnerID;
        newJob.Number = "test 1";
        newJob.Name = "Test project Arab Tec";
        newJob.Description = "JOBSITE 12";

        newJob.IsActive = true;
        newJob.StartDate = DateTime.Now.ToShortDateString();
        newJob.StopDate = DateTime.Now.AddMonths(3).ToShortDateString();
        newJob.BusinessPartnerID = custID;

        newJob.DefaultRateProfileID = profiles[0].RateProfileID;
        newJob.BillingMethod = BillingMethodType.Arrears;
        newJob.ArrearsBillingCycle = ArrearsBillingCycleType.Monthly;
        newJob.MonthlyBillingCycle = MonthlyBillingCycleType.LastDay;
        newJob.FirstInvoiceDate = (new DateTime(DateTime.Now.Year, DateTime.Now.Month + 1, 1).AddDays(-1)).ToShortDateString();

        TaxRateCollection oTaxCollection = TaxRateCollection.GetTaxRateCollection(ActiveStatus.Active, false, Guid.Empty);
        Guid oOtherTaxRateID = Guid.Empty;
        oOtherTaxRateID = oTaxCollection[0].TaxRateID;
        newJob.JobTax1ID = oOtherTaxRateID;
        newJob.RentIsTaxable = true;
        newJob.IsBillable = true;

        newJob.Save();
    }

AvontusDev · March 21, 2022, 2:26pm

Hi, dsalhotra.

Your code refers to Customer “C-AUH-01018102” and Branch “Dubai”. These does not exist in your system/database hence appropriate exception would be thrown.
Instead of “Dubai” you’ve got “Dubai Branch” and instead of “C-AUH-01018102” you’ve got “C-AUH-010233453” in your database. If you refer to any of these existing items your code would execute fine. I shall send detailed screenshots comparing [existing data vs code] through support to you.

Let us know if you need any further support.
Thanks for posting!