[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_UTILITY
Source
1 PACKAGE BODY pay_ip_utility AS
2 /* $Header: payiputil.pkb 120.9.12020000.7 2013/03/18 12:22:49 rmugloo noship $ */
3
4 --
5 -- Global Variables
6 --
7 g_package_name VARCHAR2(240);
8 g_debug BOOLEAN;
9 SUBTYPE dff_flx_type IS fnd_descriptive_flexs%ROWTYPE;
10 SUBTYPE kff_flx_type IS fnd_id_flexs%ROWTYPE;
11 SUBTYPE kff_str_type IS fnd_id_flex_structures%ROWTYPE;
12
13 /**********************************************************************
14 ** Name : get_ip_installation
15 ** Purpose : This function returns whether the IP has been installed
16 ** Arguments : IN Parameters
17 ** p_business_grp_id -> Business Group ID
18 ** Notes :
19 **********************************************************************/
20
21 FUNCTION get_ip_installation (p_business_grp_id IN NUMBER)
22 RETURN VARCHAR2 IS
23
24 cursor c_ip_installed (cp_business_grp_id NUMBER) is
25 select 'Y'
26 from per_business_groups pbg,
27 hr_legislation_installations hli
28 where business_group_id = cp_business_grp_id
29 and pbg.legislation_code = hli.legislation_code;
30
31 lv_leg_installed VARCHAR2(10) := 'N';
32
33 BEGIN
34
35 --hr_utility.trace_on( NULL, 'BAL');
36
37 open c_ip_installed (p_business_grp_id);
38 fetch c_ip_installed into lv_leg_installed;
39 close c_ip_installed;
40
41 return lv_leg_installed;
42
43 END get_ip_installation;
44
45
46 FUNCTION get_ip_installation (p_legislation_code IN VARCHAR2)
47 RETURN VARCHAR2 IS
48
49 CURSOR c_ip_installed (cp_legislation_code VARCHAR2) is
50 SELECT 'Y'
51 FROM hr_legislation_installations hli
52 WHERE hli.legislation_code =cp_legislation_code
53 AND APPLICATION_SHORT_NAME ='PAY'
54 AND STATUS='I';
55
56 lv_leg_installed VARCHAR2(10) := 'N';
57
58 BEGIN
59
60 OPEN c_ip_installed (p_legislation_code);
61 FETCH c_ip_installed into lv_leg_installed;
62 CLOSE c_ip_installed;
63
64 RETURN lv_leg_installed;
65
66 END get_ip_installation;
67
68 FUNCTION APPLICATION_ID (X_APPLICATION_SHORT_NAME in VARCHAR2)
69 RETURN NUMBER is
70
71 CURSOR CSR_APPLICATION (X_APPLICATION_SHORT_NAME in VARCHAR2) is
72 select APPLICATION_ID
73 from FND_APPLICATION
74 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
75 X_APPLICATION CSR_APPLICATION%rowtype;
76 begin
77 open CSR_APPLICATION(X_APPLICATION_SHORT_NAME);
78 fetch CSR_APPLICATION into X_APPLICATION;
79 close CSR_APPLICATION;
80 return(X_APPLICATION.APPLICATION_ID);
81 end APPLICATION_ID;
82
83 -- ---------------------------------------------------------------------
84 -- Function to get the Balance Name when balance_type_id is being passed.
85 -- ---------------------------------------------------------------------
86
87 FUNCTION get_balance_name (p_balance_type_id IN NUMBER)
88 RETURN VARCHAR2 is
89 lv_balance_name pay_balance_types.balance_name%TYPE;
90 BEGIN
91
92 SELECT balance_name
93 INTO lv_balance_name
94 FROM pay_balance_types
95 WHERE balance_type_id = p_balance_type_id;
96
97 RETURN lv_balance_name;
98 EXCEPTION
99 WHEN OTHERS THEN
100 lv_balance_name:=NULL;
101 RETURN lv_balance_name;
102 END get_balance_name;
103
104 -- ---------------------------------------------------------------------
105 -- Procedure for creating the Further Element Information flexfield.
106 -- ---------------------------------------------------------------------
107 PROCEDURE create_ele_info_dff_ctxt(p_legislation_code IN varchar2
108 ,p_dff_name IN varchar2
109 ,p_appl_short_name IN VARCHAR2
110 ,p_list_of_ctxt_string in varchar2) IS
111 lv_sql_stmt varchar2(2000);
112
113 TYPE cv_typ IS REF CURSOR;
114 cv_get_context cv_typ;
115 get_context_rec cv_get_context%TYPE;
116
117 ln_application_id fnd_descr_flex_contexts.application_id%TYPE;
118 lv_desc_flexfield_name fnd_descr_flex_contexts.descriptive_flexfield_name%TYPE;
119 lv_desc_flex_context_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
120 lv_enabled_flag fnd_descr_flex_contexts.enabled_flag%TYPE;
121 lv_global_flag fnd_descr_flex_contexts.global_flag%TYPE;
122 lv_desc_flex_context_name fnd_descr_flex_contexts_tl.descriptive_flex_context_name%TYPE;
123 lv_description fnd_descr_flex_contexts_tl.description%TYPE;
124 lv_language fnd_descr_flex_contexts_tl.language%TYPE;
125
126 /* CURSOR c_get_context IS
127 select fdfc.application_id,
128 fdfc.DESCRIPTIVE_FLEXFIELD_NAME
129 ,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
130 ,fdfc.ENABLED_FLAG
131 ,fdfc.GLOBAL_FLAG
132 --,fdfc.SECURITY_GROUP_ID
133 ,fdfctl.DESCRIPTIVE_FLEX_CONTEXT_NAME
134 ,fdfctl.DESCRIPTION
135 ,fdfctl.language
136 from fnd_descr_flex_contexts fdfc,
137 fnd_descr_flex_contexts_tl fdfctl
138 where fdfc.APPLICATION_ID = fdfctl.APPLICATION_ID
139 AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = fdfctl.DESCRIPTIVE_FLEX_CONTEXT_CODE
140 AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME = fdfctl.DESCRIPTIVE_FLEXFIELD_NAME
141 AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME = 'Element Developer DF'
142 AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE IN l_list_of_ctxt := '('ZZ_SUPPLEMENTAL EARNINGS','ZZ_EARNINGS','ZZ_TAXABLE BENEFITS'
143 ,'ZZ_DIRECT PAYMENT','ZZ_TAX DEDUCTIONS','ZZ_PRE-TAX DEDUCTIONS'
144 ,'ZZ_VOLUNTARY DEDUCTIONS','ZZ_INVOLUNTARY DEDUCTIONS')'*/
145
146
147 CURSOR c_get_segments(cp_context_code varchar2) IS
148 select b.APPLICATION_ID
149 ,b.DESCRIPTIVE_FLEXFIELD_NAME
150 ,b.DESCRIPTIVE_FLEX_CONTEXT_CODE
151 ,b.APPLICATION_COLUMN_NAME
152 ,b.END_USER_COLUMN_NAME
153 ,b.COLUMN_SEQ_NUM
154 ,b.ENABLED_FLAG
155 ,b.REQUIRED_FLAG
156 ,b.SECURITY_ENABLED_FLAG
157 ,b.DISPLAY_FLAG
158 ,b.DISPLAY_SIZE
159 ,b.MAXIMUM_DESCRIPTION_LEN
160 ,b.CONCATENATION_DESCRIPTION_LEN
161 ,b.FLEX_VALUE_SET_ID
162 ,b.RANGE_CODE
163 ,b.DEFAULT_TYPE
164 ,b.DEFAULT_VALUE
165 ,b.SRW_PARAM
166 ,t.FORM_LEFT_PROMPT
167 ,t.FORM_ABOVE_PROMPT
168 ,t.DESCRIPTION
169 ,t.language
170 FROM fnd_descr_flex_col_usage_tl t
171 ,fnd_descr_flex_column_usages b
172 WHERE b.application_id = t.application_id
173 AND b.descriptive_flexfield_name = t.descriptive_flexfield_name
174 AND b.descriptive_flex_context_code = t.descriptive_flex_context_code
175 AND b.application_column_name = t.application_column_name
176 AND t.language = userenv ('LANG')
177 AND b.descriptive_flexfield_name = p_dff_name --'Element Developer DF'
178 AND b.descriptive_flex_context_code = cp_context_code;
179
180 CURSOR c_get_val_set_name(c_val_set_id NUMBER) IS
181 SELECT flex_value_set_name
182 FROM fnd_flex_value_sets
183 WHERE FLEX_VALUE_SET_ID = c_val_set_id;
184
185 lv_context_exists BOOLEAN;
186 lv_segment_exists BOOLEAN;
187 lv_new_context_name VARCHAR2(50);
188 lv_val_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
189 BEGIN
190 hr_utility.set_location('pay_ip_utility.create_ele_info_dff_ctxt ',10);
191 /*hr_utility.trace_on(null,'DFF_FLEX'); */
192 fnd_flex_dsc_api.set_session_mode('seed_data');
193
194 lv_sql_stmt := 'select fdfc.application_id,
195 fdfc.DESCRIPTIVE_FLEXFIELD_NAME
196 ,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
197 ,fdfc.ENABLED_FLAG
198 ,fdfc.GLOBAL_FLAG
199 --,fdfc.SECURITY_GROUP_ID
200 ,fdfctl.DESCRIPTIVE_FLEX_CONTEXT_NAME
201 ,fdfctl.DESCRIPTION
202 ,fdfctl.language
203 from fnd_descr_flex_contexts fdfc,
204 fnd_descr_flex_contexts_tl fdfctl
205 where fdfc.APPLICATION_ID = fdfctl.APPLICATION_ID
206 AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = fdfctl.DESCRIPTIVE_FLEX_CONTEXT_CODE
207 AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME = fdfctl.DESCRIPTIVE_FLEXFIELD_NAME
208 AND fdfctl.language = userenv (''LANG'')
209 AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME ='''||p_dff_name|| '''
210 AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE IN (' ||p_list_of_ctxt_string|| ')' ;
211
212
213 hr_utility.trace('lv_sql_stmt : '||lv_sql_stmt);
214 OPEN cv_get_context FOR lv_sql_stmt;
215 LOOP
216
217 FETCH cv_get_context INTO
218 ln_application_id
219 ,lv_desc_flexfield_name
220 ,lv_desc_flex_context_code
221 ,lv_enabled_flag
222 ,lv_global_flag
223 ,lv_desc_flex_context_name
224 ,lv_description
225 ,lv_language ;
226 EXIT WHEN cv_get_context%NOTFOUND;
227
228 hr_utility.set_location('pay_ip_utility.create_ele_info_dff_ctxt ',20);
229 lv_new_context_name := p_legislation_code||substr(lv_desc_flex_context_code,3);
230 hr_utility.trace('lv_new_context_name '||lv_new_context_name );
231 lv_context_exists := fnd_flex_dsc_api.context_exists(
232 p_appl_short_name => p_appl_short_name
233 ,p_flexfield_name => p_dff_name
234 ,p_context_code => lv_new_context_name
235 );
236 IF lv_context_exists THEN
237 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
238 pay_ip_startup_util.write_log('LOG','PAY_34073_IP_UPD_FLEX_CONTEXT',lv_new_context_name, NULL);
239
240 hr_utility.trace('Updating the context '||lv_new_context_name );
241 fnd_flex_dsc_api.update_context(
242 p_appl_short_name => p_appl_short_name
243 ,p_flexfield_name => lv_desc_flexfield_name
244 ,p_desc_flex_context_code =>lv_new_context_name
245 ,p_desc_flex_context_name => lv_new_context_name
246 ,p_description => lv_description
247 ,p_enabled_flag => lv_enabled_flag
248 ,p_language => lv_language );
249
250 ELSE
251 hr_utility.trace('Creating Context');
252 hr_utility.trace('language '||lv_language );
253 hr_utility.trace('get_context_rec.DESCRIPTION '||lv_description);
254 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
255 pay_ip_startup_util.write_log('LOG','PAY_34072_IP_INS_FLEX_CONTEXT',lv_new_context_name, NULL);
256
257 fnd_flex_dsc_api.create_context(
258 appl_short_name => p_appl_short_name
259 ,flexfield_name => lv_desc_flexfield_name
260 ,context_code => lv_new_context_name
261 ,context_name =>lv_new_context_name
262 ,description => lv_description
263 ,enabled => lv_enabled_flag
264 ,global_flag => lv_global_flag );
265
266 hr_utility.trace('created new context '||lv_new_context_name );
267 END IF;
268
269 FOR get_segments_rec IN c_get_segments(lv_desc_flex_context_code)
270 LOOP
271 hr_utility.set_location('pay_ip_utility.create_ele_info_dff_ctxt ',30);
272 hr_utility.trace('segments start');
273 OPEN c_get_val_set_name(get_segments_rec.FLEX_VALUE_SET_ID);
274 FETCH c_get_val_set_name INTO lv_val_set_name;
275 IF c_get_val_set_name%NOTFOUND THEN
276 lv_val_set_name :=NULL;
277 END IF;
278 CLOSE c_get_val_set_name;
279 hr_utility.set_location('pay_ip_utility.create_ele_info_dff_ctxt ',40);
280 lv_segment_exists := fnd_flex_dsc_api.segment_exists(
281 p_appl_short_name => p_appl_short_name
282 ,p_segment_name => get_segments_rec.END_USER_COLUMN_NAME
283 ,p_flexfield_name => p_dff_name
284 ,p_context_code => lv_new_context_name
285 );
286
287 IF lv_segment_exists THEN
288 hr_utility.trace('segments exists TRUE');
289 pay_ip_startup_util.write_log('LOG','PAY_34075_IP_UPD_FLEX_SEGMENT',get_segments_rec.END_USER_COLUMN_NAME, NULL);
290
291
292 fnd_flex_dsc_api.modify_segment
293 (
294 P_APPL_SHORT_NAME => p_appl_short_name,
295 P_FLEXFIELD_NAME => p_dff_name,
296 P_CONTEXT_CODE => lv_new_context_name,
297 P_SEGMENT_NAME => get_segments_rec.END_USER_COLUMN_NAME,
298 P_DESCRIPTION => get_segments_rec.DESCRIPTION,
299 P_SEQUENCE_NUMBER => get_segments_rec.COLUMN_SEQ_NUM,
300 P_ENABLED => get_segments_rec.ENABLED_FLAG,
301 P_DISPLAYED => get_segments_rec.DISPLAY_FLAG,
302 P_VALUE_SET => lv_val_set_name,
303 P_DEFAULT_TYPE => get_segments_rec.DEFAULT_TYPE,
304 P_DEFAULT_VALUE => get_segments_rec.DEFAULT_VALUE,
305 P_REQUIRED => get_segments_rec.REQUIRED_FLAG,
306 P_SECURITY_ENABLED => get_segments_rec.SECURITY_ENABLED_FLAG,
307 P_DISPLAY_SIZE => get_segments_rec.DISPLAY_SIZE,
308 P_DESCRIPTION_SIZE => 40,
309 P_CONCAT_DESC_SIZE => 40,
310 P_LOV_PROMPT => get_segments_rec.MAXIMUM_DESCRIPTION_LEN,
311 P_WINDOW_PROMPT => get_segments_rec.FORM_LEFT_PROMPT,
312 P_RANGE => null,
313 P_SRW_PARAMETER => get_segments_rec.SRW_PARAM,
314 P_RUNTIME_PROPERTY_FUNCTION => null
315 );
316
317
318 ELSE
319 hr_utility.trace('segments exists FALSE');
320 pay_ip_startup_util.write_log('LOG','PAY_34074_IP_INS_FLEX_SEGMENT',get_segments_rec.END_USER_COLUMN_NAME, NULL);
321 fnd_flex_dsc_api.create_segment(
322 appl_short_name => p_appl_short_name,
323 flexfield_name => p_dff_name,
324 context_name => lv_new_context_name,
325 name => get_segments_rec.END_USER_COLUMN_NAME,
326 COLUMN => get_segments_rec.APPLICATION_COLUMN_NAME,
327 DESCRIPTION => get_segments_rec.DESCRIPTION,
328 SEQUENCE_NUMBER => get_segments_rec.COLUMN_SEQ_NUM,
329 ENABLED => get_segments_rec.ENABLED_FLAG,
330 DISPLAYED => get_segments_rec.DISPLAY_FLAG,
331 VALUE_SET => lv_val_set_name,
332 DEFAULT_TYPE => get_segments_rec.DEFAULT_TYPE,
333 DEFAULT_VALUE => get_segments_rec.DEFAULT_VALUE,
334 REQUIRED => get_segments_rec.REQUIRED_FLAG,
335 SECURITY_ENABLED => get_segments_rec.SECURITY_ENABLED_FLAG,
336 DISPLAY_SIZE => get_segments_rec.DISPLAY_SIZE,
337 DESCRIPTION_SIZE => get_segments_rec.MAXIMUM_DESCRIPTION_LEN,
338 concatenated_description_size => get_segments_rec.CONCATENATION_DESCRIPTION_LEN,
339 list_of_values_prompt => get_segments_rec.FORM_LEFT_PROMPT,
340 WINDOW_PROMPT => get_segments_rec.FORM_LEFT_PROMPT,
341 RANGE => NULL);
342
343 END IF;
344 END LOOP; --c_get_segments
345 pay_ip_startup_util.write_log('LOG','PAY_34015_IP_UPD_TABLE','FND_DESCR_FLEX_COL_USAGE_TL for the Context '||lv_new_context_name,NULL);
346 update_flex_col_usages_tl(lv_desc_flex_context_code,lv_new_context_name,p_dff_name);
347
348 END LOOP;--get_context_rec
349 CLOSE cv_get_context;
350 hr_utility.set_location('pay_ip_utility.create_ele_info_dff_ctxt ',50);
351
352 END create_ele_info_dff_ctxt;
353 -- ---------------------------------------------------------------------
354 -- Procedure to modify flex field structure for languages other than
355 -- user environment language
356 -- ---------------------------------------------------------------------
357 PROCEDURE update_flex_col_usages_tl(P_SOURCE_CONTEXT_CODE IN VARCHAR2
358 ,P_DEST_CONTEXT_CODE IN VARCHAR2
359 ,p_dff_name IN varchar2) IS
360
361 CURSOR c_get_segments_ol(cp_context_code IN VARCHAR2) IS
362 select b.APPLICATION_ID
363 ,b.DESCRIPTIVE_FLEXFIELD_NAME
364 ,b.DESCRIPTIVE_FLEX_CONTEXT_CODE
365 ,b.APPLICATION_COLUMN_NAME
366 ,t.FORM_LEFT_PROMPT
367 ,t.FORM_ABOVE_PROMPT
368 ,t.DESCRIPTION
369 ,t.language
370 FROM fnd_descr_flex_col_usage_tl t
371 ,fnd_descr_flex_column_usages b
372 WHERE b.application_id = t.application_id
373 AND b.descriptive_flexfield_name = t.descriptive_flexfield_name
374 AND b.descriptive_flex_context_code = t.descriptive_flex_context_code
375 AND b.application_column_name = t.application_column_name
376 AND t.language <> userenv ('LANG')
377 AND b.descriptive_flexfield_name = p_dff_name --'Element Developer DF'
378 AND b.descriptive_flex_context_code = cp_context_code;
379
380 BEGIN
381
382 hr_utility.trace('Entered pay_ip_utility.update_flex_col_usages_tl');
383
384 FOR rec in c_get_segments_ol(P_SOURCE_CONTEXT_CODE)
385 LOOP
386
387 hr_utility.trace('Entered pay_ip_utility.update_flex_col_usages_tl --For Loop');
388 UPDATE fnd_descr_flex_col_usage_tl SET
389 FORM_LEFT_PROMPT = rec.FORM_LEFT_PROMPT
390 ,FORM_ABOVE_PROMPT = rec.FORM_ABOVE_PROMPT
391 ,DESCRIPTION = rec.DESCRIPTION
392 WHERE APPLICATION_ID = rec.APPLICATION_ID
393 AND DESCRIPTIVE_FLEXFIELD_NAME = rec.DESCRIPTIVE_FLEXFIELD_NAME
394 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = P_DEST_CONTEXT_CODE
395 AND APPLICATION_COLUMN_NAME = rec.APPLICATION_COLUMN_NAME
396 AND LANGUAGE = rec.LANGUAGE;
397
398 hr_utility.trace('Updated fnd_descr_flex_col_usage_tl = '||SQL%ROWCOUNT);
399 END LOOP;
400 END update_flex_col_usages_tl;
401
402 -- ---------------------------------------------------------------------
403 -- Procedure for registering Organization Info types flexfield.
404 -- ---------------------------------------------------------------------
405 PROCEDURE create_org_info_type
406 (p_ORG_INFORMATION_TYPE VARCHAR2
407 ,p_DESCRIPTION VARCHAR2
408 ,p_DESTINATION VARCHAR2
409 ,p_DISP_ORG_INFORMATION_TYPE VARCHAR2
410 ,p_LEGISLATION_CODE VARCHAR2
411 ,p_APPLICATION_SHORT_NAME VARCHAR2
412 ,p_NAVIGATION_METHOD VARCHAR2) IS
413
414 CURSOR c_org_info_type(cp_org_info_type IN VARCHAR2, cp_legislation_code IN VARCHAR2) IS
415 SELECT org_information_type FROM HR_ORG_INFORMATION_TYPES
416 WHERE org_information_type = cp_org_info_type
417 AND legislation_code = cp_legislation_code;
418
419 lv_org_info_type VARCHAR2(200);
420 lv_CREATION_DATE DATE;
421 lv_CREATED_BY NUMBER;
422 lv_LAST_UPDATE_DATE DATE;
423 lv_LAST_UPDATED_BY NUMBER;
424 lv_LAST_UPDATE_LOGIN NUMBER;
425 lv_APPLICATION_ID NUMBER;
426
427 BEGIN
428 lv_CREATED_BY := 0;
429 lv_LAST_UPDATED_BY := 0;
430 lv_CREATION_DATE := sysdate;
431 lv_LAST_UPDATE_DATE := sysdate;
432 lv_LAST_UPDATE_LOGIN := 0;
433 lv_APPLICATION_ID := APPLICATION_ID(p_APPLICATION_SHORT_NAME);
434
435 OPEN c_org_info_type(p_ORG_INFORMATION_TYPE, p_LEGISLATION_CODE);
436 FETCH c_org_info_type INTO lv_org_info_type;
437 IF c_org_info_type%FOUND THEN
438 HR_ORG_INFORMATION_TYPES_PKG.UPDATE_ROW
439 ( X_ORG_INFORMATION_TYPE => p_ORG_INFORMATION_TYPE
440 ,X_DESTINATION => p_DESTINATION
441 ,X_LEGISLATION_CODE => p_LEGISLATION_CODE
442 ,X_NAVIGATION_METHOD => p_NAVIGATION_METHOD
443 ,X_FND_APPLICATION_ID => lv_APPLICATION_ID
444 ,X_DESCRIPTION => p_DESCRIPTION
445 ,X_DISPLAYED_ORG_INFORMATION_TP => p_DISP_ORG_INFORMATION_TYPE
446 ,X_LAST_UPDATE_DATE => lv_LAST_UPDATE_DATE
447 ,X_LAST_UPDATED_BY => lv_LAST_UPDATED_BY
448 ,X_LAST_UPDATE_LOGIN => lv_LAST_UPDATE_LOGIN);
449 ELSE
450 HR_ORG_INFORMATION_TYPES_PKG.INSERT_ROW
451 ( X_ORG_INFORMATION_TYPE => p_ORG_INFORMATION_TYPE
452 ,X_DESTINATION => p_DESTINATION
453 ,X_LEGISLATION_CODE => p_LEGISLATION_CODE
454 ,X_NAVIGATION_METHOD => p_NAVIGATION_METHOD
455 ,X_FND_APPLICATION_ID => lv_APPLICATION_ID
456 ,X_DESCRIPTION => p_DESCRIPTION
457 ,X_DISPLAYED_ORG_INFORMATION_TP => p_DISP_ORG_INFORMATION_TYPE
458 ,X_CREATION_DATE => lv_CREATION_DATE
459 ,X_CREATED_BY => lv_CREATED_BY
460 ,X_LAST_UPDATE_DATE => lv_LAST_UPDATE_DATE
461 ,X_LAST_UPDATED_BY => lv_LAST_UPDATED_BY
462 ,X_LAST_UPDATE_LOGIN => lv_LAST_UPDATE_LOGIN);
463 END IF;
464 CLOSE c_org_info_type;
465
466 END create_org_info_type;
467
468 -- ---------------------------------------------------------------------
469 -- Create organization info types by class.
470 -- ---------------------------------------------------------------------
471
472 PROCEDURE create_org_info_type_by_class
473 (p_ORG_CLASSIFICATION VARCHAR2
474 ,p_ORG_INFORMATION_TYPE VARCHAR2
475 ,p_MANDATORY_FLAG VARCHAR2) IS
476 BEGIN
477 HR_ORG_INFO_TYPES_BY_CLASS_PKG.LOAD_ROW
478 ( X_ORG_INFORMATION_TYPE => p_ORG_INFORMATION_TYPE
479 ,X_ORG_CLASSIFICATION => p_ORG_CLASSIFICATION
480 ,X_MANDATORY_FLAG => p_MANDATORY_FLAG
481 ,X_OWNER => NULL );
482 END create_org_info_type_by_class;
483
484 -- ---------------------------------------------------------------------
485 -- Deleting organization info types by class
486 -- ---------------------------------------------------------------------
487
488 PROCEDURE delete_org_info_type_by_class
489 (p_ORG_CLASSIFICATION VARCHAR2
490 ,p_ORG_INFORMATION_TYPE VARCHAR2) IS
491
492 l_row_exists NUMBER := 0;
493 BEGIN
494 SELECT count(*)
495 INTO l_row_exists
496 FROM hr_org_info_types_by_class
497 WHERE org_classification = p_ORG_CLASSIFICATION
498 AND org_information_type = p_ORG_INFORMATION_TYPE;
499
500 IF l_row_exists > 0 then
501 hr_org_info_types_by_class_pkg.delete_row(p_ORG_CLASSIFICATION
502 ,p_ORG_INFORMATION_TYPE);
503 END IF;
504 END delete_org_info_type_by_class;
505
506 -- ---------------------------------------------------------------------
507 -- Deleting organization info type.
508 -- ---------------------------------------------------------------------
509
510 PROCEDURE delete_org_info_type
511 (p_ORG_INFORMATION_TYPE VARCHAR2) IS
512
513 l_row_exists NUMBER := 0;
514 BEGIN
515 SELECT count(*)
516 INTO l_row_exists
517 FROM hr_org_information_types
518 WHERE org_information_type = p_ORG_INFORMATION_TYPE;
519
520 IF l_row_exists > 0 then
521 hr_org_information_types_pkg.delete_row(p_ORG_INFORMATION_TYPE);
522 END IF;
523 END delete_org_info_type;
524
525
526 -- ---------------------------------------------------------------------
527 -- Creating PQP reporting dimensions.
528 -- ---------------------------------------------------------------------
529 PROCEDURE create_pqp_rep_dim
530 (p_legislation_code IN VARCHAR2,
531 p_install_tax_unit IN VARCHAR2,
532 p_tax_year IN VARCHAR2) IS
533
534 CURSOR c_get_dimensions(cp_install_tax_unit IN VARCHAR2) IS
535 SELECT database_item_suffix, legislation_code, exception_report_period,
536 last_updated_by, last_update_date, last_update_login,
537 created_by, creation_date, object_version_number, year_begin_date
538 FROM pqp_exception_report_suffix
539 WHERE legislation_code = 'ZZ'
540 AND ((cp_install_tax_unit='N' AND INSTR(database_item_suffix,'_TU_') = 0)
541 OR cp_install_tax_unit='Y');
542
543 BEGIN
544 hr_utility.trace('Entered procedure create_pqp_rep_dim');
545 FOR rec IN c_get_dimensions(p_install_tax_unit) LOOP
546 hr_utility.trace('Database Item Suffix :'||rec.database_item_suffix);
547 UPDATE pqp_exception_report_suffix
548 SET exception_report_period = rec.exception_report_period
549 ,last_updated_by = rec.last_updated_by
550 ,last_update_date = sysdate
551 ,last_update_login = rec.last_update_login
552 ,year_begin_date = fnd_date.canonical_to_date(p_tax_year)
553 WHERE database_item_suffix = rec.database_item_suffix
554 AND legislation_code = p_legislation_code;
555
556 IF SQL%ROWCOUNT = 0 THEN
557 hr_utility.trace('Inserting Database Item Suffix :'||rec.database_item_suffix);
558 INSERT INTO PQP_EXCEPTION_REPORT_SUFFIX
559 (database_item_suffix
560 , legislation_code
561 , exception_report_period
562 , last_updated_by
563 , last_update_date
564 , last_update_login
565 , year_begin_date
566 )
567 VALUES ( rec.database_item_suffix
568 , p_legislation_code
569 , rec.exception_report_period
570 , rec.last_updated_by
571 , sysdate
572 , rec.last_update_login
573 , fnd_date.canonical_to_date(p_tax_year)
574 ) ;
575 END IF;
576 END LOOP;
577 hr_utility.trace('Leaving procedure create_pqp_rep_dim');
578 END create_pqp_rep_dim;
579
580
581 FUNCTION IS_release_122_plus return BOOLEAN IS
582 BEGIN
583 IF substr(fnd_release.release_name,1,2) = '12' and substr(fnd_release.release_name,4,1) >= '2' THEN
584 RETURN TRUE;
585 ELSE
586 RETURN FALSE;
587 END IF;
588 END IS_release_122_plus;
589
590
591 FUNCTION get_check_number(p_pre_payment_assact in number
592 ,p_pre_payment_id in number)
593 RETURN varchar2 is
594
595 lv_check_number varchar2(60);
596
597 CURSOR c_check_number(cp_pre_payment_action IN number
598 ,cp_pre_payment_id IN number) is
599 SELECT decode(ppa_pymt.action_type,
600 'M', 'Direct Deposit',
601 paa_pymt.serial_number),ppa_pymt.action_type
602 FROM pay_pre_payments ppp,
603 pay_assignment_actions paa_pymt,
604 pay_payroll_actions ppa_pymt,
605 pay_action_interlocks pai
606 WHERE pai.locked_action_id = cp_pre_payment_action
607 AND paa_pymt.assignment_action_id = pai.locking_action_id
608 AND ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
609 AND ppa_pymt.action_type in ('M','H', 'E')
610 AND paa_pymt.pre_payment_id = cp_pre_payment_id
611 AND ppp.pre_payment_id = paa_pymt.pre_payment_id
612 AND NOT EXISTS (
613 SELECT 1
614 FROM pay_payroll_actions ppa,
615 pay_assignment_actions paa,
616 pay_action_interlocks pai_void
617 WHERE pai_void.locked_action_id = paa_pymt.assignment_action_id --Void
618 /* Check if the locking is that of Void Pymt */
619 AND pai_void.locking_action_id = paa.assignment_action_id
620 AND ppa.payroll_action_id = paa.payroll_action_id
621 AND paa.action_status = 'C'
622 AND ppa.action_status = 'C'
623 AND ppa.action_type = 'D');
624
625 BEGIN
626
627 hr_utility.trace('Entering ... '||g_package_name||'.get_check_number');
628 OPEN c_check_number(p_pre_payment_assact, p_pre_payment_id);
629 FETCH c_check_number into lv_check_number,g_net_pay_action_type;
630 hr_utility.trace('cheque number : '||lv_check_number);
631 hr_utility.trace('g_net_pay_action_type : '||g_net_pay_action_type);
632 IF c_check_number%notfound then
633 lv_check_number := null;
634 END IF;
635 CLOSE c_check_number;
636 hr_utility.trace('lv_check_number '||lv_check_number);
637 RETURN lv_check_number;
638
639 END get_check_number;
640
641 PROCEDURE insert_fnd_lookup_val
642 ( p_lookup_type in varchar2
643 , p_lookup_code in varchar2
644 , p_territory_code in varchar2
645 , p_enabled_flag in varchar2
646 , p_meaning in varchar2
647 , p_description in varchar2
648 , p_start_date_active in DATE
649 , p_end_date_active in DATE
650 )
651 IS
652 l_row_id varchar2(100);
653 l_count number(3);
654 l_meaning varchar2(100);
655 len_desc number(3);
656 lenb_desc number(3);
657 l_description varchar2(300);
658 BEGIN
659
660 hr_utility.set_location('Entering ... '||g_package_name||'INSERT_FND_LOOKUP_VAL',1);
661
662 SELECT count(*)
663 INTO l_count
664 FROM FND_LOOKUP_values
665 WHERE lookup_type = P_LOOKUP_TYPE
666 AND lookup_code = P_LOOKUP_CODE
667 AND security_group_id = fnd_global.lookup_security_group(P_LOOKUP_TYPE,3);
668
669 hr_utility.set_location('inside FND_LOOKUP_VAL_INSERT',5);
670 hr_utility.set_location('P_LOOKUP_TYPE' || P_LOOKUP_TYPE,10);
671 hr_utility.set_location('P_LOOKUP_CODE' || P_LOOKUP_CODE,15);
672
673 hr_utility.set_location('P_START_DATE_ACTIVE' || to_char(P_START_DATE_ACTIVE,'dd-mm-yyyy'),20);
674 hr_utility.set_location('P_END_DATE_ACTIVE' || to_char(P_END_DATE_ACTIVE,'dd-mm-yyyy'),30);
675 hr_utility.set_location('l_meaning' || l_meaning,40);
676
677
678 IF l_count = 0 THEN
679
680 hr_utility.set_location('No Row exists',50);
681 hr_utility.set_location('fnd_global.lookup_security_group(P_LOOKUP_TYPE,3)'|| fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),60);
682
683 len_desc:=length(P_DESCRIPTION);
684 SELECT vsize(P_DESCRIPTION)
685 INTO lenb_desc
686 FROM dual;
687
688 hr_utility.set_location('len_desription:'|| len_desc,70);
689 hr_utility.set_location('len_bytes_desription:'|| lenb_desc,80);
690
691 L_DESCRIPTION := P_DESCRIPTION;
692 hr_utility.set_location('Before Loop',90);
693
694 WHILE lenb_desc > 240
695 LOOP
696 len_desc:=length(L_DESCRIPTION);
697 L_DESCRIPTION:=substr(L_DESCRIPTION,1,len_desc-1);
698 SELECT vsize(L_DESCRIPTION)
699 INTO lenb_desc FROM dual;
700 END LOOP;
701 hr_utility.set_location('After loop',100);
702
703 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
704 X_ROWID => l_row_id,
705 X_LOOKUP_TYPE => P_LOOKUP_TYPE,
706 X_SECURITY_GROUP_ID => fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),
707 X_VIEW_APPLICATION_ID => 3,
708 X_LOOKUP_CODE => P_LOOKUP_CODE,
709 X_ATTRIBUTE_CATEGORY => NULL,
710 X_ATTRIBUTE1 => NULL,
711 X_ATTRIBUTE2 => NULL,
712 X_ATTRIBUTE3 => NULL,
713 X_ATTRIBUTE4 => NULL,
714 X_ATTRIBUTE5 => NULL,
715 X_ATTRIBUTE6 => NULL,
716 X_ATTRIBUTE7 => NULL,
717 X_ATTRIBUTE8 => NULL,
718 X_ATTRIBUTE9 => NULL,
719 X_ATTRIBUTE10 => NULL,
720 X_ATTRIBUTE11 => NULL,
721 X_ATTRIBUTE12 => NULL,
722 X_ATTRIBUTE13 => NULL,
723 X_ATTRIBUTE14 => NULL,
724 X_ATTRIBUTE15 => NULL,
725 X_TAG => null,
726 X_ENABLED_FLAG => P_ENABLED_FLAG,
727 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
728 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
729 X_TERRITORY_CODE => P_TERRITORY_CODE,
730 X_MEANING => P_MEANING,
731 X_DESCRIPTION => L_DESCRIPTION,
732 X_CREATION_DATE => trunc(sysdate),
733 X_CREATED_BY => 1,
734 X_LAST_UPDATE_DATE => trunc(sysdate),
735 X_LAST_UPDATED_BY => 1,
736 X_LAST_UPDATE_LOGIN => 0);
737 hr_utility.set_location('After fnd_lookup_pkg.insert_row',110);
738 END IF;
739 hr_utility.set_location('Leaving ... '||g_package_name||'INSERT_FND_LOOKUP_VAL',110);
740
741 END insert_fnd_lookup_val;
742
743 PROCEDURE insert_bal_lookup_val (p_legislation_code VARCHAR2)
744 IS
745 BEGIN
746 hr_utility.set_location('Entering ... '||g_package_name||'INSERT_BAL_LOOKUP_VAL',10);
747 hr_utility.set_location('Before creating View Employee Balances',20);
748 INSERT_FND_LOOKUP_VAL
749 ( P_LOOKUP_TYPE => 'LOCAL_BAL_FORM_NAME'
750 , P_LOOKUP_CODE => P_LEGISLATION_CODE||'_EMPVIEWBALSS'
751 , P_TERRITORY_CODE => P_LEGISLATION_CODE
752 , P_ENABLED_FLAG => 'Y'
753 , P_MEANING => 'View Employee Balances ('||P_LEGISLATION_CODE||')'
754 , P_DESCRIPTION => NULL
755 , P_START_DATE_ACTIVE => NULL
756 , P_END_DATE_ACTIVE => NULL
757 );
758 hr_utility.set_location('Before creating Standard Balances Window',30);
759 INSERT_FND_LOOKUP_VAL
760 ( P_LOOKUP_TYPE => 'LOCAL_BAL_FORM_NAME'
761 , P_LOOKUP_CODE => P_LEGISLATION_CODE||'_STANDARD'
762 , P_TERRITORY_CODE => P_LEGISLATION_CODE
763 , P_ENABLED_FLAG => 'Y'
764 , P_MEANING => 'Standard Balances Window ('||P_LEGISLATION_CODE||')'
765 , P_DESCRIPTION => NULL
766 , P_START_DATE_ACTIVE => NULL
767 , P_END_DATE_ACTIVE => NULL
768 );
769 hr_utility.set_location('Leaving ... '||g_package_name||'INSERT_BAL_LOOKUP_VAL',40);
770 END insert_bal_lookup_val;
771
772 /**********************************************************************
773 ** Name : create_fnd_menu_entry
774 ** Purpose : This procedure creates an entry(function/submenu)
775 ** into the given fnd Menu.
776 **********************************************************************/
777
778 PROCEDURE create_fnd_menu_entry
779 (p_mode VARCHAR2
780 ,p_menu_name VARCHAR2
781 ,p_sub_menu_name VARCHAR2
782 ,p_function_name VARCHAR2
783 ,p_grant_flag VARCHAR2
784 ,p_prompt VARCHAR2
785 ,p_description VARCHAR2
786 ,p_owner VARCHAR2) IS
787
788 BEGIN
789
790 fnd_menu_entries_pkg.LOAD_ROW (
791 X_MODE => p_mode,
792 X_ENT_SEQUENCE => to_char(fnd_menus_pkg.NEXT_ENTRY_SEQUENCE),
793 X_MENU_NAME => p_menu_name,
794 X_SUB_MENU_NAME => p_sub_menu_name,
795 X_FUNCTION_NAME => p_function_name,
796 X_GRANT_FLAG => p_grant_flag,
797 X_PROMPT => p_prompt,
798 X_DESCRIPTION => p_description,
799 X_OWNER => p_owner);
800
801 END create_fnd_menu_entry;
802
803 /**********************************************************************
804 ** Name : get_application_id
805 ** Purpose : This function returns Application_ID of the given
806 product short name.
807 **********************************************************************/
808
809 FUNCTION get_application_id(p_application_short_name IN VARCHAR2)
810 RETURN VARCHAR2
811 IS
812 l_func_name VARCHAR2(80);
813 l_application_id fnd_application.application_id%TYPE;
814 BEGIN
815 l_func_name := g_package_name || 'get_application_id(short_name)';
816 pay_ip_startup_util.write_log('LOG','PAY_34016_IP_CALL_PROC',l_func_name,NULL);
817 SELECT application_id
818 INTO l_application_id
819 FROM fnd_application
820 WHERE application_short_name = p_application_short_name;
821 RETURN l_application_id;
822 EXCEPTION
823 WHEN OTHERS THEN
824 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
825 END get_application_id;
826
827
828 /**********************************************************************
829 ** Name : get_dff_flx
830 ** Purpose : This function populates the global row type variable
831 x_dff_flx and returns TRUE if the given DFF is present.
832 **********************************************************************/
833 FUNCTION get_dff_flx(p_application_id IN VARCHAR2,
834 p_descriptive_flexfield_name IN VARCHAR2,
835 x_dff_flx OUT nocopy dff_flx_type)
836 RETURN BOOLEAN
837 IS
838 l_func_name VARCHAR2(80);
839 l_application_id fnd_application.application_id%TYPE;
840
841 BEGIN
842 l_func_name := g_package_name || 'get_dff_flx(F)';
843 hr_utility.trace('get_dff_flx :p_descriptive_flexfield_name '||p_descriptive_flexfield_name);
844 pay_ip_startup_util.write_log('LOG','PAY_34016_IP_CALL_PROC',l_func_name,NULL);
845
846 SELECT *
847 INTO x_dff_flx
848 FROM fnd_descriptive_flexs
849 WHERE application_id = p_application_id
850 AND descriptive_flexfield_name = p_descriptive_flexfield_name;
851 RETURN TRUE;
852 EXCEPTION
853 WHEN no_data_found THEN
854 RETURN FALSE;
855 WHEN OTHERS THEN
856 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
857 END get_dff_flx;
858
859 /**********************************************************************
860 ** Name : get_kff_str
861 ** Purpose : This function populates the global row type variable
862 x_kff_str and returns TRUE if the given KFF is present.
863 **********************************************************************/
864 FUNCTION get_kff_str(p_application_id IN VARCHAR2,
865 p_id_kff_flx_code IN VARCHAR2,
866 p_id_flex_structure_code IN VARCHAR2,
867 x_kff_str OUT nocopy kff_str_type
868 )
869 RETURN BOOLEAN
870 IS
871 l_func_name VARCHAR2(80);
872 BEGIN
873
874 hr_utility.trace('get_kff_str : ');
875 l_func_name := g_package_name || 'get_kff_str(code,F)';
876 hr_utility.trace('get_kff_str :p_application_id '||p_application_id);
877 hr_utility.trace('get_kff_str :p_id_flex_structure_code '||p_id_flex_structure_code);
878 pay_ip_startup_util.write_log('LOG','PAY_34016_IP_CALL_PROC',l_func_name,NULL);
879 SELECT *
880 INTO x_kff_str
881 FROM fnd_id_flex_structures
882 WHERE application_id = p_application_id
883 AND id_flex_code = p_id_kff_flx_code
884 AND id_flex_structure_code = p_id_flex_structure_code;
885 RETURN TRUE;
886 EXCEPTION
887 WHEN no_data_found THEN
888 RETURN FALSE;
889 WHEN OTHERS THEN
890 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
891 END get_kff_str;
892
893
894 /**********************************************************************
895 ** Name : delete_compiled_data
896 ** Purpose : This function deletes all the compiled data of DFF
897 KFF Structure.
898 **********************************************************************/
899
900 PROCEDURE delete_compiled_data(p_mode IN VARCHAR2,
901 p_application_id IN NUMBER,
902 p_descriptive_flexfield_name IN VARCHAR2 DEFAULT NULL,
903 p_id_flex_code IN VARCHAR2 DEFAULT NULL,
904 p_id_flex_num IN NUMBER DEFAULT NULL)
905 IS
906 l_func_name VARCHAR2(80);
907 BEGIN
908 l_func_name := g_package_name || 'delete_compiled_data()';
909
910 IF (p_mode = 'DFF') THEN
911 DELETE FROM fnd_compiled_descriptive_flexs fcdf
912 WHERE fcdf.application_id = p_application_id
913 AND fcdf.descriptive_flexfield_name = p_descriptive_flexfield_name;
914 ELSIF (p_mode = 'KFF-STR') THEN
915
916 DELETE FROM fnd_compiled_id_flex_structs fcifs
917 WHERE fcifs.application_id = p_application_id
918 AND fcifs.id_flex_code = p_id_flex_code
919 AND fcifs.id_flex_num = p_id_flex_num;
920
921 DELETE FROM fnd_compiled_id_flexs fcif
922 WHERE fcif.application_id = p_application_id
923 AND fcif.id_flex_code = p_id_flex_code;
924
925 END IF;
926 EXCEPTION
927 WHEN OTHERS THEN
928 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
929 END delete_compiled_data;
930
931 /**********************************************************************
932 ** Name : submit_cp_request
933 ** Purpose : This function calls the api fnd_request.submit_request
934 to submit the given Concurrent Program and returns
935 request_id.
936
937 Before calling the api it checks if the same request
938 with the same arguments was already submitted.
939 **********************************************************************/
940 function submit_cp_request(p_application_short_name in varchar2,
941 p_concurrent_program_name in varchar2,
942 px_description in out nocopy varchar2,
943 p_argument_count in number,
944 p_argument1 in varchar2,
945 p_argument2 in varchar2 default null,
946 p_argument3 in varchar2 default null,
947 p_argument4 in varchar2 default null,
948 p_argument5 in varchar2 default null)
949 return varchar2
950 is
951 l_request_id number;
952 l_request_id_vc2 varchar2(1000);
953 l_description varchar2(32000);
954 l_argument2 VARCHAR2(1024);
955 l_argument3 VARCHAR2(1024);
956 l_argument4 VARCHAR2(1024);
957 l_argument5 VARCHAR2(1024);
958 l_default_argument CONSTANT VARCHAR2(100) := chr(0);
959 l_nvl_value CONSTANT VARCHAR2(100) := '$FLEX$.$NULL$';
960 l_func_name VARCHAR2(80);
961 begin
962 l_func_name := g_package_name || 'submit_cp_request('||p_concurrent_program_name||')';
963
964 l_argument2 := l_default_argument;
965 l_argument3 := l_default_argument;
966 l_argument4 := l_default_argument;
967 l_argument5 := l_default_argument;
968
969 --
970 -- Dump the arguments to description.
971 --
972 hr_utility.set_location(l_func_name,10);
973 l_description := 'Flex Loader: ' || p_concurrent_program_name || '(' || p_argument1;
974
975 if (p_argument_count > 1) then
976 l_argument2 := p_argument2;
977 l_description := l_description || ',' || l_argument2;
978
979 if (p_argument_count > 2) then
980 l_argument3 := p_argument3;
981 l_description := l_description || ',' || l_argument3;
982
983 if (p_argument_count > 3) then
984 l_argument4 := p_argument4;
985 l_description := l_description || ',' || l_argument4;
986
987 if (p_argument_count > 4) then
988 l_argument5 := p_argument5;
989 l_description := l_description || ',' || l_argument5;
990 end if;
991 end if;
992 end if;
993 end if;
994
995 l_description := l_description || ')';
996
997 px_description := substrb(l_description, 1, 240);
998 hr_utility.trace('px_description :'||px_description);
999 --
1000 -- See if the same request with the same arguments was already submitted.
1001 --
1002 begin
1003 SELECT request_id
1004 INTO l_request_id
1005 FROM fnd_concurrent_requests fcr,
1006 fnd_concurrent_programs fcp,
1007 fnd_application fa
1008 WHERE fa.application_short_name = p_application_short_name
1009 AND fcp.application_id = fa.application_id
1010 AND fcp.concurrent_program_name = p_concurrent_program_name
1011 AND fcr.program_application_id = fcp.application_id
1012 AND fcr.concurrent_program_id = fcp.concurrent_program_id
1013 AND fcr.status_code in ('I', -- ' Normal'
1014 'Q', -- 'StandBy'
1015 'R') -- ' Normal'
1016 AND fcr.phase_code = 'P' -- 'Pending'
1017 --
1018 -- p_argument1 is mandatory and cannot be NULL
1019 --
1020 AND nvl(fcr.argument1, l_nvl_value) = p_argument1
1021 --
1022 -- Other arguments are optional and can be NULL
1023 --
1024 AND ((p_argument_count < 2) OR
1025 (nvl(fcr.argument2, l_nvl_value) = nvl(l_argument2, l_nvl_value)))
1026 AND ((p_argument_count < 3) OR
1027 (nvl(fcr.argument3, l_nvl_value) = nvl(l_argument3, l_nvl_value)))
1028 AND ((p_argument_count < 4) OR
1029 (nvl(fcr.argument4, l_nvl_value) = nvl(l_argument4, l_nvl_value)))
1030 AND ((p_argument_count < 5) OR
1031 (nvl(fcr.argument5, l_nvl_value) = nvl(l_argument5, l_nvl_value)))
1032 AND ROWNUM = 1;
1033 hr_utility.set_location(l_func_name,20);
1034 l_request_id_vc2 := To_char(l_request_id) || ' was already submitted';
1035 exception
1036 when others then
1037 hr_utility.set_location(l_func_name,30);
1038 l_request_id_vc2 := '0';
1039 end;
1040
1041 if (l_request_id_vc2 = '0') then
1042 hr_utility.set_location(l_func_name,40);
1043 l_request_id_vc2 := fnd_request.submit_request
1044 (application => p_application_short_name,
1045 program => p_concurrent_program_name,
1046 description => px_description,
1047 start_time => NULL,
1048 sub_request => FALSE,
1049 argument1 => p_argument1,
1050 argument2 => l_argument2,
1051 argument3 => l_argument3,
1052 argument4 => l_argument4,
1053 argument5 => l_argument5);
1054 end if;
1055 hr_utility.trace('l_request_id_vc2 140 :'||l_request_id_vc2);
1056 return l_request_id_vc2;
1057
1058 exception
1059 when others THEN
1060 return '0';
1061 end submit_cp_request;
1062 /**********************************************************************
1063 ** Name : compile_flex_field
1064 ** Purpose : This procedure responsible to call submit_cp_request
1065 to submit the Compile Flex Field Concurrent Program
1066 for the given DFF/KFF-STR
1067 **********************************************************************/
1068 PROCEDURE compile_flex_field(p_mode IN VARCHAR2,
1069 p_application_short_name IN VARCHAR2 DEFAULT NULL,
1070 p_id_flex_code IN VARCHAR2 DEFAULT NULL,
1071 p_id_flex_structure_code IN VARCHAR2 DEFAULT NULL,
1072 p_descriptive_flexfield_name IN VARCHAR2 DEFAULT NULL,
1073 p_flex_value_set_name IN VARCHAR2 DEFAULT NULL)
1074 IS
1075 l_func_name VARCHAR2(80);
1076 l_dff_flx dff_flx_type;
1077 l_kff_flx kff_flx_type;
1078 l_kff_str kff_str_type;
1079 l_request_id_vc2 VARCHAR2(1000);
1080 l_set_usage_flag VARCHAR2(10);
1081 l_description VARCHAR2(240);
1082
1083 BEGIN
1084
1085 l_func_name := g_package_name || 'compile_flex_field(' || p_mode || ')';
1086 --
1087 -- Get the definitions.
1088 --
1089
1090
1091 IF (p_mode = 'DFF') THEN
1092 hr_utility.set_location(l_func_name,10);
1093 IF (NOT get_dff_flx(get_application_id(p_application_short_name),
1094 p_descriptive_flexfield_name,
1095 l_dff_flx)) THEN
1096 hr_utility.set_location(l_func_name,20);
1097 GOTO label_done;
1098 END IF;
1099 ELSIF (p_mode = 'KFF-STR') THEN
1100 hr_utility.set_location(l_func_name,30);
1101 IF (NOT get_kff_str(get_application_id(p_application_short_name),
1102 p_id_flex_code,
1103 p_id_flex_structure_code,
1104 l_kff_str)) THEN
1105 hr_utility.set_location(l_func_name,40);
1106 GOTO label_done;
1107 END IF;
1108 END IF;
1109
1110 fnd_profile.put('CONC_PRIORITY', 1);
1111
1112 IF (p_mode = 'DFF') THEN
1113 delete_compiled_data(p_mode => p_mode,
1114 p_application_id => l_dff_flx.application_id,
1115 p_descriptive_flexfield_name => p_descriptive_flexfield_name);
1116 hr_utility.trace('call_cp 140');
1117 --
1118 -- Submit compiler request if this DFF is frozen.
1119 --
1120 IF (l_dff_flx.freeze_flex_definition_flag = 'Y') THEN
1121 hr_utility.set_location(l_func_name,50);
1122 --
1123 -- Compile non-compiled flexfields
1124 --
1125
1126
1127 l_request_id_vc2 := submit_cp_request(p_application_short_name => 'FND',
1128 p_concurrent_program_name => 'FDFCMPD',
1129 px_description => l_description,
1130 p_argument_count => 3,
1131 p_argument1 => 'D',
1132 p_argument2 => p_application_short_name,
1133 p_argument3 => p_descriptive_flexfield_name);
1134
1135 ELSE
1136 hr_utility.set_location(l_func_name,60);
1137 l_request_id_vc2 := 'DFF is not frozen.';
1138
1139 END IF;
1140
1141 ELSIF (p_mode = 'KFF-STR') THEN
1142 hr_utility.set_location(l_func_name,70);
1143 delete_compiled_data(p_mode => p_mode,
1144 p_application_id => l_kff_flx.application_id,
1145 p_id_flex_code => p_id_flex_code,
1146 p_id_flex_num => l_kff_str.id_flex_num);
1147
1148 --
1149 -- Submit compiler request if this KFF Structure is frozen.
1150 --
1151 IF (l_kff_str.freeze_flex_definition_flag = 'Y') THEN
1152 hr_utility.set_location(l_func_name,80);
1153 --
1154 -- Compile non-compiled flexfields
1155
1156 l_request_id_vc2 := submit_cp_request(p_application_short_name => 'FND',
1157 p_concurrent_program_name => 'FDFCMPK',
1158 px_description => l_description,
1159 p_argument_count => 4,
1160 p_argument1 => 'K',
1161 p_argument2 => p_application_short_name,
1162 p_argument3 => p_id_flex_code,
1163 p_argument4 => l_kff_str.id_flex_num);
1164
1165 ELSE
1166 hr_utility.set_location(l_func_name,90);
1167 l_request_id_vc2 := 'KFF Structure is not frozen.';
1168
1169
1170 END IF;
1171 END IF;
1172
1173 IF (l_request_id_vc2 = '0') THEN
1174 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
1175 pay_ip_startup_util.write_log('LOG','PAY_34078_IP_FLEX_COMP_ERROR',l_description, l_request_id_vc2);
1176 ELSE
1177 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
1178 pay_ip_startup_util.write_log('LOG','PAY_34079_IP_FLEX_COMP_SUBMIT',l_description, l_request_id_vc2);
1179 END IF;
1180
1181 --
1182 -- Generate the view.
1183 --
1184 IF (p_mode = 'DFF') THEN
1185 hr_utility.set_location(l_func_name,100);
1186
1187 -- Submit generate view request if this DFF is frozen.
1188 --
1189 IF (l_dff_flx.freeze_flex_definition_flag = 'Y') THEN
1190
1191 --
1192 -- Generate _DFV.
1193 --
1194 l_request_id_vc2 := submit_cp_request(p_application_short_name => 'FND',
1195 p_concurrent_program_name => 'FDFVGN',
1196 px_description => l_description,
1197 p_argument_count => 3,
1198 p_argument1 => '3',
1199 p_argument2 => l_dff_flx.application_id,
1200 p_argument3 => p_descriptive_flexfield_name);
1201
1202 ELSE
1203 hr_utility.set_location(l_func_name,110);
1204 l_request_id_vc2 := 'DFF is not frozen.';
1205
1206 END IF;
1207
1208 ELSIF (p_mode = 'KFF-STR') THEN
1209 --
1210 null;
1211
1212 IF (l_kff_str.structure_view_name IS NULL) THEN
1213 GOTO label_done;
1214 ELSE
1215 --
1216 -- Submit generate view request if this KFF Structure is frozen.
1217 --
1218 IF (l_kff_str.freeze_flex_definition_flag = 'Y') THEN
1219
1220 l_request_id_vc2 := submit_cp_request(p_application_short_name => 'FND',
1221 p_concurrent_program_name => 'FDFVGN',
1222 px_description => l_description,
1223 p_argument_count => 5,
1224 p_argument1 => '1',
1225 p_argument2 => l_kff_str.application_id,
1226 p_argument3 => p_id_flex_code,
1227 p_argument4 => l_kff_str.id_flex_num,
1228 p_argument5 => l_kff_str.structure_view_name);
1229 ELSE
1230 l_request_id_vc2 := 'KFF Structure is not frozen.';
1231
1232 END IF;
1233 END IF;
1234 END IF;
1235
1236 <<label_done>>
1237 fnd_message.clear();
1238
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Not handling the top level exception. '||
1242 'SQLERRM : '|| SQLERRM);
1243 END compile_flex_field;
1244
1245
1246 PROCEDURE insert_ownership(p_key_name IN VARCHAR2
1247 ,p_product_name IN VARCHAR2
1248 ,p_key_value IN NUMBER
1249 )
1250 IS
1251
1252 BEGIN
1253 INSERT INTO hr_application_ownerships
1254 (key_name
1255 ,product_name
1256 ,key_value)
1257 (
1258 SELECT p_key_name
1259 ,p_product_name
1260 ,p_key_value
1261 FROM dual
1262 WHERE NOT EXISTS
1263 (SELECT NULL
1264 FROM hr_application_ownerships
1265 WHERE product_name = p_product_name
1266 AND key_name = p_key_name
1267 AND key_value = p_key_value
1268 )
1269 );
1270
1271 END insert_ownership;
1272
1273 -- ---------------------------------------------------------------------
1274 -- This procedure inserts the ownership in hr_application_ownerships
1275 -- table for element classifications belonging to 'ZZ' legislation.
1276 -- ---------------------------------------------------------------------
1277 PROCEDURE ins_element_class_ownership IS
1278
1279 CURSOR get_element_class_csr IS
1280 SELECT classification_id
1281 ,classification_name
1282 FROM pay_element_classifications
1283 WHERE legislation_code = 'ZZ';
1284
1285 BEGIN
1286 hr_utility.set_location('pay_ip_utility.ins_element_class_ownership',10);
1287 FOR rec IN get_element_class_csr LOOP
1288 pay_ip_startup_util.write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Element Classification', rec.classification_name);
1289 insert_ownership('CLASSIFICATION_ID','PAY',rec.classification_id);
1290 insert_ownership('CLASSIFICATION_ID','PER',rec.classification_id);
1291
1292 END LOOP;
1293
1294 hr_utility.set_location('pay_ip_utility.ins_element_class_ownership',20);
1295
1296 END ins_element_class_ownership;
1297
1298 -- ---------------------------------------------------------------------
1299 -- This procedure inserts the ownership in hr_application_ownerships
1300 -- table for balance types belonging to 'ZZ' legislation.
1301 -- ---------------------------------------------------------------------
1302 PROCEDURE ins_bal_type_ownership IS
1303
1304 CURSOR get_balance_type_csr IS
1305 SELECT balance_type_id,
1306 balance_name
1307 FROM pay_balance_types
1308 WHERE legislation_code = 'ZZ';
1309
1310 BEGIN
1311 hr_utility.set_location('pay_ip_utility.ins_bal_type_ownership',10);
1312 FOR rec IN get_balance_type_csr LOOP
1313 pay_ip_startup_util.write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Type', rec.balance_name);
1314 insert_ownership('BALANCE_TYPE_ID','PAY',rec.balance_type_id);
1315 insert_ownership('BALANCE_TYPE_ID','PER',rec.balance_type_id);
1316
1317 END LOOP;
1318
1319 hr_utility.set_location('pay_ip_utility.ins_bal_type_ownership',20);
1320
1321 END ins_bal_type_ownership;
1322
1323 -- ---------------------------------------------------------------------
1324 -- This procedure inserts the ownership in hr_application_ownerships
1325 -- table for balance types belonging to 'ZZ' legislation.
1326 -- ---------------------------------------------------------------------
1327 PROCEDURE ins_bal_dim_ownership IS
1328
1329 CURSOR get_balance_dimension_csr IS
1330 SELECT balance_dimension_id,
1331 dimension_name
1332 FROM pay_balance_dimensions
1333 WHERE legislation_code = 'ZZ';
1334
1335 BEGIN
1336 hr_utility.set_location('pay_ip_utility.ins_bal_dim_ownership',10);
1337 FOR rec IN get_balance_dimension_csr LOOP
1338 pay_ip_startup_util.write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Dimension', rec.dimension_name);
1339 insert_ownership('BALANCE_DIMENSION_ID','PAY',rec.balance_dimension_id);
1340 insert_ownership('BALANCE_DIMENSION_ID','PER',rec.balance_dimension_id);
1341
1342 END LOOP;
1343
1344 hr_utility.set_location('pay_ip_utility.ins_bal_dim_ownership',20);
1345
1346 END ins_bal_dim_ownership;
1347
1348 -- ---------------------------------------------------------------------
1349 -- This procedure inserts the ownership in hr_application_ownerships
1350 -- table for defined balances belonging to 'ZZ' legislation.
1351 -- ---------------------------------------------------------------------
1352 PROCEDURE ins_def_bal_ownership IS
1353
1354 CURSOR get_defined_balance_csr IS
1355 SELECT pdb.defined_balance_id,
1356 replace(upper(pbt.balance_name),' ','_') || pbd.database_item_suffix def_bal_name
1357 FROM pay_defined_balances pdb
1358 ,pay_balance_dimensions pbd
1359 ,pay_balance_types pbt
1360 WHERE pdb.legislation_code = 'ZZ'
1361 AND pdb.balance_type_id = pbt.balance_type_id
1362 AND pdb.legislation_code = pbt.legislation_code
1363 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1364 AND pdb.legislation_code = pbd.legislation_code;
1365
1366 BEGIN
1367 hr_utility.set_location('pay_ip_utility.ins_def_bal_ownership',10);
1368 FOR rec IN get_defined_balance_csr LOOP
1369 pay_ip_startup_util.write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Defined Balance', rec.def_bal_name);
1370 insert_ownership('DEFINED_BALANCE_ID','PAY',rec.defined_balance_id);
1371 insert_ownership('DEFINED_BALANCE_ID','PER',rec.defined_balance_id);
1372
1373 END LOOP;
1374
1375 hr_utility.set_location('pay_ip_utility.ins_def_bal_ownership',20);
1376
1377 END ins_def_bal_ownership;
1378
1379 -- ---------------------------------------------------------------------
1380 -- This procedure inserts the ownership in hr_application_ownerships
1381 -- table for routes belonging to 'ZZ' legislation.
1382 -- ---------------------------------------------------------------------
1383 PROCEDURE ins_route_ownership IS
1384
1385 CURSOR get_route_csr IS
1386 SELECT route.route_id,
1387 route.route_name
1388 FROM ff_routes route
1389 ,pay_balance_dimensions pbd
1390 WHERE pbd.legislation_code = 'ZZ'
1391 AND pbd.route_id = route.route_id
1392 UNION
1393 SELECT route.route_id,
1394 route.route_name
1395 FROM ff_routes route
1396 ,pay_balance_dimensions pbd,
1397 pay_dimension_routes pdr
1398 WHERE pbd.legislation_code = 'ZZ'
1399 AND pbd.balance_dimension_id = pdr.balance_dimension_id
1400 AND pdr.route_id = route.route_id;
1401
1402 BEGIN
1403 hr_utility.set_location('pay_ip_utility.ins_route_ownership',10);
1404 FOR rec IN get_route_csr LOOP
1405 pay_ip_startup_util.write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Route', rec.route_name);
1406 insert_ownership('ROUTE_ID','PAY',rec.route_id);
1407 insert_ownership('ROUTE_ID','PER',rec.route_id);
1408
1409 END LOOP;
1410
1411 hr_utility.set_location('pay_ip_utility.ins_route_ownership',20);
1412
1413 END ins_route_ownership;
1414
1415 -- ---------------------------------------------------------------------
1416 -- This procedure inserts the ownership in hr_application_ownerships
1417 -- table for balance categories belonging to 'ZZ' legislation.
1418 -- ---------------------------------------------------------------------
1419 PROCEDURE ins_bal_cat_ownership IS
1420
1421 CURSOR get_bal_cat_csr IS
1422 SELECT balance_category_id
1423 ,category_name
1424 FROM pay_balance_categories_f
1425 WHERE legislation_code = 'ZZ';
1426
1427 BEGIN
1428 hr_utility.set_location('pay_ip_utility.ins_bal_cat_ownership',10);
1429
1430 FOR rec IN get_bal_cat_csr LOOP
1431 pay_ip_startup_util.write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Category', rec.category_name);
1432 insert_ownership('BALANCE_CATEGORY_ID','PAY',rec.balance_category_id);
1433 insert_ownership('BALANCE_CATEGORY_ID','PER',rec.balance_category_id);
1434
1435 END LOOP;
1436
1437 hr_utility.set_location('pay_ip_utility.ins_bal_cat_ownership',20);
1438
1439 END ins_bal_cat_ownership;
1440
1441
1442
1443 -- ---------------------------------------------------------------------
1444 -- This procedure inserts the ownership in hr_application_ownerships
1445 -- table.
1446 -- ---------------------------------------------------------------------
1447 PROCEDURE insert_all_ownerships IS
1448
1449 BEGIN
1450 hr_utility.set_location('pay_ip_utility.insert_all_ownerships',10);
1451
1452 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
1453
1454 --Insert Element Classification Ownerships
1455 ins_element_class_ownership;
1456
1457 --Insert Balance Type Ownerships
1458 ins_bal_type_ownership;
1459
1460 --Insert Balance Fimension Ownerships
1461 ins_bal_dim_ownership;
1462
1463 --Insert Defined Balances Ownerships
1464 ins_def_bal_ownership;
1465
1466 --Insert Route Ownerships
1467 ins_route_ownership;
1468
1469 --Insert Balance Category Ownerships
1470 ins_bal_cat_ownership;
1471
1472 hr_utility.set_location('pay_ip_utility.insert_all_ownerships',20);
1473
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1477
1478 END insert_all_ownerships;
1479
1480 /**********************************************************************
1481 ** Name : check_patch_exists
1482 ** Purpose : This function checks for a given patch
1483 ** in table pay_patch_status
1484 **********************************************************************/
1485 FUNCTION check_patch_exists (p_patch_name IN VARCHAR2
1486 ,p_patch_status IN VARCHAR2
1487 ,p_appl_release IN VARCHAR2
1488 ,p_leg_code IN VARCHAR2
1489 )
1490 RETURN BOOLEAN
1491 IS
1492 lv_func_name VARCHAR2(80);
1493 lv_patch_status VARCHAR(2);
1494 BEGIN
1495 lv_func_name := g_package_name || 'check_patch_exists(F)';
1496 hr_utility.trace('check_patch_exists :p_patch_name ' || p_patch_name);
1497 hr_utility.trace('check_patch_exists :p_patch_status ' || p_patch_status);
1498 hr_utility.trace('check_patch_exists :p_appl_release ' || p_appl_release);
1499 hr_utility.trace('check_patch_exists :p_leg_code ' || p_leg_code);
1500 pay_ip_startup_util.write_log('LOG','PAY_34016_IP_CALL_PROC',lv_func_name,NULL);
1501
1502 SELECT status
1503 INTO lv_patch_status
1504 FROM pay_patch_status
1505 WHERE patch_name = p_patch_name
1506 AND status = nvl(p_patch_status,status)
1507 AND legislation_code = p_leg_code
1508 AND ((p_appl_release = nvl(application_release,p_appl_release)) OR
1509 (p_appl_release = 'ANY'));
1510 RETURN TRUE;
1511 EXCEPTION
1512 WHEN no_data_found THEN
1513 RETURN FALSE;
1514 WHEN OTHERS THEN
1515 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1516 END check_patch_exists;
1517
1518 /**********************************************************************
1519 ** Name : insert_patch_status
1520 ** Purpose : This procedure inserts a given patch
1521 ** in table pay_patch_status
1522 **********************************************************************/
1523 PROCEDURE insert_patch_status
1524 (p_patch_number IN VARCHAR2
1525 ,p_patch_name IN VARCHAR2
1526 ,p_patch_desc IN VARCHAR2
1527 ,p_patch_status IN VARCHAR2
1528 ,p_appl_release IN VARCHAR2
1529 ,p_leg_code IN VARCHAR2
1530 )
1531 IS
1532
1533 BEGIN
1534 hr_utility.set_location('Entering... pay_ip_utility.insert_patch',10);
1535 INSERT INTO pay_patch_status
1536 (ID,
1537 PATCH_NUMBER,
1538 PATCH_NAME,
1539 APPLIED_DATE,
1540 STATUS,
1541 DESCRIPTION,
1542 APPLICATION_RELEASE,
1543 LEGISLATION_CODE
1544 )
1545 VALUES
1546 (PAY_PATCH_STATUS_S.nextval,
1547 p_patch_number,
1548 p_patch_name,
1549 sysdate,
1550 p_patch_status,
1551 p_patch_desc,
1552 decode(p_appl_release,'ANY',null,p_appl_release),
1553 p_leg_code
1554 );
1555
1556 hr_utility.set_location('Leaving... pay_ip_utility.insert_patch',20);
1557
1558 END insert_patch_status;
1559
1560 /**********************************************************************
1561 ** Name : compile_formulas
1562 ** Purpose : This procedure submits request for bulk
1563 ** compiling the fromulas
1564 **********************************************************************/
1565 PROCEDURE compile_formulas
1566 (p_user_id IN NUMBER
1567 ,p_resp_id IN NUMBER
1568 ,p_resp_appl_id IN NUMBER
1569 )
1570 IS
1571 ln_req_id fnd_concurrent_requests.request_id%TYPE;
1572
1573 lv_status VARCHAR2(80);
1574 lv_phase VARCHAR2(80);
1575 lv_dev_phase VARCHAR2(80);
1576 lv_dev_status VARCHAR2(80);
1577 lv_message VARCHAR2(80);
1578 req_data VARCHAR2(240) := NULL;
1579
1580 l_wait_outcome BOOLEAN;
1581 BEGIN
1582 hr_utility.set_location('Entering... pay_ip_utility.compile_formulas',10);
1583 hr_utility.trace('p_user_id: ' || p_user_id);
1584 hr_utility.trace('p_resp_id: ' || p_resp_id);
1585 hr_utility.trace('p_resp_appl_id: ' || p_resp_appl_id);
1586
1587 hr_utility.trace('Initializing current user and responsibility');
1588 fnd_global.APPS_INITIALIZE(user_id => p_user_id,
1589 resp_id => p_resp_id,
1590 resp_appl_id => p_resp_appl_id);
1591
1592
1593 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
1594 pay_ip_startup_util.write_log('LOG','PAY_34082_IP_BULK_COMP_SUBMIT',null, null);
1595 ln_req_id := fnd_request.submit_request(application => 'FF',
1596 program => 'BULKCOMPILE',
1597 argument1 => '-W',
1598 argument2 => '%',
1599 argument3 => '%',
1600 sub_request => FALSE);
1601
1602 hr_utility.trace('ln_req_id: ' || ln_req_id);
1603 IF(ln_req_id = 0) THEN
1604 hr_utility.set_location('pay_ip_utility.compile_formulas',15);
1605 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
1606 pay_ip_startup_util.write_log('LOG','PAY_34084_IP_BULK_COMP_SUB_ERR',null, null);
1607 hr_utility.raise_error;
1608 END IF;
1609
1610 COMMIT;
1611 hr_utility.set_location('pay_ip_utility.compile_formulas',20);
1612 hr_utility.trace('Waiting for request ' || ln_req_id || ' to complete');
1613
1614 l_wait_outcome := fnd_concurrent.wait_for_request(
1615 request_id => ln_req_id,
1616 phase => lv_phase,
1617 status => lv_status,
1618 dev_phase => lv_dev_phase,
1619 dev_status => lv_dev_status,
1620 message => lv_message);
1621 hr_utility.trace('lv_dev_phase: ' ||lv_dev_phase);
1622 hr_utility.trace('lv_dev_status: ' ||lv_dev_status);
1623 hr_utility.trace('message: ' ||lv_message);
1624
1625 IF(lv_dev_phase = 'COMPLETE' AND lv_dev_status <> 'NORMAL') THEN
1626 pay_ip_startup_util.write_log('LOG',NULL,NULL,NULL);
1627 pay_ip_startup_util.write_log('LOG','PAY_34083_IP_BULK_COMP_ERROR',null, null);
1628 END IF;
1629
1630 hr_utility.set_location('Leaving... pay_ip_utility.complie_formulas',30);
1631
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1635 END compile_formulas;
1636
1637
1638 /**********************************************************************
1639 ** Name : check_run_balance_enabled
1640 ** Purpose : This function checks whether rub balances
1641 ** are enabled or not
1642 **********************************************************************/
1643 FUNCTION check_run_balance_enabled
1644 RETURN BOOLEAN
1645 IS
1646 lv_func_name VARCHAR2(200);
1647 lv_rbr_enabled VARCHAR2(5);
1648 BEGIN
1649 lv_func_name := g_package_name || 'check_run_balance_enabled(F)';
1650
1651 pay_ip_startup_util.write_log('LOG','PAY_34016_IP_CALL_PROC',lv_func_name,NULL);
1652
1653 SELECT 'Y'
1654 INTO lv_rbr_enabled
1655 FROM pay_legislation_rules
1656 WHERE legislation_code = 'ZZ'
1657 AND rule_type = 'SAVE_ASG_RUN_BAL'
1658 AND rule_mode = 'Y';
1659
1660 RETURN TRUE;
1661
1662 EXCEPTION
1663 WHEN no_data_found THEN
1664 RETURN FALSE;
1665 WHEN OTHERS THEN
1666 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1667 END check_run_balance_enabled;
1668
1669 BEGIN
1670
1671 g_package_name := 'pay_ip_utility.';
1672 g_debug := hr_utility.debug_enabled;
1673
1674 END pay_ip_utility;