[Home] [Help]
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
422: l_param_tbl_var PVX_UTILITY_PVT.log_params_tbl_type;
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
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
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';
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');
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
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
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
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') ;
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';
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
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
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;
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
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: );
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,
896: p_data => x_msg_data
898:
899: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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
912: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
913: THEN
914: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
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
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
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
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: */
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
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
1146: EXCEPTION
1147: WHEN FND_API.G_EXC_ERROR THEN
1148: ROLLBACK TO Process_ptr_member_type;
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
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
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
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;
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
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
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
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
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
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
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
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