财务表
资金-现金，银行存款，应收票据发生额
with uccashflow as (
SELECT
a.AccountedDr,
a.AccountedCr,
e.segment1 as code ,
left(e.segment1 ,4) as tcode,
f.displayname,
left(f.displayname ,4) as tcodename,
--a.OpposingAccountNames,
b.org,
b.BusinessDate,
c.name,
d.Abstracts
FROM gl_entry a
LEFT JOIN GL_Voucher b ON a.Voucher = b.id
left join Base_Organization_Trl c on b.org=c.id
left join GL_Entry_Trl d on a.id=d.id
left join CBO_Account e on a.Account=e.id
left join CBO_Account_Trl f on e.id=f.id
WHERE 1=1
and b.VoucherSourceMethod!=9 and b.VoucherStatus!=0 --排除期初和草稿凭证
and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
and FORMAT(b.BusinessDate, 'yyyy-MM-dd') <= '2024-11-30'
AND a.iserror=0
and LEFT(e.Segment1,4) IN('1001','1002','1121')
)
select sum(accountedcr) as 贷,SUM(accounteddr) as 借, sum(accounteddr)-SUM(accountedcr) as 余额,code from uccashflow group by code
资金—现金，银行存款，应收票据期初额
select LEFT(b.code, CHARINDEX('|', b.code) - 1) as code,
LEFT(f.Displayname, CHARINDEX('|', f.Displayname) - 1) AS codename,
g.displayname as year_month,
g.year,g.code as month,
h.name as company,
sum(
case when e.Direction=0
then (a.BalanceValue_BeginBalanceDr-a.BalanceValue_BeginBalanceCr)
else (a.BalanceValue_BeginBalanceCr-a.BalanceValue_BeginBalanceDr) end
)
from GL_GeneralLedger a
inner join Base_SetofBooks a1 on a1.ID=a.AccessKey_SOB
inner join CBO_Account b on b.id=a.AccessKey_Account
--inner join MANA_AssetLiabilitys d on d.SubjectAttribute=b.AccountProperty
inner join CBO_AccountProperty e on e.id=b.AccountProperty
inner join CBO_Account_trl f on b.id=f.id
inner join Base_SOBAccountingPeriod g on a.AccessKey_PostPeriod= g.id
inner join Base_Organization_trl h on b.org= h.id
where a1.Code=102 and a.AccessKey_Currency=1
and g.displayname in ('2024-07','2024-08')
and a.AccessKey_IsSumLowLevel=0
--AND B.CODE='1001|0|0|0|0|0|0|0|0|0|0|0|0'
--and d.SubjectAttribute=@AccountAttribute and d.AssetLiability=@AssetReportItem
group by LEFT(b.code, CHARINDEX('|', b.code) - 1) ,
LEFT(f.Displayname, CHARINDEX('|', f.displayname) - 1) ,
g.displayname ,
g.year,g.code ,
h.name
拆卸订单材料成本
-- 找到拆卸订单，拆卸订单对应物物料，对应生产成本表单价
-- MO-2407200008
select a.ID,c.mo ,b.name,c.AdjustWIPCost_CURRENTcost + c.AdjustWIPCost_PRIORcost as 在制成本,d.displayname as 会计年月 from MO_MO a
left join MO_MODocType_Trl b on a.MODocType=b.id
-- 关联生产订单对应材料成本 EarnedCost 记入成本 ActualQty 实际用量 ItemMaster 料品信息 Period 会计期间
left join CA_CostQuery c on a.ID=c.mo
-- 会计期间 计算几月份的在制成本则算该月份的成本占比情况
left join Base_SOBAccountingPeriod d on c.SOBPeriod= d.id
where b.sysmlflag='zh-CN' and b.name in ('现场拆卸订单','公司拆卸订单') and docNO='MO-2407200008'
and d.DisplayName='2024-07'
and c.AdjustWIPCost_CURRENTcost + c.AdjustWIPCost_PRIORcost <> 0
枚举值
select A.ExtEnumType,A.ExtEnumTypeUID,A2.Code as EnumTypeCode,A.Code,A.EValue,A1.Name
from UBF_Sys_ExtEnumValue A
INNER JOIN UBF_Sys_ExtEnumValue_Trl A1 ON (A1.ID=A.ID and a1.SysMLFlag='zh-CN')
LEFT JOIN UBF_Sys_ExtEnumType A2 ON A2.ID = A.ExtEnumType
where A2.Code='UFIDA.U9.Base.SOB.SOBTypeEnum'--此处更改字典查到的枚举值即可
自制报表存储过程写法
-- 此段代码用于调试信息用，在普通查询中调用存储过程
exec CGdingdan @DOCNO=N' (DOCNO = N''PO0-2406010005'') ',@status='2'
--exec CGdingdan @DOCNO='PO0-2406010005',@status='2'
--用于u9c报表开发，与前端控件条件做自定义筛选用，如> < = 包含，不等于等等条件，或者为空时数据处理。
ALTER PROCEDURE [dbo].[CGdingdan]
@DOCNO NVARCHAR(1000),
@status char(1)
--@date date
AS
BEGIN
declare @doc_condition nvarchar(1000)
if(isnull(@DocNo,'')!='')
begin
set @doc_condition = ' and ' + replace(@DOCNO,'DOCNO','a.DOCNO')
print (@doc_condition)
end
else
begin
set @doc_condition =''
end
declare @sql nvarchar(max)
set @sql='
select a.DocNo,a.BusinessDate,a.Status,b.PurQtyTU from PM_PurchaseOrder a
left join PM_POLine b on a.ID=b.PurchaseOrder
left join PM_POShipLine c on b.ID=c.POLine
where a.status='
-- + '2' + 'and a.DOCNO = N''PO0-2406010005'
+ @status+@doc_condition
print(@sql)
exec(@sql)
-----------------------------------------------------------------------
-- 在UBF调试时发现字段信息无法正常取出，用下段代码带出
-- 调试完成后，切换为上段代码，作用是查询方式比较灵活支持各种><=包含，不包含等
--select a.DocNo,a.BusinessDate,a.Status,b.PurQtyTU from PM_PurchaseOrder a
--left join PM_POLine b on a.ID=b.PurchaseOrder
--left join PM_POShipLine c on b.ID=c.POLine
-- where a.status= @status
--and a.DOCNO =@DOCNO
END
拆卸成本调整单（现场）
厂外：拆卸订单和BOM对比（也就是没有拆出的物料）找到价格，算出这些材料价格占比。然后把订单在制金额（已经拆卸过的剩余成本+人工等等成本），分摊到这些拆不出的材料上
-- 拆卸订单和BOM对比（也就是没有拆出的物料）找到价格，算出这些材料价格占比。然后把订单在制金额（已经拆卸过的剩余成本+人工等等成本），分摊到这些拆不出的材料上
SELECT
*,
( AVG ( CaiLiaoF ) * ( FenTanXiS / FenTanXiS_SUM ) ) AS CaiLiaoFFT,
( AVG ( RenGongF ) * ( FenTanXiS / FenTanXiS_SUM ) ) AS RenGongFFT,
( AVG ( ZhiZaoF ) * ( FenTanXiS / FenTanXiS_SUM ) ) AS ZhiZaoFFT,
( AVG ( WaiXieF ) * ( FenTanXiS / FenTanXiS_SUM ) ) AS WaiXieFFT,
( AVG ( JiQiF ) * ( FenTanXiS / FenTanXiS_SUM ) ) AS JiQiFFT
FROM
(
SELECT
*,
SUM ( FenTanXiS ) OVER ( ) AS FenTanXiS_SUM -- 使用窗口函数计算 B 列的总和
FROM
(
SELECT
*,
( TEMPS.BOMMZUsageQty* TEMPSS.BOMZCostAVG ) AS FenTanXiS
FROM
(
----------------------------------------------
--料品信息
SELECT
MO_MO.ID,
MO_MO.DocNO,
MO_MO.BOMMaster,
DATEPART( yy, FICloseDate ) AS MO_MOYear,
FORMAT ( FICloseDate, 'MM' ) AS MO_MOMonth,
CBO_ItemMaster.code,
CBO_ItemMaster.name,
CBO_ItemMaster.specs,
BM.BOMVersionCode,
BOMMZ.BOMMZCode,
BOMMZ.BOMMZName,
BOMMZ.BOMMZspecs,
BOMMZ.BOMMZUsageQty
FROM
MO_MO
LEFT JOIN CBO_ItemMaster ON MO_MO.ItemMaster= CBO_ItemMaster.ID
LEFT JOIN CBO_BOMMaster BM ON MO_MO.BOMMaster= BM.ID
LEFT JOIN (
-------------------------------------------------
-- BOM 子项
SELECT
BOMMaster,
ItemMaster,
CBO_ItemMaster.Code AS BOMMZCode,
CBO_ItemMaster.Name AS BOMMZName,
CBO_ItemMaster.specs AS BOMMZspecs,
UsageQty AS BOMMZUsageQty
FROM
CBO_BOMComponent
LEFT JOIN CBO_ItemMaster ON CBO_BOMComponent.ItemMaster= CBO_ItemMaster.ID
--------------------------------------------------
) BOMMZ ON MO_MO.BOMMaster= BOMMZ.BOMMaster
WHERE
MODocType = '1002405130000021' --AND DocNO='MO-2407200008'
AND BOMMZ.ItemMaster NOT IN ( SELECT item FROM MO_MOOutput WHERE MO_MOOutput.MO= MO_MO.ID )
) TEMPS
LEFT JOIN (
----------------------------------------------------
-- 料品存货成本
SELECT
iteminfo_itemcode,
Base_SOBAccountingPeriod.Year,
Base_SOBAccountingPeriod.Code AS BOMZCostMonth,
AVG ( ElementTypeCost_MaterialCurrentCost ) AS BOMZCostAVG
FROM
IC_ItemInvCost
LEFT JOIN Base_SOBAccountingPeriod ON IC_ItemInvCost.SOBPeriod= Base_SOBAccountingPeriod.ID
GROUP BY
iteminfo_itemcode,
Base_SOBAccountingPeriod.Year,
Base_SOBAccountingPeriod.Code
) TEMPSS ON TEMPS.BOMMZCode= TEMPSS.ItemInfo_ItemCode
------------------------------------------------
LEFT JOIN (
-- 调整成本费用表
----------------------------------------
SELECT
MO,
SOBPeriod,
[1002311060101521] AS CaiLiaoF,
[1002311060101523] AS RenGongF,
[1002311060101525] AS ZhiZaoF,
[1002311060101527] AS WaiXieF,
[1002311060101529] AS JiQiF
FROM
(
SELECT
MO,
SOBPeriod,
costelement,
( AdjustWIPCost_CURRENTcost + AdjustWIPCost_PRIORcost ) AS TZCost
FROM
CA_CostQuery
LEFT JOIN CBO_CostElement ON CA_CostQuery.costelement= CBO_CostElement.ID
WHERE
AdjustWIPCost_CURRENTcost + AdjustWIPCost_PRIORcost <> 0 --and MO='1002407230004056'
) AS SourceTable PIVOT ( SUM ( TZCost ) FOR costelement IN ( [1002311060101521], [1002311060101523], [1002311060101525], [1002311060101527], [1002311060101529] ) ) AS PivotedTable
---------------------------------------------
) TEMPSSS ON TEMPS.ID= TEMPSSS.MO
WHERE
TEMPS.MO_MOYear= TEMPSS.Year
AND TEMPS.MO_MOMonth= TEMPSS.BOMZCostMonth
) AS temps1
) AS TEMPS2
WHERE
1 = 1 --and (ISNULL(@DocNO, '') = '' OR DocNO = @DocNO)
--and MO_MOYear= @MO_MOYear
--and MO_MOMonth= @MO_MOMonth
GROUP BY
TEMPS2.ID,
TEMPS2.DocNO,
TEMPS2.BOMMaster,
TEMPS2.BOMMaster,
TEMPS2.MO_MOYear,
TEMPS2.MO_MOMonth,
TEMPS2.code,
TEMPS2.name,
TEMPS2.BOMVersionCode,
TEMPS2.BOMMZCode,
TEMPS2.BOMMZName,
TEMPS2.BOMMZUsageQty,
TEMPS2.iteminfo_itemcode,
TEMPS2.Year,
TEMPS2.BOMZCostMonth,
TEMPS2.BOMZCostAVG,
TEMPS2.MO,
TEMPS2.SOBPeriod,
TEMPS2.CaiLiaoF,
TEMPS2.RenGongF,
TEMPS2.ZhiZaoF,
TEMPS2.WaiXieF,
TEMPS2.JiQiF,
TEMPS2.FenTanXiS,
TEMPS2.FenTanXiS_SUM,
TEMPS2.specs,
TEMPS2.BOMMZspecs
ORDER BY
DocNo
拆卸成本调整单（公司）
与厂外不同，内部拆卸订单的价格按实际库存价格算占比
ALTER PROCEDURE [dbo].[TiaoZhengDanGS]
@DocNO nvarchar(50),
@DocType nvarchar(50),
@MO_MOYear int,
@MO_MOMonth int
AS
BEGIN
--整合
SELECT *,(AVG(CaiLiaoF) * (SS1 / Total_B)) AS CaiLiaoFFT,(AVG(RenGongF) * (SS1 / Total_B)) AS RenGongFFT,(AVG(ZhiZaoF) * (SS1 / Total_B)) AS ZhiZaoFFT,(AVG(WaiXieF) * (SS1 / Total_B)) AS WaiXieFFT,(AVG(JiQiF) * (SS1 / Total_B)) AS JiQiFFT
FROM (
SELECT
MO_MO.ID,MO_MO.DocNO,
--MO_MO.BOMMaster,
MO_MO.MODocType,DATEPART(yy, ActualCompleteDate) AS MO_MOYear,FORMAT(ActualCompleteDate, 'MM') AS MO_MOMonth,
CBO_ItemMaster.code,CBO_ItemMaster.name,
MO_MOOutputTEMP.ChanChuMO,MO_MOOutputTEMP.ChanChuCode,MO_MOOutputTEMP.ChanChuName,ChanChuQty,
ISNULL(ChanChuZCostAVG, 0) AS ChanChuZCostAVG,
CaiLiaoF,RenGongF ,ZhiZaoF, WaiXieF, JiQiF,
(ChanChuQty*ISNULL(ChanChuZCostAVG, 0)) AS SS1,
SUM((ChanChuQty*ISNULL(ChanChuZCostAVG, 0))) OVER () AS Total_B
FROM MO_MO
LEFT JOIN CBO_ItemMaster ON MO_MO.ItemMaster=CBO_ItemMaster.ID
RIGHT JOIN (
--拆卸订单产出
SELECT MO_MOOutput.MO AS ChanChuMO,CBO_ItemMaster.Code AS ChanChuCode,CBO_ItemMaster.Name AS ChanChuName,PlanOutputQty AS ChanChuQty FROM MO_MOOutput
LEFT JOIN CBO_ItemMaster ON MO_MOOutput.Item=CBO_ItemMaster.ID
--产出类型0主产品1副产品2联产品
WHERE MO_MOOutput.OutputType <>'0' AND (MO_MOOutput.MO+MO_MOOutput.Item) NOT IN (SELECT MO+Item FROM Complete_RcvRptDocLine)
) MO_MOOutputTEMP ON MO_MO.ID=MO_MOOutputTEMP.ChanChuMO
LEFT JOIN (
--料品期间结存单价
SELECT iteminfo_itemcode,Base_SOBAccountingPeriod.Year,Base_SOBAccountingPeriod.Code AS ChanChuZCostMonth,AVG(ElementTypeCost_MaterialCurrentCost) AS ChanChuZCostAVG FROM IC_ItemInvCost
LEFT JOIN Base_SOBAccountingPeriod ON IC_ItemInvCost.SOBPeriod=Base_SOBAccountingPeriod.ID
GROUP BY iteminfo_itemcode,Base_SOBAccountingPeriod.Year,Base_SOBAccountingPeriod.Code
) JieCunDJ ON MO_MOOutputTEMP.ChanChuCode=JieCunDJ.ItemInfo_ItemCode
LEFT JOIN (
--调整单金额
SELECT MO,SOBPeriod,[1002311060101521] AS CaiLiaoF, [1002311060101523] AS RenGongF , [1002311060101525] AS ZhiZaoF, [1002311060101527] AS WaiXieF, [1002311060101529] AS JiQiF
FROM
(
SELECT MO,SOBPeriod,costelement,(AdjustWIPCost_CURRENTcost+AdjustWIPCost_PRIORcost) AS TZCost FROM CA_CostQuery
left join CBO_CostElement ON CA_CostQuery.costelement=CBO_CostElement.ID
WHERE AdjustWIPCost_CURRENTcost + AdjustWIPCost_PRIORcost <> 0 --and MO='1002407230004056'
) AS SourceTable
PIVOT
(
SUM(TZCost) FOR costelement IN ( [1002311060101521], [1002311060101523], [1002311060101525], [1002311060101527], [1002311060101529])
) AS PivotedTable
) TiaoZhengD ON MO_MO.ID=TiaoZhengD.MO
) AS TEMP2
--WHERE MODocType='1002405150054799' AND DocNO='MO-2410290003'
WHERE
1 = 1
and MODocType='1002405150054799'
and (ISNULL(@DocNO, '') = '' OR DocNO = @DocNO)
and (ISNULL(@DocType, '') = '' OR MODocType=@DocType)
and MO_MOYear= @MO_MOYear
and MO_MOMonth= @MO_MOMonth
GROUP BY TEMP2.ID,TEMP2.DocNO,MODocType,MO_MOYear,MO_MOMonth,TEMP2.code,TEMP2.name,TEMP2.ChanChuMO,TEMP2.ChanChuCode,TEMP2.ChanChuName,TEMP2.ChanChuQty,
TEMP2.ChanChuZCostAVG,TEMP2.CaiLiaoF,TEMP2.RenGongF,TEMP2.ZhiZaoF,TEMP2.WaiXieF,TEMP2.JiQiF,TEMP2.SS1,TEMP2.Total_B
order by docNO
END
项目制造费用与订单对比
用于财务查询当月做凭证对应辅助核算，与当月实际业务订单对应不上的情况，方便调整
with u9c5101 as (
SELECT
FORMAT(b.BusinessDate, 'yyyy-MM') as period,
a.AccountedDr,
a.AccountedCr,
CASE
WHEN CHARINDEX('|', a.OpposingAccountCodes) > 0 THEN
LEFT(a.OpposingAccountCodes, CHARINDEX('|', a.OpposingAccountCodes) - 1)
ELSE
a.OpposingAccountCodes
END AS code,
LEFT(a.OpposingAccountNames, 4) AS codename,
--a.OpposingAccountNames,
b.org,
--b.BusinessDate,
c.name,
d.Abstracts ,
d.accountdisplayname,
e.segment6,
b.DocNo as fullname,
b.voucherdisplaycode
FROM gl_entry a
LEFT JOIN GL_Voucher b ON a.Voucher = b.id
left join Base_Organization_Trl c on b.org=c.id
left join GL_Entry_Trl d on a.id=d.id
left join CBO_Account e on a.account = e.id
WHERE 1=1
and b.VoucherSourceMethod!=9 and b.VoucherStatus!=0 --排除期初和草稿凭证
and FORMAT(b.BusinessDate, 'yyyy-MM') = '2024-06'
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') <= '2024-06-30'
AND a.iserror=0
and e.segment6 !='0'
--and a.projectcode is not null
and a.OpposingAccountCodes LIKE '5101%'
),
U9CMO AS (
SELECT
MO_MO.BusinessDate,
MO_MO.ID,
MO_MO.DocNO,
MO_MO.BOMMaster,
DATEPART( yy, FICloseDate ) AS MO_MOYear,
FORMAT ( FICloseDate, 'MM' ) AS MO_MOMonth,
CBO_ItemMaster.code as liaohao,
CBO_ItemMaster.name as mingcheng ,
CBO_ItemMaster.specs as guige,
BM.BOMVersionCode,
CBO_Project.code AS projectname,
CBO_Project_trl.name as jiancheng
FROM
MO_MO
LEFT JOIN CBO_ItemMaster ON MO_MO.ItemMaster= CBO_ItemMaster.ID
LEFT JOIN CBO_BOMMaster BM ON MO_MO.BOMMaster= BM.ID
LEFT JOIN CBO_Project ON MO_MO.Project=CBO_Project.ID
left join CBO_Project_trl on CBO_Project.id=CBO_Project_trl.id
where 1=1
and FORMAT(MO_MO.BusinessDate, 'yyyy-MM')='2024-06'
AND CBO_Project.code IS NOT NULL
)
select a.*,b.docno,b.liaohao,b.mingcheng,b.guige,b.projectname,b.jiancheng from u9c5101 a left join U9CMO b on a.segment6=b.projectname
科目余额表
--科目余额表
with qichu as (
select t1.*,
t1.qichujie+t1.fashengjie as qimojie,
t1.qichudai+t1.fashengdai as qimodai,
t1.beginmoney+t1.fasheng as qimoyue
from (
select LEFT(b.code, CHARINDEX('|', b.code) - 1) as code,
LEFT(f.Displayname, CHARINDEX('|', f.Displayname) - 1) AS codename,
e.direction,
g.displayname as year_month,
g.year,g.code as month,
h.name as company,
sum(a.BalanceValue_BeginBalanceDr) as qichujie,
sum(a.BalanceValue_BeginBalanceCr) as qichudai,
sum(
case when e.Direction=0
then (a.BalanceValue_BeginBalanceDr-a.BalanceValue_BeginBalanceCr)
else (a.BalanceValue_BeginBalanceCr-a.BalanceValue_BeginBalanceDr) end
) as beginmoney,
sum(a.BalanceValue_PeriodNetDr) as fashengjie,
sum(a.BalanceValue_PeriodNetCr) as fashengdai,
sum(
case when e.Direction=0
then (a.BalanceValue_PeriodNetDr-a.BalanceValue_PeriodNetCr)
else (a.BalanceValue_PeriodNetCr-a.BalanceValue_PeriodNetDr) end
) as fasheng
from GL_GeneralLedger a
inner join Base_SetofBooks a1 on a1.ID=a.AccessKey_SOB
inner join CBO_Account b on b.id=a.AccessKey_Account
--inner join MANA_AssetLiabilitys d on d.SubjectAttribute=b.AccountProperty
inner join CBO_AccountProperty e on e.id=b.AccountProperty
inner join CBO_Account_trl f on b.id=f.id
inner join Base_SOBAccountingPeriod g on a.AccessKey_PostPeriod= g.id
inner join Base_Organization_trl h on b.org= h.id
where a1.Code=102 and a.AccessKey_Currency=1
--and g.displayname in ('2024-06')
and a.AccessKey_IsSumLowLevel=0
--AND B.CODE='1001|0|0|0|0|0|0|0|0|0|0|0|0'
--and d.SubjectAttribute=@AccountAttribute and d.AssetLiability=@AssetReportItem
group by LEFT(b.code, CHARINDEX('|', b.code) - 1) ,
LEFT(f.Displayname, CHARINDEX('|', f.displayname) - 1) ,
g.displayname ,
g.year,g.code ,
h.name ,
e.direction
) t1
)
select * from qichu where year_month = CONVERT(VARCHAR(7), '2024-10-01', 120)
科目余额明细表
(
select t1.*,
t1.qichujie+t1.fashengjie as qimojie,
t1.qichudai+t1.fashengdai as qimodai,
t1.beginmoney+t1.fasheng as qimoyue
from (
select LEFT(b.code, CHARINDEX('|', b.code) - 1) as code,
f.Displayname AS codename,
SUBSTRING(
f.Displayname,
CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname) + 1) + 1) + 1,
CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname) + 1) + 1) + 1) -
CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname, CHARINDEX('|', f.Displayname) + 1) + 1) - 1
) AS kehu,
e.direction,
g.displayname as year_month,
g.year,g.code as month,
h.name as company,
sum(a.BalanceValue_BeginBalanceDr) as qichujie,
sum(a.BalanceValue_BeginBalanceCr) as qichudai,
sum(
case when e.Direction=0
then (a.BalanceValue_BeginBalanceDr-a.BalanceValue_BeginBalanceCr)
else (a.BalanceValue_BeginBalanceCr-a.BalanceValue_BeginBalanceDr) end
) as beginmoney,
sum(a.BalanceValue_PeriodNetDr) as fashengjie,
sum(a.BalanceValue_PeriodNetCr) as fashengdai,
sum(
case when e.Direction=0
then (a.BalanceValue_PeriodNetDr-a.BalanceValue_PeriodNetCr)
else (a.BalanceValue_PeriodNetCr-a.BalanceValue_PeriodNetDr) end
) as fasheng
from GL_GeneralLedger a
inner join Base_SetofBooks a1 on a1.ID=a.AccessKey_SOB
inner join CBO_Account b on b.id=a.AccessKey_Account
--inner join MANA_AssetLiabilitys d on d.SubjectAttribute=b.AccountProperty
inner join CBO_AccountProperty e on e.id=b.AccountProperty
inner join CBO_Account_trl f on b.id=f.id
inner join Base_SOBAccountingPeriod g on a.AccessKey_PostPeriod= g.id
inner join Base_Organization_trl h on b.org= h.id
where a1.Code=102 and a.AccessKey_Currency=1
--and g.displayname in ('2024-06')
and a.AccessKey_IsSumLowLevel=0
--AND B.CODE='1001|0|0|0|0|0|0|0|0|0|0|0|0'
--and d.SubjectAttribute=@AccountAttribute and d.AssetLiability=@AssetReportItem
group by LEFT(b.code, CHARINDEX('|', b.code) - 1) ,
f.displayname ,
g.displayname ,
g.year,g.code ,
h.name ,
e.direction
) t1
)
现金表
现金 银行存款 票据（银行承兑 商业承兑）
作用是与NC底层表逻辑一致，方便后续做表
--发生额
with uccashflow as (
SELECT
a.AccountedDr,
a.AccountedCr,
e.segment1 as code ,
left(e.segment1 ,4) as tcode,
f.displayname,
left(f.displayname ,4) as tcodename,
--a.OpposingAccountNames,
b.org,
b.BusinessDate,
c.name,
d.Abstracts
FROM gl_entry a
LEFT JOIN GL_Voucher b ON a.Voucher = b.id
left join Base_Organization_Trl c on b.org=c.id
left join GL_Entry_Trl d on a.id=d.id
left join CBO_Account e on a.Account=e.id
left join CBO_Account_Trl f on e.id=f.id
WHERE 1=1
and b.VoucherSourceMethod!=9 and b.VoucherStatus!=0 --排除期初和草稿凭证
and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
and FORMAT(b.BusinessDate, 'yyyy-MM-dd') < '2024-11-20'
AND a.iserror=0
and LEFT(e.Segment1,4) IN('1001','1002','1121')
),
-- 期初额
qichu as (
select t1.*,
t1.qichujie+t1.fashengjie as qimojie,
t1.qichudai+t1.fashengdai as qimodai,
t1.beginmoney+t1.fasheng as qimoyue
from (
select LEFT(b.code, CHARINDEX('|', b.code) - 1) as code,
LEFT(b.code, 4) as Tcode,
LEFT(f.Displayname, CHARINDEX('|', f.Displayname) - 1) AS codename,
LEFT(f.Displayname, 4) AS tcodename,
e.direction,
g.displayname as year_month,
g.year,g.code as month,
h.name as company,
sum(a.BalanceValue_BeginBalanceDr) as qichujie,
sum(a.BalanceValue_BeginBalanceCr) as qichudai,
sum(
case when e.Direction=0
then (a.BalanceValue_BeginBalanceDr-a.BalanceValue_BeginBalanceCr)
else (a.BalanceValue_BeginBalanceCr-a.BalanceValue_BeginBalanceDr) end
) as beginmoney,
sum(a.BalanceValue_PeriodNetDr) as fashengjie,
sum(a.BalanceValue_PeriodNetCr) as fashengdai,
sum(
case when e.Direction=0
then (a.BalanceValue_PeriodNetDr-a.BalanceValue_PeriodNetCr)
else (a.BalanceValue_PeriodNetCr-a.BalanceValue_PeriodNetDr) end
) as fasheng
from GL_GeneralLedger a
inner join Base_SetofBooks a1 on a1.ID=a.AccessKey_SOB
inner join CBO_Account b on b.id=a.AccessKey_Account
--inner join MANA_AssetLiabilitys d on d.SubjectAttribute=b.AccountProperty
inner join CBO_AccountProperty e on e.id=b.AccountProperty
inner join CBO_Account_trl f on b.id=f.id
inner join Base_SOBAccountingPeriod g on a.AccessKey_PostPeriod= g.id
inner join Base_Organization_trl h on b.org= h.id
where a1.Code=102 and a.AccessKey_Currency=1
--and g.displayname in ('2024-06')
and a.AccessKey_IsSumLowLevel=0
--AND B.CODE='1001|0|0|0|0|0|0|0|0|0|0|0|0'
--and d.SubjectAttribute=@AccountAttribute and d.AssetLiability=@AssetReportItem
group by LEFT(b.code, CHARINDEX('|', b.code) - 1) ,
LEFT(b.code, 4) ,
LEFT(f.Displayname, CHARINDEX('|', f.displayname) - 1) ,
LEFT(f.Displayname, 4),
g.displayname ,
g.year,g.code ,
h.name ,
e.direction
) t1
where t1.tcode in('1001','1002','1121')
)
-----------------------------------------------------------
select sum(a.余额) as 余额,a.TCODENAME,a.COMPANYNAME from
(
SELECT
SUM(accountedcr) AS 贷,
SUM(accounteddr) AS 借,
SUM(accounteddr) - SUM(accountedcr) AS 余额,
tcodename,
tcode,
name as companyname,
'发生额' as leixing
FROM
uccashflow
GROUP BY
tcodename,
tcode,
name
UNION ALL
SELECT
qichudai AS 贷,
qichujie AS 借,
beginmoney AS 余额,
tcodename,
tcode,
company as companyname,
'年初额' as leixing
FROM
qichu
WHERE 1=1
and year_month = CONVERT(VARCHAR(7), '2024-01-01', 120)
--and year_month = CAST(YEAR('${aaa}') AS VARCHAR(4)) + '-01'
) a
where 1=1
and a.companyname in ('XXXXXX有限公司')--表中的公司
and a.tcodename in ('库存现金')
group by a.tcodename,a.companyname
凭证表
用于制作财务相关表的发生额数据
SELECT
a.AccountedDr,
a.AccountedCr,
e.segment1 as code ,
e.Balancedirection,
left(e.segment1 ,4) as tcode,
f.displayname,
CASE
WHEN COALESCE(NULLIF(CHARINDEX('┆', f.displayname), 0), LEN(f.displayname) + 1) <
COALESCE(NULLIF(CHARINDEX('|', f.displayname), 0), LEN(f.displayname) + 1) THEN
SUBSTRING(f.displayname, 1, CHARINDEX('┆', f.displayname) - 1)
ELSE
SUBSTRING(f.displayname, 1, CHARINDEX('|', f.displayname) - 1)
END AS tcodename,
--left(f.displayname ,4) as tcodename,
--a.OpposingAccountNames,
b.org,
b.BusinessDate,
c.name,
d.Abstracts
FROM gl_entry a
LEFT JOIN GL_Voucher b ON a.Voucher = b.id
left join Base_Organization_Trl c on b.org=c.id
left join GL_Entry_Trl d on a.id=d.id
left join CBO_Account e on a.Account=e.id
left join CBO_Account_Trl f on e.id=f.id
WHERE 1=1
and b.VoucherSourceMethod!=9 and b.VoucherStatus!=0 --排除期初和草稿凭证
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') >= '2024-06-01'
--and FORMAT(b.BusinessDate, 'yyyy-MM-dd') <= '2024-11-30'
AND a.iserror=0
--and LEFT(e.Segment1,4) IN('1001','1002','1121')
采购入库收货
用于采购降本调取U9C采购入库价格。
select
f.name,
a.CreatedOn as 创建时间,
b.DocNo as 收货单号,
b.Supplier_Code as 供应商编号,
d.Name as 供应商名称,
a.DocLineNo as 收货单行号,
a.ItemInfo_ItemCode as 物料编码,
a.ItemInfo_ItemName as 物料名称,
e.SPECS as 规格型号,
c.Name as 仓库,
a.RcvQtyCU as 入库数量,
g.name as 单位,
a.FinallyPriceTC as 含税价,
a.TotalMnyAC as 含税金额,
a.ConfirmDate as 入库确认日期,
a.SrcPO_SrcDocNo as 采购订单号,
a.SrcPO_SrcDocLineNo as 采购订单行号,
a.Status as 状态
from PM_RcvLine a
left join PM_Receivement b on a.Receivement=b.ID
left join CBO_Wh_Trl c on a.Wh=c.ID
left join CBO_Supplier_Trl d on b.Supplier_Supplier=d.ID
left join CBO_ItemMaster e ON a.ItemInfo_ItemID=e.id
left join Base_Organization_Trl f on a.CurrentOrg=f.id
left join Base_UOM_trl g on a.StoreUOM=g.id
where a.Status=5 and a.SrcDocType=1
生产订单关联领料单情况
主要用于查询当月已下达订单，已发料，但未入库的在制品情况
SELECT
DATEPART(yy, f.BusinessDate) AS MO_MOYear,FORMAT(f.BusinessDate, 'MM') AS MO_MOMonth,
--a.Org,
b.name as 公司,
h.code as 生产订单料品编码,
h.code2 as 生产订单NC料号,
h.name as 生产订单料品名称,
h.SPECS as 生产订单规格,
h.code1 as 生产订单图号,
d.mo as 生产订单,
f.docno as 生产订单号,
f.ProductQty as 生产订单数量,
--f.DocState as 生产订单状态,
case
when f.DocState = 0 then '开立'
when f.DocState = 1 then '已核准'
when f.DocState = 2 then '开工'
when f.DocState = 3 then '完工'
when f.DocState = 4 then '核准中'
end as 生产订单状态,
--f.MODocType as 单据类型,
g.name as 生产订单单据类型,
a.BusinessDate as 领料日期,
a.DocState as 状态码,
case
when a.DocState = 0 then '开立'
when a.DocState = 1 then '已核准'
when a.DocState = 2 then '关闭'
when a.DocState = 3 then '核准中'
end as 领料状态,
c.name as 单据类型,
--d.item as 料品,
e.code as 领料单料品编码,
e.code2 as 领料单NC料号,
e.name as 领料单料品名称,
e.SPECS as 领料单规格,
e.code1 as 领料单图号,
d.IssuedQty as 实发数量,
d.CostAmt as 金额
FROM
MO_IssueDoc a
INNER JOIN Base_Organization_trl b ON a.org= b.id
INNER JOIN MO_IssueDocType_Trl c on a.IssueDocType = c.id
right join mo_issuedocline d on a.id = d.IssueDoc
LEFT JOIN CBO_ItemMaster e ON d.item=e.ID
left join MO_MO f on d.MO = f.id
left join MO_MODocType_trl g on f.MODocType=g.id
left join CBO_ItemMaster h on f.itemmaster= h.id
WHERE 1=1
--a.DocState!=2
and c.SysMLFlag='zh-CN'
and g.SysMLFlag='zh-CN'
and f.DocState !=3
and a.DocState = 2
人资
期初员工数据
select t1.* from (
SELECT
--person.email,
CONVERT ( VARCHAR ( 100 ), b.AssgnBeginDate, 23 ) AS 开始时间,
--CONVERT ( VARCHAR ( 100 ), b.AssgnEndDate, 23 ) as 结束时间,
CASE
WHEN b.AssgnEndDate IS NULL THEN FORMAT(EOMONTH(GETDATE()), 'yyyy-MM-dd')
ELSE CONVERT ( VARCHAR ( 100 ), b.AssgnEndDate, 23 )
END AS 结束时间,
--b.AssgnEndDate,
--a.OwnerOrg AS org_id,
e.name as 人资组织,
--a.Dept AS dept_id,
d.name as 部门,
b.PositionStatus AS 是否在岗,
a.Person AS staff_id,
a.ID AS objid,
a.EmployeeCode AS 员工号,
a.Name AS 人员,
a.EndPositionActive 是否结束,
b.AssignActive,
b.ismain AS 最新记录
FROM
CBO_EmployeeArchive a
-- 任职记录表
JOIN CBO_EmployeeAssignment b ON a.ID= b.Employee
-- 人员信息表
JOIN CBO_Person person ON person.ID= a.Person
left join CBO_Department_Trl d on a.dept= d.id
left join Base_Organization_Trl e on a.OwnerOrg = e.id
WHERE 1=1
and b.IsMain= 1
AND a.Dept>0
--and (b.AssignActive=4 or b.AssignActive=-1) -- 任职活动 4 入职 10 内部调动 11 跨组织调动 -- 结束任职活动 EndAssignActive
-- 离职 0 辞退 2 离退休 3
) t1
where 1=1
AND 开始时间 <= EOMONTH(DATEADD(MONTH, -1, '2025-01-01'))
AND 结束时间 >= EOMONTH(DATEADD(MONTH, -1, '2025-01-01'))
岗位人员信息
select t1.员工号,t1.部门,t1.人资组织,t1.岗位名,t1.人员,
case when t1.性别=0 then 1
when t1.性别=1 then 2 end as 性别,
case when t1.学历名称 ='中等专科毕业' then '中专'
when t1.学历名称 ='大学专科毕业' then '大专'
when t1.学历名称 ='大学本科毕业' then '大学本科'
when t1.学历名称 ='普通初中毕业' then '初中'
when t1.学历名称 ='普通高中毕业' then '高中'
when t1.学历名称 ='研究生班毕业' then '硕士研究生'
when t1.学历名称 ='硕士研究生毕业' then '硕士研究生'
else '无' end as 学历名称,
t1.人员类别,t1.工作年龄,t1.年龄 from (
SELECT
--person.email,
CONVERT ( VARCHAR ( 100 ), b.AssgnBeginDate, 23 ) AS 开始时间,
--CONVERT ( VARCHAR ( 100 ), b.AssgnEndDate, 23 ) as 结束时间,
CASE
WHEN b.AssgnEndDate IS NULL THEN FORMAT(EOMONTH(GETDATE()), 'yyyy-MM-dd')
ELSE CONVERT ( VARCHAR ( 100 ), b.AssgnEndDate, 23 )
END AS 结束时间,
--b.AssgnEndDate,
--a.OwnerOrg AS org_id,
e.name as 人资组织,
--a.Dept AS dept_id,
d.name as 部门,
b.PositionStatus AS 是否在岗,
a.Person AS staff_id,
a.ID AS objid,
a.EmployeeCode AS 员工号,
a.Name AS 人员,
a.EndPositionActive 是否结束,
b.AssignActive,
b.ismain AS 最新记录,
f.name as 岗位名,
g.name as 学历名称,
i.name as 人员类别,
person.sex as 性别,
person.workage as 工作年龄,
person.age as 年龄
FROM
CBO_EmployeeArchive a
-- 任职记录表
JOIN CBO_EmployeeAssignment b ON a.ID= b.Employee
-- 人员信息表
JOIN CBO_Person person ON person.ID= a.Person
left join CBO_Department_Trl d on a.dept= d.id
left join Base_Organization_Trl e on a.OwnerOrg = e.id
left join CBO_Position_trl f on a.Position= f.id
left join CBO_AcademicCredential_Trl g on person.Diploma=g.id
left join CBO_Position h on a.Position= h.id
left join CBO_Positiontype_trl i on h.PositionType = i.id
WHERE 1=1
and b.IsMain= 1
AND a.Dept>0
) t1
where 1=1
--AND 开始时间 <= '${sdate}'
--AND 结束时间 >= '${sdate}'
AND 开始时间 <= '2025-02-22'
AND 结束时间 >= '2025-02-22'
人员调动信息
其中部分补充字段为适应NC系统字段信息
select t1.* from (
select a.person as PK_PSNDOC,a.transferdate as 日期,b.name as 调配人员,c.name as 原组织,d.name as 原部门,e.name as 现组织,f.name as 现部门,
'已执行' AS SPZT, '调动' as 调整类型,'组织内调配' as 调配方式,'' as 调整原因 from HI_InsideTransDocPerson a
left join CBO_Person b on a.person = b.id
left join Base_Organization_Trl c on a.BeforeBusinessOrg=c.id
left join CBO_Department_Trl d on a.BeforeDept=d.id
left join Base_Organization_Trl e on a.AcceptBusinessOrg=e.id
left join CBO_Department_Trl f on a.AcceptDepart=f.id
where a.status = 5
union
-- 离职
select a.person as PK_PSNDOC,a.DimissionDate as 日期,b.name as 调配人员,c.name as 原组织,d.name as 原部门,c.name as 现组织,d.name as 现部门,
'已执行' AS SPZT, '辞职' as 调整类型,'组织内调配' as 调配方式,g.DimissionReason as 调整原因
from HI_DimissionDoc a
left join CBO_Person b on a.person = b.id
left join Base_Organization_Trl c on a.ApplyBusinessOrg=c.id
left join CBO_Department_Trl d on a.ApplyDept=d.id
left join HI_DimissionDoc_trl g on a.id = g.id
where a.DocStatus = 5
) t1 where 1=1
AND CONVERT(VARCHAR(6), CAST(t1.日期 AS DATE), 112) = CONVERT(VARCHAR(6), CAST(CONVERT(DATE, '${sdate}', 120) AS DATE), 112)
离职人员信息
-- 离职
-- 离职
select t1.* from (
select a.person as CODE,a.DimissionDate as 日期,b.name as 调配人员,c.name as 现组织,d.name as 现部门,
'辞职' as 调整类型,'组织内调配' as 调配方式,e.DimissionReason as 调整原因,
case when g.name ='中等专科毕业' then '中专'
when g.name ='大学专科毕业' then '大专'
when g.name ='大学本科毕业' then '大学本科'
when g.name ='普通初中毕业' then '初中'
when g.name ='普通高中毕业' then '高中'
when g.name ='研究生班毕业' then '硕士研究生'
when g.name ='硕士研究生毕业' then '硕士研究生'
else '无' end as 学历名称,
j.name as 人员类别,b.workage as 工作年龄
from HI_DimissionDoc a
left join CBO_Person b on a.person = b.id
left join Base_Organization_Trl c on a.ApplyBusinessOrg=c.id
left join CBO_Department_Trl d on a.ApplyDept=d.id
left join HI_DimissionDoc_trl e on a.id = e.id
left join CBO_AcademicCredential_Trl g on b.Diploma=g.id
-- 人员类别
left join CBO_EmployeeArchive h on a.Employee= h.id
left join CBO_Position i on h.Position=i.id
left join CBO_PositionType_Trl j on i.PositionType = j.id
where a.DocStatus = 5
) t1
where 1=1
AND CONVERT(VARCHAR(6), CAST(t1.日期 AS DATE), 112) = CONVERT(VARCHAR(6), CAST(CONVERT(DATE, '2025-02-02', 120) AS DATE), 112)
网友评论