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