e-Recruiting

Have you tried the 'opt-in query' at DigitalConcourse.com? That tool will allow you to search for clients by product.

We are in the middle of doing a e-Recruiting impementation. We have run into a few issues here and there. Right now I am trying to get the candidate interface running.

I have implemented successfully at my company recently. Ask away..

Eric

I ended up just rewriting the candidate interface. I has given us some added flexibility that way.

We have the candidate interface working as well. However, it doesnt pass along all the data that I would like it to. We are considering using a combination of MicroSoft Access and Lawson Add-ins to load a more robust data set into Lawson for newly hired employees.

Hopefully this will be a good starting point for you. You may want to break the select out that gets the work phone number. We also do some additional selects against Lawson to make sure an employee isn't in Lawson already(check on SSN) and also to join job requisition data with new hire data. This select has an issue where it would return duplicate rows if a person puts in two or more work phone numbers. The second select attempts to pull a home phone if one is present but will get a cell phone if one is not present etc... The last little bit is just the format of the data that is needed for the PA job.

SELECT DISTINCT req.req_lawson_reqnr AS lawson_req_num
, cust.cust_id
, cust.cust_lastname AS last_name
, cust.cust_firstname AS first_name
, cust.cust_middlename AS middle_name
, addr.custa_address AS addr1
, addr.custa_address2 AS addr2
, addr.custa_city AS city
, state.skill_name AS state
, addr.custa_zip AS zip_code
, country.skill_name AS country
, work_phone.work_phone
, work_phone.work_phone_country
, work_phone.work_phone_ext
, '' AS fax_phone
, '' AS fax_phone_country
, '' AS fax_phone_ext
, cand.cand_ssn AS fica_nbr
, TO_CHAR(cand_autodatecreated,'MMDDYYYY') AS date_applied
, '' AS HIRE_SOURCE
, '' AS PREVIOUS_EMP
, '' AS WORK_SCHEDULE
, '' AS WORK_ELIGIBILITY
, TO_CHAR(SYSDATE,'MMDDYYYY') AS date_export
, TO_CHAR(SYSDATE,'HH24MISS') AS time_export
, DECODE(cust.cust_e_mail,NULL,'',cust.cust_e_mail || '@' || edomain.skill_name) AS email_addr
FROM vn_ijob.askill hired
, vn_ijob.acand_req_skill mtm
, vn_ijob.acand cand
, vn_ijob.areq req
, vn_ijob.acust cust
, vn_ijob.acust_address addr
, vn_ijob.askill state
, vn_ijob.askill edomain
, vn_ijob.askill country
, (
SELECT work_phone.custp_t_cust_id
, work_phone.custp_area_code||work_phone.custp_number AS work_phone
, work_phone_country.skill_name AS work_phone_country
, work_phone.custp_ext AS work_phone_ext
FROM vn_ijob.ACUST_PHONE work_phone
, vn_ijob.askill work
, vn_ijob.askill work_phone_country
WHERE work_phone.custp_t_skill_type = work.skill_id
AND work_phone.custp_t_skill_country = work_phone_country.skill_id
AND work.skill_name = 'Work Phone'
) work_phone
WHERE UPPER(hired.skill_name) LIKE '%HIRED%'
AND mtm.crs_autodatecreated > SYSDATE - 30
AND hired.skill_id = mtm.crs_t_skill_id
AND mtm.crs_t_cand_id = cand.cand_id
AND mtm.crs_t_req_id = req.req_id
AND cand.cand_t_cust_id = cust.cust_id
AND cust.cust_id = addr.custa_t_cust_id(+)
AND addr.custa_t_skill_state = state.skill_id(+)
AND addr.custa_t_skill_country = country.skill_id(+)
AND cust.cust_id = work_phone.custp_t_cust_id(+)
AND cust.cust_t_skill_e_maildomain = edomain.skill_id(+)


SELECT home_phone.custp_area_code||home_phone.custp_number AS home_phone
, home_phone_country.skill_name AS home_phone_country
, home.skill_name AS phone_type
, CASE home.skill_name
WHEN 'Home Phone' THEN 1
WHEN 'Cell Phone' THEN 2
ELSE 3 END as phone_rank
FROM vn_ijob.ACUST_PHONE home_phone
, vn_ijob.askill home
, vn_ijob.askill home_phone_country
WHERE home_phone.custp_t_skill_type = home.skill_id
AND home_phone.custp_t_skill_country = home_phone_country.skill_id
AND home_phone.custp_t_cust_id = ?
ORDER BY phone_rank


