Insert / Send RefNbr to another database in Acumatica ERP System

Question

I have created a payment in Checks and Payments screen of Acumatica screen and released it. Please refer to the following screenshot.

I've already create the following code to provide it.

  protected virtual void APPayment_RowPersisted(PXCache sender, PXRowPersistedEventArgs e)
  {
      string serverJade, dbJade, userJade, passJade;
      serverJade = Properties.Settings.Default.serverJade;
      dbJade = Properties.Settings.Default.dbJade;
      userJade = Properties.Settings.Default.userJade;
      passJade = Properties.Settings.Default.passJade;


      APPayment app = (APPayment)e.Row;
      if (e.Operation == PXDBOperation.Update && e.TranStatus == PXTranStatus.Completed)
      {
          if (app.DocType == APPaymentType.Check || app.DocType == APPaymentType.Prepayment)
          {
              if (app.RefNbr != null)
              {

                  using (SqlConnection con = new SqlConnection("server = " + serverJade + "; database = " + dbJade + "; user = " + userJade + "; password = " + passJade + ""))
                  {
                      con.Open();
                      //---- query to update a field in the table of another database -------//
                      string query = "Update EVMaster set AcuRefNo = '" + app.RefNbr + "' where VchNo = 'DD02/16-VIII/12206-VCH-01'";
                      using (SqlCommand com = new SqlCommand(query, con))
                      {
                          SqlDataReader sdr = com.ExecuteReader();
                          sdr.Close();
                      }
                      con.Close();
                  }
              }
          }
      }
  }

I already tried to debug this code, but didn't work. And then I try to use this follwing code.

    protected virtual void APPayment_RowPersisted(PXCache sender, PXRowPersistedEventArgs e)
  {
      string serverJade, dbJade, userJade, passJade;
      serverJade = Properties.Settings.Default.serverJade;
      dbJade = Properties.Settings.Default.dbJade;
      userJade = Properties.Settings.Default.userJade;
      passJade = Properties.Settings.Default.passJade;


      APPayment app = (APPayment)e.Row;
          if (app.DocType == APPaymentType.Check || app.DocType == APPaymentType.Prepayment)
          {
              if (app.RefNbr != null)
              {

                  using (SqlConnection con = new SqlConnection("server = " + serverJade + "; database = " + dbJade + "; user = " + userJade + "; password = " + passJade + ""))
                  {
                      con.Open();
                      //---- query to update a field in the table of another database -------//
                      string query = "Update EVMaster set AcuRefNo = '" + app.RefNbr + "' where VchNo = 'DD02/16-VIII/12206-VCH-01'";
                      using (SqlCommand com = new SqlCommand(query, con))
                      {
                          SqlDataReader sdr = com.ExecuteReader();
                          sdr.Close();
                      }
                      con.Close();
                  }
              }
          }
  }

The codes above is worked, I just remove the if condition (if (e.Operation == PXDBOperation.Update && e.TranStatus == PXTranStatus.Completed) {}).

But it's not my goal, I have to filter document only for Doc Status = 'printed' from the document, and this process will be executed when 'Release' button was clicked.

And also any idea how to get all records in APAdjust of the current document ? because I need to comparing adjgrefnbr in apadjust with refnbr in APInvoice based on adjgrefnbr (apadjust) = refnbr (apinvoice). So I can get also all records of APinvoice based on refnbr (APinvoice) = ajgrefnbr (current apadjust). This condition is used to make 'where' condition of query not have to be hardcoded, I will used variable to provide it.

any suggestions to sove this problem ?

RuslanDev RuslanDev · Accepted Answer · 2017-02-17T04:54:46

Below is an example showing how to extend Release process for checks and subscribe to RowPersisted handler for the APRegister DAC to save released document RefNbr to another database:

public class APPaymentEntryExt : PXGraphExtension<APPaymentEntry>
{
    public PXAction<APPayment> release;

    [PXUIField(DisplayName = "Release", MapEnableRights = PXCacheRights.Update, MapViewRights = PXCacheRights.Update)]
    [PXProcessButton]
    public IEnumerable Release(PXAdapter adapter)
    {
        PXGraph.InstanceCreated.AddHandler<APReleaseProcess>((graph) =>
        {
            graph.RowPersisted.AddHandler<APRegister>(APReleaseCheckProcess.APPaymentRowPersisted);
        });

        return Base.release.Press(adapter);
    }
}

public class APReleaseChecksExt : PXGraphExtension<APReleaseChecks>
{
    protected virtual void ReleaseChecksFilter_RowSelected(PXCache sender, PXRowSelectedEventArgs e)
    {
        var row = e.Row as ReleaseChecksFilter;
        if (row == null) return;

        Base.APPaymentList.SetProcessDelegate(list =>
        {
            PXGraph.InstanceCreated.AddHandler<APReleaseProcess>((graph) =>
            {
                graph.RowPersisted.AddHandler<APRegister>(APReleaseCheckProcess.APPaymentRowPersisted);
            });

            APReleaseChecks.ReleasePayments(list, row.Action);
        });
    }
}

By executing quite simple BQL query you can access APAdjust records associated with released check within handler for the RowPersisted event:

public static class APReleaseCheckProcess
{
    public static void APPaymentRowPersisted(PXCache sender, PXRowPersistedEventArgs e)
    {
        if (e.TranStatus == PXTranStatus.Completed && e.Operation == PXDBOperation.Update)
        {
            var doc = e.Row as APPayment;
            if (doc != null && doc.Released == true)
            {
                // save RefNbr to another database

                foreach (APAdjust oldadj in PXSelect<APAdjust,
                    Where<
                        APAdjust.adjgDocType, Equal<Required<APPayment.docType>>,
                            And<APAdjust.adjgRefNbr, Equal<Required<APPayment.refNbr>>,
                            And<APAdjust.adjNbr, Less<Required<APPayment.lineCntr>>>>>>
                    .Select(sender.Graph, doc.DocType, doc.RefNbr, doc.LineCntr))
                {

                }
            }
        }
    }
}

Insert / Send RefNbr to another database in Acumatica ERP System

1 Answers