[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT source_id,user_id, substr(source_name,1,20)
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = cv_resource_id;
SELECT partner_party_id
FROM PV_PARTNERS_V
WHERE partner_profile_id = cv_partner_profile_id;
SELECT partner_party_id
FROM PV_PARTNER_PROFILES
WHERE partner_profile_id = cv_partner_profile_id;
SELECT party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = cv_party_id
AND identifying_address_flag = 'Y'
AND NVL(start_date_active,SYSDATE) <= SYSDATE
AND NVL(end_date_active,SYSDATE) >= SYSDATE;
SELECT GRPREL.group_id
FROM JTF_RS_RESOURCE_EXTNS RES ,JTF_RS_ROLE_RELATIONS RREL ,JTF_RS_ROLES_VL ROLE
,JTF_RS_GROUPS_TL GROUPS ,JTF_RS_GROUP_USAGES U ,JTF_RS_GROUP_MEMBERS GRPREL
WHERE RES.category = 'EMPLOYEE' AND ROLE.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
AND RREL.role_id = ROLE.role_id AND GROUPS.language = userenv('LANG') AND GRPREL.group_id = GROUPS.group_id
AND NVL(RREL.delete_flag,'N') = 'N' AND RREL.start_date_active <= sysdate
AND NVL(RREL.end_date_active,sysdate) >= sysdate AND U.group_id = GRPREL.group_id
AND U.usage = 'SALES' AND (ROLE.member_flag = 'Y' or ROLE.manager_flag='Y')
AND RREL.role_resource_type = 'RS_GROUP_MEMBER' AND GRPREL.group_member_id = RREL.role_resource_id
AND NVL(GRPREL.delete_flag,'N') = 'N' AND RES.resource_id = GRPREL.resource_id
AND RES.resource_id = cv_salesforce_id;
SELECT jrre.resource_number,jrret.resource_name,jrre.source_name,jrre.source_org_id,
jrre.source_first_name,jrre.source_last_name,jrre.source_middle_name
FROM jtf_rs_resource_extns jrre ,jtf_rs_resource_extns_tl jrret
WHERE jrre.resource_id = p_admin_rec.role_resource_id
AND jrre.resource_id = jrret.resource_id ;
SELECT
HZL.address1
,HZL.address2
,HZL.address3
,HZL.address4
,HZL.city
,HZL.postal_code
,HZL.state
,HZL.province
,HZL.county
,HZL.country
FROM hz_party_sites HZPS
,hz_locations HZL
WHERE
HZPS.location_id = HZL.location_id(+)
AND HZPS.identifying_address_flag(+) = 'Y'
AND HZPS.party_site_id = curvar_party_site_id;
if (p_mode <> 'UPDATE') then
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
PVX_Utility_PVT.debug_message(l_full_name ||': create');
elsif (p_mode = 'UPDATE') then
Complete_Admin_Rec (
p_admin_rec =>l_admin_rec
,x_complete_rec =>l_admin_complete_rec
);
JTF_RS_RESOURCE_PUB.Update_Resource(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_false
,p_resource_id => l_admin_complete_rec.role_resource_id
,p_resource_number => l_admin_complete_rec.resource_number
-- ,p_start_date_active => SYSDATE
-- ,p_end_date_active => FND_API.g_miss_date
,p_resource_name => l_admin_complete_rec.resource_name -- resource_name
,p_source_name => l_admin_complete_rec.source_name -- source_name
,p_source_org_id => l_admin_complete_rec.source_org_id -- source_org_id
/*,p_source_org_name => l_admin_complete_rec.source_org_name -- source_org_name
,p_source_address1 => currec_resource_address.address1
,p_source_address2 => currec_resource_address.address2
,p_source_address3 => currec_resource_address.address3
,p_source_address4 => currec_resource_address.address4
,p_source_city => currec_resource_address.city
,p_source_postal_code => currec_resource_address.postal_code
,p_source_state => currec_resource_address.state
,p_source_province => currec_resource_address.province
,p_source_county => currec_resource_address.county
,p_source_country => currec_resource_address.country
*/
,p_source_first_name => l_admin_complete_rec.source_first_name
,p_source_last_name => l_admin_complete_rec.source_last_name
,p_source_middle_name => l_admin_complete_rec.source_middle_name
--,p_source_category => l_admin_complete_rec.resource_type
--,p_source_id => l_admin_complete_rec.partner_id -- source_id
--,p_user_id => l_admin_complete_rec.user_id -- user_id
--,p_contact_id => l_admin_complete_rec.contact_id
--,p_address_id => l_admin_complete_rec.party_site_id
,p_object_version_num => l_admin_complete_rec.object_version_number
--,p_user_name => l_admin_complete_rec.user_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT role_id
FROM JTF_RS_ROLES_VL
WHERE role_code = cv_role_code;
SELECT SUBSTR(party_name,1,80)
FROM hz_parties
WHERE party_id = cv_partner_id;
SELECT SUBSTRB(PARTNER.party_name,1,44)||'('||PARTNER.party_id||')'
FROM hz_relationships HZR, hz_parties PARTNER, hz_organization_profiles HZOP
WHERE HZR.party_id = cv_partner_id
AND HZR.subject_id = PARTNER.party_id
AND PARTNER.party_id = HZOP.party_id
AND NVL(HZOP.internal_flag,'N') = 'N'
AND HZOP.effective_end_date IS NULL;
SELECT role_id
FROM JTF_RS_ROLES_VL
WHERE role_code = cv_role_code;
PROCEDURE Update_User(
p_api_version 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_fnd_rec IN fnd_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_User';
SAVEPOINT Update_User;
FND_USER_PKG.UpdateUser
(
X_USER_NAME => l_fnd_rec.user_name
,X_OWNER => l_owner
,X_END_DATE => l_fnd_rec.end_date
,X_EMAIL_ADDRESS => l_fnd_rec.email_address
);
FND_MESSAGE.set_name('PV', 'PV_ERROR_UPDATE_USER');
ROLLBACK TO Update_User;
ROLLBACK TO Update_User;
ROLLBACK TO Update_User;
END Update_User;
SELECT FNDU.user_name
,FNDR.responsibility_key
,FNDSG.security_group_key
FROM fnd_user FNDU, fnd_responsibility FNDR, fnd_user_resp_groups FNDURG, fnd_security_groups FNDSG
WHERE FNDU.user_id = cv_user_id
AND FNDR.responsibility_id = cv_resp_id
AND FNDR.application_id = cv_resp_app_id
AND FNDU.user_id = FNDURG.user_id
AND FNDR.responsibility_id = FNDURG.responsibility_id
AND FNDR.application_id = FNDURG.responsibility_application_id
AND FNDURG.security_group_id = FNDSG.security_group_id
;
PROCEDURE update_partner_status (
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2 ) IS
CURSOR c_partners IS
SELECT *
FROM pv_partner_profiles
Where status <> 'M'; -- hekkiral for bug fix 6694939. Merged records
SELECT subject_id vendor_party_id,
start_date,
end_date,
status
FROM hz_relationships
WHERE party_id = p_party_id
AND object_id = p_partner_party_id;
SELECT NVL(status, 'A') party_status
FROM hz_parties
WHERE party_id = p_party_id;
SELECT start_date_active,
end_date_active
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id;
SELECT SUBSTRB(party_name, 1, 100) partner_name,
party_number partner_number
FROM hz_parties
WHERE party_id = p_party_id;
SAVEPOINT update_partner_status;
PVX_PRTNR_PRFLS_PVT.Update_Prtnr_Prfls(
p_api_version => l_api_version
,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_prtnr_prfls_rec => l_prtnr_prfls_rec
);
ROLLBACK TO update_partner_status;
END Update_Partner_Status;
select distinct pvpp.partner_party_id, hzp.party_name
from pv_partner_profiles pvpp, hz_parties hzp
where pvpp.status = 'A'
and hzp.party_id = pvpp.partner_party_id
and hzp.status = 'A'
and not exists
(select 1
from hz_cust_accounts hzca
where hzca.status = 'A'
and hzca.party_id = pvpp.partner_party_id);
select hzr.party_id, max(hzca.cust_account_id) cust_account_id
from jtf_rs_resource_extns jtfre, hz_relationships hzr, hz_cust_accounts hzca
where jtfre.category= 'PARTY'
and jtfre.user_id is not null
and jtfre.source_id = hzr.party_id
and hzr.object_id in (select pvpp.partner_party_id
from pv_partner_profiles pvpp
where status = 'A')
and hzca.party_id = hzr.object_id
and hzca.status = 'A'
and hzr.relationship_code = 'EMPLOYEE_OF'
and hzr.directional_flag = 'F'
and hzr.status = 'A'
and hzr.start_date <= sysdate
and nvl(hzr.end_date,sysdate) >= sysdate
and not exists
( select 1
from hz_cust_account_roles hzcar
where hzcar.cust_account_id IN (select cust_account_id from hz_cust_accounts where party_id = hzr.object_id)
and nvl(hzcar.status,'A')='A'
and nvl(hzcar.begin_date,sysdate) <= sysdate
and nvl(hzcar.end_date,sysdate) >= sysdate
and hzr.party_id = hzcar.party_id
)
group by hzr.party_id;
SELECT generate_customer_number INTO l_gen_cust_num FROM ar_system_parameters;
select TO_CHAR( HZ_ACCOUNT_NUM_S.NEXTVAL) into account_rec.account_number from dual ;