1 package pay_ca_rl1_can_mag AUTHID CURRENT_USER as
2 /* $Header: pycarlcmg.pkh 120.1.12020000.2 2012/11/21 11:29:01 sbachu ship $ */
3
4 -- 'level_cnt' will allow the cursors to select function results,
5 -- whether it is a standard fuction such as to_char or a function
6 -- defined in a package (with the correct pragma restriction).
7
8 level_cnt NUMBER;
9
10 /*Bug 14701466*/
11 type other_info_rec is record (code varchar2(10),
12 amount varchar2(50));
13
14 type other_info_tab is table of other_info_rec index by binary_integer;
15 g_further_info_list other_info_tab;
16
17 -- Used by Magnetic RL1 (RL1 format).
18 --
19 -- Sets up the tax unit context for the transmitter_GRE
20 --
21
22 --g_pre_id number;
23
24 /* Commented for bug 9142143 and re written below.
25 CURSOR mag_rl1_transmitter IS
26 SELECT distinct
27 'PAYROLL_ACTION_ID=P',
28 ppa1.payroll_action_id
29 from
30 pay_payroll_actions ppa,
31 pay_payroll_actions ppa1,
32 pay_assignment_actions paa,
33 pay_assignment_actions paa1,
34 pay_action_interlocks int
35 where
36 ppa.payroll_action_id = paa.payroll_action_id
37 and ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
38 and int.locking_action_id = paa.assignment_action_id
39 and paa1.assignment_action_id = int.locked_action_id
40 and ppa1.payroll_action_id = paa1.payroll_action_id
41 and ppa1.report_type in ('RL1','CAEOY_RL1_AMEND_PP')
42 and ppa1.action_status = 'C';
43 */
44 CURSOR mag_rl1_transmitter IS
45 SELECT distinct
46 'PAYROLL_ACTION_ID=P',
47 ppa.payroll_action_id
48 from
49 pay_payroll_actions ppa
50 where ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'));
51
52 --
53 -- Used by Magnetic RL1 (RL1 format).
54 --
55 -- Sets up the tax unit context for each employer to be reported. sets
56 -- up a parameter holding the tax unit identifier which can then be used by
57 -- subsequent cursors to restrict to employees within the employer.
58 --
59
60 CURSOR mag_rl1_employer IS
61 select
62 'PAYROLL_ACTION_ID=C',to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')),
63 'PAYROLL_ACTION_ID=P',to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
64 from dual;
65
66
67 --
68 -- Used by Magnetic RL1 (RL1 format).
69 --
70 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
71 -- for an employee. The date_earned context is set to be the least of the
72 -- end of the period being reported and the maximum end date of the
73 -- assignment. This ensures that personal information ie. name etc... is
74 -- current relative to the period being reported on.
75 --
76
77 CURSOR mag_rl1_employee IS
78 SELECT 'TRANSFER_ACT_ID=P',
79 paa.assignment_action_id
80 FROM
81 per_all_people_f ppf,
82 per_all_assignments_f paf,
83 pay_action_interlocks pai,
84 pay_assignment_actions paa,
85 pay_payroll_actions ppa,
86 pay_assignment_actions paa_mag
87 WHERE
88 ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
89 AND paa.payroll_action_id = ppa.payroll_action_id
90 AND pai.locking_action_id = paa.assignment_action_id
91 AND paf.assignment_id = paa.assignment_id
92 AND ppf.person_id = paf.person_id
93 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
94 between paf.effective_start_date and paf.effective_end_date
95 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
96 between ppf.effective_start_date and ppf.effective_end_date
97 AND paa_mag.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
98 AND paa_mag.assignment_action_id = pai.locked_action_id
99 ORDER BY
100 ppf.last_name,ppf.first_name,ppf.middle_names;
101
102
103 PROCEDURE get_report_parameters
104 (
105 p_pactid IN NUMBER,
106 p_year_start IN OUT NOCOPY DATE,
107 p_year_end IN OUT NOCOPY DATE,
108 p_report_type IN OUT NOCOPY VARCHAR2,
109 p_business_group_id IN OUT NOCOPY NUMBER,
110 p_legislative_parameters IN OUT NOCOPY VARCHAR2
111 );
112 /*Bug 14701466*/
113 procedure store_further_information(p_arch_assact_id IN NUMBER
114 ,p_assgn_id IN NUMBER);
115
116 procedure get_further_information(p_index in number,
117 p_code in out nocopy varchar2,
118 p_amount in out nocopy varchar2);
119
120 PROCEDURE range_cursor (
121 p_pactid IN NUMBER,
122 p_sqlstr OUT NOCOPY VARCHAR2
123 );
124
125
126 PROCEDURE create_assignment_act(
127 p_pactid IN NUMBER,
128 p_stperson IN NUMBER,
129 p_endperson IN NUMBER,
130 p_chunk IN NUMBER );
131
132 FUNCTION get_parameter(name in varchar2,
133 parameter_list varchar2)
134 RETURN varchar2;
135
136 pragma restrict_references(get_parameter, WNDS, WNPS);
137
138
139
140 FUNCTION validate_quebec_number(p_quebec_no IN VARCHAR2,
141 p_qin_name varchar2)
142 RETURN NUMBER;
143
144
145 FUNCTION get_arch_val(p_context_id IN NUMBER,
146 p_user_name IN VARCHAR2)
147 RETURN VARCHAR2;
148
149
150 PROCEDURE xml_transmitter_record;
151
152 PROCEDURE end_of_file;
153
154 PROCEDURE xml_employee_record;
155
156 PROCEDURE xml_employer_start;
157
158 PROCEDURE XML_EMPLOYER_RECORD;
159
160 CURSOR rl1_asg_actions
161 IS
162 SELECT 'TRANSFER_ACT_ID=P',pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
163 FROM DUAL;
164
165 END pay_ca_rl1_can_mag;
[Home] [Help]