Outgoing payment query not showing data please correct it...

[former_member835712]

SELECT

	     ovpm.DocEntry
	    ,OVPM.DOCNUM   AS  'Voucher No.'
	    ,OVPM.DOCDATE  AS  'Voucher Date'
		,OVPM.CardCode AS  'Vendor Code'
		,OVPM.CardName AS  'Vendor Name'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.DocNum
					  WHEN VPM2.INVTYPE=19 THEN ORPC.DocNum END)    AS    'Invoice No.'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.NumAtCard
					  WHEN VPM2.INVTYPE=19 THEN ORPC.NumAtCard END) AS    'Vendor Invoice Reference.'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.DocDate
					  WHEN VPM2.INVTYPE=19 THEN ORPC.DocDate END)   AS    'Vendor Invoice Date'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.DocTotal
					  WHEN VPM2.INVTYPE=19 THEN ORPC.DocTotal END)  AS    'Bill Amount'
		
	 
		,(SELECT CASE WHEN VPM2.INVTYPE<>'' OR VPM2.INVTYPE<>'NULL' THEN  ( SELECT CASE WHEN VPM2.INVTYPE=19   THEN (-VPM2.SUMAPPLIED)
						WHEN VPM2.INVTYPE<>19 THEN VPM2.SUMAPPLIED  END )
						WHEN VPM4.AcctCode<>'' OR VPM4.AcctCode<>'NULL' THEN VPM4.SUMAPPLIED
						ELSE OVPM.DocTotal END) AS 'Line Total Payment Amt'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN opch.DocTotal
										WHEN VPM2.INVTYPE=19 THEN (-ORPC.DocTotal ) END) 'Invoice Doc Total'
		,(OPCH.DOCTOTAL- vpm2.SumApplied) 'Due Amt'
		 ,OVPM.DOCTOTAL AS 'Outgoing payment Doc total'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN 'A/P Invoice'
					  WHEN VPM2.INVTYPE=19 THEN 'Credit Note'
					  WHEN vpm2.InvType=46 then 'Outgoing Payment'
				      WHEN VPM2.InvType=23 THEN 'Incoming Payment' 
					  WHEN VPM2.InvType=30 THEN 'Journal Entry'
					  WHEN VPM2.InvType=-2 THEN 'Opening Balance' ELSE 'Payment on Account ' END) 'Base Document Type'
		 ,VPM1.CHECKNUM 'Cheque No.' 
		 ,(CASE WHEN (ISNULL(OVPM.CashSum,0) <> 0 ) THEN 'Cash Transfer' 
			WHEN (ISNULL(OVPM.[CheckSum],0) <> 0 ) THEN 'Cheque Transfer'
			WHEN (ISNULL(OVPM.TrsfrSum,0) <> 0 ) THEN 'NEFT' 
			WHEN OJDT.TransType = '30' THEN 'JEs' 
			ELSE ''
			 END) AS 'Transaction Type'
		
                      
FROM         ODSC (NOLOCK) RIGHT OUTER JOIN
                      OVPM (NOLOCK) LEFT OUTER JOIN
                      VPM4 (NOLOCK) ON OVPM.DOCENTRY = VPM4.DOCNUM LEFT OUTER JOIN
                      VPM1 (NOLOCK) ON OVPM.DOCENTRY = VPM1.DOCNUM LEFT OUTER JOIN
                      OACT (NOLOCK) ON OVPM.TRSFRACCT = OACT.ACCTCODE ON ODSC.BANKCODE = VPM1.BANKCODE LEFT OUTER JOIN
                      VPM2 (NOLOCK) ON OVPM.DOCENTRY = VPM2.DOCNUM
                      LEFT OUTER JOIN OPCH (NOLOCK) ON OPCH.DOCENTRY=VPM2.DOCENTRY AND VPM2.INVTYPE=18
                      LEFT OUTER JOIN ORPC (NOLOCK) ON ORPC.DocEntry=VPM2.DocEntry AND VPM2.INVTYPE=19
                      LEFT OUTER JOIN OJDT (NOLOCK) ON OJDT.TRANSID=OPCH.TRANSID
                      LEFT OUTER JOIN OJDT as X (NOLOCK) ON x.TRANSID=ORPC.TRANSID
                      LEFT OUTER JOIN OJDT AS Y (NOLOCK) ON Y.TransId=VPM2.DocEntry AND VPM2.InvType IN (23,46,30)
                     
                      LEFT JOIN OCRD (NOLOCK) ON OCRD.CARDCODE=OVPM.CARDCODE
