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