[Home] [Help]
PACKAGE BODY: APPS.HR_PASSED_SQL
Source
1 PACKAGE BODY HR_PASSED_SQL AS
2 /* $Header: hrpsdsql.pkb 120.1.12010000.1 2008/07/28 03:42:10 appldev ship $ */
3 --
4 --------------------------------------------------------------------------------
5 -- FUNCTION: GET_PASSED_SQL_ID --
6 -- --
7 -- DESCRIPTION: Inserts Query into Parameters tables, and a --
8 -- session date. --
9 -- --
10 -- PARAMETERS: WHERE clause - query to be executed by HR SQL Control --
11 -- Session Date - date to be used by HR SQL Control --
12 -- --
13 -- --
14 -- --
15 -- RETURN: parameter list id. --
16 -- --
17 -- MODIFICATION HISTORY --
18 -- Date Username Description --
19 -- 10-JUL-02 smcmilla CREATED --
20 --------------------------------------------------------------------------------
21 --
22 --$ addd additional argument p_form_name to add additional paramater to the list
23 FUNCTION GET_PASSED_SQL_ID(p_where_clause in VARCHAR2
24 ,p_session_date in VARCHAR2
25 ,p_form_name in VARCHAR2) RETURN varchar2 as
26 --
27 l_list_id number;
28 l_list_name varchar2(50);
29 l_list_code varchar2(60);
30 l_list_key varchar2(60);
31 l_prompt_above varchar2(240);
32 --
33 l_param_defn_app_id number;
34 l_param_defn_code varchar2(60);
35
36 l_param_defn_id number;
37 l_param_name varchar2(50);
38 l_param_source varchar2(50);
39 l_param_category number;
40 l_val_type number;
41 l_max_size number;
42 l_display_type number;
43 l_display_style number;
44 l_display_size number;
45 l_data_type number;
46 l_required varchar2(1);
47 l_persistent varchar2(1);
48 l_visible varchar2(1);
49 l_modifyable varchar2(1);
50 l_desc_value varchar2(240);
51 l_prompt_left varchar2(240);
52 l_sequence_num number;
53
54 l_plsql varchar2(4000);
55
56 l_where1 varchar2(2000);
57 l_where2 varchar2(2000);
58
59 BEGIN
60 --
61 -- Determine param_list_id
62 -- EXECUTE IMMEDIATE
63 -- 'SELECT BNE_PARAMETER_UTILS.GET_NEXT_PARAM_LIST_SEQ ' ||
64 -- 'FROM dual'
65 -- INTO l_list_id;
66 --
67 l_persistent := 'Y';
68 l_prompt_above := 'HR WHERE clause';
69 --
70 l_list_code := 'HRPS_' || fnd_global.user_id || '_' || to_char(sysdate,'DDMMYY_HHMISS');
71 l_list_name := 'HR Passed SQL for ' ||
72 fnd_global.user_id || ' ' || sysdate;
73 --
74 -- Create the initial Parameter List
75 --
76 l_plsql := 'BEGIN ' ||
77 ' :1 := BNE_PARAMETER_UTILS.CREATE_PARAM_LIST_ALL' ||
78 ' (p_application_id => 800 ' ||
79 ' ,p_param_list_code => ''' || l_list_code || ''' ' ||
80 ' ,p_persistent => ''' || l_persistent || ''' ' ||
81 ' ,p_comments => null ' ||
82 ' ,p_attribute_app_id => null ' ||
83 ' ,p_attribute_code => null ' ||
84 ' ,p_list_resolver => null ' ||
85 ' ,p_prompt_left => null ' ||
86 ' ,p_prompt_above => ''' || l_prompt_above || ''' ' ||
87 ' ,p_user_name => ''' || l_list_name || ''' ' ||
88 ' ,p_user_tip => null ' ||
89 ' ); ' ||
90 'END; ';
91 --
92 EXECUTE IMMEDIATE l_plsql
93 USING out l_list_key;
94 --
95 -- COMMIT;
96 --
97 --
98 --Find the Parameter Definition Id for hr:sessionDate
99 --
100 --EXECUTE IMMEDIATE
101 -- 'SELECT param_defn_id ' ||
102 -- ' FROM bne_param_defn ' ||
103 -- ' WHERE param_name = ''hr:sessionDate'''
104 --INTO l_param_defn_id;
105 --
106 l_param_name := 'hr:sessionDate';
107 l_param_Source := l_list_name;
108 l_param_category := 5;
109 l_val_type := 1;
110 l_max_size := 240;
111 l_display_type := 4;
112 l_display_Style := 1;
113 l_data_type := 1;
114 l_required := 'Y';
115 l_visible := 'Y';
116 l_modifyable := 'N';
117 l_display_size := 100;
118 l_desc_value := 'Session Date';
119 l_prompt_left := 'Session Date';
120 --
121 --
122 -- l_plsql := 'BEGIN ' ||
123 -- ' :1 := BNE_PARAMETER_UTILS.CREATE_PARAM_ALL ' ||
124 -- ' (P_PARAM_ID => ' || to_char(l_param_defn_id) ||
125 -- ' ,P_PARAM_NAME => ''' || l_param_name || ''' ' ||
126 -- ' ,P_PARAM_SOURCE => ''' || l_list_name || ''' ' ||
127 -- ' ,P_CATEGORY => ' || to_char(l_param_category) ||
128 -- ' ,P_DESCRIPTION => null ' ||
129 -- ' ,P_DATA_TYPE => ' || to_char(l_data_type) ||
130 -- ' ,P_ATTRIBUTE_ID => null ' ||
131 -- ' ,P_PARAM_RESOLVER => null ' ||
132 -- ' ,P_REQUIRED => ''' || l_required || ''' ' ||
133 -- ' ,P_VISIBLE => ''' || l_visible || ''' ' ||
134 -- ' ,P_MODIFYABLE => ''' || l_modifyable || ''' ' ||
135 -- ' ,P_DEFAULT_STRING => ''' || p_session_date || ''' ' ||
136 -- ' ,P_DEFAULT_DATE => null ' ||
137 -- ' ,P_DEFAULT_NUM => null ' ||
138 -- ' ,P_DEFAULT_BOOLEAN => null ' ||
139 -- ' ,P_DEFAULT_FORMULA => null ' ||
140 -- ' ,P_VAL_TYPE => ' || to_char(l_val_type) ||
141 -- ' ,P_VAL_VALUE => null ' ||
142 -- ' ,P_MAXIMUM_SIZE => ' || to_char(l_max_size) ||
143 -- ' ,P_DISPLAY_TYPE => ' || to_char(l_display_type) ||
144 -- ' ,P_DISPLAY_STYLE => ' || to_char(l_display_style) ||
145 -- ' ,P_DISPLAY_SIZE => ' || to_char(l_display_size) ||
146 -- ' ,P_HELP_URL => null ' ||
147 -- ' ,P_FORMAT_MASK => null ' ||
148 -- ' ,P_DEFAULT_DESC => ''' || l_desc_value || ''' ' ||
149 -- ' ,P_PROMPT_LEFT => ''' || l_prompt_left || ''' ' ||
150 -- ' ,P_PROMPT_ABOVE => null ' ||
151 -- ' ,P_USER_TIP => null ' ||
152 -- ' ,P_ACCESS_KEY => null ' ||
153 -- ' ); ' ||
154 -- ' END;';
155 --
156 --EXECUTE IMMEDIATE l_plsql
157 -- USING out l_param_defn_id;
158 --
159 --l_plsql := 'BEGIN ' ||
160 -- ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL ' ||
161 -- ' (p_param_list_id => ' || to_char(l_list_id) ||
162 -- ' ,p_param_defn_id => ' || to_char(l_param_defn_id) ||
163 -- ' ,p_param_name => ''' || l_param_name || ''' ' ||
164 -- ' ,p_attribute_id => null ' ||
165 -- ' ,p_string_val => null ' ||
166 -- ' ,p_date_val => to_date(''' || p_session_date ||
167 -- ''',''YYYY-MM-DD'') ' ||
168 -- ' ,p_number_val => null ' ||
169 -- ' ,p_boolean_val => null ' ||
170 -- ' ,p_formula => null ' ||
171 -- ' ,p_desc_val => ''' || l_desc_value || ''' ' ||
172 -- ' ); ' ||
173 -- ' END;';
174 --
175 --EXECUTE IMMEDIATE l_plsql
176 -- USING out l_sequence_num;
177 --
178 -- COMMIT;
179 --
180 --
181 -- Create the Parameter Definition for hr:where
182 --
183 EXECUTE IMMEDIATE
184 'SELECT application_id, param_defn_code' ||
185 ' FROM bne_param_defns_b ' ||
186 ' WHERE param_name = ''hr:where'''
187 INTO l_param_defn_app_id, l_param_defn_code;
188 --
189 l_param_name := 'hr:where';
190 l_param_Source := l_list_name;
191 l_param_category := 5;
192 l_val_type := 1;
193 l_max_size := 240;
194 l_display_type := 4;
195 l_display_Style := 1;
196 l_data_type := 1;
197 l_required := 'Y';
198 l_visible := 'Y';
199 l_modifyable := 'N';
200 l_display_size := 100;
201 l_desc_value := 'HR WHERE clause';
202 l_prompt_left := 'SQL WHERE clause';
203 --
204 --
205 -- l_plsql := 'BEGIN ' ||
206 -- ' :1 := BNE_PARAMETER_UTILS.CREATE_PARAM_ALL ' ||
207 -- ' (p_param_id => ' || to_char(l_param_defn_id) ||
208 -- ' ,p_param_name => ''' || l_param_name || ''' ' ||
209 -- ' ,p_param_source => ''' || l_list_name || ''' ' ||
210 -- ' ,p_category => ' || to_char(l_param_category) ||
211 -- ' ,p_description => null ' ||
212 -- ' ,p_data_type => ' || to_char(l_data_type) ||
213 -- ' ,p_attribute_id => null ' ||
214 -- ' ,p_param_resolver => null ' ||
215 -- ' ,p_required => ''' || l_required || ''' ' ||
216 -- ' ,p_visible => ''' || l_visible || ''' ' ||
217 -- ' ,p_modifyable => ''' || l_modifyable || ''' ' ||
218 -- ' ,p_default_string => :2 ' ||
219 -- ' ,p_default_date => null ' ||
220 -- ' ,p_default_num => null ' ||
221 -- ' ,p_default_boolean => null ' ||
222 -- ' ,p_default_formula => null ' ||
223 -- ' ,p_val_type => ''' || l_val_type || ''' ' ||
224 -- ' ,p_val_value => null ' ||
225 -- ' ,p_maximum_size => ' || to_char(l_max_size) ||
226 -- ' ,p_display_type => ' || to_char(l_display_type) ||
227 -- ' ,p_display_style => ' || to_char(l_display_style) ||
228 -- ' ,p_display_size => ' || to_char(l_display_size) ||
229 -- ' ,p_help_url => null ' ||
230 -- ' ,p_format_mask => null ' ||
231 -- ' ,p_default_desc => ''' || l_desc_value || ''' ' ||
232 -- ' ,p_prompt_left => ''' || l_prompt_left || ''' ' ||
233 -- ' ,p_prompt_above => null ' ||
234 -- ' ,p_user_tip => null ' ||
235 -- ' ,p_access_key => null ' ||
236 -- ' ); ' ||
237 -- ' END;';
238 --
239 --EXECUTE IMMEDIATE l_plsql
240 -- USING out l_param_defn_id;
241 --
242 l_plsql := 'BEGIN ' ||
243 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL ' ||
244 ' (p_application_id => 800 ' ||
245 ' ,p_param_list_code => ''' || l_list_code || ''' ' ||
246 ' ,p_param_defn_app_id => ' || l_param_defn_app_id ||
247 ' ,p_param_defn_code => ''' || l_param_defn_code || ''' ' ||
248 ' ,p_param_name => ''' || l_param_name || ''' ' ||
249 ' ,p_attribute_app_id => null ' ||
250 ' ,p_attribute_code => null ' ||
251 ' ,p_string_val => :2 ' ||
252 ' ,p_date_val => null ' ||
253 ' ,p_number_val => null ' ||
254 ' ,p_boolean_val => null ' ||
255 ' ,p_formula => null ' ||
256 ' ,p_desc_val => ''' || l_desc_value || ''' ' ||
257 ' ); ' ||
258 ' END;';
259 --
260 -- Determine if we use pass entire WHERE clause, or need to split it
261 --
262 IF length(p_where_clause) > 2000 THEN
263 --
264 l_where1 := substr(p_where_clause,1,2000);
265 l_where2 := substr(p_where_clause,2001);
266 ELSE
267 l_where1 := p_where_clause;
268 l_where2 := null;
269 END IF;
270 --
271 EXECUTE IMMEDIATE l_plsql
272 USING out l_sequence_num
273 , in l_where1;
274 --
275 -- Create the Parameter Definition for hr:extra
276 --
277 EXECUTE IMMEDIATE
278 'SELECT application_id, param_defn_code' ||
279 ' FROM bne_param_defns_b ' ||
280 ' WHERE param_name = ''hr:extra'''
281 INTO l_param_defn_app_id, l_param_defn_code;
282 --
283 l_param_name := 'hr:extra';
284 l_param_Source := l_list_name;
285 l_param_category := 5;
286 l_val_type := 1;
287 l_max_size := 240;
288 l_display_type := 4;
289 l_display_Style := 1;
290 l_data_type := 1;
291 l_required := 'Y';
292 l_visible := 'Y';
293 l_modifyable := 'N';
294 l_display_size := 100;
295 l_desc_value := 'HR EXTRA clause';
296 l_prompt_left := 'SQL EXTRA clause';
297 --
298 l_plsql := 'BEGIN ' ||
299 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL ' ||
300 ' (p_application_id => 800 ' ||
301 ' ,p_param_list_code => ''' || l_list_code || ''' ' ||
302 ' ,p_param_defn_app_id => ' || l_param_defn_app_id ||
303 ' ,p_param_defn_code => ''' || l_param_defn_code || ''' ' ||
304 ' ,p_param_name => ''' || l_param_name || ''' ' || ' ,p_attribute_app_id => null ' ||
305 ' ,p_attribute_code => null ' ||
306 ' ,p_string_val => :2 ' ||
307 ' ,p_date_val => null ' ||
308 ' ,p_number_val => null ' ||
309 ' ,p_boolean_val => null ' ||
310 ' ,p_formula => null ' ||
311 ' ,p_desc_val => ''' || l_desc_value || ''' ' ||
312 ' ); ' ||
313 ' END;';
314 --
315 EXECUTE IMMEDIATE l_plsql
316 USING out l_sequence_num
317 , in l_where2;
318 --
319 -- COMMIT;
320 --
321 --$ Add third parameter to the parameter list to pass form name
322 --
323 l_param_name := 'hr:form';
324 l_desc_value := 'hr form parameter';
325 l_plsql := 'BEGIN ' ||
326 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL ' ||
327 ' (p_application_id => 800 ' ||
328 ' ,p_param_list_code => ''' || l_list_code || ''' ' ||
329 ' ,p_param_defn_app_id => null ' ||
330 ' ,p_param_defn_code => null ' ||
331 ' ,p_param_name => ''' || l_param_name || ''' ' ||
332 ' ,p_attribute_app_id => null ' ||
333 ' ,p_attribute_code => null ' ||
334 ' ,p_string_val => :2 ' ||
335 ' ,p_date_val => null ' ||
336 ' ,p_number_val => null ' ||
337 ' ,p_boolean_val => null ' ||
338 ' ,p_formula => null ' ||
339 ' ,p_desc_val => ''' || l_desc_value || ''' ' ||
340 ' ); ' ||
341 ' END;';
342 --
343 EXECUTE IMMEDIATE l_plsql
344 USING out l_sequence_num
345 , in p_form_name;
346 --
347
348
349 RETURN l_list_key;
350 --
351 END get_passed_sql_id;
352 --
353 END HR_PASSED_SQL;