Mensaje de SQL Server 245: error de conversión al convertir el valor varchar "indirecto" en el tipo de datos INT al ejecutar Cerrar año de proyecto a Datos Reales (AL.CPA.00)

En este artículo se proporciona una solución al error de conversión 245 de SQL Server que ocurre en Microsoft Dynamics SL.

Se aplica a: Microsoft Dynamics SL
Número de KB original: 2953415

Síntomas

Cuando se ejecuta Project Allocator (PA.PRO.00) en modo final o cuando se ejecuta el proceso Cerrar Año del Proyecto en Real (AL.CPA.00), puede recibir el siguiente error:

Mensaje de SQL Server 245: [Microsoft][SQL Server Native Client 11.0][SQL Server]Error de conversión al convertir el valor varchar "ACCOUNT CATAGORY" al tipo de datos int.value "ACCOUNT CATAGORY" al tipo de datos int.

Solución

Ejecute el siguiente script SQL en cada base de datos de aplicación.

-- FIX for bug 25152 for 2011 FP1
--
-- Run against SL Application databases

/****** Object: View [dbo].[PJvAIC_source] Script Date: 12/12/2012 13:30:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

alter VIEW [dbo].[PJvAIC_source]
AS
-- This is part of a group of SQL tables, views, and functions used by the PJAIC SQL stored procedure to
-- generate actual indirect cost amounts.

select pt.alloc_calc_type,
 pt.alloc_method_cd,
 pt.alloc_method1_cd,
 pt.alloc_rate,
 amount_01 = isnull(s.amount_01, 0), amount_02 = isnull(s.amount_02, 0), amount_03 = isnull(s.amount_03, 0),
 amount_04 = isnull(s.amount_04, 0), amount_05 = isnull(s.amount_05, 0), amount_06 = isnull(s.amount_06, 0),
 amount_07 = isnull(s.amount_07, 0), amount_08 = isnull(s.amount_08, 0), amount_09 = isnull(s.amount_09, 0),
 amount_10 = isnull(s.amount_10, 0), amount_11 = isnull(s.amount_11, 0), amount_12 = isnull(s.amount_12, 0),
 amount_13 = isnull(s.amount_13, 0), amount_14 = isnull(s.amount_14, 0), amount_15 = isnull(s.amount_15, 0),
 pt.begin_step,
 pt.end_step,
 pt.emp_CpnyId,
 pt.emp_gl_subacct,
 pt.fsyear_num,
 pt.from_method,
 pt.ptd_indirectgrp,
 pt.ytd_indirectgrp,
 pt.pjt_entity,
 post_acct_ptd_indirectgrp = g.ptd_indirectgrp,
 post_acct_ytd_indirectgrp = g.ytd_indirectgrp,
 pt.project,
 project_cpnyid = pt.CpnyId,
 pt.src_acct,
 pt.src_CpnyId,
 pt.src_gl_subacct,
 pt.step_number
from ( -- Need to union the project allocation method and the second project allocation method
 select distinct p.project, t.pjt_entity, p.alloc_method_cd, alloc_method1_cd = p.alloc_method_cd, a.alloc_calc_type,
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, a.ptd_indirectgrp, a.ytd_indirectgrp,
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct, s.src_CpnyId, s.src_gl_subacct, s.emp_CpnyId, s.emp_gl_subacct
 from PJPROJ p with (nolock)
 inner join PJPENT t with (nolock)
 on t.project = p.project
 inner join PJALLOC a with (nolock)
 on a.alloc_method_cd = p.alloc_method_cd
 -- Link to PJINDSRC without using step_number to get all the fiscal year numbers and source account cateogies possible for each step
 inner join PJINDSRC s with (nolock)
 on s.project = p.project
 and s.pjt_entity = t.pjt_entity
 and s.alloc_method_cd = a.alloc_method_cd
 where len(rtrim(p.alloc_method_cd)) <> 0
 and a.alloc_basis = 'A' -- Only interested in steps based on amounts
 union
 select distinct p.project, t.pjt_entity, p.alloc_method2_cd, p.alloc_method_cd, a.alloc_calc_type,
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, a.ptd_indirectgrp, a.ytd_indirectgrp,
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct, s.src_CpnyId, s.src_gl_subacct, s.emp_CpnyId, s.emp_gl_subacct
 from PJPROJ p with (nolock)
 inner join PJPENT t with (nolock)
 on t.project = p.project 
 inner join PJALLOC a with (nolock)
 on a.alloc_method_cd = p.alloc_method2_cd
 inner join PJINDSRC s with (nolock)
 on s.project = p.project
 and s.pjt_entity = t.pjt_entity
 and s.alloc_method_cd = a.alloc_method_cd
 where len(rtrim(p.alloc_method2_cd)) <> 0
 and a.alloc_basis = 'A'
 ) pt
-- Link to PJINDSRC once again, this time just to get amounts where they exists
left join PJINDSRC s with (nolock)
 on s.fsyear_num = pt.fsyear_num
 and s.project = pt.project
 and s.pjt_entity = pt.pjt_entity
 and s.src_CpnyId = pt.src_CpnyId
 and s.alloc_method_cd = pt.alloc_method_cd
 and s.step_number = pt.step_number
 and s.src_acct = pt.src_acct
 and s.src_gl_subacct = pt.src_gl_subacct
 and s.emp_CpnyId = pt.emp_CpnyId
 and s.emp_gl_subacct = pt.emp_gl_subacct
-- Link to get the post account category indirect groups
left join PJvPJACCT_IndirectGrps g
 on g.acct = dbo.PJfMask_acct(pt.post_acct, s.src_acct)

GO

go
ALTER VIEW [dbo].[PJvAIC_source_summary] 
AS 
-- This is part of a group of SQL tables, views, and functions used by the PJAIC_summary SQL stored procedure to 
-- generate actual indirect cost amounts. 

-- Dependent on: PJvPJINDSRC_summary, PJvPJACCT_IndirectGrps 

select pt.alloc_calc_type, 
 pt.alloc_method_cd, 
 pt.alloc_method1_cd, 
 pt.alloc_rate, 
 amount_01 = isnull(s.amount_01, 0), amount_02 = isnull(s.amount_02, 0), amount_03 = isnull(s.amount_03, 0), 
 amount_04 = isnull(s.amount_04, 0), amount_05 = isnull(s.amount_05, 0), amount_06 = isnull(s.amount_06, 0), 
 amount_07 = isnull(s.amount_07, 0), amount_08 = isnull(s.amount_08, 0), amount_09 = isnull(s.amount_09, 0), 
 amount_10 = isnull(s.amount_10, 0), amount_11 = isnull(s.amount_11, 0), amount_12 = isnull(s.amount_12, 0), 
 amount_13 = isnull(s.amount_13, 0), amount_14 = isnull(s.amount_14, 0), amount_15 = isnull(s.amount_15, 0), 
 pt.begin_step, 
 pt.end_step, 
 pt.fsyear_num, 
 pt.from_method, 
 pt.ptd_indirectgrp, 
 pt.ytd_indirectgrp, 
 pt.pjt_entity, 
 post_acct_ptd_indirectgrp = g.ptd_indirectgrp, 
 post_acct_ytd_indirectgrp = g.ytd_indirectgrp, 
 pt.project, 
 project_cpnyid = pt.CpnyId, 
 pt.src_acct, 
 pt.step_number 
from ( -- Need to union the project allocation method and the second project allocation method 
 select distinct p.project, t.pjt_entity, p.alloc_method_cd, alloc_method1_cd = p.alloc_method_cd, a.alloc_calc_type, 
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, -- HERE
 a.ptd_indirectgrp, a.ytd_indirectgrp, 
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct 
 from PJPROJ p with (nolock) 
 inner join PJPENT t with (nolock) 
 on t.project = p.project 
 inner join PJALLOC a with (nolock) 
 on a.alloc_method_cd = p.alloc_method_cd 
 -- Link to PJvINDSRC_summary without using step_number to get all the fiscal year numbers and source account cateogies possible for each step 
 inner join PJvPJINDSRC_summary s with (nolock) 
 on s.project = p.project 
 and s.pjt_entity = t.pjt_entity 
 and s.alloc_method_cd = a.alloc_method_cd 
 where len(rtrim(p.alloc_method_cd)) <> 0 
 and a.alloc_basis = 'A' -- Only interested in steps based on amounts 
 union 
 select distinct p.project, t.pjt_entity, p.alloc_method2_cd, p.alloc_method_cd, a.alloc_calc_type, 
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, -- HERE
 a.ptd_indirectgrp, a.ytd_indirectgrp, 
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct 
 from PJPROJ p with (nolock) 
 inner join PJPENT t with (nolock) 
 on t.project = p.project 
 inner join PJALLOC a with (nolock) 
 on a.alloc_method_cd = p.alloc_method2_cd 
 inner join PJvPJINDSRC_summary s with (nolock) 
 on s.project = p.project 
 and s.pjt_entity = t.pjt_entity 
 and s.alloc_method_cd = a.alloc_method_cd 
 where len(rtrim(p.alloc_method2_cd)) <> 0 
 and a.alloc_basis = 'A' 
 ) pt 
-- Link to PJINDSRC once again, this time just to get amounts where they exists 
left join PJvPJINDSRC_summary s with (nolock) 
 on s.fsyear_num = pt.fsyear_num 
 and s.project = pt.project 
 and s.pjt_entity = pt.pjt_entity 
 and s.src_acct = pt.src_acct 
 and s.alloc_method_cd = pt.alloc_method_cd 
 and s.step_number = pt.step_number 
-- Link to get the post account category indirect groups 
left join PJvPJACCT_IndirectGrps g 
 on g.acct = dbo.PJfMask_acct(pt.post_acct, s.src_acct) 

GO