TIME ACCRUALS-TAEMPTRANS

Hi bernfc10,

It sounds like you are looking for a way to do a dynamic query directly, as you could with a SQL query. Unfortunately, I don't believe this is possible using the tools we have available.

If I'm reading your question correctly, you are looking for the most recent Tran Type 21/22 transaction for any employee in each employee group - is that correct? In that case, I think you are going to have to build an array in working storage which is keyed on Employee Group and holds the TAEMPTRANS keys, and then query TAEMPTRANS and cycle through the records to determine if the record should be written to the array (overwriting the existing record) or not. Once you've gone through all the records, pull the data back from the array to query only the records you really wanted.

It's a little ugly, but we don't really have a way to do a more direct database query. Maybe someone else has a better idea. I hope so, as it would be very handy to know.

Hi Woozy, you are correct. I am looking for the most recent tra type 21/22 transaction for a specific employee in a specific employee group. I believe the tran date should determine which record would be the most recent one so as you suggested, I might have to build a WS table to store. I was reading about accessing records using the aggreate ragne routines (max) but I am not an experience programmer. Thanks a lot for your prompted response and suggestion, greatly appreciated!
My Profile
Send Message:






05/30/2012 09:08 AM

Quote
Reply
Alert

I went back to the book and reviewed those aggregate functions. To be honest, I've never used them before. After reading about them, it looks to me like they return the aggregate values, but not the record(s) that match those values. If you just wanted the date of the latest record, then maybe those would work, though you'd need to look for the 21/22 tran types, so I'm not sure how well that would work. Also, I suppose you could take the date and throw it back against a "standard" 850-FIND-NLT to retrieve the records.

Unfortunately, I think you are just going to have to experiment and see what happens. I have to do that a lot.

Sorry I'm not much help. Good Luck - I hope you are able to get what you need.

I am actually going to go with your suggestion. I am building a WS table order by date. The only problem is that I have never used API database routines. Here is what I have coded but I am not sure if it would get everything or just record type 21 and 22.  

           MOVE LPMST-TEM-COMPANY        TO DB-COMPANY.
           MOVE LPMST-TEM-EMPLOYEE       TO DB-EMPLOYEE.
           MOVE LPMST-TEM-PLAN                  TO DB-PLAN.
           MOVE LPMST-TEM-EMPLOYEE-GROUP TO DB-EMPLOYEE-GROUP.
           MOVE LPMST-TEM-POSITION          TO DB-POSITION.
           MOVE SPACES                                   TO FILTER-STRING.
           STRING "((TTR-TRAN-TYPE = ?) OR"
                            "(TTR-TRAN-TYPE = ?)) "    
           DELIMITED BY SIZE INTO FILTER-STRING.
           PERFORM 890-CREATE-FILTER.
           MOVE TTRSET1-POSITION       TO WS-DB-BEG-RNG.
           PERFORM
              UNTIL (TAEMPTRANS-NOTFOUND)
              MOVE TO WS TABLE
              PERFORM 860-FIND-NXTRNG-TTRSET1
           END-PERFORM.
          
