Open Forum

Expand all | Collapse all

Identify Who Applied Cash to a Customer's Account

  • 1.  Identify Who Applied Cash to a Customer's Account

    Posted 19 days ago
    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
    ------------------------------


  • 2.  RE: Identify Who Applied Cash to a Customer's Account

    Posted 18 days ago
    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
    ------------------------------



  • 3.  RE: Identify Who Applied Cash to a Customer's Account

    TOP CONTRIBUTOR
    Posted 18 days ago
    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
    ------------------------------



  • 4.  RE: Identify Who Applied Cash to a Customer's Account

    TOP CONTRIBUTOR
    Posted 16 days ago
    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
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role