Buenas tardes, tengo una duda, en este Post http://scn.sap.com/thread/2115797 Hay una consulta de Erik que me gustaria pasarla a crystal pero no se como pasarle los parametros @BpCode, @Fechain,@FechaFin, para que crystal al ejecutarlo los pida.
Alguien sabe?
Gracias y Saludos.
Estado de Cuenta
DECLARE @BPCode AS VARCHAR(15)
DECLARE @FechaIn AS DATETIME
DECLARE @FechaFin AS DATETIME
DECLARE @SaldoInicial AS NUMERIC(19,6)
DECLARE @SaldoFinal AS NUMERIC(19,6)
DECLARE @SaldoVencido AS NUMERIC(19,6)
SELECT @BPCode = T0.CardCode, @FechaIn = T1.RefDate, @FechaFin = T2.RefDate FROM OCRD T0 , JDT1 T1, JDT1 T2 WHERE T0.CardCode ='[%0]' AND T1.RefDate ='[%1]' AND T2.RefDate ='[%2]'
SET @BPCode = '[%0]'
SET @FechaIn = CONVERT(DATETIME, '[%1]', 112)
SET @FechaFin = CONVERT(DATETIME, '[%2]', 112)
SELECT @SaldoInicial = ISNULL((SUM(T0.Debit) - SUM(T0.Credit)),0) FROM JDT1 T0 WHERE T0.ShortName = @BPCode AND T0.RefDate <= @FechaIn
SELECT @SaldoFinal = ISNULL((SUM(T0.Debit) - SUM(T0.Credit)),0) FROM JDT1 T0 WHERE T0.ShortName = @BPCode AND T0.RefDate <= @FechaFin
SELECT @SaldoVencido = ISNULL(SUM(ISNULL(T0.DocTotal,0)),0) FROM OINV T0
WHERE T0.CardCode = @BPCode AND NOT T0.DocEntry IN
(SELECT T1.DocEntry FROM RCT2 T1 INNER JOIN ORCT T2 ON T2.DocNum = T1.DocNum WHERE T2.CardCode = @BPCode)
AND T0.DocDueDate < GETDATE()
SELECT IDENTITY(INT,1,1) AS TID, * INTO #PreEdoCta1 FROM (
SELECT TOP 999999999 Tbl1.RefDate, CASE(Tbl1.Transtype)
WHEN(24) THEN
T6.DocNum
ELSE
Num_Factura
END AS Num_Factura, CASE(Tbl1.Transtype)
WHEN(24) THEN
T6.NumAtCard
ELSE
Ref_Factura
END AS Ref_Factura, Tbl1.Ref1 AS Documento_Destino, FolioFiscal, Cargo, Abono,
Vence, FormaPago,
CASE Tbl1.TransType
WHEN 13 THEN 1
WHEN 14 THEN 2
WHEN 24 THEN 5
END AS MV, Notas, Tbl1.TransID, Line_ID FROM
(
SELECT T0.RefDate, T0.TransId, T0.Line_ID,
CASE(T0.Transtype)
WHEN(14) THEN
T4.Num_Factura
WHEN(13) THEN
T0.Ref1
WHEN(24) THEN
NULL
END AS Num_Factura,
CASE(T0.Transtype)
WHEN(14) THEN
T4.NumAtCard
WHEN(13) THEN
T2.NumAtCard
WHEN(24) THEN
NULL
END AS Ref_Factura,
CASE(T0.Transtype)
WHEN(14) THEN
CAST(T3.FolioPref AS VARCHAR(100)) + '-' + CAST(T3.FolioNum AS VARCHAR(100))
WHEN(13) THEN
T2.NumAtCard
END AS FolioFiscal,
CASE(T0.Transtype)
WHEN(14) THEN
Credit * (-1)
ELSE
Debit
END AS Cargo, CASE(T0.Transtype)
WHEN(14) THEN
Debit * (-1)
WHEN(24) THEN
ISNULL(T1.SumApplied, T0.Credit)
ELSE
Credit
END AS Abono, CASE(T0.Transtype)
WHEN(14) THEN
T3.DocDueDate
WHEN(13) THEN
T2.DocDueDate
WHEN(24) THEN
T7.DocDueDate
END AS Vence, T0.Ref1, T0.TransType, T1.DocEntry AS FactDestinoPago,
CASE TransType
WHEN 13 THEN T2.Comments
WHEN 14 THEN T3.Comments
WHEN 24 THEN T7.Comments
WHEN 30 THEN T0.LineMemo
END AS Notas,
CASE WHEN(T7.CashSum > 0) THEN
'EFECTV'
WHEN(T7.CreditSum > 0) THEN
'TRCRED'
WHEN(T7.[CheckSum] > 0) THEN
'CHEQUE'
WHEN(T7.TrsfrSum > 0) THEN
'TRBANC'
END AS FormaPago
FROM JDT1 T0
LEFT JOIN (SELECT T0.TransId, T0.Line_ID, T1.DocNum, ISNULL(SumApplied, T0.Credit) AS SumApplied,
CASE WHEN(InvType = 13) THEN FactOrigen ELSE '' END AS DocEntry, InvType
FROM JDT1 T0 LEFT JOIN (SELECT Tx1.DocNum, Tx0.DocEntry FactOrigen, Tx0.InvType,
(CASE WHEN(Tx0.InvType IN (13,24,30,46)) THEN Tx0.SumApplied
ELSE (Tx0.SumApplied * (-1)) END * CASE WHEN((Tx1.DocTotal + (Tx1.NoDocSum * (-1))) = 0) THEN 1
ELSE(((Tx1.DocTotal*100)/(Tx1.DocTotal + (Tx1.NoDocSum * (-1))))/100) END )
AS SumApplied
FROM RCT2 Tx0 LEFT JOIN ORCT Tx1 ON
Tx0.DocNum = Tx1.DocNum WHERE Tx1.Canceled = 'N')
T1 ON T0.Ref1 = T1.DocNum WHERE T0.SHORTNAME = @BPCode AND T0.TransType = 24
UNION ALL
SELECT Ty3.TransId, Ty3.Line_ID, Ty2.DocNum, Ty1.DocTotal AS SumApplied, 0 AS DocEntry, 24 AS InvType FROM (
SELECT Ty0.DocNum, SUM(CASE WHEN(Ty0.InvType IN (13,24,30,46)) THEN Ty0.SumApplied
ELSE (Ty0.SumApplied * (-1)) END) AS Suma FROM RCT2 Ty0
GROUP BY Ty0.DocNum) Ty2 LEFT JOIN ORCT Ty1 ON Ty2.DocNum = Ty1.DocNum
LEFT JOIN JDT1 Ty3 ON Ty2.DocNum = Ty3.Ref1
WHERE Ty1.CardCode = @BPCode AND Ty2.Suma = 0 AND Ty3.SHORTNAME = @BPCode AND Ty3.TransType = 24) T1 ON T0.TransId = T1.TransId AND T0.Line_Id = T1.Line_Id
LEFT JOIN OINV T2 ON T0.TransId = T2.TransId
LEFT JOIN ORIN T3 ON T0.TransId = T3.TransId
LEFT JOIN (SELECT DISTINCT TD.BaseRef Num_Factura, TH.DocEntry, TF.NumAtCard FROM RIN1 TD RIGHT JOIN ORIN TH
ON TH.DocEntry = TD.DocEntry LEFT JOIN OINV TF ON TD.BaseEntry = TF.DocEntry
WHERE NOT TD.BaseRef IS NULL AND TD.BaseType = 13) T4 ON T3.DocEntry = T4.DocEntry
LEFT JOIN ORCT T7 ON T0.Ref1 = CAST(T7.DocNum AS VARCHAR(30)) AND T0.TransType = 24
WHERE T0.ShortName = @BPCode AND
T0.RefDate >= @FechaIn AND T0.RefDate <= @FechaFin
) Tbl1 LEFT JOIN OINV T6 ON Tbl1.FactDestinoPago = T6.DocEntry
ORDER BY Refdate, FolioFiscal, Num_Factura ASC
) TblEdoCta1
SELECT 0 AS TID, 0 as transid, NULL AS Fecha, NULL AS Num_Factura, 'Cliente: ' + T0.CardCode + ' - ' + T0.CardName + CHAR(10) + CHAR(13) + T0.Address AS Ref_Factura, NULL AS FolioFiscal,
'Saldo Actual: ' AS Documento_Destino, T0.Balance AS Cargo, NULL AS Abono, NULL AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV,
'RFC: ' + T0.LicTradNum AS Referencia, 'CP: ' + T0.ZipCode AS C9, 'Tel: ' + T0.Phone1 AS C1, 'Contacto: ' + T0.CntctPrsn AS C2,
'Vendedor: ' + T1.SlpName AS C3, 'Límite de Crédito: ' + CAST(T0.CreditLine AS VARCHAR(30)) AS C4, 'Saldo Vencido: ' + CAST(@SaldoVencido AS VARCHAR(30)) + '' AS C5
FROM OCRD T0 LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CardType <> N'L' AND T0.CardCode >= '' + @BPCode + '' AND
T0.CardCode <= '' + @BPCode + ''
UNION ALL
SELECT 0 AS TID,0 as transid, NULL AS Fecha, NULL AS Num_Factura, NULL AS Ref_Factura, 'SALDO FINAL' AS FolioFiscal, NULL AS Documento_Destino,
NULL AS Cargo, NULL AS Abono, @SaldoFinal AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV,
NULL AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5
UNION ALL
SELECT 0 AS TID,0 as transid,NULL AS Fecha, NULL AS Num_Factura, 'Fecha de Análisis Inicial: ' + CAST(@FechaIn AS VARCHAR(100)) + '' AS Ref_Factura,
'Fecha de Análisis Final: ' + CAST(@FechaFin AS VARCHAR(100)) + '' AS FolioFiscal, NULL AS Documento_Destino, NULL AS Cargo, NULL AS Abono,
NULL AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV, NULL AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5
UNION ALL
SELECT 0 AS TID,0 as transid, NULL AS Fecha, NULL AS Num_Factura, NULL AS Ref_Factura, 'SALDO INICIAL' AS FolioFiscal, NULL AS Documento_Destino,
NULL AS Cargo, @SaldoInicial AS Abono, @SaldoInicial AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV,
NULL AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5
UNION ALL
(SELECT TID, transid, RefDate AS Fecha, Num_Factura, Ref_Factura, FolioFiscal, Documento_Destino, Cargo, Abono,
0 as 'Saldo' ,
Vence, FormaPago, MV, CASE MV
WHEN 1 THEN 'Fact. ' + CAST(Num_Factura AS VARCHAR(15))
WHEN 2 THEN 'NC. a Factura: ' + CAST(Num_Factura AS VARCHAR(15))
WHEN 5 THEN 'Pago a Factura :' + CAST(Num_Factura AS VARCHAR(15))
ELSE Notas
END AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5 FROM #PreEdoCta1 a)
order by num_factura, mv asc
DROP TABLE #PreEdoCta1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.