2024-11-19 16:51:37 3159	保存单据状态信息到中间表失败：   支出预算执行率查询报表  给归口预算管理员也加一个权限  修改了原有的sql语句
select  x.预算编号,
		x.归口部门,
		x.分类,
		x.一级编码,
		x.一级,
		x.预算类型,
		x.支出明细编码,
		x.支出明细,
		x.项目名称编码,
		x.项目名称,
		x.预算类别,
		x.预算来源,
		x.预算年度,
		x.年初预算,
		x.预算金额,
		x.调整金额,
		--x.导入执行数 '已申请金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已申请金额 end '已申请金额',
		--x.预算金额-x.导入执行数 '申请剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.申请剩余 end '申请剩余',
		--x.导入执行数 '已支付金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已支付金额 end '已支付金额',
		--x.预算金额-x.导入执行数 '支付剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.支付剩余 end '支付剩余',
		case when x.导入执行数>0 then (case when x.年初预算=0 or x.预算金额=0 then '0.00'+'%' else 
		format( x.导入执行数/(isnull(x.年初预算,x.预算金额)) *100,'N2')+'%' end) else (case when x.预算金额=0 then '0.00'+'%' else 
        format( x.已申请金额/x.预算金额 *100,'N2')+'%' end)	
end as 执行率,
		case when x.导入执行数>0 then '导入数据' else '内控数据' end '数据来源'
from 
(select  
b.budgetcode 预算编号,
org.NAME 归口部门,
b.fl 分类,
P.FCOSTSITEMC 一级编码,
p.FCOSTSITEMN 一级,
b.ysnx 预算类型,
s.FCOSTSITEMLC 支出明细编码,
s.FCOSTSITEMLN 支出明细,
f.code 项目名称编码,
f.name 项目名称,
t.name 预算类别,
g.valuename 预算来源,
l.valuename 预算年度,
b.InitialAmount 年初预算,
isnull(z.budgetamount,b.BUDGETAMOUNT) 预算金额,
isnull(z.ajustamount,b.AJUSTAMOUNT) 调整金额,
isnull(z.frozenamount,b.FROZENAMOUNT) 已申请金额,
isnull(z.frozenremain,b.FROZENREMAIN) 申请剩余,
isnull(z.isusedamount,b.ISUSEDAMOUNT) 已支付金额,
isnull(z.isusedremain,b.ISUSEDREMAIN) 支付剩余
--,case when z.BUDGETAMOUNT=0 or b.BUDGETAMOUNT=0 then '0' else 
-- format( (isnull(z.FROZENAMOUNT,b.FROZENAMOUNT))/(isnull(z.BUDGETAMOUNT,b.BUDGETAMOUNT)) *100,'N2')+'%'
--end as 执行率
,SUM(zxqk.BUDGETAMOUNTZX) 导入执行数
from rate_budgetaccount b 
left join RATE_BUDGETRESOLVEL z on b.id=z.sheetpointer_xid
left join D_FINANCECOSTSITEM p on p.id= b.FCOSTSITEM_XID
left join D_FINANCECOSTSITEML s on s.id=b.FCOSTSITEML_XID
left join D_FINANCEACCOUNT f on f.id=b.FINANCEACCOUNT_XID 
left join D_PARAMETER_LINE l on l.id= b.ANNUAL_XID 
left join d_organization o on o.id=z.orgcode_xid 
left join Rate_BudgetType t on t.id=b.BudgeType_xid
left join d_organization o1 on o1.id=b.comcode_xid
left join D_FunctionAccount d on b.FunctionAccount_xid=d.id
left join D_EconomicsAccount e on b.economicsaccount_xid=e.id
left join D_Parameter_Line  g on b.budgetsource_xid=g.id
left join d_organization org on org.id=b.UPORGNAME_XID
left join AD_BudgetImportZC zxqk on zxqk.budgetCode=b.budgetCode and zxqk.budgetCode=b.budgetCode
where f.NAME not like '%测试预算%' and  f.NAME not like '%资金池%' and b.FL!='收入' and  l.valuename='2024'  and b.BUDGETAMOUNT!=0 
and  b.UPORGNAME_XID in (


(select d.ID from   AD_WorkFlowRole  a
left join  d_user  b on  a.USERCODE_XID=b.id
left  join AD_WorkFlowRoleDepart c on  a.id=c.SHEETPOINTER_XID
left  join  D_ORGANIZATION d  on c.ORGNAME_XID=d.id
where convert(varchar(50),b.id) ='null')
union all
(select c.id  from  GKContrast a
left  join  D_USER b on  a.USERCODE_XID=b.id
left  join  D_ORGANIZATION  c  on a.ORGCODE_XID=c.ID
where convert(varchar(50),b.id) ='100001154')



)
group by b.BUDGETCODE,org.NAME,b.fl,P.FCOSTSITEMC,s.FCOSTSITEMLC,f.code,
p.FCOSTSITEMN,
b.ysnx,
s.FCOSTSITEMLN,
f.name,
t.name,
g.valuename,
l.valuename,
b.InitialAmount,
isnull(z.budgetamount,b.BUDGETAMOUNT),
isnull(z.ajustamount,b.AJUSTAMOUNT),
isnull(z.frozenamount,b.FROZENAMOUNT),
isnull(z.frozenremain,b.FROZENREMAIN),
isnull(z.isusedamount,b.ISUSEDAMOUNT),
isnull(z.isusedremain,b.ISUSEDREMAIN)
) as x

