Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Laurie Millington]

Does anyone know of a way that you can identified who applied a cash receipt payment or credit memo to a customer’s account when the transaction is open, posted or in history status?  

I have found this query and it gets me closer but I would still need to see who processed those batch/transactions. Any suggestions? 

SELECT T.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.SHRTNAME Short_Name,
T.DOCDATE Document_Date,
T.GLPOSTDT GL_Posting_Date,
CASE T.RMDTYPAL
WHEN 7 THEN ‘Credit Memo’
WHEN 8 THEN ‘Return’
WHEN 9 THEN ‘Payment’
END AS RM_Doc_Type,
T.BACHNUMB Payment_Batch,
T.docTypeNum Document_Type_and_Number,
T.DOCNUMBR Document_Number,
T.ORTRXAMT Original_Trx_Amount,
T.CURTRXAM Current_Trx_Amount,
T.amountApplied Total_Applied_Amount,
A.APPTOAMT Amount_Applied,
A.APTODCTY Applied_to_Doc_Type,
A.debitType Applied_to_Doc_Type_Name,
A.APTODCNM Applied_to_Doc_Number,
A.APTODCDT Applied_to_Document_Date,
A.ApplyToGLPostDate Applied_to_GL_Posting_Date,
A.DISTKNAM Discount,
A.WROFAMNT Writeoff,
A.DATE1 Apply_Document_Date,
A.GLPOSTDT Apply_GL_Posting_Date,
D.ORTRXAMT Applied_To_Doc_Total,
D.DINVPDOF Applied_To_Date_Paid_Off,
D.CURTRXAM Applied_To_Doc_Unapplied_Amount,
D.CSPORNBR Customer_PO_Number

FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL
WHEN 7 THEN ‘Credit Memo’
WHEN 8 THEN ‘Return’
WHEN 9 THEN
CASE CSHRCTYP
WHEN 0 THEN ‘Payment – Check ‘ +
CASE CHEKNMBR
WHEN ” THEN ”
ELSE ‘#’ + CHEKNMBR
END
WHEN 1 THEN ‘Payment – Cash’
WHEN 2 THEN ‘Payment – Credit Card’
END
END AS docTypeNum,
DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB,
ORTRXAMT – CURTRXAM amountApplied

FROM RM20101
WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)

UNION

SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL
WHEN 7 THEN ‘Credit Memo’
WHEN 8 THEN ‘Return’
WHEN 9 THEN
CASE CSHRCTYP
WHEN 0 THEN ‘Payment – Check ‘ +
CASE CHEKNMBR
WHEN ” THEN ”
ELSE ‘#’ + CHEKNMBR
END
WHEN 1 THEN ‘Payment – Cash’
WHEN 2 THEN ‘Payment – Credit Card’
END
END AS docTypeNum,
DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB,
ORTRXAMT – CURTRXAM amountApplied

FROM RM30101
WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)) T

INNER JOIN RM00101 CM
ON T.CUSTNMBR = CM.CUSTNMBR

INNER JOIN
(SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM,
APFRDCTY,APFRDCNM,
CASE APTODCTY
WHEN 1 THEN ‘Sale / Invoice’
WHEN 2 THEN ‘Scheduled Payment’
WHEN 3 THEN ‘Debit Memo’
WHEN 4 THEN ‘Finance Charge’
WHEN 5 THEN ‘Service Repair’
WHEN 6 THEN ‘Warranty’
END as debitType,
APPTOAMT, ApplyToGLPostDate, APTODCDT, tO2.DISTKNAM,
tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT

FROM RM20201 tO2

INNER JOIN RM20101 tO1
ON tO2.APTODCTY = tO1.RMDTYPAL
AND tO2.APTODCNM = tO1.DOCNUMBR

UNION

SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM,
APFRDCTY, APFRDCNM,
CASE APTODCTY
WHEN 1 THEN ‘Sale / Invoice’
WHEN 2 THEN ‘Scheduled Payment’
WHEN 3 THEN ‘Debit Memo’
WHEN 4 THEN ‘Finance Charge’
WHEN 5 THEN ‘Service Repair’
WHEN 6 THEN ‘Warranty’
END AS debitType,
APPTOAMT, ApplyToGLPostDate, APTODCDT, tH2.DISTKNAM,
tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT
FROM RM30201 tH2

INNER JOIN RM30101 tH1
ON tH2.APTODCTY = tH1.RMDTYPAL
AND tH2.APTODCNM = tH1.DOCNUMBR) A

ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR

INNER JOIN
(SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
CURTRXAM, CSPORNBR
FROM RM20101

UNION

SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF,
CURTRXAM = 0, CSPORNBR
FROM RM30101) D

ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR

——————————
Laurie Millington
Boyer & Associates
Plymouth MN
——————————

[Brenda Zeben]

Laurie – I have a query on table SY05000, which has security information.
That will have a message:  “The XXXXXXXXX record in the Sales Transaction Work file was modified.” where XXXXXXX is your invoice number or your return #.User id, date and time will be in the record.

Not sure if that will help in your situation, but that how I identify the user.

?

——————————
Brenda Zeben
Controller
Imlach Movers, Inc.
Trenton MI
——————————
——————————————-

[Blair Christensen]

I have made a few additions to the query you supplied to add the users.  I also added a little logic to deal with unhandled CASE conditions:

