[Home] [Help]
PACKAGE BODY: APPS.PO_ACTION_HISTORY_SV
Source
1 PACKAGE BODY PO_ACTION_HISTORY_SV AS
2 -- $Header: POXACTHB.pls 115.0 2004/03/27 04:28:33 sbull noship $
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'PO_ACTION_HISTORY_SV';
5
6 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.' ;
7
8 -- Read the profile option that enables/disables the debug log
9 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
11
12
13
14
15 --------------------------------------------------------------------------------
16 -- Private procedures
17 --------------------------------------------------------------------------------
18
19 PROCEDURE initialize_vars(
20 x_employee_id IN OUT NOCOPY NUMBER
21 , x_user_id OUT NOCOPY NUMBER
22 , x_login_id OUT NOCOPY NUMBER
23 );
24
25
26 -------------------------------------------------------------------------------
27 --Start of Comments
28 --Name: update_action_history
29 --Pre-reqs:
30 -- None.
31 --Modifies:
32 -- PO_ACTION_HISTORY
33 --Locks:
34 -- None.
35 --Function:
36 -- Updates the action history entry for documents that currently have
37 -- a NULL entry in the action history.
38 --Parameters:
39 --IN:
40 --p_doc_id_tbl
41 -- Document header ids.
42 -- PO_HEADERS_ALL.po_header_id
43 -- PO_RELEASES_ALL.po_release_id
44 -- PO_REQUISITION_HEADERS_ALL.requisition_header_id
45 --p_doc_type_tbl
46 -- Document type corresponding to the IDs.
47 -- PO_ACTION_HISTORY.object_type_code
48 --p_action_code
49 -- The action with which to fill in the NULL entry.
50 -- PO_ACTION_HISTORY.action_code
51 --p_employee_id
52 -- The HR employee_id with which to record the action.
53 -- This id should correspond to PER_ALL_PEOPLE_F.person_id.
54 -- If NULL is passed, the employee_id will be retrieved
55 -- that corresponds to the current FND user (FND_USER.employee_id).
56 --Testing:
57 --
58 --End of Comments
59 -------------------------------------------------------------------------------
60 PROCEDURE update_action_history(
61 p_doc_id_tbl IN po_tbl_number
62 , p_doc_type_tbl IN po_tbl_varchar30
63 , p_action_code IN VARCHAR2
64 , p_employee_id IN NUMBER
65 DEFAULT NULL
66 )
67 IS
68
69 l_proc_name CONSTANT VARCHAR2(30) := 'UPDATE_ACTION_HISTORY';
70 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
71 l_progress VARCHAR2(3) := '000';
72
73 l_user_id NUMBER;
74 l_login_id NUMBER;
75 l_employee_id NUMBER;
76
77 BEGIN
78
79 IF g_debug_stmt THEN
80 PO_DEBUG.debug_begin(l_log_head);
81 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_id_tbl',p_doc_id_tbl);
82 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type_tbl',p_doc_type_tbl);
83 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action_code',p_action_code);
84 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
85 END IF;
86
87 l_progress := '010';
88
89 -- Do some high-level parameter validation.
90
91 IF (p_doc_id_tbl.COUNT <> p_doc_type_tbl.COUNT) THEN
92 l_progress := '020';
93 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
94 END IF;
95
96 l_progress := '100';
97
98 -- Initialize vars.
99
100 l_employee_id := p_employee_id;
101
102 l_progress := '110';
103
104 initialize_vars(
105 x_employee_id => l_employee_id
106 , x_user_id => l_user_id
107 , x_login_id => l_login_id
108 );
109
110 l_progress := '200';
111
112 -- We've got all the data we need, so update the table.
113
114 FORALL i IN 1 .. p_doc_id_tbl.COUNT
115 UPDATE PO_ACTION_HISTORY POAH
116 SET
117 POAH.last_update_date = SYSDATE
118 , POAH.last_updated_by = l_user_id
119 , POAH.action_code = p_action_code
120 , POAH.action_date = SYSDATE
121 , POAH.employee_id = NVL(l_employee_id, POAH.employee_id)
122 , POAH.note = NULL
123 , POAH.offline_code =
124 DECODE( POAH.offline_code
125 , 'PRINTED', 'PRINTED'
126 , NULL
127 )
128 , POAH.last_update_login = l_login_id
129 WHERE POAH.object_id = p_doc_id_tbl(i)
130 AND POAH.object_type_code = p_doc_type_tbl(i)
131 AND POAH.action_code IS NULL
132 ;
133
134 l_progress := '300';
135
136 IF g_debug_stmt THEN
137 PO_DEBUG.debug_var(l_log_head,l_progress,'SQL%ROWCOUNT',SQL%ROWCOUNT);
138 PO_DEBUG.debug_end(l_log_head);
139 END IF;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 PO_MESSAGE_S.sql_error(g_pkg_name,l_proc_name,l_progress,SQLCODE,SQLERRM);
144 RAISE;
145
146 END update_action_history;
147
148
149
150
151 -------------------------------------------------------------------------------
152 --Start of Comments
153 --Name: insert_action_history
154 --Pre-reqs:
155 -- None.
156 --Modifies:
157 -- PO_ACTION_HISTORY
158 --Locks:
159 -- None.
160 --Function:
161 -- Creates the specified action history entries.
162 --Parameters:
163 --IN:
164 --p_doc_id_tbl
165 -- Document header ids.
166 -- PO_HEADERS_ALL.po_header_id
167 -- PO_RELEASES_ALL.po_release_id
168 -- PO_REQUISITION_HEADERS_ALL.requisition_header_id
169 --p_doc_type_tbl
170 -- Document type corresponding to the IDs.
171 -- PO_ACTION_HISTORY.object_type_code
172 --p_doc_subtype_tbl
173 -- Document subtype.
174 -- PO_HEADERS_ALL.type_lookup_code
175 -- PO_RELEASES_ALL.release_type
176 -- PO_REQUISITION_HEADERS_ALL.type_lookup_code
177 --p_doc_revision_num_tbl
178 -- The revision number of the document.
179 -- PO_HEADERS_ALL.revision_num
180 -- PO_RELEASES_ALL.revision_num
181 -- These are NULL for Reqs.
182 --p_action_code_tbl
183 -- The actions to record.
184 -- PO_ACTION_HISTORY.action_code
185 -- The ordering of these actions are important for the
186 -- sequence_num of the action history entry.
187 -- SUBMIT should come before the other action.
188 --p_employee_id
189 -- The HR employee_id with which to record the action.
190 -- This id should correspond to PER_ALL_PEOPLE_F.person_id.
191 -- If NULL is passed, the employee_id will be retrieved
192 -- that corresponds to the current FND user (FND_USER.employee_id).
193 --Testing:
194 --
195 --End of Comments
196 -------------------------------------------------------------------------------
197 PROCEDURE insert_action_history(
198 p_doc_id_tbl IN po_tbl_number
199 , p_doc_type_tbl IN po_tbl_varchar30
200 , p_doc_subtype_tbl IN po_tbl_varchar30
201 , p_doc_revision_num_tbl IN po_tbl_number
202 , p_action_code_tbl IN po_tbl_varchar30
203 , p_employee_id IN NUMBER
204 DEFAULT NULL
205 )
206 IS
207
208 l_proc_name CONSTANT VARCHAR2(30) := 'INSERT_ACTION_HISTORY';
209 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
210 l_progress VARCHAR2(3) := '000';
211
212 l_user_id NUMBER;
213 l_login_id NUMBER;
214 l_employee_id NUMBER;
215
216 l_count NUMBER;
217
218 BEGIN
219
220 IF g_debug_stmt THEN
221 PO_DEBUG.debug_begin(l_log_head);
222 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_id_tbl',p_doc_id_tbl);
223 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type_tbl',p_doc_type_tbl);
224 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype_tbl',p_doc_subtype_tbl);
225 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_revision_num_tbl',p_doc_revision_num_tbl);
226 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action_code_tbl',p_action_code_tbl);
227 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
228 END IF;
229
230 l_progress := '010';
231
232 -- Do some high-level parameter validation.
233
234 l_count := p_doc_id_tbl.COUNT;
235
236 l_progress := '020';
237
238 IF ( l_count <> p_doc_type_tbl.COUNT
239 OR l_count <> p_doc_subtype_tbl.COUNT
240 OR l_count <> p_doc_revision_num_tbl.COUNT
241 OR l_count <> p_action_code_tbl.COUNT
242 ) THEN
243 l_progress := '030';
244 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
245 END IF;
246
247 l_progress := '100';
248
249 -- Initialize vars.
250
251 l_employee_id := p_employee_id;
252
253 l_progress := '110';
254
255 initialize_vars(
256 x_employee_id => l_employee_id
257 , x_user_id => l_user_id
258 , x_login_id => l_login_id
259 );
260
261 l_progress := '200';
262
263 -- Create the entries.
264 --
265 -- The SELECT is used to retrieve the sequence_num.
266 -- The NVL, MAX are arranged to return a row with 0 as the sequence_num
267 -- when no entries exist for the doc.
268
269 FORALL i IN 1 .. p_doc_id_tbl.COUNT
270 INSERT INTO PO_ACTION_HISTORY
271 ( object_id
272 , object_type_code
273 , object_sub_type_code
274 , sequence_num
275 , last_update_date
276 , last_updated_by
277 , creation_date
278 , created_by
279 , action_code
280 , action_date
281 , employee_id
282 , object_revision_num
283 , last_update_login
284 , program_update_date
285 )
286 SELECT
287 p_doc_id_tbl(i)
288 , p_doc_type_tbl(i)
289 , p_doc_subtype_tbl(i)
290 , NVL(MAX(POAH.sequence_num),-1) + 1
291 , SYSDATE
292 , l_user_id
293 , SYSDATE
294 , l_user_id
295 , p_action_code_tbl(i)
296 , SYSDATE
297 , l_employee_id
298 , p_doc_revision_num_tbl(i)
299 , l_login_id
300 , SYSDATE
301 FROM
302 PO_ACTION_HISTORY POAH
303 WHERE POAH.object_id = p_doc_id_tbl(i)
304 AND POAH.object_type_code = p_doc_type_tbl(i)
305 ;
306
307 l_progress := '300';
308
309 IF g_debug_stmt THEN
310 PO_DEBUG.debug_var(l_log_head,l_progress,'SQL%ROWCOUNT',SQL%ROWCOUNT);
311 PO_DEBUG.debug_end(l_log_head);
312 END IF;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 PO_MESSAGE_S.sql_error(g_pkg_name,l_proc_name,l_progress,SQLCODE,SQLERRM);
317 RAISE;
318
319 END insert_action_history;
320
321
322
323
324 -------------------------------------------------------------------------------
325 --Start of Comments
326 --Name: initialize_vars
327 --Pre-reqs:
328 -- None.
329 --Modifies:
330 -- None.
331 --Locks:
332 -- None.
333 --Function:
334 -- Retrieves the current FND user_id, login_id, and employee_id.
335 --Parameters:
336 --IN OUT:
337 --x_employee_id
338 -- If the input value is NULL, then this will be updated with
339 -- the employee_id corresponding to the current user.
340 --OUT:
341 --x_user_id
342 -- The current FND user.
343 --x_login_id
344 -- The current FND login ID.
345 --Testing:
346 --
347 --End of Comments
348 -------------------------------------------------------------------------------
349 PROCEDURE initialize_vars(
350 x_employee_id IN OUT NOCOPY NUMBER
351 , x_user_id OUT NOCOPY NUMBER
352 , x_login_id OUT NOCOPY NUMBER
353 )
354 IS
355
356 l_proc_name CONSTANT VARCHAR2(30) := 'INITIALIZE_VARS';
357 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
358 l_progress VARCHAR2(3) := '000';
359
360 BEGIN
361
362 IF g_debug_stmt THEN
363 PO_DEBUG.debug_begin(l_log_head);
364 PO_DEBUG.debug_var(l_log_head,l_progress,'x_employee_id',x_employee_id);
365 END IF;
366
367 l_progress := '010';
368
369 x_user_id := FND_GLOBAL.USER_ID;
370 x_login_id := FND_GLOBAL.LOGIN_ID;
371
372 l_progress := '110';
373
374 -- If an employee_id was not provided, determine an appropriate id.
375
376 IF (x_employee_id IS NULL) THEN
377
378 -- Get the employee id corresponding to the current user.
379
380 BEGIN
381
382 l_progress := '120';
383
384 SELECT
385 HR.employee_id
386 INTO
387 x_employee_id
388 FROM
389 FND_USER FND
390 , HR_EMPLOYEES_CURRENT_V HR
391 WHERE FND.user_id = x_user_id
392 AND FND.employee_id = HR.employee_id
393 ;
394
395 l_progress := '130';
396
397 EXCEPTION
398 WHEN NO_DATA_FOUND THEN
399 l_progress := '140';
400
401 IF g_debug_unexp THEN
402 PO_DEBUG.debug_unexp(l_log_head,l_progress,'Employee id not found.');
403 END IF;
404
405 -- If we can't find an employee_id,
406 -- then we'll keep the old employee_id,
407 -- so keep x_employee_id as NULL to enable an NVL().
408 x_employee_id := NULL;
409
410 l_progress := '150';
411
412 END;
413
414 END IF;
415 -- If an employee id was provided, we don't change it.
416
417 IF g_debug_stmt THEN
418 PO_DEBUG.debug_var(l_log_head,l_progress,'x_employee_id',x_employee_id);
419 PO_DEBUG.debug_var(l_log_head,l_progress,'x_user_id',x_user_id);
420 PO_DEBUG.debug_var(l_log_head,l_progress,'x_login_id',x_login_id);
421 PO_DEBUG.debug_end(l_log_head);
422 END IF;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 PO_MESSAGE_S.sql_error(g_pkg_name,l_proc_name,l_progress,SQLCODE,SQLERRM);
427
428 IF g_debug_stmt THEN
429 PO_DEBUG.debug_var(l_log_head,l_progress,'x_employee_id',x_employee_id);
430 PO_DEBUG.debug_var(l_log_head,l_progress,'x_user_id',x_user_id);
431 PO_DEBUG.debug_var(l_log_head,l_progress,'x_login_id',x_login_id);
432 END IF;
433
434 RAISE;
435
436 END initialize_vars;
437
438
439
440
441 END PO_ACTION_HISTORY_SV;