Mohammad R. Daoud: Dynamics GP Reporting Series: SOP Sales Invoice

Sunday, April 25, 2010

Dynamics GP Reporting Series: SOP Sales Invoice

Moving from the financial series into the Sales, sales invoice will need to be sent to the customer, where it has to be designed to represent company image, I have used design similar to the one come with GP, below the SQL command needed:

SQL Command:

SELECT
dbo.SOP10100.SOPNUMBE, dbo.SOP10100.DOCDATE,
dbo.SOP10100.PYMTRMID, dbo.SOP10100.CUSTNMBR,
dbo.SOP10100.CUSTNAME, dbo.SOP10100.CSTPONBR,
dbo.SOP10100.SHIPMTHD, dbo.SOP10100.SLPRSNID,
dbo.SOP10200.ITEMNMBR, dbo.SOP10200.ITEMDESC,
dbo.SOP10200.QTYORDER, dbo.SOP10200.QTYTBAOR,
dbo.SOP10200.QTYTOINV, dbo.SOP10100.CURNCYID,
dbo.SOP10100.CURRNIDX, dbo.SOP10200.UOFM,
dbo.SOP10100.MSTRNUMB, dbo.SOP10106.USRTAB01,
dbo.SOP10106.USERDEF2, dbo.SOP10106.USRDEF03,
RM00102_1.ADDRESS1, RM00102_1.ADDRESS2,
RM00102_1.ADDRESS3, dbo.SOP10100.ORTDISAM,
dbo.SOP10100.ORSUBTOT, dbo.SOP10100.ORFRTAMT,
dbo.SOP10100.ORMISCAMT, dbo.SOP10100.ORTAXAMT,
dbo.SOP10100.ORDOCAMT, dbo.SOP10200.ORUNTPRC,
dbo.SOP10200.OXTNDPRC, dbo.SOP10200.ORMRKDAM,
dbo.SOP10200.CMPNTSEQ
FROM dbo.SOP10100
INNER JOIN dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE
AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE
AND dbo.SOP10100.TRXSORCE = dbo.SOP10200.TRXSORCE
LEFT OUTER JOIN dbo.SOP10106 ON dbo.SOP10100.SOPTYPE = dbo.SOP10106.SOPTYPE
AND dbo.SOP10100.SOPNUMBE = dbo.SOP10106.SOPNUMBE
LEFT OUTER JOIN dbo.RM00102 RM00102_1 ON dbo.SOP10100.PRSTADCD = RM00102_1.ADRSCODE
AND dbo.SOP10100.CUSTNMBR = RM00102_1.CUSTNMBR
WHERE dbo.SOP10100.SOPNUMBE = {?SOPNumber}

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original SOP Sales Invoice:

Crystal Report Design: