Announcements
Hello,
Does anyone know which date in ProdTable is associated with GL Posting Date? Your help would be greatly appreciated.
thanks,
Tremor
*This post is locked for comments
Hi Tremor,
The production order table does not contain a date field for transactions. The posting date is determined at journals and status updates.
Thanks Andre! Is it safe to say that the ones with status of "Ended" would have a posting date in the journals?
You can view the log or all journals to see which date had been used. Also you can browse the inventory transactions and look at the financial date for the "Ended" date.
ProdTable has a field named RealDate which indicates the date the production order was Ended.
I tested the theory that ProdTable.RealDate should be equal to MAX(InventTrans.DateFinancial), and across all 270k ProdTable records in my business database where the order was Ended, they are identical.
Here's a quick query I used to look for cases where they were different, and 0 records returned.
select p.[PARTITION], p.DATAAREAID, p.PRODID, p.REALDATE, MAX(t.DATEFINANCIAL) AS DATEFINANCIAL
from PRODTABLE p
join INVENTTRANSORIGIN o ON o.[PARTITION] = p.[PARTITION] AND o.DATAAREAID = p.DATAAREAID AND o.REFERENCEID = p.PRODID AND o.REFERENCECATEGORY = 2
join INVENTTRANS t ON t.[PARTITION] = o.[PARTITION] AND t.DATAAREAID = o.DATAAREAID AND t.INVENTTRANSORIGIN = o.RECID
where t.STATUSISSUE <= 1 AND t.STATUSRECEIPT <= 1
and p.PRODSTATUS = 7 -- 7=ProdStatus::Ended
group by p.[PARTITION], p.DATAAREAID, p.PRODID, p.REALDATE
having p.REALDATE <> MAX(t.DATEFINANCIAL) -- where different
Thanks Brandon. We are still on AX2012 R1, it is not recognizing column "Partition".
Simply remove the [PARTITION] parts from the ON join clauses, and from the GROUP BY and SELECT clauses.
Thanks Brandon! I also did not get any results! I really appreciate it!
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our community stars!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
