[Home] [Help]
PACKAGE: APPS.PAY_CA_DIRECT_DEPOSIT_PKG
Source
1 PACKAGE pay_ca_direct_deposit_pkg AUTHID CURRENT_USER AS
2 /* $Header: pycatapd.pkh 120.0 2005/05/29 03:48:01 appldev noship $ */
3 --
4 -- ROYAL BANK OF CANADA (RBC) CURSORS
5 --
6 CURSOR rbc_header IS
7 select
8 'ORG_PAY_METHOD_ID=C', opm.org_payment_method_id
9 , 'TRANSFER_ORG_PAY_METH=P', fnd_number.number_to_canonical(opm.org_payment_method_id)
10 , 'ORIGINATOR_ID=P', opm.pmeth_information2
11 , 'ORIGINATOR_NAME=P', opm.pmeth_information3
12 , 'TRANSFER_PAY_ACT_ID=P', fnd_number.number_to_canonical(ppa.payroll_action_id)
13 , 'TRANSFER_DD_DATE=P', nvl(to_char(ppa.overriding_dd_date,'YYYYDDD'),
14 to_char(ppa.effective_date,'YYYYDDD'))
15 , 'BUSINESS_GROUP_ID=P', fnd_number.number_to_canonical(ppa.business_group_id)
16 from pay_org_payment_methods_f opm
17 , pay_payroll_actions ppa
18 where ppa.payroll_action_id =
19 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
20 and ppa.org_payment_method_id = opm.org_payment_method_id
21 and ppa.effective_date between opm.effective_start_date
22 and opm.effective_end_date;
23 --
24 --
25 CURSOR rbc_multi_payments IS
26 select
27 'TRANSFER_COUNT=P', count(paf.person_id)
28 , 'TRANSFER_PERSON_ID=P', paf.person_id
29 from
30 pay_pre_payments ppp
31 , per_assignments paf
32 , pay_assignment_actions paa
33 where
34 paa.payroll_action_id =
35 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
36 and ppp.org_payment_method_id +0 =
37 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
38 and paa.pre_payment_id = ppp.pre_payment_id
39 and paf.assignment_id = paa.assignment_id
40 group by paf.person_id;
41 --
42 --
43 CURSOR rbc_payment IS
44 select
45 'CPA_CODE=P', opm.pmeth_information7
46 , 'ORIGINATOR_ID=P', opm.pmeth_information2
47 , 'CUSTOMER_NUMBER=P', paf.assignment_number
48 , 'ASSIGNMENT_ID=P', paf.assignment_id
49 , 'TRANSIT_NUMBER=P', pea.segment4
50 , 'BANK_NUMBER=P', pea.segment7
51 , 'ACCOUNT_NUMBER=P', pea.segment3
52 , 'AMOUNT=P', ppp.value * 100
53 , 'CUSTOMER_NAME=P', ppf.full_name
54 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
55 , 'CURRENCY_CODE=P', opm.currency_code
56 from
57 pay_org_payment_methods opm
58 , pay_personal_payment_methods ppm
59 , pay_external_accounts pea
60 , pay_pre_payments ppp
61 , per_people ppf
62 , per_assignments paf
63 , pay_assignment_actions paa
64 where paa.payroll_action_id =
65 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
66 and ppp.org_payment_method_id +0 =
67 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
68 and opm.org_payment_method_id = ppm.org_payment_method_id
69 and ppm.external_account_id = pea.external_account_id
70 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
71 and paa.pre_payment_id = ppp.pre_payment_id
72 and paf.person_id = ppf.person_id
73 and paf.assignment_id = paa.assignment_id
74 and ppf.person_id =
75 pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID')
76 order by paf.assignment_number;
77 --
78 --
79 -- BANK OF Montreal (BMO) CURSORS
80 --
81 CURSOR bmo_header IS
82 select
83 'ORG_PAY_METHOD_ID=C', opm.org_payment_method_id
84 , 'TRANSFER_ORG_PAY_METH=P', fnd_number.number_to_canonical(opm.org_payment_method_id)
85 , 'ORIGINATOR_ID=P', opm.pmeth_information2
86 , 'ORIGINATOR_NAME=P', opm.pmeth_information3
87 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
88 , 'TRANSFER_PAY_ACT_ID=P', fnd_number.number_to_canonical(ppa.payroll_action_id)
89 , 'TRANSFER_DD_DATE=P', nvl(to_char(ppa.overriding_dd_date,'YYDDD'),to_char(ppa.effective_date,'YYDDD'))
90 , 'DES_DATA_CENTRE=P', opm.pmeth_information8
91 , 'CPA_CODE=P', opm.pmeth_information7
92 , 'RETURN_BANK_NUMBER=P', substr(opm.pmeth_information5,2,3)
93 , 'RETURN_TRANSIT_NUMBER=P', substr(opm.pmeth_information5,5,5)
94 , 'RETURN_ACCOUNT_NUMBER=P', opm.pmeth_information6
95 , 'BUSINESS_GROUP_ID=P', fnd_number.number_to_canonical(ppa.business_group_id)
96 from pay_org_payment_methods_f opm
97 , pay_payroll_actions ppa
98 where ppa.payroll_action_id =
99 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
100 and ppa.org_payment_method_id = opm.org_payment_method_id
101 and ppa.effective_date between opm.effective_start_date
102 and opm.effective_end_date;
103 --
104 --
105 CURSOR bmo_multi_payments IS
106 select
107 'TRANSFER_COUNT=P', count(paf.person_id)
108 , 'TRANSFER_PERSON_ID=P', paf.person_id
109 from
110 pay_pre_payments ppp
111 , per_assignments paf
112 , pay_assignment_actions paa
113 where
114 paa.payroll_action_id =
115 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
116 and ppp.org_payment_method_id +0 =
117 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
118 and paa.pre_payment_id = ppp.pre_payment_id
119 and paf.assignment_id = paa.assignment_id
120 group by paf.person_id;
121 --
122 --
123 CURSOR bmo_payment IS
124 select
125 'CPA_CODE=P', opm.pmeth_information7
126 , 'ORIGINATOR_ID=P', opm.pmeth_information2
127 , 'CUSTOMER_NUMBER=P', paf.assignment_number
128 , 'ASSIGNMENT_ID=P', paf.assignment_id
129 , 'TRANSIT_NUMBER=P', pea.segment4
130 , 'BANK_NUMBER=P', pea.segment7
131 , 'ACCOUNT_NUMBER=P', pea.segment3
132 , 'AMOUNT=P', ppp.value * 100
133 , 'CUSTOMER_NAME=P', ppf.full_name
134 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
135 , 'CURRENCY_CODE=P', opm.currency_code
136 from
137 pay_org_payment_methods opm
138 , pay_personal_payment_methods ppm
139 , pay_external_accounts pea
140 , pay_pre_payments ppp
141 , per_people ppf
142 , per_assignments paf
143 , pay_assignment_actions paa
144 where paa.payroll_action_id =
145 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
146 and ppp.org_payment_method_id +0 =
147 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
148 and opm.org_payment_method_id = ppm.org_payment_method_id
149 and ppm.external_account_id = pea.external_account_id
150 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
151 and paa.pre_payment_id = ppp.pre_payment_id
152 and paf.person_id = ppf.person_id
153 and paf.assignment_id = paa.assignment_id
154 and ppf.person_id =
155 pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID')
156 order by paf.assignment_number;
157 --
158 --
159 -- National Bank (BNC) CURSORS
160 --
161 --
162 CURSOR bnc_header IS
163 select
164 'ORG_PAY_METHOD_ID=C', opm.org_payment_method_id
165 , 'TRANSFER_ORG_PAY_METH=P', fnd_number.number_to_canonical(opm.org_payment_method_id)
166 , 'ORIGINATOR_ID=P', opm.pmeth_information2
167 , 'ORIGINATOR_NAME=P', opm.pmeth_information3
168 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
169 , 'TRANSFER_PAY_ACT_ID=P', fnd_number.number_to_canonical(ppa.payroll_action_id)
170 , 'TRANSFER_DD_DATE=P', nvl(to_char(ppa.overriding_dd_date,'YYDDD'),to_char(ppa.effective_date,'YYDDD'))
171 , 'CPA_CODE=P', opm.pmeth_information7
172 , 'RETURN_BANK_NUMBER=P', substr(opm.pmeth_information5,2,3)
173 , 'RETURN_TRANSIT_NUMBER=P', substr(opm.pmeth_information5,5,5)
174 , 'RETURN_ACCOUNT_NUMBER=P', opm.pmeth_information6
175 , 'BUSINESS_GROUP_ID=P', fnd_number.number_to_canonical(ppa.business_group_id)
176 from pay_org_payment_methods_f opm
177 , pay_payroll_actions ppa
178 where ppa.payroll_action_id =
179 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
180 and ppa.org_payment_method_id = opm.org_payment_method_id
181 and ppa.effective_date between opm.effective_start_date
182 and opm.effective_end_date;
183 --
184 --
185 CURSOR bnc_multi_payments IS
186 select
187 'TRANSFER_COUNT=P', count(paf.person_id)
188 , 'TRANSFER_PERSON_ID=P', paf.person_id
189 from
190 pay_pre_payments ppp
191 , per_assignments paf
192 , pay_assignment_actions paa
193 where
194 paa.payroll_action_id =
195 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
196 and ppp.org_payment_method_id +0 =
197 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
198 and paa.pre_payment_id = ppp.pre_payment_id
199 and paf.assignment_id = paa.assignment_id
200 group by paf.person_id;
201 --
202 --
203 CURSOR bnc_payment IS
204 select
205 'CPA_CODE=P', opm.pmeth_information7
209 , 'TRANSIT_NUMBER=P', pea.segment4
206 , 'ORIGINATOR_ID=P', opm.pmeth_information2
207 , 'CUSTOMER_NUMBER=P', paf.assignment_number
208 , 'ASSIGNMENT_ID=P', paf.assignment_id
210 , 'BANK_NUMBER=P', pea.segment7
211 , 'ACCOUNT_NUMBER=P', pea.segment3
212 , 'AMOUNT=P', ppp.value * 100
213 , 'CUSTOMER_LAST_NAME=P', ppf.last_name
214 , 'CUSTOMER_FIRST_NAME=P', ppf.first_name
215 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
216 , 'CURRENCY_CODE=P', opm.currency_code
217 from
218 pay_org_payment_methods opm
219 , pay_personal_payment_methods ppm
220 , pay_external_accounts pea
221 , pay_pre_payments ppp
222 , per_people ppf
223 , per_assignments paf
224 , pay_assignment_actions paa
225 where paa.payroll_action_id =
226 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
227 and ppp.org_payment_method_id +0 =
228 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
229 and opm.org_payment_method_id = ppm.org_payment_method_id
230 and ppm.external_account_id = pea.external_account_id
231 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
232 and paa.pre_payment_id = ppp.pre_payment_id
233 and paf.person_id = ppf.person_id
234 and paf.assignment_id = paa.assignment_id
235 and ppf.person_id =
236 pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID')
237 order by paf.assignment_number;
238
239 /* Bank of Nova Scotia */
240
241 CURSOR bnvsc_header IS
242 select
243 'ORG_PAY_METHOD_ID=C', opm.org_payment_method_id
244 , 'TRANSFER_ORG_PAY_METH=P', fnd_number.number_to_canonical(opm.org_payment_method_id)
245 , 'ORIGINATOR_ID=P', opm.pmeth_information2
246 , 'ORIGINATOR_NAME=P', opm.pmeth_information3
247 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
248 , 'TRANSFER_PAY_ACT_ID=P', fnd_number.number_to_canonical(ppa.payroll_action_id)
249 , 'TRANSFER_DD_DATE=P', nvl(to_char(ppa.overriding_dd_date,'YYDDD'),to_char(ppa.effective_date,'YYDDD'))
250 , 'CPA_CODE=P', opm.pmeth_information7
251 , 'RETURN_BANK_NUMBER=P', substr(opm.pmeth_information5,2,3)
252 , 'RETURN_TRANSIT_NUMBER=P', substr(opm.pmeth_information5,5,5)
253 , 'RETURN_ACCOUNT_NUMBER=P', opm.pmeth_information6
254 , 'DES_DATA_CENTRE=P', opm.pmeth_information8
255 , 'RETURN_BANK_TRANSIT_NUMBER=P', pea.segment4
256 , 'BANK_NUMBER=P', pea.segment7
257 , 'BUSINESS_GROUP_ID=P', fnd_number.number_to_canonical(ppa.business_group_id)
258 from pay_org_payment_methods_f opm,
259 pay_external_accounts pea
260 , pay_payroll_actions ppa
261 where ppa.payroll_action_id =
262 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
263 and ppa.org_payment_method_id = opm.org_payment_method_id
264 and ppa.effective_date between opm.effective_start_date
265 and opm.effective_end_date
266 and opm.external_account_id = pea.external_account_id;
267 --
268 --
269 CURSOR bnvsc_multi_payments IS
270 select
271 'TRANSFER_COUNT=P', count(paf.person_id)
272 , 'TRANSFER_PERSON_ID=P', paf.person_id
273 from
274 pay_pre_payments ppp
275 , per_assignments paf
276 , pay_assignment_actions paa
277 where
278 paa.payroll_action_id =
279 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
280 and ppp.org_payment_method_id +0 =
281 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
282 and paa.pre_payment_id = ppp.pre_payment_id
283 and paf.assignment_id = paa.assignment_id
284 group by paf.person_id;
285 --
286 --
287 CURSOR bnvsc_payment IS
288 select
289 'CPA_CODE=P', opm.pmeth_information7
290 , 'ORIGINATOR_ID=P', opm.pmeth_information2
291 , 'CUSTOMER_NUMBER=P', paf.assignment_number
292 , 'ASSIGNMENT_ID=P', paf.assignment_id
293 , 'TRANSIT_NUMBER=P', pea.segment4
294 , 'BANK_NUMBER=P', pea.segment7
295 , 'ACCOUNT_NUMBER=P', pea.segment3
296 , 'AMOUNT=P', ppp.value * 100
297 , 'CUSTOMER_NAME=P', ppf.full_name
298 , 'ORIGINATOR_SHR_NAME=P', opm.pmeth_information4
299 , 'CURRENCY_CODE=P', opm.currency_code
300 from
301 pay_org_payment_methods opm
302 , pay_personal_payment_methods ppm
303 , pay_external_accounts pea
304 , pay_pre_payments ppp
305 , per_people ppf
306 , per_assignments paf
307 , pay_assignment_actions paa
308 where paa.payroll_action_id =
309 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
310 and ppp.org_payment_method_id +0 =
311 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH')
312 and opm.org_payment_method_id = ppm.org_payment_method_id
313 and ppm.external_account_id = pea.external_account_id
314 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
315 and paa.pre_payment_id = ppp.pre_payment_id
316 and paf.person_id = ppf.person_id
317 and paf.assignment_id = paa.assignment_id
318 and ppf.person_id =
319 pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID')
320 order by paf.assignment_number;
321 --
322 level_cnt number;
323 --
324 FUNCTION get_file_creation_number(p_originator_id varchar2
325 ,p_fin_institution varchar2
326 ,p_override_fcn varchar2)
327 return varchar2;
328
329 FUNCTION get_dd_file_creation_number(p_org_payment_method_id number
330 ,p_fin_institution varchar2
331 ,p_override_fcn varchar2
332 ,p_pact_id number
333 ,p_business_group_id number)
334 return varchar2;
335
336 FUNCTION convert_uppercase(p_input_string varchar2)
337 return varchar2;
338
339 PRAGMA RESTRICT_REFERENCES(convert_uppercase, WNDS);
340
341 --
342 --
343 end pay_ca_direct_deposit_pkg;