[Home] [Help]
PACKAGE: APPS.OKE_FUNDING_UTIL_PKG
Source
1 PACKAGE OKE_FUNDING_UTIL_PKG AUTHID CURRENT_USER as
2 /* $Header: OKEFUTLS.pls 115.16 2002/11/21 20:46:43 syho ship $ */
3
4 --
5 -- Global Variables
6 --
7
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKE_API.G_APP_NAME;
9 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
10 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
12
13 --
14 -- Proj_Sum_Type
15 --
16
17 TYPE proj_sum_type is RECORD
18 (project_id NUMBER ,
19 project_number VARCHAR2(25) ,
20 amount NUMBER ,
21 org_id NUMBER
22 );
23
24 --
25 -- Proj_Sum_Tbl_Type
26 --
27
28 TYPE proj_sum_tbl_type is TABLE of proj_sum_type
29 index by binary_integer;
30
31 --
32 -- Task_Sum_Type
33 --
34
35 TYPE task_sum_type is RECORD
36 (task_id NUMBER ,
37 project_id NUMBER ,
38 project_number VARCHAR2(25) ,
39 amount NUMBER ,
40 org_id NUMBER
41 );
42
43 --
44 -- Task_Sum_Tbl_Type
45 --
46
47 TYPE task_sum_tbl_type is TABLE of task_sum_type
48 index by binary_integer;
49
50 --
51 -- Funding_Level_Type
52 --
53
54 TYPE funding_level_type is RECORD
55 (project_id NUMBER ,
56 funding_level VARCHAR2(1)
57 );
58
59 --
60 -- Funding_Level_Tbl_Type
61 --
62
63 TYPE funding_level_tbl_type is TABLE of funding_level_type
64 index by binary_integer;
65
66
67 --
68 -- Functions and Procedures
69 --
70
71 --
72 -- Procedure : validate_source_pool_amount
73 --
74 -- Purpose : check if there is enough funding from the pool party to be allocated
75 --
76 -- Parameters :
77 -- (in) x_first_amount number amount
78 -- x_source_id number funding_source_id
79 -- x_pool_party_id number pool_party_id
80 -- x_new_flag varchar2 new funding source record
81 -- Y : new funding source
82 --
83 -- (out) x_return_status varchar2 return status
84 -- Y : valid
85 -- N : invalid
86 --
87
88 PROCEDURE validate_source_pool_amount(x_first_amount number ,
89 x_source_id number ,
90 x_pool_party_id number ,
91 x_new_flag varchar2 ,
92 x_return_status OUT NOCOPY varchar2 );
93
94
95
96 --
97 -- Procedure : validate_source_pool_date
98 --
99 -- Purpose : check if
100 -- 1) funding source start date assocated w/ the pool party >= pool party start date
101 -- 2) funding source end date associated w/ the pool party <= pool party end date
102 --
103 -- Parameters :
104 -- (in) x_start_end varchar2 date validation choice
105 -- START : start date
106 -- END : end date
107 -- x_pool_party_id number pool party id
108 -- x_date date date to be validated
109 --
110 -- (out) x_return_status varchar2 return status
111 -- Y : valid
112 -- N : invalid
113 --
114
115 PROCEDURE validate_source_pool_date(x_start_end varchar2 ,
116 x_pool_party_id number ,
117 x_date date ,
118 x_return_status OUT NOCOPY varchar2 );
119
120
121
122 --
123 -- Procedure : validate_alloc_source_amount
124 --
125 -- Purpose : check if the new funding source amount >= sum of its allocations
126 --
127 -- Parameters :
128 -- (in) x_source_id number funding source id
129 -- x_allocation_id number funding allocation id
130 -- x_amount number allocation amount
131 --
132 -- (out) x_return_status varchar2 return status
133 -- Y : valid
134 -- N : invalid
135 --
136
137 PROCEDURE validate_alloc_source_amount(x_source_id number ,
138 x_allocation_id number ,
139 x_amount number ,
140 x_return_status OUT NOCOPY varchar2 );
141
142
143
144 --
145 -- Procedure : validate_alloc_source_limit
146 --
147 -- Purpose : check if
148 -- w/ allocation_id passed in :
149 -- there is enough funding source hard limit to be allocated for the newly allocated
150 -- hard limit
151 --
152 -- w/o allocaiton_id passed in
153 -- the new funding source hard limit is >= sum of its hard limit allocations
154 --
155 -- Parameters :
156 -- (in) x_source_id number funding source id
157 -- x_allocation_id number funding allocation id (optional)
158 -- x_amount number limit amount
159 -- x_revenue_amount number revenue hard limit
160 --
161 -- (out) x_type varchar2 hard limit type (INVOICE/REVENUE)
162 -- x_return_status varchar2 return status
163 -- Y : valid
164 -- N : invalid
165 --
166
167 PROCEDURE validate_alloc_source_limit(x_source_id number ,
168 x_allocation_id number ,
169 x_amount number ,
170 x_revenue_amount number ,
171 x_type OUT NOCOPY varchar2 ,
172 x_return_status OUT NOCOPY varchar2 );
173
174
175
176 --
177 -- Procedure : validate_pool_party_date
178 --
179 -- Purpose : check if
180 -- 1) pool party start date <= the earliest funding source start date associated w/ the pool party
181 -- 2) pool party end >= the latest funding source end date associated w/ the pool party
182 --
183 -- Parameters :
184 -- (in) x_start_end varchar2 date validation choice
185 -- START : start date
186 -- END : end date
187 -- x_pool_party_id number pool party id
188 -- x_date date date to be validated
189 --
190 -- (out) x_return_status varchar2 return status
191 -- Y : valid
192 -- N : invalid
193 --
194
195 PROCEDURE validate_pool_party_date(x_start_end varchar2 ,
196 x_pool_party_id number ,
197 x_date date ,
198 x_return_status OUT NOCOPY varchar2 );
199
200
201
202 --
203 -- Function : allocation_exist
204 --
205 -- Purpose : check if funding has been allocated for particular funding pool party or not
206 --
207 -- Parameters : x_pool_party id number pool party id
208 --
209 -- Return : Y -- allocation exists
210 -- values N -- no allocation exists
211 --
212
213 FUNCTION allocation_exist(x_pool_party_id number) return varchar2;
214
215
216
217
218 --
219 -- Procedure : validate_pool_party_amount
220 --
221 -- Purpose : check if the new pool party amount >= the allocated amount
222 --
223 -- Parameters :
224 -- (in) x_pool_party_id number pool party id
225 -- x_amount number new funding amount
226 --
227 -- (out) x_allocated_amount number calculated allocated amount
228 -- x_return_status varchar2 return status
229 -- Y : valid
230 -- N : invalid
231 --
232
233 PROCEDURE validate_pool_party_amount(x_pool_party_id number ,
234 x_amount number ,
235 x_allocated_amount OUT NOCOPY number ,
236 x_return_status OUT NOCOPY varchar2 );
237
238
239
240 --
241 -- Procedure : validate_source_alloc_date
242 --
243 -- Purpose : check if
244 -- 1) funding source start date <= the earliest funding allocation start date
245 -- 2) funding source end date >= the latest funding allocation end date
246 --
247 -- Parameters :
248 -- (in) x_start_end varchar2 date validation choice
249 -- START : start date
250 -- END : end date
251 -- x_funding_source_id number funding source id
252 -- x_date date date to be validated
253 --
254 -- (out) x_return_status varchar2 return status
255 -- Y : valid
256 -- N : invalid
257 --
258
259 PROCEDURE validate_source_alloc_date(x_start_end varchar2 ,
260 x_funding_source_id number ,
261 x_date date ,
262 x_return_status OUT NOCOPY varchar2 );
263
264
265
266 --
267 -- Procedure : validate_alloc_source_date
268 --
269 -- Purpose : check if
270 -- 1) funding allocation start date >= funding source start date
271 -- 2) funding allocation end date <= funding source end date
272 --
273 -- Parameters :
274 -- (in) x_start_end varchar2 date validation choice
275 -- START : start date
276 -- END : end date
277 -- x_funding_source_id number funding source id
278 -- x_date date date to be validated
279 --
280 -- (out) x_return_status varchar2 return status
281 -- Y : valid
282 -- N : invalid
283 --
284
285 PROCEDURE validate_alloc_source_date(x_start_end varchar2 ,
286 x_funding_source_id number ,
287 x_date date ,
288 x_return_status OUT NOCOPY varchar2 );
289
290
291
292 --
293 -- Procedure : multi_customer
294 --
295 -- Purpose : find out how many customers associated with particular project
296 --
297 -- Parameters :
298 -- (in) x_project_id number project id
299 --
300 -- (out) x_count number number of customers
301 -- x_project_number varchar2 project number
302 --
303
304 PROCEDURE multi_customer(x_project_id number ,
305 x_project_number OUT NOCOPY varchar2,
306 x_count OUT NOCOPY number );
307
308
309
310
311 --
312 -- Procedure : save_user_profile
313 --
314 -- Purpose : save user profile on the preference of showing funding wizard or not
315 --
316 -- Parameters :
317 -- (in) x_profile_name varchar2 profile name
318 -- x_value varchar2 profile value
319 --
320
321 PROCEDURE save_user_profile(x_profile_name varchar2 ,
322 x_value varchar2 );
323
324
325
326
327 --
328 -- Procedure : validate_start_end_date
329 --
330 -- Purpose : check if start date <= end date
331 --
332 -- Parameters :
333 -- (in) x_start_date date start date
334 -- x_end_date date end date
335 --
336 -- (out) x_return_status varchar2 return status
337 -- Y : valid
338 -- N : not valid
339 --
340
341 PROCEDURE validate_start_end_date(x_start_date date ,
342 x_end_date date ,
343 x_return_status OUT NOCOPY varchar2 );
344
345
346
347
348 --
349 -- Procedure : validate_source_alloc_limit
350 --
351 -- Purpose : check if funding source invoice/revenue hard limit >= sum(funding allocations invoice/revenue hard limit)
352 -- (for MCB change)
353 --
354 -- Parameters :
355 -- (in) x_source_id number funding source id
356 -- x_amount number limit amount
357 -- x_revenue_amount number revenue hard limit amount
358 --
359 -- (out) x_type varchar2 hard limit type
360 -- x_return_status varchar2 return status
361 -- Y : valid
362 -- N : invalid
363 --
364
365 PROCEDURE validate_source_alloc_limit(x_source_id number ,
366 x_amount number ,
367 x_revenue_amount number ,
368 x_type OUT NOCOPY varchar2 ,
369 x_return_status OUT NOCOPY varchar2 );
370
371
372
373 --
374 -- Procedure : validate_source_alloc_amount
375 --
376 -- Purpose : validate if funding source amount >= sum(funding allocations amount)
377 --
378 -- Parameters :
379 -- (in) x_source_id number funding source id
380 -- x_amount number amount
381 --
382 -- (out) x_return_status varchar2 return status
383 -- Y : valid
384 -- N : not valid
385 --
386
387 PROCEDURE validate_source_alloc_amount(x_source_id number ,
388 x_amount number ,
389 x_return_status OUT NOCOPY varchar2 );
390
391
392 --
393 -- Procedure : validate_hard_limit
394 --
395 -- Purpose : validate if hard limit <= funding amount
396 --
397 -- Parameters :
398 -- (in) x_fund_amount number funding amount
399 -- x_hard_limit number hard limit
400 --
401 -- (out) x_return_status varchar2 return status
402 -- Y : valid
403 -- N : not valid
404 --
405
406 PROCEDURE validate_hard_limit(x_fund_amount number ,
407 x_hard_limit number ,
408 x_return_status OUT NOCOPY varchar2 );
409
410
411 --
412 -- Procedure : get_conversion_rate
413 --
414 -- Purpose : get the conversion rate for the particular conversion type and date
415 --
416 -- Parameters :
417 -- (in) x_from_currency varchar2 conversion from currency
418 -- x_to_currency varchar2 conversion to currency
419 -- x_conversion_type varchar2 conversion type
420 -- x_conversion_date date conversion date
421 --
422 -- (out) x_conversion_rate number conversion rate
423 -- x_return_status varchar2 return status
424 -- Y : exist
425 -- N : not exist
426 --
427
428 PROCEDURE get_conversion_rate(x_from_currency varchar2 ,
429 x_to_currency varchar2 ,
430 x_conversion_type varchar2 ,
431 x_conversion_date date ,
432 x_conversion_rate out NOCOPY number ,
433 x_return_status out NOCOPY varchar2
434 );
435
436
437 --
438 -- PROCEDURE : check_agreement_exist
439 --
440 -- Purpose : check if agreement exist for the funding source
441 --
442 -- Parameters :
443 -- (in) x_funding_source_id number funding_source_id
444 --
445 -- (out) x_return_status varchar2 return status
446 -- Y : exist
447 -- N : not exist
448 --
449
450 PROCEDURE check_agreement_exist(x_funding_source_id number ,
451 x_return_status out NOCOPY varchar2 );
452
453
454
455 --
456 -- Function : get_project_currency
457 --
458 -- Purpose : get the project currency
459 --
460 -- Parameters :
461 -- (in) x_project_id number project_id
462 --
463
464 FUNCTION get_project_currency(x_project_id number) return varchar2;
465
466
467
468 --
469 -- Function : get_owned_by
470 --
471 -- Purpose : get the owned_by_person_id
472 --
473 -- Parameters :
474 -- (in) x_user_id number user id
475 --
476
477 FUNCTION get_owned_by(x_user_id number) return number;
478
479
480
481 --
482 -- PROCEDURE : get_agreement_info
483 --
484 -- Purpose : get existing agreement_type, customer_id for the existing funding_source_id
485 --
486 -- Parameters :
487 -- (in) x_funding_source_id number funding_source_id
488 --
489 -- (out) x_agreement_type varchar2 agreement_type
490 -- x_customer_id number customer_id
491 -- x_return_status varchar2 return status
492 -- Y : exist
493 -- N : not exist
494 --
495
496 PROCEDURE get_agreement_info(x_funding_source_id number ,
500 );
497 x_agreement_type out NOCOPY varchar2 ,
498 x_customer_id out NOCOPY number ,
499 x_return_status out NOCOPY varchar2
501
502
503
504 --
505 -- Procedure : update_alloc_version
506 --
507 -- Description : This procedure is used to update agreement_version and insert_update_flag of OKE_K_FUND_ALLOCATIONS table
508 --
509 -- Parameters :
510 -- (in) x_fund_allocation_id number fund_allocation_id
511 -- x_version_add number version increment
512 -- x_commit varchar2 commit flag
513 --
514
515 PROCEDURE update_alloc_version(x_fund_allocation_id IN NUMBER ,
516 x_version_add IN NUMBER ,
517 x_commit IN VARCHAR2 := OKE_API.G_FALSE
518 );
519
520
521 --
522 -- Procedure : update_source_flag
523 --
524 -- Description : This procedure is used to update agreement_flag of OKE_K_FUNDING_SOURCES table
525 --
526 -- Parameters :
527 -- (in) x_funding_source_id number funding_source_id
528 -- x_commit varchar2 commit flag
529 --
530
531 PROCEDURE update_source_flag(x_funding_source_id IN NUMBER ,
532 x_commit IN VARCHAR2 := OKE_API.G_FALSE
533 );
534
535
536 --
537 -- Procedure : funding_mode
538 --
539 -- Description : This procedure is used to check the funding mode is vaild or not
540 --
541 -- Parameters :
542 -- (in) x_proj_sum_tbl proj_sum_tbl_type allocation amount by project
543 -- x_task_sum_tbl task_sum_tbl_type allocation amount by task
544 --
545 -- (out) x_funding_level_tbl funding_level_tbl_type funding level by project
546 -- x_return_status varchar2 return_status
547 -- S: successful
548 -- E: error
549 -- x_project_err varchar2 project number with funding mode error
550 --
551
552 PROCEDURE funding_mode(x_proj_sum_tbl IN PROJ_SUM_TBL_TYPE ,
553 x_task_sum_tbl IN TASK_SUM_TBL_TYPE ,
554 x_funding_level_tbl OUT NOCOPY FUNDING_LEVEL_TBL_TYPE ,
555 x_return_status OUT NOCOPY VARCHAR2 ,
556 x_project_err OUT NOCOPY VARCHAR2
557 );
558
559
560
561 --
562 -- Procedure : get_converted_amount
563 --
564 -- Description : This function is used to calculate the allocated amount
565 --
566 -- Parameters :
567 -- (in) x_funding_source_id number funding_source_id
568 -- x_project_id number project_id
569 -- x_project_number varchar2 project number
570 -- x_amount number original amount
571 -- x_conversion_type varchar2 currency conversion type
572 -- x_conversion_date date currency conversion date
573 -- x_conversion_rate number currency conversion rate
574 --
575 -- (out) x_converted_amount number converted amount
576 -- x_return_status varchar2 return status
577 -- S: successful
578 -- E: error
579 -- U: unexpected error
580 --
581
582 PROCEDURE get_converted_amount(x_funding_source_id IN NUMBER ,
583 x_project_id IN NUMBER ,
584 x_project_number IN VARCHAR2 ,
585 x_amount IN NUMBER ,
586 -- x_org_id IN NUMBER ,
587 x_conversion_type IN VARCHAR2 ,
588 x_conversion_date IN DATE ,
589 x_conversion_rate IN NUMBER ,
590 x_converted_amount OUT NOCOPY NUMBER ,
591 x_return_status OUT NOCOPY VARCHAR2
592 );
593
594
595 --
596 -- Procedure : get_calculate_amount
597 --
598 -- Description : This procedure is used to get the converted amount
599 --
600 -- Parameters :
601 -- (in) x_conversion_type varchar2 currency conversion type
602 -- x_conversion_date date currency conversion date
603 -- x_conversion_rate number currency conversion rate
604 -- x_org_amount number original amount
605 -- x_min_unit number minimum amount unit of the currency
606 -- x_fund_currency varchar2 funding source currency
607 -- x_project_currency varchar2 project currency
608 --
609 -- (out) x_amount number converted amount
610 -- x_return_status varchar2 return status
611 -- S: successful
612 -- E: error
613 -- U: unexpected error
614 --
615
616 PROCEDURE get_calculate_amount(x_conversion_type VARCHAR2 ,
617 x_conversion_date DATE ,
618 x_conversion_rate NUMBER ,
619 x_org_amount NUMBER ,
620 x_min_unit NUMBER ,
621 x_fund_currency VARCHAR2 ,
622 x_project_currency VARCHAR2 ,
623 x_amount OUT NOCOPY NUMBER ,
624 x_return_status OUT NOCOPY VARCHAR2
625 );
626
627 END OKE_FUNDING_UTIL_PKG;