[Home] [Help]
PACKAGE BODY: APPS.ARI_SELF_REGISTRATION_PKG
Source
1 PACKAGE BODY ARI_SELF_REGISTRATION_PKG AS
2 /* $Header: ARISREGB.pls 120.24 2006/06/24 14:17:02 rsinthre noship $ */
3
4 /*=======================================================================+
5 | Global Constants
6 +=======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARI_SELF_REGISTRATION_PKG';
9 G_CREATED_BY_MODULE CONSTANT VARCHAR2(5) := 'ARI';
10
11 ------------------------------------------------------------------------
12 -- Procedure Result Codes
13 ------------------------------------------------------------------------
14 G_NO_ROWS CONSTANT NUMBER := 0;
15 G_EXACT_MATCH CONSTANT NUMBER := 1;
16 G_BOTH_FOUND CONSTANT NUMBER := 2;
17 G_PERSON_FOUND CONSTANT NUMBER := 3;
18 G_BUSINESS_PERSON_FOUND CONSTANT NUMBER := 4;
19 G_FUZZY_MATCH CONSTANT NUMBER := 5;
20 G_NOT_UNIQUE CONSTANT NUMBER := 6;
21 G_COMPANY_DIFF CONSTANT NUMBER := -1;
22 G_PERSON_DIFF CONSTANT NUMBER := -2;
23 G_INVALID_PARTY_ID CONSTANT NUMBER := -3;
24 G_PERSON_NO_SIBLING CONSTANT NUMBER := -4;
25 G_BUSINESS_PERSON_NO_SIBLING CONSTANT NUMBER := -5;
26 G_NO_MATCH CONSTANT NUMBER := -6;
27 G_PERSON_NOT_UNIQUE CONSTANT NUMBER := -7;
28 G_BUSINESS_PERSON_NOT_UNIQUE CONSTANT NUMBER := -8;
29 G_FIRST_DIFF CONSTANT NUMBER := -9;
30 G_FAMILY_DIFF CONSTANT NUMBER := -10;
31
32 --------------------------------------------------------------------------
33 -- Registration Status Constants
34 --------------------------------------------------------------------------
35 G_HOLD CONSTANT VARCHAR2(4) := 'HOLD';
36 G_REGISTERED CONSTANT VARCHAR2(10) := 'REGISTERED';
37 G_NEW CONSTANT VARCHAR2(3) := 'NEW';
38 G_NEW_ACCESS CONSTANT VARCHAR2(10) := 'NEW_ACCESS';
39 G_RETRY CONSTANT VARCHAR2(5) := 'RETRY';
40 G_NEW_ACCESS_RETRY CONSTANT VARCHAR2(20) := 'NEW_ACCESS_RETRY';
41 G_CREATE_USER_REQUESTED CONSTANT VARCHAR2(25) := 'CREATE_USER_REQUESTED';
42
43 --------------------------------------------------------------------------
44 -- Access Domain Type Constants
45 --------------------------------------------------------------------------
46 G_INVOICE_NUM CONSTANT VARCHAR2(15) := 'INVOICE_NUM';
47 G_CUST_ACCT_NUM CONSTANT VARCHAR2(15) := 'CUST_ACCT_NUM';
48
49 G_CUST_ACCT_HOLD CONSTANT VARCHAR2(15) := 'CUST_ACCT_HOLD';
50 G_USER_HOLD CONSTANT VARCHAR2(10) := 'USER_HOLD';
51 G_USER_REG_COUNT CONSTANT VARCHAR2(15) := 'USER_REG_COUNT';
52 G_CUST_ACCT_N_USER CONSTANT VARCHAR2(20) := 'CUST_ACCT_N_USER';
53
54 G_RECEIPT_DATE CONSTANT VARCHAR2(15) := 'RECEIPT_DATE';
55 G_RECEIPT_AMT CONSTANT VARCHAR2(15) := 'RECEIPT_AMT';
56
57 G_ORGANIZATION CONSTANT VARCHAR2(15) := 'ORGANIZATION';
58 G_PERSON CONSTANT VARCHAR2(10) := 'PERSON';
59
60 G_BUSINESS CONSTANT VARCHAR2(10) := 'BUSINESS';
61 G_CONSUMER CONSTANT VARCHAR2(10) := 'CONSUMER';
62
63 --------------------------------------------------------------------------
64 -- Local Procedure Signature
65 --------------------------------------------------------------------------
66 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
67
68 PROCEDURE InformSysAdminError(p_procedure_name IN VARCHAR2,
69 p_debug_info IN VARCHAR2,
70 p_error IN VARCHAR2);
71
72
73 /* =======================================================================
74 | PROCEDURE ResolveCustomerAccessRequest
75 |
76 | DESCRIPTION
77 |
78 |
79 | PARAMETERS
80 |
81 * ======================================================================*/
82 ---------------------------------------------------------------------------
83 PROCEDURE ResolveCustomerAccessRequest(p_customer_id IN VARCHAR2,
84 x_cust_acct_type OUT NOCOPY VARCHAR2,
85 x_result_code OUT NOCOPY NUMBER)
86 ---------------------------------------------------------------------------
87 IS
88 l_cust_acct_cur GenCursorRef;
89 l_cust_acct_id NUMBER;
90 l_cust_acct_number VARCHAR2(50);
91 l_party_id NUMBER;
92 l_party_type HZ_PARTIES.party_type%TYPE;
93 l_procedure_name VARCHAR2(30) := '.ResolveCustomerAccessRequest';
94 l_debug_info VARCHAR2(200);
95 BEGIN
96
97 --------------------------------------------------------------------
98 l_debug_info := 'In debug mode, log we have entered this procedure';
99 --------------------------------------------------------------------
100 IF (PG_DEBUG = 'Y') THEN
101 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
102 END IF;
103
104 --------------------------------------------------------------------------
105 l_debug_info := 'Calling OpenCustAcctCur';
106 ---------------------------------------------------------------------------
107 IF (PG_DEBUG = 'Y') THEN
108 arp_standard.debug(l_debug_info);
109 END IF;
110 OpenCustAcctCur(p_customer_id => p_customer_id,
111 p_cust_acct_cur => l_cust_acct_cur);
112
113 ------------------------------------------------------------------------
114 l_debug_info := 'Fetching the results of the cursor';
115 ------------------------------------------------------------------------
116 IF (PG_DEBUG = 'Y') THEN
117 arp_standard.debug(l_debug_info);
118 END IF;
119 ------------------------------------------------------------------------
120 LOOP
121 FETCH l_cust_acct_cur INTO l_cust_acct_id,
122 l_cust_acct_number,
123 l_party_id,
124 l_party_type;
125
126 EXIT WHEN l_cust_acct_cur%NOTFOUND;
127 END LOOP;
128
129 IF (l_cust_acct_cur%ROWCOUNT = 1) THEN
130 ----------------------------------------------------------------
131 l_debug_info := 'Exact match on customer_id provided';
132 ----------------------------------------------------------------
133 IF (PG_DEBUG = 'Y') THEN
134 arp_standard.debug(l_debug_info);
135 END IF;
136
137 x_result_code := G_EXACT_MATCH;
138
139 IF (l_party_type = G_ORGANIZATION) THEN
140 x_cust_acct_type := G_BUSINESS;
141 ELSE
142 x_cust_acct_type := G_CONSUMER;
143 END IF;
144
145 ELSIF (l_cust_acct_cur%ROWCOUNT > 1) THEN
146 ----------------------------------------------------------------------
147 l_debug_info := 'Non-unique match on customer_id provided';
148 ----------------------------------------------------------------------
149 IF (PG_DEBUG = 'Y') THEN
150 arp_standard.debug(l_debug_info);
151 END IF;
152 x_result_code := G_NOT_UNIQUE;
153
154 ELSE
155 -------------------------------------------------------------------------
156 l_debug_info := 'No record found based on customer_id provided';
157 -------------------------------------------------------------------------
158 IF (PG_DEBUG = 'Y') THEN
159 arp_standard.debug(l_debug_info);
160 END IF;
161 x_result_code := G_NO_ROWS;
162
163 END IF;
164
165 ------------------------------------------------------------------------
166 l_debug_info := 'Close Cust Account Cursor';
167 ------------------------------------------------------------------------
168 IF (PG_DEBUG = 'Y') THEN
169 arp_standard.debug(l_debug_info);
170 END IF;
171 CLOSE l_cust_acct_cur;
172
173 --------------------------------------------------------------------
174 l_debug_info := 'In debug mode, log we have exited this procedure';
175 --------------------------------------------------------------------
176 IF (PG_DEBUG = 'Y') THEN
177 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 IF (SQLCODE <> -20001) THEN
183 IF (PG_DEBUG = 'Y') THEN
184 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
185 arp_standard.debug('Debug Info: ' || l_debug_info);
186 arp_standard.debug(SQLERRM);
187 END IF;
188 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
189 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
190 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
191 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
192 FND_MSG_PUB.ADD;
193 InformSysAdminError(p_procedure_name => l_procedure_name,
194 p_debug_info => l_debug_info,
195 p_error => SQLERRM);
196 END IF;
197 APP_EXCEPTION.RAISE_EXCEPTION;
198 END ResolveCustomerAccessRequest;
199
200 /* =======================================================================
201 | PROCEDURE InitiateHZUserCreation
202 |
203 | DESCRIPTION
204 |
205 |
206 | PARAMETERS
207 |
208 * ======================================================================*/
209 ---------------------------------------------------------------------------
210 PROCEDURE InitiateHZUserCreation(p_registration_id IN NUMBER,
211 p_user_email_addr IN VARCHAR2,
212 p_cust_acct_type IN VARCHAR2,
213 p_company_id IN NUMBER DEFAULT NULL,
214 p_access_domain_id IN NUMBER,
215 p_access_domain_number IN VARCHAR2,
216 p_person_id IN NUMBER DEFAULT NULL,
217 p_first_name IN VARCHAR2 DEFAULT NULL,
218 p_family_name IN VARCHAR2 DEFAULT NULL,
219 p_job_title IN VARCHAR2 DEFAULT NULL,
220 p_phone_country_code IN VARCHAR2 DEFAULT NULL,
221 p_area_code IN VARCHAR2 DEFAULT NULL,
222 p_phone_number IN VARCHAR2 DEFAULT NULL,
223 p_extension IN VARCHAR2 DEFAULT NULL,
224 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
225 p_reg_service_code IN VARCHAR2 DEFAULT 'FND_RESP|AR|ARI_EXTERNAL|STAND',
226 p_identity_verification_reqd IN VARCHAR2 DEFAULT NULL,
227 p_requested_username IN VARCHAR2 DEFAULT NULL,
228 p_justification IN VARCHAR2 DEFAULT NULL,
229 p_req_start_date IN DATE DEFAULT SYSDATE,
230 p_req_end_date IN DATE DEFAULT NULL,
231 p_ame_application_id IN VARCHAR2 DEFAULT NULL,
232 p_ame_trx_type_id IN VARCHAR2 DEFAULT NULL,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2)
236 ---------------------------------------------------------------------------
237 IS
238 l_app_name FND_NEW_MESSAGES.message_text%TYPE;
239 l_umx_reg_data UMX_REGISTRATION_PVT.UMX_REGISTRATION_DATA_TBL;
240 l_reg_service_type VARCHAR2(30);
241 l_procedure_name VARCHAR2(30) := '.InitiateHZUserCreation';
242 l_debug_info VARCHAR2(500);
243 BEGIN
244
245 --------------------------------------------------------------------
246 l_debug_info := 'In debug mode, log we have entered this procedure';
247 --------------------------------------------------------------------
248 IF (PG_DEBUG = 'Y') THEN
249 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
250 END IF;
251
252 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
253 -----------------------------------------------------------------------
254 l_debug_info := 'Initialize message list if requested by calling api';
255 -----------------------------------------------------------------------
256 FND_MSG_PUB.initialize;
257 END IF;
258
259 ----------------------------------------------------------------------------
260 l_debug_info := 'Retrieve Application Name';
261 ----------------------------------------------------------------------------
262 FND_MESSAGE.Set_Name('AR', 'ARI_REG_APP_NAME');
263 l_app_name := FND_MESSAGE.Get;
264
265 ----------------------------------------------------------------------------
266 l_debug_info := 'Check if the registration service code passed in is valid';
267 ----------------------------------------------------------------------------
268 BEGIN
269 SELECT REGSRVC.REG_SERVICE_TYPE
270 INTO l_reg_service_type
271 FROM UMX_REG_SERVICES_VL REGSRVC
272 WHERE REGSRVC.REG_SERVICE_CODE = p_reg_service_code
273 AND REGSRVC.START_DATE <= SYSDATE
274 AND NVL(REGSRVC.END_DATE, SYSDATE+1) > SYSDATE;
275 EXCEPTION
276 WHEN OTHERS THEN
277 FND_MESSAGE.SET_NAME ('AR','ARI_REG_PROCESS_TYPE_ERROR');
278 FND_MESSAGE.SET_TOKEN('REG_PROCESS', p_reg_service_code);
279 FND_MSG_PUB.ADD;
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END;
282
283 --Reg Service Type should be SELF_SERVICE when calling from Collections
284 IF l_reg_service_type <> 'SELF_SERVICE' THEN
285 FND_MESSAGE.SET_NAME ('AR','ARI_REG_PROCESS_TYPE_ERROR');
286 FND_MESSAGE.SET_TOKEN('REG_PROCESS', p_reg_service_code);
287 FND_MSG_PUB.ADD;
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290
291 ----------------------------------------------------------------------------
292 l_debug_info := 'Set up UMX parameters';
293 ----------------------------------------------------------------------------
294 l_umx_reg_data(1).ATTR_NAME := UMX_REGISTRATION_PVT.G_REG_SERVICE_CODE;
295 l_umx_reg_data(1).ATTR_VALUE := p_reg_service_code;
296
297 l_umx_reg_data(2).ATTR_NAME := UMX_REGISTRATION_PVT.G_REG_SERVICE_TYPE;
298 l_umx_reg_data(2).ATTR_VALUE := 'SELF_SERVICE';
299
300 l_umx_reg_data(3).ATTR_NAME := UMX_REGISTRATION_PVT.G_REG_SERVICE_APP_ID;
301 l_umx_reg_data(3).ATTR_VALUE := '222';
302
303 l_umx_reg_data(4).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_USERNAME;
304 --IF the requested username is not passes, set the user name to email address
305 IF p_requested_username IS NOT NULL THEN
306 l_umx_reg_data(4).ATTR_VALUE := p_requested_username;
307 ELSE
308 l_umx_reg_data(4).ATTR_VALUE := p_user_email_addr;
309 END IF;
310
311 l_umx_reg_data(5).ATTR_NAME := UMX_REGISTRATION_PVT.G_IDENTITY_VERIFY_REQD;
312 l_umx_reg_data(5).ATTR_VALUE := p_identity_verification_reqd;
313
314 l_umx_reg_data(6).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_FOR_PARTY_ID;
315 l_umx_reg_data(6).ATTR_VALUE := p_person_id;
316
317 l_umx_reg_data(7).ATTR_NAME := 'CUST_ACCT_TYPE';
318 l_umx_reg_data(7).ATTR_VALUE := p_cust_acct_type;
319
320 l_umx_reg_data(8).ATTR_NAME := 'CUSTOMER_ID';
321 l_umx_reg_data(8).ATTR_VALUE := p_access_domain_id;
322
323 l_umx_reg_data(9).ATTR_NAME := UMX_REGISTRATION_PVT.G_JUSTIFICATION;
324 l_umx_reg_data(9).ATTR_VALUE := p_justification;
325
326 --NOTE: Date has to be in this format 'YYYY/MM/DD'
327 --THis is in sync with FND_DATE.canonical_to_date which is used to retrieve value from WF.
328 l_umx_reg_data(10).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_START_DATE;
329 l_umx_reg_data(10).ATTR_VALUE := to_char(p_req_start_date,'YYYY/MM/DD');
330
331 l_umx_reg_data(11).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_END_DATE;
332 l_umx_reg_data(11).ATTR_VALUE := to_char(p_req_end_date,'YYYY/MM/DD');
333
334 l_umx_reg_data(12).ATTR_NAME := UMX_REGISTRATION_PVT.G_AME_APPLICATION_ID;
335 l_umx_reg_data(12).ATTR_VALUE := p_ame_application_id;
336
337 l_umx_reg_data(13).ATTR_NAME := UMX_REGISTRATION_PVT.G_AME_TXN_TYPE_ID;
338 l_umx_reg_data(13).ATTR_VALUE := p_ame_trx_type_id;
339
340 l_umx_reg_data(14).ATTR_NAME := 'EMAIL_ADDRESS';
341 l_umx_reg_data(14).ATTR_VALUE := p_user_email_addr;
342
343 l_umx_reg_data(15).ATTR_NAME := 'FIRST_NAME';
344 l_umx_reg_data(15).ATTR_VALUE := p_first_name;
345
346 l_umx_reg_data(16).ATTR_NAME := 'LAST_NAME';
347 l_umx_reg_data(16).ATTR_VALUE := p_family_name;
348
349 l_umx_reg_data(17).ATTR_NAME := 'COUNTRY_CODE';
350 l_umx_reg_data(17).ATTR_VALUE := p_phone_country_code;
351
352 l_umx_reg_data(18).ATTR_NAME := 'AREA_CODE';
353 l_umx_reg_data(18).ATTR_VALUE := p_area_code;
354
355 l_umx_reg_data(19).ATTR_NAME := 'PRIMARY_PHONE';
356 l_umx_reg_data(19).ATTR_VALUE := p_phone_number;
357
358 l_umx_reg_data(20).ATTR_NAME := 'PHONE_EXTENSION';
359 l_umx_reg_data(20).ATTR_VALUE := p_extension;
360
361 ----------------------------------------------------------------------------
362 l_debug_info := 'Call UMX API to process registration request';
363 ----------------------------------------------------------------------------
364 UMX_REGISTRATION_PVT.UMX_PROCESS_REG_REQUEST(l_umx_reg_data);
365
366 ----------------------------------------------------------------------------
367 l_debug_info := 'In debug mode, log that we have exited this procedure';
368 ----------------------------------------------------------------------------
369 IF (PG_DEBUG = 'Y') THEN
370 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
371 END IF;
372
373 EXCEPTION
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
377 p_data => x_msg_data);
378 WHEN OTHERS THEN
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 IF (SQLCODE <> -20001) THEN
381 IF (PG_DEBUG = 'Y') THEN
382 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
383 arp_standard.debug('Debug Info: ' || l_debug_info);
384 arp_standard.debug(SQLERRM);
385 END IF;
386 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
387 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
388 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
389 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
390 FND_MSG_PUB.ADD;
391 InformSysAdminError(p_procedure_name => l_procedure_name,
392 p_debug_info => l_debug_info,
393 p_error => SQLERRM);
394 END IF;
395 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
396 p_data => x_msg_data);
397 END InitiateHZUserCreation;
398
399 /* =======================================================================
400 | PROCEDURE OpenCustAcctCur
401 |
402 | DESCRIPTION
403 |
404 |
405 | PARAMETERS
406 |
407 * ======================================================================*/
408 --------------------------------------------------------------------------
409 PROCEDURE OpenCustAcctCur(p_customer_id IN VARCHAR2,
410 p_cust_acct_cur OUT NOCOPY GenCursorRef)
411 --------------------------------------------------------------------------
412 IS
413 l_procedure_name VARCHAR2(30) := '.OpenCustAcctCur';
414 l_debug_info VARCHAR2(200);
415 BEGIN
416
417 --------------------------------------------------------------------
418 l_debug_info := 'In debug mode, log we have entered this procedure';
419 --------------------------------------------------------------------
420 IF (PG_DEBUG = 'Y') THEN
421 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
422 END IF;
423
424 --------------------------------------------------------------------
425 l_debug_info := 'Open p_cust_acct_cur';
426 --------------------------------------------------------------------
427 OPEN p_cust_acct_cur FOR
428 SELECT CustAcct.cust_account_id,
429 CustAcct.account_number,
430 CustAcct.party_id,
431 Party.party_type
432 FROM HZ_CUST_ACCOUNTS CustAcct,
433 HZ_PARTIES Party
434 WHERE CustAcct.cust_account_id = p_customer_id
435 AND CustAcct.party_id = Party.party_id;
436
437 --------------------------------------------------------------------
438 l_debug_info := 'In debug mode, log we have exited this procedure';
439 --------------------------------------------------------------------
440 IF (PG_DEBUG = 'Y') THEN
441 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
442 END IF;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 IF (SQLCODE <> -20001) THEN
447 IF (PG_DEBUG = 'Y') THEN
448 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
449 arp_standard.debug('Debug Info: ' || l_debug_info);
450 arp_standard.debug(SQLERRM);
451 END IF;
452 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
453 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
454 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
455 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
456 FND_MSG_PUB.ADD;
457 InformSysAdminError(p_procedure_name => l_procedure_name,
458 p_debug_info => l_debug_info,
459 p_error => SQLERRM);
460 END IF;
461 APP_EXCEPTION.RAISE_EXCEPTION;
462 END OpenCustAcctCur;
463
464 /* =======================================================================
465 | PROCEDURE InformSysAdminError
466 |
467 | DESCRIPTION
468 |
469 |
470 | PARAMETERS
471 |
472 * ======================================================================*/
473 --------------------------------------------------------------------------
474 PROCEDURE InformSysAdminError(p_procedure_name IN VARCHAR2,
475 p_debug_info IN VARCHAR2,
476 p_error IN VARCHAR2)
477 --------------------------------------------------------------------------
478 IS
479 l_pkg_name VARCHAR2(30) := G_PKG_NAME;
480 l_procedure_name VARCHAR2(30) := 'InformSysAdminError';
481 l_debug_info VARCHAR2(200);
482 BEGIN
483
484 --------------------------------------------------------------------
485 l_debug_info := 'Initiate Inform Sysadmin Workflow';
486 -------------------------------------------------------------------
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 IF (SQLCODE <> -20001) THEN
491 IF (PG_DEBUG = 'Y') THEN
492 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
493 arp_standard.debug('Debug Info: ' || l_debug_info);
494 arp_standard.debug(SQLERRM);
495 END IF;
496 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
497 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
498 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
499 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
500 FND_MSG_PUB.ADD;
501 END IF;
502 APP_EXCEPTION.RAISE_EXCEPTION;
503 END InformSysAdminError;
504
505 /* =======================================================================
506 | PROCEDURE GenerateAccessVerifyQuestion
507 |
508 | DESCRIPTION
509 |
510 |
511 | PARAMETERS
512 |
513 * ======================================================================*/
514 ---------------------------------------------------------------------------
515 PROCEDURE GenerateAccessVerifyQuestion(
516 p_registration_id IN NUMBER,
517 p_client_ip_address IN VARCHAR2,
518 p_customer_id IN VARCHAR2,
519 p_customer_site_use_id IN VARCHAR2)
520 ---------------------------------------------------------------------------
521 IS
522 l_rowid VARCHAR2(100);
523 l_reg_access_verify_id NUMBER;
524 l_verify_access VerifyAccessTable;
525 l_verify_access_rec VerifyAccessRec;
526 l_curr_question VARCHAR2(2000);
527 l_curr_exp_answer VARCHAR2(2000);
528 l_attempts NUMBER;
529 l_currency_code VARCHAR2(15);
530 l_customer_site_use_id VARCHAR2(50);
531 l_procedure_name VARCHAR2(50) := '.GenerateAccessVerifyQuestion';
532 l_debug_info VARCHAR2(300);
533 BEGIN
534 ----------------------------------------------------------------------------
535 l_debug_info := 'In debug mode, log we have entered this procedure';
536 ----------------------------------------------------------------------------
537 IF (PG_DEBUG = 'Y') THEN
538 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
539 END IF;
540
541 ----------------------------------------------------------------------------
542 l_debug_info := 'Insert Row, reg_id = ' || to_char(p_registration_id) || ',ip_addr= ' ||
543 p_client_ip_address || 'customer_id = ' || to_char(p_customer_id) || ',customer_site_id = ' ||
544 to_char(p_customer_site_use_id);
545 ----------------------------------------------------------------------------
546 IF (PG_DEBUG = 'Y') THEN
547 arp_standard.debug(l_debug_info);
548 END IF;
549
550 --If customer site id is -1, pass null to ARI_SELF_REG_CONFIG
551 IF (p_customer_site_use_id <> -1) THEN
552 l_customer_site_use_id := p_customer_site_use_id;
553 END IF;
554
555 ARI_SELF_REG_CONFIG.verify_customer_site_access(p_customer_id => p_customer_id,
556 p_customer_site_use_id => l_customer_site_use_id,
557 x_verify_access => l_verify_access,
558 x_attempts => l_attempts);
559
560 IF (l_verify_access.count > 0) THEN
561 FOR i IN 1..l_verify_access.count LOOP
562
563 l_reg_access_verify_id := null;
564
565 ARI_REG_VERIFICATIONS_PKG.Insert_Row(
566 x_rowid => l_rowid,
567 x_client_ip_address => p_client_ip_address,
568 x_question => l_verify_access(i).question,
569 x_expected_answer => l_verify_access(i).expected_answer,
570 x_number_of_attempts => 0,--l_attempts,
571 x_currency_code => l_verify_access(i).currency_code,
572 x_customer_id => p_customer_id,
573 x_customer_site_use_id => p_customer_site_use_id,
574 x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
575 x_last_update_date => sysdate,
576 x_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
577 x_creation_date => sysdate,
578 x_created_by => nvl(FND_GLOBAL.user_id,-1));
579
580
581
582 END LOOP;
583 END IF;
584
585 COMMIT;
586
587 IF (PG_DEBUG = 'Y') THEN
588 arp_standard.debug('after insert row');
589 END IF;
590 ----------------------------------------------------------------------------
591 l_debug_info := 'In debug mode, log that we have exited this procedure';
592 ----------------------------------------------------------------------------
593 IF (PG_DEBUG = 'Y') THEN
594 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
595 END IF;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF (SQLCODE <> -20001) THEN
600 IF (PG_DEBUG = 'Y') THEN
601 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
602 arp_standard.debug('Debug Info: ' || l_debug_info);
603 arp_standard.debug(SQLERRM);
604 END IF;
605 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
606 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
607 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
608 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
609 FND_MSG_PUB.ADD;
610 InformSysAdminError(p_procedure_name => l_procedure_name,
611 p_debug_info => l_debug_info,
612 p_error => SQLERRM);
613 END IF;
614 APP_EXCEPTION.RAISE_EXCEPTION;
615 END GenerateAccessVerifyQuestion;
616
617 /* =======================================================================
618 | PROCEDURE ClearRegistrationTable
619 |
620 | DESCRIPTION
621 |
622 |
623 | PARAMETERS
624 |
625 * ======================================================================*/
626 ---------------------------------------------------------------------------
627 PROCEDURE ClearRegistrationTable IS
628
629 l_procedure_name VARCHAR2(50);
630 l_debug_info VARCHAR2(200);
631
632 BEGIN
633
634 l_procedure_name := '.ClearRegistrationTable';
635
636 ----------------------------------------------------------------------------------------
637 l_debug_info := 'Delete all records in Registration GT';
638 -----------------------------------------------------------------------------------------
639 IF (PG_DEBUG = 'Y') THEN
640 arp_standard.debug(l_debug_info);
641 END IF;
642
643 DELETE FROM ARI_REG_VERIFICATIONS_GT;
644
645 COMMIT;
646
647 ----------------------------------------------------------------------------------------
648 l_debug_info := 'All records in Registration GT deleted';
649 -----------------------------------------------------------------------------------------
650 IF (PG_DEBUG = 'Y') THEN
651 arp_standard.debug(l_debug_info);
652 END IF;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 IF (PG_DEBUG = 'Y') THEN
657 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
658 arp_standard.debug('ERROR =>'|| SQLERRM);
659 END IF;
660
661 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
662 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
663 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
664 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
665 FND_MSG_PUB.ADD;
666
667
668 END;
669
670 /* =======================================================================
671 | PROCEDURE GenCustDetailAccessQuestion
672 |
673 | DESCRIPTION
674 |
675 |
676 | PARAMETERS
677 |
678 * ======================================================================*/
679 ---------------------------------------------------------------------------
680 PROCEDURE GenCustDetailAccessQuestion(
681 p_client_ip_address IN VARCHAR2,
682 p_customer_id IN VARCHAR2)
683 ---------------------------------------------------------------------------
684 IS
685 l_rowid VARCHAR2(100);
686 l_reg_access_verify_id NUMBER;
687 l_verify_access VerifyAccessTable;
688 l_verify_access_rec VerifyAccessRec;
689 l_curr_question VARCHAR2(2000);
690 l_curr_exp_answer VARCHAR2(2000);
691 l_attempts NUMBER;
692 l_currency_code VARCHAR2(15);
693 l_procedure_name VARCHAR2(50) := '.GenCustDetailAccessQuestion';
694 l_debug_info VARCHAR2(300);
695 BEGIN
696 ----------------------------------------------------------------------------
697 l_debug_info := 'In debug mode, log we have entered this procedure';
698 ----------------------------------------------------------------------------
699 IF (PG_DEBUG = 'Y') THEN
700 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
701 END IF;
702 ----------------------------------------------------------------------------
703 l_debug_info := 'Insert Row,ip_addr= ' || p_client_ip_address || 'customer_id = ' || to_char(p_customer_id);
704 ----------------------------------------------------------------------------
705 IF (PG_DEBUG = 'Y') THEN
706 arp_standard.debug(l_debug_info);
707 END IF;
708
709 ARI_SELF_REG_CONFIG.validate_cust_detail_access(p_customer_id => p_customer_id,
710 x_verify_access => l_verify_access,
711 x_attempts => l_attempts);
712
713
714 IF (l_verify_access.count > 0) THEN
715 FOR i IN 1..l_verify_access.count LOOP
716
717 l_reg_access_verify_id := null;
718
719 ARI_REG_VERIFICATIONS_PKG.Insert_Row(
720 x_rowid => l_rowid,
721 x_client_ip_address => p_client_ip_address,
722 x_question => l_verify_access(i).question,
723 x_expected_answer => l_verify_access(i).expected_answer,
724 x_number_of_attempts => 0, --l_attempts,
725 x_currency_code => l_verify_access(i).currency_code,
726 x_customer_id => p_customer_id,
727 x_customer_site_use_id => null,
728 x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
729 x_last_update_date => sysdate,
730 x_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
731 x_creation_date => sysdate,
732 x_created_by => nvl(FND_GLOBAL.user_id,-1));
733
734
735 END LOOP;
736 END IF;
737
738 COMMIT;
739
740 IF (PG_DEBUG = 'Y') THEN
741 arp_standard.debug('after insert row');
742 END IF;
743 ----------------------------------------------------------------------------
744 l_debug_info := 'In debug mode, log that we have exited this procedure';
745 ----------------------------------------------------------------------------
746 IF (PG_DEBUG = 'Y') THEN
747 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
748 END IF;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 IF (SQLCODE <> -20001) THEN
753 IF (PG_DEBUG = 'Y') THEN
754 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
755 arp_standard.debug('Debug Info: ' || l_debug_info);
756 arp_standard.debug(SQLERRM);
757 END IF;
758 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
759 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
760 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
761 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
762 FND_MSG_PUB.ADD;
763 InformSysAdminError(p_procedure_name => l_procedure_name,
764 p_debug_info => l_debug_info,
765 p_error => SQLERRM);
766 END IF;
767 APP_EXCEPTION.RAISE_EXCEPTION;
768 END GenCustDetailAccessQuestion;
769
770 /* =======================================================================
771 | FUNCTION ValidateAnswer
772 |
773 | DESCRIPTION
774 |
775 |
776 | PARAMETERS
777 |
778 * ======================================================================*/
779 ---------------------------------------------------------------------------
780 /*FUNCTION ValidateAnswer( p_answer IN VARCHAR2,
781 p_reg_access_verify_id IN NUMBER)
782 RETURN VARCHAR2
783 ---------------------------------------------------------------------------
784 IS
785 l_expected_answer ari_reg_verifications_gt.expected_answer%type;
786 l_return VARCHAR2(5);
787 BEGIN
788
789 BEGIN
790 select expected_answer
791 into l_expected_answer
792 from ari_reg_verifications_gt
793 where reg_access_verify_id = p_reg_access_verify_id;
794 END;
795
796 IF l_expected_answer = to_char(p_answer) THEN
797 l_return := 'Y';
798 ELSE
799 l_return := 'N';
800 END IF;
801
802 RETURN l_return;
803
804 END ValidateAnswer;*/
805
806 /* =======================================================================
807 | FUNCTION RemoveRoleAccess
808 |
809 | DESCRIPTION
810 |
811 |
812 | PARAMETERS
813 |
814 * ======================================================================*/
815 ---------------------------------------------------------------------------
816 PROCEDURE RemoveRoleAccess(p_person_party_id IN VARCHAR2,
817 p_customer_id IN VARCHAR2,
818 p_cust_acct_site_id IN VARCHAR2,
819 x_return_status OUT NOCOPY VARCHAR2)
820 ---------------------------------------------------------------------------
821 IS
822 CURSOR cust_acct_role_cur(p_person_party_id IN VARCHAR2,
823 p_customer_id IN VARCHAR2,
824 p_cust_acct_site_id IN VARCHAR2) IS
825 select hcar.cust_account_role_id
826 from hz_role_responsibility hrr, hz_cust_account_roles hcar
827 where hrr.responsibility_type = 'SELF_SERVICE_USER'
828 and hrr.cust_account_role_id = hcar.cust_account_role_id
829 and hcar.cust_account_id = p_customer_id
830 --Bug 4764121 : Fixed the removal of access to all customers
831 and DECODE(p_cust_acct_site_id, '-1', -1,p_cust_acct_site_id) =
832 DECODE(p_cust_acct_site_id, '-1', -1, hcar.cust_acct_site_id)
833 and hcar.party_id = p_person_party_id;
834
835 CURSOR cu_acct_role_version (p_cust_acct_role_id IN NUMBER) IS
836 SELECT OBJECT_VERSION_NUMBER
837 FROM HZ_CUST_ACCOUNT_ROLES
838 WHERE CUST_ACCOUNT_ROLE_ID = p_cust_acct_role_id;
839
840 l_cust_account_role_id HZ_CUST_ACCOUNT_ROLES.cust_account_role_id%TYPE;
841 p_cust_account_role_rec_type HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
842 l_object_version_number HZ_CUST_ACCOUNT_ROLES.object_version_number%TYPE;
843 l_msg_count NUMBER;
844 l_msg_data VARCHAR2(2000);
845 l_return_status VARCHAR2(10);
846 l_procedure_name VARCHAR2(50) := '.RemoveRoleAccess';
847 l_debug_info VARCHAR2(300);
848
849 BEGIN
850 ----------------------------------------------------------------------------
851 l_debug_info := 'In debug mode, log we have entered this procedure';
852 ----------------------------------------------------------------------------
853 IF (PG_DEBUG = 'Y') THEN
854 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
855 END IF;
856
857 SAVEPOINT RemoveAccessStart;
858
859 ----------------------------------------------------------------------------
860 l_debug_info := 'Update Cust Account Role';
861 ----------------------------------------------------------------------------
862 IF (PG_DEBUG = 'Y') THEN
863 arp_standard.debug(l_debug_info);
864 END IF;
865
866 FOR role_record IN cust_acct_role_cur(p_person_party_id,
867 p_customer_id,
868 p_cust_acct_site_id)
869 LOOP
870
871 OPEN cu_acct_role_version(role_record.cust_account_role_id);
872 FETCH cu_acct_role_version INTO
873 l_object_version_number;
874 CLOSE cu_acct_role_version;
875
876 p_cust_account_role_rec_type.cust_account_role_id := role_record.cust_account_role_id;
877 p_cust_account_role_rec_type.status := 'I'; --Inactive
878 --p_cust_account_role_rec_type.end_date := sysdate;
879
880 HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
881 p_init_msg_list => FND_API.G_FALSE,
882 p_cust_account_role_rec => p_cust_account_role_rec_type,
883 p_object_version_number => l_object_version_number,
884 x_return_status => l_return_status,
885 x_msg_count => l_msg_count,
886 x_msg_data => l_msg_data);
887
888 x_return_status := l_return_status;
889 --IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
890 -- RETURN;
891 --END IF;
892
893 END LOOP;
894
895 COMMIT;
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 ROLLBACK TO RemoveAccessStart;
901 END RemoveRoleAccess;
902
903 /* =======================================================================
904 | FUNCTION GetPartyRelationshipId
905 |
906 | DESCRIPTION
907 |
908 |
909 | PARAMETERS
910 |
911 * ======================================================================*/
912 ---------------------------------------------------------------------------
913 FUNCTION GetPartyRelationshipId (p_user_id IN VARCHAR2,
914 p_customer_id IN VARCHAR2)
915 RETURN VARCHAR2
916 ---------------------------------------------------------------------------
917 IS
918 l_party_rel_id VARCHAR2(50);
919 l_party_id VARCHAR2(50);
920 BEGIN
921
922 IF p_customer_id IS NULL THEN
923
924 select to_char(customer_id)
925 into l_party_rel_id
926 from fnd_user
927 where user_id = p_user_id;
928
929 ELSE
930 -- Bug 5219389 - Party Id should be passed instead of cust_account_id.
931 SELECT party_id INTO l_party_id FROM hz_cust_accounts WHERE cust_account_id = p_customer_id;
932
933 select party_id into l_party_rel_id
934 from (
935 select hr1.party_id
936 from hz_relationships hr1,
937 hz_relationships hr2,
938 fnd_user fu
939 where hr1.subject_type = 'PERSON'
940 AND (hr1.relationship_code = 'CONTACT_OF' OR hr1.relationship_code = 'EMPLOYEE_OF')
941 AND hr1.status = 'A'
942 and hr1.object_id = l_party_id
943 and hr1.subject_id = hr2.subject_id
944 AND (hr1.end_date is null OR hr1.end_date > sysdate)
945 and hr2.party_id = fu.customer_id
946 and fu.user_id = p_user_id
947 UNION ALL
948 select hr1.party_id
949
950 from hz_relationships hr1,
951 fnd_user fu, hz_parties Party
952 where hr1.subject_type = 'PERSON'
953 AND (hr1.relationship_code = 'CONTACT_OF' OR hr1.relationship_code = 'EMPLOYEE_OF')
954 AND hr1.status = 'A'
955 and hr1.object_id = l_party_id
956 and hr1.subject_id = fu.customer_id
957 AND (hr1.end_date is null OR hr1.end_date > sysdate)
958 and fu.user_id = p_user_id
959 AND Party.party_id = fu.customer_id
960 AND Party.party_type = 'PERSON'
961 AND Party.status = 'A');
962
963 END IF;
964
965 RETURN l_party_rel_id;
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 return null;
970 END GetPartyRelationshipId;
971
972 /* =======================================================================
973 | FUNCTION GetCustomerAcctNumber
974 |
975 | DESCRIPTION
976 |
977 |
978 | PARAMETERS
979 |
980 * ======================================================================*/
981 ---------------------------------------------------------------------------
982 FUNCTION GetCustomerAcctNumber (p_cust_account_id IN VARCHAR2)
983 RETURN VARCHAR2
984 ---------------------------------------------------------------------------
985 IS
986 l_acct_number varchar2(30);
987 BEGIN
988 select account_number
989 into l_acct_number
990 from hz_cust_accounts
991 where cust_account_id = p_cust_account_id;
992
993 RETURN l_acct_number;
994
995 EXCEPTION
996 WHEN OTHERS THEN
997 return null;
998 END GetCustomerAcctNumber;
999
1000 /* =======================================================================
1001 | FUNCTION CheckUserIsAdmin
1002 |
1003 | DESCRIPTION
1004 |
1005 |
1006 | PARAMETERS
1007 |
1008 * ======================================================================*/
1009 ---------------------------------------------------------------------------
1010 FUNCTION CheckUserIsAdmin (p_user_id IN VARCHAR2)
1011 RETURN VARCHAR2
1012 ---------------------------------------------------------------------------
1013 IS
1014 l_return varchar2(5);
1015 BEGIN
1016
1017 select 'Y'
1018 into l_return
1019 from dual
1020 where p_user_id IN ( select user_id
1021 from umx_role_assignments_v
1022 where role_name like 'UMX|ARI_CUST_ADMIN');
1023
1024 RETURN l_return;
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 return 'N';
1029 END CheckUserIsAdmin;
1030
1031 /*=======================================================================
1032 | FUNCTION CreatePersonPartyInternal
1033 |
1034 | DESCRIPTION
1035 |
1036 |
1037 | PARAMETERS
1038 |
1039 * ======================================================================*/
1040 ---------------------------------------------------------------------------
1041 Procedure CreatePersonPartyInternal(p_event in out NOCOPY WF_EVENT_T,
1042 p_person_party_id out NOCOPY varchar2)
1043 ---------------------------------------------------------------------------
1044 IS
1045
1046 l_first_name hz_parties.person_first_name%type;
1047 l_last_name hz_parties.person_last_name%type;
1048 l_middle_name hz_parties.person_middle_name%type;
1049 l_pre_name_adjunct hz_parties.person_pre_name_adjunct%type;
1050 l_person_name_suffix hz_parties.person_name_suffix%type;
1051
1052 l_party_number hz_parties.party_number%type;
1053 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
1054 l_profile_id NUMBER;
1055 l_reg_type VARCHAR2(50);
1056 l_reg_user_name fnd_user.user_name%type;
1057 l_email_address fnd_user.email_address%type;
1058 l_procedure_name VARCHAR2(50) := '.CreatePersonPartyInternal';
1059 l_debug_info VARCHAR2(300);
1060
1061 X_Return_Status VARCHAR2(20);
1062 X_Msg_Count NUMBER;
1063 X_Msg_data VARCHAR2(300);
1064
1065 BEGIN
1066
1067 ----------------------------------------------------------------------------
1068 l_debug_info := 'In debug mode, log we have entered this procedure';
1069 ----------------------------------------------------------------------------
1070 IF (PG_DEBUG = 'Y') THEN
1071 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1072 END IF;
1073
1074 ----------------------------------------------------------------------------
1075 l_debug_info := 'Read values from the event';
1076 ----------------------------------------------------------------------------
1077 IF (PG_DEBUG = 'Y') THEN
1078 arp_standard.debug(l_debug_info);
1079 END IF;
1080 l_first_name := p_event.getvalueforparameter('FIRST_NAME');
1081 l_last_name := p_event.getvalueforparameter('LAST_NAME');
1082 l_middle_name := p_event.getvalueforparameter('MIDDLE_NAME');
1083 l_pre_name_adjunct := p_event.getvalueforparameter('PRE_NAME_ADJUNCT');
1084 l_person_name_suffix := p_event.getvalueforparameter('PERSON_SUFFIX');
1085
1086 ----------------------------------------------------------------------------
1087 l_debug_info := 'Populate person record';
1088 ----------------------------------------------------------------------------
1089 IF (PG_DEBUG = 'Y') THEN
1090 arp_standard.debug(l_debug_info);
1091 END IF;
1092
1093 l_person_rec.person_first_name := l_first_name;
1094 l_person_rec.person_middle_name := l_middle_name;
1095 l_person_rec.person_last_name := l_last_name;
1096 l_person_rec.person_pre_name_adjunct := l_pre_name_adjunct;
1097 l_person_rec.person_name_suffix := l_person_name_suffix;
1098 l_person_rec.created_by_module := 'ARI';
1099 l_person_rec.application_id := 0;
1100
1101 HZ_PARTY_V2PUB.create_person (
1102 p_person_rec => l_person_rec,
1103 x_party_id => p_person_party_id,
1104 x_party_number => l_party_number,
1105 x_profile_id => l_profile_id,
1106 x_return_status => X_Return_Status,
1107 x_msg_count => X_Msg_Count,
1108 x_msg_data => X_Msg_Data);
1109
1110 ----------------------------------------------------------------------------
1111 l_debug_info := 'Completed Hz_party_v2_pub.createperson: Status'||x_return_status;
1112 ----------------------------------------------------------------------------
1113 IF (PG_DEBUG = 'Y') THEN
1114 arp_standard.debug(l_debug_info);
1115 END IF;
1116
1117 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1118 ----------------------------------------------------------------------------
1119 l_debug_info := 'Completed Hz_party_v2_pub.createperson: Message'||X_Msg_Data;
1120 ----------------------------------------------------------------------------
1121 IF (PG_DEBUG = 'Y') THEN
1122 arp_standard.debug(l_debug_info);
1123 END IF;
1124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1125 end if;
1126
1127 ----------------------------------------------------------------------------
1128 l_debug_info := 'After creating person party, if this is an Additional Access flow,
1129 associate the created person party id with the user';
1130 ----------------------------------------------------------------------------
1131 IF (PG_DEBUG = 'Y') THEN
1132 arp_standard.debug(l_debug_info);
1133 END IF;
1134
1135 l_reg_type := p_event.getvalueforparameter('REG_SERVICE_TYPE');
1136
1137 IF (l_reg_type = 'ADDITIONAL_ACCESS' OR l_reg_type ='ARI_ADD_CUST_ACCESS') THEN
1138 --Requested User Name is populated with the user name of the user requesting
1139 --In case of this scenario, admin will not be able to request additional access
1140 --because this user will not come up in the listing of users under admin.
1141 -- This is strictly an ART flow scenario.
1142 l_reg_user_name := p_event.getvalueforparameter('REQUESTED_USERNAME');
1143 l_email_address := p_event.getvalueforparameter('EMAIL_ADDRESS');
1144
1145 ----------------------------------------------------------------------------
1146 l_debug_info := 'Call FND_USER_PKG to update user with person party id';
1147 ----------------------------------------------------------------------------
1148 IF (PG_DEBUG = 'Y') THEN
1149 arp_standard.debug(l_debug_info);
1150 END IF;
1151
1152 FND_USER_PKG.UpdateUser (
1153 x_user_name => l_reg_user_name,
1154 x_owner => 'CUST',
1155 x_email_address => l_email_address,
1156 x_customer_id => p_person_party_id
1157 );
1158
1159 END IF;
1160
1161 ----------------------------------------------------------------------------
1162 l_debug_info := 'In debug mode, log that we have exited this procedure';
1163 ----------------------------------------------------------------------------
1164 IF (PG_DEBUG = 'Y') THEN
1165 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1166 END IF;
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 IF (SQLCODE <> -20001) THEN
1171 IF (PG_DEBUG = 'Y') THEN
1172 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1173 arp_standard.debug('Debug Info: ' || l_debug_info);
1174 arp_standard.debug(SQLERRM);
1175 END IF;
1176 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1177 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1178 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1179 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1180 FND_MSG_PUB.ADD;
1181 InformSysAdminError(p_procedure_name => l_procedure_name,
1182 p_debug_info => l_debug_info,
1183 p_error => SQLERRM);
1184 END IF;
1185 APP_EXCEPTION.RAISE_EXCEPTION;
1186 END CreatePersonPartyInternal;
1187
1188 /*=======================================================================
1189 | FUNCTION GetOrgPartyId
1190 |
1191 | DESCRIPTION
1192 |
1193 |
1194 | PARAMETERS
1195 |
1196 * ======================================================================*/
1197 ---------------------------------------------------------------------------
1198 FUNCTION GetOrgPartyId(p_customer_id IN NUMBER) RETURN NUMBER
1199 IS
1200 l_org_party_id NUMBER;
1201 ---------------------------------------------------------------------------
1202 BEGIN
1203
1204 select party_id
1205 into l_org_party_id
1206 from hz_cust_accounts
1207 where cust_account_id = p_customer_id;
1208
1209 return l_org_party_id;
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 return null;
1214 END GetOrgPartyId;
1215
1216 /*=======================================================================
1217 | FUNCTION GetPartySiteId
1218 |
1219 | DESCRIPTION
1220 |
1221 |
1222 | PARAMETERS
1223 |
1224 * ======================================================================*/
1225 ---------------------------------------------------------------------------
1226 FUNCTION GetPartySiteId(p_cust_site_use_id IN NUMBER) RETURN NUMBER
1227 ---------------------------------------------------------------------------
1228 IS
1229 l_party_site_id NUMBER;
1230 BEGIN
1231
1232 select hcas.party_site_id
1233 into l_party_site_id
1234 from hz_cust_acct_sites hcas, hz_cust_site_uses hcsu
1235 where hcas.cust_acct_site_id = hcsu.cust_acct_site_id
1236 and hcsu.site_use_id = p_cust_site_use_id;
1237
1238 return l_party_site_id;
1239
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 return null;
1243 END GetPartySiteId;
1244
1245 /*=======================================================================
1246 | FUNCTION GetCustAcctSiteId
1247 |
1248 | DESCRIPTION
1249 |
1250 |
1251 | PARAMETERS
1252 |
1253 * ======================================================================*/
1254 ---------------------------------------------------------------------------
1255 FUNCTION GetCustAcctSiteId(p_cust_site_use_id IN NUMBER) RETURN NUMBER
1256 ---------------------------------------------------------------------------
1257 IS
1258 l_cust_acct_site_id NUMBER;
1259 BEGIN
1260
1261 select cust_acct_site_id
1262 into l_cust_acct_site_id
1263 from hz_cust_site_uses
1264 where site_use_id = p_cust_site_use_id;
1265
1266 return l_cust_acct_site_id;
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 return null;
1271 END GetCustAcctSiteId;
1272
1273 /*=======================================================================
1274 | FUNCTION CreateOrgContactInternal
1275 |
1276 | DESCRIPTION
1277 |
1278 |
1279 | PARAMETERS
1280 |
1281 * ======================================================================*/
1282 ---------------------------------------------------------------------------
1283 Procedure CreateOrgContactInternal(p_event in out NOCOPY WF_EVENT_T,
1284 p_person_party_id in varchar2,
1285 p_party_id out NOCOPY number)
1286 ---------------------------------------------------------------------------
1287 IS
1288 l_org_contact_id NUMBER;
1289 l_party_rel_id NUMBER;
1290 l_profile_id NUMBER;
1291 l_org_party_id NUMBER;
1292 l_party_site_id NUMBER;
1293 l_customer_id NUMBER;
1294 l_cust_site_use_id NUMBER;
1295
1296 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
1297 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
1298 x_org_contact_party_id NUMBER;
1299 --l_party_id NUMBER;-- to be used for contactpoints
1300 l_party_number VARCHAR2(100);
1301 l_cust_acct_type VARCHAR2(20);
1302 l_already_exists VARCHAR2(10);
1303 l_procedure_name VARCHAR2(50) := '.CreateOrgContactInternal';
1304 l_debug_info VARCHAR2(300);
1305
1306 X_Return_Status VARCHAR2(20);
1307 X_Msg_Count NUMBER;
1308 X_Msg_data VARCHAR2(300);
1309
1310 BEGIN
1311
1312 ----------------------------------------------------------------------------
1313 l_debug_info := 'In debug mode, log we have entered this procedure';
1314 ----------------------------------------------------------------------------
1315 IF (PG_DEBUG = 'Y') THEN
1316 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1317 END IF;
1318
1319 l_customer_id := p_event.getvalueforparameter('CUSTOMER_ID');
1320 l_cust_site_use_id := p_event.getvalueforparameter('CUSTOMER_SITE_USE_ID');
1321
1322 l_org_party_id := GetOrgPartyId(p_customer_id => l_customer_id);
1323 l_party_site_id := GetPartySiteId(p_cust_site_use_id => l_cust_site_use_id);
1324
1325 l_cust_acct_type := p_event.getvalueforparameter('CUST_ACCT_TYPE');
1326
1327 ----------------------------------------------------------------------------
1328 l_debug_info := 'Check if relationship already exists';
1329 ----------------------------------------------------------------------------
1330 IF (PG_DEBUG = 'Y') THEN
1331 arp_standard.debug(l_debug_info);
1332 END IF;
1333 BEGIN
1334 --Bug 4764121: Storing the value of party_id (even when relationship exists)
1335 SELECT 'Y',party_id
1336 INTO l_already_exists,p_party_id
1337 FROM HZ_RELATIONSHIPS
1338 WHERE SUBJECT_ID = p_person_party_id
1339 AND SUBJECT_TYPE = 'PERSON'
1340 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1341 AND RELATIONSHIP_TYPE = 'CONTACT'
1342 AND RELATIONSHIP_CODE = 'CONTACT_OF'
1343 AND OBJECT_ID = l_org_party_id
1344 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date,SYSDATE))
1345 AND TRUNC(NVL(end_date,SYSDATE));
1346 EXCEPTION
1347 WHEN TOO_MANY_ROWS THEN
1348 --Relationship exists
1349 RETURN;
1350 WHEN NO_DATA_FOUND THEN
1351 l_already_exists := 'N';
1352 END;
1353
1354 IF l_already_exists = 'N' THEN
1355
1356 ----------------------------------------------------------------------------
1357 l_debug_info := 'Create the org contact relationship';
1358 ----------------------------------------------------------------------------
1359 IF (PG_DEBUG = 'Y') THEN
1360 arp_standard.debug(l_debug_info);
1361 END IF;
1362
1363 l_party_rel_rec.subject_id := p_person_party_id;
1364 l_party_rel_rec.subject_type := 'PERSON';
1365 l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
1366 l_party_rel_rec.relationship_type := 'CONTACT';
1367 l_party_rel_rec.relationship_code := 'CONTACT_OF';
1368 l_party_rel_rec.start_date := sysdate;
1369 l_party_rel_rec.object_id := l_org_party_id;
1370 IF l_cust_acct_type = G_BUSINESS THEN
1371 l_party_rel_rec.object_type := 'ORGANIZATION';
1372 ELSIF l_cust_acct_type = G_CONSUMER THEN
1373 l_party_rel_rec.object_type := 'PERSON';
1374 END IF;
1375 l_party_rel_rec.object_table_name := 'HZ_PARTIES';
1376 l_party_rel_rec.created_by_module := G_CREATED_BY_MODULE;
1377 l_party_rel_rec.application_id := 0;
1378 l_org_contact_rec.party_rel_rec := l_party_rel_rec;
1379 l_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
1380 l_org_contact_rec.application_id := 0;
1381 l_org_contact_rec.party_site_id := l_party_site_id;
1382
1383 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
1384 p_org_contact_rec => l_org_contact_rec,
1385 x_org_contact_id => x_org_contact_party_id,
1386 x_party_rel_id => l_party_rel_id,
1387 x_party_id => p_party_id,
1388 x_party_number => l_party_number,
1389 x_return_status => X_Return_Status,
1390 x_msg_count => X_Msg_Count,
1391 x_msg_data => X_Msg_data
1392 );
1393
1394 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 end if;
1397
1398 END IF;
1399
1400 ----------------------------------------------------------------------------
1401 l_debug_info := 'In debug mode, log that we have exited this procedure';
1402 ----------------------------------------------------------------------------
1403 IF (PG_DEBUG = 'Y') THEN
1404 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1405 END IF;
1406
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 IF (SQLCODE <> -20001) THEN
1410 IF (PG_DEBUG = 'Y') THEN
1411 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1412 arp_standard.debug('Debug Info: ' || l_debug_info);
1413 arp_standard.debug(SQLERRM);
1414 END IF;
1415 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1416 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1417 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1418 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1419 FND_MSG_PUB.ADD;
1420 InformSysAdminError(p_procedure_name => l_procedure_name,
1421 p_debug_info => l_debug_info,
1422 p_error => SQLERRM);
1423 END IF;
1424 APP_EXCEPTION.RAISE_EXCEPTION;
1425
1426 END CreateOrgContactInternal;
1427
1428 /*=======================================================================
1429 | FUNCTION CreateContactPointInternal
1430 |
1431 | DESCRIPTION
1432 |
1433 |
1434 | PARAMETERS
1435 |
1436 * ======================================================================*/
1437 ---------------------------------------------------------------------------
1438 Procedure CreateContactPointInternal(p_event in out NOCOPY WF_EVENT_T,
1439 p_contact_party_id in varchar2)
1440 ---------------------------------------------------------------------------
1441 IS
1442
1443 l_contact_point_id hz_contact_points.contact_point_id%type;
1444 l_contact_preference_id hz_contact_preferences.contact_preference_id%type;
1445 l_email_format HZ_CONTACT_POINTS.email_format%type;
1446 l_email_address HZ_CONTACT_POINTS.email_address%type;
1447 l_primary_phone HZ_CONTACT_POINTS.phone_number%type;
1448 l_area_code HZ_CONTACT_POINTS.phone_area_code%type;
1449 l_country_code HZ_CONTACT_POINTS.phone_country_code%type;
1450 l_phone_purpose HZ_CONTACT_POINTS.contact_point_purpose%type;
1451 l_phone_extension HZ_CONTACT_POINTS.phone_extension%type;
1452 l_object_version_number HZ_CONTACT_POINTS.object_version_number%type;
1453
1454 l_profile_id number;
1455 l_cust_site_use_id NUMBER;
1456
1457 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
1458 l_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
1459 l_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
1460 l_procedure_name VARCHAR2(50) := '.CreateContactPointInternal';
1461 l_debug_info VARCHAR2(300);
1462
1463 X_Return_Status VARCHAR2(20);
1464 X_Msg_Count NUMBER;
1465 X_Msg_data VARCHAR2(300);
1466
1467 BEGIN
1468
1469 ----------------------------------------------------------------------------
1470 l_debug_info := 'In debug mode, log we have entered this procedure';
1471 ----------------------------------------------------------------------------
1472 IF (PG_DEBUG = 'Y') THEN
1473 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1474 END IF;
1475
1476 -- get the values from event object
1477 l_email_address := p_event.getvalueforparameter('EMAIL_ADDRESS');
1478 l_email_format := p_event.getvalueforparameter('EMAIL_PREFERENCE');
1479 l_primary_phone := p_event.getvalueforparameter('PRIMARY_PHONE');
1480 l_area_code := p_event.getvalueforparameter('AREA_CODE');
1481 l_country_code := p_event.getvalueforparameter('COUNTRY_CODE');
1482 l_phone_purpose := p_event.getvalueforparameter('PHONE_PURPOSE');
1483 l_phone_extension := p_event.getvalueforparameter('PHONE_EXTENSION');
1484
1485 --populate the record
1486 l_contact_point_rec.status := 'A';
1487 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1488 l_contact_point_rec.owner_table_id := p_contact_party_id;
1489 l_contact_point_rec.primary_flag := 'Y';
1490 l_contact_point_rec.created_by_module := 'ARI';
1491 l_contact_point_rec.application_id := 0;
1492
1493 if l_email_address is not null then
1494 ----------------------------------------------------------------------------
1495 l_debug_info := 'Email Address not null - create/update email contact point';
1496 ----------------------------------------------------------------------------
1497 IF (PG_DEBUG = 'Y') THEN
1498 arp_standard.debug(l_debug_info);
1499 END IF;
1500
1501 l_contact_point_rec.contact_point_type := 'EMAIL';
1502
1503 l_email_rec.email_address := l_email_address;
1504 l_email_rec.email_format := l_email_format;
1505
1506 ----------------------------------------------------------------------------
1507 l_debug_info := 'Check if an email record already exists';
1508 ----------------------------------------------------------------------------
1509 IF (PG_DEBUG = 'Y') THEN
1510 arp_standard.debug(l_debug_info);
1511 END IF;
1512 BEGIN
1513 l_contact_point_id := null;
1514
1515 SELECT contact_point_id
1516 INTO l_contact_point_id
1517 FROM hz_contact_points
1518 WHERE owner_table_id = p_contact_party_id
1519 AND owner_table_name = 'HZ_PARTIES'
1520 AND status = 'A'
1521 AND primary_flag = 'Y'
1522 AND contact_point_type = 'EMAIL';
1523 EXCEPTION
1524 WHEN TOO_MANY_ROWS THEN
1525 --Not possible
1526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1527 WHEN OTHERS THEN
1528 NULL;
1529 END;
1530
1531 IF (l_contact_point_id IS NULL) THEN
1532
1533 ----------------------------------------------------------------------------
1534 l_debug_info := 'Create email contact point';
1535 ----------------------------------------------------------------------------
1536 IF (PG_DEBUG = 'Y') THEN
1537 arp_standard.debug(l_debug_info);
1538 END IF;
1539
1540 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1541 p_contact_point_rec => l_contact_point_rec,
1542 p_email_rec => l_email_rec,
1543 x_contact_point_id => l_contact_point_id,
1544 x_return_status => X_Return_Status,
1545 x_msg_count => X_Msg_Count,
1546 x_msg_data => X_Msg_Data);
1547 ELSE
1548
1549 ----------------------------------------------------------------------------
1550 l_debug_info := 'Update email contact point';
1551 ----------------------------------------------------------------------------
1552 IF (PG_DEBUG = 'Y') THEN
1553 arp_standard.debug(l_debug_info);
1554 END IF;
1555
1556 SELECT object_version_number
1557 INTO l_object_version_number
1558 FROM HZ_CONTACT_POINTS
1559 WHERE contact_point_id = l_contact_point_id;
1560
1561
1562 HZ_CONTACT_POINT_V2PUB.update_contact_point (
1563 p_contact_point_rec => l_contact_point_rec,
1564 p_email_rec => l_email_rec,
1565 p_object_version_number => l_object_version_number,
1566 x_return_status => X_Return_Status,
1567 x_msg_count => X_Msg_Count,
1568 x_msg_data => X_Msg_Data);
1569 END IF;
1570
1571 ----------------------------------------------------------------------------
1572 l_debug_info := 'Contact point done: Status' || X_Return_Status;
1573 ----------------------------------------------------------------------------
1574 IF (PG_DEBUG = 'Y') THEN
1575 arp_standard.debug(l_debug_info);
1576 END IF;
1577
1578 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1580 end if;
1581
1582 end if; --mail address not null
1583
1584
1585 if l_primary_phone is not null then
1586
1587 ----------------------------------------------------------------------------
1588 l_debug_info := 'Primary Phone not null - create/update phone contact point';
1589 ----------------------------------------------------------------------------
1590 IF (PG_DEBUG = 'Y') THEN
1591 arp_standard.debug(l_debug_info);
1592 END IF;
1593
1594 l_contact_point_rec.contact_point_type := 'PHONE';
1595 l_contact_point_rec.contact_point_purpose := l_phone_purpose;
1596 --bug #3483248
1597 l_phone_rec.phone_number := l_primary_phone;
1598 l_phone_rec.phone_area_code := l_area_code;
1599 l_phone_rec.phone_country_code := l_country_code;
1600 l_phone_rec.phone_extension := l_phone_extension;
1601 l_phone_rec.phone_line_type := 'GEN';
1602
1603 ----------------------------------------------------------------------------
1604 l_debug_info := 'Check if a phone record already exists';
1605 ----------------------------------------------------------------------------
1606 IF (PG_DEBUG = 'Y') THEN
1607 arp_standard.debug(l_debug_info);
1608 END IF;
1609 BEGIN
1610 l_contact_point_id := null;
1611
1612 SELECT contact_point_id
1613 INTO l_contact_point_id
1614 FROM hz_contact_points
1615 WHERE owner_table_id = p_contact_party_id
1616 AND owner_table_name = 'HZ_PARTIES'
1617 AND status = 'A'
1618 AND primary_flag = 'Y'
1619 AND contact_point_type = 'PHONE';
1620 EXCEPTION
1621 WHEN TOO_MANY_ROWS THEN
1622 --Not possible
1623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1624 WHEN OTHERS THEN
1625 NULL;
1626 END;
1627
1628 IF (l_contact_point_id IS NULL) THEN
1629
1630 ----------------------------------------------------------------------------
1631 l_debug_info := 'Create phone contact point';
1632 ----------------------------------------------------------------------------
1633 IF (PG_DEBUG = 'Y') THEN
1634 arp_standard.debug(l_debug_info);
1635 END IF;
1636
1637 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1638 p_contact_point_rec => l_contact_point_rec,
1639 p_phone_rec => l_phone_rec,
1640 x_contact_point_id => l_contact_point_id,
1641 x_return_status => X_Return_Status,
1642 x_msg_count => X_Msg_Count,
1643 x_msg_data => X_Msg_Data );
1644 ELSE
1645
1646 ----------------------------------------------------------------------------
1647 l_debug_info := 'Update phone contact point';
1648 ----------------------------------------------------------------------------
1649 IF (PG_DEBUG = 'Y') THEN
1650 arp_standard.debug(l_debug_info);
1651 END IF;
1652
1653 SELECT object_version_number
1654 INTO l_object_version_number
1655 FROM HZ_CONTACT_POINTS
1656 WHERE contact_point_id = l_contact_point_id;
1657
1658 HZ_CONTACT_POINT_V2PUB.update_contact_point (
1659 p_contact_point_rec => l_contact_point_rec,
1660 p_phone_rec => l_phone_rec,
1661 p_object_version_number => l_object_version_number,
1662 x_return_status => X_Return_Status,
1663 x_msg_count => X_Msg_Count,
1664 x_msg_data => X_Msg_Data);
1665 END IF;
1666
1667 ----------------------------------------------------------------------------
1668 l_debug_info := 'Contact point done: Status' || X_Return_Status;
1669 ----------------------------------------------------------------------------
1670 IF (PG_DEBUG = 'Y') THEN
1671 arp_standard.debug(l_debug_info);
1672 END IF;
1673
1674 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1676 end if;
1677
1678 end if;
1679
1680 ----------------------------------------------------------------------------
1681 l_debug_info := 'In debug mode, log that we have exited this procedure';
1682 ----------------------------------------------------------------------------
1683 IF (PG_DEBUG = 'Y') THEN
1684 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1685 END IF;
1686
1687 EXCEPTION
1688 WHEN OTHERS THEN
1689 IF (SQLCODE <> -20001) THEN
1690 IF (PG_DEBUG = 'Y') THEN
1691 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1692 arp_standard.debug('Debug Info: ' || l_debug_info);
1693 arp_standard.debug(SQLERRM);
1694 END IF;
1695 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1696 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1697 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1698 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1699 FND_MSG_PUB.ADD;
1700 InformSysAdminError(p_procedure_name => l_procedure_name,
1701 p_debug_info => l_debug_info,
1702 p_error => SQLERRM);
1703 END IF;
1704 APP_EXCEPTION.RAISE_EXCEPTION;
1705
1706 END CreateContactPointInternal;
1707
1708 /* =======================================================================
1709 | PROCEDURE CreateCustAcctRoleFor
1710 |
1711 | DESCRIPTION
1712 |
1713 |
1714 | PARAMETERS
1715 |
1716 * ======================================================================*/
1717 --------------------------------------------------------------------------
1718 PROCEDURE CreateCustAcctRoleFor(p_event in out NOCOPY WF_EVENT_T,
1719 p_party_id IN NUMBER,
1720 p_cust_acct_role_id OUT NOCOPY NUMBER)
1721 --------------------------------------------------------------------------
1722 IS
1723 l_cust_acct_roles_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
1724 l_return_status VARCHAR2(1);
1725 l_msg_count NUMBER;
1726 l_msg_data VARCHAR2(2000);
1727 l_customer_id NUMBER;
1728 l_cust_acct_site_id NUMBER;
1729 l_cust_site_use_id NUMBER;
1730 l_already_exists VARCHAR2(10);
1731
1732 l_cust_acct_role_id NUMBER;
1733 l_procedure_name VARCHAR2(30) := '.CreateCustAcctRoleFor';
1734 l_debug_info VARCHAR2(200);
1735 l_status VARCHAR2(1);
1736 l_version_number NUMBER;
1737 BEGIN
1738
1739 ----------------------------------------------------------------------------
1740 l_debug_info := 'In debug mode, log we have entered this procedure';
1741 ----------------------------------------------------------------------------
1742 IF (PG_DEBUG = 'Y') THEN
1743 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1744 END IF;
1745
1746 l_customer_id := p_event.getvalueforparameter('CUSTOMER_ID');
1747 l_cust_site_use_id := p_event.getvalueforparameter('CUSTOMER_SITE_USE_ID');
1748
1749 l_cust_acct_site_id := GetCustAcctSiteId(p_cust_site_use_id => l_cust_site_use_id);
1750
1751 ----------------------------------------------------------------------------
1752 l_debug_info := 'Check if role already exists';
1753 ----------------------------------------------------------------------------
1754 IF (PG_DEBUG = 'Y') THEN
1755 arp_standard.debug(l_debug_info);
1756 END IF;
1757
1758 BEGIN
1759 l_already_exists := 'N';
1760 l_status:='A';
1761 --Bug 4764121: Activating a inactive role
1762 SELECT 'Y',a.cust_account_role_id,status,a.object_version_number
1763 INTO l_already_exists,p_cust_acct_role_id,l_status,l_version_number
1764 FROM hz_cust_account_roles a
1765 WHERE party_id = p_party_id
1766 AND cust_account_id = l_customer_id
1767 AND ((cust_acct_site_id is null and l_cust_acct_site_id is null )
1768 OR cust_acct_site_id = l_cust_acct_site_id )
1769 AND role_type = 'CONTACT'
1770 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(begin_date,SYSDATE))
1771 AND TRUNC(NVL(end_date,SYSDATE));
1772 IF l_status='I' THEN
1773 ----------------------------------------------------------------------------
1774 l_debug_info := 'Role exists but is Inactive.Trying to activate it.Role Id= '||p_cust_acct_role_id;
1775 ----------------------------------------------------------------------------
1776 IF (PG_DEBUG = 'Y') THEN
1777 arp_standard.debug(l_debug_info);
1778 END IF;
1779
1780 l_cust_acct_roles_rec.party_id := p_party_id;
1781 l_cust_acct_roles_rec.cust_account_id := l_customer_id;
1782 l_cust_acct_roles_rec.cust_acct_site_id := l_cust_acct_site_id;
1783 l_cust_acct_roles_rec.role_type := 'CONTACT';
1784 l_cust_acct_roles_rec.created_by_module := 'ARI';
1785 l_cust_acct_roles_rec.cust_account_role_id := p_cust_acct_role_id;
1786 l_cust_acct_roles_rec.status := 'A';
1787
1788 HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
1789 p_init_msg_list => FND_API.G_TRUE,
1790 p_cust_account_role_rec => l_cust_acct_roles_rec,
1791 x_return_status => l_return_status,
1792 x_msg_count => l_msg_count,
1793 x_msg_data => l_msg_data,
1794 p_object_version_number => l_version_number);
1795
1796 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1797
1798 ----------------------------------------------------------------------
1799 l_debug_info := 'Error Calling HZ Update Cust Acct Roles API: ' || l_msg_data;
1800 ----------------------------------------------------------------------
1801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1802
1803 END IF;
1804 END IF;
1805 EXCEPTION
1806 WHEN TOO_MANY_ROWS THEN
1807 --Role exists
1808 RETURN;
1809 WHEN OTHERS THEN
1810 NULL;
1811 END;
1812
1813 IF l_already_exists = 'N' THEN
1814
1815 ----------------------------------------------------------------------------
1816 l_debug_info := 'Create customer account role';
1817 ----------------------------------------------------------------------------
1818 IF (PG_DEBUG = 'Y') THEN
1819 arp_standard.debug(l_debug_info);
1820 END IF;
1821
1822 l_cust_acct_roles_rec.party_id := p_party_id;
1823 l_cust_acct_roles_rec.cust_account_id := l_customer_id;
1824 l_cust_acct_roles_rec.cust_acct_site_id := l_cust_acct_site_id;
1825 l_cust_acct_roles_rec.role_type := 'CONTACT';
1826 l_cust_acct_roles_rec.created_by_module := 'ARI';
1827
1828 ------------------------------------------------------------------------
1829 l_debug_info := 'Call hz_cust_account_v2pub.Create_Cust_Acct_Roles';
1830 ------------------------------------------------------------------------
1831 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Cust_Account_Role(
1832 p_init_msg_list => FND_API.G_TRUE,
1833 p_cust_account_role_rec => l_cust_acct_roles_rec,
1834 x_return_status => l_return_status,
1835 x_msg_count => l_msg_count,
1836 x_msg_data => l_msg_data,
1837 x_cust_account_role_id => l_cust_acct_role_id);
1838
1839 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1840 ----------------------------------------------------------------------
1841 l_debug_info := 'Return cust acct role id';
1842 ----------------------------------------------------------------------
1843 p_cust_acct_role_id := l_cust_acct_role_id;
1844 ELSE
1845
1846 ----------------------------------------------------------------------
1847 l_debug_info := 'Error Calling HZ Create Cust Acct Roles API: ' || l_msg_data;
1848 ----------------------------------------------------------------------
1849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1850
1851 END IF;
1852
1853 END IF;
1854
1855 ----------------------------------------------------------------------------
1856 l_debug_info := 'In debug mode, log that we have exited this procedure';
1857 ----------------------------------------------------------------------------
1858 IF (PG_DEBUG = 'Y') THEN
1859 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1860 END IF;
1861
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 IF (SQLCODE <> -20001) THEN
1865 IF (PG_DEBUG = 'Y') THEN
1866 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1867 arp_standard.debug('Debug Info: ' || l_debug_info);
1868 arp_standard.debug(SQLERRM);
1869 END IF;
1870 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1871 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1872 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1873 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1874 FND_MSG_PUB.ADD;
1875 InformSysAdminError(p_procedure_name => l_procedure_name,
1876 p_debug_info => l_debug_info,
1877 p_error => SQLERRM);
1878 END IF;
1879 APP_EXCEPTION.RAISE_EXCEPTION;
1880
1881 END CreateCustAcctRoleFor;
1882
1883
1884 /* =======================================================================
1885 | PROCEDURE CreateRoleRespFor
1886 |
1887 | DESCRIPTION
1888 |
1889 |
1890 | PARAMETERS
1891 |
1892 * ======================================================================*/
1893 --------------------------------------------------------------------------
1894 PROCEDURE CreateRoleRespFor(p_cust_acct_role_id IN NUMBER,
1895 p_role_resp_id OUT NOCOPY NUMBER)
1896 --------------------------------------------------------------------------
1897 IS
1898 l_role_resp_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.role_responsibility_rec_type;
1899 l_return_status VARCHAR2(1);
1900 l_msg_count NUMBER;
1901 l_msg_data VARCHAR2(2000);
1902 l_responsibility_id NUMBER;
1903 l_procedure_name VARCHAR2(30) := '.CreateRoleRespFor';
1904 l_debug_info VARCHAR2(200);
1905 l_already_exists VARCHAR2(10);
1906 BEGIN
1907
1908 ----------------------------------------------------------------------------
1909 l_debug_info := 'In debug mode, log we have entered this procedure';
1910 ----------------------------------------------------------------------------
1911 IF (PG_DEBUG = 'Y') THEN
1912 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1913 END IF;
1914
1915 ----------------------------------------------------------------------------
1916 l_debug_info := 'Check if role responsbility already exists';
1917 ----------------------------------------------------------------------------
1918 IF (PG_DEBUG = 'Y') THEN
1919 arp_standard.debug(l_debug_info);
1920 END IF;
1921
1922 BEGIN
1923 l_already_exists := 'N';
1924
1925 SELECT 'Y'
1926 INTO l_already_exists
1927 FROM hz_role_responsibility
1928 WHERE cust_account_role_id = p_cust_acct_role_id
1929 AND responsibility_type = 'SELF_SERVICE_USER';
1930
1931 EXCEPTION
1932 WHEN TOO_MANY_ROWS THEN
1933 --Role exists
1934 RETURN;
1935 WHEN OTHERS THEN
1936 NULL;
1937 END;
1938
1939 IF l_already_exists = 'N' THEN
1940
1941 l_role_resp_rec.cust_account_role_id := p_cust_acct_role_id;
1942 l_role_resp_rec.responsibility_type := 'SELF_SERVICE_USER';
1943 l_role_resp_rec.created_by_module := 'ARI';
1944
1945 --------------------------------------------------------------------
1946 l_debug_info := 'Call HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility';
1947 --------------------------------------------------------------------
1948 IF (PG_DEBUG = 'Y') THEN
1949 arp_standard.debug(l_debug_info);
1950 END IF;
1951 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility(
1952 p_init_msg_list => FND_API.G_TRUE,
1953 p_role_responsibility_rec => l_role_resp_rec,
1954 x_return_status => l_return_status,
1955 x_msg_count => l_msg_count,
1956 x_msg_data => l_msg_data,
1957 x_responsibility_id => l_responsibility_id);
1958
1959 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1960 ----------------------------------------------------------------------
1961 l_debug_info := 'Return role responsibility id';
1962 ----------------------------------------------------------------------
1963 IF (PG_DEBUG = 'Y') THEN
1964 arp_standard.debug(l_debug_info);
1965 END IF;
1966 p_role_resp_id := l_responsibility_id;
1967 ELSE
1968 ----------------------------------------------------------------------
1969 l_debug_info := 'Error Calling HZ Create Role Resp API: ' || l_msg_data;
1970 ----------------------------------------------------------------------
1971 IF (PG_DEBUG = 'Y') THEN
1972 arp_standard.debug(l_debug_info);
1973 END IF;
1974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1975
1976 END IF;
1977
1978 END IF;
1979
1980 ----------------------------------------------------------------------------
1981 l_debug_info := 'In debug mode, log that we have exited this procedure';
1982 ----------------------------------------------------------------------------
1983 IF (PG_DEBUG = 'Y') THEN
1984 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1985 END IF;
1986
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 IF (SQLCODE <> -20001) THEN
1990 IF (PG_DEBUG = 'Y') THEN
1991 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1992 arp_standard.debug('Debug Info: ' || l_debug_info);
1993 arp_standard.debug(SQLERRM);
1994 END IF;
1995 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1996 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1997 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1998 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1999 FND_MSG_PUB.ADD;
2000 InformSysAdminError(p_procedure_name => l_procedure_name,
2001 p_debug_info => l_debug_info,
2002 p_error => SQLERRM);
2003 END IF;
2004 APP_EXCEPTION.RAISE_EXCEPTION;
2005 END CreateRoleRespFor;
2006
2007 /*=======================================================================
2008 | PROCEDURE RegisterB2BUser
2009 |
2010 | DESCRIPTION
2011 |
2012 |
2013 | PARAMETERS
2014 |
2015 * ======================================================================*/
2016 ---------------------------------------------------------------------------
2017 PROCEDURE RegisterB2BUser( p_event IN OUT NOCOPY WF_EVENT_T,
2018 p_person_party_id IN OUT NOCOPY VARCHAR2)
2019 ---------------------------------------------------------------------------
2020 IS
2021 l_party_id NUMBER;
2022 l_cust_acct_role_id HZ_CUST_ACCOUNT_ROLES.cust_account_role_id%type;
2023 l_role_resp_id HZ_ROLE_RESPONSIBILITY.responsibility_id%type;
2024 l_cust_acct_type VARCHAR2(20);
2025 l_procedure_name VARCHAR2(50) := '.RegisterB2BUser';
2026 l_debug_info VARCHAR2(300);
2027
2028 X_Return_Status VARCHAR2(20);
2029 X_Msg_Count NUMBER;
2030 X_Msg_data VARCHAR2(300);
2031
2032 BEGIN
2033
2034 ----------------------------------------------------------------------------
2035 l_debug_info := 'In debug mode, log we have entered this procedure';
2036 ----------------------------------------------------------------------------
2037 IF (PG_DEBUG = 'Y') THEN
2038 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2039 END IF;
2040
2041 IF (p_person_party_id IS NULL) THEN
2042 CreatePersonPartyInternal(p_event,p_person_party_id);
2043 END IF;
2044 CreateOrgContactInternal(p_event, p_person_party_id, l_party_id);
2045 CreateContactPointInternal(p_event,to_char(l_party_id));
2046 CreateCustAcctRoleFor(p_event, l_party_id, l_cust_acct_role_id);
2047 CreateRoleRespFor(l_cust_acct_role_id, l_role_resp_id);
2048
2049 ----------------------------------------------------------------------------
2050 l_debug_info := 'In debug mode, log that we have exited this procedure';
2051 ----------------------------------------------------------------------------
2052 IF (PG_DEBUG = 'Y') THEN
2053 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2054 END IF;
2055
2056 END RegisterB2BUser;
2057
2058 /*=======================================================================
2059 | PROCEDURE RegisterB2CUser
2060 |
2061 | DESCRIPTION
2062 |
2063 |
2064 | PARAMETERS
2065 |
2066 * ======================================================================*/
2067 ---------------------------------------------------------------------------
2068 PROCEDURE RegisterB2CUser( p_event IN OUT NOCOPY WF_EVENT_T,
2069 p_person_party_id IN OUT NOCOPY varchar2 )
2070 ---------------------------------------------------------------------------
2071 IS
2072 l_contact_preference_rec HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE;
2073 l_contact_preference VARCHAR2(5);
2074 l_contact_preference_id number;
2075 l_party_id NUMBER;
2076 l_cust_acct_role_id HZ_CUST_ACCOUNT_ROLES.cust_account_role_id%type;
2077 l_role_resp_id HZ_ROLE_RESPONSIBILITY.responsibility_id%type;
2078 l_cust_acct_type VARCHAR2(20);
2079 l_new_user VARCHAR2(10);
2080
2081 l_procedure_name VARCHAR2(50) := '.RegisterB2CUser';
2082 l_debug_info VARCHAR2(300);
2083
2084
2085 X_Return_Status VARCHAR2(20);
2086 X_Msg_Count NUMBER;
2087 X_Msg_data VARCHAR2(300);
2088
2089 BEGIN
2090
2091 ----------------------------------------------------------------------------
2092 l_debug_info := 'In debug mode, log we have entered this procedure';
2093 ----------------------------------------------------------------------------
2094 IF (PG_DEBUG = 'Y') THEN
2095 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2096 END IF;
2097
2098 IF (p_person_party_id IS NULL) THEN
2099 l_new_user := 'Y';
2100 CreatePersonPartyInternal(p_event,p_person_party_id);
2101 END IF;
2102 CreateContactPointInternal(p_event,p_person_party_id);
2103 CreateOrgContactInternal(p_event, p_person_party_id, l_party_id);
2104 CreateCustAcctRoleFor(p_event, l_party_id, l_cust_acct_role_id);
2105 CreateRoleRespFor(l_cust_acct_role_id, l_role_resp_id);
2106
2107 --populate contact preference if its a new user
2108 IF (l_new_user = 'Y') THEN
2109 l_contact_preference := p_event.getvalueforparameter('CONTACT_PREFERENCE');
2110
2111 IF (l_contact_preference = 'Y') THEN
2112 l_contact_preference_rec.preference_code := 'DO';
2113 ELSE
2114 l_contact_preference_rec.preference_code := 'DO_NOT';
2115 END IF;
2116
2117 l_contact_preference_rec.contact_level_table := 'HZ_PARTIES';
2118 l_contact_preference_rec.contact_level_table_id := p_person_party_id;
2119 l_contact_preference_rec.contact_type := 'EMAIL';
2120 l_contact_preference_rec.requested_by := 'INTERNAL';
2121 l_contact_preference_rec.created_by_module := 'ARI';
2122 l_contact_preference_rec.application_id := 0;
2123
2124 ----------------------------------------------------------------------------
2125 l_debug_info := 'Create contact preference';
2126 ----------------------------------------------------------------------------
2127 IF (PG_DEBUG = 'Y') THEN
2128 arp_standard.debug(l_debug_info);
2129 END IF;
2130
2131 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference(
2132 p_contact_preference_rec => l_contact_preference_rec,
2133 x_contact_preference_id => l_contact_preference_id,
2134 x_return_status => x_return_status,
2135 x_msg_count => x_msg_count,
2136 x_msg_data => x_msg_data
2137 );
2138
2139 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141 END IF;
2142 END IF;
2143
2144 ----------------------------------------------------------------------------
2145 l_debug_info := 'In debug mode, log that we have exited this procedure';
2146 ----------------------------------------------------------------------------
2147 IF (PG_DEBUG = 'Y') THEN
2148 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2149 END IF;
2150
2151 END RegisterB2CUser;
2152
2153 /*=======================================================================
2154 | PROCUDURE RegisterUser
2155 |
2156 | DESCRIPTION
2157 |
2158 |
2159 | PARAMETERS
2160 |
2161 * ======================================================================*/
2162 ---------------------------------------------------------------------------
2163 PROCEDURE RegisterUser( p_event IN OUT NOCOPY WF_EVENT_T,
2164 p_person_party_id IN OUT NOCOPY varchar2 )
2165 ---------------------------------------------------------------------------
2166 IS
2167 l_cust_acct_type VARCHAR2(20);
2168 l_procedure_name VARCHAR2(50) := '.RegisterUser';
2169 l_debug_info VARCHAR2(300);
2170 BEGIN
2171
2172 ----------------------------------------------------------------------------
2173 l_debug_info := 'In debug mode, log we have entered this procedure';
2174 ----------------------------------------------------------------------------
2175 IF (PG_DEBUG = 'Y') THEN
2176 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2177 END IF;
2178
2179 ----------------------------------------------------------------------------
2180 l_debug_info := 'Check if the customer access requested if of BUSINESS or CONSUMER type';
2181 ----------------------------------------------------------------------------
2182 IF (PG_DEBUG = 'Y') THEN
2183 arp_standard.debug(l_debug_info);
2184 END IF;
2185
2186 l_cust_acct_type := p_event.getvalueforparameter('CUST_ACCT_TYPE');
2187
2188 IF (l_cust_acct_type = G_BUSINESS) THEN
2189 RegisterB2BUser(p_event, p_person_party_id);
2190 ELSIF (l_cust_acct_type = G_CONSUMER) THEN
2191 RegisterB2CUser(p_event, p_person_party_id);
2192 END IF;
2193
2194 ----------------------------------------------------------------------------
2195 l_debug_info := 'In debug mode, log we have entered this procedure';
2196 ----------------------------------------------------------------------------
2197 IF (PG_DEBUG = 'Y') THEN
2198 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2199 END IF;
2200
2201 END RegisterUser;
2202
2203 /*=======================================================================
2204 | FUNCTION AddCustomerAccess
2205 |
2206 | DESCRIPTION
2207 |
2208 |
2209 | PARAMETERS
2210 |
2211 * ======================================================================*/
2212 ---------------------------------------------------------------------------
2213 FUNCTION AddCustomerAccess(p_subscription_guid in raw,
2214 p_event in out NOCOPY WF_EVENT_T)
2215 RETURN VARCHAR2
2216 ---------------------------------------------------------------------------
2217 IS
2218 l_success VARCHAR2(10);
2219 l_reg_service_type VARCHAR2(50);
2220 l_person_party_id VARCHAR2(50);
2221 l_temp1 VARCHAR2(100);
2222 l_temp2 VARCHAr2(100);
2223
2224 l_procedure_name VARCHAR2(50) := '.AddCustomerAccess';
2225 l_debug_info VARCHAR2(300);
2226
2227 BEGIN
2228
2229 ----------------------------------------------------------------------------
2230 l_debug_info := 'In debug mode, log we have entered this procedure';
2231 ----------------------------------------------------------------------------
2232 IF (PG_DEBUG = 'Y') THEN
2233 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2234 END IF;
2235
2236 -- This function is called on the event 'oracle.apps.fnd.umx.requestapproved'
2237 -- To ensure that the code to add access gets executed only for Add Access flow
2238 -- Check for the REG_SERVICE_TYPE of the registration process.
2239
2240 l_reg_service_type := p_event.getvalueforparameter('REG_SERVICE_TYPE');
2241
2242 IF ((l_reg_service_type = 'ADDITIONAL_ACCESS' AND
2243 p_event.getValueForParameter('UMX_CUSTOM_EVENT_CONTEXT') = UMX_PUB.ROLE_APPROVED)
2244 --Bug 4764121
2245 OR l_reg_service_type = 'ARI_ADD_CUST_ACCESS' ) THEN
2246
2247 ----------------------------------------------------------------------------
2248 l_debug_info := 'Add access for the user';
2249 ----------------------------------------------------------------------------
2250 IF (PG_DEBUG = 'Y') THEN
2251 arp_standard.debug(l_debug_info);
2252 END IF;
2253 l_person_party_id := p_event.getvalueforparameter('PERSON_PARTY_ID');
2254
2255 RegisterUser(p_event, l_person_party_id);
2256
2257 END IF;
2258
2259 ----------------------------------------------------------------------------
2260 l_debug_info := 'In debug mode, log we have entered this procedure';
2261 ----------------------------------------------------------------------------
2262 IF (PG_DEBUG = 'Y') THEN
2263 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2264 END IF;
2265
2266 l_success := 'success';
2267 RETURN l_success;
2268
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'AddCustomerAccess',
2272 p_event.getEventName( ),p_subscription_guid,
2273 sqlerrm,sqlcode);
2274 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2275 raise;
2276 return 'ERROR';
2277
2278 END AddCustomerAccess;
2279
2280 /*=======================================================================
2281 | FUNCTION CreatePersonParty
2282 |
2283 | DESCRIPTION
2284 |
2285 |
2286 | PARAMETERS
2287 |
2288 * ======================================================================*/
2289 ---------------------------------------------------------------------------
2290 FUNCTION CreatePersonParty(p_subscription_guid in raw,
2291 p_event in out NOCOPY WF_EVENT_T)
2292 RETURN VARCHAR2
2293 ---------------------------------------------------------------------------
2294 IS
2295
2296 l_first_name hz_parties.person_first_name%type;
2297 l_last_name hz_parties.person_last_name%type;
2298 l_middle_name hz_parties.person_middle_name%type;
2299 l_pre_name_adjunct hz_parties.person_pre_name_adjunct%type;
2300 l_person_name_suffix hz_parties.person_name_suffix%type;
2301
2302 l_party_number hz_parties.party_number%type;
2303 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
2304 l_profile_id NUMBER;
2305 l_success VARCHAR2(10);
2306 p_person_party_id varchar2(30);
2307 l_procedure_name VARCHAR2(50) := '.CreateContactPointInternal';
2308 l_debug_info VARCHAR2(300);
2309
2310 X_Return_Status VARCHAR2(20);
2311 X_Msg_Count NUMBER;
2312 X_Msg_data VARCHAR2(300);
2313
2314 BEGIN
2315
2316 ----------------------------------------------------------------------------
2317 l_debug_info := 'In debug mode, log we have entered this procedure';
2318 ----------------------------------------------------------------------------
2319 IF (PG_DEBUG = 'Y') THEN
2320 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2321 END IF;
2322
2323 ----------------------------------------------------------------------------
2324 l_debug_info := 'Create B2C party and populate the event object back to main workflow';
2325 ----------------------------------------------------------------------------
2326 IF (PG_DEBUG = 'Y') THEN
2327 arp_standard.debug(l_debug_info);
2328 END IF;
2329
2330 if(p_event.getValueForParameter('UMX_CUSTOM_EVENT_CONTEXT') =
2331 UMX_PUB.BEFORE_ACT_ACTIVATION) then
2332
2333 RegisterUser(p_event,p_person_party_id);
2334
2335 ----------------------------------------------------------------------------
2336 l_debug_info := 'Person Party Created:' || p_person_party_id;
2337 ----------------------------------------------------------------------------
2338 IF (PG_DEBUG = 'Y') THEN
2339 arp_standard.debug(l_debug_info);
2340 END IF;
2341
2342 l_success := UMX_REGISTRATION_UTIL.set_event_object(p_event,'PERSON_PARTY_ID',p_person_party_id);
2343
2344 end if;
2345
2346 ----------------------------------------------------------------------------
2347 l_debug_info := 'In debug mode, log that we have exited this procedure';
2348 ----------------------------------------------------------------------------
2349 IF (PG_DEBUG = 'Y') THEN
2350 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2351 END IF;
2352
2353 IF l_success <> 'success' THEN
2354 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'CREATE_PERSON_PARTY',
2355 p_event.getEventName( ),p_subscription_guid,sqlerrm,sqlcode);
2356 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2357 END IF;
2358
2359 return l_success;
2360
2361 EXCEPTION
2362 WHEN OTHERS THEN
2363 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'CREATE_PERSON_PARTY',
2364 p_event.getEventName( ),p_subscription_guid,
2365 sqlerrm,sqlcode);
2366 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2367 raise;
2368 return 'ERROR';
2369
2370 END CreatePersonParty;
2371
2372 /*=======================================================================
2373 | FUNCTION RaiseAddCustAccessEvent
2374 |
2375 | DESCRIPTION
2376 |
2377 |
2378 | PARAMETERS
2379 |
2380 * ======================================================================*/
2381 ---------------------------------------------------------------------------
2382 PROCEDURE RaiseAddCustAccessEvent (p_person_party_id IN VARCHAR2,
2383 p_customer_id IN VARCHAR2,
2384 p_cust_site_use_id IN VARCHAR2 DEFAULT NULL,
2385 p_cust_acct_type IN VARCHAR2,
2386 p_first_name IN VARCHAR2,
2387 p_last_name IN VARCHAR2,
2388 p_middle_name IN VARCHAR2,
2389 p_pre_name_adjunct IN VARCHAR2,
2390 p_person_suffix IN VARCHAR2)
2391 ---------------------------------------------------------------------------
2392 IS
2393 l_parameter_list wf_parameter_list_t;
2394 l_event_name VARCHAR2(100);
2395 l_item_key VARCHAR2(2000);
2396 l_user_name VARCHAR2(100);
2397 BEGIN
2398
2399 wf_event.addParametertoList('PERSON_PARTY_ID', p_person_party_id, l_parameter_list);
2400 wf_event.addParametertoList('CUSTOMER_ID', p_customer_id, l_parameter_list);
2401 wf_event.addParametertoList('CUSTOMER_SITE_USE_ID', p_cust_site_use_id, l_parameter_list);
2402 wf_event.addParametertoList('REG_SERVICE_TYPE', 'ARI_ADD_CUST_ACCESS', l_parameter_list);
2403 wf_event.addParametertoList('CUST_ACCT_TYPE', p_cust_acct_type, l_parameter_list);
2404 wf_event.addParametertoList('FIRST_NAME', p_first_name, l_parameter_list);
2405 wf_event.addParametertoList('LAST_NAME', p_last_name, l_parameter_list);
2406 wf_event.addParametertoList('MIDDLE_NAME', p_middle_name, l_parameter_list);
2407 wf_event.addParametertoList('PRE_NAME_ADJUNCT', p_pre_name_adjunct, l_parameter_list);
2408 wf_event.addParametertoList('PERSON_SUFFIX', p_person_suffix, l_parameter_list);
2409 IF p_person_party_id IS NULL THEN
2410 SELECT usr.user_name into l_user_name
2411 FROM fnd_user usr
2412 WHERE Usr.user_id = fnd_global.user_id;
2413 wf_event.addParametertoList('REQUESTED_USERNAME', l_user_name, l_parameter_list);
2414 END IF;
2415 l_event_name := 'oracle.apps.ar.irec.addcustaccess';
2416 SELECT UMX_REG_REQUESTS_S.nextval INTO l_item_key FROM dual;
2417 wf_event.raise(l_event_name,l_item_key,null,l_parameter_list,sysdate);
2418
2419 END RaiseAddCustAccessEvent;
2420
2421 /*=======================================================================
2422 | FUNCTION GetRegSecurityProfile
2423 |
2424 | DESCRIPTION
2425 |
2426 |
2427 | PARAMETERS
2428 |
2429 * ======================================================================*/
2430 ---------------------------------------------------------------------------
2431 FUNCTION GetRegSecurityProfile(p_user_id IN VARCHAR2 DEFAULT NULL,
2432 p_resp_id IN VARCHAR2)
2433 RETURN VARCHAR2
2434 ---------------------------------------------------------------------------
2435 IS
2436 l_reg_sec_profile VARCHAR2(15) := 0;
2437 BEGIN
2438
2439 IF p_user_id IS NOT NULL THEN
2440 BEGIN
2441 select fpov.profile_option_value
2442 into l_reg_sec_profile
2443 from fnd_profile_option_values fpov, fnd_profile_options fpo
2444 where fpov.profile_option_id = fpo.profile_option_id
2445 and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
2446 and fpov.level_id = 10004 -- user level
2447 and fpov.level_value = p_user_id;
2448 EXCEPTION
2449 WHEN OTHERS THEN
2450 NULL;
2451 END;
2452 END IF;
2453
2454 IF l_reg_sec_profile <> 0 THEN
2455 RETURN l_reg_sec_profile;
2456 END IF;
2457
2458 BEGIN
2459 --Get the security profile value set up for the
2460 --iReceivables Registration responsibility
2461 select fpov.profile_option_value
2462 into l_reg_sec_profile
2463 from fnd_profile_option_values fpov, fnd_profile_options fpo
2464 where fpov.profile_option_id = fpo.profile_option_id
2465 and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
2466 and fpov.level_id = 10003 -- responsibility level
2467 and fpov.level_value_application_id = 222
2468 and fpov.level_value = p_resp_id; -- Resp Id of ARI_REGISTER_RESP- iReceivables Registration Responsibility
2469 EXCEPTION
2470 WHEN NO_DATA_FOUND THEN
2471 --Get the security profile set up at the site level
2472 select fpov.profile_option_value
2473 into l_reg_sec_profile
2474 from fnd_profile_option_values fpov, fnd_profile_options fpo
2475 where fpov.profile_option_id = fpo.profile_option_id
2476 and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
2477 and fpov.level_id = 10001; -- Site level
2478
2479 return l_reg_sec_profile;
2480 END;
2481
2482 return l_reg_sec_profile;
2483
2484 EXCEPTION
2485 WHEN OTHERS THEN
2486 return l_reg_sec_profile;
2487 END GetRegSecurityProfile;
2488
2489 /*=======================================================================
2490 | PROCEDURE ValidateRequestedCustomer
2491 |
2492 | DESCRIPTION
2493 |
2494 |
2495 | PARAMETERS
2496 |
2497 * ======================================================================*/
2498 ---------------------------------------------------------------------------
2499 PROCEDURE ValidateRequestedCustomer (p_customer_id IN VARCHAR2,
2500 x_return_status OUT NOCOPY VARCHAR2)
2501 ---------------------------------------------------------------------------
2502 IS
2503 l_count_sites NUMBER;
2504 BEGIN
2505 x_return_status := 'N';
2506
2507 SELECT count(cust_acct_site_id)
2508 INTO l_count_sites
2509 FROM hz_cust_acct_sites hcas
2510 WHERE hcaS.cust_account_id = p_customer_id;
2511
2512 IF l_count_sites > 0 THEN
2513 x_return_status := 'Y';
2514 END IF;
2515 EXCEPTION
2516 WHEN OTHERS THEN
2517 x_return_status := 'N';
2518 END ValidateRequestedCustomer;
2519
2520 /*=======================================================================
2521 | PROCEDURE GetRequestedRespId
2522 |
2523 | DESCRIPTION
2524 |
2525 |
2526 | PARAMETERS
2527 |
2528 * ======================================================================*/
2529 ---------------------------------------------------------------------------
2530 FUNCTION GetRequestedRespId (p_role_name IN VARCHAR2)
2531 RETURN VARCHAR2
2532 ---------------------------------------------------------------------------
2533 IS
2534 l_resp_id VARCHAR2(30) := 0;
2535 BEGIN
2536
2537 SELECT to_char(resp.responsibility_id)
2538 INTO l_resp_id
2539 FROM fnd_responsibility_vl resp, wf_roles role
2540 WHERE role.name = p_role_name
2541 AND resp.responsibility_name = role.display_name;
2542
2543 RETURN l_resp_id;
2544
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 RETURN l_resp_id;
2548 END GetRequestedRespId;
2549
2550
2551 END ARI_SELF_REGISTRATION_PKG;