財務表

資金-現(xiàn)金篮绿，銀行存款，應收票據(jù)發(fā)生額

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

資金—現(xiàn)金，銀行存款拧烦，應收票據(jù)期初額

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  
-- 關聯(lián)生產訂單對應材料成本  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 ('現(xiàn)場拆卸訂單','公司拆卸訂單') 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報表開發(fā)瘫絮，與前端控件條件做自定義篩選用涨冀，如> < = 包含，不等于等等條件麦萤，或者為空時數(shù)據(jù)處理鹿鳖。

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調試時發(fā)現(xiàn)字段信息無法正常取出扁眯，用下段代碼帶出
-- 調試完成后，切換為上段代碼翅帜，作用是查詢方式比較靈活支持各種><=包含姻檀，不包含等
--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

拆卸成本調整單（現(xiàn)場）

廠外：拆卸訂單和BOM對比（也就是沒有拆出的物料）找到價格，算出這些材料價格占比涝滴。然后把訂單在制金額（已經(jīng)拆卸過的剩余成本+人工等等成本）施敢，分攤到這些拆不出的材料上

-- 拆卸訂單和BOM對比（也就是沒有拆出的物料）找到價格，算出這些材料價格占比狭莱。然后把訂單在制金額（已經(jīng)拆卸過的剩余成本+人工等等成本）僵娃，分攤到這些拆不出的材料上
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 -- 使用窗口函數(shù)計算 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聯(lián)產品
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

項目制造費用與訂單對比

用于財務查詢當月做憑證對應輔助核算腋妙，與當月實際業(yè)務訂單對應不上的情況默怨，方便調整

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)

現(xiàn)金表

現(xiàn)金 銀行存款 票據(jù)（銀行承兌 商業(yè)承兌）
作用是與NC底層表邏輯一致，方便后續(xù)做表

--發(fā)生額
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,
        '發(fā)生額' 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 ('庫存現(xiàn)金')
group by a.tcodename,a.companyname

憑證表

用于制作財務相關表的發(fā)生額數(shù)據(jù)

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 創(chuàng)建時間,
b.DocNo as 收貨單號,
b.Supplier_Code as 供應商編號,
d.Name as 供應商名稱,
a.DocLineNo as 收貨單行號,
a.ItemInfo_ItemCode as 物料編碼,
a.ItemInfo_ItemName as 物料名稱,
e.SPECS as 規(guī)格型號,
c.Name as 倉庫,
a.RcvQtyCU as 入庫數(shù)量,
g.name as 單位,
a.FinallyPriceTC as 含稅價,
a.TotalMnyAC as 含稅金額,
a.ConfirmDate as 入庫確認日期,
a.SrcPO_SrcDocNo as 采購訂單號,
a.SrcPO_SrcDocLineNo as 采購訂單行號,
a.Status as 狀態(tài)
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