[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(partner.Enabled_Flag,'N')
FROM JTF_QUAL_USGS_ALL partner
WHERE partner.org_id= 204
AND partner.Seeded_Qual_Id = cv_prtnr_qualifier
AND partner.QUAL_TYPE_USG_ID=-1701 ;
FUNCTION chk_partner_qflr_updated(p_upd_prtnr_qflr_flg_rec IN prtnr_qflr_flg_rec_type )
RETURN VARCHAR2
IS
l_prtnr_qflr_enabled VARCHAR2(1) ;
END chk_partner_qflr_updated;
SELECT resource_id, person_id, resource_category, group_id
BULK COLLECT INTO
x_resource_rec.resource_id,
x_resource_rec.person_id,
x_resource_rec.resource_category,
x_resource_rec.group_id
FROM
( SELECT distinct m.resource_id resource_id,
m.person_id person_id,
res.category resource_category,
m.group_id group_id
FROM jtf_rs_groups_b g,
jtf_rs_group_usages u,
jtf_rs_group_members m,
jtf_rs_role_relations rr,
jtf_rs_roles_b r,
jtf_rs_resource_extns res
WHERE g.group_id = l_resource_group_id
AND sysdate between nvl(g.start_date_active,sysdate) and
nvl(g.end_date_active,sysdate)
AND u.group_id = g.group_id
AND u.usage = 'PRM'
AND m.group_id = g.group_id
AND m.group_id = u.group_id
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND NVL(rr.delete_flag,'N') <> 'Y'
AND sysdate between rr.start_date_active and
nvl(rr.end_date_active,sysdate)
AND rr.role_id = r.role_id
AND r.role_code in ('CHANNEL_MANAGER', 'CHANNEL_REP')
AND r.role_type_code = 'PRM'
AND r.active_flag = 'Y'
AND r.member_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE', 'PARTY')
AND sysdate between nvl(res.start_date_active,sysdate) and
nvl(res.end_date_active,sysdate)
AND res.user_id IS NOT NULL ) j
WHERE j.group_id = l_resource_group_id;
SELECT resource_id, person_id, resource_category, group_id
BULK COLLECT INTO
x_resource_rec.resource_id,
x_resource_rec.person_id,
x_resource_rec.resource_category,
x_resource_rec.group_id
FROM (
SELECT min(tm.team_resource_id) resource_id,
min(tm.person_id) person_id,
min(g.group_id) group_id,
min(t.team_id) team_id,
tres.category resource_category
FROM jtf_rs_team_members tm,
jtf_rs_teams_b t,
jtf_rs_team_usages tu,
jtf_rs_role_relations trr,
jtf_rs_roles_b tr,
jtf_rs_resource_extns tres,
(
SELECT m.group_id group_id,
m.resource_id resource_id
FROM jtf_rs_group_members m,
jtf_rs_groups_b g,
jtf_rs_group_usages u,
jtf_rs_role_relations rr,
jtf_rs_roles_b r,
jtf_rs_resource_extns res
WHERE m.group_id = g.group_id
AND sysdate BETWEEN nvl(g.start_date_active,sysdate)
AND nvl(g.end_date_active,sysdate)
AND u.group_id = g.group_id
AND u.usage = 'PRM'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND sysdate between rr.start_date_active
AND nvl(rr.end_date_active,sysdate)
AND rr.role_id = r.role_id
AND r.role_type_code in ('PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND sysdate between nvl(res.start_date_active,sysdate) and
nvl(res.end_date_active,sysdate)
AND res.category IN ('EMPLOYEE','PARTY') ) g /* Added PARTY category also */
WHERE tm.team_id = t.team_id
AND sysdate between nvl(t.start_date_active,sysdate)
AND nvl(t.end_date_active,sysdate)
AND tu.team_id = t.team_id
AND tu.usage = 'PRM'
AND tm.team_member_id = trr.role_resource_id
AND tm.delete_flag <> 'Y'
AND tm.resource_type = 'INDIVIDUAL'
AND trr.role_resource_type = 'RS_TEAM_MEMBER'
AND trr.delete_flag <> 'Y'
AND sysdate between trr.start_date_active
AND nvl(trr.end_date_active,sysdate)
AND trr.role_id = tr.role_id
AND tr.role_type_code in ('PRM')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND sysdate between nvl(tres.start_date_active,sysdate) and
nvl(tres.end_date_active,sysdate)
AND tres.category IN ('EMPLOYEE','PARTY')
AND tm.team_resource_id = g.resource_id
GROUP BY tm.team_member_id, tm.team_resource_id, tm.person_id, tres.category
UNION
SELECT min(m.resource_id) resource_id,
min(m.person_id) person_id,
min(m.group_id) group_id,
min(jtm.team_id) team_id,
res.category resource_category
FROM jtf_rs_group_members m,
jtf_rs_groups_b g,
jtf_rs_group_usages u,
jtf_rs_role_relations rr,
jtf_rs_roles_b r,
jtf_rs_resource_extns res,
(
SELECT tm.team_resource_id group_id, t.team_id team_id
FROM jtf_rs_team_members tm,
jtf_rs_teams_b t,
jtf_rs_team_usages tu,
jtf_rs_role_relations trr,
jtf_rs_roles_b tr,
jtf_rs_resource_extns tres
WHERE tm.team_id = t.team_id
AND sysdate between nvl(t.start_date_active,sysdate)
and nvl(t.end_date_active,sysdate)
AND tu.team_id = t.team_id
AND tu.usage = 'PRM'
AND tm.team_member_id = trr.role_resource_id
AND tm.delete_flag <> 'Y'
AND tm.resource_type = 'GROUP'
AND trr.role_resource_type = 'RS_TEAM_MEMBER'
AND trr.delete_flag <> 'Y'
AND sysdate between trr.start_date_active and
nvl(trr.end_date_active,sysdate)
AND trr.role_id = tr.role_id
AND tr.role_type_code in ('PRM')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND sysdate between nvl(tres.start_date_active,sysdate) and
nvl(tres.end_date_active,sysdate)
AND tres.category IN ('EMPLOYEE','PARTY') ) jtm
WHERE m.group_id = g.group_id
AND sysdate between nvl(g.start_date_active,sysdate) and
nvl(g.end_date_active,sysdate)
AND u.group_id = g.group_id
AND u.usage = 'PRM'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND sysdate between rr.start_date_active and
nvl(rr.end_date_active,sysdate)
AND rr.role_id = r.role_id
AND r.role_type_code in ( 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category IN ('EMPLOYEE','PARTY')
AND sysdate between nvl(res.start_date_active,sysdate) and
nvl(res.end_date_active,sysdate)
AND jtm.group_id = g.group_id
group by m.resource_id, m.person_id, m.group_id, res.category ) j
where
j.team_id = l_resource_team_id
and j.resource_category IN ('EMPLOYEE', 'PARTY');
SELECT 'Y'
FROM PV_PARTNER_ACCESSES
WHERE partner_id = cv_partner_id
AND resource_id = cv_resource_id ;
SELECT 'Y'
FROM PV_PARTNER_ACCESSES
WHERE partner_id = cv_partner_id;
SELECT 'Y'
FROM PV_TAP_ACCESS_TERRS
WHERE terr_id = cv_terr_id
AND partner_access_id = cv_partner_access_id ;
SELECT 'Y'
FROM jtf_rs_resource_extns RES,
jtf_rs_group_members GRPMEM,
jtf_rs_group_usages GRPUSG,
jtf_rs_role_relations ROLRELAT ,
jtf_rs_roles_vl ROLE,
jtf_rs_groups_b b
WHERE RES.resource_id = cv_resource_id
AND RES.category = 'EMPLOYEE'
AND sysdate between nvl(RES.start_date_active,sysdate) and
nvl(RES.end_date_active,sysdate)
AND RES.resource_id = GRPMEM.resource_id
AND nvl(GRPMEM.delete_flag, 'N') = 'N'
AND GRPMEM.group_id = GRPUSG.group_id
AND GRPMEM.group_id = b.group_id
AND b.group_id = GRPUSG.group_id
AND GRPUSG.usage IN ('PRM')
AND GRPMEM.group_member_id=ROLRELAT.role_resource_id
AND ROLRELAT.role_resource_type = 'RS_GROUP_MEMBER'
AND NVL(ROLRELAT.delete_flag,'N') = 'N'
AND ROLRELAT.start_date_active <= sysdate
AND NVL(ROLRELAT.end_date_active,sysdate) >= sysdate
AND ROLRELAT.role_id = ROLE.ROLE_ID
AND ROLE.role_code in( 'CHANNEL_MANAGER' ,'CHANNEL_REP')
AND ROLE.role_type_code in ('PRM')
AND ROLE.MEMBER_FLAG = 'Y' ;
SELECT 'Y'
FROM pv_enty_attr_values ATTR
WHERE ATTR.entity_id = cv_partner_id
AND ATTR.entity= 'PARTNER'
AND ATTR.attribute_id = 3
AND ATTR.attr_value = 'VAD'
AND ATTR.latest_flag = 'Y'
AND ATTR.enabled_flag = 'Y' ;
SELECT 'Y'
FROM
pv_partner_profiles PROFILE,
hz_relationships HZPR_PART_CONT ,
hz_parties CONTACT ,
jtf_rs_resource_extns RES
WHERE
PROFILE.partner_id = cv_partner_id
AND PROFILE.partner_party_id = HZPR_PART_CONT.object_id
AND HZPR_PART_CONT.RELATIONSHIP_TYPE = 'EMPLOYMENT'
AND HZPR_PART_CONT.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZPR_PART_CONT.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZPR_PART_CONT.status = 'A'
AND HZPR_PART_CONT.start_date <= SYSDATE
AND NVL(HZPR_PART_CONT.end_date,SYSDATE) >= SYSDATE
AND HZPR_PART_CONT.subject_id = CONTACT.PARTY_ID
AND HZPR_PART_CONT.subject_type = 'PERSON'
AND HZPR_PART_CONT.party_id = RES.source_id
AND RES.category = 'PARTY'
AND RES.resource_id = cv_resource_id -- 100000944 100069925
AND sysdate between nvl(RES.start_date_active,sysdate) and
nvl(RES.end_date_active,sysdate)
AND RES.resource_id IN (
SELECT GRPMEM.resource_id
FROM
jtf_rs_group_members GRPMEM,
jtf_rs_groups_b GROUPB,
jtf_rs_group_usages GRPUSG,
jtf_rs_role_relations ROLRELAT ,
jtf_rs_roles_vl ROLE
WHERE GRPMEM.resource_id = RES.resource_id
AND nvl(GRPMEM.delete_flag, 'N') = 'N'
AND GRPMEM.group_id = GROUPB.group_id
AND GROUPB.group_id = GRPUSG.group_id
AND GRPUSG.usage IN ('PRM')
AND GRPMEM.group_member_id=ROLRELAT.role_resource_id
AND ROLRELAT.role_resource_type = 'RS_GROUP_MEMBER'
AND NVL(ROLRELAT.delete_flag,'N') = 'N'
AND ROLRELAT.start_date_active <= sysdate
AND NVL(ROLRELAT.end_date_active,sysdate) >= sysdate
AND ROLRELAT.role_id = ROLE.ROLE_ID
AND ROLE.role_code in( 'CHANNEL_MANAGER' ,'CHANNEL_REP')
AND ROLE.role_type_code in ('PRM')
AND ROLE.MEMBER_FLAG = 'Y') ;
SELECT partner_access_id,
created_by_tap_flag
FROM PV_PARTNER_ACCESSES
WHERE partner_id = cv_partner_id
AND resource_id = cv_resource_id;
SELECT source_org_id
FROM jtf_rs_resource_extns RES
WHERE resource_id = cv_resource_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE user_id = cv_user_id
AND sysdate between nvl(start_date_active,sysdate) and
nvl(end_date_active,sysdate);
SELECT 'Y'
FROM PV_PARTNER_ACCESSES
WHERE partner_id = cv_partner_id
AND resource_id = cv_resource_id;
SELECT pref_functional_currency
FROM HZ_ORGANIZATION_PROFILES
WHERE effective_end_date is NULL
AND party_id = cv_party_id;
SELECT category
FROM jtf_rs_resource_extns res,
jtf_rs_role_relations rr,
jtf_rs_roles_b r
WHERE res.resource_id = cv_resource_id
AND res.resource_id = rr.role_resource_id
AND rr.role_resource_type ='RS_INDIVIDUAL'
AND NVL(rr.delete_flag,'N') <> 'Y'
AND sysdate between rr.start_date_active and
nvl(rr.end_date_active,sysdate)
AND rr.role_id = r.role_id
AND r.role_code in ('CHANNEL_MANAGER', 'CHANNEL_REP')
AND r.role_type_code = 'PRM'
AND r.active_flag = 'Y'
AND r.member_flag = 'Y';
SELECT PARTY.party_id party_id,
SITE.party_site_id party_site_id,
PARTY.city city,
PARTY.country country,
PARTY.county county,
PARTY.state state,
PARTY.province province,
PARTY.postal_code postal_code,
PARTY.primary_phone_area_code phone_area_code,
PARTY.employees_total employees_total,
upper(PARTY.party_name) party_name,
PARTY.category_code category_code,
PARTY.curr_fy_potential_revenue annual_revenue
FROM HZ_PARTY_SITES SITE,
HZ_PARTIES PARTY
WHERE SITE.status = 'A'
AND SITE.identifying_address_flag = 'Y'
AND PARTY.party_id = cv_party_id
AND SITE.party_id = PARTY.party_id
AND PARTY.party_type = 'ORGANIZATION'
AND PARTY.status = 'A';
SELECT PARTY.party_id party_id,
SITE.party_site_id party_site_id,
LOC.city city,
LOC.country country,
LOC.county county,
LOC.state state,
LOC.province province,
LOC.postal_code postal_code,
CNTPNT.phone_area_code phone_area_code,
PARTY.employees_total employees_total,
upper(PARTY.party_name) party_name,
PARTY.category_code category_code,
PARTY.curr_fy_potential_revenue annual_revenue
FROM HZ_PARTY_SITES SITE,
HZ_CONTACT_POINTS CNTPNT,
HZ_LOCATIONS LOC,
HZ_PARTIES PARTY
WHERE SITE.status = 'A'
AND SITE.identifying_address_flag = 'Y'
AND PARTY.party_id = cv_party_id
AND SITE.party_id = PARTY.party_id
AND PARTY.party_type = 'ORGANIZATION'
AND PARTY.status = 'A'
AND CNTPNT.owner_table_name(+) = 'HZ_PARTY_SITES'
AND CNTPNT.owner_table_id(+) = SITE.party_site_id
AND CNTPNT.status(+) = 'A'
AND CNTPNT.primary_flag(+) = 'Y'
AND CNTPNT.contact_point_type(+) = 'PHONE'
AND LOC.location_id = SITE.location_id
UNION ALL
SELECT to_number(null) party_id,
to_number(NULL) party_site_id ,
to_char(NULL) city ,
to_char(NULL) country,
to_char(NULL) county ,
to_char(NULL) state ,
to_char(NULL) province ,
to_char(NULL) postal_code ,
CP.phone_area_code phone_area_code,
PARTY.employees_total employees_total,
upper(PARTY.party_name) party_name,
PARTY.category_code category_code,
PARTY.curr_fy_potential_revenue annual_revenue
FROM HZ_CONTACT_POINTS CP,
HZ_PARTIES PARTY
WHERE CP.owner_table_name(+) = 'HZ_PARTIES'
AND CP.owner_table_id(+) = PARTY.party_id
AND PARTY.party_id = cv_party_id
AND PARTY.party_type = 'ORGANIZATION'
AND PARTY.status = 'A'
AND CP.status(+) = 'A'
AND CP.primary_flag(+) = 'Y'
AND CP.contact_point_type(+) = 'PHONE';
PROCEDURE Delete_Channel_Team(
p_partner_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Channel_Team';
SELECT partner_access_id, object_version_number
FROM PV_PARTNER_ACCESSES
WHERE partner_id = cv_partner_id
AND KEEP_FLAG = 'N'
AND CREATED_BY_TAP_FLAG = 'Y';
SELECT partner_access_id, terr_id, object_version_number
FROM PV_TAP_ACCESS_TERRS
WHERE partner_access_id = cv_partner_access_id;
/*** First Delete, if any territory record exists in the PV_TAP_ACCESS_TERRS table for
a gievn partner_access_id ***/
FOR l_territory_rec IN l_territory_csr( l_channel_team_rec.partner_access_id)
LOOP
-- Delete the Territory record for the given PARTNER_ACCESS_ID and TERR_ID
-- from PV_TAP_ACCESS_TERRS table.
PV_TAP_ACCESS_TERRS_PVT.Delete_Tap_Access_Terrs(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_partner_access_id => l_territory_rec.partner_access_id,
p_terr_id => l_territory_rec.terr_id,
p_object_version_number => l_territory_rec.object_version_number );
-- Delete the Partner Access record for the given PARTNER_ACCESS_ID
-- from PV_PARTNER_ACCESSES table.
PV_Partner_Accesses_PVT.Delete_Partner_Accesses(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_partner_access_id => l_channel_team_rec.partner_access_id,
p_object_version_number => l_channel_team_rec.object_version_number );
hz_utility_v2pub.debug('Delete_Channel_Team (-)');
hz_utility_v2pub.debug('Delete_Channel_Team (-)');
hz_utility_v2pub.debug('Delete_Channel_Team (-)');
END Delete_Channel_Team;
SELECT partner_party_id, pacv.attr_code
FROM pv_partner_profiles ppp,
PV_ATTRIBUTE_CODES_VL pacv
WHERE ppp.partner_id = cv_partner_id
AND ppp.status = 'A'
AND ppp.partner_level = pacv.ATTR_CODE_ID(+);
SELECT attr_value
FROM pv_enty_attr_values
WHERE attribute_id = 3
AND entity= 'PARTNER'
AND entity_id = cv_partner_id
AND latest_flag = 'Y';
SELECT 'Y'
FROM PV_TAP_ACCESS_TERRS
WHERE partner_access_id = cv_partner_access_id
AND terr_id = cv_terr_id;
SELECT nvl(fnd_profile.value(cv_profile_name),0) from dual;
-- Logic for inserting the logged in user's(Vendor employee) resource_id in the
-- PV_PARTNER_ACCESSES table, IF the user is playing a role of
-- 'CHANNEL_MANAGER' or 'CHANNEL_REP'
IF (p_mode <> 'UPDATE') THEN
IF (p_vad_partner_id IS NULL OR p_vad_partner_id = FND_API.g_miss_num) THEN
IF (p_login_user IS NULL OR p_login_user = FND_API.g_miss_num) THEN
l_login_user_id := FND_GLOBAL.user_id;
END IF; -- p_mode <> 'UPDATE'
SELECT processed_flag, object_version_number
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE partner_id = cv_partner_id;
SELECT fnd_profile.value(cv_profile_name) from dual;
IF ( l_mode = 'UPDATE' ) THEN
-- Call the Delete_Channel_Team for a given Partner_id
Delete_Channel_Team(
p_partner_id => l_partner_id ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
END IF; /*** p_mode = 'UPDATE' ***/
-- then do not insert the record for that partner_id.
OPEN l_chk_partner_exist_csr(l_partner_id);
PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
p_api_version_number => 1.0
,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 => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_batch_chg_prtnrs_rec => l_changed_partner_rec);
FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');
SELECT partner_party_id, pacv.attr_code
FROM pv_partner_profiles ppp,
PV_ATTRIBUTE_CODES_VL pacv
WHERE ppp.partner_id = cv_partner_id
AND ppp.status = 'A'
AND ppp.partner_level = pacv.ATTR_CODE_ID(+);
SELECT partner_party_id, pacv.attr_code
FROM pv_partner_profiles ppp,
PV_ATTRIBUTE_CODES_VL pacv
WHERE ppp.partner_id = cv_partner_id
AND ppp.status = 'A'
AND ppp.partner_level = pacv.ATTR_CODE_ID(+);
SELECT attr_value
FROM pv_enty_attr_values
WHERE attribute_id = 3
AND entity= 'PARTNER'
AND entity_id = cv_partner_id
AND latest_flag = 'Y';
SELECT 'Y'
FROM PV_TAP_ACCESS_TERRS
WHERE partner_access_id = cv_partner_access_id
AND terr_id = cv_terr_id;
IF (p_mode <> 'UPDATE') THEN
IF (p_vad_partner_id IS NULL OR p_vad_partner_id = FND_API.g_miss_num) THEN
IF (p_login_user IS NULL OR p_login_user = FND_API.g_miss_num) THEN
l_login_user_id := FND_GLOBAL.user_id;
END IF; -- p_mode <> 'UPDATE'
SELECT partner_party_id, pacv.attr_code
FROM pv_partner_profiles ppp,
PV_ATTRIBUTE_CODES_VL pacv
WHERE ppp.partner_id = cv_partner_id
AND ppp.status = 'A'
AND ppp.partner_level = pacv.ATTR_CODE_ID(+);
SELECT attr_value
FROM pv_enty_attr_values
WHERE attribute_id = 3
AND entity= 'PARTNER'
AND entity_id = cv_partner_id
AND latest_flag = 'Y';
SELECT 'Y'
FROM PV_TAP_ACCESS_TERRS
WHERE partner_access_id = cv_partner_access_id
AND terr_id = cv_terr_id;
IF (p_mode <> 'UPDATE') THEN
IF (p_vad_partner_id IS NULL OR p_vad_partner_id = FND_API.g_miss_num) THEN
IF (p_login_user IS NULL OR p_login_user = FND_API.g_miss_num) THEN
l_login_user_id := FND_GLOBAL.user_id;
END IF; -- p_mode <> 'UPDATE'
SELECT partner_party_id
FROM pv_partner_profiles a,
pv_enty_attr_values b
WHERE a.partner_id = cv_vad_partner_id
AND a.status = 'A'
AND a.partner_id = b.entity_id
AND b.entity = 'PARTNER'
AND b.attribute_id = 3
AND b.attr_value = 'VAD'
AND b.latest_flag = 'Y';
SELECT DISTINCT
RES.resource_id
FROM
hz_relationships HZPR_PART_CONT ,
hz_parties CONTACT ,
jtf_rs_resource_extns RES ,
jtf_rs_group_members GRPMEM,
jtf_rs_group_usages GRPUSG,
jtf_rs_role_relations ROLRELAT ,
jtf_rs_roles_vl ROLE
WHERE HZPR_PART_CONT.RELATIONSHIP_TYPE = 'EMPLOYMENT'
AND HZPR_PART_CONT.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZPR_PART_CONT.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZPR_PART_CONT.status = 'A'
AND HZPR_PART_CONT.start_date <= SYSDATE
AND NVL(HZPR_PART_CONT.end_date,SYSDATE) >= SYSDATE
AND HZPR_PART_CONT.object_id = cv_vad_party_id
AND HZPR_PART_CONT.subject_id = CONTACT.PARTY_ID
AND CONTACT.party_type = 'PERSON'
AND HZPR_PART_CONT.party_id = RES.source_id
AND RES.category = 'PARTY'
AND sysdate between nvl(RES.start_date_active,sysdate) and
nvl(RES.end_date_active,sysdate)
AND RES.resource_id = GRPMEM.resource_id
AND NVL(GRPMEM.delete_flag,'N') = 'N'
AND GRPMEM.group_id = GRPUSG.group_id
AND GRPUSG.usage = 'PRM'
AND GRPMEM.group_member_id=ROLRELAT.role_resource_id
AND ROLRELAT.role_resource_type = 'RS_GROUP_MEMBER'
AND NVL(ROLRELAT.delete_flag,'N') = 'N'
AND ROLRELAT.start_date_active <= sysdate
AND NVL(ROLRELAT.end_date_active,sysdate) >= sysdate
AND ROLRELAT.role_id = ROLE.ROLE_ID
AND ROLE.role_type_code = 'PRM'
AND ROLE.MEMBER_FLAG = 'Y'
AND ROLE.role_code IN ('CHANNEL_MANAGER', 'CHANNEL_REP')
AND RES.user_id is not null;
PROCEDURE Update_Channel_Team
( 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 ,
p_partner_id IN NUMBER ,
p_vad_partner_id IN NUMBER ,
p_mode IN VARCHAR2 := 'UPDATE',
p_login_user IN NUMBER ,
p_upd_prtnr_qflr_flg_rec IN prtnr_qflr_flg_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
) IS
l_partner_id NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Channel_Team';
SELECT partner_party_id, partner_level
FROM pv_partner_profiles
WHERE partner_id = cv_partner_id
AND status = 'A';
SAVEPOINT Update_Channel_Team_pub;
ROLLBACK TO Update_Channel_Team_pub;
hz_utility_v2pub.debug('Update_Channel_Team (-)');
ROLLBACK TO Update_Channel_Team_pub;
hz_utility_v2pub.debug('Update_Channel_Team (-)');
ROLLBACK TO Update_Channel_Team_pub;
hz_utility_v2pub.debug('Update_Channel_Team (-)');
END Update_Channel_Team;
SELECT partner_party_id, pacv.attr_code
FROM pv_partner_profiles ppp,
PV_ATTRIBUTE_CODES_VL pacv
WHERE ppp.partner_id = cv_partner_id
AND ppp.status = 'A'
AND ppp.partner_level = pacv.ATTR_CODE_ID(+);
Delete_Channel_Team(
p_partner_id => p_partner_id ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_TERR_ASSIGN_PUB.Delete_Channel_Team');
l_program_update_date DATE := sysdate;
SELECT partner_id , object_version_number
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE PARTNER_ID BETWEEN cv_first_partner_id AND cv_last_partner_id;
SELECT partner_id, vad_partner_id, object_version_number,created_by
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE PROCESSED_FLAG = 'P'
AND PARTNER_ID BETWEEN cv_first_partner_id AND cv_last_partner_id;
SELECT hzp.party_name partner_name,ptbcp.partner_id, ptbcp.vad_partner_id, ptbcp.object_version_number,ptbcp.created_by
FROM PV_TAP_BATCH_CHG_PARTNERS ptbcp,
PV_PARTNER_PROFILES ppp,
HZ_PARTIES hzp
WHERE ptbcp.PROCESSED_FLAG = 'P'
-- AND ptbcp.PARTNER_ID BETWEEN cv_first_partner_id AND cv_last_partner_id
AND ptbcp.PARTNER_ID >= cv_first_partner_id AND ptbcp.PARTNER_ID <= cv_last_partner_id
AND ptbcp.partner_id = ppp.partner_id
AND ppp.partner_party_id= hzp.party_id
ORDER BY hzp.party_name ;
SELECT distinct partner_id, NULL "vad_partner_id", NULL "object_version_number" , created_by
FROM PV_PARTNER_PROFILES
WHERE STATUS = 'A'
AND PARTNER_ID BETWEEN cv_first_partner_id AND cv_last_partner_id;
SELECT distinct hzp.party_name partner_name, partner_id,
NULL "vad_partner_id", NULL "object_version_number" , ppp.created_by
FROM PV_PARTNER_PROFILES ppp,
HZ_PARTIES hzp
WHERE ppp.partner_party_id = hzp.party_id
-- AND ppp.PARTNER_ID BETWEEN cv_first_partner_id AND cv_last_partner_id;
PV_BATCH_CHG_PRTNR_PVT.Delete_Batch_Chg_Prtnrs(
p_api_version_number => 1.0 ,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_partner_id => l_batch_partner_id,
p_object_version_number => l_batch_oversion_number);
-- * PROGRAM_UPDATE_DATE
------------------------------------------------------------
l_change_partner.partner_id := l_partner_id ;
l_change_partner.program_update_date := l_program_update_date;
-- * PROGRAM_UPDATE_DATE
------------------------------------------------------------
l_change_partner.partner_id := l_partner_id ;
l_change_partner.program_update_date := l_program_update_date;
l_change_partner.last_update_date := sysdate;
l_change_partner.last_update_by := FND_GLOBAL.user_id;
l_change_partner.last_update_login := FND_GLOBAL.user_id;
PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
p_api_version_number => 1.0 ,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_chg_prtnrs_rec => l_change_partner );
fnd_msg_pub.Delete_Msg;
fnd_msg_pub.Delete_Msg;
fnd_msg_pub.Delete_Msg;
SELECT /*+ index(ptbcp1 PV_TAP_BATCH_CHG_PARTNERS_N1) */ count(*)
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE PROCESSED_FLAG = 'P' ;
SELECT count(*)
FROM PV_PARTNER_PROFILES
WHERE STATUS = 'A' ;
SELECT /*+ index(ptbcp1 PV_TAP_BATCH_CHG_PARTNERS_N1) */ partner_id
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE PROCESSED_FLAG = 'P' ;
SELECT partner_id
FROM PV_PARTNER_PROFILES
WHERE STATUS = 'A' ;
SELECT first.f,decode(last.l,null,(SELECT max(partner_id)
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE partner_id >= first.f
AND PROCESSED_FLAG = 'P'
AND last.l is null),last.l) la
FROM
(SELECT decode(mod(rn,cv_batch_size),1,partner_id,null) f,null last,rownum rn
FROM ( SELECT PARTNER_ID,ROWNUM RN
FROM ( SELECT /*+ index(ptbcp1 PV_TAP_BATCH_CHG_PARTNERS_N1) */ partner_id
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE PROCESSED_FLAG = 'P'
ORDER BY partner_id asc ) )
WHERE decode(mod(rn,cv_batch_size),1,partner_id,null) IS NOT null ) first,
(SELECT null first,decode(mod(rn,cv_batch_size),0,partner_id,null) l,rownum rn
FROM ( SELECT PARTNER_ID,ROWNUM RN
FROM ( SELECT /*+ index(ptbcp1 PV_TAP_BATCH_CHG_PARTNERS_N1) */ partner_id
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE PROCESSED_FLAG = 'P'
ORDER BY partner_id asc) )
WHERE decode(mod(rn,cv_batch_size),0,partner_id,null) is not null) last
WHERE first.rn=last.rn(+);
SELECT first.f,decode(last.l,null,(SELECT max(partner_id)
FROM PV_PARTNER_PROFILES
WHERE partner_id >= first.f
AND STATUS = 'A'
AND last.l is null),last.l) la
FROM
(SELECT decode(mod(rn,cv_batch_size),1,partner_id,null) f,null last,rownum rn
FROM ( SELECT PARTNER_ID,ROWNUM RN
FROM ( SELECT partner_id
FROM PV_PARTNER_PROFILES
WHERE STATUS = 'A'
ORDER BY partner_id asc ) )
WHERE decode(mod(rn,cv_batch_size),1,partner_id,null) IS NOT null ) first ,
(SELECT null first,decode(mod(rn,cv_batch_size),0,partner_id,null) l,rownum rn
FROM ( SELECT PARTNER_ID,ROWNUM RN
FROM( SELECT partner_id
FROM PV_PARTNER_PROFILES
WHERE STATUS = 'A'
ORDER BY partner_id asc) )
WHERE decode(mod(rn,cv_batch_size),0,partner_id,null) is not null) last
WHERE first.rn=last.rn(+);
l_program_update_date DATE := sysdate;
SELECT partner_id , object_version_number
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE partner_id = cv_partner_id;
SELECT ppa.partner_id
FROM pv_partner_accesses ppa,
pv_partner_profiles ppp,
pv_tap_access_terrs ptat
WHERE ppa.partner_access_id = ptat.partner_access_id
AND ppa.partner_id = ppp.partner_id
AND ppp.status = 'A'
AND ptat.terr_id IN (
cv_terr_id1, cv_terr_id2, cv_terr_id3, cv_terr_id4,
cv_terr_id5, cv_terr_id6, cv_terr_id7, cv_terr_id8,
cv_terr_id9, cv_terr_id10, cv_terr_id11, cv_terr_id12,
cv_terr_id13, cv_terr_id14, cv_terr_id15, cv_terr_id16,
cv_terr_id17, cv_terr_id18, cv_terr_id19, cv_terr_id20);
SELECT object_version_number
FROM PV_TAP_BATCH_CHG_PARTNERS
WHERE partner_id = cv_partner_id
AND processed_flag = 'P';
SELECT distinct hzp.party_name partner_name
FROM PV_PARTNER_PROFILES ppp,
HZ_PARTIES hzp
WHERE ppp.partner_id = cv_partner_id
AND ppp.partner_party_id = hzp.party_id
AND ppp.STATUS = 'A';
-- UPDATE the record for that PARTNER_ID with the appropriate PROCESSED_FLAG status.
-- ELSE
-- CREATE a record for that PARTNER_ID with the appropriate PROCESSED_FLAG status.
-- END IF;
-- * PROGRAM_UPDATE_DATE
------------------------------------------------------------
l_change_partner.partner_id := l_partner_id ;
l_change_partner.program_update_date := l_program_update_date;
-- * PROGRAM_UPDATE_DATE
------------------------------------------------------------
l_change_partner.partner_id := l_partner_id ;
l_change_partner.program_update_date := l_program_update_date;
l_change_partner.last_update_date := sysdate;
l_change_partner.last_update_by := FND_GLOBAL.user_id;
l_change_partner.last_update_login := FND_GLOBAL.user_id;
PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
p_api_version_number => 1.0 ,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_chg_prtnrs_rec => l_change_partner );
FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');
fnd_msg_pub.Delete_Msg;
fnd_msg_pub.Delete_Msg;
fnd_msg_pub.Delete_Msg;