Adding Backordered Items to Invoice

  • 8 December 2020
  • 19 replies
  • 1348 views

Userlevel 1

Does anyone have any ideas on how to add backordered items to an Invoice? These would be zero dollar lines for visibility purposes only.

We have the Add Zero Lines for Items Not in Stock checkbox checked on the Sales Orders Preferences (SO101000) screen so we get the following behavior:

 

1. Create a Sales Order where the first 2 lines are available, but the 3rd line is not available:

 

2. Create a Shipment and all three lines are carried onto the Shipment, even though the 3rd line is not shipped:

 

3. Create an Invoice and only the first two lines are carried over. The 3rd line disappears since it didn’t ship:

 


19 replies

Userlevel 7
Badge +10

I’ve been receiving private messages and e-mails about this since mentioning that I had built something similar in the past, so I decided to create a new, simplified example that works from the Acumatica demo database. Here it is for the community:

The main invoice form and the subreport can be found at this link: https://velixo-my.sharepoint.com/:f:/p/gabriel/Eq3FeY6NXUlOqw3jbqDGZ70B1EizeTcGu7nqpka0tDd3oA?e=wEEdWm

Userlevel 7
Badge +15

Hello, followers of this post.  

I noticed that an idea was added to Community to ask to support this idea generically in the product.  However, this idea has just 4 votes so far.  Just curious as to how many customers/partners out there are implementing Gabriel’s idea mentioned above.

 

Hi @Dana Moffat 

I have an implementation currently where the customer just asked for this yesterday. I believe there is a need for this. 

Userlevel 1

I noticed there was discussion above about why this is needed on the invoice.  

Can followers on this post, elaborate on who is looking at this information on the invoice and how/why it’s needed there.

-Dana

 

Yes, from a AP receiving standpoint it takes the guess work of what is or isn't being shipped. When my vendors don’t include it I have to find what is missing before receiving. Inline is a treat, but sub reports work too.

From a retail customer standpoint it communicates what wasn't shipped. This may prevent a call to the merchant. Having more information on one document simplifies and adds options for acumatica customers. We don’t use the shipment documents for customers (so642000). So being able to display it on an invoice would benefit our workflow.

Userlevel 2
Badge

I’ve been receiving private messages and e-mails about this since mentioning that I had built something similar in the past, so I decided to create a new, simplified example that works from the Acumatica demo database. Here it is for the community:

The main invoice form and the subreport can be found at this link: https://velixo-my.sharepoint.com/:f:/p/gabriel/Eq3FeY6NXUlOqw3jbqDGZ70B1EizeTcGu7nqpka0tDd3oA?e=wEEdWm

Hi @Gabriel Michaud 

Sorry my mistakes, I can access your sharepoint and testing it right now. 

Thank you

Daru

Userlevel 7
Badge +2

Hello, followers of this post.  

I noticed that an idea was added to Community to ask to support this idea generically in the product.  However, this idea has just 4 votes so far.  Just curious as to how many customers/partners out there are implementing Gabriel’s idea mentioned above.

 

Userlevel 7
Badge +2

I noticed there was discussion above about why this is needed on the invoice.  

Can followers on this post, elaborate on who is looking at this information on the invoice and how/why it’s needed there.

-Dana

 

Userlevel 4
Badge

In addition to some customers wanting to see what is still on backorder / unshipped on the current invoice, sometimes they would also like to see an ETA of when the backordered product might be expected. This could be from SO Line Ship On date if it is maintained to accurately display likely ship date. Seeing that there are back ordered lines and when they may be expected can save a lot of calls to customer service.

Userlevel 7
Badge +10

Hi @timrodman,

 

I’ve seen what you’re looking for in a prior implementation; the backordered items are added as a subreport at the end of the invoice, and we simply look at open items in the SO Order:

Send me a private message and i’ll share with you.

Userlevel 4
Badge +1

I noticed there was discussion above about why this is needed on the invoice.  

Can followers on this post, elaborate on who is looking at this information on the invoice and how/why it’s needed there.

-Dana

 

Typical customers like to see this. They view the transaction rom the lens of their purchase order/our sales order, and each invoice shoudl indicate the entire transaction status. They don;t care that we as a vendor compelted the sale in multiple invoices rather than 1.

