[Home] [Help]
PACKAGE BODY: APPS.FEM_DS_WHERE_CLAUSE_GENERATOR
Source
1 package body FEM_DS_WHERE_CLAUSE_GENERATOR AS
2 --$Header: FEMDSWGB.pls 120.3.12010000.2 2008/10/10 22:25:55 huli ship $
3
4 z_Dup_Entries_Tab Skipped_Data_List_Entries_Tab;
5 z_Dup_Entries_Ctr BINARY_INTEGER := 0;
6
7 z_No_Eff_Cal_Entries_Tab Skipped_Data_List_Entries_Tab;
8 z_No_Eff_Cal_Entries_Ctr BINARY_INTEGER := 0;
9
10
11 G_LOG_STATEMENT CONSTANT NUMBER := fnd_log.level_statement;
12 G_LOG_PROCEDURE CONSTANT NUMBER := fnd_log.level_procedure;
13 G_LOG_EVENT CONSTANT NUMBER := fnd_log.level_event;
14 G_LOG_EXCEPTION CONSTANT NUMBER := fnd_log.level_exception;
15 G_LOG_ERROR CONSTANT NUMBER := fnd_log.level_error;
16 G_LOG_UNEXPECTED CONSTANT NUMBER := fnd_log.level_unexpected;
17 -- 000000000111111111122222222223333333333444444444455555555556
18 -- 123456789012345678901234567890123456789012345678901234567890
19 G_APP_NAME CONSTANT VARCHAR2(4) := 'FEM';
20 G_PKG_NAME CONSTANT VARCHAR2(35) := 'FEM_DS_WHERE_CLAUSE_GENERATOR';
21 G_MODULE_NAME CONSTANT VARCHAR2(70) := 'fem.plsql.' || G_PKG_NAME || '.';
22
23 -- these variables must be kept in numerical order, with the lowest number indicating 'no error',
24 -- and each variable after indicating a higher level of error (with the highest being utterly fatal..
25 z_master_err_state NUMBER := FEM_UTILS.G_RSM_NO_ERR;
26
27 G_ERRMSG_NO_ODS_FOR_DSG CONSTANT varchar2(40) := 'FEM_DSWG_NO_ODS_FOR_DSG ';
28 G_ERRMSG_UNEXPECTED_SQLERROR CONSTANT varchar2(40) := 'FEM_RSM_UNEXPECTED_SQLERROR';
29 G_ERRMAC_ROUTINE_NAME CONSTANT varchar2(40) := 'ROUTINE_NAME';
30 G_ERRMAC_SQL_ERROR CONSTANT varchar2(40) := 'SQL_ERROR';
31
32
33
34 -- *******************************************************************************************
35 -- name reset_master_err_state
36 -- Function set master_err_state to no error on entry to master API calls.
37 -- Parameters
38 --
39 -- HISTORY
40 -- 22-Apr-2004 rjking created
41 --
42 -- *******************************************************************************************
43 PROCEDURE reset_master_err_state IS
44 BEGIN
45 z_master_err_state := FEM_UTILS.G_RSM_NO_ERR;
46 END reset_master_err_state;
47
48
49 ---------------------------------------------------------------
50 Function DoesTableRequireChaining(X_Table_Name IN VARCHAR2)RETURN VARCHAR2 IS
51
52 CURSOR c1 is
53 SELECT
54 tc.table_classification_code
55 FROM
56 fem_table_class_assignmt tc
57 WHERE
58 tc.table_name = X_Table_Name
59 and tc.table_classification_code = 'DATASET_IO_WCLAUSE';
60
61 l_Table_Classification_Code VARCHAR2(30) := 'X';
62
63
64 -- 1234567890123456789012345
65 l_api_name CONSTANT VARCHAR2(30) := 'DoesTableRequireChaining';
66 l_module_name VARCHAR2(70) := G_MODULE_NAME || l_api_name;
67 Begin
68 -- *******************************************************************************************
69 -- name DoesTableRequireChaining
70 -- Function Look up the table classification code for the table name
71 -- passed into this function..
72 --
73 -- Parameters
74 --
75 -- IN
76 -- X_Table_Name IN VARCHAR2
77 -- - Table name for lookup..
78 --
79 -- OUT
80 --
81 -- Returns
82 -- The table classification code.
83 --
84 -- HISTORY
85 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
86 --
87 -- *******************************************************************************************
88 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
89 p_module=> l_module_name,
90 p_msg_text=> 'ENTRY');
91
92 OPEN c1;
93 FETCH c1 into l_Table_Classification_Code;
94 CLOSE c1;
95
96 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
97 p_module=> l_module_name,
98 p_msg_text=> 'EXIT');
99
100 RETURN l_Table_Classification_Code;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 FEM_UTILS.set_master_err_state( z_master_err_state,
105 FEM_UTILS.G_RSM_FATAL_ERR,
106 G_APP_NAME,
107 G_ERRMSG_UNEXPECTED_SQLERROR,
108 G_ERRMAC_ROUTINE_NAME,
109 l_module_name,
110 NULL,
111 G_ERRMAC_SQL_ERROR,
112 SQLERRM);
113 End DoesTableRequireChaining;
114
115
116 Function No_Entry_For_IDS_ECP(p_Input_Dataset_Code IN NUMBER
117 ,p_Input_Period_ID IN NUMBER
118 ,p_List_B IN List_B
119 ,p_List_B_Ctr IN BINARY_INTEGER) RETURN BOOLEAN IS
120 -- 1 2
121 -- 1234567890123456789012345
122 l_api_name CONSTANT VARCHAR2(30) := 'No_Entry_For_IDS_ECP';
123 l_module_name VARCHAR2(70) := G_MODULE_NAME || l_api_name;
124 l_retval BOOLEAN := TRUE;
125 l_curr_list_b NUMBER;
126 Begin
127 -- *******************************************************************************************
128 -- name No_Entry_For_IDS_ECP
129 -- Function Search the p_List_B for an entry that matches the input dataset code and
130 -- input period ID passed into this routine.
131 --
132 --
133 -- Parameters
134 --
135 -- IN
136 -- p_Input_Dataset_Code IN NUMBER
137 -- - input dataset code to search for..
138 -- ,p_Input_Period_ID IN NUMBER
139 -- - input period id to search for..
140 -- ,p_List_B IN List_B
141 -- - The data structure to search
142 -- ,p_List_B_Ctr IN BINARY_INTEGER
143 -- - count of valid entries in the data structure p_List_B
144 -- OUT
145 --
146 -- Returns
147 -- FALSE if an entry was found matching p_Input_Dataset_Code and p_Input_Period_ID
148 -- TRUE if NO entry was found matching p_Input_Dataset_Code and p_Input_Period_ID
149 --
150 -- HISTORY
151 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
152 --
153 -- *******************************************************************************************
154 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
155 p_module=> l_module_name,
156 p_msg_text=> 'ENTRY');
157
158 -- look for a match in p_List_B
159 l_curr_list_b := 0;
160 WHILE (l_curr_list_b <= p_List_B_Ctr)
161 AND l_retval LOOP
162
163 If ( p_List_B(l_curr_list_b).X_Dataset_Code = p_Input_Dataset_Code
164 AND p_List_B(l_curr_list_b).X_Cal_Period_ID = p_Input_Period_ID ) then
165 -- if found..
166 l_retval := FALSE;
167 End If;
168
169 l_curr_list_b := l_curr_list_b + 1;
170 END LOOP;
171
172 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
173 p_module=> l_module_name,
174 p_msg_text=> 'EXIT');
175 RETURN l_retval;
176
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 -- if not found
180 RETURN TRUE;
181
182 WHEN OTHERS THEN
183 FEM_UTILS.set_master_err_state( z_master_err_state,
184 FEM_UTILS.G_RSM_FATAL_ERR,
185 G_APP_NAME,
186 G_ERRMSG_UNEXPECTED_SQLERROR,
187 G_ERRMAC_ROUTINE_NAME,
188 l_module_name,
189 NULL,
190 G_ERRMAC_SQL_ERROR,
191 SQLERRM);
192
193 End No_Entry_For_IDS_ECP;
194
195
196 Procedure Add_IDS_ECP_Entry_To_List_B
197 (p_Input_Dataset_Code IN NUMBER
198 ,p_Input_Calendar_Period_ID IN NUMBER
199 ,p_List_B IN OUT NOCOPY List_B
200 ,p_List_B_Ctr IN OUT NOCOPY BINARY_INTEGER) IS
201 -- 1 2
202 -- 1234567890123456789012345
203 l_api_name CONSTANT VARCHAR2(30) := 'Add_IDS_ECP_Entry_To_List_B';
204 l_module_name VARCHAR2(70) := G_MODULE_NAME || l_api_name;
205 Begin
206 -- *******************************************************************************************
207 -- name Add_IDS_ECP_Entry_To_List_B
208 -- Function as the name suggests.. add the current p_Input_Dataset_Code/p_Input_Calendar_Period_ID
209 -- tuple to p_List_B, and increment the count of valid entries.
210 --
211 --
212 --
213 -- Parameters
214 --
215 -- IN
216 -- p_Input_Dataset_Code IN NUMBER
217 -- - input data set code to add to p_List_B
218 -- p_Input_Calendar_Period_ID IN NUMBER
219 -- - input calendar period to add to p_List_B
220 --
221 -- OUT
222 -- x_Err_Code OUT NUMBER
223 -- - error code..
224 -- x_Err_Msg OUT VARCHAR2) IS
225 -- - error message.
226 --
227 -- IN OUT
228 -- p_List_B IN OUT NOCOPY List_B
229 -- - data structure receiving the tuple.
230 -- p_List_B_Ctr IN OUT NOCOPY BINARY_INTEGER
231 -- - count of valid elements in p_List_B.
232 --
233 --
234 -- HISTORY
235 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
236 --
237 -- *******************************************************************************************
238 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
239 p_module=> l_module_name,
240 p_msg_text=> 'ENTRY');
241
242 p_List_B(p_List_B_Ctr).X_DATASET_CODE := p_Input_Dataset_Code;
243 p_List_B(p_List_B_Ctr).X_CAL_PERIOD_ID := p_Input_Calendar_Period_ID;
244 p_List_B_Ctr := p_List_B_Ctr +1;
245
246 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
247 p_module=> l_module_name,
248 p_msg_text=> 'EXIT');
249 EXCEPTION
250 WHEN OTHERS THEN
251 FEM_UTILS.set_master_err_state( z_master_err_state,
252 FEM_UTILS.G_RSM_FATAL_ERR,
253 G_APP_NAME,
254 G_ERRMSG_UNEXPECTED_SQLERROR,
255 G_ERRMAC_ROUTINE_NAME,
256 l_module_name,
257 NULL,
258 G_ERRMAC_SQL_ERROR,
259 SQLERRM);
260
261 End Add_IDS_ECP_Entry_To_List_B;
262
263
264
265 Procedure Add_Missing_Entries
266 ( p_Input_Dataset_Name IN DEF_DATASET_NAME%TYPE
267 ,p_Abs_Cal_Period_Flag IN FEM_DS_INPUT_LISTS.ABSOLUTE_CAL_PERIOD_FLAG%TYPE
268 ,p_Abs_Cal_Period_Name IN DEF_CAL_PERIOD_NAME%TYPE
269 ,p_Rel_Dim_Grp_Name IN DEF_DIM_GRP_NAME%TYPE
270 ,p_Rel_Cal_Period_Offset IN DEF_REL_CAL_PERIOD_OFFSET%TYPE
271 ,p_Eff_Cal_Period_Name IN DEF_CAL_PERIOD_NAME%TYPE
272 ,p_Missing_Entry_Type IN VARCHAR2) IS
273
274 -- 1 2
275 -- 1234567890123456789012345
276 l_api_name CONSTANT VARCHAR2(30) := 'Add_Missing_Entries';
277 l_module_name VARCHAR2(70) := G_MODULE_NAME || l_api_name;
278 Begin
279 -- *******************************************************************************************
280 -- name Add_Missing_Entries
281 -- Function Add a missing entry to the correct data structure.
282 --
283 --
284 -- Parameters
285 --
286 -- IN
287 -- p_Input_Dataset_Name IN DEF_DATASET_NAME%TYPE
288 -- -
289 -- p_Abs_Cal_Period_Flag IN FEM_DS_INPUT_LISTS.ABSOLUTE_CAL_PERIOD_FLAG%TYPE
290 -- -
291 -- p_Abs_Cal_Period_Name IN DEF_CAL_PERIOD_NAME%TYPE
292 -- -
293 -- p_Rel_Dim_Grp_Name IN DEF_DIM_GRP_NAME%TYPE
294 -- -
295 -- p_Rel_Cal_Period_Offset IN DEF_REL_CAL_PERIOD_OFFSET%TYPE
296 -- -
297 -- p_Eff_Cal_Period_Name IN DEF_CAL_PERIOD_NAME%TYPE
298 -- -
299 -- p_Missing_Entry_Type IN VARCHAR2
300 -- -
301 --
302 -- OUT
303 -- x_Err_Code OUT NUMBER
304 -- -
305 -- x_Err_Msg OUT VARCHAR2
306 -- -
307 --
308 -- Returns
309 --
310 -- HISTORY
311 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
312 --
313 -- *******************************************************************************************
314 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
315 p_module=> l_module_name,
316 p_msg_text=> 'ENTRY');
317 If (p_Missing_Entry_Type = 'DUP_ENTRY') then
318
319 -- add it to the duplicate entries table..
320
321 z_Dup_Entries_Tab(z_Dup_Entries_Ctr).Input_Dataset_Name
322 := p_Input_Dataset_Name;
323 z_Dup_Entries_Tab(z_Dup_Entries_Ctr).Absolute_Cal_Period_Flag
324 := p_Abs_Cal_Period_Flag;
325 z_Dup_Entries_Tab(z_Dup_Entries_Ctr).Abs_Cal_Period_Name
326 := p_Abs_Cal_Period_Name;
327 z_Dup_Entries_Tab(z_Dup_Entries_Ctr).Relative_Dim_Grp_Name
328 := p_Rel_Dim_Grp_Name;
329 z_Dup_Entries_Tab(z_Dup_Entries_Ctr).Relative_Cal_Period_Offset
330 := p_Rel_Cal_Period_Offset;
331 z_Dup_Entries_Tab(z_Dup_Entries_Ctr).Eff_Cal_Period_Name
332 := p_Eff_Cal_Period_Name;
333 z_Dup_Entries_Ctr
334 := z_Dup_Entries_Ctr + 1;
335
336 Elsif (p_Missing_Entry_Type = 'NO_EFF_CAL_PER') then
337 -- add it to the 'we are missing a calendar period' table
338
339 z_No_Eff_Cal_Entries_Tab(z_No_Eff_Cal_Entries_Ctr).Input_Dataset_Name
340 := p_Input_Dataset_Name;
341 z_No_Eff_Cal_Entries_Tab(z_No_Eff_Cal_Entries_Ctr).Absolute_Cal_Period_Flag
342 := p_Abs_Cal_Period_Flag;
343 z_No_Eff_Cal_Entries_Tab(z_No_Eff_Cal_Entries_Ctr).Abs_Cal_Period_Name
344 := p_Abs_Cal_Period_Name;
345 z_No_Eff_Cal_Entries_Tab(z_No_Eff_Cal_Entries_Ctr).Relative_Dim_Grp_Name
346 := p_Rel_Dim_Grp_Name;
347 z_No_Eff_Cal_Entries_Tab(z_No_Eff_Cal_Entries_Ctr).Relative_Cal_Period_Offset
348 := p_Rel_Cal_Period_Offset;
349 z_No_Eff_Cal_Entries_Tab(z_No_Eff_Cal_Entries_Ctr).Eff_Cal_Period_Name
350 := p_Eff_Cal_Period_Name;
351 z_No_Eff_Cal_Entries_Ctr
352 := z_No_Eff_Cal_Entries_Ctr + 1;
353
354 End If;
355
356 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
357 p_module=> l_module_name,
358 p_msg_text=> 'EXIT');
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 FEM_UTILS.set_master_err_state( z_master_err_state,
363 FEM_UTILS.G_RSM_FATAL_ERR,
364 G_APP_NAME,
365 G_ERRMSG_UNEXPECTED_SQLERROR,
366 G_ERRMAC_ROUTINE_NAME,
367 l_module_name,
368 NULL,
369 G_ERRMAC_SQL_ERROR,
370 SQLERRM);
371 End Add_Missing_Entries;
372
373 Procedure GetDatasetGroupNameandFolder( p_DS_IO_Def_ID IN DEF_IODD_DEF_ID%TYPE
374 ,p_Dataset_Group_Name OUT NOCOPY DEF_OBJECT_NAME%TYPE
375 ,p_Dataset_Group_Folder_Name OUT NOCOPY DEF_FOLDER_NAME%TYPE) IS
376
377 l_IODD_Name DEF_OBJECT_NAME%TYPE;
378 l_Folder_Name DEF_FOLDER_NAME%TYPE;
379
380 cursor getIODDNameandFolder is
381 select
382 a.object_name
383 ,b.folder_name
384 from
385 fem_object_definition_b c
386 ,fem_object_catalog_vl a
387 ,fem_folders_vl b
388 where
389 c.object_definition_id = p_DS_IO_Def_ID
390 and c.object_id = a.object_id
391 and b.folder_id = a.folder_id;
392 -- 1 2
393 -- 1234567890123456789012345
394 l_api_name CONSTANT VARCHAR2(30) := 'GetDatasetGroupNameandFolder';
395 l_module_name VARCHAR2(75) := G_MODULE_NAME || l_api_name;
396 Begin
397 -- *******************************************************************************************
398 -- name GetDatasetGroupNameandFolder
399 -- Function retrieve the data set group name and the folder it is stored in based on the
400 -- definition ID passed to us.
401 --
402 --
403 -- Parameters
404 --
405 -- IN
406 -- p_DS_IO_Def_ID IN DEF_IODD_DEF_ID%TYPE
407 -- - The definition ID to translate to a group name/folder tuple.
408 --
409 -- OUT
410 -- p_Dataset_Group_Name OUT DEF_OBJECT_NAME%TYPE
411 -- - the group name found.
412 -- p_Dataset_Group_Folder_Name OUT DEF_FOLDER_NAME%TYPE) IS
413 -- - the folder name where the group name is stored.
414 --
415 -- Returns
416 --
417 -- HISTORY
418 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
419 --
420 -- *******************************************************************************************
421 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
422 p_module=> l_module_name,
423 p_msg_text=> 'ENTRY');
424 OPEN getIODDNameandFolder;
425 FETCH getIODDNameandFolder INTO
426 l_IODD_Name
427 ,l_Folder_Name;
428 CLOSE getIODDNameandFolder;
429
430 p_Dataset_Group_Name := l_IODD_Name;
431 p_Dataset_Group_Folder_Name := l_Folder_Name;
432
433 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
434 p_module=> l_module_name,
435 p_msg_text=> 'EXIT');
436 EXCEPTION
437 WHEN OTHERS THEN
438 FEM_UTILS.set_master_err_state( z_master_err_state,
439 FEM_UTILS.G_RSM_FATAL_ERR,
440 G_APP_NAME,
441 G_ERRMSG_UNEXPECTED_SQLERROR,
442 G_ERRMAC_ROUTINE_NAME,
443 l_module_name,
444 NULL,
445 G_ERRMAC_SQL_ERROR,
446 SQLERRM);
447 End GetDatasetGroupNameandFolder;
448
449 Function GetDimensionGroupName(p_Dim_Group_ID IN NUMBER) RETURN VARCHAR2 IS
450 cursor getName is
451 select
452 Dimension_Group_Name
453 from
454 Fem_Dimension_Grps_Vl
455 where Dimension_Group_ID = p_Dim_Group_ID;
456
457 l_Dim_Grp_Name DEF_DIM_GRP_NAME%TYPE;
458
459 -- 1 2
460 -- 1234567890123456789012345
461 l_api_name CONSTANT VARCHAR2(30) := 'GetDatasetName';
462 l_module_name VARCHAR2(75) := G_MODULE_NAME || l_api_name;
463 Begin
464 -- *******************************************************************************************
465 -- name GetDimensionGroupName
466 -- Function retrieve the dimension group name based on the
467 -- dimension group ID passed to us.
468 --
469 --
470 -- Parameters
471 --
472 -- IN
473 -- p_Dim_Group_ID IN NUMBER
474 -- - The dimension group ID to translate to a group name
475 --
476 -- OUT
477 --
478 -- Returns
479 -- The group name.
480 -- HISTORY
481 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
482 --
483 -- *******************************************************************************************
484 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
485 p_module=> l_module_name,
486 p_msg_text=> 'ENTRY');
487 OPEN getName;
488 Fetch getName into l_Dim_Grp_Name;
489 CLOSE getName;
490
491
492 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
493 p_module=> l_module_name,
494 p_msg_text=> 'EXIT');
495 RETURN l_Dim_Grp_Name;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 FEM_UTILS.set_master_err_state( z_master_err_state,
500 FEM_UTILS.G_RSM_FATAL_ERR,
501 G_APP_NAME,
502 G_ERRMSG_UNEXPECTED_SQLERROR,
503 G_ERRMAC_ROUTINE_NAME,
504 l_module_name,
505 NULL,
506 G_ERRMAC_SQL_ERROR,
507 SQLERRM);
508
509 End GetDimensionGroupName;
510
511 Function GetDatasetName(p_Dataset_Code IN NUMBER) RETURN VARCHAR2 IS
512 cursor getName is
513 select
514 Dataset_Name
515 from
516 Fem_Datasets_Vl
517 where Dataset_Code = p_Dataset_Code;
518
519 l_Dataset_Name DEF_DATASET_NAME%TYPE;
520
521 -- 1 2
522 -- 1234567890123456789012345
523 l_api_name CONSTANT VARCHAR2(30) := 'GetDatasetName';
524 l_module_name VARCHAR2(75) := G_MODULE_NAME || l_api_name;
525 Begin
526 -- *******************************************************************************************
527 -- name GetDatasetName
528 -- Function retrieve the data set name based on the
529 -- data set code passed to us.
530 --
531 --
532 -- Parameters
533 --
534 -- IN
535 -- p_Dataset_Code IN NUMBER
536 -- - The data set code to translate to a data set name
537 --
538 -- OUT
539 --
540 -- Returns
541 -- The data set name.
542 -- HISTORY
543 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
544 --
545 -- *******************************************************************************************
546 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
547 p_module=> l_module_name,
548 p_msg_text=> 'ENTRY');
549 OPEN getName;
550 Fetch getName into l_Dataset_Name;
551 CLOSE getName;
552
553 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
554 p_module=> l_module_name,
555 p_msg_text=> 'EXIT');
556
557 RETURN l_Dataset_Name;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 FEM_UTILS.set_master_err_state( z_master_err_state,
562 FEM_UTILS.G_RSM_FATAL_ERR,
563 G_APP_NAME,
564 G_ERRMSG_UNEXPECTED_SQLERROR,
565 G_ERRMAC_ROUTINE_NAME,
566 l_module_name,
567 NULL,
568 G_ERRMAC_SQL_ERROR,
569 SQLERRM);
570 End GetDatasetName;
571
572 Function GetCalPeriodName(p_Cal_Period_ID IN NUMBER) RETURN VARCHAR2 IS
573 cursor getName is
574 select
575 Cal_Period_Name
576 from
577 Fem_Cal_Periods_Vl
578 where cal_Period_id = p_Cal_Period_ID;
579
580 l_Cal_Period_Name DEF_CAL_PERIOD_NAME%TYPE;
581
582 -- 1 2
583 -- 1234567890123456789012345
584 l_api_name CONSTANT VARCHAR2(30) := 'GetCalPeriodName';
585 l_module_name VARCHAR2(75) := G_MODULE_NAME || l_api_name;
586
587 Begin
588 -- *******************************************************************************************
589 -- name GetCalPeriodName
590 -- Function retrieve the calendar period name based on the
591 -- calendar period id passed to us.
592 --
593 --
594 -- Parameters
595 --
596 -- IN
597 -- p_Cal_Period_ID IN NUMBER
598 -- - The calendar period id to translate to a calendar period name
599 --
600 -- OUT
601 --
602 -- Returns
603 -- The calendar period name.
604 -- HISTORY
605 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
606 --
607 -- *******************************************************************************************
608 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
609 p_module=> l_module_name,
610 p_msg_text=> 'ENTRY');
611 OPEN getName;
612 Fetch getName into l_Cal_Period_Name;
613 CLOSE getName;
614
615 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
616 p_module=> l_module_name,
617 p_msg_text=> 'EXIT');
618
619 RETURN l_Cal_Period_Name;
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 FEM_UTILS.set_master_err_state( z_master_err_state,
624 FEM_UTILS.G_RSM_FATAL_ERR,
625 G_APP_NAME,
626 G_ERRMSG_UNEXPECTED_SQLERROR,
627 G_ERRMAC_ROUTINE_NAME,
628 l_module_name,
629 NULL,
630 G_ERRMAC_SQL_ERROR,
631 SQLERRM);
632
633 End GetCalPeriodName;
634
635 Procedure Log_Missing_Entries(p_DS_IO_Def_ID IN NUMBER) IS
636 l_Dataset_Group_Name DEF_OBJECT_NAME%TYPE;
637 l_Dataset_Group_Folder_Name DEF_FOLDER_NAME%TYPE;
638
639 -- 1 2
640 -- 1234567890123456789012345
641 l_api_name CONSTANT VARCHAR2(30) := 'Log_Missing_Entries';
642 l_module_name VARCHAR2(75) := G_MODULE_NAME || l_api_name;
643
644 Begin
645 -- *******************************************************************************************
646 -- name Log_Missing_Entries
647 -- Function Generate a report of all duplicate entries and
648 -- all entries that are relative that are missing effective calendar periods.
649 --
650 -- Parameters
651 --
652 -- IN
653 -- p_DS_IO_Def_ID IN NUMBER
654 -- - The data set group Definition ID for the report..
655 --
656 -- OUT
657 --
658 -- HISTORY
659 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
660 --
661 -- *******************************************************************************************
662 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
663 p_module=> l_module_name,
664 p_msg_text=> 'ENTRY');
665 /*--Get Dataset Group Name and Folder Name--*/
666 GetDatasetGroupNameandFolder
667 (p_DS_IO_Def_ID => p_DS_IO_Def_ID
668 ,p_Dataset_Group_Name => l_Dataset_Group_Name
669 ,p_Dataset_Group_Folder_Name => l_Dataset_Group_Folder_Name);
670
671 fnd_file.put_line(FND_FILE.OUTPUT,'Dataset Group : '||l_Dataset_Group_Name);
672 fnd_file.put_line(FND_FILE.OUTPUT,'Dataset Group Folder : '||l_Dataset_Group_Folder_Name);
673
674 /*----Log Duplicate Entries-----*/
675 fnd_file.put_line(FND_FILE.OUTPUT,'Duplicate Entries');
676 fnd_file.put_line(FND_FILE.OUTPUT,'-----------------');
677 fnd_file.put_line(FND_FILE.OUTPUT,
678 RPAD('Input Dataset',40,' ')
679 || RPAD('Absolute Period',40,' ')
680 || RPAD('Relative Dimension Group',50,' ')
681 || RPAD('Relative Offset',5,' ')
682 || RPAD('Effective Period',40,' ')
683 );
684 fnd_file.put_line(FND_FILE.OUTPUT,
685 RPAD('-------------',40,' ')
686 || RPAD('---------------',40,' ')
687 || RPAD('------------------------',50,' ')
688 || RPAD('---------------',5,' ')
689 || RPAD('----------------',40,' ')
690 );
691 For i in 0..z_Dup_Entries_Ctr-1 LOOP
692 fnd_file.put_line(FND_FILE.OUTPUT,
693 RPAD(z_Dup_Entries_Tab(i).Input_Dataset_Name,40,' ')
694 || RPAD(z_Dup_Entries_Tab(i).Abs_Cal_Period_Name,40,' ')
695 || RPAD(z_Dup_Entries_Tab(i).Relative_Dim_Grp_Name,50,' ')
696 || RPAD(z_Dup_Entries_Tab(i).Relative_Cal_Period_Offset,5,' ')
697 || RPAD(z_Dup_Entries_Tab(i).Eff_Cal_Period_Name,40,' ')
698 );
699 End LOOP;
700
701
702 ---Log entries that are relative and
703 ---did not have an Effective Cal Period
704
705 fnd_file.put_line(FND_FILE.OUTPUT,'Entries with no Effective Cal Period');
706 fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------');
707 fnd_file.put_line(FND_FILE.OUTPUT,
708 RPAD('Input Dataset',40,' ')
709 || RPAD('Relative Dimension Group',50,' ')
710 || RPAD('Relative Offset',5,' ')
711 );
712 fnd_file.put_line(FND_FILE.OUTPUT,
713 RPAD('-------------',40,' ')
714 || RPAD('------------------------',50,' ')
715 || RPAD('---------------',5,' ')
716 );
717 For i in 0..z_No_Eff_Cal_Entries_Ctr-1 LOOP
718 fnd_file.put_line(FND_FILE.OUTPUT,
719 RPAD(z_No_Eff_Cal_Entries_Tab(i).Input_Dataset_Name,40,' ')
720 || RPAD(z_No_Eff_Cal_Entries_Tab(i).Relative_Dim_Grp_Name,50,' ')
721 || RPAD(z_No_Eff_Cal_Entries_Tab(i).Relative_Cal_Period_Offset,5,' ')
722 );
723 End LOOP;
724
725 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
726 p_module=> l_module_name,
727 p_msg_text=> 'EXIT');
728 EXCEPTION
729 WHEN OTHERS THEN
730 FEM_UTILS.set_master_err_state( z_master_err_state,
731 FEM_UTILS.G_RSM_FATAL_ERR,
732 G_APP_NAME,
733 G_ERRMSG_UNEXPECTED_SQLERROR,
734 G_ERRMAC_ROUTINE_NAME,
735 l_module_name,
736 NULL,
737 G_ERRMAC_SQL_ERROR,
738 SQLERRM);
739
740 End Log_Missing_Entries;
741
742 Procedure Populate_WhereClause_List( p_DS_IO_Def_ID IN NUMBER
743 ,p_Output_Period_ID IN NUMBER
744 ,p_Ledger_ID IN NUMBER
745 ,p_List_B IN OUT NOCOPY List_B
746 ,p_List_B_Ctr IN OUT NOCOPY BINARY_INTEGER
747 ,p_output_ds_code IN NUMBER ) IS
748 cursor get_Input_List is
749 Select
750 a.INPUT_DATASET_CODE
751 ,b.DATASET_NAME
752 ,a.ABSOLUTE_CAL_PERIOD_FLAG
753 ,a.ABSOLUTE_CAL_PERIOD_ID
754 ,c.CAL_PERIOD_NAME
755 ,a.RELATIVE_DIMENSION_GROUP_ID
756 ,d.dimension_group_name
757 ,a.RELATIVE_CAL_PERIOD_OFFSET
758 from
759 FEM_DS_INPUT_LISTS a
760 ,FEM_DATASETS_VL b
761 ,FEM_CAL_PERIODS_TL c
762 ,FEM_DIMENSION_GRPS_TL d
763 where
764 a.DATASET_IO_OBJ_DEF_ID = p_DS_IO_Def_ID
765 and a.INPUT_DATASET_CODE = b.DATASET_CODE
766 and a.ABSOLUTE_CAL_PERIOD_ID= c.CAL_PERIOD_ID(+)
767 and c.language(+) = USERENV('LANG')
768 and a.relative_dimension_group_id = d.dimension_group_id(+)
769 and d.language(+) = USERENV('LANG');
770
771 l_Input_Dataset_Code DEF_DATASET_CODE%TYPE;
772 l_Input_Dataset_Name DEF_DATASET_NAME%TYPE;
773 l_Absolute_Cal_Period_Flag DEF_ABS_CAL_PERIOD_FLAG%TYPE;
774 l_Absolute_Cal_Period_ID DEF_CAL_PERIOD_ID%TYPE;
775 l_Absolute_Cal_Period_Name DEF_CAL_PERIOD_NAME%TYPE;
776 l_Rel_Dimension_Group_ID DEF_DIM_GRP_ID%TYPE;
777 l_Rel_Dimension_Group_Name DEF_DIM_GRP_NAME%TYPE;
778 l_Rel_Cal_Period_Offset DEF_REL_CAL_PERIOD_OFFSET%TYPE;
779
780 l_Effective_Cal_Period_ID DEF_CAL_PERIOD_ID%TYPE;
781 l_Effective_Cal_Period_Name DEF_CAL_PERIOD_NAME%TYPE;
782 l_TEMP NUMBER;
783 x_Err_Code NUMBER;
784
785 l_return_status VARCHAR2(10);
786 l_msg_count NUMBER;
787 l_msg_data VARCHAR2(4000);
788
789 -- 1 2
790 -- 1234567890123456789012345
791 l_api_name CONSTANT VARCHAR2(30) := 'Populate_WhereClause_List';
792 l_module_name VARCHAR2(75) := G_MODULE_NAME || l_api_name;
793
794 Begin
795 -- *******************************************************************************************
796 -- name Populate_WhereClause_List
797 -- Function As it states, populate the where clause with all appropriate predicates.
798 --
799 --
800 -- Parameters
801 -- IN
802 -- p_DS_IO_Def_ID IN NUMBER
803 -- -
804 -- p_Output_Period_ID IN NUMBER
805 -- -
806 -- p_Ledger_ID IN NUMBER
807 -- -
808 --
809 -- OUT
810 -- x_Err_Code OUT NUMBER
811 -- -
812 -- x_Err_Msg OUT VARCHAR2
813 -- -
814 --
815 -- IN OUT
816 -- p_List_B IN OUT NOCOPY List_B
817 -- -
818 -- p_List_B_Ctr IN OUT NOCOPY BINARY_INTEGER
819 -- -
820 --
821 -- Returns
822 -- HISTORY
823 -- 09-Jan-2004 rjking comment header added, reformatted and commented.
824 --
825 -- *******************************************************************************************
826 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
827 p_module=> l_module_name,
828 p_msg_text=> 'ENTRY');
829
830 ------------------------------------------------
831 ------------------------------------------------
832 -- add the output_ds/cal_period tuple first..
833 ------------------------------------------------
834 ------------------------------------------------
835 If No_Entry_For_IDS_ECP(p_output_ds_code
836 ,p_Output_Period_ID
837 ,p_List_B
838 ,p_List_B_Ctr ) then
839
840 -- add it.
841 Add_IDS_ECP_Entry_To_List_B( p_output_ds_code
842 ,p_Output_Period_ID
843 ,p_List_B
844 ,p_List_B_Ctr);
845
846 END IF;
847 ------------------------------------------------
848 ------------------------------------------------
849 -- now add all the input list tuples..
850 ------------------------------------------------
851 ------------------------------------------------
852 OPEN get_Input_List;
853 LOOP
854 FETCH get_Input_List INTO
855 l_Input_Dataset_Code
856 ,l_Input_Dataset_Name
857 ,l_Absolute_Cal_Period_Flag
858 ,l_Absolute_Cal_Period_ID
859 ,l_Absolute_Cal_Period_Name
860 ,l_Rel_Dimension_Group_ID
861 ,l_Rel_Dimension_Group_Name
862 ,l_Rel_Cal_Period_Offset;
863
864 EXIT WHEN get_Input_List%NOTFOUND;
865
866 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
867 p_module=> l_module_name,
868 p_msg_text=> '========loop execution==============');
869
870 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
871 p_module=> l_module_name,
872 p_msg_text=> 'l_Input_Dataset_Code(' || l_Input_Dataset_Code || ')');
873
874 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
875 p_module=> l_module_name,
876 p_msg_text=> 'l_Input_Dataset_Name(' || l_Input_Dataset_Name || ')');
877 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
878 p_module=> l_module_name,
879 p_msg_text=> 'l_Absolute_Cal_Period_Flag(' || l_Absolute_Cal_Period_Flag || ')');
880 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
881 p_module=> l_module_name,
882 p_msg_text=> 'l_Absolute_Cal_Period_ID(' || l_Absolute_Cal_Period_ID || ')');
883 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
884 p_module=> l_module_name,
885 p_msg_text=> 'l_Absolute_Cal_Period_Name(' || l_Absolute_Cal_Period_Name || ')');
886 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
887 p_module=> l_module_name,
888 p_msg_text=> 'l_Rel_Dimension_Group_ID(' || l_Rel_Dimension_Group_ID || ')');
889 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
890 p_module=> l_module_name,
891 p_msg_text=> 'l_Rel_Dimension_Group_Name(' || l_Rel_Dimension_Group_Name || ')');
892 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
893 p_module=> l_module_name,
894 p_msg_text=> 'l_Rel_Cal_Period_Offset(' || l_Rel_Cal_Period_Offset || ')');
895
896
897 If (l_Absolute_Cal_Period_Flag = 'Y') then
898 l_Effective_Cal_Period_ID := l_Absolute_Cal_Period_ID;
899 Else
900 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
901 p_module=> l_module_name,
902 p_msg_text=> 'before fem_dimension_util_pkg.Effective_Cal_Period_ID');
903 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
904 p_module=> l_module_name,
905 p_msg_text=> 'p_Ledger_ID(' || p_Ledger_ID || ')');
906 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
907 p_module=> l_module_name,
908 p_msg_text=> 'p_Output_Period_ID(' || p_Output_Period_ID || ')');
909
910 l_Effective_Cal_Period_ID :=
911 fem_dimension_util_pkg.Relative_Cal_Period_ID (
912 p_api_version => 1.0 ,
913 x_return_status => l_return_status ,
914 x_msg_count => l_msg_count ,
915 x_msg_data => l_msg_data ,
916 p_per_num_offset => l_Rel_Cal_Period_Offset ,
917 p_base_cal_period_id => p_Output_Period_ID);
918
919 fem_engines_pkg.tech_message(p_severity=>G_LOG_STATEMENT ,
920 p_module=> l_module_name,
921 p_msg_text=> 'l_Effective_Cal_Period_ID(' || l_Effective_Cal_Period_ID || ')');
922
923
924 If (l_Effective_Cal_Period_ID = -1) then
925 -- we couldn't find an effective cal period ID for the relative..
926 Add_Missing_Entries
927 (p_Input_Dataset_Name => l_Input_Dataset_Name
928 ,p_Abs_Cal_Period_Flag => l_Absolute_Cal_Period_Flag
929 ,p_Abs_Cal_Period_Name => l_Absolute_Cal_Period_Name
930 ,p_Rel_Dim_Grp_Name => l_Rel_Dimension_Group_Name
931 ,p_Rel_Cal_Period_Offset => l_Rel_Cal_Period_Offset
932 ,p_Eff_Cal_Period_Name => 'No Eff Cal Period Found'
933 ,p_Missing_Entry_Type => 'NO_EFF_CAL_PER'
934 );
935
936 FEM_UTILS.set_master_err_state( z_master_err_state,
937 FEM_UTILS.G_RSM_NONFATAL_ERR,
938 G_APP_NAME,
939 G_NO_EFFECTIVE_CAL_PERIOD );
940 -- The following offset information in the Dataset Input List doesn't
941 -- resolve to an Effective Input Calendar Period
942 --
943 -- Dataset Group p_DS_IO_Def_ID
944 -- Reference Period p_Output_Period_ID
945 -- Input Dataset l_Input_Dataset_Code
946 -- Relative Dimension Group ID l_Rel_Dimension_Group_ID
947 -- Relative Cal Period Offset l_Rel_Cal_Period_Offset
948
949 End If; -- (l_Effective_Cal_Period_ID = -1)
950 End If; -- (l_Absolute_Cal_Period_Flag = 'Y')
951
952 If ( (l_Effective_Cal_Period_ID IS NOT NULL )
953 AND (l_Effective_Cal_Period_ID <> -1 ) ) then
954 -- seems valid. so if it hasn't been added to p_List_B...
955 If No_Entry_For_IDS_ECP(l_Input_Dataset_Code
956 ,l_Effective_Cal_Period_ID
957 ,p_List_B
958 ,p_List_B_Ctr ) then
959
960 -- add it.
961 Add_IDS_ECP_Entry_To_List_B( l_Input_Dataset_Code
962 ,l_Effective_Cal_Period_ID
963 ,p_List_B
964 ,p_List_B_Ctr);
965
966 -- If ((l_Pft_Eng_Write_Flg = 1) and (p_Chaining_Enabled = 'Y')) then
967 null;
968 -- End If;
969 Else
970 -- otherwise show it as a duplicate entry..
971 l_Effective_Cal_Period_Name
972 := GetCalPeriodName (p_Cal_Period_ID => l_Effective_Cal_Period_ID);
973
974 Add_Missing_Entries( p_Input_Dataset_Name => l_Input_Dataset_Name
975 ,p_Abs_Cal_Period_Flag => l_Absolute_Cal_Period_Flag
976 ,p_Abs_Cal_Period_Name => l_Absolute_Cal_Period_Name
977 ,p_Rel_Dim_Grp_Name => l_Rel_Dimension_Group_Name
978 ,p_Rel_Cal_Period_Offset => l_Rel_Cal_Period_Offset
979 ,p_Eff_Cal_Period_Name => l_Effective_Cal_Period_Name
980 ,p_Missing_Entry_Type => 'DUP_ENTRY' );
981
982 FEM_UTILS.set_master_err_state( z_master_err_state,
983 FEM_UTILS.G_RSM_NONFATAL_ERR,
984 G_APP_NAME,
985 G_DUPLICATE_INPUT_LIST_ENTRY,
986 G_MACRO_DATASET,
987 l_Input_Dataset_Name,
988 NULL,
989 G_MACRO_EFF_CAL_PERIOD,
990 l_Effective_Cal_Period_Name);
991 -- Dataset Group p_DS_IO_Def_ID
992 -- Reference Period p_Output_Period_ID
993 -- Input Dataset l_Input_Dataset_Code
994 -- Absolute Cal Period Flag l_Absolute_Cal_Period_Flag
995 -- Absolute Cal Period l_Absolute_Cal_Period_ID
996 -- Relative Dimension Group ID l_Rel_Dimension_Group_ID
997 -- Relative Cal Period Offset l_Rel_Cal_Period_Offset
998 -- Effective Cal Period l_Effective_Cal_Period_ID
999
1000 End If;
1001
1002 End If;
1003
1004 END LOOP;
1005
1006 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
1007 p_module=> l_module_name,
1008 p_msg_text=> 'EXIT');
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 FEM_UTILS.set_master_err_state( z_master_err_state,
1012 FEM_UTILS.G_RSM_FATAL_ERR,
1013 G_APP_NAME,
1014 G_ERRMSG_UNEXPECTED_SQLERROR,
1015 G_ERRMAC_ROUTINE_NAME,
1016 l_module_name,
1017 NULL,
1018 G_ERRMAC_SQL_ERROR,
1019 SQLERRM);
1020
1021
1022 End Populate_WhereClause_List;
1023
1024
1025 PROCEDURE FEM_GetOutputDS_PVT(p_api_version IN NUMBER
1026 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1027 ,p_encoded IN VARCHAR2 := FND_API.G_TRUE
1028 ,x_return_status OUT NOCOPY VARCHAR2
1029 ,x_msg_count OUT NOCOPY NUMBER
1030 ,x_msg_data OUT NOCOPY VARCHAR2
1031 ,p_DSGroup_Def_ID IN NUMBER
1032 ,x_Output_DS_ID OUT NOCOPY NUMBER
1033 ,p_pop_messages_at_exit IN VARCHAR2 := FND_API.G_TRUE)
1034 IS
1035 l_api_version NUMBER := 1.0;
1036 l_api_name CONSTANT VARCHAR2(30) := 'FEM_GetOutputDS_PVT';
1037 l_module_name VARCHAR2(70) := G_MODULE_NAME || l_api_name;
1038
1039 cursor GetOutputDS is
1040 Select
1041 a.output_dataset_code
1042 from
1043 fem_ds_input_output_defs a
1044 where
1045 a.DATASET_IO_OBJ_DEF_ID = p_DSGroup_Def_ID;
1046
1047 BEGIN
1048 -- *******************************************************************************************
1049 -- API name FEM_GetOutputDS_PVT
1050 -- Type Private
1051 -- Pre-reqs None
1052 -- Function 1) convert a rule set into a flat structure prior to engine processing
1053 -- 2) Report all errors that occur during the conversion that are not
1054 -- covered by the UI validation routines
1055 --
1056 --
1057 -- Parameters
1058 -- IN
1059 -- p_api_version IN NUMBER
1060 -- Current version of this API
1061 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1062 -- If set to
1063 -- FND_API.G_TRUE - Initialize FND_MSG_PUB
1064 -- FND_API.G_FALSE - DO NOT Initialize FND_MSG_PUB
1065 -- p_encoded IN VARCHAR2 := FND_API.G_TRUE
1066 -- If set to
1067 -- FND_API.G_TRUE - return error messages in encoded format
1068 -- FND_API.G_FALSE - return error messages in non-encoded (natural language) format
1069
1070 -- p_DSGroup_Def_ID IN NUMBER
1071 -- - dataset group's object_definition_id
1072 -- x_Output_DS_ID OUT NOCOPY NUMBER
1073 -- - the output dataset in use by the p_DSGroup_Def_ID
1074 --
1075 -- OUT
1076 -- x_return_status OUT VARCHAR2
1077 -- Possible return status
1078 -- FND_API.G_RET_STS_SUCCESS - Call was successful, msgs may
1079 -- still be present (check x_msg_count)
1080 -- FND_API.G_RET_STS_ERROR - Call was not successful, msgs should
1081 -- be present (check x_msg_count)
1082 -- FND_API.G_RET_STS_UNEXP_ERROR - Unexpected errors occurred which are
1083 -- unrecoverable (check x_msg_count)
1084 --
1085 -- x_msg_count OUT NUMBER
1086 -- Count of messages returned. If x_msg_count = 1, then the message is returned
1087 -- in x_msg_data. If x_msg_count > 1, then messages are returned via FND_MSG_PUB.
1088 --
1089 -- x_msg_data OUT VARCHAR2
1090 -- Error message returned.
1091 --
1092 -- Version Current Version 1.0
1093 --
1094 -- Previous version N/A
1095 -- Initial version 1.0
1096 -- *******************************************************************************************
1097
1098 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
1099 p_module=> l_module_name,
1100 p_msg_text=> 'ENTRY');
1101 reset_master_err_state;
1102
1103 -- initialize our status to 'we are good!'
1104 x_return_status := FND_API.G_RET_STS_SUCCESS;
1105
1106 -- initialize msg stack?
1107 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1108 fnd_msg_pub.initialize;
1109 END IF;
1110
1111 -- check API version...
1112 IF NOT fnd_api.Compatible_API_Call (l_api_version,
1113 p_api_version,
1114 l_api_name,
1115 G_PKG_NAME ) THEN
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117 END IF;
1118
1119 OPEN GetOutputDS;
1120 FETCH GetOutputDS INTO
1121 x_Output_DS_ID;
1122
1123 IF GetOutputDS%NOTFOUND THEN
1124 FEM_UTILS.set_master_err_state( z_master_err_state,
1125 FEM_UTILS.G_RSM_FATAL_ERR,
1126 G_APP_NAME,
1127 G_ERRMSG_NO_ODS_FOR_DSG );
1128 -- this one and only error is utterly fatal if we get it (no output ds!!!)
1129 RAISE FND_API.G_EXC_ERROR;
1130 ELSE
1131 CLOSE GetOutputDS;
1132 END IF;
1133
1134 IF fnd_api.to_Boolean(p_pop_messages_at_exit) THEN
1135 FND_MSG_PUB.Count_And_Get ( p_encoded,
1136 x_msg_count,
1137 x_msg_data );
1138 END IF;
1139
1140 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
1141 p_module=> l_module_name,
1142 p_msg_text=> 'EXIT');
1143
1144
1145 EXCEPTION
1146 WHEN FND_API.G_EXC_ERROR THEN
1147 IF fnd_api.to_Boolean(p_pop_messages_at_exit) THEN
1148 FND_MSG_PUB.Count_And_Get ( p_encoded,
1149 x_msg_count,
1150 x_msg_data );
1151 END IF;
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153
1154
1155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156 IF fnd_api.to_Boolean(p_pop_messages_at_exit) THEN
1157 FND_MSG_PUB.Count_And_Get ( p_encoded,
1158 x_msg_count,
1159 x_msg_data );
1160 END IF;
1161
1162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1163
1164 WHEN OTHERS THEN
1165 FEM_UTILS.set_master_err_state( z_master_err_state,
1166 FEM_UTILS.G_RSM_FATAL_ERR,
1167 G_APP_NAME,
1168 G_ERRMSG_UNEXPECTED_SQLERROR,
1169 G_ERRMAC_ROUTINE_NAME,
1170 l_module_name,
1171 NULL,
1172 G_ERRMAC_SQL_ERROR,
1173 SQLERRM);
1174
1175 IF fnd_api.to_Boolean(p_pop_messages_at_exit) THEN
1176 FND_MSG_PUB.Count_And_Get ( p_encoded,
1177 x_msg_count,
1178 x_msg_data );
1179 END IF;
1180
1181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1182
1183 END FEM_GetOutputDS_PVT;
1184
1185
1186
1187 -- *******************************************************************************************
1188 -- API name FEM_Gen_DS_WClause_By_TblNm_PVT
1189 -- Type Private
1190 -- Pre-reqs None
1191 -- Function 1) convert a rule set into a flat structure prior to engine processing
1192 -- 2) Report all errors that occur during the conversion that are not
1193 -- covered by the UI validation routines
1194 --
1195 --
1196 -- Parameters
1197 -- IN
1198 -- p_api_version IN NUMBER
1199 -- Current version of this API
1200 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1201 -- If set to
1202 -- FND_API.G_TRUE - Initialize FND_MSG_PUB
1203 -- FND_API.G_FALSE - DO NOT Initialize FND_MSG_PUB
1204 -- p_encoded IN VARCHAR2 := FND_API.G_TRUE
1205 -- If set to
1206 -- FND_API.G_TRUE - return error messages in encoded format
1207 -- FND_API.G_FALSE - return error messages in non-encoded (natural language) format
1208
1209 -- p_DS_IO_Def_ID IN NUMBER
1210 -- - dataset group's object_definition_id
1211 -- p_Output_Period_ID OUT NUMBER
1212 -- - period we are using for all relative references.
1213 -- p_Table_Alias IN VARCHAR2 DEFAULT NULL
1214 -- - table alias to use for 'p_Table_Name'
1215 -- p_Table_Name IN VARCHAR2
1216 -- - table name where data is coming from.
1217 -- p_Ledger_ID IN NUMBER DEFAULT NULL
1218 -- - the ledger_id that is being processed.
1219 --
1220 --
1221 -- OUT
1222 -- x_return_status OUT VARCHAR2
1223 -- Possible return status
1224 -- FND_API.G_RET_STS_SUCCESS - Call was successful, msgs may
1225 -- still be present (check x_msg_count)
1226 -- FND_API.G_RET_STS_ERROR - Call was not successful, msgs should
1227 -- be present (check x_msg_count)
1228 -- FND_API.G_RET_STS_UNEXP_ERROR - Unexpected errors occurred which are
1229 -- unrecoverable (check x_msg_count)
1230 --
1231 -- x_msg_count OUT NUMBER
1232 -- Count of messages returned. If x_msg_count = 1, then the message is returned
1233 -- in x_msg_data. If x_msg_count > 1, then messages are returned via FND_MSG_PUB.
1234 --
1235 -- x_msg_data OUT VARCHAR2
1236 -- Error message returned.
1237 --
1238 -- Version Current Version 1.0
1239 --
1240 -- Previous version N/A
1241 -- Initial version 1.0
1242 -- *******************************************************************************************
1243 PROCEDURE FEM_Gen_DS_WClause_PVT(
1244 p_api_version IN NUMBER
1245 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1246 ,p_encoded IN VARCHAR2 := FND_API.G_TRUE
1247 ,x_return_status OUT NOCOPY VARCHAR2
1248 ,x_msg_count OUT NOCOPY NUMBER
1249 ,x_msg_data OUT NOCOPY VARCHAR2
1250 ,p_DS_IO_Def_ID IN NUMBER
1251 ,p_Output_Period_ID IN NUMBER
1252 ,p_Table_Alias IN VARCHAR2 DEFAULT NULL
1253 ,p_Table_Name IN VARCHAR2
1254 ,p_Ledger_ID IN NUMBER DEFAULT NULL
1255 ,p_where_clause OUT NOCOPY LONG
1256 ) IS
1257
1258 -- STANDARD STUFF
1259 l_api_version NUMBER := 1.0;
1260 l_api_name CONSTANT VARCHAR2(30) := 'FEM_Gen_DS_WClause_PVT';
1261 l_module_name VARCHAR2(70) := G_MODULE_NAME || l_api_name;
1262 l_output_ds_code NUMBER := 0;
1263
1264 p_List_B List_B; -- List used to keep track of Dataset_Code and Cal_Period_Combinations
1265 -- that have been already added to the whereclause
1266
1267 p_List_B_Ctr BINARY_INTEGER := 0;
1268 X_WhereClause LONG := NULL;
1269
1270 l_return_status VARCHAR2(20);
1271 l_msg_count NUMBER;
1272 l_msg_data VARCHAR2(2000);
1273
1274 BEGIN
1275 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
1276 p_module=> l_module_name,
1277 p_msg_text=> 'ENTRY');
1278 reset_master_err_state;
1279
1280 -- initialize our status to 'we are good!'
1281 x_return_status := FND_API.G_RET_STS_SUCCESS;
1282
1283 -- initialize msg stack?
1284 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1285 fnd_msg_pub.initialize;
1286 END IF;
1287
1288 -- check API version...
1289 IF NOT fnd_api.Compatible_API_Call (l_api_version,
1290 p_api_version,
1291 l_api_name,
1292 G_PKG_NAME ) THEN
1293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1294 END IF;
1295
1296 -- ===============================================================
1297 -- =========imported from first version call======================
1298 -- ===============================================================
1299
1300 /*---Reinitialize Global PLSQL tables------------*/
1301 z_No_Eff_Cal_Entries_Tab.DELETE;
1302 z_No_Eff_Cal_Entries_Ctr := 0;
1303
1304 z_Dup_Entries_Tab.DELETE;
1305 z_Dup_Entries_Ctr := 0;
1306 /*-----------------------------------------------*/
1307
1308 FEM_GetOutputDS_PVT( p_api_version => 1.0
1309 ,p_init_msg_list => FND_API.G_FALSE
1310 ,p_encoded => p_encoded
1311 ,x_return_status => l_return_status
1312 ,x_msg_count => l_msg_count
1313 ,x_msg_data => l_msg_data
1314 ,p_DSGroup_Def_ID => p_DS_IO_Def_ID
1315 ,x_Output_DS_ID => l_output_ds_code
1316 ,p_pop_messages_at_exit => FND_API.G_FALSE );
1317
1318 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1319 RAISE FND_API.G_EXC_ERROR;
1320 END IF;
1321
1322 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1324 END IF;
1325
1326 If (p_Ledger_Id is NULL) then
1327 FEM_UTILS.set_master_err_state( z_master_err_state,
1328 FEM_UTILS.G_RSM_NONFATAL_ERR,
1329 G_APP_NAME,
1330 G_LEDGER_REQD_FOR_LEDG_TABS );
1331 ELSE
1332
1333
1334 Populate_WhereClause_List( p_DS_IO_Def_ID
1335 ,p_Output_Period_ID
1336 ,p_Ledger_ID
1337 ,p_List_B
1338 ,p_List_B_Ctr
1339 ,l_output_ds_code
1340 );
1341
1342
1343 Begin
1344 For i in 0 ..p_List_B_Ctr LOOP
1345 If (p_Table_Alias is NOT NULL) then
1346 X_WhereClause :=
1347 X_WhereClause
1348 || '('
1349 || '('||p_Table_Alias||'.DATASET_CODE ='||p_List_B(i).X_Dataset_Code||')'
1350 || 'and'
1351 || '('||p_Table_Alias||'.CAL_PERIOD_ID ='||p_List_B(i).X_Cal_Period_ID||')'
1352 || ') OR';
1353 Else
1354 X_WhereClause :=
1355 X_WhereClause
1356 || '('
1357 || '(DATASET_CODE ='||p_List_B(i).X_Dataset_Code||')'
1358 || 'and'
1359 || '(CAL_PERIOD_ID ='|| p_List_B(i).X_Cal_Period_ID||')'
1360 || ') OR';
1361 End If;
1362
1363 End LOOP;
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN
1366 NULL;
1367 End;
1368
1369 Begin
1370 x_WhereClause := '('||rtrim(x_WhereClause,'OR')||')';
1371 End;
1372 End If; --(p_Ledger_Id is NULL)
1373
1374 Log_Missing_Entries(p_DS_IO_Def_ID => p_DS_IO_Def_ID);
1375
1376 -- ===============================================================
1377 -- =========end of imported from first version call===============
1378 -- ===============================================================
1379
1380
1381
1382 p_where_clause := x_WhereClause;
1383
1384 fem_engines_pkg.tech_message(p_severity=>G_LOG_PROCEDURE ,
1385 p_module=> l_module_name,
1386 p_msg_text=> 'EXIT');
1387
1388
1389 IF (z_master_err_state = FEM_UTILS.G_RSM_FATAL_ERR) THEN
1390 RAISE FND_API.G_EXC_ERROR;
1391 END IF;
1392
1393 FND_MSG_PUB.Count_And_Get ( p_encoded,
1394 x_msg_count,
1395 x_msg_data );
1396 EXCEPTION
1397 WHEN FND_API.G_EXC_ERROR THEN
1398 FND_MSG_PUB.Count_And_Get ( p_encoded,
1399 x_msg_count,
1400 x_msg_data );
1401 x_return_status := FND_API.G_RET_STS_ERROR;
1402
1403
1404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1405 FND_MSG_PUB.Count_And_Get ( p_encoded,
1406 x_msg_count,
1407 x_msg_data );
1408
1409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1410
1411 WHEN OTHERS THEN
1412 FEM_UTILS.set_master_err_state( z_master_err_state,
1413 FEM_UTILS.G_RSM_FATAL_ERR,
1414 G_APP_NAME,
1415 G_ERRMSG_UNEXPECTED_SQLERROR,
1416 G_ERRMAC_ROUTINE_NAME,
1417 l_module_name,
1418 NULL,
1419 G_ERRMAC_SQL_ERROR,
1420 SQLERRM);
1421
1422 FND_MSG_PUB.Count_And_Get ( p_encoded,
1423 x_msg_count,
1424 x_msg_data );
1425
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427
1428
1429 END FEM_Gen_DS_WClause_PVT;
1430
1431
1432
1433 End FEM_DS_WHERE_CLAUSE_GENERATOR;
1434 --End FEM_DS_WHERE_CLAUSE_G_RJK;