Skip to main content
Solved

How to get to Reference Nbr Data in Inventory Allocation Details for Reports and other Inquiries


Forum|alt.badge.img+1

Hi All,

I am currently working on an Inventory Report and need to access the underlying Data for Inventory Allocation Details.

I am 95% there by using INSiteStatus and INItemPlan Tables. But I am missing a crucial piece and that is the Reference Nbr:

 

Anybody have an idea on where this is stored/how I can access this?

The Document Type/Allocation Type, Allocation Date etc are all stored in INItemPlan but I cannot seem to find the actual Source Document or the reference number related to it. This would be crucial for this Report.

Best answer by krausef77

Thanks for the detailed response but the String value is actually store in AMRP Tables available to join by Inventory ID, Site ID, etc.

So all good but it took a little bit to track these down.

View original
Did this topic help you find an answer to your question?

7 replies

Forum|alt.badge.img+1
  • 24 replies
  • July 2, 2024

I may be misunderstanding your question but those are Production Order numbers and the “0020” is referring to the Operation number from the BOM. You should be able to view Production Order Maintenance and find those orders. Screen AM2015PL. Within the Production Order there is a References tab which tells you where the inventory source is being driven from (default BOM).


Kandy Beatty
Captain II
Forum|alt.badge.img+16
  • Captain II
  • 2137 replies
  • July 2, 2024

Hi @krausef77 

I’m sure you know this but on the screen, you can hit customization and inspect element:

 

It looks like this is pulling from the InventoryAllocDetEnqResult

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro I
  • 174 replies
  • July 2, 2024
AMorey51 wrote:

I may be misunderstanding your question but those are Production Order numbers and the “0020” is referring to the Operation number from the BOM. You should be able to view Production Order Maintenance and find those orders. Screen AM2015PL. Within the Production Order there is a References tab which tells you where the inventory source is being driven from (default BOM).

I know that, I was looking for the actual Data Table as you cannot join directly on any of this.

 

Turns out you can join via Plan ID into some of the Material Planning Tables which contains that info.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro I
  • 174 replies
  • July 2, 2024
Kandy Beatty wrote:

Hi @krausef77 

I’m sure you know this but on the screen, you can hit customization and inspect element:

 

It looks like this is pulling from the InventoryAllocDetEnqResult

 

I am aware of that. This is the issue that this DAC Schema is a virtual one and doesn’t really store any info but is populated once an Inventory ID is selected. 
AMRP Tables is where that info actually resides.

 


brendanhennelly02
Acumatica Employee

The data as you see it for Related Documents are usually not stored anywhere as it relates to the string value shown in the UI. What you will need is a field on a DAC which will convert the NoteID (GUID) of the document to the string key values unless you want to link each key table seperatly. Here are the two options:

[Option 1 - Customization for RefNoteID]

One example of this is AMProdEvnt.RefNoteID or you can try to search the source for other fields with “RefNoteID” in the name as they usually implement the same logic. The use of PXRefNoteAttribute is the important part here. The part I am unsure of is if this will work in a report. If you want to test before getting into custom DAC code you can add AMProdEvnt to a report and see if the RefNoteID field returns the correct values.

 

Here is a working example in a GI (didn’t test in a report). You can include this DAC in a customization and then see how well it works in your report. Use INItemPlanRefNbr DAC in place of INItemPlan and RefNoteID is the field which will show your results you are looking for.

 

using System;
using PX.Data;

namespace PX.Objects.IN
{
	[PXCacheName("IN Item Plan with Related Document")]
	public class INItemPlanRefNbr : INItemPlan
	{
		#region RefNoteID

		[PXUIField(DisplayName = "Related Document", Enabled = false)]
		[PXRefNote]
		public override Guid? RefNoteID
		{
			get
			{
				return this._RefNoteID;
			}
			set
			{
				this._RefNoteID = value;
			}
		}
		#endregion

		public class PXRefNoteAttribute : PX.Data.PXRefNoteAttribute
		{
			public PXRefNoteAttribute()
				: base()
			{
			}

			public class PXLinkState : PXStringState
			{
				protected object[] _keys;
				protected Type _target;

				public object[] keys
				{
					get { return _keys; }
				}

				public Type target
				{
					get { return _target; }
				}

				public PXLinkState(object value)
					: base(value)
				{
				}

				public static PXFieldState CreateInstance(object value, Type target, object[] keys)
				{
					PXLinkState state = value as PXLinkState;
					if (state == null)
					{
						PXFieldState field = value as PXFieldState;
						if (field != null && field.DataType != typeof(object) && field.DataType != typeof(string))
						{
							return field;
						}
						state = new PXLinkState(value);
					}
					if (target != null)
					{
						state._target = target;
					}
					if (keys != null)
					{
						state._keys = keys;
					}

					return state;
				}
			}
		}
	}
}

 

 

[Option 2 - Join each table on RefNoteID to key table NoteID]

If you are just looking to join only the production data you can join to the NoteID.

INItemPlan.RefNoteID → AMProdItem.NoteID

INItemPlan.RefNoteID → AMProdOper.NoteID 

Similar to other tables like sales order and purchase order:

INItemPlan.RefNoteID → SOOrder.NoteID

Then you can simply show the values from these tables if you wish. This is less of a generic way as using option 1 string values but could also work as a way to not have to code a customization.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro I
  • 174 replies
  • Answer
  • July 3, 2024

Thanks for the detailed response but the String value is actually store in AMRP Tables available to join by Inventory ID, Site ID, etc.

So all good but it took a little bit to track these down.


brendanhennelly02
Acumatica Employee

We did in newer versions start to store the string value for performance reasons in MRP tables. Just keep in mind any transactions generated after the MRP run will not show values in your report.

 

Example MRP starts at 6am and finishes at 6:30am. Most of the data is captured up front so any orders generated after 6am will not have an entry in the MRP tables. Also, when MRP is running there is no data in those tables. Just something to be aware of.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings