Hola,
Me dieron este Scrpt de Sap Sql que lo convirtiera a Hana, por fa necesito ayuda.
if object_id('tempdb.dbo.#Tmp1') is not null drop table #Tmp1;
if object_id('tempdb.dbo.#Tmp2') is not null drop table #Tmp2;
if object_id('tempdb.dbo.#Tmp3') is not null drop table #Tmp3;
if object_id('tempdb.dbo.#Tmp4') is not null drop table #Tmp4;
if object_id('tempdb.dbo.#Tmp5') is not null drop table #Tmp5;
if object_id('tempdb.dbo.#Tmp6') is not null drop table #Tmp6;
if object_id('tempdb.dbo.#Tmp7') is not null drop table #Tmp7;
/* Select * From [dbo].[OIPF] T0 */
Declare @F1 Date='20190602'
Declare @F2 Date='20190701'
/* Where */
select @F1 /* T0.[CreateDate] */= '[%0]'
----/* And */
Select @F2 /* T0.[CreateDate] */= '[%1]'
---- TODO LO RECAUDADO
SELECT distinct
T3.SlpName,T1.DocNum DocNumFAC,
SUM(T1.SumApplied) MontoRecaudado into #Tmp1
FROM ORCT T0
INNER JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
INNER JOIN OINV T2 ON T1.DocEntry=T2.DocEntry
LEFT JOIN OSLP T3 ON T2.SlpCode=T3.SlpCode
WHERE T0.DocDate BETWEEN @F1 AND @F2
--AND T0.DocNum=106756
GROUP BY T3.SlpName,T1.DocNum
---TODO LO EFECTIVIZAEDO SIN CHEQUES
SELECT distinct
T3.SlpName,T0.DocNum DocNumPR,
SUM(t0.CashSum+t0.CreditSum+t0.TrsfrSum) MontoEfectivizado into #Tmp2
FROM ORCT T0
INNER JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
INNER JOIN OINV T2 ON T1.DocEntry=T2.DocEntry
LEFT JOIN OSLP T3 ON T2.SlpCode=T3.SlpCode
WHERE T0.DocDate BETWEEN @F1 AND @F2
--AND T0.DocNum=106756
GROUP BY T3.SlpName,T0.DocNum
--- TODOS LOS CHEQUES EFECTIVIZADOS
SELECT DISTINCT
T3.SlpName,
T4.CheckKey,t4.CheckNum,t4.BankCode,t4.CheckDate,t4.CardCode,t4.RcptNum,t4.CheckSum ChqEfectivizado into #Tmp3
FROM ORCT T0
INNER JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
INNER JOIN OINV T2 ON T1.DocEntry=T2.DocEntry
LEFT JOIN OSLP T3 ON T2.SlpCode=T3.SlpCode
INNER JOIN OCHH T4 ON T0.DocEntry=T4.RcptNum
WHERE T0.DocDate BETWEEN @F1 AND @F2
/*AND T2.DocStatus='C' */ and Deposited='C'
--TOTAL DE EFECTIVIZADO ----->
select t0.SlpName,sum(t0.MontoEfectivizado) MontoEfectivizado into #Tmp4 from(
SELECT T0.SlpName,sUM(T0.MontoEfectivizado) MontoEfectivizado FROM #Tmp2 T0
group by T0.SlpName
UNION ALL
SELECT T0.SlpName,sUM(T0.ChqEfectivizado)FROM #Tmp3 T0
group by T0.SlpName
) t0 group by t0.SlpName
--- efectivisado con facturas cerradas
SELECT DISTINCT
T3.SlpName,
T4.CheckKey,t4.CheckNum,t4.BankCode,t4.CheckDate,t4.CardCode,t4.RcptNum,t4.CheckSum ChqEfectivizado into #Tmp5
FROM ORCT T0
INNER JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
INNER JOIN OINV T2 ON T1.DocEntry=T2.DocEntry
LEFT JOIN OSLP T3 ON T2.SlpCode=T3.SlpCode
INNER JOIN OCHH T4 ON T0.DocEntry=T4.RcptNum
WHERE T0.DocDate BETWEEN @F1 AND @F2
AND T2.DocStatus='C' and Deposited='C'
---consolidado facturas cerradas
select t0.SlpName ,sum(t0.ChqEfectivizado) CierraFActura INTO #Tmp6 from #Tmp5 t0 group by T0.SlpName
SELECT T0.SlpName,SUM(T0.MontoRecaudado) MontoRecaudado INTO #Tmp7 from #Tmp1 t0 group by T0.SlpName
select T3.U_MEG_SUCURSAL,
T0.SlpName VENDEDOR,T0.MontoRecaudado,ISNULL(T1.MontoEfectivizado,0) MontoEfectivizado,ISNULL(T2.CierraFActura,0) CierraFActura,
ISNULL(T1.MontoEfectivizado,0)-ISNULL(T2.CierraFActura,0) PagoParcial
from #Tmp7 T0
LEFT JOIN #Tmp4 T1 ON T0.SlpName=T1.SlpName
LEFT JOIN #Tmp6 T2 ON T0.SlpName=T2.SlpName
LEFT JOIN OSLP T3 ON T0.SlpName=T3.SlpName
if object_id('tempdb.dbo.#Tmp1') is not null drop table #Tmp1;
if object_id('tempdb.dbo.#Tmp2') is not null drop table #Tmp2;
if object_id('tempdb.dbo.#Tmp3') is not null drop table #Tmp3;
if object_id('tempdb.dbo.#Tmp4') is not null drop table #Tmp4;
if object_id('tempdb.dbo.#Tmp5') is not null drop table #Tmp5;
if object_id('tempdb.dbo.#Tmp6') is not null drop table #Tmp6;
if object_id('tempdb.dbo.#Tmp7') is not null drop table #Tmp7;
