[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_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_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;
the record from ari_reg_verifications when access is removed for the selected location for Bug 13869981 */
---------------------------------------------------------------------
if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
l_debug_info := 'InsideRemoveRole'||p_person_party_id||' '||p_customer_id||p_cust_acct_site_id;
select user_name INTO l_user_name FROM fnd_user where user_id = l_user_id;
select site_uses.site_use_id INTO l_site_use_id FROM hz_cust_site_uses site_uses
where site_uses.cust_acct_site_id = p_cust_acct_site_id
and site_uses.site_use_code = 'BILL_TO';
delete from ARI_REG_VERIFICATIONS
WHERE UPPER(user_email_addr) = UPPER(l_user_name)
AND application_code = 'ARI'
AND access_domain_cust_id = p_customer_id
AND (access_domain_billto_id is null or access_domain_billto_id = nvl(l_site_use_id,-1))
AND status = 'COMPLETE';
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 fnd.user_id into l_user_id
from fnd_user fnd
where user_name='GUEST';
select fnd.user_id into l_user_id
from fnd_user fnd
where person_party_id = p_person_party_id;
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => l_registration_id,
x_Status_Lookup_Code => 'COMPLETE',
x_Last_Update_Date => sysdate);
SELECT usr.user_name into l_user_name
FROM fnd_user usr
WHERE Usr.user_id = fnd_global.user_id;
SELECT usr.user_name into l_user_name
FROM fnd_user usr
WHERE Usr.person_party_id = p_person_party_id;
SELECT UMX_REG_REQUESTS_S.nextval INTO l_item_key FROM dual;
SELECT UMX_REG_REQUESTS_S.nextval INTO l_event_key FROM dual;
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
x_Wf_Item_Key => l_item_key,
x_Status_Lookup_Code => 'IN PROGRESS',
x_Last_Update_Date => sysdate);
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
| FUNCTION UpdateRegistrationTable
|
| DESCRIPTION
|
|
| PARAMETERS
|
* ======================================================================*/
---------------------------------------------------------------------------
FUNCTION UpdateRegistrationTable(p_subscription_guid in raw,
p_event in out NOCOPY WF_EVENT_T)
RETURN VARCHAR2
---------------------------------------------------------------------------
IS
l_registration_id VARCHAR2(500);
l_procedure_name VARCHAR2(50) := '.UpdateRegistrationTable';
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateRegistrationTable');
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => l_registration_id,
x_Wf_Item_Key => l_item_key,
x_Status_Lookup_Code => 'IN PROGRESS',
x_Last_Update_Date => sysdate);
WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'UpdateRegistrationTable',
p_event.getEventName( ),p_subscription_guid,
sqlerrm,sqlcode);
END UpdateRegistrationTable;
| FUNCTION UpdateRejectedStatus
|
| DESCRIPTION This function is added as a part of Bug 13869981
|
|
| PARAMETERS
|
* ======================================================================*/
--------------------------------------------------------------------------
FUNCTION UpdateRejectedStatus(p_subscription_guid in raw,
p_event in out NOCOPY WF_EVENT_T)
RETURN VARCHAR2
--------------------------------------------------------------------------
IS
l_item_key VARCHAR2(500);
l_procedure_name VARCHAR2(500) := '.UpdateRejectedStatus';
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'In UpdateRejectedStatus Function');
-- To ensure that the status has been updated to Rejected
-- This function is called on the event 'oracle.apps.ar.irec.accountrequest.notification.start'
-- To ensure that the status has been updated to Complete or Rejected in the ARI_REG_VERIFICATIONS table
l_registration_id := p_event.getvalueforparameter('REGISTRATION_ID');
l_debug_info := 'Values in UpdateRejectedStatus ---'||l_registration_id||l_item_key||l_approval_result;
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => l_registration_id,
x_Status_Lookup_Code => l_status,
x_Last_Update_Date => sysdate);
WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'UpdateRejectedStatus',
p_event.getEventName( ),p_subscription_guid,
sqlerrm,sqlcode);
END UpdateRejectedStatus;
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;
l_days_since_last_update NUMBER;
l_company_id, l_days_since_last_update;
IF (l_release_if_on_hold_flag OR (l_days_since_last_update > l_profile_reg_hold_days)) THEN
---------------------------------------------------------------------------
l_debug_info := 'Release the hold for registration_id: ' || to_char(l_registration_id);
SELECT trunc(sysdate - max(last_update_date))
FROM ARI_REG_VERIFICATIONS
WHERE access_domain_cust_id = p_cust_account_id
AND application_code = p_application_code
AND status = G_HOLD;
SELECT trunc(sysdate - max(last_update_date))
FROM ARI_REG_VERIFICATIONS
WHERE UPPER(user_email_addr) = UPPER(p_user_name)
AND application_code = p_application_code
AND status = G_HOLD;
SELECT trunc(sysdate - max(last_update_date))
FROM ARI_REG_VERIFICATIONS
WHERE access_domain_cust_id = p_cust_account_id
AND access_domain_billto_id = p_billto_id
AND application_code = p_application_code
AND status = G_HOLD;
SELECT count(*)
FROM ARI_REG_VERIFICATIONS
WHERE UPPER(user_email_addr) = UPPER(p_user_name)
AND application_code = p_application_code
AND access_domain_cust_id = p_cust_account_id
AND status = 'COMPLETE';
SELECT registration_id,
access_domain_search_type,
access_domain_cust_id,
status,
company_id,
trunc(sysdate - last_update_date)
FROM ARI_REG_VERIFICATIONS
WHERE UPPER(user_email_addr) = UPPER(p_user_name)
AND access_domain_cust_id = p_cust_account_id
AND (access_domain_billto_id is null or access_domain_billto_id = nvl(p_billto_id,-1))
AND application_code = p_application_code
AND (status IS NULL OR
status <> 'REJECTED');
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
x_Status_Lookup_Code => G_RETRY,
x_Missed_Cust_Question_Count => 0,
x_Missed_Billto_Question_Count => 0);
l_debug_info := 'Insert New Registration Record';
ARI_REG_VERIFY_PKG.Insert_Row(
x_Rowid => l_rowid,
x_Registration_Id => p_registration_id,
x_Application_Code => p_application_code,
x_User_Email_Addr => upper(p_user_email_addr), -- Modified as a part of Bug# 13869981
x_Access_Domain_Search_Type => p_access_domain_search_type,
x_Access_Domain_Search_Number => p_access_domain_search_number,
x_Access_Domain_Type => p_access_domain_type,
x_Access_Domain_Cust_Id => l_cust_acct_id,
x_Access_Domain_Billto_Id => p_access_domain_billto_id,
x_Access_Domain_Number => l_cust_acct_number,
x_Cust_Acct_Type => l_cust_acct_type,
x_Status_Lookup_Code => 'NEW', -- Modified the parameter value for Bug 13869981
x_Missed_Cust_Question_Count => 0,
x_Missed_Billto_Question_Count => 0,
x_Company_Id => l_company_id,
x_Person_Id => l_person_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),
x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
x_Registration_Type => p_registration_type);
SELECT CustAcct.cust_account_id,
CustAcct.account_number,
CustAcct.party_id,
Party.party_type,
CustTrxn.org_id
FROM HZ_CUST_ACCOUNTS CustAcct,
RA_CUSTOMER_TRX_ALL CustTrxn,
HZ_PARTIES Party
WHERE CustTrxn.trx_number = p_access_domain_search_number
AND CustTrxn.bill_to_customer_id = CustAcct.cust_account_id
AND CustAcct.party_id = Party.party_id;
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
x_Status_Lookup_Code => p_status);
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
x_Missed_Cust_Question_Count => p_missed_cust_count,
x_Missed_Billto_Question_Count => p_missed_billto_count,
x_Last_Update_Date => sysdate);
PROCEDURE UpdateBilltoSite(p_registration_id IN VARCHAR2,
p_access_domain_billto_id IN VARCHAR2)
IS
l_procedure_name VARCHAR2(30) := '.UpdateBilltoSite';
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
x_Access_Domain_Billto_Id => p_access_domain_billto_id,
x_Last_Update_Date => sysdate);
END UpdateBilltoSite;
| PROCEDURE UpdateRequestAccess
|
| DESCRIPTION This procedure updates the status(New Access,New Access Retry)
|into the table.
|
|
| PARAMETERS
|
* ======================================================================*/
PROCEDURE UpdateRequestAccess(p_registration_id IN VARCHAR2,
p_registration_type IN VARCHAR2,
p_registration_status IN VARCHAR2)
IS
l_procedure_name VARCHAR2(300) := '.UpdateRequestAccess';
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateRequestAccess');
l_debug_info := 'UpdateRequestAccess param values --- '||p_registration_id||p_registration_type||p_registration_status;
ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
x_Registration_Type => p_registration_type,
x_Status_Lookup_Code => p_registration_status,
x_Last_Update_Date => sysdate);
END UpdateRequestAccess;
| PROCEDURE UpdateApprovalStatus
|
| DESCRIPTION This is used to set the status of Add.Req.Access registration
| record to COMPLETE when the notification has been approved.
| Created as a part of Bug 13869981
|
| PARAMETERS
|
* ======================================================================*/
---------------------------------------------------------------------------
PROCEDURE UpdateApprovalStatus(p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result IN OUT NOCOPY VARCHAR2) IS
-----------------------------------------------------------------------------
l_procedure_name VARCHAR2(300) := 'UpdateApprovalStatus';
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateApprovalStatus'||p_itemkey);
UPDATE ARI_REG_VERIFICATIONS SET STATUS = 'COMPLETE', LAST_UPDATE_DATE = sysdate WHERE WF_ITEM_KEY = p_itemkey;
END UpdateApprovalStatus;
| PROCEDURE UpdateRejectStatus
|
| DESCRIPTION This is used to set the status of Add.Req.Access registration
| record to REJECTED when the notification has been rejected.
| created as a part of Bug 13869981
|
| PARAMETERS
|
* ======================================================================*/
---------------------------------------------------------------------------
PROCEDURE UpdateRejectStatus(p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result IN OUT NOCOPY VARCHAR2) IS
-----------------------------------------------------------------------------
l_procedure_name VARCHAR2(300) := 'UpdateRejectStatus';
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateRejectStatus'||p_itemkey);
UPDATE ARI_REG_VERIFICATIONS SET STATUS = 'REJECTED', LAST_UPDATE_DATE = sysdate WHERE WF_ITEM_KEY = p_itemkey;
END UpdateRejectStatus;