1 PACKAGE BODY Pv_ptr_member_type_pvt as
2 /* $Header: pvxvmtcb.pls 120.3 2005/08/31 17:27:24 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
9 -- History 10-SEP-2003 pukken created
6 -- Pv_ptr_member_type_pvt
7 -- Purpose
8 -- to handle member type related functionality
10 -- 16-FEB-2004 pukken fixed bug 3439734
11 -- 29-APRIL-2004 pukken fix bug 3597966
12 -- 31-AUG-2005 ktsao fix bug 4534894
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Pv_ptr_member_type_pvt';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvmtcb.pls';
17
18 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
19 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
20 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
21 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
22
23 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
24 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
25
26 -- validate whether the partner is having member type 'GLOBAL'
27 -- IN .. partner_id of the global partner from pv_partner_profiles table
28
29 FUNCTION is_global_valid
30 ( p_global_partner_id IN NUMBER
31 )RETURN VARCHAR2
32 IS
33
34 cursor is_global_cur(p_ptr_id NUMBER) IS
35 SELECT decode ( attr_value,'GLOBAL','Y','N')
36 FROM pv_enty_attr_values
37 WHERE entity='PARTNER'
38 AND entity_id=p_ptr_id
39 AND attribute_id=6
40 AND latest_flag='Y';
41 l_is_global varchar2(1) := 'N';
42
43 BEGIN
44 OPEN is_global_cur( p_global_partner_id );
45 FETCH is_global_cur INTO l_is_global;
46 CLOSE is_global_cur;
47
48 return l_is_global;
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 return ('N');
52 END is_global_valid; --validate whether the partner is having member type 'GLOBAL'
53
54
55
56 FUNCTION isRecordExists
57 ( p_sub_partner_id IN NUMBER --subsidiary partner id
58 )RETURN VARCHAR2
59 IS
60
61 cursor is_global_cur(p_ptr_id NUMBER) IS
62 SELECT 'Y'
63 FROM hz_relationships rel
64 , pv_partner_profiles prof
65 WHERE rel.status='A'
66 AND prof.partner_id=p_ptr_id
67 AND relationship_type = 'PARTNER_HIERARCHY'
68 AND rel.subject_id = prof.partner_party_id
69 AND rel.relationship_code = 'SUBSIDIARY_OF'
70 AND rel.start_date <= SYSDATE
71 AND ( rel.end_date is null or rel.end_date>=sysdate);
72
73 l_is_exists varchar2(1) := 'N';
74
75 BEGIN
76 OPEN is_global_cur( p_sub_partner_id );
77 FETCH is_global_cur INTO l_is_exists;
78 CLOSE is_global_cur;
79 return l_is_exists;
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 return ('N');
83 END; --validate whether the partner is having member type 'GLOBAL'
84
85 PROCEDURE validate_member_type
86 (
87 p_member_type VARCHAR2
88 ,x_return_status OUT NOCOPY VARCHAR2
89 )IS
90
91 l_value VARCHAR2(1);
92 CURSOR memb_csr( attr_cd VARCHAR2 ) IS
93 SELECT 'X'
94 FROM PV_ATTRIBUTE_CODES_VL
95 WHERE ATTRIBUTE_ID = 6
96 AND ENABLED_FLAG = 'Y'
97 AND ATTR_CODE =attr_cd;
98
99 BEGIN
100 x_return_status := FND_API.g_ret_sts_success;
101 OPEN memb_csr( p_member_type );
102 FETCH memb_csr INTO l_value;
103 CLOSE memb_csr;
104 IF l_value IS NULL THEN
105 x_return_status := FND_API.g_ret_sts_error;
106 FND_MESSAGE.set_name('PV', 'PV_INVALID_MEMBER_TYPE');
107 FND_MESSAGE.set_token('MEMBER_TYPE',p_member_type );
108 FND_MSG_PUB.add;
109 END IF;
110
111 END validate_member_type;
112
113 PROCEDURE validate_Lookup(
114 p_lookup_type IN VARCHAR2
115 ,p_lookup_code IN VARCHAR2
116 ,x_return_status OUT NOCOPY VARCHAR2
117 )
118 IS
119 l_lookup_exists VARCHAR2(1);
120 BEGIN
121 x_return_status := FND_API.g_ret_sts_success;
122 --validate lookup
123 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists
124 ( p_lookup_table_name => 'PV_LOOKUPS'
125 ,p_lookup_type => p_lookup_type
126 ,p_lookup_code => p_lookup_code
127 );
128 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
129 x_return_status := FND_API.g_ret_sts_error;
130 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
131 FND_MESSAGE.set_token('LOOKUP_TYPE',p_lookup_type );
132 FND_MESSAGE.set_token('LOOKUP_CODE', p_lookup_code );
133 FND_MSG_PUB.add;
134 END IF;
135
136 END validate_Lookup;
137
138
139
140 FUNCTION logging_enabled (p_log_level IN NUMBER)
141 RETURN BOOLEAN
142 IS
143 BEGIN
144 RETURN (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
145 END;
146
147 PROCEDURE debug_message
148 (
149 p_log_level IN NUMBER
150 , p_module_name IN VARCHAR2
151 , p_text IN VARCHAR2
152 )
153 IS
154 BEGIN
155
156 -- IF logging_enabled (p_log_level) THEN
157 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 FND_LOG.STRING(p_log_level, p_module_name, p_text);
159 END IF;
160
161 END debug_message;
162
163 PROCEDURE WRITE_LOG
164 (
165 p_api_name IN VARCHAR2
166 , p_log_message IN VARCHAR2
167 )
168 IS
169
173 debug_message
170
171
172 BEGIN
174 (
175 p_log_level => g_log_level
176 , p_module_name => 'plsql.pv'||'.'|| g_pkg_name||'.'||p_api_name||'.'||p_log_message
177 , p_text => p_log_message
178 );
179 END WRITE_LOG;
180
181 --======================
182
183
184
185
186
187 PROCEDURE validate_partner_id(
188 p_partner_id IN NUMBER
189 ,x_return_status OUT NOCOPY VARCHAR2
190 )
191 IS
192
193 l_is_valid VARCHAR2(1):=null;
194 CURSOR is_partner_cur(ptr_id NUMBER) IS
195 SELECT 'Y'
196 FROM pv_partner_profiles
197 WHERE partner_id=ptr_id
198 AND STATUS='A';
199
200 BEGIN
201 x_return_status := FND_API.g_ret_sts_success;
202 --validate lookup
203 IF p_partner_id is NULL THEN
204 l_is_valid :='N';
205 ELSE
206 OPEN is_partner_cur(p_partner_id);
207 FETCH is_partner_cur INTO l_is_valid;
208 CLOSE is_partner_cur;
209 IF l_is_valid is NULL THEN
210 l_is_valid:='N';
211 END IF;
212 END IF;
213 IF l_is_valid='N' THEN
214 x_return_status := FND_API.g_ret_sts_error;
215 FND_MESSAGE.set_name('PV', 'PV_NO_PARTNER_ID');
216 FND_MESSAGE.set_token('ID',p_partner_id );
217 FND_MSG_PUB.add;
218 END IF;
219 END validate_partner_id;
220
221 --------------------------------------------
222 -- PROCEDURE
223 -- Register_term_ptr_memb_type
224 --
225 -- PURPOSE
226 -- This api can register as well as terminate member type and its corresponding relationships
227 -- IN
228 -- partner_id IN NUMBER.
229 -- for which member type is getting registered/terminated - either created/updated
230 -- p_current_memb_type.IN VARCHAR2 DEFAULT NULL
231 -- The existing member type stored in the db. if its not passed, we will query and get it
232 -- p_new_memb_type IN VARCHAR2.
233 -- pass GLOBAL,SUBSIDIARY or STANDARD if you want to register a new member type(also validated).
234 -- if you want to terminate the relationship pass null.
235 -- p_global_ptr_id. IN NUMBER DEFAULT NULL
236 -- if the new member type is SUBSIDIARY, pass the global's partner id from pv_partner_profiles table
237 -- this is validated only if the new member type is SUBSIDIARY
238
239 -- HISTORY
240 -- 15-SEP-2003 pukken CREATION
241 --------------------------------------------------------------------------
242 PROCEDURE Register_term_ptr_memb_type
243 (
244 p_api_version_number IN NUMBER
245 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
246 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
247 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
248 ,p_partner_id IN NUMBER
249 ,p_current_memb_type IN VARCHAR2 DEFAULT NULL
250 ,p_new_memb_type IN VARCHAR2
251 ,p_global_ptr_id IN NUMBER DEFAULT NULL
252 ,x_return_status OUT NOCOPY VARCHAR2
253 ,x_msg_count OUT NOCOPY NUMBER
254 ,x_msg_data OUT NOCOPY VARCHAR2
255 ) IS
256
257 CURSOR memb_type_cur( p_ptr_id NUMBER) IS
258 SELECT attr_value,version
259 FROM pv_enty_attr_values
260 WHERE entity='PARTNER'
261 AND entity_id=p_ptr_id
262 AND attribute_id=6
263 AND latest_flag='Y';
264
265 CURSOR attr_version_cur( p_ptr_id NUMBER) IS
266 SELECT version
267 FROM pv_enty_attr_values
268 WHERE entity='PARTNER'
269 AND entity_id=p_ptr_id
270 AND attribute_id=6
271 AND latest_flag='Y';
272
273 CURSOR c_partner_party_id_cur ( p_ptr_id NUMBER ) IS
274 SELECT partner_party_id
275 FROM pv_partner_profiles
276 WHERE partner_id=p_ptr_id;
277
278 --to get all the subsidiaries of the given global partner
279 CURSOR sub_cur( p_ptr_id NUMBER) IS
280 SELECT subs_prof.partner_id
281 , subs_prof.partner_party_id
282 , subs_enty_val.version
283 , rel.relationship_id
284 , rel.object_version_number
285 , rel.status
286 , rel.start_date
287 FROM pv_partner_profiles subs_prof
288 , pv_partner_profiles global_prof
289 , pv_enty_attr_values subs_enty_val
290 , hz_relationships rel
291 WHERE
292 global_prof.partner_id = p_ptr_id
293 AND global_prof.partner_party_id = rel.subject_id
294 AND rel.relationship_type = 'PARTNER_HIERARCHY'
295 AND rel.object_id = subs_prof.partner_party_id
296 AND rel.relationship_code = 'PARENT_OF'
297 AND rel.status = 'A'
298 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
299 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
300 AND subs_enty_val.entity = 'PARTNER'
301 AND subs_enty_val.entity_id = subs_prof.partner_id
302 AND subs_enty_val.attribute_id = 6
303 AND subs_enty_val.latest_flag = 'Y'
304 AND subs_enty_val.attr_value = 'SUBSIDIARY';
305
306 /*
307 --to fix sql id 12266928 11.5.10 cu1 , below just befoe the cursor sub_cur is opened,
308 --the current memeb type is already queried and hence it need not be querioed again
309 --and thats the change done in the above SQL.
310 SELECT subs_prof.partner_id
311 , subs_prof.partner_party_id
312 , subs_enty_val.version
313 , rel.relationship_id
314 , rel.object_version_number
315 , rel.status
316 , rel.start_date
320 , pv_enty_attr_values global_enty_val
317 FROM pv_partner_profiles subs_prof
318 , pv_partner_profiles global_prof
319 , pv_enty_attr_values subs_enty_val
321 , hz_relationships rel
322 WHERE global_enty_val.entity = 'PARTNER'
323 AND global_enty_val.entity_id = global_prof.partner_id
324 AND global_enty_val.attribute_id = 6
325 AND global_enty_val.latest_flag = 'Y'
326 AND global_enty_val.attr_value = 'GLOBAL'
327 AND global_prof.partner_id = p_ptr_id
328 AND global_prof.partner_party_id = rel.subject_id
329 AND rel.relationship_type = 'PARTNER_HIERARCHY'
330 AND rel.object_id = subs_prof.partner_party_id
331 AND rel.relationship_code = 'PARENT_OF'
332 AND rel.status = 'A'
333 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
334 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
335 AND subs_enty_val.entity = 'PARTNER'
336 AND subs_enty_val.entity_id = subs_prof.partner_id
337 AND subs_enty_val.attribute_id = 6
338 AND subs_enty_val.latest_flag = 'Y'
339 AND subs_enty_val.attr_value = 'SUBSIDIARY';
340 */
341
342 --given the subsidiary partner id, get the corresponding relationship id with its global partner
343 CURSOR rel_cur (p_subs_ptr_id NUMBER ) IS
344 SELECT rel.relationship_id relationship_id
345 , rel.start_date
346 , rel.object_version_number object_version_number
347 , subs_prof.partner_party_id partner_party_id
348 FROM pv_partner_profiles subs_prof
349 , hz_relationships rel
350 WHERE rel.subject_id=subs_prof.partner_party_id
351 AND rel.relationship_code = 'SUBSIDIARY_OF'
352 AND rel.relationship_type = 'PARTNER_HIERARCHY'
353 AND rel.status = 'A'
354 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
355 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
359 --to fix sql id 12266947 in 11.5.10 cu1 , below just befoe the cursor sub_cur is opened,
356 AND subs_prof.partner_id=p_subs_ptr_id;
357
358 /*
360 --the current memeb type is already queried and hence it need not be querioed again
361 --and thats the change done in the above SQL.
362 SELECT rel.relationship_id relationship_id
363 , rel.start_date
364 , rel.object_version_number object_version_number
365 , subs_prof.partner_party_id partner_party_id
366 , subs_enty_val.version version
367 FROM pv_partner_profiles subs_prof
368 , pv_enty_attr_values subs_enty_val
369 , hz_relationships rel
370 WHERE rel.subject_id=subs_prof.partner_party_id
371 AND rel.relationship_code = 'SUBSIDIARY_OF'
372 AND rel.relationship_type = 'PARTNER_HIERARCHY'
373 AND rel.status = 'A'
374 AND NVL(rel.start_date, SYSDATE) <= SYSDATE
375 AND NVL(rel.end_date, SYSDATE) >= SYSDATE
376 AND subs_prof.partner_id=p_subs_ptr_id
377 AND subs_enty_val.entity = 'PARTNER'
378 AND subs_enty_val.entity_id = p_subs_ptr_id
379 AND subs_enty_val.attribute_id = 6
380 AND subs_enty_val.latest_flag = 'Y'
381 AND subs_enty_val.attr_value = 'SUBSIDIARY';
382 */
383
384 CURSOR party_cur(p_ptnr_party_id NUMBER) IS
385 SELECT object_version_number
386 FROM hz_parties
387 WHERE party_id=p_ptnr_party_id;
388
389 CURSOR get_memb_csr ( attr_cd IN VARCHAR2 ) IS
390 SELECT DESCRIPTION
391 FROM PV_ATTRIBUTE_CODES_VL
392 WHERE ATTRIBUTE_ID = 6
393 AND ENABLED_FLAG = 'Y'
394 AND ATTR_CODE =attr_cd;
395
396 CURSOR get_party_csr( p_partner_id IN NUMBER ) IS
397 SELECT party_name
398 FROM hz_parties party
399 ,pv_partner_profiles prof
400 WHERE prof.partner_id=p_partner_id
401 AND prof.partner_party_id=party.party_id;
402
403 l_api_name CONSTANT VARCHAR2(30) := 'Register_term_ptr_memb_type';
404 l_relationship_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
405 l_current_memb_type VARCHAR2(30):=null;
406 l_partner_party_id NUMBER;
407 l_global_partner_party_id NUMBER;
408 l_version NUMBER:=null;
409 l_party_obj_ver_number NUMBER;
410 l_attr_value_tbl_type PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
411 l_relationship_id NUMBER;
412 l_object_version_number NUMBER;
413 l_api_version_number CONSTANT NUMBER := 1.0;
414 l_isGlobalValid VARCHAR2(1):='N';
415 l_memb_rel_id NUMBER;
416 l_memb_party_id NUMBER;
417 l_memb_party_number VARCHAR2(100);
418 l_check_status VARCHAR2(1) :='N';
422 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
419 l_start_date DATE;
420 l_from_memb_type VARCHAR2(30);
421 l_to_memb_type VARCHAR2(30);
423 l_param_tbl_var1 PVX_UTILITY_PVT.log_params_tbl_type;
424 l_party_name VARCHAR2(360) ;
425 BEGIN
426 -- Standard Start of API savepoint
427 SAVEPOINT Register_term_ptr_memb_type;
428 -- Standard call to check for call compatibility.
429 IF NOT FND_API.Compatible_API_Call
430 ( l_api_version_number
431 ,p_api_version_number
432 ,l_api_name
433 ,G_PKG_NAME
434 )
435 THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 END IF ;
438 -- Initialize message list if p_init_msg_list is set to TRUE.
439 IF FND_API.to_Boolean( p_init_msg_list ) THEN
440 FND_MSG_PUB.initialize;
441 END IF;
442 -- Debug Message
443
444 IF (PV_DEBUG_HIGH_ON) THEN
445 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
446 END IF;
447
448 -- Initialize API return status to SUCCESS
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450 -- Validate Environment
451 IF FND_GLOBAL.USER_ID IS NULL THEN
452 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
453 RAISE FND_API.G_EXC_ERROR;
454 END IF;
455
456 -- find out the existing the member type if its not passed in.. If its passed , validate it
457 IF p_current_memb_type is NULL THEN
458 OPEN memb_type_cur(p_partner_id);
459 FETCH memb_type_cur INTO l_current_memb_type,l_version;
460 CLOSE memb_type_cur;
461 ELSE
462 --VALIDATE the passed in member type value thats passed in
463 /*validate_Lookup
464 (
465 p_lookup_type => 'PV_MEMBER_TYPE_CODE'
466 ,p_lookup_code => p_current_memb_type
467 ,x_return_status => x_return_status
468 );
469 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
470 RAISE FND_API.G_EXC_ERROR;
471 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
473 END IF;
474 */
475 Validate_member_type
476 (
477 p_member_type => p_current_memb_type
478 ,x_return_status => x_return_status
479 );
480 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
481 RAISE FND_API.G_EXC_ERROR;
482 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485 l_current_memb_type:=p_current_memb_type;
486 END IF;
487
488 -- get the attribute value version number for the member type
489 OPEN attr_version_cur(p_partner_id);
490 FETCH attr_version_cur INTO l_version;
491 CLOSE attr_version_cur;
492
493 --check the new member type value.. if its null, then it means we need to terminate the membership
494 IF p_new_memb_type is NULL THEN
495 --check the existing the membership type and perform actions accordingly
496
497 IF l_current_memb_type='GLOBAL' THEN
498 --first get all its subsidiariess and terminate the relationship between subsidiaries and global
499 --for all these subsidiaries, update the profile attribute value to STANDARD
500 --finally update the globals profile attribute value to STANDARD.
501 FOR subs in sub_cur(p_partner_id) LOOP
502 l_relationship_rec.relationship_id := subs.relationship_id;
503 l_relationship_rec.status:= 'I';
504 --l_relationship_rec.start_date := to_date(subs.start_date,'DD-MM-YYYY HH24:MI:SS');
505 --reduce the end date by 10 seconds from sysdate.the reason is when the subsidiary'd
509 l_party_obj_ver_number:=null;
506 -- global chnages to a global ,we need to create a new relationship in the same transaction
507 -- and since multiple parents are not allowed, sometimes it will fail in TCA validation
508 l_relationship_rec.end_date:= sysdate-10*1/24/60/60;
510 --terminate the relationship between the subsidiary and global
511
512 HZ_RELATIONSHIP_V2PUB.update_relationship
513 (
514 p_init_msg_list => FND_API.g_false
515 ,p_relationship_rec => l_relationship_rec
516 ,p_object_version_number => subs.object_version_number
517 ,p_party_object_version_number => l_party_obj_ver_number
518 ,x_return_status => x_return_status
522 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
519 ,x_msg_count => x_msg_count
520 ,x_msg_data => x_msg_data
521 );
523 RAISE FND_API.G_EXC_ERROR;
524 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527 --update the attribute value to STANDARD
528 l_attr_value_tbl_type(1).attr_value:='STANDARD';
529 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value
530 (
531 p_api_version_number => 1.0
532 ,p_init_msg_list => FND_API.g_false
533 ,p_commit => FND_API.g_false
534 ,p_validation_level => FND_API.g_valid_level_full
535 ,x_return_status => x_return_status
536 ,x_msg_count => x_msg_count
537 ,x_msg_data => x_msg_data
538 ,p_attribute_id => 6
539 ,p_entity => 'PARTNER'
540 ,p_entity_id => subs.partner_id
541 ,p_version => subs.version
542 ,p_attr_val_tbl => l_attr_value_tbl_type
543 );
544 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
545 RAISE FND_API.G_EXC_ERROR;
546 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
548 END IF;
549
550 --send notification to the subsidiary partner that their member type changed
551 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
552 (
553 p_api_version_number => 1.0
554 , p_init_msg_list => FND_API.G_FALSE
555 , p_commit => FND_API.G_FALSE
556 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
557 , p_context_id => p_partner_id -- context id is global partner_id when change to memb type is subsidiary
558 , p_context_code => 'SUBSIDIARY'
559 , p_target_ctgry => 'PARTNER'
560 , p_target_ctgry_pt_id => subs.partner_id
561 , p_notif_event_code => 'GLOBAL_MEMBTYPE_CHANGE'
562 , p_entity_id => subs.partner_id
563 , p_entity_code => 'STANDARD'
564 , p_wait_time => 0
565 , x_return_status => x_return_status
566 , x_msg_count => x_msg_count
567 , x_msg_data => x_msg_data
568 );
569
570 --write to the subsdidiary's history log that the global partner's member type changed
571 OPEN get_party_csr(p_partner_id) ;
572 FETCH get_party_csr INTO l_party_name;
573 CLOSE get_party_csr ;
574
575 l_param_tbl_var1(1).param_name := 'PARTNER_NAME';
576 l_param_tbl_var1(1).param_value := l_party_name;
577
578 PVX_UTILITY_PVT.create_history_log
579 (
580 p_arc_history_for_entity_code => 'GENERAL'
581 , p_history_for_entity_id => subs.partner_id
582 , p_history_category_code => 'PARTNER'
583 , p_message_code => 'PV_GLOBAL_MB_TYPE_CHANGE'
584 , p_comments => null
585 , p_partner_id => subs.partner_id
586 , p_access_level_flag => 'P'
587 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
588 , p_log_params_tbl => l_param_tbl_var1
589 , p_init_msg_list => FND_API.g_false
590 , p_commit => FND_API.G_FALSE
591 , x_return_status => x_return_status
592 , x_msg_count => x_msg_count
593 , x_msg_data => x_msg_data
594 );
595 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
596 RAISE FND_API.G_EXC_ERROR;
597 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
599 END IF;
600
601 -- write to the subsidiary history log that membertype of the subsidiary changed to standard.
602 OPEN get_memb_csr('SUBSIDIARY') ;
603 FETCH get_memb_csr INTO l_from_memb_type;
604 CLOSE get_memb_csr ;
605 OPEN get_memb_csr('STANDARD') ;
606 FETCH get_memb_csr INTO l_to_memb_type;
607 CLOSE get_memb_csr ;
608
609 l_param_tbl_var(1).param_name := 'FROM_MEMB_TYPE';
610 l_param_tbl_var(1).param_value := l_from_memb_type;
611 l_param_tbl_var(2).param_name := 'TO_MEMB_TYPE';
612 l_param_tbl_var(2).param_value := l_to_memb_type ;
613
614 PVX_UTILITY_PVT.create_history_log
615 (
616 p_arc_history_for_entity_code => 'GENERAL'
617 , p_history_for_entity_id => subs.partner_id
618 , p_history_category_code => 'PARTNER'
619 , p_message_code => 'PV_MEMBER_TYPE_CHANGE'
620 , p_comments => null
621 , p_partner_id => subs.partner_id
622 , p_access_level_flag => 'P'
623 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
624 , p_log_params_tbl => l_param_tbl_var
625 , p_init_msg_list => FND_API.g_false
631 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
626 , p_commit => FND_API.G_FALSE
627 , x_return_status => x_return_status
628 , x_msg_count => x_msg_count
629 , x_msg_data => x_msg_data
630 );
632 RAISE FND_API.G_EXC_ERROR;
633 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 END IF;
636
637
638 END LOOP;
639 --also update the globals profile attribute value to standard
640 l_attr_value_tbl_type(1).attr_value:='STANDARD';
641 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value
642 (
643 p_api_version_number => 1.0
644 ,p_init_msg_list => FND_API.g_false
645 ,p_commit => FND_API.g_false
646 ,p_validation_level => FND_API.g_valid_level_full
647 ,x_return_status => x_return_status
648 ,x_msg_count => x_msg_count
649 ,x_msg_data => x_msg_data
650 ,p_attribute_id => 6
651 ,p_entity => 'PARTNER'
652 ,p_entity_id => p_partner_id
653 ,p_version => l_version
654 ,p_attr_val_tbl => l_attr_value_tbl_type
655 );
656 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
657 RAISE FND_API.G_EXC_ERROR;
658 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660 END IF;
661 ELSIF l_current_memb_type='SUBSIDIARY' THEN
662
663 --get the relationship_id
664 OPEN rel_cur(p_partner_id);
665 FETCH rel_cur INTO l_relationship_id,l_start_date,l_object_version_number,l_partner_party_id;
666 CLOSE rel_cur;
667 --terminate the relationship between this subsidiary and global
668 l_relationship_rec.relationship_id := l_relationship_id;
669 l_relationship_rec.status:= 'I';
670 --l_relationship_rec.start_date := to_date(l_start_date,'DD-MM-YYYY HH24:MI:SS');
671 --reduce the end date by 10 seconds from sysdate.the reason is when the subsidiary'd
672 -- global chnages to a global ,we need to create a new relationship in the same transaction
673 -- and since multiple parents are not allowed, sometimes it will fail in TCA validation
674 l_relationship_rec.end_date:= sysdate-10*1/24/60/60; --reduce the end date by 10 seconds from sysdate
675 l_party_obj_ver_number:=null;
676 IF l_relationship_id IS NOT NULL THEN
677
678 HZ_RELATIONSHIP_V2PUB.update_relationship
679 (
680 p_init_msg_list => FND_API.g_false
681 ,p_relationship_rec => l_relationship_rec
682 ,p_object_version_number => l_object_version_number
683 ,p_party_object_version_number => l_party_obj_ver_number
684 ,x_return_status => x_return_status
685 ,x_msg_count => x_msg_count
686 ,x_msg_data => x_msg_data
687 );
688 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
689 RAISE FND_API.G_EXC_ERROR;
690 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END IF;
693 END IF;
694 --also update the attr_value to STANDARD
695
696 IF l_version is NULL THEN
697 OPEN attr_version_cur(p_partner_id);
698 FETCH attr_version_cur INTO l_version;
699 CLOSE attr_version_cur;
700 END IF;
701
702 l_attr_value_tbl_type(1).attr_value:='STANDARD';
703 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value
704 (
705 p_api_version_number => 1.0
706 ,p_init_msg_list => FND_API.g_false
707 ,p_commit => FND_API.g_false
708 ,p_validation_level => FND_API.g_valid_level_full
709 ,x_return_status => x_return_status
710 ,x_msg_count => x_msg_count
711 ,x_msg_data => x_msg_data
715 ,p_version => l_version
712 ,p_attribute_id => 6
713 ,p_entity => 'PARTNER'
714 ,p_entity_id => p_partner_id
716 ,p_attr_val_tbl => l_attr_value_tbl_type
717 );
718
719 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
720 RAISE FND_API.G_EXC_ERROR;
721 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
723 END IF;
724
725 END IF; --end of if else for l_current_memb_type
726
727 ELSE
728 --this code is executed when p_new_memb_type is anything other than null
729 --VALIDATE the passed in new member type ( p_new_memb_type) value
730 /*
731 validate_Lookup
732 (
733 p_lookup_type => 'PV_MEMBER_TYPE_CODE'
734 ,p_lookup_code => p_new_memb_type
735 ,x_return_status => x_return_status
736 );
737 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
738 RAISE FND_API.G_EXC_ERROR;
739 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
741 END IF;
742 */
743
744 Validate_member_type
745 (
746 p_member_type => p_new_memb_type
747 ,x_return_status => x_return_status
748 );
749 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
750 RAISE FND_API.G_EXC_ERROR;
751 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 END IF;
754 --update/insert the attributes table only if its null or the one in the db is not equal to the one thats passed in
755 IF ( l_current_memb_type<>p_new_memb_type OR l_current_memb_type IS NULL ) THEN
756 --if the attribute val for this attribute is being created for the first time pass version as zero.
757 IF l_version is NULL THEN
758 l_version:=0;
759 END IF;
760 --update the attribute value
761 l_attr_value_tbl_type(1).attr_value:=p_new_memb_type;
762
763 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value
764 (
765 p_api_version_number => 1.0
766 ,p_init_msg_list => FND_API.g_false
767 ,p_commit => FND_API.g_false
768 ,p_validation_level => FND_API.g_valid_level_full
769 ,x_return_status => x_return_status
770 ,x_msg_count => x_msg_count
771 ,x_msg_data => x_msg_data
772 ,p_attribute_id => 6
773 ,p_entity => 'PARTNER'
774 ,p_entity_id => p_partner_id
775 ,p_version => l_version
776 ,p_attr_val_tbl => l_attr_value_tbl_type
777 );
778 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
779 RAISE FND_API.G_EXC_ERROR;
780 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
782 END IF;
783 --only if the new memb type is SUBSIADIARY, you need to create a new relationship
784 IF p_new_memb_type='SUBSIDIARY' THEN
785 l_check_status := isRecordExists(p_partner_id);
786
787 IF l_check_status = 'N' THEN
788 --validate the global partner id
789 l_isGlobalValid := is_global_valid(p_global_ptr_id);
790
791 IF l_isGlobalValid='Y' THEN
792
793 --get the subsidiary partner_party_id
794 OPEN c_partner_party_id_cur(p_partner_id);
795 FETCH c_partner_party_id_cur INTO l_partner_party_id;
796 CLOSE c_partner_party_id_cur;
797
798 --get the global partner_party_id
799 OPEN c_partner_party_id_cur(p_global_ptr_id);
800 FETCH c_partner_party_id_cur INTO l_global_partner_party_id;
801 CLOSE c_partner_party_id_cur;
802
803 -- create a new relationship in TCA for subsidiary global relationship
804 -- Initilize the l_relationship_rec with required values.
805 l_relationship_rec.subject_id := l_partner_party_id;--subsidiary party id
806 l_relationship_rec.subject_type := 'ORGANIZATION';
807 l_relationship_rec.subject_table_name := 'HZ_PARTIES';
808 l_relationship_rec.object_id := l_global_partner_party_id; --global party id
809 l_relationship_rec.object_type := 'ORGANIZATION';
810 l_relationship_rec.object_table_name := 'HZ_PARTIES';
811 l_relationship_rec.relationship_code := 'SUBSIDIARY_OF';
812 l_relationship_rec.relationship_type := 'PARTNER_HIERARCHY';
813 l_relationship_rec.start_date := SYSDATE;
814 l_relationship_rec.created_by_module:= 'PV';
815 l_relationship_rec.application_id:= 691;
816 l_relationship_rec.status:= 'A';
817 -- Create the relationship.
818
819 HZ_RELATIONSHIP_V2PUB.create_relationship
820 (
821 p_init_msg_list => FND_API.G_FALSE
822 ,p_relationship_rec => l_relationship_rec
823 ,x_relationship_id => l_memb_rel_id
824 ,x_party_id => l_memb_party_id
825 ,x_party_number => l_memb_party_number
826 ,x_return_status => x_return_status
827 ,x_msg_count => x_msg_count
828 ,x_msg_data => x_msg_data
829 ,p_create_org_contact => 'N'
830 );
831
832 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
833 RAISE FND_API.G_EXC_ERROR;
834 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END IF;
837
838 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
839 (
840 p_api_version_number => 1.0
841 , p_init_msg_list => FND_API.G_FALSE
842 , p_commit => FND_API.G_FALSE
843 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
844 , p_context_id => p_partner_id-- partner id of the subsidiary partner
845 , p_context_code => 'PARTNER'
846 , p_target_ctgry => 'PARTNER'
847 , p_target_ctgry_pt_id => p_global_ptr_id -- global partner_id
848 , p_notif_event_code => 'SUBSIDIARY_PTNR_REGISTRATION'
849 , p_entity_id => p_global_ptr_id
850 , p_entity_code => 'PARTNER'
851 , p_wait_time => 0
852 , x_return_status => x_return_status
853 , x_msg_count => x_msg_count
854 , x_msg_data => x_msg_data
855 );
856
857
858 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
859 RAISE FND_API.G_EXC_ERROR;
860 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
862 END IF;
863 ELSE
864 --raise error if the global is invalid
865 FND_MESSAGE.set_name('PV', 'PV_GLOBAL_PARTNER_ID_INVALID');
866 FND_MSG_PUB.add;
867 RAISE FND_API.G_EXC_ERROR;
868 END IF;-- global exists
869 END IF; --end of iif to check whether the subsidiary already has an active relationship with a global
870 END IF;--if new memb type is subsidiary
871 END IF;--end of if , if the current member type and new member type are not equal
872 END IF; --if we terminating or creating
873
874 -- Standard call to get message count and if count is 1, get message info.
875 FND_MSG_PUB.Count_And_Get
876 (p_count => x_msg_count,
877 p_data => x_msg_data
878 );
879
880 IF (PV_DEBUG_HIGH_ON) THEN
881 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
882 END IF;
883
884 IF FND_API.to_Boolean( p_commit ) THEN
885 COMMIT WORK;
886 END IF;
887
888 EXCEPTION
889 WHEN FND_API.G_EXC_ERROR THEN
890 ROLLBACK TO Register_term_ptr_memb_type;
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 -- Standard call to get message count and if count=1, get the message
893 FND_MSG_PUB.Count_And_Get (
894 p_encoded => FND_API.G_FALSE,
895 p_count => x_msg_count,
899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
896 p_data => x_msg_data
897 );
898
900 ROLLBACK TO Register_term_ptr_memb_type;
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902 -- Standard call to get message count and if count=1, get the message
903 FND_MSG_PUB.Count_And_Get (
904 p_encoded => FND_API.G_FALSE,
905 p_count => x_msg_count,
906 p_data => x_msg_data
907 );
908
909 WHEN OTHERS THEN
910 ROLLBACK TO Register_term_ptr_memb_type;
914 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
913 THEN
915 END IF;
916 -- Standard call to get message count and if count=1, get the message
917 FND_MSG_PUB.Count_And_Get (
918 p_encoded => FND_API.G_FALSE,
919 p_count => x_msg_count,
920 p_data => x_msg_data
921 );
922 END Register_term_ptr_memb_type;
923
924
925 ---------------------------------------------
926
927 -- PROCEDURE
928 -- Pv_ptr_member_type_pvt.Process_ptr_member_type
929 --
930 -- PURPOSE
931 -- Change Membership Type.
932 -- IN
933 -- partner_id IN NUMBER
934 -- partner_id for which member type is getting changed
935 -- p_chg_from_memb_type IN VARCHAR2 := NULL
936 -- if not given, will get from profile, should be 'SUBSIDIARY','GLOBAL','STANDARD'
937 -- p_chg_to_memb_type IN VARCHAR2
938 -- should be 'SUBSIDIARY','GLOBAL','STANDARD'
939 -- p_chg_to_global_ptr_id IN NUMBER DEFAULT NULL
940 -- if p_chg_to_memb_type is 'SUBSIDIARY', this needs to be passed for identifying the global partner_id for the subsidiary
941 -- USED BY
942 -- called from vendor facing UI when member type change is requested by partner
943 --
944 -- HISTORY
945 -- 15-SEP-2003 pukken CREATION
946 --------------------------------------------------------------------------
947 PROCEDURE Process_ptr_member_type
948 (
949 p_api_version_number IN NUMBER
950 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
951 , p_commit IN VARCHAR2 := FND_API.G_FALSE
952 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
953 , p_partner_id IN NUMBER
954 , p_chg_from_memb_type IN VARCHAR2 DEFAULT NULL
955 , p_chg_to_memb_type IN VARCHAR2
956 , p_chg_to_global_ptr_id IN NUMBER DEFAULT NULL
957 , x_return_status OUT NOCOPY VARCHAR2
958 , x_msg_count OUT NOCOPY NUMBER
959 , x_msg_data OUT NOCOPY VARCHAR2
960 ) IS
961
962 CURSOR memb_type_cur( p_ptr_id NUMBER) IS
963 SELECT attr_value
964 FROM pv_enty_attr_values
965 WHERE entity='PARTNER'
966 AND entity_id=p_ptr_id
967 AND attribute_id=6
968 AND latest_flag='Y';
969
970 l_current_memb_type VARCHAR2(30);
971 l_chg_from_memb_type VARCHAR2(30);
972 l_context_id NUMBER;
973 l_api_name CONSTANT VARCHAR2(30) := 'Process_ptr_member_type';
974 l_api_version_number CONSTANT NUMBER := 1.0;
975
976 BEGIN
977 /**
978 a). Call register terminate API twice.
979 once to terminate the existing relationship and update profile attribute value
980 by passing p_new_membtype as null
981 Call it again to create new relationship and update profile attributes
982 by passing the p_new_membtype with the member type you want to tag the partner with
983 the values would be STANDARD,GLOBAL,SUBSIDIARY.
984 But if the partner is getting registered for the first time , you just need to call
985 Register_term_ptr_memb_type once with p_new_membtype = to the member type.
986 b). Terminate_ptr_memberships to terminate all the program memberships.
987 */
988 -- Standard Start of API savepoint
989 SAVEPOINT Process_ptr_member_type;
990 -- Standard call to check for call compatibility.
991 IF NOT FND_API.Compatible_API_Call
992 ( l_api_version_number
993 ,p_api_version_number
994 ,l_api_name
995 ,G_PKG_NAME
996 )
997 THEN
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000 -- Initialize message list if p_init_msg_list is set to TRUE.
1001 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1002 FND_MSG_PUB.initialize;
1003 END IF;
1004 -- Debug Message
1005 IF (PV_DEBUG_HIGH_ON) THEN
1006 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1007 END IF;
1008
1009 -- Initialize API return status to SUCCESS
1010 x_return_status := FND_API.G_RET_STS_SUCCESS;
1011 -- Validate Environment
1012 IF FND_GLOBAL.USER_ID IS NULL THEN
1013 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1014 RAISE FND_API.G_EXC_ERROR;
1015 END IF;
1016
1017 --terminate program memberships before terminating relationship
1018 PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships
1019 (
1020 p_api_version_number => 1.0
1021 ,p_init_msg_list => FND_API.G_FALSE
1022 ,p_commit => FND_API.G_FALSE
1023 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1024 ,p_partner_id => p_partner_id
1025 ,p_memb_type => p_chg_from_memb_type
1026 ,p_status_reason_code => 'MEMBER_TYPE_CHANGE' -- pass 'MEMBER_TYPE_CHANGE' if it is happening because of member type change -- it validates against PV_MEMB_STATUS_REASON_CODE
1027 ,p_comments => 'Membership terminated by system as member type is changed'
1028 ,x_return_status => x_return_status
1029 ,x_msg_count => x_msg_count
1030 ,x_msg_data => x_msg_data
1031 );
1032 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1033 RAISE FND_API.G_EXC_ERROR;
1034 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1036 END IF;
1040 IF l_chg_from_memb_type IS NULL THEN
1037
1038 -- Call register terminate API with p_new_memb_type as null
1039 l_chg_from_memb_type := p_chg_from_memb_type;
1041 OPEN memb_type_cur( p_partner_id );
1042 FETCH memb_type_cur INTO l_chg_from_memb_type;
1043 CLOSE memb_type_cur;
1044 END IF;
1045
1046 Register_term_ptr_memb_type
1047 (
1051 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1048 p_api_version_number => 1.0
1049 ,p_init_msg_list => FND_API.G_FALSE
1050 ,p_commit => FND_API.G_FALSE
1052 ,p_partner_id => p_partner_id
1053 ,p_current_memb_type => l_chg_from_memb_type
1054 ,p_new_memb_type => null
1055 ,p_global_ptr_id => null
1056 ,x_return_status => x_return_status
1057 ,x_msg_count => x_msg_count
1058 ,x_msg_data => x_msg_data
1059 );
1060 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1061 RAISE FND_API.G_EXC_ERROR;
1062 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1064 END IF;
1065
1066 -- Call register terminate API with p_new_memb_type with the new member type
1067 -- since the p_chg_from_memb_type would have got changed by now because of the above call,
1068 -- i will query the database again and get the current member type again
1069
1070 OPEN memb_type_cur(p_partner_id);
1071 FETCH memb_type_cur INTO l_current_memb_type;
1072 CLOSE memb_type_cur;
1073
1074 IF ( l_current_memb_type<> p_chg_to_memb_type OR l_current_memb_type IS NULL ) THEN
1075 Register_term_ptr_memb_type
1076 (
1077 p_api_version_number => 1.0
1078 ,p_init_msg_list => FND_API.G_FALSE
1079 ,p_commit => FND_API.G_FALSE
1080 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1081 ,p_partner_id => p_partner_id
1082 ,p_current_memb_type => l_current_memb_type
1083 ,p_new_memb_type => p_chg_to_memb_type
1084 ,p_global_ptr_id => p_chg_to_global_ptr_id
1085 ,x_return_status => x_return_status
1086 ,x_msg_count => x_msg_count
1087 ,x_msg_data => x_msg_data
1088 );
1089 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1090 RAISE FND_API.G_EXC_ERROR;
1091 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 END IF;
1094 END IF;
1095 --also terminate all the program memberships
1096
1097
1098 IF p_chg_to_memb_type = 'SUBSIDIARY' THEN
1099 l_context_id := p_chg_to_global_ptr_id;
1100 ELSE
1101 l_context_id := p_partner_id;
1102 END IF;
1103
1104 IF l_chg_from_memb_type IS NOT NULL AND p_chg_to_memb_type IS NOT NULL THEN
1105 PV_PG_NOTIF_UTILITY_PVT.Send_Workflow_Notification
1106 (
1107 p_api_version_number => 1.0
1108 , p_init_msg_list => FND_API.G_FALSE
1109 , p_commit => FND_API.G_FALSE
1110 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1111 , p_context_id => l_context_id -- context id is global partner_id when change to memb type is subsidiary
1112 , p_context_code => l_chg_from_memb_type
1113 , p_target_ctgry => 'PARTNER'
1114 , p_target_ctgry_pt_id => p_partner_id
1115 , p_notif_event_code => 'MEMBER_TYPE_CHANGE'
1116 , p_entity_id => p_partner_id
1117 , p_entity_code => p_chg_to_memb_type
1118 , p_wait_time => 0
1119 , x_return_status => x_return_status
1120 , x_msg_count => x_msg_count
1121 , x_msg_data => x_msg_data
1122 );
1123 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1124 RAISE FND_API.G_EXC_ERROR;
1125 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1127 END IF;
1128 END IF;
1129
1130
1131
1132
1133 FND_MSG_PUB.Count_And_Get
1134 (
1135 p_count => x_msg_count
1136 , p_data => x_msg_data
1137 );
1138 IF (PV_DEBUG_HIGH_ON) THEN
1139 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1140 END IF;
1141
1142 IF FND_API.to_Boolean( p_commit ) THEN
1143 COMMIT WORK;
1144 END IF;
1148 ROLLBACK TO Process_ptr_member_type;
1145
1146 EXCEPTION
1147 WHEN FND_API.G_EXC_ERROR THEN
1149 x_return_status := FND_API.G_RET_STS_ERROR;
1150 -- Standard call to get message count and if count=1, get the message
1151 FND_MSG_PUB.Count_And_Get (
1152 p_encoded => FND_API.G_FALSE,
1153 p_count => x_msg_count,
1154 p_data => x_msg_data
1155 );
1156
1157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1158 ROLLBACK TO Process_ptr_member_type;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1160 -- Standard call to get message count and if count=1, get the message
1161 FND_MSG_PUB.Count_And_Get (
1162 p_encoded => FND_API.G_FALSE,
1163 p_count => x_msg_count,
1164 p_data => x_msg_data
1165 );
1166
1167 WHEN OTHERS THEN
1168 ROLLBACK TO Process_ptr_member_type;
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1170 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1171 THEN
1172 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1173 END IF;
1174 -- Standard call to get message count and if count=1, get the message
1175 FND_MSG_PUB.Count_And_Get (
1176 p_encoded => FND_API.G_FALSE,
1177 p_count => x_msg_count,
1178 p_data => x_msg_data
1179 );
1180 END Process_ptr_member_type;
1181
1182
1183
1184 FUNCTION validate_global_partner_orgzn
1185 ( p_global_prtnr_org_number IN VARCHAR2
1186 )RETURN VARCHAR2
1187 IS
1188
1189 cursor cv_validate_global_orgzn(cv_orgzn_number VARCHAR2) IS
1190 select 1 from dual where exists
1191 (select 1 from hz_parties hzp, pv_partner_profiles pvpp, pv_enty_attr_values pvev
1192 where hzp.party_number= cv_orgzn_number
1193 and hzp.party_id = pvpp.partner_party_id
1194 and pvpp.status = 'A'
1195 and pvpp.partner_id = pvev.entity_id
1196 and pvev.entity = 'PARTNER'
1197 and pvev.enabled_flag = 'Y'
1198 and pvev.latest_flag = 'Y'
1199 and pvev.attr_value = 'GLOBAL'
1200 and pvev.attribute_id = 6
1201 );
1202
1203 l_is_global varchar2(1) := 'N';
1204
1205
1206 BEGIN
1207
1208 FOR x in cv_validate_global_orgzn(p_global_prtnr_org_number) loop
1209 l_is_global := 'Y';
1210 exit;
1211 end loop;
1212
1213 return l_is_global;
1214 EXCEPTION
1215 WHEN NO_DATA_FOUND THEN
1216 return ('N');
1217 END; --validate_global_partner_orgzn
1218
1219
1220
1221
1222 FUNCTION get_global_partner_id
1223 ( p_global_prtnr_org_number IN VARCHAR2
1224 ) RETURN NUMBER
1225 IS
1226 l_global_prtnr_id NUMBER := NULL ;
1227
1228 cursor cv_get_global_prtnr_id(cv_orgzn_number VARCHAR2) IS
1229 select pvpp.partner_id from hz_parties hzp, pv_partner_profiles pvpp, pv_enty_attr_values pvev
1230 where hzp.party_number= cv_orgzn_number
1231 and hzp.party_id = pvpp.partner_party_id
1232 and pvpp.status = 'A'
1233 and pvpp.partner_id = pvev.entity_id
1234 and pvev.entity = 'PARTNER'
1235 and pvev.enabled_flag = 'Y'
1236 and pvev.latest_flag = 'Y'
1237 and pvev.attr_value = 'GLOBAL';
1238
1239 BEGIN
1240
1241 open cv_get_global_prtnr_id(p_global_prtnr_org_number);
1242 fetch cv_get_global_prtnr_id into l_global_prtnr_id;
1243 close cv_get_global_prtnr_id;
1244
1245
1246 RETURN(l_global_prtnr_id);
1247 EXCEPTION
1248 WHEN NO_DATA_FOUND THEN
1249 return (NULL);
1250 END; --get_global_partner_id
1251
1252
1253
1254
1255 -- Function
1256 -- terminate_partner
1257 --
1258 -- PURPOSE
1259 -- this procedure is called for the subscription for the TCA business event
1260 -- for relationship create or update
1261 -- This would update the member type of the partner to the appropriate value
1262 -- and would also terminate all program memberships
1263 -- HISTORY
1264 -- 06-NOV-2003 pukken CREATION
1265 FUNCTION terminate_partner
1266 (
1267 p_subscription_guid IN RAW
1268 , p_event IN OUT NOCOPY wf_event_t
1269 )
1270 RETURN VARCHAR2 IS
1271
1272 l_key VARCHAR2(240) := p_event.GetEventKey();
1273 l_api_name CONSTANT VARCHAR2(30) := 'terminate_partner';
1274 id NUMBER;
1275 l_count NUMBER;
1276 l_partner_id NUMBER;
1277 l_org_id NUMBER;
1278 l_user_id NUMBER;
1279 l_resp_id NUMBER;
1280 l_old_status VARCHAR2(1);
1281 l_new_status VARCHAR2(1);
1282 l_attr_value_tbl_type PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
1283 l_version NUMBER;
1284 l_memb_type VARCHAR2(30);
1285 l_resp_appl_id NUMBER;
1286 l_security_group_id NUMBER;
1287 x_return_status VARCHAR2(1);
1288 x_msg_count NUMBER;
1289 x_msg_data VARCHAR2(2000);
1290 l_status VARCHAR2(1);
1291 l_subject_id NUMBER;
1292 l_sub_flag VARCHAR2(1);
1293
1294 CURSOR rel_status ( id NUMBER) IS
1295 SELECT status
1296 , subject_id
1297 FROM hz_relationships
1298 WHERE relationship_id= id;
1299
1300 CURSOR c_get_partner_id ( rel_id NUMBER ) IS
1301 SELECT partner_id
1302
1303 FROM pv_partner_profiles prof
1304 , hz_relationships rel
1305 , pv_enty_attr_values enty
1306 WHERE rel.relationship_id = rel_id
1307 AND rel.relationship_type= 'PARTNER_HIERARCHY'
1308 AND rel.relationship_code= 'SUBSIDIARY_OF'
1312 AND enty.latest_flag='Y'
1309 AND rel.subject_id=prof.partner_party_id
1310 AND prof.partner_id=enty.entity_id
1311 AND enty.attribute_id=6
1313 AND enty.attr_value='SUBSIDIARY';
1314
1315 CURSOR c_get_sub_glob ( rel_id NUMBER ) IS
1316 SELECT sprof.partner_id subsidiary_partner_id
1317 , gprof.partner_id global_partner_id
1318 , enty.attr_value attr_value
1319 FROM pv_partner_profiles sprof
1320 , pv_partner_profiles gprof
1321 , hz_relationships rel
1322 , pv_enty_attr_values enty
1323 WHERE rel.relationship_id = rel_id
1324 AND rel.relationship_type= 'PARTNER_HIERARCHY'
1325 AND rel.relationship_code= 'SUBSIDIARY_OF'
1326 AND rel.subject_id=sprof.partner_party_id
1327 AND rel.object_id= gprof.partner_party_id
1328 AND gprof.partner_id=enty.entity_id
1329 AND enty.attribute_id=6
1330 AND enty.latest_flag='Y' ;
1331
1332 CURSOR attr_version_cur( p_ptr_id NUMBER) IS
1333 SELECT version,attr_value
1334 FROM pv_enty_attr_values
1335 WHERE entity='PARTNER'
1336 AND entity_id=p_ptr_id
1337 AND attribute_id=6
1338 AND latest_flag='Y';
1339
1340 BEGIN
1341 IF (PV_DEBUG_HIGH_ON) THEN
1342 WRITE_LOG
1343 (
1344 l_api_name
1345 , 'Entered the subscription pl/sql block Pv_ptr_member_type_pvt.terminate_partner()'
1346 );
1347 END IF;
1348
1349 IF ( l_key like 'oracle.apps.ar.hz.Relationship.update%' OR l_key like 'oracle.apps.ar.hz.Relationship.create%' ) THEN
1350 l_org_id := p_event.GetValueForParameter('ORG_ID');
1351 l_user_id := p_event.GetValueForParameter('USER_ID');
1352 l_resp_id := p_event.GetValueForParameter('RESP_ID');
1353 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
1354 l_security_group_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
1355 id := p_event.getValueForParameter('RELATIONSHIP_ID');
1356
1357 IF (PV_DEBUG_HIGH_ON) THEN
1358 WRITE_LOG
1359 (
1360 l_api_name
1361 , 'relationship id is ' || id
1362 );
1363 END IF;
1364 fnd_global.apps_initialize
1365 (
1366 l_user_id
1367 , l_resp_id
1368 , l_resp_appl_id
1369 , l_security_group_id
1370 );
1371
1372 --call register_terminate with new memb type = null;
1373 -- the logic should be
1374 -- check whether the relationship is inactive
1375 -- check whether the subsidiary has any other active global sub relationship.
1376 -- if not update the enty attr _value s table to STANDARD
1377 -- terminate all the subsidiary membersips that are because of the previous global.
1378 OPEN rel_status ( id );
1379 FETCH rel_status INTO l_status, l_subject_id;
1380 CLOSE rel_status;
1381
1382 IF l_status= 'I' THEN
1383 -- check whether the subsidiary has any other active global sub relationship.
1384 IF (PV_DEBUG_HIGH_ON) THEN
1385 WRITE_LOG
1386 (
1387 l_api_name
1388 , 'inside if , if status is still Inactive '
1389 );
1390 END IF;
1391 BEGIN
1392 SELECT 1 INTO l_count
1393 FROM HZ_RELATIONSHIPS
1394 WHERE SUBJECT_ID = l_subject_id -- subsidiary partner party id
1395 AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
1396 AND OBJECT_TYPE = 'ORGANIZATION'
1397 AND RELATIONSHIP_TYPE = 'PARTNER_HIERARCHY'
1398 AND DIRECTION_CODE = 'C'
1399 AND STATUS='A';
1400 -- there is already a parent, so set the flag
1401 l_sub_flag:= 'Y';
1402 EXCEPTION
1403 WHEN NO_DATA_FOUND THEN
1404 -- no other parent found, proceed
1405 l_sub_flag:= 'N';
1406 END;
1407 IF (PV_DEBUG_HIGH_ON) THEN
1408 WRITE_LOG
1409 (
1410 l_api_name
1411 , 'is global found found flag ' || l_sub_flag
1412 );
1413 END IF;
1414
1415 IF l_sub_flag = 'N' THEN
1416 FOR rec in c_get_partner_id( id ) LOOP
1417 -- call upsert api with value 'STANDARD' and terminate program memberships
1418 IF (PV_DEBUG_HIGH_ON) THEN
1419 WRITE_LOG
1420 (
1421 l_api_name
1422 , ' before calling register api'
1423 );
1424 END IF;
1425 OPEN attr_version_cur(rec.partner_id);
1426 FETCH attr_version_cur INTO l_version,l_memb_type;
1427 CLOSE attr_version_cur;
1428 IF l_memb_type <> 'STANDARD' THEN
1429 l_attr_value_tbl_type(1).attr_value:='STANDARD';
1430 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value
1431 (
1432 p_api_version_number => 1.0
1433 ,p_init_msg_list => FND_API.g_false
1434 ,p_commit => FND_API.g_false
1435 ,p_validation_level => FND_API.g_valid_level_full
1436 ,x_return_status => x_return_status
1437 ,x_msg_count => x_msg_count
1438 ,x_msg_data => x_msg_data
1439 ,p_attribute_id => 6
1440 ,p_entity => 'PARTNER'
1441 ,p_entity_id => rec.partner_id
1442 ,p_version => l_version
1446 WRITE_LOG
1443 ,p_attr_val_tbl => l_attr_value_tbl_type
1444 );
1445 IF (PV_DEBUG_HIGH_ON) THEN
1447 (
1448 l_api_name
1449 , 'after Register_term_ptr_memb_type call return status ' || x_return_status || 'msgdata' || x_msg_data
1450 );
1451 END IF;
1452 END IF;
1453
1454 --call terminate ptr memberships api to terminate all program memberships
1455 PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships
1456 (
1457 p_api_version_number => 1.0
1458 , p_init_msg_list => FND_API.G_FALSE
1459 , p_commit => FND_API.G_FALSE
1460 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1461 , p_partner_id => rec.partner_id
1462 , p_memb_type => null
1463 , p_status_reason_code => 'PTR_INACTIVE'
1464 , p_comments => null
1465 , x_return_status => x_return_status
1466 , x_msg_count => x_msg_count
1467 , x_msg_data => x_msg_data
1468 );
1469
1470 IF (PV_DEBUG_HIGH_ON) THEN
1471 WRITE_LOG
1472 (
1473 l_api_name
1474 , 'after Terminate_ptr_memberships call return status ' || x_return_status || 'msgdata' || x_msg_data
1475 );
1476 END IF;
1477 END LOOP;
1478 END IF; -- end of if , l_sub_flag is N
1479
1480 -- check whether relationship is still active
1481 -- check whether global partner is global, if not call change member type api to make it global
1482 -- then for the subsidiary , call upsert api to change the member type to subsidiary
1483
1484 ELSIF l_status = 'A' THEN
1485 FOR rec in c_get_sub_glob( id ) LOOP
1486 IF rec.attr_value <> 'GLOBAL' THEN
1487 Process_ptr_member_type
1488 (
1489 p_api_version_number => 1.0
1490 , p_init_msg_list => FND_API.G_FALSE
1491 , p_commit => FND_API.G_FALSE
1492 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1493 , p_partner_id => rec.global_partner_id
1494 , p_chg_from_memb_type => rec.attr_value
1495 , p_chg_to_memb_type => 'GLOBAL'
1496 , p_chg_to_global_ptr_id => null
1497 , x_return_status => x_return_status
1498 , x_msg_count => x_msg_count
1499 , x_msg_data => x_msg_data
1500 );
1501 IF (PV_DEBUG_HIGH_ON) THEN
1502 WRITE_LOG
1503 (
1504 l_api_name
1505 , 'after Process_ptr_member_type call return status ' || x_return_status || 'msgdata' || x_msg_data
1506 );
1507 END IF;
1508 END IF;
1509 -- just call upsert api to change the member type to subsidiary if its not subsidiary
1510
1511 OPEN attr_version_cur(rec.subsidiary_partner_id);
1512 FETCH attr_version_cur INTO l_version,l_memb_type;
1513 CLOSE attr_version_cur;
1514 IF l_memb_type <> 'SUBSIDIARY' THEN
1515 l_attr_value_tbl_type(1).attr_value:='SUBSIDIARY';
1516 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value
1517 (
1518 p_api_version_number => 1.0
1519 ,p_init_msg_list => FND_API.g_false
1520 ,p_commit => FND_API.g_false
1521 ,p_validation_level => FND_API.g_valid_level_full
1522 ,x_return_status => x_return_status
1523 ,x_msg_count => x_msg_count
1527 ,p_entity_id => rec.subsidiary_partner_id
1524 ,x_msg_data => x_msg_data
1525 ,p_attribute_id => 6
1526 ,p_entity => 'PARTNER'
1528 ,p_version => l_version
1529 ,p_attr_val_tbl => l_attr_value_tbl_type
1530 );
1531 IF (PV_DEBUG_HIGH_ON) THEN
1532 WRITE_LOG
1536 );
1533 (
1534 l_api_name
1535 , 'after Upsert_Attr_Value call return status ' || x_return_status || 'msgdata' || x_msg_data
1537 END IF;
1538 END IF;
1539 END LOOP;
1540 END IF; -- end of if , if l_status = 'I'
1541 END IF; -- end of IF ( l_key like 'oracle.apps.ar.hz.Relationship.update%' OR l_key like 'oracle.apps.ar.hz.Relationship.create%' ) THEN
1542 RETURN 'SUCCESS';
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 WF_CORE.CONTEXT('pv_ptr_member_type_pvt', 'terminate_partner', p_event.getEventName(), p_subscription_guid);
1546 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1547 FND_MESSAGE.SET_NAME('PV', 'PV_API_OTHERS_EXCEP');
1548 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1549 FND_MSG_PUB.ADD;
1550 RETURN 'ERROR';
1551 END;
1552
1553
1554 PROCEDURE update_partner_dtl
1555 (
1556 p_api_version_number IN NUMBER
1557 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1558 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1559 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1560 , p_partner_id IN NUMBER
1561 , p_old_partner_status IN VARCHAR2
1562 , p_new_partner_status IN VARCHAR2
1563 , p_chg_from_memb_type IN VARCHAR2
1564 , p_chg_to_memb_type IN VARCHAR2
1565 , p_old_global_ptr_id IN NUMBER DEFAULT NULL
1566 , p_new_global_ptr_id IN NUMBER DEFAULT NULL
1567 , x_return_status OUT NOCOPY VARCHAR2
1568 , x_msg_count OUT NOCOPY NUMBER
1569 , x_msg_data OUT NOCOPY VARCHAR2
1570 ) IS
1571 l_api_version_number CONSTANT NUMBER := 1.0;
1572 l_api_name CONSTANT VARCHAR2(30) := 'update_partner_dtl';
1573 l_to_memb_type VARCHAR2(30);
1574 l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
1575 l_param_tbl_var1 PVX_UTILITY_PVT.log_params_tbl_type;
1576 l_from_memb_type VARCHAR2(30);
1577 l_from_party_name VARCHAR2(360);
1578 l_to_party_name VARCHAR2(360);
1579
1580 CURSOR get_memb_csr ( attr_cd IN VARCHAR2 ) IS
1581 SELECT DESCRIPTION
1582 FROM PV_ATTRIBUTE_CODES_VL
1583 WHERE ATTRIBUTE_ID = 6
1584 AND ENABLED_FLAG = 'Y'
1585 AND ATTR_CODE =attr_cd;
1586
1587 CURSOR get_party_csr( p_partner_id IN NUMBER ) IS
1588 SELECT party_name
1589 FROM hz_parties party
1590 ,pv_partner_profiles prof
1591 WHERE prof.partner_id=p_partner_id
1592 AND prof.partner_party_id=party.party_id;
1593
1594 BEGIN
1595 -- Standard Start of API savepoint
1596 SAVEPOINT update_partner_dtl ;
1597 -- Standard call to check for call compatibility.
1598 IF NOT FND_API.Compatible_API_Call
1599 ( l_api_version_number
1600 ,p_api_version_number
1601 ,l_api_name
1602 ,G_PKG_NAME
1603 )
1604 THEN
1605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1606 END IF;
1607 -- Initialize message list if p_init_msg_list is set to TRUE.
1608 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1609 FND_MSG_PUB.initialize;
1610 END IF;
1611 -- Debug Message
1612
1613 IF (PV_DEBUG_HIGH_ON) THEN
1614 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1615 END IF;
1616
1617 -- Initialize API return status to SUCCESS
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619 -- Validate Environment
1620 IF FND_GLOBAL.USER_ID IS NULL THEN
1621 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1622 RAISE FND_API.G_EXC_ERROR;
1623 END IF;
1624
1625 -- validate all the required in parameters
1626 IF ( p_partner_id IS NULL OR p_partner_id = FND_API.G_MISS_NUM ) THEN
1627 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1628 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'PARTNER_ID' );
1629 FND_MSG_PUB.add;
1630 RAISE FND_API.G_EXC_ERROR;
1631 END IF;
1632 IF ( p_old_partner_status = FND_API.G_MISS_CHAR OR p_old_partner_status is NULL ) THEN
1633 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1634 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'OLD PARTNER STATUS' );
1635 FND_MSG_PUB.add;
1636 RAISE FND_API.G_EXC_ERROR;
1637 END IF;
1638 IF ( p_new_partner_status = FND_API.G_MISS_CHAR OR p_new_partner_status is NULL ) THEN
1639 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1640 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'NEW PARTNER STATUS' );
1641 FND_MSG_PUB.add;
1642 RAISE FND_API.G_EXC_ERROR;
1643 END IF;
1644 IF ( p_new_partner_status = 'A' ) THEN
1645 IF ( p_chg_from_memb_type IS NULL OR p_chg_from_memb_type = FND_API.G_MISS_CHAR ) THEN
1646 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1647 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'CHANGE_FROM_MEMBER_TYPE' );
1648 FND_MSG_PUB.add;
1649 RAISE FND_API.G_EXC_ERROR;
1650 END IF;
1651
1652 IF ( p_chg_to_memb_type IS NULL OR p_chg_to_memb_type = FND_API.G_MISS_CHAR ) THEN
1653 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1654 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'CHANGE_TO_MEMBER_TYPE' );
1658 IF ( p_chg_to_memb_type ='SUBSIDIARY' AND ( p_new_global_ptr_id IS NULL OR p_new_global_ptr_id = FND_API.G_MISS_NUM) ) THEN
1655 FND_MSG_PUB.add;
1656 RAISE FND_API.G_EXC_ERROR;
1657 END IF;
1659 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1660 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'NEW GlOBAL PARTNER ID' );
1661 FND_MSG_PUB.add;
1662 RAISE FND_API.G_EXC_ERROR;
1663 IF ( p_chg_from_memb_type ='SUBSIDIARY'AND ( p_old_global_ptr_id IS NULL OR p_old_global_ptr_id = FND_API.G_MISS_NUM ) ) THEN
1664 FND_MESSAGE.SET_NAME('PV', 'PV_MISSING_ITEM');
1665 FND_MESSAGE.SET_TOKEN('ITEM_NAME', 'OLD GlOBAL PARTNER ID' );
1666 FND_MSG_PUB.add;
1667 RAISE FND_API.G_EXC_ERROR;
1668 END IF;
1669 END IF;
1670 END IF;
1671
1672 IF ( p_old_partner_status <> p_new_partner_status and p_new_partner_status ='I' ) THEN
1676 ,p_init_msg_list => FND_API.G_FALSE
1673 PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships
1674 (
1675 p_api_version_number => 1.0
1677 ,p_commit => FND_API.G_FALSE
1678 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1679 ,p_partner_id => p_partner_id
1680 ,p_memb_type => null
1681 ,p_status_reason_code => 'PTR_INACTIVE' -- it validates against PV_MEMB_STATUS_REASON_CODE
1682 ,p_comments => null
1683 ,x_return_status => x_return_status
1684 ,x_msg_count => x_msg_count
1685 ,x_msg_data => x_msg_data
1686 );
1687 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1688 RAISE FND_API.G_EXC_ERROR;
1689 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1691 END IF;
1692
1693 Register_term_ptr_memb_type
1694 (
1695 p_api_version_number => 1.0
1696 , p_init_msg_list => FND_API.G_FALSE
1697 , p_commit => FND_API.G_FALSE
1698 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1699 , p_partner_id => p_partner_id
1700 , p_current_memb_type => null
1701 , p_new_memb_type => null
1702 , p_global_ptr_id => null
1703 , x_return_status => x_return_status
1704 , x_msg_count => x_msg_count
1705 , x_msg_data => x_msg_data
1706 );
1707 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1708 RAISE FND_API.G_EXC_ERROR;
1712
1709 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1711 END IF;
1713 -- changed by paul on 28may04 as after discussion with Ravi and Karen
1714 -- to revoke responsibilities when partner is inactivated
1715 Pv_User_Resp_Pvt.revoke_default_resp (
1716 p_api_version_number => 1.0
1717 ,p_init_msg_list => FND_API.G_FALSE
1718 ,p_commit => FND_API.G_FALSE
1719 ,x_return_status => x_return_status
1720 ,x_msg_count => x_msg_count
1721 ,x_msg_data => x_msg_data
1722 ,p_partner_id => p_partner_id
1723 );
1724
1725 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1726 RAISE FND_API.G_EXC_ERROR;
1727 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730
1731 ELSE
1732
1733 IF( p_new_partner_status ='A' and ( p_chg_from_memb_type <> p_chg_to_memb_type ) or ( p_old_global_ptr_id <> p_new_global_ptr_id and p_chg_to_memb_type ='SUBSIDIARY' ) ) THEN
1734 Process_ptr_member_type
1735 (
1736 p_api_version_number => 1.0
1737 , p_init_msg_list => FND_API.G_FALSE
1738 , p_commit => FND_API.G_FALSE
1739 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1740 , p_partner_id => p_partner_id
1741 , p_chg_from_memb_type => p_chg_from_memb_type
1742 , p_chg_to_memb_type => p_chg_to_memb_type
1743 , p_chg_to_global_ptr_id => p_new_global_ptr_id
1744 , x_return_status => x_return_status
1745 , x_msg_count => x_msg_count
1746 , x_msg_data => x_msg_data
1747 );
1748
1749 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1750 RAISE FND_API.G_EXC_ERROR;
1751 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1753 END IF;
1754 IF p_chg_from_memb_type<>p_chg_to_memb_type THEN
1755 --set the message the member type has been changed.
1756 -- call the history log api.
1757 OPEN get_memb_csr( p_chg_from_memb_type ) ;
1758 FETCH get_memb_csr INTO l_from_memb_type;
1759 CLOSE get_memb_csr ;
1760
1761 OPEN get_memb_csr( p_chg_to_memb_type ) ;
1762 FETCH get_memb_csr INTO l_to_memb_type;
1763 CLOSE get_memb_csr ;
1764
1765 l_param_tbl_var(1).param_name := 'FROM_MEMB_TYPE';
1766 l_param_tbl_var(1).param_value := l_from_memb_type;
1767 l_param_tbl_var(2).param_name := 'TO_MEMB_TYPE';
1768 l_param_tbl_var(2).param_value := l_to_memb_type ;
1769
1770
1771 PVX_UTILITY_PVT.create_history_log
1772 (
1773 p_arc_history_for_entity_code => 'GENERAL'
1774 , p_history_for_entity_id => p_partner_id
1775 , p_history_category_code => 'PARTNER'
1776 , p_message_code => 'PV_MEMBER_TYPE_CHANGE'
1777 , p_comments => null
1778 , p_partner_id => p_partner_id
1779 , p_access_level_flag => 'P'
1780 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
1781 , p_log_params_tbl => l_param_tbl_var
1782 , p_init_msg_list => FND_API.g_false
1783 , p_commit => FND_API.G_FALSE
1784 , x_return_status => x_return_status
1785 , x_msg_count => x_msg_count
1786 , x_msg_data => x_msg_data
1787 );
1788 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1789 RAISE FND_API.G_EXC_ERROR;
1790 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1792 END IF;
1793
1794 ELSIF p_chg_from_memb_type=p_chg_to_memb_type AND p_old_global_ptr_id <> p_new_global_ptr_id and p_chg_to_memb_type ='SUBSIDIARY' THEN
1795 --set the message that the subsidiary partners global organisation has changed.
1796 -- call the history log api.
1797 OPEN get_party_csr(p_old_global_ptr_id) ;
1798 FETCH get_party_csr INTO l_from_party_name;
1799 CLOSE get_party_csr ;
1800
1801 l_param_tbl_var1(1).param_name := 'FROM_PARTNER_NAME';
1802 l_param_tbl_var1(1).param_value := l_from_party_name;
1803
1804 OPEN get_party_csr(p_new_global_ptr_id) ;
1805 FETCH get_party_csr INTO l_to_party_name;
1806 CLOSE get_party_csr ;
1807
1808 l_param_tbl_var1(2).param_name := 'TO_PARTNER_NAME';
1809 l_param_tbl_var1(2).param_value := l_to_party_name;
1810
1811 PVX_UTILITY_PVT.create_history_log
1812 (
1813 p_arc_history_for_entity_code => 'GENERAL'
1814 , p_history_for_entity_id => p_partner_id
1815 , p_history_category_code => 'PARTNER'
1816 , p_message_code => 'PV_GLOBAL_PARTNER_CHANGE'
1817 , p_comments => null
1818 , p_partner_id => p_partner_id
1819 , p_access_level_flag => 'P'
1820 , p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50
1821 , p_log_params_tbl => l_param_tbl_var1
1822 , p_init_msg_list => FND_API.g_false
1823 , p_commit => FND_API.G_FALSE
1824 , x_return_status => x_return_status
1825 , x_msg_count => x_msg_count
1826 , x_msg_data => x_msg_data
1827 );
1828
1829 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1830 RAISE FND_API.G_EXC_ERROR;
1831 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833 END IF;
1834
1835 END IF;
1836
1837 END IF;
1838 END IF;
1839
1840
1841
1842 FND_MSG_PUB.Count_And_Get
1843 (
1844 p_count => x_msg_count
1845 , p_data => x_msg_data
1846 );
1847
1848 IF (PV_DEBUG_HIGH_ON) THEN
1849 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1850 END IF;
1851
1852 IF FND_API.to_Boolean( p_commit ) THEN
1853 COMMIT WORK;
1854 END IF;
1855
1856 EXCEPTION
1857 WHEN FND_API.G_EXC_ERROR THEN
1858 ROLLBACK TO update_partner_dtl;
1859 x_return_status := FND_API.G_RET_STS_ERROR;
1860 -- Standard call to get message count and if count=1, get the message
1861 FND_MSG_PUB.Count_And_Get (
1862 p_encoded => FND_API.G_FALSE,
1863 p_count => x_msg_count,
1864 p_data => x_msg_data
1865 );
1866
1867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1868 ROLLBACK TO update_partner_dtl;
1869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1870 -- Standard call to get message count and if count=1, get the message
1871 FND_MSG_PUB.Count_And_Get (
1872 p_encoded => FND_API.G_FALSE,
1873 p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876
1877 WHEN OTHERS THEN
1878 ROLLBACK TO update_partner_dtl;
1879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1880 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1881 THEN
1882 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1883 END IF;
1884 -- Standard call to get message count and if count=1, get the message
1885 FND_MSG_PUB.Count_And_Get (
1886 p_encoded => FND_API.G_FALSE,
1887 p_count => x_msg_count,
1888 p_data => x_msg_data
1889 );
1890
1891 END update_partner_dtl;
1892
1893 END Pv_ptr_member_type_pvt;
[Home] [Help]