1 package body BNE_LCT_TOOLS_PKG as
2 /* $Header: bnelcttoolsb.pls 120.6 2005/12/07 16:09:24 dagroves noship $ */
3
4 function APP_ID_TO_ASN(X_APP_ID IN NUMBER) RETURN FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
5 is
6 V_ASN FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE;
7 begin
8 if X_APP_ID is null
9 then
10 return(null);
11 end if;
12 --
13 SELECT APPLICATION_SHORT_NAME
14 INTO V_ASN
15 FROM FND_APPLICATION
16 WHERE APPLICATION_ID = X_APP_ID;
17 --
18 return(V_ASN);
19 exception
20 when no_data_found then
21 raise_application_error(-20000, 'No Application is registered in Applications with an application id of '||to_char(X_APP_ID)||'.');
22 end APP_ID_TO_ASN;
23
24 function ASN_TO_APP_ID(X_ASN IN VARCHAR2) RETURN FND_APPLICATION.APPLICATION_ID%TYPE
25 is
26 V_APP_ID FND_APPLICATION.APPLICATION_ID%TYPE;
27 begin
28 if X_ASN is null
29 then
30 return(null);
31 end if;
32 --
33 SELECT APPLICATION_ID
34 INTO V_APP_ID
35 FROM FND_APPLICATION
36 WHERE APPLICATION_SHORT_NAME = X_ASN;
37 --
38 return(V_APP_ID);
39 exception
40 when no_data_found then
41 raise_application_error(-20000, 'No Application is registered in Applications with an application short name of '''||X_ASN||'''.');
42 end ASN_TO_APP_ID;
43
44 function GET_APP_ID(X_BNE_KEY IN VARCHAR2)
45 RETURN NUMBER
46 IS
47 COLON_POSITION INTEGER;
48 KEY_NUMBER NUMBER;
49 BEGIN
50 if (X_BNE_KEY is NULL)
51 THEN
52 RETURN(NULL);
53 END IF;
54
55 COLON_POSITION := INSTR(X_BNE_KEY, ':');
56 if (COLON_POSITION = 0)
57 THEN
58 RETURN(NULL);
59 END IF;
60
61 BEGIN
62 KEY_NUMBER := SUBSTR(X_BNE_KEY,1,(COLON_POSITION -1));
63 EXCEPTION
64 WHEN INVALID_NUMBER THEN
65 KEY_NUMBER := ASN_TO_APP_ID(SUBSTR(X_BNE_KEY,1,(COLON_POSITION -1)));
66 END;
67 RETURN(KEY_NUMBER);
68 END GET_APP_ID;
69
70 function GET_CODE(X_BNE_KEY IN VARCHAR2)
71 RETURN VARCHAR2
72 IS
73 COLON_POSITION INTEGER;
74 KEY_CODE VARCHAR2(40);
75 BEGIN
76 if (X_BNE_KEY is NULL)
77 THEN
78 RETURN(NULL);
79 END IF;
80
81 COLON_POSITION := INSTR(X_BNE_KEY, ':');
82 if (COLON_POSITION = 0)
83 THEN
84 RETURN(NULL);
85 END IF;
86
87 KEY_CODE := TRIM(SUBSTR(X_BNE_KEY, COLON_POSITION+1));
88 RETURN(KEY_CODE);
89 END GET_CODE;
90
91 --------------------------------------------------------------------------------
92 -- FUNCTION: GET_PLSQL_ATT9 (PACKAGE PRIVATE) --
93 -- --
94 -- DESCRIPTION: Retrieve any code values for parameter lists encoded in --
95 -- attributes, this is done for the PLSQL, CLEANUP_PLSQL --
96 -- and CONCURRENT_REQUEST steps of the parameter driven --
97 -- importer. --
98 -- --
99 -- MODIFICATION HISTORY --
100 -- Date Username Description --
101 -- 30-MAY-2005 DAGROVES Created. --
102 --------------------------------------------------------------------------------
103 function GET_PLSQL_ATT9(X_LIST_APP_ID IN NUMBER,
104 X_LIST_CODE IN VARCHAR2)
105 RETURN VARCHAR2
106 IS
107 CURSOR C_ATT9(CP_APPLICATION_ID NUMBER,
108 CP_PARAM_LIST_CODE VARCHAR2)
109 IS
110 SELECT ATTRIBUTE9
111 FROM BNE_ATTRIBUTES A, BNE_PARAM_LIST_ITEMS I
112 WHERE A.APPLICATION_ID = I.ATTRIBUTE_APP_ID
113 AND A.ATTRIBUTE_CODE = I.ATTRIBUTE_CODE
114 AND I.APPLICATION_ID = CP_APPLICATION_ID
115 AND I.PARAM_LIST_CODE = CP_PARAM_LIST_CODE
116 AND ATTRIBUTE9 IS NOT NULL
117 ORDER BY I.SEQUENCE_NUM;
118
119 REC C_ATT9%ROWTYPE;
120 COLON_POSITION INTEGER;
121 KEY_CODE VARCHAR2(40);
122 RET VARCHAR2(2000);
123 BEGIN
124 FOR REC IN C_ATT9(X_LIST_APP_ID, X_LIST_CODE)
125 LOOP
126 COLON_POSITION := INSTR(REC.ATTRIBUTE9, ':');
127 if (COLON_POSITION > 0)
128 THEN
129 KEY_CODE := TRIM(SUBSTR(REC.ATTRIBUTE9, COLON_POSITION+1));
130 RET := RET||','||KEY_CODE;
131 END IF;
132 END LOOP;
133 RETURN(RET);
134 END ;
135
136 --------------------------------------------------------------------------------
137 -- FUNCTION: GET_IMPORT_LISTS --
138 -- --
139 -- DESCRIPTION: Retrieve all code values for parameter lists used by --
140 -- the parameter driven importer. --
141 -- --
142 -- MODIFICATION HISTORY --
143 -- Date Username Description --
144 -- 30-MAY-2005 DAGROVES Created. --
145 --------------------------------------------------------------------------------
146 function GET_IMPORT_LISTS(X_IMPORT_LIST_APP_ID IN NUMBER,
147 X_IMPORT_LIST_CODE IN VARCHAR2)
148 RETURN VARCHAR2
149 IS
150 CURSOR C_MASTER_LIST_ITEMS(CP_APPLICATION_ID NUMBER,
151 CP_PARAM_LIST_CODE VARCHAR2)
152 IS
153 SELECT PARAM_NAME,
154 STRING_VALUE,
155 ATTRIBUTE_APP_ID,
156 ATTRIBUTE_CODE
157 FROM BNE_PARAM_LIST_ITEMS
158 WHERE APPLICATION_ID = CP_APPLICATION_ID
159 AND PARAM_LIST_CODE = CP_PARAM_LIST_CODE;
160
161 PARAM_NAME BNE_PARAM_LIST_ITEMS.PARAM_NAME%TYPE;
162 PARAM_VALUE BNE_PARAM_LIST_ITEMS.STRING_VALUE%TYPE;
163 COLON_POSITION INTEGER;
164 KEY_NUMBER NUMBER;
165 KEY_CODE VARCHAR2(40);
166 TMP VARCHAR2(2000);
167 RET VARCHAR2(2000);
168 BEGIN
169 RET := NULL;
170 if X_IMPORT_LIST_APP_ID is null OR
171 X_IMPORT_LIST_CODE is null
172 then
173 return(null);
174 end if;
175 --
176 FOR REC IN C_MASTER_LIST_ITEMS(X_IMPORT_LIST_APP_ID, X_IMPORT_LIST_CODE)
177 LOOP
178 -- PARAM_NAME can have the following values:
179 -- SQL
180 -- PLSQL
181 -- CONCURRENT_REQUEST
182 -- SEQUENCE
183 -- GROUP
184 -- SUCCESS_MESSAGE
185 -- ROW_MAPPING
186 -- ERRORED_ROWS
187 -- ERROR_LOOKUP
188 -- CLEANUP_SQL
189 -- CLEANUP_PLSQL
190 --
191 PARAM_NAME := REC.PARAM_NAME;
192 PARAM_VALUE := REC.STRING_VALUE;
193
194 -- All entries except 'SUCCESS_MESSAGE' have a sub-list.
195 IF (PARAM_NAME NOT IN ('SUCCESS_MESSAGE'))
196 THEN
197 COLON_POSITION := INSTR(PARAM_VALUE, ':');
198 KEY_NUMBER := SUBSTR(PARAM_VALUE,1,(COLON_POSITION -1));
199 KEY_CODE := TRIM(SUBSTR(PARAM_VALUE, COLON_POSITION+1));
200 -- all sublists are required, add to return value.
201 RET := RET||','||KEY_CODE;
202
203 -- Drill further into lists as required.
204 if (PARAM_NAME = 'PLSQL')
205 then
206 -- attribute 9 of parameters in sublist contains a sublist
207 RET := RET||','||GET_PLSQL_ATT9(KEY_NUMBER, KEY_CODE);
208 elsif (PARAM_NAME = 'CONCURRENT_REQUEST')
209 then
210 -- attribute 9 of parameters in sublist contains a sublist
211 RET := RET||','||GET_PLSQL_ATT9(KEY_NUMBER, KEY_CODE);
212 elsif (PARAM_NAME = 'CLEANUP_PLSQL')
213 then
214 -- attribute 9 of parameters in sublist contains a sublist
215 RET := RET||','||GET_PLSQL_ATT9(KEY_NUMBER, KEY_CODE);
216 -- Commented out for efficiency, however these values form
217 -- the unique list of import steps and are left for clarity.
218 -- elsif (PARAM_NAME = 'SEQUENCE')
219 -- then
220 -- null; -- No sub-lists for this step.
221 -- elsf (PARAM_NAME = 'SQL')
222 -- then
223 -- null; -- No sub-lists for this step.
224 -- elsif (PARAM_NAME = 'GROUP')
225 -- then
226 -- null; -- No sub-lists for this step.
227 -- elsif (PARAM_NAME = 'ROW_MAPPING')
228 -- then
229 -- null; -- No sub-lists for this step.
230 -- elsif (PARAM_NAME = 'ERRORED_ROWS')
231 -- then
232 -- null; -- No sub-lists for this step.
233 -- elsif (PARAM_NAME = 'ERROR_LOOKUP')
234 -- then
235 -- null; -- No sub-lists for this step.
236 -- elsif (PARAM_NAME = 'CLEANUP_SQL')
237 -- then
238 -- null; -- No sub-lists for this step.
239 END IF;
240 END IF;
241
242 END LOOP;
243 RETURN(RET);
244 END GET_IMPORT_LISTS;
245
246 function GET_NAMED_ATTRIBUTE(ATTS IN BNE_ATTRIBUTES%ROWTYPE,
247 NAME IN BNE_ATTRIBUTES.ATTRIBUTE_NAME1%TYPE)
248 RETURN VARCHAR2
249 IS
250 RET BNE_ATTRIBUTES.ATTRIBUTE1%TYPE;
251 BEGIN
252 if NAME is null
253 THEN
254 return(null);
255 end if;
256 if ATTS.ATTRIBUTE_NAME1 = name then
257 return(ATTS.ATTRIBUTE1);
258 elsif ATTS.ATTRIBUTE_NAME2 = name then
259 return(ATTS.ATTRIBUTE2);
260 elsif ATTS.ATTRIBUTE_NAME3 = name then
261 return(ATTS.ATTRIBUTE3);
262 elsif ATTS.ATTRIBUTE_NAME4 = name then
263 return(ATTS.ATTRIBUTE4);
264 elsif ATTS.ATTRIBUTE_NAME5 = name then
265 return(ATTS.ATTRIBUTE5);
266 elsif ATTS.ATTRIBUTE_NAME6 = name then
267 return(ATTS.ATTRIBUTE6);
268 elsif ATTS.ATTRIBUTE_NAME7 = name then
269 return(ATTS.ATTRIBUTE7);
270 elsif ATTS.ATTRIBUTE_NAME8 = name then
271 return(ATTS.ATTRIBUTE8);
272 elsif ATTS.ATTRIBUTE_NAME9 = name then
273 return(ATTS.ATTRIBUTE9);
274 elsif ATTS.ATTRIBUTE_NAME10 = name then
275 return(ATTS.ATTRIBUTE10);
276 elsif ATTS.ATTRIBUTE_NAME11 = name then
277 return(ATTS.ATTRIBUTE11);
278 elsif ATTS.ATTRIBUTE_NAME12 = name then
279 return(ATTS.ATTRIBUTE12);
280 elsif ATTS.ATTRIBUTE_NAME13 = name then
281 return(ATTS.ATTRIBUTE13);
282 elsif ATTS.ATTRIBUTE_NAME14 = name then
283 return(ATTS.ATTRIBUTE14);
284 elsif ATTS.ATTRIBUTE_NAME15 = name then
285 return(ATTS.ATTRIBUTE15);
286 elsif ATTS.ATTRIBUTE_NAME16 = name then
287 return(ATTS.ATTRIBUTE16);
288 elsif ATTS.ATTRIBUTE_NAME17 = name then
289 return(ATTS.ATTRIBUTE17);
290 elsif ATTS.ATTRIBUTE_NAME18 = name then
291 return(ATTS.ATTRIBUTE18);
292 elsif ATTS.ATTRIBUTE_NAME19 = name then
293 return(ATTS.ATTRIBUTE19);
294 elsif ATTS.ATTRIBUTE_NAME20 = name then
295 return(ATTS.ATTRIBUTE20);
296 elsif ATTS.ATTRIBUTE_NAME21 = name then
297 return(ATTS.ATTRIBUTE21);
298 elsif ATTS.ATTRIBUTE_NAME22 = name then
299 return(ATTS.ATTRIBUTE22);
300 elsif ATTS.ATTRIBUTE_NAME23 = name then
301 return(ATTS.ATTRIBUTE23);
302 elsif ATTS.ATTRIBUTE_NAME24 = name then
303 return(ATTS.ATTRIBUTE24);
304 elsif ATTS.ATTRIBUTE_NAME25 = name then
305 return(ATTS.ATTRIBUTE25);
306 elsif ATTS.ATTRIBUTE_NAME26 = name then
307 return(ATTS.ATTRIBUTE26);
308 elsif ATTS.ATTRIBUTE_NAME27 = name then
309 return(ATTS.ATTRIBUTE27);
310 elsif ATTS.ATTRIBUTE_NAME28 = name then
311 return(ATTS.ATTRIBUTE28);
312 elsif ATTS.ATTRIBUTE_NAME29 = name then
313 return(ATTS.ATTRIBUTE29);
314 elsif ATTS.ATTRIBUTE_NAME30 = name then
315 return(ATTS.ATTRIBUTE30);
316 else
317 return(null);
318 END IF;
319 END GET_NAMED_ATTRIBUTE;
320
321 --------------------------------------------------------------------------------
322 -- FUNCTION: GET_EXTENSIBLE_MENUS_LISTS --
323 -- --
324 -- DESCRIPTION: Retrieve all code values for parameter lists used by --
325 -- the extensible menus functionality. --
326 -- --
327 -- MODIFICATION HISTORY --
328 -- Date Username Description --
329 -- 07-Dec-2005 DAGROVES Created. --
330 --------------------------------------------------------------------------------
331 function GET_EXTENSIBLE_MENUS_LISTS(X_CDPF_LIST_APP_ID IN NUMBER,
332 X_CDPF_LIST_CODE IN VARCHAR2)
333 RETURN VARCHAR2
334 IS
335 RET VARCHAR2(2000);
336 ATTS BNE_ATTRIBUTES%ROWTYPE;
337 TMP VARCHAR2(2000);
338 COLON_POSITION INTEGER;
339
340 CURSOR C_LIST_FK(CP_APPLICATION_ID NUMBER,
341 CP_PARAM_LIST_CODE VARCHAR2)
342 IS
343 SELECT A.*
344 FROM BNE_PARAM_LIST_ITEMS LI, BNE_PARAM_DEFNS_B PD, BNE_ATTRIBUTES A
345 WHERE LI.PARAM_DEFN_APP_ID = PD.APPLICATION_ID
346 AND LI.PARAM_DEFN_CODE = PD.PARAM_DEFN_CODE
347 AND PD.PARAM_SOURCE = 'WEBADI:ViewerExtension'
348 AND A.APPLICATION_ID = PD.ATTRIBUTE_APP_ID
349 AND A.ATTRIBUTE_CODE = PD.ATTRIBUTE_CODE
350 AND LI.APPLICATION_ID = CP_APPLICATION_ID
351 AND LI.PARAM_LIST_CODE = CP_PARAM_LIST_CODE;
352 BEGIN
353 RET := NULL;
354 if X_CDPF_LIST_APP_ID is null OR
355 X_CDPF_LIST_CODE is null
356 then
357 return(null);
358 end if;
359 --
360 OPEN C_LIST_FK(X_CDPF_LIST_APP_ID, X_CDPF_LIST_CODE);
361 FETCH C_LIST_FK INTO ATTS;
362 if C_LIST_FK%NOTFOUND then
363 CLOSE C_LIST_FK;
364 return(null);
365 end if;
366 CLOSE C_LIST_FK;
367 TMP := GET_NAMED_ATTRIBUTE(ATTS, 'PARAM_LIST_KEY');
368 if TMP is null
369 then
370 return(null);
371 end if;
372 COLON_POSITION := INSTR(TMP, ':');
373 RET := TRIM(SUBSTR(TMP, COLON_POSITION+1));
374
375 return(ret);
376 exception
377 when OTHERS then
378 if (C_LIST_FK%ISOPEN) then
379 close C_LIST_FK;
380 end if;
381 END GET_EXTENSIBLE_MENUS_LISTS;
382
383
384 --------------------------------------------------------------------------------
385 -- FUNCTION: GET_ESC_EXTENSIBLE_MENUS_LISTS --
386 -- --
387 -- DESCRIPTION: Retrieve all code values for parameter lists used by --
388 -- the extensible menus functionality. The Menus codes are--
389 -- surrounded by the '#' character to prevent substr probs.--
390 -- --
391 -- MODIFICATION HISTORY --
392 -- Date Username Description --
393 -- 07-Dec-2005 DAGROVES Created. --
394 --------------------------------------------------------------------------------
395 function GET_ESC_EXTENSIBLE_MENUS_LISTS(X_CDPF_LIST_APP_ID IN NUMBER,
396 X_CDPF_LIST_CODE IN VARCHAR2)
397 RETURN VARCHAR2
398 IS
399 RET VARCHAR2(2000);
400 BEGIN
401 RET := GET_EXTENSIBLE_MENUS_LISTS(X_CDPF_LIST_APP_ID, X_CDPF_LIST_CODE);
402 if RET is not NULL
403 then
404 return('#'||RET||'#');
405 end if;
406 return(null);
407 END GET_ESC_EXTENSIBLE_MENUS_LISTS;
408
409
410
411 end BNE_LCT_TOOLS_PKG;
[Home] [Help]