eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_RI_CONFIG_TECH_SUMMARY dependencies on DBMS_LOB

Line 53: dbms_lob.createtemporary(l_bg_clob,TRUE);

49:
50: hr_utility.set_location('Entering ' ||l_proc ,10);
51:
52: l_bg_clob := get_clob_locator('BUSINESS_GROUPS');
53: dbms_lob.createtemporary(l_bg_clob,TRUE);
54:
55: -- dbms_lob.writeappend(l_bg_clob,length(''),'');
56: l_bg_prejoin_sql := ' select Effective_Date,terr.TERRITORY_SHORT_NAME CountryName,Short_Name, '
57: ||' lookup1.meaning app_gen_method_val, '

Line 55: -- dbms_lob.writeappend(l_bg_clob,length(''),'');

51:
52: l_bg_clob := get_clob_locator('BUSINESS_GROUPS');
53: dbms_lob.createtemporary(l_bg_clob,TRUE);
54:
55: -- dbms_lob.writeappend(l_bg_clob,length(''),'');
56: l_bg_prejoin_sql := ' select Effective_Date,terr.TERRITORY_SHORT_NAME CountryName,Short_Name, '
57: ||' lookup1.meaning app_gen_method_val, '
58: ||' lookup2.meaning emp_gen_method_val,lookup3.meaning cwk_gen_method_val, '
59: ||' curr.name CurrencyName , grade_flex_stru_code,group_flex_stru_code,'

Line 63: dbms_lob.writeappend(l_bg_clob,length(l_bg_prejoin_sql),l_bg_prejoin_sql);

59: ||' curr.name CurrencyName , grade_flex_stru_code,group_flex_stru_code,'
60: ||' job_flex_stru_code,cost_flex_stru_code, '
61: ||' position_flex_stru_code,competence_flex_stru_code from (';
62:
63: dbms_lob.writeappend(l_bg_clob,length(l_bg_prejoin_sql),l_bg_prejoin_sql);
64:
65: hr_utility.set_location('Entering BG Loop'||l_proc,15);
66:
67: IF p_business_grp_tab.count > 0 THEN

Line 97: dbms_lob.writeappend(l_bg_clob,length(l_temp_sql),l_temp_sql);

93: '''' || p_business_grp_tab(i).security_group_name ||''''||' security_group_name ,'||
94: '''' || p_business_grp_tab(i).competence_flex_stru_code||''''||' competence_flex_stru_code '||
95: ' FROM DUAL UNION';
96:
97: dbms_lob.writeappend(l_bg_clob,length(l_temp_sql),l_temp_sql);
98: end loop;
99: end if;
100: hr_utility.set_location('Leaving BG Loop',20);
101: dbms_lob.trim(l_bg_clob,length(l_bg_clob)-5);

Line 101: dbms_lob.trim(l_bg_clob,length(l_bg_clob)-5);

97: dbms_lob.writeappend(l_bg_clob,length(l_temp_sql),l_temp_sql);
98: end loop;
99: end if;
100: hr_utility.set_location('Leaving BG Loop',20);
101: dbms_lob.trim(l_bg_clob,length(l_bg_clob)-5);
102:
103: l_bg_postjoin_sql := ' ) X ,FND_TERRITORIES_VL terr ,fnd_lookup_values lookup1, '
104: ||' fnd_lookup_values lookup2,fnd_lookup_values lookup3 , '
105: ||' fnd_currencies_vl curr '

Line 119: dbms_lob.writeappend(l_bg_clob,length(l_bg_postjoin_sql),l_bg_postjoin_sql);

115: ||' and lookup2.language = userenv(''LANG'')'
116: ||' and lookup3.language = userenv(''LANG'')'
117: ||' order by CountryName desc' ;
118:
119: dbms_lob.writeappend(l_bg_clob,length(l_bg_postjoin_sql),l_bg_postjoin_sql);
120:
121: l_ret_bg_clob := fetch_clob(l_bg_clob,'BusinessGroup','BusinessGroups');
122:
123: hr_utility.set_location('Leaving ' ||l_proc ,30);

Line 148: dbms_lob.createtemporary(l_org_clob,TRUE);

144:
145: hr_utility.set_location('Entering ' || l_proc ,10);
146:
147: l_org_clob := get_clob_locator('ORGANIZATIONS');
148: dbms_lob.createtemporary(l_org_clob,TRUE);
149: l_orderby := ' order by business_grp_name desc, name desc ';
150:
151: hr_utility.set_location('Before Org Enterprise Loop ' || l_proc ,10);
152:

Line 166: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);

162: '''' || p_org_ent_tab(i).location_code ||''''||' location_code ,'||
163: '''' || p_org_ent_tab(i).internal_external_flag ||''''||' internal_external_flag '||
164: ' FROM DUAL UNION';
165:
166: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);
167: end loop;
168: end if;
169:
170: hr_utility.set_location('Leaving Ent Loop',20);

Line 187: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);

183: '''' || p_org_oc_tab(i).location_code ||''''||' location_code ,'||
184: '''' || p_org_oc_tab(i).internal_external_flag ||''''||' internal_external_flag '||
185: ' FROM DUAL UNION';
186:
187: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);
188: end loop;
189: end if;
190:
191: hr_utility.set_location('Leaving OC Loop',40);

Line 207: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);

203: '''' || p_org_le_tab(i).location_code ||''''||' location_code ,'||
204: '''' || p_org_le_tab(i).internal_external_flag ||''''||' internal_external_flag '||
205: ' FROM DUAL UNION';
206:
207: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);
208: end loop;
209: end if;
210:
211: hr_utility.set_location('Leaving LE Loop',60);

Line 212: dbms_lob.trim(l_org_clob,length(l_org_clob)-5);

208: end loop;
209: end if;
210:
211: hr_utility.set_location('Leaving LE Loop',60);
212: dbms_lob.trim(l_org_clob,length(l_org_clob)-5);
213: dbms_lob.writeappend(l_org_clob,length(l_orderby),l_orderby);
214:
215: l_ret_org_clob := fetch_clob(l_org_clob,'Organization','Organizations');
216: return l_ret_org_clob;

Line 213: dbms_lob.writeappend(l_org_clob,length(l_orderby),l_orderby);

209: end if;
210:
211: hr_utility.set_location('Leaving LE Loop',60);
212: dbms_lob.trim(l_org_clob,length(l_org_clob)-5);
213: dbms_lob.writeappend(l_org_clob,length(l_orderby),l_orderby);
214:
215: l_ret_org_clob := fetch_clob(l_org_clob,'Organization','Organizations');
216: return l_ret_org_clob;
217: end get_org_sql;

Line 243: dbms_lob.createtemporary(l_org_class_clob,TRUE);

239: hr_utility.set_location('Entering ' || l_proc,10);
240:
241: select meaning into l_enabled from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
242: l_org_class_clob := get_clob_locator('ORGANIZATIONS');
243: dbms_lob.createtemporary(l_org_class_clob,TRUE);
244:
245: hr_utility.set_location('Entering Enterprise Classification ',15);
246:
247: l_class_prejoin_sql:= ' select effective_date,'

Line 256: dbms_lob.writeappend(l_org_class_clob,length(l_class_prejoin_sql),l_class_prejoin_sql);

252: ||' hrlkp.meaning org_classif_val,'
253: ||' ''Yes'' Enabled '
254: ||' from ( ';
255:
256: dbms_lob.writeappend(l_org_class_clob,length(l_class_prejoin_sql),l_class_prejoin_sql);
257:
258: IF p_org_ent_class_tab.count > 0 THEN
259: for i in p_org_ent_class_tab.first ..
260: p_org_ent_class_tab.last loop

Line 270: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);

266: '''' || p_org_ent_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
267: '''' || REPLACE(p_org_ent_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name '||
268: ' FROM DUAL UNION';
269:
270: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
271: end loop;
272: end if;
273:
274: hr_utility.set_location('Leaving Enterprise Classification ',20);

Line 289: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);

