USE [NMAS]
GO
/****** Object: StoredProcedure [dbo].[nmas_b_subsp_getMBAOpeningBalanceForMBAL] Script Date: 2023-11-21 11:03:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--'**************************************************************************************************************************
--* Created by:
--*
--* Purpose: This stored procedure is used to gather a MBA Balance (cloned from dbo.nmas_b_subsp_getMBAOpeningBalance)
--*
--* CVS File: $RCSfile: dbo.nmas_b_subsp_getMBAOpeningBalanceForMBAL.PRC,v $
--* Commit Date: $Date: 2008/10/16 13:58:38 $ (UTC)
--* Committed by: $Author: walshkel $
--* CVS Revision: $Revision: 1.2 $
--* Checkout Tag: $Name: Version_1-3-3-1 $ (Version/Build)
--*
--* Called By: nmas_sp_getMBA_LOF_LedgerData
--*
--'**************************************************************************************************************************
/**********************************************************************************************************************
***********************************************************************************************************************/
ALTER PROCEDURE [dbo].[nmas_b_subsp_getMBAOpeningBalanceForMBAL]
--INPUTS
@isMBA_LOF BIT,
@ReportMBAorLOF BIT,
@MBACode VARCHAR(4),
@PKMPCode VARCHAR(2),
@SafeguardedExemptMode VARCHAR(1),
-- Safeguarded {S|s}, Non-Safeguarded {N|n}, Exempted Use {U|u}, Exempted Quantity {Q|q}
@RequestDate DATETIME,
-- The date to which the opening balance is required for a specifc MBA
@ApplicationMode int
-- 0 - By difference, 1 - By Virtual Replacement
--OUTPUTS
-- A temporary table is populated with the result set
AS
BEGIN
DECLARE
@PreviousPIVDate DATETIME,
@DayBeforeRequestDate DATETIME,
@intMaterialCode INT,
@counterBefore INT,
@counterAfter INT,
@elementCode VARCHAR(20),
@isotopeCode VARCHAR(20),
@reportOnlyLOF BIT,
@reportEntireMBA BIT,
@guarded INT,
@safeguardInd BIT,
@shipping INT,
@receiving INT,
@MBAisLOF INT,
@MBAisNotLOF INT
--create a temporary table to store results
Create Table #TempPhysicalBeginning(
MBACode VARCHAR(4),
PKMPCode VARCHAR(2),
CNSCElementCode VARCHAR(20),
ElementWeight DECIMAL(27,6),
ElementUnit VARCHAR(10),
CNSCIsotopeCode VARCHAR(20),
IsotopeWeight DECIMAL(27,6),
IsotopeUnit VARCHAR(10))
--create a temporary table to store the sum of element and isotope weights
Create Table #MBAInterimOpeningBalance(
ElmIsoKey VARCHAR(40),
CNSCElementCode VARCHAR(20),
ElementWeight DECIMAL(27,6),
ElementUnit VARCHAR(10),
CNSCIsotopeCode VARCHAR(20),
IsotopeWeight DECIMAL(27,6),
IsotopeUnit VARCHAR(10))
--create a temporary table to store transaction records
Create Table #MBABalanceTransactions(
TransKey VARCHAR(40),
MBACode VARCHAR(4),
ElementName VARCHAR(60),
CNSCElementCode VARCHAR(20),
ElementWeight DECIMAL(27,6),
ElementUnit VARCHAR(10),
IsotopeName VARCHAR(60),
CNSCIsotopeCode VARCHAR(20),
IsotopeWeight DECIMAL(27,6),
IsotopeUnit VARCHAR(10))
-- Create a temporary table that contains all the transaction material that meets the search criteria.
-- This table is required to create missing material entries in forward or previous transactions AS there is a need
-- to look at previous transactions. All missing material entry are entered with a zero weight.
CREATE TABLE #TempTransactionMaterial (
TransID UNIQUEIDENTIFIER,
TransMatElmIsoKey VARCHAR(40),
TransMatCNSCElementCode VARCHAR(20),
TransMatElementWeight DECIMAL(27,6),
TransMatElementUnit VARCHAR(4),
TransMatCNSCIsotopeCode VARCHAR(20),
TransMatIsotopeWeight DECIMAL(27,6),
TransMatIsotopeUnit VARCHAR(4),
TransPreviousID UNIQUEIDENTIFIER,
TransMatObligatedCountryCode VARCHAR(2))
-- Initialize variables and constants
SET @counterBefore = 0
SET @counterAfter = 0
SET @guarded = 1
SET @shipping = 1
SET @receiving = 0
--set constants that indicate that a LOF MBA will be reported by whole MBA or component MBA
SET @reportEntireMBA = 1
SET @reportOnlyLOF = 0
SET @MBAisLOF = 1
SET @MBAisNotLOF = 0
-- Get the day before request date
SET @DayBeforeRequestDate = DATEADD(DAY, -1, @RequestDate)
-- Create a cursor of DISTINCT element & isotope combinations gathered from the inventory and transaction tables for
-- a specific MBA
IF @ReportMBAorLOF = @reportOnlyLOF
BEGIN
DECLARE ListOfMaterialsCursor CURSOR
FOR
SELECT DISTINCT *
FROM (
-- Get element/isotope from inventory table
SELECT DISTINCT InvItemCNSCElementCode,
InvItemCNSCIsotopeCode
FROM dbo.listInventoryItem
INNER JOIN dbo.inventoryItem
On dbo.listInventoryItem.listInvItemID = dbo.inventoryItem.listInvItemID
WHERE listInvItemMBACode = @MBACode
AND ListInvItemPKMPCode = @PKMPCode
UNION
-- Get element/isotope from transaction table associated to shipping transactions
SELECT DISTINCT TransMatCNSCElementCode,
TransMatCNSCIsotopeCode
FROM dbo.[transaction]
INNER JOIN dbo.transactionMaterial
On dbo.[transaction].transID = dbo.transactionMaterial.transID
WHERE transMBACode = @MBACode
AND transPKMPCode = @PKMPCode
AND dbo.[transaction].transShippingReceiving_Ind = @shipping
UNION
-- Get element/isotope from transaction table associated to receiving transactions
SELECT DISTINCT TransMatCNSCElementCode,
TransMatCNSCIsotopeCode
FROM dbo.[transaction] AS [receiver]
INNER JOIN dbo.[transaction] AS [shipper]
On [shipper].transID = [receiver].transPeerTransID
INNER JOIN dbo.transactionMaterial
On [shipper].transID = dbo.transactionMaterial.transID
WHERE [receiver].transMBACode = @MBACode
AND [receiver].transPKMPCode = @PKMPCode
AND [receiver].transShippingReceiving_Ind = @receiving
) AS A
End
If @ReportMBAorLOF = @reportEntireMBA
Begin
Declare ListOfMaterialsCursor Cursor
For
SELECT DISTINCT *
FROM (
-- Get element/isotope from inventory table
SELECT DISTINCT InvItemCNSCElementCode,
InvItemCNSCIsotopeCode
FROM dbo.listInventoryItem
INNER JOIN dbo.inventoryItem
On dbo.listInventoryItem.listInvItemID = dbo.inventoryItem.listInvItemID
WHERE listInvItemMBACode = @MBACode
UNION
-- Get element/isotope from transaction table associated to shipping transactions
SELECT DISTINCT TransMatCNSCElementCode,
TransMatCNSCIsotopeCode
FROM dbo.[transaction]
INNER JOIN dbo.transactionMaterial
On dbo.[transaction].transID = dbo.transactionMaterial.transID
WHERE transMBACode = @MBACode
AND dbo.[transaction].transShippingReceiving_Ind = @shipping
UNION
-- Get element/isotope from transaction table associated to receiving transactions
SELECT DISTINCT TransMatCNSCElementCode,
TransMatCNSCIsotopeCode
FROM dbo.[transaction] AS [receiver]
INNER JOIN dbo.[transaction] AS [shipper]
On [shipper].transID = [receiver].transPeerTransID
INNER JOIN dbo.transactionMaterial
On [shipper].transID = dbo.transactionMaterial.transID
WHERE [receiver].transMBACode = @MBACode
AND [receiver].transShippingReceiving_Ind = @receiving
) AS A
End
--loop through the cursor, calling a separate procedure to insert the element and isotope
--weights into the temporary table. If no weights are appended, then insert a value of 0.
OPEN ListOfMaterialsCursor
FETCH NEXT FROM ListOfMaterialsCursor INTO @ElementCode, @IsotopeCode
WHILE @@Fetch_Status = 0
BEGIN
-- Count the number of element/isotope and MBA for which a physical beginning was found
SELECT @CounterBefore = (SELECT COUNT(*) FROM #TempPhysicalBeginning)
-- Get an opening balance for the element/isotope for a specific MBA. If the element/isotope code is guarded
-- then the PB is the PE from the previous MBR, If there are no MBR then get the PB from the inventories.
-- Determine if the element/isotope is IAEA element/isotope
SELECT @safeguardInd = ( SELECT ElementSG_Ind
FROM dbo.Element
WHERE CNSCElementCode = @ElementCode )
--=========
-- CASE 'S'
--=========
-- If the element/isotope code is reportable then the PB is the PE from the previous MBR.
-- If there are no MBR then get the PB from the inventories.
IF (@safeguardedExemptMode = 'S')
BEGIN
SET @PreviousPIVDate = NULL -- initialize to let us know if a record has been added to #TempPhysicalBeginning.
-- Get the element/isotope PB from the MBR
-- For the MBA Ledger and the SIC reports, the MBR should not be included if it exists on the same
-- date AS the request date. Therefore, the day before the request date is used.
IF (@safeguardInd = @guarded) AND (@isMBA_LOF = @MBAisNotLOF)
EXEC nmas_a_subsp_getMBRElementIsotopeWeights
@MBACode,
@DayBeforeRequestDate,
@ElementCode,
@IsotopeCode,
@PreviousPIVDate OUTPUT
-- If no MBR can be found or if the element/isotope is not safeguarded or it is a LOF then get the
-- weights from the PIL or the LIIs if there is no PIL
-- If the element/isotope is reportable and the previous PIV date is null, then this is an indication that there is no MBR report
IF @PreviousPIVDate is Null
EXEC nmas_a_subsp_getPILElementIsotopeWeightsForCBR
@MBACode,
@PKMPCode,
@DayBeforeRequestDate,
@ElementCode,
@IsotopeCode,
@safeguardedExemptMode,
@PreviousPIVDate OUTPUT
END
--===================
-- CASE 'N', 'U', 'Q'
--===================
IF (@safeguardedExemptMode <> 'S')
BEGIN
EXEC nmas_a_subsp_getPILElementIsotopeWeightsForCBR
@MBACode,
@PKMPCode,
@DayBeforeRequestDate,
@ElementCode,
@IsotopeCode,
@safeguardedExemptMode,
@PreviousPIVDate OUTPUT
END
-- Determine if a physical beginning was found for the element/isotope by counting the entries in the temporary table
SELECT @CounterAfter = (SELECT count(*) FROM #TempPhysicalBeginning)
-- Determine if an opening balance was found for the element/isotope else add an opening balance of 0 for the weight in the temporary table
IF @CounterBefore = @CounterAfter
BEGIN
--insert a line with zero weights for this element-isotope combination
INSERT INTO #TempPhysicalBeginning
(
MBACode,
PKMPCode,
CNSCElementCode,
ElementWeight,
CNSCIsotopeCode,
IsotopeWeight
)
VALUES (
@MBACode,
@PKMPCode,
@ElementCode,
0,
@IsotopeCode,
0
)
END
-- Call sub procedure to get the transactions between the Previous PIV date and the requested date into a temporary table
-- With the transaction gathered, the weight will be added to the opening balance to have the exact balance at the requested date
EXEC nmas_a_subsp_getMBABalanceTransactionsForCBR @MBACode,
NULL,
@PKMPCode,
@ElementCode,
@IsotopeCode,
@DayBeforeRequestDate,
@PreviousPIVDate,
@ApplicationMode,
@SafeguardedExemptMode
--fetch the next element-isotope
FETCH NEXT FROM ListOfMaterialsCursor INTO @ElementCode, @IsotopeCode
END
--clean up the cursor object
CLOSE ListOfMaterialsCursor
DEALLOCATE ListOfMaterialsCursor
-- Add the unit to the #TempPhysicalBeginning table when missing. The unit are not inserted when
-- an opening balance of 0 was entered for an elment isotope
UPDATE #TempPhysicalBeginning
SET ElementUnit = ( SELECT ElementUnit
FROM Element
WHERE Element.CNSCElementCode = #TempPhysicalBeginning.CNSCElementCode )
WHERE ElementUnit IS NULL
UPDATE #TempPhysicalBeginning
SET IsotopeUnit = ( SELECT IsotopeUnit
FROM Isotope
WHERE Isotope.CNSCIsotopeCode = #TempPhysicalBeginning.CNSCIsotopeCode )
WHERE IsotopeUnit IS NULL
AND CNSCIsotopeCode IS NOT NULL
--summarize the element-isotope weights and insert into a second temp table
INSERT #MBAInterimOpeningBalance
SELECT CASE When CNSCIsotopeCode IS NULL
THEN CNSCElementCode
ELSE CNSCElementCode + CNSCIsotopeCode
END AS ElmIsoKey,
CNSCElementCode,
SUM(ElementWeight) AS [ElementWeight],
ElementUnit,
CNSCIsotopeCode,
SUM(IsotopeWeight) AS [IsotopeWeight],
IsotopeUnit
FROM #TempPhysicalBeginning
GROUP BY CNSCElementCode, CNSCIsotopeCode, ElementUnit, IsotopeUnit
--Combine the Inventory table and the Transaction data table to get the Opening Balance before the FROM Date
INSERT #MBAOpeningBalance
SELECT [OpBal].ElmIsoKey,
[OpBal].CNSCElementCode,
CASE WHEN [BalTrans].ElementWeight IS NULL
THEN [OpBal].ElementWeight
ELSE [OpBal].ElementWeight + [BalTrans].ElementWeight
END AS SumElementWeight,
[OpBal].ElementUnit,
[OpBal].CNSCIsotopeCode,
CASE WHEN [BalTrans].IsotopeWeight IS NULL
THEN [OpBal].IsotopeWeight
ELSE [OpBal].IsotopeWeight + [BalTrans].IsotopeWeight
END AS SumIsotopeWeight,
[OpBal].IsotopeUnit
FROM #MBAInterimOpeningBalance AS [OpBal]
LEFT OUTER JOIN #MBABalanceTransactions AS [BalTrans]
ON [BalTrans].TransKey = [OpBal].ElmISoKey
END
--end stored procedure
