GLTRANS join check with APDISTRIB & APINVOICE

I would start with etiher GLTRANS or APINVOICE as my first table after the from depending on which table you want to filter on. APDISTRIB is used to join APINVOICE to GLTRANS. Here is an example of a query I use.

SELECT GLT.OBJ_ID,GLT.FISCAL_YEAR,GLT.ACCT_PERIOD,GLT.SYSTEM,GLT.LINE_NBR,GLT.STATUS,GLT.ACCT_UNIT,
GLT.ACCOUNT,GLT.SOURCE_CODE,GLT.R_DATE,GLT.DESCRIPTION,GLT.BASE_AMOUNT,GLT.POSTING_DATE,GLT.ACTIVITY,
APD.DIS_ACCT_UNIT,GLN.DESCRIPTION AS DEPTNAME,APD.DIS_ACCOUNT,GDT.ACCOUNT_DESC,APD.INVOICE,SUM(APD.ORIG_BASE_AMT) AS ORIG_BASE_AMT,API.INVOICE_DTE,
VEN.VENDOR,VEN.VENDOR_VNAME,
APP.CHECK_DATE,GLT.POSTING_DATE,APP.VOID_SEQ
FROM dbo.GLTRANS GLT with (nolock)
LEFT OUTER JOIN dbo.APDISTRIB APD with (nolock) ON GLT.OBJ_ID = APD.GLT_OBJ_ID AND SYSTEM=''AP''
LEFT OUTER JOIN dbo.APPAYMENT APP with (nolock) ON APD.COMPANY = APP.COMPANY AND APD.VENDOR = APP.VENDOR AND
APD.INVOICE = APP.INVOICE AND APD.SUFFIX = APP.SUFFIX AND APD.CANCEL_SEQ = APP.CANCEL_SEQ AND APP.VOID_SEQ=0
LEFT OUTER JOIN dbo.APINVOICE API with (nolock) ON APD.COMPANY = API.COMPANY AND
APD.VENDOR = API.VENDOR AND
APD.INVOICE = API.INVOICE AND
APD.SUFFIX = API.SUFFIX AND
APD.CANCEL_SEQ = API.CANCEL_SEQ
LEFT OUTER JOIN dbo.APCOMPANY CPY with (nolock) ON CPY.COMPANY = APD.COMPANY
LEFT OUTER JOIN dbo.GLNAMES GLN with (nolock) ON APD.COMPANY=GLN.COMPANY AND APD.DIS_ACCT_UNIT=GLN.ACCT_UNIT
LEFT OUTER JOIN dbo.APVENMAST VEN with (nolock) ON CPY.VENDOR_GROUP = VEN.VENDOR_GROUP AND API.VENDOR=VEN.VENDOR
LEFT OUTER JOIN dbo.GLCHARTDTL GDT with (nolock) ON APD.DIS_ACCOUNT=GDT.ACCOUNT
WHERE GLT.FISCAL_YEAR=@FISCALYEAR AND GLT.ACCT_UNIT=''@ACCTUNIT'''

Some "performance enhancers":

FROM dbo.GLTRANS GLT with (nolock)
LEFT OUTER JOIN dbo.APDISTRIB APD with (nolock) ON GLT.OBJ_ID = APD.GLT_OBJ_ID AND SYSTEM=''AP''

FROM dbo.GLTRANS GLT with (nolock)
LEFT OUTER JOIN dbo.APDISTRIB APD with (nolock) ON APD.APDSET5_SS_SW='Y' AND GLT.OBJ_ID = APD.GLT_OBJ_ID AND SYSTEM=''AP''

LEFT OUTER JOIN dbo.APPAYMENT APP with (nolock) ON APD.COMPANY = APP.COMPANY AND APD.VENDOR = APP.VENDOR AND
APD.INVOICE = APP.INVOICE AND APD.SUFFIX = APP.SUFFIX AND APD.CANCEL_SEQ = APP.CANCEL_SEQ AND APP.VOID_SEQ=0
LEFT OUTER JOIN dbo.APINVOICE API with (nolock) ON APD.COMPANY = API.COMPANY AND
APD.VENDOR = API.VENDOR AND
APD.INVOICE = API.INVOICE AND
APD.SUFFIX = API.SUFFIX AND
APD.CANCEL_SEQ = API.CANCEL_SEQ

LEFT OUTER JOIN dbo.APINVOICE API with (nolock) ON APD.COMPANY = API.COMPANY AND
APD.VENDOR = API.VENDOR AND
APD.INVOICE = API.INVOICE AND
APD.SUFFIX = API.SUFFIX AND
APD.CANCEL_SEQ = API.CANCEL_SEQ
LEFT OUTER JOIN dbo.APPAYMENT APP with (nolock) ON
API.COMPANY = APP.COMPANY
AND API.VENDOR = API.VENDOR AND
API.INVOICE = APP.INVOICE
AND API.SUFFIX = APP.SUFFIX
AND API.CANCEL_SEQ = APP.CANCEL_SEQ

LEFT OUTER JOIN dbo.GLNAMES GLN with (nolock) ON APD.COMPANY=GLN.COMPANY AND APD.DIS_ACCT_UNIT=GLN.ACCT_UNIT

LEFT OUTER JOIN dbo.GLNAMES GLN with (nolock) ON APD.DIST_COMPANY=GLN.COMPANY AND APD.DIS_ACCT_UNIT=GLN.ACCT_UNIT

LEFT OUTER JOIN dbo.GLCHARTDTL GDT with (nolock) ON APD.DIS_ACCOUNT=GDT.ACCOUNT

LEFT OUTER JOIN dbo.GLSYSTEM GLS with (nolock) ON APD.DIST_COMPANY=GLS.ACCOUNT
LEFT OUTER JOIN dbo.GLCHARTDTL GDT with (nolock) ON APD.DIS_ACCOUNT=GDT.ACCOUNT AND APD.DIS_SUB_ACCT=GDT.SUB_ACCOUNT AND GLS.CHART_NAME=GDT.CHART_NAME

What is the purpose of the (nolock) ?

the with (nolock) is a table lock hint in sql server. Basically that means the select statment won't take out a lock on the row or table.

Okay.

I have adjusted my joins, but I still have an issue. The company is not coming in correctly with the join:

FROM APINVOICE LEFT OUTER JOIN
ACACTIVITY LEFT OUTER JOIN
GLTRANS LEFTOUTER INNER JOIN
APDISTRIB ON LEFTOUTER.OBJ_ID = APDISTRIB.GLT_OBJ_ID LEFT OUTER JOIN
GLSYSTEM ON LEFTOUTER.COMPANY = GLSYSTEM.COMPANY ON ACACTIVITY.ACTIVITY = LEFTOUTER.ACTIVITY ON
APINVOICE.OBJ_ID = APDISTRIB.API_OBJ_ID

I should be getting a Canadian company, but the primary company which is the US Company comes in instead. Why?

Thanks!

What program are you using to create your joins? When you join 2 tables, you have to join the tables with the right columns. So when you are joining the ACTIVITY table to APINVOICE, you have to join on columns like company and activity. From what I can tell your joins are only joining on 1 column.

There has to be something wrong with the ACACTIVITY join to the GLSYSTEM join as I don't get the right company number with the right company name.

You can link the ACACTIVITY to ACTRANS and then link the ACTRANS to the ACDISTRIB table.

the actrans.obj_id should be linked to the apdistrib.atn_obj_id along with the other joins between actrans to apdistrib. Lawson documentation on these tables will explain how these tables are joined.

hth

I meant to say APDISTRIB and not ACDISTRIB.

I don' t even have the ACTRANS table pulled into this query. Why would I need to, I just need these fields:

Company
AU
Account
Sub-account
Currency Code
Transaction Amount
Base Amount
Posting Date
Update Date
Invoice Date

SO that is all I need to pull in. This should be pretty easy.

If you do not need activity information you do not have to go to any of the AC tables.

If you are joining GLtrans to apdistrib the fol. are the joins:

glt_obj_id=apdistrib.glt_obj_id.  All the fields are in these 2 tables depending on which amounts you would like to display. The only field that you would need to go to apinvoice is the invoice date. Lawson's documentation on joining the apdistib to apinvoice is very clear. I recommend their documentation which you can get on this site when you click on ERDs.

Thanks I appreciate your help much! And I do have to travel over to APINOVICE to pick up the Invoice Date, that is where all the discussion came in about adding in the APDISTRIB and the APINVOICE table and where I have erred.

Okay so if you look at the data dictionary from the APDISTRIB to the APINVOICE and the joins there are several:
APD-COMPANY -> API-COMPANY
APD-VENDOR -> API-VENDOR
APD-INVOICE -> API-INVOICE
APD-SUFFIX -> API-SUFFIX
APD-CANCEL-SEQ -> API-CANCEL-SEQ

If you look at APINVOICE TO APDISTRIB there is one:

API-OBJ-ID -> APD-API-OBJ-ID
APD-DIST-SEQ-NBR (why is this here?)

So which one is correct to use in this situation.

If you are joining GLTRANS to APDISTRIB to APINVOICE whre you are connecting APDISTRIB to APINVOICE you will use :

APD-COMPANY -> API-COMPANY
APD-VENDOR -> API-VENDOR
APD-INVOICE -> API-INVOICE
APD-SUFFIX -> API-SUFFIX
APD-CANCEL-SEQ -> API-CANCEL-SEQ

If you are joining APINVOICE to APDISTRIB to GLTRANS, where you are going rom APINVOICE to APDISTRIB you will use:
API-OBJ-ID -> APD-API-OBJ-ID.

This should give you the right results.

Hope the above helps. I cannot help you more than this.

Thanks I appreciate your help!

Thank you all for your suggestions - I got the report done just a few minutes ago and I got the correct results. RUMA Thanks so much for your clarifications - that was the icing on the cake!

NOW folks, how do I learn the rest of Lawson tables!

THANKS AGAIN!