285: '''' || p_org_oc_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
286: '''' || REPLACE(p_org_oc_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name '||
287: ' FROM DUAL UNION';
288:
289: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
290: end loop;
291: end if;
292:
293: hr_utility.set_location('Leaving OC Loop',40);

Line 308: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);

304: '''' || p_org_le_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
305: '''' || REPLACE (p_org_le_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name '||
306: ' FROM DUAL UNION';
307:
308: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
309: end loop;
310: end if;
311: hr_utility.set_location('Leaving LE Loop',60);
312: dbms_lob.trim(l_org_class_clob,length(l_org_class_clob)-5);

Line 312: dbms_lob.trim(l_org_class_clob,length(l_org_class_clob)-5);

308: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
309: end loop;
310: end if;
311: hr_utility.set_location('Leaving LE Loop',60);
312: dbms_lob.trim(l_org_class_clob,length(l_org_class_clob)-5);
313:
314: l_class_postjoin_sql := ' ), hr_lookups hrlkp '
315: ||' where hrlkp.lookup_type= ''ORG_CLASS'''
316: ||' and hrlkp.lookup_code = org_classif_code ';

Line 318: dbms_lob.writeappend(l_org_class_clob,length(l_class_postjoin_sql),l_class_postjoin_sql);

314: l_class_postjoin_sql := ' ), hr_lookups hrlkp '
315: ||' where hrlkp.lookup_type= ''ORG_CLASS'''
316: ||' and hrlkp.lookup_code = org_classif_code ';
317:
318: dbms_lob.writeappend(l_org_class_clob,length(l_class_postjoin_sql),l_class_postjoin_sql);
319:
320: l_ret_org_class_clob := fetch_clob(l_org_class_clob,'OrgClassification','OrgClassifications');
321: return l_ret_org_class_clob;
322:

Line 353: dbms_lob.createtemporary(l_org_class_clob_for_pv,TRUE);

349:
350: select meaning into l_enabled from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
351: l_orderby := ' order by business_grp_name desc, name desc ';
352: l_org_class_clob_for_pv := get_clob_locator('ORGANIZATION_CLASSIF');
353: dbms_lob.createtemporary(l_org_class_clob_for_pv,TRUE);
354:
355: --For every org find all classifications
356: IF p_org_ent_tab.count > 0 THEN
357: for j in p_org_ent_tab.first ..

Line 397: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);

393: end if;
394: end loop;
395: l_temp_sql := l_temp_sql || ' FROM DUAL';
396: if l_classification_exists then
397: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
398: l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
399: dbms_lob.trim(l_org_class_clob_for_pv,0);
400: end if;
401: end if;

Line 399: dbms_lob.trim(l_org_class_clob_for_pv,0);

395: l_temp_sql := l_temp_sql || ' FROM DUAL';
396: if l_classification_exists then
397: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
398: l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
399: dbms_lob.trim(l_org_class_clob_for_pv,0);
400: end if;
401: end if;
402: end loop;
403: end if;

Line 450: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);

446: end loop;
447:
448: l_temp_sql := l_temp_sql || ' FROM DUAL';
449: if l_classification_exists then
450: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
451: l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
452: dbms_lob.trim(l_org_class_clob_for_pv,0);
453: end if;
454: end if;

Line 452: dbms_lob.trim(l_org_class_clob_for_pv,0);

448: l_temp_sql := l_temp_sql || ' FROM DUAL';
449: if l_classification_exists then
450: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
451: l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
452: dbms_lob.trim(l_org_class_clob_for_pv,0);
453: end if;
454: end if;
455: end loop;
456: end if;

Line 503: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);

499: end if;
500: end loop;
501: l_temp_sql := l_temp_sql || ' FROM DUAL';
502: if l_classification_exists then
503: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
504: l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
505: dbms_lob.trim(l_org_class_clob_for_pv,0);
506: end if;
507: end if;

Line 505: dbms_lob.trim(l_org_class_clob_for_pv,0);

501: l_temp_sql := l_temp_sql || ' FROM DUAL';
502: if l_classification_exists then
503: dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
504: l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
505: dbms_lob.trim(l_org_class_clob_for_pv,0);
506: end if;
507: end if;
508: end loop;
509: end if;

Line 550: dbms_lob.createtemporary(l_location_clob,TRUE);

546:
547: BEGIN
548: hr_utility.set_location('Entering ' || l_proc,10);
549: l_location_clob := get_clob_locator('LOCATIONS');
550: dbms_lob.createtemporary(l_location_clob,TRUE);
551:
552: l_prejoin_sql := ' select decode(A.style,''GENERIC'',''Generic'',terr.territory_short_name) Address_Style '
553: ||' from ( ';
554: l_postjoin_sql := ') A, fnd_territories_vl terr where terr.territory_code(+) = A.style ';

Line 625: dbms_lob.writeappend(l_location_clob,length(l_temp_sql),l_temp_sql);

