APPS.PAY_DK_PR_ELE SQL Statements

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

Line: 76

SELECT
	fnd_date.canonical_to_date(PAY_DK_PR_ELE.GET_PARAMETER(legislative_parameters,'FROM_DATE'))
	,fnd_date.canonical_to_date(PAY_DK_PR_ELE.GET_PARAMETER(legislative_parameters,'TO_DATE'))
	,PAY_DK_PR_ELE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
	,PAY_DK_PR_ELE.GET_PARAMETER(legislative_parameters,'ELEMENT_SET_ID')
	,PAY_DK_PR_ELE.GET_PARAMETER(legislative_parameters,'ARCHIVE')
	,effective_date
	,business_group_id
FROM
	pay_payroll_actions
WHERE
	payroll_action_id = p_payroll_action_id;

Line: 112

SELECT
    defined_balance_id
INTO
	l_defined_balance_id
FROM
(
SELECT
	pdb.defined_balance_id 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 = l_business_group_id)
    AND     pbt.input_value_id = p_input_value_id
	AND    (pbt.legislation_code = 'DK' OR pbt.business_group_id = l_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 = l_business_group_id)
)
WHERE ROWNUM < 2;

Line: 151

SELECT
pcak.concatenated_segments cost_code
FROM
pay_costs pc
,pay_cost_allocation_keyflex pcak
WHERE
	NVL (pc.distributed_input_value_id, pc.input_value_id) = p_input_value_id
	AND pc.run_result_id = p_run_result_id
	AND pc.balance_or_cost       = 'C'
	AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id;

Line: 186

select count(*)
from   pay_action_information
where  action_information_category = 'EMEA REPORT DETAILS'
and    action_information1         = 'PYDKPRELEA'
and    action_context_id           = pactid;

Line: 199

 SELECT
	SUM (pay_balance_pkg.get_value(pay_dk_pr_ele.get_defined_balance_id
        (pivf.input_value_id,'_ASG_RUN')
        	,paa1.assignment_action_id)) ele_ytd
FROM
	per_all_assignments_f  paaf
	,pay_all_payrolls_f ppf
	,pay_assignment_actions paa1
	,pay_assignment_actions paa2
	,pay_payroll_actions ppa1
	,pay_payroll_actions ppa2
	,pay_run_results prr
	,pay_input_values_f pivf
	,pay_element_types_f petf
	,pay_action_interlocks  pai

WHERE
	paaf.business_group_id = l_business_group_id -- BG Check
	AND ppf.payroll_id = p_payroll_id
	AND petf.element_type_id = p_ele_type_id
	AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
	AND  paaf.payroll_id = ppf.payroll_id
	AND  paaf.assignment_status_type_id = 1 -- to check for active assignments
	AND paa1.assignment_id = paaf.assignment_id
	AND paa1.action_status = 'C'  -- Completed

	--for payroll actions
	AND ppa1.date_earned between p_from_date and p_to_date --date condition
	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
	AND ppa1.payroll_action_id = paa1.payroll_action_id

	--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 between p_from_date and p_to_date

	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
	/*date check*/
	AND    ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date
	AND    ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date
	AND    ppa2.date_earned between pivf.effective_start_date and pivf.effective_end_date

	-- for run results
	AND prr.assignment_action_id = paa1.assignment_action_id

	AND petf.element_type_id = prr.element_type_id
	AND pivf.element_type_id = prr.element_type_id
	AND pivf.name='Pay Value' ;

Line: 253

SELECT
	payroll_name
	,payroll_id
	,ele_type_id
	,ele_proc_prior
	,ele_name
	,input_value_id
	,date_earned
	,costed_code
	,SUM(pay_value) pay_value
--	,SUM(balance_amount) balance_amount
 FROM
