---------------------------------------------------------------------------------------------------
--HITB Summary
---------------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[BIT_HITBTemp] Script Date: 10/01/2018 04:57:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_HITBTemp](
[ACTNUMBR_1] [char](6) NOT NULL,
[ACTNUMBR_2] [char](6) NOT NULL,
[ACTNUMBR_3] [char](6) NOT NULL,
[ACTNUMBR_4] [char](6) NOT NULL,
[ACTNUMBR_5] [char](6) NOT NULL,
[ACTNUMBR_6] [char](6) NOT NULL,
[ACTNUMBR_7] [char](6) NOT NULL,
[ACTNUMBR_8] [char](6) NOT NULL,
[ACTNUMBR_9] [char](6) NOT NULL,
[ACTNUMBR_10] [char](6) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LOCNCODE] [char](11) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[JRNENTRY] [int] NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMDESC] [char](101) NOT NULL,
[ITMGEDSC] [char](11) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[TRXREFERENCE] [smallint] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[VARIANCEQTY] [numeric](19, 5) NOT NULL,
[QTYTYPE] [smallint] NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[IsLandedCostTrx] [tinyint] NOT NULL,
[IsVarianceTrx] [tinyint] NOT NULL,
[IsOverrideReceipt] [tinyint] NOT NULL,
[VCTNMTHD] [smallint] NOT NULL,
[ASOFDATE] [datetime] NOT NULL,
[STR1] [char](1) NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ACTINDX] [int] NOT NULL,
[DEX_ROW_ID] [int] NOT NULL,
[ACTNUMST] [char](129) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
---------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_HITBView]
AS
SELECT ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5, ACTNUMBR_6, ACTNUMBR_7, ACTNUMBR_8, ACTNUMBR_9, ACTNUMBR_10, ITEMNMBR, LOCNCODE, DOCDATE, GLPOSTDT, JRNENTRY, SEQNUMBR, ITEMDESC, ITMGEDSC,
DOCNUMBR, DOCTYPE, TRXSORCE, TRXREFERENCE, HSTMODUL, TRXQTY, VARIANCEQTY, QTYTYPE, UNITCOST, EXTDCOST, CRDTAMNT, DEBITAMT, IsLandedCostTrx, IsVarianceTrx, IsOverrideReceipt, VCTNMTHD, ASOFDATE, STR1, DECPLQTY, DECPLCUR,
ACTINDX, DEX_ROW_ID, ACTNUMST, DEBITAMT - CRDTAMNT AS StockVal
FROM BIT_HITBTemp
GO
--------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[BI_BuildHITB]
-- Add the parameters for the stored procedure here
@Tdt as datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
delete from BIT_HITBTemp
Insert into BIT_HITBTemp
exec seeHITB @I_nSortBy=2,@I_nReceiptOptions=2,@I_sStartItemNumber=N'',@I_sEndItemNumber=N'þþþþþþþþþþþþþþþþþþþþþþþþþþþþþþþ',@I_sStartAccountNumber=N' - - ',@I_sEndAccountNumber=N'ÿÿÿ-ÿÿÿÿ-ÿÿ',@I_sStartLocationCode=N'',@I_sEndLocationCode=N'þþþþþþþþþþþ',@I_nStartQTYType=1,@I_nEndQTYType=5,@I_dtStart='1900-01-01 00:00:00',@I_dtEnd=@Tdt,@sStartClass=N'',@sEndClass=N'þþþþþþþþþþþ',@sStartGenericDesc=N'',@sEndGenericDesc=N'þþþþþþþþþþþ',@I_fUseGLPostDate=0,@I_fIncludeZeroQtyItems=1,@O_SQL_Error_State=NULL
select * from BI_HITBView
END
GO
------------------------------------------------------------------------------------
--HITB Detail
---------------------------------------------------------------------------------------
/****** Object: Table [dbo].[BIT_HitbTemp_Dtl] Script Date: 10/01/2018 04:58:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_HitbTemp_Dtl](
[ACTNUMBR_1] [char](6) NOT NULL,
[ACTNUMBR_2] [char](6) NOT NULL,
[ACTNUMBR_3] [char](6) NOT NULL,
[ACTNUMBR_4] [char](6) NOT NULL,
[ACTNUMBR_5] [char](6) NOT NULL,
[ACTNUMBR_6] [char](6) NOT NULL,
[ACTNUMBR_7] [char](6) NOT NULL,
[ACTNUMBR_8] [char](6) NOT NULL,
[ACTNUMBR_9] [char](6) NOT NULL,
[ACTNUMBR_10] [char](6) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LOCNCODE] [char](11) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[JRNENTRY] [int] NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMDESC] [char](101) NOT NULL,
[ITMGEDSC] [char](11) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[TRXREFERENCE] [smallint] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[VARIANCEQTY] [numeric](19, 5) NOT NULL,
[QTYTYPE] [smallint] NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[IsLandedCostTrx] [tinyint] NOT NULL,
[IsVarianceTrx] [tinyint] NOT NULL,
[IsOverrideReceipt] [tinyint] NOT NULL,
[VCTNMTHD] [smallint] NOT NULL,
[ASOFDATE] [datetime] NOT NULL,
[STR1] [char](1) NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ACTINDX] [int] NOT NULL,
[DEX_ROW_ID] [int] NOT NULL,
[ACTNUMST] [char](129) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-----------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_HITBView_Dtl]
AS
SELECT ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5, ACTNUMBR_6, ACTNUMBR_7, ACTNUMBR_8, ACTNUMBR_9, ACTNUMBR_10, ITEMNMBR, LOCNCODE, DOCDATE, GLPOSTDT, JRNENTRY, SEQNUMBR, ITEMDESC, ITMGEDSC,
DOCNUMBR, DOCTYPE, TRXSORCE, TRXREFERENCE, HSTMODUL, TRXQTY, VARIANCEQTY, QTYTYPE, UNITCOST, EXTDCOST, CRDTAMNT, DEBITAMT, IsLandedCostTrx, IsVarianceTrx, IsOverrideReceipt, VCTNMTHD, ASOFDATE, STR1, DECPLQTY, DECPLCUR,
ACTINDX, DEX_ROW_ID, ACTNUMST, CASE WHEN jrnentry = 0 THEN 0 ELSE dbo.BIT_HitbTemp_Dtl.DEBITAMT - dbo.BIT_HitbTemp_Dtl.CRDTAMNT END AS StockVal
FROM BIT_HitbTemp_Dtl
GO
-----------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[BI_BuildHITB_Dtl]
-- Add the parameters for the stored procedure here
@Tdt as datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
delete from BIT_HITBTemp_Dtl
Insert into BIT_HITBTemp_Dtl
exec seeHITB @I_nSortBy=2,@I_nReceiptOptions=1,@I_sStartItemNumber=N'',@I_sEndItemNumber=N'þþþþþþþþþþþþþþþþþþþþþþþþþþþþþþþ',@I_sStartAccountNumber=N' - - ',@I_sEndAccountNumber=N'ÿÿÿ-ÿÿÿÿ-ÿÿ',@I_sStartLocationCode=N'',@I_sEndLocationCode=N'þþþþþþþþþþþ',@I_nStartQTYType=1,@I_nEndQTYType=5,@I_dtStart='1900-01-01 00:00:00',@I_dtEnd=@Tdt,@sStartClass=N'',@sEndClass=N'þþþþþþþþþþþ',@sStartGenericDesc=N'',@sEndGenericDesc=N'þþþþþþþþþþþ',@I_fUseGLPostDate=0,@I_fIncludeZeroQtyItems=1,@O_SQL_Error_State=NULL
select * from BI_HITBView_Dtl
END
GO
No comments:
Post a Comment