621: '''' || nvl(replace(p_location_tab(i).loc_information20,'''',''''''),' ')||''''||' LOC_INFORMATION20 ,'||
622: '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION20')||''''||' LOC_INFORMATION20_PROMPT '||
623: ' FROM DUAL UNION';
624:
625: dbms_lob.writeappend(l_location_clob,length(l_temp_sql),l_temp_sql);
626:
627: end loop;
628: end if;
629:

Line 630: dbms_lob.trim(l_location_clob,length(l_location_clob)-5);

626:
627: end loop;
628: end if;
629:
630: dbms_lob.trim(l_location_clob,length(l_location_clob)-5);
631: dbms_lob.writeappend(l_location_clob,length(l_orderby),l_orderby);
632: l_ret_location_clob := fetch_clob(l_location_clob,'Location','Locations');
633: hr_utility.set_location('Leaving ' || l_proc,20);
634: return l_ret_location_clob;

Line 631: dbms_lob.writeappend(l_location_clob,length(l_orderby),l_orderby);

627: end loop;
628: end if;
629:
630: dbms_lob.trim(l_location_clob,length(l_location_clob)-5);
631: dbms_lob.writeappend(l_location_clob,length(l_orderby),l_orderby);
632: l_ret_location_clob := fetch_clob(l_location_clob,'Location','Locations');
633: hr_utility.set_location('Leaving ' || l_proc,20);
634: return l_ret_location_clob;
635:

Line 653: dbms_lob.createtemporary(l_user_clob,TRUE);

649: BEGIN
650:
651: hr_utility.set_location('Entering ' || l_proc,10);
652: l_user_clob := get_clob_locator('USERS');
653: dbms_lob.createtemporary(l_user_clob,TRUE);
654:
655: if p_user_tab.count > 0 THEN
656: for i in p_user_tab.first ..
657: p_user_tab.last loop

Line 665: dbms_lob.writeappend(l_user_clob,length(l_temp_sql),l_temp_sql);

661: '''' || p_user_tab(i).start_date ||''''||' start_date ,'||
662: '''' || p_user_tab(i).description ||''''||' description '||
663: ' FROM DUAL UNION';
664:
665: dbms_lob.writeappend(l_user_clob,length(l_temp_sql),l_temp_sql);
666: end loop;
667: end if;
668:
669: dbms_lob.trim(l_user_clob,length(l_user_clob)-5);

Line 669: dbms_lob.trim(l_user_clob,length(l_user_clob)-5);

665: dbms_lob.writeappend(l_user_clob,length(l_temp_sql),l_temp_sql);
666: end loop;
667: end if;
668:
669: dbms_lob.trim(l_user_clob,length(l_user_clob)-5);
670: l_ret_user_clob := fetch_clob(l_user_clob,'User','Users');
671: hr_utility.set_location('Leaving ' || l_proc,20);
672: return l_ret_user_clob;
673:

Line 698: dbms_lob.createtemporary(l_resp_clob,TRUE);

694: BEGIN
695:
696: hr_utility.set_location('Entering ' || l_proc,10);
697: l_resp_clob := get_clob_locator('RESPONSIBILITIES');
698: dbms_lob.createtemporary(l_resp_clob,TRUE);
699:
700: l_prejoin_sql := ' select distinct A.user_name , A.resp_key ResponsibilityName ,'
701: ||' decode(A.security_group,''STANDARD'',''Standard'',A.security_group) SecurityGroupName, '
702: ||' app.APPLICATION_NAME ApplicationName, A.start_date from ( ';

Line 704: dbms_lob.writeappend(l_resp_clob,length(l_prejoin_sql),l_prejoin_sql);

700: l_prejoin_sql := ' select distinct A.user_name , A.resp_key ResponsibilityName ,'
701: ||' decode(A.security_group,''STANDARD'',''Standard'',A.security_group) SecurityGroupName, '
702: ||' app.APPLICATION_NAME ApplicationName, A.start_date from ( ';
703:
704: dbms_lob.writeappend(l_resp_clob,length(l_prejoin_sql),l_prejoin_sql);
705:
706: if p_resp_tab.count > 0 THEN
707: for i in p_resp_tab.first ..
708: p_resp_tab.last loop

Line 721: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);

717: '''' || p_resp_tab(i).end_date ||''''||' end_date,' ||
718: '''' || p_resp_tab(i).description ||''''||' description' ||
719: ' FROM DUAL UNION';
720:
721: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
722: end loop;
723: end if;
724:
725: if p_hrms_resp_tab.count > 0 THEN

Line 740: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);

736: '''' || p_hrms_resp_tab(i).end_date ||''''||' end_date,' ||
737: '''' || p_hrms_resp_tab(i).description ||''''||' description' ||
738: ' FROM DUAL UNION';
739:
740: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
741: end loop;
742: end if;
743:
744: /*if p_hrms_misc_resp_tab.count > 0 THEN

Line 759: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);

755: '''' || p_hrms_misc_resp_tab(i).end_date ||''''||' end_date,' ||
756: '''' || p_hrms_misc_resp_tab(i).description ||''''||' description' ||
757: ' FROM DUAL UNION';
758:
759: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
760: end loop;
761: end if;*/
762:
763: dbms_lob.trim(l_resp_clob,length(l_resp_clob)-5);

Line 763: dbms_lob.trim(l_resp_clob,length(l_resp_clob)-5);

759: dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
760: end loop;
761: end if;*/
762:
763: dbms_lob.trim(l_resp_clob,length(l_resp_clob)-5);
764: l_postjoin_sql := ' )A ,fnd_responsibility_vl resp,FND_SECURITY_GROUPS_VL secgrp ,fnd_application_vl app '
765: ||' where '
766: ||' A.app_short_name = app.application_short_name '
767: ||' order by ResponsibilityName ,ApplicationName , SecurityGroupName ';

Line 770: dbms_lob.writeappend(l_resp_clob,length(l_postjoin_sql),l_postjoin_sql);

766: ||' A.app_short_name = app.application_short_name '
767: ||' order by ResponsibilityName ,ApplicationName , SecurityGroupName ';
768:
769:
770: dbms_lob.writeappend(l_resp_clob,length(l_postjoin_sql),l_postjoin_sql);
771: l_ret_resp_clob := fetch_clob(l_resp_clob,'Resp','Resps');
772:
773: hr_utility.set_location('Leaving ' || l_proc,20);
774: return l_ret_resp_clob;

Line 799: dbms_lob.createtemporary(l_profile_clob,TRUE);

795:
796: BEGIN
797: hr_utility.set_location('Entering ' || l_proc,10);
798: l_profile_clob := get_clob_locator('Profiles');
799: dbms_lob.createtemporary(l_profile_clob,TRUE);
800:
801: l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
802: ' decode(A.profile_option_value, ''Y'',''Yes'',''N'',''No'',A.profile_option_value) ProfileValue, '||
803: ' fapp.Application_Name, A.level_value from (';

Line 812: dbms_lob.writeappend(l_profile_clob,length(l_prejoin_sql),l_prejoin_sql);

808: ' and fapp.APPLICATION_SHORT_NAME = A.level_value_app '||
809: ' and level_x = ''1001'' '|| --only for Site level profiles
810: ' order by UserProfileName desc';
811:
812: dbms_lob.writeappend(l_profile_clob,length(l_prejoin_sql),l_prejoin_sql);
813:
814: if p_profile_tab.count > 0 THEN
815: for i in p_profile_tab.first ..
816: p_profile_tab.last loop

Line 838: dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);

834: '''' || p_profile_tab(i).profile_name ||''''||' profile_name,' ||
835: '''' || l_translated_prof_opt_value ||''''||' profile_option_value ' ||
836: ' FROM DUAL UNION';
837:
838: dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);
839: end loop;
840: end if;
841:
842: if p_profile_dpe_ent_tab.count > 0 THEN

Line 863: dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);

859: '''' || p_profile_dpe_ent_tab(i).profile_name ||''''||' profile_name,' ||
860: '''' || l_translated_prof_opt_value ||''''||' profile_option_value ' ||
861: ' FROM DUAL UNION';
862:
863: dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);
864: end loop;
865: end if;
866:
867: dbms_lob.trim(l_profile_clob,length(l_profile_clob)-5);

Line 867: dbms_lob.trim(l_profile_clob,length(l_profile_clob)-5);

863: dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);
864: end loop;
865: end if;
866:
867: dbms_lob.trim(l_profile_clob,length(l_profile_clob)-5);
868: dbms_lob.writeappend(l_profile_clob,length(l_postjoin_sql),l_postjoin_sql);
869: l_ret_profile_clob := fetch_clob(l_profile_clob,'Profile','Profiles');
870: hr_utility.set_location('Leaving ' || l_proc,20);
871: return l_ret_profile_clob;

Line 868: dbms_lob.writeappend(l_profile_clob,length(l_postjoin_sql),l_postjoin_sql);

864: end loop;
865: end if;
866:
867: dbms_lob.trim(l_profile_clob,length(l_profile_clob)-5);
868: dbms_lob.writeappend(l_profile_clob,length(l_postjoin_sql),l_postjoin_sql);
869: l_ret_profile_clob := fetch_clob(l_profile_clob,'Profile','Profiles');
870: hr_utility.set_location('Leaving ' || l_proc,20);
871: return l_ret_profile_clob;
872:

Line 896: dbms_lob.createtemporary(l_profile_apps_clob,TRUE);

892: BEGIN
893:
894: hr_utility.set_location('Entering ' || l_proc,10);
895: l_profile_apps_clob := get_clob_locator('profile_appss');
896: dbms_lob.createtemporary(l_profile_apps_clob,TRUE);
897:
898: l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
899: ' decode(A.profile_option_value, ''Y'',''Yes'',''N'',''No'',A.profile_option_value) ProfileValue, '||
900: ' fapp.Application_Name, A.level_value from (';

Line 907: dbms_lob.writeappend(l_profile_apps_clob,length(l_prejoin_sql),l_prejoin_sql);

903: ' ,fnd_application_vl fapp '||
904: ' where fprf.profile_option_name = A.profile_name '||
905: ' and fapp.APPLICATION_SHORT_NAME = A.level_value_app ';
906:
907: dbms_lob.writeappend(l_profile_apps_clob,length(l_prejoin_sql),l_prejoin_sql);
908:
909: if p_profile_apps_tab.count > 0 THEN
910: for i in p_profile_apps_tab.first ..
911: p_profile_apps_tab.last loop

Line 921: dbms_lob.writeappend(l_profile_apps_clob,length(l_temp_sql),l_temp_sql);

917: '''' || p_profile_apps_tab(i).profile_name ||''''||' profile_name,' ||
918: '''' || p_profile_apps_tab(i).profile_option_value ||''''||' profile_option_value ' ||
919: ' FROM DUAL UNION';
920:
921: dbms_lob.writeappend(l_profile_apps_clob,length(l_temp_sql),l_temp_sql);
922: end loop;
923: end if;
924:
925: dbms_lob.trim(l_profile_apps_clob,length(l_profile_apps_clob)-5);

Line 925: dbms_lob.trim(l_profile_apps_clob,length(l_profile_apps_clob)-5);

921: dbms_lob.writeappend(l_profile_apps_clob,length(l_temp_sql),l_temp_sql);
922: end loop;
923: end if;
924:
925: dbms_lob.trim(l_profile_apps_clob,length(l_profile_apps_clob)-5);
926: dbms_lob.writeappend(l_profile_apps_clob,length(l_postjoin_sql),l_postjoin_sql);
927: l_ret_profile_apps_clob := fetch_clob(l_profile_apps_clob,'profile_apps','profile_apps');
928: hr_utility.set_location('Leaving ' || l_proc,20);
929: return l_ret_profile_apps_clob;

Line 926: dbms_lob.writeappend(l_profile_apps_clob,length(l_postjoin_sql),l_postjoin_sql);

922: end loop;
923: end if;
924:
925: dbms_lob.trim(l_profile_apps_clob,length(l_profile_apps_clob)-5);
926: dbms_lob.writeappend(l_profile_apps_clob,length(l_postjoin_sql),l_postjoin_sql);
927: l_ret_profile_apps_clob := fetch_clob(l_profile_apps_clob,'profile_apps','profile_apps');
928: hr_utility.set_location('Leaving ' || l_proc,20);
929: return l_ret_profile_apps_clob;
930:

Line 952: dbms_lob.createtemporary(l_profile_resp_clob,TRUE);

948:
949: BEGIN
950: hr_utility.set_location('Entering ' || l_proc,10);
951: l_profile_resp_clob := get_clob_locator('profile_resp');
952: dbms_lob.createtemporary(l_profile_resp_clob,TRUE);
953:
954: l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
955: ' decode(A.profile_option_value, ''Y'',''Yes'',''N'',''No'',A.profile_option_value) ProfileValue, '||
956: ' fapp.Application_Name, A.level_value from (';

Line 963: dbms_lob.writeappend(l_profile_resp_clob,length(l_prejoin_sql),l_prejoin_sql);

959: ' ,fnd_application_vl fapp '||
960: ' where fprf.profile_option_name = A.profile_name '||
961: ' and fapp.APPLICATION_SHORT_NAME = A.level_value_app ';
962:
963: dbms_lob.writeappend(l_profile_resp_clob,length(l_prejoin_sql),l_prejoin_sql);
964:
965: if p_profile_resp_tab.count > 0 THEN
966: for i in p_profile_resp_tab.first ..
967: p_profile_resp_tab.last loop

Line 984: dbms_lob.writeappend(l_profile_resp_clob,length(l_temp_sql),l_temp_sql);

980: '''' || l_prof_opt_value ||''''||' profile_option_value ' ||
981: ' FROM DUAL UNION';
982:
983:
984: dbms_lob.writeappend(l_profile_resp_clob,length(l_temp_sql),l_temp_sql);
985: end loop;
986: end if;
987:
988: dbms_lob.trim(l_profile_resp_clob,length(l_profile_resp_clob)-5);

Line 988: dbms_lob.trim(l_profile_resp_clob,length(l_profile_resp_clob)-5);

984: dbms_lob.writeappend(l_profile_resp_clob,length(l_temp_sql),l_temp_sql);
985: end loop;
986: end if;
987:
988: dbms_lob.trim(l_profile_resp_clob,length(l_profile_resp_clob)-5);
989: dbms_lob.writeappend(l_profile_resp_clob,length(l_postjoin_sql),l_postjoin_sql);
990: l_ret_profile_resp_clob := fetch_clob(l_profile_resp_clob,'profile_resp','profile_resp');
991:
992: hr_utility.set_location('Leaving ' || l_proc,20);

Line 989: dbms_lob.writeappend(l_profile_resp_clob,length(l_postjoin_sql),l_postjoin_sql);

985: end loop;
986: end if;
987:
988: dbms_lob.trim(l_profile_resp_clob,length(l_profile_resp_clob)-5);
989: dbms_lob.writeappend(l_profile_resp_clob,length(l_postjoin_sql),l_postjoin_sql);
990: l_ret_profile_resp_clob := fetch_clob(l_profile_resp_clob,'profile_resp','profile_resp');
991:
992: hr_utility.set_location('Leaving ' || l_proc,20);
993: return l_ret_profile_resp_clob;

Line 1045: dbms_lob.createtemporary(l_kf_str_clob,TRUE);

1041: l_enabled := l_allow_dynamic;
1042: l_freeze_flex_def := l_enabled;
1043:
1044: l_kf_str_clob := get_clob_locator('JobKeyFlexStruct');
1045: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1046:
1047: l_prejoin_sql := ' select A.appl_short_name , A.flex_code ,A.structure_code,A.structure_title,A.description, A.allow_dynamic_inserts,A.segment_separator,A.enabled,A.freeze_flex_def,'||
1048: ' fapp.Application_Name from( ';
1049:

Line 1054: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);

1050: l_postjoin_sql := ' )A, fnd_application_vl fapp '||
1051: ' where fapp.APPLICATION_SHORT_NAME = A.appl_short_name '||
1052: ' order by Application_Name desc,structure_title desc';
1053:
1054: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1055:
1056: hr_utility.set_location('Before Main Job Keyflex Structure ' || l_proc,20);
1057: if p_kf_job_tab.count > 0 THEN
1058: for i in p_kf_job_tab.first ..

Line 1073: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1069: '''' || l_freeze_flex_def ||''''||' freeze_flex_def, ' ||
1070: '''' || l_enabled ||''''||' enabled ' ||
1071: ' FROM DUAL UNION';
1072:
1073: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1074: end loop;
1075: end if;
1076:
1077: hr_utility.set_location('Before RV Job Keyflex Structure ' || l_proc,30);

Line 1095: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1091: '''' || l_freeze_flex_def ||''''||' freeze_flex_def, ' ||
1092: '''' || l_enabled ||''''||' enabled ' ||
1093: ' FROM DUAL UNION';
1094:
1095: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1096: end loop;
1097: end if;
1098:
1099: hr_utility.set_location('Before No RV Job Keyflex Structure ' || l_proc,40);

Line 1117: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1113: '''' || l_freeze_flex_def ||''''||' freeze_flex_def, ' ||
1114: '''' || l_enabled ||''''||' enabled ' ||
1115: ' FROM DUAL UNION';
1116:
1117: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1118: end loop;
1119: end if;
1120:
1121: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

Line 1121: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

1117: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1118: end loop;
1119: end if;
1120:
1121: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1122: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1123:
1124: if p_kf_job_tab.count > 0 or p_kf_job_rv_tab.count > 0 or p_kf_job_no_rv_tab.count > 0 THEN
1125: p_kf_job_str_clob := fetch_clob(l_kf_str_clob,'JobKeyFlexStruct','JobKeyFlexStructures');

Line 1122: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);

1118: end loop;
1119: end if;
1120:
1121: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1122: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1123:
1124: if p_kf_job_tab.count > 0 or p_kf_job_rv_tab.count > 0 or p_kf_job_no_rv_tab.count > 0 THEN
1125: p_kf_job_str_clob := fetch_clob(l_kf_str_clob,'JobKeyFlexStruct','JobKeyFlexStructures');
1126: end if;

Line 1130: dbms_lob.createtemporary(l_kf_str_clob,TRUE);

1126: end if;
1127:
1128: l_kf_str_clob := null;
1129: l_kf_str_clob := get_clob_locator('PosKeyFlexStruct');
1130: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1131: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1132:
1133: hr_utility.set_location('Before Main Pos Keyflex Structure ' || l_proc,50);
1134:

Line 1131: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);

1127:
1128: l_kf_str_clob := null;
1129: l_kf_str_clob := get_clob_locator('PosKeyFlexStruct');
1130: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1131: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1132:
1133: hr_utility.set_location('Before Main Pos Keyflex Structure ' || l_proc,50);
1134:
1135: if p_kf_pos_tab.count > 0 THEN

Line 1152: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1148: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1149: ' FROM DUAL UNION';
1150:
1151:
1152: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1153: end loop;
1154: end if;
1155:
1156: hr_utility.set_location('Before RV Pos Keyflex Structure ' || l_proc,60);

Line 1174: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1170: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1171: ' FROM DUAL UNION';
1172:
1173:
1174: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1175: end loop;
1176: end if;
1177:
1178: hr_utility.set_location('Before NO RV Pos Keyflex Structure ' || l_proc,70);

Line 1197: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1193: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1194: ' FROM DUAL UNION';
1195:
1196:
1197: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1198: end loop;
1199: end if;
1200:
1201: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

Line 1201: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

1197: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1198: end loop;
1199: end if;
1200:
1201: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1202: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1203:
1204: if p_kf_pos_tab.count > 0 or p_kf_pos_rv_tab.count > 0 or p_kf_pos_no_rv_tab.count > 0 then
1205: p_kf_pos_str_clob := fetch_clob(l_kf_str_clob,'PosKeyFlexStruct','PosKeyFlexStructures');

Line 1202: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);

1198: end loop;
1199: end if;
1200:
1201: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1202: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1203:
1204: if p_kf_pos_tab.count > 0 or p_kf_pos_rv_tab.count > 0 or p_kf_pos_no_rv_tab.count > 0 then
1205: p_kf_pos_str_clob := fetch_clob(l_kf_str_clob,'PosKeyFlexStruct','PosKeyFlexStructures');
1206: end if;

Line 1210: dbms_lob.createtemporary(l_kf_str_clob,TRUE);

1206: end if;
1207:
1208: l_kf_str_clob := null;
1209: l_kf_str_clob := get_clob_locator('GrdKeyFlexStruct');
1210: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1211: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1212:
1213: hr_utility.set_location('Before Main Grd Keyflex Structure ' || l_proc,80);
1214:

Line 1211: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);

1207:
1208: l_kf_str_clob := null;
1209: l_kf_str_clob := get_clob_locator('GrdKeyFlexStruct');
1210: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1211: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1212:
1213: hr_utility.set_location('Before Main Grd Keyflex Structure ' || l_proc,80);
1214:
1215: if p_kf_grd_tab.count > 0 THEN

Line 1232: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1228: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1229: ' FROM DUAL UNION';
1230:
1231:
1232: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1233: end loop;
1234: end if;
1235:
1236: hr_utility.set_location('Before RV Grd Keyflex Structure ' || l_proc,90);

Line 1255: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1251: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1252: ' FROM DUAL UNION';
1253:
1254:
1255: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1256: end loop;
1257: end if;
1258:
1259: hr_utility.set_location('Before NO RV Grd Keyflex Structure ' || l_proc,100);

Line 1277: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1273: '''' || l_enabled ||''''||' enabled, ' ||
1274: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1275: ' FROM DUAL UNION';
1276:
1277: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1278: end loop;
1279: end if;
1280:
1281: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

Line 1281: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

1277: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1278: end loop;
1279: end if;
1280:
1281: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1282: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1283:
1284: if p_kf_grd_tab.count > 0 or p_kf_grd_rv_tab.count > 0 or p_kf_grd_no_rv_tab.count > 0 then
1285: p_kf_grd_str_clob := fetch_clob(l_kf_str_clob,'GrdKeyFlexStruct','GrdKeyFlexStructures');

Line 1282: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);

1278: end loop;
1279: end if;
1280:
1281: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1282: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1283:
1284: if p_kf_grd_tab.count > 0 or p_kf_grd_rv_tab.count > 0 or p_kf_grd_no_rv_tab.count > 0 then
1285: p_kf_grd_str_clob := fetch_clob(l_kf_str_clob,'GrdKeyFlexStruct','GrdKeyFlexStructures');
1286: end if;

Line 1290: dbms_lob.createtemporary(l_kf_str_clob,TRUE);

1286: end if;
1287:
1288: l_kf_str_clob := null;
1289: l_kf_str_clob := get_clob_locator('CmpKeyFlexStruct');
1290: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1291: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1292:
1293: hr_utility.set_location('Before Cmp Keyflex Structure ' || l_proc,110);
1294:

Line 1291: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);

1287:
1288: l_kf_str_clob := null;
1289: l_kf_str_clob := get_clob_locator('CmpKeyFlexStruct');
1290: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1291: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1292:
1293: hr_utility.set_location('Before Cmp Keyflex Structure ' || l_proc,110);
1294:
1295: if p_kf_cmp_tab.count > 0 THEN

Line 1312: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1308: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1309: ' FROM DUAL UNION';
1310:
1311:
1312: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1313: end loop;
1314: end if;
1315:
1316: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

Line 1316: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

1312: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1313: end loop;
1314: end if;
1315:
1316: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1317: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1318:
1319: if p_kf_cmp_tab.count > 0 then
1320: p_kf_cmp_str_clob := fetch_clob(l_kf_str_clob,'CmpKeyFlexStruct','CmpKeyFlexStructures');

Line 1317: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);

1313: end loop;
1314: end if;
1315:
1316: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1317: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1318:
1319: if p_kf_cmp_tab.count > 0 then
1320: p_kf_cmp_str_clob := fetch_clob(l_kf_str_clob,'CmpKeyFlexStruct','CmpKeyFlexStructures');
1321: end if;

Line 1325: dbms_lob.createtemporary(l_kf_str_clob,TRUE);

1321: end if;
1322:
1323: l_kf_str_clob := null;
1324: l_kf_str_clob := get_clob_locator('GrpKeyFlexStruct');
1325: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1326: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1327:
1328: hr_utility.set_location('Before Grp Keyflex Structure ' || l_proc,120);
1329:

Line 1326: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);

1322:
1323: l_kf_str_clob := null;
1324: l_kf_str_clob := get_clob_locator('GrpKeyFlexStruct');
1325: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1326: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1327:
1328: hr_utility.set_location('Before Grp Keyflex Structure ' || l_proc,120);
1329:
1330: if p_kf_grp_tab.count > 0 THEN

Line 1347: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1343: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1344: ' FROM DUAL UNION';
1345:
1346:
1347: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1348: end loop;
1349: end if;
1350:
1351: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

Line 1351: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

1347: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1348: end loop;
1349: end if;
1350:
1351: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1352: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1353:
1354: if p_kf_grp_tab.count > 0 then
1355: p_kf_grp_str_clob := fetch_clob(l_kf_str_clob,'GrpKeyFlexStruct','GrpKeyFlexStructures');

Line 1352: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);

1348: end loop;
1349: end if;
1350:
1351: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1352: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1353:
1354: if p_kf_grp_tab.count > 0 then
1355: p_kf_grp_str_clob := fetch_clob(l_kf_str_clob,'GrpKeyFlexStruct','GrpKeyFlexStructures');
1356: end if;

Line 1360: dbms_lob.createtemporary(l_kf_str_clob,TRUE);

1356: end if;
1357:
1358: l_kf_str_clob := null;
1359: l_kf_str_clob := get_clob_locator('CostKeyFlexStruct');
1360: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1361: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1362:
1363: hr_utility.set_location('Before Cost Keyflex Structure ' || l_proc,130);
1364:

Line 1361: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);

1357:
1358: l_kf_str_clob := null;
1359: l_kf_str_clob := get_clob_locator('CostKeyFlexStruct');
1360: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1361: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1362:
1363: hr_utility.set_location('Before Cost Keyflex Structure ' || l_proc,130);
1364:
1365: if p_kf_cost_tab.count > 0 THEN

Line 1382: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);

1378: '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1379: ' FROM DUAL UNION';
1380:
1381:
1382: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1383: end loop;
1384: end if;
1385:
1386: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

Line 1386: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);

1382: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1383: end loop;
1384: end if;
1385:
1386: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1387: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1388:
1389: if p_kf_cost_tab.count > 0 then
1390: p_kf_cost_str_clob := fetch_clob(l_kf_str_clob,'CostKeyFlexStruct','CostKeyFlexStructures');

Line 1387: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);

1383: end loop;
1384: end if;
1385:
1386: dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1387: dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1388:
1389: if p_kf_cost_tab.count > 0 then
1390: p_kf_cost_str_clob := fetch_clob(l_kf_str_clob,'CostKeyFlexStruct','CostKeyFlexStructures');
1391: end if;

Line 1456: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);

1452: select meaning into l_vs_validation_type from fnd_lookups where lookup_type = 'SEG_VAL_TYPES' and lookup_code = 'I';
1453:
1454:
1455: l_kf_seg_clob := get_clob_locator('JobKeyFlexSegment');
1456: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1457:
1458: l_orderby := ' order by segment_number ';
1459:
1460: hr_utility.set_location('Before Job Keyflex Segment ' ||l_proc,20);

Line 1504: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1500: '''' || p_kf_job_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1501: '''' || p_kf_job_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1502: ' FROM DUAL UNION';
1503:
1504: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1505: end loop;
1506: end if;
1507:
1508:

Line 1552: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1548: '''' || p_kf_job_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1549: '''' || p_kf_job_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1550: ' FROM DUAL UNION';
1551:
1552: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1553: end loop;
1554: end if;
1555:
1556: if p_kf_job_no_rv_seg_tab.count > 0 THEN

Line 1599: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1595: '''' || p_kf_job_no_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1596: '''' || p_kf_job_no_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1597: ' FROM DUAL UNION';
1598:
1599: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1600: end loop;
1601: end if;
1602:
1603: if p_kf_job_seg_tab.count > 0 or p_kf_job_rv_seg_tab.count > 0 or p_kf_job_no_rv_seg_tab.count > 0 then

Line 1604: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);

1600: end loop;
1601: end if;
1602:
1603: if p_kf_job_seg_tab.count > 0 or p_kf_job_rv_seg_tab.count > 0 or p_kf_job_no_rv_seg_tab.count > 0 then
1604: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1605:
1606: --Added to order the segments display in technical summary report for Job ff
1607: l_temp_sql := ' ORDER BY column_name ';
1608: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

Line 1608: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1604: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1605:
1606: --Added to order the segments display in technical summary report for Job ff
1607: l_temp_sql := ' ORDER BY column_name ';
1608: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1609:
1610: p_kf_job_seg_clob := fetch_clob(l_kf_seg_clob,'JobKeyFlexSegment','JobKeyFlexSegments');
1611: end if;
1612:

Line 1618: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);

1614: hr_utility.set_location('Before Pos Keyflex Segment ' ||l_proc,20);
1615:
1616: l_kf_seg_clob := null;
1617: l_kf_seg_clob := get_clob_locator('PosKeyFlexSegment');
1618: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1619:
1620: if p_kf_pos_seg_tab.count > 0 THEN
1621: for i in p_kf_pos_seg_tab.first ..
1622: p_kf_pos_seg_tab.last loop

Line 1663: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1659: '''' || p_kf_pos_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1660: '''' || p_kf_pos_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1661: ' FROM DUAL UNION';
1662:
1663: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1664: end loop;
1665: end if;
1666:
1667: if p_kf_pos_rv_seg_tab.count > 0 THEN

Line 1710: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1706: '''' || p_kf_pos_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1707: '''' || p_kf_pos_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1708: ' FROM DUAL UNION';
1709:
1710: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1711: end loop;
1712: end if;
1713:
1714: if p_kf_pos_no_rv_seg_tab.count > 0 THEN

Line 1757: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1753: '''' || p_kf_pos_no_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1754: '''' || p_kf_pos_no_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1755: ' FROM DUAL UNION';
1756:
1757: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1758: end loop;
1759: end if;
1760:
1761: if p_kf_pos_seg_tab.count > 0 or p_kf_pos_rv_seg_tab.count > 0 or p_kf_pos_no_rv_seg_tab.count > 0 then

Line 1762: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);

1758: end loop;
1759: end if;
1760:
1761: if p_kf_pos_seg_tab.count > 0 or p_kf_pos_rv_seg_tab.count > 0 or p_kf_pos_no_rv_seg_tab.count > 0 then
1762: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1763:
1764: --Added to order the segments display in technical summary report for position ff
1765: l_temp_sql := ' ORDER BY column_name ';
1766: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

Line 1766: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1762: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1763:
1764: --Added to order the segments display in technical summary report for position ff
1765: l_temp_sql := ' ORDER BY column_name ';
1766: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1767:
1768: p_kf_pos_seg_clob := fetch_clob(l_kf_seg_clob,'PosNoRVKeyFlexSegment','PosNoRVKeyFlexSegments');
1769: end if;
1770:

Line 1777: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);

1773: hr_utility.set_location('Before Grade Keyflex Segment ' ||l_proc,20);
1774:
1775: l_kf_seg_clob := null;
1776: l_kf_seg_clob := get_clob_locator('GrdKeyFlexSegment');
1777: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1778:
1779: if p_kf_grd_seg_tab.count > 0 THEN
1780: for i in p_kf_grd_seg_tab.first ..
1781: p_kf_grd_seg_tab.last loop

Line 1822: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1818: '''' || p_kf_grd_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1819: '''' || p_kf_grd_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1820: ' FROM DUAL UNION';
1821:
1822: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1823: end loop;
1824: end if;
1825:
1826: if p_kf_grd_rv_seg_tab.count > 0 THEN

Line 1869: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1865: '''' || p_kf_grd_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1866: '''' || p_kf_grd_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1867: ' FROM DUAL UNION';
1868:
1869: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1870:
1871: end loop;
1872: end if;
1873:

Line 1917: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1913: '''' || p_kf_grd_no_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1914: '''' || p_kf_grd_no_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1915: ' FROM DUAL UNION';
1916:
1917: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1918:
1919: end loop;
1920: end if;
1921:

Line 1924: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);

1920: end if;
1921:
1922:
1923: if p_kf_grd_seg_tab.count > 0 or p_kf_grd_rv_seg_tab.count > 0 or p_kf_grd_no_rv_seg_tab.count > 0 then
1924: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1925:
1926: --Added to order the segments display in technical summary report for grade ff
1927: l_temp_sql := ' ORDER BY column_name ';
1928: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

Line 1928: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1924: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1925:
1926: --Added to order the segments display in technical summary report for grade ff
1927: l_temp_sql := ' ORDER BY column_name ';
1928: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1929:
1930: p_kf_grd_seg_clob := fetch_clob(l_kf_seg_clob,'GrdNoRVKeyFlexSegment','GrdNoRVKeyFlexSegments');
1931: end if;
1932:

Line 1938: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);

1934: hr_utility.set_location('Before Grp Keyflex Segment ' ||l_proc,20);
1935:
1936: l_kf_seg_clob := null;
1937: l_kf_seg_clob := get_clob_locator('GrpKeyFlexSegment');
1938: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1939:
1940:
1941: if p_kf_grp_seg_tab.count > 0 THEN
1942: for i in p_kf_grp_seg_tab.first ..

Line 1984: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1980: '''' || p_kf_grp_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1981: '''' || p_kf_grp_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1982: ' FROM DUAL UNION';
1983:
1984: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1985: end loop;
1986: end if;
1987:
1988: if p_kf_grp_seg_tab.count > 0 then

Line 1989: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);

1985: end loop;
1986: end if;
1987:
1988: if p_kf_grp_seg_tab.count > 0 then
1989: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1990:
1991: --Added to order the segments display in technical summary report for grp ff
1992: l_temp_sql := ' ORDER BY column_name ';
1993: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

Line 1993: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

1989: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1990:
1991: --Added to order the segments display in technical summary report for grp ff
1992: l_temp_sql := ' ORDER BY column_name ';
1993: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1994:
1995: p_kf_grp_seg_clob := fetch_clob(l_kf_seg_clob,'GrpKeyFlexSegment','GrpKeyFlexSegment');
1996: end if;
1997:

Line 2004: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);

2000: hr_utility.set_location('Before Cmp Keyflex Segment ' ||l_proc,20);
2001:
2002: l_kf_seg_clob := null;
2003: l_kf_seg_clob := get_clob_locator('CmpKeyFlexSegment');
2004: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
2005:
2006: if p_kf_cmp_seg_tab.count > 0 THEN
2007: for i in p_kf_cmp_seg_tab.first ..
2008: p_kf_cmp_seg_tab.last loop

Line 2049: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

2045: '''' || p_kf_cmp_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
2046: '''' || p_kf_cmp_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
2047: ' FROM DUAL UNION';
2048:
2049: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2050: end loop;
2051: end if;
2052:
2053: if p_kf_cmp_seg_tab.count > 0 then

Line 2054: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);

2050: end loop;
2051: end if;
2052:
2053: if p_kf_cmp_seg_tab.count > 0 then
2054: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
2055:
2056: --Added to order the segments display in technical summary report for cmp ff
2057: l_temp_sql := ' ORDER BY column_name ';
2058: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

Line 2058: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

2054: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
2055:
2056: --Added to order the segments display in technical summary report for cmp ff
2057: l_temp_sql := ' ORDER BY column_name ';
2058: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2059:
2060: p_kf_cmp_seg_clob := fetch_clob(l_kf_seg_clob,'CmpKeyFlexSegment','CmpKeyFlexSegments');
2061: end if;
2062:

Line 2069: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);

2065: hr_utility.set_location('Before Cost Keyflex Segment ' ||l_proc,20);
2066:
2067: l_kf_seg_clob := null;
2068: l_kf_seg_clob := get_clob_locator('CostKeyFlexSegment');
2069: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
2070:
2071: if p_kf_cost_seg_tab.count > 0 THEN
2072: for i in p_kf_cost_seg_tab.first ..
2073: p_kf_cost_seg_tab.last loop

Line 2114: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

2110: '''' || p_kf_cost_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
2111: '''' || p_kf_cost_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
2112: ' FROM DUAL UNION';
2113:
2114: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2115: end loop;
2116: end if;
2117:
2118: hr_utility.set_location('Done with Cost Keyflex Segment ' ||l_proc,20);

Line 2121: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);

2117:
2118: hr_utility.set_location('Done with Cost Keyflex Segment ' ||l_proc,20);
2119:
2120: if p_kf_cost_seg_tab.count > 0 then
2121: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
2122:
2123: --Added to order the segments display in technical summary report for cost ff
2124: l_temp_sql := ' ORDER BY column_name ';
2125: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

Line 2125: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);

2121: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
2122:
2123: --Added to order the segments display in technical summary report for cost ff
2124: l_temp_sql := ' ORDER BY column_name ';
2125: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2126:
2127: p_kf_cost_seg_clob := fetch_clob(l_kf_seg_clob,'CostKeyFlexSegment','CostKeyFlexSegments');
2128: end if;
2129:

Line 3073: dbms_lob.createtemporary(l_kf_int_hr_clob,TRUE);

3069: BEGIN
3070:
3071: hr_utility.set_location('Entering ' ||l_proc,10);
3072: l_kf_int_hr_clob := get_clob_locator('IntlHRMSSetup');
3073: dbms_lob.createtemporary(l_kf_int_hr_clob,TRUE);
3074:
3075: l_prejoin_sql := ' select terr.TERRITORY_SHORT_NAME Legislation , curr.name Currency, A.Tax_Start_Date, '
3076: ||' lookup.meaning Install_Tax_Unit_Val '
3077: ||' from ( ';

Line 3086: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_prejoin_sql),l_prejoin_sql);

3082: ||' and lookup.lookup_code = A.install_tax_unit '
3083: ||' and lookup.lookup_type=''YES_NO'' and lookup.application_id=800 '
3084: ||' order by Legislation desc';
3085:
3086: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_prejoin_sql),l_prejoin_sql);
3087:
3088: if p_int_hrms_setup_tab.count > 0 THEN
3089: for i in p_int_hrms_setup_tab.first ..
3090: p_int_hrms_setup_tab.last loop

Line 3099: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_temp_sql),l_temp_sql);

3095: '''' || p_int_hrms_setup_tab(i).tax_start_date ||''''||' tax_start_date,' ||
3096: '''' || p_int_hrms_setup_tab(i).install_tax_unit ||''''||' install_tax_unit' ||
3097: ' FROM DUAL UNION';
3098:
3099: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_temp_sql),l_temp_sql);
3100: end loop;
3101: end if;
3102:
3103: if (length(l_kf_int_hr_clob)>length(l_prejoin_sql)+ 5)

Line 3106: dbms_lob.trim(l_kf_int_hr_clob,length(l_kf_int_hr_clob)-5);

3102:
3103: if (length(l_kf_int_hr_clob)>length(l_prejoin_sql)+ 5)
3104: then
3105:
3106: dbms_lob.trim(l_kf_int_hr_clob,length(l_kf_int_hr_clob)-5);
3107: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_postjoin_sql),l_postjoin_sql);
3108: l_ret_kf_int_hr_clob := fetch_clob(l_kf_int_hr_clob,'IntlHRMSSetup','IntlHRMSSetups');
3109:
3110: end if;

Line 3107: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_postjoin_sql),l_postjoin_sql);

3103: if (length(l_kf_int_hr_clob)>length(l_prejoin_sql)+ 5)
3104: then
3105:
3106: dbms_lob.trim(l_kf_int_hr_clob,length(l_kf_int_hr_clob)-5);
3107: dbms_lob.writeappend(l_kf_int_hr_clob,length(l_postjoin_sql),l_postjoin_sql);
3108: l_ret_kf_int_hr_clob := fetch_clob(l_kf_int_hr_clob,'IntlHRMSSetup','IntlHRMSSetups');
3109:
3110: end if;
3111:

Line 3134: dbms_lob.createtemporary(l_secprf_clob,TRUE);

3130: BEGIN
3131:
3132: hr_utility.set_location('Entering ' ||l_proc,10);
3133: l_secprf_clob := get_clob_locator('SecurityProfile');
3134: dbms_lob.createtemporary(l_secprf_clob,TRUE);
3135:
3136: l_orderby := ' order by business_group_name desc ,security_group_name desc ';
3137:
3138: if p_security_profile_tab.count > 0 THEN

Line 3153: dbms_lob.writeappend(l_secprf_clob,length(l_temp_sql),l_temp_sql);

3149: ' FROM DUAL UNION';
3150:
3151:
3152:
3153: dbms_lob.writeappend(l_secprf_clob,length(l_temp_sql),l_temp_sql);
3154: end loop;
3155: end if;
3156:
3157: if length(l_secprf_clob)> 5

Line 3159: dbms_lob.trim(l_secprf_clob,length(l_secprf_clob)-5);

3155: end if;
3156:
3157: if length(l_secprf_clob)> 5
3158: then
3159: dbms_lob.trim(l_secprf_clob,length(l_secprf_clob)-5);
3160: dbms_lob.writeappend(l_secprf_clob,length(l_orderby),l_orderby);
3161:
3162: l_ret_secprf_clob := fetch_clob(l_secprf_clob,'SecurityProfile','SecurityProfiles');
3163: end if;

Line 3160: dbms_lob.writeappend(l_secprf_clob,length(l_orderby),l_orderby);

3156:
3157: if length(l_secprf_clob)> 5
3158: then
3159: dbms_lob.trim(l_secprf_clob,length(l_secprf_clob)-5);
3160: dbms_lob.writeappend(l_secprf_clob,length(l_orderby),l_orderby);
3161:
3162: l_ret_secprf_clob := fetch_clob(l_secprf_clob,'SecurityProfile','SecurityProfiles');
3163: end if;
3164:

Line 3187: dbms_lob.createtemporary(l_org_hier_clob,TRUE);

3183: BEGIN
3184:
3185: hr_utility.set_location('Entering ' ||l_proc,10);
3186: l_org_hier_clob := get_clob_locator('OrgHierarchy');
3187: dbms_lob.createtemporary(l_org_hier_clob,TRUE);
3188:
3189: if p_org_hierarchy_tab.count > 0 THEN
3190: for i in p_org_hierarchy_tab.first ..
3191: p_org_hierarchy_tab.last loop

Line 3198: dbms_lob.writeappend(l_org_hier_clob,length(l_temp_sql),l_temp_sql);

3194: '''' || p_org_hierarchy_tab(i).name ||''''||' name , ' ||
3195: '''' || p_org_hierarchy_tab(i).org_structure_version_id ||''''||' org_structure_version_id ' ||
3196: ' FROM DUAL UNION';
3197:
3198: dbms_lob.writeappend(l_org_hier_clob,length(l_temp_sql),l_temp_sql);
3199: end loop;
3200: end if;
3201:
3202: if length(l_org_hier_clob)> 5

Line 3204: dbms_lob.trim(l_org_hier_clob,length(l_org_hier_clob)-5);

3200: end if;
3201:
3202: if length(l_org_hier_clob)> 5
3203: then
3204: dbms_lob.trim(l_org_hier_clob,length(l_org_hier_clob)-5);
3205: l_ret_org_hier_clob := fetch_clob(l_org_hier_clob,'OrgHierarchy','OrgHierarchies');
3206:
3207: end if;
3208: hr_utility.set_location('Leaving ' ||l_proc,20);

Line 3229: dbms_lob.createtemporary(l_org_hier_ele_clob,TRUE);

3225: BEGIN
3226:
3227: hr_utility.set_location('Entering '||l_proc,10);
3228: l_org_hier_ele_clob := get_clob_locator('OrgHierarchyEle');
3229: dbms_lob.createtemporary(l_org_hier_ele_clob,TRUE);
3230:
3231: if p_org_hierarchy_ele_oc_tab.count > 0 THEN
3232: for i in p_org_hierarchy_ele_oc_tab.first ..
3233: p_org_hierarchy_ele_oc_tab.last loop

Line 3241: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);

3237: '''' || REPLACE ( p_org_hierarchy_ele_oc_tab(i).parent_organization_name , '''', '''''') ||''''||' parent_organization_name,'||
3238: '''' || REPLACE (p_org_hierarchy_ele_oc_tab(i).child_organization_name, '''', '''''') ||''''||' child_organization_name '||
3239: ' FROM DUAL UNION';
3240:
3241: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3242: end loop;
3243: end if;
3244:
3245: if p_org_hierarchy_ele_le_tab.count > 0 THEN

