[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_STATSR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'SPAN_RPT')
,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,fnd_date.canonical_to_date(PAY_DK_STATSR_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 pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.legislation_code='DK'
AND pbt.balance_name = p_balance_name
AND pbd.legislation_code = 'DK'
AND pbd.database_item_suffix = p_balance_dim
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT pdb.defined_balance_id DBAL_ID
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_balance_categories_f pbc
WHERE pbc.category_name = p_balance_cat_name
AND pbt.balance_category_id = pbc.balance_category_id
AND pbd.legislation_code = 'DK'
AND pbd.database_item_suffix = p_balance_dim
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
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
,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 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
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 = 'PYDKSTATSA'
AND action_information4 = 'HDR';
SELECT tax_unit_id
,to_number(action_information3) PAYROLL_ID
,action_information8 LE_NAME
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 = 'PYDKSTATSA'
AND action_information4 = 'CHDR';
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 distinct
paaf.assignment_id ASG_ID
,ppf.payroll_name PAYROLL_NAME
,paaf.assignment_number ASSIGNMENT_NUMBER
,to_char(paaf.effective_start_date,'YYYYMMDD') ASG_START_DATE
-- Selecting assignments effective end date to fetch correct balance value - Bug 5003744
,paaf.effective_end_date ASG_END_DATE
,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
,scl.SEGMENT3 COND_OF_EMP
,scl.SEGMENT4 EMP_GRP
,scl.SEGMENT14 JOB_OCC_MKODE
,scl.SEGMENT15 JOB_STATUS_MKODE
,paaf.NORMAL_HOURS NORMAL_HOURS
,paaf.FREQUENCY FREQ
,scl.SEGMENT10 DEFAULT_WORK_PATT
,scl.SEGMENT11 HOURLY_ACCR_RATE
,scl.SEGMENT13 SAL_ALLOW_RATE
,decode(ppf.PERIOD_TYPE
,'Calendar Month','1'
,'Bi-Week' ,'2'
,'Week' ,'3'
,'Lunar Month' ,'4') PAYROLL_PERIOD /*Changes for Lunar Payroll */
,scl.SEGMENT16 SAL_BASIS_MKODE
,scl.SEGMENT17 TIME_OFF_LIEU
,paaf.hourly_salaried_code HOURLY_SALARIED_CODE
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
/* Modified for bug 5003744 - Start */
--AND p_date_to BETWEEN paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
-- Added for Multi Record nprasath
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'
/* Modified for bug 5003744 - End */
AND scl.segment1 = to_char(p_le_id)
ORDER BY asg_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);
SELECT PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'SPAN_RPT')
INTO l_span
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
/* Logic for selecting mkode0100 */
IF( nvl(csr_rec.cond_of_emp, rec_get_le_emp_dflts.cond_of_emp) IN('3','4')) THEN
l_mkode0100 := '2';
/* Logic for selecting mkode0200 */
IF( nvl(csr_rec.emp_grp, rec_get_le_emp_dflts.emp_grp) = '1') THEN
l_mkode0200 := '1';
/* Logic for selecting mkode0600 */
/* Bug 5030983 Fixes - Start */
/* For salary record mkode0600,if salary basis not in 81,82,83,84 and payroll period is weekly
and biweekly then get working hours balance and divide by 13, 26 or 52 based on report span. */
IF (l_span ='Q') THEN
l_dimension := '_ASG_LE_QTD';
/* Logic for selecting mkode0610 */
IF( nvl(csr_rec.default_work_patt,rec_get_hol_entit.default_work_patt) = '5DAY') THEN
l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_5DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
/* Logic for selecting l_mkode0620 */
/*Changes for Lunar Payroll */
/*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
IF l_hourly_salaried = 'H' THEN
l_mkode0620 := nvl(nvl(csr_rec.hourly_accr_rate,rec_get_hol_entit.hourly_accr_rate)
,PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_HOLIDAY_PAY_LEGSL_PERCENTAGE', l_date_to));
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 = 'PYDKSTATSA'
AND action_information4 = 'HDR';
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 = 'PYDKSTATSA'
AND action_information4 = 'CHDR';
SELECT *
FROM pay_action_information pai
WHERE action_context_type = 'AAP'
AND action_information3 = to_char(p_payroll_action_id)
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYDKSTATSA'
AND action_information6 ='PART1'
AND tax_unit_id = p_tax_unit_id
-- Add check on MKODE800 not being 91 or 92
AND action_information15 NOT IN('91','92');
SELECT *
FROM pay_action_information pai
WHERE action_context_type = 'AAP'
AND action_information3 = p_payroll_action_id
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYDKSTATSA'
AND action_information6 ='PART2'
AND tax_unit_id = p_tax_unit_id
AND action_context_id = p_action_context_id
AND effective_date = p_effective_date
AND assignment_id = p_assignment_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';