WHERE     OVPM.DocDate =(SELECT CONVERT(date, getdate()))
GROUP BY    OVPM.DOCNUM, OVPM.DOCDATE, OVPM.CARDNAME,OVPM.CARDCODE,  OVPM.DOCTOTAL, 
 VPM1.CHECKNUM,  OPCH.DOCTOTAL,VPM2.SUMAPPLIED,OPCH.DOCNUM,OPCH.NumAtCard,ORPC.DocTotal
,OJDT.Number,X.Number,Y.Number,ORPC.DocNum,ORPC.NumAtCard ,OPCH.DocDate,ORPC.DocDate,VPM2.INVTYPE,VPM4.AcctCode,VPM4.SUMAPPLIED
,VPM1.CHECKSUM,ovpm.DocEntry,OVPM.CashSum,OVPM.[CheckSum],OVPM.TrsfrSum,OJDT.TransType
order by
ovpm.DocEntry asc

[jitin_chawla · Accepted solution]

If there is no Outgoing Payment today, then no result will appear.

WHERE OVPM.DocDate =(SELECT CONVERT(date, getdate()))

I checked by adding an Outgoing Payment in DEMO and it worked and brought the same in result.

Kr,

Jitin

[former_member835712 · Accepted solution]

Sir kinldy add in this query to date and from date..

[former_member835712 · Accepted solution]

Need date Selection in this query

[jitin_chawla]

Same for me in Query Generator.

Check in MSSQL?

Kr,

Jitin

[former_member835712]

Invalid column name '%1'

MSSQL ERROR FOR THIS QUERY

[jitin_chawla]

Hi, first try by replacing the [%1] with a valid date like '20230401' and see if it is giving results as expected. Then the same can be declared as variable for it to work.

[former_member835712]

Data are coming but only this date '20230401'

But i required data ( from date TO to Date) please do the same...

[jitin_chawla]

Replace OVPM.DocDate = [%1]

with

OVPM.DocDate >= 'From Date' and OVPM.DocDate <= 'To Date'

'From Date' and 'To Date' enter manually.

[former_member835712]

SIR I NEED THIS REPORT WITH DATE SELECTION PLEASE DO THE SAME...

[jitin_chawla]