Userlevel 2
Badge

We achieved something similar to this by creating a SQL view that brings the data together, and then wrapped a DAC around that view, and then joined the DAC into the report.

Customization project is attached.
 

Here’s the SQL view (at the mercy of the code formatter!):
 

create or alter view AISalesInvoice as

select l.CompanyID
, os.InvoiceType, os.InvoiceNbr, l.lineNbr as SOLineLineNbr -- composite key for view/DAC
, l.OrderType, l.OrderNbr, l.SortOrder
, sl.ShipmentType, sl.ShipmentNbr
, ar.TranType, ar.RefNbr, ar.LineNbr as ARTranLineNbr
from SOLine l
left join SOOrderShipment os on os.OrderNbr = l.OrderNbr
and os.OrderType = l.OrderType
and os.CompanyID = l.CompanyID
left join SOShipLine sl on sl.OrigOrderType = l.OrderType
and sl.OrigOrderNbr = l.OrderNbr
and sl.OrigLineNbr = l.LineNbr
and sl.ShipmentNbr = os.ShipmentNbr
and sl.ShipmentType = os.ShipmentType
and sl.CompanyID = l.CompanyID
left join ARTran ar on ar.RefNbr = os.InvoiceNbr
and ar.TranType = os.InvoiceType
and ar.CompanyID = os.CompanyID
and ar.SOShipmentNbr = sl.ShipmentNbr
and ar.SOShipmentLineNbr = sl.LineNbr
where 1=1
and l.OpenQty + isnull(ar.qty,0) > 0 -- forces fully shipped items from previous invoices to drop off list

Here’s the code for the DAC (at the mercy of the code formatter!):

using System;
using PX.Data;

