[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ee.element_entry_id element_entry_id
, eev1.screen_entry_value screen_entry_value
, iv1.name
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND et.element_name = 'Override Holiday Duration'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('Start Date', 'End Date', 'Override Hours', 'Override Days','File ID','Year')
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
ORDER BY ee.element_entry_id;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'Holiday Card Payment Request'
AND nvl(business_group_id, p_business_group_id) = p_business_group_id
AND nvl(legislation_code, 'DK') = 'DK'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = p_name
AND nvl(business_group_id, p_business_group_id) = p_business_group_id
AND nvl(legislation_code, 'DK') = 'DK'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT links.element_link_id
FROM pay_element_links_f links, per_all_assignments_f assign
WHERE links.element_type_id = p_element_type_id
AND links.business_group_id=p_business_group_id
AND assign.assignment_id=p_assignment_id
AND (( links.payroll_id is not null
and links.payroll_id = assign.payroll_id)
OR ( links.link_to_all_payrolls_flag='Y'
and assign.payroll_id is not null)
OR ( links.payroll_id is null
and links.link_to_all_payrolls_flag='N')
OR links.job_id=assign.job_id
OR links.position_id=assign.position_id
OR links.people_group_id=assign.people_group_id
OR links.organization_id=assign.organization_id
OR links.grade_id=assign.grade_id
OR links.location_id=assign.location_id
OR links.pay_basis_id=assign.pay_basis_id
OR links.employment_category=assign.employment_category)
AND p_effective_date BETWEEN links.effective_start_date
AND links.effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'Override Holiday Duration'
AND nvl(business_group_id, p_business_group_id) = p_business_group_id
AND nvl(legislation_code, 'DK') = 'DK'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = p_name
AND nvl(business_group_id, p_business_group_id) = p_business_group_id
AND nvl(legislation_code, 'DK') = 'DK'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT links.element_link_id
FROM pay_element_links_f links, per_all_assignments_f assign
WHERE links.element_type_id = p_element_type_id
AND links.business_group_id=p_business_group_id
AND assign.assignment_id=p_assignment_id
AND (( links.payroll_id is not null
and links.payroll_id = assign.payroll_id)
OR ( links.link_to_all_payrolls_flag='Y'
and assign.payroll_id is not null)
OR ( links.payroll_id is null
and links.link_to_all_payrolls_flag='N')
OR links.job_id=assign.job_id
OR links.position_id=assign.position_id
OR links.people_group_id=assign.people_group_id
OR links.organization_id=assign.organization_id
OR links.grade_id=assign.grade_id
OR links.location_id=assign.location_id
OR links.pay_basis_id=assign.pay_basis_id
OR links.employment_category=assign.employment_category)
AND p_effective_date BETWEEN links.effective_start_date
AND links.effective_end_date;
SELECT o1.name le_name ,
hoi2.organization_id org_id,
hoi2.ORG_INFORMATION1 cvr_no,
hoi2.ORG_INFORMATION2,
hoi2.ORG_INFORMATION3,
hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5 se_no,
hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
AND hoi2.ORG_INFORMATION5 = NVL(p_se_no,hoi2.ORG_INFORMATION5)
AND hoi2.ORG_INFORMATION1= p_cvr_no
AND hoi2.ORG_INFORMATION3 = 'Y';
SELECT ORGANIZATION_ID org_id,ORG_INFORMATION1 cvr_no,ORG_INFORMATION3
FROM hr_organization_information
WHERE org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
AND organization_id in (
select organization_id from hr_organization_units
where business_group_id= p_business_group_id)
AND org_information1 = p_cvr_no ;
SELECT DISTINCT papf.PERSON_ID person_id,
papf.full_name full_name,
paf.assignment_id assg_id ,
scl.SEGMENT10 def_wrk_patt ,
ppf.payroll_id payroll_id ,
ppf.PRL_INFORMATION1 holiday_card
FROM pay_payrolls_f ppf,
per_assignments_f paf,
hr_organization_units hou,
hr_organization_information hoi,
hr_soft_coding_keyflex scl ,
per_people_f papf
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = g_legal_employer_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
AND paf.payroll_id = ppf.payroll_id
AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
AND scl.segment1 = to_char(hou.organization_id)
AND paf.business_group_id = p_business_group_id
AND paf.ASSIGNMENT_NUMBER = p_assignment_no
AND papf.person_id = paf.person_id
AND papf.NATIONAL_IDENTIFIER = p_cvr_no
AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date ;
SELECT hoi2.ORG_INFORMATION1 def_wrk_patt,
NVL(hoi2.org_information5,'N') holiday_card
FROM HR_ORGANIZATION_UNITS hou
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE hou.business_group_id = p_business_group_id
AND hoi1.organization_id = hou.organization_id
AND hoi1.organization_id = p_le_id
AND hoi1.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.ORG_INFORMATION2 = 'Y'
AND hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
AND hoi2.organization_id = hoi1.organization_id
AND p_effective_date BETWEEN hou.DATE_FROM AND nvl(hou.DATE_TO, p_effective_date);
SELECT ppf.PRL_INFORMATION1
FROM pay_payrolls_f ppf
WHERE PAYROLL_id = p_payroll_id
AND p_effective_date BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
SELECT pel.ELEMENT_LINK_ID
FROM pay_element_links_f pel ,pay_element_types_f pet
WHERE pel.ELEMENT_TYPE_ID = pet.element_type_id
AND nvl(pel.PAYROLL_ID,p_payroll_id) = p_payroll_id
AND pet.element_name = p_element_name
AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND pel.business_group_id = p_business_group_id;
SELECT 1
FROM per_all_assignments_f asg1
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assg_id
-- AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Holiday Card Payment Request'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('File ID')
AND ee.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND eev1.screen_entry_value = p_run_id;
SELECT asg2.ASSIGNMENT_ID
FROM per_all_people_f per ,
per_all_assignments_f asg1,
per_all_assignments_f asg2
WHERE asg1.assignment_number = p_assig_no
AND per.NATIONAL_IDENTIFIER = p_cpr_no
AND asg1.person_id = per.person_id
AND per.person_id = asg2.person_id
AND asg2.business_group_id = p_business_group_id
AND asg1.assignment_id <> asg2.assignment_id
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date;
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = v_bg_id;