1 PACKAGE OZF_Funds_PVT AUTHID CURRENT_USER AS
2 /* $Header: ozfvfuns.pls 120.4 2011/12/29 15:00:56 apyadav ship $ */
3
4 TYPE fund_rec_type IS RECORD
5 (
6 fund_id NUMBER
7 ,last_update_date DATE
8 ,last_updated_by NUMBER
9 ,last_update_login NUMBER
10 ,creation_date DATE
11 ,created_by NUMBER
12 ,created_from VARCHAR2(30)
13 ,request_id NUMBER
14 ,program_application_id NUMBER
15 ,program_id NUMBER
16 ,program_update_date DATE
17 ,fund_number VARCHAR2(30)
18 ,parent_fund_id NUMBER
19 ,category_id NUMBER
20 ,fund_type VARCHAR2(30)
21 ,status_code VARCHAR2(30)
22 ,user_status_id NUMBER
23 ,status_date DATE
24 ,accrued_liable_account NUMBER
25 ,ded_adjustment_account NUMBER
26 ,start_date_active DATE
27 ,end_date_active DATE
28 ,currency_code_tc VARCHAR2(15)
29 ,owner NUMBER
30 ,hierarchy VARCHAR2(30)
31 ,hierarchy_level VARCHAR2(30)
32 ,hierarchy_id NUMBER
33 ,parent_node_id NUMBER
34 ,node_id NUMBER
35 ,object_version_number NUMBER
36 ,org_id NUMBER
37 ,earned_flag VARCHAR2(1)
38 ,original_budget NUMBER
39 ,transfered_in_amt NUMBER
40 ,transfered_out_amt NUMBER
41 ,holdback_amt NUMBER
42 ,planned_amt NUMBER
43 ,committed_amt NUMBER
44 ,earned_amt NUMBER
45 ,paid_amt NUMBER
46 ,liable_accnt_segments VARCHAR2(155)
47 ,adjustment_accnt_segments VARCHAR2(155)
48 ,short_name VARCHAR2(80)
49 ,description VARCHAR2(4000)
50 ,language VARCHAR2(4)
51 ,source_lang VARCHAR2(4)
52 ,start_period_name VARCHAR2(15)
53 ,end_period_name VARCHAR2(15)
54 ,fund_calendar VARCHAR2(15)
55 ,accrue_to_level_id NUMBER
56 ,accrual_quantity NUMBER
57 ,accrual_phase VARCHAR2(30)
58 ,accrual_cap NUMBER
59 ,accrual_uom VARCHAR2(30)
60 ,accrual_method VARCHAR2(30)
61 ,accrual_operand VARCHAR2(30)
62 ,accrual_rate NUMBER
63 ,accrual_basis VARCHAR2(30)
64 ,accrual_discount_level VARCHAR2(30)
65 ,custom_setup_id NUMBER
66 -- added 06/21/2001 mpande
67 ,threshold_id NUMBER
68 ,business_unit_id NUMBER
69 ,country_id NUMBER
70 ,task_id NUMBER
71 ,recal_committed NUMBER
72 ,attribute_category VARCHAR2(30)
73 ,attribute1 VARCHAR2(150)
74 ,attribute2 VARCHAR2(150)
75 ,attribute3 VARCHAR2(150)
76 ,attribute4 VARCHAR2(150)
77 ,attribute5 VARCHAR2(150)
78 ,attribute6 VARCHAR2(150)
79 ,attribute7 VARCHAR2(150)
80 ,attribute8 VARCHAR2(150)
81 ,attribute9 VARCHAR2(150)
82 ,attribute10 VARCHAR2(150)
83 ,attribute11 VARCHAR2(150)
84 ,attribute12 VARCHAR2(150)
85 ,attribute13 VARCHAR2(150)
86 ,attribute14 VARCHAR2(150)
87 ,attribute15 VARCHAR2(150)
88 -- the follwoign columns are obsolete and not used anymore
89 ,fund_usage VARCHAR2(30)
90 ,plan_type VARCHAR2(30)
91 ,plan_id NUMBER
92 ,apply_accrual_on VARCHAR2(30)
93 ,level_value VARCHAR2(240)
94 ,budget_flag VARCHAR2(1)
95 ,liability_flag VARCHAR2(1)
96 ,set_of_books_id NUMBER
97 ,start_period_id NUMBER
98 ,end_period_id NUMBER
99 ,budget_amount_tc NUMBER
100 ,budget_amount_fc NUMBER
101 ,available_amount NUMBER
102 ,distributed_amount NUMBER
103 ,currency_code_fc VARCHAR2(15)
104 ,exchange_rate_type VARCHAR2(30)
105 ,exchange_rate_date DATE
106 ,exchange_rate NUMBER
107 ,department_id NUMBER
108 ,costcentre_id NUMBER
109 -- added by feliu on 02/08/02
110 ,rollup_original_budget NUMBER
111 ,rollup_transfered_in_amt NUMBER
112 ,rollup_transfered_out_amt NUMBER
113 ,rollup_holdback_amt NUMBER
114 ,rollup_planned_amt NUMBER
115 ,rollup_committed_amt NUMBER
116 ,rollup_earned_amt NUMBER
117 ,rollup_paid_amt NUMBER
118 ,rollup_recal_committed NUMBER
119 -- added mpande 10/25/2002 11.5.9
120 ,retroactive_flag VARCHAR2(1)
121 ,qualifier_id NUMBER
122 -- niprakas added
123 ,prev_fund_id NUMBER
124 ,transfered_flag VARCHAR2(1)
125 ,utilized_amt NUMBER
126 ,rollup_utilized_amt NUMBER
127 -- kdass added
128 ,product_spread_time_id NUMBER
129 -- sangara added 06/25/05
130 ,activation_date DATE
131 -- kdass - R12 MOAC changes
132 ,ledger_id NUMBER
133 );
134
135 TYPE fund_rec_int_type IS RECORD
136 (
137 BATCH_ID NUMBER,
138 FUND_ID NUMBER,
139 BUDGET_AMOUNT_TC NUMBER,
140 BUDGET_AMOUNT_FC NUMBER,
141 AVAILABLE_AMOUNT NUMBER,
142 TRANSFERED_IN_AMT NUMBER,
143 TRANSFERED_OUT_AMT NUMBER,
144 PLANNED_AMT NUMBER,
145 COMMITTED_AMT NUMBER,
146 EARNED_AMT NUMBER,
147 PAID_AMT NUMBER,
148 RECAL_COMMITTED NUMBER,
149 ROLLUP_ORIGINAL_BUDGET NUMBER,
150 ROLLUP_TRANSFERED_IN_AMT NUMBER,
151 ROLLUP_TRANSFERED_OUT_AMT NUMBER,
152 ROLLUP_HOLDBACK_AMT NUMBER,
153 ROLLUP_PLANNED_AMT NUMBER,
154 ROLLUP_COMMITTED_AMT NUMBER,
155 ROLLUP_RECAL_COMMITTED NUMBER,
156 ROLLUP_EARNED_AMT NUMBER,
157 ROLLUP_PAID_AMT NUMBER,
158 UTILIZED_AMT NUMBER,
159 ROLLUP_UTILIZED_AMT NUMBER
160 );
161
162 ---------------------------------------------------------------------
163 -- PROCEDURE
164 -- Create_Fund
165 --
166 -- PURPOSE
167 -- Create a new fund.
168 --
169 -- PARAMETERS
170 -- p_fund_rec: the new record to be inserted
171 -- x_fund_id: return the fund_id of the new fund
172 --
173 -- NOTES
174 -- 1. object_version_number will be set to 1.
175 -- 2. If fund_id is passed in, the uniqueness will be checked.
176 -- Raise exception in case of duplicates.
177 -- 3. If fund_id is not passed in, generate a unique one from
178 -- the sequence.
179 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
180 -- Raise exception for invalid flag.
181 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
182 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
183 ---------------------------------------------------------------------
184 PROCEDURE Create_Fund(
185 p_api_version IN NUMBER
186 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
187 ,p_commit IN VARCHAR2 := FND_API.g_false
188 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
189
190 ,x_return_status OUT NOCOPY VARCHAR2
191 ,x_msg_count OUT NOCOPY NUMBER
192 ,x_msg_data OUT NOCOPY VARCHAR2
193
194 ,p_fund_rec IN fund_rec_type
195 ,x_fund_id OUT NOCOPY NUMBER
196 );
197
198
199 --------------------------------------------------------------------
200 -- PROCEDURE
201 -- Delete_Fund
202 --
203 -- PURPOSE
204 -- Delete a fund.
205 --
206 -- PARAMETERS
207 -- p_fund_id: the fund_id
208 -- p_object_version: the object_version_number
209 --
210 -- NOTES
211 -- 1. Raise exception if the object_version_number doesn't match.
212 --------------------------------------------------------------------
213 PROCEDURE Delete_Fund(
214 p_api_version IN NUMBER
215 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
216 ,p_commit IN VARCHAR2 := FND_API.g_false
217
218 ,x_return_status OUT NOCOPY VARCHAR2
219 ,x_msg_count OUT NOCOPY NUMBER
220 ,x_msg_data OUT NOCOPY VARCHAR2
221
222 ,p_fund_id IN NUMBER
223 ,p_object_version IN NUMBER
224 );
225
226
227 -------------------------------------------------------------------
228 -- PROCEDURE
229 -- Lock_Fund
230 --
231 -- PURPOSE
232 -- Lock a fund.
233 --
234 -- PARAMETERS
235 -- p_fund_id: the fund_id
236 -- p_object_version: the object_version_number
237 --
238 -- NOTES
239 -- 1. Raise exception if the object_version_number doesn't match.
240 --------------------------------------------------------------------
241 PROCEDURE Lock_Fund(
242 p_api_version IN NUMBER
243 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
244
245 ,x_return_status OUT NOCOPY VARCHAR2
246 ,x_msg_count OUT NOCOPY NUMBER
247 ,x_msg_data OUT NOCOPY VARCHAR2
248
249 ,p_fund_id IN NUMBER
250 ,p_object_version IN NUMBER
251 );
252
253
254 ---------------------------------------------------------------------
255 -- PROCEDURE
256 -- Update_Fund
257 --
258 -- PURPOSE
259 -- Update a fund.
260 --
261 -- PARAMETERS
262 -- p_fund_rec: the record with new items.
263 -- p_mode : determines what sort of validation is to be performed during update.
264 -- : The mode should always be 'UPDATE' except when updating the earned or committed amount
265 --
266 -- NOTES
267 -- 1. Raise exception if the object_version_number doesn't match.
268 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
269 -- that column won't be updated.
270 ----------------------------------------------------------------------
271 PROCEDURE Update_Fund(
272 p_api_version IN NUMBER
273 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
274 ,p_commit IN VARCHAR2 := FND_API.g_false
275 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
276
277 ,x_return_status OUT NOCOPY VARCHAR2
278 ,x_msg_count OUT NOCOPY NUMBER
279 ,x_msg_data OUT NOCOPY VARCHAR2
280
281 ,p_fund_rec IN fund_rec_type
282 ,p_mode IN VARCHAR2 := JTF_PLSQL_API.g_update
283 );
284
285
286 ---------------------------------------------------------------------
287 -- PROCEDURE
288 -- Update_Fund
289 --
290 -- PURPOSE
291 -- Update a fund.
292 --
293 -- PARAMETERS
294 -- p_fund_rec: the record with new items.
295 -- p_mode : determines what sort of validation is to be performed during update.
296 -- : The mode should always be 'UPDATE' except when updating the earned or committed amount
297 --
298 -- NOTES
299 -- 1. Raise exception if the object_version_number doesn't match.
300 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
301 -- that column won't be updated.
302
303 -- 27/10/2009 muthsubr Added p_resale_batch_id, p_use_fund_staging_tables
304 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype
305 -- for bug#8867381.
306 -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
307 -- for TPA Parallel Execution ER Bug#9614703.
308 ----------------------------------------------------------------------
309 PROCEDURE Update_Fund(
310 p_api_version IN NUMBER
311 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
312 ,p_commit IN VARCHAR2 := FND_API.g_false
313 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
314
315 ,x_return_status OUT NOCOPY VARCHAR2
316 ,x_msg_count OUT NOCOPY NUMBER
317 ,x_msg_data OUT NOCOPY VARCHAR2
318
319 ,p_fund_rec IN fund_rec_type
320 ,p_mode IN VARCHAR2 := JTF_PLSQL_API.g_update
321 ,p_resale_batch_id IN NUMBER DEFAULT NULL
322 ,p_batch_type IN VARCHAR2
323 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
324 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
325 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
326 );
327
328 ---------------------------------------------------------------------
329 -- PROCEDURE
330 -- Validate_Fund
331 --
332 -- PURPOSE
333 -- Validate a fund record.
334 --
335 -- PARAMETERS
336 -- p_fund_rec: the fund record to be validated
337 --
338 -- NOTES
339 -- 1. p_fund_rec should be the complete fund record. There
340 -- should not be any FND_API.g_miss_char/num/date in it.
341 ----------------------------------------------------------------------
342 PROCEDURE Validate_Fund(
343 p_api_version IN NUMBER
344 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
345 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
346
347 ,x_return_status OUT NOCOPY VARCHAR2
348 ,x_msg_count OUT NOCOPY NUMBER
349 ,x_msg_data OUT NOCOPY VARCHAR2
350
351 ,p_fund_rec IN fund_rec_type
352 );
353
354
355 ---------------------------------------------------------------------
356 -- PROCEDURE
357 -- Check_Fund_Items
358 --
359 -- PURPOSE
360 -- Perform the item level checking including unique keys,
361 -- required columns, foreign keys, domain constraints.
362 --
363 -- PARAMETERS
364 -- p_fund_rec: the record to be validated
365 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
366 -- p_mode: default NULL, Added parameter Bug 13529250
367 ---------------------------------------------------------------------
368 PROCEDURE Check_Fund_Items(
369 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ,p_fund_rec IN fund_rec_type
372 ,p_mode IN VARCHAR2 DEFAULT NULL
373 );
374
375
376
377 ---------------------------------------------------------------------
378 -- PROCEDURE
379 -- Check_Fund_Record
380 --
381 -- PURPOSE
382 -- Check the record level business rules.
383 --
384 -- PARAMETERS
385 -- p_fund_rec: the record to be validated; may contain attributes
386 -- as FND_API.g_miss_char/num/date
387 -- p_complete_rec: the complete record after all "g_miss" items
388 -- have been replaced by current database values
389 ---------------------------------------------------------------------
390 PROCEDURE Check_Fund_Record(
391 p_fund_rec IN fund_rec_type
392 ,p_complete_rec IN fund_rec_type := NULL
393 ,p_mode IN VARCHAR2 := 'INSERT'
394 ,x_return_status OUT NOCOPY VARCHAR2
395 );
396
397
398 ---------------------------------------------------------------------
399 -- PROCEDURE
400 -- Init_Fund_Rec
401 --
402 -- PURPOSE
403 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
404 ---------------------------------------------------------------------
405 PROCEDURE Init_Fund_Rec(
409
406 x_fund_rec OUT NOCOPY fund_rec_type
407 );
408
410 ---------------------------------------------------------------------
411 -- PROCEDURE
412 -- Complete_Fund_Rec
413 --
414 -- PURPOSE
415 -- For update_fund, some attributes may be passed in as
416 -- FND_API.g_miss_char/num/date if the user doesn't want to
417 -- update those attributes. This procedure will replace the
418 -- "g_miss" attributes with current database values.
419 --
420 -- PARAMETERS
421 -- p_fund_rec: the record which may contain attributes as
422 -- FND_API.g_miss_char/num/date
423 -- x_complete_rec: the complete record after all "g_miss" items
424 -- have been replaced by current database values
425 ---------------------------------------------------------------------
426 PROCEDURE Complete_Fund_Rec(
427 p_fund_rec IN fund_rec_type
428 ,x_complete_rec OUT NOCOPY fund_rec_type
429 );
430
431 -- ADDED FOR R2 Requirements to get default GL info--- by mpande //6th JULY-2000
432 ---------------------------------------------------------------------
433 -- PROCEDURE
434 -- COMPLETE_DEFAULT_GL_INFO
435 --
436 -- PURPOSE : A fund should always have a category . When creating a category the user can
437 -- give the GL info 1) ACCRUED_LIABILITY_ACCOUNT 2) DED_ADJUSTMENT_ACCOUNT
438 -- When the user is creating a fund the funds API should pickup
439 -- the default GL INFO if the user has not passed anything.
440 -- This API gets the defauls GL INFO.
441 -- PARAMETERS
442
443 ---------------------------------------------------------------------
444 PROCEDURE COMPLETE_DEFAULT_GL_INFO(
445 p_category_id IN NUMBER,
446 p_accrued_liability_account IN OUT NOCOPY NUMBER,
447 p_ded_adjustment_account IN OUT NOCOPY NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2
449 );
450
451 ---------------------------------------------------------------------
452 -- PROCEDURE
453 -- check_fund_inter_entity
454 --
455 -- PURPOSE
456 -- Check the inter-entity level business rules.
457 --
458 -- PARAMETERS
459 -- p_fund_rec: the record to be validated; may contain attributes
460 -- as FND_API.g_miss_char/num/date
461 -- p_complete_rec: the complete record after all "g_miss" items
462 -- have been replaced by current database values
463 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
464 ---------------------------------------------------------------------
465 PROCEDURE check_fund_inter_entity(
466 p_fund_rec IN fund_rec_type,
467 p_complete_rec IN fund_rec_type,
468 p_validation_mode IN VARCHAR2,
469 x_return_status OUT NOCOPY VARCHAR2
470 );
471
472
473 ---------------------------------------------------------------------
474 -- PROCEDURE
475 -- copy_fund
476 --
477 -- PURPOSE
478 -- copy fund. added by feliu.
479 --
480 -- PARAMETERS
481 -- p_source_object_id: Original object id,
482 -- p_attributes_table: AMS_CpyUtility_PVT.copy_attributes_table_type,
483 -- p_copy_columns_table: AMS_CpyUtility_PVT.copy_columns_table_type,
484 -- x_new_object_id: New object Id.
485 -- x_custom_setup_id: custom_setup_id.
486 ---------------------------------------------------------------------
487
488 PROCEDURE copy_fund (
489 p_api_version IN NUMBER,
490 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
491 p_commit IN VARCHAR2 := FND_API.G_FALSE,
492 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
493 x_return_status OUT NOCOPY VARCHAR2,
494 x_msg_count OUT NOCOPY NUMBER,
495 x_msg_data OUT NOCOPY VARCHAR2,
496 p_source_object_id IN NUMBER,
497 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
498 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
499 x_new_object_id OUT NOCOPY NUMBER,
500 x_custom_setup_id OUT NOCOPY NUMBER
501 );
502
503
504
505 ---------------------------------------------------------------------
506 -- PROCEDURE
507 --- update_rollup_amount
508 --
509 -- PURPOSE
510 -- Update rollup columns. added by feliu
511 --
512 -- PARAMETERS
513 -- p_fund_rec: the fund record.
514 ---------------------------------------------------------------------
515
516 PROCEDURE update_rollup_amount(
517 p_api_version IN NUMBER
518 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
519 ,p_commit IN VARCHAR2 := fnd_api.g_false
520 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
521 ,x_return_status OUT NOCOPY VARCHAR2
522 ,x_msg_count OUT NOCOPY NUMBER
523 ,x_msg_data OUT NOCOPY VARCHAR2
524 ,p_fund_rec IN fund_rec_type
525 );
526
527 ---------------------------------------------------------------------
528 -- PROCEDURE
529 --- update_funds_access
530 --
531 -- PURPOSE
532 -- Update parent funds access. added by feliu
533 --
534 -- PARAMETERS
535 -- p_fund_rec: the fund record.
536 -- p_mod: the mode for update access.
537 ---------------------------------------------------------------------
538
539 PROCEDURE update_funds_access(
540 p_api_version IN NUMBER
541 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
542 ,p_commit IN VARCHAR2 := fnd_api.g_false
543 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
544 ,x_return_status OUT NOCOPY VARCHAR2
545 ,x_msg_count OUT NOCOPY NUMBER
546 ,x_msg_data OUT NOCOPY VARCHAR2
547 ,p_fund_rec IN fund_rec_type
551 END OZF_Funds_PVT;
548 ,p_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
549 );
550
552
[Home] [Help]