4 /*========================================================================
1 PACKAGE BODY ARI_SELF_REG_CONFIG AS
2 /* $Header: ARISRCGB.pls 120.16 2012/01/10 10:26:43 parln ship $ */
3
5 | PUBLIC PROCEDURE verify_customer_site_access
6 |
7 | DESCRIPTION
8 | This procedure can be customised to specify access verification questions
9 | when the user selects the location of the customer requesting access.
10 |
11 | PARAMETERS
12 | p_customer_id IN VARCHAR2
13 | p_customer_site_use_id IN VARCHAR2 DEFAULT NULL
14 | x_verify_access OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
15 | x_attempts OUT NUMBER
16 |
17 | NOTES
18 | Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
19 | question VARCHAR2(2000)
20 | expected_answer VARCHAR2(2000)
21 | currency_code VARCHAR2(15)
22 |
23 | MODIFICATION HISTORY
24 | Date Author Description of Changes
25 | 09-May-2005 vnb Created
26 | 22-Mar-2010 avepati bug 7713325 - FLEXIBILITY TO DEFINE CHALLENGE QUESTION DURING SELF REGISTRION
27 *=======================================================================*/
28 PROCEDURE verify_customer_site_access( p_customer_id IN VARCHAR2,
29 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL,
30 x_verify_access OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
31 x_attempts OUT NOCOPY NUMBER)
32 IS
33 l_trx_number VARCHAR2(255);
34 l_receipt_number VARCHAR2(255);
35 l_temp number;
36 l_no_inv_flag boolean:=FALSE;
37 cust_site_org_id NUMBER ;
38 l_answer varchar2(200);
39 i PLS_INTEGER := 1 ;
40
41 CURSOR cust_site_cur IS
42 select organization_id org_id from hr_operating_units where mo_global.check_access(organization_id) = 'Y';
43
44 cust_site_rec cust_site_cur%ROWTYPE;
45 BEGIN
46
47 arp_standard.debug(' customer site use id :: '||p_customer_site_use_id);
48 arp_standard.debug(' customer Id :: '||p_customer_id);
49 /*
50 Customize this portion to specify the parameters for different
51 customers/customer sites
52 */
53 --At present, the registration process works with 3 attempts.
54 --This hook is for future enhancements
55 x_attempts := 0;
56
57 cust_site_org_id := NULL ;
58 if(p_customer_site_use_id is not null) then
59 SELECT org_id INTO cust_site_org_id
60 FROM hz_cust_site_uses
61 WHERE site_use_id = p_customer_site_use_id ;
62 else
63 -- added below lines to avoid error when navigated from preferences->Access Request -> additional access for new Oracle apps user who doesn't have iRec access.
64 mo_global.init('AR');
65 mo_global.set_policy_context('M',null);
66
67 --check if rule exist for the default org id.
68 if(mo_utils.get_default_org_id is NOT NULL and FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_VALIDATE_SITE_ACCESS', NULL,mo_utils.get_default_org_id)) then
69 cust_site_org_id := mo_utils.get_default_org_id;
70 else
71 --if rule does not exist for the cust_site_org or default org, then search thru all org setup at the security profile
72 FOR cust_site_rec IN cust_site_cur LOOP
73 if(FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_VALIDATE_SITE_ACCESS', NULL,cust_site_rec.org_id)) then
74 --site access question is set at the org id in secuirty profile, then set that org as cust_site_org_id
75 cust_site_org_id := cust_site_rec.org_id;
76 EXIT;
77 end if;
78 END LOOP;
79 end if;
80 end if;
81
82 IF(FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_VALIDATE_SITE_ACCESS', NULL,cust_site_org_id)) THEN
83
84 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_VALIDATE_SITE_ACCESS', 'AR', NULL, cust_site_org_id );
85 FUN_RULE_PUB.init_parameter_list;
86 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID', p_customer_id);
87 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID', p_customer_site_use_id);
88 FUN_RULE_PUB.apply_rule('AR','ARI_VALIDATE_SITE_ACCESS');
89
90 -- begin added for bug 7713325
91
92 IF (FUN_RULE_PUB.get_attribute15 is not null) THEN
93
94 arp_standard.debug(' question1 - attribute15 :: '||FUN_RULE_PUB.get_attribute15||' answer table1 - attribute14 :: '||FUN_RULE_PUB.get_attribute14||' answer column1 - attribute13 :: '||FUN_RULE_PUB.get_attribute13);
95 arp_standard.debug('answer join column1 - attribute12 :: '||FUN_RULE_PUB.get_attribute12||' hz party sites join column1 - attribute11 :: '||FUN_RULE_PUB.get_attribute11);
96
100
97 l_answer := validate_access(p_customer_id,p_customer_site_use_id,FUN_RULE_PUB.get_attribute14,FUN_RULE_PUB.get_attribute13,FUN_RULE_PUB.get_attribute12,FUN_RULE_PUB.get_attribute11);
98
99 arp_standard.debug('l_answer :: '||l_answer);
101 x_verify_access(i).question := FUN_RULE_PUB.get_attribute15;
102 x_verify_access(i).expected_answer := l_answer;
103
104 arp_standard.debug('Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
105
106 i := i+1;
107
108 END IF;
109
110 IF (FUN_RULE_PUB.get_attribute10 is not null and FUN_RULE_PUB.get_attribute6 is not null) THEN -- to confirm that 2nd custom question is defined
111
112 arp_standard.debug('question2 - attribute10 :: '||FUN_RULE_PUB.get_attribute10||' answer table2 - attribute9 :: '||FUN_RULE_PUB.get_attribute9||' answer column2 - attribute8 :: '||FUN_RULE_PUB.get_attribute8);
113 arp_standard.debug(' answer join column2 - attribute7 :: '||FUN_RULE_PUB.get_attribute7||' hz join column2 - attribute6 :: '||FUN_RULE_PUB.get_attribute6);
114
115 l_answer := validate_access(p_customer_id,p_customer_site_use_id,FUN_RULE_PUB.get_attribute9,FUN_RULE_PUB.get_attribute8,FUN_RULE_PUB.get_attribute7,FUN_RULE_PUB.get_attribute6);
116
117 x_verify_access(i).question := FUN_RULE_PUB.get_attribute10;
118 x_verify_access(i).expected_answer := l_answer;
119
120 arp_standard.debug('Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
121
122 i := i+1;
123
124 END IF; --IF (FUN_RULE_PUB.get_attribute6 is not null) THEN
125
126 IF (FUN_RULE_PUB.get_attribute5 is not null and FUN_RULE_PUB.get_attribute1 is not null) THEN -- to confirm that 3 rd custom question is defined
127
128 arp_standard.debug('question3 - attribute5 :: '||FUN_RULE_PUB.get_attribute5||' answer table3 - attribute4 :: '||FUN_RULE_PUB.get_attribute4||' answer column3 - attribute3 :: '||FUN_RULE_PUB.get_attribute3);
132 x_verify_access(i).question := FUN_RULE_PUB.get_attribute5;
129 arp_standard.debug(' answer join column3 - attribute2 :: '||FUN_RULE_PUB.get_attribute2||' hz join column3 - attribute1 :: '||FUN_RULE_PUB.get_attribute1);
130 l_answer := validate_access(p_customer_id,p_customer_site_use_id,FUN_RULE_PUB.get_attribute4,FUN_RULE_PUB.get_attribute3,FUN_RULE_PUB.get_attribute2,FUN_RULE_PUB.get_attribute1);
131
133 x_verify_access(i).expected_answer := l_answer;
137 i := i+1;
134
135 arp_standard.debug('Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
136
138
139 END IF; --IF (FUN_RULE_PUB.get_attribute11 is not null) THEN
140
141 -- end for bug 7713325
142
143 IF (FUN_RULE_PUB.get_attribute1 is not null and FUN_RULE_PUB.get_attribute5 is null) THEN
144 x_verify_access(i).question := FUN_RULE_PUB.get_attribute1;
145 x_verify_access(i).expected_answer := FUN_RULE_PUB.get_attribute2;
146 i := i+1;
147 return;
148 END IF;
149
150 IF( FUN_RULE_PUB.get_attribute3 = 'Y' OR
151 FUN_RULE_PUB.get_attribute5 = 'Y') THEN
152 BEGIN
153 --Select an open invoice
154 SELECT trx_number
155 INTO l_trx_number
156 FROM ar_payment_schedules
157 WHERE customer_id = p_customer_id
158 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
159 AND status like 'OP'
160 AND amount_due_remaining > 0
161 AND rownum = 1;
162
163 --Use the existing message as below, or define a new message
164 --and set that as the question
165 FND_MESSAGE.SET_NAME('AR', 'ARI_REG_VERIFY_QUESTION');
166 FND_MESSAGE.SET_TOKEN('INVOICE', l_trx_number);
167 --Set this as first question
168 x_verify_access(i).question := FND_MESSAGE.get;
169 BEGIN
170 --Set the expected answer
171 SELECT to_char(amount_due_remaining)
172 INTO x_verify_access(i).expected_answer
173 FROM ar_payment_schedules
174 WHERE customer_id = p_customer_id
175 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
176 AND status like 'OP'
177 AND amount_due_remaining > 0
178 AND rownum = 1;
179 END;
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 l_no_inv_flag:=TRUE;
183 END;
184 i := i+1;
185 END IF;
186 --Questions can be defined on payment too
187 IF ( FUN_RULE_PUB.get_attribute4 = 'Y' OR
188 FUN_RULE_PUB.get_attribute5 = 'Y' ) THEN
189 BEGIN
190 --Select a receipt at this customer site
191 SELECT receipt_number
192 INTO l_receipt_number
193 FROM ar_cash_receipts
194 WHERE PAY_FROM_CUSTOMER = p_customer_id
195 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
196 AND rownum = 1;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 RETURN;
200 END;
201 --Set this as another question in the 'x_verify_access' var
202 FND_MESSAGE.SET_NAME('AR', 'ARI_REG_VERIFY_ADDL_QUESTION');
203 FND_MESSAGE.SET_TOKEN('RECEIPT', l_receipt_number);
204 IF ( l_no_inv_flag OR
205 (FUN_RULE_PUB.get_attribute3 is null AND FUN_RULE_PUB.get_attribute5 is null) ) THEN
206 x_verify_access(i).question := FND_MESSAGE.get;
207 ELSE
208 x_verify_access(i).question := FND_MESSAGE.get;
209 END IF;
210
211
212 BEGIN
213 --Set the expected answer
214 SELECT amount
215 INTO l_temp
216 FROM ar_cash_receipts
217 WHERE PAY_FROM_CUSTOMER = p_customer_id
218 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
219 AND rownum = 1;
220
221 IF ( l_no_inv_flag OR
222 (FUN_RULE_PUB.get_attribute3 is null AND FUN_RULE_PUB.get_attribute5 is null)) THEN
223 x_verify_access(i).expected_answer := l_temp;
224 ELSE
225 x_verify_access(i).expected_answer := l_temp;
226 END IF;
227 -- Added for Bug# 13506129
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 RETURN;
231 END;
232
233 END IF;
234 i := i+1;
235 END IF;
236
237
238 END verify_customer_site_access;
239
240
241
242 /*========================================================================
243 | PUBLIC PROCEDURE validate_cust_detail_access
244 |
245 | DESCRIPTION
246 | This procedure can be customised to specify access verification questions
247 | when the user selects the customer requesting access.
248 |
249 | PARAMETERS
250 | p_customer_id IN VARCHAR2
251 | x_verify_access OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
252 | x_attempts OUT NUMBER
253 |
254 | NOTES
255 | Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
256 | question VARCHAR2(2000)
257 | expected_answer VARCHAR2(2000)
258 | currency_code VARCHAR2(15)
259 |
260 | MODIFICATION HISTORY
261 | Date Author Description of Changes
262 | 09-May-2005 vnb Created
263 | 19-Mar-2010 avepati bug 7713325 - FLEXIBILITY TO DEFINE CHALLENGE QUESTION DURING SELF REGISTRION
267 x_attempts OUT NOCOPY NUMBER)
264 *=======================================================================*/
265 PROCEDURE validate_cust_detail_access( p_customer_id IN VARCHAR2,
266 x_verify_access OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
268 IS
269 l_customer_name hz_parties.party_name%type;
270 l_answer varchar2(200);
271 i PLS_INTEGER := 1 ;
272
273 BEGIN
274
275 /*
276 Customize this portion to specify the parameters for different
277 customers
278 */
282 FUN_RULE_PUB.apply_rule('AR','ARI_VALIDATE_CUST_ACCESS');
279 arp_standard.debug('Begin procedure validate_cust_detail_access ');
280 FUN_RULE_PUB.init_parameter_list;
281 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID', to_char(p_customer_id));
283
284 BEGIN
285
286 IF (FUN_RULE_PUB.get_attribute1 is not null) THEN
287 IF (FUN_RULE_PUB.get_attribute5 is not null) THEN -- to confirm that 1 st custom question is defined
288
289 arp_standard.debug(' question1 - attribute1 :: '||FUN_RULE_PUB.get_attribute1||' answer table1 - attribute2 '||FUN_RULE_PUB.get_attribute2||' answer column1 - attribute3 '||FUN_RULE_PUB.get_attribute3);
290 arp_standard.debug(' answer join column1 - attribute4 '||FUN_RULE_PUB.get_attribute4||' hz join column1 - attribute5 '||FUN_RULE_PUB.get_attribute5);
291
292 l_answer := validate_access(p_customer_id,'CUST_LEVEL',FUN_RULE_PUB.get_attribute2,FUN_RULE_PUB.get_attribute3,FUN_RULE_PUB.get_attribute4,FUN_RULE_PUB.get_attribute5);
293 arp_standard.debug('l_answer :: '||l_answer);
294
295 elsif (FUN_RULE_PUB.get_attribute2 is not null) then -- IF (FUN_RULE_PUB.get_attribute5 is not null) THEN
296 l_answer := FUN_RULE_PUB.get_attribute2;
297 arp_standard.debug('l_answer for static question '||l_answer);
298 END IF; --IF (FUN_RULE_PUB.get_attribute5 is not null) THEN
299
300 x_verify_access(i).question := FUN_RULE_PUB.get_attribute1;
301 x_verify_access(i).expected_answer := l_answer;
302
303 arp_standard.debug('Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
304 i := i+1;
305
306 END IF; --IF (FUN_RULE_PUB.get_attribute1 is not null) THEN
307
308 IF (FUN_RULE_PUB.get_attribute6 is not null and FUN_RULE_PUB.get_attribute10 is not null) THEN -- to confirm that 2nd custom question is defined
309
310 arp_standard.debug('question2 - attribute6 :: '||FUN_RULE_PUB.get_attribute6||' answer table2 - attribute7 '||FUN_RULE_PUB.get_attribute7||' answer column2 - attribute8 '||FUN_RULE_PUB.get_attribute8);
311 arp_standard.debug('answer join column2 - attribute9 '||FUN_RULE_PUB.get_attribute9||' hz join column2 - attribute10 '||FUN_RULE_PUB.get_attribute10);
312
313 l_answer := validate_access(p_customer_id,'CUST_LEVEL',FUN_RULE_PUB.get_attribute7,FUN_RULE_PUB.get_attribute8,FUN_RULE_PUB.get_attribute9,FUN_RULE_PUB.get_attribute10);
314
315 x_verify_access(i).question := FUN_RULE_PUB.get_attribute6;
316 x_verify_access(i).expected_answer := l_answer;
317
318 arp_standard.debug('Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
319
320 i := i+1;
321
322 END IF; --IF (FUN_RULE_PUB.get_attribute6 is not null) THEN
323
324
325 IF (FUN_RULE_PUB.get_attribute11 is not null and FUN_RULE_PUB.get_attribute15 is not null) THEN -- to confirm that 3 rd custom question is defined
326
327 arp_standard.debug('question3 - attribute11 :: '||FUN_RULE_PUB.get_attribute11||' answer table3 - attribute12 '||FUN_RULE_PUB.get_attribute12||' answer column3 - attribute13 '||FUN_RULE_PUB.get_attribute13);
328 arp_standard.debug(' answer join column3 - attribute14 '||FUN_RULE_PUB.get_attribute14||' hz join column3 - attribute15 '||FUN_RULE_PUB.get_attribute15);
329
330 l_answer := validate_access(p_customer_id,'CUST_LEVEL',FUN_RULE_PUB.get_attribute12,FUN_RULE_PUB.get_attribute13,FUN_RULE_PUB.get_attribute14,FUN_RULE_PUB.get_attribute15);
331
332 x_verify_access(i).question := FUN_RULE_PUB.get_attribute11;
333 x_verify_access(i).expected_answer := l_answer;
334
335 arp_standard.debug('Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
336
337 i := i+1;
338
339 END IF; --IF (FUN_RULE_PUB.get_attribute11 is not null) THEN
340 --Modified for Bug# 13506129
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 arp_standard.debug('In Exception.. Expected Answer.. '||x_verify_access(i).expected_answer||'..Question'||x_verify_access(i).question);
344 arp_standard.debug('ERROR => :: '||SQLERRM);
348
345 l_answer := NULL;
346 RETURN;
347 END;
349 END validate_cust_detail_access;
350
351
352 /*========================================================================
353 | PUBLIC FUNCTION get_customer_id
354 |
355 | DESCRIPTION
356 | This function returns the customer id of the customer that the user requests access to.
357 | This can be customised to return the customer id in case of custom search queries.
358 |
359 | PARAMETERS
360 | p_search_type IN VARCHAR2
361 | p_search_number IN VARCHAR2
362 |
363 | NOTES
364 | This hook is kept as customisable for future enhancements
365 | in the direction of writing custom customer search for the registration process.
366 |
367 | MODIFICATION HISTORY
368 | Date Author Description of Changes
369 | 09-May-2005 vnb Created
370 |
371 *=======================================================================*/
372 FUNCTION get_customer_id ( p_search_type VARCHAR2,
373 p_search_number VARCHAR2) RETURN NUMBER
374 IS
375 l_customer_id NUMBER;
376 BEGIN
377
378 --If user searches by customer, get customer id from hz_cust_accounts
379 IF p_search_type = 'CUSTOMER_NUMBER' THEN
380 SELECT cust_account_id
381 INTO l_customer_id
382 FROM hz_cust_accounts
383 where account_number = p_search_number;
384
385 --If user searches by transactions, get customer id from ar_payment_schedules
386 ELSIF p_search_type = 'INVOICES' OR
387 p_search_type = 'PAYMENTS' OR
388 p_search_type = 'DEBIT_MEMOS' OR
389 p_search_type = 'CREDIT_MEMOS' OR
390 p_search_type = 'DEPOSITS' THEN
391
392 BEGIN
393 /* Fix for bug# 5153874.
394 * A customer can have same invoice no for more than invoices depending on
395 * the source. So added 'distinct' in the below query to select unique customer_id.
396 */
397 SELECT DISTINCT customer_id
398 INTO l_customer_id
399 FROM ar_payment_schedules
400 WHERE trx_number = p_search_number
401 AND class = (CASE p_search_type
402 WHEN 'INVOICES' THEN 'INV'
403 WHEN 'PAYMENTS' THEN 'PMT'
404 WHEN 'DEBIT_MEMOS' THEN 'DM'
405 WHEN 'CREDIT_MEMOS' THEN 'CM'
406 WHEN 'DEPOSITS' THEN 'DEP'
407 END);
408
412 END;
409 EXCEPTION
410 WHEN TOO_MANY_ROWS THEN
411 RETURN -1;
413
414 END IF;
415
416 RETURN l_customer_id;
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN
419 RETURN -2;
420 END get_customer_id;
421
422 /*========================================================================
423 | PUBLIC FUNCTION auto_generate_passwd_option
424 |
425 | DESCRIPTION
426 | This function can be customised to specify if the password is to automatically generated
427 | at customer/site level.
428 |
429 | PARAMETERS
430 | p_customer_id IN VARCHAR2
431 | p_customer_site_use_id IN VARCHAR2
432 |
433 | NOTES
434 |
435 | MODIFICATION HISTORY
436 | Date Author Description of Changes
437 | 09-May-2005 vnb Created
438 |
439 *=======================================================================*/
440 FUNCTION auto_generate_passwd_option ( p_customer_id IN VARCHAR2,
441 p_customer_site_use_id IN VARCHAR2)
442 RETURN VARCHAR2
443 IS
444 BEGIN
445 /*IF p_customer_id = 1006 THEN
446 RETURN 'Y';
447 END IF;*/
448 RETURN 'N';
449 END auto_generate_passwd_option;
450
451 /*========================================================================
452 | PUBLIC FUNCTION validate_access
453 |
454 | DESCRIPTION
455 | This function returns the self registration custom question answere
456 | defined at customer/site level.
457 |
458 | PARAMETERS
459 | p_customer_id IN VARCHAR2
460 | p_customer_site_use_id IN VARCHAR2
461 | p_answer_table IN VARCHAR2
462 | p_answer_column IN VARCHAR2
463 | p_answer_join_column IN VARCHAR2
464 | p_hz_join_column IN VARCHAR2
465 |
466 | NOTES
467 |
468 | MODIFICATION HISTORY
469 | Date Author Description of Changes
473 p_customer_site_use_id IN VARCHAR2,
470 | 18-Mar-2010 avepati Created
471 *=======================================================================*/
472 FUNCTION validate_access ( p_customer_id IN VARCHAR2,
474 p_answer_table IN VARCHAR2,
475 p_answer_column IN VARCHAR2,
476 p_answer_join_column IN VARCHAR2,
477 p_hz_join_column IN VARCHAR2 ) RETURN VARCHAR2 IS
478
479 l_answer varchar2(200);
480 l_query_string varchar2(2000);
481 l_site_use_id number(15);
482 TYPE l_ref_cur_type IS REF CURSOR;
483 l_ref_cur l_ref_cur_type;
484 BEGIN
485
486
487 if ( p_customer_id is not null and p_customer_site_use_id ='CUST_LEVEL') then
488
489 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||
490 ' and hca.cust_account_id = '||p_customer_id;
491 elsif ( p_customer_site_use_id is not null ) then
492
493 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 ||
494 ' 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;
495
496 else
497
498 mo_global.init('AR');
499 mo_global.set_policy_context('M',null);
500 arp_standard.debug('apps context initialized');
501
502 SELECT site_use_id into l_site_use_id FROM
503 ( SELECT site_uses.site_use_id FROM
504 fnd_territories_vl Terr,
505 hz_cust_acct_sites acct_sites,
506 hz_party_sites party_sites,
507 hz_locations loc,
508 hz_cust_accounts Cust,
509 hz_parties Party,
510 hz_cust_site_uses site_uses
511 WHERE Party.party_id = Cust.party_id
512 AND Cust.account_number = p_customer_id
513 AND Cust.cust_account_id = acct_sites.cust_account_id
514 AND ACCT_SITES.party_site_id = PARTY_SITES.party_site_id
515 AND PARTY_SITES.location_id = LOC.location_id
516 AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
517 AND site_uses.site_use_code = 'BILL_TO'
518 AND loc.country = Terr.territory_code(+)
519 order by site_uses.creation_date asc ) dummy
520 where rownum=1;
521
522 arp_standard.debug('oldest bill to site id :: '||l_site_use_id);
523
524 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 ||
525 ' 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;
526
527 end if;
528
529 arp_standard.debug('l_query_string :: '||l_query_string);
530
531 open l_ref_cur for l_query_string;
532 fetch l_ref_cur into l_answer;
533 close l_ref_cur;
534
535 arp_standard.debug('l_answer ::'||l_answer);
536
537 RETURN l_answer;
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 arp_standard.debug('Exception in validate_access');
542 arp_standard.debug('ERROR => :: '||SQLERRM);
543 l_answer := NULL;
544 return l_answer;
545
546 END validate_access;
547
548
549
550 PROCEDURE get_customer_id_number ( p_search_type IN VARCHAR2,
551 p_search_number IN VARCHAR2,
552 x_cust_account_id OUT NOCOPY NUMBER,
553 x_account_number OUT NOCOPY VARCHAR2)
557 BEGIN
554 IS
555 l_customer_id NUMBER;
556 l_account_number VARCHAR2(30);
558 --If user searches by customer, get customer id from hz_cust_accounts
559 arp_standard.debug('Enetered procedure get_customer_id_number..');
560 IF p_search_type = 'CUSTOMER_NUMBER' THEN
561 SELECT cust_account_id
562 INTO l_customer_id
563 FROM hz_cust_accounts
564 where account_number = p_search_number;
565
566 l_account_number := p_search_number;
567
568 --If user searches by transactions, get customer id from ar_payment_schedules
569 ELSIF p_search_type = 'INVOICES' OR
570 p_search_type = 'PAYMENTS' OR
571 p_search_type = 'DEBIT_MEMOS' OR
572 p_search_type = 'CREDIT_MEMOS' OR
573 p_search_type = 'DEPOSITS' THEN
574
575 BEGIN
576 /* Fix for bug# 5153874.
577 * A customer can have same invoice no for more than invoices depending on
578 * the source. So added 'distinct' in the below query to select unique customer_id.
579 */
580 SELECT DISTINCT customer_id
581 INTO l_customer_id
582 FROM ar_payment_schedules
583 WHERE trx_number = p_search_number
584 AND class = (CASE p_search_type
585 WHEN 'INVOICES' THEN 'INV'
586 WHEN 'PAYMENTS' THEN 'PMT'
587 WHEN 'DEBIT_MEMOS' THEN 'DM'
588 WHEN 'CREDIT_MEMOS' THEN 'CM'
589 WHEN 'DEPOSITS' THEN 'DEP'
590 END);
591
592 SELECT account_number
593 INTO l_account_number
594 FROM hz_cust_accounts
595 where cust_account_id = l_customer_id;
596
597 EXCEPTION
598 WHEN TOO_MANY_ROWS THEN
599 l_customer_id := -1;
600 END;
601
602 END IF;
603 x_account_number := l_account_number;
604 arp_standard.debug('x_account_number: '||x_account_number);
605 x_cust_account_id := l_customer_id;
606 arp_standard.debug('x_cust_account_id: '||x_cust_account_id);
607 arp_standard.debug('Exiting procedure get_customer_id_number..');
608 EXCEPTION
609 WHEN NO_DATA_FOUND THEN
610 x_cust_account_id := -2;
611
612 END get_customer_id_number;
613
614 END ari_self_reg_config;
[Home] [Help]