[Home] [Help]
PACKAGE BODY: APPS.ASO_WORKFLOW_PVT
Source
1 PACKAGE BODY ASO_WORKFLOW_PVT as
2 /* $Header: asovwftb.pls 120.1 2005/06/29 12:46:12 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_WORKFLOW_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- ASO_workflow_pvt - Procedure for all iStore alerts that will be
9 -- executed using Workflow
10 -- End of Comments
11
12 g_ItemType Varchar2(10) := 'ASOALERT';
13 g_processName Varchar2(30) := 'ASOALERT';
14
15 Cursor c_ship_methods(pCode Varchar2) IS
16 Select Meaning
17 from oe_ship_methods_v
18 Where Lookup_code = pCode;
19
20 Cursor c_quote_header (p_quote_id NUMBER) IS
21 SELECT org_id,party_id, quote_name,quote_number, quote_version,
22 quote_password,cust_account_id,invoice_to_party_id,
23 invoice_to_party_site_id,quote_header_id,ordered_date,
24 order_id, total_list_price,total_shipping_charge,total_tax,
25 total_quote_price,invoice_to_cust_account_id,
26 total_adjusted_amount,currency_code
27 FROM aso_quote_headers_all
28 WHERE quote_header_id = p_quote_id;
29
30 g_quote_header_rec c_quote_header%ROWTYPE;
31
32 Cursor c_quote_detail (p_quote_id NUMBER) IS
33 SELECT Inventory_item_id, Organization_id, Quantity,
34 Line_quote_price,currency_code
35 FROM Aso_quote_lines_all
36 WHERE quote_header_id = p_quote_id
37 ORDER BY line_number;
38
39 g_quote_line_rec c_quote_detail%ROWTYPE;
40
41 Cursor c_quote_payment (p_quote_id NUMBER) IS
42 SELECT Payment_type_code
43 FROM Aso_Payments
44 WHERE quote_header_id = p_quote_id;
45
46 Cursor c_hz_parties(p_party_id NUMBER) IS
47 SELECT Party_Name,Person_First_Name,Person_Middle_Name,
48 Person_Last_name,party_type
49 FROM hz_parties
50 WHERE party_id = p_party_id;
51
52 Cursor c_hz_contact_points(p_party_id NUMBER) IS
53 SELECT Contact_Point_type,Primary_flag, Phone_line_type,
54 Phone_Country_code, Phone_area_code, Phone_number, Email_address
55 FROM hz_contact_points
56 WHERE owner_table_name = 'HZ_PARTIES'
57 AND owner_table_id = p_party_id;
58
59
60 cursor c_curr_symbol(p_currCode VARCHAR2) IS
61 SELECT fc.symbol FROM FND_CURRENCIES fc
62 WHERE fc.currency_code = p_currCode;
63
64
65 NEWLINE VARCHAR2(1) := fnd_global.newline;
66 TAB VARCHAR2(1) := fnd_global.tab;
67
68
69 Procedure getUserType(pPartyId IN Varchar2,pUserType OUT NOCOPY /* file.sql.39 change */ Varchar2) IS
70 l_PartyType Varchar2(30);
71 l_UserType Varchar2(30) := 'B2B';
72 BEGIN
73
74 FOR c_hz_parties_rec IN c_hz_parties(pPartyId) LOOP
75 l_PartyType := rtrim(c_hz_parties_rec.party_type);
76 END LOOP;
77
78 If l_PartyType = 'PERSON' Then
79 l_userType := 'B2C';
80 End If;
81
82 pUserType := l_userType;
83
84 END getUserType;
85
86
87 PROCEDURE NotifyOrderStatus(
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
90 p_quote_id IN NUMBER,
91 p_status IN VARCHAR2,
92 p_errmsg_count IN NUMBER,
93 p_errmsg_data IN VARCHAR2,
94 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
95 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
96 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
97 ) IS
98
99 l_adhoc_user WF_USERS.NAME%TYPE;
100 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
101 l_event_type VARCHAR2(20);
102 l_email_addr WF_USERS.Email_Address%TYPE;
103 l_this NUMBER;
104 l_temp_str VARCHAR2(2000);
105 l_next NUMBER;
106 l_errmsg_count NUMBER;
107 l_errmsg_data VARCHAR2(32000);
108 l_item_owner WF_USERS.NAME%TYPE := 'SYSADMIN';
109 l_UserType Varchar2(30) := 'ALL';
110 l_notifname Varchar2(100);
111 --dummy pls_integer;
112 l_display_name Varchar2(100) := 'Quoting Order Administrator';
113 l_name Varchar2(100) := 'ASOORDERADMIN';
114 CURSOR wf_name_cur IS
115 select name
116 from wf_users
117 where orig_system = 'WF_LOCAL_USERS'
118 and name = 'ASOORDERADMIN'
119 and status = 'ACTIVE';
120 l_wf_name_cur_name varchar2(2000);
121
122 BEGIN
123 x_return_status := FND_API.g_ret_sts_success;
124
125 l_event_type := 'ORDERROR';
126 l_notifName := 'ORDERROR';
127
128 l_errmsg_count := p_errmsg_count;
129 IF aso_debug_pub.g_debug_flag = 'Y' THEN
130 ASO_DEBUG_PUB.add('Notify Order Status - Error Message Count - '||to_char(l_errmsg_count),1,'Y');
131 END IF;
132
133 IF ( l_errmsg_count = 1 ) THEN
134 l_errmsg_data := p_errmsg_data;
135 ELSE
136 l_this := 1;
137 l_errmsg_data := '';
138 WHILE ( l_this <= l_errmsg_count ) LOOP
139 l_temp_str := null;
140 fnd_msg_pub.Get(l_this,FND_API.G_FALSE,l_temp_str,l_next);
141 l_errmsg_data := l_errmsg_data || TAB || TAB ||l_temp_str || NEWLINE;
142 l_this := l_this + 1;
143 END LOOP;
144 END IF;
145
146 IF aso_debug_pub.g_debug_flag = 'Y' THEN
147 ASO_DEBUG_PUB.add('NotifyOrderStatus - Error Message Data After LOOP - '||l_errmsg_data,1,'Y');
148 END IF;
149
150
151 l_adhoc_user := FND_PROFILE.VALUE('ASO_ADMIN_EMAIL');
152
153 OPEN wf_name_cur;
154 FETCH wf_name_cur INTO l_wf_name_cur_name;
155 CLOSE wf_name_cur;
156
157 If l_wf_name_cur_name is not null Then
158
159 wf_directory.SetAdHocUserAttr(
160 user_name => l_name,
161 notification_preference => 'MAILTEXT',
162 email_address => l_adhoc_user );
163
164 wf_directory.SetAdHocUserExpiration(
165 user_name => l_name,
166 expiration_date => sysdate+10);
167
168 Else
169 wf_directory.CreateAdHocUser(
170 name => l_name,
171 display_name => l_display_name,
172 notification_preference => 'MAILTEXT',
173 email_address => l_adhoc_user,
174 expiration_date => sysdate+10 );
175
176 End If;
177
178 IF aso_debug_pub.g_debug_flag = 'Y' THEN
179 ASO_DEBUG_PUB.add('NotifyOrderStatus - p_quote_id - '||to_char(p_quote_id)||','||p_status,1,'Y');
180 END IF;
181
182 l_item_key := l_event_type||'-'||to_char(sysdate,'MMDDYYHH24MISS')||'-'||p_quote_id;
183
184 /* Item Key should be Unique as it represent a process instance with ITEM TYPE*/
185
186 IF aso_debug_pub.g_debug_flag = 'Y' THEN
187 ASO_DEBUG_PUB.add('Create and Start Process with Item Key: '||l_item_key,1,'Y');
188 END IF;
189
190 wf_engine.CreateProcess(
191 itemtype => g_ItemType,
192 itemkey => l_item_key,
193 process => g_processName);
194
195 wf_engine.SetItemUserKey(
196 itemtype => g_ItemType,
197 itemkey => l_item_key,
198 userkey => l_item_key);
199
200 wf_engine.SetItemAttrText(
201 itemtype => g_ItemType,
202 itemkey => l_item_key,
203 aname => 'ITEMKEY',
204 avalue => l_item_key);
205
206
207 wf_engine.SetItemAttrText(
208 itemtype => g_ItemType,
209 itemkey => l_item_key,
210 aname => 'EVENTTYPE',
211 avalue => l_event_type);
212
213 wf_engine.SetItemAttrText(
214 itemtype => g_ItemType,
215 itemkey => l_item_key,
216 aname => 'QUOTE_ID',
217 avalue => p_quote_id);
218
219 wf_engine.SetItemAttrText(
220 itemtype => g_ItemType,
221 itemkey => l_item_key,
222 aname => 'SENDTO',
223 avalue => l_name);
224
225 wf_engine.SetItemAttrText(
226 itemtype => g_ItemType,
227 itemkey => l_item_key,
228 aname => 'ERRMSG',
229 avalue => l_errmsg_data);
230
231 wf_engine.SetItemOwner(
232 itemtype => g_ItemType,
233 itemkey => l_item_key,
234 owner => l_item_owner);
235
236 wf_engine.StartProcess(
237 itemtype => g_ItemType,
238 itemkey => l_item_key);
239
240 IF aso_debug_pub.g_debug_flag = 'Y' THEN
241 ASO_DEBUG_PUB.add('Process Started',1,'Y');
242 END IF;
243
244 Exception
245 When OTHERS Then
246 x_return_status := FND_API.g_ret_sts_error;
247 x_msg_count := 0;
248
249 wf_core.context('ASO_WORKFLOW_PVT',
250 'NotifyOrderStatus',
251 l_event_type,
252 to_char(p_quote_id)
253 );
254 raise;
255
256 END NotifyOrderStatus;
257
258 PROCEDURE GenerateQuoteHeader(
259 document_id IN VARCHAR2,
260 display_type IN VARCHAR2,
261 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
262 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
263 ) IS
264
265 l_item_key wf_items.item_key%TYPE;
266 l_quote_id NUMBER;
267 l_event_type VARCHAR2(20);
268 l_contract_id NUMBER;
269 l_contract_Number VARCHAR2(120);
270 l_contract_Modifier VARCHAR2(120);
271 l_contact_name VARCHAR2(400);
272 l_contact_number VARCHAR2(70);
273 l_contact_email hz_contact_points.email_address%TYPE;
274 l_bill_to_party_name hz_parties.party_name%TYPE;
275 l_bill_to_name VARCHAR2(400);
276 l_bill_to_number VARCHAR2(70);
277 l_bill_to_fax VARCHAR2(70);
278 l_bill_to_address hz_locations.address1%TYPE;
279 l_bill_to_city hz_locations.city%TYPE;
280 l_bill_to_state hz_locations.state%TYPE;
281 l_bill_to_zip hz_locations.postal_code%TYPE;
282 l_bill_to_country hz_locations.country%TYPE;
283 l_ship_to_site_id aso_shipments.ship_to_party_site_id%TYPE;
284 l_ship_to_party_id aso_shipments.ship_to_party_id%TYPE;
285 l_ship_to_cust_account_id aso_shipments.ship_to_cust_account_id%TYPE;
286 l_ship_method_code aso_shipments.ship_method_code%TYPE;
287 l_ship_method varchar2(80);
288 l_ship_to_party_name hz_parties.party_name%TYPE;
289 l_ship_to_name VARCHAR2(400);
290 l_ship_to_number VARCHAR2(70);
291 l_ship_to_fax VARCHAR2(70);
292 l_ship_to_address hz_locations.address1%TYPE;
293 l_ship_to_city hz_locations.city%TYPE;
294 l_ship_to_state hz_locations.state%TYPE;
295 l_ship_to_zip hz_locations.postal_code%TYPE;
296 l_ship_to_country hz_locations.country%TYPE;
297 l_document VARCHAR2(32000) := '';
298 l_temp_str VARCHAR2(2000):='';
299
300 Cursor c_hz_locations(p_loc_site_id NUMBER) IS
301 SELECT rtrim(address1) || ' ' || rtrim(address2) || ' ' || rtrim(address3) || ' ' || rtrim(address4) loc_address,
302 rtrim(city) loc_city,
303 rtrim(state)||'/' || rtrim(province) loc_state,
304 rtrim(postal_code) loc_zip,
305 rtrim(country) loc_country
306 FROM hz_locations
307 WHERE location_id = ( SELECT location_id
308 FROM hz_party_sites
309 WHERE party_site_id = p_loc_site_id);
310 Cursor c_aso_shipments(p_quote_id NUMBER) IS
311 SELECT ship_to_cust_account_id, ship_to_party_site_id, ship_to_party_id, ship_method_code
312 FROM aso_shipments
313 WHERE quote_header_id = p_quote_id
314 AND quote_line_id IS NULL
315 AND rownum = 1;
316
317
318 Cursor c_hz_cust_accounts(p_cust_account_id NUMBER) IS
319 SELECT hc.party_id,hp.Party_Name,hp.Person_First_Name,hp.Person_Middle_Name,hp.Person_Last_name,hp.party_type
320 FROM hz_cust_accounts hc, hz_parties hp
321 WHERE cust_account_id = p_cust_account_id
322 AND hc.party_id = hp.party_id;
323
324 l_sold_contact_party_id Number;
325 l_bill_contact_party_id Number;
326 l_ship_contact_party_id Number;
327
328
329 BEGIN
330
331 l_item_key := document_id;
332
333 IF aso_debug_pub.g_debug_flag = 'Y' THEN
334 ASO_DEBUG_PUB.add('GenerateQuoteHeader - l_item_key - '||l_item_key,1,'Y');
335 END IF;
336
337 l_quote_id := wf_engine.GetItemAttrText (
338 itemtype => g_ItemType,
339 itemkey => l_item_key,
340 aname => 'QUOTE_ID'
341 );
342
343 IF aso_debug_pub.g_debug_flag = 'Y' THEN
344 ASO_DEBUG_PUB.add('GenerateQuoteHeader - l_quote_id - '||l_quote_id,1,'Y');
345 END IF;
346
347 l_event_type := wf_engine.GetItemAttrText (
348 itemtype => g_ItemType,
349 itemkey => l_item_key,
350 aname => 'EVENTTYPE'
351 );
352
353 IF aso_debug_pub.g_debug_flag = 'Y' THEN
354 aso_debug_pub.add('GenerateQuoteHeader - l_event_type - '|| l_event_type,1,'Y');
355 END IF;
356
357 OPEN c_quote_header(l_quote_id);
358 LOOP
359 FETCH c_quote_header INTO g_quote_header_rec;
360 EXIT WHEN c_quote_header%NOTFOUND;
361
362 /* Get all contact information */
363
364 l_contact_name := null;
365 l_sold_contact_party_id := null;
366
367 FOR c_hz_parties_rec IN c_hz_parties(g_quote_header_rec.party_id) LOOP
368 If c_hz_parties_rec.party_type = 'PARTY_RELATIONSHIP' Then
369 l_contact_name := upper(rtrim(c_hz_parties_rec.person_first_name))||' '||upper(rtrim(c_hz_parties_rec.person_last_name));
370
371 l_sold_contact_party_id := g_quote_header_rec.party_id;
372 End If;
373 END LOOP;
374
375
376 If l_sold_contact_party_id is null Then
377 FOR c_hz_cust_acct_rec IN c_hz_cust_accounts(g_quote_header_rec.cust_account_id) LOOP
378 l_contact_name := upper(rtrim(c_hz_cust_acct_rec.person_first_name))||' '||upper(rtrim(c_hz_cust_acct_rec.person_last_name));
379 l_sold_contact_party_id := c_hz_cust_acct_rec.party_id;
380 End Loop;
381 End If;
382
383 IF aso_debug_pub.g_debug_flag = 'Y' THEN
384 ASO_DEBUG_PUB.add('GenerateQuoteHeader - l_contact_name '|| l_contact_name,1,'Y');
385 END IF;
386
387 l_contact_number := null;
388 l_contact_email := null;
389
390
391 FOR c_hz_contact_rec IN c_hz_contact_points(l_sold_contact_party_id) LOOP
392
393 If (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'GEN') AND (l_contact_number IS NULL OR c_hz_contact_rec.primary_flag ='Y') Then
394
395 l_contact_number := trim(c_hz_contact_rec.Phone_Country_code||' '||c_hz_contact_rec.Phone_area_code||' '||c_hz_contact_rec.Phone_number);
396
397 Elsif c_hz_contact_rec.contact_point_type = 'EMAIL' AND (l_contact_email IS NULL OR c_hz_contact_rec.primary_flag ='Y') Then
398
399 l_contact_email := c_hz_contact_rec.email_address;
400
401 End If;
402 END LOOP;
403
404 IF aso_debug_pub.g_debug_flag = 'Y' THEN
405 ASO_DEBUG_PUB.add('GenerateQuoteHeader - l_contact_number - '|| l_contact_number,1,'Y');
406 ASO_DEBUG_PUB.add('GenerateQuoteHeader - l_contact_email - '|| l_contact_email,1,'Y');
407 END IF;
408
409
410 /* Get all billing information */
411
412 l_bill_to_party_name := null;
413 l_bill_to_name := null;
414
415
416 /* Bill Customer Info. From Invoice_To_Cust_Account_ID */
417
418 FOR c_hz_cust_acct_rec IN c_hz_cust_accounts(nvl(g_quote_header_rec.invoice_to_cust_account_id,g_quote_header_rec.cust_account_id)) LOOP
419 l_bill_to_party_name := rtrim(c_hz_cust_acct_rec.party_name);
420 l_bill_to_name := upper(rtrim(c_hz_cust_acct_rec.person_first_name))||' '||upper(rtrim(c_hz_cust_acct_rec.person_last_name));
421 l_bill_contact_party_id := c_hz_cust_acct_rec.party_id;
422 End Loop;
423
424 IF aso_debug_pub.g_debug_flag = 'Y' THEN
425 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_party_name - '||l_bill_to_party_name,1,'Y');
426 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_name - '||l_bill_to_name,1,'Y');
427 END IF;
428
429
433 /* Bill Contact Info. From Invoice_To_Party_ID (PARTY_RELATIONSHIP) OR Bill Customer Party Id. */
430 l_bill_to_number := null;
431 l_bill_to_fax := null;
432
434
435 If g_quote_header_rec.invoice_to_party_id is not null Then
436 FOR c_hz_parties_rec IN c_hz_parties(g_quote_header_rec.invoice_to_party_id) LOOP
437 If c_hz_parties_rec.party_type = 'PARTY_RELATIONSHIP' Then
438 l_bill_contact_party_id := g_quote_header_rec.party_id;
439 End If;
440 END LOOP;
441 End if;
442
443 FOR c_hz_contact_rec IN c_hz_contact_points(l_bill_contact_party_id) LOOP
444
445 If (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'GEN') AND (l_bill_to_number IS NULL OR c_hz_contact_rec.primary_flag ='Y') Then
446
447 l_bill_to_number := trim(c_hz_contact_rec.Phone_Country_code||' '||c_hz_contact_rec.Phone_area_code||' '||c_hz_contact_rec.Phone_number);
448
449 Elsif (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'FAX') AND (l_bill_to_fax IS NULL OR c_hz_contact_rec.primary_flag ='Y') Then
450
451 l_bill_to_fax := trim(c_hz_contact_rec.Phone_Country_code||' '||c_hz_contact_rec.Phone_area_code||' '||c_hz_contact_rec.Phone_number);
452
453 End If;
454 END LOOP;
455
456 IF aso_debug_pub.g_debug_flag = 'Y' THEN
457 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_party_number - '||l_bill_to_number,1,'Y');
458 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_fax - '||l_bill_to_fax,1,'Y');
459 END IF;
460 l_bill_to_address := null;
461 l_bill_to_city := null;
462 l_bill_to_state := null;
463 l_bill_to_zip := null;
464 l_bill_to_country := null;
465
466 /* Bill to Location id using invoice_to_party_site_id */
467
468 FOR c_hz_locations_rec IN c_hz_locations(g_quote_header_rec.invoice_to_party_site_id) LOOP
469
470 l_bill_to_address := c_hz_locations_rec.loc_address;
471 l_bill_to_city := c_hz_locations_rec.loc_city;
472 l_bill_to_state := c_hz_locations_rec.loc_state;
473 l_bill_to_zip := c_hz_locations_rec.loc_zip;
474 l_bill_to_country := c_hz_locations_rec.loc_country;
475
476 END LOOP;
477
478 IF aso_debug_pub.g_debug_flag = 'Y' THEN
479 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_address - '||l_bill_to_address,1,'Y');
480 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_city - '||l_bill_to_city,1,'Y');
481 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_state - '||l_bill_to_state,1,'Y');
482 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_zip - '||l_bill_to_zip,1,'Y');
483 ASO_DEBUG_PUB.add('GenerateQuoteHeader - bill_country - '||l_bill_to_country,1,'Y');
484 END IF;
485
486 /* Get all shipping information */
487
488 l_ship_to_site_id := null;
489 l_ship_to_party_id := null;
490 l_ship_method_code := null;
491
492 FOR c_aso_shipments_rec IN c_aso_shipments(l_quote_id) LOOP
493 l_ship_to_site_id := c_aso_shipments_rec.ship_to_party_site_id;
494 l_ship_to_cust_account_id := c_aso_shipments_rec.ship_to_cust_account_id;
495 l_ship_to_party_id := c_aso_shipments_rec.ship_to_party_id;
496 l_ship_method_code := c_aso_shipments_rec.ship_method_code;
497 END LOOP;
498
499 For c_ship_method_rec in c_ship_methods(l_ship_method_code) LOOP
500 l_Ship_Method := c_ship_method_rec.Meaning;
501 End Loop;
502
503 IF aso_debug_pub.g_debug_flag = 'Y' THEN
504 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_site_id - '||l_ship_to_site_id,1,'Y');
505 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_party_id - '||l_ship_to_party_id,1,'Y');
506 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_method - '||l_ship_method_code,1,'Y');
507 END IF;
508
509 /* Shipping Customer Information - ship_to_cust_account_id */
510
511 FOR c_hz_cust_acct_rec IN c_hz_cust_accounts(nvl(l_ship_to_cust_account_id,g_quote_header_rec.cust_account_id)) LOOP
512 l_ship_to_party_name := rtrim(c_hz_cust_acct_rec.party_name);
513 l_ship_to_name := upper(rtrim(c_hz_cust_acct_rec.person_first_name))||' '||upper(rtrim(c_hz_cust_acct_rec.person_last_name));
514 l_ship_contact_party_id := c_hz_cust_acct_rec.party_id;
515 End Loop;
516
517
518 IF aso_debug_pub.g_debug_flag = 'Y' THEN
519 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_party_name - '||l_ship_to_party_name,1,'Y');
520 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_name - '||l_ship_to_name,1,'Y');
521 END IF;
522
523 /* Shipping Contact ship_to_party_id(PARTY_REALTIONSHIP) OR Ship Customer's Party Id */
524 If l_ship_to_party_id is not null Then
525 FOR c_hz_parties_rec IN c_hz_parties(l_ship_to_party_id) LOOP
526 If c_hz_parties_rec.party_type = 'PARTY_RELATIONSHIP' Then
527 l_ship_contact_party_id := g_quote_header_rec.party_id;
528 End If;
529 END LOOP;
530 End If;
531
532 FOR c_hz_contact_rec IN c_hz_contact_points(l_ship_contact_party_id) LOOP
533
537
534 If (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'GEN') AND (l_ship_to_number IS NULL OR c_hz_contact_rec.primary_flag ='Y') Then
535
536 l_ship_to_number := trim(c_hz_contact_rec.Phone_Country_code||' '||c_hz_contact_rec.Phone_area_code||' '||c_hz_contact_rec.Phone_number);
538 Elsif (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'FAX') AND (l_ship_to_fax IS NULL OR c_hz_contact_rec.primary_flag ='Y') Then
539
540 l_ship_to_fax := trim(c_hz_contact_rec.Phone_Country_code||' '||c_hz_contact_rec.Phone_area_code||' '||c_hz_contact_rec.Phone_number);
541
542 End If;
543 END LOOP;
544
545 IF aso_debug_pub.g_debug_flag = 'Y' THEN
546 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_number - '||l_ship_to_number,1,'Y');
547 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_to_fax - '||l_ship_to_fax,1,'Y');
548 END IF;
549
550 l_ship_to_address := null;
551 l_ship_to_city := null;
552 l_ship_to_state := null;
553 l_ship_to_zip := null;
554 l_ship_to_country := null;
555
556 FOR c_hz_locations_rec IN c_hz_locations(l_ship_to_site_id) LOOP
557
558 l_ship_to_address := c_hz_locations_rec.loc_address;
559 l_ship_to_city := c_hz_locations_rec.loc_city;
560 l_ship_to_state := c_hz_locations_rec.loc_state;
561 l_ship_to_zip := c_hz_locations_rec.loc_zip;
562 l_ship_to_country := c_hz_locations_rec.loc_country;
563
564 END LOOP;
565
566
567 IF aso_debug_pub.g_debug_flag = 'Y' THEN
568 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_address - '||l_ship_to_address,1,'Y');
569 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_city - '||l_ship_to_city,1,'Y');
570 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_state - '||l_ship_to_state,1,'Y');
571 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_zip - '||l_ship_to_zip,1,'Y');
572 ASO_DEBUG_PUB.add('GenerateQuoteHeader - ship_country - '||l_ship_to_country,1,'Y');
573 ASO_DEBUG_PUB.add('GenerateQuoteHeader - quote_header_id - '||g_quote_header_rec.quote_header_id,1,'Y');
574 END IF;
575
576 IF (display_type = 'text/plain' ) THEN
577 fnd_message.set_name('ASO','ASO_PRMT_QUOTE_NUMBER_COLON');
578 l_temp_str := null;
579 l_temp_str := fnd_message.get;
580 l_document := l_document || l_temp_str ||TAB||TAB||g_quote_header_rec.quote_number||NEWLINE;
581
582 fnd_message.set_name('ASO','ASO_PRMT_QUOTE_NAME_COLON');
583 l_temp_str := null;
584 l_temp_str := fnd_message.get;
585 l_document := l_document || l_temp_str ||TAB||g_quote_header_rec.quote_name||NEWLINE;
586
587 fnd_message.set_name('ASO','ASO_PRMT_SHIP_METH_COLON');
588 l_temp_str := null;
589 l_temp_str := fnd_message.get;
590 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_method||NEWLINE;
591
592 fnd_message.set_name('ASO','ASO_PRMT_CUST_CNTCT_INFO');
593 l_temp_str := null;
594 l_temp_str := fnd_message.get;
595 l_document := l_document || NEWLINE ||l_temp_str ||NEWLINE||NEWLINE;
596
597 fnd_message.set_name('ASO','ASO_PRMT_CNTCT_NAME_COL');
598 l_temp_str := null;
599 l_temp_str := fnd_message.get;
600 l_document := l_document || l_temp_str ||TAB||TAB||TAB||l_contact_name||NEWLINE;
601
602 fnd_message.set_name('ASO','ASO_PRMT_CNTCT_NUM_COL');
603 l_temp_str := null;
604 l_temp_str := fnd_message.get;
605 l_document := l_document || l_temp_str ||TAB||TAB||TAB||l_contact_number||NEWLINE;
606
607 fnd_message.set_name('ASO','ASO_PRMT_CNTCT_EMAIL_COL');
608 l_temp_str := null;
609 l_temp_str := fnd_message.get;
610 l_document := l_document || l_temp_str ||TAB||TAB||l_contact_email||NEWLINE;
611
612 fnd_message.set_name('ASO','ASO_PRMT_QUOTE_BILL_INFO');
613 l_temp_str := null;
614 l_temp_str := fnd_message.get;
615 l_document := l_document || NEWLINE ||l_temp_str ||NEWLINE||NEWLINE;
616
617 fnd_message.set_name('ASO','ASO_PRMT_CUST_NAME_COLON');
618 l_temp_str := null;
619 l_temp_str := fnd_message.get;
620 l_document := l_document || l_temp_str ||TAB||TAB||l_bill_to_party_name||NEWLINE;
621
622 fnd_message.set_name('ASO','ASO_PRMT_ADDRESS_COLON');
623 l_temp_str := null;
624 l_temp_str := fnd_message.get;
625 l_document := l_document || l_temp_str ||TAB||TAB||l_bill_to_address||NEWLINE;
626
627 fnd_message.set_name('ASO','ASO_PRMT_CITY_COLON');
628 l_temp_str := null;
629 l_temp_str := fnd_message.get;
630 l_document := l_document || l_temp_str ||TAB||TAB||TAB||l_bill_to_city||NEWLINE;
631
632 fnd_message.set_name('ASO','ASO_PRMT_STATE_PRO_COLON');
633 l_temp_str := null;
634 l_temp_str := fnd_message.get;
635 l_document := l_document || l_temp_str ||TAB||TAB||l_bill_to_state||NEWLINE;
636
637 fnd_message.set_name('ASO','ASO_PRMT_ZIP_POSTAL_COLON');
638 l_temp_str := null;
639 l_temp_str := fnd_message.get;
640 l_document := l_document || l_temp_str ||TAB||l_bill_to_zip||NEWLINE;
641
642 fnd_message.set_name('ASO','ASO_PRMT_COUNTRY_COLON');
643 l_temp_str := null;
644 l_temp_str := fnd_message.get;
645 l_document := l_document || l_temp_str ||TAB||TAB||l_bill_to_country||NEWLINE;
646
647 fnd_message.set_name('ASO','ASO_PRMT_CNTCT_NAME_COL');
651
648 l_temp_str := null;
649 l_temp_str := fnd_message.get;
650 l_document := l_document || l_temp_str ||TAB||TAB||l_bill_to_name||NEWLINE;
652 fnd_message.set_name('ASO','ASO_PRMT_TEL_NO_COLON');
653 l_temp_str := null;
654 l_temp_str := fnd_message.get;
655 l_document := l_document || l_temp_str ||TAB||l_bill_to_number||NEWLINE;
656
657 fnd_message.set_name('ASO','ASO_PRMT_FAX_NO_COLON');
658 l_temp_str := null;
659 l_temp_str := fnd_message.get;
660 l_document := l_document || l_temp_str ||TAB||TAB||l_bill_to_fax||NEWLINE;
661
662 fnd_message.set_name('ASO','ASO_PRMT_QUOTE_SHIP_INFOR');
663 l_temp_str := null;
664 l_temp_str := fnd_message.get;
665 l_document := l_document || NEWLINE ||l_temp_str ||NEWLINE||NEWLINE;
666
667 fnd_message.set_name('ASO','ASO_PRMT_CUST_NAME_COLON');
668 l_temp_str := null;
669 l_temp_str := fnd_message.get;
670 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_to_party_name||NEWLINE;
671
672 fnd_message.set_name('ASO','ASO_PRMT_ADDRESS_COLON');
673 l_temp_str := null;
674 l_temp_str := fnd_message.get;
675 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_to_address||NEWLINE;
676
677 fnd_message.set_name('ASO','ASO_PRMT_CITY_COLON');
678 l_temp_str := null;
679 l_temp_str := fnd_message.get;
680 l_document := l_document || l_temp_str ||TAB||TAB||TAB||l_ship_to_city||NEWLINE;
681
682 fnd_message.set_name('ASO','ASO_PRMT_STATE_PRO_COLON');
683 l_temp_str := null;
684 l_temp_str := fnd_message.get;
685 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_to_state||NEWLINE;
686
687 fnd_message.set_name('ASO','ASO_PRMT_COUNTRY_COLON');
688 l_temp_str := null;
689 l_temp_str := fnd_message.get;
690 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_to_country||NEWLINE;
691
692 fnd_message.set_name('ASO','ASO_PRMT_CNTCT_NAME_COL');
693 l_temp_str := null;
694 l_temp_str := fnd_message.get;
695 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_to_name||NEWLINE;
696
697 fnd_message.set_name('ASO','ASO_PRMT_TEL_NO_COLON');
698 l_temp_str := null;
699 l_temp_str := fnd_message.get;
700 l_document := l_document || l_temp_str ||TAB||l_ship_to_number||NEWLINE;
701
702 fnd_message.set_name('ASO','ASO_PRMT_FAX_NO_COLON');
703 l_temp_str := null;
704 l_temp_str := fnd_message.get;
705 l_document := l_document || l_temp_str ||TAB||TAB||l_ship_to_fax||NEWLINE;
706
707 ELSE
708 null;
709 END IF;
710
711 END LOOP;
712 CLOSE c_quote_header;
713
714 IF aso_debug_pub.g_debug_flag = 'Y' THEN
715 ASO_DEBUG_PUB.add('GenerateQuoteHeader - l_document'||NEWLINE|| l_document,1,'Y');
716 END IF;
717
718 document := l_document;
719 document_type := 'text/plain';
720
721 EXCEPTION
722 When Others Then
723 IF c_quote_header%ISOPEN THEN
724 CLOSE c_quote_header;
725 END IF;
726 Raise;
727 END GenerateQuoteHeader;
728
729 PROCEDURE Selector(
730 itemtype IN VARCHAR2,
731 itemkey IN VARCHAR2,
732 actid IN NUMBER,
733 funcmode IN VARCHAR2,
734 result OUT NOCOPY /* file.sql.39 change */ VARCHAR2
735 ) IS
736
737 l_event_type VARCHAR2(50);
738
739 BEGIN
740 IF ( funcmode = 'RUN' ) THEN
741 l_event_type := wf_engine.GetItemAttrText(
742 itemtype => itemtype,
743 itemkey => itemkey,
744 aname => 'EVENTTYPE'
745 );
746
747 IF aso_debug_pub.g_debug_flag = 'Y' THEN
748 ASO_DEBUG_PUB.add('Selector - Inside RUN- '||l_event_type,1,'Y');
749 END IF;
750
751 IF l_event_type = 'ORDERROR' THEN
752 IF aso_debug_pub.g_debug_flag = 'Y' THEN
753 ASO_DEBUG_PUB.add('Selector - Inside order confirmation selection ',1,'Y');
754 END IF;
755 result := 'COMPLETE:ORDERROR';
756 END IF;
757 END IF;
758 IF ( funcmode = 'CANCEL' ) THEN
759 result := 'COMPLETE';
760 END IF;
761 END Selector;
762
763 END aso_workflow_pvt;