Dynamics Community Forum Thread Details

Have to include non posted records here

We are using Great Plains.
the issue is that for one customer, they don't have their invoices processed, so they don't get into history. The following SQL creates a report in Reporting Servics, you can see they have incompete data for census and billing, they only have quotes.

So we need to include unposted (but not voided) invoices to this query. might as well make for all rather than to hard code fore the one cust. I am told to include SOP10100 and SOP 10200 - I am not entirely sure how to make this inclusion to the existing working SQL I am not sure which fields I need to link.

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr as UserCatLongDescr_IV40600,
CATS.UserCatLongDescr as UserCatLongDescr_CATS,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source

FROM ((((SOP10200
SOP10200 INNER JOIN IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))

INNER JOIN
IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN
IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR Where SOP10100.DOCDATE between @FromDate and @ToDate and
RM00101.CPRCSTNM = @Customer and CATS.UserCatLongDescr <> ''

UNION ALL

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' as source

FROM ((((SOP30300
SOP30300 LEFT OUTER JOIN IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))

LEFT OUTER JOIN
IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

LEFT OUTER JOIN
IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR
Where SOP30200.DOCDATE between @FromDate and @ToDate and
RM00101.CPRCSTNM = @Customer and CATS.UserCatLongDescr <> ''

You would want to add this at the Bottom. You had 2 distinct selects. Your data from the Open Tables and your data from the History tables. They are combined into 1 dataset with the UNION ALL statement. This adds the third select to the dataset or Unposted / Work Transactions. All I did was copy one of the sections and replaced instances of SOP30200 with SOP10100 and instances of SOP30300 with SOP10200, then added a UNION ALL to it and changed the "History" as Source to "Unposted" as source.

Try adding this to the bottom

UNION ALL

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Unposted' as source
FROM ((((SOP10200
SOP10200 LEFT OUTER JOIN IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))
LEFT OUTER JOIN
IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
LEFT OUTER JOIN
IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR
Where SOP10100.DOCDATE between @FromDate and @ToDate and
RM00101.CPRCSTNM = @Customer and CATS.UserCatLongDescr <> ''

Quick Links

Leaderboard