SSRS report APDISTRIB join to APPAYMENT

Hello -

I have just begun utilizing SSRS to write Lawson reports.

The goal of the report I'm working on is to display all APDISTRIB records for a given company and date and see the corresponding APPAYMENT check number, due date, payment date. My problem is that I don't have a one to one relationship in those tables. I managed to pull the entire APDISTRIB and did a LEFT OUTER JOIN to APPAYMENT. The problem I'm having is partial payments in APPAYMENT have multiple APPAYMENT records. So even though I'm using a LEFT OUTER JOIN, the query is still returning all the rows that match on the right, therefore the one row in the left that matches two rows on the right does return as two rows, just like an inner join.

This is throwing the report totaling off since I didn't really have two distribution records, yet two are appearing for the partial payments. Has anyone experienced a similar problem and know how to fix?

Find more posts tagged with

Infor Lawson Technology Group - Discussion

Comments

unknown

I've worked through this frustration in Lawson myself, when using Crystal Reports. AP payments are recorded 3 ways in Lawson: APINVOICE, APDISTRIB, and APPAYMENT. All 3 total up the same, but, money is distributed differently in all three. APPAYMENT can contain one or more APINVOICE entries. There can be multiple distributions for a single invoice. So what I do is to link APPAYMENT to APINVOICE by COMPANY, VENDOR, INVOICE, SUFFIX, CANCEL_SEQ, SEQ_NBR, and VOID_SEQ. I also link APINVOICE to APDISTRIB with APINVOICE.OBJ_ID linked to APDISTRIB.API_OBJ_ID. I've had to play with the joins to get the data right. It all works, but, as I said, the money is not the same in each column. In the end, it all works out.

I notice in my reports that I am using inner joins, not left or right outer joins.

raymond-irizarry

Thanks for the feedback. I thought I was going crazy. I didn't even know our AP did partial payments until now.

unknown

Nope - you're definitely not crazy. It's great functionality in the system, just doesn't allow for easy reporting. Here's the SQL from a Crystal Report that we made available to our users. Because of the "duplication" the columns don't add up or tie out to each other, but our users are aware. The report includes TRAN_INV_AMT, which will always be greater than or equal to the ORIG_BASE_AMT (also included). The TRAN_PMT_AMT is included, and can be far higher that the other two columns, depending on the parameters selected. The person running the report can choose: Proc Level, start and end distrib dates, start and end distrib Acctg Units, start and end distrib Accounts, start and end check dates (include 1/1/1700 in Crystal to indicate unpaid).

We also have this note in the report file:

//Please keep in mind that when there is a void and reissue of a check

//in the report period, with no cancellation of the invoice, this report

//will appear to duplicate the distribution information on an invoice

//because it will include two lines in this report to display both checks.

SELECT "APDISTRIB"."COMPANY", "APDISTRIB"."PROC_LEVEL", "APDISTRIB"."REC_STATUS", "APDISTRIB"."DIS_ACCT_UNIT", "APDISTRIB"."DIS_ACCOUNT", "APDISTRIB"."DISTRIB_DATE", "APINVOICE"."VENDOR", "APVENMAST"."VENDOR_VNAME", "GLCHARTDTL"."ACCOUNT_DESC", "GLNAMES"."DESCRIPTION", "APINVOICE"."INVOICE", "APINVOICE"."SUFFIX", "APINVOICE"."INVOICE_DTE", "APINVOICE"."PO_NUMBER", "APINVOICE"."DESCRIPTION", "APDISTRIB"."ORIG_BASE_AMT", "APDISTRIB"."ACCR_CODE", "APPAYMENT"."TRAN_PMT_AMT", "APPAYMENT"."REC_STATUS", "APPAYMENT"."TRANS_NBR", "APPAYMENT"."SEQ_NBR", "APINVOICE"."TRAN_INV_AMT", "APDISTRIB"."DIS_SUB_ACCT", "APPAYMENT"."VOID_DATE", "APPAYMENT"."CHECK_DATE", "CBCHECK"."TRAN_PMT_AMT", "APDISTRIB"."CANCEL_SEQ"