Line 3255: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);

3251: '''' || REPLACE ( p_org_hierarchy_ele_le_tab(i).parent_organization_name , '''', '''''') ||''''||' parent_organization_name,'||
3252: '''' || REPLACE ( p_org_hierarchy_ele_le_tab(i).child_organization_name, '''','''''') ||''''||' child_organization_name '||
3253: ' FROM DUAL UNION';
3254:
3255: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3256: end loop;
3257: end if;
3258:
3259: if length(l_org_hier_ele_clob)> 5

Line 3261: dbms_lob.trim(l_org_hier_ele_clob,length(l_org_hier_ele_clob)-5);

3257: end if;
3258:
3259: if length(l_org_hier_ele_clob)> 5
3260: then
3261: dbms_lob.trim(l_org_hier_ele_clob,length(l_org_hier_ele_clob)-5);
3262: l_ret_org_hier_ele_clob := fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEle','OrgHierarchyEles');
3263:
3264: end if;
3265:

Line 3295: dbms_lob.createtemporary(l_org_hier_ele_clob,TRUE);

3291: hr_utility.set_location('Entering '||l_proc,10);
3292:
3293: l_org_hier_ele_clob := get_clob_locator('OrgHierarchyEleForPV');
3294: l_temp_clob := get_clob_locator('OrgHierarchyEleForPV');
3295: dbms_lob.createtemporary(l_org_hier_ele_clob,TRUE);
3296:
3297: if p_org_hierarchy_ele_oc_tab.count > 0 THEN
3298:
3299: l_parent_org_name := ' SELECT ' ||

