1 PACKAGE BODY OZF_FUND_UTILIZED_PUB AS
2 /* $Header: OZFPFUTB.pls 120.17.12020000.3 2013/02/21 07:06:25 kdass ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_FUND_UTILIZED_PUB';
5 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- Validate_Items
10 --
11 -- PURPOSE
12 -- Validate adjustment record.
13 --
14 -- PARAMETERS
15 -- p_adj_rec: adjustment record to be validated
16 -- x_return_status: return status
17 --
18 -- HISTORY
19 -- 04/05/2005 kdass Created
20 -- 03/14/2005 psomyaju ER-6858324
21 ---------------------------------------------------------------------
22 PROCEDURE Validate_Items (
23 p_adj_rec IN OUT NOCOPY OZF_FUND_UTILIZED_PUB.adjustment_rec_type
24 ,x_return_status OUT NOCOPY VARCHAR2
25 )
26 IS
27 l_api_name VARCHAR(30) := 'Validate_Items';
28 l_fund_exists NUMBER := NULL;
29 l_fund_id NUMBER := NULL;
30 l_activity_id NUMBER := NULL;
31 l_valid_csch NUMBER := NULL;
32 l_valid_scantype_id NUMBER := NULL;
33 l_cust_type NUMBER := NULL;
34 l_dummy NUMBER := 0;
35 l_inv_org_id NUMBER := FND_PROFILE.VALUE ('AMS_ITEM_ORGANIZATION_ID');
36 l_site_org_id NUMBER := NULL;
37 l_org_for_product NUMBER := NULL;
38 l_offer_org_id NUMBER := NULL;
39 l_adjustment_type VARCHAR2(30);
40 l_adjustment_type_id NUMBER;
41 l_fund_curr_code VARCHAR(30);
42 l_sdr_offer NUMBER := NULL;
43 l_valid_curr NUMBER := NULL;
44 l_offer_curr_code VARCHAR2(15);
45
46 CURSOR c_fund_exists (p_fund_id IN NUMBER) IS
47 SELECT 1
48 FROM ozf_funds_all_b
49 WHERE fund_id = p_fund_id;
50
51 CURSOR c_fund_num_exists (p_fund_num IN VARCHAR2) IS
52 SELECT fund_id
53 FROM ozf_funds_all_b
54 WHERE fund_number = p_fund_num;
55
56 CURSOR c_valid_campaign (p_activity_id IN NUMBER) IS
57 SELECT campaign_id
58 FROM ams_campaigns_vl
59 WHERE active_flag = 'Y'
60 AND show_campaign_flag = 'Y'
61 AND campaign_id = p_activity_id;
62
63 CURSOR c_valid_event (p_activity_id IN NUMBER, p_approver_id IN NUMBER) IS
64 SELECT event.event_header_id
65 FROM ams_event_headers_vl event,
66 jtf_loc_hierarchies_vl loc,
67 ams_act_access_denorm acc
68 WHERE loc.location_type_code = 'COUNTRY'
69 AND event.active_flag='Y'
70 AND event.event_level='MAIN'
71 AND event.event_standalone_flag='N'
72 AND event.user_status_id NOT IN (6,9,7,27)
73 AND TO_NUMBER(event.country_code) = loc.location_hierarchy_id
74 AND acc.object_type = 'EVEH'
75 AND acc.object_id = event.event_header_id
76 AND acc.resource_id = p_approver_id
77 AND event.event_header_id = p_activity_id;
78
79 CURSOR c_valid_deliverable (p_activity_id IN NUMBER) IS
80 SELECT b.deliverable_id
81 FROM ams_deliverables_all_b b,
82 ams_deliverables_all_tl tl,
83 jtf_loc_hierarchies_vl c
84 WHERE c.location_type_code = 'COUNTRY'
85 AND b.active_flag='Y'
86 AND c.location_hierarchy_id = b.country_id
87 AND b.deliverable_id = tl.deliverable_id
88 AND tl.language =userenv('LANG')
89 AND b.deliverable_id = p_activity_id;
90
91 CURSOR c_valid_offer (p_activity_id IN NUMBER) IS
92 SELECT list_header_id, orig_org_id
93 FROM qp_list_headers_all_b
94 WHERE list_header_id = p_activity_id;
95
96 --kdass
97 CURSOR c_sdr_offer (p_activity_id IN NUMBER) IS
98 SELECT offer_id
99 FROM ozf_sd_request_headers_all_b
100 WHERE offer_id = p_activity_id;
101
102 CURSOR c_valid_currency (p_curr_code IN VARCHAR2) IS
103 SELECT 1
104 FROM fnd_currencies
105 WHERE enabled_flag = 'Y'
106 AND NVL(start_date_active, SYSDATE) <= SYSDATE
107 AND NVL(end_date_active, SYSDATE) >= SYSDATE
108 AND currency_code = p_curr_code;
109
110 CURSOR c_valid_csch (p_activity_id IN NUMBER) IS
111 SELECT 1
112 FROM ams_campaign_schedules_vl
113 WHERE campaign_id = p_activity_id;
114
115 CURSOR c_valid_cust_type (p_cust_type IN VARCHAR2) IS
116 SELECT 1
117 FROM ozf_lookups
118 WHERE lookup_type = 'OZF_VO_CUSTOMER_TYPES'
119 AND enabled_flag = 'Y'
120 AND lookup_code = p_cust_type;
121
122 CURSOR c_cust_id_buyer (p_cust_id IN NUMBER) IS
123 SELECT max(cust_account_id)
124 FROM hz_cust_accounts
125 WHERE party_id = p_cust_id
126 AND status= 'A';
127
128 CURSOR c_cust_id_billto (p_cust_id IN NUMBER) IS
129 SELECT hzas.cust_account_id, hzas.org_id
130 FROM hz_cust_site_uses_all hzs,
131 hz_cust_acct_sites_all hzas
132 WHERE hzs.cust_acct_site_id = hzas.cust_acct_site_id
133 AND hzs.site_use_id = p_cust_id;
134
135 CURSOR c_cust_id_shipto (p_cust_id IN NUMBER) IS
136 SELECT hzas.cust_account_id, hzs.bill_to_site_use_id, hzas.org_id
137 FROM hz_cust_site_uses_all hzs,
138 hz_cust_acct_sites_all hzas
139 WHERE hzs.cust_acct_site_id = hzas.cust_acct_site_id
140 AND hzs.site_use_id = p_cust_id;
141
142 CURSOR c_valid_scantype_id (p_activity_id IN NUMBER, p_scan_type_id IN NUMBER) IS
143 SELECT 1
144 FROM ams_media_channels_vl med, ozf_offers off
145 WHERE med.media_id = off.activity_media_id(+)
146 AND qp_list_header_id = p_activity_id
147 AND channel_id = p_scan_type_id;
148
149 --08-MAY-2006 kdass bug 5199585 SQL ID# 17777526 - added last condition so that table uses index
150 /*CURSOR c_valid_prod_family (p_prod_name IN VARCHAR2) IS
151 SELECT category_id
152 FROM eni_prod_den_hrchy_parents_v
153 WHERE category_desc = p_prod_name
154 AND NVL(category_id, 0) = category_id;*/
155
156 --nirprasa, the category passed to the API was being validated incorrectly.
157 --Bug 8785946, FP of 8779543
158 CURSOR c_valid_prod_family (p_prod_name IN VARCHAR2) IS
159 SELECT c.category_id
160 FROM mtl_default_category_sets a ,
161 mtl_category_sets_b b ,
162 mtl_categories_v c ,
163 ENI_PROD_DEN_HRCHY_PARENTS_V d
164 WHERE a.functional_area_id in (7,11)
165 AND a.category_set_id = b.category_set_id
166 AND b.structure_id = c.structure_id
167 AND c.category_id = d.category_id(+)
168 AND UPPER(NVL(d.category_desc, c.category_concat_segs)) = UPPER(p_prod_name);
169
170 CURSOR c_valid_product (p_prod_name IN VARCHAR2, p_org_id IN NUMBER) IS
171 SELECT inventory_item_id
172 FROM mtl_system_items_kfv
173 WHERE organization_id = p_org_id
174 -- AND trim(padded_concatenated_segments) = p_prod_name;
175 AND concatenated_segments = p_prod_name; --AMITAMKU Bug fix-14248296
176
177 CURSOR c_adj_type_id (p_adj_type IN VARCHAR2, p_org_id IN NUMBER) IS
178 SELECT max(claim_type_id)
179 FROM ozf_claim_types_all_vl
180 WHERE adjustment_type = p_adj_type
181 AND claim_class = 'ADJ'
182 AND claim_type_id > -1
183 AND org_id = p_org_id;
184 --nirprasa,ER 8399134
185 CURSOR c_adj_type (p_adj_type_id IN NUMBER, p_org_id IN NUMBER) IS
186 SELECT adjustment_type
187 FROM ozf_claim_types_all_vl
188 WHERE claim_type_id = p_adj_type_id
189 AND claim_class = 'ADJ'
190 AND org_id = p_org_id;
191
192
193 CURSOR c_approver_id (p_fund_id IN NUMBER) IS
194 SELECT owner
195 FROM ozf_funds_all_vl
196 WHERE fund_id = p_fund_id;
197
198 CURSOR c_curr_code (p_fund_id IN NUMBER) IS
199 SELECT currency_code_tc
200 FROM ozf_funds_all_b
201 WHERE fund_id = p_fund_id;
202
203 --Order_Line_Id validation added for ER-6858324
204 CURSOR c_order_line (p_order_line_id IN NUMBER, p_header_id IN NUMBER) IS
205 SELECT 1
206 FROM oe_order_lines_all
207 WHERE line_id = p_order_line_id
208 AND header_id = p_header_id;
209
210 CURSOR c_org_id (p_org_id IN NUMBER, p_fund_id IN NUMBER) IS
211 SELECT 1
212 FROM hr_operating_units hr, ozf_funds_all_b fund
213 WHERE fund.fund_id = p_fund_id
214 AND hr.organization_id = p_org_id
215 AND hr.set_of_books_id = fund.ledger_id;
216
217 CURSOR c_org_order (p_header_id IN NUMBER) IS
218 SELECT org_id
219 FROM oe_order_headers_all
220 WHERE header_id = p_header_id;
221
222 CURSOR c_inventory_org (p_org_id IN NUMBER) IS
223 SELECT parameter_value
224 FROM oe_sys_parameters_all
225 WHERE parameter_code = 'MASTER_ORGANIZATION_ID'
226 AND org_id = p_org_id;
227
228 --nirprasa,ER 8399134
229 CURSOR c_org_order_line (p_order_line_id IN NUMBER) IS
230 SELECT h.org_id
231 FROM oe_order_headers_all h, oe_order_lines_all l
232 WHERE h.header_id = l.header_id
233 AND l.line_id = p_order_line_id ;
234
235 CURSOR c_offer_info (p_activity_id IN NUMBER) IS
236 SELECT beneficiary_account_id,autopay_party_attr,autopay_party_id
237 FROM ozf_offers
238 WHERE qp_list_header_id = p_activity_id;
239
240 -- Cursor to get the org_id for third party order
241 CURSOR c_tp_order_org_id (p_line_id IN NUMBER) IS
242 SELECT org_id FROM ozf_resale_lines_all
243 WHERE resale_line_id = p_line_id;
244
245 -- Cursor to get the org_id for purchase order
246 CURSOR c_purchase_order_org_id (p_header_id IN NUMBER) IS
247 SELECT org_id FROM po_headers_all
248 WHERE po_header_id = p_header_id;
249
250 -- Cursor to get the org_id for invoice
251 CURSOR c_invoice_org_id (p_cust_trx_id IN NUMBER)IS
252 SELECT org_id FROM ar_payment_schedules_all
253 WHERE customer_trx_id = p_cust_trx_id;
254
255 -- get sites org id type
256 CURSOR c_benef_org_id (p_site_use_id IN NUMBER) IS
257 SELECT org_id
258 FROM hz_cust_site_uses_all
259 WHERE site_use_id = p_site_use_id;
260
261 --nirprasa,ER 8399134
262 CURSOR c_get_offer_currency (p_activity_id IN NUMBER) IS
263 SELECT NVL(transaction_currency_code,fund_request_curr_code) fund_request_curr_code,
264 transaction_currency_code
265 FROM ozf_offers
266 WHERE qp_list_header_id=p_activity_id;
267
268 CURSOR c_get_order_currency (p_document_number IN NUMBER) IS
269 SELECT transactional_curr_code
270 FROM oe_order_headers_all
271 WHERE header_id = p_document_number;
272
273 CURSOR c_get_tp_order_currency (p_document_number IN NUMBER) IS
274 SELECT currency_code
275 FROM ozf_resale_lines_all
276 WHERE resale_line_id = p_document_number;
277
278 CURSOR c_get_txn_currency (p_document_number IN NUMBER) IS
279 SELECT invoice_currency_code
280 FROM ra_customer_trx_all
281 WHERE customer_trx_id = p_document_number;
282
283 CURSOR c_get_pcho_currency (p_document_number IN NUMBER) IS
284 SELECT currency_code FROM po_headers_all
285 WHERE po_header_id = p_document_number;
286
287 CURSOR c_get_header_id(p_order_line_id IN NUMBER) IS
288 SELECT oh.transactional_curr_code
289 FROM oe_order_lines_all ol, oe_order_headers_all oh
290 WHERE ol.line_id = p_order_line_id
291 AND ol.header_id = oh.header_id;
292
293 CURSOR c_get_camp_currency (p_activity_id IN NUMBER) IS
294 SELECT transaction_currency_code FROM ams_campaigns_vl
295 WHERE campaign_id = p_activity_id;
296
297 CURSOR c_get_csch_currency (p_activity_id IN NUMBER) IS
298 SELECT transaction_currency_code F
299 FROM ams_campaign_schedules_vl
300 WHERE schedule_id = p_activity_id;
301
302 CURSOR c_get_delv_currency (p_activity_id IN NUMBER) IS
303 SELECT transaction_currency_code
304 FROM ams_deliverables_vl
305 WHERE deliverable_id = p_activity_id;
306
307 CURSOR c_get_eveh_currency (p_activity_id IN NUMBER) IS
308 SELECT currency_code_tc FROM ams_event_headers_vl
309 WHERE event_header_id = p_activity_id;
310
311 CURSOR c_get_eveo_currency (p_activity_id IN NUMBER) IS
312 SELECT currency_code_tc FROM ams_event_offers_vl
313 WHERE event_offer_id = p_activity_id;
314
315 l_offer_info c_offer_info%ROWTYPE;
316 l_offer_currency c_get_offer_currency%ROWTYPE;
317 l_document_curr VARCHAR2(30);
318 l_header_id NUMBER;
319 --end ER 8399134 code
320 BEGIN
321
322 --check if the fund id or fund number is valid
323 IF p_adj_rec.fund_id <> fnd_api.g_miss_num AND p_adj_rec.fund_id IS NOT NULL THEN
324 --check if the input fund_id is valid
325 OPEN c_fund_exists (p_adj_rec.fund_id);
326 FETCH c_fund_exists INTO l_fund_exists;
327 CLOSE c_fund_exists;
328
329 IF l_fund_exists IS NULL THEN
330 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
331 fnd_message.set_name('OZF', 'OZF_INVALID_FUND_ID');
332 fnd_msg_pub.add;
333 END IF;
334 x_return_status := fnd_api.g_ret_sts_error;
335 RETURN;
336 END IF;
337 ELSIF p_adj_rec.fund_number <> fnd_api.g_miss_num AND p_adj_rec.fund_number IS NOT NULL THEN
338 --check if the input fund_number is valid
339 OPEN c_fund_num_exists (p_adj_rec.fund_number);
340 FETCH c_fund_num_exists INTO l_fund_id;
341 CLOSE c_fund_num_exists;
342
343 IF l_fund_id IS NOT NULL THEN
344 p_adj_rec.fund_id := l_fund_id;
345 ELSE
346 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
347 fnd_message.set_name('OZF', 'OZF_INVALID_FUND_NUM');
348 fnd_msg_pub.add;
349 END IF;
350 x_return_status := fnd_api.g_ret_sts_error;
351 RETURN;
352 END IF;
353 ELSE
354 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
355 fnd_message.set_name('OZF', 'OZF_NO_FUND_ID');
356 fnd_msg_pub.add;
357 END IF;
358 x_return_status := fnd_api.g_ret_sts_error;
359 RETURN;
360 END IF;
361
362 IF p_adj_rec.adjustment_type = fnd_api.g_miss_char OR p_adj_rec.adjustment_type IS NULL THEN
363 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
364 fnd_message.set_name('OZF', 'OZF_FUND_NO_ADJ_TYPE');
365 fnd_msg_pub.add;
366 END IF;
367 x_return_status := fnd_api.g_ret_sts_error;
368 RETURN;
369 END IF;
370
371 IF p_adj_rec.adjustment_type NOT IN ('DECREASE_COMM_EARNED', 'DECREASE_COMMITTED', 'DECREASE_EARNED',
372 'STANDARD', 'DECREASE_PAID', 'INCREASE_PAID') THEN
373 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
374 fnd_message.set_name('OZF', 'OZF_INVALID_ADJ_TYPE');
375 fnd_msg_pub.add;
376 END IF;
377 x_return_status := fnd_api.g_ret_sts_error;
378 RETURN;
379 END IF;
380
381 mo_global.init('OZF');
382
383
384 IF (p_adj_rec.amount = fnd_api.g_miss_num OR p_adj_rec.amount IS NULL)
385 --nirprasa,ER 8399134 add this condition since user can now pass plan_amount also
386 AND (p_adj_rec.plan_amount = fnd_api.g_miss_num OR p_adj_rec.plan_amount IS NULL) THEN
387 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
388 fnd_message.set_name('OZF', 'OZF_FUND_NO_ADJ_AMT');
389 fnd_msg_pub.add;
390 END IF;
391 x_return_status := fnd_api.g_ret_sts_error;
392 RETURN;
393 END IF;
394
395 IF p_adj_rec.fund_id IS NOT NULL THEN
396 OPEN c_curr_code(p_adj_rec.fund_id);
397 FETCH c_curr_code INTO l_fund_curr_code;
398 CLOSE c_curr_code;
399 --nirprasa,ER 8399134 validate if p_adj_rec.currency_code is passed
400 IF p_adj_rec.currency_code IS NULL OR p_adj_rec.currency_code = fnd_api.g_miss_char THEN
401 p_adj_rec.currency_code := l_fund_curr_code;
402 ELSIF p_adj_rec.currency_code <> l_fund_curr_code THEN
403 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
404 fnd_message.set_name('OZF', 'OZF_INVALID_FUND_CURR_CODE');
405 fnd_msg_pub.add;
406 END IF;
407 x_return_status := fnd_api.g_ret_sts_error;
408 RETURN;
409 END IF;
410 END IF;
411 --end ER 8399134 code changes
412
413 IF p_adj_rec.activity_type NOT IN ('CAMP', 'DELV', 'EVEH', 'OFFR') THEN
414 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
415 fnd_message.set_name('OZF', 'OZF_INVALID_ACTIVITY_TYPE');
416 fnd_msg_pub.add;
417 END IF;
418 x_return_status := fnd_api.g_ret_sts_error;
419 RETURN;
420 END IF;
421
422 IF p_adj_rec.activity_type = 'CAMP' THEN
423 OPEN c_valid_campaign (p_adj_rec.activity_id);
424 FETCH c_valid_campaign INTO l_activity_id;
425 CLOSE c_valid_campaign;
426 ELSIF p_adj_rec.activity_type = 'DELV' THEN
427 OPEN c_valid_deliverable (p_adj_rec.activity_id);
428 FETCH c_valid_deliverable INTO l_activity_id;
429 CLOSE c_valid_deliverable;
430 ELSIF p_adj_rec.activity_type = 'EVEH' THEN
431 OPEN c_valid_event (p_adj_rec.activity_id, p_adj_rec.approver_id);
432 FETCH c_valid_event INTO l_activity_id;
433 CLOSE c_valid_event;
434 ELSIF p_adj_rec.activity_type = 'OFFR' THEN
435 OPEN c_valid_offer (p_adj_rec.activity_id);
436 FETCH c_valid_offer INTO l_activity_id, l_offer_org_id;
437 CLOSE c_valid_offer;
438 END IF;
439
440 IF l_activity_id IS NULL THEN
441 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
442 fnd_message.set_name('OZF', 'OZF_INVALID_ACTIVITY_ID');
443 fnd_msg_pub.add;
444 END IF;
445 x_return_status := fnd_api.g_ret_sts_error;
446 RETURN;
447 END IF;
448
449 IF p_adj_rec.activity_type = 'CAMP' AND p_adj_rec.camp_schedule_id <> fnd_api.g_miss_num
450 AND p_adj_rec.camp_schedule_id IS NOT NULL THEN
451
452 OPEN c_valid_csch (p_adj_rec.activity_id);
453 FETCH c_valid_csch INTO l_valid_csch;
454 CLOSE c_valid_csch;
455
456 IF l_valid_csch IS NULL THEN
457 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
458 fnd_message.set_name('OZF', 'OZF_INVALID_CSCH_ID');
459 fnd_msg_pub.add;
460 END IF;
461 x_return_status := fnd_api.g_ret_sts_error;
462 RETURN;
463 END IF;
464 END IF;
465
466 IF p_adj_rec.customer_type <> fnd_api.g_miss_char AND p_adj_rec.customer_type IS NOT NULL THEN
467
468 OPEN c_valid_cust_type (p_adj_rec.customer_type);
469 FETCH c_valid_cust_type INTO l_cust_type;
470 CLOSE c_valid_cust_type;
471
472 IF l_cust_type IS NULL THEN
473 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
474 fnd_message.set_name('OZF', 'OZF_INVALID_CUST_TYPE');
475 fnd_msg_pub.add;
476 END IF;
477 x_return_status := fnd_api.g_ret_sts_error;
478 RETURN;
479 END IF;
480
481 IF p_adj_rec.customer_type = 'CUSTOMER' THEN
482 p_adj_rec.cust_account_id := p_adj_rec.cust_id;
483 p_adj_rec.bill_to_site_use_id := NULL;
484 p_adj_rec.ship_to_site_use_id := NULL;
485 ELSIF p_adj_rec.customer_type = 'BUYER' THEN
486
487 OPEN c_cust_id_buyer (p_adj_rec.cust_id);
488 FETCH c_cust_id_buyer INTO p_adj_rec.cust_account_id;
489 CLOSE c_cust_id_buyer;
490
491 p_adj_rec.bill_to_site_use_id := NULL;
492 p_adj_rec.ship_to_site_use_id := NULL;
493 ELSIF p_adj_rec.customer_type = 'CUSTOMER_BILL_TO' THEN
494
495 OPEN c_cust_id_billto (p_adj_rec.cust_id);
496 FETCH c_cust_id_billto INTO p_adj_rec.cust_account_id, l_site_org_id;
497 CLOSE c_cust_id_billto;
498
499 p_adj_rec.bill_to_site_use_id := p_adj_rec.cust_id;
500 p_adj_rec.ship_to_site_use_id := NULL;
501 ELSIF p_adj_rec.customer_type = 'SHIP_TO' THEN
502
503 OPEN c_cust_id_shipto (p_adj_rec.cust_id);
504 FETCH c_cust_id_shipto INTO p_adj_rec.cust_account_id, p_adj_rec.bill_to_site_use_id, l_site_org_id;
505 CLOSE c_cust_id_shipto;
506
507 p_adj_rec.ship_to_site_use_id := p_adj_rec.cust_id;
508 END IF;
509
510 IF p_adj_rec.cust_account_id = fnd_api.g_miss_num OR p_adj_rec.cust_account_id IS NULL THEN
511
512 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
513 fnd_message.set_name('OZF', 'OZF_INVALID_CUST_ID');
514 fnd_msg_pub.add;
515 END IF;
516 x_return_status := fnd_api.g_ret_sts_error;
517 RETURN;
518 END IF;
519
520 END IF;
521
522 IF p_adj_rec.document_type <> fnd_api.g_miss_char AND p_adj_rec.document_type IS NOT NULL THEN
523 IF p_adj_rec.document_type NOT IN ('INVOICE', 'ORDER', 'PCHO', 'TP_ORDER') THEN
524
525 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
526 fnd_message.set_name('OZF', 'OZF_INVALID_DOCUMENT_TYPE');
527 fnd_msg_pub.add;
528 END IF;
529 x_return_status := fnd_api.g_ret_sts_error;
530 RETURN;
531 END IF;
532
533 IF p_adj_rec.document_number = fnd_api.g_miss_num OR p_adj_rec.document_number IS NULL THEN
534
535 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
536 fnd_message.set_name('OZF', 'OZF_NO_DOCUMENT_NUM');
537 fnd_msg_pub.add;
538 END IF;
539 x_return_status := fnd_api.g_ret_sts_error;
540 RETURN;
541 END IF;
542 END IF;
543
544 -- for SCAN_DATA type of offer
545 IF p_adj_rec.scan_type_id <> fnd_api.g_miss_num AND p_adj_rec.scan_type_id IS NOT NULL THEN
546
547 OPEN c_valid_scantype_id (p_adj_rec.activity_id, p_adj_rec.scan_type_id);
548 FETCH c_valid_scantype_id INTO l_valid_scantype_id;
549 CLOSE c_valid_scantype_id;
550
551 IF l_valid_scantype_id IS NULL THEN
552 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
553 fnd_message.set_name('OZF', 'OZF_INVALID_SCANTYPE_ID');
554 fnd_msg_pub.add;
555 END IF;
556 x_return_status := fnd_api.g_ret_sts_error;
557 RETURN;
558 END IF;
559 END IF;
560
561 IF p_adj_rec.activity_type = 'OFFR' THEN
562 IF p_adj_rec.product_level_type <> fnd_api.g_miss_char AND p_adj_rec.product_level_type IS NOT NULL THEN
563
564 IF p_adj_rec.product_level_type NOT IN ('FAMILY', 'PRODUCT') THEN
565 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
566 fnd_message.set_name('OZF', 'OZF_INVALID_PROD_LEVEL');
567 fnd_msg_pub.add;
568 END IF;
569 x_return_status := fnd_api.g_ret_sts_error;
570 RETURN;
571 END IF;
572
573 IF G_DEBUG THEN
574 ozf_utility_pvt.debug_message('p_adj_rec.product_id: ' || p_adj_rec.product_id);
575 ozf_utility_pvt.debug_message('p_adj_rec.product_name: ' || p_adj_rec.product_name);
576 ozf_utility_pvt.debug_message('l_inv_org_id: ' || l_inv_org_id);
577 END IF;
578
579 IF p_adj_rec.product_name = fnd_api.g_miss_char OR p_adj_rec.product_name IS NULL THEN
580 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
581 fnd_message.set_name('OZF', 'OZF_NO_PROD_NAME');
582 fnd_msg_pub.add;
583 END IF;
584 x_return_status := fnd_api.g_ret_sts_error;
585 RETURN;
586 ELSE
587 IF p_adj_rec.product_level_type = 'FAMILY' THEN
588 OPEN c_valid_prod_family (p_adj_rec.product_name);
589 FETCH c_valid_prod_family INTO p_adj_rec.product_id;
590 CLOSE c_valid_prod_family;
591 ELSIF p_adj_rec.product_level_type = 'PRODUCT' THEN
592
593 /*07-APR-09 kdass bug 8402334 - used Inventory Org instead of Operating Unit
594 Derive Inventory Org in precedence - Order's OU, Offer's OU, profile AMS_ITEM_ORGANIZATION_ID
595 */
596 IF p_adj_rec.document_type <> fnd_api.g_miss_char AND p_adj_rec.document_type IS NOT NULL
597 AND p_adj_rec.document_type = 'ORDER' THEN
598
599 OPEN c_org_order (p_adj_rec.document_number);
600 FETCH c_org_order INTO l_org_for_product;
601 CLOSE c_org_order;
602
603 ELSIF l_offer_org_id IS NOT NULL THEN
604 l_org_for_product := l_offer_org_id;
605 END IF;
606
607 IF l_org_for_product IS NOT NULL THEN
608 OPEN c_inventory_org (l_org_for_product);
609 FETCH c_inventory_org INTO l_inv_org_id;
610 CLOSE c_inventory_org;
611
612 IF G_DEBUG THEN
613 ozf_utility_pvt.debug_message('l_inv_org_id: ' || l_inv_org_id);
614 END IF;
615 END IF;
616
617 OPEN c_valid_product (p_adj_rec.product_name, l_inv_org_id);
618 FETCH c_valid_product INTO p_adj_rec.product_id;
619 CLOSE c_valid_product;
620 END IF;
621
622 IF p_adj_rec.product_id IS NULL THEN
623 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
624 fnd_message.set_name('OZF', 'OZF_INVALID_PROD');
625 fnd_msg_pub.add;
626 END IF;
627 x_return_status := fnd_api.g_ret_sts_error;
628 RETURN;
629 END IF;
630 END IF;
631 END IF;
632 ELSE
633 p_adj_rec.product_level_type := NULL;
634 p_adj_rec.product_id := NULL;
635 END IF;
636
637 IF p_adj_rec.adjustment_type IN ('INCREASE_PAID', 'DECREASE_PAID') THEN
638 IF p_adj_rec.gl_account_credit = fnd_api.g_miss_num OR p_adj_rec.gl_account_credit IS NULL THEN
639 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
640 fnd_message.set_name('OZF', 'OZF_NO_GL_CREDIT_ACCT');
641 fnd_msg_pub.add;
642 END IF;
643 x_return_status := fnd_api.g_ret_sts_error;
644 RETURN;
645 END IF;
646
647 IF p_adj_rec.gl_account_debit = fnd_api.g_miss_num OR p_adj_rec.gl_account_debit IS NULL THEN
648 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
649 fnd_message.set_name('OZF', 'OZF_NO_GL_DEBIT_ACCT');
650 fnd_msg_pub.add;
651 END IF;
652 x_return_status := fnd_api.g_ret_sts_error;
653 RETURN;
654 END IF;
655 END IF;
656
657 OPEN c_approver_id (p_adj_rec.fund_id);
658 FETCH c_approver_id INTO p_adj_rec.approver_id;
659 CLOSE c_approver_id;
660
661 --ER 9382547
662 --ER 13069257
663
664 IF p_adj_rec.skip_acct_gen_flag <> fnd_api.g_miss_char AND p_adj_rec.skip_acct_gen_flag IS NOT NULL THEN
665
666 IF p_adj_rec.skip_acct_gen_flag NOT IN ('F', 'T') THEN
667 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
668 fnd_message.set_name('OZF', 'OZF_INVALID_ACCT_GEN_FLAG');
669 fnd_msg_pub.add;
670 END IF;
671 x_return_status := fnd_api.g_ret_sts_error;
672 RETURN;
673 END IF;
674 END IF;
675
676 IF p_adj_rec.document_type <> fnd_api.g_miss_char AND p_adj_rec.document_type IS NOT NULL
677 AND p_adj_rec.document_type = 'ORDER' AND p_adj_rec.order_line_id <> fnd_api.g_miss_num
678 AND p_adj_rec.order_line_id IS NOT NULL THEN
679
680 --07-APR-09 kdass bug 8402334 - added document_number to the cursor
681 OPEN c_order_line(p_adj_rec.order_line_id, p_adj_rec.document_number);
682 FETCH c_order_line INTO l_dummy;
683 IF c_order_line%NOTFOUND THEN
684 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
685 fnd_message.set_name('OZF', 'OZF_INVALID_ORDER_LINE');
686 fnd_msg_pub.add;
687 END IF;
688 x_return_status := fnd_api.g_ret_sts_error;
689 RETURN;
690 END IF;
691 CLOSE c_order_line;
692 END IF;
693
694 /*07-APR-09 kdass bug 8402334
695 OU validation rules:
696 1) OU should be part of Budget's ledger
697 2) OU should be same as bill to/ ship to site's OU
698 3) OU should be same as Offer's OU
699 */
700 IF p_adj_rec.org_id <> fnd_api.g_miss_num AND p_adj_rec.org_id IS NOT NULL THEN
701
702 --nirprasa,ER 8399134 if fund_id is not passed then this will always evaluate to false.
703 IF p_adj_rec.fund_id <> fnd_api.g_miss_num AND p_adj_rec.fund_id IS NOT NULL THEN
704 OPEN c_org_id(p_adj_rec.org_id, p_adj_rec.fund_id);
705 FETCH c_org_id INTO l_dummy;
706 IF c_org_id%NOTFOUND THEN
707 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
708 fnd_message.set_name('OZF', 'OZF_INVALID_ORG_LEDGER');
709 fnd_msg_pub.add;
710 END IF;
711 x_return_status := fnd_api.g_ret_sts_error;
712 RETURN;
713 END IF;
714 CLOSE c_org_id;
715 END IF;
716
717 IF l_site_org_id IS NOT NULL AND l_site_org_id <> p_adj_rec.org_id THEN
718 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
719 fnd_message.set_name('OZF', 'OZF_INVALID_ORG_SITE');
720 fnd_msg_pub.add;
721 END IF;
722 x_return_status := fnd_api.g_ret_sts_error;
723 RETURN;
724 END IF;
725
726 IF l_offer_org_id IS NOT NULL AND l_offer_org_id <> p_adj_rec.org_id THEN
727 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
728 fnd_message.set_name('OZF', 'OZF_INVALID_ORG_OFFER');
729 fnd_msg_pub.add;
730 END IF;
731 x_return_status := fnd_api.g_ret_sts_error;
732 RETURN;
733 END IF;
734
735 ELSE
736 --nirprasa,ER 8399134
737 /* Order for OU assignemnt
738 1) Beneficiary OU
739 2) Document OU
740 3) Offer OU
741 Added else condition to identify the org_id if not passed to the API.
742 org_id will be used to validate adjustment_type_id id passed to the API.
743 */
744 OPEN c_offer_info (p_adj_rec.activity_id);
745 FETCH c_offer_info INTO l_offer_info;
746 CLOSE c_offer_info;
747
748 IF l_offer_info.beneficiary_account_id IS NOT NULL AND
749 l_offer_info.autopay_party_attr <> 'CUSTOMER' AND
750 l_offer_info.autopay_party_attr IS NOT NULL THEN
751 OPEN c_benef_org_id (l_offer_info.autopay_party_id);
752 FETCH c_benef_org_id INTO p_adj_rec.org_id ;
753 CLOSE c_benef_org_id;
754 ELSIF p_adj_rec.document_type <> fnd_api.g_miss_char AND p_adj_rec.document_type IS NOT NULL THEN
755 IF p_adj_rec.document_type = 'ORDER' THEN
756 OPEN c_org_order (p_adj_rec.document_number);
757 FETCH c_org_order INTO p_adj_rec.org_id ;
758 CLOSE c_org_order;
759 ELSIF p_adj_rec.document_type = 'PCHO' THEN
760 OPEN c_purchase_order_org_id( p_adj_rec.document_number) ;
761 FETCH c_purchase_order_org_id INTO p_adj_rec.org_id ;
762 CLOSE c_purchase_order_org_id ;
763 ELSIF p_adj_rec.document_type = 'TP_ORDER' THEN
764 OPEN c_tp_order_org_id( p_adj_rec.document_number) ;
765 FETCH c_tp_order_org_id INTO p_adj_rec.org_id ;
766 CLOSE c_tp_order_org_id ;
767 ELSIF p_adj_rec.document_type = 'INVOICE' THEN
768 OPEN c_invoice_org_id( p_adj_rec.document_number) ;
769 FETCH c_invoice_org_id INTO p_adj_rec.org_id ;
770 CLOSE c_invoice_org_id ;
771 END IF;
772 ELSIF p_adj_rec.order_line_id <> fnd_api.g_miss_num AND p_adj_rec.order_line_id IS NOT NULL THEN
773 OPEN c_org_order_line (p_adj_rec.order_line_id);
774 FETCH c_org_order_line INTO p_adj_rec.org_id ;
775 CLOSE c_org_order_line;
776 END IF;
777 IF l_offer_org_id IS NOT NULL AND l_offer_org_id <> p_adj_rec.org_id THEN
778 p_adj_rec.org_id := l_offer_org_id;
779 END IF;
780 END IF;
781
782 IF p_adj_rec.adjustment_type = 'INCREASE_PAID' THEN
783 p_adj_rec.adjustment_type_id := -12;
784
785 ELSIF p_adj_rec.adjustment_type = 'DECREASE_PAID' THEN
786 p_adj_rec.adjustment_type_id := -13;
787
788 ELSE
789
790 --nirprasa,ER 8399134 use the adjustment_type_id passed to the API. Currently it is ignored.
791 OPEN c_adj_type_id(p_adj_rec.adjustment_type,p_adj_rec.org_id);
792 FETCH c_adj_type_id INTO l_adjustment_type_id;
793 CLOSE c_adj_type_id;
794
795 IF p_adj_rec.adjustment_type_id IS NULL THEN
796 p_adj_rec.adjustment_type_id := l_adjustment_type_id;
797 ELSE
798 OPEN c_adj_type(p_adj_rec.adjustment_type_id,p_adj_rec.org_id);
799 FETCH c_adj_type INTO l_adjustment_type;
800 IF c_adj_type%NOTFOUND THEN
801 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
802 fnd_message.set_name('OZF', 'OZF_INVALID_ADJ_TYPE_ID');
803 fnd_msg_pub.add;
804 END IF;
805 x_return_status := fnd_api.g_ret_sts_error;
806 RETURN;
807 END IF;
808 CLOSE c_adj_type;
809
810 IF p_adj_rec.adjustment_type <> l_adjustment_type THEN
811 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
812 fnd_message.set_name('OZF', 'OZF_INVALID_ADJ_TYPE_ID');
813 fnd_msg_pub.add;
814 END IF;
815 x_return_status := fnd_api.g_ret_sts_error;
816 RETURN;
817 END IF;
818 END IF;
819
820 END IF;
821
822
823 IF p_adj_rec.activity_type = 'OFFR' THEN
824 OPEN c_get_offer_currency(p_adj_rec.activity_id);
825 FETCH c_get_offer_currency INTO l_offer_currency;
826 CLOSE c_get_offer_currency;
827
828 IF l_offer_currency.transaction_currency_code IS NULL
829 OR l_offer_currency.transaction_currency_code = fnd_api.g_miss_char THEN
830 IF p_adj_rec.adjustment_type NOT IN ('DECREASE_COMMITTED') THEN
831 IF p_adj_rec.order_line_id IS NULL
832 OR p_adj_rec.order_line_id = fnd_api.g_miss_num THEN
833 --Bug 14078405
834 /*IF p_adj_rec.document_type IS NULL
835 OR p_adj_rec.document_type = fnd_api.g_miss_char
836 OR p_adj_rec.document_number IS NULL
837 OR p_adj_rec.document_number = fnd_api.g_miss_num THEN
838 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
839 fnd_message.set_name('OZF', 'OZF_NO_DOCUMENT_INFO');
840 fnd_msg_pub.add;
841 END IF;
842 x_return_status := fnd_api.g_ret_sts_error;
843 RETURN;
844 ELSE*/
845 --Bug 14078405
846 IF p_adj_rec.document_type = 'ORDER' THEN
847 OPEN c_get_order_currency(p_adj_rec.document_number);
848 FETCH c_get_order_currency INTO l_document_curr;
849 CLOSE c_get_order_currency;
850 ELSIF p_adj_rec.document_type = 'TP_ORDER' THEN
851 OPEN c_get_tp_order_currency(p_adj_rec.document_number);
852 FETCH c_get_tp_order_currency INTO l_document_curr;
853 CLOSE c_get_tp_order_currency;
854 ELSIF p_adj_rec.document_type = 'INVOICE' THEN
855 OPEN c_get_txn_currency(p_adj_rec.document_number);
856 FETCH c_get_txn_currency INTO l_document_curr;
857 CLOSE c_get_txn_currency;
858 ELSIF p_adj_rec.document_type = 'PCHO' THEN
859 OPEN c_get_pcho_currency(p_adj_rec.document_number);
860 FETCH c_get_pcho_currency INTO l_document_curr;
861 CLOSE c_get_pcho_currency;
862 END IF; -- end of IF p_adj_rec.document_type = 'ORDER' THEN
863 -- END IF; Bug 14078405
864 ELSE
865 OPEN c_get_header_id(p_adj_rec.order_line_id);
866 FETCH c_get_header_id INTO l_document_curr;
867 CLOSE c_get_header_id;
868 END IF; -- end of IF p_adj_rec.document_type IS NULL
869 ELSE
870 l_document_curr := l_offer_currency.fund_request_curr_code;
871 END IF; --end of IF p_adj_rec.adjustment_type NOT IN ( 'DECREASE_COMMITTED')
872 ELSE
873 l_document_curr := l_offer_currency.fund_request_curr_code;
874 END IF; --end of IF l_offer_currency.transaction_currency_code
875 ELSIF p_adj_rec.activity_type = 'CAMP' THEN
876 OPEN c_get_camp_currency(p_adj_rec.activity_id);
877 FETCH c_get_camp_currency INTO l_document_curr;
878 CLOSE c_get_camp_currency;
879 ELSIF p_adj_rec.activity_type = 'CSCH' THEN
880 OPEN c_get_csch_currency(p_adj_rec.activity_id);
881 FETCH c_get_csch_currency INTO l_document_curr;
882 CLOSE c_get_csch_currency;
883 ELSIF p_adj_rec.activity_type = 'DELV' THEN
884 OPEN c_get_delv_currency(p_adj_rec.activity_id);
885 FETCH c_get_delv_currency INTO l_document_curr;
886 CLOSE c_get_delv_currency;
887 ELSIF p_adj_rec.activity_type = 'EVEH' THEN
888 OPEN c_get_eveh_currency(p_adj_rec.activity_id);
889 FETCH c_get_eveh_currency INTO l_document_curr;
890 CLOSE c_get_eveh_currency;
891 ELSIF p_adj_rec.activity_type = 'EVEO' OR p_adj_rec.activity_type = 'EONE' THEN
892 OPEN c_get_eveo_currency(p_adj_rec.activity_id);
893 FETCH c_get_eveo_currency INTO l_document_curr;
894 CLOSE c_get_eveo_currency;
895 END IF; -- IF p_adj_rec.activity_type = 'OFFR' THEN
896
897 -- Bug 14078405
898 /*IF p_adj_rec.plan_currency_code IS NULL OR p_adj_rec.plan_currency_code = fnd_api.g_miss_char THEN
899 p_adj_rec.plan_currency_code := l_document_curr;
900 ELSE
901 IF l_document_curr <> p_adj_rec.plan_currency_code THEN
902 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
903 fnd_message.set_name('OZF', 'OZF_INVALID_PLAN_CURR_CODE');
904 fnd_msg_pub.add;
905 END IF;
906 x_return_status := fnd_api.g_ret_sts_error;
907 RETURN;
908 END IF;
909 END IF; */
910 --end ER 8399134 code changes
911 -- Bug 14078405
912
913 --kdass - bug 9470625 - added Cost Price, Cost Price Currency, Discount Type, Discount Amount,
914 --Discount Amount Currency for SDR Offers
915 IF p_adj_rec.activity_type = 'OFFR' THEN
916
917 l_sdr_offer := NULL;
918
919 OPEN c_sdr_offer (p_adj_rec.activity_id);
920 FETCH c_sdr_offer INTO l_sdr_offer;
921 CLOSE c_sdr_offer;
922
923 IF l_sdr_offer IS NULL THEN
924
925 p_adj_rec.cost_price := NULL;
926 p_adj_rec.cost_price_currency_code := NULL;
927 p_adj_rec.discount_type := NULL;
928 p_adj_rec.discount_amount := NULL;
929 p_adj_rec.discount_amount_currency_code := NULL;
930
931 ELSE
932
933 --Cost Price is mandatory for Ship & Debit Offer
934 IF p_adj_rec.cost_price = fnd_api.g_miss_num OR p_adj_rec.cost_price IS NULL THEN
935 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
936 fnd_message.set_name('OZF', 'OZF_NO_COST_PRICE');
937 fnd_msg_pub.add;
938 END IF;
939 x_return_status := fnd_api.g_ret_sts_error;
940 RETURN;
941 END IF;
942
943 --Cost Price should be greater than 0
944 IF p_adj_rec.cost_price <= 0 THEN
945 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
946 fnd_message.set_name('OZF', 'OZF_COST_PRICE_GT_ZERO');
947 fnd_msg_pub.add;
948 END IF;
949 x_return_status := fnd_api.g_ret_sts_error;
950 RETURN;
951 END IF;
952
953 IF p_adj_rec.cost_price_currency_code <> fnd_api.g_miss_char AND p_adj_rec.cost_price_currency_code IS NOT NULL THEN
954
955 OPEN c_valid_currency (p_adj_rec.cost_price_currency_code);
956 FETCH c_valid_currency INTO l_valid_curr;
957 CLOSE c_valid_currency;
958
959 IF l_valid_curr IS NULL THEN
960 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
961 fnd_message.set_name('OZF', 'OZF_INVALID_COST_PRICE_CURR');
962 fnd_msg_pub.add;
963 END IF;
964 x_return_status := fnd_api.g_ret_sts_error;
965 RETURN;
966 END IF;
967 ELSE
968 --If Cost Price Currency is not passed, it is same as Offer Currency
969 p_adj_rec.cost_price_currency_code := p_adj_rec.plan_currency_code;
970 END IF;
971
972 --Discount Type is mandatory for Ship & Debit Offer
973 IF p_adj_rec.discount_type = fnd_api.g_miss_char OR p_adj_rec.discount_type IS NULL THEN
974 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
975 fnd_message.set_name('OZF', 'OZF_NO_DIS_TYPE');
976 fnd_msg_pub.add;
977 END IF;
978 x_return_status := fnd_api.g_ret_sts_error;
979 RETURN;
980 ELSE
981
982 --Valid values for Discount Type = % (Percent), AMT (Amount), NEWPRICE (New Price)
983 IF p_adj_rec.discount_type NOT IN ('%', 'AMT', 'NEWPRICE') THEN
984 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
985 fnd_message.set_name('OZF', 'OZF_INVALID_DIS_TYPE');
986 fnd_msg_pub.add;
987 END IF;
988 x_return_status := fnd_api.g_ret_sts_error;
989 RETURN;
990 END IF;
991 END IF;
992
993 --Discount Amount is mandatory for Ship & Debit Offer
994 IF p_adj_rec.discount_amount = fnd_api.g_miss_num OR p_adj_rec.discount_amount IS NULL THEN
995 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
996 fnd_message.set_name('OZF', 'OZF_NO_DIS_AMT');
997 fnd_msg_pub.add;
998 END IF;
999 x_return_status := fnd_api.g_ret_sts_error;
1000 RETURN;
1001 END IF;
1002
1003 --Discount Amount should be greater than 0
1004 IF p_adj_rec.discount_amount <= 0 THEN
1005 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1006 fnd_message.set_name('OZF', 'OZF_DIS_AMT_GT_ZERO');
1007 fnd_msg_pub.add;
1008 END IF;
1009 x_return_status := fnd_api.g_ret_sts_error;
1010 RETURN;
1011 END IF;
1012
1013 --Discount Amount Currency is mandatory for Ship & Debit Offer except for Discount Type = % (Percent)
1014 IF p_adj_rec.discount_type = '%' THEN
1015 p_adj_rec.discount_amount_currency_code := NULL;
1016
1017 ELSIF p_adj_rec.discount_amount_currency_code <> fnd_api.g_miss_char AND p_adj_rec.discount_amount_currency_code IS NOT NULL THEN
1018
1019 l_valid_curr := NULL;
1020 OPEN c_valid_currency (p_adj_rec.discount_amount_currency_code);
1021 FETCH c_valid_currency INTO l_valid_curr;
1022 CLOSE c_valid_currency;
1023
1024 IF l_valid_curr IS NULL THEN
1025 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1026 fnd_message.set_name('OZF', 'OZF_INVALID_DIS_AMT_CURR');
1027 fnd_msg_pub.add;
1028 END IF;
1029 x_return_status := fnd_api.g_ret_sts_error;
1030 RETURN;
1031 END IF;
1032 ELSE
1033 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1034 fnd_message.set_name('OZF', 'OZF_NO_DIS_AMT_CURR');
1035 fnd_msg_pub.add;
1036 END IF;
1037 x_return_status := fnd_api.g_ret_sts_error;
1038 RETURN;
1039 END IF;
1040
1041 END IF; --IF l_sdr_offer IS NULL THEN
1042
1043 END IF; --IF p_adj_rec.activity_type = 'OFFR' THEN
1044
1045 END Validate_Items;
1046
1047 ---------------------------------------------------------------------
1048 -- PROCEDURE
1049 -- Create_Fund_Adjustment
1050 --
1051 -- PURPOSE
1052 -- Create fund adjustment.
1053 --
1054 -- PARAMETERS
1055 -- p_adj_rec: the new record to be inserted
1056 --
1057 -- HISTORY
1058 -- 04/05/2005 kdass Created
1059 -- 03/14/2008 psomyaju ER-6858324
1060 ---------------------------------------------------------------------
1061 -- nirprasa Added for FP of bug 9383565.
1062 PROCEDURE Create_Fund_Adjustment(
1063 p_api_version IN NUMBER
1064 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1065 ,p_commit IN VARCHAR2 := fnd_api.g_false
1066 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1067 ,p_adj_rec IN OZF_FUND_UTILIZED_PUB.adjustment_rec_type
1068 ,x_return_status OUT NOCOPY VARCHAR2
1069 ,x_msg_count OUT NOCOPY NUMBER
1070 ,x_msg_data OUT NOCOPY VARCHAR2
1071 )IS
1072 l_utilization_id NUMBER;
1073 BEGIN
1074
1075 --nirprasa - added for Bug 9383565
1076 Create_Fund_Adjustment(
1077 p_api_version => p_api_version
1078 ,p_init_msg_list => p_init_msg_list
1079 ,p_commit => p_commit
1080 ,p_validation_level => p_validation_level
1081 ,p_adj_rec => p_adj_rec
1082 ,x_return_status => x_return_status
1083 ,x_msg_count => x_msg_count
1084 ,x_msg_data => x_msg_data
1085 ,x_utilization_id => l_utilization_id
1086 );
1087
1088 END Create_Fund_Adjustment;
1089
1090 ---------------------------------------------------------------------
1091 -- PROCEDURE
1092 -- Create_Fund_Adjustment
1093 --
1094 -- PURPOSE
1095 -- Create fund adjustment.
1096 --
1097 -- PARAMETERS
1098 -- p_adj_rec: the new record to be inserted
1099 --
1100 -- HISTORY
1101 -- 04/05/2005 kdass Created
1102 -- 03/14/2008 psomyaju ER-6858324
1103 ---------------------------------------------------------------------
1104 PROCEDURE Create_Fund_Adjustment(
1105 p_api_version IN NUMBER
1106 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1107 ,p_commit IN VARCHAR2 := fnd_api.g_false
1108 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1109 ,p_adj_rec IN OZF_FUND_UTILIZED_PUB.adjustment_rec_type
1110 ,x_return_status OUT NOCOPY VARCHAR2
1111 ,x_msg_count OUT NOCOPY NUMBER
1112 ,x_msg_data OUT NOCOPY VARCHAR2
1113 ,x_utilization_id OUT NOCOPY NUMBER
1114 )
1115 IS
1116 l_api_name VARCHAR(30) := 'Create_Fund_Adjustment';
1117 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1118 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1119 l_act_budget_util_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1120 l_parent_src_tbl ozf_fund_adjustment_pvt.parent_src_tbl_type;
1121 l_accrual_flag NUMBER := 0;
1122 l_adj_type_id NUMBER;
1123 l_flagDecCommitted BOOLEAN := TRUE; -- flag for adjustment type DECREASE_COMM_EARNED
1124 l_adj_rec OZF_FUND_UTILIZED_PUB.adjustment_rec_type := p_adj_rec;
1125 l_api_version NUMBER := p_api_version;
1126 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1127 l_validation_level NUMBER := p_validation_level;
1128 l_act_budget_id NUMBER;
1129 l_utilized_amount NUMBER;
1130 l_fund_id NUMBER;
1131 l_gl_posted_flag VARCHAR2(1);
1132 l_rate NUMBER;
1133 l_utilization_id NUMBER;
1134
1135 CURSOR c_fund_type (p_fund_id IN NUMBER) IS
1136 SELECT 1 FROM ozf_funds_all_b
1137 WHERE fund_id = p_fund_id
1138 AND fund_type = 'FULLY_ACCRUED'
1139 AND accrual_basis= 'CUSTOMER'
1140 AND liability_flag= 'Y';
1141
1142 /* 07-APR-09 kdass bug 8402334
1143 CURSOR c_org_order (p_doc_number IN NUMBER) IS
1144 SELECT org_id
1145 FROM oe_order_headers_all
1146 WHERE header_id = p_doc_number;
1147
1148 CURSOR c_org_fund (p_fund_id IN NUMBER) IS
1149 SELECT org_id
1150 FROM ozf_funds_all_b
1151 WHERE fund_id = p_fund_id;
1152 */
1153
1154 CURSOR c_orig_util_id (p_activity_id IN NUMBER, p_activity_type IN VARCHAR2,
1155 p_fund_id IN NUMBER, p_product_id IN NUMBER, p_cust_acct_id IN NUMBER) IS
1156 SELECT utilization_id, NVL(gl_posted_flag,'N')
1157 FROM ozf_funds_utilized_all_b
1158 WHERE plan_id = p_activity_id
1159 AND plan_type = p_activity_type
1160 AND fund_id = p_fund_id
1161 AND NVL(product_id,0) = NVL(p_product_id,0)
1162 AND NVL(cust_account_id,0) = NVL(p_cust_acct_id,0)
1163 AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
1164
1165 CURSOR c_get_fund_currency(p_fund_id IN NUMBER, p_budget_source_id IN NUMBER,
1166 p_budget_source_type VARCHAR2) IS
1167 SELECT fund_currency
1168 FROM ozf_object_fund_summary
1169 WHERE fund_id = p_fund_id
1170 AND object_id = p_budget_source_id
1171 AND object_type = p_budget_source_type;
1172
1173 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
1174 SELECT exchange_rate_type
1175 FROM ozf_sys_parameters_all
1176 WHERE org_id = p_org_id;
1177
1178
1179 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
1180 --nirprasa,ER 8399134
1181 BEGIN
1182 SAVEPOINT Create_Fund_Adjustment;
1183
1184 validate_items(p_adj_rec => l_adj_rec
1185 ,x_return_status => x_return_status);
1186 --nirprasa,ER 8399134 decide on plan_currency_code
1187
1188
1189 /*fund_id is mandatory now. In R12 fund_id is optional but currency_code is mandatory.
1190 Hence the fails if offer has multiple budgets because there is no provision of
1191 passing currency for all budgets sourced by the offer.*/
1192
1193 --ER 8399134 Since fund_id is passed, validate_items() defaults the currency_code
1194 --if it is not passed. If currency_code is passed then validate it.
1195 --If plan_amount is passed then ignore amount column.
1196 IF l_adj_rec.plan_amount IS NOT NULL OR l_adj_rec.plan_amount <> fnd_api.g_miss_num THEN
1197 l_adj_rec.amount := NULL;
1198 l_act_budgets_rec.request_amount := l_adj_rec.plan_amount;
1199 END IF;
1200
1201
1202 l_act_util_rec.plan_currency_code := l_adj_rec.plan_currency_code;
1203 l_act_budgets_rec.request_currency := l_act_util_rec.plan_currency_code;
1204
1205
1206 /* 07-APR-09 kdass bug 8402334
1207 IF l_adj_rec.org_id = fnd_api.g_miss_num OR l_adj_rec.org_id IS NULL THEN
1208 IF l_adj_rec.document_type = 'ORDER' THEN
1209 OPEN c_org_order (l_adj_rec.document_number);
1210 FETCH c_org_order INTO l_adj_rec.org_id;
1211 CLOSE c_org_order;
1212 ELSE
1213 OPEN c_org_fund (NVL(l_adj_rec.fund_id,l_fund_id));
1214 FETCH c_org_fund INTO l_adj_rec.org_id;
1215 CLOSE c_org_fund;
1216 END IF;
1217 END IF;
1218 */
1219
1220 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1221 RAISE fnd_api.g_exc_unexpected_error;
1222 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1223 RAISE fnd_api.g_exc_error;
1224 END IF;
1225
1226 l_act_util_rec.adjustment_type_id := l_adj_rec.adjustment_type_id;
1227 l_act_util_rec.adjustment_type := l_adj_rec.adjustment_type;
1228 l_act_util_rec.adjustment_date := l_adj_rec.adjustment_date;
1229 l_act_util_rec.gl_date := l_adj_rec.gl_date;
1230 l_act_util_rec.gl_account_credit := l_adj_rec.gl_account_credit;
1231 l_act_util_rec.gl_account_debit := l_adj_rec.gl_account_debit;
1232 l_act_util_rec.camp_schedule_id := l_adj_rec.camp_schedule_id;
1233 l_act_util_rec.object_type := l_adj_rec.document_type;
1234 l_act_util_rec.object_id := l_adj_rec.document_number;
1235 l_act_util_rec.product_level_type := l_adj_rec.product_level_type;
1236 l_act_util_rec.product_id := l_adj_rec.product_id;
1237 l_act_util_rec.cust_account_id := l_adj_rec.cust_account_id;
1238 l_act_util_rec.bill_to_site_use_id := l_adj_rec.bill_to_site_use_id;
1239 l_act_util_rec.ship_to_site_use_id := l_adj_rec.ship_to_site_use_id;
1240 l_act_budgets_rec.justification := fnd_message.get_string ('OZF', 'OZF_ADJ_PUB_API');
1241 l_act_budgets_rec.budget_source_type := l_adj_rec.activity_type;
1242 l_act_budgets_rec.budget_source_id := l_adj_rec.activity_id;
1243 l_act_budgets_rec.transaction_type := 'DEBIT';
1244 l_act_budgets_rec.status_code := 'APPROVED';
1245 l_act_budgets_rec.approver_id := l_adj_rec.approver_id;
1246 --ER 9382547
1247 --ER 13069257
1248 g_skip_acct_gen_flag := l_adj_rec.skip_acct_gen_flag;
1249
1250 l_act_budgets_rec.exchange_rate_date := l_adj_rec.exchange_rate_date; --bug 8532055
1251 l_act_budget_util_rec.exchange_rate_date := l_adj_rec.exchange_rate_date; --bug 8532055
1252
1253 --DFFs/order_line_id added for ER-6858324
1254 l_act_util_rec.order_line_id := l_adj_rec.order_line_id;
1255 l_act_util_rec.attribute_category := l_adj_rec.attribute_category;
1256 l_act_util_rec.attribute1 := l_adj_rec.attribute1;
1257 l_act_util_rec.attribute2 := l_adj_rec.attribute2;
1258 l_act_util_rec.attribute3 := l_adj_rec.attribute3;
1259 l_act_util_rec.attribute4 := l_adj_rec.attribute4;
1260 l_act_util_rec.attribute5 := l_adj_rec.attribute5;
1261 l_act_util_rec.attribute6 := l_adj_rec.attribute6;
1262 l_act_util_rec.attribute7 := l_adj_rec.attribute7;
1263 l_act_util_rec.attribute8 := l_adj_rec.attribute8;
1264 l_act_util_rec.attribute9 := l_adj_rec.attribute9;
1265 l_act_util_rec.attribute10 := l_adj_rec.attribute10;
1266 l_act_util_rec.attribute11 := l_adj_rec.attribute11;
1267 l_act_util_rec.attribute12 := l_adj_rec.attribute12;
1268 l_act_util_rec.attribute13 := l_adj_rec.attribute13;
1269 l_act_util_rec.attribute14 := l_adj_rec.attribute14;
1270 l_act_util_rec.attribute15 := l_adj_rec.attribute15;
1271
1272 --07-APR-09 kdass bug 8402334 - add OU to the adjustment record
1273 l_act_util_rec.org_id := l_adj_rec.org_id;
1274
1275 --kdass - bug 9470625
1276 l_act_util_rec.cost_price := l_adj_rec.cost_price;
1277 l_act_util_rec.cost_price_currency_code := l_adj_rec.cost_price_currency_code;
1278 l_act_util_rec.discount_type := l_adj_rec.discount_type;
1279 l_act_util_rec.discount_amount := l_adj_rec.discount_amount;
1280 l_act_util_rec.discount_amount_currency_code := l_adj_rec.discount_amount_currency_code;
1281
1282 -- if adjustment type is 'Decrease Committed and Earned Amounts'
1283 IF l_act_util_rec.adjustment_type = 'DECREASE_COMM_EARNED' THEN
1284 l_act_budgets_rec.transfer_type := 'TRANSFER';
1285 l_act_budgets_rec.act_budget_used_by_id := l_adj_rec.fund_id;
1286 l_act_budgets_rec.arc_act_budget_used_by := 'FUND';
1287 l_act_budget_util_rec.justification := l_act_budgets_rec.justification;
1288 l_act_budget_util_rec.budget_source_type := l_act_budgets_rec.budget_source_type;
1289 l_act_budget_util_rec.budget_source_id := l_act_budgets_rec.budget_source_id;
1290 l_act_budget_util_rec.transaction_type := l_act_budgets_rec.transaction_type;
1291 l_act_budget_util_rec.request_currency := l_act_budgets_rec.request_currency;
1292 l_act_budget_util_rec.request_amount := l_act_budgets_rec.request_amount;
1293 l_act_budget_util_rec.status_code := l_act_budgets_rec.status_code;
1294 l_act_budget_util_rec.approver_id := l_act_budgets_rec.approver_id;
1295 l_act_budget_util_rec.transfer_type := 'UTILIZED';
1296 l_act_budget_util_rec.act_budget_used_by_id := l_act_budgets_rec.budget_source_id;
1297 l_act_budget_util_rec.arc_act_budget_used_by := l_act_budgets_rec.budget_source_type;
1298 l_act_util_rec.utilization_type := 'ADJUSTMENT';
1299 l_act_budget_util_rec.parent_source_id := l_adj_rec.fund_id;
1300 --nirprasa,ER 8399134
1301 --l_act_budget_util_rec.parent_src_curr := l_act_budgets_rec.request_currency;
1302 --l_act_budget_util_rec.parent_src_apprvd_amt := l_act_budgets_rec.request_amount;
1303 l_act_budget_util_rec.parent_src_curr := l_adj_rec.currency_code;
1304 l_act_budget_util_rec.parent_src_apprvd_amt := l_adj_rec.amount;
1305
1306 -- for customer fully accrual budget with liability flag on, do not decrease committed in java,
1307 -- instead let pl/sql api handle it along with other cases
1308 OPEN c_fund_type(l_adj_rec.fund_id);
1309 FETCH c_fund_type INTO l_accrual_flag;
1310 CLOSE c_fund_type;
1311
1312 IF l_accrual_flag = 1 THEN
1313 l_flagDecCommitted := FALSE;
1314 END IF;
1315
1316 IF l_adj_rec.fund_id IS NOT NULL THEN
1317
1318 ozf_fund_utilized_pvt.create_act_utilization(p_api_version => l_api_version
1319 ,p_init_msg_list => l_init_msg_list
1320 ,p_validation_level => l_validation_level
1321 ,x_return_status => x_return_status
1322 ,x_msg_count => x_msg_count
1323 ,x_msg_data => x_msg_data
1324 ,p_act_budgets_rec => l_act_budget_util_rec
1325 ,p_act_util_rec => l_act_util_rec
1326 ,x_act_budget_id => l_act_budget_id
1327 ,x_utilization_id => x_utilization_id --nirprasa, added for FP of bug 9383565
1328 );
1329 --ER 8399134 This will be used to created dec comm record.
1330 l_act_budgets_rec.request_currency := l_adj_rec.currency_code;
1331 l_act_budgets_rec.request_amount := l_adj_rec.amount;
1332
1333 IF l_adj_rec.plan_amount IS NOT NULL OR l_adj_rec.plan_amount <> FND_API.G_MISS_NUM THEN
1334 l_act_budgets_rec.src_curr_req_amt := l_adj_rec.plan_amount;
1335 END IF;
1336
1337 /*OPEN c_offer_currency(l_adj_rec.activity_id);
1338 FETCH c_offer_currency INTO l_offer_currency;
1339 CLOSE c_offer_currency;
1340
1341 IF l_offer_currency.transaction_currency_code IS NULL
1342 OR l_offer_currency.transaction_currency_code = FND_API.G_MISS_CHAR THEN
1343 IF l_adj_rec.plan_currency_code <> l_offer_currency.fund_request_curr_code THEN
1344 ozf_utility_pvt.convert_currency (
1345 x_return_status => x_return_status
1346 ,p_from_currency => l_adj_rec.plan_currency_code
1347 ,p_to_currency => l_offer_currency.fund_request_curr_code
1348 ,p_from_amount => l_adj_rec.plan_amount
1349 ,x_to_amount => l_conv_plan_amount
1350 ,x_rate => l_rate
1351 );
1352 l_adj_rec.plan_amount := l_conv_plan_amount;
1353 END IF;
1354 l_adj_rec.plan_currency_code := l_offer_currency.fund_request_curr_code;
1355 END IF;*/
1356
1357 --nirprasa, ER 8399134 remove this code, since fund_id is mandatory now.
1358 /*ELSE --if fund id is null, then post proportionately to the budgets which the offer is sourcing from
1359 FOR i IN NVL (l_parent_src_tbl.FIRST, 1) .. NVL (l_parent_src_tbl.LAST, 0)
1360 LOOP
1361 --nirprasa,ER 8399134 change the assignments as the modified private API.
1362 l_act_budgets_rec.act_budget_used_by_id := l_parent_src_tbl (i).fund_id;
1363 l_act_budget_util_rec.request_currency := l_act_util_rec.plan_currency_code;
1364 l_act_budget_util_rec.request_amount := l_parent_src_tbl (i).plan_amount;
1365 l_act_budget_util_rec.parent_source_id := l_parent_src_tbl (i).fund_id;
1366 l_act_budget_util_rec.parent_src_curr := l_parent_src_tbl (i).fund_curr;
1367 l_act_budget_util_rec.parent_src_apprvd_amt := l_parent_src_tbl (i).fund_amount;
1368
1369 ozf_fund_utilized_pvt.create_act_utilization(p_api_version => l_api_version
1370 ,p_init_msg_list => l_init_msg_list
1371 ,p_validation_level => l_validation_level
1372 ,x_return_status => x_return_status
1373 ,x_msg_count => x_msg_count
1374 ,x_msg_data => x_msg_data
1375 ,p_act_budgets_rec => l_act_budget_util_rec
1376 ,p_act_util_rec => l_act_util_rec
1377 ,x_act_budget_id => l_act_budget_id
1378 );
1379 END LOOP;*/
1380
1381 END IF;
1382
1383 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1384 RAISE FND_API.g_exc_unexpected_error;
1385 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1386 RAISE FND_API.g_exc_unexpected_error;
1387 END IF;
1388
1389 --reset utilization_type for decrease committed amount
1390 l_act_util_rec.utilization_type := NULL;
1391
1392 -- if adjustment type is 'Decrease Committed Amount'
1393 ELSIF l_act_util_rec.adjustment_type = 'DECREASE_COMMITTED' THEN
1394 l_act_util_rec.utilization_type := null;
1395 l_act_budgets_rec.transfer_type := 'TRANSFER';
1396 l_act_budgets_rec.act_budget_used_by_id := l_adj_rec.fund_id;
1397 l_act_budgets_rec.arc_act_budget_used_by := 'FUND';
1398 --nirprasa,ER 8399134
1399 l_act_budgets_rec.request_currency := l_adj_rec.currency_code;
1400 l_act_budgets_rec.request_amount := l_adj_rec.amount;
1401 IF l_adj_rec.plan_amount IS NOT NULL AND l_adj_rec.plan_amount <> FND_API.G_MISS_NUM THEN
1402 l_act_budgets_rec.src_curr_req_amt := l_adj_rec.plan_amount;
1403 END IF;
1404
1405 -- if adjustment type is 'Increase Earned Amount' (STANDARD) or 'Decrease Earned Amount' (DECREASE_EARNED)
1406 -- or 'Increase Paid Amount' (INCREASE_PAID) or 'Decrease Paid Amount' (DECREASE_PAID)
1407 ELSE
1408 l_act_budgets_rec.transfer_type := 'UTILIZED';
1409 l_act_budgets_rec.act_budget_used_by_id := l_act_budgets_rec.budget_source_id;
1410 l_act_budgets_rec.arc_act_budget_used_by := l_act_budgets_rec.budget_source_type;
1411 l_act_util_rec.utilization_type := 'ADJUSTMENT';
1412 l_act_util_rec.scan_type_id := l_adj_rec.scan_type_id;
1413 l_act_budgets_rec.parent_source_id := l_adj_rec.fund_id;
1414 --nirprasa,ER 8399134
1415 l_act_budgets_rec.parent_src_curr := l_adj_rec.currency_code;
1416 l_act_budgets_rec.parent_src_apprvd_amt := l_adj_rec.amount;
1417 --l_act_util_rec.orig_utilization_id := l_adj_rec.orig_utilization_id;
1418 END IF;
1419
1420 --for all adjustment types
1421 IF (l_flagDecCommitted) THEN
1422
1423 IF l_adj_rec.fund_id IS NOT NULL THEN
1424 IF l_act_util_rec.adjustment_type NOT IN ('INCREASE_PAID', 'DECREASE_PAID') THEN
1425 ozf_fund_utilized_pvt.create_act_utilization(p_api_version => l_api_version
1426 ,p_init_msg_list => l_init_msg_list
1427 ,p_validation_level => l_validation_level
1428 ,x_return_status => x_return_status
1429 ,x_msg_count => x_msg_count
1430 ,x_msg_data => x_msg_data
1431 ,p_act_budgets_rec => l_act_budgets_rec
1432 ,p_act_util_rec => l_act_util_rec
1433 ,x_act_budget_id => l_act_budget_id
1434 ,x_utilization_id => l_utilization_id --nirprasa, added for FP of bug 9383565
1435 );
1436
1437 --nirprasa, added for FP of bug 9383565
1438 IF l_act_util_rec.adjustment_type <> 'DECREASE_COMM_EARNED' THEN
1439 x_utilization_id := l_utilization_id;
1440 END IF;
1441 ELSE
1442
1443 --get the original utilization id
1444 OPEN c_orig_util_id (l_adj_rec.activity_id, l_adj_rec.activity_type, l_adj_rec.fund_id,
1445 l_adj_rec.product_id, l_adj_rec.cust_account_id);
1446 FETCH c_orig_util_id INTO l_act_util_rec.orig_utilization_id, l_gl_posted_flag;
1447 CLOSE c_orig_util_id;
1448
1449 IF G_DEBUG THEN
1450 ozf_utility_pvt.debug_message('orig_utilization_id: ' || l_act_util_rec.orig_utilization_id);
1451 ozf_utility_pvt.debug_message('activity_id: ' || l_adj_rec.activity_id);
1452 ozf_utility_pvt.debug_message('activity_type: ' || l_adj_rec.activity_type);
1453 ozf_utility_pvt.debug_message('fund_id: ' || l_adj_rec.fund_id);
1454 ozf_utility_pvt.debug_message('product_id: ' || l_adj_rec.product_id);
1455 ozf_utility_pvt.debug_message('cust_account_id: ' || l_adj_rec.cust_account_id);
1456 END IF;
1457
1458 IF l_act_util_rec.orig_utilization_id IS NULL THEN
1459 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1460 fnd_message.set_name('OZF', 'OZF_NO_ORIG_UTIL_ID');
1461 fnd_msg_pub.add;
1462 END IF;
1463 RAISE fnd_api.g_exc_error;
1464 END IF;
1465
1466 --if the original utilization is not posted to GL(e.g. marketing cost, or off invoice offer
1467 --where gl posting is not required), then paid adjustment should not be allowed.
1468 IF l_gl_posted_flag <> 'Y' THEN
1469 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1470 fnd_message.set_name('OZF', 'OZF_ADJ_PAID_NOT_ALLOWED');
1471 fnd_msg_pub.add;
1472 END IF;
1473 RAISE fnd_api.g_exc_error;
1474 END IF;
1475
1476 IF l_act_util_rec.adjustment_type = 'DECREASE_PAID' THEN
1477 l_act_budgets_rec.parent_src_apprvd_amt := - l_act_budgets_rec.parent_src_apprvd_amt;
1478 l_act_budgets_rec.request_amount := - l_act_budgets_rec.request_amount;
1479 END IF;
1480
1481 --kdass fixed bug 9432297
1482 l_act_util_rec.fund_request_currency_code := OZF_ACTBUDGETS_PVT.get_object_currency (
1483 l_act_budgets_rec.arc_act_budget_used_by
1484 ,l_act_budgets_rec.act_budget_used_by_id
1485 ,x_return_status
1486 );
1487
1488 IF l_act_budgets_rec.request_amount IS NOT NULL THEN
1489 IF ((l_act_budgets_rec.parent_src_apprvd_amt IS NULL
1490 OR l_act_budgets_rec.parent_src_apprvd_amt = fnd_api.g_miss_num)
1491 AND l_act_budgets_rec.request_currency <> l_act_budgets_rec.parent_src_curr) THEN
1492
1493 ozf_utility_pvt.convert_currency (
1494 x_return_status=> x_return_status
1495 ,p_from_currency=> l_act_budgets_rec.request_currency
1496 ,p_to_currency=> l_act_budgets_rec.parent_src_curr
1497 ,p_conv_type=>l_exchange_rate_type --Added for bug 7030415
1498 ,p_from_amount=> l_act_budgets_rec.request_amount
1499 ,x_to_amount=> l_act_budgets_rec.parent_src_apprvd_amt
1500 ,x_rate=> l_rate
1501 );
1502
1503 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1504 RAISE fnd_api.g_exc_unexpected_error;
1505 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1506 RAISE fnd_api.g_exc_error;
1507 END IF;
1508 ELSE
1509 l_act_budgets_rec.parent_src_apprvd_amt := l_act_budgets_rec.request_amount;
1510 END IF;
1511 ELSIF l_act_budgets_rec.parent_src_apprvd_amt IS NOT NULL THEN
1512 IF l_act_budgets_rec.request_currency <> l_act_budgets_rec.parent_src_curr
1513 AND (l_act_budgets_rec.request_amount IS NULL
1514 OR l_act_budgets_rec.request_amount = fnd_api.g_miss_num) THEN
1515 --nirprasa,ER 8399134
1516 ozf_utility_pvt.convert_currency (
1517 x_return_status=> x_return_status
1518 ,p_from_currency=> l_act_budgets_rec.parent_src_curr
1519 ,p_to_currency=> l_act_budgets_rec.request_currency
1520 ,p_conv_type=>l_exchange_rate_type --Added for bug 7030415
1521 ,p_conv_date => l_act_budgets_rec.exchange_rate_date --bug 8532055
1522 ,p_from_amount=> l_act_budgets_rec.parent_src_apprvd_amt
1523 ,x_to_amount=> l_act_budgets_rec.request_amount
1524 ,x_rate=> l_rate
1525 );
1526 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1527 RAISE fnd_api.g_exc_unexpected_error;
1528 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1529 RAISE fnd_api.g_exc_error;
1530 END IF;
1531 ELSE
1532 l_act_budgets_rec.request_amount := l_act_budgets_rec.parent_src_apprvd_amt;
1533 END IF;
1534 END IF;
1535
1536 ozf_fund_adjustment_pvt.create_fund_utilization(p_act_budget_rec => l_act_budgets_rec
1537 ,x_return_status => x_return_status
1538 ,x_msg_count => x_msg_count
1539 ,x_msg_data => x_msg_data
1540 ,p_act_util_rec => l_act_util_rec
1541 ,x_utilized_amount => l_utilized_amount
1542 ,x_utilization_id => x_utilization_id -- nirprasa, added for FP of bug 9383565.
1543 );
1544 END IF;
1545 --nirprasa, ER 8399134 remove this code, since fund_id is mandatory now.
1546 /*ELSE --if fund id is null, then post proportionately to the budgets which the offer is sourcing from
1547
1548 FOR i IN NVL (l_parent_src_tbl.FIRST, 1) .. NVL (l_parent_src_tbl.LAST, 0)
1549 LOOP
1550 --nirprasa,12.2 changed the assignments
1551 l_act_budgets_rec.request_currency := l_act_util_rec.plan_currency_code;
1552 l_act_budgets_rec.request_amount := l_parent_src_tbl (i).plan_amount;
1553
1554 IF l_adj_rec.adjustment_type IN ('DECREASE_COMM_EARNED', 'DECREASE_COMMITTED') THEN
1555 l_act_budgets_rec.act_budget_used_by_id := l_parent_src_tbl (i).fund_id;
1556 ELSE
1557 l_act_budgets_rec.parent_src_curr := l_parent_src_tbl (i).fund_curr;
1558 l_act_budgets_rec.parent_src_apprvd_amt := l_parent_src_tbl (i).fund_amount;
1559 l_act_budgets_rec.parent_source_id := l_parent_src_tbl (i).fund_id;
1560 END IF;
1561
1562 IF l_act_util_rec.adjustment_type NOT IN ('INCREASE_PAID', 'DECREASE_PAID') THEN
1563
1564 ozf_fund_utilized_pvt.create_act_utilization(p_api_version => l_api_version
1565 ,p_init_msg_list => l_init_msg_list
1566 ,p_validation_level => l_validation_level
1567 ,x_return_status => x_return_status
1568 ,x_msg_count => x_msg_count
1569 ,x_msg_data => x_msg_data
1570 ,p_act_budgets_rec => l_act_budgets_rec
1571 ,p_act_util_rec => l_act_util_rec
1572 ,x_act_budget_id => l_act_budget_id
1573 );
1574 ELSE
1575
1576 --get the original utilization id
1577 OPEN c_orig_util_id (l_adj_rec.activity_id, l_adj_rec.activity_type, l_parent_src_tbl (i).fund_id,
1578 l_adj_rec.product_id, l_adj_rec.cust_account_id);
1579 FETCH c_orig_util_id INTO l_act_util_rec.orig_utilization_id, l_gl_posted_flag;
1580 CLOSE c_orig_util_id;
1581
1582 IF G_DEBUG THEN
1583 ozf_utility_pvt.debug_message('orig_utilization_id: ' || l_act_util_rec.orig_utilization_id);
1584 ozf_utility_pvt.debug_message('activity_id: ' || l_adj_rec.activity_id);
1585 ozf_utility_pvt.debug_message('activity_type: ' || l_adj_rec.activity_type);
1586 ozf_utility_pvt.debug_message('fund_id: ' || l_parent_src_tbl (i).fund_id);
1587 ozf_utility_pvt.debug_message('product_id: ' || l_adj_rec.product_id);
1588 ozf_utility_pvt.debug_message('cust_account_id: ' || l_adj_rec.cust_account_id);
1589 END IF;
1590
1591 IF l_act_util_rec.orig_utilization_id IS NULL THEN
1592 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1593 fnd_message.set_name('OZF', 'OZF_NO_ORIG_UTIL_ID');
1594 fnd_msg_pub.add;
1595 END IF;
1596 RAISE fnd_api.g_exc_error;
1597 END IF;
1598
1599 --if the original utilization is not posted to GL(e.g. marketing cost, or off invoice offer
1600 --where gl posting is not required), then paid adjustment should not be allowed.
1601 IF l_gl_posted_flag <> 'Y' THEN
1602 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1603 fnd_message.set_name('OZF', 'OZF_ADJ_PAID_NOT_ALLOWED');
1604 fnd_msg_pub.add;
1605 END IF;
1606 RAISE fnd_api.g_exc_error;
1607 END IF;
1608
1609 IF l_act_util_rec.adjustment_type = 'DECREASE_PAID' THEN
1610 l_act_budgets_rec.parent_src_apprvd_amt := - l_act_budgets_rec.parent_src_apprvd_amt;
1611 l_act_budgets_rec.request_amount := - l_act_budgets_rec.request_amount;
1612 END IF;
1613
1614 ozf_fund_adjustment_pvt.create_fund_utilization(p_act_budget_rec => l_act_budgets_rec
1615 ,x_return_status => x_return_status
1616 ,x_msg_count => x_msg_count
1617 ,x_msg_data => x_msg_data
1618 ,p_act_util_rec => l_act_util_rec
1619 ,x_utilized_amount => l_utilized_amount
1620 );
1621 END IF;
1622 END LOOP;*/
1623 END IF;
1624
1625 END IF;
1626
1627 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1628 RAISE fnd_api.g_exc_unexpected_error;
1629 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1630 RAISE fnd_api.g_exc_error;
1631 END IF;
1632
1633 FND_MSG_PUB.Count_And_Get (
1634 p_encoded => FND_API.G_FALSE,
1635 p_count => x_msg_count,
1636 p_data => x_msg_data
1637 );
1638
1639 EXCEPTION
1640 WHEN FND_API.G_EXC_ERROR THEN
1641 ROLLBACK TO Create_Fund_Adjustment;
1642 x_return_status := FND_API.G_RET_STS_ERROR;
1643 -- Standard call to get message count and if count=1, get the message
1644 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1645 ,p_count => x_msg_count
1646 ,p_data => x_msg_data
1647 );
1648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1649 ROLLBACK TO Create_Fund_Adjustment;
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651 -- Standard call to get message count and if count=1, get the message
1652 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1653 ,p_count => x_msg_count
1654 ,p_data => x_msg_data
1655 );
1656 WHEN OTHERS THEN
1657 ROLLBACK TO Create_Fund_Adjustment;
1658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1659 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1660 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1661 END IF;
1662 -- Standard call to get message count and if count=1, get the message
1663 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1664 ,p_count => x_msg_count
1665 ,p_data => x_msg_data
1666 );
1667 END Create_Fund_Adjustment;
1668 --------------------------------------------------------------------
1669
1670 END OZF_FUND_UTILIZED_PUB;
[Home] [Help]