1 PACKAGE BODY FEM_PL_INCR_PKG AS
2 /* $Header: fem_pl_incr.plb 120.1 2006/02/27 17:35:33 gcheng noship $ */
3
4 -- Private package variables
5 G_RUNNING CONSTANT VARCHAR2(10) := 'RUNNING';
6 G_SNAPSHOT CONSTANT VARCHAR2(1) := 'S';
7 G_REPLACEMENT CONSTANT VARCHAR2(1) := 'R';
8 G_ERROR_REPROCESS CONSTANT VARCHAR2(1) := 'E';
9
10 /**************************************************************************
11 -- Private Procedure Declarations
12 **************************************************************************/
13
14 PROCEDURE XGL_Obj_Exec_Lock_Exists
15 (p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
16 p_object_id IN NUMBER,
17 p_obj_def_id IN NUMBER,
18 p_cal_period_id IN NUMBER,
19 p_ledger_id IN NUMBER,
20 p_dataset_code IN NUMBER,
21 x_obj_exec_lock_exists OUT NOCOPY VARCHAR2,
22 x_exec_state OUT NOCOPY VARCHAR2,
23 x_prev_request_id OUT NOCOPY NUMBER,
24 x_num_msg OUT NOCOPY NUMBER);
25
26 PROCEDURE XGL_Exec_Mode_Lock_Exists
27 (p_cal_period_id IN NUMBER,
28 p_ledger_id IN NUMBER,
29 p_dataset_code IN NUMBER,
30 p_object_id IN NUMBER,
31 p_exec_mode IN VARCHAR2,
32 x_exec_mode_lock_exists OUT NOCOPY VARCHAR2,
33 x_num_msg OUT NOCOPY NUMBER);
34
35 PROCEDURE Fact_Obj_Exec_Lock_Exists
36 (p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
37 p_object_id IN NUMBER,
38 p_obj_def_id IN NUMBER,
39 p_cal_period_id IN NUMBER,
40 p_ledger_id IN NUMBER,
41 p_dataset_code IN NUMBER,
42 p_source_system_code IN NUMBER,
43 p_table_name IN VARCHAR2,
44 x_obj_exec_lock_exists OUT NOCOPY VARCHAR2,
45 x_exec_state OUT NOCOPY VARCHAR2,
46 x_prev_request_id OUT NOCOPY NUMBER,
47 x_num_msg OUT NOCOPY NUMBER);
48
49 PROCEDURE Fact_Exec_Mode_Lock_Exists
50 (p_cal_period_id IN NUMBER,
51 p_ledger_id IN NUMBER,
52 p_dataset_code IN NUMBER,
53 p_source_system_code IN NUMBER,
54 p_table_name IN VARCHAR2,
55 p_object_id IN NUMBER,
56 p_exec_mode IN VARCHAR2,
57 x_exec_mode_lock_exists OUT NOCOPY VARCHAR2,
58 x_num_msg OUT NOCOPY NUMBER);
59
60
61 PROCEDURE Snapshot_Period_Lock_Exists
62 (p_cal_period_id IN NUMBER,
63 p_ledger_id IN NUMBER,
64 p_dataset_code IN NUMBER,
65 x_ss_per_lock_exists OUT NOCOPY VARCHAR2,
66 x_num_msg OUT NOCOPY NUMBER);
67
68
69 /**************************************************************************
70 -- Public Procedures
71 **************************************************************************/
72
73 -- =======================================================================
74 PROCEDURE Exec_Lock_Exists
75 (p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
76 p_object_id IN NUMBER,
77 p_obj_def_id IN NUMBER,
78 p_cal_period_id IN NUMBER,
79 p_ledger_id IN NUMBER,
80 p_dataset_code IN NUMBER,
81 p_source_system_code IN VARCHAR2 DEFAULT NULL,
82 p_table_name IN VARCHAR2 DEFAULT NULL,
83 p_exec_mode IN VARCHAR2,
84 x_exec_lock_exists OUT NOCOPY VARCHAR2,
85 x_exec_state OUT NOCOPY VARCHAR2,
86 x_prev_request_id OUT NOCOPY NUMBER,
87 x_num_msg OUT NOCOPY NUMBER) IS
88 -- =========================================================================
89 -- Purpose
90 -- High-level process lock API procedure specific to GL integration and
91 -- other engines that support incremental loads. Calls other API
92 -- procedures to perform process validations to ensure that there are
93 -- no process execution locks or process overlaps and to validate the
94 -- Execution Mode parameter.
95 -- History
96 -- 12-22-03 G Hall Created
97 -- 03-10-04 G Hall Added x_prev_request_id parameter
98 -- 11-22-04 G Hall Bug# 3922507
99 -- Removed call to Snapshot_Period_Lock_Exists;
100 -- this check isn't needed for XGL since roll up
101 -- and roll forward will not be implemented.
102 -- Arguments
103 -- p_calling_context 'ENGINE' (default) or 'UI'.
104 -- p_object_id The Object ID that identifies the rule being
105 -- executed.
106 -- p_obj_def_id The Object Definition ID that identifies the
107 -- rule version being executed.
108 -- p_cal_period_id The Cal Period ID value passed to the engine.
109 -- p_ledger_id The Ledger ID value passed to the engine.
110 -- p_dataset_code The Dataset Code value passed to the engine.
111 -- p_exec_mode The Execution Mode value passed to the engine.
112 -- S (Snapshot), I (Incremental),
113 -- E (Error Reprocessing).
114 -- x_exec_lock_exists 'T' or 'F'
115 -- x_exec_state 'NORMAL', 'RERUN', 'RESTART'
116 -- x_prev_request_id Passes back the Request ID of the previous
117 -- execution for the given ledger, dataset, period,
118 -- and object when x_exec_state = 'RERUN'.
119 -- x_num_msg The number of end-user messages put onto the
120 -- FND message stack by this procedure and any of
121 -- its subordinate procedures.
122 -- Notes
123 -- Called by FEM_PL_PKG.Obj_Execution_Lock_Exists
124 -- =========================================================================
125
126 v_num_msg NUMBER(2) := 0;
127 v_object_type FEM_OBJECT_TYPES.object_type_code%TYPE;
128
129 BEGIN
130
131 FEM_ENGINES_PKG.Tech_Message
132 (p_severity => fnd_log.level_procedure,
133 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
134 'exec_lock_exists.begin',
135 p_msg_text => 'BEGIN');
136
137 -- ----------------------------------------------------------------------
138 -- Call the Obj_Exec_Lock_Exists API procedure to make sure that no
139 -- other integration rule, and no other version of the current
140 -- integration rule, has been run, or is running for the given ledger,
141 -- dataset, and period, and that no other instance of the current rule
142 -- version is currently running for the given ledger, dataset, and
143 -- period, and to retrieve the execution state (NORMAL, RERUN, RESTART).
144 -- ----------------------------------------------------------------------
145
146 SELECT object_type_code
147 INTO v_object_type
148 FROM fem_object_catalog_b
149 WHERE object_id = p_object_id;
150
151 IF v_object_type = 'XGL_INTEGRATION' THEN
152 XGL_Obj_Exec_Lock_Exists
153 (p_calling_context => p_calling_context,
154 p_object_id => p_object_id,
155 p_obj_def_id => p_obj_def_id,
156 p_cal_period_id => p_cal_period_id,
157 p_ledger_id => p_ledger_id,
158 p_dataset_code => p_dataset_code,
159 x_obj_exec_lock_exists => x_exec_lock_exists,
160 x_exec_state => x_exec_state,
161 x_prev_request_id => x_prev_request_id,
162 x_num_msg => v_num_msg);
163 ELSIF v_object_type = 'SOURCE_DATA_LOADER' THEN
164 Fact_Obj_Exec_Lock_Exists
165 (p_calling_context => p_calling_context,
166 p_object_id => p_object_id,
167 p_obj_def_id => p_obj_def_id,
168 p_cal_period_id => p_cal_period_id,
169 p_ledger_id => p_ledger_id,
170 p_dataset_code => p_dataset_code,
171 p_source_system_code => p_source_system_code,
172 p_table_name => p_table_name,
173 x_obj_exec_lock_exists => x_exec_lock_exists,
174 x_exec_state => x_exec_state,
175 x_prev_request_id => x_prev_request_id,
176 x_num_msg => v_num_msg);
177 END IF;
178
179
180 x_num_msg := NVL(v_num_msg, 0);
181
182 IF x_exec_lock_exists = 'T' THEN
183
184 FEM_ENGINES_PKG.Tech_Message
185 (p_severity => fnd_log.level_procedure,
186 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
187 'exec_lock_exists.oele',
188 p_msg_text => 'RETURNING: Object Execution Lock Exists');
189
190 RETURN;
191
192 END IF;
193
194 -- ----------------------------------------------------------------------
195 -- Call the Exec_Mode_Lock_Exists API procedure to verify that for
196 -- Execution Mode = 'S', there are no previous successful executions for
197 -- the given ledger, dataset, and period, and for Execution Mode = 'I'
198 -- or 'E', a successful snapshot run has been executed.
199 -- ----------------------------------------------------------------------
200
201 IF v_object_type = 'XGL_INTEGRATION' THEN
202 XGL_Exec_Mode_Lock_Exists
203 (p_cal_period_id => p_cal_period_id,
204 p_ledger_id => p_ledger_id,
205 p_dataset_code => p_dataset_code,
206 p_object_id => p_object_id,
207 p_exec_mode => p_exec_mode,
208 x_exec_mode_lock_exists => x_exec_lock_exists,
209 x_num_msg => v_num_msg);
210 ELSIF v_object_type = 'SOURCE_DATA_LOADER' THEN
211 Fact_Exec_Mode_Lock_Exists
212 (p_cal_period_id => p_cal_period_id,
213 p_ledger_id => p_ledger_id,
214 p_dataset_code => p_dataset_code,
215 p_source_system_code => p_source_system_code,
216 p_table_name => p_table_name,
217 p_object_id => p_object_id,
218 p_exec_mode => p_exec_mode,
219 x_exec_mode_lock_exists => x_exec_lock_exists,
220 x_num_msg => v_num_msg);
221 END IF;
222 x_num_msg := x_num_msg + NVL(v_num_msg, 0);
223
224 IF x_exec_lock_exists = 'T' THEN
225
226 FEM_ENGINES_PKG.Tech_Message
227 (p_severity => fnd_log.level_procedure,
228 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
229 'exec_lock_exists.emle',
230 p_msg_text => 'RETURNING: Execution Mode Lock Exists; ' ||
231 'x_exec_state = NULL');
232
233 x_exec_state := NULL;
234 RETURN;
235
236 END IF;
237
238 FEM_ENGINES_PKG.Tech_Message
239 (p_severity => fnd_log.level_procedure,
240 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
241 'exec_lock_exists.end',
242 p_msg_text => 'END');
243
244 END Exec_Lock_Exists;
245 -- =======================================================================
246
247
248 /**************************************************************************
249 -- Private Procedures
250 **************************************************************************/
251
252 -- =========================================================================
253 PROCEDURE XGL_Obj_Exec_Lock_Exists
254 (p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
255 p_object_id IN NUMBER,
256 p_obj_def_id IN NUMBER,
257 p_cal_period_id IN NUMBER,
258 p_ledger_id IN NUMBER,
259 p_dataset_code IN NUMBER,
260 x_obj_exec_lock_exists OUT NOCOPY VARCHAR2,
261 x_exec_state OUT NOCOPY VARCHAR2,
262 x_prev_request_id OUT NOCOPY NUMBER,
263 x_num_msg OUT NOCOPY NUMBER) IS
264 -- =========================================================================
265 -- Purpose
266 -- Performs process validations specific to GL integration and other
267 -- engines that support incremental loads, to ensure that there are no
268 -- process execution locks or process overlaps.
269 -- History
270 -- 01-12-04 G Hall Created
271 -- 03-10-04 G Hall Added x_prev_request_id parameter
272 -- 05-21-04 G Hall Bug# 3643866: Fixed first query
273 -- (SELECT DISTINCT e.object_id...) to only look up
274 -- objects with the object type of the current object.
275 -- 05-25-04 G Hall Added call to FEM_PL_PKG.Set_Exec_State.
276 -- Arguments
277 -- p_calling_context 'ENGINE' (default) or 'UI'.
278 -- p_object_id The Object ID identifying the rule being
279 -- executed.
280 -- p_obj_def_id The Object_Definition_ID identifying the version
281 -- of the rule being executed.
282 -- p_cal_period_id The Cal Period ID value passed to the engine.
283 -- p_ledger_id The Ledger ID value passed to the engine.
284 -- p_dataset_code The Dataset Code value passed to the engine.
285 -- x_obj_lock_exists Returns 'T' or 'F'.
286 -- x_exec_state Returns '
287 -- x_prev_request_id Passes back the Request ID of the previous
288 -- execution for the given ledger, dataset, period,
289 -- and object when x_exec_state = 'RERUN'.
290 -- x_num_msg Returns the number of end-user messages put onto
291 -- the FND message stack by this procedure.
292 -- Notes
293 -- Called by FEM_PL_INCR.Exec_Lock_Exists.
294 -- =========================================================================
295
296 v_precedent_obj_id fem_pl_object_executions.object_id%TYPE;
297 v_precedent_obj_def_id fem_pl_object_executions.exec_object_definition_id%TYPE;
298
299 v_prev_event_order fem_pl_object_executions.event_order%TYPE;
300 v_prev_exec_status_cd fem_pl_object_executions.exec_status_code%TYPE;
301 v_prev_request_id fem_pl_object_executions.request_id%TYPE;
302
303 v_current_request_id NUMBER;
304
305 v_msg_count NUMBER;
306 v_msg_data VARCHAR2(512);
307 v_return_status VARCHAR2(1);
308
309 CURSOR c1 IS
310 SELECT r.request_id
311 FROM fem_pl_requests r,
312 fem_pl_object_executions o
313 WHERE r.cal_period_id = p_cal_period_id
314 AND r.ledger_id = p_ledger_id
315 AND r.output_dataset_code = p_dataset_code
316 AND o.request_id = r.request_id
317 AND o.object_id = p_object_id
318 AND o.exec_status_code = 'RUNNING';
319
320 BEGIN
321
322 FEM_ENGINES_PKG.Tech_Message
323 (p_severity => fnd_log.level_procedure,
324 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
325 'xgl_obj_exec_lock_exists.begin',
326 p_msg_text => 'BEGIN');
327
328 -- ----------------------------------------------------------------------
329 -- Reset failed executions that are left in 'RUNNING' status to their
330 -- correct error status.
331 -- ----------------------------------------------------------------------
332
333 FOR reset IN c1 LOOP
334
335 FEM_ENGINES_PKG.Tech_Message
336 (p_severity => fnd_log.level_statement,
337 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
338 'xgl_obj_exec_lock_exists.reset_request_id.',
339 p_app_name => 'FEM',
340 p_msg_name => 'FEM_GL_POST_204',
341 p_token1 => 'VAR_NAME',
342 p_value1 => 'reset.request_id',
343 p_token2 => 'VAR_VAL',
344 p_value2 => reset.request_id);
345
346 FEM_PL_PKG.set_exec_state
347 (p_api_version => 1.0,
348 p_commit => fnd_api.g_false,
349 p_request_id => reset.request_id,
350 p_object_id => p_object_id,
351 x_msg_count => v_msg_count,
352 x_msg_data => v_msg_data,
353 x_return_status => v_return_status);
354
355 END LOOP;
356
357 -- ----------------------------------------------------------------------
358 -- Only one object and object version is allowed to run for any ledger,
359 -- dataset, period combination. Get the object and version that have
360 -- been run for the current ledger, dataset, and period, if any. Only
361 -- currently running or successfully completed executions count for
362 -- establishing the object and object version precedent for the current
363 -- ledger, period, and dataset.
364 -- ----------------------------------------------------------------------
365
366 BEGIN
367
368 SELECT DISTINCT e.object_id, e.exec_object_definition_id
369 INTO v_precedent_obj_id, v_precedent_obj_def_id
370 FROM fem_pl_requests r,
371 fem_pl_object_executions e,
372 fem_object_catalog_b c1
373 WHERE r.ledger_id = p_ledger_id
374 AND r.cal_period_id = p_cal_period_id
375 AND r.output_dataset_code = p_dataset_code
376 AND e.request_id = r.request_id
377 AND c1.object_id = e.object_id
378 AND c1.object_type_code =
379 (SELECT object_type_code
380 FROM fem_object_catalog_b c2
381 WHERE c2.object_id = p_object_id);
382
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385
386 -- ----------------------------------------------------------------
387 -- There is no lock, and execution state is NORMAL. This will be
388 -- the case for the first run of each period for the given ledger
389 -- and dataset, i.e. the snapshot load.
390 -- ----------------------------------------------------------------
391
392 FEM_ENGINES_PKG.Tech_Message
393 (p_severity => fnd_log.level_procedure,
394 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
395 'xgl_obj_exec_lock_exists.end.normal1.',
396 p_msg_text => 'END: x_exec_state = NORMAL');
397
398 x_obj_exec_lock_exists := 'F';
399 x_exec_state := 'NORMAL';
400 x_num_msg := 0;
401
402 RETURN;
403
404 END;
405
406 -- ----------------------------------------------------------------------
407 -- Make sure that the current integration rule matches the rule precedent
408 -- set by previous executions for the given ledger, dataset, and period.
409 -- ----------------------------------------------------------------------
410
411 IF p_object_id <> v_precedent_obj_id THEN
412
413 -- -------------------------------------------------------------------
414 -- Wrong Rule:
415 -- The current rule does not match the rule precedent set for the
416 -- given ledger, dataset, and period.
417 -- -------------------------------------------------------------------
418
419 FEM_ENGINES_PKG.Tech_Message
420 (p_severity => fnd_log.level_exception,
421 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
422 'xgl_obj_exec_lock_exists.end.wr',
423 p_app_name => 'FEM',
424 p_msg_name => 'FEM_GL_POST_016');
425
426 FEM_ENGINES_PKG.Put_Message
427 (p_app_name => 'FEM',
428 p_msg_name => 'FEM_GL_POST_016');
429
430 x_obj_exec_lock_exists := 'T';
431 x_exec_state := NULL;
432 x_num_msg := 1;
433
434 RETURN;
435
436 END IF;
437
438 -- ----------------------------------------------------------------------
439 -- Make sure that the current integration rule version matches the
440 -- rule version precedent set by previous executions for the given
441 -- ledger, dataset, and period.
442 -- ----------------------------------------------------------------------
443
444 IF p_obj_def_id <> v_precedent_obj_def_id THEN
445
446 -- -------------------------------------------------------------------
447 -- Wrong Rule Version:
448 -- The current rule version does not match the rule version precedent
449 -- set for the given ledger, dataset, and period.
450 -- -------------------------------------------------------------------
451
452 FEM_ENGINES_PKG.Tech_Message
453 (p_severity => fnd_log.level_exception,
454 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
455 'xgl_obj_exec_lock_exists.end.wrv',
456 p_app_name => 'FEM',
457 p_msg_name => 'FEM_GL_POST_017');
458
459 FEM_ENGINES_PKG.Put_Message
460 (p_app_name => 'FEM',
461 p_msg_name => 'FEM_GL_POST_017');
462
463 x_obj_exec_lock_exists := 'T';
464 x_exec_state := NULL;
465 x_num_msg := 1;
466
467 RETURN;
468
469 END IF;
470
471 -- ----------------------------------------------------------------------
472 -- Check for execution locks and determine execution state based on the
473 -- EXEC_STATUS_CODE of the most recent execution for the given ledger,
474 -- dataset, period, and object/object version.
475 -- ----------------------------------------------------------------------
476
477 SELECT MAX(e.event_order)
478 INTO v_prev_event_order
479 FROM fem_pl_requests r,
480 fem_pl_object_executions e
481 WHERE r.ledger_id = p_ledger_id
482 AND r.cal_period_id = p_cal_period_id
483 AND r.output_dataset_code = p_dataset_code
484 AND e.request_id = r.request_id
485 AND e.object_id = p_object_id
486 AND e.exec_object_definition_id = p_obj_def_id;
487
488 SELECT exec_status_code, request_id
489 INTO v_prev_exec_status_cd, v_prev_request_id
490 FROM fem_pl_object_executions
491 WHERE event_order = v_prev_event_order;
492
493 IF v_prev_exec_status_cd = 'SUCCESS' THEN
494
495 -- -------------------------------------------------------------------
496 -- No lock exists and execution state is NORMAL
497 -- -------------------------------------------------------------------
498
499 FEM_ENGINES_PKG.Tech_Message
500 (p_severity => fnd_log.level_procedure,
501 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
502 'xgl_obj_exec_lock_exists.end.normal2.',
503 p_msg_text => 'END: x_exec_state = NORMAL');
504
505 x_obj_exec_lock_exists := 'F';
506 x_exec_state := 'NORMAL';
507 x_num_msg := 0;
508
509 RETURN;
510
511 ELSIF v_prev_exec_status_cd IN ('CANCELLED_RERUN', 'ERROR_RERUN') THEN
512
513 -- -------------------------------------------------------------------
514 -- No lock exists and execution state is RERUN
515 -- -------------------------------------------------------------------
516
517 FEM_ENGINES_PKG.Tech_Message
518 (p_severity => fnd_log.level_procedure,
519 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
520 'xgl_obj_exec_lock_exists.end.rerun',
521 p_msg_text => 'END: x_exec_state = RERUN');
522
523 x_obj_exec_lock_exists := 'F';
524 x_exec_state := 'RERUN';
525 x_prev_request_id := v_prev_request_id;
526 x_num_msg := 0;
527
528 RETURN;
529
530 ELSIF v_prev_exec_status_cd IN ('CANCELLED_UNDO', 'ERROR_UNDO') THEN
531
532 -- -------------------------------------------------------------------
533 -- Exec Undo Lock:
534 -- A lock exists until the previous execution is undone
535 -- -------------------------------------------------------------------
536
537 FEM_ENGINES_PKG.Tech_Message
538 (p_severity => fnd_log.level_exception,
539 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
540 'xgl_obj_exec_lock_exists.end.eule1.',
541 p_app_name => 'FEM',
542 p_msg_name => 'FEM_GL_POST_018');
543
544 FEM_ENGINES_PKG.Put_Message
545 (p_app_name => 'FEM',
546 p_msg_name => 'FEM_GL_POST_018');
547
548 x_obj_exec_lock_exists := 'T';
549 x_exec_state := NULL;
550 x_num_msg := 1;
551
552 RETURN;
553
554 ELSIF v_prev_exec_status_cd = 'RUNNING' THEN
555
556 -- -------------------------------------------------------------------
557 -- Determine whether this is a restart or whether another instance is
558 -- still running. This comparison differs depending on the calling
559 -- context. The "restart" execution mode is impossible from the UI
560 -- because there is no concurrent manager context -- The UI is only
561 -- checking if there is a lock prior to starting a concurrent request.
562 -- So a RUNNING status for the previous execution can only mean that
563 -- the previous execution is still running.
564 -- -------------------------------------------------------------------
565
566 IF p_calling_context = 'UI' THEN
567
568 -- ----------------------------------------------------------------
569 -- Object Already Running:
570 -- A lock exists because another instance of the current object
571 -- version is still running for the given ledger, dataset, and
572 -- period.
573 -- ----------------------------------------------------------------
574
575 FEM_ENGINES_PKG.Tech_Message
576 (p_severity => fnd_log.level_exception,
577 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
578 'xgl_obj_exec_lock_exists.end.oar1.',
579 p_app_name => 'FEM',
580 p_msg_name => 'FEM_GL_POST_019');
581
582 FEM_ENGINES_PKG.Put_Message
583 (p_app_name => 'FEM',
584 p_msg_name => 'FEM_GL_POST_019');
585
586 x_obj_exec_lock_exists := 'T';
587 x_exec_state := NULL;
588 x_num_msg := 1;
589
590 RETURN;
591
592 ELSE
593
594 -- ----------------------------------------------------------------
595 -- p_calling_context must be 'ENGINE'
596 -- ----------------------------------------------------------------
597
598 v_current_request_id := FND_GLOBAL.CONC_REQUEST_ID;
599
600 IF v_current_request_id = v_prev_request_id THEN
601
602 -- -------------------------------------------------------------
603 -- No lock exists, execution state is RESTART
604 -- -------------------------------------------------------------
605
606 FEM_ENGINES_PKG.Tech_Message
607 (p_severity => fnd_log.level_procedure,
608 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
609 'xgl_obj_exec_lock_exists.end.restart',
610 p_msg_text => 'END: x_exec_state = RESTART');
611
612 x_obj_exec_lock_exists := 'F';
613 x_exec_state := 'RESTART';
614 x_num_msg := 0;
615
616 RETURN;
617
618 ELSE
619
620 -- -------------------------------------------------------------
621 -- Object Already Running:
622 -- A lock exists because another instance of the current object
623 -- version is still running for the given ledger, dataset, and
624 -- period.
625 -- -------------------------------------------------------------
626
627 FEM_ENGINES_PKG.Tech_Message
628 (p_severity => fnd_log.level_exception,
629 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
630 'xgl_obj_exec_lock_exists.end.oar2.',
631 p_app_name => 'FEM',
632 p_msg_name => 'FEM_GL_POST_019');
633
634 FEM_ENGINES_PKG.Put_Message
635 (p_app_name => 'FEM',
636 p_msg_name => 'FEM_GL_POST_019');
637
638 x_obj_exec_lock_exists := 'T';
639 x_exec_state := NULL;
640 x_num_msg := 1;
641
642 RETURN;
643
644 END IF;
645
646 END IF;
647
648 END IF;
649
650 FEM_ENGINES_PKG.Tech_Message
651 (p_severity => fnd_log.level_exception,
652 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
653 'xgl_obj_exec_lock_exists.end',
654 p_msg_text => 'Invalid Exec Status for previous execution: ' ||
655 v_prev_exec_status_cd ||
656 '. Previous Request ID: ' || TO_CHAR(v_prev_request_id));
657
658 FEM_ENGINES_PKG.Tech_Message
659 (p_severity => fnd_log.level_exception,
660 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
661 'xgl_obj_exec_lock_exists.end.eule2.',
662 p_app_name => 'FEM',
663 p_msg_name => 'FEM_GL_POST_018');
664
665 FEM_ENGINES_PKG.Put_Message
666 (p_app_name => 'FEM',
667 p_msg_name => 'FEM_GL_POST_018');
668
669 x_obj_exec_lock_exists := 'T';
670 x_exec_state := NULL;
671 x_num_msg := 1;
672
673 END XGL_Obj_Exec_Lock_Exists;
674 -- =========================================================================
675
676
677 -- =========================================================================
678 PROCEDURE XGL_Exec_Mode_Lock_Exists
679 (p_cal_period_id IN NUMBER,
680 p_ledger_id IN NUMBER,
681 p_dataset_code IN NUMBER,
682 p_object_id IN NUMBER,
683 p_exec_mode IN VARCHAR2,
684 x_exec_mode_lock_exists OUT NOCOPY VARCHAR2,
685 x_num_msg OUT NOCOPY NUMBER) IS
686 -- =========================================================================
687 -- Purpose
688 -- Ensure that exactly one snapshot load is run first, before any
689 -- incremental loads, for each ledger, dataset, and period.
690 -- History
691 -- 01-12-04 G Hall Created
692 -- Arguments
693 -- p_cal_period_id The Cal Period ID value passed to the engine.
694 -- p_ledger_id The Ledger ID value passed to the engine.
695 -- p_dataset_code The Dataset Code value passed to the engine.
696 -- p_object_id The Object ID identifying the rule being
697 -- executed.
698 -- p_exec_mode 'S' (snapshot), 'I', (incremental),
699 -- 'E' (error reprocessing).
700 -- x_exec_mode_lock_exists Returns 'T' or 'F'.
701 -- x_num_msg Returns the number of end-user messages put onto
702 -- the FND message stack by this procedure.
703 -- Logic
704 -- 1. Check that there is no entry in the FEM_PL_REQUESTS table for the
705 -- given ledger, dataset, period, and object with EXEC_MODE_CODE = 'S'
706 -- (SNAPSHOT) and EXEC_STATUS = 'SUCCESS' ('CANCELLED_UNDO' and
707 -- 'ERROR_UNDO' have already been checked for by XGL_Obj_Exec_Lock_Exists,
708 -- and 'CANCELLED_RERUN' or 'ERROR_RERUN' are OK since they indicate an
709 -- incomplete run). If there is, the snapshot has already been run.
710 -- 2. Check that there is an entry in the FEM_PL_REQUESTS table
711 -- for the given ledger, dataset, and period, and object with
712 -- EXEC_MODE_CODE = 'S' (SNAPSHOT) and EXEC_STATUS = 'SUCCESS'. If not,
713 -- raise the Snapshot Not Run Yet exception.
714 -- Notes
715 -- Called by FEM_PL_INCR.Exec_Lock_Exists.
716 -- =========================================================================
717
718 v_row_count NUMBER;
719
720 BEGIN
721
722 FEM_ENGINES_PKG.Tech_Message
723 (p_severity => fnd_log.level_procedure,
724 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
725 'xgl_exec_mode_lock_exists.begin',
726 p_msg_text => 'BEGIN');
727
728 IF p_exec_mode NOT IN ('S', 'I', 'E') THEN
729
730 -- Execution mode is invalid
731
732 FEM_ENGINES_PKG.Tech_Message
733 (p_severity => fnd_log.level_exception,
734 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
735 'xgl_exec_mode_lock_exists.iem',
736 p_app_name => 'FEM',
737 p_msg_name => 'FEM_GL_POST_014');
738
739 FEM_ENGINES_PKG.Put_Message
740 (p_app_name => 'FEM',
741 p_msg_name => 'FEM_GL_POST_014');
742
743 x_num_msg := 1;
744 x_exec_mode_lock_exists := 'T';
745
746 RETURN;
747
748 END IF;
749
750 -- ---------------------------------------------------------------------
751 -- Find out if there are any previous snapshot loads successfully
752 -- completed for the given ledger, dataset, period, and rule.
753 -- ---------------------------------------------------------------------
754
755 SELECT COUNT(*)
756 INTO v_row_count
757 FROM fem_pl_requests r,
758 fem_pl_object_executions o
759 WHERE r.cal_period_id = p_cal_period_id
760 AND r.ledger_id = p_ledger_id
761 AND r.output_dataset_code = p_dataset_code
762 AND r.exec_mode_code = 'S'
763 AND r.exec_status_code = 'SUCCESS'
764 AND o.request_id = r.request_id
765 AND o.object_id = p_object_id;
766
767 IF (p_exec_mode = 'S') AND (v_row_count > 0) THEN
768
769 -- ------------------------------------------------------------------
770 -- The snapshot load has already been run for this Ledger, Dataset,
771 -- and period.
772 -- ------------------------------------------------------------------
773
774 FEM_ENGINES_PKG.Tech_Message
775 (p_severity => fnd_log.level_exception,
776 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
777 'xgl_exec_mode_lock_exists.ssar',
778 p_app_name => 'FEM',
779 p_msg_name => 'FEM_GL_POST_012');
780
781 FEM_ENGINES_PKG.Put_Message
782 (p_app_name => 'FEM',
783 p_msg_name => 'FEM_GL_POST_012');
784
785 x_num_msg := 1;
786 x_exec_mode_lock_exists := 'T';
787
788 RETURN;
789
790 ELSIF (p_exec_mode in ('I', 'E')) AND (v_row_count = 0) THEN
791
792 -- ------------------------------------------------------------------
793 -- The snapshot load has not been run yet for this Ledger, Dataset,
794 -- and period.
795 -- ------------------------------------------------------------------
796
797 FEM_ENGINES_PKG.Tech_Message
798 (p_severity => fnd_log.level_exception,
799 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
800 'xgl_exec_mode_lock_exists.ssnry',
801 p_app_name => 'FEM',
802 p_msg_name => 'FEM_GL_POST_013');
803
804 FEM_ENGINES_PKG.Put_Message
805 (p_app_name => 'FEM',
806 p_msg_name => 'FEM_GL_POST_013');
807
808 x_num_msg := 1;
809 x_exec_mode_lock_exists := 'T';
810
811 RETURN;
812
813 END IF;
814
815 x_num_msg := 0;
816 x_exec_mode_lock_exists := 'F';
817
818 FEM_ENGINES_PKG.Tech_Message
819 (p_severity => fnd_log.level_procedure,
820 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
821 'xgl_exec_mode_lock_exists.end',
822 p_msg_text => 'END');
823
824 END XGL_Exec_Mode_Lock_Exists;
825 -- =========================================================================
826
827
828 -- =========================================================================
829 PROCEDURE Snapshot_Period_Lock_Exists
830 (p_cal_period_id IN NUMBER,
831 p_ledger_id IN NUMBER,
832 p_dataset_code IN NUMBER,
833 x_ss_per_lock_exists OUT NOCOPY VARCHAR2,
834 x_num_msg OUT NOCOPY NUMBER) IS
835 -- =========================================================================
836 -- Purpose
837 -- For snapshot loads, make sure that the targeted period will be the
838 -- latest loaded period. This is done by checking FEM_PL_REQUESTS for
839 -- any successful executions for periods after the current period, for
840 -- the given ledger and dataset (it isn't necessary to check the current
841 -- period; that was already done in XGL_Exec_Mode_Lock_Exists).
842 -- History
843 -- 12-22-03 G Hall Created
844 -- 11-22-04 G Hall This procedure is now obsolete, per bu# 3922507.
845 -- The check performed by this procedure was only
846 -- needed if roll up and/or roll forward were
847 -- implemented for XGL; they have not been and
848 -- probably won't be. However, this procedure can
849 -- be saved for a while in case that plan changes.
850 -- Arguments
851 -- p_cal_period_id The Cal Period ID value passed to the engine.
852 -- p_ledger_id The Ledger ID value passed to the engine.
853 -- p_dataset_code The Dataset Code value passed to the engine.
854 -- x_ss_per_lock_exists Returns 'T' or 'F'.
855 -- x_num_msg Returns the number of end-user messages put onto
856 -- the FND message stack by this procedure.
857 -- Notes
858 -- Called by FEM_PL_INCR.Exec_Lock_Exists.
859 -- This procedure can also be used for Incremental loads to identify
860 -- if the period is a back-post.
861 -- =========================================================================
862
863 v_cal_per_dim_id fem_dimensions_b.dimension_id%TYPE;
864 v_API_return_code NUMBER;
865 v_dim_attr_id fem_dim_attributes_b.attribute_id%TYPE;
866 v_dim_attr_ver_id fem_dim_attr_versions_b.version_id%TYPE;
867 v_cal_per_end_date fem_cal_periods_attr.date_assign_value%TYPE;
868 v_row_count NUMBER;
869 v_dim_name1 fem_dimensions_tl.dimension_name%TYPE;
870 v_attr_name fem_dim_attributes_tl.attribute_name%TYPE;
871
872 BEGIN
873
874 FEM_ENGINES_PKG.Tech_Message
875 (p_severity => fnd_log.level_procedure,
876 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
877 'snapshot_period_lock_exists.begin',
878 p_msg_text => 'BEGIN');
879
880 -- -----------------------------------------------------------------------
881 -- Look up the Attribute ID and the Version ID for the CAL_PERIOD_END_DATE
882 -- attribute, and look up its value for the current Cal Period ID.
883 -- -----------------------------------------------------------------------
884
885 SELECT dimension_id
886 INTO v_cal_per_dim_id
887 FROM fem_dimensions_b
888 WHERE dimension_varchar_label = 'CAL_PERIOD';
889
890 fem_dimension_util_pkg.get_dim_attr_id_ver_id
891 (x_err_code => v_API_return_code,
892 x_attr_id => v_dim_attr_id,
893 x_ver_id => v_dim_attr_ver_id,
894 p_dim_id => v_cal_per_dim_id,
895 p_attr_label => 'CAL_PERIOD_END_DATE');
896
897 IF v_API_return_code > 0 THEN
898 RAISE NO_DATA_FOUND;
899 END IF;
900
901 SELECT date_assign_value
902 INTO v_cal_per_end_date
903 FROM fem_cal_periods_attr
904 WHERE attribute_id = v_dim_attr_id
905 AND version_id = v_dim_attr_ver_id
906 AND cal_period_id = p_cal_period_id;
907
908 FEM_ENGINES_PKG.Tech_Message
909 (p_severity => fnd_log.level_statement,
910 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
911 'snapshot_period_lock_exists.v_cal_per_end_date',
912 p_app_name => 'FEM',
913 p_msg_name => 'FEM_GL_POST_204',
914 p_token1 => 'VAR_NAME',
915 p_value1 => 'v_cal_per_end_date',
916 p_token2 => 'VAR_VAL',
917 p_value2 => TO_CHAR(v_cal_per_end_date, 'DD-MON-YYYY'));
918
919 -- ----------------------------------------------------------------------
920 -- See if there are has been any processing for any later period.
921 -- ----------------------------------------------------------------------
922
923 -- Note: This SQL is incorrect; it needs to check only XGL requests.
924 --
925 -- SELECT count(*)
926 -- INTO v_row_count
927 -- FROM fem_pl_requests r,
928 -- fem_cal_periods_attr a
929 -- WHERE r.ledger_id = p_ledger_id
930 -- AND r.output_dataset_code = p_dataset_code
931 -- AND r.exec_status_code = 'SUCCESS'
932 -- AND a.attribute_id = v_dim_attr_id
933 -- AND a.version_id = v_dim_attr_ver_id
934 -- AND a.cal_period_id = r.cal_period_id
935 -- AND a.date_assign_value > v_cal_per_end_date;
936 --
937 -- Note: The following SQL should be correct, however it has only been
938 -- superficially tested, since this procedure is currently obsolete.
939 -- If this procedure is reinstated, this section needs to be fully
940 -- tested.
941 --
942 SELECT count(*)
943 INTO v_row_count
944 FROM fem_pl_requests r,
945 fem_pl_object_executions e,
946 fem_object_catalog_b o,
947 fem_cal_periods_attr a
948 WHERE r.ledger_id = p_ledger_id
949 AND r.output_dataset_code = p_dataset_code
950 AND r.exec_status_code = 'SUCCESS'
951 AND e.request_id = r.request_id
952 AND o.object_id = e.object_id
953 AND o.object_type_code = 'XGL_INTEGRATION'
954 AND a.attribute_id = v_dim_attr_id
955 AND a.version_id = v_dim_attr_ver_id
956 AND a.cal_period_id = r.cal_period_id
957 AND a.date_assign_value > v_cal_per_end_date;
958
959 IF v_row_count > 0 THEN
960
961 -- This snapshot load is being run out of order.
962
963 FEM_ENGINES_PKG.Tech_Message
964 (p_severity => fnd_log.level_exception,
965 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
966 'snapshot_period_lock_exists.ssple',
967 p_app_name => 'FEM',
968 p_msg_name => 'FEM_GL_POST_015');
969
970 FEM_ENGINES_PKG.Put_Message
971 (p_app_name => 'FEM',
972 p_msg_name => 'FEM_GL_POST_015');
973
974 x_num_msg := 1;
975 x_ss_per_lock_exists := 'T';
976
977 RETURN;
978
979 END IF;
980
981 x_num_msg := 0;
982 x_ss_per_lock_exists := 'F';
983
984 FEM_ENGINES_PKG.Tech_Message
985 (p_severity => fnd_log.level_procedure,
986 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
987 'snapshot_period_lock_exists.end',
988 p_msg_text => 'END');
989
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 -- -------------------------------------------------------------------
993 -- This exception can occur in retrieving the Cal Period End Date
994 -- attribute. Log a message indicating Invalid Calendar Period
995 -- because the Cal Period End Date Attribute is not set.
996 -- -------------------------------------------------------------------
997
998 v_dim_name1 := FEM_DIMENSION_UTIL_PKG.Get_Dimension_Name
999 (p_dim_id => v_cal_per_dim_id);
1000
1001 IF v_dim_name1 IS NULL THEN
1002 v_dim_name1 := 'Calendar Period';
1003 END IF;
1004
1005 v_attr_name := FEM_DIMENSION_UTIL_PKG.Get_Dim_Attr_Name
1006 (p_dim_id => v_cal_per_dim_id,
1007 p_attr_label => 'CAL_PERIOD_END_DATE');
1008
1009 IF v_attr_name IS NULL THEN
1010 v_attr_name := 'Calendar Period End Date';
1011 END IF;
1012
1013 FEM_ENGINES_PKG.Tech_Message
1014 (p_severity => fnd_log.level_exception,
1015 p_module => 'fem.plsql.fem_pl_incr_pkg.' ||
1016 'snapshot_period_lock_exists.invalid_cal_period_id',
1017 p_app_name => 'FEM',
1018 p_msg_name => 'FEM_GL_POST_003',
1019 p_token1 => 'DIMENSION_NAME1',
1020 p_value1 => v_dim_name1,
1021 p_token2 => 'DIMENSION_NAME2',
1022 p_value2 => v_dim_name1,
1023 p_token3 => 'ATTRIBUTE_NAME',
1024 p_value3 => v_attr_name);
1025
1026 FEM_ENGINES_PKG.Put_Message
1027 (p_app_name => 'FEM',
1028 p_msg_name => 'FEM_GL_POST_003',
1029 p_token1 => 'DIMENSION_NAME1',
1030 p_value1 => v_dim_name1,
1031 p_token2 => 'DIMENSION_NAME2',
1032 p_value2 => v_dim_name1,
1033 p_token3 => 'ATTRIBUTE_NAME',
1034 p_value3 => v_attr_name);
1035
1036 x_num_msg := 1;
1037 x_ss_per_lock_exists := 'T';
1038
1039 RAISE;
1040
1041 END Snapshot_Period_Lock_Exists;
1042 -- =======================================================================
1043
1044
1045 -- =========================================================================
1046 PROCEDURE Fact_Obj_Exec_Lock_Exists
1047 (p_calling_context IN VARCHAR2 DEFAULT 'ENGINE',
1048 p_object_id IN NUMBER,
1049 p_obj_def_id IN NUMBER,
1050 p_cal_period_id IN NUMBER,
1051 p_ledger_id IN NUMBER,
1052 p_dataset_code IN NUMBER,
1053 p_source_system_code IN NUMBER,
1054 p_table_name IN VARCHAR2,
1055 x_obj_exec_lock_exists OUT NOCOPY VARCHAR2,
1056 x_exec_state OUT NOCOPY VARCHAR2,
1057 x_prev_request_id OUT NOCOPY NUMBER,
1058 x_num_msg OUT NOCOPY NUMBER) IS
1059 -- =========================================================================
1060 -- Purpose
1061 -- Performs process validations specific to Fact Data Loader
1062 -- to ensure that there are no process execution locks.
1063 -- History
1064 -- 02-21-06 gcheng Created
1065 -- Arguments
1066 -- p_calling_context 'ENGINE' (default) or 'UI'.
1067 -- p_object_id The Object ID identifying the rule being executed.
1068 -- p_obj_def_id The Object_Definition_ID identifying the version
1069 -- of the rule being executed.
1070 -- p_cal_period_id The Cal Period ID value passed to the engine.
1071 -- p_ledger_id The Ledger ID value passed to the engine.
1072 -- p_dataset_code The Dataset Code value passed to the engine.
1073 -- p_source_system_code The Source System Code value passed to the engine.
1074 -- p_table_name The Table that the rule is writing to.
1075 -- x_obj_lock_exists Returns 'T' or 'F'.
1076 -- x_exec_state Return values are 'NORMAL', 'RERUN', 'RESTART'
1077 -- x_prev_request_id Passes back the Request ID of the previous
1078 -- execution for the given ledger, dataset, period,
1079 -- system, table and object when x_exec_state = 'RERUN'.
1080 -- x_num_msg Returns the number of end-user messages put onto
1081 -- the FND message stack by this procedure.
1082 -- Notes
1083 -- Called by FEM_PL_INCR_PKG.Exec_Lock_Exists.
1084 -- =========================================================================
1085
1086 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1087 'fem.plsql.fem_pl_incr_pkg.fact_obj_exec_lock_exists';
1088
1089 v_prev_event_order fem_pl_object_executions.event_order%TYPE;
1090 v_prev_exec_status_cd fem_pl_object_executions.exec_status_code%TYPE;
1091 v_prev_request_id fem_pl_object_executions.request_id%TYPE;
1092
1093 v_current_request_id NUMBER;
1094
1095 v_msg_count NUMBER;
1096 v_msg_data VARCHAR2(512);
1097 v_return_status VARCHAR2(1);
1098
1099 CURSOR c1 IS
1100 SELECT r.request_id
1101 FROM fem_pl_requests r,
1102 fem_pl_object_executions o
1103 WHERE r.cal_period_id = p_cal_period_id
1104 AND r.ledger_id = p_ledger_id
1105 AND r.output_dataset_code = p_dataset_code
1106 AND r.source_system_code = p_source_system_code
1107 AND r.table_name = p_table_name
1108 AND o.request_id = r.request_id
1109 AND o.object_id = p_object_id
1110 AND o.exec_status_code = G_RUNNING;
1111
1112 BEGIN
1113
1114 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1115 FEM_ENGINES_PKG.Tech_Message
1116 (p_severity => fnd_log.level_procedure,
1117 p_module => C_MODULE || '.begin',
1118 p_msg_text => 'BEGIN');
1119 END IF;
1120
1121 -- Initialize vars:
1122 x_obj_exec_lock_exists := NULL;
1123 x_num_msg := 0;
1124
1125 -- ----------------------------------------------------------------------
1126 -- Reset failed executions that are left in 'RUNNING' status to their
1127 -- correct error status.
1128 -- ----------------------------------------------------------------------
1129
1130 FOR reset IN c1 LOOP
1131
1132 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1133 FEM_ENGINES_PKG.Tech_Message
1134 (p_severity => fnd_log.level_statement,
1135 p_module => C_MODULE || '.reset_request_id',
1136 p_app_name => 'FEM',
1137 p_msg_name => 'FEM_GL_POST_204',
1138 p_token1 => 'VAR_NAME',
1139 p_value1 => 'reset.request_id',
1140 p_token2 => 'VAR_VAL',
1141 p_value2 => reset.request_id);
1142 END IF;
1143
1144 FEM_PL_PKG.set_exec_state
1145 (p_api_version => 1.0,
1146 p_commit => fnd_api.g_false,
1147 p_request_id => reset.request_id,
1148 p_object_id => p_object_id,
1149 x_msg_count => v_msg_count,
1150 x_msg_data => v_msg_data,
1151 x_return_status => v_return_status);
1152
1153 END LOOP;
1154
1155 -- ----------------------------------------------------------------------
1156 -- Check for execution locks and determine execution state based on the
1157 -- EXEC_STATUS_CODE of the most recent execution for the given ledger,
1158 -- dataset, period, and object/object version.
1159 -- ----------------------------------------------------------------------
1160
1161 SELECT MAX(e.event_order)
1162 INTO v_prev_event_order
1163 FROM fem_pl_requests r,
1164 fem_pl_object_executions e
1165 WHERE r.ledger_id = p_ledger_id
1166 AND r.cal_period_id = p_cal_period_id
1167 AND r.output_dataset_code = p_dataset_code
1168 AND r.source_system_code = p_source_system_code
1169 AND r.table_name = p_table_name
1170 AND e.request_id = r.request_id
1171 AND e.object_id = p_object_id
1172 AND e.exec_object_definition_id = p_obj_def_id;
1173
1174 BEGIN
1175 SELECT exec_status_code, request_id
1176 INTO v_prev_exec_status_cd, v_prev_request_id
1177 FROM fem_pl_object_executions
1178 WHERE event_order = v_prev_event_order;
1179 EXCEPTION
1180 WHEN no_data_found THEN
1181 v_prev_exec_status_cd := NULL;
1182 END;
1183
1184 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1185 FEM_ENGINES_PKG.Tech_Message
1186 (p_severity => fnd_log.level_procedure,
1187 p_module => C_MODULE || '.prev_exec_status',
1188 p_msg_text => 'v_prev_exec_status_cd: ' || v_prev_exec_status_cd);
1189 END IF;
1190
1191 IF v_prev_exec_status_cd IS NULL THEN
1192
1193 -- ----------------------------------------------------------------
1194 -- There is no lock, and execution state is NORMAL. This will be
1195 -- the case for the first run of each period for the given ledger
1196 -- and dataset, i.e. the snapshot load.
1197 -- ----------------------------------------------------------------
1198
1199 x_obj_exec_lock_exists := 'F';
1200 x_exec_state := 'NORMAL';
1201
1202 ELSIF v_prev_exec_status_cd = 'SUCCESS' THEN
1203
1204 -- -------------------------------------------------------------------
1205 -- No lock exists and execution state is NORMAL
1206 -- -------------------------------------------------------------------
1207
1208 x_obj_exec_lock_exists := 'F';
1209 x_exec_state := 'NORMAL';
1210
1211 ELSIF v_prev_exec_status_cd IN ('CANCELLED_RERUN', 'ERROR_RERUN') THEN
1212
1213 -- -------------------------------------------------------------------
1214 -- No lock exists and execution state is RERUN
1215 -- -------------------------------------------------------------------
1216
1217 x_obj_exec_lock_exists := 'F';
1218 x_exec_state := 'RERUN';
1219 x_prev_request_id := v_prev_request_id;
1220
1221 ELSIF v_prev_exec_status_cd IN ('CANCELLED_UNDO', 'ERROR_UNDO') THEN
1222
1223 -- -------------------------------------------------------------------
1224 -- Exec Undo Lock:
1225 -- A lock exists until the previous execution is undone
1226 -- -------------------------------------------------------------------
1227
1228 x_obj_exec_lock_exists := 'T';
1229
1230 ELSIF v_prev_exec_status_cd = 'RUNNING' THEN
1231
1232 -- -------------------------------------------------------------------
1233 -- Determine whether this is a restart or whether another instance is
1234 -- still running. This comparison differs depending on the calling
1235 -- context. The "restart" execution mode is impossible from the UI
1236 -- because there is no concurrent manager context -- The UI is only
1237 -- checking if there is a lock prior to starting a concurrent request.
1238 -- So a RUNNING status for the previous execution can only mean that
1239 -- the previous execution is still running.
1240 -- -------------------------------------------------------------------
1241
1242 IF p_calling_context = 'UI' THEN
1243
1244 -- ----------------------------------------------------------------
1245 -- Object Already Running:
1246 -- A lock exists because another instance of the current object
1247 -- version is still running for the given ledger, dataset, and
1248 -- period.
1249 -- ----------------------------------------------------------------
1250
1251 x_obj_exec_lock_exists := 'T';
1252
1253 ELSE
1254
1255 -- ----------------------------------------------------------------
1256 -- p_calling_context must be 'ENGINE'
1257 -- ----------------------------------------------------------------
1258
1259 v_current_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1260
1261 IF v_current_request_id = v_prev_request_id THEN
1262
1263 -- -------------------------------------------------------------
1264 -- No lock exists, execution state is RESTART
1265 -- -------------------------------------------------------------
1266
1267 x_obj_exec_lock_exists := 'F';
1268 x_exec_state := 'RESTART';
1269
1270 ELSE
1271
1272 -- -------------------------------------------------------------
1273 -- Object Already Running:
1274 -- A lock exists because another instance of the current object
1275 -- version is still running for the given ledger, dataset, and
1276 -- period.
1277 -- -------------------------------------------------------------
1278
1279 x_obj_exec_lock_exists := 'T';
1280
1281 END IF; -- v_current_request_id = v_prev_request_id
1282
1283 END IF; -- p_calling_context = 'UI'
1284
1285 END IF; -- v_prev_exec_status_cd = 'SUCCESS'
1286
1287 IF nvl(x_obj_exec_lock_exists,'T') = 'T' THEN
1288 IF x_obj_exec_lock_exists IS NULL THEN
1289 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1290 FEM_ENGINES_PKG.Tech_Message
1291 (p_severity => fnd_log.level_exception,
1292 p_module => C_MODULE || '.invalid_prev_exec_status',
1293 p_msg_text => 'Invalid Exec Status for previous execution: ' ||
1294 v_prev_exec_status_cd ||
1295 '. Previous Request ID: ' || TO_CHAR(v_prev_request_id));
1296 END IF;
1297
1298 x_obj_exec_lock_exists := 'T';
1299 END IF;
1300
1301 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1302 FEM_ENGINES_PKG.Tech_Message
1303 (p_severity => fnd_log.level_exception,
1304 p_module => C_MODULE || '.lock_exists',
1305 p_app_name => 'FEM',
1306 p_msg_name => 'FEM_PL_RESULTS_EXIST_ERR');
1307 END IF;
1308
1309 FEM_ENGINES_PKG.Put_Message
1310 (p_app_name => 'FEM',
1311 p_msg_name => 'FEM_PL_RESULTS_EXIST_ERR');
1312
1313 x_num_msg := 1;
1314 x_exec_state := NULL;
1315
1316 ELSE
1317 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1318 FEM_ENGINES_PKG.Tech_Message
1319 (p_severity => fnd_log.level_procedure,
1320 p_module => C_MODULE || '.exec_state',
1321 p_msg_text => 'x_exec_state = ' || x_exec_state);
1322 END IF;
1323 END IF;
1324
1325 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1326 FEM_ENGINES_PKG.Tech_Message
1327 (p_severity => fnd_log.level_procedure,
1328 p_module => C_MODULE || '.end',
1329 p_msg_text => 'END');
1330 END IF;
1331
1332 END Fact_Obj_Exec_Lock_Exists;
1333 -- =========================================================================
1334
1335
1336 -- =========================================================================
1337 PROCEDURE Fact_Exec_Mode_Lock_Exists
1338 (p_cal_period_id IN NUMBER,
1339 p_ledger_id IN NUMBER,
1340 p_dataset_code IN NUMBER,
1341 p_source_system_code IN NUMBER,
1342 p_table_name IN VARCHAR2,
1343 p_object_id IN NUMBER,
1344 p_exec_mode IN VARCHAR2,
1345 x_exec_mode_lock_exists OUT NOCOPY VARCHAR2,
1346 x_num_msg OUT NOCOPY NUMBER) IS
1347 -- =========================================================================
1348 -- Purpose
1349 -- Ensure that exactly one snapshot load is run first, before any
1350 -- replacement or error reprocessing loads, for each ledger, dataset,
1351 -- period, source system and table.
1352 -- History
1353 -- 02-21-06 gcheng Created
1354 -- Arguments
1355 -- p_cal_period_id The Cal Period ID value passed to the engine.
1356 -- p_ledger_id The Ledger ID value passed to the engine.
1357 -- p_dataset_code The Dataset Code value passed to the engine.
1358 -- p_source_system_code The Source System Code value passed to the engine.
1359 -- p_table_name The Table that the rule is writing to.
1360 -- p_object_id The Object ID identifying the rule being
1361 -- executed.
1362 -- p_exec_mode 'S' (snapshot), 'I', (incremental),
1363 -- 'E' (error reprocessing).
1364 -- x_exec_mode_lock_exists Returns 'T' or 'F'.
1365 -- x_num_msg Returns the number of end-user messages put onto
1366 -- the FND message stack by this procedure.
1367 -- Logic
1368 -- For a given ledger, dataset, period, system, and table,
1369 -- the Fact Data Loader rule can only run in Snapshot (S) mode
1370 -- if no data has been written out to the given parameter set.
1371 -- Also, the Fact Data Loader can only run in Error Reprocessing (E)
1372 -- or Replacement (R) mode once Snapshot mode was run once,
1373 -- regardless if that Snapshot run wrote any data to the table.
1374 -- Notes
1375 -- Called by FEM_PL_INCR_PKG.Exec_Lock_Exists.
1376 -- =========================================================================
1377
1378 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1379 'fem.plsql.fem_pl_incr_pkg.fact_exec_mode_lock_exists';
1380
1381 v_row_count NUMBER;
1382
1383 BEGIN
1384
1385 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1386 FEM_ENGINES_PKG.Tech_Message
1387 (p_severity => fnd_log.level_procedure,
1388 p_module => C_MODULE || '.begin',
1389 p_msg_text => 'BEGIN');
1390 END IF;
1391
1392 IF p_exec_mode NOT IN ('S', 'R', 'E') THEN
1393
1394 -- Execution mode is invalid
1395
1396 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1397 FEM_ENGINES_PKG.Tech_Message
1398 (p_severity => fnd_log.level_exception,
1399 p_module => C_MODULE || '.inv_exec_mode',
1400 p_app_name => 'FEM',
1401 p_msg_name => 'FEM_SD_LDR_INV_EXEC_MODE');
1402 END IF;
1403
1404 FEM_ENGINES_PKG.Put_Message
1405 (p_app_name => 'FEM',
1406 p_msg_name => 'FEM_SD_LDR_INV_EXEC_MODE');
1407
1408 x_num_msg := 1;
1409 x_exec_mode_lock_exists := 'T';
1410
1411 RETURN;
1412
1413 END IF;
1414
1415
1416 -- ---------------------------------------------------------------------
1417 -- A Snapshot load can only be run if no previous loads have already
1418 -- posted data for the given ledger, dataset, period, system and table.
1419 -- ---------------------------------------------------------------------
1420
1421 IF p_exec_mode = G_SNAPSHOT THEN
1422
1423 SELECT COUNT(*)
1424 INTO v_row_count
1425 FROM fem_pl_requests r,
1426 fem_pl_object_executions o,
1427 fem_pl_tables t
1428 WHERE r.cal_period_id = p_cal_period_id
1429 AND r.ledger_id = p_ledger_id
1430 AND r.output_dataset_code = p_dataset_code
1431 AND r.source_system_code = p_source_system_code
1432 AND r.table_name = p_table_name
1433 AND t.request_id = o.request_id
1434 AND t.object_id = o.object_id
1435 AND t.num_of_output_rows > 0
1436 AND o.request_id = r.request_id
1437 AND o.object_id = p_object_id;
1438
1439 IF v_row_count > 0 THEN
1440
1441 -- ------------------------------------------------------------------
1442 -- A load has already populated data for this Ledger, Dataset,
1443 -- Period, System and Table.
1444 -- ------------------------------------------------------------------
1445
1446 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1447 FEM_ENGINES_PKG.Tech_Message
1448 (p_severity => fnd_log.level_exception,
1449 p_module => C_MODULE || '.data_loaded',
1450 p_app_name => 'FEM',
1451 p_msg_name => 'FEM_PL_CANNOT_SNAPSHOT');
1452 END IF;
1453
1454 FEM_ENGINES_PKG.Put_Message
1455 (p_app_name => 'FEM',
1456 p_msg_name => 'FEM_PL_CANNOT_SNAPSHOT');
1457
1458 x_num_msg := 1;
1459 x_exec_mode_lock_exists := 'T';
1460
1461 RETURN;
1462
1463 END IF;
1464
1465 END IF;
1466
1467 -- ---------------------------------------------------------------------
1468 -- The loader cannot run in Replacement or Error Reprocessing mode until
1469 -- it has run in Snapshot mode for a ledger, dataset, period, system
1470 -- and table.
1471 -- ---------------------------------------------------------------------
1472
1473 IF p_exec_mode IN (G_REPLACEMENT, G_ERROR_REPROCESS) THEN
1474
1475 SELECT COUNT(*)
1476 INTO v_row_count
1477 FROM fem_pl_requests r,
1478 fem_pl_object_executions o
1479 WHERE r.cal_period_id = p_cal_period_id
1480 AND r.ledger_id = p_ledger_id
1481 AND r.output_dataset_code = p_dataset_code
1482 AND r.source_system_code = p_source_system_code
1483 AND r.table_name = p_table_name
1484 AND r.exec_mode_code = G_SNAPSHOT
1485 AND o.request_id = r.request_id
1486 AND o.object_id = p_object_id;
1487
1488 IF v_row_count = 0 THEN
1489
1490 -- ------------------------------------------------------------------
1491 -- A load has already been populated data for this Ledger, Dataset,
1492 -- Period, System and Table.
1493 -- ------------------------------------------------------------------
1494
1495 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1496 FEM_ENGINES_PKG.Tech_Message
1497 (p_severity => fnd_log.level_exception,
1498 p_module => C_MODULE || '.data_loaded',
1499 p_app_name => 'FEM',
1500 p_msg_name => 'FEM_PL_RUN_SNAPSHOT_FIRST');
1501 END IF;
1502
1503 FEM_ENGINES_PKG.Put_Message
1504 (p_app_name => 'FEM',
1505 p_msg_name => 'FEM_PL_RUN_SNAPSHOT_FIRST');
1506
1507 x_num_msg := 1;
1508 x_exec_mode_lock_exists := 'T';
1509
1510 RETURN;
1511
1512 END IF;
1513
1514 END IF;
1515
1516 x_num_msg := 0;
1517 x_exec_mode_lock_exists := 'F';
1518
1519
1520 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1521 FEM_ENGINES_PKG.Tech_Message
1522 (p_severity => fnd_log.level_procedure,
1523 p_module => C_MODULE || '.end',
1524 p_msg_text => 'END');
1525 END IF;
1526
1527 END Fact_Exec_Mode_Lock_Exists;
1528 -- =========================================================================
1529
1530
1531 END FEM_PL_INCR_PKG;
[Home] [Help]