415
Hi All,
How GP Get all the transaction information on Receivable Summary Inquiry windows.
Can you please provide any table and stored procedures related to this.
Thanks,
Srinivasulun.
*This post is locked for comments
Hi,
victoriayudin.com/.../sql-view-for-current-receivables-aging-detail-in-dynamics-gp
victoriayudin.com/.../sql-view-for-customer-yearly-totals-in-dynamics-gp
victoriayudin.com/.../sql-view-for-current-receivables-aging-in-dynamics-gp
victoriayudin.com/.../receivables-sql-views
Some of the above links help u to find Required Answer,
select
CM.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms,
CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,
case RM.RMDTYPAL
when 1 then 'Sale / Invoice'
when 3 then 'Debit Memo'
when 4 then 'Finance Charge'
when 5 then 'Service Repair'
when 6 then 'Warranty'
when 7 then 'Credit Memo'
when 8 then 'Return'
when 9 then 'Payment'
else 'Other'
end Document_Type,
RM.DOCNUMBR Document_Number,
RM.DOCDATE Document_Date,
RM.DUEDATE Due_Date,
S.LASTPYDT Last_Payment_Date,
case
when RM.RMDTYPAL < 7 then RM.ORTRXAMT
else RM.ORTRXAMT * -1
end Document_Amount,
case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end Unapplied_Amount,
case
when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [Current],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 1 and 30
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [0_to_30_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [31_to_60_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [61_to_90_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) > 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) > 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [91_and_Over]
from RM20101 RM
inner join RM00101 CM
on RM.CUSTNMBR = CM.CUSTNMBR
left outer join RM00103 S
on RM.CUSTNMBR = S.CUSTNMBR
where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
-- add permissions for DYNGRP
GO
grant select on view_Current_Receivables_Aging_Detail to DYNGRP
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,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (