K3账龄分析表SQL

最新推荐文章于 2023-12-04 17:44:14 发布
最新推荐文章于 2023-12-04 17:44:14 发布
阅读量3.9k 收藏 4
点赞数 2
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/angel20082008/article/details/51555437
版权

Set NoCount On
 SET ANSI_WARNINGS OFF
 Create Table #Happen2(
        FItemID int Null,
        FStockID int Null,
        FBatchNo NVARCHAR(200),
        FQty decimal(28,10) Null,
        FCUUnitQty decimal(28,10) Null,
        FQty1 Decimal(28,10),
        FCUUnitQty1 Decimal(28,10),
        FAmount1 Decimal(28,10),
        FQty2 Decimal(28,10),
        FCUUnitQty2 Decimal(28,10),
        FAmount2 Decimal(28,10),
        FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
 Create Table #Happen(
        FItemID int Null,
        FStockID int Null,
        FBatchNo NVARCHAR(200),
        FQty decimal(28,10) Null,
        FCUUnitQty decimal(28,10) Null,
        FQty1 Decimal(28,10),
        FCUUnitQty1 Decimal(28,10),
        FAmount1 Decimal(28,10),
        FQty2 Decimal(28,10),
        FCUUnitQty2 Decimal(28,10),
        FAmount2 Decimal(28,10),
        FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
 Create Table #Happen1(
        FItemID int Null,
        FStockID int Null,
        FBatchNo NVARCHAR(200),
        FQty decimal(28,10) Null,
        FCUUnitQty decimal(28,10) Null,
FQty1 Decimal(28,10),
FCUUnitQty1 Decimal(28,10),
FQty2 Decimal(28,10),
FCUUnitQty2 Decimal(28,10),
 FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) )
  Insert Into #Happen1
  Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty1,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty1,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty2,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty2,
1 ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
 FROM ICStockBill t11 
 INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID 
     WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
         AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
         (t11.FCheckerID IS NULL OR t11.FCheckerID=0)
         AND t11.FTrantype IN(1,2,5,10,40,41) 
     ORDER BY t11.FDate DESC),0),0,0
 From t_ICItem t1
Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 ))
Join ICStockBillEntry t6 On t5.FInterID=t6.FInterID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = (case when t5.ftrantype=24 then t6.FSCStockID else t6.FDCStockID end)
 Where t1.FItemID = t6.FItemID
 And ((t5.FTrantype In (1,2,5,10,40) And t5.FRob =1) Or (t5.FTrantype In(21,24,28,29) And t5.FRob=-1))
 AND (NOT (t5.FTrantype In (1) AND t5.FPOMode = 36681 ))
 And t2.FTypeID NOT IN (504)


  Insert Into #Happen1
  Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t6.FBegQty Else 0 End)  As FQty1,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty1,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t6.FBegQty Else 0 End)  As FQty2,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty2,
1  ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
 FROM ICStockBill t11 
 INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID 
     WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
         AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
         (t11.FCheckerID IS NULL OR t11.FCheckerID=0)
         AND t11.FTrantype IN(1,2,5,10,40,41) 
     ORDER BY t11.FDate DESC),0),0,0

From t_ICItem t1
Join ICInvInitial t6 On t1.FItemID = t6.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t6.FStockID
 Where 1=1  And t2.FTypeID NOT IN (504)


CREATE TABLE #InventoryHanppen
(FItemID    INT           NOT NULL,
 FStockID   INT           NULL,
 FBatchNo   Varchar(255)  NULL,
 FQty     DECIMAL(28,10)  NOT NULL,
 FAmount  DECIMAL(28,10)  NOT NULL )
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal
FROM t_ICItem t1
INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID
LEFT  JOIN t_Stock  t2 ON t2.FItemID = u1.FStockID
WHERE u1.FYear=2015 AND u1.FPeriod=9 And t2.FTypeID NOT IN (504)

INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,t2.FItemID,u1.FBatchNo, CASE WHEN v1.FTranType IN (1,2,5,10,40,41,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FQty ELSE -1 * u1.FQty END,
CASE WHEN v1.FTranType IN (1,2,5,10,40,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FAmount WHEN v1.FTranType=41 THEN u1.FAmtRef ELSE -1 * u1.FAmount END
FROM t_ICItem t1
INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID
INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID
LEFT  JOIN t_Stock  t2 ON ((v1.FTrantype=24 AND u1.FSCStockID=t2.FItemID) OR (v1.FTranType IN (1,2,5,10,21,41,28,29,43,40,100,101,102) AND u1.FDCStockID=t2.FItemID ))
WHERE v1.FDate>='2015/09/01' AND v1.FDate<='2015/09/21' And t2.FTypeID NOT IN (504)

AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102) AND (NOT (v1.FTrantype In (1) AND isnull(v1.FPOMode,0) = 36681 ))
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,t2.FItemID,u1.FBatchNo,-1 * u1.FQty ,-1 * u1.FAmount
FROM t_ICItem t1
INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID
INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID
LEFT  JOIN t_Stock  t2 ON  u1.FSCStockID=t2.FItemID
WHERE v1.FDate>='2015/09/01' AND v1.FDate<='2015/09/21' And t2.FTypeID NOT IN (504)

AND v1.Ftrantype=41
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORY
FROM #InventoryHanppen
GROUP BY FItemID,FStockID,FBatchNo
DROP TABLE #InventoryHanppen
DELETE FROM #INVENTORY WHERE FQty<=0
 Insert Into #Happen1
Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,CAST(t3.FQTY AS DECIMAL(28,10))/t7.FCoefficient As FCUUnitQty,0,0,0,0,1  ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
 FROM ICStockBill t11 
 INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID 
     WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
         AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
         (t11.FCheckerID IS NULL OR t11.FCheckerID=0)
         AND t11.FTrantype IN(1,2,5,10,40,41) 
     ORDER BY t11.FDate DESC),0),0,0

From t_ICItem t1
 Join #INVENTORY t3 On t1.FItemID = t3.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t3.FStockID
Where 1=1
 And t2.FTypeID NOT IN (504)


 Insert Into #HAPPEN2
Select t1.FITEMID,FStockID,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)
From #HAPPEN1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID 
GROUP By t1.FITEMID,t1.FBatchNo,FStockID Update #Happen2 Set FQty1= FQty,FQty2=0
 Where  FQty-FQty1<0
 Update #Happen2 Set FQty2= FQty-FQty1
Update #Happen2 Set FAmount1=FPrice*FQty1
Update #Happen2 Set FAmount2=FPrice*FQty2

 Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0
 Where  FCUUnitQty-FCUUnitQty1<0
 Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1

 Insert Into #HAPPEN
Select t1.FITEMID,t1.FStockID,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,Sum(FAmount1) As FAmount1,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,Sum(FAmount2) As FAmount2,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)
From #HAPPEN2 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID
GROUP By t1.FITEMID,t1.FBatchNo,t1.FStockID

HAVING (SUM(FQTY)>=0)
SET NOCOUNT ON
CREATE TABLE #ItemLevel(
 FNumber1 Varchar(355),
 FName1 Varchar(355),
 FNumber2 Varchar(355),
 FName2 Varchar(355),
 FNumber3 Varchar(355),
 FName3 Varchar(355),
 FItemID int,
 FNumber Varchar(355))

 INSERT INTO #ItemLevel SELECT 
 CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1)  END,
 '',
 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1)  END,
 '',
 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1)  END,
 '',
 FItemID,FNumber FROM t_Item
 WHERE FItemClassID=4
 AND FDetail=1  AND FItemID In (Select Distinct FItemID from #Happen )
 UPDATE t0 SET t0.FName1=t1.FName,t0.FName2=t2.FName,t0.FName3=t3.FName
  FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0
 left join t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0
 left join t_Item t3 On t0.FNumber3=t3.FNumber  AND t3.FItemClassID=4 AND t3.FDetail=0

CREATE TABLE #DATA(
FName1 Varchar(355) Null,
FName2 Varchar(355) Null,
FName3 Varchar(355) Null,

FStockName Varchar(355) Null,     FNumber  Varchar(355) null,
     FShortNumber  Varchar(355) null,
     FName  Varchar(355) null,
     FModel  Varchar(355) null,
     FUnitName  Varchar(355) null,
     FCUUnitName  Varchar(355) null,
     FQtyDecimal smallint null,
     FPriceDecimal smallint null,
     FQty Decimal(28,10) Null,
     FCUUnitQty Decimal(28,10) Null,
     FPrice Decimal(28,10) NULL,
     FCUPrice Decimal(28,10) Null,
     FAmount Decimal(28,10) Null,
 FQty1 Decimal(28,10),
 FCUUnitQty1 Decimal(28,10),
 FAmount1 Decimal(28,10),
 FQty2 Decimal(28,10),
 FCUUnitQty2 Decimal(28,10),
 FAmount2 Decimal(28,10),
     FSumSort smallint not null Default(0),
Flevel0 Decimal(10,3),
Flevel1 Decimal(10,3),
Flevel2 Decimal(10,3),
Flevel3 Decimal(10,3),
Flevel4 Decimal(10,3),
Flevel5 Decimal(10,3),
Flevel6 Decimal(10,3),

     FID int IDENTITY)
INSERT INTO #DATA
SELECT tt1.FName1,tt1.FName2,tt1.FName3,t2.FName,t1.FNumber,'','','','','',MAX(t1.FQtyDecimal),MAX(t1.FPriceDecimal),Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1),
 SUM(FCUUnitQty1),
 SUM(FAmount1),
 SUM(FQty2),
 SUM(FCUUnitQty2),
 SUM(FAmount2),
CASE   WHEN   Grouping(tt1.FName1)=1 THEN 106
  WHEN   Grouping(tt1.FName2)=1 THEN 107
  WHEN   Grouping(tt1.FName3)=1 THEN 108
  WHEN   Grouping(t2.FName)=1 THEN  109  WHEN   Grouping(t1.FNumber)=1 THEN 110  ELSE   0 END
,0 ,0 ,0 ,0 ,0 ,0 ,0  FROM #Happen v2
 Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
 Left Join t_Stock t2 On v2.FStockID=t2.FItemID
Inner Join #ItemLevel tt1

 On t1.FItemID=tt1.FItemID Where 1=1


  Group By tt1.FName1,tt1.FName2,tt1.FName3,t2.FName,t1.FNumber WITH ROLLUP Having Sum(FQty)>0 

 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal  From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4  Where t1.FNumber=t2.FNumber  And t2.FUnitGroupID=t3.FUnitGroupID  And t2.FStoreUnitID=t4.FMeasureUnitID  And t3.FStandard=1


SELECT IDENTITY(INT,1,1) AS FLevel0,FNAME1
 INTO #LEVEL0 FROM #DATA
Where FSumSort=107


UPDATE a3 SET a3.FLevel0=lv.FLevel0
FROM #DATA a3
INNER JOIN #LEVEL0 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL0
 Update  #DATA set FLevel0=(Select max(ISNULL(FLevel0,0))+1 From #DATA) Where 1=1 AND FSumSort=106

SELECT IDENTITY(INT,1,1) AS FLevel1,FNAME1 , FNAME2
 INTO #LEVEL1 FROM #DATA
Where FSumSort=108


ORDER BY  FLEVEL0
UPDATE a3 SET a3.FLevel1=lv.FLevel1
FROM #DATA a3
INNER JOIN #LEVEL1 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL1

SELECT IDENTITY(INT,1,1) AS FLevel2,FNAME1 ,FNAME2 , FNAME3
 INTO #LEVEL2 FROM #DATA
Where FSumSort=109


ORDER BY  FLEVEL0 ,  FLEVEL1
UPDATE a3 SET a3.FLevel2=lv.FLevel2
FROM #DATA a3
INNER JOIN #LEVEL2 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0) AND ISNULL(lv.FNAME3,0)=ISNULL(a3.FNAME3,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL2

SELECT IDENTITY(INT,1,1) AS FLevel3,FNAME1 ,FNAME2 ,FNAME3 , FSTOCKNAME
 INTO #LEVEL3 FROM #DATA
Where FSumSort=110


ORDER BY  FLEVEL0 ,  FLEVEL1,  FLEVEL2
UPDATE a3 SET a3.FLevel3=lv.FLevel3
FROM #DATA a3
INNER JOIN #LEVEL3 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0) AND ISNULL(lv.FNAME3,0)=ISNULL(a3.FNAME3,0) AND ISNULL(lv.FSTOCKNAME,0)=ISNULL(a3.FSTOCKNAME,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL3

 Update #DATA Set FLevel0=FLevel0+0.9   Where FSumSort=107
 Update #DATA Set FLevel1=FLevel1+0.9   Where FSumSort=108
 Update #DATA Set FLevel2=FLevel2+0.9   Where FSumSort=109
 Update #DATA Set FLevel3=FLevel3+0.9   Where FSumSort=110
Update #Data Set  FName1=isnull(FName1,'')+'(小计)'  WHERE FSumSort=107
Update #Data Set  FName2=isnull(FName2,'')+'(小计)'  WHERE FSumSort=108
Update #Data Set  FName3=isnull(FName3,'')+'(小计)'  WHERE FSumSort=109
Update #Data Set FStockName=Rtrim(ISNULL(FStockName,''))+'(小计)' WHERE FSumSort=110
Update #Data Set FName1='合计' WHERE FSumSort=106
Update #Data Set FSumSort=101   WHERE FSumSort=106

SELECT * FROM #DATA 
 Order By  FLevel0,  FLevel1,  FLevel2,  FLevel3
DROP TABLE #DATA 
DROP TABLE #ItemLevel
 Drop Table #Happen
 Drop Table #Happen1
 Drop Table #Happen2
 DROP TABLE #INVENTORY

angel20082008
关注
k3坏账处理之账龄分析法
07-20
k3坏账处理之账龄分析法
金蝶云星空库存账龄分析表(SQL)
01-16
金蝶云星空库存账龄分析表(SQL)
金蝶K3 SQL报表系列-库存账龄分析
hzfw2008的博客
08-13 3684
金蝶K3 SQL报表系列-库存账龄分析
账龄分析-基于SQL
最新发布
Axlsss的博客
12-04 1324
账龄分析-基于SQL
金蝶K3库存账龄分析表（优化）
mamengna的博客
12-28 600
Set NoCount On SET ANSI_WARNINGS OFF Create Table #Happen2( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), ...
K3 WISE-更新采购价格管理表的审核SQL语句.zip
07-05
K3 WISE-更新采购价格管理表的审核SQL语句，方便大家导入完采购价格管理表后，可以批量更新审核状态。
金蝶K3CLOUD 常用SQL
03-10
金蝶K3CLOUD 常用SQL 金蝶K3CLOUD 是一个基于云计算的企业管理软件，提供了一个完整的企业资源规划（ERP）解决方案。以下是金蝶K3CLOUD 中常用的SQL语句，涵盖了物料管理、组织管理、元数据管理、部门管理、单据...
应收账龄分析
12-19
K3应收账龄分析，总账取数，不启用应收账款系统
K3 WISE-bom表维护批量更新审核和使用SQL语句.zip
07-05
K3 WISE-bom表维护批量更新审核和使用SQL语句,方便大家维护bom时直接用语句刷。
用EXCEL自动编制往来账款账龄分析表
05-06
用EXCEL自动编制往来账款账龄分析表 小企业用还是可以的
应收款项及账龄分析Excel图表模板
11-25
这是一套应收款项及账龄分析Excel图表模板，喜欢的人都来下载吧。该文档为应收款项及账龄分析Excel图表模板，是一份很不错的参考资料，具有较高参考价值，感兴趣的可以下载看看
应收应付账龄计算器账龄公式.xlsx.rar
09-04
软件介绍: 应收应付账龄计算器 评分:     通过债务人、期初余额、期末余额、发生额、账龄期间，自动计算应收及应付账龄，含详细说明。　　应收款项自动分账龄公式用到3个函数：if函数、and函数、min函数适用条件：完整会计年度审计　　公式解释：　　首先，上述模拟的应收款明细账中，B列-I列数据是可以直接从客户提供的资料中获取的，O列-S列是可以从上年的底稿中获取的。　　应收债务人甲本年度与被审计单位可能的交易总共有20种情况已一一列出。　　为了确定账龄在1年以内的金额，设计上述公式的思路是先对借方发生额和贷方发生额进行判断，在同时满足借方有发生且贷方发生额小于或等于期初余额的情况下，账龄在1年以内的金额就是本年借方发生额。如果任意条件不满足，则继续判断是否同时满足借方有发生且贷方发生额大于期初余额，如果是，则账龄在1年以内的。　　金额就是期末余额，在不存在审计调整的情况下，也即是审定金额。如果上述两次判断的结果都是否，则说明本期借方无发生额，账龄在1年以内的金额为0。　　账龄在1-2年和2-3年的金额，可以用min函数进行获取：　　账龄在1-2年的金额可以输入公式"=min(审定数-1年以下，上年审定数账龄在1年以内的金额)"    以此类推。　　账龄在3年以上的，可以从平衡关系中直接倒挤出来。　　理论上，本公式仅适用于不存在审计调整的账龄分析中，因为审计调整进来的金额应该具体情况具体分析。
金蝶 K3 库存账龄分析表 数据不一致
大猴子
07-17 3858
用户反馈某个物料在库存账龄分析表内的统计后存在超过1080天的数量，但实际该料只是在一年前导入，故不可能存在超过1080天的数据。 原因分析：用户没勾选包含调拨单的数据，报表统计时将调拨单排除在外。造成即时库存数据大于流水单据（不含调拨单）数据，多出来的部分系统将其归类到最后一项天数。 Set NoCount On SET ANSI_WARNINGS OFF Create Table ...
应付的帐龄分析SQL
知了学飞
12-31 592
selecthao.name公司名称,pha.segment1订单编号,to_char(pla.line_num)订单行编号,pv.vendor_name供应商名,pvs.vendor_site_code供应商地点,nvl(msib.se...
账龄分析表excel模板_老会计分享财务报表及EXCEL会计报表分析系统模板！收藏领取！...
weixin_39892481的博客
11-18 661
分享财务报表模板及EXCEL会计报表分析系统！财务报表模板包含模板内容：资产负债表、利润表、现金流量表、应收帐款明细及帐龄分析表、应付帐款明细及帐龄分析表、其他应收款明细及帐龄分析表、其他应付款明细及帐龄分析表、预收、预付帐款明细表、短期借款、长期借款明细表、实收资本明细表、资金周报表、收支月报表、短期投资明细表、长期投资明细表、固定资产明细表、无形资产、长期待摊费用明细表、收入结构明细表、投资收...
金蝶k3库存账龄分析报表（带参数）
hzfw2008的博客
12-14 6279
金蝶k3库存账龄分析报表（带参数）。可以根据需要查看特定账期的库存账龄。
K3 增加SQL报表，统计入库物料未出库列表（存储过程）
weixin_44819434的博客
12-04 597
仓库为统计吊滞情况，要求做一张报表，统计某月入库的物料，在未来一段时期内未出过库的列表。1、写存储过程，入库物料就统计起始月份当月入库物料。2、在BOS中新增直接SQL报表。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值