APPS.PAY_DK_SICKNESS_DP202 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: 53

SELECT
*
FROM
(
SELECT
	paa1.assignment_action_id,ppf.period_type
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 IN ('C','S') -- 10229494
	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           IN ('C','S') -- 10229494
	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: 88

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: 139

SELECT
*
FROM
(
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
FROM
     per_all_people_f papf
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')
GROUP BY ename,national_identifier;

Line: 163

SELECT
    pa.address_line1 address_line1
    ,pa.address_line2 address_line2
    ,pa.postal_code postal_code
FROM
    per_addresses   pa
WHERE
    pa.person_id = p_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: 177

SELECT
	paaf.person_id person_id
    ,paaf.assignment_id asg_id
	,paaf.assignment_number asg_num
	,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
	,paaf.payroll_id payroll_id
FROM
	per_all_assignments_f  paaf
	,pay_element_types_f petf
	,pay_element_entries_f peef
WHERE
    paaf.person_id = p_person_id
	AND paaf.assignment_id = NVL(p_assignment_id,paaf.assignment_id)
--	AND l_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
	AND paaf.assignment_status_type_id = 1
	AND petf.element_name LIKE 'Absent Sick' -- To check for the Sickness element
--	AND l_run_date BETWEEN petf.effective_start_date AND petf.effective_end_date

	AND peef.assignment_id = paaf.assignment_id
	AND peef.element_type_id = petf.element_type_id
    AND peef.effective_start_date BETWEEN l_start_date and l_end_date
    GROUP BY
    paaf.person_id
    ,paaf.assignment_id
    ,paaf.assignment_number
	,peef.effective_start_date
	,peef.effective_end_date
	,paaf.payroll_id;

Line: 213

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 p_effective_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
	AND haou.organization_id = hsck.segment1
	AND hl.location_id(+) = haou.location_id;

Line: 235

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: 247

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;