Generating Recruitment Letters using Web ADI

Web ADI can be used to generate mail merge letters from the Request Recruitment Letter

window in Oracle HRMS which associates a letter with an applicant assignment status, or

Letter Request form in Oracle Learning Management which uses enrollment status.

1. The first step is to identify the information required from database and create a view
   to retrieve data for the letter to be downloaded to Word. 
   The view must join with the PER_LETTER_REQUEST_LINES table, and
   must include the column LETTER_REQUEST_ID.

create or replace view MBE_APP_LETTER_CONTRACT_SITE as

select ppf.full_name applicant_name,

haou.name department,

hla.location_code location,

pjd.segment2 designation,

pa.address_line1 Flat_Door_Block,

pa.address_line2 Building,

pa.address_line3 Post_Office,

pa.add_information13 Area,

pa.add_information14 Town_City,

pa.postal_code Pin_Code,

to_char(sysdate,'DD-Mon-YYYY') Current_Date,

papl.projected_hire_date Proposed_Start_Date,

papl.Appl_Attribute11 Project_Name,

papl.appl_attribute12 Assignment_Fee,

papl.appl_attribute13 Contract_End_Date,

ppf1.full_name Recruiter_Name,

pjd1.segment2 Recruiter_designation,

haou1.name Recruiter_department

from per_all_people_f ppf,

per_all_assignments_f paf,

per_jobs pj,

per_job_definitions pjd,

hr_all_organization_units haou,

hr_locations_all hla,

per_letter_request_lines plr,

per_addresses pa,

per_applications papl,

per_All_people_f ppf1,

per_all_assignments_f paf1,

per_jobs pj1,

per_job_definitions pjd1,

hr_all_organization_units haou1

where ppf.business_group_id=(select business_group_id from per_business_groups wherenamelike'MBE%')

and ppf.person_id = paf.person_id

and paf.assignment_status_type_id=6 – 6 stands for Applicant Accepted

and paf.job_id=pj.job_id(+)

and pjd.job_definition_id(+)=pj.job_definition_id

and ppf.person_id = plr.person_id

and paf.assignment_id=plr.assignment_id

and haou.organization_id=paf.organization_id

and hla.location_id(+)=paf.location_id

and NVL(pa.primary_flag, ‘Y’) ='Y'

and pa.person_id(+)=ppf.person_id

and papl.person_id=ppf.person_id

and paf.assignment_status_type_id=plr.assignment_status_type_id

and ppf1.person_id=paf.recruiter_id

and paf1.person_id=ppf1.person_id

and NVL(paf1.primary_flag, ‘Y’) = 'Y'

and pj1.job_id(+)=paf1.job_id

and pjd1.job_definition_id(+)=pj1.job_definition_id

and haou1.organization_id=paf1.organization_id

and trunc(plr.date_from) between paf1.effective_start_date and paf1.effective_end_date

and trunc(plr.date_from) between ppf1.effective_start_date and ppf1.effective_end_date

and trunc(plr.date_from) between ppf.effective_start_date and

ppf.effective_end_date

2. Create a Letter Integrator as follows. N.B. Form Name must be set to LETTER.

1. Navigate to Oracle Web ADI responsibility.

2. Go to Create Document function.
   

3. Select ‘None’ for shortcut and click on continue.

4. Select ‘HR Integrator Setup’ from the list of integrators.

5. Select Excel 2003 as viewer and uncheck the ‘Reporting’ button as you will be uploading a new integrator
   definition to the database. Click on Next button.

6. Select the content as ‘None’ and click on Next button.

7. On the review page, check the details and click on Create Document button.

9. A file download popup opens. Click on Open.

9. Once the download is complete, following confirmation message appears. Click on Close.

10. In the blank spreadsheet enter a value for each of the columns as follows. Remaining columns are
    blank for a download integrator.

Upl - ignore

Metadata Type – Double click on the filed and Choose DOWNLOAD from list of values.

Application Short Name - PER

Integrator User Name - Enter a name for your integrator (e.g. Appointment Letter - Contact Site)

View Name - MBE_APP_LETTER_CONTRACT_SITE

Form Name – LETTER

Interface User Name - MBE_APP_LETTER_CONTRACT_SITE 

11. Select Oracle -> Upload from Add-ins.

12. In the Upload Parameters window, select flagged rows and check validate before upload. Click on Upload.

13. Once the information successfully uploaded, you get the following confirmation message.

3. Navigate to Work Structures -> Recruitment Letter Type. Define a letter and link it to Applicant Assignment Status - Accepted.
   In this example, call the Letter, ’App. Letter Contract Site’ (must be same as layout name), and associate it to the Accepted status.
   You’ll need to enter a concurrent program name even though it isn’t used.

4. Now Define Layout, and select the fields that you wish to be included in the letter.
   The Layout Name must be same as the Letter Type Name in 3. i.e. ’App. Letter Contract Site’.

1. Navigate to ‘Oracle Web ADI’ Responsibility, ‘Define Layout’ function.

2. Select the integrator ‘Appointment Letter – Contract Site’ and click on Go.

3. Select on the create button.

4. Enter the layout name same as the recruitment letter type, ‘App. Letter Contract Site’ here and click on Next.

5. Click on Select All to get all these information in your document and click on apply.

5. Navigate to Recruitment -> Request Recruitment Letter. Enter Letter Name, and pick in Applicants to generate
   Appointment letters for. Save work. This will have created rows on the table PER_LETTER_REQUEST_LINES, and
   therefore the view, MBE_APP_LETTER_CONTRACT_SITE, should now pick up the rows that Web ADI will
    download to Word. Click on the export icon to download the information.

1. Navigate to Recruitment -> Request Recruitment Letter.

2. Select the letter type and applicants in the assignment status and save the work. Click on the export icon.

3. Select the viewer as Word 2003 and check the Reporting option.

4. Click on Create Document on the review page.

5. Download window appears. Click on Open.

6. Once the download is complete, following confirmation message appears.

7. Required information gets downloaded in the excel file. Save this as the data source document such as

6. Create a Template Letter using Word, and associate this with the data source file saved above.

1. Go to Mailings tab ->Start Mail Merge ->Step by Step Mail Merge Wizard.

2. Select the document type as Letter and click on Next.

3. Select the current document as starting document and click on next.

4. Select the recipients from an existing list and click on browse.

5. Select the sheet from the list. Check ‘First row of data contains column headers’.

6. It shows the rows of data. Select/deselect the ones as per your requirement.

7. Go to Mailings -> Insert Merged Field to select the fields you want in your letter.

8. Create the letter template and save it as a document, here in this case as “App Letter Contract Site.doc”.

7. If you now upload the Template Letter document created in 6. to the database, the Mail Merge process can be performed
   seamlessly when clicking on the Export Data icon in the Request Recruitment Letter form.

1. Add the HRMS ADI Document Manager submenu to your Web ADI menu and give it a prompt of Document Management.

2. Navigate to Oracle Web ADI -> Document Management -> Document Management.

3. Click on Upload New Document button.

4. Browse the file from your local machine and select the category as Recruitment. Click on Apply. 

5. You will get the confirmation message as App Letter Contract site.doc has been successfully uploaded.

8. Link the document with your integrator.

1. Add the function Web ADI Manage Document Links to your Web ADI menu, and give it a prompt of Link Document.

2. Navigate to Oracle Web ADI -> Link Documents. 

3. Select your integrator and click on Go.

4. Click on the Link icon to link the document with this integrator.

5. Select the document from the list and click on the Select button.

6. It shows the linked document in the list and a confirmation message.

9. Now, navigate to HRMS Manager -> Recruitment -> Request Recruitment Letter.

1. In the Request Letter window, select the letter type, select the persons and click on the export icon.

2. Select word 2003 as viewer and check the Reporting box. Click on Next.

3. Check the details in review page and click on Create Document button.

4. Downloads the letters for all selected applicants in the format below.

Add comment