Introduction:
This SQL query is used to fetching the data for candidates who are applied
various assessment tests (Wheebox) at the time of recruiting like one candidate can apply two or more tests. In this Last update date is used as a parameter so based on the parameter passing data will display.
SQL Query:
SELECT DISTINCT
ROWNUM sno,
name_g.first_name,
name_g.last_name,
irc.candidate_number employeeid,
irb.requisition_number rollnumber,
(
SELECT
LISTAGG(extra.pei_information1, ‘,’) WITHIN GROUP(
ORDER BY
extra.pei_information1
)
FROM
fnd_lookup_values_vl flv,
irc_ja_extra_info extra
WHERE
flv.lookup_type = ‘ASSESSMENT_TEST_ID’
AND flv.lookup_code = extra.pei_information1
AND extra.pei_information_number3 IS NULL
AND extra.pei_information_category = ‘Wheebox’
AND iss.submission_id = extra.submission_id
) assessment_name,
(
SELECT
LISTAGG(flv.tag, ‘,’) WITHIN GROUP(
ORDER BY
extra.pei_information1
)
FROM
fnd_lookup_values_vl flv,
irc_ja_extra_info extra
WHERE
flv.lookup_type = ‘ASSESSMENT_TEST_ID’
AND flv.lookup_code = extra.pei_information1
AND extra.pei_information_number3 IS NULL
AND extra.pei_information_category = ‘Wheebox’
AND iss.submission_id = extra.submission_id
) assessment_ids,
(
SELECT
to_char(extra.creation_date , ‘YYYY-MM-DD HH24:MI:SS’)
FROM
fnd_lookup_values_vl flv,
irc_ja_extra_info extra
WHERE
flv.lookup_type = ‘ASSESSMENT_TEST_ID’
AND flv.lookup_code = extra.pei_information1
AND extra.pei_information_number3 IS NULL
AND extra.pei_information_category = ‘Wheebox’
AND iss.submission_id = extra.submission_id
) assessment_start_date,
(
SELECT
to_char(extra.creation_date + 3, ‘YYYY-MM-DD HH24:MI:SS’)
FROM
fnd_lookup_values_vl flv,
irc_ja_extra_info extra
WHERE
flv.lookup_type = ‘ASSESSMENT_TEST_ID’
AND flv.lookup_code = extra.pei_information1
AND extra.pei_information_number3 IS NULL
AND extra.pei_information_category = ‘Wheebox’
AND iss.submission_id = extra.submission_id
) assessment_end_date,
(
SELECT DISTINCT
email.email_address
FROM
per_email_addresses email
WHERE
email.email_address_id (+) = irc.cand_email_id
AND trunc(sysdate) BETWEEN nvl(email.date_from(+), sysdate) AND nvl(email.date_to(+), sysdate)
) cand_email,
(
SELECT
region_2
FROM
per_addresses_f paf
WHERE
paf.address_id = irc.cand_address_id
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
) center_state,
(
SELECT
town_or_city
FROM
per_addresses_f paf
WHERE
paf.address_id = irc.cand_address_id
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
) test_city,
(
SELECT
town_or_city
FROM
per_addresses_f paf
WHERE
paf.address_id = irc.cand_address_id
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
) center_district,
‘Hello@password123’ password,
decode(pplf.sex, ‘M’, ‘Male’, ‘F’, ‘Female’,
‘Others’) sex
FROM
irc_candidates irc,
per_person_names_f name_g,
per_people_legislative_f pplf,
irc_submissions iss,
irc_requisitions_b irb
WHERE
name_g.person_id (+) = irc.person_id
AND trunc(sysdate) BETWEEN nvl(name_g.effective_start_date, sysdate) AND nvl(name_g.effective_end_date, sysdate)
AND trunc(sysdate) BETWEEN trunc(nvl(pplf.effective_start_date, sysdate)) AND trunc(nvl(pplf.effective_end_date, sysdate))
AND ( iss.last_update_date >= to_date(:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’) )
AND name_g.person_id = pplf.person_id (+)
AND name_g.name_type = ‘GLOBAL’
AND iss.confirmed_flag = ‘Y’
AND iss.person_id = irc.person_id
AND EXISTS (
SELECT
1
FROM
irc_ja_extra_info
WHERE
iss.submission_id = submission_id
AND pei_information_number3 IS NULL
AND pei_information_category = ‘Wheebox’
)
AND iss.requisition_id = irb.requisition_id
AND EXISTS (
SELECT
1
FROM
fnd_lookup_values_vl flv,
irc_ja_extra_info extra
WHERE
flv.lookup_type = ‘ASSESSMENT_TEST_ID’
AND flv.lookup_code = extra.pei_information1
AND extra.pei_information_number3 IS NULL
AND extra.pei_information_category = ‘Wheebox’
AND iss.submission_id = extra.submission_id
)
ORDER BY
1
