Skip to Content
avatar image
Former Member

Outgoing Payment Query Including Document Type = JE and PU

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]'='')

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 09, 2016 at 12:49 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 09, 2016 at 04:04 AM

    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.
    Add comment
    10|10000 characters needed 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

  • Nov 09, 2016 at 08:22 AM

    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]'='')

    Add comment
    10|10000 characters needed characters exceeded