1 Reply Latest reply: May 15, 2014 10:02 AM by Grant Perkins RSS

    Different type of format report

    francisco _

      Hi. Hope everyone who sees this post is having a great day!

       

      I am back with a second question.

      The reports that I'm dealing with are not as the regular ones where the data pertaining to one item is accross the page. I can see that this way it is easier to capture more than one field record at a time.

       

      Name          ID            Age         Sex           Hight

      XXXXX        XX           XX           X              XXX

       

      ...but my report is layed out in a different way or format.

      Please see below.

       

      From:  COMPANY820                                                                 

      Document Type: 820 Paymnt Order/Remit Advice    Version: 004030                    

      Document ID: D-300000060                                                           

      DOCUMENT HEADER                      ST                                            

        Transaction Set Identifier Code    820          This section is useless                                 

      -


                                                                                       

      BEGIN SEGMNT FOR PAYMT ORD/REMIT AD  BPR                                           

        Transaction Handling Code          Remittance Information Onl                    

        Payment Amount                     111967.43                                     

        Credit/Debit Flag Code             Credit                                        

        Payment Method Code                Automated Clearing House (ACH)                

        Payment Format Code                Cash Concentration/Disbursement plus Addend   

        (DFI) ID Number Qualifier          ABA Transit Routing Number Including Check    

        (DFI) Identification Number        XXXXXXXXX                                     

        Account Number Qualifier           Demand Deposit                                

        Originating Company Account Number XXXXXXXXXXXXX                                 

        Originating Company ID Number      XXXXXXXXXX                                    

        (DFI) ID Number Qualifier          ABA Transit Routing Number Including Check    

        (DFI) Identification Number        XXXXXXXXX                                     

        Account Number Qualifier           Demand Deposit                                

        Receiving Company Account Number   XXXXXXXX                                      

        Effective Entry Date               Aug.  5, 2008                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Electronic Funds Transfer ID Number           

        Reference Identification           494943 ----


        This is chk # and  

      it’ll always be here only once throughout the report                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Aug.  1, 2008 --This is the check date.  This Date only                   correspond to the check                                                                               

      NAME                                 N1                                            

        Entity Identifier Code             Payer                

                                

        Name                               Company Common                                                                               

      NAME                                 N1                                            

        Entity Identifier Code             Payee                                         

        Name                               VENDOR COMPANY INC                  

       

      Loop Type 1

                                                                                      ENTITY                               ENT                                           

        Assigned Number                    820                                           

        Entity Identifier Code             Account Of                                    

        Identification Code Qualifier      Assigned by Buyer or Buyer                    

        Identification Code                006288                                                                               

      INDIVIDUAL NAME                      NM1                                           

        Entity Identifier Code             Bill-to-Party                                 

        Entity Type Qualifier              Non-Person Entity                             

        Identification Code Qualifier      Assigned by Buyer or Buyer                    

        Identification Code                91140                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -477.73                                       

        Adjustment Reason Code             Shipper/Consignee Ref. Number Missing         

        Reference Identification Qualifier Adjustment Memo (Charge Ba                    

        Reference Identification           0028096                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           COMP-73247-00180                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           000                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Billing Account                               

        Reference Identification           COMMERCIAL ZONE (NON-COMP DEDUCTION) 00002    

                                           SHIPMEN                                       

      End of loop type 1                                                                               

      This is a loop (Type 1) for adjustment of $477.73 to shipment #0028096 sent to

      store #91140

       

      Next loop is the same but for another store

       

      ENTITY                               ENT                                           

        Assigned Number                    820                                           

        Entity Identifier Code             Account Of                                    

        Identification Code Qualifier      Assigned by Buyer or Buyer                    

        Identification Code                006288                                                                               

      Date Printed: Aug. 14, 2008                                            Page: 1 of 66From:  COMPANY820                                                                 

      Document Type: 820 Paymnt Order/Remit Advice    Version: 004030                    

      Document ID: D-300000060                                                           

      INDIVIDUAL NAME                      NM1                                           

        Entity Identifier Code             Bill-to-Party                                 

        Entity Type Qualifier              Non-Person Entity                             

        Identification Code Qualifier      Assigned by Buyer or Buyer                    

        Identification Code                91306                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -126                                          

        Adjustment Reason Code             Merchandise Not Ordered                       

        Reference Identification Qualifier Adjustment Memo (Charge Ba                    

        Reference Identification           8003746-1OVAR                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           4325420:JCP_AP_MATCH_VAR_ORD                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           000                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Billing Account                               

        Reference Identification           DM for Invoice: 8003746                       

        -


      inside loop for same store #91306----


                                                                                       

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -126                                          

        Adjustment Reason Code             Merchandise Not Ordered                       

        Reference Identification Qualifier Adjustment Memo (Charge Ba                    

        Reference Identification           8007398-1OVAR                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           4325420:JCP_AP_MATCH_VAR_ORD                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           000                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Billing Account                               

        Reference Identification           DM for Invoice: 8007398                       

       

       

      End of loop 2                                                                               

      This following loop (Type 2)section (RMR-ADX) shows the payment detail and

      deduction for one single invoice. It includes the PO# and PO date and the

      breakdown of the deductions (the lat two “ADX” segment of each loop. These

      two will add up the “Discount Amount Taken” (i.e 9.8 + 1.96=11.76)

       

       

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8002598                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        184.24                                        

        Total Invoice/Credit-Debit Amount  196                                           

        Discount Amount Taken              11.76                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4604294C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 16, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -9.8                                          

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -1.96                                         

        Adjustment Reason Code             Defective Allowance                           

       

      The grayed part is the header of the report. It is useless and interferes

      with some templates

       

       

       

      Somewhere further in the report, there are more loops of type 1 and 2 and of

      course it is inpredictable to know if there are going to be more and in

      what page.

                                                                                      Date Printed: Aug. 14, 2008                                            Page: 2 of 66From:  COMPANY820                                                                 

      Document Type: 820 Paymnt Order/Remit Advice    Version: 004030                    

      Document ID: D-300000060                                                           

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8002606                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        2163.88                                       

        Total Invoice/Credit-Debit Amount  2302                                          

        Discount Amount Taken              138.12                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4628814C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 16, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -115.1                                        

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -23.02                                        

        Adjustment Reason Code             Defective Allowance                                                                               

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8003729                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        120.32                                        

        Total Invoice/Credit-Debit Amount  128                                           

        Discount Amount Taken              7.68                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4485298C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 21, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -6.4                                          

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -1.28                                         

        Adjustment Reason Code             Defective Allowance                                                                               

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8003730                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        270.72                                        

        Total Invoice/Credit-Debit Amount  288                                           

        Discount Amount Taken              17.28                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

      Date Printed: Aug. 14, 2008                                            Page: 3 of 66From:  COMPANY820                                                                 

      Document Type: 820 Paymnt Order/Remit Advice    Version: 004030                    

      Document ID: D-300000060                                                           

        Reference Identification           4485306C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 21, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -14.4                                         

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -2.88                                         

        Adjustment Reason Code             Defective Allowance                                                                               

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8003731                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        1528.44                                       

        Total Invoice/Credit-Debit Amount  1626                                          

        Discount Amount Taken              97.56                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4485314C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 21, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -81.3                                         

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -16.26                                        

        Adjustment Reason Code             Defective Allowance                                                                               

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8003734                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        665.52                                        

        Total Invoice/Credit-Debit Amount  708                                           

        Discount Amount Taken              42.48                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4544888C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      Date Printed: Aug. 14, 2008                                            Page: 4 of 66From:  COMPANY820                                                                 

      Document Type: 820 Paymnt Order/Remit Advice    Version: 004030                    

      Document ID: D-300000060                                                           

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 21, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -35.4                                         

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -7.08                                         

        Adjustment Reason Code             Defective Allowance                                                                               

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8003746                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        355.32                                        

        Total Invoice/Credit-Debit Amount  378                                           

        Discount Amount Taken              22.68                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4686879C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           721                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Jan. 21, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -18.9                                         

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -3.78                                         

        Adjustment Reason Code             Defective Allowance                                                                               

      REMIT ADVICE ACCTS REC OPEN ITEM RE  RMR                                           

        Reference Identification Qualifier Seller's Invoice Number                       

        Reference Identification           8007398                                       

        Payment Action Code                Payment on Account                            

        Amount Paid                        947.52                                        

        Total Invoice/Credit-Debit Amount  1008                                          

        Discount Amount Taken              60.48                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Purchase Order Number                         

        Reference Identification           4733481C                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Department Number                             

        Reference Identification           725                                                                               

      REFERENCE IDENTIFICATION             REF                                           

        Reference Identification Qualifier Accounting Code                               

        Reference Identification           EDI810-73349-00122                                                                               

      DATE/TIME REFERENCE                  DTM                                           

        Date/Time Qualifier                Transaction Creation                          

        Date                               Feb.  6, 2008                                                                               

      ADJUSTMENT                           ADX                                           

        Adjustment Amount                  -50.4                                         

        Adjustment Reason Code             Cooperative Advertising                                                                               

      ADJUSTMENT                           ADX                                            /CODE

       

      How can I create a templete or templetes that can capture the following informatioin:

       

      Electronica Funds transfer ID "494943".

      Transaction Creation "Aug 1, 2008" (this is the date for the check #494943.

       

      Store 91140 and it's deduction of $477.73, reason, and doc # (Shpper/Consignee Ref # Missing, 0028096.

       

      Store 91306, $126, Merchandise Not Ordered, 8003746-1OVAR

                        $126, Merchandise Not Ordered, 8007398-1OVAR

      (two docs for this Store)

       

      Invoice #8002598, Amount paid $184.24, Total Inv $196, Discount $11.76, PO# 4604294C, PO Date Jan 16, 2008, Adjustment (1) $9.8 "Cooperative Advertising", and $1.96 "Defective Allowance".

       

      The data is not across the page but top to bottom and some of this data may be confused with other on the same trap lenght.

       

      Any suggestioins?

      I can gladly e-mail further information if you think is needed.

       

      A million Thank you!

       

      Francisco

        • Different type of format report
          Grant Perkins

          Francisco,

           

          The basic needs for this report are sort of similar to those used for the last problem report BUT this one looks like it may be IN EFFECT as far as your analysis is required, an amalgamation of more than one report that does not lend itself easily to extraction or perhaps does not lend itself to easy extraction in one process.

           

          I am a little concerned that although you have clearly identified 2 different types of record groupings there could be more. Possibly many more. That would make things very messy.

           

          I find the layout very time consuming to work with so it does not encourage a quick investigation. Since so many parts of the records are obviously different but are referred to by the same 'tags' to the left of the actual data yet spread out over so many lines it makes it an uncertain task. You have my sympathy.

           

          I'm sure your problem can be resolved but it looks currently like it will require more time than I can allocate at the moment and probably also the analysis of a very complete report to cover all the variant sections that may appear since I suspect there may be more than the sample currently offers? If that guess is wrong then please let me know.

           

           

           

           

          Grant