· 8 years ago · Aug 27, 2018, 07:32 AM
1GO
2
3alter table AxLegalEntity add [Type] tinyint
4GO
5update AxLegalEntity set [Type] = case when substring(name,1,1)='D' then 1 else 2 end
6
7
8GO
9
10
11CREATE procedure SpCloseCaseAfterLoadUpdate
12 @ageSeconds int
13as
14begin
15 set nocount on;
16 declare @trancount int;
17 set @trancount = @@trancount;
18 begin try
19 if @trancount = 0
20 begin transaction
21 else
22 save transaction usp_my_procedure_name;
23
24 -- Do the actual work here
25
26
27 -- get a list of commissionids to work with
28 declare @commissionIds table(CommissionId uniqueidentifier, LoadItemId bigint, LoadId bigint, TransferId bigint, StatusId int, SubStatusId int, NewSubStatus int)
29
30 insert into @commissionIds
31 select
32 c.CommissionId, li.LoadItemId, li.LoadId, l.TransferId, c.CommissionStatusTypeId, c.CommissionSubStatusTypeId, nullif(SUBSTRING(lip.PropertyValue, CHARINDEX(';', lip.PropertyValue)+1, 100), '')
33 from LoadItemProperty lip
34 left join LoadItemCommission lc on lc.LoadItemId = lip.LoadItemId
35 left join Commission c on c.CommissionId = lc.CommissionId
36 join LoadItem li on li.LoadItemId = lip.LoadItemId
37 join Load l on l.LoadId = li.LoadId
38 -- Property 15 is really a DestinationProperty but the readymark code set it on LoadItem level.
39 -- This is the assumption we make and is what makes it so that we only close cases that are readymarked with the TDP code.
40 where lip.PropertyId = 15 /*ReadyMarkPalletStatus*/
41 and dateadd(ss, @ageSeconds, li.DateChanged) < getdate()
42 --select * from @commissionIds
43
44
45
46 -- close commission with substatus from LoadItemProperty
47 update Commission
48 set
49 CommissionStatusTypeId = 3/*CommissionStatus.Closed*/,
50 CommissionSubStatusTypeId = i.NewSubStatus
51 from @commissionIds i
52 join LoadItemCommission lic on i.CommissionId = lic.CommissionId
53 join LoadItemProperty lip on lip.LoadItemId = lic.LoadItemId and lip.PropertyId = 15 /*ReadyMarkPalletStatus*/
54 where i.CommissionId = Commission.CommissionId
55
56
57 -- create CommissionLog for Status change
58 insert into CommissionLog(
59 CommissionLogId,
60 CommissionId,
61 Matter,
62 Created,
63 CreatedBy,
64 Modified,
65 ModifiedBy,
66 CommissionStatusTypeId,
67 CommissionLogTypeId,
68 OwnerGroup,
69 OwnerGroupPerson,
70 CommissionSubStatusTypeId,
71 ReceiverGroup,
72 ReceiverGroupPerson,
73 LocationId,
74 RelocationTypeId)
75 select
76 newid(),
77 c.CommissionId,
78 cast(i.StatusId as nvarchar(20)) + ';' + cast(c.CommissionStatusTypeId as nvarchar(20)),
79 GETDATE(),
80 'SYS',
81 GETDATE(),
82 '',
83 c.CommissionStatusTypeId,
84 4 /*EntryType.StatusChange*/,
85 c.OwnerGroupId,
86 c.UserId,
87 c.CommissionSubStatusTypeId,
88 c.ReceiverGroupId,
89 c.ReceiverUserId,
90 0,
91 null
92 from @commissionIds i
93 join Commission c on i.CommissionId = c.CommissionId
94
95
96 -- create CommissionLog for SubStatus change
97 insert into CommissionLog(
98 CommissionLogId,
99 CommissionId,
100 Matter,
101 Created,
102 CreatedBy,
103 Modified,
104 ModifiedBy,
105 CommissionStatusTypeId,
106 CommissionLogTypeId,
107 OwnerGroup,
108 OwnerGroupPerson,
109 CommissionSubStatusTypeId,
110 ReceiverGroup,
111 ReceiverGroupPerson,
112 LocationId,
113 RelocationTypeId)
114 select
115 newid(),
116 c.CommissionId,
117 cast(i.SubStatusId as nvarchar(20)) + ';' + cast(c.CommissionSubStatusTypeId as nvarchar(20)),
118 GETDATE(),
119 'SYS',
120 GETDATE(),
121 '',
122 c.CommissionStatusTypeId,
123 9 /*SubStatusChange*/,
124 c.OwnerGroupId,
125 c.UserId,
126 c.CommissionSubStatusTypeId,
127 c.ReceiverGroupId,
128 c.ReceiverUserId,
129 0,
130 null
131 from @commissionIds i
132 join Commission c on i.CommissionId = c.CommissionId
133 where i.SubStatusId <> i.NewSubStatus
134
135
136 -- create CommissionLog for deleted LoadItemCommission
137 insert into CommissionLog(
138 CommissionLogId,
139 CommissionId,
140 Matter,
141 Created,
142 CreatedBy,
143 Modified,
144 ModifiedBy,
145 CommissionStatusTypeId,
146 CommissionLogTypeId,
147 OwnerGroup,
148 OwnerGroupPerson,
149 CommissionSubStatusTypeId,
150 ReceiverGroup,
151 ReceiverGroupPerson,
152 LocationId,
153 RelocationTypeId)
154 select
155 newid(),
156 c.CommissionId,
157 lic.LoadItemId,
158 GETDATE(),
159 'SYS',
160 GETDATE(),
161 '',
162 c.CommissionStatusTypeId,
163 17 /*LoadItemConnectionRemoved*/,
164 c.OwnerGroupId,
165 c.UserId,
166 c.CommissionSubStatusTypeId,
167 c.ReceiverGroupId,
168 c.ReceiverUserId,
169 0,
170 null
171 from @commissionIds i
172 join Commission c on i.CommissionId = c.CommissionId
173 join LoadItemCommission lic on i.CommissionId = lic.CommissionId
174
175
176 -- delete LoadItemCommission
177 delete LoadItemCommission
178 from LoadItemCommission lic
179 join @commissionIds i on i.CommissionId = lic.CommissionId
180
181 -- delete LoadItemProperty
182 delete LoadItemProperty
183 from LoadItemProperty lip
184 join @commissionIds i on i.LoadItemId = lip.LoadItemId
185
186 -- delete LoadItemOrder
187 delete LoadItemOrder
188 from LoadItemOrder lio
189 join @commissionIds i on i.LoadItemId = lio.LoadItemId
190
191 -- delete LoadItem
192 delete LoadItem
193 from LoadItem li
194 join @commissionIds i on i.LoadItemId = li.LoadItemId
195
196 -- delete LoadProperty for EMPTY Load
197 delete LoadProperty
198 from LoadProperty lp
199 join (select distinct LoadId from @commissionIds) i on i.LoadId = lp.LoadId
200 where not exists (select 1 from LoadItem where LoadId = lp.LoadId)
201
202 -- delete EMPTY Load
203 delete Load
204 from Load l
205 join (select distinct LoadId from @commissionIds) i on i.LoadId = l.LoadId
206 where not exists (select 1 from LoadItem where LoadId = l.LoadId)
207 -- may not be any sub-Load with LoadItem(s)
208 and not exists (select 1 from Load l2
209 join Loaditem li2 on li2.LoadId = l2.LoadId
210 where l2.ParentLoadId = l.LoadId)
211
212 -- delete TransferProperty for EMPTY Transfer
213 delete TransferProperty
214 from TransferProperty tp
215 join (select distinct TransferId from @commissionIds) i on i.TransferId = tp.TransferId
216 where not exists (select 1 from Load where TransferId = tp.TransferId)
217
218 -- delete EMPTY Transfer
219 delete Transfer
220 from Transfer t
221 join (select distinct TransferId from @commissionIds) i on i.TransferId = t.TransferId
222 where not exists (select 1 from Load where TransferId = t.TransferId)
223
224
225
226lbexit:
227 if @trancount = 0
228 commit;
229 end try
230 begin catch
231 declare @error int, @message varchar(4000), @xstate int;
232 select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
233 if @xstate = -1
234 rollback;
235 if @xstate = 1 and @trancount = 0
236 rollback
237 if @xstate = 1 and @trancount > 0
238 rollback transaction usp_my_procedure_name;
239
240 raiserror ('SpCloseCaseAfterLoadUpdate: %d: %s', 16, 1, @error, @message) ;
241 end catch
242end
243go