1 PACKAGE OZF_Utility_PVT AUTHID CURRENT_USER as
2 /* $Header: ozfvutls.pls 120.11.12020000.4 2013/02/14 10:05:07 nepanda ship $ */
3
4 ------------------------------------------------------------------------------
5 -- HISTORY
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 -- 07-Jul-2000 KHUNG Un-commented for R2
10 -- 13-Jul-2000 choang Added get_resource_id
11 -- 07-Aug-2000 ptendulk Added procedure to write Concurrent Progrozf logs.
12 -- 14-Sep-2000 holiu Added check_status_change, get_default_user_status,
13 -- get_system_status_type, get_system_status_code.
14 -- 03/27/2001 MPANDE MOved 4 Procedures from OZF to OZF
15 -- 04/13/2001 FELIU Modify create_log procedure by adding threshold_id
16 -- and budget_id
17 -- 04/23/2001 FELIU Modify create_log procedure by adding p_transaction_id,
18 -- p_notification_creat_date.
19 -- 05/24/2001 FELIU Modify create_log procedure by adding p_activity_log_id.
20 -- 16-Jun-2001 ptendulk Added check_new_status_change procedure to obsolute
21 -- the old check_status_change api
22 -- 09-Jul-2001 ptendulk Added new function Check_Status_Change
23 -- 13-Aug-2001 slkrishn Added a new function for currency rounding
24 -- 05-Nov-2001 sveerave Added specs for rec and table type of delete dependencies.
25 -- 14-Jan-2001 sveerave Added send_wf_standalone_message procedure, and
26 -- Get_Resource_Role procedures for sending standalone mesages.
27 -- 06-Jun-2002 sveerave Added overloaded check_lookup_exists
28 -- which accepts view_application_id, and query from fnd_lookups
29 -- 19-Dec-2002 mayjain Added get_install_info
30 -- Wed Oct 29 2003:3/44 PM RSSHARMA Added function get_party_name
31 -- Fri Jan 23 2004:3/20 PM RSSHARMA Added the following fuctions
32 -- 1. get_fnd_lookup_meaning
33 -- 2. get_currency_name
34 -- 3. get_media_name
35 -- 17-FEB-2005 feliu added functions
36 -- get_commited_amount
37 -- get_recal_commited_amount
38 -- get_utilized_amount
39 -- get_paid_amount
40 -- get_earned_amount
41 -- 21-Sep-05 SSHIVALI R12: Added a Type
42 -- Mon May 22 2006:5/59 PM rssharma Fixed bug # 5213655.Added getAttributeMethods to get AKAttribute long labels.
43 -- 05-Mar-2010 BKUNJAN ER 9382547 - TM SLA Uptake
44 -- 05-01-2011 muthsubr For TPA Parallel Execution ER Bug#9614703
45 -- 12-Sep-2011 BKUNJAN ER 12764004 - TPM Upgrade ER
46 ------------------------------------------------------------------------------
47
48 g_number CONSTANT NUMBER := 1; -- data type is number
49 g_varchar2 CONSTANT NUMBER := 2; -- data type is varchar2
50 g_ozf_lookups CONSTANT VARCHAR2(12) := 'OZF_LOOKUPS';
51
52 resource_locked EXCEPTION;
53 pragma EXCEPTION_INIT(resource_locked, -54);
54
55 G_RCS_ID CONSTANT VARCHAR2(80) := 'RCS_ID';
56 G_OBJ_ID CONSTANT VARCHAR2(80) := 'OBJECT_ID';
57 G_OBJ_TYPE CONSTANT VARCHAR2(80) := 'OBJECT_TYPE';
58 G_ERRNO CONSTANT VARCHAR2(80) := 'ERROR_NUMBER';
59 G_REASON CONSTANT VARCHAR2(80) := 'REASON';
60 G_METHOD_NAME CONSTANT VARCHAR2(80) := 'METHOD_NAME';
61 G_LABEL CONSTANT VARCHAR2(80) := 'LABEL';
62 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'MODULE_NAME';
63
64 -- For Bug#8867381
65 G_BATCH_PENDING_CLOSE CONSTANT VARCHAR2(30) := 'PENDING_CLOSE';
66
67 -- For Bug#9614703
68 G_BATCH_PENDING_ACCRUALS CONSTANT VARCHAR2(30) := 'PENDING_ACCRUALS';
69
70 --Multiorg Changes
71 TYPE operating_units_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
72 -- For TPA Parallel Execution ER - 9614703 (+)
73
74 --//12764004 -TPM Integration ER
75 TYPE ozf_msg_type is RECORD
76 (
77 msg_text VARCHAR2(254)
78 );
79
80 TYPE ozf_msg_tbl_type IS TABLE OF ozf_msg_type
81 INDEX BY BINARY_INTEGER;
82
83
84 TYPE ozf_funds_all_b_rectype IS
85 RECORD (
86 RESALE_BATCH_ID NUMBER,
87 FUND_ID NUMBER,
88 BUDGET_AMOUNT_TC NUMBER,
89 BUDGET_AMOUNT_FC NUMBER,
90 AVAILABLE_AMOUNT NUMBER,
91 TRANSFERED_IN_AMT NUMBER,
92 TRANSFERED_OUT_AMT NUMBER,
93 PLANNED_AMT NUMBER,
94 COMMITTED_AMT NUMBER,
95 EARNED_AMT NUMBER,
96 PAID_AMT NUMBER,
97 RECAL_COMMITTED NUMBER,
98 ROLLUP_ORIGINAL_BUDGET NUMBER,
99 ROLLUP_TRANSFERED_IN_AMT NUMBER,
100 ROLLUP_TRANSFERED_OUT_AMT NUMBER,
101 ROLLUP_HOLDBACK_AMT NUMBER,
102 ROLLUP_PLANNED_AMT NUMBER,
103 ROLLUP_COMMITTED_AMT NUMBER,
104 ROLLUP_RECAL_COMMITTED NUMBER,
105 ROLLUP_EARNED_AMT NUMBER,
106 ROLLUP_PAID_AMT NUMBER,
107 UTILIZED_AMT NUMBER,
108 ROLLUP_UTILIZED_AMT NUMBER,
109 REQUEST_ID NUMBER,
110 BATCH_TYPE VARCHAR2(30)
111 );
112
113 TYPE ozf_funds_table IS TABLE OF ozf_funds_all_b_rectype
114 INDEX BY BINARY_INTEGER;
115
116 TYPE ozf_act_budgets_rectype IS
117 RECORD (
118 RESALE_BATCH_ID NUMBER,
119 ACTIVITY_BUDGET_ID NUMBER,
120 REQUEST_AMOUNT NUMBER,
121 APPROVED_AMOUNT NUMBER,
122 APPROVED_ORIGINAL_AMOUNT NUMBER,
123 APPROVED_AMOUNT_FC NUMBER,
124 PARENT_SRC_APPRVD_AMT NUMBER,
125 SRC_CURR_REQUEST_AMT NUMBER,
126 REQUEST_ID NUMBER,
127 BATCH_TYPE VARCHAR2(30)
128 );
129 TYPE ozf_act_budgets_table IS TABLE OF ozf_act_budgets_rectype
130 INDEX BY BINARY_INTEGER;
131
132 -- For TPA Parallel Execution ER - 9614703 (-)
133
134 --======================================================================
135 -- PROCEDURE
136 -- debug_message
137 --
138 -- PURPOSE
139 -- Writes the message to the log file for the spec'd level and module
140 -- if logging is enabled for this level and module
141 --
142 -- HISTORY
143 -- 01-Oct-2003 huili Create.
144 --======================================================================
145 PROCEDURE debug_message (p_log_level IN NUMBER,
146 p_module_name IN VARCHAR2,
147 p_text IN VARCHAR2);
148
149 --======================================================================
150 -- PROCEDURE
151 -- log_message
152 --
153 -- PURPOSE
154 -- Writes a message to the log file if this level and module is enabled
155 -- The message gets set previously with FND_MESSAGE.SET_NAME,
156 -- SET_TOKEN, etc.
157 -- The message is popped off the message dictionary stack, if POP_MESSAGE
158 -- is TRUE. Pass FALSE for POP_MESSAGE if the message will also be
159 -- displayed to the user later.
160 -- Example usage:
161 -- FND_MESSAGE.SET_NAME(...); -- Set message
162 -- FND_MESSAGE.SET_TOKEN(...); -- Set token in message
163 -- AMS_Utility_PVT.log_message(..., FALSE); -- Log message
164 --
165 -- HISTORY
166 -- 01-Oct-2003 huili Create.
167 --======================================================================
168
169 PROCEDURE log_message(p_log_level IN NUMBER,
170 p_module_name IN VARCHAR2,
171 p_RCS_ID IN VARCHAR2 := NULL,
172 p_pop_message IN BOOLEAN DEFAULT NULL);
173
174 --======================================================================
175 -- FUNCTION
176 -- logging_enabled
177 --
178 -- PURPOSE
179 -- Return whether logging is enabled for a particular level
180 --
181 -- HISTORY
182 -- 03-Oct-2003 huili Create.
183 --======================================================================
184 FUNCTION logging_enabled (p_log_level IN NUMBER)
185 RETURN BOOLEAN;
186
187
188 ---------------------------------------------------------------------
189 -- FUNCTION
190 -- check_fk_exists
191 --
192 -- PURPOSE
193 -- This function checks if a foreign key is valid.
194 --
195 -- NOTES
196 -- 1. It will return FND_API.g_true/g_false.
197 -- 2. Exception encountered will be raised to the caller.
198 -- 3. p_pk_data_type can be OZF_Global_PVT.g_number/g_varchar2.
199 -- 4. Please don't put 'AND' at the beginning of your additional
200 -- where clause.
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; -- FND_API.g_true/g_false
210
211
212 ---------------------------------------------------------------------
213 -- FUNCTION
214 -- check_lookup_exists
215 --
216 -- PURPOSE
217 -- This function checks if a lookup_code is valid.
218
219 ---------------------------------------------------------------------
220 FUNCTION check_lookup_exists(
221 p_lookup_table_name IN VARCHAR2 := g_ozf_lookups,
222 p_lookup_type IN VARCHAR2,
223 p_lookup_code IN VARCHAR2
224 )
225 Return VARCHAR2; -- FND_API.g_true/g_false
226
227 ---------------------------------------------------------------------
228 -- FUNCTION
229 -- check_lookup_exists
230 --
231 -- PURPOSE
232 -- This function checks if a lookup_code is valid from fnd_lookups when
233 -- view_application_id is passed in.
234 ---------------------------------------------------------------------
235 FUNCTION check_lookup_exists(
236 p_lookup_type IN VARCHAR2,
237 p_lookup_code IN VARCHAR2,
238 p_view_application_id IN NUMBER
239 )
240 Return VARCHAR2; -- FND_API.g_true/g_false
241
242
243 ---------------------------------------------------------------------
244 -- FUNCTION
245 -- check_uniqueness
246 --
247 -- PURPOSE
248 -- This function is to check the uniqueness of the keys.
249 -- In order to make this function more flexible, you need to
250 -- pass in where clause of your unique key's check.
251 ---------------------------------------------------------------------
252 FUNCTION check_uniqueness(
253 p_table_name IN VARCHAR2,
254 p_where_clause IN VARCHAR2
255 )
256 RETURN VARCHAR2; -- FND_API.g_true/g_false
257
258
259 ---------------------------------------------------------------------
260 -- FUNCTION
261 -- is_Y_or_N
262 --
263 -- PURPOSE
264 -- Return FND_API.g_true if p_value='Y' or p_value='N';
265 -- return FND_API.g_flase otherwise.
266 ---------------------------------------------------------------------
267 FUNCTION is_Y_or_N(
268 p_value IN VARCHAR2
269 )
270 RETURN VARCHAR2; -- FND_API.g_true/g_false
271
272
273 ---------------------------------------------------------------------
274 -- PROCEDURE
275 -- get_qual_table_name_and_pk
276 --
277 -- PURPOSE
278 -- This procedure will return the table name and the primary key
279 -- field which are associated with each System Qualifer values.
280 -- The will allow for easier FK validation.
281 ---------------------------------------------------------------------
282 PROCEDURE get_qual_table_name_and_pk(
283 p_sys_qual IN VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_table_name OUT NOCOPY VARCHAR2,
286 x_pk_name OUT NOCOPY VARCHAR2
287 );
288
289
290 ---------------------------------------------------------------------
291 -- PROCEDURE
292 -- debug_message
293 --
294 -- PURPOSE
295 -- This procedure will check the message level and try to add a
296 -- debug message into the message table of FND_MSG_API package.
297 -- Note that this debug message won't be translated.
298 ---------------------------------------------------------------------
299 PROCEDURE debug_message(
300 p_message_text IN VARCHAR2,
301 p_message_level IN NUMBER := NULL
302 );
303
304
305 ---------------------------------------------------------------------
306 -- PROCEDURE
307 -- error_message
308 --
309 -- PURPOSE
310 -- Add an error message to the message_list for an expected error.
311 ---------------------------------------------------------------------
312 PROCEDURE error_message(
313 p_message_name VARCHAR2,
314 p_token_name VARCHAR2 := NULL,
315 P_token_value VARCHAR2 := NULL
316 );
317
318
319 ---------------------------------------------------------------------
320 -- PROCEDURE
321 -- display_messages
322 --
323 -- PURPOSE
324 -- This procedure will display all messages in the message list
325 -- using DBMS_OUTPUT.put_line( ) .
326 ---------------------------------------------------------------------
327 PROCEDURE display_messages;
328
329
330 ---------------------------------------------------------------
331 -- PROCEDURE
332 -- create_log
333 --
334 -- PURPOSE
335 -- This procedure is to create a row in ams_act_logs table
336 -- to record the log
337 ---------------------------------------------------------------
338 PROCEDURE create_log(
339 x_return_status OUT NOCOPY VARCHAR2,
340 p_arc_log_used_by IN VARCHAR2,
341 p_log_used_by_id IN VARCHAR2,
342 p_msg_data IN VARCHAR2,
343 p_msg_level IN NUMBER DEFAULT NULL,
344 p_msg_type IN VARCHAR2 DEFAULT NULL,
345 p_desc IN VARCHAR2 DEFAULT NULL,
346 p_budget_id IN NUMBER DEFAULT NULL,
347 p_threshold_id IN NUMBER DEFAULT NULL,
348 p_transaction_id IN NUMBER DEFAULT NULL,
349 p_notification_creat_date IN DATE DEFAULT NULL,
350 p_activity_log_id IN NUMBER DEFAULT NULL
351 );
352
353
354 ---------------------------------------------------------------------
355 -- FUNCTION
356 -- get_object_name
357 --
358 -- PURPOSE
359 -- Return the name of the object identified by the four-letter
360 -- p_sys_arc_qualifier and the p_object_id.
361 ---------------------------------------------------------------------
362 FUNCTION get_object_name(
363 p_sys_arc_qualifier IN VARCHAR2,
364 p_object_id IN NUMBER
365 )
366 RETURN VARCHAR2;
367 PRAGMA RESTRICT_REFERENCES(get_object_name, WNDS, WNPS, RNPS);
368
372 -- Convert_Currency
369
370 ---------------------------------------------------------------------
371 -- PROCEDURE
373 -- PURPOSE
374 -- Call the GL API to convert one currency to another, which
375 -- has a different currency code.
376 ---------------------------------------------------------------------
377 PROCEDURE Convert_Currency (
378 x_return_status OUT NOCOPY VARCHAR2,
379 p_from_currency IN VARCHAR2,
380 p_to_currency IN VARCHAR2,
381 p_conv_date IN DATE DEFAULT SYSDATE,
382 p_from_amount IN NUMBER,
383 x_to_amount OUT NOCOPY NUMBER
384 );
385
386 ---------------------------------------------------------------------
387 -- PROCEDURE
388 -- get_lookup_meaning
389 --
390 -- PURPOSE
391 -- This procedure will return the meaning from ozf_lookups if
392 -- you pass the right lookup_type and lookup_code
393 ---------------------------------------------------------------------
394 PROCEDURE get_lookup_meaning(
395 p_lookup_type IN VARCHAR2,
396 p_lookup_code IN VARCHAR2,
397 x_return_status OUT NOCOPY VARCHAR2,
398 x_meaning OUT NOCOPY VARCHAR2
399 );
400
401 ---------------------------------------------------------------------
402 -- FUNCTION
403 -- get_lookup_meaning
404 -- DESCRIPTION
405 -- Given a lookup_type and lookup_code, return the meaning from
406 -- OZF_LOOKUPS.
407 ---------------------------------------------------------------------
408 FUNCTION get_lookup_meaning (
409 p_lookup_type IN VARCHAR2,
410 p_lookup_code IN VARCHAR2
411 )
412 RETURN VARCHAR2;
413 PRAGMA RESTRICT_REFERENCES(get_lookup_meaning, WNDS);
414
415 ---------------------------------------------------------------------
416 -- PROCEDURE
417 -- get_System_Timezone
418 --
419 -- PURPOSE
420 -- This procedure will return the timezone from the System Timezone profile option
421 -- HISTORY created 04/24/2000 sugupta
422 ---------------------------------------------------------------------
423 PROCEDURE get_System_Timezone(
424 x_return_status OUT NOCOPY VARCHAR2,
425 x_sys_time_id OUT NOCOPY NUMBER,
426 x_sys_time_name OUT NOCOPY VARCHAR2
427 );
428 ---------------------------------------------------------------------
429 -- PROCEDURE
430 -- get_User_Timezone
431 --
432 -- PURPOSE
433 -- This procedure will return the timezone from the User Timezone profile option
434 -- HISTORY created 04/24/2000 sugupta
435 ---------------------------------------------------------------------
436 PROCEDURE get_User_Timezone(
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_user_time_id OUT NOCOPY NUMBER,
439 x_user_time_name OUT NOCOPY VARCHAR2
440 );
441 ---------------------------------------------------------------------
442 -- PROCEDURE
443 -- Convert_Timezone
444 --
445 -- PURPOSE
446 -- This procedure will take the user timezone and the input time, depending on the parameter
447 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
448 -- HISTORY
449 -- 04/24/2000 sugupta created
450 -- 04/26/2000 ptendulk Modified Added a parameter which will tell
451 -- which timezone to convert time into
452 -- If the convert type is 'SYS' then input time will be
453 -- converted into system timezone else it will be
454 -- converted to user timezone sent.
455 -- 06-sep-2001 choang set default for p_user_tz_id for bug 1857131.
456 ---------------------------------------------------------------------
457 PROCEDURE Convert_Timezone(
458 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462
463 p_user_tz_id IN NUMBER := null,
464 p_in_time IN DATE, -- required
465 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
466
467 x_out_time OUT NOCOPY DATE
468 );
469
470
471 ---------------------------------------------------------------------
472 -- FUNCTION
473 -- get_resource_name
474 -- DESCRIPTION
475 -- Given a resource ID, returns the full_name from
476 -- JTF_RS_RES_EMP_VL.
477 ---------------------------------------------------------------------
478 FUNCTION get_resource_name (
479 p_resource_id IN VARCHAR2
480 )
481 RETURN VARCHAR2;
482 PRAGMA RESTRICT_REFERENCES(get_resource_name, WNDS);
483
484
485 ---------------------------------------------------------------------
486 -- PROCEDURE
487 -- get_source_code
488 --
489 -- PURPOSE
490 -- Returns The Source Code for The Activity Type specified by the
491 -- p_activity_type parameter.
492 ---------------------------------------------------------------------
493 PROCEDURE get_source_code(
494 p_activity_type IN VARCHAR2,
495 p_activity_id IN NUMBER,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_source_code OUT NOCOPY VARCHAR2,
498 x_source_id OUT NOCOPY NUMBER
499 );
500
501 -----------------------------------------------------------------------
502 -- FUNCTION
503 -- is_in_my_division
504 --
505 -- PURPOSE
506 -- Check if the object is running within the same division
507 -- as the specified country.
508 -- -> 'Y' : the object is within my division
509 -- -> 'N' : the object is not within my division
510 --
511 -- PARAMETERS
512 -- p_object_id: (10001)
513 -- p_object_type: ('CAMP')
517 p_object_type IN VARCHAR2,
514 -- p_country_id: (location_hierarchy_id of the country)
515 -----------------------------------------------------------------------
516 FUNCTION is_in_my_division(
518 p_object_id IN NUMBER,
519 p_country_id IN NUMBER
520 )
521 RETURN VARCHAR2;
522 PRAGMA RESTRICT_REFERENCES(is_in_my_division, WNDS);
523
524 ---------------------------------------------------------------------
525 -- FUNCTION
526 -- get_product_name
527 -- DESCRIPTION
528 -- Get the product or product family name.
529 ---------------------------------------------------------------------
530 FUNCTION get_product_name(
531 p_prod_level IN VARCHAR2,
532 p_prod_id IN NUMBER,
533 p_org_id IN NUMBER := NULL
534 )
535 RETURN VARCHAR2;
536 PRAGMA RESTRICT_REFERENCES(get_product_name, WNDS);
537
538
539 ---------------------------------------------------------------------
540 -- FUNCTION
541 -- get_price_list_name
542 -- DESCRIPTION
543 -- Get the price list name for a given price list line.
544 ---------------------------------------------------------------------
545 FUNCTION get_price_list_name(
546 p_price_list_line_id IN NUMBER
547 )
548 RETURN VARCHAR2;
549 PRAGMA RESTRICT_REFERENCES(get_price_list_name, WNDS);
550
551
552 ---------------------------------------------------------------------
553 -- FUNCTION
554 -- get_uom_name
555 -- DESCRIPTION
556 -- Get the uom name for a given uom code.
557 ---------------------------------------------------------------------
558 FUNCTION get_uom_name(
559 p_uom_code IN VARCHAR2
560 )
561 RETURN VARCHAR2;
562 PRAGMA RESTRICT_REFERENCES(get_uom_name, WNDS);
563
564
565 ---------------------------------------------------------------------
566 -- FUNCTION
567 -- get_qp_lookup_meaning
568 -- DESCRIPTION
569 -- Get the meaning of the given lookup code in qp_lookups.
570 ---------------------------------------------------------------------
571 FUNCTION get_qp_lookup_meaning(
572 p_lookup_type IN VARCHAR2,
573 p_lookup_code IN VARCHAR2
574 )
575 RETURN VARCHAR2;
576 PRAGMA RESTRICT_REFERENCES(get_qp_lookup_meaning, WNDS);
577
578 ---------------------------------------------------------------------
579 -- FUNCTION
580 -- get_resource_id
581 -- DESCRIPTION
582 -- Returns resource_id from the JTF Resource module given
583 -- an AOL user_id.
584 -- NOTE
585 -- Calling programs should check if the returned resource_id
586 -- is -1, which indicates an error in resource setup. Usually,
587 -- this means either the user does not have an associated resource
588 -- or the resource import was done before the association was made
589 -- between the user and the employee.
590 ---------------------------------------------------------------------
591 FUNCTION get_resource_id (
592 p_user_id IN NUMBER
593 )
594 RETURN NUMBER;
595 PRAGMA RESTRICT_REFERENCES (get_resource_id, WNDS);
596
597
598 ---------------------------------------------------------------------
599 -- FUNCTION
600 -- Write_Conc_Log
601 -- DESCRIPTION
602 -- Writes the log for Concurrent programs
603 -- History
604 -- 07-Aug-2000 PTENDULK Created
605 -- NOTE
606 -- If the parameter p_text is passed then the value sent will be printed
607 -- as log else the messages in the stack are printed.
608 ---------------------------------------------------------------------
609 PROCEDURE Write_Conc_Log
610 ( p_text IN VARCHAR2 := NULL)
611 ;
612
613
614 -----------------------------------------------------------------------
615 -- FUNCTION
616 -- get_system_status_type
617 --
618 -- PURPOSE
619 -- Return the system_status_type in ams_status_order_rules table
620 -- for an object.
621 -----------------------------------------------------------------------
622 FUNCTION get_system_status_type(
623 p_object IN VARCHAR2
624 )
625 RETURN VARCHAR2;
626
627
628 -----------------------------------------------------------------------
629 -- FUNCTION
630 -- get_system_status_code
631 --
632 -- PURPOSE
633 -- Return the system_status_code based on user_status_id.
634 -----------------------------------------------------------------------
635 FUNCTION get_system_status_code(
636 p_user_status_id IN NUMBER
637 )
638 RETURN VARCHAR2;
639
640
641 -----------------------------------------------------------------------
642 -- FUNCTION
643 -- get_default_user_status
644 --
645 -- PURPOSE
646 -- Return the default user_status_id based on system_status_type
647 -- and system_status_code.
648 -----------------------------------------------------------------------
649 FUNCTION get_default_user_status(
650 p_status_type IN VARCHAR2,
651 p_status_code IN VARCHAR2
652 )
653 RETURN VARCHAR2;
654
655
656 -----------------------------------------------------------------------
657 -- PROCEDURE
658 -- check_status_change
659 --
660 -- PURPOSE
661 -- Check if approval is needed when changing status.
662 -----------------------------------------------------------------------
663 PROCEDURE check_status_change(
664 p_object_type IN VARCHAR2,
665 p_object_id IN NUMBER,
666 p_old_status_id IN NUMBER,
667 p_new_status_id IN NUMBER,
668 x_approval_type OUT NOCOPY VARCHAR2,
669 x_return_status OUT NOCOPY VARCHAR2
670 );
671
672 -----------------------------------------------------------------------
673 -- PROCEDURE
677 -- Check if approval is needed when changing status. This will override the
674 -- check_new_status_change
675 --
676 -- PURPOSE
678 -- previous procedure as the object attribute table is obsoleted now.
679 --
680 -- History
681 -- 16-Jun-2001 ptendulk Created to replace the old check_status_change
682 --
683 -----------------------------------------------------------------------
684 PROCEDURE Check_New_Status_Change(
685 p_object_type IN VARCHAR2,
686 p_object_id IN NUMBER,
687 p_old_status_id IN NUMBER,
688 p_new_status_id IN NUMBER,
689 p_custom_setup_id IN NUMBER,
690 x_approval_type OUT NOCOPY VARCHAR2,
691 x_return_status OUT NOCOPY VARCHAR2
692 );
693
694 ---------------------------------------------------------------------
695 -- PROCEDURE
696 -- Convert_Currency
697 -- NOTE
698 -- Moved from OZF
699 ---------------------------------------------------------------------
700
701 PROCEDURE convert_currency(
702 p_set_of_books_id IN NUMBER
703 ,p_from_currency IN VARCHAR2
704 ,p_conversion_date IN DATE
705 ,p_conversion_type IN VARCHAR2
706 ,p_conversion_rate IN NUMBER
707 ,p_amount IN NUMBER
708 ,x_return_status OUT NOCOPY VARCHAR2
709 ,x_acc_amount OUT NOCOPY NUMBER
710 ,x_rate OUT NOCOPY NUMBER);
711 ---------------------------------------------------------------------
712 -- PROCEDURE
713 -- get_code_combinations
714 --
715 -- PURPOSE
716 -- get code_combination concacnenated segments and ids
717 -- 20-Sep-2000 slkrishn Created
718 -- NOTE
719 -- Moved from OZF
720 ---------------------------------------------------------------------
721 FUNCTION get_code_combinations(
722 p_code_combination_id IN NUMBER
723 ,p_chart_of_accounts_id IN NUMBER)
724 RETURN VARCHAR2;
725
726
727 ---------------------------------------------------------------------
728 -- PROCEDURE
729 -- Convert_functional_Curr
730 -- NOTE
731 --This procedures takes in amount and converts it to the functional currency and returns
732 --the converted amount,exchange_rate,set_of_book_id,f-nctional_currency_code,exchange_rate_date
733
734 -- HISTORY
735 -- 20-Jul-2000 mpande Created.
736 -- 02/23/2001 MPAnde Updated for getting org id query
737 -- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) - add org_id, default to null
738 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
739 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
740 -- x_Set_of_books_id OUT NUMBER,
741 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R' - We only do it for primary ('P' because we donot supprot MRC)
742 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
743 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,-- comes from a OZF profile or what ever is passed
744 -- x_EXCHANGE_RATE_DATE OUT DATE, -- could come from a OZF profile but right now is sysdate
745 -- x_EXCHANGE_RATE OUT VARCHAR2,
746 -- x_return_status OUT VARCHAR2
747 -- The following is the rule in the GL API
748 -- If x_conversion_type = 'User', and the relationship between the
749 -- two currencies is not fixed, x_user_rate will be used as the
750 -- conversion rate to convert the amount
751 -- else no_user_rate is required
752 -- NOTE
753 -- Moved from OZF
754 ---------------------------------------------------------------------
755
756 PROCEDURE calculate_functional_curr(
757 p_from_amount IN NUMBER
758 ,p_conv_date IN DATE DEFAULT SYSDATE
759 ,p_tc_currency_code IN VARCHAR2
760 ,p_org_id IN NUMBER DEFAULT NULL
761 ,x_to_amount OUT NOCOPY NUMBER
762 ,x_set_of_books_id OUT NOCOPY NUMBER
763 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
764 ,x_fc_currency_code OUT NOCOPY VARCHAR2
765 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
766 ,x_exchange_rate IN OUT NOCOPY NUMBER
767 ,x_return_status OUT NOCOPY VARCHAR2);
768
769
770 ---------------------------------------------------------------------
771 -- PROCEDURE
772 -- calculate_functional_currency
773 -- NOTE
774 -- This procedures takes in amount and converts it to the functional currency
775 -- and returns the converted amount,exchange_rate,ledger_id,
776 -- functional_currency_code,exchange_rate_date
777
778 -- HISTORY
779 -- 29-SEP-2005 kdass Created.
780 ---------------------------------------------------------------------
781 PROCEDURE calculate_functional_currency(
782 p_from_amount IN NUMBER
783 ,p_conv_date IN DATE DEFAULT SYSDATE
784 ,p_tc_currency_code IN VARCHAR2
785 ,p_ledger_id IN NUMBER DEFAULT NULL
786 ,x_to_amount OUT NOCOPY NUMBER
787 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
788 ,x_fc_currency_code OUT NOCOPY VARCHAR2
789 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
790 ,x_exchange_rate IN OUT NOCOPY NUMBER
791 ,x_return_status OUT NOCOPY VARCHAR2);
792
793
794 ---------------------------------------------------------------------
795 -- PROCEDURE
796 -- Convert_Currency
797 -- NOTE
798
799 -- HISTORY
800 -- 20-Jul-2000 mpande Created.
801 --parameter p_from_currency IN VARCHAR2,
802 -- p_to_currency IN VARCHAR2,
803 -- p_conv_date IN DATE DEFAULT SYSDATE,
804 -- p_from_amount IN NUMBER,
805 -- x_to_amount OUT NUMBER
806 -- If x_conversion_type = 'User', and the relationship between the
807 -- two currencies is not fixed, x_user_rate will be used as the
811 -- 02/23/2001 MPAnde Updated for getting org id query
808 -- conversion rate to convert the amount
809 -- else no_user_rate is required
810
812 -- 04/07/2001 slkrishn Added p_conv_type and p_conv_rate with defaults
813 ---------------------------------------------------------------------
814 PROCEDURE convert_currency(
815 p_from_currency IN VARCHAR2
816 ,p_to_currency IN VARCHAR2
817 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
818 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
819 ,p_conv_date IN DATE DEFAULT SYSDATE
820 ,p_from_amount IN NUMBER
821 ,x_return_status OUT NOCOPY VARCHAR2
822 ,x_to_amount OUT NOCOPY NUMBER
823 ,x_rate OUT NOCOPY NUMBER);
824
825 /*============================================================================*/
826 -- Start of Comments
827 -- NAME
828 -- Get_Resource_Role
829 --
830 -- PURPOSE
831 -- This Procedure will be return the workflow user role for
832 -- the resourceid sent
833 -- Called By
834 -- NOTES
835 -- End of Comments
836
837 /*============================================================================*/
838
839 PROCEDURE Get_Resource_Role
840 ( p_resource_id IN NUMBER,
841 x_role_name OUT NOCOPY VARCHAR2,
842 x_role_display_name OUT NOCOPY VARCHAR2 ,
843 x_return_status OUT NOCOPY VARCHAR2
844 );
845
846 --======================================================================
847 -- Procedure Name: send_wf_standalone_message
848 -- Type : Generic utility
849 -- Pre-Req :
850 -- Notes:
851 -- Common utility to send standalone message without initiating
852 -- process using workflow.
853 -- Parameters:
854 -- IN:
855 -- p_item_type IN VARCHAR2 Required Default = 'MAPGUTIL'
856 -- item type for the workflow utility.
857 -- p_message_name IN VARCHAR2 Required Default = 'GEN_STDLN_MESG'
858 -- Internal name for standalone message name
859 -- p_subject IN VARCHAR2 Required
860 -- Subject for the message
861 -- p_body IN VARCHAR2 Optional
862 -- Body for the message
863 -- p_send_to_role_name IN VARCHAR2 Optional
864 -- Role name to whom message is to be sent.
865 -- Instead of this, one can send even p_send_to_res_id
866 -- p_send_to_res_id IN NUMBER Optional
867 -- Resource Id that will be used to get role name from WF_DIRECTORY.
868 -- This is required if role name is not passed.
869
870 -- OUT:
871 -- x_notif_id OUT NUMBER
872 -- Notification Id created that is being sent to recipient.
873 -- x_return_status OUT VARCHAR2
874 -- Return status. If it is error, messages will be put in mesg pub.
875 -- History:
876 -- 11-Jan-2002 sveerave Created.
877 --======================================================================
878
879 PROCEDURE send_wf_standalone_message(
880 p_item_type IN VARCHAR2 := 'MAPGUTIL'
881 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
882 ,p_subject IN VARCHAR2
883 ,p_body IN VARCHAR2 := NULL
884 ,p_send_to_role_name IN VARCHAR2 := NULL
885 ,p_send_to_res_id IN NUMBER := NULL
886 ,x_notif_id OUT NOCOPY NUMBER
887 ,x_return_status OUT NOCOPY VARCHAR2
888 );
889
890 --======================================================================
891 -- FUNCTION
892 -- Check_Status_Change
893 --
894 -- PURPOSE
895 -- Created to check if the status change is valid or not.
896 -- Returns FND_API.G_TRUE if it is valid status change
897 -- or will return FND_API.G_FALSE
898 --
899 -- HISTORY
900 -- 09-Jul-2001 ptendulk Create.
901 --======================================================================
902 FUNCTION Check_Status_Change(
903 p_status_type IN VARCHAR2,
904 p_current_status IN VARCHAR2,
905 p_next_status IN VARCHAR2
906 )
907 RETURN VARCHAR2;
908
909 --======================================================================
910 -- FUNCTION
911 -- CurrRound
912 --
913 -- PURPOSE
914 -- Returns the round value for an amount based on the currency
915 --
916 -- HISTORY
917 -- 13-Sep-2001 slkrishn Create.
918 --======================================================================
919 FUNCTION CurrRound(
920 p_amount IN NUMBER,
921 p_currency_code IN VARCHAR2
922 )
923 RETURN NUMBER;
924
925 --======================================================================
926 -- PROCEDURE
927 -- get_install_info
928 --
929 -- PURPOSE
930 -- Gets the installation information for an application
931 -- with application_id p_dep_appl_id
932 --
933 -- HISTORY
934 -- 19-Dec-2002 mayjain Create.
935 --======================================================================
936 procedure get_install_info(p_appl_id in number,
937 p_dep_appl_id in number,
938 x_status out nocopy varchar2,
939 x_industry out nocopy varchar2,
940 x_installed out nocopy number);
941
942 --======================================================================
943 -- PROCEDURE
944 -- Get_Object_Name
945 --
946 -- PURPOSE
947 -- Callback method for IBC to get the Associated Object name for an
951 -- 3/7/2003 mayjain Create.
948 -- Electronic Deliverable Attachment.
949 --
950 -- HISTORY
952 --======================================================================
953 PROCEDURE Get_Object_Name(
954 p_association_type_code IN VARCHAR2
955 ,p_associated_object_val1 IN VARCHAR2
956 ,p_associated_object_val2 IN VARCHAR2
957 ,p_associated_object_val3 IN VARCHAR2 DEFAULT NULL
958 ,p_associated_object_val4 IN VARCHAR2 DEFAULT NULL
959 ,p_associated_object_val5 IN VARCHAR2 DEFAULT NULL
960 ,x_object_name OUT NOCOPY VARCHAR2
961 ,x_object_code OUT NOCOPY VARCHAR2
962 ,x_return_status OUT NOCOPY VARCHAR2
963 ,x_msg_count OUT NOCOPY NUMBER
964 ,x_msg_data OUT NOCOPY VARCHAR2
965 );
966
967
968 --======================================================================
969 -- PL/SQL RECORD
970 -- dependent_objects_rec_type
971 --
972 -- PURPOSE
973 -- Dependent Objects Record definition so that it can be used
974 -- across all objects for delete.
975 -- This stores details of all the dependent objects for the object to
976 -- be deleted.
977 --
978 -- HISTORY
979 -- 05-Nov-2001 sveerave Create.
980 --======================================================================
981 TYPE dependent_objects_rec_type IS RECORD
982 (
983 name VARCHAR2(240)
984 ,type VARCHAR2(30)
985 ,status VARCHAR2(30)
986 ,owner VARCHAR2(240)
987 ,deletable_flag VARCHAR2(1)
988 );
989
990 --======================================================================
991 -- PL/SQL TABLE OF RECORDS
992 -- dependent_objects_tbl_type
993 --
994 -- PURPOSE
995 -- Dependent Objects table definition which holds dependent objects rec
996 -- so that it can be used across all objects for delete.
997 -- This stores details of all the dependent objects for the object to
998 -- be deleted.
999 --
1000 -- HISTORY
1001 -- 05-Nov-2001 sveerave Create.
1002 --======================================================================
1003
1004 TYPE dependent_objects_tbl_type IS TABLE OF dependent_objects_rec_type INDEX BY BINARY_INTEGER;
1005
1006
1007
1008 --========================================================================
1009 -- PROCEDURE
1010 -- get_user_id
1011 --
1012 -- PURPOSE
1013 -- This api will take a resource id and give the corresponding user_id
1014 --
1015 -- NOTE
1016 --
1017 -- HISTORY
1018 -- 28-may-2003 soagrawa Created
1019 --========================================================================
1020
1021
1022 FUNCTION get_user_id (
1023 p_resource_id IN NUMBER
1024 )
1025 RETURN NUMBER ;
1026
1027
1028 --========================================================================
1029 -- PROCEDURE
1030 -- get_party_name
1031 --
1032 -- PURPOSE
1033 -- This api returns party name given the customer_account_id
1034 --
1035 -- NOTE
1036 --
1037 -- HISTORY
1038 -- Wed Oct 29 2003:3/36 PM rssharma Created
1039 --========================================================================
1040 FUNCTION get_party_name(p_cust_account_id IN NUMBER)
1041 RETURN VARCHAR2;
1042
1043 --========================================================================
1044 -- PROCEDURE
1045 -- get_user_status_name
1046 --
1047 -- PURPOSE
1048 -- This api returns user_status_name given the user_status_id and the system_status_code
1049 --
1050 -- NOTE
1051 --
1052 -- HISTORY
1053 -- Wed Jan 21 2004:4/18 PM rssharma Created
1054 --========================================================================
1055 FUNCTION get_user_status_name(p_user_status_id IN NUMBER , p_system_status_type IN VARCHAR2)
1056 RETURN VARCHAR2;
1057
1058 --========================================================================
1059 -- PROCEDURE
1060 -- get_currency_name
1061 --
1062 -- PURPOSE
1063 -- This api returns Currency Name given the Currency Code
1064 --
1065 -- NOTE
1066 --
1067 -- HISTORY
1068 -- Fri Jan 23 2004:3/18 PM rssharma Created
1069 --========================================================================
1070 FUNCTION get_currency_name(p_currency_code IN VARCHAR2) RETURN VARCHAR2;
1071
1072 --========================================================================
1073 -- PROCEDURE
1074 -- get_currency_name
1075 --
1076 -- PURPOSE
1077 -- This api returns Media Name given the Media id
1078 --
1079 -- NOTE
1080 --
1081 -- HISTORY
1082 -- Fri Jan 23 2004:3/18 PM rssharma Created
1083 --========================================================================
1084 FUNCTION get_media_name(p_media_id IN NUMBER) RETURN VARCHAR2 ;
1085
1086 --========================================================================
1087 -- PROCEDURE
1088 -- get_fnd_lookup_meaning
1089 --
1090 -- PURPOSE
1091 -- This api returns Meaning of a NON-OZF FND lookup given the lookupcode and lookup_type
1092 --
1093 -- NOTE
1094 --
1095 -- HISTORY
1096 -- Fri Jan 23 2004:3/18 PM rssharma Created
1097 --========================================================================
1098 FUNCTION get_fnd_lookup_meaning(p_lookup_type IN VARCHAR2,p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
1099
1100 --======================================================================
1101 -- FUNCTION
1102 -- Has_Sales_Rep_Role
1103 --
1104 -- PURPOSE
1105 -- This api returns FND_API.g_true if the given user has the "Sales
1106 -- Representative" role. It returns FND_API.g_false otherwise.
1107 --
1108 -- HISTORY
1109 -- 23-Feb-2004 yizhang Create.
1110 --======================================================================
1111 FUNCTION Has_Sales_Rep_Role(
1112 p_resource_id IN NUMBER
1113 )
1114 RETURN VARCHAR2;
1115
1116
1117 --======================================================================
1118 -- FUNCTION
1119 -- get_commited_amount
1120 -- get_recal_commited_amount
1121 -- get_utilized_amount
1122 -- get_paid_amount
1123 -- get_earned_amount
1124
1125 -- PURPOSE
1126 -- The api return budget amount.
1127
1128 -- HISTORY
1129 -- 17-Feb-2005 feliu Create.
1130 --======================================================================
1131
1132 FUNCTION get_commited_amount(p_list_header_id IN NUMBER) RETURN NUMBER;
1133 FUNCTION get_recal_commited_amount(p_list_header_id IN NUMBER) RETURN NUMBER;
1134 FUNCTION get_utilized_amount(p_list_header_id IN NUMBER) RETURN NUMBER;
1135 FUNCTION get_paid_amount(p_list_header_id IN NUMBER) RETURN NUMBER;
1136 FUNCTION get_earned_amount(p_list_header_id IN NUMBER) RETURN NUMBER;
1137 FUNCTION get_org_name(p_org_id NUMBER) RETURN VARCHAR2;
1138
1139 --======================================================================
1140 -- FUNCTION
1141 -- get_object_org_ledger
1142
1143 -- PURPOSE
1144 -- The api return the object's org_id and ledger:
1145 -- offer: org_id for local offer
1146 -- user default org_id for global offer
1147 -- pric: org_id for price list
1148 -- budget: budget's org_id
1149 -- ledger on budget if specified - ledger on budget category if specified - budget creator default OU's ledger
1150 -- others: user default org_id for global offer
1151 -- HISTORY
1152 -- 13-Oct-2005 yzhao Create.
1153 --======================================================================
1154
1155 PROCEDURE get_object_org_ledger(
1156 p_object_id IN NUMBER
1157 ,p_object_type IN VARCHAR2
1158 ,x_org_id OUT NOCOPY NUMBER
1159 ,x_ledger_id OUT NOCOPY NUMBER
1160 ,x_return_status OUT NOCOPY VARCHAR2
1161 );
1162
1163 FUNCTION getAttributeName
1164 (
1165 p_attributeCode IN VARCHAR2
1166 )
1167 RETURN VARCHAR2;
1168
1169 FUNCTION getAttributeName
1170 (
1171 p_attributeCode IN VARCHAR2
1172 , p_applicationId IN NUMBER
1173 ) RETURN VARCHAR2;
1174
1175 --======================================================================
1176 -- FUNCTION
1177 -- get_revenue_account
1178
1179 -- PURPOSE
1180 -- The function returns the revenue account for Off-Invoice offer.
1181 --
1182 -- HISTORY
1183 -- 05-Mar-2010 BKUNJAN ER 9382547 - TM SLA Uptake
1184 --======================================================================
1185 FUNCTION get_revenue_account (p_utilization_id IN NUMBER
1186 ,p_org_id IN NUMBER)
1187 RETURN NUMBER;
1188 -- For TPA Parallel Execution ER Bug#9614703 (+)
1189 ---------------------------------------------------------------------
1190 -- PROCEDURE
1191 -- UPDATE_OZF_ACT_BUDGETS
1192 --
1193 -- PURPOSE
1194 -- This API is used to update fund main table (OZF_ACT_BUDGETS) at once with values
1195 -- from PL/SQL table type px_ozf_act_budgets_tbl for chargeback flow only.
1196 --
1197 -- PARAMETERS
1198 --
1199 -- NOTES
1200 -- MUTHSUBR CREATED FOR BUG#8867381
1201 ---------------------------------------------------------------------
1202 PROCEDURE UPDATE_OZF_ACT_BUDGETS (
1203 p_batch_id IN NUMBER,
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_budget_update_status OUT NOCOPY VARCHAR2,
1206 px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
1207 p_batch_type IN VARCHAR2
1208 );
1209
1210
1211 ---------------------------------------------------------------------
1212 -- PROCEDURE
1213 -- UPDATE_OZF_FUNDS_ALL_B
1214 --
1215 -- PURPOSE
1216 -- This API is used to update fund main table (OZF_FUNDS_ALL_B) at once with values
1217 -- from PL/SQL record type px_ozf_funds_new_rectype for chargeback flow only.
1218 --
1219 -- PARAMETERS
1220 --
1221 -- NOTES
1222 -- MUTHSUBR CREATED FOR BUG#8867381
1223 ---------------------------------------------------------------------
1224 PROCEDURE UPDATE_OZF_FUNDS_ALL_B (
1225 p_batch_id IN NUMBER,
1226 x_return_status OUT NOCOPY VARCHAR2,
1227 x_fund_update_status OUT NOCOPY VARCHAR2,
1228 px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
1229 p_batch_type IN VARCHAR2
1230 );
1231 -- For TPA Parallel Execution ER Bug#9614703 (-)
1232
1233 ---------------------------------------------------------------------
1234 -- FUNCTION
1235 -- convert_currency
1236 -- DESCRIPTION
1237 -- convert_currency created for Endeca View Purpose
1238 ---------------------------------------------------------------------
1239 FUNCTION convert_currency(
1240 p_from_currency IN VARCHAR2
1241 ,p_to_currency IN VARCHAR2
1242 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1243 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
1244 ,p_conv_date IN DATE DEFAULT SYSDATE
1245 ,p_from_amount IN NUMBER
1246 )
1247 RETURN NUMBER;
1248
1249
1250 END OZF_Utility_PVT;
[Home] [Help]