CODE WITH EDD

Biblioteca digital de código fuente.

QUERY CARTERA DE CLIENTE PARA SAPB1

Code with Edd
Por Edd LozanoPublicado el Publicada en SAP Business OneEtiquetada como , , , ,

QUERY PARA MOSTRAR LOS DÍAS CARTERA DE UN CLIENTE EN ESPECIFICO.

declare @CardCode nvarchar(30)
set @CardCode = 'C01156'

declare @Cartera table 
(
  CardCode nvarchar(30)
  ,CardName nvarchar(100)
  ,DocNum int
  ,NumDoc nvarchar(254)
  ,TipoDoc nvarchar(30)
  ,Fecha datetime
  ,FechaVencimiento datetime
  ,Total numeric(19,6)
  ,Saldo numeric(19,6)
  ,NoVencido numeric(19,6)
  ,C_1_30 numeric(19,6)
  ,C_31_60 numeric(19,6)
  ,C_61_90 numeric(19,6)
  ,C_91_120 numeric(19,6)
  ,C_121_mas numeric(19,6)
)


INSERT INTO @Cartera
SELECT   
  T0.cardCode as CveCliente,T0.CardName,Convert(varchar(20),T0.docnum) docnum,
  'Factura ' + Convert(varchar(20),T0.docnum) + ' Saldo pendiente: $ ' +  Convert(varchar(20), ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0))  + ' días vencida: ' + Convert(varchar(10),DATEDIFF(day , max(T0.DocDueDate),getdate())) AS NumDoc,
  'Factura' as TipoDoc,  max(T0.DocDate) as 'fecha', 
  max(T0.docduedate) as 'Fecha_vencimiento',
  SUM(docTotal)as Total,
  Saldo=    ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0) ,
  'NoVencido'= ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1<0 ),0),
  '1_30'    =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 0 and 30 ),0),
  '31_60'   =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 31 and 60 ),0),
  '61_90'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 61 and 90),0), 
  '91_120'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 91 and 120),0), 
  '121_mas'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 > 121),0)
  
FROM OINV T0
  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
  INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
  INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
WHERE T0.DocStatus='O' AND DocSubType <>'DN' AND T0.CardCode=@CardCode
GROUP BY T0.cardname, T0.docnum,T0.CardCode ,T0.CardName


UNION ALL


SELECT   
  T0.cardCode as CveCliente,T0.CardName,Convert(varchar(20),T0.docnum) docnum,
  'Nota Débito ' + Convert(varchar(20),T0.docnum) + ' Saldo pendiente: $ ' +  Convert(varchar(20), ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0))   + ' días vencida: ' + Convert(varchar(10),DATEDIFF(day , max(T0.DocDueDate),getdate()))  AS NumDoc,
  'Nota Débito' as TipoDoc, max(T0.DocDate) as 'fecha del doc', 
  max(T0.DocDueDate) as 'Fecha de vencimiento',SUM(docTotal) as Total,
  Saldo=    ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0) ,
  'NoVencido'= ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1<0 ),0),
  '1_30'    =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 0 and 30 ),0),
  '31_60'   =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 31 and 60 ),0),
  '61_90'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 61 and 90),0), 
  '91_120'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 91 and 120),0), 
  '121_mas'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 > 121),0)
FROM OINV T0
  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
  INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
  INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
WHERE T0.DocStatus='O'  AND DocSubType ='DN' AND T0.CardCode=@CardCode
GROUP BY T0.cardname, T0.docnum,T0.CardCode ,T0.CardName


UNION ALL


SELECT  T0.cardCode as CveCliente, T0.CardName,Convert(varchar(20),T0.docnum) docnum,
  'Nota Crédito ' + Convert(varchar(20),T0.docnum) + ' Saldo pendiente: $ ' +  Convert(varchar(20), ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0))  + ' días vencida: ' + Convert(varchar(10),DATEDIFF(day , max(T0.DocDueDate),getdate())) as NumDoc
  ,'NC' as TipoDoc
  ,max(T0.DocDate) as 'fecha del doc',
  max(T0.DocDueDate) as 'Fecha de vencimiento',SUM(docTotal)*-1 as Total,
  Saldo= ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0) *-1 ,
  'NoVencido' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1<0 ),0) *-1,
  '1_30'    =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 0 and 30 ),0) *-1,
  '31_60'   =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 31 and 60 ),0) *-1,
  '61_90'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 61 and 90),0) *-1, 
  '91_120'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 91 and 120),0) *-1, 
  '121_mas'  =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 > 121),0) *-1
FROM ORIN T0
  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
  INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
  INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
WHERE T0.DocStatus='O' AND T0.CardCode=@CardCode
GROUP BY T0.cardname, T0.docnum,T0.CardCode ,T0.CardName


select 
  CardCode
  ,CardName 
  ,DocNum
  ,NumDoc
  ,TipoDoc
  ,Fecha
  ,FechaVencimiento
  ,Total
  ,Saldo
  ,NoVencido
  ,C_1_30
  ,C_31_60
  ,C_61_90
  ,C_91_120
  ,C_121_mas
from @Cartera
order by Fecha asc
QUERY CARTERA DE CLIENTE PARA SAPB1
Edd Lozano
Web | + posts

Full Stack Web Developer && SDK SAPB1 Developer.

Melómano, Gamer (Xbox), Comprador compulsivo de Amazon y Posiblemente con TDAH.

Scroll hacia arriba