1 PACKAGE BODY pay_jp_result_pkg AS
2 /* $Header: pyjprslt.pkb 120.3 2006/12/06 06:34:43 keyazawa noship $ */
3 --
4 -- Current ORACLE has so many bugs when using object.
5 -- Avoid using so many objects, or cause unexpected errors.
6 -- These problems will be fixed after 8.2.0.0.0.
7 --
8 -- This function returns result("Result Value" and "Balance Value") of "RUN".
9 -- Available action_type is as follows.
10 -- B : Balance Adjustment
11 -- F : Advance Pay(?)
12 -- I : Balance Initialization
13 -- Q : QuickPay Run
14 -- R : Run
15 -- V : Reversal
16 -- Z : Purge(?)
17 --
18 --
19 PROCEDURE result_values_internal(
20 p_assignment_action_id IN NUMBER,
21 p_effective_date IN DATE,
22 p_result_values_by_iv IN OUT NOCOPY result_values_by_iv_t)
23 IS
24 --
25 -- Bulk Result informations.
26 --
27 -- To execute bulk fetch, nested loop(or VARRAY) is mandatory.
28 --
29 TYPE result_values_t IS TABLE OF PAY_RUN_RESULT_VALUES.RESULT_VALUE%TYPE;
30 TYPE data_types_t IS TABLE OF VARCHAR2(30);
31 /* BUG1899306: Modified not to use an object type. ***********
32 l_input_value_ids pay_jp_numbers_t;
33 *********************************************************** */
34 TYPE numbers_t IS TABLE OF NUMBER;
35 l_input_value_ids numbers_t;
36 l_data_types data_types_t;
37 l_result_values result_values_t;
38 CURSOR csr_result_value IS
39 /* Removed the hint to fix Bug# 5464717. */
40 select
41 piv.input_value_id,
42 decode(substrb(piv.uom,1,1),'M','N','N','N','I','N','H','N','D','D','T') DATA_TYPE,
43 decode(
44 /* N(Number), T(Text), D(Date) */
45 decode(substrb(piv.uom,1,1),'M','N','N','N','I','N','H','N','D','D','T'),
46 'N',fnd_number.number_to_canonical(
47 sum(
48 decode(
49 decode(substrb(piv.uom,1,1),'M','N','N','N','I','N','H','N','D','D','T'),
50 /* BUG3038738: Modified not to return result value if uom is number type. **
51 ** When UOM of that result_value is number type, and it exceeds 20 digits **
52 ** after the decimal point, ORA-06502 error occurs in **
53 ** fnd_number.canonical_to_number() function. Because fnd_number uses a **
54 ** canonical_mask with only 20 decimal places against result_value with up **
55 ** to 38 decimal places. Currently, result_value for number type from this **
56 ** cursor has not been used anywhere. **
57 'N',fnd_number.canonical_to_number(prrv.result_value),
58 ********************************************************************************* */
59 'N',
60 decode(substrb(piv.uom,1,1),'N',0,
61 fnd_number.canonical_to_number(prrv.result_value)),
62 NULL
63 )
64 )
65 ),
66 min(distinct prrv.result_value)) RESULT_VALUE
67 from
68 pay_run_result_values prrv, -- Run result value
69 pay_run_results prr, -- Run result
70 pay_input_values_f piv
71 where prr.assignment_action_id = p_assignment_action_id
72 and prr.status in ('P','PA')
73 and prrv.run_result_id = prr.run_result_id
74 and piv.input_value_id = prrv.input_value_id
75 and p_effective_date
76 between piv.effective_start_date and piv.effective_end_date
77 group by
78 piv.input_value_id,
79 decode(substrb(piv.uom,1,1),'M','N','N','N','I','N','H','N','D','D','T');
80 --
81 -- Other local variables.
82 --
83 l_result_values_by_iv result_values_by_iv_t;
84 l_input_value_id NUMBER;
85 l_data_type hr_lookups.LOOKUP_CODE%TYPE;
86 l_result_value PAY_RUN_RESULT_VALUES.RESULT_VALUE%TYPE;
87 BEGIN
88 --
89 -- Get all result values with bulk collect.
90 --
91 open csr_result_value;
92 fetch csr_result_value bulk collect into l_input_value_ids,l_data_types,l_result_values;
93 close csr_result_value;
94 --
95 -- Convert result values from nexted loop to index-by PL/SQL table.
96 --
97 for i in 1..l_input_value_ids.count loop
98 l_input_value_id := l_input_value_ids(i);
99 l_data_type := l_data_types(i);
100 l_result_value := l_result_values(i);
101 --
102 -- Setup result_values indexed by input_value_id for current assignment_action_id.
103 --
104 l_result_values_by_iv(l_input_value_id).data_type := l_data_type;
105 l_result_values_by_iv(l_input_value_id).result_value := l_result_value;
106 --
107 -- Unite result_values indexed by input_value_id of current assignment_action_id
108 -- to the p_result_values_by_iv input variable.
109 --
110 if p_result_values_by_iv.exists(l_input_value_id) then
111 if l_result_value is not NULL then
112 if p_result_values_by_iv(l_input_value_id).result_value is NULL then
113 p_result_values_by_iv(l_input_value_id).result_value := l_result_value;
114 else
115 if l_data_type = 'N' then
116 p_result_values_by_iv(l_input_value_id).result_value
117 := fnd_number.number_to_canonical(
118 fnd_number.canonical_to_number(p_result_values_by_iv(l_input_value_id).result_value)
119 + fnd_number.canonical_to_number(l_result_value));
120 else
121 p_result_values_by_iv(l_input_value_id).result_value
122 := least(p_result_values_by_iv(l_input_value_id).result_value,l_result_value);
123 end if;
124 end if;
125 end if;
126 else
127 p_result_values_by_iv(l_input_value_id).data_type := l_data_type;
128 p_result_values_by_iv(l_input_value_id).result_value := l_result_value;
129 end if;
130 end loop;
131 END result_values_internal;
132 --
133 PROCEDURE balance_values_internal(
134 p_result_values_by_iv IN result_values_by_iv_t,
135 p_balance_type_ids IN balance_type_ids_t,
136 p_feed_checking_date IN DATE,
137 p_balance_values_by_bal IN OUT NOCOPY balance_values_by_bal_t)
138 IS
139 --
140 -- Bulk Feed informations.
141 --
142 /* BUG1899306: Modified not to use EXECUTE IMMEDIATE. ************************
143 -- Bulk collect is applied to this function to raise performance.
144 -- Remember that binding SQL in PL/SQL is very slow, that is, fetch is very slow.
145 -- Additionally, when using bulk fetch with EXECUTE IMMEDIATE,
146 -- output variables must be declared with "CREATE TYPE".
147 --
148 l_feed_bals pay_jp_numbers_t;
149 l_feed_ivs pay_jp_numbers_t;
150 l_feed_scales pay_jp_numbers_t;
151 *************************************************************************** */
152 --
153 -- BUG1899306
154 --
155 -- Since using object type is a violation of Apps standards, we cannot use
156 -- EXECUTE IMMEDIATE. That's why fetching balance feed informations was modified
157 -- to using DBMS_SQL package.
158 --
159 l_feed_bals dbms_sql.Number_Table;
160 l_feed_ivs dbms_sql.Number_Table;
161 l_feed_scales dbms_sql.Number_Table;
162 l_stmt_str VARCHAR2(32767);
163 l_cur_hdl NUMBER;
164 l_rows_processed NUMBER;
165 l_idx NUMBER := 0;
166 --
167 -- Other local variables.
168 --
169 l_index NUMBER;
170 l_balance_type_id NUMBER;
171 l_concat_bal VARCHAR2(32767);
172 l_input_value_id NUMBER;
173 l_concat_iv VARCHAR2(32767);
174 l_result_value PAY_RUN_RESULT_VALUES.RESULT_VALUE%TYPE;
175 BEGIN
176 --
177 -- Setup balance values.
178 --
179 -- Initialize output variable "p_balance_values_by_bal".
180 --
181 if p_balance_type_ids is not NULL then
182 l_index := p_balance_type_ids.first;
183 while l_index is not NULL loop
184 l_balance_type_id := p_balance_type_ids(l_index);
185 if l_balance_type_id is not NULL then
186 --
187 -- If not instantiated, default as "0".
188 --
189 if not p_balance_values_by_bal.exists(l_balance_type_id) then
190 p_balance_values_by_bal(l_balance_type_id) := 0;
191 end if;
192 --
193 -- Construct balance_type_id list used by EXECUTE IMMEDIATE statement below.
194 --
195 if l_concat_bal is NULL then
196 l_concat_bal := to_char(l_balance_type_id);
197 else
198 l_concat_bal := l_concat_bal || ',' || to_char(l_balance_type_id);
199 end if;
200 end if;
201 l_index := p_balance_type_ids.next(l_index);
202 end loop;
203 end if;
204 --
205 -- Construct input_value_id lists used by EXECUTE IMMEDIATE statement below.
206 --
207 if l_concat_bal is not NULL then
208 l_input_value_id := p_result_values_by_iv.first;
209 while l_input_value_id is not NULL loop
210 --
211 -- Balance is available only for data_type = 'N'(UOM = M,N,I,etc...).
212 --
213 if p_result_values_by_iv(l_input_value_id).data_type = 'N' then
214 if l_concat_iv is NULL then
215 l_concat_iv := to_char(l_input_value_id);
216 else
217 l_concat_iv := l_concat_iv || ',' || to_char(l_input_value_id);
218 end if;
219 end if;
220 l_input_value_id := p_result_values_by_iv.next(l_input_value_id);
221 end loop;
222 end if;
223 --
224 -- Setup balance values to output variable "p_balance_values_by_bal".
225 -- When no balance required(p_concat_bal is NULL) or no results with data_type=Number(p_concat_iv is NULL),
226 -- the following code is skipped.
227 --
228 if l_concat_iv is not NULL then
229 --
230 -- Bulk fetch minimum balance feed informations.
231 --
232 /* BUG1899306: modified not to use EXECUTE IMMEDIATE ***********************************
233 execute immediate
234 'BEGIN
235 select pbf.balance_type_id,
236 pbf.input_value_id,
237 pbf.scale
238 bulk collect into
239 :b_feed_bal,
240 :b_feed_iv,
241 :b_feed_scale
242 from pay_balance_feeds_f pbf
243 where pbf.balance_type_id in (' || l_concat_bal || ')
244 and pbf.input_value_id in (' || l_concat_iv || ')
245 and :b_feed_cheking_date
246 between pbf.effective_start_date and pbf.effective_end_date;
247 END;'
248 using OUT l_feed_bals,
249 OUT l_feed_ivs,
250 OUT l_feed_scales,
251 IN p_feed_checking_date;
252 ************************************************************************************ */
253 --
254 -- BUG1899306: Fetch balance feed informations using DBMS_SQL package.
255 --
256 l_stmt_str := ' select pbf.balance_type_id,
257 pbf.input_value_id,
258 pbf.scale
259 from pay_balance_feeds_f pbf
260 where pbf.balance_type_id in (' || l_concat_bal || ')
261 and pbf.input_value_id in (' || l_concat_iv || ')
262 and :b_feed_cheking_date
263 between pbf.effective_start_date and pbf.effective_end_date';
264 l_cur_hdl := dbms_sql.open_cursor;
265 dbms_sql.parse(l_cur_hdl, l_stmt_str, DBMS_SQL.NATIVE);
266 dbms_sql.bind_variable(l_cur_hdl, 'b_feed_cheking_date', p_feed_checking_date);
267 dbms_sql.define_array(l_cur_hdl, 1, l_feed_bals, 10, l_idx);
268 dbms_sql.define_array(l_cur_hdl, 2, l_feed_ivs, 10, l_idx);
269 dbms_sql.define_array(l_cur_hdl, 3, l_feed_scales, 10, l_idx);
270 l_rows_processed := dbms_sql.execute(l_cur_hdl);
271 loop
272 l_rows_processed := dbms_sql.fetch_rows(l_cur_hdl);
273 dbms_sql.column_value(l_cur_hdl, 1, l_feed_bals);
274 dbms_sql.column_value(l_cur_hdl, 2, l_feed_ivs);
275 dbms_sql.column_value(l_cur_hdl, 3, l_feed_scales);
276 exit when l_rows_processed <> 10;
277 end loop;
278 dbms_sql.close_cursor(l_cur_hdl);
279 --
280 -- l_feed_bals is not NULL even when no records fetched in above SQL statement.
281 -- So not necessary to check l_feed_bals is NULL or not.
282 --
283 if l_feed_bals.count > 0 then
284 for i in l_feed_bals.first..l_feed_bals.last loop
285 --
286 -- No need to check collection method "EXISTS".
287 --
288 l_result_value := p_result_values_by_iv(l_feed_ivs(i)).result_value;
289 if l_result_value is not NULL and l_result_value <> 0 then
290 p_balance_values_by_bal(l_feed_bals(i)) := p_balance_values_by_bal(l_feed_bals(i))
291 + fnd_number.canonical_to_number(l_result_value) * l_feed_scales(i);
292 end if;
293 end loop;
294 end if;
295 end if;
296 END balance_values_internal;
297 --
298 PROCEDURE run_internal(
299 p_assignment_action_id IN NUMBER,
300 p_feed_checking_date IN DATE,
301 p_balance_type_ids IN balance_type_ids_t,
302 p_result_values_by_iv IN OUT NOCOPY result_values_by_iv_t,
303 p_balance_values_by_bal IN OUT NOCOPY balance_values_by_bal_t)
304 IS
305 BEGIN
306 result_values_internal(
307 p_assignment_action_id => p_assignment_action_id,
308 p_effective_date => p_feed_checking_date,
309 p_result_values_by_iv => p_result_values_by_iv);
310 balance_values_internal(
311 p_result_values_by_iv => p_result_values_by_iv,
312 p_balance_type_ids => p_balance_type_ids,
313 p_feed_checking_date => p_feed_checking_date,
314 p_balance_values_by_bal => p_balance_values_by_bal);
315 END run_internal;
316 --
317 -- Function result_value
318 --
319 FUNCTION result_value(
320 p_result_values_by_iv IN result_values_by_iv_t,
321 p_input_value_id IN NUMBER) RETURN VARCHAR2
322 IS
323 l_result_value PAY_RUN_RESULT_VALUES.RESULT_VALUE%TYPE;
324 BEGIN
325 if p_input_value_id is not NULL then
326 if p_result_values_by_iv.exists(p_input_value_id) then
327 l_result_value := p_result_values_by_iv(p_input_value_id).result_value;
328 end if;
329 end if;
330 --
331 -- Return value.
332 --
333 return l_result_value;
334 END result_value;
335 --
336 -- Function balance_value
337 --
338 FUNCTION balance_value(
339 p_balance_values_by_bal IN balance_values_by_bal_t,
340 p_balance_type_id IN NUMBER) RETURN NUMBER
341 IS
342 l_balance_value NUMBER;
343 BEGIN
344 if p_balance_type_id is not NULL then
345 if p_balance_values_by_bal.exists(p_balance_type_id) then
346 l_balance_value := p_balance_values_by_bal(p_balance_type_id);
347 end if;
348 end if;
349 --
350 -- Return value.
351 --
352 return l_balance_value;
353 END balance_value;
354 --
355 -- Procedure convert_to_table
356 --
357 PROCEDURE convert_to_table(
358 p_balance_type_id1 IN NUMBER,
359 p_balance_type_id2 IN NUMBER,
360 p_balance_type_id3 IN NUMBER,
361 p_balance_type_id4 IN NUMBER,
362 p_balance_type_id5 IN NUMBER,
363 p_balance_type_id6 IN NUMBER,
364 p_balance_type_id7 IN NUMBER,
365 p_balance_type_id8 IN NUMBER,
366 p_balance_type_id9 IN NUMBER,
367 p_balance_type_id10 IN NUMBER,
368 p_balance_type_id11 IN NUMBER,
369 p_balance_type_id12 IN NUMBER,
370 p_balance_type_id13 IN NUMBER,
371 p_balance_type_id14 IN NUMBER,
372 p_balance_type_id15 IN NUMBER,
373 p_balance_type_id16 IN NUMBER,
374 p_balance_type_id17 IN NUMBER,
375 p_balance_type_id18 IN NUMBER,
376 p_balance_type_id19 IN NUMBER,
377 p_balance_type_id20 IN NUMBER,
378 p_balance_type_id21 IN NUMBER,
379 p_balance_type_id22 IN NUMBER,
380 p_balance_type_id23 IN NUMBER,
381 p_balance_type_id24 IN NUMBER,
382 p_balance_type_id25 IN NUMBER,
383 p_balance_type_id26 IN NUMBER,
384 p_balance_type_id27 IN NUMBER,
385 p_balance_type_id28 IN NUMBER,
386 p_balance_type_id29 IN NUMBER,
387 p_balance_type_id30 IN NUMBER,
388 p_balance_type_ids OUT NOCOPY balance_type_ids_t)
389 IS
390 PROCEDURE set_to_table(
391 p_balance_type_id IN NUMBER)
392 IS
393 BEGIN
394 if p_balance_type_id is not NULL then
395 if p_balance_type_ids is NULL then
396 p_balance_type_ids := balance_type_ids_t();
397 else
398 p_balance_type_ids.extend;
399 p_balance_type_ids(p_balance_type_ids.count) := p_balance_type_id;
400 end if;
401 end if;
402 END set_to_table;
403 BEGIN
404 set_to_table(p_balance_type_id1);
405 set_to_table(p_balance_type_id1);
406 set_to_table(p_balance_type_id2);
407 set_to_table(p_balance_type_id3);
408 set_to_table(p_balance_type_id4);
409 set_to_table(p_balance_type_id5);
410 set_to_table(p_balance_type_id6);
411 set_to_table(p_balance_type_id7);
412 set_to_table(p_balance_type_id8);
413 set_to_table(p_balance_type_id9);
414 set_to_table(p_balance_type_id10);
415 set_to_table(p_balance_type_id11);
416 set_to_table(p_balance_type_id12);
417 set_to_table(p_balance_type_id13);
418 set_to_table(p_balance_type_id14);
422 set_to_table(p_balance_type_id18);
419 set_to_table(p_balance_type_id15);
420 set_to_table(p_balance_type_id16);
421 set_to_table(p_balance_type_id17);
423 set_to_table(p_balance_type_id19);
424 set_to_table(p_balance_type_id20);
425 set_to_table(p_balance_type_id21);
426 set_to_table(p_balance_type_id22);
427 set_to_table(p_balance_type_id23);
428 set_to_table(p_balance_type_id24);
429 set_to_table(p_balance_type_id25);
430 set_to_table(p_balance_type_id26);
431 set_to_table(p_balance_type_id27);
432 set_to_table(p_balance_type_id28);
433 set_to_table(p_balance_type_id29);
434 set_to_table(p_balance_type_id30);
435 END convert_to_table;
436 /* BUG1899306: Obsoleted run and prepay functions *************************
437 --
438 -- Function run
439 --
440 FUNCTION run(
441 p_assignment_action_id IN NUMBER,
442 p_input_value_id1 IN NUMBER DEFAULT NULL,
443 p_input_value_id2 IN NUMBER DEFAULT NULL,
444 p_input_value_id3 IN NUMBER DEFAULT NULL,
445 p_input_value_id4 IN NUMBER DEFAULT NULL,
446 p_input_value_id5 IN NUMBER DEFAULT NULL,
447 p_input_value_id6 IN NUMBER DEFAULT NULL,
448 p_input_value_id7 IN NUMBER DEFAULT NULL,
449 p_input_value_id8 IN NUMBER DEFAULT NULL,
450 p_input_value_id9 IN NUMBER DEFAULT NULL,
451 p_input_value_id10 IN NUMBER DEFAULT NULL,
452 p_input_value_id11 IN NUMBER DEFAULT NULL,
453 p_input_value_id12 IN NUMBER DEFAULT NULL,
454 p_input_value_id13 IN NUMBER DEFAULT NULL,
455 p_input_value_id14 IN NUMBER DEFAULT NULL,
456 p_input_value_id15 IN NUMBER DEFAULT NULL,
457 p_input_value_id16 IN NUMBER DEFAULT NULL,
458 p_input_value_id17 IN NUMBER DEFAULT NULL,
459 p_input_value_id18 IN NUMBER DEFAULT NULL,
460 p_input_value_id19 IN NUMBER DEFAULT NULL,
461 p_input_value_id20 IN NUMBER DEFAULT NULL,
462 p_input_value_id21 IN NUMBER DEFAULT NULL,
463 p_input_value_id22 IN NUMBER DEFAULT NULL,
464 p_input_value_id23 IN NUMBER DEFAULT NULL,
465 p_input_value_id24 IN NUMBER DEFAULT NULL,
466 p_input_value_id25 IN NUMBER DEFAULT NULL,
467 p_input_value_id26 IN NUMBER DEFAULT NULL,
468 p_input_value_id27 IN NUMBER DEFAULT NULL,
469 p_input_value_id28 IN NUMBER DEFAULT NULL,
470 p_input_value_id29 IN NUMBER DEFAULT NULL,
471 p_input_value_id30 IN NUMBER DEFAULT NULL,
472 p_input_value_id31 IN NUMBER DEFAULT NULL,
473 p_input_value_id32 IN NUMBER DEFAULT NULL,
474 p_input_value_id33 IN NUMBER DEFAULT NULL,
475 p_input_value_id34 IN NUMBER DEFAULT NULL,
476 p_input_value_id35 IN NUMBER DEFAULT NULL,
477 p_input_value_id36 IN NUMBER DEFAULT NULL,
478 p_input_value_id37 IN NUMBER DEFAULT NULL,
479 p_input_value_id38 IN NUMBER DEFAULT NULL,
480 p_input_value_id39 IN NUMBER DEFAULT NULL,
481 p_input_value_id40 IN NUMBER DEFAULT NULL,
482 p_input_value_id41 IN NUMBER DEFAULT NULL,
483 p_input_value_id42 IN NUMBER DEFAULT NULL,
484 p_input_value_id43 IN NUMBER DEFAULT NULL,
485 p_input_value_id44 IN NUMBER DEFAULT NULL,
486 p_input_value_id45 IN NUMBER DEFAULT NULL,
487 p_input_value_id46 IN NUMBER DEFAULT NULL,
488 p_input_value_id47 IN NUMBER DEFAULT NULL,
489 p_input_value_id48 IN NUMBER DEFAULT NULL,
490 p_input_value_id49 IN NUMBER DEFAULT NULL,
491 p_input_value_id50 IN NUMBER DEFAULT NULL,
492 p_input_value_id51 IN NUMBER DEFAULT NULL,
493 p_input_value_id52 IN NUMBER DEFAULT NULL,
494 p_input_value_id53 IN NUMBER DEFAULT NULL,
495 p_input_value_id54 IN NUMBER DEFAULT NULL,
496 p_input_value_id55 IN NUMBER DEFAULT NULL,
497 p_input_value_id56 IN NUMBER DEFAULT NULL,
498 p_input_value_id57 IN NUMBER DEFAULT NULL,
499 p_input_value_id58 IN NUMBER DEFAULT NULL,
500 p_input_value_id59 IN NUMBER DEFAULT NULL,
501 p_input_value_id60 IN NUMBER DEFAULT NULL,
502 p_input_value_id61 IN NUMBER DEFAULT NULL,
503 p_input_value_id62 IN NUMBER DEFAULT NULL,
504 p_input_value_id63 IN NUMBER DEFAULT NULL,
505 p_input_value_id64 IN NUMBER DEFAULT NULL,
506 p_input_value_id65 IN NUMBER DEFAULT NULL,
507 p_input_value_id66 IN NUMBER DEFAULT NULL,
508 p_input_value_id67 IN NUMBER DEFAULT NULL,
509 p_input_value_id68 IN NUMBER DEFAULT NULL,
510 p_input_value_id69 IN NUMBER DEFAULT NULL,
511 p_input_value_id70 IN NUMBER DEFAULT NULL,
512 p_input_value_id71 IN NUMBER DEFAULT NULL,
513 p_input_value_id72 IN NUMBER DEFAULT NULL,
514 p_input_value_id73 IN NUMBER DEFAULT NULL,
515 p_input_value_id74 IN NUMBER DEFAULT NULL,
516 p_input_value_id75 IN NUMBER DEFAULT NULL,
517 p_input_value_id76 IN NUMBER DEFAULT NULL,
518 p_input_value_id77 IN NUMBER DEFAULT NULL,
519 p_input_value_id78 IN NUMBER DEFAULT NULL,
520 p_input_value_id79 IN NUMBER DEFAULT NULL,
521 p_input_value_id80 IN NUMBER DEFAULT NULL,
522 p_input_value_id81 IN NUMBER DEFAULT NULL,
523 p_input_value_id82 IN NUMBER DEFAULT NULL,
524 p_input_value_id83 IN NUMBER DEFAULT NULL,
525 p_input_value_id84 IN NUMBER DEFAULT NULL,
526 p_input_value_id85 IN NUMBER DEFAULT NULL,
527 p_input_value_id86 IN NUMBER DEFAULT NULL,
528 p_input_value_id87 IN NUMBER DEFAULT NULL,
529 p_input_value_id88 IN NUMBER DEFAULT NULL,
530 p_input_value_id89 IN NUMBER DEFAULT NULL,
531 p_input_value_id90 IN NUMBER DEFAULT NULL,
532 p_input_value_id91 IN NUMBER DEFAULT NULL,
533 p_input_value_id92 IN NUMBER DEFAULT NULL,
534 p_input_value_id93 IN NUMBER DEFAULT NULL,
535 p_input_value_id94 IN NUMBER DEFAULT NULL,
536 p_input_value_id95 IN NUMBER DEFAULT NULL,
537 p_input_value_id96 IN NUMBER DEFAULT NULL,
538 p_input_value_id97 IN NUMBER DEFAULT NULL,
542 p_balance_type_id1 IN NUMBER DEFAULT NULL,
539 p_input_value_id98 IN NUMBER DEFAULT NULL,
540 p_input_value_id99 IN NUMBER DEFAULT NULL,
541 p_input_value_id100 IN NUMBER DEFAULT NULL,
543 p_balance_type_id2 IN NUMBER DEFAULT NULL,
544 p_balance_type_id3 IN NUMBER DEFAULT NULL,
545 p_balance_type_id4 IN NUMBER DEFAULT NULL,
546 p_balance_type_id5 IN NUMBER DEFAULT NULL,
547 p_balance_type_id6 IN NUMBER DEFAULT NULL,
548 p_balance_type_id7 IN NUMBER DEFAULT NULL,
549 p_balance_type_id8 IN NUMBER DEFAULT NULL,
550 p_balance_type_id9 IN NUMBER DEFAULT NULL,
551 p_balance_type_id10 IN NUMBER DEFAULT NULL,
552 p_balance_type_id11 IN NUMBER DEFAULT NULL,
553 p_balance_type_id12 IN NUMBER DEFAULT NULL,
554 p_balance_type_id13 IN NUMBER DEFAULT NULL,
555 p_balance_type_id14 IN NUMBER DEFAULT NULL,
556 p_balance_type_id15 IN NUMBER DEFAULT NULL,
557 p_balance_type_id16 IN NUMBER DEFAULT NULL,
558 p_balance_type_id17 IN NUMBER DEFAULT NULL,
559 p_balance_type_id18 IN NUMBER DEFAULT NULL,
560 p_balance_type_id19 IN NUMBER DEFAULT NULL,
561 p_balance_type_id20 IN NUMBER DEFAULT NULL,
562 p_balance_type_id21 IN NUMBER DEFAULT NULL,
563 p_balance_type_id22 IN NUMBER DEFAULT NULL,
564 p_balance_type_id23 IN NUMBER DEFAULT NULL,
565 p_balance_type_id24 IN NUMBER DEFAULT NULL,
566 p_balance_type_id25 IN NUMBER DEFAULT NULL,
567 p_balance_type_id26 IN NUMBER DEFAULT NULL,
568 p_balance_type_id27 IN NUMBER DEFAULT NULL,
569 p_balance_type_id28 IN NUMBER DEFAULT NULL,
570 p_balance_type_id29 IN NUMBER DEFAULT NULL,
571 p_balance_type_id30 IN NUMBER DEFAULT NULL) RETURN pay_jp_result_run_t
572 IS
573 --
574 -- Payroll action information.
575 --
576 CURSOR csr_pact IS
577 select ppa.effective_date
578 from pay_payroll_actions ppa,
579 pay_assignment_actions paa
580 where paa.assignment_action_id = p_assignment_action_id
581 and ppa.payroll_action_id = paa.payroll_action_id;
582 l_pact csr_pact%ROWTYPE;
583 --
584 -- Other local variables.
585 --
586 l_balance_type_ids balance_type_ids_t;
587 l_result_values_by_iv result_values_by_iv_t;
588 l_balance_values_by_bal balance_values_by_bal_t;
589 BEGIN
590 --
591 -- Get payroll action information.
592 --
593 open csr_pact;
594 fetch csr_pact into l_pact;
595 if csr_pact%FOUND then
596 --
597 -- Convert input parameters to VARRAY variable.
598 --
599 convert_to_table(
600 p_balance_type_id1 => p_balance_type_id1,
601 p_balance_type_id2 => p_balance_type_id2,
602 p_balance_type_id3 => p_balance_type_id3,
603 p_balance_type_id4 => p_balance_type_id4,
604 p_balance_type_id5 => p_balance_type_id5,
605 p_balance_type_id6 => p_balance_type_id6,
606 p_balance_type_id7 => p_balance_type_id7,
607 p_balance_type_id8 => p_balance_type_id8,
608 p_balance_type_id9 => p_balance_type_id9,
609 p_balance_type_id10 => p_balance_type_id10,
610 p_balance_type_id11 => p_balance_type_id11,
611 p_balance_type_id12 => p_balance_type_id12,
612 p_balance_type_id13 => p_balance_type_id13,
613 p_balance_type_id14 => p_balance_type_id14,
614 p_balance_type_id15 => p_balance_type_id15,
615 p_balance_type_id16 => p_balance_type_id16,
616 p_balance_type_id17 => p_balance_type_id17,
617 p_balance_type_id18 => p_balance_type_id18,
618 p_balance_type_id19 => p_balance_type_id19,
619 p_balance_type_id20 => p_balance_type_id20,
620 p_balance_type_id21 => p_balance_type_id21,
621 p_balance_type_id22 => p_balance_type_id22,
622 p_balance_type_id23 => p_balance_type_id23,
623 p_balance_type_id24 => p_balance_type_id24,
624 p_balance_type_id25 => p_balance_type_id25,
625 p_balance_type_id26 => p_balance_type_id26,
626 p_balance_type_id27 => p_balance_type_id27,
627 p_balance_type_id28 => p_balance_type_id28,
628 p_balance_type_id29 => p_balance_type_id29,
629 p_balance_type_id30 => p_balance_type_id30,
630 p_balance_type_ids => l_balance_type_ids);
631 --
632 -- Get the following informations.
633 -- 1. All Result Values
634 -- 2. Balance Values specified by l_balance_type_ids.
635 -- Balance value is returned as "0"(not "NULL" value) when no results for balance_type_id.
636 --
637 run_internal(
638 p_assignment_action_id => p_assignment_action_id,
639 p_feed_checking_date => l_pact.effective_date,
640 p_balance_type_ids => l_balance_type_ids,
641 p_result_values_by_iv => l_result_values_by_iv,
642 p_balance_values_by_bal => l_balance_values_by_bal);
643 end if;
644 close csr_pact;
645 --
646 -- Return value.
647 --
648 return pay_jp_result_run_t(
649 result_value(l_result_values_by_iv,p_input_value_id1),
650 result_value(l_result_values_by_iv,p_input_value_id2),
651 result_value(l_result_values_by_iv,p_input_value_id3),
652 result_value(l_result_values_by_iv,p_input_value_id4),
653 result_value(l_result_values_by_iv,p_input_value_id5),
654 result_value(l_result_values_by_iv,p_input_value_id6),
655 result_value(l_result_values_by_iv,p_input_value_id7),
656 result_value(l_result_values_by_iv,p_input_value_id8),
657 result_value(l_result_values_by_iv,p_input_value_id9),
658 result_value(l_result_values_by_iv,p_input_value_id10),
659 result_value(l_result_values_by_iv,p_input_value_id11),
660 result_value(l_result_values_by_iv,p_input_value_id12),
661 result_value(l_result_values_by_iv,p_input_value_id13),
662 result_value(l_result_values_by_iv,p_input_value_id14),
663 result_value(l_result_values_by_iv,p_input_value_id15),
667 result_value(l_result_values_by_iv,p_input_value_id19),
664 result_value(l_result_values_by_iv,p_input_value_id16),
665 result_value(l_result_values_by_iv,p_input_value_id17),
666 result_value(l_result_values_by_iv,p_input_value_id18),
668 result_value(l_result_values_by_iv,p_input_value_id20),
669 result_value(l_result_values_by_iv,p_input_value_id21),
670 result_value(l_result_values_by_iv,p_input_value_id22),
671 result_value(l_result_values_by_iv,p_input_value_id23),
672 result_value(l_result_values_by_iv,p_input_value_id24),
673 result_value(l_result_values_by_iv,p_input_value_id25),
674 result_value(l_result_values_by_iv,p_input_value_id26),
675 result_value(l_result_values_by_iv,p_input_value_id27),
676 result_value(l_result_values_by_iv,p_input_value_id28),
677 result_value(l_result_values_by_iv,p_input_value_id29),
678 result_value(l_result_values_by_iv,p_input_value_id30),
679 result_value(l_result_values_by_iv,p_input_value_id31),
680 result_value(l_result_values_by_iv,p_input_value_id32),
681 result_value(l_result_values_by_iv,p_input_value_id33),
682 result_value(l_result_values_by_iv,p_input_value_id34),
683 result_value(l_result_values_by_iv,p_input_value_id35),
684 result_value(l_result_values_by_iv,p_input_value_id36),
685 result_value(l_result_values_by_iv,p_input_value_id37),
686 result_value(l_result_values_by_iv,p_input_value_id38),
687 result_value(l_result_values_by_iv,p_input_value_id39),
688 result_value(l_result_values_by_iv,p_input_value_id40),
689 result_value(l_result_values_by_iv,p_input_value_id41),
690 result_value(l_result_values_by_iv,p_input_value_id42),
691 result_value(l_result_values_by_iv,p_input_value_id43),
692 result_value(l_result_values_by_iv,p_input_value_id44),
693 result_value(l_result_values_by_iv,p_input_value_id45),
694 result_value(l_result_values_by_iv,p_input_value_id46),
695 result_value(l_result_values_by_iv,p_input_value_id47),
696 result_value(l_result_values_by_iv,p_input_value_id48),
697 result_value(l_result_values_by_iv,p_input_value_id49),
698 result_value(l_result_values_by_iv,p_input_value_id50),
699 result_value(l_result_values_by_iv,p_input_value_id51),
700 result_value(l_result_values_by_iv,p_input_value_id52),
701 result_value(l_result_values_by_iv,p_input_value_id53),
702 result_value(l_result_values_by_iv,p_input_value_id54),
703 result_value(l_result_values_by_iv,p_input_value_id55),
704 result_value(l_result_values_by_iv,p_input_value_id56),
705 result_value(l_result_values_by_iv,p_input_value_id57),
706 result_value(l_result_values_by_iv,p_input_value_id58),
707 result_value(l_result_values_by_iv,p_input_value_id59),
708 result_value(l_result_values_by_iv,p_input_value_id60),
709 result_value(l_result_values_by_iv,p_input_value_id61),
710 result_value(l_result_values_by_iv,p_input_value_id62),
711 result_value(l_result_values_by_iv,p_input_value_id63),
712 result_value(l_result_values_by_iv,p_input_value_id64),
713 result_value(l_result_values_by_iv,p_input_value_id65),
714 result_value(l_result_values_by_iv,p_input_value_id66),
715 result_value(l_result_values_by_iv,p_input_value_id67),
716 result_value(l_result_values_by_iv,p_input_value_id68),
717 result_value(l_result_values_by_iv,p_input_value_id69),
718 result_value(l_result_values_by_iv,p_input_value_id70),
719 result_value(l_result_values_by_iv,p_input_value_id71),
720 result_value(l_result_values_by_iv,p_input_value_id72),
721 result_value(l_result_values_by_iv,p_input_value_id73),
722 result_value(l_result_values_by_iv,p_input_value_id74),
723 result_value(l_result_values_by_iv,p_input_value_id75),
724 result_value(l_result_values_by_iv,p_input_value_id76),
725 result_value(l_result_values_by_iv,p_input_value_id77),
726 result_value(l_result_values_by_iv,p_input_value_id78),
727 result_value(l_result_values_by_iv,p_input_value_id79),
728 result_value(l_result_values_by_iv,p_input_value_id80),
729 result_value(l_result_values_by_iv,p_input_value_id81),
730 result_value(l_result_values_by_iv,p_input_value_id82),
731 result_value(l_result_values_by_iv,p_input_value_id83),
732 result_value(l_result_values_by_iv,p_input_value_id84),
733 result_value(l_result_values_by_iv,p_input_value_id85),
734 result_value(l_result_values_by_iv,p_input_value_id86),
735 result_value(l_result_values_by_iv,p_input_value_id87),
736 result_value(l_result_values_by_iv,p_input_value_id88),
737 result_value(l_result_values_by_iv,p_input_value_id89),
738 result_value(l_result_values_by_iv,p_input_value_id90),
739 result_value(l_result_values_by_iv,p_input_value_id91),
740 result_value(l_result_values_by_iv,p_input_value_id92),
741 result_value(l_result_values_by_iv,p_input_value_id93),
742 result_value(l_result_values_by_iv,p_input_value_id94),
743 result_value(l_result_values_by_iv,p_input_value_id95),
744 result_value(l_result_values_by_iv,p_input_value_id96),
745 result_value(l_result_values_by_iv,p_input_value_id97),
746 result_value(l_result_values_by_iv,p_input_value_id98),
747 result_value(l_result_values_by_iv,p_input_value_id99),
748 result_value(l_result_values_by_iv,p_input_value_id100),
749 balance_value(l_balance_values_by_bal,p_balance_type_id1),
750 balance_value(l_balance_values_by_bal,p_balance_type_id2),
751 balance_value(l_balance_values_by_bal,p_balance_type_id3),
752 balance_value(l_balance_values_by_bal,p_balance_type_id4),
753 balance_value(l_balance_values_by_bal,p_balance_type_id5),
754 balance_value(l_balance_values_by_bal,p_balance_type_id6),
755 balance_value(l_balance_values_by_bal,p_balance_type_id7),
759 balance_value(l_balance_values_by_bal,p_balance_type_id11),
756 balance_value(l_balance_values_by_bal,p_balance_type_id8),
757 balance_value(l_balance_values_by_bal,p_balance_type_id9),
758 balance_value(l_balance_values_by_bal,p_balance_type_id10),
760 balance_value(l_balance_values_by_bal,p_balance_type_id12),
761 balance_value(l_balance_values_by_bal,p_balance_type_id13),
762 balance_value(l_balance_values_by_bal,p_balance_type_id14),
763 balance_value(l_balance_values_by_bal,p_balance_type_id15),
764 balance_value(l_balance_values_by_bal,p_balance_type_id16),
765 balance_value(l_balance_values_by_bal,p_balance_type_id17),
766 balance_value(l_balance_values_by_bal,p_balance_type_id18),
767 balance_value(l_balance_values_by_bal,p_balance_type_id19),
768 balance_value(l_balance_values_by_bal,p_balance_type_id20),
769 balance_value(l_balance_values_by_bal,p_balance_type_id21),
770 balance_value(l_balance_values_by_bal,p_balance_type_id22),
771 balance_value(l_balance_values_by_bal,p_balance_type_id23),
772 balance_value(l_balance_values_by_bal,p_balance_type_id24),
773 balance_value(l_balance_values_by_bal,p_balance_type_id25),
774 balance_value(l_balance_values_by_bal,p_balance_type_id26),
775 balance_value(l_balance_values_by_bal,p_balance_type_id27),
776 balance_value(l_balance_values_by_bal,p_balance_type_id28),
777 balance_value(l_balance_values_by_bal,p_balance_type_id29),
778 balance_value(l_balance_values_by_bal,p_balance_type_id30));
779 END run;
780 --
781 FUNCTION prepay(
782 p_assignment_action_id IN NUMBER,
783 p_input_value_id1 IN NUMBER DEFAULT NULL,
784 p_input_value_id2 IN NUMBER DEFAULT NULL,
785 p_input_value_id3 IN NUMBER DEFAULT NULL,
786 p_input_value_id4 IN NUMBER DEFAULT NULL,
787 p_input_value_id5 IN NUMBER DEFAULT NULL,
788 p_input_value_id6 IN NUMBER DEFAULT NULL,
789 p_input_value_id7 IN NUMBER DEFAULT NULL,
790 p_input_value_id8 IN NUMBER DEFAULT NULL,
791 p_input_value_id9 IN NUMBER DEFAULT NULL,
792 p_input_value_id10 IN NUMBER DEFAULT NULL,
793 p_input_value_id11 IN NUMBER DEFAULT NULL,
794 p_input_value_id12 IN NUMBER DEFAULT NULL,
795 p_input_value_id13 IN NUMBER DEFAULT NULL,
796 p_input_value_id14 IN NUMBER DEFAULT NULL,
797 p_input_value_id15 IN NUMBER DEFAULT NULL,
798 p_input_value_id16 IN NUMBER DEFAULT NULL,
799 p_input_value_id17 IN NUMBER DEFAULT NULL,
800 p_input_value_id18 IN NUMBER DEFAULT NULL,
801 p_input_value_id19 IN NUMBER DEFAULT NULL,
802 p_input_value_id20 IN NUMBER DEFAULT NULL,
803 p_input_value_id21 IN NUMBER DEFAULT NULL,
804 p_input_value_id22 IN NUMBER DEFAULT NULL,
805 p_input_value_id23 IN NUMBER DEFAULT NULL,
806 p_input_value_id24 IN NUMBER DEFAULT NULL,
807 p_input_value_id25 IN NUMBER DEFAULT NULL,
808 p_input_value_id26 IN NUMBER DEFAULT NULL,
809 p_input_value_id27 IN NUMBER DEFAULT NULL,
810 p_input_value_id28 IN NUMBER DEFAULT NULL,
811 p_input_value_id29 IN NUMBER DEFAULT NULL,
812 p_input_value_id30 IN NUMBER DEFAULT NULL,
813 p_input_value_id31 IN NUMBER DEFAULT NULL,
814 p_input_value_id32 IN NUMBER DEFAULT NULL,
815 p_input_value_id33 IN NUMBER DEFAULT NULL,
816 p_input_value_id34 IN NUMBER DEFAULT NULL,
817 p_input_value_id35 IN NUMBER DEFAULT NULL,
818 p_input_value_id36 IN NUMBER DEFAULT NULL,
819 p_input_value_id37 IN NUMBER DEFAULT NULL,
820 p_input_value_id38 IN NUMBER DEFAULT NULL,
821 p_input_value_id39 IN NUMBER DEFAULT NULL,
822 p_input_value_id40 IN NUMBER DEFAULT NULL,
823 p_input_value_id41 IN NUMBER DEFAULT NULL,
824 p_input_value_id42 IN NUMBER DEFAULT NULL,
825 p_input_value_id43 IN NUMBER DEFAULT NULL,
826 p_input_value_id44 IN NUMBER DEFAULT NULL,
827 p_input_value_id45 IN NUMBER DEFAULT NULL,
828 p_input_value_id46 IN NUMBER DEFAULT NULL,
829 p_input_value_id47 IN NUMBER DEFAULT NULL,
830 p_input_value_id48 IN NUMBER DEFAULT NULL,
831 p_input_value_id49 IN NUMBER DEFAULT NULL,
832 p_input_value_id50 IN NUMBER DEFAULT NULL,
833 p_input_value_id51 IN NUMBER DEFAULT NULL,
834 p_input_value_id52 IN NUMBER DEFAULT NULL,
835 p_input_value_id53 IN NUMBER DEFAULT NULL,
836 p_input_value_id54 IN NUMBER DEFAULT NULL,
837 p_input_value_id55 IN NUMBER DEFAULT NULL,
838 p_input_value_id56 IN NUMBER DEFAULT NULL,
839 p_input_value_id57 IN NUMBER DEFAULT NULL,
840 p_input_value_id58 IN NUMBER DEFAULT NULL,
841 p_input_value_id59 IN NUMBER DEFAULT NULL,
842 p_input_value_id60 IN NUMBER DEFAULT NULL,
843 p_input_value_id61 IN NUMBER DEFAULT NULL,
844 p_input_value_id62 IN NUMBER DEFAULT NULL,
845 p_input_value_id63 IN NUMBER DEFAULT NULL,
846 p_input_value_id64 IN NUMBER DEFAULT NULL,
847 p_input_value_id65 IN NUMBER DEFAULT NULL,
848 p_input_value_id66 IN NUMBER DEFAULT NULL,
849 p_input_value_id67 IN NUMBER DEFAULT NULL,
850 p_input_value_id68 IN NUMBER DEFAULT NULL,
851 p_input_value_id69 IN NUMBER DEFAULT NULL,
852 p_input_value_id70 IN NUMBER DEFAULT NULL,
853 p_input_value_id71 IN NUMBER DEFAULT NULL,
854 p_input_value_id72 IN NUMBER DEFAULT NULL,
855 p_input_value_id73 IN NUMBER DEFAULT NULL,
856 p_input_value_id74 IN NUMBER DEFAULT NULL,
857 p_input_value_id75 IN NUMBER DEFAULT NULL,
858 p_input_value_id76 IN NUMBER DEFAULT NULL,
859 p_input_value_id77 IN NUMBER DEFAULT NULL,
860 p_input_value_id78 IN NUMBER DEFAULT NULL,
861 p_input_value_id79 IN NUMBER DEFAULT NULL,
862 p_input_value_id80 IN NUMBER DEFAULT NULL,
863 p_input_value_id81 IN NUMBER DEFAULT NULL,
864 p_input_value_id82 IN NUMBER DEFAULT NULL,
865 p_input_value_id83 IN NUMBER DEFAULT NULL,
869 p_input_value_id87 IN NUMBER DEFAULT NULL,
866 p_input_value_id84 IN NUMBER DEFAULT NULL,
867 p_input_value_id85 IN NUMBER DEFAULT NULL,
868 p_input_value_id86 IN NUMBER DEFAULT NULL,
870 p_input_value_id88 IN NUMBER DEFAULT NULL,
871 p_input_value_id89 IN NUMBER DEFAULT NULL,
872 p_input_value_id90 IN NUMBER DEFAULT NULL,
873 p_input_value_id91 IN NUMBER DEFAULT NULL,
874 p_input_value_id92 IN NUMBER DEFAULT NULL,
875 p_input_value_id93 IN NUMBER DEFAULT NULL,
876 p_input_value_id94 IN NUMBER DEFAULT NULL,
877 p_input_value_id95 IN NUMBER DEFAULT NULL,
878 p_input_value_id96 IN NUMBER DEFAULT NULL,
879 p_input_value_id97 IN NUMBER DEFAULT NULL,
880 p_input_value_id98 IN NUMBER DEFAULT NULL,
881 p_input_value_id99 IN NUMBER DEFAULT NULL,
882 p_input_value_id100 IN NUMBER DEFAULT NULL,
883 p_balance_type_id1 IN NUMBER DEFAULT NULL,
884 p_balance_type_id2 IN NUMBER DEFAULT NULL,
885 p_balance_type_id3 IN NUMBER DEFAULT NULL,
886 p_balance_type_id4 IN NUMBER DEFAULT NULL,
887 p_balance_type_id5 IN NUMBER DEFAULT NULL,
888 p_balance_type_id6 IN NUMBER DEFAULT NULL,
889 p_balance_type_id7 IN NUMBER DEFAULT NULL,
890 p_balance_type_id8 IN NUMBER DEFAULT NULL,
891 p_balance_type_id9 IN NUMBER DEFAULT NULL,
892 p_balance_type_id10 IN NUMBER DEFAULT NULL,
893 p_balance_type_id11 IN NUMBER DEFAULT NULL,
894 p_balance_type_id12 IN NUMBER DEFAULT NULL,
895 p_balance_type_id13 IN NUMBER DEFAULT NULL,
896 p_balance_type_id14 IN NUMBER DEFAULT NULL,
897 p_balance_type_id15 IN NUMBER DEFAULT NULL,
898 p_balance_type_id16 IN NUMBER DEFAULT NULL,
899 p_balance_type_id17 IN NUMBER DEFAULT NULL,
900 p_balance_type_id18 IN NUMBER DEFAULT NULL,
901 p_balance_type_id19 IN NUMBER DEFAULT NULL,
902 p_balance_type_id20 IN NUMBER DEFAULT NULL,
903 p_balance_type_id21 IN NUMBER DEFAULT NULL,
904 p_balance_type_id22 IN NUMBER DEFAULT NULL,
905 p_balance_type_id23 IN NUMBER DEFAULT NULL,
906 p_balance_type_id24 IN NUMBER DEFAULT NULL,
907 p_balance_type_id25 IN NUMBER DEFAULT NULL,
908 p_balance_type_id26 IN NUMBER DEFAULT NULL,
909 p_balance_type_id27 IN NUMBER DEFAULT NULL,
910 p_balance_type_id28 IN NUMBER DEFAULT NULL,
911 p_balance_type_id29 IN NUMBER DEFAULT NULL,
912 p_balance_type_id30 IN NUMBER DEFAULT NULL) RETURN pay_jp_result_prepay_t
913 IS
914 l_balance_type_ids balance_type_ids_t;
915 l_result_values_by_iv result_values_by_iv_t;
916 l_balance_values_by_bal balance_values_by_bal_t;
917 --
918 -- Cursor to fetch assigment actions locked by Pre-payments assact.
919 --
920 CURSOR csr_assact IS
921 select paa.assignment_action_id,
922 ppa.effective_date
923 from pay_payroll_actions ppa, -- Run pact
924 pay_assignment_actions paa, -- Run assact
925 pay_action_interlocks pai -- Locked by Prepay assact
926 where pai.locking_action_id = p_assignment_action_id
927 and paa.assignment_action_id = pai.locked_action_id
928 and ppa.payroll_action_id = paa.payroll_action_id
929 and ppa.action_type <> 'V'
930 and not exists(
931 select NULL
932 from pay_payroll_actions ppa2, -- Reversal pact
933 pay_assignment_actions paa2, -- Reversal assact
934 pay_action_interlocks pai2 -- Locked by Reversal assact
935 where pai2.locked_action_id = paa.assignment_action_id
936 and paa2.assignment_action_id = pai2.locking_action_id
937 and ppa2.payroll_action_id = paa2.payroll_action_id
938 and ppa2.action_type = 'V');
939 --
940 -- Cursor to fetch payment info.
941 --
942 CURSOR csr_payment IS
943 select ppp.value
944 from pay_pre_payments ppp
945 where ppp.assignment_action_id = p_assignment_action_id
946 order by ppp.pre_payment_id;
947 l_payments pay_jp_numbers_t;
948 --
949 -- Function payment
950 --
951 FUNCTION payment(p_index IN NUMBER) RETURN NUMBER
952 IS
953 l_payment NUMBER;
954 BEGIN
955 --
956 -- l_payments is not atomically NULL because of bulk fetch.
957 -- Even if no records returned, l_payments is instantiated.
958 --
959 if l_payments.exists(p_index) then
960 l_payment := l_payments(p_index);
961 end if;
962 --
963 -- Return value.
964 --
965 return l_payment;
966 END payment;
967 BEGIN
968 --
969 -- Convert input parameters to VARRAY variable.
970 --
971 convert_to_table(
972 p_balance_type_id1 => p_balance_type_id1,
973 p_balance_type_id2 => p_balance_type_id2,
974 p_balance_type_id3 => p_balance_type_id3,
975 p_balance_type_id4 => p_balance_type_id4,
976 p_balance_type_id5 => p_balance_type_id5,
977 p_balance_type_id6 => p_balance_type_id6,
978 p_balance_type_id7 => p_balance_type_id7,
979 p_balance_type_id8 => p_balance_type_id8,
980 p_balance_type_id9 => p_balance_type_id9,
981 p_balance_type_id10 => p_balance_type_id10,
982 p_balance_type_id11 => p_balance_type_id11,
983 p_balance_type_id12 => p_balance_type_id12,
984 p_balance_type_id13 => p_balance_type_id13,
985 p_balance_type_id14 => p_balance_type_id14,
986 p_balance_type_id15 => p_balance_type_id15,
987 p_balance_type_id16 => p_balance_type_id16,
988 p_balance_type_id17 => p_balance_type_id17,
989 p_balance_type_id18 => p_balance_type_id18,
990 p_balance_type_id19 => p_balance_type_id19,
991 p_balance_type_id20 => p_balance_type_id20,
995 p_balance_type_id24 => p_balance_type_id24,
992 p_balance_type_id21 => p_balance_type_id21,
993 p_balance_type_id22 => p_balance_type_id22,
994 p_balance_type_id23 => p_balance_type_id23,
996 p_balance_type_id25 => p_balance_type_id25,
997 p_balance_type_id26 => p_balance_type_id26,
998 p_balance_type_id27 => p_balance_type_id27,
999 p_balance_type_id28 => p_balance_type_id28,
1000 p_balance_type_id29 => p_balance_type_id29,
1001 p_balance_type_id30 => p_balance_type_id30,
1002 p_balance_type_ids => l_balance_type_ids);
1003 --
1004 -- Looped by assacts locked by Pre-payments assact.
1005 --
1006 for l_assact in csr_assact loop
1007 run_internal(
1008 p_assignment_action_id => l_assact.assignment_action_id,
1009 p_feed_checking_date => l_assact.effective_date,
1010 p_balance_type_ids => l_balance_type_ids,
1011 p_result_values_by_iv => l_result_values_by_iv,
1012 p_balance_values_by_bal => l_balance_values_by_bal);
1013 end loop;
1014 --
1015 -- Setup Payment information with BULK COLLECT.
1016 --
1017 open csr_payment;
1018 fetch csr_payment bulk collect into l_payments;
1019 close csr_payment;
1020 --
1021 -- Return value.
1022 --
1023 return pay_jp_result_prepay_t(
1024 result_value(l_result_values_by_iv,p_input_value_id1),
1025 result_value(l_result_values_by_iv,p_input_value_id2),
1026 result_value(l_result_values_by_iv,p_input_value_id3),
1027 result_value(l_result_values_by_iv,p_input_value_id4),
1028 result_value(l_result_values_by_iv,p_input_value_id5),
1029 result_value(l_result_values_by_iv,p_input_value_id6),
1030 result_value(l_result_values_by_iv,p_input_value_id7),
1031 result_value(l_result_values_by_iv,p_input_value_id8),
1032 result_value(l_result_values_by_iv,p_input_value_id9),
1033 result_value(l_result_values_by_iv,p_input_value_id10),
1034 result_value(l_result_values_by_iv,p_input_value_id11),
1035 result_value(l_result_values_by_iv,p_input_value_id12),
1036 result_value(l_result_values_by_iv,p_input_value_id13),
1037 result_value(l_result_values_by_iv,p_input_value_id14),
1038 result_value(l_result_values_by_iv,p_input_value_id15),
1039 result_value(l_result_values_by_iv,p_input_value_id16),
1040 result_value(l_result_values_by_iv,p_input_value_id17),
1041 result_value(l_result_values_by_iv,p_input_value_id18),
1042 result_value(l_result_values_by_iv,p_input_value_id19),
1043 result_value(l_result_values_by_iv,p_input_value_id20),
1044 result_value(l_result_values_by_iv,p_input_value_id21),
1045 result_value(l_result_values_by_iv,p_input_value_id22),
1046 result_value(l_result_values_by_iv,p_input_value_id23),
1047 result_value(l_result_values_by_iv,p_input_value_id24),
1048 result_value(l_result_values_by_iv,p_input_value_id25),
1049 result_value(l_result_values_by_iv,p_input_value_id26),
1050 result_value(l_result_values_by_iv,p_input_value_id27),
1051 result_value(l_result_values_by_iv,p_input_value_id28),
1052 result_value(l_result_values_by_iv,p_input_value_id29),
1053 result_value(l_result_values_by_iv,p_input_value_id30),
1054 result_value(l_result_values_by_iv,p_input_value_id31),
1055 result_value(l_result_values_by_iv,p_input_value_id32),
1056 result_value(l_result_values_by_iv,p_input_value_id33),
1057 result_value(l_result_values_by_iv,p_input_value_id34),
1058 result_value(l_result_values_by_iv,p_input_value_id35),
1059 result_value(l_result_values_by_iv,p_input_value_id36),
1060 result_value(l_result_values_by_iv,p_input_value_id37),
1061 result_value(l_result_values_by_iv,p_input_value_id38),
1062 result_value(l_result_values_by_iv,p_input_value_id39),
1063 result_value(l_result_values_by_iv,p_input_value_id40),
1064 result_value(l_result_values_by_iv,p_input_value_id41),
1065 result_value(l_result_values_by_iv,p_input_value_id42),
1066 result_value(l_result_values_by_iv,p_input_value_id43),
1067 result_value(l_result_values_by_iv,p_input_value_id44),
1068 result_value(l_result_values_by_iv,p_input_value_id45),
1069 result_value(l_result_values_by_iv,p_input_value_id46),
1070 result_value(l_result_values_by_iv,p_input_value_id47),
1071 result_value(l_result_values_by_iv,p_input_value_id48),
1072 result_value(l_result_values_by_iv,p_input_value_id49),
1073 result_value(l_result_values_by_iv,p_input_value_id50),
1074 result_value(l_result_values_by_iv,p_input_value_id51),
1075 result_value(l_result_values_by_iv,p_input_value_id52),
1076 result_value(l_result_values_by_iv,p_input_value_id53),
1077 result_value(l_result_values_by_iv,p_input_value_id54),
1078 result_value(l_result_values_by_iv,p_input_value_id55),
1079 result_value(l_result_values_by_iv,p_input_value_id56),
1080 result_value(l_result_values_by_iv,p_input_value_id57),
1081 result_value(l_result_values_by_iv,p_input_value_id58),
1082 result_value(l_result_values_by_iv,p_input_value_id59),
1083 result_value(l_result_values_by_iv,p_input_value_id60),
1084 result_value(l_result_values_by_iv,p_input_value_id61),
1085 result_value(l_result_values_by_iv,p_input_value_id62),
1086 result_value(l_result_values_by_iv,p_input_value_id63),
1087 result_value(l_result_values_by_iv,p_input_value_id64),
1088 result_value(l_result_values_by_iv,p_input_value_id65),
1089 result_value(l_result_values_by_iv,p_input_value_id66),
1090 result_value(l_result_values_by_iv,p_input_value_id67),
1091 result_value(l_result_values_by_iv,p_input_value_id68),
1092 result_value(l_result_values_by_iv,p_input_value_id69),
1093 result_value(l_result_values_by_iv,p_input_value_id70),
1094 result_value(l_result_values_by_iv,p_input_value_id71),
1095 result_value(l_result_values_by_iv,p_input_value_id72),
1096 result_value(l_result_values_by_iv,p_input_value_id73),
1097 result_value(l_result_values_by_iv,p_input_value_id74),
1098 result_value(l_result_values_by_iv,p_input_value_id75),
1099 result_value(l_result_values_by_iv,p_input_value_id76),
1100 result_value(l_result_values_by_iv,p_input_value_id77),
1101 result_value(l_result_values_by_iv,p_input_value_id78),
1102 result_value(l_result_values_by_iv,p_input_value_id79),
1103 result_value(l_result_values_by_iv,p_input_value_id80),
1104 result_value(l_result_values_by_iv,p_input_value_id81),
1105 result_value(l_result_values_by_iv,p_input_value_id82),
1106 result_value(l_result_values_by_iv,p_input_value_id83),
1107 result_value(l_result_values_by_iv,p_input_value_id84),
1108 result_value(l_result_values_by_iv,p_input_value_id85),
1109 result_value(l_result_values_by_iv,p_input_value_id86),
1110 result_value(l_result_values_by_iv,p_input_value_id87),
1111 result_value(l_result_values_by_iv,p_input_value_id88),
1112 result_value(l_result_values_by_iv,p_input_value_id89),
1113 result_value(l_result_values_by_iv,p_input_value_id90),
1114 result_value(l_result_values_by_iv,p_input_value_id91),
1115 result_value(l_result_values_by_iv,p_input_value_id92),
1116 result_value(l_result_values_by_iv,p_input_value_id93),
1117 result_value(l_result_values_by_iv,p_input_value_id94),
1118 result_value(l_result_values_by_iv,p_input_value_id95),
1119 result_value(l_result_values_by_iv,p_input_value_id96),
1120 result_value(l_result_values_by_iv,p_input_value_id97),
1121 result_value(l_result_values_by_iv,p_input_value_id98),
1122 result_value(l_result_values_by_iv,p_input_value_id99),
1123 result_value(l_result_values_by_iv,p_input_value_id100),
1124 balance_value(l_balance_values_by_bal,p_balance_type_id1),
1125 balance_value(l_balance_values_by_bal,p_balance_type_id2),
1126 balance_value(l_balance_values_by_bal,p_balance_type_id3),
1127 balance_value(l_balance_values_by_bal,p_balance_type_id4),
1128 balance_value(l_balance_values_by_bal,p_balance_type_id5),
1129 balance_value(l_balance_values_by_bal,p_balance_type_id6),
1130 balance_value(l_balance_values_by_bal,p_balance_type_id7),
1131 balance_value(l_balance_values_by_bal,p_balance_type_id8),
1132 balance_value(l_balance_values_by_bal,p_balance_type_id9),
1133 balance_value(l_balance_values_by_bal,p_balance_type_id10),
1134 balance_value(l_balance_values_by_bal,p_balance_type_id11),
1135 balance_value(l_balance_values_by_bal,p_balance_type_id12),
1136 balance_value(l_balance_values_by_bal,p_balance_type_id13),
1137 balance_value(l_balance_values_by_bal,p_balance_type_id14),
1138 balance_value(l_balance_values_by_bal,p_balance_type_id15),
1139 balance_value(l_balance_values_by_bal,p_balance_type_id16),
1140 balance_value(l_balance_values_by_bal,p_balance_type_id17),
1141 balance_value(l_balance_values_by_bal,p_balance_type_id18),
1142 balance_value(l_balance_values_by_bal,p_balance_type_id19),
1143 balance_value(l_balance_values_by_bal,p_balance_type_id20),
1144 balance_value(l_balance_values_by_bal,p_balance_type_id21),
1145 balance_value(l_balance_values_by_bal,p_balance_type_id22),
1146 balance_value(l_balance_values_by_bal,p_balance_type_id23),
1147 balance_value(l_balance_values_by_bal,p_balance_type_id24),
1148 balance_value(l_balance_values_by_bal,p_balance_type_id25),
1149 balance_value(l_balance_values_by_bal,p_balance_type_id26),
1150 balance_value(l_balance_values_by_bal,p_balance_type_id27),
1151 balance_value(l_balance_values_by_bal,p_balance_type_id28),
1152 balance_value(l_balance_values_by_bal,p_balance_type_id29),
1153 balance_value(l_balance_values_by_bal,p_balance_type_id30),
1154 payment(1),
1155 payment(2),
1156 payment(3),
1157 payment(4),
1158 payment(5));
1159 END prepay;
1160 ************************************************************************ */
1161 --
1162 END pay_jp_result_pkg;
[Home] [Help]