[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| when the user selects the location of the customer requesting access.
|
| PARAMETERS
| p_customer_id IN VARCHAR2
| p_customer_site_use_id IN VARCHAR2 DEFAULT NULL
| x_verify_access OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
| x_attempts OUT NUMBER
|
| NOTES
| Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
| question VARCHAR2(2000)
| expected_answer VARCHAR2(2000)
| currency_code VARCHAR2(15)
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-May-2005 vnb Created
| 22-Mar-2010 avepati bug 7713325 - FLEXIBILITY TO DEFINE CHALLENGE QUESTION DURING SELF REGISTRION
*=======================================================================*/
PROCEDURE verify_customer_site_access( p_customer_id IN VARCHAR2,
p_customer_site_use_id IN VARCHAR2 DEFAULT NULL,
x_verify_access OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
x_attempts OUT NOCOPY NUMBER)
IS
l_trx_number VARCHAR2(255);
select organization_id org_id from hr_operating_units where mo_global.check_access(organization_id) = 'Y';
SELECT org_id INTO cust_site_org_id
FROM hz_cust_site_uses
WHERE site_use_id = p_customer_site_use_id ;
SELECT trx_number
INTO l_trx_number
FROM ar_payment_schedules
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
AND status like 'OP'
AND amount_due_remaining > 0
AND rownum = 1;
SELECT to_char(amount_due_remaining)
INTO x_verify_access(i).expected_answer
FROM ar_payment_schedules
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
AND status like 'OP'
AND amount_due_remaining > 0
AND rownum = 1;
SELECT receipt_number
INTO l_receipt_number
FROM ar_cash_receipts
WHERE PAY_FROM_CUSTOMER = p_customer_id
AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
AND rownum = 1;
SELECT amount
INTO l_temp
FROM ar_cash_receipts
WHERE PAY_FROM_CUSTOMER = p_customer_id
AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
AND rownum = 1;
| when the user selects the customer requesting access.
|
| PARAMETERS
| p_customer_id IN VARCHAR2
| x_verify_access OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
| x_attempts OUT NUMBER
|
| NOTES
| Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
| question VARCHAR2(2000)
| expected_answer VARCHAR2(2000)
| currency_code VARCHAR2(15)
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-May-2005 vnb Created
| 19-Mar-2010 avepati bug 7713325 - FLEXIBILITY TO DEFINE CHALLENGE QUESTION DURING SELF REGISTRION
*=======================================================================*/
PROCEDURE validate_cust_detail_access( p_customer_id IN VARCHAR2,
x_verify_access OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
x_attempts OUT NOCOPY NUMBER)
IS
l_customer_name hz_parties.party_name%type;
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_accounts
where account_number = p_search_number;
* the source. So added 'distinct' in the below query to select unique customer_id.
*/
SELECT DISTINCT customer_id
INTO l_customer_id
FROM ar_payment_schedules
WHERE trx_number = p_search_number
AND class = (CASE p_search_type
WHEN 'INVOICES' THEN 'INV'
WHEN 'PAYMENTS' THEN 'PMT'
WHEN 'DEBIT_MEMOS' THEN 'DM'
WHEN 'CREDIT_MEMOS' THEN 'CM'
WHEN 'DEPOSITS' THEN 'DEP'
END);
l_query_string := 'SELECT anstable.'||p_answer_column|| ' from '|| p_answer_table||' anstable, hz_cust_accounts hca WHERE rownum = 1 and hca.'||p_hz_join_column||' = anstable.'||p_answer_join_column||
' and hca.cust_account_id = '||p_customer_id;
l_query_string := 'SELECT anstable.'||p_answer_column|| ' from '|| p_answer_table||' anstable,hz_party_sites hps, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu where hcsu.site_use_id =' || p_customer_site_use_id ||
' and hcsu.cust_acct_site_id = hcas.cust_acct_site_id and hcas.party_site_id = hps.party_site_id and hps.'||p_hz_join_column||' = anstable.'||p_answer_join_column;
SELECT site_use_id into l_site_use_id FROM
( SELECT site_uses.site_use_id FROM
fnd_territories_vl Terr,
hz_cust_acct_sites acct_sites,
hz_party_sites party_sites,
hz_locations loc,
hz_cust_accounts Cust,
hz_parties Party,
hz_cust_site_uses site_uses
WHERE Party.party_id = Cust.party_id
AND Cust.account_number = p_customer_id
AND Cust.cust_account_id = acct_sites.cust_account_id
AND ACCT_SITES.party_site_id = PARTY_SITES.party_site_id
AND PARTY_SITES.location_id = LOC.location_id
AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'BILL_TO'
AND loc.country = Terr.territory_code(+)
order by site_uses.creation_date asc ) dummy
where rownum=1;
l_query_string :='SELECT anstable.'||p_answer_column|| ' from '|| p_answer_table||' anstable,hz_party_sites hps, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu where hcsu.site_use_id =' || l_site_use_id ||
' and hcsu.cust_acct_site_id = hcas.cust_acct_site_id and hcas.party_site_id = hps.party_site_id and hps.'||p_hz_join_column||' = anstable.'||p_answer_join_column;
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_accounts
where account_number = p_search_number;
* the source. So added 'distinct' in the below query to select unique customer_id.
*/
SELECT DISTINCT customer_id
INTO l_customer_id
FROM ar_payment_schedules
WHERE trx_number = p_search_number
AND class = (CASE p_search_type
WHEN 'INVOICES' THEN 'INV'
WHEN 'PAYMENTS' THEN 'PMT'
WHEN 'DEBIT_MEMOS' THEN 'DM'
WHEN 'CREDIT_MEMOS' THEN 'CM'
WHEN 'DEPOSITS' THEN 'DEP'
END);
SELECT account_number
INTO l_account_number
FROM hz_cust_accounts
where cust_account_id = l_customer_id;