[Home] [Help]
PACKAGE BODY: APPS.EDW_OPTION
Source
1 PACKAGE BODY EDW_OPTION as
2 /* $Header: EDWCFIGB.pls 115.9 2003/11/18 07:00:05 smulye noship $ */
3
4
5 function get_warehouse_option(p_object_name varchar2, p_object_id number,p_option_code varchar2,
6 p_option_value out nocopy varchar2) return boolean is
7 l_object_id number(9);
8 l_stmt varchar2(5000);
9 TYPE CurTyp IS REF CURSOR;
10 cv CurTyp;
11 check_tspace_exist varchar(1);
12 check_ts_mode varchar(1);
13 physical_tspace_name varchar2(100);
14
15 begin
16 p_option_value :=null;
17
18 if (p_option_code='DEBUG') then
19 p_option_value:=fnd_profile.value('EDW_DEBUG');
20 elsif (p_option_code='TRACE') then
21 p_option_value:=fnd_profile.value('EDW_TRACE');
22 elsif (p_option_code='DUPLICATE') then
23 p_option_value:=fnd_profile.value('EDW_DUPLICATE_COLLECT');
24 elsif (p_option_code='HASHAREA') then
25 p_option_value:=fnd_profile.value('EDW_HASH_AREA_SIZE');
26 elsif (p_option_code='SORTAREA') then
27 p_option_value:=fnd_profile.value('EDW_EDW_SORT_AREA_SIZE');
28 elsif (p_option_code='KEYSETSIZE') then
29 p_option_value:=fnd_profile.value('EDW_FK_SET_SIZE');
30 elsif (p_option_code='FRESHSTART') then
31 p_option_value:=fnd_profile.value('EDW_FRESH_RESTART');
32 elsif (p_option_code='UPDATETYPE') then
33 p_option_value:=fnd_profile.value('EDW_UPDATE_TYPE');
34 elsif (p_option_code='OPTABLESPACE') then
35 p_option_value:=fnd_profile.value('EDW_OP_TABLE_SPACE');
36 elsif (p_option_code='FK_USE_NL') then
37 p_option_value:=fnd_profile.value('EDW_FK_USE_NL');
38 elsif (p_option_code='SMARTUPDATE') then
39 p_option_value:=fnd_profile.value('EDW_SMART_UPDATE');
40 elsif (p_option_code='LTC_COPY_MERGE_NL') then
41 p_option_value:=fnd_profile.value('EDW_LTC_COPY_MERGE_NL');
42 elsif (p_option_code='COMMITSIZE') then
43 p_option_value:=fnd_profile.value('EDW_COLLECTION_SIZE');
44 elsif (p_option_code='PARALLELISM') then
45 p_option_value:=fnd_profile.value('EDW_PARALLEL');
46 elsif (p_option_code='DANGLING') then
47 p_option_value:=fnd_profile.value('EDW_TEST_MODE');
48 elsif (p_option_code='ROLLBACK') then
49 p_option_value:=fnd_profile.value('EDW_LOAD_ROLLBACK');
50 end if;
51
52 if (p_option_code='HASHAREA' and p_option_value is null) then
53 select param.value into p_option_value from v$parameter param
54 where param.name='hash_area_size';
55 end if;
56
57 if (p_option_code='SORTAREA' and p_option_value is null) then
58 select param.value into p_option_value from v$parameter param
59 where param.name='sort_area_size';
60 end if;
61
62 if (p_option_code='OPTABLESPACE' and p_option_value is null) then
63 AD_TSPACE_UTIL.is_new_ts_mode (check_ts_mode);
64 If check_ts_mode ='Y' then
65 AD_TSPACE_UTIL.get_tablespace_name ('BIS', 'INTERFACE','Y',check_tspace_exist, physical_tspace_name);
66 if check_tspace_exist='Y' and physical_tspace_name is not null then
67 p_option_value := physical_tspace_name;
68 end if;
69 end if;
70 if p_option_value is null then
71 select default_tablespace into p_option_value from dba_users where
72 username=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
73 end if;
74
75 end if;
76
77 return true;
78
79 Exception when others then
80 g_status_message:=sqlerrm;
81 edw_owb_collection_util.write_to_log_file_n('Error in get_warehouse_option '||sqlerrm);
82 return false;
83 end get_warehouse_option;
84
85 function get_option_columns(p_object_name varchar2, p_object_id number,p_option_code varchar2,
86 p_option_cols out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,
87 p_number_option_cols out nocopy number) return boolean is
88 l_object_id number(9);
89 l_stmt varchar2(5000);
90 l_option_fk_key number;
91 l_option_value varchar2(400);
92 TYPE CurTyp IS REF CURSOR;
93 cv CurTyp;
94 l_count number;
95 begin
96 p_number_option_cols:=0;
97 return true;
98 Exception when others then
99 g_status_message:=sqlerrm;
100 edw_owb_collection_util.write_to_log_file_n('Error in get_option_columns '||sqlerrm);
101 return false;
102 end get_option_columns;
103
104 function get_fk_dangling_load(p_object_id number,p_option_fk_key number,
105 p_option_cols out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,p_number_option_cols out nocopy number)
106 return boolean is
107 l_stmt varchar2(5000);
108 TYPE CurTyp IS REF CURSOR;
109 cv CurTyp;
110 l_count number;
111 l_option_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
112 l_option_values EDW_OWB_COLLECTION_UTIL.varcharTableType;
113 l_number_option_cols number;
114 Begin
115 p_number_option_cols:=0;
116 return true;
117 Exception when others then
118 g_status_message:=sqlerrm;
119 edw_owb_collection_util.write_to_log_file_n('Error in get_fk_dangling_load '||sqlerrm);
120 return false;
121 End;
122
123 function get_level_skip_update(p_object_id number,p_option_fk_key number,
124 p_option_cols out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,p_number_option_cols out nocopy number)
125 return boolean is
126 l_stmt varchar2(5000);
127 TYPE CurTyp IS REF CURSOR;
128 cv CurTyp;
129 l_option_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
130 l_option_values EDW_OWB_COLLECTION_UTIL.varcharTableType;
131 l_number_option_cols number;
132 Begin
133 p_number_option_cols:=0;
134 return true;
135 Exception when others then
136 g_status_message:=sqlerrm;
137 edw_owb_collection_util.write_to_log_file_n('Error in get_level_skip_update '||sqlerrm);
138 return false;
139 End;
140
141
142 function get_level_skip_delete(p_object_id number,p_option_fk_key number,
143 p_option_cols out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,p_number_option_cols out nocopy number)
144 return boolean is
145 l_stmt varchar2(5000);
146 TYPE CurTyp IS REF CURSOR;
147 cv CurTyp;
148 l_option_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
149 l_option_values EDW_OWB_COLLECTION_UTIL.varcharTableType;
150 l_number_option_cols number;
151 Begin
152 p_number_option_cols:=0;
153 return true;
154 Exception when others then
155 g_status_message:=sqlerrm;
156 edw_owb_collection_util.write_to_log_file_n('Error in get_level_skip_delete '||sqlerrm);
157 return false;
158 End;
159
160 function get_time return varchar2 is
161 begin
162 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
163 Exception when others then
164 write_to_log_file_n('Exception in get_time '||sqlerrm);
165 return null;
166 End;
167
168 procedure write_to_log_file(p_message varchar2) is
169 begin
170 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
171 Exception when others then
172 null;
173 End;
174
175 procedure write_to_log_file_n(p_message varchar2) is
176 begin
177 write_to_log_file(' ');
178 write_to_log_file(p_message);
179 Exception when others then
180 null;
181 End;
182
183 procedure set_debug(p_debug boolean) is
184 Begin
185 g_debug:=p_debug;
186 Exception when others then
187 null;
188 End;
189
190 FUNCTION get_object_type (p_object_id IN NUMBER)
191 RETURN varchar2
192 IS
193 l_stmt VARCHAR2 (5000);
194 l_object_type VARCHAR2 (30);
195 TYPE curtyp IS REF CURSOR;
196 cv curtyp;
197 l_status BOOLEAN;
198 BEGIN
199
200 l_stmt := 'select ''DIMENSION'' from cmpwbdimension_v'
201 || ' where elementid=:d '
202 || ' union '
203 || ' select ''FACT'' from cmpwbcube_v'
204 || ' where elementid=:f ';
205 OPEN cv FOR l_stmt USING p_object_id, p_object_id;
206 FETCH cv INTO l_object_type;
207 CLOSE cv;
208
209 IF l_object_type = 'DIMENSION'
210 OR l_object_type = 'FACT'
211 THEN
212 null;
213 ELSE
214 l_object_type := 'FACT';
215 END IF;
216
217 RETURN l_object_type;
218 EXCEPTION
219 WHEN NO_DATA_FOUND
220 THEN
221 BEGIN
222 CLOSE cv;
223 EXCEPTION
224 WHEN OTHERS
225 THEN
226 NULL;
227 END;
228 l_object_type := 'FACT';
229 RETURN l_object_type;
230
231 WHEN OTHERS
232 THEN
233 BEGIN
234 CLOSE cv;
235 EXCEPTION
236 WHEN OTHERS
237 THEN
238 NULL;
239 END;
240 l_object_type := 'FACT';
241 RETURN l_object_type;
242 END get_object_type;
243
244
245 end;