Skip to Content
0
Jun 03, 2022 at 03:21 PM

Double filter for a business partner and only one sales person.

52 Views

Is there a way when a certain name is selected it also filters for the business partner? Lets say slpcode 39 in the master data OCRD table. Right now it does the selection on the invoice and credits but would like to do it for one sales person with a code.

--Invoice Details 
SELECT T0.[SlpCode], T4.[SlpName], 
T0.[DocDate], T5.[CardCode], T5.[CardName], T0.[DocNum],  
T1.[ItemCode], T1.[Dscription], 
T2.[ItmsGrpCod], 
T3.[ItmsGrpNam],  
T1.[Quantity], T1.[Price], T1.[LineTotal], T1.[DistribSum]  
FROM OINV T0  
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]  
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]  
INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]  
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
INNER JOIN OCRD T5 ON T0.[CardCode] = T5.[CardCode]  
WHERE T0.[DocDate] >= '[%1]' and T0.[DocDate] <= '[%2]'  
AND T4.[SlpName] = '[%0]'
UNION ALL 
--Credit Details 
SELECT T0.[SlpCode], T4.[SlpName], 
T0.[DocDate], T5.[CardCode], T5.[CardName], T0.[DocNum],  
T1.[ItemCode], T1.[Dscription], 
T2.[ItmsGrpCod], 
T3.[ItmsGrpNam],  
-T1.[Quantity], T1.[Price], -T1.[LineTotal], -T1.[DistribSum]  
FROM ORIN T0  
INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]  
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]  
INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]  
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]  
INNER JOIN OCRD T5 ON T0.[CardCode] = T5.[CardCode]
WHERE T0.[DocDate] >= '[%1]' and T0.[DocDate] <= '[%2]'  
AND T4.[SlpName] = '[%0]'