Contract Payments extract

Sort:
You are not authorized to post a reply.
Author
Messages
Chesca
Veteran Member
Posts: 490
Veteran Member
    I need to export contract payments including every invoice/purchase order/contract line paid from Lawson. Do you know what tables would I need to get these from?
    POLINE
    PURCHORDER
    JonA
    Veteran Member
    Posts: 1162
    Veteran Member
      Perhaps the MAINVDTL table to get the invoice match detail? Might want POVAGRMTHD to get the contract description and other agreement related details.
      Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
      Chesca
      Veteran Member
      Posts: 490
      Veteran Member
        JonA, thanks. I actually have to provide NYS with 3 interface files include contracts, contract payments, and PO payments or non contract payments.
        So to get the contracts and prime vendors attached to each contract, will I use the POVAGRMTHD and APVENMAST?
        JonA
        Veteran Member
        Posts: 1162
        Veteran Member
          That should work. POVAGRMTHD will provide the contract description and vendor and APVENMAST gets you the vendor name.
          Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
          Chesca
          Veteran Member
          Posts: 490
          Veteran Member
            JoA, I was able to create a SQL to get contract and vendor information. Thank You!

            select DISTINCT con.ven_AGRMT_REF, CON.EFFECTIVE_DT, CON.EXPIRE_DT, CON.AGMT_TYPE, con.buyer, con.hold_flag, con.closed_dt,con.vendor,
            ven.VENDOR_SNAME, ven.tax_id, ven.phone_num, ven.fax_num, ven.E_MAIL_ADDRESS
            from lawson.POVAGRMTHD con
            inner join lawson.apvenmast ven
            on ven.vendor = con.vendor and ven.vendor_group = con.PROCURE_GROUP
            where VEN.VENDOR_STATUS='A'
            and con.EXPIRE_DT>'01-JAN-16'
            and con.HOLD_FLAG='N'
            ORDER BY CON.VENDOR;

            You are not authorized to post a reply.