SWxPtSWJ

1IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Recon_Report_Column_Mapping]') AND type in (N'U'))
2DROP TABLE [dbo].[Recon_Report_Column_Mapping]
3GO
4
5create table dbo.Recon_Report_Column_Mapping
6(Recon_Report_Column int NOT NULL identity(1,1) primary key, 
7 Recon_Report_Name varchar(75) NOT NULL ,
8 Recon_Report_Column_Name varchar(75) NOT NULL ,
9 Recon_Report_Column_SortOrder tinyint not null, 
10 Is_Active char(1) NOT NULL DEFAULT('Y'), 
11 Updated_Time datetime not null default (getdate())
12 ) 
13 go 
14 CREATE UNIQUE NONCLUSTERED INDEX [IDX_Uniqu_Recon_Report_Column] ON [dbo].[Recon_Report_Column_Mapping]
15(   [Recon_Report_Name] ASC,
16	[Recon_Report_Column_Name] ASC,
17	[Is_Active] ASC
18)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
19GO
20
21INSERT INTO dbo.Recon_Report_Column_Mapping ( Recon_Report_Name,Recon_Report_Column_Name,Recon_Report_Column_SortOrder) 
22SELECT * FROM (
23SELECT 'LCHREP045'AS Recon_Report_Name, 'Run_Date_Time' as   Recon_Report_Column_Name    	,1 as Recon_Report_Column_SortOrder  UNION ALL 
24SELECT 'LCHREP045'AS Recon_Report_Name,'Origin_of_Record'	Recon_Report_Column_Name,2 as Recon_Report_Column_SortOrder UNION ALL 
25SELECT 'LCHREP045'AS Recon_Report_Name,'Scmmnemonic'	Recon_Report_Column_Name,3 as Recon_Report_Column_SortOrder UNION ALL 
26SELECT 'LCHREP045'AS Recon_Report_Name,'Sdmnemonic' 	Recon_Report_Column_Name,4 as Recon_Report_Column_SortOrder UNION ALL 
27SELECT 'LCHREP045'AS Recon_Report_Name,'Tran_Trade_id'   Recon_Report_Column_Name     	,5 as Recon_Report_Column_SortOrder UNION ALL 
28SELECT 'LCHREP045'AS Recon_Report_Name,'LCH_Ref_No'      Recon_Report_Column_Name              	,6 as Recon_Report_Column_SortOrder UNION ALL  
29SELECT 'LCHREP045'AS Recon_Report_Name,'Pay_Date'         Recon_Report_Column_Name  	,7 as Recon_Report_Column_SortOrder  UNION ALL 
30SELECT 'LCHREP045'AS Recon_Report_Name,'Currency'	Recon_Report_Column_Name,8 as Recon_Report_Column_SortOrder UNION ALL 
31SELECT 'LCHREP045'AS Recon_Report_Name,'Account'	Recon_Report_Column_Name,9 as Recon_Report_Column_SortOrder UNION ALL 
32SELECT 'LCHREP045'AS Recon_Report_Name,'Amount' Recon_Report_Column_Name             	,10 as Recon_Report_Column_SortOrder UNION ALL 
33SELECT 'LCHREP045'AS Recon_Report_Name,'Statename'    Recon_Report_Column_Name  	,11 as Recon_Report_Column_SortOrder UNION ALL 
34SELECT 'LCHREP045'AS Recon_Report_Name,'Fee_Tran_Trade_id' Recon_Report_Column_Name	,12 as Recon_Report_Column_SortOrder UNION ALL 
35SELECT 'LCHREP045'AS Recon_Report_Name,'Counterparty'  Recon_Report_Column_Name ,    13 as Recon_Report_Column_SortOrder   	
36
37) T ORDER BY 3
38
39SELECT * FROM [dbo].[Recon_Report_Column_Mapping] WHERE Is_Active='Y' AND Recon_Report_Name='LCHREP045'