[Home] [Help]
PACKAGE BODY: APPS.HXC_LOV_MIGRATION
Source
1 PACKAGE BODY hxc_lov_migration AS
2 /* $Header: hxclovmig.pkb 115.3 2004/03/30 15:57:33 mstewart noship $ */
3
4
5 -- =================================================================
6 -- == find_application_id
7 -- =================================================================
8 FUNCTION find_application_id
9 (p_application_short_name IN VARCHAR2
10 )
11 RETURN fnd_application.application_id%TYPE
12 IS
13 --
14 l_appl_id fnd_application.application_id%TYPE;
15 --
16 BEGIN
17 --
18 IF p_application_short_name IS NULL THEN
19 l_appl_id := NULL;
20 ELSE
21 SELECT application_id
22 INTO l_appl_id
23 FROM fnd_application
24 WHERE application_short_name = P_APPLICATION_SHORT_NAME;
25 END IF;
26 --
27 RETURN l_appl_id;
28 --
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 FND_MESSAGE.SET_NAME('HXC','HXC_INVALID_APPL_NAME');
32 FND_MESSAGE.RAISE_ERROR;
33 END find_application_id;
34
35 -- =================================================================
36 -- == get_region_row
37 -- =================================================================
38 FUNCTION get_region_row
39 (p_region_code IN ak_regions_vl.region_code%TYPE
40 ,p_application_id IN fnd_application.application_id%TYPE
41 )
42 RETURN ak_regions_vl%ROWTYPE
43 IS
44 l_region_row ak_regions_vl%ROWTYPE;
45 BEGIN
46 SELECT *
47 INTO l_region_row
48 FROM ak_regions_vl
49 WHERE region_code = p_region_code
50 AND region_application_id = p_application_id;
51
52 -- check region is an LOV region
53 IF l_region_row.region_style <> 'LOV' THEN
54 fnd_message.set_name('HXC', 'HXC_NOT_LOV_REGION');
55 fnd_message.raise_error;
56 END IF;
57
58 RETURN l_region_row;
59
60 EXCEPTION
61 WHEN NO_DATA_FOUND then
62 FND_MESSAGE.SET_NAME('HXC','HXC_AK_REGION_NOT_FOUND');
63 FND_MESSAGE.RAISE_ERROR;
64 END get_region_row;
65
66 PROCEDURE migrate_region
67 (p_region_code IN ak_regions_vl.region_code%TYPE
68 ,p_application_id IN fnd_application.application_id%TYPE
69 )
70 IS
71 --
72 region_row ak_regions_vl%ROWTYPE;
73
74 region_obj jdr_docbuilder.DOCUMENT;
75 top_level_element jdr_docbuilder.ELEMENT;
76 table_element jdr_docbuilder.ELEMENT;
77 attribute_element jdr_docbuilder.ELEMENT;
78
79 mapping_obj jdr_docbuilder.DOCUMENT;
80 mapping_element jdr_docbuilder.ELEMENT;
81
82 l_document_name VARCHAR2(80);
83 result_code PLS_INTEGER;
84
85 l_mapping_id NUMBER;
86 l_parent_id NUMBER;
87 l_max_sequence NUMBER;
88 --
89 CURSOR csr_get_region_items(p_ak_region_code VARCHAR2, p_ak_region_app_id NUMBER)
90 IS
91 SELECT *
92 FROM ak_region_items_vl
93 WHERE region_code = p_ak_region_code
94 AND region_application_id = p_ak_region_app_id
95 ORDER BY display_sequence;
96 --
97 BEGIN
98 -- verify the region exists, and is an LOV region and get the required attributes to set
99 region_row := get_region_row(p_region_code, p_application_id);
100
101 -- create the ak-mds mapping
102 mapping_obj := jdr_docbuilder.createChildDocument('/oracle/apps/hxc/regionMap/' || p_region_code);
103 mapping_element := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, 'listOfValues');
104 jdr_docbuilder.setAttribute(mapping_element, 'docName', p_region_code);
105 jdr_docbuilder.setAttribute(mapping_element, 'extends', '/oracle/apps/hxc/selfservice/configui/webui/' || p_region_code);
106 jdr_docbuilder.setAttribute(mapping_element, 'user:akRegionStyle', 'LOV');
107 jdr_docbuilder.setTopLevelElement(mapping_obj, mapping_element);
108
109 -- save the MDS document
110 result_code := jdr_docbuilder.save;
111
112 IF result_code <> jdr_docbuilder.SUCCESS THEN
113 fnd_message.set_name('HXC', 'HXC_MDS_MAP_SAVE_FAILED');
114 fnd_message.raise_error;
115 END IF;
116
117 -- create the mds document object
118 region_obj := jdr_docbuilder.createDocument('/oracle/apps/hxc/selfservice/configui/webui/' || p_region_code);
119
120 -- create the top level element
121 top_level_element := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, 'listOfValues');
122
123 jdr_docbuilder.setAttribute(top_level_element, 'amDefName', region_row.applicationmodule_object_type);
124 jdr_docbuilder.setAttribute(top_level_element, 'controllerClass', region_row.region_object_type);
125 jdr_docbuilder.setAttribute(top_level_element, 'title', region_row.name);
126 --jdr_docbuilder.setAttribute(top_level_element, 'xmlns', 'http://xmlns.oracle.com/jrad');
127 --jdr_docbuilder.setAttribute(top_level_element, 'xmlns:ui', 'http://xmlns.oracle.com/uix/ui');
128 --jdr_docbuilder.setAttribute(top_level_element, 'xmlns:oa', 'http://xmlns.oracle.com/oa');
129 --jdr_docbuilder.setAttribute(top_level_element, 'xmlns:user', 'http://xmlns.oracle.com/jrad/user');
130 jdr_docbuilder.setAttribute(top_level_element, 'file-version', '$Header: hxclovmig.pkb 115.3 2004/03/30 15:57:33 mstewart noship $');
131 --jdr_docbuilder.setAttribute(top_level_element, 'version', '9.0.3.6.2_398');
132
133 jdr_docbuilder.setTopLevelElement(region_obj, top_level_element);
134
135
136 table_element := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, 'table');
137 jdr_docbuilder.setAttribute(table_element, 'name', region_row.region_code);
138 jdr_docbuilder.setAttribute(table_element, 'id', region_row.region_code || '_lovTable');
139 jdr_docbuilder.setAttribute(table_element, 'akRegionCode', region_row.region_code);
140 jdr_docbuilder.setAttribute(table_element, 'regionName', region_row.name);
141 jdr_docbuilder.setAttribute(table_element, 'blockSize', region_row.num_rows_display);
142 jdr_docbuilder.setAttribute(table_element, 'standalone', 'true');
143
144 jdr_docbuilder.addChild(top_level_element, jdr_docbuilder.UI_NS, 'contents', table_element);
145
146 -- now loop through the child elements, adding a node for each one
147 FOR region_attr_row IN csr_get_region_items(p_region_code, p_application_id) LOOP
148 attribute_element := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, 'messageStyledText');
149 IF region_attr_row.node_query_flag = 'Y' THEN
150 jdr_docbuilder.setAttribute(attribute_element, 'queryable', 'true');
151 END IF;
152
153 IF region_attr_row.node_display_flag = 'N' THEN
154 jdr_docbuilder.setAttribute(attribute_element, 'rendered', 'false');
155 END IF;
156
157 jdr_docbuilder.setAttribute(attribute_element, 'vAlign', region_attr_row.vertical_alignment);
158 jdr_docbuilder.setAttribute(attribute_element, 'columns', region_attr_row.display_value_length);
159 jdr_docbuilder.setAttribute(attribute_element, 'prompt', region_attr_row.attribute_label_long);
160
161 IF region_attr_row.data_type IN ('NUMBER', 'DATE') THEN
162 jdr_docbuilder.setAttribute(attribute_element, 'dataType', region_attr_row.data_type);
163 END IF;
164
165 jdr_docbuilder.setAttribute(attribute_element, 'rows', region_attr_row.display_height);
166 jdr_docbuilder.setAttribute(attribute_element, 'viewName', region_attr_row.view_usage_name);
167 jdr_docbuilder.setAttribute(attribute_element, 'viewAttr', region_attr_row.view_attribute_name);
168 jdr_docbuilder.setAttribute(attribute_element, 'maximumLength', region_attr_row.attribute_value_length);
169 jdr_docbuilder.setAttribute(attribute_element, 'id', region_attr_row.item_name);
170 jdr_docbuilder.setAttribute(attribute_element, 'promptTranslationExpansion', '100%');
171 jdr_docbuilder.setAttribute(attribute_element, 'user:akAttributeCode', region_attr_row.attribute_code);
172 jdr_docbuilder.setAttribute(attribute_element, 'user:akAttributeApplicationId', region_attr_row.attribute_application_id);
173
174 IF region_attr_row.data_type = 'DATE' THEN
175 jdr_docbuilder.setAttribute(attribute_element, 'tipType', 'dateFormat');
176 END IF;
177
178 jdr_docbuilder.addChild(table_element, jdr_docbuilder.UI_NS, 'contents', attribute_element);
179 END LOOP;
180
181 -- save the MDS document
182 result_code := jdr_docbuilder.save;
183
184 IF result_code <> jdr_docbuilder.SUCCESS THEN
185 fnd_message.set_name('HXC', 'HXC_MDS_SAVE_FAILED');
186 fnd_message.raise_error;
187 END IF;
188
189 END migrate_region;
190
191 FUNCTION mds_doc_exists
192 (p_region_code IN AK_REGIONS_VL.REGION_CODE%TYPE
193 )
194 RETURN BOOLEAN
195 IS
196 BEGIN
197 --
198 RETURN jdr_docbuilder.documentExists('/oracle/apps/hxc/selfservice/configui/webui/' || p_region_code);
199 --
200 END mds_doc_exists;
201
202 -- =================================================================
203 -- == migrate_lov_region
204 -- =================================================================
205 PROCEDURE migrate_lov_region
206 (p_region_code IN AK_REGIONS_VL.REGION_CODE%TYPE DEFAULT NULL
207 ,p_region_app_short_name IN FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE DEFAULT NULL
208 ,p_force IN VARCHAR2 DEFAULT NULL
209 )
210 IS
211 --
212 region_app_id fnd_application.application_id%TYPE;
213 l_app_short_name FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE DEFAULT NULL;
214 l_force BOOLEAN := FALSE;
215 --
216 CURSOR csr_get_regions IS
217 SELECT region_code
218 FROM ak_regions
219 WHERE region_application_id = 809
220 AND region_style = 'LOV'
221 AND region_code NOT IN
222 ('HXCAPPROVALPEOPLELOV'
223 ,'HXC_CUI_LOV_PROJECT'
224 ,'HXC_CUI_LOV_TASK'
225 ,'HXC_CUI_LOV_EXPTYPE'
226 ,'HXC_CUI_LOV_SYSLINKFUNC'
227 ,'HXC_CUI_PROJECT_LOV'
228 ,'HXC_CUI_EXPTYPE_LOV'
229 ,'HXC_CUI_TASK_LOV'
230 ,'HXC_CUI_LOV_EXPTYPE_ELEMENT'
231 ,'HXC_CUI_OVERRIDE_APPROVER_LOV'
232 ,'HXC_CUI_PROJECT_B_LOV'
233 ,'HXC_CUI_TASK_B_LOV');
234 --
235 BEGIN
236 --
237 IF p_force IS NOT NULL THEN
238 IF p_force = 'Y' THEN
239 l_force := TRUE;
240 END IF;
241 END IF;
242
243 -- default the app if not set
244 IF (p_region_app_short_name IS NULL) THEN
245 l_app_short_name := 'HXC';
246 ELSE
247 l_app_short_name := p_region_app_short_name;
248 END IF;
249
250 -- validate the app short name and get the id
251 region_app_id := find_application_id(l_app_short_name);
252
253 -- now determine if we are going to migrate multiple LOV regions
254 -- or just a specific one
255 IF (p_region_code IS NULL) THEN
256 -- migrate all non-seeded hxc regions
257 FOR mig_region_row IN csr_get_regions LOOP
258 --
259 IF (l_force OR (NOT l_force AND NOT mds_doc_exists(mig_region_row.region_code))) THEN
260 migrate_region(mig_region_row.region_code, region_app_id);
261 END IF;
262 --
263 END LOOP;
264 ELSE
265 -- migrate the single specified region
266 IF (l_force OR (NOT l_force AND NOT mds_doc_exists(p_region_code))) THEN
267 migrate_region(p_region_code, region_app_id);
268 END IF;
269 END IF;
270 END migrate_lov_region;
271 END hxc_lov_migration;