on 07-07-2014 3:30 PM
Colegas,
Meu cliente solicitou no momento do levantamento, que necessita que seja gerado um relatório no SAP, baseado somente no documento que foi pago (CR) e separado por itens, baseado na comissão deste.
Vou mostrar o cenário, e de acordo com a experiência de vocês, gostaria de saber se será necessário customização, ou se posso fazer isso de maneira mais simples.
Segue cenário:
É gerado a seguinte NF de Saída:
Cód descrição - Quant - valor -comissão vendedor % - comissão gerente %
item 1 - descrição 1 10 R$ 100,00 7% 4%
item 2 - descrição 2 5 R$ 85,00 4% 2%
item 3 - descrição 3 5 R$ 66,00 2% 1%
Criei os campos "comissão vendedor %" e "comissão gerente %" na linha do documento de marketing, pois estes não são padrão no B1.
Agora supondo que este documento foi vendido com a condição de pagamento 30,60,90 dias, foi gerado o seguinte no Contas a Receber :
1ª Parcela = 83,66
2ª Parcela = 83,66
3ª Parcela = 83,67
Nesse momento é que preciso de um relatório (ou outra opção) que me mostre o quanto devo pagar ao vendedor e ao gerente de vendas, porém baseado na parcela paga, e não na NF emitida.
Alguém já teve um cenário parecido?
Eu tenho uma comissão que funciona assim, é feita no gerenciador de consultas.
Tenho os campos formatados na linha do docto de marketing: Com1, Com2. Com3
Só pagamos a comissão quando o cliente paga, incluindo pagamentos parcelados.
SET DATEFORMAT DMY
/*SELECT FROM [dbo].[ORCT] T0*/
declare @fromdate as datetime
/* WHERE */
set @fromdate = /* T0.[TrsfrDate] */ '[%0]'
/*SELECT FROM [dbo].[ORCT] T1*/
declare @tilldate as datetime
/* WHERE */
set @tilldate = /* T1.[TrsfrDate] */ '[%1]'
/*SELECT FROM [dbo].[OINV] T2*/
declare @NmCli nvarchar(100)
/* WHERE */
set @NmCli = /* T2.[CardName] */ '[%2]'
/*SELECT FROM [dbo].[OSLP] T3*/
declare @NmVend nvarchar(32)
/*WHERE */
set @NmVend = /* T3.[SlpName] */ '[%3]'
declare @CdVend nvarchar(20)
set @CdVend=''
SELECT @CdVend=isnull(T0.SlpCode,'') FROM [dbo].[OSLP] T0 WHERE T0.SlpName=@NmVend
--Variaveis
declare
@DocPaid int
,@DocEntry int
,@InstId nvarchar(max)
,@Paid decimal(34,2)
,@PaidDate datetime
,@InvType decimal(34,2)
--Tabelas temporárias
declare @Temp as table(
[DocPaid] nvarchar(max)
,[DocEntry] nvarchar(max)
,[InstId] nvarchar(max)
,[Paid] decimal(34,2)
,[PaidDate] datetime
,[InvType] decimal(34,2))
declare @Result as table(
[DocPaid] nvarchar(max)
,[DocNum] nvarchar(max)
,[Serial] nvarchar(max)
,[DocTotal] decimal(34,2)
,[Installmnt] nvarchar(max)
,[PaidToDate] nvarchar(max)
,[Paid]decimal(34,2)
,[DocDate] datetime
,[DocDueDate] datetime
,[PaidDate] datetime
,[CardCode] nvarchar(max)
,[CardName] nvarchar(max)
,[SlpName] nvarchar(max)
,[ItemCode] nvarchar(max)
,[ItemName] nvarchar(max)
,[Quantity] nvarchar(max)
,[Price] decimal(34,2)
,[LineTotal] decimal(34,2)
,[VatSum] decimal(34,2)
,[ValorItem] decimal(34,2)
,[Commission] decimal(34,2)
,[TotalComi] decimal(34,2)
,[PropPaid] decimal(34,2)
,[VlComi]decimal(34,2))
--Armazena na tabela temporária todos os títulos pagos
--no intervalo de data definido
Insert into @Temp (DocPaid,DocEntry,InstId,Paid,PaidDate,InvType)
Select
T0.[DocNum]
,T0.[DocEntry]
,T0.[InstId]
,T0.[SumApplied]
,T1.[TrsfrDate]
,T0.[InvType]
From [dbo].[RCT2] T0
Inner Join [dbo].[ORCT] T1 on T1.[DocEntry]=T0.[DocNum]
Where ((T1.[TrsfrDate] BETWEEN @fromdate AND @tilldate) or (@fromdate='' and @tilldate='')) and (T0.[InvType]=13 or T0.[InvType]=203) and T1.Canceled='N'
Order by T0.DocEntry
--Loop na tabela temporária
Declare Cur_Payments cursor for
Select T0.[DocPaid],T0.[DocEntry],T0.[InstId],T0.[Paid],T0.[PaidDate],T0.[InvType]
From @Temp T0
open Cur_Payments
Fetch next from Cur_Payments into @DocPaid,@DocEntry,@InstId,@Paid,@PaidDate,@InvType
while @@FETCH_STATUS = 0
begin
--Venda Normal
Insert into @Result ([DocPaid], [DocNum] ,[Serial],[DocTotal] ,[Installmnt] ,[PaidToDate] ,[Paid],[DocDate] ,[DocDueDate] ,[PaidDate],[CardCode] ,[CardName] ,[SlpName] ,[ItemCode] ,[ItemName],[Quantity] ,[Price] ,[LineTotal] ,[VatSum] ,[ValorItem] ,[Commission] ,[TotalComi] ,[PropPaid] ,[VlComi])
Select
@DocPaid as 'Numero do Pgto'
,T0.[DocNum] as 'Numero do documento'
,T0.[Serial] as 'Numero da nota'
,T0.[DocTotal] as 'Total do documento'
,@InstId + '/' + cast(T0.[Installmnt] as nvarchar(max)) as 'Parcela'
,T0.[PaidToDate] as 'Total pago'
,@Paid
,T0.[DocDate] as 'Data do documento'
,@PaidDate as 'Data do vencimento'
,@PaidDate as 'Data do pagamento'
,T0.[CardCode] as 'Cód. do cliente'
,T0.[CardName] as 'Cliente'
,T2.[SlpName] as 'Vendedor'
,T1.[ItemCode]
,T3.[ItemName]
,T1.[Quantity]
,T1.[Price] as 'Preço pós desconto'
,T1.[LineTotal] as 'Total do item'
,T1.[VatSum] as 'Total de imposto'
,(T1.[LineTotal] + T1.[VatSum]) as 'Valor do item'
,isnull(T1.[Commission],0) as '% comissão'
,((isnull(T1.[Commission],0)/100) * T1.[LineTotal]) as 'Total comissão'
,((100 * @Paid)/T0.[DocTotal]) as 'Proporcional pago'
,((((100 * @Paid)/T0.[DocTotal])/100)* ((isnull(T1.[Commission],0)/100) * T1.[LineTotal])) as 'Valor da comissão'
From OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry
LEFT OUTER JOIN OSLP T2 ON T2.SlpCode=T0.SlpCode
LEFT OUTER JOIN OITM T3 ON T3.ItemCode=T1.ItemCode
Where T0.DocEntry=@DocEntry AND (T0.[CardName] = @NmCli or @NmCli='') AND (T0.[SlpCode] = @CdVend or @CdVend='') and (@InvType = 13)
--Venda Normal
Insert into @Result ([DocPaid], [DocNum] ,[Serial],[DocTotal] ,[Installmnt] ,[PaidToDate] ,[Paid],[DocDate] ,[DocDueDate] ,[PaidDate],[CardCode] ,[CardName] ,[SlpName] ,[ItemCode] ,[ItemName],[Quantity] ,[Price] ,[LineTotal] ,[VatSum] ,[ValorItem] ,[Commission] ,[TotalComi] ,[PropPaid] ,[VlComi])
Select
@DocPaid as 'Numero do Pgto'
,T0.[DocNum] as 'Numero do documento'
,T0.[Serial] as 'Numero da nota'
,T0.[DocTotal] as 'Total do documento'
,@InstId + '/' + cast(T0.[Installmnt] as nvarchar(max)) as 'Parcela'
,T0.[PaidToDate] as 'Total pago'
,@Paid
,T0.[DocDate] as 'Data do documento'
,@PaidDate as 'Data do vencimento'
,@PaidDate as 'Data do pagamento'
,T0.[CardCode] as 'Cód. do cliente'
,T0.[CardName] as 'Cliente'
,T2.[SlpName] as 'Vendedor'
,T1.[ItemCode]
,T3.[ItemName]
,T1.[Quantity]
,T1.[Price] as 'Preço pós desconto'
,T1.[LineTotal] as 'Total do item'
,T1.[VatSum] as 'Total de imposto'
,(T1.[LineTotal] + T1.[VatSum]) as 'Valor do item'
,isnull(T1.[Commission],0) as '% comissão'
,((isnull(T1.[Commission],0)/100) * T1.[LineTotal]) as 'Total comissão'
,((100 * @Paid)/T0.[DocTotal]) as 'Proporcional pago'
,((((100 * @Paid)/T0.[DocTotal])/100)* ((isnull(T1.[Commission],0)/100) * T1.[LineTotal])) as 'Valor da comissão'
From ODPI T0
INNER JOIN DPI1 T1 ON T1.DocEntry=T0.DocEntry
LEFT OUTER JOIN OSLP T2 ON T2.SlpCode=T0.SlpCode
LEFT OUTER JOIN OITM T3 ON T3.ItemCode=T1.ItemCode
Where T0.DocEntry=@DocEntry AND (T0.[CardName] = @NmCli or @NmCli='') AND (T0.[SlpCode] = @CdVend or @CdVend='') and (@InvType = 203)
--Venda Compartilhada/Vendedor2
Insert into @Result ([DocPaid], [DocNum] ,[Serial],[DocTotal] ,[Installmnt] ,[PaidToDate] ,[Paid],[DocDate] ,[DocDueDate] ,[PaidDate],[CardCode] ,[CardName] ,[SlpName] ,[ItemCode] ,[ItemName],[Quantity] ,[Price] ,[LineTotal] ,[VatSum] ,[ValorItem] ,[Commission] ,[TotalComi] ,[PropPaid] ,[VlComi])
Select
@DocPaid as 'Numero do Pgto'
,T0.[DocNum] as 'Numero do documento'
,T0.[Serial] as 'Numero da nota'
,T0.[DocTotal] as 'Total do documento'
,@InstId + '/' + cast(T0.[Installmnt] as nvarchar(max)) as 'Parcela'
,T0.[PaidToDate] as 'Total pago'
,@Paid
,T0.[DocDate] as 'Data do documento'
,@PaidDate as 'Data do vencimento'
,@PaidDate as 'Data do pagamento'
,T0.[CardCode] as 'Cód. do cliente'
,T0.[CardName] as 'Cliente'
,isnull(T2.[firstName],'') + ' ' + isnull(T2.[middleName],'') + ' ' + isnull(T2.[lastName],'') as 'Vendedor'
,T1.[ItemCode]
,T3.[ItemName]
,T1.[Quantity]
,T1.[Price] as 'Preço pós desconto'
,T1.[LineTotal] as 'Total do item'
,T1.[VatSum] as 'Total de imposto'
,(T1.[LineTotal] + T1.[VatSum]) as 'Valor do item'
,isnull(T1.[U_PC_Com1],0) as '% comissão'
,((isnull(T1.[U_PC_Com1],0)/100) * T1.[LineTotal]) as 'Total comissão'
,((100 * @Paid)/T0.[DocTotal]) as 'Proporcional pago'
,((((100 * @Paid)/T0.[DocTotal])/100)* ((isnull(T1.[U_PC_Com1],0)/100) * T1.[LineTotal])) as 'Valor da comissão'
From OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry
LEFT OUTER JOIN OHEM T2 ON T2.empID=T0.U_SlpCode2
LEFT OUTER JOIN OITM T3 ON T3.ItemCode=T1.ItemCode
Where T0.DocEntry=@DocEntry AND (T0.[CardName] = @NmCli or @NmCli='') AND (T0.[SlpCode] = @CdVend or @CdVend='') and (T0.U_SlpCode2 <> '' and T0.U_SlpCode2 is Not null) and (@InvType = 13)
--Venda Compartilhada/Vendedor2
Insert into @Result ([DocPaid], [DocNum] ,[Serial],[DocTotal] ,[Installmnt] ,[PaidToDate] ,[Paid],[DocDate] ,[DocDueDate] ,[PaidDate],[CardCode] ,[CardName] ,[SlpName] ,[ItemCode] ,[ItemName],[Quantity] ,[Price] ,[LineTotal] ,[VatSum] ,[ValorItem] ,[Commission] ,[TotalComi] ,[PropPaid] ,[VlComi])
Select
@DocPaid as 'Numero do Pgto'
,T0.[DocNum] as 'Numero do documento'
,T0.[Serial] as 'Numero da nota'
,T0.[DocTotal] as 'Total do documento'
,@InstId + '/' + cast(T0.[Installmnt] as nvarchar(max)) as 'Parcela'
,T0.[PaidToDate] as 'Total pago'
,@Paid
,T0.[DocDate] as 'Data do documento'
,@PaidDate as 'Data do vencimento'
,@PaidDate as 'Data do pagamento'
,T0.[CardCode] as 'Cód. do cliente'
,T0.[CardName] as 'Cliente'
,isnull(T2.[firstName],'') + ' ' + isnull(T2.[middleName],'') + ' ' + isnull(T2.[lastName],'') as 'Vendedor'
,T1.[ItemCode]
,T3.[ItemName]
,T1.[Quantity]
,T1.[Price] as 'Preço pós desconto'
,T1.[LineTotal] as 'Total do item'
,T1.[VatSum] as 'Total de imposto'
,(T1.[LineTotal] + T1.[VatSum]) as 'Valor do item'
,isnull(T1.[U_PC_Com1],0) as '% comissão'
,((isnull(T1.[U_PC_Com1],0)/100) * T1.[LineTotal]) as 'Total comissão'
,((100 * @Paid)/T0.[DocTotal]) as 'Proporcional pago'
,((((100 * @Paid)/T0.[DocTotal])/100)* ((isnull(T1.[U_PC_Com1],0)/100) * T1.[LineTotal])) as 'Valor da comissão'
From ODPI T0
INNER JOIN DPI1 T1 ON T1.DocEntry=T0.DocEntry
LEFT OUTER JOIN OHEM T2 ON T2.empID=T0.U_SlpCode2
LEFT OUTER JOIN OITM T3 ON T3.ItemCode=T1.ItemCode
Where T0.DocEntry=@DocEntry AND (T0.[CardName] = @NmCli or @NmCli='') AND (T0.[SlpCode] = @CdVend or @CdVend='') and (T0.U_SlpCode2 <> '' and T0.U_SlpCode2 is Not null) and (@InvType = 203)
--Venda Compartilhada/Gerente
Insert into @Result ([DocPaid], [DocNum] ,[Serial],[DocTotal] ,[Installmnt] ,[PaidToDate] ,[Paid],[DocDate] ,[DocDueDate] ,[PaidDate],[CardCode] ,[CardName] ,[SlpName] ,[ItemCode] ,[ItemName],[Quantity] ,[Price] ,[LineTotal] ,[VatSum] ,[ValorItem] ,[Commission] ,[TotalComi] ,[PropPaid] ,[VlComi])
Select
@DocPaid as 'Numero do Pgto'
,T0.[DocNum] as 'Numero do documento'
,T0.[Serial] as 'Numero da nota'
,T0.[DocTotal] as 'Total do documento'
,@InstId + '/' + cast(T0.[Installmnt] as nvarchar(max)) as 'Parcela'
,T0.[PaidToDate] as 'Total pago'
,@Paid
,T0.[DocDate] as 'Data do documento'
,@PaidDate as 'Data do vencimento'
,@PaidDate as 'Data do pagamento'
,T0.[CardCode] as 'Cód. do cliente'
,T0.[CardName] as 'Cliente'
,isnull(T2.[firstName],'') + ' ' + isnull(T2.[middleName],'') + ' ' + isnull(T2.[lastName],'') as 'Vendedor'
,T1.[ItemCode]
,T3.[ItemName]
,T1.[Quantity]
,T1.[Price] as 'Preço pós desconto'
,T1.[LineTotal] as 'Total do item'
,T1.[VatSum] as 'Total de imposto'
,(T1.[LineTotal] + T1.[VatSum]) as 'Valor do item'
,isnull(T1.[U_PC_Com2],0) as '% comissão'
,((isnull(T1.[U_PC_Com2],0)/100) * T1.[LineTotal]) as 'Total comissão'
,((100 * @Paid)/T0.[DocTotal]) as 'Proporcional pago'
,((((100 * @Paid)/T0.[DocTotal])/100)* ((isnull(T1.[U_PC_Com2],0)/100) * T1.[LineTotal])) as 'Valor da comissão'
From OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry
LEFT OUTER JOIN OHEM T2 ON T2.empID=T0.U_SlpCode3
LEFT OUTER JOIN OITM T3 ON T3.ItemCode=T1.ItemCode
Where T0.DocEntry=@DocEntry AND (T0.[CardName] = @NmCli or @NmCli='') AND (T0.[SlpCode] = @CdVend or @CdVend='') and (T0.U_SlpCode3 <> '' and T0.U_SlpCode3 is Not null) and (@InvType = 13)
--Venda Compartilhada/Gerente
Insert into @Result ([DocPaid], [DocNum] ,[Serial],[DocTotal] ,[Installmnt] ,[PaidToDate] ,[Paid],[DocDate] ,[DocDueDate] ,[PaidDate],[CardCode] ,[CardName] ,[SlpName] ,[ItemCode] ,[ItemName],[Quantity] ,[Price] ,[LineTotal] ,[VatSum] ,[ValorItem] ,[Commission] ,[TotalComi] ,[PropPaid] ,[VlComi])
Select
@DocPaid as 'Numero do Pgto'
,T0.[DocNum] as 'Numero do documento'
,T0.[Serial] as 'Numero da nota'
,T0.[DocTotal] as 'Total do documento'
,@InstId + '/' + cast(T0.[Installmnt] as nvarchar(max)) as 'Parcela'
,T0.[PaidToDate] as 'Total pago'
,@Paid
,T0.[DocDate] as 'Data do documento'
,@PaidDate as 'Data do vencimento'
,@PaidDate as 'Data do pagamento'
,T0.[CardCode] as 'Cód. do cliente'
,T0.[CardName] as 'Cliente'
,isnull(T2.[firstName],'') + ' ' + isnull(T2.[middleName],'') + ' ' + isnull(T2.[lastName],'') as 'Vendedor'
,T1.[ItemCode]
,T3.[ItemName]
,T1.[Quantity]
,T1.[Price] as 'Preço pós desconto'
,T1.[LineTotal] as 'Total do item'
,T1.[VatSum] as 'Total de imposto'
,(T1.[LineTotal] + T1.[VatSum]) as 'Valor do item'
,isnull(T1.[U_PC_Com2],0) as '% comissão'
,((isnull(T1.[U_PC_Com2],0)/100) * T1.[LineTotal]) as 'Total comissão'
,((100 * @Paid)/T0.[DocTotal]) as 'Proporcional pago'
,((((100 * @Paid)/T0.[DocTotal])/100)* ((isnull(T1.[U_PC_Com2],0)/100) * T1.[LineTotal])) as 'Valor da comissão'
From ODPI T0
INNER JOIN DPI1 T1 ON T1.DocEntry=T0.DocEntry
LEFT OUTER JOIN OHEM T2 ON T2.empID=T0.U_SlpCode3
LEFT OUTER JOIN OITM T3 ON T3.ItemCode=T1.ItemCode
Where T0.DocEntry=@DocEntry AND (T0.[CardName] = @NmCli or @NmCli='') AND (T0.[SlpCode] = @CdVend or @CdVend='') and (T0.U_SlpCode3 <> '' and T0.U_SlpCode3 is Not null) and (@InvType = 203)
--Proximo Registro
Fetch next from Cur_Payments into @DocPaid,@DocEntry,@InstId,@Paid,@PaidDate,@InvType
end
--Fecha o cursor e desaloca da memoria
close Cur_Payments
deallocate Cur_Payments
--Exibe o resultado
Select
T0.[Serial] as 'N° N.F.'
,T0.[CardCode] as 'Cód. PN'
,T0.[CardName] as 'Nome PN'
,T0.[DocDate] as 'Data Doc.'
,T0.[DocDueDate] as 'Data Vencto'
,T0.[DocTotal] as 'Total Doc.'
,T0.[PaidDate] as 'Data do pagamento'
,T0.[ItemCode] as 'Código do item'
,T0.[ItemName] as 'Descrição do item'
,T0.[SlpName] as 'Vendedor'
,T0.[LineTotal] as 'Total do item'
,T0.[PropPaid]/100 * T0.LineTotal as 'Vr. Base Comissão'
,T0.[Commission] as '% comissão'
,T0.[VlComi] as 'Valor da comissão'
,T0.[Installmnt] as 'Parcela'
from @Result T0 order by [SlpName],[Serial]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thiago, acredito q vou implementar sua consulta (te devo umas horas de consultoria...rsrsrsrs)...
Porém estou primeiro tentando entender o seu relatório...
Os campos U_SlpCode3 e U_SlpCode2 foram criados tb... Qual a finalidade deles??
Sua consulta não é baseada no campo SlpCode do documento de marketing?
Boa tarde Rafael!
Já fiz um relatório parecido sim, e realmente é necessário customizá-lo no Crystal Reports.
Att.
Mayara
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.