[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND enabled_flag = 'Y';
SELECT
PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
,PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
,PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'YEAR')
,PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,fnd_date.canonical_to_date(PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_END_DATE'))
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT global_value
FROM ff_globals_f
WHERE global_name = p_global_name
AND legislation_code = 'DK'
AND p_effective_date BETWEEN effective_start_date AND effective_END_date;
SELECT count(*)
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYDKSTATSA'
AND action_context_id = pactid;
/* If p_sender_id is null=> No Legal Employer selected, hence Service Provider of the BG is the Sender */
CURSOR csr_get_sender_details(p_sender_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
SELECT hoi2.org_information1 CVR_NO
,hou1.name NAME
-- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
,SUBSTR (loc.ADDRESS_LINE_1,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_2,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_3,1,80) ADDR --Bug Fix-4998649
,loc.postal_code PCODE
FROM HR_ORGANIZATION_UNITS hou1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
, HR_LOCATIONS loc
WHERE hou1.business_group_id = p_business_group_id
and hou1.organization_id = nvl(p_sender_id ,hou1.organization_id)
and hou1.location_id = loc.LOCATION_ID(+)
and hoi1.organization_id = hou1.organization_id
and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
and hoi1.org_information1 = nvl2(p_sender_id,'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER')
and hoi1.ORG_INFORMATION2 ='Y'
and hoi2.ORG_INFORMATION_CONTEXT= nvl2(p_sender_id,'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou1.DATE_FROM and nvl(hou1.DATE_TO, p_effective_date);
SELECT hou.organization_id ORG_ID
,hoi2.ORG_INFORMATION1 CVR_NO
,hoi2.ORG_INFORMATION2 DS_WPCODE
,hoi2.ORG_INFORMATION6 PUNIT
,hou.name NAME
-- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
,SUBSTR (loc.ADDRESS_LINE_1,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_2,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_3,1,80) ADDR --Bug Fix-4998649
,loc.postal_code PCODE
FROM HR_ORGANIZATION_UNITS hou
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
, HR_LOCATIONS loc
WHERE hou.business_group_id = p_business_group_id
and hoi1.organization_id = hou.organization_id
and hou.location_id = loc.LOCATION_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_LEGAL_ENTITY_DETAILS'
and hoi2.organization_id = hoi1.organization_id
--and nvl(hoi2.org_information1,0)= nvl2(p_sender_id,p_sender_cvr_no,nvl(hoi2.org_information1,0) )
and hou.organization_id = p_sender_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
SELECT hoi2.ORG_INFORMATION1 DA_SCODE
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'
-- For bug fix 4997786
and hoi2.ORG_INFORMATION_CONTEXT= 'DK_DA_OFFICE_CODE' --'DK_EMPLOYMENT_DEFAULTS'
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
SELECT hoi2.ORG_INFORMATION1 DA_SYS_NO
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_business_group_id
and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
and hoi1.org_information1 = 'HR_BG'
and hoi1.ORG_INFORMATION2 = 'Y'
and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
SELECT effective_date
,fnd_date.CANONICAL_TO_DATE(action_information6) from_date
,fnd_date.CANONICAL_TO_DATE(action_information7) to_date
,to_number(action_information2) business_group_id
,tax_unit_id
,to_number(action_information3) PAYROLL_ID
,action_information8 LE_NAME
,action_information18 LE_PUNIT --8840262
,action_information17 LE_DA_OFFICE_CODE
,action_information10 LE_CVR_NUMBER
FROM pay_action_information pai
WHERE action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYDKASORA'
AND action_information4 = 'HDR';
SELECT hoi2.ORG_INFORMATION1 COND_OF_EMP
,hoi2.ORG_INFORMATION2 EMP_GRP
,hoi2.ORG_INFORMATION3 WORK_HOURS
,hoi2.ORG_INFORMATION4 FREQ
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_EMPLOYMENT_DEFAULTS'
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
SELECT hoi2.ORG_INFORMATION1 DEFAULT_WORK_PATT
,hoi2.ORG_INFORMATION3 HOURLY_ACCR_RATE
,hoi2.ORG_INFORMATION4 SAL_ALLOW_RATE
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 hoi2.ORG_INFORMATION6
FROM hr_organization_information hoi1
, hr_organization_information hoi2
WHERE hoi1.organization_id = hoi2.organization_id
AND hoi1.organization_id = hr_org_id
AND hoi1.org_information1 = 'HR_ORG'
AND hoi1.org_information_context = 'CLASS'
AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS';
SELECT lei_information1
FROM hr_location_extra_info
WHERE location_id = p_location_id
AND information_type='DK_LOCATION_INFO';
SELECT distinct
paaf.assignment_id ASG_ID
,ppf.payroll_name PAYROLL_NAME
,paaf.assignment_number ASSIGNMENT_NUMBER
,substr(to_char(papf.national_identifier),1,instr(to_char(papf.national_identifier),'-')-1)||substr(to_char(papf.national_identifier),instr(to_char(papf.national_identifier),'-')+1) CPR_NO
,paaf.organization_id HR_ORG_ID
,paaf.person_id PERSON_ID
,paaf.location_id LOC_ID --8820009
,papf.EMPLOYEE_NUMBER --8820009
FROM
per_all_people_f papf
,per_all_assignments_f paaf
,pay_payrolls_f ppf
,hr_soft_coding_keyflex scl
--,pay_assignment_actions paa
--,pay_payroll_actions ppa
WHERE paaf.person_id BETWEEN p_start_person AND p_end_person
AND papf.PERSON_ID = paaf.PERSON_ID
AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
/*
AND paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' -- Completed
AND ppa.action_type IN ('P','U') -- Pre-Payments
AND ppa.effective_date BETWEEN p_date_from AND p_date_to
AND ppa.effective_date <= paaf.EFFECTIVE_END_DATE
AND ppa.effective_date >= paaf.EFFECTIVE_start_DATE
*/
AND paaf.EFFECTIVE_START_DATE <= p_date_to
AND paaf.EFFECTIVE_END_DATE >= p_date_from
AND papf.current_employee_flag = 'Y'
AND scl.segment1 = to_char(p_le_id)
ORDER BY asg_id, person_id;
select
paaf.effective_start_date ASG_START_DATE
,paaf.effective_end_date ASG_END_DATE
FROM
per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
where
paaf.assignment_id = p_assignment_id1
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND paaf.EFFECTIVE_START_DATE <= p_date_to1
AND paaf.EFFECTIVE_START_DATE >= p_pre_asg_end_date
AND paaf.EFFECTIVE_END_DATE >= p_date_from1
AND (scl.SEGMENT14 <> p_job_occ_mkode or scl.SEGMENT15 <> p_job_status_mkode
or scl.SEGMENT16 <> p_sal_basis_mkode or scl.SEGMENT17 <> p_time_off_lieu
or paaf.location_id <> p_loc_id);
select paat.absence_attendance_id, paat.absence_attendance_type_id,
GREATEST(paat.date_start,p_date_from) date_start, LEAST(paat.date_end,p_date_to) date_end,
paat.date_start actual_date_start, paat.date_end actual_date_end,
replace(paat.time_start,':','.') time_start, replace(paat.time_end,':','.') time_end,
paatv.information1 ABS_CODE,
paatv.NAME
FROM
PER_ABSENCE_ATTENDANCES paat,
PER_ABSENCE_ATTENDANCE_TYPES paatv
where
paat.PERSON_ID = p_person_id
and paat.business_group_id = p_business_group_id
and paat.business_group_id = paatv.business_group_id
and paat.absence_attendance_type_id = paatv.absence_attendance_type_id
and nvl(paatv.information_category,'DK') = 'DK' --8789760
and (paat.date_start between p_date_from AND p_date_to
OR paat.date_end between p_date_from AND p_date_to
)
and exists
(
select 1
from
pay_element_types_f petf, --8917251
pay_element_entries_f peef,
pay_element_entry_values_f peevf
where
peef.assignment_id = p_asg_id
and petf.element_type_id = peef.element_type_id --8917251
and petf.business_group_id is NULL --8917251
and petf.legislation_code = 'DK' --8917251
and peef.element_entry_id = peevf.element_entry_id
and (peef.effective_start_date between p_date_from AND p_date_to
or peef.effective_end_date between p_date_from AND p_date_to)
and (peevf.effective_start_date between p_date_from AND p_date_to
or peevf.effective_end_date between p_date_from AND p_date_to)
and to_char(paat.absence_attendance_id) = peevf.screen_entry_value
)
--8917251
UNION
select paat.absence_attendance_id, paat.absence_attendance_type_id,
GREATEST(paat.date_start,p_date_from) date_start, LEAST(paat.date_end,p_date_to) date_end,
paat.date_start actual_date_start, paat.date_end actual_date_end,
replace(paat.time_start,':','.') time_start, replace(paat.time_end,':','.') time_end,
paatv.information1 ABS_CODE,
paatv.NAME
FROM
PER_ABSENCE_ATTENDANCES paat,
PER_ABSENCE_ATTENDANCE_TYPES paatv
where
paat.PERSON_ID = p_person_id
and paat.business_group_id = p_business_group_id
and paat.business_group_id = paatv.business_group_id
and paat.absence_attendance_type_id = paatv.absence_attendance_type_id
and nvl(paatv.information_category,'DK') = 'DK' --8789760
and (paat.date_start between p_date_from AND p_date_to
OR paat.date_end between p_date_from AND p_date_to
)
and exists
(
select 1
from
pay_element_types_f petf,
pay_element_entries_f peef,
per_business_groups_perf pbg
where
peef.assignment_id = p_asg_id
and petf.element_type_id = peef.element_type_id
and peef.creator_type = 'A'
and petf.business_group_id is NOT NULL
and pbg.business_group_id = petf.business_group_id
and pbg.legislation_code = 'DK'
and (peef.effective_start_date between p_date_from AND p_date_to
or peef.effective_end_date between p_date_from AND p_date_to)
and to_char(paat.absence_attendance_id) = peef.creator_id
)
Order By 9 Asc;
select global_value
from ff_globals_f
where
global_name = 'DK_HOURS_IN_DAY'
AND p_effective_date between effective_start_date AND effective_end_date;
SELECT PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'YEAR')
INTO l_year
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
l_tab_per_abs_rec.DELETE;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
SELECT *
FROM pay_action_information pai
WHERE action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYDKASORA'
AND action_information4 = 'HDR';
SELECT pai.*
FROM
pay_payroll_actions ppa,
Pay_assignment_actions paa,
pay_action_information pai
WHERE
ppa.payroll_action_id = p_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKASORA'
AND pai.action_information6 = 'ABS_DETAILS'
AND pai.action_information3 = to_char(p_payroll_action_id)
AND pai.tax_unit_id = p_tax_unit_id;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id between fcrs1.request_id and fcrs.request_id
AND ppa.request_id = fcrs1.request_id;
xml_tab(l_counter).TagName :='UPDATE_DATE';