[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Function to display the details of the selected employee for T4/T4A Report Type
********************************************************************************/
FUNCTION formated_detail_string(
p_output_file_type in VARCHAR2
,p_year VARCHAR2
,p_gre VARCHAR2
,p_employee_name VARCHAR2
,p_employee_sin VARCHAR2
,p_employee_number VARCHAR2
) RETURN VARCHAR2
IS
lv_format1 VARCHAR2(22000);
Function to display the details of the selected employee for RL1/RL2 Report Type
********************************************************************************/
FUNCTION formated_detail_string_rl(
p_output_file_type in VARCHAR2
,p_year VARCHAR2
,p_pre VARCHAR2
,p_employee_name VARCHAR2
,p_employee_sin VARCHAR2
,p_employee_number VARCHAR2
) RETURN VARCHAR2
IS
lv_format1 VARCHAR2(22000);
Procedure to display message if no employees are selected
*************************************************************************/
PROCEDURE formated_zero_count(output_file_type VARCHAR2)
IS
lvc_message VARCHAR2(200);
Procedure to display the name of the assignment set to which the selected
assignments are added
************************************************************************/
PROCEDURE formated_assign_count(assignment_set_name in varchar2,
assignment_set_id in number,
record_count in number,
assign_set_created in number,
output_file_type in varchar2)
IS
lvc_message1 VARCHAR2(400);
Name: select_employee
Description: The input parameters for the procedure are
Date,GRE/PRE,Assignment Set and output file type from
the concurrent program. The procedure identifies the
missing assignments , adds them to the assignment
set entered and generates the report in the specified
format.
*****************************************************/
PROCEDURE select_employee(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_effective_date IN VARCHAR2,
p_bus_grp IN NUMBER,
p_report_type IN VARCHAR2,
p_dummy1 IN VARCHAR2,
p_gre_id IN NUMBER,
p_dummy2 IN VARCHAR2,
p_pre_id IN NUMBER,
p_assign_set IN VARCHAR2,
p_output_file_type IN VARCHAR2)
IS
/* Cursor to select primary assignments that are not archived by the
YEPP in the given year for the given GRE */
CURSOR c_missing_assignments(cp_effective_date date,
cp_bus_grp number,
cp_report_type varchar2,
cp_tax_unit_id number) is
SELECT DISTINCT asg.assignment_id ass_id
FROM per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_people_f ppf
WHERE ppa.effective_date BETWEEN cp_effective_date
AND add_months(cp_effective_date, 12) - 1
AND ppa.action_type in ('R','Q','V','B','I')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.assignment_id = asg.assignment_id
AND ppa.business_group_id+0 = cp_bus_grp
AND ppa.business_group_id = asg.business_group_id +0
AND asg.person_id = ppf.person_id
AND asg.assignment_type = 'E'
AND ppa.effective_date between asg.effective_start_date AND asg.effective_end_date
AND ppa.effective_date between ppf.effective_start_date AND ppf.effective_end_date
AND NOT EXISTS ( SELECT 1
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1
WHERE ppa1.report_type = cp_report_type
AND ppa1.report_qualifier = 'CAEOY'
AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
AND get_parameter('TRANSFER_GRE',ppa1.legislative_parameters) = to_char(cp_tax_unit_id)
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.business_group_id+0 = cp_bus_grp
AND paa1.serial_number = to_char(ppf.person_id))
ORDER BY asg.assignment_id DESC;
/* Cursor to select primary assignments that are not archived by the
YEPP in the given year for the given PRE of Report Type RL1*/
CURSOR c_missing_assignments_rl1(cp_effective_date date,
cp_bus_grp number,
cp_report_type varchar2,
cp_pre_id number) is
SELECT DISTINCT ASG.assignment_id ass_id
FROM per_all_assignments_f ASG,
pay_all_payrolls_f PPY,
hr_soft_coding_keyflex SCL
WHERE ASG.business_group_id + 0 = cp_bus_grp
AND ASG.assignment_type = 'E'
AND ASG.effective_start_date <= add_months(cp_effective_date, 12) - 1
AND ASG.effective_end_date >= cp_effective_date
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND (
(rtrim(ltrim(SCL.segment1)) in
(select to_char(hoi.organization_id)
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = to_char(cp_pre_id) ))
or
(rtrim(ltrim(SCL.segment11)) in
(select to_char(hoi.organization_id)
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = to_char(cp_pre_id) ))
)
AND PPY.payroll_id = ASG.payroll_id
AND EXISTS (select 'X'
from pay_action_contexts pac, ff_contexts fc
where pac.assignment_id = asg.assignment_id
and pac.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE'
and pac.context_value = 'QC')
AND NOT EXISTS (SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.report_type = cp_report_type
AND ppa.report_qualifier = 'CAEOYRL1'
AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
AND get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters) = to_char(cp_pre_id)
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id+0 = cp_bus_grp
AND paa.serial_number = to_char(ASG.person_id))
ORDER BY asg.assignment_id DESC;
/* Cursor to select primary assignments that are not archived by the
YEPP in the given year for the given PRE of Report Type RL2 */
CURSOR c_missing_assignments_rl2(cp_effective_date date,
cp_bus_grp number,
cp_report_type varchar2,
cp_pre_id number) is
SELECT DISTINCT ASG.assignment_id ass_id
FROM per_all_assignments_f ASG,
pay_all_payrolls_f PPY,
hr_soft_coding_keyflex SCL
WHERE ASG.business_group_id + 0 = cp_bus_grp
AND ASG.assignment_type = 'E'
AND ASG.effective_start_date <= add_months(cp_effective_date, 12) - 1
AND ASG.effective_end_date >= cp_effective_date
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND rtrim(ltrim(SCL.segment12)) in
(select to_char(hoi.organization_id)
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = to_char(cp_pre_id)
and hoi.org_information5 = 'T4A/RL2')
AND PPY.payroll_id = ASG.payroll_id
AND EXISTS (select 'X' from pay_action_contexts pac, ff_contexts fc
where pac.assignment_id = asg.assignment_id
and pac.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE'
and pac.context_value = 'QC')
AND NOT EXISTS (SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.report_type = cp_report_type
AND ppa.report_qualifier = 'CAEOYRL2'
AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
AND get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)= to_char(cp_pre_id)
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id+0 = cp_bus_grp
AND paa.serial_number = to_char(ASG.person_id))
ORDER BY asg.assignment_id DESC;
/* Cursor to check if the assignment selected has atleast a single
non zero run result value with an input value of Money in the
entered year */
CURSOR c_non_zero_run_result(cp_business_group number,
cp_assignment_id number,
cp_effective_date date,
cp_tax_unit_id number) is
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_all_payrolls_f ppf
WHERE ppa.business_group_id+0 = cp_business_group
AND paa.assignment_id = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
AND prr.assignment_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('R','B','Q','V','I')
AND ppa.effective_date between cp_effective_date
AND add_months(cp_effective_date, 12) - 1
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date between ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.payroll_id > 0
AND prrv.run_result_id = prr.run_result_id
AND prrv.result_value <> '0'
AND piv.input_value_id = prrv.input_value_id
AND ppa.effective_date between piv.effective_Start_date
AND piv.effective_end_date
AND piv.uom = 'M'
AND EXISTS (SELECT '1'
FROM pay_balance_feeds_f pbf
WHERE piv.input_value_id = pbf.input_value_id
AND ppa.effective_date BETWEEN pbf.effective_Start_date
AND pbf.effective_end_date));
SELECT name
FROM hr_all_organization_units_tl
WHERE organization_id = p_org_id
AND language = userenv('LANG');
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = c_assign_id
AND business_group_id+0 = p_bus_grp;
SELECT assignment_number
FROM per_all_assignments_f
WHERE assignment_id = c_assign_id;
SELECT full_name,national_identifier
FROM per_all_people_f
WHERE person_id = c_person_id;
SELECT hr_assignment_sets_s.nextval
FROM dual;
SELECT assignment_set_id
FROM hr_assignment_sets
WHERE assignment_set_name=assign_set_name;
SELECT 1
FROM hr_assignment_set_amendments
WHERE assignment_set_id = c_assignment_set_id
AND assignment_id = c_assignment_id;
SELECT hoi.organization_id gre_id
FROM hr_organization_information hoi,
hr_all_organization_units hou
WHERE hoi.org_information_context = 'Canada Employer Identification'
AND hoi.org_information2 = to_char(cp_pre_id)
AND hou.business_group_id = p_bus_grp
AND hou.organization_id = hoi.organization_id;
/* Inserting the New Assignment set into hr_assignment_sets table */
hr_assignment_sets_pkg.insert_row(row_id,
lv_assignment_set_id,
p_bus_grp,
lv_payroll_id,
p_assign_set,
lv_formula_id);
hr_assignment_set_amds_pkg.insert_row(row_id,lv_assn_id,lv_assignment_set_id,'I');
hr_assignment_set_amds_pkg.insert_row(row_id,lv_assn_id,lv_assignment_set_id,'I');
END LOOP; /*loop for checking the nonzero run_result values for selected assignments*/
UPDATE fnd_concurrent_requests
SET output_file_type = 'HTML'
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
END select_employee;