1 PACKAGE ozf_actbudgets_pvt AUTHID CURRENT_USER AS
2 /*$Header: ozfvbdgs.pls 120.12 2011/12/20 15:24:46 nirprasa ship $*/
3 -- Start of Comments
4 --
5 -- NAME
6 -- OZF_ACTBUDGETS_PVT
7 --
8 -- PURPOSE
9 -- This package is a Private API for managing Budget information in
10 -- OZF.
11 --
12 -- Procedures:
13 -- Create_Act_Budgets (see below for specification)
14 -- Update_Act_Budgets (see below for specification)
15 -- Delete_Act_Budgets (see below for specification)
16 -- Lock_Act_Budgets (see below for specification)
17 -- Validate_Act_Budgets (see below for specification)
18 -- Validate_Act_Budgets_Items (see below for specification)
19 -- Validate_Act_Budgets_Record (see below for specification
20 -- Complete_Act_Budgets_Rec
21 -- Init_Act_Budgets_Rec
22 --
23 -- NOTES
24 -- Added by mpande 04/26/2001
25 -- The Transfer_type columns can have the follwoing values
26 -- REQUEST -- When an object requests money . A workflow is submitted
27 -- Workflow for FUND --> to FUND is different than FUND --> OTHER OBJECTS (CAMP,EVEH)
28 -- TRANSFER -- This means it is a transfer back to the parent or the FUND
29 -- RELEASE -- Only for FUND ( Releasing Holdback)
30 -- RESERVE -- Only for FUND ( Reserving Holdback)
31 -- UTILIZED-- Utilized budget Amounts by the objects
32 --- The data in the table looks the following
33 -- Budget Source is always the depleting object and used_by is always the target object
34 -- Used_by_id Used_by_type bdg_src_id bdg_src_type transfer_type request_amt req cur apprv_amt appr_in_curr apprv_org_amt
35 -- C1 CAMP F1 FUND 'REQUEST' 1000 USD 1000 GBP 300
36 --
37 -- (C1 curr) (C1 curr) (F1 curr)
38 -- C1 CAMP F2 FUND 'REQUEST' 2000 USD 1500 CND 500
39 -- (C1 curr) (C1 curr) (F2 curr)
40 -- F1 CAMP C1 FUND 'TRANSFER' 1000 USD 500 GBP 150
41 -- (F1 curr) (F1 curr) (F1 curr)
42 -- CS1 CSCH C1 FUND 'REQUEST' 1200 RS 60,000 USD 1200
43 -- (CS1 curr) (CS1 curr) (C1 curr)
44
45 --- Sourcing Rules
46 -- 1) You can only source directly frma budget or from your parent object
47 -- for eg. Campaign schedule can only source from its parent campaign or a budget
48 -- and not from its peer or anyother object
49 -- 2) Progrozf cannot source from a budget
50 -- 3) Offers have to source directly from a budget . It cannot use the fund from its parent activity
51 -- 4) You cannot transfer more than what you have requested
52 -- History created sugupta 04/12/2000
53 -- 25-Jun-2000 choang Commented out show errors and uncommented exit
54 -- 14-Aug-2000 choang Modified signature of act_budgets_rec_type.
55 -- 16-Aug-2000 choang Added Init_Act_Budgets_Rec, Approve_ActBudget,
56 -- Reject_ActBudget and Close_ActBudget.
57 -- 20-Aug-2000 choang Added user_status_id.
58 -- 22-Aug-2000 choang Added can_modify() and is_account_closed().
59 -- 12-Sep-2000 choang 1) Moved approval API's to OZF_BudgetApproval_PVT.
60 -- 22-FEB-2001 mpande ADDED two more columns, adjusted_flag and posted flag
61 -- 22-Feb-2001 mpande Modified for All Hornet changes.
62 -- 1) Addded 7 new columns and added functional validation
63 -- 2) ALL FUND_TRANSFERS and requests are going to be performed from this table-- Added code for that
64 -- 3) Integrated with notes API to create justification and comments
65 -- 04/26/2001 mpande 1)Added code for utilizarions , requesterId , date_requred_by , transfertype and respective validations
66 -- 2) Added code for Parent source_id -- This value is always Budget id
67 -- 3) Added Code for transfer_type - Utilizations
68 -- 05/22/2001 mpande Added a new overloaded procedure for update_Act_budget
69 -- 06/07/2001 feliu Added partner_holding_type, partner_address_id, vendor_id.
70 -- 06/29/2001 feliu Added owner_id.
71 -- 10/22/2001 mpande Changed code different owner allocation bug
72 -- 10/23/2001 feliu Added record type act_util_rec_type, recal_flag in act_budgets_rec_type, and
73 -- one more input p_act_util_rec in create_act_budgets. added one overload create_act_budgets.
74 -- 12/19/2001 mpande Added Code for src_curr_request_amount
75 -- 02/26/2002 fliu added more comlumns for act_util_rec_type.
76 -- 04/16/2002 feliu Moved some functions to OZF_ACTBUDGETRULES_PVT to reduce this file size.
77 -- 10/28/2002 feliu Change for 11.5.9
78 -- 10/28/2002 feliu added scan_unit,scan_unit_remaining,activity_product_id,scan_type_id for act_util_rec_type.
79 -- 11/12/2002 feliu added volume_offer_tiers_id.
80 -- 11/04/2003 yzhao 11.5.10: added billto_cust_account_id, reference_type, reference_id to act_util_rec_type
81 -- 06/12/2005 rimehrot R12 Changes
82 -- 03/16/2006 kdass fixed bug 5080481 - exposed flexfields
83 -- 08/01/2008 nirprasa fixed bug 7030415
84 -- 08/14/2008 nirprasa fixed bug 6657242
85 -- 08/14/2008 nirprasa fixed bug 7425189
86 -- 06/12/2009 kdass bug 8532055 - ADD EXCHANGE RATE DATE PARAM TO OZF_FUND_UTILIZED_PUB.CREATE_FUND_ADJUSTMENT API
87 -- 07/24/2009 kdass Bug 8726683 - SSD Adjustments ER - Return utilization_id to the adjustment API
88 -- 2/17/2010 nepanda Bug 9131648 : multi currency changes
89 -- 07/05/2010 kdass FP bug 9470625 - MULTIPLE BATCH LINES FOR THE SAME UTILIZATION ENTRY
90 -- 04/01/2011 muthsubr Fix for bug#8867381 - IDSM BATCH PAYMENT INITIALIZATION ERROR
91 -- 04/01/2011 muthsubr For TPA Parallel Execution ER Bug#9614703.
92 -- 12/20/2011 nirprasa 13406517 - TST1213:SSDADJ:CALCULATION INCORRECT IF ONE ITEM
93 -- ADDED MULTIPLE TIMES TO OFFER
94 -- End of Comments
95
96 -- global constants
97
98 TYPE act_budgets_rec_type IS RECORD(
99 activity_budget_id NUMBER,
100 last_update_date DATE,
101 last_updated_by NUMBER,
102 creation_date DATE,
103 created_by NUMBER,
104 last_update_login NUMBER,
105 object_version_number NUMBER,
106 act_budget_used_by_id NUMBER,
107 arc_act_budget_used_by VARCHAR2(30),
108 budget_source_type VARCHAR2(30),
109 budget_source_id NUMBER,
110 transaction_type VARCHAR2(30),
111 request_amount NUMBER,
112 request_currency VARCHAR2(15),
113 request_date DATE,
114 user_status_id NUMBER,
115 status_code VARCHAR2(30),
116 approved_amount NUMBER,
117 approved_original_amount NUMBER,
118 approved_in_currency VARCHAR2(15),
119 -- ADDED 06/18/2000 SUGUPTA
120 approval_date DATE,
121 approver_id NUMBER,
122 -- ADDED 09/26/2009 MUTHSUBR
123 approved_amount_fc NUMBER,
124 src_curr_request_amt NUMBER,
125 spent_amount NUMBER,
126 partner_po_number VARCHAR2(50),
127 partner_po_date DATE,
128 partner_po_approver VARCHAR2(120),
129 --ADDED 02/22/2001 MPANDE
130 adjusted_flag VARCHAR2(1),
131 posted_flag VARCHAR2(1),
132 justification VARCHAR(4000),
133 comment VARCHAR(4000),
134 parent_act_budget_id NUMBER,
135 contact_id NUMBER,
136 reason_code VARCHAR2(30),
137 transfer_type VARCHAR2(30),
138 requester_id NUMBER,
139 date_required_by DATE,
140 parent_source_id NUMBER,
141 parent_src_curr VARCHAR2(30),
142 parent_src_apprvd_amt NUMBER,
143 partner_holding_type VARCHAR2(30),
144 partner_address_id NUMBER,
145 vendor_id NUMBER,
146 owner_id NUMBER,
147 recal_flag VARCHAR2(1),
148 exchange_rate_date DATE,-- nirprasa, Added for bug 7425189
149 -- **************--
150 attribute_category VARCHAR2(30),
151 attribute1 VARCHAR2(150),
152 attribute2 VARCHAR2(150),
153 attribute3 VARCHAR2(150),
154 attribute4 VARCHAR2(150),
155 attribute5 VARCHAR2(150),
156 attribute6 VARCHAR2(150),
157 attribute7 VARCHAR2(150),
158 attribute8 VARCHAR2(150),
159 attribute9 VARCHAR2(150),
160 attribute10 VARCHAR2(150),
161 attribute11 VARCHAR2(150),
162 attribute12 VARCHAR2(150),
163 attribute13 VARCHAR2(150),
164 attribute14 VARCHAR2(150),
165 attribute15 VARCHAR2(150),
166 src_curr_req_amt NUMBER);
167
168 TYPE act_util_rec_type IS RECORD
169 (
170 object_type VARCHAR2(30)
171 ,object_id NUMBER
172 ,adjustment_type VARCHAR2(30)
173 ,camp_schedule_id NUMBER
174 ,adjustment_type_id NUMBER
175 ,product_level_type VARCHAR2(30)
176 ,product_id NUMBER
177 ,cust_account_id NUMBER
178 ,price_adjustment_id NUMBER
179 ,utilization_type VARCHAR2(30)
180 ,adjustment_date DATE
181 ,gl_date DATE
182 ,scan_unit NUMBER
183 ,scan_unit_remaining NUMBER
184 ,activity_product_id NUMBER
185 ,scan_type_id NUMBER -- this colums is not in the table but required for scan data offers adj
186 ,volume_offer_tiers_id NUMBER
187 -- 11/04/2003 yzhao 11.5.10: added
188 ,billto_cust_account_id NUMBER
189 ,reference_type VARCHAR2(30)
190 ,reference_id NUMBER
191 -- 01/02/2004 kdass added for 11.5.10
192 ,order_line_id NUMBER
193 ,org_id NUMBER
194 ,orig_utilization_id NUMBER
195 ,gl_posted_flag VARCHAR2(1)
196 ,bill_to_site_use_id NUMBER
197 ,ship_to_site_use_id NUMBER
198 --07/26/2005 kdass added for 12.0
199 ,gl_account_credit NUMBER
200 ,gl_account_debit NUMBER
201 ,site_use_id NUMBER -- fix for bug 6657242
202 ,exchange_rate_date DATE -- nirprasa, Added for bug 7425189
203 ,exchange_rate_type VARCHAR2(30)--nirprasa, added for 12.2 enhancements
204 --nirprasa, ER 8399134
205 ,currency_code VARCHAR2(15)
206 ,plan_curr_amount NUMBER
207 ,plan_curr_amount_remaining NUMBER
208 ,plan_currency_code VARCHAR2(15)
209 ,fund_request_amount NUMBER
210 ,fund_request_amount_remaining NUMBER
211 ,fund_request_currency_code VARCHAR2(15)
212 --nirprasa, ER 8399134
213 --kdass added flexfields
214 ,attribute_category VARCHAR2(30)
215 ,attribute1 VARCHAR2(150)
216 ,attribute2 VARCHAR2(150)
217 ,attribute3 VARCHAR2(150)
218 ,attribute4 VARCHAR2(150)
219 ,attribute5 VARCHAR2(150)
220 ,attribute6 VARCHAR2(150)
221 ,attribute7 VARCHAR2(150)
222 ,attribute8 VARCHAR2(150)
223 ,attribute9 VARCHAR2(150)
224 ,attribute10 VARCHAR2(150)
225 ,attribute11 VARCHAR2(150)
226 ,attribute12 VARCHAR2(150)
227 ,attribute13 VARCHAR2(150)
228 ,attribute14 VARCHAR2(150)
229 ,attribute15 VARCHAR2(150)
230 --kdass - bug 9470625
231 ,cost_price NUMBER
232 ,cost_price_currency_code VARCHAR2(15)
233 ,discount_type VARCHAR2(30)
234 ,discount_amount NUMBER
235 ,discount_amount_currency_code VARCHAR2(15)
236 ,list_line_id NUMBER
237 );
238
239 G_MISS_ACT_UTIL_REC act_util_rec_type;
240
241 /****************************************************************************/
242 -- Start of Comments
243 --
244 -- API name : create_act_budgets
245 -- Type : Private
246 -- Function : Create a row in OZF_ACT_Budgets table
247 --
248 -- Pre-reqs : None
249 --
250 -- Version : Current version 1.0
251 -- Initial version 1.0
252 --
253 -- Note
254 -- End Of Comments
255 /****************************************************************************/
256
257 PROCEDURE create_act_budgets(
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 ,x_return_status OUT NOCOPY VARCHAR2
263 ,x_msg_count OUT NOCOPY NUMBER
264 ,x_msg_data OUT NOCOPY VARCHAR2
265 ,p_act_budgets_rec IN act_budgets_rec_type
266 ,x_act_budget_id OUT NOCOPY NUMBER);
267
268 /****************************************************************************/
269 -- Start of Comments
270 --
271 -- API name : create_act_budgets
272 -- Type : Private
273 -- Function : Create a row in OZF_ACT_Budgets table
274 --
275 -- Pre-reqs : None
276 --
277 -- Version : Current version 1.0
278 -- Initial version 1.0
279 --
280 -- Note : This overloaded procedure is to be called from
281 -- recalculating concurrent program.
282 --
283 -- End Of Comments
284 /****************************************************************************/
285
286 PROCEDURE create_act_budgets(
287 p_api_version IN NUMBER
291 ,x_return_status OUT NOCOPY VARCHAR2
288 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
289 ,p_commit IN VARCHAR2 := fnd_api.g_false
290 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
292 ,x_msg_count OUT NOCOPY NUMBER
293 ,x_msg_data OUT NOCOPY VARCHAR2
294 ,p_act_budgets_rec IN act_budgets_rec_type
295 ,p_act_util_rec IN act_util_rec_type
296 ,x_act_budget_id OUT NOCOPY NUMBER
297 ,p_approval_flag IN VARCHAR2 :=fnd_api.g_false);
298
299
300 /****************************************************************************
301 * Ying Zhao: 06/21/2004 overloaded function to return actual utilized amount for chargeback
302 * added x_utilized_amount
303 */
304 PROCEDURE create_act_budgets(
305 p_api_version IN NUMBER
306 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
307 ,p_commit IN VARCHAR2 := fnd_api.g_false
308 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
309 ,x_return_status OUT NOCOPY VARCHAR2
310 ,x_msg_count OUT NOCOPY NUMBER
311 ,x_msg_data OUT NOCOPY VARCHAR2
312 ,p_act_budgets_rec IN act_budgets_rec_type
313 ,p_act_util_rec IN act_util_rec_type
314 ,x_act_budget_id OUT NOCOPY NUMBER
315 ,p_approval_flag IN VARCHAR2 :=fnd_api.g_false
316 ,x_utilized_amount OUT NOCOPY NUMBER);
317
318
319 /****************************************************************************
320 * 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
321 * px_ozf_funds_old_rectype, px_ozf_funds_new_rectype
322 * for bug#8867381.
323 * 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
324 * for TPA Parallel Execution ER Bug#9614703.
325 ****************************************************************************/
326 PROCEDURE create_act_budgets(
327 p_api_version IN NUMBER
328 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
329 ,p_commit IN VARCHAR2 := fnd_api.g_false
330 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
331 ,x_return_status OUT NOCOPY VARCHAR2
332 ,x_msg_count OUT NOCOPY NUMBER
333 ,x_msg_data OUT NOCOPY VARCHAR2
334 ,p_act_budgets_rec IN act_budgets_rec_type
335 ,p_act_util_rec IN act_util_rec_type
336 ,x_act_budget_id OUT NOCOPY NUMBER
337 ,p_approval_flag IN VARCHAR2 :=fnd_api.g_false
338 ,x_utilized_amount OUT NOCOPY NUMBER
339 ,p_resale_batch_id IN NUMBER
340 ,p_batch_type IN VARCHAR2
341 ,p_use_fund_staging_tables IN VARCHAR2
342 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
343 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
344 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER - 9614703
345 );
346
347 --nirprasa - added for Bug 9383565
348 PROCEDURE create_act_budgets(
349 p_api_version IN NUMBER
350 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
351 ,p_commit IN VARCHAR2 := fnd_api.g_false
352 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
353 ,x_return_status OUT NOCOPY VARCHAR2
354 ,x_msg_count OUT NOCOPY NUMBER
355 ,x_msg_data OUT NOCOPY VARCHAR2
356 ,p_act_budgets_rec IN act_budgets_rec_type
357 ,p_act_util_rec IN act_util_rec_type
358 ,x_act_budget_id OUT NOCOPY NUMBER
359 ,p_approval_flag IN VARCHAR2 :=fnd_api.g_false
360 ,x_utilized_amount OUT NOCOPY NUMBER
361 ,p_resale_batch_id IN NUMBER
362 ,p_batch_type IN VARCHAR2
363 ,p_use_fund_staging_tables IN VARCHAR2
364 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
365 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
366 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
367 ,x_utilization_id OUT NOCOPY NUMBER
368 );
369
370 PROCEDURE create_act_budgets (
371 p_api_version IN NUMBER
372 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
373 ,p_commit IN VARCHAR2 := fnd_api.g_false
374 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
375 ,x_return_status OUT NOCOPY VARCHAR2
376 ,x_msg_count OUT NOCOPY NUMBER
377 ,x_msg_data OUT NOCOPY VARCHAR2
378 ,p_act_budgets_rec IN act_budgets_rec_type
379 ,p_act_util_rec IN act_util_rec_type
380 ,x_act_budget_id OUT NOCOPY NUMBER
381 ,p_approval_flag IN VARCHAR2 := fnd_api.g_false
382 ,x_utilized_amount OUT NOCOPY NUMBER
383 ,x_utilization_id OUT NOCOPY NUMBER
384 );
385
386 /****************************************************************************/
387 -- Start of Comments
388 --
389 -- API name : Update_Act_Budgets
390 -- Type : Private
391 -- Function : Update a row in OZF_ACT_Budgets table
392 --
393 -- Pre-reqs : None
394 --
395 -- Version : Current version 1.0
396 -- Initial version 1.0
397 --
398 -- Note : 1. p_act_Budgets_rec.ACT_BUDGET_USED_BY_ID, ARC_ACT_BUDGET_USED_BY
402 -- 3. p_act_Budgets_rec.activity_budget_id is not updatable
399 -- BUDGET_SOURCE_TYPE, BUDGET_SOURCE_ID are required parameters
400 -- Should also make CONTRIBUTION_AMOUNT mandatory
401 -- 2. if source type is PARTNER, then PO related fields become mandatory
403 --
404 -- End Of Comments
405 /****************************************************************************/
406
407 PROCEDURE update_act_budgets(
408 p_api_version IN NUMBER
409 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
410 ,p_commit IN VARCHAR2 := fnd_api.g_false
411 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
412 ,x_return_status OUT NOCOPY VARCHAR2
413 ,x_msg_count OUT NOCOPY NUMBER
414 ,x_msg_data OUT NOCOPY VARCHAR2
415 ,p_act_budgets_rec IN act_budgets_rec_type);
416 /****************************************************************************/
417 -- Start of Comments
418 --
419 -- API name : Update_Act_Budgets
420 -- Type : Private
421 -- Function : Update a row in OZF_ACT_Budgets table
422 -- Note : This overloaded procedure is to be called from
423 -- Workflow to maintain the context.
424 --
425 -- End Of Comments
426 /****************************************************************************/
427 PROCEDURE update_act_budgets(
428 p_api_version IN NUMBER
429 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
430 ,p_commit IN VARCHAR2 := fnd_api.g_false
431 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
432 ,x_return_status OUT NOCOPY VARCHAR2
433 ,x_msg_count OUT NOCOPY NUMBER
434 ,x_msg_data OUT NOCOPY VARCHAR2
435 ,p_act_budgets_rec IN act_budgets_rec_type
436 ,p_parent_process_flag IN VARCHAR2
437 ,p_parent_process_key IN VARCHAR2
438 ,p_parent_context IN VARCHAR2
439 ,p_parent_approval_flag IN VARCHAR2
440 ,p_continue_flow IN VARCHAR2
441 ,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
442 -- 10/22/2001 mpande added for allocation bug
443 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
444 ,p_act_util_rec IN act_util_rec_type := NULL
445 );
446
447
448
449 /****************************************************************************/
450 -- Start of Comments
451 --
452 -- API name : Update_Act_Budgets
453 -- Type : Private
454 -- Function : Update a row in OZF_ACT_Budgets table
455 -- Note : This overloaded procedure is to be called from
456 -- Workflow to maintain the context.
457 --
458 -- End Of Comments
459 /****************************************************************************/
460 PROCEDURE update_act_budgets(
461 p_api_version IN NUMBER
462 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
463 ,p_commit IN VARCHAR2 := fnd_api.g_false
464 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
465 ,x_return_status OUT NOCOPY VARCHAR2
466 ,x_msg_count OUT NOCOPY NUMBER
467 ,x_msg_data OUT NOCOPY VARCHAR2
468 ,p_act_budgets_rec IN act_budgets_rec_type
469 ,p_parent_process_flag IN VARCHAR2
470 ,p_parent_process_key IN VARCHAR2
471 ,p_parent_context IN VARCHAR2
472 ,p_parent_approval_flag IN VARCHAR2
473 ,p_continue_flow IN VARCHAR2
474 ,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
475 -- 10/22/2001 mpande added for allocation bug
476 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
477 ,p_act_util_rec IN act_util_rec_type := NULL
478 ,x_utilized_amount OUT NOCOPY NUMBER);
479
480 /****************************************************************************/
481 -- Start of Comments
482 --
483 -- API name : Update_Act_Budgets
484 -- Type : Private
485 -- Function : Update a row in OZF_ACT_Budgets table
486 -- Note : This overloaded procedure is to be called from
487 -- Workflow to maintain the context.
488 --
489 -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
490 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype,
491 -- p_old_act_budgets_rec for bug#8867381.
492 -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
493 -- for TPA Parallel Execution ER Bug#9614703.
494 -- End Of Comments
495 /****************************************************************************/
496 PROCEDURE update_act_budgets(
497 p_api_version IN NUMBER
498 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
499 ,p_commit IN VARCHAR2 := fnd_api.g_false
500 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
501 ,x_return_status OUT NOCOPY VARCHAR2
502 ,x_msg_count OUT NOCOPY NUMBER
503 ,x_msg_data OUT NOCOPY VARCHAR2
504 ,p_old_act_budgets_rec IN act_budgets_rec_type
505 ,p_act_budgets_rec IN act_budgets_rec_type
506 ,p_parent_process_flag IN VARCHAR2
507 ,p_parent_process_key IN VARCHAR2
508 ,p_parent_context IN VARCHAR2
509 ,p_parent_approval_flag IN VARCHAR2
513 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
510 ,p_continue_flow IN VARCHAR2
511 ,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
512 -- 10/22/2001 mpande added for allocation bug
514 ,p_act_util_rec IN act_util_rec_type := NULL
515 ,x_utilized_amount OUT NOCOPY NUMBER
516 ,p_batch_type IN VARCHAR2
517 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
518 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
519 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
520 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER - 9614703
521 );
522
523 PROCEDURE update_act_budgets(
524 p_api_version IN NUMBER
525 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
526 ,p_commit IN VARCHAR2 := fnd_api.g_false
527 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
528 ,x_return_status OUT NOCOPY VARCHAR2
529 ,x_msg_count OUT NOCOPY NUMBER
530 ,x_msg_data OUT NOCOPY VARCHAR2
531 ,p_old_act_budgets_rec IN act_budgets_rec_type
532 ,p_act_budgets_rec IN act_budgets_rec_type
533 ,p_parent_process_flag IN VARCHAR2
534 ,p_parent_process_key IN VARCHAR2
535 ,p_parent_context IN VARCHAR2
536 ,p_parent_approval_flag IN VARCHAR2
537 ,p_continue_flow IN VARCHAR2
538 ,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
539 -- 10/22/2001 mpande added for allocation bug
540 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
541 ,p_act_util_rec IN act_util_rec_type := NULL
542 ,x_utilized_amount OUT NOCOPY NUMBER
543 ,p_batch_type IN VARCHAR2
544 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
545 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
546 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
547 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER - 9614703
548 ,x_utilization_id OUT NOCOPY NUMBER
549 );
550
551 /****************************************************************************/
552 -- Start of Comments
553 --
554 -- API name : Update_Act_Budgets
555 -- Type : Private
556 -- Function : Update a row in OZF_ACT_Budgets table
557 -- Note : This overloaded procedure is to be called from fund module for child -- parent approval
558 -- Workflow to maintain the context.
559 --
560 -- End Of Comments
561 /****************************************************************************/
562 PROCEDURE update_act_budgets(
563 p_api_version IN NUMBER
564 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
565 ,p_commit IN VARCHAR2 := fnd_api.g_false
566 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
567 ,x_return_status OUT NOCOPY VARCHAR2
568 ,x_msg_count OUT NOCOPY NUMBER
569 ,x_msg_data OUT NOCOPY VARCHAR2
570 ,p_act_budgets_rec IN act_budgets_rec_type
571 ,p_child_approval_flag IN VARCHAR2
572 -- 10/22/2001 mpande added for allocation bug
573 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
574 ,p_act_util_rec IN act_util_rec_type := NULL
575 );
576
577
578 /****************************************************************************
579 * Ying Zhao: 06/21/2004 overloaded function to return actual utilized amount for chargeback
580 * added x_utilized_amount
581 */
582 PROCEDURE update_act_budgets(
583 p_api_version IN NUMBER
584 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
585 ,p_commit IN VARCHAR2 := fnd_api.g_false
586 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
587 ,x_return_status OUT NOCOPY VARCHAR2
588 ,x_msg_count OUT NOCOPY NUMBER
589 ,x_msg_data OUT NOCOPY VARCHAR2
590 ,p_act_budgets_rec IN act_budgets_rec_type
591 ,p_child_approval_flag IN VARCHAR2
592 -- 10/22/2001 mpande added for allocation bug
593 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
594 ,p_act_util_rec IN act_util_rec_type := NULL
595 ,x_utilized_amount OUT NOCOPY NUMBER
596 );
597
598 --kdass - added for Bug 8726683
599 PROCEDURE update_act_budgets (
600 p_api_version IN NUMBER
601 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
602 ,p_commit IN VARCHAR2 := fnd_api.g_false
603 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
604 ,x_return_status OUT NOCOPY VARCHAR2
605 ,x_msg_count OUT NOCOPY NUMBER
606 ,x_msg_data OUT NOCOPY VARCHAR2
607 ,p_act_budgets_rec IN act_budgets_rec_type
608 ,p_parent_process_flag IN VARCHAR2
609 ,p_parent_process_key IN VARCHAR2
610 ,p_parent_context IN VARCHAR2
611 ,p_parent_approval_flag IN VARCHAR2
612 ,p_continue_flow IN VARCHAR2
613 ,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
614 ,p_requestor_owner_flag IN VARCHAR2 := 'N'
615 ,p_act_util_rec IN act_util_rec_type := NULL
616 ,x_utilized_amount OUT NOCOPY NUMBER
620 /*****************************************************************************************/
617 ,x_utilization_id OUT NOCOPY NUMBER
618 );
619
621 -- Start of Comments
622 --
623 -- API name : Delete_Act_Budgets
624 -- Type : Private
625 -- Function : Delete a row in OZF_ACT_BudgetsS table
626 --
627 -- Pre-reqs : None
628 -- Version : Current version 1.0
629 -- Initial version 1.0
630 --
631 -- Note : 1. p_Budgets_rec.activity_budget_id, object_version_number is a required parameter
632 --
633 -- End Of Comments
634
635 PROCEDURE delete_act_budgets(
636 p_api_version IN NUMBER
637 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
638 ,p_commit IN VARCHAR2 := fnd_api.g_false
639 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
640 ,x_return_status OUT NOCOPY VARCHAR2
641 ,x_msg_count OUT NOCOPY NUMBER
642 ,x_msg_data OUT NOCOPY VARCHAR2
643 ,p_act_budget_id IN NUMBER
644 ,p_object_version IN NUMBER);
645
646 /*****************************************************************************************/
647 -- Start of Comments
648 --
649 -- API name : Lock_Act_Budgets
650 -- Type : Private
651 -- Function : Lock a row in OZF_ACT_BudgetsS table
652 --
653 -- Pre-reqs : None
654 -- Paramaeters :
655 -- IN :
656 -- Version : Current version 1.0
657 -- Initial version 1.0
658 --
659 -- Note : p_Budgets_rec.activity_Budget_id, object_version_number is a required parameter
660 --
661 -- End Of Comments
662
663 PROCEDURE lock_act_budgets(
664 p_api_version IN NUMBER
665 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
666 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
667 ,x_return_status OUT NOCOPY VARCHAR2
668 ,x_msg_count OUT NOCOPY NUMBER
669 ,x_msg_data OUT NOCOPY VARCHAR2
670 ,p_act_budget_id IN NUMBER
671 ,p_object_version IN NUMBER);
672
673 /*****************************************************************************************/
674 -- Start of Comments
675 --
676 -- API name : Validate_Act_Budgets
677 -- Type : Private
678 -- Function : Validate a row in OZF_ACT_BudgetsS table
679 --
680 -- Pre-reqs : None
681 -- Version : Current version 1.0
682 -- Initial version 1.0
683 --
684 -- Note : 1. p_Budgets_rec.activity_Budget_id is a required parameter
685 -- 2. x_return_status will be FND_API.G_RET_STS_SUCCESS,
686 -- FND_API.G_RET_STS_ERROR, or
687 -- FND_API.G_RET_STS_UNEXP_ERROR
688 --
689 -- End Of Comments
690 /*****************************************************************************************/
691 PROCEDURE validate_act_budgets(
692 p_api_version IN NUMBER
693 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
694 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
695 ,x_return_status OUT NOCOPY VARCHAR2
696 ,x_msg_count OUT NOCOPY NUMBER
697 ,x_msg_data OUT NOCOPY VARCHAR2
698 ,p_act_budgets_rec IN act_budgets_rec_type);
699
700 /*****************************************************************************************/
701 -- Start of Comments
702 --
703 -- NAME
704 -- Validate_Act_Budgets_Items
705 --
706 -- PURPOSE
707 -- This procedure is to validate busget items
708 -- HISTORY
709 -- 24-Aug-2000 choang Changed ams_fund_details_v to ams_fund_details_v
710 -- 22-Feb-2001 mpande Modified for Hornet changes.
711 -- End of Comments
712 /*****************************************************************************************/
713 PROCEDURE validate_act_budgets_items(
714 p_act_budgets_rec IN act_budgets_rec_type
715 ,p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create
716 ,x_return_status OUT NOCOPY VARCHAR2);
717 /*****************************************************************************************/
718 -- Start of Comments
719 --
720 -- NAME
721 -- Validate_Act_Budgets_Record
722 --
723 -- PURPOSE
724 -- This procedure is to validate budget record
725 --
726 -- NOTES
727 -- HISTORY
728 -- 22-Aug-2000 choang Added validation of credit request amounts.
729 -- 23-Jan-2001 mpande Added validation in validation_actbudget_rec for not
730 -- to submit for approval when the requested amount is 0. BUG# 1604000
731 -- 22-Feb-2001 mpande Modified for Hornet changes.
732 -- End of Comments
733 /*****************************************************************************************/
734 PROCEDURE validate_act_budgets_record(
735 p_act_budgets_rec IN act_budgets_rec_type
736 ,p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create
737 ,x_return_status OUT NOCOPY VARCHAR2);
738 /*****************************************************************************************/
739 -- Start of Comments
740 --
741 -- NAME
742 -- complete_act_budgets_rec
743 --
744 -- PURPOSE
745 -- This procedure is to complete budget record
746 --
747 -- NOTES
748 -- HISTORY
749 -- End of Comments
750 /*****************************************************************************************/
754
751 PROCEDURE complete_act_budgets_rec(
752 p_act_budgets_rec IN act_budgets_rec_type
753 ,x_act_budgets_rec OUT NOCOPY act_budgets_rec_type);
755
756 /*****************************************************************************************/
757 -- Start of Comments
758 --
759 -- NAME
760 -- Init_Act_Budgets_Rec
761 -- PURPOSE
762 -- Initialize all column values to FND_API.g_miss_char/num/date
763 -- HISTORY
764 -- 15-Aug-2000 choang Created.
765 -- 22-Feb-2001 mpande Modified for Hornet changes.
766 /*****************************************************************************************/
767 PROCEDURE init_act_budgets_rec(
768 x_act_budgets_rec OUT NOCOPY act_budgets_rec_type);
769 /*****************************************************************************************/
770 -- Start of Comments
771 --
772 -- Procedure and function declarations.
773 --
774 -- NAME
775 -- get_object_currency
776 -- PURPOSE
777 -- Return the currency code of the object trying to
778 -- associate a budget.
779 /*****************************************************************************************/
780 FUNCTION get_object_currency(
781 p_object IN VARCHAR2
782 ,p_object_id IN NUMBER
783 ,x_return_status OUT NOCOPY VARCHAR2)
784 RETURN VARCHAR2;
785
786
787 /*****************************************************************************************/
788 -- Start of Comments
789 --
790 -- Procedure and function declarations.
791 --
792 -- NAME
793 -- create_child_act_budget
794 -- PURPOSE
795 -- create child requests when sourcing from parent.
796 --
797 /*****************************************************************************************/
798
799 PROCEDURE create_child_act_budget (
800 x_return_status OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2,
803 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
804 p_exchange_rate_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR --Added for bug 7030415
805 );
806
807 /*****************************************************************************************/
808 -- nirprasa, Added for bug 7425189
809 -- NAME
810 -- update_reconcile_objfundsum
811 --
812 -- PURPOSE
813 -- This Procedure updates record in object fund summary table
814 -- for budget reconcile. This will not affect any other flow.
815 --
816 -- NOTES
817 --
818 -- HISTORY
819
820 /******************************************************************************************/
821 /*commented for bug 8532055
822 PROCEDURE update_reconcile_objfundsum (
823 p_api_version IN NUMBER,
824 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
825 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
826 p_objfundsum_rec IN OZF_OBJFUNDSUM_PVT.objfundsum_rec_type,
827 p_conv_date IN DATE,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_count OUT NOCOPY NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2
831 );
832 */
833 /******************************************************************************************/
834 END ozf_actbudgets_pvt;
[Home] [Help]