1 package pay_ca_rl2_can_mag AUTHID CURRENT_USER as
2 /* $Header: pycarl2cmg.pkh 120.1 2011/01/05 12:56:27 sneelapa noship $ */
3 /*
4 Name
5 pay_ca_rl2_can_mag
6
7 Purpose
8 The purpose of this package is to support the generation of magnetic tape RL2
9 cancellation for CA legislative requirements.
10 */
11
12 -- 'level_cnt' will allow the cursors to select function results,
13 -- whether it is a standard fuction such as to_char or a function
14 -- defined in a package (with the correct pragma restriction).
15
16 level_cnt NUMBER;
17
18 -- Used by Magnetic RL2 (RL2 format).
19 --
20 --
21
22 CURSOR mag_rl2_transmitter IS
23 SELECT distinct
24 'PAYROLL_ACTION_ID=P',
25 ppa.payroll_action_id
26 from
27 pay_payroll_actions ppa
28 where ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'));
29 /* SELECT distinct
30 'PAYROLL_ACTION_ID=P',
31 ppa1.payroll_action_id,
32 'PAYROLL_ACTION_ID=C',
33 ppa1.payroll_action_id
34 from
35 pay_payroll_actions ppa,
36 pay_payroll_actions ppa1,
37 pay_assignment_actions paa,
38 pay_assignment_actions paa1,
39 pay_action_interlocks int
40 where
41 ppa.payroll_action_id = paa.payroll_action_id
42 and ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
43 and int.locking_action_id = paa.assignment_action_id
44 and paa1.assignment_action_id = int.locked_action_id
45 and ppa1.payroll_action_id = paa1.payroll_action_id
46 and ppa1.report_type in ('RL2','CAEOY_RL2_AMEND_PP')
47 and ppa1.action_status = 'C';*/
48
49 CURSOR mag_rl2_employer IS
50 select
51 'PAYROLL_ACTION_ID=C',to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')),
52 'PAYROLL_ACTION_ID=P',to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
53 from dual;
54
55
56 CURSOR mag_rl2_employee IS
57 SELECT 'TRANSFER_ACT_ID=P',
58 paa.assignment_action_id
59 FROM
60 per_all_people_f ppf,
61 per_all_assignments_f paf,
62 pay_action_interlocks pai,
63 pay_assignment_actions paa,
64 pay_payroll_actions ppa,
65 pay_assignment_actions paa_mag
66 WHERE
67 ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
68 AND paa.payroll_action_id = ppa.payroll_action_id
69 AND pai.locking_action_id = paa.assignment_action_id
70 AND paf.assignment_id = paa.assignment_id
71 AND ppf.person_id = paf.person_id
72 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
73 between paf.effective_start_date and paf.effective_end_date
74 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
75 between ppf.effective_start_date and ppf.effective_end_date
76 AND paa_mag.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
77 AND paa_mag.assignment_action_id = pai.locked_action_id
78 ORDER BY
79 ppf.last_name,ppf.first_name,ppf.middle_names;
80
81
82 PROCEDURE get_report_parameters
83 (
84 p_pactid IN NUMBER,
85 p_year_start IN OUT NOCOPY DATE,
86 p_year_end IN OUT NOCOPY DATE,
87 p_report_type IN OUT NOCOPY VARCHAR2,
88 p_business_group_id IN OUT NOCOPY NUMBER,
89 p_legislative_param IN OUT NOCOPY VARCHAR2
90 );
91
92
93 PROCEDURE range_cursor (
94 p_pactid IN NUMBER,
95 p_sqlstr OUT NOCOPY VARCHAR2
96 );
97
98
99 PROCEDURE create_assignment_act(
100 p_pactid IN NUMBER,
101 p_stperson IN NUMBER,
102 p_endperson IN NUMBER,
103 p_chunk IN NUMBER );
104
105
106 FUNCTION get_parameter(name IN VARCHAR2,
107 parameter_list VARCHAR2)
108 RETURN VARCHAR2;
109
110 pragma restrict_references(get_parameter, WNDS, WNPS);
111
112
113 FUNCTION get_transmitter_item(p_business_group_id IN NUMBER,
114 p_pact_id IN NUMBER,
115 p_archived_item IN VARCHAR2)
116 RETURN VARCHAR2;
117
118
119 FUNCTION get_employer_item(p_business_group_id IN NUMBER,
120 p_pact_id IN NUMBER,
121 p_archived_item IN VARCHAR2)
122 RETURN VARCHAR2;
123
124 PROCEDURE xml_transmitter_record;
125
126 PROCEDURE end_of_file;
127
128 PROCEDURE xml_employee_record;
129
130 PROCEDURE xml_employer_start;
131
132 PROCEDURE xml_employer_record;
133
134 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
135 RETURN NUMBER;
136
137 CURSOR rl2_asg_actions
138 IS
139 SELECT 'TRANSFER_ACT_ID=P',
140 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
141 FROM DUAL;
142
143 FUNCTION convert_special_char( p_data IN VARCHAR2)
144 RETURN VARCHAR2;
145
146 END pay_ca_rl2_can_mag;
[Home] [Help]