[Home] [Help]
PACKAGE BODY: APPS.IBE_JTA_INTEGRATION_GRP
Source
1 PACKAGE BODY IBE_JTA_INTEGRATION_GRP AS
2 /* $Header: IBEVUREJB.pls 120.0.12010000.2 2008/08/06 08:39:15 saradhak ship $ */
3
4
5 /*+====================================================================
6 | FUNCTION NAME
7 | postRejection
8 |
9 | DESCRIPTION
10 | This function is seeded as a subscription to the rejection event
11 |
12 | USAGE
13 | - Inactivates the contact associated with the rejected username.
14 |
15 | REFERENCED APIS
16 | This API calls the following APIs
17 | - ibe_party_v2pvt.Update_Party_Status
18 | - PRM_USER_PVT.INACTIVATEPARTNERUSER
19 +======================================================================*/
20
21 FUNCTION postRejection(
22 p_subscription_guid IN RAW,
23 p_event IN OUT NOCOPY wf_event_t)
24 RETURN VARCHAR2
25 IS
26
27 l_key VARCHAR2(240) := p_event.GetEventKey();
28 l_id NUMBER;
29 l_userreg_id NUMBER;
30 l_usertype_key VARCHAR2(240);
31 l_usertype_appId VARCHAR2(240);
32 l_usertype_partial VARCHAR2(240);
33 l_party_id NUMBER;
34 l_user_id NUMBER;
35 l_change_org_status VARCHAR2(240);
36 l_return_status VARCHAR2(1);
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(240);
39 l_usertype_partner VARCHAR2(240);
40
41 cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
42 fdbk BINARY_INTEGER;
43 x_prm_return_status VARCHAR2(40);
44 x_prm_msg_count NUMBER;
45 x_prm_msg_data VARCHAR2(40);
46
47 /*
48 Cursor C_get_party_id(c_reg_id NUMBER) IS
49 select fu.customer_id from
50 fnd_user fu, jtf_um_usertype_reg jureg
51 where jureg.usertype_reg_id=c_reg_id
52 and jureg.user_id=fu.user_id;
53 */
54
55 Cursor C_get_user_id(c_reg_id NUMBER) IS
56 select usr.user_id from
57 fnd_user usr, jtf_um_usertype_reg jureg
58 where jureg.usertype_reg_id=c_reg_id
59 and usr.user_id = jureg.user_id;
60
61 BEGIN
62
63 -- fnd_global.apps_initialize(1007888,22372,671);
64
65 IBE_UTIL.debug('Inside postRejection procedure');
66 IBE_UTIL.debug('l_Key is '|| l_key);
67
68 -- Check for the event and get the required WF parameters
69
70 IBE_UTIL.debug('Getting wf params');
71 l_userreg_id := p_event.getValueForParameter(G_USERTYPEREG_ID);
72 l_usertype_key := p_event.getValueForParameter(G_USERTYPE_KEY);
73 l_usertype_appId := p_event.getValueForParameter(G_USERTYPE_APPID);
74 l_party_id := p_event.getValueForParameter(G_USER_CUSTOMER_ID);
75 IBE_UTIL.debug('l_userreg_id=' || l_userreg_id || 'l_usertype_key=' || l_usertype_key || 'l_usertype_appId=' || l_usertype_appId || 'l_party_id=' || l_party_id);
76
77 -- Skip inactivation if username is not released
78
79 OPEN C_get_user_id(l_userreg_id);
80 FETCH C_get_user_id into l_user_id;
81 CLOSE C_get_user_id;
82
83 IF (l_user_id is null) THEN
84
85 IBE_UTIL.debug('l_user_id is released. Hence continue to inactivate contact details');
86
87 IF (l_usertype_appId = '671' or l_usertype_appId = '672') THEN
88
89 -- Skip inactivation for partial registrations
90 -- If l_usertype_key is not in Partial_register_usertypes_lookUp Then
91
92 l_usertype_partial := getIsPartialRegistrationUser(l_userreg_id);
93 IBE_UTIL.debug('l_usertype_partial=' || l_usertype_partial);
94
95 If l_usertype_partial = FND_API.G_TRUE Then
96 RETURN 'SUCCESS';
97 Else
98 -- Get the party_id corresponding to the l_Userreg_id
99 /*
100 OPEN C_get_party_id(l_userreg_id);
101 FETCH C_get_party_id into l_party_id;
102 CLOSE C_get_party_id;
103 */
104
105 IBE_UTIL.debug('l_party_id=' || l_party_id);
106
107 If l_party_id = null Then
108 RETURN 'SUCCESS';
109 End If;
110
111 -- For B2B, decide if Company shall also be inactivated?
112 l_change_org_status:=getIsUserCompanyToBeExpired(l_party_id);
113
114 IBE_UTIL.debug('l_change_org_status=' || l_change_org_status);
115
116 IBE_UTIL.debug('Calling Inactivation API');
117 -- Call inactivation API
118 ibe_party_v2pvt.Update_Party_Status(
119 p_party_id=>l_party_id,
120 p_party_status=>'I',
121 p_change_org_status=>l_change_org_status,
122 p_commit=>FND_API.G_TRUE,
123 x_return_status=>l_return_status,
124 x_msg_count=>l_msg_count,
125 x_msg_data=>l_msg_data
126 ) ;
127 IBE_UTIL.debug('Called Inactivation API');
128
129 -- If ERROR, propogate.
130 If l_return_status = FND_API.G_RET_STS_ERROR Then
131 WF_CORE.CONTEXT('IBE', 'postRejection',
132 p_event.getEventName(), p_subscription_guid);
133 WF_EVENT.setErrorInfo(p_event, 'l_msg_data=' || l_msg_data);
134 RETURN 'ERROR';
135 End If;
136
137 /* Bug 7145499 : PRM team responded that no records to inactivate in their tables
138 on rejection
139
140 -- If Partner Usertype, call PRM API using dynamic sql, through dbms_sql
141
142 l_usertype_partner := getIsPartnerUser(l_userreg_id);
143 IBE_UTIL.debug('l_usertype_partner=' || l_usertype_partner);
144
145 If l_usertype_partner = FND_API.G_TRUE Then
146
147 DBMS_SQL.PARSE (cur,
148 'BEGIN PRM_USER_PVT.INACTIVATEPARTNERUSER(:userid, :usertype, :appid, :partyid, :x_returnstatus, :x_msgcount, :x_msgdata); END;',
149 DBMS_SQL.NATIVE);
150 DBMS_SQL.BIND_VARIABLE (cur, 'usertype', l_usertype_key);
151 DBMS_SQL.BIND_VARIABLE (cur, 'appid', l_usertype_appId);
152 DBMS_SQL.BIND_VARIABLE (cur, 'partyid', l_party_id);
153 DBMS_SQL.BIND_VARIABLE (cur, 'x_returnstatus', x_prm_return_status);
154 DBMS_SQL.BIND_VARIABLE (cur, 'x_msgcount', x_prm_msg_count);
155 DBMS_SQL.BIND_VARIABLE (cur, 'x_msgdata', x_prm_msg_data);
156
157 fdbk := DBMS_SQL.EXECUTE (cur);
158 DBMS_SQL.VARIABLE_VALUE (cur, 'x_returnstatus', x_prm_return_status);
159 DBMS_SQL.VARIABLE_VALUE (cur, 'x_msgcount', x_prm_msg_count);
160 DBMS_SQL.VARIABLE_VALUE (cur, 'x_msgdata', x_prm_msg_data);
161
162 IBE_UTIL.debug('x_prm_return_status ' || x_prm_return_status);
163 IBE_UTIL.debug('x_prm_msg_count ' || to_char(x_prm_msg_count));
164 IBE_UTIL.debug('x_prm_msg_data ' || x_prm_msg_data);
165
166 If x_prm_return_status = FND_API.G_FALSE Then
167 DBMS_SQL.CLOSE_CURSOR (cur);
168 IBE_UTIL.debug('Returning ERROR due to PRM');
169 WF_CORE.CONTEXT('IBE', 'postRejection',
170 p_event.getEventName(), p_subscription_guid);
171 WF_EVENT.setErrorInfo(p_event, 'ERROR due to PRM : x_prm_msg_data=' || x_prm_msg_data);
172 RETURN 'ERROR';
173 End If;
174
175 End If;
176
177 DBMS_SQL.CLOSE_CURSOR (cur);
178 */
179 End If;
180
181 END IF;
182 ELSE
183 IBE_UTIL.debug('l_user_id is not released. Hence skipping inactivation of contact details');
184 END IF;
185
186 IBE_UTIL.debug('Returning SUCCESS');
187 RETURN 'SUCCESS';
188
189 EXCEPTION
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 WF_CORE.CONTEXT('IBE', 'postRejection',
192 p_event.getEventName(), p_subscription_guid);
193 WF_EVENT.setErrorInfo(p_event, 'UNEXPECTED ERROR');
194 RETURN 'ERROR';
195 WHEN OTHERS THEN
196 WF_CORE.CONTEXT('IBE', 'postRejection',
197 p_event.getEventName(), p_subscription_guid);
198 WF_EVENT.setErrorInfo(p_event, 'ERROR');
199 RETURN 'ERROR';
200 END;
201
202
203
204 /*+====================================================================
205 | FUNCTION NAME
206 | getIsUserCompanyToBeExpired
207 |
208 | DESCRIPTION
209 | This API is called by postRejection
210 |
211 | USAGE
212 | - To determine if Company details are also to be inactivated?
213 |
214 | REFERENCED APIS
215 +======================================================================*/
216
217 FUNCTION getIsUserCompanyToBeExpired(
218 p_contact_party_id IN NUMBER)
219 RETURN VARCHAR2
220 IS
221 l_company_rel_count NUMBER;
222
223 BEGIN
224 select count(*) into l_company_rel_count from
225 hz_relationships hr
226 where hr.object_id in(
227 select object_id from hz_relationships
228 where party_id=p_contact_party_id
229 and relationship_code='EMPLOYEE_OF'
230 )
231 and status<>'I';
232 IF l_company_rel_count < 2 THEN
233 RETURN FND_API.G_TRUE;
234 ELSE
235 RETURN FND_API.G_FALSE;
236 END IF;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 -- must be B2C
240 RETURN FND_API.G_FALSE;
241 END;
242
243
244
245 /*+====================================================================
246 | FUNCTION NAME
247 | getIsPartialRegistrationUser
248 |
249 | DESCRIPTION
250 | This API is called by postRejection
251 |
252 | USAGE
253 | - Determines whether the user under rejection had registered
254 | using one of the partial registration usertypes
255 |
256 | REFERENCED APIS
257 +======================================================================*/
258
259 FUNCTION getIsPartialRegistrationUser(
260 p_user_reg_id IN NUMBER)
261 RETURN VARCHAR2
262 IS
263 l_usertype_group VARCHAR2(240) := 'IBE_UM_PARTIAL_USER_TYPES';
264 l_usertype_key VARCHAR2(240);
265
266 Cursor C_check_user_type(c_lookup_type VARCHAR2) IS
267 select ut.usertype_key
268 from jtf_um_usertypes_b ut, jtf_um_usertype_reg ureg, fnd_lookup_values flv
269 where ut.usertype_id=ureg.usertype_id
270 and ureg.usertype_reg_id=p_user_reg_id
271 and flv.lookup_type=c_lookup_type
272 and ut.usertype_key=flv.lookup_code;
273
274 BEGIN
275 OPEN C_check_user_type(l_usertype_group);
276 FETCH C_check_user_type into l_usertype_key;
277 IF C_check_user_type%NOTFOUND THEN
278 IBE_UTIL.debug('Not a partial registration type');
279 CLOSE C_check_user_type;
280 RETURN FND_API.G_FALSE;
281 END IF;
282
283 IBE_UTIL.debug('Is a partial registration type');
284 CLOSE C_check_user_type;
285 RETURN FND_API.G_TRUE;
286
287 EXCEPTION
288 WHEN NO_DATA_FOUND THEN
289 RETURN FND_API.G_FALSE;
290 WHEN OTHERS THEN
291 RETURN FND_API.G_FALSE;
292 END;
293
294
295
296 /*+====================================================================
297 | FUNCTION NAME
298 | getIsPartnerUser
299 |
300 | DESCRIPTION
301 | This API is called by postRejection
302 |
303 | USAGE
304 | - Determines whether the user under rejection had registered
305 | using one of the partner registration usertypes
306 |
307 | REFERENCED APIS
308 +======================================================================*/
309
310 FUNCTION getIsPartnerUser(
311 p_user_reg_id IN NUMBER)
312 RETURN VARCHAR2
313 IS
314 l_usertype_group VARCHAR2(240) := 'IBE_UM_PARTNER_USER_TYPES';
315 l_usertype_key VARCHAR2(240);
316
317 Cursor C_check_user_type(c_lookup_type VARCHAR2) IS
318 select ut.usertype_key
319 from jtf_um_usertypes_b ut, jtf_um_usertype_reg ureg, fnd_lookup_values flv
320 where ut.usertype_id=ureg.usertype_id
321 and ureg.usertype_reg_id=p_user_reg_id
322 and flv.lookup_type=c_lookup_type
323 and ut.usertype_key=flv.lookup_code;
324
325 BEGIN
326 OPEN C_check_user_type(l_usertype_group);
327 FETCH C_check_user_type into l_usertype_key;
328 IF C_check_user_type%NOTFOUND THEN
329 IBE_UTIL.debug('Not a partner registration type');
330 CLOSE C_check_user_type;
331 RETURN FND_API.G_FALSE;
332 END IF;
333
334 IBE_UTIL.debug('Is a partner registration type');
335 CLOSE C_check_user_type;
336 RETURN FND_API.G_TRUE;
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 RETURN FND_API.G_FALSE;
341 WHEN OTHERS THEN
342 RETURN FND_API.G_FALSE;
343 END;
344
345
346
347 END IBE_JTA_INTEGRATION_GRP;
348