Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 212 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture
SQL Error on Date Parameter
alliebaker
Rank 0 - Community Observer
I have been trying to de-bug my code for a while. When I am working in SQL Connect on my desktop my code works perfectly, but when I bring it over into OTBI to create a data model it has generated various errors. Could someone help me review my code?
- First, I was getting the "expected date but got character", so I changed all of my trunc(:p_asofdate) to to_date(:p_asofdate)
- Then I got the error that the date was ending too soon so I changed it to to_date(:p_asofdate, 'MM-DD-YYYY')
- Now I have the error that it is not a valid month despite my date input being '05-01-2024'
WITH
FUNCTION tier_name (
org_id IN NUMBER,
num IN NUMBER,
p_date IN DATE
) RETURN VARCHAR2 IS
tier_name VARCHAR2(1000);
BEGIN
SELECT DISTINCT
houft.name
INTO tier_name
FROM
per_dept_tree_node pdtn,
hr_organization_units_f_tl houft,
fnd_tree_version ftv
WHERE
pdtn.tree_code = ftv.tree_code
AND pdtn.tree_version_id = ftv.tree_version_id
AND pdtn.tree_structure_code = ftv.tree_structure_code
AND trunc(p_date) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
AND ftv.status = 'ACTIVE'
AND ftv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
AND pdtn.depth = num
AND pdtn.pk1_start_value = houft.organization_id
AND houft.language = userenv('LANG')
AND sysdate BETWEEN houft.effective_start_date AND houft.effective_end_date
CONNECT BY
PRIOR pdtn.parent_pk1_value = pdtn.pk1_start_value
START WITH pdtn.pk1_start_value = org_id;
RETURN tier_name;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
FUNCTION tier_manager (
org_id IN NUMBER,
num IN NUMBER,
p_date IN DATE
) RETURN VARCHAR2 IS
tier_manager VARCHAR2(1000);
BEGIN
SELECT DISTINCT
hoi.org_information1
INTO tier_manager
FROM
per_dept_tree_node pdtn,
hr_organization_information_f hoi,
fnd_tree_version ftv
WHERE
pdtn.tree_code = ftv.tree_code
AND pdtn.tree_version_id = ftv.tree_version_id
AND pdtn.tree_structure_code = ftv.tree_structure_code
AND trunc(p_date) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
AND ftv.status = 'ACTIVE'
AND ftv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
AND pdtn.depth = num
AND pdtn.pk1_start_value = hoi.organization_id
AND hoi.org_information_context = 'PER_ORG_MANAGER_INFO'
AND sysdate BETWEEN hoi.effective_start_date AND hoi.effective_end_date
CONNECT BY
PRIOR pdtn.parent_pk1_value = pdtn.pk1_start_value
START WITH pdtn.pk1_start_value = org_id;
RETURN tier_manager;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
FUNCTION manager_vp (
assgn_id IN NUMBER,
num IN NUMBER,
effdt IN DATE
) RETURN VARCHAR2 IS
manager_vp VARCHAR2(1000);
BEGIN
SELECT
full_name
INTO manager_vp
FROM
(
SELECT DISTINCT
pmhr.manager_id,
pmhr.manager_assignment_id,
pmhr.manager_level,
ppnf.full_name,
pjlf.information3 job_level,
ROW_NUMBER() OVER(
PARTITION BY pmhr.person_id
ORDER BY
pmhr.manager_level ASC
) AS row_num
FROM
per_manager_hrchy_dn pmhr,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_job_leg_f pjlf
WHERE
pmhr.assignment_id = assgn_id
AND pmhr.manager_type = 'LINE_MANAGER'
AND trunc(effdt) BETWEEN pmhr.effective_start_date AND pmhr.effective_end_date
AND pmhr.manager_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND pmhr.effective_start_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND pmhr.manager_assignment_id = paam.assignment_id
AND pmhr.effective_start_date BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.job_id = pjlf.job_id
AND pjlf.information_category = 'HRX_US_JOBS'
AND pjlf.legislation_code = 'US'
AND trunc(sysdate) BETWEEN pjlf.effective_start_date AND pjlf.effective_end_date
AND pjlf.information3 LIKE 'E%'
ORDER BY
pmhr.manager_level ASC
)
WHERE
row_num = num;
RETURN manager_vp;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END; SELECT * FROM
(SELECT distinct
papf.person_number employee_number,
pastt.user_status assignment_status,
paam.employee_category "Employee Category",
paam.employment_category "Employment Category",
-- adding in the Headcount type column
CASE
WHEN (substr(hauft.name, 5) like '%Disability') THEN ('LTD')
WHEN (substr(hauft.name, 5) LIKE '%intern%') THEN ('Intern')
WHEN (paam.employee_category = 'INTERN') THEN ('Intern')
WHEN (paam.employment_category = 'PT') THEN ('PT Reg')
ELSE 'FT Reg' END "Headcount Type",
csb.salary_basis_name "Pay Basis",
nvl(ppnf.nam_information16, ppnf.last_name)
|| ', '
|| nvl(ppnf.known_as, ppnf.first_name) "Prefferred Full Name",
ppnf.full_name "Legal Full Name",
ppnf.last_name "Legal Last Name",
ppnf.first_name "Legal Fisrt Name",
ppnf.middle_names,
(CASE
WHEN papf.person_number = '105074' THEN '09-10-1990'
WHEN papf.person_number = '105089' THEN '02-19-1990'
WHEN papf.person_number = '105020' THEN '07-11-1996' WHEN papf.person_number = '105068' THEN '09-17-1993'
WHEN papf.person_number = '105149' THEN '05-04-1976'
WHEN papf.person_number = '107003' THEN '02-28-1981'
ELSE to_char(ccpi.DATE_OF_BIRTH, 'MM-DD-YYYY') END
) DOB,
ccpi.sex ,
(CASE
WHEN ccpi.ethnicity = '10' THEN 'African'
WHEN ccpi.ethnicity = '7' THEN 'American Indian or Alaska Native'
WHEN ccpi.ethnicity = 'E2' THEN 'Any Other'
WHEN ccpi.ethnicity = 'AE1' THEN 'Arab'
WHEN ccpi.ethnicity = '5' THEN 'Asian'
WHEN ccpi.ethnicity = 'C4' THEN 'Asian - Any other Asian background'
WHEN ccpi.ethnicity = 'C1' THEN 'Asian - Indian'
WHEN ccpi.ethnicity = 'C2' THEN 'Asian - Pakistani'
WHEN ccpi.ethnicity = '15' THEN 'Black'
WHEN ccpi.ethnicity = '3' THEN 'Black or African American'
WHEN ccpi.ethnicity = 'ORA_HRX_BRIN' THEN 'Brazilian Indian'
WHEN ccpi.ethnicity = 'E1' THEN 'Chinese'
WHEN ccpi.ethnicity = '30' THEN 'Coloured'
WHEN ccpi.ethnicity = '4' THEN 'I am Hispanic or Latino.'
WHEN ccpi.ethnicity = 'SG_I' THEN 'Indian'
WHEN ccpi.ethnicity = 'ORA_HRX_MIXED' THEN 'Mixed'
WHEN ccpi.ethnicity = '6' THEN 'Native Hawaiian or other Pacific Islander'
WHEN ccpi.ethnicity = '8' THEN 'Not disclosed'
WHEN ccpi.ethnicity = 'E3' THEN 'Other - Arab'
WHEN ccpi.ethnicity = 'AE2' THEN 'South Asian'
WHEN ccpi.ethnicity = '2' THEN 'Two or more races'
WHEN ccpi.ethnicity = '1' THEN 'White'
WHEN ccpi.ethnicity = 'A0' THEN 'White - Any other White background'
WHEN ccpi.ethnicity = 'A1' THEN 'White - English/Welsh/Scottish/Northern Irish/British'
WHEN ccpi.ethnicity = 'A2' THEN 'White - Irish'
ELSE NULL END) Ethnicity,
pu.username "LANID",
(
SELECT
ppeif.pei_information1
FROM
per_people_extra_info_f ppeif
WHERE
ppeif.person_id = papf.person_id
AND ppeif.pei_information_category = 'Appointment'
AND ppeif.pei_information1 IN (
'R',
'FT',
'I'
)
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN ppeif.effective_start_date AND ppeif.effective_end_date
AND ppeif.pei_information_date1 = (
SELECT
MAX(ppeif1.pei_information_date1)
FROM
per_people_extra_info_f ppeif1
WHERE
ppeif1.person_id = ppeif.person_id
AND ppeif1.pei_information_category = 'Appointment'
AND ppeif1.pei_information_date1 <= to_date(:p_asofdate, 'MM-DD-YYYY')
)
) "Appointment_Type",
(SELECT
to_char(ppeif.pei_information_date1, 'MM-DD-YYYY')
FROM
per_people_extra_info_f ppeif
WHERE
ppeif.person_id = papf.person_id
AND ppeif.pei_information_category = 'EBS Service Dates'
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN ppeif.effective_start_date AND ppeif.effective_end_date
AND ppeif.pei_information_date1 = (
SELECT
MAX(ppeif1.pei_information_date1)
FROM
per_people_extra_info_f ppeif1
WHERE
ppeif1.person_id = ppeif.person_id
AND ppeif1.pei_information_category = 'EBS Service Dates'
AND ppeif1.pei_information_date1 <= to_date(:p_asofdate, 'MM-DD-YYYY')
) ) "Appointment Date",
to_char(ppos.date_start, 'MM/DD/YYYY') "Latest_Start_Date",
(SELECT
to_char(ppeif.pei_information_date4, 'MM-DD-YYYY')
FROM
per_people_extra_info_f ppeif
WHERE
ppeif.person_id = papf.person_id
AND ppeif.pei_information_category = 'EBS Service Dates'
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN ppeif.effective_start_date AND ppeif.effective_end_date
AND ppeif.pei_information_date1 = (
SELECT
MAX(ppeif1.pei_information_date1)
FROM
per_people_extra_info_f ppeif1
WHERE
ppeif1.person_id = ppeif.person_id
AND ppeif1.pei_information_category = 'EBS Service Dates'
AND ppeif1.pei_information_date1 <= to_date(:p_asofdate, 'MM-DD-YYYY')
) ) "service_date",
(
SELECT
mgr_ppnf.full_name
FROM
per_person_names_f mgr_ppnf
WHERE
mgr_ppnf.person_id = pasf.manager_id
AND mgr_ppnf.name_type = 'GLOBAL'
AND pasf.effective_start_date BETWEEN mgr_ppnf.effective_start_date AND mgr_ppnf.effective_end_date
) supervisor_name,
(
SELECT
papf_sup.person_number
FROM
per_all_people_f papf_sup
WHERE
papf_sup.person_id = pasf.manager_id
AND trunc(sysdate) BETWEEN papf_sup.effective_start_date AND papf_sup.effective_end_date
) supervisor_id,
paam.internal_mailstop,
paam.internal_office_number,
(select pea2.email_address from per_email_addresses pea2 where pea2.person_id=papf.person_id
and sysdate between pea2.date_from and nvl(pea2.date_to,sysdate+1)
and pea2.email_type='W1' and rownum=1) email_address,
(select distinct gc.segment5 from
gl_code_combinations gc
where gc.CODE_COMBINATION_ID=paam.default_code_comb_id) Division,
(
SELECT
hoi.org_information3
FROM
hr_organization_information hoi
WHERE
hoi.org_information_context = 'PER_GL_COST_CENTER_INFO'
AND hoi.organization_id = paam.organization_id
) AS le_code,
(
SELECT
hoi.org_information1
FROM
hr_organization_information hoi
WHERE
hoi.org_information_context = 'PER_GL_COST_CENTER_INFO'
AND hoi.organization_id = paam.organization_id
) AS cc_no,
paam.assignment_name bus_title,
pgft.name "Grade",
pj.job_code "Job Code",
pj.name "Comp Title",
pjlf.information3 job_level,
hra.location_name location_code,
pldg.LEGISLATIVE_DATA_GROUP_ID,
pldg.LEGISLATION_CODE,
pldg.CREATION_DATE,
ccpi.EFFECTIVE_DATE,
ccpi.FULL_APPROVAL_STATUS_DATE,
ccpi.LAST_UPDATE_DATE cmp_last_update,
(CASE
WHEN (hra.location_name = 'Remote - Home Office')
THEN nvl((SELECT P2.COUNTRY FROM PER_PERSON_ADDR_USAGES_F P1, PER_ADDRESSES_F P2
WHERE P1.PERSON_ID = PAPF.PERSON_ID AND P1.ADDRESS_TYPE = 'HOME'
AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE AND P1.EFFECTIVE_END_DATE
AND P1.ADDRESS_ID = P2.ADDRESS_ID
AND (TRUNC(SYSDATE) BETWEEN P2.EFFECTIVE_START_DATE AND P2.EFFECTIVE_END_DATE) AND ROWNUM=1),
(select p3.country from PER_ADDRESSES_F p3 where p3.address_id=papf.MAILING_ADDRESS_ID
and TRUNC(SYSDATE) BETWEEN P3.EFFECTIVE_START_DATE AND P3.EFFECTIVE_END_DATE))
WHEN (SELECT
hoi.org_information3
FROM
hr_organization_information hoi
WHERE
hoi.org_information_context = 'PER_GL_COST_CENTER_INFO'
AND hoi.organization_id = paam.organization_id) ='721'
then hra.COUNTRY
ELSE paam.LEGISLATION_CODE
END) country_code,
substr(hauft.name, 5) department,
tier_name(haouf.organization_id, 1, sysdate) tier1name,
tier_name(haouf.organization_id, 2, sysdate) tier2name,
tier_name(haouf.organization_id, 3, sysdate) tier3name,
tier_manager(haouf.organization_id, 3, sysdate) AS vp_name,
tier_manager(haouf.organization_id, 2, sysdate) AS department_leader,
tier_manager(haouf.organization_id, 1, sysdate) mc_name,
nvl((
CASE
WHEN pjlf.information3 LIKE 'E%' THEN
ppnf.full_name
ELSE
manager_vp(paam.assignment_id, 1, :p_asofdate)
END
), ppnf.full_name) manager_vp_name,
paam.ass_attribute4 lti,
paam.ass_attribute5 "Officer_Code",
cs.currency_code,
(
CASE
WHEN csb.salary_basis_name = 'Hourly' THEN
cs.salary_amount
ELSE
round(cs.salary_amount, 2)
END
) salary_amount,
round(cs.annual_salary, 2) annual_salary,
aip.screen_entry_value "AIP_Target_Percentage",
sip.target "SIP_Target_Amount",
sip.position "SIP_POS",
gdr.conversion_rate,
round(cs.annual_salary*NVL(gdr.conversion_rate, 1), 2) usd_annual_salary,
(
CASE
WHEN csb.salary_basis_name = 'Hourly' THEN
cs.salary_amount
ELSE
round(cs.salary_amount*NVL(gdr.conversion_rate, 1), 2)
END
) usd_salary_amount,
sip.target*NVL(gdr.conversion_rate, 1) "USD_SIP_Target_Amount",
(select pawmf.value
from per_assign_work_measures_f pawmf
WHERE pawmf.assignment_id = paam.assignment_id
and pawmf.unit = 'FTE'
AND paam.effective_start_date between pawmf.effective_start_date AND pawmf.effective_end_date) FTE ,
ROW_NUMBER() OVER (PARTITION BY papf.person_number ORDER BY ccpi.LAST_UPDATE_DATE DESC) as rn
FROM
PER_ALL_PEOPLE_F_V papf,
per_person_names_f ppnf,
per_periods_of_service ppos,
per_all_assignments_m paam,
cmp_salary cs,
cmp_salary_bases_vl csb,
per_email_addresses pea,
per_grades_f pgf,
per_grades_f_tl pgft,
per_assignment_status_types past,
per_assignment_status_types_tl pastt,
hr_org_unit_classifications_f houcf,
hr_all_organization_units_f haouf,
hr_organization_units_f_tl hauft,
hr_locations_all hra,
per_assignment_supervisors_f pasf,
per_jobs pj,
per_job_leg_f pjlf,
per_legislative_data_groups pldg,
per_users pu,
cmp_cwb_person_info ccpi,
gl_daily_rates gdr,
(
SELECT
peef.assignment_id assignment_id,
peev.screen_entry_value screen_entry_value
FROM
pay_element_entries_vl peef,
pay_element_entry_values_f peev,
pay_input_values_tl piv,
pay_element_types_vl petf
WHERE
petf.element_name IN (
'AIP Bonus Target Core',
'AIP Bonus Target Mgmt'
)
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN petf.effective_start_date AND petf.effective_end_date
AND petf.element_type_id = peef.element_type_id
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peef.effective_start_date AND peef.effective_end_date
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peef.effective_start_date1 AND peef.effective_end_date1
AND peef.element_entry_id = peev.element_entry_id
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
AND piv.name = 'Percentage'
AND piv.language = 'US'
) aip,
(
SELECT
peef.assignment_id assignment_id,
peeva.screen_entry_value target,
pos.screen_entry_value position
FROM
pay_element_entries_vl peef,
pay_element_entry_values_f peeva,
pay_input_values_tl piva,
pay_element_types_vl petf,
(
SELECT
peevp.element_entry_id,
peevp.screen_entry_value
FROM
pay_element_entry_values_f peevp,
pay_input_values_tl pivp
WHERE
to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peevp.effective_start_date AND peevp.effective_end_date
AND peevp.input_value_id = pivp.input_value_id
AND pivp.name = 'Position (For HR Use Only)'
AND pivp.language = 'US'
) pos
WHERE
petf.element_name = 'SIP Target'
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN petf.effective_start_date AND petf.effective_end_date
AND petf.element_type_id = peef.element_type_id
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peef.effective_start_date AND peef.effective_end_date
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peef.effective_start_date1 AND peef.effective_end_date1
AND peef.element_entry_id = peeva.element_entry_id
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN peeva.effective_start_date AND peeva.effective_end_date
AND peeva.input_value_id = piva.input_value_id
AND piva.name = 'Amount'
AND piva.language = 'US'
AND peef.element_entry_id = pos.element_entry_id (+)
) sip
WHERE
1 = 1
AND papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND paam.period_of_service_id = ppos.period_of_service_id
AND ccpi.person_id = paam.person_id
AND pldg.BUSINESS_GROUP_ID = houcf.BUSINESS_GROUP_ID
AND papf.person_id = paam.person_id
AND paam.assignment_status_type_id = past.assignment_status_type_id
AND past.assignment_status_type_id = pastt.assignment_status_type_id
AND pastt.language = 'US'
AND paam.grade_id = pgf.grade_id (+)
AND pgf.grade_id = pgft.grade_id (+)
AND pgft.language (+) = userenv('LANG')
AND papf.primary_email_id = pea.email_address_id (+)
AND cs.assignment_id (+) = paam.assignment_id
AND csb.salary_basis_id (+) = cs.salary_basis_id
AND paam.primary_flag = 'Y'
AND paam.assignment_type = 'E'
AND paam.assignment_status_type IN (
'ACTIVE',
'SUSPENDED'
)
AND paam.organization_id = haouf.organization_id (+)
AND haouf.organization_id = houcf.organization_id (+)
AND haouf.organization_id = hauft.organization_id (+)
AND hauft.language (+) = userenv('LANG')
AND houcf.classification_code (+) = 'DEPARTMENT'
AND paam.location_id = hra.location_id (+)
AND paam.assignment_id = pasf.assignment_id (+)
AND pasf.manager_type (+) = 'LINE_MANAGER'
AND paam.job_id = pj.job_id (+)
AND pj.job_id = pjlf.job_id (+)
AND pjlf.information_category (+) = 'HRX_US_JOBS'
AND pjlf.legislation_code (+) = 'US'
AND papf.person_id = pu.person_id (+)
AND gdr.from_currency(+) = cs.currency_code
AND gdr.conversion_type (+) = 'Corporate'
AND gdr.to_currency(+) = 'USD'
and gdr.conversion_date(+) =
(CASE
WHEN to_date(:p_asofdate, 'MM-DD-YYYY') > (trunc(sysdate)) THEN trunc(sysdate)
ELSE to_date(:p_asofdate, 'MM-DD-YYYY') END)
AND paam.effective_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.effective_start_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.effective_start_date BETWEEN pgf.effective_start_date (+) AND pgf.effective_end_date (+)
AND paam.effective_start_date BETWEEN pgft.effective_start_date (+) AND pgft.effective_end_date (+)
AND paam.effective_start_date BETWEEN houcf.effective_start_date (+) AND houcf.effective_end_date (+)
AND paam.effective_start_date BETWEEN haouf.effective_start_date (+) AND haouf.effective_end_date (+)
AND paam.effective_start_date BETWEEN hauft.effective_start_date (+) AND hauft.effective_end_date (+)
AND paam.effective_start_date BETWEEN hra.effective_start_date (+) AND hra.effective_end_date (+)
AND paam.effective_start_date BETWEEN pjlf.effective_start_date (+) AND pjlf.effective_end_date (+)
AND paam.effective_start_date BETWEEN pasf.effective_start_date (+) AND pasf.effective_end_date (+)
AND to_date(:p_asofdate, 'MM-DD-YYYY') BETWEEN cs.date_from (+) AND nvl(cs.date_to(+), trunc(sysdate))
AND paam.effective_start_date BETWEEN pu.start_date (+) AND nvl(pu.end_date(+), trunc(sysdate))
AND paam.assignment_id = aip.assignment_id (+)
AND paam.assignment_id = sip.assignment_id (+)
AND pldg.LEGISLATION_CODE =
(CASE
WHEN (hra.location_name = 'Remote - Home Office')
THEN nvl((SELECT P2.COUNTRY FROM PER_PERSON_ADDR_USAGES_F P1, PER_ADDRESSES_F P2
WHERE P1.PERSON_ID = PAPF.PERSON_ID AND P1.ADDRESS_TYPE = 'HOME'
AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE AND P1.EFFECTIVE_END_DATE
AND P1.ADDRESS_ID = P2.ADDRESS_ID
AND (TRUNC(SYSDATE) BETWEEN P2.EFFECTIVE_START_DATE AND P2.EFFECTIVE_END_DATE) AND ROWNUM=1),
(select p3.country from PER_ADDRESSES_F p3 where p3.address_id=papf.MAILING_ADDRESS_ID
and TRUNC(SYSDATE) BETWEEN P3.EFFECTIVE_START_DATE AND P3.EFFECTIVE_END_DATE))
WHEN (SELECT
hoi.org_information3
FROM
hr_organization_information hoi
WHERE
hoi.org_information_context = 'PER_GL_COST_CENTER_INFO'
AND hoi.organization_id = paam.organization_id) ='721'
then hra.COUNTRY
ELSE paam.LEGISLATION_CODE
END)
order by papf.person_number
) WHERE rn = 1
0
Answers
-
HI
Looks like this is a data conversion issue. Check the column date how the data format is stored in the database table/column accordingly build the SQL in that format and validate the results
0