Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 Other Topics
 Visual Foxpro Hell

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-06-23 : 16:33:20
I administer an ERP program called Made2Manage. It was originally written in Foxpro but was later upgraded to a SQL database. This is my primary source to report from. My biggest problem is this, the program itself and the reports written for it are still written in Foxpro. The foxpro is used to generate the SQL statements which are sent to the database. I don't know much in the way of Foxpro. Look at this mess of a report I have to edit. Here is the PRJ File: 



PARAMETER lcfilter, lcdaterange, lcsortrange, lcadvfilter, lcothervariable, lcsortorder
LOCAL lccurrdate


* TMS 03/19/2008 commented out copywrite check 
*!*	lccopyrightfile = osession.csyspath + 'reports\prgs\' + lcsqlcursorname + '.exe'
*!*	IF !FILE(lccopyrightfile)
*!*	     = MESSAGEBOX("You have an unregistered copy of this report.  " + CHR(13) + CHR(10) + "Please contact ADS Information Systems, Inc. @ 952-278-2377", 16, "ADS Information Systems, Inc.")
*!*	     RETURN
*!*	ENDIF
*!*	DO &lcCopyRightFile

IF TYPE('lcRangedesc') = 'L'
     lcrangedesc = ''
ENDIF

IF TYPE('lcFacility') = 'L'
     lcfacility = ''
ENDIF

IF TYPE('lcfilterdesc') = 'L'
     lcfilterdesc = ''
ENDIF

IF TYPE('lcorderdesc') = 'L'
     lcorderdesc = ''
ENDIF

lccurrdate = "'" + DTOC(DATE()) + "'"

PRIVATE lddate
IF (TYPE('lcOtherVariable[2]') = 'C' .AND. EMPTY(lcothervariable(2))) .OR. TYPE('lcOtherVariable[2]') = 'L'
     lddate = DATE() + 3650
ELSE
     lddate = lcothervariable(2)
ENDIF

PRIVATE lcfacility
lcfacility = ""
IF TYPE('lcOtherVariable[1]') = 'C' .AND. !EMPTY(lcothervariable(1))
     lcfacility = lcothervariable(1)
ENDIF
IF !EMPTY(lcfacility) .AND. TYPE('lcFacility') <> 'L'
     sqlfac = 'UPPER(JodBom.cfac) = "' + UPPER(lcfacility) + '"'
ELSE
     sqlfac = '.T.'
ENDIF

lcpart = UPPER(SUBSTR(lcsortrange, AT('"', lcsortrange, 1) + 1, 25))
lcpart1 = UPPER(SUBSTR(lcsortrange, AT('"', lcsortrange, 3) + 1, 25))


WAIT WINDOW NOWAIT "Step 1 of 6, JO BOM, is in process..."
m2mselect('SELECT Jodbom.cFac as Fac, Jodbom.fbompart as part, Jodbom.fbomrev as rev, "       JO Bom " + Jodbom.fjobno + ' + ;
		'IIF(AT("ON HOLD", UPPER(Jomast.fstatus)) > 0, "*", " ") + SPACE(12) AS Demand, ' + ;
		'SPACE(28) AS Supply, ' + ;
		'jodbom.fjobno+jodbom.fbominum AS fKey, ' + ;
		'val(str(iif(Jodbom.flextend=.T.,(Jomast.fquantity*Jodbom.ftotqty)-iif(Jodbom.fqty_iss>0,Jodbom.fqty_iss,0)+Jodbom.fnretpoqty, ' + ;
		'Jodbom.ftotqty-iif(Jodbom.fqty_iss>0,Jodbom.fqty_iss,0)+Jodbom.fnretpoqty),25,10)) AS QtyReqd,Jodbom.fneed_dt AS date, ' + ;
		'Iif(Jodbom.fresponse = "C", "Cus Supl",Jodbom.fbomsource + SPACE(7)) AS Source,Inmast.fbuyer, Inmast.fprodcl, Inmast.fonhand, Inmast.fqtyinspec, Inmast.fsafety, ' + ;
		'Inmast.fgroup,substr(Jomast.fstatus,1,10) as status,Jodbom.fresponse, Jodbom.fpono, Jodbom.fjobno, Jodbom.fbominum, ' + ;
		'{} as podate, Inmast.fdescript, Inmast.fmeasure, Inmast.fleadtime, Inmast.fsource ' + ;
		'FROM m2mdata!Jodbom, m2mdata!Jomast, m2mdata!Inmast ' + ;
		'WHERE UPPER(jodbom.cFac) + UPPER(jodbom.fbompart) + UPPER(jodbom.fbomrev) == Upper(inmast.fac) + Upper(inmast.fpartno) + UPPER(inmast.frev) ' + ;
		'AND UPPER(jodbom.fjobno) == Upper(Jomast.fjobno) ' + ;
		'AND UPPER(Left(Jomast.fstatus,4)) NOT IN ("STAR", "CLOS", "CANC")' + ;
		'AND Inmast.fbulkissue <> "Y" ' + ;
		'AND iif(Jodbom.flextend=.T.,(Jomast.fquantity*Jodbom.ftotqty)-iif(Jodbom.fqty_iss>0,Jodbom.fqty_iss,0)+Jodbom.fnretpoqty,Jodbom.ftotqty-iif(Jodbom.fqty_iss>0,Jodbom.fqty_iss,0)+Jodbom.fnretpoqty) > 0 ' + ;
		'AND ' + lcadvfilter + ' ' + ;
		'AND ' + lcfilter + ' ' + ;
		'And ' + lcsortrange + ' ' + ;
		'AND ' + sqlfac + ' ' + ;
		'AND Jodbom.fneed_dt < (' + v(lddate) + '+1) ' + ;
		'INTO cursor oneA',SET('DATASESSION'), ,'')
		