参数名: Invalid value for Cause
实际值是    支出预算执行率查询报表  给归口预算管理员也加一个权限  修改了原有的sql语句
select  x.预算编号,
		x.归口部门,
		x.分类,
		x.一级编码,
		x.一级,
		x.预算类型,
		x.支出明细编码,
		x.支出明细,
		x.项目名称编码,
		x.项目名称,
		x.预算类别,
		x.预算来源,
		x.预算年度,
		x.年初预算,
		x.预算金额,
		x.调整金额,
		--x.导入执行数 '已申请金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已申请金额 end '已申请金额',
		--x.预算金额-x.导入执行数 '申请剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.申请剩余 end '申请剩余',
		--x.导入执行数 '已支付金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已支付金额 end '已支付金额',
		--x.预算金额-x.导入执行数 '支付剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.支付剩余 end '支付剩余',
		case when x.导入执行数>0 then (case when x.年初预算=0 or x.预算金额=0 then '0.00'+'%' else 
		format( x.导入执行数/(isnull(x.年初预算,x.预算金额)) *100,'N2')+'%' end) else (case when x.预算金额=0 then '0.00'+'%' else 
        format( x.已申请金额/x.预算金额 *100,'N2')+'%' end)	
end as 执行率,
		case when x.导入执行数>0 then '导入数据' else '内控数据' end '数据来源'
from 
(select  
b.budgetcode 预算编号,
org.NAME 归口部门,
b.fl 分类,
P.FCOSTSITEMC 一级编码,
p.FCOSTSITEMN 一级,
b.ysnx 预算类型,
s.FCOSTSITEMLC 支出明细编码,
s.FCOSTSITEMLN 支出明细,
f.code 项目名称编码,
f.name 项目名称,
t.name 预算类别,
g.valuename 预算来源,
l.valuename 预算年度,
b.InitialAmount 年初预算,
isnull(z.budgetamount,b.BUDGETAMOUNT) 预算金额,
isnull(z.ajustamount,b.AJUSTAMOUNT) 调整金额,
isnull(z.frozenamount,b.FROZENAMOUNT) 已申请金额,
isnull(z.frozenremain,b.FROZENREMAIN) 申请剩余,
isnull(z.isusedamount,b.ISUSEDAMOUNT) 已支付金额,
isnull(z.isusedremain,b.ISUSEDREMAIN) 支付剩余
--,case when z.BUDGETAMOUNT=0 or b.BUDGETAMOUNT=0 then '0' else 
-- format( (isnull(z.FROZENAMOUNT,b.FROZENAMOUNT))/(isnull(z.BUDGETAMOUNT,b.BUDGETAMOUNT)) *100,'N2')+'%'
--end as 执行率
,SUM(zxqk.BUDGETAMOUNTZX) 导入执行数
from rate_budgetaccount b 
left join RATE_BUDGETRESOLVEL z on b.id=z.sheetpointer_xid
left join D_FINANCECOSTSITEM p on p.id= b.FCOSTSITEM_XID
left join D_FINANCECOSTSITEML s on s.id=b.FCOSTSITEML_XID
left join D_FINANCEACCOUNT f on f.id=b.FINANCEACCOUNT_XID 
left join D_PARAMETER_LINE l on l.id= b.ANNUAL_XID 
left join d_organization o on o.id=z.orgcode_xid 
left join Rate_BudgetType t on t.id=b.BudgeType_xid
left join d_organization o1 on o1.id=b.comcode_xid
left join D_FunctionAccount d on b.FunctionAccount_xid=d.id
left join D_EconomicsAccount e on b.economicsaccount_xid=e.id
left join D_Parameter_Line  g on b.budgetsource_xid=g.id
left join d_organization org on org.id=b.UPORGNAME_XID
left join AD_BudgetImportZC zxqk on zxqk.budgetCode=b.budgetCode and zxqk.budgetCode=b.budgetCode
where f.NAME not like '%测试预算%' and  f.NAME not like '%资金池%' and b.FL!='收入' and  l.valuename='2024'  and b.BUDGETAMOUNT!=0 
and  b.UPORGNAME_XID in (


(select d.ID from   AD_WorkFlowRole  a
left join  d_user  b on  a.USERCODE_XID=b.id
left  join AD_WorkFlowRoleDepart c on  a.id=c.SHEETPOINTER_XID
left  join  D_ORGANIZATION d  on c.ORGNAME_XID=d.id
where convert(varchar(50),b.id) ='null')
union all
(select c.id  from  GKContrast a
left  join  D_USER b on  a.USERCODE_XID=b.id
left  join  D_ORGANIZATION  c  on a.ORGCODE_XID=c.ID
where convert(varchar(50),b.id) ='100001154')



)
group by b.BUDGETCODE,org.NAME,b.fl,P.FCOSTSITEMC,s.FCOSTSITEMLC,f.code,
p.FCOSTSITEMN,
b.ysnx,
s.FCOSTSITEMLN,
f.name,
t.name,
g.valuename,
l.valuename,
b.InitialAmount,
isnull(z.budgetamount,b.BUDGETAMOUNT),
isnull(z.ajustamount,b.AJUSTAMOUNT),
isnull(z.frozenamount,b.FROZENAMOUNT),
isnull(z.frozenremain,b.FROZENREMAIN),
isnull(z.isusedamount,b.ISUSEDAMOUNT),
isnull(z.isusedremain,b.ISUSEDREMAIN)
) as x
。
2024-11-19 16:51:46 9985	保存单据状态信息到中间表失败：   支出预算执行率查询报表  给归口预算管理员也加一个权限  修改了原有的sql语句
select  x.预算编号,
		x.归口部门,
		x.分类,
		x.一级编码,
		x.一级,
		x.预算类型,
		x.支出明细编码,
		x.支出明细,
		x.项目名称编码,
		x.项目名称,
		x.预算类别,
		x.预算来源,
		x.预算年度,
		x.年初预算,
		x.预算金额,
		x.调整金额,
		--x.导入执行数 '已申请金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已申请金额 end '已申请金额',
		--x.预算金额-x.导入执行数 '申请剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.申请剩余 end '申请剩余',
		--x.导入执行数 '已支付金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已支付金额 end '已支付金额',
		--x.预算金额-x.导入执行数 '支付剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.支付剩余 end '支付剩余',
		case when x.导入执行数>0 then (case when x.年初预算=0 or x.预算金额=0 then '0.00'+'%' else 
		format( x.导入执行数/(isnull(x.年初预算,x.预算金额)) *100,'N2')+'%' end) else (case when x.预算金额=0 then '0.00'+'%' else 
        format( x.已申请金额/x.预算金额 *100,'N2')+'%' end)	
end as 执行率,
		case when x.导入执行数>0 then '导入数据' else '内控数据' end '数据来源'
