[Home] [Help]
PACKAGE BODY: APPS.IRC_JPP_GENERATOR
Source
1 package body irc_JPP_generator as
2 /* $Header: irjppgen.pkb 120.15.12010000.2 2009/02/05 07:26:08 sethanga ship $ */
3
4 procedure generateJPPint(p_recruitment_activity_id in number
5 ,p_sender_id in number
6 ,p_stylesheet varchar2 default null
7 ,p_jpp_doc out nocopy clob) is
8 l_query varchar2(32767);
9 l_jpp_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 l_query:= 'select hr_xml_packet_id_s.nextval packetId'
23 ||' , ''CREATE'' action'
24 ||' , hr_xml_transaction_id_s.nextval transactionid'
25 ||' , translate(to_char(sysdate,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') timestamp'
26 ||' , rse.posting_url vendorURL'
27 ||' , decode(rse.posting_username '
28 ||' ,''#USERID'',to_char(sndr_usr.user_id) '
29 ||' ,''#USERNAME'',sndr_usr.user_name '
30 ||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
31 ||' , rse.posting_username) posting_username'
32 ||' , decode(fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id )'
33 ||' ,''#USERID'',to_char(sndr_usr.user_id) '
34 ||' ,''#USERNAME'',sndr_usr.user_name '
35 ||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
36 ||' , fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id) )posting_password'
37 ||' , rse.stylesheet '
38 ||' , sndr_per.last_name personlastname'
39 ||' , sndr_per.first_name personfirstname'
40 ||' , sndr_per.email_address emailaddress'
41 ||' , sndr_usr.user_id userid'
42 ||' , recr_pp.first_name recr_first_name'
43 ||' , recr_pp.last_name recr_last_name'
44 ||' , recr_pp.full_name recr_full_name'
45 ||' , recr_pp.email_address recr_email_address'
46 ||' , recr_phn.phone_number recr_phone_number'
47 ||' , recr_phn_fax.phone_number recr_fax_number'
48 ||' , recr_pp.person_id recr_person_id'
49 ||' , vac.number_of_openings'
50 ||' , vac.name vacancy_name'
51 ||' , vac.budget_measurement_type'
52 ||' , vac.budget_measurement_value'
53 ||' , to_char(vac.date_from,''RRRR-MM-DD'') vacancy_start_date'
54 ||' , to_char(vac.date_to,''RRRR-MM-DD'') vacancy_end_date'
55 ||' , rtrim(nvl(fnd_profile.value(''IRC_FRAMEWORK_AGENT'')'
56 ||' ,fnd_profile.value(''APPS_FRAMEWORK_AGENT''))||'
57 ||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
58 ||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
59 ||' ''&p_svid=''||to_char(vac.vacancy_id)||'
60 ||' ''&p_spid=''||to_char(ipc.posting_content_id)||';
61 if(HR_MULTI_TENANCY_PKG.is_multi_tenant_system()) then
62 l_query:=l_query ||' ''&OAMC=''||to_char(''R'')||' ;
63 end if;
64 l_query := l_query
65 ||' ''&p_site_id=''||to_char(rse.recruiting_site_id) application_url'
66 ||' , rtrim(fnd_profile.value(''APPS_FRAMEWORK_AGENT'')||'
67 ||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
68 ||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
69 ||' ''&p_svid=''||to_char(vac.vacancy_id)||'
70 ||' ''&p_spid=''||to_char(ipc.posting_content_id)||'
71 ||' ''&p_site_id=''||to_char(rse.recruiting_site_id) int_application_url'
72 ||' , irc_isc.object_id'
73 ||' , hr_general.decode_lookup(''IRC_PROFESSIONAL_AREA'',irc_isc.professional_area) professional_area'
74 ||' , irc_isc.employee'
75 ||' , irc_isc.contractor'
76 ||' , irc_isc.employment_category'
77 ||' , irc_isc.min_salary'
78 ||' , irc_isc.max_salary'
79 ||' , nvl(irc_isc.max_salary,irc_isc.min_salary) salary'
80 ||' , irc_isc.salary_currency'
81 ||' , irc_isc.salary_period'
82 ||' , irc_isc.travel_percentage'
83 ||' , decode(irc_isc.employment_category,''EITHER'',''Y'',''FULLTIME'',''Y'',''PARTTIME'',''N'',''Y'') full_time'
84 ||' , decode(irc_isc.employment_category,''EITHER'',''Y'',''PARTTIME'',''Y'',''FULLTIME'',''N'',''Y'') part_time'
85 ||' , ipc.display_recruiter_info'
86 ||' , ipctl.posting_content_id'
87 ||' , ipctl.name'
88 ||' , ipctl.org_name'
89 ||' , replace(replace(ipctl.org_description,''&'',''&''||''amp;''),''<'',''&''||''lt;'') org_description'
90 ||' , nvl(ipctl.job_title,ipctl.name) job_title'
91 ||' , replace(replace(ipctl.brief_description,''&'',''&''||''amp;''),''<'',''&''||''lt;'') brief_description'
92 ||' , replace(replace(ipctl.detailed_description,''&'',''&''||''amp;''),''<'',''&''||''lt;'') detailed_description'
93 ||' , replace(replace(ipctl.job_requirements,''&'',''&''||''amp;''),''<'',''&''||''lt;'') job_requirements'
94 ||' , replace(replace(ipctl.additional_details,''&'',''&''||''amp;''),''<'',''&''||''lt;'') additional_details'
95 ||' , replace(replace(ipctl.how_to_apply,''&'',''&''||''amp;''),''<'',''&''||''lt;'') how_to_apply'
96 ||' , replace(replace(ipctl.benefit_info,''&'',''&''||''amp;''),''<'',''&''||''lt;'') benefit_info'
97 ||' , rec.recruitment_activity_id'
98 ||' , to_char(rec.date_start,''RRRR-MM-DD'') posting_start_date'
99 ||' , to_char(rec.date_end,''RRRR-MM-DD'') posting_end_date'
100 ||' , loc.address_line_1'
101 ||' , loc.address_line_2'
102 ||' , loc.address_line_3'
103 ||' , loc.town_or_city'
104 ||' , loc.country'
105 ||' , loc.postal_code'
106 ||' , loc.region_1'
107 ||' , loc.region_2'
108 ||' , loc.region_3'
109 -- cursor to get the details of the competences/skills for the position
110 ||' , cursor (select pc.name '
111 ||' , pce.mandatory '
112 ||' , pc.competence_id '
113 ||' , pce.competence_element_id '
114 ||' , prl1.step_value min_level_id '
115 ||' , prl2.step_value max_level_id '
116 ||' , prl1.name min_level '
117 ||' , prl2.name max_level'
118 ||' from per_competences_tl pc'
119 ||' , per_competence_elements pce'
120 ||' , per_rating_levels prl1'
121 ||' , per_rating_levels prl2'
122 ||' where pc.language=ipctl.source_language'
123 ||' and pc.competence_id = pce.competence_id '
124 ||' and pce.type = ''REQUIREMENT'''
125 ||' and pce.object_name = ''VACANCY'''
126 ||' and pce.object_id = vac.vacancy_id'
127 ||' and prl1.rating_level_id(+) = pce.proficiency_level_id'
128 ||' and prl2.rating_level_id(+) = pce.high_proficiency_level_id'
129 ||' and vac.date_from '
130 ||' between nvl(pce.effective_date_from, vac.date_from)'
131 ||' and nvl(pce.effective_date_to, vac.date_from)'
132 ||' ORDER BY pce.mandatory, pc.name DESC'
133 ||' ) competences'
134 -- cursor to get the variable comp element
135 ||' , cursor (select hr_general.decode_lookup(''IRC_VARIABLE_COMP_ELEMENT'' ,vce.variable_comp_lookup) var_comp'
136 ||' from irc_variable_comp_elements vce'
137 ||' where vac.vacancy_id = vce.vacancy_id'
138 ||' ) comp_elements'
139 ||' from irc_posting_contents_tl ipctl'
140 ||' , irc_posting_contents ipc'
141 ||' , per_recruitment_activities rec'
142 ||' , per_recruitment_activity_for recf'
143 ||' , per_all_vacancies vac'
144 ||' , hr_locations_all_vl loc'
145 ||' , irc_search_criteria irc_isc'
146 ||' , irc_all_recruiting_sites rse'
147 ||' , fnd_user sndr_usr'
148 ||' , per_all_people_f sndr_per'
149 ||' , per_all_people_f recr_pp'
150 ||' , per_phones recr_phn'
151 ||' , per_phones recr_phn_fax'
152 ||' where rec.recruitment_activity_id=:1'
153 ||' and recf.vacancy_id = vac.vacancy_id'
154 ||' and not exists (select 1 from per_recruitment_activity_for recf2'
155 ||' where recf2.recruitment_activity_id =rec.recruitment_activity_id'
156 ||' and recf2.recruitment_activity_for_id>recf.recruitment_activity_for_id)'
157 ||' and rec.recruitment_activity_id = recf.recruitment_activity_id'
158 ||' and ipc.posting_content_id = rec.posting_content_id'
159 ||' and ipctl.posting_content_id = ipc.posting_content_id '
160 ||' and ipctl.source_language = userenv(''LANG'')'
161 ||' and rse.recruiting_site_id = rec.recruiting_site_id'
162 ||' and sndr_usr.user_id = :2'
163 ||' and sndr_usr.employee_id=sndr_per.person_id'
164 ||' and trunc(sysdate)'
165 ||' between sndr_per.effective_start_date and '
166 ||' sndr_per.effective_end_date'
167 ||' and vac.recruiter_id = recr_pp.person_id(+) '
168 ||' and vac.recruiter_id = recr_phn.parent_id(+) '
169 ||' and recr_phn.parent_table(+) = ''PER_ALL_PEOPLE_F'''
170 ||' and recr_phn.phone_type(+) = ''W1'''
171 ||' and vac.recruiter_id = recr_phn_fax.parent_id(+) '
172 ||' and recr_phn_fax.parent_table(+) = ''PER_ALL_PEOPLE_F'''
173 ||' and recr_phn_fax.phone_type(+) = ''WF'''
174 ||' and trunc(sysdate)'
175 ||' between nvl(recr_pp.effective_start_date,trunc(sysdate)) '
176 ||' and nvl(recr_pp.effective_end_date,trunc(sysdate))'
177 ||' and trunc(sysdate)'
178 ||' between nvl(recr_phn.date_from,trunc(sysdate)) '
179 ||' and nvl(recr_phn.date_to, trunc(sysdate)) '
180 ||' and trunc(sysdate)'
181 ||' between nvl(recr_phn_fax.date_from,trunc(sysdate)) '
182 ||' and nvl(recr_phn_fax.date_to, trunc(sysdate)) '
183 ||' and vac.location_id = loc.location_id (+)'
184 ||' and vac.vacancy_id = irc_isc.object_id (+)'
185 ||' and irc_isc.object_type(+) = ''VACANCY'''
186 ||' and rownum=1';
187
188 if (p_stylesheet is null) then
189
190 lv_stylesheet:='<?xml version="1.0" ?>'||
191 '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">'||
192 '<xsl:output method="xml" indent="yes" doctype-system="Envelope-v01-00.dtd"/>'||
193 '<xsl:template match="JobPositionPosting">'||
194 '<xsl:for-each select="row">&'||'lt;?xml version = "1.0"?>
195 ';
196 if(p_stylesheet is null) then
197 lv_stylesheet:=lv_stylesheet||'&'||'lt;!DOCTYPE Envelope SYSTEM "ProvisionalEnvelope-v1.0.dtd">
198 ';
199 end if;
200 lv_stylesheet:=lv_stylesheet||
201 '&'||'lt;Envelope version = "01.00">'||
202 '&'||'lt;Sender>'||
203 '&'||'lt;Id><xsl:value-of select="posting_username "/>&'||'lt;/Id>'||
204 '&'||'lt;Credential><xsl:value-of select="posting_password "/>&'||'lt;/Credential>'||
205 '&'||'lt;/Sender>'||
206 '&'||'lt;Recipient>'||
207 '&'||'lt;Id><xsl:value-of select="vendorurl"/>&'||'lt;/Id>'||
208 '&'||'lt;/Recipient>'||
209 '&'||'lt;TransactInfo transactType = "request">'||
210 '&'||'lt;TransactId><xsl:value-of select="transactionid"/>&'||'lt;/TransactId>'||
211 '&'||'lt;TimeStamp><xsl:value-of select="timestamp"/>&'||'lt;/TimeStamp>'||
212 '&'||'lt;/TransactInfo>'||
213 '&'||'lt;Packet>'||
214 '&'||'lt;PacketInfo packetType = "request">'||
215 '&'||'lt;PacketId><xsl:value-of select="packetid"/>&'||'lt;/PacketId>'||
216 '&'||'lt;Action><xsl:value-of select="action"/>&'||'lt;/Action>'||
217 '&'||'lt;Manifest>JobPositionSeeker-1_1.dtd&'||'lt;/Manifest>'||
218 '&'||'lt;/PacketInfo>'||
219 --
220 -- This is the start of the ProvisionalEnvelope Payload Element.
221 -- This element contains the posting details in JobPositionPosting-1_1.dtd
222 -- complient XML
223 --
224 '&'||'lt;Payload>&'||'lt;![CDATA[&'||'lt;?xml version = "1.0"?>'||
225 '&'||'lt;!DOCTYPE JobPositionPosting SYSTEM "JobPositionPosting-1_1.dtd">'||
226 '&'||'lt;JobPositionPosting>'||
227 '&'||'lt;JobPositionPostingId idOwner="oracle.com"><xsl:value-of select="recruitment_activity_id"/>&'||'lt;/JobPositionPostingId>'||
228 '&'||'lt;HiringOrg>'||
229 '&'||'lt;HiringOrgName><xsl:value-of select="org_name"/>&'||'lt;/HiringOrgName>'||
230 '&'||'lt;Industry>'||
231 '&'||'lt;SummaryText><xsl:value-of select="professional_area"/>&'||'lt;/SummaryText>'||
232 '&'||'lt;/Industry>'||
233 '<xsl:if test="display_recruiter_info=''Y'' and count(recr_person_id)!=0"> '||
234 '&'||'lt;Contact>'||
235 '&'||'lt;PersonName>'||
236 '&'||'lt;FormattedName><xsl:value-of select="recr_full_name"/>&'||'lt;/FormattedName>'||
237 '&'||'lt;GivenName><xsl:value-of select="recr_first_name"/>&'||'lt;/GivenName>'||
238 '&'||'lt;FamilyName><xsl:value-of select="recr_last_name"/>&'||'lt;/FamilyName>'||
239 '&'||'lt;/PersonName>'||
240 '&'||'lt;VoiceNumber>'||
241 '&'||'lt;TelNumber><xsl:value-of select="recr_phone_number"/>&'||'lt;/TelNumber>'||
242 '&'||'lt;/VoiceNumber>'||
243 '&'||'lt;FaxNumber>'||
244 '&'||'lt;TelNumber><xsl:value-of select="recr_fax_number"/>&'||'lt;/TelNumber>'||
245 '&'||'lt;/FaxNumber>'||
246 '&'||'lt;E-mail><xsl:value-of select="recr_email_address"/>&'||'lt;/E-mail>'||
247 '&'||'lt;/Contact>'||
248 '</xsl:if>'||
249 '&'||'lt;OrganizationalUnit>'||
250 '&'||'lt;Description><xsl:value-of select="org_description"/>&'||'lt;/Description>'||
251 '&'||'lt;/OrganizationalUnit>'||
252 '&'||'lt;/HiringOrg>'||
253 '&'||'lt;PostDetail>'||
254 '&'||'lt;StartDate>'||
255 '&'||'lt;Date><xsl:value-of select="posting_start_date"/>&'||'lt;/Date>'||
256 '&'||'lt;/StartDate>'||
257 '&'||'lt;EndDate>'||
258 '&'||'lt;Date><xsl:value-of select="posting_end_date"/>&'||'lt;/Date>'||
259 '&'||'lt;/EndDate>'||
260 '&'||'lt;PostedBy>'||
261 '&'||'lt;Contact>'||
262 '&'||'lt;PersonName>'||
263 '&'||'lt;GivenName><xsl:value-of select="personfirstname"/>&'||'lt;/GivenName>'||
264 '&'||'lt;FamilyName><xsl:value-of select="personlastname"/>&'||'lt;/FamilyName>'||
265 '&'||'lt;/PersonName>'||
266 '&'||'lt;E-mail><xsl:value-of select="emailaddress"/>&'||'lt;/E-mail>'||
267 '&'||'lt;/Contact>'||
268 '&'||'lt;/PostedBy>'||
269 '&'||'lt;/PostDetail>'||
270 '&'||'lt;JobPositionInformation>'||
271 '&'||'lt;JobPositionTitle><xsl:value-of select="job_title"/>&'||'lt;/JobPositionTitle>'||
272 '&'||'lt;JobPositionDescription>'||
273 '&'||'lt;JobPositionPurpose><xsl:value-of select="detailed_description"/>&'||'lt;/JobPositionPurpose>'||
274 '&'||'lt;JobPositionLocation>'||
275 '&'||'lt;PostalAddress>'||
276 '&'||'lt;CountryCode><xsl:value-of select="country"/>&'||'lt;/CountryCode>'||
277 '&'||'lt;PostalCode><xsl:value-of select="postal_code"/> &'||'lt;/PostalCode>'||
278 '&'||'lt;Region><xsl:value-of select="region_1"/>&'||'lt;/Region>'||
279 '&'||'lt;Region><xsl:value-of select="region_2"/>&'||'lt;/Region>'||
280 '&'||'lt;Region><xsl:value-of select="region_3"/>&'||'lt;/Region>'||
281 '&'||'lt;Municipality><xsl:value-of select="town_or_city"/>&'||'lt;/Municipality>'||
282 '&'||'lt;DeliveryAddress>'||
283 '&'||'lt;AddressLine><xsl:value-of select="address_line_1"/>&'||'lt;/AddressLine>'||
284 '&'||'lt;AddressLine><xsl:value-of select="address_line_2"/>&'||'lt;/AddressLine>'||
285 '&'||'lt;AddressLine><xsl:value-of select="address_line_3"/>&'||'lt;/AddressLine>'||
286 '&'||'lt;/DeliveryAddress>'||
287 '&'||'lt;/PostalAddress>'||
288 '&'||'lt;/JobPositionLocation>'||
289 '&'||'lt;Classification>'||
290 --
291 -- The element DirectHireOrContract only allows one of the following child elements
292 --
293 -- DirectHire Contract Temp TempToPerm
294 --
295 -- Oracle only use DirectHire or Contract in this element.
296 --
297 -- The DTD specifies that only one can be set, so if both are, we use DirectHire.
298 -- The child element has to be null.
299 --
300 '<xsl:choose> <xsl:when test="employee=''Y'' or contractor=''Y''">'||
301 '&'||'lt;DirectHireOrContract><xsl:choose>'||
302 '<xsl:when test="employee=''Y''"> '||
303 '&'||'lt;DirectHire>&'||'lt;/DirectHire> </xsl:when><xsl:otherwise>'||
304 '&'||'lt;Contract>&'||'lt;/Contract> </xsl:otherwise></xsl:choose>'||
305 '&'||'lt;/DirectHireOrContract>'||
306 '</xsl:when>'||
310 --
307 '</xsl:choose>'||
308 --
309 -- The Schedule Element has FullTime and PartTime child elments.
311 -- Only one can be set - so we are default to FullTime if both are set.
312 -- The JobPositionPosting-1_1 DTD has an error whereby both FullTime and PartTime
313 -- have a child element called SummaryText. Element FullTime has this as mandatory
314 -- whereas element PartTime has this as optional.
315 --
316 -- For consistancy, both will assume it is mandatory.
317 --
318 '<xsl:choose> <xsl:when test="full_time=''Y'' or part_time=''Y''">'||
319 '&'||'lt;Schedule>'||
320 '<xsl:choose>'||
321 '<xsl:when test="full_time=''Y''"> '||
322 '&'||'lt;FullTime>'||
323 '&'||'lt;SummaryText><xsl:value-of select="full_time"/>&'||'lt;/SummaryText>'||
324 '&'||'lt;/FullTime> </xsl:when><xsl:otherwise>'||
325 '&'||'lt;PartTime>'||
326 '&'||'lt;SummaryText><xsl:value-of select="part_time"/>&'||'lt;/SummaryText>'||
327 '&'||'lt;/PartTime> </xsl:otherwise></xsl:choose>'||
328 '&'||'lt;/Schedule>'||
329 '</xsl:when> </xsl:choose>'||
330 '&'||'lt;/Classification>'||
331 '<xsl:if test="count(salary_currency)!=0 or string-length(benefit_info)!=0 or count(comp_elements/comp_elements_row)!=0">'||
332 '&'||'lt;CompensationDescription>'||
333 --
334 -- Element Pay and associated children will only be displayed if the
335 -- salary_currency is available.
336 --
337 -- Currently, our UI only deals with Annual values, however this xsl
338 -- will handle both monthly and hourly values as well. It will default
339 -- to ANNUAL. (This does mean it will have to change should the PAY_BASIS
340 -- lookup code PERIOD ever be used.
341 --
342 '<xsl:if test="count(salary_currency)!=0"> &'||'lt;Pay><xsl:choose>'||
343 '<!-- Default to ANNUAL as we only really use this -->'||
344 '<xsl:when test="salary_period=''MONTHLY''"> '||
345 '&'||'lt;SalaryMonthly currency="<xsl:value-of select="salary_currency"/>">'||
346 '<xsl:value-of select="salary"/>&'||'lt;/SalaryMonthly>'||
347 '</xsl:when>'||
348 '<xsl:when test="salary_period=''HOURLY''"> '||
349 '&'||'lt;RatePerHour currency="<xsl:value-of select="salary_currency"/>">'||
350 '<xsl:value-of select="salary"/>&'||'lt;/RatePerHour> </xsl:when> <xsl:otherwise>'||
351 '&'||'lt;SalaryAnnual currency="<xsl:value-of select="salary_currency"/>">'||
352 '<xsl:value-of select="salary"/>&'||'lt;/SalaryAnnual> </xsl:otherwise></xsl:choose>'||
353 '&'||'lt;/Pay> </xsl:if> '||
354 --
355 -- Do not generate the BenefitsDescription element and children if there is no
356 -- information to display
357 --
358 '<xsl:if test="string-length(benefit_info)!=0 or count(comp_elements/comp_elements_row)!=0">'||
359 '&'||'lt;BenefitsDescription>'||
360 '&'||'lt;P><xsl:value-of select="benefit_info"/>&'||'lt;/P>'||
361 '&'||'lt;UL>'||
362 '<xsl:for-each select="comp_elements/comp_elements_row">'||
363 '&'||'lt;LI><xsl:value-of select="var_comp"/>&'||'lt;/LI></xsl:for-each>'||
364 '&'||'lt;/UL>'||
365 '&'||'lt;/BenefitsDescription></xsl:if> '||
366 '&'||'lt;/CompensationDescription></xsl:if>'||
367 --
368 -- Do not generate the SummaryText element and children if there is no
369 -- information to display
370 --
371 '<xsl:if test="count(brief_description)!=0 or count(additional_details)!=0">'||
372 '&'||'lt;SummaryText>'||
373 '<xsl:if test="count(brief_description)!=0"> <xsl:value-of select="brief_description"/></xsl:if>'||
374 '<xsl:if test="count(additional_details)!=0"> <xsl:value-of select="additional_details"/></xsl:if>'||
375 '&'||'lt;/SummaryText>'||
376 '</xsl:if>'||
377 '&'||'lt;/JobPositionDescription>'||
378 '&'||'lt;JobPositionRequirements>'||
379 --
380 -- The competences can either be mandatory or preferred.
381 --
382 '<xsl:if test="count(competences/competences_row[mandatory=''Y''])!=0">'||
383 '&'||'lt;QualificationsRequired>&'||'lt;UL>'||
384 '<xsl:for-each select="competences/competences_row[mandatory=''Y'']">'||
385 '&'||'lt;LI>&'||'lt;Qualification><xsl:value-of select="name"/>&'||'lt;/Qualification>&'||'lt;/LI></xsl:for-each>'||
386 '&'||'lt;/UL>&'||'lt;/QualificationsRequired> </xsl:if>'||
387 --
388 -- Using !='Y' as opposed to ='N' in the next test to catch any nulls
389 --
390 '<xsl:if test="count(competences/competences_row[mandatory!=''Y''])!=0"> '||
391 '&'||'lt;QualificationsPreferred>&'||'lt;UL>'||
392 '<xsl:for-each select="competences/competences_row[mandatory!=''Y'']">'||
393 '&'||'lt;LI>&'||'lt;Qualification><xsl:value-of select="name"/>&'||'lt;/Qualification>&'||'lt;/LI></xsl:for-each>'||
394 '&'||'lt;/UL>&'||'lt;/QualificationsPreferred> </xsl:if>'||
395 '&'||'lt;TravelRequired>'||
396 '&'||'lt;PercentageOfTime><xsl:value-of select="travel_percentage"/>'||
397 '&'||'lt;/PercentageOfTime>'||
398 '&'||'lt;/TravelRequired>'||
399 '&'||'lt;SummaryText><xsl:value-of select="job_requirements"/>&'||'lt;/SummaryText>'||
400 '&'||'lt;/JobPositionRequirements>'||
401 '&'||'lt;/JobPositionInformation>'||
402 --
403 -- HowToApply element is mandatory so no need to check if data is available.
404 --
405 '&'||'lt;HowToApply>'||
406 '&'||'lt;ApplicationMethods>'||
407 '&'||'lt;ByWeb>'||
408 '&'||'lt;URL><xsl:value-of select="application_url"/>&'||'lt;/URL>'||
409 '&'||'lt;/ByWeb>'||
410 '&'||'lt;/ApplicationMethods>'||
411 '&'||'lt;SummaryText><xsl:value-of select="how_to_apply"/>&'||'lt;/SummaryText>'||
412 '&'||'lt;/HowToApply>'||
413 --
414 -- Only display NumberToFill XML element if number_of_openings info is available
415 --
416 ' <xsl:if test="string-length(number_of_openings)!=0">'||
417 '&'||'lt;NumberToFill><xsl:value-of select="number_of_openings"/>&'||'lt;/NumberToFill>'||
418 ' </xsl:if>'||
419 '&'||'lt;/JobPositionPosting>'||
420 ']]&'||'gt;&'||'lt;/Payload>'||
421 '&'||'lt;/Packet>'||
422 '&'||'lt;/Envelope>'||
423 '</xsl:for-each>'||
424 '</xsl:template>'||
425 '</xsl:stylesheet>';
426 else
427 l_stylesheet_url:=fnd_profile.value('APPS_FRAMEWORK_AGENT')||'/OA_HTML/'||p_stylesheet;
428 tv_sheet:= irc_xml_util.http_get_pieces(l_stylesheet_url,100);
429 lv_stylesheet:='';
430 for l_index in 1..tv_sheet.count loop
431 lv_stylesheet:=lv_stylesheet||tv_sheet(l_index);
432 end loop;
433 end if;
434
435
436 ctx:= dbms_xmlquery.newContext(l_query);
437 dbms_xmlquery.setBindValue(ctx,'1',p_recruitment_activity_id);
438 dbms_xmlquery.setBindValue(ctx,'2',p_sender_id);
439 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
440 dbms_xmlquery.setRowsetTag(ctx,'JobPositionPosting');
441 clobdoc:=dbms_xmlquery.getXML(ctx);
442 dbms_xmlquery.closeContext(ctx);
443 --
444 parser:=xmlparser.newparser;
445 -- parse the clob document
446 xmlparser.parseCLOB(parser,clobdoc);
447 -- and put the parsed clob document in to an xml document
448 xmldoc:=xmlparser.getDocument(parser);
449 engine:=xslprocessor.newProcessor;
450 dbms_lob.createTemporary(l_jpp_doc,false,dbms_lob.call);
451 -- create the stylesheet
452 xmlparser.parseBuffer(parser,lv_stylesheet);
453 stylesheetdoc:=xmlparser.getDocument(parser);
454 stylesheet:=xslprocessor.newStylesheet(stylesheetdoc,null);
455 -- transform the queried xml document using the stylesheet
456 xslprocessor.processXSL(engine,stylesheet,xmldoc,l_jpp_doc);
457 l_jpp_doc:=dbms_xmlgen.convert(l_jpp_doc,1);
458 xmlParser.freeParser(parser);
459 xslprocessor.freeProcessor(engine);
460 xmldom.freeDocument(xmldoc);
461 xmldom.freeDocument(stylesheetdoc);
462 p_jpp_doc:=l_jpp_doc;
463 -- p_jpp_doc:=clobdoc;
464 -- dbms_lob.write(p_jpp_doc,length(lv_stylesheet),1,lv_stylesheet);
465 exception when others then
466 xslprocessor.freeProcessor(engine);
467 xmldom.freeDocument(xmldoc);
468 xmldom.freeDocument(stylesheetdoc);
469 raise;
470 end generateJPPint;
471 --
472 function generateJPP(p_recruitment_activity_id in number
473 ,p_sender_id in number
474 ,p_stylesheet varchar2 default null) return CLOB is
475 l_formatted_doc CLOB;
476 begin
477 generateJPPint(p_recruitment_activity_id=>p_recruitment_activity_id
478 ,p_sender_id =>p_sender_id
479 ,p_stylesheet=>p_stylesheet
480 ,p_jpp_doc=>l_formatted_doc);
481
482 return l_formatted_doc;
483
484 end generateJPP;
485 --
486 procedure show_posting(p in number
487 ,u in number
488 ,s in varchar2 default null) is
489 l_result CLOB;
490 begin
491 l_result:=generateJPP(p,u,s);
492 htp.p(dbms_lob.substr(l_result));
493 end show_posting;
494 --
495 end;