Consulta Comissões

[Former Member]

Estou iniciando em consultas no SAP B1 e encontrei essa abaixo em um fórum, mas preciso incluir uma coluna com o nome do item da NF. Alguém pode me ajudar?

SELECT Distinct T0.DocNum AS 'Incoming Payment', T0.DocDate AS ' Data Pagamento', T0.CardCode, T0.CardName, T3.SlpName, T3.Commission, T1.DocNum AS 'Invoice Number', T2.OpenSum AS 'Invoice Line Amounts', T2.OpenSum * T3.Commission / 100 AS 'Commission Amount'

FROM ORCT T0

LEFT JOIN RCT2 T4 ON T0.DocNum = T4.DocNum

LEFT JOIN OINV T1 ON T1.DocEntry = T4.DocEntry

LEFT JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry

LEFT JOIN OSLP T3 ON T1.SlpCode = T3.SlpCode

WHERE T0.DocDate >=[%0\] AND T0.DocDate <=[%1\]

[fbio_bilicki · Accepted solution]

Boa tarde Paulo,

Adicione somente mais dois campos, as informações do item estão na tabela INV1

   T2.ItemCode,

   T2.Dscription

Na sua consulta

SELECT Distinct 
   T0.DocNum AS 'Incoming Payment', 
   T0.DocDate AS ' Data Pagamento', 
   T0.CardCode, 
   T0.CardName, 
   T3.SlpName, 
   T3.Commission, 
   T1.DocNum AS 'Invoice Number', 
   T2.OpenSum AS 'Invoice Line Amounts', 
   T2.OpenSum * T3.Commission / 100 AS 'Commission Amount',
   T2.ItemCode,
   T2.Dscription
FROM ORCT T0
LEFT JOIN RCT2 T4 ON T0.DocNum = T4.DocNum
LEFT JOIN OINV T1 ON T1.DocEntry = T4.DocEntry
LEFT JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry
LEFT JOIN OSLP T3 ON T1.SlpCode = T3.SlpCode
WHERE T0.DocDate >=[%0\] AND T0.DocDate <=[%1\]

[]'s

Fabio.

SAP 8.82 PL13

[Former Member · Accepted solution]

Obrigado Fábio,

Funcionou perfeitamente!