1 Package Body IRC_OFFER_STATUS_HISTORY_API as
2 /* $Header: iriosapi.pkb 120.3.12000000.2 2007/06/22 13:37:33 gaukumar noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'IRC_OFFER_STATUS_HISTORY_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------< create_offer_status_history >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_offer_status_history
13 ( P_VALIDATE IN boolean default false
14 ,P_EFFECTIVE_DATE IN DATE default null
15 ,P_OFFER_ID IN NUMBER
16 ,P_STATUS_CHANGE_DATE IN DATE default null
17 ,P_OFFER_STATUS IN VARCHAR2
18 ,P_CHANGE_REASON IN VARCHAR2 default null
19 ,P_DECLINE_REASON IN VARCHAR2 default null
20 ,P_NOTE_TEXT IN VARCHAR2 default null
21 ,P_OFFER_STATUS_HISTORY_ID OUT nocopy NUMBER
22 ,P_OBJECT_VERSION_NUMBER OUT nocopy NUMBER
23 ) is
24 --
25 -- Declare cursors and local variables
26 --
27 l_proc varchar2(72) := g_package||'create_offer_status_history';
28 l_offer_status_history_id number;
29 l_object_version_number number := 1;
30 l_effective_date date;
31 l_status_change_date date;
32 l_note_id number;
33 l_note_ovn number;
34 --
35 begin
36 hr_utility.set_location('Entering:'|| l_proc, 10);
37 --
38 -- Issue a savepoint
39 --
40 savepoint CREATE_OFFER_STATUS_HISTORY;
41 --
42 -- Truncate the time portion from all IN date parameters
43 --
44 l_effective_date := trunc(p_effective_date);
45 if (P_STATUS_CHANGE_DATE is null) then
46 l_status_change_date := sysdate;
47 else
48 l_status_change_date := p_status_change_date;
49 end if;
50 --adding a sec into the time the hold status is generated
51 --so that the previous status is always a sec before it
52 --we need this so that we can access d record before the hold
53 --status on the basis of time
54 if(P_OFFER_STATUS='HOLD') then
55 l_status_change_date := l_status_change_date+(1/(24*60*60));
56 end if;
57
58 --
59 -- Call Before Process User Hook
60 --
61 begin
62 IRC_OFFER_STATUS_HISTORY_BK1.create_offer_status_history_b
63 ( P_EFFECTIVE_DATE => l_effective_date
64 ,P_OFFER_ID => P_OFFER_ID
65 ,P_STATUS_CHANGE_DATE => l_status_change_date
66 ,P_OFFER_STATUS => P_OFFER_STATUS
67 ,P_CHANGE_REASON => P_CHANGE_REASON
68 ,P_DECLINE_REASON => P_DECLINE_REASON
69 ,P_NOTE_TEXT => P_NOTE_TEXT
70 );
71 exception
72 when hr_api.cannot_find_prog_unit then
73 hr_api.cannot_find_prog_unit_error
74 (p_module_name => 'CREATE_OFFER_STATUS_HISTORY'
75 ,p_hook_type => 'BP'
76 );
77 end;
78 --
79 -- Process Logic
80 --
81 irc_ios_ins.ins
82 (
83 p_effective_date => l_effective_date
84 ,p_offer_id => p_offer_id
85 ,p_status_change_date => l_status_change_date
86 ,p_offer_status => p_offer_status
87 ,p_change_reason => p_change_reason
88 ,p_decline_reason => p_decline_reason
89 ,p_offer_status_history_id => l_offer_status_history_id
90 ,p_object_version_number => l_object_version_number
91 );
92 --
93 -- Create note record for the newly created offer status history record.
94 --
95 if (P_NOTE_TEXT is not null) then
96 irc_notes_api.create_note
97 (
98 p_validate => p_validate
99 ,p_offer_status_history_id => l_offer_status_history_id
100 ,p_note_text => p_note_text
101 ,p_note_id => l_note_id
102 ,p_object_version_number => l_note_ovn
103 );
104 end if;
105 --
106 -- Call After Process User Hook
107 --
108 begin
109 IRC_OFFER_STATUS_HISTORY_BK1.create_offer_status_history_a
110 ( P_EFFECTIVE_DATE => l_effective_date
111 ,P_OFFER_ID => P_OFFER_ID
112 ,P_STATUS_CHANGE_DATE => l_status_change_date
113 ,P_OFFER_STATUS => P_OFFER_STATUS
114 ,P_CHANGE_REASON => P_CHANGE_REASON
115 ,P_DECLINE_REASON => P_DECLINE_REASON
116 ,P_NOTE_TEXT => P_NOTE_TEXT
117 ,P_OFFER_STATUS_HISTORY_ID => l_offer_status_history_id
118 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
119 );
120 exception
121 when hr_api.cannot_find_prog_unit then
122 hr_api.cannot_find_prog_unit_error
123 (p_module_name => 'CREATE_OFFER_STATUS_HISTORY'
124 ,p_hook_type => 'AP'
125 );
126 end;
127 --
128 -- When in validation only mode raise the Validate_Enabled exception
129 --
130 if p_validate then
131 raise hr_api.validate_enabled;
132 end if;
133 --
134 -- Set all IN OUT and OUT parameters with out values
135 --
136 p_offer_status_history_id := l_offer_status_history_id;
137 p_object_version_number := l_object_version_number;
138 --
139 exception
140 when hr_api.validate_enabled then
141 --
142 -- As the Validate_Enabled exception has been raised
143 -- we must rollback to the savepoint
144 --
145 rollback to CREATE_OFFER_STATUS_HISTORY;
146 --
147 -- Reset IN OUT parameters and set OUT parameters
148 -- (Any key or derived arguments must be set to null
149 -- when validation only mode is being used.)
150 --
151 p_offer_status_history_id := null;
152 p_object_version_number := null;
153 hr_utility.set_location(' Leaving:'||l_proc, 80);
154 when others then
155 --
156 -- A validation or unexpected error has occured
157 --
158 rollback to CREATE_OFFER_STATUS_HISTORY;
159 --
160 -- Reset IN OUT parameters and set all
161 -- OUT parameters, including warnings, to null
162 --
163 p_offer_status_history_id := null;
164 p_object_version_number := null;
165 hr_utility.set_location(' Leaving:'||l_proc, 90);
166 raise;
167 end CREATE_OFFER_STATUS_HISTORY;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |---------------------< update_offer_status_history >----------------------|
171 -- ----------------------------------------------------------------------------
172 --
173 procedure update_offer_status_history
174 ( P_VALIDATE IN boolean default false
175 ,P_EFFECTIVE_DATE IN DATE default null
176 ,P_OFFER_STATUS_HISTORY_ID IN NUMBER
177 ,P_OFFER_ID IN NUMBER
178 ,P_STATUS_CHANGE_DATE IN DATE
179 ,P_OFFER_STATUS IN VARCHAR2
180 ,P_CHANGE_REASON IN VARCHAR2 default null
181 ,P_DECLINE_REASON IN VARCHAR2 default null
182 ,P_NOTE_TEXT IN VARCHAR2 default null
183 ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
184 ) is
185 --
186 -- Declare cursors and local variables
187 --
188 l_object_version_number number(9);
189 l_proc varchar2(72) := g_package||'UPDATE_OFFER_STATUS_HISTORY';
190 --l_offer_status_history_id number;
191
192 l_note_id irc_notes.note_id%type;
193 l_note_ovn irc_notes.object_version_number%type;
194 l_effective_date date;
195 l_status_change_date date;
196 --
197 begin
198 hr_utility.set_location('Entering:'|| l_proc, 10);
199 --
200 -- Issue a savepoint
201 --
202 savepoint UPDATE_OFFER_STATUS_HISTORY;
203 --
204 -- Truncate the time portion from all IN date parameters
205 --
206 l_effective_date := trunc(p_effective_date);
207 if (P_STATUS_CHANGE_DATE is null) then
208 l_status_change_date := sysdate;
209 else
210 l_status_change_date := p_status_change_date;
211 end if;
212
213 --adding a sec into the time the hold status is generated
214 --so that the previous status is always a sec before it
215 --we need this so that we can access d record before the hold
216 --status on the basis of time
217 if(P_OFFER_STATUS='HOLD') then
218 l_status_change_date := l_status_change_date+(1/(24*60*60));
219 end if;
220
221 --
222 -- Call Before Process User Hook
223 --
224 begin
225 IRC_OFFER_STATUS_HISTORY_BK2.update_offer_status_history_b
226 (P_EFFECTIVE_DATE => l_effective_date
227 ,P_OFFER_STATUS_HISTORY_ID => P_OFFER_STATUS_HISTORY_ID
228 ,P_STATUS_CHANGE_DATE => l_status_change_date
229 ,P_CHANGE_REASON => P_CHANGE_REASON
230 ,P_DECLINE_REASON => P_DECLINE_REASON
231 ,P_NOTE_TEXT => P_NOTE_TEXT
232 );
233 exception
234 when hr_api.cannot_find_prog_unit then
235 hr_api.cannot_find_prog_unit_error
236 (p_module_name => 'UPDATE_OFFER_STATUS_HISTORY'
237 ,p_hook_type => 'BP'
238 );
239 end;
240 --
241 -- Process Logic
242 --
243 l_object_version_number := p_object_version_number;
244 --
245 -- Create note record for the updated offer status history record.
246 --
247 if (P_NOTE_TEXT is not null) then
248 irc_notes_api.create_note
249 (
250 p_validate => p_validate
251 ,p_offer_status_history_id => p_offer_status_history_id
252 ,p_note_text => p_note_text
253 ,p_note_id => l_note_id
254 ,p_object_version_number => l_note_ovn
255 );
256 end if;
257 --
258 -- Call After Process User Hook
259 --
260 begin
261 IRC_OFFER_STATUS_HISTORY_BK2.update_offer_status_history_a
262 (P_EFFECTIVE_DATE => l_effective_date
263 ,P_OFFER_STATUS_HISTORY_ID => P_OFFER_STATUS_HISTORY_ID
264 ,P_STATUS_CHANGE_DATE => l_status_change_date
265 ,P_CHANGE_REASON => P_CHANGE_REASON
266 ,P_DECLINE_REASON => P_DECLINE_REASON
267 ,P_NOTE_TEXT => P_NOTE_TEXT
268 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
269 );
270 exception
271 when hr_api.cannot_find_prog_unit then
272 hr_api.cannot_find_prog_unit_error
273 (p_module_name => 'UPDATE_OFFER_STATUS_HISTORY'
274 ,p_hook_type => 'AP'
275 );
276 end;
277 --
278 -- When in validation only mode raise the Validate_Enabled exception
279 --
280 if p_validate then
281 raise hr_api.validate_enabled;
282 end if;
283 --
284 -- Set all IN OUT and OUT parameters with out values
285 --
286 p_object_version_number := l_object_version_number;
287 --
288 hr_utility.set_location(' Leaving:'||l_proc, 70);
289 exception
290 when hr_api.validate_enabled then
291 --
292 -- As the Validate_Enabled exception has been raised
293 -- we must rollback to the savepoint
294 --
295 rollback to UPDATE_OFFER_STATUS_HISTORY;
296 --
297 -- Reset IN OUT parameters and set OUT parameters
298 -- (Any key or derived arguments must be set to null
299 -- when validation only mode is being used.)
300 --
301 p_object_version_number := null;
302 hr_utility.set_location(' Leaving:'||l_proc, 80);
303 when others then
304 --
305 -- A validation or unexpected error has occured
306 --
307 rollback to UPDATE_OFFER_STATUS_HISTORY;
308 --
309 -- Reset IN OUT parameters and set all
310 -- OUT parameters, including warnings, to null
311 --
312 p_object_version_number := null;
313 hr_utility.set_location(' Leaving:'||l_proc, 90);
314 raise;
315 end UPDATE_OFFER_STATUS_HISTORY;
316 --
317 --
318 -- ----------------------------------------------------------------------------
319 -- |----------------------< delete_offer_status_history >---------------------|
320 -- ----------------------------------------------------------------------------
321 --
322 procedure delete_offer_status_history
323 ( P_VALIDATE IN boolean default false
324 ,P_OFFER_ID IN NUMBER
325 ,P_EFFECTIVE_DATE IN DATE
326 ) is
327 --
328 --
329 -- Declare cursors and local variables
330 --
331 l_proc varchar2(72) := g_package||'DELETE_OFFER_STATUS_HISTORY';
332
333 cursor csr_history_records is
334 select offer_status_history_id,
335 object_version_number
336 from irc_offer_status_history
337 where offer_id = p_offer_id;
338
339 begin
340
341 hr_utility.set_location('Entering:'|| l_proc, 10);
342 --
343 -- Issue a savepoint
344 --
345 savepoint DELETE_OFFER_STATUS_HISTORY;
346 --
347 --loop thru all the records
348 --
349 for c_rec in csr_history_records loop
350 --
351 -- Truncate the time portion from all IN date parameters
352 --
353 -- Call Before Process User Hook
354 --
355 begin
356 IRC_OFFER_STATUS_HISTORY_BK3.delete_offer_status_history_b
357 ( P_OBJECT_VERSION_NUMBER => c_rec.object_version_number
358 ,P_OFFER_STATUS_HISTORY_ID => c_rec.offer_status_history_id
359 );
360 exception
361 when hr_api.cannot_find_prog_unit then
362 hr_api.cannot_find_prog_unit_error
363 (p_module_name => 'DELETE_OFFER_STATUS_HISTORY'
364 ,p_hook_type => 'BP'
365 );
366 end;
367 --
368 -- Process Logic
369 --
370 irc_ios_del.del
371 (P_OFFER_STATUS_HISTORY_ID => c_rec.offer_status_history_id
372 ,P_OBJECT_VERSION_NUMBER => c_rec.object_version_number
373 );
374 --
375 -- Call After Process User Hook
376 --
377 begin
378 IRC_OFFER_STATUS_HISTORY_BK3.delete_offer_status_history_a
379 ( P_OBJECT_VERSION_NUMBER => c_rec.object_version_number
380 ,P_OFFER_STATUS_HISTORY_ID => c_rec.offer_status_history_id
381 );
382 exception
383 when hr_api.cannot_find_prog_unit then
384 hr_api.cannot_find_prog_unit_error
385 (p_module_name => 'DELETE_OFFER_STATUS_HISTORY'
386 ,p_hook_type => 'AP'
387 );
388 end;
389 --
390 -- When in validation only mode raise the Validate_Enabled exception
391 --
392 if p_validate then
393 raise hr_api.validate_enabled;
394 end if;
395 --
396 hr_utility.set_location(' Leaving:'||l_proc, 70);
397 end loop;
398 exception
399 when hr_api.validate_enabled then
400 --
401 -- As the Validate_Enabled exception has been raised
402 -- we must rollback to the savepoint
403 --
404 rollback to DELETE_OFFER_STATUS_HISTORY;
405 --
406 hr_utility.set_location(' Leaving:'||l_proc, 80);
407 when others then
408 --
409 -- A validation or unexpected error has occured
410 --
411 rollback to DELETE_OFFER_STATUS_HISTORY;
412 --
413 hr_utility.set_location(' Leaving:'||l_proc, 90);
414 raise;
415 end DELETE_OFFER_STATUS_HISTORY;
416 --
417 end IRC_OFFER_STATUS_HISTORY_API;
[Home] [Help]