Solved

How to update APTran table in the APReleaseProcess_Extension ReleaseInvoice delegate


Userlevel 6
Badge +3

Here is the scenario I am trying to accomplish:

When an AP invoice is released using the APRelease - ReleaseInvoice process, I want to check each line of the APTran table for the given invoice.  If the line item has an AllocationIDUsed code in the UsrAllocationIDUsed field, I want to delete that line and create new ones based on the setup of the custom allocation ID setup.

The APTran table is not part of the signature of the ReleaseInvoice process so I don’t have access to it via a cache.

My approach is to pass the “doc” into a function and do the allocation there.

In order to make updates to the APTran table, I need a graph (I think).

In my AllocateLine function, do I create a generic graph for each call to the function?  I am pretty sure I can accomplish my goal doing it that way, but I think I might have a performance issue if the invoice has 100 APTran lines and every one of them is allocated.  Yes...my customer releases HUNDREDS of AP Invoices every day and each invoice might have 50 to 100 lines.

This is my code so far. 

		public delegate List<APRegister> ReleaseInvoiceDelegate(JournalEntry je, ref APRegister doc, PXResult<APInvoice, CurrencyInfo, Terms, Vendor> res, bool isPrebooking, out List<INRegister> inDocs);
[PXOverride]
public virtual List<APRegister> ReleaseInvoice(JournalEntry je, ref APRegister doc, PXResult<APInvoice, CurrencyInfo, Terms, Vendor> res, bool isPrebooking, out List<INRegister> inDocs, ReleaseInvoiceDelegate baseMethod)
{
if (doc.Released != true && doc.DocType == "INV" && (!isPrebooking || doc.Prebooked != true))
{
doc.DocDesc = "JOE ####";
AllocateLine(doc);
}
return baseMethod(je, ref doc, res, isPrebooking, out inDocs);
}

