[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT source_id
FROM jtf_rs_resource_extns
WHERE resource_id = cv_resource_id
AND category='EMPLOYEE' ;
PVX_UTILITY_PVT.debug_message('Start : Inserting into pv_search_attr_values table for partner :'||l_partner_id);
' INSERT INTO pv_search_attr_values
SELECT pv_search_attr_values_s.nextval
, partner_id
, null
, attr_text
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, 1.0
, FND_GLOBAL.user_id
, null
, attribute_id
, null
from (
SELECT distinct 4 attribute_id, hl.country attr_text, pvpp.partner_id partner_id
FROM hz_locations hl,
hz_party_sites hs,
pv_partner_profiles pvpp
WHERE hl.location_id = hs.location_id
AND hs.party_id = pvpp.partner_party_id
AND pvpp.partner_id = :1
AND (hs.status = ''A'' OR hs.status IS NULL)
UNION all
SELECT 11 attribute_id, UPPER(PARTNER.party_name) attr_text ,PVPP.partner_id partner_id
FROM hz_organization_profiles HZOP_PRT,
pv_enty_attr_values PEAV,
hz_parties PARTNER,
hz_organization_profiles HZOP,
hz_parties INTERNAL,
hz_relationships HZR,
pv_partner_profiles PVPP
WHERE HZR.party_id = PVPP.partner_id
AND HZR.object_id = INTERNAL.party_id
AND HZR.subject_id = PVPP.partner_party_id
AND HZR.subject_id = PARTNER.party_id
AND HZR.subject_table_name = ''HZ_PARTIES''
AND HZR.object_table_name = ''HZ_PARTIES''
AND HZR.subject_type = ''ORGANIZATION''
AND HZR.object_type = ''ORGANIZATION''
AND HZR.RELATIONSHIP_TYPE IN (''PARTNER'',''PARTNER_MANAGED_CUSTOMER'')
AND INTERNAL.PARTY_TYPE=''ORGANIZATION''
AND PARTNER.PARTY_TYPE =''ORGANIZATION''
AND HZOP.party_id = INTERNAL.party_id
AND HZOP.effective_end_date is null
AND INTERNAL.status IN (''A'',''I'')
AND PARTNER.status IN (''A'',''I'')
AND HZR.status IN (''A'',''I'')
AND HZOP_PRT.party_id = PARTNER.party_id
AND HZOP_PRT.effective_end_date is null
AND PEAV.entity_id(+) = HZR.party_id
AND PEAV.entity(+) = ''PARTNER''
AND PEAV.attribute_id(+) = 3
AND PEAV.attr_value(+) = ''VAD''
AND PEAV.latest_flag(+) = ''Y''
AND HZOP.internal_flag = ''Y''
AND PVPP.partner_id = :1
UNION all
SELECT 3 attribute_id,pear.attr_value attr_text, pear.entity_id partner_id
FROM pv_enty_attr_values pear
WHERE pear.entity_id = :1
AND pear.latest_flag = ''Y''
AND attr_value is not null
AND attribute_id = 3 )' USING l_partner_id,l_partner_id,l_partner_id ;
PVX_UTILITY_PVT.debug_message('End : Inserting into search table ');
PVX_UTILITY_PVT.debug_message('Start : Inserting into pv_search_attr_values table for partner :'||l_partner_id);
' INSERT INTO pv_search_attr_values
SELECT pv_search_attr_values_s.nextval
, partner_id
, null
, attr_text
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, 1.0
, FND_GLOBAL.user_id
, null
, attribute_id
, null
from (
SELECT distinct 4 attribute_id, hl.country attr_text, pvpp.partner_id partner_id
FROM hz_locations hl,
hz_party_sites hs,
pv_partner_profiles pvpp
WHERE hl.location_id = hs.location_id
AND hs.party_id = pvpp.partner_party_id
AND pvpp.partner_id = :1
AND (hs.status = ''A'' OR hs.status IS NULL)
UNION all
SELECT 11 attribute_id, UPPER(PARTNER.party_name) attr_text ,PVPP.partner_id partner_id
FROM hz_organization_profiles HZOP_PRT,
pv_enty_attr_values PEAV,
hz_parties PARTNER,
hz_organization_profiles HZOP,
hz_parties INTERNAL,
hz_relationships HZR,
pv_partner_profiles PVPP
WHERE HZR.party_id = PVPP.partner_id
AND HZR.object_id = INTERNAL.party_id
AND HZR.subject_id = PVPP.partner_party_id
AND HZR.subject_id = PARTNER.party_id
AND HZR.subject_table_name = ''HZ_PARTIES''
AND HZR.object_table_name = ''HZ_PARTIES''
AND HZR.subject_type = ''ORGANIZATION''
AND HZR.object_type = ''ORGANIZATION''
AND HZR.RELATIONSHIP_TYPE IN (''PARTNER'',''PARTNER_MANAGED_CUSTOMER'')
AND INTERNAL.PARTY_TYPE=''ORGANIZATION''
AND PARTNER.PARTY_TYPE =''ORGANIZATION''
AND HZOP.party_id = INTERNAL.party_id
AND HZOP.effective_end_date is null
AND INTERNAL.status IN (''A'',''I'')
AND PARTNER.status IN (''A'',''I'')
AND HZR.status IN (''A'',''I'')
AND HZOP_PRT.party_id = PARTNER.party_id
AND HZOP_PRT.effective_end_date is null
AND PEAV.entity_id(+) = HZR.party_id
AND PEAV.entity(+) = ''PARTNER''
AND PEAV.attribute_id(+) = 3
AND PEAV.attr_value(+) = ''VAD''
AND PEAV.latest_flag(+) = ''Y''
AND HZOP.internal_flag = ''Y''
AND PVPP.partner_id = :1
UNION all
SELECT 3 attribute_id,pear.attr_value attr_text, pear.entity_id partner_id
FROM pv_enty_attr_values pear
WHERE pear.entity_id = :1
AND pear.latest_flag = ''Y''
AND attr_value is not null
AND attribute_id = 3 )' USING l_partner_id,l_partner_id,l_partner_id ;
PVX_UTILITY_PVT.debug_message('End : Inserting into search table ');
SELECT distinct site_use_type
FROM hz_party_site_uses hpsu,
hz_party_sites hps
WHERE hpsu.party_site_id = hps.party_site_id
AND site_use_type in ('SHIP_TO','BILL_TO')
AND hps.party_id = cv_party_id ;
SELECT party_site_id FROM HZ_PARTY_SITES WHERE PARTY_ID = CV_PARTY_ID AND IDENTIFYING_ADDRESS_FLAG = 'Y';
SELECT party_site_id
FROM hz_party_sites
WHERE party_id = cv_party_id
AND identifying_address_flag = 'Y'
AND status = 'A';
SELECT party_name
FROM hz_parties PARTY
WHERE PARTY.party_id = cv_partner_id
AND PARTY.status = 'A'
AND PARTY.party_type = 'PARTY_RELATIONSHIP';
SELECT hzr.relationship_id relationship_id,
hzp.party_id party_id,
hzp.party_number party_number
FROM hz_relationships hzr,
hz_parties hzp
WHERE hzr.subject_id = cv_partner_party_id
AND hzr.object_id = cv_vendor_party_id
AND hzr.status = 'A'
AND hzr.end_date > sysdate
AND relationship_code = 'PARTNER_OF'
AND relationship_type = 'PARTNER'
AND hzr.party_id = hzp.party_id;
PVX_UTILITY_PVT.debug_message('Prior to calling update_elig_prgm_4_new_ptnr ');
PV_USER_MGMT_PVT.update_elig_prgm_4_new_ptnr(
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 => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_partner_id => l_partner_id
,p_member_type => NULL
);
PVX_UTILITY_PVT.debug_message('After call to update_elig_prgm_4_new_ptnr API. l_return_status: ' || l_return_status);
SELECT partner_id
FROM pv_partner_profiles
WHERE partner_party_id = cv_party_id;
SELECT COUNT(*) cnt
FROM jtf_rs_resource_extns jtfre,
jtf_auth_principals_b jtfp1,
jtf_auth_principal_maps jtfpm,
jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2,
jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm
WHERE jtfre.resource_id = p_resource_id AND
jtfre.user_name = jtfp1.principal_name AND
jtfp1.is_user_flag = 1 AND
jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_principal_id AND
jtfpm.jtf_auth_domain_id = jtfd.jtf_auth_domain_id AND
jtfd.domain_name = 'CRM_DOMAIN' 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_UPDATE_ALL_PARTNERS', 'PV_VIEW_ALL_PARTNERS'))
LOOP
IF (x.cnt > 0) THEN
l_ret_val := 1;