Symptoms

When you run Project Allocator (PA.PRO.00) in Final Mode or when you run the Close Project Year at Actual (AL.CPA.00) process, you may receive the following error:

SQL Server Message 245: [Microsoft][SQL Server Native Client 11.0][SQL
Server]Conversion failed when converting the varchar
value 'ACCOUNT CATAGORY' to data type int.value 'ACCOUNT CATAGORY' to data type int.



Resolution

Run the following SQL Script against each application database.

-- 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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×