1 PACKAGE BODY ARI_SELF_REG_CONFIG AS
2 /* $Header: ARISRCGB.pls 120.7 2006/07/12 12:17:25 rsinthre noship $ */
3
4 /*========================================================================
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 |
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
39 CURSOR cust_site_cur IS
40 select organization_id org_id from hr_operating_units where mo_global.check_access(organization_id) = 'Y';
41
42 cust_site_rec cust_site_cur%ROWTYPE;
43 BEGIN
44
45 /*
46 Customize this portion to specify the parameters for different
47 customers/customer sites
48 */
49 --At present, the registration process works with 3 attempts.
50 --This hook is for future enhancements
51 x_attempts := 0;
52
53 cust_site_org_id := NULL ;
54 if(p_customer_site_use_id is not null) then
55 SELECT org_id INTO cust_site_org_id
56 FROM hz_cust_site_uses
57 WHERE site_use_id = p_customer_site_use_id ;
58 else
59 --check if rule exist for the default org id.
60 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
61 cust_site_org_id := mo_utils.get_default_org_id;
62 else
63 --if rule does not exist for the cust_site_org or default org, then search thru all org setup at the security profile
64 FOR cust_site_rec IN cust_site_cur LOOP
65 if(FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_VALIDATE_SITE_ACCESS', NULL,cust_site_rec.org_id)) then
66 --site access question is set at the org id in secuirty profile, then set that org as cust_site_org_id
67 cust_site_org_id := cust_site_rec.org_id;
68 EXIT;
69 end if;
70 END LOOP;
71 end if;
72 end if;
73
74 IF(FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_VALIDATE_SITE_ACCESS', NULL,cust_site_org_id)) THEN
75
76 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_VALIDATE_SITE_ACCESS', 'AR', NULL, cust_site_org_id );
77 FUN_RULE_PUB.init_parameter_list;
78 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID', p_customer_id);
79 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID', p_customer_site_use_id);
80 FUN_RULE_PUB.apply_rule('AR','ARI_VALIDATE_SITE_ACCESS');
81 IF (FUN_RULE_PUB.get_attribute1 is not null) THEN
82 x_verify_access(1).question := FUN_RULE_PUB.get_attribute1;
83 x_verify_access(1).expected_answer := FUN_RULE_PUB.get_attribute2;
84 x_verify_access(1).currency_code := 'USD';
85 return;
86 END IF;
87 IF( FUN_RULE_PUB.get_attribute3 = 'Y' OR
88 FUN_RULE_PUB.get_attribute5 = 'Y') THEN
89 BEGIN
90 --Select an open invoice
91 SELECT trx_number
92 INTO l_trx_number
93 FROM ar_payment_schedules
94 WHERE customer_id = p_customer_id
95 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
96 AND status like 'OP'
97 AND invoice_currency_code = 'USD'
98 AND amount_due_remaining > 0
99 AND rownum = 1;
100
101 --Use the existing message as below, or define a new message
102 --and set that as the question
103 FND_MESSAGE.SET_NAME('AR', 'ARI_REG_VERIFY_QUESTION');
104 FND_MESSAGE.SET_TOKEN('INVOICE', l_trx_number);
105 --Set this as first question
106 x_verify_access(1).question := FND_MESSAGE.get;
107 BEGIN
108 --Set the expected answer
109 SELECT to_char(amount_due_remaining)
110 INTO x_verify_access(1).expected_answer
111 FROM ar_payment_schedules
112 WHERE customer_id = p_customer_id
113 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
114 AND status like 'OP'
115 AND invoice_currency_code = 'USD'
116 AND amount_due_remaining > 0
117 AND rownum = 1;
118 --Set the currency code
119 x_verify_access(1).currency_code := 'USD';
120 END;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 l_no_inv_flag:=TRUE;
124 END;
125 END IF;
126 --Questions can be defined on payment too
127 IF ( FUN_RULE_PUB.get_attribute4 = 'Y' OR
128 FUN_RULE_PUB.get_attribute5 = 'Y' ) THEN
129 BEGIN
130 --Select a receipt at this customer site
131 SELECT receipt_number
132 INTO l_receipt_number
133 FROM ar_cash_receipts
134 WHERE PAY_FROM_CUSTOMER = p_customer_id
135 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
136 AND currency_code = 'USD'
137 AND rownum = 1;
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 RETURN;
141 END;
142 --Set this as another question in the 'x_verify_access' var
143 FND_MESSAGE.SET_NAME('AR', 'ARI_REG_VERIFY_ADDL_QUESTION');
144 FND_MESSAGE.SET_TOKEN('RECEIPT', l_receipt_number);
145 IF ( l_no_inv_flag OR
146 (FUN_RULE_PUB.get_attribute3 is null AND FUN_RULE_PUB.get_attribute5 is null) ) THEN
147 x_verify_access(1).question := FND_MESSAGE.get;
148 ELSE
149 x_verify_access(2).question := FND_MESSAGE.get;
150 END IF;
151
152
153 BEGIN
154 --Set the expected answer
155 SELECT amount
156 INTO l_temp
157 FROM ar_cash_receipts
158 WHERE PAY_FROM_CUSTOMER = p_customer_id
159 AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
160 AND currency_code = 'USD'
161 AND rownum = 1;
162
163 IF ( l_no_inv_flag OR
164 (FUN_RULE_PUB.get_attribute3 is null AND FUN_RULE_PUB.get_attribute5 is null)) THEN
165 x_verify_access(1).expected_answer := l_temp;
166 x_verify_access(1).currency_code := 'USD';
167 ELSE
168 x_verify_access(2).expected_answer := l_temp;
169 x_verify_access(2).currency_code := 'USD';
170 END IF;
171 END;
172
173 END IF;
174 END IF;
175
176
177 END verify_customer_site_access;
178
179
180
181
182
183
184
185 /*========================================================================
186 | PUBLIC PROCEDURE validate_cust_detail_access
187 |
188 | DESCRIPTION
189 | This procedure can be customised to specify access verification questions
190 | when the user selects the customer requesting access.
191 |
192 | PARAMETERS
193 | p_customer_id IN VARCHAR2
194 | x_verify_access OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
195 | x_attempts OUT NUMBER
196 |
197 | NOTES
198 | Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
199 | question VARCHAR2(2000)
200 | expected_answer VARCHAR2(2000)
201 | currency_code VARCHAR2(15)
202 |
203 | MODIFICATION HISTORY
204 | Date Author Description of Changes
205 | 09-May-2005 vnb Created
206 |
207 *=======================================================================*/
208 PROCEDURE validate_cust_detail_access( p_customer_id IN VARCHAR2,
209 x_verify_access OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
210 x_attempts OUT NOCOPY NUMBER)
211 IS
212 l_customer_name hz_parties.party_name%type;
213 BEGIN
214 /*
215 Customize this portion to specify the parameters for different
216 customers
217 */
218 /*--------------------------------------------------------------------------
219 Sample Code
220 --------------------------------------------------------------------------*/
221 FUN_RULE_PUB.init_parameter_list;
222 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID', to_char(p_customer_id));
223 FUN_RULE_PUB.apply_rule('AR','ARI_VALIDATE_CUST_ACCESS');
224 IF (FUN_RULE_PUB.get_attribute1 is not null) THEN
225 x_verify_access(1).question := FUN_RULE_PUB.get_attribute1;
226 x_verify_access(1).expected_answer := FUN_RULE_PUB.get_attribute2;
227 x_verify_access(1).currency_code := 'USD';
228 return;
229 END IF;
230
231 /* IF p_customer_id = 1006 THEN
232
233 --Find Customer Name
234 select party_name
235 into l_customer_name
236 from hz_parties party, hz_cust_accounts cust
237 where cust.cust_account_id = p_customer_id
238 and cust.party_id = party.party_id;
239
240 --Use existing message/define new nessage for the question.
241 FND_MESSAGE.SET_NAME('AR', 'ARI_REG_VALIDATE_ACCT_ACCESS');
242 FND_MESSAGE.SET_TOKEN('ORGNAME', l_customer_name);
243 x_verify_access(1).question := FND_MESSAGE.get;
244
245 --Set expected answer here
246 x_verify_access(1).expected_answer := 'CSR';
247 x_verify_access(1).currency_code := 'USD';
248
249 --At present, the registration process works with 3 attempts.
250 --This hook is for future enhancements
251 x_attempts := 3;
252
253 END IF; */
254 /*--------------------------------------------------------------------------
255 End of sample Code
256 --------------------------------------------------------------------------*/
257 END validate_cust_detail_access;
258
259 /*========================================================================
260 | PUBLIC FUNCTION get_customer_id
261 |
262 | DESCRIPTION
263 | This function returns the customer id of the customer that the user requests access to.
264 | This can be customised to return the customer id in case of custom search queries.
265 |
266 | PARAMETERS
267 | p_search_type IN VARCHAR2
268 | p_search_number IN VARCHAR2
269 |
270 | NOTES
271 | This hook is kept as customisable for future enhancements
272 | in the direction of writing custom customer search for the registration process.
273 |
274 | MODIFICATION HISTORY
275 | Date Author Description of Changes
276 | 09-May-2005 vnb Created
277 |
278 *=======================================================================*/
279 FUNCTION get_customer_id ( p_search_type VARCHAR2,
280 p_search_number VARCHAR2) RETURN NUMBER
281 IS
282 l_customer_id NUMBER;
283 BEGIN
284
285 --If user searches by customer, get customer id from hz_cust_accounts
286 IF p_search_type = 'CUSTOMER_NUMBER' THEN
287 SELECT cust_account_id
288 INTO l_customer_id
289 FROM hz_cust_accounts
290 where account_number = p_search_number;
291
292 --If user searches by transactions, get customer id from ar_payment_schedules
293 ELSIF p_search_type = 'INVOICES' OR
294 p_search_type = 'PAYMENTS' OR
295 p_search_type = 'DEBIT_MEMOS' OR
296 p_search_type = 'CREDIT_MEMOS' OR
297 p_search_type = 'DEPOSITS' THEN
298
299 BEGIN
300 /* Fix for bug# 5153874.
301 * A customer can have same invoice no for more than invoices depending on
302 * the source. So added 'distinct' in the below query to select unique customer_id.
303 */
304 SELECT DISTINCT customer_id
305 INTO l_customer_id
306 FROM ar_payment_schedules
307 WHERE trx_number = p_search_number
308 AND class = (CASE p_search_type
309 WHEN 'INVOICES' THEN 'INV'
310 WHEN 'PAYMENTS' THEN 'PMT'
311 WHEN 'DEBIT_MEMOS' THEN 'DM'
312 WHEN 'CREDIT_MEMOS' THEN 'CM'
313 WHEN 'DEPOSITS' THEN 'DEP'
314 END);
315
316 EXCEPTION
317 WHEN TOO_MANY_ROWS THEN
318 RETURN -1;
319 END;
320
321 END IF;
322
323 RETURN l_customer_id;
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 RETURN -2;
327 END get_customer_id;
328
329 /*========================================================================
330 | PUBLIC FUNCTION auto_generate_passwd_option
331 |
332 | DESCRIPTION
333 | This function can be customised to specify if the password is to automatically generated
334 | at customer/site level.
335 |
336 | PARAMETERS
337 | p_customer_id IN VARCHAR2
338 | p_customer_site_use_id IN VARCHAR2
339 |
340 | NOTES
341 |
342 | MODIFICATION HISTORY
343 | Date Author Description of Changes
344 | 09-May-2005 vnb Created
345 |
346 *=======================================================================*/
347 FUNCTION auto_generate_passwd_option ( p_customer_id IN VARCHAR2,
348 p_customer_site_use_id IN VARCHAR2)
349 RETURN VARCHAR2
350 IS
351 BEGIN
352 /*IF p_customer_id = 1006 THEN
353 RETURN 'Y';
354 END IF;*/
355 RETURN 'N';
356 END auto_generate_passwd_option;
357
358
359
360 END ari_self_reg_config;
[Home] [Help]