[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_RESP_INFO_PVT
Source
1 PACKAGE BODY JTF_UM_RESP_INFO_PVT as
2 /*$Header: JTFVRESB.pls 120.2 2006/03/16 22:11:44 vimohan ship $*/
3
4 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_RESP_INFO_PVT';
5 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
6 L_DELIMITING_CHARACTER CONSTANT VARCHAR2(1) := ';';
7
8 function format_output(p_input_string in varchar2,
9 p_source_type in varchar2) return varchar2 is
10
11 l_procedure_name CONSTANT varchar2(30) := 'format_output';
12 l_message_name varchar2(255);
13 l_token_name varchar2(100);
14 BEGIN
15
16 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
17 p_message => l_procedure_name
18 );
19
20 if l_is_debug_parameter_on then
21 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
22 p_message => 'p_input_string:' || p_input_string || '+' || 'p_source_type:' || p_source_type
23 );
24 end if;
25
26 IF p_source_type = 'USERTYPE' THEN
27 l_message_name := 'JTA_UM_USERTYPE_SOURCE';
28 l_token_name := 'USERTYPE_NAME';
29 ELSIF p_source_type = 'ENROLLMENT' THEN
30 l_message_name := 'JTA_UM_ENROLLMENT_SOURCE';
31 l_token_name := 'ENROLLMENT_NAME';
32 ELSE
33 l_message_name := 'JTA_UM_UNKNOWN_SOURCE';
34 END IF;
35
36 fnd_message.set_name('JTF',l_message_name);
37
38 IF p_source_type = 'USERTYPE' OR p_source_type = 'ENROLLMENT' THEN
39 fnd_message.set_token(l_token_name,p_input_string);
40 END IF;
41
42 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
43 p_message => l_procedure_name
44 );
45
46 return fnd_message.get;
47
48 END format_output;
49
50
51 /**
52 * Procedure : GET_RESP_INFO_SOURCE
53 * Type : Private
54 * Pre_reqs : None
55 * Description : Returns the responsibility details and source for a user
56 * Parameters :
57 * input parameters
58 * @param p_user_id
59 * description: The user_id of a user
60 * required : Y
61 * validation : Must be a valid user id
62 * output parameters
63 * x_result: RESP_INFO_TABLE_TYPE
64 */
65 procedure GET_RESP_INFO_SOURCE(
66 p_user_id in number,
67 x_result out NOCOPY RESP_INFO_TABLE_TYPE
68 ) IS
69
70 l_procedure_name CONSTANT varchar2(30) := 'GET_RESP_INFO_SOURCE';
71 CURSOR FIND_RESP_INFO IS SELECT FR.RESPONSIBILITY_ID RESP_ID, FR.APPLICATION_ID APP_ID, FR.RESPONSIBILITY_NAME RESP_NAME, FR.RESPONSIBILITY_KEY RESP_KEY
72 FROM FND_RESPONSIBILITY_VL FR, FND_USER_RESP_GROUPS FG
73 WHERE FR.RESPONSIBILITY_ID = FG.RESPONSIBILITY_ID
74 AND FR.APPLICATION_ID = FG.RESPONSIBILITY_APPLICATION_ID
75 AND FG.USER_ID = p_user_id
76 AND NVL(FG.END_DATE, SYSDATE +1) > SYSDATE
77 AND FG.START_DATE < SYSDATE
78 AND FR.VERSION = 'W'
79 ORDER BY FR.RESPONSIBILITY_NAME;
80
81 l_resp_key FND_RESPONSIBILITY_VL.RESPONSIBILITY_KEY%TYPE;
82
83 CURSOR FIND_RESP_SOURCE IS
84 SELECT SOURCE_TYPE, SOURCE_NAME FROM
85 (
86 SELECT 'USERTYPE' SOURCE_TYPE, UT.USERTYPE_SHORTNAME SOURCE_NAME
87 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_USERTYPE_RESP UTRESP, JTF_UM_USERTYPE_REG UTREG
88 WHERE UT.USERTYPE_ID = UTRESP.USERTYPE_ID
89 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
90 AND UTREG.USER_ID = p_user_id
91 AND UTRESP.RESPONSIBILITY_KEY = l_resp_key
92 AND NVL(UTRESP.EFFECTIVE_END_DATE, SYSDATE +1) > SYSDATE
93 AND NVL(UTREG.EFFECTIVE_END_DATE, SYSDATE +1) > SYSDATE
94 AND UTRESP.EFFECTIVE_START_DATE < SYSDATE
95 AND UTREG.EFFECTIVE_START_DATE < SYSDATE
96 AND UTREG.STATUS_CODE = 'APPROVED'
97
98 UNION ALL
99
100 SELECT 'ENROLLMENT' SOURCE_TYPE, SUB.SUBSCRIPTION_NAME SOURCE_NAME
101 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_SUBSCRIPTION_RESP SUBRESP, JTF_UM_SUBSCRIPTION_REG SUBREG
102 WHERE SUB.SUBSCRIPTION_ID = SUBRESP.SUBSCRIPTION_ID
103 AND SUB.SUBSCRIPTION_ID = SUBREG.SUBSCRIPTION_ID
104 AND SUBREG.USER_ID = p_user_id
105 AND SUBRESP.RESPONSIBILITY_KEY = l_resp_key
106 AND NVL(SUBRESP.EFFECTIVE_END_DATE, SYSDATE +1) > SYSDATE
107 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE +1) > SYSDATE
108 AND SUBRESP.EFFECTIVE_START_DATE < SYSDATE
109 AND SUBREG.EFFECTIVE_START_DATE < SYSDATE
110 AND SUBREG.STATUS_CODE = 'APPROVED'
111 ) respSources order by SOURCE_TYPE,SOURCE_NAME;
112
113 i NUMBER := 1;
114 l_formatted_output varchar2(200);
115 l_delimiter varchar2(1) := '';
116
117 BEGIN
118
119 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
120 p_message => l_procedure_name
121 );
122
123 if l_is_debug_parameter_on then
124 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
125 p_message => 'p_user_id:' || p_user_id
126 );
127 end if;
128
129
130 FOR j in FIND_RESP_INFO LOOP
131
132 x_result(i).RESP_ID := j.RESP_ID;
133 x_result(i).APP_ID := j.APP_ID;
134 x_result(i).RESP_NAME := j.RESP_NAME;
135 x_result(i).RESP_KEY := j.RESP_KEY;
136 l_resp_key := j.RESP_KEY;
137 x_result(i).RESP_SOURCE := '';
138
139 -- Set the source
140
141 FOR k in FIND_RESP_SOURCE LOOP
142
143 l_formatted_output := format_output(k.SOURCE_NAME,RTRIM(k.SOURCE_TYPE));
144 x_result(i).RESP_SOURCE := x_result(i).RESP_SOURCE || l_delimiter || l_formatted_output;
145
146 l_delimiter := L_DELIMITING_CHARACTER;
147
148 END LOOP;
149
150 l_delimiter := '';
151
152 -- Set the source to Unknown, if we do not know it.
153
154 IF x_result(i).RESP_SOURCE IS NULL THEN
155
156 x_result(i).RESP_SOURCE := format_output('','');
157
158 END IF;
159
160 i := i + 1;
161
162 END LOOP;
163
164 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
165 p_message => l_procedure_name
166 );
167
168 END GET_RESP_INFO_SOURCE;
169 end JTF_UM_RESP_INFO_PVT;