[Home] [Help]
PACKAGE BODY: APPS.WMS_CONFIG_UI
Source
1 PACKAGE BODY WMS_CONFIG_UI AS
2 /* $Header: WMSCFGUIB.pls 115.2 2004/01/22 21:42:02 kkandiku noship $ */
3
4
5 g_version_printed BOOLEAN := FALSE;
6 g_pkg_name VARCHAR2 (30) := 'WMS_CONFIG_UI';
7
8 PROCEDURE DEBUG (p_message IN VARCHAR2, p_module IN VARCHAR2, p_level NUMBER)
9 IS
10 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
11 BEGIN
12 IF NOT g_version_printed
13 THEN
14 inv_log_util.TRACE ('$Header: WMSCFGUIB.pls 115.2 2004/01/22 21:42:02 kkandiku noship $',
15 g_pkg_name,
16 9
17 );
18 g_version_printed := TRUE;
19 END IF;
20
21 IF (l_debug = 1)
22 THEN
23 inv_log_util.TRACE (p_message,
24 g_pkg_name || '.' || p_module,
25 p_level
26 );
27 END IF;
28 END;
29
30 /**
31 * This procedure returns the fields' metadata + config values for
32 * the given page, template as a ref cursor.
33 * If the given tenplate is null or does not exist, the
34 * fields' metadata + config values for the default template are returned.
35 * If the default tenplate does not exist, the fields' metadata + config values
36 * for the default template are returned.
37
38 * @param p_page_id The page for which the fields are required
39 * @param p_template_name The template name for which the fields are required
40 * @param p_organization_id Organization ID
41 * @param x_page_template_fields Cursor containg the fields list
42 **/
43 PROCEDURE get_fields(
44 p_page_id IN NUMBER DEFAULT NULL,
45 p_template_name IN VARCHAR2 DEFAULT NULL,
46 p_organization_id IN NUMBER DEFAULT NULL,
47 p_get_page_level_props IN VARCHAR2 DEFAULT 'N',
48 x_page_template_fields OUT NOCOPY t_fields_list, -- Page Template fields. Does not contain page level properties
49 x_page_level_props OUT NOCOPY t_page_level_props, -- Page level properties
50 x_return_template OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2,
53 x_return_status OUT NOCOPY VARCHAR2)
54 IS
55 l_module_name CONSTANT VARCHAR2 (30) := 'GET_FIELDS';
56 l_template_id NUMBER;
57 l_temp_template_id NUMBER;
58 BEGIN
59 DEBUG (' p_page_id ==> ' || p_page_id, l_module_name, 9);
60 DEBUG (' p_template_name ==> ' || p_template_name, l_module_name, 9);
61 DEBUG (' p_organization_id ==> ' || p_organization_id, l_module_name, 9);
62 DEBUG (' p_get_page_level_props==> ' || p_get_page_level_props, l_module_name, 9);
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 IF (p_page_id IS NULL) OR (p_organization_id IS NULL) THEN
65 DEBUG (' Not Enough Inputs: pageId or OrgId is NULL', l_module_name, 9);
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68 -- Get the template Id for the given page, template name and Org
69 get_template(
70 p_page_id => p_page_id,
71 p_template_name => p_template_name,
72 p_organization_id => p_organization_id,
73 x_template_id => l_template_id,
74 x_return_template => x_return_template,
75 x_msg_count => x_msg_count,
76 x_msg_data => x_msg_data,
77 x_return_status => x_return_status);
78 IF x_return_status <> 'S' THEN
79 DEBUG(' Error in getting the Template id ', l_module_name, 9);
80 l_template_id := -1;
81 DEBUG(' Defaulting Temaplte ID : ' || l_template_id, l_module_name, 9);
82 ELSE
83 DEBUG(' Found Temaplte ID : ' || l_template_id, l_module_name, 9);
84 END IF;
85
86 IF(p_page_id = 2) THEN
87 l_temp_template_id := -1;
88 ELSE
89 l_temp_template_id := l_template_id;
90 END IF;
91 DEBUG (' Fetching the page fields', l_module_name, 9);
92 OPEN x_page_template_fields FOR
93 SELECT PAGE_ID
94 , FIELD_ID
95 , FIELD_DISP_SEQUENCE_NUMBER
96 , FIELD_NAME
97 , FIELD_TYPE
98 , FIELD_CONSTRUCTOR_PARAM
99 , FIELD_PROMPT
100 , FIELD_IS_CONFIGURABLE
101 , FIELD_CATEGORY
102 , FIELD_PROPERTY1_DEFAULT_VALUE
103 , FIELD_PROPERTY2_DEFAULT_VALUE
104 , FIELD_IS_VISIBLE
105 , TEMPLATE_ID
106 , TEMPLATE_NAME
107 , USER_TEMPLATE_NAME
108 , TEMPLATE_DESCRIPTION
109 , CREATING_ORGANIZATION_ID
110 , CREATING_ORGANIZATION_CODE
111 , COMMON_TO_ALL_ORGS
112 , ENABLED
113 , FIELD_PROPERTY1_VALUE
114 , FIELD_PROPERTY2_VALUE
115 FROM WMS_PAGE_CONFIG_UI_V
116 WHERE PAGE_ID = p_page_id
117 AND TEMPLATE_ID = -1
118 AND FIELD_IS_CONFIGURABLE <> 'Y'
119 AND FIELD_CATEGORY <> 'PAGE'
120 UNION ALL
121 SELECT PAGE_ID
122 , FIELD_ID
123 , FIELD_DISP_SEQUENCE_NUMBER
124 , FIELD_NAME
125 , FIELD_TYPE
126 , FIELD_CONSTRUCTOR_PARAM
127 , FIELD_PROMPT
128 , FIELD_IS_CONFIGURABLE
129 , FIELD_CATEGORY
130 , FIELD_PROPERTY1_DEFAULT_VALUE
131 , FIELD_PROPERTY2_DEFAULT_VALUE
132 , FIELD_IS_VISIBLE
133 , TEMPLATE_ID
134 , TEMPLATE_NAME
135 , USER_TEMPLATE_NAME
136 , TEMPLATE_DESCRIPTION
137 , CREATING_ORGANIZATION_ID
138 , CREATING_ORGANIZATION_CODE
139 , COMMON_TO_ALL_ORGS
140 , ENABLED
141 , FIELD_PROPERTY1_VALUE
142 , FIELD_PROPERTY2_VALUE
143 FROM WMS_PAGE_CONFIG_UI_V
144 WHERE PAGE_ID = p_page_id
145 AND TEMPLATE_ID = l_temp_template_id
146 AND FIELD_IS_CONFIGURABLE = 'Y'
147 AND FIELD_CATEGORY <> 'PAGE'
148 ORDER BY FIELD_DISP_SEQUENCE_NUMBER;
149
150 -- Get page level properties
151 IF(p_get_page_level_props = 'Y') THEN
152 DEBUG (' Fetching the page level properties', l_module_name, 9);
153 IF l_template_id <> -1 THEN
154 OPEN x_page_level_props FOR
155 SELECT WPF.FIELD_NAME, WPF.FIELD_CATEGORY, WTF.FIELD_PROPERTY1_VALUE, WTF.FIELD_PROPERTY2_VALUE, WPF.FIELD_PROPERTY1_DEFAULT_VALUE, WPF.FIELD_PROPERTY2_DEFAULT_VALUE
156 FROM WMS_PAGE_FIELDS_VL WPF, WMS_PAGE_TEMPLATE_FIELDS WTF
157 WHERE WPF.PAGE_ID = WTF.PAGE_ID
158 AND WPF.FIELD_ID = WTF.FIELD_ID
159 AND WPF.PAGE_ID = p_page_id
160 AND WTF.TEMPLATE_ID = l_template_id
161 AND WPF.FIELD_CATEGORY = 'PAGE';
162 ELSE
163 OPEN x_page_level_props FOR
164 SELECT FIELD_NAME, FIELD_CATEGORY, -1, -1, FIELD_PROPERTY1_DEFAULT_VALUE, FIELD_PROPERTY2_DEFAULT_VALUE
165 FROM WMS_PAGE_FIELDS_VL
166 WHERE PAGE_ID = p_page_id
167 AND FIELD_CATEGORY = 'PAGE';
168 END IF;
169 END IF;
170 EXCEPTION
171 WHEN OTHERS THEN
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 DEBUG (' Error in getting the fields ',l_module_name, 9);
174 DEBUG (' ERROR CODE = ' || SQLCODE, l_module_name, 9);
175 DEBUG (' ERROR MESSAGE = ' || SQLERRM, l_module_name, 9);
176 END get_fields;
177
178
179 /**
180 * This procedure returns the template id for the the given page, template name
181 * and organization
182 * If the given tenplate is null or does not exist, the default template id is returned
183 * If the default tenplate does not exist, -1 is returned
184
185 * @param p_page_id The page for which the fields are required
186 * @param p_template_name The template name for which the fields are required
187 * @param p_organization_id Organization ID
188 * @param x_template_id Template ID
189 **/
190 PROCEDURE get_template(
191 p_page_id IN NUMBER DEFAULT NULL,
192 p_template_name IN VARCHAR2 DEFAULT NULL,
193 p_organization_id IN NUMBER DEFAULT NULL,
194 x_template_id OUT NOCOPY NUMBER,
195 x_return_template OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2)
199 IS
200 l_module_name CONSTANT VARCHAR2 (30) := 'GET_TEMPLATE';
201 l_parent_page_id NUMBER;
202 BEGIN
203 DEBUG (' p_page_id ==> ' || p_page_id, l_module_name, 9);
204 DEBUG (' p_template_name ==> ' || p_template_name, l_module_name, 9);
208 DEBUG (' Not Enough Inputs, pageId or OrgId is NULL', l_module_name, 9);
205 DEBUG (' p_organization_id ==> ' || p_organization_id, l_module_name, 9);
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207 IF (p_page_id IS NULL) OR (p_organization_id IS NULL) THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 IF p_page_id = 2 THEN
213 l_parent_page_id := 1;
214 END IF;
215
216 IF(p_template_name IS NOT NULL) THEN
217 BEGIN
218 select template_id, template_name
219 into x_template_id, x_return_template
220 from WMS_PAGE_TEMPLATES_VL
221 where page_id in (p_page_id, nvl(l_parent_page_id, p_page_id ))
222 and template_name = p_template_name
223 and enabled = 'Y'
224 and decode(common_to_all_orgs,
225 'Y', -99,
226 'N', creating_organization_id,
227 creating_organization_id) = decode (common_to_all_orgs, 'Y', -99,
228 'N', p_organization_id,
229 p_organization_id);
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 DEBUG (' INVALID TEMAPLTE : The given template does not exist ',l_module_name, 9);
233 WHEN OTHERS THEN
234 DEBUG (' Error in getting the Template id ',l_module_name, 9);
235 DEBUG (' ERROR CODE = ' || SQLCODE, l_module_name, 9);
236 DEBUG (' ERROR MESSAGE = ' || SQLERRM, l_module_name, 9);
237 END;
238 END IF;
239 DEBUG (' x_template_id ==> ' || x_template_id, l_module_name, 9);
240 DEBUG (' x_return_template ==> ' || x_return_template, l_module_name, 9);
241
242 -- If template Name is null or the given tenplate name does not exist
243 -- get the default template for the given Page
244 IF(p_template_name IS NULL) OR (x_template_id IS NULL)THEN
245 DEBUG (' Getting the Default Template for the Page' || x_template_id, l_module_name, 9);
246 BEGIN
247 select template_id, template_name
248 into x_template_id, x_return_template
249 from WMS_PAGE_TEMPLATES_VL
250 where page_id in (p_page_id, nvl(l_parent_page_id, p_page_id ))
251 and enabled = 'Y'
252 and default_flag = 'Y';
253 DEBUG (' x_template_id ==> ' || x_template_id, l_module_name, 9);
254 DEBUG (' x_return_template ==> ' || x_return_template, l_module_name, 9);
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 DEBUG (' Default Template Does Not Exist ',l_module_name, 9);
258 x_template_id := -1;
259 WHEN OTHERS THEN
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 x_template_id := -1;
262 DEBUG (' Error in getting the Template id ',l_module_name, 9);
263 DEBUG (' ERROR CODE = ' || SQLCODE, l_module_name, 9);
264 DEBUG (' ERROR MESSAGE = ' || SQLERRM, l_module_name, 9);
265 END;
266 END IF;
267 END get_template;
268
269 /**
270 * This procedure returns the fields' metadata + config values for
271 * the given page, template as a ref cursor.
275 * for the default template are returned.
272 * If the given tenplate is null or does not exist, the
273 * fields' metadata + config values for the default template are returned.
274 * If the default tenplate does not exist, the fields' metadata + config values
276
277 * @param p_page_id The page for which the fields are required
278 * @param p_template_name The template name for which the fields are required
279 * @param p_organization_id Organization ID
280 * @param x_page_template_fields Cursor containg the fields list
281 **/
282 PROCEDURE get_field_properties(
283 p_page_id IN NUMBER DEFAULT NULL,
284 p_template_name IN VARCHAR2 DEFAULT NULL,
285 p_organization_id IN NUMBER DEFAULT NULL,
286 p_field_name IN VARCHAR2 DEFAULT NULL,
287 x_field_properties OUT NOCOPY t_fields_list, -- Does not contain page level properties
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2,
290 x_return_status OUT NOCOPY VARCHAR2)
291 IS
292 l_module_name CONSTANT VARCHAR2 (30) := 'GET_FIELD_PROPERTIES';
293 l_template_id NUMBER;
294 l_return_template VARCHAR2 (128);
295 BEGIN
296 DEBUG (' p_page_id ==> ' || p_page_id, l_module_name, 9);
297 DEBUG (' p_template_name ==> ' || p_template_name, l_module_name, 9);
298 DEBUG (' p_field_name ==> ' || p_field_name, l_module_name, 9);
299 DEBUG (' p_organization_id ==> ' || p_organization_id, l_module_name, 9);
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301 IF (p_page_id IS NULL) OR (p_organization_id IS NULL) THEN
302 DEBUG (' Not Enough Inputs, pageId or OrgId is NULL', l_module_name, 9);
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305 -- Get the template Id for the given page, template name and Org
306 get_template(
307 p_page_id => p_page_id,
308 p_template_name => p_template_name,
309 p_organization_id => p_organization_id,
310 x_template_id => l_template_id,
311 x_return_template => l_return_template,
312 x_msg_count => x_msg_count,
313 x_msg_data => x_msg_data,
314 x_return_status => x_return_status);
315 IF x_return_status <> 'S' THEN
316 DEBUG(' Error in getting the Template id ', l_module_name, 9);
317 l_template_id := -1;
318 DEBUG(' Defaulting Temaplte ID : ' || l_template_id, l_module_name, 9);
319 ELSE
320 DEBUG(' Found Temaplte ID : ' || l_template_id, l_module_name, 9);
321 END IF;
322
323 -- Fetch the field info for the given page_id and template_id
324 -- Selecting all the columns to re-use the config field object constructor
325 OPEN x_field_properties FOR
326 SELECT WPF.PAGE_ID
327 , WPF.FIELD_ID
328 , WPF.FIELD_DISP_SEQUENCE_NUMBER
329 , WPF.FIELD_NAME
330 , WPF.FIELD_TYPE
331 , WPF.FIELD_CONSTRUCTOR_PARAM
332 , WPF.FIELD_PROMPT
333 , WPF.FIELD_IS_CONFIGURABLE
334 , WPF.FIELD_CATEGORY
335 , WPF.FIELD_PROPERTY1_DEFAULT_VALUE
336 , WPF.FIELD_PROPERTY2_DEFAULT_VALUE
337 , WPF.FIELD_IS_VISIBLE
338 , WTF.TEMPLATE_ID
339 , null
340 , null
341 , null
342 , -1
343 , null
344 , null
345 , null
346 , WTF.FIELD_PROPERTY1_VALUE
347 , WTF.FIELD_PROPERTY2_VALUE
348 FROM WMS_PAGE_FIELDS_VL WPF , WMS_PAGE_TEMPLATE_FIELDS WTF
349 WHERE WPF.PAGE_ID = WTF.PAGE_ID
350 AND WPF.FIELD_ID = WTF.FIELD_ID
351 AND WPF.PAGE_ID = p_page_id
352 AND WTF.TEMPLATE_ID = l_template_id
353 AND WPF.FIELD_NAME = p_field_name;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 DEBUG (' Error in getting the field properties for field : '||p_field_name,l_module_name, 9);
359 DEBUG (' ERROR CODE = ' || SQLCODE, l_module_name, 9);
360 DEBUG (' ERROR MESSAGE = ' || SQLERRM, l_module_name, 9);
361 END get_field_properties;
362
363 END WMS_CONFIG_UI;