1 PACKAGE BODY CSM_UTIL_PKG AS
2 /* $Header: csmeutlb.pls 120.22.12020000.3 2013/04/18 07:59:54 avitiwar ship $ */
3 error EXCEPTION;
4 g_invalid_argument_exception exception;
5 g_null_primary_key_exception exception;
6 g_element_not_found_exception exception;
7
8 g_debug_level NUMBER; -- variable containing debug level
9 g_object_name VARCHAR(255):= 'CSM_UTIL_PKG';
10
11 g_initialize_log BOOLEAN := FALSE;
12
13 --Table to contain information regarding the master tables to be updated
14 --either thru concurrent job or workflow activities.
15 g_acc_refresh_desc_tbl Acc_Refresh_Desc_Tbl_Type := Acc_Refresh_Desc_Tbl_Type();
16
17 --a null list
18 g_null_user_list asg_download.user_list;
19
20 --g_buffer varchar(1024);
21
22 /***
23 Function that returns debug level.
24 0 = No debug
25 1 = Log errors
26 2 = Log errors and functional messages
27 3 = Log errors, functional messages and SQL statements
28 4 = Full Debug
29 ***/
30 FUNCTION Get_Debug_Level RETURN NUMBER
31 IS
32 BEGIN
33 /*** has debug mode already been retrieved ***/
34 IF g_debug_level IS NULL THEN
35 /*** no -> get it from profile ***/
36 g_debug_level := FND_PROFILE.VALUE( 'JTM_DEBUG_LEVEL');
37 END IF;
38 RETURN g_debug_level;
39 END Get_Debug_Level;
40
41
42 /**
43 Paramaters:
44 mesg: The message to be logged
45 */
46 procedure pvt_log (mesg varchar2)
47 IS
48 BEGIN
49 NULL;
50 END;
51
52
53 /* logs messages using the JTT framework */
54 /* log_level: fnd_log.statement, fnd_log.procedure, fnd_log.event, fnd_log.exception, fnd_log.error */
55 PROCEDURE LOG(message IN VARCHAR2,
56 module IN VARCHAR2 DEFAULT 'CSM',
57 log_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
58 IS
59 l_message VARCHAR2(4000);
60 BEGIN
61 -- csm_saj_log_pkg.log(module,message);
62 l_message := message;
63 IF g_initialize_log = TRUE THEN
64 IF (log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
65 FND_LOG.STRING(log_level, module, l_message);
66 END IF;
67 ELSE
68 fnd_log_repository.init();
69 g_initialize_log := TRUE;
70 IF (log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
71 fnd_log.string(log_level, module, l_message);
72 END IF;
73 END IF;
74 END LOG;
75
76 /**
77 Initializes g_acc_refresh_desc_tbl with the informatoin pertaining to
78 master tables to be updated
79 either through concurrent job or workflow activities.
80 */
81 procedure initialize_g_table_desc_tbl
82 IS
83 i NUMBER := 0;
84 BEGIN
85 IF (g_acc_refresh_desc_tbl IS NULL OR g_acc_refresh_desc_tbl.COUNT = 0) THEN
86
87 /* -- CSM_TXN_SUB_TYPES
88 i := i +1;
89 g_acc_refresh_desc_tbl.EXTEND;
90 g_acc_refresh_desc_tbl(i).BACKEND_TABLE_NAME := 'CSI_IB_TXN_TYPES';
91 g_acc_refresh_desc_tbl(i).ACC_TABLE_NAME := 'CSM_IB_TXN_TYPES_ACC';
92 g_acc_refresh_desc_tbl(i).ACC_SEQUENCE_NAME := 'CSM_IB_TXN_TYPES_ACC_S';
93 g_acc_refresh_desc_tbl(i).PRIMARY_KEY_COLUMN := 'SUB_TYPE_ID';
94 g_acc_refresh_desc_tbl(i).PUBLICATION_ITEM_NAME := 'CSF_M_TXN_SUB_TYPES';
95 g_acc_refresh_desc_tbl(i).ACCESS_QUERY :=
96 'select cit.sub_type_id
97 from
98 csi_ib_txn_types cit,
99 csi_source_ib_types cst,
100 csi_txn_types ctt,
101 csi_instance_statuses cis
102 where cst.sub_type_id = cit.sub_type_id
103 and cst.transaction_type_id = ctt.transaction_type_id
104 and cit.src_return_reqd = ''N''
105 and cit.non_src_reference_reqd = ''N''
106 and ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
107 and ctt.source_application_id = 513
108 and cis.instance_status_id(+) = cit.src_status_id
109 and cis.terminated_flag(+) <> ''Y''';
110 */
111
112 --CSM_OBJECT_MAPPINGS
113 i := i +1;
114 g_acc_refresh_desc_tbl.EXTEND;
115 g_acc_refresh_desc_tbl(i).BACKEND_TABLE_NAME := 'JTF_OBJECT_MAPPINGS';
116 g_acc_refresh_desc_tbl(i).ACC_TABLE_NAME := 'CSM_OBJECT_MAPPINGS_ACC';
117 g_acc_refresh_desc_tbl(i).ACC_SEQUENCE_NAME := 'CSM_OBJECT_MAPPINGS_ACC_S';
118 g_acc_refresh_desc_tbl(i).PRIMARY_KEY_COLUMN := 'MAPPING_ID';
119 g_acc_refresh_desc_tbl(i).PUBLICATION_ITEM_NAME := 'CSF_M_OBJECT_MAPPINGS';
120 g_acc_refresh_desc_tbl(i).ACCESS_QUERY :=
121 'select jom.mapping_id
122 from jtf_object_mappings jom
123 WHERE jom.source_object_code IN (''PARTY'', ''TASK'', ''SR'', ''CP'',''OKS_COV_NOTE'',''SD'')';
124
125 END IF;
126 NULL;
127 END initialize_g_table_desc_tbl;
128
129
130 /**
131 Returns the record representing the information needed
132 to refresh/sync an acc table with backend
133
134 Arguments:
135 p_acc_table_name : the ACC table name for which the refresh information
136 is to be retrieved
137
138 Return Value: the record representing the information needed
139 to refresh/sync an acc table with backend
140 */
141 function get_Acc_Refresh_Desc_Rec(p_acc_table_name varchar2)
142 return Acc_Refresh_Desc_Rec_Type
143 IS
144 BEGIN
145
146 FOR i IN 1 .. g_acc_refresh_desc_tbl.COUNT LOOP
147 IF (g_acc_refresh_desc_tbl(i).ACC_TABLE_NAME = p_acc_table_name) THEN
148 RETURN g_acc_refresh_desc_tbl(i);
149 END IF;
150 END LOOP;
151
152 RAISE g_element_not_found_exception;
153
154 END get_Acc_Refresh_Desc_Rec;
155
156
157 FUNCTION get_all_omfs_palm_user_list RETURN asg_download.user_list
158 IS
159 i NUMBER;
160 l_all_omfs_palm_users_list asg_download.user_list;
161
162 CURSOR l_omfs_palm_users_csr
163 IS
164 SELECT usr.USER_ID
165 FROM asg_user_pub_resps pubresp
166 , asg_user usr
167 WHERE usr.enabled = 'Y'
168 AND pubresp.user_name = usr.user_name
169 AND pubresp.pub_name ='SERVICEP';
170 --Cursor when data routed to owner profile is set
171 CURSOR l_route_omfs_palm_users_csr
172 IS
173 SELECT usr.USER_ID
174 FROM asg_user_pub_resps pubresp
175 , asg_user usr
176 WHERE usr.enabled = 'Y'
177 AND pubresp.user_name = usr.user_name
178 AND pubresp.pub_name ='SERVICEP'
179 AND usr.USER_ID = usr.OWNER_ID;
180 BEGIN
181 i := 0;
182 IF csm_profile_pkg.Get_Route_Data_To_Owner ='Y' THEN
183 FOR r_omfs_palm_users_rec IN l_route_omfs_palm_users_csr LOOP
184 i := i + 1;
185 l_all_omfs_palm_users_list(i) := r_omfs_palm_users_rec.user_id;
186 END LOOP;
187 ELSE
188 FOR r_omfs_palm_users_rec IN l_omfs_palm_users_csr LOOP
189 i := i + 1;
190 l_all_omfs_palm_users_list(i) := r_omfs_palm_users_rec.user_id;
191 END LOOP;
192 END IF;
193
194 RETURN l_all_omfs_palm_users_list;
195
196 END get_all_omfs_palm_user_list;
197
198 /**
199 Returns the list containing the RESOURCE_ID of all the OMFS Palm Users
200 */
201 FUNCTION get_all_omfs_palm_res_list RETURN asg_download.user_list
202 IS
203 i NUMBER;
204 l_all_omfs_palm_resource_list asg_download.user_list;
205
206 CURSOR l_omfs_palm_resources_csr
207 IS
208 SELECT usr.resource_id
209 FROM asg_user_pub_resps pubresp
210 , asg_user usr
211 WHERE usr.enabled = 'Y'
212 AND pubresp.user_name = usr.user_name
213 AND pubresp.pub_name = 'SERVICEP';
214
215 CURSOR l_route_omfs_palm_res_csr
216 IS
217 SELECT usr.resource_id
218 FROM asg_user_pub_resps pubresp
219 , asg_user usr
220 WHERE usr.enabled = 'Y'
221 AND pubresp.user_name = usr.user_name
222 AND pubresp.pub_name = 'SERVICEP'
223 AND usr.USER_ID = usr.OWNER_ID;
224
225 BEGIN
226 i := 0;
227 IF csm_profile_pkg.Get_Route_Data_To_Owner ='Y' THEN
228 FOR r_omfs_palm_resource_rec IN l_route_omfs_palm_res_csr LOOP
229 i := i + 1;
230 l_all_omfs_palm_resource_list(i) := r_omfs_palm_resource_rec.resource_id;
231 END LOOP;
232 ELSE
233 FOR r_omfs_palm_resource_rec IN l_omfs_palm_resources_csr LOOP
234 i := i + 1;
235 l_all_omfs_palm_resource_list(i) := r_omfs_palm_resource_rec.resource_id;
236 END LOOP;
237 END IF;
238
239 RETURN l_all_omfs_palm_resource_list;
240
241 END get_all_omfs_palm_res_list;
242
243
244 Function GetLocalTime(p_server_time date, p_userid number)
245 return date
246 is
247 v_client_timezone varchar2(100);
248 v_server_timezone varchar2(100);
249 client_time date;
250
251 begin
252 v_client_timezone := FND_PROFILE.VALUE_SPECIFIC('HZ_CLIENT_TIMEZONE',p_userid,NULL,170);
253
254
255 v_server_timezone := FND_PROFILE.VALUE_SPECIFIC('HZ_SERVER_TIMEZONE',NULL,NULL,170);
256
257
258 if v_client_timezone is null
259 or v_server_timezone is null
260 then
261 return p_server_time;
262
263 else
264
265 client_time := p_server_time - (v_server_timezone - v_client_timezone);
266
267 END if;
268
269 return client_time;
270
271 -- Enter further code below as specified in the Package spec.
272 END GetLocalTime;
273
274 Function GetLocalTime(p_server_time date, p_user_name varchar2)
275 return date
276 is
277 cursor c_user_id (p_user_name varchar2)
278 is
279 select user_id
280 from asg_user
281 where user_name = p_user_name;
282
283 l_user_id asg_user.user_id%TYPE;
284
285 begin
286
287 --select user_id into l_user_id
288 --from asg_user
289 --where user_name = p_user_name;
290
291 OPEN c_user_id(p_user_name);
292 FETCH c_user_id INTO l_user_id;
293 CLOSE c_user_id;
294
295 return GetLocalTime(p_server_time, l_user_id);
296
297 END GetLocalTime;
298
299 Function GetServerTime(p_client_time date, p_user_name varchar2)
300 return date
301 is
302 cursor c_user_id (p_user_name varchar2)
303 is
304 select user_id
305 from asg_user
306 where user_name = p_user_name;
307
308 l_user_id asg_user.user_id%TYPE;
309
310 v_client_timezone varchar2(100);
311 v_server_timezone varchar2(100);
312 l_server_time date;
313
314 begin
315
316 --select user_id into l_user_id
317 --from asg_user
318 --where user_name = p_user_name;
319
320 OPEN c_user_id(p_user_name);
321 FETCH c_user_id INTO l_user_id;
322 CLOSE c_user_id;
323
324 v_client_timezone := FND_PROFILE.VALUE_SPECIFIC('HZ_CLIENT_TIMEZONE',l_user_id,NULL,170);
325
326
327 v_server_timezone := FND_PROFILE.VALUE_SPECIFIC('HZ_SERVER_TIMEZONE',NULL,NULL,170);
328
329
330 if v_client_timezone is null
331 or v_server_timezone is null
332 then
333 return p_client_time;
334
335 else
336
337 l_server_time := p_client_time - (v_client_timezone - v_server_timezone);
338
339 END if;
340
341 return l_server_time;
342
343 END GetServerTime;
344
345
346
347 Function Get_Responsibility_ID(p_userid in number)
348 RETURN NUMBER
349 IS
350 /********************************************************
351 Name:
352 GET_RESPONSIBILITY_ID
353
354 Purpose:
355 Get the responsibility id to determine the rule_id for
356 the state_transitions
357
358 Arguments:
359
360 Known Limitations:
361 - Because we check on rule_id this function is only usable
362 for jtf_state_transitions
363
364 Notes:
365 Following rules are implemented:
366 1 ) User has no responsibility
367 - Get the profile value
368
369 2) User has one responsibility with a matching rule_id:
370 - Use this responsibility_id
371
372 3) User has multiple responsibilities:
373 - Check if all responsibilities have the same set of rules,
374 a) if this is the case then use one of these responsibilities,
375 when a responisibility has no rules we don't count this
376 b) if not, use the profile
377
378
379 ********************************************************/
380
381 l_resp_id NUMBER;
382 l_rule_id NUMBER;
383
384 l_cnt_resp NUMBER;
385 l_cnt_rule NUMBER;
386
387 l_responsibility_id NUMBER;
388 l_diff BOOLEAN;
389 l_last_rec BOOLEAN;
390
391 TYPE RULE_TABLE_TYPE IS TABLE OF NUMBER
392 INDEX BY BINARY_INTEGER;
393 RULE_TABLE RULE_TABLE_TYPE;
394
395 CURSOR C_USER_RESP ( p_user_id NUMBER ) IS
396 SELECT RESPONSIBILITY_ID
397 FROM FND_USER_RESP_GROUPS
398 WHERE NVL( START_DATE , SYSDATE) <= SYSDATE
399 AND NVL( END_DATE , SYSDATE) >= SYSDATE
400 AND USER_ID = p_user_id;
401
402 CURSOR C_STATE_RULE ( p_resp_id NUMBER ) IS
403 SELECT RULE_ID
404 FROM JTF_STATE_RESPONSIBILITIES
405 WHERE RESPONSIBILITY_ID = p_resp_id ;
406
407 BEGIN
408 l_cnt_resp := 0;
409 l_diff := FALSE;
410 l_responsibility_id := NULL;
411 /* Fetch all responsibility ids */
412 FOR r_resp_record IN C_USER_RESP( p_userid) LOOP
413 l_resp_id := r_resp_record.RESPONSIBILITY_ID;
414 l_cnt_resp := l_cnt_resp + 1;
415 /*Check if the responsibility has state rules*/
416 l_last_rec := FALSE;
417 l_cnt_rule := 0;
418 OPEN C_STATE_RULE( p_resp_id => l_resp_id );
419 WHILE l_diff <> TRUE AND l_last_rec <> TRUE LOOP
420 FETCH C_STATE_RULE INTO l_rule_id;
421 IF C_STATE_RULE%NOTFOUND THEN
422 l_last_rec := TRUE;
423 ELSE
424 l_cnt_rule := l_cnt_rule + 1;
425 /* Check if this is the first time we check/ fill the pl/sql table */
426 IF RULE_TABLE.EXISTS( 1 ) AND l_responsibility_id IS NOT NULL THEN
427 /* Pl/sql table already filled, compare rule id with table value */
428 /* Because we sort this is ok */
429 /* First check if our current record set isn't larger than the table record set */
430 /* RAVIR Added for loop as sort by is removed Jan 6, 2003 */
431 FOR i in 1..l_cnt_rule LOOP
432 IF RULE_TABLE.EXISTS( i ) THEN
433 IF RULE_TABLE(i) <> l_rule_id THEN
434 /* ids are different, so use the profile */
435 l_diff := TRUE;
436 END IF;
437 --ELSE
438 -- l_diff := TRUE;
439 END IF;
440 END LOOP ;
441 ELSE
442 /* First time, so fill the Pl/sql table */
443 RULE_TABLE(l_cnt_rule) := l_rule_id;
444 END IF;
445 END IF;
446 END LOOP;
447 CLOSE C_STATE_RULE;
448 IF l_cnt_rule <> 0 THEN
449 /* Set l_responsibility_id, we have a valid responsibility id with state rule(s)*/
450 l_responsibility_id := l_resp_id;
451 END IF;
452 END LOOP;
453 IF l_diff = TRUE OR l_responsibility_id IS NULL THEN
454 /* No responsibility found or rules per responisibilities are different, get profileval */
455 l_responsibility_id := CSM_PROFILE_PKG.GetDefaultStatusResponsibility(p_userid);
456 END IF;
457 return l_responsibility_id;
458 END GET_RESPONSIBILITY_ID;
459
460
461 /*
462 Modified by :
463 Ravi Ranjan 06/19/2002:
464
465 l_value := wf_engine.GetActivityAttrNumber(..)
466
467 Replaced By:
468 l_value := wf_engine.GetActivityAttrText(..)
469 */
470
471 Function MakeDirtyForUser ( p_publication_item in varchar2,
472 p_accessList in number, --1 access_id
473 p_resourceList in number, --1 user_id
474 p_dmlList in char,
475 p_timestamp in date)
476 return boolean
477 is
478 l_markdirty boolean;
479 l_resourcelist number;
480
481 cursor l_rs_resource_extns_csr (p_user_id fnd_user.user_id%type) is
482 select resource_id
483 from jtf_rs_resource_extns
484 where user_id = p_user_id
485 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
486 AND NVL(end_date_active, SYSDATE)
487 ;
488
489 begin
490 -- getthe resource_id, since make dirty requires resource_id
491 open l_rs_resource_extns_csr(p_resourcelist);
492 fetch l_rs_resource_extns_csr into l_resourcelist;
493 close l_rs_resource_extns_csr;
494 l_markdirty := asg_download.MarkDirty(p_publication_item,
495 p_accesslist,
496 l_resourcelist,
497 p_dmllist,
498 p_timestamp);
499 return l_markdirty;
500 end;
501
502 Function MakeDirtyForUser ( p_publication_item in varchar2,
503 p_accessList in asg_download.access_list,
504 p_resourceList in asg_download.user_list,
505 p_dmlList in asg_download.dml_list,
506 p_timestamp in date)
507 return boolean
508 is
509 l_markdirty boolean;
510 l_resourcelist asg_download.user_list;
511
512 cursor l_rs_resource_extns_csr (p_user_id fnd_user.user_id%type) is
513 select resource_id
514 from jtf_rs_resource_extns
515 where user_id = p_user_id
516 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
517 AND NVL(end_date_active, SYSDATE)
518 ;
519
520 begin
521
522
523 if p_resourcelist.count > 0 then
524 for l_ind in 1..p_resourcelist.count loop
525 -- getthe resource_id, since make dirty requires resource_id
526 open l_rs_resource_extns_csr(p_resourcelist(l_ind));
527 fetch l_rs_resource_extns_csr into l_resourcelist(l_ind);
528 close l_rs_resource_extns_csr;
529 end loop;
530 end if;
531
532 l_markdirty := asg_download.MarkDirty(p_publication_item,
533 p_accesslist,
534 l_resourcelist,
535 p_dmllist,
536 p_timestamp);
537
538 return l_markdirty;
539
540 end;
541
542
543 Function MakeDirtyForUser ( p_publication_item in varchar2,
544 p_accessList in asg_download.access_list,
545 p_resourceList in asg_download.user_list,
546 p_dmlList in char,
547 p_timestamp in date)
548 return boolean
549 is
550 l_markdirty boolean;
551 l_resourcelist asg_download.user_list;
552
553 cursor l_rs_resource_extns_csr (p_user_id fnd_user.user_id%type) is
554 select resource_id
555 from jtf_rs_resource_extns
556 where user_id = p_user_id
557 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
558 AND NVL(end_date_active, SYSDATE)
559 ;
560
561 BEGIN
562 if p_accesslist.count > 0 THEN
563 for l_ind in 1..p_resourcelist.count LOOP
564 -- getthe resource_id, since make dirty requires resource_id
565 open l_rs_resource_extns_csr(p_resourcelist(l_ind));
566 fetch l_rs_resource_extns_csr into l_resourcelist(l_ind);
567 close l_rs_resource_extns_csr;
568 end loop;
569 end if;
570
571 --logm('Before markdirty:' || 'pub:' || p_publication_item || 'access_id:' || p_accesslist(0) || ' ' || l_resourcelist(0) );
572
573 IF p_accesslist.count > 0 THEN
574 l_markdirty := asg_download.MarkDirty(p_publication_item,
575 p_accesslist,
576 l_resourcelist,
577 p_dmllist,
578 p_timestamp);
579 return l_markdirty;
580
581 END IF;
582 end;
583
584 FUNCTION MakeDirtyForUser(p_publication_item in varchar2,
585 p_accessList in number,
586 p_resourceList in number,
587 p_dmlList in char,
588 p_timestamp in date,
589 p_pkvalueslist IN asg_download.pk_list)
590 RETURN BOOLEAN
591 IS
592 l_markdirty boolean;
593 l_resourcelist number;
594
595 cursor l_rs_resource_extns_csr (p_user_id fnd_user.user_id%type) is
596 select resource_id
597 from jtf_rs_resource_extns
598 where user_id = p_user_id
599 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
600 AND NVL(end_date_active, SYSDATE);
601
602 BEGIN
603 -- getthe resource_id, since make dirty requires resource_id
604 open l_rs_resource_extns_csr(p_resourcelist);
605 fetch l_rs_resource_extns_csr into l_resourcelist;
606 close l_rs_resource_extns_csr;
607
608 l_markdirty := asg_download.MarkDirty(p_publication_item,
609 p_accesslist,
610 l_resourcelist,
611 p_dmllist,
612 p_timestamp,
613 p_pkvalueslist);
614 return l_markdirty;
615
616 END MakeDirtyForUser;
617
618
619 Function MakeDirtyForResource ( p_publication_item in varchar2,
620 p_accessList in asg_download.access_list,
621 p_resourceList in asg_download.user_list,
622 p_dmlList in char,
623 p_timestamp in date)
624 return boolean
625 is
626 l_markdirty boolean;
627
628 begin
629
630 l_markdirty := asg_download.MarkDirty(p_publication_item,
631 p_accesslist,
632 p_resourcelist,
633 p_dmllist,
634 p_timestamp);
635 return l_markdirty;
636
637 end MakeDirtyForResource;
638
639 Function MakeDirtyForResource ( p_publication_item in varchar2,
640 p_accessList in number,
641 p_resourceList in number,
642 p_dmlList in char,
643 p_timestamp in date)
644 return boolean
645 is
646 l_markdirty boolean;
647
648 begin
649
650 l_markdirty := asg_download.MarkDirty(p_publication_item,
651 p_accesslist,
652 p_resourcelist,
653 p_dmllist,
654 p_timestamp);
655 return l_markdirty;
656
657 end MakeDirtyForResource;
658
659 FUNCTION MakeDirtyForResource(p_publication_item in varchar2,
660 p_accessList in number,
661 p_resourceList in number,
662 p_dmlList in char,
663 p_timestamp in date,
664 p_pkvalueslist IN asg_download.pk_list)
665 RETURN BOOLEAN
666 IS
667 l_markdirty boolean;
668
669 BEGIN
670 l_markdirty := asg_download.MarkDirty(p_publication_item,
671 p_accesslist,
672 p_resourcelist,
673 p_dmllist,
674 p_timestamp,
675 p_pkvalueslist);
676
677 return l_markdirty;
678
679 END MakeDirtyForResource;
680
681
682 FUNCTION GetAsgDmlConstant( p_dml in char)
683 return char
684 is
685 /********************************************************
686 Name:
687 GetAsgDmlConstant
688
689 Purpose:
690 Converts the DML constants defined in Workflows to the ones
691 understood by ASG. Presently, both the constant sets are same.
692
693 Arguments:
694 p_dml: DML constant defined in WorkFlow
695
696 Returns:
697 DML constant understood by ASG
698
699 Exceptions:
700 g_invalid_argument_exception:
701 In case the passed parameter dml type is not in ('I', 'U', 'D')
702
703 *********************************************************/
704 begin
705 --convert the passed parameter to one of the ASG constants
706 if (p_dml = 'I') then
707 return ASG_DOWNLOAD.INS;
708 elsif (p_dml = 'U') then
709 return ASG_DOWNLOAD.UPD;
710 elsif (p_dml = 'D') then
711 return ASG_DOWNLOAD.DEL;
712 --In case the passed parameter dml type is not in ('I', 'U', 'D')
713 --throw invalid argument exception
714 else raise g_invalid_argument_exception;
715 end if;
716 end;
717
718 /**
719 * Check if the passed resource is a palm resource
720 */
721
722 FUNCTION is_palm_resource(p_resource_id IN NUMBER)
723 RETURN BOOLEAN
724 IS
725 CURSOR l_is_palm_resource_csr(p_resource_id IN jtf_rs_resource_extns.resource_id%TYPE)
726 IS--R12 on multiple responsibility
727 SELECT 1
728 FROM asg_user_pub_resps pubresp
729 , asg_user usr
730 WHERE usr.enabled = 'Y'
731 AND pubresp.user_name = usr.user_name
732 AND pubresp.pub_name = 'SERVICEP'
733 AND usr.resource_id = p_resource_id;
734
735 /*CURSOR l_is_palm_resource_csr(p_resource_id IN jtf_rs_resource_extns.resource_id%type)
736 IS
737 select fnd_user_resp.user_id
738 from asg_pub_responsibility asg_resp,
739 asg_pub,
740 fnd_user_resp_groups fnd_user_resp,
741 fnd_application fnd_app,
742 jtf_rs_resource_extns res
743 where asg_resp.pub_id = asg_pub.pub_id
744 and asg_pub.name = 'SERVICEP'
745 and asg_resp.responsibility_id = fnd_user_resp.responsibility_id
746 and fnd_app.application_id = fnd_user_resp.responsibility_application_id
747 and fnd_user_resp.user_id = res.user_id
748 AND SYSDATE BETWEEN nvl(fnd_user_resp.start_date, sysdate) AND nvl(fnd_user_resp.end_date, sysdate)
749 and fnd_app.application_short_name = 'CSM'
750 and res.resource_id = p_resource_id;
751 */
752 l_is_palm_resource_rec l_is_palm_resource_csr%ROWTYPE;
753
754 BEGIN
755 OPEN l_is_palm_resource_csr(p_resource_id);
756 FETCH l_is_palm_resource_csr INTO l_is_palm_resource_rec;
757 IF l_is_palm_resource_csr%FOUND THEN
758 CLOSE l_is_palm_resource_csr;
759 RETURN TRUE;
760 ELSE
761 CLOSE l_is_palm_resource_csr;
762 RETURN FALSE;
763 END IF;
764
765 EXCEPTION
766 WHEN OTHERS THEN
767 IF l_is_palm_resource_csr%ISOPEN THEN
768 CLOSE l_is_palm_resource_csr;
769 END IF;
770 END is_palm_resource;
771
772 FUNCTION is_palm_user(p_user_id IN NUMBER)
773 RETURN BOOLEAN
774 IS
775 CURSOR l_responsibilities_csr (b_user_id NUMBER)--R12 For multiple responsibility
776 IS
777 SELECT 1
778 FROM asg_user_pub_resps pubresp
779 , asg_user usr
780 WHERE usr.enabled = 'Y'
781 AND pubresp.user_name = usr.user_name
782 AND pubresp.pub_name = 'SERVICEP'
783 AND usr.user_id = b_user_id;
784
785
786 /* cursor l_responsibilities_csr (b_user_id number) is
787 select fnd_user_resp.user_id
788 from asg_pub_responsibility asg_resp,
789 asg_pub,
790 fnd_user_resp_groups fnd_user_resp,
791 fnd_application fnd_app
792 where asg_resp.pub_id = asg_pub.pub_id
793 and asg_pub.name = 'SERVICEP'
794 and asg_resp.responsibility_id = fnd_user_resp.responsibility_id
795 and fnd_app.application_id = fnd_user_resp.responsibility_application_id
796 and fnd_app.application_short_name = 'CSM'
797 and fnd_user_resp.user_id = p_user_id
798 AND SYSDATE BETWEEN nvl(fnd_user_resp.start_date, sysdate) AND nvl(fnd_user_resp.end_date, sysdate)
799 ;
800 */
801 l_responsibilities_rec l_responsibilities_csr%ROWTYPE;
802 BEGIN
803 open l_responsibilities_csr (p_user_id);
804 fetch l_responsibilities_csr into l_responsibilities_rec;
805 if (l_responsibilities_csr%notfound) then
806 close l_responsibilities_csr;
807 RETURN FALSE;
808 else --intersection is non-void
809 close l_responsibilities_csr;
810 RETURN TRUE;
811 end if;
812 --close the cursor
813 EXCEPTION
814 WHEN OTHERS THEN
815 --close the cursor if open
816 if (l_responsibilities_csr%isopen) then
817 close l_responsibilities_csr;
818 end if;
819 RAISE;
820 END is_palm_user;
821
822 function get_tl_omfs_palm_users(p_language varchar2)
823 return asg_download.user_list
824 AS
825
826 l_tl_omfs_palm_users_list asg_download.user_list;
827 i NUMBER;
828
829 CURSOR l_tl_omfs_palm_users_csr is
830 --R12 For multiple responsibility
831 select usr.USER_ID
832 FROM asg_user_pub_resps pubresp
833 , asg_user usr
834 WHERE usr.LANGUAGE = p_language
835 AND usr.enabled = 'Y'
836 AND pubresp.user_name = usr.user_name
837 AND pubresp.pub_name ='SERVICEP';
838
839 CURSOR l_route_tl_omfs_palm_users_csr is
840 --R12 For multiple responsibility
841 select usr.USER_ID
842 FROM asg_user_pub_resps pubresp
843 , asg_user usr
844 WHERE usr.LANGUAGE = p_language
845 AND usr.enabled = 'Y'
846 AND pubresp.user_name = usr.user_name
847 AND pubresp.pub_name ='SERVICEP'
848 AND usr.USER_ID = usr.OWNER_ID;
849 BEGIN
850 i := 0;
851 IF csm_profile_pkg.Get_Route_Data_To_Owner ='Y' THEN
852 FOR r_tl_omfs_palm_users_rec in l_route_tl_omfs_palm_users_csr LOOP
853 i := i + 1;
854 l_tl_omfs_palm_users_list(i) := r_tl_omfs_palm_users_rec.user_id;
855 END LOOP;
856 ELSE
857 FOR r_tl_omfs_palm_users_rec in l_tl_omfs_palm_users_csr LOOP
858 i := i + 1;
859 l_tl_omfs_palm_users_list(i) := r_tl_omfs_palm_users_rec.user_id;
860 END LOOP;
861 END IF;
862 return l_tl_omfs_palm_users_list;
863
864 END get_tl_omfs_palm_users;
865
866 function get_tl_omfs_palm_resources(p_language varchar2)
867 return asg_download.user_list
868 AS
869
870 l_tl_omfs_palm_resource_list asg_download.user_list;
871 i NUMBER;
872
873 CURSOR l_tl_omfs_palm_resources_csr is
874 --R12 For multiple responsibility
875 select usr.RESOURCE_ID
876 FROM asg_user_pub_resps pubresp
877 , asg_user usr
878 WHERE usr.LANGUAGE = p_language
879 AND usr.enabled = 'Y'
880 AND pubresp.user_name = usr.user_name
881 AND pubresp.pub_name ='SERVICEP';
882
883 CURSOR l_route_tl_omfs_palm_res_csr is
884 --R12 For multiple responsibility
885 select usr.RESOURCE_ID
886 FROM asg_user_pub_resps pubresp
887 , asg_user usr
888 WHERE usr.LANGUAGE = p_language
889 AND usr.enabled = 'Y'
890 AND pubresp.user_name = usr.user_name
891 AND pubresp.pub_name ='SERVICEP'
892 AND usr.USER_ID = usr.OWNER_ID;
893 BEGIN
894 i := 0;
895 IF csm_profile_pkg.Get_Route_Data_To_Owner ='Y' THEN
896 FOR r_tl_omfs_palm_resource_rec in l_route_tl_omfs_palm_res_csr LOOP
897 i := i + 1;
898 l_tl_omfs_palm_resource_list(i) := r_tl_omfs_palm_resource_rec.resource_id;
899 END LOOP;
900 ELSE
901 FOR r_tl_omfs_palm_resource_rec in l_tl_omfs_palm_resources_csr LOOP
902 i := i + 1;
903 l_tl_omfs_palm_resource_list(i) := r_tl_omfs_palm_resource_rec.resource_id;
904 END LOOP;
905 END IF;
906
907 return l_tl_omfs_palm_resource_list;
908
909 END get_tl_omfs_palm_resources;
910
911 /* get language for the specified user_id */
912 FUNCTION get_user_language(p_user_id IN NUMBER)
913 return VARCHAR2
914 IS
915 CURSOR c_language_csr(p_user_id number) IS
916 SELECT language
917 FROM asg_user
918 WHERE user_id = p_user_id;
919
920 l_language asg_user.language%TYPE;
921
922 BEGIN
923 OPEN c_language_csr(p_user_id);
924 FETCH c_language_csr INTO l_language;
925 CLOSE c_language_csr;
926
927 RETURN l_language;
928
929 END get_user_language;
930
931 FUNCTION get_user_name(p_user_id IN number)
932 RETURN varchar2
933 IS
934 l_user_name asg_user.user_name%TYPE;
935
936 CURSOR l_get_user_name(p_user_id IN number)
937 IS
938 SELECT user_name
939 FROM asg_user
940 WHERE user_id = p_user_id;
941
942 BEGIN
943 OPEN l_get_user_name(p_user_id);
944 FETCH l_get_user_name INTO l_user_name;
945 CLOSE l_get_user_name;
946
947 RETURN l_user_name;
948 END get_user_name;
949
950 /**
951 Refreshes the specified application level ACC tables
952 Also adds the entries in the System Dirty Queue for all the
953 OMFS Palm users if the entry gets updated, deleted or inserted in the ACC table
954 */
955 PROCEDURE refresh_app_level_acc (
956 p_backend_table_name varchar2,
957 p_primary_key_column varchar2,
958 p_acc_table_name varchar2,
959 p_acc_sequence_name varchar2,
960 p_tl_table_name varchar2,
961 p_publication_item_name varchar2,
962 p_access_query varchar2,
963 p_primary_key_value number)
964
965 IS
966 l_access_id number;
967 l_pk_value number;
968 l_last_update_date date;
969 l_prev_language varchar2(24);
970 l_language varchar2(24);
971
972 l_sqlerrno varchar2(20);
973 l_sqlerrmsg varchar2(2000);
974 l_acc_table_name varchar2(30);
975 l_acc_sequence_name varchar2(50);
976 l_backend_table_name varchar2(30);
977 l_tl_table_name varchar2(30);
978 l_primary_key_column varchar2(30);
979 l_publication_item_name varchar2(30);
980 l_access_query varchar2(2048);
981 l_primary_key_value number;
982
983 l_dsql varchar2(2048);
984 l_upd_dsql varchar2(1024);
985 l_cursorid NUMBER;
986 l_result NUMBER;
987
988 l_changed_records_cur Changed_Records_Cur_Type;
989
990 l_tl_omfs_palm_resource_list asg_download.user_list;
991 l_all_omfs_palm_resource_list asg_download.user_list;
992 --contains the same access ID n times (where n = number of users for which
993 --to insert into SDQ)
994 l_single_access_id_list asg_download.access_list;
995 --a null list
996 l_null_access_list asg_download.access_list;
997
998 i NUMBER;
999 l_mark_dirty BOOLEAN;
1000
1001 begin --refresh_app_level_acc
1002 l_backend_table_name := p_backend_table_name;
1003 l_primary_key_column := p_primary_key_column;
1004 l_acc_table_name := p_acc_table_name;
1005 l_acc_sequence_name := p_acc_sequence_name;
1006 l_tl_table_name := p_tl_table_name;
1007 l_publication_item_name := p_publication_item_name;
1008 l_access_query := p_access_query;
1009 l_primary_key_value := p_primary_key_value;
1010
1011 --initialize the list of all omfs palm resources
1012 l_all_omfs_palm_resource_list := get_all_omfs_palm_res_list;
1013
1014
1015 /********** DELETES *****************/
1016 --Delete deleted entries in backend from the ACC
1017
1018 IF l_primary_key_value IS NULL THEN
1019 -- Mark Dirty 'D' the SDQ
1020 l_dsql :=
1021 ' SELECT access_id '
1022 || ' FROM '
1023 || l_acc_table_name
1024 || ' WHERE '
1025 || l_primary_key_column
1026 || ' not in ( '
1027 || l_access_query
1028 || ' )';
1029
1030 open l_changed_records_cur for l_dsql;
1031
1032 LOOP
1033 FETCH l_changed_records_cur INTO l_access_id;
1034 EXIT WHEN l_changed_records_cur%NOTFOUND;
1035 -- process data record
1036 --add for all the users into the SDQ
1037 --nullify the access list
1038 l_single_access_id_list := l_null_access_list;
1039 i := 0;
1040
1041 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
1042 l_single_access_id_list(i) := l_access_id;
1043 END LOOP;
1044 --mark dirty the SDQ
1045 l_mark_dirty := MakeDirtyForResource(l_publication_item_name,
1046 l_single_access_id_list,
1047 l_all_omfs_palm_resource_list,
1048 ASG_DOWNLOAD.DEL,
1049 sysdate);
1050 END LOOP;
1051
1052
1053 --generate the sql for deleting removed values from ACC table
1054 l_dsql :=
1055 'DELETE FROM '
1056 || l_acc_table_name
1057 || ' WHERE access_id '
1058 || ' IN
1059 ( '
1060 || l_dsql
1061 || ' )';
1062
1063 --open database cursor
1064 l_cursorid := DBMS_SQL.open_cursor;
1065 --parse and execute the sql
1066 DBMS_SQL.parse(l_cursorid, l_dsql, DBMS_SQL.v7);
1067 l_result := DBMS_SQL.execute(l_cursorid);
1068 DBMS_SQL.close_cursor (l_cursorid);
1069
1070 END IF; --END l_primary_key_value IS NULL (for delete case)
1071
1072 /********** END DELETES **************/
1073
1074
1075 /******* UPDATES *********/
1076 -- a) Updates to non TL table
1077
1078 l_dsql :=
1079 ' SELECT '
1080 || ' acc.access_id'
1081 || ' AS ACCESS_ID, b.'
1082 || l_primary_key_column
1083 || ' , b.LAST_UPDATE_DATE
1084 FROM '
1085 || l_backend_table_name
1086 || ' b ,'
1087 || l_acc_table_name
1088 || ' acc
1089 WHERE
1090 b.'
1091 || l_primary_key_column
1092 || ' = acc.'
1093 || l_primary_key_column
1094 || ' AND
1095 b.LAST_UPDATE_DATE > acc.LAST_UPDATE_DATE'
1096 ;
1097
1098 IF l_primary_key_value IS NOT NULL THEN
1099 l_dsql := l_dsql || ' AND b.' || l_primary_key_column || ' = '
1100 || l_primary_key_value;
1101 END IF;
1102
1103 open l_changed_records_cur for l_dsql;
1104
1105 LOOP
1106 FETCH l_changed_records_cur INTO l_access_id, l_pk_value, l_last_update_date;
1107 EXIT WHEN l_changed_records_cur%NOTFOUND;
1108 -- process data record
1109 --get the users with this language
1110 l_tl_omfs_palm_resource_list := get_tl_omfs_palm_resources(l_language);
1111
1112 --add for all the users into the SDQ
1113 --nullify the access list
1114 l_single_access_id_list := l_null_access_list;
1115
1116 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
1117 l_single_access_id_list(i) := l_access_id;
1118 END LOOP;
1119 --mark dirty the SDQ
1120 l_mark_dirty := MakeDirtyForResource(l_publication_item_name,
1121 l_single_access_id_list,
1122 l_all_omfs_palm_resource_list,
1123 ASG_DOWNLOAD.UPD,
1124 sysdate);
1125
1126 --update the ACC table
1127 l_upd_dsql := 'UPDATE ' || l_acc_table_name
1128 || ' SET LAST_UPDATE_DATE = (SELECT LAST_UPDATE_DATE FROM '
1129 || l_backend_table_name
1130 || ' WHERE ' || l_primary_key_column || ' = ' || l_pk_value
1131 || '), LAST_UPDATED_BY = fnd_global.user_id WHERE '
1132 || ' access_id = ' || l_access_id;
1133
1134 --open database cursor
1135 l_cursorid := DBMS_SQL.open_cursor;
1136 --parse and execute the sql
1137 DBMS_SQL.parse(l_cursorid, l_upd_dsql, DBMS_SQL.v7);
1138 l_result := DBMS_SQL.execute(l_cursorid);
1139 DBMS_SQL.close_cursor (l_cursorid);
1140
1141 END LOOP;
1142 -- END Updates to non TL table
1143
1144
1145 -- b) Updates to TL table
1146
1147 IF ( l_tl_table_name IS NOT NULL ) THEN
1148
1149 l_dsql :=
1150 ' SELECT acc.access_id, '
1151 || ' b.'
1152 || l_primary_key_column
1153 || ' , b.LAST_UPDATE_DATE, b.LANGUAGE
1154 FROM '
1155 || l_tl_table_name
1156 || ' b ,'
1157 || l_acc_table_name
1158 || ' acc
1159 WHERE
1160 b.'
1161 || l_primary_key_column
1162 || ' = acc.'
1163 || l_primary_key_column
1164 || ' AND
1165 b.LAST_UPDATE_DATE > acc.LAST_UPDATE_DATE'
1166 ;
1167
1168 IF l_primary_key_value IS NOT NULL THEN
1169 l_dsql := l_dsql || ' AND b.' || l_primary_key_column || ' = '
1170 || l_primary_key_value;
1171 END IF;
1172
1173 open l_changed_records_cur for l_dsql;
1174
1175 LOOP
1176 FETCH l_changed_records_cur INTO l_access_id, l_pk_value, l_last_update_date, l_language;
1177 EXIT WHEN l_changed_records_cur%NOTFOUND;
1178 -- process data record
1179 --add for the users with same language as the language in TL into the SDQ
1180 l_tl_omfs_palm_resource_list := get_tl_omfs_palm_resources (l_language);
1181 --nullify the access list
1182 l_single_access_id_list := l_null_access_list;
1183 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
1184 l_single_access_id_list(i) := l_access_id;
1185 END LOOP;
1186 --mark dirty the SDQ
1187 l_mark_dirty := MakeDirtyForResource(l_publication_item_name,
1188 l_single_access_id_list,
1189 l_tl_omfs_palm_resource_list,
1190 ASG_DOWNLOAD.UPD,
1191 sysdate);
1192
1193 --update the ACC table
1194 l_upd_dsql := 'UPDATE ' || l_acc_table_name
1195 || ' SET LAST_UPDATE_DATE = (SELECT LAST_UPDATE_DATE FROM '
1196 || l_tl_table_name
1197 || ' WHERE ' || l_primary_key_column || ' = ' || l_pk_value
1198 || '), LAST_UPDATED_BY = fnd_global.user_id WHERE '
1199 || ' access_id = ' || l_access_id;
1200
1201 --open database cursor
1202 l_cursorid := DBMS_SQL.open_cursor;
1203 --parse and execute the sql
1204 DBMS_SQL.parse(l_cursorid, l_upd_dsql, DBMS_SQL.v7);
1205 l_result := DBMS_SQL.execute(l_cursorid);
1206 DBMS_SQL.close_cursor (l_cursorid);
1207
1208 END LOOP;
1209
1210 END IF; --END IF l_tl_table_name is not null
1211
1212 /******* END UPDATES ****/
1213
1214 /******* INSERTS *******/
1215 --Insert new entries in backend to the SDQ and ACC
1216
1217 -- Mark Dirty 'I' the SDQ
1218 l_dsql :=
1219 ' SELECT '
1220 || l_acc_sequence_name || '.nextval, '
1221 || l_primary_key_column
1222 || ' FROM '
1223 || l_backend_table_name
1224 || ' WHERE '
1225 || l_primary_key_column
1226 || ' in ( '
1227 || l_access_query
1228 || ' ) AND '
1229 || l_primary_key_column
1230 || ' not in
1231 ( SELECT '
1232 || l_primary_key_column
1233 || ' FROM '
1234 || l_acc_table_name
1235 || ' )';
1236
1237
1238 IF l_primary_key_value IS NOT NULL THEN
1239 l_dsql := l_dsql || ' AND ' || l_primary_key_column || ' = '
1240 || l_primary_key_value;
1241 END IF;
1242
1243 open l_changed_records_cur for l_dsql;
1244
1245 LOOP
1246 FETCH l_changed_records_cur INTO l_access_id, l_pk_value;
1247 EXIT WHEN l_changed_records_cur%NOTFOUND;
1248 -- process data record
1249 --add for all the users into the SDQ
1250 --nullify the access list
1251 l_single_access_id_list := l_null_access_list;
1252 i := 0;
1253 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
1254 l_single_access_id_list(i) := l_access_id;
1255 END LOOP;
1256
1257 --mark dirty the SDQ
1258 l_mark_dirty := MakeDirtyForResource(
1259 p_publication_item => l_publication_item_name,
1260 p_accessList => l_single_access_id_list,
1261 p_resourceList => l_all_omfs_palm_resource_list,
1262 p_dmlList => ASG_DOWNLOAD.INS,
1263 p_timestamp => sysdate);
1264
1265 --generate the sql for inserting new values into ACC table
1266 l_dsql :=
1267 'INSERT INTO '
1268 || l_acc_table_name
1269 || ' ( access_id, '
1270 || l_primary_key_column
1271 || ', CREATED_BY,
1272 CREATION_DATE,
1273 LAST_UPDATED_BY,
1274 LAST_UPDATE_DATE,
1275 LAST_UPDATE_LOGIN)
1276 SELECT ' || l_access_id || ', b.'
1277 || l_primary_key_column
1278 || ', fnd_global.user_id,
1279 sysdate,
1280 fnd_global.user_id,
1281 NVL(b.LAST_UPDATE_DATE, sysdate),
1282 fnd_global.user_id
1283 FROM '
1284 || l_backend_table_name
1285 || ' b '
1286 || ' WHERE '
1287 || l_primary_key_column
1288 || ' = '
1289 || l_pk_value
1290 || ' AND '
1291 || l_primary_key_column
1292 || ' not in
1293 ( SELECT '
1294 || l_primary_key_column
1295 || ' FROM '
1296 || l_acc_table_name
1297 || ' )';
1298
1299
1300 IF l_primary_key_value IS NOT NULL THEN
1301 l_dsql := l_dsql || ' AND ' || l_primary_key_column || ' = '
1302 || l_primary_key_value;
1303 END IF;
1304
1305 --open database cursor
1306 l_cursorid := DBMS_SQL.open_cursor;
1307 --parse and execute the sql
1308 DBMS_SQL.parse(l_cursorid, l_dsql, DBMS_SQL.v7);
1309 l_result := DBMS_SQL.execute(l_cursorid);
1310 DBMS_SQL.close_cursor (l_cursorid);
1311
1312 /********** END INSERTS **************/
1313
1314 END LOOP;
1315
1316 EXCEPTION
1317 WHEN others THEN
1318 l_sqlerrno := to_char(SQLCODE);
1319 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1320 csm_util_pkg.log('Error in refresh_app_level_acc:' || l_sqlerrno || ':' || l_sqlerrmsg);
1321 END refresh_app_level_acc;
1322
1323 /**
1324 Refreshes all the application level ACC tables
1325 Also adds the entries in the System Dirty Queue for all the
1326 OMFS Palm users if the entry gets updated, deleted or inserted in the ACC table
1327 */
1328 PROCEDURE refresh_all_app_level_acc(p_status OUT NOCOPY VARCHAR2,
1329 p_message OUT NOCOPY VARCHAR2)
1330 IS
1331 PRAGMA AUTONOMOUS_TRANSACTION;
1332 l_sqlerrno varchar2(20);
1333 l_sqlerrmsg varchar2(2000);
1334 l_dummy number;
1335 l_last_run_date date;
1336
1337 CURSOR l_upd_last_run_date_csr
1338 IS
1339 SELECT 1
1340 FROM jtm_con_request_data
1341 WHERE product_code = 'CSM'
1342 AND package_name = 'CSM_UTIL_PKG'
1343 AND procedure_name = 'REFRESH_ALL_APP_LEVEL_ACC'
1344 FOR UPDATE OF last_run_date NOWAIT
1345 ;
1346
1347 BEGIN
1348 l_last_run_date := SYSDATE;
1349 --initialize the table information descriptions
1350 initialize_g_table_desc_tbl;
1351
1352 --iterate over all the tables to be refreshed
1353 FOR i in 1 .. g_acc_refresh_desc_tbl.COUNT LOOP
1354 refresh_app_level_acc (
1355 p_backend_table_name => g_acc_refresh_desc_tbl(i).BACKEND_TABLE_NAME,
1356 p_primary_key_column => g_acc_refresh_desc_tbl(i).PRIMARY_KEY_COLUMN,
1357 p_acc_table_name => g_acc_refresh_desc_tbl(i).ACC_TABLE_NAME,
1358 p_acc_sequence_name => g_acc_refresh_desc_tbl(i).ACC_SEQUENCE_NAME,
1359 p_tl_table_name => g_acc_refresh_desc_tbl(i).TL_TABLE_NAME,
1360 p_publication_item_name => g_acc_refresh_desc_tbl(i).PUBLICATION_ITEM_NAME,
1361 p_access_query => g_acc_refresh_desc_tbl(i).ACCESS_QUERY,
1362 p_primary_key_value => null
1363 );
1364 END LOOP;
1365
1366 -- update last_run_date
1367 OPEN l_upd_last_run_date_csr;
1368 FETCH l_upd_last_run_date_csr INTO l_dummy;
1369 IF l_upd_last_run_date_csr%FOUND THEN
1370 UPDATE jtm_con_request_data
1371 SET last_run_date = l_last_run_date
1372 WHERE CURRENT OF l_upd_last_run_date_csr;
1373 END IF;
1374 CLOSE l_upd_last_run_date_csr;
1375
1376 COMMIT;
1377
1378 p_status := 'FINE';
1379 p_message := 'CSM_UTIL_PKG.refresh_all_app_level_acc Executed successfully';
1380
1381 EXCEPTION
1382 WHEN others THEN
1383 l_sqlerrno := to_char(SQLCODE);
1384 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1385 p_status := 'ERROR';
1386 p_message := 'Error in CSM_UTIL_PKG.refresh_all_app_level_acc :' || l_sqlerrno || ':' || l_sqlerrmsg;
1387 ROLLBACK;
1388 csm_util_pkg.log('CSM_UTIL_PKG.REFRESH_ALL_APP_LEVEL_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1389 END refresh_all_app_level_acc;
1390
1391 /***
1392 This function returns a translated error message string. If p_api_error is FALSE, it gets
1393 message with MESSAGE_NAME = p_message from FND_NEW_MESSAGES and replaces any tokens with
1394 the supplied token values. If p_api_error is TRUE, it just returns the api error in the
1395 FND_MSG_PUB message stack.
1396 ***/
1397 FUNCTION GET_ERROR_MESSAGE_TEXT
1398 (
1399 p_api_error IN BOOLEAN DEFAULT FALSE
1400 , p_message IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE DEFAULT NULL
1401 , p_token_name1 IN VARCHAR2 DEFAULT NULL
1402 , p_token_value1 IN VARCHAR2 DEFAULT NULL
1403 , p_token_name2 IN VARCHAR2 DEFAULT NULL
1404 , p_token_value2 IN VARCHAR2 DEFAULT NULL
1405 , p_token_name3 IN VARCHAR2 DEFAULT NULL
1406 , p_token_value3 IN VARCHAR2 DEFAULT NULL
1407 )
1408 RETURN VARCHAR2 IS
1409 l_fnd_message VARCHAR2(4000);
1410 l_counter NUMBER;
1411 l_msg_data VARCHAR2(2000);
1412 l_msg_dummy NUMBER;
1413 BEGIN
1414 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1415 log ( 'CSM_UTIL_PKG.Get_error_message_text' );
1416 END IF;
1417
1418 /*** Is this an API error? ***/
1419 IF NOT p_api_error THEN
1420 /*** no -> retrieve error message p_message and replace tokens ***/
1421 FND_MESSAGE.Set_Name
1422 ( application => 'CSL'
1423 , name => p_message
1424 );
1425 IF p_token_name1 IS NOT NULL
1426 THEN
1427 FND_MESSAGE.Set_Token
1428 ( token => p_token_name1
1429 , value => p_token_value1
1430 );
1431 END IF;
1432 IF p_token_name2 IS NOT NULL
1433 THEN
1434 FND_MESSAGE.Set_Token
1435 ( token => p_token_name2
1436 , value => p_token_value2
1437 );
1438 END IF;
1439 IF p_token_name3 IS NOT NULL
1440 THEN
1441 FND_MESSAGE.Set_Token
1442 ( token => p_token_name3
1443 , value => p_token_value3
1444 );
1445 END IF;
1446
1447 l_fnd_message := FND_MESSAGE.Get;
1448 ELSE
1449 /*** API error -> retrieve error from message stack ***/
1450 IF FND_MSG_PUB.Count_Msg > 0 THEN
1451 FND_MSG_PUB.Get
1452 ( p_msg_index => 1
1453 , p_encoded => FND_API.G_FALSE
1454 , p_data => l_msg_data
1455 , p_msg_index_out => l_msg_dummy
1456 );
1457 l_fnd_message := l_msg_data;
1458 FOR l_counter
1459 IN 2 .. FND_MSG_PUB.Count_Msg
1460 LOOP
1461 FND_MSG_PUB.Get
1462 ( p_msg_index => l_counter
1463 , p_encoded => FND_API.G_FALSE
1464 , p_data => l_msg_data
1465 , p_msg_index_out => l_msg_dummy
1466 );
1467 l_fnd_message := l_fnd_message || FND_GLOBAL.Newline || l_msg_data;
1468 END LOOP;
1469 END IF;
1470 END IF;
1471
1472 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1473 log
1474 ( 'Leaving CSM_UTIL_PKG.Get_error_message_text'
1475 );
1476 END IF;
1477 RETURN l_fnd_message;
1478 EXCEPTION WHEN OTHERS THEN
1479
1480 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1481 log
1482 ( 'Exception occurred in CSM_UTIL_PKG.Get_error_message_text:' || ' ' || sqlerrm);
1483 END IF;
1484
1485 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1486 log
1487 ( 'Leaving CSM_UTIL_PKG.Get_error_message_text');
1488 END IF;
1489
1490 RETURN l_fnd_message;
1491 END GET_ERROR_MESSAGE_TEXT;
1492
1493 /***
1494 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure when a record was successfully
1495 applied and needs to be deleted from the in-queue.
1496 ***/
1497 PROCEDURE DELETE_RECORD
1498 (
1499 p_user_name IN VARCHAR2,
1500 p_tranid IN NUMBER,
1501 p_seqno IN NUMBER,
1502 p_pk IN VARCHAR2,
1503 p_object_name IN VARCHAR2,
1504 p_pub_name IN VARCHAR2,
1505 p_error_msg OUT NOCOPY VARCHAR2,
1506 x_return_status IN OUT NOCOPY VARCHAR2
1507 ) IS
1508 l_tracking_id NUMBER;
1509 BEGIN
1510 x_return_status := FND_API.G_RET_STS_SUCCESS;
1511
1512 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1513 log
1514 ( 'Entering DELETE_RECORD');
1515 log
1516 ( 'Check and clear TRACKING deferred record');
1517 END IF;
1518
1519 -- Remove tracking table record if this record is deferred.
1520 -- reqd if tracking records are reapplied via MFS Admin pages
1521 IF asg_defer.is_deferred(p_user_name, p_tranid,p_pub_name, p_seqno)=FND_API.G_TRUE THEN
1522 BEGIN
1523 SELECT TRACKING_ID INTO l_tracking_id
1524 FROM CSM_DEFERRED_NFN_INFO
1525 WHERE DEFERRED_TRAN_ID=p_tranid
1526 AND CLIENT_ID=p_user_name
1527 AND SEQUENCE = p_seqno
1528 AND OBJECT_NAME=p_pub_name
1529 AND OBJECT_ID=p_pk;
1530
1531 CSM_ACC_PKG.Delete_Acc
1532 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
1533 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
1534 ,P_PK1_NAME => 'TRACKING_ID'
1535 ,P_PK1_NUM_VALUE => l_tracking_id
1536 ,P_USER_ID => asg_base.get_user_id(p_user_name)
1537 );
1538 DELETE FROM CSM_DEFERRED_NFN_INFO
1539 WHERE TRACKING_ID=l_tracking_id;
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 NULL;
1543 END;
1544 END IF;
1545
1546 asg_apply.delete_row(p_user_name,
1547 p_tranid,
1548 p_pub_name,
1549 p_seqno,
1550 x_return_status);
1551
1552
1553 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1554 log( 'ASG Delete row unsuccessful');
1555
1556 /*** error occurred ***/
1557 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'DELETE_RECORD', 'Unknown error');
1558 p_error_msg := GET_ERROR_MESSAGE_TEXT
1559 (
1560 p_api_error => TRUE
1561 );
1562 ELSE
1563 DELETE FROM CSM_HTML5_PARSED_DATA WHERE SESSION_ID= p_tranid AND SEQUENCE=p_seqno;
1564 END IF;
1565
1566 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1567 log
1568 ( 'Leaving DELETE_RECORD');
1569 END IF;
1570
1571 EXCEPTION WHEN OTHERS THEN
1572 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1573 log
1574 ( 'Exception occurred in DELETE_RECORD:' || ' ' || sqlerrm);
1575 END IF;
1576
1577 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'DELETE_RECORD', sqlerrm);
1578 p_error_msg := GET_ERROR_MESSAGE_TEXT
1579 (
1580 p_api_error => TRUE
1581 );
1582
1583 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1584 log
1585 ( 'Leaving DELETE_RECORD');
1586 END IF;
1587 x_return_status := FND_API.G_RET_STS_ERROR;
1588 END DELETE_RECORD;
1589
1590 /***
1591 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure
1592 when a record failed to be processed and needs to be deferred and rejected from mobile.
1593 ***/
1594 PROCEDURE DEFER_RECORD
1595 (
1596 p_user_name IN VARCHAR2,
1597 p_tranid IN NUMBER,
1598 p_seqno IN NUMBER,
1599 p_pk IN VARCHAR2,
1600 p_object_name IN VARCHAR2,
1601 p_pub_name IN VARCHAR2,
1602 p_error_msg IN VARCHAR2,
1603 x_return_status IN OUT NOCOPY VARCHAR2,
1604 p_dml_type IN VARCHAR2
1605 ) IS
1606
1607 CURSOR c_err_msg
1608 IS
1609 SELECT ERROR_DESCRIPTION FROM ASG_DEFERRED_TRANINFO
1610 WHERE DEVICE_USER_NAME = p_user_name
1611 AND DEFERRED_TRAN_ID = p_tranid
1612 AND OBJECT_NAME = p_pub_name
1613 AND SEQUENCE = p_seqno;
1614
1615 l_error_msg VARCHAR2(4000);
1616 l_t VARCHAR2(1);
1617
1618 BEGIN
1619 x_return_status := FND_API.G_RET_STS_SUCCESS;
1620
1621 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1622 log
1623 ( 'Entering DEFER_RECORD');
1624 END IF;
1625
1626 asg_defer.defer_row(p_user_name,
1627 p_tranid,
1628 p_pub_name,
1629 p_seqno,
1630 p_error_msg,
1631 x_return_status);
1632
1633
1634 /*** check if defer was successfull ***/
1635 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1636 /*** no -> log and return error ***/
1637
1638 log( 'asg_defer.defer_row failed:' || ' ' || p_error_msg);
1639
1640 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1641 log
1642 ( 'Leaving DEFER_RECORD');
1643 END IF;
1644 RETURN;
1645 END IF;
1646
1647 l_error_msg := p_error_msg;
1648
1649 IF ( p_error_msg IS NULL) THEN
1650 OPEN c_err_msg;
1651 FETCH c_err_msg INTO l_error_msg;
1652 CLOSE c_err_msg;
1653 END IF;
1654
1655 /*Removed reject row and replaced with deferred notification and tracking logic*/
1656 -- 12.1.3
1657 csm_notification_event_pkg.notify_deferred(p_user_name,p_tranid,
1658 p_pub_name,p_seqno, p_dml_type,p_pk,l_error_msg);
1659
1660
1661 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1662 log
1663 ( 'Leaving DEFER_RECORD');
1664 END IF;
1665 EXCEPTION WHEN OTHERS THEN
1666
1667 log( 'Exception occurred in DEFER_RECORD:' || ' ' || sqlerrm);
1668
1669
1670 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1671 log
1672 ( 'Leaving DEFER_RECORD');
1673 END IF;
1674
1675 x_return_status := FND_API.G_RET_STS_ERROR;
1676 END DEFER_RECORD;
1677
1678 /***
1679 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure
1680 when the PK of the inserted record is created in the API.
1681 We need to remove the local PK from local
1682 ***/
1683 PROCEDURE REJECT_RECORD
1684 (
1685 p_user_name IN VARCHAR2,
1686 p_tranid IN NUMBER,
1687 p_seqno IN NUMBER,
1688 p_pk IN VARCHAR2,
1689 p_object_name IN VARCHAR2,
1690 p_pub_name IN VARCHAR2,
1691 p_error_msg IN VARCHAR2,
1692 x_return_status IN OUT NOCOPY VARCHAR2
1693 ) IS
1694
1695 CURSOR c_acc_id
1696 IS
1697 SELECT PK_VALUE
1698 FROM CSM_HTML5_PARSED_DATA
1699 WHERE SESSION_ID=p_tranid
1700 AND SEQUENCE=p_seqno;
1701
1702 l_acc NUMBER;
1703
1704 BEGIN
1705 x_return_status := FND_API.G_RET_STS_SUCCESS;
1706
1707 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1708 log( 'Entering REJECT_RECORD');
1709 END IF;
1710
1711 asg_defer.reject_row(p_user_name,
1712 p_tranid,
1713 p_pub_name,
1714 p_seqno,
1715 p_error_msg,
1716 x_return_status);
1717
1718 /*** check if reject was successfull ***/
1719 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1720 log( 'asg_defer.reject_row failed:' || ' ' || p_error_msg);
1721 ELSIF IS_HTML5_USER(p_user_name) THEN --this is required since access_id will be different in client
1722 OPEN c_acc_id;
1723 FETCH c_acc_id INTO l_acc;
1724 CLOSE c_acc_id;
1725
1726 IF(l_acc IS NOT NULL) THEN
1727 UPDATE asg_system_dirty_queue a
1728 SET ACCESS_ID=l_acc
1729 WHERE PUB_ITEM=p_pub_name
1730 AND CLIENT_ID=p_user_name
1731 AND DML_TYPE=0
1732 AND EXISTS(SELECT 1 FROM ASG_DELETE_QUEUE b
1733 WHERE b.QID=a.QID
1734 AND b.ATTRIBUTE1=p_pk) ;
1735
1736 IF asg_defer.is_deferred(p_user_name, p_tranid,p_pub_name, p_seqno)=FND_API.G_FALSE THEN
1737 DELETE FROM CSM_HTML5_PARSED_DATA WHERE SESSION_ID=p_tranid AND SEQUENCE=p_seqno;
1738 END IF;
1739 END IF;
1740 END IF;
1741
1742 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1743 log( 'Leaving REJECT_RECORD');
1744 END IF;
1745 EXCEPTION WHEN OTHERS THEN
1746 log( 'Exception occurred in REJECT_RECORD:' || ' ' || sqlerrm);
1747 x_return_status := FND_API.G_RET_STS_ERROR;
1748 END REJECT_RECORD;
1749
1750
1751 /* Two functions to check if field service palm is enabled. */
1752
1753 FUNCTION IS_FIELD_SERVICE_PALM_ENABLED
1754 RETURN BOOLEAN
1755 IS
1756 l_option_value VARCHAR2(1);
1757 l_responsibility_id NUMBER;
1758 l_application_id NUMBER;
1759
1760 CURSOR l_get_resp_id IS
1761 SELECT resp.APPLICATION_ID, resp.RESPONSIBILITY_ID
1762 FROM FND_RESPONSIBILITY resp, fnd_application app
1763 WHERE resp.RESPONSIBILITY_KEY = 'OMFS_PALM'
1764 AND SYSDATE BETWEEN nvl(resp.start_date, sysdate) AND nvl(resp.end_date, sysdate)
1765 AND app.application_id = resp.application_id
1766 AND app.application_short_name = 'CSM';
1767
1768 BEGIN
1769 OPEN l_get_resp_id;
1770 FETCH l_get_resp_id INTO l_application_id, l_responsibility_id;
1771 CLOSE l_get_resp_id;
1772
1773 l_option_value := fnd_profile.value_specific('JTM_MOB_APPS_ENABLED', null,
1774 l_responsibility_id, l_application_id);
1775
1776 -- CSM_UTIL_PKG.LOG('YL: IS_FIELD_SERVICE_PALM_ENABLED = ' || l_option_value);
1777
1778 IF ( l_option_value = 'Y' ) THEN
1779 RETURN TRUE;
1780 END IF;
1781
1782 RETURN FALSE;
1783 EXCEPTION
1784 WHEN NO_DATA_FOUND THEN
1785 -- CSM_UTIL_PKG.LOG('YL: IS_FIELD_SERVICE_PALM_ENABLED EXCEPTION ');
1786 RETURN FALSE;
1787 WHEN OTHERS THEN
1788 -- CSM_UTIL_PKG.LOG('YL: IS_FIELD_SERVICE_PALM_ENABLED EXCEPTION ');
1789 RAISE;
1790 END IS_FIELD_SERVICE_PALM_ENABLED;
1791
1792 /* GET_TASK_ESC_LEVEL
1793 */
1794 FUNCTION GET_TASK_ESC_LEVEL( p_task_id IN NUMBER) RETURN VARCHAR2
1795 IS
1796 CURSOR l_esc_csr (b_task_id NUMBER) IS
1797 SELECT esc.escalation_level
1798 FROM jtf_tasks_b tasks,
1799 jtf_task_references_vl ref,
1800 jtf_tasks_b esc
1801 WHERE ref.object_id = tasks.task_id
1802 and ref.object_type_code = 'TASK'
1803 and ref.reference_code = 'ESC'
1804 and ref.task_id = esc.task_id
1805 and esc.source_object_type_code = 'ESC'
1806 and tasks.task_id = b_task_id;
1807 l_esc VARCHAR2(30) := NULL;
1808 BEGIN
1809 OPEN l_esc_csr (p_task_id);
1810 FETCH l_esc_csr INTO l_esc;
1811 IF (l_esc_csr%NOTFOUND) THEN
1812 l_esc := NULL;
1813 END IF;
1814 CLOSE l_esc_csr;
1815 RETURN l_esc;
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818 CLOSE l_esc_csr;
1819 RETURN NULL;
1820 END GET_TASK_ESC_LEVEL;
1821
1822 FUNCTION item_name(p_item_name IN varchar2)
1823 RETURN varchar2
1824 IS
1825 BEGIN
1826 RETURN p_item_name;
1827 END item_name;
1828
1829 FUNCTION is_flow_history(p_flowtype IN VARCHAR2)
1830 RETURN BOOLEAN
1831 IS
1832 l_err_msg VARCHAR2(4000);
1833
1834 BEGIN
1835 IF p_flowtype = 'HISTORY' THEN
1836 RETURN TRUE;
1837 ELSE
1838 RETURN FALSE;
1839 END IF ;
1840
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 l_err_msg := 'Failed csm_util_pkg.is_flow_history : ' || p_flowtype;
1844 CSM_UTIL_PKG.LOG(l_err_msg, 'CSM_UTIL_PKG.IS_FLOW_HISTORY', FND_LOG.LEVEL_ERROR);
1845 RETURN FALSE;
1846 END is_flow_history;
1847
1848 FUNCTION get_debrief_header_id(p_debrief_header_id in CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE)
1849 RETURN NUMBER
1850 IS
1851 BEGIN
1852 RETURN p_debrief_header_id;
1853 END get_debrief_header_id;
1854
1855
1856 /*R12-Function to return nullable number type for not null numbers*/
1857 FUNCTION get_number(p_number IN NUMBER)
1858 RETURN NUMBER
1859 IS
1860 BEGIN
1861 RETURN p_number;
1862 END get_number;
1863
1864 /*R12-Function to return nullable varchar type for not null varchar2*/
1865 FUNCTION get_varchar(p_varchar IN VARCHAR2)
1866 RETURN VARCHAR2
1867 IS
1868 BEGIN
1869 RETURN p_varchar;
1870 END get_varchar;
1871
1872 /*R12-Function to return nullable date type for not null date*/
1873 FUNCTION get_date(p_date IN DATE)
1874 RETURN DATE
1875 IS
1876 BEGIN
1877 RETURN p_date;
1878 END get_date;
1879
1880 /*R12-Function to get owner's full/group name*/
1881 FUNCTION get_owner_name(p_owner_type_code IN VARCHAR2,p_owner_id IN NUMBER,p_language IN VARCHAR2)
1882 RETURN VARCHAR2
1883 IS
1884
1885 CURSOR c_grp_name(b_grp_id NUMBER,b_lang VARCHAR2) IS
1886 SELECT TL.GROUP_NAME
1887 FROM JTF_RS_GROUPS_B B,
1888 JTF_RS_GROUPS_TL TL
1889 WHERE B.GROUP_ID = b_grp_id
1890 AND B.GROUP_ID = TL.GROUP_ID
1891 AND TL.LANGUAGE=b_lang;
1892
1893 CURSOR c_owner_name(b_owner_id NUMBER) IS
1894 SELECT PF.FULL_NAME
1895 FROM JTF_RS_RESOURCE_EXTNS RES,
1896 PER_ALL_PEOPLE_F PF
1897 WHERE RES.resource_id = b_owner_id
1898 AND RES.SOURCE_ID=PF.PERSON_ID;
1899
1900 l_name JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE := NULL;
1901
1902 BEGIN
1903 IF NVL(p_owner_type_code,'NULL')='RS_GROUP' THEN
1904 OPEN c_grp_name(p_owner_id,p_language);
1905 FETCH c_grp_name INTO l_name;
1906 CLOSE c_grp_name;
1907 ELSIF NVL(p_owner_type_code,'NULL')='RS_EMPLOYEE' THEN
1908 OPEN c_owner_name(p_owner_id);
1909 FETCH c_owner_name INTO l_name;
1910 CLOSE c_owner_name;
1911 END IF;
1912
1913 RETURN l_name;
1914
1915 END get_owner_name;
1916
1917 /*to return the notification's Text attribute ignoring if the attribute doesn't exist*/
1918 FUNCTION get_wf_attrText(p_notification_id IN NUMBER,p_attribute IN VARCHAR2)
1919 RETURN VARCHAR2
1920 IS
1921 BEGIN
1922 RETURN wf_notification.getAttrText(p_notification_id,p_attribute,true);
1923 END;
1924
1925 FUNCTION Get_Datediff_For_Req_UOM(
1926 p_start_date IN DATE,
1927 p_end_date IN DATE,
1928 p_class IN MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
1929 p_to_uom IN CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE,
1930 p_min_uom IN CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE
1931 )
1932 RETURN NUMBER
1933 IS
1934 l_datediffmin NUMBER;
1935 l_converted_value NUMBER;
1936 l_conversion_rate NUMBER;
1937 l_base_value NUMBER;
1938 l_actual_value NUMBER;
1939 l_uom_min CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
1940 l_base_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
1941 l_base_conversion_rate NUMBER;
1942
1943 --Cursor Declarations
1944 CURSOR c_base_uom(c_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE)
1945 IS
1946 SELECT UOM_CODE
1947 FROM MTL_UNITS_OF_MEASURE
1948 WHERE UOM_CLASS = c_uom_class
1949 AND base_uom_flag = 'Y';
1950
1951 CURSOR c_actual_conversion(c_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
1952 c_convert_to_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE)
1953 IS
1954 SELECT conversion_rate
1955 FROM MTL_UOM_CONVERSIONS
1956 WHERE UOM_CLASS = c_uom_class
1957 AND UOM_CODE = c_convert_to_uom
1958 AND inventory_item_id = 0;
1959
1960 CURSOR c_base_conversion(c_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
1961 c_convert_to_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE)
1962 IS
1963 SELECT conversion_rate
1964 FROM MTL_UOM_CONVERSIONS
1965 WHERE UOM_CLASS = c_uom_class
1966 AND UOM_CODE = c_convert_to_uom
1967 AND inventory_item_id = 0;
1968
1969 BEGIN
1970 --Get the difference in Minutes
1971 l_datediffmin :=((p_end_date-p_start_date)*24*60);
1972
1973 OPEN c_base_uom(p_class);
1974 FETCH c_base_uom INTO l_base_uom;
1975 CLOSE c_base_uom;
1976
1977 OPEN c_base_conversion(p_class,p_min_uom);
1978 FETCH c_base_conversion INTO l_base_conversion_rate;
1979 CLOSE c_base_conversion;
1980
1981 OPEN c_actual_conversion(p_class,p_to_uom);
1982 FETCH c_actual_conversion INTO l_conversion_rate;
1983 CLOSE c_actual_conversion;
1984
1985 --converting to base uom
1986 l_base_value := l_base_conversion_rate * l_datediffmin;
1987 --converting to actual uom required
1988 l_actual_value := l_base_value / l_conversion_rate ;
1989
1990 RETURN(ROUND(l_actual_value,2));
1991
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1995 log ( 'Exception occurred in Get_Datediff_For_Req_UOM :' || ' ' || sqlerrm);
1996 END IF;
1997
1998 RAISE;
1999
2000 END Get_Datediff_For_Req_UOM;
2001
2002 --12.1
2003 FUNCTION is_mfs_group(p_group_id NUMBER) RETURN BOOLEAN
2004 IS
2005 CURSOR c_mfs_group(b_group_id NUMBER) IS
2006 SELECT 1
2007 FROM ASG_USER
2008 WHERE GROUP_ID=b_group_id;
2009
2010 l_mfs_grp NUMBER:=0;
2011 BEGIN
2012
2013 OPEN c_mfs_group(p_group_id);
2014 FETCH c_mfs_group INTO l_mfs_grp;
2015 CLOSE c_mfs_group;
2016
2017 IF l_mfs_grp=1 THEN
2018 RETURN TRUE;
2019 END IF;
2020
2021 RETURN FALSE;
2022 END is_mfs_group;
2023
2024 --12.1
2025 FUNCTION get_group_owner(p_group_id NUMBER) RETURN NUMBER
2026 IS
2027 CURSOR c_group_owner(b_group_id NUMBER) IS
2028 SELECT USER_ID
2029 FROM ASG_USER
2030 WHERE OWNER_ID=USER_ID
2031 AND GROUP_ID=b_group_id;
2032
2033 /*
2034 CURSOR c_group_owner(b_group_id NUMBER) IS
2035 SELECT USER_ID
2036 FROM ASG_USER
2037 WHERE ROLE_CODE = 'MFS_OWNER'
2038 AND GROUP_ID=b_group_id;*/
2039
2040 l_grp_owner NUMBER;
2041 BEGIN
2042
2043 OPEN c_group_owner(p_group_id);
2044 FETCH c_group_owner INTO l_grp_owner;
2045 CLOSE c_group_owner;
2046
2047 RETURN NVL(l_grp_owner,-1);
2048
2049 END get_group_owner;
2050
2051 --12.1
2052 FUNCTION from_same_group(p_member1_resource_id NUMBER,p_member2_resource_id NUMBER) RETURN BOOLEAN
2053 IS
2054 CURSOR c_from_same_group(b_owner NUMBER,b_member NUMBER)
2055 IS
2056 SELECT 1
2057 FROM JTF_RS_GROUP_MEMBERS memG
2058 WHERE RESOURCE_ID = b_member
2059 AND EXISTS (SELECT 1 FROM JTF_RS_GROUP_MEMBERS ownG
2060 WHERE ownG.GROUP_ID=memG.GROUP_ID
2061 AND ownG.RESOURCE_ID=b_owner);
2062
2063 l_temp NUMBER :=0;
2064 BEGIN
2065
2066 OPEN c_from_same_group(p_member1_resource_id,p_member2_resource_id);
2067 FETCH c_from_same_group INTO l_temp;
2068 CLOSE c_from_same_group;
2069
2070 IF l_temp=1 THEN
2071 RETURN TRUE;
2072 END IF;
2073
2074 RETURN FALSE;
2075 END from_same_group;
2076
2077 --function to get owner id for any user
2078 FUNCTION get_owner(p_user_id NUMBER) RETURN NUMBER
2079 IS
2080 CURSOR c_get_owner(b_user_id NUMBER)
2081 IS
2082 SELECT OWNER_ID
2083 FROM ASG_USER
2084 WHERE USER_ID= p_user_id;
2085
2086 l_owner_id NUMBER;
2087 BEGIN
2088
2089 OPEN c_get_owner(p_user_id);
2090 FETCH c_get_owner INTO l_owner_id;
2091 CLOSE c_get_owner;
2092
2093 RETURN NVL(l_owner_id,-1);
2094
2095 END get_owner;
2096
2097 --12.1 function to get Group name for a group
2098 FUNCTION get_group_name(p_group_id NUMBER, p_language VARCHAR2) RETURN VARCHAR2
2099 IS
2100 CURSOR c_get_group_name(b_group_id NUMBER, b_language VARCHAR2)
2101 IS
2102 SELECT GROUP_NAME--group_desc is a nullable column hence group_name is selected
2103 FROM jtf_rs_groups_tl
2104 WHERE GROUP_ID = b_group_id
2105 AND LANGUAGE = b_language;
2106
2107 l_group_name VARCHAR2(60) := NULL;
2108 BEGIN
2109
2110 IF p_group_id IS NOT NULL THEN
2111 OPEN c_get_group_name(p_group_id,p_language);
2112 FETCH c_get_group_name INTO l_group_name;
2113 CLOSE c_get_group_name;
2114 END IF;
2115
2116 RETURN l_group_name;
2117
2118 END get_group_name;
2119
2120 /*returns True if the asg user name passed is just/being created by mmu*/
2121 FUNCTION is_new_mmu_user(p_user_name IN VARCHAR2) RETURN BOOLEAN
2122 IS
2123 l_exists NUMBER;
2124 BEGIN
2125
2126 select 1 INTO l_exists
2127 from asg_user
2128 where cookie is not null --user has not synch'ed yet
2129 and enabled ='Y'
2130 and user_name = p_user_name;
2131
2132 select 1 INTO l_exists
2133 from asg_user_pub_resps ar, asg_user au
2134 where synch_disabled='N' -- is being created by MMU
2135 and ar.pub_name='SERVICEP'
2136 and au.useR_name=ar.user_name
2137 and au.user_name = p_user_name;
2138
2139 return false;
2140
2141 EXCEPTION
2142 WHEN Others THEN
2143 return true;
2144 END is_new_mmu_user;
2145
2146 FUNCTION is_html5_user(p_user_name IN VARCHAR2) RETURN BOOLEAN
2147 IS
2148 ln NUMBER;
2149 BEGIN
2150 SELECT 1 INTO ln FROM ASG_USER
2151 WHERE USER_NAME=p_user_name
2152 AND NVL(MULTI_PLATFORM,'N')='Y';
2153 RETURN TRUE;
2154 EXCEPTION WHEN OTHERS THEN
2155 RETURN FALSE;
2156 END is_html5_user;
2157
2158 FUNCTION is_html5_user(p_user_id IN NUMBER) RETURN BOOLEAN
2159 IS
2160 ln NUMBER;
2161 BEGIN
2162 SELECT 1 INTO ln FROM ASG_USER
2163 WHERE USER_ID=p_user_id
2164 AND NVL(MULTI_PLATFORM,'N')='Y';
2165 RETURN TRUE;
2166 EXCEPTION WHEN OTHERS THEN
2167 RETURN FALSE;
2168 END is_html5_user;
2169
2170 FUNCTION get_inq_tab(pi VARCHAR2) RETURN VARCHAR2
2171 IS
2172 l_t VARCHAR2(200);
2173 BEGIN
2174 SELECT INQ_NAME INTO l_t FROM ASG_PUB_ITEM WHERE ITEM_ID =pi;
2175 RETURN l_t;
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178 RETURN NULL;
2179 END;
2180
2181 FUNCTION bool2yn(INP in boolean) return varchar2
2182 IS
2183 BEGIN
2184 IF(INP IS NULL OR NOT INP) THEN
2185 RETURN 'N';
2186 END IF;
2187 RETURN 'Y';
2188 END bool2yn;
2189
2190 FUNCTION get_String_fromList(tab in ASG_DOWNLOAD.ACCESS_LIST) return varchar2
2191 IS
2192 l_res VARCHAR2(4000):='-1';
2193 BEGIN
2194 FOR I in 1..tab.count
2195 LOOP
2196 IF I=1 THEN
2197 l_res:=to_char(tab(i));
2198 ELSE
2199 l_res:=l_res||','||to_char(tab(i));
2200 END IF;
2201 END LOOP;
2202 RETURN l_res;
2203 END get_String_fromList;
2204
2205 FUNCTION set_task_audit(p_new_val in varchar2,p_old_val in varchar2) RETURN VARCHAR2
2206 IS
2207 l_cur_val VARCHAR2(100);
2208 x boolean;
2209 BEGIN
2210 IF p_new_val='N' THEN
2211 l_cur_val:=upper(fnd_profile.value('JTF_TASK_ENABLE_AUDIT'));
2212 IF NVL(l_cur_val,'Y')='Y' THEN
2213 x:=fnd_profile.save('JTF_TASK_ENABLE_AUDIT','N','SITE');
2214 log('Turning Off Audit as Current Audit Profile Value:'||l_cur_val,'CSM_UTIL_PKG.set_task_audit');
2215 END IF;
2216 RETURN l_cur_val;
2217 ELSE
2218 IF p_old_val IS NULL THEN
2219 x:=fnd_profile.delete('JTF_TASK_ENABLE_AUDIT','SITE');
2220 log('Deleting Audit profile as it did not exist before','CSM_UTIL_PKG.set_task_audit');
2221 ELSIF p_old_val='Y' THEN
2222 x:=fnd_profile.save('JTF_TASK_ENABLE_AUDIT','Y','SITE');
2223 log('Re-enabling Audit profile','CSM_UTIL_PKG.set_task_audit');
2224 END IF;
2225 RETURN p_old_val;
2226 END IF;
2227 END set_task_audit;
2228
2229 /* returns user langauge at user level/site level */
2230 FUNCTION get_fnd_user_language( p_user_id fnd_user.user_id%TYPE)
2231 RETURN VARCHAR2
2232 IS
2233 l_profile_option_value VARCHAR2(255) := NULL;
2234 l_profile_option_name VARCHAR2(255) := 'ICX_LANGUAGE';
2235
2236 CURSOR c_get_user_value (c_user_id NUMBER)
2237 IS
2238 SELECT lang.LANGUAGE_CODE
2239 FROM FND_PROFILE_OPTION_VALUES val,
2240 FND_PROFILE_OPTIONS prf,
2241 FND_LANGUAGES lang
2242 WHERE prf.PROFILE_OPTION_ID = val.PROFILE_OPTION_ID
2243 AND prf.PROFILE_OPTION_NAME = l_profile_option_name
2244 AND LEVEL_ID = 10004
2245 AND LEVEL_VALUE = c_user_id
2246 AND val.PROFILE_OPTION_VALUE = lang.NLS_LANGUAGE;
2247
2248 CURSOR c_get_site_value
2249 IS
2250 SELECT lang.LANGUAGE_CODE
2251 FROM FND_PROFILE_OPTION_VALUES val,
2252 FND_PROFILE_OPTIONS prf,
2253 FND_LANGUAGES lang
2254 WHERE prf.PROFILE_OPTION_ID = val.PROFILE_OPTION_ID
2255 AND prf.PROFILE_OPTION_NAME = l_profile_option_name
2256 AND LEVEL_ID = 10001
2257 AND LEVEL_VALUE = 0
2258 AND val.PROFILE_OPTION_VALUE = lang.NLS_LANGUAGE;
2259
2260 BEGIN
2261
2262 IF p_user_id IS NOT NULL THEN
2263 OPEN c_get_user_value (p_user_id);
2264 FETCH c_get_user_value INTO l_profile_option_value;
2265 CLOSE c_get_user_value;
2266 END IF;
2267
2268 IF l_profile_option_value IS NULL THEN
2269 OPEN c_get_site_value;
2270 FETCH c_get_site_value INTO l_profile_option_value;
2271 CLOSE c_get_site_value;
2272 END IF;
2273
2274
2275
2276 -- if profile is not set then return Null
2277 RETURN l_profile_option_value;
2278
2279 EXCEPTION
2280 WHEN OTHERS THEN
2281 RETURN NULL;
2282 END get_fnd_user_language;
2283
2284 END CSM_UTIL_PKG;
[Home] [Help]