FROM (((((("LAWPROD"."APVENMAST" "APVENMAST" INNER JOIN "LAWPROD"."APDISTRIB" "APDISTRIB" ON "APVENMAST"."VENDOR"="APDISTRIB"."VENDOR") INNER JOIN "LAWPROD"."GLNAMES" "GLNAMES" ON ("APDISTRIB"."DIST_COMPANY"="GLNAMES"."COMPANY") AND ("APDISTRIB"."DIS_ACCT_UNIT"="GLNAMES"."ACCT_UNIT")) INNER JOIN "LAWPROD"."GLMASTER" "GLMASTER" ON ((("APDISTRIB"."DIST_COMPANY"="GLMASTER"."COMPANY") AND ("APDISTRIB"."DIS_ACCT_UNIT"="GLMASTER"."ACCT_UNIT")) AND ("APDISTRIB"."DIS_ACCOUNT"="GLMASTER"."ACCOUNT")) AND ("APDISTRIB"."DIS_SUB_ACCT"="GLMASTER"."SUB_ACCOUNT")) INNER JOIN "LAWPROD"."APINVOICE" "APINVOICE" ON (((("APDISTRIB"."VENDOR"="APINVOICE"."VENDOR") AND ("APDISTRIB"."INVOICE"="APINVOICE"."INVOICE")) AND ("APDISTRIB"."SUFFIX"="APINVOICE"."SUFFIX")) AND ("APDISTRIB"."COMPANY"="APINVOICE"."COMPANY")) AND ("APDISTRIB"."CANCEL_SEQ"="APINVOICE"."CANCEL_SEQ")) LEFT OUTER JOIN "LAWPROD"."APPAYMENT" "APPAYMENT" ON (((("APINVOICE"."COMPANY"="APPAYMENT"."COMPANY") AND ("APINVOICE"."VENDOR"="APPAYMENT"."VENDOR")) AND ("APINVOICE"."INVOICE"="APPAYMENT"."INVOICE")) AND ("APINVOICE"."SUFFIX"="APPAYMENT"."SUFFIX")) AND ("APINVOICE"."CANCEL_SEQ"="APPAYMENT"."CANCEL_SEQ")) LEFT OUTER JOIN "LAWPROD"."CBCHECK" "CBCHECK" ON (("APPAYMENT"."CASH_CODE"="CBCHECK"."CASH_CODE") AND ("APPAYMENT"."BANK_INST_CODE"="CBCHECK"."BANK_INST_CODE")) AND ("APPAYMENT"."TRANS_IDENT"="CBCHECK"."TRANS_IDENT")) INNER JOIN "LAWPROD"."GLCHARTDTL" "GLCHARTDTL" ON (("GLMASTER"."CHART_NAME"="GLCHARTDTL"."CHART_NAME") AND ("GLMASTER"."ACCOUNT"="GLCHARTDTL"."ACCOUNT")) AND ("GLMASTER"."SUB_ACCOUNT"="GLCHARTDTL"."SUB_ACCOUNT")

WHERE "APDISTRIB"."COMPANY"=1000 AND "APDISTRIB"."REC_STATUS"=9 AND ("APDISTRIB"."PROC_LEVEL">'0' AND "APDISTRIB"."PROC_LEVEL"<'z') AND ("APDISTRIB"."DIS_ACCT_UNIT">='0' AND "APDISTRIB"."DIS_ACCT_UNIT"<='999999') AND ("APDISTRIB"."DIS_ACCOUNT">=0 AND "APDISTRIB"."DIS_ACCOUNT"<=999999) AND "APDISTRIB"."DIS_SUB_ACCT">=0 AND ("APDISTRIB"."DISTRIB_DATE">=TO_DATE ('01-01-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "APDISTRIB"."DISTRIB_DATE"<TO_DATE ('01-02-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND ("APDISTRIB"."CANCEL_SEQ">0 OR ("APPAYMENT"."CHECK_DATE">=TO_DATE ('01-01-1700 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "APPAYMENT"."CHECK_DATE"<TO_DATE ('01-02-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS')))

Copyright © 2025 Infor. All rights reserved.