We are looking to find the history for allocations. Scenario -A payable is entered on a particular company and an auto trigger would pull a distribution from a given account and allocate to another. We want to be able to determine what was allocated and what was distributed from a historical perspective over a duration.
*This post is locked for comments
Great -thanks!
The document date is stored in the PM30200 as for historical transactions. Here is an updated to Harry's script
Share
Report
All responses (
Answers (
SELECT GLAD.ACTNUMST ,
GLM.ACTDESCR ,
PM.DOCDATE ,
CASE PMDH.DISTTYPE
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Payable'
WHEN 3 THEN 'Discount Available'
WHEN 4 THEN 'Discount Taken'
WHEN 5 THEN 'Finance Charge'
WHEN 6 THEN 'Purchases'
WHEN 7 THEN 'Trade Disc.'
WHEN 8 THEN 'Misc. Charge'
WHEN 9 THEN 'Freight'
WHEN 10 THEN 'Taxes'
WHEN 11 THEN 'Writeoffs'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'GST Disc'
WHEN 14 THEN 'PPS Amount'
WHEN 16 THEN 'Round'
WHEN 17 THEN 'Realized Gain'
WHEN 18 THEN 'Realized Loss'
WHEN 19 THEN 'Due To'
WHEN 20 THEN 'Due From'
END Distribution_Description ,
*
FROM PM30600 PMDH
INNER JOIN GL00105 GLAD ON PMDH.DSTINDX = GLAD.ACTINDX
LEFT JOIN GL00100 GLM ON GLAD.ACTINDX = GLM.ACTINDX
LEFT OUTER JOIN dbo.PM30200 AS PM ON PM.VCHRNMBR = PMDH.VCHRNMBR
Let us know if you need any further assistance,
Worked great --how about if I need to see the document date for these records (on payables entry screen), which I'm not readily seeing in these tables?
Thanks so much!
Mahmoud is right on with the tables involved. Here is some query language to help you produce the results you need. This query translates the Distribution Type into English and links the table to provide the Account Number and Description. The last line, which I commented out can be used to restrict the results to only Purchases distribution records.
select GLAD.ACTNUMST,GLM.ACTDESCR,
Case PMDH.DISTTYPE when 1 then 'Cash'
when 2 then 'Payable'
when 3 then 'Discount Available'
when 4 then 'Discount Taken'
when 5 then 'Finance Charge'
when 6 then 'Purchases'
when 7 then 'Trade Disc.'
when 8 then 'Misc. Charge'
when 9 then 'Freight'
when 10 then 'Taxes'
when 11 then 'Writeoffs'
when 12 then 'Other'
when 13 then 'GST Disc'
when 14 then 'PPS Amount'
when 16 then 'Round'
when 17 then 'Realized Gain'
when 18 then 'Realized Loss'
when 19 then 'Due To'
when 20 then 'Due From'
End Distribution_Description,
*
from PM30600 PMDH
inner join GL00105 GLAD on PMDH.DSTINDX = GLAD.ACTINDX
Left join GL00100 GLM on GLAD.ACTINDX = GLM.ACTINDX
--where PMDH.DISTTYPE = 6 --To Restrict Distributions to Purchases Only
Payable distribution tables are :
PM30600 -- PM Distribution History File PM10100 -- PM Distribution WORK OPEN PM40103 -- Payables Distribution Type SETP
Your feedback is highly appreciated,
#1
Almas Mahfooz
3
User Group Leader
