[Home] [Help]
348: B.resource_id,
349: B.resource_number ,
350: B.source_id,
351: A.object_version_number
352: from jtf_rs_group_members_vl A, jtf_rs_resource_extns B
353: where group_id = p_group_id
354: and A.resource_id = B.resource_id
355: and A.delete_flag = 'N'
356: and A.resource_id not in ( select resource_id
376: from ibu_usergroups_temp A, jtf_rs_resource_extns B
377: where A.source_id = B.source_id
378: and A.category = B.category
379: and not exists ( select resource_id
380: from jtf_rs_group_members
381: where resource_id = B.resource_id
382: and group_id = p_group_id
383: and delete_flag = 'N')
384: and ( ( B.end_date_active is null ) or (B.end_date_active > sysdate) ) ;
385:
386: l_resource_cnt number;
387: l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
388: l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
389: l_group_member_id jtf_rs_group_members.group_member_id%TYPE;
390:
391: l_fndactive_cnt NUMBER;
392: l_res_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
393: l_res_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
482: Update_Category_SourceId(l_category);
483:
484: /* Delete existing members in the group who should not be part of this user group. Reason : because of change in the user group query */
485: For l_del_mem_rec IN l_del_mem_csr(l_group_id) Loop
486: JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members
487: (P_API_VERSION => 1,
488: P_INIT_MSG_LIST =>FND_API.G_FALSE,
489: P_COMMIT =>FND_API.G_FALSE,
490: P_GROUP_ID =>l_upd_rec.group_id ,
495: X_RETURN_STATUS => l_return_status,
496: X_MSG_COUNT => l_msg_count,
497: X_MSG_DATA => l_msg_data
498: );
499: Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members api ' ||
500: ' l_mem_id = ' || to_char(l_del_mem_rec.group_member_id) || ' ',
501: l_return_status , l_msg_count , l_msg_data );
502: End Loop;
503:
615: End Loop;
616:
617: /* create news members in the group who are not part of this user group. Reason : because of change in the user group query */
618: For l_cre_rec IN l_cre_csr(l_group_id) Loop
619: JTF_RS_GROUP_MEMBERS_PUB.create_resource_group_members(P_API_VERSION => 1.0,
620: P_INIT_MSG_LIST => FND_API.G_FALSE,
621: P_COMMIT => FND_API.G_FALSE,
622: P_GROUP_ID => l_group_id,
623: P_GROUP_NUMBER => l_group_number,
628: X_MSG_DATA => l_msg_data,
629: X_GROUP_MEMBER_ID => l_group_member_id
630: );
631: If (l_return_status <> fnd_api.g_ret_sts_success) Then
632: Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.create_resource_group_members api ' ||
633: ' l_cre_rec.resource_id = ' || to_char(l_cre_rec.resource_id) || ' ',
634: l_return_status , l_msg_count , l_msg_data );
635: End If;
636: End Loop;
717: l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
718: l_group_usage_id JTF_RS_GROUP_USAGES.GROUP_USAGE_ID%TYPE;
719: l_category varchar2(50);
720: l_res_cnt NUMBER;
721: l_group_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE;
722: l_object_version_number jtf_rs_resource_extns.object_version_number%TYPE;
723: l_user_id fnd_user.user_id%TYPE;
724:
725: l_fndactive_cnt NUMBER;
1011: -- Resource exists by now, so create resource member.
1012:
1013: If l_resource_id <> 0 Then
1014: /* resource already exists and it is active. So, call create group members api */
1015: jtf_rs_group_members_pub.create_resource_group_members
1016: (P_API_VERSION => 1.0,
1017: P_INIT_MSG_LIST => FND_API.G_FALSE,
1018: P_COMMIT => FND_API.G_FALSE,
1019: P_GROUP_ID => l_group_id,
1026: X_GROUP_MEMBER_ID => l_group_member_id
1027: );
1028: If (l_return_status <> fnd_api.g_ret_sts_success) Then
1029: select count(*) into l_temp_cnt
1030: from jtf_rs_group_members
1031: Where group_id = l_group_id
1032: and resource_id = l_resource_id
1033: and delete_flag = 'N';
1034: If l_temp_cnt = 0 Then
1031: Where group_id = l_group_id
1032: and resource_id = l_resource_id
1033: and delete_flag = 'N';
1034: If l_temp_cnt = 0 Then
1035: Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.create_resource_group_members api ' ||
1036: ' l_resource_id = ' || to_char(l_resource_id) || ' ',
1037: l_return_status , l_msg_count , l_msg_data );
1038: End If;
1039: End If;
1080: select channel_id
1081: from amv_c_channels_vl
1082: where channel_name = p_group_name;
1083:
1084: Cursor l_del_mem_csr(p_group_id jtf_rs_group_members.group_id%TYPE )
1085: IS
1086: select B.group_member_id,
1087: A.group_id,
1088: A.group_number,
1088: A.group_number,
1089: B.object_version_number,
1090: B.resource_id,
1091: C.resource_number
1092: from jtf_rs_groups_vl A, jtf_rs_group_members B, jtf_rs_resource_extns C
1093: Where A.group_id = p_group_id
1094: and A.group_id = B.group_id
1095: and B.resource_id = C.resource_id
1096: and B.delete_flag = 'N' ;
1194: /* Find category for the SQL_TEXT */
1195: get_category(l_del_rec.sql_text, l_category);
1196:
1197: For l_del_mem_rec IN l_del_mem_csr(l_del_rec.static_group_id) Loop
1198: jtf_rs_group_members_pub.delete_resource_group_members(P_API_VERSION => 1.0,
1199: P_INIT_MSG_LIST => FND_API.G_FALSE,
1200: P_COMMIT => FND_API.G_FALSE,
1201: P_GROUP_ID => l_del_mem_rec.group_id,
1202: P_GROUP_NUMBER => l_del_mem_rec.group_number,
1207: X_MSG_COUNT => l_msg_count,
1208: X_MSG_DATA => l_msg_data
1209: );
1210: If (l_return_status <> fnd_api.g_ret_sts_success) Then
1211: Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members api ' ||
1212: ' l_mem_id = ' || to_char(l_del_mem_rec.group_member_id) || ' ',
1213: l_return_status , l_msg_count , l_msg_data );
1214: End If;
1215: End Loop; -- End of l_del_mem_csr loop