namespace AISalesInvoice.View
{
[Serializable]
[PXCacheName("AISalesInvoice")]
public class AISalesInvoice : IBqlTable
{
#region InvoiceType
[PXDBString(3, IsFixed = true, InputMask = "", IsKey = true)]
[PXUIField(DisplayName = "Invoice Type")]
public virtual string InvoiceType { get; set; }
public abstract class invoiceType : PX.Data.BQL.BqlString.Field<invoiceType> { }
#endregion

#region InvoiceNbr
[PXDBString(15, IsUnicode = true, InputMask = "", IsKey = true)]
[PXUIField(DisplayName = "Invoice Nbr")]
public virtual string InvoiceNbr { get; set; }
public abstract class invoiceNbr : PX.Data.BQL.BqlString.Field<invoiceNbr> { }
#endregion

#region SOLineLineNbr
[PXDBInt(IsKey = true)]
[PXUIField(DisplayName = "SOLine Line Nbr")]
public virtual int? SOLineLineNbr { get; set; }
public abstract class sOLineLineNbr : PX.Data.BQL.BqlInt.Field<sOLineLineNbr> { }
#endregion

#region OrderType
[PXDBString(2, IsFixed = true, InputMask = "")]
[PXUIField(DisplayName = "Order Type")]
public virtual string OrderType { get; set; }
public abstract class orderType : PX.Data.BQL.BqlString.Field<orderType> { }
#endregion

#region OrderNbr
[PXDBString(15, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Order Nbr")]
public virtual string OrderNbr { get; set; }
public abstract class orderNbr : PX.Data.BQL.BqlString.Field<orderNbr> { }
#endregion

#region SortOrder
[PXDBInt()]
[PXUIField(DisplayName = "Sort Order")]
public virtual int? SortOrder { get; set; }
public abstract class sortOrder : PX.Data.BQL.BqlInt.Field<sortOrder> { }
#endregion

#region ShipmentType
[PXDBString(1, IsFixed = true, InputMask = "")]
[PXUIField(DisplayName = "Shipment Type")]
public virtual string ShipmentType { get; set; }
public abstract class shipmentType : PX.Data.BQL.BqlString.Field<shipmentType> { }
#endregion

#region ShipmentNbr
[PXDBString(15, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Shipment Nbr")]
public virtual string ShipmentNbr { get; set; }
public abstract class shipmentNbr : PX.Data.BQL.BqlString.Field<shipmentNbr> { }
#endregion

#region TranType
[PXDBString(3, IsFixed = true, InputMask = "")]
[PXUIField(DisplayName = "Tran Type")]
public virtual string TranType { get; set; }
public abstract class tranType : PX.Data.BQL.BqlString.Field<tranType> { }
#endregion

#region RefNbr
[PXDBString(15, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Ref Nbr")]
public virtual string RefNbr { get; set; }
public abstract class refNbr : PX.Data.BQL.BqlString.Field<refNbr> { }
#endregion

#region ARTranLineNbr
[PXDBInt()]
[PXUIField(DisplayName = "ARTran Line Nbr")]
public virtual int? ARTranLineNbr { get; set; }
public abstract class aRTranLineNbr : PX.Data.BQL.BqlInt.Field<aRTranLineNbr> { }
#endregion
}
}

 

Userlevel 1

Hi,

→ Created an order with 3 line items (Stock available for 1st and 2nd line items, but for 3rd product)

→ Created the Shipment, Confirmed the Shipment and Prepared the Invoice, then Invoice is created for 2 line items since 3rd product not yet shipped.

 

 

→ In new tab, Released Inventory Receipt for 3rd product

→ Navigated to Sales Order and create, confirm the shipment for 3rd line item

→ Navigated to Same invoice which is in Balanced Status with 2 line items, click on Add Order, this time new Shipment number shown on the popup. Select the Shipment and click on Add & Close

→ 3rd line item is added to the Same invoice.

 

Hope this address the question.

 

Best Regards,

Bala

Badge

The 3rd line can be added, but the quantity would need to show as 0.  When you process the shipment to invoice there is an option to “Add SO line” (on 2020R2 anyways).  You can add the line from the original Sales Order, however I believe Acumatica will still try to create a shipment if there is a qty there.  We have used this just to show there was items not shipped with the order. 

Userlevel 7
Badge +11

Hi @timrodman 

You may explore this option. I have not tried the complete process yet.

  • Create a GI to pull the backorder line with zero quantity  from Shipment with Invoice number using SOShipLine and SOOrderShipment table/DAC.
  • Create an import scenario to insert line to an invoice
  • Set business event using above GI and to trigger the import scenario using GI output
  • Business event should insert the item and description with zero quantity to the invoice using the import scenario
  • As I have not tested this yet, the Stock item code and description may be added as description line, as one may not be able to add the Stock item directly on Invoice in this case.
  • Since the information is required for visibility only, inserting a description line with Stock item code and description, quantity may help

Hope this may help,

Regards,

 

Userlevel 1

Thanks @Gabriel Michaud. A Subreport is a good idea. My only concern is if it handles page breaks well. The last time I tried a Subreport across a page break, it got cutoff. If your solution handles page breaks, I’d love to see it. I’ll PM you.

Thanks @balakrishnak12 for the idea with screenshots. Unfortunately it won’t work for me because the Invoice needs to go out before the the 3rd line is received.

Userlevel 2
Badge

I have just come across a client who requires this and have solved it for the time being by adding a subreport, which isn’t ideal. It’s baffling to me that Acumatica doesn’t already support this, as it is common in distribution companies. I will add it as an Idea in the Ideas area.

Userlevel 4
Badge +2

I think the reason it is not as common is that Invoices go out to accounting departments.  These people are less concerned about what else is coming in.

Typically the warehouse receiver cares which is why we have the shipment confirmation (packing slip)

Userlevel 4
Badge +2

Also what if there are partials.  You’d have duplicate lines items.  I.E. Item ABC order qty 10, shipped qty 5, BO 5.

 

Invoice shows Item ABC once for invoicing 5

and then the subreport will show 5 back ordered.

 

Also,

Subreport group header section you can set keep together parameter to false to help with proper page break

Userlevel 3
Badge

Note on the above solution:  This was designed for a scenario where each invoice bills a single shipment.  Multiple shipments may create duplicate lines on the invoice form output.  

Contact @JKurtz29 or me for more info on the invoice form itself.

Hello, 

 

does anyone know how to take back ordered items on a shipment report and pull that onto the sales order invoice? Thank you in advance. 

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