· 6 years ago · Mar 06, 2019, 11:16 AM
1create proc [dbo].[sp_ORA_SyncCashierReceiptMethodDetails]
2as
3BEGIN
4
5 DECLARE @ErrorVar int
6 create table #tmp
7 (
8 MethodID nvarchar(15)
9 ,DescriptionE nvarchar(50)
10 ,DescriptionAR nvarchar(50)
11 )
12
13 Insert into #tmp(MethodID,DescriptionE,DescriptionAR)
14 select distinct RECEIPT_METHOD_ID,RECEIPT_METHOD,RECEIPT_METHOD
15 from openquery(ORALINK,'select RECEIPT_METHOD_ID,RECEIPT_METHOD from XXBI.XXBI_V_CashierReceiptMethod ')
16
17 SET @ErrorVar = @@ERROR
18 IF @ErrorVar <> 0
19 return @ErrorVar
20
21 --Delete from SBDB,records that are not on oracleDB
22 delete AR_CashierReceiptMethod from AR_CashierReceiptMethod as s left join
23 #tmp
24 as o on s.MethodID = o.MethodID
25 where o.MethodID is null
26
27
28 SET @ErrorVar = @@ERROR
29 IF @ErrorVar <> 0
30 return @ErrorVar
31
32 --Update records on SBDB where the corresponding records on oracleDB have CREATION_DATE > LAST_UPDATE_DATE
33
34 update s
35 set s.MethodID = o.MethodID,
36 s.Description = substring(o.DescriptionE, 1, 50),
37 s.DescriptionAR = substring(o.DescriptionAR, 1, 50)
38 from AR_CashierReceiptMethod as s
39 inner join #tmp
40 as o on s.MethodID = o.MethodID
41
42
43 SET @ErrorVar = @@ERROR
44 IF @ErrorVar <> 0
45 return @ErrorVar
46
47
48 --Insert records on SBDB that exists on oracleDB only & have LAST_UPDATE_DATE>LastSnapshotDate
49 insert into AR_CashierReceiptMethod (MethodID,Description,DescriptionAR)
50 select o.MethodID,substring(o.DescriptionE, 1, 50),substring(o.DescriptionAR, 1, 50)
51 from
52 #tmp as o left outer join AR_CashierReceiptMethod s
53 on o.MethodID = s.MethodID
54 where s.MethodID Is Null
55
56
57 SET @ErrorVar = @@ERROR
58 IF @ErrorVar <> 0
59 return @ErrorVar
60 drop table #tmp
61
62
63 create table #tmp1
64 (
65 [MethodID] [nvarchar](15) NOT NULL,
66 [Serial] [int] NOT NULL,
67 [BankID] [nvarchar](15) NULL,
68 [BranchID] [nvarchar](15) NULL,
69 [AccountNum] [nvarchar](30) NULL,
70 [AccountID] [int] NULL,
71 )
72
73 Insert into #tmp1
74 select RECEIPT_METHOD_ID,RANK() over (partition by RECEIPT_METHOD_ID order by BANKID,BRANCHID,BANK_ACCOUNT_NUM),BANKID,BRANCHID,BANK_ACCOUNT_NUM,bank_account_id
75 from openquery(ORALINK,'select RECEIPT_METHOD_ID,BANKID,BRANCHID,BANK_ACCOUNT_NUM,bank_account_id from XXBI.XXBI_V_CashierReceiptMethod ')
76
77
78 SET @ErrorVar = @@ERROR
79 IF @ErrorVar <> 0
80 return @ErrorVar
81
82 --Delete from SBDB,records that are not on oracleDB
83 delete AR_CashierReceiptMethodDetails from AR_CashierReceiptMethodDetails as s left join
84 #tmp1
85 as o on s.MethodID = o.MethodID and o.AccountID = s.AccountID
86 where o.MethodID is null
87
88
89 SET @ErrorVar = @@ERROR
90 IF @ErrorVar <> 0
91 return @ErrorVar
92
93 --Update records on SBDB where the corresponding records on oracleDB have CREATION_DATE > LAST_UPDATE_DATE
94
95 update s
96 set s.MethodID = o.MethodID,
97 s.Serial = o.Serial,
98 s.BankID = o.BankID,
99 s.BranchID = o.BranchID,
100 s.AccountID = o.AccountID,
101 s.AccountNo = o.AccountNum
102 from AR_CashierReceiptMethodDetails as s
103 inner join #tmp1
104 as o on s.MethodID = o.MethodID and o.AccountID = s.AccountID
105
106
107 SET @ErrorVar = @@ERROR
108 IF @ErrorVar <> 0
109 return @ErrorVar
110
111
112 --Insert records on SBDB that exists on oracleDB only & have LAST_UPDATE_DATE>LastSnapshotDate
113 insert into AR_CashierReceiptMethodDetails (MethodID,Serial,BankID,BranchID,AccountID,AccountNo)
114 select o.MethodID,o.Serial,o.BankID,o.BranchID,o.AccountID,o.AccountNum
115 from
116 #tmp1 as o left outer join AR_CashierReceiptMethodDetails s
117 on o.MethodID = s.MethodID and o.AccountID = s.AccountID
118 where s.MethodID Is Null
119
120
121 SET @ErrorVar = @@ERROR
122 IF @ErrorVar <> 0
123 return @ErrorVar
124 drop table #tmp1
125
126END