( SELECT
	paa1.assignment_action_id	 asg_act_id
	,ppf.payroll_name payroll_name
	,ppf.payroll_id payroll_id
	,petf.element_type_id ele_type_id
	,petf.processing_priority ele_proc_prior
        ,NVL(petf.reporting_name,petf.element_name) ele_name
	,pay_dk_pr_ele.costed_code(prr.run_result_id,pivf.input_value_id) costed_code
	,pivf.input_value_id  input_value_id
	,to_char(ppa1.date_earned,'DD-MON-RRRR')    date_earned
	,pay_balance_pkg.get_value(pay_dk_pr_ele.get_defined_balance_id
        (pivf.input_value_id,'_ASG_RUN')
        	,paa1.assignment_action_id) pay_value
/*	,pay_balance_pkg.get_value(pay_dk_pr_ele.get_defined_balance_id
        (pivf.input_value_id,'_ASG_YTD'),
        	paa1.assignment_action_id) balance_amount*/
FROM
	per_all_assignments_f  paaf
	,pay_all_payrolls_f ppf
	,pay_assignment_actions paa1
	,pay_assignment_actions paa2
	,pay_payroll_actions ppa1
	,pay_payroll_actions ppa2
	,pay_run_results prr
	,pay_input_values_f pivf
	,pay_element_types_f petf
	,pay_action_interlocks  pai

WHERE
	paaf.business_group_id = l_business_group_id -- BG Check
	AND ppf.payroll_id = NVL(l_payroll_id,ppf.payroll_id)
	AND  paaf.payroll_id = ppf.payroll_id
	AND  paaf.assignment_status_type_id = 1 -- to check for active assignments
	AND paa1.assignment_id = paaf.assignment_id
	AND paa1.action_status = 'C'  -- Completed

	--for payroll actions
	AND ppa1.date_earned between l_from_date and l_to_date --date condition
	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
	AND ppa1.payroll_action_id = paa1.payroll_action_id

	--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 between l_from_date and l_to_date

	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
	/*date check*/
	AND    ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date
	AND    ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date
	AND    ppa2.date_earned between pivf.effective_start_date and pivf.effective_end_date

	-- for run results
	AND prr.assignment_action_id = paa1.assignment_action_id

	AND petf.element_type_id = prr.element_type_id
	AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
	AND petf.element_name <> 'Tax'    --To exclude Tax
    AND petf.element_name <> 'Mileage Claim' -- To exclude Mileage Claim
	AND pivf.element_type_id = prr.element_type_id
	AND pivf.name='Pay Value' )

GROUP BY
   	payroll_name
	,payroll_id
	,ele_type_id
	,ele_proc_prior
	,ele_name
	,input_value_id
	,date_earned
	,costed_code
ORDER BY payroll_id,ele_proc_prior,ele_type_id;

Line: 343

SELECT
	payroll_name
	,payroll_id
	,ele_type_id
	,ele_proc_prior
	,ele_name
	,input_value_id
	,date_earned
	,costed_code
	,SUM(pay_value) pay_value
--	,SUM(balance_amount) balance_amount
 FROM