m2msetflddatatype("date","D","oneA",SET('DATASESSION'))

onebsqlfac = STRTRAN(UPPER(sqlfac), "JODBOM.CFAC", "POITEM.FAC")

m2mselect('SELECT  Poitem.fjokey, ' + ;
		'MAX(Poitem.fpono + poitem.fitemno + poitem.frelsno) as fpokey, ' + ;
		'SUM(Poitem.frcpqty) as totrcpqty, ' + ;
		'Poitem.fjoitm, MAX(Poitem.fpartno) AS fPartNo, MAX(Poitem.frev) AS fRev, MAX(Poitem.fac) AS fac ' + ;
		'FROM Jodbom, Inmast, Jomast, Poitem ' + ;
		'WHERE UPPER(jodbom.cFac) + UPPER(jodbom.fbompart) + UPPER(jodbom.fbomrev) == Upper(inmast.fac) + Upper(inmast.fpartno) + UPPER(inmast.frev) ' + ;
		'AND UPPER(jodbom.fjobno) == Upper(Jomast.fjobno) ' + ;
		'AND UPPER(Jodbom.fjobno) == UPPER(Poitem.fjokey) AND UPPER(Jodbom.fbominum) == UPPER(Poitem.fjoitm) ' + ;
		'AND UPPER(Left(Jomast.fstatus,4)) NOT IN ("STAR", "CLOS", "CANC", "ON H")' + ;
		'AND Inmast.fbulkissue <> "Y" ' + 'AND Poitem.fcategory = "JO" ' + ;
		'AND Poitem.frcpqty > 0 ' + ;
		'AND iif(Jodbom.flextend=.T.,(Jomast.fquantity*Jodbom.ftotqty)-iif(Jodbom.fqty_iss>0,Jodbom.fqty_iss,0)+Jodbom.fnretpoqty,Jodbom.ftotqty-iif(Jodbom.fqty_iss>0,Jodbom.fqty_iss,0)+Jodbom.fnretpoqty) > 0 ' + ;
		'AND ' + lcadvfilter + ' ' + ;
		'AND ' + lcfilter + ' ' + ;
		'And ' + lcsortrange + ' ' + ;
		'AND Jodbom.fneed_dt < (' + v(lddate) + '+1) ' + ;
		'AND ' + onebsqlfac + ' ' + ;
		'GROUP BY Poitem.fjokey, Poitem.fjoitm ' + ;
		'ORDER BY Poitem.fjoitm, Poitem.fjokey ' + ;
		'INTO cursor oneB',SET('DATASESSION'), ,'')
		
SELECT onea.fac, onea.part, onea.rev, onea.demand, onea.supply, onea.fkey, ;
IIF(ISNULL(oneb.fpokey), SPACE(12), oneb.fpokey) AS fpokey, ;
onea.qtyreqd - IIF(ISNULL(oneb.totrcpqty), 0, oneb.totrcpqty) AS qtyreqd, ;
onea.date, onea.source, onea.fbuyer, onea.fprodcl, onea.fgroup, onea.status, ;
onea.fresponse, onea.fpono, onea.fjobno, SPACE(10) AS fsono, onea.podate, ;
onea.fdescript, onea.fmeasure, onea.fleadtime, onea.fsource, onea.fonhand, ;
onea.fqtyinspec, onea.fsafety ;
FROM onea ;
LEFT JOIN oneb ON onea.fjobno = oneb.fjokey AND onea.fbominum = oneb.fjoitm ;
WHERE onea.qtyreqd - IIF(ISNULL(oneb.totrcpqty), 0, oneb.totrcpqty) > 0 ;
INTO CURSOR One


WAIT WINDOW NOWAIT "Step 2 of 6, SO Items, is in process..."
PRIVATE lcsortrange2
lcsortrange2 = STRTRAN(UPPER(lcsortrange), "INMAST.FPARTNO", "SORELS.FPARTNO")
lcsortrange2 = STRTRAN(lcsortrange2, "INMAST.FREV", "SORELS.FREV")
twoasqlfac = STRTRAN(UPPER(sqlfac), "JODBOM.CFAC", "SOITEM.FAC")

m2mselect('SELECT Soitem.fac AS Fac, LEFT(Sorels.fpartno,25) as part, ' + ;
'SoRels.fpartrev as rev, ' + '' + CHR(34) + '     SO ' + CHR(34) + ;
'+Sorels.fsono + SPACE(2) + LEFT(Somast.fCompany,20) as demand, ' + CHR(34) + '                            ' + CHR(34) + ' as supply, ' + ;
'sorels.fsono + sorels.finumber + sorels.frelease + SPACE(2)  as fkey, ' + ;
'val(str(Sorels.forderqty-Sorels.fshipbook-Sorels.fshipbuy-Sorels.fshipmake,25,10)) as qtyreqd, ' + ;
'Sorels.fduedate as date, Soitem.fsource+' + CHR(34) + ' ' + CHR(34) + '+Sorels.fpostatus as source, ' + ;
'IIF(ISNULL(Inmast.fbuyer),' + CHR(34) + '   ' + CHR(34) + ',Inmast.fbuyer) AS fbuyer, ' + ;
'IIF(ISNULL(Inmast.fprodcl),' + CHR(34) + '  ' + CHR(34) + ',Inmast.fprodcl) AS fprodcl, ' + ;
'IIF(ISNULL(Inmast.fgroup),' + CHR(34) + '      ' + CHR(34) + ',Inmast.fgroup) AS fgroup, ' + ;
'IIF(ISNULL(Inmast.fdescript),' + CHR(34) + '                                   ' + CHR(34) + ',Inmast.fdescript) AS fdescript, ' + ;
'IIF(ISNULL(Inmast.fmeasure),' + CHR(34) + '   ' + CHR(34) + ',Inmast.fmeasure) AS fmeasure, ' + ;
'IIF(ISNULL(Inmast.fleadtime),0,Inmast.fleadtime) AS fleadtime, ' + ;
'IIF(ISNULL(Inmast.fsource),' + CHR(34) + ' ' + CHR(34) + ',Inmast.fsource) AS fsource, ' + ;
'IIF(ISNULL(Inmast.fonhand),0,Inmast.fonhand) AS fonhand, ' + 'IIF(ISNULL(Inmast.fqtyinspec),0,Inmast.fqtyinspec) AS fqtyinspec, ' + ;
'IIF(ISNULL(Inmast.fsafety),0,Inmast.fsafety) AS fsafety, ' + 'substr(Somast.fstatus,1,10) as status, ' + CHR(34) + ' ' + CHR(34) + ' as fresponse, ' + ;
'Soitem.fsono + SPACE(4) AS fsono, Sorels.fpostatus as fpono, ' + '{} as podate ' + ;
'FROM m2mdata!Sorels ' + ;
'JOIN m2mdata!Somast ON UPPER(Somast.fsono) = Upper(Sorels.fsono) ' + ;
'JOIN m2mdata!Soitem ON UPPER(Soitem.fsono) = UPPER(Sorels.fsono) AND UPPER(Soitem.finumber) = UPPER(Sorels.finumber) ' + ;
'LEFT OUTER JOIN m2mdata!Inmast ON Inmast.fac = Soitem.fac AND Inmast.fpartno = Sorels.fpartno AND Inmast.frev = Sorels.fpartrev ' + ;
'WHERE UPPER(Somast.fstatus) = ' + CHR(34) + 'OPEN' + CHR(34) + ' ' + ;
'AND Sorels.forderqty-Sorels.fshipbook-Sorels.fshipbuy-Sorels.fshipmake>0 ' + ;
'AND Sorels.fmasterrel=.F. ' + ;
'AND ' + lcadvfilter + ' ' + ;
'AND ' + lcfilter + ' ' + ;
'And ' + lcsortrange2 + ' ' + ;
'AND Sorels.fduedate < (' + v(lddate) + '+1) ' + ;
'AND ' + twoasqlfac + ' ' + ;
'INTO cursor twoA',SET('DATASESSION'), ,'')

m2msetflddatatype("date","D","twoA",SET('DATASESSION'))

m2mselect('SELECT  Poitem.fsokey, ' + ;
'MAX(Poitem.fpono+poitem.fitemno+poitem.frelsno) AS fpokey, ' + ;
'SUM(Poitem.frcpqty) AS totrcpqty, ' + ;
'Poitem.fsoitm, MAX(Poitem.fac) AS Fac, ' + ;
'MAX(Poitem.fpartno) AS fPartNo, ' + ;
'MAX(Poitem.frev) AS fRev ' + ;
'FROM Sorels, Somast, Soitem, Inmast, POItem ' + ;
'WHERE Upper(Somast.fsono) == Upper(Sorels.fsono) ' + ;
'AND Upper(Soitem.fsono)+Upper(Soitem.finumber) == Upper(Sorels.fsono)+Upper(Sorels.finumber) ' + ;
'AND Upper(inmast.fac) + Upper(Inmast.fpartno) + Upper(Inmast.frev) == Upper(Soitem.fac) + Upper(Sorels.fpartno) + Upper(Sorels.fpartrev) ' + ;
'AND Upper(Sorels.fpartno) = Upper(Poitem.fpartno) AND Upper(SoRels.fpartrev) = Upper(PoItem.frev) AND Upper(sorels.fsono) = Upper(Poitem.fsokey) ' + ;
'AND UPPER(Somast.fstatus) = ' + CHR(34) + 'OPEN' + CHR(34) + ' ' + ;
'AND Sorels.forderqty-Sorels.fshipbook-Sorels.fshipbuy-Sorels.fshipmake>0 ' + ;
'AND Sorels.fmasterrel=.F. ' + ;
'And Poitem.fcategory = "SO" ' + ;
'And Poitem.frcpqty > 0 ' + ;
'AND ' + lcadvfilter + ' ' + ;
'AND ' + lcfilter + ' ' + ;
'And ' + lcsortrange2 + ' ' + ;
'AND Sorels.fduedate < (' + v(lddate) + '+1) ' + ;
'AND ' + twoasqlfac + ' ' + ;
'GROUP BY Poitem.fsokey, Poitem.fsoitm ' + ;
'ORDER BY Poitem.fsoitm, Poitem.fsokey ' + ;
'INTO CURSOR twoB',SET('DATASESSION'), ,'')

SELECT twoa.fac, twoa.part, twoa.rev, twoa.demand, twoa.supply, twoa.fkey, ;
(IIF(ISNULL(twob.fpokey), SPACE(12), twob.fpokey)) AS fpokey, ;
(twoa.qtyreqd - IIF(ISNULL(twob.totrcpqty), 0, twob.totrcpqty)) AS qtyreqd, ;
twoa.date, twoa.source, IIF(ISNULL(twoa.fbuyer), SPACE(3), twoa.fbuyer) AS fbuyer, ;
twoa.fprodcl, twoa.fgroup, twoa.status, twoa.fresponse, twoa.fpono, twoa.fsono, ;
SPACE(10) AS fjobno, twoa.podate, twoa.fdescript, twoa.fmeasure, twoa.fleadtime, ;
twoa.fsource, twoa.fonhand, twoa.fqtyinspec, twoa.fsafety ;
FROM twoA ;
LEFT JOIN twoB ON twoa.fac = twob.fac AND twoa.part = twob.fpartno AND twoa.rev = twob.frev AND twoa.fsono = twob.fsokey ;
WHERE qtyreqd - IIF(ISNULL(totrcpqty), 0, totrcpqty) > 0 ;
INTO CURSOR Two


WAIT WINDOW NOWAIT "Step 3 of 6, JO Supply, is in process..."

threecsqlfac = STRTRAN(UPPER(sqlfac), "JODBOM.CFAC", "JOMAST.FAC")

m2mselect('SELECT Jomast.fac as fac, Jomast.fpartno as part, Jomast.fpartrev as rev, ' + ;
		'' + CHR(34) + '                                    ' + CHR(34) + ' as demand, ' + ;
		'padr(' + CHR(34) + 'JO ' + CHR(34) + '+Jomast.fjobno+IIF(' + CHR(39) + 'ON HOLD' + CHR(39) + ;
		' $ UPPER(JoMast.fstatus),' + CHR(39) + '*' + CHR(39) + ',' + CHR(39) + '' + CHR(39) + ;
		')+iif(Jomast.fsono=' + CHR(34) + '     ' + CHR(34) + ',' + CHR(34) + ' ' + CHR(34) + ',' + CHR(34) + ;
		' for SO ' + CHR(34) + '+Jomast.fsono),28) as supply, ' + 'Jodrtg.fjobno+str(jodrtg.foperno,4) as fkey, ;
		' + CHR(34) + '            ' + CHR(34) + ' as fpokey, ' + ;
		'VAL(STR(Jomast.fquantity-sum(Jodrtg.fnqty_move),25,10)) as qtyreqd, ' + ;
		'Inmast.fbuyer, Inmast.fprodcl, Inmast.fgroup, ' + ;
		'Jomast.fddue_date as date,iif(Jomast.ftype=' + CHR(34) + 'I' + CHR(34) + ;
		'.AND.Jomast.fsub_from=' + CHR(34) + '     ' + CHR(34) + ',;
		' + CHR(34) + 'S       ' + CHR(34) + ',' + CHR(34) + 'M       ' + CHR(34) + ;
		')  as source, substr(Jomast.fstatus,1,10) as status, Jodrtg.foperno, ;
		' + CHR(34) + ' ' + CHR(34) + ' as fresponse, ' + '' + CHR(34) + '      ' + CHR(34) + ;
		' as fpono, jomast.fjobno, ' + CHR(34) + '          ' + CHR(34) + ' as fsono, ' + ;
		'{} as podate, Inmast.fdescript, Inmast.fmeasure, Inmast.fleadtime, Inmast.fsource, Inmast.fonhand, Inmast.fqtyinspec, Inmast.fsafety ' + ;
		'FROM m2mdata!Jomast, m2mdata!Jodrtg, m2mdata!Inmast ' + ;
		'WHERE UPPER(Jodrtg.fjobno) == UPPER(Jomast.fjobno) ' + ;
		'AND Upper(Inmast.fac) + Upper(Inmast.fpartno) + Upper(Inmast.frev) == Upper(Jomast.fac) + Upper(Jomast.fpartno) + Upper(Jomast.fpartrev) ' + ;
		'AND (Jomast.ftype=' + CHR(34) + 'C' + CHR(34) + ;
		' or (Jomast.ftype=' + CHR(34) + 'I' + CHR(34) + ;
		' and Jomast.fitype = ' + CHR(34) + '1' + CHR(34) + ')) ' + ;
		'AND Upper(Left(Jomast.fstatus,4)) <> ' + CHR(34) + 'STAR' + CHR(34) + ' ' + ;
		'AND Upper(Left(Jomast.fstatus,4)) <> ' + CHR(34) + 'CLOS' + CHR(34) + ' ' + ;
		'AND Upper(Left(Jomast.fstatus,4)) <> ' + CHR(34) + 'CANC' + CHR(34) + ' ' + ;
		'AND Upper(Left(Jomast.fstatus,4)) <> ' + CHR(34) + 'ON H' + CHR(34) + ' ' + ;
		'AND Jomast.fddue_date < (' + v(lddate) + '+1) ' + ;
		'AND ' + lcadvfilter + ' ' + ;
		'AND ' + lcfilter + ' ' + ;
		'And ' + lcsortrange + ' ' + ;
		'AND ' + threecsqlfac + ' ' + ;
		'GROUP BY Jomast.fjobno ' + ;
		'INTO cursor threec',SET('DATASESSION'), ,'')
		
m2msetflddatatype("date","D","threec",SET('DATASESSION'))

SELECT * FROM threec WHERE threec.qtyreqd > 0 INTO CURSOR three


WAIT WINDOW NOWAIT "Step 4 of 6, PO Receipts, is in process..."

foursqlfac = STRTRAN(UPPER(sqlfac), "JODBOM.CFAC", "POITEM.FAC")

m2mselect('SELECT Poitem.fac AS Fac, Poitem.fpartno as part,Poitem.frev as rev,' + CHR(34) + '                                    ' + CHR(34) + ;
' as demand, ' + 'padr(' + CHR(34) + 'PO ' + CHR(34) + '+Poitem.fpono+' + CHR(34) + ' ' + CHR(34) + ;
'+Poitem.fcategory+alltrim(Poitem.fjokey+Poitem.fsokey),28) as supply, ' + '' + CHR(34) + '              ' + CHR(34) + ;
' as fkey, Poitem.fpono+poitem.fitemno+poitem.frelsno as fpokey, ' + ;
'VAL(STR(Poitem.fordqty-Poitem.frcpqty,25,10)) as qtyreqd, Poitem.flstpdate as date, ' + ;
'Iif(Pomast.fmethod=' + CHR(34) + '3' + CHR(34) + ',' + CHR(34) + 'Cus Supl' + CHR(34) + ;
',IIF(Poitem.fcategory=' + CHR(34) + 'INV' + CHR(34) + ',' + CHR(34) + 'S       ' + CHR(34) + ;
',' + CHR(34) + 'B       ' + CHR(34) + ')) as source, ' + 'IIF(ISNULL(Inmast.fbuyer),' + CHR(34) + '   ' + CHR(34) + ;
',Inmast.fbuyer) AS fbuyer, ' + 'IIF(ISNULL(Inmast.fprodcl),' + CHR(34) + '  ' + CHR(34) + ;
',Inmast.fprodcl) AS fprodcl, ' + 'IIF(ISNULL(Inmast.fgroup),' + CHR(34) + '      ' + CHR(34) + ;
',Inmast.fgroup) AS fgroup, ' + 'substr(Pomast.fstatus,1,10) as status,Iif(Pomast.fmethod=' + CHR(34) + ;
'3' + CHR(34) + ',' + CHR(34) + 'C' + CHR(34) + ',' + CHR(34) + ' ' + CHR(34) + ') as fresponse, ' + ;
'Pomast.fpono, ' + CHR(34) + '          ' + CHR(34) + ' as fjobno, ' + CHR(34) + '          ' + CHR(34) + ;
' as fsono, ' + 'Poitem.forgpdate as podate, ' + 'IIF(ISNULL(Inmast.fdescript),' + CHR(34) + ;
'                                   ' + CHR(34) + ',Inmast.fdescript) AS fdescript, ' + ;
'IIF(ISNULL(Inmast.fmeasure),' + CHR(34) + '   ' + CHR(34) + ',Inmast.fmeasure) AS fmeasure, ' + ;
'IIF(ISNULL(Inmast.fleadtime),0,Inmast.fleadtime) AS fleadtime, ' + 'IIF(ISNULL(Inmast.fsource),' + CHR(34) + ' ' + CHR(34) + ;
',Inmast.fsource) AS fsource, ' + 'IIF(ISNULL(Inmast.fonhand),0,Inmast.fonhand) AS fonhand, ' + ;
'IIF(ISNULL(Inmast.fqtyinspec),0,Inmast.fqtyinspec) AS fqtyinspec, ' + ;
'IIF(ISNULL(Inmast.fsafety),0,Inmast.fsafety) AS fsafety ' + ;
'FROM m2mdata!Poitem ' + ;
'JOIN  m2mdata!Pomast ON UPPER(Pomast.fpono) = UPPER(Poitem.fpono) ' + ;
'LEFT OUTER JOIN m2mdata!Inmast ON UPPER(Inmast.fac) + UPPER(Inmast.fpartno) + UPPER(Inmast.frev) = UPPER(Poitem.fac) + UPPER(Poitem.fpartno) + UPPER(Poitem.frev) ' + ;
'WHERE Poitem.fordqty- Poitem.frcpqty>0 ' + ;
'AND Upper(Pomast.fstatus) = ' + CHR(34) + 'OPEN' + CHR(34) + ' ' + ;
'AND inlist(Poitem.fcategory,' + CHR(34) + 'JO' + CHR(34) + ',' + CHR(34) + 'SO' + CHR(34) + ',' + CHR(34) + 'INV' + CHR(34) + ') ' + ;
'AND (Poitem.fmultirls<>' + CHR(34) + 'Y' + CHR(34) + ' OR (Poitem.fmultirls=' + CHR(34) + 'Y' + CHR(34) + ;
' AND Poitem.frelsno<>' + CHR(34) + '  0' + CHR(34) + ')) ' + 'AND ' + lcadvfilter + ' ' + ;
'AND ' + lcfilter + ' ' + 'And ' + lcsortrange + ' ' + 'AND Poitem.flstpdate < (' + v(lddate) + '+1) ' + ;
'AND ' + foursqlfac + ' ' + 'INTO cursor four',SET('DATASESSION'), ,'')

m2msetflddatatype("date","D","four",SET('DATASESSION'))


WAIT WINDOW NOWAIT "Step 5 of 6, Item Master Safety Stock, is in process..."

fivesqlfac = STRTRAN(UPPER(sqlfac), "JODBOM.CFAC", "INMAST.FAC")

m2mselect('Select Inmast.fac AS fac, Inmast.fpartno as part, Inmast.frev as rev, ' + CHR(34) + ;
'Safety Stock                        ' + CHR(34) + ' as demand, ' + 'padr(' + CHR(34) + ;
' ' + CHR(34) + ',28) as supply, ' + '' + CHR(34) + '              ' + CHR(34) + ;
' as fkey, ' + CHR(34) + '            ' + CHR(34) + ' as fpokey, ' + 'Inmast.fsafety as qtyreqd, ' + CHR(34) + 'S       ' + CHR(34) + ;
' as source, ' + lccurrdate + ' as date, ' + CHR(34) + '          ' + CHR(34) + ' as status, Inmast.fbuyer, Inmast.fprodcl, ' + ;
'Inmast.fgroup, ' + CHR(34) + ' ' + CHR(34) + ' as fresponse, ' + CHR(34) + '      ' + CHR(34) + ' as fpono, ' + CHR(34) + ;
'          ' + CHR(34) + ' as fjobno, ' + CHR(34) + '          ' + CHR(34) + ' as fsono, ' + ;
'{} as podate, Inmast.fdescript, Inmast.fmeasure, Inmast.fleadtime, Inmast.fsource, Inmast.fonhand, Inmast.fqtyinspec, Inmast.fsafety ' + ;
'From m2mdata!Inmast ' + ;
'Where Inmast.fsafety > 0 ' + ;
'AND ' + lcadvfilter + ' ' + ;
'AND ' + lcfilter + ' ' + ;
'And ' + lcsortrange + ' ' + ;
'AND ' + fivesqlfac + ' ' + ;
'Into cursor five',SET('DATASESSION'), ,'')

m2msetflddatatype("date","D","five",SET('DATASESSION'))


WAIT WINDOW NOWAIT "Step 6 of 6, Totaling, is in process..."
sort = "     "
IF lcsortorder = "INMAST.FPARTNO"
     sort = "1,2,3,10"
ENDIF
IF lcsortorder = "INMAST.FBUYER"
     sort = "12,1,2,3,10"
ENDIF
IF lcsortorder = "INMAST.FPRODCL"
     sort = "13,1,2,3,10"
ENDIF
IF lcsortorder = "INMAST.FGROUP"
     sort = "14,1,2,3,10"
ENDIF

ddd = lddate + 1

m2mselect('SELECT  fac, part, rev, demand, supply, fkey, fpokey, qtyreqd, source, date, status, fbuyer, ' + ;
		  'fprodcl, fgroup, ddd as dddate,fresponse, fpono, fjobno, fsono, podate, ' + ;
		  'fdescript, fmeasure, fleadtime, fsource, fonhand, fqtyinspec, fsafety ' + ;
		  'FROM one ' + ;
		  'UNION ALL ' + ;
		  'SELECT fac, part, rev, demand, supply, fkey, fpokey, qtyreqd, source, date, status, fbuyer, ' + ;
		  'fprodcl, fgroup, ddd as dddate,fresponse, fpono, fjobno, fsono, podate, ' + ;
		  'fdescript, fmeasure, fleadtime, fsource, fonhand, fqtyinspec, fsafety ' + ;
		  'FROM two ' + ;
		  'UNION ALL ' + ;
		  'SELECT fac, part, rev, demand, supply, fkey, fpokey, qtyreqd, source, date, status, fbuyer, ' + ;
		  'fprodcl, fgroup, ddd as dddate,fresponse, fpono, fjobno, fsono, podate, ' + ;
		  'fdescript, fmeasure, fleadtime, fsource, fonhand, fqtyinspec, fsafety ' + ;
		  'FROM three ' + ;
		  'UNION ALL ' + ;
		  'SELECT fac, part, rev, demand, supply, fkey, fpokey, qtyreqd, source, date, status, fbuyer, ' + ;
		  'fprodcl, fgroup, ddd as dddate,fresponse, fpono, fjobno, fsono, podate, ' + ;
		  'fdescript, fmeasure, fleadtime, fsource, fonhand, fqtyinspec, fsafety ' + ;
		  'FROM four ' + ;
		  'UNION ALL ' + ;
		  'SELECT fac, part, rev, demand, supply, fkey, fpokey, qtyreqd, source, date, status, fbuyer, ' + ;
		  'fprodcl, fgroup, ddd as dddate,fresponse, fpono, fjobno, fsono, podate, ' + ;
		  'fdescript, fmeasure, fleadtime, fsource, fonhand, fqtyinspec, fsafety ' + 'FROM five ' + ;
		  'ORDER BY ' + sort + ' ' + ;
		  'INTO cursor rpmavla',SET('DATASESSION'), ,'',2)


WAIT WINDOW TIMEOUT 0.1 ""

CREATE CURSOR rpmavl (part C (25), rev C (3), demand C (36), supply C (28), fkey C (14), ;
	fpokey C (12), qtyreqd N (14, 5), source C (8), date D, status C (10), fbuyer C (3), ;
	fprodcl C (2), fgroup C (6), dddate D, fresponse C (1), fpono C (6), fjobno C (10), ;
	fsono C (10), podate D, fac C (20), fdescript C (35), fmeasure C (3), fleadtime N (7, 1), ;
	fsource C (1), fonhand N (15, 5), fqtyinspec N (15, 5), fsafety N (15, 5), onhnd M)

LOCAL lnhandle, lcsql, holdpart
lnhandle = CURSORGETPROP("ConnectHandle", "SOMAST")

SELECT rpmavla
GOTO TOP
SCATTER MEMO MEMVAR
DO WHILE !EOF()
     holdpart = rpmavla.fac + rpmavla.part + rpmavla.rev
     lclocation = ""
     lcsql = "SELECT * FROM inonhd WHERE fac+fpartno+fpartrev = '" + holdpart + "'"
     IF SQLEXEC(lnhandle, lcsql, "csrInOnHd") > 0
          IF RECCOUNT("csrInOnHd") > 0
               lclocation = "On Hand by Location   "
               SELECT csrinonhd
               GOTO TOP
               DO WHILE !EOF()
                    lclocation = lclocation + csrinonhd.flocation + SPACE(1) + csrinonhd.fbinno + SPACE(1) + STR(csrinonhd.fonhand, 14, 2) + CHR(13) + CHR(10) + SPACE(22)
                    SKIP
               ENDDO
          ENDIF
     ENDIF
     SELECT rpmavl
     APPEND BLANK
     GATHER MEMO MEMVAR
     REPLACE onhnd WITH lclocation
     SELECT rpmavla
     SKIP
     SCATTER MEMO MEMVAR
ENDDO
ENDPROC


Can anyone offer a suggestion as to how to approach this pile of crap?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2008-06-24 : 09:14:27
You haven't said what the problem is that you are looking to solve.....slow, not-working, generating error messges, unreadable?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-06-24 : 09:39:10
I need to add a field from a table called poitem_ext. It joins to the poitem table ON poitem.identity_column = poitem_ext.fKey_ID.

However, since it is a myriad of cursors, I have no idea how to do this.

I would greatly appreciate any advice.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2008-06-24 : 10:08:50
Gasoline and a match?

Jim
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-06-25 : 09:00:22
quote:
Originally posted by jimf

Gasoline and a match?

Jim


Thanks for the input Jim. Any Helpful suggestions?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2008-06-25 : 09:39:27
Wouldn't this topic have been better posted in one of the forums where people answer questions, like "Other Development Tools"?

Of course, if you are looking to have your problems mocked, then you posted in the right spot...



CODO ERGO SUM
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-06-25 : 09:45:34
Fine, would one of the admins like to move it or shall I just create a new thread?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-06-25 : 10:37:25
you can move it by using edit topic button at the left side of topic header.

if you can't then tell us in which forum you'd like it and we'll move it.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-06-25 : 10:53:35
quote:
Originally posted by spirit1

you can move it by using edit topic button at the left side of topic header.

if you can't then tell us in which forum you'd like it and we'll move it.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!


It doesn't matter. That forum is dead anyway.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-06-25 : 10:55:46
ok...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-06-27 : 12:54:10
It took me 4 days, but I finally solved this issue.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site