[Home] [Help]
PACKAGE BODY: APPS.PAY_PPMV4_UTILS_SS
Source
1 package body pay_ppmv4_utils_ss as
2 /* $Header: pyppmv4u.pkb 120.9 2011/08/22 12:17:53 panumala ship $ */
3 ---------------------------------------------------------------------------
4 ---------------------------------- CONSTANTS ------------------------------
5 ---------------------------------------------------------------------------
6 g_package constant varchar(2000) default 'pay_ppmv4_utils_ss.';
7 ---------------------------------------------------------------------------
8 ----------------------- FUNCTIONS AND PROCEDURES --------------------------
9 ---------------------------------------------------------------------------
10 -------------------------------< seterror >--------------------------------
11 procedure seterrorstage
12 (p_proc in varchar2, p_stage in varchar2, p_location in number) is
13 begin
14 hr_utility.set_location(p_proc || ':' || p_stage, p_location);
15 end seterrorstage;
16 -----------------------------< add_tx_row >-------------------------
17 --
18 -- {Start Of Comments}
19 --
20 -- Description:
21 -- Adds a row of a given type to the PLSQL representation of the
22 -- transaction table.
23 --
24 -- Prerequisites:
25 -- None.
26 --
27 -- Post Success:
28 -- A new table entry is created.
29 --
30 -- Post Failure:
31 -- Not applicable.
32 --
33 -- Access Status:
34 -- Internal Development Use Only.
35 --
36 -- {End Of Comments}
37 --
38 procedure add_tx_row
39 (p_parameter_name in varchar2
40 ,p_parameter_value in long
41 ,p_data_type in varchar2 default 'VARCHAR2'
42 ,p_proc in varchar2
43 ,p_table in out nocopy hr_transaction_ss.transaction_table
44 ) is
45 i binary_integer;
46 begin
47 seterrorstage(p_proc, p_parameter_name, 5);
48 i := p_table.count + 1;
49 p_table(i).param_name := p_parameter_name;
50 p_table(i).param_value := p_parameter_value;
51 p_table(i).param_data_type := p_data_type;
52 exception
53 when others then
54 raise;
55 end add_tx_row;
56 ------------------------------< ppm2hrtt >---------------------------------
57 procedure ppm2hrtt
58 (p_item_type in varchar2
59 ,p_item_key in varchar2
60 ,p_activity_id in number
61 ,p_login_person_id in number
62 ,p_review_proc_call in varchar2
63 ,p_transaction_step_id in number -- From PAY_PSS_TRANSACTION_STEPS.
64 ,p_force_new_transaction in boolean
65 ) is
66 l_tx_table hr_transaction_ss.transaction_table;
67 l_transaction_id number;
68 l_api_display_name varchar2(2000);
69 l_api_name varchar2(2000);
70 l_result varchar2(2000);
71 l_wf_txstep_id number;
72 l_ovn number;
73 l_proc varchar2(2000) := g_package || 'ppm2wftt';
74 begin
75 seterrorstage(l_proc, 'ENTER', 0);
76 --
77 -- Build the transaction row.
78 --
79 add_tx_row
80 (p_parameter_name => C_TX_STEP_ID_ARG
81 ,p_parameter_value => to_char(p_transaction_step_id)
82 ,p_data_type => 'NUMBER'
83 ,p_proc => l_proc
84 ,p_table => l_tx_table
85 );
86 add_tx_row
87 (p_parameter_name => C_REVIEW_PROC_CALL_ARG
88 ,p_parameter_value => p_review_proc_call
89 ,p_proc => l_proc
90 ,p_table => l_tx_table
91 );
92 add_tx_row
93 (p_parameter_name => C_REVIEW_ACTID_ARG
94 ,p_parameter_value => to_char(p_activity_id)
95 ,p_proc => l_proc
96 ,p_table => l_tx_table
97 );
98 add_tx_row
99 (p_parameter_name => C_PROCESSED_FLAG_ARG
100 ,p_parameter_value => 'N'
101 ,p_proc => l_proc
102 ,p_table => l_tx_table
103 );
104 --
105 -- Write to HR transaction table.
106 --
107 l_api_name := C_PSS_API;
108 l_api_display_name := 'PAYROLL PAYMENTS SELF-SERVICE V4';
109 if p_force_new_transaction then
110 seterrorstage(l_proc, 'START_TRANSACTION_ID', 10);
111 hr_transaction_ss.start_transaction
112 (itemtype => p_item_type
113 ,itemkey => p_item_key
114 ,actid => p_activity_id
115 ,funmode => 'RUN'
116 ,p_login_person_id => p_login_person_id
117 ,result => l_result
118 );
119 seterrorstage(l_proc, 'GET_TRANSACTION_ID:2', 20);
120 l_transaction_id := hr_transaction_ss.get_transaction_id
121 (p_item_type => p_item_type
122 ,p_item_key => p_item_key
123 );
124 else
125 seterrorstage(l_proc, 'GET_TRANSACTION_ID:1', 30);
126 l_transaction_id := hr_transaction_ss.get_transaction_id
127 (p_item_type => p_item_type
128 ,p_item_key => p_item_key
129 );
130 end if;
131 seterrorstage(l_proc, 'CREATE_TRANSACTION_STEP', 35);
132 hr_transaction_api.create_transaction_step
133 (p_validate => false
134 ,p_creator_person_id => p_login_person_id
135 ,p_transaction_id => l_transaction_id
136 ,p_api_name => l_api_name
137 ,p_api_display_name => l_api_display_name
138 ,p_item_type => p_item_type
139 ,p_item_key => p_item_key
140 ,p_activity_id => p_activity_id
141 ,p_transaction_step_id => l_wf_txstep_id
142 ,p_object_version_number => l_ovn
143 );
144 seterrorstage(l_proc, 'SAVE_TRANSACTION_STEP', 40);
145 hr_transaction_ss.save_transaction_step
146 (p_item_type => p_item_type
147 ,p_item_key => p_item_key
148 ,p_actid => p_activity_id
149 ,p_login_person_id => p_login_person_id
150 ,p_transaction_step_id => l_wf_txstep_id
151 ,p_api_name => l_api_name
152 ,p_api_display_name => l_api_display_name
153 ,p_transaction_data => l_tx_table
154 );
155 return;
156 exception
157 when others then
158 seterrorstage(l_proc, 'EXIT:FAIL', 50);
159 raise;
160 end ppm2hrtt;
161 --------------------------------< ppm2tt >---------------------------------
162 procedure ppm2tt
163 (p_ppm in out nocopy t_ppmv4
164 ) is
165 l_proc varchar2(2000) := g_package || 'ppm2tt';
166 begin
167 seterrorstage(l_proc, 'ENTER', 0);
168 if p_ppm.transaction_step_id is null then
169 pay_pss_tx_steps_pkg.insert_row
170 (p_transaction_id => p_ppm.transaction_id
171 ,p_transaction_step_id => p_ppm.transaction_step_id
172 ,p_source_table => p_ppm.source_table
173 ,p_state => p_ppm.state
174 ,p_personal_payment_method_id => p_ppm.personal_payment_method_id
175 ,p_update_ovn => p_ppm.update_ovn
176 ,p_delete_ovn => p_ppm.delete_ovn
177 ,p_update_datetrack_mode => p_ppm.update_datetrack_mode
178 ,p_delete_datetrack_mode => p_ppm.delete_datetrack_mode
179 ,p_delete_disabled => p_ppm.delete_disabled
180 ,p_effective_date => p_ppm.effective_date
181 ,p_org_payment_method_id => p_ppm.org_payment_method_id
182 ,p_assignment_id => p_ppm.assignment_id
183 ,p_payment_type => p_ppm.payment_type
184 ,p_currency_code => p_ppm.currency_code
185 ,p_territory_code => p_ppm.territory_code
186 ,p_run_type_id => p_ppm.run_type_id
187 ,p_real_priority => p_ppm.real_priority
188 ,p_logical_priority => p_ppm.logical_priority
189 ,p_amount_type => p_ppm.amount_type
190 ,p_amount => p_ppm.amount
191 ,p_external_account_id => p_ppm.external_account_id
192 ,p_attribute_category => p_ppm.attribute_category
193 ,p_attribute1 => p_ppm.attribute1
194 ,p_attribute2 => p_ppm.attribute2
195 ,p_attribute3 => p_ppm.attribute3
196 ,p_attribute4 => p_ppm.attribute4
197 ,p_attribute5 => p_ppm.attribute5
198 ,p_attribute6 => p_ppm.attribute6
199 ,p_attribute7 => p_ppm.attribute7
200 ,p_attribute8 => p_ppm.attribute8
201 ,p_attribute9 => p_ppm.attribute9
202 ,p_attribute10 => p_ppm.attribute10
203 ,p_attribute11 => p_ppm.attribute11
204 ,p_attribute12 => p_ppm.attribute12
205 ,p_attribute13 => p_ppm.attribute13
206 ,p_attribute14 => p_ppm.attribute14
207 ,p_attribute15 => p_ppm.attribute15
208 ,p_attribute16 => p_ppm.attribute16
209 ,p_attribute17 => p_ppm.attribute17
210 ,p_attribute18 => p_ppm.attribute18
211 ,p_attribute19 => p_ppm.attribute19
212 ,p_attribute20 => p_ppm.attribute20
213 ,p_o_real_priority => p_ppm.o_real_priority
214 ,p_o_logical_priority => p_ppm.o_logical_priority
215 ,p_o_amount_type => p_ppm.o_amount_type
216 ,p_o_amount => p_ppm.o_amount
217 ,p_o_external_account_id => p_ppm.o_external_account_id
218 ,p_o_attribute_category => p_ppm.o_attribute_category
219 ,p_o_attribute1 => p_ppm.o_attribute1
220 ,p_o_attribute2 => p_ppm.o_attribute2
221 ,p_o_attribute3 => p_ppm.o_attribute3
222 ,p_o_attribute4 => p_ppm.o_attribute4
223 ,p_o_attribute5 => p_ppm.o_attribute5
224 ,p_o_attribute6 => p_ppm.o_attribute6
225 ,p_o_attribute7 => p_ppm.o_attribute7
226 ,p_o_attribute8 => p_ppm.o_attribute8
227 ,p_o_attribute9 => p_ppm.o_attribute9
228 ,p_o_attribute10 => p_ppm.o_attribute10
229 ,p_o_attribute11 => p_ppm.o_attribute11
230 ,p_o_attribute12 => p_ppm.o_attribute12
231 ,p_o_attribute13 => p_ppm.o_attribute13
232 ,p_o_attribute14 => p_ppm.o_attribute14
233 ,p_o_attribute15 => p_ppm.o_attribute15
234 ,p_o_attribute16 => p_ppm.o_attribute16
235 ,p_o_attribute17 => p_ppm.o_attribute17
236 ,p_o_attribute18 => p_ppm.o_attribute18
237 ,p_o_attribute19 => p_ppm.o_attribute19
238 ,p_o_attribute20 => p_ppm.o_attribute20
239 ,p_ppm_information_category => p_ppm.ppm_information_category
240 ,p_ppm_information1 => p_ppm.ppm_information1
241 ,p_ppm_information2 => p_ppm.ppm_information2
242 ,p_ppm_information3 => p_ppm.ppm_information3
243 ,p_ppm_information4 => p_ppm.ppm_information4
244 ,p_ppm_information5 => p_ppm.ppm_information5
245 ,p_ppm_information6 => p_ppm.ppm_information6
246 ,p_ppm_information7 => p_ppm.ppm_information7
247 ,p_ppm_information8 => p_ppm.ppm_information8
248 ,p_ppm_information9 => p_ppm.ppm_information9
249 ,p_ppm_information10 => p_ppm.ppm_information10
250 ,p_ppm_information11 => p_ppm.ppm_information11
251 ,p_ppm_information12 => p_ppm.ppm_information12
252 ,p_ppm_information13 => p_ppm.ppm_information13
253 ,p_ppm_information14 => p_ppm.ppm_information14
254 ,p_ppm_information15 => p_ppm.ppm_information15
255 ,p_ppm_information16 => p_ppm.ppm_information16
256 ,p_ppm_information17 => p_ppm.ppm_information17
257 ,p_ppm_information18 => p_ppm.ppm_information18
258 ,p_ppm_information19 => p_ppm.ppm_information19
259 ,p_ppm_information20 => p_ppm.ppm_information20
260 ,p_ppm_information21 => p_ppm.ppm_information21
261 ,p_ppm_information22 => p_ppm.ppm_information22
262 ,p_ppm_information23 => p_ppm.ppm_information23
263 ,p_ppm_information24 => p_ppm.ppm_information24
264 ,p_ppm_information25 => p_ppm.ppm_information25
265 ,p_ppm_information26 => p_ppm.ppm_information26
266 ,p_ppm_information27 => p_ppm.ppm_information27
267 ,p_ppm_information28 => p_ppm.ppm_information28
268 ,p_ppm_information29 => p_ppm.ppm_information29
269 ,p_ppm_information30 => p_ppm.ppm_information30
270 ,p_o_ppm_information_category => p_ppm.o_ppm_information_category
271 ,p_o_ppm_information1 => p_ppm.o_ppm_information1
272 ,p_o_ppm_information2 => p_ppm.o_ppm_information2
273 ,p_o_ppm_information3 => p_ppm.o_ppm_information3
274 ,p_o_ppm_information4 => p_ppm.o_ppm_information4
275 ,p_o_ppm_information5 => p_ppm.o_ppm_information5
276 ,p_o_ppm_information6 => p_ppm.o_ppm_information6
277 ,p_o_ppm_information7 => p_ppm.o_ppm_information7
278 ,p_o_ppm_information8 => p_ppm.o_ppm_information8
279 ,p_o_ppm_information9 => p_ppm.o_ppm_information9
280 ,p_o_ppm_information10 => p_ppm.o_ppm_information10
281 ,p_o_ppm_information11 => p_ppm.o_ppm_information11
282 ,p_o_ppm_information12 => p_ppm.o_ppm_information12
283 ,p_o_ppm_information13 => p_ppm.o_ppm_information13
284 ,p_o_ppm_information14 => p_ppm.o_ppm_information14
285 ,p_o_ppm_information15 => p_ppm.o_ppm_information15
286 ,p_o_ppm_information16 => p_ppm.o_ppm_information16
287 ,p_o_ppm_information17 => p_ppm.o_ppm_information17
288 ,p_o_ppm_information18 => p_ppm.o_ppm_information18
289 ,p_o_ppm_information19 => p_ppm.o_ppm_information19
290 ,p_o_ppm_information20 => p_ppm.o_ppm_information20
291 ,p_o_ppm_information21 => p_ppm.o_ppm_information21
292 ,p_o_ppm_information22 => p_ppm.o_ppm_information22
293 ,p_o_ppm_information23 => p_ppm.o_ppm_information23
294 ,p_o_ppm_information24 => p_ppm.o_ppm_information24
295 ,p_o_ppm_information25 => p_ppm.o_ppm_information25
296 ,p_o_ppm_information26 => p_ppm.o_ppm_information26
297 ,p_o_ppm_information27 => p_ppm.o_ppm_information27
298 ,p_o_ppm_information28 => p_ppm.o_ppm_information28
299 ,p_o_ppm_information29 => p_ppm.o_ppm_information29
300 ,p_o_ppm_information30 => p_ppm.o_ppm_information30
301 );
302 else
303 pay_pss_tx_steps_pkg.update_row
304 (p_transaction_step_id => p_ppm.transaction_step_id
305 ,p_source_table => p_ppm.source_table
306 ,p_state => p_ppm.state
307 ,p_personal_payment_method_id => p_ppm.personal_payment_method_id
308 ,p_update_ovn => p_ppm.update_ovn
309 ,p_delete_ovn => p_ppm.delete_ovn
310 ,p_update_datetrack_mode => p_ppm.update_datetrack_mode
311 ,p_delete_datetrack_mode => p_ppm.delete_datetrack_mode
312 ,p_delete_disabled => p_ppm.delete_disabled
313 ,p_effective_date => p_ppm.effective_date
314 ,p_org_payment_method_id => p_ppm.org_payment_method_id
315 ,p_assignment_id => p_ppm.assignment_id
316 ,p_payment_type => p_ppm.payment_type
317 ,p_currency_code => p_ppm.currency_code
318 ,p_territory_code => p_ppm.territory_code
319 ,p_run_type_id => p_ppm.run_type_id
320 ,p_real_priority => p_ppm.real_priority
321 ,p_logical_priority => p_ppm.logical_priority
322 ,p_amount_type => p_ppm.amount_type
323 ,p_amount => p_ppm.amount
324 ,p_external_account_id => p_ppm.external_account_id
325 ,p_attribute_category => p_ppm.attribute_category
326 ,p_attribute1 => p_ppm.attribute1
327 ,p_attribute2 => p_ppm.attribute2
328 ,p_attribute3 => p_ppm.attribute3
329 ,p_attribute4 => p_ppm.attribute4
330 ,p_attribute5 => p_ppm.attribute5
331 ,p_attribute6 => p_ppm.attribute6
332 ,p_attribute7 => p_ppm.attribute7
333 ,p_attribute8 => p_ppm.attribute8
334 ,p_attribute9 => p_ppm.attribute9
335 ,p_attribute10 => p_ppm.attribute10
336 ,p_attribute11 => p_ppm.attribute11
337 ,p_attribute12 => p_ppm.attribute12
338 ,p_attribute13 => p_ppm.attribute13
339 ,p_attribute14 => p_ppm.attribute14
340 ,p_attribute15 => p_ppm.attribute15
341 ,p_attribute16 => p_ppm.attribute16
342 ,p_attribute17 => p_ppm.attribute17
343 ,p_attribute18 => p_ppm.attribute18
344 ,p_attribute19 => p_ppm.attribute19
345 ,p_attribute20 => p_ppm.attribute20
346 ,p_o_real_priority => p_ppm.o_real_priority
347 ,p_o_logical_priority => p_ppm.o_logical_priority
348 ,p_o_amount_type => p_ppm.o_amount_type
349 ,p_o_amount => p_ppm.o_amount
350 ,p_o_external_account_id => p_ppm.o_external_account_id
351 ,p_o_attribute_category => p_ppm.o_attribute_category
352 ,p_o_attribute1 => p_ppm.o_attribute1
353 ,p_o_attribute2 => p_ppm.o_attribute2
354 ,p_o_attribute3 => p_ppm.o_attribute3
355 ,p_o_attribute4 => p_ppm.o_attribute4
356 ,p_o_attribute5 => p_ppm.o_attribute5
357 ,p_o_attribute6 => p_ppm.o_attribute6
358 ,p_o_attribute7 => p_ppm.o_attribute7
359 ,p_o_attribute8 => p_ppm.o_attribute8
360 ,p_o_attribute9 => p_ppm.o_attribute9
361 ,p_o_attribute10 => p_ppm.o_attribute10
362 ,p_o_attribute11 => p_ppm.o_attribute11
363 ,p_o_attribute12 => p_ppm.o_attribute12
364 ,p_o_attribute13 => p_ppm.o_attribute13
365 ,p_o_attribute14 => p_ppm.o_attribute14
366 ,p_o_attribute15 => p_ppm.o_attribute15
367 ,p_o_attribute16 => p_ppm.o_attribute16
368 ,p_o_attribute17 => p_ppm.o_attribute17
369 ,p_o_attribute18 => p_ppm.o_attribute18
370 ,p_o_attribute19 => p_ppm.o_attribute19
371 ,p_o_attribute20 => p_ppm.o_attribute20
372 ,p_ppm_information_category => p_ppm.ppm_information_category
373 ,p_ppm_information1 => p_ppm.ppm_information1
374 ,p_ppm_information2 => p_ppm.ppm_information2
375 ,p_ppm_information3 => p_ppm.ppm_information3
376 ,p_ppm_information4 => p_ppm.ppm_information4
377 ,p_ppm_information5 => p_ppm.ppm_information5
378 ,p_ppm_information6 => p_ppm.ppm_information6
379 ,p_ppm_information7 => p_ppm.ppm_information7
380 ,p_ppm_information8 => p_ppm.ppm_information8
381 ,p_ppm_information9 => p_ppm.ppm_information9
382 ,p_ppm_information10 => p_ppm.ppm_information10
383 ,p_ppm_information11 => p_ppm.ppm_information11
384 ,p_ppm_information12 => p_ppm.ppm_information12
385 ,p_ppm_information13 => p_ppm.ppm_information13
386 ,p_ppm_information14 => p_ppm.ppm_information14
387 ,p_ppm_information15 => p_ppm.ppm_information15
388 ,p_ppm_information16 => p_ppm.ppm_information16
389 ,p_ppm_information17 => p_ppm.ppm_information17
390 ,p_ppm_information18 => p_ppm.ppm_information18
391 ,p_ppm_information19 => p_ppm.ppm_information19
392 ,p_ppm_information20 => p_ppm.ppm_information20
393 ,p_ppm_information21 => p_ppm.ppm_information21
394 ,p_ppm_information22 => p_ppm.ppm_information22
395 ,p_ppm_information23 => p_ppm.ppm_information23
396 ,p_ppm_information24 => p_ppm.ppm_information24
397 ,p_ppm_information25 => p_ppm.ppm_information25
398 ,p_ppm_information26 => p_ppm.ppm_information26
399 ,p_ppm_information27 => p_ppm.ppm_information27
400 ,p_ppm_information28 => p_ppm.ppm_information28
401 ,p_ppm_information29 => p_ppm.ppm_information29
402 ,p_ppm_information30 => p_ppm.ppm_information30
403 ,p_o_ppm_information_category => p_ppm.o_ppm_information_category
404 ,p_o_ppm_information1 => p_ppm.o_ppm_information1
405 ,p_o_ppm_information2 => p_ppm.o_ppm_information2
406 ,p_o_ppm_information3 => p_ppm.o_ppm_information3
407 ,p_o_ppm_information4 => p_ppm.o_ppm_information4
408 ,p_o_ppm_information5 => p_ppm.o_ppm_information5
409 ,p_o_ppm_information6 => p_ppm.o_ppm_information6
410 ,p_o_ppm_information7 => p_ppm.o_ppm_information7
411 ,p_o_ppm_information8 => p_ppm.o_ppm_information8
412 ,p_o_ppm_information9 => p_ppm.o_ppm_information9
413 ,p_o_ppm_information10 => p_ppm.o_ppm_information10
414 ,p_o_ppm_information11 => p_ppm.o_ppm_information11
415 ,p_o_ppm_information12 => p_ppm.o_ppm_information12
416 ,p_o_ppm_information13 => p_ppm.o_ppm_information13
417 ,p_o_ppm_information14 => p_ppm.o_ppm_information14
418 ,p_o_ppm_information15 => p_ppm.o_ppm_information15
419 ,p_o_ppm_information16 => p_ppm.o_ppm_information16
420 ,p_o_ppm_information17 => p_ppm.o_ppm_information17
421 ,p_o_ppm_information18 => p_ppm.o_ppm_information18
422 ,p_o_ppm_information19 => p_ppm.o_ppm_information19
423 ,p_o_ppm_information20 => p_ppm.o_ppm_information20
424 ,p_o_ppm_information21 => p_ppm.o_ppm_information21
425 ,p_o_ppm_information22 => p_ppm.o_ppm_information22
426 ,p_o_ppm_information23 => p_ppm.o_ppm_information23
427 ,p_o_ppm_information24 => p_ppm.o_ppm_information24
428 ,p_o_ppm_information25 => p_ppm.o_ppm_information25
429 ,p_o_ppm_information26 => p_ppm.o_ppm_information26
430 ,p_o_ppm_information27 => p_ppm.o_ppm_information27
431 ,p_o_ppm_information28 => p_ppm.o_ppm_information28
432 ,p_o_ppm_information29 => p_ppm.o_ppm_information29
433 ,p_o_ppm_information30 => p_ppm.o_ppm_information30
434 );
435 end if;
436 seterrorstage(l_proc, 'EXIT:SUCCESS', 10);
437 return;
438 exception
439 when others then
440 seterrorstage(l_proc, 'EXIT:FAIL', 20);
441 raise;
442 end ppm2tt;
443 --------------------------------< tt2ppm >---------------------------------
444 procedure tt2ppm
445 (p_transaction_step_id in number
446 ,p_ppm out nocopy t_ppmv4
447 ) is
448 cursor csr_ppm
449 (p_transaction_step_id in number
450 ) is
451 select p.transaction_id
452 ,p.transaction_step_id
453 ,p.source_table
454 ,p.state
455 ,p.personal_payment_method_id
456 ,p.update_ovn
457 ,p.delete_ovn
458 ,p.update_datetrack_mode
459 ,p.delete_datetrack_mode
460 ,p.delete_disabled
461 ,p.effective_date
462 ,p.org_payment_method_id
463 ,p.assignment_id
464 ,p.payment_type
465 ,p.currency_code
466 ,p.territory_code
467 ,p.real_priority
468 ,p.logical_priority
469 ,p.amount_type
470 ,p.amount
471 ,p.external_account_id
472 ,p.attribute_category
473 ,p.attribute1
474 ,p.attribute2
475 ,p.attribute3
476 ,p.attribute4
477 ,p.attribute5
478 ,p.attribute6
479 ,p.attribute7
480 ,p.attribute8
481 ,p.attribute9
482 ,p.attribute10
483 ,p.attribute11
484 ,p.attribute12
485 ,p.attribute13
486 ,p.attribute14
487 ,p.attribute15
488 ,p.attribute16
489 ,p.attribute17
490 ,p.attribute18
491 ,p.attribute19
492 ,p.attribute20
493 ,p.o_real_priority
494 ,p.o_logical_priority
495 ,p.o_amount_type
496 ,p.o_amount
497 ,p.o_external_account_id
498 ,p.o_attribute_category
499 ,p.o_attribute1
500 ,p.o_attribute2
501 ,p.o_attribute3
502 ,p.o_attribute4
503 ,p.o_attribute5
504 ,p.o_attribute6
505 ,p.o_attribute7
506 ,p.o_attribute8
507 ,p.o_attribute9
508 ,p.o_attribute10
509 ,p.o_attribute11
510 ,p.o_attribute12
511 ,p.o_attribute13
512 ,p.o_attribute14
513 ,p.o_attribute15
514 ,p.o_attribute16
515 ,p.o_attribute17
516 ,p.o_attribute18
517 ,p.o_attribute19
518 ,p.o_attribute20
519 ,p.run_type_id
520 ,p.ppm_information_category
521 ,p.ppm_information1
522 ,p.ppm_information2
523 ,p.ppm_information3
524 ,p.ppm_information4
525 ,p.ppm_information5
526 ,p.ppm_information6
527 ,p.ppm_information7
528 ,p.ppm_information8
529 ,p.ppm_information9
530 ,p.ppm_information10
531 ,p.ppm_information11
532 ,p.ppm_information12
533 ,p.ppm_information13
534 ,p.ppm_information14
535 ,p.ppm_information15
536 ,p.ppm_information16
537 ,p.ppm_information17
538 ,p.ppm_information18
539 ,p.ppm_information19
540 ,p.ppm_information20
541 ,p.ppm_information21
542 ,p.ppm_information22
543 ,p.ppm_information23
544 ,p.ppm_information24
545 ,p.ppm_information25
546 ,p.ppm_information26
547 ,p.ppm_information27
548 ,p.ppm_information28
549 ,p.ppm_information29
550 ,p.ppm_information30
551 ,p.o_ppm_information_category
552 ,p.o_ppm_information1
553 ,p.o_ppm_information2
554 ,p.o_ppm_information3
555 ,p.o_ppm_information4
556 ,p.o_ppm_information5
557 ,p.o_ppm_information6
558 ,p.o_ppm_information7
559 ,p.o_ppm_information8
560 ,p.o_ppm_information9
561 ,p.o_ppm_information10
562 ,p.o_ppm_information11
563 ,p.o_ppm_information12
564 ,p.o_ppm_information13
565 ,p.o_ppm_information14
566 ,p.o_ppm_information15
567 ,p.o_ppm_information16
568 ,p.o_ppm_information17
569 ,p.o_ppm_information18
570 ,p.o_ppm_information19
571 ,p.o_ppm_information20
572 ,p.o_ppm_information21
573 ,p.o_ppm_information22
574 ,p.o_ppm_information23
575 ,p.o_ppm_information24
576 ,p.o_ppm_information25
577 ,p.o_ppm_information26
578 ,p.o_ppm_information27
579 ,p.o_ppm_information28
580 ,p.o_ppm_information29
581 ,p.o_ppm_information30
582 from pay_pss_transaction_steps p
583 where p.transaction_step_id = p_transaction_step_id;
584
585 l_proc varchar2(2000) := g_package || 'tt2ppm';
586 begin
587 seterrorstage(l_proc, 'ENTER', 0);
588 --
589 open csr_ppm
590 (p_transaction_step_id => p_transaction_step_id
591 );
592 fetch csr_ppm
593 into p_ppm.transaction_id
594 , p_ppm.transaction_step_id
595 , p_ppm.source_table
596 , p_ppm.state
597 , p_ppm.personal_payment_method_id
598 , p_ppm.update_ovn
599 , p_ppm.delete_ovn
600 , p_ppm.update_datetrack_mode
601 , p_ppm.delete_datetrack_mode
602 , p_ppm.delete_disabled
603 , p_ppm.effective_date
604 , p_ppm.org_payment_method_id
605 , p_ppm.assignment_id
606 , p_ppm.payment_type
607 , p_ppm.currency_code
608 , p_ppm.territory_code
609 , p_ppm.real_priority
610 , p_ppm.logical_priority
611 , p_ppm.amount_type
612 , p_ppm.amount
613 , p_ppm.external_account_id
614 , p_ppm.attribute_category
615 , p_ppm.attribute1
616 , p_ppm.attribute2
617 , p_ppm.attribute3
618 , p_ppm.attribute4
619 , p_ppm.attribute5
620 , p_ppm.attribute6
621 , p_ppm.attribute7
622 , p_ppm.attribute8
623 , p_ppm.attribute9
624 , p_ppm.attribute10
625 , p_ppm.attribute11
626 , p_ppm.attribute12
627 , p_ppm.attribute13
628 , p_ppm.attribute14
629 , p_ppm.attribute15
630 , p_ppm.attribute16
631 , p_ppm.attribute17
632 , p_ppm.attribute18
633 , p_ppm.attribute19
634 , p_ppm.attribute20
635 , p_ppm.o_real_priority
636 , p_ppm.o_logical_priority
637 , p_ppm.o_amount_type
638 , p_ppm.o_amount
639 , p_ppm.o_external_account_id
640 , p_ppm.o_attribute_category
641 , p_ppm.o_attribute1
642 , p_ppm.o_attribute2
643 , p_ppm.o_attribute3
644 , p_ppm.o_attribute4
645 , p_ppm.o_attribute5
646 , p_ppm.o_attribute6
647 , p_ppm.o_attribute7
648 , p_ppm.o_attribute8
649 , p_ppm.o_attribute9
650 , p_ppm.o_attribute10
651 , p_ppm.o_attribute11
652 , p_ppm.o_attribute12
653 , p_ppm.o_attribute13
654 , p_ppm.o_attribute14
655 , p_ppm.o_attribute15
656 , p_ppm.o_attribute16
657 , p_ppm.o_attribute17
658 , p_ppm.o_attribute18
659 , p_ppm.o_attribute19
660 , p_ppm.o_attribute20
661 , p_ppm.run_type_id
662 , p_ppm.ppm_information_category
663 , p_ppm.ppm_information1
664 , p_ppm.ppm_information2
665 , p_ppm.ppm_information3
666 , p_ppm.ppm_information4
667 , p_ppm.ppm_information5
668 , p_ppm.ppm_information6
669 , p_ppm.ppm_information7
670 , p_ppm.ppm_information8
671 , p_ppm.ppm_information9
672 , p_ppm.ppm_information10
673 , p_ppm.ppm_information11
674 , p_ppm.ppm_information12
675 , p_ppm.ppm_information13
676 , p_ppm.ppm_information14
677 , p_ppm.ppm_information15
678 , p_ppm.ppm_information16
679 , p_ppm.ppm_information17
680 , p_ppm.ppm_information18
681 , p_ppm.ppm_information19
682 , p_ppm.ppm_information20
683 , p_ppm.ppm_information21
684 , p_ppm.ppm_information22
685 , p_ppm.ppm_information23
686 , p_ppm.ppm_information24
687 , p_ppm.ppm_information25
688 , p_ppm.ppm_information26
689 , p_ppm.ppm_information27
690 , p_ppm.ppm_information28
691 , p_ppm.ppm_information29
692 , p_ppm.ppm_information30
693 , p_ppm.o_ppm_information_category
694 , p_ppm.o_ppm_information1
695 , p_ppm.o_ppm_information2
696 , p_ppm.o_ppm_information3
697 , p_ppm.o_ppm_information4
698 , p_ppm.o_ppm_information5
699 , p_ppm.o_ppm_information6
700 , p_ppm.o_ppm_information7
701 , p_ppm.o_ppm_information8
702 , p_ppm.o_ppm_information9
703 , p_ppm.o_ppm_information10
704 , p_ppm.o_ppm_information11
705 , p_ppm.o_ppm_information12
706 , p_ppm.o_ppm_information13
707 , p_ppm.o_ppm_information14
708 , p_ppm.o_ppm_information15
709 , p_ppm.o_ppm_information16
710 , p_ppm.o_ppm_information17
711 , p_ppm.o_ppm_information18
712 , p_ppm.o_ppm_information19
713 , p_ppm.o_ppm_information20
714 , p_ppm.o_ppm_information21
715 , p_ppm.o_ppm_information22
716 , p_ppm.o_ppm_information23
717 , p_ppm.o_ppm_information24
718 , p_ppm.o_ppm_information25
719 , p_ppm.o_ppm_information26
720 , p_ppm.o_ppm_information27
721 , p_ppm.o_ppm_information28
722 , p_ppm.o_ppm_information29
723 , p_ppm.o_ppm_information30
724 ;
725 close csr_ppm;
726 seterrorstage(l_proc, 'EXIT:SUCCESS', 20);
727 return;
728 exception
729 when others then
730 seterrorstage(l_proc, 'EXIT:FAIL', 30);
731 if csr_ppm%isopen then
732 close csr_ppm;
733 end if;
734 raise;
735 end;
736 -----------------------------< changed >-------------------------
737 --
738 -- {Start Of Comments}
739 --
740 -- Description:
741 -- Compares data used to check whether or not a PPM has changed.
742 --
743 -- Prerequisites:
744 -- None.
745 --
746 -- Post Success:
747 -- P_CHANGES is set to true if there are any differences.
748 -- P_BANK is set to true if the Bank Details differ.
749 --
750 -- Post Failure:
751 -- Not applicable.
752 --
753 -- Access Status:
754 -- Internal Development Use Only.
755 --
756 -- {End Of Comments}
757 --
758 procedure changed
759 (p_logical_priority in number
760 ,p_amount_type in varchar2
761 ,p_amount in number
762 ,p_external_account_id in number
763 ,p_attribute_category in varchar2
764 ,p_attribute1 in varchar2
765 ,p_attribute2 in varchar2
766 ,p_attribute3 in varchar2
767 ,p_attribute4 in varchar2
768 ,p_attribute5 in varchar2
769 ,p_attribute6 in varchar2
770 ,p_attribute7 in varchar2
771 ,p_attribute8 in varchar2
772 ,p_attribute9 in varchar2
773 ,p_attribute10 in varchar2
774 ,p_attribute11 in varchar2
775 ,p_attribute12 in varchar2
776 ,p_attribute13 in varchar2
777 ,p_attribute14 in varchar2
778 ,p_attribute15 in varchar2
779 ,p_attribute16 in varchar2
780 ,p_attribute17 in varchar2
781 ,p_attribute18 in varchar2
782 ,p_attribute19 in varchar2
783 ,p_attribute20 in varchar2
784 ,p_o_logical_priority in number
785 ,p_o_amount_type in varchar2
786 ,p_o_amount in number
787 ,p_o_external_account_id in number
788 ,p_o_attribute_category in varchar2
789 ,p_o_attribute1 in varchar2
790 ,p_o_attribute2 in varchar2
791 ,p_o_attribute3 in varchar2
792 ,p_o_attribute4 in varchar2
793 ,p_o_attribute5 in varchar2
794 ,p_o_attribute6 in varchar2
795 ,p_o_attribute7 in varchar2
796 ,p_o_attribute8 in varchar2
797 ,p_o_attribute9 in varchar2
798 ,p_o_attribute10 in varchar2
799 ,p_o_attribute11 in varchar2
800 ,p_o_attribute12 in varchar2
801 ,p_o_attribute13 in varchar2
802 ,p_o_attribute14 in varchar2
803 ,p_o_attribute15 in varchar2
804 ,p_o_attribute16 in varchar2
805 ,p_o_attribute17 in varchar2
806 ,p_o_attribute18 in varchar2
807 ,p_o_attribute19 in varchar2
808 ,p_o_attribute20 in varchar2
809 ,p_ppm_information_category in varchar2
810 ,p_ppm_information1 in varchar2
811 ,p_ppm_information2 in varchar2
812 ,p_ppm_information3 in varchar2
813 ,p_ppm_information4 in varchar2
814 ,p_ppm_information5 in varchar2
815 ,p_ppm_information6 in varchar2
816 ,p_ppm_information7 in varchar2
817 ,p_ppm_information8 in varchar2
818 ,p_ppm_information9 in varchar2
819 ,p_ppm_information10 in varchar2
820 ,p_ppm_information11 in varchar2
821 ,p_ppm_information12 in varchar2
822 ,p_ppm_information13 in varchar2
823 ,p_ppm_information14 in varchar2
824 ,p_ppm_information15 in varchar2
825 ,p_ppm_information16 in varchar2
826 ,p_ppm_information17 in varchar2
827 ,p_ppm_information18 in varchar2
828 ,p_ppm_information19 in varchar2
829 ,p_ppm_information20 in varchar2
830 ,p_ppm_information21 in varchar2
831 ,p_ppm_information22 in varchar2
832 ,p_ppm_information23 in varchar2
833 ,p_ppm_information24 in varchar2
834 ,p_ppm_information25 in varchar2
835 ,p_ppm_information26 in varchar2
836 ,p_ppm_information27 in varchar2
837 ,p_ppm_information28 in varchar2
838 ,p_ppm_information29 in varchar2
839 ,p_ppm_information30 in varchar2
840 ,p_o_ppm_information_category in varchar2
841 ,p_o_ppm_information1 in varchar2
842 ,p_o_ppm_information2 in varchar2
843 ,p_o_ppm_information3 in varchar2
844 ,p_o_ppm_information4 in varchar2
845 ,p_o_ppm_information5 in varchar2
846 ,p_o_ppm_information6 in varchar2
847 ,p_o_ppm_information7 in varchar2
848 ,p_o_ppm_information8 in varchar2
849 ,p_o_ppm_information9 in varchar2
850 ,p_o_ppm_information10 in varchar2
851 ,p_o_ppm_information11 in varchar2
852 ,p_o_ppm_information12 in varchar2
853 ,p_o_ppm_information13 in varchar2
854 ,p_o_ppm_information14 in varchar2
855 ,p_o_ppm_information15 in varchar2
856 ,p_o_ppm_information16 in varchar2
857 ,p_o_ppm_information17 in varchar2
858 ,p_o_ppm_information18 in varchar2
859 ,p_o_ppm_information19 in varchar2
860 ,p_o_ppm_information20 in varchar2
861 ,p_o_ppm_information21 in varchar2
862 ,p_o_ppm_information22 in varchar2
863 ,p_o_ppm_information23 in varchar2
864 ,p_o_ppm_information24 in varchar2
865 ,p_o_ppm_information25 in varchar2
866 ,p_o_ppm_information26 in varchar2
867 ,p_o_ppm_information27 in varchar2
868 ,p_o_ppm_information28 in varchar2
869 ,p_o_ppm_information29 in varchar2
870 ,p_o_ppm_information30 in varchar2
871 ,p_changes out nocopy boolean
872 ,p_bank out nocopy boolean
873 ) is
874 --
875 l_changes boolean := false;
876 l_bank boolean := false;
877 --
878 -- Local procedures to detect a changed column.
879 --
880 procedure ch
881 (p_value1 in number
882 ,p_value2 in number
883 ,p_change in out nocopy boolean
884 ) is
885 begin
886 --
887 -- The HR_API defaults are used to indicate no change.
888 --
889 if nvl(p_value1, hr_api.g_number) <> nvl(p_value2, hr_api.g_number) then
890 p_change := true;
891 end if;
892 end ch;
893 --
894 procedure ch
895 (p_value1 in varchar2
896 ,p_value2 in varchar2
897 ,p_change in out nocopy boolean
898 ) is
899 begin
900 --
901 -- The HR_API defaults are used to indicate no change.
902 --
903 if nvl(p_value1, hr_api.g_varchar2) <> nvl(p_value2, hr_api.g_varchar2) then
904 p_change := true;
905 end if;
906 end ch;
907 --
908 begin
909 ch(p_logical_priority, p_o_logical_priority, l_changes);
910 ch(p_amount, p_o_amount, l_changes);
911 ch(p_amount_type, p_o_amount_type, l_changes);
912 ch(p_external_account_id, p_o_external_account_id, l_bank);
913 ch(p_attribute_category, p_o_attribute_category, l_changes);
914 ch(p_attribute1, p_o_attribute1, l_changes);
915 ch(p_attribute2, p_o_attribute2, l_changes);
916 ch(p_attribute3, p_o_attribute3, l_changes);
917 ch(p_attribute4, p_o_attribute4, l_changes);
918 ch(p_attribute5, p_o_attribute5, l_changes);
919 ch(p_attribute6, p_o_attribute6, l_changes);
920 ch(p_attribute7, p_o_attribute7, l_changes);
921 ch(p_attribute8, p_o_attribute8, l_changes);
922 ch(p_attribute9, p_o_attribute9, l_changes);
923 ch(p_attribute10, p_o_attribute10, l_changes);
924 ch(p_attribute11, p_o_attribute11, l_changes);
925 ch(p_attribute12, p_o_attribute12, l_changes);
926 ch(p_attribute13, p_o_attribute13, l_changes);
927 ch(p_attribute14, p_o_attribute14, l_changes);
928 ch(p_attribute15, p_o_attribute15, l_changes);
929 ch(p_attribute16, p_o_attribute16, l_changes);
930 ch(p_attribute17, p_o_attribute17, l_changes);
931 ch(p_attribute18, p_o_attribute18, l_changes);
932 ch(p_attribute19, p_o_attribute19, l_changes);
933 ch(p_attribute20, p_o_attribute20, l_changes);
934
935 ch(p_ppm_information_category, p_o_ppm_information_category, l_changes);
936 ch(p_ppm_information1, p_o_ppm_information1, l_changes);
937 ch(p_ppm_information2, p_o_ppm_information2, l_changes);
938 ch(p_ppm_information3, p_o_ppm_information3, l_changes);
939 ch(p_ppm_information4, p_o_ppm_information4, l_changes);
940 ch(p_ppm_information5, p_o_ppm_information5, l_changes);
941 ch(p_ppm_information6, p_o_ppm_information6, l_changes);
942 ch(p_ppm_information7, p_o_ppm_information7, l_changes);
943 ch(p_ppm_information8, p_o_ppm_information8, l_changes);
944 ch(p_ppm_information9, p_o_ppm_information9, l_changes);
945 ch(p_ppm_information10, p_o_ppm_information10, l_changes);
946 ch(p_ppm_information11, p_o_ppm_information11, l_changes);
947 ch(p_ppm_information12, p_o_ppm_information12, l_changes);
948 ch(p_ppm_information13, p_o_ppm_information13, l_changes);
949 ch(p_ppm_information14, p_o_ppm_information14, l_changes);
950 ch(p_ppm_information15, p_o_ppm_information15, l_changes);
951 ch(p_ppm_information16, p_o_ppm_information16, l_changes);
952 ch(p_ppm_information17, p_o_ppm_information17, l_changes);
953 ch(p_ppm_information18, p_o_ppm_information18, l_changes);
954 ch(p_ppm_information19, p_o_ppm_information19, l_changes);
955 ch(p_ppm_information20, p_o_ppm_information20, l_changes);
956 ch(p_ppm_information22, p_o_ppm_information21, l_changes);
957 ch(p_ppm_information22, p_o_ppm_information22, l_changes);
958 ch(p_ppm_information23, p_o_ppm_information23, l_changes);
959 ch(p_ppm_information24, p_o_ppm_information24, l_changes);
960 ch(p_ppm_information25, p_o_ppm_information25, l_changes);
961 ch(p_ppm_information26, p_o_ppm_information26, l_changes);
962 ch(p_ppm_information27, p_o_ppm_information27, l_changes);
963 ch(p_ppm_information28, p_o_ppm_information28, l_changes);
964 ch(p_ppm_information29, p_o_ppm_information29, l_changes);
965 ch(p_ppm_information30, p_o_ppm_information30, l_changes);
966 --
967 -- Set the return values.
968 --
969 if l_bank then
970 l_changes := true;
971 end if;
972 p_bank := l_bank;
973 p_changes := l_changes;
974 end changed;
975 -----------------------------< changedppm >------------------------
976 procedure changedppm
977 (p_ppm in t_ppmv4
978 ,p_changes out nocopy boolean
979 ,p_bank out nocopy boolean
980 ) is
981 begin
982 --
983 -- Call private routine.
984 --
985 changed
986 (p_logical_priority => p_ppm.logical_priority
987 ,p_amount_type => p_ppm.amount_type
988 ,p_amount => p_ppm.amount
989 ,p_external_account_id => p_ppm.external_account_id
990 ,p_attribute_category => p_ppm.attribute_category
991 ,p_attribute1 => p_ppm.attribute1
992 ,p_attribute2 => p_ppm.attribute2
993 ,p_attribute3 => p_ppm.attribute3
994 ,p_attribute4 => p_ppm.attribute4
995 ,p_attribute5 => p_ppm.attribute5
996 ,p_attribute6 => p_ppm.attribute6
997 ,p_attribute7 => p_ppm.attribute7
998 ,p_attribute8 => p_ppm.attribute8
999 ,p_attribute9 => p_ppm.attribute9
1000 ,p_attribute10 => p_ppm.attribute10
1001 ,p_attribute11 => p_ppm.attribute11
1002 ,p_attribute12 => p_ppm.attribute12
1003 ,p_attribute13 => p_ppm.attribute13
1004 ,p_attribute14 => p_ppm.attribute14
1005 ,p_attribute15 => p_ppm.attribute15
1006 ,p_attribute16 => p_ppm.attribute16
1007 ,p_attribute17 => p_ppm.attribute17
1008 ,p_attribute18 => p_ppm.attribute18
1009 ,p_attribute19 => p_ppm.attribute19
1010 ,p_attribute20 => p_ppm.attribute20
1011 ,p_o_logical_priority => p_ppm.o_logical_priority
1012 ,p_o_amount_type => p_ppm.o_amount_type
1013 ,p_o_amount => p_ppm.o_amount
1014 ,p_o_external_account_id => p_ppm.o_external_account_id
1015 ,p_o_attribute_category => p_ppm.o_attribute_category
1016 ,p_o_attribute1 => p_ppm.o_attribute1
1017 ,p_o_attribute2 => p_ppm.o_attribute2
1018 ,p_o_attribute3 => p_ppm.o_attribute3
1019 ,p_o_attribute4 => p_ppm.o_attribute4
1020 ,p_o_attribute5 => p_ppm.o_attribute5
1021 ,p_o_attribute6 => p_ppm.o_attribute6
1022 ,p_o_attribute7 => p_ppm.o_attribute7
1023 ,p_o_attribute8 => p_ppm.o_attribute8
1024 ,p_o_attribute9 => p_ppm.o_attribute9
1025 ,p_o_attribute10 => p_ppm.o_attribute10
1026 ,p_o_attribute11 => p_ppm.o_attribute11
1027 ,p_o_attribute12 => p_ppm.o_attribute12
1028 ,p_o_attribute13 => p_ppm.o_attribute13
1029 ,p_o_attribute14 => p_ppm.o_attribute14
1030 ,p_o_attribute15 => p_ppm.o_attribute15
1031 ,p_o_attribute16 => p_ppm.o_attribute16
1032 ,p_o_attribute17 => p_ppm.o_attribute17
1033 ,p_o_attribute18 => p_ppm.o_attribute18
1034 ,p_o_attribute19 => p_ppm.o_attribute19
1035 ,p_o_attribute20 => p_ppm.o_attribute20
1036 ,p_ppm_information_category => p_ppm.ppm_information_category
1037 ,p_ppm_information1 => p_ppm.ppm_information1
1038 ,p_ppm_information2 => p_ppm.ppm_information2
1039 ,p_ppm_information3 => p_ppm.ppm_information3
1040 ,p_ppm_information4 => p_ppm.ppm_information4
1041 ,p_ppm_information5 => p_ppm.ppm_information5
1042 ,p_ppm_information6 => p_ppm.ppm_information6
1043 ,p_ppm_information7 => p_ppm.ppm_information7
1044 ,p_ppm_information8 => p_ppm.ppm_information8
1045 ,p_ppm_information9 => p_ppm.ppm_information9
1046 ,p_ppm_information10 => p_ppm.ppm_information10
1047 ,p_ppm_information11 => p_ppm.ppm_information11
1048 ,p_ppm_information12 => p_ppm.ppm_information12
1049 ,p_ppm_information13 => p_ppm.ppm_information13
1050 ,p_ppm_information14 => p_ppm.ppm_information14
1051 ,p_ppm_information15 => p_ppm.ppm_information15
1052 ,p_ppm_information16 => p_ppm.ppm_information16
1053 ,p_ppm_information17 => p_ppm.ppm_information17
1054 ,p_ppm_information18 => p_ppm.ppm_information18
1055 ,p_ppm_information19 => p_ppm.ppm_information19
1056 ,p_ppm_information20 => p_ppm.ppm_information20
1057 ,p_ppm_information21 => p_ppm.ppm_information21
1058 ,p_ppm_information22 => p_ppm.ppm_information22
1059 ,p_ppm_information23 => p_ppm.ppm_information23
1060 ,p_ppm_information24 => p_ppm.ppm_information24
1061 ,p_ppm_information25 => p_ppm.ppm_information25
1062 ,p_ppm_information26 => p_ppm.ppm_information26
1063 ,p_ppm_information27 => p_ppm.ppm_information27
1064 ,p_ppm_information28 => p_ppm.ppm_information28
1065 ,p_ppm_information29 => p_ppm.ppm_information29
1066 ,p_ppm_information30 => p_ppm.ppm_information30
1067 ,p_o_ppm_information_category => p_ppm.o_ppm_information_category
1068 ,p_o_ppm_information1 => p_ppm.o_ppm_information1
1069 ,p_o_ppm_information2 => p_ppm.o_ppm_information2
1070 ,p_o_ppm_information3 => p_ppm.o_ppm_information3
1071 ,p_o_ppm_information4 => p_ppm.o_ppm_information4
1072 ,p_o_ppm_information5 => p_ppm.o_ppm_information5
1073 ,p_o_ppm_information6 => p_ppm.o_ppm_information6
1074 ,p_o_ppm_information7 => p_ppm.o_ppm_information7
1075 ,p_o_ppm_information8 => p_ppm.o_ppm_information8
1076 ,p_o_ppm_information9 => p_ppm.o_ppm_information9
1077 ,p_o_ppm_information10 => p_ppm.o_ppm_information10
1078 ,p_o_ppm_information11 => p_ppm.o_ppm_information11
1079 ,p_o_ppm_information12 => p_ppm.o_ppm_information12
1080 ,p_o_ppm_information13 => p_ppm.o_ppm_information13
1081 ,p_o_ppm_information14 => p_ppm.o_ppm_information14
1082 ,p_o_ppm_information15 => p_ppm.o_ppm_information15
1083 ,p_o_ppm_information16 => p_ppm.o_ppm_information16
1084 ,p_o_ppm_information17 => p_ppm.o_ppm_information17
1085 ,p_o_ppm_information18 => p_ppm.o_ppm_information18
1086 ,p_o_ppm_information19 => p_ppm.o_ppm_information19
1087 ,p_o_ppm_information20 => p_ppm.o_ppm_information20
1088 ,p_o_ppm_information21 => p_ppm.o_ppm_information21
1089 ,p_o_ppm_information22 => p_ppm.o_ppm_information22
1090 ,p_o_ppm_information23 => p_ppm.o_ppm_information23
1091 ,p_o_ppm_information24 => p_ppm.o_ppm_information24
1092 ,p_o_ppm_information25 => p_ppm.o_ppm_information25
1093 ,p_o_ppm_information26 => p_ppm.o_ppm_information26
1094 ,p_o_ppm_information27 => p_ppm.o_ppm_information27
1095 ,p_o_ppm_information28 => p_ppm.o_ppm_information28
1096 ,p_o_ppm_information29 => p_ppm.o_ppm_information29
1097 ,p_o_ppm_information30 => p_ppm.o_ppm_information30
1098 ,p_changes => p_changes
1099 ,p_bank => p_bank
1100 );
1101 end changedppm;
1102 -----------------------------< changedppm >------------------------
1103 procedure changedppm
1104 (p_new_ppm in t_ppmv4
1105 ,p_saved_ppm in t_ppmv4
1106 ,p_original out nocopy boolean
1107 ,p_current out nocopy boolean
1108 ) is
1109 l_bank boolean;
1110 l_changes boolean;
1111 begin
1112 --
1113 -- Call private routine.
1114 --
1115 changed
1116 (p_logical_priority => hr_api.g_number
1117 ,p_amount_type => p_new_ppm.amount_type
1118 ,p_amount => p_new_ppm.amount
1119 ,p_external_account_id => p_new_ppm.external_account_id
1120 ,p_attribute_category => p_new_ppm.attribute_category
1121 ,p_attribute1 => p_new_ppm.attribute1
1122 ,p_attribute2 => p_new_ppm.attribute2
1123 ,p_attribute3 => p_new_ppm.attribute3
1124 ,p_attribute4 => p_new_ppm.attribute4
1125 ,p_attribute5 => p_new_ppm.attribute5
1126 ,p_attribute6 => p_new_ppm.attribute6
1127 ,p_attribute7 => p_new_ppm.attribute7
1128 ,p_attribute8 => p_new_ppm.attribute8
1129 ,p_attribute9 => p_new_ppm.attribute9
1130 ,p_attribute10 => p_new_ppm.attribute10
1131 ,p_attribute11 => p_new_ppm.attribute11
1132 ,p_attribute12 => p_new_ppm.attribute12
1133 ,p_attribute13 => p_new_ppm.attribute13
1134 ,p_attribute14 => p_new_ppm.attribute14
1135 ,p_attribute15 => p_new_ppm.attribute15
1136 ,p_attribute16 => p_new_ppm.attribute16
1137 ,p_attribute17 => p_new_ppm.attribute17
1138 ,p_attribute18 => p_new_ppm.attribute18
1139 ,p_attribute19 => p_new_ppm.attribute19
1140 ,p_attribute20 => p_new_ppm.attribute20
1141 ,p_o_logical_priority => hr_api.g_number
1142 ,p_o_amount_type => p_saved_ppm.o_amount_type
1143 ,p_o_amount => p_saved_ppm.o_amount
1144 ,p_o_external_account_id => p_saved_ppm.o_external_account_id
1145 ,p_o_attribute_category => p_saved_ppm.o_attribute_category
1146 ,p_o_attribute1 => p_saved_ppm.o_attribute1
1147 ,p_o_attribute2 => p_saved_ppm.o_attribute2
1148 ,p_o_attribute3 => p_saved_ppm.o_attribute3
1149 ,p_o_attribute4 => p_saved_ppm.o_attribute4
1150 ,p_o_attribute5 => p_saved_ppm.o_attribute5
1151 ,p_o_attribute6 => p_saved_ppm.o_attribute6
1152 ,p_o_attribute7 => p_saved_ppm.o_attribute7
1153 ,p_o_attribute8 => p_saved_ppm.o_attribute8
1154 ,p_o_attribute9 => p_saved_ppm.o_attribute9
1155 ,p_o_attribute10 => p_saved_ppm.o_attribute10
1156 ,p_o_attribute11 => p_saved_ppm.o_attribute11
1157 ,p_o_attribute12 => p_saved_ppm.o_attribute12
1158 ,p_o_attribute13 => p_saved_ppm.o_attribute13
1159 ,p_o_attribute14 => p_saved_ppm.o_attribute14
1160 ,p_o_attribute15 => p_saved_ppm.o_attribute15
1161 ,p_o_attribute16 => p_saved_ppm.o_attribute16
1162 ,p_o_attribute17 => p_saved_ppm.o_attribute17
1163 ,p_o_attribute18 => p_saved_ppm.o_attribute18
1164 ,p_o_attribute19 => p_saved_ppm.o_attribute19
1165 ,p_o_attribute20 => p_saved_ppm.o_attribute20
1166 ,p_ppm_information_category => p_new_ppm.ppm_information_category
1167 ,p_ppm_information1 => p_new_ppm.ppm_information1
1168 ,p_ppm_information2 => p_new_ppm.ppm_information2
1169 ,p_ppm_information3 => p_new_ppm.ppm_information3
1170 ,p_ppm_information4 => p_new_ppm.ppm_information4
1171 ,p_ppm_information5 => p_new_ppm.ppm_information5
1172 ,p_ppm_information6 => p_new_ppm.ppm_information6
1173 ,p_ppm_information7 => p_new_ppm.ppm_information7
1174 ,p_ppm_information8 => p_new_ppm.ppm_information8
1175 ,p_ppm_information9 => p_new_ppm.ppm_information9
1176 ,p_ppm_information10 => p_new_ppm.ppm_information10
1177 ,p_ppm_information11 => p_new_ppm.ppm_information11
1178 ,p_ppm_information12 => p_new_ppm.ppm_information12
1179 ,p_ppm_information13 => p_new_ppm.ppm_information13
1180 ,p_ppm_information14 => p_new_ppm.ppm_information14
1181 ,p_ppm_information15 => p_new_ppm.ppm_information15
1182 ,p_ppm_information16 => p_new_ppm.ppm_information16
1183 ,p_ppm_information17 => p_new_ppm.ppm_information17
1184 ,p_ppm_information18 => p_new_ppm.ppm_information18
1185 ,p_ppm_information19 => p_new_ppm.ppm_information19
1186 ,p_ppm_information20 => p_new_ppm.ppm_information20
1187 ,p_ppm_information21 => p_new_ppm.ppm_information21
1188 ,p_ppm_information22 => p_new_ppm.ppm_information22
1189 ,p_ppm_information23 => p_new_ppm.ppm_information23
1190 ,p_ppm_information24 => p_new_ppm.ppm_information24
1191 ,p_ppm_information25 => p_new_ppm.ppm_information25
1192 ,p_ppm_information26 => p_new_ppm.ppm_information26
1193 ,p_ppm_information27 => p_new_ppm.ppm_information27
1194 ,p_ppm_information28 => p_new_ppm.ppm_information28
1195 ,p_ppm_information29 => p_new_ppm.ppm_information29
1196 ,p_ppm_information30 => p_new_ppm.ppm_information30
1197 ,p_o_ppm_information_category => p_saved_ppm.o_ppm_information_category
1198 ,p_o_ppm_information1 => p_saved_ppm.o_ppm_information1
1199 ,p_o_ppm_information2 => p_saved_ppm.o_ppm_information2
1200 ,p_o_ppm_information3 => p_saved_ppm.o_ppm_information3
1201 ,p_o_ppm_information4 => p_saved_ppm.o_ppm_information4
1202 ,p_o_ppm_information5 => p_saved_ppm.o_ppm_information5
1203 ,p_o_ppm_information6 => p_saved_ppm.o_ppm_information6
1204 ,p_o_ppm_information7 => p_saved_ppm.o_ppm_information7
1205 ,p_o_ppm_information8 => p_saved_ppm.o_ppm_information8
1206 ,p_o_ppm_information9 => p_saved_ppm.o_ppm_information9
1207 ,p_o_ppm_information10 => p_saved_ppm.o_ppm_information10
1208 ,p_o_ppm_information11 => p_saved_ppm.o_ppm_information11
1209 ,p_o_ppm_information12 => p_saved_ppm.o_ppm_information12
1210 ,p_o_ppm_information13 => p_saved_ppm.o_ppm_information13
1211 ,p_o_ppm_information14 => p_saved_ppm.o_ppm_information14
1212 ,p_o_ppm_information15 => p_saved_ppm.o_ppm_information15
1213 ,p_o_ppm_information16 => p_saved_ppm.o_ppm_information16
1214 ,p_o_ppm_information17 => p_saved_ppm.o_ppm_information17
1215 ,p_o_ppm_information18 => p_saved_ppm.o_ppm_information18
1216 ,p_o_ppm_information19 => p_saved_ppm.o_ppm_information19
1217 ,p_o_ppm_information20 => p_saved_ppm.o_ppm_information20
1218 ,p_o_ppm_information21 => p_saved_ppm.o_ppm_information21
1219 ,p_o_ppm_information22 => p_saved_ppm.o_ppm_information22
1220 ,p_o_ppm_information23 => p_saved_ppm.o_ppm_information23
1221 ,p_o_ppm_information24 => p_saved_ppm.o_ppm_information24
1222 ,p_o_ppm_information25 => p_saved_ppm.o_ppm_information25
1223 ,p_o_ppm_information26 => p_saved_ppm.o_ppm_information26
1224 ,p_o_ppm_information27 => p_saved_ppm.o_ppm_information27
1225 ,p_o_ppm_information28 => p_saved_ppm.o_ppm_information28
1226 ,p_o_ppm_information29 => p_saved_ppm.o_ppm_information29
1227 ,p_o_ppm_information30 => p_saved_ppm.o_ppm_information30
1228 ,p_changes => l_changes
1229 ,p_bank => l_bank
1230 );
1231 p_original := l_changes;
1232 --
1233 changed
1234 (p_logical_priority => hr_api.g_number
1235 ,p_amount_type => p_new_ppm.amount_type
1236 ,p_amount => p_new_ppm.amount
1237 ,p_external_account_id => p_new_ppm.external_account_id
1238 ,p_attribute_category => p_new_ppm.attribute_category
1239 ,p_attribute1 => p_new_ppm.attribute1
1240 ,p_attribute2 => p_new_ppm.attribute2
1241 ,p_attribute3 => p_new_ppm.attribute3
1242 ,p_attribute4 => p_new_ppm.attribute4
1243 ,p_attribute5 => p_new_ppm.attribute5
1244 ,p_attribute6 => p_new_ppm.attribute6
1245 ,p_attribute7 => p_new_ppm.attribute7
1246 ,p_attribute8 => p_new_ppm.attribute8
1247 ,p_attribute9 => p_new_ppm.attribute9
1248 ,p_attribute10 => p_new_ppm.attribute10
1249 ,p_attribute11 => p_new_ppm.attribute11
1250 ,p_attribute12 => p_new_ppm.attribute12
1251 ,p_attribute13 => p_new_ppm.attribute13
1252 ,p_attribute14 => p_new_ppm.attribute14
1253 ,p_attribute15 => p_new_ppm.attribute15
1254 ,p_attribute16 => p_new_ppm.attribute16
1255 ,p_attribute17 => p_new_ppm.attribute17
1256 ,p_attribute18 => p_new_ppm.attribute18
1257 ,p_attribute19 => p_new_ppm.attribute19
1258 ,p_attribute20 => p_new_ppm.attribute20
1259 ,p_o_logical_priority => hr_api.g_number
1260 ,p_o_amount_type => p_saved_ppm.amount_type
1261 ,p_o_amount => p_saved_ppm.amount
1262 ,p_o_external_account_id => p_saved_ppm.external_account_id
1263 ,p_o_attribute_category => p_saved_ppm.attribute_category
1264 ,p_o_attribute1 => p_saved_ppm.attribute1
1265 ,p_o_attribute2 => p_saved_ppm.attribute2
1266 ,p_o_attribute3 => p_saved_ppm.attribute3
1267 ,p_o_attribute4 => p_saved_ppm.attribute4
1268 ,p_o_attribute5 => p_saved_ppm.attribute5
1269 ,p_o_attribute6 => p_saved_ppm.attribute6
1270 ,p_o_attribute7 => p_saved_ppm.attribute7
1271 ,p_o_attribute8 => p_saved_ppm.attribute8
1272 ,p_o_attribute9 => p_saved_ppm.attribute9
1273 ,p_o_attribute10 => p_saved_ppm.attribute10
1274 ,p_o_attribute11 => p_saved_ppm.attribute11
1275 ,p_o_attribute12 => p_saved_ppm.attribute12
1276 ,p_o_attribute13 => p_saved_ppm.attribute13
1277 ,p_o_attribute14 => p_saved_ppm.attribute14
1278 ,p_o_attribute15 => p_saved_ppm.attribute15
1279 ,p_o_attribute16 => p_saved_ppm.attribute16
1280 ,p_o_attribute17 => p_saved_ppm.attribute17
1281 ,p_o_attribute18 => p_saved_ppm.attribute18
1282 ,p_o_attribute19 => p_saved_ppm.attribute19
1283 ,p_o_attribute20 => p_saved_ppm.attribute20
1284 ,p_ppm_information_category => p_new_ppm.ppm_information_category
1285 ,p_ppm_information1 => p_new_ppm.ppm_information1
1286 ,p_ppm_information2 => p_new_ppm.ppm_information2
1287 ,p_ppm_information3 => p_new_ppm.ppm_information3
1288 ,p_ppm_information4 => p_new_ppm.ppm_information4
1289 ,p_ppm_information5 => p_new_ppm.ppm_information5
1290 ,p_ppm_information6 => p_new_ppm.ppm_information6
1291 ,p_ppm_information7 => p_new_ppm.ppm_information7
1292 ,p_ppm_information8 => p_new_ppm.ppm_information8
1293 ,p_ppm_information9 => p_new_ppm.ppm_information9
1294 ,p_ppm_information10 => p_new_ppm.ppm_information10
1295 ,p_ppm_information11 => p_new_ppm.ppm_information11
1296 ,p_ppm_information12 => p_new_ppm.ppm_information12
1297 ,p_ppm_information13 => p_new_ppm.ppm_information13
1298 ,p_ppm_information14 => p_new_ppm.ppm_information14
1299 ,p_ppm_information15 => p_new_ppm.ppm_information15
1300 ,p_ppm_information16 => p_new_ppm.ppm_information16
1301 ,p_ppm_information17 => p_new_ppm.ppm_information17
1302 ,p_ppm_information18 => p_new_ppm.ppm_information18
1303 ,p_ppm_information19 => p_new_ppm.ppm_information19
1304 ,p_ppm_information20 => p_new_ppm.ppm_information20
1305 ,p_ppm_information21 => p_new_ppm.ppm_information21
1306 ,p_ppm_information22 => p_new_ppm.ppm_information22
1307 ,p_ppm_information23 => p_new_ppm.ppm_information23
1308 ,p_ppm_information24 => p_new_ppm.ppm_information24
1309 ,p_ppm_information25 => p_new_ppm.ppm_information25
1310 ,p_ppm_information26 => p_new_ppm.ppm_information26
1311 ,p_ppm_information27 => p_new_ppm.ppm_information27
1312 ,p_ppm_information28 => p_new_ppm.ppm_information28
1313 ,p_ppm_information29 => p_new_ppm.ppm_information29
1314 ,p_ppm_information30 => p_new_ppm.ppm_information30
1315 ,p_o_ppm_information_category => p_saved_ppm.o_ppm_information_category
1316 ,p_o_ppm_information1 => p_saved_ppm.o_ppm_information1
1317 ,p_o_ppm_information2 => p_saved_ppm.o_ppm_information2
1318 ,p_o_ppm_information3 => p_saved_ppm.o_ppm_information3
1319 ,p_o_ppm_information4 => p_saved_ppm.o_ppm_information4
1320 ,p_o_ppm_information5 => p_saved_ppm.o_ppm_information5
1321 ,p_o_ppm_information6 => p_saved_ppm.o_ppm_information6
1322 ,p_o_ppm_information7 => p_saved_ppm.o_ppm_information7
1323 ,p_o_ppm_information8 => p_saved_ppm.o_ppm_information8
1324 ,p_o_ppm_information9 => p_saved_ppm.o_ppm_information9
1325 ,p_o_ppm_information10 => p_saved_ppm.o_ppm_information10
1326 ,p_o_ppm_information11 => p_saved_ppm.o_ppm_information11
1327 ,p_o_ppm_information12 => p_saved_ppm.o_ppm_information12
1328 ,p_o_ppm_information13 => p_saved_ppm.o_ppm_information13
1329 ,p_o_ppm_information14 => p_saved_ppm.o_ppm_information14
1330 ,p_o_ppm_information15 => p_saved_ppm.o_ppm_information15
1331 ,p_o_ppm_information16 => p_saved_ppm.o_ppm_information16
1332 ,p_o_ppm_information17 => p_saved_ppm.o_ppm_information17
1333 ,p_o_ppm_information18 => p_saved_ppm.o_ppm_information18
1334 ,p_o_ppm_information19 => p_saved_ppm.o_ppm_information19
1335 ,p_o_ppm_information20 => p_saved_ppm.o_ppm_information20
1336 ,p_o_ppm_information21 => p_saved_ppm.o_ppm_information21
1337 ,p_o_ppm_information22 => p_saved_ppm.o_ppm_information22
1338 ,p_o_ppm_information23 => p_saved_ppm.o_ppm_information23
1339 ,p_o_ppm_information24 => p_saved_ppm.o_ppm_information24
1340 ,p_o_ppm_information25 => p_saved_ppm.o_ppm_information25
1341 ,p_o_ppm_information26 => p_saved_ppm.o_ppm_information26
1342 ,p_o_ppm_information27 => p_saved_ppm.o_ppm_information27
1343 ,p_o_ppm_information28 => p_saved_ppm.o_ppm_information28
1344 ,p_o_ppm_information29 => p_saved_ppm.o_ppm_information29
1345 ,p_o_ppm_information30 => p_saved_ppm.o_ppm_information30
1346 ,p_changes => l_changes
1347 ,p_bank => l_bank
1348 );
1349 p_current := l_changes;
1350 end changedppm;
1351 -----------------------------< nextentry >--------------------------
1352 function nextentry
1353 (p_list in varchar2
1354 ,p_separator in varchar2
1355 ,p_start in out nocopy number
1356 ) return varchar2 is
1357 l_list long;
1358 l_entry long;
1359 l_end number;
1360 begin
1361 l_list := substr(p_list, p_start);
1362 l_end := instr(l_list, p_separator);
1363 --
1364 -- Separator not found - last entry in the list.
1365 --
1366 if nvl(l_end, 0) = 0 then
1367 l_entry := l_list;
1368 p_start := 0;
1369 else
1370 --
1371 -- Found separator, get the entry and reset the start of
1372 -- the list. Don't worry if the new start is beyond the
1373 -- end of the list.
1374 --
1375 l_entry := substr(l_list, 1, l_end-1);
1376 p_start := p_start + l_end;
1377 end if;
1378 return l_entry;
1379 end nextentry;
1380 ------------------------< read_wf_config_option >------------------------
1381 function read_wf_config_option
1382 (p_item_type in varchar2
1383 ,p_item_key in varchar2
1384 ,p_activity_id in number default null
1385 ,p_option in varchar2
1386 ,p_number in boolean default false
1387 ) return varchar2 is
1388 l_value long;
1389 l_exists boolean;
1390 l_subtype wf_activity_attributes.subtype%type;
1391 l_type wf_activity_attributes.type%type;
1392 l_format wf_activity_attributes.format%type;
1393 l_date wf_activity_attr_values.date_value%type;
1394 l_number wf_activity_attr_values.number_value%type;
1395 begin
1396 if p_activity_id is not null then
1397 --
1398 -- Look for activity attribute first.
1399 --
1400 hr_mee_workflow_service.get_act_attr_expanded_info
1401 (p_item_type => p_item_type
1402 ,p_item_key => p_item_key
1403 ,p_actid => p_activity_id
1404 ,p_name => p_option
1405 ,p_exists => l_exists
1406 ,p_subtype => l_subtype
1407 ,p_type => l_type
1408 ,p_format => l_format
1409 ,p_date_value => l_date
1410 ,p_number_value => l_number
1411 ,p_text_value => l_value
1412 );
1413 if l_exists then
1414 if l_type = 'NUMBER' then
1415 l_value := to_char(l_number);
1416 elsif l_type = 'DATE' then
1417 l_value := to_char(l_date, hr_transaction_ss.g_date_format);
1418 end if;
1419 return l_value;
1420 end if;
1421 end if;
1422 --
1423 -- Just look for item attribute.
1424 --
1425 if hr_workflow_utility.item_attribute_exists
1426 (p_item_type => p_item_type
1427 ,p_item_key => p_item_key
1428 ,p_name => p_option
1429 )
1430 then
1431 if p_number then
1432 l_number := wf_engine.getitemattrnumber
1433 (itemtype => p_item_type
1434 ,itemkey => p_item_key
1435 ,aname => p_option
1436 );
1437 l_value := to_char(l_number);
1438 else
1439 l_value := wf_engine.getitemattrtext
1440 (itemtype => p_item_type
1441 ,itemkey => p_item_key
1442 ,aname => p_option
1443 );
1444 end if;
1445 return l_value;
1446 end if;
1447 --
1448 -- Display a fatal error.
1449 --
1450 hr_utility.set_message
1451 (applid => 800
1452 ,l_message_name => 'PAY_52631_PPMSS_OPTION_ERROR'
1453 );
1454 hr_utility.set_message_token
1455 (l_token_name => 'OPTION'
1456 ,l_token_value => p_option
1457 );
1458 hr_utility.raise_error;
1459 exception
1460 when others then
1461 raise;
1462 end read_wf_config_option;
1463 ----------------------< getpriorities >-----------------------
1464 procedure getpriorities
1465 (p_assignment_id in number
1466 ,p_effective_date in date
1467 ,p_run_type_id in number default null
1468 ,p_priority_tbl out nocopy t_boolean_tbl
1469 ,p_first_available out nocopy number
1470 ) is
1471 cursor csr_priorities
1472 (p_assignment_id in number
1473 ,p_effective_date in date
1474 ,p_run_type_id in number
1475 ) is
1476 select ppm.priority priority
1477 from pay_personal_payment_methods_f ppm
1478 , pay_org_payment_methods_f opm
1479 where ppm.assignment_id = p_assignment_id
1480 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
1481 and p_effective_date between
1482 ppm.effective_start_date and ppm.effective_end_date
1483 and opm.org_payment_method_id = ppm.org_payment_method_id
1484 and opm.defined_balance_id is not null
1485 and p_effective_date between
1486 opm.effective_start_date and opm.effective_end_date
1487 order by priority
1488 ;
1489 --
1490 l_priority_tbl t_boolean_tbl;
1491 l_first_available number;
1492 l_proc varchar2(2000) := g_package||'getpriorities';
1493 begin
1494 seterrorstage(l_proc, 'ENTER', 0);
1495 --
1496 -- Initialise the priority table.
1497 --
1498 for i in C_MIN_PRIORITY .. C_MAX_PRIORITY loop
1499 l_priority_tbl(i) := true;
1500 end loop;
1501 --
1502 -- Get the existing priorities.
1503 --
1504 seterrorstage(l_proc, 'FETCH_PRIORITIES', 10);
1505 l_first_available := C_MIN_PRIORITY;
1506 for crec in csr_priorities
1507 (p_assignment_id => p_assignment_id
1508 ,p_effective_date => p_effective_date
1509 ,p_run_type_id => p_run_type_id
1510 ) loop
1511 --
1512 -- Avoid adding entries for values < C_MIN_PRIORITY.
1513 --
1514 if crec.priority >= C_MIN_PRIORITY then
1515 l_priority_tbl(crec.priority) := false;
1516 end if;
1517 if crec.priority = l_first_available then
1518 l_first_available := l_first_available + 1;
1519 end if;
1520 end loop;
1521 p_first_available := l_first_available;
1522 p_priority_tbl := l_priority_tbl;
1523 seterrorstage(l_proc, 'EXIT:SUCCESS', 20);
1524 return;
1525 exception
1526 when others then
1527 seterrorstage(l_proc, 'EXIT:FAIL', 30);
1528 raise;
1529 end getpriorities;
1530 -----------------------------< validateppm >------------------------
1531 procedure validateppm
1532 (p_state in varchar2
1533 ,p_personal_payment_method_id in number default null
1534 ,p_object_version_number in number default null
1535 ,p_update_datetrack_mode in varchar2 default null
1536 ,p_effective_date in date default null
1537 ,p_org_payment_method_id in number default null
1538 ,p_assignment_id in number default null
1539 ,p_run_type_id in number default null
1540 ,p_payment_type in varchar2 default null
1541 ,p_territory_code in varchar2 default null
1542 ,p_amount_type in varchar2 default null
1543 ,p_amount in number default null
1544 ,p_external_account_id in number default null
1545 ,p_attribute_category in varchar2 default null
1546 ,p_attribute1 in varchar2 default null
1547 ,p_attribute2 in varchar2 default null
1548 ,p_attribute3 in varchar2 default null
1549 ,p_attribute4 in varchar2 default null
1550 ,p_attribute5 in varchar2 default null
1551 ,p_attribute6 in varchar2 default null
1552 ,p_attribute7 in varchar2 default null
1553 ,p_attribute8 in varchar2 default null
1554 ,p_attribute9 in varchar2 default null
1555 ,p_attribute10 in varchar2 default null
1556 ,p_attribute11 in varchar2 default null
1557 ,p_attribute12 in varchar2 default null
1558 ,p_attribute13 in varchar2 default null
1559 ,p_attribute14 in varchar2 default null
1560 ,p_attribute15 in varchar2 default null
1561 ,p_attribute16 in varchar2 default null
1562 ,p_attribute17 in varchar2 default null
1563 ,p_attribute18 in varchar2 default null
1564 ,p_attribute19 in varchar2 default null
1565 ,p_attribute20 in varchar2 default null
1566 ,p_segment1 in varchar2 default null
1567 ,p_segment2 in varchar2 default null
1568 ,p_segment3 in varchar2 default null
1569 ,p_segment4 in varchar2 default null
1570 ,p_segment5 in varchar2 default null
1571 ,p_segment6 in varchar2 default null
1572 ,p_segment7 in varchar2 default null
1573 ,p_segment8 in varchar2 default null
1574 ,p_segment9 in varchar2 default null
1575 ,p_segment10 in varchar2 default null
1576 ,p_segment11 in varchar2 default null
1577 ,p_segment12 in varchar2 default null
1578 ,p_segment13 in varchar2 default null
1579 ,p_segment14 in varchar2 default null
1580 ,p_segment15 in varchar2 default null
1581 ,p_segment16 in varchar2 default null
1582 ,p_segment17 in varchar2 default null
1583 ,p_segment18 in varchar2 default null
1584 ,p_segment19 in varchar2 default null
1585 ,p_segment20 in varchar2 default null
1586 ,p_segment21 in varchar2 default null
1587 ,p_segment22 in varchar2 default null
1588 ,p_segment23 in varchar2 default null
1589 ,p_segment24 in varchar2 default null
1590 ,p_segment25 in varchar2 default null
1591 ,p_segment26 in varchar2 default null
1592 ,p_segment27 in varchar2 default null
1593 ,p_segment28 in varchar2 default null
1594 ,p_segment29 in varchar2 default null
1595 ,p_segment30 in varchar2 default null
1596 ,p_ppm_information_category in varchar2 default null
1597 ,p_ppm_information1 in varchar2 default null
1598 ,p_ppm_information2 in varchar2 default null
1599 ,p_ppm_information3 in varchar2 default null
1600 ,p_ppm_information4 in varchar2 default null
1601 ,p_ppm_information5 in varchar2 default null
1602 ,p_ppm_information6 in varchar2 default null
1603 ,p_ppm_information7 in varchar2 default null
1604 ,p_ppm_information8 in varchar2 default null
1605 ,p_ppm_information9 in varchar2 default null
1606 ,p_ppm_information10 in varchar2 default null
1607 ,p_ppm_information11 in varchar2 default null
1608 ,p_ppm_information12 in varchar2 default null
1609 ,p_ppm_information13 in varchar2 default null
1610 ,p_ppm_information14 in varchar2 default null
1611 ,p_ppm_information15 in varchar2 default null
1612 ,p_ppm_information16 in varchar2 default null
1613 ,p_ppm_information17 in varchar2 default null
1614 ,p_ppm_information18 in varchar2 default null
1615 ,p_ppm_information19 in varchar2 default null
1616 ,p_ppm_information20 in varchar2 default null
1617 ,p_ppm_information21 in varchar2 default null
1618 ,p_ppm_information22 in varchar2 default null
1619 ,p_ppm_information23 in varchar2 default null
1620 ,p_ppm_information24 in varchar2 default null
1621 ,p_ppm_information25 in varchar2 default null
1622 ,p_ppm_information26 in varchar2 default null
1623 ,p_ppm_information27 in varchar2 default null
1624 ,p_ppm_information28 in varchar2 default null
1625 ,p_ppm_information29 in varchar2 default null
1626 ,p_ppm_information30 in varchar2 default null
1627 ,p_return_status out nocopy varchar2
1628 ,p_msg_count out nocopy number
1629 ,p_msg_data out nocopy varchar2
1630 ) is
1631 --
1632 -- Cursor to get business_group_id from assignment_id and
1633 -- effective_date. This is required for the INSERT operation.
1634 --
1635 cursor csr_asg_busgrp
1636 (p_assignment_id in number
1637 ,p_effective_date in date
1638 ) is
1639 select asg.business_group_id
1640 from per_all_assignments_f asg
1641 where asg.assignment_id = p_assignment_id
1642 and p_effective_date between
1643 asg.effective_start_date and asg.effective_end_date
1644 ;
1645 --
1646 -- Cursor to get business_group_id from the personal_payment_method_id
1647 -- and effective_date. This is required for the UPDATE operation.
1648 --
1649 cursor csr_ppm_busgrp
1650 (p_personal_payment_method_id in number
1651 ,p_effective_date in date
1652 ) is
1653 select ppm.business_group_id
1654 from pay_personal_payment_methods_f ppm
1655 where ppm.personal_payment_method_id = p_personal_payment_method_id
1656 and p_effective_date between
1657 ppm.effective_start_date and ppm.effective_end_date
1658 ;
1659 --
1660 l_business_group_id number;
1661 l_rec pay_ppm_shd.g_rec_type;
1662 l_datetrack_mode varchar2(2000);
1663 l_validation_start_date date;
1664 l_validation_end_date date;
1665 l_message_name varchar2(2000);
1666 l_column varchar2(2000);
1667 l_prompt varchar2(2000);
1668 --
1669 -- Percentage/Amount values.
1670 --
1671 l_amount number;
1672 l_percentage number;
1673 --
1674 -- The following are required for bank details segment validation.
1675 --
1676 l_external_account_id number;
1677 l_external_account_ovn number;
1678 l_exa_user_error boolean := false;
1679 l_user_error boolean := false;
1680 --
1681 -- API default values.
1682 --
1683 l_default_number number;
1684 l_default_varchar2 varchar2(2000);
1685 --
1686 -- Transit code checking.
1687 --
1688 l_check_digit number;
1689 l_transit_code_sum number;
1690 --
1691 l_proc varchar2(2000) := g_package || 'validateppm';
1692 begin
1693 --
1694 savepoint start_validate;
1695 --
1696 -- Set up default values by operation and type.
1697 --
1698 seterrorstage(l_proc, 'ENTER', 0);
1699 if p_state = pay_pss_tx_steps_pkg.C_STATE_NEW then
1700 seterrorstage(l_proc, 'C_STATE_NEW', 10);
1701 l_default_number := null;
1702 l_default_varchar2 := null;
1703 l_datetrack_mode := hr_api.g_insert;
1704 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_UPDATED then
1705 seterrorstage(l_proc, 'C_STATE_UPDATED', 20);
1706 l_default_number := hr_api.g_number;
1707 l_default_varchar2 := hr_api.g_varchar2;
1708 l_datetrack_mode := p_update_datetrack_mode;
1709 else
1710 --
1711 -- Should not reach here.
1712 --
1713 seterrorstage(l_proc, 'STATE:'||p_state, 30);
1714 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
1715 fnd_message.set_token('WHERE', l_proc);
1716 fnd_message.set_token('ADDITIONAL_INFO', '<p_state = ' || p_state || '>');
1717 fnd_msg_pub.add;
1718 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1719 fnd_msg_pub.count_and_get
1720 (p_count => p_msg_count
1721 ,p_data => p_msg_data
1722 );
1723 return;
1724 end if;
1725 --
1726 -- Set up Percent/Amount validation.
1727 --
1728 if p_amount_type = pay_pss_tx_steps_pkg.C_PERCENTAGE or
1729 p_amount_type = pay_pss_tx_steps_pkg.C_PERCENTAGE_ONLY then
1730 seterrorstage(l_proc, 'C_PERCENTAGE', 40);
1731 l_amount := null;
1732 l_percentage := p_amount;
1733 elsif p_amount_type = pay_pss_tx_steps_pkg.C_MONETARY or
1734 p_amount_type = pay_pss_tx_steps_pkg.C_MONETARY_ONLY then
1735 seterrorstage(l_proc, 'C_MONETARY', 50);
1736 l_amount := p_amount;
1737 l_percentage := null;
1738 elsif p_amount_type = pay_pss_tx_steps_pkg.C_REMAINING_PAY then
1739 --
1740 -- Handled as 100%.
1741 --
1742 seterrorstage(l_proc, 'C_REMAINING_PAY', 60);
1743 l_amount := null;
1744 l_percentage := 100;
1745 else
1746 --
1747 -- Should not reach here.
1748 --
1749 seterrorstage(l_proc, 'AMOUNT_TYPE:'||p_amount_type, 70);
1750 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
1751 fnd_message.set_token('WHERE', l_proc);
1752 fnd_message.set_token
1753 ('ADDITIONAL_INFO', '<p_amount_type = ' || p_amount_type || '>');
1754 fnd_msg_pub.add;
1755 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1756 fnd_msg_pub.count_and_get
1757 (p_count => p_msg_count
1758 ,p_data => p_msg_data
1759 );
1760 return;
1761 end if;
1762 --
1763 -- Set up the business_group_id for the INSERT and UPDATE operations.
1764 --
1765 if p_state = pay_pss_tx_steps_pkg.C_STATE_NEW then
1766 --
1767 -- Check for (fatal) business_group_id error. If this error
1768 -- occurs then the module is broken.
1769 --
1770 seterrorstage(l_proc, 'ASG_BUSGRPID', 80);
1771 begin
1772 open csr_asg_busgrp
1773 (p_assignment_id => p_assignment_id
1774 ,p_effective_date => p_effective_date
1775 );
1776 fetch csr_asg_busgrp
1777 into l_business_group_id;
1778 close csr_asg_busgrp;
1779 hr_api.validate_bus_grp_id
1780 (p_business_group_id => l_business_group_id
1781 );
1782 exception
1783 when others then
1784 if csr_asg_busgrp%isopen then
1785 close csr_asg_busgrp;
1786 end if;
1787 raise;
1788 end;
1789 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_UPDATED then
1790 --
1791 -- Check for (fatal) business_group_id error. If this error
1792 -- occurs then the module is broken.
1793 --
1794 seterrorstage(l_proc, 'PPM_BUSGRPID', 90);
1795 begin
1796 open csr_ppm_busgrp
1797 (p_personal_payment_method_id => p_personal_payment_method_id
1798 ,p_effective_date => p_effective_date
1799 );
1800 fetch csr_ppm_busgrp
1801 into l_business_group_id;
1802 close csr_ppm_busgrp;
1803 hr_api.validate_bus_grp_id
1804 (p_business_group_id => l_business_group_id
1805 );
1806 exception
1807 when others then
1808 if csr_ppm_busgrp%isopen then
1809 close csr_ppm_busgrp;
1810 end if;
1811 raise;
1812 end;
1813 end if;
1814 --
1815 -- Set up the internal PPM record structure.
1816 --
1817 seterrorstage(l_proc, 'CONVERT_ARGS', 100);
1818 l_rec := pay_ppm_shd.convert_args
1819 (p_personal_payment_method_id =>
1820 nvl(p_personal_payment_method_id, l_default_number)
1821 ,p_effective_start_date => null
1822 ,p_effective_end_date => null
1823 ,p_business_group_id => l_business_group_id
1824 ,p_external_account_id => l_external_account_id
1825 ,p_assignment_id => nvl(p_assignment_id, l_default_number)
1826 ,p_run_type_id => p_run_type_id
1827 ,p_org_payment_method_id => nvl(p_org_payment_method_id, l_default_number)
1828 ,p_amount => l_amount
1829 ,p_comment_id => l_default_number
1830 ,p_comments => l_default_varchar2
1831 ,p_percentage => l_percentage
1832 ,p_priority => hr_api.g_number
1833 ,p_attribute_category => p_attribute_category
1834 ,p_attribute1 => p_attribute1
1835 ,p_attribute2 => p_attribute2
1836 ,p_attribute3 => p_attribute3
1837 ,p_attribute4 => p_attribute4
1838 ,p_attribute5 => p_attribute5
1839 ,p_attribute6 => p_attribute6
1840 ,p_attribute7 => p_attribute7
1841 ,p_attribute8 => p_attribute8
1842 ,p_attribute9 => p_attribute9
1843 ,p_attribute10 => p_attribute10
1844 ,p_attribute11 => p_attribute11
1845 ,p_attribute12 => p_attribute12
1846 ,p_attribute13 => p_attribute13
1847 ,p_attribute14 => p_attribute14
1848 ,p_attribute15 => p_attribute15
1849 ,p_attribute16 => p_attribute16
1850 ,p_attribute17 => p_attribute17
1851 ,p_attribute18 => p_attribute18
1852 ,p_attribute19 => p_attribute19
1853 ,p_attribute20 => p_attribute20
1854 ,p_object_version_number => nvl(p_object_version_number,l_default_number)
1855 ,p_payee_type => l_default_varchar2
1856 ,p_payee_id => l_default_number
1857 ,p_ppm_information_category => p_ppm_information_category
1858 ,p_ppm_information1 => p_ppm_information1
1859 ,p_ppm_information2 => p_ppm_information2
1860 ,p_ppm_information3 => p_ppm_information3
1861 ,p_ppm_information4 => p_ppm_information4
1862 ,p_ppm_information5 => p_ppm_information5
1863 ,p_ppm_information6 => p_ppm_information6
1864 ,p_ppm_information7 => p_ppm_information7
1865 ,p_ppm_information8 => p_ppm_information8
1866 ,p_ppm_information9 => p_ppm_information9
1867 ,p_ppm_information10 => p_ppm_information10
1868 ,p_ppm_information11 => p_ppm_information11
1869 ,p_ppm_information12 => p_ppm_information12
1870 ,p_ppm_information13 => p_ppm_information13
1871 ,p_ppm_information14 => p_ppm_information14
1872 ,p_ppm_information15 => p_ppm_information15
1873 ,p_ppm_information16 => p_ppm_information16
1874 ,p_ppm_information17 => p_ppm_information17
1875 ,p_ppm_information18 => p_ppm_information18
1876 ,p_ppm_information19 => p_ppm_information19
1877 ,p_ppm_information20 => p_ppm_information20
1878 ,p_ppm_information21 => p_ppm_information21
1879 ,p_ppm_information22 => p_ppm_information22
1880 ,p_ppm_information23 => p_ppm_information23
1881 ,p_ppm_information24 => p_ppm_information24
1882 ,p_ppm_information25 => p_ppm_information25
1883 ,p_ppm_information26 => p_ppm_information26
1884 ,p_ppm_information27 => p_ppm_information27
1885 ,p_ppm_information28 => p_ppm_information28
1886 ,p_ppm_information29 => p_ppm_information29
1887 ,p_ppm_information30 => p_ppm_information30);
1888
1889 --
1890 -- Do initial checks specific to each operation.
1891 --
1892 if p_state = pay_pss_tx_steps_pkg.C_STATE_NEW then
1893 --
1894 -- Do the record locking. If this code fails then it is fatal
1895 -- error within the module.
1896 --
1897 seterrorstage(l_proc, 'INS:INITIAL', 110);
1898 begin
1899 pay_ppm_ins.ins_lck
1900 (p_effective_date => p_effective_date
1901 ,p_datetrack_mode => l_datetrack_mode
1902 ,p_rec => l_rec
1903 ,p_validation_start_date => l_validation_start_date
1904 ,p_validation_end_date => l_validation_end_date
1905 );
1906 --
1907 l_validation_end_date := pay_ppm_bus.return_effective_end_date
1908 (p_datetrack_mode => l_datetrack_mode
1909 ,p_effective_date => p_effective_date
1910 ,p_org_payment_method_id => l_rec.org_payment_method_id
1911 ,p_business_group_id => l_rec.business_group_id
1912 ,p_personal_payment_method_id => l_rec.personal_payment_method_id
1913 ,p_assignment_id => l_rec.assignment_id
1914 ,p_run_type_id => l_rec.run_type_id
1915 ,p_priority => l_rec.priority
1916 ,p_validation_start_date => l_validation_start_date
1917 ,p_validation_end_date => l_validation_end_date
1918 );
1919 exception
1920 when others then
1921 raise;
1922 end;
1923 --
1924 -- Check for (fatal) org_payment_method_id error. The module is
1925 -- broken if a correct value for org_payment_method_id is not
1926 -- supplied because the org_payment_method_id select list is
1927 -- generated.
1928 --
1929 begin
1930 pay_ppm_bus.chk_org_payment_method_id
1931 (p_business_group_id => l_business_group_id
1932 ,p_org_payment_method_id => l_rec.org_payment_method_id
1933 ,p_effective_date => p_effective_date
1934 );
1935 exception
1936 when others then
1937 raise;
1938 end;
1939 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_UPDATED then
1940 --
1941 -- Do the record locking. If this code fails then it is fatal
1942 -- error within the module.
1943 --
1944 seterrorstage(l_proc, 'UPD:INITIAL', 120);
1945 begin
1946 pay_ppm_shd.lck
1947 (p_effective_date => p_effective_date
1948 ,p_datetrack_mode => l_datetrack_mode
1949 ,p_personal_payment_method_id => l_rec.personal_payment_method_id
1950 ,p_object_version_number => l_rec.object_version_number
1951 ,p_validation_start_date => l_validation_start_date
1952 ,p_validation_end_date => l_validation_end_date
1953 );
1954 exception
1955 when others then
1956 raise;
1957 end;
1958 --
1959 -- Convert the default values to the actual values.
1960 --
1961 pay_ppm_upd.convert_defs(p_rec => l_rec);
1962 --
1963 -- Check for (fatal) error in the arguments that may not be updated.
1964 -- Such an error indicates a bug in the module.
1965 --
1966 seterrorstage(l_proc, 'CHK_NON_UPDATEABLE', 130);
1967 begin
1968 pay_ppm_bus.check_non_updateable_args
1969 (p_rec => l_rec
1970 ,p_effective_date => p_effective_date
1971 );
1972 exception
1973 when others then
1974 raise;
1975 end;
1976 end if;
1977 --
1978 -- Now call the segment validation code.
1979 --
1980 seterrorstage(l_proc, 'VALIDATE_BANK_SEGMENTS', 150);
1981 begin
1982 --
1983 if p_payment_type = pay_pss_tx_steps_pkg.C_DEPOSIT then
1984 --
1985 -- The OA key flex code inserts into the combination table: therefore,
1986 -- p_external_account_id refers to an existing row in
1987 -- PAY_EXTERNAL_ACCOUNTS.
1988 --
1989 l_external_account_id := p_external_account_id;
1990 pay_exa_upd.upd_or_sel
1991 (p_segment1 => p_segment1
1992 ,p_segment2 => p_segment2
1993 ,p_segment3 => p_segment3
1994 ,p_segment4 => p_segment4
1995 ,p_segment5 => p_segment5
1996 ,p_segment6 => p_segment6
1997 ,p_segment7 => p_segment7
1998 ,p_segment8 => p_segment8
1999 ,p_segment9 => p_segment9
2000 ,p_segment10 => p_segment10
2001 ,p_segment11 => p_segment11
2002 ,p_segment12 => p_segment12
2003 ,p_segment13 => p_segment13
2004 ,p_segment14 => p_segment14
2005 ,p_segment15 => p_segment15
2006 ,p_segment16 => p_segment16
2007 ,p_segment17 => p_segment17
2008 ,p_segment18 => p_segment18
2009 ,p_segment19 => p_segment19
2010 ,p_segment20 => p_segment20
2011 ,p_segment21 => p_segment21
2012 ,p_segment22 => p_segment22
2013 ,p_segment23 => p_segment23
2014 ,p_segment24 => p_segment24
2015 ,p_segment25 => p_segment25
2016 ,p_segment26 => p_segment26
2017 ,p_segment27 => p_segment27
2018 ,p_segment28 => p_segment28
2019 ,p_segment29 => p_segment29
2020 ,p_segment30 => p_segment30
2021 ,p_concat_segments => null
2022 ,p_business_group_id => l_business_group_id
2023 ,p_territory_code => p_territory_code
2024 ,p_external_account_id => l_external_account_id
2025 ,p_object_version_number => l_external_account_ovn
2026 ,p_prenote_date => null
2027 ,p_validate => false
2028 );
2029 end if;
2030 exception
2031 when others then
2032 l_exa_user_error := true;
2033 l_user_error := true;
2034 l_external_account_id := null;
2035 hr_message.provide_error;
2036 l_message_name := hr_message.last_message_name;
2037 --
2038 -- Can set field-level errors for US and GB segments.
2039 --
2040 if (p_territory_code = 'US' and
2041 ( l_message_name = 'HR_51458_EXA_US_ACCT_NAME_LONG' or
2042 l_message_name = 'HR_51459_EXA_US_ACCT_TYPE_LONG' or
2043 l_message_name = 'HR_51460_EXA_US_ACC_TYP_UNKNOW' or
2044 l_message_name = 'HR_51461_EXA_US_ACCT_NO_LONG' or
2045 l_message_name = 'HR_51462_EXA_US_TRAN_CODE_LONG' or
2046 l_message_name = 'HR_51463_EXA_US_BANK_NAME_LONG' or
2047 l_message_name = 'HR_51464_EXA_US_BANK_BRAN_LONG'
2048 )
2049 ) or
2050 (p_territory_code = 'GB' and
2051 ( l_message_name = 'HR_51416_EXA_BANK_NAME_LONG' or
2052 l_message_name = 'HR_51417_EXA_BANK_NAME_UNKNOWN' or
2053 l_message_name = 'HR_51418_EXA_BANK_BRANCH_LONG' or
2054 l_message_name = 'HR_51419_EXA_SORT_CODE_LENGTH' or
2055 l_message_name = 'HR_51420_EXA_SORT_CODE_POSITVE' or
2056 l_message_name = 'HR_51421_EXA_ACCOUNT_NO_LONG' or
2057 l_message_name = 'HR_51422_EXA_ACCT_NO_POSITIVE' or
2058 l_message_name = 'HR_51423_EXA_ACCOUNT_NAME_LONG' or
2059 l_message_name = 'HR_51424_EXA_ACCOUNT_NAME_CASE' or
2060 l_message_name = 'HR_51425_EXA_ACCOUNT_TYPE_LONG' or
2061 l_message_name = 'HR_51426_EXA_ACCT_TYPE_RANGE' or
2062 l_message_name = 'HR_51427_EXA_BS_ACCT_NO_LONG' or
2063 l_message_name = 'HR_51428_EXA_BS_ACCT_NO_CASE' or
2064 l_message_name = 'HR_51429_EXA_BANK_LOC_LONG' or
2065 l_message_name = 'HR_51430_EXA_BANK_LOC_UNKNOWN'
2066 )
2067 )
2068 then
2069 fnd_msg_pub.add;
2070 end if;
2071 --
2072 -- Handle generic flexfield errors.
2073 --
2074 if l_message_name = 'HR_FLEX_VALUE_MISSING' then
2075 fnd_message.set_name('PER', 'HR_WEB_REQUIRED_FIELD');
2076 fnd_msg_pub.add;
2077 elsif l_message_name = 'HR_FLEX_VALUE_INVALID' then
2078 fnd_message.set_name('PER', 'PAY_52634_PPM_BAD_FLEX_VALUE');
2079 l_prompt := hr_message.get_token_value(p_token_name => 'PROMPT');
2080 fnd_message.set_token('PROMPT', l_prompt);
2081 fnd_msg_pub.add;
2082 else
2083 --
2084 -- General flexfield message that cannot be assigned to a
2085 -- particular field.
2086 --
2087 fnd_msg_pub.add;
2088 end if;
2089 end;
2090 --
2091 -- Check Transit Code for US Bank flex.
2092 --
2093 if p_territory_code = 'US' then
2094 /*Only validate the transit code if the IAT Profile is set to N.
2095 If it's 'Y', then transit code should be '000000000' and this validation
2096 is raised in user hook of the US (PAY_US_PPM_HOOK)
2097 The below logic doesn't raise error message if the transit code is
2098 '000000000'. Logically it's valid but it's not a valid transit code.*/
2099 if (NVL(FND_PROFILE.VALUE('PAY_US_NACHA_IAT'),'N')='N') then
2100 if (p_segment4 = '000000000') then
2101 l_exa_user_error := true;
2102 l_user_error := true;
2103 fnd_message.set_name('PAY', 'PAY_50043_INVALID_TRANSIT_CODE');
2104 fnd_msg_pub.add;
2105 else
2106 l_transit_code_sum := 0;
2107 --
2108 -- Standard Transit Code checking algorithm.
2109 --
2110 l_check_digit := substr(p_segment4, 9, 1);
2111 for i in 1 .. 8 loop
2112 if i = 1 or i = 4 or i = 7 then
2113 l_transit_code_sum :=
2114 l_transit_code_sum + 3 * substr(p_segment4, i, 1);
2115 elsif i = 2 or i = 5 or i = 8 then
2116 l_transit_code_sum :=
2117 l_transit_code_sum + 7 * substr(p_segment4, i, 1);
2118 else
2119 l_transit_code_sum :=
2120 l_transit_code_sum + substr(p_segment4, i, 1);
2121 end if;
2122 end loop;
2123 l_transit_code_sum := 10 - mod(l_transit_code_sum, 10);
2124 if l_transit_code_sum = 10 then
2125 l_transit_code_sum := 0;
2126 end if;
2127 if l_transit_code_sum <> l_check_digit then
2128 l_exa_user_error := true;
2129 l_user_error := true;
2130 fnd_message.set_name('PAY', 'PAY_50043_INVALID_TRANSIT_CODE');
2131 fnd_msg_pub.add;
2132 end if;
2133 end if;
2134 end if;
2135 end if;
2136 --
2137 -- Only do the external_account_id check if there are no
2138 -- errors.
2139 --
2140 if not l_exa_user_error then
2141 seterrorstage(l_proc, 'CHK_EXA_ID', 160);
2142 begin
2143 pay_ppm_bus.chk_external_account_id
2144 (p_personal_payment_method_id => p_personal_payment_method_id
2145 ,p_org_payment_method_id => l_rec.org_payment_method_id
2146 ,p_external_account_id => l_external_account_id
2147 ,p_effective_date => p_effective_date
2148 ,p_object_version_number => p_object_version_number
2149 );
2150 exception
2151 when others then
2152 l_user_error := true;
2153 hr_message.provide_error;
2154 l_message_name := hr_message.last_message_name;
2155 if l_message_name = 'HR_6678_PPM_MT_BANK' then
2156 --
2157 -- The user did not supply bank details when required.
2158 --
2159 fnd_msg_pub.add;
2160 else
2161 --
2162 -- The remaining errors are fatal errors because they
2163 -- concern data that the module should set up correctly.
2164 --
2165 raise;
2166 end if;
2167 end;
2168 end if;
2169 --
2170 -- defined_balance_id check - this is yet another fatal error
2171 -- check.
2172 --
2173 begin
2174 seterrorstage(l_proc, 'CHK_DEF_BAL_ID', 170);
2175 pay_ppm_bus.chk_defined_balance_id
2176 (p_business_group_id => l_rec.business_group_id
2177 ,p_assignment_id => l_rec.assignment_id
2178 ,p_personal_payment_method_id => l_rec.personal_payment_method_id
2179 ,p_org_payment_method_id => l_rec.org_payment_method_id
2180 ,p_effective_date => p_effective_date
2181 ,p_object_version_number => l_rec.object_version_number
2182 ,p_payee_type => l_rec.payee_type
2183 ,p_payee_id => l_rec.payee_id
2184 );
2185 exception
2186 when others then
2187 raise;
2188 end;
2189 --
2190 -- Amount and percentage checks.
2191 --
2192 begin
2193 seterrorstage(l_proc, 'CHK_AMOUNT', 180);
2194 pay_ppm_bus.chk_amount_percent
2195 (p_amount => l_rec.amount
2196 ,p_percentage => l_rec.percentage
2197 ,p_personal_payment_method_id => l_rec.personal_payment_method_id
2198 ,p_org_payment_method_id => l_rec.org_payment_method_id
2199 ,p_effective_date => p_effective_date
2200 ,p_object_version_number => l_rec.object_version_number
2201 );
2202 exception
2203 when others then
2204 l_user_error := true;
2205 hr_message.provide_error;
2206 l_message_name := hr_message.last_message_name;
2207 if l_message_name = 'HR_6221_PAYM_INVALID_PPM' or
2208 l_message_name = 'HR_6680_PPM_AMT_PERC' then
2209 --
2210 -- Choose more specific messages based on whether or not
2211 -- the configuration is for percent only or amount only.
2212 --
2213 if p_amount_type = pay_pss_tx_steps_pkg.C_PERCENTAGE or
2214 p_amount_type = pay_pss_tx_steps_pkg.C_MONETARY then
2215 fnd_msg_pub.add;
2216 else
2217 fnd_message.set_name('PER', 'HR_WEB_REQUIRED_FIELD');
2218 fnd_msg_pub.add;
2219 end if;
2220 elsif l_message_name = 'HR_7355_PPM_AMOUNT_NEGATIVE' or
2221 l_message_name = 'HR_7040_PERCENT_RANGE' or
2222 l_message_name = 'HR_7912_CHECK_FMT_MONEY' or
2223 l_message_name = 'HR_7913_CHK_FMT_INTEGER'
2224 then
2225 fnd_msg_pub.add;
2226 else
2227 --
2228 -- Some other (fatal) error.
2229 --
2230 raise;
2231 end if;
2232 end;
2233 --
2234 -- Check the descriptive flex field - another user-level error.
2235 --
2236 begin
2237 seterrorstage(l_proc, 'CHK_DF', 190);
2238 pay_ppm_bus.chk_df(p_rec => l_rec);
2239 exception
2240 when others then
2241 l_user_error := true;
2242 hr_message.provide_error;
2243 l_message_name := hr_message.last_message_name;
2244 if l_message_name = 'HR_FLEX_VALUE_MISSING' then
2245 fnd_message.set_name('PER', 'HR_WEB_REQUIRED_FIELD');
2246 fnd_msg_pub.add;
2247 elsif l_message_name = 'HR_FLEX_VALUE_INVALID' then
2248 l_prompt := hr_message.get_token_value(p_token_name => 'PROMPT');
2249 fnd_message.set_name('PER', 'PAY_52634_PPM_BAD_FLEX_VALUE');
2250 fnd_message.set_token('PROMPT', l_prompt);
2251 fnd_msg_pub.add;
2252 else
2253 if l_message_name is null then
2254 if fnd_flex_descval.encoded_error_message is not null then
2255 fnd_message.set_encoded(fnd_flex_descval.encoded_error_message);
2256 end if;
2257 end if;
2258 --
2259 -- General flexfield message that cannot be assigned to a
2260 -- particular field.
2261 --
2262 fnd_msg_pub.add;
2263 end if;
2264 end;
2265 --
2266 rollback to start_validate;
2267 --
2268 -- Set up messages to Oracle Applications API standards as these
2269 -- are handled "for free" using checkErrors.
2270 --
2271 if l_user_error then
2272 seterrorstage(l_proc, 'GOT_USER_ERRORS', 200);
2273 p_return_status := fnd_api.G_RET_STS_ERROR;
2274 else
2275 seterrorstage(l_proc, 'NO_USER_ERRORS', 205);
2276 p_return_status := fnd_api.G_RET_STS_SUCCESS;
2277 end if;
2278 fnd_msg_pub.count_and_get
2279 (p_count => p_msg_count
2280 ,p_data => p_msg_data
2281 );
2282 seterrorstage(l_proc, 'EXIT:SUCCESS', 210);
2283 return;
2284 exception
2285 when others then
2286 rollback to start_validate;
2287 seterrorstage(l_proc, 'EXIT:FAIL', 220);
2288 --
2289 -- Set up messages to Oracle Applications API standards as these
2290 -- are handled "for free" using checkErrors.
2291 --
2292 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2293 fnd_msg_pub.initialize;
2294 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
2295 fnd_message.set_token('WHERE', l_proc);
2296 fnd_message.set_token('ADDITIONAL_INFO', sqlerrm);
2297 fnd_msg_pub.add;
2298 fnd_msg_pub.count_and_get
2299 (p_count => p_msg_count
2300 ,p_data => p_msg_data
2301 );
2302 return;
2303 end validateppm;
2304 -----------------------------< process_api >------------------------
2305 procedure process_api
2306 (p_state in varchar2 default null
2307 ,p_personal_payment_method_id in number default null
2308 ,p_object_version_number in number default null
2309 ,p_delete_ovn in number default null
2310 ,p_update_datetrack_mode in varchar2 default null
2311 ,p_delete_datetrack_mode in varchar2 default null
2312 ,p_effective_date in date default null
2313 ,p_org_payment_method_id in number default null
2314 ,p_assignment_id in number default null
2315 ,p_run_type_id in number default null
2316 ,p_territory_code in varchar2 default null
2317 ,p_real_priority in number default null
2318 ,p_amount_type in varchar2 default null
2319 ,p_amount in number default null
2320 ,p_attribute_category in varchar2 default null
2321 ,p_attribute1 in varchar2 default null
2322 ,p_attribute2 in varchar2 default null
2323 ,p_attribute3 in varchar2 default null
2324 ,p_attribute4 in varchar2 default null
2325 ,p_attribute5 in varchar2 default null
2326 ,p_attribute6 in varchar2 default null
2327 ,p_attribute7 in varchar2 default null
2328 ,p_attribute8 in varchar2 default null
2329 ,p_attribute9 in varchar2 default null
2330 ,p_attribute10 in varchar2 default null
2331 ,p_attribute11 in varchar2 default null
2332 ,p_attribute12 in varchar2 default null
2333 ,p_attribute13 in varchar2 default null
2334 ,p_attribute14 in varchar2 default null
2335 ,p_attribute15 in varchar2 default null
2336 ,p_attribute16 in varchar2 default null
2337 ,p_attribute17 in varchar2 default null
2338 ,p_attribute18 in varchar2 default null
2339 ,p_attribute19 in varchar2 default null
2340 ,p_attribute20 in varchar2 default null
2341 ,p_segment1 in varchar2 default null
2342 ,p_segment2 in varchar2 default null
2343 ,p_segment3 in varchar2 default null
2344 ,p_segment4 in varchar2 default null
2345 ,p_segment5 in varchar2 default null
2346 ,p_segment6 in varchar2 default null
2347 ,p_segment7 in varchar2 default null
2348 ,p_segment8 in varchar2 default null
2349 ,p_segment9 in varchar2 default null
2350 ,p_segment10 in varchar2 default null
2351 ,p_segment11 in varchar2 default null
2352 ,p_segment12 in varchar2 default null
2353 ,p_segment13 in varchar2 default null
2354 ,p_segment14 in varchar2 default null
2355 ,p_segment15 in varchar2 default null
2356 ,p_segment16 in varchar2 default null
2357 ,p_segment17 in varchar2 default null
2358 ,p_segment18 in varchar2 default null
2359 ,p_segment19 in varchar2 default null
2360 ,p_segment20 in varchar2 default null
2361 ,p_segment21 in varchar2 default null
2362 ,p_segment22 in varchar2 default null
2363 ,p_segment23 in varchar2 default null
2364 ,p_segment24 in varchar2 default null
2365 ,p_segment25 in varchar2 default null
2366 ,p_segment26 in varchar2 default null
2367 ,p_segment27 in varchar2 default null
2368 ,p_segment28 in varchar2 default null
2369 ,p_segment29 in varchar2 default null
2370 ,p_segment30 in varchar2 default null
2371 ,p_o_real_priority in number default null
2372 ,p_validate in boolean default false
2373 ,p_ppm_information_category in varchar2 default null
2374 ,p_ppm_information1 in varchar2 default null
2375 ,p_ppm_information2 in varchar2 default null
2376 ,p_ppm_information3 in varchar2 default null
2377 ,p_ppm_information4 in varchar2 default null
2378 ,p_ppm_information5 in varchar2 default null
2379 ,p_ppm_information6 in varchar2 default null
2380 ,p_ppm_information7 in varchar2 default null
2381 ,p_ppm_information8 in varchar2 default null
2382 ,p_ppm_information9 in varchar2 default null
2383 ,p_ppm_information10 in varchar2 default null
2384 ,p_ppm_information11 in varchar2 default null
2385 ,p_ppm_information12 in varchar2 default null
2386 ,p_ppm_information13 in varchar2 default null
2387 ,p_ppm_information14 in varchar2 default null
2388 ,p_ppm_information15 in varchar2 default null
2389 ,p_ppm_information16 in varchar2 default null
2390 ,p_ppm_information17 in varchar2 default null
2391 ,p_ppm_information18 in varchar2 default null
2392 ,p_ppm_information19 in varchar2 default null
2393 ,p_ppm_information20 in varchar2 default null
2394 ,p_ppm_information21 in varchar2 default null
2395 ,p_ppm_information22 in varchar2 default null
2396 ,p_ppm_information23 in varchar2 default null
2397 ,p_ppm_information24 in varchar2 default null
2398 ,p_ppm_information25 in varchar2 default null
2399 ,p_ppm_information26 in varchar2 default null
2400 ,p_ppm_information27 in varchar2 default null
2401 ,p_ppm_information28 in varchar2 default null
2402 ,p_ppm_information29 in varchar2 default null
2403 ,p_ppm_information30 in varchar2 default null
2404 ) is
2405 l_effective_date date;
2406 v_check_value varchar2(10):='Y';
2407 --
2408 -- Various OUT-parameters.
2409 --
2410 l_effective_start_date date;
2411 l_effective_end_date date;
2412 l_object_version_number number;
2413 l_personal_payment_method_id number;
2414 l_external_account_id number;
2415 l_comment_id number;
2416 --
2417 -- Percentage/Amount.
2418 --
2419 l_percentage number;
2420 l_amount number;
2421 l_proc varchar2(100) := 'PAY_PPMV4_UTILS_SS.PROCESS_API';
2422 begin
2423 seterrorstage(l_proc,'Entering ...',10);
2424 l_object_version_number := p_object_version_number;
2425 l_personal_payment_method_id := p_personal_payment_method_id;
2426 --
2427 -- Set Percentage/Amount.
2428 --
2429 if p_amount_type = pay_pss_tx_steps_pkg.C_PERCENTAGE or
2430 p_amount_type = pay_pss_tx_steps_pkg.C_PERCENTAGE_ONLY then
2431 l_percentage := p_amount;
2432 l_amount := null;
2433 elsif p_amount_type = pay_pss_tx_steps_pkg.C_MONETARY or
2434 p_amount_type = pay_pss_tx_steps_pkg.C_MONETARY_ONLY then
2435 l_percentage := null;
2436 l_amount := p_amount;
2437
2438 --
2439 -- If its the C_REMAINING_PAY (i.e. lowest priority) pay method,
2440 -- then setting the values to hr_api.g_number so that original values are retained
2441 -- when date track apis enters a new record.
2442 else
2443 /* Bug 12624040 Making the chnages made in 7230549 functionality switchable */
2444 begin
2445 select parameter_value
2446 into v_check_value
2447 from pay_action_parameters pap
2448 where pap.parameter_name = 'PPM_PERC_GTR_100';
2449 exception
2450 when others then
2451 v_check_value := 'Y';
2452 end;
2453 if v_check_value ='N' then
2454 if p_state = pay_pss_tx_steps_pkg.C_STATE_NEW then
2455 l_percentage := 100;
2456 l_amount := null;
2457 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_UPDATED then
2458 l_percentage := hr_api.g_number;
2459 l_amount := hr_api.g_number;
2460 end if;
2461 else
2462 l_percentage := 100;
2463 l_amount := null;
2464 end if;
2465 end if;
2466
2467 /*Store the session into FND_SESSIONS and profile PAY_US_NACHA_IAT.
2468 *This is required to derive the segments of the DFFs*/
2469 pay_ppmv4_ss.store_session(trunc(p_effective_date));
2470 fnd_profile.put('PAY_US_NACHA_IAT',p_ppm_information4);
2471 --
2472 -- Check the PPM state to determine which API call to make.
2473 --
2474 seterrorstage(l_proc,'Calling ...hr_personal_pay_method_api.create_personal_pay_method',20);
2475 if p_state = pay_pss_tx_steps_pkg.C_STATE_NEW then
2476 hr_personal_pay_method_api.create_personal_pay_method
2477 (p_validate => p_validate
2478 ,p_effective_date => p_effective_date
2479 ,p_assignment_id => p_assignment_id
2480 ,p_run_type_id => p_run_type_id
2481 ,p_org_payment_method_id => p_org_payment_method_id
2482 ,p_personal_payment_method_id => l_personal_payment_method_id
2483 ,p_object_version_number => l_object_version_number
2484 ,p_amount => l_amount
2485 ,p_percentage => l_percentage
2486 ,p_priority => p_real_priority
2487 ,p_attribute_category => p_attribute_category
2488 ,p_attribute1 => p_attribute1
2489 ,p_attribute2 => p_attribute2
2490 ,p_attribute3 => p_attribute3
2491 ,p_attribute4 => p_attribute4
2492 ,p_attribute5 => p_attribute5
2493 ,p_attribute6 => p_attribute6
2494 ,p_attribute7 => p_attribute7
2495 ,p_attribute8 => p_attribute8
2496 ,p_attribute9 => p_attribute9
2497 ,p_attribute10 => p_attribute10
2498 ,p_attribute11 => p_attribute11
2499 ,p_attribute12 => p_attribute12
2500 ,p_attribute13 => p_attribute13
2501 ,p_attribute14 => p_attribute14
2502 ,p_attribute15 => p_attribute15
2503 ,p_attribute16 => p_attribute16
2504 ,p_attribute17 => p_attribute17
2505 ,p_attribute18 => p_attribute18
2506 ,p_attribute19 => p_attribute19
2507 ,p_attribute20 => p_attribute20
2508 ,p_territory_code => p_territory_code
2509 ,p_segment1 => p_segment1
2510 ,p_segment2 => p_segment2
2511 ,p_segment3 => p_segment3
2512 ,p_segment4 => p_segment4
2513 ,p_segment5 => p_segment5
2514 ,p_segment6 => p_segment6
2515 ,p_segment7 => p_segment7
2516 ,p_segment8 => p_segment8
2517 ,p_segment9 => p_segment9
2518 ,p_segment10 => p_segment10
2519 ,p_segment11 => p_segment11
2520 ,p_segment12 => p_segment12
2521 ,p_segment13 => p_segment13
2522 ,p_segment14 => p_segment14
2523 ,p_segment15 => p_segment15
2524 ,p_segment16 => p_segment16
2525 ,p_segment17 => p_segment17
2526 ,p_segment18 => p_segment18
2527 ,p_segment19 => p_segment19
2528 ,p_segment20 => p_segment20
2529 ,p_segment21 => p_segment21
2530 ,p_segment22 => p_segment22
2531 ,p_segment23 => p_segment23
2532 ,p_segment24 => p_segment24
2533 ,p_segment25 => p_segment25
2534 ,p_segment26 => p_segment26
2535 ,p_segment27 => p_segment27
2536 ,p_segment28 => p_segment28
2537 ,p_segment29 => p_segment29
2538 ,p_segment30 => p_segment30
2539 ,p_ppm_information_category => p_ppm_information_category
2540 ,p_ppm_information1 => p_ppm_information1
2541 ,p_ppm_information2 => p_ppm_information2
2542 ,p_ppm_information3 => p_ppm_information3
2543 ,p_ppm_information4 => p_ppm_information4
2544 ,p_ppm_information5 => p_ppm_information5
2545 ,p_ppm_information6 => p_ppm_information6
2546 ,p_ppm_information7 => p_ppm_information7
2547 ,p_ppm_information8 => p_ppm_information8
2548 ,p_ppm_information9 => p_ppm_information9
2549 ,p_ppm_information10 => p_ppm_information10
2550 ,p_ppm_information11 => p_ppm_information11
2551 ,p_ppm_information12 => p_ppm_information12
2552 ,p_ppm_information13 => p_ppm_information13
2553 ,p_ppm_information14 => p_ppm_information14
2554 ,p_ppm_information15 => p_ppm_information15
2555 ,p_ppm_information16 => p_ppm_information16
2556 ,p_ppm_information17 => p_ppm_information17
2557 ,p_ppm_information18 => p_ppm_information18
2558 ,p_ppm_information19 => p_ppm_information19
2559 ,p_ppm_information20 => p_ppm_information20
2560 ,p_ppm_information21 => p_ppm_information21
2561 ,p_ppm_information22 => p_ppm_information22
2562 ,p_ppm_information23 => p_ppm_information23
2563 ,p_ppm_information24 => p_ppm_information24
2564 ,p_ppm_information25 => p_ppm_information25
2565 ,p_ppm_information26 => p_ppm_information26
2566 ,p_ppm_information27 => p_ppm_information27
2567 ,p_ppm_information28 => p_ppm_information28
2568 ,p_ppm_information29 => p_ppm_information29
2569 ,p_ppm_information30 => p_ppm_information30
2570 ,p_external_account_id => l_external_account_id
2571 ,p_effective_start_date => l_effective_start_date
2572 ,p_effective_end_date => l_effective_end_date
2573 ,p_comment_id => l_comment_id
2574 );
2575 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_UPDATED then
2576 seterrorstage(l_proc, 'Calling ...hr_personal_pay_method_api.update_personal_pay_method', 30);
2577 hr_personal_pay_method_api.update_personal_pay_method
2578 (p_validate => p_validate
2579 ,p_effective_date => p_effective_date
2580 ,p_datetrack_update_mode => p_update_datetrack_mode
2581 ,p_personal_payment_method_id => p_personal_payment_method_id
2582 ,p_object_version_number => l_object_version_number
2583 ,p_amount => l_amount
2584 ,p_percentage => l_percentage
2585 ,p_priority => p_real_priority
2586 ,p_attribute_category => p_attribute_category
2587 ,p_attribute1 => p_attribute1
2588 ,p_attribute2 => p_attribute2
2589 ,p_attribute3 => p_attribute3
2590 ,p_attribute4 => p_attribute4
2591 ,p_attribute5 => p_attribute5
2592 ,p_attribute6 => p_attribute6
2593 ,p_attribute7 => p_attribute7
2594 ,p_attribute8 => p_attribute8
2595 ,p_attribute9 => p_attribute9
2596 ,p_attribute10 => p_attribute10
2597 ,p_attribute11 => p_attribute11
2598 ,p_attribute12 => p_attribute12
2599 ,p_attribute13 => p_attribute13
2600 ,p_attribute14 => p_attribute14
2601 ,p_attribute15 => p_attribute15
2602 ,p_attribute16 => p_attribute16
2603 ,p_attribute17 => p_attribute17
2604 ,p_attribute18 => p_attribute18
2605 ,p_attribute19 => p_attribute19
2606 ,p_attribute20 => p_attribute20
2607 ,p_territory_code => p_territory_code
2608 ,p_segment1 => p_segment1
2609 ,p_segment2 => p_segment2
2610 ,p_segment3 => p_segment3
2611 ,p_segment4 => p_segment4
2612 ,p_segment5 => p_segment5
2613 ,p_segment6 => p_segment6
2614 ,p_segment7 => p_segment7
2615 ,p_segment8 => p_segment8
2616 ,p_segment9 => p_segment9
2617 ,p_segment10 => p_segment10
2618 ,p_segment11 => p_segment11
2619 ,p_segment12 => p_segment12
2620 ,p_segment13 => p_segment13
2621 ,p_segment14 => p_segment14
2622 ,p_segment15 => p_segment15
2623 ,p_segment16 => p_segment16
2624 ,p_segment17 => p_segment17
2625 ,p_segment18 => p_segment18
2626 ,p_segment19 => p_segment19
2627 ,p_segment20 => p_segment20
2628 ,p_segment21 => p_segment21
2629 ,p_segment22 => p_segment22
2630 ,p_segment23 => p_segment23
2631 ,p_segment24 => p_segment24
2632 ,p_segment25 => p_segment25
2633 ,p_segment26 => p_segment26
2634 ,p_segment27 => p_segment27
2635 ,p_segment28 => p_segment28
2636 ,p_segment29 => p_segment29
2637 ,p_segment30 => p_segment30
2638 ,p_ppm_information_category => p_ppm_information_category
2639 ,p_ppm_information1 => p_ppm_information1
2640 ,p_ppm_information2 => p_ppm_information2
2641 ,p_ppm_information3 => p_ppm_information3
2642 ,p_ppm_information4 => p_ppm_information4
2643 ,p_ppm_information5 => p_ppm_information5
2644 ,p_ppm_information6 => p_ppm_information6
2645 ,p_ppm_information7 => p_ppm_information7
2646 ,p_ppm_information8 => p_ppm_information8
2647 ,p_ppm_information9 => p_ppm_information9
2648 ,p_ppm_information10 => p_ppm_information10
2649 ,p_ppm_information11 => p_ppm_information11
2650 ,p_ppm_information12 => p_ppm_information12
2651 ,p_ppm_information13 => p_ppm_information13
2652 ,p_ppm_information14 => p_ppm_information14
2653 ,p_ppm_information15 => p_ppm_information15
2654 ,p_ppm_information16 => p_ppm_information16
2655 ,p_ppm_information17 => p_ppm_information17
2656 ,p_ppm_information18 => p_ppm_information18
2657 ,p_ppm_information19 => p_ppm_information19
2658 ,p_ppm_information20 => p_ppm_information20
2659 ,p_ppm_information21 => p_ppm_information21
2660 ,p_ppm_information22 => p_ppm_information22
2661 ,p_ppm_information23 => p_ppm_information23
2662 ,p_ppm_information24 => p_ppm_information24
2663 ,p_ppm_information25 => p_ppm_information25
2664 ,p_ppm_information26 => p_ppm_information26
2665 ,p_ppm_information27 => p_ppm_information27
2666 ,p_ppm_information28 => p_ppm_information28
2667 ,p_ppm_information29 => p_ppm_information29
2668 ,p_ppm_information30 => p_ppm_information30
2669 ,p_external_account_id => l_external_account_id
2670 ,p_effective_start_date => l_effective_start_date
2671 ,p_effective_end_date => l_effective_end_date
2672 ,p_comment_id => l_comment_id
2673 );
2674 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_DELETED then
2675 l_object_version_number := p_delete_ovn;
2676 if p_delete_datetrack_mode = hr_api.g_zap then
2677 l_effective_date := p_effective_date;
2678 else
2679 l_effective_date := p_effective_date - 1;
2680 end if;
2681 seterrorstage(l_proc,'Calling ...hr_personal_pay_method_api.delete_personal_pay_method',40);
2682 hr_personal_pay_method_api.delete_personal_pay_method
2683 (p_validate => p_validate
2684 ,p_effective_date => l_effective_date
2685 ,p_datetrack_delete_mode => p_delete_datetrack_mode
2686 ,p_personal_payment_method_id => p_personal_payment_method_id
2687 ,p_object_version_number => l_object_version_number
2688 ,p_effective_start_date => l_effective_start_date
2689 ,p_effective_end_date => l_effective_end_date
2690 );
2691 elsif p_state = pay_pss_tx_steps_pkg.C_STATE_EXISTING and
2692 p_real_priority <> p_o_real_priority then
2693 seterrorstage(l_proc,'Calling ...hr_personal_pay_method_api.update_personal_pay_method',50);
2694 hr_personal_pay_method_api.update_personal_pay_method
2695 (p_validate => p_validate
2696 ,p_personal_payment_method_id => p_personal_payment_method_id
2697 ,p_object_version_number => l_object_version_number
2698 ,p_effective_date => p_effective_date
2699 ,p_datetrack_update_mode => p_update_datetrack_mode
2700 ,p_priority => p_real_priority
2701 ,p_external_account_id => l_external_account_id
2702 ,p_effective_start_date => l_effective_start_date
2703 ,p_effective_end_date => l_effective_end_date
2704 ,p_comment_id => l_comment_id
2705 );
2706 end if;
2707 exception
2708 when others then
2709 raise;
2710 end process_api;
2711 -------------------------< get_bank_segments >----------------------
2712 procedure get_bank_segments
2713 (p_external_account_id in number
2714 ,p_segment1 out nocopy varchar2
2715 ,p_segment2 out nocopy varchar2
2716 ,p_segment3 out nocopy varchar2
2717 ,p_segment4 out nocopy varchar2
2718 ,p_segment5 out nocopy varchar2
2719 ,p_segment6 out nocopy varchar2
2720 ,p_segment7 out nocopy varchar2
2721 ,p_segment8 out nocopy varchar2
2722 ,p_segment9 out nocopy varchar2
2723 ,p_segment10 out nocopy varchar2
2724 ,p_segment11 out nocopy varchar2
2725 ,p_segment12 out nocopy varchar2
2726 ,p_segment13 out nocopy varchar2
2727 ,p_segment14 out nocopy varchar2
2728 ,p_segment15 out nocopy varchar2
2729 ,p_segment16 out nocopy varchar2
2730 ,p_segment17 out nocopy varchar2
2731 ,p_segment18 out nocopy varchar2
2732 ,p_segment19 out nocopy varchar2
2733 ,p_segment20 out nocopy varchar2
2734 ,p_segment21 out nocopy varchar2
2735 ,p_segment22 out nocopy varchar2
2736 ,p_segment23 out nocopy varchar2
2737 ,p_segment24 out nocopy varchar2
2738 ,p_segment25 out nocopy varchar2
2739 ,p_segment26 out nocopy varchar2
2740 ,p_segment27 out nocopy varchar2
2741 ,p_segment28 out nocopy varchar2
2742 ,p_segment29 out nocopy varchar2
2743 ,p_segment30 out nocopy varchar2
2744 ) is
2745 l_proc varchar2(2000) := g_package || 'get_bank_segments';
2746 begin
2747 --
2748 -- Use SELECT ... INTO ... rather than a cursor as this select
2749 -- statement must return segments. The OA framework code inserts
2750 -- into the combination table.
2751 --
2752 seterrorstage(l_proc, 'ENTER', 10);
2753 select segment1
2754 , segment2
2755 , segment3
2756 , segment4
2757 , segment5
2758 , segment6
2759 , segment7
2760 , segment8
2761 , segment9
2762 , segment10
2763 , segment11
2764 , segment12
2765 , segment13
2766 , segment14
2767 , segment15
2768 , segment16
2769 , segment17
2770 , segment18
2771 , segment19
2772 , segment20
2773 , segment21
2774 , segment22
2775 , segment23
2776 , segment24
2777 , segment25
2778 , segment26
2779 , segment27
2780 , segment28
2781 , segment29
2782 , segment30
2783 into p_segment1
2784 , p_segment2
2785 , p_segment3
2786 , p_segment4
2787 , p_segment5
2788 , p_segment6
2789 , p_segment7
2790 , p_segment8
2791 , p_segment9
2792 , p_segment10
2793 , p_segment11
2794 , p_segment12
2795 , p_segment13
2796 , p_segment14
2797 , p_segment15
2798 , p_segment16
2799 , p_segment17
2800 , p_segment18
2801 , p_segment19
2802 , p_segment20
2803 , p_segment21
2804 , p_segment22
2805 , p_segment23
2806 , p_segment24
2807 , p_segment25
2808 , p_segment26
2809 , p_segment27
2810 , p_segment28
2811 , p_segment29
2812 , p_segment30
2813 from pay_external_accounts
2814 where external_account_id = p_external_account_id;
2815 exception
2816 when others then
2817 seterrorstage(l_proc, 'EXIT:FAIL', 20);
2818 raise;
2819 end get_bank_segments;
2820 --
2821 end pay_ppmv4_utils_ss;