( SELECT
	paa1.assignment_action_id	 asg_act_id
	,ppf.payroll_name payroll_name
	,ppf.payroll_id payroll_id
	,petf.element_type_id ele_type_id
	,petf.processing_priority ele_proc_prior
        ,NVL(petf.reporting_name,petf.element_name) ele_name
	,pay_dk_pr_ele.costed_code(prr.run_result_id,pivf.input_value_id) costed_code
	,pivf.input_value_id  input_value_id
	,to_char(ppa1.date_earned,'DD-MON-RRRR')    date_earned
	,pay_balance_pkg.get_value(pay_dk_pr_ele.get_defined_balance_id
        (pivf.input_value_id,'_ASG_RUN')
        	,paa1.assignment_action_id) pay_value
/*	,pay_balance_pkg.get_value(pay_dk_pr_ele.get_defined_balance_id
        (pivf.input_value_id,'_ASG_YTD'),
        	paa1.assignment_action_id) balance_amount*/
FROM

	per_all_assignments_f  paaf
        ,pay_element_set_members pesm
	,pay_all_payrolls_f ppf
	,pay_assignment_actions paa1
	,pay_assignment_actions paa2
	,pay_payroll_actions ppa1
       ,pay_payroll_actions ppa2
	,pay_run_results prr
	,pay_input_values_f pivf
	,pay_element_types_f petf
	,pay_action_interlocks  pai

WHERE
	paaf.business_group_id = l_business_group_id -- BG Check
	AND pesm.element_set_id = l_element_set_id
	AND ppf.payroll_id = NVL(l_payroll_id,ppf.payroll_id)
	AND  paaf.payroll_id = ppf.payroll_id
	AND  paaf.assignment_status_type_id = 1 -- to check for active assignments
	AND paa1.assignment_id = paaf.assignment_id
	AND paa1.action_status = 'C'  -- Completed

	--for payroll actions
	AND ppa1.date_earned between l_from_date and l_to_date --date condition
	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
	AND ppa1.payroll_action_id = paa1.payroll_action_id

	--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 between l_from_date and l_to_date

	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
	/*date check*/
	AND    ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date
	AND    ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date
	AND    ppa2.date_earned between pivf.effective_start_date and pivf.effective_end_date

	-- for run results
	AND prr.assignment_action_id = paa1.assignment_action_id

	AND prr.element_type_id = pesm.element_type_id
	AND petf.element_type_id = prr.element_type_id
	AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
	AND petf.element_name <> 'Tax'    --To exclude Tax
    AND petf.element_name <> 'Mileage Claim' -- To exclude Mileage Claim
	AND pivf.element_type_id = prr.element_type_id
	AND pivf.name='Pay Value' )

GROUP BY
   	payroll_name
	,payroll_id
	,ele_type_id
	,ele_proc_prior
	,ele_name
	,input_value_id
	,date_earned
	,costed_code
ORDER BY payroll_id,ele_proc_prior,ele_type_id;

Line: 451

 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';

Line: 483

	SELECT pes.element_set_name INTO
		l_element_set_name
	FROM
		pay_element_sets pes
	WHERE pes.element_set_id=l_element_set_id;

Line: 495

	SELECT ppf.payroll_name INTO l_payroll_name
	FROM
	-- Replaced view  pay_payrolls_f with table pay_all_payrolls_f for bug fix 5231458
	--pay_payrolls_f ppf
	pay_all_payrolls_f ppf
	WHERE ppf.payroll_id=l_payroll_id;

Line: 508

	SELECT haou.name INTO l_business_group_name
	FROM hr_all_organization_units haou
	WHERE haou.organization_id=l_business_group_id;

Line: 700

SELECT
	pai1.action_information3||' to '||pai1.action_information4 report_period
	,pai2.action_information2 payroll_id
	,pai2.action_information3 payroll_name
	,pai2.action_information4 ele_type_id
	,pai2.action_information5 ele_proc_prior
	,pai2.action_information6 ele_name
	,pai2.action_information7 date_earned
	,pai2.action_information8 costed_code
	,fnd_number.canonical_to_number(pai2.action_information9) pay_value
	,fnd_number.canonical_to_number(pai2.action_information10) balance_amount

FROM
	pay_action_information pai1,
	pay_action_information pai2
WHERE

        -- Removing to_char for bug fix 5231458
	--TO_CHAR(pai1.action_context_id)=l_payroll_action_id
        pai1.action_context_id = l_payroll_action_id
	AND pai1.action_information_category='EMEA REPORT DETAILS'
	--AND TO_CHAR(pai2.action_context_id)= l_payroll_action_id
	AND pai2.action_context_id = l_payroll_action_id
	AND pai2.action_information_category='EMEA REPORT INFORMATION'
ORDER BY
	TO_NUMBER(pai2.action_information2)
	,TO_NUMBER(pai2.action_information5)
	,TO_NUMBER(pai2.action_information4)
	,fnd_date.string_to_date(pai2.action_information7,'DD-MON-RRRR');

Line: 733

	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;