1 PACKAGE BODY CSM_UTIL_PKG AS
2 /* $Header: csmeutlb.pls 120.16 2008/02/21 09:02:34 trajasek 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 l_message := message;
62 IF g_initialize_log = TRUE THEN
63 IF (log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
64 FND_LOG.STRING(log_level, module, l_message);
65 END IF;
66 ELSE
67 fnd_log_repository.init();
68 g_initialize_log := TRUE;
69 IF (log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
70 fnd_log.string(log_level, module, l_message);
71 END IF;
72 END IF;
73 END LOG;
74
75 /**
76 Initializes g_acc_refresh_desc_tbl with the informatoin pertaining to
77 master tables to be updated
78 either through concurrent job or workflow activities.
79 */
80 procedure initialize_g_table_desc_tbl
81 IS
82 i NUMBER := 0;
83 BEGIN
84 IF (g_acc_refresh_desc_tbl IS NULL OR g_acc_refresh_desc_tbl.COUNT = 0) THEN
85
86 /* -- CSM_TXN_SUB_TYPES
87 i := i +1;
88 g_acc_refresh_desc_tbl.EXTEND;
89 g_acc_refresh_desc_tbl(i).BACKEND_TABLE_NAME := 'CSI_IB_TXN_TYPES';
90 g_acc_refresh_desc_tbl(i).ACC_TABLE_NAME := 'CSM_IB_TXN_TYPES_ACC';
91 g_acc_refresh_desc_tbl(i).ACC_SEQUENCE_NAME := 'CSM_IB_TXN_TYPES_ACC_S';
92 g_acc_refresh_desc_tbl(i).PRIMARY_KEY_COLUMN := 'SUB_TYPE_ID';
93 g_acc_refresh_desc_tbl(i).PUBLICATION_ITEM_NAME := 'CSF_M_TXN_SUB_TYPES';
94 g_acc_refresh_desc_tbl(i).ACCESS_QUERY :=
95 'select cit.sub_type_id
96 from
97 csi_ib_txn_types cit,
98 csi_source_ib_types cst,
99 csi_txn_types ctt,
100 csi_instance_statuses cis
101 where cst.sub_type_id = cit.sub_type_id
102 and cst.transaction_type_id = ctt.transaction_type_id
103 and cit.src_return_reqd = ''N''
104 and cit.non_src_reference_reqd = ''N''
105 and ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
106 and ctt.source_application_id = 513
107 and cis.instance_status_id(+) = cit.src_status_id
108 and cis.terminated_flag(+) <> ''Y''';
109 */
110
111 --CSM_OBJECT_MAPPINGS
112 i := i +1;
113 g_acc_refresh_desc_tbl.EXTEND;
114 g_acc_refresh_desc_tbl(i).BACKEND_TABLE_NAME := 'JTF_OBJECT_MAPPINGS';
115 g_acc_refresh_desc_tbl(i).ACC_TABLE_NAME := 'CSM_OBJECT_MAPPINGS_ACC';
116 g_acc_refresh_desc_tbl(i).ACC_SEQUENCE_NAME := 'CSM_OBJECT_MAPPINGS_ACC_S';
117 g_acc_refresh_desc_tbl(i).PRIMARY_KEY_COLUMN := 'MAPPING_ID';
118 g_acc_refresh_desc_tbl(i).PUBLICATION_ITEM_NAME := 'CSF_M_OBJECT_MAPPINGS';
119 g_acc_refresh_desc_tbl(i).ACCESS_QUERY :=
120 'select jom.mapping_id
121 from jtf_object_mappings jom
122 WHERE jom.source_object_code IN (''PARTY'', ''TASK'', ''SR'', ''CP'',''OKS_COV_NOTE'',''SD'')
123 AND NVL(jom.end_date, SYSDATE) >= SYSDATE';
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 BEGIN
1509 x_return_status := FND_API.G_RET_STS_SUCCESS;
1510
1511 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1512 log
1513 ( 'Entering DELETE_RECORD');
1514 END IF;
1515
1516 asg_apply.delete_row(p_user_name,
1517 p_tranid,
1518 p_pub_name,
1519 p_seqno,
1520 x_return_status);
1521
1522 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1523 /*** error occurred ***/
1524 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'DELETE_RECORD', 'Unknown error');
1525 p_error_msg := GET_ERROR_MESSAGE_TEXT
1526 (
1527 p_api_error => TRUE
1528 );
1529 END IF;
1530
1531 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1532 log
1533 ( 'Leaving DELETE_RECORD');
1534 END IF;
1535
1536 EXCEPTION WHEN OTHERS THEN
1537 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1538 log
1539 ( 'Exception occurred in DELETE_RECORD:' || ' ' || sqlerrm);
1540 END IF;
1541
1542 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'DELETE_RECORD', sqlerrm);
1543 p_error_msg := GET_ERROR_MESSAGE_TEXT
1544 (
1545 p_api_error => TRUE
1546 );
1547
1548 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1549 log
1550 ( 'Leaving DELETE_RECORD');
1551 END IF;
1552 x_return_status := FND_API.G_RET_STS_ERROR;
1553 END DELETE_RECORD;
1554
1555 /***
1556 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure
1557 when a record failed to be processed and needs to be deferred and rejected from mobile.
1558 ***/
1559 PROCEDURE DEFER_RECORD
1560 (
1561 p_user_name IN VARCHAR2,
1562 p_tranid IN NUMBER,
1563 p_seqno IN NUMBER,
1564 p_pk IN VARCHAR2,
1565 p_object_name IN VARCHAR2,
1566 p_pub_name IN VARCHAR2,
1567 p_error_msg IN VARCHAR2,
1568 x_return_status IN OUT NOCOPY VARCHAR2,
1569 p_dml_type IN VARCHAR2
1570 ) IS
1571 BEGIN
1572 x_return_status := FND_API.G_RET_STS_SUCCESS;
1573
1574 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1575 log
1576 ( 'Entering DEFER_RECORD');
1577 END IF;
1578
1579 asg_defer.defer_row(p_user_name,
1580 p_tranid,
1581 p_pub_name,
1582 p_seqno,
1583 p_error_msg,
1584 x_return_status);
1585 /*** check if defer was successfull ***/
1586 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1587 /*** no -> log and return error ***/
1588 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1589 log
1590 ( 'asg_defer.defer_row failed:' || ' ' || p_error_msg
1591 );
1592 END IF;
1593
1594 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1595 log
1596 ( 'Leaving DEFER_RECORD');
1597 END IF;
1598 RETURN;
1599 END IF;
1600
1601 -- ANURAG
1602 /*** defer successful -> reject record only for inserts ***/
1603 IF p_dml_type = 'I' THEN
1604 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1605 log
1606 ( 'Rejecting record');
1607 END IF;
1608
1609 asg_defer.reject_row(p_user_name,
1610 p_tranid,
1611 p_pub_name,
1612 p_seqno,
1613 p_error_msg,
1614 x_return_status);
1615 /*** check if reject was successfull ***/
1616 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1617 /*** no -> log error ***/
1618 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1619 log
1620 ( 'asg_defer.reject_row failed:' || ' ' || p_error_msg);
1621 END IF;
1622 END IF;
1623 END IF; --end DML = 'I'
1624
1625 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1626 log
1627 ( 'Leaving DEFER_RECORD');
1628 END IF;
1629 EXCEPTION WHEN OTHERS THEN
1630 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1631 log
1632 ( 'Exception occurred in DEFER_RECORD:' || ' ' || sqlerrm);
1633 END IF;
1634
1635 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1636 log
1637 ( 'Leaving DEFER_RECORD');
1638 END IF;
1639
1640 x_return_status := FND_API.G_RET_STS_ERROR;
1641 END DEFER_RECORD;
1642
1643 /***
1644 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure
1645 when the PK of the inserted record is created in the API.
1646 We need to remove the local PK from local
1647 ***/
1648 PROCEDURE REJECT_RECORD
1649 (
1650 p_user_name IN VARCHAR2,
1651 p_tranid IN NUMBER,
1652 p_seqno IN NUMBER,
1653 p_pk IN VARCHAR2,
1654 p_object_name IN VARCHAR2,
1655 p_pub_name IN VARCHAR2,
1656 p_error_msg IN VARCHAR2,
1657 x_return_status IN OUT NOCOPY VARCHAR2
1658 ) IS
1659 BEGIN
1660 x_return_status := FND_API.G_RET_STS_SUCCESS;
1661
1662 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1663 log
1664 ( 'Entering REJECT_RECORD');
1665 END IF;
1666
1667 asg_defer.reject_row(p_user_name,
1668 p_tranid,
1669 p_pub_name,
1670 p_seqno,
1671 p_error_msg,
1672 x_return_status);
1673 /*** check if reject was successfull ***/
1674 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1675 /*** no -> log error ***/
1676 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1677 log
1678 ( 'asg_defer.reject_row failed:' || ' ' || p_error_msg);
1679 END IF;
1680 END IF;
1681
1682 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1683 log
1684 ( 'Leaving REJECT_RECORD');
1685 END IF;
1686 EXCEPTION WHEN OTHERS THEN
1687 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1688 log
1689 ( 'Exception occurred in REJECT_RECORD:' || ' ' || sqlerrm);
1690 END IF;
1691
1692 IF g_debug_level = CSM_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1693 log
1694 ( 'Leaving REJECT_RECORD');
1695 END IF;
1696
1697 x_return_status := FND_API.G_RET_STS_ERROR;
1698 END REJECT_RECORD;
1699
1700 /* Two functions to check if field service palm is enabled. */
1701
1702 FUNCTION IS_FIELD_SERVICE_PALM_ENABLED
1703 RETURN BOOLEAN
1704 IS
1705 l_option_value VARCHAR2(1);
1706 l_responsibility_id NUMBER;
1707 l_application_id NUMBER;
1708
1709 CURSOR l_get_resp_id IS
1710 SELECT resp.APPLICATION_ID, resp.RESPONSIBILITY_ID
1711 FROM FND_RESPONSIBILITY resp, fnd_application app
1712 WHERE resp.RESPONSIBILITY_KEY = 'OMFS_PALM'
1713 AND SYSDATE BETWEEN nvl(resp.start_date, sysdate) AND nvl(resp.end_date, sysdate)
1714 AND app.application_id = resp.application_id
1715 AND app.application_short_name = 'CSM';
1716
1717 BEGIN
1718 OPEN l_get_resp_id;
1719 FETCH l_get_resp_id INTO l_application_id, l_responsibility_id;
1720 CLOSE l_get_resp_id;
1721
1722 l_option_value := fnd_profile.value_specific('JTM_MOB_APPS_ENABLED', null,
1723 l_responsibility_id, l_application_id);
1724
1725 -- CSM_UTIL_PKG.LOG('YL: IS_FIELD_SERVICE_PALM_ENABLED = ' || l_option_value);
1726
1727 IF ( l_option_value = 'Y' ) THEN
1728 RETURN TRUE;
1729 END IF;
1730
1731 RETURN FALSE;
1732 EXCEPTION
1733 WHEN NO_DATA_FOUND THEN
1734 -- CSM_UTIL_PKG.LOG('YL: IS_FIELD_SERVICE_PALM_ENABLED EXCEPTION ');
1735 RETURN FALSE;
1736 WHEN OTHERS THEN
1737 -- CSM_UTIL_PKG.LOG('YL: IS_FIELD_SERVICE_PALM_ENABLED EXCEPTION ');
1738 RAISE;
1739 END IS_FIELD_SERVICE_PALM_ENABLED;
1740
1741 /* GET_TASK_ESC_LEVEL
1742 */
1743 FUNCTION GET_TASK_ESC_LEVEL( p_task_id IN NUMBER) RETURN VARCHAR2
1744 IS
1745 CURSOR l_esc_csr (b_task_id NUMBER) IS
1746 SELECT esc.escalation_level
1747 FROM jtf_tasks_b tasks,
1748 jtf_task_references_vl ref,
1749 jtf_tasks_b esc
1750 WHERE ref.object_id = tasks.task_id
1751 and ref.object_type_code = 'TASK'
1752 and ref.reference_code = 'ESC'
1753 and ref.task_id = esc.task_id
1754 and esc.source_object_type_code = 'ESC'
1755 and tasks.task_id = b_task_id;
1756 l_esc VARCHAR2(30) := NULL;
1757 BEGIN
1758 OPEN l_esc_csr (p_task_id);
1759 FETCH l_esc_csr INTO l_esc;
1760 IF (l_esc_csr%NOTFOUND) THEN
1761 l_esc := NULL;
1762 END IF;
1763 CLOSE l_esc_csr;
1764 RETURN l_esc;
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 CLOSE l_esc_csr;
1768 RETURN NULL;
1769 END GET_TASK_ESC_LEVEL;
1770
1771 FUNCTION item_name(p_item_name IN varchar2)
1772 RETURN varchar2
1773 IS
1774 BEGIN
1775 RETURN p_item_name;
1776 END item_name;
1777
1778 FUNCTION is_flow_history(p_flowtype IN VARCHAR2)
1779 RETURN BOOLEAN
1780 IS
1781 l_err_msg VARCHAR2(4000);
1782
1783 BEGIN
1784 IF p_flowtype = 'HISTORY' THEN
1785 RETURN TRUE;
1786 ELSE
1787 RETURN FALSE;
1788 END IF ;
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 l_err_msg := 'Failed csm_util_pkg.is_flow_history : ' || p_flowtype;
1793 CSM_UTIL_PKG.LOG(l_err_msg, 'CSM_UTIL_PKG.IS_FLOW_HISTORY', FND_LOG.LEVEL_ERROR);
1794 RETURN FALSE;
1795 END is_flow_history;
1796
1797 FUNCTION get_debrief_header_id(p_debrief_header_id in CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE)
1798 RETURN NUMBER
1799 IS
1800 BEGIN
1801 RETURN p_debrief_header_id;
1802 END get_debrief_header_id;
1803
1804
1805 /*R12-Function to return nullable number type for not null numbers*/
1806 FUNCTION get_number(p_number IN NUMBER)
1807 RETURN NUMBER
1808 IS
1809 BEGIN
1810 RETURN p_number;
1811 END get_number;
1812
1813 /*R12-Function to return nullable varchar type for not null varchar2*/
1814 FUNCTION get_varchar(p_varchar IN VARCHAR2)
1815 RETURN VARCHAR2
1816 IS
1817 BEGIN
1818 RETURN p_varchar;
1819 END get_varchar;
1820
1821 /*R12-Function to return nullable date type for not null date*/
1822 FUNCTION get_date(p_date IN DATE)
1823 RETURN DATE
1824 IS
1825 BEGIN
1826 RETURN p_date;
1827 END get_date;
1828
1829 /*R12-Function to get owner's full/group name*/
1830 FUNCTION get_owner_name(p_owner_type_code IN VARCHAR2,p_owner_id IN NUMBER,p_language IN VARCHAR2)
1831 RETURN VARCHAR2
1832 IS
1833
1834 CURSOR c_grp_name(b_grp_id NUMBER,b_lang VARCHAR2) IS
1835 SELECT TL.GROUP_NAME
1836 FROM JTF_RS_GROUPS_B B,
1837 JTF_RS_GROUPS_TL TL
1838 WHERE B.GROUP_ID = b_grp_id
1839 AND B.GROUP_ID = TL.GROUP_ID
1840 AND TL.LANGUAGE=b_lang;
1841
1842 CURSOR c_owner_name(b_owner_id NUMBER) IS
1843 SELECT PF.FULL_NAME
1844 FROM JTF_RS_RESOURCE_EXTNS RES,
1845 PER_ALL_PEOPLE_F PF
1846 WHERE RES.resource_id = b_owner_id
1847 AND RES.SOURCE_ID=PF.PERSON_ID;
1848
1849 l_name JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE := NULL;
1850
1851 BEGIN
1852 IF NVL(p_owner_type_code,'NULL')='RS_GROUP' THEN
1853 OPEN c_grp_name(p_owner_id,p_language);
1854 FETCH c_grp_name INTO l_name;
1855 CLOSE c_grp_name;
1856 ELSIF NVL(p_owner_type_code,'NULL')='RS_EMPLOYEE' THEN
1857 OPEN c_owner_name(p_owner_id);
1858 FETCH c_owner_name INTO l_name;
1859 CLOSE c_owner_name;
1860 END IF;
1861
1862 RETURN l_name;
1863
1864 END get_owner_name;
1865
1866 /*to return the notification's Text attribute ignoring if the attribute doesn't exist*/
1867 FUNCTION get_wf_attrText(p_notification_id IN NUMBER,p_attribute IN VARCHAR2)
1868 RETURN VARCHAR2
1869 IS
1870 BEGIN
1871 RETURN wf_notification.getAttrText(p_notification_id,p_attribute,true);
1872 END;
1873
1874 FUNCTION Get_Datediff_For_Req_UOM(
1875 p_start_date IN DATE,
1876 p_end_date IN DATE,
1877 p_class IN MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
1878 p_to_uom IN CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE,
1879 p_min_uom IN CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE
1880 )
1881 RETURN NUMBER
1882 IS
1883 l_datediffmin NUMBER;
1884 l_converted_value NUMBER;
1885 l_conversion_rate NUMBER;
1886 l_base_value NUMBER;
1887 l_actual_value NUMBER;
1888 l_uom_min CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
1889 l_base_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
1890 l_base_conversion_rate NUMBER;
1891
1892 --Cursor Declarations
1893 CURSOR c_base_uom(c_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE)
1894 IS
1895 SELECT UOM_CODE
1896 FROM MTL_UNITS_OF_MEASURE
1897 WHERE UOM_CLASS = c_uom_class
1898 AND base_uom_flag = 'Y';
1899
1900 CURSOR c_actual_conversion(c_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
1901 c_convert_to_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE)
1902 IS
1903 SELECT conversion_rate
1904 FROM MTL_UOM_CONVERSIONS
1905 WHERE UOM_CLASS = c_uom_class
1906 AND UOM_CODE = c_convert_to_uom
1907 AND inventory_item_id = 0;
1908
1909 CURSOR c_base_conversion(c_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
1910 c_convert_to_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE)
1911 IS
1912 SELECT conversion_rate
1913 FROM MTL_UOM_CONVERSIONS
1914 WHERE UOM_CLASS = c_uom_class
1915 AND UOM_CODE = c_convert_to_uom
1916 AND inventory_item_id = 0;
1917
1918 BEGIN
1919 --Get the difference in Minutes
1920 l_datediffmin :=((p_end_date-p_start_date)*24*60);
1921
1922 OPEN c_base_uom(p_class);
1923 FETCH c_base_uom INTO l_base_uom;
1924 CLOSE c_base_uom;
1925
1926 OPEN c_base_conversion(p_class,p_min_uom);
1927 FETCH c_base_conversion INTO l_base_conversion_rate;
1928 CLOSE c_base_conversion;
1929
1930 OPEN c_actual_conversion(p_class,p_to_uom);
1931 FETCH c_actual_conversion INTO l_conversion_rate;
1932 CLOSE c_actual_conversion;
1933
1934 --converting to base uom
1935 l_base_value := l_base_conversion_rate * l_datediffmin;
1936 --converting to actual uom required
1937 l_actual_value := l_base_value / l_conversion_rate ;
1938
1939 RETURN(ROUND(l_actual_value,2));
1940
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1944 log ( 'Exception occurred in Get_Datediff_For_Req_UOM :' || ' ' || sqlerrm);
1945 END IF;
1946
1947 RAISE;
1948
1949 END Get_Datediff_For_Req_UOM;
1950
1951 --12.1
1952 FUNCTION is_mfs_group(p_group_id NUMBER) RETURN BOOLEAN
1953 IS
1954 CURSOR c_mfs_group(b_group_id NUMBER) IS
1955 SELECT 1
1956 FROM ASG_USER
1957 WHERE GROUP_ID=b_group_id;
1958
1959 l_mfs_grp NUMBER:=0;
1960 BEGIN
1961
1962 OPEN c_mfs_group(p_group_id);
1963 FETCH c_mfs_group INTO l_mfs_grp;
1964 CLOSE c_mfs_group;
1965
1966 IF l_mfs_grp=1 THEN
1967 RETURN TRUE;
1968 END IF;
1969
1970 RETURN FALSE;
1971 END is_mfs_group;
1972
1973 --12.1
1974 FUNCTION get_group_owner(p_group_id NUMBER) RETURN NUMBER
1975 IS
1976 CURSOR c_group_owner(b_group_id NUMBER) IS
1977 SELECT USER_ID
1978 FROM ASG_USER
1979 WHERE OWNER_ID=USER_ID
1980 AND GROUP_ID=b_group_id;
1981
1982 /*
1983 CURSOR c_group_owner(b_group_id NUMBER) IS
1984 SELECT USER_ID
1985 FROM ASG_USER
1986 WHERE ROLE_CODE = 'MFS_OWNER'
1987 AND GROUP_ID=b_group_id;*/
1988
1989 l_grp_owner NUMBER;
1990 BEGIN
1991
1992 OPEN c_group_owner(p_group_id);
1993 FETCH c_group_owner INTO l_grp_owner;
1994 CLOSE c_group_owner;
1995
1996 RETURN NVL(l_grp_owner,-1);
1997
1998 END get_group_owner;
1999
2000 --12.1
2001 FUNCTION from_same_group(p_member1_resource_id NUMBER,p_member2_resource_id NUMBER) RETURN BOOLEAN
2002 IS
2003 CURSOR c_from_same_group(b_owner NUMBER,b_member NUMBER)
2004 IS
2005 SELECT 1
2006 FROM JTF_RS_GROUP_MEMBERS memG
2007 WHERE RESOURCE_ID = b_member
2008 AND EXISTS (SELECT 1 FROM JTF_RS_GROUP_MEMBERS ownG
2009 WHERE ownG.GROUP_ID=memG.GROUP_ID
2010 AND ownG.RESOURCE_ID=b_owner);
2011
2012 l_temp NUMBER :=0;
2013 BEGIN
2014
2015 OPEN c_from_same_group(p_member1_resource_id,p_member2_resource_id);
2016 FETCH c_from_same_group INTO l_temp;
2017 CLOSE c_from_same_group;
2018
2019 IF l_temp=1 THEN
2020 RETURN TRUE;
2021 END IF;
2022
2023 RETURN FALSE;
2024 END from_same_group;
2025
2026 --function to get owner id for any user
2027 FUNCTION get_owner(p_user_id NUMBER) RETURN NUMBER
2028 IS
2029 CURSOR c_get_owner(b_user_id NUMBER)
2030 IS
2031 SELECT OWNER_ID
2032 FROM ASG_USER
2033 WHERE USER_ID= p_user_id;
2034
2035 l_owner_id NUMBER;
2036 BEGIN
2037
2038 OPEN c_get_owner(p_user_id);
2039 FETCH c_get_owner INTO l_owner_id;
2040 CLOSE c_get_owner;
2041
2042 RETURN NVL(l_owner_id,-1);
2043
2044 END get_owner;
2045
2046 --12.1 function to get Group name for a group
2047 FUNCTION get_group_name(p_group_id NUMBER, p_language VARCHAR2) RETURN VARCHAR2
2048 IS
2049 CURSOR c_get_group_name(b_group_id NUMBER, b_language VARCHAR2)
2050 IS
2051 SELECT GROUP_NAME--group_desc is a nullable column hence group_name is selected
2052 FROM jtf_rs_groups_tl
2053 WHERE GROUP_ID = b_group_id
2054 AND LANGUAGE = b_language;
2055
2056 l_group_name VARCHAR2(60) := NULL;
2057 BEGIN
2058
2059 IF p_group_id IS NOT NULL THEN
2060 OPEN c_get_group_name(p_group_id,p_language);
2061 FETCH c_get_group_name INTO l_group_name;
2062 CLOSE c_get_group_name;
2063 END IF;
2064
2065 RETURN l_group_name;
2066
2067 END get_group_name;
2068
2069 END CSM_UTIL_PKG;
[Home] [Help]