from 
(select  
b.budgetcode 预算编号,
org.NAME 归口部门,
b.fl 分类,
P.FCOSTSITEMC 一级编码,
p.FCOSTSITEMN 一级,
b.ysnx 预算类型,
s.FCOSTSITEMLC 支出明细编码,
s.FCOSTSITEMLN 支出明细,
f.code 项目名称编码,
f.name 项目名称,
t.name 预算类别,
g.valuename 预算来源,
l.valuename 预算年度,
b.InitialAmount 年初预算,
isnull(z.budgetamount,b.BUDGETAMOUNT) 预算金额,
isnull(z.ajustamount,b.AJUSTAMOUNT) 调整金额,
isnull(z.frozenamount,b.FROZENAMOUNT) 已申请金额,
isnull(z.frozenremain,b.FROZENREMAIN) 申请剩余,
isnull(z.isusedamount,b.ISUSEDAMOUNT) 已支付金额,
isnull(z.isusedremain,b.ISUSEDREMAIN) 支付剩余
--,case when z.BUDGETAMOUNT=0 or b.BUDGETAMOUNT=0 then '0' else 
-- format( (isnull(z.FROZENAMOUNT,b.FROZENAMOUNT))/(isnull(z.BUDGETAMOUNT,b.BUDGETAMOUNT)) *100,'N2')+'%'
--end as 执行率
,SUM(zxqk.BUDGETAMOUNTZX) 导入执行数
from rate_budgetaccount b 
left join RATE_BUDGETRESOLVEL z on b.id=z.sheetpointer_xid
left join D_FINANCECOSTSITEM p on p.id= b.FCOSTSITEM_XID
left join D_FINANCECOSTSITEML s on s.id=b.FCOSTSITEML_XID
left join D_FINANCEACCOUNT f on f.id=b.FINANCEACCOUNT_XID 
left join D_PARAMETER_LINE l on l.id= b.ANNUAL_XID 
left join d_organization o on o.id=z.orgcode_xid 
left join Rate_BudgetType t on t.id=b.BudgeType_xid
left join d_organization o1 on o1.id=b.comcode_xid
left join D_FunctionAccount d on b.FunctionAccount_xid=d.id
left join D_EconomicsAccount e on b.economicsaccount_xid=e.id
left join D_Parameter_Line  g on b.budgetsource_xid=g.id
left join d_organization org on org.id=b.UPORGNAME_XID
left join AD_BudgetImportZC zxqk on zxqk.budgetCode=b.budgetCode and zxqk.budgetCode=b.budgetCode
where f.NAME not like '%测试预算%' and  f.NAME not like '%资金池%' and b.FL!='收入' and  l.valuename='2024'  and b.BUDGETAMOUNT!=0 
and  b.UPORGNAME_XID in (


(select d.ID from   AD_WorkFlowRole  a
left join  d_user  b on  a.USERCODE_XID=b.id
left  join AD_WorkFlowRoleDepart c on  a.id=c.SHEETPOINTER_XID
left  join  D_ORGANIZATION d  on c.ORGNAME_XID=d.id
where convert(varchar(50),b.id) ='null')
union all
(select c.id  from  GKContrast a
left  join  D_USER b on  a.USERCODE_XID=b.id
left  join  D_ORGANIZATION  c  on a.ORGCODE_XID=c.ID
where convert(varchar(50),b.id) ='100001154')



)
group by b.BUDGETCODE,org.NAME,b.fl,P.FCOSTSITEMC,s.FCOSTSITEMLC,f.code,
p.FCOSTSITEMN,
b.ysnx,
s.FCOSTSITEMLN,
f.name,
t.name,
g.valuename,
l.valuename,
b.InitialAmount,
isnull(z.budgetamount,b.BUDGETAMOUNT),
isnull(z.ajustamount,b.AJUSTAMOUNT),
isnull(z.frozenamount,b.FROZENAMOUNT),
isnull(z.frozenremain,b.FROZENREMAIN),
isnull(z.isusedamount,b.ISUSEDAMOUNT),
isnull(z.isusedremain,b.ISUSEDREMAIN)
) as x

参数名: Invalid value for Cause
实际值是    支出预算执行率查询报表  给归口预算管理员也加一个权限  修改了原有的sql语句
select  x.预算编号,
		x.归口部门,
		x.分类,
		x.一级编码,
		x.一级,
		x.预算类型,
		x.支出明细编码,
		x.支出明细,
		x.项目名称编码,
		x.项目名称,
		x.预算类别,
		x.预算来源,
		x.预算年度,
		x.年初预算,
		x.预算金额,
		x.调整金额,
		--x.导入执行数 '已申请金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已申请金额 end '已申请金额',
		--x.预算金额-x.导入执行数 '申请剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.申请剩余 end '申请剩余',
		--x.导入执行数 '已支付金额',
		case when x.导入执行数>0 then x.导入执行数 else X.已支付金额 end '已支付金额',
		--x.预算金额-x.导入执行数 '支付剩余',
		case when x.导入执行数>0 then x.预算金额-x.导入执行数 else X.支付剩余 end '支付剩余',
		case when x.导入执行数>0 then (case when x.年初预算=0 or x.预算金额=0 then '0.00'+'%' else 
		format( x.导入执行数/(isnull(x.年初预算,x.预算金额)) *100,'N2')+'%' end) else (case when x.预算金额=0 then '0.00'+'%' else 
        format( x.已申请金额/x.预算金额 *100,'N2')+'%' end)	
