[Home] [Help]
PACKAGE BODY: APPS.IRC_JPP_GENERATOR
Source
1 package body irc_JPP_generator as
2 /* $Header: irjppgen.pkb 120.19.12020000.5 2013/03/14 14:01:19 nitnaras 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
8 ,p_stylesheet_content clob default null) is
9 l_query varchar2(32767);
10 l_jpp_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 l_query:= 'select hr_xml_packet_id_s.nextval packetId'
24 ||' , ''CREATE'' action'
25 ||' , hr_xml_transaction_id_s.nextval transactionid'
26 ||' , translate(to_char(sysdate,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') timestamp'
27 ||' , rse.posting_url vendorURL'
28 ||' , decode(rse.posting_username '
29 ||' ,''#USERID'',to_char(sndr_usr.user_id) '
30 ||' ,''#USERNAME'',sndr_usr.user_name '
31 ||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
32 ||' , rse.posting_username) posting_username'
33 ||' , decode(fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id )'
34 ||' ,''#USERID'',to_char(sndr_usr.user_id) '
35 ||' ,''#USERNAME'',sndr_usr.user_name '
36 ||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
37 ||' , fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id) )posting_password'
38 ||' , rse.stylesheet '
39 ||' , sndr_per.last_name personlastname'
40 ||' , sndr_per.first_name personfirstname'
41 ||' , sndr_per.email_address emailaddress'
42 ||' , sndr_usr.user_id userid'
43 ||' , recr_pp.first_name recr_first_name'
44 ||' , recr_pp.last_name recr_last_name'
45 ||' , recr_pp.full_name recr_full_name'
46 ||' , recr_pp.email_address recr_email_address'
47 ||' , recr_phn.phone_number recr_phone_number'
48 ||' , recr_phn_fax.phone_number recr_fax_number'
49 ||' , recr_pp.person_id recr_person_id'
50 ||' , vac.number_of_openings'
51 ||' , vac.name vacancy_name'
52 ||' , vac.budget_measurement_type'
53 ||' , vac.budget_measurement_value'
54 ||' , vac.attribute1'
55 ||' , vac.attribute2'
56 ||' , vac.attribute3'
57 ||' , vac.attribute4'
58 ||' , vac.attribute5'
59 ||' , vac.attribute6'
60 ||' , vac.attribute7'
61 ||' , vac.attribute8'
62 ||' , vac.attribute9'
63 ||' , vac.attribute10'
64 ||' , vac.attribute11'
65 ||' , vac.attribute12'
66 ||' , vac.attribute13'
67 ||' , vac.attribute14'
68 ||' , vac.attribute15'
69 ||' , vac.attribute16'
70 ||' , vac.attribute17'
71 ||' , vac.attribute18'
72 ||' , vac.attribute19'
73 ||' , vac.attribute20'
74 ||' , vac.attribute_category'
75 ||' , to_char(vac.date_from,''RRRR-MM-DD'') vacancy_start_date'
76 ||' , to_char(vac.date_to,''RRRR-MM-DD'') vacancy_end_date'
77 ||' , rtrim(nvl(fnd_profile.value(''IRC_FRAMEWORK_AGENT'')'
78 ||' ,fnd_profile.value(''APPS_FRAMEWORK_AGENT''))||'
79 ||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
80 ||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
81 ||' ''&p_svid=''||to_char(vac.vacancy_id)||'
82 ||' ''&p_spid=''||to_char(ipc.posting_content_id)||';
83 if(HR_MULTI_TENANCY_PKG.is_multi_tenant_system()) then
84 l_query:=l_query ||' ''&OAMC=''||to_char(''R'')||' ;
85 end if;
86 l_query := l_query
87 ||' ''&p_site_id=''||to_char(rse.recruiting_site_id) application_url'
88 ||' , rtrim(fnd_profile.value(''APPS_FRAMEWORK_AGENT'')||'
89 ||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
90 ||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
91 ||' ''&p_svid=''||to_char(vac.vacancy_id)||'
92 ||' ''&p_spid=''||to_char(ipc.posting_content_id)||'
93 ||' ''&p_site_id=''||to_char(rse.recruiting_site_id) int_application_url'
94 ||' , irc_isc.object_id'
95 ||' , hr_general.decode_lookup(''IRC_PROFESSIONAL_AREA'',irc_isc.professional_area) professional_area'
96 ||' , irc_isc.employee'
97 ||' , irc_isc.contractor'
98 ||' , irc_isc.employment_category'
99 ||' , irc_isc.min_salary'
100 ||' , irc_isc.max_salary'
101 ||' , nvl(irc_isc.max_salary,irc_isc.min_salary) salary'
102 ||' , irc_isc.salary_currency'
103 ||' , irc_isc.salary_period'
104 ||' , irc_isc.travel_percentage'
105 ||' , irc_isc.attribute1'
106 ||' , irc_isc.attribute2'
107 ||' , irc_isc.attribute3'
108 ||' , irc_isc.attribute4'
109 ||' , irc_isc.attribute5'
110 ||' , irc_isc.attribute6'
111 ||' , irc_isc.attribute7'
112 ||' , irc_isc.attribute8'
113 ||' , irc_isc.attribute9'
114 ||' , irc_isc.attribute10'
115 ||' , irc_isc.attribute11'
116 ||' , irc_isc.attribute12'
117 ||' , irc_isc.attribute13'
118 ||' , irc_isc.attribute14'
119 ||' , irc_isc.attribute15'
120 ||' , irc_isc.attribute16'
121 ||' , irc_isc.attribute17'
122 ||' , irc_isc.attribute18'
123 ||', irc_isc.attribute19'
124 ||' , irc_isc.attribute20'
125 ||' , irc_isc.attribute21'
126 ||' , irc_isc.attribute22'
127 ||' , irc_isc.attribute23'
128 ||' , irc_isc.attribute24'
129 ||' , irc_isc.attribute25'
130 ||' , irc_isc.attribute26'
131 ||' , irc_isc.attribute27'
132 ||' , irc_isc.attribute28'
133 ||' , irc_isc.attribute29'
134 ||' , irc_isc.attribute30'
135 ||' , irc_isc.attribute_category'
136 ||' , decode(irc_isc.employment_category,''EITHER'',''Y'',''FULLTIME'',''Y'',''PARTTIME'',''N'',''Y'') full_time'
137 ||' , decode(irc_isc.employment_category,''EITHER'',''Y'',''PARTTIME'',''Y'',''FULLTIME'',''N'',''Y'') part_time'
138 ||' , ipc.display_recruiter_info'
139 ||' , ipctl.posting_content_id'
140 ||' , ipctl.name'
141 ||' , ipctl.org_name'
142 ||' , replace(replace(ipctl.org_description,''&'',''&''||''amp;''),''<'',''&''||''lt;'') org_description'
143 ||' , nvl(ipctl.job_title,ipctl.name) job_title'
144 ||' , replace(replace(ipctl.brief_description,''&'',''&''||''amp;''),''<'',''&''||''lt;'') brief_description'
145 ||' , replace(replace(ipctl.detailed_description,''&'',''&''||''amp;''),''<'',''&''||''lt;'') detailed_description'
146 ||' , replace(replace(ipctl.job_requirements,''&'',''&''||''amp;''),''<'',''&''||''lt;'') job_requirements'
147 ||' , replace(replace(ipctl.additional_details,''&'',''&''||''amp;''),''<'',''&''||''lt;'') additional_details'
148 ||' , replace(replace(ipctl.how_to_apply,''&'',''&''||''amp;''),''<'',''&''||''lt;'') how_to_apply'
149 ||' , replace(replace(ipctl.benefit_info,''&'',''&''||''amp;''),''<'',''&''||''lt;'') benefit_info'
150 ||' , rec.recruitment_activity_id'
151 ||' , to_char(rec.date_start,''RRRR-MM-DD'') posting_start_date'
152 ||' , to_char(rec.date_end,''RRRR-MM-DD'') posting_end_date'
153 ||' , loc.address_line_1'
154 ||' , loc.address_line_2'
155 ||' , loc.address_line_3'
156 ||' , loc.town_or_city'
157 ||' , loc.country'
158 ||' , loc.postal_code'
159 ||' , loc.region_1'
160 ||' , loc.region_2'
161 ||' , loc.region_3'
162 -- cursor to get the details of the competences/skills for the position
163 ||' , cursor (select pc.name '
164 ||' , pce.mandatory '
165 ||' , pc.competence_id '
166 ||' , pce.competence_element_id '
167 ||' , prl1.step_value min_level_id '
168 ||' , prl2.step_value max_level_id '
169 ||' , prl1.name min_level '
170 ||' , prl2.name max_level'
171 ||' from per_competences_tl pc'
172 ||' , per_competence_elements pce'
173 ||' , per_rating_levels prl1'
174 ||' , per_rating_levels prl2'
175 ||' where pc.language=ipctl.source_language'
176 ||' and pc.competence_id = pce.competence_id '
177 ||' and pce.type = ''REQUIREMENT'''
178 ||' and pce.object_name = ''VACANCY'''
179 ||' and pce.object_id = vac.vacancy_id'
180 ||' and prl1.rating_level_id(+) = pce.proficiency_level_id'
181 ||' and prl2.rating_level_id(+) = pce.high_proficiency_level_id'
182 ||' and vac.date_from '
183 ||' between nvl(pce.effective_date_from, vac.date_from)'
184 ||' and nvl(pce.effective_date_to, vac.date_from)'
185 ||' ORDER BY pce.mandatory, pc.name DESC'
186 ||' ) competences'
187 -- cursor to get the variable comp element
188 ||' , cursor (select meaning var_comp from fnd_lookup_values_vl where lookup_type=''IRC_VARIABLE_COMP_ELEMENT'''
189 ||' and lookup_code in ( select variable_comp_lookup vce from irc_variable_comp_elements vce where vac.vacancy_id = vce.vacancy_id)'
190 ||' ) comp_elements'
191 ||' from irc_posting_contents_tl ipctl'
192 ||' , irc_posting_contents ipc'
193 ||' , per_recruitment_activities rec'
194 ||' , per_recruitment_activity_for recf'
195 ||' , per_all_vacancies vac'
196 ||' , hr_locations_all_vl loc'
197 ||' , irc_search_criteria irc_isc'
198 ||' , irc_all_recruiting_sites rse'
199 ||' , fnd_user sndr_usr'
200 ||' , per_all_people_f sndr_per'
201 ||' , per_all_people_f recr_pp'
202 ||' , per_phones recr_phn'
203 ||' , per_phones recr_phn_fax'
204 ||' where rec.recruitment_activity_id=:1'
205 ||' and recf.vacancy_id = vac.vacancy_id'
206 ||' and not exists (select 1 from per_recruitment_activity_for recf2'
207 ||' where recf2.recruitment_activity_id =rec.recruitment_activity_id'
208 ||' and recf2.recruitment_activity_for_id>recf.recruitment_activity_for_id)'
209 ||' and rec.recruitment_activity_id = recf.recruitment_activity_id'
210 ||' and ipc.posting_content_id = rec.posting_content_id'
211 ||' and ipctl.posting_content_id = ipc.posting_content_id '
212 ||' and ipctl.source_language = userenv(''LANG'')'
213 ||' and rse.recruiting_site_id = rec.recruiting_site_id'
214 ||' and sndr_usr.user_id = :2'
215 ||' and sndr_usr.employee_id=sndr_per.person_id'
216 ||' and trunc(sysdate)'
217 ||' between sndr_per.effective_start_date and '
218 ||' sndr_per.effective_end_date'
219 ||' and vac.recruiter_id = recr_pp.person_id(+) '
220 ||' and vac.recruiter_id = recr_phn.parent_id(+) '
221 ||' and recr_phn.parent_table(+) = ''PER_ALL_PEOPLE_F'''
222 ||' and recr_phn.phone_type(+) = ''W1'''
223 ||' and vac.recruiter_id = recr_phn_fax.parent_id(+) '
224 ||' and recr_phn_fax.parent_table(+) = ''PER_ALL_PEOPLE_F'''
225 ||' and recr_phn_fax.phone_type(+) = ''WF'''
226 ||' and trunc(sysdate)'
227 ||' between nvl(recr_pp.effective_start_date,trunc(sysdate)) '
228 ||' and nvl(recr_pp.effective_end_date,trunc(sysdate))'
229 ||' and trunc(sysdate)'
230 ||' between nvl(recr_phn.date_from,trunc(sysdate)) '
231 ||' and nvl(recr_phn.date_to, trunc(sysdate)) '
232 ||' and trunc(sysdate)'
233 ||' between nvl(recr_phn_fax.date_from,trunc(sysdate)) '
234 ||' and nvl(recr_phn_fax.date_to, trunc(sysdate)) '
235 ||' and vac.location_id = loc.location_id (+)'
236 ||' and vac.vacancy_id = irc_isc.object_id (+)'
237 ||' and irc_isc.object_type(+) = ''VACANCY'''
238 ||' and rownum=1';
239
240 if (p_stylesheet is null) then
241
242
243 lv_stylesheet:='<?xml version="1.0" ?>'||
244 '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">'||
245 '<xsl:output method="xml" indent="yes" doctype-system="Envelope-v01-00.dtd"/>'||
246 '<xsl:template match="JobPositionPosting">'||
247 '<xsl:for-each select="row">&'||'lt;?xml version = "1.0"?>
248 ';
249 if(p_stylesheet is null) then
250 lv_stylesheet:=lv_stylesheet||'&'||'lt;!DOCTYPE Envelope SYSTEM "ProvisionalEnvelope-v1.0.dtd">
251 ';
252 end if;
253 lv_stylesheet:=lv_stylesheet||
254 '&'||'lt;Envelope version = "01.00">'||
255 '&'||'lt;Sender>'||
256 '&'||'lt;Id><xsl:value-of select="posting_username "/>&'||'lt;/Id>'||
257 '&'||'lt;Credential><xsl:value-of select="posting_password "/>&'||'lt;/Credential>'||
258 '&'||'lt;/Sender>'||
259 '&'||'lt;Recipient>'||
260 '&'||'lt;Id><xsl:value-of select="vendorurl"/>&'||'lt;/Id>'||
261 '&'||'lt;/Recipient>'||
262 '&'||'lt;TransactInfo transactType = "request">'||
263 '&'||'lt;TransactId><xsl:value-of select="transactionid"/>&'||'lt;/TransactId>'||
264 '&'||'lt;TimeStamp><xsl:value-of select="timestamp"/>&'||'lt;/TimeStamp>'||
265 '&'||'lt;/TransactInfo>'||
266 '&'||'lt;Packet>'||
267 '&'||'lt;PacketInfo packetType = "request">'||
268 '&'||'lt;PacketId><xsl:value-of select="packetid"/>&'||'lt;/PacketId>'||
269 '&'||'lt;Action><xsl:value-of select="action"/>&'||'lt;/Action>'||
270 '&'||'lt;Manifest>JobPositionSeeker-1_1.dtd&'||'lt;/Manifest>'||
271 '&'||'lt;/PacketInfo>'||
272 --
273 -- This is the start of the ProvisionalEnvelope Payload Element.
274 -- This element contains the posting details in JobPositionPosting-1_1.dtd
275 -- complient XML
276 --
277 '&'||'lt;Payload>&'||'lt;![CDATA[&'||'lt;?xml version = "1.0"?>'||
278 '&'||'lt;!DOCTYPE JobPositionPosting SYSTEM "JobPositionPosting-1_1.dtd">'||
279 '&'||'lt;JobPositionPosting>'||
280 '&'||'lt;JobPositionPostingId idOwner="oracle.com"><xsl:value-of select="recruitment_activity_id"/>&'||'lt;/JobPositionPostingId>'||
281 '&'||'lt;HiringOrg>'||
282 '&'||'lt;HiringOrgName><xsl:value-of select="org_name"/>&'||'lt;/HiringOrgName>'||
283 '&'||'lt;Industry>'||
284 '&'||'lt;SummaryText><xsl:value-of select="professional_area"/>&'||'lt;/SummaryText>'||
285 '&'||'lt;/Industry>'||
286 '<xsl:if test="display_recruiter_info=''Y'' and count(recr_person_id)!=0"> '||
287 '&'||'lt;Contact>'||
288 '&'||'lt;PersonName>'||
289 '&'||'lt;FormattedName><xsl:value-of select="recr_full_name"/>&'||'lt;/FormattedName>'||
290 '&'||'lt;GivenName><xsl:value-of select="recr_first_name"/>&'||'lt;/GivenName>'||
291 '&'||'lt;FamilyName><xsl:value-of select="recr_last_name"/>&'||'lt;/FamilyName>'||
292 '&'||'lt;/PersonName>'||
293 '&'||'lt;VoiceNumber>'||
294 '&'||'lt;TelNumber><xsl:value-of select="recr_phone_number"/>&'||'lt;/TelNumber>'||
295 '&'||'lt;/VoiceNumber>'||
296 '&'||'lt;FaxNumber>'||
297 '&'||'lt;TelNumber><xsl:value-of select="recr_fax_number"/>&'||'lt;/TelNumber>'||
298 '&'||'lt;/FaxNumber>'||
299 '&'||'lt;E-mail><xsl:value-of select="recr_email_address"/>&'||'lt;/E-mail>'||
300 '&'||'lt;/Contact>'||
301 '</xsl:if>'||
302 '&'||'lt;OrganizationalUnit>'||
303 '&'||'lt;Description><xsl:value-of select="org_description"/>&'||'lt;/Description>'||
304 '&'||'lt;/OrganizationalUnit>'||
305 '&'||'lt;/HiringOrg>'||
306 '&'||'lt;PostDetail>'||
307 '&'||'lt;StartDate>'||
308 '&'||'lt;Date><xsl:value-of select="posting_start_date"/>&'||'lt;/Date>'||
309 '&'||'lt;/StartDate>'||
310 '&'||'lt;EndDate>'||
311 '&'||'lt;Date><xsl:value-of select="posting_end_date"/>&'||'lt;/Date>'||
312 '&'||'lt;/EndDate>'||
313 '&'||'lt;PostedBy>'||
314 '&'||'lt;Contact>'||
315 '&'||'lt;PersonName>'||
316 '&'||'lt;GivenName><xsl:value-of select="personfirstname"/>&'||'lt;/GivenName>'||
317 '&'||'lt;FamilyName><xsl:value-of select="personlastname"/>&'||'lt;/FamilyName>'||
318 '&'||'lt;/PersonName>'||
319 '&'||'lt;E-mail><xsl:value-of select="emailaddress"/>&'||'lt;/E-mail>'||
320 '&'||'lt;/Contact>'||
321 '&'||'lt;/PostedBy>'||
322 '&'||'lt;/PostDetail>'||
323 '&'||'lt;JobPositionInformation>'||
324 '&'||'lt;JobPositionTitle><xsl:value-of select="job_title"/>&'||'lt;/JobPositionTitle>'||
325 '&'||'lt;JobPositionDescription>'||
326 '&'||'lt;JobPositionPurpose><xsl:value-of select="detailed_description"/>&'||'lt;/JobPositionPurpose>'||
327 '&'||'lt;JobPositionLocation>'||
328 '&'||'lt;PostalAddress>'||
329 '&'||'lt;CountryCode><xsl:value-of select="country"/>&'||'lt;/CountryCode>'||
330 '&'||'lt;PostalCode><xsl:value-of select="postal_code"/> &'||'lt;/PostalCode>'||
331 '&'||'lt;Region><xsl:value-of select="region_1"/>&'||'lt;/Region>'||
332 '&'||'lt;Region><xsl:value-of select="region_2"/>&'||'lt;/Region>'||
333 '&'||'lt;Region><xsl:value-of select="region_3"/>&'||'lt;/Region>'||
334 '&'||'lt;Municipality><xsl:value-of select="town_or_city"/>&'||'lt;/Municipality>'||
335 '&'||'lt;DeliveryAddress>'||
336 '&'||'lt;AddressLine><xsl:value-of select="address_line_1"/>&'||'lt;/AddressLine>'||
337 '&'||'lt;AddressLine><xsl:value-of select="address_line_2"/>&'||'lt;/AddressLine>'||
338 '&'||'lt;AddressLine><xsl:value-of select="address_line_3"/>&'||'lt;/AddressLine>'||
339 '&'||'lt;/DeliveryAddress>'||
340 '&'||'lt;/PostalAddress>'||
341 '&'||'lt;/JobPositionLocation>'||
342 '&'||'lt;Classification>'||
343 --
344 -- The element DirectHireOrContract only allows one of the following child elements
345 --
346 -- DirectHire Contract Temp TempToPerm
347 --
348 -- Oracle only use DirectHire or Contract in this element.
349 --
350 -- The DTD specifies that only one can be set, so if both are, we use DirectHire.
351 -- The child element has to be null.
352 --
353 '<xsl:choose> <xsl:when test="employee=''Y'' or contractor=''Y''">'||
354 '&'||'lt;DirectHireOrContract><xsl:choose>'||
355 '<xsl:when test="employee=''Y''"> '||
356 '&'||'lt;DirectHire>&'||'lt;/DirectHire> </xsl:when><xsl:otherwise>'||
357 '&'||'lt;Contract>&'||'lt;/Contract> </xsl:otherwise></xsl:choose>'||
358 '&'||'lt;/DirectHireOrContract>'||
359 '</xsl:when>'||
360 '</xsl:choose>'||
361 --
362 -- The Schedule Element has FullTime and PartTime child elments.
363 --
364 -- Only one can be set - so we are default to FullTime if both are set.
365 -- The JobPositionPosting-1_1 DTD has an error whereby both FullTime and PartTime
366 -- have a child element called SummaryText. Element FullTime has this as mandatory
367 -- whereas element PartTime has this as optional.
368 --
369 -- For consistancy, both will assume it is mandatory.
370 --
371 '<xsl:choose> <xsl:when test="full_time=''Y'' or part_time=''Y''">'||
372 '&'||'lt;Schedule>'||
373 '<xsl:choose>'||
374 '<xsl:when test="full_time=''Y''"> '||
375 '&'||'lt;FullTime>'||
376 '&'||'lt;SummaryText><xsl:value-of select="full_time"/>&'||'lt;/SummaryText>'||
377 '&'||'lt;/FullTime> </xsl:when><xsl:otherwise>'||
378 '&'||'lt;PartTime>'||
379 '&'||'lt;SummaryText><xsl:value-of select="part_time"/>&'||'lt;/SummaryText>'||
380 '&'||'lt;/PartTime> </xsl:otherwise></xsl:choose>'||
381 '&'||'lt;/Schedule>'||
382 '</xsl:when> </xsl:choose>'||
383 '&'||'lt;/Classification>'||
384 '<xsl:if test="count(salary_currency)!=0 or string-length(benefit_info)!=0 or count(comp_elements/comp_elements_row)!=0">'||
385 '&'||'lt;CompensationDescription>'||
386 --
387 -- Element Pay and associated children will only be displayed if the
388 -- salary_currency is available.
389 --
390 -- Currently, our UI only deals with Annual values, however this xsl
391 -- will handle both monthly and hourly values as well. It will default
392 -- to ANNUAL. (This does mean it will have to change should the PAY_BASIS
393 -- lookup code PERIOD ever be used.
394 --
395 '<xsl:if test="count(salary_currency)!=0"> &'||'lt;Pay><xsl:choose>'||
396 '<!-- Default to ANNUAL as we only really use this -->'||
397 '<xsl:when test="salary_period=''MONTHLY''"> '||
398 '&'||'lt;SalaryMonthly currency="<xsl:value-of select="salary_currency"/>">'||
399 '<xsl:value-of select="salary"/>&'||'lt;/SalaryMonthly>'||
400 '</xsl:when>'||
401 '<xsl:when test="salary_period=''HOURLY''"> '||
402 '&'||'lt;RatePerHour currency="<xsl:value-of select="salary_currency"/>">'||
403 '<xsl:value-of select="salary"/>&'||'lt;/RatePerHour> </xsl:when> <xsl:otherwise>'||
404 '&'||'lt;SalaryAnnual currency="<xsl:value-of select="salary_currency"/>">'||
405 '<xsl:value-of select="salary"/>&'||'lt;/SalaryAnnual> </xsl:otherwise></xsl:choose>'||
406 '&'||'lt;/Pay> </xsl:if> '||
407 --
408 -- Do not generate the BenefitsDescription element and children if there is no
409 -- information to display
410 --
411 '<xsl:if test="string-length(benefit_info)!=0 or count(comp_elements/comp_elements_row)!=0">'||
412 '&'||'lt;BenefitsDescription>'||
413 '&'||'lt;P><xsl:value-of select="benefit_info"/>&'||'lt;/P>'||
414 '&'||'lt;UL>'||
415 '<xsl:for-each select="comp_elements/comp_elements_row">'||
416 '&'||'lt;LI><xsl:value-of select="var_comp"/>&'||'lt;/LI></xsl:for-each>'||
417 '&'||'lt;/UL>'||
418 '&'||'lt;/BenefitsDescription></xsl:if> '||
419 '&'||'lt;/CompensationDescription></xsl:if>'||
420 --
421 -- Do not generate the SummaryText element and children if there is no
422 -- information to display
423 --
424 '<xsl:if test="count(brief_description)!=0 or count(additional_details)!=0">'||
425 '&'||'lt;SummaryText>'||
426 '<xsl:if test="count(brief_description)!=0"> <xsl:value-of select="brief_description"/></xsl:if>'||
427 '<xsl:if test="count(additional_details)!=0"> <xsl:value-of select="additional_details"/></xsl:if>'||
428 '&'||'lt;/SummaryText>'||
429 '</xsl:if>'||
430 '&'||'lt;/JobPositionDescription>'||
431 '&'||'lt;JobPositionRequirements>'||
432 --
433 -- The competences can either be mandatory or preferred.
434 --
435 '<xsl:if test="count(competences/competences_row[mandatory=''Y''])!=0">'||
436 '&'||'lt;QualificationsRequired>&'||'lt;UL>'||
437 '<xsl:for-each select="competences/competences_row[mandatory=''Y'']">'||
438 '&'||'lt;LI>&'||'lt;Qualification><xsl:value-of select="name"/>&'||'lt;/Qualification>&'||'lt;/LI></xsl:for-each>'||
439 '&'||'lt;/UL>&'||'lt;/QualificationsRequired> </xsl:if>'||
440 --
441 -- Using !='Y' as opposed to ='N' in the next test to catch any nulls
442 --
443 '<xsl:if test="count(competences/competences_row[mandatory!=''Y''])!=0"> '||
444 '&'||'lt;QualificationsPreferred>&'||'lt;UL>'||
445 '<xsl:for-each select="competences/competences_row[mandatory!=''Y'']">'||
446 '&'||'lt;LI>&'||'lt;Qualification><xsl:value-of select="name"/>&'||'lt;/Qualification>&'||'lt;/LI></xsl:for-each>'||
447 '&'||'lt;/UL>&'||'lt;/QualificationsPreferred> </xsl:if>'||
448 '&'||'lt;TravelRequired>'||
449 '&'||'lt;PercentageOfTime><xsl:value-of select="travel_percentage"/>'||
450 '&'||'lt;/PercentageOfTime>'||
451 '&'||'lt;/TravelRequired>'||
452 '&'||'lt;SummaryText><xsl:value-of select="job_requirements"/>&'||'lt;/SummaryText>'||
453 '&'||'lt;/JobPositionRequirements>'||
454 '&'||'lt;/JobPositionInformation>'||
455 --
456 -- HowToApply element is mandatory so no need to check if data is available.
457 --
458 '&'||'lt;HowToApply>'||
459 '&'||'lt;ApplicationMethods>'||
460 '&'||'lt;ByWeb>'||
461 '&'||'lt;URL><xsl:value-of select="application_url"/>&'||'lt;/URL>'||
462 '&'||'lt;/ByWeb>'||
463 '&'||'lt;/ApplicationMethods>'||
464 '&'||'lt;SummaryText><xsl:value-of select="how_to_apply"/>&'||'lt;/SummaryText>'||
465 '&'||'lt;/HowToApply>'||
466 --
467 -- Only display NumberToFill XML element if number_of_openings info is available
468 --
469 ' <xsl:if test="string-length(number_of_openings)!=0">'||
470 '&'||'lt;NumberToFill><xsl:value-of select="number_of_openings"/>&'||'lt;/NumberToFill>'||
471 ' </xsl:if>'||
472 '&'||'lt;/JobPositionPosting>'||
473 ']]&'||'gt;&'||'lt;/Payload>'||
474 '&'||'lt;/Packet>'||
475 '&'||'lt;/Envelope>'||
476 '</xsl:for-each>'||
477 '</xsl:template>'||
478 '</xsl:stylesheet>';
479 else
480 if(p_stylesheet_content is not null) then
481 lv_stylesheet:=p_stylesheet_content;
482 else
483 l_stylesheet_url:=fnd_profile.value('APPS_FRAMEWORK_AGENT')||'/OA_HTML/'||p_stylesheet;
484 tv_sheet:= irc_xml_util.http_get_pieces(l_stylesheet_url,100);
485 lv_stylesheet:='';
486 for l_index in 1..tv_sheet.count loop
487 lv_stylesheet:=lv_stylesheet||tv_sheet(l_index);
488 end loop;
489 end if;
490 end if;
491
492
493 ctx:= dbms_xmlquery.newContext(l_query);
494 dbms_xmlquery.setBindValue(ctx,'1',p_recruitment_activity_id);
495 dbms_xmlquery.setBindValue(ctx,'2',p_sender_id);
496 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
497 dbms_xmlquery.setRowsetTag(ctx,'JobPositionPosting');
498 clobdoc:=dbms_xmlquery.getXML(ctx);
499 dbms_xmlquery.closeContext(ctx);
500 --
501 parser:=xmlparser.newparser;
502 -- parse the clob document
503 xmlparser.parseCLOB(parser,clobdoc);
504 -- and put the parsed clob document in to an xml document
505 xmldoc:=xmlparser.getDocument(parser);
506 engine:=xslprocessor.newProcessor;
507 dbms_lob.createTemporary(l_jpp_doc,false,dbms_lob.call);
508 -- create the stylesheet
509 xmlparser.parseBuffer(parser,lv_stylesheet);
510 stylesheetdoc:=xmlparser.getDocument(parser);
511 stylesheet:=xslprocessor.newStylesheet(stylesheetdoc,null);
512 -- transform the queried xml document using the stylesheet
513 xslprocessor.processXSL(engine,stylesheet,xmldoc,l_jpp_doc);
514 l_jpp_doc:=dbms_xmlgen.convert(l_jpp_doc,1);
515 xmlParser.freeParser(parser);
516 xslprocessor.freeProcessor(engine);
517 xmldom.freeDocument(xmldoc);
518 xmldom.freeDocument(stylesheetdoc);
519 p_jpp_doc:=l_jpp_doc;
520 -- p_jpp_doc:=clobdoc;
521 -- dbms_lob.write(p_jpp_doc,length(lv_stylesheet),1,lv_stylesheet);
522 exception when others then
523 xslprocessor.freeProcessor(engine);
524 xmldom.freeDocument(xmldoc);
525 xmldom.freeDocument(stylesheetdoc);
526 raise;
527 end generateJPPint;
528 --
529 function generateJPP(p_recruitment_activity_id in number
530 ,p_sender_id in number
531 ,p_stylesheet varchar2 default null
532 ,p_stylesheet_content clob default null) return CLOB is
533 l_formatted_doc CLOB;
534 begin
535 generateJPPint(p_recruitment_activity_id=>p_recruitment_activity_id
536 ,p_sender_id =>p_sender_id
537 ,p_stylesheet=>p_stylesheet
538 ,p_jpp_doc=>l_formatted_doc
539 ,p_stylesheet_content=>p_stylesheet_content);
540
541 return l_formatted_doc;
542
543 end generateJPP;
544 --
545 procedure show_posting(p in number
546 ,u in number
547 ,s in varchar2 default null
548 ,p_stylesheet_content clob default null) is
549 l_result CLOB;
550 begin
551 l_result:=generateJPP(p,u,s,p_stylesheet_content);
552 htp.p(dbms_lob.substr(l_result));
553 end show_posting;
554 --
555 function getXMLDataFromDB(p_recruitment_activity_id in number
556 ,p_sender_id in number) return clob is
557 l_query varchar2(32767);
558 clobdoc CLOB;
559 ctx DBMS_XMLQUERY.ctxType;
560 begin
561 hr_utility.set_location('Entering getXMLDataFromDB',10);
562 l_query:= 'select hr_xml_packet_id_s.nextval packetId'
563 ||' , ''CREATE'' action'
564 ||' , hr_xml_transaction_id_s.nextval transactionid'
565 ||' , translate(to_char(sysdate,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') timestamp'
566 ||' , rse.posting_url vendorURL'
567 ||' , decode(rse.posting_username '
568 ||' ,''#USERID'',to_char(sndr_usr.user_id) '
569 ||' ,''#USERNAME'',sndr_usr.user_name '
570 ||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
571 ||' , rse.posting_username) posting_username'
572 ||' , decode(fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id )'
573 ||' ,''#USERID'',to_char(sndr_usr.user_id) '
574 ||' ,''#USERNAME'',sndr_usr.user_name '
575 ||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
576 ||' , fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id) )posting_password'
577 ||' , rse.stylesheet '
578 ||' , sndr_per.last_name personlastname'
579 ||' , sndr_per.first_name personfirstname'
580 ||' , sndr_per.email_address emailaddress'
581 ||' , sndr_usr.user_id userid'
582 ||' , recr_pp.first_name recr_first_name'
583 ||' , recr_pp.last_name recr_last_name'
584 ||' , recr_pp.full_name recr_full_name'
585 ||' , recr_pp.email_address recr_email_address'
586 ||' , recr_phn.phone_number recr_phone_number'
587 ||' , recr_phn_fax.phone_number recr_fax_number'
588 ||' , recr_pp.person_id recr_person_id'
589 ||' , vac.number_of_openings'
590 ||' , vac.name vacancy_name'
591 ||' , vac.business_group_id'
592 ||' , vac.budget_measurement_type'
593 ||' , vac.budget_measurement_value'
594 ||' , to_char(vac.date_from,''RRRR-MM-DD'') vacancy_start_date'
595 ||' , to_char(vac.date_to,''RRRR-MM-DD'') vacancy_end_date'
596 ||' , (select name from hr_all_organization_units where organization_id=vac.business_group_id) organization_name'
597 ||' , rtrim(nvl(fnd_profile.value(''IRC_FRAMEWORK_AGENT'')'
598 ||' ,fnd_profile.value(''APPS_FRAMEWORK_AGENT''))||'
599 ||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
600 ||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
601 ||' ''&p_svid=''||to_char(vac.vacancy_id)||'
602 ||' ''&p_spid=''||to_char(ipc.posting_content_id)||';
603 if(HR_MULTI_TENANCY_PKG.is_multi_tenant_system()) then
604 l_query:=l_query ||' ''&OAMC=''||to_char(''R'')||' ;
605 end if;
606 l_query := l_query
607 ||' ''&p_site_id=''||to_char(rse.recruiting_site_id) application_url'
608 ||' , rtrim(fnd_profile.value(''APPS_FRAMEWORK_AGENT'')||'
609 ||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
610 ||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
611 ||' ''&p_svid=''||to_char(vac.vacancy_id)||'
612 ||' ''&p_spid=''||to_char(ipc.posting_content_id)||'
613 ||' ''&p_site_id=''||to_char(rse.recruiting_site_id) int_application_url'
614 ||' , vac.*'
615 ||' , irc_isc.object_id'
616 ||' , hr_general.decode_lookup(''IRC_PROFESSIONAL_AREA'',irc_isc.professional_area) professional_area'
617 ||' , irc_isc.employee'
618 ||' , irc_isc.contractor'
619 ||' , irc_isc.employment_category'
620 ||' , irc_isc.min_salary'
621 ||' , irc_isc.max_salary'
622 ||' , nvl(irc_isc.max_salary,irc_isc.min_salary) salary'
623 ||' , irc_isc.salary_currency'
624 ||' , irc_isc.salary_period'
625 ||' , irc_isc.travel_percentage'
626 ||' , hr_general.decode_lookup(''IRC_TRAVEL_PERCENTAGE'',irc_isc.travel_percentage) travel'
627 ||' , decode(irc_isc.employment_category,''EITHER'',''Y'',''FULLTIME'',''Y'',''PARTTIME'',''N'',''Y'') full_time'
628 ||' , decode(irc_isc.employment_category,''EITHER'',''Y'',''PARTTIME'',''Y'',''FULLTIME'',''N'',''Y'') part_time'
629 ||' ,irc_isc.*'
630 ||' , ipc.display_recruiter_info'
631 ||' , ipctl.posting_content_id'
632 ||' , ipctl.name'
633 ||' , ipctl.org_name'
634 ||' , ipctl.org_description org_description'
635 ||' , nvl(ipctl.job_title,ipctl.name) job_title'
636 ||' , ipctl.brief_description brief_description'
637 ||' , ipctl.detailed_description detailed_description'
638 ||' , ipctl.job_requirements job_requirements'
639 ||' , ipctl.additional_details additional_details'
640 ||' , ipctl.how_to_apply how_to_apply'
641 ||' , ipctl.benefit_info benefit_info'
642 ||' , ipctl.*'
643 ||' , rec.recruitment_activity_id'
644 ||' , to_char(rec.date_start,''RRRR-MM-DD'') posting_start_date'
645 ||' , to_char(rec.date_end,''RRRR-MM-DD'') posting_end_date'
646 ||' , loc.address_line_1'
647 ||' , loc.address_line_2'
648 ||' , loc.address_line_3'
649 ||' , loc.town_or_city'
650 ||' , loc.country'
651 ||' , loc.postal_code'
652 ||' , loc.region_1'
653 ||' , loc.region_2'
654 ||' , loc.region_3'
655 ||' , loc.location_code'
656 -- cursor to get the details of the competences/skills for the position
657 ||' , cursor (select irc_utilities_pkg.removeTags(pc.name) name '
658 ||' , pce.mandatory '
659 ||' , pc.competence_id '
660 ||' , pce.competence_element_id '
661 ||' , pc.*'
662 ||' , pce.*'
663 ||' , prl1.step_value min_level_id '
664 ||' , prl2.step_value max_level_id '
665 ||' , prl1.name min_level '
666 ||' , prl2.name max_level'
667 ||' from per_competences_tl pc'
668 ||' , per_competence_elements pce'
669 ||' , per_rating_levels prl1'
670 ||' , per_rating_levels prl2'
671 ||' where pc.language=ipctl.source_language'
672 ||' and pc.competence_id = pce.competence_id '
673 ||' and pce.type = ''REQUIREMENT'''
674 ||' and pce.object_name = ''VACANCY'''
675 ||' and pce.object_id = vac.vacancy_id'
676 ||' and prl1.rating_level_id(+) = pce.proficiency_level_id'
677 ||' and prl2.rating_level_id(+) = pce.high_proficiency_level_id'
678 ||' and vac.date_from '
679 ||' between nvl(pce.effective_date_from, vac.date_from)'
680 ||' and nvl(pce.effective_date_to, vac.date_from)'
681 ||' ORDER BY pce.mandatory, pc.name DESC'
682 ||' ) competences'
683 -- cursor to get the variable comp element
684 ||' , cursor (select meaning var_comp from fnd_lookup_values_vl where lookup_type=''IRC_VARIABLE_COMP_ELEMENT'''
685 ||' and lookup_code in ( select variable_comp_lookup vce from irc_variable_comp_elements vce where vac.vacancy_id = vce.vacancy_id)'
686 ||' ) comp_elements'
687 ||' from irc_posting_contents_tl ipctl'
688 ||' , irc_posting_contents ipc'
689 ||' , per_recruitment_activities rec'
690 ||' , per_recruitment_activity_for recf'
691 ||' , per_all_vacancies vac'
692 ||' , hr_locations_all_vl loc'
693 ||' , irc_search_criteria irc_isc'
694 ||' , irc_all_recruiting_sites rse'
695 ||' , fnd_user sndr_usr'
696 ||' , per_all_people_f sndr_per'
697 ||' , per_all_people_f recr_pp'
698 ||' , per_phones recr_phn'
699 ||' , per_phones recr_phn_fax'
700 ||' where rec.recruitment_activity_id=:1'
701 ||' and recf.vacancy_id = vac.vacancy_id'
702 ||' and not exists (select 1 from per_recruitment_activity_for recf2'
703 ||' where recf2.recruitment_activity_id =rec.recruitment_activity_id'
704 ||' and recf2.recruitment_activity_for_id>recf.recruitment_activity_for_id)'
705 ||' and rec.recruitment_activity_id = recf.recruitment_activity_id'
706 ||' and ipc.posting_content_id = rec.posting_content_id'
707 ||' and ipctl.posting_content_id = ipc.posting_content_id '
708 ||' and ipctl.source_language = userenv(''LANG'')'
709 ||' and rse.recruiting_site_id = rec.recruiting_site_id'
710 ||' and sndr_usr.user_id = :2'
711 ||' and sndr_usr.employee_id=sndr_per.person_id'
712 ||' and trunc(sysdate)'
713 ||' between sndr_per.effective_start_date and '
714 ||' sndr_per.effective_end_date'
715 ||' and vac.recruiter_id = recr_pp.person_id(+) '
716 ||' and vac.recruiter_id = recr_phn.parent_id(+) '
717 ||' and recr_phn.parent_table(+) = ''PER_ALL_PEOPLE_F'''
718 ||' and recr_phn.phone_type(+) = ''W1'''
719 ||' and vac.recruiter_id = recr_phn_fax.parent_id(+) '
720 ||' and recr_phn_fax.parent_table(+) = ''PER_ALL_PEOPLE_F'''
721 ||' and recr_phn_fax.phone_type(+) = ''WF'''
722 ||' and trunc(sysdate)'
723 ||' between nvl(recr_pp.effective_start_date,trunc(sysdate)) '
724 ||' and nvl(recr_pp.effective_end_date,trunc(sysdate))'
725 ||' and trunc(sysdate)'
726 ||' between nvl(recr_phn.date_from,trunc(sysdate)) '
727 ||' and nvl(recr_phn.date_to, trunc(sysdate)) '
728 ||' and trunc(sysdate)'
729 ||' between nvl(recr_phn_fax.date_from,trunc(sysdate)) '
730 ||' and nvl(recr_phn_fax.date_to, trunc(sysdate)) '
731 ||' and vac.location_id = loc.location_id (+)'
732 ||' and vac.vacancy_id = irc_isc.object_id (+)'
733 ||' and irc_isc.object_type(+) = ''VACANCY'''
734 ||' and rownum=1';
735 ctx:= dbms_xmlquery.newContext(l_query);
736 hr_utility.set_location('After dbms_xmlquery.newContext',20);
737 dbms_xmlquery.setBindValue(ctx,'1',p_recruitment_activity_id);
738 hr_utility.set_location('p_recruitment_activity_id:'||p_recruitment_activity_id,30);
739 dbms_xmlquery.setBindValue(ctx,'2',p_sender_id);
740 hr_utility.set_location('p_sender_id:'||p_sender_id,40);
741 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
742 dbms_xmlquery.setRowsetTag(ctx,'JobPositionPosting');
743 dbms_xmlquery.setSqlToXmlNameEscaping(ctx,true);
744 dbms_xmlquery.setEncodingTag(ctx,dbms_xmlquery.DB_ENCODING);
745 hr_utility.set_location('After dbms_xmlquery.setEncodingTag',50);
746 clobdoc:=dbms_xmlquery.getXML(ctx);
747 hr_utility.set_location('After dbms_xmlquery.getXML',60);
748 dbms_xmlquery.closeContext(ctx);
749 hr_utility.set_location('Leaving getXMLDataFromDB',70);
750 return clobdoc;
751
752 exception when others then
753 hr_utility.set_location('Exception occured',100);
754 hr_utility.set_location('Exception: '||substrb(sqlerrm,1,160),110);
755 hr_utility.set_location('Exception: '||sqlcode,120);
756 raise;
757 end getXMLDataFromDB;
758
759 end;