eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_DK_SICKNESS_DP201 SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19


SELECT
	pdb.defined_balance_id
INTO
	l_defined_balance_id
FROM
	pay_defined_balances      pdb
	,pay_balance_types         pbt
	,pay_balance_dimensions    pbd
WHERE
	pbd.database_item_suffix = p_dbi_suffix
	AND    (pbd.legislation_code = 'DK' OR pbt.business_group_id = p_business_group_id)
	AND    pbt.balance_name = p_balance_name
	AND    (pbt.legislation_code = 'DK' OR pbt.business_group_id = p_business_group_id)
	AND    pdb.balance_type_id = pbt.balance_type_id
	AND    pdb.balance_dimension_id = pbd.balance_dimension_id
	AND    (pdb.legislation_code = 'DK' OR pbt.business_group_id = p_business_group_id);

Line: 52


SELECT
*
FROM
(
SELECT
	paa1.assignment_action_id
FROM
	pay_assignment_actions paa1
	,pay_payroll_actions ppa1
	,pay_assignment_actions paa2
	,pay_payroll_actions ppa2
	,pay_action_interlocks  pai
	,pay_payrolls_f ppf
WHERE
	paa1.assignment_id = p_assignment_id
	AND paa1.action_status = 'C'
	AND paa1.payroll_action_id = ppa1.payroll_action_id
	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
	AND ppa1.date_earned <= p_effective_date

	--for prepayments
	AND    paa2.action_status           = 'C' -- Completed
	AND    paa2.assignment_action_id    = pai.locking_action_id
	AND    paa2.payroll_action_id       = ppa2.payroll_action_id
	AND    ppa2.action_type            IN ('P','U')
	AND    ppa2.date_earned <= p_effective_date

	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id

	AND ppf.payroll_id = ppa1.payroll_id
	ORDER BY ppa1.date_earned desc)
WHERE
	ROWNUM=1;

Line: 90


SELECT
	SUM (pay_balance_pkg.get_value(
	     get_defined_balance_id(pbt.balance_name,'_ASG_PTD',p_business_group_id)
		,asg_act_id)) - pay_balance_pkg.get_value(
	     get_defined_balance_id('Employee AMB Deduction','_ASG_PTD',p_business_group_id)
		,asg_act_id) pay_value
FROM
	pay_balance_types pbt
	,pay_balance_categories_f pbcf
WHERE
	pbcf.legislation_code = 'DK'
	AND pbcf.category_name = 'Salary Reporting'
	AND pbcf.balance_category_id = pbt.balance_category_id;

Line: 132


SELECT
	paaf.frequency
	,paaf.normal_hours
	,hsck.segment10 default_work_pattern
 FROM
	 per_all_assignments_f paaf
	,hr_soft_coding_keyflex hsck
WHERE
	paaf.assignment_id = p_assignment_id
	AND paaf.assignment_status_type_id = 1
	AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;

Line: 147


SELECT
	hoi1.org_information4
	,hoi1.org_information3
	,hoi2.org_information1
FROM
	hr_organization_information hoi1
	,hr_organization_information hoi2
WHERE
	hoi1.organization_id = p_leg_emp_id
	AND hoi2.organization_id = hoi1.organization_id
	AND hoi1.org_information_context =  'DK_EMPLOYMENT_DEFAULTS'
	AND hoi2.org_information_context(+) =  'DK_HOLIDAY_ENTITLEMENT_INFO';

Line: 217


SELECT
/*	papf.first_name first_name
	,papf.middle_names middle_name
	,papf.last_name last_name*/
	/*Bug 5049222 fix- Employee name formatting*/
	--SUBSTR (papf.first_name,1,40)||NVL2(papf.middle_names,','||SUBSTR(papf.middle_names,1,40),NULL)||','||SUBSTR(papf.last_name,1,40) ename
	/*Reopened Bug 5049222 fix*/
     SUBSTR (papf.last_name,1,40)||', '||SUBSTR(papf.first_name,1,40)||NVL2(papf.middle_names,' '||SUBSTR(papf.middle_names,1,40),NULL) ename
	,papf.national_identifier national_identifier
	,papf.start_date person_start_date
	,pa.address_line1 address_line1
	,pa.address_line2 address_line2
	,pa.postal_code postal_code
FROM
	per_all_people_f papf
	,per_addresses   pa
