科目余额表代码

最新推荐文章于 2024-05-08 13:51:52 发布
最新推荐文章于 2024-05-08 13:51:52 发布
阅读量2.6k 收藏 6
点赞数
分类专栏： 金蝶K3二次开发 文章标签： insert join table delete c

科目余额表代码


/**公司名称代码开始**/
Select t.* Into #AcctBal2569457n
From (
    Select 0 FBalanceTotal,a.FAccountID,a.FNumber ,
    convert(varchar(800),a.FName) FName,a.FLevel,a.FDC,a.FDetail,
    a.FParentID,a.FRootID,Isnull(b.FDetailID,0) FDetailID,
    ISNULL(FBeginBalanceFor,0) FBeginBalanceFor, ISNULL(FBeginBalance,0) FBeginBalanceLocal,
    ISNULL(FEndBalanceFor,0) FEndBalanceFor, ISNULL(FEndBalance,0) FEndBalanceLocal,
    ISNULL(FDebitFor,0) FDebit, ISNULL(FDebit,0) FDebitLocal, ISNULL(FCreditFor,0) FCredit,
    ISNULL(FCredit,0) FCreditLocal, ISNULL(FYtdDebitFor,0) FYtdDebit, ISNULL(FYtdDebit,0) FYtdDebitLocal,
    ISNULL(FYtdCreditFor,0) FYtdCredit, ISNULL(FYtdCredit,0) FYtdCreditLocal,
    ISNULL(CASE WHEN FBeginBalanceFor > 0 THEN FBeginBalanceFor END,0) FBeginDebit,
    ISNULL(CASE WHEN FBeginBalance > 0 THEN FBeginBalance END,0) FBeginDebitLocal,
    ISNULL(CASE WHEN FBeginBalanceFor < 0 THEN -FBeginBalanceFor END,0) FBeginCredit,
    ISNULL(CASE WHEN FBeginBalance < 0 THEN -FBeginBalance END,0) FBeginCreditLocal,
    ISNULL(CASE WHEN FEndBalanceFor> 0 THEN FEndBalanceFor END,0) FEndDebit,
    ISNULL(CASE WHEN FEndBalance> 0 THEN FEndBalance END,0) FEndDebitLocal,
    ISNULL(CASE WHEN FEndBalanceFor < 0 THEN -FEndBalanceFor END,0) FEndCredit,
    ISNULL(CASE WHEN FEndBalance < 0 THEN -FEndBalance END,0) FEndCreditLocal,
    convert(varchar(800),'公司名称')   账套
    From AIS20090905160608..t_Account a 
        LEFT OUTER JOIN   (
            Select * From AIS20090905160608..t_Balance 
                WHERE  FCurrencyID=1 And  FYear*100+FPeriod =@MyYear*100+@MyMon  ) b On a.FAccountID = b.FAccountID 
    WHERE 1=1  AND  (a.FGroupID<600 or a.FGroupID>=700) and ( a.FNumber like '2202%'  or a.FNumber like '2241%'  or a.FNumber like '2221%')  ) t