protected void AllocateLine(APRegister doc)
{
foreach (APTran apTran in SelectFrom<APTran>
.Where<APTran.refNbr.IsEqual<@P.AsString>>
.View.Select(Base, doc.RefNbr, 0))
{
if (apTran.CuryLineAmt > 0)
{
//if line has allocation code on it
// 1) delete original line
// 2) add in new lines that are allocated

APTranExt itemExt = PXCache<APTran>.GetExtension<APTranExt>(apTran);

if (itemExt.UsrAllocationIDUsed == null) continue;

// allocate line

}
}

  At the // allocate line comment is where I would do the allocation.

The issue is how to make changes to the DB so that prior to calling the base method, the APTran table has been updated.

I have a custom Action on the Bills and Adjustments screen which works fine if you allocate the lines as they are entered.  Due to the number of lines to be allocated, this is not a feasible approach since the AP invoices are uploaded and released automatically.

This is the code for my Action WHICH WORKS GREAT!  But this action has reference to the cache on the invoice since it is part of the graph.  I am showing this code as maybe I can leverage it somehow.

		public PXAction<APTran> AllocateLine;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Allocate Line", Enabled = false, Visible = false)]
protected void allocateLine()
{

APTran currentItem = this.Base.Transactions.Current;

if (currentItem == null) return;

if (currentItem.CuryLineAmt == 0) return;

APTranExt itemExt = PXCache<APTran>.GetExtension<APTranExt>(currentItem);

if (itemExt.UsrAllocationID == null)
{
throw new Exception("You need to select an Allocation Template");
}

int itemCounter = 0;
int itemCount = 0;
decimal? balanceToAllocate = currentItem.CuryLineAmt;
decimal? balanceToAllocateDiscount = currentItem.CuryDiscAmt;

foreach (var detail in SelectFrom<ICAllocationAccounts>.InnerJoin<ICAllocationCode>.
On<ICAllocationCode.allocationID.IsEqual<ICAllocationAccounts.allocationID>>.
Where<ICAllocationCode.allocationID.IsEqual<@P.AsInt>>.
View.Select(Base, itemExt.UsrAllocationID))
{
itemCount++;
}

this.Base.Transactions.Delete(currentItem);

ICAllocationCode code = SelectFrom<ICAllocationCode>.
Where<ICAllocationCode.allocationID.IsEqual<@P.AsInt>>.
View.Select(Base, itemExt.UsrAllocationID);


foreach (ICAllocationAccounts detail in SelectFrom<ICAllocationAccounts>.InnerJoin<ICAllocationCode>.
On<ICAllocationCode.allocationID.IsEqual<ICAllocationAccounts.allocationID>>.
Where<ICAllocationCode.allocationID.IsEqual<@P.AsInt>>.
View.Select(Base, itemExt.UsrAllocationID))
{
itemCounter++;

APTran newRow = (APTran)this.Base.Transactions.Cache.CreateCopy(currentItem);

newRow.LineNbr = null;
newRow.TranID = null;
newRow.NoteID = null;

if (itemCount == itemCounter)
{
newRow.CuryLineAmt = balanceToAllocate;

if (currentItem.CuryDiscAmt != null && currentItem.CuryDiscAmt > 0)
{
newRow.CuryDiscAmt = balanceToAllocateDiscount;
newRow.CuryTranAmt = newRow.CuryLineAmt - newRow.CuryDiscAmt;
}
else
{
newRow.CuryTranAmt = balanceToAllocate;
}
}
else
{
newRow.CuryLineAmt = Math.Round((decimal)((detail.Percent / 100) * currentItem.CuryLineAmt), 2);
balanceToAllocate -= (decimal)newRow.CuryLineAmt;

if (currentItem.CuryDiscAmt != null && currentItem.CuryDiscAmt > 0)
{
newRow.CuryDiscAmt = Math.Round((decimal)((detail.Percent / 100) * currentItem.CuryDiscAmt), 2);
balanceToAllocateDiscount -= (decimal)newRow.CuryDiscAmt;
newRow.CuryTranAmt = newRow.CuryLineAmt - newRow.CuryDiscAmt;
}
else
{
newRow.CuryTranAmt = newRow.CuryLineAmt;
}
}

if (currentItem.Qty != null && currentItem.Qty > 0)
{
newRow.Qty = Math.Round((decimal)((decimal)(detail.Percent / 100) * currentItem.Qty), 2);
}

if (code.DescriptionSource == DescriptionSourceType.FromDocument)
{
newRow.TranDesc = currentItem.TranDesc;
}
else
{
newRow.TranDesc = code.TranDescr;
}

newRow.BranchID = detail.BranchID;

if(newRow.InventoryID == null)
{
newRow.SubID = detail.AccountSubID;
}
else
{
newRow.TranDesc = currentItem.TranDesc;
}

APTranExt itemExt2 = PXCache<APTran>.GetExtension<APTranExt>(newRow);
itemExt2.UsrAllocationIDUsed = itemExt.UsrAllocationID;
itemExt2.UsrAllocationID = null;

this.Base.Transactions.Cache.Update(newRow);
//this.Base.Transactions.Cache.Insert(newRow);
}
}

If there is some way to call my Action from the ReleaseInvoice delegate that might save a lot of code.  I don’t know if it is possible to call an action from the Bills and Adjustments graph.

 

icon

Best answer by Dmitrii Naumov 30 August 2022, 20:09

View original

11 replies

Userlevel 6
Badge +4

Hi @joe21 

I recommend to have your logic in a separate process that look after those invoices. ReleaseInvoice can be called from multiple places and that added complexity can be a cause of bugs or unintended behaviors (Please see this similar case: How to get the error messages?

 

 

Userlevel 7
Badge +5

@joe21  it seems to me that removing an APTran record and creating a new one may compromise the data integrity. E.g. generated GLTrans have specific relations to initial APTrans. Also, APTran can be related to a project, project task, there may be specific tax involved or a discount. 

 

I’d recommend to rethink the approach completely and avoid changing APtrans after document release.

Userlevel 6
Badge +3

@Leonardo Justiniano The ReleaseInvoice process is the only place I can call to adjust the APTran lines prior to them being posted to the GL.  The lines in the APTran table affect the GLTran table.

I do the same check at the top of my override as is used in the original method:

if (doc.Released != true && doc.DocType == "INV" && (!isPrebooking || doc.Prebooked != true))

As such, the only time this override will call my method to allocate is when it is ok to be released.    

I actually want this override called no matter where an AP document gets released.  The ONLY time that it will actually call my custom method to allocate is if the APTran record (original one) that was imported includes a value in the AllocationIDUsed field.

I read the other thread.  The solutions there don’t look like would be able to handle my use case.

Userlevel 6
Badge +4

@joe21  it seems to me that removing an APTran record and creating a new one may compromise the data integrity. E.g. generated GLTrans have specific relations to initial APTrans. Also, APTran can be related to a project, project task, there may be specific tax involved or a discount. 

 

I’d recommend to rethink the approach completely and avoid changing APtrans after document release.

I agree with Dimitrii. Too many moving pieces at that moment.

Userlevel 7
Badge +5

Actually, I see that you are doing that before the release. Still, there are some questions. 

 

E.g. what happens if the release fails? Do you want to rollback your “allocations”? 

 

I think what you can do in AllocateLine is to create the APInvoiceEntry graph, set the document as current, click your custom button, save it. 

Userlevel 6
Badge +3

I’d recommend to rethink the approach completely and avoid changing APtrans after document release.

@Dmitrii Naumov   If I am changing the APTran table before it is actually released, I would think that any calculations on the invoice would be handled as part of the release process. At the start of the override, nothing has actually been created in the GL.  Note that I do copy over the project ID to the new lines.  I also allocate any affected financial values such as discounts.  

From debugging, when the delegate fires, the only thing in it with any values is the doc object which is the “header” record.  

The only other option I can think of is to create a completely standalone operation that you would run on the APTran table before you release the invoices.  I think maybe that is the best approach.  Let me know if you all agree with that.

If the customer forgets to run the “pre-release” process before releasing invoices, I guess that will be on them. :-)

 

Userlevel 6
Badge +3

Actually, I see that you are doing that before the release. Still, there are some questions. 

 

E.g. what happens if the release fails? Do you want to rollback your “allocations”? 

 

I think what you can do in AllocateLine is to create the APInvoiceEntry graph, set the document as current, click your custom button, save it. 

That is actually what I am trying right now!  :-)

        APInvoiceEntry apInvoiceEntryGraph = PXGraph.CreateInstance<APInvoiceEntry>();

            apInvoiceEntryGraph.GetExtension<APInvoiceEntry_Extension>();

            apInvoiceEntryGraph.Transactions.Current = 
