Solved

Project Specific Inventory

  • 17 January 2022
  • 13 replies
  • 684 views

Userlevel 1
Badge

I can’t find a report or inquiry that displays project specific inventory quantity. The only time you get to see the quantity for project specific inventory is when you add a line with an inventory item on the sales order, shipment, transfer or issue form, then the information gets displayed in the footer section of the screen as indicated below.

project inventory tracking on the footer of the applicable forms

What table and field name is used to store project specific inventory quantities that’s reserved in a virtual location.

Thanks.

icon

Best answer by mvolshteyn 19 January 2022, 14:16

View original

13 replies

Userlevel 7
Badge +17

Hi @DanWats  Hope you are looking for the below information.

Table Name: INSiteStatus

 

 

Userlevel 1
Badge

Hi Naveen,

Thanks for the response.

I’ve reviewed the suggested table (INSiteStatus) but it is missing the project values and the related reserved quantities associated the warehouse. I expect the table to contain fields for; Site (Warehouse), Location, Project, Task and the Reserved-quantity.

Regards,

Userlevel 7
Badge +17

Hi @DanWats , I’m NOT sure that we can see the QtyAvailable, Qty OnHand with Project ID and Task values. 

If you wanted to see Project fields along with Inventory Transaction you can check the INTran table.

This table will have the information on the each Inventory Transaciton.

 

Userlevel 1
Badge

Hi @Naveen B,

I believe the Project/Task quantity on hand and quantity available is stored somewhere. The INTran table info will not provide the ideal analysis for this requirement. The fact that the highlighted quantities on the screen below get displayed in the footer after the project and task is selected on the item line indicates that the record exists somewhere in one of the tables.

Issue form showing the reserved project quantity (Highlighted)

Will look some more to see what can be found.

Thanks,

Userlevel 5
Badge +3

@DanWats , you can find the description of the whole ‘Project Material Management’ feature in the release notes for 21R2 (http://acumatica-builds.s3.amazonaws.com/builds/21.2/ReleaseNotes/AcumaticaERP_2021R2_ReleaseNotes_full.pdf), Projects and Construction: Inventory Tracking section (starting from p.296). The general description of showing and storing available quantity is described in the Calculation of Item Availability subsection.

Is this information enough or do you need to know the exact dac fields used for showing this availability?

Userlevel 1
Badge

Hi @mvolshteyn,

Thanks for the suggestion.

I read that documentation and have a clear understanding of the feature however, the challenge is with reporting. There should really be a report to show the analysis of the quantity reserved for the projects but it’s not currently available that’s why I still need to know the exact dac fields so that I can design the report or create a generic inquiry that provides this information.

Regards,

Userlevel 5
Badge +3

@DanWats , sure:

these ‘yellow’ quantities are taken from the  PMSIteStatus table record with the

  • Inventory Id, Warehouse Id corresponding to the sales order line
  • Project Id =
    • if the Project Id speicified in SO has the *Inventory Tracking* other than by Location (PMProject.AccountingMode <> ‘L’), this Project Id speicified in SO
    • if the Project Id speicified in SO has the *Inventory Tracking* = by Location, the ‘non-project’ (0)
Userlevel 5
Badge +3

@DanWats , this was the calculation for a *sales order* (for some reason, I assumed the calculation was needed for the SO). For inventory documents , including issue, the calculation is basically the same, but

  • the PMLocationStatus table is used as as availability source it Location is specified in the line
  • the PMLotSerialStatus table is used as as availability source it Lot/Serial Nbr is specified in the line
Userlevel 1
Badge

Hi @mvolshteyn,

That looks like it! Those tables have the quantity details relating to the projects and tasks.

Thank you!

Userlevel 7
Badge +10

For Future References, starting in 2023 R1, the tables mentioned above have been deprecated and replaced.


From Page 56 of the Developer Notes: https://acumatica-builds.s3.amazonaws.com/builds/23.1/ReleaseNotes/AcumaticaERP_2023R1_ReleaseNotes_for_Developers.pdf 
The PMSiteStatus, PMLocationStatus, and PMLotSerialStatus DACs are obsolete and no longer used. Since now the corresponding INSiteStatusByCostCenter, INLocationStatusByCostCenter, INLotSerialStatusByCostCenter DACs reference the INCostCenter DAC, which stores the project key and the project-specific items information. All records from the tables that correspond to the obsolete DACs are migrated to the new tables during upgrade.

Userlevel 4
Badge

Prior to 2023 R1, we have been using the Project Stock inquiry to help see items that are “earmarked”

With the changes @meganfriesen37  pointed out in developer notes, there is no reference to the Project Stock inquiry and the original inquiry gets an error because PMLocation does not exist. Does anyone know if there is a new replacement inquiry for the Project Stock generic inquiry, or will we need to create our own for 2023 R1 and up?

Userlevel 4
Badge

Update - I found this post How to review project-specific inventory quantity and cost | Community (acumatica.com) that already has updated 23R1 inquiries for Project inventory. (Post 71064)

Userlevel 2

For Future References, starting in 2023 R1, the tables mentioned above have been deprecated and replaced.


From Page 56 of the Developer Notes: https://acumatica-builds.s3.amazonaws.com/builds/23.1/ReleaseNotes/AcumaticaERP_2023R1_ReleaseNotes_for_Developers.pdf 
The PMSiteStatus, PMLocationStatus, and PMLotSerialStatus DACs are obsolete and no longer used. Since now the corresponding INSiteStatusByCostCenter, INLocationStatusByCostCenter, INLotSerialStatusByCostCenter DACs reference the INCostCenter DAC, which stores the project key and the project-specific items information. All records from the tables that correspond to the obsolete DACs are migrated to the new tables during upgrade.

Hello Megan,

I am trying to understand the tables you mentioned above, INSiteStatusbyCostCenter and INLocationStatusbyCostCenter, but I am not really sure I understand them. I understand they are Site (warehouse) vs location, however, I am confused as for the dates and the transactions displayed. See below, a comparison of the same item for both tables and questions after.

FYI, both “INTRANSIT” and “WAREHOUSE” are locations in the same site (warehouse).

My questions:

  1. Why the difference in Dates for, what it looks like, the same transaction between the two GI’s (10/23 vs 10/12) ?
  2. Why the difference in Transactions, IN304000 (Transfer) vs PO302000 (PO Receipt) in the two GI’s
  3. Why 3 different rows? It seems they all come from the same form (Transfers) for which I understand the 2 rows together but not the bottom one. I know the transfer was for only 1000, and the 75 were already in inventory from before.  See Inv transaction Details below

Lastly, can you confirm the Cost Center ID (Aka Cost Site) is the unique system # for the combination of WHS/Project/Project Task? 

Thank you so much for the help Megan!

 

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