1 PACKAGE ozf_fund_adjustment_pvt AUTHID CURRENT_USER AS
2 /*$Header: ozfvadjs.pls 120.5 2011/01/12 07:16:11 muthsubr ship $*/
3
4 /*****************************************************************************************/
5 -- Start of Comments
6 -- NAME
7 -- Create Fund Utilization
8 -- PURPOSE
9 -- Create utilizations for the utlized amount of that activity
10 -- called only from ozf_Act_budgets API for utlized amount creation
11 -- HISTORY
12 -- 02/23/2001 mpande CREATED
13
14 PROCEDURE create_fund_utilization(
15 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
16 ,x_return_status OUT NOCOPY VARCHAR2
17 ,x_msg_count OUT NOCOPY NUMBER
18 ,x_msg_data OUT NOCOPY VARCHAR2
19 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.G_MISS_ACT_UTIL_REC
20 );
21
22 /*****************************************************************************************/
23 -- NAME
24 -- Create Fund Utilization
25 -- PURPOSE
26 -- Create utilizations for the utlized amount of that activity
27 -- called only from ozf_Act_budgets API for utlized amount creation
28 -- HISTORY
29 -- 02/23/2001 mpande CREATED
30 -- 06/21/2004 yzhao UPDATED added x_utilized_amount to return actual utilized amount
31
32 PROCEDURE create_fund_utilization(
33 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2
37 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.G_MISS_ACT_UTIL_REC
38 ,x_utilized_amount OUT NOCOPY NUMBER
39 );
40
41
42 /*****************************************************************************************/
43 -- NAME
44 -- Create Fund Utilization
45 -- PURPOSE
46 -- Create utilizations for the utlized amount of that activity
47 -- called only from ozf_Act_budgets API for utlized amount creation
48 -- HISTORY
49 -- 02/23/2001 mpande CREATED
50 -- 06/21/2004 yzhao UPDATED added x_utilized_amount to return actual utilized amount
51 -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
52 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype for bug#8867381
53 -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
54 -- for TPA Parallel Execution ER Bug#9614703.
55
56 PROCEDURE create_fund_utilization(
57 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
58 ,x_return_status OUT NOCOPY VARCHAR2
59 ,x_msg_count OUT NOCOPY NUMBER
60 ,x_msg_data OUT NOCOPY VARCHAR2
61 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.G_MISS_ACT_UTIL_REC
62 ,x_utilized_amount OUT NOCOPY NUMBER
63 ,p_batch_type IN VARCHAR2
64 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
65 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
66 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
67 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
68 );
69
70 --nirprasa - added for Bug 9383565
71 PROCEDURE create_fund_utilization (
72 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
73 ,x_return_status OUT NOCOPY VARCHAR2
74 ,x_msg_count OUT NOCOPY NUMBER
75 ,x_msg_data OUT NOCOPY VARCHAR2
76 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.g_miss_act_util_rec
77 ,x_utilized_amount OUT NOCOPY NUMBER
78 ,p_batch_type IN VARCHAR2
79 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
80 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
81 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
82 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
83 ,x_utilization_id OUT NOCOPY NUMBER
84 );
85
86 PROCEDURE create_fund_utilization(
87 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
88 ,x_return_status OUT NOCOPY VARCHAR2
89 ,x_msg_count OUT NOCOPY NUMBER
90 ,x_msg_data OUT NOCOPY VARCHAR2
91 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.G_MISS_ACT_UTIL_REC
92 ,x_utilized_amount OUT NOCOPY NUMBER
93 ,x_utilization_id OUT NOCOPY NUMBER
94 );
95
96
97 /* =========================================================
98 --rec_type to hold the amount
99 --This is a private rec type to be used by this API only
100 ============================================================*/
101 TYPE cost_rec_type IS RECORD(
102 cost_id NUMBER,
103 cost_amount NUMBER, -- amount in object currency
104 cost_desc VARCHAR2(2000),
105 cost_curr VARCHAR2(30) -- now only supports the object_currency
106 );
107
108 /* =========================================================
109 --tbl_type to hold the amount
110 --This is a private rec type to be used by this API only
111 ============================================================*/
112
113 TYPE cost_tbl_type IS TABLE OF cost_rec_type
114 INDEX BY BINARY_INTEGER;
115
116 /*****************************************************************************************/
117 -- Start of Comments
118 -- NAME
119 -- Reconcile_budget_line
120 -- PURPOSE
121 -- This API is called from the java layer from the reconcile button on budget_sourcing screen
122 -- It releases all th ebudget that was requested from a fund to the respective fund by creating transfer records
123 -- and negative committment.
124 -- HISTORY
125 -- 04/30/2001 mpande CREATED
126
127 PROCEDURE create_budget_amt_utilized(
128 p_budget_used_by_id IN NUMBER
129 ,p_budget_used_by_type IN VARCHAR2
130 ,p_currency IN VARCHAR2
131 ,p_cost_tbl IN cost_tbl_type
132 ,p_api_version IN NUMBER
133 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
134 ,p_commit IN VARCHAR2 := fnd_api.g_false
135 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
136 ,x_return_status OUT NOCOPY VARCHAR2
137 ,x_msg_count OUT NOCOPY NUMBER
138 ,x_msg_data OUT NOCOPY VARCHAR2);
139
140 ---------------------------------------------------------------------
141 -- PROCEDURE
142 -- Convert_Currency
143 --
144 -- PURPOSE
145 -- This API will be used to convert currency for checkbook.
146 -- PARAMETERS
147 -- p_from_currency IN VARCHAR2 From currency
148 -- p_to_currency IN VARCHAR@ To currency
149 -- p_from_amount IN NUMBER From amount
150 -- NOTES
151
152 -- HISTORY
153 -- 06/08/2001 feliu Create.
154 ----------------------------------------------------------------------
155 FUNCTION Convert_Currency (
156 p_from_currency IN VARCHAR2,
157 p_to_currency IN VARCHAR2,
158 p_from_amount IN NUMBER,
159 p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR --Added for bug 7030415
160 )RETURN NUMBER;
161
162 ---------------------------------------------------------------------
163 -- PROCEDURE
164 -- get_exchange_rate
165 -- PURPOSE
166 -- Get currency exchange rate. called by BudgetOverVO.java.
167 -- PARAMETERS
168 -- p_from_currency IN VARCHAR2,
169 -- p_to_currency IN VARCHAR2,
170 -- p_conversion_date IN DATE ,
171 -- p_conversion_type IN VARCHAR2,
172 -- p_max_roll_days IN NUMBER,
173 -- x_denominator OUT NUMBER,
174 -- x_numerator OUT NUMBER,
175 -- x_rate OUT NUMBER,
176 -- x_return_status OUT VARCHAR2
177
178 -- HISTORY
179 -- 02/05/2002 feliu CREATED
180 ----------------------------------------------------------------------
181
182 PROCEDURE get_exchange_rate (
183 p_from_currency IN VARCHAR2,
184 p_to_currency IN VARCHAR2,
185 p_conversion_date IN DATE ,
186 p_conversion_type IN VARCHAR2,
187 p_max_roll_days IN NUMBER,
188 x_denominator OUT NOCOPY NUMBER,
189 x_numerator OUT NOCOPY NUMBER,
190 x_rate OUT NOCOPY NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2);
192
193 ---------------------------------------------------------------------
194 -- PROCEDURE
195 -- process_act_budgets
196 --
197 -- PURPOSE
198 --
199 -- PARAMETERS
200 -- p_api_version
201 -- ,x_return_status
202 -- ,x_msg_count
203 -- ,x_msg_data
204 -- ,p_act_budgets_rec
205 -- ,x_act_budget_id
206 -- NOTES
207 -- HISTORY
208 -- 4/18/2002 Mumu Pande Create.
209 ----------------------------------------------------------------------
210 PROCEDURE process_act_budgets (
211 x_return_status OUT NOCOPY VARCHAR2,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
215 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
216 x_act_budget_id OUT NOCOPY NUMBER
217 );
218
219
220 ---------------------------------------------------------------------
221 -- PROCEDURE
222 -- process_act_budgets
223 --
224 -- PURPOSE
225 --
226 -- PARAMETERS
227 -- p_api_version
228 -- ,x_return_status
229 -- ,x_msg_count
230 -- ,x_msg_data
231 -- ,p_act_budgets_rec
232 -- ,x_act_budget_id
233 -- x_utilized_amount : actual utilized amount when success
234 -- NOTES
235 -- HISTORY
236 -- 6/21/2004 Ying Zhao Create.
237 ----------------------------------------------------------------------
238 PROCEDURE process_act_budgets (
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_msg_count OUT NOCOPY NUMBER,
241 x_msg_data OUT NOCOPY VARCHAR2,
242 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
243 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
244 x_act_budget_id OUT NOCOPY NUMBER,
245 x_utilized_amount OUT NOCOPY NUMBER
246 );
247
248
249 ---------------------------------------------------------------------
250 -- PROCEDURE
251 -- process_act_budgets
252 --
253 -- PURPOSE
254 --
255 -- PARAMETERS
256 -- p_api_version
257 -- ,x_return_status
258 -- ,x_msg_count
259 -- ,x_msg_data
260 -- ,p_act_budgets_rec
261 -- ,x_act_budget_id
262 -- x_utilized_amount : actual utilized amount when success
263 -- p_use_fund_staging_tables
264 -- px_ozf_act_budgets_tbl
265 -- px_ozf_funds_old_rectype
266 -- px_ozf_funds_new_tbl ()
267 -- NOTES
268 -- HISTORY
269 -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
270 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype for bug#8867381
271 -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
272 -- for TPA Parallel Execution ER Bug#9614703.
276 x_msg_count OUT NOCOPY NUMBER,
273 ----------------------------------------------------------------------
274 PROCEDURE process_act_budgets (
275 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_data OUT NOCOPY VARCHAR2,
278 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
279 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
280 x_act_budget_id OUT NOCOPY NUMBER,
281 x_utilized_amount OUT NOCOPY NUMBER,
282 p_batch_type IN VARCHAR2,
283 p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F',
284 px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
285 px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype,
286 px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
287 );
288
289 PROCEDURE process_act_budgets (
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2,
293 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
294 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
295 x_act_budget_id OUT NOCOPY NUMBER,
296 x_utilized_amount OUT NOCOPY NUMBER,
297 p_batch_type IN VARCHAR2,
298 p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F',
299 px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
300 px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype,
301 px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
302 x_utilization_id OUT NOCOPY NUMBER
303 );
304
305 --nirprasa - added for Bug 9383565
306 PROCEDURE process_act_budgets (
307 x_return_status OUT NOCOPY VARCHAR2,
308 x_msg_count OUT NOCOPY NUMBER,
309 x_msg_data OUT NOCOPY VARCHAR2,
310 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
311 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
312 x_act_budget_id OUT NOCOPY NUMBER,
313 x_utilized_amount OUT NOCOPY NUMBER,
314 x_utilization_id OUT NOCOPY NUMBER
315 );
316 ---------------------------------------------------------------------
317 -- PROCEDURE
318 -- post_utilized_budget
319 --
320 -- PURPOSE
321 -- This procedure is called by updating offer API when changing offer status to "ACTIVE'
322 -- and by post_utilized_budget concurrent program for scan data offer and lump sum offer.
323 -- It is used to create utilized records when offer start date reaches.
324
325 -- PARAMETERS
326 -- p_offer_id
327 -- p_offer_type
328 -- ,p_api_version IN NUMBER
329 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
330 -- ,p_commit IN VARCHAR2 := fnd_api.g_false
331 -- ,x_msg_count OUT NUMBER
332 -- ,x_msg_data OUT VARCHAR2
333 -- ,x_return_status OUT VARCHAR2)
334
335 -- NOTES
336 -- HISTORY
337 -- 09/24/2002 feliu Create.
338 ----------------------------------------------------------------------
339 PROCEDURE post_utilized_budget (
340 p_offer_id IN NUMBER
341 ,p_offer_type IN VARCHAR2
342 ,p_api_version IN NUMBER
343 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
344 ,p_commit IN VARCHAR2 := fnd_api.g_false
345 ,p_check_date IN VARCHAR2 := fnd_api.g_true -- do date validation
346 ,x_msg_count OUT NOCOPY NUMBER
347 ,x_msg_data OUT NOCOPY VARCHAR2
348 ,x_return_status OUT NOCOPY VARCHAR2
349 );
350
351 ---------------------------------------------------------------------
352 -- PROCEDURE
353 -- adjust_utilized_budget
354 --
355 -- PURPOSE
356 --This API will be called by claim to automatic increase committed and utilized budget
357 --when automatic adjustment is allowed for scan data offer.
358 --It will increase both committed and utilized amount.
359
360 -- PARAMETERS
361 -- p_claim_id IN NUMBER
362 -- p_offer_id
363 -- p_product_activity_id
364 -- p_amount
365 -- ,p_cust_acct_id IN NUMBER
366 -- ,p_bill_to_cust_acct_id IN NUMBER
367 -- ,p_bill_to_site_use_id IN NUMBER
368 -- ,p_ship_to_site_use_id IN NUMBER
369 -- ,p_api_version IN NUMBER
370 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
371 -- ,p_commit IN VARCHAR2 := fnd_api.g_false
372 -- ,x_msg_count OUT NUMBER
373 -- ,x_msg_data OUT VARCHAR2
374 -- ,x_return_status OUT VARCHAR2)
375
376 -- NOTES
377 -- HISTORY
378 -- 09/24/2002 feliu Create.
379 -- 03/29/2005 kdass bug 5117557 - added params p_cust_acct_id, p_bill_to_cust_acct_id,
380 -- p_bill_to_site_use_id, p_ship_to_site_use_id
381 ----------------------------------------------------------------------
382 PROCEDURE adjust_utilized_budget (
383 p_claim_id IN NUMBER
384 ,p_offer_id IN NUMBER
385 ,p_product_activity_id IN NUMBER
386 ,p_amount IN NUMBER
387 ,p_cust_acct_id IN NUMBER
388 ,p_bill_to_cust_acct_id IN NUMBER
389 ,p_bill_to_site_use_id IN NUMBER
390 ,p_ship_to_site_use_id IN NUMBER
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 ,x_msg_count OUT NOCOPY NUMBER
395 ,x_msg_data OUT NOCOPY VARCHAR2
396 ,x_return_status OUT NOCOPY VARCHAR2
397 );
398
399 /*****************************************************************************************/
400 -- Start of Comments
401 -- NAME
402 -- update_budget_source
403 -- PURPOSE
404 -- This API is called from the java layer from the update button on budget_sourcing screen
405 -- It update source_from_parent column for ams_campaign_schedules_b and AMS_EVENT_OFFERS_ALL_B.
406 -- HISTORY
407 -- 12/08/2002 feliu CREATED
408 ---------------------------------------------------------------------
409
410 PROCEDURE update_budget_source(
411 p_object_version_number IN NUMBER
412 ,p_budget_used_by_id IN NUMBER
413 ,p_budget_used_by_type IN VARCHAR2
414 ,p_from_parent IN VARCHAR2
415 ,p_api_version IN NUMBER
416 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
417 ,p_commit IN VARCHAR2 := fnd_api.g_false
418 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
419 ,x_return_status OUT NOCOPY VARCHAR2
420 ,x_msg_count OUT NOCOPY NUMBER
421 ,x_msg_data OUT NOCOPY VARCHAR2
422 );
423
424
425 /*****************************************************************************************/
426 -- Start of Comments
427 -- NAME
428 -- post_sf_lumpsum_amount
429 -- PURPOSE
430 -- This API is called from soft fund request to create expense based utilization.
431 -- HISTORY
432 -- 10/22/2003 feliu CREATED
433 ---------------------------------------------------------------------
434 PROCEDURE post_sf_lumpsum_amount (
435 p_offer_id IN NUMBER
436 ,p_api_version IN NUMBER
437 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
438 ,p_commit IN VARCHAR2 := fnd_api.g_false
439 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
440 ,x_msg_count OUT NOCOPY NUMBER
441 ,x_msg_data OUT NOCOPY VARCHAR2
442 ,x_return_status OUT NOCOPY VARCHAR2
443 );
444
445 /*****************************************************************************************/
446 --rec_type to hold the fund src curr
447 --This is a private rec type to be used by this API only
448 ---------------------------------------------------------------------
449 TYPE parent_src_rec_type IS RECORD (
450 fund_id NUMBER
451 ,fund_curr VARCHAR2 (30)
452 ,fund_amount NUMBER
453 ,plan_amount NUMBER
454 );
455
456 /*****************************************************************************************/
457 --tbl_type to hold the amount
458 --This is a private rec type to be used by this API only
459 ---------------------------------------------------------------------
460
461 TYPE parent_src_tbl_type IS TABLE OF parent_src_rec_type
462 INDEX BY BINARY_INTEGER;
463
464 /*****************************************************************************************/
465 --
466 -- NAME
467 -- get_parent_Src
468 -- PURPOSE
469 -- API to automaticaly populate the parent_source_id ( fund_id), parent_curr, parent_amt
470 -- for transfers and requests
471 -- HISTORY
472 -- 04/27/2001 mpande Created.
473 ---------------------------------------------------------------------
474 PROCEDURE get_parent_src (
475 p_budget_source_type IN VARCHAR2
476 ,p_budget_source_id IN NUMBER
477 ,p_amount IN NUMBER
478 ,p_req_curr IN VARCHAR2
479 ,p_mode IN VARCHAR2 := jtf_plsql_api.g_create
480 ,p_old_amount IN NUMBER := 0
481 ,p_exchange_rate_type IN VARCHAR2 DEFAULT NULL -- Added for bug 7030415
482 ,x_return_status OUT NOCOPY VARCHAR2
483 ,x_parent_src_tbl OUT NOCOPY parent_src_tbl_type
484 );
485
486 ---------------------------------------------------------------------
487
488 END ozf_fund_adjustment_pvt;
[Home] [Help]