[Home] [Help]
PACKAGE BODY: APPS.IRC_JPS_GENERATOR
Source
1 package body irc_JPS_generator as
2 /* $Header: irjpsgen.pkb 120.11.12010000.2 2008/08/05 10:49:47 ubhat ship $ */
3
4 procedure generateJPSint(p_person_id in number
5 ,p_stylesheet varchar2 default null
6 ,p_raw_doc out nocopy clob
7 ,p_formatted_doc out nocopy clob) is
8 l_query varchar2(32767);
9 l_formatted_doc CLOB;
10 lv_stylesheet varchar2(32767);
11 l_stylesheet_url varchar2(2000);
12 stylesheetdoc xmldom.DOMDocument;
13 stylesheet xslprocessor.Stylesheet;
14 engine xslprocessor.Processor;
15 parser xmlparser.parser;
16 xmldoc xmldom.DOMDocument;
17 clobdoc CLOB;
18 ctx DBMS_XMLQUERY.ctxType;
19 tv_sheet utl_http.html_pieces;
20 l_index number;
21 begin
22
23 hr_utility.set_location('Entering: generateJPSinit',10);
24 l_query:= 'select per.*'
25 ||' ,per.full_name personname'
26 ||' ,per.last_name personlastname'
27 ||' ,decode(per.sex, ''M'',''1'',''F'',''2'',''9'') gender'
28 ||' ,translate(to_char(per.date_of_birth,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') dateofbirth'
29 --
30 -- This cursor retrieves all the Recruiting addresses associated
31 -- with the person
32 --
33 ||' ,cursor (select address_line1 addressline1'
34 ||' ,address_line2 addressline2'
35 ||' ,address_line3 addressline3'
36 ||' ,region_1 region1'
37 ||' ,region_2 region2'
38 ||' ,region_3 region3'
39 ||' ,postal_code postalcode'
40 ||' ,town_or_city townorcity'
41 ||' ,country country'
42 ||' ,add_information13'
43 ||' ,add_information14'
44 ||' ,add_information15'
45 ||' ,add_information16'
46 ||' ,add_information17'
47 ||' ,add_information18'
48 ||' ,add_information19'
49 ||' ,add_information20'
50 ||' from per_addresses'
51 ||' where address_type = ''REC'''
52 ||' and person_id=per.person_id) as address'
53 --
54 -- This cursor retrieves the telephone numbers that can be passed
55 -- The decode statement is used to get the correct order by :
56 -- the translate removes H and W, and changes 1,2 and 3 to Vs
57 -- as they are irrelevant to the numbering
58 --
59 ||' ,cursor ( select phn.*'
60 ||' , substr(phone_type,1,1) phonelabel'
61 ||' , decode (translate(phone_type,''123HW'',''VVV'')'
62 ||' ,''V'', ''1'''
63 ||' ,''F'', ''2'''
64 ||' ,''P'', ''3'''
65 ||' ,''1'') phonetype'
66 ||' from per_phones phn'
67 ||' where parent_id=per.person_id'
68 ||' and substr(phone_type, 1,1) in (''H'',''W'',''P'',''M'')) as phone'
69 ||' ,usr.email_address emailaddress'
70 ||' ,cursor(select ppe.*'
71 ||' ,ppj.job_name job_name,ppj.*'
72 ||' ,ppe.employer_name employername'
73 ||' ,ppe.employer_address employeraddress'
74 ||' ,ppe.description descriptionText'
75 ||' ,translate(to_char(ppe.start_date,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') attendedstartdate'
76 ||' ,translate(to_char(ppe.end_date,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') attendedenddate'
77 ||' from per_previous_employers ppe'
78 ||' ,per_previous_jobs ppj'
79 ||' where ppe.previous_employer_id = ppj.previous_employer_id'
80 ||' and ppe.person_id=per.person_id) as emphistory'
81 --
82 -- This cursor retrieves the qualifications held in per_qualifications.
83 -- 'attendanceid' is used in the stylesheet to determine the section
84 -- of the generated XML document to place the data.
85 --
86 ||' ,cursor(select qua.title title,qua.*'
87 ||' ,esa.establishment establishment'
88 ||' ,esa.address address'
89 ||' ,esa.*'
90 ||' ,qtytl.name type'
91 ||' ,decode(qty.category '
92 ||' ,''SKILL'', ''skill'''
93 ||' ,''EXPERIENCE'', ''experience'''
94 ||' ,''EDUCATION'', ''education'''
95 ||' ,''LICENSE'', ''license'''
96 ||' ,''CERTIFICATION'', ''certification'''
97 ||' ,''EQUIPMENT'', ''equipment'''
98 ||' ,''other'') categoryCode'
99 ||' ,translate(to_char(esa.attended_start_date,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') attendedstartdate'
100 ||' ,translate(to_char(esa.attended_end_date,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') attendedenddate'
101 ||' from per_qualifications_vl qua'
102 ||' ,per_qualification_types qty'
103 ||' ,per_qualification_types_tl qtytl'
104 ||' ,per_establishment_attendances esa'
105 ||' where qua.qualification_type_id = qty.qualification_type_id'
106 ||' and qua.qualification_type_id = qtytl.qualification_type_id'
107 ||' and ppttl.language = qtytl.language'
108 ||' and qua.attendance_id = esa.attendance_id'
109 ||' and qua.person_id = per.person_id'
110 ||' and esa.person_id=per.person_id) as education'
111
112 ||' ,cursor(select qua.title title,qua.*'
113 ||' ,qty.name type'
114 ||' ,decode(qty.category '
115 ||' ,''SKILL'', ''skill'''
116 ||' ,''EXPERIENCE'', ''experience'''
117 ||' ,''EDUCATION'', ''education'''
118 ||' ,''LICENSE'', ''license'''
119 ||' ,''CERTIFICATION'', ''certification'''
120 ||' ,''EQUIPMENT'', ''equipment'''
121 ||' ,''other'') categoryCode'
122 ||' from per_qualifications_vl qua'
123 ||' ,per_qualification_types qty'
124 ||' ,per_qualification_types_tl qtytl'
125 ||' where qua.qualification_type_id = qty.qualification_type_id'
126 ||' and qua.qualification_type_id = qtytl.qualification_type_id'
127 ||' and ppttl.language = qtytl.language'
128 ||' and qua.attendance_id is null'
129 ||' and qua.person_id=per.person_id) as non_edu_quals'
130 --
131 -- This cursor retrieves the skills that are held the competences
132 -- tables. Only skills that have an associated rating_level are
133 -- required
134 --
135 ||' ,cursor(select /*+ INDEX (rtl PER_RATING_LEVELS_PK)'
136 ||' INDEX(cmp PER_COMPETENCES_PK) INDEX(rsc PER_RATING_SCALES_PK)*/ '
137 ||' cel.*,cmpt.name name'
138 ||' , decode(round((rtl.step_value/decode(nvl(nvl(rsc.max_scale_step, cmp.max_level)'
139 ||' ,rtl.step_value),0,1,nvl(nvl(rsc.max_scale_step, cmp.max_level)'
140 ||' ,rtl.step_value)))*5),0,1'
141 ||' , round((rtl.step_value/decode(nvl(nvl(rsc.max_scale_step, cmp.max_level)'
142 ||' ,rtl.step_value),0,1,nvl(nvl(rsc.max_scale_step, cmp.max_level)'
143 ||' ,rtl.step_value)))*5)) lvl'
144 ||' from per_competence_elements cel'
145 ||' , per_competences cmp'
146 ||' , per_competences_tl cmpt'
147 ||' , per_rating_levels rtl'
148 ||' , per_rating_scales rsc'
149 ||' where cel.competence_id = cmp.competence_id'
150 ||' and cel.competence_id = cmpt.competence_id'
151 ||' and ppttl.language= cmpt.language'
152 ||' and cel.proficiency_level_id = rtl.rating_level_id'
153 ||' and rtl.rating_scale_id = rsc.rating_scale_id(+)'
154 ||' and cel.type = ''PERSONAL'''
155 ||' and cel.person_id = per.person_id) as skills'
156 ||' ,cursor(select cel.*,cmp.name name'
157 ||' from per_competence_elements cel'
158 ||' , per_competences_tl cmp'
159 ||' where cel.competence_id = cmp.competence_id'
160 ||' and cmp.language=ppttl.language'
161 ||' and cel.proficiency_level_id is null'
162 ||' and cel.type = ''PERSONAL'''
163 ||' and cel.person_id = per.person_id) as skills_no_level'
164 ||' , cursor(select isc.* '
165 ||' ,cursor(select * from irc_location_criteria_values ilcv'
166 ||' where ilcv.search_criteria_id=isc.search_criteria_id) as locations'
167 ||' ,cursor(select * from irc_prof_area_criteria_values ipacv'
168 ||' where ipacv.search_criteria_id=isc.search_criteria_id) as professional_areas'
169 ||' from irc_search_criteria isc'
170 ||' where isc.object_id=per.person_id'
171 ||' and isc.object_type=''WPREF'') as work_preferences'
172 ||' from per_all_people_f per '
173 ||' ,fnd_user usr '
174 ||' ,per_person_types_tl ppttl '
175 ||' where per.person_id = :1'
176 ||' and usr.employee_id(+)=per.person_id'
177 ||' and trunc(sysdate) between nvl(usr.start_date(+),trunc(sysdate))'
178 ||' and nvl(usr.end_date(+),trunc(sysdate))'
179 ||' and trunc(sysdate) between per.effective_start_date'
180 ||' and per.effective_end_date'
181 ||' and per.person_type_id=ppttl.person_type_id'
182 ||' and ppttl.language=userenv(''LANG'')'
183 ||' and rownum=1';
184
185 hr_utility.set_location('Formed the query to retrieve data',11);
186
187 if (p_stylesheet is null) then
188
189 hr_utility.set_location('Stylesheet is null',12);
190
191 lv_stylesheet:='<?xml version="1.0" ?>'||
192 '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">'||
193 '<xsl:output method="xml" indent="yes" doctype-system="JobPositionSeeker-1_1.dtd"/>'||
194 '<xsl:template match="jobpositionseeker">'||
195 '<xsl:for-each select="row">
196 ';
197
198 lv_stylesheet:=lv_stylesheet||'<JobPositionSeeker>
199 '||'<JobPositionSeekerId idOwner="oracle.com"><xsl:value-of select="person_id"/></JobPositionSeekerId>
200 '||'<PersonalData>
201 '||'<PersonName>
202 '||'<FormattedName><xsl:value-of select="personname"/></FormattedName>
203 '||'<GivenName><xsl:value-of select="first_name"/></GivenName>
204 '||'<PreferredGivenName><xsl:value-of select="known_as"/></PreferredGivenName>
205 '||'<MiddleName><xsl:value-of select="middle_names"/></MiddleName>
206 '||'<FamilyName prefix="{pre_name_adjunct}"><xsl:value-of select="personlastname"/></FamilyName>
207 '||'<Affix type="formOfAddress"><xsl:value-of select="title"/></Affix>
208 '||'<Affix type="qualification"><xsl:value-of select="honors"/></Affix>
209 '||'</PersonName>
210 '||'<!--Dont display address XML if there are no addresses -->
211 '||'<xsl:if test="count(address/address_row/country)!=0">
212 '||'<xsl:for-each select="address/address_row">'||'<PostalAddress>
213 '||'<CountryCode><xsl:value-of select="country"/></CountryCode>
214 '||'<PostalCode><xsl:value-of select="postalcode"/></PostalCode>
215 '||'<Region><xsl:value-of select="region1"/></Region>
216 '||'<Region><xsl:value-of select="region2"/></Region>
217 '||'<Region><xsl:value-of select="region3"/></Region>
218 '||'<Municipality><xsl:value-of select="townorcity"/></Municipality>
219 '||'<DeliveryAddress>
220 '||'<AddressLine><xsl:value-of select="addressline1"/></AddressLine>
221 '||'<AddressLine><xsl:value-of select="addressline2"/></AddressLine>
222 '||'<AddressLine><xsl:value-of select="addressline3"/></AddressLine>
223 '||'</DeliveryAddress>
224 '||'</PostalAddress>
225 '||'</xsl:for-each>
226 '||'</xsl:if>
227 '||'<xsl:for-each select="phone/phone_row">
228 '||'<xsl:sort select="phonetype"/><!-- This is 1(for Voice), 2(for Fax), 3(for pager)-->
229 '||'<xsl:choose>
230 '||'<xsl:when test="phonetype=''1''"><!-- Phone No is a Voice number -->'||'<VoiceNumber label="{phonelabel}">
231 '||'<TelNumber><xsl:value-of select="phone_number"/></TelNumber>
232 '||'</VoiceNumber>
233 '||'</xsl:when>
234 '||'<xsl:when test="phonetype=''2''"><!-- Phone No. is a Fax number -->'||'<FaxNumber label="{phonelabel}">
235 '||'<TelNumber><xsl:value-of select="phone_number"/></TelNumber>
236 '||'</FaxNumber>
237 '||'</xsl:when>
238 '||'<xsl:when test="phonetype=''3''"><!-- Phone No. is a Pager number -->'||'<PagerNumber label="{phonelabel}">
239 '||'<TelNumber><xsl:value-of select="phone_number"/></TelNumber>
240 '||'</PagerNumber>
241 '||'</xsl:when>
242 '||'</xsl:choose>
243 '||'</xsl:for-each>'||'<E-mail><xsl:value-of select="emailaddress"/></E-mail>
244 '||'<DemographicDetail>
245 '||'<GovernmentId countryCode="{identifiercountry}"><xsl:value-of select="national_identifer"/></GovernmentId>
246 '||'<DateOfBirth>
247 '||'<Date><xsl:value-of select="dateofbirth"/></Date>
248 '||'</DateOfBirth>
249 '||'<Sex type="{gender}"/>
250 '||'<Ethnicity><xsl:value-of select="declaredethnicity"/></Ethnicity>
251 '||'<Language><xsl:value-of select="correspondence_language"/></Language>
252 '||'</DemographicDetail>
253 '||'</PersonalData>
254 '||'<!-- Only write the profile section if there are rows in education with a title
255 '||' but not an attendance_id -->
256 '||'<xsl:if test="count(non_edu_quals/non_edu_quals_row/title) !=0">'||'<Profile>
257 <xsl:for-each select="non_edu_quals/non_edu_quals_row">
258 '||'<Qualification type="{categorycode}" description="{type}">
259 '||'<xsl:value-of select="title"/>
260 '||'</Qualification></xsl:for-each>
261 '||'</Profile></xsl:if>
262 '||'<Resume>
263 '||'<!-- The element StructuredResume should not be displayed if there isnt going
264 '||' to be any data in it. Therefore a few checks need to be made.-->
265 '||'<xsl:if test="count(education/education_row/attendance_id)!=0
266 '||' or count(skills/skills_row/name)!=0
267 '||' or count(education/education_row/attendance_id)!=0
268 '||' or count(emphistory/emphistory_row/employername)!=0 ">'||'<StructuredResume>
269 '||'<xsl:if test="count(emphistory/emphistory_row/employername)!=0">'||'<EmploymentHistory>
270 '||'<xsl:for-each select="emphistory/emphistory_row">'||'<Position>
271 '||'<EmployerName><xsl:value-of select="employername"/></EmployerName>
272 '||'<JobPositionLocation>
273 '||'<PostalAddress>
274 '||'<CountryCode><xsl:value-of select="employer_country"/></CountryCode>
275 '||'<DeliveryAddress>
276 '||'<AddressLine><xsl:value-of select="employeraddress"/></AddressLine>
277 '||'</DeliveryAddress>
278 '||'</PostalAddress>
279 '||'</JobPositionLocation>
280 '||'<PositionTitle><xsl:value-of select="job_name"/></PositionTitle>
281 '||'<Industry><NAICS><xsl:value-of select="employer_type"/></NAICS></Industry>
282 '||'<EffectiveDate>
283 '||'<StartDate>
284 '||'<Date><xsl:value-of select="attendedstartdate"/></Date>
285 '||'</StartDate>
286 '||'<EndDate>
287 '||'<Date><xsl:value-of select="attendedenddate"/></Date>
288 '||'</EndDate>
289 '||'</EffectiveDate>
290 '||'<SummaryText><xsl:value-of select="descriptionText"/></SummaryText>
291 '||'</Position>
292 '||'</xsl:for-each>'||'</EmploymentHistory>
293 '||'</xsl:if>
294 '||'<!-- Only add a SchoolOrInstitution element if the person has qualifications
295 '||' gained in a named SchoolOrInstitution.-->
296 '||'<xsl:if test="count(education/education_row/attendance_id)!=0"> '||'<EducationQualifs><xsl:for-each select="education/education_row">
297 '||'<SchoolOrInstitution>
298 '||'<SchoolName><xsl:value-of select="establishment"/></SchoolName>
299 '||'<LocationSummary>
300 '||'<Region><xsl:value-of select="address"/></Region>
301 '||'</LocationSummary>
302 '||'<EduDegree degreeType="{type}"><xsl:value-of select="title"/></EduDegree>
303 '||'<GPA><xsl:value-of select="grade_attained"/></GPA>
304 '||'<EffectiveDate>
305 '||'<StartDate>
306 '||'<Date><xsl:value-of select="attendedstartdate"/></Date>
307 '||'</StartDate>
308 '||'<EndDate>
309 '||'<Date><xsl:value-of select="attendedenddate"/></Date>
310 '||'</EndDate>
311 '||'</EffectiveDate>
312 '||'</SchoolOrInstitution>
313 '||'</xsl:for-each> '||'</EducationQualifs></xsl:if>
314 '||'<!-- Only display the skills if there is any to display
315 '||' The check ensures that one mandatory column is present -->
316 '||'<xsl:if test="count(skills/skills_row/name)!=0">
317 '||'<SkillQualifs><xsl:for-each select="skills/skills_row">
318 '||'<Skill level= "{lvl}">
319 '||'<SkillName><xsl:value-of select="name"/></SkillName>
320 '||'</Skill></xsl:for-each>
321 '||'</SkillQualifs>
322 '||'</xsl:if>
323 '||'</StructuredResume>
324 '||'</xsl:if>'||'<TextOrNonXMLResume>
325 '||'<ResumeLink>
326 '||'<Link><xsl:value-of select="textornonxmlresume/textornonxmlresume_row/sit_job"/>
327 '||'</Link>
328 '||'</ResumeLink>
329 '||'</TextOrNonXMLResume>
330 '||'</Resume>
331 </JobPositionSeeker></xsl:for-each>
332 </xsl:template>
333 </xsl:stylesheet>';
334 hr_utility.set_location('Created the stylesheet',13);
335
336 else
337 hr_utility.set_location('Stylesheet is not null',14);
338
339 begin
340 hr_utility.set_location('Getting stylesheet from URL',15);
341
342 l_stylesheet_url:=rtrim(fnd_profile.value('APPS_FRAMEWORK_AGENT'),'/')||'/OA_HTML/'||userenv('LANG')||'/'||p_stylesheet;
343 tv_sheet:= irc_xml_util.http_get_pieces(l_stylesheet_url,100);
344 if instr(lower(tv_sheet(1)),'<?xml')<>1 then
345 l_stylesheet_url:=rtrim(fnd_profile.value('APPS_FRAMEWORK_AGENT'),'/')||'/OA_HTML/'||p_stylesheet;
346 tv_sheet:= irc_xml_util.http_get_pieces(l_stylesheet_url,100);
347 end if;
348 hr_utility.set_location('Got the stylesheet from the URL',16);
349
350 exception when others then
351 hr_utility.set_location('Exception occured while getting stylesheet',17);
352 hr_utility.set_location('Exception: '||substrb(sqlerrm,1,160),18);
353 hr_utility.set_location('Exception: '||sqlcode,19);
354 l_stylesheet_url:=rtrim(fnd_profile.value('APPS_FRAMEWORK_AGENT'),'/')||'/OA_HTML/'||p_stylesheet;
355 tv_sheet:= irc_xml_util.http_get_pieces(l_stylesheet_url,100);
356 end;
357 lv_stylesheet:='';
358 for l_index in 1..tv_sheet.count loop
359 lv_stylesheet:=lv_stylesheet||tv_sheet(l_index);
360 end loop;
361 hr_utility.set_location('Created the Stylesheet',20);
362 end if;
363 hr_utility.set_location('Executing the query',21);
364 ctx:= dbms_xmlquery.newContext(l_query);
365 dbms_xmlquery.setBindValue(ctx,'1',p_person_id);
366 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
367 dbms_xmlquery.setRowsetTag(ctx,'jobpositionseeker');
368 clobdoc:=dbms_xmlquery.getXML(ctx);
369 hr_utility.set_location('Retrieved the XML data',22);
370 dbms_xmlquery.closeContext(ctx);
371 dbms_lob.createTemporary(l_formatted_doc,false,dbms_lob.call);
372 hr_utility.set_location('Entering Parsing section',23);
373 parser:=xmlparser.newparser;
374
375 hr_utility.set_location('Setting encoding',231);
376 if(fnd_profile.value('ICX_CLIENT_IANA_ENCODING') <> '') then
377 clobdoc := replace(clobdoc, '?>', ' encoding = '''||fnd_profile.value('ICX_CLIENT_IANA_ENCODING')||'''?>');
378 end if;
379 hr_utility.set_location('Exiting after setting encoding',233);
380
381 xmlparser.parseCLOB(parser,clobdoc);
382 xmldoc:=xmlparser.getDocument(parser);
383 engine:=xslprocessor.newProcessor;
384 xmlparser.parseBuffer(parser,lv_stylesheet);
385 stylesheetdoc:=xmlparser.getDocument(parser);
386 stylesheet:=xslprocessor.newStylesheet(stylesheetdoc,null);
387 hr_utility.set_location('Parsing the stylesheet',24);
388 xslprocessor.processXSL(engine,stylesheet,xmldoc,l_formatted_doc);
389 hr_utility.set_location('Parsing Sucess. Freeing parser',25);
390 xslprocessor.freeStylesheet(stylesheet);
391 xmldom.freeDocument(stylesheetdoc);
392 xmlParser.freeParser(parser);
393 xslprocessor.freeProcessor(engine);
394 xmldom.freeDocument(xmldoc);
395 p_raw_doc:=clobdoc;
396
397 if(p_stylesheet is null and substr(l_formatted_doc,0,5)<> '<?xml') then
398 l_formatted_doc :='<?xml version = ''1.0'' encoding = ''UTF-8''?>
399 <!DOCTYPE JobPositionSeeker SYSTEM "JobPositionSeeker-1_1.dtd">' || l_formatted_doc;
400 end if;
401
402 p_formatted_doc:=l_formatted_doc;
403 -- p_formatted_doc:=clobdoc;
404 hr_utility.set_location('Generation successful',26);
405 exception when others then
406 hr_utility.set_location('Exception occured',27);
407 hr_utility.set_location('Exception: '||substrb(sqlerrm,1,160),28);
408 hr_utility.set_location('Exception: '||sqlcode,29);
409 xmlParser.freeParser(parser);
410 xslprocessor.freeProcessor(engine);
411 xslprocessor.freeStylesheet(stylesheet);
412 xmldom.freeDocument(xmldoc);
413 xmldom.freeDocument(stylesheetdoc);
414 raise;
415 end generateJPSint;
416 --
417 function generateJPS(p_person_id in number
418 ,p_stylesheet varchar2 default null) return CLOB is
419 l_dummy_doc CLOB;
420 l_formatted_doc CLOB;
421 l_stylesheet varchar2(240);
422 begin
423 hr_utility.set_location('Entering generateJPS',10);
424 if (p_stylesheet is not null) then
425 hr_utility.set_location('Stylesheet not null',11);
426 l_stylesheet:=p_stylesheet||'.xsl';
427 hr_utility.set_location('Calling generateJPSint with stylesheet',12);
428 generateJPSint(p_person_id=>p_person_id
429 ,p_stylesheet=>l_stylesheet
430 ,p_raw_doc=>l_dummy_doc
431 ,p_formatted_doc=>l_formatted_doc);
432 else
433 hr_utility.set_location('Calling generateJPSint without stylesheet',12);
434 generateJPSint(p_person_id=>p_person_id
435 ,p_raw_doc=>l_dummy_doc
436 ,p_formatted_doc=>l_formatted_doc);
437 end if;
438 return l_formatted_doc;
439 end generateJPS;
440 --
441 procedure show_resume(p number,s varchar2) is
442 --
443 l_result CLOB;
444 begin
445 hr_utility.set_location('Entering show_resume',10);
446 l_result:=generateJPS(p,s);
447 htp.p(dbms_lob.substr(l_result));
448 exception
449 when others then
450 hr_utility.set_location('Exception occured',20);
451 hr_utility.set_location('Exception: '||substrb(sqlerrm,1,160),30);
452 hr_utility.set_location('Exception: '||sqlcode,40);
453 htp.p(dbms_utility.format_error_stack);
454 end show_resume;
455 --
456 procedure save_candidate_resume(p_person_id in number
457 ,p_stylesheet in varchar2
458 ,p_assignment_id in number default null
459 ,p_overwrite boolean default true) is
460 --
461 cursor get_first_resume is
462 select document_id
463 from irc_documents
464 where person_id=p_person_id
465 and type='AUTO_RESUME'
466 and nvl(assignment_id,-1)=nvl(p_assignment_id,-1)
467 order by creation_date asc;
468 --
469 cursor get_doc_name is
470 select substr(replace(initcap(full_name),' '),1,120)
471 from per_all_people_f
472 where person_id=p_person_id
473 and sysdate between effective_start_date and effective_end_date;
474 l_binary_doc blob;
475 l_character_doc clob;
476 l_parsed_xml clob;
477 l_document_id number;
478 l_overwriting boolean :=false;
479 l_ovn number;
480 text_data varchar2(32767);
481 l_amount number;
482 l_position number;
483 l_block_size number:=10000;
484 l_file_name varchar(125);
485 l_description varchar2(240);
486 l_stylesheet varchar2(240);
487 --
488 cursor get_meaning(p_type varchar2) is
489 select meaning
490 from hr_lookups
491 where lookup_type=p_type
492 and lookup_code=p_stylesheet;
493 l_meaning varchar2(240);
494 l_proc varchar2(72) := 'irc_JPS_generator.save_candidate_resume';
495 l_error_added boolean;
496 Begin
497 --
498 hr_multi_message.enable_message_list;
499 --
500 hr_utility.set_location('Entering save_candidate_resume',10);
501 open get_doc_name;
502 fetch get_doc_name into l_file_name;
503 close get_doc_name;
504 if l_file_name is null then
505 l_file_name:=to_char(sysdate,'YYYY-MM-DD');
506 end if;
507 l_file_name:=l_file_name||'.htm';
508 --
509 open get_meaning('IRC_RESUME_STYLE');
510 fetch get_meaning into l_meaning;
511 close get_meaning;
512 fnd_message.set_name('PER','IRC_SAVE_RES_DESC');
513 fnd_message.set_token('STYLE',l_meaning);
514 l_description:=substrb(fnd_message.get,1,240);
515 fnd_message.clear;
516 --
517 hr_utility.set_location('Getting stylesheet',11);
518 l_stylesheet:=p_stylesheet||'.xsl';
519 generateJPSint(p_person_id=>p_person_id
520 ,p_stylesheet=>l_stylesheet
521 ,p_raw_doc =>l_parsed_xml
522 ,p_formatted_doc=>l_character_doc);
523 dbms_lob.createTemporary(l_binary_doc,false,dbms_lob.call);
524 l_position:=1;
525 loop
526 l_amount:=l_block_size;
527 dbms_lob.read(l_character_doc,l_amount,l_position,text_data);
528 dbms_lob.write(l_binary_doc,l_amount,l_position,utl_raw.cast_to_raw(text_data));
529 if(l_amount<l_block_size) then
530 exit;
531 end if;
532 l_position:=l_position+l_amount;
533 end loop;
534
535 if (p_overwrite) then
536 open get_first_resume;
537 fetch get_first_resume into l_document_id;
538 if get_first_resume%found then
539 l_overwriting:=true;
540 update irc_documents
541 set binary_doc=l_binary_doc
542 ,character_doc=empty_clob()
543 ,parsed_xml=l_parsed_xml
544 ,mime_type='text/html'
545 ,file_format='TEXT'
546 ,file_name=l_file_name
547 ,description=l_description
548 where document_id=l_document_id;
549 end if;
550 close get_first_resume;
551 end if;
552 --
553 if not l_overwriting then
554 irc_document_api.create_document
555 (p_validate => false
556 ,p_effective_date=>sysdate
557 ,p_type=>'AUTO_RESUME'
558 ,p_person_id=>p_person_id
559 ,p_assignment_id=>p_assignment_id
560 ,p_mime_type=>'text/html'
561 ,p_file_name=>l_file_name
562 ,p_description=>l_description
563 ,p_document_id=>l_document_id
564 ,p_object_version_number=>l_ovn);
565 --
566 update irc_documents
567 set binary_doc=l_binary_doc
568 ,parsed_xml=l_parsed_xml
569 where document_id=l_document_id;
570 end if;
571 irc_document_api.process_document(l_document_id);
572 --
573 hr_utility.set_location('Saved resume',12);
574 exception
575 when others then
576 hr_utility.set_location('Exception occured',20);
577 hr_utility.set_location('Exception: '||substrb(sqlerrm,1,160),30);
578 hr_utility.set_location('Exception: '||sqlcode,40);
579 l_error_added := hr_multi_message.unexpected_error_add(l_proc);
580 raise;
581 end save_candidate_resume;
582 end;