[Home] [Help]
PACKAGE BODY: APPS.PO_FORWARD_SV1
Source
1 PACKAGE BODY po_forward_sv1 AS
2 /* $Header: POXAPFOB.pls 120.1.12020000.3 2013/02/10 11:41:06 vegajula ship $*/
3 /*===========================================================================
4
5 PROCEDURE NAME: test_insert_action_history
6
7 ===========================================================================*/
8
9 PROCEDURE test_insert_action_history (x_object_id IN NUMBER,
10 x_object_type_code IN VARCHAR2,
11 x_object_sub_type_code IN VARCHAR2,
12 x_sequence_num IN NUMBER,
13 x_action_code IN VARCHAR2,
14 x_action_date IN DATE,
15 x_employee_id IN NUMBER,
16 x_approval_path_id IN NUMBER,
17 x_note IN VARCHAR2,
18 x_object_revision_num IN NUMBER,
19 x_offline_code IN VARCHAR2,
20 x_request_id IN NUMBER,
21 x_program_application_id IN NUMBER,
22 x_program_id IN NUMBER,
23 x_program_date IN DATE,
24 x_user_id IN NUMBER,
25 x_login_id IN NUMBER) IS
26 BEGIN
27
28 --dbms_output.put_line('before call');
29
30 insert_action_history (x_object_id,
31 x_object_type_code,
32 x_object_sub_type_code,
33 x_sequence_num,
34 x_action_code,
35 x_action_date,
36 x_employee_id,
37 x_approval_path_id,
38 x_note,
39 x_object_revision_num,
40 x_offline_code,
41 x_request_id,
42 x_program_application_id,
43 x_program_id,
44 x_program_date,
45 x_user_id,
46 x_login_id);
47
48 --dbms_output.put_line('after call');
49
50 END;
51
52 /*===========================================================================
53
54 PROCEDURE NAME: insert_action_history
55
56 ===========================================================================*/
57
58 PROCEDURE insert_action_history (x_object_id IN NUMBER,
59 x_object_type_code IN VARCHAR2,
60 x_object_sub_type_code IN VARCHAR2,
61 x_sequence_num IN NUMBER,
62 x_action_code IN VARCHAR2,
63 x_action_date IN DATE,
64 x_employee_id IN NUMBER,
65 x_approval_path_id IN NUMBER,
66 x_note IN VARCHAR2,
67 x_object_revision_num IN NUMBER,
68 x_offline_code IN VARCHAR2,
69 x_request_id IN NUMBER,
70 x_program_application_id IN NUMBER,
71 x_program_id IN NUMBER,
72 x_program_date IN DATE,
73 x_user_id IN NUMBER,
74 x_login_id IN NUMBER,
75 x_approval_group_id IN NUMBER)
76 IS
77 x_progress VARCHAR2(3) := '';
78 x_db_sequence_num PO_ACTION_HISTORY.sequence_num%TYPE := NULL;
79
80 BEGIN
81
82 x_db_sequence_num := x_sequence_num;
83
84 IF x_db_sequence_num is NULL THEN
85
86 --<ENCUMBRANCE FPJ START>
87
88 x_progress := '010';
89
90 SELECT MAX(sequence_num)
91 INTO x_db_sequence_num
92 FROM PO_ACTION_HISTORY
93 WHERE object_id = x_object_id
94 AND object_type_code = x_object_type_code;
95
96 x_progress := '020';
97
98 IF (x_db_sequence_num IS NULL) THEN
99 x_progress := '030';
100 -- The action history sequence_num starts at 1.
101 x_db_sequence_num := 1; --Bug 13579433
102 ELSE
103 x_progress := '040';
104 -- Use the next sequence num.
105 x_db_sequence_num := x_db_sequence_num + 1;
106 END IF;
107
108 x_progress := '050';
109
110 --<ENCUMBRANCE FPJ END>
111
112 END IF;
113
114 x_progress := '100';
115
116 --dbms_output.put_line('Before insert');
117
118 INSERT INTO PO_ACTION_HISTORY
119 (object_id,
120 object_type_code,
121 object_sub_type_code,
122 sequence_num,
123 last_update_date,
124 last_updated_by,
128 note,
125 employee_id,
126 action_code,
127 action_date,
129 object_revision_num,
130 last_update_login,
131 creation_date,
132 created_by,
133 request_id,
134 program_application_id,
135 program_id,
136 program_date,
137 approval_path_id,
138 offline_code,
139 program_update_date,
140 approval_group_id)
141 VALUES (x_object_id,
142 x_object_type_code,
143 x_object_sub_type_code,
144 x_db_sequence_num,
145 sysdate,
146 x_user_id,
147 x_employee_id,
148 x_action_code,
149 x_action_date,
150 x_note,
151 x_object_revision_num,
152 x_login_id,
153 sysdate,
154 x_user_id,
155 x_request_id,
156 x_program_application_id,
157 x_program_id,
158 x_program_date,
159 x_approval_path_id,
160 x_offline_code,
161 sysdate,
162 x_approval_group_id);
163
164 --dbms_output.put_line('After insert');
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 --dbms_output.put_line('Exception in insert_action_history');
169 PO_MESSAGE_S.SQL_ERROR('INSERT_ACTION_HISTORY', x_progress, sqlcode);
170 RAISE;
171 END;
172
173
174 PROCEDURE insert_action_history( x_object_id IN NUMBER,
175 x_object_type_code IN VARCHAR2,
176 x_object_sub_type_code IN VARCHAR2,
177 x_sequence_num IN NUMBER,
178 x_action_code IN VARCHAR2,
179 x_action_date IN DATE,
180 x_employee_id IN NUMBER,
181 x_approval_path_id IN NUMBER,
182 x_note IN VARCHAR2,
183 x_object_revision_num IN NUMBER,
184 x_offline_code IN VARCHAR2,
185 x_request_id IN NUMBER,
186 x_program_application_id IN NUMBER,
187 x_program_id IN NUMBER,
188 x_program_date IN DATE,
189 x_user_id IN NUMBER,
190 x_login_id IN NUMBER)
191 IS
192
193 BEGIN
194
195 -- invoke the wrapper procedure insert_action_history with one more additional input parameter.
196 -- We pass NULL value for the new column approval_group_id.
197 insert_action_history( x_object_id,
198 x_object_type_code,
199 x_object_sub_type_code,
200 x_sequence_num,
201 x_action_code,
202 x_action_date,
203 x_employee_id,
204 x_approval_path_id,
205 x_note,
206 x_object_revision_num,
207 x_offline_code,
208 x_request_id,
209 x_program_application_id,
210 x_program_id,
211 x_program_date,
212 x_user_id,
213 x_login_id,
214 NULL);
215
216 END;
217
218 /*===========================================================================
219
220 PROCEDURE NAME: test_insert_all_action_history
221
222 ===========================================================================*/
223
224 PROCEDURE test_insert_all_action_history (x_old_employee_id IN NUMBER,
225 x_new_employee_id IN NUMBER,
226 x_offline_code IN VARCHAR2,
227 x_user_id IN NUMBER,
228 x_login_id IN NUMBER) IS
229 BEGIN
230
231 --dbms_output.put_line('before call');
232
233 insert_all_action_history (x_old_employee_id,
234 x_new_employee_id,
235 x_offline_code,
236 x_user_id,
237 x_login_id);
238
239 --dbms_output.put_line('after call');
240
241 END;
242
243 /*===========================================================================
244
245 PROCEDURE NAME: insert_all_action_history
246
247 ===========================================================================*/
248
249 PROCEDURE insert_all_action_history (x_old_employee_id IN NUMBER,
250 x_new_employee_id IN NUMBER,
251 x_offline_code IN VARCHAR2,
252 x_user_id IN NUMBER,
253 x_login_id IN NUMBER)
254 IS
255 x_progress VARCHAR2(3) := '';
256 BEGIN
257
258 x_progress := '010';
259
260 IF (x_old_employee_id IS NOT NULL AND
261 x_new_employee_id IS NOT NULL) THEN
262
263 x_progress := '020';
264 --dbms_output.put_line('Before Insert');
265
266 INSERT INTO PO_ACTION_HISTORY
267 (object_id,
268 object_type_code,
269 object_sub_type_code,
270 sequence_num,
271 last_update_date,
272 last_updated_by,
276 note,
273 action_date,
274 employee_id,
275 action_code,
277 object_revision_num,
278 last_update_login,
279 creation_date,
280 created_by,
281 request_id,
282 program_application_id,
283 program_id,
284 program_date,
285 approval_path_id,
286 offline_code,
287 program_update_date)
288 SELECT object_id,
289 object_type_code,
290 object_sub_type_code,
291 sequence_num + 1,
292 sysdate,
293 x_user_id,
294 NULL,
295 x_new_employee_id,
296 NULL,
297 NULL,
298 object_revision_num,
299 x_login_id,
300 sysdate,
301 x_user_id,
302 request_id,
303 program_application_id,
304 program_id,
305 sysdate,
306 approval_path_id,
307 x_offline_code,
308 sysdate
309 FROM PO_ACTION_HISTORY
310 WHERE employee_id = x_old_employee_id
311 AND action_code IS NULL;
312
313 --dbms_output.put_line('After Insert');
314
315 ELSE
316 x_progress := '030';
317 PO_MESSAGE_S.SQL_ERROR('INSERT_ALL_ACTION_HISTORY', x_progress, sqlcode);
318 END IF;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 --dbms_output.put_line('In Exception');
323 PO_MESSAGE_S.SQL_ERROR('INSERT_ALL_ACTION_HISTORY', x_progress, sqlcode);
324 RAISE;
325 END;
326
327 /*===========================================================================
328
329 PROCEDURE NAME: test_update_action_history
330
331 ===========================================================================*/
332
333 /* PROCEDURE test_update_action_history (x_object_id IN NUMBER,
334 x_object_type_code IN VARCHAR2,
335 x_old_employee_id IN NUMBER,
336 x_action_code IN VARCHAR2,
337 x_note IN VARCHAR2,
338 x_user_id IN NUMBER,
339 x_login_id IN NUMBER) IS
340 BEGIN
341
342 --dbms_output.put_line('before call');
343
344 update_action_history (x_object_id,
345 x_object_type_code,
346 x_old_employee_id,
347 x_action_code,
348 x_note,
349 x_user_id,
350 x_login_id);
351
352 --dbms_output.put_line('after call');
353
354 END;
355 */
356 /*===========================================================================
357
358 PROCEDURE NAME: update_action_history
359
360 ===========================================================================*/
361
362 PROCEDURE update_action_history (x_object_id IN NUMBER,
363 x_object_type_code IN VARCHAR2,
364 x_old_employee_id IN NUMBER,
365 x_action_code IN VARCHAR2,
366 x_note IN VARCHAR2,
367 x_user_id IN NUMBER,
368 x_login_id IN NUMBER)
369 IS
370 x_progress VARCHAR2(3) := '';
371 x_employee_id NUMBER ;
372
373 BEGIN
374 x_progress := '010';
375
376 IF (x_object_id IS NOT NULL AND
377 x_object_type_code IS NOT NULL) THEN
378
379 x_progress := '020';
380 -- dbms_output.put_line('Before Update');
381
382 /* Bug# 1326148: Amitabh
383 ** Desc: The update_action_history() procedure has been modified to update t he
384 ** employee_id also in the action history. Employee id should belong to the
385 ** id of the corresponding user taking the action, not the employee id to
386 ** which the req was forwarded to.
387 */
388 If (x_old_employee_id is NULL) then
389 SELECT HR.EMPLOYEE_ID
390 INTO x_employee_id
391 FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
392 WHERE FND.USER_ID = NVL(x_user_id, fnd_global.user_id)
393 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID;
394 end if;
395
396 UPDATE PO_ACTION_HISTORY
397 SET last_update_date = sysdate,
398 last_updated_by = x_user_id,
399 last_update_login = x_login_id,
400 employee_id = NVL(x_employee_id, employee_id),
401 action_date = sysdate,
402 action_code = x_action_code,
403 note = x_note,
404 offline_code = decode(offline_code,
405 'PRINTED', 'PRINTED', NULL)
406 WHERE employee_id = NVL(x_old_employee_id, employee_id)
407 AND object_id = x_object_id
408 AND object_type_code = x_object_type_code
409 AND action_code IS NULL;
410
411 ELSE
412 x_progress := '030';
413 PO_MESSAGE_S.SQL_ERROR('UPDATE_ACTION_HISTORY', x_progress, sqlcode);
414 END IF;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 --dbms_output.put_line('Exception in update_action_history');
419 PO_MESSAGE_S.SQL_ERROR('UPDATE_ACTION_HISTORY', x_progress, sqlcode);
420 RAISE;
421 END;
422
423
424 /*===========================================================================
425
426 PROCEDURE NAME: test_update_all_action_history
427
428 ===========================================================================*/
429
430 PROCEDURE test_update_all_action_history (x_old_employee_id IN NUMBER,
431 x_note IN VARCHAR2,
432 x_user_id IN NUMBER,
433 x_login_id IN NUMBER) IS
434 BEGIN
435
436 --dbms_output.put_line('before call');
437
438 update_all_action_history (x_old_employee_id,
439 x_note,
440 x_user_id,
441 x_login_id);
442
443 --dbms_output.put_line('after call');
444
445 END;
446
447 /*===========================================================================
448
449 PROCEDURE NAME: update_all_action_history
450
451 ===========================================================================*/
452
453 PROCEDURE update_all_action_history (x_old_employee_id IN NUMBER,
454 x_note IN VARCHAR2,
455 x_user_id IN NUMBER,
456 x_login_id IN NUMBER)
457 IS
458 x_progress VARCHAR2(3) := '';
459 BEGIN
460 x_progress := '010';
461
462 IF (x_old_employee_id IS NOT NULL) THEN
463
464 x_progress := '020';
465 --dbms_output.put_line('Before Update');
466
467 UPDATE PO_ACTION_HISTORY
468 SET last_update_date = sysdate,
469 last_updated_by = x_user_id,
470 last_update_login = x_login_id,
471 action_date = sysdate,
472 action_code = 'FORWARD',
473 note = x_note,
474 offline_code = decode(offline_code,
475 'PRINTED', 'PRINTED', NULL)
476 WHERE employee_id = x_old_employee_id
477 AND action_code IS NULL;
478
479 ELSE
480 x_progress := '030';
481 PO_MESSAGE_S.SQL_ERROR('UPDATE_ALL_ACTION_HISTORY', x_progress, sqlcode);
482 END IF;
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 --dbms_output.put_line('In Exception');
487 PO_MESSAGE_S.SQL_ERROR('UPDATE_ALL_ACTION_HISTORY', x_progress, sqlcode);
488 RAISE;
489 END;
490
491 /*===========================================================================
492
493 PROCEDURE NAME: lock_row
494
495 ===========================================================================*/
496
497 PROCEDURE lock_row (x_rowid VARCHAR2,
498 x_last_update_date DATE) IS
499 CURSOR C IS
500 SELECT *
501 FROM po_action_history
502 WHERE rowid = x_rowid
503 FOR UPDATE of sequence_num NOWAIT;
504 Recinfo C%ROWTYPE;
505
506 BEGIN
507 OPEN C;
508 FETCH C INTO Recinfo;
509 IF (C%NOTFOUND) then
510 CLOSE C;
511 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
512 APP_EXCEPTION.Raise_Exception;
513 END IF;
514 CLOSE C;
515
516 IF (Recinfo.last_update_date = x_last_update_date) THEN
517 return;
518 ELSE
519 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
520 APP_EXCEPTION.RAISE_EXCEPTION;
521 END IF;
522
523 END;
524
525 END PO_FORWARD_SV1;