PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 01/11/2016 10:23 AM by  Lesley
SQL Query of GL90
 1 Replies
Sort:
You are not authorized to post a reply.
Author Messages
PeshWen
Accountant
Private
New Member
(1 points)
New Member
Posts:1


Send Message:

--
01/09/2016 2:54 PM
    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
    App Supp Eng
    Kaman
    (9 points)
    Posts:3


    Send Message:

    --
    01/11/2016 10:23 AM
    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
    You are not authorized to post a reply.