1 PACKAGE FEM_DIM_MEMBER_LOADER_PKG AUTHID CURRENT_USER AS
2 --$Header: femdimldr_pkh.pls 120.5 2006/08/04 21:12:26 rflippo noship $
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | femdimldr_pkh.pls
10 |
11 | NAME fem_dim_member_loader_pkg
12 |
13 | DESCRIPTION
14 |
15 | Package Spec for fem_dim_member_loader_pkg. This package is the engine for
16 | loading dimension members, member translatable names/descriptions,
17 | and member attribute assignments from interface tables into the FEM
18 | database.
19 |
20 | NOTES
21 |
22 |
23 | HISTORY
24 |
25 | 20-OCT-03 RFlippo Created
26 | 31-MAR-04 RFlippo Added new exceptions for Error Reprocessing
27 | 06-MAY-04 RFlippo Exposed sub-processes to the public spec for MP
28 | 21-SEP-04 RFlippo Bug#3900960 Insufficient messaging for snapshot
29 | load with 0 rows
30 | 28-Sep-04 RFlippo Bug#3906218 NEED ABILITY TO UNDELETE DIMENSIONS
31 | modified the Base_Update procedure signature
32 | 22-Nov-04 RFlippo Bug#4019853 Date Overlap logic - added pragma exception
33 | 14-DEC-04 RFlippo Bug#4061097 Get ORA-1722 when try to load budget member
34 | Bug#3654256 Change loader to use dimension_id as in parm
35 | so that the list comes from Xdim metadata.
36 | 18-MAR-05 RFlippo Bug#4244082 Modify base_update signature to
37 | accomodate the update of the dimgrp for a member
38 | 28-APR-05 sshanmug Added support for Composite dimension Loader
39 | 26-MAY-05 RFlippo Bug#4107370 Added new message for folder security
40 | violation
41 | 31-MAY-05 RFlippo Bug#3923485 removed date_format_mask parm from Main
42 | since now using ICX: Date Format Mask profile option
43 | 13-JUN-05 RFlippo Bug#3895203 lvl specific attributes required new
44 | parm in pre_validation_attr
45 | 24-JAN-06 RFlippo Bug#4927869 Added new message for G_INVALID_DATE_MASK
46 | 28-APR-06 RFlippo Bug#5174039 Added new exception
47 | e_invalid_calp_start_date
48 | 04-AUG-06 RFlippo Bug 5060746 Modify signatures to use bind variables
49 | push MP methodology
50 +=========================================================================*/
51
52 ---------------------------------------------
53 -- Package Constants
54 ---------------------------------------------
55 c_block CONSTANT VARCHAR2(80) := 'fem.plsql.fem_dim_member_loader_pkg';
56 c_fem CONSTANT VARCHAR2(3) := 'FEM';
57 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
58 c_object_version_number CONSTANT NUMBER := 1;
59 c_enabled_flag VARCHAR2(1) := 'Y';
60 c_personal_flag VARCHAR2(1) := 'N';
61 c_read_only_flag VARCHAR2(1) := 'Y';
62
63 c_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
64 c_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
65 c_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
66 c_error CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
67 c_unexp CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
68 c_api_version CONSTANT NUMBER := 1.0;
69 c_fetch_limit CONSTANT NUMBER := 99999;
70
71
72
73 ---------Message Constants--------------
74 G_INVALID_DATE_MASK VARCHAR2(30) := 'FEM_DIM_MBR_LDR_DATE_MASK';
75 G_DIM_NOT_FOUND VARCHAR2(30) := 'FEM_DIM_NOT_FOUND';
76 G_INVALID_SIMPLE_DIM VARCHAR2(30) := 'FEM_DIM_LOADS_NOT_ALLOWED';
77 G_INVALID_DATE_FORMAT VARCHAR2(30) := 'FEM_INVALID_DATE_FORMAT';
78 G_INVALID_EXEC_MODE VARCHAR2(30) := 'FEM_DIM_MEMBER_LDR_EXEC_MODE';
79 G_EXEC_LOCK_EXISTS VARCHAR2(30) := 'FEM_PL_OBJ_EXECLOCK_EXISTS_ERR';
80 G_INVALID_OBJ_DEF VARCHAR2(30) := 'FEM_DATAX_LDR_BAD_OBJ_ERR';
81 G_EXT_LDR_POST_PROC_ERR VARCHAR2(30) := 'FEM_EXT_LDR_POST_PROC_ERR';
82 G_EXT_LDR_EXEC_STATUS VARCHAR2(30) := 'FEM_EXT_LDR_EXEC_STATUS';
83 G_PL_REG_REQUEST_ERR VARCHAR2(30) := 'FEM_PL_REG_REQUEST_ERR';
84 G_PL_OBJ_EXEC_LOCK_ERR VARCHAR2(30) := 'FEM_PL_OBJ_EXEC_LOCK_ERR';
85 G_MULT_DEFAULT_VERSION VARCHAR2(30) := 'FEM_TOO_MANY_DEFAULT_VERSIONS';
86 G_NO_ROWS_TO_LOAD VARCHAR2(30) := 'FEM_DIM_MBR_LDR_NO_ROWS_LOAD';
87 G_NO_STRUCTURE_DEFINED VARCHAR2(30) := 'FEM_DIM_NO_STRUCTURE_DEFINED';
88 G_NO_FOLDER_PRIVS VARCHAR2(30) := 'FEM_DIM_MBR_LDR_FOLDER_PRIV';
89
90 ---------------------------------------
91 ------------------------
92 -- Declare Exceptions --
93 ------------------------
94 e_dimension_not_found EXCEPTION;
95 e_dim_load_not_enabled EXCEPTION;
96 e_invalid_simple_dim EXCEPTION;
97 e_invalid_number EXCEPTION;
98 e_invalid_number1722 EXCEPTION;
99 e_invalid_date EXCEPTION;
100 e_invalid_date_numeric EXCEPTION;
101 e_invalid_date_format EXCEPTION;
102 e_invalid_date_result EXCEPTION;
103 e_invalid_date_mask EXCEPTION;
104 e_invalid_date_between EXCEPTION;
105 e_invalid_date_year EXCEPTION;
106 e_invalid_date_day EXCEPTION;
107 e_invalid_date_month EXCEPTION;
108 e_date_string_too_long EXCEPTION;
109 e_invalid_calp_start_date EXCEPTION;
110 e_invalid_cal_period_end_date EXCEPTION;
111 e_invalid_cal_period_number EXCEPTION;
112 e_invalid_acct_year EXCEPTION;
113 e_terminate EXCEPTION;
114 e_main_terminate EXCEPTION;
115 e_mult_default_version EXCEPTION;
116 e_invalid_exec_mode EXCEPTION;
117 e_exec_lock_exists EXCEPTION;
118 e_unable_to_register_req EXCEPTION;
119 e_invalid_obj_def EXCEPTION;
120 e_pl_registration_failed EXCEPTION;
121 e_no_rows_to_load EXCEPTION;
122 e_no_structure_defined EXCEPTION;
123 e_no_folder_privs EXCEPTION;
124
125 PRAGMA EXCEPTION_INIT(e_invalid_number, -6502);
126 PRAGMA EXCEPTION_INIT(e_invalid_number1722, -1722);
127 PRAGMA EXCEPTION_INIT(e_invalid_date, -1843);
128 PRAGMA EXCEPTION_INIT(e_invalid_date_result, -1821);
129 PRAGMA EXCEPTION_INIT(e_date_string_too_long, -1830);
130 PRAGMA EXCEPTION_INIT(e_invalid_date_numeric, -1858);
131 PRAGMA EXCEPTION_INIT(e_invalid_date_between, -1841);
132 PRAGMA EXCEPTION_INIT(e_invalid_date_year, -1847);
133 PRAGMA EXCEPTION_INIT(e_invalid_date_format, -1861);
134 PRAGMA EXCEPTION_INIT(e_invalid_date_month, -1816);
135 PRAGMA EXCEPTION_INIT(e_invalid_date_day, -1839);
136
137 ---------------------------------------
138
139 ---------------------------------------------
140 -- Package Types
141 ---------------------------------------------
142 TYPE cv_curs IS REF CURSOR;
143 TYPE rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
144 TYPE number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
145 TYPE date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
146 TYPE varchar2_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
147 TYPE varchar2_150_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
148 TYPE desc_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
149 TYPE flag_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
150 TYPE lang_type IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
151 TYPE varchar2_1000_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
152
153
154
155 PROCEDURE Main (
156 errbuf OUT NOCOPY VARCHAR2
157 ,retcode OUT NOCOPY VARCHAR2
158 ,p_execution_mode IN VARCHAR2
159 ,p_dimension_id IN NUMBER
160 );
161
162 PROCEDURE Pre_Validation (p_eng_sql IN VARCHAR2
163 ,p_data_slc IN VARCHAR2
164 ,p_proc_num IN NUMBER
165 ,p_partition_code IN NUMBER
166 ,p_fetch_limit IN NUMBER
167 ,p_load_type IN VARCHAR2
168 ,p_dimension_varchar_label IN VARCHAR2
169 ,p_dimension_id IN NUMBER
170 ,p_source_b_table IN VARCHAR2
171 ,p_source_tl_table IN VARCHAR2
172 ,p_source_attr_table IN VARCHAR2
173 ,p_target_b_table IN VARCHAR2
174 ,p_member_t_dc_col IN VARCHAR2
175 ,p_member_dc_col IN VARCHAR2
176 ,p_value_set_required_flag IN VARCHAR2
177 ,p_simple_dimension_flag IN VARCHAR2
178 ,p_shared_dimension_flag IN VARCHAR2
179 ,p_exec_mode_clause IN VARCHAR2
180 ,p_master_request_id IN NUMBER);
181
182 PROCEDURE Pre_Validation_Attr (p_eng_sql IN VARCHAR2
183 ,p_data_slc IN VARCHAR2
184 ,p_proc_num IN VARCHAR2
185 ,p_partition_code IN NUMBER
186 ,p_fetch_limit IN NUMBER
187 ,p_load_type IN VARCHAR2
188 ,p_dimension_varchar_label IN VARCHAR2
189 ,p_dimension_id IN NUMBER
190 ,p_source_b_table IN VARCHAR2
191 ,p_source_tl_table IN VARCHAR2
192 ,p_source_attr_table IN VARCHAR2
193 ,p_target_b_table IN VARCHAR2
194 ,p_member_t_dc_col IN VARCHAR2
195 ,p_member_dc_col IN VARCHAR2
196 ,p_value_set_required_flag IN VARCHAR2
197 ,p_simple_dimension_flag IN VARCHAR2
198 ,p_shared_dimension_flag IN VARCHAR2
199 ,p_hier_dimension_flag IN VARCHAR2
200 ,p_exec_mode_clause IN VARCHAR2
201 ,p_master_request_id IN NUMBER);
202
203
204 PROCEDURE New_Members (p_eng_sql IN VARCHAR2
205 ,p_data_slc IN VARCHAR2
206 ,p_proc_num IN VARCHAR2
207 ,p_partition_code IN NUMBER
208 ,p_fetch_limit IN NUMBER
209 ,p_load_type IN VARCHAR2
210 ,p_dimension_varchar_label IN VARCHAR2
211 ,p_date_format_mask IN VARCHAR2
212 ,p_dimension_id IN VARCHAR2
213 ,p_target_b_table IN VARCHAR2
214 ,p_target_tl_table IN VARCHAR2
215 ,p_target_attr_table IN VARCHAR2
216 ,p_source_b_table IN VARCHAR2
217 ,p_source_tl_table IN VARCHAR2
218 ,p_source_attr_table IN VARCHAR2
219 ,p_table_handler_name IN VARCHAR2
220 ,p_member_col IN VARCHAR2
221 ,p_member_dc_col IN VARCHAR2
222 ,p_member_name_col IN VARCHAR2
223 ,p_member_t_dc_col IN VARCHAR2
224 ,p_member_t_name_col IN VARCHAR2
225 ,p_member_description_col IN VARCHAR2
226 ,p_value_set_required_flag IN VARCHAR2
227 ,p_simple_dimension_flag IN VARCHAR2
228 ,p_shared_dimension_flag IN VARCHAR2
229 ,p_hier_dimension_flag IN VARCHAR2
230 ,p_member_id_method_code IN VARCHAR2
231 ,p_exec_mode_clause IN VARCHAR2
232 ,p_master_request_id IN NUMBER);
233
234 PROCEDURE TL_Update (p_eng_sql IN VARCHAR2
235 ,p_data_slc IN VARCHAR2
236 ,p_proc_num IN VARCHAR2
237 ,p_partition_code IN NUMBER
238 ,p_fetch_limit IN NUMBER
239 ,p_load_type IN VARCHAR2
240 ,p_dimension_varchar_label IN VARCHAR2
241 ,p_dimension_id IN VARCHAR2
242 ,p_target_b_table IN VARCHAR2
243 ,p_target_tl_table IN VARCHAR2
244 ,p_source_b_table IN VARCHAR2
245 ,p_source_tl_table IN VARCHAR2
246 ,p_member_col IN VARCHAR2
247 ,p_member_dc_col IN VARCHAR2
248 ,p_member_name_col IN VARCHAR2
249 ,p_member_t_dc_col IN VARCHAR2
250 ,p_member_t_name_col IN VARCHAR2
251 ,p_member_description_col IN VARCHAR2
252 ,p_value_set_required_flag IN VARCHAR2
253 ,p_simple_dimension_flag IN VARCHAR2
254 ,p_shared_dimension_flag IN VARCHAR2
255 ,p_hier_dimension_flag IN VARCHAR2
256 ,p_exec_mode_clause IN VARCHAR2
257 ,p_master_request_id IN NUMBER);
258
259 PROCEDURE Base_Update (p_eng_sql IN VARCHAR2
260 ,p_data_slc IN VARCHAR2
261 ,p_proc_num IN VARCHAR2
262 ,p_partition_code IN NUMBER
263 ,p_fetch_limit IN NUMBER
264 ,p_load_type IN VARCHAR2
265 ,p_dimension_varchar_label IN VARCHAR2
266 ,p_simple_dimension_flag IN VARCHAR2
267 ,p_shared_dimension_flag IN VARCHAR2
268 ,p_dimension_id IN NUMBER
269 ,p_value_set_required_flag IN VARCHAR2
270 ,p_hier_table_name IN VARCHAR2
271 ,p_hier_dimension_flag IN VARCHAR2
272 ,p_source_b_table IN VARCHAR2
273 ,p_target_b_table IN VARCHAR2
274 ,p_member_dc_col IN VARCHAR2
275 ,p_member_t_dc_col IN VARCHAR2
276 ,p_member_col IN VARCHAR2
277 ,p_exec_mode_clause IN VARCHAR2
278 ,p_master_request_id IN NUMBER);
279
280 PROCEDURE Attr_Assign_Update (p_eng_sql IN VARCHAR2
281 ,p_data_slc IN VARCHAR2
282 ,p_proc_num IN VARCHAR2
283 ,p_partition_code IN NUMBER
284 ,p_fetch_limit IN NUMBER
285 ,p_dimension_varchar_label IN VARCHAR2
286 ,p_date_format_mask IN VARCHAR2
287 ,p_dimension_id IN VARCHAR2
288 ,p_target_b_table IN VARCHAR2
289 ,p_target_attr_table IN VARCHAR2
290 ,p_source_b_table IN VARCHAR2
291 ,p_source_attr_table IN VARCHAR2
292 ,p_member_col IN VARCHAR2
293 ,p_member_dc_col IN VARCHAR2
294 ,p_member_t_dc_col IN VARCHAR2
295 ,p_value_set_required_flag IN VARCHAR2
296 ,p_hier_dimension_flag IN VARCHAR2
297 ,p_simple_dimension_flag IN VARCHAR2
298 ,p_shared_dimension_flag IN VARCHAR2
299 ,p_exec_mode_clause IN VARCHAR2
300 ,p_master_request_id IN NUMBER);
301
302
303 PROCEDURE Post_Cal_Periods (p_eng_sql IN VARCHAR2
304 ,p_data_slc IN VARCHAR2
305 ,p_proc_num IN VARCHAR2
306 ,p_partition_code IN NUMBER
307 ,p_fetch_limit IN NUMBER
308 ,p_operation_mode IN VARCHAR2
309 ,p_master_request_id IN NUMBER);
310
311
312 /*PROCEDURE Process_Rows (x_status OUT NOCOPY NUMBER
313 ,x_message OUT NOCOPY VARCHAR2
314 ,x_rows_processed OUT NOCOPY NUMBER
315 ,x_rows_loaded OUT NOCOPY NUMBER
316 ,x_rows_rejected OUT NOCOPY NUMBER
317 ,p_eng_sql IN VARCHAR2
318 ,p_data_slc IN VARCHAR2
319 ,p_proc_num IN VARCHAR2
320 ,p_slice_id IN VARCHAR2
321 ,p_fetch_limit IN NUMBER
322 ,p_load_type IN VARCHAR2
323 ,p_dimension_varchar_label IN VARCHAR2
324 ,p_execution_mode IN VARCHAR2
325 ,p_structure_id IN NUMBER); */
326
327
328 END FEM_DIM_MEMBER_LOADER_PKG;
329
330
331
332
333
[Home] [Help]