WHERE
	papf.person_id = p_person_id
	AND p_effective_date between papf.effective_start_date and papf.effective_end_date
	AND papf.current_employee_flag = 'Y'
	AND pa.person_id(+) = papf.person_id
	AND pa.primary_flag(+) = 'Y'
	AND p_effective_date BETWEEN pa.date_from(+) AND nvl(pa.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY'));

Line: 243


SELECT
	paaf.person_id person_id
	,paaf.assignment_id asg_id
	,paaf.assignment_number asg_num
	,pj.name job_title
	,MIN(paaf.effective_start_date) asg_start_date
	,MAX(paaf.effective_end_date) asg_end_date
	,peef.effective_start_date absence_start_date
	,peef.effective_end_date absence_end_date
	,peevf.screen_entry_value absence_reason
	,paaf.payroll_id payroll_id
	,ppf.period_type period_type
FROM
	per_all_assignments_f  paaf
	,pay_element_entries_f peef
	,pay_element_types_f petf
	,per_jobs pj
	,pay_element_entry_values_f peevf
	,pay_input_values_f pivf
	,pay_payrolls_f ppf
WHERE
    paaf.person_id = p_person_id
	AND paaf.assignment_id = NVL(p_assignment_id,paaf.assignment_id)
	AND paaf.assignment_status_type_id = 1
	AND petf.element_name LIKE 'Absent Sick' -- To check for the Sickness element
	AND petf.legislation_code ='DK'
    AND peef.element_type_id = petf.element_type_id
    AND peef.assignment_id = paaf.assignment_id

    AND peef.effective_start_date BETWEEN l_start_date and l_end_date

	AND pivf.element_type_id = peef.element_type_id
	AND pivf.NAME = 'Absent Reason'
	AND peevf.element_entry_id= peef.element_entry_id
	AND peevf.input_value_id = pivf.input_value_id
	AND pj.job_id(+) = paaf.job_id
	AND ppf.payroll_id(+) = paaf.payroll_id
	GROUP BY paaf.person_id
	,paaf.assignment_id
	,paaf.assignment_number
	,pj.name
	,peef.effective_start_date
	,peef.effective_end_date
	,peevf.screen_entry_value
	,paaf.payroll_id
    ,ppf.period_type;

Line: 292


SELECT
--	haou.organization_id le_id
	 SUBSTR(haou.NAME,1,30) le_name
	,substr(hl.address_line_1,1,30) le_address1
	,decode(hl.postal_code,NULL,' ',','||substr(hl.postal_code,1,5)) le_postalcode
	,substr(p_le_phone_number,1,30) le_phone
	,substr(p_le_email_addr,1,30) le_email
FROM
--	per_all_assignments_f paaf
--	,hr_soft_coding_keyflex hsck
	hr_all_organization_units haou
	,hr_locations hl
WHERE
--	paaf.assignment_id = p_assignment_id
--	AND l_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
--	AND paaf.assignment_status_type_id = 1
--	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
	haou.organization_id = p_le_id
	AND hl.location_id(+) = haou.location_id;

Line: 314


SELECT
hoi.org_information1 le_cvr_num
FROM
hr_organization_information hoi
WHERE
hoi.organization_id = p_le_id
AND hoi.org_information_context = 'DK_LEGAL_ENTITY_DETAILS';

Line: 326


SELECT
    ptp.start_date
FROM
    per_time_periods ptp
WHERE
    ptp.payroll_id = p_payroll_id
    AND p_absence_start_date BETWEEN ptp.start_date AND ptp.end_Date;

Line: 336


SELECT
    NVL(hsck.segment18,'N') section28
   	,hsck.segment4 employee_group
	,hsck.segment1 le_id
	,hsck.segment10 asg_work_pattern
FROM
	per_all_assignments_f paaf
	,hr_soft_coding_keyflex hsck
WHERE
	paaf.assignment_id = p_assignment_id
	AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;

Line: 351


SELECT
    hoi.org_information1
FROM
    hr_organization_information hoi
WHERE
    hoi.organization_id = p_le_id
    AND hoi.org_information_context = 'DK_SICKPAY_DEFAULTS'
    AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2) and fnd_date.canonical_to_date(hoi.org_information3)
ORDER  BY hoi.org_information2;

Line: 362


SELECT
    org_information1 le_work_pattern
FROM
    hr_organization_information
WHERE
    organization_id = p_org_id
    AND org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';

Line: 372


 SELECT
       fgf.global_value
 FROM
       ff_globals_f fgf
 WHERE
 fgf.legislation_code = 'DK'
 AND fgf.GLOBAL_NAME = p_global_name
 AND p_effective_date BETWEEN fgf.effective_start_date AND fgf.effective_end_date;