USE [NMAS]
GO
/****** Object: StoredProcedure [dbo].[nmas_sp_getMBA_LOF_LedgerData] Script Date: 2023-11-21 11:00:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[nmas_sp_getMBA_LOF_LedgerData]
--'**************************************************************************************************************************
--* Created by:
--*
--* Purpose: This stored procedure is used to get the transaction data for the MBA/LOF Ledger.
--*
--* CVS File: $RCSfile: dbo.nmas_sp_getMBA_LOF_LedgerData.PRC,v $
--* Commit Date: $Date: 2008/10/16 13:58:38 $ (UTC)
--* Committed by: $Author: walshkel $
--* CVS Revision: $Revision: 1.12 $
--* 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 *******************************
--*************************************************************************************************
-
--*************************************************************************************************
--INPUTS
@r04mbaCode varchar(4),
--The MBA code
@b02pkmPKMPCode varchar(2),
--if the MBA is a LOF, the user can enter a PKMP to report on
--the individual component of the LOF
@r01semSafeguardedExemptMode varchar(1),
-- Safeguarded {S|s}, Non-Safeguarded {N|n}, Exempted Use {U|u}, Exempted Quantity {Q|q}
@r01bvdMode char,
--report corrections as "B" (by-difference) or "V" (virtual replacement) mode
@r06dteFromDate varchar(6),
--begining date of the requested reporting period
@r06dteToDate varchar(6)
--ending date of the requested reporting period
--OUTPUTS
--a temporary table containing all the fields used in the MBA/LOF Ledger,
--and the opening requested period balance for the selected MBA at the
--begining of the reporting period. The reporting mode ("V" for Virtual Replacement
--or "D" for By-Difference) is also included as a column in the output table.
AS
BEGIN
DECLARE
--rename the input parameters to a simpler variable name
@MBACode varchar(4),
@PKMPCode varchar(2),
@Mode char,
@strFromDate varchar(6),
@strToDate varchar(6),
@FromDate datetime,
@ToDate datetime,
@isMBA_LOF bit,
@MBAisLOF int,
@MBAisNotLOF int,
@reportMBAorLOF bit,
@reportOnlyLOF int,
@reportEntireMBALOF bit,
@reportEntireMBA bit,
@ApplicationMode int,
@ByDifference varchar(1),
@SafeguardedExemptMode varchar(1)
-- Set the constants
Set @ByDifference = 'B'
--Copy the input parameters into the duplicate variables
set @MBACode = @r04mbaCode
If @b02pkmPKMPCode = ''
set @PKMPCode = NULL
Else
set @PKMPCode = @b02pkmPKMPCode
set @SafeguardedExemptMode = @r01semSafeguardedExemptMode
set @mode = @r01bvdMode
set @strFromDate = @r06dteFromDate
set @strToDate = @r06dteToDate
--create a temporary table to store the sum of element and isotope weights
Create Table #MBAOpeningBalance(
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 the transactions for the ledger
Create Table #LedgerTransactions(
TransKey varchar(40),
Mode int, -- indicates if reporting by Virtual Replacement (1) or by-Diff. (0)
PKMPCode varchar(2), -- indicates that report is one component of a LOF MBA
FlowKMPCode varchar(2),
Label309 varchar(1),
RevisionNumber int,
ICType varchar(4),
TransactionDate dateTime,
MaterialDescriptionCode varchar(4),
NumberOfItems int,
MeasurementBasis varchar(1),
ICSign varchar(1),
ShipperBatchName varchar(16),
FromMBACode varchar(4),
FromCountryCode varchar(2),
FromFacilityCode varchar(4),
ReceiverBatchName varchar(16),
ToMBACode varchar(4),
ToCountryCode varchar(2),
ToFacilityCode varchar(4),
ElementCode varchar(20),
ElementName varchar(60),
ElementUnit varchar(10),
ElementWeight Decimal(27,6),
IsotopeCode varchar(20),
IsotopeName varchar(60),
IsotopeUnit varchar(4),
IsotopeWeight Decimal(27,6),
SumFlag int,
RASortField int,
OriginalTransElementSign varchar(1),
OriginalTransIsotopeSign varchar(1)
)
Create Table #TempFinalResults (
[ElementOB] Decimal(27,6),
[IsotopeOB] Decimal(27,6),
[TransKey] varchar(40),
[Mode] int,
[Label309] varchar(1),
[PKMPCode] varchar(2),
[FlowKMPCode] varchar(2),
[RevisionNumber] int,
[ICType] varchar(4),
[TransactionDate] datetime,
[MaterialDescriptionCode] varchar(4),
[NumberOfItems] int,
[MeasurementBasis] varchar(1),
[ICSign] varchar(1),
[ShipperBatchName] varchar(16),
[FromMBACode] varchar(4),
[FromCountryCode] varchar(2),
[FromFacilityCode] varchar(4),
[ReceiverBatchName] varchar(16),
[ToMBACode] varchar(4),
[ToCountryCode] varchar(2),
[ToFacilityCode] varchar(4),
[ElementCode] varchar(20),
[ElementName] varchar(60),
[ElementUnit] varchar(10),
[ElementWeight] Decimal(27,6),
[IsotopeCode] varchar(20),
[IsotopeName] varchar(60),
[IsotopeUnit] varchar(10),
[IsotopeWeight] Decimal(27,6),
[SumFlag] int,
[RASortField] int,
[OriginalTransElementSign] varchar(1),
[OriginalTransIsotopeSign] varchar(1),
[MBALOF_Ind] bit
)
--set constants that indicate that MBA is a LOF or not
Set @MBAisLOF = 1
Set @MBAisNotLOF = 0
--set constants that indicate that a LOF MBA will be reported by whole MBA or component MBA
Set @ReportEntireMBA = 1
Set @ReportOnlyLOF = 0
--convert the string datatype NMAS dates into SQL date datatypes
Set @FromDate = dbo.convertNMASDate(@strFromDate)
Set @ToDate = dbo.convertNMASDate(@strToDate)
--determine if the selected MBA is a LOF or not
Select @isMBA_LOF = ( Select MBALOF_IND
From MBA
Where MBACode = @MBACode )
--validate the PKMP Code
If @isMBA_LOF = @MBAisLOF
BEGIN
--validate the PKMP Code
if @PKMPCode is not null
BEGIN
--validate that the user entered a valid PKMP Code for the MBA
if @PKMPCode in ( Select PKMPCode
From PKMP
Inner join MBA
on PKMP.MBAID = MBA.MBAID
Where MBACode = @MBACode )
BEGIN
--The user entered a valid PKMP Code for the MBA
Set @reportMBAorLOF = @reportOnlyLOF
END
Else
BEGIN
set @PKMPCode = NULL
--The user did not enter a valid PKMP Code for the MBA.
--Set the Report type to report entire MBA
set @reportMBAorLOF = @ReportEntireMBA
END
END
Else --PKMP Code is NULL
BEGIN
Set @reportMBAorLOF = @ReportEntireMBA
END
END
ELSE --@isMBA_LOF = @MBAisNotLOF
BEGIN
set @PKMPCode = NULL
set @ReportMBAorLOF = @ReportEntireMBA
END
-- Validate that the Mode is "B" or "V", otherwise use global setting
If @Mode not in ('B', 'V')
Begin
--Set the reporting mode to the default mode
Select @ApplicationMode = ( Select Top 1 GlobalVariableVirtualReplacement_Ind
From dbo.GlobalVariable)
End
Else
Begin
If @Mode = @ByDifference -- 'B'
Set @ApplicationMode = 0 -- By Difference
Else
Set @ApplicationMode = 1 -- By Virtual Replacement
End
--==========================================================================================================
-- Get the opening consolidated balance for the MBA which populates the table #MBAOpeningBalance
--==========================================================================================================
Exec nmas_b_subsp_getMBAOpeningBalanceForMBAL @isMBA_LOF, @ReportMBAorLOF, @MBACode, @PKMPCode, @SafeguardedExemptMode, @FromDate, @ApplicationMode
--==========================================================================================================
-- Call a separate procedure to insert the transactions into a temporary table
--==========================================================================================================
Exec nmas_a_subsp_GetMBALOFLedgerTransactions @MBACode, @ReportMBAorLOF, @PKMPCode, @FromDate, @ToDate, @ApplicationMode, @SafeguardedExemptMode
--==========================================================================================================
-- Join the opening balances and the transactions
-- If no transactions were found for a MBA, include all material that have an oppening balance different than zero
--==========================================================================================================
-- Write this result set to a temporary table to determine if there are any rows or not
Insert into #TempFinalResults
Select [OB].ElementWeight as [ElementOB],
[OB].IsotopeWeight as [IsotopeOB],
CASE WHEN [OB].CNSCIsotopeCode Is NULL
THEN [OB].CNSCElementCode
ELSE
[OB].CNSCElementCode + [OB].CNSCIsotopeCode
END as TransKey,
@ApplicationMode as Mode,
[Transactions].Label309,
CASE When @isMBA_LOF = @MBAisLOF And @PKMPCode is not null Then
@PKMPCode
Else
NULL
End as PKMPCode,
[Transactions].FlowKMPCode,
[Transactions].RevisionNumber,
[Transactions].ICType,
[Transactions].TransactionDate,
[Transactions].MaterialDescriptionCode,
[Transactions].NumberOfItems,
[Transactions].MeasurementBasis,
[Transactions].ICSign,
[Transactions].ShipperBatchName,
[Transactions].FromMBACode,
[Transactions].FromCountryCode,
[Transactions].FromFacilityCode,
[Transactions].ReceiverBatchName,
[Transactions].ToMBACode,
[Transactions].ToCountryCode,
[Transactions].ToFacilityCode,
[OB].CNSCElementCode as ElementCode,
[Element].ElementName,
[OB].ElementUnit,
[Transactions].ElementWeight,
[OB].CNSCIsotopeCode as IsotopeCode,
[Isotope].IsotopeName,
[OB].IsotopeUnit,
[Transactions].IsotopeWeight,
[Transactions].SumFlag,
[Transactions].RASortField,
[Transactions].OriginalTransElementSign,
[Transactions].OriginalTransIsotopeSign,
@isMBA_LOF as [MBALOF_Ind]
From #MBAOpeningBalance as [OB]
Left Outer join #LedgerTransactions as [Transactions]
On [OB].ElmIsoKey = [Transactions].TransKey
Inner Join Element
On Element.CNSCElementCode = [OB].CNSCElementCode
Left Outer Join Isotope
On Isotope.CNSCIsotopeCode = [OB].CNSCIsotopeCode
Where [Transactions].ElementWeight Is Not NULL
Or [OB].ElementWeight <> 0
-- Determine if there are any results
DECLARE @intFinalRowCount as Int
Select @intFinalRowCount = (
Select Count(*)
From #TempFinalResults
)
-- Decide on sending a NULL row or the contents of the final results table
if @intFinalRowCount > 0
Begin
-- There are results - return them
-- added shipperbatchname and receiverbatchname to the following select sort order Mark Hughes February 12, 2013
select CASE
when [ICType] IN ('SD', 'SF', 'RASD', 'RASF') Then -- fixed pre-existing bug for shipper vs receiver ICTypes February 25, 2013 Mark Hughes
[ShipperBatchName] + [TransKey] + [FlowKMPCode] + SUBSTRING([ICType], LEN([ICType]) - 1, LEN([ICTYPE])) + CAST(RASortField AS VARCHAR(1))
Else
[ReceiverBatchName] + [TransKey] + [FlowKMPCode] + SUBSTRING([ICType], LEN([ICType]) - 1, LEN([ICTYPE])) + CAST(RASortField AS VARCHAR(1))
End as [ordercolumn],
*
from #TempFinalResults
order by [ordercolumn]
End
else
Begin
-- Create our own return with NULLs
Insert into #TempFinalResults
Select
NULL as [ElementOB],
NULL as [IsotopeOB],
NULL as [TransKey],
@ApplicationMode as [Mode],
NULL as [Label309],
NULL as [PKMPCode],
NULL as [FlowKMPCode],
NULL as [RevisionNumber],
NULL as [ICType],
NULL as [TransactionDate],
NULL as [MaterialDescriptionCode],
NULL as [NumberOfItems],
NULL as [MeasurementBasis],
NULL as [ICSign],
NULL as [ShipperBatchName],
NULL as [FromMBACode],
NULL as [FromCountryCode],
NULL as [FromFacilityCode],
NULL as [ReceiverBatchName],
NULL as [ToMBACode],
NULL as [ToCountryCode],
NULL as [ToFacilityCode],
NULL as [ElementCode],
NULL as [ElementName],
NULL as [ElementUnit],
NULL as [ElementWeight],
NULL as [IsotopeCode],
NULL as [IsotopeName],
NULL as [IsotopeUnit],
NULL as [IsotopeWeight],
NULL as [SumFlag],
NULL as [RASortField],
NULL as [OriginalTransElementSign],
NULL as [OriginalTransIsotopeSign],
@isMBA_LOF as [MBALOF_Ind]
-- moved shipperbatchname and receiverbatchname to the beginning of the following select sort order Mark Hughes February 12, 2013
select CASE
when [ICType] IN ('SD', 'SF', 'RASD', 'RASF') Then -- fixed pre-existing bug for shipper vs receiver ICTypes February 25, 2013 Mark Hughes
[ShipperBatchName] + [TransKey] + [FlowKMPCode] + SUBSTRING([ICType], LEN([ICType]) - 1, LEN([ICTYPE])) + CAST(RASortField AS VARCHAR(1))
Else
[ReceiverBatchName] + [TransKey] + [FlowKMPCode] + SUBSTRING([ICType], LEN([ICType]) - 1, LEN([ICTYPE])) + CAST(RASortField AS VARCHAR(1))
End as [ordercolumn],
*
from #TempFinalResults
order by [ordercolumn]
End
END
--End of Stored Procedure
