1 PACKAGE OZF_Funds_PVT AS
2 /* $Header: ozfvfuns.pls 120.2 2005/08/16 21:37:20 appldev 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
136 ---------------------------------------------------------------------
137 -- PROCEDURE
138 -- Create_Fund
139 --
140 -- PURPOSE
141 -- Create a new fund.
142 --
143 -- PARAMETERS
144 -- p_fund_rec: the new record to be inserted
145 -- x_fund_id: return the fund_id of the new fund
146 --
147 -- NOTES
148 -- 1. object_version_number will be set to 1.
149 -- 2. If fund_id is passed in, the uniqueness will be checked.
150 -- Raise exception in case of duplicates.
151 -- 3. If fund_id is not passed in, generate a unique one from
152 -- the sequence.
153 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
154 -- Raise exception for invalid flag.
155 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
156 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
157 ---------------------------------------------------------------------
158 PROCEDURE Create_Fund(
159 p_api_version IN NUMBER
160 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
161 ,p_commit IN VARCHAR2 := FND_API.g_false
162 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
163
164 ,x_return_status OUT NOCOPY VARCHAR2
165 ,x_msg_count OUT NOCOPY NUMBER
166 ,x_msg_data OUT NOCOPY VARCHAR2
167
168 ,p_fund_rec IN fund_rec_type
169 ,x_fund_id OUT NOCOPY NUMBER
170 );
171
172
173 --------------------------------------------------------------------
174 -- PROCEDURE
175 -- Delete_Fund
176 --
177 -- PURPOSE
178 -- Delete a fund.
179 --
180 -- PARAMETERS
181 -- p_fund_id: the fund_id
182 -- p_object_version: the object_version_number
183 --
184 -- NOTES
185 -- 1. Raise exception if the object_version_number doesn't match.
186 --------------------------------------------------------------------
187 PROCEDURE Delete_Fund(
188 p_api_version IN NUMBER
189 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
190 ,p_commit IN VARCHAR2 := FND_API.g_false
191
192 ,x_return_status OUT NOCOPY VARCHAR2
193 ,x_msg_count OUT NOCOPY NUMBER
194 ,x_msg_data OUT NOCOPY VARCHAR2
195
196 ,p_fund_id IN NUMBER
197 ,p_object_version IN NUMBER
198 );
199
200
201 -------------------------------------------------------------------
202 -- PROCEDURE
203 -- Lock_Fund
204 --
205 -- PURPOSE
206 -- Lock a fund.
207 --
208 -- PARAMETERS
209 -- p_fund_id: the fund_id
210 -- p_object_version: the object_version_number
211 --
212 -- NOTES
213 -- 1. Raise exception if the object_version_number doesn't match.
214 --------------------------------------------------------------------
215 PROCEDURE Lock_Fund(
216 p_api_version IN NUMBER
217 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
218
219 ,x_return_status OUT NOCOPY VARCHAR2
220 ,x_msg_count OUT NOCOPY NUMBER
221 ,x_msg_data OUT NOCOPY VARCHAR2
222
223 ,p_fund_id IN NUMBER
224 ,p_object_version IN NUMBER
225 );
226
227
228 ---------------------------------------------------------------------
229 -- PROCEDURE
230 -- Update_Fund
231 --
232 -- PURPOSE
233 -- Update a fund.
234 --
235 -- PARAMETERS
236 -- p_fund_rec: the record with new items.
237 -- p_mode : determines what sort of validation is to be performed during update.
238 -- : The mode should always be 'UPDATE' except when updating the earned or committed amount
239 --
240 -- NOTES
241 -- 1. Raise exception if the object_version_number doesn't match.
242 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
243 -- that column won't be updated.
244 ----------------------------------------------------------------------
245 PROCEDURE Update_Fund(
246 p_api_version IN NUMBER
247 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
248 ,p_commit IN VARCHAR2 := FND_API.g_false
249 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
250
251 ,x_return_status OUT NOCOPY VARCHAR2
252 ,x_msg_count OUT NOCOPY NUMBER
253 ,x_msg_data OUT NOCOPY VARCHAR2
254
255 ,p_fund_rec IN fund_rec_type
256 ,p_mode IN VARCHAR2 := JTF_PLSQL_API.g_update
257 );
258
259
260 ---------------------------------------------------------------------
261 -- PROCEDURE
262 -- Validate_Fund
263 --
264 -- PURPOSE
265 -- Validate a fund record.
266 --
267 -- PARAMETERS
268 -- p_fund_rec: the fund record to be validated
269 --
270 -- NOTES
271 -- 1. p_fund_rec should be the complete fund record. There
272 -- should not be any FND_API.g_miss_char/num/date in it.
273 ----------------------------------------------------------------------
274 PROCEDURE Validate_Fund(
275 p_api_version IN NUMBER
276 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
277 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
278
279 ,x_return_status OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282
283 ,p_fund_rec IN fund_rec_type
284 );
285
286
287 ---------------------------------------------------------------------
288 -- PROCEDURE
289 -- Check_Fund_Items
290 --
291 -- PURPOSE
292 -- Perform the item level checking including unique keys,
293 -- required columns, foreign keys, domain constraints.
294 --
295 -- PARAMETERS
296 -- p_fund_rec: the record to be validated
297 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
298 ---------------------------------------------------------------------
299 PROCEDURE Check_Fund_Items(
300 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
301 ,x_return_status OUT NOCOPY VARCHAR2
302 ,p_fund_rec IN fund_rec_type
303 );
304
305
306
307 ---------------------------------------------------------------------
308 -- PROCEDURE
309 -- Check_Fund_Record
310 --
311 -- PURPOSE
312 -- Check the record level business rules.
313 --
314 -- PARAMETERS
315 -- p_fund_rec: the record to be validated; may contain attributes
316 -- as FND_API.g_miss_char/num/date
317 -- p_complete_rec: the complete record after all "g_miss" items
318 -- have been replaced by current database values
319 ---------------------------------------------------------------------
320 PROCEDURE Check_Fund_Record(
321 p_fund_rec IN fund_rec_type
322 ,p_complete_rec IN fund_rec_type := NULL
323 ,p_mode IN VARCHAR2 := 'INSERT'
324 ,x_return_status OUT NOCOPY VARCHAR2
325 );
326
327
328 ---------------------------------------------------------------------
329 -- PROCEDURE
330 -- Init_Fund_Rec
331 --
332 -- PURPOSE
333 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
334 ---------------------------------------------------------------------
335 PROCEDURE Init_Fund_Rec(
336 x_fund_rec OUT NOCOPY fund_rec_type
337 );
338
339
340 ---------------------------------------------------------------------
341 -- PROCEDURE
342 -- Complete_Fund_Rec
343 --
344 -- PURPOSE
345 -- For update_fund, some attributes may be passed in as
346 -- FND_API.g_miss_char/num/date if the user doesn't want to
347 -- update those attributes. This procedure will replace the
348 -- "g_miss" attributes with current database values.
349 --
350 -- PARAMETERS
351 -- p_fund_rec: the record which may contain attributes as
352 -- FND_API.g_miss_char/num/date
353 -- x_complete_rec: the complete record after all "g_miss" items
354 -- have been replaced by current database values
355 ---------------------------------------------------------------------
356 PROCEDURE Complete_Fund_Rec(
357 p_fund_rec IN fund_rec_type
358 ,x_complete_rec OUT NOCOPY fund_rec_type
359 );
360
361 -- ADDED FOR R2 Requirements to get default GL info--- by mpande //6th JULY-2000
362 ---------------------------------------------------------------------
363 -- PROCEDURE
364 -- COMPLETE_DEFAULT_GL_INFO
365 --
369 -- the default GL INFO if the user has not passed anything.
366 -- PURPOSE : A fund should always have a category . When creating a category the user can
367 -- give the GL info 1) ACCRUED_LIABILITY_ACCOUNT 2) DED_ADJUSTMENT_ACCOUNT
368 -- When the user is creating a fund the funds API should pickup
370 -- This API gets the defauls GL INFO.
371 -- PARAMETERS
372
373 ---------------------------------------------------------------------
374 PROCEDURE COMPLETE_DEFAULT_GL_INFO(
375 p_category_id IN NUMBER,
376 p_accrued_liability_account IN OUT NOCOPY NUMBER,
377 p_ded_adjustment_account IN OUT NOCOPY NUMBER,
378 x_return_status OUT NOCOPY VARCHAR2
379 );
380
381 ---------------------------------------------------------------------
382 -- PROCEDURE
383 -- check_fund_inter_entity
384 --
385 -- PURPOSE
386 -- Check the inter-entity level business rules.
387 --
388 -- PARAMETERS
389 -- p_fund_rec: the record to be validated; may contain attributes
390 -- as FND_API.g_miss_char/num/date
391 -- p_complete_rec: the complete record after all "g_miss" items
392 -- have been replaced by current database values
393 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
394 ---------------------------------------------------------------------
395 PROCEDURE check_fund_inter_entity(
396 p_fund_rec IN fund_rec_type,
397 p_complete_rec IN fund_rec_type,
398 p_validation_mode IN VARCHAR2,
399 x_return_status OUT NOCOPY VARCHAR2
400 );
401
402
403 ---------------------------------------------------------------------
404 -- PROCEDURE
405 -- copy_fund
406 --
407 -- PURPOSE
408 -- copy fund. added by feliu.
409 --
410 -- PARAMETERS
411 -- p_source_object_id: Original object id,
412 -- p_attributes_table: AMS_CpyUtility_PVT.copy_attributes_table_type,
413 -- p_copy_columns_table: AMS_CpyUtility_PVT.copy_columns_table_type,
414 -- x_new_object_id: New object Id.
415 -- x_custom_setup_id: custom_setup_id.
416 ---------------------------------------------------------------------
417
418 PROCEDURE copy_fund (
419 p_api_version IN NUMBER,
420 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
421 p_commit IN VARCHAR2 := FND_API.G_FALSE,
422 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
423 x_return_status OUT NOCOPY VARCHAR2,
424 x_msg_count OUT NOCOPY NUMBER,
425 x_msg_data OUT NOCOPY VARCHAR2,
426 p_source_object_id IN NUMBER,
427 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
428 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
429 x_new_object_id OUT NOCOPY NUMBER,
430 x_custom_setup_id OUT NOCOPY NUMBER
431 );
432
433
434
435 ---------------------------------------------------------------------
436 -- PROCEDURE
437 --- update_rollup_amount
438 --
439 -- PURPOSE
440 -- Update rollup columns. added by feliu
441 --
442 -- PARAMETERS
443 -- p_fund_rec: the fund record.
444 ---------------------------------------------------------------------
445
446 PROCEDURE update_rollup_amount(
447 p_api_version IN NUMBER
448 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
449 ,p_commit IN VARCHAR2 := fnd_api.g_false
450 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
451 ,x_return_status OUT NOCOPY VARCHAR2
452 ,x_msg_count OUT NOCOPY NUMBER
453 ,x_msg_data OUT NOCOPY VARCHAR2
454 ,p_fund_rec IN fund_rec_type
455 );
456
457 ---------------------------------------------------------------------
458 -- PROCEDURE
459 --- update_funds_access
460 --
461 -- PURPOSE
462 -- Update parent funds access. added by feliu
463 --
464 -- PARAMETERS
465 -- p_fund_rec: the fund record.
466 -- p_mod: the mode for update access.
467 ---------------------------------------------------------------------
468
469 PROCEDURE update_funds_access(
470 p_api_version IN NUMBER
471 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
472 ,p_commit IN VARCHAR2 := fnd_api.g_false
473 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
474 ,x_return_status OUT NOCOPY VARCHAR2
475 ,x_msg_count OUT NOCOPY NUMBER
476 ,x_msg_data OUT NOCOPY VARCHAR2
477 ,p_fund_rec IN fund_rec_type
478 ,p_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
479 );
480
481 END OZF_Funds_PVT;
482