[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pvpp.partner_id, pvpp.PARTNER_GROUP_ID , person_hzp.PERSON_FIRST_NAME, person_hzp.person_last_name, hzoc.org_contact_id, org_hzp.party_name
from HZ_PARTIES PERSON_HZP, HZ_RELATIONSHIPS HZR, PV_PARTNER_PROFILES pvpp, hz_org_contacts hzoc, HZ_PARTIES ORG_HZP
where HZR.party_id = cv_person_rel_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.status = 'A'
and hzr.subject_id = person_hzp.party_id
and person_hzp.status = 'A'
and hzr.object_id = pvpp.partner_party_id
and pvpp.status = 'A'
and pvpp.partner_group_id is not null
and hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id
and hzr.object_id = org_hzp.party_id;
select resource_id, resource_number, user_id, object_version_number
from jtf_rs_resource_extns
where source_id = cv_person_rel_party_id
and category='PARTY'
and start_date_active <= sysdate
and (end_date_active is null or end_date_active >= sysdate);
select role_relate_id
from jtf_rs_role_relations rr, jtf_rs_roles_b rrb
where role_resource_id = cv_resource_id
and role_resource_type = 'RS_INDIVIDUAL'
and rr.start_date_active <= sysdate
and (rr.end_date_active is null or rr.end_date_active >=sysdate)
and rrb.role_id = rr.role_id
and rrb.role_code= 'PARTNER_CONTACT_MEMBER'
and (rr.delete_flag is null or rr.delete_flag='N')
and (rr.active_flag is null or rr.active_flag = 'Y')
and (rrb.active_flag = 'Y');
select group_member_id
from jtf_rs_group_members
where group_id = cv_partner_group_id
and resource_id = cv_resource_id
and (delete_flag is null or delete_flag = 'N');
select role_relate_id
from jtf_rs_role_relations rr, jtf_rs_roles_b rrb
where role_resource_id = cv_group_member_id
and role_resource_type = 'RS_GROUP_MEMBER'
and rr.start_date_active <= sysdate
and (rr.end_date_active is null or rr.end_date_active >=sysdate)
and rrb.role_id = rr.role_id
and rrb.role_code= 'PARTNER_CONTACT_MEMBER'
and (rr.delete_flag is null or rr.delete_flag='N')
and (rr.active_flag is null or rr.active_flag = 'Y')
and (rrb.active_flag = 'Y');
jtf_rs_resource_pub.update_resource (
P_API_VERSION => p_api_version_number,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_RESOURCE_NUMBER => l_resource_number,
P_USER_ID => p_partner_user_rec.user_ID,
P_SOURCE_NAME => FND_API.G_MISS_CHAR,
P_OBJECT_VERSION_NUM => l_object_version_number,
P_USER_NAME => p_partner_user_rec.user_name,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
select jtfperm.permission_name
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0
and jtfp1.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 (G_PARTNER_PERMISSION, G_PRIMARY_PERMISSION)
and jtfp1.principal_name IN
(select principal_name
from jtf_um_usertype_role jtur
where jtur.usertype_id = l_user_type_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union all
select jtsr.principal_name
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
where jtus.usertype_id = l_user_type_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
--and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_flag in ('IMPLICIT', 'DELEGATION_SELFSERVICE')
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate)
)
group by jtfperm.permission_name;
select jtfperm.permission_name
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0
and jtfp1.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 (G_PARTNER_PERMISSION, G_PRIMARY_PERMISSION)
and jtfp1.principal_name IN
(select principal_name
from jtf_um_usertype_role jtur
where jtur.usertype_id = l_user_type_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union all
select jtsr.principal_name
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
where jtus.usertype_id = l_user_type_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
--and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_flag in ('IMPLICIT', 'DELEGATION_SELFSERVICE')
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate)
)
group by jtfperm.permission_name;
select jtfperm.permission_name , jtfp1.jtf_auth_principal_id
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0
and jtfp1.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 (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfp1.principal_name IN (Select * from table (CAST(l_role_name AS JTF_VARCHAR2_TABLE_1000)));
Select jtfperm.permission_name
FROM jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd, jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm
where jtfp1.principal_name = l_user_name
and jtfp1.is_user_flag=1
and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
and jtfrp.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
and jtfrp.positive_flag = 1
and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name in ( G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
and jtfd.domain_name='CRM_DOMAIN'
and jtfpm.jtf_auth_parent_principal_id NOT IN (Select * from table (CAST(l_role_id AS JTF_NUMBER_TABLE)))
group by jtfperm.permission_name;
Select fndu.user_id
from fnd_user fndu
where fndu.user_name = l_user_name;
PROCEDURE delete_role
(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_role_name IN JTF_VARCHAR2_TABLE_1000
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_role';
select jtfperm.permission_name , jtfp1.jtf_auth_principal_id
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0
and jtfp1.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 (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfp1.principal_name IN (Select * from table (CAST(cv_role_name AS JTF_VARCHAR2_TABLE_1000)));
select /*+ cardinality( t 10 ) */ jtfp2.principal_name, fndu.user_id
from jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp2,jtf_auth_principals_b jtfp1,
fnd_user fndu, jtf_rs_resource_extns jtfre, (Select column_value from table
(CAST(cv_role_id AS JTF_NUMBER_TABLE))) t
where jtfp1.jtf_auth_principal_id = t.column_value
and jtfp1.is_user_flag = 0
and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
and jtfpm.jtf_auth_principal_id = jtfp2.jtf_auth_principal_id
and jtfp2.is_user_flag=1
and jtfp2.principal_name = fndu.user_name
and fndu.user_id = jtfre.user_id
and jtfre.category = 'PARTY';
select /*+ cardinality( t 10 ) */ jtfperm.permission_name, jtfp3.principal_name
from jtf_auth_principals_b jtfp3, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm, jtf_auth_principal_maps jtfpm2, (Select column_value from table (CAST(cv_user_name AS JTF_VARCHAR2_TABLE_1000))) t
where jtfp3.principal_name = t.column_value
and jtfp3.is_user_flag = 1 and jtfp3.jtf_auth_principal_id=jtfpm2.jtf_auth_principal_id
and jtfpm2.jtf_auth_parent_principal_id = jtfrp.JTF_AUTH_PRINCIPAL_ID
and jtfpm2.jtf_auth_parent_principal_id NOT IN (Select * from table (CAST(cv_role_id AS JTF_NUMBER_TABLE)))
and jtfrp.JTF_AUTH_PERMISSION_ID = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name IN (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfrp.positive_flag = 1
group by jtfperm.permission_name, jtfp3.principal_name
order by jtfp3.principal_name;
SAVEPOINT delete_role;
ROLLBACK TO delete_role;
ROLLBACK TO delete_role;
ROLLBACK TO delete_role;
END delete_role;
select jtfperm.permission_name
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0 and jtfp1.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 ( G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfp1.principal_name in (Select * from table (CAST(l_role_name AS JTF_VARCHAR2_TABLE_1000)))
group by jtfperm.permission_name;
select fndu.user_id
from fnd_user fndu
where fndu.user_name = l_user_name;
select jtfperm.permission_name
from jtf_auth_principals_b jtfp1, jtf_auth_principal_maps jtfpm,
jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.principal_name = l_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 = 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 (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
group by jtfperm.permission_name;
select 'X' from dual
where exists
(select 'X' from jtf_rs_resource_extns
where user_id = l_user_id and category='PARTY');
PROCEDURE update_role
(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_assigned_perms IN JTF_VARCHAR2_TABLE_1000
,p_unassigned_perms IN JTF_VARCHAR2_TABLE_1000
,p_role_name IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_role';
select /*+ cardinality( t 10 ) */ jtfperm.permission_name, jtfp3.principal_name
from jtf_auth_principals_b jtfp3, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm, jtf_auth_principal_maps jtfpm2, (Select column_value from table (CAST(cv_user_name AS JTF_VARCHAR2_TABLE_1000))) t
where jtfp3.principal_name = t.column_value
and jtfp3.is_user_flag = 1
and jtfp3.jtf_auth_principal_id=jtfpm2.jtf_auth_principal_id
and jtfpm2.jtf_auth_parent_principal_id = jtfrp.JTF_AUTH_PRINCIPAL_ID
and jtfrp.JTF_AUTH_PERMISSION_ID = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name IN (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfrp.positive_flag = 1
and jtfpm2.jtf_auth_parent_principal_id <> cv_role_id
group by jtfperm.permission_name, jtfp3.principal_name
order by jtfp3.principal_name;
select jtfp2.principal_name, fndu.user_id
from jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp2,jtf_auth_principals_b jtfp1,
fnd_user fndu, jtf_rs_resource_extns jtfre
where jtfp1.jtf_auth_principal_id = cv_role_id
and jtfp1.is_user_flag = 0
and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
and jtfpm.jtf_auth_principal_id = jtfp2.jtf_auth_principal_id
and jtfp2.is_user_flag=1
and jtfp2.principal_name = fndu.user_name
and fndu.user_id = jtfre.user_id
and jtfre.category = 'PARTY';
select jtfperm.permission_name, jtfp1.jtf_auth_principal_id
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0 and jtfp1.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 ( G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
and jtfp1.principal_name = l_role_name;
SAVEPOINT update_role;
ROLLBACK TO update_role;
ROLLBACK TO update_role;
ROLLBACK TO update_role;
END update_role;
select 1 from dual where exists
(
select /*+ use_nl (hzr res) */ pvpp.partner_id, hzr.party_id, hzr.subject_id ,pvpp.partner_party_id
from jtf_rs_resource_extns RES, hz_relationships hzr, pv_partner_profiles pvpp
where RES.category = 'PARTY' and RES.source_id = 6310
and RES.start_date_active <= SYSDATE
and (RES.end_date_active is null or RES.end_date_active > 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' and exists
(
select 1 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.principal_name = RES.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 = G_PARTNER_PERMISSION
and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
and jtfd.domain_name='CRM_DOMAIN'
)
);
select usr.user_id, usr.user_name, usr.customer_id, jureg.usertype_id
from fnd_user usr, jtf_um_usertype_reg jureg
where jureg.usertype_reg_id=c_reg_id
and usr.user_id = jureg.user_id;
select 1 from dual
where exists
( select jtfperm.permission_name
from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
where jtfp1.is_user_flag = 0
and jtfp1.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 = 'PV_PARTNER_USER'
and jtfp1.principal_name IN
(select principal_name
from jtf_um_usertype_role jtur
where jtur.usertype_id = c_usertype_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union all
select jtsr.principal_name
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
where jtus.usertype_id = c_usertype_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
--and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_flag in ('IMPLICIT', 'DELEGATION_SELFSERVICE')
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate)
)
);
PROCEDURE update_elig_prgm_4_new_ptnr(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_partner_id IN NUMBER
,p_member_type IN VARCHAR2 := null
)
IS
CURSOR c_get_program_info IS
SELECT prg.program_id, prereq_process_rule_id
FROM pv_partner_program_b prg
WHERE prg.program_status_code = 'ACTIVE'
AND prg.program_level_code = 'MEMBERSHIP'
AND NVL(prg.allow_enrl_until_date, SYSDATE +1) >= SYSDATE
AND prg.enabled_flag = 'Y';
SELECT change_from_program_id
FROM pv_pg_enrl_change_rules rule
WHERE rule.change_to_program_id = c_program_id
AND rule.change_direction_code = 'PREREQUISITE'
AND rule.EFFECTIVE_FROM_DATE <= SYSDATE
AND NVL(rule.EFFECTIVE_TO_DATE, SYSDATE+1) >= SYSDATE
AND rule.ACTIVE_FLAG = 'Y';
SELECT 1
FROM dual
WHERE not exists(
SELECT 1
FROM pv_pg_memberships memb
WHERE memb.program_id = c_program_id
AND memb.partner_id = c_partner_id
AND memb.MEMBERSHIP_STATUS_CODE = 'ACTIVE'
AND memb.START_DATE <= SYSDATE
AND NVL(memb.ACTUAL_END_DATE,NVL(memb.ORIGINAL_END_DATE,SYSDATE+1)) >= SYSDATE
);
SELECT PV_PG_ELIG_PROGRAMS_S.NEXTVAL
FROM dual;
l_api_name CONSTANT VARCHAR2(30) := 'update_elig_prgm_4_new_ptnr';
l_stmt_str := 'INSERT
INTO pv_pg_elig_programs
(
ELIG_PROGRAM_ID,
PROGRAM_ID,
PARTNER_ID,
ELIGIBILITY_CRIT_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number
)
VALUES
(
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10
)';
l_stmt_str := 'INSERT
INTO pv_pg_elig_programs
(
ELIG_PROGRAM_ID,
PROGRAM_ID,
PARTNER_ID,
ELIGIBILITY_CRIT_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number
)
VALUES
(
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10
)';
END update_elig_prgm_4_new_ptnr;