[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_id
FROM pv_partner_primary_users_v
WHERE partner_id = p_partner_id;
SELECT user_id
FROM pv_partner_business_users_v
WHERE partner_id = p_partner_id;
SELECT user_id
FROM (
SELECT jtfre.user_id user_id, pvpp.partner_id partner_id
FROM pv_partner_profiles pvpp, hz_relationships hzr, jtf_rs_resource_extns jtfre, fnd_user fndu
WHERE pvpp.partner_party_id = hzr.object_id
AND hzr.relationship_code = 'EMPLOYEE_OF'
AND HZR.subject_table_name ='HZ_PARTIES'
AND HZR.object_table_name ='HZ_PARTIES'
AND HZR.directional_flag = 'F'
AND hzr.start_date <= SYSDATE
AND (hzr.end_date is null or hzr.end_date > sysdate)
AND HZR.status = 'A'
AND hzr.party_id = jtfre.source_id
AND jtfre.category = 'PARTY'
AND fndu.user_id = jtfre.user_id
AND fndu.start_date <= sysdate
AND (fndu.end_date is null or fndu.end_date > sysdate)
AND exists (
SELECT jtfp1.principal_name username
FROM jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd, jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
WHERE jtfp1.is_user_flag=1
AND jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
AND jtfp2.jtf_auth_principal_id=jtfpm.jtf_auth_parent_principal_id
AND jtfp2.is_user_flag=0
AND jtfp2.jtf_auth_principal_id=jtfrp.jtf_auth_principal_id
AND jtfrp.positive_flag = 1
AND jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
AND jtfperm.permission_name in ('PV_PARTNER_USER', 'IBE_INT_PRIMARY_USER')
AND jtfd.jtf_auth_domain_id = jtfpm.jtf_auth_domain_id
AND jtfd.domain_name = 'CRM_DOMAIN'
and jtfp1.principal_name = jtfre.user_name
GROUP BY jtfp1.principal_name
HAVING count (distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', null, jtfperm.permission_name) ) = 1
AND count(distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', jtfperm.permission_name, null )) =1
)
)
WHERE partner_id = p_partner_id;
SELECT user_id
FROM (
SELECT jtfre.user_id user_id, pvpp.partner_id partner_id
FROM pv_partner_profiles pvpp, hz_relationships hzr, jtf_rs_resource_extns jtfre, fnd_user fndu
WHERE pvpp.partner_party_id = hzr.object_id
AND hzr.relationship_code = 'EMPLOYEE_OF'
AND HZR.subject_table_name ='HZ_PARTIES'
AND HZR.object_table_name ='HZ_PARTIES'
AND HZR.directional_flag = 'F'
AND hzr.start_date <= SYSDATE
AND (hzr.end_date is null or hzr.end_date > sysdate)
AND HZR.status = 'A'
AND hzr.party_id = jtfre.source_id
AND jtfre.category = 'PARTY'
AND fndu.user_id = jtfre.user_id
AND fndu.start_date <= sysdate
AND (fndu.end_date is null or fndu.end_date > sysdate)
AND exists (
SELECT jtfp1.principal_name username
FROM jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd, jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
WHERE jtfp1.is_user_flag = 1
AND jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_principal_id
AND jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
AND jtfp2.is_user_flag = 0
AND jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
AND jtfrp.positive_flag = 1
AND jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
AND jtfperm.permission_name in ('PV_PARTNER_USER', 'IBE_INT_PRIMARY_USER')
AND jtfd.jtf_auth_domain_id = jtfpm.jtf_auth_domain_id
AND jtfd.domain_name = 'CRM_DOMAIN'
and jtfp1.principal_name = jtfre.user_name
GROUP BY jtfp1.principal_name
HAVING count( distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', null, jtfperm.permission_name ) ) = 1
AND count (distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER' , jtfperm.permission_name, null ) ) = 0 )
)
WHERE partner_id = p_partner_id;
select pvpp.partner_id partner_id
from jtf_rs_resource_extns RES, hz_relationships hzr, pv_partner_profiles pvpp
where RES.user_id = p_user_id
and RES.category = 'PARTY'
and RES.start_date_active <= SYSDATE and nvl(RES.end_date_active , sysdate) >= SYSDATE
and RES.source_id = hzr.party_id and hzr.directional_flag = 'F'
and hzr.relationship_code = 'EMPLOYEE_OF' and HZR.subject_table_name ='HZ_PARTIES'
and HZR.object_table_name ='HZ_PARTIES' and hzr.start_date <= SYSDATE
and (hzr.end_date is null or hzr.end_date > SYSDATE)
and hzr.object_id = pvpp.partner_party_id
and pvpp.status = 'A';
SELECT user_id, responsibility_id, responsibility_application_id, security_group_id, start_date, description
FROM fnd_user_resp_groups
WHERE user_id in (
SELECT * FROM TABLE (CAST(p_user_id AS JTF_NUMBER_TABLE))
)
AND responsibility_id = p_resp_id;
SELECT jtfperm.permission_name
FROM jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2,
jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm,
fnd_user fndu
WHERE fndu.user_id = p_user_id
AND jtfp1.principal_name = fndu.user_name
AND jtfp1.is_user_flag=1
AND jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
AND jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
AND jtfp2.is_user_flag=0
AND jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
AND jtfrp.positive_flag = 1
AND jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
AND jtfperm.permission_name in ('IBE_INT_PRIMARY_USER', 'PV_PARTNER_USER')
AND jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
AND jtfd.domain_name='CRM_DOMAIN'
GROUP BY jtfperm.permission_name;
SELECT resp_map_rule_id, geo_hierarchy_id, responsibility_id
FROM pv_ge_resp_map_rules
WHERE user_role_code = p_user_role_code
AND program_id is null
AND delete_flag = 'N';
SELECT rmr.resp_map_rule_id, rmr.responsibility_id
FROM pv_ge_resp_map_rules rmr, jtf_loc_hierarchies_vl lh
WHERE rmr.user_role_code = p_user_role_code
AND rmr.program_id is null
AND rmr.geo_hierarchy_id = lh.location_hierarchy_id
AND lh.location_type_code = 'AREA1'
AND rmr.delete_flag = 'N';
SELECT resp_map_rule_id, geo_hierarchy_id, responsibility_id
FROM pv_ge_resp_map_rules
WHERE user_role_code = cv_user_role_code
AND program_id is null
AND delete_flag = 'N';
SELECT rmr.resp_map_rule_id, rmr.responsibility_id
FROM pv_ge_resp_map_rules rmr, jtf_loc_hierarchies_vl lh
WHERE rmr.user_role_code = p_user_role_code
AND rmr.program_id is null
AND rmr.geo_hierarchy_id = lh.location_hierarchy_id
AND lh.location_type_code = 'AREA1'
AND rmr.delete_flag = 'N';
SELECT resp_map_rule_id, geo_hierarchy_id, responsibility_id
FROM pv_ge_resp_map_rules
WHERE user_role_code = cv_user_role_code
AND program_id = cv_program_id
AND delete_flag = 'N';
SELECT rmr.resp_map_rule_id, rmr.responsibility_id
FROM pv_ge_resp_map_rules rmr, jtf_loc_hierarchies_vl lh
WHERE rmr.user_role_code = p_user_role_code
AND rmr.program_id = cv_program_id
AND rmr.geo_hierarchy_id = lh.location_hierarchy_id
AND lh.location_type_code = 'AREA1'
AND rmr.delete_flag = 'N';
SELECT ben.benefit_id, ben.program_benefits_id
FROM pv_program_benefits ben
WHERE
ben.benefit_type_code = 'STORES'
AND ben.delete_flag = 'N'
AND ben.program_id in
(
SELECT program_id
FROM pv_partner_program_b
START WITH program_id in
(
SELECT /*+ leading(T) USE_NL(T MEM)*/ mem.program_id
FROM pv_pg_memberships mem, (SELECT column_value FROM TABLE (CAST(p_partner_id AS JTF_NUMBER_TABLE))) t
WHERE mem.partner_id = t.column_value
AND mem.membership_status_code = 'ACTIVE'
)
CONNECT BY PRIOR program_parent_id = program_id
);
SELECT ben.benefit_id, ben.program_benefits_id
FROM pv_program_benefits ben
WHERE ben.program_id IN (
SELECT program_id
FROM pv_partner_program_b
START WITH program_id = p_program_id
CONNECT BY PRIOR program_parent_id = program_id
)
AND ben.benefit_type_code = 'STORES'
AND ben.delete_flag = 'N';
SELECT /*+ CARDINALITY(t 10) */ responsibility_id
FROM pv_ge_ptnr_resps,
(SELECT * FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))) t
WHERE partner_id = t.column_value
AND user_role_code = cv_user_role_code
AND resp_type_code = cv_resp_type_code;
SELECT /*+ CARDINALITY(t 10) */ program_id, partner_id
FROM pv_pg_memberships,
(SELECT * FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))) t
WHERE partner_id = t.column_value
AND membership_status_code = 'ACTIVE';
select usr.user_id
from jtf_rs_resource_extns extn, fnd_user usr
where extn.user_id = usr.user_id
and usr.user_name = p_user_name;
(SELECT /*+LEADING(T) USE_NL(t p f)*/ p.responsibility_id, 'REVOKE' action, f.responsibility_application_id, f.security_group_id, f.start_date, f.description
FROM pv_ge_ptnr_resps p, fnd_user_resp_groups f
WHERE partner_id in (
SELECT * FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))
)
AND p.user_role_code = cv_from_user_role_code
AND p.responsibility_id = f.responsibility_id
AND f.user_id = cv_user_id
AND p.resp_type_code = G_PROGRAM
MINUS
SELECT /*+LEADING(T) USE_NL(t p f)*/ p.responsibility_id, 'REVOKE' action, f.responsibility_application_id, f.security_group_id, f.start_date, f.description
FROM pv_ge_ptnr_resps p, fnd_user_resp_groups f
WHERE partner_id in (
SELECT * FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))
)
AND p.user_role_code = cv_to_user_role_code
AND p.responsibility_id = f.responsibility_id
AND f.user_id = cv_user_id
AND p.resp_type_code = G_PROGRAM
)
UNION
(SELECT /*+LEADING(T) USE_NL(t p f)*/ p.responsibility_id, 'ASSIGN' action, f.responsibility_application_id, f.security_group_id, f.start_date, f.description
FROM pv_ge_ptnr_resps p, fnd_user_resp_groups f
WHERE partner_id in (
SELECT * FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))
)
AND p.user_role_code = cv_to_user_role_code
AND p.responsibility_id = f.responsibility_id
AND f.user_id = cv_user_id
AND p.resp_type_code = G_PROGRAM
MINUS
SELECT /*+LEADING(T) USE_NL(t p f)*/ p.responsibility_id, 'ASSIGN' action, f.responsibility_application_id, f.security_group_id, f.start_date, f.description
FROM pv_ge_ptnr_resps p, fnd_user_resp_groups f
WHERE partner_id in (
SELECT * FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))
)
AND p.user_role_code = cv_from_user_role_code
AND p.responsibility_id = f.responsibility_id
AND f.user_id = cv_user_id
AND p.resp_type_code = G_PROGRAM
);
* 2. Update the corresponding row (using p_ptnr_resp_id) in pv_ge_ptnr_resps.
* 3. Assign the new responsibility_id that passed in.
************/
PROCEDURE revoke_update_assign(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_user_ids_tbl IN JTF_NUMBER_TABLE
,p_ptnr_resp_id IN NUMBER
,p_old_responsibility_id IN NUMBER
,p_new_responsibility_id IN NUMBER
,p_program_id IN NUMBER := null
,p_resp_map_rule_id IN NUMBER := null
,p_object_version_number IN NUMBER
,p_is_revoke IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'revoke_update_assign';
Debug_Log('revoke_update_assign: p_old_responsibility_id' || p_old_responsibility_id);
* API to update the resp in pv_partner_memberships
****/
l_ge_ptnr_resps_rec.ptnr_resp_id := p_ptnr_resp_id;
Debug_Log('revoke_update_assign: l_ge_ptnr_resps_rec.source_resp_map_rule_id = ' || l_ge_ptnr_resps_rec.source_resp_map_rule_id);
Debug_Log('revoke_update_assign: l_ge_ptnr_resps_rec.program_id = ' || l_ge_ptnr_resps_rec.program_id);
Debug_Log('revoke_update_assign: l_ge_ptnr_resps_rec.ptnr_resp_id = ' || l_ge_ptnr_resps_rec.ptnr_resp_id);
Debug_Log('revoke_update_assign: l_ge_ptnr_resps_rec.responsibility_id = ' || l_ge_ptnr_resps_rec.responsibility_id);
Debug_Log('revoke_update_assign: l_ge_ptnr_resps_rec.object_version_number = ' || l_ge_ptnr_resps_rec.object_version_number);
PV_Ge_Ptnr_Resps_PVT.Update_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ge_ptnr_resps_rec => l_ge_ptnr_resps_rec
);
Debug_Log('revoke_update_assign: p_new_responsibility_id' || p_new_responsibility_id);
END revoke_update_assign;
SELECT ptnr_resp_id, user_role_code, responsibility_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE partner_id = p_partner_id
AND program_id = p_program_id
AND resp_type_code = cv_resp_type_code;
SELECT responsibility_id
FROM pv_ge_ptnr_resps
WHERE partner_id in (
SELECT partner_id
FROM pv_partner_profiles
WHERE partner_party_id in (
SELECT partner_party_id
FROM pv_partner_profiles
WHERE partner_id = p_partner_id
)
AND partner_id <> p_partner_id
)
AND user_role_code = cv_user_role_code
AND resp_type_code = cv_resp_type_code;
SELECT count(*)
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND responsibility_id = cv_responsibility_id
AND resp_type_code = cv_resp_type_code;
SELECT 1
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND resp_type_code = G_PROGRAM;
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_ter_exp_memb_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_ter_exp_memb_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_ter_exp_memb_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
SELECT ptnr_resp_id, responsibility_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND program_id is null
AND user_role_code = cv_user_role_code
AND resp_type_code = G_PROGRAM;
SELECT prev.program_id
FROM pv_pg_mmbr_transitions tran, pv_pg_memberships prev
WHERE tran.to_membership_id = cv_membership_id
AND prev.membership_id = tran.from_membership_id;
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_active_memb_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_active_memb_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
SELECT membership_status_code, partner_id, program_id
FROM pv_pg_memberships
WHERE membership_id = cv_membership_id;
* delete_resp_mapping
* This public API will take care of managing partner user responsibilities when
* responsibility mapping is soft deleted.
*/
PROCEDURE delete_resp_mapping(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_source_resp_map_rule_id IN NUMBER
)
IS
CURSOR c_get_ptnr_resps (cv_source_resp_map_rule_id NUMBER) IS
SELECT ptnr_resp_id, responsibility_id, partner_id, user_role_code, program_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE source_resp_map_rule_id = cv_source_resp_map_rule_id
AND resp_type_code = G_PROGRAM
ORDER BY partner_id, user_role_code;
SELECT count(*)
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND responsibility_id = cv_responsibility_id
AND resp_type_code = G_PROGRAM;
SELECT 1
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND resp_type_code = G_PROGRAM;
l_api_name CONSTANT VARCHAR2(30) := 'delete_resp_mapping';
SAVEPOINT delete_resp_mapping;
Debug_Log('delete_resp_mapping: x.program_id = ' || x.program_id);
Debug_Log('delete_resp_mapping: x.responsibility_id = ' || x.responsibility_id);
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('delete_resp_mapping: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
Debug_Log('delete_resp_mapping: x.ptnr_resp_id = ' || x.ptnr_resp_id);
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
Debug_Log('delete_resp_mapping: before calling get_program_resp');
Debug_Log('delete_resp_mapping: (l_responsibility_id is null or l_resp_map_rule_id is null) 1');
Debug_Log('delete_resp_mapping: !!!(l_responsibility_id is null or l_resp_map_rule_id is null) and l_count = 1');
Debug_Log('delete_resp_mapping: before calling PV_Ge_Ptnr_Resps_PVT.Create_Ge_Ptnr_Resps');
Debug_Log('delete_resp_mapping: l_ge_ptnr_resps_rec.partner_id = ' || l_ge_ptnr_resps_rec.partner_id);
Debug_Log('delete_resp_mapping: l_ge_ptnr_resps_rec.user_role_code = ' || l_ge_ptnr_resps_rec.user_role_code);
Debug_Log('delete_resp_mapping: l_ge_ptnr_resps_rec.responsibility_id = ' || l_ge_ptnr_resps_rec.responsibility_id);
Debug_Log('delete_resp_mapping: l_ge_ptnr_resps_rec.program_id = ' || l_ge_ptnr_resps_rec.program_id);
Debug_Log('delete_resp_mapping: l_ge_ptnr_resps_rec.source_resp_map_rule_id = ' || l_ge_ptnr_resps_rec.source_resp_map_rule_id);
Debug_Log('delete_resp_mapping: l_ge_ptnr_resps_rec.resp_type_code = ' || l_ge_ptnr_resps_rec.resp_type_code);
Debug_Log('delete_resp_mapping: x.program_id is null');
Debug_Log('delete_resp_mapping: x.responsibility_id = ' || x.responsibility_id);
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('delete_resp_mapping: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
Debug_Log('delete_resp_mapping: x.ptnr_resp_id = ' || x.ptnr_resp_id);
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
Debug_Log('delete_resp_mapping: before calling get_default_assign_addrow');
ROLLBACK TO delete_resp_mapping;
ROLLBACK TO delete_resp_mapping;
ROLLBACK TO delete_resp_mapping;
END delete_resp_mapping;
* update_resp_mapping
* This public API will take care of managing partner user responsibilities when
* responsibility mapping is updated with a new responsibility.
*/
PROCEDURE update_resp_mapping(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_source_resp_map_rule_id IN NUMBER
)
IS
CURSOR c_get_ptnr_resps (cv_source_resp_map_rule_id NUMBER) IS
SELECT ptnr_resp_id, responsibility_id, partner_id, user_role_code, program_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE source_resp_map_rule_id = cv_source_resp_map_rule_id
AND resp_type_code = G_PROGRAM
ORDER BY partner_id, user_role_code;
SELECT responsibility_id
FROM pv_ge_resp_map_rules
WHERE resp_map_rule_id = cv_source_resp_map_rule_id;
SELECT count(*)
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND responsibility_id = cv_responsibility_id
AND resp_type_code = G_PROGRAM;
l_api_name CONSTANT VARCHAR2(30) := 'update_resp_mapping';
SAVEPOINT update_resp_mapping;
Debug_Log('update_resp_mapping: new x.responsibility_id = ' || x.responsibility_id);
Debug_Log('update_resp_mapping: x.responsibility_id = ' || x.responsibility_id);
Debug_Log('update_resp_mapping: x.program_id = ' || x.program_id);
Debug_Log('update_resp_mapping: x.program_id is not null');
Debug_Log('update_resp_mapping: before calling revoke_update_assign');
revoke_update_assign(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_user_ids_tbl => l_user_ids_tbl
,p_ptnr_resp_id => x.ptnr_resp_id
,p_old_responsibility_id => x.responsibility_id
,p_new_responsibility_id => l_new_responsibility_id
,p_object_version_number => x.object_version_number
,p_is_revoke => 'Y'
);
Debug_Log('update_resp_mapping: after calling revoke_update_assign');
Debug_Log('update_resp_mapping: before calling revoke_update_assign');
revoke_update_assign(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_user_ids_tbl => l_user_ids_tbl
,p_ptnr_resp_id => x.ptnr_resp_id
,p_old_responsibility_id => x.responsibility_id
,p_new_responsibility_id => l_new_responsibility_id
,p_object_version_number => x.object_version_number
,p_is_revoke => 'N'
);
Debug_Log('update_resp_mapping: after calling revoke_update_assign');
Debug_Log('update_resp_mapping: before calling revoke_update_assign');
revoke_update_assign(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_user_ids_tbl => l_user_ids_tbl
,p_ptnr_resp_id => x.ptnr_resp_id
,p_old_responsibility_id => x.responsibility_id
,p_new_responsibility_id => l_new_responsibility_id
,p_object_version_number => x.object_version_number
,p_is_revoke => 'Y'
);
Debug_Log('update_resp_mapping: before calling revoke_update_assign');
ROLLBACK TO update_resp_mapping;
ROLLBACK TO update_resp_mapping;
ROLLBACK TO update_resp_mapping;
END update_resp_mapping;
SELECT m.partner_id, mr.user_role_code, mr.program_id
FROM pv_pg_memberships m, pv_ge_resp_map_rules mr
WHERE mr.program_id = m.program_id
AND m.membership_status_code = 'ACTIVE'
AND mr.resp_map_rule_id = cv_source_resp_map_rule_id;
SELECT user_role_code
FROM pv_ge_resp_map_rules mr
WHERE program_id is null
AND resp_map_rule_id = cv_source_resp_map_rule_id;
SELECT ptnr_resp_id, program_id, responsibility_id, source_resp_map_rule_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND program_id = cv_program_id
AND resp_type_code = G_PROGRAM;
SELECT ptnr_resp_id, responsibility_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND program_id is null
AND resp_type_code = G_PROGRAM;
SELECT ptnr_resp_id, responsibility_id, source_resp_map_rule_id, object_version_number, partner_id
FROM pv_ge_ptnr_resps
WHERE user_role_code = cv_user_role_code
AND program_id is null
AND resp_type_code = G_PROGRAM;
SELECT count(*)
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND responsibility_id = cv_responsibility_id
AND resp_type_code = G_PROGRAM;
Debug_Log('create_resp_mapping: before calling revoke_update_assign');
revoke_update_assign(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_user_ids_tbl => l_user_ids_tbl
,p_ptnr_resp_id => y.ptnr_resp_id
,p_old_responsibility_id => y.responsibility_id
,p_new_responsibility_id => l_new_responsibility_id
,p_resp_map_rule_id => l_resp_map_rule_id
,p_object_version_number => y.object_version_number
,p_is_revoke => 'Y'
);
Debug_Log('create_resp_mapping: before calling revoke_update_assign');
revoke_update_assign(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_user_ids_tbl => l_user_ids_tbl
,p_ptnr_resp_id => y.ptnr_resp_id
,p_old_responsibility_id => y.responsibility_id
,p_new_responsibility_id => l_new_responsibility_id
,p_object_version_number => y.object_version_number
,p_is_revoke => 'N'
);
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('create_resp_mapping: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => y.ptnr_resp_id
,p_object_version_number => y.object_version_number
);
Debug_Log('create_resp_mapping: before calling revoke_update_assign');
revoke_update_assign(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_user_ids_tbl => l_user_ids_tbl
,p_ptnr_resp_id => y.ptnr_resp_id
,p_old_responsibility_id => y.responsibility_id
,p_new_responsibility_id => l_new_responsibility_id
,p_resp_map_rule_id => l_resp_map_rule_id
,p_object_version_number => y.object_version_number
,p_is_revoke => 'Y'
);
SELECT /*+ LEADING(T) USE_NL (T P F) */
p.responsibility_id, f.responsibility_application_id, f.security_group_id,
f.start_date, f.description, f.user_id
FROM pv_ge_ptnr_resps p,
(SELECT column_value FROM TABLE (CAST(cv_partner_id AS JTF_NUMBER_TABLE))) t ,
fnd_user_resp_groups f
WHERE p.partner_id = t.column_value
AND nvl(end_date, sysdate) >= sysdate
AND f.user_id = p_user_id
AND f.responsibility_id = p.responsibility_id
AND user_role_code in (p_user_role_code, G_ALL);
SELECT
user_id, responsibility_id, responsibility_application_id, security_group_id,
start_date, description
FROM fnd_user_resp_groups,
(SELECT column_value FROM TABLE (CAST(cv_responsibility_id_tbl AS JTF_NUMBER_TABLE))) t
WHERE responsibility_id = t.column_value
AND user_id = p_user_id;
select usr.user_id
from jtf_rs_resource_extns extn, fnd_user usr
where extn.user_id = usr.user_id
and usr.user_name = p_user_name;
SELECT partner_id, program_id
FROM pv_pg_memberships
WHERE program_id in (
SELECT program_id
FROM pv_partner_program_b
WHERE program_level_code = 'MEMBERSHIP'
START WITH program_id = p_program_id
CONNECT BY PRIOR program_id = program_parent_id
)
AND membership_status_code = 'ACTIVE';
* manage_store_resp_on_delete
* This public API will take care of deleting store responsibility of partner that
* have active membership in the program. This should be called when a store
* responsibility is deleted.
*/
PROCEDURE manage_store_resp_on_delete(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_resp_map_rule_id IN NUMBER
)
IS
CURSOR c_get_partner_id (cv_program_id NUMBER) IS
SELECT partner_id
FROM pv_pg_memberships
WHERE program_id = cv_program_id
AND membership_status_code = 'ACTIVE';
SELECT ptnr_resp_id, program_id, responsibility_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE source_resp_map_rule_id = p_resp_map_rule_id
AND resp_type_code = G_STORE;
SELECT count(*)
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = G_ALL
AND responsibility_id = cv_responsibility_id
AND resp_type_code = G_STORE;
SELECT responsibility_id
FROM pv_ge_ptnr_resps
WHERE partner_id in (
SELECT partner_id
FROM pv_partner_profiles
WHERE partner_party_id in (
SELECT partner_party_id
FROM pv_partner_profiles
WHERE partner_id = cv_partner_id
)
AND partner_id <> cv_partner_id
)
AND user_role_code = G_ALL
AND resp_type_code = G_STORE;
l_api_name CONSTANT VARCHAR2(30) := 'manage_store_resp_on_delete';
SAVEPOINT manage_store_resp_on_delete;
Debug_Log('manage_store_resp_on_delete: x.ptnr_resp_id = ' || x.ptnr_resp_id);
Debug_Log('manage_store_resp_on_delete: x.program_id = ' || x.program_id);
Debug_Log('manage_store_resp_on_delete: x.responsibility_id = ' || x.responsibility_id);
Debug_Log('manage_store_resp_on_delete: x.object_version_number = ' || x.object_version_number);
Debug_Log('manage_store_resp_on_delete: l_count = ' || l_count);
Debug_Log('manage_store_resp_on_delete: l_count is 1');
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_store_resp_on_delete: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
Debug_Log('manage_store_resp_on_delete: x.ptnr_resp_id = ' || x.ptnr_resp_id);
Debug_Log('manage_store_resp_on_delete: x.object_version_number = ' || x.object_version_number);
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
ROLLBACK TO manage_store_resp_on_delete;
ROLLBACK TO manage_store_resp_on_delete;
ROLLBACK TO manage_store_resp_on_delete;
END manage_store_resp_on_delete;
SELECT jtfre.user_id user_id
FROM hz_relationships hzr, jtf_rs_resource_extns jtfre, pv_partner_profiles pvpp, fnd_user fndu
WHERE pvpp.partner_id = p_partner_id
AND pvpp.status = 'A'
AND pvpp.partner_party_id = hzr.object_id
AND hzr.directional_flag = 'F'
AND hzr.relationship_code = 'EMPLOYEE_OF'
AND HZR.subject_table_name ='HZ_PARTIES'
AND HZR.object_table_name ='HZ_PARTIES'
AND hzr.start_date <= SYSDATE
AND (hzr.end_date is null or hzr.end_date > sysdate)
AND HZR.status = 'A'
AND hzr.party_id = jtfre.source_id
AND jtfre.category = 'PARTY'
AND fndu.user_id = jtfre.user_id
AND fndu.start_date <= sysdate
AND (fndu.end_date is null or fndu.end_date > sysdate)
AND exists (
SELECT jtfp1.principal_name username
FROM jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1,
jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2,
jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm
WHERE jtfp1.is_user_flag=1
AND jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
AND jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
AND jtfp2.is_user_flag=0
AND jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
AND jtfrp.positive_flag = 1
AND jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
AND jtfperm.permission_name in ('PV_PARTNER_USER', 'IBE_INT_PRIMARY_USER')
AND jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
AND jtfd.domain_name='CRM_DOMAIN'
AND jtfp1.principal_name = jtfre.user_name
GROUP BY jtfp1.principal_name
HAVING count( distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', null, jtfperm.permission_name ) ) = 1
AND count( distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', jtfperm.permission_name, null ) ) = 1
);
SELECT jtfre.user_id user_id
FROM hz_relationships hzr,
jtf_rs_resource_extns jtfre,
pv_partner_profiles pvpp, fnd_user fndu
WHERE pvpp.partner_id = p_partner_id
AND pvpp.status = 'A'
AND pvpp.partner_party_id = hzr.object_id
AND hzr.directional_flag = 'F'
AND hzr.relationship_code = 'EMPLOYEE_OF'
AND HZR.subject_table_name ='HZ_PARTIES'
AND HZR.object_table_name ='HZ_PARTIES'
AND hzr.start_date <= SYSDATE
AND (hzr.end_date is null or hzr.end_date > sysdate)
AND HZR.status = 'A'
AND hzr.party_id = jtfre.source_id
AND jtfre.category = 'PARTY'
AND fndu.user_id = jtfre.user_id
AND fndu.start_date <= sysdate
AND (fndu.end_date is null or fndu.end_date > sysdate)
AND exists (
SELECT jtfp1.principal_name username
FROM jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1,
jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2,
jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm
WHERE jtfp1.is_user_flag=1
AND jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
AND jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
AND jtfp2.is_user_flag=0
AND jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
AND jtfrp.positive_flag = 1
AND jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
AND jtfperm.permission_name in ('PV_PARTNER_USER', 'IBE_INT_PRIMARY_USER')
AND jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
AND jtfd.domain_name='CRM_DOMAIN'
AND jtfp1.principal_name = jtfre.user_name
GROUP BY jtfp1.principal_name
HAVING count( distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', null, jtfperm.permission_name ) ) = 1
AND count( distinct decode(jtfperm.permission_name, 'IBE_INT_PRIMARY_USER', jtfperm.permission_name, null ) ) = 0
);
SELECT partner_id
FROM pv_partner_profiles
WHERE partner_party_id = p_org_party_id;
SELECT ptnr_resp_id, responsibility_id, partner_id, program_id,
user_role_code, object_version_number
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND resp_type_code = G_PROGRAM
ORDER BY user_role_code;
SELECT 1
FROM pv_ge_ptnr_resps
WHERE partner_id = cv_partner_id
AND user_role_code = cv_user_role_code
AND resp_type_code = G_PROGRAM;
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('manage_resp_on_address_change: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => y.ptnr_resp_id
,p_object_version_number => y.object_version_number
);
SELECT ptnr_resp_id, user_role_code, responsibility_id, object_version_number
FROM pv_ge_ptnr_resps
WHERE partner_id = p_partner_id
AND program_id is null
AND resp_type_code = G_PROGRAM;
* API to delete the row with ptnr_resp_id = x.ptnr_resp_id from pv_partner_memberships
****/
IF (PV_DEBUG_HIGH_ON) THEN
Debug_Log('revoke_default_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
WRITE_LOG(l_api_name, 'revoke_default_resp: before calling PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps');
PV_Ge_Ptnr_Resps_PVT.Delete_Ge_Ptnr_Resps(
p_api_version_number => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ptnr_resp_id => x.ptnr_resp_id
,p_object_version_number => x.object_version_number
);
SELECT responsibility_id
FROM pv_ge_ptnr_resps
WHERE partner_id = p_partner_id
AND user_role_code = p_user_role_code
AND resp_type_code = G_PROGRAM;
SELECT user_id, responsibility_id, responsibility_application_id, security_group_id, start_date, description
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_id = p_def_resp_id;
Debug_Log('exec_cre_upd_del_resp_mapping: execute delete_resp_mapping');
delete_resp_mapping(
p_api_version_number => l_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_source_resp_map_rule_id => p_source_resp_map_rule_id
);
Debug_Log('exec_cre_upd_del_resp_mapping: execute update_resp_mapping');
update_resp_mapping(
p_api_version_number => l_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_source_resp_map_rule_id => p_source_resp_map_rule_id
);
SELECT partner_party_id
FROM pv_partner_profiles prof
, hz_party_sites st
, hz_locations loc
WHERE prof.partner_party_id = st.party_id
AND prof.status = 'A'
AND st.location_id=loc.location_id
AND st.identifying_address_flag = 'Y'
AND st.status='A'
AND st.location_id= p_location_id;
SELECT partner_id
FROM hz_party_sites hzps,
pv_partner_profiles ppp
WHERE hzps.party_site_id = cv_party_site_id
AND hzps.status = 'A'
AND hzps.identifying_address_flag = 'Y'
AND ppp.partner_party_id = hzps.party_id
AND ppp.status = 'A';
IF ( l_key like 'oracle.apps.ar.hz.Location.update%' ) THEN
FOR x in get_party_id_csr ( l_location_id ) LOOP
IF (PV_DEBUG_HIGH_ON) THEN
WRITE_LOG(l_api_name, 'partner party_id = ' || to_char(x.partner_party_id) );
IF ( l_key like 'oracle.apps.ar.hz.PartySite.update%' ) THEN
IF (PV_DEBUG_HIGH_ON) THEN
WRITE_LOG(l_api_name, 'oracle.apps.ar.hz.PartySite.update event fired');
WRITE_LOG(l_api_name, 'in party site update evnt before manage update address api call');
WRITE_LOG(l_api_name, 'party site update subscription x_return_status = ' || x_return_status || 'x_msg_data is ' || x_msg_data);
END IF; -- end of if , if the event is PartySite.update
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 1
FROM pv_partner_business_users_v
WHERE partner_id = p_from_partner_id);
SELECT 1
FROM pv_ge_ptnr_resps
WHERE partner_id = p_to_partner_id
and user_role_code = 'BUSINESS';
SELECT program_id, partner_id
FROM pv_pg_memberships
WHERE partner_id = p_to_partner_id
AND membership_status_code = 'ACTIVE';
SELECT responsibility_id, user_role_code
FROM pv_ge_ptnr_resps
WHERE partner_id = p_to_partner_id;