Microsoft Dynamics GP (Archived)

Help with POP query for smartlist builder

(0) Share

Report

Posted on by Alvaro Rodríguez Ochoa

2,395

Hi all, i'm trying to make a POP smartlist with the following information:

PO Number

PO line number

Item ID

Item description

Vendor ID

Qty ordered

Qty delivered

Required date

Promised date

Released date

Vendor name

Buyer ID

Actual cost

UofM

Invoiced qty (if any)

Comment ID

What I have so far is:

SELECT TOP (100) PERCENT POP.PONUMBER, POP.ORD, POP.ITEMNMBR, QTYRCP.POPRCTNM, QTYRCP.QTYSHPPD, POP.ITEMDESC, POP.VENDORID, POP.LineNumber, POP.REQDATE,

POP.PRMSHPDTE, POP.PRMDATE, POP.QTYORDER, dbo.IV00101.CURRCOST, POP.UOFM, QTYRCP.QTYINVCD, dbo.POP10100.USER2ENT, dbo.PM00200.VENDNAME, POP.POLNESTA AS estado,

POP.Released_Date, dbo.POP10100.DOCDATE

FROM dbo.POP10500 AS QTYRCP INNER JOIN

dbo.POP10100 ON QTYRCP.PONUMBER = dbo.POP10100.PONUMBER INNER JOIN

dbo.POP10110 AS POP INNER JOIN

dbo.PM00200 ON POP.VENDORID = dbo.PM00200.VENDORID LEFT OUTER JOIN

dbo.IV00101 ON POP.ITEMNMBR = dbo.IV00101.ITEMNMBR ON QTYRCP.POLNENUM = POP.ORD AND QTYRCP.ITEMNMBR = POP.ITEMNMBR AND ISNULL(QTYRCP.QTYSHPPD, 0)

< POP.QTYORDER

WHERE (POP.POLNESTA IN (1, 2, 3, 4))

ORDER BY POP.PONUMBER, POP.ITEMNMBR

But I keep getting duplicated records, hope someone can help me with this.

*This post is locked for comments

All responses (5)

Answers (1)

Alvaro Rodríguez Ochoa 2,395 on at

Like (0)

Report

RE: Help with POP query for smartlist builder

Thanks a lot thetrev68 I think this nailed it!

Best regards.
Verified answer

Community Member on at

Like (0)

Report

RE: Help with POP query for smartlist builder

I had difficulty following your joins. Try this...

SELECT RTRIM(A.PONUMBER) [PO Number]

, B.LineNumber [PO Line No.]

, RTRIM(B.ITEMNMBR) [Item Number]

, RTRIM(B.ITEMDESC) [Item Description]

, B.VENDORID [Vendor ID]

, B.QTYORDER - B.QTYCANCE [Qty Ordered]

, ISNULL(C.QTYSHPPD,0) [Qty Received]

, B.REQDATE [Required Date]

, B.PRMSHPDTE [Promised Date]

, A.DOCDATE [Released Date]

, RTRIM(D.VENDNAME) [Vendor Name]

, RTRIM(A.BUYERID) [Buyer ID]

, COALESCE(C.ORCPTCOST, B.UNITCOST,0) [Unit Cost]

, B.UOFM

, ISNULL(C.QTYINVCD,0) [Qty Invoiced]

, A.COMMNTID [PO Comment ID]

, B.COMMNTID [Line Item Comment ID]

, CASE B.POLNESTA

WHEN 1 THEN 'New'

WHEN 2 THEN 'Released'

WHEN 3 THEN 'Change Order'

WHEN 4 THEN 'Received'

WHEN 5 THEN 'Closed'

WHEN 6 THEN 'Cancelled'

ELSE '#Error'

END [PO Line Status]

FROM POP10100 A

JOIN POP10110 B ON A.PONUMBER = B.PONUMBER

LEFT JOIN (SELECT PONUMBER, POLNENUM, SUM(QTYSHPPD) QTYSHPPD, SUM(QTYINVCD) QTYINVCD, AVG(ORCPTCOST) ORCPTCOST FROM POP10500 GROUP BY PONUMBER, POLNENUM) C ON B.PONUMBER = C.PONUMBER AND B.ORD = C.POLNENUM

LEFT JOIN PM00200 D ON B.VENDORID = D.VENDORID

ORDER BY B.PONUMBER, B.LineNumber

-Trevor
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Help with POP query for smartlist builder

No, what I am actually indicating is that records from the header table could be normally duplicated when joined with the detail. Therefore, I don't think that using this script to build a "smart list" would be a good idea since it might result with "incorrect results" if not well user by end - users.

Lets suppose that your smart list is exported into Excel by one of the users and inserted into Pivot Table for analysis purposes, see how "header" data will be duplicated (which is normal) and result with incorrect data.

Therefore, I believe you could better use Crystal Reports "for instance" to publish your view, through which you could separate (by grouping by) the header from the details data, and ensure that no correct grouping will be perfored through "smart list" as shown above.
Alvaro Rodríguez Ochoa 2,395 on at

Like (0)

Report

RE: Help with POP query for smartlist builder

So, you are saying that this can't be done using a SQL view for use on a smartlist?

P.S. sorry for the dellay in replying, got some server issues yesterday that got me bussy all day.
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Help with POP query for smartlist builder

When joining header and detail table, definition in the header will absolutely show in duplicated records depending on the no of details records, such as (Vendor ID, PO Number .. etc).

Most importantly, fields like "Quantity Ordered" on the purchase order, as well as the "Item Unit Cost" are duplicated. Therefore, Pivoting data on this report could result with incorrect numbers.

Therefore, I would recommend publishing the report on Crystal report for instance, in order to split between the header and details data.