5 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    2 files 1 report

    JPR _

      v 9.01 Pro

       

      I have one of two files that can be created depending on the type of transaction the program receives.  The files are listed below -

       

      1.

                                           Somewhere Bank & Trust Co                                                                               

      01:03:04 PM, Feb 4, 2008     Summary Remittance Report     Page 1                                                                               

      My Company Inc                                                                               

      -


                         

                                               ACH INFORMATION                                                  

               -


                         

               Receiver Name   : My Company Inc        Originator Name : HelloMe                        

               Eff. Entry Date (Scheduled Payment Date)   : Feb 05, 08                                

               Settlement Date (Actual Payment Date)      : Feb 05, 08       

               Amount              : $5,000.00                                                                    

               Receiver Account : 9765230             Trace Number : 200024250119   

               Trans Type : Automated Demand Deposit  Standard Entry Class Code: CCD                                                                               

      -


                         

                                             ADDENDA RECORD(S)                                                  

               -


                         

               Electronic Funds Transfer ID Number: 221435

       

       

       

      2.

                                           Somewhere Bank & Trust Co                                                                               

      05:46:11 PM, Feb 5, 2008     Summary Remittance Report     Page 1                                                                               

      My Company Inc                                                                               

      -


                     

                                               ACH INFORMATION                                              

               -


                     

               Receiver Name   : My Company Inc        Originator Name : HelloYou Co., Inc

               Eff. Entry Date (Scheduled Payment Date)   : Feb 6, 08                                       

               Settlement Date (Actual Payment Date)      : Feb 6, 08                                       

               Amount            : $21,000.00                                                               

               Receiver Account : 1234567             Trace Number : 091036128400017             

               Trans Type : Automated Demand Deposit  Standard Entry Class Code: CTX                                                                               

      -


                     

                                            REMITTANCE INFORMATION                                          

               -


                                                                                       

      --- Remittance Advice Accounts Receivable Open Item Reference ---                       

               Seller's Invoice Number  : CR4499      Payment Action Code      :                          

               Amount Paid              : -$10.03        Amount of Invoice        : $0.00

               Amount of Discount    : $0.00                                                             

               Invoice                  : Jan 31, 08                                                        

               Time                     :                                                                   

               Time Code              :                                                                               

      Both files will be named the same and have the same type of information in the first 23 lines.  It is the 24 line that I need to identify within the report to see what filter I need to apply, either for an Addenda report or a Remittance report.

       

      Can I do this all within one report run?

        • 2 files 1 report
          Grant Perkins

          JPR,

           

          When you say the FILES have the same name do you mean they actually have the same file name (2 files, same name), a filename does not tell you which sort it is (but the name will not be duplicated) or they have the same TITLE, as per your examples)

           

          Second question.

           

          For the shorter of the two records - I assume the ADDENDA RECORDS will be the shorter - how many row, populated or blank are available after  the

           

          -


                             

                                                 ADDENDA RECORD(S)                                                  

                   -


           

          heading before the next record starts? Would there be enough to fit in all of the rows for a                                        REMITTANCE INFORMATION section (10 rows max?).

           

          I'm fairly sure the answer to your question is YES but the details of the format of the report will have to guide us about the best methods to use.

           

          There seem to be several possibilities.

           

           

          Grant

            • 2 files 1 report
              JPR _

              Grant,

               

              To answer your first question - the file name will always be the same (filename.txt), but the content of the file will either contain addenda records or remittance records, but not both.

               

              The ADDENDA RECORDS are listed one to a page while the REMITTANCE RECORDS are listed one after another.

               

              I hope I understood and answered your questions correctly.

               

              JPR

                • 2 files 1 report
                  Grant Perkins

                  Hi JPR,

                   

                  I had wondered it you needed to process both types of file at the same time in a group of files but from you file name answer I take it the processing would be one file at a time (named filename.txt) but it could be either type of report. For the Remittance records is the format like this:

                   

                  (Note  - If I try to post quoting the original format even edited, I exceed the number of character allowed so I have simplified it - hope the question still makes sense.)

                   

                  Is the format:

                  -


                  ACH

                  -


                   

                  -


                  Remittance

                  -


                   

                  Details of Remittance.

                   

                   

                  -


                  ACH

                  -


                   

                  -


                  Remittance

                  -


                   

                  Details of Remittance.

                   

                   

                   

                  OR

                   

                  ---

                  ACH

                  -


                   

                  -


                  Remittance

                  -


                   

                  Details of Remittance.

                   

                   

                  -


                  Remittance

                  -


                   

                  Details of Remittance.

                   

                   

                  OR

                   

                  --

                  ACH

                  -


                   

                  -


                  Remittance

                  -


                   

                  Details of Remittance.

                   

                    Details of Remittance.

                   

                  Details of Remittance

                   

                   

                   

                   

                  Or perhaps something else?

                   

                   

                  Each option offers different possibilities (or limitations in the last example) for deciding what to use as a Detail record sample and how to trap it. In turn that will influence the options for extracting both data layout types using the same model.

                   

                  Identification of either Addenda or Remittance based on line 24 is not looking like too much of a problem but extracting the detail is a little more complicated if that report lists a number of transactions under a single Remittance Information heading since the detail template trap would need careful consideration. Sorry about more questions but getting a full 'picture' of things is especially important for this type of exercise if results are to be reliable.

                   

                  HTH

                  Grant

                    • 2 files 1 report
                      JPR _

                      Hi Grant,

                       

                      It is -

                       

                      --

                      ACH

                      -


                       

                      -


                      Remittance

                      -


                       

                      Details of Remittance.

                       

                      Details of Remittance.

                       

                      Details of Remittance

                       

                       

                      I appreciate your time and knowledge with this .

                       

                      JPR

                        • 2 files 1 report
                          Grant Perkins

                          Hi JPR,

                           

                          Hmm, I was worried you might say that ...

                           

                          OK, we are working at the limits of possibility here I think and I'm not sure that the sample can present all possible problems BUT you could try this.

                           

                          We need the DETAIL to be the lines of Remittance OR the single line of the ADDENDA record. The only trap I can see that is common is the word "Number" or possible "Number :" but there seems to be a difference in colon position.

                           

                          Also the trap needs to float.

                           

                          If you use that you will get a line form the ACH section as well ....

                           

                          So, to start with make template for all the lines in the ACH section but instead of making it an APPEND, define it as a PAGE HEADER. That will make it invisible to Monarch when Monarch is looking for DETAIL traps but you will still be able to extract and append the data fields to the detail records. It does not matter that it is an artificial page break.

                           

                          Now create a DETAIL template with enough lines in the sample to include all possible remittance lines and using the NUMBER as the trap and make it a floating trap. There are specific rules for accuracy of floating trap definitions. Set it up so that it is based on the Addenda line. It will not fit the Remittance line exactly but we can get around that. Paint a field for the Addenda reference.

                           

                          In the second line of the sample area paint a field that, horizontally, aligns with the start and end of the "Seller's Invoice Number field", It is probably that this will already be partly selected by the Addenda related field definition. In the advanced properties tab for the field enter "seller's invoice number" as a Preceding String. OK that and return to the template definition screen and you should see the field identified. Use the nudge arrows to shift it if required. (Note? I am suspicious that the field in the sample looks small and that it may, on the report, be of variable length. If it is we need to do some more work on it.)

                           

                          Do something similar, ensuring a UNIQUE preceding string for all the other fields in the Rem section you need to extract. You can define more that one field on a line so long as they do not overlap. If there is a risk of overlap extract on separate lines OR extract the entire line after the preceding string and we can split it up later. If the field sizes can be variable in length we may need to do that anyway.

                           

                          If you need anything from the REAL page headers set up an Append template (or two) to get that data.

                           

                          I have a rough sample file and model based on you posted sample. If seeing that might help send me a PM with an email address that will accept attached files.

                           

                          HTH.

                           

                           

                          Grant