用友核算项目余额表
--************************************************************************
--因为用友U8v10.0以后版本的会计科目,是按年度区分的,这里需要结合年度判断科目信息
--优化后
select * from code
drop view df_v_code_1
create view df_v_code_1 as
(
select T1.* , T2.科目名称 AS [1级科目名称] , ( case when T3.科目名称 is null then T2.科目名称 else T3.科目名称 end ) as [2级科目名称] from
( select ( CONVERT(varchar(8),code.iyear) + '-' + 'code' ) as 年度科目档案 , ( CONVERT(varchar(8),code.iyear) + '-' + CONVERT(varchar(20),code.cCode) ) as 年度科目编码
, code.iyear as 科目年度 , code.cCode as 科目编码 , code.ccode_name as 科目名称 , code.chelp As 科目助记码 , code.cclass As 科目类型 , code.cmeasure as 计量单位
, ( case when code.bproperty = 1 then '借' when code.bproperty = 0 then '贷' end ) As 科目方向
, code.igrade as 科目级次 , ( case when code.bend = 1 then '是' when code.bend = 0 then '否' end ) As 是否末级
, ( case when code.bclose = 1 then '是' when code.bclose = 0 then null end ) As 是否封存
, ( left(code.cCode, substring(GradeDef.CODINGRULE , 1, 1) ) ) as [1级科目编码]
, ( left(code.cCode, convert(int, substring(GradeDef.CODINGRULE , 1, 1)) + convert(int, substring(GradeDef.CODINGRULE,2,1)) ) ) as [2级科目编码]
---获取年度1级科目编码和年度2级科目编码
, ( CONVERT(varchar(8),code.iyear) + '-' + left(code.cCode, substring(GradeDef.CODINGRULE , 1, 1) ) ) as 年度科目1级编码
, ( CONVERT(varchar(8),code.iyear) + '-' + left(code.cCode, convert(int, substring(GradeDef.CODINGRULE , 1, 1)) + convert(int, substring(GradeDef.CODINGRULE,2,1)) ) ) as 年度科目2级编码
---核算项目信息
, ( case when code.bperson = 1 then '是' else null end ) as 是否个人核算 , ( case when code.bcus = 1 then '是' else null end ) as 是否客户核算 , ( case when code.bsup = 1 then '是' else null end ) as 是否供应商核算 , (case when code.bdept = 1 then '是' else null end ) as 是否部门核算
, ( case when code.bitem = 1 then '是' else null end ) as 是否项目核算 , code.cass_item as 项目大类编码
From code left outer join GradeDef on ( CONVERT(varchar(8),code.iyear) + '-' + 'code' ) = ( CONVERT(varchar(8),GradeDef.iyear) + '-' + GradeDef.KEYWORD )
) as T1
left outer join ( select ( CONVERT(varchar(8),code.iyear) + '-' + CONVERT(varchar(20),code.cCode) ) as 年度科目编码 , code.cCode as 科目编码, code.ccode_name as 科目名称 From code where code.igrade = 1 ) as T2 ON T1.年度科目1级编码 = T2.年度科目编码
left outer join ( select ( CONVERT(varchar(8),code.iyear) + '-' + CONVERT(varchar(20),code.cCode) ) as 年度科目编码 , code.cCode as 科目编码, code.ccode_name as 科目名称 From code where code.igrade = 2 ) as T3 ON T1.年度科目2级编码 = T3.年度科目编码
)
select * from df_v_code_1
--************************************************************************
--合并自定义的项目档案表
select * from df_v_fitemss99
union all select * from df_v_fitemss98
union all select * from df_v_fitemss97
union all select * from df_v_fitemss00
union all select * from df_v_fitemss01
union all select * from df_v_fitemss02
union all select * from df_v_fitemssCH
select * from df_v_fitemssZH
--************************************************************************
--因为用友U8v10.0以后版本
--核算项目余额表
select * from gl_accass
------------------------
select
gl_accass.ccode As 科目编号 , ( case when gl_accass.cexch_name is null then '人民币' else gl_accass.cexch_name end ) as 币别名称 , gl_accass.cexch_name As 币别名称 , gl_accass.cdept_id As 部门编号 , gl_accass.cperson_id As 职员编号 , gl_accass.ccus_id As 客户编号 , gl_accass.csup_id As 供应商编号 , gl_accass.citem_class As 项目类别 , gl_accass.citem_id As 项目编号
, gl_accass.iYPeriod As 会计年月 , gl_accass.iperiod As 会计期间 , gl_accass.cbegind_c As 金额期初方向 , gl_accass.mb As 金额期初 , gl_accass.md As 借方金额 , gl_accass.mc As 贷方金额 , gl_accass.cendd_c As 金额期末方向 , gl_accass.me As 金额期末 , gl_accass.mb_f As 外币期初 , gl_accass.md_f As 外币借方金额 , gl_accass.mc_f As 外币贷方金额 , gl_accass.me_f As 外币期末 , gl_accass.nb_s As 数量期初 , gl_accass.nd_s As 数量借方 , gl_accass.nc_s As 数量贷方 , gl_accass.ne_s As 数量期末 , gl_accass.iyear As 会计年度
from gl_accass
------------------------
--不带自定义的核算项目
drop view df_v_gl_accass_1
create view df_v_gl_accass_1 as
(
select gl_accass.ccode As 科目编号 , ( case when gl_accass.cexch_name is null then '人民币' else gl_accass.cexch_name end ) as 币别名称
, gl_accass.iYPeriod As 会计年月 , gl_accass.iperiod As 会计期间 , gl_accass.cbegind_c As 金额期初方向 , gl_accass.mb As 金额期初 , gl_accass.md As 借方金额 , gl_accass.mc As 贷方金额 , gl_accass.cendd_c As 金额期末方向 , gl_accass.me As 金额期末 , gl_accass.mb_f As 外币期初 , gl_accass.md_f As 外币借方金额 , gl_accass.mc_f As 外币贷方金额 , gl_accass.me_f As 外币期末 , gl_accass.nb_s As 数量期初 , gl_accass.nd_s As 数量借方 , gl_accass.nc_s As 数量贷方 , gl_accass.ne_s As 数量期末 , gl_accass.iyear As 会计年度
,( case when df_v_code_1.科目类型 = '损益' and df_v_code_1.科目方向 = '贷' then gl_accass.mc when df_v_code_1.科目类型 = '损益' and df_v_code_1.科目方向 = '借' then (-gl_accass.md) else 0 end ) as 损益_合并发生
,( case when df_v_code_1.科目类型 <> '损益' and gl_accass.cendd_c = '借' then gl_accass.me when df_v_code_1.科目类型 <> '损益' and gl_accass.cendd_c = '贷' then (-gl_accass.me) else 0 end ) as 非损益_合并余额
, df_v_code_1.*
, person.cPersonCode as 职员编码 , person.cPersonName as 职员名称
, df_v_department_1.*
, Customer.cCusCode as 客户编码 , Customer.cCusName as 客户名称 , Customer.cCusAbbName as 客户简称 , df_v_CustomerClass_1.*
, Vendor.cVenCode as 供应商编码 , Vendor.cVenName as 供应商名称 , Vendor.cVenAbbName as 供应商简称 , df_v_vendorclass_1.*
from gl_accass
left outer join df_v_code_1 on ( CONVERT(varchar(8),gl_accass.iyear) + '-' + CONVERT(varchar(20),gl_accass.ccode) ) = df_v_code_1.年度科目编码
left outer join person on gl_accass.cperson_id = person.cPersoncode
left outer join df_v_department_1 on gl_accass.cdept_id = df_v_department_1.部门编码
left outer join Customer on gl_accass.ccus_id = Customer.cCusCode
left outer join df_v_CustomerClass_1 on Customer.cCCCode = df_v_CustomerClass_1.客户分类编码
left outer join Vendor on gl_accass.csup_id = Vendor.cVenCode
left outer join df_v_vendorclass_1 on Vendor.cVCCode = df_v_vendorclass_1.供应商分类编码
)
------------------------
--加上自定义的核算项目
create view df_v_gl_accass_1 as
(
select gl_accass.ccode As 科目编号 , ( case when gl_accass.cexch_name is null then '人民币' else gl_accass.cexch_name end ) as 币别名称
, gl_accass.iYPeriod As 会计年月 , gl_accass.iperiod As 会计期间 , gl_accass.cbegind_c As 金额期初方向 , gl_accass.mb As 金额期初 , gl_accass.md As 借方金额 , gl_accass.mc As 贷方金额 , gl_accass.cendd_c As 金额期末方向 , gl_accass.me As 金额期末 , gl_accass.mb_f As 外币期初 , gl_accass.md_f As 外币借方金额 , gl_accass.mc_f As 外币贷方金额 , gl_accass.me_f As 外币期末 , gl_accass.nb_s As 数量期初 , gl_accass.nd_s As 数量借方 , gl_accass.nc_s As 数量贷方 , gl_accass.ne_s As 数量期末 , gl_accass.iyear As 会计年度
,( case when df_v_code_1.科目类型 = '损益' and df_v_code_1.科目方向 = '贷' then gl_accass.mc when df_v_code_1.科目类型 = '损益' and df_v_code_1.科目方向 = '借' then (-gl_accass.md) else 0 end ) as 损益_合并发生
,( case when df_v_code_1.科目类型 <> '损益' and gl_accass.cendd_c = '借' then gl_accass.me when df_v_code_1.科目类型 <> '损益' and gl_accass.cendd_c = '贷' then (-gl_accass.me) else 0 end ) as 非损益_合并余额
, df_v_code_1.*
, person.cPersonCode as 职员编码 , person.cPersonName as 职员名称
, df_v_department_1.*
, Customer.cCusCode as 客户编码 , Customer.cCusName as 客户名称 , Customer.cCusAbbName as 客户简称 , df_v_CustomerClass_1.*
, Vendor.cVenCode as 供应商编码 , Vendor.cVenName as 供应商名称 , Vendor.cVenAbbName as 供应商简称 , df_v_vendorclass_1.*
, df_v_fitemssZH.*
from gl_accass
left outer join df_v_code_1 on ( CONVERT(varchar(8),gl_accass.iyear) + '-' + CONVERT(varchar(20),gl_accass.ccode) ) = df_v_code_1.年度科目编码
left outer join person on gl_accass.cperson_id = person.cPersoncode
left outer join df_v_department_1 on gl_accass.cdept_id = df_v_department_1.部门编码
left outer join Customer on gl_accass.ccus_id = Customer.cCusCode
left outer join df_v_CustomerClass_1 on Customer.cCCCode = df_v_CustomerClass_1.客户分类编码
left outer join Vendor on gl_accass.csup_id = Vendor.cVenCode
left outer join df_v_vendorclass_1 on Vendor.cVCCode = df_v_vendorclass_1.供应商分类编码
left outer join df_v_fitemssZH on ( CONVERT(varchar(8), gl_accass.citem_class) + '-' + CONVERT(varchar(50), gl_accass.citem_id) ) = ( CONVERT(varchar(8), df_v_fitemssZH.项目大类编号) + '-' + CONVERT(varchar(50), df_v_fitemssZH.项目编号) )
)
select * from df_v_gl_accass_1
select * from df_v_code_1
--*****************************************************
--判断会计科目挂了哪些核算项目
select * from t_Account --会计科目档案
select * from t_ItemClass --项目分类档案
select * from t_ItemDetailV --核算项目纵表
select * from t_ItemDetail --核算项目横表
---------------------------------------------------------
---判断会计科目挂了哪些核算项目----方法1
--核算项目使用详情-纵表:查询会计科目所挂的项目信息-01(T_ItemPropDesc
drop view df_v_Account_item_01
create view df_v_Account_item_01 as
(
SELECT t_Account.FAccountID as 科目内码,t_Account.FNumber as 科目代码,t_Account.FName as 科目名称
, t_ItemClass.FItemClassID as 项目大类内码
, t_ItemClass.FNumber as 项目大类编码 , t_ItemClass.FName as 项目大类名称
,( case when t_ItemClass.FItemClassID < 3001 then t_ItemClass.FSQLTableName else 'df_v_item_' + CONVERT(VARCHAR(4), t_ItemClass.FItemClassID) end ) as 项目sql表名
, CASE t_ItemDetailV.FItemID WHEN -1 THEN 1 ELSE 0 END FBalChecked
FROM t_Account inner join t_ItemDetailV on t_Account.FDetailID = t_ItemDetailV.FDetailID
inner join t_ItemClass on t_ItemDetailV.FItemClassID = t_ItemClass.FItemClassID
where t_ItemDetailV.FItemID IN(-1,-2)
)
select * from df_v_Account_item_01
--核算项目使用详情-纵表:查询会计科目所挂的项目信息-01(T_ItemPropDesc
drop view df_v_Account_item_02
create view df_v_Account_item_02 as
(
SELECT *
,( ' , ' + 项目sql表名 + '.' + 'FNumber' + ' as ' + 项目大类名称 + '代码' + ' , ' + 项目sql表名 + '.' + 'FName' + ' as ' + 项目大类名称 + '名称' ) as 显示字段
,( ' left outer join (select * from ' + 项目sql表名 + ' where FItemID <> 0 ) as ' + 项目sql表名 + ' on T_ItemDetail.F' + CONVERT(VARCHAR(4), 项目大类内码) + ' = ' + 项目sql表名 + '.FItemID' ) as 连接语句
FROM df_v_Account_item_01
)
select * from df_v_Account_item_02
--判断哪些核算项目被会计科目调用了
drop view df_v_Account_item_11
create view df_v_Account_item_11 as ( select distinct 项目大类内码 , 项目大类编码 , 项目大类名称 , 项目sql表名 , 显示字段 , 连接语句 from df_v_Account_item_02 )
select * from df_v_Account_item_11 order by 项目大类内码
--判断哪些会计科目被项目调用了
drop view df_v_Account_item_12
create view df_v_Account_item_12 as ( select distinct 科目内码 , 科目代码 , 科目名称 from df_v_Account_item_02 )
select * from df_v_Account_item_12
---------------------------------------------------------
--判断会计科目挂了哪些核算项目----方法2-1
--1>.先对会计科目和核算项目横表做连接,查询出所有挂了,所有(含金蝶自带的项目信息)核算项目的科目
select t_Account.FAccountID As 科目内码 , t_Account.FNumber As 科目代码 , t_Account.FName As 科目名称 , t_Account.FFullName As 科目全名 , t_Account.FLevel As 科目级次 , t_Account.FDetail As 明细科目_1是_0否 , t_Account.FParentID As 顶级_即1级科目内码 , t_Account.FRootID As 上级科目内码 , t_Account.FGroupID As 科目类别内码 , t_Account.FDC As 借贷方向_1借_负1贷 , t_Account.FHelperCode As 助记码 , t_Account.FCurrencyID As 币别内码_0核算所有_1不核算 , t_Account.FDetailID As 核算项目内码 , t_Account.FDelete As 是否禁用
, T_ItemDetail.*
from t_Account left outer join T_ItemDetail on t_Account.FDetailID = T_ItemDetail.FDetailID
where t_Account.FDetailID <> 0
---------------------------------------------------------
--判断会计科目挂了哪些核算项目----方法2-2
--核算项目使用详情-横表:查询会计科目所挂的项目信息-02(T_ItemDetail
SELECT t_Account.FAccountID as 科目内码,t_Account.FNumber as 科目代码,t_Account.FName as 科目名称,
T_ItemDetail.F1 as 客户核算,T_ItemDetail.F2 as 部门核算,T_ItemDetail.F3 as 职员核算, T_ItemDetail.F4 as 商品核算, T_ItemDetail.F5 as 仓库核算, T_ItemDetail.F8 as 供应商核算, T_ItemDetail.F9 as 现金流量核算, T_ItemDetail.F10 as 分支机构核算, T_ItemDetail.F14 as 工作中心核算,
T_ItemDetail.F3001 as 自定义01核算,T_ItemDetail.F3002 as 自定义02核算,T_ItemDetail.F3003 as 自定义03核算, T_ItemDetail.F3004 as 自定义04核算
FROM t_Account inner join T_ItemDetail on t_Account.FDetailID = T_ItemDetail.FDetailID
where T_ItemDetail.F1 = '-1' or T_ItemDetail.F2 = '-1' or T_ItemDetail.F3 = '-1' or T_ItemDetail.F4 = '-1' or T_ItemDetail.F5 = '-1' or T_ItemDetail.F8 = '-1' or T_ItemDetail.F9 = '-1' or T_ItemDetail.F10 = '-1' or T_ItemDetail.F14 = '-1'
or T_ItemDetail.F3001 = '-1' or T_ItemDetail.F3002 = '-1' or T_ItemDetail.F3003 = '-1' or T_ItemDetail.F3004 = '-1'
order by t_Account.FNumber
---------------------------------------------------------
--判断哪些核算项目,给会计科目调用----方法2-3
--2>.通过对自定义的项目列做sum汇总,判断哪些自定义的核算项目(3打头的字段)被会计科目调用了
select sum(T_ItemDetail.F3001) AS F3001
, sum(T_ItemDetail.F3002) AS F3002
, sum(T_ItemDetail.F3003) AS F3003
, sum(T_ItemDetail.F3004) AS F3004
, sum(T_ItemDetail.F3005) AS F3005
, sum(T_ItemDetail.F3006) AS F3006
, sum(T_ItemDetail.F3007) AS F3007
, sum(T_ItemDetail.F3008) AS F3008
, sum(T_ItemDetail.F3009) AS F3009
--, sum(T_ItemDetail.F3010) AS F3010
--, sum(T_ItemDetail.F3011) AS F3011
--, sum(T_ItemDetail.F3012) AS F3012
--, sum(T_ItemDetail.F3013) AS F3013
--, sum(T_ItemDetail.F3014) AS F3014
--, sum(T_ItemDetail.F3015) AS F3015
from t_Account left outer join T_ItemDetail on t_Account.FDetailID = T_ItemDetail.FDetailID
where t_Account.FDetailID <> 0 --核算项目情况(不等于0说明挂了项目)
--*****************************************************
select * from t_ItemClass order by FItemClassID --项目分类档案
select t_Organization.FNumber as 客户代码 , t_Organization.FName as 客户名称 , * from t_Organization --客户--------1
select t_Department.FNumber as 部门代码 , t_Department.FName as 部门名称 , * from t_Department --部门--------2
select t_Emp.FNumber as 职员代码 , t_Emp.FName as 职员名称 , * from t_Emp --职员--------3
select t_ICItem.FNumber as 产品代码 , t_ICItem.FName as 产品名称 , t_ICItem.FModel as 规格型号 ,* from t_ICItem --物料--------4
select t_Stock.FNumber as 仓库代码 , t_Stock.FName as 仓库名称 , * from t_Stock --仓库--------5
select t_Supplier.FNumber as 供应商代码 , t_Supplier.FName as 供应商名称 , * from t_Supplier --供应商------8
select t_SonCompany.FNumber as 分支机构代码 , t_SonCompany.FName as 分支机构名称 , * from t_SonCompany --分支机构---10
select t_WorkCenter.FNumber as 工作中心代码 , t_WorkCenter.FName as 工作中心名称 , * from t_WorkCenter --工作中心---14
select cbCostObj.FNumber as 成本对象代码 , cbCostObj.FName as 成本对象名称 , * from cbCostObj --成本对象---2001
select cbService.FNumber as 劳务代码 , cbService.FName as 劳务名称 , * from cbService --劳务-------2002
select cbCostItem.FNumber as 成本项目代码 , cbCostItem.FName as 成本项目名称 , * from cbCostItem --成本项目---2003
select cbExpense.FNumber as 要素费用代码 , cbExpense.FName as 要素费用名称 , * from cbExpense --要素费用---2004
select t_Fee.FNumber as 费用代码 , t_Fee.FName as 费用名称 , * from t_Fee --费用-------2014
select t_scPlanItem.FNumber as 计划项目代码 , t_scPlanItem.FName as 计划项目名称 , * from t_scPlanItem --计划项目---2021
select cbCostObjGroup.FNumber as 成本对象组代码 , cbCostObjGroup.FName as 成本对象组名称 , * from cbCostObjGroup --成本对象组-2023
select t_BK_Acnt.FNumber as 银行账号代码 , t_BK_Acnt.FName as 银行账号名称 , * from t_BK_Acnt --银行账号---2024
select t_BASE_Area.FNumber as 国别地区代码 , t_BASE_Area.FName as 国别地区名称 , * from t_BASE_Area --国别地区---2026
select t_BASE_CityPort.FNumber as 城市港口代码, t_BASE_CityPort.FName as 城市港口名称 , * from t_BASE_CityPort --城市港口---2027
select t_BASE_HSCode.FNumber as HS编码代码 , t_BASE_HSCode.FName as HS编码名称 , * from t_BASE_HSCode --HS编码-----2028
select T_BASE_InsuranceType.FNumber as 保险险种代码 , T_BASE_InsuranceType.FName as 保险险种名称 , * from T_BASE_InsuranceType --保险险种---2029
select t_BASE_CostCenter.FNumber as 成本中心代码 , t_BASE_CostCenter.FName as 成本中心名称 , * from t_BASE_CostCenter --成本中心---2030
select t_Base_BudgetItem.FNumber as 要素项目代码 , t_Base_BudgetItem.FName as 要素项目名称 , * from t_Base_BudgetItem --要素项目---2035
select t_Base_FinInstitution.FNumber as 金融机构代码 , t_Base_FinInstitution.FName as 金融机构名称 , * from t_Base_FinInstitution --金融机构---2036
select t_Base_ProjectItem.FNumber as 工程项目代码 , t_Base_ProjectItem.FName as 工程项目名称 , * from t_Base_ProjectItem --工程项目---2039
select t_Base_RespCenter.FNumber as 工程项目代码 , t_Base_RespCenter.FName as 工程项目名称 , * from t_Base_RespCenter --责任中心---2040
select t_Base_SpecialProject.FNumber as 专项代码 , t_Base_SpecialProject.FName as 专项名称 , * from t_Base_SpecialProject --专项---2041
|
|
发表于 2018-12-5 10:19:48
中发现Ni：
中发现Ni：
中发现Ni：
中发现Ni：
中发现Ni：
中发现Ni：
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