I’m not sure what to do right now to set the current transaction.  If you know what goes after the equal sign, that will save me about 4 hours…  HA!

If the release fails, it is ok.  I still want the lines allocated.  I’m thinking I need to add a bit field to the APTran record to indicate it has already been allocated so it doesn’t do it twice if the release fails for some reason.

UPDATE:  I think I need to use 
            apInvoiceEntryGraph.Document.Current = 

to set the graph to the record I want to process.  I just can’t figure out how to set RefNbr to the current refnbr of the doc passed to my method.

 

Userlevel 6
Badge +3

@Dmitrii Naumov 

If I am able to make reference to my graph extension, do I have to instantiate it for each invoice or can I create it at the “top level” somehow and just re-use it?

Userlevel 6
Badge +3

I’m clicking Refresh on this screen every 10 seconds, like I’m waiting for a girl to tell me if she will go on a date with me.  😂

Userlevel 7
Badge +5

 

So I think it should be something like that:

APInvoiceEntry apInvoiceEntryGraph = PXGraph.CreateInstance<APInvoiceEntry>();

apInvoiceEntryGraph.Document.Current = PXSelect<APInvoice, Where<APInvoice.docType, Equal<Required<APInvoice.docType>>, And<APInvoice.refNbr, Equal<Required<APInvoice.refNbr>>>>>.Select(apInvoiceEntryGraph );
var extension= apInvoiceEntryGraph.GetExtension<APInvoiceEntry_Extension>();
extension.AllocateLines.Press();
apInvoiceEntryGraph.Save.Press();




In the APInvoiceEntryExtension you need something like that:

 

public PXAction<APTran> AllocateLine;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Allocate Line", Enabled = false, Visible = false)]
protected void allocateLine()
{

APTran currentItem = this.Base.Transactions.Current;
PerformAllocation(currentItem);
}

public PXAction<APTran> AllocateLines;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Allocate All Lines", Enabled = false, Visible = false)]
protected void allocateLines()
{

foreach (APTran apTran in Base.Transactions.Select())
{
PerformAllocation(apTran);
}

}

