[Home] [Help]
PACKAGE: APPS.FEM_DIMENSION_MIGRATION_PKG
Source
1 PACKAGE FEM_DIMENSION_MIGRATION_PKG AUTHID CURRENT_USER AS
2 -- $Header: femdimmig_pkh.pls 120.1 2005/07/25 14:45:43 appldev noship $
3 /*==========================================================================+
4 | Copyright (c) 2005 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | femdimmig_pkh.pls
10 |
11 | NAME fem_dimension_migration_pkg
12 |
13 | DESCRIPTION
14 |
15 | Package fem_dimension_migration_pkg. This package is the engine for
16 | migrating dimension members, member translatable names/descriptions,
17 | and member attribute assignments from a source database into the dimension
18 | member loader interface tables in the target database
19 |
20 | FUNCTIONS/PROCEDURES
21 |
22 | Main
23 | errbuf OUT NOCOPY VARCHAR2
24 | retcode OUT NOCOPY VARCHAR2
25 | p_execution_mode IN VARCHAR2
26 | p_object_definition_id IN NUMBER DEFAULT 1200
27 | p_dimension_varchar_label IN VARCHAR2
28 | p_date_format_mask IN VARCHAR2
29 |
30 | NOTES
31 |
32 |
33 | HISTORY
34 |
35 | 25-APR-05 PRANDALL Created
36 +=========================================================================*/
37
38 ---------------------------------------------
39 -- Package Constants
40 ---------------------------------------------
41 c_block CONSTANT VARCHAR2(80) := 'fem.plsql.fem_dimension_migration_pkg';
42 c_fem CONSTANT VARCHAR2(3) := 'FEM';
43 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
44 c_object_version_number CONSTANT NUMBER := 1;
45 c_enabled_flag VARCHAR2(1) := 'Y';
46 c_personal_flag VARCHAR2(1) := 'Y';
47 c_read_only_flag VARCHAR2(1) := 'Y';
48
49 c_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
50 c_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
51 c_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
52 c_error CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
53 c_unexp CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
54 c_api_version CONSTANT NUMBER := 1.0;
55 c_fetch_limit CONSTANT NUMBER := 99999;
56
57
58
59 ---------Message Constants--------------
60 --G_DIM_NOT_FOUND VARCHAR2(30) := 'FEM_DIM_NOT_FOUND';
61 --G_INVALID_SIMPLE_DIM VARCHAR2(30) := 'FEM_DIM_LOADS_NOT_ALLOWED';
62 --G_INVALID_DATE_FORMAT VARCHAR2(30) := 'FEM_INVALID_DATE_FORMAT';
63 --G_INVALID_EXEC_MODE VARCHAR2(30) := 'FEM_DIM_MEMBER_LDR_EXEC_MODE';
64 G_EXEC_LOCK_EXISTS VARCHAR2(30) := 'FEM_PL_OBJ_EXECLOCK_EXISTS_ERR';
65 --G_INVALID_OBJ_DEF VARCHAR2(30) := 'FEM_DATAX_LDR_BAD_OBJ_ERR';
66 --G_EXT_LDR_POST_PROC_ERR VARCHAR2(30) := 'FEM_EXT_LDR_POST_PROC_ERR';
67 --G_EXT_LDR_EXEC_STATUS VARCHAR2(30) := 'FEM_EXT_LDR_EXEC_STATUS';
68 G_PL_REG_REQUEST_ERR VARCHAR2(30) := 'FEM_PL_REG_REQUEST_ERR';
69 G_PL_OBJ_EXEC_LOCK_ERR VARCHAR2(30) := 'FEM_PL_OBJ_EXEC_LOCK_ERR';
70 --G_MULT_DEFAULT_VERSION VARCHAR2(30) := 'FEM_TOO_MANY_DEFAULT_VERSIONS';
71 --G_NO_ROWS_TO_LOAD VARCHAR2(30) := 'FEM_DIM_MBR_LDR_NO_ROWS_LOAD';
72 G_MESSAGE VARCHAR2(30) := 'FEM_MISSING_MESSAGE';
73
74 G_DB_LINK_NOT_REGISTERED VARCHAR2(30) := 'FEM_DM_DB_LINK_NOT_REGISTERED';
75 G_DB_LINK_NOT_FUNCTIONAL VARCHAR2(30) := 'FEM_DM_DB_LINK_NOT_FUNCTIONAL';
76 G_INVALID_VERSION_PARAM VARCHAR2(30) := 'FEM_DM_INV_VERSION_PARAM';
77 G_MISSING_VERSION_PARAM VARCHAR2(30) := 'FEM_DM_MISSING_VERSION_PARAM';
78 G_INVALID_SRC_USER_DIM VARCHAR2(30) := 'FEM_DM_INV_SOURCE_USER_DIM';
79 G_USER_DIM_MISMATCH VARCHAR2(30) := 'FEM_DM_USER_DIM_MISMATCH';
80 G_VERSION_EXISTS VARCHAR2(30) := 'FEM_DM_VERSION_EXISTS';
81 G_INVALID_HIERARCHY VARCHAR2(30) := 'FEM_DM_INV_HIER_OBJ';
82 G_INVALID_HIER_VERSION VARCHAR2(30) := 'FEM_DM_INV_HIER_OBJ_DEF';
83 G_MISSING_LANG VARCHAR2(30) := 'FEM_DM_MISSING_LANG';
84 G_DIM_NOT_SUPPORTED VARCHAR2(30) := 'FEM_DM_DIM_CANNOT_MIGRATE';
85 G_INVALID_DIMENSION VARCHAR2(30) := 'FEM_DM_INVALID_DIMENSION';
86 G_UNHANDLED_ERROR VARCHAR2(30) := 'FEM_DM_UNHANDLED_ERROR';
87 G_INSERT_ERROR VARCHAR2(30) := 'FEM_DM_INSERT_ERROR';
88 G_PL_MIGRATION_ERROR VARCHAR2(30) := 'FEM_DM_PL_EXEC_ERROR';
89 G_HIERARCHY_RULE_EXISTS VARCHAR2(30) := 'FEM_DM_TGT_HIER_EXISTS';
90 G_INVALID_VERSION_DISP_CD VARCHAR2(30) := 'FEM_DM_INV_VERSION_DC';
91 G_INVALID_VERSION_NAME VARCHAR2(30) := 'FEM_DM_INV_VERSION_NAME';
92 G_DIM_HIER_NOT_SUPPORTED VARCHAR2(30) := 'FEM_DM_DIM_HIER_NOT_SUPPORTED';
93
94
95 ---------------------------------------
96 ------------------------
97 -- Declare Exceptions --
98 ------------------------
99 /*e_dimension_not_found EXCEPTION;
100 e_dim_load_not_enabled EXCEPTION;
101 e_invalid_simple_dim EXCEPTION;
102 e_invalid_number EXCEPTION;
103 e_invalid_number1722 EXCEPTION;
104 e_invalid_date EXCEPTION;
105 e_invalid_date_numeric EXCEPTION;
106 e_invalid_date_format EXCEPTION;
107 e_invalid_date_result EXCEPTION;
108 e_invalid_date_mask EXCEPTION;
109 e_invalid_date_between EXCEPTION;
110 e_invalid_date_year EXCEPTION;
111 e_invalid_date_day EXCEPTION;
112 e_invalid_date_month EXCEPTION;
113 e_date_string_too_long EXCEPTION;
114 e_invalid_cal_period_end_date EXCEPTION;
115 e_invalid_cal_period_number EXCEPTION;
116 e_invalid_acct_year EXCEPTION;
117 e_terminate EXCEPTION;
118 e_main_terminate EXCEPTION;
119 e_mult_default_version EXCEPTION;
120 e_invalid_exec_mode EXCEPTION;
121 e_exec_lock_exists EXCEPTION;
122 e_unable_to_register_req EXCEPTION;
123 e_invalid_obj_def EXCEPTION;
124 e_pl_registration_failed EXCEPTION;
125 e_no_rows_to_load EXCEPTION;*/
126
127
128 e_pl_registration_failed EXCEPTION;
129 e_dimension_not_supported EXCEPTION;
130 e_invalid_dimension EXCEPTION;
131 e_db_link_not_registered EXCEPTION;
132 e_db_link_not_functional EXCEPTION;
133 e_invalid_version_param EXCEPTION;
134 e_dim_not_user_extensible EXCEPTION;
135 e_missing_version_params EXCEPTION;
136 e_invalid_obj_def EXCEPTION;
137 --e_invalid_source_dim_name EXCEPTION;
138 e_invalid_version_name EXCEPTION;
139 e_invalid_version_display_code EXCEPTION;
140 e_invalid_source_vs_name EXCEPTION;
141 e_terminate EXCEPTION;
142 e_main_terminate EXCEPTION;
143 e_post_process EXCEPTION;
144 e_src_dim_not_user_extensible EXCEPTION;
145 e_invalid_hierarchy EXCEPTION;
146 e_invalid_hierarchy_version EXCEPTION;
147 e_target_hierarchy_exists EXCEPTION;
148 e_insert_b_exception EXCEPTION;
149 e_insert_tl_exception EXCEPTION;
150 e_insert_attr_exception EXCEPTION;
151 e_insert_hier_exception EXCEPTION;
152 e_dim_hier_not_supported EXCEPTION;
153
154 /*PRAGMA EXCEPTION_INIT(e_invalid_number, -6502);
155 PRAGMA EXCEPTION_INIT(e_invalid_number1722, -1722);
156 PRAGMA EXCEPTION_INIT(e_invalid_date, -1843);
157 PRAGMA EXCEPTION_INIT(e_invalid_date_result, -1821);
158 PRAGMA EXCEPTION_INIT(e_date_string_too_long, -1830);
159 PRAGMA EXCEPTION_INIT(e_invalid_date_numeric, -1858);
160 PRAGMA EXCEPTION_INIT(e_invalid_date_between, -1841);
161 PRAGMA EXCEPTION_INIT(e_invalid_date_year, -1847);
162 PRAGMA EXCEPTION_INIT(e_invalid_date_format, -1861);
163 PRAGMA EXCEPTION_INIT(e_invalid_date_month, -1816);
164 PRAGMA EXCEPTION_INIT(e_invalid_date_day, -1839);*/
165
166 ---------------------------------------
167
168 ---------------------------------------------
169 -- Package Types
170 ---------------------------------------------
171 TYPE cv_curs IS REF CURSOR;
172 TYPE rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
173 TYPE number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
174 TYPE date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
175 TYPE varchar2_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
176 TYPE varchar2_150_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
177 TYPE desc_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
178 TYPE flag_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
179 TYPE lang_type IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
180 TYPE varchar2_1000_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
181
182 TYPE DIMENSION_PROPS_REC IS RECORD(
183 DIMENSION_ID FEM_XDIM_DIMENSIONS.DIMENSION_ID%TYPE,
184 MIGRATION_OBJ_ID NUMBER,
185 MIGRATION_OBJ_DEF_ID NUMBER,
186 USER_DEFINED_FLAG FEM_XDIM_DIMENSIONS.USER_DEFINED_FLAG%TYPE,
187 GROUP_USE_CODE FEM_XDIM_DIMENSIONS.GROUP_USE_CODE%TYPE,
188 SIMPLE_DIMENSION_FLAG FEM_XDIM_DIMENSIONS.SIMPLE_DIMENSION_FLAG%TYPE,
189 VALUE_SET_REQUIRED_FLAG FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%TYPE,
190 INTF_MEMBER_B_TABLE_NAME FEM_XDIM_DIMENSIONS.INTF_MEMBER_B_TABLE_NAME%TYPE,
191 INTF_MEMBER_TL_TABLE_NAME FEM_XDIM_DIMENSIONS.INTF_MEMBER_TL_TABLE_NAME%TYPE,
192 INTF_ATTRIBUTE_TABLE_NAME FEM_XDIM_DIMENSIONS.INTF_ATTRIBUTE_TABLE_NAME %TYPE,
193 MEMBER_B_TABLE_NAME FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE,
194 MEMBER_TL_TABLE_NAME FEM_XDIM_DIMENSIONS.MEMBER_TL_TABLE_NAME %TYPE,
195 ATTRIBUTE_TABLE_NAME FEM_XDIM_DIMENSIONS.ATTRIBUTE_TABLE_NAME%TYPE,
196 MEMBER_COL FEM_XDIM_DIMENSIONS.MEMBER_COL%TYPE,
197 MEMBER_DISPLAY_CODE_COL FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE,
198 MEMBER_NAME_COL FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%TYPE,
199 MEMBER_DESCRIPTION_COL FEM_XDIM_DIMENSIONS.MEMBER_DESCRIPTION_COL%TYPE,
200 HIERARCHY_TABLE_NAME FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%TYPE,
201 HIERARCHY_INTF_TABLE_NAME FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%TYPE);
202
203
204 FUNCTION GET_ATTR_ASSIGN_VALUE(p_source_db_link IN VARCHAR2,
205 p_dimension_id IN NUMBER,
206 p_value IN VARCHAR2 ) RETURN VARCHAR2;
207
208
209 PROCEDURE MIGRATE_MEMBERS(x_retcode OUT NOCOPY VARCHAR2,
210 x_errug OUT NOCOPY VARCHAR2,
211 p_source_db_link IN VARCHAR2,
212 p_dim_varchar_lbl IN VARCHAR2,
213 p_autoload_dims IN VARCHAR2,
214 p_migrate_dependent_dims IN VARCHAR2,
215 p_version_mode IN VARCHAR2,
216 p_version_disp_cd IN VARCHAR2,
217 p_version_name IN VARCHAR2,
218 p_version_desc IN VARCHAR2,
219 p_hier_obj_name IN VARCHAR2,
220 p_hier_obj_def_name IN VARCHAR2,
221 p_source_user_dim_name IN VARCHAR2);
222
223 PROCEDURE MIGRATE_MEMBERS(x_retcode OUT NOCOPY VARCHAR2,
224 x_errug OUT NOCOPY VARCHAR2,
225 p_source_db_link IN VARCHAR2,
226 p_dim_varchar_lbl IN VARCHAR2,
227 -- p_version_mode IN VARCHAR2,
228 -- p_version_disp_cd IN VARCHAR2,
229 -- p_version_name IN VARCHAR2,
230 -- p_version_desc IN VARCHAR2,
231 p_source_user_dim_name IN VARCHAR2);
232
233 PROCEDURE MIGRATE_HIERARCHY(x_retcode OUT NOCOPY VARCHAR2,
234 x_errug OUT NOCOPY VARCHAR2,
235 p_source_db_link IN VARCHAR2,
236 p_dim_varchar_lbl IN VARCHAR2,
237 p_hier_obj_name IN VARCHAR2,
238 p_hier_obj_def_name IN VARCHAR2,
239 p_source_user_dim_name IN VARCHAR2);
240
241 END FEM_DIMENSION_MIGRATION_PKG;