Untitled

USE [Reporting]
GO
/****** Object:  StoredProcedure [dbo].[PortfolioStatement_Report1]    Script Date: 11/30/2023 09:24:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[PortfolioStatement_Report1]
@ExtractionDate datetime,
@PortfolioNb varchar(50)
AS
BEGIN
SELECT DISTINCT
		 vw.COD21Z_Counter, vw.COD21Z_batchdate, vw.COD21Z_Customer_ID, vw.COD21Z_Ptfnum_AccNum, vw.COD21Z_Security_ID, vw.COD21Z_SC_Master_ID, vw.COD21Z_Orig_CCY, vw.COD21Z_Conv_Ccy, 
		vw.COD21Z_num_Securities, vw.COD21Z_CV_Cost_Price, vw.COD21Z_Orig_Cost_Price, vw.COD21Z_CV_Market_Price, vw.COD21Z_Orig_Market_Price, vw.COD21Z_CV_Estimation, vw.COD21Z_Orig_Estimation, 
		vw.COD21Z_CV_Account_Interest, vw.COD21Z_Orig_Account_Interest, vw.COD21Z_Interest_Date, vw.COD21Z_Security_Description, vw.COD21Z_Maturity_Date, vw.COD21Z_Security_Type, vw.COD21Z_Security_Sub_Type, 
		vw.COD21Z_Security_Sub_Type_Description, vw.COD21Z_Ticker_Code, vw.COD21Z_ISIN_Code, vw.COD21Z_CUSIP_Code, vw.COD21Z_CV_Unrealized_PL, vw.COD21Z_Orig_Unrealized_PL, vw.COD21Z_CV_Percentage_PL, 
		vw.COD21Z_Orig_Percentage_PL, vw.COD21Z_Barcode, vw.COD21Z_Extraction_Date, vw.COD21Z_Total, vw.COD21Z_GTotal, vw.COD21W_Name1, vw.COD21W_Name2, vw.COD21W_Mail_Flag, vw.COD21W_PO_Box, 
		vw.COD21W_Building_Name, vw.COD21W_Building_Number, vw.COD21W_Street_Name, vw.COD21W_Reference, vw.COD21W_Town_Name, vw.COD21W_Postal_Code, vw.COD21W_Country_Name, vw.COD21W_Care_Of, 
		vw.COD21W_Last_Line, vw.COD21W_Recipient_Branch_Number, vw.COD21W_Rel_Manager_Code, '' AS COD21W_Rel_Manager_Name, vw.COD21W_Branch_Number, vw.COD21W_Branch_Name, vw.COD21W_Extraction_Date, 
		vw.COD21V_Count_PTF, vw.COD21V_Position,'1' as test
FROM          View_Report1 vw 
where vw.COD21Z_batchdate=@ExtractionDate and vw.COD21Z_Ptfnum_AccNum=@PortfolioNb 
UNION
SELECT DISTINCT
		 vw.COD21Z_Counter, vw.COD21Z_batchdate, vw.COD21Z_Customer_ID, vw.COD21Z_Ptfnum_AccNum, vw.COD21Z_Security_ID, vw.COD21Z_SC_Master_ID, vw.COD21Z_Orig_CCY, vw.COD21Z_Conv_Ccy, 
		vw.COD21Z_num_Securities, vw.COD21Z_CV_Cost_Price, vw.COD21Z_Orig_Cost_Price, vw.COD21Z_CV_Market_Price, vw.COD21Z_Orig_Market_Price, vw.COD21Z_CV_Estimation, vw.COD21Z_Orig_Estimation, 
		vw.COD21Z_CV_Account_Interest, vw.COD21Z_Orig_Account_Interest, vw.COD21Z_Interest_Date, vw.COD21Z_Security_Description, vw.COD21Z_Maturity_Date, vw.COD21Z_Security_Type, vw.COD21Z_Security_Sub_Type, 
		'Prev. ' + vw.COD21Z_Security_Sub_Type_Description as COD21Z_Security_Sub_Type_Description, vw.COD21Z_Ticker_Code, vw.COD21Z_ISIN_Code, vw.COD21Z_CUSIP_Code, vw.COD21Z_CV_Unrealized_PL, vw.COD21Z_Orig_Unrealized_PL, vw.COD21Z_CV_Percentage_PL, 
		vw.COD21Z_Orig_Percentage_PL, vw.COD21Z_Barcode, vw.COD21Z_Extraction_Date, vw.COD21Z_Total, vw.COD21Z_GTotal, vw.COD21W_Name1, vw.COD21W_Name2, vw.COD21W_Mail_Flag, vw.COD21W_PO_Box, 
		vw.COD21W_Building_Name, vw.COD21W_Building_Number, vw.COD21W_Street_Name, vw.COD21W_Reference, vw.COD21W_Town_Name, vw.COD21W_Postal_Code, vw.COD21W_Country_Name, vw.COD21W_Care_Of, 
		vw.COD21W_Last_Line, vw.COD21W_Recipient_Branch_Number, vw.COD21W_Rel_Manager_Code, '' AS COD21W_Rel_Manager_Name, vw.COD21W_Branch_Number, vw.COD21W_Branch_Name, vw.COD21W_Extraction_Date, 
		vw.COD21V_Count_PTF, vw.COD21V_Position,'2' as test
FROM          View_Report1 vw 
where 
vw.COD21Z_Ptfnum_AccNum=@PortfolioNb
AND vw.COD21Z_batchdate=(select max(COD21Z_batchdate) 
from View_Report1 z1 WHERE z1.COD21Z_batchdate < @ExtractionDate and z1.COD21Z_Ptfnum_AccNum=@PortfolioNb) 
AND COD21Z_Security_Sub_Type_Description in ('Portfolio c/v $','Interest c/v $','Portfolio Includ. Int c/v $')
END