Untitled
teeest
unknown
sqlserver
3 days ago
11 kB
3
Indexable
Never
USE [QAS_Market]
GO
/***** Object: StoredProcedure [Business].[usp_Shrink_Billing_GetShrinkBillingHeader] Script Date: 10/1/2024 4:43:13 PM *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* -------------------------------------------------------------------------------------------------
Stored Proc: [Business].[usp_Shrink_Billing_GetShrinkBillingHeader]
Description: Returns the data set of Shrink Billing Header
Revisions: 02/29/2024 - AL - Created
04/22/2024 - AL - Added Notes
05/24/2024 - AL - Added CurrentLoss
05/30/2024 - AL - Added NationalAccountInd
07/01/2024 - AL - Added Last Audit Date
-- Added full names for InvoiceRejectedBy and ReviewedBy
------------------------------------------------------------------------------------------------- */
ALTER PROCEDURE [Business].[usp_Shrink_Billing_GetShrinkBillingHeader]
(
@FiscalPeriodID INT,
@OperationID INT,
@StatusLookupID INT = NULL,
@ErrorsInd BIT,
@CustomerID INT = NULL,
@AccountID INT = NULL,
@LocationID INT = NULL,
@DeliveryLocationIDList VARCHAR(MAX) = NULL,
@BillingTypeLookupID INT = NULL
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnedStatusLookupID INT;
SET @ReturnedStatusLookupID = dbo.udf_GetMarketLookupID('ShrinkBillingType', 'RETURNED');
CREATE TABLE #DeliveryLocationIDs
(
DeliveryLocationID INT,
DeliveryLocationName VARCHAR(200),
InventoryAccountID INT
);
IF @DeliveryLocationIDList IS NULL
BEGIN
IF @LocationID IS NULL
BEGIN
IF @AccountID IS NULL
BEGIN
IF @CustomerID IS NULL
BEGIN
INSERT INTO #DeliveryLocationIDs
SELECT
DL.DeliveryLocationID,
DL.Name AS DeliveryLocationName,
DL.InventoryAccountID
FROM
Delivery_Location DL WITH (NOLOCK)
JOIN Location L WITH (NOLOCK) ON DL.LocationID = L.LocationID
JOIN Account A WITH (NOLOCK) ON L.AccountID = A.AccountID
WHERE
A.OperationID = @OperationID
AND A.BillForShrinkInd = 1;
END
ELSE
BEGIN
INSERT INTO #DeliveryLocationIDs
SELECT
DL.DeliveryLocationID,
DL.Name AS DeliveryLocationName,
DL.InventoryAccountID
FROM
Delivery_Location DL WITH (NOLOCK)
JOIN Location L WITH (NOLOCK) ON DL.LocationID = L.LocationID
JOIN Account A WITH (NOLOCK) ON L.AccountID = A.AccountID
WHERE
A.CustomerID = @CustomerID
AND A.OperationID = @OperationID
AND A.BillForShrinkInd = 1;
END
END
ELSE
BEGIN
INSERT INTO #DeliveryLocationIDs
SELECT
DL.DeliveryLocationID,
DL.Name AS DeliveryLocationName,
DL.InventoryAccountID
FROM
Delivery_Location DL WITH (NOLOCK)
JOIN Location L WITH (NOLOCK) ON DL.LocationID = L.LocationID
WHERE
L.AccountID = @AccountID;
END
END
ELSE
BEGIN
INSERT INTO #DeliveryLocationIDs
SELECT
DL.DeliveryLocationID,
DL.Name AS DeliveryLocationName,
DL.InventoryAccountID
FROM
Delivery_Location DL WITH (NOLOCK)
WHERE
DL.LocationID = @LocationID;
END
END
ELSE
BEGIN
INSERT INTO #DeliveryLocationIDs
SELECT
D.Data,
DL.Name AS DeliveryLocationName,
DL.InventoryAccountID
FROM
dbo.udf_Split(@DeliveryLocationIDList, ',') D
JOIN Delivery_Location DL WITH (NOLOCK) ON DL.DeliveryLocationID = D.Data;
END
CREATE TABLE #Result
(
ShrinkBillingID INT,
BillingDataSentDateTime DATETIME,
StatusName VARCHAR(100),
StatusLookupID SMALLINT,
TypeName VARCHAR(100),
BillingPeriod VARCHAR(100),
CustomerName VARCHAR(200),
AccountName VARCHAR(200),
LocationName VARCHAR(200),
DeliveryLocationName VARCHAR(200),
BillingRate DECIMAL(4,2),
CurrentLoss MONEY,
Error VARCHAR(MAX),
Notes VARCHAR(MAX),
WorkflowNotes VARCHAR(MAX),
NationalAccountInd BIT,
MarketAuditDate VARCHAR(200),
ReviewedBy VARCHAR(200),
ReviewedDateTime DATETIME,
InvoiceRejectedBy VARCHAR(200),
InvoiceRejectedDateTime DATETIME
);
INSERT INTO #Result
SELECT
R.ShrinkBillingID,
R.BillingDataSentDateTime,
R.StatusName,
R.StatusLookupID,
R.TypeName,
R.BillingPeriod,
R.CustomerName,
R.AccountName,
R.LocationName,
R.DeliveryLocationName,
R.BillingRate,
CASE
WHEN R.SalesAmountSum = 0 THEN 0
ELSE ABS((R.RetailShortageSum / R.SalesAmountSum) * 100)
END AS CurrentLoss,
R.Error,
R.Notes,
R.WorkflowNotes,
R.NationalAccountInd,
R.MarketAuditDate,
R.ReviewedBy,
R.ReviewedDateTime,
R.InvoiceRejectedBy,
R.InvoiceRejectedDateTime
FROM
(
SELECT
SB.ShrinkBillingID,
SB.BillingDataSentDateTime,
LS.Name AS StatusName,
LS.LookupID AS StatusLookupID,
LT.Name AS TypeName,
CONVERT(VARCHAR(MAX), FP.PeriodOpenDate, 101) + ' | ' + CONVERT(VARCHAR(MAX), FP.PeriodCloseDate, 101) AS BillingPeriod,
BFC.Name AS CustomerName,
A.Name AS AccountName,
L.Name AS LocationName,
DLI.DeliveryLocationName,
SB.BillingRate,
CAST(SUM(ISNULL(SBDR.RetailShortage, 0)) AS MONEY) AS RetailShortageSum,
CAST(SUM(ISNULL(SBDS.SalesAmount, 0)) AS MONEY) AS SalesAmountSum,
CASE
WHEN SB.Error IS NULL THEN SB.Error
WHEN SB.Error IS NOT NULL AND CHARINDEX('Error: Delivery Location', SB.Error) > 0 THEN SB.Error
ELSE 'Please contact your LP Champion'
END AS Error,
SB.Notes,
SB.WorkflowNotes,
BFC.NationalAccountInd,
CASE
WHEN MAX(INVP.FiscalDate) IS NULL THEN 'No Audit'
ELSE CAST(MAX(INVP.FiscalDate) AS VARCHAR)
END AS MarketAuditDate,
MIN(ISNULL(U2.FirstName + ' ' + U2.LastName, '')) AS ReviewedBy,
SB.ReviewedDateTime,
MIN(ISNULL(U1.FirstName + ' ' + U1.LastName, '')) AS InvoiceRejectedBy,
SB.InvoiceRejectedDateTime
FROM
Shrink_Billing SB WITH (NOLOCK)
JOIN #DeliveryLocationIDs DLI WITH (NOLOCK) ON SB.DeliveryLocationID = DLI.DeliveryLocationID
LEFT JOIN Shrink_Billing_Detail SBDS WITH (NOLOCK) ON SBDS.ShrinkBillingID = SB.ShrinkBillingID
LEFT JOIN Shrink_Billing_Detail SBDR WITH (NOLOCK) ON SBDR.ShrinkBillingDetailID = SBDS.ShrinkBillingDetailID AND SBDR.RetailShortage < 0
JOIN Location L WITH (NOLOCK) ON L.LocationID = SB.LocationID
JOIN Account A WITH (NOLOCK) ON A.AccountID = SB.AccountID
LEFT JOIN Business_Foundation.Customer BFC WITH (NOLOCK) ON BFC.CustomerID = SB.CustomerID
JOIN Lookup LS WITH (NOLOCK) ON LS.LookupID = SB.StatusLookupID
JOIN Lookup LT WITH (NOLOCK) ON LT.LookupID = SB.BillingTypeLookupID
JOIN Business_Foundation.Fiscal_Period FP WITH (NOLOCK) ON FP.FiscalPeriodID = SB.FiscalPeriodID
LEFT JOIN Inventory_Physical INVP WITH (NOLOCK) ON INVP.InventoryAccountID = DLI.InventoryAccountID
LEFT JOIN CVTG_Foundation.Users U1 WITH (NOLOCK) ON U1.UsersID = SB.InvoiceRejectedBy
LEFT JOIN CVTG_Foundation.Users U2 WITH (NOLOCK) ON U2.UsersID = SB.ReviewedBy
WHERE
SB.FiscalPeriodID = @FiscalPeriodID
AND (@StatusLookupID IS NULL OR SB.StatusLookupID = @StatusLookupID)
AND (@BillingTypeLookupID IS NULL OR SB.BillingTypeLookupID = @BillingTypeLookupID)
GROUP BY
SB.ShrinkBillingID,
SB.BillingDataSentDateTime,
LS.Name,
LS.LookupID,
LT.Name,
FP.PeriodOpenDate,
FP.PeriodCloseDate,
BFC.Name,
A.Name,
L.Name,
DLI.DeliveryLocationName,
SB.BillingRate,
SB.Error,
SB.Notes,
SB.WorkflowNotes,
BFC.NationalAccountInd,
SB.ReviewedDateTime,
SB.InvoiceRejectedDateTime,
SB.ReviewedBy,
SB.InvoiceRejectedBy
) R;
IF @ErrorsInd = 1
BEGIN
SELECT DISTINCT
R.ShrinkBillingID,
R.BillingDataSentDateTime,
R.StatusName,
R.TypeName,
R.BillingPeriod,
R.CustomerName,
R.AccountName,
R.LocationName,
R.DeliveryLocationName,
R.BillingRate,
R.CurrentLoss,
R.Error,
R.Notes,
R.WorkflowNotes,
R.NationalAccountInd,
R.MarketAuditDate,
R.ReviewedBy,
R.ReviewedDateTime,
R.InvoiceRejectedBy,
R.InvoiceRejectedDateTime
FROM
#Result R
WHERE
R.StatusLookupID = @ReturnedStatusLookupID OR R.Error IS NOT NULL;
END
ELSE
BEGIN
SELECT DISTINCT
R.ShrinkBillingID,
R.BillingDataSentDateTime,
R.StatusName,
R.TypeName,
R.BillingPeriod,
R.CustomerName,
R.AccountName,
R.LocationName,
R.DeliveryLocationName,
R.BillingRate,
R.CurrentLoss,
R.Error,
R.Notes,
R.WorkflowNotes,
R.NationalAccountInd,
R.MarketAuditDate,
R.ReviewedBy,
R.ReviewedDateTime,
R.InvoiceRejectedBy,
R.InvoiceRejectedDateTime
FROM
#Result R;
END
END
Leave a Comment