SELECT ovpm.DocEntry
	    ,OVPM.DOCNUM   AS  'Voucher No.'
	    ,OVPM.DOCDATE  AS  'Voucher Date'
		,OVPM.CardCode AS  'Vendor Code'
		,OVPM.CardName AS  'Vendor Name'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.DocNum
					  WHEN VPM2.INVTYPE=19 THEN ORPC.DocNum END)    AS    'Invoice No.'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.NumAtCard
					  WHEN VPM2.INVTYPE=19 THEN ORPC.NumAtCard END) AS    'Vendor Invoice Reference.'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.DocDate
					  WHEN VPM2.INVTYPE=19 THEN ORPC.DocDate END)   AS    'Vendor Invoice Date'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN OPCH.DocTotal
					  WHEN VPM2.INVTYPE=19 THEN ORPC.DocTotal END)  AS    'Bill Amount'
		
	 
		,(SELECT CASE WHEN VPM2.INVTYPE<>'' OR VPM2.INVTYPE<>'NULL' THEN  ( SELECT CASE WHEN VPM2.INVTYPE=19   THEN (-VPM2.SUMAPPLIED)
						WHEN VPM2.INVTYPE<>19 THEN VPM2.SUMAPPLIED  END )
						WHEN VPM4.AcctCode<>'' OR VPM4.AcctCode<>'NULL' THEN VPM4.SUMAPPLIED
						ELSE OVPM.DocTotal END) AS 'Line Total Payment Amt'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN opch.DocTotal
										WHEN VPM2.INVTYPE=19 THEN (-ORPC.DocTotal ) END) 'Invoice Doc Total'
		,(OPCH.DOCTOTAL- vpm2.SumApplied) 'Due Amt'
		 ,OVPM.DOCTOTAL AS 'Outgoing payment Doc total'
		,(SELECT CASE WHEN VPM2.INVTYPE=18 THEN 'A/P Invoice'
					  WHEN VPM2.INVTYPE=19 THEN 'Credit Note'
					  WHEN vpm2.InvType=46 then 'Outgoing Payment'
				      WHEN VPM2.InvType=23 THEN 'Incoming Payment' 
					  WHEN VPM2.InvType=30 THEN 'Journal Entry'
					  WHEN VPM2.InvType=-2 THEN 'Opening Balance' ELSE 'Payment on Account ' END) 'Base Document Type'
		 ,VPM1.CHECKNUM 'Cheque No.' 
		 ,(CASE WHEN (ISNULL(OVPM.CashSum,0) <> 0 ) THEN 'Cash Transfer' 
			WHEN (ISNULL(OVPM.[CheckSum],0) <> 0 ) THEN 'Cheque Transfer'
			WHEN (ISNULL(OVPM.TrsfrSum,0) <> 0 ) THEN 'NEFT' 
			WHEN OJDT.TransType = '30' THEN 'JEs' 
			ELSE ''
			 END) AS 'Transaction Type'
		
                      
FROM         ODSC (NOLOCK) RIGHT OUTER JOIN
                      OVPM (NOLOCK) LEFT OUTER JOIN
                      VPM4 (NOLOCK) ON OVPM.DOCENTRY = VPM4.DOCNUM LEFT OUTER JOIN
                      VPM1 (NOLOCK) ON OVPM.DOCENTRY = VPM1.DOCNUM LEFT OUTER JOIN
                      OACT (NOLOCK) ON OVPM.TRSFRACCT = OACT.ACCTCODE ON ODSC.BANKCODE = VPM1.BANKCODE LEFT OUTER JOIN
                      VPM2 (NOLOCK) ON OVPM.DOCENTRY = VPM2.DOCNUM
                      LEFT OUTER JOIN OPCH (NOLOCK) ON OPCH.DOCENTRY=VPM2.DOCENTRY AND VPM2.INVTYPE=18
                      LEFT OUTER JOIN ORPC (NOLOCK) ON ORPC.DocEntry=VPM2.DocEntry AND VPM2.INVTYPE=19
                      LEFT OUTER JOIN OJDT (NOLOCK) ON OJDT.TRANSID=OPCH.TRANSID
                      LEFT OUTER JOIN OJDT as X (NOLOCK) ON x.TRANSID=ORPC.TRANSID
                      LEFT OUTER JOIN OJDT AS Y (NOLOCK) ON Y.TransId=VPM2.DocEntry AND VPM2.InvType IN (23,46,30)
                     
                      LEFT JOIN OCRD (NOLOCK) ON OCRD.CARDCODE=OVPM.CARDCODE
WHERE     OVPM.DocDate = [%1]
GROUP BY    OVPM.DOCNUM, OVPM.DOCDATE, OVPM.CARDNAME,OVPM.CARDCODE,  OVPM.DOCTOTAL, 
 VPM1.CHECKNUM,  OPCH.DOCTOTAL,VPM2.SUMAPPLIED,OPCH.DOCNUM,OPCH.NumAtCard,ORPC.DocTotal
,OJDT.Number,X.Number,Y.Number,ORPC.DocNum,ORPC.NumAtCard ,OPCH.DocDate,ORPC.DocDate,VPM2.INVTYPE,VPM4.AcctCode,VPM4.SUMAPPLIED
,VPM1.CHECKSUM,ovpm.DocEntry,OVPM.CashSum,OVPM.[CheckSum],OVPM.TrsfrSum,OJDT.TransType
order by
ovpm.DocEntry asc

[former_member835712]

Query Showing Meation below error.

1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Must specify table to select from. 2). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.