gistfile1.txt

**RPSO - Sales Order Document
PARAMETERS lcFilter, lcDateRange, lcSortRange, lcAdvFilter, lcOtherVariable, lcSortOrder

***JDM--CR 80887--3/18/02--Need the following variables.
LOCAL lcSql, lcSql1, lcSql2, lcSql3, lcSql4, lcSql5, lcSql6, lcSql7, lcSql8, lcSql9
LOCAL lcSql0, lcSql11, lcSql12, lcSql13, lcSql14, lcSql15, lcSql16, lcSql17, lcSql18, lcSql19
LOCAL lcSql20, lcSql21, lcSql22, lcSql23
LOCAL lnHandle, lnReturn, lcSortRange2


IF 'SOMAST' $ UPPER(lcSortRange) AND 'FSONO' $ UPPER(lcSortRange)
   lcSortRange = STRTRAN(UPPER(lcSortRange),'SOMAST','SORELS')		&& RDZ CR 60659
ENDIF

lnHandle = CURSORGETPROP('ConnectHandle','Somast')
lcfilter = STRTRAN(UPPER(lcFilter),["],['])
lcfilter = IIF(ALLT(lcFilter) = ".T.", STRTRAN(UPPER(lcFilter),[.T.],[1=1]),lcFilter)

lcSql = "SELECT Somast.fsono, somast.fcustno, somast.fstatus, Somast.fprinted, somast.fccurid, "
lcSql1 = "('SLCDPM    ' + somast.fcustno + 'S' + Somast.fshptoaddr) as Syadmaskey, "
lcSql2 = "('SLCDPM    ' + somast.fcustno + 'S' + sorels.fshptoaddr) as syadrelkey, "
lcSql3 = "sorels.finumber, sorels.frelease, SoRels.fshptoaddr, SoRels.flistaxabl, SoRels.flJrDif, "
lcSql4 = "Jomast.fjobno, Jomast.frel_dt, jomast.fcusrchr1, jomast.fcusrchr2, "
lcSql5 = "SoRels.fDueDate, (sorels.fsono + sorels.finumber + sorels.frelease) as Sorelskey, soitem.fcustpart, "
lcSql6 = "(Soitem.fsono + Soitem.finumber) as Soitemkey, SoItem.fordertype, SoItem.fmultiple, SoRels.fORDERQTY, SoRels.funetprice, "
lcSql7 = "SoRels.funettxnpric, SoRels.funeteuropr, Slcdpm.ftype, Somast.fterm, sorels.fcpbtype, "
lcSql8 = "pbdtl.fnamnt AS pbAmt, pbdtl.fntxnamt AS pbTxnAmt, pbdtl.fnEuroAmt AS pbEuroAmt, "
lcSql9 = "pbdtl.filmarkup, pbdtl.fimmarkup, pbdtl.fddate AS pbDate, pbdtl.fitemno AS pbItemNo, CAST(0 AS bit) AS MinPb, "  		
lcSql10 = "SYADDR.fccompany as Scompany, SYADDR.fmstreet as Saddress, SYADDR.fccity as Scity, "
lcSql11 = "SYADDR.fcstate as Sstate, sorels.forderqty - (sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake) as fnboqty, SYADDR.fczip as Szip, "
lcSql12 = "SYADDR.fccountry as Scountry FROM somast INNER JOIN "
lcSql13 = "sorels ON somast.fsono = sorels.fsono LEFT OUTER JOIN "
lcSql14 = "jomast ON sorels.fstatus = jomast.fjobno INNER JOIN "
lcSql15 = "soitem ON sorels.fsono = soitem.fsono AND sorels.finumber = soitem.finumber INNER JOIN "
lcSql16 = "slcdpm ON somast.fcustno = slcdpm.fcustno LEFT OUTER JOIN "
lcSql17 = "syaddr ON syaddr.fcalias = 'SLCDPM' AND syaddr.fcaddrtype = 'S' AND somast.fcustno = syaddr.fcaliaskey AND "
lcSql18 = "sorels.fshptoaddr = syaddr.fcaddrkey LEFT OUTER JOIN "
lcSql19 = "pbdtl ON pbdtl.fcdocno = sorels.fsono AND pbdtl.finumber = sorels.finumber AND "
lcSql20 = "pbdtl.frelsno = sorels.frelease "
lcSql21 = "WHERE " + FIXVFPWHERE(lcFilter) + " AND " + FIXVFPWHERE(lcSortRange) + " "
lcSql22 = "AND " + FIXVFPWHERE(lcAdvFilter) + " "
lcSql23 = "Order by " + lcSortOrder 
		
lnReturn = SQLEXEC(lnHandle, lcSql + lcSql1 + lcSql2 + lcSql3 + lcSql4 + lcSql5 + lcSql6 + lcSql7 + ;
			lcSql8 + lcSql9 + lcSql10 + lcSql11 + lcSql12 + lcSql13 + lcSql14 + lcSql15 + ;
			lcSql16 + lcSql17 + lcSql18 + lcSql19 + lcSql20 + lcSql21 + lcSql22, 'csrsorels')
IF lnReturn < 0 OR RECCOUNT('csrSoRels') <= 0
	RETURN
ENDIF

***DWL 06/06/2002 CR 083884 - added TTOD to the CalcItemTax (fDueDate)
SELECT fsono, fcustno, fstatus, fprinted, fccurid, UPPER(Syadmaskey) AS Syadmaskey, UPPER(syadrelkey) AS syadrelkey, ;
	finumber, frelease, fshptoaddr, flistaxabl, fljrdif, fduedate, UPPER(Sorelskey) AS Sorelskey, ;
	ALLTRIM(fcustpart) AS fcustpart, UPPER(Soitemkey) AS Soitemkey, ;
	fmultiple, fOrderQty, fUNetPrice, fUNetTxnPric, fUnetEuroPr, (IIF(ftype = 'C', 'S', 'D') + fTerm) AS UTTermKey, ;
	IIF(flistaxabl, CalcItemTax('SO', fsono, finumber, frelease, flJrDif, TTOD(fDueDate), fORDERQTY*funetprice), ROUNDCURR(0)) AS fTax, ;
	IIF(flistaxabl AND oCsGenl.fMultiCurr AND NOT EMPTY(fccurid) AND fccurid <> oCsGenl.fccurid, ;
    CalcItemTax('SO', fsono, finumber, frelease, flJrDif, TTOD(fDueDate), fORDERQTY*funettxnpric, fccurid), ROUNDCURR(0)) AS fTxnTax, ;
    IIF(flistaxabl AND oCsGenl.fMultiCurr AND oCsGenl.flEuro AND NOT EMPTY(fccurid) AND fccurid <> oCsGenl.fccurid, ;
	CalcItemTax('SO', fsono, finumber, frelease, flJrDif, TTOD(fDueDate), fORDERQTY*funeteuropr, 'EUR'), ROUNDCURR(0)) AS fEurTax, ;
	fcpbtype, pbAmt, pbTxnAmt, pbEuroAmt, filmarkup, fimmarkup, pbDate, pbItemNo, ;
	IIF(EMPTY(fcPbType), MinPb, MinPbItem('SO', fsono, finumber, frelease, pbitemno)) AS MinPb, ;
	frel_dt, fordertype, fnboqty, Scompany, Saddress, Scity, Sstate, Szip, Scountry ;
FROM csrSorels ;
WHERE fordertype='R';
AND fnboqty > 0 ;
INTO CURSOR SoRels2

lcSortRange2 = '1 = 1'
IF 'FSONO' $ UPPER(lcSortRange)
	lcSortRange2 = STRTRAN(UPPER(lcSortRange),'SORELS','SOMAST')
ENDIF
lcSql = "SELECT SOSHIP.fcinumber, SOSHIP.fcrelease, SOSHIP.fcsono, SOSHIP.fccompany, "
lcSql1 = "SOSHIP.fmstreet, SOSHIP.fccity, SOSHIP.fcstate, SOSHIP.fczip, SOSHIP.fccountry "
lcSql2 = "FROM SOSHIP, SOMAST "
lcSql3 = "WHERE Soship.fcsono = SoMast.fSoNo AND "
lcSql4 = FIXVFPWHERE(lcSortRange2)
		
lnReturn = SQLEXEC(lnHandle, lcSql + lcSql1 + lcSql2 + lcSql3 + lcSql4, 'csrSoShip')
IF lnReturn < 0 
	RETURN
ENDIF


SELECT sorels2.*, right(sorels2.soitemkey,3) as finumber_a, right(sorels2.sorelskey,3) as frelease_a, ;
	csrSoShip.fcsono as sono, csrSoShip.fccompany as company, csrSoShip.fmstreet as address, ;
	csrSoShip.fccity as city, csrSoShip.fcstate as state, csrSoShip.fczip as zip, ;
	csrSoShip.fccountry as country ;
   FROM sorels2 LEFT JOIN csrSoShip ON UPPER(csrSoShip.fcsono) = UPPER(sorels2.fsono) AND csrSoShip.fcinumber = '   ' ;
   INTO CURSOR cursor2

SELECT cursor2.*, csrSoShip.fcinumber as fcinumber, csrSoShip.fcrelease as fcrelease, csrSoShip.fcsono as isono, ;
	csrSoShip.fccompany as icompany, csrSoShip.fmstreet as iaddress, csrSoShip.fccity as icity, ;
	csrSoShip.fcstate as istate, csrSoShip.fczip as izip, csrSoShip.fccountry as icountry ;
	FROM cursor2 ;
	LEFT JOIN csrSoShip ON UPPER(csrSoShip.fcsono) = UPPER(cursor2.fsono) AND ;
	INT(VAL(fcinumber)) = INT(VAL(cursor2.finumber_a)) AND INT(VAL(fcrelease)) = INT(VAL(cursor2.frelease_a)) ;
	INTO CURSOR rpso


RETURN