Share
ReportHello,
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
You’re offline. This is a read only version of the page.
2,135
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
Thanks Brandon! I also did not get any results! I really appreciate it!
Simply remove the [PARTITION] parts from the ON join clauses, and from the GROUP BY and SELECT clauses.
Thanks Brandon. We are still on AX2012 R1, it is not recognizing column "Partition".
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
ProdTable has a field named RealDate which indicates the date the production order was Ended.
Hi Tremor,
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.
Thanks Andre! Is it safe to say that the ones with status of "Ended" would have a posting date in the journals?
Hi Tremor,
The production order table does not contain a date field for transactions. The posting date is determined at journals and status updates.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (