[Home] [Help]
PACKAGE BODY: APPS.ARI_SELF_REGISTRATION_PKG
Source
1 PACKAGE BODY ARI_SELF_REGISTRATION_PKG AS
2 /* $Header: ARISREGB.pls 120.34.12020000.3 2013/03/13 09:20:16 melapaku ship $ */
3
4
5 /*=======================================================================+
6 | Global Constants
7 +=======================================================================*/
8
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARI_SELF_REGISTRATION_PKG';
10 G_CREATED_BY_MODULE CONSTANT VARCHAR2(5) := 'ARI';
11
12 ------------------------------------------------------------------------
13 -- Procedure Result Codes
14 ------------------------------------------------------------------------
15 G_NO_ROWS CONSTANT NUMBER := 0;
16 G_EXACT_MATCH CONSTANT NUMBER := 1;
17 G_BOTH_FOUND CONSTANT NUMBER := 2;
18 G_PERSON_FOUND CONSTANT NUMBER := 3;
19 G_BUSINESS_PERSON_FOUND CONSTANT NUMBER := 4;
20 G_FUZZY_MATCH CONSTANT NUMBER := 5;
21 G_NOT_UNIQUE CONSTANT NUMBER := 6;
22 G_COMPANY_DIFF CONSTANT NUMBER := -1;
23 G_PERSON_DIFF CONSTANT NUMBER := -2;
24 G_INVALID_PARTY_ID CONSTANT NUMBER := -3;
25 G_PERSON_NO_SIBLING CONSTANT NUMBER := -4;
26 G_BUSINESS_PERSON_NO_SIBLING CONSTANT NUMBER := -5;
27 G_NO_MATCH CONSTANT NUMBER := -6;
28 G_PERSON_NOT_UNIQUE CONSTANT NUMBER := -7;
29 G_BUSINESS_PERSON_NOT_UNIQUE CONSTANT NUMBER := -8;
30 G_FIRST_DIFF CONSTANT NUMBER := -9;
31 G_FAMILY_DIFF CONSTANT NUMBER := -10;
32
33 --------------------------------------------------------------------------
34 -- Registration Status Constants
35 --------------------------------------------------------------------------
36 G_HOLD CONSTANT VARCHAR2(4) := 'HOLD';
37 G_REGISTERED CONSTANT VARCHAR2(10) := 'REGISTERED';
38 G_NEW CONSTANT VARCHAR2(3) := 'NEW';
39 G_NEW_ACCESS CONSTANT VARCHAR2(10) := 'NEW ACCESS';
40 G_RETRY CONSTANT VARCHAR2(5) := 'RETRY';
41 G_NEW_ACCESS_RETRY CONSTANT VARCHAR2(20) := 'NEW ACCESS RETRY';
42 G_CREATE_USER_REQUESTED CONSTANT VARCHAR2(25) := 'CREATE_USER_REQUESTED';
43
44 --------------------------------------------------------------------------
45 -- Access Domain Type Constants
46 --------------------------------------------------------------------------
47 G_INVOICE_NUM CONSTANT VARCHAR2(15) := 'INVOICE_NUM';
48 G_CUST_ACCT_NUM CONSTANT VARCHAR2(15) := 'CUST_ACCT_NUM';
49
50 G_CUST_ACCT_HOLD CONSTANT VARCHAR2(15) := 'CUST_ACCT_HOLD';
51 G_USER_HOLD CONSTANT VARCHAR2(10) := 'USER_HOLD';
52 G_BILLTO_SITE_HOLD CONSTANT VARCHAR2(20) := 'BILLTO_SITE_HOLD';
53 G_USER_REG_COUNT CONSTANT VARCHAR2(15) := 'USER_REG_COUNT';
54 G_CUST_ACCT_N_USER CONSTANT VARCHAR2(20) := 'CUST_ACCT_N_USER';
55
56 G_RECEIPT_DATE CONSTANT VARCHAR2(15) := 'RECEIPT_DATE';
57 G_RECEIPT_AMT CONSTANT VARCHAR2(15) := 'RECEIPT_AMT';
58
59 G_ORGANIZATION CONSTANT VARCHAR2(15) := 'ORGANIZATION';
60 G_PERSON CONSTANT VARCHAR2(10) := 'PERSON';
61
62 G_BUSINESS CONSTANT VARCHAR2(10) := 'BUSINESS';
63 G_CONSUMER CONSTANT VARCHAR2(10) := 'CONSUMER';
64
65 --------------------------------------------------------------------------
66 -- Local Procedure Signature
67 --------------------------------------------------------------------------
68 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
69
70 PROCEDURE InformSysAdminError(p_procedure_name IN VARCHAR2,
71 p_debug_info IN VARCHAR2,
72 p_error IN VARCHAR2);
73
74
75 /* =======================================================================
76 | PROCEDURE ResolveCustomerAccessRequest
77 |
78 | DESCRIPTION
79 |
80 |
81 | PARAMETERS
82 |
83 * ======================================================================*/
84 ---------------------------------------------------------------------------
85 PROCEDURE ResolveCustomerAccessRequest(p_customer_id IN VARCHAR2,
86 x_cust_acct_type OUT NOCOPY VARCHAR2,
87 x_result_code OUT NOCOPY NUMBER)
88 ---------------------------------------------------------------------------
89 IS
90 l_cust_acct_cur GenCursorRef;
91 l_cust_acct_id NUMBER;
92 l_cust_acct_number VARCHAR2(50);
93 l_party_id NUMBER;
94 l_party_type HZ_PARTIES.party_type%TYPE;
95 l_procedure_name VARCHAR2(30) := '.ResolveCustomerAccessRequest';
96 l_debug_info VARCHAR2(200);
97 BEGIN
98
99 --------------------------------------------------------------------
100 l_debug_info := 'In debug mode, log we have entered this procedure';
101 --------------------------------------------------------------------
102 IF (PG_DEBUG = 'Y') THEN
103 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
104 END IF;
105
106 --------------------------------------------------------------------------
107 l_debug_info := 'Calling OpenCustAcctCur';
108 ---------------------------------------------------------------------------
109 IF (PG_DEBUG = 'Y') THEN
110 arp_standard.debug(l_debug_info);
111 END IF;
112 OpenCustAcctCur(p_customer_id => p_customer_id,
113 p_cust_acct_cur => l_cust_acct_cur);
114
115 ------------------------------------------------------------------------
116 l_debug_info := 'Fetching the results of the cursor';
117 ------------------------------------------------------------------------
118 IF (PG_DEBUG = 'Y') THEN
119 arp_standard.debug(l_debug_info);
120 END IF;
121 ------------------------------------------------------------------------
122 LOOP
123 FETCH l_cust_acct_cur INTO l_cust_acct_id,
124 l_cust_acct_number,
125 l_party_id,
126 l_party_type;
127
128 EXIT WHEN l_cust_acct_cur%NOTFOUND;
129 END LOOP;
130
131 IF (l_cust_acct_cur%ROWCOUNT = 1) THEN
132 ----------------------------------------------------------------
133 l_debug_info := 'Exact match on customer_id provided';
134 ----------------------------------------------------------------
135 IF (PG_DEBUG = 'Y') THEN
136 arp_standard.debug(l_debug_info);
137 END IF;
138
139 x_result_code := G_EXACT_MATCH;
140
141 IF (l_party_type = G_ORGANIZATION) THEN
142 x_cust_acct_type := G_BUSINESS;
143 ELSE
144 x_cust_acct_type := G_CONSUMER;
145 END IF;
146
147 ELSIF (l_cust_acct_cur%ROWCOUNT > 1) THEN
148 ----------------------------------------------------------------------
149 l_debug_info := 'Non-unique match on customer_id provided';
150 ----------------------------------------------------------------------
151 IF (PG_DEBUG = 'Y') THEN
152 arp_standard.debug(l_debug_info);
153 END IF;
154 x_result_code := G_NOT_UNIQUE;
155
156 ELSE
157 -------------------------------------------------------------------------
158 l_debug_info := 'No record found based on customer_id provided';
159 -------------------------------------------------------------------------
160 IF (PG_DEBUG = 'Y') THEN
161 arp_standard.debug(l_debug_info);
162 END IF;
163 x_result_code := G_NO_ROWS;
164
165 END IF;
166
167 ------------------------------------------------------------------------
168 l_debug_info := 'Close Cust Account Cursor';
169 ------------------------------------------------------------------------
170 IF (PG_DEBUG = 'Y') THEN
171 arp_standard.debug(l_debug_info);
172 END IF;
173 CLOSE l_cust_acct_cur;
174
175 --------------------------------------------------------------------
176 l_debug_info := 'In debug mode, log we have exited this procedure';
177 --------------------------------------------------------------------
178 IF (PG_DEBUG = 'Y') THEN
179 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
180 END IF;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 IF (SQLCODE <> -20001) THEN
185 IF (PG_DEBUG = 'Y') THEN
186 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
187 arp_standard.debug('Debug Info: ' || l_debug_info);
188 arp_standard.debug(SQLERRM);
189 END IF;
190 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
191 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
192 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
193 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
194 FND_MSG_PUB.ADD;
195 InformSysAdminError(p_procedure_name => l_procedure_name,
196 p_debug_info => l_debug_info,
197 p_error => SQLERRM);
198 END IF;
199 APP_EXCEPTION.RAISE_EXCEPTION;
200 END ResolveCustomerAccessRequest;
201
202 /* =======================================================================
203 | PROCEDURE InitiateHZUserCreation
204 |
205 | DESCRIPTION
206 |
207 |
208 | PARAMETERS
209 |
210 * ======================================================================*/
211 ---------------------------------------------------------------------------
212 PROCEDURE InitiateHZUserCreation(p_registration_id IN NUMBER,
213 p_user_email_addr IN VARCHAR2,
214 p_cust_acct_type IN VARCHAR2,
215 p_company_id IN NUMBER DEFAULT NULL,
216 p_access_domain_id IN NUMBER,
217 p_access_domain_number IN VARCHAR2,
218 p_person_id IN NUMBER DEFAULT NULL,
219 p_first_name IN VARCHAR2 DEFAULT NULL,
220 p_family_name IN VARCHAR2 DEFAULT NULL,
221 p_job_title IN VARCHAR2 DEFAULT NULL,
222 p_phone_country_code IN VARCHAR2 DEFAULT NULL,
223 p_area_code IN VARCHAR2 DEFAULT NULL,
224 p_phone_number IN VARCHAR2 DEFAULT NULL,
225 p_extension IN VARCHAR2 DEFAULT NULL,
226 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
227 p_reg_service_code IN VARCHAR2 DEFAULT 'FND_RESP|AR|ARI_EXTERNAL|STAND',
228 p_identity_verification_reqd IN VARCHAR2 DEFAULT NULL,
229 p_requested_username IN VARCHAR2 DEFAULT NULL,
230 p_justification IN VARCHAR2 DEFAULT NULL,
231 p_req_start_date IN DATE DEFAULT SYSDATE,
232 p_req_end_date IN DATE DEFAULT NULL,
233 p_ame_application_id IN VARCHAR2 DEFAULT NULL,
234 p_ame_trx_type_id IN VARCHAR2 DEFAULT NULL,
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_count OUT NOCOPY NUMBER,
237 x_msg_data OUT NOCOPY VARCHAR2)
238 ---------------------------------------------------------------------------
239 IS
240 l_app_name FND_NEW_MESSAGES.message_text%TYPE;
241 l_umx_reg_data UMX_REGISTRATION_PVT.UMX_REGISTRATION_DATA_TBL;
242 l_reg_service_type VARCHAR2(30);
243 l_procedure_name VARCHAR2(30) := '.InitiateHZUserCreation';
244 l_debug_info VARCHAR2(500);
245 BEGIN
246
247 --------------------------------------------------------------------
248 l_debug_info := 'In debug mode, log we have entered this procedure';
249 --------------------------------------------------------------------
250 IF (PG_DEBUG = 'Y') THEN
251 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
252 END IF;
253
254 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
255 -----------------------------------------------------------------------
256 l_debug_info := 'Initialize message list if requested by calling api';
257 -----------------------------------------------------------------------
258 FND_MSG_PUB.initialize;
259 END IF;
260
261 ----------------------------------------------------------------------------
262 l_debug_info := 'Retrieve Application Name';
263 ----------------------------------------------------------------------------
264 FND_MESSAGE.Set_Name('AR', 'ARI_REG_APP_NAME');
265 l_app_name := FND_MESSAGE.Get;
266
267 ----------------------------------------------------------------------------
268 l_debug_info := 'Check if the registration service code passed in is valid';
269 ----------------------------------------------------------------------------
270 BEGIN
271 SELECT REGSRVC.REG_SERVICE_TYPE
272 INTO l_reg_service_type
273 FROM UMX_REG_SERVICES_VL REGSRVC
274 WHERE REGSRVC.REG_SERVICE_CODE = p_reg_service_code
275 AND REGSRVC.START_DATE <= SYSDATE
276 AND NVL(REGSRVC.END_DATE, SYSDATE+1) > SYSDATE;
277 EXCEPTION
278 WHEN OTHERS THEN
279 FND_MESSAGE.SET_NAME ('AR','ARI_REG_PROCESS_TYPE_ERROR');
280 FND_MESSAGE.SET_TOKEN('REG_PROCESS', p_reg_service_code);
281 FND_MSG_PUB.ADD;
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 END;
284
285 --Reg Service Type should be SELF_SERVICE when calling from Collections
286 IF l_reg_service_type <> 'SELF_SERVICE' THEN
287 FND_MESSAGE.SET_NAME ('AR','ARI_REG_PROCESS_TYPE_ERROR');
288 FND_MESSAGE.SET_TOKEN('REG_PROCESS', p_reg_service_code);
289 FND_MSG_PUB.ADD;
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 ----------------------------------------------------------------------------
294 l_debug_info := 'Set up UMX parameters';
295 ----------------------------------------------------------------------------
296 l_umx_reg_data(1).ATTR_NAME := UMX_REGISTRATION_PVT.G_REG_SERVICE_CODE;
297 l_umx_reg_data(1).ATTR_VALUE := p_reg_service_code;
298
299 l_umx_reg_data(2).ATTR_NAME := UMX_REGISTRATION_PVT.G_REG_SERVICE_TYPE;
300 l_umx_reg_data(2).ATTR_VALUE := 'SELF_SERVICE';
301
302 l_umx_reg_data(3).ATTR_NAME := UMX_REGISTRATION_PVT.G_REG_SERVICE_APP_ID;
303 l_umx_reg_data(3).ATTR_VALUE := '222';
304
305 l_umx_reg_data(4).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_USERNAME;
306 --IF the requested username is not passes, set the user name to email address
307 IF p_requested_username IS NOT NULL THEN
308 l_umx_reg_data(4).ATTR_VALUE := p_requested_username;
309 ELSE
310 l_umx_reg_data(4).ATTR_VALUE := p_user_email_addr;
311 END IF;
312
313 l_umx_reg_data(5).ATTR_NAME := UMX_REGISTRATION_PVT.G_IDENTITY_VERIFY_REQD;
314 l_umx_reg_data(5).ATTR_VALUE := p_identity_verification_reqd;
315
316 l_umx_reg_data(6).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_FOR_PARTY_ID;
317 l_umx_reg_data(6).ATTR_VALUE := p_person_id;
318
319 l_umx_reg_data(7).ATTR_NAME := 'CUST_ACCT_TYPE';
320 l_umx_reg_data(7).ATTR_VALUE := p_cust_acct_type;
321
322 l_umx_reg_data(8).ATTR_NAME := 'CUSTOMER_ID';
323 l_umx_reg_data(8).ATTR_VALUE := p_access_domain_id;
324
325 l_umx_reg_data(9).ATTR_NAME := UMX_REGISTRATION_PVT.G_JUSTIFICATION;
326 l_umx_reg_data(9).ATTR_VALUE := p_justification;
327
328 --NOTE: Date has to be in this format 'YYYY/MM/DD'
329 --THis is in sync with FND_DATE.canonical_to_date which is used to retrieve value from WF.
330 l_umx_reg_data(10).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_START_DATE;
331 l_umx_reg_data(10).ATTR_VALUE := to_char(p_req_start_date,'YYYY/MM/DD');
332
333 l_umx_reg_data(11).ATTR_NAME := UMX_REGISTRATION_PVT.G_REQUESTED_END_DATE;
334 l_umx_reg_data(11).ATTR_VALUE := to_char(p_req_end_date,'YYYY/MM/DD');
335
336 l_umx_reg_data(12).ATTR_NAME := UMX_REGISTRATION_PVT.G_AME_APPLICATION_ID;
337 l_umx_reg_data(12).ATTR_VALUE := p_ame_application_id;
338
339 l_umx_reg_data(13).ATTR_NAME := UMX_REGISTRATION_PVT.G_AME_TXN_TYPE_ID;
340 l_umx_reg_data(13).ATTR_VALUE := p_ame_trx_type_id;
341
342 l_umx_reg_data(14).ATTR_NAME := 'EMAIL_ADDRESS';
343 l_umx_reg_data(14).ATTR_VALUE := p_user_email_addr;
344
345 l_umx_reg_data(15).ATTR_NAME := 'FIRST_NAME';
346 l_umx_reg_data(15).ATTR_VALUE := p_first_name;
347
348 l_umx_reg_data(16).ATTR_NAME := 'LAST_NAME';
349 l_umx_reg_data(16).ATTR_VALUE := p_family_name;
350
351 l_umx_reg_data(17).ATTR_NAME := 'COUNTRY_CODE';
352 l_umx_reg_data(17).ATTR_VALUE := p_phone_country_code;
353
354 l_umx_reg_data(18).ATTR_NAME := 'AREA_CODE';
355 l_umx_reg_data(18).ATTR_VALUE := p_area_code;
356
357 l_umx_reg_data(19).ATTR_NAME := 'PRIMARY_PHONE';
358 l_umx_reg_data(19).ATTR_VALUE := p_phone_number;
359
360 l_umx_reg_data(20).ATTR_NAME := 'PHONE_EXTENSION';
361 l_umx_reg_data(20).ATTR_VALUE := p_extension;
362
363 ----------------------------------------------------------------------------
364 l_debug_info := 'Call UMX API to process registration request';
365 ----------------------------------------------------------------------------
366 -- bug 8809700 - commented by avepati
367 -- UMX_REGISTRATION_PVT.UMX_PROCESS_REG_REQUEST(l_umx_reg_data);
368
369 ----------------------------------------------------------------------------
370 l_debug_info := 'In debug mode, log that we have exited this procedure';
371 ----------------------------------------------------------------------------
372 IF (PG_DEBUG = 'Y') THEN
373 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
374 END IF;
375
376 EXCEPTION
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
380 p_data => x_msg_data);
381 WHEN OTHERS THEN
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 IF (SQLCODE <> -20001) THEN
384 IF (PG_DEBUG = 'Y') THEN
385 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
386 arp_standard.debug('Debug Info: ' || l_debug_info);
387 arp_standard.debug(SQLERRM);
388 END IF;
389 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
390 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
391 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
392 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
393 FND_MSG_PUB.ADD;
394 InformSysAdminError(p_procedure_name => l_procedure_name,
395 p_debug_info => l_debug_info,
396 p_error => SQLERRM);
397 END IF;
398 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
399 p_data => x_msg_data);
400 END InitiateHZUserCreation;
401
402 /* =======================================================================
403 | PROCEDURE OpenCustAcctCur
404 |
405 | DESCRIPTION
406 |
407 |
408 | PARAMETERS
409 |
410 * ======================================================================*/
411 --------------------------------------------------------------------------
412 PROCEDURE OpenCustAcctCur(p_customer_id IN VARCHAR2,
413 p_cust_acct_cur OUT NOCOPY GenCursorRef)
414 --------------------------------------------------------------------------
415 IS
416 l_procedure_name VARCHAR2(30) := '.OpenCustAcctCur';
417 l_debug_info VARCHAR2(200);
418 BEGIN
419
420 --------------------------------------------------------------------
421 l_debug_info := 'In debug mode, log we have entered this procedure';
422 --------------------------------------------------------------------
423 IF (PG_DEBUG = 'Y') THEN
424 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
425 END IF;
426
427 --------------------------------------------------------------------
428 l_debug_info := 'Open p_cust_acct_cur';
429 --------------------------------------------------------------------
430 OPEN p_cust_acct_cur FOR
431 SELECT CustAcct.cust_account_id,
432 CustAcct.account_number,
433 CustAcct.party_id,
434 Party.party_type
435 FROM HZ_CUST_ACCOUNTS CustAcct,
436 HZ_PARTIES Party
437 WHERE CustAcct.cust_account_id = p_customer_id
438 AND CustAcct.party_id = Party.party_id;
439
440 --------------------------------------------------------------------
441 l_debug_info := 'In debug mode, log we have exited this procedure';
442 --------------------------------------------------------------------
443 IF (PG_DEBUG = 'Y') THEN
444 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
445 END IF;
446
447 EXCEPTION
448 WHEN OTHERS THEN
449 IF (SQLCODE <> -20001) THEN
450 IF (PG_DEBUG = 'Y') THEN
451 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
452 arp_standard.debug('Debug Info: ' || l_debug_info);
453 arp_standard.debug(SQLERRM);
454 END IF;
455 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
456 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
457 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
458 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
459 FND_MSG_PUB.ADD;
460 InformSysAdminError(p_procedure_name => l_procedure_name,
461 p_debug_info => l_debug_info,
462 p_error => SQLERRM);
463 END IF;
464 APP_EXCEPTION.RAISE_EXCEPTION;
465 END OpenCustAcctCur;
466
467 /* =======================================================================
468 | PROCEDURE InformSysAdminError
469 |
470 | DESCRIPTION
471 |
472 |
473 | PARAMETERS
474 |
475 * ======================================================================*/
476 --------------------------------------------------------------------------
477 PROCEDURE InformSysAdminError(p_procedure_name IN VARCHAR2,
478 p_debug_info IN VARCHAR2,
479 p_error IN VARCHAR2)
480 --------------------------------------------------------------------------
481 IS
482 l_pkg_name VARCHAR2(30) := G_PKG_NAME;
483 l_procedure_name VARCHAR2(30) := 'InformSysAdminError';
484 l_debug_info VARCHAR2(200);
485 BEGIN
486
487 --------------------------------------------------------------------
488 l_debug_info := 'Initiate Inform Sysadmin Workflow';
489 -------------------------------------------------------------------
490
491 EXCEPTION
492 WHEN OTHERS THEN
493 IF (SQLCODE <> -20001) THEN
494 IF (PG_DEBUG = 'Y') THEN
495 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
496 arp_standard.debug('Debug Info: ' || l_debug_info);
497 arp_standard.debug(SQLERRM);
498 END IF;
499 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
500 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
501 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
502 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
503 FND_MSG_PUB.ADD;
504 END IF;
505 APP_EXCEPTION.RAISE_EXCEPTION;
506 END InformSysAdminError;
507
508 /* =======================================================================
509 | PROCEDURE GenerateAccessVerifyQuestion
510 |
511 | DESCRIPTION
512 |
513 |
514 | PARAMETERS
515 |
516 * ======================================================================*/
517 ---------------------------------------------------------------------------
518 PROCEDURE GenerateAccessVerifyQuestion(
519 p_registration_id IN NUMBER,
520 p_client_ip_address IN VARCHAR2,
521 p_customer_id IN VARCHAR2,
522 p_customer_site_use_id IN VARCHAR2)
523 ---------------------------------------------------------------------------
524 IS
525 l_rowid VARCHAR2(100);
526 l_reg_access_verify_id NUMBER;
527 l_verify_access VerifyAccessTable;
528 l_verify_access_rec VerifyAccessRec;
529 l_curr_question VARCHAR2(2000);
530 l_curr_exp_answer VARCHAR2(2000);
531 l_attempts NUMBER;
532 l_customer_site_use_id VARCHAR2(50);
533 l_procedure_name VARCHAR2(50) := '.GenerateAccessVerifyQuestion';
534 l_debug_info VARCHAR2(300);
535 BEGIN
536 ----------------------------------------------------------------------------
537 l_debug_info := 'In debug mode, log we have entered this procedure';
538 ----------------------------------------------------------------------------
539 IF (PG_DEBUG = 'Y') THEN
540 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
541 END IF;
542
543 ----------------------------------------------------------------------------
544 l_debug_info := 'Insert Row, reg_id = ' || to_char(p_registration_id) || ',ip_addr= ' ||
545 p_client_ip_address || 'customer_id = ' || to_char(p_customer_id) || ',customer_site_id = ' ||
546 to_char(p_customer_site_use_id);
547 ----------------------------------------------------------------------------
548 IF (PG_DEBUG = 'Y') THEN
549 arp_standard.debug(l_debug_info);
550 END IF;
551
552 --If customer site id is -1, pass null to ARI_SELF_REG_CONFIG
553 IF (p_customer_site_use_id <> -1) THEN
554 l_customer_site_use_id := p_customer_site_use_id;
555 END IF;
556
557 ARI_SELF_REG_CONFIG.verify_customer_site_access(p_customer_id => p_customer_id,
558 p_customer_site_use_id => l_customer_site_use_id,
559 x_verify_access => l_verify_access,
560 x_attempts => l_attempts);
561
562 IF (l_verify_access.count > 0) THEN
563 FOR i IN 1..l_verify_access.count LOOP
564
565 l_reg_access_verify_id := null;
566
567 ARI_REG_VERIFICATIONS_PKG.Insert_Row(
568 x_rowid => l_rowid,
569 x_client_ip_address => p_client_ip_address,
570 x_question => l_verify_access(i).question,
571 x_expected_answer => l_verify_access(i).expected_answer,
572 x_number_of_attempts => 0,--l_attempts,
573 x_customer_id => p_customer_id,
574 x_customer_site_use_id => p_customer_site_use_id,
575 x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
576 x_last_update_date => sysdate,
577 x_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
578 x_creation_date => sysdate,
579 x_created_by => nvl(FND_GLOBAL.user_id,-1));
580
581
582
583 END LOOP;
584 END IF;
585
586 COMMIT;
587
588 IF (PG_DEBUG = 'Y') THEN
589 arp_standard.debug('after insert row');
590 END IF;
591 ----------------------------------------------------------------------------
592 l_debug_info := 'In debug mode, log that we have exited this procedure';
593 ----------------------------------------------------------------------------
594 IF (PG_DEBUG = 'Y') THEN
595 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
596 END IF;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 IF (SQLCODE <> -20001) THEN
601 IF (PG_DEBUG = 'Y') THEN
602 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
603 arp_standard.debug('Debug Info: ' || l_debug_info);
604 arp_standard.debug(SQLERRM);
605 END IF;
606 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
607 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
608 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
609 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
610 FND_MSG_PUB.ADD;
611 InformSysAdminError(p_procedure_name => l_procedure_name,
612 p_debug_info => l_debug_info,
613 p_error => SQLERRM);
614 END IF;
615 APP_EXCEPTION.RAISE_EXCEPTION;
616 END GenerateAccessVerifyQuestion;
617
618 /* =======================================================================
619 | PROCEDURE ClearRegistrationTable
620 |
621 | DESCRIPTION
622 |
623 |
624 | PARAMETERS
625 |
626 * ======================================================================*/
627 ---------------------------------------------------------------------------
628 PROCEDURE ClearRegistrationTable IS
629
630 l_procedure_name VARCHAR2(50);
631 l_debug_info VARCHAR2(200);
632
633 BEGIN
634
635 l_procedure_name := '.ClearRegistrationTable';
636
637 ----------------------------------------------------------------------------------------
638 l_debug_info := 'Delete all records in Registration GT';
639 -----------------------------------------------------------------------------------------
640 IF (PG_DEBUG = 'Y') THEN
641 arp_standard.debug(l_debug_info);
642 END IF;
643
644 DELETE FROM ARI_REG_VERIFICATIONS_GT;
645
646 COMMIT;
647
648 ----------------------------------------------------------------------------------------
649 l_debug_info := 'All records in Registration GT deleted';
650 -----------------------------------------------------------------------------------------
651 IF (PG_DEBUG = 'Y') THEN
652 arp_standard.debug(l_debug_info);
653 END IF;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 IF (PG_DEBUG = 'Y') THEN
658 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
659 arp_standard.debug('ERROR =>'|| SQLERRM);
660 END IF;
661
662 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
663 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
664 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
665 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
666 FND_MSG_PUB.ADD;
667
668
669 END;
670
671 /* =======================================================================
672 | PROCEDURE GenCustDetailAccessQuestion
673 |
674 | DESCRIPTION
675 |
676 |
677 | PARAMETERS
678 |
679 * ======================================================================*/
680 ---------------------------------------------------------------------------
681 PROCEDURE GenCustDetailAccessQuestion(
682 p_client_ip_address IN VARCHAR2,
683 p_customer_id IN VARCHAR2)
684 ---------------------------------------------------------------------------
685 IS
686 l_rowid VARCHAR2(100);
687 l_reg_access_verify_id NUMBER;
688 l_verify_access VerifyAccessTable;
689 l_verify_access_rec VerifyAccessRec;
690 l_curr_question VARCHAR2(2000);
691 l_curr_exp_answer VARCHAR2(2000);
692 l_attempts NUMBER;
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_customer_id => p_customer_id,
726 x_customer_site_use_id => null,
727 x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
728 x_last_update_date => sysdate,
729 x_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
730 x_creation_date => sysdate,
731 x_created_by => nvl(FND_GLOBAL.user_id,-1));
732
733
734 END LOOP;
735 END IF;
736
737 COMMIT;
738
739 IF (PG_DEBUG = 'Y') THEN
740 arp_standard.debug('after insert row');
741 END IF;
742 ----------------------------------------------------------------------------
743 l_debug_info := 'In debug mode, log that we have exited this procedure';
744 ----------------------------------------------------------------------------
745 IF (PG_DEBUG = 'Y') THEN
746 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
747 END IF;
748
749 EXCEPTION
750 WHEN OTHERS THEN
751 IF (SQLCODE <> -20001) THEN
752 IF (PG_DEBUG = 'Y') THEN
753 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
754 arp_standard.debug('Debug Info: ' || l_debug_info);
755 arp_standard.debug(SQLERRM);
756 END IF;
757 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
758 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
759 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
760 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
761 FND_MSG_PUB.ADD;
762 InformSysAdminError(p_procedure_name => l_procedure_name,
763 p_debug_info => l_debug_info,
764 p_error => SQLERRM);
765 END IF;
766 APP_EXCEPTION.RAISE_EXCEPTION;
767 END GenCustDetailAccessQuestion;
768
769 /* =======================================================================
770 | FUNCTION ValidateAnswer
771 |
772 | DESCRIPTION
773 |
774 |
775 | PARAMETERS
776 |
777 * ======================================================================*/
778 ---------------------------------------------------------------------------
779 /*FUNCTION ValidateAnswer( p_answer IN VARCHAR2,
780 p_reg_access_verify_id IN NUMBER)
781 RETURN VARCHAR2
782 ---------------------------------------------------------------------------
783 IS
784 l_expected_answer ari_reg_verifications_gt.expected_answer%type;
785 l_return VARCHAR2(5);
786 BEGIN
787
788 BEGIN
789 select expected_answer
790 into l_expected_answer
791 from ari_reg_verifications_gt
792 where reg_access_verify_id = p_reg_access_verify_id;
793 END;
794
795 IF l_expected_answer = to_char(p_answer) THEN
796 l_return := 'Y';
797 ELSE
798 l_return := 'N';
799 END IF;
800
801 RETURN l_return;
802
803 END ValidateAnswer;*/
804
805 /* =======================================================================
806 | FUNCTION RemoveRoleAccess
807 |
808 | DESCRIPTION
809 |
810 |
811 | PARAMETERS
812 |
813 * ======================================================================*/
814 ---------------------------------------------------------------------------
815 PROCEDURE RemoveRoleAccess(p_person_party_id IN VARCHAR2,
816 p_customer_id IN VARCHAR2,
817 p_cust_acct_site_id IN VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2)
819 ---------------------------------------------------------------------------
820 IS
821 CURSOR cust_acct_role_cur(p_person_party_id IN VARCHAR2,
822 p_customer_id IN VARCHAR2,
823 p_cust_acct_site_id IN VARCHAR2) IS
824 select hcar.cust_account_role_id
825 from hz_role_responsibility hrr, hz_cust_account_roles hcar
826 where hrr.responsibility_type = 'SELF_SERVICE_USER'
827 and hrr.cust_account_role_id = hcar.cust_account_role_id
828 and hcar.cust_account_id = p_customer_id
829 --Bug 4764121 : Fixed the removal of access to all customers
830 and DECODE(p_cust_acct_site_id, '-1', -1,p_cust_acct_site_id) =
831 DECODE(p_cust_acct_site_id, '-1', -1, hcar.cust_acct_site_id)
832 and hcar.party_id = p_person_party_id;
833
834 CURSOR cu_acct_role_version (p_cust_acct_role_id IN NUMBER) IS
835 SELECT OBJECT_VERSION_NUMBER
836 FROM HZ_CUST_ACCOUNT_ROLES
837 WHERE CUST_ACCOUNT_ROLE_ID = p_cust_acct_role_id;
838
839 l_cust_account_role_id HZ_CUST_ACCOUNT_ROLES.cust_account_role_id%TYPE;
840 p_cust_account_role_rec_type HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
841 l_object_version_number HZ_CUST_ACCOUNT_ROLES.object_version_number%TYPE;
842 l_msg_count NUMBER;
843 l_msg_data VARCHAR2(2000);
844 l_return_status VARCHAR2(10);
845 l_procedure_name VARCHAR2(500) := '.RemoveRoleAccess';
846 l_debug_info VARCHAR2(4000);
847 l_user_id NUMBER;
848 l_user_name VARCHAR2(50);
849 l_site_use_id VARCHAR2(50);
850
851 BEGIN
852 ----------------------------------------------------------------------------
853 l_debug_info := 'In debug mode, log we have entered this procedure';
854 ----------------------------------------------------------------------------
855 IF (PG_DEBUG = 'Y') THEN
856 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
857 END IF;
858 /* Added the below queries to fetch the user name and site_use_id for deleting
859 the record from ari_reg_verifications when access is removed for the selected location for Bug 13869981 */
860 ---------------------------------------------------------------------
861 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
862 l_debug_info := 'InsideRemoveRole'||p_person_party_id||' '||p_customer_id||p_cust_acct_site_id;
863 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,l_debug_info);
864 end if;
865 -------------------------------------------------------------------------
866 l_user_id := FND_PROFILE.VALUE('USER_ID');
867
868 select user_name INTO l_user_name FROM fnd_user where user_id = l_user_id;
869
870 if(p_cust_acct_site_id <> -1) then
871 select site_uses.site_use_id INTO l_site_use_id FROM hz_cust_site_uses site_uses
872 where site_uses.cust_acct_site_id = p_cust_acct_site_id
873 and site_uses.site_use_code = 'BILL_TO';
874 else -- This is being done to signify All Locations record
875 l_site_use_id := -1;
876 end if;
877 -------------------------------------------------------------------------
878 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
879 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'InsideRemoveRole'||l_user_name||l_site_use_id);
880 end if;
881 -------------------------------------------------------------------------
882 delete from ARI_REG_VERIFICATIONS
883 WHERE UPPER(user_email_addr) = UPPER(l_user_name)
884 AND application_code = 'ARI'
885 AND access_domain_cust_id = p_customer_id
886 AND (access_domain_billto_id is null or access_domain_billto_id = nvl(l_site_use_id,-1))
887 AND status = 'COMPLETE';
888 COMMIT;
889
890
891 SAVEPOINT RemoveAccessStart;
892
893 ----------------------------------------------------------------------------
894 l_debug_info := 'Update Cust Account Role';
895 ----------------------------------------------------------------------------
896 IF (PG_DEBUG = 'Y') THEN
897 arp_standard.debug(l_debug_info);
898 END IF;
899
900 FOR role_record IN cust_acct_role_cur(p_person_party_id,
901 p_customer_id,
902 p_cust_acct_site_id)
903 LOOP
904
905 OPEN cu_acct_role_version(role_record.cust_account_role_id);
906 FETCH cu_acct_role_version INTO
907 l_object_version_number;
908 CLOSE cu_acct_role_version;
909
910 p_cust_account_role_rec_type.cust_account_role_id := role_record.cust_account_role_id;
911 p_cust_account_role_rec_type.status := 'I'; --Inactive
912 --p_cust_account_role_rec_type.end_date := sysdate;
913
914 HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
915 p_init_msg_list => FND_API.G_FALSE,
916 p_cust_account_role_rec => p_cust_account_role_rec_type,
917 p_object_version_number => l_object_version_number,
918 x_return_status => l_return_status,
919 x_msg_count => l_msg_count,
920 x_msg_data => l_msg_data);
921
922 x_return_status := l_return_status;
923 --IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
924 -- RETURN;
925 --END IF;
926
927 END LOOP;
928
929 COMMIT;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 x_return_status := FND_API.G_RET_STS_ERROR;
934 ROLLBACK TO RemoveAccessStart;
935 END RemoveRoleAccess;
936
937 /* =======================================================================
938 | FUNCTION GetPartyRelationshipId
939 |
940 | DESCRIPTION
941 |
942 |
943 | PARAMETERS
944 |
945 * ======================================================================*/
946 ---------------------------------------------------------------------------
947 FUNCTION GetPartyRelationshipId (p_user_id IN VARCHAR2,
948 p_customer_id IN VARCHAR2)
949 RETURN VARCHAR2
950 ---------------------------------------------------------------------------
951 IS
952 l_party_rel_id VARCHAR2(50);
953 l_party_id VARCHAR2(50);
954 BEGIN
955
956 IF p_customer_id IS NULL THEN
957
958 select to_char(customer_id)
959 into l_party_rel_id
960 from fnd_user
961 where user_id = p_user_id;
962
963 ELSE
964 -- Bug 5219389 - Party Id should be passed instead of cust_account_id.
965 SELECT party_id INTO l_party_id FROM hz_cust_accounts WHERE cust_account_id = p_customer_id;
966
967 select party_id into l_party_rel_id
968 from (
969 select hr1.party_id
970 from hz_relationships hr1,
971 hz_relationships hr2,
972 fnd_user fu
973 where hr1.subject_type = 'PERSON'
974 AND (hr1.relationship_code = 'CONTACT_OF' OR hr1.relationship_code = 'EMPLOYEE_OF')
975 AND hr1.status = 'A'
976 and hr1.object_id = l_party_id
977 and hr1.subject_id = hr2.subject_id
978 AND (hr1.end_date is null OR hr1.end_date > sysdate)
979 and hr2.party_id = fu.customer_id
980 and fu.user_id = p_user_id
981 UNION ALL
982 select hr1.party_id
983
984 from hz_relationships hr1,
985 fnd_user fu, hz_parties Party
986 where hr1.subject_type = 'PERSON'
987 AND (hr1.relationship_code = 'CONTACT_OF' OR hr1.relationship_code = 'EMPLOYEE_OF')
988 AND hr1.status = 'A'
989 and hr1.object_id = l_party_id
990 and hr1.subject_id = fu.customer_id
991 AND (hr1.end_date is null OR hr1.end_date > sysdate)
992 and fu.user_id = p_user_id
993 AND Party.party_id = fu.customer_id
994 AND Party.party_type = 'PERSON'
995 AND Party.status = 'A');
996
997 END IF;
998
999 RETURN l_party_rel_id;
1000
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 return null;
1004 END GetPartyRelationshipId;
1005
1006 /* =======================================================================
1007 | FUNCTION GetCustomerAcctNumber
1008 |
1009 | DESCRIPTION
1010 |
1011 |
1012 | PARAMETERS
1013 |
1014 * ======================================================================*/
1015 ---------------------------------------------------------------------------
1016 FUNCTION GetCustomerAcctNumber (p_cust_account_id IN VARCHAR2)
1017 RETURN VARCHAR2
1018 ---------------------------------------------------------------------------
1019 IS
1020 l_acct_number varchar2(30);
1021 BEGIN
1022 select account_number
1023 into l_acct_number
1024 from hz_cust_accounts
1025 where cust_account_id = p_cust_account_id;
1026
1027 RETURN l_acct_number;
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 return null;
1032 END GetCustomerAcctNumber;
1033
1034 /* =======================================================================
1035 | FUNCTION CheckUserIsAdmin
1036 |
1037 | DESCRIPTION
1038 |
1039 |
1040 | PARAMETERS
1041 |
1042 * ======================================================================*/
1043 ---------------------------------------------------------------------------
1044 FUNCTION CheckUserIsAdmin (p_user_id IN VARCHAR2)
1045 RETURN VARCHAR2
1046 ---------------------------------------------------------------------------
1047 IS
1048 l_return varchar2(5);
1049 BEGIN
1050
1051 select 'Y'
1052 into l_return
1053 from dual
1054 where p_user_id IN ( select user_id
1055 from umx_role_assignments_v
1056 where role_name like 'UMX|ARI_CUST_ADMIN');
1057
1058 RETURN l_return;
1059
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 return 'N';
1063 END CheckUserIsAdmin;
1064
1065 /*=======================================================================
1066 | FUNCTION CreatePersonPartyInternal
1067 |
1068 | DESCRIPTION
1069 |
1070 |
1071 | PARAMETERS
1072 |
1073 * ======================================================================*/
1074 ---------------------------------------------------------------------------
1075 Procedure CreatePersonPartyInternal(p_event in out NOCOPY WF_EVENT_T,
1076 p_person_party_id out NOCOPY varchar2)
1077 ---------------------------------------------------------------------------
1078 IS
1079
1080 l_first_name hz_parties.person_first_name%type;
1081 l_last_name hz_parties.person_last_name%type;
1082 l_middle_name hz_parties.person_middle_name%type;
1083 l_pre_name_adjunct hz_parties.person_pre_name_adjunct%type;
1084 l_person_name_suffix hz_parties.person_name_suffix%type;
1085
1086 l_party_number hz_parties.party_number%type;
1087 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
1088 l_profile_id NUMBER;
1089 l_reg_type VARCHAR2(50);
1090 l_reg_user_name fnd_user.user_name%type;
1091 l_email_address fnd_user.email_address%type;
1092 l_procedure_name VARCHAR2(50) := '.CreatePersonPartyInternal';
1093 l_debug_info VARCHAR2(300);
1094
1095 X_Return_Status VARCHAR2(20);
1096 X_Msg_Count NUMBER;
1097 X_Msg_data VARCHAR2(300);
1098
1099 BEGIN
1100
1101 ----------------------------------------------------------------------------
1102 l_debug_info := 'In debug mode, log we have entered this procedure';
1103 ----------------------------------------------------------------------------
1104 IF (PG_DEBUG = 'Y') THEN
1105 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1106 END IF;
1107
1108 ----------------------------------------------------------------------------
1109 l_debug_info := 'Read values from the event';
1110 ----------------------------------------------------------------------------
1111 IF (PG_DEBUG = 'Y') THEN
1112 arp_standard.debug(l_debug_info);
1113 END IF;
1114 l_first_name := p_event.getvalueforparameter('FIRST_NAME');
1115
1116 l_last_name := p_event.getvalueforparameter('LAST_NAME');
1117 l_middle_name := p_event.getvalueforparameter('MIDDLE_NAME');
1118 l_pre_name_adjunct := p_event.getvalueforparameter('PRE_NAME_ADJUNCT');
1119 l_person_name_suffix := p_event.getvalueforparameter('PERSON_SUFFIX');
1120 ----------------------------------------------------------------------------
1121 l_debug_info := 'In CreatePersonPartyInternal l_first_name: '||l_first_name||' l_last_name:'||l_last_name||
1122 ' l_middle_name: '||l_middle_name||' l_pre_name_adjunct:'||l_pre_name_adjunct||' l_person_name_suffix: '||l_person_name_suffix;
1123 ----------------------------------------------------------------------------
1124 IF (PG_DEBUG = 'Y') THEN
1125 arp_standard.debug(l_debug_info);
1126 END IF;
1127 ----------------------------------------------------------------------------
1128 l_debug_info := 'Populate person record';
1129 ----------------------------------------------------------------------------
1130 IF (PG_DEBUG = 'Y') THEN
1131 arp_standard.debug(l_debug_info);
1132 END IF;
1133
1134 l_person_rec.person_first_name := l_first_name;
1135 l_person_rec.person_middle_name := l_middle_name;
1136 l_person_rec.person_last_name := l_last_name;
1137 l_person_rec.person_pre_name_adjunct := l_pre_name_adjunct;
1138 l_person_rec.person_name_suffix := l_person_name_suffix;
1139 l_person_rec.created_by_module := 'ARI';
1140 l_person_rec.application_id := 0;
1141
1142 HZ_PARTY_V2PUB.create_person (
1143 p_person_rec => l_person_rec,
1144 x_party_id => p_person_party_id,
1145 x_party_number => l_party_number,
1146 x_profile_id => l_profile_id,
1147 x_return_status => X_Return_Status,
1148 x_msg_count => X_Msg_Count,
1149 x_msg_data => X_Msg_Data);
1150
1151 ----------------------------------------------------------------------------
1152 l_debug_info := 'Completed Hz_party_v2_pub.createperson: Status'||x_return_status;
1153 ----------------------------------------------------------------------------
1154 IF (PG_DEBUG = 'Y') THEN
1155 arp_standard.debug(l_debug_info);
1156 END IF;
1157
1158 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1159 ----------------------------------------------------------------------------
1160 l_debug_info := 'Completed Hz_party_v2_pub.createperson: Message'||X_Msg_Data;
1161 ----------------------------------------------------------------------------
1162 IF (PG_DEBUG = 'Y') THEN
1163 arp_standard.debug(l_debug_info);
1164 END IF;
1165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 end if;
1167
1168 ----------------------------------------------------------------------------
1169 l_debug_info := 'After creating person party, if this is an Additional Access flow,
1170 associate the created person party id with the user';
1171 ----------------------------------------------------------------------------
1172 IF (PG_DEBUG = 'Y') THEN
1173 arp_standard.debug(l_debug_info);
1174 END IF;
1175
1176 l_reg_type := p_event.getvalueforparameter('REG_SERVICE_TYPE');
1177
1178 l_debug_info := 'In CreatePersonPartyInternal REG_SERVICE_TYPE is '||l_reg_type;
1179 ----------------------------------------------------------------------------
1180 IF (PG_DEBUG = 'Y') THEN
1181 arp_standard.debug(l_debug_info);
1182 END IF;
1183 IF (l_reg_type = 'ADDITIONAL_ACCESS' OR l_reg_type ='ARI_ADD_CUST_ACCESS') THEN
1184 --Requested User Name is populated with the user name of the user requesting
1185 --In case of this scenario, admin will not be able to request additional access
1186 --because this user will not come up in the listing of users under admin.
1187 -- This is strictly an ART flow scenario.
1188 l_reg_user_name := p_event.getvalueforparameter('REQUESTED_USERNAME');
1189 l_email_address := p_event.getvalueforparameter('EMAIL_ADDRESS');
1190
1191 ----------------------------------------------------------------------------
1192 l_debug_info := 'Call FND_USER_PKG to update user with person party id';
1193 ----------------------------------------------------------------------------
1194 IF (PG_DEBUG = 'Y') THEN
1195 arp_standard.debug(l_debug_info);
1196 END IF;
1197
1198 FND_USER_PKG.UpdateUser (
1199 x_user_name => l_reg_user_name,
1200 x_owner => 'CUST',
1201 x_email_address => l_email_address,
1202 x_customer_id => p_person_party_id
1203 );
1204
1205 END IF;
1206
1207 ----------------------------------------------------------------------------
1208 l_debug_info := 'In debug mode, log that we have exited this procedure';
1209 ----------------------------------------------------------------------------
1210 IF (PG_DEBUG = 'Y') THEN
1211 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1212 END IF;
1213
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216
1217 IF (SQLCODE <> -20001) THEN
1218 IF (PG_DEBUG = 'Y') THEN
1219 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1220 arp_standard.debug('Debug Info: ' || l_debug_info);
1221 arp_standard.debug(SQLERRM);
1222 END IF;
1223 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1224 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1225 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1226 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1227 FND_MSG_PUB.ADD;
1228 InformSysAdminError(p_procedure_name => l_procedure_name,
1229 p_debug_info => l_debug_info,
1230 p_error => SQLERRM);
1231 END IF;
1232 APP_EXCEPTION.RAISE_EXCEPTION;
1233 END CreatePersonPartyInternal;
1234
1235 /*=======================================================================
1236 | FUNCTION GetOrgPartyId
1237 |
1238 | DESCRIPTION
1239 |
1240 |
1241 | PARAMETERS
1242 |
1243 * ======================================================================*/
1244 ---------------------------------------------------------------------------
1245 FUNCTION GetOrgPartyId(p_customer_id IN NUMBER) RETURN NUMBER
1246 IS
1247 l_org_party_id NUMBER;
1248 ---------------------------------------------------------------------------
1249 BEGIN
1250
1251 select party_id
1252 into l_org_party_id
1253 from hz_cust_accounts
1254 where cust_account_id = p_customer_id;
1255
1256 return l_org_party_id;
1257
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 return null;
1261 END GetOrgPartyId;
1262
1263 /*=======================================================================
1264 | FUNCTION GetPartySiteId
1265 |
1266 | DESCRIPTION
1267 |
1268 |
1269 | PARAMETERS
1270 |
1271 * ======================================================================*/
1272 ---------------------------------------------------------------------------
1273 FUNCTION GetPartySiteId(p_cust_site_use_id IN NUMBER) RETURN NUMBER
1274 ---------------------------------------------------------------------------
1275 IS
1276 l_party_site_id NUMBER;
1277 l_debug_info VARCHAR2(4000);
1278
1279 BEGIN
1280 l_debug_info := 'In GetPartySiteId MO global value: '||MO_GLOBAL.get_current_org_id;
1281
1282 IF (PG_DEBUG = 'Y') THEN
1283 arp_standard.debug(l_debug_info);
1284 END IF;
1285
1286
1287 select hcas.party_site_id
1288 into l_party_site_id
1289 from hz_cust_acct_sites hcas, hz_cust_site_uses hcsu
1290 where hcas.cust_acct_site_id = hcsu.cust_acct_site_id
1291 and hcsu.site_use_id = p_cust_site_use_id;
1292
1293 l_debug_info := 'In GetPartySiteId l_party_site_id value: '||l_party_site_id;
1294
1295 IF (PG_DEBUG = 'Y') THEN
1296 arp_standard.debug(l_debug_info);
1297 END IF;
1298
1299 return l_party_site_id;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 l_debug_info := 'In GetPartySiteId Exception'||SQLERRM;
1304 IF (PG_DEBUG = 'Y') THEN
1305 arp_standard.debug(l_debug_info);
1306 END IF;
1307
1308
1309 return null;
1310 END GetPartySiteId;
1311
1312 /*=======================================================================
1313 | FUNCTION GetCustAcctSiteId
1314 |
1315 | DESCRIPTION
1316 |
1317 |
1318 | PARAMETERS
1319 |
1320 * ======================================================================*/
1321 ---------------------------------------------------------------------------
1322 FUNCTION GetCustAcctSiteId(p_cust_site_use_id IN NUMBER) RETURN NUMBER
1323 ---------------------------------------------------------------------------
1324 IS
1325 l_cust_acct_site_id NUMBER;
1326 l_debug_info VARCHAR2(4000);
1327
1328 BEGIN
1329
1330 l_debug_info:='Entered GetCustAcctSiteId MO global value: '||MO_GLOBAL.get_current_org_id;
1331 IF (PG_DEBUG = 'Y') THEN
1332 arp_standard.debug(l_debug_info);
1333 END IF;
1334
1335 select cust_acct_site_id
1336 into l_cust_acct_site_id
1337 from hz_cust_site_uses
1338 where site_use_id = p_cust_site_use_id;
1339
1340 l_debug_info:='In GetCustAcctSiteId'||l_cust_acct_site_id;
1341 IF (PG_DEBUG = 'Y') THEN
1342 arp_standard.debug(l_debug_info);
1343 END IF;
1344 return l_cust_acct_site_id;
1345
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 l_debug_info := 'Exception in GetCustAcctSiteId'||SQLERRM;
1349 IF (PG_DEBUG = 'Y') THEN
1350 arp_standard.debug(l_debug_info);
1351 END IF;
1352
1353
1354 return null;
1355 END GetCustAcctSiteId;
1356
1357
1358 /*=======================================================================
1359 | FUNCTION CreateOrgContactInternal
1360 |
1361 | DESCRIPTION
1362 |
1363 |
1364 | PARAMETERS
1365 |
1366 * ======================================================================*/
1367 ---------------------------------------------------------------------------
1368 Procedure CreateOrgContactInternal(p_event in out NOCOPY WF_EVENT_T,
1369 p_person_party_id in varchar2,
1370 p_party_id out NOCOPY number)
1371 ---------------------------------------------------------------------------
1372 IS
1373 l_org_contact_id NUMBER;
1374 l_party_rel_id NUMBER;
1375 l_profile_id NUMBER;
1376 l_org_party_id NUMBER;
1377 l_party_site_id NUMBER;
1378 l_customer_id NUMBER;
1379 l_cust_site_use_id NUMBER;
1380
1381 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
1382 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
1383 x_org_contact_party_id NUMBER;
1384 --l_party_id NUMBER;-- to be used for contactpoints
1385 l_party_number VARCHAR2(100);
1386 l_cust_acct_type VARCHAR2(20);
1387 l_already_exists VARCHAR2(10);
1388 l_procedure_name VARCHAR2(50) := '.CreateOrgContactInternal';
1389 l_debug_info VARCHAR2(300);
1390
1391 X_Return_Status VARCHAR2(20);
1392 X_Msg_Count NUMBER;
1393 X_Msg_data VARCHAR2(300);
1394
1395 BEGIN
1396 ----------------------------------------------------------------------------
1397 l_debug_info := 'In debug mode, log we have entered this procedure';
1398 ----------------------------------------------------------------------------
1399 IF (PG_DEBUG = 'Y') THEN
1400 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1401 END IF;
1402
1403 l_customer_id := p_event.getvalueforparameter('CUSTOMER_ID');
1404 l_cust_site_use_id := p_event.getvalueforparameter('CUSTOMER_SITE_USE_ID');
1405 ----------------------------------------------------------------------------
1406 l_debug_info := 'In CreateOrgContactInternal l_customer_id: '||l_customer_id||'l_cust_site_use_id: '||l_cust_site_use_id;
1407 ----------------------------------------------------------------------------
1408 IF (PG_DEBUG = 'Y') THEN
1409 arp_standard.debug(l_debug_info);
1410 END IF;
1411 l_org_party_id := GetOrgPartyId(p_customer_id => l_customer_id);
1412 l_party_site_id := GetPartySiteId(p_cust_site_use_id => l_cust_site_use_id);
1413
1414 l_cust_acct_type := p_event.getvalueforparameter('CUST_ACCT_TYPE');
1415 ----------------------------------------------------------------------------
1416 l_debug_info := 'In CreateOrgContactInternal l_org_party_id: '||l_org_party_id||'l_party_site_id: '||l_party_site_id||' l_cust_acct_type: '||l_cust_acct_type;
1417 ----------------------------------------------------------------------------
1418 IF (PG_DEBUG = 'Y') THEN
1419 arp_standard.debug(l_debug_info);
1420 END IF;
1421 ----------------------------------------------------------------------------
1422 l_debug_info := 'Check if relationship already exists';
1423 ----------------------------------------------------------------------------
1424 IF (PG_DEBUG = 'Y') THEN
1425 arp_standard.debug(l_debug_info);
1426 END IF;
1427 BEGIN
1428 --Bug 4764121: Storing the value of party_id (even when relationship exists)
1429 SELECT 'Y',party_id
1430 INTO l_already_exists,p_party_id
1431 FROM HZ_RELATIONSHIPS
1432 WHERE SUBJECT_ID = p_person_party_id
1433 AND SUBJECT_TYPE = 'PERSON'
1434 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1435 AND RELATIONSHIP_TYPE = 'CONTACT'
1436 AND RELATIONSHIP_CODE = 'CONTACT_OF'
1437 AND OBJECT_ID = l_org_party_id
1438 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date,SYSDATE))
1439 AND TRUNC(NVL(end_date,SYSDATE));
1440 EXCEPTION
1441 WHEN TOO_MANY_ROWS THEN
1442 --Relationship exists
1443 RETURN;
1444 WHEN NO_DATA_FOUND THEN
1445 l_already_exists := 'N';
1446 END;
1447 ----------------------------------------------------------------------------
1448 l_debug_info := 'In CreateOrgContactInternal l_already_exists: '||l_already_exists;
1449 ----------------------------------------------------------------------------
1450 IF (PG_DEBUG = 'Y') THEN
1451 arp_standard.debug(l_debug_info);
1452 END IF;
1453 IF l_already_exists = 'N' THEN
1454
1455 ----------------------------------------------------------------------------
1456 l_debug_info := 'Create the org contact relationship';
1457 ----------------------------------------------------------------------------
1458 IF (PG_DEBUG = 'Y') THEN
1459 arp_standard.debug(l_debug_info);
1460 END IF;
1461
1462 l_party_rel_rec.subject_id := p_person_party_id;
1463 l_party_rel_rec.subject_type := 'PERSON';
1464 l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
1465 l_party_rel_rec.relationship_type := 'CONTACT';
1466 l_party_rel_rec.relationship_code := 'CONTACT_OF';
1467 l_party_rel_rec.start_date := sysdate;
1468 l_party_rel_rec.object_id := l_org_party_id;
1469 IF l_cust_acct_type = G_BUSINESS THEN
1470 l_party_rel_rec.object_type := 'ORGANIZATION';
1471 ELSIF l_cust_acct_type = G_CONSUMER THEN
1472 l_party_rel_rec.object_type := 'PERSON';
1473 END IF;
1474 l_party_rel_rec.object_table_name := 'HZ_PARTIES';
1475 l_party_rel_rec.created_by_module := G_CREATED_BY_MODULE;
1476 l_party_rel_rec.application_id := 0;
1477 l_org_contact_rec.party_rel_rec := l_party_rel_rec;
1478 l_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
1479 l_org_contact_rec.application_id := 0;
1480 l_org_contact_rec.party_site_id := l_party_site_id;
1481
1482 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
1483 p_org_contact_rec => l_org_contact_rec,
1484 x_org_contact_id => x_org_contact_party_id,
1485 x_party_rel_id => l_party_rel_id,
1486 x_party_id => p_party_id,
1487 x_party_number => l_party_number,
1488 x_return_status => X_Return_Status,
1489 x_msg_count => X_Msg_Count,
1490 x_msg_data => X_Msg_data
1491 );
1492
1493 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 end if;
1496
1497 END IF;
1498
1499 ----------------------------------------------------------------------------
1500 l_debug_info := 'In debug mode, log that we have exited this procedure';
1501 ----------------------------------------------------------------------------
1502 IF (PG_DEBUG = 'Y') THEN
1503 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508
1509 IF (SQLCODE <> -20001) THEN
1510 IF (PG_DEBUG = 'Y') THEN
1511 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1512 arp_standard.debug('Debug Info: ' || l_debug_info);
1513 arp_standard.debug(SQLERRM);
1514 END IF;
1515 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1516 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1517 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1518 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1519 FND_MSG_PUB.ADD;
1520 InformSysAdminError(p_procedure_name => l_procedure_name,
1521 p_debug_info => l_debug_info,
1522 p_error => SQLERRM);
1523 END IF;
1524 APP_EXCEPTION.RAISE_EXCEPTION;
1525
1526 END CreateOrgContactInternal;
1527
1528 /*=======================================================================
1529 | FUNCTION CreateContactPointInternal
1530 |
1531 | DESCRIPTION
1532 |
1533 |
1534 | PARAMETERS
1535 |
1536 * ======================================================================*/
1537 ---------------------------------------------------------------------------
1538 Procedure CreateContactPointInternal(p_event in out NOCOPY WF_EVENT_T,
1539 p_contact_party_id in varchar2)
1540 ---------------------------------------------------------------------------
1541 IS
1542
1543 l_contact_point_id hz_contact_points.contact_point_id%type;
1544 l_contact_preference_id hz_contact_preferences.contact_preference_id%type;
1545 l_email_format HZ_CONTACT_POINTS.email_format%type;
1546 l_email_address HZ_CONTACT_POINTS.email_address%type;
1547 l_primary_phone HZ_CONTACT_POINTS.phone_number%type;
1548 l_area_code HZ_CONTACT_POINTS.phone_area_code%type;
1549 l_country_code HZ_CONTACT_POINTS.phone_country_code%type;
1550 l_phone_purpose HZ_CONTACT_POINTS.contact_point_purpose%type;
1551 l_phone_extension HZ_CONTACT_POINTS.phone_extension%type;
1552 l_object_version_number HZ_CONTACT_POINTS.object_version_number%type;
1553
1554 l_profile_id number;
1555 l_cust_site_use_id NUMBER;
1556
1557 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
1558 l_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
1559 l_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
1560 l_procedure_name VARCHAR2(50) := '.CreateContactPointInternal';
1561 l_debug_info VARCHAR2(300);
1562
1563 X_Return_Status VARCHAR2(20);
1564 X_Msg_Count NUMBER;
1565 X_Msg_data VARCHAR2(300);
1566
1567 BEGIN
1568
1569 ----------------------------------------------------------------------------
1570 l_debug_info := 'In debug mode, log we have entered this procedure';
1571 ----------------------------------------------------------------------------
1572 IF (PG_DEBUG = 'Y') THEN
1573 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1574 END IF;
1575
1576 ----------------------------------------------------------------------------
1577 l_debug_info := 'In CreateContactPointInternal IN Value of p_contact_party_id: '||p_contact_party_id;
1578 ----------------------------------------------------------------------------
1579 IF (PG_DEBUG = 'Y') THEN
1580 arp_standard.debug(l_debug_info);
1581 END IF;
1582 -- get the values from event object
1583 l_email_address := p_event.getvalueforparameter('EMAIL_ADDRESS');
1584 l_email_format := p_event.getvalueforparameter('EMAIL_PREFERENCE');
1585 l_primary_phone := p_event.getvalueforparameter('PRIMARY_PHONE');
1586 l_area_code := p_event.getvalueforparameter('AREA_CODE');
1587 l_country_code := p_event.getvalueforparameter('COUNTRY_CODE');
1588 l_phone_purpose := p_event.getvalueforparameter('PHONE_PURPOSE');
1589 l_phone_extension := p_event.getvalueforparameter('PHONE_EXTENSION');
1590
1591 ----------------------------------------------------------------------------
1592 l_debug_info := 'In CreateContactPointInternal values from event object l_email_address:'||l_email_address||
1593 ' l_email_format:'||l_email_format||' l_primary_phone:'||l_primary_phone||' l_area_code:'||l_area_code||' l_country_code:'||l_country_code||
1594 ' l_phone_purpose:'||l_phone_purpose||' l_phone_extension:'||l_phone_extension;
1595 ----------------------------------------------------------------------------
1596 IF (PG_DEBUG = 'Y') THEN
1597 arp_standard.debug(l_debug_info);
1598 END IF;
1599 --populate the record
1600 l_contact_point_rec.status := 'A';
1601 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1602 l_contact_point_rec.owner_table_id := p_contact_party_id;
1603 l_contact_point_rec.primary_flag := 'Y';
1604 l_contact_point_rec.created_by_module := 'ARI';
1605 l_contact_point_rec.application_id := 0;
1606
1607 if l_email_address is not null then
1608 ----------------------------------------------------------------------------
1609 l_debug_info := 'Email Address not null - create/update email contact point';
1610 ----------------------------------------------------------------------------
1611 IF (PG_DEBUG = 'Y') THEN
1612 arp_standard.debug(l_debug_info);
1613 END IF;
1614
1615 l_contact_point_rec.contact_point_type := 'EMAIL';
1616
1617 l_email_rec.email_address := l_email_address;
1618 l_email_rec.email_format := l_email_format;
1619
1620 ----------------------------------------------------------------------------
1621 l_debug_info := 'Check if an email record already exists';
1622 ----------------------------------------------------------------------------
1623 IF (PG_DEBUG = 'Y') THEN
1624 arp_standard.debug(l_debug_info);
1625 END IF;
1626 BEGIN
1627 l_contact_point_id := null;
1628
1629 SELECT contact_point_id
1630 INTO l_contact_point_id
1631 FROM hz_contact_points
1632 WHERE owner_table_id = p_contact_party_id
1633 AND owner_table_name = 'HZ_PARTIES'
1634 AND status = 'A'
1635 AND primary_flag = 'Y'
1636 AND contact_point_type = 'EMAIL';
1637
1638 EXCEPTION
1639 WHEN TOO_MANY_ROWS THEN
1640 --Not possible
1641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1642 WHEN OTHERS THEN
1643 NULL;
1644 END;
1645 ----------------------------------------------------------------------------
1646 l_debug_info := 'In CreateContactPointInternal l_contact_point_id:'||l_contact_point_id;
1647 ----------------------------------------------------------------------------
1648 IF (PG_DEBUG = 'Y') THEN
1649 arp_standard.debug(l_debug_info);
1650 END IF;
1651 IF (l_contact_point_id IS NULL) THEN
1652
1653 ----------------------------------------------------------------------------
1654 l_debug_info := 'Create email contact point';
1655 ----------------------------------------------------------------------------
1656 IF (PG_DEBUG = 'Y') THEN
1657 arp_standard.debug(l_debug_info);
1658 END IF;
1659
1660 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1661 p_contact_point_rec => l_contact_point_rec,
1662 p_email_rec => l_email_rec,
1663 x_contact_point_id => l_contact_point_id,
1664 x_return_status => X_Return_Status,
1665 x_msg_count => X_Msg_Count,
1666 x_msg_data => X_Msg_Data);
1667 ELSE
1668
1669 ----------------------------------------------------------------------------
1670 l_debug_info := 'Update email contact point';
1671 ----------------------------------------------------------------------------
1672 IF (PG_DEBUG = 'Y') THEN
1673 arp_standard.debug(l_debug_info);
1674 END IF;
1675
1676 SELECT object_version_number
1677 INTO l_object_version_number
1678 FROM HZ_CONTACT_POINTS
1679 WHERE contact_point_id = l_contact_point_id;
1680 ----------------------------------------------------------------------------
1681 l_debug_info := 'In CreateContactPointInternal l_object_version_number:'||l_object_version_number;
1682 ----------------------------------------------------------------------------
1683 IF (PG_DEBUG = 'Y') THEN
1684 arp_standard.debug(l_debug_info);
1685 END IF;
1686
1687 HZ_CONTACT_POINT_V2PUB.update_contact_point (
1688 p_contact_point_rec => l_contact_point_rec,
1689 p_email_rec => l_email_rec,
1690 p_object_version_number => l_object_version_number,
1691 x_return_status => X_Return_Status,
1692 x_msg_count => X_Msg_Count,
1693 x_msg_data => X_Msg_Data);
1694 END IF;
1695
1696 ----------------------------------------------------------------------------
1697 l_debug_info := 'Contact point done: Status' || X_Return_Status;
1698 ----------------------------------------------------------------------------
1699 IF (PG_DEBUG = 'Y') THEN
1700 arp_standard.debug(l_debug_info);
1701 END IF;
1702
1703 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1705 end if;
1706
1707 end if; --mail address not null
1708
1709
1710 if l_primary_phone is not null then
1711
1712 ----------------------------------------------------------------------------
1713 l_debug_info := 'Primary Phone not null - create/update phone contact point';
1714 ----------------------------------------------------------------------------
1715 IF (PG_DEBUG = 'Y') THEN
1716 arp_standard.debug(l_debug_info);
1717 END IF;
1718
1719 l_contact_point_rec.contact_point_type := 'PHONE';
1720 l_contact_point_rec.contact_point_purpose := l_phone_purpose;
1721 --bug #3483248
1722 l_phone_rec.phone_number := l_primary_phone;
1723 l_phone_rec.phone_area_code := l_area_code;
1724 l_phone_rec.phone_country_code := l_country_code;
1725 l_phone_rec.phone_extension := l_phone_extension;
1726 l_phone_rec.phone_line_type := 'GEN';
1727
1728 ----------------------------------------------------------------------------
1729 l_debug_info := 'Check if a phone record already exists';
1730 ----------------------------------------------------------------------------
1731 IF (PG_DEBUG = 'Y') THEN
1732 arp_standard.debug(l_debug_info);
1733 END IF;
1734 BEGIN
1735 l_contact_point_id := null;
1736
1737 SELECT contact_point_id
1738 INTO l_contact_point_id
1739 FROM hz_contact_points
1740 WHERE owner_table_id = p_contact_party_id
1741 AND owner_table_name = 'HZ_PARTIES'
1742 AND status = 'A'
1743 AND primary_flag = 'Y'
1744 AND contact_point_type = 'PHONE';
1745 EXCEPTION
1746 WHEN TOO_MANY_ROWS THEN
1747 --Not possible
1748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1749 WHEN OTHERS THEN
1750 NULL;
1751 END;
1752
1753 IF (l_contact_point_id IS NULL) THEN
1754
1755 ----------------------------------------------------------------------------
1756 l_debug_info := 'Create phone contact point';
1757 ----------------------------------------------------------------------------
1758 IF (PG_DEBUG = 'Y') THEN
1759 arp_standard.debug(l_debug_info);
1760 END IF;
1761
1762 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1763 p_contact_point_rec => l_contact_point_rec,
1764 p_phone_rec => l_phone_rec,
1765 x_contact_point_id => l_contact_point_id,
1766 x_return_status => X_Return_Status,
1767 x_msg_count => X_Msg_Count,
1768 x_msg_data => X_Msg_Data );
1769 ELSE
1770
1771 ----------------------------------------------------------------------------
1772 l_debug_info := 'Update phone contact point';
1773 ----------------------------------------------------------------------------
1774 IF (PG_DEBUG = 'Y') THEN
1775 arp_standard.debug(l_debug_info);
1776 END IF;
1777
1778 SELECT object_version_number
1779 INTO l_object_version_number
1780 FROM HZ_CONTACT_POINTS
1781 WHERE contact_point_id = l_contact_point_id;
1782 ----------------------------------------------------------------------------
1783 l_debug_info := 'In CreateContactPointInternal l_object_version_number:' || l_object_version_number;
1784 ----------------------------------------------------------------------------
1785 IF (PG_DEBUG = 'Y') THEN
1786 arp_standard.debug(l_debug_info);
1787 END IF;
1788 HZ_CONTACT_POINT_V2PUB.update_contact_point (
1789 p_contact_point_rec => l_contact_point_rec,
1790 p_phone_rec => l_phone_rec,
1791 p_object_version_number => l_object_version_number,
1792 x_return_status => X_Return_Status,
1793 x_msg_count => X_Msg_Count,
1794 x_msg_data => X_Msg_Data);
1795 END IF;
1796
1797
1798
1799
1800
1801 ----------------------------------------------------------------------------
1802 l_debug_info := 'Contact point done: Status' || X_Return_Status;
1803 ----------------------------------------------------------------------------
1804 IF (PG_DEBUG = 'Y') THEN
1805 arp_standard.debug(l_debug_info);
1806 END IF;
1807
1808 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810 end if;
1811
1812 end if;
1813
1814 ----------------------------------------------------------------------------
1815 l_debug_info := 'In debug mode, log that we have exited this procedure';
1816 ----------------------------------------------------------------------------
1817 IF (PG_DEBUG = 'Y') THEN
1818 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
1819 END IF;
1820
1821 EXCEPTION
1822 WHEN OTHERS THEN
1823
1824 IF (SQLCODE <> -20001) THEN
1825 IF (PG_DEBUG = 'Y') THEN
1826 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1827 arp_standard.debug('Debug Info: ' || l_debug_info);
1828 arp_standard.debug(SQLERRM);
1829 END IF;
1830 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1831 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1832 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1833 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1834 FND_MSG_PUB.ADD;
1835 InformSysAdminError(p_procedure_name => l_procedure_name,
1836 p_debug_info => l_debug_info,
1837 p_error => SQLERRM);
1838 END IF;
1839 APP_EXCEPTION.RAISE_EXCEPTION;
1840
1841 END CreateContactPointInternal;
1842
1843 /* =======================================================================
1844 | PROCEDURE CreateCustAcctRoleFor
1845 |
1846 | DESCRIPTION
1847 |
1848 |
1849 | PARAMETERS
1850 |
1851 * ======================================================================*/
1852 --------------------------------------------------------------------------
1853 PROCEDURE CreateCustAcctRoleFor(p_event in out NOCOPY WF_EVENT_T,
1854 p_party_id IN NUMBER,
1855 p_cust_acct_role_id OUT NOCOPY NUMBER)
1856 --------------------------------------------------------------------------
1857 IS
1858 l_cust_acct_roles_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
1859 l_return_status VARCHAR2(1);
1860 l_msg_count NUMBER;
1861 l_msg_data VARCHAR2(2000);
1862 l_customer_id NUMBER;
1863 l_cust_acct_site_id NUMBER;
1864 l_cust_site_use_id NUMBER;
1865 l_already_exists VARCHAR2(10);
1866
1867 l_cust_acct_role_id NUMBER;
1868 l_procedure_name VARCHAR2(30) := '.CreateCustAcctRoleFor';
1869 l_debug_info VARCHAR2(200);
1870 l_status VARCHAR2(1);
1871 l_version_number NUMBER;
1872 BEGIN
1873
1874 ----------------------------------------------------------------------------
1875 l_debug_info := 'In debug mode, log we have entered this procedure';
1876 ----------------------------------------------------------------------------
1877 IF (PG_DEBUG = 'Y') THEN
1878 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1879 END IF;
1880 ----------------------------------------------------------------------------
1881 l_debug_info := 'In CreateCustAcctRoleFor IN PARAMETERS p_party_id: '||p_party_id;
1882 ----------------------------------------------------------------------------
1883 IF (PG_DEBUG = 'Y') THEN
1884 arp_standard.debug(l_debug_info);
1885 END IF;
1886
1887 l_customer_id := p_event.getvalueforparameter('CUSTOMER_ID');
1888 l_cust_site_use_id := p_event.getvalueforparameter('CUSTOMER_SITE_USE_ID');
1889
1890 l_cust_acct_site_id := GetCustAcctSiteId(p_cust_site_use_id => l_cust_site_use_id);
1891
1892
1893
1894 ----------------------------------------------------------------------------
1895 l_debug_info := 'In CreateCustAcctRoleFor event parameters l_customer_id: '||l_customer_id||'l_cust_site_use_id: '||l_cust_site_use_id||'l_cust_acct_site_id: '||l_cust_acct_site_id;
1896 ----------------------------------------------------------------------------
1897 IF (PG_DEBUG = 'Y') THEN
1898 arp_standard.debug(l_debug_info);
1899 END IF;
1900
1901
1902 ----------------------------------------------------------------------------
1903 l_debug_info := 'Check if role already exists';
1904 ----------------------------------------------------------------------------
1905 IF (PG_DEBUG = 'Y') THEN
1906 arp_standard.debug(l_debug_info);
1907 END IF;
1908
1909 BEGIN
1910 l_already_exists := 'N';
1911 l_status:='A';
1912 --Bug 4764121: Activating a inactive role
1913 SELECT 'Y',a.cust_account_role_id,status,a.object_version_number
1914 INTO l_already_exists,p_cust_acct_role_id,l_status,l_version_number
1915 FROM hz_cust_account_roles a
1916 WHERE party_id = p_party_id
1917 AND cust_account_id = l_customer_id
1918 AND ((cust_acct_site_id is null and l_cust_acct_site_id is null )
1919 OR cust_acct_site_id = l_cust_acct_site_id )
1920 AND role_type = 'CONTACT'
1921 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(begin_date,SYSDATE))
1922 AND TRUNC(NVL(end_date,SYSDATE));
1923
1924 ----------------------------------------------------------------------------
1925 l_debug_info := 'In CreateCustAcctRoleFor l_already_exists:'||l_already_exists||' p_cust_acct_role_id:'||p_cust_acct_role_id||
1926 ' l_status'||l_status||' l_version_number:'||l_version_number;
1927 ----------------------------------------------------------------------------
1928 IF (PG_DEBUG = 'Y') THEN
1929 arp_standard.debug(l_debug_info);
1930 END IF;
1931 IF l_status='I' THEN
1932 ----------------------------------------------------------------------------
1933 l_debug_info := 'Role exists but is Inactive.Trying to activate it.Role Id= '||p_cust_acct_role_id;
1934 ----------------------------------------------------------------------------
1935 IF (PG_DEBUG = 'Y') THEN
1936 arp_standard.debug(l_debug_info);
1937 END IF;
1938
1939 l_cust_acct_roles_rec.party_id := p_party_id;
1940 l_cust_acct_roles_rec.cust_account_id := l_customer_id;
1941 l_cust_acct_roles_rec.cust_acct_site_id := l_cust_acct_site_id;
1942 l_cust_acct_roles_rec.role_type := 'CONTACT';
1943 l_cust_acct_roles_rec.created_by_module := 'ARI';
1944 l_cust_acct_roles_rec.cust_account_role_id := p_cust_acct_role_id;
1945 l_cust_acct_roles_rec.status := 'A';
1946
1947 HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
1948 p_init_msg_list => FND_API.G_TRUE,
1949 p_cust_account_role_rec => l_cust_acct_roles_rec,
1950 x_return_status => l_return_status,
1951 x_msg_count => l_msg_count,
1952 x_msg_data => l_msg_data,
1953 p_object_version_number => l_version_number);
1954
1955 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1956
1957 ----------------------------------------------------------------------
1958 l_debug_info := 'Error Calling HZ Update Cust Acct Roles API: ' || l_msg_data;
1959 ----------------------------------------------------------------------
1960 IF (PG_DEBUG = 'Y') THEN
1961 arp_standard.debug(l_debug_info);
1962 END IF;
1963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1964
1965 END IF;
1966 END IF;
1967 EXCEPTION
1968 WHEN TOO_MANY_ROWS THEN
1969 --Role exists
1970 RETURN;
1971 WHEN OTHERS THEN
1972 NULL;
1973 END;
1974
1975 IF l_already_exists = 'N' THEN
1976
1977 ----------------------------------------------------------------------------
1978 l_debug_info := 'Create customer account role';
1979 ----------------------------------------------------------------------------
1980 IF (PG_DEBUG = 'Y') THEN
1981 arp_standard.debug(l_debug_info);
1982 END IF;
1983
1984 l_cust_acct_roles_rec.party_id := p_party_id;
1985 l_cust_acct_roles_rec.cust_account_id := l_customer_id;
1986 l_cust_acct_roles_rec.cust_acct_site_id := l_cust_acct_site_id;
1987 l_cust_acct_roles_rec.role_type := 'CONTACT';
1988 l_cust_acct_roles_rec.created_by_module := 'ARI';
1989
1990 ------------------------------------------------------------------------
1991 l_debug_info := 'Call hz_cust_account_v2pub.Create_Cust_Acct_Roles';
1992 ------------------------------------------------------------------------
1993 IF (PG_DEBUG = 'Y') THEN
1994 arp_standard.debug(l_debug_info);
1995 END IF;
1996 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Cust_Account_Role(
1997 p_init_msg_list => FND_API.G_TRUE,
1998 p_cust_account_role_rec => l_cust_acct_roles_rec,
1999 x_return_status => l_return_status,
2000 x_msg_count => l_msg_count,
2001 x_msg_data => l_msg_data,
2002 x_cust_account_role_id => l_cust_acct_role_id);
2003
2004 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2005 ----------------------------------------------------------------------
2006 l_debug_info := 'Return cust acct role id';
2007 ----------------------------------------------------------------------
2008 IF (PG_DEBUG = 'Y') THEN
2009 arp_standard.debug(l_debug_info);
2010 END IF;
2011 p_cust_acct_role_id := l_cust_acct_role_id;
2012 ----------------------------------------------------------------------------
2013 l_debug_info := 'In CreateCustAcctRoleFor p_cust_acct_role_id: '||p_cust_acct_role_id;
2014 ----------------------------------------------------------------------------
2015 IF (PG_DEBUG = 'Y') THEN
2016 arp_standard.debug(l_debug_info);
2017 END IF;
2018 ELSE
2019
2020 ----------------------------------------------------------------------
2021 l_debug_info := 'Error Calling HZ Create Cust Acct Roles API: ' || l_msg_data;
2022 ----------------------------------------------------------------------
2023 IF (PG_DEBUG = 'Y') THEN
2024 arp_standard.debug(l_debug_info);
2025 END IF;
2026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027
2028 END IF;
2029
2030 END IF;
2031
2032 ----------------------------------------------------------------------------
2033 l_debug_info := 'In debug mode, log that we have exited this procedure';
2034 ----------------------------------------------------------------------------
2035 IF (PG_DEBUG = 'Y') THEN
2036 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2037 END IF;
2038
2039 EXCEPTION
2040 WHEN OTHERS THEN
2041 IF (SQLCODE <> -20001) THEN
2042 IF (PG_DEBUG = 'Y') THEN
2043 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2044 arp_standard.debug('Debug Info: ' || l_debug_info);
2045 arp_standard.debug(SQLERRM);
2046 END IF;
2047 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
2048 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2049 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
2050 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2051 FND_MSG_PUB.ADD;
2052 InformSysAdminError(p_procedure_name => l_procedure_name,
2053 p_debug_info => l_debug_info,
2054 p_error => SQLERRM);
2055 END IF;
2056 APP_EXCEPTION.RAISE_EXCEPTION;
2057
2058 END CreateCustAcctRoleFor;
2059
2060
2061 /* =======================================================================
2062 | PROCEDURE CreateRoleRespFor
2063 |
2064 | DESCRIPTION
2065 |
2066 |
2067 | PARAMETERS
2068 |
2069 * ======================================================================*/
2070 --------------------------------------------------------------------------
2071 PROCEDURE CreateRoleRespFor(p_cust_acct_role_id IN NUMBER,
2072 p_role_resp_id OUT NOCOPY NUMBER)
2073 --------------------------------------------------------------------------
2074 IS
2075 l_role_resp_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.role_responsibility_rec_type;
2076 l_return_status VARCHAR2(1);
2077 l_msg_count NUMBER;
2078 l_msg_data VARCHAR2(2000);
2079 l_responsibility_id NUMBER;
2080 l_procedure_name VARCHAR2(30) := '.CreateRoleRespFor';
2081 l_debug_info VARCHAR2(200);
2082 l_already_exists VARCHAR2(10);
2083 BEGIN
2084
2085 ----------------------------------------------------------------------------
2086 l_debug_info := 'In debug mode, log we have entered this procedure';
2087 ----------------------------------------------------------------------------
2088 IF (PG_DEBUG = 'Y') THEN
2089 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2090 END IF;
2091
2092 ----------------------------------------------------------------------------
2093 l_debug_info := 'Check if role responsbility already exists';
2094 ----------------------------------------------------------------------------
2095 IF (PG_DEBUG = 'Y') THEN
2096 arp_standard.debug(l_debug_info);
2097 END IF;
2098
2099 BEGIN
2100 l_already_exists := 'N';
2101
2102 SELECT 'Y'
2103 INTO l_already_exists
2104 FROM hz_role_responsibility
2105 WHERE cust_account_role_id = p_cust_acct_role_id
2106 AND responsibility_type = 'SELF_SERVICE_USER';
2107 ----------------------------------------------------------------------------
2108 l_debug_info := 'In CreateRoleRespFor l_already_exists:'||l_already_exists;
2109 ----------------------------------------------------------------------------
2110 IF (PG_DEBUG = 'Y') THEN
2111 arp_standard.debug(l_debug_info);
2112 END IF;
2113 EXCEPTION
2114 WHEN TOO_MANY_ROWS THEN
2115 --Role exists
2116 RETURN;
2117 WHEN OTHERS THEN
2118 NULL;
2119 END;
2120
2121 IF l_already_exists = 'N' THEN
2122
2123 l_role_resp_rec.cust_account_role_id := p_cust_acct_role_id;
2124 l_role_resp_rec.responsibility_type := 'SELF_SERVICE_USER';
2125 l_role_resp_rec.created_by_module := 'ARI';
2126
2127 --------------------------------------------------------------------
2128 l_debug_info := 'Call HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility';
2129 --------------------------------------------------------------------
2130 IF (PG_DEBUG = 'Y') THEN
2131 arp_standard.debug(l_debug_info);
2132 END IF;
2133 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility(
2134 p_init_msg_list => FND_API.G_TRUE,
2135 p_role_responsibility_rec => l_role_resp_rec,
2136 x_return_status => l_return_status,
2137 x_msg_count => l_msg_count,
2138 x_msg_data => l_msg_data,
2139 x_responsibility_id => l_responsibility_id);
2140
2141 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2142 ----------------------------------------------------------------------
2143 l_debug_info := 'Return role responsibility id';
2144 ----------------------------------------------------------------------
2145 IF (PG_DEBUG = 'Y') THEN
2146 arp_standard.debug(l_debug_info);
2147 END IF;
2148 p_role_resp_id := l_responsibility_id;
2149 ----------------------------------------------------------------------------
2150 l_debug_info := 'In CreateRoleRespFor p_role_resp_id:'||p_role_resp_id;
2151 ----------------------------------------------------------------------------
2152 IF (PG_DEBUG = 'Y') THEN
2153 arp_standard.debug(l_debug_info);
2154 END IF;
2155 ELSE
2156 ----------------------------------------------------------------------
2157 l_debug_info := 'Error Calling HZ Create Role Resp API: ' || l_msg_data;
2158 ----------------------------------------------------------------------
2159 IF (PG_DEBUG = 'Y') THEN
2160 arp_standard.debug(l_debug_info);
2161 END IF;
2162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2163
2164 END IF;
2165
2166 END IF;
2167
2168 ----------------------------------------------------------------------------
2169 l_debug_info := 'In debug mode, log that we have exited this procedure';
2170 ----------------------------------------------------------------------------
2171 IF (PG_DEBUG = 'Y') THEN
2172 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2173 END IF;
2174
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177
2178 IF (SQLCODE <> -20001) THEN
2179 IF (PG_DEBUG = 'Y') THEN
2180 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2181 arp_standard.debug('Debug Info: ' || l_debug_info);
2182 arp_standard.debug(SQLERRM);
2183 END IF;
2184 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
2185 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2186 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
2187 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2188 FND_MSG_PUB.ADD;
2189 InformSysAdminError(p_procedure_name => l_procedure_name,
2190 p_debug_info => l_debug_info,
2191 p_error => SQLERRM);
2192 END IF;
2193 APP_EXCEPTION.RAISE_EXCEPTION;
2194 END CreateRoleRespFor;
2195
2196 /*=======================================================================
2197 | PROCEDURE RegisterB2BUser
2198 |
2199 | DESCRIPTION
2200 |
2201 |
2202 | PARAMETERS
2203 |
2204 * ======================================================================*/
2205 ---------------------------------------------------------------------------
2206 PROCEDURE RegisterB2BUser( p_event IN OUT NOCOPY WF_EVENT_T,
2207 p_person_party_id IN OUT NOCOPY VARCHAR2)
2208 ---------------------------------------------------------------------------
2209 IS
2210 l_party_id NUMBER;
2211 l_cust_acct_role_id HZ_CUST_ACCOUNT_ROLES.cust_account_role_id%type;
2212 l_role_resp_id HZ_ROLE_RESPONSIBILITY.responsibility_id%type;
2213 l_cust_acct_type VARCHAR2(20);
2214 l_procedure_name VARCHAR2(50) := '.RegisterB2BUser';
2215 l_debug_info VARCHAR2(300);
2216
2217 X_Return_Status VARCHAR2(20);
2218 X_Msg_Count NUMBER;
2219 X_Msg_data VARCHAR2(300);
2220
2221 BEGIN
2222
2223 ----------------------------------------------------------------------------
2224 l_debug_info := 'In debug mode, log we have entered this procedure';
2225 ----------------------------------------------------------------------------
2226 IF (PG_DEBUG = 'Y') THEN
2227 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2228 END IF;
2229
2230 IF (p_person_party_id IS NULL) THEN
2231 CreatePersonPartyInternal(p_event,p_person_party_id);
2232 END IF;
2233 CreateOrgContactInternal(p_event, p_person_party_id, l_party_id);
2234 CreateContactPointInternal(p_event,to_char(l_party_id));
2235 CreateCustAcctRoleFor(p_event, l_party_id, l_cust_acct_role_id);
2236 CreateRoleRespFor(l_cust_acct_role_id, l_role_resp_id);
2237
2238 ----------------------------------------------------------------------------
2239 l_debug_info := 'In debug mode, log that we have exited this procedure';
2240 ----------------------------------------------------------------------------
2241 IF (PG_DEBUG = 'Y') THEN
2242 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2243 END IF;
2244
2245 END RegisterB2BUser;
2246
2247 /*=======================================================================
2248 | PROCEDURE RegisterB2CUser
2249 |
2250 | DESCRIPTION
2251 |
2252 |
2253 | PARAMETERS
2254 |
2255 * ======================================================================*/
2256 ---------------------------------------------------------------------------
2257 PROCEDURE RegisterB2CUser( p_event IN OUT NOCOPY WF_EVENT_T,
2258 p_person_party_id IN OUT NOCOPY varchar2 )
2259 ---------------------------------------------------------------------------
2260 IS
2261 l_contact_preference_rec HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE;
2262 l_contact_preference VARCHAR2(5);
2263 l_contact_preference_id number;
2264 l_party_id NUMBER;
2265 l_cust_acct_role_id HZ_CUST_ACCOUNT_ROLES.cust_account_role_id%type;
2266 l_role_resp_id HZ_ROLE_RESPONSIBILITY.responsibility_id%type;
2267 l_cust_acct_type VARCHAR2(20);
2268 l_new_user VARCHAR2(10);
2269
2270 l_procedure_name VARCHAR2(50) := '.RegisterB2CUser';
2271 l_debug_info VARCHAR2(300);
2272
2273
2274 X_Return_Status VARCHAR2(20);
2275 X_Msg_Count NUMBER;
2276 X_Msg_data VARCHAR2(300);
2277
2278 BEGIN
2279
2280 ----------------------------------------------------------------------------
2281 l_debug_info := 'In debug mode, log we have entered this procedure';
2282 ----------------------------------------------------------------------------
2283 IF (PG_DEBUG = 'Y') THEN
2284 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2285 END IF;
2286
2287 IF (p_person_party_id IS NULL) THEN
2288 l_new_user := 'Y';
2289 CreatePersonPartyInternal(p_event,p_person_party_id);
2290 END IF;
2291 CreateContactPointInternal(p_event,p_person_party_id);
2292 CreateOrgContactInternal(p_event, p_person_party_id, l_party_id);
2293 CreateCustAcctRoleFor(p_event, l_party_id, l_cust_acct_role_id);
2294 CreateRoleRespFor(l_cust_acct_role_id, l_role_resp_id);
2295
2296 --populate contact preference if its a new user
2297 IF (l_new_user = 'Y') THEN
2298 l_contact_preference := p_event.getvalueforparameter('CONTACT_PREFERENCE');
2299
2300 IF (l_contact_preference = 'Y') THEN
2301 l_contact_preference_rec.preference_code := 'DO';
2302 ELSE
2303 l_contact_preference_rec.preference_code := 'DO_NOT';
2304 END IF;
2305
2306 l_contact_preference_rec.contact_level_table := 'HZ_PARTIES';
2307 l_contact_preference_rec.contact_level_table_id := p_person_party_id;
2308 l_contact_preference_rec.contact_type := 'EMAIL';
2309 l_contact_preference_rec.requested_by := 'INTERNAL';
2310 l_contact_preference_rec.created_by_module := 'ARI';
2311 l_contact_preference_rec.application_id := 0;
2312
2313 ----------------------------------------------------------------------------
2314 l_debug_info := 'Create contact preference';
2315 ----------------------------------------------------------------------------
2316 IF (PG_DEBUG = 'Y') THEN
2317 arp_standard.debug(l_debug_info);
2318 END IF;
2319
2320 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference(
2321 p_contact_preference_rec => l_contact_preference_rec,
2322 x_contact_preference_id => l_contact_preference_id,
2323 x_return_status => x_return_status,
2324 x_msg_count => x_msg_count,
2325 x_msg_data => x_msg_data
2326 );
2327
2328 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2330 END IF;
2331 END IF;
2332
2333 ----------------------------------------------------------------------------
2334 l_debug_info := 'In debug mode, log that we have exited this procedure';
2335 ----------------------------------------------------------------------------
2336 IF (PG_DEBUG = 'Y') THEN
2337 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2338 END IF;
2339
2340 END RegisterB2CUser;
2341
2342 /*=======================================================================
2343 | PROCUDURE RegisterUser
2344 |
2345 | DESCRIPTION
2346 |
2347 |
2348 | PARAMETERS
2349 |
2350 * ======================================================================*/
2351 ---------------------------------------------------------------------------
2352 PROCEDURE RegisterUser( p_event IN OUT NOCOPY WF_EVENT_T,
2353 p_person_party_id IN OUT NOCOPY varchar2 )
2354 ---------------------------------------------------------------------------
2355 IS
2356 l_cust_acct_type VARCHAR2(20);
2357 l_procedure_name VARCHAR2(50) := '.RegisterUser';
2358 l_debug_info VARCHAR2(300);
2359 l_user_id NUMBER;
2360 l_responsibility_id NUMBER;
2361 l_application_id NUMBER;
2362 BEGIN
2363
2364 ----------------------------------------------------------------------------
2365 l_debug_info := 'In debug mode, log we have entered this procedure';
2366 ----------------------------------------------------------------------------
2367
2368 IF (PG_DEBUG = 'Y') THEN
2369 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2370 END IF;
2371
2372 ----------------------------------------------------------------------------
2373 l_debug_info := 'In RegisterUser p_person_party_id IN is '||p_person_party_id;
2374 ----------------------------------------------------------------------------
2375 IF (PG_DEBUG = 'Y') THEN
2376 arp_standard.debug(l_debug_info);
2377 END IF;
2378 -- Start :Added below statements for Bug#13856182
2379 l_responsibility_id := to_number(p_event.getvalueforparameter('RESPONSIBILITY_ID'));
2380
2381 ----------------------------------------------------------------------------
2382 l_debug_info := 'In RegisterUser l_responsibility_id is'||l_responsibility_id;
2383 ----------------------------------------------------------------------------
2384 IF (PG_DEBUG = 'Y') THEN
2385 arp_standard.debug(l_debug_info);
2386 END IF;
2387 begin
2388 select fnd.user_id into l_user_id
2389 from fnd_user fnd
2390 where user_name='GUEST';
2391 IF p_person_party_id IS NOT NULL then
2392 select fnd.user_id into l_user_id
2393 from fnd_user fnd
2394 where person_party_id = p_person_party_id;
2395 end if;
2396 exception
2397 when no_data_found then
2398 NULL;
2399 end;
2400
2401 l_application_id := 222;
2402
2403 ----------------------------------------------------------------------------
2404 l_debug_info := 'In RegisterUser apps_initialize values '||l_user_id||' '||l_responsibility_id||' '||l_application_id;
2405 ----------------------------------------------------------------------------
2406 IF (PG_DEBUG = 'Y') THEN
2407 arp_standard.debug(l_debug_info);
2408 END IF;
2409
2410 if(l_user_id is not null) then
2411 ----------------------------------------------------------------------------
2412 l_debug_info := 'Setting the apps initialize with the above values of user id, resp id';
2413 ----------------------------------------------------------------------------
2414 IF (PG_DEBUG = 'Y') THEN
2415 arp_standard.debug(l_debug_info);
2416 END IF;
2417 fnd_global.apps_initialize(l_user_id, l_responsibility_id, l_application_id);
2418 end if;
2419 mo_global.init('AR');
2420 -- End :Added below statements for Bug#13856182
2421
2422 ----------------------------------------------------------------------------
2423 l_debug_info := 'In RegisterUser Check if the customer access requested if of BUSINESS or CONSUMER type';
2424 ----------------------------------------------------------------------------
2425 IF (PG_DEBUG = 'Y') THEN
2426 arp_standard.debug(l_debug_info);
2427 END IF;
2428
2429 l_cust_acct_type := p_event.getvalueforparameter('CUST_ACCT_TYPE');
2430 ----------------------------------------------------------------------------
2431 l_debug_info := 'In RegisterUser CUST_ACCT_TYPE is'||l_cust_acct_type;
2432 ----------------------------------------------------------------------------
2433 IF (PG_DEBUG = 'Y') THEN
2434 arp_standard.debug(l_debug_info);
2435 END IF;
2436 IF (l_cust_acct_type = G_BUSINESS) THEN
2437 RegisterB2BUser(p_event, p_person_party_id);
2438 ELSIF (l_cust_acct_type = G_CONSUMER) THEN
2439 RegisterB2CUser(p_event, p_person_party_id);
2440 END IF;
2441
2442 ----------------------------------------------------------------------------
2443 l_debug_info := 'In debug mode, log we have entered this procedure';
2444 ----------------------------------------------------------------------------
2445 IF (PG_DEBUG = 'Y') THEN
2446 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2447 END IF;
2448
2449 END RegisterUser;
2450
2451 /*=======================================================================
2452 | FUNCTION AddCustomerAccess
2453 |
2454 | DESCRIPTION
2455 |
2456 |
2457 | PARAMETERS
2458 |
2459 * ======================================================================*/
2460 ---------------------------------------------------------------------------
2461 FUNCTION AddCustomerAccess(p_subscription_guid in raw,
2462 p_event in out NOCOPY WF_EVENT_T)
2463 RETURN VARCHAR2
2464 ---------------------------------------------------------------------------
2465 IS
2466 l_success VARCHAR2(10);
2467 l_reg_service_type VARCHAR2(50);
2468 l_person_party_id VARCHAR2(50);
2469 l_temp1 VARCHAR2(100);
2470 l_temp2 VARCHAR2(100);
2471 l_registration_id VARCHAR2(100);
2472 l_procedure_name VARCHAR2(500) := '.AddCustomerAccess';
2473 l_debug_info VARCHAR2(4000);
2474
2475 BEGIN
2476
2477 mo_global.init('AR');
2478 ----------------------------------------------------------------------------
2479 l_debug_info := 'In debug mode, log we have entered this procedure';
2480 ----------------------------------------------------------------------------
2481 IF (PG_DEBUG = 'Y') THEN
2482 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2483 END IF;
2484
2485 -- This function is called on the event 'oracle.apps.fnd.umx.requestapproved'
2486 -- To ensure that the code to add access gets executed only for Add Access flow
2487 -- Check for the REG_SERVICE_TYPE of the registration process.
2488
2489 l_reg_service_type := p_event.getvalueforparameter('REG_SERVICE_TYPE');
2490
2491 IF ((l_reg_service_type = 'ADDITIONAL_ACCESS' AND
2492 p_event.getValueForParameter('UMX_CUSTOM_EVENT_CONTEXT') = UMX_PUB.ROLE_APPROVED)
2493 --Bug 4764121
2494 OR l_reg_service_type = 'ARI_ADD_CUST_ACCESS' ) THEN
2495
2496 ----------------------------------------------------------------------------
2497 l_debug_info := 'Add access for the user';
2498 ----------------------------------------------------------------------------
2499 IF (PG_DEBUG = 'Y') THEN
2500 arp_standard.debug(l_debug_info);
2501 END IF;
2502 l_person_party_id := p_event.getvalueforparameter('PERSON_PARTY_ID');
2503
2504 RegisterUser(p_event, l_person_party_id);
2505 -- Added the below lines for Bug 13869981
2506 l_registration_id := p_event.getValueForParameter('REGISTRATION_ID');
2507 -----------------------------------------------------------------------
2508 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2509 l_debug_info := 'RegId IN ADD CUST ACCESS----'||l_registration_id;
2510 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,l_debug_info);
2511 end if;
2512 -----------------------------------------------------------------------
2513 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => l_registration_id,
2514 x_Status_Lookup_Code => 'COMPLETE',
2515 x_Last_Update_Date => sysdate);
2516 COMMIT;
2517
2518 END IF;
2519
2520 ----------------------------------------------------------------------------
2521 l_debug_info := 'In debug mode, log we have entered this procedure';
2522 ----------------------------------------------------------------------------
2523 IF (PG_DEBUG = 'Y') THEN
2524 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2525 END IF;
2526
2527 l_success:='SUCCESS';
2528
2529
2530 RETURN l_success;
2531
2532 EXCEPTION
2533 WHEN OTHERS THEN
2534 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'AddCustomerAccess',
2535 p_event.getEventName( ),p_subscription_guid,
2536 sqlerrm,sqlcode);
2537 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2538 raise;
2539 return 'ERROR';
2540
2541 END AddCustomerAccess;
2542
2543 /*=======================================================================
2544 | FUNCTION CreatePersonParty
2545 |
2546 | DESCRIPTION
2547 |
2548 |
2549 | PARAMETERS
2550 |
2551 * ======================================================================*/
2552 ---------------------------------------------------------------------------
2553 FUNCTION CreatePersonParty(p_subscription_guid in raw,
2554 p_event in out NOCOPY WF_EVENT_T)
2555 RETURN VARCHAR2
2556 ---------------------------------------------------------------------------
2557 IS
2558
2559 l_first_name hz_parties.person_first_name%type;
2560 l_last_name hz_parties.person_last_name%type;
2561 l_middle_name hz_parties.person_middle_name%type;
2562 l_pre_name_adjunct hz_parties.person_pre_name_adjunct%type;
2563 l_person_name_suffix hz_parties.person_name_suffix%type;
2564
2565 l_party_number hz_parties.party_number%type;
2566 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
2567 l_profile_id NUMBER;
2568 l_success VARCHAR2(10);
2569 p_person_party_id varchar2(30);
2570 l_procedure_name VARCHAR2(50) := '.CreatePersonParty';
2571 l_debug_info VARCHAR2(300);
2572
2573 X_Return_Status VARCHAR2(20);
2574 X_Msg_Count NUMBER;
2575 X_Msg_data VARCHAR2(300);
2576
2577 BEGIN
2578 mo_global.init('AR');
2579 ----------------------------------------------------------------------------
2580 l_debug_info := 'In debug mode, log we have entered this procedure';
2581 ----------------------------------------------------------------------------
2582 IF (PG_DEBUG = 'Y') THEN
2583 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2584 END IF;
2585
2586 ----------------------------------------------------------------------------
2587 l_debug_info := 'Create B2C party and populate the event object back to main workflow';
2588 ----------------------------------------------------------------------------
2589 IF (PG_DEBUG = 'Y') THEN
2590 arp_standard.debug(l_debug_info);
2591 END IF;
2592
2593 ----------------------------------------------------------------------------
2594 l_debug_info := 'In CreatePersonParty UMX_CUSTOM_EVENT_CONTEXT : '||p_event.getValueForParameter('UMX_CUSTOM_EVENT_CONTEXT');
2595 ----------------------------------------------------------------------------
2596 IF (PG_DEBUG = 'Y') THEN
2597 arp_standard.debug(l_debug_info);
2598 END IF;
2599
2600 if(p_event.getValueForParameter('UMX_CUSTOM_EVENT_CONTEXT') =
2601 UMX_PUB.BEFORE_ACT_ACTIVATION) then
2602
2603 RegisterUser(p_event,p_person_party_id);
2604
2605 ----------------------------------------------------------------------------
2606 l_debug_info := 'Person Party Created:' || p_person_party_id;
2607 ----------------------------------------------------------------------------
2608 IF (PG_DEBUG = 'Y') THEN
2609 arp_standard.debug(l_debug_info);
2610 END IF;
2611
2612 l_success := UMX_REGISTRATION_UTIL.set_event_object(p_event,'PERSON_PARTY_ID',p_person_party_id);
2613
2614 ----------------------------------------------------------------------------
2615 l_debug_info := 'After Calling UMX_REGISTRATION_UTIL.set_event_object l_success: '||l_success;
2616 ----------------------------------------------------------------------------
2617 IF (PG_DEBUG = 'Y') THEN
2618 arp_standard.debug(l_debug_info);
2619 END IF;
2620 end if;
2621
2622 ----------------------------------------------------------------------------
2623 l_debug_info := 'In debug mode, log that we have exited this procedure';
2624 ----------------------------------------------------------------------------
2625 IF (PG_DEBUG = 'Y') THEN
2626 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
2627 END IF;
2628
2629 -- To match with UMX patch 10133712
2630 IF upper(l_success) <> 'SUCCESS' THEN
2631 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'CREATE_PERSON_PARTY',
2632 p_event.getEventName( ),p_subscription_guid,sqlerrm,sqlcode);
2633 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2634 --BUG 9976852 - Should return success even during AFTER_ACT_ACTIVATION context
2635 ELSIF(p_event.getValueForParameter('UMX_CUSTOM_EVENT_CONTEXT') = UMX_PUB.AFTER_ACT_ACTIVATION) then
2636 l_success := 'SUCCESS';
2637 END IF;
2638
2639 IF (PG_DEBUG = 'Y') THEN
2640 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-'||'Return status ::'||upper(l_success));
2641 END IF;
2642 --To avoid child error workflows, should return 'SUCCESS' and not 'success'.
2643 return upper(l_success);
2644
2645 EXCEPTION
2646 WHEN OTHERS THEN
2647 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'CREATE_PERSON_PARTY',
2648 p_event.getEventName( ),p_subscription_guid,
2649 sqlerrm,sqlcode);
2650 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2651 raise;
2652 return 'ERROR';
2653
2654 END CreatePersonParty;
2655
2656 /*=======================================================================
2657 | FUNCTION RaiseAddCustAccessEvent
2658 |
2659 | DESCRIPTION
2660 |
2661 |
2662 | PARAMETERS
2663 |
2664 * ======================================================================*/
2665 ---------------------------------------------------------------------------
2666
2667 PROCEDURE RaiseAddCustAccessEvent (p_person_party_id IN VARCHAR2,
2668 p_customer_id IN VARCHAR2,
2669 p_cust_site_use_id IN VARCHAR2 DEFAULT NULL,
2670 p_cust_acct_type IN VARCHAR2,
2671 p_first_name IN VARCHAR2,
2672 p_last_name IN VARCHAR2,
2673 p_middle_name IN VARCHAR2,
2674 p_pre_name_adjunct IN VARCHAR2,
2675 p_person_suffix IN VARCHAR2,
2676 p_req_acc_no IN VARCHAR2,
2677 p_req_location IN VARCHAR2,
2678 p_req_cust_name IN VARCHAR2,
2679 p_registration_id IN VARCHAR2,
2680 p_responsibility_id IN NUMBER ) --Added this parameter for Bug 13869981
2681 ---------------------------------------------------------------------------
2682 IS
2683 l_parameter_list wf_parameter_list_t;
2684 l_event_name VARCHAR2(100);
2685 l_item_key VARCHAR2(2000);
2686 l_user_name VARCHAR2(100);
2687 l_debug_info VARCHAR2(300);
2688 l_event_key VARCHAR2(2000);
2689 l_approval_profile VARCHAR2(100);
2690 l_event wf_event_t;
2691
2692 BEGIN
2693
2694 wf_event.addParametertoList('PERSON_PARTY_ID', p_person_party_id, l_parameter_list);
2695 wf_event.addParametertoList('CUSTOMER_ID', p_customer_id, l_parameter_list);
2696 wf_event.addParametertoList('CUSTOMER_SITE_USE_ID', p_cust_site_use_id, l_parameter_list);
2697 wf_event.addParametertoList('REG_SERVICE_TYPE', 'ARI_ADD_CUST_ACCESS', l_parameter_list);
2698 wf_event.addParametertoList('CUST_ACCT_TYPE', p_cust_acct_type, l_parameter_list);
2699 wf_event.addParametertoList('FIRST_NAME', p_first_name, l_parameter_list);
2700 wf_event.addParametertoList('LAST_NAME', p_last_name, l_parameter_list);
2701 wf_event.addParametertoList('MIDDLE_NAME', p_middle_name, l_parameter_list);
2702 wf_event.addParametertoList('PRE_NAME_ADJUNCT', p_pre_name_adjunct, l_parameter_list);
2703 wf_event.addParametertoList('PERSON_SUFFIX', p_person_suffix, l_parameter_list);
2704 wf_event.addParametertoList('ACCOUNT_NUMBER', p_req_acc_no, l_parameter_list);
2705 wf_event.addParametertoList('CUSTOMER_LOCATION', p_req_location, l_parameter_list);
2706 wf_event.addParametertoList('CUSTOMER_NAME', p_req_cust_name, l_parameter_list);
2707 wf_event.addParametertoList('REGISTRATION_ID',p_registration_id , l_parameter_list);
2708 wf_event.addParametertoList('RESPONSIBILITY_ID',p_responsibility_id, l_parameter_list);
2709 IF p_person_party_id IS NULL THEN
2710 SELECT usr.user_name into l_user_name
2711 FROM fnd_user usr
2712 WHERE Usr.user_id = fnd_global.user_id;
2713 wf_event.addParametertoList('REQUESTED_USERNAME', l_user_name, l_parameter_list);
2714
2715 ELSE
2716 SELECT usr.user_name into l_user_name
2717 FROM fnd_user usr
2718 WHERE Usr.person_party_id = p_person_party_id;
2719 END IF;
2720 wf_event_t.initialize(l_event);
2721 l_event.setParameterList(l_parameter_list);
2722
2723 FND_PROFILE.Get('OIR_APPROVAL_FOR_ACCOUNT_ACCESS', l_approval_profile);
2724 l_event_name := 'oracle.apps.ar.irec.addcustaccess';
2725 SELECT UMX_REG_REQUESTS_S.nextval INTO l_item_key FROM dual;
2726
2727 IF (l_approval_profile='Y') THEN
2728
2729 WF_ENGINE.CREATEPROCESS('ARIADDAC',
2730 l_item_key,
2731 'ADD_ACCESS_ACCOUNT_REQUEST');
2732
2733 /*------------------------------------------------------------------+
2734 | Set the Person_party_id |
2735 +------------------------------------------------------------------*/
2736 WF_ENGINE.SetItemAttrText('ARIADDAC',
2737 l_item_key,
2738 'PERSON_PARTY_ID',
2739 p_person_party_id);
2740
2741
2742 WF_ENGINE.SetItemAttrText('ARIADDAC',
2743 l_item_key,
2744 'ACCOUNT_NUMBER',
2745 p_req_acc_no);
2746
2747 WF_ENGINE.SetItemAttrText('ARIADDAC',
2748 l_item_key,
2749 'CUSTOMER_NAME',
2750 p_req_cust_name);
2751 WF_ENGINE.SetItemAttrText('ARIADDAC',
2752 l_item_key,
2753 'CUSTOMER_LOCATION',
2754 p_req_location);
2755
2756 /*------------------------------------------------------------------+
2757 | Set the Person_party_id |
2758 +------------------------------------------------------------------*/
2759 WF_ENGINE.SetItemAttrText('ARIADDAC',
2760 l_item_key,
2761 'CUSTOMER_ID',
2762 p_customer_id);
2763
2764 /*------------------------------------------------------------------+
2765 | Set the Person_party_id |
2766 +------------------------------------------------------------------*/
2767 WF_ENGINE.SetItemAttrText('ARIADDAC',
2768 l_item_key,
2769 'CUSTOMER_SITE_USE_ID',
2770 p_cust_site_use_id);
2771
2772 /*------------------------------------------------------------------+
2773 | Set the Person_party_id |
2774 +------------------------------------------------------------------*/
2775 WF_ENGINE.SetItemAttrText('ARIADDAC',
2776 l_item_key,
2777 'REG_SERVICE_TYPE',
2778 'ARI_ADD_CUST_ACCESS');
2779
2780 /*------------------------------------------------------------------+
2781 | Set the Person_party_id |
2782 +------------------------------------------------------------------*/
2783 WF_ENGINE.SetItemAttrText('ARIADDAC',
2784 l_item_key,
2785 'CUST_ACCT_TYPE',
2786 p_cust_acct_type);
2787
2788 /*------------------------------------------------------------------+
2789 | Set the Person_party_id |
2790 +------------------------------------------------------------------*/
2791 WF_ENGINE.SetItemAttrText('ARIADDAC',
2792 l_item_key,
2793 'FIRST_NAME',
2794 p_first_name);
2795
2796 /*------------------------------------------------------------------+
2797 | Set the Person_party_id |
2798 +------------------------------------------------------------------*/
2799 WF_ENGINE.SetItemAttrText('ARIADDAC',
2800 l_item_key,
2801 'LAST_NAME',
2802 p_last_name);
2803
2804 /*------------------------------------------------------------------+
2805 | Set the Person_party_id |
2806 +------------------------------------------------------------------*/
2807 WF_ENGINE.SetItemAttrText('ARIADDAC',
2808 l_item_key,
2809 'MIDDLE_NAME',
2810 p_middle_name);
2811
2812
2813 /*------------------------------------------------------------------+
2814 | Set the Person_party_id |
2815 +------------------------------------------------------------------*/
2816 WF_ENGINE.SetItemAttrText('ARIADDAC',
2817 l_item_key,
2818 'PRE_NAME_ADJUNCT',
2819 p_pre_name_adjunct);
2820
2821 /*------------------------------------------------------------------+
2822 | Set the Person_party_id |
2823 +------------------------------------------------------------------*/
2824 WF_ENGINE.SetItemAttrText('ARIADDAC',
2825 l_item_key,
2826 'PERSON_SUFFIX',
2827 p_person_suffix);
2828
2829 /*------------------------------------------------------------------+
2830 | Set the Person_party_id |
2831 +------------------------------------------------------------------*/
2832 WF_ENGINE.SetItemAttrText('ARIADDAC',
2833 l_item_key,
2834 'REQUESTED_USERNAME',
2835 l_user_name);
2836
2837
2838
2839 WF_ENGINE.SetItemAttrText('ARIADDAC',
2840 l_item_key,
2841 'FND_USER',
2842 l_user_name);
2843
2844 /*------------------------------------------------------------------+
2845 | Set the Person_party_id |
2846 +------------------------------------------------------------------*/
2847 SELECT UMX_REG_REQUESTS_S.nextval INTO l_event_key FROM dual;
2848 WF_ENGINE.SetItemAttrText('ARIADDAC',
2849 l_item_key,
2850 'EVENT_KEY',
2851 l_event_key);
2852
2853 WF_ENGINE.setitemattrevent ('ARIADDAC',
2854 l_item_key,
2855 'ADD_ACCESS_DATA',
2856 l_event);
2857 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
2858 x_Wf_Item_Key => l_item_key,
2859 x_Status_Lookup_Code => 'IN PROGRESS',
2860 x_Last_Update_Date => sysdate);
2861 ----------------------------------------------------------------------------------------
2862 l_debug_info := 'Start Additional Access Approval request Workflow';
2863 -----------------------------------------------------------------------------------------
2864 IF (PG_DEBUG = 'Y') THEN
2865 arp_standard.debug(l_debug_info);
2866 END IF;
2867 WF_ENGINE.STARTPROCESS('ARIADDAC',
2868 l_item_key);
2869
2870 ELSE
2871 wf_event.raise(l_event_name,l_item_key,null,l_parameter_list,sysdate);
2872 END IF;
2873
2874 END RaiseAddCustAccessEvent;
2875
2876
2877 /*=======================================================================
2878 | FUNCTION GetRegSecurityProfile
2879 |
2880 | DESCRIPTION
2881 |
2882 |
2883 | PARAMETERS
2884 |
2885 * ======================================================================*/
2886 ---------------------------------------------------------------------------
2887 FUNCTION GetRegSecurityProfile(p_user_id IN VARCHAR2 DEFAULT NULL,
2888 p_resp_id IN VARCHAR2)
2889 RETURN VARCHAR2
2890 ---------------------------------------------------------------------------
2891 IS
2892 l_reg_sec_profile VARCHAR2(15) := 0;
2893 BEGIN
2894
2895 IF p_user_id IS NOT NULL THEN
2896 BEGIN
2897 select fpov.profile_option_value
2898 into l_reg_sec_profile
2899 from fnd_profile_option_values fpov, fnd_profile_options fpo
2900 where fpov.profile_option_id = fpo.profile_option_id
2901 and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
2902 and fpov.level_id = 10004 -- user level
2903 and fpov.level_value = p_user_id;
2904 EXCEPTION
2905 WHEN OTHERS THEN
2906 NULL;
2907 END;
2908 END IF;
2909
2910 IF l_reg_sec_profile <> 0 THEN
2911 RETURN l_reg_sec_profile;
2912 END IF;
2913
2914 BEGIN
2915 --Get the security profile value set up for the
2916 --iReceivables Registration responsibility
2917 select fpov.profile_option_value
2918 into l_reg_sec_profile
2919 from fnd_profile_option_values fpov, fnd_profile_options fpo
2920 where fpov.profile_option_id = fpo.profile_option_id
2921 and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
2922 and fpov.level_id = 10003 -- responsibility level
2923 and fpov.level_value_application_id = 222
2924 and fpov.level_value = p_resp_id; -- Resp Id of ARI_REGISTER_RESP- iReceivables Registration Responsibility
2925 EXCEPTION
2926 WHEN NO_DATA_FOUND THEN
2927 --Get the security profile set up at the site level
2928 select fpov.profile_option_value
2929 into l_reg_sec_profile
2930 from fnd_profile_option_values fpov, fnd_profile_options fpo
2931 where fpov.profile_option_id = fpo.profile_option_id
2932 and fpo.profile_option_name like 'XLA_MO_SECURITY_PROFILE_LEVEL'
2933 and fpov.level_id = 10001; -- Site level
2934
2935 return l_reg_sec_profile;
2936 END;
2937
2938 return l_reg_sec_profile;
2939
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 return l_reg_sec_profile;
2943 END GetRegSecurityProfile;
2944
2945 -- Added this for Bug# 13869981
2946 /*=======================================================================
2947 | FUNCTION UpdateRegistrationTable
2948 |
2949 | DESCRIPTION
2950 |
2951 |
2952 | PARAMETERS
2953 |
2954 * ======================================================================*/
2955 ---------------------------------------------------------------------------
2956 FUNCTION UpdateRegistrationTable(p_subscription_guid in raw,
2957 p_event in out NOCOPY WF_EVENT_T)
2958 RETURN VARCHAR2
2959 ---------------------------------------------------------------------------
2960 IS
2961 l_registration_id VARCHAR2(500);
2962 l_item_key VARCHAR2(500);
2963 l_procedure_name VARCHAR2(50) := '.UpdateRegistrationTable';
2964 l_debug_info VARCHAR2(4000);
2965
2966 BEGIN
2967 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2968 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateRegistrationTable');
2969 END IF;
2970 -- This function is called on the event 'oracle.apps.fnd.umx.startaccountrequestwf'
2971 -- To ensure that the status has been updated to In Progress
2972 l_registration_id := p_event.getvalueforparameter('REGISTRATION_ID');
2973 l_item_key := p_event.getvalueforparameter('UMX_PARENT_ITEM_KEY');
2974 ------------------------------------------------------------------------
2975 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2976 l_debug_info := 'Values in URT---'||'l_registration_id --'||l_registration_id||' l_item_key -- '||l_item_key;
2977 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,l_debug_info);
2978 end if;
2979 -------------------------------------------------------------------------
2980 --Added if condition for Bug#16475167
2981 if(l_registration_id IS NOT NULL AND l_item_key IS NOT NULL) then
2982 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => l_registration_id,
2983 x_Wf_Item_Key => l_item_key,
2984 x_Status_Lookup_Code => 'IN PROGRESS',
2985 x_Last_Update_Date => sysdate);
2986 COMMIT;
2987 end if;
2988
2989 return 'SUCCESS';
2990
2991 EXCEPTION
2992 WHEN OTHERS THEN
2993 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'UpdateRegistrationTable',
2994 p_event.getEventName( ),p_subscription_guid,
2995 sqlerrm,sqlcode);
2996 WF_EVENT.SetErrorInfo(p_event,'ERROR');
2997 return 'ERROR';
2998
2999 END UpdateRegistrationTable;
3000 /*=======================================================================
3001 | FUNCTION UpdateRejectedStatus
3002 |
3003 | DESCRIPTION This function is added as a part of Bug 13869981
3004 |
3005 |
3006 | PARAMETERS
3007 |
3008 * ======================================================================*/
3009 --------------------------------------------------------------------------
3010 FUNCTION UpdateRejectedStatus(p_subscription_guid in raw,
3011 p_event in out NOCOPY WF_EVENT_T)
3012 RETURN VARCHAR2
3013 --------------------------------------------------------------------------
3014 IS
3015 l_item_key VARCHAR2(500);
3016 l_procedure_name VARCHAR2(500) := '.UpdateRejectedStatus';
3017 l_debug_info VARCHAR2(4000);
3018 l_registration_id VARCHAR2(4000);
3019 l_item_type VARCHAR2(4000);
3020 l_status VARCHAR2(4000);
3021 l_approval_result VARCHAR2(4000);
3022
3023 BEGIN
3024 -----------------------------------------------------------------------
3025 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3026 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'In UpdateRejectedStatus Function');
3027 end if;
3028 -----------------------------------------------------------------------
3029
3030 -- This function is called on the event 'oracle.apps.ar.irec.accountrequest.notification.start'
3031 -- To ensure that the status has been updated to Rejected
3032 -- This function is called on the event 'oracle.apps.ar.irec.accountrequest.notification.start'
3033 -- To ensure that the status has been updated to Complete or Rejected in the ARI_REG_VERIFICATIONS table
3034 l_registration_id := p_event.getvalueforparameter('REGISTRATION_ID');
3035 l_item_key := p_event.getvalueforparameter('UMX_PARENT_ITEM_KEY');
3036 l_item_type := p_event.getvalueforparameter ('UMX_PARENT_ITEM_TYPE');
3037 l_approval_result := wf_engine.getitemattrtext(itemtype => l_item_type,
3038 itemkey => l_item_key,
3039 aname => 'APPROVAL_RESULT',
3040 ignore_notfound => false);
3041 -----------------------------------------------------------------------------
3042 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3043 l_debug_info := 'Values in UpdateRejectedStatus ---'||l_registration_id||l_item_key||l_approval_result;
3044 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,l_debug_info);
3045 end if;
3046 ------------------------------------------------------------------------------
3047 if (l_approval_result = 'REJECTED') then
3048 l_status := 'REJECTED';
3049 else
3050 if (l_approval_result = 'APPROVED') then
3051 l_status := 'COMPLETE';
3052 end if;
3053 end if;
3054 --Added if condition for Bug#16475167
3055 if(l_registration_id IS NOT NULL AND l_status IS NOT NULL)then
3056 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => l_registration_id,
3057 x_Status_Lookup_Code => l_status,
3058 x_Last_Update_Date => sysdate);
3059 COMMIT;
3060 end if;
3061 return upper('SUCCESS');
3062
3063 EXCEPTION
3064 WHEN OTHERS THEN
3065 WF_CORE.CONTEXT('ARI_SELF_REGISTRATION_PKG', 'UpdateRejectedStatus',
3066 p_event.getEventName( ),p_subscription_guid,
3067 sqlerrm,sqlcode);
3068 WF_EVENT.SetErrorInfo(p_event,'ERROR');
3069 return 'ERROR';
3070 END UpdateRejectedStatus;
3071
3072 /*=======================================================================
3073 | PROCEDURE ValidateRequestedCustomer
3074 |
3075 | DESCRIPTION
3076 |
3077 |
3078 | PARAMETERS
3079 |
3080 * ======================================================================*/
3081 ---------------------------------------------------------------------------
3082 PROCEDURE ValidateRequestedCustomer (p_customer_id IN VARCHAR2,
3083 x_return_status OUT NOCOPY VARCHAR2)
3084 ---------------------------------------------------------------------------
3085 IS
3086 l_count_sites NUMBER;
3087 BEGIN
3088 x_return_status := 'N';
3089
3090 SELECT count(cust_acct_site_id)
3091 INTO l_count_sites
3092 FROM hz_cust_acct_sites hcas
3093 WHERE hcaS.cust_account_id = p_customer_id;
3094
3095 IF l_count_sites > 0 THEN
3096 x_return_status := 'Y';
3097 END IF;
3098 EXCEPTION
3099 WHEN OTHERS THEN
3100 x_return_status := 'N';
3101 END ValidateRequestedCustomer;
3102
3103 /*=======================================================================
3104 | PROCEDURE GetRequestedRespId
3105 |
3106 | DESCRIPTION
3107 |
3108 |
3109 | PARAMETERS
3110 |
3111 * ======================================================================*/
3112 ---------------------------------------------------------------------------
3113 FUNCTION GetRequestedRespId (p_role_name IN VARCHAR2)
3114 RETURN VARCHAR2
3115 ---------------------------------------------------------------------------
3116 IS
3117 l_resp_id VARCHAR2(30) := 0;
3118 BEGIN
3119
3120 SELECT to_char(resp.responsibility_id)
3121 INTO l_resp_id
3122 FROM fnd_responsibility_vl resp, wf_roles role
3123 WHERE role.name = p_role_name
3124 AND resp.responsibility_name = role.display_name;
3125
3126 RETURN l_resp_id;
3127
3128 EXCEPTION
3129 WHEN OTHERS THEN
3130 RETURN l_resp_id;
3131 END GetRequestedRespId;
3132
3133 PROCEDURE CheckPastRegStatus(p_user_name IN VARCHAR2,
3134 p_access_domain_search_number IN VARCHAR2,
3135 p_access_domain_billto_id IN NUMBER,
3136 p_access_domain_search_type IN VARCHAR2,
3137 p_registration_id OUT NOCOPY NUMBER,
3138 p_past_reg_status OUT NOCOPY VARCHAR2) IS
3139
3140
3141 l_registration_cur GenCursorRef;
3142 l_registration_id NUMBER;
3143 l_company_id NUMBER;
3144 l_access_domain_search_type VARCHAR2(30);
3145 l_access_domain_cust_id VARCHAR2(30);
3146 l_registration_status VARCHAR2(30);
3147 l_profile_reg_hold_type VARCHAR2(30);
3148 l_profile_reg_hold_days NUMBER;
3149 l_release_if_on_hold_flag BOOLEAN := FALSE;
3150 l_days_since_last_update NUMBER;
3151 l_days_since_latest_hold_date NUMBER;
3152 l_reg_count NUMBER;
3153 l_procedure_name VARCHAR2(30) := '.CheckPastRegStatus';
3154 l_debug_info VARCHAR2(200);
3155 BEGIN
3156 ----------------------------------------------------------------------------
3157 l_debug_info := 'In debug mode, log we have entered this procedure';
3158 ----------------------------------------------------------------------------
3159 IF (PG_DEBUG = 'Y') THEN
3160 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3161 END IF;
3162
3163 ----------------------------------------------------------------------------
3164 l_debug_info := 'Retrieve value of RegHoldDomain profile' ||
3165 p_past_reg_status;
3166 ----------------------------------------------------------------------------
3167 FND_PROFILE.Get('ARI_REG_HOLD_DOMAIN', l_profile_reg_hold_type);
3168
3169 ----------------------------------------------------------------------------
3170 l_debug_info := 'Retrieve value of RegHoldNumOfDays profile' ||
3171 p_past_reg_status;
3172 ----------------------------------------------------------------------------
3173 FND_PROFILE.GET('ARI_REG_HOLD_DURATION', l_profile_reg_hold_days);
3174
3175 ----------------------------------------------------------------------------
3176 l_debug_info := 'OpenRegistrationCur checking for holds';
3177 ----------------------------------------------------------------------------
3178 IF (PG_DEBUG = 'Y') THEN
3179 arp_standard.debug(l_debug_info);
3180 END IF;
3181
3182 if(l_profile_reg_hold_type is null OR '' = l_profile_reg_hold_type) then
3183 l_profile_reg_hold_type := 'USER';
3184 end if;
3185
3186 if(l_profile_reg_hold_days is null) then
3187 l_profile_reg_hold_days := 0;
3188 end if;
3189
3190 IF (PG_DEBUG = 'Y') THEN
3191 arp_standard.debug('l_profile_reg_hold_type '||l_profile_reg_hold_type||' l_profile_reg_hold_days '||l_profile_reg_hold_days);
3192 END IF;
3193
3194 l_access_domain_cust_id := ARI_SELF_REG_CONFIG.get_customer_id(p_access_domain_search_type,p_access_domain_search_number);
3195
3196 OpenRegistrationCur(p_cust_account_id => l_access_domain_cust_id,
3197 p_billto_id => p_access_domain_billto_id,
3198 p_user_name => p_user_name,
3199 p_application_code => 'ARI',
3200 p_mode => l_profile_reg_hold_type|| '_' ||G_HOLD,
3201 p_registration_cur => l_registration_cur);
3202
3203 ----------------------------------------------------------------------------
3204 l_debug_info := 'Retrieve registration records from cursor';
3205 ----------------------------------------------------------------------------
3206 IF (PG_DEBUG = 'Y') THEN
3207 arp_standard.debug(l_debug_info);
3208 END IF;
3209 LOOP
3210 FETCH l_registration_cur INTO l_days_since_latest_hold_date;
3211 EXIT WHEN l_registration_cur%NOTFOUND;
3212 END LOOP;
3213
3214 IF (l_registration_cur%ROWCOUNT > 0) THEN
3215 IF (l_days_since_latest_hold_date < l_profile_reg_hold_days) THEN
3216 p_past_reg_status := G_HOLD;
3217 RETURN;
3218 ELSE
3219 l_release_if_on_hold_flag := TRUE;
3220 END IF;
3221 END IF;
3222 ----------------------------------------------------------------------------
3223 l_debug_info := 'OpenRegistrationCur To Check User Registration Count';
3224 ----------------------------------------------------------------------------
3225 IF (PG_DEBUG = 'Y') THEN
3226 arp_standard.debug(l_debug_info);
3227 END IF;
3228 OpenRegistrationCur(p_cust_account_id => l_access_domain_cust_id,
3229 p_billto_id => p_access_domain_billto_id,
3230 p_user_name => p_user_name,
3231 p_application_code => 'ARI',
3232 p_mode => G_USER_REG_COUNT,
3233 p_registration_cur => l_registration_cur);
3234
3235 FETCH l_registration_cur INTO l_reg_count;
3236 ----------------------------------------------------------------------------
3237 l_debug_info := 'OpenRegistrationCur To Check current Registration Status';
3238 ----------------------------------------------------------------------------
3239 IF (PG_DEBUG = 'Y') THEN
3240 arp_standard.debug(l_debug_info);
3241 END IF;
3242 OpenRegistrationCur(p_cust_account_id => l_access_domain_cust_id,
3243 p_billto_id => p_access_domain_billto_id,
3244 p_user_name => p_user_name,
3245 p_application_code => 'ARI',
3246 p_mode => G_CUST_ACCT_N_USER,
3247 p_registration_cur => l_registration_cur);
3248
3249 ----------------------------------------------------------------------------
3250 l_debug_info := 'Retrieve registration records from cursor in User Mode';
3251 ----------------------------------------------------------------------------
3252 IF (PG_DEBUG = 'Y') THEN
3253 arp_standard.debug(l_debug_info);
3254 END IF;
3255 LOOP
3256 FETCH l_registration_cur INTO l_registration_id, l_access_domain_search_type,
3257 l_access_domain_cust_id, l_registration_status,
3258 l_company_id, l_days_since_last_update;
3259 EXIT WHEN l_registration_cur%NOTFOUND;
3260 IF (PG_DEBUG = 'Y') THEN
3261 arp_standard.debug(to_char(l_registration_id));
3262 END IF;
3263 END LOOP;
3264
3265 IF (l_registration_cur%ROWCOUNT = 1) THEN
3266
3267 --------------------------------------------------------------------------
3268 l_debug_info := 'One record returned by registration cursor';
3269 --------------------------------------------------------------------------
3270 IF (PG_DEBUG = 'Y') THEN
3271 arp_standard.debug(l_debug_info);
3272 END IF;
3273
3274 IF (l_registration_status IS NULL) THEN
3275
3276 IF (l_reg_count > 0) THEN
3277
3278 ------------------------------------------------------------------------
3279 l_debug_info := 'Existing registration did not complete, so this is a' ||
3280 'add access retry';
3281 --------------------------------------------------------------------------
3282 IF (PG_DEBUG = 'Y') THEN
3283 arp_standard.debug(l_debug_info);
3284 END IF;
3285 p_past_reg_status := G_NEW_ACCESS_RETRY;
3286
3287 ELSE
3288 ------------------------------------------------------------------------
3289 l_debug_info := 'Existing registration did not complete, so this is a' ||
3290 'retry';
3291 --------------------------------------------------------------------------
3292 IF (PG_DEBUG = 'Y') THEN
3293 arp_standard.debug(l_debug_info);
3294 END IF;
3295 p_past_reg_status := G_RETRY;
3296
3297 END IF;
3298
3299 ELSIF (l_registration_status = G_HOLD) THEN
3300
3301 IF (l_release_if_on_hold_flag OR (l_days_since_last_update > l_profile_reg_hold_days)) THEN
3302
3303 ---------------------------------------------------------------------------
3304 l_debug_info := 'Release the hold for registration_id: ' || to_char(l_registration_id);
3305 ---------------------------------------------------------------------------
3306 ReleaseRegHold(p_registration_id => l_registration_id,
3307 p_reg_status_lookup_code => p_past_reg_status);
3308
3309 ELSE
3310 p_past_reg_status := l_registration_status;
3311
3312 END IF;
3313
3314 ELSE
3315 ------------------------------------------------------------------------
3316 l_debug_info := 'Return not null past reg status for pre-existing ' ||
3317 'registration';
3318 ------------------------------------------------------------------------
3319 IF (PG_DEBUG = 'Y') THEN
3320 arp_standard.debug(l_debug_info);
3321 END IF;
3322 p_past_reg_status := l_registration_status;
3323
3324 END IF; -- l_registration_status IS NOT NULL
3325
3326 ------------------------------------------------------------------------
3327 l_debug_info := 'Pass back previous registration id';
3328 ------------------------------------------------------------------------
3329 IF (PG_DEBUG = 'Y') THEN
3330 arp_standard.debug(l_debug_info);
3331 END IF;
3332 p_registration_id := l_registration_id;
3333
3334 ELSIF (l_registration_cur%ROWCOUNT = 0) THEN
3335
3336 IF (l_reg_count > 0) THEN
3337 --------------------------------------------------------------------------
3338 l_debug_info := 'User has pre-existing registrations returned by registration ' ||
3339 'cursor';
3340 --------------------------------------------------------------------------
3341 IF (PG_DEBUG = 'Y') THEN
3342 arp_standard.debug(l_debug_info);
3343 END IF;
3344 p_registration_id := NULL;
3345 p_past_reg_status := G_NEW_ACCESS;
3346
3347 ELSE
3348 --------------------------------------------------------------------------
3349 l_debug_info := 'No pre-existing registrations returned by registration ' ||
3350 'cursor';
3351 --------------------------------------------------------------------------
3352 IF (PG_DEBUG = 'Y') THEN
3353 arp_standard.debug(l_debug_info);
3354 END IF;
3355 p_registration_id := NULL;
3356 p_past_reg_status := G_NEW;
3357
3358 END IF;
3359
3360 END IF;
3361 ----------------------------------------------------------------------------
3362 l_debug_info := 'CLOSE registration_cur';
3363 ----------------------------------------------------------------------------
3364 IF (PG_DEBUG = 'Y') THEN
3365 arp_standard.debug(l_debug_info);
3366 END IF;
3367 CLOSE l_registration_cur;
3368
3369 ----------------------------------------------------------------------------
3370 l_debug_info := 'In debug mode, log that we have exited this procedure';
3371 ----------------------------------------------------------------------------
3372 IF (PG_DEBUG = 'Y') THEN
3373 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
3374 END IF;
3375
3376 EXCEPTION
3377 WHEN OTHERS THEN
3378 IF (SQLCODE <> -20001) THEN
3379 IF (PG_DEBUG = 'Y') THEN
3380 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3381 arp_standard.debug('Debug Info: ' || l_debug_info);
3382 arp_standard.debug(SQLERRM);
3383 END IF;
3384 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3385 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3386 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3387 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3388 FND_MSG_PUB.ADD;
3389 InformSysAdminError(p_procedure_name => l_procedure_name,
3390 p_debug_info => l_debug_info,
3391 p_error => SQLERRM);
3392 END IF;
3393 APP_EXCEPTION.RAISE_EXCEPTION;
3394 END CheckPastRegStatus;
3395
3396 PROCEDURE OpenRegistrationCur(p_cust_account_id IN VARCHAR2,
3397 p_billto_id IN VARCHAR2,
3398 p_user_name IN VARCHAR2,
3399 p_application_code IN VARCHAR2,
3400 p_mode IN VARCHAR2,
3401 p_registration_cur OUT NOCOPY GenCursorRef)
3402 --------------------------------------------------------------------------
3403 IS
3404 l_procedure_name VARCHAR2(30) := '.OpenRegistrationCur';
3405 l_debug_info VARCHAR2(200);
3406 BEGIN
3407 --------------------------------------------------------------------
3408 l_debug_info := 'In debug mode, log we have entered this procedure';
3409 --------------------------------------------------------------------
3410 IF (PG_DEBUG = 'Y') THEN
3411 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3412 END IF;
3413 --------------------------------------------------------------------
3414 l_debug_info := 'OpenRegistrationCur, mode = ' || p_mode;
3415 --------------------------------------------------------------------
3416 IF (p_mode = G_CUST_ACCT_HOLD) THEN
3417 OPEN p_registration_cur FOR
3418 SELECT trunc(sysdate - max(last_update_date))
3419 FROM ARI_REG_VERIFICATIONS
3420 WHERE access_domain_cust_id = p_cust_account_id
3421 AND application_code = p_application_code
3422 AND status = G_HOLD;
3423
3424 ELSIF (p_mode = G_USER_HOLD) THEN
3425
3426 OPEN p_registration_cur FOR
3427 SELECT trunc(sysdate - max(last_update_date))
3428 FROM ARI_REG_VERIFICATIONS
3429 WHERE UPPER(user_email_addr) = UPPER(p_user_name)
3430 AND application_code = p_application_code
3431 AND status = G_HOLD;
3432
3433 ELSIF (p_mode = G_BILLTO_SITE_HOLD) THEN
3434 OPEN p_registration_cur FOR
3435 SELECT trunc(sysdate - max(last_update_date))
3436 FROM ARI_REG_VERIFICATIONS
3437 WHERE access_domain_cust_id = p_cust_account_id
3438 AND access_domain_billto_id = p_billto_id
3439 AND application_code = p_application_code
3440 AND status = G_HOLD;
3441
3442 ELSIF (p_mode = G_USER_REG_COUNT) THEN
3443
3444 OPEN p_registration_cur FOR
3445 SELECT count(*)
3446 FROM ARI_REG_VERIFICATIONS
3447 WHERE UPPER(user_email_addr) = UPPER(p_user_name)
3448 AND application_code = p_application_code
3449 AND access_domain_cust_id = p_cust_account_id
3450 AND status = 'COMPLETE';
3451
3452 ELSIF (p_mode = G_CUST_ACCT_N_USER) THEN
3453
3454 OPEN p_registration_cur FOR
3455 SELECT registration_id,
3456 access_domain_search_type,
3457 access_domain_cust_id,
3458 status,
3459 company_id,
3460 trunc(sysdate - last_update_date)
3461 FROM ARI_REG_VERIFICATIONS
3462 WHERE UPPER(user_email_addr) = UPPER(p_user_name)
3463 AND access_domain_cust_id = p_cust_account_id
3464 AND (access_domain_billto_id is null or access_domain_billto_id = nvl(p_billto_id,-1))
3465 AND application_code = p_application_code
3466 AND (status IS NULL OR
3467 status <> 'REJECTED');
3468 ELSE
3469 --------------------------------------------------------------------
3470 l_debug_info := 'Invalid Mode passed to OpenRegistrationCur ' || p_mode;
3471 --------------------------------------------------------------------
3472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3473 END IF;
3474 --------------------------------------------------------------------
3475 l_debug_info := 'In debug mode, log we have exited this procedure';
3476 --------------------------------------------------------------------
3477 IF (PG_DEBUG = 'Y') THEN
3478 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
3479 END IF;
3480
3481 EXCEPTION
3482 WHEN OTHERS THEN
3483 IF (SQLCODE <> -20001) THEN
3484 IF (PG_DEBUG = 'Y') THEN
3485 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3486 arp_standard.debug('Debug Info: ' || l_debug_info);
3487 arp_standard.debug(SQLERRM);
3488 END IF;
3489 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3490 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3491 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3492 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3493 FND_MSG_PUB.ADD;
3494 InformSysAdminError(p_procedure_name => l_procedure_name,
3495 p_debug_info => l_debug_info,
3496 p_error => SQLERRM);
3497 END IF;
3498 APP_EXCEPTION.RAISE_EXCEPTION;
3499 END OpenRegistrationCur;
3500
3501 PROCEDURE ReleaseRegHold(p_registration_id IN NUMBER,
3502 p_reg_status_lookup_code OUT NOCOPY VARCHAR2)
3503 ---------------------------------------------------------------------------
3504 IS
3505 l_procedure_name VARCHAR2(30) := '.ReleaseRegHold';
3506 l_debug_info VARCHAR2(200);
3507 BEGIN
3508 ----------------------------------------------------------------------------
3509 l_debug_info := 'In debug mode, log we have entered this procedure';
3510 ----------------------------------------------------------------------------
3511 IF (PG_DEBUG = 'Y') THEN
3512 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3513 END IF;
3514
3515 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
3516 x_Status_Lookup_Code => G_RETRY,
3517 x_Missed_Cust_Question_Count => 0,
3518 x_Missed_Billto_Question_Count => 0);
3519
3520 p_reg_status_lookup_code := G_RETRY;
3521
3522 ----------------------------------------------------------------------------
3523 l_debug_info := 'In debug mode, log that we have exited this procedure';
3524 ----------------------------------------------------------------------------
3525 IF (PG_DEBUG = 'Y') THEN
3526 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
3527 END IF;
3528 COMMIT;
3529 EXCEPTION
3530 WHEN OTHERS THEN
3531 IF (SQLCODE <> -20001) THEN
3532 IF (PG_DEBUG = 'Y') THEN
3533 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3534 arp_standard.debug('Debug Info: ' || l_debug_info);
3535 arp_standard.debug(SQLERRM);
3536 END IF;
3537 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3538 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3539 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3540 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3541 FND_MSG_PUB.ADD;
3542 InformSysAdminError(p_procedure_name => l_procedure_name,
3543 p_debug_info => l_debug_info,
3544 p_error => SQLERRM);
3545 END IF;
3546 APP_EXCEPTION.RAISE_EXCEPTION;
3547 END ReleaseRegHold;
3548
3549 PROCEDURE LogRegistration(p_registration_id IN OUT NOCOPY NUMBER,
3550 p_application_code IN VARCHAR2,
3551 p_user_email_addr IN VARCHAR2,
3552 p_access_domain_search_type IN VARCHAR2,
3553 p_access_domain_search_number IN VARCHAR2,
3554 p_access_domain_type IN VARCHAR2,
3555 p_access_domain_cust_id IN NUMBER,
3556 p_access_domain_billto_id IN NUMBER,
3557 p_registration_type IN VARCHAR2) --Added this parameter for Bug# 13869981
3558 ---------------------------------------------------------------------------
3559 IS
3560 l_rowid VARCHAR2(100);
3561 l_procedure_name VARCHAR2(30) := '.LogRegistration';
3562 l_debug_info VARCHAR2(200);
3563 l_access_domain_number NUMBER;
3564 l_cust_acct_cur GenCursorRef;
3565 l_cust_acct_id NUMBER;
3566 l_cust_acct_number VARCHAR2(50);
3567 l_party_id NUMBER;
3568 l_company_id NUMBER;
3569 l_person_id NUMBER;
3570 l_cust_acct_type VARCHAR2(30);
3571 l_party_type VARCHAR2(30);
3572 BEGIN
3573 ----------------------------------------------------------------------------
3574 l_debug_info := 'In debug mode, log we have entered this procedure';
3575 ----------------------------------------------------------------------------
3576 IF (PG_DEBUG = 'Y') THEN
3577 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3578 END IF;
3579
3580 OpenCustAcctCur(p_customer_id => p_access_domain_cust_id,
3581 p_cust_acct_cur => l_cust_acct_cur);
3582
3583 ------------------------------------------------------------------------
3584 l_debug_info := 'Fetching the results of the cursor';
3585 ------------------------------------------------------------------------
3586 IF (PG_DEBUG = 'Y') THEN
3587 arp_standard.debug(l_debug_info);
3588 END IF;
3589 ------------------------------------------------------------------------
3590 LOOP
3591 FETCH l_cust_acct_cur INTO l_cust_acct_id,
3592 l_cust_acct_number,
3593 l_party_id,
3594 l_party_type;
3595
3596 EXIT WHEN l_cust_acct_cur%NOTFOUND;
3597 END LOOP;
3598
3599 IF (PG_DEBUG = 'Y') THEN
3600 arp_standard.debug(l_debug_info);
3601 END IF;
3602
3603 IF (l_party_type = G_ORGANIZATION) THEN
3604 l_cust_acct_type := G_BUSINESS;
3605 l_company_id := l_party_id;
3606 ELSE
3607 l_cust_acct_type := G_CONSUMER;
3608 l_person_id := l_party_id;
3609 END IF;
3610 ----------------------------------------------------------------------------
3611 l_debug_info := 'Insert New Registration Record';
3612 ----------------------------------------------------------------------------
3613 IF (PG_DEBUG = 'Y') THEN
3614 arp_standard.debug(l_debug_info);
3615 END IF;
3616 ARI_REG_VERIFY_PKG.Insert_Row(
3617 x_Rowid => l_rowid,
3618 x_Registration_Id => p_registration_id,
3619 x_Application_Code => p_application_code,
3620 x_User_Email_Addr => upper(p_user_email_addr), -- Modified as a part of Bug# 13869981
3621 x_Access_Domain_Search_Type => p_access_domain_search_type,
3622 x_Access_Domain_Search_Number => p_access_domain_search_number,
3623 x_Access_Domain_Type => p_access_domain_type,
3624 x_Access_Domain_Cust_Id => l_cust_acct_id,
3625 x_Access_Domain_Billto_Id => p_access_domain_billto_id,
3626 x_Access_Domain_Number => l_cust_acct_number,
3627 x_Cust_Acct_Type => l_cust_acct_type,
3628 x_Status_Lookup_Code => 'NEW', -- Modified the parameter value for Bug 13869981
3629 x_Missed_Cust_Question_Count => 0,
3630 x_Missed_Billto_Question_Count => 0,
3631 x_Company_Id => l_company_id,
3632 x_Person_Id => l_person_id,
3633 x_Last_Update_Date => sysdate,
3634 x_Last_Updated_By => nvl(FND_GLOBAL.user_id,-1),
3635 x_Creation_Date => sysdate,
3636 x_Created_By => nvl(FND_GLOBAL.user_id,-1),
3637 x_last_update_login => nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
3638 x_Registration_Type => p_registration_type);
3639 COMMIT;
3640 ----------------------------------------------------------------------------
3641 l_debug_info := 'In debug mode, log that we have exited this procedure';
3642 ----------------------------------------------------------------------------
3643 IF (PG_DEBUG = 'Y') THEN
3644 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
3645 END IF;
3646
3647 EXCEPTION
3648 WHEN OTHERS THEN
3649 IF (SQLCODE <> -20001) THEN
3650 IF (PG_DEBUG = 'Y') THEN
3651 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3652 arp_standard.debug('Debug Info: ' || l_debug_info);
3653 arp_standard.debug(SQLERRM);
3654 END IF;
3655 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3656 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3657 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3658 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3659 FND_MSG_PUB.ADD;
3660 InformSysAdminError(p_procedure_name => l_procedure_name,
3661 p_debug_info => l_debug_info,
3662 p_error => SQLERRM);
3663 END IF;
3664 APP_EXCEPTION.RAISE_EXCEPTION;
3665 END LogRegistration;
3666
3667 PROCEDURE OpenInvCustAcctCur(p_access_domain_search_number IN VARCHAR2,
3668 p_inv_cust_acct_cur OUT NOCOPY GenCursorRef)
3669 --------------------------------------------------------------------------
3670 IS
3671 l_procedure_name VARCHAR2(30) := '.OpenInvCustAcctCur';
3672 l_debug_info VARCHAR2(200);
3673 BEGIN
3674
3675 --------------------------------------------------------------------
3676 l_debug_info := 'In debug mode, log we have entered this procedure';
3677 --------------------------------------------------------------------
3678 IF (PG_DEBUG = 'Y') THEN
3679 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3680 END IF;
3681
3682 --------------------------------------------------------------------
3683 l_debug_info := 'Open p_cust_acct_cur';
3684 --------------------------------------------------------------------
3685 OPEN p_inv_cust_acct_cur FOR
3686 SELECT CustAcct.cust_account_id,
3687 CustAcct.account_number,
3688 CustAcct.party_id,
3689 Party.party_type,
3690 CustTrxn.org_id
3691 FROM HZ_CUST_ACCOUNTS CustAcct,
3692 RA_CUSTOMER_TRX_ALL CustTrxn,
3693 HZ_PARTIES Party
3694 WHERE CustTrxn.trx_number = p_access_domain_search_number
3695 AND CustTrxn.bill_to_customer_id = CustAcct.cust_account_id
3696 AND CustAcct.party_id = Party.party_id;
3697
3698 --------------------------------------------------------------------
3699 l_debug_info := 'In debug mode, log we have exited this procedure';
3700 --------------------------------------------------------------------
3701 IF (PG_DEBUG = 'Y') THEN
3702 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
3703 END IF;
3704
3705 EXCEPTION
3706 WHEN OTHERS THEN
3707 IF (SQLCODE <> -20001) THEN
3708 IF (PG_DEBUG = 'Y') THEN
3709 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3710 arp_standard.debug('Debug Info: ' || l_debug_info);
3711 arp_standard.debug(SQLERRM);
3712 END IF;
3713 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3714 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3715 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3716 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3717 FND_MSG_PUB.ADD;
3718 InformSysAdminError(p_procedure_name => l_procedure_name,
3719 p_debug_info => l_debug_info,
3720 p_error => SQLERRM);
3721 END IF;
3722 APP_EXCEPTION.RAISE_EXCEPTION;
3723 END OpenInvCustAcctCur;
3724
3725 PROCEDURE HoldRegistrationRequest(p_registration_id IN VARCHAR2,
3726 p_status IN VARCHAR2)
3727 IS
3728 l_procedure_name VARCHAR2(30) := '.HoldRegistrationRequest';
3729 l_debug_info VARCHAR2(200);
3730 BEGIN
3731
3732 --------------------------------------------------------------------
3733 l_debug_info := 'In debug mode, log we have entered this procedure';
3734 --------------------------------------------------------------------
3735 IF (PG_DEBUG = 'Y') THEN
3736 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3737 END IF;
3738
3739 --------------------------------------------------------------------
3740 l_debug_info := 'Open p_cust_acct_cur';
3741 --------------------------------------------------------------------
3742 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
3743 x_Status_Lookup_Code => p_status);
3744 COMMIT;
3745 EXCEPTION
3746 WHEN OTHERS THEN
3747 IF (SQLCODE <> -20001) THEN
3748 IF (PG_DEBUG = 'Y') THEN
3749 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3750 arp_standard.debug('Debug Info: ' || l_debug_info);
3751 arp_standard.debug(SQLERRM);
3752 END IF;
3753 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3754 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3755 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3756 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3757 FND_MSG_PUB.ADD;
3758 InformSysAdminError(p_procedure_name => l_procedure_name,
3759 p_debug_info => l_debug_info,
3760 p_error => SQLERRM);
3761 END IF;
3762 APP_EXCEPTION.RAISE_EXCEPTION;
3763 END HoldRegistrationRequest;
3764
3765 PROCEDURE IncrementMissedCount(p_registration_id IN VARCHAR2,
3766 p_missed_cust_count IN VARCHAR2,
3767 p_missed_billto_count IN VARCHAR2)
3768 IS
3769 l_procedure_name VARCHAR2(30) := '.IncrementMissedCount';
3770 l_debug_info VARCHAR2(200);
3771 BEGIN
3772
3773 --------------------------------------------------------------------
3774 l_debug_info := 'In debug mode, log we have entered this procedure';
3775 --------------------------------------------------------------------
3776 IF (PG_DEBUG = 'Y') THEN
3777 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3778 END IF;
3779 --------------------------------------------------------------------
3780 l_debug_info := 'Open p_cust_acct_cur';
3781 --------------------------------------------------------------------
3782 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
3783 x_Missed_Cust_Question_Count => p_missed_cust_count,
3784 x_Missed_Billto_Question_Count => p_missed_billto_count,
3785 x_Last_Update_Date => sysdate);
3786 COMMIT;
3787 EXCEPTION
3788 WHEN OTHERS THEN
3789 IF (SQLCODE <> -20001) THEN
3790 IF (PG_DEBUG = 'Y') THEN
3791 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3792 arp_standard.debug('Debug Info: ' || l_debug_info);
3793 arp_standard.debug(SQLERRM);
3794 END IF;
3795 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3796 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3797 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3798 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3799 FND_MSG_PUB.ADD;
3800 InformSysAdminError(p_procedure_name => l_procedure_name,
3801 p_debug_info => l_debug_info,
3802 p_error => SQLERRM);
3803 END IF;
3804 APP_EXCEPTION.RAISE_EXCEPTION;
3805 END IncrementMissedCount;
3806
3807 PROCEDURE UpdateBilltoSite(p_registration_id IN VARCHAR2,
3808 p_access_domain_billto_id IN VARCHAR2)
3809 IS
3810 l_procedure_name VARCHAR2(30) := '.UpdateBilltoSite';
3811 l_debug_info VARCHAR2(200);
3812 BEGIN
3813
3814 --------------------------------------------------------------------
3815 l_debug_info := 'In debug mode, log we have entered this procedure';
3816 --------------------------------------------------------------------
3817 IF (PG_DEBUG = 'Y') THEN
3818 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
3819 END IF;
3820 --------------------------------------------------------------------
3821 l_debug_info := 'Open p_cust_acct_cur';
3822 --------------------------------------------------------------------
3823 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
3824 x_Access_Domain_Billto_Id => p_access_domain_billto_id,
3825 x_Last_Update_Date => sysdate);
3826 COMMIT;
3827 EXCEPTION
3828 WHEN OTHERS THEN
3829 IF (SQLCODE <> -20001) THEN
3830 IF (PG_DEBUG = 'Y') THEN
3831 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3832 arp_standard.debug('Debug Info: ' || l_debug_info);
3833 arp_standard.debug(SQLERRM);
3834 END IF;
3835 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3836 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3837 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3838 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3839 FND_MSG_PUB.ADD;
3840 InformSysAdminError(p_procedure_name => l_procedure_name,
3841 p_debug_info => l_debug_info,
3842 p_error => SQLERRM);
3843 END IF;
3844 APP_EXCEPTION.RAISE_EXCEPTION;
3845 END UpdateBilltoSite;
3846 -- Added this for Bug# 13869981
3847 /* =======================================================================
3848 | PROCEDURE UpdateRequestAccess
3849 |
3850 | DESCRIPTION This procedure updates the status(New Access,New Access Retry)
3851 |into the table.
3852 |
3853 |
3854 | PARAMETERS
3855 |
3856 * ======================================================================*/
3857
3858 PROCEDURE UpdateRequestAccess(p_registration_id IN VARCHAR2,
3859 p_registration_type IN VARCHAR2,
3860 p_registration_status IN VARCHAR2)
3861 IS
3862 l_procedure_name VARCHAR2(300) := '.UpdateRequestAccess';
3863 l_debug_info VARCHAR2(4000);
3864 BEGIN
3865
3866 --------------------------------------------------------------------
3867 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3868 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateRequestAccess');
3869 end if;
3870 --------------------------------------------------------------------
3871
3872 --------------------------------------------------------------------
3873 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3874 l_debug_info := 'UpdateRequestAccess param values --- '||p_registration_id||p_registration_type||p_registration_status;
3875 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,l_debug_info);
3876 end if;
3877 ---------------------------------------------------------------------
3878 ARI_REG_VERIFY_PKG.Update_Row(x_Registration_id => p_registration_id,
3879 x_Registration_Type => p_registration_type,
3880 x_Status_Lookup_Code => p_registration_status,
3881 x_Last_Update_Date => sysdate);
3882 COMMIT;
3883 EXCEPTION
3884 WHEN OTHERS THEN
3885 IF (SQLCODE <> -20001) THEN
3886 IF (PG_DEBUG = 'Y') THEN
3887 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3888 arp_standard.debug('Debug Info: ' || l_debug_info);
3889 arp_standard.debug(SQLERRM);
3890 END IF;
3891 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3892 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3893 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3894 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3895 FND_MSG_PUB.ADD;
3896 InformSysAdminError(p_procedure_name => l_procedure_name,
3897 p_debug_info => l_debug_info,
3898 p_error => SQLERRM);
3899 END IF;
3900 APP_EXCEPTION.RAISE_EXCEPTION;
3901 END UpdateRequestAccess;
3902
3903 PROCEDURE Set_Application_Login_Url(p_itemtype IN VARCHAR2,
3904 p_itemkey IN VARCHAR2,
3905 p_actid IN NUMBER,
3906 p_funcmode IN VARCHAR2,
3907 p_result IN OUT NOCOPY VARCHAR2) IS
3908 -----------------------------------------------------------------------------
3909 l_app_short_name VARCHAR2(30);
3910 l_url VARCHAR2(2000);
3911 l_procedure_name VARCHAR2(30) := 'Set_Application_Login_Url';
3912 l_result_code VARCHAR2(1);
3913 l_error_msg VARCHAR2(2000);
3914 l_debug_info VARCHAR2(200);
3915 BEGIN
3916
3917 --------------------------------------------------------------------
3918 l_debug_info := 'Begin proc Set_Application_Login_Url';
3919 -------------------------------------------------------------------
3920 --
3921 -- RUN mode - normal process execution
3922 --
3923 IF (p_funcmode = 'RUN') THEN
3924
3925 --------------------------------------------------------------------
3926 l_debug_info := 'Retrieve Login URL';
3927 -------------------------------------------------------------------
3928 FND_MESSAGE.Set_Name('AR','ARI_REG_APPLICATION_LOGIN_URL');
3929
3930 l_url := FND_MESSAGE.Get;
3931
3932 ---------------------------------------------------------------------------
3933 l_debug_info := 'Set Login Url to access current application';
3934 ----------------------------------------------------------------------------
3935 WF_ENGINE.SetItemAttrText(p_itemtype,
3936 p_itemkey,
3937 'APPLICATION_URL',
3938 l_url);
3939
3940 p_result := 'COMPLETE';
3941 RETURN;
3942 END IF;
3943
3944 --
3945 -- CANCEL mode - activity 'compensation'
3946 --
3947 -- This is in the event that the activity must be undone,
3948 -- for example when a process is reset to an earlier point
3949 -- due to a loop back.
3950 --
3951 IF (p_funcmode = 'CANCEL') THEN
3952
3953 -- your cancel code goes here
3954 null;
3955
3956 -- no result needed
3957 p_result := 'COMPLETE';
3958 RETURN;
3959 END IF;
3960
3961 --
3962 -- Other execution modes may be created in the future. Your
3963 -- activity will indicate that it does not implement a mode
3964 -- by returning null
3965 --
3966 p_result := '';
3967 RETURN;
3968
3969
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 IF (SQLCODE = -20001) THEN
3973 l_error_msg := FND_MESSAGE.Get;
3974 ELSE
3975 l_error_msg := l_debug_info || 'error: ' || SQLERRM;
3976 END IF;
3977
3978 IF (SQLCODE <> -20001) THEN
3979 l_error_msg := FND_MESSAGE.Get;
3980 IF (PG_DEBUG = 'Y') THEN
3981 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
3982 arp_standard.debug('Debug Info: ' || l_debug_info);
3983 arp_standard.debug(l_error_msg);
3984 END IF;
3985 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
3986 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3987 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
3988 FND_MESSAGE.SET_TOKEN('ERROR',l_error_msg);
3989 FND_MSG_PUB.ADD;
3990 InformSysAdminError(p_procedure_name => l_procedure_name,
3991 p_debug_info => l_debug_info,
3992 p_error => l_error_msg);
3993 END IF;
3994 APP_EXCEPTION.RAISE_EXCEPTION;
3995
3996 END Set_Application_Login_Url;
3997
3998 /*=======================================================================
3999 | PROCEDURE UpdateApprovalStatus
4000 |
4001 | DESCRIPTION This is used to set the status of Add.Req.Access registration
4002 | record to COMPLETE when the notification has been approved.
4003 | Created as a part of Bug 13869981
4004 |
4005 | PARAMETERS
4006 |
4007 * ======================================================================*/
4008 ---------------------------------------------------------------------------
4009 PROCEDURE UpdateApprovalStatus(p_itemtype IN VARCHAR2,
4010 p_itemkey IN VARCHAR2,
4011 p_actid IN NUMBER,
4012 p_funcmode IN VARCHAR2,
4013 p_result IN OUT NOCOPY VARCHAR2) IS
4014 -----------------------------------------------------------------------------
4015 l_procedure_name VARCHAR2(300) := 'UpdateApprovalStatus';
4016 l_error_msg VARCHAR2(2000);
4017 l_debug_info VARCHAR2(4000);
4018 BEGIN
4019 mo_global.init('AR');
4020 ----------------------------------------------------------------------
4021 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4022 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateApprovalStatus'||p_itemkey);
4023 end if;
4024 ----------------------------------------------------------------------
4025 --
4026 -- RUN mode - normal process execution
4027 --
4028 IF (p_funcmode = 'RUN') THEN
4029
4030 -------------------------------------------------------
4031 l_debug_info := 'Set Approval Result Item Attribute';
4032 -------------------------------------------------------
4033 WF_ENGINE.SetItemAttrText(p_itemtype,
4034 p_itemkey,
4035 'APPROVAL_RESULT',
4036 'COMPLETE');
4037 UPDATE ARI_REG_VERIFICATIONS SET STATUS = 'COMPLETE', LAST_UPDATE_DATE = sysdate WHERE WF_ITEM_KEY = p_itemkey;
4038 COMMIT;
4039 p_result := 'COMPLETE';
4040 RETURN;
4041 END IF;
4042
4043
4044 --
4045 -- CANCEL mode - activity 'compensation'
4046 --
4047 -- This is in the event that the activity must be undone,
4048 -- for example when a process is reset to an earlier point
4049 -- due to a loop back.
4050 --
4051 IF (p_funcmode = 'CANCEL') THEN
4052
4053 -- your cancel code goes here
4054 null;
4055
4056 -- no result needed
4057 p_result := 'COMPLETE';
4058 RETURN;
4059 END IF;
4060
4061 --
4062 -- Other execution modes may be created in the future. Your
4063 -- activity will indicate that it does not implement a mode
4064 -- by returning null
4065 --
4066 p_result := '';
4067 RETURN;
4068
4069 EXCEPTION
4070 WHEN OTHERS THEN
4071 IF (SQLCODE = -20001) THEN
4072 l_error_msg := FND_MESSAGE.Get;
4073 ELSE
4074 l_error_msg := l_debug_info || 'error: ' || SQLERRM;
4075 END IF;
4076
4077 IF (SQLCODE <> -20001) THEN
4078 l_error_msg := FND_MESSAGE.Get;
4079 IF (PG_DEBUG = 'Y') THEN
4080 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
4081 arp_standard.debug('Debug Info: ' || l_debug_info);
4082 arp_standard.debug(l_error_msg);
4083 END IF;
4084 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
4085 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
4086 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
4087 FND_MESSAGE.SET_TOKEN('ERROR',l_error_msg);
4088 FND_MSG_PUB.ADD;
4089 InformSysAdminError(p_procedure_name => l_procedure_name,
4090 p_debug_info => l_debug_info,
4091 p_error => l_error_msg);
4092 END IF;
4093 APP_EXCEPTION.RAISE_EXCEPTION;
4094 END UpdateApprovalStatus;
4095
4096
4097 /*=======================================================================
4098 | PROCEDURE UpdateRejectStatus
4099 |
4100 | DESCRIPTION This is used to set the status of Add.Req.Access registration
4101 | record to REJECTED when the notification has been rejected.
4102 | created as a part of Bug 13869981
4103 |
4104 | PARAMETERS
4105 |
4106 * ======================================================================*/
4107 ---------------------------------------------------------------------------
4108 PROCEDURE UpdateRejectStatus(p_itemtype IN VARCHAR2,
4109 p_itemkey IN VARCHAR2,
4110 p_actid IN NUMBER,
4111 p_funcmode IN VARCHAR2,
4112 p_result IN OUT NOCOPY VARCHAR2) IS
4113 -----------------------------------------------------------------------------
4114 l_procedure_name VARCHAR2(300) := 'UpdateRejectStatus';
4115 l_error_msg VARCHAR2(2000);
4116 l_debug_info VARCHAR2(4000);
4117 BEGIN
4118 ---------------------------------------------------------------------
4119 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4120 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside UpdateRejectStatus'||p_itemkey);
4121 end if;
4122 ----------------------------------------------------------------------
4123 --
4124 -- RUN mode - normal process execution
4125 --
4126 IF (p_funcmode = 'RUN') THEN
4127
4128 -------------------------------------------------------
4129 l_debug_info := 'Set Approval Result Item Attribute';
4130 -------------------------------------------------------
4131 WF_ENGINE.SetItemAttrText(p_itemtype,
4132 p_itemkey,
4133 'APPROVAL_RESULT',
4134 'REJECTED');
4135 UPDATE ARI_REG_VERIFICATIONS SET STATUS = 'REJECTED', LAST_UPDATE_DATE = sysdate WHERE WF_ITEM_KEY = p_itemkey;
4136 COMMIT;
4137 p_result := 'COMPLETE';
4138 RETURN;
4139 END IF;
4140
4141
4142 --
4143 -- CANCEL mode - activity 'compensation'
4144 --
4145 -- This is in the event that the activity must be undone,
4146 -- for example when a process is reset to an earlier point
4147 -- due to a loop back.
4148 --
4149 IF (p_funcmode = 'CANCEL') THEN
4150
4151 -- your cancel code goes here
4152 null;
4153
4154 -- no result needed
4155 p_result := 'COMPLETE';
4156 RETURN;
4157 END IF;
4158
4159 --
4160 -- Other execution modes may be created in the future. Your
4161 -- activity will indicate that it does not implement a mode
4162 -- by returning null
4163 --
4164 p_result := '';
4165 RETURN;
4166
4167 EXCEPTION
4168 WHEN OTHERS THEN
4169 IF (SQLCODE = -20001) THEN
4170 l_error_msg := FND_MESSAGE.Get;
4171 ELSE
4172 l_error_msg := l_debug_info || 'error: ' || SQLERRM;
4173 END IF;
4174
4175 IF (SQLCODE <> -20001) THEN
4176 l_error_msg := FND_MESSAGE.Get;
4177 IF (PG_DEBUG = 'Y') THEN
4178 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
4179 arp_standard.debug('Debug Info: ' || l_debug_info);
4180 arp_standard.debug(l_error_msg);
4181 END IF;
4182 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
4183 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
4184 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
4185 FND_MESSAGE.SET_TOKEN('ERROR',l_error_msg);
4186 FND_MSG_PUB.ADD;
4187 InformSysAdminError(p_procedure_name => l_procedure_name,
4188 p_debug_info => l_debug_info,
4189 p_error => l_error_msg);
4190 END IF;
4191 APP_EXCEPTION.RAISE_EXCEPTION;
4192 END UpdateRejectStatus;
4193
4194 END ARI_SELF_REGISTRATION_PKG;