Insert Into #AcctBal2569457n
    ( FBalanceTotal,FAccountID,FNumber ,FName,FLevel,
    FDC,FDetail,FParentID,FRootID,FDetailID, FBeginBalanceFor,
    FBeginBalanceLocal,FEndBalanceFor,FEndBalanceLocal, FDebit,
    FDebitLocal, FCredit, FCreditLocal, FYtdDebit,FYtdDebitLocal,
    FYtdCredit, FYtdCreditLocal, FBeginDebit, FBeginDebitLocal,
    FBeginCredit, FBeginCreditLocal, FEndDebit,FEndDebitLocal,
    FEndCredit, FEndCreditLocal,账套
    ) 
  Select distinct 0 FBalanceTotal,a.FAccountID,a.FNumber ,a.FName,
    a.FLevel,a.FDC,a.FDetail,a.FParentID,a.FRootID,FDetailID=0,
    0 FBeginBalanceFor,0 FBeginBalanceLocal,0 FEndBalanceFor,
    0 FEndBalanceLocal,0 FDebit,0 FDebitLocal,0 FCredit,0 FCreditLocal,
    0 FYtdDebit,0 FYtdDebitLocal,0 FYtdCredit, 0 FYtdCreditLocal,
    0 FBeginDebit,0 FBeginDebitLocal,0 FBeginCredit,0 FBeginCreditLocal,
    0 FEndDebit,0 FEndDebitLocal,0 FEndCredit,0 FEndCreditLocal,'公司名称' 
   From  ( select distinct FNumber from (Select a.FNumber,a.FAccountID,a.FRootID,
        Sum(e.FDC*e.FAmountFor) FDebit,Sum(e.FDC*e.FAmount) FDebitLocal ,
        Sum((1-e.FDC)*e.FAmountFor) FCredit  ,Sum((1-e.FDC)*e.FAmount) FCreditLocal 
        From AIS20090905160608..t_Voucher v,AIS20090905160608..t_VoucherEntry e,AIS20090905160608..t_Account a 
         Where v.FVoucherID=e.FVoucherID And e.FAccountID=a.FAccountID And v.FPosted=0
            and ( a.FNumber like '2202%'  or a.FNumber like '2241%'  or a.FNumber like '2221%')
            And ( FYear*100+FPeriod between @MyYear*100+@MyMon and @MyYear*100+@MyMon)  And e.FCurrencyID=1
            And  (a.FGroupID<600 or a.FGroupID>=700)  Group By a.FNumber,a.FAccountID,a.FRootID  ) xyz ) v1,t_account a 
            where ( a.FNumber=v1.FNumber or v1.FNumber like a.FNumber+'.%')
                and a.FNumber not in ( select FNumber from #AcctBal2569457n)  And a.FLevel<=4 And  (a.FGroupID<600 or a.FGroupID>=700)

UPDATE #AcctBal2569457n
    Set  FDebit=t.FDebit+e.FDebit , FDebitLocal=t.FDebitLocal+e.FDebitLocal ,
    FCredit=t.FCredit+e.FCredit, FCreditLocal=t.FCreditLocal+e.FCreditLocal ,
    FYtdDebit=t.FYtdDebit+e.FDebit , FYtdDebitLocal=t.FYtdDebitLocal+e.FDebitLocal ,
    FYtdCredit=t.FYtdCredit+e.FCredit , FYtdCreditLocal=t.FYtdCreditLocal+e.FCreditLocal ,
    FEndBalanceFor=(t.FEndBalanceFor+ e.FDebit - e.FCredit ) , FEndBalanceLocal=(t.FEndBalanceLocal+e.FDebitLocal - e.FCreditLocal) 
From #AcctBal2569457n t
    Inner join (
        Select b.FNumber, b.FAccountID, b.FRootID,SUM(FDebit) FDebit,SUM(FDebitLocal) FDebitLocal,
        SUM(FCredit) FCredit,SUM(FCreditLocal) FCreditLocal 
            from AIS20090905160608..t_Account b
            inner join (
                Select a.FNumber,a.FAccountID,a.FRootID,
                Sum(e.FDC*e.FAmountFor) FDebit,Sum(e.FDC*e.FAmount) FDebitLocal ,
                Sum((1-e.FDC)*e.FAmountFor) FCredit  ,Sum((1-e.FDC)*e.FAmount) FCreditLocal 
                From AIS20090905160608..t_Voucher v,AIS20090905160608..t_VoucherEntry e,AIS20090905160608..t_Account a 
                Where v.FVoucherID=e.FVoucherID And e.FAccountID=a.FAccountID And v.FPosted=0 
                    And ( FYear*100+FPeriod between @MyYear*100+@MyMon and @MyYear*100+@MyMon)  And e.FCurrencyID=1
                    And  (a.FGroupID<600 or a.FGroupID>=700) 
                    Group By a.FNumber,a.FAccountID,a.FRootID
                    ) t on (t.FNumber like b.FNumber+'.%' or t.FNumber = b.FNumber)
                  group by b.FNumber, b.FAccountID, b.FRootID
                 ) e  on t.FRootID=e.FRootID and t.FNumber =e.FNumber  And t.FDetailID=0

Insert Into #AcctBal2569457n
    ( FBalanceTotal,FAccountID,FNumber ,FName,FLevel,FDC,FDetail,FParentID,FRootID,FDetailID,
    FBeginBalanceFor,FBeginBalanceLocal,FEndBalanceFor,FEndBalanceLocal, FDebit, FDebitLocal,
    FCredit, FCreditLocal, FYtdDebit,FYtdDebitLocal, FYtdCredit, FYtdCreditLocal, FBeginDebit,
    FBeginDebitLocal, FBeginCredit, FBeginCreditLocal, FEndDebit,FEndDebitLocal, FEndCredit,
    FEndCreditLocal,账套) 
Select 0 FBalanceTotal,v1.FAccountID,a.FNumber ,a.FName,a.FLevel,a.FDC,a.FDetail,a.FParentID,a.FRootID,
    v1.FDetailID,0 FBeginBalanceFor,0 FBeginBalanceLocal,0 FEndBalanceFor,0 FEndBalanceLocal,0 FDebit,
    0 FDebitLocal,0 FCredit,0 FCreditLocal,0 FYtdDebit,0 FYtdDebitLocal,0 FYtdCredit, 0 FYtdCreditLocal,
    0 FBeginDebit,0 FBeginDebitLocal,0 FBeginCredit,0 FBeginCreditLocal,0 FEndDebit,0 FEndDebitLocal,0 FEndCredit,
    0 FEndCreditLocal,'公司名称' 
    From  (
        Select  Distinct e.FAccountID,e.FDetailID  
            From AIS20090905160608..t_Voucher v,AIS20090905160608..t_VoucherEntry e,AIS20090905160608..t_Account a
                Where v.FVoucherID=e.FVoucherID And e.FAccountID=a.FAccountID And v.FPosted=0
                and ( a.FNumber like '2202%'  or a.FNumber like '2241%'  or a.FNumber like '2221%')
                And e.FDetailID>0  And ( FYear*100+FPeriod between @MyYear*100+@MyMon and @MyYear*100+@MyMon) 
                And e.FCurrencyID=1 And  (a.FGroupID<600 or a.FGroupID>=700)  ) v1,AIS20090905160608..t_Account a
          Where v1.FAccountID=a.FAccountID
        And convert(varchar,v1.FAccountid) +','+ convert(varchar, v1.FDetailID) Not In 
        (Select  convert(varchar,FAccountid) +','+ convert(varchar, FDetailID)
            From AIS20090905160608..t_Balance Where FDetailID>0 And FCurrencyID=1 And ( FYear*100+FPeriod =@MyYear*100+@MyMon)  )

UPDATE #AcctBal2569457n
    Set  FDebit=FDebit+t1.FDebit1, FDebitLocal=FDebitLocal+t1.FDebitLocal1, FCredit=FCredit+t1.FCredit1,
    FCreditLocal=FCreditLocal+t1.FCreditLocal1, FYtdDebit=FYtdDebit+t1.FDebit1, FYtdDebitLocal=FYtdDebitLocal+t1.FDebitLocal1,
    FYtdCredit=FYtdCredit+t1.FCredit1, FYtdCreditLocal=FYtdCreditLocal+t1.FCreditLocal1,
    FEndBalanceFor=(FEndBalanceFor+t1.FDebit1 -t1.FCredit1) , FEndBalanceLocal=(FEndBalanceLocal+t1.FDebitLocal1 -t1.FCreditLocal1) 
  From (
    Select d1,sum(FDebit1) FDebit1,sum(FCredit1) FCredit1,sum(FDebitLocal1) FDebitLocal1,sum(FCreditLocal1) FCreditLocal1 
        From (Select convert(varchar,a.FAccountid) +','+ convert(varchar, e.FDetailID) d1,
            e.FDC*e.FAmountFor FDebit1,e.FDC*e.FAmount FDebitLocal1,(1-e.FDC)*e.FAmountFor FCredit1,(1-e.FDC)*e.FAmount FCreditLocal1
                From AIS20090905160608..t_Voucher v,AIS20090905160608..t_VoucherEntry e, AIS20090905160608..t_Account a
                    Where v.FVoucherID=e.FVoucherID And e.FAccountID=a.FAccountID And v.FPosted=0
                        And e.FDetailID>0 And ( FYear*100+FPeriod between @MyYear*100+@MyMon and @MyYear*100+@MyMon)  And e.FCurrencyID=1
                        And  (a.FGroupID<600 or a.FGroupID>=700)  )  t2 group by d1 )  t1
                     Where convert(varchar,FAccountid) +','+ convert(varchar, FDetailID)=d1

UPDATE #AcctBal2569457n
    Set  FEndDebit=ISNULL(CASE WHEN FEndBalanceFor> 0 THEN FEndBalanceFor END,0) , 
    FEndDebitLocal=ISNULL(CASE WHEN FEndBalanceLocal> 0 THEN FEndBalanceLocal END,0) , 
    FEndCredit=ISNULL(CASE WHEN FEndBalanceFor < 0 THEN -FEndBalanceFor END,0), 
    FEndCreditLocal=ISNULL(CASE WHEN FEndBalanceLocal < 0 THEN -FEndBalanceLocal END,0)

delete b from #AcctBal2569457n b
        where b.FBeginCredit=0 and b.FBeginDebit=0 and FBeginCreditLocal=0
            and b.FBeginDebitLocal=0 and b.FDebit=0 and b.FCredit=0 and b.FDebitLocal=0
            and b.FCreditLocal=0 and b.FYtdDebit=0 and b.FYtdCredit=0 and b.FYtdDebitLocal=0
            and b.FYtdCreditLocal=0 and b.FEndBalanceFor=0 and b.FEndBalanceLocal=0

UPDATE #AcctBal2569457n SET  FName=Space(Flevel*2-2) + FName  where len(isnull(FNumber,'')) >0 and FDetailID=0 

Alter Table #AcctBal2569457n Add FItemNumber nvarchar(200) null


---exec sp_executesql N'Select FValue From t_SystemProfile Where FCategory = @P1 And FKey = @P2', N'@P1 varchar(30),@P2 varchar(30)', 'GL', 'LdgViewItemNumber'

Select t1.FDetailID,convert(nvarchar(800), '') FName,convert(nvarchar(800), '') FNumber Into #MultiItem2569457n
    From AIS20090905160608..t_ItemDetail as t1
    Inner join  ( Select DistInct FDetailID From #AcctBal2569457n Where FDetailID >0) as t2 On t1.FDetailID=t2.FDetailID   Where t1.FDetailID>0

---Select distinct c.FITEMCLASSID,c.Fnumber from t_Itemclass c, t_Itemdetailv v Where c.FItemClassID=v.FItemClassID and c.FType=1 and c.FItemclassID>0 order by c.Fnumber

/*应收账款**/
---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f1 ) t  Where FDetailID=t.F1

/**部门**/
---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f2 ) t  Where FDetailID=t.F1

/**职员类别**/
Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    From (Select  FDetailID F1,  FName FN,FNumber FNum From AIS20090905160608..t_Itemdetail a, AIS20090905160608..t_item b where b.FitemID>0 And b.FitemID=a.f3 ) t  Where FDetailID=t.F1

/**物料类别**/
Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    From (Select  FDetailID F1,  FName FN,FNumber FNum From AIS20090905160608..t_Itemdetail a, AIS20090905160608..t_item b where b.FitemID>0 And b.FitemID=a.f4 ) t  Where FDetailID=t.F1

/**应付账款**/
Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    From (Select  FDetailID F1,  FName FN,FNumber FNum From AIS20090905160608..t_Itemdetail a, AIS20090905160608..t_item b where b.FitemID>0 And b.FitemID=a.f8 ) t  Where FDetailID=t.F1

/**
Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f10 ) t  Where FDetailID=t.F1
**/
---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f14 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800)  From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f2003 ) t  Where FDetailID=t.F1
---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800)  From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f2014 ) t  Where FDetailID=t.F1

 

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f2024 ) t  Where FDetailID=t.F1

 

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3001 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3002 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3003 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3004 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3005 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3006 ) t  Where FDetailID=t.F1

Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    From (Select  FDetailID F1,  FName FN,FNumber FNum From AIS20090905160608..t_Itemdetail a, AIS20090905160608..t_item b where b.FitemID>0 And b.FitemID=a.f3007 ) t  Where FDetailID=t.F1

---Update #MultiItem2569457n Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800) 
    ---From (Select  FDetailID F1,  FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3008 ) t  Where FDetailID=t.F1

Update #MultiItem2569457n Set FName =STUFF(FName, 1, 1, ''),FNumber=STUFF(FNumber, 1, 1, '')

UPDATE #AcctBal2569457n Set  FName=Space(Flevel*2-2 +4) + t1.n1  ,FItemNumber = left(t1.FNumber,200) 
    From (Select FDetailID d1 ,FName  n1,FNumber From (Select * From #MultiItem2569457n ) t2  ) t1 Where FDetailID>0 AND FDetailID=t1.d1

UPDATE #AcctBal2569457n
    Set FBeginDebit=case when fdc=1 then FBeginDebit-FBeginCredit else 0 end,
    FBeginDebitLocal=case when fdc=1 then FBeginDebitLocal-FBeginCreditLocal else 0 end,
    FBeginCredit=case when fdc=1 then 0 else FBeginCredit -FBeginDebit end,
    FBeginCreditLocal=case when fdc=1 then 0 else FBeginCreditLocal -FBeginDebitLocal end,
    FEndDebit=case when fdc=1 then FEndDebit-FEndCredit else 0 end,
    FEndDebitLocal=case when fdc=1 then FEndDebitLocal-FEndCreditLocal else 0 end,
    FEndCredit=case when fdc=1 then 0 else FEndCredit -FEndDebit end,
    FEndCreditLocal=case when fdc=1 then 0 else FEndCreditLocal -FEndDebitLocal end where FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FBeginDebit=-FBeginCredit,FBeginCredit=0 where FBeginCredit<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FBeginDebitLocal=-FBegincreditLocal ,FBeginCreditLocal=0 where fBegincreditlocal<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FBeginCredit=-FBeginDebit,FBeginDebit=0 where FBeginDebit<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FBeginCreditLocal=-FBegindeBitLocal,FBeginDebitLocal=0 where FBegindebitlocal<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FEndDebit=-FEndCredit,FendCredit=0 where fendcredit<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FEndDebitlocal=-FEndCreditLocal ,FEndCreditLocal=0 where FEndCreditlocal<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FEndCredit=-FEndDebit,FEndDebit=0 where FEndDebit<0 and FBalanceTotal<>-1

UPDATE #AcctBal2569457n Set FEndCreditlocal=-FEndDebitlocal,FEndDebitLocal=0 where FEndDebitlocal<0 and FBalanceTotal<>-1

insert into #AcctBal2569457n
    (FBalanceTotal,FAccountID,FNumber  ,FName,FLevel,FDC,FDetail,FParentID,FRootID,FDetailID,FBeginBalanceFor,
    FBeginBalanceLocal,FEndBalanceFor,FEndBalanceLocal,FDebit, FDebitLocal,FCredit, FCreditLocal,FYtdDebit,
    FYtdDebitLocal,FYtdCredit,FYtdCreditLocal,FBeginDebit,FBeginDebitLocal,FBeginCredit,FBeginCreditLocal,
    FEndDebit,FEndDebitLocal, FEndCredit,FEndCreditLocal,账套)
Select -1 FBalanceTotal,0 FAccountID,'ZZ' FNumber  ,'总计' FName,1 FLevel,1 FDC,1 FDetail,0 FParentID,0 FRootID,0 FDetailID, 0 FBeginBalanceFor,
     0 FBeginBalanceLocal, 0 FEndBalanceFor, 0 FEndBalanceLocal, ISNULL(sum(FDebit),0) FDebit,  ISNULL(sum(FDebitLocal),0) FDebitLocal,
     ISNULL(sum(FCredit),0) FCredit, ISNULL(sum(FCreditLocal),0) FCreditLocal, ISNULL(sum(FYtdDebit),0) FYtdDebit,
    ISNULL(sum(FYtdDebitLocal),0) FYtdDebitLocal, ISNULL(sum(FYtdCredit),0) FYtdCredit, ISNULL(sum(FYtdCreditLocal),0) FYtdCreditLocal, 
    ISNULL(Sum(FBeginDebit),0) FBeginDebit, ISNULL(Sum(FBeginDebitLocal),0) FBeginDebitLocal, ISNULL(Sum(FBeginCredit),0) FBeginCredit,
    ISNULL(Sum(FBeginCreditLocal),0) FBeginCreditLocal, ISNULL(Sum(FEndDebit),0) FEndDebit, ISNULL(Sum(FEndDebitLocal),0) FEndDebitLocal,
    ISNULL(Sum(FEndCredit),0) FEndCredit, ISNULL(Sum(FEndCreditLocal),0) FEndCreditLocal,'公司名称'  
   From #AcctBal2569457n b  
    INNER JOIN AIS20090905160608..t_Account a ON a.FAccountID=b.FAccountID 
   Where a.FDetail =1 and b.FDetailID =0  AND b.FBalanceTotal=0


insert Into #tAllBal
 
Select b.* ,FDetailIDOrder =( CASE b.FDetailID WHEN 0 THEN 0 ELSE 1 END)  From #AcctBal2569457n b  WHERE 1=1 AND b.FLevel<= 4
  Order By b.FNumber,FDetailIDOrder,b.FName

 

Drop table #AcctBal2569457n
drop table #MultiItem2569457n

 

 

/**公司名称代码结束**/
 

sumeing
关注
专栏目录
SAP FICO 科目余额查询表开发说明书（包括测试样例、程序代码仅作参考，不保证一定可以运行)
我好像病了的博客
12-27 634
SAP FICO 科目余额查询表开发说明书（包括开发源代码、测试样例)
sql 凭证明细表 科目余额表_sql查询金蝶科目余额表个期间的数据
weixin_35964207的博客
01-14 1484
selectnull as QYDM,null as SBNF,null as SBYF,本期发生_本年累计_科目余额表.会计年度 as会计年度,本期发生_本年累计_科目余额表.会计期间 as 会计期间,本期发生_本年累计_科目余额表.借贷方向_1借_负1贷as 借贷方向_1借_负1贷,本期发生_本年累计_科目余额表.科目级次 as科目级次,本期发生_本年累计_科目余额表.明细科目_1是_0否 a...
科目余额表（包括明细）
04-16
最近准备着手更新我的账务处理系统，论坛里关于新会计准则下会计科目表和财务报表的介绍已很多，但关于明细科目设置的介绍很少，特将新会计准则科目余额表(含明细科目)发上来与大家共享！将科目余额表中的总账余额拷到资产负债表和损益表即可生成财务报表！
java 中查询余额怎么写_查询余额示例代码
weixin_39950824的博客
02-28 379
package api.jisuapi.sms;import api.util.HttpUtil;import net.sf.json.JSONArray;import net.sf.json.JSONObject;public class Balance {public static final String APPKEY = "your_appkey_here";// 你的appkeypubl...
科目余额表有辅助核算的科目查询不到辅助
martian6125 's blog!
11-02 3688
方法1：登陆当前公司，打开总账-期末-结账，按ctrl+alt+a，将弹出的对话框左边内容选到右边，点确定，如果还不行，就用方法2. 方法2：登陆到集团，打开总账-期末-结账，按ctrl+alt+F5，在弹出的框里点确定，完了重启中间件。...
发一个刚帮同事写的用友U8科目余额发生额取数代码
weixin_33727510的博客
08-13 352
declare @begindate as datetime declare @enddate as datetime declare @period as int --修改期间开始时间 set @begindate='2010-7-7' --修改期间终止时间 set @enddate='2010-7-8' --修改会计期间 set @period=7 --...
用友U8科目余额表存储过程
05-14
用友U8科目余额表存储过程
科目余额表批量显示（ABAP源码）
最新发布
nopicture的博客
05-08 278
最近做的科目余额表，实现交易货币，集团货币，本位币一个页面展示。
SAP导出科目余额表和凭证表操作说明及截图.pdf
09-24
在SAP系统中，FAGLL0３和FBL３N命令代码用于导出科目余额表，FAVLN0３和FBVLN３N命令代码用于导出凭证表。这些命令代码可以根据需要进行修改和组合，以满足不同的财务报表需求。 四、导出结果分析 在SAP系统中导出...
关于 sql 取值 统计某个科目 余额表余额问题
MZ199290的博客
04-18 943
---按照 公司名称分组， 会计期间分组， 每个分组数据集 建立一个rownidex 便于后续取出分组后的第一条 select unit.FNAME_l2, period.fnumber , balance.FENDBALANCELOCAL, ROW_NUMBER() over(partition by unit.FNAME_l2 order by period.fnumber desc) as newIndex from T_GL_AccountBalanc..
深入解析AAVE智能合约:取款
WongSSH的博客
12-29 792
本文主要介绍AAVE V3合约中的取款withdraw函数。AAVE交互指南。
在JavaScript中利用代码来实现ATM的效果，具有存钱，取钱，查看余额，退出功能
小杨的博客
08-02 1351
模拟简易的ATM机，模拟银行取钱存钱。 题目描述有四个功能： 存钱 取钱 显示余额 退出 注意： 默认里面本身有100块钱。 如果存钱，就用输入的钱数加上先前的钱，然后弹出余额。 如果取钱，就减去取的钱，然后.... 查询 2. 取钱 3. 存钱 4. 转账 5. 修改密码 6. 返回上一级')-0; switch(num) { case1: getMoney();//查询 break; case2: takeMoney();//取钱 break......
sql 凭证明细表 科目余额表_科目余额SQL
weixin_39777967的博客
12-24 1315
selectgb.period_year会计年度,gb.period_name会计期间,gcck.concatenated_segments科目代码,gl_flexfields_pkg.get_description_sql(gcck.chart_of_accounts_id,1,gcck.segment1)公司,gl_flexfields_pkg.get_description_sql(...
金蝶EAS，后台代码查询科目余额，SQL查询科目余额
金蝶EAS开发专栏
06-06 5627
以下代码演示了如何从EAS系统查询科目余额，用于后台业务逻辑（app目录下的ControllerBean）。 /** * 查询科目余额，封装数据，返回值映射中，键：科目ID；值：科目余额队列 * @param ctx 上下文 * @param easOrgId 财务组织 * @param periodId 会计期间 */ @SuppressWarnings({ "unchecked",
SAP财务模块中，科目余额表中的数据存在数据库的哪张表中？
热门推荐
少年休闲海
09-19 1万+
科目余额相关表：FAGLFLEXT,GLT0； 科目余额相关函数：BAPI_GL_GETGLACCPERIODBALANCES,FAGL_GET_GLT0 建议：直接调用函数BAPI_GL_GETGLACCPERIODBALANCES，读取该函数返回的TABLE ACCOUNT_BALANCES，列BALANCE即为你要的科目余额，值和年度期间有关系。
关于非现场审计软件的一些介绍（ACL、IEDA、Teammate）
dechen6073的博客
12-15 1069
http://group.vsharing.com/Article.aspx?aid=661512 IDEA是由caseware开发的数据分析软件。caseware的网址如下：http://www.caseware.com/fsh.asp I DEA 是个数据分析软件，是个基于审计业务的数据分析工具。它是一个由审计员、会计、调查员及 IT 人员使用的，基于计算机的文件查询工具。它有多...
财务类程序使用的表（我的第一篇）
ABAP这几年
06-09 1151
BKPF,"会计核算凭证标题 BSEG,"会计核算凭证段 T003T,"凭证类型文本 SKAT,"总帐科目主记录（科目表：说明） KNA1,"客户主文件的一般数据 CSKT,"成本中心说明文本 TFKBT,"功能范围的名称 CEPCT,  "利润中心主数据文本 T053S,  "付款原因代码文本 BSIS "会计核算，未清明细
如何从SAP的多维度科目余额表中导出带有超链接的数据透视表格？
06-13
在SAP多维度科目余额表中导出带有超链接的数据透视表格，您可以按照以下步骤进行操作： 1. 首先，在SAP系统的多维度科目余额表中，选择您需要的科目范围、公司代码和会计年度等筛选条件，然后运行报表。 2. 在报表运行结果页面，您可以右键单击所需的数据透视表格，然后选择“导出”选项。 3. 在导出选项中，选择带有超链接的XML格式文件作为导出格式，并确保选择“包括超链接”选项。 4. 单击“导出”按钮，将带有超链接的数据透视表格导出到本地计算机中。 5. 打开导出的XML格式文件，可以看到所有的数据透视表格和超链接信息。您可以通过单击超链接查看相关的详细信息。 希望这些步骤可以帮助您成功导出带有超链接的数据透视表格。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值