SELECT T.CUSTNMBR as Customer_ID
     , CM.CUSTNAME as Customer_Name
     , CM.SHRTNAME as Customer_Short_Name
     , T.DOCDATE as Document_Date
     , T.GLPOSTDT as GL_Posting_Date
     , CASE T.RMDTYPAL
            WHEN 7 THEN 'Credit Memo'
            WHEN 8 THEN 'Return'
            WHEN 9 THEN 'Payment'
            ELSE 'OTHER'
        END AS RM_Doc_Type
     , T.BACHNUMB as Payment_Batch
     , T.docTypeNum as Document_Type_and_Number
     , T.DOCNUMBR as Document_Number
     , T.ORTRXAMT as Original_Trx_Amount
     , T.CURTRXAM as Current_Trx_Amount
     , T.amountApplied as Total_Applied_Amount
     , A.APPTOAMT as Amount_Applied
     , A.APTODCTY as Applied_to_Doc_Type
     , A.debitType as Applied_to_Doc_Type_Name
     , A.APTODCNM as Applied_to_Doc_Number
     , A.APTODCDT as Applied_to_Document_Date
     , A.ApplyToGLPostDate as Applied_to_GL_Posting_Date
     , A.DISTKNAM as Discount
     , A.WROFAMNT as Writeoff
     , A.DATE1 as Apply_Document_Date
     , A.GLPOSTDT as Apply_GL_Posting_Date
     , A.PSTUSRID as Posting_User
     , D.ORTRXAMT as Applied_To_Doc_Total
     , D.DINVPDOF as Applied_To_Date_Paid_Off
     , D.CURTRXAM as Applied_To_Doc_Unapplied_Amount
     , D.CSPORNBR as Customer_PO_Number
     , d.PSTUSRID as Original_Posting_User
  FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL
             , CASE RMDTYPAL
                    WHEN 7 THEN 'Credit Memo'
                    WHEN 8 THEN 'Return'
                    WHEN 9 THEN
                           CASE CSHRCTYP
                                WHEN 0 THEN 'Payment - Check ' +
                                            CASE CHEKNMBR
                                                 WHEN '' THEN ''
                                                 ELSE '#' + CHEKNMBR
                                             END
                                WHEN 1 THEN 'Payment - Cash'
                                WHEN 2 THEN 'Payment - Credit Card'
                            END ELSE 'OTHER'
                END AS docTypeNum
             , DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB
             , ORTRXAMT - CURTRXAM as amountApplied
          FROM RM20101
          WHERE (RMDTYPAL > 6)
           AND (VOIDSTTS = 0)
          UNION
         SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL
              , CASE RMDTYPAL
                     WHEN 7 THEN 'Credit Memo'
                     WHEN 8 THEN 'Return'
                     WHEN 9 THEN
                            CASE CSHRCTYP
                                 WHEN 0 THEN 'Payment - Check ' +
                                             CASE CHEKNMBR
                                                  WHEN '' THEN ''
                                                  ELSE '#' + CHEKNMBR
                                              END
                                 WHEN 1 THEN 'Payment - Cash'
                                 WHEN 2 THEN 'Payment - Credit Card'
                             END
                     ELSE 'OTHER'
                 END AS docTypeNum
               , DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB
               , ORTRXAMT - CURTRXAM as amountApplied
           FROM RM30101
          WHERE RMDTYPAL > 6
            AND VOIDSTTS = 0 ) T
 INNER JOIN RM00101 CM ON T.CUSTNMBR = CM.CUSTNMBR
 INNER JOIN (
            SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM
                 , APFRDCTY, APFRDCNM
                 , CASE APTODCTY
                        WHEN 1 THEN 'Sale / Invoice'
                        WHEN 2 THEN 'Scheduled Payment'
                        WHEN 3 THEN 'Debit Memo'
                        WHEN 4 THEN 'Finance Charge'
                        WHEN 5 THEN 'Service Repair'
                        WHEN 6 THEN 'Warranty'
                        ELSE 'OTHER'
                    END as debitType
                 , APPTOAMT, ApplyToGLPostDate, APTODCDT, tO2.DISTKNAM
                 , tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT, to1.PSTUSRID
              FROM RM20201 tO2
             INNER JOIN RM20101 tO1 ON tO2.APTODCTY = tO1.RMDTYPAL
                        AND tO2.APTODCNM = tO1.DOCNUMBR
             UNION
            SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM
                 , APFRDCTY, APFRDCNM
                 , CASE APTODCTY
                        WHEN 1 THEN 'Sale / Invoice'
                        WHEN 2 THEN 'Scheduled Payment'
                        WHEN 3 THEN 'Debit Memo'
                        WHEN 4 THEN 'Finance Charge'
                        WHEN 5 THEN 'Service Repair'
                        WHEN 6 THEN 'Warranty'
                        ELSE 'OTHER'
                    END AS debitType
                 , APPTOAMT, ApplyToGLPostDate, APTODCDT, tH2.DISTKNAM
                 , tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT, th1.PSTUSRID
              FROM RM30201 tH2
             INNER JOIN RM30101 tH1 ON tH2.APTODCTY = tH1.RMDTYPAL
                        AND tH2.APTODCNM = tH1.DOCNUMBR
            ) A ON A.APFRDCTY = T.RMDTYPAL
                   AND A.APFRDCNM = T.DOCNUMBR

 INNER JOIN (
            SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF
                 , CURTRXAM, CSPORNBR, PSTUSRID
              FROM RM20101
             UNION
            SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF
                 , 0 as CURTRXAM, CSPORNBR, PSTUSRID
              FROM RM30101
            ) D ON A.APTODCTY = D.RMDTYPAL
                   AND A.APTODCNM = D.DOCNUMBR

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Bruce Strom]

UNION ALL rather than UNION should speed up this query by about 30%, since you do not need to check for duplicates in the UNION.

——————————
Bruce Strom
Programmer Analyst
Associated Grocers of Florida / Supervalu
Sunrise FL
——————————
——————————————-