From there, you can link to the AP subledger (might be PM20000 ?), also the statement of accounts (GL1????).
I'm doing this from memory, so I can't look anything up for you, but you will definitely need to start with the GL.
SELECT D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
D.PSTGDATE GL_Posting_Date,
K.DOCDATE Document_Date,
case K.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
end Document_Type,
G.ACTNUMST Account_Number,
A.ACTDESCR Account_Name,
case D.DISTTYPE
when 1 then 'Cash'
when 2 then 'Payable'
when 3 then 'Discount Available'
when 4 then 'Discount Taken'
when 5 then 'Finance Charge'
when 6 then 'Purchase'
when 7 then 'Trade Disc.'
when 8 then 'Misc. Charge'
when 9 then 'Freight'
when 10 then 'Taxes'
when 11 then 'Writeoffs'
when 12 then 'Other'
when 13 then 'GST Disc'
when 14 then 'PPS Amount'
when 16 then 'Round'
when 17 then 'Realized Gain'
when 18 then 'Realized Loss'
when 19 then 'Due To'
when 20 then 'Due From'
end Distribution_Type,
D.DEBITAMT Debit_Amount,
D.CRDTAMNT Credit_Amount,
D.DistRef Distribution_Reference,
T.BACHNUMB Batch_ID,
T.TRXDSCRN Trx_Description,
T.STAT Trx_Status,
D.VCHRNMBR Voucher_Number,
D.CURNCYID Currency_ID,
coalesce(D.XCHGRATE,0) Exchange_Rate,
D.ORDBTAMT Originating_Debit_Amount,
D.ORCRDAMT Originating_Credit_Amount,
case T.VOIDED
when 0 then 'No'
when 1 then 'Yes'
end Voided,
T.PORDNMBR PO_Number,
-- **********added 1099 columns**************
T.TEN99AMNT [1099 Amount],
case T.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
else ''
end [1099 Type],
T.TEN99BOXNUMBER [1099 Box Number]
FROM
-- all open trx distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
FROM PM10100
UNION ALL
-- all historical trx distributions
SELECT P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
FROM PM30600 P
LEFT OUTER JOIN -- historical exchange rate
MC020103 M
ON P.VCHRNMBR = M.VCHRNMBR
AND P.DOCTYPE = M.DOCTYPE) D
-- add document number and type
LEFT OUTER JOIN
PM00400 K
ON D.VCHRNMBR = K.CNTRLNUM
AND D.CNTRLTYP = K.CNTRLTYP
--add GL account number
LEFT OUTER JOIN
GL00105 G
ON D.DSTINDX = G.ACTINDX
--add status, batch and trx description
-- **********added 1099 columns to all tables in query below**************
LEFT OUTER JOIN
(SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
FROM PM10000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
FROM PM20000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
FROM PM30200
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
FROM PM10300
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', TRXDSCRN, TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
FROM PM10400) T
ON T.VCHRNMBR = D.VCHRNMBR
AND T.CNTRLTYP = D.CNTRLTYP
--add GL account name
LEFT OUTER JOIN
GL00100 A
ON A.ACTINDX = D.DSTINDX
--add vendor name
LEFT OUTER JOIN
PM00200 N
ON N.VENDORID = D.VENDORID
SELECT D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
D.PSTGDATE GL_Posting_Date,
K.DOCDATE Document_Date,
case K.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
end Document_Type,
G.ACTNUMST Account_Number,
A.ACTDESCR Account_Name,
case D.DISTTYPE
when 1 then 'Cash'
when 2 then 'Payable'
when 3 then 'Discount Available'
when 4 then 'Discount Taken'
when 5 then 'Finance Charge'
when 6 then 'Purchase'
when 7 then 'Trade Disc.'
when 8 then 'Misc. Charge'
when 9 then 'Freight'
when 10 then 'Taxes'
when 11 then 'Writeoffs'
when 12 then 'Other'
when 13 then 'GST Disc'
when 14 then 'PPS Amount'
when 16 then 'Round'
when 17 then 'Realized Gain'
when 18 then 'Realized Loss'
when 19 then 'Due To'
when 20 then 'Due From'
end Distribution_Type,
D.DEBITAMT Debit_Amount,
D.CRDTAMNT Credit_Amount,
D.DistRef Distribution_Reference,
T.BACHNUMB Batch_ID,
T.TRXDSCRN Trx_Description,
T.STAT Trx_Status,
D.VCHRNMBR Voucher_Number,
D.CURNCYID Currency_ID,
coalesce(D.XCHGRATE,0) Exchange_Rate,
D.ORDBTAMT Originating_Debit_Amount,
D.ORCRDAMT Originating_Credit_Amount,
case T.VOIDED
when 0 then 'No'
when 1 then 'Yes'
end Voided,
T.PORDNMBR PO_Number,
-- **********added 1099 columns**************
T.TEN99AMNT [1099 Amount],
case T.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
else ''
end [1099 Type],
T.TEN99BOXNUMBER [1099 Box Number]
FROM
-- all open trx distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
FROM PM10100
UNION ALL
-- all historical trx distributions
SELECT P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
FROM PM30600 P
LEFT OUTER JOIN -- historical exchange rate
MC020103 M
ON P.VCHRNMBR = M.VCHRNMBR
AND P.DOCTYPE = M.DOCTYPE) D
-- add document number and type
LEFT OUTER JOIN
PM00400 K
ON D.VCHRNMBR = K.CNTRLNUM
AND D.CNTRLTYP = K.CNTRLTYP
--add GL account number
LEFT OUTER JOIN
GL00105 G
ON D.DSTINDX = G.ACTINDX
--add status, batch and trx description
-- **********added 1099 columns to all tables in query below**************
LEFT OUTER JOIN
(SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT,
CASE
WHEN TEN99AMNT > 0 THEN 4
ELSE 0
END AS TEN99TYPE,
CASE
WHEN TEN99AMNT > 0 THEN 7
ELSE 0
END AS TEN99BOXNUMBER
FROM PM10000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT,
CASE
WHEN TEN99AMNT > 0 THEN 4
ELSE 0
END AS TEN99TYPE,
CASE
WHEN TEN99AMNT > 0 THEN 7
ELSE 0
END AS TEN99BOXNUMBER
FROM PM20000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT,
CASE
WHEN TEN99AMNT > 0 THEN 4
ELSE 0
END AS TEN99TYPE,
CASE
WHEN TEN99AMNT > 0 THEN 7
ELSE 0
END AS TEN99BOXNUMBER
FROM PM30200
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
FROM PM10300
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
FROM PM10400) T
ON T.VCHRNMBR = D.VCHRNMBR
AND T.CNTRLTYP = D.CNTRLTYP
--add GL account name
LEFT OUTER JOIN
GL00100 A
ON A.ACTINDX = D.DSTINDX
--add vendor name
LEFT OUTER JOIN
PM00200 N
ON N.VENDORID = D.VENDORID
Vendor_ID Vendor_Name Doc_Num GL_Post_Date Document_Type Account_Number Account_Name Distribution_Type Debit Credit Trx_Status
Shield Shield Inc. Mar-13 3/7/2013 Invoice 000-2000 ACCOUNTS PAYABLE Payable 0 566.1 History
NULL Mar-13 3/7/2013 Invoice 500-8344 EMPL BENE LIFE Purchase 413 0 History
NULL Mar-13 3/7/2013 Invoice 700-8344 EMPL BENE LIFE Purchase 107.74 0 History
NULL Mar-13 3/7/2013 Invoice 000-1140 RECE DUE FROM Purchase 377.09 0 History
National NAT'L LIFE INS Mar-13 3/7/2013 Invoice 000-2000 ACCOUNTS PAYABLE Payable 0 897.83 History
select * from PM10100 where VENDORID = ''
select * from PM30600 where VENDORID = ''
SELECT T.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
D.PSTGDATE GL_Posting_Date,
K.DOCDATE Document_Date,
case K.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
end Document_Type,
G.ACTNUMST Account_Number,
A.ACTDESCR Account_Name,
case D.DISTTYPE
when 1 then 'Cash'
when 2 then 'Payable'
when 3 then 'Discount Available'
when 4 then 'Discount Taken'
when 5 then 'Finance Charge'
when 6 then 'Purchase'
when 7 then 'Trade Disc.'
when 8 then 'Misc. Charge'
when 9 then 'Freight'
when 10 then 'Taxes'
when 11 then 'Writeoffs'
when 12 then 'Other'
when 13 then 'GST Disc'
when 14 then 'PPS Amount'
when 16 then 'Round'
when 17 then 'Realized Gain'
when 18 then 'Realized Loss'
when 19 then 'Due To'
when 20 then 'Due From'
end Distribution_Type,
D.DEBITAMT Debit_Amount,
D.CRDTAMNT Credit_Amount,
D.DistRef Distribution_Reference,
T.BACHNUMB Batch_ID,
T.TRXDSCRN Trx_Description,
T.STAT Trx_Status,
D.VCHRNMBR Voucher_Number,
D.CURNCYID Currency_ID,
coalesce(D.XCHGRATE,0) Exchange_Rate,
D.ORDBTAMT Originating_Debit_Amount,
D.ORCRDAMT Originating_Credit_Amount,
case T.VOIDED
when 0 then 'No'
when 1 then 'Yes'
end Voided,
T.PORDNMBR PO_Number,
T.TEN99AMNT [1099 Amount],
case T.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
else ''
end [1099 Type],
T.TEN99BOXNUMBER [1099 Box Number]
FROM
-- all open trx distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
FROM PM10100
UNION ALL
-- all historical trx distributions
SELECT P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
FROM PM30600 P
LEFT OUTER JOIN -- historical exchange rate
MC020103 M
ON P.VCHRNMBR = M.VCHRNMBR
AND P.DOCTYPE = M.DOCTYPE) D
-- add document number and type
LEFT OUTER JOIN
PM00400 K
ON D.VCHRNMBR = K.CNTRLNUM
AND D.CNTRLTYP = K.CNTRLTYP
--add GL account number
LEFT OUTER JOIN
GL00105 G
ON D.DSTINDX = G.ACTINDX
--add status, batch and trx description
LEFT OUTER JOIN
(SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT,
CASE
WHEN TEN99AMNT > 0 THEN 4
ELSE 0
END AS TEN99TYPE,
CASE
WHEN TEN99AMNT > 0 THEN 7
ELSE 0
END AS TEN99BOXNUMBER
FROM PM10000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT,
CASE
WHEN TEN99AMNT > 0 THEN 4
ELSE 0
END AS TEN99TYPE,
CASE
WHEN TEN99AMNT > 0 THEN 7
ELSE 0
END AS TEN99BOXNUMBER
FROM PM20000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT,
CASE
WHEN TEN99AMNT > 0 THEN 4
ELSE 0
END AS TEN99TYPE,
CASE
WHEN TEN99AMNT > 0 THEN 7
ELSE 0
END AS TEN99BOXNUMBER
FROM PM30200
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1, VENDORID,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
FROM PM10300
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN, VENDORID,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
FROM PM10400) T
ON T.VCHRNMBR = D.VCHRNMBR
AND T.CNTRLTYP = D.CNTRLTYP
--add GL account name
LEFT OUTER JOIN
GL00100 A
ON A.ACTINDX = D.DSTINDX
--add vendor name
LEFT OUTER JOIN
PM00200 N
ON N.VENDORID = T.VENDORID
Title | # Comments | Views | Activity |
---|---|---|---|
Help with SQL Query | 23 | 39 | |
Copy Database Wizard Error | 3 | 22 | |
Database connection opened on a machine | 8 | 37 | |
sql calculate averages | 18 | 32 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
10 Experts available now in Live!