I created the exact views while back and called them
SOPHDR(SOP10100/SOP30200) and SOPLNE(SOP10200/SOP30300). I've used them in
Crystal Reports. I'll also post this on my blog for others. Below is the
actual view for SOPHDR:
SELECT SOPTYPE, SOPNUMBE, 'Date_Diff_Days' = DATEDIFF(day, DOCDATE,
GETDATE()), 'Date_Diff_Months' = DATEDIFF(month, DOCDATE, GETDATE()),
'Document_Status' = 'Unposted', ORIGTYPE, ORIGNUMB,
DOCID, DOCDATE, GLPOSTDT, QUOTEDAT, QUOEXPDA, ORDRDATE, INVODATE, BACKDATE,
RETUDATE, ReqShipDate, FUFILDAT, ACTLSHIP, DISCDATE,
DUEDATE, REPTING, TRXFREQU, TIMEREPD, TIMETREP, DYSTINCR, DTLSTREP,
DSTBTCH1, DSTBTCH2, USDOCID1, USDOCID2, DISCFRGT,
ORDAVFRT, DISCMISC, ORDAVMSC, DISAVAMT, ORDAVAMT, DISCRTND, ORDISRTD,
DISTKNAM, ORDISTKN, DSCPCTAM, DSCDLRAM, ORDDLRAT,
DISAVTKN, ORDATKN, PYMTRMID, PRCLEVEL, LOCNCODE, BCHSOURC, BACHNUMB,
CUSTNMBR, CUSTNAME, CSTPONBR, PROSPECT, MSTRNUMB,
PCKSLPNO, PICTICNU, MRKDNAMT, ORMRKDAM, PRBTADCD, PRSTADCD,
CNTCPRSN, ShipToName, ADDRESS1, ADDRESS2, ADDRESS3,
CITY, STATE, ZIPCODE, COUNTRY, PHNUMBR1, PHNUMBR2, PHONE3, FAXNUMBR,
COMAPPTO, COMMAMNT, OCOMMAMT, CMMSLAMT, ORCOSAMT,
NCOMAMNT, ORNCMAMT, SHIPMTHD, TRDISAMT, ORTDISAM, TRDISPCT,
SUBTOTAL, ORSUBTOT, REMSUBTO, OREMSUBT, EXTDCOST,
OREXTCST, FRTAMNT, ORFRTAMT, MISCAMNT, ORMISCAMT, TXENGCLD, TAXEXMT1,
TAXEXMT2, TXRGNNUM, TAXSCHID, TXSCHSRC, BSIVCTTL,
FRTSCHID, FRTTXAMT, ORFRTTAX, FRGTTXBL, MSCSCHID, MSCTXAMT, ORMSCTAX,
MISCTXBL, BKTFRTAM, ORBKTFRT, BKTMSCAM, ORBKTMSC,
BCKTXAMT, OBTAXAMT, TXBTXAMT, OTAXTAMT, TAXAMNT, ORTAXAMT, ECTRX,
DOCAMNT, ORDOCAMT, PYMTRCVD, ORPMTRVD, DEPRECVD,
ORDEPRVD, CODAMNT, ORCODAMT, ACCTAMNT, ORACTAMT, SALSTERR, SLPRSNID,
UPSZONE, TIMESPRT, PSTGSTUS, VOIDSTTS, ALLOCABY,
NOTEINDX, CURNCYID, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE, DENXRATE,
EXCHDATE, TIME1, RTCLCMTD, MCTRXSTT, TRXSORCE,
COMMNTID, REFRENCE, POSTEDDT, PTDUSRID, USER2ENT, CREATDDT, MODIFDT,
Tax_Date, APLYWITH, WITHHAMT, SHPPGDOC, CORRCTN,
SIMPLIFD, DOCNCORR, SEQNCORR, SALEDATE
FROM SOP10100
UNION
SELECT SOPTYPE, SOPNUMBE, 'Date_Diff_Days' = DATEDIFF(day, DOCDATE,
GETDATE()), 'Date_Diff_Months' = DATEDIFF(month, DOCDATE, GETDATE()),
'Document_Status' = 'Posted', ORIGTYPE, ORIGNUMB,
DOCID, DOCDATE, GLPOSTDT, QUOTEDAT, QUOEXPDA, ORDRDATE, INVODATE, BACKDATE,
RETUDATE, ReqShipDate, FUFILDAT, ACTLSHIP, DISCDATE,
DUEDATE, REPTING, TRXFREQU, TIMEREPD, TIMETREP, DYSTINCR, DTLSTREP,
DSTBTCH1, DSTBTCH2, USDOCID1, USDOCID2, DISCFRGT,
ORDAVFRT, DISCMISC, ORDAVMSC, DISAVAMT, ORDAVAMT, DISCRTND, ORDISRTD,
DISTKNAM, ORDISTKN, DSCPCTAM, DSCDLRAM, ORDDLRAT,
DISAVTKN, ORDATKN, PYMTRMID, PRCLEVEL, LOCNCODE, BCHSOURC, BACHNUMB,
CUSTNMBR, CUSTNAME, CSTPONBR, PROSPECT, MSTRNUMB,
PCKSLPNO, PICTICNU, MRKDNAMT, ORMRKDAM, PRBTADCD, PRSTADCD,
CNTCPRSN, ShipToName, ADDRESS1, ADDRESS2, ADDRESS3,
CITY, STATE, ZIPCODE, COUNTRY, PHNUMBR1, PHNUMBR2, PHONE3, FAXNUMBR,
COMAPPTO, COMMAMNT, OCOMMAMT, CMMSLAMT, ORCOSAMT,
NCOMAMNT, ORNCMAMT, SHIPMTHD, TRDISAMT, ORTDISAM, TRDISPCT,
SUBTOTAL, ORSUBTOT, REMSUBTO, OREMSUBT, EXTDCOST,
OREXTCST, FRTAMNT, ORFRTAMT, MISCAMNT, ORMISCAMT, TXENGCLD, TAXEXMT1,
TAXEXMT2, TXRGNNUM, TAXSCHID, TXSCHSRC, BSIVCTTL,
FRTSCHID, FRTTXAMT, ORFRTTAX, FRGTTXBL, MSCSCHID, MSCTXAMT, ORMSCTAX,
MISCTXBL, BKTFRTAM, ORBKTFRT, BKTMSCAM, ORBKTMSC,
BCKTXAMT, OBTAXAMT, TXBTXAMT, OTAXTAMT, TAXAMNT, ORTAXAMT, ECTRX,
DOCAMNT, ORDOCAMT, PYMTRCVD, ORPMTRVD, DEPRECVD,
ORDEPRVD, CODAMNT, ORCODAMT, ACCTAMNT, ORACTAMT, SALSTERR, SLPRSNID,
UPSZONE, TIMESPRT, PSTGSTUS, VOIDSTTS, ALLOCABY,
NOTEINDX, CURNCYID, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE, DENXRATE,
EXCHDATE, TIME1, RTCLCMTD, MCTRXSTT, TRXSORCE,
COMMNTID, REFRENCE, POSTEDDT, PTDUSRID, USER2ENT, CREATDDT, MODIFDT,
Tax_Date, APLYWITH, WITHHAMT, SHPPGDOC, CORRCTN,
SIMPLIFD, DOCNCORR, SEQNCORR, SALEDATE
FROM SOP30200
--
Microsoft Dynamics GP MVP
http://ddelprado.blogspot.com
Post by ***@TurboChefIs it possible to create a SQL view (or anything else) that can combine data
from open and history tables, like SOP10100 and SOP30200? Similar to what
SmartList does for Sales Transactions, but I don't think they create a view.
I'm trying to construct queries to search customer activity across both open
and history files.
--