Ayuda sql Server - Procedimiento Almacenado
Publicado por Alberto (9 intervenciones) el 12/01/2021 04:15:56
Buenas Tardes amigos;
Tengo el Siguiente Procedimiento almacenado solo tiene un parámetro, pero al ejecutarlo me sale error de conversión pero no se donde modificar por favor ayúdenme, Gracias.
EXEC [dbo].[SYP_RPT_CAJACHICA_METAX] 'C1011103-21001'
Este el el error:
Msg 245, Level 16, State 1, Procedure SYP_RPT_CAJACHICA_METAX, Line 20 [Batch Start Line 54]
Conversion failed when converting the nvarchar value '1011111 -' to data type int.
Tengo el Siguiente Procedimiento almacenado solo tiene un parámetro, pero al ejecutarlo me sale error de conversión pero no se donde modificar por favor ayúdenme, Gracias.
EXEC [dbo].[SYP_RPT_CAJACHICA_METAX] 'C1011103-21001'
Este el el error:
Msg 245, Level 16, State 1, Procedure SYP_RPT_CAJACHICA_METAX, Line 20 [Batch Start Line 54]
Conversion failed when converting the nvarchar value '1011111 -' to data type int.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
USE [PRUEBA_METAX]
GO
/****** Object: StoredProcedure [dbo].[SYP_RPT_CAJACHICA_METAX] Script Date: 11/01/2021 17:34:46 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SYP_RPT_CAJACHICA_METAX]
(@ref1 nvarchar(30)
)AS
DECLARE @accountNum as int
DECLARE @MainCurr as nvarchar(10)
DECLARE @Comments1 As NVARCHAR(100)
DECLARE @Comments2 As NVARCHAR(100)
BEGIN
SET @accountNum = (SELECT B."AcctCode" FROM OVPM A INNER JOIN VPM4 B on A."DocEntry" = B."DocNum" WHERE A."U_SYP_TPOOPER" = 'CC01' and A."Canceled" = 'N' and A."U_SYP_REF2" = @ref1)
SET @MainCurr = (SELECT "MainCurncy" FROM OADM)
--SET @Comments1 = (SELECT A."Comments" FROM OVPM A INNER JOIN VPM4 B on A."DocEntry" = B."DocNum" WHERE A."U_SYP_TPOOPER" = 'CC01' and A."Canceled" = 'N' and A."U_SYP_REF2" = @ref1)
--SET @Comments2 = (SELECT A."JrnlMemo" FROM OVPM A INNER JOIN VPM4 B on A."DocEntry" = B."DocNum" WHERE A."U_SYP_TPOOPER" = 'CC01' and A."Canceled" = 'N' and A."U_SYP_REF2" = @ref1)
SELECT T0."TransId", T0."TransType", T0."BaseRef", T1."Account", ISNULL(T1."FCCurrency",'S/') as "Moneda", CASE WHEN T1."Debit" > 0 THEN T1."Debit" ELSE -1 * T1."Credit" END AS "Amount",
T1."Ref2", T1."LineMemo",CASE WHEN T1."Debit" > 0 THEN 'in' ELSE 'out' END as "Type",T0."RefDate", ISNULL(T0."Indicator",'') as "Indicator",
CASE T0."TransType" WHEN 46 THEN (SELECT A."U_SYP_TPOOPER" FROM OVPM A WHERE A."DocEntry" = T0."BaseRef") END AS "TipoPago",
CASE T0."TransType" WHEN 46 THEN CASE (SELECT A."DocType" FROM OVPM A WHERE A."DocEntry" = T0."BaseRef")
WHEN 'S' THEN CASE WHEN (SELECT COUNT(*) FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") > 1 THEN 'Varios Documentos'
WHEN (SELECT COUNT(*) FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") = 0 THEN 'Pago a Cuenta' + (SELECT A."CardCode" FROM OVPM A WHERE A."DocNum" = T0."BaseRef")
ELSE CASE (SELECT B."InvType" FROM VPM2 B WHERE B."DocNum" = T0."BaseRef")
WHEN 18 THEN (SELECT C."CardName" + ' (' + C."U_SYP_MDTD" + '-' + C."U_SYP_MDSD" + '-' + C."U_SYP_MDCD" +')' FROM OPCH C WHERE C."DocEntry" = (SELECT B."DocEntry" FROM VPM2 B WHERE B."DocNum" = T0."BaseRef"))
END
END
WHEN 'A' THEN CASE WHEN (SELECT COUNT(*) FROM VPM4 B WHERE B."DocNum" = T0."BaseRef") > 1 THEN 'Varios Gastos'
ELSE ISNULL((SELECT B."Descrip" FROM VPM4 B WHERE B."DocNum" = T0."BaseRef"),T1."LineMemo")
END
WHEN 'C' THEN CASE WHEN (SELECT U_SYP_TPOOPER FROM OVPM B WHERE B."DocEntry" = T0."BaseRef") = 'ERC1' THEN 'Apertura ER (' + (SELECT U_SYP_NUMER FROM OVPM B WHERE B."DocEntry" = T0."BaseRef") + ')'
WHEN (SELECT U_SYP_TPOOPER FROM OVPM B WHERE B."DocEntry" = T0."BaseRef") = 'ERC2' THEN 'Reembolso ER (' + (SELECT U_SYP_NUMER FROM OVPM B WHERE B."DocEntry" = T0."BaseRef") + ')'
END
END
END AS "Desc",
(SELECT A."DocType" FROM OVPM A WHERE A."DocEntry" = T0."BaseRef") AS "tipoOVPM",
(SELECT B."InvType" FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") AS "TipoDOC",
(SELECT ISNULL(C."Comments",'') FROM OPCH C WHERE C."DocEntry" = (SELECT B."DocEntry" FROM VPM2 B WHERE B."DocNum" = T0."BaseRef"))
AS "Comentario",
CASE T0."TransType" WHEN 46 THEN CASE (SELECT A."DocType" FROM OVPM A WHERE A."DocEntry" = T0."BaseRef")
WHEN 'S' THEN CASE WHEN (SELECT COUNT(*) FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") > 1 THEN 'OVPM'
WHEN (SELECT COUNT(*) FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") = 0 THEN 'OVPM'
ELSE CASE (SELECT B."InvType" FROM VPM2 B WHERE B."DocNum" = T0."BaseRef")
WHEN 18 THEN 'OPCH'END
END
WHEN 'A' THEN 'OVPM'
WHEN 'C' THEN 'OVPM'
END
WHEN 30 THEN 'OJDT'END AS "Table",
CASE T0."TransType" WHEN 46 THEN CASE (SELECT A."DocType" FROM OVPM A WHERE A."DocEntry" = T0."BaseRef")
WHEN 'S' THEN CASE WHEN (SELECT COUNT(*) FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") > 1 THEN T0."BaseRef"
WHEN (SELECT COUNT(*) FROM VPM2 B WHERE B."DocNum" = T0."BaseRef") = 0 THEN T0."BaseRef"
ELSE CASE (SELECT B."InvType" FROM VPM2 B WHERE B."DocNum" = T0."BaseRef")
WHEN 18 THEN (SELECT A."DocEntry" FROM VPM2 A WHERE A."DocNum" = T0."BaseRef")
END
END
WHEN 'A' THEN T0."BaseRef"
END
WHEN 30 THEN T0."BaseRef"
END AS "DocEntry",
(SELECT "CompnyName" FROM OADM) as "Company", @MainCurr as "MainCurr", @accountNum as "CuentaCC" , @Comments1 as "Comentarios 1", @Comments2 as "Comentarios 2"
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId"
WHERE "Account" = @accountNum and T0."StornoToTr" is null
and T0."TransId" not in (SELECT DISTINCT A1."StornoToTr" FROM OJDT A1 WHERE "StornoToTr" is not null)
and T1."Ref1" = @ref1
ORDER BY T0."RefDate", T0."TransId";
END
Valora esta pregunta
0
