1 PACKAGE BODY fem_pl_pkg AS
2 /* $Header: fem_pl_pkb.plb 120.10.12000000.3 2007/08/10 21:08:51 gcheng ship $ */
3
4 -- ***********************
5 -- Package constants
6 -- ***********************
7 g_pkg_name CONSTANT VARCHAR2(30) := 'fem_pl_pkg';
8
9 g_ret_sts_success CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_success;
10 g_ret_sts_error CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_error;
11 g_ret_sts_unexp_error CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_unexp_error;
12
13 c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
14
15 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
16 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
17 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
18 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
19 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
20 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
21
22 E_UNEXP EXCEPTION;
23
24
25 -- ***************************************************************************
26 -- Private procedure signatures:
27 -- ***************************************************************************
28
29 PROCEDURE Perform_Standard_API_Steps (
30 p_current_api_version IN NUMBER,
31 p_caller_api_version IN NUMBER,
32 p_api_name IN VARCHAR2,
33 p_pkg_name IN VARCHAR2,
34 p_init_msg_list IN VARCHAR2);
35
36 PROCEDURE Get_Translated_Name (
37 p_vl_view_name IN VARCHAR2,
38 p_trans_col_name IN VARCHAR2,
39 p_id_col_name IN VARCHAR2,
40 p_id_value IN NUMBER,
41 x_trans_name OUT NOCOPY VARCHAR2);
42
43 PROCEDURE preview_exec_lock_exists (
44 p_object_id IN NUMBER,
45 p_exec_object_definition_id IN NUMBER,
46 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
47 x_exec_state OUT NOCOPY VARCHAR2,
48 x_prev_request_id OUT NOCOPY NUMBER,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 x_exec_lock_exists OUT NOCOPY VARCHAR2);
52
53 -- ***************************************************************************
54 -- Private procedure bodies:
55 -- ***************************************************************************
56
57 PROCEDURE Perform_Standard_API_Steps (
58 p_current_api_version IN NUMBER,
59 p_caller_api_version IN NUMBER,
60 p_api_name IN VARCHAR2,
61 p_pkg_name IN VARCHAR2,
62 p_init_msg_list IN VARCHAR2) IS
63 --
64 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
65 'fem.plsql.'||g_pkg_name||'.perform_standard_api_steps';
66 --
67 BEGIN
68 -- ==========================================================================
69 -- Performs the common steps that all standard API's need to perform:
70 -- 1. Check API version compatibility.
71 -- 2. Initialize FND_MSG_PUB message queue if necessary.
72 -- ==========================================================================
73
74 -- Check for call compatibility.
75 IF NOT FND_API.Compatible_API_Call (
76 p_current_version_number => p_current_api_version,
77 p_caller_version_number => p_caller_api_version,
78 p_api_name => p_api_name,
79 p_pkg_name => p_api_name) THEN
80 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
81 FEM_ENGINES_PKG.TECH_MESSAGE(
82 p_severity => FND_LOG.level_unexpected,
83 p_module => C_MODULE,
84 p_msg_text => 'INTERNAL ERROR: API Version ('||p_current_api_version||') not compatible with '
85 ||'passed in version ('||p_caller_api_version||')');
86 END IF;
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88 END IF;
89
90 -- Initialize FND message queue
91 IF p_init_msg_list = FND_API.G_TRUE then
92 FND_MSG_PUB.Initialize;
93 END IF;
94
95 END Perform_Standard_API_Steps;
96
97 -- ***************************************************************************
98
99 PROCEDURE Get_Translated_Name (
100 p_vl_view_name IN VARCHAR2,
101 p_trans_col_name IN VARCHAR2,
102 p_id_col_name IN VARCHAR2,
103 p_id_value IN NUMBER,
104 x_trans_name OUT NOCOPY VARCHAR2) IS
105 --
106 v_sql VARCHAR2(1000);
107 --
108 BEGIN
109 -- ==========================================================================
110 -- This procedure returns the user translated name given the ID value.
111 -- Any errors in this API will simply result in the return of the ID as the
112 -- name. This includes the case where the ID is not found.
113 -- ==========================================================================
114
115 v_sql := 'SELECT '||p_trans_col_name
116 ||' FROM '||p_vl_view_name
117 ||' WHERE '||p_id_col_name||' = :id';
118 fem_engines_pkg.tech_message (
119 p_severity => c_log_level_1,
120 p_module => 'fem.plsql.'||g_pkg_name||'.get_translated_name',
121 p_msg_text => 'v_sql: '||v_sql);
122
123 BEGIN
124 EXECUTE IMMEDIATE v_sql INTO x_trans_name USING p_id_value;
125 EXCEPTION
126 WHEN others THEN
127 x_trans_name := to_char(p_id_value);
128 END;
129
130 END Get_Translated_Name;
131
132 -- ***************************************************************************
133 -- Public procedures:
134 -- ***************************************************************************
135
136 -- ***************************************************************************
137
138 PROCEDURE obj_def_data_edit_lock_exists (
139 p_object_definition_id IN NUMBER,
140 x_data_edit_lock_exists OUT NOCOPY VARCHAR2
141 ) IS
142 -- ==========================================================================
143 -- Check if data lock exists for the object definition
144 -- If object is one which cannot be modified if it has been read whilst
145 -- generating results (i.e FEM_OBJECT_TYPES.DATA_EDIT_LOCK_FLAG='Y') then,
146 -- Check to see if object definition read by existing executions.
147 -- If object definition read, OR,
148 -- object definition is a seeded definition (i.e ID < 10000), then,
149 -- it means data lock exists.
150 -- else
151 -- data lock does not exist.
152 -- ==========================================================================
153 v_count NUMBER;
154 v_object_id NUMBER;
155 v_data_edit_lock_flag VARCHAR2(1);
156 l_api_name CONSTANT VARCHAR2(30) := 'obj_def_data_edit_lock_exists';
157
158 BEGIN
159
160 fem_engines_pkg.tech_message (
161 p_severity => c_log_level_1,
162 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
163 p_msg_text => 'Begin. P_OBJECT_DEFINITION_ID:'||
164 p_object_definition_id);
165
166 SELECT object_id
167 INTO v_object_id
168 FROM fem_object_definition_b
169 WHERE object_definition_id = p_object_definition_id;
170
171 SELECT t.data_edit_lock_flag
172 INTO v_data_edit_lock_flag
173 FROM fem_object_definition_b d,
174 fem_object_catalog_b o,
175 fem_object_types t
176 WHERE d.object_definition_id = p_object_definition_id
177 AND d.object_id = o.object_id
178 AND o.object_type_code = t.object_type_code;
179
180 SELECT COUNT(*)
181 INTO v_count
182 FROM fem_pl_object_defs
183 WHERE object_definition_id = p_object_definition_id;
184
185 IF (p_object_definition_id < 10000) THEN
186 x_data_edit_lock_exists := 'T';
187 ELSIF (v_data_edit_lock_flag = 'Y') AND (v_count > 0) THEN
188 x_data_edit_lock_exists := 'T';
189 ELSE
190 x_data_edit_lock_exists := 'F';
191 END IF;
192
193 fem_engines_pkg.tech_message (
194 p_severity => c_log_level_1,
195 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
196 p_msg_text => 'End. X_DATA_EDIT_LOCK_EXISTS:'||
197 x_data_edit_lock_exists);
198
199 END obj_def_data_edit_lock_exists;
200 -- ******************************************************************************
201
202 PROCEDURE effective_date_incl_rslt_data (
203 p_api_version IN NUMBER,
204 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
205 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2,
209 p_object_definition_id IN NUMBER,
210 p_new_effective_start_date IN DATE,
211 p_new_effective_end_date IN DATE,
212 x_date_incl_rslt_data OUT NOCOPY VARCHAR2) IS
213
214 -- ==========================================================================
215 -- Note: The code assumes p_new_effective_start_date<=p_new_effective_end_date
216 -- ==========================================================================
217 -- If new effective start date is greater than earliest effective date used
218 -- to select object definition for processing OR
219 -- new effective end date is less than latest effective date used
220 -- to select object definition for processing THEN
221 -- Effective date does not include result data range (Return false).
222 -- else
223 -- Effective date includes result data range (Return true).
224 -- end if.
225 -- ==========================================================================
226
227 C_API_NAME CONSTANT VARCHAR2(30) := 'effective_date_incl_rslt_data';
228 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
229 'fem.plsql.'||g_pkg_name||'.'||C_API_NAME;
230 C_API_VERSION CONSTANT NUMBER := 1.0;
231
232 v_rslts_start_date DATE;
233 v_rslts_end_date DATE;
234 v_obj_def_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
235
236 BEGIN
237
238 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
239 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
240 p_module => C_MODULE,
241 p_msg_text => 'Begin. P_OBJECT_DEFINITION_ID:'
242 ||p_object_definition_id||' P_NEW_EFFECTIVE_START_DATE:'
243 ||fnd_date.date_to_displaydate(p_new_effective_start_date)
244 ||' P_NEW_EFFECTIVE_END_DATE:'
245 ||fnd_date.date_to_displaydate(p_new_effective_end_date));
246 END IF;
247
248 Perform_Standard_API_Steps(
249 p_current_api_version => C_API_VERSION,
250 p_caller_api_version => p_api_version,
251 p_api_name => C_API_NAME,
252 p_pkg_name => G_PKG_NAME,
253 p_init_msg_list => p_init_msg_list);
254
255 SELECT min(r.effective_date), max(r.effective_date)
256 INTO v_rslts_start_date, v_rslts_end_date
257 FROM fem_pl_requests r, fem_pl_object_defs d
258 WHERE r.request_id = d.request_id
259 AND d.object_definition_id = p_object_definition_id;
260
261 IF p_new_effective_start_date > v_rslts_start_date OR
262 p_new_effective_end_date < v_rslts_end_date THEN
263
264 Get_Translated_Name (
265 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
266 p_trans_col_name => 'DISPLAY_NAME',
267 p_id_col_name => 'OBJECT_DEFINITION_ID',
268 p_id_value => p_object_definition_id,
269 x_trans_name => v_obj_def_name);
270 fem_engines_pkg.put_message(
271 p_app_name => 'FEM',
272 p_msg_name =>'FEM_PL_EFFDT_OUTSIDE_RSLTS_ERR',
273 p_token1 => 'OBJ_DEF_NAME',
274 p_value1 => v_obj_def_name,
275 p_trans1 => 'N',
276 p_token2 => 'RESULT_DATA_START_DATE',
277 p_value2 => fnd_date.date_to_displaydate(v_rslts_start_date),
278 p_trans2 => 'N',
279 p_token3 => 'RESULT_DATA_END_DATE',
280 p_value3 => fnd_date.date_to_displaydate(v_rslts_end_date),
281 p_trans3 => 'N');
282
283 x_date_incl_rslt_data := 'F';
284 ELSE
285 x_date_incl_rslt_data := 'T';
286 END IF;
287
288 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
289 fem_engines_pkg.tech_message(
290 p_severity => c_log_level_1,
291 p_module => C_MODULE,
292 p_msg_text => 'End. X_DATE_INCL_RSLT_DATA:'||
293 x_date_incl_rslt_data);
294 END IF;
295
296 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
297 p_count => x_msg_count,
298 p_data => x_msg_data);
299
300 -- Returning error if this API is putting an error message
301 -- on the stack so OAF code will detect the error and pull
302 -- that message off the stack.
303 IF x_date_incl_rslt_data = 'F' THEN
304 x_return_status := g_ret_sts_error;
305 ELSE
306 x_return_status := g_ret_sts_success;
307 END IF;
308
309 EXCEPTION
310 WHEN others THEN
311 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
312 FEM_ENGINES_PKG.TECH_MESSAGE(
313 p_severity => FND_LOG.level_statement,
314 p_module => C_MODULE,
315 p_msg_text => 'Unexpected error.');
316 FEM_ENGINES_PKG.TECH_MESSAGE(
317 p_severity => FND_LOG.level_statement,
318 p_module => C_MODULE,
319 p_msg_text => SQLERRM);
320 END IF;
321 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
322 p_count => x_msg_count,
323 p_data => x_msg_data);
324 x_date_incl_rslt_data := 'F';
325 x_return_status := g_ret_sts_unexp_error;
326
327 END effective_date_incl_rslt_data;
328
329
330 PROCEDURE effective_date_incl_rslt_data (
331 p_object_definition_id IN NUMBER,
332 p_new_effective_start_date IN DATE,
333 p_new_effective_end_date IN DATE,
334 x_msg_count OUT NOCOPY NUMBER,
335 x_msg_data OUT NOCOPY VARCHAR2,
336 x_date_incl_rslt_data OUT NOCOPY VARCHAR2) IS
337 -- ==========================================================================
338 -- API signature kept for backward compatibility. It simply calls the
339 -- effective_date_incl_rslt_data that follows the FND API standards.
340 -- ==========================================================================
341 v_return_status VARCHAR2(1);
342 BEGIN
343 effective_date_incl_rslt_data (
344 p_api_version => 1.0,
345 x_return_status => v_return_status,
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data,
348 p_object_definition_id => p_object_definition_id,
349 p_new_effective_start_date => p_new_effective_start_date,
350 p_new_effective_end_date => p_new_effective_end_date,
351 x_date_incl_rslt_data => x_date_incl_rslt_data);
352 END effective_date_incl_rslt_data;
353
354 -- ******************************************************************************
355 PROCEDURE obj_def_approval_lock_exists (p_object_definition_id IN NUMBER,
356 x_approval_edit_lock_exists OUT NOCOPY VARCHAR2) IS
357 -- ==========================================================================
358 -- Return true if object definition has been submitted for approval.
359 -- ==========================================================================
360 v_count NUMBER;
361 l_api_name CONSTANT VARCHAR2(30) := 'obj_def_approval_lock_exists';
362
363 BEGIN
364
365 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
366 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
367 p_msg_text => 'Begin. P_OBJECT_DEFINITION_ID:'||p_object_definition_id);
368
369 SELECT COUNT(*) INTO v_count
370 FROM fem_object_definition_b
371 WHERE object_definition_id = p_object_definition_id
372 AND approval_status_code IN ('SUBMIT_APPROVAL','SUBMIT_DELETE');
373
374 IF v_count = 1 THEN
378 END IF;
375 x_approval_edit_lock_exists := 'T';
376 ELSE
377 x_approval_edit_lock_exists := 'F';
379
380 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
381 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
382 p_msg_text => 'End. X_APPROVAL_EDIT_LOCK_EXISTS:'||
383 x_approval_edit_lock_exists);
384
385 END obj_def_approval_lock_exists;
386 -- ******************************************************************************
387 PROCEDURE get_object_def_edit_locks (p_object_definition_id IN NUMBER,
388 x_approval_edit_lock_exists OUT NOCOPY VARCHAR2,
389 x_data_edit_lock_exists OUT NOCOPY VARCHAR2) IS
390 -- ==========================================================================
391 -- Return x_approval_edit_lock_exists = 'T' if object definition has been
392 -- submitted for approval.
393 -- Return x_data_edit_lock_exists = 'T' if object definition is referenced by
394 -- result data.
395 -- ==========================================================================
396 v_count NUMBER;
397 l_api_name CONSTANT VARCHAR2(30) := 'get_object_def_edit_locks';
398
399 BEGIN
400
401 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
402 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
403 p_msg_text => 'Begin. P_OBJECT_DEFINITION_ID:'||p_object_definition_id);
404
405 obj_def_approval_lock_exists(p_object_definition_id, x_approval_edit_lock_exists);
406 obj_def_data_edit_lock_exists(p_object_definition_id, x_data_edit_lock_exists);
407
408 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
409 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
410 p_msg_text => 'End. X_APPROVAL_EDIT_LOCK_EXISTS:'||
411 x_approval_edit_lock_exists||' X_DATA_EDIT_LOCK_EXISTS:'||x_data_edit_lock_exists);
412
413 END get_object_def_edit_locks;
414
415 -- ***************************************************************************
416
417 PROCEDURE can_delete_object_def (
418 p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
420 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2,
424 p_object_definition_id IN NUMBER,
425 p_process_type IN NUMBER DEFAULT NULL,
426 p_calling_program IN VARCHAR2 DEFAULT NULL,
427 x_can_delete_obj_def OUT NOCOPY VARCHAR2) IS
428 -- ==========================================================================
429 -- Purpose
430 -- Checks to see if a rule (object) can be deleted or not.
431 -- Arguments
432 -- p_object_definition_id ID of the rule version being checked
433 -- p_process_type 1 indicates procedure called from a workflow
434 -- process. This parameter should only be
435 -- used by workflow code.
436 -- p_calling_program This parameter should only be used if this API is
437 -- being called by the "can_delete_object" API.
438 -- x_can_delete_obj_def Returns 'T' if the rule version can be deleted;
439 -- 'F' otherwise.
440 -- x_return_status Returns 'S' if the rule version can be deleted;
441 -- 'E' if rule version cannot be deleted and
442 -- this API has placed error message(s) on
443 -- the stack; 'U' for unexpected error.
444 -- Logic
445 -- A rule version can only be deleted if it is not is not seeded
446 -- (i.e ID < 10000), AND it is not Data Edit Locked, AND it is not the
447 -- only version defined for the rule, AND it is not Approval locked
448 -- (unless this is called from a workflow process and the
449 -- Approval Status is 'SUBMIT_DELETE').
450 -- ==========================================================================
451
452 C_API_NAME CONSTANT VARCHAR2(30) := 'can_delete_object_def';
453 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
454 'fem.plsql.'||g_pkg_name||'.'||C_API_NAME;
455 C_API_VERSION CONSTANT NUMBER := 1.0;
456
457 v_object_id NUMBER(9);
458 v_approval_status_code VARCHAR2(30);
459 v_num_of_definitions NUMBER;
460 v_data_edit_lock_exists VARCHAR2(1);
461 v_approval_edit_lock_exists VARCHAR2(1);
462 v_obj_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
463 v_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
464 v_obj_def_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
465 v_dep_obj_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
466 v_dep_obj_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
467
468 e_definition_is_seeded EXCEPTION;
469 e_data_edit_locked EXCEPTION;
470 e_only_definition EXCEPTION;
471 e_approval_edit_locked EXCEPTION;
472
473 BEGIN
474
475 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
476 fem_engines_pkg.tech_message (
477 p_severity => c_log_level_1,
478 p_module => C_MODULE,
479 p_msg_text => 'Begin. P_OBJECT_DEFINITION_ID:'||
480 p_object_definition_id||' P_PROCESS_TYPE:'||p_process_type);
481 END IF;
482
486 p_api_name => C_API_NAME,
483 Perform_Standard_API_Steps(
484 p_current_api_version => C_API_VERSION,
485 p_caller_api_version => p_api_version,
487 p_pkg_name => G_PKG_NAME,
488 p_init_msg_list => p_init_msg_list);
489
490 BEGIN
491 SELECT object_id, approval_status_code
492 INTO v_object_id, v_approval_status_code
493 FROM fem_object_definition_b
494 WHERE object_definition_id = p_object_definition_Id;
495 EXCEPTION
496 -- If no_data_found, then the version does not exist
497 -- and is safe to delete. Skip all other checks and exit procedure.
498 WHEN no_data_found THEN
499 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
500 fem_engines_pkg.tech_message(
501 p_severity => c_log_level_2,
502 p_module => C_MODULE,
503 p_msg_text => 'No data found for object definition. '||
504 'OBJECT_DEFINITION_ID:'||p_object_definition_id);
505 END IF;
506 x_can_delete_obj_def := 'T';
507 x_return_status := g_ret_sts_success;
508 RETURN;
509 END;
510
511 -- Cannot delete version if it is seeded
512 IF (p_object_definition_id < 10000) THEN
513 RAISE e_definition_is_seeded;
514 END IF;
515
516 get_object_def_edit_locks (
517 p_object_definition_id,
518 v_approval_edit_lock_exists,
519 v_data_edit_lock_exists);
520
521 -- Cannot delete if data edit lock exists
522 IF (v_data_edit_lock_exists = 'T') THEN
523 RAISE e_data_edit_locked;
524 END IF;
525
526 -- If this API is not called by 'can_delete_object',
527 -- then we need to check if this is the only version defined for
528 -- the rule. If it is, then the version cannot be deleted.
529 -- If this API is called by 'can_delete_object', then this check
530 -- is not necessary because deleting the object will delete the versions.
531 IF (nvl(p_calling_program,'X') <> 'can_delete_object') THEN
532 SELECT COUNT(*)
533 INTO v_num_of_definitions
534 FROM fem_object_definition_b
535 WHERE object_id = v_object_id
536 AND old_approved_copy_flag = 'N';
537
538 IF (v_num_of_definitions = 1) THEN
539 RAISE e_only_definition;
540 END IF;
541 END IF;
542
543 -- If the approval edit lock exists, the version cannot be
544 -- deleted unless the API is being called from a workflow process
545 -- and the approval status code is 'SUBMIT_DELETE'.
546 IF (v_approval_edit_lock_exists = 'T') THEN
547 IF ((p_process_type = 1) AND
548 (v_approval_status_code = 'SUBMIT_DELETE')) THEN
549 null;
550 ELSE
551 RAISE e_approval_edit_locked;
552 END IF;
553 END IF;
554
555 x_can_delete_obj_def := 'T';
556
557 FND_MSG_PUB.Count_And_Get
558 (p_encoded => p_encoded,
559 p_count => x_msg_count,
560 p_data => x_msg_data);
561
562 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
563 fem_engines_pkg.tech_message(
564 p_severity => c_log_level_1,
565 p_module => C_MODULE,
566 p_msg_text => 'End. X_CAN_DELETE_OBJ_DEF:'||x_can_delete_obj_def);
567 END IF;
568
569 x_return_status := g_ret_sts_success;
570
571 EXCEPTION
572 WHEN e_definition_is_seeded THEN
573 Get_Translated_Name (
574 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
575 p_trans_col_name => 'DISPLAY_NAME',
576 p_id_col_name => 'OBJECT_DEFINITION_ID',
577 p_id_value => p_object_definition_id,
578 x_trans_name => v_obj_def_name);
579 fem_engines_pkg.put_message(
580 p_app_name => 'FEM',
581 p_msg_name => 'FEM_PL_CANNOT_DEL_SEEDED_DEF',
582 p_token1 => 'OBJECT_DEF_NAME',
583 p_value1 => v_obj_def_name);
584 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
585 p_count => x_msg_count,
586 p_data => x_msg_data);
587 x_can_delete_obj_def := 'F';
588 -- Returning error so OAF code will detect error
589 -- and get the messages off the stack.
590 x_return_status := g_ret_sts_error;
591
592 WHEN e_data_edit_locked THEN
593 Get_Translated_Name (
594 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
595 p_trans_col_name => 'DISPLAY_NAME',
596 p_id_col_name => 'OBJECT_DEFINITION_ID',
597 p_id_value => p_object_definition_id,
598 x_trans_name => v_obj_def_name);
599 fem_engines_pkg.put_message(
600 p_app_name => 'FEM',
601 p_msg_name =>'FEM_PL_DATA_LOCKED_DEF_ERR',
602 p_token1 => 'OBJECT_DEF_NAME',
603 p_value1 => v_obj_def_name);
604 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
605 p_count => x_msg_count,
606 p_data => x_msg_data);
607 x_can_delete_obj_def := 'F';
608 x_return_status := g_ret_sts_error;
609
610 WHEN e_only_definition THEN
611 Get_Translated_Name (
612 p_vl_view_name => 'FEM_OBJECT_CATALOG_VL',
613 p_trans_col_name => 'OBJECT_NAME',
617 Get_Translated_Name (
614 p_id_col_name => 'OBJECT_ID',
615 p_id_value => v_object_id,
616 x_trans_name => v_obj_name);
618 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
619 p_trans_col_name => 'DISPLAY_NAME',
620 p_id_col_name => 'OBJECT_DEFINITION_ID',
621 p_id_value => p_object_definition_id,
622 x_trans_name => v_obj_def_name);
623 fem_engines_pkg.put_message(
624 p_app_name => 'FEM',
625 p_msg_name => 'FEM_PL_CANNOT_DEL_LAST_DEF',
626 p_token1 => 'OBJECT_DEF_NAME',
627 p_value1 => v_obj_def_name,
628 p_token2 => 'OBJECT_NAME',
629 p_value2 => v_obj_name);
630 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
631 p_count => x_msg_count,
632 p_data => x_msg_data);
633 x_can_delete_obj_def := 'F';
634 x_return_status := g_ret_sts_error;
635
636 WHEN e_approval_edit_locked THEN
637 Get_Translated_Name (
638 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
639 p_trans_col_name => 'DISPLAY_NAME',
640 p_id_col_name => 'OBJECT_DEFINITION_ID',
641 p_id_value => p_object_definition_id,
642 x_trans_name => v_obj_def_name);
643 fem_engines_pkg.put_message(
644 p_app_name => 'FEM',
645 p_msg_name =>'FEM_PL_SUBMITTED_DEF_ERR',
646 p_token1 => 'OBJECT_DEF_NAME',
647 p_value1 => v_obj_def_name);
648 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
649 p_count => x_msg_count,
650 p_data => x_msg_data);
651 x_can_delete_obj_def := 'F';
652 x_return_status := g_ret_sts_error;
653
654 WHEN others THEN
655 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
656 FEM_ENGINES_PKG.TECH_MESSAGE(
657 p_severity => FND_LOG.level_statement,
658 p_module => C_MODULE,
659 p_msg_text => 'Unexpected error.');
660 FEM_ENGINES_PKG.TECH_MESSAGE(
661 p_severity => FND_LOG.level_statement,
662 p_module => C_MODULE,
663 p_msg_text => SQLERRM);
664 END IF;
665 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
666 p_count => x_msg_count,
667 p_data => x_msg_data);
668 x_can_delete_obj_def := 'F';
669 x_return_status := g_ret_sts_unexp_error;
670
671 END can_delete_object_def;
672
673
674 PROCEDURE can_delete_object_def (
675 p_object_definition_id IN NUMBER,
676 x_can_delete_obj_def OUT NOCOPY VARCHAR2,
677 x_msg_count OUT NOCOPY NUMBER,
678 x_msg_data OUT NOCOPY VARCHAR2,
679 p_process_type IN NUMBER DEFAULT NULL,
680 p_calling_program IN VARCHAR2 DEFAULT NULL) IS
681 -- ==========================================================================
682 -- API signature kept for backward compatibility. It simply calls the
683 -- can_delete_object_def that follows the FND API standards.
684 -- ==========================================================================
685 v_return_status VARCHAR2(1);
686 BEGIN
687 can_delete_object_def (
688 p_api_version => 1.0,
689 x_return_status => v_return_status,
690 x_msg_count => x_msg_count,
691 x_msg_data => x_msg_data,
692 p_object_definition_id => p_object_definition_id,
693 p_process_type => p_process_type,
694 p_calling_program => p_calling_program,
695 x_can_delete_obj_def => x_can_delete_obj_def);
696 END can_delete_object_def;
697
698 -- **************************************************************************
699
700 PROCEDURE can_delete_object (
701 p_api_version IN NUMBER,
702 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
703 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
704 x_return_status OUT NOCOPY VARCHAR2,
705 x_msg_count OUT NOCOPY NUMBER,
706 x_msg_data OUT NOCOPY VARCHAR2,
707 p_object_id IN NUMBER,
708 p_process_type IN NUMBER DEFAULT NULL,
709 x_can_delete_obj OUT NOCOPY VARCHAR2) IS
710 -- ==========================================================================
711 -- Purpose
712 -- Checks to see if a rule (object) can be deleted or not.
713 -- Arguments
714 -- p_object_id ID of the rule being checked
715 -- p_process_type 1 indicates procedure called from a workflow process.
716 -- This parameter should only be used by workflow code.
717 -- x_can_delete_obj Returns 'T' if rule can be deleted; 'F' otherwise.
718 -- x_return_status Returns 'S' if rule can be deleted; 'E' if rule
719 -- cannot be deleted and this API has placed error
720 -- message(s) on the stack; 'U' for unexpected error.
721 -- Logic
722 -- A rule can only be deleted if all of its versions can be
723 -- deleted AND the rule is not seeded (i.e ID < 10000),
724 -- AND the rule is not referenced by another rule.
725 -- ==========================================================================
726
727 CURSOR c1 IS
728 SELECT object_definition_id
732
729 FROM fem_object_definition_b
730 WHERE object_id = p_object_id
731 AND old_approved_copy_flag = 'N';
733 CURSOR c_object_dependencies (p_object_id NUMBER) IS
734 SELECT object_definition_id
735 FROM fem_object_dependencies
736 WHERE required_object_id = p_object_id;
737
738 C_API_NAME CONSTANT VARCHAR2(30) := 'can_delete_object';
739 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
740 'fem.plsql.'||g_pkg_name||'.'||C_API_NAME;
741 C_API_VERSION CONSTANT NUMBER := 1.0;
742
743 v_can_delete_object_def VARCHAR2(1);
744 v_obj_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
745 v_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
746 v_obj_def_name FEM_OBJECT_DEFINITION_TL.display_name%TYPE;
747 v_dep_obj_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
748 v_dep_obj_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
749 v_return_status VARCHAR2(1);
750
751 e_cannot_del_version EXCEPTION;
752 e_object_is_seeded EXCEPTION;
753 e_dependencies_exist EXCEPTION;
754
755 BEGIN
756
757 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
758 fem_engines_pkg.tech_message(
759 p_severity => c_log_level_1,
760 p_module => C_MODULE,
761 p_msg_text => 'Begin. P_OBJECT_ID:'||
762 p_object_id||' P_PROCESS_TYPE:'||p_process_type);
763 END IF;
764
765 Perform_Standard_API_Steps(
766 p_current_api_version => C_API_VERSION,
767 p_caller_api_version => p_api_version,
768 p_api_name => C_API_NAME,
769 p_pkg_name => G_PKG_NAME,
770 p_init_msg_list => p_init_msg_list);
771
772 -- Check to see if the rule can be removed.
773 IF (p_object_id < 10000) THEN
774 RAISE e_object_is_seeded;
775 END IF;
776
777 -- Check to make sure the versions themselves
778 -- can be deleted. This check is done before the
779 -- dependencies check to make sure
780 -- the error messaging is consistent regardless if
781 -- the UI calls can_delete_object_def or this API when
782 -- the user wants to delete a version or rule.
783 -- See bug 4600065 for details of this issue.
784 FOR adef IN c1 LOOP
785 can_delete_object_def(
786 p_api_version => 1.0,
787 p_init_msg_list => FND_API.G_FALSE,
788 p_encoded => p_encoded,
789 x_return_status => v_return_status,
790 x_msg_count => x_msg_count,
791 x_msg_data => x_msg_data,
792 p_object_definition_id => adef.object_definition_id,
793 p_process_type => p_process_type,
794 p_calling_program => 'can_delete_object',
795 x_can_delete_obj_def => v_can_delete_object_def);
796
797 IF (v_return_status = g_ret_sts_unexp_error) THEN
798 RAISE E_UNEXP;
799 END IF;
800
801 IF (v_can_delete_object_def = 'F') THEN
802 v_obj_def_id := adef.object_definition_id;
803 RAISE e_cannot_del_version;
804 END IF;
805 END LOOP;
806
807 -- Check to see if rule is referenced by any other rules.
808 OPEN c_object_dependencies(p_object_id);
809 FETCH c_object_dependencies INTO v_obj_def_id;
810 CLOSE c_object_dependencies;
811
812 IF (v_obj_def_id IS NOT NULL) THEN
813 RAISE e_dependencies_exist;
814 END IF;
815
816 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
817 p_count => x_msg_count,
818 p_data => x_msg_data);
819
820 x_can_delete_obj := 'T';
821 x_return_status := g_ret_sts_success;
822
823 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
824 fem_engines_pkg.tech_message(
825 p_severity => c_log_level_1,
826 p_module => C_MODULE,
827 p_msg_text => 'End. X_CAN_DELETE_OBJ:'||x_can_delete_obj);
828 END IF;
829
830 EXCEPTION
831 WHEN e_cannot_del_version THEN
832 Get_Translated_Name (
833 p_vl_view_name => 'FEM_OBJECT_CATALOG_VL',
834 p_trans_col_name => 'OBJECT_NAME',
835 p_id_col_name => 'OBJECT_ID',
836 p_id_value => p_object_id,
837 x_trans_name => v_obj_name);
838 Get_Translated_Name (
839 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
840 p_trans_col_name => 'DISPLAY_NAME',
841 p_id_col_name => 'OBJECT_DEFINITION_ID',
842 p_id_value => v_obj_def_id,
843 x_trans_name => v_obj_def_name);
844 fem_engines_pkg.put_message(
845 p_app_name => 'FEM',
846 p_msg_name => 'FEM_PL_CANNOT_DELETE_DEF_ERR',
847 p_token1 => 'OBJECT_NAME',
848 p_value1 => v_obj_name,
849 p_token2 => 'OBJECT_DEF_NAME',
850 p_value2 => v_obj_def_name);
851 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
852 p_count => x_msg_count,
853 p_data => x_msg_data);
854 x_can_delete_obj := 'F';
855 -- Returning error so OAF code will detect error
856 -- and get the messages off the stack.
857 x_return_status := g_ret_sts_error;
861 p_vl_view_name => 'FEM_OBJECT_CATALOG_VL',
858
859 WHEN e_object_is_seeded THEN
860 Get_Translated_Name (
862 p_trans_col_name => 'OBJECT_NAME',
863 p_id_col_name => 'OBJECT_ID',
864 p_id_value => p_object_id,
865 x_trans_name => v_obj_name);
866 fem_engines_pkg.put_message(
867 p_app_name => 'FEM',
868 p_msg_name => 'FEM_PL_CANNOT_DEL_SEEDED_OBJ',
869 p_token1 => 'OBJECT_NAME',
870 p_value1 => v_obj_name,
871 p_trans1 => 'N');
872 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
873 p_count => x_msg_count,
874 p_data => x_msg_data);
875 x_can_delete_obj := 'F';
876 x_return_status := g_ret_sts_error;
877
878 WHEN e_dependencies_exist THEN
879 Get_Translated_Name (
880 p_vl_view_name => 'FEM_OBJECT_CATALOG_VL',
881 p_trans_col_name => 'OBJECT_NAME',
882 p_id_col_name => 'OBJECT_ID',
883 p_id_value => p_object_id,
884 x_trans_name => v_obj_name);
885 Get_Translated_Name (
886 p_vl_view_name => 'FEM_OBJECT_DEFINITION_VL',
887 p_trans_col_name => 'DISPLAY_NAME',
888 p_id_col_name => 'OBJECT_DEFINITION_ID',
889 p_id_value => v_obj_def_id,
890 x_trans_name => v_obj_def_name);
891
892 SELECT c.object_id, c.object_name
893 INTO v_dep_obj_id, v_dep_obj_name
894 FROM fem_object_catalog_vl c, fem_object_definition_b d
895 WHERE c.object_id = d.object_id
896 AND d.object_definition_id = v_obj_def_id;
897
898 fem_engines_pkg.put_message(
899 p_app_name => 'FEM',
900 p_msg_name => 'FEM_PL_REFERENCED_OBJ_ERR',
901 p_token1 => 'OBJECT_NAME',
902 p_value1 => v_obj_name,
903 p_token2 => 'DEP_OBJECT_NAME',
904 p_value2 => v_dep_obj_name,
905 p_token3 => 'DEP_OBJECT_DEF_NAME',
906 p_value3 => v_obj_def_name);
907 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
908 p_count => x_msg_count,
909 p_data => x_msg_data);
910 x_can_delete_obj := 'F';
911 x_return_status := g_ret_sts_error;
912
913 WHEN others THEN
914 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
915 FEM_ENGINES_PKG.TECH_MESSAGE(
916 p_severity => FND_LOG.level_statement,
917 p_module => C_MODULE,
918 p_msg_text => 'Unexpected error.');
919 FEM_ENGINES_PKG.TECH_MESSAGE(
920 p_severity => FND_LOG.level_statement,
921 p_module => C_MODULE,
922 p_msg_text => SQLERRM);
923 END IF;
924 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
925 p_count => x_msg_count,
926 p_data => x_msg_data);
927 x_can_delete_obj := 'd';
928 x_return_status := g_ret_sts_unexp_error;
929
930 END can_delete_object;
931
932
933 PROCEDURE can_delete_object (
934 p_object_id IN NUMBER,
935 p_process_type IN NUMBER DEFAULT NULL,
936 x_can_delete_obj OUT NOCOPY VARCHAR2,
937 x_msg_count OUT NOCOPY NUMBER,
938 x_msg_data OUT NOCOPY VARCHAR2) IS
939 -- ==========================================================================
940 -- API signature kept for backward compatibility. It simply calls the
941 -- can_delete_object that follows the FND API standards.
942 -- ==========================================================================
943 v_return_status VARCHAR2(1);
944 BEGIN
945 can_delete_object (
946 p_api_version => 1.0,
947 x_return_status => v_return_status,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 p_object_id => p_object_id,
951 p_process_type => p_process_type,
952 x_can_delete_obj => x_can_delete_obj);
953 END can_delete_object;
954
955 -- ******************************************************************************
956 PROCEDURE obj_execution_lock_exists (p_object_id IN NUMBER,
957 p_exec_object_definition_id IN NUMBER,
958 p_ledger_id IN NUMBER DEFAULT NULL,
959 p_cal_period_id IN NUMBER DEFAULT NULL,
960 p_output_dataset_code IN NUMBER DEFAULT NULL,
961 p_source_system_code IN NUMBER DEFAULT NULL,
962 p_exec_mode_code IN VARCHAR2 DEFAULT NULL,
963 p_dimension_id IN NUMBER DEFAULT NULL,
964 p_table_name IN VARCHAR2 DEFAULT NULL,
965 p_hierarchy_name IN VARCHAR2 DEFAULT NULL,
966 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
967 x_exec_state OUT NOCOPY VARCHAR2,
968 x_prev_request_id OUT NOCOPY NUMBER,
969 x_msg_count OUT NOCOPY NUMBER,
973 -- Returns true if an object execution lock exists.
970 x_msg_data OUT NOCOPY VARCHAR2,
971 x_exec_lock_exists OUT NOCOPY VARCHAR2) IS
972 -- ==========================================================================
974 -- Calls specialized procedures for specific object types. If object is
975 -- execution locked and does not have a specified procedure, uses
976 -- the procedure for mapping rules.
977 -- This procedure does not detect execution state for non execution locked
978 -- objects.
979 -- ==========================================================================
980 -- BEGIN obj_execution_lock_exists
981 -- IF an approval edit lock exists for the object definition to be executed THEN
982 -- Return T;
983 -- Fem_engines_pkg.put_message (FEM_PL_SUBMITTED_DEF_ERR).
984 -- ELSIF object_type IN ('XGL_INTEGRATION', 'OGL_INTG_BAL_RULE',
985 -- 'SOURCE_DATA_LOADER', EGL_INTG_BAL_RULE') THEN
986 -- Call specialized procedure (FEM_PL_INCR_PKG.Obj_Exec_Lock_Exists)
987 -- ELSIF object_type IN ('DIM_MEMBER_LOADER',
988 -- 'OGL_INTG_DIM_RULE',
989 -- 'OGL_INTG_CAL_RULE',
990 -- 'EGL_INTG_DIM_RULE',
991 -- 'EGL_INTG_CAL_RULE',
992 -- 'UNDO',
993 -- 'REFRESH_ENGINE'),
994 -- 'DIM_MEMBER_MIGRATION',
995 -- 'HIERARCHY_MIGRATION') THEN
996 -- Call specialized procedure (fem_pl_pkg.dim_mbr_ldr_Exec_Lock_Exists)
997 -- ELSIF object_type = 'DATAX_LOADER' THEN
998 -- Call specialized procedure (fem_pl_pkg.datax_ldr_Exec_Lock_Exists)
999 -- ELSIF object_type in ('HIERARCHY_LOADER', 'OGL_INTG_HIER_RULE',
1000 -- 'EGL_INTG_HIER_RULE') THEN
1001 -- Call specialized procedure (fem_pl_pkg.hier_ldr_Exec_Lock_Exists)
1002 -- ELSIF object_type in ('RCM_PROCESS_RULE','TP_PROCESS_RULE') THEN
1003 -- Call specialized procedure (fem_pl_pkg.rcm_proc_Exec_Lock_Exists)
1004 -- ELSIF object_type = 'MAPPING_PREVIEW' THEN
1005 -- Call specialized procedure (fem_pl_pkg.Preview_Exec_Lock_Exists)
1006 -- ELSIF an executable locked object (i.e FEM_OBJECT_TYPES.EXECUTABLE_LOCK = Y) THEN
1007 -- Call specialized procedure (fem_pl_pkg.mapping_Exec_Lock_Exists)
1008 -- ELSIF not an executable locked object
1009 -- (i.e FEM_OBJECT_TYPES.EXECUTABLE_LOCK = N) THEN
1010 -- Set execution state (IF object execution is
1011 -- already registered and is running THEN x_exec_state='RESTART' ELSE
1012 -- x_exec_state = 'NORMAL).
1013 -- Return F.
1014 -- ELSE
1015 -- Return T and put message ('FEM_PL_RESULTS_EXIST_ERR');
1016 -- End if;
1017 -- END obj_execution_lock_exists;
1018 -- ==========================================================================
1019
1020 v_approval_edit_lock_exists VARCHAR2(1);
1021 v_executable_lock_flag VARCHAR2(1);
1022 v_request_id NUMBER;
1023 v_object_type_code VARCHAR2(30);
1024 v_restart VARCHAR2(1);
1025 v_normal_run VARCHAR2(1);
1026 v_rerun VARCHAR2(1);
1027 l_api_name CONSTANT VARCHAR2(30) := 'obj_execution_lock_exists';
1028
1029 BEGIN
1030
1031 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1032 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1033 p_msg_text => 'Begin. P_OBJECT_ID:'||p_object_id||
1034 ' P_LEDGER_ID:'||p_ledger_id||' P_CAL_PERIOD_ID:'||p_cal_period_id||
1035 ' P_OUTPUT_DATASET_CODE:'||p_output_dataset_code||
1036 ' P_DIMENSION_ID:'||p_dimension_id||' P_TABLE_NAME:'||p_table_name||
1037 ' P_HIERARCHY_NAME:'||p_hierarchy_name||' P_EXEC_MODE_CODE:'||p_exec_mode_code||
1038 ' P_CALLING_CONTEXT:'||p_calling_context);
1039
1040 x_msg_count := 0;
1041 x_exec_state := NULL;
1042 x_prev_request_id := NULL;
1043
1044 -- Check for approval edit lock
1045 obj_def_approval_lock_exists(p_exec_object_definition_id, v_approval_edit_lock_exists);
1046
1047 -- Retrieve object type code and check to see if the object
1048 -- type is executable locked.
1049 SELECT t.executable_lock_flag, o.object_type_code
1050 INTO v_executable_lock_flag, v_object_type_code
1051 FROM fem_object_types t, fem_object_catalog_b o
1052 WHERE o.object_id = p_object_id
1053 AND o.object_type_code = t.object_type_code;
1054
1055 IF v_approval_edit_lock_exists = 'T' THEN
1056 x_exec_lock_exists := 'T';
1057 fem_engines_pkg.put_message(p_app_name =>'FEM',p_msg_name =>'FEM_PL_SUBMITTED_DEF_ERR',
1058 p_token1 => 'OBJECT_DEFINITION_ID', p_value1 => p_exec_object_definition_id, p_trans1 => 'N');
1059
1060 ELSIF v_object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
1061 'SOURCE_DATA_LOADER','EGL_INTG_BAL_RULE') THEN
1062
1063 -- call specialized procedure.
1064 fem_pl_incr_pkg.exec_lock_exists(
1065 p_calling_context => p_calling_context,
1066 p_object_id => p_object_id,
1067 p_obj_def_id => p_exec_object_definition_id,
1068 p_cal_period_id => p_cal_period_id,
1069 p_ledger_id => p_ledger_id,
1070 p_dataset_code => p_output_dataset_code,
1071 p_source_system_code => p_source_system_code,
1072 p_table_name => p_table_name,
1073 p_exec_mode => p_exec_mode_code,
1074 x_exec_lock_exists => x_exec_lock_exists,
1075 x_exec_state => x_exec_state,
1076 x_prev_request_id => x_prev_request_id,
1077 x_num_msg => x_msg_count);
1078
1079 ELSIF v_object_type_code IN ('DIM_MEMBER_LOADER',
1083 'EGL_INTG_CAL_RULE',
1080 'OGL_INTG_DIM_RULE',
1081 'OGL_INTG_CAL_RULE',
1082 'EGL_INTG_DIM_RULE',
1084 'UNDO',
1085 'REFRESH_ENGINE',
1086 'DIM_MEMBER_MIGRATION',
1087 'HIERARCHY_MIGRATION') THEN
1088
1089 -- call specialized procedure. (This procedure checks to make
1090 -- sure that the same OBJECT_ID is not executed while an execution
1091 -- of that same OBJECT_ID is in progress. It does not check any other
1092 -- parameters.)
1093 dim_mbr_ldr_exec_lock_exists(p_object_id => p_object_id,
1094 p_exec_object_definition_id => p_exec_object_definition_id,
1095 p_calling_context => p_calling_context,
1096 x_exec_state => x_exec_state,
1097 x_msg_count => x_msg_count,
1098 x_msg_data => x_msg_data,
1099 x_exec_lock_exists => x_exec_lock_exists,
1100 x_prev_request_id => x_prev_request_id);
1101
1102 ELSIF v_object_type_code = 'DATAX_LOADER' THEN
1103
1104 -- call specialized procedure.
1105 datax_ldr_exec_lock_exists(p_object_id => p_object_id,
1106 p_exec_object_definition_id => p_exec_object_definition_id,
1107 p_ledger_id => p_ledger_id,
1108 p_cal_period_id => p_cal_period_id,
1109 p_output_dataset_code => p_output_dataset_code,
1110 p_source_system_code => p_source_system_code,
1111 p_table_name => p_table_name,
1112 p_calling_context => p_calling_context,
1113 x_exec_state => x_exec_state,
1114 x_msg_count => x_msg_count,
1115 x_msg_data => x_msg_data,
1116 x_exec_lock_exists => x_exec_lock_exists,
1117 x_prev_request_id => x_prev_request_id);
1118
1119 ELSIF v_object_type_code in ('HIERARCHY_LOADER','OGL_INTG_HIER_RULE',
1120 'EGL_INTG_HIER_RULE') THEN
1121
1122 -- call specialized procedure.
1123 hier_ldr_exec_lock_exists(p_object_id => p_object_id,
1124 p_exec_object_definition_id => p_exec_object_definition_id,
1125 p_hierarchy_name => p_hierarchy_name,
1126 p_calling_context => p_calling_context,
1127 x_exec_state => x_exec_state,
1128 x_msg_count => x_msg_count,
1129 x_msg_data => x_msg_data,
1130 x_exec_lock_exists => x_exec_lock_exists,
1131 x_prev_request_id => x_prev_request_id);
1132
1133 ELSIF v_object_type_code in ('RCM_PROCESS_RULE','TP_PROCESS_RULE') THEN
1134
1135 -- call specialized procedure.
1136 rcm_proc_exec_lock_exists(p_object_id => p_object_id,
1137 p_exec_object_definition_id => p_exec_object_definition_id,
1138 p_ledger_id => p_ledger_id,
1139 p_cal_period_id => p_cal_period_id,
1140 p_output_dataset_code => p_output_dataset_code,
1141 p_calling_context => p_calling_context,
1142 x_exec_state => x_exec_state,
1143 x_prev_request_id => x_prev_request_id,
1144 x_msg_count => x_msg_count,
1145 x_msg_data => x_msg_data,
1146 x_exec_lock_exists => x_exec_lock_exists);
1147
1148 ELSIF (v_object_type_code = 'MAPPING_PREVIEW') THEN
1149
1150 -- call specialized procedure.
1151 preview_exec_lock_exists(
1152 p_object_id => p_object_id,
1153 p_exec_object_definition_id => p_exec_object_definition_id,
1154 p_calling_context => p_calling_context,
1155 x_exec_state => x_exec_state,
1156 x_prev_request_id => x_prev_request_id,
1157 x_msg_count => x_msg_count,
1158 x_msg_data => x_msg_data,
1159 x_exec_lock_exists => x_exec_lock_exists);
1160
1161 ELSIF v_executable_lock_flag = 'Y' THEN
1162
1163 -- call specialized procedure for mapping rules. This is for mapping
1164 -- rules, and is the default for all other rules that are execution
1165 -- locked, but do not have specialized procedures.
1166
1167 mapping_exec_lock_exists(p_object_id => p_object_id,
1168 p_exec_object_definition_id => p_exec_object_definition_id,
1169 p_ledger_id => p_ledger_id,
1170 p_cal_period_id => p_cal_period_id,
1171 p_output_dataset_code => p_output_dataset_code,
1172 p_calling_context => p_calling_context,
1173 x_exec_state => x_exec_state,
1174 x_prev_request_id => x_prev_request_id,
1175 x_msg_count => x_msg_count,
1176 x_msg_data => x_msg_data,
1177 x_exec_lock_exists => x_exec_lock_exists);
1178
1179 ELSE
1180 -- This is for objects which are not execution locked i.e.
1181 -- v_executable_lock_flag = 'N'
1182
1183 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1184
1185 -- If this object execution is already registered and is currently running,
1186 -- then it is a restart, else it is a normal run.
1187 SELECT DECODE(COUNT(*),1,'RESTART','NORMAL') INTO x_exec_state
1188 FROM fem_pl_object_executions o, fem_pl_requests r
1189 WHERE r.request_id = v_request_id
1190 AND r.request_id = o.request_id
1191 AND o.object_id = p_object_id
1192 AND o.exec_status_code = 'RUNNING';
1193
1194 x_exec_lock_exists := 'F';
1195
1196 END IF;
1197
1198 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1199 p_module => 'fem.plsql.'||g_pkg_name||'.obj_execution_lock_exists',
1203 -- Standard call to get message count and if count is 1, get message info.
1200 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists||
1201 '; Execution state:'||x_exec_state||' X_PREV_REQUEST_ID:'||x_prev_request_id);
1202
1204 FND_MSG_PUB.Count_And_Get
1205 (p_count => x_msg_count,
1206 p_data => x_msg_data);
1207
1208 END obj_execution_lock_exists;
1209 -- ******************************************************************************
1210 PROCEDURE register_object_execution (p_api_version IN NUMBER,
1211 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1212 p_request_id IN NUMBER,
1213 p_object_id IN NUMBER,
1214 p_exec_object_definition_id IN NUMBER,
1215 p_user_id IN NUMBER,
1216 p_last_update_login IN NUMBER,
1217 p_exec_mode_code IN VARCHAR2 DEFAULT NULL,
1218 x_exec_state OUT NOCOPY VARCHAR2,
1219 x_prev_request_id OUT NOCOPY NUMBER,
1220 x_msg_count OUT NOCOPY NUMBER,
1221 x_msg_data OUT NOCOPY VARCHAR2,
1222 x_return_status OUT NOCOPY VARCHAR2) IS
1223 -- ==========================================================================
1224 -- NOTE: The p_commit flag is currently ignored to ensure that the
1225 -- exclusive lock on FEM_PL_OBJECT_EXECUTIONS is always released at
1226 -- the end of the procedure. Bug# 3981986
1227 -- ==========================================================================
1228 -- x_return_status returns: S=successful, E=error, U=unexpected error.
1229 -- ==========================================================================
1230 --BEGIN register_object_execution
1231 -- Acquire table lock (Wait until lock available)
1232 -- IF execution lock exists THEN
1233 -- Rollback to release exclusive table lock.
1234 -- post message CANNOT_EXECUTE_LOCKED_OBJ;
1235 -- set x_return_status = E;
1236 -- ELSE
1237 -- Insert object execution data in FEM_PL_OBJECT_EXECUTIONS.
1238 -- IF object is a GL incremental load, RCM_PROCESS_RULE, or TP_PROCESS_RULE THEN
1239 -- Set FEM_PL_OBJECT_EXECUTIONS.DISPLAY_FLAG = N for previous executions.
1240 -- END IF;
1241 -- Register executed object definition in FEM_PL_OBJECT_DEFS.
1242 -- IF cannot register object definition, rollback and return an error status ELSE
1243 -- Issue a commit to release exclusive table lock. END IF;
1244 -- END IF;
1245 -- EXCEPTION
1246 -- Rollback to release exclusive table lock.
1247 -- WHEN DUP_VAL_ON_INDEX THEN (This happens in a restart)
1248 -- x_return_status := 1;
1249 --END register_object_execution
1250 -- ==========================================================================
1251 v_obj_execution_lock_exists VARCHAR2(1);
1252 v_ledger_id NUMBER;
1253 v_cal_period_id NUMBER;
1254 v_output_dataset_code NUMBER;
1255 v_source_system_code NUMBER;
1256 v_table_name VARCHAR2(30);
1257 v_exec_mode_code VARCHAR2(30);
1258 v_dimension_id NUMBER;
1259 v_hierarchy_name VARCHAR2(150);
1260 l_api_name CONSTANT VARCHAR2(30) := 'register_object_execution';
1261 l_api_version CONSTANT NUMBER := 1.0;
1262 v_undo_flag VARCHAR2(1);
1263 v_object_type_code FEM_OBJECT_TYPES.object_type_code%TYPE;
1264 v_display_flag VARCHAR2(1);
1265
1266 -- This cursor retrieves all previous executions of an
1267 -- object that has the same parameters as the current
1268 -- execution.
1269 CURSOR c1 IS
1270 SELECT r.request_id
1271 FROM fem_pl_requests r, fem_pl_object_executions pl
1272 WHERE pl.object_id = p_object_id
1273 AND pl.request_id = r.request_id
1274 AND r.ledger_id = v_ledger_id
1275 AND r.cal_period_Id = v_cal_period_id
1276 AND r.output_dataset_code = v_output_dataset_code
1277 AND r.source_system_code = v_source_system_code
1278 AND r.dimension_id = v_dimension_id
1279 AND r.table_name = v_table_name
1280 AND r.hierarchy_name = v_hierarchy_name;
1281
1282 BEGIN
1283
1284 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1285 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1286 p_msg_text => 'Begin. P_USER_ID:'||p_user_id||
1287 ' P_REQUEST_ID:'||p_request_id||' P_OBJECT_ID:'||p_object_id||
1288 ' P_EXEC_OBJECT_DEFINITION_ID:'||p_exec_object_definition_id);
1289
1290 -- Standard Start of API savepoint
1291 SAVEPOINT register_object_execution_pub;
1292
1293 -- Standard call to check for call compatibility.
1294 IF NOT FND_API.Compatible_API_Call (l_api_version,
1295 p_api_version,
1296 l_api_name,
1297 g_pkg_name)
1298 THEN
1299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1300 END IF;
1301
1302 -- Initialize API return status to success
1303 x_return_status := G_RET_STS_SUCCESS;
1304
1305 -- Retrieve request's parameters
1306 SELECT ledger_id, cal_period_id, output_dataset_code, source_system_code,
1307 dimension_id, table_Name, exec_mode_code, hierarchy_name INTO
1308 v_ledger_id, v_cal_period_id, v_output_dataset_code,
1309 v_source_system_code, v_dimension_id, v_table_Name,
1310 v_exec_mode_code, v_hierarchy_name
1311 FROM fem_pl_requests
1312 WHERE request_id = p_request_id;
1313
1314 -- Retrieve undo_flag
1315 SELECT undo_flag INTO v_undo_flag
1316 FROM fem_object_catalog_b o, fem_object_types t
1317 WHERE o.object_id = p_object_id
1318 AND o.object_type_code = t.object_type_code;
1319
1320 -- Acquire table lock
1321 LOCK TABLE fem_pl_object_executions IN EXCLUSIVE MODE;
1322
1323 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1324 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1325 p_msg_text => 'Table FEM_PL_OBJECT_EXECUTIONS locked in exclusive mode.');
1326
1327 obj_execution_lock_exists (
1328 p_object_id => p_object_id,
1329 p_exec_object_definition_id => p_exec_object_definition_id,
1330 p_ledger_id => v_ledger_id,
1331 p_cal_period_id => v_cal_period_id,
1332 p_output_dataset_code => v_output_dataset_code,
1333 p_source_system_code => v_source_system_code,
1334 p_dimension_id => v_dimension_id,
1335 p_table_name => v_table_name,
1336 p_hierarchy_name => v_hierarchy_name,
1337 p_exec_mode_code => v_exec_mode_code,
1338 x_exec_state => x_exec_state,
1339 x_prev_request_id => x_prev_request_id,
1340 x_msg_count => x_msg_count,
1341 x_msg_data => x_msg_data,
1342 x_exec_lock_exists => v_obj_execution_lock_exists);
1343
1344 IF v_obj_execution_lock_exists = 'T' THEN
1345
1346 ROLLBACK TO register_object_execution_pub;
1347
1348 fem_engines_pkg.put_message(p_app_name =>'FEM',
1349 p_msg_name =>'FEM_PL_OBJ_EXECLOCK_EXISTS_ERR');
1350
1351 fem_engines_pkg.tech_message(p_severity => c_log_level_4,
1352 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1353 p_msg_name =>'FEM_PL_OBJ_EXECLOCK_EXISTS_ERR');
1354
1355 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1356 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1357 p_msg_text => 'Released exclusive lock on FEM_PL_OBJECT_EXECUTIONS .');
1358
1359 x_return_status := g_ret_sts_error;
1360
1361 ELSE
1362 -- Determine object type code for the object
1363 SELECT object_type_code
1364 INTO v_object_type_code
1365 FROM fem_object_catalog_b
1366 WHERE object_id = p_object_id;
1367
1368 -- If no locks exist register object execution.
1369
1370 -- Set the display flag to 'N' for all previous
1371 -- rules of the following types OGL_INTG_BAL_RULE, XGL_INTEGRATION,
1372 -- RCM_PROCESS_RULE, TP_PROCESS_RULE, EGL_INTG_BAL_RULE
1373 -- that have the same OBJECT_ID, LEDGER_ID, CAL_PERIOD_ID and
1374 -- OUTPUT_DATASET_CODE as the current object execution.
1375 IF v_object_type_code IN ('RCM_PROCESS_RULE','OGL_INTG_BAL_RULE',
1376 'TP_PROCESS_RULE','EGL_INTG_BAL_RULE') THEN
1377 UPDATE fem_pl_object_executions SET display_flag = 'N'
1378 WHERE object_id = p_object_id
1379 AND display_flag = 'Y'
1380 AND request_id IN
1381 (SELECT r.request_id FROM fem_pl_requests r, fem_pl_requests a
1382 WHERE a.request_id = p_request_id
1383 AND r.request_id <> p_request_id
1384 AND a.cal_period_id = r.cal_period_id
1385 AND a.ledger_id = r.ledger_id
1386 AND a.output_dataset_code = r.output_dataset_code);
1387 -- Also set the display flag to 'N' for all previous executions
1388 -- of the same rule so only the latest execution is displayed
1389 -- in the Undo UI.
1390 ELSIF v_object_type_code IN ('OGL_INTG_CAL_RULE','OGL_INTG_DIM_RULE',
1391 'OGL_INTG_HIER_RULE',
1392 'EGL_INTG_CAL_RULE','EGL_INTG_DIM_RULE',
1393 'EGL_INTG_HIER_RULE') THEN
1394 UPDATE fem_pl_object_executions SET display_flag = 'N'
1395 WHERE object_id = p_object_id
1396 AND request_id <> p_request_id;
1397 END IF;
1398
1399 -- Bug 4379913: For XGL, OGL Bal, DataX and Client loader executions,
1400 -- default display to N and set it to yes only if the execution
1401 -- produces output rows (to be set in
1402 -- the update_num_of_output_rows procecure).
1403 IF v_object_type_code IN ('SOURCE_DATA_LOADER','DATAX_LOADER',
1404 'XGL_INTEGRATION') THEN
1405 v_display_flag := 'N';
1406 ELSE
1407 v_display_flag := 'Y';
1408 END IF;
1409
1410 -- Register object execution
1411 INSERT INTO fem_pl_object_executions (request_id, object_id,
1415 fem_event_order_seq.NEXTVAL, v_display_flag,'RUNNING',p_user_id, SYSDATE,
1412 exec_object_definition_id, event_order, display_flag, exec_status_code,
1413 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
1414 VALUES (p_request_id, p_object_id, p_exec_object_definition_id,
1416 p_user_id, SYSDATE, p_last_update_login);
1417
1418 -- Register definition of executed object
1419 register_object_def(
1420 p_api_version => 1.0,
1421 p_commit => FND_API.G_FALSE,
1422 p_request_id => p_request_id,
1423 p_object_id => p_object_id,
1424 p_object_definition_id => p_exec_object_definition_id,
1425 p_user_id => p_user_id,
1426 p_last_update_login => p_last_update_login,
1427 x_msg_count => x_msg_count,
1428 x_msg_data => x_msg_data,
1429 x_return_status => x_return_status);
1430
1431 IF x_return_status = g_ret_sts_success THEN
1432 COMMIT WORK;
1433 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
1434 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1435 p_msg_text => 'Registered object execution. REQUEST_ID:'||p_request_id||' OBJECT_ID:'||p_object_id);
1436
1437 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1438 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1439 p_msg_text => 'Released exclusive lock on FEM_PL_OBJECT_EXECUTIONS.');
1440
1441 ELSE
1442 -- If cannot acquire an edit lock for the object, then rollback and return a failure status.
1443
1444 ROLLBACK TO register_object_execution_pub;
1445 FEM_ENGINES_PKG.user_message(p_app_name =>'FEM',
1446 p_msg_name => 'FEM_PL_REG_OBJ_DEF_ERR');
1447
1448 fem_engines_pkg.tech_message(p_severity => c_log_level_5,
1449 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1450 p_msg_name =>'FEM_PL_REG_OBJ_DEF_ERR');
1451
1452 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1453 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1454 p_msg_text => 'Released exclusive lock on FEM_PL_OBJECT_EXECUTIONS.');
1455
1456 x_return_status := g_ret_sts_error;
1457
1458 END IF;
1459
1460 END IF;
1461
1462 -- Standard call to get message count and if count is 1, get message info.
1463 FND_MSG_PUB.Count_And_Get
1464 (p_count => x_msg_count,
1465 p_data => x_msg_data);
1466
1467 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1468 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1469 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
1470
1471 EXCEPTION
1472 WHEN DUP_VAL_ON_INDEX THEN
1473 ROLLBACK TO register_object_execution_pub;
1474 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1475 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1476 p_msg_text => 'Released exclusive lock on FEM_PL_OBJECT_EXECUTIONS .');
1477
1478 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
1479 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1480 p_msg_text => 'End. Object execution already registered. X_RETURN_STATUS:'||x_return_status);
1481
1482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1483 ROLLBACK TO register_object_execution_pub;
1484 x_return_status := g_ret_sts_unexp_error;
1485
1486 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1487 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1488 p_msg_text => 'Released exclusive lock on FEM_PL_OBJECT_EXECUTIONS.');
1489
1490 fem_engines_pkg.tech_message(p_severity => c_log_level_6,
1491 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1492 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
1493 l_api_name||' version: '||l_api_version);
1494
1495 FND_MSG_PUB.Count_And_Get
1496 (p_count => x_msg_count,
1497 p_data => x_msg_data);
1498
1499 WHEN OTHERS THEN
1500 -- Unexpected exceptions
1501 ROLLBACK TO register_object_execution_pub;
1502 x_return_status := g_ret_sts_unexp_error;
1503
1504 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1505 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1506 p_msg_text => 'Released exclusive lock on FEM_PL_OBJECT_EXECUTIONS.');
1507
1508 -- Log the call stack and the Oracle error message to
1509 -- FND_LOG with the "unexpected exception" severity level.
1510
1511 FEM_ENGINES_PKG.Tech_Message
1512 (p_severity => c_log_level_6,
1513 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1514 p_msg_text => SQLERRM);
1515
1516 FEM_ENGINES_PKG.Tech_Message
1517 (p_severity => c_log_level_6,
1518 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1519 p_msg_text => dbms_utility.format_call_stack);
1520
1521 -- Log the Oracle error message to the stack.
1522 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
1523 p_msg_name => 'FEM_UNEXPECTED_ERROR',
1524 P_TOKEN1 => 'ERR_MSG',
1525 P_VALUE1 => SQLERRM);
1526
1530
1527 FND_MSG_PUB.Count_And_Get
1528 (p_count => x_msg_count,
1529 p_data => x_msg_data);
1531 END register_object_execution;
1532 -- ******************************************************************************
1533 PROCEDURE register_request (p_api_version IN NUMBER,
1534 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1535 p_cal_period_id IN NUMBER DEFAULT NULL,
1536 p_ledger_id IN NUMBER DEFAULT NULL,
1537 p_dataset_io_obj_def_id IN NUMBER DEFAULT NULL,
1538 p_output_dataset_code IN NUMBER DEFAULT NULL,
1539 p_source_system_code IN NUMBER DEFAULT NULL,
1540 p_effective_date IN DATE DEFAULT NULL,
1541 p_rule_set_obj_def_id IN NUMBER DEFAULT NULL,
1542 p_rule_set_name IN VARCHAR2 DEFAULT NULL,
1543 p_request_id IN NUMBER,
1544 p_user_id IN NUMBER,
1545 p_last_update_login IN NUMBER,
1546 p_program_id IN NUMBER,
1547 p_program_login_id IN NUMBER,
1548 p_program_application_id IN NUMBER,
1549 p_exec_mode_code IN VARCHAR2 DEFAULT NULL,
1550 p_dimension_id IN NUMBER DEFAULT NULL,
1551 p_table_name IN VARCHAR2 DEFAULT NULL,
1552 p_hierarchy_name IN VARCHAR2 DEFAULT NULL,
1553 x_msg_count OUT NOCOPY NUMBER,
1554 x_msg_data OUT NOCOPY VARCHAR2,
1555 x_return_status OUT NOCOPY VARCHAR2) IS
1556 -- ==========================================================================
1557 -- x_return_status returns: S=successful, E=error, U=unexpected error.
1558 -- ==========================================================================
1559 --BEGIN register request
1560 -- Insert row into FEM_PL_REQUESTS.
1561 -- IF request already exists in fem_pl_requests THEN
1562 -- Set p_request_id = existing request ID;
1563 -- END IF;
1564 --END register_request
1565 -- ==========================================================================
1566
1567 l_api_name CONSTANT VARCHAR2(30) := 'register_request';
1568 l_api_version CONSTANT NUMBER := 1.0;
1569
1570 BEGIN
1571
1572 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1573 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1574 p_msg_text => 'Begin. REQUEST_ID:'||p_request_id||
1575 ' P_RULE_SET_OBJ_DEF_ID:'||p_rule_set_obj_def_id||
1576 ' P_RULE_SET_NAME:'||p_rule_set_name||
1577 ' P_EFFECTIVE_DATE:'||fnd_date.date_to_displaydate(p_effective_date)||
1578 ' P_SOURCE_SYSTEM_CODE:'||p_source_system_code||
1579 ' P_LEDGER_ID:'||p_ledger_id||' P_CAL_PERIOD_ID:'||p_cal_period_id||
1580 ' P_DATASET_IO_OBJ_DEF_ID:'||p_dataset_io_obj_def_id||
1581 ' P_OUTPUT_DATASET_CODE:'||p_output_dataset_code||
1582 ' P_DIMENSION_ID:'||p_dimension_id||' P_TABLE_NAME:'||p_table_name||
1583 ' P_HIERARCHY_NAME:'||p_hierarchy_name||' P_EXEC_MODE_CODE:'||p_exec_mode_code);
1584
1585 -- Standard Start of API savepoint
1586 SAVEPOINT register_request_pub;
1587
1588 -- Standard call to check for call compatibility.
1589 IF NOT FND_API.Compatible_API_Call (l_api_version,
1590 p_api_version,
1591 l_api_name,
1592 g_pkg_name)
1593 THEN
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 END IF;
1596
1597 -- Initialize API return status to success
1598 x_return_status := G_RET_STS_SUCCESS;
1599
1600 INSERT INTO fem_pl_requests (request_id, exec_status_code,
1601 created_by, creation_date, last_updated_by, last_update_date,
1602 last_update_login, program_id, program_login_id,
1603 program_application_id, rule_set_obj_def_id, rule_set_name,
1604 effective_date, cal_period_id, ledger_id, dataset_io_obj_def_id,
1605 output_dataset_code, source_system_code,exec_mode_code, dimension_id,
1606 table_name, hierarchy_name)
1607 VALUES (p_request_id, 'RUNNING',
1608 p_user_id, sysdate, p_user_id, sysdate,
1609 p_last_update_login, p_program_id, p_program_login_id,
1610 p_program_application_id, p_rule_set_obj_def_id, p_rule_set_name,
1611 p_effective_date, p_cal_period_id, p_ledger_id, p_dataset_io_obj_def_id,
1612 p_output_dataset_code, p_source_system_code,p_exec_mode_code, p_dimension_id,
1613 p_table_name, p_hierarchy_name);
1614
1615 IF FND_API.To_Boolean( p_commit ) THEN
1616 COMMIT WORK;
1617 END IF;
1618
1619 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1620 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1621 p_msg_text => 'End. Registered request. X_RETURN_STATUS:'||x_return_status);
1622
1623 -- Standard call to get message count and if count is 1, get message info.
1624 FND_MSG_PUB.Count_And_Get
1625 (p_count => x_msg_count,
1626 p_data => x_msg_data);
1627
1628 EXCEPTION
1629 WHEN DUP_VAL_ON_INDEX THEN
1633 COMMIT WORK;
1630 NULL;
1631
1632 IF FND_API.To_Boolean( p_commit ) THEN
1634 ELSE
1635 ROLLBACK TO register_request_pub;
1636 END IF;
1637
1638 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
1639 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1640 p_msg_text => 'End. Request already registered. X_RETURN_STATUS:'||x_return_status);
1641
1642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1643 ROLLBACK TO register_request_pub;
1644 x_return_status := g_ret_sts_unexp_error;
1645
1646 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
1647 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1648 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
1649 l_api_name||' version: '||l_api_version);
1650
1651 FND_MSG_PUB.Count_And_Get
1652 (p_count => x_msg_count,
1653 p_data => x_msg_data);
1654
1655 END register_request;
1656 -- ******************************************************************************
1657 PROCEDURE unregister_request (p_api_version IN NUMBER,
1658 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1659 p_request_id IN NUMBER,
1660 x_msg_count OUT NOCOPY NUMBER,
1661 x_msg_data OUT NOCOPY VARCHAR2,
1662 x_return_status OUT NOCOPY VARCHAR2) IS
1663 -- ==========================================================================
1664 -- x_return_status returns: S=successful, E=error, U=unexpected error.
1665 -- ==========================================================================
1666 -- BEGIN unregister request
1667 -- If any data is found for the request in FEM_PL_OBJECT_EXECUTIONS THEN
1668 -- x_return_status = E;
1669 -- ELSE
1670 -- Delete FEM_PL_REQUESTS where request_id = p_request_id;
1671 -- x_return_status = S;
1672 -- END unregister request
1673 -- ==========================================================================
1674 v_count NUMBER;
1675 l_api_name CONSTANT VARCHAR2(30) := 'unregister_request';
1676 l_api_version CONSTANT NUMBER := 1.0;
1677
1678 BEGIN
1679
1680 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1681 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1682 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
1683 ' P_COMMIT:'||p_commit);
1684
1685 -- Standard Start of API savepoint
1686 SAVEPOINT unregister_request_pub;
1687
1688 -- Standard call to check for call compatibility.
1689 IF NOT FND_API.Compatible_API_Call (l_api_version,
1690 p_api_version,
1691 l_api_name,
1692 g_pkg_name)
1693 THEN
1694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1695 END IF;
1696
1697 -- Initialize API return status to success
1698 x_return_status := G_RET_STS_SUCCESS;
1699
1700 SELECT count(*) INTO v_count
1701 FROM fem_pl_object_executions
1702 WHERE request_id = p_request_id;
1703
1704 IF v_count = 0 THEN
1705 DELETE fem_pl_requests WHERE request_id = p_request_id;
1706 ELSE
1707 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
1708 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1709 p_msg_text => 'Cannot unregister REQUEST_ID: '||p_request_id||
1710 ' Data exists for request in FEM_PL_OBJECT_EXECUTIONS');
1711
1712 x_return_status := G_RET_STS_ERROR;
1713
1714 END IF;
1715
1716 IF FND_API.To_Boolean( p_commit ) THEN
1717 COMMIT WORK;
1718 END IF;
1719
1720 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1721 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1722 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
1723
1724 EXCEPTION
1725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1726 ROLLBACK TO unregister_request_pub;
1727 x_return_status := g_ret_sts_unexp_error;
1728
1729 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
1730 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1731 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
1732 l_api_name||' version: '||l_api_version);
1733
1734 END unregister_request;
1735 -- ******************************************************************************
1736 PROCEDURE update_request_status (p_api_version IN NUMBER,
1737 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1738 p_request_id IN NUMBER,
1739 p_exec_status_code IN VARCHAR2,
1740 p_user_id IN NUMBER,
1741 p_last_update_login IN NUMBER,
1742 x_msg_count OUT NOCOPY NUMBER,
1743 x_msg_data OUT NOCOPY VARCHAR2,
1744 x_return_status OUT NOCOPY VARCHAR2) IS
1745
1746 l_api_name CONSTANT VARCHAR2(30) := 'update_request_status';
1747 l_api_version CONSTANT NUMBER := 1.0;
1748
1749 BEGIN
1750
1754 ' P_COMMIT:'||p_commit||' P_EXEC_STATUS_CODE:'||p_exec_status_code);
1751 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1752 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1753 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
1755
1756 -- Standard Start of API savepoint
1757 SAVEPOINT update_request_status_pub;
1758
1759 -- Standard call to check for call compatibility.
1760 IF NOT FND_API.Compatible_API_Call (l_api_version,
1761 p_api_version,
1762 l_api_name,
1763 g_pkg_name)
1764 THEN
1765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1766 END IF;
1767
1768 -- Initialize API return status to success
1769 x_return_status := G_RET_STS_SUCCESS;
1770
1771 UPDATE fem_pl_requests SET exec_status_code = p_exec_status_code,
1772 last_updated_by = p_user_id, last_update_date = sysdate,
1773 last_update_login = p_last_update_login
1774 WHERE request_id = p_request_id;
1775
1776 IF FND_API.To_Boolean( p_commit ) THEN
1777 COMMIT WORK;
1778 END IF;
1779
1780 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1781 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1782 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
1783
1784 EXCEPTION
1785 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1786 ROLLBACK TO update_request_status_pub;
1787 x_return_status := g_ret_sts_unexp_error;
1788
1789 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
1790 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1791 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
1792 l_api_name||' version: '||l_api_version);
1793
1794 END update_request_status;
1795 -- ******************************************************************************
1796 PROCEDURE update_obj_exec_status (p_api_version IN NUMBER,
1797 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1798 p_request_id IN NUMBER,
1799 p_object_id IN NUMBER,
1800 p_exec_status_code IN VARCHAR2,
1801 p_user_id IN NUMBER,
1802 p_last_update_login IN NUMBER,
1803 x_msg_count OUT NOCOPY NUMBER,
1804 x_msg_data OUT NOCOPY VARCHAR2,
1805 x_return_status OUT NOCOPY VARCHAR2) IS
1806
1807 l_api_name CONSTANT VARCHAR2(30) := 'update_obj_exec_status';
1808 l_api_version CONSTANT NUMBER := 1.0;
1809
1810 BEGIN
1811
1812 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1813 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1814 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
1815 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
1816 ' P_EXEC_STATUS_CODE:'||p_exec_status_code);
1817
1818 -- Standard Start of API savepoint
1819 SAVEPOINT update_obj_exec_status_pub;
1820
1821 -- Standard call to check for call compatibility.
1822 IF NOT FND_API.Compatible_API_Call (l_api_version,
1823 p_api_version,
1824 l_api_name,
1825 g_pkg_name)
1826 THEN
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828 END IF;
1829
1830 -- Initialize API return status to success
1831 x_return_status := G_RET_STS_SUCCESS;
1832
1833 UPDATE fem_pl_object_executions SET exec_status_code = p_exec_status_code,
1834 last_updated_by = p_user_id, last_update_date = sysdate,
1835 last_update_login = p_last_update_login
1836 WHERE request_id = p_request_id
1837 AND object_id = p_object_id;
1838
1839 IF FND_API.To_Boolean( p_commit ) THEN
1840 COMMIT WORK;
1841 END IF;
1842
1843 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1844 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1845 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
1846
1847 EXCEPTION
1848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849 ROLLBACK TO update_obj_exec_status_pub;
1850 x_return_status := g_ret_sts_unexp_error;
1851
1852 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
1853 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1854 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
1855 l_api_name||' version: '||l_api_version);
1856
1857 END update_obj_exec_status;
1858 -- ******************************************************************************
1859 PROCEDURE update_obj_exec_errors (p_api_version IN NUMBER,
1860 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1861 p_request_id IN NUMBER,
1862 p_object_id IN NUMBER,
1863 p_errors_reported IN NUMBER,
1864 p_errors_reprocessed IN NUMBER,
1865 p_user_id IN NUMBER,
1866 p_last_update_login IN NUMBER,
1867 x_msg_count OUT NOCOPY NUMBER,
1871 l_api_name CONSTANT VARCHAR2(30) := 'update_obj_exec_errors';
1868 x_msg_data OUT NOCOPY VARCHAR2,
1869 x_return_status OUT NOCOPY VARCHAR2) IS
1870
1872 l_api_version CONSTANT NUMBER := 1.0;
1873
1874 BEGIN
1875
1876 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1877 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1878 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
1879 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
1880 ' P_ERRORS_REPORTED:'||p_errors_reported||
1881 ' P_ERRORS_REPROCESSED:'||p_errors_reprocessed);
1882
1883 -- Standard Start of API savepoint
1884 SAVEPOINT update_obj_exec_errors_pub;
1885
1886 -- Standard call to check for call compatibility.
1887 IF NOT FND_API.Compatible_API_Call (l_api_version,
1888 p_api_version,
1889 l_api_name,
1890 g_pkg_name)
1891 THEN
1892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1893 END IF;
1894
1895 -- Initialize API return status to success
1896 x_return_status := G_RET_STS_SUCCESS;
1897
1898 UPDATE fem_pl_object_executions
1899 SET errors_reported = p_errors_reported,
1900 errors_reprocessed=p_errors_reprocessed,
1901 last_updated_by = p_user_id, last_update_date = sysdate,
1902 last_update_login = p_last_update_login
1903 WHERE request_id = p_request_id
1904 AND object_id = p_object_id;
1905
1906 IF FND_API.To_Boolean( p_commit ) THEN
1907 COMMIT WORK;
1908 END IF;
1909
1910 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1911 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1912 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
1913
1914 EXCEPTION
1915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916 ROLLBACK TO update_obj_exec_errors_pub;
1917 x_return_status := g_ret_sts_unexp_error;
1918
1919 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
1920 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1921 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
1922 l_api_name||' version: '||l_api_version);
1923
1924 END update_obj_exec_errors;
1925 -- ******************************************************************************
1926 PROCEDURE register_object_def (p_api_version IN NUMBER,
1927 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1928 p_request_id IN NUMBER,
1929 p_object_id IN NUMBER,
1930 p_object_definition_id IN NUMBER,
1931 p_user_id IN NUMBER,
1932 p_last_update_login IN NUMBER,
1933 x_msg_count OUT NOCOPY NUMBER,
1934 x_msg_data OUT NOCOPY VARCHAR2,
1935 x_return_status OUT NOCOPY VARCHAR2) IS
1936
1937 l_api_name CONSTANT VARCHAR2(30) := 'register_object_def';
1938 l_api_version CONSTANT NUMBER := 1.0;
1939
1940 BEGIN
1941
1942 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1943 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1944 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
1945 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
1946 ' P_OBJECT_DEFINITION_ID:'||p_object_definition_id);
1947
1948 -- Standard Start of API savepoint
1949 SAVEPOINT register_object_def_pub;
1950
1951 -- Standard call to check for call compatibility.
1952 IF NOT FND_API.Compatible_API_Call (l_api_version,
1953 p_api_version,
1954 l_api_name,
1955 g_pkg_name)
1956 THEN
1957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1958 END IF;
1959
1960 -- Initialize API return status to success
1961 x_return_status := G_RET_STS_SUCCESS;
1962
1963 INSERT INTO fem_pl_object_defs (request_id, object_id, object_definition_id,
1964 created_by, creation_date, last_updated_by, last_update_date,
1965 last_update_login)
1966 SELECT
1967 request_id, object_id, p_object_definition_id,
1968 p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
1969 FROM fem_pl_object_executions
1970 WHERE request_id = p_request_id
1971 AND object_id = p_object_id;
1972
1973 IF FND_API.To_Boolean( p_commit ) THEN
1974 COMMIT WORK;
1975 END IF;
1976
1977 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
1978 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1979 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
1980
1981 EXCEPTION
1982 WHEN DUP_VAL_ON_INDEX THEN
1983 NULL;
1984 IF FND_API.To_Boolean( p_commit ) THEN
1985 COMMIT WORK;
1986 ELSE
1987 ROLLBACK TO register_object_def_pub;
1988 END IF;
1989 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
1990 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1991 p_msg_text => 'End. Object definition already registered. X_RETURN_STATUS: '||x_return_status);
1992
1993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1997 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
1994 ROLLBACK TO register_object_def_pub;
1995 x_return_status := g_ret_sts_unexp_error;
1996
1998 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
1999 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2000 l_api_name||' version: '||l_api_version);
2001
2002 END register_object_def;
2003 -- ******************************************************************************
2004 PROCEDURE register_dependent_objdefs (p_api_version IN NUMBER,
2005 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2006 p_request_id IN NUMBER,
2007 p_object_id IN NUMBER,
2008 p_exec_object_definition_id IN NUMBER,
2009 p_effective_date IN DATE,
2010 p_user_id IN NUMBER,
2011 p_last_update_login IN NUMBER,
2012 x_msg_count OUT NOCOPY NUMBER,
2013 x_msg_data OUT NOCOPY VARCHAR2,
2014 x_return_status OUT NOCOPY VARCHAR2) IS
2015
2016 l_api_name CONSTANT VARCHAR2(30) := 'register_dependent_objdefs';
2017 l_api_version CONSTANT NUMBER := 1.0;
2018 v_object_definition_id NUMBER(9);
2019
2020 ------------------------------------------------------------------------
2021 -- In order to retrieve a hierarchical list of required objects,
2022 -- no validation is performed. The cursor that retrieves the required
2023 -- objects will not determine if a required object is missing a valid
2024 -- definition for the specified effective date. It only retrieves the
2025 -- required objects that DO have a valid definition for the given
2026 -- effective date.
2027 ------------------------------------------------------------------------
2028 CURSOR c1 IS
2029 SELECT D.required_object_id
2030 FROM fem_object_dependencies D,
2031 fem_object_definition_b B
2032 WHERE D.required_object_id = B.object_id
2033 AND B.effective_start_date <= p_effective_date
2034 AND B.effective_end_date >= p_effective_date
2035 AND B.old_approved_copy_flag = 'N'
2036 AND B.approval_status_code NOT IN ('SUBMIT_APPROVAL','SUBMIT_DELETE')
2037 START WITH D.object_definition_id = p_exec_object_definition_id
2038 CONNECT BY PRIOR B.object_definition_id = D.object_definition_id;
2039
2040 BEGIN
2041
2042 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2043 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2044 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2045 ' P_OBJECT_ID:'||p_object_id||
2046 ' P_EFFECTIVE_DATE:'||fnd_date.date_to_displaydate(p_effective_date)||
2047 ' P_COMMIT:'||p_commit);
2048
2049 -- Standard Start of API savepoint
2050 SAVEPOINT register_dependent_objdefs_pub;
2051
2052 -- Standard call to check for call compatibility.
2053 IF NOT FND_API.Compatible_API_Call (l_api_version,
2054 p_api_version,
2055 l_api_name,
2056 g_pkg_name)
2057 THEN
2058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2059 END IF;
2060
2061 -- Initialize API return status to success
2062 x_return_status := G_RET_STS_SUCCESS;
2063
2064 FOR a_dependent_objdef IN c1 LOOP
2065 SELECT object_definition_id INTO v_object_definition_id
2066 FROM fem_object_definition_b
2067 WHERE object_id = a_dependent_objdef.required_object_id
2068 AND effective_start_date <= p_effective_date
2069 AND effective_end_date >= p_effective_date
2070 AND old_approved_copy_flag = 'N'
2071 AND approval_status_code NOT IN ('SUBMIT_APPROVAL','SUBMIT_DELETE');
2072
2073 register_object_def(
2074 p_api_version => 1.0,
2075 p_commit => FND_API.G_FALSE,
2076 p_request_id => p_request_id,
2077 p_object_id => p_object_id,
2078 p_object_definition_id => v_object_definition_id,
2079 p_user_id => p_user_id,
2080 p_last_update_login => p_last_update_login,
2081 x_msg_count => x_msg_count,
2082 x_msg_data => x_msg_data,
2083 x_return_status => x_return_status);
2084
2085 IF x_return_status <> g_ret_sts_success THEN
2086 EXIT;
2087 END IF;
2088
2089 END LOOP;
2090
2091 IF FND_API.To_Boolean( p_commit ) THEN
2092 COMMIT WORK;
2093 END IF;
2094
2095 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2096 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2097 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
2098
2099 EXCEPTION
2100 WHEN NO_DATA_FOUND THEN
2101 ROLLBACK TO register_dependent_objdefs_pub;
2102 x_return_status := g_ret_sts_error;
2103 fem_engines_pkg.tech_message(p_severity => c_log_level_5,
2104 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2105 p_msg_name => 'FEM_PL_NO_DEP_OBJ_DEF_ERR',
2106 p_token1 => 'OBJECT_ID',
2107 p_value1 => p_object_id,
2108 p_trans1 => 'N');
2109
2110 fem_engines_pkg.put_message(
2114 p_value1 => p_object_id,
2111 p_app_name => 'FEM',
2112 p_msg_name => 'FEM_PL_NO_DEP_OBJ_DEF_ERR',
2113 p_token1 => 'OBJECT_ID',
2115 p_trans1 => 'N');
2116
2117 FND_MSG_PUB.Count_And_Get
2118 (p_count => x_msg_count,
2119 p_data => x_msg_data);
2120
2121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2122 ROLLBACK TO register_dependent_objdefs_pub;
2123 x_return_status := g_ret_sts_unexp_error;
2124
2125 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2126 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2127 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2128 l_api_name||' version: '||l_api_version);
2129
2130 END register_dependent_objdefs;
2131 -- ******************************************************************************
2132 PROCEDURE register_table (p_api_version IN NUMBER,
2133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2134 p_request_id IN NUMBER,
2135 p_object_id IN NUMBER,
2136 p_table_name IN VARCHAR2,
2137 p_statement_type IN VARCHAR2,
2138 p_num_of_output_rows IN NUMBER,
2139 p_user_id IN NUMBER,
2140 p_last_update_login IN NUMBER,
2141 x_msg_count OUT NOCOPY NUMBER,
2142 x_msg_data OUT NOCOPY VARCHAR2,
2143 x_return_status OUT NOCOPY VARCHAR2) IS
2144
2145 l_api_name CONSTANT VARCHAR2(30) := 'register_table';
2146 l_api_version CONSTANT NUMBER := 1.0;
2147
2148 BEGIN
2149
2150 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2151 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2152 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2153 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2154 ' P_TABLE_NAME:'||p_table_name||
2155 ' P_STATEMENT_TYPE:'||p_statement_type||
2156 ' P_NUM_OF_OUTPUT_ROWS:'||p_num_of_output_rows);
2157
2158 -- Standard Start of API savepoint
2159 SAVEPOINT register_table_pub;
2160
2161 -- Standard call to check for call compatibility.
2162 IF NOT FND_API.Compatible_API_Call (l_api_version,
2163 p_api_version,
2164 l_api_name,
2165 g_pkg_name)
2166 THEN
2167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2168 END IF;
2169
2170 -- Initialize API return status to success
2171 x_return_status := G_RET_STS_SUCCESS;
2172 INSERT INTO fem_pl_tables (request_id, object_id, table_name,
2173 statement_type, num_of_output_rows,
2174 created_by, creation_date, last_updated_by, last_update_date,
2175 last_update_login)
2176 SELECT
2177 request_id, object_id, p_table_name,
2178 p_statement_type, p_num_of_output_rows,
2179 p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
2180 FROM fem_pl_object_executions
2181 WHERE request_id = p_request_id
2182 AND object_id = p_object_id;
2183
2184 IF FND_API.To_Boolean( p_commit ) THEN
2185 COMMIT WORK;
2186 END IF;
2187
2188 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2189 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2190 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
2191
2192 EXCEPTION
2193 WHEN DUP_VAL_ON_INDEX THEN
2194 NULL;
2195 IF FND_API.To_Boolean( p_commit ) THEN
2196 COMMIT WORK;
2197 ELSE
2198 ROLLBACK TO register_table_pub;
2199 END IF;
2200 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2201 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2202 p_msg_text => 'End. Table already registered. X_RETURN_STATUS: '||x_return_status);
2203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2204 ROLLBACK TO register_table_pub;
2205 x_return_status := g_ret_sts_unexp_error;
2206
2207 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2208 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2209 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2210 l_api_name||' version: '||l_api_version);
2211
2212 END register_table;
2213 -- ******************************************************************************
2214 PROCEDURE update_num_of_output_rows (p_api_version IN NUMBER,
2215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2216 p_request_id IN NUMBER,
2217 p_object_id IN NUMBER,
2218 p_table_name IN VARCHAR2,
2219 p_statement_type IN VARCHAR2,
2220 p_num_of_output_rows IN NUMBER,
2221 p_user_id IN NUMBER,
2222 p_last_update_login IN NUMBER,
2223 x_msg_count OUT NOCOPY NUMBER,
2224 x_msg_data OUT NOCOPY VARCHAR2,
2225 x_return_status OUT NOCOPY VARCHAR2) IS
2229
2226
2227 l_api_name CONSTANT VARCHAR2(30) := 'update_num_of_output_rows';
2228 l_api_version CONSTANT NUMBER := 1.0;
2230 v_object_type_code FEM_OBJECT_TYPES.object_type_code%TYPE;
2231
2232 BEGIN
2233
2234 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2235 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2236 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2237 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2238 ' P_TABLE_NAME:'||p_table_name||
2239 ' P_STATEMENT_TYPE:'||p_statement_type||
2240 ' P_NUM_OF_OUTPUT_ROWS:'||p_num_of_output_rows);
2241
2242 -- Standard Start of API savepoint
2243 SAVEPOINT update_num_of_output_rows_pub;
2244
2245 -- Standard call to check for call compatibility.
2246 IF NOT FND_API.Compatible_API_Call (l_api_version,
2247 p_api_version,
2248 l_api_name,
2249 g_pkg_name)
2250 THEN
2251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2252 END IF;
2253
2254 -- Initialize API return status to unexp error
2255 x_return_status := G_RET_STS_UNEXP_ERROR;
2256
2257 UPDATE fem_pl_tables SET num_of_output_rows = p_num_of_output_rows,
2258 last_updated_by = p_user_id, last_update_date = sysdate,
2259 last_update_login = p_last_update_login
2260 WHERE request_id = p_request_id
2261 AND object_id = p_object_id
2262 AND table_name = p_table_name
2263 AND statement_type = p_statement_type;
2264
2265 -- Bug 4379913, 4382591: For XGL, OGL, DataX and Client loader executions,
2266 -- set FEM_PL_OBJECT_EXECUTIONS.display_flag to Yes if the
2267 -- execution actually produced results (i.e. output rows > 0).
2268 -- Also make sure that earlier executions of the same rule with
2269 -- the same parameter combination have display_flag set to No.
2270
2271 IF p_num_of_output_rows > 0 THEN
2272
2273 -- Determine object type code for the object
2274 SELECT object_type_code
2275 INTO v_object_type_code
2276 FROM fem_object_catalog_b
2277 WHERE object_id = p_object_id;
2278
2279 IF v_object_type_code IN ('XGL_INTEGRATION',
2280 'SOURCE_DATA_LOADER','DATAX_LOADER') THEN
2281 UPDATE fem_pl_object_executions
2282 SET display_flag = 'Y'
2283 WHERE request_id = p_request_id
2284 AND object_id = p_object_id
2285 AND display_flag <> 'Y';
2286
2287 -- Since this API is called per table and one object execution
2288 -- can insert/update multiple tables, only perform the following
2289 -- SQL once for each object execution.
2290 IF SQL%ROWCOUNT > 0 THEN
2291 -- For OGL/XGL, processing parameters only include ledger,
2292 -- cal period and dataset. For DataX/Client loaders, the parameters
2293 -- also include source system.
2294 IF v_object_type_code = 'XGL_INTEGRATION' THEN
2295 UPDATE fem_pl_object_executions
2296 SET display_flag = 'N'
2297 WHERE object_id = p_object_id
2298 AND display_flag = 'Y'
2299 AND request_id IN
2300 (SELECT r1.request_id FROM fem_pl_requests r1, fem_pl_requests r2
2301 WHERE r2.request_id = p_request_id
2302 AND r1.request_id <> p_request_id
2303 AND r2.cal_period_id = r1.cal_period_id
2304 AND r2.ledger_id = r1.ledger_id
2305 AND r2.output_dataset_code = r1.output_dataset_code);
2306 ELSIF v_object_type_code IN ('SOURCE_DATA_LOADER','DATAX_LOADER') THEN
2307 UPDATE fem_pl_object_executions
2308 SET display_flag = 'N'
2309 WHERE object_id = p_object_id
2310 AND display_flag = 'Y'
2311 AND request_id IN
2312 (SELECT r1.request_id FROM fem_pl_requests r1, fem_pl_requests r2
2313 WHERE r2.request_id = p_request_id
2314 AND r1.request_id <> p_request_id
2315 AND r2.cal_period_id = r1.cal_period_id
2316 AND r2.ledger_id = r1.ledger_id
2317 AND r2.output_dataset_code = r1.output_dataset_code
2318 AND r2.source_system_code = r1.source_system_code);
2319 END IF; -- v_object_type_code
2320 END IF; -- rowcount > 0
2321 END IF; -- v_object_type_code
2322 END IF; -- p_num_of_output_rows > 0
2323
2324 IF FND_API.To_Boolean( p_commit ) THEN
2325 COMMIT WORK;
2326 END IF;
2327
2328 -- Update API return status to success
2329 x_return_status := G_RET_STS_SUCCESS;
2330
2331 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2332 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2333 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
2334
2335 EXCEPTION
2336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2337 ROLLBACK TO update_num_of_output_rows_pub;
2338 x_return_status := g_ret_sts_unexp_error;
2339
2340 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2341 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2342 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2343 l_api_name||' version: '||l_api_version);
2344
2345 END update_num_of_output_rows;
2346 -- ******************************************************************************
2347 PROCEDURE register_updated_column (p_api_version IN NUMBER,
2351 p_table_name IN VARCHAR2,
2348 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2349 p_request_id IN NUMBER,
2350 p_object_id IN NUMBER,
2352 p_statement_type IN VARCHAR2,
2353 p_column_name IN VARCHAR2,
2354 p_user_id IN NUMBER,
2355 p_last_update_login IN NUMBER,
2356 x_msg_count OUT NOCOPY NUMBER,
2357 x_msg_data OUT NOCOPY VARCHAR2,
2358 x_return_status OUT NOCOPY VARCHAR2) IS
2359
2360 l_api_name CONSTANT VARCHAR2(30) := 'register_updated_column';
2361 l_api_version CONSTANT NUMBER := 1.0;
2362
2363 BEGIN
2364
2365 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2366 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2367 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2368 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2369 ' P_TABLE_NAME:'||p_table_name||
2370 ' P_STATEMENT_TYPE:'||p_statement_type||
2371 ' P_COLUMN_NAME:'||p_column_name);
2372
2373 -- Standard Start of API savepoint
2374 SAVEPOINT register_updated_column_pub;
2375
2376 -- Standard call to check for call compatibility.
2377 IF NOT FND_API.Compatible_API_Call (l_api_version,
2378 p_api_version,
2379 l_api_name,
2380 g_pkg_name)
2381 THEN
2382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2383 END IF;
2384
2385 -- Initialize API return status to success
2386 x_return_status := G_RET_STS_SUCCESS;
2387
2388 INSERT INTO fem_pl_tab_updated_cols (request_id, object_id, table_name,
2389 statement_type, column_name,
2390 created_by, creation_date, last_updated_by, last_update_date,
2391 last_update_login)
2392 SELECT
2393 request_id, object_id, table_name,
2394 statement_type, p_column_name,
2395 p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
2396 FROM fem_pl_tables
2397 WHERE request_id = p_request_id
2398 AND object_id = p_object_id
2399 AND table_name = p_table_name
2400 AND statement_type = p_statement_type;
2401
2402 IF FND_API.To_Boolean( p_commit ) THEN
2403 COMMIT WORK;
2404 END IF;
2405
2406 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2407 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2408 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2409
2410 EXCEPTION
2411 WHEN DUP_VAL_ON_INDEX THEN
2412 NULL;
2413 IF FND_API.To_Boolean( p_commit ) THEN
2414 COMMIT WORK;
2415 ELSE
2416 ROLLBACK TO register_updated_column_pub;
2417 END IF;
2418 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2419 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2420 p_msg_text => 'End. Updated column already registered. X_RETURN_STATUS:'||x_return_status);
2421
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2423 ROLLBACK TO register_updated_column_pub;
2424 x_return_status := g_ret_sts_unexp_error;
2425
2426 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2427 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2428 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2429 l_api_name||' version: '||l_api_version);
2430
2431 END register_updated_column;
2432 -- ******************************************************************************
2433 PROCEDURE register_chain (p_api_version IN NUMBER,
2434 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2435 p_request_id IN NUMBER,
2436 p_object_id IN NUMBER,
2437 p_source_created_by_request_id IN NUMBER,
2438 p_source_created_by_object_id IN NUMBER,
2439 p_user_id IN NUMBER,
2440 p_last_update_login IN NUMBER,
2441 x_msg_count OUT NOCOPY NUMBER,
2442 x_msg_data OUT NOCOPY VARCHAR2,
2443 x_return_status OUT NOCOPY VARCHAR2) IS
2444
2445 l_api_name CONSTANT VARCHAR2(30) := 'register_chain';
2446 l_api_version CONSTANT NUMBER := 1.0;
2447
2448 BEGIN
2449
2450 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2451 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2452 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2453 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2454 ' P_SOURCE_CREATED_BY_REQUEST_ID:'||p_source_created_by_request_id||
2455 ' P_SOURCE_CREATED_BY_OBJECT_ID:'||p_source_created_by_object_id);
2456
2457 -- Standard Start of API savepoint
2458 SAVEPOINT register_chain_pub;
2459
2460 -- Standard call to check for call compatibility.
2461 IF NOT FND_API.Compatible_API_Call (l_api_version,
2462 p_api_version,
2463 l_api_name,
2464 g_pkg_name)
2465 THEN
2469 -- Initialize API return status to success
2466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2467 END IF;
2468
2470 x_return_status := G_RET_STS_SUCCESS;
2471
2472 INSERT INTO fem_pl_chains (request_id, object_id,
2473 source_created_by_request_id, source_created_by_object_id,
2474 created_by, creation_date, last_updated_by, last_update_date,
2475 last_update_login)
2476 SELECT
2477 request_id, object_id,
2478 p_source_created_by_request_id, p_source_created_by_object_id,
2479 p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
2480 FROM fem_pl_object_executions
2481 WHERE request_id = p_request_id
2482 AND object_id = p_object_id;
2483
2484 IF FND_API.To_Boolean( p_commit ) THEN
2485 COMMIT WORK;
2486 END IF;
2487
2488 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2489 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2490 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2491
2492 EXCEPTION
2493 WHEN DUP_VAL_ON_INDEX THEN
2494 NULL;
2495
2496 IF FND_API.To_Boolean( p_commit ) THEN
2497 COMMIT WORK;
2498 ELSE
2499 ROLLBACK TO register_chain_pub;
2500 END IF;
2501 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2502 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2503 p_msg_text => 'End. Processing chain already registered. X_RETURN_STATUS:'||x_return_status);
2504
2505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2506 ROLLBACK TO register_chain_pub;
2507 x_return_status := g_ret_sts_unexp_error;
2508
2509 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2510 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2511 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2512 l_api_name||' version: '||l_api_version);
2513
2514 END register_chain;
2515 -- ******************************************************************************
2516 PROCEDURE register_temp_object (p_api_version IN NUMBER,
2517 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2518 p_request_id IN NUMBER,
2519 p_object_id IN NUMBER,
2520 p_object_type IN VARCHAR2,
2521 p_object_name IN VARCHAR2,
2522 p_user_id IN NUMBER,
2523 p_last_update_login IN NUMBER,
2524 x_msg_count OUT NOCOPY NUMBER,
2525 x_msg_data OUT NOCOPY VARCHAR2,
2526 x_return_status OUT NOCOPY VARCHAR2) IS
2527
2528 l_api_name CONSTANT VARCHAR2(30) := 'register_temp_object';
2529 l_api_version CONSTANT NUMBER := 1.0;
2530
2531 BEGIN
2532
2533 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2534 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2535 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2536 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2537 ' P_OBJECT_TYPE:'||p_object_type||
2538 ' P_OBJECT_NAME:'||p_object_name);
2539
2540 -- Standard Start of API savepoint
2541 SAVEPOINT register_temp_object_pub;
2542
2543 -- Standard call to check for call compatibility.
2544 IF NOT FND_API.Compatible_API_Call (l_api_version,
2545 p_api_version,
2546 l_api_name,
2547 g_pkg_name)
2548 THEN
2549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2550 END IF;
2551
2552 -- Initialize API return status to success
2553 x_return_status := G_RET_STS_SUCCESS;
2554
2555 INSERT INTO fem_pl_temp_objects (request_id, object_id, object_type,
2556 object_name,
2557 created_by, creation_date, last_updated_by, last_update_date,
2558 last_update_login)
2559 SELECT
2560 request_id, object_id, p_object_type,p_object_name,
2561 p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
2562 FROM fem_pl_object_executions
2563 WHERE request_id = p_request_id
2564 AND object_id = p_object_id;
2565
2566 IF FND_API.To_Boolean( p_commit ) THEN
2567 COMMIT WORK;
2568 END IF;
2569
2570 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2571 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2572 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2573
2574 EXCEPTION
2575 WHEN DUP_VAL_ON_INDEX THEN
2576 NULL;
2577 IF FND_API.To_Boolean( p_commit ) THEN
2578 COMMIT WORK;
2579 ELSE
2580 ROLLBACK TO register_temp_object_pub;
2581 END IF;
2582
2583 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2584 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2585 p_msg_text => 'End. Temporary object already registered. X_RETURN_STATUS:'||x_return_status);
2586
2587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2588 ROLLBACK TO register_temp_object_pub;
2589 x_return_status := g_ret_sts_unexp_error;
2590
2591 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2592 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2596 END register_temp_object;
2593 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2594 l_api_name||' version: '||l_api_version);
2595
2597 -- ******************************************************************************
2598 PROCEDURE update_num_of_input_rows (p_api_version IN NUMBER,
2599 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2600 p_request_id IN NUMBER,
2601 p_object_id IN NUMBER,
2602 p_num_of_input_rows IN NUMBER,
2603 p_user_id IN NUMBER,
2604 p_last_update_login IN NUMBER,
2605 x_msg_count OUT NOCOPY NUMBER,
2606 x_msg_data OUT NOCOPY VARCHAR2,
2607 x_return_status OUT NOCOPY VARCHAR2) IS
2608
2609 l_api_name CONSTANT VARCHAR2(30) := 'update_num_of_input_rows';
2610 l_api_version CONSTANT NUMBER := 1.0;
2611
2612 BEGIN
2613
2614 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2615 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2616 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2617 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2618 ' P_NUM_OF_INPUT_ROWS:'||p_num_of_input_rows);
2619
2620 -- Standard Start of API savepoint
2621 SAVEPOINT update_num_of_input_rows_pub;
2622
2623 -- Standard call to check for call compatibility.
2624 IF NOT FND_API.Compatible_API_Call (l_api_version,
2625 p_api_version,
2626 l_api_name,
2627 g_pkg_name)
2628 THEN
2629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2630 END IF;
2631
2632 -- Initialize API return status to success
2633 x_return_status := G_RET_STS_SUCCESS;
2634
2635 UPDATE fem_pl_object_executions SET num_of_input_rows = p_num_of_input_rows,
2636 last_updated_by = p_user_id, last_update_date = sysdate,
2637 last_update_login = p_last_update_login
2638 WHERE request_id = p_request_id
2639 AND object_id = p_object_id;
2640
2641 IF FND_API.To_Boolean( p_commit ) THEN
2642 COMMIT WORK;
2643 END IF;
2644
2645 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2646 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2647 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2648
2649 EXCEPTION
2650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2651 ROLLBACK TO update_num_of_input_rows_pub;
2652 x_return_status := g_ret_sts_unexp_error;
2653
2654 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2655 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2656 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2657 l_api_name||' version: '||l_api_version);
2658
2659 END update_num_of_input_rows;
2660 -- ******************************************************************************
2661 PROCEDURE register_obj_exec_step (p_api_version IN NUMBER,
2662 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2663 p_request_id IN NUMBER,
2664 p_object_id IN NUMBER,
2665 p_exec_step IN VARCHAR2,
2666 p_exec_status_code IN VARCHAR2,
2667 p_user_id IN NUMBER,
2668 p_last_update_login IN NUMBER,
2669 x_msg_count OUT NOCOPY NUMBER,
2670 x_msg_data OUT NOCOPY VARCHAR2,
2671 x_return_status OUT NOCOPY VARCHAR2) IS
2672
2673 l_api_name CONSTANT VARCHAR2(30) := 'register_obj_exec_step';
2674 l_api_version CONSTANT NUMBER := 1.0;
2675
2676 BEGIN
2677
2678 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2679 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2680 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2681 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2682 ' P_EXEC_STEP:'||p_exec_step||
2683 ' P_EXEC_STATUS_CODE:'||p_exec_status_code);
2684
2685 -- Standard Start of API savepoint
2686 SAVEPOINT register_obj_exec_step_pub;
2687
2688 -- Standard call to check for call compatibility.
2689 IF NOT FND_API.Compatible_API_Call (l_api_version,
2690 p_api_version,
2691 l_api_name,
2692 g_pkg_name)
2693 THEN
2694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2695 END IF;
2696
2697 -- Initialize API return status to success
2698 x_return_status := G_RET_STS_SUCCESS;
2699 INSERT INTO fem_pl_obj_exec_steps (request_id, object_id, exec_step,
2700 exec_status_code, created_by, creation_date, last_updated_by,
2701 last_update_date, last_update_login)
2702 SELECT
2703 request_id, object_id, p_exec_step, p_exec_status_code,
2704 p_user_id, sysdate, p_user_id, sysdate, p_last_update_login
2705 FROM fem_pl_object_executions
2706 WHERE request_id = p_request_id
2707 AND object_id = p_object_id;
2711 END IF;
2708
2709 IF FND_API.To_Boolean( p_commit ) THEN
2710 COMMIT WORK;
2712
2713 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2714 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2715 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2716
2717 EXCEPTION
2718 WHEN DUP_VAL_ON_INDEX THEN
2719
2720 NULL;
2721 IF FND_API.To_Boolean( p_commit ) THEN
2722 COMMIT WORK;
2723 ELSE
2724 ROLLBACK TO register_obj_exec_step_pub;
2725 END IF;
2726 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2727 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2728 p_msg_text => 'End. Object execution step already registered. X_RETURN_STATUS:'||x_return_status);
2729
2730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2731 ROLLBACK TO register_obj_exec_step_pub;
2732 x_return_status := g_ret_sts_unexp_error;
2733
2734 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2735 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2736 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2737 l_api_name||' version: '||l_api_version);
2738
2739 END register_obj_exec_step;
2740 -- ******************************************************************************
2741 PROCEDURE unregister_obj_exec_step (p_api_version IN NUMBER,
2742 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2743 p_request_id IN NUMBER,
2744 p_object_id IN NUMBER,
2745 p_exec_step IN VARCHAR2,
2746 x_msg_count OUT NOCOPY NUMBER,
2747 x_msg_data OUT NOCOPY VARCHAR2,
2748 x_return_status OUT NOCOPY VARCHAR2) IS
2749
2750 l_api_name CONSTANT VARCHAR2(30) := 'unregister_obj_exec_step';
2751 l_api_version CONSTANT NUMBER := 1.0;
2752
2753 BEGIN
2754
2755 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2756 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2757 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2758 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2759 ' P_EXEC_STEP:'||p_exec_step);
2760
2761 -- Standard Start of API savepoint
2762 SAVEPOINT unregister_obj_exec_step_pub;
2763
2764 -- Standard call to check for call compatibility.
2765 IF NOT FND_API.Compatible_API_Call (l_api_version,
2766 p_api_version,
2767 l_api_name,
2768 g_pkg_name)
2769 THEN
2770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2771 END IF;
2772
2773 -- Initialize API return status to success
2774 x_return_status := G_RET_STS_SUCCESS;
2775
2776 DELETE fem_pl_obj_exec_steps
2777 WHERE request_id = p_request_id
2778 AND object_id = p_object_id
2779 AND exec_step = p_exec_step;
2780
2781 IF FND_API.To_Boolean( p_commit ) THEN
2782 COMMIT WORK;
2783 END IF;
2784
2785 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2786 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2787 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2788
2789 EXCEPTION
2790 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2791 ROLLBACK TO unregister_obj_exec_step_pub;
2792 x_return_status := g_ret_sts_unexp_error;
2793
2794 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2795 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2796 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2797 l_api_name||' version: '||l_api_version);
2798
2799 END unregister_obj_exec_step;
2800 -- ******************************************************************************
2801 PROCEDURE unregister_obj_exec_steps (p_api_version IN NUMBER,
2802 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2803 p_request_id IN NUMBER,
2804 p_object_id IN NUMBER,
2805 x_msg_count OUT NOCOPY NUMBER,
2806 x_msg_data OUT NOCOPY VARCHAR2,
2807 x_return_status OUT NOCOPY VARCHAR2)IS
2808
2809 l_api_name CONSTANT VARCHAR2(30) := 'unregister_obj_exec_steps';
2810 l_api_version CONSTANT NUMBER := 1.0;
2811
2812 BEGIN
2813
2814 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2815 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2816 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2817 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit);
2818
2819 -- Standard Start of API savepoint
2820 SAVEPOINT unregister_obj_exec_steps_pub;
2821
2822 -- Standard call to check for call compatibility.
2823 IF NOT FND_API.Compatible_API_Call (l_api_version,
2824 p_api_version,
2825 l_api_name,
2826 g_pkg_name)
2827 THEN
2828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2829 END IF;
2830
2831 -- Initialize API return status to success
2835 WHERE request_id = p_request_id
2832 x_return_status := G_RET_STS_SUCCESS;
2833
2834 DELETE fem_pl_obj_exec_steps
2836 AND object_id = p_object_id;
2837
2838 IF FND_API.To_Boolean( p_commit ) THEN
2839 COMMIT WORK;
2840 END IF;
2841
2842 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2843 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2844 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2845
2846 EXCEPTION
2847 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2848 ROLLBACK TO unregister_obj_exec_steps_pub;
2849 x_return_status := g_ret_sts_unexp_error;
2850
2851 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2852 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2853 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2854 l_api_name||' version: '||l_api_version);
2855
2856 END unregister_obj_exec_steps;
2857 -- ******************************************************************************
2858 PROCEDURE update_obj_exec_step_status (p_api_version IN NUMBER,
2859 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2860 p_request_id IN NUMBER,
2861 p_object_id IN NUMBER,
2862 p_exec_step IN VARCHAR2,
2863 p_exec_status_code IN VARCHAR2,
2864 p_user_id IN NUMBER,
2865 p_last_update_login IN NUMBER,
2866 x_msg_count OUT NOCOPY NUMBER,
2867 x_msg_data OUT NOCOPY VARCHAR2,
2868 x_return_status OUT NOCOPY VARCHAR2) IS
2869
2870 l_api_name CONSTANT VARCHAR2(30) := 'update_obj_exec_step_status';
2871 l_api_version CONSTANT NUMBER := 1.0;
2872
2873 BEGIN
2874
2875 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2876 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2877 p_msg_text => 'Begin. REQUEST_ID: '||p_request_id||
2878 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit||
2879 ' P_EXEC_STEP:'||p_exec_step||
2880 ' P_EXEC_STATUS_CODE:'||p_exec_status_code);
2881
2882 -- Standard Start of API savepoint
2883 SAVEPOINT update_obj_exec_step_statu_pub;
2884
2885 -- Standard call to check for call compatibility.
2886 IF NOT FND_API.Compatible_API_Call (l_api_version,
2887 p_api_version,
2888 l_api_name,
2889 g_pkg_name)
2890 THEN
2891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2892 END IF;
2893
2894 -- Initialize API return status to success
2895 x_return_status := G_RET_STS_SUCCESS;
2896
2897 UPDATE fem_pl_obj_exec_steps SET exec_status_code = p_exec_status_code,
2898 last_updated_by = p_user_id, last_update_date = sysdate,
2899 last_update_login = p_last_update_login
2900 WHERE request_id = p_request_id
2901 AND object_id = p_object_id
2902 AND exec_step = p_exec_step;
2903
2904 IF FND_API.To_Boolean( p_commit ) THEN
2905 COMMIT WORK;
2906 END IF;
2907
2908 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2909 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2910 p_msg_text => 'End. X_RETURN_STATUS:'||x_return_status);
2911
2912 EXCEPTION
2913 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2914 ROLLBACK TO update_obj_exec_step_statu_pub;
2915 x_return_status := g_ret_sts_unexp_error;
2916
2917 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
2918 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2919 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
2920 l_api_name||' version: '||l_api_version);
2921
2922 END update_obj_exec_step_status;
2923 -- ******************************************************************************
2924 PROCEDURE set_exec_state (p_api_version IN NUMBER,
2925 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2926 p_request_id IN NUMBER,
2927 p_object_id IN NUMBER,
2928 x_msg_count OUT NOCOPY NUMBER,
2929 x_msg_data OUT NOCOPY VARCHAR2,
2930 x_return_status OUT NOCOPY VARCHAR2) IS
2931
2932 v_always_rerunnable_flag VARCHAR2(1);
2933 v_call_status BOOLEAN;
2934 v_request_id NUMBER := p_request_id;
2935 v_rphase VARCHAR2(80);
2936 v_rstatus VARCHAR2(80);
2937 v_drphase VARCHAR2(30);
2938 v_dstatus VARCHAR2(30);
2939 v_message VARCHAR2 (240);
2940 v_user_id NUMBER := FND_GLOBAL.User_Id;
2941 v_last_update_login NUMBER := FND_GLOBAL.Login_Id;
2942 l_api_name CONSTANT VARCHAR2(30) := 'set_exec_state';
2943 l_api_version CONSTANT NUMBER := 1.0;
2944
2945 v_cancelled_status VARCHAR2(30);
2946 v_error_status VARCHAR2(30);
2947
2948 BEGIN
2949
2950 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2951 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2955 -- Standard Start of API savepoint
2952 p_msg_text => 'Begin. P_REQUEST_ID: '||p_request_id||
2953 ' P_OBJECT_ID:'||p_object_id||' P_COMMIT:'||p_commit);
2954
2956 SAVEPOINT set_exec_state_pub;
2957
2958 -- Standard call to check for call compatibility.
2959 IF NOT FND_API.Compatible_API_Call (l_api_version,
2960 p_api_version,
2961 l_api_name,
2962 g_pkg_name)
2963 THEN
2964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2965 END IF;
2966
2967 -- Initialize API return status to success
2968 x_return_status := G_RET_STS_SUCCESS;
2969
2970 -- Check to see if a previous run exists which did not terminate gracefully
2971 -- enough to populate the FEM_PL_xxx tables. IF such a previous run exists,
2972 -- set its execution state appropriately.
2973 v_call_status := fnd_concurrent.get_request_status (
2974 request_id => v_request_id,
2975 phase => v_rphase,
2976 status => v_rstatus,
2977 dev_phase => v_drphase,
2978 dev_status => v_dstatus,
2979 message => v_message);
2980
2981 SELECT always_rerunnable_flag INTO v_always_rerunnable_flag
2982 FROM fem_object_types t, fem_object_catalog_b o
2983 WHERE o.object_id = p_object_id
2984 AND o.object_type_code = t.object_type_code;
2985
2986 IF v_always_rerunnable_flag = 'Y' THEN
2987 v_cancelled_status := 'CANCELLED_RERUN';
2988 v_error_status := 'ERROR_RERUN';
2989 ELSE
2990 v_cancelled_status := 'CANCELLED_UNDO';
2991 v_error_status := 'ERROR_UNDO';
2992 END IF;
2993
2994 -- If call returned false and request is in 'RUNNING' state,
2995 -- set request to an error status
2996 IF (NOT v_call_status) THEN
2997 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
2998 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
2999 p_msg_text => 'Call to fnd_concurrent.get_request_status failed');
3000
3001 UPDATE fem_pl_requests SET exec_status_code = v_error_status,
3002 last_updated_by = v_user_id, last_update_date = sysdate,
3003 last_update_login = v_last_update_login
3004 WHERE request_id = p_request_id
3005 AND exec_status_code = 'RUNNING';
3006
3007 UPDATE fem_pl_object_executions SET exec_status_code = v_error_status,
3008 last_updated_by = v_user_id, last_update_date = sysdate,
3009 last_update_login = v_last_update_login
3010 WHERE request_id = p_request_id
3011 AND exec_status_code = 'RUNNING';
3012
3013 ELSIF v_drphase = 'COMPLETE' AND v_dstatus IN ('NORMAL','WARNING') THEN
3014
3015 UPDATE fem_pl_requests SET exec_status_code = 'SUCCESS',
3016 last_updated_by = v_user_id, last_update_date = sysdate,
3017 last_update_login = v_last_update_login
3018 WHERE request_id = p_request_id;
3019
3020 UPDATE fem_pl_object_executions SET exec_status_code = 'SUCCESS',
3021 last_updated_by = v_user_id, last_update_date = sysdate,
3022 last_update_login = v_last_update_login
3023 WHERE request_id = p_request_id
3024 AND exec_status_code = 'RUNNING';
3025
3026 ELSIF v_drphase = 'COMPLETE' AND v_dstatus IN ('ERROR','CANCELLED','TERMINATED','DELETED') THEN
3027
3028 UPDATE fem_pl_requests SET exec_status_code =
3029 DECODE(v_dstatus,'ERROR',v_error_status,v_cancelled_status),
3030 last_updated_by = v_user_id, last_update_date = sysdate,
3031 last_update_login = v_last_update_login
3032 WHERE request_id = p_request_id;
3033
3034 UPDATE fem_pl_object_executions SET exec_status_code =
3035 DECODE(v_dstatus,'ERROR',v_error_status,v_cancelled_status),
3036 last_updated_by = v_user_id, last_update_date = sysdate,
3037 last_update_login = v_last_update_login
3038 WHERE request_id = p_request_id
3039 AND exec_status_code = 'RUNNING';
3040
3041 END IF;
3042
3043 IF FND_API.To_Boolean( p_commit ) THEN
3044 COMMIT WORK;
3045 END IF;
3046
3047 fem_engines_pkg.tech_message(p_severity => c_log_level_2
3048 ,p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3049 p_msg_text => 'End. Request Phase:'||v_drphase||
3050 ' Request Status:'||v_dstatus||' Always Rerunnable Flag: '||
3051 v_always_rerunnable_flag||' X_RETURN_STATUS:'||x_return_status);
3052
3053 EXCEPTION
3054 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3055 ROLLBACK TO set_exec_state_pub;
3056 x_return_status := g_ret_sts_unexp_error;
3057
3058 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
3059 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3060 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
3061 l_api_name||' version: '||l_api_version);
3062
3063 END set_exec_state;
3064 -- ******************************************************************************
3065 PROCEDURE mapping_exec_lock_exists (p_object_id IN NUMBER,
3066 p_exec_object_definition_id IN NUMBER,
3067 p_ledger_id IN NUMBER,
3068 p_cal_period_id IN NUMBER,
3069 p_output_dataset_code IN NUMBER,
3073 x_msg_count OUT NOCOPY NUMBER,
3070 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
3071 x_exec_state OUT NOCOPY VARCHAR2,
3072 x_prev_request_id OUT NOCOPY NUMBER,
3074 x_msg_data OUT NOCOPY VARCHAR2,
3075 x_exec_lock_exists OUT NOCOPY VARCHAR2) IS
3076 -- ==========================================================================
3077 -- Returns true if an object execution lock exists.
3078 -- ** Note: This does not check for an execution lock based on approval locks
3079 -- hence this procedure must not be called directly. Always call
3080 -- obj_execution_lock_exists.
3081 -- ==========================================================================
3082 -- Mapping Rule may not be run for the same ledger, calendar period and dataset code.
3083 -- ==========================================================================
3084 -- BEGIN mapping_obj_execution_lock_exists
3085 -- IF an object execution log does not exists with the same parameters
3086 -- passed into the procedure THEN
3087 -- Return IF and set x_exec_state = NORMAL;
3088 -- ELSIF an object execution log already exists with the same parameters as the
3089 -- current object execution request, and the status of the existing object execution
3090 -- IS IN (CANCELLED_RERUN,ERROR_RERUN) (This detects a rerun) THEN
3091 -- Return IF and set x_exec_state = RERUN;
3092 -- ELSIF P_calling_context = ENGINE AND an object execution log
3093 -- already exists with the same parameters as the current object execution
3094 -- request, and the status of the object execution IS RUNNING and the
3095 -- request_id is the same as that of the current request (This detects a
3096 -- restart) THEN
3097 -- Return IF and set x_exec_state = RESTART;
3098 -- ELSE
3099 -- Return T and put message ('FEM_PL_RESULTS_EXIST_ERR');
3100 -- End if;
3101 -- END mapping_obj_execution_lock_exists;
3102 -- ==========================================================================
3103
3104 v_request_id NUMBER;
3105 v_restart VARCHAR2(1) := 'F';
3106 v_normal_run VARCHAR2(1);
3107 v_rerun VARCHAR2(1) := 'F';
3108 v_return_status VARCHAR2(1);
3109 l_api_name CONSTANT VARCHAR2(30) := 'mapping_exec_lock_exists';
3110
3111
3112 CURSOR c1 IS
3113 SELECT r.request_id
3114 FROM fem_pl_object_executions o, fem_pl_requests r
3115 WHERE r.request_id = o.request_id
3116 AND o.object_id = p_object_id
3117 AND r.cal_period_id = p_cal_period_id
3118 AND r.ledger_id = p_ledger_id
3119 AND r.output_dataset_code = p_output_dataset_code
3120 AND o.exec_status_code = 'RUNNING';
3121
3122
3123 BEGIN
3124
3125 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
3126 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3127 p_msg_text => 'Begin. P_CALLING_CONTEXT: '||p_calling_context||
3128 ' P_OBJECT_ID:'||p_object_id||
3129 ' P_LEDGER_ID:'||p_ledger_id||' P_CAL_PERIOD_ID:'||p_cal_period_id||
3130 ' P_OUTPUT_DATASET_CODE:'||p_output_dataset_code);
3131
3132 x_msg_count := 0;
3133 x_exec_state := NULL;
3134
3135 FOR a_prev_run IN c1 LOOP
3136
3137 set_exec_state (p_api_version => 1.0,
3138 p_commit => fnd_api.g_false,
3139 p_request_id => a_prev_run.request_id,
3140 p_object_id => p_object_id,
3141 x_msg_count => x_msg_count,
3142 x_msg_data => x_msg_data,
3143 x_return_status => v_return_status);
3144
3145 END LOOP;
3146
3147 -- Check if this is a normal run. (If no object executions exist for
3148 -- the same LEDGER, CALENDAR PERIOD and OUTPUT DATASET CODE then it
3149 -- is a normal run).
3150 SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
3151 FROM fem_pl_object_executions o, fem_pl_requests r
3152 WHERE r.request_id = o.request_id
3153 AND o.object_id = p_object_id
3154 AND r.cal_period_id = p_cal_period_id
3155 AND r.ledger_id = p_ledger_id
3156 AND r.output_dataset_code = p_output_dataset_code;
3157
3158 IF v_normal_run = 'F' AND p_calling_context = 'ENGINE' THEN
3159
3160 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3161
3162 -- Check if this is a restart
3163 SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
3164 FROM fem_pl_object_executions o, fem_pl_requests r
3165 WHERE r.request_id = v_request_id
3166 AND r.request_id = o.request_id
3167 AND o.object_id = p_object_id
3168 AND o.exec_status_code = 'RUNNING';
3169
3170 END IF;
3171
3172 IF v_normal_run = 'F' AND v_restart = 'F' THEN
3173
3174 -- Check if this is a rerun. (If object executions exist with
3175 -- the for the same LEDGER, CALENDAR PERIOD and OUTPUT DATASET CODE but
3176 -- the status of the executions is NOT IN ('CANCELLED_RERUN','ERROR_RERUN')
3177 -- then it is not a rerun).
3178 SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
3179 FROM fem_pl_object_executions o, fem_pl_requests r
3180 WHERE r.request_id = o.request_id
3181 AND o.object_id = p_object_id
3182 AND r.cal_period_id = p_cal_period_id
3183 AND r.ledger_id = p_ledger_id
3184 AND r.output_dataset_code = p_output_dataset_code
3188
3185 AND o.exec_status_code NOT IN ('CANCELLED_RERUN','ERROR_RERUN');
3186
3187 END IF;
3189 IF v_normal_run = 'T' THEN
3190
3191 x_exec_lock_exists := 'F';
3192 x_exec_state := 'NORMAL';
3193
3194 ELSIF v_restart = 'T' THEN
3195
3196 x_exec_lock_exists := 'F';
3197 x_exec_state := 'RESTART';
3198
3199 ELSIF v_rerun = 'T' THEN
3200
3201 x_exec_lock_exists := 'F';
3202 x_exec_state := 'RERUN';
3203
3204 -- Use MAX because there could be more than one cancelled/error rerun
3205 -- requests for the same object and parameter set.
3206 SELECT MAX(r.request_id) INTO x_prev_request_id
3207 FROM fem_pl_object_executions o, fem_pl_requests r
3208 WHERE r.request_id = o.request_id
3209 AND o.object_id = p_object_id
3210 AND r.cal_period_id = p_cal_period_id
3211 AND r.ledger_id = p_ledger_id
3212 AND r.output_dataset_code = p_output_dataset_code
3213 AND o.exec_status_code IN ('CANCELLED_RERUN','ERROR_RERUN');
3214
3215 ELSE
3216
3217 x_exec_lock_exists := 'T';
3218 fem_engines_pkg.put_message(p_app_name =>'FEM',
3219 p_msg_name =>'FEM_PL_RESULTS_EXIST_ERR');
3220
3221 END IF;
3222
3223 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
3224 p_module => 'fem.plsql.'||g_pkg_name||'.mapping_exec_lock_exists',
3225 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists||
3226 'Execution state:'||x_exec_state||
3227 ' V_NORMAL_RUN:'||v_normal_run||' V_RESTART:'||v_restart||
3228 ' V_RERUN:'||v_rerun||' X_PREV_REQUEST_ID:'||x_prev_request_id);
3229
3230 FND_MSG_PUB.Count_And_Get
3231 (p_count => x_msg_count,
3232 p_data => x_msg_data);
3233
3234 END mapping_exec_lock_exists;
3235 -- ******************************************************************************
3236 PROCEDURE dim_mbr_ldr_exec_lock_exists (p_object_id IN NUMBER,
3237 p_exec_object_definition_id IN NUMBER,
3238 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
3239 x_exec_state OUT NOCOPY VARCHAR2,
3240 x_msg_count OUT NOCOPY NUMBER,
3241 x_msg_data OUT NOCOPY VARCHAR2,
3242 x_exec_lock_exists OUT NOCOPY VARCHAR2,
3243 x_prev_request_id OUT NOCOPY NUMBER) IS
3244 -- ==========================================================================
3245 -- Returns true if an object execution lock exists.
3246 -- ** Note: This does not check for an execution lock based on approval locks
3247 -- hence this procedure must not be called directly. Always call
3248 -- obj_execution_lock_exists.
3249 -- This procedure also assumes that a dimension loader rule is for
3250 -- a single dimension, and only one dimension loader rule exists
3251 -- per dimension.
3252 -- ==========================================================================
3253 -- Dimension Loader OR any rule type for which this procedure is called,
3254 -- may not be run if another execution of that same rule is still running.
3255 -- UNDO: An undo rule may not be processed if the same rule is already running.
3256 -- DIMENSION_LOADER: A dimension loader rule cannot be processed if the rule is already running.
3257 -- ==========================================================================
3258 -- BEGIN dim_mbr_ldr_exec_lock_exists
3259 -- IF an object execution log does not exist THEN
3260 -- Return F and set x_exec_state = NORMAL;
3261 -- ELSIF an object execution log already exists, and the status of the existing object execution
3262 -- IS IN (SUCCESS,CANCELLED_RERUN,ERROR_RERUN) (This detects a rerun) THEN
3263 -- Return F and set x_exec_state = RERUN;
3264 -- ELSIF P_calling_context = ENGINE AND an object execution log
3265 -- already exists, and the status of the object execution IS RUNNING and the
3266 -- request_id is the same as that of the current request (This detects a
3267 -- restart) THEN
3268 -- Return F and set x_exec_state = RESTART;
3269 -- ELSE
3270 -- Return T and put message ('FEM_PL_OBJ_RUNNING');
3271 -- End if;
3272 -- END dim_mbr_ldr_exec_lock_exists;
3273 -- ==========================================================================
3274
3275 v_request_id NUMBER;
3276 v_restart VARCHAR2(1) := 'F';
3277 v_normal_run VARCHAR2(1);
3278 v_rerun VARCHAR2(1) := 'F';
3279 v_return_status VARCHAR2(1);
3280 l_api_name CONSTANT VARCHAR2(30) := 'dim_mbr_ldr_exec_lock_exists';
3281
3282 CURSOR c1 IS
3283 SELECT r.request_id
3284 FROM fem_pl_object_executions o, fem_pl_requests r
3285 WHERE r.request_id = o.request_id
3286 AND o.object_id = p_object_id
3287 AND o.exec_status_code = 'RUNNING';
3288
3289 BEGIN
3290
3291 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
3292 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3293 p_msg_text => 'Begin. P_CALLING_CONTEXT: '||p_calling_context||
3294 ' P_OBJECT_ID:'||p_object_id);
3295
3296 x_msg_count := 0;
3297 x_exec_state := NULL;
3298
3299 FOR a_prev_run IN c1 LOOP
3300
3301 set_exec_state (p_api_version => 1.0,
3302 p_commit => fnd_api.g_false,
3306 x_msg_data => x_msg_data,
3303 p_request_id => a_prev_run.request_id,
3304 p_object_id => p_object_id,
3305 x_msg_count => x_msg_count,
3307 x_return_status => v_return_status);
3308
3309 END LOOP;
3310
3311 -- Check if this is a normal run. (If no object executions are currently
3312 -- running then it is a normal run).
3313 SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
3314 FROM fem_pl_object_executions o, fem_pl_requests r
3315 WHERE r.request_id = o.request_id
3316 AND o.object_id = p_object_id
3317 AND o.exec_status_code = 'RUNNING';
3318
3319 IF v_normal_run = 'F' AND p_calling_context = 'ENGINE' THEN
3320
3321 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3322
3323 -- Check if this is a restart
3324 SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
3325 FROM fem_pl_object_executions o, fem_pl_requests r
3326 WHERE r.request_id = v_request_id
3327 AND r.request_id = o.request_id
3328 AND o.object_id = p_object_id
3329 AND o.exec_status_code = 'RUNNING';
3330
3331 END IF;
3332
3333 IF v_normal_run = 'F' AND v_restart = 'F' THEN
3334
3335 -- Check if this is a rerun. (If object executions exist but
3336 -- the status of the executions is NOT IN ('CANCELLED_RERUN','ERROR_RERUN',
3337 -- 'SUCCESS') then it is not a rerun).
3338 SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
3339 FROM fem_pl_object_executions o, fem_pl_requests r
3340 WHERE r.request_id = o.request_id
3341 AND o.object_id = p_object_id
3342 AND o.exec_status_code NOT IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
3343
3344 END IF;
3345
3346 IF v_normal_run = 'T' THEN
3347
3348 x_exec_lock_exists := 'F';
3349 x_exec_state := 'NORMAL';
3350
3351 ELSIF v_restart = 'T' THEN
3352
3353 x_exec_lock_exists := 'F';
3354 x_exec_state := 'RESTART';
3355
3356 ELSIF v_rerun = 'T' THEN
3357
3358 x_exec_lock_exists := 'F';
3359 x_exec_state := 'RERUN';
3360
3361 -- Use MAX because there could be more than one cancelled/error rerun
3362 -- requests for the same object and parameter set.
3363 SELECT MAX(r.request_id) INTO x_prev_request_id
3364 FROM fem_pl_object_executions o, fem_pl_requests r
3365 WHERE r.request_id = o.request_id
3366 AND o.object_id = p_object_id
3367 AND o.exec_status_code IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
3368
3369 ELSE
3370
3371 x_exec_lock_exists := 'T';
3372 fem_engines_pkg.put_message(p_app_name =>'FEM',p_msg_name =>'FEM_PL_OBJ_RUNNING');
3373
3374 END IF;
3375
3376 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
3377 p_module => 'fem.plsql.'||g_pkg_name||'.dim_mbr_ldr_exec_lock_exists',
3378 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists||
3379 'Execution state:'||x_exec_state||
3380 ' V_NORMAL_RUN:'||v_normal_run||' V_RESTART:'||v_restart||
3381 ' V_RERUN:'||v_rerun||' X_PREV_REQUEST_ID:'||x_prev_request_id);
3382
3383 FND_MSG_PUB.Count_And_Get
3384 (p_count => x_msg_count,
3385 p_data => x_msg_data);
3386
3387 END dim_mbr_ldr_exec_lock_exists;
3388 -- ******************************************************************************
3389 PROCEDURE datax_ldr_exec_lock_exists (p_object_id IN NUMBER,
3390 p_exec_object_definition_id IN NUMBER,
3391 p_ledger_id IN NUMBER,
3392 p_cal_period_id IN NUMBER,
3393 p_output_dataset_code IN NUMBER,
3394 p_source_system_code IN NUMBER,
3395 p_table_name IN VARCHAR2,
3396 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
3397 x_exec_state OUT NOCOPY VARCHAR2,
3398 x_prev_request_id OUT NOCOPY NUMBER,
3399 x_msg_count OUT NOCOPY NUMBER,
3400 x_msg_data OUT NOCOPY VARCHAR2,
3401 x_exec_lock_exists OUT NOCOPY VARCHAR2) IS
3402 -- ==========================================================================
3403 -- Returns true if an object execution lock exists.
3404 -- ** Note: This does not check for an execution lock based on approval locks
3405 -- hence this procedure must not be called directly. Always call
3406 -- obj_execution_lock_exists.
3407 -- ==========================================================================
3408 -- Data Loader Rule (DATAX_LOADER) and Detail client data loader (SOURCE_DATA_LOADER)
3409 -- may not be run for the same ledger, calendar period,
3410 -- dataset code, source_system_code and table name.
3411 -- ==========================================================================
3412 -- BEGIN datax_ldr_exec_lock_exists
3413 -- IF an object execution log does not exists with the same parameters
3414 -- passed into the procedure THEN
3415 -- Return F and set x_exec_state = NORMAL;
3416 -- ELSIF an object execution log already exists with the same parameters as the
3420 -- ELSIF P_calling_context = ENGINE AND an object execution log
3417 -- current object execution request, and the status of the existing object execution
3418 -- IS IN (CANCELLED_RERUN,ERROR_RERUN) (This detects a rerun) THEN
3419 -- Return F and set x_exec_state = RERUN;
3421 -- already exists with the same parameters as the current object execution
3422 -- request, and the status of the object execution IS RUNNING and the
3423 -- request_id is the same as that of the current request (This detects a
3424 -- restart) THEN
3425 -- Return F and set x_exec_state = RESTART;
3426 -- ELSE
3427 -- Return T and put message ('FEM_PL_RESULTS_EXIST_ERR');
3428 -- End if;
3429 -- END datax_ldr_exec_lock_exists;
3430 -- ==========================================================================
3431
3432 v_request_id NUMBER;
3433 v_restart VARCHAR2(1) := 'F';
3434 v_normal_run VARCHAR2(1);
3435 v_rerun VARCHAR2(1) := 'F';
3436 v_return_status VARCHAR2(1);
3437 l_api_name CONSTANT VARCHAR2(30) := 'datax_ldr_exec_lock_exists';
3438
3439 CURSOR c1 IS
3440 SELECT r.request_id
3441 FROM fem_pl_object_executions o, fem_pl_requests r
3442 WHERE r.request_id = o.request_id
3443 AND o.object_id = p_object_id
3444 AND r.cal_period_id = p_cal_period_id
3445 AND r.ledger_id = p_ledger_id
3446 AND r.output_dataset_code = p_output_dataset_code
3447 AND r.source_system_code = p_source_system_code
3448 AND r.table_name = p_table_name
3449 AND o.exec_status_code = 'RUNNING';
3450
3451
3452 BEGIN
3453
3454 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
3455 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3456 p_msg_text => 'Begin. P_CALLING_CONTEXT: '||p_calling_context||
3457 ' P_OBJECT_ID:'||p_object_id||
3458 ' P_LEDGER_ID:'||p_ledger_id||' P_CAL_PERIOD_ID:'||p_cal_period_id||
3459 ' P_OUTPUT_DATASET_CODE:'||p_output_dataset_code||
3460 ' P_SOURCE_SYSTEM_CODE:'||p_source_system_code||
3461 ' P_TABLE_NAME:'||p_table_name);
3462
3463 x_msg_count := 0;
3464 x_exec_state := NULL;
3465
3466 FOR a_prev_run IN c1 LOOP
3467
3468 set_exec_state (p_api_version => 1.0,
3469 p_commit => fnd_api.g_false,
3470 p_request_id => a_prev_run.request_id,
3471 p_object_id => p_object_id,
3472 x_msg_count => x_msg_count,
3473 x_msg_data => x_msg_data,
3474 x_return_status => v_return_status);
3475
3476 END LOOP;
3477
3478 -- Check if this is a normal run. (If no object executions exist for
3479 -- the same LEDGER, CALENDAR PERIOD, SOURCE SYSTEM CODE and
3480 -- OUTPUT DATASET CODE then it is a normal run).
3481 SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
3482 FROM fem_pl_object_executions o, fem_pl_requests r
3483 WHERE r.request_id = o.request_id
3484 AND o.object_id = p_object_id
3485 AND r.cal_period_id = p_cal_period_id
3486 AND r.ledger_id = p_ledger_id
3487 AND r.table_name = p_table_name
3488 AND r.output_dataset_code = p_output_dataset_code
3489 AND r.source_system_code = p_source_system_code;
3490
3491 IF v_normal_run = 'F' AND p_calling_context = 'ENGINE' THEN
3492
3493 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3494
3495 -- Check if this is a restart
3496 SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
3497 FROM fem_pl_object_executions o, fem_pl_requests r
3498 WHERE r.request_id = v_request_id
3499 AND r.request_id = o.request_id
3500 AND o.object_id = p_object_id
3501 AND r.table_name = p_table_name
3502 AND o.exec_status_code = 'RUNNING';
3503
3504 END IF;
3505
3506 IF v_normal_run = 'F' AND v_restart = 'F' THEN
3507
3508 -- Check if this is a rerun. (If object executions exist with
3509 -- the for the same LEDGER, CALENDAR PERIOD and OUTPUT DATASET CODE but
3510 -- the status of the executions is NOT IN ('CANCELLED_RERUN','ERROR_RERUN')
3511 -- then it is not a rerun).
3512 SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
3513 FROM fem_pl_object_executions o, fem_pl_requests r
3514 WHERE r.request_id = o.request_id
3515 AND o.object_id = p_object_id
3516 AND r.cal_period_id = p_cal_period_id
3517 AND r.ledger_id = p_ledger_id
3518 AND r.output_dataset_code = p_output_dataset_code
3519 AND r.source_system_code = p_source_system_code
3520 AND r.table_name = p_table_name
3521 AND o.exec_status_code NOT IN ('CANCELLED_RERUN','ERROR_RERUN');
3522
3523 END IF;
3524
3525 IF v_normal_run = 'T' THEN
3526
3527 x_exec_lock_exists := 'F';
3528 x_exec_state := 'NORMAL';
3529
3530 ELSIF v_restart = 'T' THEN
3531
3532 x_exec_lock_exists := 'F';
3533 x_exec_state := 'RESTART';
3534
3535 ELSIF v_rerun = 'T' THEN
3536
3537 x_exec_lock_exists := 'F';
3538 x_exec_state := 'RERUN';
3539
3540 -- Use MAX because there could be more than one cancelled/error rerun
3541 -- requests for the same object and parameter set.
3542 SELECT MAX(r.request_id) INTO x_prev_request_id
3543 FROM fem_pl_object_executions o, fem_pl_requests r
3544 WHERE r.request_id = o.request_id
3545 AND o.object_id = p_object_id
3549 AND r.source_system_code = p_source_system_code
3546 AND r.cal_period_id = p_cal_period_id
3547 AND r.ledger_id = p_ledger_id
3548 AND r.output_dataset_code = p_output_dataset_code
3550 AND r.table_name = p_table_name
3551 AND o.exec_status_code IN ('CANCELLED_RERUN','ERROR_RERUN');
3552
3553 ELSE
3554
3555 x_exec_lock_exists := 'T';
3556 fem_engines_pkg.put_message(p_app_name =>'FEM',
3557 p_msg_name =>'FEM_PL_RESULTS_EXIST_ERR');
3558
3559 END IF;
3560
3561 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
3562 p_module => 'fem.plsql.'||g_pkg_name||'.datax_ldr_exec_lock_exists',
3563 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists||
3564 'Execution state:'||x_exec_state||
3565 ' V_NORMAL_RUN:'||v_normal_run||' V_RESTART:'||v_restart||
3566 ' V_RERUN:'||v_rerun||' X_PREV_REQUEST_ID:'||x_prev_request_id);
3567
3568 FND_MSG_PUB.Count_And_Get
3569 (p_count => x_msg_count,
3570 p_data => x_msg_data);
3571
3572 END datax_ldr_exec_lock_exists;
3573 -- ******************************************************************************
3574 PROCEDURE hier_ldr_exec_lock_exists (p_object_id IN NUMBER,
3575 p_exec_object_definition_id IN NUMBER,
3576 p_hierarchy_name IN VARCHAR2,
3577 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
3578 x_exec_state OUT NOCOPY VARCHAR2,
3579 x_msg_count OUT NOCOPY NUMBER,
3580 x_msg_data OUT NOCOPY VARCHAR2,
3581 x_exec_lock_exists OUT NOCOPY VARCHAR2,
3582 x_prev_request_id OUT NOCOPY NUMBER) IS
3583 -- ==========================================================================
3584 -- Returns true if an object execution lock exists.
3585 -- ** Note: This does not check for an execution lock based on approval locks
3586 -- hence this procedure must not be called directly. Always call
3587 -- obj_execution_lock_exists.
3588 -- ==========================================================================
3589 -- Hierarchy Loader may not be run for the same hierarchy if another execution
3590 -- is still running for that same hierarchy.
3591 -- ==========================================================================
3592 -- BEGIN hier_ldr_exec_lock_exists
3593 -- IF an object execution log does not exists with the same parameters
3594 -- passed into the procedure THEN
3595 -- Return F and set x_exec_state = NORMAL;
3596 -- ELSIF an object execution log already exists with the same parameters as the
3597 -- current object execution request, and the status of the existing object execution
3598 -- IS IN (SUCCESS,CANCELLED_RERUN,ERROR_RERUN) (This detects a rerun) THEN
3599 -- Return F and set x_exec_state = RERUN;
3600 -- ELSIF P_calling_context = ENGINE AND an object execution log
3601 -- already exists with the same parameters as the current object execution
3602 -- request, and the status of the object execution IS RUNNING and the
3603 -- request_id is the same as that of the current request (This detects a
3604 -- restart) THEN
3605 -- Return F and set x_exec_state = RESTART;
3606 -- ELSE
3607 -- Return T and put message ('FEM_PL_OBJ_RUNNING');
3608 -- End if;
3609 -- END hier_ldr_exec_lock_exists;
3610 -- ==========================================================================
3611
3612 v_request_id NUMBER;
3613 v_restart VARCHAR2(1) := 'F';
3614 v_normal_run VARCHAR2(1);
3615 v_rerun VARCHAR2(1) := 'F';
3616 v_return_status VARCHAR2(1);
3617 l_api_name CONSTANT VARCHAR2(30) := 'hier_ldr_exec_lock_exists';
3618
3619 CURSOR c1 IS
3620 SELECT r.request_id
3621 FROM fem_pl_object_executions o, fem_pl_requests r
3622 WHERE r.request_id = o.request_id
3623 AND o.object_id = p_object_id
3624 AND r.hierarchy_name = p_hierarchy_name
3625 AND o.exec_status_code = 'RUNNING';
3626
3627 BEGIN
3628
3629 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
3630 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3631 p_msg_text => 'Begin. P_CALLING_CONTEXT: '||p_calling_context||
3632 ' P_OBJECT_ID:'||p_object_id||' P_hierarchy_name:'||p_hierarchy_name);
3633
3634 x_msg_count := 0;
3635 x_exec_state := NULL;
3636
3637 FOR a_prev_run IN c1 LOOP
3638
3639 set_exec_state (p_api_version => 1.0,
3640 p_commit => fnd_api.g_false,
3641 p_request_id => a_prev_run.request_id,
3642 p_object_id => p_object_id,
3643 x_msg_count => x_msg_count,
3644 x_msg_data => x_msg_data,
3645 x_return_status => v_return_status);
3646
3647 END LOOP;
3648
3649 -- Check if this is a normal run. (If no object executions are currently
3650 -- running for the same HIERARCHY then it is a normal run).
3651 SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
3652 FROM fem_pl_object_executions o, fem_pl_requests r
3653 WHERE r.request_id = o.request_id
3654 AND o.object_id = p_object_id
3655 AND r.hierarchy_name = p_hierarchy_name
3656 AND o.exec_status_code = 'RUNNING';
3657
3658 IF v_normal_run = 'F' AND p_calling_context = 'ENGINE' THEN
3659
3660 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3664 FROM fem_pl_object_executions o, fem_pl_requests r
3661
3662 -- Check if this is a restart
3663 SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
3665 WHERE r.request_id = v_request_id
3666 AND r.request_id = o.request_id
3667 AND o.object_id = p_object_id
3668 AND r.hierarchy_name = p_hierarchy_name
3669 AND o.exec_status_code = 'RUNNING';
3670
3671 END IF;
3672
3673 IF v_normal_run = 'F' AND v_restart = 'F' THEN
3674
3675 -- Check if this is a rerun. (If object executions exist with
3676 -- the for the same HIERARCHY but
3677 -- the status of the executions is NOT IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN')
3678 -- then it is not a rerun).
3679 SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
3680 FROM fem_pl_object_executions o, fem_pl_requests r
3681 WHERE r.request_id = o.request_id
3682 AND o.object_id = p_object_id
3683 AND r.hierarchy_name = p_hierarchy_name
3684 AND o.exec_status_code NOT IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
3685
3686 END IF;
3687
3688 IF v_normal_run = 'T' THEN
3689
3690 x_exec_lock_exists := 'F';
3691 x_exec_state := 'NORMAL';
3692
3693 ELSIF v_restart = 'T' THEN
3694
3695 x_exec_lock_exists := 'F';
3696 x_exec_state := 'RESTART';
3697
3698 ELSIF v_rerun = 'T' THEN
3699
3700 x_exec_lock_exists := 'F';
3701 x_exec_state := 'RERUN';
3702
3703 -- Use MAX because there could be more than one cancelled/error rerun
3704 -- requests for the same object and parameter set.
3705 SELECT MAX(r.request_id) INTO x_prev_request_id
3706 FROM fem_pl_object_executions o, fem_pl_requests r
3707 WHERE r.request_id = o.request_id
3708 AND o.object_id = p_object_id
3709 AND r.hierarchy_name = p_hierarchy_name
3710 AND o.exec_status_code IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
3711
3712 ELSE
3713
3714 x_exec_lock_exists := 'T';
3715 fem_engines_pkg.put_message(p_app_name =>'FEM',
3716 p_msg_name =>'FEM_PL_OBJ_RUNNING');
3717
3718 END IF;
3719
3720 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
3721 p_module => 'fem.plsql.'||g_pkg_name||'.hier_ldr_exec_lock_exists',
3722 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists||
3723 'Execution state:'||x_exec_state||
3724 ' V_NORMAL_RUN:'||v_normal_run||' V_RESTART:'||v_restart||
3725 ' V_RERUN:'||v_rerun||' X_PREV_REQUEST_ID:'||x_prev_request_id);
3726
3727 FND_MSG_PUB.Count_And_Get
3728 (p_count => x_msg_count,
3729 p_data => x_msg_data);
3730
3731 END hier_ldr_exec_lock_exists;
3732 -- ******************************************************************************
3733 PROCEDURE rcm_proc_exec_lock_exists (p_object_id IN NUMBER,
3734 p_exec_object_definition_id IN NUMBER,
3735 p_ledger_id IN NUMBER,
3736 p_cal_period_id IN NUMBER,
3737 p_output_dataset_code IN NUMBER,
3738 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
3739 x_exec_state OUT NOCOPY VARCHAR2,
3740 x_prev_request_id OUT NOCOPY NUMBER,
3741 x_msg_count OUT NOCOPY NUMBER,
3742 x_msg_data OUT NOCOPY VARCHAR2,
3743 x_exec_lock_exists OUT NOCOPY VARCHAR2) IS
3744 -- ==========================================================================
3745 -- PRIVATE
3746 -- Returns true if an object execution lock exists.
3747 -- ** Note: This does not check for an execution lock based on approval locks
3748 -- hence this procedure must not be called directly. Always call
3749 -- obj_execution_lock_exists.
3750 -- ==========================================================================
3751 -- RCM_PROCESS_RULE: An RCM Process Rule may not be run if the same rule is
3752 -- still running for the same ledger, calendar period and dataset code.
3753 -- ==========================================================================
3754 -- BEGIN rcm_proc_exec_lock_exists
3755 -- IF an object execution log does not exist with the same parameters
3756 -- passed into the procedure THEN
3757 -- Return F and set x_exec_state = NORMAL;
3758 -- ELSIF an object execution log already exists with the same parameters as the
3759 -- current object execution request, and the status of the existing object execution
3760 -- IS IN (SUCCESS,CANCELLED_RERUN,ERROR_RERUN) (This detects a rerun) THEN
3761 -- Return F and set x_exec_state = RERUN;
3762 -- ELSIF P_calling_context = ENGINE AND an object execution log
3763 -- already exists with the same parameters as the current object execution
3764 -- request, and the status of the object execution IS RUNNING and the
3765 -- request_id is the same as that of the current request (This detects a
3766 -- restart) THEN
3767 -- Return F and set x_exec_state = RESTART;
3768 -- ELSE
3769 -- Return T and put message ('FEM_PL_OBJ_RUNNING');
3770 -- End if;
3771 -- END rcm_proc_exec_lock_exists;
3775 v_restart VARCHAR2(1) := 'F';
3772 -- ==========================================================================
3773
3774 v_request_id NUMBER;
3776 v_normal_run VARCHAR2(1);
3777 v_rerun VARCHAR2(1) := 'F';
3778 v_return_status VARCHAR2(1);
3779 l_api_name CONSTANT VARCHAR2(30) := 'rcm_proc_exec_lock_exists';
3780
3781
3782 CURSOR c1 IS
3783 SELECT r.request_id
3784 FROM fem_pl_object_executions o, fem_pl_requests r
3785 WHERE r.request_id = o.request_id
3786 AND o.object_id = p_object_id
3787 AND r.cal_period_id = p_cal_period_id
3788 AND r.ledger_id = p_ledger_id
3789 AND r.output_dataset_code = p_output_dataset_code
3790 AND o.exec_status_code = 'RUNNING';
3791
3792
3793 BEGIN
3794
3795 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
3796 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3797 p_msg_text => 'Begin. P_CALLING_CONTEXT: '||p_calling_context||
3798 ' P_OBJECT_ID:'||p_object_id||
3799 ' P_LEDGER_ID:'||p_ledger_id||' P_CAL_PERIOD_ID:'||p_cal_period_id||
3800 ' P_OUTPUT_DATASET_CODE:'||p_output_dataset_code);
3801
3802 x_msg_count := 0;
3803 x_exec_state := NULL;
3804
3805 FOR a_prev_run IN c1 LOOP
3806
3807 set_exec_state (p_api_version => 1.0,
3808 p_commit => fnd_api.g_false,
3809 p_request_id => a_prev_run.request_id,
3810 p_object_id => p_object_id,
3811 x_msg_count => x_msg_count,
3812 x_msg_data => x_msg_data,
3813 x_return_status => v_return_status);
3814
3815 END LOOP;
3816
3817 -- Check if this is a normal run. (If no object executions is currently
3818 -- running with the same LEDGER, CALENDAR PERIOD and OUTPUT DATASET CODE
3819 -- then it is a normal run).
3820 SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
3821 FROM fem_pl_object_executions o, fem_pl_requests r
3822 WHERE r.request_id = o.request_id
3823 AND o.object_id = p_object_id
3824 AND r.cal_period_id = p_cal_period_id
3825 AND r.ledger_id = p_ledger_id
3826 AND r.output_dataset_code = p_output_dataset_code
3827 AND o.exec_status_code = 'RUNNING';
3828
3829 IF v_normal_run = 'F' AND p_calling_context = 'ENGINE' THEN
3830
3831 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3832
3833 -- Check if this is a restart
3834 SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
3835 FROM fem_pl_object_executions o, fem_pl_requests r
3836 WHERE r.request_id = v_request_id
3837 AND r.request_id = o.request_id
3838 AND o.object_id = p_object_id
3839 AND o.exec_status_code = 'RUNNING';
3840
3841 END IF;
3842
3843 IF v_normal_run = 'F' AND v_restart = 'F' THEN
3844
3845 -- Check if this is a rerun. (If object executions exist with
3846 -- the same LEDGER, CALENDAR PERIOD and OUTPUT DATASET CODE but
3847 -- the status of the executions is NOT IN ('CANCELLED_RERUN','ERROR_RERUN',
3848 -- 'SUCCESS') then it is not a rerun).
3849 SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
3850 FROM fem_pl_object_executions o, fem_pl_requests r
3851 WHERE r.request_id = o.request_id
3852 AND o.object_id = p_object_id
3853 AND r.cal_period_id = p_cal_period_id
3854 AND r.ledger_id = p_ledger_id
3855 AND r.output_dataset_code = p_output_dataset_code
3856 AND o.exec_status_code NOT IN ('CANCELLED_RERUN','ERROR_RERUN','SUCCESS');
3857
3858 END IF;
3859
3860 IF v_normal_run = 'T' THEN
3861
3862 x_exec_lock_exists := 'F';
3863 x_exec_state := 'NORMAL';
3864
3865 ELSIF v_restart = 'T' THEN
3866
3867 x_exec_lock_exists := 'F';
3868 x_exec_state := 'RESTART';
3869
3870 ELSIF v_rerun = 'T' THEN
3871
3872 x_exec_lock_exists := 'F';
3873 x_exec_state := 'RERUN';
3874
3875 -- Use MAX because there could be more than one cancelled/error rerun
3876 -- requests for the same object and parameter set.
3877 SELECT MAX(r.request_id) INTO x_prev_request_id
3878 FROM fem_pl_object_executions o, fem_pl_requests r
3879 WHERE r.request_id = o.request_id
3880 AND o.object_id = p_object_id
3881 AND r.cal_period_id = p_cal_period_id
3882 AND r.ledger_id = p_ledger_id
3883 AND r.output_dataset_code = p_output_dataset_code
3884 AND o.exec_status_code IN ('CANCELLED_RERUN','ERROR_RERUN','SUCCESS');
3885
3886 ELSE
3887
3888 x_exec_lock_exists := 'T';
3889 fem_engines_pkg.put_message(p_app_name =>'FEM',
3890 p_msg_name =>'FEM_PL_OBJ_RUNNING');
3891
3892 END IF;
3893
3894 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
3895 p_module => 'fem.plsql.'||g_pkg_name||'.mapping_exec_lock_exists',
3896 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists||
3897 'Execution state:'||x_exec_state||
3898 ' V_NORMAL_RUN:'||v_normal_run||' V_RESTART:'||v_restart||
3899 ' V_RERUN:'||v_rerun||' X_PREV_REQUEST_ID:'||x_prev_request_id);
3900
3901 FND_MSG_PUB.Count_And_Get
3902 (p_count => x_msg_count,
3903 p_data => x_msg_data);
3904
3905 END rcm_proc_exec_lock_exists;
3906 -- ****************************************************************************
3907
3911 p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
3908 PROCEDURE preview_exec_lock_exists (
3909 p_object_id IN NUMBER,
3910 p_exec_object_definition_id IN NUMBER,
3912 x_exec_state OUT NOCOPY VARCHAR2,
3913 x_prev_request_id OUT NOCOPY NUMBER,
3914 x_msg_count OUT NOCOPY NUMBER,
3915 x_msg_data OUT NOCOPY VARCHAR2,
3916 x_exec_lock_exists OUT NOCOPY VARCHAR2) IS
3917 -- ==========================================================================
3918 -- Returns true if an object execution lock exists.
3919 -- ** Note: This does not check for an execution lock based on approval locks
3920 -- ==========================================================================
3921 -- Preview Rule versions may not be run more than once, regardless of the
3922 -- runtime parameters.
3923 -- ==========================================================================
3924 -- BEGIN preview_obj_execution_lock_exists
3925 -- IF an object execution log does not exists for the same Preview Rule version THEN
3926 -- Return F and set x_exec_state = NORMAL;
3927 -- ELSIF (P_calling_context = ENGINE
3928 -- AND an object execution log already exists for the same Preview Rule
3929 -- AND the status of the object execution IS RUNNING
3930 -- AND registered request_id is the same as that of the current request) THEN
3931 -- Return F and set x_exec_state = RESTART;
3932 -- ELSE
3933 -- Return T and put message ('FEM_PL_RESULTS_EXIST_ERR');
3934 -- End if;
3935 -- END preview_obj_execution_lock_exists;
3936 -- ==========================================================================
3937
3938 v_request_id NUMBER;
3939 v_restart VARCHAR2(1);
3940 v_normal_run VARCHAR2(1);
3941 v_return_status VARCHAR2(1);
3942 l_api_name CONSTANT VARCHAR2(30) := 'preview_exec_lock_exists';
3943
3944 CURSOR c1 IS
3945 SELECT o.request_id
3946 FROM fem_pl_object_executions o
3947 WHERE o.exec_object_definition_id = p_exec_object_definition_id
3948 AND o.exec_status_code = 'RUNNING';
3949
3950 BEGIN
3951
3952 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
3953 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
3954 p_msg_text => 'Begin. P_CALLING_CONTEXT: '||p_calling_context
3955 ||'; P_OBJECT_ID:'||p_object_id
3956 ||'; P_EXEC_OBJECT_DEFINITION_ID:'||p_exec_object_definition_id);
3957
3958 FOR a_prev_run IN c1 LOOP
3959
3960 set_exec_state (p_api_version => 1.0,
3961 p_commit => fnd_api.g_false,
3962 p_request_id => a_prev_run.request_id,
3963 p_object_id => p_object_id,
3964 x_msg_count => x_msg_count,
3965 x_msg_data => x_msg_data,
3966 x_return_status => v_return_status);
3967
3968 END LOOP;
3969
3970 -- Check if this is a normal run. (If no object executions exist for
3971 -- the same Preview Rule version then it is a normal run).
3972 SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
3973 FROM fem_pl_object_executions o
3974 WHERE o.exec_object_definition_id = p_exec_object_definition_id;
3975
3976 IF v_normal_run = 'F' AND p_calling_context = 'ENGINE' THEN
3977
3978 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3979
3980 -- Check if this is a restart
3981 SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
3982 FROM fem_pl_object_executions o
3983 WHERE o.request_id = v_request_id
3984 AND o.exec_object_definition_id = p_exec_object_definition_id
3985 AND o.exec_status_code = 'RUNNING';
3986
3987 END IF;
3988
3989 IF v_normal_run = 'T' THEN
3990
3991 x_exec_lock_exists := 'F';
3992 x_exec_state := 'NORMAL';
3993
3994 ELSIF v_restart = 'T' THEN
3995
3996 x_exec_lock_exists := 'F';
3997 x_exec_state := 'RESTART';
3998
3999 ELSE
4000
4001 x_exec_lock_exists := 'T';
4002 fem_engines_pkg.put_message(p_app_name =>'FEM',
4003 p_msg_name =>'FEM_PL_RESULTS_EXIST_ERR');
4004
4005 END IF;
4006
4007 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
4008 p_module => 'fem.plsql.'||g_pkg_name||'.preview_exec_lock_exists',
4009 p_msg_text => 'End. Object execution lock exists:'||x_exec_lock_exists
4010 ||'; Execution state:'||x_exec_state
4011 ||'; V_NORMAL_RUN:'||v_normal_run
4012 ||'; V_RESTART:'||v_restart
4013 ||'; X_PREV_REQUEST_ID:'||x_prev_request_id);
4014
4015 FND_MSG_PUB.Count_And_Get
4016 (p_count => x_msg_count,
4017 p_data => x_msg_data);
4018
4019 END preview_exec_lock_exists;
4020 -- ****************************************************************************
4021
4022 PROCEDURE check_chaining (
4023 p_api_version IN NUMBER DEFAULT 1.0,
4024 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4025 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4026 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
4027 x_return_status OUT NOCOPY VARCHAR2,
4028 x_msg_count OUT NOCOPY NUMBER,
4029 x_msg_data OUT NOCOPY VARCHAR2,
4030 p_request_id IN NUMBER,
4031 p_object_id IN NUMBER,
4032 x_dep_request_id OUT NOCOPY NUMBER,
4033 x_dep_object_id OUT NOCOPY NUMBER,
4034 x_chain_exists OUT NOCOPY VARCHAR2
4035 ) IS
4036 -- =========================================================================
4042 -- 01-30-06 G Cheng Created
4037 -- Purpose
4038 -- Given an object execution, check if it has been chained to other
4039 -- object executions. If yes, this procedure also returns one
4040 -- of the object executions chained to the given object execution.
4041 -- History
4043 -- Arguments
4044 -- p_request_id Request ID of the object execution being checked
4045 -- for chaining.
4046 -- p_object_id Object ID of the object execution being checked
4047 -- for chaining.
4048 -- x_dep_request_id Request ID of the object execution that depends on
4049 -- the input object execution
4050 -- x_dep_object_id Object ID of the object execution that depends on
4051 -- the input object execution
4052 -- x_chain_exists Flag to indicate if the input object execution
4053 -- has been chained to another execution.
4054 -- Logic
4055 -- Checks fem_pl_chains table to see if the input object execution
4056 -- exists as a source. If yes, then set x_chain_exists to 'T' and also
4057 -- set x_dep_request/object_id parameters with the dependent object
4058 -- execution information. Otherwise, set x_chain_exists to 'F'.
4059 -- =========================================================================
4060 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
4061 'fem.plsql.fem_pl_pkg.check_chaining';
4062 C_API_NAME CONSTANT VARCHAR2(30) := 'Check_Chaining';
4063 C_API_VERSION CONSTANT NUMBER := 1.0;
4064 --
4065 v_count NUMBER;
4066 e_unexp EXCEPTION;
4067 --
4068 CURSOR c_chains (p_request_id NUMBER, p_object_id NUMBER) IS
4069 SELECT request_id, object_id
4070 FROM fem_pl_chains
4071 WHERE source_created_by_request_id = p_request_id
4072 AND source_created_by_object_id = p_object_id;
4073 --
4074 BEGIN
4075 --
4076 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4077 FEM_ENGINES_PKG.TECH_MESSAGE(
4078 p_severity => FND_LOG.level_procedure,
4079 p_module => C_MODULE,
4080 p_msg_text => 'Begin Procedure');
4081 END IF;
4082
4083 -- Initialize return status to unexpected error
4084 x_return_status := g_ret_sts_unexp_error;
4085
4086 -- Check for call compatibility.
4087 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
4088 p_api_version,
4089 C_API_NAME,
4090 G_PKG_NAME)
4091 THEN
4092 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4093 FEM_ENGINES_PKG.TECH_MESSAGE(
4094 p_severity => FND_LOG.level_unexpected,
4095 p_module => C_MODULE,
4096 p_msg_text => 'INTERNAL ERROR: API Version ('||C_API_VERSION||') not compatible with '
4097 ||'passed in version ('||p_api_version||')');
4098 END IF;
4099 RAISE e_unexp;
4100 END IF;
4101
4102 -- Initialize FND message queue
4103 IF p_init_msg_list = FND_API.G_TRUE then
4104 FND_MSG_PUB.Initialize;
4105 END IF;
4106
4107 -- Check to see this request has been chanined to another process.
4108 OPEN c_chains(p_request_id, p_object_id);
4109 FETCH c_chains INTO x_dep_request_id, x_dep_object_id;
4110 IF c_chains%NOTFOUND THEN
4111 x_chain_exists := FND_API.G_FALSE;
4112 ELSE
4113 x_chain_exists := FND_API.G_TRUE;
4114 END IF;
4115 CLOSE c_chains;
4116
4117 x_return_status := g_ret_sts_success;
4118
4119 IF (p_commit = FND_API.G_TRUE) THEN
4120 COMMIT;
4121 END IF;
4122
4123 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4124 FEM_ENGINES_PKG.TECH_MESSAGE(
4125 p_severity => FND_LOG.level_procedure,
4126 p_module => C_MODULE,
4127 p_msg_text => 'End Procedure');
4128 END IF;
4129 --
4130 EXCEPTION
4131 WHEN others THEN
4132 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4133 FEM_ENGINES_PKG.TECH_MESSAGE(
4134 p_severity => FND_LOG.level_unexpected,
4135 p_module => C_MODULE,
4136 p_msg_text => 'Unexpected error: '||SQLERRM);
4137 END IF;
4138 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4139 FEM_ENGINES_PKG.TECH_MESSAGE(
4140 p_severity => FND_LOG.level_procedure,
4141 p_module => C_MODULE,
4142 p_msg_text => 'End Procedure');
4143 END IF;
4144 x_return_status := g_ret_sts_unexp_error;
4145
4146 END check_chaining;
4147 -- ****************************************************************************
4148
4149 PROCEDURE get_exec_status (
4150 p_api_version IN NUMBER DEFAULT 1.0,
4151 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4152 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4153 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
4154 x_return_status OUT NOCOPY VARCHAR2,
4155 x_msg_count OUT NOCOPY NUMBER,
4156 x_msg_data OUT NOCOPY VARCHAR2,
4157 p_request_id IN NUMBER,
4158 p_object_id IN NUMBER,
4159 x_exec_status_code OUT NOCOPY VARCHAR2
4160 ) IS
4161 -- =========================================================================
4162 -- Purpose
4163 -- Given an object execution, returns the execution status code.
4164 -- History
4165 -- 01-30-06 G Cheng Created
4166 -- 01-10-07 G Cheng Bug 5746626. Mapping Preview project.
4167 -- Arguments
4168 -- p_request_id Request ID of the object execution
4169 -- p_object_id Object ID of the object execution
4170 -- x_exec_status_code Execution status code of the object execution
4171 -- Logic
4172 -- Checks fem_pl_object_executions table to obtain the execution status
4176 -- more accurate.
4173 -- of the input object execution. If the status is RUNNING, make sure
4174 -- that the execution is still actually running by checking the
4175 -- FND Concurrent Program status. The FND Concurrent Program status is
4177 -- =========================================================================
4178 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
4179 'fem.plsql.fem_pl_pkg.get_exec_status';
4180 C_API_NAME CONSTANT VARCHAR2(30) := 'Get_Exec_Status';
4181 C_API_VERSION CONSTANT NUMBER := 1.0;
4182 --
4183 v_count NUMBER;
4184 e_unexp EXCEPTION;
4185 e_api_error EXCEPTION;
4186 v_exec_status_code FEM_PL_OBJECT_EXECUTIONS.exec_status_code%TYPE;
4187 v_object_type_code FEM_OBJECT_CATALOG_B.object_type_code%TYPE;
4188 v_call_status BOOLEAN;
4189 v_request_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
4190 v_req_phase FND_CONCURRENT_REQUESTS.phase_code%TYPE;
4191 v_req_status FND_CONCURRENT_REQUESTS.status_code%TYPE;
4192 v_req_dev_phase VARCHAR2(30);
4193 v_rec_dev_status VARCHAR2(30);
4194 v_rec_message FND_CONCURRENT_REQUESTS.completion_text%TYPE;
4195 --
4196 BEGIN
4197 --
4198 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4199 FEM_ENGINES_PKG.TECH_MESSAGE(
4200 p_severity => FND_LOG.level_procedure,
4201 p_module => C_MODULE,
4202 p_msg_text => 'Begin Procedure: p_request_id = '||to_char(p_request_id)
4203 ||'; p_object_id = '||to_char(p_object_id));
4204 END IF;
4205
4206 -- Initialize return status to unexpected error
4207 x_return_status := g_ret_sts_unexp_error;
4208
4209 -- Check for call compatibility.
4210 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
4211 p_api_version,
4212 C_API_NAME,
4213 G_PKG_NAME)
4214 THEN
4215 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4216 FEM_ENGINES_PKG.TECH_MESSAGE(
4217 p_severity => FND_LOG.level_unexpected,
4218 p_module => C_MODULE,
4219 p_msg_text => 'INTERNAL ERROR: API Version ('||C_API_VERSION||') not compatible with '
4220 ||'passed in version ('||p_api_version||')');
4221 END IF;
4222 RAISE e_unexp;
4223 END IF;
4224
4225 -- Initialize FND message queue
4226 IF p_init_msg_list = FND_API.G_TRUE then
4227 FND_MSG_PUB.Initialize;
4228 END IF;
4229
4230 -- Standard Start of API savepoint
4231 SAVEPOINT get_exec_status_pub;
4232
4233 -- Get the exec status code as recorded in FEM_PL_OBJECT_EXECUTIONS
4234 BEGIN
4235 SELECT exec_status_code
4236 INTO v_exec_status_code
4237 FROM fem_pl_object_executions
4238 WHERE request_id = p_request_id
4239 AND object_id = p_object_id;
4240
4241 -- If the PL status indicates that the execution is still running
4242 -- we need to double check that the execution is indeed still running
4243 -- by calling set_exec_state. Set_exec_state will set the updated
4244 -- status in FEM_PL_OBJECT_EXECUTIONS.
4245 IF v_exec_status_code = 'RUNNING' THEN
4246 set_exec_state (p_api_version => 1.0,
4247 p_commit => FND_API.G_FALSE,
4248 p_request_id => p_request_id,
4249 p_object_id => p_object_id,
4250 x_msg_count => x_msg_count,
4251 x_msg_data => x_msg_data,
4252 x_return_status => x_return_status);
4253
4254 IF x_return_status <> g_ret_sts_success THEN
4255 RAISE e_api_error;
4256 END IF;
4257
4258 -- Get the status again
4259 SELECT exec_status_code
4260 INTO v_exec_status_code
4261 FROM fem_pl_object_executions
4262 WHERE request_id = p_request_id
4263 AND object_id = p_object_id;
4264 END IF;
4265
4266 EXCEPTION
4267 -- Bug 5746626. Mapping Preview project.
4268 -- If the object type is MAPPING_PREVIEW, in the case that
4269 -- the object execution is not yet registered, check the
4270 -- concurrent request status to see if it has completed running or not.
4271 -- If not, set exec_status_code as RUNNING. Otherwise, raise
4272 -- unexpected error because if the concurrent program has finished
4273 -- running, the engine should have registered the object execution already.
4274 WHEN no_data_found THEN
4275 SELECT object_type_code
4276 INTO v_object_type_code
4277 FROM fem_object_catalog_b
4278 WHERE object_id = p_object_id;
4279
4280 IF v_object_type_code = 'MAPPING_PREVIEW' THEN
4281 -- needed because request_id is an IN OUT param
4282 v_request_id := p_request_id;
4283 v_call_status := FND_CONCURRENT.get_request_status (
4284 request_id => v_request_id,
4285 phase => v_req_phase,
4286 status => v_req_status,
4287 dev_phase => v_req_dev_phase,
4288 dev_status => v_rec_dev_status,
4289 message => v_rec_message);
4290 IF v_call_status THEN
4291 -- As of 1/7/07, the possible values for dev_phase are:
4292 -- RUNNING, PENDING, COMPLETE, INACTIVE
4293 IF v_req_dev_phase <> 'COMPLETE' THEN
4294 v_exec_status_code := 'RUNNING';
4295 ELSE
4296 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4297 FEM_ENGINES_PKG.TECH_MESSAGE(
4298 p_severity => FND_LOG.level_statement,
4299 p_module => C_MODULE,
4300 p_msg_text => 'Call to FND_CONCURRENT.get_request_status '
4301 ||'returned with dev phase of: '||v_req_dev_phase);
4302 END IF;
4303 RAISE e_unexp;
4304 END IF;
4305 ELSE
4306 -- If concurrent request is no longer present, it must have
4307 -- already ran and since been removed from FND_REQUESTS due to
4308 -- system cleanup. Set v_exec_status_code to SUCCESS in this case.
4309 v_exec_status_code := 'SUCCESS';
4310 END IF;
4311 ELSE
4312 RAISE;
4313 END IF;
4314 END;
4315
4316 x_exec_status_code := v_exec_status_code;
4317 x_return_status := g_ret_sts_success;
4318
4319 IF (p_commit = FND_API.G_TRUE) THEN
4320 COMMIT;
4321 END IF;
4322
4323 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4324 FEM_ENGINES_PKG.TECH_MESSAGE(
4325 p_severity => FND_LOG.level_procedure,
4326 p_module => C_MODULE,
4327 p_msg_text => 'End Procedure: x_return_status = '||x_return_status
4328 ||'; x_exec_status_code = '||x_exec_status_code);
4329 END IF;
4330 --
4331 EXCEPTION
4332 WHEN e_api_error THEN
4333 ROLLBACK TO get_exec_status_pub;
4334 WHEN others THEN
4335 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4336 FEM_ENGINES_PKG.TECH_MESSAGE(
4337 p_severity => FND_LOG.level_unexpected,
4338 p_module => C_MODULE,
4339 p_msg_text => 'Unexpected error: '||SQLERRM);
4340 END IF;
4341 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4342 FEM_ENGINES_PKG.TECH_MESSAGE(
4343 p_severity => FND_LOG.level_procedure,
4344 p_module => C_MODULE,
4345 p_msg_text => 'End Procedure');
4346 END IF;
4347 ROLLBACK TO get_exec_status_pub;
4348 x_return_status := g_ret_sts_unexp_error;
4349
4350 END get_exec_status;
4351 -- ****************************************************************************
4352
4353
4354 END fem_pl_pkg;
[Home] [Help]