USE [Database_TestDB]
GO
/****** Object: StoredProcedure [dbo].[gtsp_interface_sap_sp] Script Date: 8/1/2018 12:14:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Store Procedure for calculating Revenue
Accepts as parameters:
GalaxyRule - Rules application
BusinessDateTime - the date we wish to impersonate as (calculate value as if on that day)
OverrideExisting - 0:No, Show current/new if not exist, 1:Yes, always recalculate and override
DebugFlag - reserved only for development
Sample
DECLARE @dt datetime = getdate();
EXEC gtsp_interface_sap_sp 0, @dt, 0, 0 -- all, save only if not exist
*/
-- ============================================================================================================================
-- Author: Tim Hsu
-- Create date: June 2018
-- Description: Calculates all Revenue
-- ============================================================================================================================
ALTER PROCEDURE [dbo].[gtsp_interface_sap_sp]
-- Add the parameters for the stored procedure here
@GalaxyRule INT, -- Run for only rule#
@BusinessDateTime DATETIME, -- Impersonate date, run as if calculation was base on this date
@OverrideExisting INT, -- 0:No, 1:Yes
@DebugFlag INT -- Show debug info: 0-No show, 1-Show
AS
BEGIN
-- Globals
DECLARE @idx BIGINT
DECLARE @max BIGINT
DECLARE @ExpirationDate DATETIME
DECLARE @CutoffDateTime DATETIME
SET @ExpirationDate = CONVERT(DATE, @BusinessDateTime)
SET @ExpirationDate = DATEADD(SECOND, 86399, @ExpirationDate); -- -1 second before next day
SET @CutoffDateTime = DATEADD(MINUTE, 1440 + 110, @BusinessDateTime); -- cut off time
-- Define ATS data exclusion
DECLARE @excludeNodes TABLE (NodeNo INT) INSERT INTO @excludeNodes VALUES (50),(52),(54);
DECLARE @excludeUsers TABLE (UserId INT) INSERT INTO @excludeUsers VALUES (999);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
PRINT 'Started: ' + CONVERT(VARCHAR(32), GETDATE(), 121);
PRINT ' initial declaration values: '
+ CONVERT(VARCHAR(4), @GalaxyRule) + ', '
+ CONVERT(VARCHAR(40), @BusinessDateTime, 121) + ', '
+ CONVERT(VARCHAR(40), @ExpirationDate, 121) + ', '
+ CONVERT(VARCHAR(40), @CutoffDateTime, 121) + ', '
+ CONVERT(VARCHAR(4), @OverrideExisting) + ', '
+ CONVERT(VARCHAR(4), @DebugFlag);
-- Check if we have data in history table
DECLARE @count INT = (SELECT COUNT(*) FROM gtsd_FI_sap_export_hist
WHERE CONVERT(DATE, BusinessDate) = CONVERT(DATE, @BusinessDateTime));
IF @count > 0
BEGIN
IF @OverrideExisting = 0 -- data exist, retrieve and return it
BEGIN
PRINT ' returning result set to caller';
SELECT * FROM gtsd_FI_sap_export_hist WHERE CONVERT(DATE, BusinessDate) = CONVERT(DATE, @BusinessDateTime);
GOTO Finalize;
END
ELSE IF @OverrideExisting = 1 -- data exist, erase it and regenerate
DELETE FROM gtsd_FI_sap_export_hist WHERE CONVERT(DATE, BusinessDate) = CONVERT(DATE, @BusinessDateTime);
END
PRINT ' cerating temporary item views';
-- Temporary view for later searching
CREATE TABLE #temp_item_view_FI
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemID INT NOT NULL,
PLU NVARCHAR(20) NULL,
Descr NVARCHAR(60) NULL,
AccessCode INT NOT NULL,
Name NVARCHAR(30) NULL,
ReportGroup NVARCHAR(12) NULL,
CalendarID INT NULL,
AttributeValueGroupID INT NULL,
RevRule NVARCHAR(256) NULL, -- for attribute CodeTableID = 35
RevNumVisits NVARCHAR(256) NULL -- for attribute CodeTableID = 36
);
-- Populate our view item table
INSERT
#temp_item_view_FI
SELECT
ItemID,
PLU,
Descr,
AccessCode,
Name,
ReportGroup,
CalendarID,
AttributeValueGroupID,
RevRule = NULL,
RevNumVisits = NULL
FROM
gtsv_interface_sap_item_FI;
-- update revrule and numofvisits
UPDATE
iv
SET
RevRule = (SELECT Value FROM gtsv_interface_sap_attr_FI WHERE PLU = iv.PLU AND CodeTableID = 35),
RevNumVisits = (SELECT Value FROM gtsv_interface_sap_attr_FI WHERE PLU = iv.PLU AND CodeTableID = 36)
FROM
#temp_item_view_FI iv
PRINT ' recreating [gtsd_FI_sap_export_logs] table';
-- Recreate Permanent Revenue Recognition Table
DROP TABLE IF EXISTS gtsd_FI_sap_export_logs;
CREATE TABLE gtsd_FI_sap_export_logs
(
ExportDate DATE NOT NULL, -- date record was generated
ExportTime NVARCHAR(8) NOT NULL, -- time record was generated
BusinessDate DATETIME NOT NULL, -- impersonate date
VisualID NVARCHAR(40) NOT NULL, -- barcode
AccessCode INT NOT NULL, -- item type
ValidFrom DATETIME NULL, -- real adjusted valid/start date
ValidTo DATETIME NULL, -- real adjusted expiration date
RevRule NVARCHAR(256) NULL, -- from attribute CodeTableID = 35
GalaxyRule INT NOT NULL, -- galaxy rule
Channel INT NOT NULL, -- 0:company, 1:wholesaler
RemValue INT NOT NULL, -- remaining value
Price MONEY NOT NULL, -- item sold price
PLU NVARCHAR(20) NULL, -- PLU
DefRevenue MONEY NOT NULL, -- deferred revenue
INDEX IXBusinessDate (BusinessDate) -- index on datetime for easy searching
);
-- Temporary table for tickets, passes, packages, etc
CREATE TABLE #temp_item_FI
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ExportDate DATE NOT NULL, -- date record was generated
ExportTime NVARCHAR(8) NOT NULL, -- time record was generated
BusinessDate DATETIME NOT NULL, -- impersonate date run on/as
TransDate DATETIME NULL, -- transaction start date time
ItemID INT NOT NULL, -- TicketID, PassNO, etc
NodeNo INT NOT NULL, -- Node number
UserId INT NOT NULL, -- User
VisualID NVARCHAR(40) NOT NULL, -- barcode
AccessCode INT NOT NULL, -- item type
IssueDate DATETIME NULL, -- system issue date
Status INT NOT NULL, -- validity flag
Price MONEY NOT NULL, -- sold price
PLU NVARCHAR(20) NULL, -- PLU
CustomerID INT NOT NULL, -- client id
ValidFromDate DATETIME NULL, -- productdef start date
ValidToDate DATETIME NULL, -- productdef expire date
RevRule NVARCHAR(256) NULL, -- from attribute CodeTableID = 35
GalaxyRule INT NOT NULL, -- galaxy rule
RevNumVisits NVARCHAR(256) NULL, -- from attribute CodeTableID = 36
MaxNumUsage INT NOT NULL, -- entitlement
NumUsage INT NOT NULL, -- total number usage
Channel INT NOT NULL, -- 0:company, 1:wholesaler
RemValue INT NOT NULL, -- remaining value
ValidFrom DATETIME NULL, -- real adjusted valid/start date
ValidTo DATETIME NULL, -- real adjusted expiration date
DefRevenue MONEY NOT NULL -- deferred revenue
);
PRINT ' populating temp table with Tickets';
-- Insert all valid tickets in our temp table
INSERT
#temp_item_FI
SELECT
CONVERT(DATE, GETDATE()),
CONVERT(NVARCHAR(8), GETDATE(), 108),
BusinessDate = @BusinessDateTime,
pd.DateSold,
tk.Ticketid,
tk.NodeNo,
UserId = 0,
tk.VisualID,
tk.AccessCode,
tk.DateSold,
tk.Status,
tk.Price,
tk.PLU,
tk.CustomerID,
pd.ValidFromDate,
pd.ValidToDate,
RevRule = iv.RevRule,
GalaxyRule = 0,
RevNumVisits = iv.RevNumVisits,
MaxNumUsage = 1,
NumUsage = 0,
Channel = 0,
RemValue = 0,
ValidFrom = pd.ValidFromDate,
ValidTo = pd.ValidToDate,
DefRevenue = 0.00
FROM
Tickets tk
JOIN
GTS_MS_TBL_ProductDeferment pd ON tk.VisualID = pd.VisualID
LEFT JOIN
#temp_item_view_FI iv ON tk.PLU = iv.PLU
WHERE
tk.Status = 0
AND tk.NodeNo NOT IN (SELECT NodeNo FROM @excludeNodes)
AND tk.AccessCode BETWEEN 10000 AND 30002
PRINT ' populating temp table with Passes';
-- Insert all valid passes in our temp table
INSERT
#temp_item_FI
SELECT
CONVERT(DATE, GETDATE()),
CONVERT(NVARCHAR(8), GETDATE(), 108),
BusinessDate = @BusinessDateTime,
pd.DateSold,
pa.PassNo,
pa.NodeNo,
UserId = 0,
pa.VisualID,
pa.AccessCode,
pa.ValidFrom,
pa.Status,
pa.Price,
pa.PLU,
pa.Company, -- maybe?
pd.ValidFromDate,
pd.ValidToDate,
RevRule = iv.RevRule,
GalaxyRule = 0,
RevNumVisits = iv.RevNumVisits,
MaxNumUsage = 1,
NumUsage = 0,
Channel = 0,
RemValue = 0,
ValidFrom = pd.ValidFromDate,
ValidTo = pd.ValidToDate,
DefRevenue = 0.00
FROM
Passes pa
JOIN
GTS_MS_TBL_ProductDeferment pd ON pa.VisualID = pd.VisualID
LEFT JOIN
#temp_item_view_FI iv ON pa.PLU = iv.PLU
WHERE
pa.Status IN (0, 1) -- voided passes may be unvoided
AND pa.NodeNo NOT IN (SELECT NodeNo FROM @excludeNodes)
AND pa.AccessCode BETWEEN 20000 AND 90000
AND USER02 = '' AND USER10 = '' -- recommended by Victor to filter this way
-- Insert miscellaneous passes in our temp table, such as renewed, reissue, replaced, etc
INSERT
#temp_item_FI
SELECT
CONVERT(DATE, GETDATE()),
CONVERT(NVARCHAR(8), GETDATE(), 108),
BusinessDate = @BusinessDateTime,
pd.DateSold,
pd.JnlTicketID,
jt.NodeNo,
UserId = 0,
jt.VisualID,
jt.AccessCode,
jt.DateSold,
jt.Status,
jt.Price,
jt.PLU,
jt.CustomerID,
pd.ValidFromDate,
pd.ValidToDate,
RevRule = iv.RevRule,
GalaxyRule = 0,
RevNumVisits = iv.RevNumVisits,
MaxNumUsage = 1,
NumUsage = 0,
Channel = 0,
RemValue = 0,
ValidFrom = pd.ValidFromDate,
ValidTo = pd.ValidToDate,
DefRevenue = 0.00
FROM
SuperTickets st
JOIN
#temp_item_FI it ON st.VisualID = it.VisualID
JOIN
JnlTickets jt ON st.VisualID = jt.VisualID
JOIN
GTS_MS_TBL_ProductDeferment pd ON st.VisualID = pd.VisualID
LEFT JOIN
#temp_item_view_FI iv ON jt.PLU = iv.PLU
WHERE
it.VisualID IS NULL -- query only NOT exist in tickets/passes table
AND jt.Status IN (0, 1) -- valid/voided
AND jt.Qty = 1 -- current
AND jt.AccessCode BETWEEN 80000 AND 90000 -- only passes
AND jt.NodeNo NOT IN (SELECT NodeNo FROM @excludeNodes)
AND jt.TktCode < 40000 -- non lost/reissue
AND st.NextID = 0 AND st.Status = 3 AND st.TicketType = 1 AND st.CurrentRecord = 1; -- old but valid ticket
-- Loop through all items and adjust usage, rules, dates, revenue, etc
SET @idx = 1;
SET @max = (SELECT MAX(ID) FROM #temp_item_FI);
PRINT ' found (' + CONVERT(VARCHAR(16), @max) + ') items';
PRINT ' looping through and calculating Revenue...';
WHILE @idx <= @max
BEGIN
DECLARE @iVisualID NVARCHAR(40)
DECLARE @iUserID INT
DECLARE @iCustomerID INT
DECLARE @iIsWholeSaler INT
DECLARE @iGalaxyRule INT
DECLARE @iMaxNumUsage INT
DECLARE @iNumUsage INT
DECLARE @iChannel INT
DECLARE @iRemValue INT
DECLARE @iAccessCode INT
DECLARE @iPLU NVARCHAR(20)
DECLARE @iRevRule NVARCHAR(256)
DECLARE @iRevNumVisits NVARCHAR(256)
DECLARE @iTransDate DATETIME
DECLARE @iIssueDate DATETIME
DECLARE @iValidFrom DATETIME
DECLARE @iValidTo DATETIME
DECLARE @iPrice DECIMAL(18,10)
DECLARE @iDefRevenue DECIMAL(18,10)
-- loop and take each record from tmp table
SELECT
@iVisualID = VisualID,
@iAccessCode = AccessCode,
@iCustomerID = CustomerID,
@iIssueDate = IssueDate,
@iPrice = Price,
@iRevRule = RevRule,
@iRevNumVisits = RevNumVisits,
@iValidFrom = ValidFrom,
@iValidTo = ValidTo
FROM #temp_item_FI WHERE ID = @idx;
-- PRE-process: get defaults
SET @iValidFrom = ISNULL(@iValidFrom, @iIssueDate);
SET @iIsWholeSaler = (SELECT COUNT(*) FROM gtsv_interface_sap_wholesale_FI WHERE CustomerID = @iCustomerID);
SET @iNumUsage = (SELECT dbo.gtsf_interface_sap_usage_FI(@iVisualID));
IF @iIsWholeSaler = 1
SET @iChannel = 1 -- wholesaler
ELSE
SET @iChannel = 0 -- company
-- set max number usage
IF ISNUMERIC(@iRevNumVisits) = 1
SET @iMaxNumUsage = CONVERT(INT, @iRevNumVisits);
ELSE IF @iRevNumVisits = 'UNLIMITED'
SET @iMaxNumUsage = 365;
ELSE
SET @iMaxNumUsage = 1;
-- set galaxy rule
IF @iRevRule = '10'
SET @iGalaxyRule = 4;
ELSE IF @iRevRule = '1' OR @iRevRule = '5' OR @iRevRule = '6'
SET @iGalaxyRule = 1;
ELSE IF @iRevRule = '2' OR @iRevRule = '3' OR @iRevRule = '4'
SET @iGalaxyRule = 2;
ELSE IF @iRevRule = '7' OR @iRevRule = '8' OR @iRevRule = '9'
SET @iGalaxyRule = 3;
ELSE
BEGIN
IF @iAccessCode BETWEEN 10000 AND 20000 -- single day
SET @iGalaxyRule = 1;
ELSE IF @iAccessCode BETWEEN 20000 AND 30000 -- multi day
SET @iGalaxyRule = 2;
ELSE IF @iAccessCode BETWEEN 80000 AND 90000 -- MA
SET @iGalaxyRule = 3;
ELSE IF @iAccessCode BETWEEN 30000 AND 30002 -- MA cert
SET @iGalaxyRule = 4;
END
-- recalculate expiration date
IF @iGalaxyRule = 1 OR @iGalaxyRule = 2
SET @iValidTo = ISNULL(@iValidTo, DATEADD(MONTH, 6, @iValidFrom));
ELSE IF @iGalaxyRule = 3
SET @iValidTo = ISNULL(@iValidTo, DATEADD(YEAR, 1, @iValidFrom));
ELSE IF @iGalaxyRule = 4
SET @iValidTo = ISNULL(@iValidTo, DATEADD(MONTH, 3, @iValidFrom));
-- Adjust calculation
SET @iRemValue = @iMaxNumUsage - @iNumUsage;
-- POST-process: calculate deferred revenue
IF @iMaxNumUsage > 0
SET @iDefRevenue = (@iPrice * @iRemValue) / @iMaxNumUsage;
-- calculate amortization
IF @iGalaxyRule = 3
BEGIN
IF @iValidFrom > @ExpirationDate
SET @iRemValue = DATEDIFF(DAY, @iValidFrom, @iValidTo);
ELSE
SET @iRemValue = DATEDIFF(DAY, @ExpirationDate, @iValidTo);
IF @iRemValue > 0
SET @iDefRevenue = @iPrice / DATEDIFF(DAY, @iValidFrom, @iValidTo) * @iRemValue;
END
-- adjust for expired + wholesaler tickets
IF @iIsWholeSaler > 0 AND @iNumUsage = 0 AND @iValidTo <= @ExpirationDate
BEGIN
SET @iGalaxyRule = 5;
SET @iValidTo = DATEADD(MONTH, 5, @iValidTo);
END
-- set upgrade galaxy rule
DECLARE @iBaseID INT
DECLARE @iCurrTicketType INT
-- write fucntion to see if ticket was upgraded, suggestion from Matt to check SuperTickets
SELECT TOP 1
@iBaseID = BaseID,
@iCurrTicketType = TicketType
FROM -- get current/new ticket
gtsv_interface_sap_super_FI WHERE VisualID = @iVisualID AND CurrentRecord = 1;
IF @@ROWCOUNT > 0
BEGIN
DECLARE @iPrevTicketType INT -- get previous/old ticket
SELECT @iPrevTicketType = TicketType FROM gtsv_interface_sap_super_FI WHERE SuperTicketID = @iBaseID AND Status = 4;
IF @@ROWCOUNT > 0
BEGIN
IF @iCurrTicketType = 1 -- current is MA
BEGIN
IF @iPrevTicketType = 1
SET @iGalaxyRule = 7; -- previous is lower tier MA
ELSE
SET @iGalaxyRule = 6; -- previous is ticket
END
ELSE IF @iCurrTicketType = 0 -- current is multi-ticket
BEGIN
IF @iPrevTicketType = 0
SET @iGalaxyRule = 8; -- previous is ticket
END
END
END
-- get transaction start date, for day end calculation
SELECT TOP 1
@iTransDate = jh.TransStartDateTime,
@iUserID = jh.UserId
FROM #temp_item_FI ti
JOIN JnlTickets jt ON ti.VisualID = jt.VisualID
JOIN JnlDetails jd ON jt.JnlDetailID = jd.JnlDetailID
JOIN JnlHeaders jh ON jd.JnlTranID = jh.JnlTranID
WHERE ti.VisualID = @iVisualID
SET @iTransDate = ISNULL(@iTransDate, @iIssueDate);
-- update temps table with new calculated values
UPDATE
#temp_item_FI
SET
TransDate = @iTransDate,
UserId = @iUserID,
NumUsage = @iNumUsage,
RemValue = @iRemValue,
GalaxyRule = @iGalaxyRule,
MaxNumUsage = @iMaxNumUsage,
ValidFrom = @iValidFrom,
ValidTo = @iValidTo,
Channel = @iChannel,
DefRevenue = @iDefRevenue
WHERE ID = @idx;
SET @idx = @idx+1
END
PRINT ' storing result to [gtsd_FI_sap_export_logs] table';
-- Insert statements for procedure here
INSERT
gtsd_FI_sap_export_logs
SELECT
ExportDate, ExportTime, BusinessDate, it.VisualID, it.AccessCode, ValidFrom, ValidTo,
RevRule, GalaxyRule, Channel, RemValue, it.Price, it.PLU, DefRevenue
FROM
#temp_item_FI it
--JOIN JnlTickets jt ON it.VisualID = jt.VisualID
--JOIN JnlDetails jd ON jt.JnlDetailID = jd.JnlDetailID
--JOIN JnlHeaders jh ON jd.JnlTranID = jh.JnlTranID
WHERE 1=1
AND (
(@GalaxyRule = 0 AND it.GalaxyRule > 0 ) -- all
OR (@GalaxyRule = it.GalaxyRule ) -- by specific rules
)
AND it.Status IN (0, 1) -- valid/voided
AND it.RemValue > 0 -- unused
AND it.ValidTo > @ExpirationDate -- unexpired
AND it.TransDate < @CutoffDateTime -- consider only data made before 3:30am
AND it.UserId NOT IN (SELECT UserId FROM @excludeUsers) -- exlude ATS users
--GROUP BY
-- jt.VisualID, jd.JnlDetailID, jh.JnlTranID
ORDER BY
it.ItemID
-- save copy to history table
PRINT ' archiving result to [gtsd_FI_sap_export_hist] table';
INSERT INTO gtsd_FI_sap_export_hist SELECT * FROM gtsd_FI_sap_export_logs;
PRINT ' returning result set to caller';
-- return result set
IF @DebugFlag = 1
SELECT /*DISTINCT*/ it.* FROM gtsd_FI_sap_export_logs el JOIN #temp_item_FI it ON el.VisualID = it.VisualID;
ELSE
SELECT /*DISTINCT*/ * FROM gtsd_FI_sap_export_logs;
Finalize: -- label
-- clean up
PRINT ' cleaning up objects';
DROP TABLE IF EXISTS #temp_item_FI;
DROP TABLE IF EXISTS #temp_item_view_FI;
PRINT 'Done: ' + CONVERT(VARCHAR(32), GETDATE(), 121);
END