Created
June 17, 2011 16:29
-
-
Save john2x/1031751 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
**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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment