Ken,
We query using the MSQuery tool with Vantage 6.1. Copy into Notepad,
modify to suit and save as positivepay-ap.dqy:
XLODBC
1
DSN=PROGRESS;UID=[ODBC user id];PWD=[ODBC password;HOST=[Vantage server
name];PORT=6150;DB=mfgsys
SELECT BankAcct_0.CheckingAccount AS 'BankAcctNum',
CheckHed_0.CheckDate, CheckHed_0.CheckNum, CheckHed_0.CheckAmt FROM
PUB.BankAcct BankAcct_0, PUB.CheckHed CheckHed_0, PUB.Company Company_0
WHERE Company_0.Company = CheckHed_0.Company AND CheckHed_0.Company =
BankAcct_0.Company AND CheckHed_0.BankAcctID = BankAcct_0.BankAcctID AND
((BankAcct_0.Company='PMSMC') AND (CheckHed_0.CheckDate>=? And
CheckHed_0.CheckDate<=?) AND (BankAcct_0.CheckingAccount='[Your Account
Number]') AND (CheckHed_0.CheckAmt>0) AND (CheckHed_0.Voided=0) AND
(CheckHed_0.Posted=1)) ORDER BY CheckHed_0.CheckDate,
CheckHed_0.CheckNum
Start Date End Date
9 9
BankAcctNum CheckDate CheckNum CheckAmt
Note that you must replace all the information within square brackets []
with your own information. Double-clicking the resulting file will open
Excel and prompt the user for a data range, then return the data from
the Vantage database.
Here's our payroll query (positivepay-pr.dqy):
XLODBC
1
DSN=PROGRESS;UID=[ODBC user id];PWD=[ODBC password;HOST=[Vantage server
name];PORT=6150;DB=mfgsys
Company_0, PUB.PRCheck PRCheck_0 WHERE Company_0.Company =
PRCheck_0.Company AND PRCheck_0.Company = BankAcct_0.Company AND
PRCheck_0.BankAcctID = BankAcct_0.BankAcctID AND
((Company_0.Company='PMSMC') AND (PRCheck_0.CheckDate>=? And
PRCheck_0.CheckDate<=?) AND (BankAcct_0.CheckingAccount='[Your account
number]') AND (PRCheck_0.CheckAmt>0) AND (PRCheck_0.Voided=0) AND
(PRCheck_0.Posted=1)) ORDER BY PRCheck_0.CheckDate, PRCheck_0.CheckNum
Start Date End Date
9 9
BankAcctNum CheckDate CheckNum CheckAmt
have fun,
juanito
On Wed, 19 Dec 2007 12:32:59 -0800 (PST), "Ken Taylor"
<ktaylorv@...> said:
We query using the MSQuery tool with Vantage 6.1. Copy into Notepad,
modify to suit and save as positivepay-ap.dqy:
XLODBC
1
DSN=PROGRESS;UID=[ODBC user id];PWD=[ODBC password;HOST=[Vantage server
name];PORT=6150;DB=mfgsys
SELECT BankAcct_0.CheckingAccount AS 'BankAcctNum',
CheckHed_0.CheckDate, CheckHed_0.CheckNum, CheckHed_0.CheckAmt FROM
PUB.BankAcct BankAcct_0, PUB.CheckHed CheckHed_0, PUB.Company Company_0
WHERE Company_0.Company = CheckHed_0.Company AND CheckHed_0.Company =
BankAcct_0.Company AND CheckHed_0.BankAcctID = BankAcct_0.BankAcctID AND
((BankAcct_0.Company='PMSMC') AND (CheckHed_0.CheckDate>=? And
CheckHed_0.CheckDate<=?) AND (BankAcct_0.CheckingAccount='[Your Account
Number]') AND (CheckHed_0.CheckAmt>0) AND (CheckHed_0.Voided=0) AND
(CheckHed_0.Posted=1)) ORDER BY CheckHed_0.CheckDate,
CheckHed_0.CheckNum
Start Date End Date
9 9
BankAcctNum CheckDate CheckNum CheckAmt
Note that you must replace all the information within square brackets []
with your own information. Double-clicking the resulting file will open
Excel and prompt the user for a data range, then return the data from
the Vantage database.
Here's our payroll query (positivepay-pr.dqy):
XLODBC
1
DSN=PROGRESS;UID=[ODBC user id];PWD=[ODBC password;HOST=[Vantage server
name];PORT=6150;DB=mfgsys
Company_0, PUB.PRCheck PRCheck_0 WHERE Company_0.Company =
PRCheck_0.Company AND PRCheck_0.Company = BankAcct_0.Company AND
PRCheck_0.BankAcctID = BankAcct_0.BankAcctID AND
((Company_0.Company='PMSMC') AND (PRCheck_0.CheckDate>=? And
PRCheck_0.CheckDate<=?) AND (BankAcct_0.CheckingAccount='[Your account
number]') AND (PRCheck_0.CheckAmt>0) AND (PRCheck_0.Voided=0) AND
(PRCheck_0.Posted=1)) ORDER BY PRCheck_0.CheckDate, PRCheck_0.CheckNum
Start Date End Date
9 9
BankAcctNum CheckDate CheckNum CheckAmt
have fun,
juanito
On Wed, 19 Dec 2007 12:32:59 -0800 (PST), "Ken Taylor"
<ktaylorv@...> said:
> Just wondering if anyone is doing Positive Pay. What is the name of the
> table that stores AP checks to PP information from. Any ideas?
> Thanks for your feedback.