[Home] [Help]
PACKAGE BODY: APPS.ASO_WORKFLOW_QUOTE_PVT
Source
1 PACKAGE BODY ASO_WORKFLOW_QUOTE_PVT AS
2 /* $Header: asovwfqb.pls 120.1 2005/06/29 12:46:05 appldev ship $ */
3
4 -- Start of Comments
5 -- Package name : ASO_WORKFLOW_QUOTE_PVT
6 -- Purpose :
7 -- History :
8 -- 03-26-2003 hyang - bug fix 2870829, increase size of
9 -- FND_CURRENCIES.Symbol
10 -- NOTE :
11 -- End of Comments
12
13
14 g_ItemType VARCHAR2(10) := 'ASOALERT';
15 g_processName VARCHAR2(30) := 'PROCESSMAP';
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_WORKFLOW_QUOTE_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovwfqb.pls';
18 NEWLINE VARCHAR2(1) := fnd_global.newline;
19 TAB VARCHAR2(1) := fnd_global.tab;
20
21 GET_MESSAGE_ERROR EXCEPTION;
22
23
24 CURSOR c_quote_header (p_quote_id NUMBER) IS
25 SELECT org_id, party_id, quote_name,quote_number,
26 quote_version, quote_password, contract_requester_id,
27 cust_account_id,invoice_to_party_id, invoice_to_party_site_id,
28 quote_header_id, ordered_date, order_id, total_list_price,
29 total_shipping_charge,total_tax, total_quote_price,
30 invoice_to_cust_account_id,total_adjusted_amount,currency_code,
31 resource_id
32 FROM aso_quote_headers_all
33 WHERE quote_header_id = p_quote_id;
34
35 g_quote_header_rec c_quote_header%ROWTYPE;
36
37 CURSOR c_curr_symbol(p_currCode VARCHAR2) IS
38 SELECT fc.symbol
39 FROM FND_CURRENCIES fc
40 WHERE fc.currency_code = p_currCode;
41
42
43 PROCEDURE getUserType(pPartyId IN Varchar2,pUserType OUT NOCOPY /* file.sql.39 change */ Varchar2)
44 IS
45
46 l_PartyType Varchar2(30);
47 l_UserType Varchar2(30) := 'B2B';
48
49 CURSOR c_hz_parties(p_party_id NUMBER) IS
50 SELECT Party_Name,Person_First_Name,Person_Middle_Name,Person_Last_name,party_type,Person_title
51 FROM hz_parties
52 WHERE party_id = p_party_id;
53
54 BEGIN
55
56 FOR c_hz_parties_rec IN c_hz_parties(pPartyId)
57 LOOP
58 l_PartyType := rtrim(c_hz_parties_rec.party_type);
59 END LOOP;
60
61 IF l_PartyType = 'PERSON'
62 THEN
63 l_userType := 'B2C';
64 END IF;
65
66 pUserType := l_userType;
67
68 END getUserType;
69
70 /* PROCEDURE: To send OUT email alert for change in contract status.
71
72 */
73
74 PROCEDURE NotifyForASOContractChange(
75 p_api_version IN NUMBER,
76 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
77 p_quote_id IN NUMBER,
78 p_contract_id IN NUMBER,
79 p_notification_type IN VARCHAR2,
80 p_customer_comments IN VARCHAR2 := FND_API.G_MISS_CHAR,
81 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
82 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
83 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
84 ) IS
85
86 l_event_type VARCHAR(30);
87 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
88 l_item_owner WF_USERS.NAME%TYPE := 'SYSADMIN';
89
90 l_quote_org_id NUMBER;
91
92 l_org_contract_rep WF_USERS.NAME%TYPE;
93 l_contract_requester WF_USERS.NAME%TYPE;
94 l_contract_requester_id NUMBER;
95
96 l_quote_number NUMBER;
97
98 l_msite_id NUMBER := null;
99
100 l_partyId NUMBER;
101
102 l_notifEnabled VARCHAR2(3) := 'Y';
103 l_notifName VARCHAR2(30) := 'ASOCONTRACTAPPROVED';
104 l_UserType VARCHAR2(30) := 'ALL';
105 l_messageName WF_MESSAGES.NAME%TYPE;
106 l_msgEnabled VARCHAR2(3) :='Y';
107
108 CURSOR c_fnd_user(lc_user_id NUMBER) IS
109 SELECT user_name
110 FROM fnd_user
111 WHERE user_id = lc_user_id;
112
113 BEGIN
114
115 x_return_status := FND_API.g_ret_sts_success;
116
117 -- Check for WorkFlow Feature Availablity.
118
119 IF p_notification_type = 'CONTRACT_APPROVED'
120 THEN
121 -- Approved
122
123 l_event_type := 'ASOCONTRACTAPPROVED';
124 l_notifName := 'ASOCONTRACTAPPROVED';
125
126 ELSIF p_notification_type = 'CONTRACT_CANCELED'
127 THEN
128
129 l_event_type := 'ASOCONTRACTCANCELED';
130 l_notifName := 'ASOCONTRACTCANCELED';
131
132 ELSIF p_notification_type = 'CONTRACT_CHANGED'
133 THEN
134
135 l_event_type := 'ASOCONTRACTCHANGED';
136 l_notifName := 'ASOCONTRACTCHANGED';
137
138 ELSIF p_notification_type = 'CONTRACT_REJECTED'
139 THEN
140
141 l_event_type := 'ASOCONTRACTREJECTED';
142 l_notifName := 'ASOCONTRACTREJECTED';
143
144 END IF;
145
146 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
147 aso_debug_pub.add('NotifyForASOContractChange: Check if this notification is enabled.', 1, 'Y');
148 END IF;
149
150 l_notifEnabled := IBE_WF_NOTIF_SETUP_PVT.Check_Notif_Enabled(l_notifName);
151
152 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
153 aso_debug_pub.add('NotifyForASOContractChange: Notification Name: '||l_notifName||' Enabled: '||l_notifEnabled, 1, 'Y');
154 END IF;
155
156 IF l_notifEnabled = 'Y'
157 THEN
158
159 FOR c_quote_rec In c_quote_header(p_quote_id)
160 LOOP
161 l_contract_requester_id := c_quote_rec.contract_requester_id;
162 l_quote_org_id := c_quote_rec.org_id;
163 l_quote_number := c_quote_rec.quote_number;
164 l_partyId := c_quote_rec.party_id;
165 END LOOP;
166
167 FOR c_fnd_user_rec In c_fnd_user(l_contract_requester_id)
168 LOOP
169 l_contract_requester := c_fnd_user_rec.user_name;
170 END LOOP;
171
172 l_msite_id := null;
173
174 getUserType(l_partyId,l_UserType);
175
176 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
177 aso_debug_pub.add('NotifyForASOContractChange: Get Message - MsiteId: '||to_Char(l_msite_id)||' Org_id: '||to_char(l_quote_org_id)||' User Type: '||l_userType, 1, 'Y');
178 END IF;
179
180 IBE_WF_MSG_MAPPING_PVT.Retrieve_Msg_Mapping
181 (
182 p_org_id => l_quote_org_id,
183 p_msite_id => l_msite_id,
184 p_user_type => l_userType,
185 p_notif_name => l_notifName,
186 x_enabled_flag => l_msgEnabled,
187 x_wf_message_name => l_MessageName,
188 x_return_status => x_return_status,
189 x_msg_data => x_msg_data,
190 x_msg_count => x_msg_data
191 );
192
193
194 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
195 aso_debug_pub.add('NotifyForASOContractChange: Message Name: '||l_MessageName||' Enabled: '||l_msgEnabled, 1, 'Y');
196 END IF;
197
198 IF x_msg_count > 0
199 THEN
200 Raise GET_MESSAGE_ERROR;
201 END IF;
202
203 IF l_msgEnabled = 'Y'
204 THEN
205
206 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
207 aso_debug_pub.add('NotifyForASOContractChange: NotifyForASOContractChange - eventtype - '||l_event_type, 1, 'Y');
208 END IF;
209
210 l_item_key := l_event_type||'-'||to_char(sysdate,'MMDDYYHH24MISS')||'-C'||p_quote_id;
211
212 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
213 aso_debug_pub.add('NotifyForASOContractChange: Create and Start Process with Item Key: '||l_item_key, 1, 'Y');
214 END IF;
215
216 wf_engine.CreateProcess(
217 itemtype => g_ItemType,
218 itemkey => l_item_key,
219 process => g_processName);
220
221 wf_engine.SetItemUserKey(
222 itemtype => g_ItemType,
223 itemkey => l_item_key,
224 userkey => l_item_key);
225
226 wf_engine.SetItemAttrText(
227 itemtype => g_ItemType,
228 itemkey => l_item_key,
229 aname => 'MESSAGE',
230 avalue => l_MessageName);
231
232 wf_engine.SetItemAttrText(
233 itemtype => g_ItemType,
234 itemkey => l_item_key,
235 aname => 'ITEMKEY',
236 avalue => l_item_key);
237
238 wf_engine.SetItemAttrText(
239 itemtype => g_ItemType,
240 itemkey => l_item_key,
241 aname => 'EVENTTYPE',
242 avalue => l_event_type);
243
244 wf_engine.SetItemAttrText(
245 itemtype => g_ItemType,
246 itemkey => l_item_key,
247 aname => 'QUOTEID',
248 avalue => p_quote_id);
249
250 wf_engine.SetItemAttrText(
251 itemtype => g_ItemType,
252 itemkey => l_item_key,
253 aname => 'QUOTENUM',
254 avalue => l_quote_number);
255
256 wf_engine.SetItemAttrText(
257 itemtype => g_ItemType,
258 itemkey => l_item_key,
259 aname => 'CONTRACTNO',
260 avalue => p_contract_id);
261
262 wf_engine.SetItemAttrText(
263 itemtype => g_ItemType,
264 itemkey => l_item_key,
265 aname => 'COMMENTS',
266 avalue => p_customer_comments);
267
268 wf_engine.SetItemAttrText(
269 itemtype => g_ItemType,
270 itemkey => l_item_key,
271 aname => 'SENDTO',
272 avalue => l_contract_requester);
273
274 wf_engine.SetItemOwner(
275 itemtype => g_ItemType,
276 itemkey => l_item_key,
277 owner => l_item_owner);
278
279 wf_engine.StartProcess(
280 itemtype => g_ItemType,
281 itemkey => l_item_key);
282
283
284 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
285 aso_debug_pub.add('NotifyForASOContractChange: Process Started', 1, 'Y');
286 END IF;
287 END IF; -- msgEnabled
288 END IF; -- NotiftEnabled
289
290 EXCEPTION
291 WHEN OTHERS
292 THEN
293 x_return_status := FND_API.g_ret_sts_error;
294 x_msg_count := 0;
295
296 wf_core.context('ASO_WORKFLOW_QUOTE_PVT',
297 'NotifyForASOContractChange',
298 l_event_type,
299 to_char(p_quote_id)
300 );
301 RAISE GET_MESSAGE_ERROR;
302
303 END NotifyForASOContractChange;
304
305
306 PROCEDURE ParseThisString (
307 p_string_in IN VARCHAR2,
308 p_string_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
309 p_string_left OUT NOCOPY /* file.sql.39 change */ VARCHAR2
310 )
311 IS
312
313 l_lengthy_word BOOLEAN;
314 l_line_length NUMBER;
315 l_length NUMBER;
316 l_lim NUMBER;
317 i NUMBER;
318 j NUMBER;
319 l_pos NUMBER;
320
321 BEGIN
322 l_line_length := 30;
323 l_length := length(p_string_in);
324 IF ( l_length < l_line_length ) THEN
325 p_string_out := rpad(p_string_in,35,' ');
326 p_string_left := '';
327 ELSE
328 l_lim := l_line_length;
329 p_string_out := '';
330 i := 1;
331 j := 1;
332 l_pos := 0;
333
334 WHILE i <= l_lim LOOP
335 j := instr(p_string_in,' ',i);
336 IF( (j=0) AND (i=1) ) THEN
337 l_pos := 28;
338 l_lengthy_word := true;
339 END IF;
340 IF ( j <> 0 ) THEN
341 i := j+1;
342 l_pos := j;
343 END IF;
344 EXIT WHEN j = 0;
345 END LOOP;
346 p_string_out := substr(p_string_in,1,l_pos);
347 IF ( l_lengthy_word = true ) THEN
348 l_lengthy_word := false;
349 p_string_out := p_string_out || '-';
350 END IF;
351 IF (length(p_string_out) < 35 ) THEN
352 p_string_out := rpad(p_string_out,35,' ');
353 END IF;
354 p_string_left := substr(p_string_in,l_pos+1,length(p_string_in)-l_pos);
355 END IF;
356 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
357 aso_debug_pub.add('ParseThisString - p_string_out - '||p_string_out, 1, 'Y');
358 aso_debug_pub.add('ParseThisString - p_string_left - '||p_string_left, 1, 'Y');
359 END IF;
360 END ParseThisString;
361
362 FUNCTION AddSpaces (
363 p_num_in IN NUMBER
364 ) RETURN VARCHAR2
365 IS
366 l_str_out varchar2(200);
367 BEGIN
368 l_str_out := rpad(' ',p_num_in,' ');
369 return l_str_out;
370 END AddSpaces;
371
372
373 PROCEDURE GenerateQuoteHeader(
374 document_id IN VARCHAR2,
375 display_type IN VARCHAR2,
376 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
377 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
378 )
379 IS
380
381 l_item_key wf_items.item_key%TYPE;
382 l_quote_id NUMBER;
383 l_event_type VARCHAR2(20);
384 l_contract_id NUMBER;
385 l_contract_Number VARCHAR2(120);
386 l_contract_Modifier VARCHAR2(120);
387 l_comments VARCHAR2(2000);
388 l_contact_name VARCHAR2(400);
389 l_contact_number VARCHAR2(70);
390 l_contact_email hz_contact_points.email_address%TYPE;
391 l_bill_to_party_name hz_parties.party_name%TYPE;
392 l_bill_to_name VARCHAR2(400);
393 l_bill_to_number VARCHAR2(70);
394 l_bill_to_fax VARCHAR2(70);
395 l_bill_to_address hz_locations.address1%TYPE;
396 l_bill_to_city hz_locations.city%TYPE;
397 l_bill_to_state hz_locations.state%TYPE;
398 l_bill_to_zip hz_locations.postal_code%TYPE;
399 l_bill_to_country hz_locations.country%TYPE;
400 l_ship_to_site_id aso_shipments.ship_to_party_site_id%TYPE;
401 l_ship_to_party_id aso_shipments.ship_to_party_id%TYPE;
402 l_ship_to_cust_account_id aso_shipments.ship_to_cust_account_id%TYPE;
403 l_ship_method_code aso_shipments.ship_method_code%TYPE;
404
405 l_ship_method varchar2(80);
406
407 l_ship_to_party_name hz_parties.party_name%TYPE;
408 l_ship_to_name VARCHAR2(400);
409 l_ship_to_number VARCHAR2(70);
410 l_ship_to_fax VARCHAR2(70);
411 l_ship_to_address hz_locations.address1%TYPE;
415 l_ship_to_country hz_locations.country%TYPE;
412 l_ship_to_city hz_locations.city%TYPE;
413 l_ship_to_state hz_locations.state%TYPE;
414 l_ship_to_zip hz_locations.postal_code%TYPE;
416 l_document VARCHAR2(32000) := '';
417 l_temp_str VARCHAR2(2000):='';
418
419 Cursor c_hz_locations(p_loc_site_id NUMBER) IS
420 SELECT rtrim(address1) || ' ' || rtrim(address2) || ' ' || rtrim(address3) || ' ' || rtrim(address4) loc_address,
421 rtrim(city) loc_city,
422 rtrim(state)||'/' || rtrim(province) loc_state,
423 rtrim(postal_code) loc_zip,
424 rtrim(country) loc_country
425 FROM hz_locations
426 WHERE location_id = ( SELECT location_id
427 FROM hz_party_sites
428 WHERE party_site_id = p_loc_site_id);
429 Cursor c_aso_shipments(p_quote_id NUMBER) IS
430 SELECT ship_to_cust_account_id, ship_to_party_site_id, ship_to_party_id, ship_method_code
431 FROM aso_shipments
432 WHERE quote_header_id = p_quote_id
433 AND quote_line_id IS NULL
434 AND rownum = 1;
435
436
437 Cursor c_hz_cust_accounts(p_cust_account_id NUMBER) IS
438 SELECT hc.party_id,hp.Party_Name,hp.Person_First_Name,hp.Person_Middle_Name,hp.Person_Last_name,hp.party_type
439 FROM hz_cust_accounts hc, hz_parties hp
440 WHERE cust_account_id = p_cust_account_id
441 AND hc.party_id = hp.party_id;
442
443 Cursor c_ship_methods(pCode Varchar2) IS
444 Select Meaning
445 from oe_ship_methods_v
446 Where Lookup_code = pCode;
447
448 CURSOR c_contract_header(p_id number) IS
449 Select Contract_number,Contract_number_modifier
450 From okc_k_headers_b
451 Where ID = p_id;
452
453 CURSOR c_hz_parties(p_party_id NUMBER) IS
454 SELECT Party_Name,Person_First_Name,Person_Middle_Name,Person_Last_name,party_type,Person_title
455 FROM hz_parties
456 WHERE party_id = p_party_id;
457
458 CURSOR c_hz_contact_points(p_party_id NUMBER) IS
459 SELECT Contact_Point_type,Primary_flag, Phone_line_type, Phone_Country_code, Phone_area_code, Phone_number, Email_address
460 FROM hz_contact_points
461 WHERE owner_table_name = 'HZ_PARTIES'
462 AND owner_table_id = p_party_id;
463
464
465 l_sold_contact_party_id Number;
466 l_bill_contact_party_id Number;
467 l_ship_contact_party_id Number;
468
469 BEGIN
470
471 l_item_key := document_id;
472
473 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
474 aso_debug_pub.add('GenerateQuoteHeader - l_item_key - '||l_item_key, 1, 'Y');
475 END IF;
476
477 l_quote_id := wf_engine.GetItemAttrText (
478 itemtype => g_ItemType,
479 itemkey => l_item_key,
480 aname => 'QUOTEID'
481 );
482 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
483 aso_debug_pub.add('GenerateQuoteHeader - l_quote_id - '||l_quote_id, 1, 'Y');
484 END IF;
485
486 l_event_type := wf_engine.GetItemAttrText (
487 itemtype => g_ItemType,
488 itemkey => l_item_key,
489 aname => 'EVENTTYPE'
490 );
491
492 l_contract_id := wf_engine.GetItemAttrText (
493 itemtype => g_ItemType,
494 itemkey => l_item_key,
495 aname => 'CONTRACTNO'
496 );
497
498
499 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
500 aso_debug_pub.add('GenerateQuoteHeader - l_event_type - '|| l_event_type, 1, 'Y');
501 END IF;
502
503 FOR c_contract_rec In c_contract_header(l_contract_id)
504 LOOP
505 l_contract_number := c_contract_rec.contract_number;
506 l_contract_modifier := c_contract_rec.contract_number_modifier;
507 END LOOP;
508
509
510 OPEN c_quote_header(l_quote_id);
511 LOOP
512 FETCH c_quote_header INTO g_quote_header_rec;
513 EXIT WHEN c_quote_header%NOTFOUND;
514
515 /* Get all contact information */
516
517 l_contact_name := null;
518 l_sold_contact_party_id := null;
519
520 FOR c_hz_parties_rec IN c_hz_parties(g_quote_header_rec.party_id)
521 LOOP
522 IF c_hz_parties_rec.party_type = 'PARTY_RELATIONSHIP'
523 THEN
524 l_contact_name := upper(rtrim(c_hz_parties_rec.person_first_name))||' '||upper(rtrim(c_hz_parties_rec.person_last_name));
525
526 l_sold_contact_party_id := g_quote_header_rec.party_id;
527 END IF;
528 END LOOP;
529
530 IF l_sold_contact_party_id is NULL
531 THEN
532 FOR c_hz_cust_acct_rec IN c_hz_cust_accounts(g_quote_header_rec.cust_account_id)
533 LOOP
534 l_contact_name := upper(rtrim(c_hz_cust_acct_rec.person_first_name))||' '||upper(rtrim(c_hz_cust_acct_rec.person_last_name));
535 l_sold_contact_party_id := c_hz_cust_acct_rec.party_id;
536 END LOOP;
537 END IF;
538
539 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
540 aso_debug_pub.add('GenerateQuoteHeader - l_contact_name '|| l_contact_name, 1, 'Y');
541 END IF;
542
543 l_contact_number := null;
544 l_contact_email := null;
545
546 FOR c_hz_contact_rec IN c_hz_contact_points(l_sold_contact_party_id)
547 LOOP
548
549 IF (c_hz_contact_rec.contact_point_type = 'PHONE'
550 AND c_hz_contact_rec.phone_line_type = 'GEN')
551 AND (l_contact_number IS NULL OR c_hz_contact_rec.primary_flag ='Y')
552 THEN
556 l_contact_email := c_hz_contact_rec.email_address;
553 l_contact_number := trim(c_hz_contact_rec.Phone_Country_code||' '||c_hz_contact_rec.Phone_area_code||' '||c_hz_contact_rec.Phone_number);
554 ELSIF c_hz_contact_rec.contact_point_type = 'EMAIL' AND (l_contact_email IS NULL OR c_hz_contact_rec.primary_flag ='Y')
555 THEN
557 END IF;
558 END LOOP;
559
560 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
561 aso_debug_pub.add('GenerateQuoteHeader - l_contact_number - '|| l_contact_number, 1, 'Y');
562 aso_debug_pub.add('GenerateQuoteHeader - l_contact_email - '|| l_contact_email, 1, 'Y');
563 END IF;
564
565 /* Get all billing information */
566
567 l_bill_to_party_name := null;
568 l_bill_to_name := null;
569
570
571 /* Bill Customer Info. From Invoice_To_Cust_Account_ID */
572
573 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))
574 LOOP
575 l_bill_to_party_name := rtrim(c_hz_cust_acct_rec.party_name);
576 l_bill_to_name := upper(rtrim(c_hz_cust_acct_rec.person_first_name))||' '||upper(rtrim(c_hz_cust_acct_rec.person_last_name));
577 l_bill_contact_party_id := c_hz_cust_acct_rec.party_id;
578 END LOOP;
579
580 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
581 aso_debug_pub.add('GenerateQuoteHeader - bill_party_name - '||l_bill_to_party_name, 1, 'Y');
582 aso_debug_pub.add('GenerateQuoteHeader - bill_name - '||l_bill_to_name, 1, 'Y');
583 END IF;
584
585
586 l_bill_to_number := null;
587 l_bill_to_fax := null;
588
589 /* Bill Contact Info. From Invoice_To_Party_ID (PARTY_RELATIONSHIP) OR Bill Customer Party Id. */
590
591 IF g_quote_header_rec.invoice_to_party_id is NOT NULL
592 THEN
593 FOR c_hz_parties_rec IN c_hz_parties(g_quote_header_rec.invoice_to_party_id)
594 LOOP
595 IF c_hz_parties_rec.party_type = 'PARTY_RELATIONSHIP' THEN
596 l_bill_contact_party_id := g_quote_header_rec.party_id;
597 END IF;
598 END LOOP;
599 END IF;
600
601 FOR c_hz_contact_rec IN c_hz_contact_points(l_bill_contact_party_id)
602 LOOP
603 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')
604 THEN
605 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);
606 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')
607 THEN
608 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);
609 END IF;
610 END LOOP;
611
612 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
613 aso_debug_pub.add('GenerateQuoteHeader - bill_party_number - '||l_bill_to_number, 1, 'Y');
614 aso_debug_pub.add('GenerateQuoteHeader - bill_fax - '||l_bill_to_fax, 1, 'Y');
615 END IF;
616
617 l_bill_to_address := null;
618 l_bill_to_city := null;
619 l_bill_to_state := null;
620 l_bill_to_zip := null;
621 l_bill_to_country := null;
622
623 /* Bill to Location id using invoice_to_party_site_id */
624
625 FOR c_hz_locations_rec IN c_hz_locations(g_quote_header_rec.invoice_to_party_site_id)
626 LOOP
627
628 l_bill_to_address := c_hz_locations_rec.loc_address;
629 l_bill_to_city := c_hz_locations_rec.loc_city;
630 l_bill_to_state := c_hz_locations_rec.loc_state;
631 l_bill_to_zip := c_hz_locations_rec.loc_zip;
632 l_bill_to_country := c_hz_locations_rec.loc_country;
633
634 END LOOP;
635
636 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
637 aso_debug_pub.add('GenerateQuoteHeader - bill_address - '||l_bill_to_address, 1, 'Y');
638 aso_debug_pub.add('GenerateQuoteHeader - bill_city - '||l_bill_to_city, 1, 'Y');
639 aso_debug_pub.add('GenerateQuoteHeader - bill_state - '||l_bill_to_state, 1, 'Y');
640 aso_debug_pub.add('GenerateQuoteHeader - bill_zip - '||l_bill_to_zip, 1, 'Y');
641 aso_debug_pub.add('GenerateQuoteHeader - bill_country - '||l_bill_to_country, 1, 'Y');
642 END IF;
643
644 /* Get all shipping information */
645
646 l_ship_to_site_id := null;
647 l_ship_to_party_id := null;
648 l_ship_method_code := null;
649
650 FOR c_aso_shipments_rec IN c_aso_shipments(l_quote_id)
651 LOOP
652 l_ship_to_site_id := c_aso_shipments_rec.ship_to_party_site_id;
653 l_ship_to_cust_account_id := c_aso_shipments_rec.ship_to_cust_account_id;
654 l_ship_to_party_id := c_aso_shipments_rec.ship_to_party_id;
655 l_ship_method_code := c_aso_shipments_rec.ship_method_code;
656 END LOOP;
657
658 FOR c_ship_method_rec in c_ship_methods(l_ship_method_code)
659 LOOP
660 l_Ship_Method := c_ship_method_rec.Meaning;
661 END LOOP;
662
663 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
664 aso_debug_pub.add('GenerateQuoteHeader - ship_to_site_id - '||l_ship_to_site_id, 1, 'Y');
665 aso_debug_pub.add('GenerateQuoteHeader - ship_to_party_id - '||l_ship_to_party_id, 1, 'Y');
666 aso_debug_pub.add('GenerateQuoteHeader - ship_to_method - '||l_ship_method_code, 1, 'Y');
667 END IF;
668
672 LOOP
669 /* Shipping Customer Information - ship_to_cust_account_id */
670
671 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))
673 l_ship_to_party_name := rtrim(c_hz_cust_acct_rec.party_name);
674 l_ship_to_name := upper(rtrim(c_hz_cust_acct_rec.person_first_name))||' '||upper(rtrim(c_hz_cust_acct_rec.person_last_name));
675 l_ship_contact_party_id := c_hz_cust_acct_rec.party_id;
676 END LOOP;
677
678 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
679 aso_debug_pub.add('GenerateQuoteHeader - ship_to_party_name - '||l_ship_to_party_name, 1, 'Y');
680 aso_debug_pub.add('GenerateQuoteHeader - ship_to_name - '||l_ship_to_name, 1, 'Y');
681 END IF;
682
683 /* Shipping Contact ship_to_party_id(PARTY_REALTIONSHIP) OR Ship Customer's Party Id */
684 IF l_ship_to_party_id IS NOT NULL
685 THEN
686 FOR c_hz_parties_rec IN c_hz_parties(l_ship_to_party_id)
687 LOOP
688 IF c_hz_parties_rec.party_type = 'PARTY_RELATIONSHIP'
689 THEN
690 l_ship_contact_party_id := g_quote_header_rec.party_id;
691 END IF;
692 END LOOP;
693 END IF;
694
695 FOR c_hz_contact_rec IN c_hz_contact_points(l_ship_contact_party_id)
696 LOOP
697 IF (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'GEN')
698 AND (l_ship_to_number IS NULL OR c_hz_contact_rec.primary_flag ='Y')
699 THEN
700 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);
701 ELSIF (c_hz_contact_rec.contact_point_type = 'PHONE' AND c_hz_contact_rec.phone_line_type = 'FAX')
702 AND (l_ship_to_fax IS NULL OR c_hz_contact_rec.primary_flag ='Y')
703 THEN
704 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);
705 END IF;
706 END LOOP;
707
708 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
709 aso_debug_pub.add('GenerateQuoteHeader - ship_to_number - '||l_ship_to_number, 1, 'Y');
710 aso_debug_pub.add('GenerateQuoteHeader - ship_to_fax - '||l_ship_to_fax, 1, 'Y');
711 END IF;
712
713 l_ship_to_address := null;
714 l_ship_to_city := null;
715 l_ship_to_state := null;
716 l_ship_to_zip := null;
717 l_ship_to_country := null;
718
719 FOR c_hz_locations_rec IN c_hz_locations(l_ship_to_site_id)
720 LOOP
721
722 l_ship_to_address := c_hz_locations_rec.loc_address;
723 l_ship_to_city := c_hz_locations_rec.loc_city;
724 l_ship_to_state := c_hz_locations_rec.loc_state;
725 l_ship_to_zip := c_hz_locations_rec.loc_zip;
726 l_ship_to_country := c_hz_locations_rec.loc_country;
727
728 END LOOP;
729
730
731 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
732 aso_debug_pub.add('GenerateQuoteHeader - ship_address - '||l_ship_to_address, 1, 'Y');
733 aso_debug_pub.add('GenerateQuoteHeader - ship_city - '||l_ship_to_city, 1, 'Y');
734 aso_debug_pub.add('GenerateQuoteHeader - ship_state - '||l_ship_to_state, 1, 'Y');
735 aso_debug_pub.add('GenerateQuoteHeader - ship_zip - '||l_ship_to_zip, 1, 'Y');
736 aso_debug_pub.add('GenerateQuoteHeader - ship_country - '||l_ship_to_country, 1, 'Y');
737 aso_debug_pub.add('GenerateQuoteHeader - quote_header_id - '||g_quote_header_rec.quote_header_id, 1, 'Y');
738 END IF;
739
740 IF (display_type = 'text/plain' )
741 THEN
742 fnd_message.set_name('ASO','ASO_TMPL_QUOTE_NAME_COLON');
743 l_temp_str := null;
744 l_temp_str := fnd_message.get;
745 l_document := l_document || rpad(l_temp_str, 40, ' ')||g_quote_header_rec.quote_name||NEWLINE;
746
747 fnd_message.set_name('ASO','ASO_TMPL_QUOTE_NUMBER_COLON');
748 l_temp_str := null;
749 l_temp_str := fnd_message.get;
750 l_document := l_document || rpad(l_temp_str, 40, ' ')||g_quote_header_rec.quote_number||NEWLINE;
751
752 fnd_message.set_name('ASO','ASO_TMPL_SHIP_METH_COLON');
753 l_temp_str := null;
754 l_temp_str := fnd_message.get;
755 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_method||NEWLINE;
756
757 fnd_message.set_name('ASO','ASO_TMPL_CUST_CNTCT_INFO');
758 l_temp_str := null;
759 l_temp_str := fnd_message.get;
760 l_document := l_document || NEWLINE ||l_temp_str ||NEWLINE||NEWLINE;
761
762 fnd_message.set_name('ASO','ASO_TMPL_CNTCT_NAME_COLON');
763 l_temp_str := null;
764 l_temp_str := fnd_message.get;
765 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_contact_name||NEWLINE;
766
767 fnd_message.set_name('ASO','ASO_TMPL_CNTCT_PHONE_COLON');
768 l_temp_str := null;
769 l_temp_str := fnd_message.get;
770 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_contact_number||NEWLINE;
771
772 fnd_message.set_name('ASO','ASO_TMPL_CNTCT_EMAIL_COLON');
773 l_temp_str := null;
774 l_temp_str := fnd_message.get;
775 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_contact_email||NEWLINE;
776
777 fnd_message.set_name('ASO','ASO_TMPL_ORD_BILL_INFO');
778 l_temp_str := null;
779 l_temp_str := fnd_message.get;
780 l_document := l_document || NEWLINE ||l_temp_str ||NEWLINE||NEWLINE;
781
782 fnd_message.set_name('ASO','ASO_TMPL_CUST_NAME_COLON');
786
783 l_temp_str := null;
784 l_temp_str := fnd_message.get;
785 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_party_name||NEWLINE;
787 fnd_message.set_name('ASO','ASO_TMPL_ADDRESS_COLON');
788 l_temp_str := null;
789 l_temp_str := fnd_message.get;
790 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_address||NEWLINE;
791
792 fnd_message.set_name('ASO','ASO_TMPL_CITY_COLON');
793 l_temp_str := null;
794 l_temp_str := fnd_message.get;
795 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_city||NEWLINE;
796
797 fnd_message.set_name('ASO','ASO_TMPL_STATE_PRO_COLON');
798 l_temp_str := null;
799 l_temp_str := fnd_message.get;
800 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_state||NEWLINE;
801
802 fnd_message.set_name('ASO','ASO_TMPL_ZIP_POSTAL_COLON');
803 l_temp_str := null;
804 l_temp_str := fnd_message.get;
805 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_zip||NEWLINE;
806
807 fnd_message.set_name('ASO','ASO_TMPL_COUNTRY_COLON');
808 l_temp_str := null;
809 l_temp_str := fnd_message.get;
810 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_country||NEWLINE;
811
812 fnd_message.set_name('ASO','ASO_TMPL_CNTCT_NAME_COLON');
813 l_temp_str := null;
814 l_temp_str := fnd_message.get;
815 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_name||NEWLINE;
816
817 fnd_message.set_name('ASO','ASO_TMPL_TEL_NO_COLON');
818 l_temp_str := null;
819 l_temp_str := fnd_message.get;
820 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_number||NEWLINE;
821
822 fnd_message.set_name('ASO','ASO_TMPL_FAX_NO_COLON');
823 l_temp_str := null;
824 l_temp_str := fnd_message.get;
825 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_bill_to_fax||NEWLINE;
826
827 fnd_message.set_name('ASO','ASO_TMPL_ORD_SHIP_INFOR');
828 l_temp_str := null;
829 l_temp_str := fnd_message.get;
830 l_document := l_document || NEWLINE ||l_temp_str ||NEWLINE||NEWLINE;
831
832 fnd_message.set_name('ASO','ASO_TMPL_CUST_NAME_COLON');
833 l_temp_str := null;
834 l_temp_str := fnd_message.get;
835 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_party_name||NEWLINE;
836
837 fnd_message.set_name('ASO','ASO_TMPL_ADDRESS_COLON');
838 l_temp_str := null;
839 l_temp_str := fnd_message.get;
840 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_address||NEWLINE;
841
842 fnd_message.set_name('ASO','ASO_TMPL_CITY_COLON');
843 l_temp_str := null;
844 l_temp_str := fnd_message.get;
845 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_city||NEWLINE;
846
847 fnd_message.set_name('ASO','ASO_TMPL_STATE_PRO_COLON');
848 l_temp_str := null;
849 l_temp_str := fnd_message.get;
850 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_state||NEWLINE;
851
852 fnd_message.set_name('ASO','ASO_TMPL_COUNTRY_COLON');
853 l_temp_str := null;
854 l_temp_str := fnd_message.get;
855 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_country||NEWLINE;
856
857 fnd_message.set_name('ASO','ASO_TMPL_CNTCT_NAME_COLON');
858 l_temp_str := null;
859 l_temp_str := fnd_message.get;
860 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_name||NEWLINE;
861
862 fnd_message.set_name('ASO','ASO_TMPL_TEL_NO_COLON');
863 l_temp_str := null;
864 l_temp_str := fnd_message.get;
865 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_number||NEWLINE;
866
867 fnd_message.set_name('ASO','ASO_TMPL_FAX_NO_COLON');
868 l_temp_str := null;
869 l_temp_str := fnd_message.get;
870 l_document := l_document || rpad(l_temp_str, 40, ' ')||l_ship_to_fax||NEWLINE;
871
872 ELSE
873 null;
874 END IF;
875
876 END LOOP;
877 CLOSE c_quote_header;
878
879 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
880 aso_debug_pub.add('GenerateQuoteHeader - l_document'||NEWLINE|| l_document, 1, 'Y');
881 END IF;
882
883 document := l_document;
884 document_type := 'text/plain';
885
886 EXCEPTION
887 WHEN OTHERS THEN
888 IF c_quote_header%ISOPEN THEN
889 CLOSE c_quote_header;
890 END IF;
891 Raise;
892 END GenerateQuoteHeader;
893
894
895
896 PROCEDURE GenerateQuoteDetail(
897 document_id IN VARCHAR2,
898 display_type IN VARCHAR2,
899 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
900 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
901 )
902 IS
903
904 l_item_key wf_items.item_key%TYPE;
905 l_quote_id NUMBER;
906 l_event_type VARCHAR2(20);
907 l_document VARCHAR2(32000) := '';
908 l_description mtl_system_items_kfv.description%TYPE;
909 l_ship_flag VARCHAR2(1);
910 l_string_in VARCHAR2(250);
911 l_string_out VARCHAR2(250);
912 l_string_left VARCHAR2(250);
913
914
915 l_amt_format Varchar2(50);
916 -- hyang - bug fix 2870829
917 l_curr_sym Varchar2(12);
918
922 WHERE inventory_item_id = p_inv_item_id
919 Cursor c_ship_flag(p_inv_item_id NUMBER,p_org_id NUMBER) IS
920 SELECT shippable_item_flag, rtrim(description) Description
921 FROM mtl_system_items_kfv
923 AND organization_id = p_org_id;
924
925 Cursor c_quote_detail (p_quote_id NUMBER) IS
926 SELECT Inventory_item_id, Organization_id, Quantity, Line_quote_price,currency_code
927 FROM Aso_quote_lines_all
928 WHERE quote_header_id = p_quote_id
929 ORDER BY line_number;
930 l_quote_line_rec c_quote_detail%ROWTYPE;
931
932 BEGIN
933
934
935 l_item_key := document_id;
936
937 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
938 aso_debug_pub.add('GenerateQuoteDetail - l_item_key - '||l_item_key, 1, 'Y');
939 END IF;
940
941 l_quote_id := wf_engine.GetItemAttrText (
942 itemtype => g_ItemType,
943 itemkey => l_item_key,
944 aname => 'QUOTEID'
945 );
946 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
947 aso_debug_pub.add('GenerateQuoteDetail - l_quote_id - '||l_quote_id, 1, 'Y');
948 END IF;
949
950 l_event_type := wf_engine.GetItemAttrText (
951 itemtype => g_ItemType,
952 itemkey => l_item_key,
953 aname => 'EVENTTYPE'
954 );
955 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
956 aso_debug_pub.add('GenerateQuoteDetail - l_event_type - '|| l_event_type, 1, 'Y');
957 END IF;
958
959
960 FOR qte_hd_rec In c_quote_header(l_quote_id)
961 LOOP
962 l_amt_format := FND_CURRENCY.GET_FORMAT_MASK(qte_hd_rec.Currency_code,18);
963 FOR curr_sym_rec In c_curr_symbol(qte_hd_rec.Currency_code)
964 LOOP
965 l_curr_sym := trim(nvl(curr_sym_rec.symbol,' '));
966 END LOOP;
967 END LOOP;
968
969 OPEN c_quote_detail(l_quote_id);
970 LOOP
971 FETCH c_quote_detail INTO l_quote_line_rec;
972 EXIT WHEN c_quote_detail%NOTFOUND;
973
974 l_ship_flag := null;
975 l_description := null;
976
977 FOR c_ship_rec IN c_ship_flag(l_quote_line_rec.inventory_item_id,l_quote_line_rec.organization_id) LOOP
978
979 l_ship_flag := c_ship_rec.shippable_item_flag;
980 l_description := c_ship_rec.description;
981
982 END LOOP;
983
984 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
985 aso_debug_pub.add('GenerateQuoteDetail - l_ship_flag - '|| l_ship_flag, 1, 'Y');
986 aso_debug_pub.add('GenerateQuoteDetail - l_description - '|| l_description, 1, 'Y');
987 aso_debug_pub.add('GenerateQuoteDetail - quantity - '||l_quote_line_rec.quantity, 1, 'Y');
988 aso_debug_pub.add('GenerateQuoteDetail - price '||l_quote_line_rec.line_quote_price, 1, 'Y');
989 END IF;
990
991 l_string_in := l_description;
992 l_string_out := '';
993 l_string_left := '';
994 ParseThisString(l_string_in,l_string_out,l_string_left);
995
996 l_document := l_document || rpad(l_string_out, 36, ' ');
997 l_document := l_document || rpad(to_char(l_quote_line_rec.quantity), 15, ' ');
998 l_document := l_document || l_ship_flag;
999 l_document := l_document || lpad(l_curr_sym||to_char( (l_quote_line_rec.quantity*l_quote_line_rec.line_quote_price), l_amt_format),23,' ') ||NEWLINE;
1000
1001 l_string_in := l_string_left;
1002 WHILE length(l_string_in) > 0 LOOP
1003 l_string_out := '';
1004 l_string_left := '';
1005 ParseThisString(l_string_in,l_string_out,l_string_left);
1006 l_document := l_document || l_string_out ||NEWLINE;
1007 l_string_in := l_string_left;
1008 END LOOP;
1009 l_document := l_document || NEWLINE;
1010
1011 END LOOP;
1012 CLOSE c_quote_detail;
1013
1014 document := l_document;
1015 document_type := 'text/plain';
1016 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1017 aso_debug_pub.add('GenerateQuoteDetail - l_document - '||NEWLINE|| l_document, 1, 'Y');
1018 END IF;
1019
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 IF c_quote_detail%ISOPEN THEN
1023 CLOSE c_quote_detail;
1024 END IF;
1025 RAISE;
1026 END GenerateQuoteDetail;
1027
1028 PROCEDURE GenerateQuoteFooter(
1029 document_id IN VARCHAR2,
1030 display_type IN VARCHAR2,
1031 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1032 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1033 )
1034 IS
1035
1036 l_item_key wf_items.item_key%TYPE;
1037 l_quote_id NUMBER;
1038 l_event_type VARCHAR2(20);
1039 l_document VARCHAR2(32000) := '';
1040 l_temp_str VARCHAR2(2000):='';
1041 l_sub_total NUMBER;
1042
1043
1044 l_amt_format Varchar2(50);
1045 -- hyang - bug fix 2870829
1046 l_curr_sym Varchar2(12);
1047
1048
1049 BEGIN
1050
1051 l_item_key := document_id;
1052
1053 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1054 aso_debug_pub.add('GenerateQuoteFooter - l_item_key - '||l_item_key, 1, 'Y');
1055 END IF;
1056
1057 l_quote_id := wf_engine.GetItemAttrText (
1058 itemtype => g_ItemType,
1059 itemkey => l_item_key,
1060 aname => 'QUOTEID'
1061 );
1062 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1063 aso_debug_pub.add('GenerateQuoteFooter - l_quote_id - '||l_quote_id, 1, 'Y');
1064 END IF;
1065
1066 l_event_type := wf_engine.GetItemAttrText (
1067 itemtype => g_ItemType,
1068 itemkey => l_item_key,
1069 aname => 'EVENTTYPE'
1070 );
1071 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1072 aso_debug_pub.add('GenerateQuoteFooter - l_event_type - '|| l_event_type, 1, 'Y');
1073 END IF;
1074
1075 OPEN c_quote_header(l_quote_id);
1076 LOOP
1077 FETCH c_quote_header INTO g_quote_header_rec;
1078 EXIT WHEN c_quote_header%NOTFOUND;
1079
1080 l_amt_format := FND_CURRENCY.GET_FORMAT_MASK( g_quote_header_rec.Currency_code,22);
1081
1082 FOR curr_sym_rec In c_curr_symbol(g_quote_header_rec.Currency_code)
1083 LOOP
1084 l_curr_sym := trim(curr_sym_rec.symbol);
1085 END LOOP;
1086
1087 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1088 aso_debug_pub.add('GenerateQuoteFooter - shipping - '||to_char(g_quote_header_rec.total_shipping_charge), 1, 'Y');
1089 aso_debug_pub.add('GenerateQuoteFooter - tax - '||to_char(g_quote_header_rec.total_tax), 1, 'Y');
1090 aso_debug_pub.add('GenerateQuoteFooter - total quote price - '||to_char(g_quote_header_rec.total_quote_price), 1, 'Y');
1091 END IF;
1092
1093 IF (display_type = 'text/plain' ) THEN
1094
1095
1096 fnd_message.set_name('ASO','ASO_TMPL_SHIP_HAND_COLON');
1097 l_temp_str := null;
1098 l_temp_str := fnd_message.get;
1099
1100 l_document := l_document || lpad(l_temp_str,54,' ')|| ' '|| lpad(l_curr_sym||to_char(NVL(g_quote_header_rec.total_shipping_charge, 0), l_amt_format), 20, ' ')|| NEWLINE;
1101
1102
1103 fnd_message.set_name('ASO','ASO_TMPL_TAX_COLON');
1104 l_temp_str := null;
1105 l_temp_str := fnd_message.get;
1106
1107 l_document := l_document || lpad(l_temp_str,54,' ')|| ' '|| lpad(l_curr_sym||to_char(NVL(g_quote_header_rec.total_tax, 0),l_amt_format),20,' ')|| NEWLINE;
1108
1109 fnd_message.set_name('ASO','ASO_TMPL_TOTAL_COLON');
1110 l_temp_str := null;
1111 l_temp_str := fnd_message.get;
1112
1113 l_document := l_document || lpad(l_temp_str,54,' ')|| ' '|| lpad(l_curr_sym|| to_char(NVL(g_quote_header_rec.total_quote_price, 0),l_amt_format),20,' ');
1114
1115 ELSE
1116 null;
1117 END IF;
1118 END LOOP;
1119 CLOSE c_quote_header;
1120
1121 document := l_document;
1122 document_type := 'text/plain';
1123 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1124 aso_debug_pub.add('GenerateQuoteFooter - l_document - '||NEWLINE|| l_document, 1, 'Y');
1125 END IF;
1126
1127 EXCEPTION
1128 When Others Then
1129 IF c_quote_header%ISOPEN THEN
1130 CLOSE c_quote_header;
1131 END IF;
1132 Raise;
1133 END GenerateQuoteFooter;
1134
1135
1136 PROCEDURE GetContractRef(
1137 document_id IN VARCHAR2,
1138 display_type IN VARCHAR2,
1139 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1140 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1141 )
1142 IS
1143
1144 l_contract_number varchar2(120);
1145 l_contract_modifier varchar2(120);
1146 l_contract_ref varchar2(245);
1147
1148 CURSOR c_contract_header(p_id number) IS
1149 Select Contract_number,Contract_number_modifier
1150 From okc_k_headers_b
1151 Where ID = p_id;
1152
1153 BEGIN
1154
1155 FOR c_contract_rec In c_contract_header(to_number(document_id)) LOOP
1156 l_contract_number := c_contract_rec.contract_number;
1157 l_contract_modifier := c_contract_rec.contract_number_modifier;
1158 l_contract_ref := l_contract_number||' '||l_contract_modifier;
1159 END LOOP;
1160
1161 document := l_contract_ref;
1162 document_type := 'text/plain';
1163
1164 END GetContractRef;
1165
1166 PROCEDURE GetCartName(
1167 document_id IN VARCHAR2,
1168 display_type IN VARCHAR2,
1169 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1170 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1171 )
1172 IS
1173
1174 l_cart_name varchar2(50);
1175
1176 BEGIN
1177
1178 FOR c_quote_rec In c_quote_header(to_number(document_id)) LOOP
1179 l_cart_name := c_quote_rec.quote_name;
1180 END LOOP;
1181
1182 document := l_cart_name;
1183 document_type := 'text/plain';
1184
1185 END GetCartName;
1186
1187 END ASO_WORKFLOW_QUOTE_PVT;