public void PerformAllocation(APTran currentItem )
{
if (currentItem == null) return;

if (currentItem.CuryLineAmt == 0) return;

APTranExt itemExt = PXCache<APTran>.GetExtension<APTranExt>(currentItem);

if (itemExt.UsrAllocationID == null)
{
throw new Exception("You need to select an Allocation Template");
}

int itemCounter = 0;
int itemCount = 0;
decimal? balanceToAllocate = currentItem.CuryLineAmt;
decimal? balanceToAllocateDiscount = currentItem.CuryDiscAmt;

foreach (var detail in SelectFrom<ICAllocationAccounts>.InnerJoin<ICAllocationCode>.
On<ICAllocationCode.allocationID.IsEqual<ICAllocationAccounts.allocationID>>.
Where<ICAllocationCode.allocationID.IsEqual<@P.AsInt>>.
View.Select(Base, itemExt.UsrAllocationID))
{
itemCount++;
}

this.Base.Transactions.Delete(currentItem);

ICAllocationCode code = SelectFrom<ICAllocationCode>.
Where<ICAllocationCode.allocationID.IsEqual<@P.AsInt>>.
View.Select(Base, itemExt.UsrAllocationID);


foreach (ICAllocationAccounts detail in SelectFrom<ICAllocationAccounts>.InnerJoin<ICAllocationCode>.
On<ICAllocationCode.allocationID.IsEqual<ICAllocationAccounts.allocationID>>.
Where<ICAllocationCode.allocationID.IsEqual<@P.AsInt>>.
View.Select(Base, itemExt.UsrAllocationID))
{
itemCounter++;

APTran newRow = (APTran)this.Base.Transactions.Cache.CreateCopy(currentItem);

newRow.LineNbr = null;
newRow.TranID = null;
newRow.NoteID = null;

if (itemCount == itemCounter)
{
newRow.CuryLineAmt = balanceToAllocate;

if (currentItem.CuryDiscAmt != null && currentItem.CuryDiscAmt > 0)
{
newRow.CuryDiscAmt = balanceToAllocateDiscount;
newRow.CuryTranAmt = newRow.CuryLineAmt - newRow.CuryDiscAmt;
}
else
{
newRow.CuryTranAmt = balanceToAllocate;
}
}
else
{
newRow.CuryLineAmt = Math.Round((decimal)((detail.Percent / 100) * currentItem.CuryLineAmt), 2);
balanceToAllocate -= (decimal)newRow.CuryLineAmt;

if (currentItem.CuryDiscAmt != null && currentItem.CuryDiscAmt > 0)
{
newRow.CuryDiscAmt = Math.Round((decimal)((detail.Percent / 100) * currentItem.CuryDiscAmt), 2);
balanceToAllocateDiscount -= (decimal)newRow.CuryDiscAmt;
newRow.CuryTranAmt = newRow.CuryLineAmt - newRow.CuryDiscAmt;
}
else
{
newRow.CuryTranAmt = newRow.CuryLineAmt;
}
}

if (currentItem.Qty != null && currentItem.Qty > 0)
{
newRow.Qty = Math.Round((decimal)((decimal)(detail.Percent / 100) * currentItem.Qty), 2);
}

if (code.DescriptionSource == DescriptionSourceType.FromDocument)
{
newRow.TranDesc = currentItem.TranDesc;
}
else
{
newRow.TranDesc = code.TranDescr;
}

newRow.BranchID = detail.BranchID;

if(newRow.InventoryID == null)
{
newRow.SubID = detail.AccountSubID;
}
else
{
newRow.TranDesc = currentItem.TranDesc;
}

APTranExt itemExt2 = PXCache<APTran>.GetExtension<APTranExt>(newRow);
itemExt2.UsrAllocationIDUsed = itemExt.UsrAllocationID;
itemExt2.UsrAllocationID = null;

this.Base.Transactions.Cache.Update(newRow);
//this.Base.Transactions.Cache.Insert(newRow);
}
}

 

Userlevel 6
Badge +3

@Dmitrii Naumov 

I actually just refactored my graph extension to do what you just showed above.

Thank you so much for this.  I will plagiarize your code if you don’t mind!  

Awesome help when I really needed it!!

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved