美文网首页
SBO 库存台帐自定义报表分享 [NGC毗翌咨询]SAP B1

SBO 库存台帐自定义报表分享 [NGC毗翌咨询]SAP B1

作者: BRIAN渣渣灰 | 来源:发表于2019-11-05 11:35 被阅读0次
S01.png

这是一个SAP Business One的库存台帐存储过程分享

USE [SBODEMOCN] --写入的数据库
GO
/****** Object:  StoredProcedure [dbo].[SBO_InventoryLedger ]    Script Date: 07/16/2014 10:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**-- =============================================
    Author:     <Brian.Ho>
    Create date:    <2019-11-05>
    Description:    <Thanks>
-- =============================================
--*/

/*  执行的语句
    Declare @Begintime datetime
    Declare @Endtime datetime
    --SELECT TOP 1 T0.[DocDueDate] FROM OINM T0 WHERE T0.[DocDueDate] > [%0]
    SET @Begintime = [%0]
    --SELECT TOP 1 T0.[DocDueDate] FROM OINM T0 WHERE T0.[DocDueDate] < [%1]
    SET @Endtime = [%1]
    exec SBO_InventoryLedger @Begintime, @Endtime;
*/
create procedure [dbo].[SBO_InventoryLedger] --建立存储过程
--ALTER PROCEDURE [dbo].[SBO_InventoryLedger] --修改存储过程  
    -- Add the parameters for the stored procedure here
        @Begintime DATETIME,
        @Endtime DATETIME
AS

BEGIN


SELECT
    SBO001.物料编码 ,SBO001.物料描述 ,SBO001.仓库名称 ,SBO001.物料组
    
    ,SUM(ISNULL(SBO002.期初数量,0)) AS N'期初数量' 
    ,SUM(ISNULL(SBO002.期初单价,0)) AS N'期初单价' 
    ,SUM(ISNULL(SBO002.期初总金额,0)) AS N'期初总金额'
    
    ,SUM(ISNULL(SBO003.本期入库数量,0)) AS N'本期入库数量' ,SUM(ISNULL(SBO003.本期入库单价,0)) AS N'本期入库单价' ,SUM(ISNULL(SBO003.本期入库总金额,0)) AS N'本期入库总金额'
    
    ,SUM(ISNULL(SBO004.本期出库数量,0)) AS N'本期出库数量' ,SUM(ISNULL(SBO004.本期出库单价,0)) AS N'本期出库单价' ,SUM(ISNULL(SBO004.本期出库总金额,0)) AS N'本期出库总金额'
    
    ,SUM(ISNULL(SBO005.本期重估总金额,0)) AS N'本期重估总金额'
    
    ,SUM(ISNULL(SBO002.期初数量,0))+SUM(ISNULL(SBO003.本期入库数量,0))+SUM(ISNULL(SBO004.本期出库数量,0)) AS N'期末数量'
    
    ,CASE WHEN SUM(ISNULL(SBO002.期初数量,0))+SUM(ISNULL(SBO003.本期入库数量,0))+SUM(ISNULL(SBO004.本期出库数量,0)) <> 0
        THEN (SUM(ISNULL(SBO002.期初总金额,0))+SUM(ISNULL(SBO003.本期入库总金额,0))+SUM(ISNULL(SBO004.本期出库总金额,0))) 
            / (SUM(ISNULL(SBO002.期初数量,0))+SUM(ISNULL(SBO003.本期入库数量,0))+SUM(ISNULL(SBO004.本期出库数量,0)))
        ELSE 0 END AS N'期末单价'
        
    ,SUM(ISNULL(SBO002.期初总金额,0))+SUM(ISNULL(SBO003.本期入库总金额,0))+SUM(ISNULL(SBO004.本期出库总金额,0)) AS N'期末总金额'
FROM 
    /**********↓↓↓↓↓基本信息↓↓↓↓↓**********/
            (
                SELECT 
                    T0.Itemcode AS N'物料编码',T0.ItemName AS N'物料描述'
                    ,T1.WhsCode AS N'仓库编码',T2.WhsName AS N'仓库名称'
                    ,T3.ItmsGrpCod AS N'物料组编码',T3.ItmsGrpNam AS N'物料组' 
                FROM OITM T0 
                    LEFT JOIN OITW T1 ON T0.ItemCode = T1.ItemCode 
                    LEFT JOIN OWHS T2 ON T1.WhsCode = T2.WhsCode
                    LEFT JOIN OITB T3 ON T0.ItmsGrpCod = T3.ItmsGrpCod
                
            ) SBO001 
    /**********↓↓↓↓↓期初信息↓↓↓↓↓**********/
    LEFT JOIN
            (
                SELECT 
                    T0.ItemCode AS N'物料编码' ,T0.Warehouse AS N'仓库编码'
                    --,SUM(ISNULL(T1.SUMInQty,0) - ISNULL(T2.SUMOutQty,0)) AS N'期初数量'
                    --,SUM(ISNULL((CASE WHEN T0.TransValue >=0 THEN T0.InQty ELSE T0.OutQty END),0)) AS N'期初数量'
                    ,SUM(ISNULL(T0.InQty,0) - ISNULL(T0.OutQty,0)) AS N'期初数量'
                    ,CASE WHEN SUM(ISNULL(T0.InQty,0) - ISNULL(T0.OutQty,0))  <> 0
                            THEN (ISNULL(T1.SUMInQtyTransValue,0) + ISNULL(T2.SUMOutQtyTransValue,0)) / SUM(ISNULL(T0.InQty,0) - ISNULL(T0.OutQty,0))  
                        ELSE 0 END AS N'期初单价'
                    ,(ISNULL(T1.SUMInQtyTransValue,0) + ISNULL(T2.SUMOutQtyTransValue,0)) AS N'期初总金额'
                FROM OINM T0 
                    LEFT JOIN 
                            (
                                SELECT 
                                    T0.ItemCode ,T0.Warehouse 
                                    ,SUM(ISNULL(T0.InQty,0)) AS 'SUMInQty'
                                    ,CASE WHEN SUM(ISNULL(T0.InQty,0)) <> 0
                                            THEN SUM(ISNULL(T0.TransValue,0)) / SUM(ISNULL(T0.InQty,0)) 
                                        ELSE 0 END AS 'InQtyPrice'
                                    ,SUM(ISNULL(T0.TransValue,0)) AS 'SUMInQtyTransValue'
                                FROM OINM T0 
                                WHERE T0.DocDate < @Begintime
                                    AND ISNULL(T0.TransValue,0) >= 0
                                GROUP BY T0.ItemCode ,T0.Warehouse
                            )T1 ON T0.ItemCode = T1.ItemCode AND T0.Warehouse = T1.Warehouse 
                    LEFT JOIN 
                            (
                                SELECT 
                                    T0.ItemCode ,T0.Warehouse 
                                    ,SUM(ISNULL(T0.OutQty,0)) AS 'SUMOutQty'
                                    ,CASE WHEN SUM(ISNULL(T0.OutQty,0)) <> 0
                                            THEN SUM(ISNULL(T0.TransValue,0)) / SUM(ISNULL(T0.OutQty,0)) 
                                        ELSE 0 END AS 'OutQtyPrice'
                                    ,SUM(ISNULL(T0.TransValue,0)) AS 'SUMOutQtyTransValue'
                                FROM OINM T0 
                                WHERE T0.DocDate < @Begintime
                                    AND ISNULL(T0.TransValue,0) < 0
                                GROUP BY T0.ItemCode ,T0.Warehouse
                            )T2 ON T0.ItemCode = T2.ItemCode AND T0.Warehouse = T2.Warehouse                            
                WHERE T0.DocDate < @Begintime
                GROUP BY T0.ItemCode ,T0.Warehouse ,T1.SUMInQtyTransValue ,T2.SUMOutQtyTransValue
            ) SBO002 ON SBO001.物料编码 = SBO002.物料编码 AND SBO001.仓库编码 = SBO002.仓库编码
    /**********↓↓↓↓↓本期入库信息↓↓↓↓↓**********/
    LEFT JOIN
            (
                SELECT 
                    T0.ItemCode AS N'物料编码' ,T0.Warehouse AS N'仓库编码'
                    ,SUM(ISNULL(T0.InQty,0)) AS N'本期入库数量'
                    ,CASE WHEN SUM(ISNULL(T0.InQty,0)) <> 0
                            THEN SUM(ISNULL(T0.TransValue,0)) / SUM(ISNULL(T0.InQty,0)) 
                        ELSE 0 END AS N'本期入库单价'
                    ,SUM(ISNULL(T0.TransValue,0)) AS N'本期入库总金额'
                FROM OINM T0 
                WHERE T0.DocDate >= @Begintime AND T0.DocDate <= @Endtime
                    AND ISNULL(T0.TransValue,0) >= 0
                GROUP BY T0.ItemCode ,T0.Warehouse
            ) SBO003 ON SBO001.物料编码 = SBO003.物料编码 AND SBO001.仓库编码 = SBO003.仓库编码 
    /**********↓↓↓↓↓本期出库信息↓↓↓↓↓**********/
    LEFT JOIN
            (
                SELECT 
                    T0.ItemCode AS N'物料编码' ,T0.Warehouse AS N'仓库编码'
                    ,SUM(ISNULL(-T0.OutQty,0)) AS N'本期出库数量'
                    ,CASE WHEN SUM(ISNULL(T0.OutQty,0)) <> 0
                            THEN SUM(ISNULL(T0.TransValue,0)) / SUM(ISNULL(T0.OutQty,0)) 
                        ELSE 0 END AS N'本期出库单价'
                    ,SUM(ISNULL(T0.TransValue,0)) AS N'本期出库总金额'
                FROM OINM T0 
                WHERE T0.DocDate >= @Begintime AND T0.DocDate <= @Endtime
                    AND ISNULL(T0.TransValue,0) < 0
                GROUP BY T0.ItemCode ,T0.Warehouse
            ) SBO004 ON SBO001.物料编码 = SBO004.物料编码 AND SBO001.仓库编码 = SBO004.仓库编码                     
    /**********↓↓↓↓↓本期重估信息↓↓↓↓↓**********/
    LEFT JOIN
            (
                SELECT 
                    T0.ItemCode AS N'物料编码' ,T0.Warehouse AS N'仓库编码'
                    ,SUM(ISNULL(T0.RevalTotal,0)) AS N'本期重估总金额'
                FROM OINM T0 
                WHERE T0.DocDate >= @Begintime AND T0.DocDate <= @Endtime
                GROUP BY T0.ItemCode ,T0.Warehouse
            ) SBO005 ON SBO001.物料编码 = SBO005.物料编码 AND SBO001.仓库编码 = SBO005.仓库编码 
    /**********↓↓↓↓↓期初重估信息↓↓↓↓↓**********/
    LEFT JOIN
            (
                SELECT 
                    T0.ItemCode AS N'物料编码' ,T0.Warehouse AS N'仓库编码'
                    ,SUM(ISNULL(T0.RevalTotal,0)) AS N'期初重估总金额'
                FROM OINM T0 
                WHERE T0.DocDate < @Begintime
                GROUP BY T0.ItemCode ,T0.Warehouse
            ) SBO006 ON SBO001.物料编码 = SBO006.物料编码 AND SBO001.仓库编码 = SBO006.仓库编码

GROUP BY SBO001.物料编码 ,SBO001.物料描述 ,SBO001.仓库名称 ,SBO001.物料组
    HAVING SUM(ISNULL(SBO002.期初数量,0))+SUM(ISNULL(SBO003.本期入库数量,0))+SUM(ISNULL(SBO004.本期出库数量,0)) <> 0
ORDER BY SBO001.物料编码
    
END

如有更多SAP Business One、SAP Business ByDesign、SAP S/4 HANA (Cloud)、SAP C/4 HANA及其他SAP®其他产品的交流。欢迎发邮件给我：brian.he@ngc-sz.com 或113191814@qq.com 或拨打 400-026-0755 或登录网站 http://www.ngc-sz.com/
毗翌咨询(NGC)是一家有10余年经验团队组成的专业SAP产品咨询公司，有自研N+平台对接OA、MES、WMS、BarCode(条码)、PLM、TMS、OMS(境内外电商)、APP、WEB+、微信、钉钉。在智能制造、新零售电商、专业服务业、化工生产，包括汽车零配件、家具家居、电子产品及元器件、快消品、分销、贸易、跨境电商、互联网公司、服装鞋帽、工业设备制造、公关与咨询服务、研发实验室、烟草粮油等都有相关经验和成功客户

以上如涉及版权信息内容，请联系进行删除。

相关文章

网友评论

      本文标题：SBO 库存台帐自定义报表分享 [NGC毗翌咨询]SAP B1

      本文链接：https://www.haomeiwen.com/subject/izesbctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|SBO 库存台帐自定义报表分享 [NGC毗翌咨询]SAP B1|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！