Skip to Content
0

Outgoing Payment Query Including Document Type = JE and PU

Nov 08, 2016 at 06:57 AM

397

avatar image

Hi,

I had create query as below, however it will not list out if the document type of outgoing payment is JE, all the result are PU, please help

Thanks

SELECT DISTINCT T0.[DocDate] AS 'Payment Date', T0.[DocNum] AS 'DocNum', T0.[CardCode] AS 'Vendor Code', T0.[CardName] AS 'Vendor Name', T2.[DocNum] AS 'Vendor Invoice', T2.[NumAtCard] AS 'Vendor Invoice Ref', T2.[DocTotal] AS 'Invoice Amount', Case When T0.[CashSum] > 0 Then 'Cash' When T0.[CreditSum] > 0 Then 'Credit Card' When T0.[TrsfrSum] > 0 Then 'Wire' Else 'Check' End AS 'Paid By', T1.[CheckNum] AS 'Check #', T0.[TrsfrRef] AS 'Wire Ref', T0.[DocTotal] AS 'Paid Amount' FROM OVPM T0 LEFT JOIN VPM1 T1 ON T0.DocEntry = T1.DocNum LEFT JOIN OPCH T2 ON T0.DocEntry = T2.ReceiptNum INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry LEFT JOIN OJDT T4 ON T0.TransId = T4.TransId WHERE (T0.[DocDate] <=[%0] OR '[%0]'='') AND (T0.[DocDate] >=[%1] OR '[%1]'='') AND (T2.[CardName] = [%2] OR '[%2]'='')

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

DIEGO LOTHER Nov 09, 2016 at 12:49 AM
0

Hi Olga,

I did some changes in your query, but basically you can get the documents that were being paid in the outgoing payment looking the table VPM2, and you can see what type of document looking the field InvType.

SELECT
	T0.[DocDate] AS 'Payment Date', 
	T0.[DocNum] AS 'DocNum', 
	CASE WHEN T1.InvType = 18 THEN 'PU'
		 WHEN T1.InvType = 30 THEN 'JE'
	END AS 'Document Type',
	T0.[CardCode] AS 'Vendor Code', 
	T0.[CardName] AS 'Vendor Name', 
	T3.[DocNum] AS 'Vendor Invoice', 
	T3.[NumAtCard] AS 'Vendor Invoice Ref', 
	T3.[DocTotal] AS 'Invoice Amount', 
	Case When T0.[CashSum] > 0 Then 'Cash' 
		 When T0.[CreditSum] > 0 Then 'Credit Card' 
		 When T0.[TrsfrSum] > 0 Then 'Wire' 
	Else 'Check' End AS 'Paid By', 
	T2.[CheckNum] AS 'Check #', 
	T0.[TrsfrRef] AS 'Wire Ref', 
	T0.[DocTotal] AS 'Paid Amount' 
FROM 
	OVPM T0 
	INNER JOIN VPM2 T1 ON T1.DocNum = T0.DocEntry
	LEFT JOIN VPM1 T2 ON T1.DocNum = T2.DocNum 
	LEFT JOIN OPCH T3 ON T1.DocEntry = T3.DocEntry 
WHERE 
	(T0.[DocDate] <=[%0] OR '[%0]'='') 
	AND (T0.[DocDate] >=[%1] OR '[%1]'='') 
	AND (T0.[CardName] = [%2] OR '[%2]'='')

EDITED:

On the last where clause was changed the instruction T2.[CardName] by T0.[CardName]

Hope it helps.

Kind Regards,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded
Olga Hu Nov 09, 2016 at 04:04 AM
0

Hi Diego,

Thanks for the reply, I tried your query, but there is some error, message as below:

Thanks

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Received Alerts' (OAIB) (s) could not be prepared.
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Olga,

I updated the ALIAS of the tables and forgot to change the alias on the where clause. I updated my answer above. Please try again.

Hope it helps.

Kind Regards,

Diego Lother

0
Edmund Leung Nov 09, 2016 at 08:22 AM
0

SELECT T0.[DocDate] AS 'Payment Date', T0.[DocNum] AS 'DocNum', CASE WHEN T1.InvType = 18 THEN 'PU' WHEN T1.InvType = 30 THEN 'JE' END AS 'Document Type', T0.[CardCode] AS 'Vendor Code', T0.[CardName] AS 'Vendor Name', T3.[DocNum] AS 'Vendor Invoice', T3.[NumAtCard] AS 'Vendor Invoice Ref', T3.[DocTotal] AS 'Invoice Amount', Case When T0.[CashSum] > 0 Then 'Cash' When T0.[CreditSum] > 0 Then 'Credit Card' When T0.[TrsfrSum] > 0 Then 'Wire' Else 'Check' End AS 'Paid By', T2.[CheckNum] AS 'Check #', T0.[TrsfrRef] AS 'Wire Ref', T0.[DocTotal] AS 'Paid Amount' FROM OVPM T0 INNER JOIN VPM2 T1 ON T1.DocNum = T0.DocEntry LEFT JOIN VPM1 T2 ON T1.DocNum = T2.DocNum LEFT JOIN OPCH T3 ON T1.DocEntry = T3.DocEntry WHERE (T0.[DocDate] <='[%0]' OR '[%0]'='') AND (T0.[DocDate] >=[%1] OR '[%1]'='') AND (T0.[CardName] = [%2] OR '[%2]'='')

Share
10 |10000 characters needed characters left characters exceeded