Solved: outgoing payment Query in sap b1

datta1990 · ‎11-17-2018

Dear sir,

I have required below output,

I have run below Query, please fulfill requirements

"Outgoing Payment Query Including Document Type = JE and PU"

SELECT
	T0.[DocDate]AS'Payment Date', 
	T0.[DocNum]AS'DocNum',CASEWHEN T1.InvType =18THEN'PU'WHEN T1.InvType =30THEN'JE'ENDAS'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',CaseWhen T0.[CashSum]>0Then'Cash'When T0.[CreditSum]>0Then'Credit Card'When T0.[TrsfrSum]>0Then'Wire'Else'Check'EndAS'Paid By', 
	T2.[CheckNum]AS'Check #', 
	T0.[TrsfrRef]AS'Wire Ref', 
	T0.[DocTotal]AS'Paid Amount'FROM 
	OVPM T0 
	INNERJOIN VPM2 T1 ON T1.DocNum = T0.DocEntry
	LEFTJOIN VPM1 T2 ON T1.DocNum = T2.DocNum 
	LEFTJOIN 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]'='')

former_member588507 · ‎11-26-2018

Hi datta,

Please close the question by clicking the 'Correct answer' link below.It will make it easier for others to benefit from your question.

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

former_member588507 · ‎11-20-2018

Hi Datta,

Can you tell me which type of transactions missed in the given query by me?

former_member588507 · ‎11-17-2018

Hi Datta,

Try this query:-

SELECT
	T0.[DocDate]AS'Payment Date', 
	T0.[DocNum]AS'DocNum'
	,(select CASE WHEN T1.InvType =18 THEN'PU' 
				  WHEN T1.InvType =30 THEN 'JE'
				  When T1.InvType=46 THEN 'OUTGOING'
				  WHEN T1.InvType=23 THEN 'INCOMING' 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'
	,(Select 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]
AND T0.DocDate<=[%1]
AND T0.CardName=[%2]

Regards,

Satish Kumar

outgoing payment Query in sap b1

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

Re: CBC 2SL customer using CLT service

Re: Function module CNV_PE_S4_RFCFM_ORG_DERIV miss...

EHS Data Migration for SAP S/4hana on-premise

Re: Whether inventory data can be extracted to GR ...

Whether inventory data can be extracted to GR Inte...