I am trying to create a method directive BPM to raise an exception and stop the user whenever they try to post an invoice group through A/R Invoice Entry that has an invoice on hold.
I have found the method Erp.InvcGrp.PostInvoices. I created a Pre-Processing BPM for this. I then hoped to simply make a Condition that stated when the InvcHead.HoldInvoice of the changed row is equal to TRUE to Raise an exception that stated the user was trying to Post a group and stop them.
However, I cannot seem to access any tt table or any data with this method. When I put a condition on the BPM Workflow Designer and try to click specified or anything related to a dataset I get the following error:
Warning
Neither table/tableset arguments nor tableset directive variables are defined.
Please define at least one tableset directive variable to use this condition.
Is this a situation that Iām going to have to use custom code to read the InvcHead table? I see that the method is on the InvcGrp. However, I cannot access the ttInvcGrp or any other table with the base widgets. Is there a using or other reference I need to use? Should I be using a different method on this?
I likely can put in a BPM on the printing of the Edit List, but I would prefer to do it at posting, if possible.
I found a Method that works for this. It is Erp.InvcGrp.PrePostInvoices. Once I used that Method, everything else came together.
Total scope of this project was to stop posting if we had any invoice within the group OnHold, with an InvoiceDtl line of 0 extended price (0 quantity or 0 doc unit price), or with a misc line, but no actual Misc Amount.
I made a Pre-Processing method that had conditions of:
Number of rows in the OnHold query is more or equal to 1
OR Number of rows in the ZeroExtPrice query is more or equal to 1
OR Number of rows in the ZeroMiscPrice query is more or equal to 1
And created queries linking ttInvcGrp with InvcHead (where InvoiceHeld is located), InvcDtl (InvcDtl.ExtPrice), and InvcMisc for the last query. Then put the criteria on the required table, such as InvcDtl.ExtPrice = 0. Last, I put a Raise Exception widget on the true for the condition to tell the user they have invoices that have $0 amounts or Invoices On Hold.
Well, it looks like the query for the InvoiceHead.InvoiceHeld would not pop the message box as expected. I also wanted to report to the user which invoice was the problem. So, I had to create this all as custom code.
/* This finds the first invoice in group that is On Hold (InvcHead.InvoiceHeld), $0 line price (InvcDtl.ExtPrice), or a misc charge with $0 total (InvcMscRow.DocMiscAmt).
// It stops the posting process at that point and pops up an Exception message box which explains what the issue is and what invoice is causing it.
// Only finds the first invoice and stops posting. If there are more after first invoice is fixed, the next posting will stop posting and tell the next issue and invoice.
*/
foreach (var IG in ttInvcGrp)
{
var ih = (from InvcHeadRow in Db.InvcHead // Find first invoice on hold
where string.Compare(IG.Company, InvcHeadRow.Company, true) == 0 &&
string.Compare(IG.GroupID, InvcHeadRow.GroupID, true) == 0 &&
InvcHeadRow.InvoiceHeld == true
select InvcHeadRow).FirstOrDefault();
if (ih != null)
{
string PrintNum = Convert.ToString(ih.InvoiceNum);
throw new Ice.BLException("Invoice " + PrintNum + " is On Hold. Please remove hold or transfer invoice to Hold Group.");
}
foreach (var ih2 in (from InvcHeadRow in Db.InvcHead //iterate through each invoice in group with InvcHead
where string.Compare(IG.Company, InvcHeadRow.Company, true) == 0 &&
string.Compare(IG.GroupID, InvcHeadRow.GroupID, true) == 0
select InvcHeadRow))
{
var id = (from InvcDtlRow in Db.InvcDtl //iterate through each invoice in group with InvDtl
where string.Compare(ih2.Company, InvcDtlRow.Company) == 0 &&
ih2.InvoiceNum == InvcDtlRow.InvoiceNum &&
Decimal.Compare(InvcDtlRow.ExtPrice, 0) == 0 //find any that have $0 total price on line
select InvcDtlRow).FirstOrDefault();
if (id != null)
{
string PrintNum = Convert.ToString(id.InvoiceNum);
throw new Ice.BLException("Invoice " + PrintNum + " has a $0 Line. Please add a unit price or quantity.");
}
}
foreach (var ih3 in (from InvcHeadRow in Db.InvcHead //iterate through each invoice in group with InvcHead for InvcMisc
where string.Compare(IG.Company, InvcHeadRow.Company, true) == 0 &&
string.Compare(IG.GroupID, InvcHeadRow.GroupID, true) == 0
select InvcHeadRow))
{
foreach (var id2 in (from InvcDtlRow in Db.InvcDtl //iterate through each invoice in group with InvcDtl
where string.Compare(ih3.Company, InvcDtlRow.Company, true) == 0
&& ih3.InvoiceNum == InvcDtlRow.InvoiceNum
select InvcDtlRow))
{
var im = (from InvcMscRow in Db.InvcMisc //iterate through each invoice in group with InvcHead for InvcMisc
where string.Compare(id2.Company, InvcMscRow.Company) == 0 &&
id2.InvoiceNum == InvcMscRow.InvoiceNum &&
id2.InvoiceLine == InvcMscRow.InvoiceLine &&
Decimal.Compare(InvcMscRow.DocMiscAmt, 0) == 0 //find first invoice that has misc charges that = $0
select InvcMscRow).FirstOrDefault();
if (im != null)
{
string PrintNum = Convert.ToString(im.InvoiceNum);
throw new Ice.BLException("Invoice " + PrintNum + " has a $0 Misc Charge. Please add a unit price or quantity.");
}
}
}
}
Db.Validate();