SQL Query of GL90

 1 Replies
 1 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
Michelle Hager
New Member Send Private Message
Posts: 2
New Member
I have the below query and I can't seem to get the description of the inventory items. I'm mimicking GL90 can anyone help?

SELECT
GLT.TO_COMPANY AS 'Co'
,CASE WHEN GLT.SYSTEM = 'AP' THEN(VEN.VENDOR)
ELSE('N/A') END AS 'Vendor'
,CASE WHEN GLT.SYSTEM = 'AP' THEN(API.VOUCHER_NBR)
ELSE('N/A') END AS 'Voucher'
,GLT.ACCT_UNIT AS 'Dept'
,GLT.SYSTEM AS 'System'
,GLT.ACCOUNT 'Account'
,GLC.ACCOUNT_DESC AS 'Acct Description'
,CASE WHEN GLT.SYSTEM = 'AP' THEN(APD.ORIG_BASE_AMT)
ELSE(GLT.TRAN_AMOUNT)END AS 'Amount w/tax'
,GLT.CONTROL_GROUP
,CASE WHEN GLT.SYSTEM = 'AP' THEN CONVERT(VARCHAR(10),APP.CHECK_DATE,101)
ELSE CONVERT(VARCHAR(10),GLT.EFFECT_DATE,101) END AS 'PAID DATE'

FROM LSLMDB.ls_apps.GLTRANS GLT

LEFT OUTER JOIN
LSLMDB.ls_apps.APDISTRIB APD
ON APD.DIST_COMPANY = GLT.COMPANY
AND APD.DIS_ACCT_UNIT = GLT.ACCT_UNIT
AND APD.DIS_ACCOUNT = GLT.ACCOUNT
AND GLT.OBJ_ID = APD.GLT_OBJ_ID

LEFT OUTER JOIN
LSLMDB.ls_apps.APPAYMENT APP
ON APP.COMPANY = APD.COMPANY
AND APD.VENDOR = APP.VENDOR
AND APP.INVOICE = APD.INVOICE
AND APP.SUFFIX = APD.SUFFIX
AND APP.CANCEL_SEQ = APD.CANCEL_SEQ
AND APP.VOID_SEQ = 0

LEFT OUTER JOIN
LSLMDB.ls_apps.APINVOICE API
ON APD.VENDOR = API.VENDOR
AND APD.INVOICE = API.INVOICE
AND APD.COMPANY = API.COMPANY
AND APD.SUFFIX = API.SUFFIX
AND APD.CANCEL_SEQ = API.CANCEL_SEQ

LEFT OUTER JOIN
LSLMDB.ls_apps.GLMASTER GLM
ON GLM.ACCOUNT = GLT.ACCOUNT
AND GLM.ACCT_UNIT = GLT.ACCT_UNIT
AND GLM.COMPANY = GLT.COMPANY

LEFT OUTER JOIN
LSLMDB.ls_apps.GLCHARTDTL GLC
ON GLC.CHART_NAME = GLM.CHART_NAME
AND GLC.ACCOUNT = GLT.ACCOUNT

LEFT OUTER JOIN
LSLMDB.ls_apps.APVENMAST VEN
ON VEN.VENDOR = API.VENDOR
AND VEN.VENDOR_GROUP = API.VENDOR_GROUP
AND VEN.VEN_CLASS = APP.VEN_CLASS

LEFT OUTER JOIN
LSLMDB.ls_apps.REQLINE REQ
ON REQ.VENDOR = VEN.VENDOR
AND REQ.COMPANY = GLT.COMPANY

LEFT OUTER JOIN
LSLMDB.ls_apps.APVENCLASS APV
ON APV.VENDOR_GROUP = APP.VENDOR_GROUP
AND APV.VEN_CLASS = VEN.VEN_CLASS

WHERE GLT.FISCAL_YEAR = 2015
AND GLT.ACCT_UNIT = 1118321
AND GLT.ACCOUNT BETWEEN 733900 AND 734920
AND GLT.COMPANY = 100
AND GLT.ACCT_PERIOD = 12
AND (APP.CHECK_DATE BETWEEN '2015-12-16 00:00:00.000' AND '2015-12-31 00:00:00.000'
OR GLT.SYSTEM <> 'AP' AND GLT.EFFECT_DATE BETWEEN '2015-12-16 00:00:00.000' AND '2015-12-31 00:00:00.000')

GROUP BY GLT.TO_COMPANY, GLT.ACCT_UNIT, GLT.ACCOUNT, GLC.ACCOUNT_DESC, APP.CHECK_DATE,GLT.TRAN_AMOUNT, GLT.EFFECT_DATE, GLT.SYSTEM, GLT.CONTROL_GROUP, APD.ORIG_BASE_AMT, VEN.VENDOR,API.VOUCHER_NBR
Lesley
Send Private Message
Posts: 3
Ok now that I know you want to get info from Item Master / Itemmast - try doing a smaller query to just that table - item master - from one of your main tables in larger query. When you do this, work out the key fields to get the links correct. If you need to - consult the laenv / User Desktop / data file / technical text for information on the links / tables (and programs if you want). (Alternately - you also can look up database schema on inforxtreme.com site.)

Good Luck!
Lesley