1 PACKAGE OZF_Fund_Utilized_PVT AUTHID CURRENT_USER AS
2 /* $Header: ozfvfuts.pls 120.13 2011/12/23 13:08:24 nirprasa ship $ */
3
4 TYPE utilization_rec_type IS RECORD
5 (
6 utilization_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 ,utilization_type VARCHAR2(30)
18 ,fund_id NUMBER
19 ,plan_type VARCHAR2(30)
20 ,plan_id NUMBER
21 ,component_type VARCHAR2(30)
22 ,component_id NUMBER
23 ,object_type VARCHAR2(30)
24 ,object_id NUMBER
25 ,order_id NUMBER
26 ,invoice_id NUMBER
27 ,amount NUMBER
28 ,acctd_amount NUMBER
29 ,currency_code VARCHAR2(3)
30 ,exchange_rate_type VARCHAR2(30)
31 ,exchange_rate_date DATE
32 ,exchange_rate NUMBER
33 ,adjustment_type VARCHAR2(30)
34 ,adjustment_date DATE
35 ,object_version_number NUMBER
36 ,attribute_category VARCHAR2(30)
37 ,attribute1 VARCHAR2(150)
38 ,attribute2 VARCHAR2(150)
39 ,attribute3 VARCHAR2(150)
40 ,attribute4 VARCHAR2(150)
41 ,attribute5 VARCHAR2(150)
42 ,attribute6 VARCHAR2(150)
43 ,attribute7 VARCHAR2(150)
44 ,attribute8 VARCHAR2(150)
45 ,attribute9 VARCHAR2(150)
46 ,attribute10 VARCHAR2(150)
47 ,attribute11 VARCHAR2(150)
48 ,attribute12 VARCHAR2(150)
49 ,attribute13 VARCHAR2(150)
50 ,attribute14 VARCHAR2(150)
51 ,attribute15 VARCHAR2(150)
52 ,org_id NUMBER
53 ,adjustment_desc VARCHAR2(2000)
54 ,language VARCHAR2(4)
55 ,source_lang VARCHAR2(4)
56 ,camp_schedule_id NUMBER
57 ,adjustment_type_id NUMBER
58 ,gl_date DATE
59 ,product_level_type VARCHAR2(30)
60 ,product_id NUMBER
61 ,ams_activity_budget_id NUMBER
62 ,amount_remaining NUMBER
63 ,acctd_amount_remaining NUMBER
64 ,cust_account_id NUMBER
65 ,price_adjustment_id NUMBER
66 ,plan_curr_amount NUMBER
67 ,plan_curr_amount_remaining NUMBER
68 ,scan_unit NUMBER
69 ,scan_unit_remaining NUMBER
70 ,activity_product_id NUMBER
71 ,scan_data_id NUMBER -- this colums is not in the table but required for scan data offers adj
72 ,volume_offer_tiers_id NUMBER
73 ,gl_posted_flag VARCHAR2(1) -- yzhao: 03/20/2003 added
74 -- 11/04/2003 yzhao 11.5.10: added
75 ,billto_cust_account_id NUMBER
76 ,reference_type VARCHAR2(30)
77 ,reference_id NUMBER
78 /*fix for bug 4778995
79 ,month_id NUMBER
80 ,quarter_id NUMBER
81 ,year_id NUMBER
82 */
83 -- 01/02/2004 kdass added for 11.5.10
84 ,order_line_id NUMBER
85 -- 03/01/2003 feliu added for 11.5.10
86 ,orig_utilization_id NUMBER
87 -- rimehrot added for R12
88 ,bill_to_site_use_id NUMBER
89 ,ship_to_site_use_id NUMBER
90 -- yzhao R12
91 ,univ_curr_amount NUMBER
92 ,univ_curr_amount_remaining NUMBER
93 -- kdass R12
94 ,gl_account_credit NUMBER
95 ,gl_account_debit NUMBER
96 ,site_use_id NUMBER -- fix for bug 7512202
97 --nirprasa ER 8399134
98 ,fund_request_currency_code VARCHAR2(15)
99 ,fund_request_amount NUMBER
100 ,fund_request_amount_remaining NUMBER
101 ,plan_currency_code VARCHAR2(15)
102 --kdass - bug 9470625
103 ,cost_price NUMBER
104 ,cost_price_currency_code VARCHAR2(15)
105 ,discount_type VARCHAR2(30)
106 ,discount_amount NUMBER
107 ,discount_amount_currency_code VARCHAR2(15)
108 ,year_id NUMBER --added for bug 11793070
109 ,list_line_id NUMBER
110 );
111
112 ---------------------------------------------------------------------
113 -- PROCEDURE
114 -- Create_Utilization
115 --
116 -- PURPOSE
117 -- Create a new fund utilization.
118 --
119 -- PARAMETERS
120 -- p_utilization_rec: the new record to be inserted
121 -- x_utilization_id: return the utilization_id of the new utilization record.
122 --
123 -- NOTES
124 -- 1. object_version_number will be set to 1.
125 -- 2. If utilization_id is passed in, the uniqueness will be checked.
126 -- Raise exception in case of duplicates.
127 -- 3. If utilization_id is not passed in, generate a unique one from
128 -- the sequence.
129 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
130 -- Raise exception for invalid flag.
131 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
132 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
133 ---------------------------------------------------------------------
134 PROCEDURE Create_Utilization(
135 p_api_version IN NUMBER
136 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
137 ,p_commit IN VARCHAR2 := FND_API.g_false
138 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
139
140 ,x_return_status OUT NOCOPY VARCHAR2
141 ,x_msg_count OUT NOCOPY NUMBER
142 ,x_msg_data OUT NOCOPY VARCHAR2
143 ,p_create_gl_entry IN VARCHAR2 := FND_API.g_false
144 ,p_utilization_rec IN utilization_rec_type
145 ,x_utilization_id OUT NOCOPY NUMBER
146 );
147
148
149 ---------------------------------------------------------------------
150 -- PROCEDURE
151 -- Create_Utilization
152 --
153 -- PURPOSE
154 -- Over loaded a new fund utilization for bug#8867381.
155 --
156 -- PARAMETERS
157 -- p_utilization_rec: the new record to be inserted
158 -- x_utilization_id: return the utilization_id of the new utilization record.
159 --
160 -- NOTES
161 -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
162 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype
163 -- for bug#8867381.
164 -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
165 -- for TPA Parallel Execution ER Bug#9614703.
166 ---------------------------------------------------------------------
167 PROCEDURE Create_Utilization(
168 p_api_version IN NUMBER
169 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
170 ,p_commit IN VARCHAR2 := FND_API.g_false
171 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
172
173 ,x_return_status OUT NOCOPY VARCHAR2
174 ,x_msg_count OUT NOCOPY NUMBER
175 ,x_msg_data OUT NOCOPY VARCHAR2
176 ,p_create_gl_entry IN VARCHAR2 := FND_API.g_false
177 ,p_utilization_rec IN utilization_rec_type
178 ,x_utilization_id OUT NOCOPY NUMBER
179 ,p_batch_type IN VARCHAR2
180 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
181 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
182 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
183 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
184 );
185
186 --------------------------------------------------------------------
187 -- PROCEDURE
188 -- Delete_Utilization
189 --
190 -- PURPOSE
191 -- Delete a fund utilization.
192 --
193 -- PARAMETERS
194 -- p_utilization_id: the utilization_id
195 -- p_object_version: the object_version_number
196 --
197 -- NOTES
198 -- 1. Raise exception if the object_version_number doesn't match.
199 --------------------------------------------------------------------
200 PROCEDURE Delete_Utilization(
201 p_api_version IN NUMBER
202 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
203 ,p_commit IN VARCHAR2 := FND_API.g_false
204
205 ,x_return_status OUT NOCOPY VARCHAR2
206 ,x_msg_count OUT NOCOPY NUMBER
207 ,x_msg_data OUT NOCOPY VARCHAR2
208
209 ,p_utilization_id IN NUMBER
210 ,p_object_version IN NUMBER
211 );
212
213
214 -------------------------------------------------------------------
215 -- PROCEDURE
216 -- Lock_Utilization
217 --
218 -- PURPOSE
219 -- Lock a fund uilization.
220 --
221 -- PARAMETERS
222 -- p_utilization_id: the utilization_id
223 -- p_object_version: the object_version_number
224 --
225 -- NOTES
226 -- 1. Raise exception if the object_version_number doesn't match.
227 --------------------------------------------------------------------
228 PROCEDURE Lock_Utilization(
229 p_api_version IN NUMBER
230 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
231
232 ,x_return_status OUT NOCOPY VARCHAR2
233 ,x_msg_count OUT NOCOPY NUMBER
237 ,p_object_version IN NUMBER
234 ,x_msg_data OUT NOCOPY VARCHAR2
235
236 ,p_utilization_id IN NUMBER
238 );
239
240
241 ---------------------------------------------------------------------
242 -- PROCEDURE
243 -- Update_Utilization
244 --
245 -- PURPOSE
246 -- Update a fund utilization.
247 --
248 -- PARAMETERS
249 -- p_utilization_rec: the record with new items.
250 -- p_mode : determines what sort of validation is to be performed during update.
251 --
252 -- NOTES
253 -- 1. Raise exception if the object_version_number doesn't match.
254 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
255 -- that column won't be updated.
256 ----------------------------------------------------------------------
257 PROCEDURE Update_Utilization(
258 p_api_version IN NUMBER
259 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
260 ,p_commit IN VARCHAR2 := FND_API.g_false
261 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
262
263 ,x_return_status OUT NOCOPY VARCHAR2
264 ,x_msg_count OUT NOCOPY NUMBER
265 ,x_msg_data OUT NOCOPY VARCHAR2
266
267 ,p_utilization_rec IN utilization_rec_type
268 ,p_mode IN VARCHAR2 := 'UPDATE'
269 );
270
271
272 ---------------------------------------------------------------------
273 -- PROCEDURE
274 -- Validate_Utilization
275 --
276 -- PURPOSE
277 -- Validate a fund utilization record.
278 --
279 -- PARAMETERS
280 -- p_utilization_rec: the fund utilization record to be validated
281 --
282 -- NOTES
283 -- 1. p_utilization_rec should be the complete fund record. There
284 -- should not be any FND_API.g_miss_char/num/date in it.
285 ----------------------------------------------------------------------
286 PROCEDURE Validate_Utilization(
287 p_api_version IN NUMBER
288 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
289 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
290
291 ,x_return_status OUT NOCOPY VARCHAR2
292 ,x_msg_count OUT NOCOPY NUMBER
293 ,x_msg_data OUT NOCOPY VARCHAR2
294
295 ,p_utilization_rec IN utilization_rec_type
296 );
297
298
299 ---------------------------------------------------------------------
300 -- PROCEDURE
301 -- Check_Utilization_Items
302 --
303 -- PURPOSE
304 -- Perform the item level checking including unique keys,
305 -- required columns, foreign keys, domain constraints.
306 --
307 -- PARAMETERS
308 -- p_utilization_rec: the record to be validated
309 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
310 ---------------------------------------------------------------------
311 PROCEDURE Check_Utilization_Items(
312 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
313 ,x_return_status OUT NOCOPY VARCHAR2
314 ,p_utilization_rec IN utilization_rec_type
315 );
316
317
318 ---------------------------------------------------------------------
319 -- PROCEDURE
320 -- Check_Utilization_Record
321 --
322 -- PURPOSE
323 -- Check the record level business rules.
324 --
325 -- PARAMETERS
326 -- p_utilization_rec: the record to be validated; may contain attributes
327 -- as FND_API.g_miss_char/num/date
328 -- p_complete_rec: the complete record after all "g_miss" items
329 -- have been replaced by current database values
330 ---------------------------------------------------------------------
331 PROCEDURE Check_Utilization_Record(
332 p_utilization_rec IN utilization_rec_type
333 ,p_complete_rec IN utilization_rec_type := NULL
334 ,p_mode IN VARCHAR2 := 'INSERT'
335 ,x_return_status OUT NOCOPY VARCHAR2
336 );
337
338
339 ---------------------------------------------------------------------
340 -- PROCEDURE
341 -- Init_Utilization_Rec
342 --
343 -- PURPOSE
347 x_utilization_rec OUT NOCOPY utilization_rec_type
344 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
345 ---------------------------------------------------------------------
346 PROCEDURE Init_Utilization_Rec(
348 );
349
350
351 ---------------------------------------------------------------------
352 -- PROCEDURE
353 -- Complete_Utilization_Rec
354 --
355 -- PURPOSE
356 -- For update_fund, some attributes may be passed in as
357 -- FND_API.g_miss_char/num/date if the user doesn't want to
358 -- update those attributes. This procedure will replace the
359 -- "g_miss" attributes with current database values.
360 --
361 -- PARAMETERS
362 -- p_utilization_rec: the record which may contain attributes as
363 -- FND_API.g_miss_char/num/date
364 -- x_complete_rec: the complete record after all "g_miss" items
365 -- have been replaced by current database values
366 ---------------------------------------------------------------------
367 PROCEDURE Complete_Utilization_Rec(
368 p_utilization_rec IN utilization_rec_type
369 ,x_complete_rec OUT NOCOPY utilization_rec_type
370 );
371
372
373 ---------------------------------------------------------------------
374 -- PROCEDURE
375 -- create_act_utilization
376 --
377 -- PURPOSE
378 -- For create act budgets and utilization record.
379 -- Called by manual fund adjustment.
380 --
381 -- PARAMETERS
382 -- p_act_util_rec: the act budget record which contain information
383 --- create act bugets record.
384 -- p_act_util_rec: the act utilization record which contain information
385 -- for utilization.
386 -- NOTES
387 -- 1. created by feliu on 02/25/2002.
388 ---------------------------------------------------------------------
389
390 PROCEDURE create_act_utilization(
391 p_api_version IN NUMBER
392 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
393 ,p_commit IN VARCHAR2 := fnd_api.g_false
394 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
395 ,x_return_status OUT NOCOPY VARCHAR2
396 ,x_msg_count OUT NOCOPY NUMBER
397 ,x_msg_data OUT NOCOPY VARCHAR2
398 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
399 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
400 ,x_act_budget_id OUT NOCOPY NUMBER
401 );
402
403 --kdass - added for Bug 8726683
404 PROCEDURE create_act_utilization(
405 p_api_version IN NUMBER
406 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
407 ,p_commit IN VARCHAR2 := fnd_api.g_false
408 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
409 ,x_return_status OUT NOCOPY VARCHAR2
410 ,x_msg_count OUT NOCOPY NUMBER
411 ,x_msg_data OUT NOCOPY VARCHAR2
412 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
413 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
414 ,x_act_budget_id OUT NOCOPY NUMBER
415 ,x_utilization_id OUT NOCOPY NUMBER
416 );
417
418 END OZF_Fund_Utilized_PVT;
[Home] [Help]