Line 3326: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);

3322: end if;
3323: end loop;
3324: end if;
3325: l_temp_sql := l_temp_sql || ' FROM DUAL';
3326: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3327: l_org_hier_append_clob := l_org_hier_append_clob || fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEleForPV','OrgHierarchyElementsForPV');
3328: dbms_lob.trim(l_org_hier_ele_clob,0);
3329: end loop;
3330:

Line 3328: dbms_lob.trim(l_org_hier_ele_clob,0);

3324: end if;
3325: l_temp_sql := l_temp_sql || ' FROM DUAL';
3326: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3327: l_org_hier_append_clob := l_org_hier_append_clob || fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEleForPV','OrgHierarchyElementsForPV');
3328: dbms_lob.trim(l_org_hier_ele_clob,0);
3329: end loop;
3330:
3331: l_temp_sql := l_parent_org_name;
3332:

Line 3352: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);

3348: l_temp_sql := l_temp_sql ||
3349: ' FROM DUAL';
3350:
3351: if l_le_exists then
3352: dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3353: l_org_hier_append_clob := l_org_hier_append_clob || fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEleForPV','OrgHierarchyElementsForPV');
3354: end if;
3355: end if;
3356:

Line 3380: dbms_lob.createtemporary(l_post_install_clob,TRUE);

