[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_UTILIZED_PVT
Source
1 PACKAGE BODY OZF_FUND_UTILIZED_PVT AS
2 /* $Header: ozfvfutb.pls 120.17.12010000.10 2009/02/02 14:57:26 kdass ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'OZF_Fund_Utilized_PVT';
4 g_cons_fund_mode CONSTANT VARCHAR2 (30) := 'ADJUST'; --JTF_PLSQL_API.G_UPDATE
5 g_universal_currency CONSTANT VARCHAR2 (15) := fnd_profile.VALUE ('OZF_UNIV_CURR_CODE');
6 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
7
8
9 -----------------------------------------------------------------------
10 -- PROCEDURE
11 -- raise_business_event
12 --
13 -- HISTORY
14 -- 05/08/2004 feliu Created.
15 -----------------------------------------------------------------------
16
17
18 PROCEDURE raise_business_event(p_object_id IN NUMBER)
19 IS
20 l_item_key varchar2(30);
21 l_parameter_list wf_parameter_list_t;
22 BEGIN
23 l_item_key := p_object_id ||'_'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
24 l_parameter_list := WF_PARAMETER_LIST_T();
25
26
27 IF G_DEBUG THEN
28 ozf_utility_pvt.debug_message(' utilization Id is :'||p_object_id );
29 END IF;
30
31 wf_event.AddParameterToList(p_name => 'P_UTIL_ID',
32 p_value => p_object_id,
33 p_parameterlist => l_parameter_list);
34
35 IF G_DEBUG THEN
36 ozf_utility_pvt.debug_message('Item Key is :'||l_item_key);
37 END IF;
38
39 wf_event.raise( p_event_name =>'oracle.apps.ozf.fund.adjustment.approval',
40 p_event_key => l_item_key,
41 p_parameters => l_parameter_list);
42
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 RAISE Fnd_Api.g_exc_error;
47 ozf_utility_pvt.debug_message('Exception in raising business event');
48 END;
49
50
51 ---------------------------------------------------------------------
52 -- PROCEDURE
53 -- Create_Utilization
54 --
55 -- HISTORY
56 -- 04/25/2001 Mumu Pande Create.
57 -- p_create_gl_entry IN VARCHAR2 := FND_API.g_false this flag indicates wether to
58 -- create gl entry or not . Right now the entry is only for utilization type 'adjustment'
59 -- Requirements for 11.5.5- hornet
60 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
61 -- 02/23/2001 Mumu Pande Updated for Hornet requirements
62 -- 02/09/2001 Mumu Pande Added validation routine for adjsutment_type_id
63 -- Created Subroutines to create committed amount record in ozf_act_budgets
64 -- 08/28/2001 Mumu Pande Bug#1950117
65 -- 09/05/2001 Mumu Pande Bug#1970359
66 -- 02/28/2002 Feliu Added create_act_utilization procedure and remove
67 -- recal_comm_flag. Modify create_utilzation.
68 -- 28-OCT-2002 Feliu added scan_unit,scan_unit_remaining,activity_product_id,
69 -- scan_type_id for utilization_rec_type for 11.5.9.
70 -- 12/23/2002 feliu Changed for chargback.
71 -- 04/25/2003 feliu fixed bug 2925302 for source from sales accrual budget.
72 -- 10/20/2003 yzhao fixed TEVA bug - customer accrual budget committed amount remains 0 when third party accrual happens
73 -- 11/14/2003 yzhao 11.5.10 populate both utilized_amt and earned_amt, added CHARGEBACK
74 -- 02/24/2004 yzhao 11.5.10 fix bug 3461280 - manual adj for off-invoice, should update paid amount
75 -- customer accrual without liability: no gl posting, do not update earned amount
76 -- use constant ozf_accrual_engine.G_GL_FLAG_* for gl_posted_flag
77 -- 19-AUG-2008 ateotia Bug # 7337263 fixed.
78 -- FP:11510-R12 7129397 - GOT ERROR ON OBJECT_VERSION_NUMBER IN INITIATE PAYMENT
79 -- Added a condition for Chargeback Batch using Fully Accrued budget.
80 ---------------------------------------------------------------------
81
82 PROCEDURE create_utilization (
83 p_api_version IN NUMBER
84 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
85 ,p_commit IN VARCHAR2 := fnd_api.g_false
86 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
87 ,x_return_status OUT NOCOPY VARCHAR2
88 ,x_msg_count OUT NOCOPY NUMBER
89 ,x_msg_data OUT NOCOPY VARCHAR2
90 ,p_create_gl_entry IN VARCHAR2 := fnd_api.g_false
91 ,p_utilization_rec IN utilization_rec_type
92 ,x_utilization_id OUT NOCOPY NUMBER
93 ) IS
94 l_api_version CONSTANT NUMBER := 1.0;
95 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Utilization';
96 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
97 || '.'
98 || l_api_name;
99 l_return_status VARCHAR2 (1);
100 l_utilization_rec utilization_rec_type := p_utilization_rec;
101 l_object_version_number NUMBER := 1;
102 l_utilization_check VARCHAR2 (10);
103 l_fund_rec ozf_funds_pvt.fund_rec_type;
104 l_utilized_amt NUMBER;
105 l_earned_amt NUMBER;
106 l_gl_rec ozf_gl_interface_pvt.gl_interface_rec_type;
107 l_event_id NUMBER;
108 l_obj_num NUMBER;
109 l_committed_amt NUMBER;
110 l_act_budget_id NUMBER;
111 l_act_budget_objver NUMBER;
112 l_fund_type VARCHAR2 (30);
113 l_accrual_basis VARCHAR2 (30);
114 l_original_budget NUMBER;
115 l_set_of_book_id NUMBER;
116 l_sob_type_code VARCHAR2 (30);
117 l_fc_code VARCHAR2 (150);
118 l_paid_amt NUMBER;
119 l_util_rec ozf_actbudgets_pvt.act_util_rec_type;
120 l_gl_posted_flag VARCHAR2(1);
121 l_offer_type VARCHAR2 (30);
122 l_volume_offer_type VARCHAR2 (30);
123 l_objfundsum_rec ozf_objfundsum_pvt.objfundsum_rec_type := NULL;
124 l_custom_setup_id NUMBER;
125
126 /*
127 -- Cursor to get the org_id for budget
128 CURSOR c_fund_org_id (p_fund_id IN NUMBER)IS
129 SELECT org_id, ledger_id
130 FROM ozf_funds_all_b
131 WHERE fund_id = p_fund_id;
132 */
133
134 -- l_activity_product_id NUMBER;
135 -- Cursor to get the sequence for utilization_id
136 CURSOR c_utilization_seq IS
137 SELECT ozf_funds_utilized_s.NEXTVAL
138 FROM DUAL;
139
140 -- Cursor to validate the uniqueness of the utilization_id
141 CURSOR c_utilization_count (cv_utilization_id IN NUMBER) IS
142 SELECT 'X'
143 FROM ozf_funds_utilized_all_b
144 WHERE utilization_id = cv_utilization_id;
145
146 -- Cursor to get original utilization gl_posted_flag
147 CURSOR c_get_orig_gl_flag (p_utilization_id IN NUMBER) IS
148 SELECT gl_posted_flag
149 FROM ozf_funds_utilized_all_b
150 WHERE utilization_id = p_utilization_id;
151
152 -- Cursor to get fund earned amount and object_version_number
153 CURSOR c_fund_b (p_fund_id IN NUMBER) IS
154 SELECT utilized_amt
155 ,earned_amt
156 ,object_version_number
157 ,committed_amt
158 ,accrual_basis
159 ,fund_type
160 ,original_budget
161 ,paid_amt
162 ,plan_id
163 ,liability_flag -- yzhao: 10/20/2003 added
164 ,recal_committed -- yzhao: 10/20/2003 added
165 FROM ozf_funds_all_b
166 WHERE fund_id = p_fund_id;
167
168 --- Cursor to get the adjustment_type from adjustmentId
169 CURSOR c_adj_type (p_adj_type_id IN NUMBER) IS
170 SELECT adjustment_type
171 FROM ozf_claim_types_all_b
172 WHERE claim_type_id = p_adj_type_id;
173
174 /* 10/20/2003 yzhao Fix TEVA bug - customer fully accrual budget committed amount is always 0 when third party accrual happens
175 update ozf_act_budgets REQUEST between fully accrual budget and its offer when accrual happens
176 */
177 CURSOR c_accrual_budget_reqeust(p_fund_id IN NUMBER, p_plan_id IN NUMBER) IS
178 SELECT activity_budget_id
179 , object_version_number
180 FROM ozf_act_budgets
181 WHERE arc_act_budget_used_by = 'OFFR'
182 AND act_budget_used_by_id = p_plan_id
183 AND budget_source_type = 'FUND'
184 AND budget_source_id = p_fund_id
185 AND transfer_type = 'REQUEST'
186 AND status_code = 'APPROVED';
187
188 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
189 CURSOR c_budget_request_utilrec(p_fund_id IN NUMBER, p_plan_id IN NUMBER, p_actbudget_id IN NUMBER) IS
190 SELECT utilization_id
191 , object_version_number
192 FROM ozf_funds_utilized_all_b
193 WHERE utilization_type = 'REQUEST'
194 AND fund_id = p_fund_id
195 AND plan_type = 'FUND'
196 AND plan_id = p_fund_id
197 AND component_type = 'OFFR'
198 AND component_id = p_plan_id
199 AND ams_activity_budget_id = p_actbudget_id;
200 */
201
202 CURSOR c_get_deal_accrual_flag(p_qp_list_header_id IN NUMBER, p_product_type IN VARCHAR2, p_product_id IN NUMBER) IS
203 SELECT 1
204 FROM DUAL
205 WHERE EXISTS (SELECT 1
206 FROM qp_list_lines line, qp_pricing_attributes attr
207 WHERE attr.list_header_id = p_qp_list_header_id
208 AND line.list_line_id = attr.list_line_id
209 AND NVL(line.accrual_flag, 'N') = 'Y'
210 AND attr.product_attribute = DECODE(NVL(p_product_type, 'OTHER')
211 , 'PRODUCT', 'ITEM', 'FAMILY', 'CATEGORY', attr.product_attribute)
212 AND attr.product_attr_value = NVL(p_product_id, attr.product_attr_value)
213 AND attr.product_attribute_context = 'ITEM'
214 );
215
216 /*fix for bug 4778995
217 -- yzhao: 11.5.10 get time_id
218 CURSOR c_get_time_id(p_date IN DATE) IS
219 SELECT month_id, ent_qtr_id, ent_year_id
220 FROM ozf_time_day
221 WHERE report_date = trunc(p_date);
222 */
223
224 -- yzhao: 11.5.10 get offer's beneficiary account id
225 CURSOR c_offer_info (p_offer_id IN NUMBER) IS
226 SELECT offer_type, volume_offer_type, custom_setup_id
227 FROM ozf_offers
228 WHERE qp_list_header_id = p_offer_id;
229
230
231 CURSOR c_sd_request_header_id(p_list_header_id IN NUMBER) IS
232 SELECT request_header_id
233 FROM ozf_sd_request_headers_all_b
234 WHERE offer_id =p_list_header_id;
235
236 /*
237 -- yzhao: 11.5.10 check if post to gl for off invoice discount
238 CURSOR c_offinvoice_gl_post_flag(p_fund_id IN NUMBER) IS
239 SELECT NVL(sob.gl_acct_for_offinv_flag, 'F')
240 FROM ozf_sys_parameters_all sob
241 ,ozf_funds_all_b fun
242 WHERE fun.fund_id = p_fund_id
243 AND sob.org_id = fun.ORG_id ;
244 */
245 CURSOR c_offinvoice_gl_post_flag(p_org_id IN NUMBER) IS
246 SELECT NVL(sob.gl_acct_for_offinv_flag, 'F')
247 FROM ozf_sys_parameters_all sob
248 WHERE sob.org_id = p_org_id;
249
250 -- yzhao: R12 update ozf_object_fund_summary table
251 CURSOR c_get_objfundsum_rec(p_object_type IN VARCHAR2, p_object_id IN NUMBER, p_fund_id IN NUMBER) IS
252 SELECT objfundsum_id
253 , object_version_number
254 , committed_amt
255 , recal_committed_amt
256 , utilized_amt
257 , earned_amt
258 , paid_amt
259 , plan_curr_committed_amt
260 , plan_curr_recal_committed_amt
261 , plan_curr_utilized_amt
262 , plan_curr_earned_amt
263 , plan_curr_paid_amt
264 , univ_curr_committed_amt
265 , univ_curr_recal_committed_amt
266 , univ_curr_utilized_amt
267 , univ_curr_earned_amt
268 , univ_curr_paid_amt
269 FROM ozf_object_fund_summary
270 WHERE object_type = p_object_type
271 AND object_id = p_object_id
272 AND fund_id = p_fund_id;
273
274 l_recal_comm_amt NUMBER;
275 l_plan_curr_amount NUMBER := 0;
276 l_plan_curr_amount_remaining NUMBER := 0;
277 l_plan_currency VARCHAR2 (150);
278 l_univ_curr_amount NUMBER := 0;
279 l_rate NUMBER;
280 l_plan_id NUMBER;
281 l_liability_flag VARCHAR2(1);
282 l_offinvoice_gl_post_flag VARCHAR2(1);
283 l_tmp_id NUMBER;
284 l_offer_accrual_flag BOOLEAN;
285 l_gl_posted BOOLEAN;
286 l_orig_gl_flag VARCHAR2(1);
287 l_objfundsum_id NUMBER;
288 l_utilization_type VARCHAR2(20);
289 l_adjustment_type VARCHAR2(1);
290 l_skip_acct_gen_flag VARCHAR2(1);
291 x_event_id NUMBER;
292 l_ledger_id NUMBER;
293 l_ledger_name VARCHAR2(30);
294
295 l_order_gl_phase VARCHAR2(15);
296 l_gl_date DATE;
297 l_line_category_code VARCHAR2(30);
298 l_shipped_quantity NUMBER;
299 l_flow_status_code VARCHAR2(30);
300 l_invoice_status_code VARCHAR2(30);
301 l_invoiced_quantity NUMBER;
302 l_actual_shipment_date DATE;
303 l_order_number NUMBER;
304
305
306 CURSOR c_order_line (p_line_id IN NUMBER) IS
307 SELECT line_category_code, shipped_quantity, flow_status_code,
308 invoice_interface_status_code, invoiced_quantity, actual_shipment_date
309 FROM oe_order_lines_all
310 WHERE line_id = p_line_id;
311
312 CURSOR c_order_num (p_header_id IN NUMBER) IS
313 SELECT order_number
314 FROM oe_order_headers_all
315 WHERE header_id = p_header_id;
316
317 CURSOR c_invoice_date(p_line_id IN NUMBER, p_order_number IN VARCHAR2) IS
318 SELECT cust.trx_date -- transaction(invoice) date
319 FROM ra_customer_trx_all cust
320 ,ra_customer_trx_lines_all cust_lines
321 WHERE cust.customer_trx_id = cust_lines.customer_trx_id
322 AND cust_lines.sales_order = p_order_number
323 AND cust_lines.interface_line_attribute6 = TO_CHAR(p_line_id);
324
325 --Added for bug 7030415
326 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
327 SELECT exchange_rate_type
328 FROM ozf_sys_parameters_all
329 WHERE org_id = p_org_id;
330
331 -- 6657242
332 -- Cursor to get the org_id for order
333 CURSOR c_order_org_id (p_header_id IN NUMBER)IS
334 SELECT org_id FROM oe_order_headers_all
335 WHERE header_id = p_header_id;
336
337 -- Cursor to get the org_id for third party order
338 CURSOR c_tp_order_org_id (p_batch_id IN NUMBER)IS
339 SELECT org_id FROM ozf_resale_batches_all
340 WHERE resale_batch_id = p_batch_id;
341
342 -- Cursor to get the org_id for purchase order
343 CURSOR c_purchase_order_org_id (p_header_id IN NUMBER)IS
344 SELECT org_id FROM po_headers_all
345 WHERE po_header_id = p_header_id;
346
347 -- Cursor to get the org_id for invoice
348 CURSOR c_invoice_org_id (p_cust_trx_id IN NUMBER)IS
349 SELECT org_id FROM ar_payment_schedules_all
350 WHERE customer_trx_id = p_cust_trx_id;
351
352
353 -- Cursor to get the offer info
354 CURSOR c_offer_qlf_info (p_offer_id IN NUMBER) IS
355 SELECT qualifier_id,qualifier_type
356 FROM ozf_offers
357 WHERE qp_list_header_id = p_offer_id;
358
359 -- Cursor to get the org_id for cust acct
360 CURSOR c_cust_acct_org_id (p_site_use_id IN NUMBER) IS
361 SELECT org_id
362 FROM hz_cust_site_uses_all
363 WHERE site_use_id = p_site_use_id;
364
365
366 CURSOR c_plan_curr_amount (p_utilization_id IN NUMBER) IS
367 SELECT plan_curr_amount,plan_curr_amount_remaining
368 FROM ozf_funds_utilized_all_b
369 WHERE utilization_id = p_utilization_id;
370
371 CURSOR c_plan_curr_amount_remaining (p_utilization_id IN NUMBER) IS
372 SELECT plan_curr_amount_remaining
373 FROM ozf_funds_utilized_all_b
374 WHERE utilization_id = p_utilization_id;
375
376 l_org_id NUMBER;
377 l_qlf_type VARCHAR2 (30);
378 l_qlf_id NUMBER;
379 l_fund_reconc_msg VARCHAR2(4000);
380 l_act_bud_cst_msg VARCHAR2(4000);
381
382 BEGIN
383 --------------------- initialize -----------------------
384 SAVEPOINT create_utilization;
385 IF G_DEBUG THEN
386 ozf_utility_pvt.debug_message ( l_full_name
387 || ': start' || p_utilization_rec.utilization_type);
388 END IF;
389
390 IF fnd_api.to_boolean (p_init_msg_list) THEN
391 fnd_msg_pub.initialize;
392 END IF;
393
394 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
395 RAISE fnd_api.g_exc_unexpected_error;
396 END IF;
397
398 x_return_status := fnd_api.g_ret_sts_success;
399
400 ----------------------- validate -----------------------
401 IF G_DEBUG THEN
402 ozf_utility_pvt.debug_message (
403 l_full_name
404 || ': validate'
405 || l_utilization_rec.adjustment_type_id
406 );
407 END IF;
408
409 --Added for bug 7425189
410 l_fund_reconc_msg := fnd_message.get_string ('OZF', 'OZF_FUND_RECONCILE');
411 l_act_bud_cst_msg := fnd_message.get_string ('OZF', 'OZF_ACT_BUDG_CST_UTIL');
412
413 -- if the utlization type is adjustment then the adjustment type is mandatory
414 IF l_utilization_rec.utilization_type IN ('ADJUSTMENT', 'CHARGEBACK', 'LEAD_ADJUSTMENT') THEN -- yzhao: 11.5.10 added CHARGEBACK, LEAD_ADJUSTMENT
415 OPEN c_adj_type (l_utilization_rec.adjustment_type_id);
416 FETCH c_adj_type INTO l_utilization_rec.adjustment_type;
417 CLOSE c_adj_type;
418 END IF;
419
420 IF G_DEBUG THEN
421 ozf_utility_pvt.debug_message (
422 l_full_name
423 || ': adjustment_type: '
424 || l_utilization_rec.adjustment_type
425 );
426 END IF;
427 validate_utilization (
428 p_api_version=> l_api_version
429 ,p_init_msg_list=> fnd_api.g_false
430 ,p_validation_level=> p_validation_level
431 ,x_return_status=> l_return_status
432 ,x_msg_count=> x_msg_count
433 ,x_msg_data=> x_msg_data
434 ,p_utilization_rec=> l_utilization_rec
435 );
436
437 IF l_return_status = fnd_api.g_ret_sts_error THEN
438 RAISE fnd_api.g_exc_error;
439 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
440 RAISE fnd_api.g_exc_unexpected_error;
441 END IF;
442
443
444 -- Here we should check for adjustment_type , if the adjustment_type is DECREASE_COMMITTMENT or DECREASE_EARNED_COMMITTED
445 -- we go and create a debit record for that amount in Ozf_ACT_budgets
446 /* IF NVL (l_utilization_rec.adjustment_type, 'STANDARD') <> ('DECREASE_COMMITTED')
447 -- this is a temporary fix have to fix it from the UI
448 OR l_utilization_rec.utilization_type = 'TRANSFER'
449 --OR NVL (l_utilization_rec.recal_comm_flag, 'N') = 'Y'
450 THEN
451 */
452 -------------------------- insert --------------------------
453 IF G_DEBUG THEN
454 ozf_utility_pvt.debug_message ( l_full_name
455 || ': insert');
456 END IF;
457
458 /* IF l_utilization_rec.adjustment_type IN ('DECREASE_EARNED', 'DECREASE_COMM_EARNED') THEN
459 l_utilization_rec.amount := -l_utilization_rec.amount;
460 l_utilization_rec.amount_remaining := -l_utilization_rec.amount_remaining;
461 END IF;
462 */
463 IF l_utilization_rec.utilization_id IS NULL THEN
464 LOOP
465 -- Get the identifier
466 OPEN c_utilization_seq;
467 FETCH c_utilization_seq INTO l_utilization_rec.utilization_id;
468 CLOSE c_utilization_seq;
469 -- Check the uniqueness of the identifier
470 OPEN c_utilization_count (l_utilization_rec.utilization_id);
471 FETCH c_utilization_count INTO l_utilization_check;
472 -- Exit when the identifier uniqueness is established
473 EXIT WHEN c_utilization_count%ROWCOUNT = 0;
474 CLOSE c_utilization_count;
475 END LOOP;
476 END IF;
477
478 IF l_utilization_rec.amount = 0 THEN
479 l_utilization_rec.acctd_amount := 0;
480 ELSE
481
482 l_utilization_rec.amount := ozf_utility_pvt.currround(l_utilization_rec.amount , l_utilization_rec.currency_code); -- round amount to fix bug 3615680;
483 END IF;
484
485
486 IF G_DEBUG THEN
487 ozf_utility_pvt.debug_message ( l_api_name
488 || ': NP ozf_funds_utilized_pvt create_utilization org_id passed in is'
489 || l_utilization_rec.org_id);
490 END IF;
491 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
492 IF p_utilization_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST') THEN
493 */
494 --fix for bug 6657242
495
496 IF p_utilization_rec.utilization_type IN ('ADJUSTMENT')
497 AND p_utilization_rec.adjustment_type IS NOT NULL THEN
498
499 IF l_utilization_rec.plan_type IN ( 'OFFR') THEN
500 OPEN c_offer_qlf_info(l_utilization_rec.plan_id);
501 FETCH c_offer_qlf_info INTO l_qlf_id, l_qlf_type;
502 CLOSE c_offer_qlf_info;
503
504 IF l_qlf_type IN ('CUSTOMER_BILL_TO', 'SHIP_TO') THEN
505 OPEN c_cust_acct_org_id (l_qlf_id);
506 FETCH c_cust_acct_org_id INTO l_org_id;
507 CLOSE c_cust_acct_org_id;
508 l_utilization_rec.org_id := l_org_id;
509 END IF;
510 END IF;
511 IF G_DEBUG THEN
512 ozf_utility_pvt.debug_message('l_org_id '|| l_org_id );
513 ozf_utility_pvt.debug_message('p_utilization_rec.site_use_id '|| p_utilization_rec.site_use_id );
514 END IF;
515
516
517 IF p_utilization_rec.site_use_id IS NOT NULL THEN
518 OPEN c_cust_acct_org_id(l_utilization_rec.site_use_id);
519 FETCH c_cust_acct_org_id INTO l_org_id ;
520 CLOSE c_cust_acct_org_id ;
521
522 l_utilization_rec.org_id := l_org_id;
523 END IF;
524
525 IF l_utilization_rec.object_type='ORDER'
526 AND l_utilization_rec.object_id IS NOT NULL THEN
527
528 OPEN c_order_org_id( l_utilization_rec.object_id) ;
529 FETCH c_order_org_id INTO l_org_id ;
530 CLOSE c_order_org_id ;
531 l_utilization_rec.org_id := l_org_id;
532
533 ELSIF l_utilization_rec.object_type='TP_ORDER'
534 AND l_utilization_rec.object_id IS NOT NULL THEN
535
536 OPEN c_tp_order_org_id( l_utilization_rec.object_id) ;
537 FETCH c_tp_order_org_id INTO l_org_id ;
538 CLOSE c_tp_order_org_id ;
539 l_utilization_rec.org_id := l_org_id;
540
541 ELSIF l_utilization_rec.object_type='INVOICE'
542 AND l_utilization_rec.object_id IS NOT NULL THEN
543
544 OPEN c_invoice_org_id( l_utilization_rec.object_id) ;
545 FETCH c_invoice_org_id INTO l_org_id ;
546 CLOSE c_invoice_org_id ;
547 l_utilization_rec.org_id := l_org_id;
548
549 ELSIF l_utilization_rec.object_type='PCHO'
550 AND l_utilization_rec.object_id IS NOT NULL THEN
551
552 OPEN c_purchase_order_org_id( l_utilization_rec.object_id) ;
553 FETCH c_purchase_order_org_id INTO l_org_id ;
554 CLOSE c_purchase_order_org_id ;
555 l_utilization_rec.org_id := l_org_id;
556
557 END IF;
558
559 --fix for bug 6657242
560 END IF;
561 IF l_utilization_rec.org_id IS NULL THEN
562 -- R12 yzhao: org_id is required for ozf_funds_utilized_all. get offer's org_id, otherwise user's default org
563 ozf_utility_pvt.get_object_org_ledger(p_object_type => l_utilization_rec.plan_type
564 , p_object_id => l_utilization_rec.plan_id
565 , x_org_id => l_utilization_rec.org_id
566 , x_ledger_id => l_ledger_id
567 , x_return_status => l_return_status);
568
569 IF l_return_status = fnd_api.g_ret_sts_error THEN
570 RAISE fnd_api.g_exc_error;
571 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
572 RAISE fnd_api.g_exc_unexpected_error;
573 END IF;
574
575 ELSE
576 --kdass R12 bug 4635529
577 MO_UTILS.Get_Ledger_Info (
578 p_operating_unit => l_utilization_rec.org_id,
579 p_ledger_id => l_ledger_id,
580 p_ledger_name => l_ledger_name
581 );
582 END IF;
583 IF G_DEBUG THEN
584 ozf_utility_pvt.debug_message ( l_api_name
585 || ': create_utilization final org_id = '
586 || l_utilization_rec.org_id
587 || ' ledger_id='
588 || l_ledger_id);
589 END IF;
590
591 --Added for bug 7030415, get the conversion type based on utilizations org_id
592 ozf_utility_pvt.write_conc_log('NP l_utilization_rec.org_id'||l_utilization_rec.org_id);
593
594 OPEN c_get_conversion_type(l_utilization_rec.org_id);
595 FETCH c_get_conversion_type INTO l_utilization_rec.exchange_rate_type;
596 CLOSE c_get_conversion_type;
597
598 ozf_utility_pvt.write_conc_log('NP l_utilization_rec.exchange_rate_type'||l_utilization_rec.exchange_rate_type);
599
600
601 -- yzhao: R12 Oct 19 2005 No need to calculate functional currency if it is for marketing use
602 IF l_ledger_id IS NULL THEN
603 IF l_utilization_rec.plan_type IN ('CAMP', 'CSCH', 'EVEO', 'EVEH') AND
604 l_utilization_rec.component_type IN ('CAMP', 'CSCH', 'EVEO', 'EVEH') THEN
605 l_utilization_rec.org_id := -3116; -- marketing object is not org aware
606 ELSE
607 IF G_DEBUG THEN
608 ozf_utility_pvt.debug_message ( l_api_name
609 || ': create_utilization ledger not found for '
610 || l_utilization_rec.plan_type
611 || ' id('
612 || l_utilization_rec.plan_id);
613 END IF;
614 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
615 fnd_message.set_name ('OZF', 'OZF_NO_LEDGER_FOUND');
616 fnd_message.set_token('OBJECT_TYPE', l_utilization_rec.plan_type);
617 fnd_message.set_token('OBJECT_ID', l_utilization_rec.plan_id);
618 fnd_msg_pub.ADD;
619 END IF;
620 x_return_status := fnd_api.g_ret_sts_error;
621 RAISE fnd_api.g_exc_error;
622 END IF;
623 ELSE
624
625 --nirprasa for bug 7425189, adjustment_desc is populated with justification column
626 --of act_budgets recocrd.
627 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
628 AND l_utilization_rec.exchange_rate_date IS NOT NULL THEN
629 l_utilization_rec.exchange_rate_type := NULL;
630 ozf_utility_pvt.calculate_functional_currency (
631 p_from_amount => l_utilization_rec.amount
632 ,p_conv_date => l_utilization_rec.exchange_rate_date
633 ,p_tc_currency_code => l_utilization_rec.currency_code
634 ,p_ledger_id => l_ledger_id
635 ,x_to_amount => l_utilization_rec.acctd_amount
636 ,x_mrc_sob_type_code => l_sob_type_code
637 ,x_fc_currency_code => l_fc_code
638 ,x_exchange_rate_type => l_utilization_rec.exchange_rate_type --Added for bug 7030415
639 ,x_exchange_rate => l_utilization_rec.exchange_rate
640 ,x_return_status => l_return_status
641 );
642 ELSE
643 ozf_utility_pvt.calculate_functional_currency (
644 p_from_amount => l_utilization_rec.amount
645 ,p_tc_currency_code => l_utilization_rec.currency_code
646 ,p_ledger_id => l_ledger_id
647 ,x_to_amount => l_utilization_rec.acctd_amount
648 ,x_mrc_sob_type_code => l_sob_type_code
649 ,x_fc_currency_code => l_fc_code
650 ,x_exchange_rate_type => l_utilization_rec.exchange_rate_type --Added for bug 7030415
651 ,x_exchange_rate => l_utilization_rec.exchange_rate
652 ,x_return_status => l_return_status
653 );
654 END IF;
655
656
657
658 IF l_return_status = fnd_api.g_ret_sts_error THEN
659 RAISE fnd_api.g_exc_error;
660 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
661 RAISE fnd_api.g_exc_unexpected_error;
662 END IF;
663 END IF;
664
665 l_plan_currency :=
666 ozf_actbudgets_pvt.get_object_currency (
667 l_utilization_rec.plan_type
668 ,l_utilization_rec.plan_id
669 ,l_return_status
670 );
671
672 l_plan_curr_amount := 0;
673 l_univ_curr_amount := 0;
674 IF NVL (l_utilization_rec.amount, 0) <> 0 THEN
675 --Added for bug 7030415, This call will only be in case of utilization
676 --So use the utilization records exchange_rate_type.
677 --Added for bug 7425189,
678 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
679 AND l_utilization_rec.exchange_rate_date IS NOT NULL
680 AND l_utilization_rec.orig_utilization_id IS NOT NULL THEN
681
682 IF G_DEBUG THEN
683 ozf_utility_pvt.debug_message('l_utilization_rec.amount '|| l_utilization_rec.amount);
684 END IF;
685
686 ozf_utility_pvt.convert_currency (
687 p_from_currency=> l_utilization_rec.currency_code
688 ,p_to_currency=> l_plan_currency
689 ,p_conv_date=> l_utilization_rec.exchange_rate_date
690 ,p_from_amount=> l_utilization_rec.amount
691 ,x_return_status=> l_return_status
692 ,x_to_amount=> l_plan_curr_amount
693 ,x_rate=> l_rate
694 );
695 IF G_DEBUG THEN
696 ozf_utility_pvt.debug_message('NP l_plan_curr_amount 1 '||l_plan_curr_amount);
697 END IF;
698
699 OPEN c_plan_curr_amount(l_utilization_rec.orig_utilization_id);
700 FETCH c_plan_curr_amount INTO l_plan_curr_amount,l_plan_curr_amount_remaining;
701 CLOSE c_plan_curr_amount;
702
703 IF G_DEBUG THEN
704 ozf_utility_pvt.debug_message('NP l_plan_curr_amount 2 '||l_plan_curr_amount);
705 END IF;
706
707 IF l_plan_curr_amount_remaining < l_plan_curr_amount THEN
708 l_plan_curr_amount := nvl(l_plan_curr_amount_remaining,0);
709 END IF;
710
711 l_plan_curr_amount := nvl(-l_plan_curr_amount,0);
712 IF G_DEBUG THEN
713 ozf_utility_pvt.debug_message('l_plan_curr_amount 3 '||l_plan_curr_amount);
714 END IF;
715
716
717 ELSE
718 -- Skip conversion to avoid rounding of plan currency amounts.
719 IF p_utilization_rec.plan_curr_amount IS NULL
720 OR p_utilization_rec.plan_curr_amount = fnd_api.g_miss_num THEN
721 ozf_utility_pvt.convert_currency (
722 p_from_currency=> l_utilization_rec.currency_code
723 ,p_to_currency=> l_plan_currency
724 ,p_conv_type=> l_utilization_rec.exchange_rate_type
725 ,p_from_amount=> l_utilization_rec.amount
726 ,x_return_status=> l_return_status
727 ,x_to_amount=> l_plan_curr_amount
728 ,x_rate=> l_rate
729 );
730 ELSE
731 l_plan_curr_amount := p_utilization_rec.plan_curr_amount;
732 END IF;
733 END IF;
734
735 IF l_return_status = fnd_api.g_ret_sts_error THEN
736 RAISE fnd_api.g_exc_error;
737 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
738 RAISE fnd_api.g_exc_unexpected_error;
739 END IF;
740
741 -- R12 yzhao: convert universal currency
742 IF g_universal_currency = l_utilization_rec.currency_code THEN
743 l_univ_curr_amount := l_utilization_rec.amount;
744 ELSIF g_universal_currency = l_plan_currency THEN
745 l_univ_curr_amount := l_plan_curr_amount;
746 ELSE
747 --Added for bug 7425189,
748 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
749 AND l_utilization_rec.exchange_rate_date IS NOT NULL THEN
750 ozf_utility_pvt.convert_currency (
751 p_from_currency=> l_utilization_rec.currency_code
752 ,p_to_currency=> g_universal_currency
753 ,p_conv_date=> l_utilization_rec.exchange_rate_date
754 ,p_from_amount=> l_utilization_rec.amount
755 ,x_return_status=> l_return_status
756 ,x_to_amount=> l_univ_curr_amount
757 ,x_rate=> l_rate
758 );
759 ELSE
760 ozf_utility_pvt.convert_currency (
761 p_from_currency=> l_utilization_rec.currency_code
762 ,p_to_currency=> g_universal_currency
763 ,p_conv_type=> l_utilization_rec.exchange_rate_type
764 ,p_from_amount=> l_utilization_rec.amount
765 ,x_return_status=> l_return_status
766 ,x_to_amount=> l_univ_curr_amount
767 ,x_rate=> l_rate
768 );
769 END IF;
770
771
772 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
773 RAISE fnd_api.g_exc_unexpected_error;
774 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
775 RAISE fnd_api.g_exc_error;
776 END IF;
777 END IF;
778 END IF;
779
780 -- yzhao: 11/25/2003 11.5.10 populate adjustment_date and time_id
781 IF l_utilization_rec.adjustment_date IS NULL THEN
782 l_utilization_rec.adjustment_date := SYSDATE;
783 END IF;
784
785 /*fix for bug 4778995
786 OPEN c_get_time_id(l_utilization_rec.adjustment_date);
787 FETCH c_get_time_id INTO l_utilization_rec.month_id, l_utilization_rec.quarter_id, l_utilization_rec.year_id;
788 CLOSE c_get_time_id;
789 */
790
791
792 IF l_utilization_rec.component_type = 'OFFR' THEN
793 OPEN c_offer_info(l_utilization_rec.component_id);
794 FETCH c_offer_info INTO l_offer_type, l_volume_offer_type, l_custom_setup_id;
795 CLOSE c_offer_info;
796 END IF;
797
798
799 -- kdass bug 7835764 - added reference_id and reference_type to adjustments linked to Ship & Debit Requests
800 IF l_custom_setup_id = 118 THEN
801 OPEN c_sd_request_header_id(l_utilization_rec.component_id);
802 FETCH c_sd_request_header_id INTO l_utilization_rec.reference_id;
803 CLOSE c_sd_request_header_id;
804 l_utilization_rec.reference_type := 'SD_REQUEST';
805 END IF;
806
807 -- yzhao: 11.5.10 populate cust_account_id with offer's beneficiary account, otherwise billto cust account id
808 IF l_utilization_rec.cust_account_id IS NULL THEN
809 l_utilization_rec.cust_account_id := l_utilization_rec.billto_cust_account_id;
810 /* IF l_utilization_rec.component_type = 'OFFR' THEN
811 IF l_beneficiary_account_id IS NOT NULL THEN
812 l_utilization_rec.cust_account_id := l_beneficiary_account_id;
813 END IF;
814 END IF;
815 */
816 END IF;
817
818 -- add by feliu on 12/31/2003
819 IF l_utilization_rec.gl_date IS NULL THEN
820 IF l_utilization_rec.adjustment_date IS NULL THEN
821 l_utilization_rec.gl_date := SYSDATE;
822 ELSE
823 l_utilization_rec.gl_date := l_utilization_rec.adjustment_date;
824 END IF;
825 END IF;
826
827 OPEN c_fund_b (l_utilization_rec.fund_id);
828 FETCH c_fund_b INTO l_utilized_amt
829 ,l_earned_amt
830 ,l_obj_num
831 ,l_committed_amt
832 ,l_accrual_basis
833 ,l_fund_type
834 ,l_original_budget
835 ,l_paid_amt
836 ,l_plan_id
837 ,l_liability_flag
838 ,l_recal_comm_amt;
839 IF (c_fund_b%NOTFOUND) THEN
840 CLOSE c_fund_b;
841
842 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
843 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
844 fnd_msg_pub.ADD;
845 END IF;
846
847 RAISE fnd_api.g_exc_error;
848 END IF;
849 CLOSE c_fund_b;
850
851 IF l_fund_type = 'FULLY_ACCRUED' AND
852 l_plan_id IS NOT NULL AND l_plan_id <> FND_API.g_miss_num THEN
853 -- Bug # 7337263 fixed by ateotia (+)
854 -- Added a condition for Chargeback Batch using Fully Accrued budget.
855 --IF l_utilization_rec.component_type = 'OFFR' AND
856 IF l_utilization_rec.component_type IN ('OFFR','PRIC') AND
857 -- Bug # 7337263 fixed by ateotia (-)
858 l_plan_id <> l_utilization_rec.component_id THEN
859 l_fund_type := 'FIXED' ;
860 END IF;
861 END IF;
862
863 --l_utilization_rec.gl_posted_flag := NULL; --could have value for adjustment.
864 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
865 IF p_utilization_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST') THEN
866 */
867 ozf_funds_pvt.init_fund_rec (x_fund_rec => l_fund_rec);
868
869 -- R12: yzhao ozf_object_fund_summary
870 l_objfundsum_rec := NULL;
871 OPEN c_get_objfundsum_rec(l_utilization_rec.component_type
872 , l_utilization_rec.component_id
873 , l_utilization_rec.fund_id);
874 FETCH c_get_objfundsum_rec INTO l_objfundsum_rec.objfundsum_id
875 , l_objfundsum_rec.object_version_number
876 , l_objfundsum_rec.committed_amt
877 , l_objfundsum_rec.recal_committed_amt
878 , l_objfundsum_rec.utilized_amt
879 , l_objfundsum_rec.earned_amt
880 , l_objfundsum_rec.paid_amt
881 , l_objfundsum_rec.plan_curr_committed_amt
882 , l_objfundsum_rec.plan_curr_recal_committed_amt
883 , l_objfundsum_rec.plan_curr_utilized_amt
884 , l_objfundsum_rec.plan_curr_earned_amt
885 , l_objfundsum_rec.plan_curr_paid_amt
886 , l_objfundsum_rec.univ_curr_committed_amt
887 , l_objfundsum_rec.univ_curr_recal_committed_amt
888 , l_objfundsum_rec.univ_curr_utilized_amt
889 , l_objfundsum_rec.univ_curr_earned_amt
890 , l_objfundsum_rec.univ_curr_paid_amt;
891 CLOSE c_get_objfundsum_rec;
892 -- R12: yzhao END ozf_object_fund_summary
893
894 -- yzhao: 27-JUL-2005 - R12 paid adjustment can be done through public api
895 IF l_utilization_rec.utilization_type = 'ADJUSTMENT' AND
896 NVL(l_utilization_rec.adjustment_type, ' ') IN ('INCREASE_PAID', 'DECREASE_PAID') THEN
897 l_gl_posted := true;
898 ELSE
899 IF l_fund_type = 'FULLY_ACCRUED' THEN -- for fully accrual budget
900 -- for a fully accrued custoemr fund the budgeted and utilized column and committed column get populated
901 -- gl_posted_flag is 'N'
902 IF l_accrual_basis = 'CUSTOMER' AND NVL(l_liability_flag, 'N') = 'Y' THEN
903 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO; -- 'N', waiting for gl posting
904 l_gl_posted := true;
905 IF p_utilization_rec.utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT') AND
906 l_utilization_rec.orig_utilization_id IS NOT NULL THEN
907 -- fix bug 3348955 - gl posting to adjustment should be in sync with original order's utilization
908 l_orig_gl_flag := NULL;
909 OPEN c_get_orig_gl_flag(l_utilization_rec.orig_utilization_id);
910 FETCH c_get_orig_gl_flag INTO l_orig_gl_flag;
911 CLOSE c_get_orig_gl_flag;
912 IF l_orig_gl_flag = ozf_accrual_engine.G_GL_FLAG_NO THEN
913 -- do not post to gl now, wait for the original utilization's posting
914 l_gl_posted := false;
915 END IF;
916 END IF; -- IF orig_utilization_id IS NOT NULL
917
918 l_fund_rec.original_budget :=
919 ( NVL (l_original_budget, 0)
920 + NVL (l_utilization_rec.amount, 0)
921 );
922 l_fund_rec.utilized_amt :=
923 NVL (l_utilized_amt, 0)
924 + NVL (l_utilization_rec.amount, 0);
925
926 -- yzhao: 10/20/2003 committed column gets populated too
927 -- 11/06/2003 for manual adjustment, DECREASE_COMM_EARNED, java does not create transfer record for fully accrual budget any more
928 l_fund_rec.committed_amt := NVL (l_committed_amt, 0) + NVL (l_utilization_rec.amount, 0);
929 l_fund_rec.recal_committed := NVL (l_recal_comm_amt, 0) + NVL (l_utilization_rec.amount, 0);
930
931 -- R12: yzhao ozf_object_fund_summary update utilized/committed amt
932 l_objfundsum_rec.utilized_amt := NVL(l_objfundsum_rec.utilized_amt, 0) + NVL(l_utilization_rec.amount, 0);
933 l_objfundsum_rec.plan_curr_utilized_amt := NVL(l_objfundsum_rec.plan_curr_utilized_amt, 0)
934 + NVL(l_plan_curr_amount, 0);
935 l_objfundsum_rec.univ_curr_utilized_amt := NVL(l_objfundsum_rec.univ_curr_utilized_amt, 0)
936 + NVL(l_univ_curr_amount, 0);
937 l_objfundsum_rec.committed_amt := NVL(l_objfundsum_rec.committed_amt, 0) + NVL(l_utilization_rec.amount, 0);
938 l_objfundsum_rec.plan_curr_committed_amt := NVL(l_objfundsum_rec.plan_curr_committed_amt, 0)
939 + NVL(l_plan_curr_amount, 0);
940 l_objfundsum_rec.univ_curr_committed_amt := NVL(l_objfundsum_rec.univ_curr_committed_amt, 0)
941 + NVL(l_univ_curr_amount, 0);
942 l_objfundsum_rec.recal_committed_amt := NVL(l_objfundsum_rec.recal_committed_amt, 0)
943 + NVL(l_utilization_rec.amount, 0);
944 l_objfundsum_rec.plan_curr_recal_committed_amt := NVL(l_objfundsum_rec.plan_curr_recal_committed_amt, 0)
945 + NVL(l_plan_curr_amount, 0);
946 l_objfundsum_rec.univ_curr_recal_committed_amt := NVL(l_objfundsum_rec.univ_curr_recal_committed_amt, 0)
947 + NVL(l_univ_curr_amount, 0);
948 -- R12: yzhao END ozf_object_fund_summary update
949
950 -- 10/14/2003 update ozf_act_budgets REQUEST between fully accrual budget and its offer when accrual happens
951 OPEN c_accrual_budget_reqeust(l_utilization_rec.fund_id, l_plan_id);
952 FETCH c_accrual_budget_reqeust INTO l_act_budget_id, l_act_budget_objver;
953 IF (c_accrual_budget_reqeust%NOTFOUND) THEN
954 CLOSE c_accrual_budget_reqeust;
955 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
956 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
957 fnd_msg_pub.ADD;
958 END IF;
959 RAISE fnd_api.g_exc_error;
960 END IF;
961 CLOSE c_accrual_budget_reqeust;
962
963 UPDATE ozf_act_budgets
964 SET request_amount = NVL(request_amount, 0) + l_plan_curr_amount
965 , src_curr_request_amt = NVL(src_curr_request_amt, 0) + l_utilization_rec.amount
966 , approved_amount = NVL(approved_amount, 0) + l_plan_curr_amount
967 , approved_original_amount = NVL(approved_original_amount, 0) + l_utilization_rec.amount
968 , approved_amount_fc = NVL(approved_amount_fc, 0) + l_utilization_rec.acctd_amount
969 , last_update_date = sysdate
970 , last_updated_by = NVL (fnd_global.user_id, -1)
971 , last_update_login = NVL (fnd_global.conc_login_id, -1)
972 , object_version_number = l_act_budget_objver + 1
973 WHERE activity_budget_id = l_act_budget_id
974 AND object_version_number = l_act_budget_objver;
975
976 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
977 OPEN c_budget_request_utilrec(l_utilization_rec.fund_id, l_plan_id, l_act_budget_id);
978 FETCH c_budget_request_utilrec INTO l_act_budget_id, l_act_budget_objver;
979 IF (c_budget_request_utilrec%NOTFOUND) THEN
980 CLOSE c_budget_request_utilrec;
981 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
982 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
983 fnd_msg_pub.ADD;
984 END IF;
985 RAISE fnd_api.g_exc_error;
986 END IF;
987 CLOSE c_budget_request_utilrec;
988
989 -- populate request amount in ozf_funds_utilized_all_b record
990 UPDATE ozf_funds_utilized_all_b
991 SET amount = NVL(amount, 0) + NVL(l_utilization_rec.amount, 0)
992 , plan_curr_amount = NVL(plan_curr_amount, 0) + NVL(l_plan_curr_amount, 0)
993 , univ_curr_amount = NVL(univ_curr_amount, 0) + NVL(l_univ_curr_amount, 0)
994 , acctd_amount = NVL(acctd_amount, 0) + NVL(l_utilization_rec.acctd_amount, 0)
995 , last_update_date = sysdate
996 , last_updated_by = NVL (fnd_global.user_id, -1)
997 , last_update_login = NVL (fnd_global.conc_login_id, -1)
998 , object_version_number = l_act_budget_objver + 1
999 WHERE utilization_id = l_act_budget_id
1000 AND object_version_number = l_act_budget_objver;
1001 yzhao END: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
1002 */
1003 -- yzhao: 10/20/2003 END Fix TEVA bug - customer fully accrual budget committed amount is always 0
1004
1005 IF l_utilization_rec.orig_utilization_id IS NOT NULL THEN
1006 -- fix bug 3348955 - gl posting to adjustment should be in sync with original order's utilization
1007 OPEN c_get_orig_gl_flag(l_utilization_rec.orig_utilization_id);
1008 FETCH c_get_orig_gl_flag INTO l_orig_gl_flag;
1009 CLOSE c_get_orig_gl_flag;
1010 IF l_orig_gl_flag = ozf_accrual_engine.G_GL_FLAG_NO THEN
1011 -- do not post to gl now, wait for the original utilization's posting
1012 l_gl_posted := false;
1013 END IF;
1014 END IF; -- IF orig_utilization_id IS NOT NULL
1015
1016 /* yzhao: 10/20/2003 sales accrual and customer accrual with liability_flag off, only total column gets populated
1017 no gl posting
1018 ELSIF l_accrual_basis = 'SALES' THEN
1019 */
1020 ELSE
1021 IF l_utilization_rec.orig_utilization_id IS NOT NULL THEN
1022 -- fix bug 3348955 - gl posting to adjustment should be in sync with original order's utilization
1023 OPEN c_get_orig_gl_flag(l_utilization_rec.orig_utilization_id);
1024 FETCH c_get_orig_gl_flag INTO l_orig_gl_flag;
1025 CLOSE c_get_orig_gl_flag;
1026 END IF;
1027
1028 IF l_orig_gl_flag = ozf_accrual_engine.G_GL_FLAG_NO THEN
1029 -- do not post to gl now, wait for the original utilization's posting
1030 l_gl_posted := false;
1031 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO;
1032 l_utilization_rec.amount_remaining := NULL; -- no amount remaining
1033 ELSE
1034 l_utilization_rec.amount_remaining := NULL; -- no amount remaining
1035 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NOLIAB; -- 'X', do not post to gl
1036 l_fund_rec.original_budget :=
1037 ( NVL (l_original_budget, 0)
1038 + NVL (l_utilization_rec.amount, 0)
1039 );
1040 END IF; -- IF orig_utilization_id IS NOT NULL
1041 END IF;
1042
1043 ELSE -- for fixed budget
1044 l_fund_rec.utilized_amt := NVL (l_utilized_amt, 0) + NVL (l_utilization_rec.amount, 0);
1045 -- R12: yzhao ozf_object_fund_summary update utilized_amt
1046 l_objfundsum_rec.utilized_amt := NVL(l_objfundsum_rec.utilized_amt, 0) + NVL(l_utilization_rec.amount, 0);
1047 l_objfundsum_rec.plan_curr_utilized_amt := NVL(l_objfundsum_rec.plan_curr_utilized_amt, 0)
1048 + NVL(l_plan_curr_amount, 0);
1049 l_objfundsum_rec.univ_curr_utilized_amt := NVL(l_objfundsum_rec.univ_curr_utilized_amt, 0)
1050 + NVL(l_univ_curr_amount, 0);
1051 IF p_utilization_rec.utilization_type IN ('ACCRUAL', 'LEAD_ACCRUAL', 'CHARGEBACK') THEN
1052 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO; -- 'N', waiting for posting to gl
1053 l_gl_posted := true;
1054 ELSE -- p_utilization_rec.utilization_type IN ('UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
1055 IF p_utilization_rec.component_type <> 'OFFR' THEN
1056
1057 IF p_utilization_rec.component_type = 'PRIC' THEN
1058 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO; -- 'N', waiting for posting to gl
1059 l_utilization_rec.amount_remaining := l_utilization_rec.amount;
1060 l_gl_posted := true;
1061 ELSE
1062 -- no gl posting for marketing objects, increase earned and paid amount immediately
1063 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NULL; -- null
1064 l_utilization_rec.amount_remaining := NULL; -- no amount remaining for off-invoice utilization/adjustment
1065 l_fund_rec.earned_amt := NVL (l_earned_amt, 0) + NVL (l_utilization_rec.amount, 0);
1066 l_fund_rec.paid_amt := NVL (l_paid_amt, 0) + NVL (l_utilization_rec.amount, 0);
1067 -- R12: yzhao ozf_object_fund_summary update earned and paid amt
1068 l_objfundsum_rec.earned_amt := NVL(l_objfundsum_rec.earned_amt, 0) + NVL(l_utilization_rec.amount, 0);
1069 l_objfundsum_rec.plan_curr_earned_amt := NVL(l_objfundsum_rec.plan_curr_earned_amt, 0)
1070 + NVL(l_plan_curr_amount, 0);
1071 l_objfundsum_rec.univ_curr_earned_amt := NVL(l_objfundsum_rec.univ_curr_earned_amt, 0)
1072 + NVL(l_univ_curr_amount, 0);
1073 l_objfundsum_rec.paid_amt := NVL(l_objfundsum_rec.paid_amt, 0) + NVL(l_utilization_rec.amount, 0);
1074 l_objfundsum_rec.plan_curr_paid_amt := NVL(l_objfundsum_rec.plan_curr_paid_amt, 0)
1075 + NVL(l_plan_curr_amount, 0);
1076 l_objfundsum_rec.univ_curr_paid_amt := NVL(l_objfundsum_rec.univ_curr_paid_amt, 0)
1077 + NVL(l_univ_curr_amount, 0);
1078 -- R12: yzhao END ozf_object_fund_summary update
1079 END IF; -- end of PRIC type
1080
1081 ELSE -- offer utilization
1082 IF p_utilization_rec.utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT') THEN
1083 --always post to gl for offer's adjustment, regardless of accrual offer or off-invoice offer
1084 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO; -- 'N', waiting for posting to gl
1085 l_utilization_rec.amount_remaining := l_utilization_rec.amount;
1086 l_gl_posted := true;
1087 IF l_utilization_rec.orig_utilization_id IS NOT NULL THEN
1088 -- fix bug 3348955 - gl posting to adjustment should be in sync with original order's utilization
1089 l_orig_gl_flag := NULL;
1090 OPEN c_get_orig_gl_flag(l_utilization_rec.orig_utilization_id);
1091 FETCH c_get_orig_gl_flag INTO l_orig_gl_flag;
1092 CLOSE c_get_orig_gl_flag;
1093 IF l_orig_gl_flag = ozf_accrual_engine.G_GL_FLAG_NO THEN
1094 -- do not post to gl now, wait for the original utilization's posting
1095 l_gl_posted := false;
1096 END IF;
1097 IF l_utilization_rec.orig_utilization_id = -1 THEN -- for bug 6021635
1098 l_gl_posted := false;
1099 END IF;
1100 END IF; -- IF orig_utilization_id IS NOT NULL
1101 ELSE -- 'UTILIZED' for offer
1102 IF l_offer_type IN ('ACCRUAL', 'NET_ACCRUAL', 'LUMPSUM', 'SCAN_DATA') THEN
1103 -- handle case for reconcile when l_utilization_rec.gl_posted_flag passed.
1104 IF l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_YES OR
1105 l_utilization_rec.gl_posted_flag is NULL THEN
1106 l_offer_accrual_flag := true;
1107 END IF;
1108 ELSIF l_offer_type = 'VOLUME_OFFER' THEN
1109 IF l_volume_offer_type = 'OFF_INVOICE' THEN
1110 IF l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_NO OR
1111 l_utilization_rec.gl_posted_flag is NULL THEN
1112 l_offer_accrual_flag := false;
1113 END IF;
1114 ELSE
1115 IF l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_YES OR
1116 l_utilization_rec.gl_posted_flag is NULL THEN
1117 l_offer_accrual_flag := true;
1118 END IF;
1119 END IF;
1120 ELSIF l_offer_type = 'DEAL' THEN
1121 l_tmp_id := NULL;
1122 OPEN c_get_deal_accrual_flag(l_utilization_rec.component_id
1123 , l_utilization_rec.product_level_type, l_utilization_rec.product_id);
1124 FETCH c_get_deal_accrual_flag INTO l_tmp_id;
1125 CLOSE c_get_deal_accrual_flag;
1126 IF l_tmp_id = 1 THEN
1127 IF l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_YES OR
1128 l_utilization_rec.gl_posted_flag is NULL THEN
1129 l_offer_accrual_flag := true;
1130 END IF;
1131 ELSE
1132 IF l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_NO OR
1133 l_utilization_rec.gl_posted_flag is NULL THEN
1134 l_offer_accrual_flag := false;
1135 END IF;
1136 END IF;
1137 ELSE -- 'OFF_INVOICE', 'OID', 'ORDER', 'TERMS'
1138 IF l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_NO OR
1139 l_utilization_rec.gl_posted_flag is NULL THEN
1140 l_offer_accrual_flag := false;
1141 END IF;
1142 END IF;
1143
1144 IF l_offer_accrual_flag THEN
1145 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO; -- 'N', waiting for gl posting
1146 l_utilization_rec.amount_remaining := l_utilization_rec.amount;
1147 l_gl_posted := true;
1148 ELSIF l_offer_accrual_flag = false THEN
1149 IF p_utilization_rec.utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT') THEN
1150 l_utilization_rec.amount_remaining := l_utilization_rec.amount; -- wait for claim for adjustment
1151 ELSE -- 'UTILIZED'
1152 l_utilization_rec.amount_remaining := NULL; -- no amount remaining for off-invoice utilization
1153 END IF;
1154 OPEN c_offinvoice_gl_post_flag(l_utilization_rec.org_id);
1155 FETCH c_offinvoice_gl_post_flag INTO l_offinvoice_gl_post_flag;
1156 CLOSE c_offinvoice_gl_post_flag;
1157 IF (l_offinvoice_gl_post_flag = 'F') THEN
1158 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NULL; -- null
1159 l_fund_rec.earned_amt := NVL (l_earned_amt, 0) + NVL (l_utilization_rec.amount, 0);
1160 -- R12: yzhao ozf_object_fund_summary update earned and paid amt
1161 l_objfundsum_rec.earned_amt := NVL(l_objfundsum_rec.earned_amt, 0) + NVL(l_utilization_rec.amount, 0);
1162 l_objfundsum_rec.plan_curr_earned_amt := NVL(l_objfundsum_rec.plan_curr_earned_amt, 0)
1163 + NVL(l_plan_curr_amount, 0);
1164 l_objfundsum_rec.univ_curr_earned_amt := NVL(l_objfundsum_rec.univ_curr_earned_amt, 0)
1165 + NVL(l_univ_curr_amount, 0);
1166 -- R12: yzhao END ozf_object_fund_summary update
1167 -- yzhao: fix bug 3741127 do not update paid amount for off-invoice offer adjustment
1168 IF p_utilization_rec.utilization_type NOT IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT') THEN -- 'UTILIZED'
1169 l_fund_rec.paid_amt := NVL (l_paid_amt, 0) + NVL (l_utilization_rec.amount, 0);
1170 -- R12: yzhao
1171 l_objfundsum_rec.paid_amt := NVL(l_objfundsum_rec.paid_amt, 0) + NVL(l_utilization_rec.amount, 0);
1172 l_objfundsum_rec.plan_curr_paid_amt := NVL(l_objfundsum_rec.plan_curr_paid_amt, 0)
1173 + NVL(l_plan_curr_amount, 0);
1174 l_objfundsum_rec.univ_curr_paid_amt := NVL(l_objfundsum_rec.univ_curr_paid_amt, 0)
1175 + NVL(l_univ_curr_amount, 0);
1176 END IF;
1177 ELSE
1178 l_utilization_rec.gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_NO; -- 'N', waiting for posting to gl
1179 l_gl_posted := true;
1180 END IF;
1181 ELSE -- for reconcile when original gl_posted_flag = 'N' and 'F'
1182 l_gl_posted := false;
1183 END IF; -- l_offer_accrual_flag
1184 END IF; -- IF p_utilization_rec.utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT')
1185 END IF; -- IF p_utilization_rec.plan_type <> 'OFFR'
1186 END IF; -- IF p_utilization_rec.utilization_type IN ('ACCRUAL', 'LEAD_ACCRUAL', 'CHARGEBACK')
1187 END IF; -- IF l_fund_type = 'FULLY_ACCRUED'
1188 END IF; -- IF l_utilization_rec.utilization_type <> 'ADJUSTMENT' OR
1189 -- NVL(l_utilization_rec.adjustment_type, ' ') NOT IN ('INCREASE_PAID', 'DECREASE_PAID')
1190 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
1191 END IF; -- IF p_utilization_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST')
1192 */
1193
1194 /*kdass 07-FEB-2006 bug 5008257 - Post to GL for offer adjustment on order's adjustment.
1195 -> if profile 'Post to Gl' is set to 'shipped', post to GL after order is shipped.
1196 -> if profile 'Post to Gl' is set to 'invoiced', post to GL after order is invoiced.
1197 -> for returned order, post to gl only after order is invoiced.
1198 */
1199 IF l_gl_posted AND p_utilization_rec.utilization_type = 'ADJUSTMENT'
1200 AND p_utilization_rec.orig_utilization_id IS NULL AND p_utilization_rec.component_type = 'OFFR'
1201 AND p_utilization_rec.object_type = 'ORDER' AND p_utilization_rec.object_id IS NOT NULL
1202 AND p_utilization_rec.order_line_id IS NOT NULL
1203 THEN
1204
1205 l_gl_date := NULL;
1206 l_order_gl_phase := NVL(fnd_profile.VALUE ('OZF_ORDER_GLPOST_PHASE'), 'SHIPPED');
1207
1208 ozf_utility_pvt.write_conc_log('gl posting phase: ' || l_order_gl_phase);
1209
1210 OPEN c_order_line (l_utilization_rec.order_line_id);
1211 FETCH c_order_line INTO l_line_category_code, l_shipped_quantity, l_flow_status_code,
1212 l_invoice_status_code, l_invoiced_quantity, l_actual_shipment_date;
1213 CLOSE c_order_line;
1214
1215 IF (l_order_gl_phase = 'SHIPPED' AND l_line_category_code <> 'RETURN' AND
1216 NVL(l_shipped_quantity,0) <> 0 AND l_flow_status_code = 'SHIPPED') THEN
1217
1218 l_gl_date := l_actual_shipment_date;
1219 ozf_utility_pvt.write_conc_log('use actual shipment date for gl date');
1220 END IF;
1221
1222 IF l_gl_date IS NULL THEN
1223 IF (l_invoice_status_code = 'YES' OR NVL(l_invoiced_quantity,0) <> 0) THEN
1224
1225 -- get order_number
1226 OPEN c_order_num (l_utilization_rec.object_id);
1227 FETCH c_order_num INTO l_order_number;
1228 CLOSE c_order_num;
1229
1230 OPEN c_invoice_date(l_utilization_rec.order_line_id, l_order_number);
1231 FETCH c_invoice_date INTO l_gl_date;
1232 CLOSE c_invoice_date;
1233
1234 IF l_gl_date IS NULL THEN
1235 l_gl_date := sysdate;
1236 ozf_utility_pvt.write_conc_log('auto-invoice not complete. use sysdate for gl date');
1237 END IF;
1238 END IF;
1239 END IF;
1240
1241 IF l_gl_date IS NULL THEN
1242 l_gl_posted := FALSE;
1243 ozf_utility_pvt.write_conc_log('adjustment will not be posted to gl');
1244 ELSE
1245 l_utilization_rec.gl_date := l_gl_date;
1246 ozf_utility_pvt.write_conc_log('gl date: ' || l_gl_date);
1247 END IF;
1248
1249 END IF; --IF l_gl_posted AND p_utilization_rec.utilization_type = 'ADJUSTMENT' .....
1250
1251 l_utilization_rec.univ_curr_amount := l_univ_curr_amount;
1252 IF NVL(l_utilization_rec.amount_remaining, 0) = 0 THEN
1253 l_utilization_rec.acctd_amount_remaining := l_utilization_rec.amount_remaining;
1254 l_utilization_rec.univ_curr_amount_remaining := l_utilization_rec.amount_remaining;
1255 ELSE
1256 l_utilization_rec.amount_remaining := ozf_utility_pvt.currround(l_utilization_rec.amount_remaining
1257 , l_utilization_rec.currency_code);
1258 l_utilization_rec.acctd_amount_remaining :=
1259 l_utilization_rec.exchange_rate * l_utilization_rec.amount_remaining ;
1260
1261 --Added for bug 7425189
1262 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1263 AND l_utilization_rec.exchange_rate_date IS NOT NULL THEN
1264 ozf_utility_pvt.convert_currency (
1265 p_from_currency=> l_utilization_rec.currency_code
1266 ,p_to_currency=> l_plan_currency
1267 ,p_conv_date=> l_utilization_rec.exchange_rate_date
1268 ,p_from_amount=> l_utilization_rec.amount_remaining
1269 ,x_return_status=> l_return_status
1270 ,x_to_amount=> l_plan_curr_amount_remaining
1271 ,x_rate=> l_rate
1272 );
1273
1274
1275
1276 OPEN c_plan_curr_amount_remaining(l_utilization_rec.orig_utilization_id);
1277 FETCH c_plan_curr_amount_remaining INTO l_plan_curr_amount_remaining;
1278 CLOSE c_plan_curr_amount_remaining;
1279
1280 l_plan_curr_amount_remaining := nvl(-l_plan_curr_amount_remaining,0);
1281
1282 ELSE
1283
1284 IF p_utilization_rec.plan_curr_amount_remaining IS NULL
1285 OR p_utilization_rec.plan_curr_amount_remaining = fnd_api.g_miss_num THEN
1286 ozf_utility_pvt.convert_currency (
1287 p_from_currency=> l_utilization_rec.currency_code
1288 ,p_to_currency=> l_plan_currency
1289 ,p_conv_type=> l_utilization_rec.exchange_rate_type
1290 ,p_from_amount=> l_utilization_rec.amount_remaining
1291 ,x_return_status=> l_return_status
1292 ,x_to_amount=> l_plan_curr_amount_remaining
1293 ,x_rate=> l_rate
1294 );
1295 ELSE
1296 l_plan_curr_amount_remaining := p_utilization_rec.plan_curr_amount_remaining;
1297 END IF;
1298 END IF;
1299
1300 IF l_return_status = fnd_api.g_ret_sts_error THEN
1301 RAISE fnd_api.g_exc_error;
1302 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1303 RAISE fnd_api.g_exc_unexpected_error;
1304 END IF;
1305
1306 -- R12 yzhao: convert universal currency
1307 IF g_universal_currency = l_utilization_rec.currency_code THEN
1308 l_utilization_rec.univ_curr_amount_remaining := l_utilization_rec.amount_remaining;
1309 ELSIF g_universal_currency = l_plan_currency THEN
1310 l_utilization_rec.univ_curr_amount_remaining := l_plan_curr_amount_remaining;
1311 ELSE
1312 --Added for bug 7425189,
1313 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1314 AND l_utilization_rec.exchange_rate_date IS NOT NULL THEN
1315 ozf_utility_pvt.convert_currency (
1316 p_from_currency=> l_utilization_rec.currency_code
1317 ,p_to_currency=> g_universal_currency
1318 ,p_conv_date=> l_utilization_rec.exchange_rate_date
1319 ,p_from_amount=> l_utilization_rec.amount_remaining
1320 ,x_return_status=> l_return_status
1321 ,x_to_amount=> l_utilization_rec.univ_curr_amount_remaining
1322 ,x_rate=> l_rate
1323 );
1324 ELSE
1325 ozf_utility_pvt.convert_currency (
1326 p_from_currency=> l_utilization_rec.currency_code
1327 ,p_to_currency=> g_universal_currency
1328 ,p_conv_type=> l_utilization_rec.exchange_rate_type
1329 ,p_from_amount=> l_utilization_rec.amount_remaining
1330 ,x_return_status=> l_return_status
1331 ,x_to_amount=> l_utilization_rec.univ_curr_amount_remaining
1332 ,x_rate=> l_rate
1333 );
1334 END IF;
1335
1336 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1337 RAISE fnd_api.g_exc_unexpected_error;
1338 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1339 RAISE fnd_api.g_exc_error;
1340 END IF;
1341 END IF;
1342
1343 END IF;
1344
1345 /*--kdass 27-JUL-2005 - R12 change for paid adjustments
1346 IF l_utilization_rec.utilization_type = 'ADJUSTMENT' AND
1347 NVL(l_utilization_rec.adjustment_type, ' ') IN ('INCREASE_PAID', 'DECREASE_PAID') THEN
1348 l_utilization_rec.amount_remaining := - l_utilization_rec.amount_remaining;
1349 l_utilization_rec.acctd_amount_remaining := - l_utilization_rec.acctd_amount_remaining;
1350 l_utilization_rec.univ_curr_amount_remaining := - l_utilization_rec.univ_curr_amount_remaining;
1351 l_plan_curr_amount_remaining := - l_plan_curr_amount_remaining;
1352 l_utilization_rec.amount := 0;
1353 l_utilization_rec.acctd_amount := 0;
1354 l_plan_curr_amount := 0;
1355 l_utilization_rec.univ_curr_amount := 0;
1356 END IF;
1357 */
1358
1359 INSERT INTO ozf_funds_utilized_all_b
1360 (utilization_id
1361 ,last_update_date
1362 ,last_updated_by
1363 ,last_update_login
1364 ,creation_date
1365 ,created_by
1366 ,created_from
1367 ,request_id
1368 ,program_application_id
1369 ,program_id
1370 ,program_update_date
1371 ,utilization_type
1372 ,fund_id
1373 ,plan_type
1374 ,plan_id
1375 ,component_type
1376 ,component_id
1377 ,object_type
1378 ,object_id
1379 ,order_id
1380 ,invoice_id
1381 ,amount
1382 ,acctd_amount
1383 ,currency_code
1384 ,exchange_rate_type
1385 ,exchange_rate_date
1386 ,exchange_rate
1387 ,adjustment_type
1388 ,adjustment_date
1389 ,object_version_number
1390 ,attribute_category
1391 ,attribute1
1392 ,attribute2
1393 ,attribute3
1394 ,attribute4
1395 ,attribute5
1396 ,attribute6
1397 ,attribute7
1398 ,attribute8
1399 ,attribute9
1400 ,attribute10
1401 ,attribute11
1402 ,attribute12
1403 ,attribute13
1404 ,attribute14
1405 ,attribute15
1406 ,org_id
1407 ,adjustment_type_id
1408 ,camp_schedule_id
1409 ,gl_date
1410 ,product_level_type
1411 ,product_id
1412 ,ams_activity_budget_id
1413 ,amount_remaining
1414 ,acctd_amount_remaining
1415 ,cust_account_id
1416 ,price_adjustment_id
1417 ,plan_curr_amount
1418 ,plan_curr_amount_remaining
1419 ,scan_unit
1420 ,scan_unit_remaining
1421 ,activity_product_id
1422 ,volume_offer_tiers_id
1423 ,gl_posted_flag
1424 -- 11/04/2003 yzhao 11.5.10: added
1425 ,billto_cust_account_id
1426 ,reference_type
1427 ,reference_id
1428 /*fix for bug 4778995
1429 ,month_id
1430 ,quarter_id
1431 ,year_id
1432 */
1433 -- 01/02/2004 kdass added for 11.5.10
1434 ,order_line_id
1435 ,orig_utilization_id
1436 ,bill_to_site_use_id
1437 ,ship_to_site_use_id
1438 ,univ_curr_amount
1439 ,univ_curr_amount_remaining
1440 )
1441 VALUES (l_utilization_rec.utilization_id
1442 ,SYSDATE -- LAST_UPDATE_DATE
1443 ,NVL (fnd_global.user_id, -1) -- LAST_UPDATED_BY
1444 ,NVL (fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
1445 ,SYSDATE -- CREATION_DATE
1446 ,NVL (fnd_global.user_id, -1) -- CREATED_BY
1447 ,l_utilization_rec.created_from -- CREATED_FROM
1448 ,fnd_global.conc_request_id -- REQUEST_ID
1449 ,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
1450 ,fnd_global.conc_program_id -- PROGRAM_ID
1451 ,SYSDATE -- PROGRAM_UPDATE_DATE
1452 ,l_utilization_rec.utilization_type
1453 ,l_utilization_rec.fund_id
1454 ,l_utilization_rec.plan_type
1455 ,l_utilization_rec.plan_id
1456 ,l_utilization_rec.component_type
1457 ,l_utilization_rec.component_id
1458 ,l_utilization_rec.object_type
1459 ,l_utilization_rec.object_id
1460 ,l_utilization_rec.order_id
1461 ,l_utilization_rec.invoice_id
1462 ,l_utilization_rec.amount
1463 ,l_utilization_rec.acctd_amount
1464 ,l_utilization_rec.currency_code
1465 ,l_utilization_rec.exchange_rate_type
1466 ,NVL (l_utilization_rec.exchange_rate_date, SYSDATE)
1467 ,l_utilization_rec.exchange_rate
1468 ,l_utilization_rec.adjustment_type
1469 ,NVL(l_utilization_rec.adjustment_date,SYSDATE)
1470 ,l_object_version_number -- object_version_number
1471 ,l_utilization_rec.attribute_category
1472 ,l_utilization_rec.attribute1
1473 ,l_utilization_rec.attribute2
1474 ,l_utilization_rec.attribute3
1475 ,l_utilization_rec.attribute4
1476 ,l_utilization_rec.attribute5
1477 ,l_utilization_rec.attribute6
1478 ,l_utilization_rec.attribute7
1479 ,l_utilization_rec.attribute8
1480 ,l_utilization_rec.attribute9
1481 ,l_utilization_rec.attribute10
1482 ,l_utilization_rec.attribute11
1483 ,l_utilization_rec.attribute12
1484 ,l_utilization_rec.attribute13
1485 ,l_utilization_rec.attribute14
1486 ,l_utilization_rec.attribute15
1487 ,l_utilization_rec.org_id
1488 ,l_utilization_rec.adjustment_type_id
1489 ,l_utilization_rec.camp_schedule_id
1490 ,l_utilization_rec.gl_date
1491 ,l_utilization_rec.product_level_type
1492 ,l_utilization_rec.product_id
1493 ,l_utilization_rec.ams_activity_budget_id
1494 ,l_utilization_rec.amount_remaining
1495 ,l_utilization_rec.acctd_amount_remaining
1496 ,l_utilization_rec.cust_account_id
1497 ,l_utilization_rec.price_adjustment_id
1498 ,l_plan_curr_amount
1499 ,l_plan_curr_amount_remaining
1500 ,l_utilization_rec.scan_unit
1501 ,l_utilization_rec.scan_unit_remaining
1502 ,l_utilization_rec.activity_product_id
1503 ,l_utilization_rec.volume_offer_tiers_id
1504 ,l_utilization_rec.gl_posted_flag -- yzhao: 03/20/2003 added
1505 -- 11/04/2003 yzhao 11.5.10: added
1506 ,l_utilization_rec.billto_cust_account_id
1507 ,l_utilization_rec.reference_type
1508 ,l_utilization_rec.reference_id
1509 /*fix for bug 4778995
1510 ,l_utilization_rec.month_id
1511 ,l_utilization_rec.quarter_id
1512 ,l_utilization_rec.year_id
1513 */
1514 -- 01/02/2004 kdass added for 11.5.10
1515 ,l_utilization_rec.order_line_id
1516 ,l_utilization_rec.orig_utilization_id
1517 -- 06/15/2005 Ribha added for R12
1518 ,l_utilization_rec.bill_to_site_use_id
1519 ,l_utilization_rec.ship_to_site_use_id
1520 ,l_utilization_rec.univ_curr_amount
1521 ,l_utilization_rec.univ_curr_amount_remaining
1522 );
1523
1524 INSERT INTO ozf_funds_utilized_all_tl
1525 (utilization_id
1526 ,last_update_date
1527 ,last_updated_by
1528 ,last_update_login
1529 ,creation_date
1530 ,created_by
1531 ,created_from
1532 ,request_id
1533 ,program_application_id
1534 ,program_id
1535 ,program_update_date
1536 ,adjustment_desc
1537 ,source_lang
1538 ,language
1539 ,org_id
1540 )
1541 SELECT l_utilization_rec.utilization_id
1542 ,SYSDATE -- LAST_UPDATE_DATE
1543 ,NVL (fnd_global.user_id, -1) -- LAST_UPDATED_BY
1544 ,NVL (fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
1545 ,SYSDATE -- CREATION_DATE
1546 ,NVL (fnd_global.user_id, -1) -- CREATED_BY
1547 ,l_utilization_rec.created_from -- CREATED_FROM
1548 ,fnd_global.conc_request_id -- REQUEST_ID
1549 ,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
1550 ,fnd_global.conc_program_id -- PROGRAM_ID
1551 ,SYSDATE -- PROGRAM_UPDATE_DATE
1552 ,l_utilization_rec.adjustment_desc -- ADJUSTMENT_DESCRIPTION
1553 ,USERENV ('LANG') -- SOURCE_LANGUAGE
1554 ,l.language_code -- LANGUAGE
1555 ,l_utilization_rec.org_id -- fix for 3640740
1556 FROM fnd_languages l
1557 WHERE l.installed_flag IN ('I', 'B')
1558 AND NOT EXISTS ( SELECT NULL
1559 FROM ozf_funds_utilized_all_tl t
1560 WHERE t.utilization_id = l_utilization_rec.utilization_id
1561 AND t.language = l.language_code);
1562
1563
1564 x_utilization_id := l_utilization_rec.utilization_id;
1565
1566 IF G_DEBUG THEN
1567 ozf_utility_pvt.debug_message ( l_full_name || ': inserted:' || ': Utilization_id: ' || l_utilization_rec.utilization_id || ': amount:' || l_utilization_rec.amount);
1568 END IF;
1569
1570 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
1571 IF p_utilization_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST') THEN
1572 */
1573
1574 --------------------------complete fund update-------------------------
1575 -- post to GL immediately for manual earning adjustments, and utilized offer type with 'post to gl' profile as yes
1576 -- the accrual type adjustment is taken care of in ozfacreb.pls
1577 IF l_gl_posted AND l_utilization_rec.gl_posted_flag = ozf_accrual_engine.G_GL_FLAG_NO AND
1578 l_utilization_rec.plan_type IN ( 'OFFR' , 'PRIC') THEN
1579 /* yzhao: 11.5.10 11/17/2003 create gl entry for off-invoice discount when profile is on
1580 -- IF l_utilization_rec.utilization_type NOT IN ('REQUEST', 'TRANSFER', 'SALES_ACCRUAL','UTILIZED')
1581 IF l_utilization_rec.utilization_type NOT IN ('REQUEST', 'TRANSFER', 'SALES_ACCRUAL') THEN
1582 */
1583 IF l_utilization_rec.utilization_type <> 'SALES_ACCRUAL' THEN
1584 --kdass R12 accounting enhancement
1585 IF l_utilization_rec.utilization_type IN ('ACCRUAL', 'LEAD_ACCRUAL') THEN
1586 l_utilization_type := 'ACCRUAL';
1587 ELSIF l_utilization_rec.utilization_type = 'UTILIZED' THEN
1588 l_utilization_type := 'OFF_INVOICE';
1589 ELSE
1590 l_utilization_type := 'ACCRUAL_ADJUSTMENT';
1591 END IF;
1592
1593 IF NVL(l_utilization_rec.amount,0) >= 0 THEN
1594 l_adjustment_type := 'P'; -- positive
1595 ELSE
1596 l_adjustment_type := 'N'; -- negative adjustment
1597 END IF;
1598
1599 l_skip_acct_gen_flag := NVL(ozf_fund_utilized_pub.g_skip_acct_gen_flag, 'F');
1600
1601 IF G_DEBUG THEN
1602 ozf_utility_pvt.debug_message(l_full_name || ': l_skip_acct_gen_flag: ' || l_skip_acct_gen_flag);
1603 ozf_utility_pvt.debug_message(l_full_name || ': gl_account_credit: ' || l_utilization_rec.gl_account_credit);
1604 ozf_utility_pvt.debug_message(l_full_name || ': gl_account_debit: ' || l_utilization_rec.gl_account_debit);
1605 END IF;
1606
1607 ozf_gl_interface_pvt.Post_Accrual_To_GL(p_api_version => 1.0
1608 ,p_init_msg_list => fnd_api.g_false
1609 ,p_commit => fnd_api.g_false
1610 ,p_validation_level => fnd_api.g_valid_level_full
1611 ,x_return_status => l_return_status
1612 ,x_msg_data => x_msg_data
1613 ,x_msg_count => x_msg_count
1614 ,p_utilization_id => x_utilization_id
1615 ,p_utilization_type => l_utilization_type
1616 ,p_adjustment_type => l_adjustment_type
1617 ,p_dr_code_combination_id => l_utilization_rec.gl_account_credit
1618 ,p_cr_code_combination_id => l_utilization_rec.gl_account_debit
1619 ,p_skip_acct_gen_flag => l_skip_acct_gen_flag
1620 ,x_event_id => x_event_id
1621 );
1622
1623 IF G_DEBUG THEN
1624 ozf_utility_pvt.debug_message ( l_full_name || ': posted GL: ' || l_return_status);
1625 END IF;
1626
1627 -- yzhao: 03/27/2003 when gl posting succeeds, set the flag 'Y', otherwise 'F', ignore error and reprocess later
1628 IF l_return_status = fnd_api.g_ret_sts_success THEN
1629 l_gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_YES; -- 'Y';
1630 --kdass 27-JUL-2005 - R12 change for paid adjustments
1631 IF l_utilization_rec.utilization_type = 'ADJUSTMENT' AND
1632 l_utilization_rec.adjustment_type IN ('INCREASE_PAID', 'DECREASE_PAID') THEN
1633 l_fund_rec.paid_amt := NVL(l_paid_amt,0) - NVL (l_utilization_rec.amount_remaining, 0);
1634 l_fund_rec.utilized_amt := l_utilized_amt;
1635 l_fund_rec.earned_amt := l_earned_amt;
1636 l_fund_rec.committed_amt := l_committed_amt;
1637 l_fund_rec.original_budget := l_original_budget;
1638 l_fund_rec.recal_committed := l_recal_comm_amt;
1639 l_objfundsum_rec.paid_amt := NVL(l_objfundsum_rec.paid_amt, 0) + NVL(-l_utilization_rec.amount_remaining, 0);
1640 l_objfundsum_rec.plan_curr_paid_amt := NVL(l_objfundsum_rec.plan_curr_paid_amt, 0)
1641 + NVL(-l_plan_curr_amount_remaining, 0);
1642 l_objfundsum_rec.univ_curr_paid_amt := NVL(l_objfundsum_rec.univ_curr_paid_amt, 0)
1643 + NVL(-l_utilization_rec.univ_curr_amount_remaining, 0);
1644 ELSE
1645 -- yzhao: 11/14/2003 update budget earned amount when gl post success for accrual offers
1646 -- 02/24/2004 11.5.10 update budget earned and paid amount for utilization against off-invoice offers
1647 -- 07/02/2004 11.5.10 update budget earned amount for adjustment against off-invoice offers
1648 l_fund_rec.earned_amt := NVL (l_earned_amt, 0) + NVL (l_utilization_rec.amount, 0);
1649 -- R12: yzhao
1650 l_objfundsum_rec.earned_amt := NVL(l_objfundsum_rec.earned_amt, 0) + NVL(l_utilization_rec.amount, 0);
1651 l_objfundsum_rec.plan_curr_earned_amt := NVL(l_objfundsum_rec.plan_curr_earned_amt, 0)
1652 + NVL(l_plan_curr_amount, 0);
1653 l_objfundsum_rec.univ_curr_earned_amt := NVL(l_objfundsum_rec.univ_curr_earned_amt, 0)
1654 + NVL(l_univ_curr_amount, 0);
1655 IF l_utilization_rec.component_type = 'OFFR' AND NOT l_offer_accrual_flag AND
1656 -- yzhao: fix bug 3741127 do not update paid amount for off-invoice offer manual adjustment
1657 l_utilization_rec.utilization_type NOT IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT') THEN
1658 l_fund_rec.paid_amt := NVL (l_paid_amt, 0) + NVL (l_utilization_rec.amount, 0);
1659 l_objfundsum_rec.paid_amt := NVL(l_objfundsum_rec.paid_amt, 0) + NVL(l_utilization_rec.amount, 0);
1660 l_objfundsum_rec.plan_curr_paid_amt := NVL(l_objfundsum_rec.plan_curr_paid_amt, 0)
1661 + NVL(l_plan_curr_amount, 0);
1662 l_objfundsum_rec.univ_curr_paid_amt := NVL(l_objfundsum_rec.univ_curr_paid_amt, 0)
1663 + NVL(l_univ_curr_amount, 0);
1664 END IF;
1665 END IF;
1666 ELSE
1667 l_gl_posted_flag := ozf_accrual_engine.G_GL_FLAG_FAIL; -- 'F';
1668 -- yzhao: 11/20 raise exception if gl posting failed for manual adjust earned amount
1669 IF l_utilization_rec.utilization_type IN ('ADJUSTMENT', 'CHARGEBACK', 'LEAD_ADJUSTMENT') AND
1670 l_utilization_rec.adjustment_type IN ('STANDARD', 'DECREASE_EARNED', 'DECREASE_COMM_EARNED') THEN
1671 fnd_message.set_name ('OZF', 'OZF_GL_POST_FAILURE');
1672 fnd_msg_pub.ADD;
1673 RAISE fnd_api.g_exc_error;
1674 ELSE
1675 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1676 fnd_message.set_name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1677 fnd_message.set_token ('TEXT', 'Failed to post to GL for utilization id ' || l_utilization_rec.utilization_id);
1678 fnd_msg_pub.ADD;
1679 END IF;
1680 END IF;
1681 END IF;
1682 END IF; -- END IF
1683
1684 UPDATE ozf_funds_utilized_all_b
1685 SET last_update_date = SYSDATE
1686 , last_updated_by = NVL (fnd_global.user_id, -1)
1687 , last_update_login = NVL (fnd_global.conc_login_id, -1)
1688 , gl_posted_flag = l_gl_posted_flag
1689 --, gl_date = sysdate
1690 WHERE utilization_id = l_utilization_rec.utilization_id
1691 AND object_version_number = l_object_version_number;
1692
1693 END IF; -- for gl entry
1694
1695 l_fund_rec.fund_id := l_utilization_rec.fund_id;
1696 l_fund_rec.object_version_number := l_obj_num;
1697
1698 --nirprasa for bug 7425189, use these 2 columns to distinguish the
1699 -- reconcile flow in fund's API.
1700 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1701 AND l_utilization_rec.exchange_rate_date IS NOT NULL THEN
1702 l_fund_rec.exchange_rate_date := l_utilization_rec.exchange_rate_date;
1703 l_fund_rec.description := l_utilization_rec.adjustment_desc;
1704 END IF;
1705
1706 ozf_funds_pvt.update_fund (
1707 p_api_version=> l_api_version
1708 ,p_init_msg_list=> fnd_api.g_false
1709 ,p_commit=> fnd_api.g_false
1710 ,p_validation_level=> p_validation_level
1711 ,x_return_status=> l_return_status
1712 ,x_msg_count=> x_msg_count
1713 ,x_msg_data=> x_msg_data
1714 ,p_fund_rec=> l_fund_rec
1715 ,p_mode=> g_cons_fund_mode
1716 );
1717
1718 IF l_return_status = fnd_api.g_ret_sts_error THEN
1719 RAISE fnd_api.g_exc_error;
1720 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1721 RAISE fnd_api.g_exc_unexpected_error;
1722 END IF;
1723
1724 -- R12: yzhao ozf_object_fund_summary update amount
1725 IF l_objfundsum_rec.objfundsum_id IS NULL THEN
1726 l_objfundsum_rec.fund_id := l_utilization_rec.fund_id;
1727 l_objfundsum_rec.fund_currency := l_utilization_rec.currency_code;
1728 l_objfundsum_rec.object_type := l_utilization_rec.component_type;
1729 l_objfundsum_rec.object_id := l_utilization_rec.component_id;
1730 ozf_objfundsum_pvt.create_objfundsum(
1731 p_api_version => 1.0,
1732 p_init_msg_list => Fnd_Api.G_FALSE,
1733 p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
1734 p_objfundsum_rec => l_objfundsum_rec,
1735 x_return_status => l_return_status,
1736 x_msg_count => x_msg_count,
1737 x_msg_data => x_msg_data,
1738 x_objfundsum_id => l_objfundsum_id
1739 );
1740 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1741 RAISE fnd_api.g_exc_unexpected_error;
1742 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1743 RAISE fnd_api.g_exc_error;
1744 END IF;
1745 ELSE
1746 --Added for bug 7425189, Call update_reconcile_objfundsum for reconcile flow.
1747 IF l_utilization_rec.adjustment_desc IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1748 AND l_utilization_rec.exchange_rate_date IS NOT NULL THEN
1749 OZF_ACTBUDGETS_PVT.update_reconcile_objfundsum(
1750 p_api_version => 1.0,
1751 p_init_msg_list => Fnd_Api.G_FALSE,
1752 p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
1753 p_objfundsum_rec => l_objfundsum_rec,
1754 p_conv_date => l_utilization_rec.exchange_rate_date,
1755 x_return_status => l_return_status,
1756 x_msg_count => x_msg_count,
1757 x_msg_data => x_msg_data
1758 );
1759 ELSE
1760 ozf_objfundsum_pvt.update_objfundsum(
1761 p_api_version => 1.0,
1762 p_init_msg_list => Fnd_Api.G_FALSE,
1763 p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
1764 p_objfundsum_rec => l_objfundsum_rec,
1765 x_return_status => l_return_status,
1766 x_msg_count => x_msg_count,
1767 x_msg_data => x_msg_data
1768 );
1769 END IF;
1770
1771 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1772 RAISE fnd_api.g_exc_unexpected_error;
1773 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1774 RAISE fnd_api.g_exc_error;
1775 END IF;
1776 END IF;
1777 -- R12: yzhao END ozf_object_fund_summary update amount
1778
1779 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
1780 END IF; -- end IF p_utilization_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST') THEN
1781 */
1782
1783 -- raise business event for adjustment
1784 IF l_utilization_rec.utilization_type = 'ADJUSTMENT' THEN
1785 raise_business_event(p_object_id => l_utilization_rec.utilization_id );
1786 END IF;
1787
1788
1789 -- Check for commit
1790 IF fnd_api.to_boolean (p_commit) THEN
1791 COMMIT;
1792 END IF;
1793
1794 fnd_msg_pub.count_and_get (
1795 p_encoded=> fnd_api.g_false
1796 ,p_count=> x_msg_count
1797 ,p_data=> x_msg_data
1798 );
1799 IF G_DEBUG THEN
1800 ozf_utility_pvt.debug_message ( l_full_name
1801 || ': end');
1802 END IF;
1803 EXCEPTION
1804 WHEN fnd_api.g_exc_error THEN
1805 ROLLBACK TO create_utilization;
1806 x_return_status := fnd_api.g_ret_sts_error;
1807 fnd_msg_pub.count_and_get (
1808 p_encoded=> fnd_api.g_false
1809 ,p_count=> x_msg_count
1810 ,p_data=> x_msg_data
1811 );
1812 WHEN fnd_api.g_exc_unexpected_error THEN
1813 ROLLBACK TO create_utilization;
1814 x_return_status := fnd_api.g_ret_sts_unexp_error;
1815 fnd_msg_pub.count_and_get (
1816 p_encoded=> fnd_api.g_false
1817 ,p_count=> x_msg_count
1818 ,p_data=> x_msg_data
1819 );
1820 WHEN OTHERS THEN
1821 ROLLBACK TO create_utilization;
1822 x_return_status := fnd_api.g_ret_sts_unexp_error;
1823
1824 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1825 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1826 END IF;
1827
1828 fnd_msg_pub.count_and_get (
1829 p_encoded=> fnd_api.g_false
1830 ,p_count=> x_msg_count
1831 ,p_data=> x_msg_data
1832 );
1833 END create_utilization;
1834
1835
1836 ---------------------------------------------------------------
1837 -- PROCEDURE
1838 -- Delete_Utilization
1839 --
1840 -- HISTORY
1841 -- 04/25/2000 Mumu Pande Create.
1842 ---------------------------------------------------------------
1843 PROCEDURE delete_utilization (
1844 p_api_version IN NUMBER
1845 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1846 ,p_commit IN VARCHAR2 := fnd_api.g_false
1847 ,x_return_status OUT NOCOPY VARCHAR2
1848 ,x_msg_count OUT NOCOPY NUMBER
1849 ,x_msg_data OUT NOCOPY VARCHAR2
1850 ,p_utilization_id IN NUMBER
1851 ,p_object_version IN NUMBER
1852 ) IS
1853 l_api_version CONSTANT NUMBER := 1.0;
1854 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Utilization';
1855 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1856 || '.'
1857 || l_api_name;
1858 BEGIN
1859 --------------------- initialize -----------------------
1860 SAVEPOINT delete_utilization;
1861 IF G_DEBUG THEN
1862 ozf_utility_pvt.debug_message ( l_full_name
1863 || ': start');
1864 END IF;
1865
1866 IF fnd_api.to_boolean (p_init_msg_list) THEN
1867 fnd_msg_pub.initialize;
1868 END IF;
1869
1870 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1871 RAISE fnd_api.g_exc_unexpected_error;
1872 END IF;
1873
1874 x_return_status := fnd_api.g_ret_sts_success;
1875
1876 ------------------------ delete ------------------------
1877 IF G_DEBUG THEN
1878 ozf_utility_pvt.debug_message ( l_full_name
1879 || ': delete');
1880 END IF;
1881
1882 DELETE FROM ozf_funds_utilized_all_b
1883 WHERE utilization_id = p_utilization_id
1884 AND object_version_number = p_object_version;
1885
1886 IF (SQL%NOTFOUND) THEN
1887 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1888 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
1889 fnd_msg_pub.ADD;
1890 END IF;
1891
1892 RAISE fnd_api.g_exc_error;
1893 END IF;
1894
1895 DELETE FROM ozf_funds_utilized_all_tl
1896 WHERE utilization_id = p_utilization_id;
1897
1898 IF (SQL%NOTFOUND) THEN
1899 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1900 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
1901 fnd_msg_pub.ADD;
1902 END IF;
1903
1904 RAISE fnd_api.g_exc_error;
1905 END IF;
1906
1907
1908 -------------------- finish --------------------------
1909 IF fnd_api.to_boolean (p_commit) THEN
1910 COMMIT;
1911 END IF;
1912
1913 fnd_msg_pub.count_and_get (
1914 p_encoded=> fnd_api.g_false
1915 ,p_count=> x_msg_count
1916 ,p_data=> x_msg_data
1917 );
1918 IF G_DEBUG THEN
1919 ozf_utility_pvt.debug_message ( l_full_name
1920 || ': end');
1921 END IF;
1922 EXCEPTION
1923 WHEN fnd_api.g_exc_error THEN
1924 ROLLBACK TO delete_utilization;
1925 x_return_status := fnd_api.g_ret_sts_error;
1926 fnd_msg_pub.count_and_get (
1927 p_encoded=> fnd_api.g_false
1928 ,p_count=> x_msg_count
1929 ,p_data=> x_msg_data
1930 );
1931 WHEN fnd_api.g_exc_unexpected_error THEN
1932 ROLLBACK TO delete_utilization;
1933 x_return_status := fnd_api.g_ret_sts_unexp_error;
1934 fnd_msg_pub.count_and_get (
1935 p_encoded=> fnd_api.g_false
1936 ,p_count=> x_msg_count
1937 ,p_data=> x_msg_data
1938 );
1939 WHEN OTHERS THEN
1940 ROLLBACK TO delete_utilization;
1941 x_return_status := fnd_api.g_ret_sts_unexp_error;
1942
1943 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1944 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1945 END IF;
1946
1947 fnd_msg_pub.count_and_get (
1948 p_encoded=> fnd_api.g_false
1949 ,p_count=> x_msg_count
1950 ,p_data=> x_msg_data
1951 );
1952 END delete_utilization;
1953
1954
1955 -------------------------------------------------------------------
1956 -- PROCEDURE
1957 -- Lock_Utilization
1958 --
1959 -- HISTORY
1960 -- 04/25/2000 Mumu Pande Create.
1961 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
1962 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
1963 --------------------------------------------------------------------
1964 PROCEDURE lock_utilization (
1965 p_api_version IN NUMBER
1966 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1967 ,x_return_status OUT NOCOPY VARCHAR2
1968 ,x_msg_count OUT NOCOPY NUMBER
1969 ,x_msg_data OUT NOCOPY VARCHAR2
1970 ,p_utilization_id IN NUMBER
1971 ,p_object_version IN NUMBER
1972 ) IS
1973 l_api_version CONSTANT NUMBER := 1.0;
1974 l_api_name CONSTANT VARCHAR2 (30) := 'Lock_Utilization';
1975 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1976 || '.'
1977 || l_api_name;
1978 l_utilization_id NUMBER;
1979
1980 CURSOR c_utilization_b IS
1981 SELECT utilization_id
1982 FROM ozf_funds_utilized_all_b
1983 WHERE utilization_id = p_utilization_id
1984 AND object_version_number = p_object_version
1985 FOR UPDATE OF utilization_id NOWAIT;
1986
1987 CURSOR c_utilization_tl IS
1988 SELECT utilization_id
1989 FROM ozf_funds_utilized_all_tl
1990 WHERE utilization_id = p_utilization_id
1991 AND USERENV ('LANG') IN (language, source_lang)
1992 FOR UPDATE OF utilization_id NOWAIT;
1993 BEGIN
1994 -------------------- initialize ------------------------
1995 IF G_DEBUG THEN
1996 ozf_utility_pvt.debug_message ( l_full_name
1997 || ': start');
1998 END IF;
1999
2000 IF fnd_api.to_boolean (p_init_msg_list) THEN
2001 fnd_msg_pub.initialize;
2002 END IF;
2003
2004 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2005 RAISE fnd_api.g_exc_unexpected_error;
2006 END IF;
2007
2008 x_return_status := fnd_api.g_ret_sts_success;
2009
2010 ------------------------ lock -------------------------
2011 IF G_DEBUG THEN
2012 ozf_utility_pvt.debug_message ( l_full_name
2013 || ': lock');
2014 END IF;
2015 OPEN c_utilization_b;
2016 FETCH c_utilization_b INTO l_utilization_id;
2017
2018 IF (c_utilization_b%NOTFOUND) THEN
2019 CLOSE c_utilization_b;
2020
2021 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2022 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
2023 fnd_msg_pub.ADD;
2024 END IF;
2025
2026 RAISE fnd_api.g_exc_error;
2027 END IF;
2028
2029 CLOSE c_utilization_b;
2030 OPEN c_utilization_tl;
2031 CLOSE c_utilization_tl;
2032
2033 -------------------- finish --------------------------
2034 fnd_msg_pub.count_and_get (
2035 p_encoded=> fnd_api.g_false
2036 ,p_count=> x_msg_count
2037 ,p_data=> x_msg_data
2038 );
2039 IF G_DEBUG THEN
2040 ozf_utility_pvt.debug_message ( l_full_name
2041 || ': end');
2042 END IF;
2043 EXCEPTION
2044 WHEN ozf_utility_pvt.resource_locked THEN
2045 x_return_status := fnd_api.g_ret_sts_error;
2046
2047 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2048 fnd_message.set_name ('OZF', 'OZF_API_RESOURCE_LOCKED');
2049 fnd_msg_pub.ADD;
2050 END IF;
2051
2052 fnd_msg_pub.count_and_get (
2053 p_encoded=> fnd_api.g_false
2054 ,p_count=> x_msg_count
2055 ,p_data=> x_msg_data
2056 );
2057 WHEN fnd_api.g_exc_error THEN
2058 x_return_status := fnd_api.g_ret_sts_error;
2059 fnd_msg_pub.count_and_get (
2060 p_encoded=> fnd_api.g_false
2061 ,p_count=> x_msg_count
2062 ,p_data=> x_msg_data
2063 );
2064 WHEN fnd_api.g_exc_unexpected_error THEN
2065 x_return_status := fnd_api.g_ret_sts_unexp_error;
2066 fnd_msg_pub.count_and_get (
2067 p_encoded=> fnd_api.g_false
2068 ,p_count=> x_msg_count
2069 ,p_data=> x_msg_data
2070 );
2071 WHEN OTHERS THEN
2072 x_return_status := fnd_api.g_ret_sts_unexp_error;
2073
2074 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2075 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2076 END IF;
2077
2078 fnd_msg_pub.count_and_get (
2079 p_encoded=> fnd_api.g_false
2080 ,p_count=> x_msg_count
2081 ,p_data=> x_msg_data
2082 );
2083 END lock_utilization;
2084
2085
2086 ---------------------------------------------------------------------
2087 -- PROCEDURE
2088 -- Update_Utilization
2089 --
2090 -- HISTORY
2091 -- 04/25/2000 Mumu Pande Create.
2092 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2093 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2094 ----------------------------------------------------------------------
2095 PROCEDURE update_utilization (
2096 p_api_version IN NUMBER
2097 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2098 ,p_commit IN VARCHAR2 := fnd_api.g_false
2099 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2100 ,x_return_status OUT NOCOPY VARCHAR2
2101 ,x_msg_count OUT NOCOPY NUMBER
2102 ,x_msg_data OUT NOCOPY VARCHAR2
2103 ,p_utilization_rec IN utilization_rec_type
2104 ,p_mode IN VARCHAR2 := 'UPDATE'
2105 ) IS
2106 l_api_version CONSTANT NUMBER := 1.0;
2107 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Utilization';
2108 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
2109 || '.'
2110 || l_api_name;
2111 l_utilization_rec utilization_rec_type;
2112 l_mode VARCHAR2 (30);
2113 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
2114 l_obj_num NUMBER := 1;
2115 l_fund_rec ozf_funds_pvt.fund_rec_type;
2116 l_utilized_amt NUMBER;
2117 l_old_util_amt NUMBER;
2118 l_plan_curr_amount NUMBER;
2119 l_plan_curr_amount_remaining NUMBER;
2120 l_plan_currency VARCHAR2 (150);
2121 l_rate NUMBER;
2122 l_univ_curr_amount NUMBER;
2123 l_univ_curr_amount_remaining NUMBER;
2124
2125 CURSOR c_fund_b (p_fund_id IN NUMBER) IS
2126 SELECT utilized_amt
2127 ,object_version_number
2128 FROM ozf_funds_all_b
2129 WHERE fund_id = p_fund_id;
2130
2131 CURSOR c_old_util_amt (p_util_id IN NUMBER) IS
2132 SELECT amount
2133 FROM ozf_funds_utilized_all_b
2134 WHERE utilization_id = p_util_id;
2135 BEGIN
2136 -------------------- initialize -------------------------
2137 SAVEPOINT update_utilization;
2138 IF G_DEBUG THEN
2139 ozf_utility_pvt.debug_message ( l_full_name
2140 || ': start');
2141 END IF;
2142
2143 IF fnd_api.to_boolean (p_init_msg_list) THEN
2144 fnd_msg_pub.initialize;
2145 END IF;
2146
2147 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2148 RAISE fnd_api.g_exc_unexpected_error;
2149 END IF;
2150
2151 x_return_status := fnd_api.g_ret_sts_success;
2152
2153 ----------------------- validate ----------------------
2154 IF G_DEBUG THEN
2155 ozf_utility_pvt.debug_message ( l_full_name
2156 || ': validate');
2157 END IF;
2158 -- replace g_miss_char/num/date with current column values
2159 complete_utilization_rec (p_utilization_rec, l_utilization_rec);
2160
2161 IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
2162 check_utilization_items (
2163 p_utilization_rec=> p_utilization_rec
2164 ,p_validation_mode=> jtf_plsql_api.g_update
2165 ,x_return_status=> l_return_status
2166 );
2167
2168 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2169 RAISE fnd_api.g_exc_unexpected_error;
2170 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2171 RAISE fnd_api.g_exc_error;
2172 END IF;
2173 END IF;
2174
2175
2176 IF p_validation_level >= jtf_plsql_api.g_valid_level_record THEN
2177 check_utilization_record (
2178 p_utilization_rec=> p_utilization_rec
2179 ,p_complete_rec=> l_utilization_rec
2180 ,p_mode=> p_mode
2181 ,x_return_status=> l_return_status
2182 );
2183
2184 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2185 RAISE fnd_api.g_exc_unexpected_error;
2186 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2187 RAISE fnd_api.g_exc_error;
2188 END IF;
2189 END IF;
2190
2191
2192 -------------------------- update --------------------
2193 IF G_DEBUG THEN
2194 ozf_utility_pvt.debug_message ( l_full_name
2195 || ': update');
2196 END IF;
2197 l_plan_currency :=
2198 ozf_actbudgets_pvt.get_object_currency (
2199 l_utilization_rec.plan_type
2200 ,l_utilization_rec.plan_id
2201 ,l_return_status
2202 );
2203
2204 IF NVL (l_utilization_rec.amount, 0) <> 0 THEN
2205 ozf_utility_pvt.convert_currency (
2206 p_from_currency=> l_utilization_rec.currency_code
2207 ,p_to_currency=> l_plan_currency
2208 ,p_from_amount=> l_utilization_rec.amount
2209 ,x_return_status=> l_return_status
2210 ,x_to_amount=> l_plan_curr_amount
2211 ,x_rate=> l_rate
2212 );
2213
2214 IF l_return_status = fnd_api.g_ret_sts_error THEN
2215 RAISE fnd_api.g_exc_error;
2216 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2217 RAISE fnd_api.g_exc_unexpected_error;
2218 END IF;
2219 END IF;
2220
2221 IF NVL (l_utilization_rec.amount_remaining, 0) <> 0 THEN
2222 ozf_utility_pvt.convert_currency (
2223 p_from_currency=> l_utilization_rec.currency_code
2224 ,p_to_currency=> l_plan_currency
2225 ,p_from_amount=> l_utilization_rec.amount_remaining
2226 ,x_return_status=> l_return_status
2227 ,x_to_amount=> l_plan_curr_amount_remaining
2228 ,x_rate=> l_rate
2229 );
2230
2231 IF l_return_status = fnd_api.g_ret_sts_error THEN
2232 RAISE fnd_api.g_exc_error;
2233 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2234 RAISE fnd_api.g_exc_unexpected_error;
2235 END IF;
2236 END IF;
2237
2238
2239 -- R12 yzhao: convert universal currency
2240 IF g_universal_currency = l_utilization_rec.currency_code THEN
2241 l_univ_curr_amount := l_utilization_rec.amount;
2242 l_univ_curr_amount_remaining := l_utilization_rec.amount_remaining;
2243 ELSIF g_universal_currency = l_plan_currency THEN
2244 l_univ_curr_amount := l_plan_curr_amount;
2245 l_univ_curr_amount_remaining := l_plan_curr_amount_remaining;
2246 ELSE
2247 IF NVL (l_utilization_rec.amount, 0) = 0 THEN
2248 l_univ_curr_amount := 0;
2249 ELSE
2250 ozf_utility_pvt.convert_currency (
2251 p_from_currency=> l_utilization_rec.currency_code
2252 ,p_to_currency=> g_universal_currency
2253 ,p_from_amount=> l_utilization_rec.amount
2254 ,x_return_status=> l_return_status
2255 ,x_to_amount=> l_univ_curr_amount
2256 ,x_rate=> l_rate
2257 );
2258 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2259 RAISE fnd_api.g_exc_unexpected_error;
2260 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2261 RAISE fnd_api.g_exc_error;
2262 END IF;
2263 END IF;
2264
2265 IF NVL (l_utilization_rec.amount_remaining, 0) = 0 THEN
2266 l_univ_curr_amount_remaining := 0;
2267 ELSE
2268 ozf_utility_pvt.convert_currency (
2269 p_from_currency=> l_utilization_rec.currency_code
2270 ,p_to_currency=> g_universal_currency
2271 ,p_from_amount=> l_utilization_rec.amount_remaining
2272 ,x_return_status=> l_return_status
2273 ,x_to_amount=> l_univ_curr_amount_remaining
2274 ,x_rate=> l_rate
2275 );
2276
2277 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2278 RAISE fnd_api.g_exc_unexpected_error;
2279 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2280 RAISE fnd_api.g_exc_error;
2281 END IF;
2282 END IF;
2283 END IF;
2284
2285 UPDATE ozf_funds_utilized_all_b
2286 SET last_update_date = SYSDATE
2287 ,last_updated_by = NVL (fnd_global.user_id, -1)
2288 ,last_update_login = NVL (fnd_global.conc_login_id, -1)
2289 ,created_from = l_utilization_rec.created_from
2290 ,request_id = fnd_global.conc_request_id
2291 ,program_application_id = fnd_global.prog_appl_id
2292 ,program_id = fnd_global.conc_program_id
2293 ,program_update_date = SYSDATE
2294 ,utilization_type = l_utilization_rec.utilization_type
2295 ,fund_id = l_utilization_rec.fund_id
2296 ,plan_type = l_utilization_rec.plan_type
2297 ,plan_id = l_utilization_rec.plan_id
2298 ,component_type = l_utilization_rec.component_type
2299 ,component_id = l_utilization_rec.component_id
2300 ,object_type = l_utilization_rec.object_type
2301 ,object_id = l_utilization_rec.object_id
2302 ,order_id = l_utilization_rec.order_id
2303 ,invoice_id = l_utilization_rec.invoice_id
2304 ,amount = l_utilization_rec.amount
2305 ,acctd_amount = l_utilization_rec.acctd_amount
2306 ,currency_code = l_utilization_rec.currency_code
2307 ,exchange_rate_type = l_utilization_rec.exchange_rate_type
2308 ,exchange_rate_date = l_utilization_rec.exchange_rate_date
2309 ,exchange_rate = l_utilization_rec.exchange_rate
2310 ,adjustment_type = l_utilization_rec.adjustment_type
2311 ,adjustment_date = l_utilization_rec.adjustment_date
2312 ,object_version_number = l_utilization_rec.object_version_number
2313 + 1
2314 ,attribute_category = l_utilization_rec.attribute_category
2315 ,attribute1 = l_utilization_rec.attribute1
2316 ,attribute2 = l_utilization_rec.attribute2
2317 ,attribute3 = l_utilization_rec.attribute3
2318 ,attribute4 = l_utilization_rec.attribute4
2319 ,attribute5 = l_utilization_rec.attribute5
2320 ,attribute6 = l_utilization_rec.attribute6
2321 ,attribute7 = l_utilization_rec.attribute7
2322 ,attribute8 = l_utilization_rec.attribute8
2323 ,attribute9 = l_utilization_rec.attribute9
2324 ,attribute10 = l_utilization_rec.attribute10
2325 ,attribute11 = l_utilization_rec.attribute11
2326 ,attribute12 = l_utilization_rec.attribute12
2327 ,attribute13 = l_utilization_rec.attribute13
2328 ,attribute14 = l_utilization_rec.attribute14
2329 ,attribute15 = l_utilization_rec.attribute15
2330 ,adjustment_type_id = l_utilization_rec.adjustment_type_id
2331 ,camp_schedule_id = l_utilization_rec.camp_schedule_id
2332 ,gl_date = l_utilization_rec.gl_date
2333 ,product_level_type = l_utilization_rec.product_level_type
2334 ,product_id = l_utilization_rec.product_id
2335 ,ams_activity_budget_id = l_utilization_rec.ams_activity_budget_id
2336 ,amount_remaining = l_utilization_rec.amount_remaining
2337 ,acctd_amount_remaining = l_utilization_rec.acctd_amount_remaining
2338 ,cust_account_id = l_utilization_rec.cust_account_id
2339 ,price_adjustment_id = l_utilization_rec.price_adjustment_id
2340 ,plan_curr_amount = l_plan_curr_amount
2341 ,plan_curr_amount_remaining = l_plan_curr_amount_remaining
2342 ,scan_unit = l_utilization_rec.scan_unit
2343 ,scan_unit_remaining = l_utilization_rec.scan_unit_remaining
2344 ,activity_product_id = l_utilization_rec.activity_product_id
2345 ,gl_posted_flag = l_utilization_rec.gl_posted_flag -- yzhao: 03/20/2003 added
2346 -- 11/04/2003 yzhao 11.5.10: added
2347 ,billto_cust_account_id = l_utilization_rec.billto_cust_account_id
2348 ,reference_type = l_utilization_rec.reference_type
2349 ,reference_id = l_utilization_rec.reference_id
2350 /*fix for bug 4778995
2351 ,month_id = l_utilization_rec.month_id
2352 ,quarter_id = l_utilization_rec.quarter_id
2353 ,year_id = l_utilization_rec.year_id
2354 */
2355 -- R12 yzhao added universal currency
2356 ,bill_to_site_use_id = l_utilization_rec.bill_to_site_use_id
2357 ,ship_to_site_use_id = l_utilization_rec.ship_to_site_use_id
2358 ,univ_curr_amount = l_univ_curr_amount
2359 ,univ_curr_amount_remaining = l_univ_curr_amount_remaining
2360 WHERE utilization_id = l_utilization_rec.utilization_id
2361 AND object_version_number = l_utilization_rec.object_version_number;
2362
2363 IF (SQL%NOTFOUND) THEN
2364 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2365 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
2366 fnd_msg_pub.ADD;
2367 END IF;
2368
2369 RAISE fnd_api.g_exc_error;
2370 END IF;
2371
2372 UPDATE ozf_funds_utilized_all_tl
2373 SET last_update_date = SYSDATE
2374 ,last_updated_by = NVL (fnd_global.user_id, -1)
2375 ,last_update_login = NVL (fnd_global.conc_login_id, -1)
2376 ,created_from = l_utilization_rec.created_from
2377 ,request_id = fnd_global.conc_request_id
2378 ,program_application_id = fnd_global.prog_appl_id
2379 ,program_id = fnd_global.conc_program_id
2380 ,program_update_date = SYSDATE
2381 ,adjustment_desc = l_utilization_rec.adjustment_desc
2382 ,source_lang = USERENV ('LANG')
2383 WHERE utilization_id = l_utilization_rec.utilization_id
2384 AND USERENV ('LANG') IN (language, source_lang);
2385
2386 IF (SQL%NOTFOUND) THEN
2387 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2388 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
2389 fnd_msg_pub.ADD;
2390 END IF;
2391
2392 RAISE fnd_api.g_exc_error;
2393 END IF;
2394
2395 OPEN c_fund_b (l_utilization_rec.fund_id);
2396 FETCH c_fund_b INTO l_utilized_amt, l_obj_num;
2397
2398 IF (c_fund_b%NOTFOUND) THEN
2399 CLOSE c_fund_b;
2400
2401 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2402 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
2403 fnd_msg_pub.ADD;
2404 END IF;
2405
2406 RAISE fnd_api.g_exc_error;
2407 END IF;
2408
2409 CLOSE c_fund_b;
2410 OPEN c_old_util_amt (l_utilization_rec.utilization_id);
2411 FETCH c_old_util_amt INTO l_old_util_amt;
2412 CLOSE c_old_util_amt;
2413 -------------------------update master fund table-------------------
2414 ozf_funds_pvt.init_fund_rec (x_fund_rec => l_fund_rec);
2415 -- new utilzed amount = fund_utilized_amount - old_util_Amount + new util amount
2416 l_fund_rec.utilized_amt :=
2417 NVL (l_utilized_amt, 0)
2418 - NVL (l_old_util_amt, 0)
2419 + NVL (l_utilization_rec.amount, 0);
2420 l_fund_rec.fund_id := l_utilization_rec.fund_id;
2421 l_fund_rec.object_version_number := l_obj_num;
2422 ozf_funds_pvt.update_fund (
2423 p_api_version=> l_api_version
2424 ,p_init_msg_list=> fnd_api.g_false
2425 ,p_commit=> fnd_api.g_false
2426 ,p_validation_level=> p_validation_level
2427 ,x_return_status=> l_return_status
2428 ,x_msg_count=> x_msg_count
2429 ,x_msg_data=> x_msg_data
2430 ,p_fund_rec=> l_fund_rec
2431 ,p_mode=> g_cons_fund_mode
2432 );
2433
2434 IF l_return_status = fnd_api.g_ret_sts_error THEN
2435 RAISE fnd_api.g_exc_error;
2436 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2437 RAISE fnd_api.g_exc_unexpected_error;
2438 END IF;
2439
2440
2441 -------------------- finish --------------------------
2442
2443 IF fnd_api.to_boolean (p_commit) THEN
2444 COMMIT;
2445 END IF;
2446
2447 fnd_msg_pub.count_and_get (
2448 p_encoded=> fnd_api.g_false
2449 ,p_count=> x_msg_count
2450 ,p_data=> x_msg_data
2451 );
2452 IF G_DEBUG THEN
2453 ozf_utility_pvt.debug_message ( l_full_name
2454 || ': end');
2455 END IF;
2456 EXCEPTION
2457 WHEN fnd_api.g_exc_error THEN
2458 ROLLBACK TO update_utilization;
2459 x_return_status := fnd_api.g_ret_sts_error;
2460 fnd_msg_pub.count_and_get (
2461 p_encoded=> fnd_api.g_false
2462 ,p_count=> x_msg_count
2463 ,p_data=> x_msg_data
2464 );
2465 WHEN fnd_api.g_exc_unexpected_error THEN
2466 ROLLBACK TO update_utilization;
2467 x_return_status := fnd_api.g_ret_sts_unexp_error;
2468 fnd_msg_pub.count_and_get (
2469 p_encoded=> fnd_api.g_false
2470 ,p_count=> x_msg_count
2471 ,p_data=> x_msg_data
2472 );
2473 WHEN OTHERS THEN
2474 ROLLBACK TO update_utilization;
2475 x_return_status := fnd_api.g_ret_sts_unexp_error;
2476
2477 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2478 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2479 END IF;
2480
2481 fnd_msg_pub.count_and_get (
2482 p_encoded=> fnd_api.g_false
2483 ,p_count=> x_msg_count
2484 ,p_data=> x_msg_data
2485 );
2486 END update_utilization;
2487
2488
2489 --------------------------------------------------------------------
2490 -- PROCEDURE
2491 -- Validate_Utilization
2492 --
2493 -- HISTORY
2494 -- 04/25/2000 Mumu Pande Create.
2495 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2496 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2497 --------------------------------------------------------------------
2498 PROCEDURE validate_utilization (
2499 p_api_version IN NUMBER
2500 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2501 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2502 ,x_return_status OUT NOCOPY VARCHAR2
2503 ,x_msg_count OUT NOCOPY NUMBER
2504 ,x_msg_data OUT NOCOPY VARCHAR2
2505 ,p_utilization_rec IN utilization_rec_type
2506 ) IS
2507 l_api_version CONSTANT NUMBER := 1.0;
2508 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Utilization';
2509 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
2510 || '.'
2511 || l_api_name;
2512 l_return_status VARCHAR2 (1);
2513 BEGIN
2514 ----------------------- initialize --------------------
2515 IF G_DEBUG THEN
2516 ozf_utility_pvt.debug_message ( l_full_name
2517 || ': start');
2518 END IF;
2519
2520 IF fnd_api.to_boolean (p_init_msg_list) THEN
2521 fnd_msg_pub.initialize;
2522 END IF;
2523
2524 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2525 RAISE fnd_api.g_exc_unexpected_error;
2526 END IF;
2527
2528 x_return_status := fnd_api.g_ret_sts_success;
2529
2530 ---------------------- validate ------------------------
2531 IF G_DEBUG THEN
2532 ozf_utility_pvt.debug_message ( l_full_name
2533 || ': check items');
2534 END IF;
2535
2536 IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
2537 check_utilization_items (
2538 p_utilization_rec=> p_utilization_rec
2539 ,p_validation_mode=> jtf_plsql_api.g_create
2540 ,x_return_status=> l_return_status
2541 );
2542
2543 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2544 RAISE fnd_api.g_exc_unexpected_error;
2545 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2546 RAISE fnd_api.g_exc_error;
2547 END IF;
2548 END IF;
2549
2550 IF G_DEBUG THEN
2551 ozf_utility_pvt.debug_message ( l_full_name
2552 || ': check record');
2553 END IF;
2554
2555 IF p_validation_level >= jtf_plsql_api.g_valid_level_record THEN
2556 check_utilization_record (
2557 p_utilization_rec=> p_utilization_rec
2558 ,p_complete_rec=> p_utilization_rec
2559 ,x_return_status=> l_return_status
2560 );
2561
2562 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2563 RAISE fnd_api.g_exc_unexpected_error;
2564 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2565 RAISE fnd_api.g_exc_error;
2566 END IF;
2567 END IF;
2568
2569
2570 -------------------- finish --------------------------
2571 fnd_msg_pub.count_and_get (
2572 p_encoded=> fnd_api.g_false
2573 ,p_count=> x_msg_count
2574 ,p_data=> x_msg_data
2575 );
2576 IF G_DEBUG THEN
2577 ozf_utility_pvt.debug_message ( l_full_name
2578 || ': end');
2579 END IF;
2580 EXCEPTION
2581 WHEN fnd_api.g_exc_error THEN
2582 x_return_status := fnd_api.g_ret_sts_error;
2583 fnd_msg_pub.count_and_get (
2584 p_encoded=> fnd_api.g_false
2585 ,p_count=> x_msg_count
2586 ,p_data=> x_msg_data
2587 );
2588 WHEN fnd_api.g_exc_unexpected_error THEN
2589 x_return_status := fnd_api.g_ret_sts_unexp_error;
2590 fnd_msg_pub.count_and_get (
2591 p_encoded=> fnd_api.g_false
2592 ,p_count=> x_msg_count
2593 ,p_data=> x_msg_data
2594 );
2595 WHEN OTHERS THEN
2596 x_return_status := fnd_api.g_ret_sts_unexp_error;
2597
2598 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2599 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2600 END IF;
2601
2602 fnd_msg_pub.count_and_get (
2603 p_encoded=> fnd_api.g_false
2604 ,p_count=> x_msg_count
2605 ,p_data=> x_msg_data
2606 );
2607 END validate_utilization;
2608
2609
2610 ---------------------------------------------------------------------
2611 -- PROCEDURE
2612 -- Check_Utilized_Req_Items
2613 --
2614 -- HISTORY
2615 -- 04/25/2000 Mumu Pande Create.
2616 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2617 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2618 ---------------------------------------------------------------------
2619 PROCEDURE check_utilized_req_items (
2620 p_utilization_rec IN utilization_rec_type
2621 ,x_return_status OUT NOCOPY VARCHAR2
2622 ) IS
2623 BEGIN
2624 x_return_status := fnd_api.g_ret_sts_success;
2625
2626
2627 ------------------------ fund_id --------------------------
2628 IF p_utilization_rec.fund_id IS NULL THEN -- check for fund id
2629 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2630 fnd_message.set_name ('OZF', 'OZF_NO_FUND_ID');
2631 fnd_msg_pub.ADD;
2632 END IF;
2633
2634 x_return_status := fnd_api.g_ret_sts_error;
2635 RETURN;
2636
2637 ------------------------ AMOUNT -------------------------------
2638 ELSIF p_utilization_rec.amount IS NULL THEN -- check for amount
2639 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2640 fnd_message.set_name ('OZF', 'OZF_NO_UTILIZED_AMOUNT');
2641 fnd_msg_pub.ADD;
2642 END IF;
2643
2644 x_return_status := fnd_api.g_ret_sts_error;
2645 RETURN;
2646 ELSIF p_utilization_rec.utilization_type IN ('ADJUSTMENT', 'CHARGEBACK', 'LEAD_ADJUSTMENT') THEN
2647 IF p_utilization_rec.adjustment_type IS NULL THEN -- check for utilization
2648 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2649 fnd_message.set_name ('OZF', 'OZF_FUND_NO_ADJUSTMENT_TYPE');
2650 fnd_msg_pub.ADD;
2651 END IF;
2652
2653 x_return_status := fnd_api.g_ret_sts_error;
2654 RETURN;
2655 END IF;
2656 -- added for customer id for offers 8/14/2002 mpande
2657 IF p_utilization_rec.adjustment_type IN ('DECREASE_EARNED' ,'DECREASE_COMM_EARNED','STANDARD')
2658 AND p_utilization_rec.plan_type = 'OFFR' AND p_utilization_rec.cust_account_id IS NULL THEN -- check for utilization type
2659 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2660 fnd_message.set_name ('OZF', 'OZF_FUND_NO_CUST_ID');
2661 fnd_msg_pub.ADD;
2662 END IF;
2663 x_return_status := fnd_api.g_ret_sts_error;
2664 RETURN;
2665 END IF;
2666 ELSIF p_utilization_rec.utilization_type IS NULL THEN -- check for utilization type
2667 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2668 fnd_message.set_name ('OZF', 'OZF_FUND_NO_UTILIZATION_TYPE');
2669 fnd_msg_pub.ADD;
2670 END IF;
2671
2672 x_return_status := fnd_api.g_ret_sts_error;
2673 RETURN;
2674 END IF;
2675
2676 END check_utilized_req_items;
2677
2678
2679 ---------------------------------------------------------------------
2680 -- PROCEDURE
2681 -- Check_Utilized_Uk_Items
2682 --
2683 -- HISTORY
2684 -- 04/25/2000 Mumu Pande Create.
2685 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2686 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2687 ---------------------------------------------------------------------
2688 PROCEDURE check_utilized_uk_items (
2689 p_utilization_rec IN utilization_rec_type
2690 ,p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create
2691 ,x_return_status OUT NOCOPY VARCHAR2
2692 ) IS
2693 l_valid_flag VARCHAR2 (1);
2694 BEGIN
2695 x_return_status := fnd_api.g_ret_sts_success;
2696
2697 -- For Create_Utilization, when utilization_id is passed in, we need to
2698 -- check if this utilization_id is unique.
2699 IF p_validation_mode = jtf_plsql_api.g_create
2700 AND p_utilization_rec.utilization_id IS NOT NULL THEN
2701 IF ozf_utility_pvt.check_uniqueness (
2702 'ozf_funds_UTILIZED_all_b'
2703 , 'utilization_id = '
2704 || p_utilization_rec.utilization_id
2705 ) = fnd_api.g_false THEN
2706 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2707 fnd_message.set_name ('OZF', 'OZF_UTILIZED_DUPLICATE_ID');
2708 fnd_msg_pub.ADD;
2709 END IF;
2710
2711 x_return_status := fnd_api.g_ret_sts_error;
2712 RETURN;
2713 END IF;
2714 END IF;
2715
2716 -- check other unique items
2717
2718 END check_utilized_uk_items;
2719
2720
2721 --------------------------------------------------------------------
2722 -- PROCEDURE
2723 -- Check_Utilized_Fk_Items
2724 --
2725 -- HISTORY
2726 -- 04/25/2000 Mumu Pande Create.
2727 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2728 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2729 ---------------------------------------------------------------------
2730 PROCEDURE check_utilized_fk_items (
2731 p_utilization_rec IN utilization_rec_type
2732 ,x_return_status OUT NOCOPY VARCHAR2
2733 ) IS
2734 BEGIN
2735 x_return_status := fnd_api.g_ret_sts_success;
2736
2737
2738 ----------------------- fund_id ------------------------
2739 IF p_utilization_rec.fund_id <> fnd_api.g_miss_num THEN
2740 IF ozf_utility_pvt.check_fk_exists (
2741 'ozf_funds_all_b'
2742 , -- Parent schema object having the primary key
2743 'fund_id'
2744 , -- Column name in the parent object that maps to the fk value
2745 p_utilization_rec.fund_id -- Value of fk to be validated against the parent object's pk column
2746 ) = fnd_api.g_false THEN
2747 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2748 fnd_message.set_name ('OZF', 'OZF_BAD_FUND_ID');
2749 fnd_msg_pub.ADD;
2750 END IF;
2751
2752 x_return_status := fnd_api.g_ret_sts_error;
2753 RETURN;
2754 END IF;
2755 END IF;
2756
2757 ----------------------- adjustment_id ------------------------
2758 IF p_utilization_rec.adjustment_type_id <> fnd_api.g_miss_num THEN
2759 IF ozf_utility_pvt.check_fk_exists (
2760 'ozf_claim_types_all_b'
2761 , -- Parent schema object having the primary key
2762 'claim_type_id'
2763 , -- Column name in the parent object that maps to the fk value
2764 p_utilization_rec.adjustment_type_id -- Value of fk to be validated against the parent object's pk column
2765 ) = fnd_api.g_false THEN
2766 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2767 fnd_message.set_name ('OZF', 'OZFBAD_ADJUSTMENT_ID');
2768 fnd_msg_pub.ADD;
2769 END IF;
2770
2771 x_return_status := fnd_api.g_ret_sts_error;
2772 RETURN;
2773 END IF;
2774 END IF;
2775
2776 -- check other fk items
2777
2778 END check_utilized_fk_items;
2779
2780
2781 ---------------------------------------------------------------------
2782 -- PROCEDURE
2783 -- Check_Utilized_Lookup_Items
2784 --
2785 -- HISTORY
2786 -- 04/25/2000 Mumu Pande Create.
2787 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2788 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2789 ---------------------------------------------------------------------
2790 PROCEDURE check_utilized_lookup_items (
2791 p_utilization_rec IN utilization_rec_type
2792 ,x_return_status OUT NOCOPY VARCHAR2
2793 ) IS
2794 BEGIN
2795 x_return_status := fnd_api.g_ret_sts_success;
2796
2797 ----------------------- utilization_type ------------------------
2798 IF p_utilization_rec.utilization_type <> fnd_api.g_miss_char THEN
2799 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
2800 AND p_utilization_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST') THEN
2801 */
2802 IF ozf_utility_pvt.check_lookup_exists (
2803 p_lookup_table_name=> 'OZF_LOOKUPS'
2804 ,p_lookup_type=> 'OZF_UTILIZATION_TYPE'
2805 ,p_lookup_code=> p_utilization_rec.utilization_type
2806 ) = fnd_api.g_false THEN
2807 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2808 fnd_message.set_name ('OZF', 'OZF_BAD_UTILIZED_CODE');
2809 fnd_msg_pub.ADD;
2810 END IF;
2811
2812 x_return_status := fnd_api.g_ret_sts_error;
2813 RETURN;
2814 END IF;
2815 END IF;
2816
2817 --------------------02/08/2001 mpande added----------------
2818 IF p_utilization_rec.adjustment_type <> fnd_api.g_miss_char THEN
2819 IF ozf_utility_pvt.check_lookup_exists (
2820 p_lookup_table_name=> 'OZF_LOOKUPS'
2821 ,p_lookup_type=> 'OZF_ADJUSTMENT_TYPE'
2822 ,p_lookup_code=> p_utilization_rec.adjustment_type
2823 ) = fnd_api.g_false THEN
2824 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
2825 fnd_message.set_name ('OZF', 'OZF_BAD_ADJUSTMENT_CODE');
2826 fnd_msg_pub.ADD;
2827 END IF;
2828
2829 x_return_status := fnd_api.g_ret_sts_error;
2830 RETURN;
2831 END IF;
2832 END IF;
2833
2834 -- check other lookup codes
2835
2836 END check_utilized_lookup_items;
2837
2838
2839 ---------------------------------------------------------------------
2840 -- PROCEDURE
2841 -- Check_Utilized_Flag_Items
2842 --
2843 -- HISTORY
2844 -- 04/25/2000 Mumu Pande Create.
2845 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2846 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2847 ---------------------------------------------------------------------
2848 PROCEDURE check_utilized_flag_items (
2849 p_utilization_rec IN utilization_rec_type
2850 ,x_return_status OUT NOCOPY VARCHAR2
2851 ) IS
2852 BEGIN
2853 x_return_status := fnd_api.g_ret_sts_success;
2854
2855 -- check other flags
2856
2857 END check_utilized_flag_items;
2858
2859
2860 ---------------------------------------------------------------------
2861 -- PROCEDURE
2862 -- Check_Utilization_Items
2863 --
2864 -- HISTORY
2865 -- 04/25/2000 Mumu Pande Create.
2866 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2867 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2868 ---------------------------------------------------------------------
2869 PROCEDURE check_utilization_items (
2870 p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create
2871 ,x_return_status OUT NOCOPY VARCHAR2
2872 ,p_utilization_rec IN utilization_rec_type
2873 ) IS
2874 BEGIN
2875 check_utilized_req_items (
2876 p_utilization_rec=> p_utilization_rec
2877 ,x_return_status=> x_return_status
2878 );
2879
2880 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2881 RETURN;
2882 END IF;
2883
2884 check_utilized_uk_items (
2885 p_utilization_rec=> p_utilization_rec
2886 ,p_validation_mode=> p_validation_mode
2887 ,x_return_status=> x_return_status
2888 );
2889
2890 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2891 RETURN;
2892 END IF;
2893
2894 check_utilized_fk_items (
2895 p_utilization_rec=> p_utilization_rec
2896 ,x_return_status=> x_return_status
2897 );
2898
2899 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2900 RETURN;
2901 END IF;
2902
2903 check_utilized_lookup_items (
2904 p_utilization_rec=> p_utilization_rec
2905 ,x_return_status=> x_return_status
2906 );
2907
2908 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2909 RETURN;
2910 END IF;
2911
2912 check_utilized_flag_items (
2913 p_utilization_rec=> p_utilization_rec
2914 ,x_return_status=> x_return_status
2915 );
2916
2917 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2918 RETURN;
2919 END IF;
2920 END check_utilization_items;
2921
2922
2923 ---------------------------------------------------------------------
2924 -- FUNCTION
2925 -- Check_committed_amount_exists
2926 --
2927 -- HISTORY
2928 -- 04/25/2000 Mumu Pande Create.
2929 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
2930 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
2931 ---------------------------------------------------------------------
2932
2933 FUNCTION check_committed_amount_exists (
2934 p_amount IN NUMBER
2935 ,p_activity_id IN NUMBER
2936 ,p_activity_type IN VARCHAR2
2937 ,p_fund_id IN NUMBER
2938 ,p_cust_account_id IN NUMBER := NULL
2939 )
2940 RETURN VARCHAR2 IS
2941 l_amount NUMBER;
2942 l_recal_flag VARCHAR2(1);
2943
2944 CURSOR c_transfer_allowed (
2945 p_object_id IN NUMBER
2946 ,p_object_type IN VARCHAR2
2947 ,p_fund_src_id IN NUMBER
2948 ) IS
2949 /* yzhao: 09/29/2005 R12
2950 SELECT SUM (total_amount) existing_amount
2951 FROM (SELECT parent_source
2952 ,SUM (amount) total_amount
2953 FROM (SELECT a1.fund_id parent_source
2954 ,NVL (SUM (a1.amount), 0) amount
2955 FROM ozf_funds_utilized_all_b a1
2956 WHERE a1.component_id = p_object_id
2957 AND a1.component_type = p_object_type
2958 AND a1.utilization_type IN ('TRANSFER', 'REQUEST')
2959 GROUP BY a1.fund_id
2960 UNION
2961 SELECT a2.fund_id parent_source
2962 ,-NVL (SUM (a2.amount), 0) amount
2963 FROM ozf_funds_utilized_all_b a2
2964 WHERE a2.plan_id = p_object_id
2965 AND a2.plan_type = p_object_type
2966 AND a2.utilization_type NOT IN ('TRANSFER', 'REQUEST')
2967 GROUP BY a2.fund_id)
2968 WHERE parent_source = p_fund_src_id
2969 GROUP BY parent_source);
2970 */
2971 SELECT SUM(NVL(committed_amt,0)-NVL(utilized_amt,0)) total_amount
2972 FROM ozf_object_fund_summary
2973 WHERE object_id =p_object_id
2974 AND object_type = p_object_type
2975 and fund_id = p_fund_src_id;
2976
2977 BEGIN
2978 -- if no offer id then chekc against the activity
2979
2980 IF G_DEBUG THEN
2981 ozf_utility_pvt.debug_message (
2982 ': check record'
2983 || p_cust_account_id
2984 || '-'
2985 || p_amount
2986 || p_fund_id
2987 || p_activity_id
2988 || p_activity_type
2989 );
2990 END IF;
2991
2992 -- IF p_component_id IS NULL THEN
2993 -- ozf_utility_pvt.debug_message(': check record1' || p_component_id ||'-'|| p_amount||p_fund_id||p_activity_id||p_activity_type);
2994 IF p_activity_type <> 'OFFR' THEN
2995 l_recal_flag := 'N' ;
2996 ELSE
2997 l_recal_flag := NVL(fnd_profile.value('OZF_BUDGET_ADJ_ALLOW_RECAL'),'N');
2998 END IF;
2999 -- if offer id present then check against the amount
3000 -- if recal flag is on then can adjsut more than committed
3001
3002 IF l_recal_flag = 'N' THEN
3003 OPEN c_transfer_allowed (p_activity_id, p_activity_type, p_fund_id);
3004 FETCH c_transfer_allowed INTO l_amount;
3005
3006 IF c_transfer_allowed%NOTFOUND THEN
3007 RETURN fnd_api.g_false;
3008 END IF;
3009
3010 CLOSE c_transfer_allowed;
3011 IF G_DEBUG THEN
3012 ozf_utility_pvt.debug_message (
3013 ': check record2'
3014 || p_cust_account_id
3015 || '-'
3016 || p_amount
3017 || p_fund_id
3018 || p_activity_id
3019 || p_activity_type
3020 );
3021 END IF;
3022 IF NVL (p_amount, 0) > NVL (l_amount, 0) THEN
3023 IF G_DEBUG THEN
3024 ozf_utility_pvt.debug_message ( ': check record'
3025 || l_amount
3026 || p_amount);
3027 END IF;
3028 RETURN fnd_api.g_false;
3029 END IF;
3030 ELSE
3031 RETURN fnd_api.g_true;
3032 END IF ;
3033
3034 RETURN fnd_api.g_true;
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037 IF c_transfer_allowed%ISOPEN THEN
3038 CLOSE c_transfer_allowed;
3039 RETURN fnd_api.g_false;
3040 END IF;
3041
3042 RAISE;
3043 END check_committed_amount_exists;
3044
3045
3046 ---------------------------------------------------------------------
3047 -- FUNCTION
3048 -- Check_earned_amount_positive
3049 --
3050 -- HISTORY
3051 -- 04/25/2000 Mumu Pande Create.
3052 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
3053 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
3054 -- 8/14/2002 Mumu Pande Added Customer Account
3055 ---------------------------------------------------------------------
3056 FUNCTION check_earned_amount_positive (
3057 p_amount IN NUMBER
3058 ,p_fund_id IN NUMBER
3059 ,p_act_id IN NUMBER
3060 ,p_act_type IN VARCHAR2
3061 ,p_cust_account_id IN NUMBER := NULL
3062 )
3063 RETURN VARCHAR2 IS
3064 l_amount NUMBER;
3065
3066 CURSOR c_budget_earn_rec (p_act_id IN NUMBER, p_act_type IN VARCHAR2, p_fund_id IN NUMBER) IS
3067 SELECT SUM (amount)
3068 FROM ozf_funds_utilized_all_b
3069 WHERE fund_id = p_fund_id
3070 AND plan_type = p_act_type
3071 AND plan_id = p_act_id
3072 -- AND utilization_type NOT IN ('REQUEST', 'TRANSFER') R12 no REQUEST/TRANSFER in util table
3073 AND NVL(cust_account_id,-1) = NVL(p_cust_account_id,-2);
3074
3075 /*
3076 CURSOR c_budget_off_earn_rec (p_offer_id IN NUMBER) IS
3077 SELECT SUM (amount)
3078 FROM ozf_funds_utilized_all_b
3079 WHERE fund_id = p_fund_id
3080 AND component_type = 'OFFR'
3081 AND component_id = p_offer_id
3082 AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
3083 */
3084 BEGIN
3085 -- if no offer id then chekc against the activity
3086
3087 IF G_DEBUG THEN
3088 ozf_utility_pvt.debug_message ( ': check record'
3089 || p_cust_account_id
3090 || p_amount);
3091 END IF;
3092
3093 -- IF p_component_id IS NULL THEN
3094 OPEN c_budget_earn_rec (p_act_id, p_act_type, p_fund_id);
3095 FETCH c_budget_earn_rec INTO l_amount;
3096
3097 IF c_budget_earn_rec%NOTFOUND THEN
3098 CLOSE c_budget_earn_rec;
3099 RETURN fnd_api.g_false;
3100 END IF;
3101
3102 CLOSE c_budget_earn_rec;
3103 /* ELSE
3104 OPEN c_budget_off_earn_rec (p_component_id);
3105 FETCH c_budget_off_earn_rec INTO l_amount;
3106
3107 IF c_budget_off_earn_rec%NOTFOUND THEN
3108 CLOSE c_budget_off_earn_rec;
3109 RETURN fnd_api.g_false;
3110 END IF;
3111
3112 CLOSE c_budget_off_earn_rec;
3113 END IF;
3114 */
3115 -- if offer id present then check against the amount
3116 IF ABS(NVL (p_amount, 0)) > NVL (l_amount, 0) THEN
3117 IF G_DEBUG THEN
3118 ozf_utility_pvt.debug_message ( ': check record'
3119 || l_amount
3120 || p_amount);
3121 END IF;
3122 RETURN fnd_api.g_false;
3123 END IF;
3124
3125 RETURN fnd_api.g_true;
3126 END check_earned_amount_positive;
3127
3128
3129 ---------------------------------------------------------------------
3130 -- FUNCTION
3131 -- Check_fund_active
3132 --
3133 -- HISTORY
3134 -- 04/25/2000 Mumu Pande Create.
3135 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
3136 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
3137 ---------------------------------------------------------------------
3138 FUNCTION check_fund_active (p_fund_id IN NUMBER)
3139 RETURN VARCHAR2 IS
3140 l_status VARCHAR2 (30);
3141
3142 CURSOR c_budget_rec (p_fund_id IN NUMBER) IS
3143 SELECT status_code
3144 FROM ozf_funds_all_b
3145 WHERE fund_id = p_fund_id;
3146 BEGIN
3147 OPEN c_budget_rec (p_fund_id);
3148 FETCH c_budget_rec INTO l_status;
3149
3150 IF c_budget_rec%NOTFOUND THEN
3151 CLOSE c_budget_rec;
3152 RETURN fnd_api.g_false;
3153 END IF;
3154
3155 -- check against the amount
3156 IF l_status <> 'ACTIVE' THEN
3157 IF G_DEBUG THEN
3158 ozf_utility_pvt.debug_message ( ': check record'
3159 || l_status);
3160 END IF;
3161 RETURN fnd_api.g_false;
3162 END IF;
3163
3164 RETURN fnd_api.g_true;
3165 END check_fund_active;
3166
3167
3168 ---------------------------------------------------------------------
3169 -- PROCEDURE
3170 -- Check_Utilization_Record
3171 --
3172 -- HISTORY
3173 -- 04/25/2000 Mumu Pande Create.
3174 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
3175 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
3176 ---------------------------------------------------------------------
3177 PROCEDURE check_utilization_record (
3178 p_utilization_rec IN utilization_rec_type
3179 ,p_complete_rec IN utilization_rec_type := NULL
3180 ,p_mode IN VARCHAR2 := 'INSERT'
3181 ,x_return_status OUT NOCOPY VARCHAR2
3182 ) IS
3183 CURSOR c_fund_type (p_fund_id IN NUMBER) IS
3184 SELECT 'X'
3185 FROM ozf_funds_all_b ozf
3186 WHERE ozf.fund_type = 'FULLY_ACCRUED'
3187 AND ozf.fund_id = p_fund_id;
3188
3189 l_dummy VARCHAR2 (3) := 'X';
3190 BEGIN
3191 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
3192 IF p_complete_rec.utilization_type IN ('REQUEST', 'TRANSFER') THEN
3193 */
3194 OPEN c_fund_type (p_complete_rec.fund_id);
3195 FETCH c_fund_type INTO l_dummy;
3196 CLOSE c_fund_type;
3197 -- END IF;
3198
3199 x_return_status := fnd_api.g_ret_sts_success;
3200
3201 IF p_mode <> 'UPDATE' THEN
3202 -- Check for committed amount exists all offers except FULLY ACCRUED budget offer
3203 /* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
3204 IF p_complete_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST')
3205 AND l_dummy IS NULL AND
3206 */
3207 IF l_dummy IS NULL AND
3208 NVL(p_complete_rec.adjustment_type,'N') NOT IN ( 'DECREASE_EARNED' ,'DECREASE_COMM_EARNED') THEN
3209 IF check_committed_amount_exists (
3210 p_complete_rec.amount
3211 ,p_complete_rec.plan_id
3212 ,p_complete_rec.plan_type
3213 ,p_complete_rec.fund_id
3214 ,p_complete_rec.component_id
3215 ) = fnd_api.g_false THEN
3216 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
3217 fnd_message.set_name ('OZF', 'OZF_FUND_NO_COMMITTMENT');
3218 fnd_msg_pub.ADD;
3219 END IF;
3220
3221 x_return_status := fnd_api.g_ret_sts_error;
3222 RETURN;
3223 END IF;
3224 END IF;
3225
3226
3227 IF check_fund_active (p_complete_rec.fund_id) = fnd_api.g_false THEN
3228 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
3229 fnd_message.set_name ('OZF', 'OZF_FUND_NO_ADJUST');
3230 fnd_msg_pub.ADD;
3231 END IF;
3232 x_return_status := fnd_api.g_ret_sts_error;
3233 RETURN;
3234 END IF;
3235
3236 END IF; -- end if for p_mode
3237
3238 IF l_dummy IS NULL THEN
3239 IF p_complete_rec.adjustment_type = ('DECREASE_EARNED') THEN
3240 -- amount should be positive always
3241 IF NVL (p_complete_rec.amount, 0) <= 0 THEN
3242 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
3243 fnd_message.set_name ('OZF', 'OZF_UTIL_NO_AMOUNT');
3244 fnd_msg_pub.ADD;
3245 END IF;
3246
3247 x_return_status := fnd_api.g_ret_sts_error;
3248 RETURN;
3249 END IF;
3250 ELSE
3251 IF NVL (p_complete_rec.amount, 0) = 0 THEN
3252 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
3253 fnd_message.set_name ('OZF', 'OZF_UTIL_NO_AMOUNT');
3254 fnd_msg_pub.ADD;
3255 END IF;
3256
3257 x_return_status := fnd_api.g_ret_sts_error;
3258 RETURN;
3259 END IF;
3260 END IF;
3261 END IF;
3262 END check_utilization_record;
3263
3264
3265 ---------------------------------------------------------------------
3266 -- PROCEDURE
3267 -- Init_Utilization_Rec
3268 --
3269 -- HISTORY
3270 -- 04/25/2000 Mumu Pande Create.
3271 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
3272 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
3273 ---------------------------------------------------------------------
3274 PROCEDURE init_utilization_rec (x_utilization_rec OUT NOCOPY utilization_rec_type) IS
3275 BEGIN
3276 RETURN;
3277 END init_utilization_rec;
3278
3279
3280 ---------------------------------------------------------------------
3281 -- PROCEDURE
3282 -- Complete_Utilization_Rec
3283 --
3284 -- HISTORY
3285 -- 04/25/2000 Mumu Pande Create.
3286 -- 02/08/2001 Mumu Pande Updated for 11.5.5 requirements
3287 -- 02/23/2001 Mumu PAnde Updated for Hornet requirements
3288
3289 ---------------------------------------------------------------------
3290 PROCEDURE complete_utilization_rec (
3291 p_utilization_rec IN utilization_rec_type
3292 ,x_complete_rec OUT NOCOPY utilization_rec_type
3293 ) IS
3294 CURSOR c_utilization IS
3295 SELECT *
3296 FROM ozf_funds_utilized_all_vl
3297 WHERE utilization_id = p_utilization_rec.utilization_id;
3298
3299 l_utilization_rec c_utilization%ROWTYPE;
3300 BEGIN
3301 x_complete_rec := p_utilization_rec;
3302 OPEN c_utilization;
3303 FETCH c_utilization INTO l_utilization_rec;
3304
3305 IF c_utilization%NOTFOUND THEN
3306 CLOSE c_utilization;
3307
3308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
3309 fnd_message.set_name ('OZF', 'OZF_API_RECORD_NOT_FOUND');
3310 fnd_msg_pub.ADD;
3311 END IF;
3312
3313 RAISE fnd_api.g_exc_error;
3314 END IF;
3315
3316 CLOSE c_utilization;
3317
3318 IF p_utilization_rec.utilization_type = fnd_api.g_miss_char THEN
3319 x_complete_rec.utilization_type := NULL;
3320 END IF;
3321 IF p_utilization_rec.utilization_type IS NULL THEN
3322 x_complete_rec.utilization_type := l_utilization_rec.utilization_type;
3323 END IF;
3324
3325 IF p_utilization_rec.fund_id = fnd_api.g_miss_num THEN
3326 x_complete_rec.fund_id := NULL;
3327 END IF;
3328 IF p_utilization_rec.fund_id IS NULL THEN
3329 x_complete_rec.fund_id := l_utilization_rec.fund_id;
3330 END IF;
3331
3332 IF p_utilization_rec.plan_type = fnd_api.g_miss_char THEN
3333 x_complete_rec.plan_type := NULL;
3334 END IF;
3335 IF p_utilization_rec.plan_type IS NULL THEN
3336 x_complete_rec.plan_type := l_utilization_rec.plan_type;
3337 END IF;
3338
3339 IF p_utilization_rec.plan_id = fnd_api.g_miss_num THEN
3340 x_complete_rec.plan_id := NULL;
3341 END IF;
3342 IF p_utilization_rec.plan_id IS NULL THEN
3343 x_complete_rec.plan_id := l_utilization_rec.plan_id;
3344 END IF;
3345
3346 IF p_utilization_rec.component_type = fnd_api.g_miss_char THEN
3347 x_complete_rec.component_type := NULL;
3348 END IF;
3349 IF p_utilization_rec.component_type IS NULL THEN
3350 x_complete_rec.component_type := l_utilization_rec.component_type;
3351 END IF;
3352
3353 IF p_utilization_rec.component_id = fnd_api.g_miss_num THEN
3354 x_complete_rec.component_id := NULL;
3355 END IF;
3356 IF p_utilization_rec.component_id IS NULL THEN
3357 x_complete_rec.component_id := l_utilization_rec.component_id;
3358 END IF;
3359
3360 IF p_utilization_rec.object_type = fnd_api.g_miss_char THEN
3361 x_complete_rec.object_type := NULL;
3362 END IF;
3363 IF p_utilization_rec.object_type IS NULL THEN
3364 x_complete_rec.object_type := l_utilization_rec.object_type;
3365 END IF;
3366
3367 IF p_utilization_rec.object_id = fnd_api.g_miss_num THEN
3368 x_complete_rec.object_id := NULL;
3369 END IF;
3370 IF p_utilization_rec.object_id IS NULL THEN
3371 x_complete_rec.object_id := l_utilization_rec.object_id;
3372 END IF;
3373
3374 IF p_utilization_rec.order_id = fnd_api.g_miss_num THEN
3375 x_complete_rec.order_id := NULL;
3376 END IF;
3377 IF p_utilization_rec.order_id IS NULL THEN
3378 x_complete_rec.order_id := l_utilization_rec.order_id;
3379 END IF;
3380
3381 IF p_utilization_rec.invoice_id = fnd_api.g_miss_num THEN
3382 x_complete_rec.invoice_id := NULL;
3383 END IF;
3384 IF p_utilization_rec.invoice_id IS NULL THEN
3385 x_complete_rec.invoice_id := l_utilization_rec.invoice_id;
3386 END IF;
3387
3388 IF p_utilization_rec.amount = fnd_api.g_miss_num THEN
3389 x_complete_rec.amount := NULL;
3390 END IF;
3391 IF p_utilization_rec.amount IS NULL THEN
3392 x_complete_rec.amount := l_utilization_rec.amount;
3393 END IF;
3394
3395 IF p_utilization_rec.acctd_amount = fnd_api.g_miss_num THEN
3396 x_complete_rec.acctd_amount := NULL;
3397 END IF;
3398 IF p_utilization_rec.acctd_amount IS NULL THEN
3399 x_complete_rec.acctd_amount := l_utilization_rec.acctd_amount;
3400 END IF;
3401
3402 IF p_utilization_rec.currency_code = fnd_api.g_miss_char THEN
3403 x_complete_rec.currency_code := NULL;
3404 END IF;
3405 IF p_utilization_rec.currency_code IS NULL THEN
3406 x_complete_rec.currency_code := l_utilization_rec.currency_code;
3407 END IF;
3408
3409
3410 ----------------------------------------------------------------------------
3411 --02/09/2001 ADDEd by mpande for 11.5.5 reqmnts.--
3412 IF p_utilization_rec.adjustment_type_id = fnd_api.g_miss_num THEN
3413 x_complete_rec.adjustment_type_id := NULL;
3414 END IF;
3415 IF p_utilization_rec.adjustment_type_id IS NULL THEN
3416 x_complete_rec.adjustment_type_id := l_utilization_rec.adjustment_type_id;
3417 END IF;
3418
3419 IF p_utilization_rec.camp_schedule_id = fnd_api.g_miss_num THEN
3420 x_complete_rec.camp_schedule_id := NULL;
3421 END IF;
3422 IF p_utilization_rec.camp_schedule_id IS NULL THEN
3423 x_complete_rec.camp_schedule_id := l_utilization_rec.camp_schedule_id;
3424 END IF;
3425
3426 IF p_utilization_rec.gl_date = fnd_api.g_miss_date THEN
3427 x_complete_rec.gl_date := NULL;
3428 END IF;
3429 IF p_utilization_rec.gl_date IS NULL THEN
3430 x_complete_rec.gl_date := l_utilization_rec.gl_date;
3431 END IF;
3432
3433 IF p_utilization_rec.product_level_type = fnd_api.g_miss_char THEN
3434 x_complete_rec.product_level_type := NULL;
3435 END IF;
3436 IF p_utilization_rec.product_level_type IS NULL THEN
3437 x_complete_rec.product_level_type := l_utilization_rec.product_level_type;
3438 END IF;
3439
3440 IF p_utilization_rec.product_id = fnd_api.g_miss_num THEN
3441 x_complete_rec.product_id := NULL;
3442 END IF;
3443 IF p_utilization_rec.product_id IS NULL THEN
3444 x_complete_rec.product_id := l_utilization_rec.product_id;
3445 END IF;
3446
3447 IF p_utilization_rec.ams_activity_budget_id = fnd_api.g_miss_num THEN
3448 x_complete_rec.ams_activity_budget_id := NULL;
3449 END IF;
3450 IF p_utilization_rec.ams_activity_budget_id IS NULL THEN
3451 x_complete_rec.ams_activity_budget_id := l_utilization_rec.ams_activity_budget_id;
3452 END IF;
3453
3454 IF p_utilization_rec.amount_remaining = fnd_api.g_miss_num THEN
3455 x_complete_rec.amount_remaining := NULL;
3456 END IF;
3457 IF p_utilization_rec.amount_remaining IS NULL THEN
3458 x_complete_rec.amount_remaining := l_utilization_rec.amount_remaining;
3459 END IF;
3460
3461 IF p_utilization_rec.acctd_amount_remaining = fnd_api.g_miss_num THEN
3462 x_complete_rec.acctd_amount_remaining := NULL;
3463 END IF;
3464 IF p_utilization_rec.acctd_amount_remaining IS NULL THEN
3465 x_complete_rec.acctd_amount_remaining := l_utilization_rec.acctd_amount_remaining;
3466 END IF;
3467
3468 IF p_utilization_rec.cust_account_id = fnd_api.g_miss_num THEN
3469 x_complete_rec.cust_account_id := NULL;
3470 END IF;
3471 IF p_utilization_rec.cust_account_id IS NULL THEN
3472 x_complete_rec.cust_account_id := l_utilization_rec.cust_account_id;
3473 END IF;
3474
3475 IF p_utilization_rec.price_adjustment_id = fnd_api.g_miss_num THEN
3476 x_complete_rec.price_adjustment_id := NULL;
3477 END IF;
3478 IF p_utilization_rec.price_adjustment_id IS NULL THEN
3479 x_complete_rec.price_adjustment_id := l_utilization_rec.price_adjustment_id;
3480 END IF;
3481
3482
3483 --------------------------------------------------------------------------------
3484 IF p_utilization_rec.exchange_rate_type = fnd_api.g_miss_char THEN
3485 x_complete_rec.exchange_rate_type := NULL;
3486 END IF;
3487 IF p_utilization_rec.exchange_rate_type IS NULL THEN
3488 x_complete_rec.exchange_rate_type := l_utilization_rec.exchange_rate_type;
3489 END IF;
3490
3491 IF p_utilization_rec.exchange_rate_date = fnd_api.g_miss_date THEN
3492 x_complete_rec.exchange_rate_date := NULL;
3493 END IF;
3494 IF p_utilization_rec.exchange_rate_date IS NULL THEN
3495 x_complete_rec.exchange_rate_date := l_utilization_rec.exchange_rate_date;
3496 END IF;
3497
3498 IF p_utilization_rec.exchange_rate = fnd_api.g_miss_num THEN
3499 x_complete_rec.exchange_rate := NULL;
3500 END IF;
3501 IF p_utilization_rec.exchange_rate IS NULL THEN
3502 x_complete_rec.exchange_rate := l_utilization_rec.exchange_rate;
3503 END IF;
3504
3505 IF p_utilization_rec.adjustment_type = fnd_api.g_miss_char THEN
3506 x_complete_rec.adjustment_type := NULL;
3507 END IF;
3508 IF p_utilization_rec.adjustment_type IS NULL THEN
3509 x_complete_rec.adjustment_type := l_utilization_rec.adjustment_type;
3510 END IF;
3511
3512 IF p_utilization_rec.adjustment_date = fnd_api.g_miss_date THEN
3513 x_complete_rec.adjustment_date := NULL;
3514 END IF;
3515 IF p_utilization_rec.adjustment_date IS NULL THEN
3516 x_complete_rec.adjustment_date := l_utilization_rec.adjustment_date;
3517 END IF;
3518
3519 IF p_utilization_rec.object_version_number = fnd_api.g_miss_num THEN
3520 x_complete_rec.object_version_number := NULL;
3521 END IF;
3522 IF p_utilization_rec.object_version_number IS NULL THEN
3523 x_complete_rec.object_version_number := l_utilization_rec.object_version_number;
3524 END IF;
3525
3526 IF p_utilization_rec.attribute_category = fnd_api.g_miss_char THEN
3527 x_complete_rec.attribute_category := NULL;
3528 END IF;
3529 IF p_utilization_rec.attribute_category IS NULL THEN
3530 x_complete_rec.attribute_category := l_utilization_rec.attribute_category;
3531 END IF;
3532
3533 IF p_utilization_rec.attribute1 = fnd_api.g_miss_char THEN
3534 x_complete_rec.attribute1 := NULL;
3535 END IF;
3536 IF p_utilization_rec.attribute1 IS NULL THEN
3537 x_complete_rec.attribute1 := l_utilization_rec.attribute1;
3538 END IF;
3539
3540 IF p_utilization_rec.attribute2 = fnd_api.g_miss_char THEN
3541 x_complete_rec.attribute2 := NULL;
3542 END IF;
3543 IF p_utilization_rec.attribute2 IS NULL THEN
3544 x_complete_rec.attribute2 := l_utilization_rec.attribute2;
3545 END IF;
3546
3547 IF p_utilization_rec.attribute3 = fnd_api.g_miss_char THEN
3548 x_complete_rec.attribute3 := NULL;
3549 END IF;
3550 IF p_utilization_rec.attribute3 IS NULL THEN
3551 x_complete_rec.attribute3 := l_utilization_rec.attribute3;
3552 END IF;
3553
3554 IF p_utilization_rec.attribute4 = fnd_api.g_miss_char THEN
3555 x_complete_rec.attribute4 := NULL;
3556 END IF;
3557 IF p_utilization_rec.attribute4 IS NULL THEN
3558 x_complete_rec.attribute4 := l_utilization_rec.attribute4;
3559 END IF;
3560
3561 IF p_utilization_rec.attribute5 = fnd_api.g_miss_char THEN
3562 x_complete_rec.attribute5 := NULL;
3563 END IF;
3564 IF p_utilization_rec.attribute5 IS NULL THEN
3565 x_complete_rec.attribute5 := l_utilization_rec.attribute5;
3566 END IF;
3567
3568 IF p_utilization_rec.attribute6 = fnd_api.g_miss_char THEN
3569 x_complete_rec.attribute6 := NULL;
3570 END IF;
3571 IF p_utilization_rec.attribute6 IS NULL THEN
3572 x_complete_rec.attribute6 := l_utilization_rec.attribute6;
3573 END IF;
3574
3575 IF p_utilization_rec.attribute7 = fnd_api.g_miss_char THEN
3576 x_complete_rec.attribute7 := NULL;
3577 END IF;
3578 IF p_utilization_rec.attribute7 IS NULL THEN
3579 x_complete_rec.attribute7 := l_utilization_rec.attribute7;
3580 END IF;
3581
3582 IF p_utilization_rec.attribute8 = fnd_api.g_miss_char THEN
3583 x_complete_rec.attribute8 := NULL;
3584 END IF;
3585 IF p_utilization_rec.attribute8 IS NULL THEN
3586 x_complete_rec.attribute8 := l_utilization_rec.attribute8;
3587 END IF;
3588
3589 IF p_utilization_rec.attribute9 = fnd_api.g_miss_char THEN
3590 x_complete_rec.attribute9 := NULL;
3591 END IF;
3592 IF p_utilization_rec.attribute9 IS NULL THEN
3593 x_complete_rec.attribute9 := l_utilization_rec.attribute9;
3594 END IF;
3595
3596 IF p_utilization_rec.attribute10 = fnd_api.g_miss_char THEN
3597 x_complete_rec.attribute10 := NULL;
3598 END IF;
3599 IF p_utilization_rec.attribute10 IS NULL THEN
3600 x_complete_rec.attribute10 := l_utilization_rec.attribute10;
3601 END IF;
3602
3603 IF p_utilization_rec.attribute11 = fnd_api.g_miss_char THEN
3604 x_complete_rec.attribute11 := NULL;
3605 END IF;
3606 IF p_utilization_rec.attribute11 IS NULL THEN
3607 x_complete_rec.attribute11 := l_utilization_rec.attribute11;
3608 END IF;
3609
3610 IF p_utilization_rec.attribute12 = fnd_api.g_miss_char THEN
3611 x_complete_rec.attribute12 := NULL;
3612 END IF;
3613 IF p_utilization_rec.attribute12 IS NULL THEN
3614 x_complete_rec.attribute12 := l_utilization_rec.attribute12;
3615 END IF;
3616
3617 IF p_utilization_rec.attribute13 = fnd_api.g_miss_char THEN
3618 x_complete_rec.attribute13 := NULL;
3619 END IF;
3620 IF p_utilization_rec.attribute13 IS NULL THEN
3621 x_complete_rec.attribute13 := l_utilization_rec.attribute13;
3622 END IF;
3623
3624 IF p_utilization_rec.attribute14 = fnd_api.g_miss_char THEN
3625 x_complete_rec.attribute14 := NULL;
3626 END IF;
3627 IF p_utilization_rec.attribute14 IS NULL THEN
3628 x_complete_rec.attribute14 := l_utilization_rec.attribute14;
3629 END IF;
3630
3631 IF p_utilization_rec.attribute15 = fnd_api.g_miss_char THEN
3632 x_complete_rec.attribute15 := NULL;
3633 END IF;
3634 IF p_utilization_rec.attribute15 IS NULL THEN
3635 x_complete_rec.attribute15 := l_utilization_rec.attribute15;
3636 END IF;
3637
3638 IF p_utilization_rec.adjustment_desc = fnd_api.g_miss_char THEN
3639 x_complete_rec.adjustment_desc := NULL;
3640 END IF;
3641 IF p_utilization_rec.adjustment_desc IS NULL THEN
3642 x_complete_rec.adjustment_desc := l_utilization_rec.adjustment_desc;
3643 END IF;
3644
3645 IF p_utilization_rec.plan_curr_amount = fnd_api.g_miss_num THEN
3646 x_complete_rec.plan_curr_amount := NULL;
3647 END IF;
3648 IF p_utilization_rec.plan_curr_amount IS NULL THEN
3649 x_complete_rec.plan_curr_amount := l_utilization_rec.plan_curr_amount;
3650 END IF;
3651
3652 IF p_utilization_rec.plan_curr_amount_remaining = fnd_api.g_miss_num THEN
3653 x_complete_rec.plan_curr_amount_remaining := NULL;
3654 END IF;
3655 IF p_utilization_rec.plan_curr_amount_remaining IS NULL THEN
3656 x_complete_rec.plan_curr_amount_remaining := l_utilization_rec.plan_curr_amount_remaining;
3657 END IF;
3658
3659 -- added for 11.5.9
3660 IF p_utilization_rec.scan_unit = fnd_api.g_miss_num THEN
3661 x_complete_rec.scan_unit := NULL;
3662 END IF;
3663 IF p_utilization_rec.scan_unit IS NULL THEN
3664 x_complete_rec.scan_unit := l_utilization_rec.scan_unit;
3665 END IF;
3666
3667 IF p_utilization_rec.scan_unit_remaining = fnd_api.g_miss_num THEN
3668 x_complete_rec.scan_unit_remaining := NULL;
3669 END IF;
3670 IF p_utilization_rec.scan_unit_remaining IS NULL THEN
3671 x_complete_rec.scan_unit_remaining := l_utilization_rec.scan_unit_remaining;
3672 END IF;
3673
3674 IF p_utilization_rec.activity_product_id = fnd_api.g_miss_num THEN
3675 x_complete_rec.activity_product_id := NULL;
3676 END IF;
3677 IF p_utilization_rec.activity_product_id IS NULL THEN
3678 x_complete_rec.activity_product_id := l_utilization_rec.activity_product_id;
3679 END IF;
3680
3681 IF p_utilization_rec.volume_offer_tiers_id = fnd_api.g_miss_num THEN
3682 x_complete_rec.volume_offer_tiers_id := NULL;
3683 END IF;
3684 IF p_utilization_rec.volume_offer_tiers_id IS NULL THEN
3685 x_complete_rec.volume_offer_tiers_id := l_utilization_rec.volume_offer_tiers_id;
3686 END IF;
3687
3688 -- yzhao: 03/20/2003 added gl_posted_flag
3689 IF p_utilization_rec.gl_posted_flag = fnd_api.g_miss_char THEN
3690 x_complete_rec.gl_posted_flag := NULL;
3691 END IF;
3692 IF p_utilization_rec.gl_posted_flag IS NULL THEN
3693 x_complete_rec.gl_posted_flag := l_utilization_rec.gl_posted_flag;
3694 END IF;
3695
3696 -- 11/04/2003 yzhao 11.5.10: added
3697 IF p_utilization_rec.billto_cust_account_id = fnd_api.g_miss_num THEN
3698 x_complete_rec.billto_cust_account_id := NULL;
3699 END IF;
3700 IF p_utilization_rec.billto_cust_account_id IS NULL THEN
3701 x_complete_rec.billto_cust_account_id := l_utilization_rec.billto_cust_account_id;
3702 END IF;
3703
3704 IF p_utilization_rec.reference_type = fnd_api.g_miss_char THEN
3705 x_complete_rec.reference_type := NULL;
3706 END IF;
3707 IF p_utilization_rec.reference_type IS NULL THEN
3708 x_complete_rec.reference_type := l_utilization_rec.reference_type;
3709 END IF;
3710
3711 IF p_utilization_rec.reference_id = fnd_api.g_miss_num THEN
3712 x_complete_rec.reference_id := NULL;
3713 END IF;
3714 IF p_utilization_rec.reference_id IS NULL THEN
3715 x_complete_rec.reference_id := l_utilization_rec.reference_id;
3716 END IF;
3717
3718 /*fix for bug 4778995
3719 IF p_utilization_rec.month_id = fnd_api.g_miss_num THEN
3720 x_complete_rec.month_id := NULL;
3721 END IF;
3722 IF p_utilization_rec.month_id IS NULL THEN
3723 x_complete_rec.month_id := l_utilization_rec.month_id;
3724 END IF;
3725
3726 IF p_utilization_rec.quarter_id = fnd_api.g_miss_num THEN
3727 x_complete_rec.quarter_id := NULL;
3728 END IF;
3729 IF p_utilization_rec.quarter_id IS NULL THEN
3730 x_complete_rec.quarter_id := l_utilization_rec.quarter_id;
3731 END IF;
3732
3733 IF p_utilization_rec.year_id = fnd_api.g_miss_num THEN
3734 x_complete_rec.year_id := NULL;
3735 END IF;
3736 IF p_utilization_rec.year_id IS NULL THEN
3737 x_complete_rec.year_id := l_utilization_rec.year_id;
3738 END IF;
3739 */
3740
3741 IF p_utilization_rec.order_line_id = fnd_api.g_miss_num THEN
3742 x_complete_rec.order_line_id := NULL;
3743 END IF;
3744 IF p_utilization_rec.order_line_id IS NULL THEN
3745 x_complete_rec.order_line_id := l_utilization_rec.order_line_id;
3746 END IF;
3747
3748 IF p_utilization_rec.orig_utilization_id = fnd_api.g_miss_num THEN
3749 x_complete_rec.orig_utilization_id := NULL;
3750 END IF;
3751 IF p_utilization_rec.orig_utilization_id IS NULL THEN
3752 x_complete_rec.orig_utilization_id := l_utilization_rec.orig_utilization_id;
3753 END IF;
3754
3755 -- R12: yzhao add
3756 IF p_utilization_rec.univ_curr_amount = fnd_api.g_miss_num THEN
3757 x_complete_rec.univ_curr_amount := NULL;
3758 END IF;
3759 IF p_utilization_rec.univ_curr_amount IS NULL THEN
3760 x_complete_rec.univ_curr_amount := l_utilization_rec.univ_curr_amount;
3761 END IF;
3762
3763 IF p_utilization_rec.univ_curr_amount_remaining = fnd_api.g_miss_num THEN
3764 x_complete_rec.univ_curr_amount_remaining := NULL;
3765 END IF;
3766 IF p_utilization_rec.univ_curr_amount_remaining IS NULL THEN
3767 x_complete_rec.univ_curr_amount_remaining := l_utilization_rec.univ_curr_amount_remaining;
3768 END IF;
3769
3770 IF p_utilization_rec.bill_to_site_use_id = fnd_api.g_miss_num THEN
3771 x_complete_rec.bill_to_site_use_id := NULL;
3772 END IF;
3773 IF p_utilization_rec.bill_to_site_use_id IS NULL THEN
3774 x_complete_rec.bill_to_site_use_id := l_utilization_rec.bill_to_site_use_id;
3775 END IF;
3776
3777 IF p_utilization_rec.ship_to_site_use_id = fnd_api.g_miss_num THEN
3778 x_complete_rec.ship_to_site_use_id := NULL;
3779 END IF;
3780 IF p_utilization_rec.ship_to_site_use_id IS NULL THEN
3781 x_complete_rec.ship_to_site_use_id := l_utilization_rec.ship_to_site_use_id;
3782 END IF;
3783 END complete_utilization_rec;
3784
3785 ---------------------------------------------------------------------
3786 -- PROCEDURE
3787 -- create_act_utilization
3788 --
3789 -- PURPOSE
3790 -- For create act budgets and utilization record.
3791 -- Called by manual fund adjustment.
3792 --
3793 -- PARAMETERS
3794 -- p_act_util_rec: the act budget record which contain information
3795 --- create act bugets record.
3796 -- p_act_util_rec: the act utilization record which contain information
3797 -- for utilization.
3798 -- NOTES
3799 -- 1. created by feliu on 02/25/2002.
3800 ---------------------------------------------------------------------
3801
3802
3803
3804 PROCEDURE create_act_utilization(
3805 p_api_version IN NUMBER
3806 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3807 ,p_commit IN VARCHAR2 := fnd_api.g_false
3808 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3809 ,x_return_status OUT NOCOPY VARCHAR2
3810 ,x_msg_count OUT NOCOPY NUMBER
3811 ,x_msg_data OUT NOCOPY VARCHAR2
3812 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
3813 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
3814 ,x_act_budget_id OUT NOCOPY NUMBER
3815 )IS
3816 l_api_version CONSTANT NUMBER := 1.0;
3817 l_api_name CONSTANT VARCHAR2 (30) := 'create_act_utilization';
3818 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
3819 || '.'
3820 || l_api_name;
3821 l_return_status VARCHAR2 (1);
3822 l_activity_id NUMBER;
3823 l_obj_ver_num NUMBER;
3824 l_old_request_amount NUMBER;
3825 l_old_parent_amount NUMBER;
3826
3827 /*
3828 CURSOR c_act_util_rec (
3829 p_used_by_id IN NUMBER
3830 ,p_used_by_type IN VARCHAR2
3831 ,p_parent_src_id IN NUMBER
3832 ) IS
3833 SELECT activity_budget_id, object_version_number,request_amount,parent_src_apprvd_amt
3834 FROM ozf_act_budgets
3835 WHERE act_budget_used_by_id = p_used_by_id
3836 AND arc_act_budget_used_by = p_used_by_type
3837 AND parent_source_id = p_parent_src_id
3838 AND transfer_type = 'UTILIZED';
3839 */
3840
3841
3842 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type := p_act_budgets_rec;
3843 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type := p_act_util_rec;
3844 l_offer_type VARCHAR2 (30);
3845 l_activity_product_id NUMBER;
3846 l_scan_value NUMBER;
3847
3848 CURSOR c_offer_type(p_offer_id IN NUMBER) IS
3849 SELECT offer_type FROM ozf_offers
3850 WHERE qp_list_header_id = p_offer_id;
3851
3852 CURSOR c_off_products (p_offer_id IN NUMBER,p_product_type VARCHAR2,
3853 p_product_id NUMBER,p_channel_id NUMBER) IS
3854 SELECT act.activity_product_id,act.scan_value
3855 FROM
3856 (SELECT activity_product_id,level_type_code level_code,
3857 DECODE (level_type_code, 'PRODUCT', inventory_item_id, category_id) product_id
3858 ,scan_value,channel_id
3859 FROM ams_act_products
3860 WHERE act_product_used_by_id = p_offer_id
3861 AND arc_act_product_used_by = 'OFFR'
3862 ) act
3863 WHERE act.level_code = p_product_type
3864 AND act.product_id = p_product_id
3865 AND act.channel_id = p_channel_id;
3866
3867 --Added for bug 7030415
3868 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
3869 SELECT exchange_rate_type
3870 FROM ozf_sys_parameters_all
3871 WHERE org_id = p_org_id;
3872
3873 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
3874 l_rate NUMBER;
3875
3876
3877 BEGIN
3878 --------------------- initialize -----------------------
3879 SAVEPOINT create_act_utilization;
3880 IF G_DEBUG THEN
3881 ozf_utility_pvt.debug_message ( l_full_name
3882 || ': start');
3883 END IF;
3884
3885 IF fnd_api.to_boolean (p_init_msg_list) THEN
3886 fnd_msg_pub.initialize;
3887 END IF;
3888
3889 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3890 RAISE fnd_api.g_exc_unexpected_error;
3891 END IF;
3892
3893 x_return_status := fnd_api.g_ret_sts_success;
3894
3895 IF l_act_budgets_rec.transfer_type = 'UTILIZED' THEN
3896 l_act_budgets_rec.request_currency :=
3897 ozf_actbudgets_pvt.get_object_currency (
3898 l_act_budgets_rec.arc_act_budget_used_by
3899 ,l_act_budgets_rec.act_budget_used_by_id
3900 ,l_return_status
3901 );
3902
3903 IF l_act_budgets_rec.parent_src_apprvd_amt IS NOT NULL THEN
3904 IF l_act_budgets_rec.request_currency <> l_act_budgets_rec.parent_src_curr THEN
3905 -- convert the src_curr_request amount to the act_used_by currency request amount.
3906 --Added for bug 7030415
3907 OPEN c_get_conversion_type(l_act_util_rec.org_id);
3908 FETCH c_get_conversion_type INTO l_exchange_rate_type;
3909 CLOSE c_get_conversion_type;
3910 ozf_utility_pvt.convert_currency (
3911 x_return_status=> l_return_status
3912 ,p_from_currency=> l_act_budgets_rec.parent_src_curr
3913 ,p_to_currency=> l_act_budgets_rec.request_currency
3914 ,p_conv_type=>l_exchange_rate_type --Added for bug 7030415
3915 ,p_from_amount=> l_act_budgets_rec.parent_src_apprvd_amt
3916 ,x_to_amount=> l_act_budgets_rec.request_amount
3917 ,x_rate=> l_rate
3918 );
3919
3920 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3921 RAISE fnd_api.g_exc_unexpected_error;
3922 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3923 RAISE fnd_api.g_exc_error;
3924 END IF;
3925 END IF;
3926 END IF;
3927
3928 END IF;
3929
3930 /*
3931 OPEN c_act_util_rec (p_act_budgets_rec.act_budget_used_by_id,
3932 p_act_budgets_rec.arc_act_budget_used_by,
3933 p_act_budgets_rec.parent_source_id);
3934 FETCH c_act_util_rec INTO l_activity_id, l_obj_ver_num, l_old_request_amount,l_old_parent_amount;
3935 CLOSE c_act_util_rec;
3936 */
3937 --get offer type
3938 IF l_act_budgets_rec.budget_source_type = 'OFFR' THEN
3939 OPEN c_offer_type(l_act_budgets_rec.budget_source_id);
3940 FETCH c_offer_type INTO l_offer_type;
3941 CLOSE c_offer_type;
3942 END IF;
3943
3944 --for scan data offer, activity_product_id, scan_unit,scan_unit_remaining are required.
3945 IF l_offer_type = 'SCAN_DATA' THEN
3946 --check if scan_type_id is null;
3947 IF l_act_util_rec.scan_type_id is null THEN
3948 ozf_utility_pvt.error_message('OZF_FUND_NO_SCAN_DATA_TYPE');
3949 END IF;
3950
3951 IF l_act_util_rec.product_id is null THEN
3952 ozf_utility_pvt.error_message('OZF_FUND_NO_PROD_ID');
3953 END IF;
3954
3955 OPEN c_off_products(l_act_budgets_rec.budget_source_id,l_act_util_rec.product_level_type,
3956 l_act_util_rec.product_id,l_act_util_rec.scan_type_id);
3957
3958 FETCH c_off_products INTO l_activity_product_id,l_scan_value;
3959 IF c_off_products%NOTFOUND THEN
3960 ozf_utility_pvt.error_message('OZF_FUND_ACT_PROD_ID_NOT_FOUND');
3961 END IF;
3962
3963 CLOSE c_off_products;
3964
3965 l_act_util_rec.activity_product_id := l_activity_product_id;
3966 l_act_util_rec.scan_unit := ROUND(l_act_budgets_rec.request_amount/l_scan_value);
3967 l_act_util_rec.scan_unit_remaining := l_act_util_rec.scan_unit;
3968 END IF;
3969
3970 ---For decrease utilization, request amount will be negative in act_budgets record.
3971 IF l_act_budgets_rec.transfer_type = 'UTILIZED' AND
3972 p_act_util_rec.adjustment_type IN ('DECREASE_EARNED', 'DECREASE_COMM_EARNED')
3973 THEN
3974 l_act_budgets_rec.request_amount := - l_act_budgets_rec.request_amount;
3975 l_act_budgets_rec.parent_src_apprvd_amt := - l_act_budgets_rec.parent_src_apprvd_amt;
3976 END IF;
3977
3978 ozf_fund_adjustment_pvt.process_act_budgets (
3979 x_return_status=> l_return_status,
3980 x_msg_count=> x_msg_count,
3981 x_msg_data=> x_msg_data,
3982 p_act_budgets_rec=> l_act_budgets_rec,
3983 p_act_util_rec=> l_act_util_rec,
3984 x_act_budget_id=> x_act_budget_id
3985 );
3986
3987 IF l_return_status = fnd_api.g_ret_sts_error THEN
3988 RAISE fnd_api.g_exc_error;
3989 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3990 RAISE fnd_api.g_exc_unexpected_error;
3991 END IF;
3992
3993 -- END IF;
3994
3995
3996 -- Check for commit
3997 IF fnd_api.to_boolean (p_commit) THEN
3998 COMMIT;
3999 END IF;
4000
4001 fnd_msg_pub.count_and_get (
4002 p_encoded=> fnd_api.g_false
4003 ,p_count=> x_msg_count
4004 ,p_data=> x_msg_data
4005 );
4006 IF G_DEBUG THEN
4007 ozf_utility_pvt.debug_message ( l_full_name
4008 || ': end');
4009 END IF;
4010
4011 EXCEPTION
4012 WHEN fnd_api.g_exc_error THEN
4013 ROLLBACK TO create_act_utilization;
4014 x_return_status := fnd_api.g_ret_sts_error;
4015 fnd_msg_pub.count_and_get (
4016 p_encoded=> fnd_api.g_false
4017 ,p_count=> x_msg_count
4018 ,p_data=> x_msg_data
4019 );
4020 WHEN fnd_api.g_exc_unexpected_error THEN
4021 ROLLBACK TO create_act_utilization;
4022 x_return_status := fnd_api.g_ret_sts_unexp_error;
4023 fnd_msg_pub.count_and_get (
4024 p_encoded=> fnd_api.g_false
4025 ,p_count=> x_msg_count
4026 ,p_data=> x_msg_data
4027 );
4028 WHEN OTHERS THEN
4029 ROLLBACK TO create_act_utilization;
4030 x_return_status := fnd_api.g_ret_sts_unexp_error;
4031
4032 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4033 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4034 END IF;
4035
4036 fnd_msg_pub.count_and_get (
4037 p_encoded=> fnd_api.g_false
4038 ,p_count=> x_msg_count
4039 ,p_data=> x_msg_data
4040 );
4041
4042
4043 END create_act_utilization;
4044
4045 END ozf_fund_utilized_pvt;
4046