sbL8V1Ag

1---- Order of banks by PaymentChannel and Country.
2
3USE [Payment];
4GO 
5
6DROP TABLE IF EXISTS [dbo].[tmpBankOrder];
7GO
8
9CREATE TABLE [dbo].[tmpBankOrder] 
10(
11    [PaymentChannelCode] NVARCHAR(35) NOT NULL,
12	[BaseResourceId] INT NOT NULL,
13	[Name] NVARCHAR(50) NOT NULL,
14	[PaymentChannelId] INT NOT NULL,
15	[Order] INT NOT NULL
16);
17GO 
18
19DECLARE @CountryCode NVARCHAR(3) = 'PER';
20
21INSERT INTO [dbo].[tmpBankOrder]
22(
23    [PaymentChannelCode],
24	[BaseResourceId],
25	[Name],
26	[PaymentChannelId],
27	[Order]
28)
29SELECT 
30	[pcc].[PaymentChannelCode],
31	[br].[BaseResourceId],
32	[br].[Name],
33	[pc].[PaymentChannelId],
34	[pcpm].[Order]
35FROM [dbo].[PaymentChannelContent] AS [pcc]
36JOIN [dbo].[BaseResource] AS [br] ON [br].[Code] = [pcc].[PaymentChannelCode]
37JOIN [dbo].[PaymentChannel] AS [pc] ON [pc].[BaseResourceId] = [br].[BaseResourceId]
38JOIN [dbo].[PaymentChannelPaymentMethod] AS [pcpm] ON [pcpm].[PaymentChannelId] = [pc].[PaymentChannelId]
39WHERE [pcc].[CountryId] = @CountryCode;
40GO 
41
42
43SELECT *
44FROM [dbo].[tmpBankOrder]
45ORDER BY [Order];