[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT REGSRVC.REG_SERVICE_TYPE
INTO l_reg_service_type
FROM UMX_REG_SERVICES_VL REGSRVC
WHERE REGSRVC.REG_SERVICE_CODE = p_reg_service_code
AND REGSRVC.START_DATE <= SYSDATE
AND NVL(REGSRVC.END_DATE, SYSDATE+1) > SYSDATE;
SELECT CustAcct.cust_account_id,
CustAcct.account_number,
CustAcct.party_id,
Party.party_type
FROM HZ_CUST_ACCOUNTS CustAcct,
HZ_PARTIES Party
WHERE CustAcct.cust_account_id = p_customer_id
AND CustAcct.party_id = Party.party_id;
l_debug_info := 'Insert Row, reg_id = ' || to_char(p_registration_id) || ',ip_addr= ' ||
p_client_ip_address || 'customer_id = ' || to_char(p_customer_id) || ',customer_site_id = ' ||
to_char(p_customer_site_use_id);
ARI_REG_VERIFICATIONS_PKG.Insert_Row(
x_rowid => l_rowid,
x_client_ip_address => p_client_ip_address,
x_question => l_verify_access(i).question,
x_expected_answer => l_verify_access(i).expected_answer,
x_number_of_attempts => 0,--l_attempts,
x_currency_code => l_verify_access(i).currency_code,
x_customer_id => p_customer_id,
x_customer_site_use_id => p_customer_site_use_id,
x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
x_last_update_date => sysdate,
x_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
x_creation_date => sysdate,
x_created_by => nvl(FND_GLOBAL.user_id,-1));
arp_standard.debug('after insert row');
l_debug_info := 'Delete all records in Registration GT';
DELETE FROM ARI_REG_VERIFICATIONS_GT;
l_debug_info := 'All records in Registration GT deleted';
l_debug_info := 'Insert Row,ip_addr= ' || p_client_ip_address || 'customer_id = ' || to_char(p_customer_id);
ARI_REG_VERIFICATIONS_PKG.Insert_Row(
x_rowid => l_rowid,
x_client_ip_address => p_client_ip_address,
x_question => l_verify_access(i).question,
x_expected_answer => l_verify_access(i).expected_answer,
x_number_of_attempts => 0, --l_attempts,
x_currency_code => l_verify_access(i).currency_code,
x_customer_id => p_customer_id,
x_customer_site_use_id => null,
x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
x_last_update_date => sysdate,
x_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
x_creation_date => sysdate,
x_created_by => nvl(FND_GLOBAL.user_id,-1));
arp_standard.debug('after insert row');
select expected_answer
into l_expected_answer
from ari_reg_verifications_gt
where reg_access_verify_id = p_reg_access_verify_id;
select hcar.cust_account_role_id
from hz_role_responsibility hrr, hz_cust_account_roles hcar
where hrr.responsibility_type = 'SELF_SERVICE_USER'
and hrr.cust_account_role_id = hcar.cust_account_role_id
and hcar.cust_account_id = p_customer_id
--Bug 4764121 : Fixed the removal of access to all customers
and DECODE(p_cust_acct_site_id, '-1', -1,p_cust_acct_site_id) =
DECODE(p_cust_acct_site_id, '-1', -1, hcar.cust_acct_site_id)
and hcar.party_id = p_person_party_id;
SELECT OBJECT_VERSION_NUMBER
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ROLE_ID = p_cust_acct_role_id;
l_debug_info := 'Update Cust Account Role';
HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
p_init_msg_list => FND_API.G_FALSE,
p_cust_account_role_rec => p_cust_account_role_rec_type,
p_object_version_number => l_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
select to_char(customer_id)
into l_party_rel_id
from fnd_user
where user_id = p_user_id;
SELECT party_id INTO l_party_id FROM hz_cust_accounts WHERE cust_account_id = p_customer_id;
select party_id into l_party_rel_id
from (
select hr1.party_id
from hz_relationships hr1,
hz_relationships hr2,
fnd_user fu
where hr1.subject_type = 'PERSON'
AND (hr1.relationship_code = 'CONTACT_OF' OR hr1.relationship_code = 'EMPLOYEE_OF')
AND hr1.status = 'A'
and hr1.object_id = l_party_id
and hr1.subject_id = hr2.subject_id
AND (hr1.end_date is null OR hr1.end_date > sysdate)
and hr2.party_id = fu.customer_id
and fu.user_id = p_user_id
UNION ALL
select hr1.party_id
from hz_relationships hr1,
fnd_user fu, hz_parties Party
where hr1.subject_type = 'PERSON'
AND (hr1.relationship_code = 'CONTACT_OF' OR hr1.relationship_code = 'EMPLOYEE_OF')
AND hr1.status = 'A'
and hr1.object_id = l_party_id
and hr1.subject_id = fu.customer_id
AND (hr1.end_date is null OR hr1.end_date > sysdate)
and fu.user_id = p_user_id
AND Party.party_id = fu.customer_id
AND Party.party_type = 'PERSON'
AND Party.status = 'A');
select account_number
into l_acct_number
from hz_cust_accounts
where cust_account_id = p_cust_account_id;
select 'Y'
into l_return
from dual
where p_user_id IN ( select user_id
from umx_role_assignments_v
where role_name like 'UMX|ARI_CUST_ADMIN');
l_debug_info := 'Call FND_USER_PKG to update user with person party id';
FND_USER_PKG.UpdateUser (
x_user_name => l_reg_user_name,
x_owner => 'CUST',
x_email_address => l_email_address,
x_customer_id => p_person_party_id
);
select party_id
into l_org_party_id
from hz_cust_accounts
where cust_account_id = p_customer_id;
select hcas.party_site_id
into l_party_site_id
from hz_cust_acct_sites hcas, hz_cust_site_uses hcsu
where hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hcsu.site_use_id = p_cust_site_use_id;
select cust_acct_site_id
into l_cust_acct_site_id
from hz_cust_site_uses
where site_use_id = p_cust_site_use_id;
SELECT 'Y',party_id
INTO l_already_exists,p_party_id
FROM HZ_RELATIONSHIPS
WHERE SUBJECT_ID = p_person_party_id
AND SUBJECT_TYPE = 'PERSON'
AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND RELATIONSHIP_TYPE = 'CONTACT'
AND RELATIONSHIP_CODE = 'CONTACT_OF'
AND OBJECT_ID = l_org_party_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date,SYSDATE))
AND TRUNC(NVL(end_date,SYSDATE));
l_debug_info := 'Email Address not null - create/update email contact point';
SELECT contact_point_id
INTO l_contact_point_id
FROM hz_contact_points
WHERE owner_table_id = p_contact_party_id
AND owner_table_name = 'HZ_PARTIES'
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'EMAIL';
l_debug_info := 'Update email contact point';
SELECT object_version_number
INTO l_object_version_number
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_contact_point_id;
HZ_CONTACT_POINT_V2PUB.update_contact_point (
p_contact_point_rec => l_contact_point_rec,
p_email_rec => l_email_rec,
p_object_version_number => l_object_version_number,
x_return_status => X_Return_Status,
x_msg_count => X_Msg_Count,
x_msg_data => X_Msg_Data);
l_debug_info := 'Primary Phone not null - create/update phone contact point';
SELECT contact_point_id
INTO l_contact_point_id
FROM hz_contact_points
WHERE owner_table_id = p_contact_party_id
AND owner_table_name = 'HZ_PARTIES'
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE';
l_debug_info := 'Update phone contact point';
SELECT object_version_number
INTO l_object_version_number
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_contact_point_id;
HZ_CONTACT_POINT_V2PUB.update_contact_point (
p_contact_point_rec => l_contact_point_rec,
p_phone_rec => l_phone_rec,
p_object_version_number => l_object_version_number,
x_return_status => X_Return_Status,
x_msg_count => X_Msg_Count,
x_msg_data => X_Msg_Data);
SELECT 'Y',a.cust_account_role_id,status,a.object_version_number
INTO l_already_exists,p_cust_acct_role_id,l_status,l_version_number
FROM hz_cust_account_roles a
WHERE party_id = p_party_id
AND cust_account_id = l_customer_id
AND ((cust_acct_site_id is null and l_cust_acct_site_id is null )
OR cust_acct_site_id = l_cust_acct_site_id )
AND role_type = 'CONTACT'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(begin_date,SYSDATE))
AND TRUNC(NVL(end_date,SYSDATE));
HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
p_init_msg_list => FND_API.G_TRUE,
p_cust_account_role_rec => l_cust_acct_roles_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_version_number => l_version_number);
l_debug_info := 'Error Calling HZ Update Cust Acct Roles API: ' || l_msg_data;
SELECT 'Y'
INTO l_already_exists
FROM hz_role_responsibility
WHERE cust_account_role_id = p_cust_acct_role_id
AND responsibility_type = 'SELF_SERVICE_USER';
SELECT usr.user_name into l_user_name
FROM fnd_user usr
WHERE Usr.user_id = fnd_global.user_id;
SELECT UMX_REG_REQUESTS_S.nextval INTO l_item_key FROM dual;
select fpov.profile_option_value
into l_reg_sec_profile
from fnd_profile_option_values fpov, fnd_profile_options fpo
where fpov.profile_option_id = fpo.profile_option_id
and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
and fpov.level_id = 10004 -- user level
and fpov.level_value = p_user_id;
select fpov.profile_option_value
into l_reg_sec_profile
from fnd_profile_option_values fpov, fnd_profile_options fpo
where fpov.profile_option_id = fpo.profile_option_id
and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
and fpov.level_id = 10003 -- responsibility level
and fpov.level_value_application_id = 222
and fpov.level_value = p_resp_id; -- Resp Id of ARI_REGISTER_RESP- iReceivables Registration Responsibility
select fpov.profile_option_value
into l_reg_sec_profile
from fnd_profile_option_values fpov, fnd_profile_options fpo
where fpov.profile_option_id = fpo.profile_option_id
and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
and fpov.level_id = 10001; -- Site level
SELECT count(cust_acct_site_id)
INTO l_count_sites
FROM hz_cust_acct_sites hcas
WHERE hcaS.cust_account_id = p_customer_id;
SELECT to_char(resp.responsibility_id)
INTO l_resp_id
FROM fnd_responsibility_vl resp, wf_roles role
WHERE role.name = p_role_name
AND resp.responsibility_name = role.display_name;