[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT business_group_id
FROM per_business_groups
WHERE business_group_id = nvl(p_business_group_id, business_group_id)
AND legislation_code = 'KW';
SELECT org.organization_id
,org.name
FROM hr_all_organization_units org
,hr_organization_information hoi
WHERE org.organization_id IN (SELECT pose.organization_id_child
FROM per_org_structure_elements pose
CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
AND pose.org_structure_version_id = p_org_structure_version_id
START WITH pose.organization_id_parent = nvl(p_legal_employer, l_parent_id)
AND pose.org_structure_version_id = p_org_structure_version_id
UNION
SELECT nvl(p_legal_employer, l_parent_id)
FROM DUAL)
AND p_org_structure_version_id IS NOT NULL
AND (p_legal_employer IS NOT NULL OR l_parent_id IS NOT NULL)
AND org.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
UNION
SELECT org.organization_id
,org.name
FROM hr_all_organization_units org
,hr_organization_information hoi
WHERE org.organization_id = NVL(p_legal_employer,org.organization_id)
AND org.business_group_id = l_business_group_id
AND p_org_structure_version_id IS NULL
AND org.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER';
SELECT /*+ INDEX(disb, PER_DISABILITIES_F_PK) */ people.employee_number
,people.full_name
,period.date_start
,hr3.meaning disability_status
,job.name job_name
,pos.name position_name
--,emp.name employment_office
,hr1.meaning disability_type
,hr2.meaning reason
,nvl(to_char(disb.degree),get_lookup_meaning('KW_DISABILITY_RANGE',(dis_information1))) rate
--, null RATE
,disb.dis_information2 rep_description
,disb.incident_id
,disb.disability_id
FROM per_all_assignments_f assg
,per_all_people_f people
,per_disabilities_f disb
,per_jobs job
,per_all_positions pos
,per_periods_of_service period
,hr_lookups hr1
,hr_lookups hr2
,hr_lookups hr3
,hr_all_organization_units org
--,hr_all_organization_units emp
,hr_soft_coding_keyflex hsck
WHERE assg.person_id = people.person_id
AND assg.assignment_type = 'E'
AND (l_date) BETWEEN assg.effective_start_date
AND assg.effective_end_date
AND (l_date) between people.effective_start_date
AND people.effective_end_date
AND (l_date) between disb.effective_start_date
AND disb.effective_end_date
AND assg.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND assg.job_id = job.job_id(+)
AND assg.position_id = pos.position_id(+)
AND people.person_id = period.person_id
--AND assg.business_group_id = org.business_group_id
--AND assg.organization_id = rec_get_org_id.organization_id
AND assg.business_group_id = org.business_group_id
AND org.business_group_id = l_business_group_id
AND hr1.lookup_type = 'DISABILITY_CATEGORY'
AND hr1.lookup_code = disb.category
--AND assg.organization_id = org.organization_id
AND disb.person_id = people.person_id
AND disb.reason = hr2.lookup_code(+)
AND hr2.lookup_type(+) = 'DISABILITY_REASON'
AND disb.status = hr3.lookup_code
AND hr3.lookup_type = 'DISABILITY_STATUS'
AND org.organization_id = rec_get_org_id.organization_id
AND nvl(p_disability_type, hr1.lookup_code) = hr1.lookup_code
AND nvl(p_disability_status, hr3.lookup_code) = hr3.lookup_code
--AND emp.organization_id = hsck.segment1
AND to_char(org.organization_id) = hsck.segment1
AND hsck.segment1 = to_char(rec_get_org_id.organization_id)
ORDER BY full_name, employee_number;
SELECT fnd_date.canonical_to_date(p_date)
INTO l_date
FROM DUAL;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('SESSIONID'), l_date);
gxmltable.DELETE;
SELECT distinct pose.organization_id_parent
INTO l_parent_id
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = p_org_structure_version_id
AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
FROM per_org_structure_elements pose1
WHERE pose1.org_structure_version_id
= p_org_structure_version_id);
SELECT name
INTO l_org_name
FROM hr_organization_units
WHERE organization_id = p_business_group_id;
SELECT name
INTO l_structure_name
FROM per_organization_structures
WHERE organization_structure_id = p_org_structure_id;
SELECT version_number
INTO l_version
FROM per_org_structure_versions
WHERE org_structure_version_id = p_org_structure_version_id;
SELECT meaning
INTO l_disability_type
FROM hr_lookups
WHERE lookup_type = 'DISABILITY_CATEGORY'
AND lookup_code = p_disability_type;
SELECT meaning
INTO l_disability_status
FROM hr_lookups
WHERE lookup_type = 'DISABILITY_STATUS'
AND lookup_code = p_disability_status;
SELECT name
INTO l_legal_employer
FROM hr_organization_units
WHERE organization_id = p_legal_employer;
SELECT incident_date
INTO l_incident_date
FROM per_work_incidents
WHERE incident_id = rec_get_dis_emp.incident_id;
SELECT consultation_date
INTO l_consultation_date
FROM per_disabilities_v
WHERE disability_id = rec_get_dis_emp.disability_id
AND l_date BETWEEN effective_start_date and effective_end_date;
SELECT business_group_id
FROM per_business_groups
WHERE business_group_id = nvl(p_business_group_id, business_group_id)
AND legislation_code = 'KW';
SELECT org.organization_id
,org.name
FROM hr_all_organization_units org
,hr_organization_information hoi
WHERE org.organization_id IN (SELECT pose.organization_id_child
FROM per_org_structure_elements pose
CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
AND pose.org_structure_version_id = p_org_structure_version_id
START WITH pose.organization_id_parent = nvl(p_legal_employer, l_parent_id)
AND pose.org_structure_version_id = p_org_structure_version_id
UNION
SELECT nvl(p_legal_employer, l_parent_id)
FROM DUAL)
AND p_org_structure_version_id IS NOT NULL
AND (p_legal_employer IS NOT NULL OR l_parent_id IS NOT NULL)
AND org.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
UNION
SELECT org.organization_id
,org.name
FROM hr_all_organization_units org
,hr_organization_information hoi
WHERE org.organization_id = NVL(p_legal_employer,org.organization_id)
AND org.business_group_id = l_business_group_id
AND p_org_structure_version_id IS NULL
AND org.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER';
SELECT people.employee_number
,people.full_name
,period.date_start
,job.name job_name
,cont.reference cont_reference
,get_lookup_meaning('CONTRACT_TYPE',cont.type) cont_type
,cont.ctr_information1 employment_status
,fnd_date.canonical_to_date(cont.ctr_information2) expiry_date
FROM per_all_assignments_f assg
,per_all_people_f people
,per_contracts cont
,per_jobs job
,per_periods_of_service period
,hr_all_organization_units org
,hr_soft_coding_keyflex hsck
WHERE assg.person_id = people.person_id
AND assg.contract_id = cont.contract_id
AND assg.assignment_type = 'E'
AND (l_date) BETWEEN assg.effective_start_date
AND assg.effective_end_date
AND (l_date) between people.effective_start_date
AND people.effective_end_date
AND assg.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND assg.job_id = job.job_id(+)
AND people.person_id = period.person_id
-- AND assg.organization_id = org.organization_id
AND org.business_group_id = l_business_group_id
AND cont.person_id = people.person_id
AND cont.ctr_information_category = 'KW'
AND org.organization_id = rec_get_org_id.organization_id
AND hsck.segment1 = to_char(rec_get_org_id.organization_id)
AND NVL(to_date(ctr_information2,'YYYY/MM/DD HH24:MI:SS'),
DECODE(duration_units,'D',(active_start_date+duration),
'W',(active_start_date+(duration*7)),
'M',(add_months(active_start_date,duration)),
'Y',(add_months(active_start_date,(duration*12)))))
BETWEEN NVL(l_date,sysdate)
AND DECODE(p_units,'D',(NVL(l_date,sysdate)+p_duration),
'W',(NVL(l_date,sysdate)+(p_duration*7)),
'M',(add_months(NVL(l_date,sysdate),p_duration)),
'Y',(add_months(NVL(l_date,sysdate),(p_duration*12))))
ORDER BY full_name, employee_number;
SELECT fnd_date.canonical_to_date(p_date)
INTO l_date
FROM DUAL;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('SESSIONID'), l_date);
gxmltable.DELETE;
SELECT distinct pose.organization_id_parent
INTO l_parent_id
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = p_org_structure_version_id
AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
FROM per_org_structure_elements pose1
WHERE pose1.org_structure_version_id
= p_org_structure_version_id);
SELECT name
INTO l_org_name
FROM hr_organization_units
WHERE organization_id = p_business_group_id;
SELECT name
INTO l_structure_name
FROM per_organization_structures
WHERE organization_structure_id = p_org_structure_id;
SELECT version_number
INTO l_version
FROM per_org_structure_versions
WHERE org_structure_version_id = p_org_structure_version_id;
SELECT meaning
INTO l_units
FROM hr_lookups
WHERE lookup_type = 'QUALIFYING_UNITS'
AND lookup_code = p_units;
SELECT name
INTO l_legal_employer
FROM hr_organization_units
WHERE organization_id = p_legal_employer;
select userenv('LANGUAGE') into g_nls_db_char from dual;
SELECT file_data
INTO p_pdf_blob
FROM fnd_lobs
WHERE file_id = (SELECT MAX(file_id)
FROM fnd_lobs
WHERE file_name like '%PER_DIS_ar_KW.pdf');
SELECT file_data
INTO p_pdf_blob
FROM fnd_lobs
WHERE file_id = (SELECT MAX(file_id)
FROM fnd_lobs
WHERE file_name like '%PER_CTR_ar_KW.pdf');
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;