[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
SELECT PAY_DK_PR_ASG.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
,PAY_DK_PR_ASG.GET_PARAMETER(legislative_parameters,'ASSIGNMENT_SET_ID')
,PAY_DK_PR_ASG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,fnd_date.canonical_to_date(PAY_DK_PR_ASG.GET_PARAMETER(legislative_parameters,'FROM_DATE'))
,fnd_date.canonical_to_date(PAY_DK_PR_ASG.GET_PARAMETER(legislative_parameters,'TO_DATE'))
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
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;
SELECT count(*)
FROM
pay_action_information
WHERE
action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYDKPRASGA'
AND action_context_id = pactid;
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 has.assignment_set_name INTO
l_assignment_set_name
FROM
hr_assignment_sets has
WHERE has.assignment_set_id=l_assignment_set_id;
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;
SELECT haou.name INTO l_organization_name
FROM hr_all_organization_units haou
WHERE haou.organization_id=l_business_group_id;
/* Cursor to select data based on the i/p parameter assignment set*/
CURSOR csr_assignment_set
(p_payroll_action_id NUMBER,
p_start_person NUMBER,
p_end_person NUMBER)
IS
SELECT
asg_id
,asg_act_id
,tax_unit_id
,payroll_id
,payroll_name
,ele_type_id
,ele_name
,ele_proc_prior
,input_value_id
,date_earned
,costed_code
,pay_value
,balance_amount
FROM
(SELECT
paa1.assignment_id asg_id
,paa1.assignment_action_id asg_act_id
,paa1.tax_unit_id tax_unit_id
,ppf.payroll_id payroll_id
,ppf.payroll_name payroll_name
,petf.element_type_id ele_type_id
,NVL(petf.reporting_name,petf.element_name) ele_name
,petf.processing_priority ele_proc_prior
,pivf.input_value_id input_value_id
,TO_CHAR (ppa1.date_earned,'DD-MON-YYYY') date_earned
,pay_dk_pr_asg.costed_code(prr.run_result_id,pivf.input_value_id) costed_code
,pay_balance_pkg.get_value(pay_dk_pr_asg.get_defined_balance_id
(pivf.input_value_id,'_ASG_RUN')
,paa1.assignment_action_id) pay_value
,pay_balance_pkg.get_value(pay_dk_pr_asg.get_defined_balance_id
(pivf.input_value_id,'_ASG_YTD')
,paa1.assignment_action_id) balance_amount
FROM
hr_assignment_set_amendments hasa
,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_action_interlocks pai
,pay_run_results prr
,pay_input_values_f pivf
,pay_element_types_f petf
WHERE
paaf.person_id BETWEEN p_start_person AND p_end_person
AND paaf.business_group_id = l_business_group_id
AND hasa.assignment_set_id = l_assignment_set_id
AND paaf.assignment_status_type_id = 1 -- to check for active assignment
AND paaf.assignment_id = hasa.assignment_id
AND ppf.payroll_id = NVL(l_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND paa1.assignment_id = paaf.assignment_id
AND paa1.action_status = 'C' -- Completed
--for payroll actions
AND ppa1.payroll_action_id = paa1.payroll_action_id
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
--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 = pivf.element_type_id
AND pivf.name='Pay Value'
AND pivf.element_type_id = petf.element_type_id
AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
AND petf.element_name <> 'Tax'
AND petf.element_name <> 'Mileage Claim') --To exclude Tax and Mileage Claim
GROUP BY
asg_id
,asg_act_id
,tax_unit_id
,payroll_name
,payroll_id
,ele_type_id
,ele_name
,ele_proc_prior
,input_value_id
,date_earned
,costed_code
,pay_value
,balance_amount
ORDER BY asg_act_id;
/* Cursor to select data if assignmnet set is null-Bug fix 4968059*/
CURSOR csr_all_assignments
(p_payroll_action_id NUMBER,
p_start_person NUMBER,
p_end_person NUMBER)
IS
SELECT
asg_id
,asg_act_id
,tax_unit_id
,payroll_id
,payroll_name
,ele_type_id
,ele_name
,ele_proc_prior
,input_value_id
,date_earned
,costed_code
,pay_value
,balance_amount
FROM
(SELECT
paa1.assignment_id asg_id
,paa1.assignment_action_id asg_act_id
,paa1.tax_unit_id tax_unit_id
,ppf.payroll_id payroll_id
,ppf.payroll_name payroll_name
,petf.element_type_id ele_type_id
,NVL(petf.reporting_name,petf.element_name) ele_name
,petf.processing_priority ele_proc_prior
,pivf.input_value_id input_value_id
,TO_CHAR (ppa1.date_earned,'DD-MON-YYYY') date_earned
,pay_dk_pr_asg.costed_code(prr.run_result_id,pivf.input_value_id) costed_code
,pay_balance_pkg.get_value(pay_dk_pr_asg.get_defined_balance_id
(pivf.input_value_id,'_ASG_RUN')
,paa1.assignment_action_id) pay_value
,pay_balance_pkg.get_value(pay_dk_pr_asg.get_defined_balance_id
(pivf.input_value_id,'_ASG_YTD')
,paa1.assignment_action_id) balance_amount
FROM
-- hr_assignment_set_amendments hasa
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_action_interlocks pai
,pay_run_results prr
,pay_input_values_f pivf
,pay_element_types_f petf
WHERE
paaf.person_id BETWEEN p_start_person AND p_end_person
AND paaf.business_group_id = l_business_group_id
AND paaf.assignment_status_type_id = 1 -- to check for active assignment
AND ppf.payroll_id = NVL(l_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND paa1.assignment_id = paaf.assignment_id
AND paa1.action_status = 'C' -- Completed
--for payroll actions
AND ppa1.payroll_action_id = paa1.payroll_action_id
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
--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 = pivf.element_type_id
AND pivf.name='Pay Value'
AND pivf.element_type_id = petf.element_type_id
AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
AND petf.element_name <> 'Tax'
AND petf.element_name <> 'Mileage Claim') --To exclude Tax and Mileage Claim
GROUP BY
asg_id
,asg_act_id
,tax_unit_id
,payroll_name
,payroll_id
,ele_type_id
,ele_name
,ele_proc_prior
,input_value_id
,date_earned
,costed_code
,pay_value
,balance_amount
ORDER BY asg_act_id;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
SELECT
TO_NUMBER (ACTION_INFORMATION_ID) action_info_id
FROM
pay_action_information
WHERE
ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND ACTION_INFORMATION1 = 'PYDKPRASGA'
AND ACTION_CONTEXT_TYPE = 'AAP'
AND ACTION_CONTEXT_ID = p_assignment_action_id
AND EFFECTIVE_DATE = p_effective_date ;
SELECT
haou.NAME organization_name
,papf.employee_number emp_num
-- ,papf.order_name ename
/*Name format- last name, first name middle name*/
,SUBSTR (papf.last_name,1,90)||', '||SUBSTR(papf.first_name,1,90)||NVL2(papf.middle_names,' '||papf.middle_names,NULL) ename
,pj.name job_title
,paaf.assignment_number asg_num
FROM
per_all_people_f papf
,per_all_assignments_f paaf
,pay_assignment_actions paa
,per_jobs pj
,hr_all_organization_units haou
WHERE
paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND papf.person_id=paaf.person_id
and paaf.job_id=pj.job_id(+)
AND paaf.organization_id = haou.organization_id
ORDER BY papf.person_id;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id ;
pay_action_information_api.update_action_information (
p_action_information_id => csr_get_act_info_rec.action_info_id -- in parameter
,p_object_version_number => l_ovn -- in out parameter
,p_action_information12 => rec_emp_details.organization_name --organization name)
,p_action_information13 => rec_emp_details.emp_num -- employee number
,p_action_information14 => rec_emp_details.ename -- employee name
,p_action_information15 => rec_emp_details.asg_num -- assignment number
,p_action_information16 => rec_emp_details.job_title -- job title
);
SELECT
pai1.action_information3||' to '||pai1.action_information4 report_period
,pai2.action_information2 payroll_id
,pai2.action_information5 payroll_name
,pai2.action_information12 org_name
,pai2.action_information13 emp_numn
,pai2.action_information14 ename
,pai2.assignment_id asg_id
,pai2.action_information15 asg_num
,pai2.action_information16 job_title
,pai2.action_information6 ele_type_id
,pai2.action_information7 ele_proc_prior
,pai2.action_information8 ele_name
,pai2.action_information10 date_earned
,pai2.action_information11 costed_code
,fnd_number.canonical_to_number(pai2.action_information17) pay_value
,fnd_number.canonical_to_number(pai2.action_information18) balance_amount
FROM
pay_action_information pai1,pay_action_information pai2
WHERE
-- Removing to_char for bug fix 5231458
-- Added to_char for bug fix 5236372
--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 pai2.action_information3 = TO_CHAR(pai1.action_context_id)
--AND pai2.action_information3 = pai1.action_context_id
AND pai2.action_information_category='EMEA REPORT INFORMATION'
ORDER BY
TO_NUMBER (pai2.action_information2),
TO_NUMBER (pai2.action_information13),
pai2.action_information15,
TO_NUMBER (pai2.action_information7),
TO_NUMBER (pai2.action_information6),
fnd_date.string_to_date(pai2.action_information10,'DD-MON-YYYY');
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;