💢账套建帐信息
select cAcc_Id 账套编号 , cAcc_Name 账套名称 ,iYear 启用年度 ,iMonth 启用月份 ,cEntType 账套类型 ,cTradeKind 会计行业制度
from UA_Account
--账套信息
💢查询科目表【科目、部门、人员、供应商、客户】
select cclass as 科目类型 , ccode 科目编码 ,ccode_name 科目名称 ,
CASE
WHEN bproperty = 0 THEN '贷方'
WHEN bproperty = 1 THEN '借方'
END AS 科目方向,
CASE
WHEN bcus = 0 THEN ''
WHEN bcus = 1 THEN '客户'
END AS 客户辅助核算,
CASE
WHEN bsup = 0 THEN ''
WHEN bsup = 1 THEN '供应商'
END AS 供应商辅助核算,
CASE
WHEN bperson = 0 THEN ''
WHEN bperson = 1 THEN '个人'
END AS 个人辅助核算
,
CASE
WHEN bdept = 0 THEN ''
WHEN bdept = 1 THEN '部门'
END AS 部门辅助核算
,
CASE
WHEN bitem = 0 THEN ''
WHEN bitem = 1 THEN '项目'
END AS 项目辅助核算
from code
group by cclass , ccode ,ccode_name ,bproperty,bsup,bcus,bperson,bdept,bitem
order by ccode
--科目表查询
💢查询所有的期间的科目余额表
SELECT distinct a.iyear as 年度, a.iYPeriod as 会计期间 ,a.iperiod as 期间, a.ccode as 科目编码 ,b.ccode_name 科目名称,b.cclass as 科目类型
,a.cbegind_c as 期初借贷方向,
a.mb as 期初余额 ,a.md as 借方累计,a.mc as 贷方累计,
a.cendd_c as 期末借贷方向 ,a.me as 期末余额
FROM GL_ACCSUM a inner join code B on a.ccode=b.ccode
order by a.iYPeriod , a.ccode
-- 查询科目余额表(所有会计期间的)
💢查询某个会计期间的科目余额表
SELECT a.iyear as 年度, a.iYPeriod as 会计期间 ,a.iperiod as 期间, a.ccode as 科目编码 ,b.ccode_name 科目名称,b.cclass as 科目类型
,a.cbegind_c as 期初借贷方向,
a.mb as 期初余额 ,a.md as 借方累计,a.mc as 贷方累计,
a.cendd_c as 期末借贷方向 ,a.me as 期末余额
FROM GL_ACCSUM a inner join code B on a.ccode=b.ccode
WHERE a.iYPeriod=202301 and b.iyear=2023 --查询会计期间和年度
order by a.ccode
-- 查询科目余额表
💢查询所有的凭证
--查询所有凭证信息
SELECT A.iyear 年度 ,IYPERIOD 期间, CONVERT(varchar(12), dbill_date, 102 ) 填制日期
,CONCAT(ino_id ,'号')AS 凭证号 ,CDIGEST AS 摘要
,cbill 制单人 ,A.ccode 科目编码 ,b.ccode_name 科目名称 ,
isnull(SUM(MD),'') 借方金额 ,isnull(SUM(MC),'') 贷方金额
,csup_id 供应商编码,citem_id 项目编码 ,citem_class 项目大类编码 ,cdept_id 部门编码 ,ccus_id 客户编码 ,cperson_id 员工编码
FROM GL_accvouch A INNER JOIN CODE B ON A.ccode=B.CCODE
where iperiod <>0
group by A.iyear, ino_id,IYPERIOD, CONVERT(varchar(12), dbill_date, 102 ) ,CDIGEST
,cbill ,A.ccode ,b.ccode_name ,csup_id ,citem_id ,citem_class ,cdept_id ,ccus_id ,cperson_id
order by IYPERIOD ,ino_id
💢客户、供应商辅助核算科目余额表
SELECT distinct a.iyear as 年度, a.iYPeriod as 会计期间 ,a.iperiod as 期间, a.ccode as 科目编码 ,
b.ccode_name 科目名称,b.cclass as 科目类型,a.ccus_id as 客户编码 ,a.csup_id as 供应商编码,a.cdept_id 部门编码,a.cperson_id 个人编码 ,a.citem_class 项目大类,a.citem_id 项目编码
,a.cbegind_c as 期初借贷方向,
a.mb as 期初余额 ,a.md as 借方累计,a.mc as 贷方累计,
a.cendd_c as 期末借贷方向 ,a.me as 期末余额
FROM GL_accass a inner join code B on a.ccode=b.ccode
order by a.iYPeriod , a.ccode
-- 查询科目余额表(所有会计期间的)
