USE [NMAS]
GO
/****** Object: StoredProcedure [dbo].[nmas_a_subsp_getMBALOFLedgerTransactions] Script Date: 2023-11-21 11:05:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[nmas_a_subsp_getMBALOFLedgerTransactions]
--'**************************************************************************************************************************
--* Created by:
--*
--* Purpose: This sub stored procedure IS used to get the transaction data for the MBA/LOF ledger
--*
--* CVS File: $RCSfile: dbo.nmas_a_subsp_getMBALOFLedgerTransactions.PRC,v $
--* Commit Date: $Date: 2009/03/17 18:33:45 $ (UTC)
--* Committed by: $Author: mullerr $
--* CVS Revision: $Revision: 1.13 $
--* Checkout Tag: $Name: Version_1-3-3-1 $ (Version/Build)
--*
--'**************************************************************************************************************************
--******************************* R E V I S I O N H I S T O R Y *******************************
--*************************************************************************************************
--*************************************************************************************************
-- Revision Nbr: 2
-- Revision Date: 03/Jul/2012
-- Revised By: Mark Hughes
-- Revision INfo: Added ReceiverBatchName IN output for Internal transactions
--*************************************************************************************************
**************************************************************************************************************************/
--INPUTS
@MBACode VARCHAR(4),
--the MBA Code
@ReportMBAorLOF bit,
--flag indicating that the MBA IS a LOF
@PKMPCode VARCHAR(2),
--individual component of the LOF to report on
@fromDate DATETIME,
--start date of the requested period
@toDate DATETIME,
--end date of the requested period
@ApplicationMode CHAR,
--flag indicating reporting mode (0 - by Difference, 1 - by Virtual Replacement)
@SafeguardedExemptMode VARCHAR(1)
-- Safeguarded {S|s}, Non-Safeguarded {N|n}, Exempted Use {U|u}, Exempted Quantity {Q|q}
AS
BEGIN
DECLARE
@guarded INT,
@shipping INT,
@receiving INT,
@byVirtualReplacement INT,
@byDifference INT,
@latestRev INT,
@notExcluded INT,
@received INT,
@reportEntireMBA INT,
@reportOnlyLOF INT,
@transRA INT,
@notTransRA INT,
@formReceived INT
--set constants
SET @byVirtualReplacement = 1
SET @byDifference = 0
SET @guarded = 1
SET @shipping = 1
SET @receiving = 0
SET @latestRev = 1
SET @notExcluded = 0
SET @received = 1
SET @reportEntireMBA = 1
SET @reportOnlyLOF = 0
SET @transRA = 1
SET @notTransRA = 0
SET @formReceived = 1
--set constants that indicate that a LOF MBA will be reported by whole MBA OR component MBA
SET @ReportEntireMBA = 1
SET @ReportOnlyLOF = 0
SET @SafeguardedExemptMode = UPPER(@SafeguardedExemptMode)
--=======================================================================================
-- Create a temporary table that contains all the transaction material that meets the serarch 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))
--=======================================================================================
-- Get the list of material for shipper AND receiver transactions into a single table that use the transID
-- of the shipper AND receiver transactions AS the reference to them rather than only the shipper transID
--=======================================================================================
EXEC nmas_0_subsp_getShipperReceiverMaterial @MBACode, @FromDate, @ToDate
--=======================================================================================
--Get the transactions for By Difference mode
--Use the transaction date of the latest transaction revision to select records
--=======================================================================================
IF @ApplicationMode = @byDifference
BEGIN
-- For By Difference, there IS a need to backfill previous OR forward transactions that have extra material
-- on it following an addition between revision OR deletion of a line of material between revision.
exec nmas_0_subsp_getByDiffShipperReceiverMaterial
--=======================================================================================
-- Get transactions when it IS for the entire MBA
--=======================================================================================
IF @ReportMBAorLOF = @reportEntireMBA
BEGIN
INSERT INTO #LedgerTransactions
-- Get all the Internal transactions corresponding to the MBA AND RA related to internal transactions
SELECT [transMat].TransMatElmIsoKey AS TransKey,
@ApplicationMode AS [Mode],
null AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[shipper].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
NULL AS [ShipperBatchName],
NULL AS [FromMBACode],
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
-- Receiver INformation
[shipper].TransBatchName AS [ReceiverBatchName], -- changed from null Jul 3,2012 Mark Hughes
[shipper].transMBACode AS [ToMBACode],
[shipper].transCountryCode AS [ToCountryCode],
[shipper].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
CASE
WHEN shipper.TransIAEALAbel309 IN ('C', 'A', 'N') AND shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U', 'D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND PreviousTrans.TransIAEALabel309 = 'U' AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight)
ELSE
(SELECT transMat.TransMatElementWeight)
END AS ElementWeight,
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
CASE
WHEN shipper.TransIAEALAbel309 IN ('C', 'A', 'N') AND shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U', 'D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND PreviousTrans.TransIAEALabel309 = 'U' AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight)
ELSE
(SELECT transMat.TransMatIsotopeWeight)
END AS IsotopeWeight,
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
0
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
CASE
WHEN originalTransMat.TransMatElementWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransElementSign,
CASE
WHEN originalTransMat.TransMatCNSCIsotopeCode IS NULL THEN
NULL
WHEN originalTransMat.TransMatIsotopeWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN #TempTransactionMaterial AS [transMat]
ON [shipper].transID = [transMat].transID
INNER JOIN dbo.[transaction] AS [original]
ON [original].transID = [shipper].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [originalTransMat]
ON [original].transID = [originalTransMat].transID
AND [originalTransMat].TransMatElmIsoKey = transMat.TransMatElmIsoKey
INNER JOIN ICTypeRef AS [IC]
ON [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
ON [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT Outer JOIN dbo.isotope AS [I]
ON [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
LEFT OUTER JOIN dbo.[Transaction] AS PreviousTrans
ON shipper.TransIAEAPreviousTransID = PreviousTrans.TransID
LEFT OUTER JOIN #TempTransactionMaterial AS PreviousTransMat
ON PreviousTransMat.TransID = PreviousTrans.TransID
AND PreviousTransMat.TransMatElmIsoKey = transMat.TransMatElmIsoKey
WHERE shipper.transMBACode = @MBACode
AND shipper.transDate >= @fromDate
AND shipper.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_INd = @notExcluded
AND shipper.transRA_INd = @notTransRA
AND shipper.transType IN ('Internal')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 THEN 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 THEN 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N' AND [E].elementSG_INd = 0 THEN 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 THEN 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 THEN 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
UNION --ALL
-- Get all the Domestic AND Export shipping transactions corresponding to the MBA AND RA reletaed to Domestic AND Export shipper transactions
SELECT [transMat].TransMatElmIsoKey AS TransKey,
@ApplicationMode AS [Mode],
NULL AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[shipper].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
CASE
WHEN shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
ELSE
(SELECT transMat.TransMatElementWeight)
END AS ElementWeight,
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
CASE
WHEN shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
ELSE
(SELECT transMat.TransMatIsotopeWeight)
END AS IsotopeWeight,
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL) THEN
0
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN dbo.[transaction] AS [receiver]
ON [receiver].transID = [shipper].transPeerTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
ON [shipper].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
ON [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
ON [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
ON [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
LEFT OUTER JOIN dbo.[Transaction] AS PreviousTrans
ON shipper.TransIAEAPreviousTransID = PreviousTrans.TransID
LEFT OUTER JOIN #TempTransactionMaterial AS PreviousTransMat
ON PreviousTransMat.TransID = PreviousTrans.TransID
AND PreviousTransMat.TransMatElmIsoKey = transMat.TransMatElmIsoKey
WHERE shipper.transMBACode = @MBACode
AND shipper.transDate >= @fromDate
AND shipper.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_ind = @notExcluded
AND shipper.transRA_INd = @notTransRA
AND shipper.transType IN ('Domestic', 'Export')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' THEN
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 THEN 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 THEN 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 THEN 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 THEN 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 THEN 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for exports)
UNION --ALL
-- Get all the Domestic AND Import receiving transactions
SELECT [transMat].TransMatElmIsoKey AS TransKey,
@ApplicationMode AS [Mode],
NULL AS [PKMPCode],
-- Receiver specific information
[receiver].transFKMPCode AS [FlowKMPCode],
[receiver].transIAEALabel309 AS [Label309],
[receiver].transRevisionNumber AS [RevisionNumber],
[receiver].transICTypeCode AS [ICType],
[receiver].transDate AS [TransactionDate],
[receiver].transMDC AS [MaterialDescriptionCode],
[receiver].transNumberOfItems AS [numberOfItems],
[receiver].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
CASE
WHEN receiver.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatElementWeight)
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight))
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatElementWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
ELSE
(SELECT transMat.TransMatElementWeight)
END AS ElementWeight,
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
CASE
WHEN receiver.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight))
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatIsotopeWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
ELSE
(SELECT transMat.TransMatIsotopeWeight)
END AS IsotopeWeight,
CASE
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND receiver.TransIAEAPreviousTransID IS Null) THEN
0
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [receiver]
INNER JOIN dbo.[transaction] AS [shipper]
ON [shipper].transID = [receiver].transPeerTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
ON [receiver].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
ON [IC].ICTypeRefCode = [receiver].transICTypeCode
INNER JOIN dbo.element AS [E]
ON [E].CNSCElementCode = [transMat].transMatCNSCElementCode
Left OUTER JOIN dbo.isotope AS [I]
ON [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
Left OUTER JOIN dbo.[Transaction] AS PreviousTrans
ON receiver.TransIAEAPreviousTransID = PreviousTrans.TransID
Left OUTER JOIN #TempTransactionMaterial AS PreviousTransMat
ON PreviousTransMat.TransID = PreviousTrans.TransID
AND PreviousTransMat.TransMatElmIsoKey = transMat.TransMatElmIsoKey
WHERE receiver.transMBACode = @MBACode
AND receiver.transDate >= @fromDate
AND receiver.transDate <= @toDate
AND receiver.transShippingReceiving_INd = @receiving
AND receiver.transExclude_ind = @notExcluded
AND receiver.transRA_INd = @notTransRA
AND receiver.transType IN ('Domestic', 'Import')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN receiver.transExemptionType IS NULL AND [E].elementSG_INd = 1 THEN 1
WHEN receiver.transExemptionType IN ('U', 'Q') AND receiver.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 THEN 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N' AND [E].elementSG_INd = 0 THEN 0
WHEN @SafeguardedExemptMode = 'U' AND receiver.transExemptionType = 'U' AND [E].elementSG_INd = 1 THEN 1
WHEN @SafeguardedExemptMode = 'Q' AND receiver.transExemptionType = 'Q' AND [E].elementSG_INd = 1 THEN 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND receiver.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for imports)
UNION --ALL
-- Get all the RA transactions (Whole MBA & By difference)
SELECT
CASE
WHEN TransMatCNSCIsotopeCode IS NULL THEN
TransMatCNSCElementCode
ELSE
TransMatCNSCElementCode + TransMatCNSCIsotopeCode
END AS TransKey,
@ApplicationMode AS [Mode],
[RATransaction].[TransPKMPCode] AS [PKMPCode],
CASE -- Try to match this RA to an FKMP based on MBA/Batch/Date/IC AND only One chain
WHEN
(-- Count the distinct rows
SELECT Count(*) AS DistinctChainCount
FROM
(-- Get matching rows
SELECT DISTINCT
[MatchFKMPtoRA].TransMBACode,
[MatchFKMPtoRA].TransBatchName,
[MatchFKMPtoRA].TransDate,
[MatchFKMPtoRA].TransICTypeCode,
[MatchFKMPtoRA].TransIAEAORiginalTransID,
[MatchFKMPtoRA].TransFKMPCode
FROM [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0) AS CountingDistinctChains -- alias to wrap AND count
) = 1 THEN
-- We have only one matching chain, lets get the FKMP now
-- top 1 IN case chain has same info including date matching (would have been reduced to one row IN distinct counting above
(SELECT TOP 1 [MatchFKMPtoRA].TransFKMPCode
FROM [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0)
ELSE
-- We have no matches OR there IS more than one chain with same info - make FKMP NULL
NULL
END AS [FlowKMPCode],
NULL AS [Label309],
NULL AS [RevisionNumber],
'RA' + [RATransaction].transICTypeCode AS [ICType],
[RATransaction].transDate AS [TransactionDate],
NULL AS [MaterialDescriptionCode],
NULL AS [numberOfItems],
NULL AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN
[ICTypeRef].ICTypeRefSign
ELSE
[ICTypeRef].ICTYpeRefExemptedSign
END AS [ICSign],
CASE
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ShipperBatchName],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].transMBACode
ELSE
NULL
End AS [FromMBACode],
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
Case
WHEN [RATransaction].TransType IN ('Internal', 'Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ReceiverBatchName],
Case
WHEN [RATransaction].TransType IN ('Internal', 'Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].transMBACode
ELSE
NULL
End AS [ToMBACode],
NULL AS [ToCountryCode],
NULL AS [ToFacilityCode],
-- Material information
TransMatCNSCElementCode AS [ElementCode],
[Element].elementName AS [ElementName],
[Element].elementUnit AS [ElementUnit],
TransMatElementWeight AS ElementWeight,
TransMatCNSCIsotopeCode AS [IsotopeCode],
[Isotope].IsotopeName AS [IsotopeName],
[Isotope].IsotopeUnit AS [IsotopeUnit],
TransMatIsotopeWeight AS IsotopeWeight,
1 AS SumFlag,
1 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
From
[Transaction] AS [RATransaction] -- Aliased to use IN the subquery for FKMP
INNER JOIN
[TransactionMaterial]
On [RATransaction].[TransID]=[TransactionMaterial].[TransID]
INNER JOIN
[Element]
On [TransactionMaterial].[TransMatCNSCElementCode]=[Element].[CNSCElementCode]
LEFT OUTER JOIN
[Isotope] -- Isotope code may be NULL IN 'left'
On [TransactionMaterial].[TransMatCNSCIsotopeCode]=[Isotope].[CNSCIsotopeCode]
INNER JOIN
[ICTypeRef]
On [RATransaction].[TransICTypeCode]=[ICTypeRef].[ICTypeRefCode]
WHERE
transMBACode = @MBACode
AND transDate >= @fromDate
AND transDate <= @toDate
AND transRA_INd = @transRA
AND [Element].elementSG_INd = -- 0, 1, OR NULL IS tested depending on ExemptMode (NULL never matches!)
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN transExemptionType IS NULL AND [Element].elementSG_INd = 1 then 1
WHEN transExemptionType IN ('U', 'Q') AND TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [Element].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND transExemptionType = 'U' AND [Element].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND transExemptionType = 'Q' AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
ORDER BY ICType
END -- entire MBA
--=======================================================================================
-- Get transactions when it IS for a specifc LOF OR PKMP
--=======================================================================================
If @reportMBAorLOF = @reportOnlyLOF
BEGIN
INSERT INTO #LedgerTransactions
-- Get all the Internal transactions corresponding to the MBA AND RA related to internal transactions
SELECT DISTINCT [transMat].TransMatElmIsoKey AS TransKey,
@ApplicationMode AS [Mode],
[shipper].transPKMPCode AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[shipper].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
NULL AS [BatchName],
NULL AS [FromMBACode],
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
-- Receiver INformation
[shipper].TransBatchName AS [ReceiverBatchName],
[shipper].transMBACode AS [ToMBACode],
[shipper].transCountryCode AS [ToCountryCode],
[shipper].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
CASE
WHEN shipper.TransIAEALAbel309 IN ('C', 'A', 'N') AND shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U', 'D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND PreviousTrans.TransIAEALabel309 = 'U' AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight)
ELSE
(SELECT transMat.TransMatElementWeight)
END AS ElementWeight,
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
CASE
WHEN shipper.TransIAEALAbel309 IN ('C', 'A', 'N') AND shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U', 'D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-')) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 = 'U' AND PreviousTrans.TransIAEALabel309 = 'U' AND shipper.TransICTypeCode IN ('R+', 'R-', 'L+', 'L-') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight)
ELSE
(SELECT transMat.TransMatIsotopeWeight)
END AS IsotopeWeight,
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS NULL AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
0
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
CASE
WHEN originalTransMat.TransMatElementWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransElementSign,
CASE
WHEN originalTransMat.TransMatCNSCIsotopeCode IS NULL THEN
Null
WHEN originalTransMat.TransMatIsotopeWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN #TempTransactionMaterial AS [transMat]
on [shipper].transID = [transMat].transID
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [shipper].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [originalTransMat]
on [original].transID = [originalTransMat].transID
AND [originalTransMat].TransMatElmIsoKey = transMat.TransMatElmIsoKey
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
LEFT OUTER JOIN dbo.[Transaction] AS PreviousTrans
On shipper.TransIAEAPreviousTransID = PreviousTrans.TransID
LEFT OUTER JOIN #TempTransactionMaterial AS PreviousTransMat
On PreviousTransMat.TransID = PreviousTrans.TransID
AND PreviousTransMat.TransMatElmIsoKey = transMat.TransMatElmIsoKey
WHERE shipper.transMBACode = @MBACode
AND shipper.transDate >= @fromDate
AND shipper.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_INd = @notExcluded
AND shipper.transRA_INd = @notTransRA
AND shipper.transPKMPCode = @PKMPCode
AND shipper.transType IN ('Internal')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
UNION --ALL
-- Get all the Domestic AND Export shipping transactions corresponding to the MBA AND RA reletaed to Domestic AND Export shipper transactions
SELECT [transMat].TransMatElmIsoKey AS TransKey,
@ApplicationMode AS [Mode],
[shipper].transPKMPCode AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[shipper].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
CASE
WHEN shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatElementWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
ELSE
(SELECT transMat.TransMatElementWeight)
END AS ElementWeight,
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
CASE
WHEN shipper.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight))
WHEN (shipper.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatIsotopeWeight)
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
ELSE
(SELECT transMat.TransMatIsotopeWeight)
END AS IsotopeWeight,
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransIAEAPreviousTransID IS Null) THEN
0
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN dbo.[transaction] AS [receiver]
on [receiver].transID = [shipper].transPeerTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [shipper].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
LEFT OUTER JOIN dbo.[Transaction] AS PreviousTrans
On shipper.TransIAEAPreviousTransID = PreviousTrans.TransID
LEFT OUTER JOIN #TempTransactionMaterial AS PreviousTransMat
On PreviousTransMat.TransID = PreviousTrans.TransID
AND PreviousTransMat.TransMatElmIsoKey = transMat.TransMatElmIsoKey
WHERE shipper.transMBACode = @MBACode
AND shipper.transDate >= @fromDate
AND shipper.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_ind = @notExcluded
AND shipper.transRA_INd = @notTransRA
AND shipper.transPKMPCode = @PKMPCode
AND shipper.transType IN ('Domestic', 'Export')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for exports)
UNION --ALL
-- Get all the Domestic AND Import receiving transactions
SELECT [transMat].TransMatElmIsoKey AS TransKey,
@ApplicationMode AS [Mode],
[receiver].transPKMPCode AS [PKMPCode],
-- Receiver specific information
[receiver].transFKMPCode AS [FlowKMPCode],
[receiver].transIAEALabel309 AS [Label309],
[receiver].transRevisionNumber AS [RevisionNumber],
[receiver].transICTypeCode AS [ICType],
[receiver].transDate AS [TransactionDate],
[receiver].transMDC AS [MaterialDescriptionCode],
[receiver].transNumberOfItems AS [numberOfItems],
[receiver].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
CASE
WHEN receiver.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatElementWeight)
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatElementWeight - PreviousTransMat.TransMatElementWeight))
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatElementWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatElementWeight)
ELSE
(SELECT transMat.TransMatElementWeight)
END AS ElementWeight,
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
CASE
WHEN receiver.TransIAEAPreviousTransID IS NULL THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT (transMat.TransMatIsotopeWeight - PreviousTransMat.TransMatIsotopeWeight))
WHEN (receiver.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('C','A','N') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT -1 * transMat.TransMatIsotopeWeight)
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
(SELECT transMat.TransMatIsotopeWeight)
ELSE
(SELECT transMat.TransMatIsotopeWeight)
END AS IsotopeWeight,
CASE
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND receiver.TransIAEAPreviousTransID IS Null) THEN
0
WHEN (receiver.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransIAEALabel309 IN ('U','D') AND PreviousTrans.TransExclude_INd = @notExcluded) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [receiver]
inner JOIN dbo.[transaction] AS [shipper]
on [shipper].transID = [receiver].transPeerTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [receiver].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [receiver].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
LEFT OUTER JOIN dbo.[Transaction] AS PreviousTrans
On receiver.TransIAEAPreviousTransID = PreviousTrans.TransID
LEFT OUTER JOIN #TempTransactionMaterial AS PreviousTransMat
On PreviousTransMat.TransID = PreviousTrans.TransID
AND PreviousTransMat.TransMatElmIsoKey = transMat.TransMatElmIsoKey
WHERE receiver.transMBACode = @MBACode
AND receiver.transDate >= @fromDate
AND receiver.transDate <= @toDate
AND receiver.transShippingReceiving_INd = @receiving
AND receiver.transExclude_ind = @notExcluded
AND receiver.transRA_INd = @notTransRA
AND receiver.transPKMPCode = @PKMPCode
AND receiver.transType IN ('Domestic', 'Import')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN receiver.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN receiver.transExemptionType IN ('U', 'Q') AND receiver.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND receiver.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND receiver.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for imports)
UNION --ALL
-- Get all the RA transactions (LOF & By Difference)
Select
Case
WHEN TransMatCNSCIsotopeCode IS NULL THEN
TransMatCNSCElementCode
ELSE
TransMatCNSCElementCode + TransMatCNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
[RATransaction].[TransPKMPCode] AS [PKMPCode],
Case -- Try to match this RA to an FKMP based on MBA/Batch/Date/IC AND only One chain
WHEN
(-- Count the distinct rows
Select Count(*) AS DistinctChainCount
From
(-- Get matching rows
Select Distinct
[MatchFKMPtoRA].TransMBACode,
[MatchFKMPtoRA].TransBatchName,
[MatchFKMPtoRA].TransDate,
[MatchFKMPtoRA].TransICTypeCode,
[MatchFKMPtoRA].TransIAEAORiginalTransID,
[MatchFKMPtoRA].TransFKMPCode
From [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0) AS CountingDistinctChains -- alias to wrap AND count
) = 1 THEN
-- We have only one matching chain, lets get the FKMP now
-- top 1 IN case chain has same info including date matching (would have been reduced to one row IN distinct counting above
(Select Top 1 [MatchFKMPtoRA].TransFKMPCode
From [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0)
ELSE
-- We have no matches OR there IS more than one chain with same info - make FKMP NULL
NULL
End AS [FlowKMPCode],
NULL AS [Label309],
NULL AS [RevisionNumber],
'RA' + [RATransaction].transICTypeCode AS [ICType],
[RATransaction].transDate AS [TransactionDate],
NULL AS [MaterialDescriptionCode],
NULL AS [numberOfItems],
NULL AS [MeasurementBasis],
Case
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN
[ICTypeRef].ICTypeRefSign
ELSE
[ICTypeRef].ICTYpeRefExemptedSign
End AS [ICSign],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ShipperBatchName],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].transMBACode
ELSE
NULL
End AS [FromMBACode],
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
Case
WHEN [RATransaction].TransType IN ('Internal','Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ReceiverBatchName],
Case
WHEN [RATransaction].TransType IN ('Internal','Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].transMBACode
ELSE
NULL
End AS [ToMBACode],
NULL AS [ToCountryCode],
NULL AS [ToFacilityCode],
-- Material information
TransMatCNSCElementCode AS [ElementCode],
[Element].elementName AS [ElementName],
[Element].elementUnit AS [ElementUnit],
TransMatElementWeight AS ElementWeight,
TransMatCNSCIsotopeCode AS [IsotopeCode],
[Isotope].IsotopeName AS [IsotopeName],
[Isotope].IsotopeUnit AS [IsotopeUnit],
TransMatIsotopeWeight AS IsotopeWeight,
1 AS SumFlag,
1 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
From
[Transaction] AS [RATransaction] -- Aliased to use IN the subquery for FKMP
INNER JOIN
[TransactionMaterial]
On [RATransaction].[TransID]=[TransactionMaterial].[TransID]
INNER JOIN
[Element]
On [TransactionMaterial].[TransMatCNSCElementCode]=[Element].[CNSCElementCode]
LEFT OUTER JOIN
[Isotope] -- Isotope code may be NULL IN 'left'
On [TransactionMaterial].[TransMatCNSCIsotopeCode]=[Isotope].[CNSCIsotopeCode]
INNER JOIN
[ICTypeRef]
On [RATransaction].[TransICTypeCode]=[ICTypeRef].[ICTypeRefCode]
WHERE
transMBACode = @MBACode
AND transPKMPCode = @PKMPCode -- LOF only
AND transDate >= @fromDate
AND transDate <= @toDate
AND transRA_INd = @transRA
AND [Element].elementSG_INd = -- 0, 1, OR NULL IS tested depending on ExemptMode (NULL never matches!)
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN transExemptionType IS NULL AND [Element].elementSG_INd = 1 then 1
WHEN transExemptionType IN ('U', 'Q') AND TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [Element].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND transExemptionType = 'U' AND [Element].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND transExemptionType = 'Q' AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
ORDER BY ICType
END -- specific LOF (or PKMP)
END -- report mode by Difference
--=======================================================================================
--Get the transactions for By Virtual Replacement Mode
--Use the transaction date of the latest transaction revision to select records
--=======================================================================================
If @ApplicationMode = @byVirtualReplacement
BEGIN
If @ReportMBAorLOF = @reportEntireMBA
BEGIN
INSERT INTO #LedgerTransactions
-- Get all the Internal transactions corresponding to the MBA
SELECT CASE WHEN [I].CNSCIsotopeCode IS NULL THEN
[E].CNSCElementCode
ELSE
[E].CNSCElementCode + [I].CNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
@PKMPCode AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[original].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
NULL AS [ShipperBatchName],
NULL AS [FromMBACode],
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
-- Receiver INformation
[shipper].TransBatchName AS [ReceiverBatchName],
[shipper].transMBACode AS [ToMBACode],
[shipper].transCountryCode AS [ToCountryCode],
[shipper].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
[transMat].transMatElementWeight AS [ElementWeight],
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
[transMat].transMatIsotopeWeight AS [IsotopeWeight],
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
CASE
WHEN [transMat].transMatElementWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransElementSign,
CASE
WHEN [transMat].TransMatCNSCIsotopeCode IS NULL THEN
Null
WHEN [transMat].transMatIsotopeWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [shipper].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [shipper].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
WHERE shipper.transMBACode = @MBACode
AND original.transDate >= @fromDate
AND original.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_ind = @notExcluded
AND shipper.transRecordLatestRev_INd = @latestRev
AND shipper.transRA_INd = @notTransRA
AND shipper.transType IN ('Internal')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
UNION --ALL
-- Get all the Domestic AND Export shipping transactions corresponding to the MBA
SELECT CASE WHEN [I].CNSCIsotopeCode IS NULL THEN
[E].CNSCElementCode
ELSE
[E].CNSCElementCode + [I].CNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
@PKMPCode AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[original].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
[transMat].transMatElementWeight AS [ElementWeight],
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
[transMat].transMatIsotopeWeight AS [IsotopeWeight],
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN dbo.[transaction] AS [receiver]
on [receiver].transID = [shipper].transPeerTransID
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [shipper].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [shipper].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
WHERE shipper.transMBACode = @MBACode
AND original.transDate >= @fromDate
AND original.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_ind = @notExcluded
AND shipper.transRecordLatestRev_INd = @latestRev
AND shipper.transRA_INd = @notTransRA
AND shipper.transType IN ('Domestic', 'Export')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for exports)
UNION --ALL
-- Get all the Domestic AND Import receiving transactions
SELECT CASE WHEN [I].CNSCIsotopeCode IS NULL
THEN [E].CNSCElementCode
ELSE [E].CNSCElementCode + [I].CNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
@PKMPCode AS [PKMPCode],
-- Receiver specific information
[receiver].transFKMPCode AS [FlowKMPCode],
[receiver].transIAEALabel309 AS [Label309],
[receiver].transRevisionNumber AS [RevisionNumber],
[receiver].transICTypeCode AS [ICType],
[original].transDate AS [TransactionDate],
[receiver].transMDC AS [MaterialDescriptionCode],
[receiver].transNumberOfItems AS [numberOfItems],
[receiver].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
[transMat].transMatElementWeight AS [ElementWeight],
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
[transMat].transMatIsotopeWeight AS [IsotopeWeight],
CASE
WHEN (receiver.TransIAEALabel309 IN ('U','D')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [receiver]
INNER JOIN dbo.[transaction] AS [shipper]
on [shipper].transID = [receiver].transPeerTransID
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [receiver].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [receiver].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [receiver].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
WHERE receiver.transMBACode = @MBACode
AND original.transDate >= @fromDate
AND original.transDate <= @toDate
AND receiver.transShippingReceiving_INd = @receiving
AND receiver.transExclude_ind = @notExcluded
AND receiver.transRecordLatestRev_INd = @latestRev
AND receiver.transRA_INd = @notTransRA
AND receiver.transType IN ('Domestic', 'Import')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN receiver.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN receiver.transExemptionType IN ('U', 'Q') AND receiver.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND receiver.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND receiver.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for imports)
UNION --ALL
-- Get all the RA transactions (Whole MBA AND VR)
Select
Case
WHEN TransMatCNSCIsotopeCode IS NULL THEN
TransMatCNSCElementCode
ELSE
TransMatCNSCElementCode + TransMatCNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
[RATransaction].[TransPKMPCode] AS [PKMPCode],
Case -- Try to match this RA to an FKMP based on MBA/Batch/Date/IC AND only One chain
WHEN
(-- Count the distinct rows
Select Count(*) AS DistinctChainCount
From
(-- Get matching rows
Select Distinct
[MatchFKMPtoRA].TransMBACode,
[MatchFKMPtoRA].TransBatchName,
[MatchFKMPtoRA].TransDate,
[MatchFKMPtoRA].TransICTypeCode,
[MatchFKMPtoRA].TransIAEAORiginalTransID,
[MatchFKMPtoRA].TransFKMPCode
From [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0) AS CountingDistinctChains -- alias to wrap AND count
) = 1 THEN
-- We have only one matching chain, lets get the FKMP now
-- top 1 IN case chain has same info including date matching (would have been reduced to one row IN distinct counting above
(Select Top 1 [MatchFKMPtoRA].TransFKMPCode
From [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0)
ELSE
-- We have no matches OR there IS more than one chain with same info - make FKMP NULL
NULL
End AS [FlowKMPCode],
NULL AS [Label309],
NULL AS [RevisionNumber],
'RA' + [RATransaction].transICTypeCode AS [ICType],
[RATransaction].transDate AS [TransactionDate],
NULL AS [MaterialDescriptionCode],
NULL AS [numberOfItems],
NULL AS [MeasurementBasis],
Case
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN
[ICTypeRef].ICTypeRefSign
ELSE
[ICTypeRef].ICTYpeRefExemptedSign
End AS [ICSign],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ShipperBatchName],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].transMBACode
ELSE
NULL
End AS FromMBACode,
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
Case
WHEN [RATransaction].TransType IN ('Internal','Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ReceiverBatchName],
Case
WHEN [RATransaction].TransType IN ('Internal','Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].TYransMBACode
ELSE
NULL
End AS [ToMBACode],
NULL AS [ToCountryCode],
NULL AS [ToFacilityCode],
-- Material information
TransMatCNSCElementCode AS [ElementCode],
[Element].elementName AS [ElementName],
[Element].elementUnit AS [ElementUnit],
TransMatElementWeight AS ElementWeight,
TransMatCNSCIsotopeCode AS [IsotopeCode],
[Isotope].IsotopeName AS [IsotopeName],
[Isotope].IsotopeUnit AS [IsotopeUnit],
TransMatIsotopeWeight AS IsotopeWeight,
1 AS SumFlag,
1 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
From
[Transaction] AS [RATransaction] -- Aliased to use IN the subquery for FKMP
INNER JOIN
[TransactionMaterial]
On [RATransaction].[TransID]=[TransactionMaterial].[TransID]
INNER JOIN
[Element]
On [TransactionMaterial].[TransMatCNSCElementCode]=[Element].[CNSCElementCode]
LEFT OUTER JOIN
[Isotope] -- Isotope code may be NULL IN 'left'
On [TransactionMaterial].[TransMatCNSCIsotopeCode]=[Isotope].[CNSCIsotopeCode]
INNER JOIN
[ICTypeRef]
On [RATransaction].[TransICTypeCode]=[ICTypeRef].[ICTypeRefCode]
WHERE
transMBACode = @MBACode
AND transDate >= @fromDate
AND transDate <= @toDate
AND transRA_INd = @transRA
AND [Element].elementSG_INd = -- 0, 1, OR NULL IS tested depending on ExemptMode (NULL never matches!)
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN transExemptionType IS NULL AND [Element].elementSG_INd = 1 then 1
WHEN transExemptionType IN ('U', 'Q') AND TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [Element].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND transExemptionType = 'U' AND [Element].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND transExemptionType = 'Q' AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
ORDER BY ICType
END -- entire MBA
--=======================================================================================
-- Get transactions when it IS for a specifc LOF OR PKMP
--=======================================================================================
If @reportMBAorLOF = @reportOnlyLOF
BEGIN
INSERT INTO #LedgerTransactions
-- Get all the Internal transactions corresponding to the MBA
SELECT CASE WHEN [I].CNSCIsotopeCode IS NULL THEN
[E].CNSCElementCode
ELSE
[E].CNSCElementCode + [I].CNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
@PKMPCode AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[original].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
NULL AS [ShipperBatchName],
NULL AS [FromMBACode],
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
-- Receiver INformation
[shipper].TransBatchName AS [ReceiverBatchName],
[shipper].transMBACode AS [ToMBACode],
[shipper].transCountryCode AS [ToCountryCode],
[shipper].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
[transMat].transMatElementWeight AS [ElementWeight],
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
[transMat].transMatIsotopeWeight AS [IsotopeWeight],
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D') AND shipper.TransICTypeCode NOT IN ('R+', 'R-', 'L+', 'L-')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
CASE
WHEN [transMat].transMatElementWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransElementSign,
CASE
WHEN [transMat].TransMatCNSCIsotopeCode IS NULL THEN
Null
WHEN [transMat].transMatIsotopeWeight < 0 THEN
'-'
ELSE
'+'
END AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [shipper].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [shipper].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
WHERE shipper.transMBACode = @MBACode
AND original.transDate >= @fromDate
AND original.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_ind = @notExcluded
AND shipper.transRecordLatestRev_INd = @latestRev
AND shipper.transRA_INd = @notTransRA
AND shipper.transPKMPCode = @PKMPCode
AND shipper.transType IN ('Internal')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
UNION --ALL
-- Get all the Domestic AND Export shipping transactions corresponding to the MBA
SELECT CASE WHEN [I].CNSCIsotopeCode IS NULL THEN
[E].CNSCElementCode
ELSE
[E].CNSCElementCode + [I].CNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
@PKMPCode AS [PKMPCode],
-- Shipper specific information
[shipper].transFKMPCode AS [FlowKMPCode],
[shipper].transIAEALabel309 AS [Label309],
[shipper].transRevisionNumber AS [RevisionNumber],
[shipper].transICTypeCode AS [ICType],
[original].transDate AS [TransactionDate],
[shipper].transMDC AS [MaterialDescriptionCode],
[shipper].transNumberOfItems AS [numberOfItems],
[shipper].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
[transMat].transMatElementWeight AS [ElementWeight],
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
[transMat].transMatIsotopeWeight AS [IsotopeWeight],
CASE
WHEN (shipper.TransIAEALabel309 IN ('U','D')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [shipper]
INNER JOIN dbo.[transaction] AS [receiver]
on [receiver].transID = [shipper].transPeerTransID
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [shipper].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [shipper].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [shipper].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
WHERE shipper.transMBACode = @MBACode
AND original.transDate >= @fromDate
AND original.transDate <= @toDate
AND shipper.transShippingReceiving_INd = @shipping
AND shipper.transExclude_ind = @notExcluded
AND shipper.transRecordLatestRev_INd = @latestRev
AND shipper.transRA_INd = @notTransRA
AND shipper.transPKMPCode = @PKMPCode
AND shipper.transType IN ('Domestic', 'Export')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN shipper.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN shipper.transExemptionType IN ('U', 'Q') AND shipper.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND shipper.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND shipper.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for exports)
UNION --ALL
-- Get all the Domestic AND Import receiving transactions
SELECT CASE WHEN [I].CNSCIsotopeCode IS NULL
THEN [E].CNSCElementCode
ELSE [E].CNSCElementCode + [I].CNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
@PKMPCode AS [PKMPCode],
-- Receiver specific information
[receiver].transFKMPCode AS [FlowKMPCode],
[receiver].transIAEALabel309 AS [Label309],
[receiver].transRevisionNumber AS [RevisionNumber],
[receiver].transICTypeCode AS [ICType],
[original].transDate AS [TransactionDate],
[receiver].transMDC AS [MaterialDescriptionCode],
[receiver].transNumberOfItems AS [numberOfItems],
[receiver].transMBCode AS [MeasurementBasis],
CASE
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN [IC].ICTypeRefSign
ELSE [IC].ICTYpeRefExemptedSign
END AS [ICSign],
-- Shipper INformation
[shipper].TransBatchName AS [ShipperBatchName],
[shipper].transMBACode AS [FromMBACode],
[shipper].transCountryCode AS [FromCountryCode],
[shipper].transFacilityCode AS [FromFacilityCode],
-- Receiver INformation
[receiver].TransBatchName AS [ReceiverBatchName],
[receiver].transMBACode AS [ToMBACode],
[receiver].transCountryCode AS [ToCountryCode],
[receiver].transFacilityCode AS [ToFacilityCode],
-- Material information
[E].CNSCElementCode AS [ElementCode],
[E].elementName AS [ElementName],
[E].elementUnit AS [ElementUnit],
[transMat].transMatElementWeight AS [ElementWeight],
[I].CNSCIsotopeCode AS [IsotopeCode],
[I].IsotopeName AS [IsotopeName],
[I].IsotopeUnit AS [IsotopeUnit],
[transMat].transMatIsotopeWeight AS [IsotopeWeight],
CASE
WHEN (receiver.TransIAEALabel309 IN ('U','D')) THEN
0
ELSE
1
END AS SumFlag,
0 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
FROM dbo.[transaction] AS [receiver]
INNER JOIN dbo.[transaction] AS [shipper]
on [shipper].transID = [receiver].transPeerTransID
INNER JOIN dbo.[transaction] AS [original]
on [original].transID = [receiver].TransIAEAORiginalTransID
INNER JOIN #TempTransactionMaterial AS [transMat]
on [receiver].transID = [transMat].transID
INNER JOIN ICTypeRef AS [IC]
on [IC].ICTypeRefCode = [receiver].transICTypeCode
INNER JOIN dbo.element AS [E]
on [E].CNSCElementCode = [transMat].transMatCNSCElementCode
LEFT OUTER JOIN dbo.isotope AS [I]
on [I].CNSCIsotopeCode = [transMat].transMatCNSCIsotopeCode
WHERE receiver.transMBACode = @MBACode
AND original.transDate >= @fromDate
AND original.transDate <= @toDate
AND receiver.transShippingReceiving_INd = @receiving
AND receiver.transExclude_ind = @notExcluded
AND receiver.transRecordLatestRev_INd = @latestRev
AND receiver.transRA_INd = @notTransRA
AND receiver.transPKMPCode = @PKMPCode
AND receiver.transType IN ('Domestic', 'Import')
AND [E].elementSG_INd =
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN receiver.transExemptionType IS NULL AND [E].elementSG_INd = 1 then 1
WHEN receiver.transExemptionType IN ('U', 'Q') AND receiver.TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [E].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND receiver.transExemptionType = 'U' AND [E].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND receiver.transExemptionType = 'Q' AND [E].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
AND shipper.transINvFormReceived_INd = @formReceived -- form received checkbox must be checked (automatically checked IN the db for imports)
UNION --ALL
-- Get all the RA transactions (LOF & VR)
Select
Case
WHEN TransMatCNSCIsotopeCode IS NULL THEN
TransMatCNSCElementCode
ELSE
TransMatCNSCElementCode + TransMatCNSCIsotopeCode
End AS TransKey,
@ApplicationMode AS [Mode],
[RATransaction].[TransPKMPCode] AS [PKMPCode],
Case -- Try to match this RA to an FKMP based on MBA/Batch/Date/IC AND only One chain
WHEN
(-- Count the distinct rows
Select Count(*) AS DistinctChainCount
From
(-- Get matching rows
Select Distinct
[MatchFKMPtoRA].TransMBACode,
[MatchFKMPtoRA].TransBatchName,
[MatchFKMPtoRA].TransDate,
[MatchFKMPtoRA].TransICTypeCode,
[MatchFKMPtoRA].TransIAEAORiginalTransID,
[MatchFKMPtoRA].TransFKMPCode
From [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0) AS CountingDistinctChains -- alias to wrap AND count
) = 1 THEN
-- We have only one matching chain, lets get the FKMP now
-- top 1 IN case chain has same info including date matching (would have been reduced to one row IN distinct counting above
(Select Top 1 [MatchFKMPtoRA].TransFKMPCode
From [Transaction] AS [MatchFKMPtoRA]
WHERE [RATransaction].TransMBACode = [MatchFKMPtoRA].TransMBACode
AND [RATransaction].TransBatchName = [MatchFKMPtoRA].TransBatchName
AND [RATransaction].TransDate = [MatchFKMPtoRA].TransDate
AND [RATransaction].TransICTypeCode = [MatchFKMPtoRA].TransICTypeCode
AND [MatchFKMPtoRA].TransRA_INd = 0)
ELSE
-- We have no matches OR there IS more than one chain with same info - make FKMP NULL
NULL
End AS [FlowKMPCode],
NULL AS [Label309],
NULL AS [RevisionNumber],
'RA' + [RATransaction].transICTypeCode AS [ICType],
[RATransaction].transDate AS [TransactionDate],
NULL AS [MaterialDescriptionCode],
NULL AS [numberOfItems],
NULL AS [MeasurementBasis],
Case
WHEN @SafeguardedExemptMode IN ('S', 'N') THEN
[ICTypeRef].ICTypeRefSign
ELSE
[ICTypeRef].ICTYpeRefExemptedSign
End AS [ICSign],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ShipperBatchName],
Case
WHEN [RATransaction].TransType IN ('Export') OR [RATransaction].TransICTypeCode IN ('SD') THEN
[RATransaction].transMBACode
ELSE
NULL
End AS FromMBACode,
NULL AS [FromCountryCode],
NULL AS [FromFacilityCode],
Case
WHEN [RATransaction].TransType IN ('Internal','Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].TransBatchName
ELSE
NULL
End AS [ReceiverBatchName],
Case
WHEN [RATransaction].TransType IN ('Internal','Import') OR [RATransaction].TransICTypeCode IN ('RD') THEN
[RATransaction].TransMBACode
ELSE
NULL
End AS [ToMBACode],
NULL AS [ToCountryCode],
NULL AS [ToFacilityCode],
-- Material information
TransMatCNSCElementCode AS [ElementCode],
[Element].elementName AS [ElementName],
[Element].elementUnit AS [ElementUnit],
TransMatElementWeight AS ElementWeight,
TransMatCNSCIsotopeCode AS [IsotopeCode],
[Isotope].IsotopeName AS [IsotopeName],
[Isotope].IsotopeUnit AS [IsotopeUnit],
TransMatIsotopeWeight AS IsotopeWeight,
1 AS SumFlag,
1 AS RASortField,
NULL AS ORiginalTransElementSign,
NULL AS ORiginalTransIsotopeSign
From
[Transaction] AS [RATransaction] -- Aliased to use IN the subquery for FKMP
INNER JOIN
[TransactionMaterial]
On [RATransaction].[TransID]=[TransactionMaterial].[TransID]
INNER JOIN
[Element]
On [TransactionMaterial].[TransMatCNSCElementCode]=[Element].[CNSCElementCode]
LEFT OUTER JOIN
[Isotope] -- Isotope code may be NULL IN 'left'
On [TransactionMaterial].[TransMatCNSCIsotopeCode]=[Isotope].[CNSCIsotopeCode]
INNER JOIN
[ICTypeRef]
On [RATransaction].[TransICTypeCode]=[ICTypeRef].[ICTypeRefCode]
WHERE
transMBACode = @MBACode
AND transPKMPCode = @PKMPCode -- LOF only
AND transDate >= @fromDate
AND transDate <= @toDate
AND transRA_INd = @transRA
AND [Element].elementSG_INd = -- 0, 1, OR NULL IS tested depending on ExemptMode (NULL never matches!)
CASE
WHEN @SafeguardedExemptMode = 'S' then
CASE
WHEN transExemptionType IS NULL AND [Element].elementSG_INd = 1 then 1
WHEN transExemptionType IN ('U', 'Q') AND TransICTypeCode IN ('EQ', 'DQ', 'EU', 'DU') AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
WHEN @SafeguardedExemptMode = 'N'AND [Element].elementSG_INd = 0 then 0
WHEN @SafeguardedExemptMode = 'U' AND transExemptionType = 'U' AND [Element].elementSG_INd = 1 then 1
WHEN @SafeguardedExemptMode = 'Q' AND transExemptionType = 'Q' AND [Element].elementSG_INd = 1 then 1
ELSE NULL --neither SG OR NSG (exclude the record)
END
ORDER BY ICType
END -- specifc LOF OR PKMP code
END --report mode by Virtual Replacement
--==========================================================================================================
-- Adjust the IC Sign for Category Change
-- There are 6 category change: DE, DN, EN, ED, ND AND NE
-- Basically the first letter indicates the element that IS decreased AND the second letter indicate the element that IS increased
-- Therefore the value corresponding to the first element should appear IN the decrease column of the report
-- AND the value of the second element should appear IN the increase column of the report
-- The ICType has the associated ICSign of "+" for all the category changes. Therefore, there IS no change to do for the second
-- element appearing on the IC Type. The change of the sign IS only applied to the element appearing first IN the category change.
-- Update DE transactions (Isotope Code IS NULL)
Update #LedgerTransactions
SET ICSign = '-',
ElementWeight = ElementWeight * -1
WHERE ( ICType = 'DE' OR ICType = 'RADE' )
AND ElementCode = 'D'
AND IsotopeCode IS NULL
-- Update DN transactions (Isotope Code IS NULL)
Update #LedgerTransactions
SET ICSign = '-',
ElementWeight = ElementWeight * -1
WHERE ( ICType = 'DN' OR ICType = 'RADN' )
AND ElementCode = 'D'
AND IsotopeCode IS NULL
-- Update EN transactions (Isotope Code IS NOT NULL)
Update #LedgerTransactions
SET ICSign = '-',
ElementWeight = ElementWeight * -1,
IsotopeWeight = IsotopeWeight * -1
WHERE ( ICType = 'EN' OR ICType = 'RAEN' )
AND ElementCode = 'E'
AND IsotopeCode IS NOT NULL
-- Update ED transactions (Isotope Code IS NOT NULL)
Update #LedgerTransactions
SET ICSign = '-',
ElementWeight = ElementWeight * -1,
IsotopeWeight = IsotopeWeight * -1
WHERE ( ICType = 'ED' OR ICType = 'RAED' )
AND ElementCode = 'E'
AND IsotopeCode IS NOT NULL
-- Update ND transactions when Isotope Code IS NULL
Update #LedgerTransactions
SET ICSign = '-',
ElementWeight = ElementWeight * -1
WHERE ( ICType = 'ND' OR ICType = 'RAND' )
AND ElementCode = 'N'
AND IsotopeCode IS NULL
-- Update NE transactions when Isotope Code IS NULL
Update #LedgerTransactions
SET ICSign = '-',
ElementWeight = ElementWeight * -1
WHERE ( ICType = 'NE' OR ICType = 'RANE' )
AND ElementCode = 'N'
AND IsotopeCode IS NULL
END
--end of sub stored procedure
