AP Check Comment Tables

Sort:
You are not authorized to post a reply.
Author
Messages
Casy
Advanced Member
Posts: 31
Advanced Member
    I am working to query the check comments using SQL. I have located the comment tables L_D*** or L_H*** but am not able to locate the actual check comments. They are not located in any of the obvious L_HAP* or L_HCB* tables. Has anyone located them or found a way to decipher these L_ tables to know which apply to what part of the system? Some are obvious, others not so much. Thank you!
    Kwane McNeal
    Veteran Member
    Posts: 479
    Veteran Member
      First the table names themselves: L_tppp
      Where t=Type: H for header, D for detail; ppp=Parent Table Prefix
      (NOTE: L_ means ‘link’. These tables used to also hold email and URL links)

      Now for what you’re looking for, are you sure it’s not in an actual table like APCOMMENTS?

      Kwane
      Casy
      Advanced Member
      Posts: 31
      Advanced Member
        I did confirm they are not in the APCOMMENTS table. I initially thought they were in the API for AP Invoice but it isn't actual invoice comments. These comments are on the payment. The main table I am using to pull that data is APPAYMENT but not finding an L_ table to relate to the payments. I even went as far as to select the first 1000 rows from each table so am wondering if my eyes were crossed by the time I got to it or if it is somewhere else.
        Kwane McNeal
        Veteran Member
        Posts: 479
        Veteran Member
          I’m not on a system at the moment, but I’d check the COBOL (or RPG, if IBMi), and see what it’s updating.

          I’ll check a system later if no one has replied and you haven’t figured it out.
          Casy
          Advanced Member
          Posts: 31
          Advanced Member
            Thank you for that recommendation to check the COBOL. I did some review and the comments are keyed on CB80 when the checks are voided there. I reviewed the CB80 code and it looks to be writing the comments to CBT. I then queried L_HCBT. The comments in this table are not the comments I need. When I review the CBCHECK table to find the L_INDEX value, it is blank on the examples I have with comments I need to find. The only other L_INDEX value I have found is on the APINVOICE table but it is not pulling back any comments in the L_HCBT table. I should note that I also queried L_DCBT and there were a total of 4 rows and I think that is just for overflow comments anyway so may not be pertinent.

            If the comments aren't in L_HCBT, could it be that a flag to store them is not turned on??
            John Henley
            Senior Member
            Posts: 3348
            Senior Member
              The comments are entered and associated with the invoice, not the check (in other words, they are (technically) invoice comments to print on the check. They are linked from APINVOICE to L_HAPI/L_DAPI. You need to use L_ATAPI_SS_SW = 'Y' as part of your WHERE clause on APINVOICE to get the subset of invoice records that has attachments, and then JOIN to L_HAPI using L_INDEX from APINVOICE, and to L_DAPI for overflow if needed. If you need more info on how comments and attachments are stored and retrieved, see this link https://www.lawsonguru.co...ts-in-Lawson-v8.aspx
              Thanks for using the LawsonGuru.com forums!
              John
              John Henley
              Senior Member
              Posts: 3348
              Senior Member
                Casy, sorry I typed this up before I saw your subsequent message about these being entered when voiding. In that case, the base records are in CBTRANS (CBT) and you use L_ATCBT_SS_SW = 'Y' and the L_INDEX from CBTRANS (NOT APINVOICE) to join to L_HCBT/L_DCBT.
                Thanks for using the LawsonGuru.com forums!
                John
                Kwane McNeal
                Veteran Member
                Posts: 479
                Veteran Member
                  Thanks John, I missed that voiding part too.
                  Casy
                  Advanced Member
                  Posts: 31
                  Advanced Member
                    I had already queried the L_HCBT and didn't find the comments, only the Title until I realized that the object field contained a good bit more information (including the comment detail). I reviewed the document and was able to track this down to get the query I need written. Thank you both so much for your help!
                    You are not authorized to post a reply.