end as 执行率,
		case when x.导入执行数>0 then '导入数据' else '内控数据' end '数据来源'
from 
(select  
b.budgetcode 预算编号,
org.NAME 归口部门,
b.fl 分类,
P.FCOSTSITEMC 一级编码,
p.FCOSTSITEMN 一级,
b.ysnx 预算类型,
s.FCOSTSITEMLC 支出明细编码,
s.FCOSTSITEMLN 支出明细,
f.code 项目名称编码,
f.name 项目名称,
t.name 预算类别,
g.valuename 预算来源,
l.valuename 预算年度,
b.InitialAmount 年初预算,
isnull(z.budgetamount,b.BUDGETAMOUNT) 预算金额,
isnull(z.ajustamount,b.AJUSTAMOUNT) 调整金额,
isnull(z.frozenamount,b.FROZENAMOUNT) 已申请金额,
isnull(z.frozenremain,b.FROZENREMAIN) 申请剩余,
isnull(z.isusedamount,b.ISUSEDAMOUNT) 已支付金额,
isnull(z.isusedremain,b.ISUSEDREMAIN) 支付剩余
--,case when z.BUDGETAMOUNT=0 or b.BUDGETAMOUNT=0 then '0' else 
-- format( (isnull(z.FROZENAMOUNT,b.FROZENAMOUNT))/(isnull(z.BUDGETAMOUNT,b.BUDGETAMOUNT)) *100,'N2')+'%'
--end as 执行率
,SUM(zxqk.BUDGETAMOUNTZX) 导入执行数
from rate_budgetaccount b 
left join RATE_BUDGETRESOLVEL z on b.id=z.sheetpointer_xid
left join D_FINANCECOSTSITEM p on p.id= b.FCOSTSITEM_XID
left join D_FINANCECOSTSITEML s on s.id=b.FCOSTSITEML_XID
left join D_FINANCEACCOUNT f on f.id=b.FINANCEACCOUNT_XID 
left join D_PARAMETER_LINE l on l.id= b.ANNUAL_XID 
left join d_organization o on o.id=z.orgcode_xid 
left join Rate_BudgetType t on t.id=b.BudgeType_xid
left join d_organization o1 on o1.id=b.comcode_xid
left join D_FunctionAccount d on b.FunctionAccount_xid=d.id
left join D_EconomicsAccount e on b.economicsaccount_xid=e.id
left join D_Parameter_Line  g on b.budgetsource_xid=g.id
left join d_organization org on org.id=b.UPORGNAME_XID
left join AD_BudgetImportZC zxqk on zxqk.budgetCode=b.budgetCode and zxqk.budgetCode=b.budgetCode
where f.NAME not like '%测试预算%' and  f.NAME not like '%资金池%' and b.FL!='收入' and  l.valuename='2024'  and b.BUDGETAMOUNT!=0 
and  b.UPORGNAME_XID in (


(select d.ID from   AD_WorkFlowRole  a
left join  d_user  b on  a.USERCODE_XID=b.id
left  join AD_WorkFlowRoleDepart c on  a.id=c.SHEETPOINTER_XID
left  join  D_ORGANIZATION d  on c.ORGNAME_XID=d.id
where convert(varchar(50),b.id) ='null')
union all
(select c.id  from  GKContrast a
left  join  D_USER b on  a.USERCODE_XID=b.id
left  join  D_ORGANIZATION  c  on a.ORGCODE_XID=c.ID
where convert(varchar(50),b.id) ='100001154')



)
group by b.BUDGETCODE,org.NAME,b.fl,P.FCOSTSITEMC,s.FCOSTSITEMLC,f.code,
p.FCOSTSITEMN,
b.ysnx,
s.FCOSTSITEMLN,
f.name,
t.name,
g.valuename,
l.valuename,
b.InitialAmount,
isnull(z.budgetamount,b.BUDGETAMOUNT),
isnull(z.ajustamount,b.AJUSTAMOUNT),
isnull(z.frozenamount,b.FROZENAMOUNT),
isnull(z.frozenremain,b.FROZENREMAIN),
isnull(z.isusedamount,b.ISUSEDAMOUNT),
isnull(z.isusedremain,b.ISUSEDREMAIN)
) as x
。