1 PACKAGE AMW_Utility_PVT as
2 /*$Header: amwvutls.pls 120.5.12000000.2 2007/04/04 01:12:02 rjohnson ship $*/
3
4 ------------------------------------------------------------------------------
5 -- HISTORY
6 ------------------------------------------------------------------------------
7
8 g_number CONSTANT NUMBER := 1; -- data type is number
9 g_varchar2 CONSTANT NUMBER := 2; -- data type is varchar2
10 g_amw_lookups CONSTANT VARCHAR2(12) := 'AMW_LOOKUPS';
11
12 resource_locked EXCEPTION;
13 pragma EXCEPTION_INIT(resource_locked, -54);
14
15 --------------
16 -- History
17 --- Creatd 04/01/2005 dliao
18 --------------------
19 /***
20 Debug levels:
21 0 = No debug
22 1 = Log errors
23 2 = Log errors and functional messages
24 3 = Log errors, functional messages and SQL statements
25 4 = Full Debug
26 ***/
27 g_debug_level_none CONSTANT NUMBER := 0;
28 g_debug_level_error CONSTANT NUMBER := 1;
29 g_debug_level_medium CONSTANT NUMBER := 2;
30 g_debug_level_sql CONSTANT NUMBER := 3;
31 g_debug_level_full CONSTANT NUMBER := 4;
32
33
34
35 ---------------------------------------------------------------------
36 -- FUNCTION
37 -- check_fk_exists
38 --
39 -- PURPOSE
40 -- This function checks if a foreign key is valid.
41 --
42 -- NOTES
43 -- 1. It will return FND_API.g_true/g_false.
44 -- 2. Exception encountered will be raised to the caller.
45 -- 3. p_pk_data_type can be AMW_Global_PVT.g_number/g_varchar2.
46 -- 4. Please don't put 'AND' at the beginning of your additional
47 -- where clause.
48 ---------------------------------------------------------------------
49 FUNCTION check_fk_exists(
50 p_table_name IN VARCHAR2,
51 p_pk_name IN VARCHAR2,
52 p_pk_value IN VARCHAR2,
53 p_pk_data_type IN NUMBER := g_number,
54 p_additional_where_clause IN VARCHAR2 := NULL
55 )
56 RETURN VARCHAR2; -- FND_API.g_true/g_false
57
58
59 ---------------------------------------------------------------------
60 -- FUNCTION
61 -- check_lookup_exists
62 --
63 -- PURPOSE
64 -- This function checks if a lookup_code is valid.
65
66 ---------------------------------------------------------------------
67 FUNCTION check_lookup_exists(
68 p_lookup_table_name IN VARCHAR2 := g_amw_lookups,
69 p_lookup_type IN VARCHAR2,
70 p_lookup_code IN VARCHAR2
71 )
72 Return VARCHAR2; -- FND_API.g_true/g_false
73
74 ---------------------------------------------------------------------
75 -- FUNCTION
76 -- check_lookup_exists
77 --
78 -- PURPOSE
79 -- This function checks if a lookup_code is valid from fnd_lookups when
80 -- view_application_id is passed in.
81 ---------------------------------------------------------------------
82 FUNCTION check_lookup_exists(
83 p_lookup_type IN VARCHAR2,
84 p_lookup_code IN VARCHAR2,
85 p_view_application_id IN NUMBER
86 )
87 Return VARCHAR2; -- FND_API.g_true/g_false
88
89
90 ---------------------------------------------------------------------
91 -- FUNCTION
92 -- check_uniqueness
93 --
94 -- PURPOSE
95 -- This function is to check the uniqueness of the keys.
96 -- In order to make this function more flexible, you need to
97 -- pass in where clause of your unique key's check.
98 ---------------------------------------------------------------------
99 FUNCTION check_uniqueness(
100 p_table_name IN VARCHAR2,
101 p_where_clause IN VARCHAR2
102 )
103 RETURN VARCHAR2; -- FND_API.g_true/g_false
104
105
106 ---------------------------------------------------------------------
107 -- FUNCTION
108 -- is_Y_or_N
109 --
110 -- PURPOSE
111 -- Return FND_API.g_true if p_value='Y' or p_value='N';
112 -- return FND_API.g_flase otherwise.
113 ---------------------------------------------------------------------
114 FUNCTION is_Y_or_N(
115 p_value IN VARCHAR2
116 )
117 RETURN VARCHAR2; -- FND_API.g_true/g_false
118
119
120 ---------------------------------------------------------------------
121 -- FUNCTION
122 -- Find_Hierarchy_Level
123 -- PURPOSE
124 -- This function returns the level in hierarchy of an entity
125 -- to be displayed on the HGrid
126 -- HISTORY
127 -- 4/23/2003 abedajna created.
128 ---------------------------------------------------------------------
129 FUNCTION Find_Hierarchy_Level(
130 entity_name IN VARCHAR2
131 )
132 Return number;
133
134
135 ---------------------------------------------------------------------
136 -- PROCEDURE
137 -- debug_message
138 --
139 -- PURPOSE
140 -- This procedure will check the message level and try to add a
141 -- debug message into the message table of FND_MSG_API package.
142 -- Note that this debug message won't be translated.
143 ---------------------------------------------------------------------
144 PROCEDURE debug_message(
145 p_message_text IN VARCHAR2,
146 p_message_level IN NUMBER := NULL
147 );
148
149
150 ---------------------------------------------------------------------
151 -- PROCEDURE
152 -- error_message
153 --
154 -- PURPOSE
155 -- Add an error message to the message_list for an expected error.
156 ---------------------------------------------------------------------
157 PROCEDURE error_message(
158 p_message_name VARCHAR2,
159 p_token_name VARCHAR2 := NULL,
160 P_token_value VARCHAR2 := NULL
161 );
162
163
164 ---------------------------------------------------------------------
165 -- PROCEDURE
166 -- display_messages
167 --
168 -- PURPOSE
169 -- This procedure will display all messages in the message list
170 -- using DBMS_OUTPUT.put_line( ) .
171 ---------------------------------------------------------------------
172 PROCEDURE display_messages;
173
174
175 --======================================================================
176 -- Procedure Name: send_wf_standalone_message
177 -- Type : Generic utility
178 -- Pre-Req :
179 -- Notes:
180 -- Common utility to send standalone message without initiating
181 -- process using workflow.
182 -- Parameters:
183 -- IN:
184 -- p_item_type IN VARCHAR2 Required Default = 'MAPGUTIL'
185 -- item type for the workflow utility.
186 -- p_message_name IN VARCHAR2 Required Default = 'GEN_STDLN_MESG'
187 -- Internal name for standalone message name
188 -- p_subject IN VARCHAR2 Required
189 -- Subject for the message
190 -- p_body IN VARCHAR2 Optional
191 -- Body for the message
192 -- p_send_to_role_name IN VARCHAR2 Optional
193 -- Role name to whom message is to be sent.
194 -- Instead of this, one can send even p_send_to_res_id
195 -- p_send_to_res_id IN NUMBER Optional
196 -- Resource Id that will be used to get role name from WF_DIRECTORY.
197 -- This is required if role name is not passed.
198
199 -- OUT:
200 -- x_notif_id OUT NUMBER
201 -- Notification Id created that is being sent to recipient.
202 -- x_return_status OUT VARCHAR2
203 -- Return status. If it is error, messages will be put in mesg pub.
204 -- History:
205 --======================================================================
206
207 PROCEDURE send_wf_standalone_message(
208 p_item_type IN VARCHAR2 := 'AMWGUTIL'
209 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
210 ,p_subject IN VARCHAR2
211 ,p_body IN VARCHAR2 := NULL
212 ,p_send_to_role_name IN VARCHAR2 := NULL
213 ,p_send_to_person_id IN NUMBER := NULL
214 ,x_notif_id OUT NOCOPY NUMBER
215 ,x_return_status OUT NOCOPY VARCHAR2
216 );
217
218 ---------------------------------------------------------------------
219 -- FUNCTION
220 -- get_lookup_meaning
221 -- USAGE
222 -- Example:
223 -- SELECT AMw_Utility_PVT.get_lookup_meaning ('AMS_RISK_STATUS', status_code)
224 -- FROM amw ....;
225 -- HISTORY
226 -- 6/4/2003 mpande Created.
227 ---------------------------------------------------------------------
228 FUNCTION get_lookup_meaning (
229 p_lookup_type IN VARCHAR2,
230 p_lookup_code IN VARCHAR2
231 )
232 RETURN VARCHAR2;
233 ---------------------------------------------------------------------
234 -- FUNCTION
235 -- get_employess_name
236 -- USAGE
237 -- Example:
238 -- SELECT AMW_Utility_PVT.get_employee_name (party_id)
239 -- FROM dual
240 -- HISTORY
241 -- 6/19/2003 mpande Created.
242 ---------------------------------------------------------------------
243 FUNCTION get_employee_name (
244 p_party_id IN VARCHAR2
245 )
246 RETURN VARCHAR2;
247
248 ---------------------------------------------------------------------
249 -- PROCEDURE
250 -- wait_for_req
251 -- HISTORY
252 -- 8/22/2003 ABEDAJNA Created.
253 ---------------------------------------------------------------------
254 procedure wait_for_req (
255 p_request_id IN NUMBER,
256 p_interval IN number,
257 p_max_wait IN number,
258 p_phase OUT nocopy varchar2,
259 p_status OUT nocopy varchar2,
260 p_dev_phase OUT nocopy varchar2,
261 p_dev_status OUT nocopy varchar2,
262 p_message OUT nocopy varchar2,
263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
264 x_return_status out nocopy varchar2,
265 x_msg_count out nocopy number,
266 x_msg_data out nocopy varchar2
267 );
268
269
270 ---------------------------------------------------------------------
271 -- PROCEDURE
272 -- get_lob_meaning
273 -- HISTORY
274 -- 9/16/2003 ABEDAJNA Created.
275 ---------------------------------------------------------------------
276 FUNCTION get_lob_meaning(p_lob_name in varchar2) return varchar2;
277
278
279 ---------------------------------------------------------------------
280 -- PROCEDURE
281 -- get_process_name
282 -- HISTORY
283 -- 11/25/2003 ABEDAJNA Created.
284 ---------------------------------------------------------------------
285 FUNCTION get_process_name(p_process_id in number) return varchar2;
286 ---------------------------------------------------------------------
287 --Function
288 -- get_message_text
289 ---------------------------------------------------------------------
290
291 FUNCTION get_message_text(p_message_name in varchar2) return varchar2;
292
293 ---------------------------------------------------------------------
294 --FUNCTION
295 -- get_risk_name
296 -- HISTORY
297 --12/30/2003 KOSRINIV Created
298 ---------------------------------------------------------------------
299 FUNCTION get_risk_name(p_risk_id in number) return varchar2;
300
301 ---------------------------------------------------------------------
302 --FUNCTION
303 -- get_control_name
304 -- HISTORY
305 --12/30/2003 KOSRINIV Created
306 ---------------------------------------------------------------------
307
308 FUNCTION get_control_name(p_control_id in number) return varchar2;
309
310
311 ---------------------------------------------------------------------
312 --FUNCTION
313 -- get_organization_name
314 -- HISTORY
315 --12/30/2003 KOSRINIV Created
316 ---------------------------------------------------------------------
317
318 FUNCTION get_organization_name(p_organization_id in number) return varchar2;
319
320 FUNCTION get_proc_org_opinion_status(p_process_id in number, p_org_id in number, p_mode in varchar2) return varchar2;
321 FUNCTION get_proc_org_opinion_date(p_process_id in number, p_org_id in number, p_mode in varchar2) return varchar2;
322
323 ---------------------------------------------------------------------
324 --FUNCTION
325 -- get_exception_name
326 -- Notes :
327 -- gives the process display name of the processe in a process exception
328 -- Parameters :
329 -- p_type :- 'A' (Add exception -for Adding process or replacing process)
330 -- 'D' (Delete exception - for Deleted process or replaced processd)
334 ---------------------------------------------------------------------
331 --p_exception_id :- Exception Id
332 -- HISTORY
333 --04/29/2004 KOSRINIV Created
335
336 FUNCTION get_exception_name(p_type in varchar2, p_exception_id in number) return varchar2;
337
338
339 procedure isUserProcessOwner (
340 p_pk IN number,
341 p_userid IN number,
342 p_objectContext IN varchar2,
343 p_retval OUT nocopy varchar2,
344 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
345 x_return_status out nocopy varchar2,
346 x_msg_count out nocopy number,
347 x_msg_data out nocopy varchar2
348 );
349
350 ---------------------------------------------------------------------
351 --FUNCTION
352 -- get_risktype_text
353 -- Notes :
354 -- gives the Risk Type heading for webadi columns
355 -- Parameters :
356 -- p_type :- p_risktype_token , The risk type name
357 -- HISTORY
358 --07/30/2004 KOSRINIV Created
359 ---------------------------------------------------------------------
360
361 FUNCTION get_risktype_text(p_risktype_token in varchar2) return varchar2;
362
363
364 FUNCTION get_parameter(p_org_id in number, p_param_name in varchar2) return varchar2;
365
366 ---------------------------------------------------------------------
367 --returns the display name of the current approved revision of
368 --process
369
370 --Parameters
371 --p_process_id :- the process id
372
373 --History
374 --11/05/2004 NIRMAKUM created
375 ---------------------------------------------------------------------
376 FUNCTION get_process_name_approved(p_process_id in number) return varchar2;
377
378 ---------------------------------------------------------------------
379 --returns the display name of the of process
380
381 --Parameters
382 --p_process_id :- the process id
383 --p_status : 'L' or 'A' (saying whether the latest process name is wanted or
384 -- the latest approved process_name is wanted)
385
386 --History
387 --11/18/2004 NIRMAKUM created
388 ---------------------------------------------------------------------
389 FUNCTION get_process_name_by_status(p_process_id in number,
390 p_status in varchar2) return varchar2;
391
392 ---------------------------------------------------------------------
393 --NOTES
394 --returns the display name of the current approved revision of
395 --organization process
396 --Parameters
397 --p_process_id :- the process id,p_org_id :- org Id
398 --History
399 --11/06/2004 KOSRINIV created
400 ---------------------------------------------------------------------
401 FUNCTION get_approved_org_process_name (p_process_id in number, p_org_id in number) return varchar2 ;
402
403 ----------------------------------------------------------------------------
404 -- utility function for use in hgrid sql ..return 'Y' if process locked, 'N' otherwise
405 --Parameters
406 -- p_process_id :- processId, p_org_id :- Organization id.
407 --History
408 --11/09/2004 KOSRINIV created
409 ------------------------------------------------------------------------------
410 FUNCTION is_process_locked(p_process_id in number, p_org_id in number) return varchar2;
411
412 ----------------------------------------------------------------------------
413 -- utility function to get the count of audit projects for a given organization
414 --Parameters
415 -- p_org_id :- Organization id.
416 --History
417 --12/06/2004 KOSRINIV created
418 ------------------------------------------------------------------------------
419 FUNCTION get_project_count( p_org_id in number) return number;
420
421 FUNCTION get_contrlol_objective_name(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number) return varchar2;
422 FUNCTION get_contrlol_objective_id(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number) return NUMBER;
423 FUNCTION is_contrlol_objective_approved(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number) return varchar2;
424 FUNCTION get_cobj_name_approved(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number) return varchar2;
425 ------------------------------------------------------------------------------------------------------------------------
426 -- utiltiy method to return a value if the process has presence in latest hierarchy.
427 --Parameters
428 -- p_org_id :- Organization id,p_proc_id = Process id
429 --History
430 --21/02/2004 KOSRINIV created
431 ------------------------------------------------------------------------------
432 FUNCTION exist_in_latest_hier(p_org_id in number,p_proc_id in number) return varchar2;
433 ------------------------------------------------------------------------------------------------------------------------
434 -- utiltiy method to return change Id of the process . returns -99 if there no CR existing.
435 --Parameters
436 -- p_org_id :- Organization id,p_proc_id = Process id,p_rev_num= revision number
437 --History
438 --21/02/2004 KOSRINIV created
439 ------------------------------------------------------------------------------
440 FUNCTION get_proc_change_id(p_org_id in number,p_proc_id in number, p_rev_num in number) return NUMBER;
441
442 ------------------------------------------------------------------------------------------------------------------------
446 --History
443 -- utiltiy method to return Y if the process is having more than one child in latest hierarchy.
444 --Parameters
445 -- p_org_id :- Organization id (-1 for RL), ,p_proc_id = Process id
447 --21/02/2004 KOSRINIV created
448 ------------------------------------------------------------------------------
449 FUNCTION has_child_morethan_two(p_proc_id in number,p_org_id in number) return VARCHAR;
450
451 -- Method to check whether any control is associated with a particular risk.
452 --Parameters
453 -- p_process_id :- Process Id, p_revision_number :- Process Revision id, p_risk_id :- Risk Id
454 --History
455 --16/03/2005 DPATEL created
456 ------------------------------------------------------------------------------
457 FUNCTION is_control_associated_to_risk(p_process_id in number, p_revision_number in number, p_risk_id in number) return varchar2;
458
459 -- Method to check whether any control is associated with all risks.
460 --Parameters
461 -- p_process_id :- Process Id, p_revision_number :- Process Revision id, p_risk_id :- Risk Id
462 --History
463 --17/03/2005 DPATEL created
464 ------------------------------------------------------------------------------
465 FUNCTION is_ctrl_assotd_to_all_risks(p_process_id in number, p_revision_number in number, p_risk_id in number) return varchar2;
466
467 -- Method to check whether a control objective is approved.
468 --Parameters
469 -- p_process_id :- Process Id, p_risk_id :- Risk Id ,p_control_id :- Control Id
470 --History
471 --01/04/2005 DPATEL created
472 ------------------------------------------------------------------------------
473 FUNCTION is_control_objective_approved(p_process_id in number, p_risk_id in number, p_control_id in number) return varchar2;
474
475 --04.01.2005 npanandi: added below method to return
476 --display value for Ineff Ctrls / Evaluated Ctrls / Total Ctrls
477 --bug 4201078 fix
478 function get_display_value(
479 p_ineff_ctrl in number
480 ,p_eval_ctrl in number
481 ,p_total_ctrl in number) return varchar2;
482
483 --04.01.2005 npanandi: added below method to return
484 --display value for Process/Org Certified vs. Total Processes/Orgs
485 --bug 4201078 fix
486 function get_display_proc_cert(
487 p_sub_process_cert in number
488 ,p_total_sub_process_cert in number) return varchar2;
489
490 -- utility methods for process variation report..
491 FUNCTION GET_EX_REASONS(p_action IN VARCHAR2,
492 p_object IN VARCHAR2,
493 p_pk1 IN VARCHAR2,
494 p_pk2 IN VARCHAR2,
495 p_pk3 IN VARCHAR2,
496 p_pk4 IN VARCHAR2 := NULL,
497 p_pk5 IN VARCHAR2 := NULL) RETURN VARCHAR2;
498 FUNCTION GET_EX_COMMENTS(p_action IN VARCHAR2,
499 p_object IN VARCHAR2,
500 p_pk1 IN VARCHAR2,
501 p_pk2 IN VARCHAR2,
502 p_pk3 IN VARCHAR2,
503 p_pk4 IN VARCHAR2 := NULL,
504 p_pk5 IN VARCHAR2 := NULL) RETURN VARCHAR2;
505
506 -------------------------------------------------------------------------
507 -- This procedure inserts a record into the FND_LOG_MESSAGES table
508 -- FND uses an autonomous transaction so even when the hookinsert is
509 -- rolled back because of an error the log messages still exists
510 -- and also writes log message in the fnd_file, which supports
511 -- concurrent program view-log message feature
512 -- History
513 --- Created on 04/01/2005 dliao
514 ----------------------------------------------------------------------
515 PROCEDURE LOG_MSG( v_object_id IN VARCHAR2
516 , v_object_name IN VARCHAR2
517 , v_message IN VARCHAR2
518 -- , v_level_id IN NUMBER := -1
519 , v_module IN VARCHAR2);
520
521 ----------------------------------------------------------------------------
522 -- this function returns 'Y','N' depending on the controls exists for a given org-process-risk in the given hierarchy..
523 -- History
524 --- Created on 04/11/2005 kosriniv
525 ---------------------------------------------------------------------------------------------------
526 FUNCTION GET_RISK_CONTROLS_EXIST(p_org_id IN NUMBER, p_process_id IN NUMBER, p_risk_id IN NUMBER, p_appr_date IN DATE) RETURN VARCHAR2;
527
528 ----------------------------------------------------------------------------
529 -- this function returns 'Y','N' depending on the organization parameters were set or not.
530 -- History
531 --- Created on 04/28/2005 kosriniv
532 ---------------------------------------------------------------------------------------------------
533 FUNCTION IS_ORG_REGISTERED(p_org_id IN NUMBER) RETURN VARCHAR2;
534
535 PROCEDURE submit_conc_request(p_template_code IN VARCHAR2,
536 p_template_lang IN VARCHAR2 default NULL,
537 p_template_territory IN VARCHAR2 default NULL,
538 p_certification_id IN NUMBER default NULL,
539 p_organization_id IN NUMBER default NULL,
540 p_process_id IN NUMBER default NULL,
541 p_from_date IN DATE default NULL,
542 p_to_date IN DATE default NULL,
543 p_include_orgs_with_issues IN VARCHAR2 default NULL,
544 p_key_controls IN VARCHAR2 default NULL,
548
545 p_material_risks IN VARCHAR2 default NULL,
546 p_significant_process IN VARCHAR2 default NULL,
547 p_request_id OUT nocopy NUMBER);
549
550 --------------------------------------------------------------------------------------------------
551 -- to get the opinion result of an organization..
552 -- p_org_id :- organization Id, p_mode :- 'CERTIFICATION' or 'EVALUATION' etc..
553 -------------------------------------------------------------------------------------------------
554
555 FUNCTION get_org_opinion_status(p_org_id in number, p_mode in varchar2) return varchar2;
556
557 ---------------------------------------------------------------------------------------------------
558 -- to cache the parameter values
559 -- kosriniv 09/03/06
560 -------------------------------------------------------------------------------------------------
561
562 type p_appr_opt_val_type is table of varchar2(1)
563 index by pls_integer;
564 g_appr_opt_val p_appr_opt_val_type;
565
566 g_appr_values_cached boolean := false;
567
568 procedure cache_appr_options;
569
570 procedure unset_appr_cache;
571
572
573 END AMW_Utility_PVT;
[Home] [Help]