or

           MOVE LPMST-TEM-COMPANY        TO DB-COMPANY.
           MOVE LPMST-TEM-EMPLOYEE       TO DB-EMPLOYEE.
           MOVE LPMST-TEM-PLAN           TO DB-PLAN.
           MOVE LPMST-TEM-EMPLOYEE-GROUP TO DB-EMPLOYEE-GROUP.
           MOVE LPMST-TEM-POSITION       TO DB-POSITION.
           PERFORM 850-FIND-NLT-TTRSET1.
           PERFORM
               UNTIL (TAEMPTRANS-NOTFOUND)
               OR    (TTR-EMPLOYEE       NOT = DB-EMPLOYEE)
               OR    (TTR-PLAN           NOT = DB-PLAN)
               OR    (TTR-EMPLOYEE-GROUP NOT = DB-EMPLOYEE-GROUP)
               OR    (TTR-COMPANY        NOT = DB-COMPANY
               IF(TTR-TRAN-TYPE = '21')
               OR(TTR-TRAN-TYPE = '22')
                   DISPLAY "TRAN TYPE: " TTR-TRAN-TYPE
                   DISPLAY "TRAN DATE: " TTR-TRAN-DATE
               END-IF
               PERFORM 860-FIND-NEXT-TTRSET1
           END-PERFORM.

You've got it, with a couple of suggestions.

First, I'm thinking that you may want to use TTRSET3 rather than TTRSET1, since it's keys are (in this order) Company, Plan, EmployeeGroup, Employee, Position, Date, SeqNbr.

Second, I think you should probably include a date limiter (Maybe within the previous month or 6 months or something?)

In the samples below, wehre I have put "{LOGIC}, you'd need to take TTR-EMPLOYEE-GROUP and toss it against your WS array to find the matching EMPLOYEE-GROUP record, then check the TTR-DATE value against the stored WS DATE value to see if the current date is greater, and if so overwrite.

The filter query probably would be best, though they can be tricky.  Note that the "?" values are populated in order using the 890-SET statements that follow.   I think it would need to be something like this (you'll need to test to be sure):

           STRING "(TTR-DATE >= ? AND ((TTR-TRAN-TYPE = ?) OR"
                            "(TTR-TRAN-TYPE = ?))) "    
                            DELIMITED BY SIZE INTO FILTER-STRING.

           PERFORM 890-CREATE-FILTER.
           MOVE {your low-end date}        TO DATETIME-FILTER-VALUE.
           PERFORM 890-SET-DATETIME-FILTER-VALUE.
           MOVE 21                         TO NUMERIC-FILTER-VALUE.
           PERFORM 890-SET-ALPHANUM-FILTER-VALUE.
           MOVE 22                         TO NUMERIC-FILTER-VALUE.
           PERFORM 890-SET-ALPHANUM-FILTER-VALUE.

           MOVE LPMST-TEM-COMPANY          TO DB-COMPANY.
           MOVE LPMST-TEM-PLAN             TO DB-PLAN.
           MOVE TTRSET3-PLAN               TO WS-DB-BEG-RNG.

           PERFORM 850-FILTER-BEGRNG-TTRSET3.

           PERFORM
           UNTIL (TAEMPTRANS-NOTFOUND)
           OR    (TTR-COMPANY        NOT = LPMST-TEM-COMPANY)
           OR    (TTR-PLAN           NOT = LPMST-TEM-PLAN)
               {LOGIC}   
               PERFORM 860-FIND-NXTRNG-TTRSET3
           END-PERFORM.


If you were going to use the second option, it would spin through all employees in COMPANY and PLAN, by employee group and look at every record in TAEMPTRANS.  It would take time, but it's pretty bullet-proof.  So I'd do something like this:

           MOVE LPMST-TEM-COMPANY        TO DB-COMPANY.
           MOVE LPMST-TEM-PLAN           TO DB-PLAN.
           MOVE SPACES                   TO DB-EMPLOYEE-GROUP.
           MOVE ZEROES                   TO DB-EMPLOYEE.
           MOVE SPACES                   TO DB-POSITION.
           PERFORM 850-FIND-NLT-TTRSET3.
           PERFORM
               UNTIL (TAEMPTRANS-NOTFOUND)
               OR    (TTR-COMPANY        NOT = LPMST-TEM-COMPANY)
               OR    (TTR-PLAN           NOT = LPMST-TEM-PLAN)
               IF(TTR-TRAN-TYPE = '21')
               OR(TTR-TRAN-TYPE = '22')
                   {LOGIC}
               END-IF
               PERFORM 860-FIND-NEXT-TTRSET3
           END-PERFORM.

I hope this helps.  Good Luck.

Hi Kelly, your help is greatly appreciated!!! I will code as suggested and will test the code and let you know once I get the expected results. Thank you very much for your time and effort.

There records in the taemptrans table but I am getting a not found and can't figure out why. Here is my code:

MOVE LPMST-TEM-COMPANY        TO DB-COMPANY.
 16410     MOVE LPMST-TEM-EMPLOYEE       TO DB-EMPLOYEE.
 16411     MOVE LPMST-TEM-PLAN           TO DB-PLAN.
 16412     MOVE LPMST-TEM-EMPLOYEE-GROUP TO DB-EMPLOYEE-GROUP.
 16413     MOVE LPMST-TEM-POSITION       TO DB-POSITION.
 16414     MOVE 5                        TO WS-DB-BEG-RNG.
 16415     MOVE SPACES                   TO FILTER-STRING.
 16416     MOVE "(TTR-TRAN-TYPE = '21')" TO FILTER-STRING.
 16417     PERFORM 890-CREATE-FILTER.
 16418     PERFORM 850-FILTER-BEGRNG-TTRSET3.
 16419     PERFORM
               UNTIL (TAEMPTRANS-NOTFOUND)
               OR    (TTR-COMPANY        NOT = LPMST-TEM-COMPANY)
               OR    (TTR-PLAN           NOT = LPMST-TEM-PLAN)
                   DISPLAY "TAEMPTRANS TRAN 21 " TTR-DATE
                   IF(TTR-DATE > LPC-PREV-TTR-DATE)
                       MOVE TTR-TA-HOURS TO LPC-ADJ-AWARD
                       MOVE TTR-DATE  TO LPC-PREV-TTR-DATE
                   END-IF
               PERFORM 860-FIND-NXTRNG-TTRSET3
           END-PERFORM.

I don't think you can just do a "MOVE "(TTR-TRAN-TYPE = '21')" TO FILTER STRING" command.  I think you have to create the filter string (with the question marks in the right place), then do the 890-CREATE-FILTER, then populate the filter values.  After you do all that, then you perform the find.  It looks like you are trying to shortcut something that you can't.  Also, I think you have to include TTR-DATE in your filter, because it is part of the key.

I'm pretty sure it has to be pretty similar to this:

           STRING "(TTR-DATE >= ? AND ((TTR-TRAN-TYPE = ?) OR"
                            "(TTR-TRAN-TYPE = ?))) "    
                            DELIMITED BY SIZE INTO FILTER-STRING.

           PERFORM 890-CREATE-FILTER.
           MOVE {your low-end date}        TO DATETIME-FILTER-VALUE.
           PERFORM 890-SET-DATETIME-FILTER-VALUE.
           MOVE 21                         TO NUMERIC-FILTER-VALUE.
           PERFORM 890-SET-ALPHANUM-FILTER-VALUE.
           MOVE 22                         TO NUMERIC-FILTER-VALUE.
           PERFORM 890-SET-ALPHANUM-FILTER-VALUE.

           MOVE LPMST-TEM-COMPANY          TO DB-COMPANY.
           MOVE LPMST-TEM-PLAN             TO DB-PLAN.
           MOVE TTRSET3-PLAN               TO WS-DB-BEG-RNG.

           PERFORM 850-FILTER-BEGRNG-TTRSET3.

           PERFORM
           UNTIL (TAEMPTRANS-NOTFOUND)
           OR    (TTR-COMPANY        NOT = LPMST-TEM-COMPANY)
           OR    (TTR-PLAN           NOT = LPMST-TEM-PLAN)
               {LOGIC}   
               PERFORM 860-FIND-NXTRNG-TTRSET3
           END-PERFORM.

Right, I will modify and test now that I finally have test records. Thanks a lot for your time and effort