3376: BEGIN
3377:
3378: hr_utility.set_location('Entering ' ||l_proc,10);
3379: l_post_install_clob := get_clob_locator('PostInstall');
3380: dbms_lob.createtemporary(l_post_install_clob,TRUE);
3381:
3382: l_prejoin_sql := ' select decode(A.legislation_code,''BF'',''International'',terr.TERRITORY_SHORT_NAME) Legislation , '
3383: ||' fapp.application_name '
3384: ||' from ( ';

Line 3391: dbms_lob.writeappend(l_post_install_clob,length(l_prejoin_sql),l_prejoin_sql);

3387: ||' where terr.TERRITORY_CODE = A.legislation_code '
3388: ||' and fapp.application_short_name = A.application_short_name '
3389: ||' order by Legislation desc , fapp.application_name desc';
3390:
3391: dbms_lob.writeappend(l_post_install_clob,length(l_prejoin_sql),l_prejoin_sql);
3392:
3393: if p_post_install_tab.count > 0 THEN
3394: for i in p_post_install_tab.first ..
3395: p_post_install_tab.last loop

Line 3402: dbms_lob.writeappend(l_post_install_clob,length(l_temp_sql),l_temp_sql);

3398: '''' || p_post_install_tab(i).legislation_code ||''''||' legislation_code,' ||
3399: '''' || p_post_install_tab(i).applicaton_short_name ||''''||' application_short_name ' ||
3400: ' FROM DUAL UNION';
3401:
3402: dbms_lob.writeappend(l_post_install_clob,length(l_temp_sql),l_temp_sql);
3403: end loop;
3404: end if;
3405:
3406: if (length(l_post_install_clob)>length(l_prejoin_sql)+ 5)

Line 3408: dbms_lob.trim(l_post_install_clob,length(l_post_install_clob)-5);

3404: end if;
3405:
3406: if (length(l_post_install_clob)>length(l_prejoin_sql)+ 5)
3407: then
3408: dbms_lob.trim(l_post_install_clob,length(l_post_install_clob)-5);
3409: dbms_lob.writeappend(l_post_install_clob,length(l_postjoin_sql),l_postjoin_sql);
3410: l_ret_post_install_clob := fetch_clob(l_post_install_clob,'PostInstall','PostInstalls');
3411:
3412: end if;

Line 3409: dbms_lob.writeappend(l_post_install_clob,length(l_postjoin_sql),l_postjoin_sql);

3405:
3406: if (length(l_post_install_clob)>length(l_prejoin_sql)+ 5)
3407: then
3408: dbms_lob.trim(l_post_install_clob,length(l_post_install_clob)-5);
3409: dbms_lob.writeappend(l_post_install_clob,length(l_postjoin_sql),l_postjoin_sql);
3410: l_ret_post_install_clob := fetch_clob(l_post_install_clob,'PostInstall','PostInstalls');
3411:
3412: end if;
3413: