• Replies 1 reply
  • Subscribers 3 subscribers
  • Views 62 views
  • Users 0 members are here
Options
Related

Query to see the items booked to a certain cost code

DanNewton5392

Hello All

I'm trying to generate a query/report to allow managers to see the items booked to a certain cost code.  I have generated the query below which gives most of teh required information.  However, there is no description for each of teh entries.  Is there a anyone who can assist to confirm the best column and table to extract that from please, i.e. do I have to track back to the PO line or is there somewhere else where this is held inthe DB?

SELECT

T3.glseg AS [Account],

LTRIM(T0.ivcnum) AS [Invoice Number],

T1.ivclinnum AS [Invoice Line Number],

--T0.ivsmoi,

--T1.invsumm_oi,

T1.amtbas_amt AS [Line Total Amount Net],

T1.amtbas_amt+ISNULL(T2.TaxAmount_amt,0) AS [Line Total Amount Gross],

ISNULL(T2.TaxAmount_amt,0) AS [Total Line Tax],

T1.amtbas_cur AS [Currency],

T4.id AS [Vendor Number],

T4.aenm AS [Vendor Name],

T1.apptext

FROM

mc.IVCSUM T0

LEFT OUTER JOIN MC.IVCLINE T1 ON T0.IVSMOI = T1.INVSUMM_OI

LEFT OUTER JOIN MC.INVTAX T2 ON T1.IVLNOI = T2.IVCLINREF_OI

LEFT OUTER JOIN MC.FMLINE T3 ON T1.ivlnoi = T3.ivclinref_oi

LEFT OUTER JOIN MC.VENDOR T4 ON T1.PAYTOVEND_OI = T4.VENDOI

WHERE

(1=1)

AND ivcnum LIKE '%1234567'

AND T3.glseg LIKE '1234-%-12345'

AND T0.ivcsent_date BETWEEN '2016-01-01' AND dateadd(day,1,'2016-12-31')

ORDER BY

T3.glseg,

LTRIM(T0.ivcnum),

T1.ivclinnum

Thanks

Regards

Dan

  • The description would need to come from the matched PO or purchase catalog item. I have added some code to your SQL for an example to pull it. Also, be aware that when their are split charges, a separate FMLINE row is created for each split (and corresponding glseg) The amtbas_amt value from FMLINE shows the amount including any tax (if tax is prorated) or the tax would have a separate line with its own GLSEG value if it is not prorated.

    SELECT
    T3.glseg AS [Account],
    LTRIM(T0.ivcnum) AS [Invoice Number],
    T1.ivclinnum AS [Invoice Line Number],
    --T0.ivsmoi,
    --T1.invsumm_oi,
    T1.amtbas_amt AS [Line Total Amount Net],
    T1.amtbas_amt+ISNULL(TX.TotalTax,0) AS [Line Total Amount Gross],
    ISNULL(TX.TotalTax,0) AS [Total Line Tax],
    t3.amtbas_amt as [Distribution Total Amount Gross],
    T1.amtbas_cur AS [Currency],
    T4.id AS [Vendor Number],
    T4.aenm AS [Vendor Name],
    isnull(po.POLineDesc,t5.aenm) as Description
    FROM
    mc.IVCSUM T0
    LEFT OUTER JOIN MC.IVCLINE T1 ON T0.IVSMOI = T1.INVSUMM_OI
    LEFT OUTER JOIN MC.FMLINE T3 ON T1.ivlnoi = T3.ivclinref_oi
    LEFT OUTER JOIN MC.VENDOR T4 ON T1.PAYTOVEND_OI = T4.VENDOI
    outer apply (select sum(x.taxamount_amt) as TotalTax from mc.invtax x where x.ivclinref_oi = t1.ivlnoi) TX
    outer apply (select top 1 d.apptext as POLineDesc from mc.ivcmemrecon a inner join mc.ivcmem b on a.ivcmemref_oi = b.ivmeoi inner join mc.podel c on b.podel_oi = c.podeloi inner join mc.poline d on c.poline_oi = d.polnoi where a.ivclinref_oi = t1.ivlnoi) PO
    left outer join mc.pcitem t5 on t1.pcitem_oi = t5.pcitoi
    WHERE
    (1=1)
    AND ivcnum LIKE '%1234567'
    AND T3.glseg LIKE '1234-%-12345'
    AND T0.ivcsent_date BETWEEN '2016-01-01' AND dateadd(day,1,'2016-12-31')
    ORDER BY
    T3.glseg,
    LTRIM(T0.ivcnum),
    T1.ivclinnum