[Home] [Help]
PACKAGE BODY: APPS.OZF_UTILITY_PVT
Source
1 PACKAGE BODY OZF_Utility_PVT AS
2 /* $Header: ozfvutlb.pls 120.27.12020000.6 2013/02/14 10:00:45 nepanda ship $ */
3
4 -- HISTORY
5 --
6 -- 15-Jun-2000 HOLIU Added procedures to get qp details
7 -- 15-Jun-2000 PTENDULK Commented the function is_in_my_area
8 -- as it will be released in R2
9 -- 28-Jun-2000 RCHAHAL Added Qualifier in get_qual_table_name_and_pk.
10 -- 13-Jul-2000 choang Added get_resource_id
11 -- 07-Aug-2000 ptendulk Added procedure Write_Conc_Log
12 -- 08-Aug-2000 ptendulk Modified procedure Write_Conc_Log
13 -- 30-Jan-2001 MPande Modified procedure get_object_name
14 -- 30-Jan-2001 ptendulk Modified Qualifier in get_qual_table_name_and_pk proc.
15 -- 13-Mar-2001 choang Removed extra close cursor code in create_log.
16 -- 03/27/2001 MPANDE MOved 4 Procedures from OZF to OZF
17 -- 03/29/2001 gjoby Added LIST and SQL condtions
18 -- in get_qual_table_name_and_pk
19 -- 29-Mar-2001 ptendulk Modified get_system_status_type proc.
20 ---13-Apr-2001 feliu Modified create_log proc.
21 -- 23-Apr-2001 feliu Modified create_log proc.
22 -- skarumur - 25-apr-2000
23 -- 07-May-2001 choang Added RCAM to get_qual_table_name_and_pk
24 -- 20-May-2001 ptendulk Modified get_system_status_type procedure for Progrozf
25 -- 24-May-2001 feliu Modified create_log proc.
26 -- 13-Jun-2001 ptendulk Added code for MUKUMAR to add validation for EONE
27 -- 15-Jun-2001 choang changed OFFR in get_qual_table_name_and_pk, and PRTN (to PTNR)
28 -- in get_object_name
29 -- 16-Jun-2001 ptendulk Added check_new_status_change procedure to obsolute
30 -- the old check_status_change api
31 -- 19-Jun-2001 ptendulk Modified Approval_Required_Flag function
32 -- 09-Jul-2001 ptendulk Added new function Check_Status_Change
33 -- 13-Sep-2001 slkrishn Added new function for amount rounding based on currency
34 -- 14-Jan-2001 sveerave Added send_wf_standalone_message procedure, and
35 -- Get_Resource_Role procedures for sending standalone mesages.
36 -- 18-Mar-2002 choang Added checkfile to dbdrv
37 -- 27-Mar-2002 dmvincen Added dialog and component validiation.
38 -- 17-May-2002 choang bug 2224836: changed get_sytem_timezone and get_user_timezone
39 -- to use HZ timezone profiles.
40 -- 06-Jun-2002 sveerave Added overloaded check_lookup_exists
41 -- which accepts view_application_id, query from fnd_lookups
42 -- 06-Jun-2002 sveerave Modified previous check_lookup_exists to call
43 -- newly created procedure.
44 -- 17-Jun-2002 sveerave Modified cursor in check_lookup_exists to have p_view_app_id
45 -- as NUMBER
46 -- 10-Jul-2002 musman Modified the procedure check_new_status_change, checking for
47 -- CAPL instead of TAPL if the object type is 'DELV'
48 -- 19-Dec-2002 mayjain Added get_install_info
49 -- 13-Oct-2003 yzhao get_qual_table_name_and_pk() for FUND, changed from ozf_fund_details_v to ozf_funds_all_b
50 -- Wed Oct 29 2003:3/44 PM RSSHARMA Added function get_party_name . Changed get_product_name to include FAMILY in Product level since the lookup
51 -- for product level for lumpsum , scandata and Net Accrual Offers has product level codes as PRODUCT AND FAMILY
52 -- Fri Jan 23 2004:3/20 PM RSSHARMA Added the following fuctions
53 -- 1. get_fnd_lookup_meaning
54 -- 2. get_currency_name
55 -- 3. get_media_name
56 -- 17-FEB-2005 feliu added functions
57 -- get_commited_amount
58 -- get_recal_commited_amount
59 -- get_utilized_amount
60 -- get_paid_amount
61 -- get_earned_amount
62 -- 19-May-05 Sahana Bug4382865: SQL Literal fix
63 -- 12-Jun-05 Ribha #4173825 Modified get_product_name
64 -- 31-Aug-05 mkothari Changed AMS to OZF for forecast tbl
65 -- 17-May-2006 asylvia fixed bug 5226685
66 -- Mon May 22 2006:5/59 PM rssharma Fixed bug # 5213655.Added getAttributeMethods to get AKAttribute long labels.
67 -- 09-Nov-2008 psomyaju Bug 7555174 - APPROVALS FOR 'GLOBAL' OFFERS - NO LEDGER DEFINED
68 -- 09-Nov-2008 psomyaju Bug 7415805 - INT1206.2:AMS:UNABLE TO APPROVE THE OFFER
69 -- 24-Nov-08 nirprasa Bug 7030415 - R12SIP WE CAN'T SETUP CURRENY CONVERSION TYPE FOR SPECIFIC OPERATING UNIT
70 -- 07-APR-2009 kdass Bug 8402334 - BUDGET UTILIZATION API TO ACCEPT ORG ID
71 -- 06/12/2009 kdass Bug 8532055 - ADD EXCHANGE RATE DATE PARAM TO OZF_FUND_UTILIZED_PUB.CREATE_FUND_ADJUSTMENT API
72 -- 05-Mar-2010 BKUNJAN ER 9382547 - TM SLA Uptake
73 -- 10-May-2010 BKUNJAN Bug 9666046 - TST1213:UNABLE TO POST THE ENTRIES TOGL DUE TO ERROR IN CREATE ACCOUNTING CC JOB
74 -- 15-Sep-2010 BKUNJAN Reverting the changes made via bug 9666046
75 -- 05-01-2011 muthsubr For TPA Parallel Execution ER Bug#9614703
76 -- 19-01-2011 nirprasa Bug#9614703 - All funds and act_budget records will be inserted to staging tables.
77 -- These four tables ozf_funds_all_b, ozf_act_budgets, ozf_funds_all_tl and ozf_mc_transactions_all will be updated
78 -- via concurrent prgm 'OZF: Update Budgets from Interface Tables'.
79 -- 7/4/2012 nepanda Bug 13744476 - datafix: 700+ offers stuck in budget pending validation
80 -- 13-12-2012 BKUNJAN Bug 15952083 - ORACLE TRADE MANAGEMENT CREATE ACCOUNTING HAS ERROR - ERROR IN REVENUE ACCT CREA
81 ---------------------------------------------------------------------
82 -- Added for use by bind_parse.
83 TYPE col_val_rec IS RECORD (
84 col_name VARCHAR2(2000),
85 col_op VARCHAR2(10),
86 col_value VARCHAR2(2000) );
87
88 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
89
90 ---------------------------------------------------------------------
91 -- FUNCTION
92 -- bind_parse
93 --
94 -- DESCRIPTION
95 -- Given a string containing the WHERE conditions in a WHERE
96 -- clause, return a tuple of column name and column value.
97 -- HISTORY
98 -- 25-Apr-2000 skarumur Created.
99 -- 26-Apr-2000 choang Modified to handle <> conditions.
100 ---------------------------------------------------------------------
101 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
102 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
103 OZF_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
104 OZF_UNEXP_ERROR CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.g_msg_lvl_unexp_error);
105 OZF_ERROR CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.g_msg_lvl_error);
106
107 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_UTILITY_PVT';
108 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvutlb.pls';
109
110 G_OZF_APP_ID CONSTANT NUMBER := 682;
111
112 PROCEDURE bind_parse (
113 p_string IN VARCHAR2,
114 x_col_val_tbl OUT NOCOPY col_val_tbl
115 );
116
117
118
119 --======================================================================
120 -- PROCEDURE
121 -- debug_message
122 --
123 -- PURPOSE
124 -- Writes the message to the log file for the spec'd level and module
125 -- if logging is enabled for this level and module
126 --
127 -- HISTORY
128 -- 01-Oct-2003 huili Create.
129 --======================================================================
130 PROCEDURE debug_message (p_log_level IN NUMBER,
131 p_module_name IN VARCHAR2,
132 p_text IN VARCHAR2)
133 IS
134 BEGIN
135 IF( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(p_log_level, p_module_name, p_text);
137 END IF;
138 END debug_message;
139
140
141 --======================================================================
142 -- PROCEDURE
143 -- log_message
144 --
145 -- PURPOSE
146 -- Writes a message to the log file if this level and module is enabled
147 -- The message gets set previously with FND_MESSAGE.SET_NAME,
148 -- SET_TOKEN, etc.
149 -- The message is popped off the message dictionary stack, if POP_MESSAGE
150 -- is TRUE. Pass FALSE for POP_MESSAGE if the message will also be
151 -- displayed to the user later.
152 -- Example usage:
153 -- FND_MESSAGE.SET_NAME(...); -- Set message
154 -- FND_MESSAGE.SET_TOKEN(...); -- Set token in message
155 -- FND_LOG.MESSAGE(..., FALSE); -- Log message
156 -- FND_MESSAGE.ERROR; -- Display message
157 --
158 -- HISTORY
159 -- 01-Oct-2003 huili Create.
160 --======================================================================
161
162 PROCEDURE log_message(p_log_level IN NUMBER,
163 p_module_name IN VARCHAR2,
164 p_RCS_ID IN VARCHAR2 := NULL,
165 p_pop_message IN BOOLEAN DEFAULT NULL)
166 IS
167 BEGIN
168 IF ( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
169 FND_LOG.MESSAGE(p_log_level, p_module_name, p_pop_message);
170 IF p_RCS_ID IS NOT NULL THEN
171 FND_MESSAGE.SET_TOKEN(G_RCS_ID, p_RCS_ID);
172 END IF;
173 END IF;
174 END log_message;
175
176 --======================================================================
177 -- FUNCTION
178 -- logging_enabled
179 --
180 -- PURPOSE
181 -- Return whether logging is enabled for a particular level
182 --
183 -- HISTORY
184 -- 03-Oct-2003 huili Create.
185 --======================================================================
186 FUNCTION logging_enabled (p_log_level IN NUMBER)
187 RETURN BOOLEAN
188 IS
189 BEGIN
190 RETURN (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
191 END;
192
193
194 ---------------------------------------------------------------------
195 -- FUNCTION
196 -- check_fk_exists
197 --
198 -- HISTORY
199 -- 05/14/99 cklee Created.
200 -- 25-Apr-2000 choang modified to use bind variables.
201 ---------------------------------------------------------------------
202 FUNCTION check_fk_exists(
203 p_table_name IN VARCHAR2,
204 p_pk_name IN VARCHAR2,
205 p_pk_value IN VARCHAR2,
206 p_pk_data_type IN NUMBER := g_number,
207 p_additional_where_clause IN VARCHAR2 := NULL
208 )
209 RETURN VARCHAR2
210 IS
211
212 l_sql VARCHAR2(4000);
213 l_count NUMBER;
214 l_bind_tbl col_val_tbl;
215
216 BEGIN
217 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
218 l_sql := l_sql || ' WHERE ' || UPPER(p_pk_name) || ' = :b1';
219
220 IF p_additional_where_clause IS NOT NULL THEN
221
222 -- Bug4382865: Start SQL Literal Fix
223 bind_parse (p_additional_where_clause, l_bind_tbl);
224
225 -- Support up to 4 WHERE conditions.
226 IF l_bind_tbl.COUNT <= 4 THEN
227 l_sql := l_sql || ' AND ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
228 FOR i IN 2..l_bind_tbl.COUNT LOOP
229 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
230 END LOOP;
231 ELSE
232 -- Exceeded the number of conditions supported
233 -- for bind variables.
234 l_sql := l_sql || ' WHERE ' || p_additional_where_clause;
235 END IF;
236 END IF;
237
238 l_sql := l_sql || ')';
239
240 IF (OZF_DEBUG_HIGH_ON) THEN
241 debug_message('SQL statement: '||l_sql);
242 END IF;
243
244 BEGIN
245 IF p_additional_where_clause IS NOT NULL THEN
246 IF l_bind_tbl.COUNT = 1 THEN
247 EXECUTE IMMEDIATE l_sql INTO l_count
248 USING p_pk_value, l_bind_tbl(1).col_value;
249 ELSIF l_bind_tbl.COUNT = 2 THEN
250 EXECUTE IMMEDIATE l_sql INTO l_count
251 USING p_pk_value, l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
252 ELSIF l_bind_tbl.COUNT = 3 THEN
253 EXECUTE IMMEDIATE l_sql INTO l_count
254 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
255 ELSIF l_bind_tbl.COUNT = 4 THEN
256 EXECUTE IMMEDIATE l_sql INTO l_count
257 USING p_pk_value, l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
258 ELSE
259 EXECUTE IMMEDIATE l_sql INTO l_count
260 USING p_pk_value;
261 END IF;
262 ELSE
263 EXECUTE IMMEDIATE l_sql INTO l_count
264 USING p_pk_value;
265 END IF;
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 l_count := 0;
269 END;
270 -- Bug4382865: End SQL Literal Fix
271
272 IF l_count = 0 THEN
273 RETURN FND_API.g_false;
274 ELSE
275 RETURN FND_API.g_true;
276 END IF;
277
278 END check_fk_exists;
279
280
281 ---------------------------------------------------------------------
282 -- FUNCTION
283 -- check_lookup_exists
284 --
285 -- HISTORY
286 -- 05/14/99 cklee Created.
287 -- 25-Apr-2000 choang Use bind variables.
288 -- 07-jun-2002 sveerave if table name is specifically not passed, changes
289 -- are made to call overloaded procedure.
290 ---------------------------------------------------------------------
291 FUNCTION check_lookup_exists(
292 p_lookup_table_name IN VARCHAR2 := g_ozf_lookups,
293 p_lookup_type IN VARCHAR2,
294 p_lookup_code IN VARCHAR2
295 )
296 Return VARCHAR2
297 IS
298
299 l_sql VARCHAR2(4000);
300 l_count NUMBER;
301
302 BEGIN
303
304 IF p_lookup_table_name = g_ozf_lookups THEN
305 return check_lookup_exists (
306 p_lookup_type => p_lookup_type
307 , p_lookup_code => p_lookup_code
308 , p_view_application_id => 682
309 );
310 ELSE
311 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
312 l_sql := l_sql || ' WHERE LOOKUP_TYPE = :b1';
313 l_sql := l_sql || ' AND LOOKUP_CODE = :b2';
314 l_sql := l_sql || ' AND ENABLED_FLAG = ''Y'')';
315
316 IF (OZF_DEBUG_HIGH_ON) THEN
317 debug_message('SQL statement: '||l_sql);
318 END IF;
319
320 BEGIN
321 EXECUTE IMMEDIATE l_sql INTO l_count
322 USING p_lookup_type, p_lookup_code;
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 l_count := 0;
326 END;
327 IF l_count = 0 THEN
328 RETURN FND_API.g_false;
329 ELSE
330 RETURN FND_API.g_true;
331 END IF;
332 END IF;
333
334 END check_lookup_exists;
335
336
337
338 ---------------------------------------------------------------------
339 -- FUNCTION
340 -- overloaded check_lookup_exists
341 -- PURPOSE
342 -- This function checks if a lookup_code is valid from fnd_lookups when
343 -- view_application_id is passed in.
344 -- HISTORY
345 -- 07-jun-2002 sveerave created.
346 -- 17-Jun-2002 sveerave Modified cursor to have p_view_app_id as NUMBER
347 ---------------------------------------------------------------------
348 FUNCTION check_lookup_exists(
349 p_lookup_type IN VARCHAR2,
350 p_lookup_code IN VARCHAR2,
351 p_view_application_id IN NUMBER
352 )
353 Return VARCHAR2
354 IS
355 CURSOR cur_check_lookup_exists( p_lookup_type VARCHAR2
356 , p_lookup_code VARCHAR2
357 , p_view_app_id NUMBER) IS
358 SELECT 1 FROM fnd_lookup_values lkup
359 WHERE lkup.LOOKUP_TYPE = p_lookup_type
360 AND lkup.LOOKUP_CODE = p_lookup_code
361 AND lkup.view_application_id = p_view_app_id
362 AND lkup.ENABLED_FLAG = 'Y'
363 AND lkup.language = USERENV('LANG')
364 AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
365 ), ' ', '0'
366 , NULL, '0'
367 , substrb(userenv('CLIENT_INFO'),55,10
368 )
369 )
370 );
371 l_count NUMBER := 0;
372
373 BEGIN
374
375 OPEN cur_check_lookup_exists( p_lookup_type
376 , p_lookup_code
377 , p_view_application_id);
378 FETCH cur_check_lookup_exists INTO l_count;
379 CLOSE cur_check_lookup_exists;
380
381 IF l_count = 0 THEN
382 RETURN FND_API.g_false;
383 ELSE
384 RETURN FND_API.g_true;
385 END IF;
386
387 END check_lookup_exists;
388
389
390 ---------------------------------------------------------------------
391 -- FUNCTION
392 -- check_uniqueness
393 --
394 -- HISTORY
395 -- 05/19/99 cklee Created.
396 -- 25-Apr-2000 choang Use bind_parse to enable use of bind variables.
397 ---------------------------------------------------------------------
398 FUNCTION check_uniqueness(
399 p_table_name IN VARCHAR2,
400 p_where_clause IN VARCHAR2
401 )
402 RETURN VARCHAR2
403 IS
404
405 l_sql VARCHAR2(4000);
406 l_count NUMBER;
407
408 l_bind_tbl col_val_tbl;
409
410 BEGIN
411
412 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
413 -- l_sql := l_sql || ' WHERE ' || p_where_clause;
414
415 bind_parse (p_where_clause, l_bind_tbl);
416
417 --
418 -- choang - 25-Apr-2000
419 -- Support up to 4 WHERE conditions for uniqueness. If
420 -- the number of conditions changes, then must also revise
421 -- the execute portion of the code.
422 IF l_bind_tbl.COUNT <= 4 THEN
423 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
424 FOR i IN 2..l_bind_tbl.COUNT LOOP
425 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
426 END LOOP;
427 ELSE
428 -- Exceeded the number of conditions supported
429 -- for bind variables.
430 l_sql := l_sql || ' WHERE ' || p_where_clause;
431 END IF;
432
433 l_sql := l_sql || ')';
434
435 IF (OZF_DEBUG_HIGH_ON) THEN
436 debug_message('SQL statement: '||l_sql);
437 END IF;
438
439 --
440 -- choang - 25-Apr-2000
441 -- Modify here if number of WHERE conditions
442 -- supported changes.
443 BEGIN
444 IF l_bind_tbl.COUNT = 1 THEN
445 EXECUTE IMMEDIATE l_sql INTO l_count
446 USING l_bind_tbl(1).col_value;
447 ELSIF l_bind_tbl.COUNT = 2 THEN
448 EXECUTE IMMEDIATE l_sql INTO l_count
449 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
450 ELSIF l_bind_tbl.COUNT = 3 THEN
451 EXECUTE IMMEDIATE l_sql INTO l_count
452 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
453 ELSIF l_bind_tbl.COUNT = 4 THEN
454 EXECUTE IMMEDIATE l_sql INTO l_count
455 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
456 ELSE
457 EXECUTE IMMEDIATE l_sql INTO l_count;
458 END IF;
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 l_count := 0;
462 END;
463
464 IF l_count = 0 THEN
465 RETURN FND_API.g_true;
466 ELSE
467 RETURN FND_API.g_false;
468 END IF;
469
470 END check_uniqueness;
471
472
473 ---------------------------------------------------------------------
474 -- FUNCTION
475 -- is_Y_or_N
476 --
477 -- HISTORY
478 -- 05/19/99 cklee Created.
479 ---------------------------------------------------------------------
480 FUNCTION is_Y_or_N(
481 p_value IN VARCHAR2
482 )
483 RETURN VARCHAR2
484 IS
485 BEGIN
486 IF p_value = 'Y' or p_value = 'N' THEN
487 RETURN FND_API.g_true;
488 ELSE
489 RETURN FND_API.g_false;
490 END IF;
491 END is_Y_or_N;
492
493
494 ---------------------------------------------------------------------
495 -- PROCEDURE
496 -- debug_message
497 --
498 -- HISTORY
499 -- 10/10/99 holiu Created.
500 -- 13-mar-2002 choang bug 2262529 - g_miss_char is a nil char which
501 -- caused some problems in the java layer; removed
502 -- g_miss_char from the error message.
503 -- 14-mar-2002 choang added text G_MISS_CHAR to be displayed in place
504 -- of nil char for debugging purposes.
505 -- 09-Dec-2002 choang All calls to debug should check for msg level
506 -- before calling; removing the check in the debug
507 -- procedure to avoid reduncy.
508 ---------------------------------------------------------------------
509 PROCEDURE debug_message(
510 p_message_text IN VARCHAR2,
511 p_message_level IN NUMBER := NULL
512 )
513 IS
514 BEGIN
515 FND_MESSAGE.set_name('OZF', 'OZF_API_DEBUG_MESSAGE');
516 FND_MESSAGE.set_token('TEXT', REPLACE (p_message_text, FND_API.G_MISS_CHAR, 'G_MISS_CHAR'));
517 FND_MSG_PUB.add;
518 END debug_message;
519
520
521 ---------------------------------------------------------------------
522 -- PROCEDURE
523 -- error_message
524 --
525 -- HISTORY
526 -- 11/01/99 holiu Created.
527 ---------------------------------------------------------------------
528 PROCEDURE error_message(
529 p_message_name VARCHAR2,
530 p_token_name VARCHAR2 := NULL,
531 P_token_value VARCHAR2 := NULL
532 )
533 IS
534 BEGIN
535 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
536 FND_MESSAGE.set_name('OZF', p_message_name);
537 IF p_token_name IS NOT NULL THEN
538 FND_MESSAGE.set_token(p_token_name, p_token_value);
539 END IF;
540 FND_MSG_PUB.add;
541 END IF;
542 END error_message;
543
544
545 ---------------------------------------------------------------------
546 -- PROCEDURE
547 -- display_messages
548 --
549 -- HISTORY
550 -- 10/26/99 holiu Created.
551 ---------------------------------------------------------------------
552 PROCEDURE display_messages
553 IS
554 l_count NUMBER;
555 l_msg VARCHAR2(2000);
556 BEGIN
557 l_count := FND_MSG_PUB.count_msg;
558 FOR i IN 1 .. l_count LOOP
559 l_msg := FND_MSG_PUB.get(i, FND_API.g_false);
560 -- holiu: remove since adchkdrv does not like it
561 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
562 END LOOP;
563 END display_messages;
564
565
566 ---------------------------------------------------------------------
567 -- NAME
568 -- create_log
569 --
570 -- HISTORY
571 -- 09/21/99 ptendulk Created.
572 -- 12-Jan-2000 choang Added autonomous transaction.
573 ---------------------------------------------------------------------
574 PROCEDURE create_log(
575 x_return_status OUT NOCOPY VARCHAR2,
576 p_arc_log_used_by IN VARCHAR2,
577 p_log_used_by_id IN VARCHAR2,
578 p_msg_data IN VARCHAR2,
579 p_msg_level IN NUMBER DEFAULT NULL,
580 p_msg_type IN VARCHAR2 DEFAULT NULL,
581 p_desc IN VARCHAR2 DEFAULT NULL,
582 p_budget_id IN NUMBER DEFAULT NULL,
583 p_threshold_id IN NUMBER DEFAULT NULL,
584 p_transaction_id IN NUMBER DEFAULT NULL,
585 p_notification_creat_date IN DATE DEFAULT NULL,
586 p_activity_log_id IN NUMBER DEFAULT NULL
587 )
588 IS
589 PRAGMA AUTONOMOUS_TRANSACTION;
590 x_rowid VARCHAR2(30);
591 l_act_log_id ams_act_logs.activity_log_id%TYPE;
592 l_log_tran_id ams_act_logs.log_transaction_id%TYPE;
593
594 CURSOR c_log_seq IS
595 SELECT ams_act_logs_s.NEXTVAL,
596 ams_act_logs_transaction_id_s.NEXTVAL
597 FROM DUAL;
598
599 CURSOR c_log(l_my_log_id VARCHAR2) IS
600 SELECT rowid
601 FROM ams_act_logs
602 WHERE activity_log_id = l_my_log_id;
603
604 BEGIN
605
606 -- Standard Start of API savepoint
607 SAVEPOINT Create_act_log;
608
609 -- Initialize API return status to success
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611
612 --
613 -- API body
614 --
615
616 -- open cursor AND fetch into local variable
617 OPEN c_log_seq;
618 FETCH c_log_seq INTO l_act_log_id,l_log_tran_id ;
619 CLOSE c_log_seq;
620
621
622 INSERT INTO ams_act_logs (
623 activity_log_id
624 -- standard who columns
625 ,last_update_date
626 ,last_updated_by
627 ,creation_date
628 ,created_by
629 ,last_update_login
630 ,object_version_number
631 ,act_log_used_by_id
632 ,arc_act_log_used_by
633 ,log_transaction_id
634 ,log_message_text
635 ,log_message_level
636 ,log_message_type
637 ,description
638 ,budget_id
639 ,threshold_id
640 ,notification_creation_date
641 )
642 VALUES (
643 NVL(p_activity_log_id,l_act_log_id)
644 -- standard who columns
645 ,SYSDATE
646 ,FND_GLOBAL.User_Id
647 ,SYSDATE
648 ,FND_GLOBAL.User_Id
649 ,FND_GLOBAL.Conc_Login_Id
650 ,1 -- Object Version Number
651 ,p_log_used_by_id
652 ,p_arc_log_used_by
653 ,NVL(p_transaction_id,l_log_tran_id)
654 ,p_msg_data
655 ,p_msg_level
656 ,p_msg_type
657 ,p_desc
658 ,p_budget_id
659 ,p_threshold_id
660 ,p_notification_creat_date
661 ) ;
662
663
664 OPEN c_log(l_act_log_id);
665 FETCH c_log INTO x_rowid;
666 IF (c_log%NOTFOUND) THEN
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
668 END IF;
669 CLOSE c_log;
670
671 --
672 -- END of API body.
673 --
674
675 COMMIT;
676 EXCEPTION
677
678 WHEN OTHERS THEN
679 ROLLBACK TO create_act_log;
680 x_return_status := FND_API.g_ret_sts_unexp_error;
681
682 END create_log;
683
684
685 ---------------------------------------------------------------------
686 -- PROCEDURE
687 -- get_qual_table_name_and_pk
688 --
689 -- HISTORY
690 -- 05/20/99 tdonohoe Created.
691 -- 10/13/99 ptendulk Removed Parameter p_qual_id;
692 -- Added qualifiers DELI, EVEH;
693 -- Changed the name from Event Offerings to Event Offers
694 -- Changed the name from Event Offerings to Event Offers
695 -- 01/06/99 ptendulk Changed the return Statuses to Standard
696 -- return statuses
697 -- 04/24/00 tdonohoe Added Qualifier 'FCST' Forecast.
698 -- 06/14/00 ptendulk Added qualifier 'OFFR' Offers
699 -- 06/28/00 rchahal Added Qualifier 'FUND' Fund.
700 -- 30-Jan-2001 ptendulk Modified Qualifier table for Schedules.
701 -- 06-Apr-2001 choang Added DIWB, MODL and SCOR in get_qual_table_name_and_pk
702 -- added error message if no valid sys_qual mapped.
703 -- 09-Apr-2001 choang added CELL
704 -- 13-Jun-2001 ptendulk Added EONE
705 -- 15-Jun-2001 choang Changed OFFR to return ozf_offers and qp_list_header_id.
706 ---------------------------------------------------------------------
707 PROCEDURE get_qual_table_name_and_pk(
708 p_sys_qual IN VARCHAR2,
709 x_return_status OUT NOCOPY VARCHAR2,
710 x_table_name OUT NOCOPY VARCHAR2,
711 x_pk_name OUT NOCOPY VARCHAR2
712 )
713 IS
714 BEGIN
715 -- initialize return status
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717
718 IF (p_sys_qual ='CSCH') THEN
719 -- Start of code modified by ptendulk on 30-Jan-2001
720 --x_table_name := 'AMS_CAMPAIGN_SCHEDULES';
721 --x_pk_name := 'CAMPAIGN_SCHEDULE_ID';
722 -- End of code modified by ptendulk on 30-Jan-2001
723 x_table_name := 'AMS_CAMPAIGN_SCHEDULES_B';
724 x_pk_name := 'SCHEDULE_ID';
725 ELSIF (p_sys_qual ='CAMP') THEN
726 -- Start of code modified by ptendulk on 30-Jan-2001
727 --x_table_name := 'AMS_CAMPAIGNS_VL';
728 -- End of code modified by ptendulk on 30-Jan-2001
729 x_table_name := 'AMS_CAMPAIGNS_ALL_B';
730 x_pk_name := 'CAMPAIGN_ID';
731 ELSIF (p_sys_qual ='EVEO') THEN
732 x_table_name := 'AMS_EVENT_OFFERS_VL';
733 x_pk_name := 'EVENT_OFFER_ID';
734 ELSIF (p_sys_qual ='EONE') THEN
735 x_table_name := 'AMS_EVENT_OFFERS_VL';
736 x_pk_name := 'EVENT_OFFER_ID';
737 ELSIF (p_sys_qual ='EVEH') THEN
738 x_table_name := 'AMS_EVENT_HEADERS_VL';
739 x_pk_name := 'EVENT_HEADER_ID';
740 ELSIF (p_sys_qual ='DELV') THEN
741 x_table_name := 'AMS_DELIVERABLES_VL';
742 x_pk_name := 'DELIVERABLE_ID';
743 ELSIF (p_sys_qual ='AMET') THEN
744 x_table_name := 'OZF_ACT_METRICS_ALL';
745 x_pk_name := 'ACTIVITY_METRIC_ID';
746 --=========================================================
747 -- Following line of code is added by ptendulk on 14Jun2000
748 --=========================================================
749 ELSIF (p_sys_qual ='OFFR') THEN --added tdonohoe 04/24/2000
750 x_table_name := 'OZF_OFFERS';
751 x_pk_name := 'QP_LIST_HEADER_ID';
752 ELSIF (p_sys_qual ='FCST') THEN --added tdonohoe 04/24/2000
753 x_table_name := 'OZF_ACT_FORECASTS_ALL';
754 x_pk_name := 'FORECAST_ID';
755 ELSIF (p_sys_qual ='FUND') THEN --added rchahal 06/28/2000
756 -- yzhao 10/13/2003 fix bug 3167497 changed from OZF_FUND_DETAILS_V to ozf_funds_all_b
757 x_table_name := 'OZF_FUNDS_ALL_B'; -- yzhao: 'OZF_FUND_DETAILS_V';
758 x_pk_name := 'FUND_ID';
759 ELSIF (p_sys_qual ='PRIC') THEN --added skarumur 12/17/2000
760 x_table_name := 'AMS_PRICE_LIST_ATTRIBUTES';
761 x_pk_name := 'QP_LIST_HEADER_ID';
762 ELSIF (p_sys_qual ='LIST') THEN --added gjoby 03/26/2001
763 x_table_name := 'OZF_LIST_HEADERS_VL';
764 x_pk_name := 'LIST_HEADER_ID';
765 ELSIF (p_sys_qual ='IMPH') THEN --added gjoby 03/26/2001
766 x_table_name := 'AMS_IMP_LIST_HEADERS_VL';
767 x_pk_name := 'IMPORT_LIST_HEADER_ID';
768 ELSIF (p_sys_qual ='SQL') THEN --added gjoby 03/26/2001
769 x_table_name := 'OZF_LIST_QUERIES_ALL';
770 x_pk_name := 'LIST_QUERY_ID';
771 ELSIF p_sys_qual = 'DIWB' THEN
772 x_table_name := 'OZF_DISCOVERER_SQL';
773 x_pk_name := 'DISCOVERER_SQL_ID';
774 ELSIF p_sys_qual = 'MODL' THEN
775 x_table_name := 'OZF_DM_MODELS_ALL_B';
776 x_pk_name := 'MODEL_ID';
777 ELSIF p_sys_qual = 'SCOR' THEN
778 x_table_name := 'OZF_DM_SCORES_ALL_B';
779 x_pk_name := 'SCORE_ID';
780 ELSIF p_sys_qual = 'CELL' THEN
781 x_table_name := 'AMS_CELLS_ALL_B';
782 x_pk_name := 'CELL_ID';
783 ELSIF (p_sys_qual = 'RCAM') THEN
784 x_table_name := 'AMS_CAMPAIGNS_ALL_B';
785 x_pk_name := 'CAMPAIGN_ID';
786 ELSIF p_sys_qual = 'DILG' THEN -- Added dmvincen 03/27/2002
787 x_table_name := 'OZF_DIALOGS_ALL_B';
788 x_pk_name := 'DIALOG_ID';
789 ELSIF p_sys_qual in -- Added dmvincen 03/27/2002
790 ('OZF_COMP_START', 'OZF_COMP_SHOW_WEB_PAGE', 'OZF_COMP_END') THEN
791 x_table_name := 'OZF_DLG_FLOW_COMPS_B';
792 x_pk_name := 'FLOW_COMPONENT_ID';
793 ELSE
794 OZF_Utility_PVT.error_message ('OZF_INVALID_SYS_QUAL', 'SYS_QUALIFIER', p_sys_qual);
795 x_return_status := FND_API.g_ret_sts_unexp_error;
796 x_table_name := NULL;
797 x_pk_name := NULL;
798 END IF;
799
800 END get_qual_table_name_and_pk;
801
802
803 --------------------------------------------------------------------
804 -- NAME
805 -- get_source_code
806 --
807 -- HISTORY
808 -- 08/18/99 tdonohoe Created.
809 --------------------------------------------------------------------
810 PROCEDURE get_source_code(
811 p_activity_type IN VARCHAR2,
812 p_activity_id IN NUMBER,
813 x_return_status OUT NOCOPY VARCHAR2,
814 x_source_code OUT NOCOPY VARCHAR2 ,
815 x_source_id OUT NOCOPY NUMBER
816 )
817 IS
818 BEGIN
819
820 SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
821 FROM ams_source_codes
822 WHERE arc_source_code_for = UPPER(p_activity_type)
823 AND source_code_for_id = UPPER(p_activity_id);
824
825
826
827 IF SQL%NOTFOUND THEN
828 x_return_status := FND_API.G_FALSE;
829 ELSE
830 x_return_status := FND_API.G_TRUE;
831 END IF;
832
833
834 EXCEPTION
835
836 WHEN OTHERS THEN
837 x_source_code := NULL;
838 x_source_id := NULL;
839 x_return_status := FND_API.G_FALSE;
840
841 End;
842
843
844 ---------------------------------------------------------------------
845 -- FUNCTION
846 -- get_object_name
847 --
848 -- HISTORY
849 -- 10/15/99 holiu Created.
850 -- 11/03/99 mpande inserted deliverable,event
851 -- 11/16/99 tdonohoe inserted campaign schedule.
852 -- 09-Dec-1999 choang Changed references of ozf_event_offers_all_vl to
853 -- ams_event_offers_vl and ozf_event_headers_all_vl
854 -- to ams_event_headers_vl.
855 -- 24-Aug-2000 choang Added FUND
856 -- 28-Sep-2000 choang Added PRTN
857 -- 13-Jun-2001 ptendulk Added EONE
858 -- 15-Jun-2001 choang changed PRNT to PTNR
859 --------------------------------------------------------------------
860 FUNCTION get_object_name(
861 p_sys_arc_qualifier IN VARCHAR2,
862 p_object_id IN NUMBER
863 )
864 RETURN VARCHAR2
865 IS
866
867 l_object_name VARCHAR2(1000);
868
869 CURSOR c_campaign(p_object_id IN NUMBER) IS
870 SELECT campaign_name
871 FROM ams_campaigns_vl
872 WHERE campaign_id = p_object_id;
873
874 --added 11/16/99 tdonohoe
875 CURSOR c_campaign_sched(p_object_id IN NUMBER) IS
876 SELECT c.campaign_name
877 FROM ams_campaigns_vl c,
878 ams_campaign_schedules s
879 WHERE s.campaign_schedule_id = p_object_id
880 AND s.campaign_id = c.campaign_id;
881
882 CURSOR c_deliv(p_object_id IN NUMBER) IS
883 SELECT deliverable_name
884 FROM ams_deliverables_vl
885 WHERE deliverable_id = p_object_id;
886
887 CURSOR c_event_header(p_object_id IN NUMBER) IS
888 SELECT event_header_name
889 FROM ams_event_headers_vl
890 WHERE event_header_id = p_object_id;
891
892 CURSOR c_event_offer(p_object_id IN NUMBER) IS
893 SELECT event_offer_name
894 FROM ams_event_offers_vl
895 WHERE event_offer_id = p_object_id;
896 --- updated by mpande 01/30/2001 to look into ozf_funds_all_vl not ozf_funds_vl
897 CURSOR c_fund (p_object_id IN NUMBER) IS
898 SELECT short_name
899 FROM ozf_funds_all_vl
900 WHERE fund_id = p_object_id;
901
902 CURSOR c_partner (p_object_id IN NUMBER) IS
903 SELECT party_name
904 FROM hz_parties
905 WHERE party_id = p_object_id;
906 BEGIN
907
908 l_object_name := NULL;
909
910 IF p_sys_arc_qualifier IS NULL OR p_object_id IS NULL THEN
911 RETURN l_object_name;
912 END IF;
913
914 IF p_sys_arc_qualifier = 'CAMP' THEN
915 OPEN c_campaign(p_object_id);
916 FETCH c_campaign INTO l_object_name;
917 CLOSE c_campaign;
918 ELSIF p_sys_arc_qualifier = 'CSCH' THEN
919 OPEN c_campaign_sched(p_object_id);
920 FETCH c_campaign_sched INTO l_object_name;
921 CLOSE c_campaign_sched;
922 ELSIF p_sys_arc_qualifier = 'DELI' THEN
923 OPEN c_deliv(p_object_id);
924 FETCH c_deliv INTO l_object_name;
925 CLOSE c_deliv;
926 ELSIF p_sys_arc_qualifier = 'EVEH' THEN
927 OPEN c_event_header(p_object_id);
928 FETCH c_event_header INTO l_object_name;
929 CLOSE c_event_header;
930 ELSIF p_sys_arc_qualifier = 'EVEO' THEN
931 OPEN c_event_offer(p_object_id);
932 FETCH c_event_offer INTO l_object_name;
933 CLOSE c_event_offer;
934 ELSIF p_sys_arc_qualifier = 'EONE' THEN
935 OPEN c_event_offer(p_object_id);
936 FETCH c_event_offer INTO l_object_name;
937 CLOSE c_event_offer;
938 ELSIF p_sys_arc_qualifier = 'FUND' THEN
939 OPEN c_fund (p_object_id);
940 FETCH c_fund INTO l_object_name;
941 CLOSE c_fund;
942 ELSIF p_sys_arc_qualifier = 'PTNR' THEN
943 OPEN c_partner (p_object_id);
944 FETCH c_partner INTO l_object_name;
945 CLOSE c_partner;
946 END IF;
947
948 RETURN l_object_name;
949
950 END get_object_name;
951
952
953 ---------------------------------------------------------------------
954 -- PROCEDURE
955 -- Convert_Currency
956 -- NOTE
957 -- Modified from code done by ptendulk.
958 -- HISTORY
959 -- 08-Dec-1999 choang Created.
960 -- 31-Aug-2000 ptendulk Added x_conversion_type parameter to the
961 -- Convert_Closest_Amount procedure
962 -- 09-Oct-2000 choang Modified error message handling for no rate
963 -- and invalid currency.
964 ---------------------------------------------------------------------
965 PROCEDURE Convert_Currency (
966 x_return_status OUT NOCOPY VARCHAR2,
967 p_from_currency IN VARCHAR2,
968 p_to_currency IN VARCHAR2,
969 p_conv_date IN DATE DEFAULT SYSDATE,
970 p_from_amount IN NUMBER,
971 x_to_amount OUT NOCOPY NUMBER
972 )
973 IS
974 L_CONVERSION_TYPE_PROFILE CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
975 L_USER_RATE CONSTANT NUMBER := 1; -- Currenty not used.
976 L_MAX_ROLL_DAYS CONSTANT NUMBER := -1; -- Negative so API rolls back to find the last conversion rate.
977 l_denominator NUMBER; -- Not used in Marketing.
978 l_numerator NUMBER; -- Not used in Marketing.
979 l_rate NUMBER; -- Not used in Marketing.
980 l_conversion_type VARCHAR2(30); -- Currency conversion type; see API documention for details.
981 l_conv_date DATE;
982 BEGIN
983 -- Initialize return status.
984 x_return_status := FND_API.G_RET_STS_SUCCESS;
985
986 -- Get the currency conversion type from profile option
987 l_conversion_type := FND_PROFILE.Value (L_CONVERSION_TYPE_PROFILE);
988
989 --bug 8532055
990 IF p_conv_date = FND_API.G_MISS_DATE OR p_conv_date IS NULL THEN
991 l_conv_date := SYSDATE;
992 ELSE
993 l_conv_date := p_conv_date;
994 END IF;
995
996 -- Call the proper GL API to convert the amount.
997 GL_Currency_API.Convert_Closest_Amount (
998 x_from_currency => p_from_currency,
999 x_to_currency => p_to_currency,
1000 x_conversion_date => l_conv_date,
1001 x_conversion_type => l_conversion_type,
1002 x_user_rate => L_USER_RATE,
1003 x_amount => p_from_amount,
1004 x_max_roll_days => L_MAX_ROLL_DAYS,
1005 x_converted_amount => x_to_amount,
1006 x_denominator => l_denominator,
1007 x_numerator => l_numerator,
1008 x_rate => l_rate
1009 );
1010 EXCEPTION
1011 WHEN GL_Currency_API.NO_RATE THEN
1012 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1013 FND_MESSAGE.Set_Name ('OZF', 'OZF_NO_RATE');
1014 FND_MESSAGE.Set_Token ('CURRENCY_FROM', p_from_currency);
1015 FND_MESSAGE.Set_Token ('CURRENCY_TO', p_to_currency);
1016 FND_MSG_PUB.Add;
1017 END IF;
1018 x_return_status := FND_API.G_RET_STS_ERROR;
1019 WHEN GL_Currency_API.INVALID_CURRENCY THEN
1020 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1021 FND_MESSAGE.Set_Name ('OZF', 'OZF_INVALID_CURR');
1022 FND_MESSAGE.Set_Token ('CURRENCY_FROM', p_from_currency);
1023 FND_MESSAGE.Set_Token ('CURRENCY_TO', p_to_currency);
1024 FND_MSG_PUB.Add;
1025 END IF;
1026 x_return_status := FND_API.G_RET_STS_ERROR;
1027 END Convert_Currency;
1028
1029 ---------------------------------------------------------------------
1030 -- PROCEDURE
1031 -- get_lookup_meaning
1032 -- created by mpande 01/11/00
1033 -- PURPOSE
1034 -- This procedure will return the meaning from ozf_lookups if
1035 -- you pass the right lookup_type and lookup_code
1036 -- HISTORY
1037 -- 28-Apr-2000 choang Modified to use explicit cursor.
1038 -- 07-Aug-2000 choang Added close cursor for success conditions
1039 -- in the fetch.
1040 ---------------------------------------------------------------------
1041
1042 PROCEDURE get_lookup_meaning (
1043 p_lookup_type IN VARCHAR2,
1044 p_lookup_code IN VARCHAR2,
1045 x_return_status OUT NOCOPY VARCHAR2,
1046 x_meaning OUT NOCOPY VARCHAR2
1047 )
1048 IS
1049 CURSOR c_meaning IS
1050 SELECT meaning
1051 FROM ozf_lookups
1052 WHERE lookup_type = UPPER (p_lookup_type)
1053 AND lookup_code = UPPER (p_lookup_code);
1054 BEGIN
1055 OPEN c_meaning;
1056 FETCH c_meaning INTO x_meaning;
1057 IF c_meaning%NOTFOUND THEN
1058 CLOSE c_meaning;
1059 x_return_status := FND_API.G_RET_STS_ERROR;
1060 x_meaning:= NULL;
1061 ELSE
1062 CLOSE c_meaning;
1063 x_return_status := FND_API.G_RET_STS_SUCCESS;
1064 END IF;
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 IF c_meaning%ISOPEN THEN
1068 CLOSE c_meaning;
1069 END IF;
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071 x_meaning := NULL;
1072 END get_lookup_meaning;
1073
1074
1075 ---------------------------------------------------------------------
1076 -- PROCEDURE
1077 -- get_System_Timezone
1078 --
1079 -- PURPOSE
1080 -- This procedure will return the timezone from the System Timezone profile option
1081 -- HISTORY created 04/24/2000 sugupta
1082 -- 17-May-2002 choang bug 2224836: changed to use SERVER_TIMEZONE_ID
1083 ---------------------------------------------------------------------
1084 PROCEDURE get_System_Timezone(
1085
1086 x_return_status OUT NOCOPY VARCHAR2,
1087 x_sys_time_id OUT NOCOPY NUMBER,
1088 x_sys_time_name OUT NOCOPY VARCHAR2
1089 ) IS
1090
1091 l_sys_time_id NUMBER;
1092 l_sys_name VARCHAR2(80);
1093
1094 cursor c_get_name(l_time_id IN NUMBER) is
1095 select NAME
1096 from HZ_TIMEZONES_VL
1097 where TIMEZONE_ID = l_time_id;
1098
1099 BEGIN
1100 -- Initialize API return status to success
1101 x_return_status := FND_API.G_RET_STS_SUCCESS;
1102 l_sys_time_id := FND_PROFILE.VALUE('SERVER_TIMEZONE_ID');
1103 OPEN c_get_name(l_sys_time_id);
1104 FETCH c_get_name into l_sys_name;
1105 IF (c_get_name%NOTFOUND) THEN
1106 CLOSE c_get_name;
1107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1108 return;
1109 END IF;
1110 CLOSE c_get_name;
1111
1112 x_sys_time_id := l_sys_time_id;
1113 x_sys_time_name := l_sys_name;
1114 EXCEPTION
1115 WHEN NO_DATA_FOUND THEN
1116 IF (c_get_name%ISOPEN) THEN
1117 CLOSE c_get_name;
1118 END IF;
1119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1120 END get_System_Timezone;
1121
1122 ---------------------------------------------------------------------
1123 -- PROCEDURE
1124 -- get_User_Timezone
1125 --
1126 -- PURPOSE
1127 -- This procedure will return the timezone from the User Timezone profile option
1128 -- HISTORY created 04/24/2000 sugupta
1129 -- 17-May-2002 choang bug 2224836: changed to use CLIENT_TIMEZONE_ID
1130 ---------------------------------------------------------------------
1131 PROCEDURE get_User_Timezone(
1132
1133 x_return_status OUT NOCOPY VARCHAR2,
1134 x_user_time_id OUT NOCOPY NUMBER,
1135 x_user_time_name OUT NOCOPY VARCHAR2
1136 ) IS
1137
1138 l_user_time_id NUMBER;
1139 l_user_time_name VARCHAR2(80);
1140
1141 cursor get_name(l_time_id IN NUMBER) is
1142 select NAME
1143 from HZ_TIMEZONES_VL
1144 where TIMEZONE_ID = l_time_id;
1145
1146 BEGIN
1147 -- Initialize API return status to success
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149 l_user_time_id := FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID');
1150 OPEN get_name(l_user_time_id);
1151 FETCH get_name into l_user_time_name;
1152 IF (get_name%NOTFOUND) THEN
1153 CLOSE get_name;
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1155 return;
1156 END IF;
1157 CLOSE get_name;
1158
1159 x_user_time_id := l_user_time_id;
1160 x_user_time_name := l_user_time_name;
1161 EXCEPTION
1162 WHEN NO_DATA_FOUND THEN
1163 IF (get_name%ISOPEN) THEN
1164 CLOSE get_name;
1165 END IF;
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1167 END get_User_Timezone;
1168
1169 -------------------------------------------------------------------------------------------------
1170 -- PROCEDURE
1171 -- Convert_Timezone
1172 --
1173 -- PURPOSE
1174 -- This procedure will take the user timezone and the input time, depending on the parameter
1175 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
1176 -- HISTORY
1177 -- 04/24/2000 sugupta created
1178 -- 04/26/2000 ptendulk Modified Added a parameter which will tell
1179 -- which timezone to convert time into.
1180 -- If the convert type is 'SYS' then input time will be
1181 -- converted into system timezone else it will be
1182 -- converted to user timezone .
1183 ---------------------------------------------------------------------------------------------------
1184 PROCEDURE Convert_Timezone(
1185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_count OUT NOCOPY NUMBER,
1188 x_msg_data OUT NOCOPY VARCHAR2,
1189
1190 p_user_tz_id IN NUMBER := null,
1191 p_in_time IN DATE , -- required
1192 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
1193
1194 x_out_time OUT NOCOPY DATE
1195 ) IS
1196
1197 l_sys_time_id NUMBER;
1198 l_user_tz_id NUMBER := p_user_tz_id ;
1199 l_sys_time_name VARCHAR2(80);
1200 l_user_time_name VARCHAR2(80);
1201 l_return_status VARCHAR2(1); -- Return value from procedures
1202
1203 l_from_timezone_id NUMBER ;
1204 l_to_timezone_id NUMBEr ;
1205 BEGIN
1206
1207 -- Initialize API return status to success
1208 x_return_status := FND_API.G_RET_STS_SUCCESS;
1209
1210 get_System_Timezone(
1211 l_return_status,
1212 l_sys_time_id,
1213 l_sys_time_name);
1214
1215 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
1216 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1217 THEN
1218 x_return_status := l_return_status;
1219 RETURN;
1220 END IF;
1221
1222 -- If the user timezone is not sent
1223 -- get it from profiles
1224 IF l_user_tz_id IS NULL THEN
1225 Get_User_Timezone(
1226 x_return_status => l_return_status,
1227 x_user_time_id => l_user_tz_id ,
1228 x_user_time_name => l_user_time_name
1229 ) ;
1230 END IF;
1231
1232 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
1233 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1234 THEN
1235 x_return_status := l_return_status;
1236 RETURN;
1237 END IF;
1238
1239 IF p_convert_type = 'SYS' THEN
1240 l_from_timezone_id := l_user_tz_id ;
1241 l_to_timezone_id := l_sys_time_id ;
1242 ELSIF p_convert_type = 'USER' THEN
1243 l_from_timezone_id := l_sys_time_id ;
1244 l_to_timezone_id := l_user_tz_id ;
1245 END IF;
1246
1247 HZ_TIMEZONE_PUB.get_time(
1248 p_api_version => 1.0,
1249 p_init_msg_list => p_init_msg_list,
1250 p_source_tz_id => l_from_timezone_id ,
1251 p_dest_tz_id => l_to_timezone_id ,
1252 p_source_day_time => p_in_time,
1253 x_dest_day_time => x_out_time,
1254 x_return_status => x_return_status,
1255 x_msg_count => x_msg_count,
1256 x_msg_data => x_msg_data
1257 );
1258
1259 END Convert_Timezone ;
1260
1261
1262 ---------------------------------------------------------------------
1263 -- PROCEDURE
1264 -- bind_parse
1265 -- USAGE
1266 -- bind_parse (varchar2, col_val_tbl);
1267 -- The input string must have a space between the AND and operator clause
1268 -- and it must exclude the initial WHERE/AND statement.
1269 -- Example: source_code = 'xyz' and campaign_id <> 1
1270 ---------------------------------------------------------------------
1271 PROCEDURE bind_parse (
1272 p_string IN VARCHAR2,
1273 x_col_val_tbl OUT NOCOPY col_val_tbl)
1274 IS
1275 l_new_str VARCHAR2(4000);
1276 l_str VARCHAR2(4000) := p_string;
1277 l_curr_pos NUMBER; -- the position index of the operator string
1278 l_eq_pos NUMBER;
1279 l_not_pos NUMBER;
1280 l_and_pos NUMBER;
1281 i NUMBER := 1;
1282 BEGIN
1283 LOOP
1284 l_and_pos := INSTR (UPPER (l_str), ' AND ');
1285 -- handle condition where no more AND's are
1286 -- left -- usually if only one condition or
1287 -- the last condition in the WHERE clause.
1288 IF l_and_pos = 0 THEN
1289 l_new_str := l_str;
1290 ELSE
1291 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
1292 END IF;
1293
1294 --
1295 -- The operator should also be passed
1296 -- back to the calling program.
1297 l_eq_pos := INSTR (l_new_str, '=');
1298 l_not_pos := INSTR (l_new_str, '<>');
1299 --
1300 -----------------------------------
1301 -- operator equal not equal
1302 -- error 0 0
1303 -- = 1 0
1304 -- <> 0 1
1305 -- = 1 2
1306 -- <> 2 1
1307 -----------------------------------
1308 IF l_eq_pos = 0 AND l_not_pos = 0 THEN
1309 -- Could not find either an = or an <>
1310 -- operator.
1311 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1312 FND_MESSAGE.set_name('OZF', 'OZF_UTIL_NO_WHERE_OPERATOR');
1313 FND_MSG_PUB.add;
1314 RAISE FND_API.g_exc_unexpected_error;
1315 END IF;
1316 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
1317 l_curr_pos := l_eq_pos;
1318 x_col_val_tbl(i).col_op := '=';
1319 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
1320 l_curr_pos := l_not_pos;
1321 x_col_val_tbl(i).col_op := '<>';
1322 ELSIF l_eq_pos < l_not_pos THEN
1323 l_curr_pos := l_eq_pos;
1324 x_col_val_tbl(i).col_op := '=';
1325 ELSE
1326 l_curr_pos := l_not_pos;
1327 x_col_val_tbl(i).col_op := '<>';
1328 END IF;
1329
1330 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
1331 -- Add 2 to the current position for '<>'.
1332 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
1333 --
1334 -- Remove the single quotes from the begin and end of the string value;
1335 -- no action if a numeric value.
1336 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
1337 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
1338 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value, 1, LENGTH(x_col_val_tbl(i).col_value) - 1);
1339 END IF;
1340
1341 IF l_and_pos = 0 THEN
1342 EXIT; -- no more to parse
1343 END IF;
1344
1345 l_str := SUBSTR (l_str, l_and_pos + 4);
1346 i := i + 1;
1347 END LOOP;
1348 END bind_parse;
1349
1350
1351 ---------------------------------------------------------------------
1352 -- FUNCTION
1353 -- get_lookup_meaning
1354 -- USAGE
1355 -- Example:
1356 -- SELECT OZF_Utility_PVT.get_lookup_meaning ('AMS_CAMPAIGN_STATUS', status_code)
1357 -- FROM ams_campaigns_vl;
1358 -- HISTORY
1359 -- 28-Apr-2000 choang Created.
1360 ---------------------------------------------------------------------
1361 FUNCTION get_lookup_meaning (
1362 p_lookup_type IN VARCHAR2,
1363 p_lookup_code IN VARCHAR2
1364 )
1365 RETURN VARCHAR2
1366 IS
1367 l_meaning VARCHAR2(80);
1368
1369 CURSOR c_meaning IS
1370 SELECT meaning
1371 FROM ozf_lookups
1372 WHERE lookup_type = UPPER (p_lookup_type)
1373 AND lookup_code = UPPER (p_lookup_code);
1374 BEGIN
1375 OPEN c_meaning;
1376 FETCH c_meaning INTO l_meaning;
1377 CLOSE c_meaning;
1378
1379 RETURN l_meaning;
1380 END get_lookup_meaning;
1381
1382
1383 ---------------------------------------------------------------------
1384 -- FUNCTION
1385 -- get_resource_name
1386 -- USAGE
1387 -- Example:
1388 -- SELECT OZF_Utility_PVT.get_resource_name (owner_user_id)
1389 -- FROM ams_campaigns_vl
1390 -- HISTORY
1391 -- 28-Apr-2000 choang Created.
1392 ---------------------------------------------------------------------
1393 FUNCTION get_resource_name (
1394 p_resource_id IN VARCHAR2
1395 )
1396 RETURN VARCHAR2
1397 IS
1398 l_resource_name VARCHAR2(240);
1399
1400 CURSOR c_resource_name IS
1401 SELECT full_name
1402 FROM ams_jtf_rs_emp_v
1403 WHERE resource_id = p_resource_id;
1404 BEGIN
1405 IF p_resource_id IS NULL THEN
1406 RETURN NULL;
1407 END IF;
1408
1409 OPEN c_resource_name;
1410 FETCH c_resource_name INTO l_resource_name;
1411 CLOSE c_resource_name;
1412
1413 RETURN l_resource_name;
1414 END get_resource_name;
1415
1416
1417 -----------------------------------------------------------------------
1418 -- FUNCTION
1419 -- is_in_my_division
1420 --
1421 -- HISTORY
1422 -- 07/28/2000 holiu Created.
1423 -----------------------------------------------------------------------
1424 FUNCTION is_in_my_division(
1425 p_object_type IN VARCHAR2,
1426 p_object_id IN NUMBER,
1427 p_country_id IN NUMBER
1428 )
1429 RETURN VARCHAR2
1430 IS
1431
1432 l_area2 VARCHAR2(30);
1433 l_obj_area2 VARCHAR2(30);
1434
1435 CURSOR c_area2 IS
1436 SELECT area2_code
1437 FROM jtf_loc_hierarchies_vl
1438 WHERE location_hierarchy_id = p_country_id;
1439
1440 CURSOR c_camp_area2 IS
1441 SELECT B.area2_code
1442 FROM ams_campaigns_vl A, jtf_loc_hierarchies_vl B
1443 WHERE A.campaign_id = p_object_id
1444 AND A.city_id = B.location_hierarchy_id;
1445
1446 BEGIN
1447
1448 OPEN c_area2;
1449 FETCH c_area2 INTO l_area2;
1450 CLOSE c_area2;
1451
1452 IF l_area2 IS NULL THEN
1453 RETURN 'N';
1454 END IF;
1455
1456 IF p_object_type = 'CAMP' THEN
1457 OPEN c_camp_area2;
1458 FETCH c_camp_area2 INTO l_obj_area2;
1459 CLOSE c_camp_area2;
1460 END IF;
1461
1462 IF l_area2 = l_obj_area2 THEN
1463 RETURN 'Y';
1464 ELSE
1465 RETURN 'N';
1466 END IF;
1467
1468 END is_in_my_division;
1469
1470
1471 ---------------------------------------------------------------------
1472 -- FUNCTION
1473 -- get_product_name
1474 -- HISTORY
1475 -- 14-JUN-2000 holiu Create.
1476 -- 10-Apr-2002 choang applied changes requested by skarumur: removed
1477 -- l_product_name because it was declared as a
1478 -- varchar2(76) - too small for product name.
1479 -- 13-Jun-2005 Ribha Use p_org_id = -1 for bypassing access check
1480 -- (organization identifier)
1481 -- 17-May-2006 asylvia Include Return NULL for the function fixed bug 5226685
1482 ---------------------------------------------------------------------
1483 FUNCTION get_product_name(
1484 p_prod_level IN VARCHAR2,
1485 p_prod_id IN NUMBER,
1486 p_org_id IN NUMBER := NULL
1487 )
1488 RETURN VARCHAR2
1489 IS
1490 CURSOR c_product_name IS
1491 SELECT padded_concatenated_segments
1492 FROM mtl_system_items_kfv
1493 WHERE inventory_item_id = p_prod_id
1494 AND organization_id = p_org_id;
1495
1496 CURSOR c_product_name_all IS
1497 SELECT DISTINCT padded_concatenated_segments
1498 FROM mtl_system_items_kfv
1499 WHERE inventory_item_id = p_prod_id;
1500
1501 CURSOR c_category_name IS
1502 SELECT description
1503 FROM mtl_categories_v
1504 WHERE category_id = p_prod_id;
1505
1506 l_product_name c_product_name%ROWTYPE;
1507 l_product_name_all c_product_name_all%ROWTYPE;
1508 l_category_name c_category_name%ROWTYPE;
1509 BEGIN
1510 IF p_prod_id IS NULL THEN
1511 RETURN NULL;
1512 END IF;
1513
1514 IF p_prod_level IN ('PRICING_ATTRIBUTE1', 'PRODUCT') THEN
1515 IF p_org_id = -1 THEN
1516 OPEN c_product_name_all;
1517 FETCH c_product_name_all INTO l_product_name_all;
1518 CLOSE c_product_name_all;
1519 RETURN l_product_name_all.padded_concatenated_segments;
1520 ELSE
1521 OPEN c_product_name;
1522 FETCH c_product_name INTO l_product_name;
1523 CLOSE c_product_name;
1524 RETURN l_product_name.padded_concatenated_segments;
1525 END IF;
1526 ELSIF p_prod_level IN ('PRICING_ATTRIBUTE2', 'CATEGORY','FAMILY') THEN
1527 OPEN c_category_name;
1528 FETCH c_category_name INTO l_category_name;
1529 CLOSE c_category_name;
1530 RETURN l_category_name.description;
1531 END IF;
1532 RETURN NULL;
1533 END get_product_name;
1534
1535
1536
1537 ---------------------------------------------------------------------
1538 -- FUNCTION
1539 -- get_price_list_name
1540 -- HISTORY
1541 -- 14-JUN-2000 holiu Create.
1542 ---------------------------------------------------------------------
1543 FUNCTION get_price_list_name(
1544 p_price_list_line_id IN NUMBER
1545 )
1546 RETURN VARCHAR2
1547 IS
1548 l_name VARCHAR2(240);
1549
1550 CURSOR c_price_list_name IS
1551 SELECT qlh.name
1552 FROM qp_list_headers_vl qlh, qp_list_lines qll
1553 WHERE qll.list_header_id = qlh.list_header_id
1554 AND qll.list_line_id = p_price_list_line_id;
1555 BEGIN
1556 IF p_price_list_line_id IS NULL THEN
1557 RETURN NULL;
1558 END IF;
1559
1560 OPEN c_price_list_name;
1561 FETCH c_price_list_name INTO l_name;
1562 CLOSE c_price_list_name;
1563
1564 RETURN l_name;
1565 END get_price_list_name;
1566
1567
1568 ---------------------------------------------------------------------
1569 -- FUNCTION
1570 -- get_uom_name
1571 -- HISTORY
1572 -- 14-JUN-2000 holiu Create.
1573 ---------------------------------------------------------------------
1574 FUNCTION get_uom_name(
1575 p_uom_code IN VARCHAR2
1576 )
1577 RETURN VARCHAR2
1578 IS
1579 l_name VARCHAR2(25);
1580
1581 CURSOR c_uom IS
1582 SELECT unit_of_measure
1583 FROM mtl_units_of_measure
1584 WHERE uom_code = p_uom_code;
1585 BEGIN
1586 IF p_uom_code IS NULL THEN
1587 RETURN NULL;
1588 END IF;
1589
1590 OPEN c_uom;
1591 FETCH c_uom INTO l_name;
1592 CLOSE c_uom;
1593
1594 RETURN l_name;
1595 END get_uom_name;
1596
1597
1598 ---------------------------------------------------------------------
1599 -- FUNCTION
1600 -- get_qp_lookup_meaning
1601 -- DESCRIPTION
1602 -- Get the meaning of the given lookup code in qp_lookups.
1603 ---------------------------------------------------------------------
1604 FUNCTION get_qp_lookup_meaning(
1605 p_lookup_type IN VARCHAR2,
1606 p_lookup_code IN VARCHAR2
1607 )
1608 RETURN VARCHAR2
1609 IS
1610 l_meaning VARCHAR2(80);
1611
1612 CURSOR c_meaning IS
1613 SELECT meaning
1614 FROM qp_lookups
1615 WHERE lookup_type = UPPER(p_lookup_type)
1616 AND lookup_code = UPPER(p_lookup_code);
1617 BEGIN
1618 IF p_lookup_type IS NULL OR p_lookup_code IS NULL THEN
1619 RETURN NULL;
1620 END IF;
1621
1622 OPEN c_meaning;
1623 FETCH c_meaning INTO l_meaning;
1624 CLOSE c_meaning;
1625
1626 RETURN l_meaning;
1627 END get_qp_lookup_meaning;
1628
1629 ---------------------------------------------------------------------
1630 -- FUNCTION
1631 -- get_resource_id
1632 -- DESCRIPTION
1633 -- Returns resource_id from the JTF Resource module given
1634 -- an AOL user_id.
1635 ---------------------------------------------------------------------
1636 FUNCTION get_resource_id (
1637 p_user_id IN NUMBER
1638 )
1639 RETURN NUMBER
1640 IS
1641 l_resource_id NUMBER;
1642
1643 CURSOR c_resource IS
1644 SELECT resource_id
1645 FROM ams_jtf_rs_emp_v
1646 WHERE user_id = p_user_id;
1647 BEGIN
1648 OPEN c_resource;
1649 FETCH c_resource INTO l_resource_id;
1650 IF c_resource%NOTFOUND THEN
1651 l_resource_id := -1;
1652 -- Adding an error message will cause the function
1653 -- to violate the WNDS pragma, preventing it from
1654 -- being able to be called from a SQL statement.
1655 END IF;
1656 CLOSE c_resource;
1657
1658 RETURN l_resource_id;
1659 END get_resource_id;
1660
1661 ---------------------------------------------------------------------
1662 -- FUNCTION
1663 -- Write_Conc_Log
1664 -- DESCRIPTION
1665 -- Writes the log for Concurrent programs
1666 -- History
1667 -- 07-Aug-2000 PTENDULK Created
1668 -- 08-Aug-2000 PTENDULK Write the output in to log instead of output
1669 -- NOTE
1670 -- If the parameter p_text is passed then the value sent will be printed
1671 -- as log else the messages in the stack are printed.
1672 ---------------------------------------------------------------------
1673 PROCEDURE Write_Conc_Log
1674 ( p_text IN VARCHAR2 := NULL)
1675 IS
1676 l_count NUMBER;
1677 l_msg VARCHAR2(2000);
1678 l_cnt NUMBER ;
1679 BEGIN
1680 IF p_text IS NULL THEN
1681 l_count := FND_MSG_PUB.count_msg;
1682 FOR l_cnt IN 1 .. l_count
1683 LOOP
1684 l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
1685 FND_FILE.PUT_LINE(FND_FILE.LOG, '(' || l_cnt || ') ' || l_msg);
1686 END LOOP;
1687 ELSE
1688 FND_FILE.PUT_LINE(FND_FILE.LOG, p_text );
1689 END IF;
1690
1691 END Write_Conc_Log ;
1692
1693
1694 -----------------------------------------------------------------------
1695 -- FUNCTION
1696 -- get_system_status_type
1697 --
1698 -- HISTORY
1699 -- 14-SEP-2000 holiu Create.
1700 -- 29-May-2001 ptendulk Added system status for Schedule
1701 -- 20-May-2001 ptendulk Added system status for programs.
1702 -- 13-Jun-2001 ptendulk Added EONE
1703 -----------------------------------------------------------------------
1704 FUNCTION get_system_status_type(
1705 p_object IN VARCHAR2
1706 )
1707 RETURN VARCHAR2
1708 IS
1709 BEGIN
1710
1711 IF p_object = 'CAMP' THEN
1712 RETURN 'AMS_CAMPAIGN_STATUS';
1713 ELSIF p_object IN ('EVEH', 'EVEO','EONE', 'EVET') THEN
1714 RETURN 'AMS_EVENT_STATUS';
1715 ELSIF p_object = 'DELV' THEN
1716 RETURN 'AMS_DELIV_STATUS';
1717 ELSIF p_object = 'CSCH' THEN
1718 RETURN 'AMS_CAMPAIGN_SCHEDULE_STATUS' ;
1719 ELSIF p_object = 'RCAM' THEN
1720 RETURN 'AMS_PROGRAM_STATUS' ;
1721 ELSIF p_object = 'OFFR' THEN
1722 RETURN 'OZF_OFFER_STATUS' ;
1723 ELSIF p_object = 'PRIC' THEN
1724 RETURN 'OZF_PRICELIST_STATUS' ;
1725 ELSE
1726 RETURN NULL;
1727 END IF;
1728
1729 END get_system_status_type;
1730
1731
1732 -----------------------------------------------------------------------
1733 -- FUNCTION
1734 -- get_system_status_code
1735 --
1736 -- HISTORY
1737 -- 14-SEP-2000 holiu Create.
1738 -----------------------------------------------------------------------
1739 FUNCTION get_system_status_code(
1740 p_user_status_id IN NUMBER
1741 )
1742 RETURN VARCHAR2
1743 IS
1744
1745 l_status_code VARCHAR2(30);
1746
1747 CURSOR c_status_code IS
1748 SELECT system_status_code
1749 FROM ams_user_statuses_vl
1750 WHERE user_status_id = p_user_status_id
1751 AND enabled_flag = 'Y';
1752
1753 BEGIN
1754
1755 OPEN c_status_code;
1756 FETCH c_status_code INTO l_status_code;
1757 CLOSE c_status_code;
1758
1759 RETURN l_status_code;
1760
1761 END get_system_status_code;
1762
1763
1764 -----------------------------------------------------------------------
1765 -- FUNCTION
1766 -- get_default_user_status
1767 --
1768 -- HISTORY
1769 -- 14-SEP-2000 holiu Create.
1770 -----------------------------------------------------------------------
1771 FUNCTION get_default_user_status(
1772 p_status_type IN VARCHAR2,
1773 p_status_code IN VARCHAR2
1774 )
1775 RETURN VARCHAR2
1776 IS
1777
1778 l_status_id NUMBER;
1779
1780 CURSOR c_status_id IS
1781 SELECT user_status_id
1782 FROM ams_user_statuses_vl
1783 WHERE system_status_type = p_status_type
1784 AND system_status_code = p_status_code
1785 AND default_flag = 'Y'
1786 AND enabled_flag = 'Y';
1787
1788 BEGIN
1789
1790 OPEN c_status_id;
1791 FETCH c_status_id INTO l_status_id;
1792 CLOSE c_status_id;
1793
1794 RETURN l_status_id;
1795
1796 END get_default_user_status;
1797
1798
1799 -----------------------------------------------------------------------
1800 -- PROCEDURE
1801 -- check_status_change
1802 --
1803 -- HISTORY
1804 -- 14-SEP-2000 holiu Create.
1805 -----------------------------------------------------------------------
1806 PROCEDURE check_status_change(
1807 p_object_type IN VARCHAR2,
1808 p_object_id IN NUMBER,
1809 p_old_status_id IN NUMBER,
1810 p_new_status_id IN NUMBER,
1811 x_approval_type OUT NOCOPY VARCHAR2,
1812 x_return_status OUT NOCOPY VARCHAR2
1813 )
1814 IS
1815
1816 l_theme_flag VARCHAR2(1);
1817 l_budget_flag VARCHAR2(1);
1818 l_status_type VARCHAR2(30);
1819 l_old_status_code VARCHAR2(30);
1820 l_new_status_code VARCHAR2(30);
1821
1822 CURSOR c_approval_flag IS
1823 SELECT theme_approval_flag, budget_approval_flag
1824 FROM ams_status_order_rules
1825 WHERE system_status_type = l_status_type
1826 AND current_status_code = l_old_status_code
1827 AND next_status_code = l_new_status_code;
1828
1829 BEGIN
1830
1831 x_return_status := FND_API.g_ret_sts_success;
1832 x_approval_type := NULL;
1833
1834 l_status_type := get_system_status_type(p_object_type);
1835 l_old_status_code := get_system_status_code(p_old_status_id);
1836 l_new_status_code := get_system_status_code(p_new_status_id);
1837
1838 IF l_old_status_code = l_new_status_code THEN
1839 RETURN;
1840 END IF;
1841
1842 OPEN c_approval_flag;
1843 FETCH c_approval_flag INTO l_theme_flag, l_budget_flag;
1844 IF c_approval_flag%NOTFOUND THEN
1845 x_return_status := FND_API.g_ret_sts_error;
1846 OZF_Utility_PVT.error_message('OZF_CAMP_BAD_STATUS_CHANGE');
1847 END IF;
1848 CLOSE c_approval_flag;
1849
1850 IF l_budget_flag = 'Y' THEN
1851 IF AMS_ObjectAttribute_PVT.check_object_attribute(
1852 p_object_type, p_object_id, 'BAPL') = FND_API.g_true
1853 THEN
1854 x_approval_type := 'BUDGET';
1855 END IF;
1856 ELSIF l_theme_flag = 'Y' THEN
1857 IF AMS_ObjectAttribute_PVT.check_object_attribute(
1858 p_object_type, p_object_id, 'TAPL') = FND_API.g_true
1859 THEN
1860 x_approval_type := 'THEME';
1861 END IF;
1862 END IF;
1863
1864 END check_status_change;
1865
1866
1867 --========================================================================
1868 -- Function
1869 -- Approval_required_flag
1870 -- Purpose
1871 -- This function will return the approval required flag for the
1872 -- given custom setup.
1873 --
1874 -- History
1875 -- 16-Jun-2001 ptendulk Created
1876 -- 19-Jun-2001 ptendulk Check specific attribute (bug in last code)
1877 --========================================================================
1878 FUNCTION Approval_Required_Flag( p_custom_setup_id IN NUMBER ,
1879 p_approval_type IN VARCHAR2)
1880 RETURN VARCHAR2 IS
1881 CURSOR c_custom_attr IS
1882 SELECT attr_available_flag
1883 FROM ams_custom_setup_attr
1884 WHERE custom_setup_id = p_custom_setup_id
1885 -- Following line is added by ptendulk on 19-Jun-2001
1886 AND object_attribute = p_approval_type ;
1887
1888 l_flag VARCHAR2(1) ;
1889 BEGIN
1890
1891 OPEN c_custom_attr;
1892 FETCH c_custom_attr INTO l_flag ;
1893 CLOSE c_custom_attr ;
1894 RETURN l_flag ;
1895
1896 END Approval_Required_Flag;
1897
1898 -----------------------------------------------------------------------
1899 -- PROCEDURE
1900 -- check_status_change
1901 --
1902 -- PURPOSE
1903 -- This procedure is created to override the obsoleted check_status_change
1904 -- procedure as object_attribute table is obsoleted now.
1905 --
1906 -- HISTORY
1907 -- 16-Jun-2001 ptendulk Created
1908 -- 02-Jul-2002 musman Added changes for deliverable approvals
1909 -----------------------------------------------------------------------
1910 PROCEDURE check_new_status_change(
1911 p_object_type IN VARCHAR2,
1912 p_object_id IN NUMBER,
1913 p_old_status_id IN NUMBER,
1914 p_new_status_id IN NUMBER,
1915 p_custom_setup_id IN NUMBER,
1916 x_approval_type OUT NOCOPY VARCHAR2,
1917 x_return_status OUT NOCOPY VARCHAR2
1918 )
1919 IS
1920
1921 l_theme_flag VARCHAR2(1);
1922 l_budget_flag VARCHAR2(1);
1923 l_status_type VARCHAR2(30);
1924 l_old_status_code VARCHAR2(30);
1925 l_new_status_code VARCHAR2(30);
1926
1927 l_custom_setup_attr VARCHAR2(4) := 'TAPL';
1928
1929 CURSOR c_approval_flag IS
1930 SELECT theme_approval_flag, budget_approval_flag
1931 FROM ams_status_order_rules
1932 WHERE system_status_type = l_status_type
1933 AND current_status_code = l_old_status_code
1934 AND next_status_code = l_new_status_code;
1935
1936 BEGIN
1937
1938 x_return_status := FND_API.g_ret_sts_success;
1939 x_approval_type := NULL;
1940
1941 l_status_type := get_system_status_type(p_object_type);
1942 l_old_status_code := get_system_status_code(p_old_status_id);
1943 l_new_status_code := get_system_status_code(p_new_status_id);
1944
1945 IF l_old_status_code = l_new_status_code THEN
1946 RETURN;
1947 END IF;
1948
1949 OPEN c_approval_flag;
1950 FETCH c_approval_flag INTO l_theme_flag, l_budget_flag;
1951 IF c_approval_flag%NOTFOUND THEN
1952 x_return_status := FND_API.g_ret_sts_error;
1953 OZF_Utility_PVT.error_message('OZF_CAMP_BAD_STATUS_CHANGE');
1954 END IF;
1955 CLOSE c_approval_flag;
1956
1957 IF l_budget_flag = 'Y' THEN
1958 IF Approval_Required_Flag(p_custom_setup_id, 'BAPL') = 'Y'
1959 THEN
1960 x_approval_type := 'BUDGET';
1961 END IF;
1962 ELSIF l_theme_flag = 'Y' THEN
1963
1964 /* since Deliv has only concept approval */
1965 IF p_object_type = 'DELV'
1966 THEN
1967 l_custom_setup_attr := 'CAPL';
1968 END IF;
1969 IF Approval_Required_Flag(p_custom_setup_id, l_custom_setup_attr) = 'Y'
1970 THEN
1971 x_approval_type := 'THEME';
1972 END IF;
1973 END IF;
1974
1975 END check_new_status_change;
1976
1977 ---------------------------------------------------------------------
1978 -- PROCEDURE
1979 -- Convert_Currency
1980 -- NOTE
1981 -- HISTORY
1982 -- 01-Sep-2000 slkrishn Created.
1983 -- 12-SEP-2000 mpande Updated
1984 -- 02/23/2001 mpande Updated for getting org id query
1985 -- 03/27/2001 MPANDE MOved from OZF to OZF
1986 ---------------------------------------------------------------------
1987 PROCEDURE convert_currency(
1988 p_set_of_books_id IN NUMBER
1989 ,p_from_currency IN VARCHAR2
1990 ,p_conversion_date IN DATE
1991 ,p_conversion_type IN VARCHAR2
1992 ,p_conversion_rate IN NUMBER
1993 ,p_amount IN NUMBER
1994 ,x_return_status OUT NOCOPY VARCHAR2
1995 ,x_acc_amount OUT NOCOPY NUMBER
1996 ,x_rate OUT NOCOPY NUMBER)
1997 IS
1998 l_api_name VARCHAR2(30) := 'Convert Currency';
1999 l_to_currenvy VARCHAR2(30);
2000 l_max_roll_days NUMBER := -1;
2001 l_user_rate NUMBER := NVL(p_conversion_rate, 1);
2002 l_numerator NUMBER;
2003 l_denominator NUMBER;
2004 l_org_id NUMBER;
2005 l_sob NUMBER;
2006 l_to_currency VARCHAR2(30);
2007 l_conv_date DATE;
2008
2009 --
2010 -- get functional currency
2011 -- gs.mrc_sob_type_code,
2012
2013 /* CURSOR c_get_gl_info(
2014 p_org_id IN NUMBER)
2015 IS
2016 SELECT gs.set_of_books_id
2017 ,gs.currency_code
2018 FROM gl_sets_of_books gs
2019 ,org_organization_definitions org
2020 WHERE gs.mrc_sob_type_code = 'P'
2021 AND org.set_of_books_id = gs.set_of_books_id
2022 AND org.operating_unit = p_org_id;
2023 */
2024 --02/23/2001 mpande changed as per Hornet requirements
2025 CURSOR c_get_gl_info( p_org_id IN NUMBER)
2026 IS
2027 SELECT gs.set_of_books_id
2028 , gs.currency_code
2029 FROM gl_sets_of_books gs
2030 , ozf_sys_parameters_all org
2031 WHERE org.set_of_books_id = gs.set_of_books_id
2032 AND NVL(org.org_id, -99) = NVL(p_org_id, -99);
2033
2034 BEGIN
2035
2036 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2037
2038 IF l_org_id IS NULL THEN
2039 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2040 fnd_message.set_name('OZF', 'OZF_ORG_ID_NOTFOUND');
2041 fnd_msg_pub.add;
2042 END IF;
2043 RAISE fnd_api.g_exc_error;
2044 END IF;
2045
2046 OPEN c_get_gl_info(l_org_id);
2047 FETCH c_get_gl_info INTO l_sob, l_to_currency;
2048
2049 IF c_get_gl_info%NOTFOUND THEN
2050 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2051 fnd_message.set_name('OZF', 'OZF_GL_SOB_NOTFOUND');
2052 fnd_msg_pub.add;
2053 END IF;
2054
2055 RAISE fnd_api.g_exc_error;
2056 END IF;
2057 CLOSE c_get_gl_info;
2058
2059 --ER 8399134
2060 IF p_conversion_date IS NULL THEN
2061 l_conv_date := SYSDATE;
2062 ELSE
2063 l_conv_date := p_conversion_date;
2064 END IF;
2065
2066 --
2067 gl_currency_api.convert_closest_amount(
2068 x_from_currency => p_from_currency
2069 ,x_to_currency => l_to_currency
2070 ,x_conversion_date => l_conv_date
2071 ,x_conversion_type => p_conversion_type
2072 ,x_user_rate => l_user_rate
2073 ,x_amount => p_amount
2074 ,x_max_roll_days => l_max_roll_days
2075 ,x_converted_amount => x_acc_amount
2076 ,x_denominator => l_denominator
2077 ,x_numerator => l_numerator
2078 ,x_rate => x_rate);
2079 --
2080
2081 EXCEPTION
2082 WHEN fnd_api.g_exc_error THEN
2083 x_return_status := fnd_api.g_ret_sts_error;
2084 WHEN gl_currency_api.no_rate THEN
2085 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2086 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2087 fnd_msg_pub.add;
2088 END IF;
2089
2090 x_return_status := fnd_api.g_ret_sts_error;
2091 WHEN gl_currency_api.invalid_currency THEN
2092 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2093 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2094 fnd_msg_pub.add;
2095 END IF;
2096
2097 x_return_status := fnd_api.g_ret_sts_error;
2098 WHEN OTHERS THEN
2099 RAISE;
2100 x_return_status := fnd_api.g_ret_sts_unexp_error;
2101 END convert_currency;
2102
2103 ---------------------------------------------------------------------
2104 -- PROCEDURE
2105 -- get_code_combinations
2106 --
2107 -- PURPOSE
2108 -- get code_combination concacnenated segments and ids
2109 -- 20-Sep-2000 slkrishn Created
2110 -- 03/27/2001 MPANDE MOved from OZF to OZF
2111 ---------------------------------------------------------------------
2112 FUNCTION get_code_combinations(
2113 p_code_combination_id IN NUMBER
2114 ,p_chart_of_accounts_id IN NUMBER)
2115 RETURN VARCHAR2
2116 IS
2117 l_api_name VARCHAR2(30) := 'Get_Code_Combinations';
2118 l_result BOOLEAN;
2119 l_app_name VARCHAR2(30) := 'SQLGL';
2120 l_flex_code VARCHAR2(30) := 'GL#';
2121 BEGIN
2122 l_result := fnd_flex_keyval.validate_ccid(
2123 appl_short_name => l_app_name
2124 ,key_flex_code => l_flex_code
2125 ,structure_number => p_chart_of_accounts_id
2126 ,combination_id => p_code_combination_id);
2127
2128 IF l_result THEN
2129 RETURN fnd_flex_keyval.concatenated_descriptions;
2130 ELSE
2131 RETURN '';
2132 END IF;
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 RAISE;
2136 END get_code_combinations;
2137 ---------------------------------------------------------------------
2138 -- PROCEDURE
2139 -- Convert_functional_Curr
2140 -- NOTE
2141 -- This procedures takes in amount and converts it to the functional currency
2142 -- and returns the converted amount,exchange_rate,set_of_book_id,
2143 -- f-nctional_currency_code,exchange_rate_date
2144
2145 -- HISTORY
2146 -- 20-Jul-2000 mpande Created.
2147 -- 02/23/2001 MPAnde Updated for getting org id query
2148 -- 03/27/2001 MPANDE MOved from OZF to OZF
2149 -- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) - pass in org_id, default to null
2150 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
2151 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
2152 -- x_Set_of_books_id OUT NUMBER,
2153 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R'
2154 -- We only do it for primary ('P' because we donot supprot MRC)
2155 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
2156 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,
2157 -- comes from a OZF profile or what ever is passed
2158 -- x_EXCHANGE_RATE_DATE OUT DATE,
2159 -- could come from a OZF profile but right now is sysdate
2160 -- x_EXCHANGE_RATE OUT VARCHAR2,
2161 -- x_return_status OUT VARCHAR2
2162 -- The following is the rule in the GL API
2163 -- If x_conversion_type = 'User', and the relationship between the
2164 -- two currencies is not fixed, x_user_rate will be used as the
2165 -- conversion rate to convert the amount
2166 -- else no_user_rate is required
2167
2168 ---------------------------------------------------------------------
2169
2170
2171 PROCEDURE calculate_functional_curr(
2172 p_from_amount IN NUMBER
2173 ,p_conv_date IN DATE DEFAULT SYSDATE
2174 ,p_tc_currency_code IN VARCHAR2
2175 ,p_org_id IN NUMBER DEFAULT NULL
2176 ,x_to_amount OUT NOCOPY NUMBER
2177 ,x_set_of_books_id OUT NOCOPY NUMBER
2178 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
2179 ,x_fc_currency_code OUT NOCOPY VARCHAR2
2180 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
2181 ,x_exchange_rate IN OUT NOCOPY NUMBER
2182 ,x_return_status OUT NOCOPY VARCHAR2)
2183 IS
2184 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
2185 l_user_rate CONSTANT NUMBER := 1;
2186 -- Currenty not used. -- this should be a profile
2187 l_max_roll_days CONSTANT NUMBER := -1;
2188 -- Negative so API rolls back to find the last conversion rate.
2189 -- this should be a profile
2190 l_denominator NUMBER; -- Not used in Marketing.
2191 l_numerator NUMBER; -- Not used in Marketing.
2192 l_conversion_type VARCHAR2(30);
2193 l_org_id NUMBER;
2194
2195 -- Cursor to get the primary set_of_books_id ,functional_currency_code
2196 -- changed the above query to look into operating unit and not organization_id
2197 --SEP12 mpande
2198 /*
2199 CURSOR c_get_gl_info(
2200 p_org_id IN NUMBER)
2201 IS
2202 SELECT gs.set_of_books_id
2203 ,gs.currency_code
2204 FROM gl_sets_of_books gs
2205 ,org_organization_definitions org
2206 WHERE org.set_of_books_id = gs.set_of_books_id
2207 AND org.operating_unit = p_org_id;
2208 */
2209 --02/23/2001 mpande changed as per Hornet requirements
2210 CURSOR c_get_gl_info(p_org_id IN NUMBER)
2211 IS
2212 SELECT gs.set_of_books_id
2213 , gs.currency_code
2214 FROM gl_sets_of_books gs
2215 , ozf_sys_parameters_all org
2216 WHERE org.set_of_books_id = gs.set_of_books_id
2217 AND NVL(org.org_id, -99) = NVL(p_org_id, -99);
2218
2219 BEGIN
2220 -- Initialize return status.
2221 x_return_status := fnd_api.g_ret_sts_success;
2222
2223 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2224 -- Mumu Pande 09/20/2000 Updated the following
2225 -- Get the currency conversion type from profile option
2226 IF x_exchange_rate_type IS NULL THEN
2227 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2228 ELSE
2229 l_conversion_type := x_exchange_rate_type;
2230 END IF;
2231
2232 IF l_conversion_type IS NULL THEN
2233 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2234 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2235 fnd_msg_pub.add;
2236 END IF;
2237
2238 x_return_status := fnd_api.g_ret_sts_error;
2239 RETURN;
2240 ELSE
2241 IF ozf_utility_pvt.check_fk_exists('GL_DAILY_CONVERSION_TYPES',
2242 'CONVERSION_TYPE'
2243 ,l_conversion_type) = fnd_api.g_false
2244 THEN
2245 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2246 fnd_message.set_name('OZF', 'OZF_WRONG_CONVERSION_TYPE');
2247 fnd_msg_pub.add;
2248 END IF;
2249 x_return_status := fnd_api.g_ret_sts_error;
2250 RETURN;
2251 END IF;
2252 END IF;
2253
2254 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2255 /* yzhao: 01/13/2003 fix bug BUG 2750841(same as 2741039) - use org_id if it is passed,
2256 otherwise get from login session */
2257 IF (p_org_id IS NOT NULL) THEN
2258 l_org_id := p_org_id;
2259 ELSE
2260
2261 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2262
2263 IF l_org_id IS NULL THEN
2264 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2265 fnd_message.set_name('OZF', 'OZF_ORG_ID_NOTFOUND');
2266 fnd_msg_pub.add;
2267 END IF;
2268
2269 RAISE fnd_api.g_exc_error;
2270 END IF;
2271
2272
2273 END IF;
2274
2275 IF (OZF_DEBUG_HIGH_ON) THEN
2276 ozf_utility_pvt.debug_message('debug: start ' || l_org_id);
2277 END IF;
2278
2279 x_mrc_sob_type_code := 'P';
2280 OPEN c_get_gl_info(l_org_id);
2281 FETCH c_get_gl_info INTO x_set_of_books_id, x_fc_currency_code;
2282
2283 IF c_get_gl_info%NOTFOUND THEN
2284 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2285 fnd_message.set_name('OZF', 'OZF_GL_SOB_NOTFOUND');
2286 fnd_msg_pub.add;
2287 END IF;
2288
2289 RAISE fnd_api.g_exc_error;
2290 END IF;
2291
2292 CLOSE c_get_gl_info;
2293 -- Call the proper GL API to convert the amount.
2294 gl_currency_api.convert_closest_amount(
2295 x_from_currency => p_tc_currency_code
2296 ,x_to_currency => x_fc_currency_code
2297 ,x_conversion_date => p_conv_date
2298 ,x_conversion_type => l_conversion_type
2299 ,x_user_rate => x_exchange_rate
2300 ,x_amount => p_from_amount
2301 ,x_max_roll_days => l_max_roll_days
2302 ,x_converted_amount => x_to_amount
2303 ,x_denominator => l_denominator
2304 ,x_numerator => l_numerator
2305 ,x_rate => x_exchange_rate);
2306
2307 x_exchange_rate_type := l_conversion_type;
2308 --
2309
2310 EXCEPTION
2311 WHEN fnd_api.g_exc_error THEN
2312 x_return_status := fnd_api.g_ret_sts_error;
2313 WHEN gl_currency_api.no_rate THEN
2314 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2315 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2316 fnd_msg_pub.add;
2317 END IF;
2318
2319 x_return_status := fnd_api.g_ret_sts_error;
2320 WHEN gl_currency_api.invalid_currency THEN
2321 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2322 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2323 fnd_msg_pub.add;
2324 END IF;
2325
2326 x_return_status := fnd_api.g_ret_sts_error;
2327 WHEN OTHERS THEN
2328 x_return_status := fnd_api.g_ret_sts_unexp_error;
2329
2330 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2331 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_functional_curency');
2332 END IF;
2333 END calculate_functional_curr;
2334
2335
2336 --======================================================================
2337 -- FUNCTION
2338 -- get_fund_ledger
2339
2340 -- PURPOSE
2341 -- The api return the budget's ledger in order:
2342 -- ledger on budget if specified - ledger on budget category if specified - budget creator default OU's ledger
2343 -- used for deriving budget's functional currency
2344 -- HISTORY
2345 -- 30-Sep-2005 kdass Create.
2346 --======================================================================
2347
2348 PROCEDURE get_fund_ledger(
2349 p_fund_id IN NUMBER
2350 ,x_org_id OUT NOCOPY NUMBER
2351 ,x_ledger_id OUT NOCOPY NUMBER
2352 ,x_return_status OUT NOCOPY VARCHAR2
2353 )
2354 IS
2355 l_ledger_name VARCHAR2(30) := NULL;
2356
2357 CURSOR c_fund_ledger IS
2358 SELECT ledger_id
2359 FROM ozf_funds_all_b
2360 WHERE fund_id = p_fund_id;
2361
2362 BEGIN
2363 x_ledger_id := NULL;
2364
2365 OPEN c_fund_ledger;
2366 FETCH c_fund_ledger INTO x_ledger_id;
2367 CLOSE c_fund_ledger;
2368
2369 --07-APR-09 kdass bug 8402334 -- defaulted org_id to NULL for budget
2370 x_org_id := NULL;
2371 x_return_status := fnd_api.g_ret_sts_success;
2372 RETURN;
2373
2374 EXCEPTION
2375 WHEN OTHERS THEN
2376 x_return_status := fnd_api.g_ret_sts_unexp_error;
2377
2378 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2379 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2380 fnd_message.set_token('ROUTINE', 'OZF_UTLITY_PVT');
2381 fnd_message.set_token('ERRNO', sqlcode);
2382 fnd_message.set_token('REASON', sqlerrm);
2383 END IF;
2384
2385 END get_fund_ledger;
2386
2387
2388 --======================================================================
2389 -- FUNCTION
2390 -- get_object_org_ledger
2391
2392 -- PURPOSE
2393 -- The api return the object's org_id and ledger:
2394 -- offer: org_id for local offer
2395 -- user default org_id for global offer
2396 -- pric: org_id for price list
2397 -- budget: budget's org_id
2398 -- ledger on budget if specified - ledger on budget category if specified - budget creator default OU's ledger
2399 -- others: user default org_id for global offer
2400 -- HISTORY
2401 -- 13-Oct-2005 yzhao Create.
2402 --======================================================================
2403
2404 PROCEDURE get_object_org_ledger(
2405 p_object_id IN NUMBER
2406 ,p_object_type IN VARCHAR2
2407 ,x_org_id OUT NOCOPY NUMBER
2408 ,x_ledger_id OUT NOCOPY NUMBER
2409 ,x_return_status OUT NOCOPY VARCHAR2
2410 )
2411 IS
2412 l_ledger_name VARCHAR2(30);
2413
2414 -- Cursor to get the org_id for offer
2415 CURSOR c_offer_org_id (p_list_header_id IN NUMBER)IS
2416 SELECT org_id
2417 FROM ozf_offers
2418 WHERE qp_list_header_id = p_list_header_id;
2419
2420 -- Cursor to get the org_id for pric
2421 CURSOR c_pric_org_id (p_list_header_id IN NUMBER)IS
2422 SELECT orig_org_id
2423 FROM qp_list_headers_b
2424 WHERE list_header_id = p_list_header_id;
2425
2426 --Bugfix: 7555174, 7415805 (Start)
2427 CURSOR C_GET_OFFERS_BUDGET_ID(p_list_header_id IN NUMBER)IS
2428 SELECT budget_source_id, budget_source_type
2429 FROM ozf_act_budgets
2430 WHERE act_budget_used_by_id=p_list_header_id
2431 AND transfer_type = 'REQUEST'; --added for bug 13744476
2432
2433 CURSOR c_camp_budget_id(p_campaign_id IN NUMBER) IS
2434 SELECT budget_source_id
2435 FROM ozf_act_budgets
2436 WHERE act_budget_used_by_id = p_campaign_id;
2437
2438 l_budget_source_id NUMBER;
2439 l_budget_source_type VARCHAR2(30);
2440 --Bugfix: 7555174, 7415805 (End)
2441
2442 BEGIN
2443 x_org_id := NULL;
2444 x_ledger_id := NULL;
2445
2446 ozf_utility_pvt.write_conc_log ('get_object_org_ledger() p_object_id: ' || p_object_id);
2447 ozf_utility_pvt.write_conc_log ('get_object_org_ledger() p_object_type: ' || p_object_type);
2448 IF p_object_type = 'OFFR' THEN
2449 OPEN c_offer_org_id( p_object_id) ;
2450 FETCH c_offer_org_id INTO x_org_id ;
2451 CLOSE c_offer_org_id ;
2452 IF OZF_DEBUG_HIGH_ON THEN
2453 ozf_utility_pvt.debug_message ('get_object_org_ledger() org_id for offer =' || x_org_id);
2454 END IF;
2455 ozf_utility_pvt.write_conc_log ('get_object_org_ledger() org_id for offer: ' || x_org_id);
2456 ELSIF p_object_type = 'PRIC' THEN
2457 OPEN c_pric_org_id( p_object_id) ;
2458 FETCH c_pric_org_id INTO x_org_id ;
2459 CLOSE c_pric_org_id ;
2460 IF OZF_DEBUG_HIGH_ON THEN
2461 ozf_utility_pvt.debug_message ('get_object_org_ledger() org_id for pric =' || x_org_id);
2462 END IF;
2463 ELSIF p_object_type = 'FUND' THEN
2464 get_fund_ledger(p_fund_id => p_object_id
2465 , x_org_id => x_org_id
2466 , x_ledger_id => x_ledger_id
2467 , x_return_status => x_return_status);
2468 IF OZF_DEBUG_HIGH_ON THEN
2469 ozf_utility_pvt.debug_message ('get_object_org_ledger() org_id for fund =' || x_org_id
2470 || ' ledger_id=' || x_ledger_id);
2471 END IF;
2472 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2473 RAISE FND_API.g_exc_unexpected_error;
2474 END IF;
2475 END IF;
2476
2477 ozf_utility_pvt.write_conc_log ('get_object_org_ledger() x_org_id: ' || x_org_id);
2478 --Bugfix - 7555174 : Modified x_org_id check
2479 --Start (Bug-7555174)
2480 /*
2481 IF x_org_id IS NULL THEN
2482 x_org_id := NVL(mo_global.get_current_org_id, mo_utils.get_default_org_id);
2483 END IF;
2484 */
2485
2486 IF x_org_id IS NULL AND p_object_type = 'OFFR' THEN
2487 OPEN C_GET_OFFERS_BUDGET_ID(p_object_id);
2488 FETCH C_GET_OFFERS_BUDGET_ID INTO l_budget_source_id,l_budget_source_type;
2489 CLOSE C_GET_OFFERS_BUDGET_ID;
2490 ozf_utility_pvt.write_conc_log ('get_object_org_ledger() l_budget_source_id: '|| l_budget_source_id);
2491
2492 IF l_budget_source_type = 'CAMP' THEN
2493 OPEN c_camp_budget_id(l_budget_source_id);
2494 FETCH c_camp_budget_id INTO l_budget_source_id;
2495 CLOSE c_camp_budget_id;
2496 END IF;
2497
2498 ozf_utility_pvt.debug_message ('NP get_object_org_ledger() l_budget_source_id '|| l_budget_source_id);
2499 get_fund_ledger(p_fund_id => l_budget_source_id
2500 , x_org_id => x_org_id
2501 , x_ledger_id => x_ledger_id
2502 , x_return_status => x_return_status);
2503 END IF;
2504
2505 IF x_org_id IS NULL AND p_object_type <> 'OFFR' THEN
2506 x_org_id := NVL(mo_global.get_current_org_id, mo_utils.get_default_org_id);
2507 END IF;
2508 --End (Bug-7555174)
2509
2510 IF x_org_id IS NOT NULL AND
2511 x_ledger_id IS NULL THEN
2512 MO_UTILS.Get_Ledger_Info (
2513 p_operating_unit => x_org_id,
2514 p_ledger_id => x_ledger_id,
2515 p_ledger_name => l_ledger_name
2516 );
2517 END IF;
2518
2519 IF OZF_DEBUG_HIGH_ON THEN
2520 ozf_utility_pvt.debug_message ('get_object_org_ledger() final org_id =' || x_org_id
2521 || ' ledger_id=' || x_ledger_id);
2522 END IF;
2523 ozf_utility_pvt.write_conc_log ('get_object_org_ledger() final org_id: ' || x_org_id || ' ledger_id: ' || x_ledger_id);
2524 x_return_status := fnd_api.g_ret_sts_success;
2525
2526 EXCEPTION
2527 WHEN OTHERS THEN
2528 x_return_status := fnd_api.g_ret_sts_unexp_error;
2529
2530 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2531 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2532 fnd_message.set_token('ROUTINE', 'OZF_UTLITY_PVT');
2533 fnd_message.set_token('ERRNO', sqlcode);
2534 fnd_message.set_token('REASON', sqlerrm);
2535 END IF;
2536
2537 END get_object_org_ledger;
2538
2539
2540 ---------------------------------------------------------------------
2541 -- PROCEDURE
2542 -- calculate_functional_currency
2543 -- NOTE
2544 -- This procedures takes in amount and converts it to the functional currency
2545 -- and returns the converted amount,exchange_rate,ledger_id,
2546 -- functional_currency_code,exchange_rate_date
2547
2548 -- HISTORY
2549 -- 29-SEP-2005 kdass Created.
2550 ---------------------------------------------------------------------
2551 PROCEDURE calculate_functional_currency(
2552 p_from_amount IN NUMBER
2553 ,p_conv_date IN DATE DEFAULT SYSDATE
2554 ,p_tc_currency_code IN VARCHAR2
2555 ,p_ledger_id IN NUMBER DEFAULT NULL
2556 ,x_to_amount OUT NOCOPY NUMBER
2557 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
2558 ,x_fc_currency_code OUT NOCOPY VARCHAR2
2559 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
2560 ,x_exchange_rate IN OUT NOCOPY NUMBER
2561 ,x_return_status OUT NOCOPY VARCHAR2)
2562 IS
2563 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
2564 l_user_rate CONSTANT NUMBER := 1;
2565 -- Currenty not used. -- this should be a profile
2566 l_max_roll_days CONSTANT NUMBER := -1;
2567 -- Negative so API rolls back to find the last conversion rate.
2568 -- this should be a profile
2569 l_denominator NUMBER; -- Not used in Marketing.
2570 l_numerator NUMBER; -- Not used in Marketing.
2571 l_conversion_type VARCHAR2(30);
2572 l_conv_date DATE;
2573
2574 CURSOR c_get_gl_info(p_ledger_id IN NUMBER)
2575 IS
2576 SELECT currency_code
2577 FROM gl_ledgers_public_v
2578 WHERE ledger_id = p_ledger_id;
2579
2580 BEGIN
2581 -- Initialize return status.
2582 x_return_status := fnd_api.g_ret_sts_success;
2583
2584 IF x_exchange_rate_type IS NULL OR x_exchange_rate_type=FND_API.G_MISS_CHAR THEN
2585 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2586 ELSE
2587 l_conversion_type := x_exchange_rate_type;
2588 END IF;
2589
2590 --bug 8532055
2591 IF p_conv_date = FND_API.G_MISS_DATE OR p_conv_date IS NULL THEN
2592 l_conv_date := SYSDATE;
2593 ELSE
2594 l_conv_date := p_conv_date;
2595 END IF;
2596
2597 IF l_conversion_type IS NULL THEN
2598 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2599 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2600 fnd_msg_pub.add;
2601 END IF;
2602
2603 x_return_status := fnd_api.g_ret_sts_error;
2604 RETURN;
2605 ELSE
2606 IF ozf_utility_pvt.check_fk_exists('GL_DAILY_CONVERSION_TYPES',
2607 'CONVERSION_TYPE'
2608 ,l_conversion_type) = fnd_api.g_false
2609 THEN
2610 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2611 fnd_message.set_name('OZF', 'OZF_WRONG_CONVERSION_TYPE');
2612 fnd_msg_pub.add;
2613 END IF;
2614 x_return_status := fnd_api.g_ret_sts_error;
2615 RETURN;
2616 END IF;
2617 END IF;
2618
2619 x_mrc_sob_type_code := 'P';
2620
2621 OPEN c_get_gl_info(p_ledger_id);
2622 FETCH c_get_gl_info INTO x_fc_currency_code;
2623 CLOSE c_get_gl_info;
2624
2625 -- Call the proper GL API to convert the amount.
2626 gl_currency_api.convert_closest_amount(
2627 x_from_currency => p_tc_currency_code
2628 ,x_to_currency => x_fc_currency_code
2629 ,x_conversion_date => l_conv_date
2630 ,x_conversion_type => l_conversion_type
2631 ,x_user_rate => x_exchange_rate
2632 ,x_amount => p_from_amount
2633 ,x_max_roll_days => l_max_roll_days
2634 ,x_converted_amount => x_to_amount
2635 ,x_denominator => l_denominator
2636 ,x_numerator => l_numerator
2637 ,x_rate => x_exchange_rate);
2638
2639 x_exchange_rate_type := l_conversion_type;
2640
2641 EXCEPTION
2642 WHEN fnd_api.g_exc_error THEN
2643 x_return_status := fnd_api.g_ret_sts_error;
2644 WHEN gl_currency_api.no_rate THEN
2645 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2646 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2647 fnd_msg_pub.add;
2648 END IF;
2649
2650 x_return_status := fnd_api.g_ret_sts_error;
2651 WHEN gl_currency_api.invalid_currency THEN
2652 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2653 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2654 fnd_msg_pub.add;
2655 END IF;
2656
2657 x_return_status := fnd_api.g_ret_sts_error;
2658 WHEN OTHERS THEN
2659 x_return_status := fnd_api.g_ret_sts_unexp_error;
2660
2661 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2662 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_functional_curency');
2663 END IF;
2664 END calculate_functional_currency;
2665
2666 ---------------------------------------------------------------------
2667 -- PROCEDURE
2668 -- Convert_Currency
2669 -- NOTE
2670
2671 -- HISTORY
2672 -- 20-Jul-2000 mpande Created.
2673 --parameter p_from_currency IN VARCHAR2,
2674 -- p_to_currency IN VARCHAR2,
2675 -- p_conv_date IN DATE DEFAULT SYSDATE,
2676 -- p_from_amount IN NUMBER,
2677 -- x_to_amount OUT NUMBER
2678 -- If x_conversion_type = 'User', and the relationship between the
2679 -- two currencies is not fixed, x_user_rate will be used as the
2680 -- conversion rate to convert the amount
2681 -- else no_user_rate is required
2682 -- 02/23/2001 MPAnde Updated for getting org id query
2683 -- 03/27/2001 MPANDE MOved from OZF to OZF
2684 -- 04/07/2001 slkrishn Added p_conv_type and p_conv_rate with defaults
2685 ---------------------------------------------------------------------
2686
2687 PROCEDURE convert_currency(
2688 p_from_currency IN VARCHAR2
2689 ,p_to_currency IN VARCHAR2
2690 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
2691 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
2692 ,p_conv_date IN DATE DEFAULT SYSDATE
2693 ,p_from_amount IN NUMBER
2694 ,x_return_status OUT NOCOPY VARCHAR2
2695 ,x_to_amount OUT NOCOPY NUMBER
2696 ,x_rate OUT NOCOPY NUMBER)
2697 IS
2698 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
2699 l_user_rate CONSTANT NUMBER := 1;
2700 -- Currenty not used.
2701 -- this should be a profile
2702 l_max_roll_days CONSTANT NUMBER := -1;
2703 -- Negative so API rolls back to find the last conversion rate.
2704 -- this should be a profile
2705 l_denominator NUMBER; -- Not used in Marketing.
2706 l_numerator NUMBER; -- Not used in Marketing.
2707 l_conversion_type VARCHAR2(30); -- Curr conversion type; see API doc for details.
2708 l_conv_date DATE;
2709 BEGIN
2710 -- Initialize return status.
2711 x_return_status := fnd_api.g_ret_sts_success;
2712
2713 -- condition added to pass conversion types
2714 IF p_conv_type = FND_API.G_MISS_CHAR OR p_conv_type IS NULL THEN --Added for bug 7030415
2715 -- Get the currency conversion type from profile option
2716 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2717 -- Conversion type cannot be null in profile
2718 IF l_conversion_type IS NULL THEN
2719 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2720 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2721 fnd_msg_pub.add;
2722 END IF;
2723 x_return_status := fnd_api.g_ret_sts_error;
2724 RETURN;
2725 END IF;
2726 ELSE
2727 l_conversion_type := p_conv_type;
2728 END IF;
2729
2730 --bug 8532055
2731 IF p_conv_date = FND_API.G_MISS_DATE OR p_conv_date IS NULL THEN
2732 l_conv_date := SYSDATE;
2733 ELSE
2734 l_conv_date := p_conv_date;
2735 END IF;
2736
2737 -- Call the proper GL API to convert the amount.
2738 gl_currency_api.convert_closest_amount(
2739 x_from_currency => p_from_currency
2740 ,x_to_currency => p_to_currency
2741 ,x_conversion_date => l_conv_date
2742 ,x_conversion_type => l_conversion_type
2743 ,x_user_rate => p_conv_rate
2744 ,x_amount => p_from_amount
2745 ,x_max_roll_days => l_max_roll_days
2746 ,x_converted_amount => x_to_amount
2747 ,x_denominator => l_denominator
2748 ,x_numerator => l_numerator
2749 ,x_rate => x_rate);
2750 --
2751
2752 EXCEPTION
2753 WHEN gl_currency_api.no_rate THEN
2754 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2755 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2756 fnd_msg_pub.add;
2757 END IF;
2758
2759 x_return_status := fnd_api.g_ret_sts_error;
2760 WHEN gl_currency_api.invalid_currency THEN
2761 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2762 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2763 fnd_msg_pub.add;
2764 END IF;
2765
2766 x_return_status := fnd_api.g_ret_sts_error;
2767 WHEN OTHERS THEN
2768 x_return_status := fnd_api.g_ret_sts_unexp_error;
2769
2770 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2771 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_curency');
2772 END IF;
2773 END convert_currency;
2774
2775 /*============================================================================*/
2776 -- Start of Comments
2777 -- NAME
2778 -- Get_Resource_Role
2779 --
2780 -- PURPOSE
2781 -- This Procedure will be return the workflow user role for
2782 -- the resourceid sent
2783 -- Called By
2784 -- NOTES
2785 -- End of Comments
2786
2787 /*============================================================================*/
2788
2789 PROCEDURE Get_Resource_Role
2790 ( p_resource_id IN NUMBER,
2791 x_role_name OUT NOCOPY VARCHAR2,
2792 x_role_display_name OUT NOCOPY VARCHAR2 ,
2793 x_return_status OUT NOCOPY VARCHAR2
2794 )
2795 IS
2796 l_msg_count NUMBER;
2797 l_msg_data VARCHAR2(4000);
2798 l_error_msg VARCHAR2(4000);
2799
2800 CURSOR c_resource IS
2801 SELECT employee_id , user_id, category
2802 FROM ams_jtf_rs_emp_v
2803 WHERE resource_id = p_resource_id ;
2804
2805 l_person_id number;
2806 l_user_id number;
2807 l_category varchar2(30);
2808 BEGIN
2809 x_return_status := FND_API.G_RET_STS_SUCCESS;
2810 OPEN c_resource ;
2811 FETCH c_resource INTO l_person_id , l_user_id, l_category;
2812 IF c_resource%NOTFOUND THEN
2813 CLOSE c_resource ;
2814 x_return_status := FND_API.G_RET_STS_ERROR;
2815 OZF_Utility_PVT.error_message ('OZF_APPR_INVALID_RESOURCE_ID');
2816 return;
2817 END IF;
2818 CLOSE c_resource ;
2819 -- Pass the Employee ID to get the Role
2820 IF l_category = 'PARTY' THEN
2821 WF_DIRECTORY.getrolename
2822 ( p_orig_system => 'FND_USR',
2823 p_orig_system_id => l_user_id ,
2824 p_name => x_role_name,
2825 p_display_name => x_role_display_name
2826 );
2827 IF x_role_name is null then
2828 x_return_status := FND_API.G_RET_STS_ERROR;
2829 OZF_Utility_PVT.error_message ('OZF_APPR_INVALID_ROLE');
2830 return;
2831 END IF;
2832 ELSE
2833 WF_DIRECTORY.getrolename
2834 ( p_orig_system => 'PER',
2835 p_orig_system_id => l_person_id ,
2836 p_name => x_role_name,
2837 p_display_name => x_role_display_name
2838 );
2839 IF x_role_name is null then
2840 x_return_status := FND_API.G_RET_STS_ERROR;
2841 OZF_Utility_PVT.error_message ('OZF_APPR_INVALID_ROLE');
2842 return;
2843 END IF;
2844 END IF;
2845 EXCEPTION
2846 WHEN OTHERS THEN
2847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2848 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2849 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Get_Resource_Role');
2850 END IF;
2851 RAISE;
2852 END Get_Resource_Role;
2853
2854 --======================================================================
2855 -- Procedure Name: send_wf_standalone_message
2856 -- Type : Generic utility
2857 -- Pre-Req :
2858 -- Notes:
2859 -- Common utility to send standalone message without initiating
2860 -- process using workflow.
2861 -- Parameters:
2862 -- IN:
2863 -- p_item_type IN VARCHAR2 Required Default = "MAPGUTIL"
2864 -- item type for the workflow utility.
2865 -- p_message_name IN VARCHAR2 Required Default = "GEN_STDLN_MESG"
2866 -- Internal name for standalone message name
2867 -- p_subject IN VARCHAR2 Required
2868 -- Subject for the message
2869 -- p_body IN VARCHAR2 Optional
2870 -- Body for the message
2871 -- p_send_to_role_name IN VARCHAR2 Optional
2872 -- Role name to whom message is to be sent.
2873 -- Instead of this, one can send even p_send_to_res_id
2874 -- p_send_to_res_id IN NUMBER Optional
2875 -- Resource Id that will be used to get role name from WF_DIRECTORY.
2876 -- This is required if role name is not passed.
2877
2878 -- OUT:
2879 -- x_notif_id OUT NUMBER
2880 -- Notification Id created that is being sent to recipient.
2881 -- x_return_status OUT VARCHAR2
2882 -- Return status. If it is error, messages will be put in mesg pub.
2883 -- History:
2884 -- 11-Jan-2002 sveerave Created.
2885 --======================================================================
2886
2887 PROCEDURE send_wf_standalone_message(
2888 p_item_type IN VARCHAR2 := 'MAPGUTIL'
2889 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
2890 ,p_subject IN VARCHAR2
2891 ,p_body IN VARCHAR2 := NULL
2892 ,p_send_to_role_name IN VARCHAR2 := NULL
2893 ,p_send_to_res_id IN NUMBER := NULL
2894 ,x_notif_id OUT NOCOPY NUMBER
2895 ,x_return_status OUT NOCOPY VARCHAR2
2896 )
2897 IS
2898 l_role_name VARCHAR2(100) := p_send_to_role_name;
2899 l_display_role_name VARCHAR2(240);
2900 l_notif_id NUMBER;
2901
2902 BEGIN
2903 x_return_status := FND_API.G_RET_STS_SUCCESS;
2904 IF p_send_to_role_name IS NULL THEN
2905 OZF_UTILITY_PVT.get_resource_role
2906 ( p_resource_id => p_send_to_res_id,
2907 x_role_name => l_role_name,
2908 x_role_display_name => l_display_role_name,
2909 x_return_status => x_return_status
2910 );
2911 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2912 return;
2913 END IF;
2914 END IF;
2915 l_notif_id := WF_NOTIFICATION.Send
2916 ( role => l_role_name
2917 , msg_type => p_item_type
2918 , msg_name => p_message_name
2919 );
2920 WF_NOTIFICATION.SetAttrText( l_notif_id
2921 , 'GEN_MSG_SUBJECT'
2922 , p_subject
2923 );
2924 WF_NOTIFICATION.SetAttrText( l_notif_id
2925 , 'GEN_MSG_BODY'
2926 , p_body
2927 );
2928 WF_NOTIFICATION.SetAttrText( l_notif_id
2929 , 'GEN_MSG_SEND_TO'
2930 , l_role_name
2931 );
2932 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
2933 x_notif_id := l_notif_id;
2934 END send_wf_standalone_message;
2935
2936 --======================================================================
2937 -- FUNCTION
2938 -- Check_Status_Change
2939 --
2940 -- PURPOSE
2941 -- Created to check if the status change is valid or not.
2942 -- Returns FND_API.G_TRUE if it is valid status change
2943 -- or will return FND_API.G_FALSE
2944 --
2945 -- HISTORY
2946 -- 09-Jul-2001 ptendulk Create.
2947 --======================================================================
2948 FUNCTION Check_Status_Change(
2949 p_status_type IN VARCHAR2,
2950 p_current_status IN VARCHAR2,
2951 p_next_status IN VARCHAR2
2952 )
2953 RETURN VARCHAR2
2954 IS
2955 CURSOR c_stat_det IS
2956 SELECT 1 FROM DUAL
2957 WHERE EXISTS (SELECT * FROM ams_status_order_rules
2958 WHERE current_status_code = p_current_status
2959 AND next_status_code = p_next_status
2960 AND system_status_type = p_status_type ) ;
2961 l_dummy NUMBER ;
2962 BEGIN
2963
2964 OPEN c_stat_det ;
2965 FETCH c_stat_det INTO l_dummy ;
2966 CLOSE c_stat_det;
2967
2968 IF l_dummy IS NULL THEN
2969 RETURN FND_API.G_FALSE ;
2970 ELSE
2971 RETURN FND_API.G_TRUE ;
2972 END IF ;
2973 END Check_Status_Change;
2974 --======================================================================
2975 -- FUNCTION
2976 -- CurrRound
2977 --
2978 -- PURPOSE
2979 -- Returns the round value for an amount based on the currency
2980 --
2981 -- HISTORY
2982 -- 13-Sep-2001 slkrishn Create.
2983 --======================================================================
2984 FUNCTION CurrRound(
2985 p_amount IN NUMBER,
2986 p_currency_code IN VARCHAR2
2987 )
2988 RETURN NUMBER
2989 IS
2990 BEGIN
2991 RETURN gl_mc_currency_pkg.CurrRound(p_amount, p_currency_code);
2992 END CurrRound;
2993
2994 --======================================================================
2995 -- PROCEDURE
2996 -- get_install_info
2997 --
2998 -- PURPOSE
2999 -- Gets the installation information for an application
3000 -- with application_id p_dep_appl_id
3001 --
3002 -- HISTORY
3003 -- 19-Dec-2002 mayjain Create.
3004 --======================================================================
3005 procedure get_install_info(p_appl_id in number,
3006 p_dep_appl_id in number,
3007 x_status out nocopy varchar2,
3008 x_industry out nocopy varchar2,
3009 x_installed out nocopy number)
3010 IS
3011 l_installed BOOLEAN;
3012
3013 BEGIN
3014 l_installed := fnd_installation.get( appl_id => p_appl_id,
3015 dep_appl_id => p_dep_appl_id,
3016 status => x_status,
3017 industry => x_industry );
3018 IF (l_installed) THEN
3019 x_installed := 1;
3020 ELSE
3021 x_installed := 0;
3022 END IF;
3023
3024 END get_install_info;
3025
3026 --======================================================================
3027 -- PROCEDURE
3028 -- Get_Object_Name
3029 --
3030 -- PURPOSE
3031 -- Callback method for IBC to get the Associated Object name for an
3032 -- Electronic Deliverable Attachment.
3033 --
3034 -- HISTORY
3035 -- 3/7/2003 mayjain Create.
3036 --======================================================================
3037 PROCEDURE Get_Object_Name(
3038 p_association_type_code IN VARCHAR2
3039 ,p_associated_object_val1 IN VARCHAR2
3040 ,p_associated_object_val2 IN VARCHAR2
3041 ,p_associated_object_val3 IN VARCHAR2 DEFAULT NULL
3042 ,p_associated_object_val4 IN VARCHAR2 DEFAULT NULL
3043 ,p_associated_object_val5 IN VARCHAR2 DEFAULT NULL
3044 ,x_object_name OUT NOCOPY VARCHAR2
3045 ,x_object_code OUT NOCOPY VARCHAR2
3046 ,x_return_status OUT NOCOPY VARCHAR2
3047 ,x_msg_count OUT NOCOPY NUMBER
3048 ,x_msg_data OUT NOCOPY VARCHAR2
3049 )
3050 IS
3051
3052
3053 CURSOR Cur_Delv(p_delv_id IN NUMBER)
3054 IS
3055 SELECT deliverable_name
3056 FROM ams_deliverables_vl
3057 WHERE deliverable_id = p_delv_id;
3058
3059
3060 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_NAME';
3061 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_UTILITY_PVT';
3062
3063 BEGIN
3064
3065 If p_association_type_code = 'OZF_DELV' then
3066 x_return_status := FND_API.G_RET_STS_SUCCESS;
3067
3068 OPEN Cur_Delv(p_associated_object_val1);
3069 FETCH Cur_Delv INTO x_object_name;
3070 CLOSE Cur_Delv;
3071
3072
3073 END IF;
3074 -- here you can add processing for other association type as the else part
3075
3076 EXCEPTION
3077 WHEN FND_API.G_EXC_ERROR THEN
3078 x_return_status := FND_API.G_RET_STS_ERROR;
3079 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3080 p_data => x_msg_data);
3081 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3083 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3084 p_data => x_msg_data);
3085 WHEN OTHERS THEN
3086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3087 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3088 THEN
3089 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3090 END IF;
3091 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3092 p_data => x_msg_data);
3093
3094 END Get_Object_Name;
3095
3096
3097
3098 --========================================================================
3099 -- PROCEDURE
3100 -- get_user_id
3101 --
3102 -- PURPOSE
3103 -- This api will take a resource id and give the corresponding user_id
3104 --
3105 -- NOTE
3106 --
3107 -- HISTORY
3108 -- 28-may-2003 soagrawa Created
3109 --========================================================================
3110
3111
3112 FUNCTION get_user_id (
3113 p_resource_id IN NUMBER
3114 )
3115 RETURN NUMBER
3116 IS
3117 l_user_id NUMBER;
3118
3119 CURSOR c_user IS
3120 SELECT user_id
3121 FROM ams_jtf_rs_emp_v
3122 WHERE resource_id = p_resource_id;
3123 BEGIN
3124 OPEN c_user;
3125 FETCH c_user INTO l_user_id;
3126 IF c_user%NOTFOUND THEN
3127 l_user_id := -1;
3128 END IF;
3129 CLOSE c_user;
3130
3131 RETURN l_user_id;
3132 END get_user_id;
3133
3134 --========================================================================
3135 -- PROCEDURE
3136 -- get_party_name
3137 --
3138 -- PURPOSE
3139 -- This api returns party name given the customer_account_id
3140 --
3141 -- NOTE
3142 --
3143 -- HISTORY
3144 -- Wed Oct 29 2003:3/36 PM rssharma Created
3145 --========================================================================
3146
3147 FUNCTION get_party_name(p_cust_account_id IN NUMBER)
3148 RETURN VARCHAR2
3149 IS
3150 CURSOR c_party_name(p_cust_account_id NUMBER)
3151 IS
3152 SELECT hp.party_name FROM hz_parties hp , hz_cust_accounts hca
3153 WHERE hca.party_id = hp.party_id
3154 AND hca.status = 'A'
3155 AND hca.cust_account_id = p_cust_account_id;
3156 l_party_name hz_parties.party_name%type;
3157 BEGIN
3158 OPEN c_party_name(p_cust_account_id );
3159 FETCH c_party_name INTO l_party_name;
3160 CLOSE c_party_name;
3161 RETURN l_party_name;
3162 END;
3163
3164 --========================================================================
3165 -- PROCEDURE
3166 -- get_user_status_name
3167 --
3168 -- PURPOSE
3169 -- This api returns user_status_name given the user_status_id and the system_status_code
3170 --
3171 -- NOTE
3172 --
3173 -- HISTORY
3174 -- Wed Jan 21 2004:4/18 PM rssharma Created
3175 --========================================================================
3176
3177 FUNCTION get_user_status_name(p_user_status_id IN NUMBER , p_system_status_type IN VARCHAR2)
3178 RETURN VARCHAR2
3179 IS
3180 CURSOR c_user_status_name (p_user_status_id NUMBER , p_system_status_type VARCHAR2)IS
3181 SELECT name FROM ams_user_statuses_vl where user_status_id = p_user_status_id
3182 AND system_status_type = p_system_status_type;
3183 l_user_status_name ams_user_statuses_tl.name%type;
3184 BEGIN
3185 OPEN c_user_status_name(p_user_status_id , p_system_status_type );
3186 FETCH c_user_status_name INTO l_user_status_name;
3187 CLOSE c_user_status_name;
3188 RETURN l_user_status_name;
3189 END;
3190
3191 --========================================================================
3192 -- PROCEDURE
3193 -- get_currency_name
3194 --
3195 -- PURPOSE
3196 -- This api returns Currency Name given the Currency Code
3197 --
3198 -- NOTE
3199 --
3200 -- HISTORY
3201 -- Fri Jan 23 2004:3/18 PM rssharma Created
3202 --========================================================================
3203 FUNCTION get_currency_name(p_currency_code IN VARCHAR2) RETURN VARCHAR2
3204 IS
3205 CURSOR C_CURR_NAME(p_currency_code VARCHAR2) IS
3206 SELECT name FROM fnd_currencies_vl WHERE currency_code = p_currency_code;
3207
3208 l_currency_name fnd_currencies_vl.name%type;
3209
3210 BEGIN
3211 OPEN C_CURR_NAME(p_currency_code);
3212 FETCH C_CURR_NAME INTO l_currency_name;
3213 CLOSE C_CURR_NAME;
3214 return l_currency_name;
3215 END;
3216
3217 --========================================================================
3218 -- PROCEDURE
3219 -- get_currency_name
3220 --
3221 -- PURPOSE
3222 -- This api returns Media Name given the Media id
3223 --
3224 -- NOTE
3225 --
3226 -- HISTORY
3227 -- Fri Jan 23 2004:3/18 PM rssharma Created
3228 --========================================================================
3229 FUNCTION get_media_name(p_media_id IN NUMBER) RETURN VARCHAR2
3230 IS
3231 CURSOR c_media_name(p_media_id NUMBER) IS
3232 SELECT media_name FROM ams_media_vl
3233 WHERE media_id = p_media_id;
3234 l_media_name ams_media_tl.media_name%type;
3235
3236 BEGIN
3237 OPEN c_media_name(p_media_id);
3238 FETCH c_media_name INTO l_media_name;
3239 CLOSE c_media_name;
3240
3241 RETURN l_media_name;
3242 END;
3243
3244 --========================================================================
3245 -- PROCEDURE
3246 -- get_fnd_lookup_meaning
3247 --
3248 -- PURPOSE
3249 -- This api returns Meaning of a NON-OZF FND lookup given the lookupcode and lookup_type
3250 --
3251 -- NOTE
3252 --
3253 -- HISTORY
3254 -- Fri Jan 23 2004:3/18 PM rssharma Created
3255 --========================================================================
3256
3257 FUNCTION get_fnd_lookup_meaning(p_lookup_type IN VARCHAR2,p_lookup_code IN VARCHAR2) RETURN VARCHAR2
3258 IS
3259 CURSOR c_lookup_meaning(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2) IS
3260 SELECT meaning FROM fnd_lookups WHERE lookup_type = p_lookup_type
3261 AND lookup_code = p_lookup_code;
3262
3263 l_lookup_meaning fnd_lookups.meaning%type;
3264 BEGIN
3265 OPEN c_lookup_meaning(p_lookup_type,p_lookup_code);
3266 FETCH c_lookup_meaning INTO l_lookup_meaning;
3267 CLOSE c_lookup_meaning;
3268
3269 RETURN l_lookup_meaning;
3270 END;
3271
3272 --======================================================================
3273 -- FUNCTION
3274 -- Has_Sales_Rep_Role
3275 --
3276 -- PURPOSE
3277 -- This api returns FND_API.g_true if the given user has the "Sales
3278 -- Representative" role. It returns FND_API.g_false otherwise.
3279 --
3280 -- HISTORY
3281 -- 23-Feb-2004 yizhang Create.
3282 --======================================================================
3283 FUNCTION Has_Sales_Rep_Role(
3284 p_resource_id IN NUMBER
3285 ) RETURN VARCHAR2
3286 IS
3287 l_role_id number;
3288
3289 CURSOR csr_sales_rep(cv_resource_id IN NUMBER) IS
3290 select rr.role_id
3291 from jtf_rs_role_relations rr
3292 , jtf_rs_roles_vl rl
3293 where rr.role_id = rl.role_id
3294 and rl.role_type_code = 'SALES'
3295 and rl.role_code = 'SALES_REP'
3296 and rr.delete_flag = 'N'
3297 and TRUNC(sysdate) between TRUNC(rr.start_date_active)
3298 and TRUNC(nvl(rr.end_date_active,sysdate))
3299 and rr.role_resource_id = cv_resource_id;
3300
3301 BEGIN
3302 OPEN csr_sales_rep(p_resource_id);
3303 FETCH csr_sales_rep INTO l_role_id;
3304 IF csr_sales_rep%NOTFOUND THEN
3305 CLOSE csr_sales_rep;
3306 RETURN FND_API.g_false;
3307 END IF;
3308 CLOSE csr_sales_rep;
3309
3310 RETURN FND_API.g_true;
3311 END;
3312
3313 --======================================================================
3314 -- FUNCTION
3315 -- get_commited_amount
3316
3317 -- PURPOSE
3318 -- The api return committed budget amount.
3319
3320 -- HISTORY
3321 -- 17-Feb-2005 feliu Create.
3322 --======================================================================
3323
3324 FUNCTION get_commited_amount(p_list_header_id IN NUMBER)
3325 RETURN NUMBER
3326 IS
3327 p_committed_amount NUMBER := 0;
3328
3329 CURSOR curr_committed_amount(list_header_id NUMBER) IS
3330 SELECT SUM(NVL(plan_curr_committed_amt,0))
3331 FROM ozf_object_fund_summary
3332 WHERE object_id = list_header_id
3333 AND object_type = 'OFFR';
3334 /*
3335 SELECT SUM(AMOUNT)
3336 FROM(
3337 SELECT uti.plan_curr_amount amount
3338 FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
3339 WHERE uti.utilization_type ='REQUEST'
3340 AND component_type = 'OFFR'
3341 AND component_id = list_header_id
3342 AND act.activity_budget_id = uti.ams_activity_budget_id
3343 AND act.recal_flag is NULL
3344 UNION ALL
3345 SELECT 0-uti.plan_curr_amount amount
3346 FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
3347 WHERE uti.utilization_type ='TRANSFER'
3348 AND plan_type = 'OFFR'
3349 AND plan_id = list_header_id
3350 AND act.activity_budget_id = uti.ams_activity_budget_id
3351 AND act.recal_flag is NULL);
3352 */
3353 BEGIN
3354 OPEN curr_committed_amount( p_list_header_id ) ;
3355 FETCH curr_committed_amount INTO p_committed_amount ;
3356 CLOSE curr_committed_amount ;
3357 RETURN p_committed_amount ;
3358
3359 EXCEPTION
3360 WHEN OTHERS THEN
3361 RETURN 0;
3362 END;
3363
3364 --======================================================================
3365 -- FUNCTION
3366 -- get_commited_amount
3367
3368 -- PURPOSE
3369 -- The api return recal-committed budget amount.
3370
3371 -- HISTORY
3372 -- 17-Feb-2005 feliu Create.
3373 --======================================================================
3374
3375 FUNCTION get_recal_commited_amount(p_list_header_id IN NUMBER)
3376 RETURN NUMBER
3377 IS
3378 p_committed_amount NUMBER := 0;
3379
3380 CURSOR curr_committed_amount(list_header_id NUMBER) IS
3381 SELECT SUM(NVL(plan_curr_recal_committed_amt,0))
3382 FROM ozf_object_fund_summary
3383 WHERE object_id = list_header_id
3384 AND object_type = 'OFFR';
3385 /*
3386 SELECT SUM(AMOUNT)
3387 FROM(
3388 SELECT uti.plan_curr_amount amount
3389 FROM ozf_funds_utilized_all_b uti
3390 WHERE uti.utilization_type ='REQUEST'
3391 AND component_type = 'OFFR'
3392 AND component_id = list_header_id
3393 UNION ALL
3394 SELECT 0-uti.plan_curr_amount amount
3395 FROM ozf_funds_utilized_all_b uti
3396 WHERE uti.utilization_type ='TRANSFER'
3397 AND plan_type = 'OFFR'
3398 AND plan_id = list_header_id);
3399 */
3400 BEGIN
3401 OPEN curr_committed_amount( p_list_header_id ) ;
3402 FETCH curr_committed_amount INTO p_committed_amount ;
3403 CLOSE curr_committed_amount ;
3404 RETURN p_committed_amount ;
3405
3406 EXCEPTION
3407 WHEN OTHERS THEN
3408 RETURN 0;
3409 END;
3410
3411
3412 FUNCTION get_utilized_amount(p_list_header_id IN NUMBER)
3413 RETURN NUMBER
3414 IS
3415 p_utilized_amount NUMBER := 0;
3416
3417 CURSOR curr_utilized_amount(list_header_id NUMBER) IS
3418 SELECT SUM(NVL(plan_curr_utilized_amt,0))
3419 FROM ozf_object_fund_summary
3420 WHERE object_id = list_header_id
3421 AND object_type = 'OFFR';
3422
3423 /*
3424 SELECT SUM(uti.plan_curr_amount)
3425 FROM ozf_funds_utilized_all_vl uti
3426 WHERE uti.utilization_type IN
3427 ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
3428 AND plan_type = 'OFFR'
3429 AND plan_id = list_header_id;
3430 */
3431 BEGIN
3432 OPEN curr_utilized_amount( p_list_header_id ) ;
3433 FETCH curr_utilized_amount INTO p_utilized_amount ;
3434 CLOSE curr_utilized_amount ;
3435 RETURN p_utilized_amount ;
3436
3437 EXCEPTION
3438 WHEN OTHERS THEN
3439 RETURN 0;
3440 END;
3441
3442 --======================================================================
3443 -- FUNCTION
3444 -- get_commited_amount
3445
3446 -- PURPOSE
3447 -- The api return earned budget amount.
3448
3449 -- HISTORY
3450 -- 17-Feb-2005 feliu Create.
3451 --======================================================================
3452
3453 FUNCTION get_earned_amount(p_list_header_id IN NUMBER)
3454 RETURN NUMBER
3455 IS
3456 p_earned_amount NUMBER := 0;
3457
3458 CURSOR curr_earned_amount(list_header_id NUMBER) IS
3459 SELECT SUM(NVL(plan_curr_earned_amt,0))
3460 FROM ozf_object_fund_summary
3461 WHERE object_id = list_header_id
3462 AND object_type = 'OFFR';
3463 /*
3464 SELECT SUM(uti.plan_curr_amount)
3465 FROM ozf_funds_utilized_all_vl uti
3466 WHERE uti.utilization_type IN
3467 ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
3468 AND plan_type = 'OFFR'
3469 AND plan_id = p_list_header_id
3470 AND gl_posted_flag NOT in('N','F');
3471 */
3472 BEGIN
3473 OPEN curr_earned_amount( p_list_header_id ) ;
3474 FETCH curr_earned_amount INTO p_earned_amount ;
3475 CLOSE curr_earned_amount ;
3476 RETURN p_earned_amount ;
3477
3478 EXCEPTION
3479 WHEN OTHERS THEN
3480 RETURN 0;
3481 END;
3482
3483 --======================================================================
3484 -- FUNCTION
3485 -- get_commited_amount
3486
3487 -- PURPOSE
3488 -- The api return paid budget amount.
3489
3490 -- HISTORY
3491 -- 17-Feb-2005 feliu Create.
3492 --======================================================================
3493
3494 FUNCTION get_paid_amount(p_list_header_id IN NUMBER)
3495 RETURN NUMBER
3496 IS
3497 p_paid_amount NUMBER := 0;
3498
3499 CURSOR curr_paid_amount(list_header_id NUMBER) IS
3500 SELECT SUM(NVL(plan_curr_paid_amt,0))
3501 FROM ozf_object_fund_summary
3502 WHERE object_id = list_header_id
3503 AND object_type = 'OFFR';
3504
3505 /*
3506
3507 SELECT SUM(AMOUNT) FROM
3508 (SELECT SUM(plan_curr_amount - NVL(plan_curr_amount_remaining,0)) amount
3509 FROM ozf_funds_utilized_all_b util
3510 WHERE utilization_type = 'UTILIZED'
3511 AND NVL(util.gl_posted_flag,'Y') = 'Y'
3512 AND plan_type = 'OFFR'
3513 AND plan_id = list_header_id
3514 UNION AlL
3515 SELECT cuti.plan_curr_amount amount
3516 FROM ozf_funds_utilized_all_b util,
3517 ozf_claim_lines_util_all cuti,
3518 ozf_claim_lines_all cln,
3519 ozf_claims_all cla
3520 WHERE util.utilization_id(+) = cuti.utilization_id
3521 AND util.utilization_type IN ('ACCRUAL','ADJUSTMENT', 'CHARGEBACK', 'LEAD_ACCRUAL')
3522 AND cuti.claim_line_id = cln.claim_line_id
3523 AND cln.claim_id = cla.claim_id
3524 AND cla.status_code = 'CLOSED'
3525 AND util.plan_type = 'OFFR'
3526 AND util.plan_id = list_header_id);
3527 */
3528
3529 BEGIN
3530 OPEN curr_paid_amount( p_list_header_id ) ;
3531 FETCH curr_paid_amount INTO p_paid_amount ;
3532 CLOSE curr_paid_amount ;
3533 RETURN p_paid_amount ;
3534
3535 EXCEPTION
3536 WHEN OTHERS THEN
3537 RETURN 0;
3538 END;
3539
3540 FUNCTION get_org_name(p_org_id NUMBER)
3541 RETURN VARCHAR2
3542 IS
3543 /*
3544 -- Bug 5065276 SQL ID 16501642
3545 -- The name can be fetched directly from the source table
3546 -- rather than the view
3547 CURSOR c_org_name IS
3548 SELECT name
3549 FROM hr_operating_units
3550 WHERE organization_id = p_org_id;
3551 */
3552
3553 CURSOR c_org_name IS
3554 SELECT name
3555 FROM hr_all_organization_units_tl
3556 WHERE organization_id = p_org_id
3557 AND language = userenv('LANG');
3558
3559 l_org_name VARCHAR2(240);
3560
3561 BEGIN
3562 OPEN c_org_name;
3563 FETCH c_org_name INTO l_org_name;
3564 CLOSE c_org_name;
3565
3566 RETURN l_org_name;
3567 END get_org_name;
3568
3569 FUNCTION getAttributeName
3570 (
3571 p_attributeCode IN VARCHAR2
3572 )
3573 RETURN VARCHAR2
3574 IS
3575 BEGIN
3576 return getAttributeName(p_attributeCode => p_attributeCode, p_applicationId => G_OZF_APP_ID );
3577 END getAttributeName;
3578
3579 FUNCTION getAttributeName
3580 (
3581 p_attributeCode IN VARCHAR2
3582 , p_applicationId IN NUMBER
3583 ) RETURN VARCHAR2
3584 IS
3585 CURSOR c_name(cp_attributeCode VARCHAR2, cp_applicationId NUMBER) IS
3586 SELECT ak.attribute_label_long
3587 FROM ak_attributes_vl ak
3588 WHERE ak.attribute_code = upper(cp_attributeCode)
3589 AND ak.attribute_application_id = cp_applicationId;
3590
3591 l_attributeName AK_ATTRIBUTES_VL.NAME%TYPE;
3592 BEGIN
3593 OPEN c_name(cp_attributeCode => p_attributeCode, cp_applicationId => p_applicationId);
3594 FETCH c_name INTO l_attributeName;
3595 IF c_name%NOTFOUND THEN
3596 l_attributeName := null;
3597 END IF;
3598 CLOSE c_name;
3599 RETURN l_attributeName;
3600 END getAttributeName;
3601 --======================================================================
3602 -- FUNCTION
3603 -- get_revenue_account
3604
3605 -- PURPOSE
3606 -- The function returns the revenue account for Off-Invoice offer.
3607 --
3608 -- HISTORY
3609 -- 05-Mar-2010 BKUNJAN ER 9382547 - TM SLA Uptake
3610 --======================================================================
3611
3612 FUNCTION get_revenue_account (p_utilization_id IN NUMBER
3613 ,p_org_id IN NUMBER)
3614 RETURN NUMBER
3615 IS
3616
3617 CURSOR get_om_profile IS
3618 SELECT parameter_value
3619 FROM oe_sys_parameters_all
3620 WHERE parameter_code = 'OE_DISCOUNT_DETAILS_ON_INVOICE'
3621 AND org_id = p_org_id;
3622
3623 CURSOR get_revenue_acct_csr1 IS
3624 SELECT cgl.code_combination_id
3625 FROM ozf_funds_utilized_all_b fu ,
3626 oe_price_adjustments pa ,
3627 oe_order_lines_all ol ,
3628 oe_order_headers_all oh ,
3629 ra_customer_trx_lines_all ctl ,
3630 ra_cust_trx_line_gl_dist_all cgl
3631 WHERE fu.price_adjustment_id = pa.price_adjustment_id
3632 AND ol.line_id = pa.line_id
3633 AND ol.header_id = oh.header_id
3634 AND ctl.interface_line_attribute1 = TO_CHAR(oh.order_number)
3635 --and ctl.sales_order_line = ol.line_number
3636 AND ctl.interface_line_attribute6 = TO_CHAR(ol.line_id) --Fix for bug 15952083
3637 AND cgl.customer_trx_line_id = ctl.customer_trx_line_id
3638 AND cgl.account_class = 'REV'
3639 AND fu.utilization_id = p_utilization_id
3640 AND fu.order_line_id = ctl.interface_line_attribute6;
3641
3642 CURSOR get_revenue_acct_csr2 IS
3643 SELECT cgl.code_combination_id
3644 FROM ozf_funds_utilized_all_b fu ,
3645 oe_price_adjustments pa ,
3646 oe_order_lines_all ol ,
3647 oe_order_headers_all oh ,
3648 ra_customer_trx_lines_all ctl ,
3649 ra_cust_trx_line_gl_dist_all cgl
3650 WHERE fu.price_adjustment_id = pa.price_adjustment_id
3651 AND ol.line_id = pa.line_id
3652 AND ol.header_id = oh.header_id
3653 AND ctl.interface_line_attribute1 = TO_CHAR(oh.order_number)
3654 --AND ctl.sales_order_line = ol.line_number
3655 AND ctl.interface_line_attribute6 = TO_CHAR(ol.line_id) --Fix for bug 15952083
3656 AND ctl.interface_line_attribute11 = TO_CHAR(pa.price_adjustment_id)
3657 AND cgl.customer_trx_line_id = ctl.customer_trx_line_id
3658 AND cgl.account_class = 'REV'
3659 AND fu.utilization_id = p_utilization_id;
3660
3661 l_oe_disc_dtls_on_invoice VARCHAR2(1);
3662 l_revenue_acct NUMBER;
3663
3664 BEGIN
3665
3666 OPEN get_om_profile;
3667 FETCH get_om_profile INTO l_oe_disc_dtls_on_invoice;
3668 CLOSE get_om_profile;
3669
3670 l_oe_disc_dtls_on_invoice := NVL(l_oe_disc_dtls_on_invoice,'N');
3671
3672 IF l_oe_disc_dtls_on_invoice = 'Y' THEN
3673 OPEN get_revenue_acct_csr2;
3674 FETCH get_revenue_acct_csr2 INTO l_revenue_acct;
3675 CLOSE get_revenue_acct_csr2;
3676 ELSE
3677
3678 OPEN get_revenue_acct_csr1;
3679 FETCH get_revenue_acct_csr1 INTO l_revenue_acct;
3680 CLOSE get_revenue_acct_csr1;
3681 END IF;
3682
3683 return l_revenue_acct;
3684
3685 END get_revenue_account;
3686 ---------------------------------------------------------------------
3687 -- PROCEDURE
3688 -- UPDATE_OZF_ACT_BUDGETS
3689 --
3690 -- PURPOSE
3691 -- This procedure for updating main tables with values from interface table
3692 -- ozf_act_budgets_int for chargeback & TPA flow only
3693 --
3694 -- PARAMETERS
3695 --
3696 -- NOTES
3697 -- MUTHSUBR FOR BUG#8867381
3698 -- MUTHSUBR For TPA Parallel Execution ER Bug#9614703
3699 ---------------------------------------------------------------------
3700 PROCEDURE UPDATE_OZF_ACT_BUDGETS (
3701 p_batch_id IN NUMBER,
3702 x_return_status OUT NOCOPY VARCHAR2,
3703 x_budget_update_status OUT NOCOPY VARCHAR2,
3704 px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
3705 p_batch_type IN VARCHAR2)
3706 IS
3707 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OZF_ACT_BUDGETS';
3708 l_api_version_number CONSTANT NUMBER := 1.0;
3709 --l_object_version_number NUMBER;
3710 l_act_budget_id NUMBER;
3711 error_no_rollback EXCEPTION;
3712 x_msg_count NUMBER;
3713 x_msg_data VARCHAR2(50);
3714
3715 --insrt_flag VARCHAR2(1);
3716 insrt_count NUMBER := 0;
3717
3718 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
3719
3720 /*CURSOR budget_objversion_csr(p_act_budget_id IN NUMBER) IS
3721 SELECT
3722 OBJECT_VERSION_NUMBER
3723 FROM OZF_ACT_BUDGETS
3724 WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;*/
3725
3726 BEGIN
3727
3728 SAVEPOINT UPDATE_OZF_ACT_BUDGETS;
3729
3730 IF G_DEBUG THEN
3731 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
3732 OZF_UTILITY_PVT.debug_message('px_ozf_act_budgets_tbl.COUNT: ' || px_ozf_act_budgets_tbl.COUNT);
3733 END IF;
3734
3735 IF px_ozf_act_budgets_tbl.COUNT > 0 THEN
3736
3737 insrt_count := 0;
3738
3739 FOR i IN 1 .. px_ozf_act_budgets_tbl.COUNT
3740 LOOP
3741
3742 l_act_budget_id := px_ozf_act_budgets_tbl(i).ACTIVITY_BUDGET_ID;
3743
3744 IF G_DEBUG THEN
3745 OZF_UTILITY_PVT.debug_message('l_act_budget_id: ' || l_act_budget_id);
3746 OZF_UTILITY_PVT.debug_message('approved_amount: ' || px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT);
3747 END IF;
3748
3749 /*Bug 9614703, insert all records in staging table and remove update of ozf_act_budgets table to avoid locking.
3750 The update is moved to the concurrent program 'OZF: Update Budgets from Interface Tables'.*/
3751
3752 /*OPEN budget_objversion_csr(l_act_budget_id);
3753 FETCH budget_objversion_csr INTO l_object_version_number;
3754 CLOSE budget_objversion_csr;
3755
3756 -- Updating the main table ozf_act_budgets with all the values of plsql table in chargeback flow
3757 UPDATE ozf_act_budgets
3758 SET request_amount = NVL(request_amount, 0) + NVL(px_ozf_act_budgets_tbl(i).REQUEST_AMOUNT,0)
3759 ,src_curr_request_amt = NVL(src_curr_request_amt, 0) + NVL(px_ozf_act_budgets_tbl(i).SRC_CURR_REQUEST_AMT,0)
3760 ,approved_amount = NVL(approved_amount, 0) + NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT,0)
3761 ,approved_original_amount = NVL(approved_original_amount, 0) + NVL(px_ozf_act_budgets_tbl(i).APPROVED_ORIGINAL_AMOUNT,0)
3762 ,approved_amount_fc = NVL(approved_amount_fc, 0) + NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT_FC,0)
3763 ,parent_src_apprvd_amt = NVL(parent_src_apprvd_amt, 0) + NVL(px_ozf_act_budgets_tbl(i).PARENT_SRC_APPRVD_AMT ,0)
3764 ,last_update_date = sysdate
3765 ,last_updated_by = NVL (fnd_global.user_id, -1)
3766 ,last_update_login = NVL (fnd_global.conc_login_id, -1)
3767 ,object_version_number = NVL (object_version_number,0) + 1
3768 WHERE activity_budget_id = px_ozf_act_budgets_tbl(i).ACTIVITY_BUDGET_ID
3769 AND OBJECT_VERSION_NUMBER = l_object_version_number;
3770
3771 IF (SQL%FOUND) THEN
3772 x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
3773
3774 ELSE */
3775
3776 insrt_count := insrt_count + 1;
3777
3778 INSERT INTO OZF_ACT_BUDGETS_INT
3779 (
3780 RESALE_BATCH_ID,
3781 ACTIVITY_BUDGET_ID,
3782 LAST_UPDATE_DATE,
3783 LAST_UPDATED_BY,
3784 CREATION_DATE,
3785 CREATED_BY,
3786 LAST_UPDATE_LOGIN,
3787 REQUEST_AMOUNT,
3788 APPROVED_AMOUNT,
3789 APPROVED_ORIGINAL_AMOUNT,
3790 APPROVED_AMOUNT_FC,
3791 PARENT_SRC_APPRVD_AMT,
3792 SRC_CURR_REQUEST_AMT,
3793 -- For JMS ER (+)
3794 BATCH_TYPE,
3795 REQUEST_ID
3796 -- For JMS ER (-)
3797 )
3798 VALUES(
3799 p_batch_id,
3800 px_ozf_act_budgets_tbl(i).ACTIVITY_BUDGET_ID,
3801 SYSDATE,
3802 NVL(fnd_global.user_id, -1),
3803 SYSDATE,
3804 NVL(fnd_global.user_id, -1),
3805 NVL(fnd_global.user_id, -1),
3806 (NVL(px_ozf_act_budgets_tbl(i).REQUEST_AMOUNT,0)),
3807 (NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT,0)),
3808 (NVL(px_ozf_act_budgets_tbl(i).APPROVED_ORIGINAL_AMOUNT,0)),
3809 (NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT_FC,0)),
3810 (NVL(px_ozf_act_budgets_tbl(i).PARENT_SRC_APPRVD_AMT ,0)),
3811 (NVL(px_ozf_act_budgets_tbl(i).SRC_CURR_REQUEST_AMT,0)),
3812 -- For JMS ER (+)
3813 p_batch_type,
3814 fnd_global.conc_request_id
3815 -- For JMS ER (-)
3816 );
3817 -- END IF;
3818 END LOOP;
3819
3820 IF G_DEBUG THEN
3821 ozf_utility_pvt.write_conc_log('insrt_count of OZF_ACT_BUDGETS_INT: ' || insrt_count);
3822 END IF;
3823
3824 -- IF insrt_count > 0 THEN
3825 IF p_batch_type = 'CHARGEBACK' THEN
3826 x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE;
3827 ELSE
3828 x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_ACCRUALS;
3829 END IF;
3830 --ELSE
3831 -- x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
3832 -- END IF;
3833
3834 px_ozf_act_budgets_tbl.delete;
3835
3836 END IF; --IF px_ozf_act_budgets_tbl.COUNT > 0 THEN
3837
3838 IF G_DEBUG THEN
3839 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
3840 END IF;
3841
3842 EXCEPTION
3843 WHEN error_no_rollback THEN
3844 x_return_status := FND_API.G_RET_STS_ERROR;
3845 FND_MSG_PUB.Count_And_Get (
3846 p_encoded => FND_API.G_FALSE,
3847 p_count => x_msg_count,
3848 p_data => x_msg_data
3849 );
3850 WHEN FND_API.g_exc_error THEN
3851 ROLLBACK TO UPDATE_OZF_ACT_BUDGETS;
3852 x_return_status := FND_API.G_RET_STS_ERROR;
3853 FND_MSG_PUB.Count_And_Get (
3854 p_encoded => FND_API.G_FALSE,
3855 p_count => x_msg_count,
3856 p_data => x_msg_data
3857 );
3858 WHEN FND_API.g_exc_unexpected_error THEN
3859 ROLLBACK TO UPDATE_OZF_ACT_BUDGETS;
3860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3861 FND_MSG_PUB.Count_And_Get (
3862 p_encoded => FND_API.G_FALSE,
3863 p_count => x_msg_count,
3864 p_data => x_msg_data
3865 );
3866 WHEN OTHERS THEN
3867 ROLLBACK TO UPDATE_OZF_ACT_BUDGETS;
3868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3869 IF OZF_UNEXP_ERROR THEN
3870 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3871 END IF;
3872 FND_MSG_PUB.Count_And_Get (
3873 p_encoded => FND_API.G_FALSE,
3874 p_count => x_msg_count,
3875 p_data => x_msg_data
3876 );
3877
3878 END UPDATE_OZF_ACT_BUDGETS;
3879
3880
3881 ---------------------------------------------------------------------
3882 -- PROCEDURE
3883 -- UPDATE_OZF_FUNDS_ALL_B
3884 --
3885 -- PURPOSE
3886 -- This procedure for updating fund main tables in one shot with values
3887 -- from new record type px_ozf_funds_new_tbl for chargeback & TPA flow only.
3888 --
3889 -- PARAMETERS
3890 --
3891 -- NOTES
3892 -- MUTHSUBR FOR BUG#8867381
3893 -- MUTHSUBR For TPA Parallel Execution ER Bug#9614703
3894 ---------------------------------------------------------------------
3895 PROCEDURE UPDATE_OZF_FUNDS_ALL_B (
3896 p_batch_id IN NUMBER,
3897 x_return_status OUT NOCOPY VARCHAR2,
3898 x_fund_update_status OUT NOCOPY VARCHAR2,
3899 px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
3900 p_batch_type IN VARCHAR2 -- For JMS ER
3901 )
3902 IS
3903 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OZF_FUNDS_ALL_B';
3904 l_api_version_number CONSTANT NUMBER := 1.0;
3905 l_api_version CONSTANT NUMBER := 1.0;
3906 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
3907 x_msg_count NUMBER;
3908 x_msg_data VARCHAR2(50);
3909
3910 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
3911
3912 l_fund_id NUMBER;
3913 l_object_version_number NUMBER;
3914 l_fund_rec ozf_funds_pvt.fund_rec_type;
3915 --l_mc_transaction_rec ozf_mc_transactions_pvt.mc_transactions_rec_type;
3916 l_currency_code_tc VARCHAR2(30);
3917 l_org_id NUMBER;
3918 l_mc_record_id NUMBER;
3919 l_mc_obj_number NUMBER;
3920 l_original_budget NUMBER;
3921 error_no_rollback EXCEPTION;
3922
3923 l_budget_amount_tc NUMBER;
3924 l_budget_amount_fc NUMBER;
3925 l_available_amount NUMBER;
3926 l_transfered_in_amt NUMBER;
3927 l_transfered_out_amt NUMBER;
3928 l_planned_amt NUMBER;
3929 l_committed_amt NUMBER;
3930 l_earned_amt NUMBER;
3931 l_paid_amt NUMBER;
3932 l_recal_committed NUMBER;
3933 l_rollup_original_budget NUMBER;
3934 l_rollup_transfered_in_amt NUMBER;
3935 l_rollup_transfered_out_amt NUMBER;
3936 l_rollup_holdback_amt NUMBER;
3937 l_rollup_planned_amt NUMBER;
3938 l_rollup_committed_amt NUMBER;
3939 l_rollup_recal_committed NUMBER;
3940 l_rollup_earned_amt NUMBER;
3941 l_rollup_paid_amt NUMBER;
3942 l_utilized_amt NUMBER;
3943 l_rollup_utilized_amt NUMBER;
3944
3945 insrt_flag VARCHAR2(1);
3946 insrt_count NUMBER := 0;
3947
3948 /*CURSOR c_mc_record(source_id IN NUMBER) IS
3949 SELECT mc_record_id, object_version_number
3950 FROM ozf_mc_transactions_all
3951 WHERE source_object_id = source_id
3952 AND source_object_name = 'FUND';
3953
3954 CURSOR funds_rollup_csr(p_fund_id IN NUMBER) IS
3955 SELECT
3956 ORIGINAL_BUDGET,
3957 OBJECT_VERSION_NUMBER,
3958 CURRENCY_CODE_TC,
3959 ORG_ID,
3960 BUDGET_AMOUNT_TC,
3961 BUDGET_AMOUNT_FC,
3962 AVAILABLE_AMOUNT,
3963 TRANSFERED_IN_AMT,
3964 TRANSFERED_OUT_AMT,
3965 PLANNED_AMT,
3966 COMMITTED_AMT,
3967 EARNED_AMT,
3968 PAID_AMT,
3969 RECAL_COMMITTED,
3970 ROLLUP_ORIGINAL_BUDGET,
3971 ROLLUP_TRANSFERED_IN_AMT,
3972 ROLLUP_TRANSFERED_OUT_AMT,
3973 ROLLUP_HOLDBACK_AMT,
3974 ROLLUP_PLANNED_AMT,
3975 ROLLUP_COMMITTED_AMT,
3976 ROLLUP_RECAL_COMMITTED,
3977 ROLLUP_EARNED_AMT,
3978 ROLLUP_PAID_AMT,
3979 UTILIZED_AMT,
3980 ROLLUP_UTILIZED_AMT
3981 FROM OZF_FUNDS_ALL_B
3982 WHERE FUND_ID = p_fund_id;*/
3983
3984 BEGIN
3985
3986 SAVEPOINT UPDATE_OZF_FUNDS_ALL_B;
3987
3988 IF G_DEBUG THEN
3989 OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
3990 OZF_UTILITY_PVT.debug_message('px_ozf_funds_new_tbl.COUNT: ' || px_ozf_funds_new_tbl.COUNT);
3991 END IF;
3992
3993 -- For TPA Parallel Execution ER - 9614703 (+)
3994
3995 insrt_flag := 'F';
3996 insrt_count := 0;
3997
3998 IF px_ozf_funds_new_tbl.COUNT > 0 THEN
3999
4000 insrt_count := 0;
4001
4002 FOR i IN 1 .. px_ozf_funds_new_tbl.COUNT
4003 LOOP
4004 l_fund_id := px_ozf_funds_new_tbl(i).FUND_ID;
4005
4006 IF G_DEBUG THEN
4007 OZF_UTILITY_PVT.debug_message('Fund ID: ' || px_ozf_funds_new_tbl(i).FUND_ID);
4008 OZF_UTILITY_PVT.debug_message('Earned Amount: ' || px_ozf_funds_new_tbl(i).EARNED_AMT);
4009 END IF;
4010
4011 /*Bug#9614703
4012 All fund records will get inserted into OZF_FUNDS_ALL_B_INT table and processed to
4013 main table via concurrent prgm 'OZF: Update Budgets from Interface Tables'*/
4014
4015 /*OPEN c_mc_record(l_fund_id);
4016 FETCH c_mc_record INTO l_mc_record_id, l_mc_obj_number;
4017 CLOSE c_mc_record;
4018
4019 OPEN funds_rollup_csr(l_fund_id);
4020 FETCH funds_rollup_csr INTO l_original_budget, l_object_version_number, l_currency_code_tc,
4021 l_org_id,l_budget_amount_tc,
4022 l_budget_amount_fc, l_available_amount, l_transfered_in_amt, l_transfered_out_amt, l_planned_amt,
4023 l_committed_amt, l_earned_amt,
4024 l_paid_amt, l_recal_committed, l_rollup_original_budget, l_rollup_transfered_in_amt,
4025 l_rollup_transfered_out_amt,
4026 l_rollup_holdback_amt, l_rollup_planned_amt, l_rollup_committed_amt, l_rollup_recal_committed,
4027 l_rollup_earned_amt,
4028 l_rollup_paid_amt, l_utilized_amt, l_rollup_utilized_amt;
4029 CLOSE funds_rollup_csr;
4030
4031 -- Updating the main table OZF_FUNDS_ALL_B using the new record type for chargeback flow only
4032
4033 IF G_DEBUG THEN
4034 OZF_UTILITY_PVT.debug_message('updating OZF_FUNDS_ALL_B');
4035 END IF;
4036
4037 UPDATE OZF_FUNDS_ALL_B
4038 SET
4039 BUDGET_AMOUNT_TC = NVL(BUDGET_AMOUNT_TC,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_TC,0),
4040 BUDGET_AMOUNT_FC = NVL(BUDGET_AMOUNT_FC,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_FC,0),
4041 AVAILABLE_AMOUNT = NVL(AVAILABLE_AMOUNT,0) + NVL(px_ozf_funds_new_tbl(i).AVAILABLE_AMOUNT,0),
4042 TRANSFERED_IN_AMT = NVL(TRANSFERED_IN_AMT,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_IN_AMT,0),
4043 TRANSFERED_OUT_AMT = NVL(TRANSFERED_OUT_AMT,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_OUT_AMT,0),
4044 PLANNED_AMT = NVL(PLANNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).PLANNED_AMT,0),
4045 COMMITTED_AMT = NVL(COMMITTED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).COMMITTED_AMT,0),
4046 EARNED_AMT = NVL(EARNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).EARNED_AMT,0),
4047 PAID_AMT = NVL(PAID_AMT,0) + NVL(px_ozf_funds_new_tbl(i).PAID_AMT,0),
4048 RECAL_COMMITTED = NVL(RECAL_COMMITTED,0) + NVL(px_ozf_funds_new_tbl(i).RECAL_COMMITTED,0),
4049 ROLLUP_ORIGINAL_BUDGET = NVL(ROLLUP_ORIGINAL_BUDGET,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_ORIGINAL_BUDGET,0),
4050 ROLLUP_TRANSFERED_IN_AMT = NVL(ROLLUP_TRANSFERED_IN_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_IN_AMT,0),
4051 ROLLUP_TRANSFERED_OUT_AMT = NVL(ROLLUP_TRANSFERED_OUT_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_OUT_AMT,0),
4052 ROLLUP_HOLDBACK_AMT = NVL(ROLLUP_HOLDBACK_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_HOLDBACK_AMT,0),
4053 ROLLUP_PLANNED_AMT = NVL(ROLLUP_PLANNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PLANNED_AMT,0),
4054 ROLLUP_COMMITTED_AMT = NVL(ROLLUP_COMMITTED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_COMMITTED_AMT,0),
4055 ROLLUP_RECAL_COMMITTED = NVL(ROLLUP_RECAL_COMMITTED,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_RECAL_COMMITTED,0),
4056 ROLLUP_EARNED_AMT = NVL(ROLLUP_EARNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_EARNED_AMT,0),
4057 ROLLUP_PAID_AMT = NVL(ROLLUP_PAID_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PAID_AMT,0),
4058 UTILIZED_AMT = NVL(UTILIZED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0),
4059 ROLLUP_UTILIZED_AMT = NVL(ROLLUP_UTILIZED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_UTILIZED_AMT,0),
4060 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
4061 WHERE FUND_ID = px_ozf_funds_new_tbl(i).FUND_ID
4062 AND OBJECT_VERSION_NUMBER = l_object_version_number;
4063
4064
4065 IF (SQL%NOTFOUND) THEN
4066 insrt_flag := 'Y';
4067 GOTO START_INSERTING;
4068 END IF;
4069
4070 -- Updating the fund tl tables.
4071 UPDATE ozf_funds_all_tl
4072 SET last_update_date = SYSDATE
4073 ,last_updated_by = NVL(fnd_global.user_id, -1)
4074 ,last_update_login = NVL(fnd_global.conc_login_id, -1)
4075 ,request_id = fnd_global.conc_request_id
4076 ,program_application_id = fnd_global.prog_appl_id
4077 ,program_id = fnd_global.conc_program_id
4078 ,program_update_date = SYSDATE
4079 WHERE fund_id = l_fund_id
4080 AND USERENV('LANG') IN(language, source_lang);
4081
4082 IF (SQL%NOTFOUND) THEN
4083 insrt_flag := 'Y';
4084 GOTO START_INSERTING;
4085 END IF;
4086
4087 -- Updating the roll up amount of the parent funds.
4088 l_fund_rec.fund_id := l_fund_id;
4089 l_fund_rec.budget_amount_tc := NVL(l_budget_amount_tc,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_TC,0);
4090 l_fund_rec.budget_amount_fc := NVL(l_budget_amount_fc,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_FC,0);
4091 l_fund_rec.available_amount := NVL(l_available_amount,0) + NVL(px_ozf_funds_new_tbl(i).AVAILABLE_AMOUNT,0);
4092 l_fund_rec.transfered_in_amt := NVL(l_transfered_in_amt,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_IN_AMT,0);
4093 l_fund_rec.transfered_out_amt := NVL(l_transfered_out_amt,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_OUT_AMT,0);
4094 l_fund_rec.planned_amt := NVL(l_planned_amt,0) + NVL(px_ozf_funds_new_tbl(i).PLANNED_AMT,0);
4095 l_fund_rec.committed_amt := NVL(l_committed_amt,0) + NVL(px_ozf_funds_new_tbl(i).COMMITTED_AMT,0);
4096 l_fund_rec.earned_amt := NVL(l_earned_amt,0) + NVL(px_ozf_funds_new_tbl(i).EARNED_AMT,0);
4097 l_fund_rec.paid_amt := NVL(l_paid_amt,0) + NVL(px_ozf_funds_new_tbl(i).PAID_AMT,0);
4098 l_fund_rec.utilized_amt := NVL(l_utilized_amt,0) + NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0);
4099 l_fund_rec.recal_committed := NVL(l_recal_committed,0) + NVL(px_ozf_funds_new_tbl(i).RECAL_COMMITTED,0);
4100 l_fund_rec.rollup_original_budget := NVL(l_rollup_original_budget,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_ORIGINAL_BUDGET,0);
4101 l_fund_rec.rollup_transfered_in_amt := NVL(l_rollup_transfered_in_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_IN_AMT,0);
4102 l_fund_rec.rollup_transfered_out_amt := NVL(l_rollup_transfered_out_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_OUT_AMT,0);
4103 l_fund_rec.rollup_holdback_amt := NVL(l_rollup_holdback_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_HOLDBACK_AMT,0);
4104 l_fund_rec.rollup_planned_amt := NVL(l_rollup_planned_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PLANNED_AMT,0);
4105 l_fund_rec.rollup_committed_amt := NVL(l_rollup_committed_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_COMMITTED_AMT,0);
4106 l_fund_rec.rollup_recal_committed := NVL(l_rollup_recal_committed,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_RECAL_COMMITTED,0);
4107 l_fund_rec.rollup_earned_amt := NVL(l_rollup_earned_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_EARNED_AMT,0);
4108 l_fund_rec.rollup_paid_amt := NVL(l_rollup_paid_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PAID_AMT,0);
4109 l_fund_rec.rollup_utilized_amt := NVL(l_rollup_utilized_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_UTILIZED_AMT,0);
4110
4111 IF G_DEBUG THEN
4112 OZF_UTILITY_PVT.debug_message('Updating update_rollup_amount from UPDATE_OZF_FUNDS_ALL_B for batch_id='||p_batch_id);
4113 END IF;
4114
4115 ozf_funds_pvt.update_rollup_amount(
4116 p_api_version => l_api_version
4117 ,p_init_msg_list => fnd_api.g_false
4118 ,p_commit => fnd_api.g_false
4119 ,p_validation_level => fnd_api.g_valid_level_full
4120 ,x_return_status => l_return_status
4121 ,x_msg_count => x_msg_count
4122 ,x_msg_data => x_msg_data
4123 ,p_fund_rec => l_fund_rec
4124 );
4125
4126 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4127 insrt_flag := 'Y';
4128 GOTO START_INSERTING;
4129 END IF;
4130
4131 -- Updating the OZF_MC_TRANSACTIONS table.
4132 OZF_MC_TRANSACTIONS_PVT.init_transaction_rec(x_mc_transactions_rec => l_mc_transaction_rec);
4133
4134 l_mc_transaction_rec.mc_record_id := l_mc_record_id;
4135 l_mc_transaction_rec.object_version_number := l_mc_obj_number;
4136 l_mc_transaction_rec.source_object_name := 'FUND';
4137 l_mc_transaction_rec.source_object_id := l_fund_id;
4138 l_mc_transaction_rec.currency_code := l_currency_code_tc;
4139 l_mc_transaction_rec.amount_column1 := l_original_budget;
4140 l_mc_transaction_rec.amount_column2 := NVL(l_transfered_in_amt,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_IN_AMT,0);
4141 l_mc_transaction_rec.amount_column3 := NVL(l_transfered_out_amt,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_OUT_AMT,0);
4142 l_mc_transaction_rec.amount_column4 := 0;
4143 l_mc_transaction_rec.amount_column5 := NVL(l_planned_amt,0) + NVL(px_ozf_funds_new_tbl(i).PLANNED_AMT,0);
4144 l_mc_transaction_rec.amount_column6 := NVL(l_committed_amt,0) + NVL(px_ozf_funds_new_tbl(i).COMMITTED_AMT,0);
4145 l_mc_transaction_rec.amount_column7 := NVL(l_earned_amt,0) + NVL(px_ozf_funds_new_tbl(i).EARNED_AMT,0);
4146 l_mc_transaction_rec.amount_column8 := NVL(l_paid_amt,0) + NVL(px_ozf_funds_new_tbl(i).PAID_AMT,0);
4147 l_mc_transaction_rec.amount_column9 := NVL(l_utilized_amt,0) + NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0);
4148
4149
4150 IF G_DEBUG THEN
4151 OZF_UTILITY_PVT.debug_message('Updating update_mc_transactions from UPDATE_OZF_FUNDS_ALL_B for batch_id'||p_batch_id);
4152 END IF;
4153
4154 ozf_mc_transactions_pvt.update_mc_transactions(
4155 p_api_version => l_api_version
4156 ,p_init_msg_list => fnd_api.g_false
4157 ,p_commit => fnd_api.g_false
4158 ,p_org_id => l_org_id
4159 ,x_return_status => l_return_status
4160 ,x_msg_count => x_msg_count
4161 ,x_msg_data => x_msg_data
4162 ,p_mc_transactions_rec => l_mc_transaction_rec);
4163
4164 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4165 insrt_flag := 'Y';
4166 GOTO START_INSERTING;
4167 END IF;
4168
4169 <<START_INSERTING>>
4170 IF insrt_flag = 'Y' THEN
4171 */
4172
4173 insrt_count := insrt_count + 1;
4174
4175 INSERT INTO OZF_FUNDS_ALL_B_INT
4176 (
4177 RESALE_BATCH_ID,
4178 BATCH_TYPE,
4179 REQUEST_ID,
4180 FUND_ID,
4181 LAST_UPDATE_DATE,
4182 LAST_UPDATED_BY,
4183 CREATION_DATE,
4184 CREATED_BY,
4185 LAST_UPDATE_LOGIN,
4186 BUDGET_AMOUNT_TC,
4187 BUDGET_AMOUNT_FC,
4188 AVAILABLE_AMOUNT,
4189 TRANSFERED_IN_AMT,
4190 TRANSFERED_OUT_AMT,
4191 PLANNED_AMT,
4192 COMMITTED_AMT,
4193 EARNED_AMT,
4194 PAID_AMT,
4195 RECAL_COMMITTED,
4196 ROLLUP_ORIGINAL_BUDGET,
4197 ROLLUP_TRANSFERED_IN_AMT,
4198 ROLLUP_TRANSFERED_OUT_AMT,
4199 ROLLUP_HOLDBACK_AMT,
4200 ROLLUP_PLANNED_AMT,
4201 ROLLUP_COMMITTED_AMT,
4202 ROLLUP_RECAL_COMMITTED,
4203 ROLLUP_EARNED_AMT,
4204 ROLLUP_PAID_AMT,
4205 UTILIZED_AMT,
4206 ROLLUP_UTILIZED_AMT
4207 ) VALUES
4208 (
4209 p_batch_id,
4210 p_batch_type,
4211 fnd_global.conc_request_id,
4212 px_ozf_funds_new_tbl(i).FUND_ID,
4213 SYSDATE,
4214 NVL(fnd_global.user_id, -1),
4215 SYSDATE,
4216 NVL(fnd_global.user_id, -1),
4217 NVL(fnd_global.user_id, -1),
4218 NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_TC,0),
4219 NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_FC,0),
4220 NVL(px_ozf_funds_new_tbl(i).AVAILABLE_AMOUNT,0),
4221 NVL(px_ozf_funds_new_tbl(i).TRANSFERED_IN_AMT,0),
4222 NVL(px_ozf_funds_new_tbl(i).TRANSFERED_OUT_AMT,0),
4223 NVL(px_ozf_funds_new_tbl(i).PLANNED_AMT,0),
4224 NVL(px_ozf_funds_new_tbl(i).COMMITTED_AMT,0),
4225 NVL(px_ozf_funds_new_tbl(i).EARNED_AMT,0),
4226 NVL(px_ozf_funds_new_tbl(i).PAID_AMT,0),
4227 NVL(px_ozf_funds_new_tbl(i).RECAL_COMMITTED,0),
4228 NVL(px_ozf_funds_new_tbl(i).ROLLUP_ORIGINAL_BUDGET,0),
4229 NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_IN_AMT,0),
4230 NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_OUT_AMT,0),
4231 NVL(px_ozf_funds_new_tbl(i).ROLLUP_HOLDBACK_AMT,0),
4232 NVL(px_ozf_funds_new_tbl(i).ROLLUP_PLANNED_AMT,0),
4233 NVL(px_ozf_funds_new_tbl(i).ROLLUP_COMMITTED_AMT,0),
4234 NVL(px_ozf_funds_new_tbl(i).ROLLUP_RECAL_COMMITTED,0),
4235 NVL(px_ozf_funds_new_tbl(i).ROLLUP_EARNED_AMT,0),
4236 NVL(px_ozf_funds_new_tbl(i).ROLLUP_PAID_AMT,0),
4237 NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0),
4238 NVL(px_ozf_funds_new_tbl(i).ROLLUP_UTILIZED_AMT,0)
4239 );
4240
4241 -- END IF;
4242 END LOOP; -- End of (px_ozf_funds_new_tbl loop)
4243
4244 IF G_DEBUG THEN
4245 ozf_utility_pvt.write_conc_log('insrt_count of OZF_FUNDS_ALL_B_INT: ' || insrt_count);
4246 END IF;
4247
4248
4249 --IF insrt_count > 0 THEN
4250 IF p_batch_type = 'CHARGEBACK' THEN
4251 x_fund_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE;
4252 ELSE
4253 x_fund_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_ACCRUALS;
4254 END IF;
4255 -- ELSE
4256 --x_fund_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
4257 -- END IF;
4258
4259 px_ozf_funds_new_tbl.delete;
4260
4261 END IF; -- End of (px_ozf_funds_new_tbl.COUNT > 0)
4262
4263 -- For TPA Parallel Execution ER - 9614703 (-)
4264
4265 IF G_DEBUG THEN
4266 OZF_UTILITY_PVT.debug_message(l_api_name||': End');
4267 END IF;
4268
4269 EXCEPTION
4270 WHEN error_no_rollback THEN
4271 x_return_status := FND_API.G_RET_STS_ERROR;
4272 FND_MSG_PUB.Count_And_Get (
4273 p_encoded => FND_API.G_FALSE,
4274 p_count => x_msg_count,
4275 p_data => x_msg_data
4276 );
4277 WHEN FND_API.g_exc_error THEN
4278 ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
4279 x_return_status := FND_API.G_RET_STS_ERROR;
4280 FND_MSG_PUB.Count_And_Get (
4281 p_encoded => FND_API.G_FALSE,
4282 p_count => x_msg_count,
4283 p_data => x_msg_data
4284 );
4285 WHEN FND_API.g_exc_unexpected_error THEN
4286 ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
4287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4288 FND_MSG_PUB.Count_And_Get (
4289 p_encoded => FND_API.G_FALSE,
4290 p_count => x_msg_count,
4291 p_data => x_msg_data
4292 );
4293 WHEN OTHERS THEN
4294 ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
4295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4296 IF OZF_UNEXP_ERROR THEN
4297 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4298 END IF;
4299 FND_MSG_PUB.Count_And_Get (
4300 p_encoded => FND_API.G_FALSE,
4301 p_count => x_msg_count,
4302 p_data => x_msg_data
4303 );
4304
4305 END UPDATE_OZF_FUNDS_ALL_B;
4306
4307 ---------------------------------------------------------------------
4308 -- FUNCTION
4309 -- convert_currency
4310 -- DESCRIPTION
4311 -- convert_currency created for Endeca View Purpose
4312 ---------------------------------------------------------------------
4313 FUNCTION convert_currency(
4314 p_from_currency IN VARCHAR2
4315 ,p_to_currency IN VARCHAR2
4316 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
4317 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
4318 ,p_conv_date IN DATE DEFAULT SYSDATE
4319 ,p_from_amount IN NUMBER
4320 )
4321 RETURN NUMBER
4322 IS
4323 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'OZF_CURR_CONVERSION_TYPE';
4324 l_user_rate CONSTANT NUMBER := 1;
4325 -- Currenty not used.
4326 -- this should be a profile
4327 l_max_roll_days CONSTANT NUMBER := -1;
4328 -- Negative so API rolls back to find the last conversion rate.
4329 -- this should be a profile
4330 l_denominator NUMBER; -- Not used in Marketing.
4331 l_numerator NUMBER; -- Not used in Marketing.
4332 l_conversion_type VARCHAR2(30); -- Curr conversion type; see API doc for details.
4333 l_conv_date DATE;
4334 l_conv_amt NUMBER;
4335 BEGIN
4336
4337 -- condition added to pass conversion types
4338 IF p_conv_type = FND_API.G_MISS_CHAR OR p_conv_type IS NULL THEN --Added for bug 7030415
4339 -- Get the currency conversion type from profile option
4340 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
4341 -- Conversion type cannot be null in profile
4342 ELSE
4343 l_conversion_type := p_conv_type;
4344 END IF;
4345
4346 --bug 8532055
4347 IF p_conv_date = FND_API.G_MISS_DATE OR p_conv_date IS NULL THEN
4348 l_conv_date := SYSDATE;
4349 ELSE
4350 l_conv_date := p_conv_date;
4351 END IF;
4352
4353 l_conv_amt := gl_currency_api.convert_closest_amount_sql(
4354 x_from_currency => p_from_currency
4355 ,x_to_currency => p_to_currency
4356 ,x_conversion_date => l_conv_date
4357 ,x_conversion_type => l_conversion_type
4358 ,x_user_rate => l_user_rate
4359 ,x_amount => p_from_amount
4360 ,x_max_roll_days => l_max_roll_days
4361 );
4362
4363 RETURN l_conv_amt;
4364
4365 END convert_currency;
4366
4367 END OZF_Utility_PVT;