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