[Home] [Help]
PACKAGE BODY: APPS.ASO_PUBLISH_MISC_INT
Source
4
1 PACKAGE BODY aso_publish_misc_int as
2 /* $Header: asoipmsb.pls 120.9.12010000.3 2009/10/19 06:55:27 akushwah ship $ */
3
5 GET_MESSAGE_ERROR EXCEPTION;
6
7 Cursor c_hz_parties(p_party_id NUMBER) IS
8 SELECT Party_Name,Person_First_Name,Person_Middle_Name,Person_Last_name,party_type,Person_title
9 FROM hz_parties
10 WHERE party_id = p_party_id;
11
12 Cursor c_quote_header (p_quote_id NUMBER) IS
13 SELECT org_id,party_id, quote_name, quote_number, quote_version, quote_password,
14 cust_account_id,invoice_to_party_id, invoice_to_party_site_id, quote_header_id,
15 ordered_date, order_id, total_list_price,total_shipping_charge,total_tax,
16 total_quote_price,invoice_to_cust_account_id,total_adjusted_amount,
17 currency_code,resource_id,quote_status_id,minisite_id
18 FROM aso_quote_headers_all
19 WHERE quote_header_id = p_quote_id;
20 g_quote_header_rec c_quote_header%ROWTYPE;
21
22
23 Cursor c_quote_statuses( p_status_code VARCHAR2) IS
24 SELECT quote_status_id
25 FROM aso_quote_statuses_b
26 WHERE status_code = p_status_code;
27 c_quote_statuses_rec c_quote_statuses%ROWTYPE;
28
29
30 Cursor c_istore_lookup(p_message_name VARCHAR2) IS
31 select LOOKUP_CODE
32 from fnd_lookups
33 where lookup_type = 'IBE_WF_NOTIFICATION'
34 and LOOKUP_CODE = p_message_name
35 and ENABLED_FLAG = 'Y';
36 c_wf_notifications c_istore_lookup%ROWTYPE;
37
38
39 Cursor c_msite_resp_name(p_msite_id VARCHAR2, p_resp_id VARCHAR2) is
40 select display_name
41 from ibe_msite_resps_vl
42 where msite_id = to_number(p_msite_id)
43 and responsibility_id = to_number(p_resp_id);
44 c_store_name c_msite_resp_name%rowtype;
45
46
47 -- API Name: NotifyUserForRegistration
48 -- Type : Public
49 -- Pre-Req : Workflow template for the notification should be there in the DB
50
51
52 PROCEDURE NotifyUserForRegistration(
53 p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
55 p_quote_id IN NUMBER,
56 p_Send_Name IN Varchar2,
57 p_Store_Name IN Varchar2,
58 p_Store_Website IN Varchar2,
59 p_FND_Password IN Varchar2,
60 p_email_address IN varchar2 := null,
61 p_email_language IN varchar2 := null,
62 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
63 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
64 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
65 ) IS
66
67
68 g_ItemType Varchar2(10) := 'ASOQOTWF';
69 g_processName Varchar2(30) := 'PROCESSMAP';
70
71
72 l_adhoc_user WF_USERS.NAME%TYPE;
73 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
74 l_item_owner WF_USERS.NAME%TYPE := 'SYSADMIN';
75
76 l_partyId Number;
77
78 l_notifEnabled Varchar2(3) := 'Y';
79 l_notifName Varchar2(30) := 'ASOQOTPUBLISHREG';
80 l_OrgId Number := null;
81 l_UserType Varchar2(30) := 'ALL';
82 l_msite_id Number := null;
83
84 l_messageName WF_MESSAGES.NAME%TYPE;
85 l_msgEnabled VARCHAR2(3) :='Y';
86
87 l_resource_id number;
88 l_first_name JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE;
89 l_last_name JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE;
90 l_email_id JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE;
91 l_full_name Varchar2(360);
92
93 l_quoteSourceSatusId Number := null;
94 l_quoteDestStatusId Number := null;
95
96
97 -- bug: 4650509 -- select person_party_id instead of customer_id
98 Cursor C_login_User(c_login_name VARCHAR2) IS
99 Select USR.person_party_id Name
100 From FND_USER USR
101 Where USR.EMPLOYEE_ID is null
102 and user_name = c_login_name;
103
104 Cursor C_Name_form_ResourceId(c_resource_id number)IS
105 Select SOURCE_FIRST_NAME, SOURCE_LAST_NAME, SOURCE_EMAIL
106 From JTF_RS_RESOURCE_EXTNS
107 Where RESOURCE_ID = c_resource_id;
108
109 l_debug VARCHAR2(1);
110
111 -- bug 5221658
112 l_display_name varchar2(360);
113 l_description varchar2(1000);
114 l_start date;
115 l_end date;
116 l_fax varchar2(200);
117 l_orig_system varchar2(30);
118 l_orig_system_id number;
119 l_partition number;
120 l_last_updated_by number;
121 l_last_update_date date;
122 l_last_update_login number;
123 wf_parameters wf_parameter_list_t;
124
125 Cursor c_get_role_details(c_role_name varchar2) is
126 Select display_name, description, start_date, expiration_date,
127 fax,orig_system, orig_system_id, partition_id ,last_updated_by,
131
128 last_update_date, last_update_login
129 From wf_local_roles
130 Where name= c_role_name;
132 BEGIN
133 l_debug := ASO_QUOTE_UTIL_PVT.is_debug_enabled;
134 IF l_debug = 'Y' THEN
135 ASO_QUOTE_UTIL_PVT.Enable_Debug_Pvt;
136 END IF;
137
138 IF l_debug = 'Y' THEN
139 ASO_QUOTE_UTIL_PVT.Debug('BEGIN');
140 END IF;
141
142 x_return_status := FND_API.g_ret_sts_success;
143
144 -- check istore lookup to find message name for quoting
145 FOR c_wf_notifications In c_istore_lookup(l_notifName) LOOP
146 g_ItemType := 'IBEALERT';
147 END LOOP;
148
149 IF l_debug = 'Y' THEN
150 ASO_QUOTE_UTIL_PVT.Debug('Check if this notification is enabled.');
151 END IF;
152
153 l_notifEnabled := IBE_WF_NOTIF_SETUP_PVT.Check_Notif_Enabled(l_notifName);
154 IF l_debug = 'Y' THEN
155 ASO_QUOTE_UTIL_PVT.Debug('Notification Name: '||l_notifName||' Enabled: '||l_notifEnabled);
156 END IF;
157
158 If l_notifEnabled = 'Y' Then
159 l_adhoc_user := upper(p_send_name);
160
161 FOR c_rec IN c_login_user(l_adhoc_user) LOOP
162 l_adhoc_user := 'HZ_PARTY:'||c_rec.Name;
163 l_partyId := c_rec.Name;
164 END LOOP;
165
166 /* l_orgId := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)); */ --Commented Code Yogeshwar (MOAC)
167
168 --New Code Start Yogeshwar (MOAC)
169 FOR c_quote_rec In c_quote_header(p_quote_id) LOOP
170 l_resource_id := c_quote_rec.resource_id;
171 l_msite_id := c_quote_rec.minisite_id;
172 l_orgid := c_quote_rec.org_id;
173 END LOOP ;
174 -- New Code End (MOAC)
175
176 getUserType(l_partyId,l_UserType);
177
178 IF l_debug = 'Y' THEN
179 ASO_QUOTE_UTIL_PVT.Debug('Get Message - Org_id: '||to_char(l_orgId) ||' User Type: '||l_userType);
180 END IF;
181
182 FOR c_quote_rec In c_quote_header(p_quote_id) LOOP
183 l_resource_id := c_quote_rec.resource_id;
184 l_msite_id := c_quote_rec.minisite_id;
185 END LOOP;
186
187 FOR c_jtf_rs_rec In C_Name_form_ResourceId(l_resource_id) LOOP
188 l_first_name := c_jtf_rs_rec.source_first_name;
189 l_last_name := c_jtf_rs_rec.source_last_name;
190 l_email_id := c_jtf_rs_rec.source_email;
191 END LOOP;
192 l_full_name := l_last_name || ', ' || l_first_name;
193
194
195 IF l_debug = 'Y' THEN
196 ASO_QUOTE_UTIL_PVT.Debug('Calling Mapping api...'|| 'Org_id :' || l_OrgId);
197 END IF;
198
199 if( g_ItemType = 'IBEALERT') Then
200
201 IBE_WF_MSG_MAPPING_PVT.Retrieve_Msg_Mapping(
202 p_org_id => l_OrgId,
203 p_msite_id => l_msite_id,
204 p_user_type => l_UserType,
205 p_notif_name => l_notifName,
206 x_enabled_flag => l_msgEnabled,
207 x_wf_message_name => l_MessageName,
208 x_return_status => x_return_status,
209 x_msg_data => x_msg_data,
210 x_msg_count => x_msg_count);
211
212 ELSE
213 l_MessageName := l_notifName;
214
215 END IF;
216
217
218 IF l_debug = 'Y' THEN
219 ASO_QUOTE_UTIL_PVT.Debug('Message Name: '||l_MessageName||' Enabled: '||l_msgEnabled);
220 END IF;
221
222 if( x_return_status= FND_API.G_RET_STS_ERROR ) then
223 raise FND_API.G_EXC_ERROR;
224 elsif( x_return_status= FND_API.G_RET_STS_UNEXP_ERROR ) then
225 raise FND_API.G_EXC_UNEXPECTED_ERROR;
226 end if;
227
228 -- bug 5221658, setting the notification preference and e-mail so the
229 -- the new fnd user created gets the notification by e-mail
230
231 open c_get_role_details(l_adhoc_user);
232 fetch c_get_role_details into l_display_name, l_description ,
233 l_start, l_end,l_fax,
234 l_orig_system, l_orig_system_id, l_partition, l_last_updated_by,
235 l_last_update_date, l_last_update_login;
236 close c_get_role_details;
237
238 IF l_debug = 'Y' THEN
239 ASO_QUOTE_UTIL_PVT.Debug('Printing the details for the role: '||l_adhoc_user);
240 ASO_QUOTE_UTIL_PVT.Debug('l_display_name : '||l_display_name);
241 ASO_QUOTE_UTIL_PVT.Debug('l_description : '||l_description);
242 ASO_QUOTE_UTIL_PVT.Debug('l_start : '||l_start);
243 ASO_QUOTE_UTIL_PVT.Debug('l_end : '||l_end);
244 ASO_QUOTE_UTIL_PVT.Debug('l_fax : '||l_fax);
245 ASO_QUOTE_UTIL_PVT.Debug('l_orig_system : '||l_orig_system);
249 ASO_QUOTE_UTIL_PVT.Debug('l_last_update_date : '||l_last_update_date);
246 ASO_QUOTE_UTIL_PVT.Debug('l_orig_system_id : '||l_orig_system_id);
247 ASO_QUOTE_UTIL_PVT.Debug('l_partition : '||l_partition);
248 ASO_QUOTE_UTIL_PVT.Debug('l_last_updated_by : '||l_last_updated_by);
250 ASO_QUOTE_UTIL_PVT.Debug('l_last_update_login : '||l_last_update_login);
251 ASO_QUOTE_UTIL_PVT.Debug('p_email_address : '||p_email_address);
252 END IF;
253
254
255 wf_parameters := NULL;
256 wf_event.AddParameterToList('USER_NAME',
257 l_adhoc_user, wf_parameters);
258 wf_event.AddParameterToList('DISPLAYNAME',
259 l_display_name, wf_parameters);
260 wf_event.AddParameterToList('DESCRIPTION',
261 l_description, wf_parameters);
262 wf_event.AddParameterToList('RAISEERRORS',
263 'TRUE', wf_parameters);
264 wf_event.AddParameterToList('ORCLWORKFLOWNOTIFICATIONPREF',
265 'MAILHTML', wf_parameters);
266 wf_event.AddParameterToList('MAIL',
267 nvl(p_email_address,l_email_id), wf_parameters);
268 wf_event.AddParameterToList('FACSIMILETELEPHONENUMBER',
269 l_fax, wf_parameters);
270 wf_event.AddParameterToList('LAST_UPDATED_BY',l_last_updated_by,wf_parameters);
271 wf_event.AddParameterToList('LAST_UPDATE_DATE',
272 l_last_update_date,wf_parameters);
273 wf_event.AddParameterToList('LAST_UPDATE_LOGIN',l_last_update_login
274 ,wf_parameters);
275
276 /* Commented as per code change for Bug 8711723
277 IF l_debug = 'Y' THEN
278 ASO_QUOTE_UTIL_PVT.Debug('Before Calling propagate_role API');
279 END IF;
280
281
282 wf_local_synch.propagate_role(p_orig_system =>l_orig_system,
283 p_orig_system_id =>l_orig_system_id,
284 p_attributes => wf_parameters,
285 p_start_date =>l_start,
286 p_expiration_date =>l_end);
287 */
288
289 /*** Code change start for Bug 8711723 ***/
290
291 IF l_debug = 'Y' THEN
292 ASO_QUOTE_UTIL_PVT.Debug('Bug : 8711723 , Before Calling propagate_user API 1');
293 END IF;
294
295 wf_local_synch.propagate_user(p_orig_system =>l_orig_system,
296 p_orig_system_id =>l_orig_system_id,
297 p_attributes => wf_parameters,
298 p_start_date =>l_start,
299 p_expiration_date =>l_end);
300
301 /*** Code change end for Bug 8711723 ***/
302
303 IF l_debug = 'Y' THEN
304 ASO_QUOTE_UTIL_PVT.Debug('After Calling propagate_role API');
305 END IF;
306
307 -- end bug 5221658
308
309 If l_msgEnabled = 'Y' Then
310
311 l_item_key := l_notifName||'-'||to_char(sysdate,'MMDDYYHH24MISS')||'-'||p_send_name;
312
313 IF l_debug = 'Y' THEN
314 ASO_QUOTE_UTIL_PVT.Debug('Create and Start Process with Item Key: '||l_item_key);
315 END IF;
316
317 wf_engine.CreateProcess(
318 itemtype => g_ItemType,
319 itemkey => l_item_key,
320 process => g_processName);
321
322 wf_engine.SetItemUserKey(
323 itemtype => g_ItemType,
324 itemkey => l_item_key,
325 userkey => l_item_key);
326
327 wf_engine.SetItemAttrText(
328 itemtype => g_ItemType,
329 itemkey => l_item_key,
330 aname => 'MESSAGE',
331 avalue => l_MessageName);
332
333 wf_engine.SetItemAttrText(
334 itemtype => g_ItemType,
335 itemkey => l_item_key,
336 aname => 'SENDTO',
337 avalue => l_adhoc_user);
338
339 wf_engine.SetItemAttrText(
343 avalue => l_first_name);
340 itemtype => g_ItemType,
341 itemkey => l_item_key,
342 aname => 'SALESREP_F_NAME',
344
345 wf_engine.SetItemAttrText(
346 itemtype => g_ItemType,
347 itemkey => l_item_key,
348 aname => 'SALESREP_L_NAME',
349 avalue => l_last_name);
350
351 wf_engine.SetItemAttrText(
352 itemtype => g_ItemType,
353 itemkey => l_item_key,
354 aname => 'SALESREP_EMAIL_ID',
355 avalue => l_email_id );
356
357 wf_engine.SetItemAttrText(
358 itemtype => g_ItemType,
359 itemkey => l_item_key,
360 aname => 'QUOTEID',
361 avalue => p_quote_id);
362
363 wf_engine.SetItemAttrText(
364 itemtype => g_ItemType,
365 itemkey => l_item_key,
366 aname => 'USERID',
367 avalue => p_Send_Name);
368
369 wf_engine.SetItemAttrText(
370 itemtype => g_ItemType,
371 itemkey => l_item_key,
372 aname => 'PASSWORD',
373 avalue => p_FND_Password);
374
375 wf_engine.SetItemAttrText(
376 itemtype => g_ItemType,
377 itemkey => l_item_key,
378 aname => 'MSITE_RESP_ID',
379 avalue => p_Store_Name);
380
381 wf_engine.SetItemAttrText(
382 itemtype => g_ItemType,
383 itemkey => l_item_key,
384 aname => 'SPECIALITY_STORE_WEBSITE',
385 avalue => p_Store_Website);
386
387 wf_engine.SetItemOwner(
388 itemtype => g_ItemType,
389 itemkey => l_item_key,
390 owner => l_item_owner);
391
392 wf_engine.StartProcess(
393 itemtype => g_ItemType,
394 itemkey => l_item_key);
395
396 IF l_debug = 'Y' THEN
397 ASO_QUOTE_UTIL_PVT.Debug('Process Started');
398 END IF;
399
400 End If;
401 End If;
402
403 IF l_debug = 'Y' THEN
404 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
405 END IF;
406
407 Exception
408
409 When OTHERS Then
410 x_return_status := FND_API.g_ret_sts_error;
411 x_msg_count := 0;
412
413 wf_core.context('ASO_IBE_INT', 'NotifyUserForRegistration', p_send_name);
414 raise;
415
416 IF l_debug = 'Y' THEN
417 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
418 END IF;
419
420 END NotifyUserForRegistration;
421
422
423
424 -- API Name: NotifyForQuotePublish
425 -- Type : Public
426 -- Pre-Req : Workflow template for the notification should be there in the DB
427
428
429 PROCEDURE NotifyForQuotePublish(
430 p_api_version IN NUMBER,
431 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
432 p_quote_id IN NUMBER,
433 p_Send_Name IN Varchar2,
434 p_Comments IN Varchar2,
435 p_Store_Name IN Varchar2,
436 p_Store_Website IN Varchar2,
437 p_url IN Varchar2,
438 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
439 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
440 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
441 ) IS
442
443
444 g_ItemType Varchar2(10) := 'ASOQOTWF';
445 g_processName Varchar2(30) := 'PROCESSMAP';
446
447
448 l_adhoc_user WF_USERS.NAME%TYPE;
449 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
450 l_item_owner WF_USERS.NAME%TYPE := 'SYSADMIN';
451
452 l_partyId Number;
453
454 l_notifEnabled Varchar2(3) := 'Y';
455 l_notifName Varchar2(30) := '';
456 l_OrgId Number := null;
457 l_UserType Varchar2(30) := 'ALL';
458 l_msite_id Number := null;
459
460 l_messageName WF_MESSAGES.NAME%TYPE;
461 l_msgEnabled VARCHAR2(3) :='Y';
462
463 l_resource_id number;
464 l_first_name JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE;
465 l_last_name JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE;
466 l_email_id JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE;
467 l_full_name Varchar2(360);
468 l_cart_name varchar2(80);
469
470 l_quoteSourceSatusId Number := null;
471 l_quoteDestStatusId Number := null;
472
473 l_sales_adhoc_user WF_USERS.NAME%TYPE;
474 l_sales_adhoc_user_display WF_USERS.DISPLAY_NAME%TYPE;
475 l_nls_language WF_LANGUAGES.NLS_LANGUAGE%TYPE := 'AMERICAN';
476
477 l_notification_preference VARCHAR(100);
478 l_role_users VARCHAR(800);
479 l_sales_adhoc_role WF_ROLES.NAME%TYPE;
480 l_sales_adhoc_role_display WF_ROLES.DISPLAY_NAME%TYPE;
481 --bug: 4650509 -- select person_party_id instead of customer_id
482 Cursor C_login_User(c_login_name VARCHAR2) IS
483 Select USR.person_party_id Name
484 From FND_USER USR
485 Where USR.EMPLOYEE_ID is null
486 and user_name = c_login_name;
487
488 Cursor C_Name_form_ResourceId(c_resource_id number)IS
489 Select SOURCE_FIRST_NAME, SOURCE_LAST_NAME, SOURCE_EMAIL
490 From JTF_RS_RESOURCE_EXTNS
491 Where RESOURCE_ID = c_resource_id;
492
493 Cursor C_nls_language IS
494 select nls_language from wf_languages
495 where code = USERENV('LANG');
496
497 l_debug VARCHAR2(1);
498
499 -- bug 5221658
500 l_display_name varchar2(360);
501 l_description varchar2(1000);
502 l_start date;
503 l_end date;
504 l_fax varchar2(200);
505 l_orig_system varchar2(30);
506 l_orig_system_id number;
507 l_partition number;
508 l_last_updated_by number;
509 l_last_update_date date;
510 l_last_update_login number;
511 wf_parameters wf_parameter_list_t;
512
513 Cursor c_get_role_details(c_role_name varchar2) is
514 Select display_name, description, start_date, expiration_date,
515 fax,orig_system, orig_system_id, partition_id ,last_updated_by,
516 last_update_date, last_update_login
517 From wf_local_roles
518 Where name= c_role_name;
519
520 BEGIN
521 l_debug := ASO_QUOTE_UTIL_PVT.is_debug_enabled;
522 IF l_debug = 'Y' THEN
523 ASO_QUOTE_UTIL_PVT.Enable_Debug_Pvt;
524 ASO_QUOTE_UTIL_PVT.Debug('BEGIN');
525 END IF;
526
527 x_return_status := FND_API.g_ret_sts_success;
528
529
530 FOR c_quote_rec In c_quote_header(p_quote_id) LOOP
531 l_quoteSourceSatusId := c_quote_rec.quote_status_id;
532 END LOOP;
533
534 FOR c_quote_statuses_rec In c_quote_statuses('ORDER SUBMITTED') LOOP
538 IF l_debug = 'Y' THEN
535 l_quoteDestStatusId := c_quote_statuses_rec.quote_status_id;
536 END LOOP;
537
539 ASO_QUOTE_UTIL_PVT.Debug('SOURCE Status done');
540 END IF;
541
542 ASO_VALIDATE_PVT.Validate_Status_Transition(
543 p_init_msg_list => FND_API.G_FALSE,
544 p_source_status_id => l_quoteSourceSatusId,
545 p_dest_status_id => l_quoteDestStatusId,
546 x_return_status => x_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data);
549
550 IF l_debug = 'Y' THEN
551 ASO_QUOTE_UTIL_PVT.Debug('Calling Validate_Status_Transition done ' || x_return_status );
552 END IF;
553
554 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
555 l_notifName := 'ASOQOTPUBLISHEXEC';
556 ELSE
557 l_notifName := 'ASOQOTPUBLISHUNEXEC';
558 FND_MSG_PUB.initialize;
559 END IF;
560
561 -- reset return status code
562 x_return_status := FND_API.g_ret_sts_success;
563
564 -- check istore lookup to find message name for quoting
565 FOR c_wf_notifications In c_istore_lookup(l_notifName) LOOP
566 g_ItemType := 'IBEALERT';
567 END LOOP;
568
569
570 IF l_debug = 'Y' THEN
571 ASO_QUOTE_UTIL_PVT.Debug('Check if this notification is enabled.');
572 END IF;
573
574 l_notifEnabled := IBE_WF_NOTIF_SETUP_PVT.Check_Notif_Enabled(l_notifName);
575
576 IF l_debug = 'Y' THEN
577 ASO_QUOTE_UTIL_PVT.Debug('Notification Name: '||l_notifName||' Enabled: '||l_notifEnabled);
578 END IF;
579
580 If l_notifEnabled = 'Y' Then
581 l_adhoc_user := upper(p_send_name);
582
583 FOR c_rec IN c_login_user(l_adhoc_user) LOOP
584 l_adhoc_user := 'HZ_PARTY:'||c_rec.Name;
585 l_partyId := c_rec.Name;
586 END LOOP;
587
588 /* l_orgId := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)); */ --Commented Code Yogeshwar (MOAC)
589 --New Code Start yogeshwar (MOAC)
590 FOR c_quote_rec In c_quote_header(p_quote_id) LOOP
591 l_resource_id := c_quote_rec.resource_id;
592 l_msite_id := c_quote_rec.minisite_id;
593 l_orgid := c_quote_rec.org_id;
594 END LOOP;
595 --New Code End Yogeshwar (MOAC)
596
597 getUserType(l_partyId,l_UserType);
598
599 IF l_debug = 'Y' THEN
600 ASO_QUOTE_UTIL_PVT.Debug('Get Message - Org_id: '||to_char(l_orgId) ||' User Type: '||l_userType);
601 END IF;
602
603 FOR c_quote_rec In c_quote_header(p_quote_id) LOOP
604 l_resource_id := c_quote_rec.resource_id;
605 l_msite_id := c_quote_rec.minisite_id;
606 END LOOP;
607
608 FOR c_jtf_rs_rec In C_Name_form_ResourceId(l_resource_id) LOOP
609 l_first_name := c_jtf_rs_rec.source_first_name;
610 l_last_name := c_jtf_rs_rec.source_last_name;
611 l_email_id := c_jtf_rs_rec.source_email;
612 END LOOP;
613 l_full_name := l_last_name || ', ' || l_first_name;
614
615 FOR c_quote_rec In c_quote_header(p_quote_id) LOOP
616 l_cart_name := c_quote_rec.quote_name;
617 END LOOP;
618
619 IF l_debug = 'Y' THEN
620 ASO_QUOTE_UTIL_PVT.Debug('Calling Mapping api...'|| 'Org_id :' || l_OrgId);
621 END IF;
622
623
624 -- get env language
625 for c_language_rec In C_nls_language LOOP
626 l_nls_language := c_language_rec.nls_language;
627 end loop;
628
629
630 if( g_ItemType = 'IBEALERT') Then
631
632 IBE_WF_MSG_MAPPING_PVT.Retrieve_Msg_Mapping(
633 p_org_id => l_OrgId,
634 p_msite_id => l_msite_id,
635 p_user_type => l_UserType,
636 p_notif_name => l_notifName,
637 x_enabled_flag => l_msgEnabled,
638 x_wf_message_name => l_MessageName,
639 x_return_status => x_return_status,
640 x_msg_data => x_msg_data,
641 x_msg_count => x_msg_count);
642
643 ELSE
644 l_MessageName := l_notifName;
645
646 END IF;
647
648
649 IF l_debug = 'Y' THEN
650 ASO_QUOTE_UTIL_PVT.Debug('Message Name: '||l_MessageName||' Enabled: '||l_msgEnabled);
651 END IF;
652
653 if( x_return_status= FND_API.G_RET_STS_ERROR ) then
654 raise FND_API.G_EXC_ERROR;
655 elsif( x_return_status= FND_API.G_RET_STS_UNEXP_ERROR ) then
656 raise FND_API.G_EXC_UNEXPECTED_ERROR;
657 end if;
658
659 If l_msgEnabled = 'Y' Then
660 -- bug 2107290 # code Start
661 IF l_debug = 'Y' THEN
662 ASO_QUOTE_UTIL_PVT.Debug('Create workflow adhoc user for Sales Rep');
663 END IF;
664 l_sales_adhoc_user := 'QOTU'||to_char(sysdate,'MMDDYYHH24MISS')||'P'||p_quote_id ;
665 l_sales_adhoc_user_display := 'QOTU'||to_char(sysdate,'MMDDYYHH24MISS')||'P'||p_quote_id;
666
667 l_notification_preference := NVL( FND_PROFILE.VALUE_SPECIFIC('IBE_DEFAULT_USER_EMAIL_FORMAT',null,null,671), 'MAILTEXT');
668 IF l_debug = 'Y' THEN
669 ASO_QUOTE_UTIL_PVT.Debug(' l_notification_preference '||l_notification_preference);
670 ASO_QUOTE_UTIL_PVT.Debug(' l_sales_adhoc_user '||l_sales_adhoc_user);
671 END IF;
672 -- move the CreateAdHocUser Api to a local block -- skm
673 begin
674
675 wf_directory.CreateAdHocUser(
676 name => l_sales_adhoc_user,
677 display_name => l_sales_adhoc_user_display,
678 notification_preference => l_notification_preference,
679 email_address => l_email_id,
680 expiration_date => sysdate + 1,
681 language => l_nls_language);
682
683 IF l_debug = 'Y' THEN
684 ASO_QUOTE_UTIL_PVT.Debug('Successful creation of Ad Hoc User');
685 END IF;
686
687 exception
688
689 when others then
690
691 IF l_debug = 'Y' THEN
692 ASO_QUOTE_UTIL_PVT.Debug ('Create Ad Hoc User failed');
693 END IF;
694
695 end;
696 IF l_debug = 'Y' THEN
697 ASO_QUOTE_UTIL_PVT.Debug('Create workflow role user for Sales Rep');
698 END IF;
699
700 l_role_users := l_adhoc_user ||','||l_sales_adhoc_user;
701 l_sales_adhoc_role := 'QOTR'||to_char(sysdate,'MMDDYYHH24MISS')||'P'||p_quote_id;
702 l_sales_adhoc_role_display := 'QOTR'||to_char(sysdate,'MMDDYYHH24MISS')||'P'||p_quote_id;
703
704 IF l_debug = 'Y' THEN
705 ASO_QUOTE_UTIL_PVT.Debug(' l_sales_adhoc_role '||l_sales_adhoc_role);
706 ASO_QUOTE_UTIL_PVT.Debug(' l_role_users '||l_role_users);
707 END IF;
708 -- move the CreateAdHocRole api to a local block --skm
709 begin
710
711 wf_directory.CreateAdHocRole(
712 role_name => l_sales_adhoc_role,
713 role_display_name => l_sales_adhoc_role_display,
714 language => l_nls_language,
715 notification_preference => l_notification_preference,
716 role_users => l_role_users,
717 expiration_date => sysdate + 1);
718
719 IF l_debug = 'Y' THEN
720 ASO_QUOTE_UTIL_PVT.Debug('Successful creation of AdHoc Role');
721 END IF;
722 exception
723
724 when others then
725
726 IF l_debug = 'Y' THEN
727 ASO_QUOTE_UTIL_PVT.Debug('CreateAdHocRole api failed');
728 END IF;
729
730 end;
731
732 -- bug 2107290 # code End
733
734
735 l_item_key := l_notifName||'-'||to_char(sysdate,'MMDDYYHH24MISS')||'-'||p_send_name;
736
737 IF l_debug = 'Y' THEN
738 ASO_QUOTE_UTIL_PVT.Debug('Create and Start Process with Item Key: '||l_item_key);
739 END IF;
740
741
742 wf_engine.CreateProcess(
743 itemtype => g_ItemType,
744 itemkey => l_item_key,
745 process => g_processName);
746
747 wf_engine.SetItemUserKey(
748 itemtype => g_ItemType,
749 itemkey => l_item_key,
750 userkey => l_item_key);
751
752 wf_engine.SetItemAttrText(
753 itemtype => g_ItemType,
754 itemkey => l_item_key,
755 aname => 'MESSAGE',
756 avalue => l_MessageName);
757
758 wf_engine.SetItemAttrText(
759 itemtype => g_ItemType,
760 itemkey => l_item_key,
761 aname => 'SENDTO',
762 avalue => l_sales_adhoc_role);
763
764 wf_engine.SetItemAttrText(
765 itemtype => g_ItemType,
766 itemkey => l_item_key,
767 aname => 'SALESREP_F_NAME',
768 avalue => l_first_name);
769
770 wf_engine.SetItemAttrText(
771 itemtype => g_ItemType,
772 itemkey => l_item_key,
773 aname => 'SALESREP_L_NAME',
774 avalue => l_last_name);
775
776 wf_engine.SetItemAttrText(
777 itemtype => g_ItemType,
778 itemkey => l_item_key,
779 aname => 'SALESREP_EMAIL_ID',
780 avalue => l_email_id );
781
782 wf_engine.SetItemAttrText(
783 itemtype => g_ItemType,
784 itemkey => l_item_key,
785 aname => 'QUOTEID',
786 avalue => p_quote_id);
787
788 wf_engine.SetItemAttrText(
789 itemtype => g_ItemType,
790 itemkey => l_item_key,
791 aname => 'QUOTENAME',
792 avalue => l_cart_name);
793
794 wf_engine.SetItemAttrText(
795 itemtype => g_ItemType,
796 itemkey => l_item_key,
797 aname => 'COMMENTS',
798 avalue => p_Comments);
799
800 wf_engine.SetItemAttrText(
801 itemtype => g_ItemType,
802 itemkey => l_item_key,
803 aname => 'MSITE_RESP_ID',
804 avalue => p_Store_Name);
805
809 aname => 'SPECIALITY_STORE_WEBSITE',
806 wf_engine.SetItemAttrText(
807 itemtype => g_ItemType,
808 itemkey => l_item_key,
810 avalue => p_Store_Website);
811
812 wf_engine.SetItemAttrText(
813 itemtype => g_ItemType,
814 itemkey => l_item_key,
815 aname => 'URL',
816 avalue => p_url);
817
818 wf_engine.SetItemOwner(
819 itemtype => g_ItemType,
820 itemkey => l_item_key,
821 owner => l_item_owner);
822
823 wf_engine.StartProcess(
824 itemtype => g_ItemType,
825 itemkey => l_item_key);
826
827 IF l_debug = 'Y' THEN
828 ASO_QUOTE_UTIL_PVT.Debug('Process Started');
829 END IF;
830
831 End If;
832 End If;
833
834 -- bug 5221658, setting the notification preference and e-mail so the
835 -- the new fnd user created gets the notification by e-mail
836
837 open c_get_role_details(l_adhoc_user);
838 fetch c_get_role_details into l_display_name, l_description ,
839 l_start, l_end,l_fax,
840 l_orig_system, l_orig_system_id, l_partition, l_last_updated_by,
841 l_last_update_date, l_last_update_login;
842 close c_get_role_details;
843
844 IF l_debug = 'Y' THEN
845 ASO_QUOTE_UTIL_PVT.Debug('Printing the details for the role: '||l_adhoc_user);
846 ASO_QUOTE_UTIL_PVT.Debug('l_display_name : '||l_display_name);
847 ASO_QUOTE_UTIL_PVT.Debug('l_description : '||l_description);
848 ASO_QUOTE_UTIL_PVT.Debug('l_start : '||l_start);
849 ASO_QUOTE_UTIL_PVT.Debug('l_end : '||l_end);
850 ASO_QUOTE_UTIL_PVT.Debug('l_fax : '||l_fax);
851 ASO_QUOTE_UTIL_PVT.Debug('l_orig_system : '||l_orig_system);
852 ASO_QUOTE_UTIL_PVT.Debug('l_orig_system_id : '||l_orig_system_id);
853 ASO_QUOTE_UTIL_PVT.Debug('l_partition : '||l_partition);
854 ASO_QUOTE_UTIL_PVT.Debug('l_last_updated_by : '||l_last_updated_by);
855 ASO_QUOTE_UTIL_PVT.Debug('l_last_update_date : '||l_last_update_date);
856 ASO_QUOTE_UTIL_PVT.Debug('l_last_update_login : '||l_last_update_login);
857 END IF;
858
859
860 wf_parameters := NULL;
861 wf_event.AddParameterToList('USER_NAME',
862 l_adhoc_user, wf_parameters);
863 wf_event.AddParameterToList('DISPLAYNAME',
864 l_display_name, wf_parameters);
865 wf_event.AddParameterToList('DESCRIPTION',
866 l_description, wf_parameters);
867 wf_event.AddParameterToList('RAISEERRORS',
868 'TRUE', wf_parameters);
869 wf_event.AddParameterToList('ORCLWORKFLOWNOTIFICATIONPREF',
870 'MAILHTML', wf_parameters);
871 wf_event.AddParameterToList('FACSIMILETELEPHONENUMBER',
872 l_fax, wf_parameters);
873 wf_event.AddParameterToList('LAST_UPDATED_BY',l_last_updated_by,wf_parameters);
874 wf_event.AddParameterToList('LAST_UPDATE_DATE',
875 l_last_update_date,wf_parameters);
876 wf_event.AddParameterToList('LAST_UPDATE_LOGIN',l_last_update_login
877 ,wf_parameters);
878
879 /* Commented as per code change for Bug 8711723
880 IF l_debug = 'Y' THEN
881 ASO_QUOTE_UTIL_PVT.Debug('Before Calling propagate_role API');
882 END IF;
883
884 wf_local_synch.propagate_role(p_orig_system =>l_orig_system,
885 p_orig_system_id =>l_orig_system_id,
886 p_attributes => wf_parameters,
887 p_start_date =>l_start,
888 p_expiration_date =>l_end);
889 */
890
891 /*** Code change start for Bug 8711723 ***/
892 IF l_debug = 'Y' THEN
893 ASO_QUOTE_UTIL_PVT.Debug('Bug : 8711723 , Before Calling propagate_user API 2');
894 END IF;
895
896 wf_local_synch.propagate_user(p_orig_system =>l_orig_system,
897 p_orig_system_id =>l_orig_system_id,
898 p_attributes => wf_parameters,
899 p_start_date =>l_start,
900 p_expiration_date =>l_end);
901 /*** Code change end for Bug 8711723 ***/
902
903 IF l_debug = 'Y' THEN
904 ASO_QUOTE_UTIL_PVT.Debug('After Calling propagate_role API');
905 END IF;
906
907 -- end bug 5221658
908
909
910
911
912
913 IF l_debug = 'Y' THEN
914 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
915 END IF;
916 Exception
917
918 When OTHERS Then
919
920
921 IF l_debug = 'Y' THEN
922 ASO_QUOTE_UTIL_PVT.Debug('Error in NotifiyForQuotePublish');
923 END IF;
924
925 x_return_status := FND_API.g_ret_sts_error;
926 x_msg_count := 0;
927
928 wf_core.context('ASO_IBE_INT', 'NotifyForQuotePublish', p_send_name);
929 raise;
930
931 -- ASO_Quote_Util_Pvt.Disable_Debug_Pvt;
932 IF l_debug = 'Y' THEN
933 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
934 END IF;
935
936 END NotifyForQuotePublish;
937
938
939 -- API Name: GetFirstName
940 -- Type : Public
941 -- Pre-Req : NO
942
943 PROCEDURE GetFirstName(
944 document_id IN VARCHAR2,
945 display_type IN VARCHAR2,
946 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
947 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
948 ) IS
949
950 l_party_id number;
951 l_first_name varchar2(150);
952 l_order_id number;
953 l_debug varchar2(1);
954
955
956 Cursor c_b2b_contact(pPartyId Number) IS
957 Select p.party_id Person_Party_id,l.party_id contact_party_id,p.person_first_name,p.person_last_name,p.party_type
958 from hz_relationships l,hz_parties p
959 where l.party_id = pPartyId
960 and l.subject_id = p.party_id
961 and l.subject_type = 'PERSON'
962 and l.subject_table_name = 'HZ_PARTIES';
963
964 Begin
965
966 FOR c_quote_rec In c_quote_header(to_number(document_id)) LOOP
967 l_party_id := c_quote_rec.party_id;
968 l_order_id := c_quote_rec.order_id;
969 END LOOP;
970
971 FOR c_hz_parties_rec IN c_hz_parties(l_party_id) LOOP
972 If c_hz_parties_rec.Party_type = 'PARTY_RELATIONSHIP' Then
973 For c_b2b_contact_rec in c_b2b_contact(l_party_id) Loop
974 l_first_name := upper(rtrim(c_b2b_contact_rec.person_first_name));
975 End Loop;
976 Else
977 l_first_name := upper(rtrim(c_hz_parties_rec.person_first_name));
978 End If;
979 END LOOP;
980
981 document := l_first_name;
982 document_type := 'text/plain';
983
984 End GetFirstName;
985
986
987 -- API Name: GetLastName
988 -- Type : Public
989 -- Pre-Req : No
990
991
992 PROCEDURE GetLastName(
993 document_id IN VARCHAR2,
994 display_type IN VARCHAR2,
995 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
996 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
997 ) IS
998
999 l_party_id number;
1000 l_last_name varchar2(150);
1001 l_order_id number;
1002
1003 Cursor c_b2b_contact(pPartyId Number) IS
1004 Select p.party_id Person_Party_id,l.party_id contact_party_id,p.person_first_name,p.person_last_name,p.party_type
1005 from hz_relationships l,hz_parties p
1006 where l.party_id = pPartyId
1007 and l.subject_id = p.party_id
1008 and l.subject_type = 'PERSON'
1009 and l.subject_table_name = 'HZ_PARTIES';
1010
1011 Begin
1012
1013 FOR c_quote_rec In c_quote_header(to_number(document_id)) LOOP
1014 l_party_id := c_quote_rec.party_id;
1015 l_order_id := c_quote_rec.order_id;
1016 END LOOP;
1017
1018 FOR c_hz_parties_rec IN c_hz_parties(l_party_id) LOOP
1019 If c_hz_parties_rec.Party_type = 'PARTY_RELATIONSHIP' Then
1020 For c_b2b_contact_rec in c_b2b_contact(l_party_id) Loop
1021 l_last_name := upper(rtrim(c_b2b_contact_rec.person_last_name));
1022 End Loop;
1023 Else
1024 l_last_name := upper(rtrim(c_hz_parties_rec.person_last_name));
1025 End If;
1026 END LOOP;
1027
1028 document := l_last_name;
1029 document_type := 'text/plain';
1030
1031 End GetLastName;
1032
1033 -- API Name: GetTitle
1034 -- Type : Public
1035 -- Pre-Req : No
1036
1037 PROCEDURE GetTitle(
1038 document_id IN VARCHAR2,
1039 display_type IN VARCHAR2,
1040 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1041 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1042 ) IS
1043
1044 l_party_id number;
1045 l_order_id number;
1046 l_person_title HZ_PARTIES.PERSON_TITLE%TYPE;
1047
1048 Cursor c_b2b_contact(pPartyId Number) IS
1049 Select p.party_id Person_Party_id,l.party_id contact_party_id,p.person_first_name,p.person_last_name,p.party_type,p.person_title
1050 from hz_relationships l,hz_parties p
1051 where l.party_id = pPartyId
1052 and l.subject_id = p.party_id
1053 and l.subject_table_name = 'HZ_PARTIES'
1054 and l.subject_type = 'PERSON';
1055
1056 Begin
1057
1058 FOR c_quote_rec In c_quote_header(to_number(document_id)) LOOP
1059 l_party_id := c_quote_rec.party_id;
1060 l_order_id := c_quote_rec.order_id;
1061 END LOOP;
1062
1063 FOR c_hz_parties_rec IN c_hz_parties(l_party_id) LOOP
1064 If c_hz_parties_rec.Party_type = 'PARTY_RELATIONSHIP' Then
1065 For c_b2b_contact_rec in c_b2b_contact(l_party_id) Loop
1066 l_person_title := upper(rtrim(c_b2b_contact_rec.person_title));
1067 End Loop;
1068 Else
1069 l_person_title := upper(rtrim(c_hz_parties_rec.person_title));
1070 End If;
1071 END LOOP;
1072
1073 document := l_person_title;
1074 document_type := 'text/plain';
1075
1076 End GetTitle;
1077
1078 -- API Name: GetCartName
1079 -- Type : Public
1080 -- Pre-Req : No
1081
1082 PROCEDURE GetCartName(
1083 document_id IN VARCHAR2,
1084 display_type IN VARCHAR2,
1085 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1086 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1087 ) IS
1088
1089 l_cart_name varchar2(50);
1090
1091 Begin
1092
1093 FOR c_quote_rec In c_quote_header(to_number(document_id)) LOOP
1094 l_cart_name := c_quote_rec.quote_name;
1095 END LOOP;
1096
1097 document := l_cart_name;
1098 document_type := 'text/plain';
1099
1100 End GetCartName;
1101
1102
1103 -- API Name: getUserType
1104 -- Type : Public
1105 -- Pre-Req : No
1106
1107
1108 Procedure getUserType(pPartyId IN Varchar2,pUserType OUT NOCOPY /* file.sql.39 change */ Varchar2) IS
1109 l_PartyType Varchar2(30);
1110 l_UserType Varchar2(30) := 'B2B';
1111 BEGIN
1112
1113 FOR c_hz_parties_rec IN c_hz_parties(pPartyId) LOOP
1114 l_PartyType := rtrim(c_hz_parties_rec.party_type);
1115 END LOOP;
1116
1117 If l_PartyType = 'PERSON' Then
1118 l_userType := 'B2C';
1119 End If;
1120
1121 pUserType := l_userType;
1122
1123 END getUserType;
1124
1125
1126 -- API Name: GetStoreName
1127 -- Type : Public
1128 -- Pre-Req : No
1129
1130
1131 PROCEDURE GetStoreName(
1132 document_id IN VARCHAR2,
1133 display_type IN VARCHAR2,
1134 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1135 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1136 ) IS
1137
1138 l_store_name varchar2(50);
1139 l_msite_id varchar2(25);
1140 l_resp_id varchar2(25);
1141 l_index number;
1142
1143 Begin
1144
1145 l_index := instr(document_id, '-');
1146 l_msite_id := SUBSTR(document_id, 1, l_index-1);
1147 l_resp_id := SUBSTR(document_id,l_index+1);
1148
1149
1150 FOR c_store_name In c_msite_resp_name(l_msite_id, l_resp_id) LOOP
1151 l_store_name := c_store_name.display_name;
1152 END LOOP;
1153
1154 document := l_store_name;
1155 document_type := 'text/plain';
1156
1157 End GetStoreName;
1158
1159 /*
1160 PROCEDURE PublishQuoteLocal(
1161 p_quote_header_id IN NUMBER,
1162 p_publish_flag IN VARCHAR2,
1163 p_last_update_date IN DATE
1164 ) IS
1165
1166
1167 P_Api_Version_Number NUMBER := 1.0;
1168 P_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
1169 P_Commit VARCHAR2(1) := FND_API.G_FALSE;
1170 P_Validation_Level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1171
1172 P_Control_Rec ASO_QUOTE_PUB.Control_Rec_Type
1173 := ASO_QUOTE_PUB.G_Miss_Control_Rec;
1174
1175 P_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type
1176 := ASO_QUOTE_PUB.G_MISS_Qte_Header_Rec;
1177
1178 P_hd_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
1179 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl;
1180
1184 P_hd_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type
1181 P_hd_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type
1182 := ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL;
1183
1185 := ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL;
1186
1187 P_hd_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type
1188 := ASO_QUOTE_PUB.G_Miss_Freight_Charge_Tbl;
1189
1190 P_hd_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type
1191 := ASO_QUOTE_PUB.G_Miss_Tax_Detail_Tbl;
1192
1193 P_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
1194 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL;
1195
1196 P_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type
1197 := ASO_QUOTE_PUB.G_MISS_Sales_Credit_Tbl;
1198
1199 P_hd_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type
1200 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl;
1201
1202 P_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type
1203 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL;
1204
1205 P_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
1206 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_DTL_TBL;
1207
1208 P_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
1209 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL;
1210
1211 P_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
1212 := ASO_QUOTE_PUB.G_MISS_Line_Rltship_Tbl;
1213
1214 P_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type
1215 := ASO_QUOTE_PUB.G_Miss_Price_Adj_Tbl;
1216
1217 P_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
1218 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl;
1219
1220 P_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
1221 := ASO_QUOTE_PUB.G_Miss_Price_Adj_Rltship_Tbl;
1222
1223 P_Ln_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
1224 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl;
1225
1226 P_Ln_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type
1227 := ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL;
1228
1229 P_Ln_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type
1230 := ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL;
1231
1232 P_Ln_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type
1233 := ASO_QUOTE_PUB.G_Miss_Freight_Charge_Tbl;
1234
1235 P_Ln_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type
1236 := ASO_QUOTE_PUB.G_Miss_Tax_Detail_Tbl;
1237
1238 P_ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type
1239 := ASO_QUOTE_PUB.G_MISS_Sales_Credit_Tbl;
1240
1241 P_ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type
1242 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl;
1243
1244 X_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1245
1246 X_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1247 X_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1248 X_Hd_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1249 X_Hd_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1250 X_Hd_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1251 X_Hd_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
1252 X_Hd_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1253 X_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1254 X_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1255 X_hd_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1256 X_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1257 X_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
1258 X_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1259 X_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1260 X_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1261 X_Ln_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1262 X_Ln_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1263 X_Ln_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1264 X_Ln_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
1265 X_Ln_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1266 X_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1267 X_Ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1268
1269
1270 X_return_status VARCHAR2(1);
1271 X_msg_count NUMBER;
1272 X_msg_data VARCHAR2(300);
1273
1274
1275
1276 BEGIN
1277
1278 p_qte_header_rec.quote_header_id := p_quote_header_id;
1279 p_qte_header_rec.publish_flag := p_publish_flag;
1280 p_qte_header_rec.last_update_date := p_last_update_date;
1281
1282 ASO_QUOTE_PUB.Update_Quote(
1283 P_Api_Version_Number => p_api_version_number,
1284 P_Init_Msg_List => p_init_msg_list,
1285 P_Commit => p_commit,
1286 P_Validation_Level => p_validation_level,
1287 p_qte_header_rec => p_qte_header_rec,
1288 p_qte_line_tbl => p_qte_line_tbl,
1289 p_ln_shipment_tbl => p_ln_shipment_tbl,
1290 X_Qte_Header_Rec => x_qte_header_rec,
1291 X_Qte_Line_Tbl => x_qte_line_tbl,
1292 X_Qte_Line_Dtl_Tbl => x_qte_line_dtl_tbl,
1293 X_Hd_Price_Attributes_Tbl => x_hd_price_attributes_tbl,
1294 X_Hd_Payment_Tbl => x_hd_payment_tbl,
1295 X_Hd_Shipment_Tbl => x_hd_shipment_tbl,
1296 X_Hd_Freight_Charge_Tbl => x_hd_freight_charge_tbl,
1297 X_Hd_Tax_Detail_Tbl => x_hd_tax_detail_tbl,
1298 X_hd_Attr_Ext_Tbl => x_hd_attr_ext_tbl,
1299 X_hd_Sales_Credit_Tbl => x_hd_sales_credit_tbl,
1303 X_Price_Adjustment_Tbl => x_price_adjustment_tbl,
1300 X_hd_Quote_Party_Tbl => x_hd_quote_party_tbl,
1301 X_Line_Attr_Ext_Tbl => x_line_attr_ext_tbl,
1302 X_line_rltship_tbl => x_line_rltship_tbl,
1304 X_Price_Adj_Attr_Tbl => x_price_adj_attr_tbl,
1305 X_Price_Adj_Rltship_Tbl => x_price_adj_rltship_tbl,
1306 X_Ln_Price_Attributes_Tbl => x_ln_price_attributes_tbl,
1307 X_Ln_Payment_Tbl => x_ln_payment_tbl,
1308 X_Ln_Shipment_Tbl => x_ln_shipment_tbl,
1309 X_Ln_Freight_Charge_Tbl => x_ln_freight_charge_tbl,
1310 X_Ln_Tax_Detail_Tbl => x_ln_tax_detail_tbl,
1311 X_Ln_Sales_Credit_Tbl => x_ln_sales_credit_tbl,
1312 X_Ln_Quote_Party_Tbl => x_ln_quote_party_tbl,
1313 X_return_status => x_return_status,
1314 X_msg_count => x_msg_count,
1315 X_msg_data => x_msg_data );
1316
1317
1318 END PublishQuoteLocal;
1319
1320 */
1321
1322 procedure createStoreUser
1323 (
1324 p_api_version_number IN NUMBER,
1325 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1326 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1327 p_user_name IN VARCHAR2,
1328 p_user_password IN VARCHAR2,
1329 p_email_address IN VARCHAR2 DEFAULT NULL, /* Add for Bug 7334453 */
1330 p_email_language IN VARCHAR2,
1331 p_party_id IN NUMBER,
1332 p_party_type IN VARCHAR2,
1333 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1334 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1335 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1336 )
1337 IS
1338 l_api_name CONSTANT VARCHAR2(30) := 'create_Store_User' ;
1339 l_api_version_number CONSTANT NUMBER := 1.0;
1340 G_PKG_NAME CONSTANT VARCHAR2(30) := 'aso_publish_misc_int';
1341 l_user_id NUMBER;
1342 l_lang_rec1 hz_person_info_v2pub.PERSON_LANGUAGE_REC_TYPE;
1343 l_lang_rec2 hz_person_info_v2pub.PERSON_LANGUAGE_REC_TYPE;
1344 l_return_status VARCHAR2(1);
1345 l_msg_count NUMBER;
1346 l_msg_data VARCHAR2(240);
1347 l_date DATE;
1348 l_id NUMBER;
1349 l_object_version_number NUMBER;
1350 l_ref_id NUMBER;
1351 l_person_party_id NUMBER;
1352 l_debug VARCHAR2(1);
1353
1354 Cursor C_Lang1 (l_party_id number) is
1355 SELECT language_use_reference_id, object_version_number
1356 FROM hz_person_language
1357 WHERE party_id=l_party_id and primary_language_indicator='Y';
1358
1359 Cursor C_Lang2 (l_party_id number, l_language_name varchar2) is
1360 SELECT language_use_reference_id, object_version_number
1361 FROM hz_person_language
1362 WHERE party_id=l_party_id
1363 AND language_name=l_language_name
1364 AND nvl(status,'A') = 'A';
1365
1366 Cursor C_B2b_Contact(l_party_id Number) IS
1367 Select p.party_id Person_Party_id
1368 from hz_relationships l,hz_parties p
1369 where l.party_id = l_party_id
1370 and l.subject_id = p.party_id
1371 and l.subject_table_name = 'HZ_PARTIES'
1372 and l.subject_type = 'PERSON';
1373
1374 BEGIN
1375 l_debug := ASO_QUOTE_UTIL_PVT.is_debug_enabled;
1376 IF l_debug = 'Y' THEN
1377 ASO_QUOTE_UTIL_PVT.Enable_Debug_Pvt;
1378 END IF;
1379 -- Standard Start of API savepoint
1380 SAVEPOINT create_Store_User_int;
1381 IF l_debug = 'Y' THEN
1382
1383 ASO_QUOTE_UTIL_PVT.Debug('aso_publish_misc_int: createStoreUser: Start %%%%%%%%%%%%%%%%%%%');
1384
1385 ASO_QUOTE_UTIL_PVT.Debug('createStoreUser: p_user_name: '|| p_user_name);
1386 ASO_QUOTE_UTIL_PVT.Debug('createStoreUser: p_user_password: '|| p_user_password);
1387 ASO_QUOTE_UTIL_PVT.Debug('createStoreUser: p_email_Address: '|| p_email_address);
1388 ASO_QUOTE_UTIL_PVT.Debug('createStoreUser: p_email_language: '|| p_email_language);
1389 ASO_QUOTE_UTIL_PVT.Debug('p_party_id: '|| p_party_id);
1390 ASO_QUOTE_UTIL_PVT.Debug('p_party_type: '|| p_party_type);
1391
1392 END IF;
1393
1394 -- Standard call to check for call compatibility.
1395 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1396 p_api_version_number,
1397 l_api_name,
1398 G_PKG_NAME) THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401
1402 -- Initialize message list if p_init_msg_list is set to TRUE.
1403 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1404 FND_MSG_PUB.initialize;
1405 END IF;
1406
1407 -- Set return status to success
1408 x_return_status := FND_API.G_RET_STS_SUCCESS;
1409
1410 --//////////////////////////////////////////////////////////////////////////
1411 -- create user id with supplied password
1412 l_user_id := fnd_user_pkg.CreateUserId (
1413 x_user_name => p_user_name,
1414 x_owner => null,
1415 x_unencrypted_password => p_user_password,
1416 x_email_address => p_email_address, /* Add for Bug 7334453 */
1417 x_customer_id => p_party_id );
1418
1419 --update cutomer id
1420 /* UPDATE FND_USER
1421 SET CUSTOMER_ID = p_party_id
1422 WHERE USER_NAME = p_user_name;
1423 */
1424 -- assign roles
1425 JTF_UM_USERTYPE_CREDENTIALS.ASSIGN_DEF_ROLES(
1426 P_USERNAME => p_user_name,
1427 P_ACCOUNT_TYPE => p_party_type );
1428
1429 -- assign resp
1430 JTF_UM_USERTYPE_CREDENTIALS.ASSIGN_DEF_RESP(
1434 -- CHANGE LANGUAGE PERF
1431 P_USERNAME => p_user_name,
1432 P_ACCOUNT_TYPE => p_party_type );
1433
1435
1436 BEGIN
1437 -- unset primary language indicator
1438 l_person_party_id := p_party_id;
1439
1440 open C_B2b_Contact(p_party_id);
1441 fetch C_B2b_Contact INTO l_person_party_id;
1442 close C_B2b_Contact;
1443
1444 open C_Lang1(l_person_party_id);
1445 fetch C_Lang1 INTO l_id, l_object_version_number;
1446 close C_Lang1;
1447
1448 l_lang_rec1.primary_language_indicator := 'N';
1449 l_lang_rec1.language_use_reference_id := l_id;
1450
1451
1452 if (l_debug = 'Y') then
1453
1454
1455 ASO_QUOTE_UTIL_PVT.Debug('Primary Language Indicator '|| l_lang_rec1.primary_language_indicator);
1456
1457 ASO_QUOTE_UTIL_PVT.Debug('Language use reference id '|| to_char(l_lang_rec1.language_use_reference_id));
1458
1459 end if;
1460
1461
1462 hz_person_info_v2pub.update_person_language(
1463 p_init_msg_list => FND_API.G_FALSE,
1464 p_person_language_rec => l_lang_rec1,
1465 p_object_version_number => l_object_version_number,
1466 x_return_status => l_return_status,
1467 x_msg_count => l_msg_count,
1468 x_msg_data => l_msg_data);
1469
1470 if (l_debug = 'Y') then
1471
1472 ASO_QUOTE_UTIL_PVT.Debug('successful completion of hz_person_info_v2pub.update_person_language - unset Primary Language Indicator');
1473
1474 end if;
1475 -- set primary language indicator
1476
1477 open C_Lang2(l_person_party_id, p_email_language);
1478
1479 fetch C_Lang2 INTO l_id, l_object_version_number;
1480
1481 if(C_Lang2%NOTFOUND) THEN
1482
1483 l_lang_rec2.primary_language_indicator := 'Y';
1484 l_lang_rec2.party_id := l_person_party_id;
1485 l_lang_rec2.language_name := p_email_language;
1486 -- Set Created_by_module as SALES in uppercase - skm
1487 l_lang_rec2.created_by_module := 'SALES';
1488
1489 if (l_debug = 'Y') then
1490
1491
1492 ASO_QUOTE_UTIL_PVT.Debug('Primary Language Indicator '|| l_lang_rec2.primary_language_indicator);
1493
1494 ASO_QUOTE_UTIL_PVT.Debug('party_id '|| to_char(l_lang_rec2.party_id));
1495
1496 ASO_QUOTE_UTIL_PVT.Debug('language_name '||l_lang_rec2.language_name);
1497
1498 end if;
1499 hz_person_info_v2pub.create_person_language(
1500 p_person_language_rec => l_lang_rec2,
1501 x_language_use_reference_id => l_ref_id,
1502 x_return_status => x_return_status,
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data);
1505
1506 if (l_debug = 'Y') then
1507
1508
1509 ASO_QUOTE_UTIL_PVT.Debug('successful completion of hz_person_info_v2pub.create_person_language');
1510
1511 end if;
1512
1513 ELSE
1514
1515 l_lang_rec2.primary_language_indicator := 'Y';
1516 l_lang_rec2.language_use_reference_id := l_id;
1517
1518 if (l_debug = 'Y') then
1519
1520 ASO_QUOTE_UTIL_PVT.Debug('Primary Language Indicator '||l_lang_rec2.primary_language_indicator);
1521 ASO_QUOTE_UTIL_PVT.Debug('Language Use Reference Id '||to_char(l_lang_rec2.language_use_reference_id));
1522
1523 end if;
1524
1525 hz_person_info_v2pub.update_person_language(
1526 p_person_language_rec => l_lang_rec2,
1527 p_object_version_number => l_object_version_number,
1528 x_return_status => x_return_status,
1529 x_msg_count => x_msg_count,
1530 x_msg_data => x_msg_data);
1531 END IF;
1532
1533 close C_Lang2;
1534
1535 EXCEPTION
1536
1537 WHEN others then
1538
1539 FND_MSG_PUB.count_and_get(
1540 p_encoded => FND_API.G_FALSE,
1541 p_count => x_msg_count,
1542 p_data => x_msg_data);
1543
1544 if (l_debug = 'Y') then
1545
1546
1547 ASO_QUOTE_UTIL_PVT.Debug('Error in iStore User Creation');
1548
1549 end if;
1550 end;
1551
1552 IF l_debug = 'Y' THEN
1553 ASO_QUOTE_UTIL_PVT.Debug ('End create_Store_User_int procedure');
1554 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
1555
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN fnd_api.g_exc_error
1560 THEN
1561 IF l_debug = 'Y' THEN
1562 ASO_QUOTE_UTIL_PVT.Debug ('Exception FND_API.G_EXC_ERROR in createStoreUser');
1563 END IF;
1564 aso_utility_pvt.handle_exceptions (
1565 p_api_name => l_api_name,
1566 p_pkg_name => g_pkg_name,
1567 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1568 p_package_type => aso_utility_pvt.g_int,
1569 p_sqlcode => SQLCODE,
1570 p_sqlerrm => SQLERRM,
1571 x_msg_count => x_msg_count,
1572 x_msg_data => x_msg_data,
1573 x_return_status => x_return_status
1574 );
1575 IF l_debug = 'Y' THEN
1576 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
1577 END IF;
1578
1579 WHEN fnd_api.g_exc_unexpected_error
1580 THEN
1581 IF l_debug = 'Y' THEN
1582 ASO_QUOTE_UTIL_PVT.Debug ('Exception FND_API.G_EXC_UNEXPECTED_ERROR in createStoreUser ');
1583 END IF;
1584 aso_utility_pvt.handle_exceptions (
1585 p_api_name => l_api_name,
1586 p_pkg_name => g_pkg_name,
1587 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1591 x_msg_count => x_msg_count,
1588 p_package_type => aso_utility_pvt.g_int,
1589 p_sqlcode => SQLCODE,
1590 p_sqlerrm => SQLERRM,
1592 x_msg_data => x_msg_data,
1593 x_return_status => x_return_status
1594 );
1595 IF l_debug = 'Y' THEN
1596 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
1597 END IF;
1598
1599 WHEN OTHERS
1600 THEN
1601 IF l_debug = 'Y' THEN
1602 ASO_QUOTE_UTIL_PVT.Debug ('When Others Exception in createStoreUser ');
1603 END IF;
1604 aso_utility_pvt.handle_exceptions (
1605 p_api_name => l_api_name,
1606 p_pkg_name => g_pkg_name,
1607 p_exception_level => aso_utility_pvt.g_exc_others,
1608 p_package_type => aso_utility_pvt.g_int,
1609 p_sqlcode => SQLCODE,
1610 p_sqlerrm => SQLERRM,
1611 x_msg_count => x_msg_count,
1612 x_msg_data => x_msg_data,
1613 x_return_status => x_return_status
1614 );
1615
1616 END createStoreUser;
1617
1618 PROCEDURE TestUserName(
1619 p_user_name IN VARCHAR2,
1620 x_test_user_status OUT NOCOPY VARCHAR2,
1621 x_return_status OUT NOCOPY VARCHAR2,
1622 x_msg_count OUT NOCOPY NUMBER,
1623 x_msg_data OUT NOCOPY VARCHAR2
1624 )
1625 IS
1626 l_api_version_number CONSTANT NUMBER := 1.0;
1627 l_api_name CONSTANT VARCHAR2(30) := 'TestUserName' ;
1628 l_debug VARCHAR2(1);
1629 BEGIN
1630
1631 l_debug := ASO_QUOTE_UTIL_PVT.is_debug_enabled;
1632 IF l_debug = 'Y' THEN
1633 ASO_QUOTE_UTIL_PVT.Enable_Debug_Pvt;
1634 END IF;
1635
1636 x_return_status := Fnd_Api.g_ret_sts_success;
1637
1638 IF l_debug = 'Y' THEN
1639 ASO_QUOTE_UTIL_PVT.Debug('Calling FND_USER_PKG.TestUserName');
1640 ASO_QUOTE_UTIL_PVT.Debug('p_user_name '||p_user_name);
1641 END IF;
1642
1643 x_test_user_status := Fnd_User_Pkg.TestUserName(p_user_name);
1644
1645 x_msg_data := fnd_message.get;
1646
1647 IF l_debug = 'Y' THEN
1648 ASO_QUOTE_UTIL_PVT.Debug('x_test_user_status '||x_test_user_status);
1649 ASO_QUOTE_UTIL_PVT.Debug('Successful completion of TestUserName');
1650 ASO_QUOTE_UTIL_PVT.Debug('Return Status '||x_return_status);
1651 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
1652 END IF;
1653
1654 EXCEPTION
1655
1656 WHEN OTHERS THEN
1657 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1658 Aso_Utility_Pvt.HANDLE_EXCEPTIONS(
1659 P_API_NAME => l_api_name
1660 ,P_PKG_NAME => 'ASO_PUBLISH_MISC_INT'
1661 ,P_EXCEPTION_LEVEL => Aso_Utility_Pvt.G_EXC_OTHERS
1662 ,P_PACKAGE_TYPE => Aso_Utility_Pvt.G_PUB
1663 ,X_MSG_COUNT => X_MSG_COUNT
1664 ,X_MSG_DATA => X_MSG_DATA
1665 ,X_RETURN_STATUS => X_RETURN_STATUS
1666 );
1667
1668 IF l_debug = 'Y' THEN
1669 ASO_QUOTE_UTIL_PVT.Disable_Debug_Pvt;
1670 END IF;
1671
1672 END TestUserName;
1673
1674
1675 END aso_publish_misc_int;