strLn = strLn & "A," 'For add
strLn = strLn & "1," 'Company number
strLn = strLn & "," '?lawson_req_num?
strLn = strLn & row("last_name").ToString() & ","
strLn = strLn & row("first_name").ToString() & ","
strLn = strLn & row("middle_name") & ","
strLn = strLn & Mid(row("middle_name").ToString(), 1, 1) & ","
strLn = strLn & "," 'nickName
strLn = strLn & row("addr1").ToString() & ","
strLn = strLn & row("addr2").ToString() & ","
strLn = strLn & ","
strLn = strLn & ","
strLn = strLn & row("city").ToString() & ","
strLn = strLn & row("state").ToString() & ","
strLn = strLn & row("zip_code").ToString() & ","
strLn = strLn & strMapCountry(row("country").ToString()) & ","
strLn = strLn & strMapCountry(HomePhone.strCountry) & ","
strLn = strLn & strFixPhone(HomePhone.strPhoneNum) & ","
strLn = strLn & strMapCountry(row("work_phone_country").ToString()) & ","
strLn = strLn & strFixPhone(row("work_phone").ToString()) & ","
strLn = strLn & row("work_phone_ext").ToString() & ","
strLn = strLn & "," 'fax_country
strLn = strLn & "," 'fax_number
strLn = strLn & "," 'faxext
strLn = strLn & "," 'Date Hired?
strLn = strLn & "," 'appStatus
strLn = strLn & strFixFica(row("fica_nbr").ToString()) & ","
strLn = strLn & row("date_applied").ToString() & "," 'dateApplied
strLn = strLn & "," 'hiresource
strLn = strLn & "," 'refLastName
strLn = strLn & "," 'refFirstName
strLn = strLn & "," 'refMiddleInit
strLn = strLn & "," 'findersFee
strLn = strLn & "," 'prevApply
strLn = strLn & "," 'prevLocation
strLn = strLn & "," 'prevDate
strLn = strLn & "N," 'prevEmploy
strLn = strLn & "," 'prevEmpNbr
strLn = strLn & "," 'prevLocWork
strLn = strLn & "," 'prevBegDate
strLn = strLn & "," 'prevEndDate
strLn = strLn & "," 'appRating
strLn = strLn & "," 'formerLastNumber
strLn = strLn & "," 'formerFirstNumber
strLn = strLn & "," 'formerMi
strLn = strLn & "," 'prevAddr1
strLn = strLn & "," 'prevAddr2
strLn = strLn & "," 'prevAddr3
strLn = strLn & "," 'prevAddr4
strLn = strLn & "," 'prevCity
strLn = strLn & "," 'prevState
strLn = strLn & "," 'prevZip
strLn = strLn & "," 'prevCountryCode
strLn = strLn & "," 'eeoClass
strLn = strLn & "," 'sex
strLn = strLn & "," 'ageFlag
strLn = strLn & "," 'handicapId + ",");
strLn = strLn & "," 'job1 + ",");
strLn = strLn & "," 'job2 + ",");
strLn = strLn & "," 'job3 + ",");
strLn = strLn & "," 'begPay + ",");
strLn = strLn & "," 'endPay + ",");
strLn = strLn & "," 'dateAvail + ",");
strLn = strLn & "," 'veteran + ",");
strLn = strLn & "," 'draftStatus + ",");
strLn = strLn & "," 'finalRank + ",");
strLn = strLn & "," 'curStatus + ",");
strLn = strLn & "," 'lastPhysical + ",");
strLn = strLn & "," 'wrkRstCode1 + ",");
strLn = strLn & "," 'wrkRstCode2 + ",");
strLn = strLn & "," 'wrkRstCode3 + ",");
strLn = strLn & "," 'wrkRstCode4 + ",");
strLn = strLn & "," 'resume + ",");
strLn = strLn & "," 'position1 + ",");
strLn = strLn & "," 'position2 + ",");
strLn = strLn & "," 'position3 + ",");
strLn = strLn & "," 'processLevel + ",");
strLn = strLn & "," 'empStatus + ",");
strLn = strLn & JobReq.strWorkSched & "," 'workSchedule + ",");
strLn = strLn & "," 'nbrFte + ",");
strLn = strLn & "," 'pin + ",");
strLn = strLn & "Y," 'eligWork + ",");
strLn = strLn & "," 'travAvail + ",");
strLn = strLn & "," 'travPct + ",");
strLn = strLn & "," 'relocAvail + ",");
strLn = strLn & "," 'otAvail + ",");
strLn = strLn & row("email_addr") & "," 'emailAddr + ",");
strLn = strLn & row("date_export") & "," 'dateStamp + ",");
strLn = strLn & row("time_export") & "," 'timeStamp + ",");
strLn = strLn & "," 'userId + ",");
strLn = strLn & "," 'employee + ",");
strLn = strLn & "," 'userId + ",");
strLn = strLn & "," 'nameSuffix + ",");
strLn = strLn & "," 'nameTitle + ",");
strLn = strLn & "," 'languageCode + ",");
strLn = strLn & "," 'birthDate + ",");
strLn = strLn & "," 'disability + ",");
strLn = strLn & "," 'currencyCode + ",");
strLn = strLn & "," 'baseBegPay + ",");
strLn = strLn & "," 'baseEndPay + ",");
strLn = strLn & "," 'baseCurrency + ",");
strLn = strLn & "," 'currCode2 + ",");
strLn = strLn & "," 'baseFee + ",");
strLn = strLn & "," 'consent + ",");
strLn = strLn & "," 'lastNamePre + ",");
strLn = strLn & "," 'religion);
strLn = strLn & "," 'StringUtil.LS);
strLn = strLn & vbCrLf