[Home] [Help]
PACKAGE BODY: APPS.IBE_QUOTE_MISC_PVT
Source
1 PACKAGE BODY IBE_Quote_Misc_pvt AS
2 /* $Header: IBEVQMIB.pls 120.15 2007/10/10 07:25:29 scnagara ship $ */
3
4
5 l_true VARCHAR2(1) := FND_API.G_TRUE;
6
7 FUNCTION get_multi_svc_profile return varchar2 is
8 BEGIN
9 IF(FND_PROFILE.Value('IBE_ENABLE_MULT_SVC') = 'Y') THEN
10 return FND_API.G_TRUE;
11 ELSE
12 return FND_API.G_FALSE;
13 END IF;
14 END;
15
16 FUNCTION is_quote_usable(
17 p_quote_header_id IN NUMBER,
18 p_party_id IN NUMBER,
19 p_cust_account_id IN NUMBER) return varchar2 is
20 CURSOR c_get_quote_details(c_quote_header_id NUMBER) is
21 select quote_expiration_date
22 from aso_quote_headers_all
23 where quote_header_id = c_quote_header_id;
24
25 CURSOR c_find_active_cart(c_quote_header_id NUMBER,
26 c_party_id NUMBER,
27 c_cust_account_id NUMBER) is
28 select quote_header_id
29 from ibe_active_quotes
30 where quote_header_id = c_quote_header_id
31 and party_id = c_party_id
32 and cust_account_id = c_cust_account_id
33 and record_type = 'CART';
34
35
36 l_api_name CONSTANT VARCHAR2(30) := 'is_quote_usable';
37 l_expiration_date date;
38 l_active_cart number;
39 l_return_status VARCHAR2(1);
40 l_msg_count NUMBER ;
41 l_msg_data VARCHAR2(2000);
42 rec_get_quote_details c_get_quote_details%rowtype;
43 rec_find_active_cart c_find_active_cart%rowtype;
44 BEGIN
45 SAVEPOINT is_quote_usable;
46 for rec_get_quote_details in c_get_quote_details(p_quote_header_id) LOOP
47 l_expiration_date := rec_get_quote_details.quote_expiration_date;
48 exit when c_get_quote_details%notfound;
49 END LOOP;
50
51 IF (nvl(trunc(l_expiration_date), trunc(sysdate)+1) < trunc(sysdate)) THEN
52 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
53
54 IBE_Util.Debug('is_quote_usable: Quote: '||p_quote_header_id||' has expired');
55 IBE_Util.Debug('Expiration date for the quote is: '||to_char(l_expiration_date,'mm-dd-yyyy:hh24:mi:ss'));
56 END IF;
57 FOR rec_find_active_cart in c_find_active_cart(p_quote_header_id,
58 p_party_id,
59 p_cust_account_id) LOOP
60 l_active_cart := rec_find_active_cart.quote_header_id;
61 IF (l_active_cart is not null) THEN
62 IBE_QUOTE_SAVESHARE_V2_PVT.DEACTIVATE_QUOTE (
63 P_Quote_header_id => p_quote_header_id,
64 P_Party_id => p_party_id ,
65 P_Cust_account_id => p_Cust_account_id ,
66 p_api_version => 1 ,
67 p_init_msg_list => fnd_api.g_false ,
68 p_commit => fnd_api.g_false ,
69 x_return_status => l_return_status ,
70 x_msg_count => l_msg_count ,
71 x_msg_data => l_msg_data );
72
73 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
74 RAISE FND_API.G_EXC_ERROR;
75 END IF;
76
77 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80 END IF;
81 EXIT when c_find_active_cart%notfound;
82 END LOOP;
83 return FND_API.G_FALSE;
84 ELSE
85 return FND_API.G_TRUE;
86 END IF;
87
88 EXCEPTION
89 WHEN FND_API.G_EXC_ERROR THEN
90 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
91 IBE_Util.Debug('Expected exception in IBE_QUOTE_MISC_PVT.Is_quote_usable');
92 END IF;
93
94 ROLLBACK TO is_quote_usable;
95 l_return_status := FND_API.G_RET_STS_ERROR;
96 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
97 p_count => l_msg_count,
98 p_data => l_msg_data);
99 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
100 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
101 IBE_Util.Debug('Unexpected exception in IBE_QUOTE_MISC_PVT.Is_quote_usable');
102 END IF;
103 ROLLBACK TO is_quote_usable;
104 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
106 p_count => l_msg_count,
107 p_data => l_msg_data);
108 WHEN OTHERS THEN
109 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
110 IBE_Util.Debug('Unknown exception in IBE_QUOTE_MISC_PVT.Is_quote_usable');
111 END IF;
112 ROLLBACK TO is_quote_usable;
113 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
115 THEN
116 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
117 l_api_name);
118 END IF;
119 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
120 p_count => l_msg_count,
121 p_data => l_msg_data);
122
123 END ;
124
125 -- Start of comments
126 -- API name : Get_Active_Quote_ID
127 -- Type : Private
128 -- Function :
129 -- Parameters :
130 -- Version : Current version 1.0
131 -- Notes :
132 --
133 -- End of comments
134
135 FUNCTION Get_Active_Quote_ID
136 (
137 p_party_id IN NUMBER,
138 p_cust_account_id IN NUMBER
139 ) RETURN NUMBER
140 IS
141 l_quote_header_id NUMBER := NULL;
142 l_is_quote_usable VARCHAR2(1) ;
143 BEGIN
144 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
145 IBE_Util.Debug(' Querying active quote id for partyid: ' || p_party_id || ' and acctid: ' || p_cust_account_id);
146 END IF;
147
148 --MANNAMRA:09/22/02: Changed this query to get the active quote id from
149 --active_quotes table(single source of truth for active quotes)
150
151 SELECT aq.quote_header_id
152 INTO l_quote_header_id
153 FROM IBE_ACTIVE_QUOTES AQ
154 where party_id = p_party_id
155 and cust_account_id = p_cust_account_id
156 and record_type = 'CART';
157
158 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
159 IBE_Util.Debug(' Querying found: ' || l_quote_header_id);
160 END IF;
161
162 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
163 IBE_Util.Debug('get_active_qute_id: checking to see the usability of above quote');
164 END IF;
165
166 IF(l_quote_header_id is not null) THEN
167 l_is_quote_usable := is_quote_usable(l_quote_header_id,
168 p_party_id,
169 p_cust_account_id);
170 IF (l_is_quote_usable =FND_API.G_TRUE) THEN
171 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
172 IBE_Util.Debug('get_active_qute_id:l_quote_usable is true');
173 END IF;
174 RETURN l_quote_header_id;
175 END IF;
176 END IF;
177 return 0;
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 RETURN 0;
182 WHEN TOO_MANY_ROWS THEN
183 RETURN -1;
184 END Get_Active_Quote_ID;
185
186
187 -- Start of comments
188 -- API name : Get_Number_Of_Lines
189 -- Type : Private.
190 -- Function :
191 -- Parameters :
192 -- Version : Current version 1.0
193 -- Notes :
194 --
195 -- End of comments
196 PROCEDURE Get_Number_Of_Lines
197 (
198 p_party_id IN NUMBER,
199 p_cust_account_id IN NUMBER,
200 x_number_of_lines OUT NOCOPY NUMBER
201 )
202 IS
203 l_quote_header_id NUMBER := NULL;
204 BEGIN
205 l_quote_header_id := Get_Active_Quote_ID(p_party_id, p_cust_account_id);
206
207 IF l_quote_header_id = 0
208 OR l_quote_header_id = -1 THEN
209 x_number_of_lines := -1;
210 ELSE
211 SELECT COUNT(*)
212 INTO x_number_of_lines
213 FROM aso_quote_lines
214 WHERE quote_header_id = l_quote_header_id;
215 END IF;
216 END Get_Number_Of_Lines;
217
218 -- wli
219
220 FUNCTION get_Quote_Status(
221 p_quote_header_id IN NUMBER
222 ) RETURN VARCHAR2
223 IS
224 l_quote_header_id NUMBER;
225 l_order_id NUMBER;
226 BEGIN
227
228 SELECT quote_header_id, order_id INTO l_quote_header_id, l_order_id
229 FROM aso_quote_headers
230 WHERE quote_header_id = p_quote_header_id;
231
232 IF (l_order_id IS NULL) THEN
233 RETURN 'NOT_ORDERED';
234 ELSE
235 RETURN 'ORDERED';
236 END IF;
237
238 EXCEPTION
239 WHEN NO_DATA_FOUND THEN
240 RETURN 'NOT_EXIST';
241 END get_quote_status;
242
243
244 FUNCTION getLineIndexFromLineId(
245 p_quote_line_id IN NUMBER
246 ,p_qte_line_tbl IN aso_quote_pub.qte_line_tbl_type
247 ) RETURN NUMBER
248 IS
249 BEGIN
250
251 FOR i IN 1..p_qte_line_tbl.count LOOP
252 IF p_quote_line_id = p_qte_line_tbl(i).quote_line_id then
253 RETURN i;
254 END IF;
255
256
257 END LOOP;
258
259 RETURN FND_API.G_MISS_NUM;
260 END getLineIndexFromLineId;
261
262
263 FUNCTION getQuoteLastUpdateDate(
264 p_quote_header_id IN NUMBER
265 ) RETURN DATE
266 IS
267
268 CURSOR c_getLastUpdateDate(c_qte_header_id NUMBER) IS
269 SELECT last_update_date FROM ASO_QUOTE_HEADERS
270 WHERE quote_header_id = c_qte_header_id;
271 l_last_update_date date := FND_API.G_MISS_DATE;
272 BEGIN
273
274 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
275 IBE_UTIL.Debug('getQuoteLastUpdateDate: starts');
276 END IF;
277
278 OPEN c_getLastUpdateDate(p_quote_header_id);
279 FETCH c_getLastUpdateDate into l_last_update_date;
280 CLOSE c_getLastUpdateDate;
281
282 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
283 IBE_UTIL.Debug('getQuoteLastUpdateDate: ends');
284 END IF;
285
286 RETURN l_last_update_date;
287 END getQuoteLastUpdateDate;
288
289
290 FUNCTION getLinePrcAttrTbl(
291 p_quote_line_id IN NUMBER
292 ) RETURN ASO_QUOTE_PUB.PRICE_ATTRIBUTES_TBL_TYPE
293 IS
294 l_ln_price_attributes_rec ASO_QUOTE_PUB.PRICE_ATTRIBUTES_REC_Type;
295 l_ln_price_attributes_tbl ASO_QUOTE_PUB.PRICE_ATTRIBUTES_Tbl_Type
296 := ASO_QUOTE_PUB.g_miss_PRICE_ATTRIBUTES_Tbl;
297
298 CURSOR c_getlnprcattrtbl(p_quote_line_id number) is
299 SELECT APA.price_attribute_id
300 ,APA.creation_date
301 ,APA.created_by
302 ,APA.last_update_date
303 ,APA.last_updated_by
304 ,APA.last_update_login
305 ,APA.request_id
306 ,APA.program_application_id
307 ,APA.program_id
308 ,APA.program_update_date
309 ,APA.quote_header_id
310 ,APA.quote_line_id
311 ,APA.flex_title
312 ,APA.pricing_context
313 ,APA.pricing_attribute1
314 ,APA.pricing_attribute2
315 ,APA.pricing_attribute3
316 ,APA.pricing_attribute4
317 ,APA.pricing_attribute5
318 ,APA.pricing_attribute6
319 ,APA.pricing_attribute7
320 ,APA.pricing_attribute8
321 ,APA.pricing_attribute9
322 ,APA.pricing_attribute10
323 ,APA.pricing_attribute11
324 ,APA.pricing_attribute12
325 ,APA.pricing_attribute13
326 ,APA.pricing_attribute14
327 ,APA.pricing_attribute15
328 ,APA.pricing_attribute16
329 ,APA.pricing_attribute17
330 ,APA.pricing_attribute18
331 ,APA.pricing_attribute19
332 ,APA.pricing_attribute20
333 ,APA.pricing_attribute21
334 ,APA.pricing_attribute22
335 ,APA.pricing_attribute23
336 ,APA.pricing_attribute24
337 ,APA.pricing_attribute25
338 ,APA.pricing_attribute26
339 ,APA.pricing_attribute27
340 ,APA.pricing_attribute28
341 ,APA.pricing_attribute29
342 ,APA.pricing_attribute30
343 ,APA.pricing_attribute31
344 ,APA.pricing_attribute32
345 ,APA.pricing_attribute33
346 ,APA.pricing_attribute34
347 ,APA.pricing_attribute35
348 ,APA.pricing_attribute36
349 ,APA.pricing_attribute37
350 ,APA.pricing_attribute38
351 ,APA.pricing_attribute39
352 ,APA.pricing_attribute40
353 ,APA.pricing_attribute41
354 ,APA.pricing_attribute42
355 ,APA.pricing_attribute43
356 ,APA.pricing_attribute44
357 ,APA.pricing_attribute45
358 ,APA.pricing_attribute46
359 ,APA.pricing_attribute47
360 ,APA.pricing_attribute48
361 ,APA.pricing_attribute49
362 ,APA.pricing_attribute50
363 ,APA.pricing_attribute51
364 ,APA.pricing_attribute52
365 ,APA.pricing_attribute53
366 ,APA.pricing_attribute54
367 ,APA.pricing_attribute55
368 ,APA.pricing_attribute56
369 ,APA.pricing_attribute57
370 ,APA.pricing_attribute58
371 ,APA.pricing_attribute59
372 ,APA.pricing_attribute50
373 ,APA.pricing_attribute51
374 ,APA.pricing_attribute52
375 ,APA.pricing_attribute53
376 ,APA.pricing_attribute54
377 ,APA.pricing_attribute55
378 ,APA.pricing_attribute56
379 ,APA.pricing_attribute57
380 ,APA.pricing_attribute58
381 ,APA.pricing_attribute59
382 ,APA.pricing_attribute60
383 ,APA.pricing_attribute61
384 ,APA.pricing_attribute62
385 ,APA.pricing_attribute63
386 ,APA.pricing_attribute64
387 ,APA.pricing_attribute65
388 ,APA.pricing_attribute66
389 ,APA.pricing_attribute67
390 ,APA.pricing_attribute68
391 ,APA.pricing_attribute69
392 ,APA.pricing_attribute70
393 ,APA.pricing_attribute71
394 ,APA.pricing_attribute72
395 ,APA.pricing_attribute73
396 ,APA.pricing_attribute74
397 ,APA.pricing_attribute75
398 ,APA.pricing_attribute76
399 ,APA.pricing_attribute77
400 ,APA.pricing_attribute78
401 ,APA.pricing_attribute79
402 ,APA.pricing_attribute80
403 ,APA.pricing_attribute81
404 ,APA.pricing_attribute82
405 ,APA.pricing_attribute83
406 ,APA.pricing_attribute84
407 ,APA.pricing_attribute85
408 ,APA.pricing_attribute86
409 ,APA.pricing_attribute87
410 ,APA.pricing_attribute88
411 ,APA.pricing_attribute89
412 ,APA.pricing_attribute90
413 ,APA.pricing_attribute91
414 ,APA.pricing_attribute92
415 ,APA.pricing_attribute93
416 ,APA.pricing_attribute94
417 ,APA.pricing_attribute95
418 ,APA.pricing_attribute96
419 ,APA.pricing_attribute97
420 ,APA.pricing_attribute98
421 ,APA.pricing_attribute99
422 ,APA.pricing_attribute100
423 ,APA.context
424 ,APA.attribute1
425 ,APA.attribute2
426 ,APA.attribute3
427 ,APA.attribute4
428 ,APA.attribute5
429 ,APA.attribute6
430 ,APA.attribute7
431 ,APA.attribute8
432 ,APA.attribute9
433 ,APA.attribute10
434 ,APA.attribute11
435 ,APA.attribute12
436 ,APA.attribute13
437 ,APA.attribute14
438 ,APA.attribute15
439 FROM aso_price_attributes APA
440 WHERE APA.quote_line_id = p_quote_line_id;
441
442 BEGIN
443 OPEN c_getlnprcattrtbl(p_quote_line_id);
444 LOOP
445 FETCH c_getlnprcattrtbl
446 INTO l_ln_price_attributes_rec.price_attribute_id
447 ,l_ln_price_attributes_rec.creation_date
448 ,l_ln_price_attributes_rec.created_by
449 ,l_ln_price_attributes_rec.last_update_date
450 ,l_ln_price_attributes_rec.last_updated_by
451 ,l_ln_price_attributes_rec.last_update_login
452 ,l_ln_price_attributes_rec.request_id
453 ,l_ln_price_attributes_rec.program_application_id
454 ,l_ln_price_attributes_rec.program_id
455 ,l_ln_price_attributes_rec.program_update_date
456 ,l_ln_price_attributes_rec.quote_header_id
457 ,l_ln_price_attributes_rec.quote_line_id
458 ,l_ln_price_attributes_rec.flex_title
459 ,l_ln_price_attributes_rec.pricing_context
460 ,l_ln_price_attributes_rec.pricing_attribute1
461 ,l_ln_price_attributes_rec.pricing_attribute2
462 ,l_ln_price_attributes_rec.pricing_attribute3
463 ,l_ln_price_attributes_rec.pricing_attribute4
464 ,l_ln_price_attributes_rec.pricing_attribute5
465 ,l_ln_price_attributes_rec.pricing_attribute6
466 ,l_ln_price_attributes_rec.pricing_attribute7
467 ,l_ln_price_attributes_rec.pricing_attribute8
468 ,l_ln_price_attributes_rec.pricing_attribute9
469 ,l_ln_price_attributes_rec.pricing_attribute10
470 ,l_ln_price_attributes_rec.pricing_attribute11
471 ,l_ln_price_attributes_rec.pricing_attribute12
472 ,l_ln_price_attributes_rec.pricing_attribute13
473 ,l_ln_price_attributes_rec.pricing_attribute14
474 ,l_ln_price_attributes_rec.pricing_attribute15
475 ,l_ln_price_attributes_rec.pricing_attribute16
476 ,l_ln_price_attributes_rec.pricing_attribute17
477 ,l_ln_price_attributes_rec.pricing_attribute18
478 ,l_ln_price_attributes_rec.pricing_attribute19
479 ,l_ln_price_attributes_rec.pricing_attribute20
480 ,l_ln_price_attributes_rec.pricing_attribute21
481 ,l_ln_price_attributes_rec.pricing_attribute22
482 ,l_ln_price_attributes_rec.pricing_attribute23
483 ,l_ln_price_attributes_rec.pricing_attribute24
484 ,l_ln_price_attributes_rec.pricing_attribute25
485 ,l_ln_price_attributes_rec.pricing_attribute26
486 ,l_ln_price_attributes_rec.pricing_attribute27
487 ,l_ln_price_attributes_rec.pricing_attribute28
488 ,l_ln_price_attributes_rec.pricing_attribute29
489 ,l_ln_price_attributes_rec.pricing_attribute30
490 ,l_ln_price_attributes_rec.pricing_attribute31
491 ,l_ln_price_attributes_rec.pricing_attribute32
492 ,l_ln_price_attributes_rec.pricing_attribute33
493 ,l_ln_price_attributes_rec.pricing_attribute34
494 ,l_ln_price_attributes_rec.pricing_attribute35
495 ,l_ln_price_attributes_rec.pricing_attribute36
496 ,l_ln_price_attributes_rec.pricing_attribute37
497 ,l_ln_price_attributes_rec.pricing_attribute38
498 ,l_ln_price_attributes_rec.pricing_attribute39
499 ,l_ln_price_attributes_rec.pricing_attribute40
500 ,l_ln_price_attributes_rec.pricing_attribute41
501 ,l_ln_price_attributes_rec.pricing_attribute42
502 ,l_ln_price_attributes_rec.pricing_attribute43
503 ,l_ln_price_attributes_rec.pricing_attribute44
504 ,l_ln_price_attributes_rec.pricing_attribute45
505 ,l_ln_price_attributes_rec.pricing_attribute46
506 ,l_ln_price_attributes_rec.pricing_attribute47
507 ,l_ln_price_attributes_rec.pricing_attribute48
508 ,l_ln_price_attributes_rec.pricing_attribute49
509 ,l_ln_price_attributes_rec.pricing_attribute50
510 ,l_ln_price_attributes_rec.pricing_attribute51
511 ,l_ln_price_attributes_rec.pricing_attribute52
512 ,l_ln_price_attributes_rec.pricing_attribute53
513 ,l_ln_price_attributes_rec.pricing_attribute54
514 ,l_ln_price_attributes_rec.pricing_attribute55
515 ,l_ln_price_attributes_rec.pricing_attribute56
516 ,l_ln_price_attributes_rec.pricing_attribute57
517 ,l_ln_price_attributes_rec.pricing_attribute58
518 ,l_ln_price_attributes_rec.pricing_attribute59
519 ,l_ln_price_attributes_rec.pricing_attribute50
520 ,l_ln_price_attributes_rec.pricing_attribute51
521 ,l_ln_price_attributes_rec.pricing_attribute52
522 ,l_ln_price_attributes_rec.pricing_attribute53
523 ,l_ln_price_attributes_rec.pricing_attribute54
524 ,l_ln_price_attributes_rec.pricing_attribute55
525 ,l_ln_price_attributes_rec.pricing_attribute56
526 ,l_ln_price_attributes_rec.pricing_attribute57
527 ,l_ln_price_attributes_rec.pricing_attribute58
528 ,l_ln_price_attributes_rec.pricing_attribute59
529 ,l_ln_price_attributes_rec.pricing_attribute60
530 ,l_ln_price_attributes_rec.pricing_attribute61
531 ,l_ln_price_attributes_rec.pricing_attribute62
532 ,l_ln_price_attributes_rec.pricing_attribute63
533 ,l_ln_price_attributes_rec.pricing_attribute64
534 ,l_ln_price_attributes_rec.pricing_attribute65
535 ,l_ln_price_attributes_rec.pricing_attribute66
536 ,l_ln_price_attributes_rec.pricing_attribute67
537 ,l_ln_price_attributes_rec.pricing_attribute68
538 ,l_ln_price_attributes_rec.pricing_attribute69
539 ,l_ln_price_attributes_rec.pricing_attribute70
540 ,l_ln_price_attributes_rec.pricing_attribute71
541 ,l_ln_price_attributes_rec.pricing_attribute72
542 ,l_ln_price_attributes_rec.pricing_attribute73
543 ,l_ln_price_attributes_rec.pricing_attribute74
544 ,l_ln_price_attributes_rec.pricing_attribute75
545 ,l_ln_price_attributes_rec.pricing_attribute76
546 ,l_ln_price_attributes_rec.pricing_attribute77
547 ,l_ln_price_attributes_rec.pricing_attribute78
548 ,l_ln_price_attributes_rec.pricing_attribute79
549 ,l_ln_price_attributes_rec.pricing_attribute80
550 ,l_ln_price_attributes_rec.pricing_attribute81
551 ,l_ln_price_attributes_rec.pricing_attribute82
552 ,l_ln_price_attributes_rec.pricing_attribute83
553 ,l_ln_price_attributes_rec.pricing_attribute84
554 ,l_ln_price_attributes_rec.pricing_attribute85
555 ,l_ln_price_attributes_rec.pricing_attribute86
556 ,l_ln_price_attributes_rec.pricing_attribute87
557 ,l_ln_price_attributes_rec.pricing_attribute88
558 ,l_ln_price_attributes_rec.pricing_attribute89
559 ,l_ln_price_attributes_rec.pricing_attribute90
560 ,l_ln_price_attributes_rec.pricing_attribute91
561 ,l_ln_price_attributes_rec.pricing_attribute92
562 ,l_ln_price_attributes_rec.pricing_attribute93
563 ,l_ln_price_attributes_rec.pricing_attribute94
564 ,l_ln_price_attributes_rec.pricing_attribute95
565 ,l_ln_price_attributes_rec.pricing_attribute96
566 ,l_ln_price_attributes_rec.pricing_attribute97
567 ,l_ln_price_attributes_rec.pricing_attribute98
568 ,l_ln_price_attributes_rec.pricing_attribute99
569 ,l_ln_price_attributes_rec.pricing_attribute100
570 ,l_ln_price_attributes_rec.context
571 ,l_ln_price_attributes_rec.attribute1
572 ,l_ln_price_attributes_rec.attribute2
573 ,l_ln_price_attributes_rec.attribute3
574 ,l_ln_price_attributes_rec.attribute4
575 ,l_ln_price_attributes_rec.attribute5
576 ,l_ln_price_attributes_rec.attribute6
577 ,l_ln_price_attributes_rec.attribute7
578 ,l_ln_price_attributes_rec.attribute8
579 ,l_ln_price_attributes_rec.attribute9
580 ,l_ln_price_attributes_rec.attribute10
581 ,l_ln_price_attributes_rec.attribute11
582 ,l_ln_price_attributes_rec.attribute12
583 ,l_ln_price_attributes_rec.attribute13
584 ,l_ln_price_attributes_rec.attribute14
585 ,l_ln_price_attributes_rec.attribute15
586 ;
587 EXIT WHEN c_getlnprcattrtbl%NOTFOUND;
588 l_ln_price_attributes_tbl(l_ln_price_attributes_tbl.count+1)
589 :=l_ln_price_attributes_rec;
590 END LOOP;
591 CLOSE c_getlnprcattrtbl;
592 RETURN l_ln_price_attributes_tbl;
593 END GETLINEPRCATTRTBL;
594
595 FUNCTION getLineAttrExtTbl(
596 p_quote_line_id IN NUMBER
597 ) RETURN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
598 IS
599
600 l_line_attr_ext_tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
601 := ASO_QUOTE_PUB.g_miss_Line_Attribs_Ext_Tbl;
602
603 l_line_attr_ext_rec ASO_QUOTE_PUB.Line_Attribs_Ext_rec_Type;
604
605 CURSOR c_getLineAttrExtTbl(p_quote_line_id number) is
606 SELECT lae.LINE_ATTRIBUTE_ID
607 ,lae.CREATION_DATE
608 ,lae.CREATED_BY
609 ,lae.LAST_UPDATE_DATE
610 ,lae.LAST_UPDATED_BY
611 ,lae.LAST_UPDATE_LOGIN
612 ,lae.REQUEST_ID
613 ,lae.PROGRAM_APPLICATION_ID
614 ,lae.PROGRAM_ID
615 ,lae.PROGRAM_UPDATE_DATE
616 ,lae.APPLICATION_ID
617 ,lae.QUOTE_LINE_ID
618 ,lae.ATTRIBUTE_TYPE_CODE
619 ,lae.NAME
620 ,lae.VALUE
621 ,lae.VALUE_TYPE
622 ,lae.STATUS
623 ,lae.START_DATE_ACTIVE
624 ,lae.END_DATE_ACTIVE
625 -- ,lae.QUOTE_HEADER_ID
626 -- ,lae.QUOTE_SHIPMENT_ID
627 -- ,lae.SECURITY_GROUP_ID
628 -- ,lae.OBJECT_VERSION_NUMBER
629 From ASO_QUOTE_LINE_ATTRIBS_EXT lae
630 Where lae.QUOTE_LINE_ID = p_quote_line_id;
631
632 BEGIN
633 OPEN c_getLineAttrExtTbl(p_quote_line_id);
634 LOOP
635 FETCH c_getLineAttrExtTbl into
636 l_line_attr_ext_rec.LINE_ATTRIBUTE_ID
637 ,l_line_attr_ext_rec.CREATION_DATE
638 ,l_line_attr_ext_rec.CREATED_BY
639 ,l_line_attr_ext_rec.LAST_UPDATE_DATE
640 ,l_line_attr_ext_rec.LAST_UPDATED_BY
641 ,l_line_attr_ext_rec.LAST_UPDATE_LOGIN
642 ,l_line_attr_ext_rec.REQUEST_ID
643 ,l_line_attr_ext_rec.PROGRAM_APPLICATION_ID
644 ,l_line_attr_ext_rec.PROGRAM_ID
645 ,l_line_attr_ext_rec.PROGRAM_UPDATE_DATE
646 ,l_line_attr_ext_rec.APPLICATION_ID
647 ,l_line_attr_ext_rec.QUOTE_LINE_ID
648 ,l_line_attr_ext_rec.ATTRIBUTE_TYPE_CODE
649 ,l_line_attr_ext_rec.NAME
650 ,l_line_attr_ext_rec.VALUE
651 ,l_line_attr_ext_rec.VALUE_TYPE
652 ,l_line_attr_ext_rec.STATUS
653 ,l_line_attr_ext_rec.START_DATE_ACTIVE
654 ,l_line_attr_ext_rec.END_DATE_ACTIVE
655 -- ,l_line_attr_ext_rec.QUOTE_HEADER_ID
656 -- ,l_line_attr_ext_rec.QUOTE_SHIPMENT_ID
657 -- ,l_line_attr_ext_rec.SECURITY_GROUP_ID
658 -- ,l_line_attr_ext_rec.OBJECT_VERSION_NUMBER
659 ;
660 EXIT WHEN c_getLineAttrExtTbl%NOTFOUND;
661 l_line_attr_ext_tbl(l_line_attr_ext_tbl.count+1) := l_line_attr_ext_rec;
662 END LOOP;
663 CLOSE c_getLineAttrExtTbl;
664 RETURN l_line_attr_ext_tbl;
665 END getLineAttrExtTbl;
666
667 FUNCTION getLineDetailTbl(
668 p_quote_line_id IN NUMBER
669 ) RETURN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
670 IS
671 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
672 := ASO_QUOTE_PUB.g_miss_Qte_Line_Dtl_tbl;
673 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
674
675 CURSOR c_getDetLinetbl(p_quote_line_id number) IS
676 SELECT dl.quote_line_detail_id
677 ,dl.creation_date
678 ,dl.created_by
679 ,dl.last_update_date
680 ,dl.last_updated_by
681 ,dl.last_update_login
682 ,dl.request_id
683 ,dl.program_application_id
684 ,dl.program_id
685 ,dl.program_update_date
686 ,dl.quote_line_id
687 ,dl.config_header_id
688 ,dl.config_revision_num
689 ,dl.config_item_id
690 ,dl.complete_configuration_flag
691 ,dl.valid_configuration_flag
692 ,dl.component_code
693 ,dl.service_coterminate_flag
694 ,dl.service_duration
695 ,dl.service_period
696 ,dl.service_unit_selling_percent
697 ,dl.service_unit_list_percent
698 ,dl.service_number
699 ,dl.unit_percent_base_price
700 ,dl.attribute_category
701 ,dl.attribute1
702 ,dl.attribute2
703 ,dl.attribute3
704 ,dl.attribute4
705 ,dl.attribute5
706 ,dl.attribute6
707 ,dl.attribute7
708 ,dl.attribute8
709 ,dl.attribute9
710 ,dl.attribute10
711 ,dl.attribute11
712 ,dl.attribute12
713 ,dl.attribute13
714 ,dl.attribute14
715 ,dl.attribute15
716 ,dl.service_ref_type_code
717 ,dl.service_ref_order_number
718 ,dl.service_ref_line_number
719 ,dl.service_ref_line_id
720 ,dl.service_ref_system_id
721 ,dl.service_ref_option_numb
722 ,dl.service_ref_shipment_numb
723 ,dl.return_ref_type
724 ,dl.return_ref_header_id
725 ,dl.return_ref_line_id
726 ,dl.return_attribute1
727 ,dl.return_attribute2
728 ,dl.return_attribute3
729 ,dl.return_attribute4
730 ,dl.return_attribute5
731 ,dl.return_attribute6
732 ,dl.return_attribute7
733 ,dl.return_attribute8
734 ,dl.return_attribute9
735 ,dl.return_attribute10
736 ,dl.return_attribute11
737 ,dl.return_attribute12
738 ,dl.return_attribute13
739 ,dl.return_attribute14
740 ,dl.return_attribute15
741 From ASO_quote_LINE_details dl
742 Where QUOTE_LINE_ID = p_quote_line_id;
743 BEGIN
744 open c_getDetLinetbl(p_quote_line_id);
745 loop
746 fetch c_getDetLinetbl into
747 l_qte_line_dtl_rec.quote_line_detail_id
748 ,l_qte_line_dtl_rec.creation_date
749 ,l_qte_line_dtl_rec.created_by
750 ,l_qte_line_dtl_rec.last_update_date
751 ,l_qte_line_dtl_rec.last_updated_by
752 ,l_qte_line_dtl_rec.last_update_login
753 ,l_qte_line_dtl_rec.request_id
754 ,l_qte_line_dtl_rec.program_application_id
755 ,l_qte_line_dtl_rec.program_id
756 ,l_qte_line_dtl_rec.program_update_date
757 ,l_qte_line_dtl_rec.quote_line_id
758 ,l_qte_line_dtl_rec.config_header_id
759 ,l_qte_line_dtl_rec.config_revision_num
760 ,l_qte_line_dtl_rec.config_item_id
761 ,l_qte_line_dtl_rec.complete_configuration_flag
762 ,l_qte_line_dtl_rec.valid_configuration_flag
763 ,l_qte_line_dtl_rec.component_code
764 ,l_qte_line_dtl_rec.service_coterminate_flag
765 ,l_qte_line_dtl_rec.service_duration
766 ,l_qte_line_dtl_rec.service_period
767 ,l_qte_line_dtl_rec.service_unit_selling_percent
768 ,l_qte_line_dtl_rec.service_unit_list_percent
769 ,l_qte_line_dtl_rec.service_number
770 ,l_qte_line_dtl_rec.unit_percent_base_price
771 ,l_qte_line_dtl_rec.attribute_category
772 ,l_qte_line_dtl_rec.attribute1
773 ,l_qte_line_dtl_rec.attribute2
774 ,l_qte_line_dtl_rec.attribute3
775 ,l_qte_line_dtl_rec.attribute4
776 ,l_qte_line_dtl_rec.attribute5
777 ,l_qte_line_dtl_rec.attribute6
778 ,l_qte_line_dtl_rec.attribute7
779 ,l_qte_line_dtl_rec.attribute8
780 ,l_qte_line_dtl_rec.attribute9
781 ,l_qte_line_dtl_rec.attribute10
782 ,l_qte_line_dtl_rec.attribute11
783 ,l_qte_line_dtl_rec.attribute12
784 ,l_qte_line_dtl_rec.attribute13
785 ,l_qte_line_dtl_rec.attribute14
786 ,l_qte_line_dtl_rec.attribute15
787 ,l_qte_line_dtl_rec.service_ref_type_code
788 ,l_qte_line_dtl_rec.service_ref_order_number
789 ,l_qte_line_dtl_rec.service_ref_line_number
790 ,l_qte_line_dtl_rec.service_ref_line_id
791 ,l_qte_line_dtl_rec.service_ref_system_id
792 ,l_qte_line_dtl_rec.service_ref_option_numb
793 ,l_qte_line_dtl_rec.service_ref_shipment_numb
794 ,l_qte_line_dtl_rec.return_ref_type
795 ,l_qte_line_dtl_rec.return_ref_header_id
796 ,l_qte_line_dtl_rec.return_ref_line_id
797 ,l_qte_line_dtl_rec.return_attribute1
798 ,l_qte_line_dtl_rec.return_attribute2
799 ,l_qte_line_dtl_rec.return_attribute3
800 ,l_qte_line_dtl_rec.return_attribute4
801 ,l_qte_line_dtl_rec.return_attribute5
802 ,l_qte_line_dtl_rec.return_attribute6
803 ,l_qte_line_dtl_rec.return_attribute7
804 ,l_qte_line_dtl_rec.return_attribute8
805 ,l_qte_line_dtl_rec.return_attribute9
806 ,l_qte_line_dtl_rec.return_attribute10
807 ,l_qte_line_dtl_rec.return_attribute11
808 ,l_qte_line_dtl_rec.return_attribute12
809 ,l_qte_line_dtl_rec.return_attribute13
810 ,l_qte_line_dtl_rec.return_attribute14
811 ,l_qte_line_dtl_rec.return_attribute15;
812 EXIT WHEN c_getDetLinetbl%notfound;
813 l_qte_line_dtl_tbl(l_qte_line_dtl_tbl.count+1) := l_qte_line_dtl_rec;
814 END LOOP;
815 CLOSE c_getDetLinetbl;
816 RETURN l_qte_line_dtl_tbl;
817 END getLineDetailTbl;
818
819
820 FUNCTION getLineRelationshipTbl(
821 p_quote_line_id IN NUMBER
822 ) RETURN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
823 IS
824 l_line_rltship_rec ASO_QUOTE_PUB.Line_Rltship_rec_Type;
825 l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
826 := ASO_QUOTE_PUB.g_miss_Line_Rltship_Tbl;
827 CURSOR c_getRelLinetbl(l_quote_line_id number) IS
828 SELECT LINE_RELATIONSHIP_ID
829 ,CREATION_DATE
830 ,CREATED_BY
831 ,LAST_UPDATED_BY
832 ,LAST_UPDATE_DATE
833 ,LAST_UPDATE_LOGIN
834 ,REQUEST_ID
835 ,PROGRAM_APPLICATION_ID
836 ,PROGRAM_ID
837 ,PROGRAM_UPDATE_DATE
838 ,QUOTE_LINE_ID
839 ,RELATED_QUOTE_LINE_ID
840 ,RELATIONSHIP_TYPE_CODE
841 ,RECIPROCAL_FLAG
842 From ASO_LINE_RELATIONSHIPS
843 Where QUOTE_LINE_ID = p_quote_line_id;
844
845
846 begin
847
848 open c_getRelLinetbl(p_quote_line_id);
849
850 loop
851 fetch c_getRelLinetbl into
852 l_line_rltship_rec.LINE_RELATIONSHIP_ID
853 ,l_line_rltship_rec.CREATION_DATE
854 ,l_line_rltship_rec.CREATED_BY
855 ,l_line_rltship_rec.LAST_UPDATED_BY
856 ,l_line_rltship_rec.LAST_UPDATE_DATE
857 ,l_line_rltship_rec.LAST_UPDATE_LOGIN
858 ,l_line_rltship_rec.REQUEST_ID
859 ,l_line_rltship_rec.PROGRAM_APPLICATION_ID
860 ,l_line_rltship_rec.PROGRAM_ID
861 ,l_line_rltship_rec.PROGRAM_UPDATE_DATE
862 ,l_line_rltship_rec.QUOTE_LINE_ID
863 ,l_line_rltship_rec.RELATED_QUOTE_LINE_ID
864 ,l_line_rltship_rec.RELATIONSHIP_TYPE_CODE
865 ,l_line_rltship_rec.RECIPROCAL_FLAG;
866 EXIT WHEN c_getRelLinetbl%NOTFOUND;
867 l_line_rltship_tbl(l_line_rltship_tbl.count+1) := l_line_rltship_rec;
868 END LOOP;
869 CLOSE c_getRelLinetbl;
870 RETURN l_line_rltship_tbl;
871 END getLineRelationshipTbl;
872
873 FUNCTION getLinePrcAdjTbl(
874 p_quote_line_id IN NUMBER
875 ) RETURN ASO_Quote_Pub.Price_Adj_Tbl_Type
876 IS
877 l_line_PrcAdj_rec ASO_QUOTE_PUB.Price_Adj_Rec_Type;
878 l_line_PrcAdj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type
879 := ASO_QUOTE_PUB.G_Miss_Price_Adj_Tbl;
880 CURSOR c_getLinePrcAdjTbl(l_quote_line_id number) IS
881 SELECT PRICE_ADJUSTMENT_ID
882 ,CREATION_DATE
883 ,CREATED_BY
884 ,LAST_UPDATE_DATE
885 ,LAST_UPDATED_BY
886 ,LAST_UPDATE_LOGIN
887 ,PROGRAM_APPLICATION_ID
888 ,PROGRAM_ID
889 ,PROGRAM_UPDATE_DATE
890 ,REQUEST_ID
891 ,QUOTE_HEADER_ID
892 ,QUOTE_LINE_ID
893 ,QUOTE_SHIPMENT_ID
894 ,MODIFIER_HEADER_ID
895 ,MODIFIER_LINE_ID
896 ,MODIFIER_LINE_TYPE_CODE
897 ,MODIFIER_MECHANISM_TYPE_CODE
898 ,MODIFIED_FROM
899 ,MODIFIED_TO
900 ,OPERAND
901 ,ARITHMETIC_OPERATOR
902 ,AUTOMATIC_FLAG
903 ,UPDATE_ALLOWABLE_FLAG
904 ,UPDATED_FLAG
905 ,APPLIED_FLAG
906 ,ON_INVOICE_FLAG
907 ,PRICING_PHASE_ID
908 ,ATTRIBUTE_CATEGORY
909 ,ATTRIBUTE1
910 ,ATTRIBUTE2
911 ,ATTRIBUTE3
912 ,ATTRIBUTE4
913 ,ATTRIBUTE5
914 ,ATTRIBUTE6
915 ,ATTRIBUTE7
916 ,ATTRIBUTE8
917 ,ATTRIBUTE9
918 ,ATTRIBUTE10
919 ,ATTRIBUTE11
920 ,ATTRIBUTE12
921 ,ATTRIBUTE13
922 ,ATTRIBUTE14
923 ,ATTRIBUTE15
924 ,ORIG_SYS_DISCOUNT_REF
925 ,CHANGE_SEQUENCE
926 ,UPDATE_ALLOWED
927 ,CHANGE_REASON_CODE
928 ,CHANGE_REASON_TEXT
929 ,COST_ID
930 ,TAX_CODE
931 ,TAX_EXEMPT_FLAG
932 ,TAX_EXEMPT_NUMBER
933 ,TAX_EXEMPT_REASON_CODE
934 ,PARENT_ADJUSTMENT_ID
935 ,INVOICED_FLAG
936 ,ESTIMATED_FLAG
937 ,INC_IN_SALES_PERFORMANCE
938 ,SPLIT_ACTION_CODE
939 ,ADJUSTED_AMOUNT
940 ,CHARGE_TYPE_CODE
941 ,CHARGE_SUBTYPE_CODE
942 ,RANGE_BREAK_QUANTITY
943 ,ACCRUAL_CONVERSION_RATE
944 ,PRICING_GROUP_SEQUENCE
945 ,ACCRUAL_FLAG
946 ,LIST_LINE_NO
947 ,SOURCE_SYSTEM_CODE
948 ,BENEFIT_QTY
949 ,BENEFIT_UOM_CODE
950 ,PRINT_ON_INVOICE_FLAG
951 ,EXPIRATION_DATE
952 ,REBATE_TRANSACTION_TYPE_CODE
953 ,REBATE_TRANSACTION_REFERENCE
954 ,REBATE_PAYMENT_SYSTEM_CODE
955 ,REDEEMED_DATE
956 ,REDEEMED_FLAG
957 ,MODIFIER_LEVEL_CODE
958 ,PRICE_BREAK_TYPE_CODE
959 ,SUBSTITUTION_ATTRIBUTE
960 ,PRORATION_TYPE_CODE
961 ,INCLUDE_ON_RETURNS_FLAG
962 ,CREDIT_OR_CHARGE_FLAG
963 From ASO_PRICE_ADJUSTMENTS
964 Where QUOTE_LINE_ID = p_quote_line_id;
965
966 begin
967 open c_getLinePrcAdjTbl(p_quote_line_id);
968 loop
969 fetch c_getLinePrcAdjTbl into
970 l_line_PrcAdj_rec.PRICE_ADJUSTMENT_ID
971 ,l_line_PrcAdj_rec.CREATION_DATE
972 ,l_line_PrcAdj_rec.CREATED_BY
973 ,l_line_PrcAdj_rec.LAST_UPDATE_DATE
974 ,l_line_PrcAdj_rec.LAST_UPDATED_BY
975 ,l_line_PrcAdj_rec.LAST_UPDATE_LOGIN
976 ,l_line_PrcAdj_rec.PROGRAM_APPLICATION_ID
977 ,l_line_PrcAdj_rec.PROGRAM_ID
978 ,l_line_PrcAdj_rec.PROGRAM_UPDATE_DATE
979 ,l_line_PrcAdj_rec.REQUEST_ID
980 ,l_line_PrcAdj_rec.QUOTE_HEADER_ID
981 ,l_line_PrcAdj_rec.QUOTE_LINE_ID
982 ,l_line_PrcAdj_rec.QUOTE_SHIPMENT_ID
983 ,l_line_PrcAdj_rec.MODIFIER_HEADER_ID
984 ,l_line_PrcAdj_rec.MODIFIER_LINE_ID
985 ,l_line_PrcAdj_rec.MODIFIER_LINE_TYPE_CODE
986 ,l_line_PrcAdj_rec.MODIFIER_MECHANISM_TYPE_CODE
987 ,l_line_PrcAdj_rec.MODIFIED_FROM
988 ,l_line_PrcAdj_rec.MODIFIED_TO
989 ,l_line_PrcAdj_rec.OPERAND
990 ,l_line_PrcAdj_rec.ARITHMETIC_OPERATOR
991 ,l_line_PrcAdj_rec.AUTOMATIC_FLAG
992 ,l_line_PrcAdj_rec.UPDATE_ALLOWABLE_FLAG
993 ,l_line_PrcAdj_rec.UPDATED_FLAG
994 ,l_line_PrcAdj_rec.APPLIED_FLAG
995 ,l_line_PrcAdj_rec.ON_INVOICE_FLAG
996 ,l_line_PrcAdj_rec.PRICING_PHASE_ID
997 ,l_line_PrcAdj_rec.ATTRIBUTE_CATEGORY
998 ,l_line_PrcAdj_rec.ATTRIBUTE1
999 ,l_line_PrcAdj_rec.ATTRIBUTE2
1000 ,l_line_PrcAdj_rec.ATTRIBUTE3
1001 ,l_line_PrcAdj_rec.ATTRIBUTE4
1002 ,l_line_PrcAdj_rec.ATTRIBUTE5
1003 ,l_line_PrcAdj_rec.ATTRIBUTE6
1004 ,l_line_PrcAdj_rec.ATTRIBUTE7
1005 ,l_line_PrcAdj_rec.ATTRIBUTE8
1006 ,l_line_PrcAdj_rec.ATTRIBUTE9
1007 ,l_line_PrcAdj_rec.ATTRIBUTE10
1008 ,l_line_PrcAdj_rec.ATTRIBUTE11
1009 ,l_line_PrcAdj_rec.ATTRIBUTE12
1010 ,l_line_PrcAdj_rec.ATTRIBUTE13
1011 ,l_line_PrcAdj_rec.ATTRIBUTE14
1012 ,l_line_PrcAdj_rec.ATTRIBUTE15
1013 ,l_line_PrcAdj_rec.ORIG_SYS_DISCOUNT_REF
1014 ,l_line_PrcAdj_rec.CHANGE_SEQUENCE
1015 ,l_line_PrcAdj_rec.UPDATE_ALLOWED
1016 ,l_line_PrcAdj_rec.CHANGE_REASON_CODE
1017 ,l_line_PrcAdj_rec.CHANGE_REASON_TEXT
1018 ,l_line_PrcAdj_rec.COST_ID
1019 ,l_line_PrcAdj_rec.TAX_CODE
1020 ,l_line_PrcAdj_rec.TAX_EXEMPT_FLAG
1021 ,l_line_PrcAdj_rec.TAX_EXEMPT_NUMBER
1022 ,l_line_PrcAdj_rec.TAX_EXEMPT_REASON_CODE
1023 ,l_line_PrcAdj_rec.PARENT_ADJUSTMENT_ID
1024 ,l_line_PrcAdj_rec.INVOICED_FLAG
1025 ,l_line_PrcAdj_rec.ESTIMATED_FLAG
1026 ,l_line_PrcAdj_rec.INC_IN_SALES_PERFORMANCE
1027 ,l_line_PrcAdj_rec.SPLIT_ACTION_CODE
1028 ,l_line_PrcAdj_rec.ADJUSTED_AMOUNT
1029 ,l_line_PrcAdj_rec.CHARGE_TYPE_CODE
1030 ,l_line_PrcAdj_rec.CHARGE_SUBTYPE_CODE
1031 ,l_line_PrcAdj_rec.RANGE_BREAK_QUANTITY
1032 ,l_line_PrcAdj_rec.ACCRUAL_CONVERSION_RATE
1033 ,l_line_PrcAdj_rec.PRICING_GROUP_SEQUENCE
1034 ,l_line_PrcAdj_rec.ACCRUAL_FLAG
1035 ,l_line_PrcAdj_rec.LIST_LINE_NO
1036 ,l_line_PrcAdj_rec.SOURCE_SYSTEM_CODE
1037 ,l_line_PrcAdj_rec.BENEFIT_QTY
1038 ,l_line_PrcAdj_rec.BENEFIT_UOM_CODE
1039 ,l_line_PrcAdj_rec.PRINT_ON_INVOICE_FLAG
1040 ,l_line_PrcAdj_rec.EXPIRATION_DATE
1041 ,l_line_PrcAdj_rec.REBATE_TRANSACTION_TYPE_CODE
1042 ,l_line_PrcAdj_rec.REBATE_TRANSACTION_REFERENCE
1043 ,l_line_PrcAdj_rec.REBATE_PAYMENT_SYSTEM_CODE
1044 ,l_line_PrcAdj_rec.REDEEMED_DATE
1045 ,l_line_PrcAdj_rec.REDEEMED_FLAG
1046 ,l_line_PrcAdj_rec.MODIFIER_LEVEL_CODE
1047 ,l_line_PrcAdj_rec.PRICE_BREAK_TYPE_CODE
1048 ,l_line_PrcAdj_rec.SUBSTITUTION_ATTRIBUTE
1049 ,l_line_PrcAdj_rec.PRORATION_TYPE_CODE
1050 ,l_line_PrcAdj_rec.INCLUDE_ON_RETURNS_FLAG
1051 ,l_line_PrcAdj_rec.CREDIT_OR_CHARGE_FLAG;
1052 EXIT WHEN c_getLinePrcAdjTbl%NOTFOUND;
1053 l_line_PrcAdj_tbl(l_line_PrcAdj_tbl.count+1) := l_line_PrcAdj_rec;
1054 END LOOP;
1055 CLOSE c_getLinePrcAdjTbl;
1056 RETURN l_line_PrcAdj_tbl;
1057 END getLinePrcAdjTbl;
1058
1059 FUNCTION getHdrPrcAdjTbl(
1060 p_quote_hdr_id IN NUMBER
1061 ) RETURN ASO_Quote_Pub.Price_Adj_Tbl_Type
1062 IS
1063 l_hdr_PrcAdj_rec ASO_QUOTE_PUB.Price_Adj_Rec_Type;
1064 l_hdr_PrcAdj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type
1065 := ASO_QUOTE_PUB.G_Miss_Price_Adj_Tbl;
1066 CURSOR c_getHdrPrcAdjTbl(l_quote_hdr_id number) IS
1067 SELECT PRICE_ADJUSTMENT_ID
1068 ,CREATION_DATE
1069 ,CREATED_BY
1070 ,LAST_UPDATE_DATE
1071 ,LAST_UPDATED_BY
1072 ,LAST_UPDATE_LOGIN
1073 ,PROGRAM_APPLICATION_ID
1074 ,PROGRAM_ID
1075 ,PROGRAM_UPDATE_DATE
1076 ,REQUEST_ID
1077 ,QUOTE_HEADER_ID
1078 ,QUOTE_LINE_ID
1079 ,QUOTE_SHIPMENT_ID
1080 ,MODIFIER_HEADER_ID
1081 ,MODIFIER_LINE_ID
1082 ,MODIFIER_LINE_TYPE_CODE
1083 ,MODIFIER_MECHANISM_TYPE_CODE
1084 ,MODIFIED_FROM
1085 ,MODIFIED_TO
1086 ,OPERAND
1087 ,ARITHMETIC_OPERATOR
1088 ,AUTOMATIC_FLAG
1089 ,UPDATE_ALLOWABLE_FLAG
1090 ,UPDATED_FLAG
1091 ,APPLIED_FLAG
1092 ,ON_INVOICE_FLAG
1093 ,PRICING_PHASE_ID
1094 ,ATTRIBUTE_CATEGORY
1095 ,ATTRIBUTE1
1096 ,ATTRIBUTE2
1097 ,ATTRIBUTE3
1098 ,ATTRIBUTE4
1099 ,ATTRIBUTE5
1100 ,ATTRIBUTE6
1101 ,ATTRIBUTE7
1102 ,ATTRIBUTE8
1103 ,ATTRIBUTE9
1104 ,ATTRIBUTE10
1105 ,ATTRIBUTE11
1106 ,ATTRIBUTE12
1107 ,ATTRIBUTE13
1108 ,ATTRIBUTE14
1109 ,ATTRIBUTE15
1110 ,ORIG_SYS_DISCOUNT_REF
1111 ,CHANGE_SEQUENCE
1112 ,UPDATE_ALLOWED
1113 ,CHANGE_REASON_CODE
1114 ,CHANGE_REASON_TEXT
1115 ,COST_ID
1116 ,TAX_CODE
1117 ,TAX_EXEMPT_FLAG
1118 ,TAX_EXEMPT_NUMBER
1119 ,TAX_EXEMPT_REASON_CODE
1120 ,PARENT_ADJUSTMENT_ID
1121 ,INVOICED_FLAG
1122 ,ESTIMATED_FLAG
1123 ,INC_IN_SALES_PERFORMANCE
1124 ,SPLIT_ACTION_CODE
1125 ,ADJUSTED_AMOUNT
1126 ,CHARGE_TYPE_CODE
1127 ,CHARGE_SUBTYPE_CODE
1128 ,RANGE_BREAK_QUANTITY
1129 ,ACCRUAL_CONVERSION_RATE
1130 ,PRICING_GROUP_SEQUENCE
1131 ,ACCRUAL_FLAG
1132 ,LIST_LINE_NO
1133 ,SOURCE_SYSTEM_CODE
1134 ,BENEFIT_QTY
1135 ,BENEFIT_UOM_CODE
1136 ,PRINT_ON_INVOICE_FLAG
1137 ,EXPIRATION_DATE
1138 ,REBATE_TRANSACTION_TYPE_CODE
1139 ,REBATE_TRANSACTION_REFERENCE
1140 ,REBATE_PAYMENT_SYSTEM_CODE
1141 ,REDEEMED_DATE
1142 ,REDEEMED_FLAG
1143 ,MODIFIER_LEVEL_CODE
1144 ,PRICE_BREAK_TYPE_CODE
1145 ,SUBSTITUTION_ATTRIBUTE
1146 ,PRORATION_TYPE_CODE
1147 ,INCLUDE_ON_RETURNS_FLAG
1148 ,CREDIT_OR_CHARGE_FLAG
1149 From ASO_PRICE_ADJUSTMENTS
1150 Where QUOTE_HEADER_ID = p_quote_hdr_id;
1151
1152 begin
1153 open c_getHdrPrcAdjTbl(p_quote_hdr_id);
1154 loop
1155 fetch c_getHdrPrcAdjTbl into
1156 l_hdr_PrcAdj_rec.PRICE_ADJUSTMENT_ID
1157 ,l_hdr_PrcAdj_rec.CREATION_DATE
1158 ,l_hdr_PrcAdj_rec.CREATED_BY
1159 ,l_hdr_PrcAdj_rec.LAST_UPDATE_DATE
1160 ,l_hdr_PrcAdj_rec.LAST_UPDATED_BY
1161 ,l_hdr_PrcAdj_rec.LAST_UPDATE_LOGIN
1162 ,l_hdr_PrcAdj_rec.PROGRAM_APPLICATION_ID
1163 ,l_hdr_PrcAdj_rec.PROGRAM_ID
1164 ,l_hdr_PrcAdj_rec.PROGRAM_UPDATE_DATE
1165 ,l_hdr_PrcAdj_rec.REQUEST_ID
1166 ,l_hdr_PrcAdj_rec.QUOTE_HEADER_ID
1167 ,l_hdr_PrcAdj_rec.QUOTE_LINE_ID
1168 ,l_hdr_PrcAdj_rec.QUOTE_SHIPMENT_ID
1169 ,l_hdr_PrcAdj_rec.MODIFIER_HEADER_ID
1170 ,l_hdr_PrcAdj_rec.MODIFIER_LINE_ID
1171 ,l_hdr_PrcAdj_rec.MODIFIER_LINE_TYPE_CODE
1172 ,l_hdr_PrcAdj_rec.MODIFIER_MECHANISM_TYPE_CODE
1173 ,l_hdr_PrcAdj_rec.MODIFIED_FROM
1174 ,l_hdr_PrcAdj_rec.MODIFIED_TO
1175 ,l_hdr_PrcAdj_rec.OPERAND
1176 ,l_hdr_PrcAdj_rec.ARITHMETIC_OPERATOR
1177 ,l_hdr_PrcAdj_rec.AUTOMATIC_FLAG
1178 ,l_hdr_PrcAdj_rec.UPDATE_ALLOWABLE_FLAG
1179 ,l_hdr_PrcAdj_rec.UPDATED_FLAG
1180 ,l_hdr_PrcAdj_rec.APPLIED_FLAG
1181 ,l_hdr_PrcAdj_rec.ON_INVOICE_FLAG
1182 ,l_hdr_PrcAdj_rec.PRICING_PHASE_ID
1183 ,l_hdr_PrcAdj_rec.ATTRIBUTE_CATEGORY
1184 ,l_hdr_PrcAdj_rec.ATTRIBUTE1
1185 ,l_hdr_PrcAdj_rec.ATTRIBUTE2
1186 ,l_hdr_PrcAdj_rec.ATTRIBUTE3
1187 ,l_hdr_PrcAdj_rec.ATTRIBUTE4
1188 ,l_hdr_PrcAdj_rec.ATTRIBUTE5
1189 ,l_hdr_PrcAdj_rec.ATTRIBUTE6
1190 ,l_hdr_PrcAdj_rec.ATTRIBUTE7
1191 ,l_hdr_PrcAdj_rec.ATTRIBUTE8
1192 ,l_hdr_PrcAdj_rec.ATTRIBUTE9
1193 ,l_hdr_PrcAdj_rec.ATTRIBUTE10
1194 ,l_hdr_PrcAdj_rec.ATTRIBUTE11
1195 ,l_hdr_PrcAdj_rec.ATTRIBUTE12
1196 ,l_hdr_PrcAdj_rec.ATTRIBUTE13
1197 ,l_hdr_PrcAdj_rec.ATTRIBUTE14
1198 ,l_hdr_PrcAdj_rec.ATTRIBUTE15
1199 ,l_hdr_PrcAdj_rec.ORIG_SYS_DISCOUNT_REF
1200 ,l_hdr_PrcAdj_rec.CHANGE_SEQUENCE
1201 ,l_hdr_PrcAdj_rec.UPDATE_ALLOWED
1202 ,l_hdr_PrcAdj_rec.CHANGE_REASON_CODE
1203 ,l_hdr_PrcAdj_rec.CHANGE_REASON_TEXT
1204 ,l_hdr_PrcAdj_rec.COST_ID
1205 ,l_hdr_PrcAdj_rec.TAX_CODE
1206 ,l_hdr_PrcAdj_rec.TAX_EXEMPT_FLAG
1207 ,l_hdr_PrcAdj_rec.TAX_EXEMPT_NUMBER
1208 ,l_hdr_PrcAdj_rec.TAX_EXEMPT_REASON_CODE
1209 ,l_hdr_PrcAdj_rec.PARENT_ADJUSTMENT_ID
1210 ,l_hdr_PrcAdj_rec.INVOICED_FLAG
1211 ,l_hdr_PrcAdj_rec.ESTIMATED_FLAG
1212 ,l_hdr_PrcAdj_rec.INC_IN_SALES_PERFORMANCE
1213 ,l_hdr_PrcAdj_rec.SPLIT_ACTION_CODE
1214 ,l_hdr_PrcAdj_rec.ADJUSTED_AMOUNT
1215 ,l_hdr_PrcAdj_rec.CHARGE_TYPE_CODE
1216 ,l_hdr_PrcAdj_rec.CHARGE_SUBTYPE_CODE
1217 ,l_hdr_PrcAdj_rec.RANGE_BREAK_QUANTITY
1218 ,l_hdr_PrcAdj_rec.ACCRUAL_CONVERSION_RATE
1219 ,l_hdr_PrcAdj_rec.PRICING_GROUP_SEQUENCE
1220 ,l_hdr_PrcAdj_rec.ACCRUAL_FLAG
1221 ,l_hdr_PrcAdj_rec.LIST_LINE_NO
1222 ,l_hdr_PrcAdj_rec.SOURCE_SYSTEM_CODE
1223 ,l_hdr_PrcAdj_rec.BENEFIT_QTY
1224 ,l_hdr_PrcAdj_rec.BENEFIT_UOM_CODE
1225 ,l_hdr_PrcAdj_rec.PRINT_ON_INVOICE_FLAG
1226 ,l_hdr_PrcAdj_rec.EXPIRATION_DATE
1227 ,l_hdr_PrcAdj_rec.REBATE_TRANSACTION_TYPE_CODE
1228 ,l_hdr_PrcAdj_rec.REBATE_TRANSACTION_REFERENCE
1229 ,l_hdr_PrcAdj_rec.REBATE_PAYMENT_SYSTEM_CODE
1230 ,l_hdr_PrcAdj_rec.REDEEMED_DATE
1231 ,l_hdr_PrcAdj_rec.REDEEMED_FLAG
1232 ,l_hdr_PrcAdj_rec.MODIFIER_LEVEL_CODE
1233 ,l_hdr_PrcAdj_rec.PRICE_BREAK_TYPE_CODE
1234 ,l_hdr_PrcAdj_rec.SUBSTITUTION_ATTRIBUTE
1235 ,l_hdr_PrcAdj_rec.PRORATION_TYPE_CODE
1236 ,l_hdr_PrcAdj_rec.INCLUDE_ON_RETURNS_FLAG
1237 ,l_hdr_PrcAdj_rec.CREDIT_OR_CHARGE_FLAG;
1238 EXIT WHEN c_getHdrPrcAdjTbl%NOTFOUND;
1239 l_hdr_PrcAdj_tbl(l_hdr_PrcAdj_tbl.count+1) := l_hdr_PrcAdj_rec;
1240 END LOOP;
1241 CLOSE c_getHdrPrcAdjTbl;
1242 RETURN l_hdr_PrcAdj_tbl;
1243 END getHdrPrcAdjTbl;
1244
1245 FUNCTION getAllLinesPrcAdjTbl(
1246 p_quote_hdr_id IN NUMBER
1247 ) RETURN ASO_Quote_Pub.Price_Adj_Tbl_Type
1248 IS
1249 l_AllLines_PrcAdj_rec ASO_QUOTE_PUB.Price_Adj_Rec_Type;
1250 l_AllLines_PrcAdj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type
1251 := ASO_QUOTE_PUB.G_Miss_Price_Adj_Tbl;
1252 CURSOR c_getAllLinesPrcAdjTbl(l_quote_hdr_id number) IS
1253 SELECT PRICE_ADJUSTMENT_ID
1254 ,CREATION_DATE
1255 ,CREATED_BY
1256 ,LAST_UPDATE_DATE
1257 ,LAST_UPDATED_BY
1258 ,LAST_UPDATE_LOGIN
1259 ,PROGRAM_APPLICATION_ID
1260 ,PROGRAM_ID
1261 ,PROGRAM_UPDATE_DATE
1262 ,REQUEST_ID
1263 ,QUOTE_HEADER_ID
1264 ,QUOTE_LINE_ID
1265 ,QUOTE_SHIPMENT_ID
1266 ,MODIFIER_HEADER_ID
1267 ,MODIFIER_LINE_ID
1268 ,MODIFIER_LINE_TYPE_CODE
1269 ,MODIFIER_MECHANISM_TYPE_CODE
1270 ,MODIFIED_FROM
1271 ,MODIFIED_TO
1272 ,OPERAND
1273 ,ARITHMETIC_OPERATOR
1274 ,AUTOMATIC_FLAG
1275 ,UPDATE_ALLOWABLE_FLAG
1276 ,UPDATED_FLAG
1277 ,APPLIED_FLAG
1278 ,ON_INVOICE_FLAG
1279 ,PRICING_PHASE_ID
1280 ,ATTRIBUTE_CATEGORY
1281 ,ATTRIBUTE1
1282 ,ATTRIBUTE2
1283 ,ATTRIBUTE3
1284 ,ATTRIBUTE4
1285 ,ATTRIBUTE5
1286 ,ATTRIBUTE6
1287 ,ATTRIBUTE7
1288 ,ATTRIBUTE8
1289 ,ATTRIBUTE9
1290 ,ATTRIBUTE10
1291 ,ATTRIBUTE11
1292 ,ATTRIBUTE12
1293 ,ATTRIBUTE13
1294 ,ATTRIBUTE14
1295 ,ATTRIBUTE15
1296 ,ORIG_SYS_DISCOUNT_REF
1297 ,CHANGE_SEQUENCE
1298 ,UPDATE_ALLOWED
1299 ,CHANGE_REASON_CODE
1300 ,CHANGE_REASON_TEXT
1301 ,COST_ID
1302 ,TAX_CODE
1303 ,TAX_EXEMPT_FLAG
1304 ,TAX_EXEMPT_NUMBER
1305 ,TAX_EXEMPT_REASON_CODE
1306 ,PARENT_ADJUSTMENT_ID
1307 ,INVOICED_FLAG
1308 ,ESTIMATED_FLAG
1309 ,INC_IN_SALES_PERFORMANCE
1310 ,SPLIT_ACTION_CODE
1311 ,ADJUSTED_AMOUNT
1312 ,CHARGE_TYPE_CODE
1313 ,CHARGE_SUBTYPE_CODE
1314 ,RANGE_BREAK_QUANTITY
1315 ,ACCRUAL_CONVERSION_RATE
1316 ,PRICING_GROUP_SEQUENCE
1317 ,ACCRUAL_FLAG
1318 ,LIST_LINE_NO
1319 ,SOURCE_SYSTEM_CODE
1320 ,BENEFIT_QTY
1321 ,BENEFIT_UOM_CODE
1322 ,PRINT_ON_INVOICE_FLAG
1323 ,EXPIRATION_DATE
1324 ,REBATE_TRANSACTION_TYPE_CODE
1325 ,REBATE_TRANSACTION_REFERENCE
1326 ,REBATE_PAYMENT_SYSTEM_CODE
1327 ,REDEEMED_DATE
1328 ,REDEEMED_FLAG
1329 ,MODIFIER_LEVEL_CODE
1330 ,PRICE_BREAK_TYPE_CODE
1331 ,SUBSTITUTION_ATTRIBUTE
1332 ,PRORATION_TYPE_CODE
1333 ,INCLUDE_ON_RETURNS_FLAG
1334 ,CREDIT_OR_CHARGE_FLAG
1335 From ASO_PRICE_ADJUSTMENTS
1336 Where QUOTE_HEADER_ID = p_quote_hdr_id
1337 and QUOTE_LINE_ID is not null;
1338
1339 begin
1340 open c_getAllLinesPrcAdjTbl(p_quote_hdr_id);
1341 loop
1342 fetch c_getAllLinesPrcAdjTbl into
1343 l_AllLines_PrcAdj_rec.PRICE_ADJUSTMENT_ID
1344 ,l_AllLines_PrcAdj_rec.CREATION_DATE
1345 ,l_AllLines_PrcAdj_rec.CREATED_BY
1346 ,l_AllLines_PrcAdj_rec.LAST_UPDATE_DATE
1347 ,l_AllLines_PrcAdj_rec.LAST_UPDATED_BY
1348 ,l_AllLines_PrcAdj_rec.LAST_UPDATE_LOGIN
1349 ,l_AllLines_PrcAdj_rec.PROGRAM_APPLICATION_ID
1350 ,l_AllLines_PrcAdj_rec.PROGRAM_ID
1351 ,l_AllLines_PrcAdj_rec.PROGRAM_UPDATE_DATE
1352 ,l_AllLines_PrcAdj_rec.REQUEST_ID
1353 ,l_AllLines_PrcAdj_rec.QUOTE_HEADER_ID
1354 ,l_AllLines_PrcAdj_rec.QUOTE_LINE_ID
1355 ,l_AllLines_PrcAdj_rec.QUOTE_SHIPMENT_ID
1356 ,l_AllLines_PrcAdj_rec.MODIFIER_HEADER_ID
1357 ,l_AllLines_PrcAdj_rec.MODIFIER_LINE_ID
1358 ,l_AllLines_PrcAdj_rec.MODIFIER_LINE_TYPE_CODE
1359 ,l_AllLines_PrcAdj_rec.MODIFIER_MECHANISM_TYPE_CODE
1360 ,l_AllLines_PrcAdj_rec.MODIFIED_FROM
1361 ,l_AllLines_PrcAdj_rec.MODIFIED_TO
1362 ,l_AllLines_PrcAdj_rec.OPERAND
1363 ,l_AllLines_PrcAdj_rec.ARITHMETIC_OPERATOR
1364 ,l_AllLines_PrcAdj_rec.AUTOMATIC_FLAG
1365 ,l_AllLines_PrcAdj_rec.UPDATE_ALLOWABLE_FLAG
1366 ,l_AllLines_PrcAdj_rec.UPDATED_FLAG
1367 ,l_AllLines_PrcAdj_rec.APPLIED_FLAG
1368 ,l_AllLines_PrcAdj_rec.ON_INVOICE_FLAG
1369 ,l_AllLines_PrcAdj_rec.PRICING_PHASE_ID
1370 ,l_AllLines_PrcAdj_rec.ATTRIBUTE_CATEGORY
1371 ,l_AllLines_PrcAdj_rec.ATTRIBUTE1
1372 ,l_AllLines_PrcAdj_rec.ATTRIBUTE2
1373 ,l_AllLines_PrcAdj_rec.ATTRIBUTE3
1374 ,l_AllLines_PrcAdj_rec.ATTRIBUTE4
1375 ,l_AllLines_PrcAdj_rec.ATTRIBUTE5
1376 ,l_AllLines_PrcAdj_rec.ATTRIBUTE6
1377 ,l_AllLines_PrcAdj_rec.ATTRIBUTE7
1378 ,l_AllLines_PrcAdj_rec.ATTRIBUTE8
1379 ,l_AllLines_PrcAdj_rec.ATTRIBUTE9
1380 ,l_AllLines_PrcAdj_rec.ATTRIBUTE10
1381 ,l_AllLines_PrcAdj_rec.ATTRIBUTE11
1382 ,l_AllLines_PrcAdj_rec.ATTRIBUTE12
1383 ,l_AllLines_PrcAdj_rec.ATTRIBUTE13
1384 ,l_AllLines_PrcAdj_rec.ATTRIBUTE14
1385 ,l_AllLines_PrcAdj_rec.ATTRIBUTE15
1386 ,l_AllLines_PrcAdj_rec.ORIG_SYS_DISCOUNT_REF
1387 ,l_AllLines_PrcAdj_rec.CHANGE_SEQUENCE
1388 ,l_AllLines_PrcAdj_rec.UPDATE_ALLOWED
1389 ,l_AllLines_PrcAdj_rec.CHANGE_REASON_CODE
1390 ,l_AllLines_PrcAdj_rec.CHANGE_REASON_TEXT
1391 ,l_AllLines_PrcAdj_rec.COST_ID
1392 ,l_AllLines_PrcAdj_rec.TAX_CODE
1393 ,l_AllLines_PrcAdj_rec.TAX_EXEMPT_FLAG
1394 ,l_AllLines_PrcAdj_rec.TAX_EXEMPT_NUMBER
1395 ,l_AllLines_PrcAdj_rec.TAX_EXEMPT_REASON_CODE
1396 ,l_AllLines_PrcAdj_rec.PARENT_ADJUSTMENT_ID
1397 ,l_AllLines_PrcAdj_rec.INVOICED_FLAG
1398 ,l_AllLines_PrcAdj_rec.ESTIMATED_FLAG
1399 ,l_AllLines_PrcAdj_rec.INC_IN_SALES_PERFORMANCE
1400 ,l_AllLines_PrcAdj_rec.SPLIT_ACTION_CODE
1401 ,l_AllLines_PrcAdj_rec.ADJUSTED_AMOUNT
1402 ,l_AllLines_PrcAdj_rec.CHARGE_TYPE_CODE
1403 ,l_AllLines_PrcAdj_rec.CHARGE_SUBTYPE_CODE
1404 ,l_AllLines_PrcAdj_rec.RANGE_BREAK_QUANTITY
1405 ,l_AllLines_PrcAdj_rec.ACCRUAL_CONVERSION_RATE
1406 ,l_AllLines_PrcAdj_rec.PRICING_GROUP_SEQUENCE
1407 ,l_AllLines_PrcAdj_rec.ACCRUAL_FLAG
1408 ,l_AllLines_PrcAdj_rec.LIST_LINE_NO
1409 ,l_AllLines_PrcAdj_rec.SOURCE_SYSTEM_CODE
1410 ,l_AllLines_PrcAdj_rec.BENEFIT_QTY
1411 ,l_AllLines_PrcAdj_rec.BENEFIT_UOM_CODE
1412 ,l_AllLines_PrcAdj_rec.PRINT_ON_INVOICE_FLAG
1413 ,l_AllLines_PrcAdj_rec.EXPIRATION_DATE
1414 ,l_AllLines_PrcAdj_rec.REBATE_TRANSACTION_TYPE_CODE
1415 ,l_AllLines_PrcAdj_rec.REBATE_TRANSACTION_REFERENCE
1416 ,l_AllLines_PrcAdj_rec.REBATE_PAYMENT_SYSTEM_CODE
1417 ,l_AllLines_PrcAdj_rec.REDEEMED_DATE
1418 ,l_AllLines_PrcAdj_rec.REDEEMED_FLAG
1419 ,l_AllLines_PrcAdj_rec.MODIFIER_LEVEL_CODE
1420 ,l_AllLines_PrcAdj_rec.PRICE_BREAK_TYPE_CODE
1421 ,l_AllLines_PrcAdj_rec.SUBSTITUTION_ATTRIBUTE
1422 ,l_AllLines_PrcAdj_rec.PRORATION_TYPE_CODE
1423 ,l_AllLines_PrcAdj_rec.INCLUDE_ON_RETURNS_FLAG
1424 ,l_AllLines_PrcAdj_rec.CREDIT_OR_CHARGE_FLAG;
1425 EXIT WHEN c_getAllLinesPrcAdjTbl%NOTFOUND;
1426 l_AllLines_PrcAdj_tbl(l_AllLines_PrcAdj_tbl.count+1) := l_AllLines_PrcAdj_rec;
1427 END LOOP;
1428 CLOSE c_getAllLinesPrcAdjTbl;
1429 RETURN l_AllLines_PrcAdj_tbl;
1430 END getAllLinesPrcAdjTbl;
1431
1432 FUNCTION getPrcAdjIndexFromPrcAdjId(
1433 p_price_adjustment_id IN NUMBER
1434 ,p_Price_Adjustment_tbl IN aso_quote_pub.Price_Adj_Tbl_Type
1435 ) RETURN NUMBER
1436 IS
1437 BEGIN
1438
1439 FOR i IN 1..p_Price_Adjustment_tbl.count LOOP
1440 IF p_price_adjustment_id = p_Price_Adjustment_tbl(i).price_adjustment_id then
1441 RETURN i;
1442 END IF;
1443
1444
1445 END LOOP;
1446
1447 RETURN FND_API.G_MISS_NUM;
1448 END getPrcAdjIndexFromPrcAdjId;
1449
1450
1451 FUNCTION getLinePrcAdjRelTbl(
1452 p_price_adjustment_id IN NUMBER
1453 ) RETURN ASO_Quote_Pub.Price_Adj_Rltship_Tbl_Type
1454 IS
1455 l_line_PrcAdjRel_rec ASO_QUOTE_PUB.Price_Adj_Rltship_Rec_Type;
1456 l_line_PrcAdjRel_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
1457 := ASO_QUOTE_PUB.G_Miss_Price_Adj_Rltship_Tbl;
1458 CURSOR c_getLinePrcAdjRelTbl(l_price_adjustment_id number) IS
1459 SELECT ADJ_RELATIONSHIP_ID
1460 ,CREATION_DATE
1461 ,CREATED_BY
1462 ,LAST_UPDATE_DATE
1463 ,LAST_UPDATED_BY
1464 ,LAST_UPDATE_LOGIN
1465 ,REQUEST_ID
1466 ,PROGRAM_APPLICATION_ID
1467 ,PROGRAM_ID
1468 ,PROGRAM_UPDATE_DATE
1469 ,QUOTE_LINE_ID
1470 ,QUOTE_SHIPMENT_ID
1471 ,PRICE_ADJUSTMENT_ID
1472 ,RLTD_PRICE_ADJ_ID
1473 From ASO_PRICE_ADJ_RELATIONSHIPS
1474 Where RLTD_PRICE_ADJ_ID = p_price_adjustment_id;
1475
1476 begin
1477 open c_getLinePrcAdjRelTbl(p_price_adjustment_id);
1478 loop
1479 fetch c_getLinePrcAdjRelTbl into
1480 l_line_PrcAdjRel_rec.ADJ_RELATIONSHIP_ID
1481 ,l_line_PrcAdjRel_rec.CREATION_DATE
1482 ,l_line_PrcAdjRel_rec.CREATED_BY
1483 ,l_line_PrcAdjRel_rec.LAST_UPDATE_DATE
1484 ,l_line_PrcAdjRel_rec.LAST_UPDATED_BY
1485 ,l_line_PrcAdjRel_rec.LAST_UPDATE_LOGIN
1486 ,l_line_PrcAdjRel_rec.REQUEST_ID
1487 ,l_line_PrcAdjRel_rec.PROGRAM_APPLICATION_ID
1488 ,l_line_PrcAdjRel_rec.PROGRAM_ID
1489 ,l_line_PrcAdjRel_rec.PROGRAM_UPDATE_DATE
1490 ,l_line_PrcAdjRel_rec.QUOTE_LINE_ID
1491 ,l_line_PrcAdjRel_rec.QUOTE_SHIPMENT_ID
1492 ,l_line_PrcAdjRel_rec.PRICE_ADJUSTMENT_ID
1493 ,l_line_PrcAdjRel_rec.RLTD_PRICE_ADJ_ID;
1494 EXIT WHEN c_getLinePrcAdjRelTbl%NOTFOUND;
1495 l_line_PrcAdjRel_tbl(l_line_PrcAdjRel_tbl.count+1) := l_line_PrcAdjRel_rec;
1496 END LOOP;
1497 CLOSE c_getLinePrcAdjRelTbl;
1498 RETURN l_line_PrcAdjRel_tbl;
1499 END getLinePrcAdjRelTbl;
1500
1501
1502 FUNCTION getLineTbl(
1503 p_quote_header_Id IN NUMBER
1504 ) RETURN ASO_QUOTE_PUB.QTE_LINE_TBL_TYPE
1505 IS
1506
1507 l_qte_line_rec ASO_QUOTE_PUB.QTE_LINE_REC_TYPE;
1508 l_qte_line_tbl ASO_QUOTE_PUB.QTE_LINE_TBL_TYPE;
1509 CURSOR c_getlinetbl(l_quote_header_id number) IS
1510 SELECT l.QUOTE_LINE_ID
1511 ,l.CREATION_DATE
1512 ,l.CREATED_BY
1513 ,l.LAST_UPDATE_DATE
1514 ,l.LAST_UPDATED_BY
1515 ,l.LAST_UPDATE_LOGIN
1516 ,l.REQUEST_ID
1517 ,l.PROGRAM_APPLICATION_ID
1518 ,l.PROGRAM_ID
1519 ,l.PROGRAM_UPDATE_DATE
1520 ,l.QUOTE_HEADER_ID
1521 ,l.ORG_ID
1522 ,l.LINE_CATEGORY_CODE
1523 ,l.ITEM_TYPE_CODE
1524 ,l.LINE_NUMBER
1525 ,l.START_DATE_ACTIVE
1526 ,l.END_DATE_ACTIVE
1527 ,l.ORDER_LINE_TYPE_ID
1528 ,l.INVOICE_TO_PARTY_SITE_ID
1529 ,l.INVOICE_TO_PARTY_ID
1530 ,l.ORGANIZATION_ID
1531 ,l.INVENTORY_ITEM_ID
1532 ,l.QUANTITY
1533 ,l.UOM_CODE
1534 ,l.MARKETING_SOURCE_CODE_ID
1535 ,l.PRICE_LIST_ID
1536 ,l.PRICE_LIST_LINE_ID
1537 ,l.CURRENCY_CODE
1538 ,l.LINE_LIST_PRICE
1539 ,l.LINE_ADJUSTED_AMOUNT
1540 ,l.LINE_ADJUSTED_PERCENT
1541 ,l.LINE_QUOTE_PRICE
1542 ,l.RELATED_ITEM_ID
1543 ,l.ITEM_RELATIONSHIP_TYPE
1544 ,l.ACCOUNTING_RULE_ID
1545 ,l.INVOICING_RULE_ID
1546 ,l.SPLIT_SHIPMENT_FLAG
1547 ,l.BACKORDER_FLAG
1548 ,l.ATTRIBUTE_CATEGORY -- bug 6015035, scnagara, Uncommented the code from ATTRIBUTE_CATEGORY to ATTRIBUTE15
1549 ,l.ATTRIBUTE1
1550 ,l.ATTRIBUTE2
1551 ,l.ATTRIBUTE3
1552 ,l.ATTRIBUTE4
1553 ,l.ATTRIBUTE5
1554 ,l.ATTRIBUTE6
1555 ,l.ATTRIBUTE7
1556 ,l.ATTRIBUTE8
1557 ,l.ATTRIBUTE9
1558 ,l.ATTRIBUTE10
1559 ,l.ATTRIBUTE11
1560 ,l.ATTRIBUTE12
1561 ,l.ATTRIBUTE13
1562 ,l.ATTRIBUTE14
1563 ,l.ATTRIBUTE15 --bug# 3395318
1564 ,l.pricing_line_type_indicator
1565 From aso_quote_lines l
1566 Where l.QUOTE_HEADER_ID = l_QUOTE_HEADER_ID
1567 Order by l.quote_line_id;
1568 begin
1569
1570 open c_getlinetbl(p_quote_header_id);
1571 loop
1572 fetch c_getlinetbl into
1573 l_qte_line_rec.QUOTE_LINE_ID
1574 ,l_qte_line_rec.CREATION_DATE
1575 ,l_qte_line_rec.CREATED_BY
1576 ,l_qte_line_rec.LAST_UPDATE_DATE
1577 ,l_qte_line_rec.LAST_UPDATED_BY
1578 ,l_qte_line_rec.LAST_UPDATE_LOGIN
1579 ,l_qte_line_rec.REQUEST_ID
1580 ,l_qte_line_rec.PROGRAM_APPLICATION_ID
1581 ,l_qte_line_rec.PROGRAM_ID
1582 ,l_qte_line_rec.PROGRAM_UPDATE_DATE
1583 ,l_qte_line_rec.QUOTE_HEADER_ID
1584 ,l_qte_line_rec.ORG_ID
1585 ,l_qte_line_rec.LINE_CATEGORY_CODE
1586 ,l_qte_line_rec.ITEM_TYPE_CODE
1587 ,l_qte_line_rec.LINE_NUMBER
1588 ,l_qte_line_rec.START_DATE_ACTIVE
1589 ,l_qte_line_rec.END_DATE_ACTIVE
1590 ,l_qte_line_rec.ORDER_LINE_TYPE_ID
1591 ,l_qte_line_rec.INVOICE_TO_PARTY_SITE_ID
1592 ,l_qte_line_rec.INVOICE_TO_PARTY_ID
1593 ,l_qte_line_rec.ORGANIZATION_ID
1594 ,l_qte_line_rec.INVENTORY_ITEM_ID
1595 ,l_qte_line_rec.QUANTITY
1596 ,l_qte_line_rec.UOM_CODE
1597 ,l_qte_line_rec.MARKETING_SOURCE_CODE_ID
1598 ,l_qte_line_rec.PRICE_LIST_ID
1599 ,l_qte_line_rec.PRICE_LIST_LINE_ID
1600 ,l_qte_line_rec.CURRENCY_CODE
1601 ,l_qte_line_rec.LINE_LIST_PRICE
1602 ,l_qte_line_rec.LINE_ADJUSTED_AMOUNT
1603 ,l_qte_line_rec.LINE_ADJUSTED_PERCENT
1604 ,l_qte_line_rec.LINE_QUOTE_PRICE
1605 ,l_qte_line_rec.RELATED_ITEM_ID
1606 ,l_qte_line_rec.ITEM_RELATIONSHIP_TYPE
1607 ,l_qte_line_rec.ACCOUNTING_RULE_ID
1608 ,l_qte_line_rec.INVOICING_RULE_ID
1609 ,l_qte_line_rec.SPLIT_SHIPMENT_FLAG
1610 ,l_qte_line_rec.BACKORDER_FLAG
1611 ,l_qte_line_rec.ATTRIBUTE_CATEGORY -- bug 6015035, scnagara, Uncommented the code
1612 ,l_qte_line_rec.ATTRIBUTE1 -- from ATTRIBUTE_CATEGORY to ATTRIBUTE15
1613 ,l_qte_line_rec.ATTRIBUTE2
1614 ,l_qte_line_rec.ATTRIBUTE3
1615 ,l_qte_line_rec.ATTRIBUTE4
1616 ,l_qte_line_rec.ATTRIBUTE5
1617 ,l_qte_line_rec.ATTRIBUTE6
1618 ,l_qte_line_rec.ATTRIBUTE7
1619 ,l_qte_line_rec.ATTRIBUTE8
1620 ,l_qte_line_rec.ATTRIBUTE9
1621 ,l_qte_line_rec.ATTRIBUTE10
1622 ,l_qte_line_rec.ATTRIBUTE11
1623 ,l_qte_line_rec.ATTRIBUTE12
1624 ,l_qte_line_rec.ATTRIBUTE13
1625 ,l_qte_line_rec.ATTRIBUTE14
1626 ,l_qte_line_rec.ATTRIBUTE15 --bug# 3395318
1627 ,l_qte_line_rec.pricing_line_type_indicator;
1628 EXIT WHEN c_getlinetbl%NOTFOUND;
1629 l_qte_line_tbl(l_qte_line_tbl.count+1) := l_qte_line_rec;
1630 END LOOP;
1631 CLOSE c_getlinetbl;
1632 RETURN l_qte_line_tbl;
1633 END getLineTbl;
1634
1635
1636 FUNCTION getLineRec(
1637 p_qte_line_id IN NUMBER
1638 ) RETURN ASO_QUOTE_PUB.QTE_LINE_REC_TYPE
1639 IS
1640
1641 l_qte_line_rec ASO_QUOTE_PUB.QTE_LINE_REC_TYPE;
1642 CURSOR c_getlineRec(l_qte_line_id number) IS
1643 SELECT l.QUOTE_LINE_ID
1644 ,l.CREATION_DATE
1645 ,l.CREATED_BY
1646 ,l.LAST_UPDATE_DATE
1647 ,l.LAST_UPDATED_BY
1648 ,l.LAST_UPDATE_LOGIN
1649 ,l.REQUEST_ID
1650 ,l.PROGRAM_APPLICATION_ID
1651 ,l.PROGRAM_ID
1652 ,l.PROGRAM_UPDATE_DATE
1653 ,l.QUOTE_HEADER_ID
1654 ,l.ORG_ID
1655 ,l.LINE_CATEGORY_CODE
1656 ,l.ITEM_TYPE_CODE
1657 ,l.LINE_NUMBER
1658 ,l.START_DATE_ACTIVE
1659 ,l.END_DATE_ACTIVE
1660 ,l.ORDER_LINE_TYPE_ID
1661 ,l.INVOICE_TO_PARTY_SITE_ID
1662 ,l.INVOICE_TO_PARTY_ID
1663 ,l.ORGANIZATION_ID
1664 ,l.INVENTORY_ITEM_ID
1665 ,l.QUANTITY
1666 ,l.UOM_CODE
1667 ,l.MARKETING_SOURCE_CODE_ID
1668 ,l.PRICE_LIST_ID
1669 ,l.PRICE_LIST_LINE_ID
1670 ,l.CURRENCY_CODE
1671 ,l.LINE_LIST_PRICE
1672 ,l.LINE_ADJUSTED_AMOUNT
1673 ,l.LINE_ADJUSTED_PERCENT
1674 ,l.LINE_QUOTE_PRICE
1675 ,l.RELATED_ITEM_ID
1676 ,l.ITEM_RELATIONSHIP_TYPE
1677 ,l.ACCOUNTING_RULE_ID
1678 ,l.INVOICING_RULE_ID
1679 ,l.SPLIT_SHIPMENT_FLAG
1680 ,l.BACKORDER_FLAG
1681 ,l.ATTRIBUTE_CATEGORY
1682 ,l.ATTRIBUTE1
1683 ,l.ATTRIBUTE2
1684 ,l.ATTRIBUTE3
1685 ,l.ATTRIBUTE4
1686 ,l.ATTRIBUTE5
1687 ,l.ATTRIBUTE6
1688 ,l.ATTRIBUTE7
1689 ,l.ATTRIBUTE8
1690 ,l.ATTRIBUTE9
1691 ,l.ATTRIBUTE10
1692 ,l.ATTRIBUTE11
1693 ,l.ATTRIBUTE12
1694 ,l.ATTRIBUTE13
1695 ,l.ATTRIBUTE14
1696 ,l.ATTRIBUTE15
1697 ,l.MINISITE_ID
1698 From aso_quote_lines l
1699 Where l.QUOTE_LINE_ID = l_QTE_LINE_ID;
1700 begin
1701
1702 open c_getlineRec(p_qte_line_id);
1703 fetch c_getlineRec into
1704 l_qte_line_rec.QUOTE_LINE_ID
1705 ,l_qte_line_rec.CREATION_DATE
1706 ,l_qte_line_rec.CREATED_BY
1707 ,l_qte_line_rec.LAST_UPDATE_DATE
1708 ,l_qte_line_rec.LAST_UPDATED_BY
1709 ,l_qte_line_rec.LAST_UPDATE_LOGIN
1710 ,l_qte_line_rec.REQUEST_ID
1711 ,l_qte_line_rec.PROGRAM_APPLICATION_ID
1712 ,l_qte_line_rec.PROGRAM_ID
1713 ,l_qte_line_rec.PROGRAM_UPDATE_DATE
1714 ,l_qte_line_rec.QUOTE_HEADER_ID
1715 ,l_qte_line_rec.ORG_ID
1716 ,l_qte_line_rec.LINE_CATEGORY_CODE
1717 ,l_qte_line_rec.ITEM_TYPE_CODE
1718 ,l_qte_line_rec.LINE_NUMBER
1719 ,l_qte_line_rec.START_DATE_ACTIVE
1720 ,l_qte_line_rec.END_DATE_ACTIVE
1721 ,l_qte_line_rec.ORDER_LINE_TYPE_ID
1722 ,l_qte_line_rec.INVOICE_TO_PARTY_SITE_ID
1723 ,l_qte_line_rec.INVOICE_TO_PARTY_ID
1724 ,l_qte_line_rec.ORGANIZATION_ID
1725 ,l_qte_line_rec.INVENTORY_ITEM_ID
1726 ,l_qte_line_rec.QUANTITY
1727 ,l_qte_line_rec.UOM_CODE
1728 ,l_qte_line_rec.MARKETING_SOURCE_CODE_ID
1729 ,l_qte_line_rec.PRICE_LIST_ID
1730 ,l_qte_line_rec.PRICE_LIST_LINE_ID
1731 ,l_qte_line_rec.CURRENCY_CODE
1732 ,l_qte_line_rec.LINE_LIST_PRICE
1733 ,l_qte_line_rec.LINE_ADJUSTED_AMOUNT
1734 ,l_qte_line_rec.LINE_ADJUSTED_PERCENT
1735 ,l_qte_line_rec.LINE_QUOTE_PRICE
1736 ,l_qte_line_rec.RELATED_ITEM_ID
1737 ,l_qte_line_rec.ITEM_RELATIONSHIP_TYPE
1738 ,l_qte_line_rec.ACCOUNTING_RULE_ID
1739 ,l_qte_line_rec.INVOICING_RULE_ID
1740 ,l_qte_line_rec.SPLIT_SHIPMENT_FLAG
1741 ,l_qte_line_rec.BACKORDER_FLAG
1742 ,l_qte_line_rec.ATTRIBUTE_CATEGORY
1743 ,l_qte_line_rec.ATTRIBUTE1
1744 ,l_qte_line_rec.ATTRIBUTE2
1745 ,l_qte_line_rec.ATTRIBUTE3
1746 ,l_qte_line_rec.ATTRIBUTE4
1747 ,l_qte_line_rec.ATTRIBUTE5
1748 ,l_qte_line_rec.ATTRIBUTE6
1749 ,l_qte_line_rec.ATTRIBUTE7
1750 ,l_qte_line_rec.ATTRIBUTE8
1751 ,l_qte_line_rec.ATTRIBUTE9
1752 ,l_qte_line_rec.ATTRIBUTE10
1753 ,l_qte_line_rec.ATTRIBUTE11
1754 ,l_qte_line_rec.ATTRIBUTE12
1755 ,l_qte_line_rec.ATTRIBUTE13
1756 ,l_qte_line_rec.ATTRIBUTE14
1757 ,l_qte_line_rec.ATTRIBUTE15
1758 ,l_qte_line_rec.MINISITE_ID;
1759 CLOSE c_getlineRec;
1760 RETURN l_qte_line_rec;
1761 END getLineRec;
1762
1763 FUNCTION getHeaderRec(
1764 p_quote_header_Id IN NUMBER
1765 ) RETURN ASO_QUOTE_PUB.QTE_HEADER_REC_TYPE
1766 IS
1767 CURSOR c_getHeaderRec(p_quote_header_id NUMBER) IS
1768 SELECT last_update_date
1769 ,ORG_ID
1770 ,QUOTE_NAME
1771 ,QUOTE_NUMBER
1772 ,QUOTE_VERSION
1773 ,QUOTE_STATUS_ID
1774 ,QUOTE_SOURCE_CODE
1775 ,QUOTE_EXPIRATION_DATE
1776 ,PRICE_FROZEN_DATE
1777 ,QUOTE_PASSWORD
1778 ,ORIGINAL_SYSTEM_REFERENCE
1779 ,PARTY_ID
1780 ,CUST_ACCOUNT_ID
1781 ,ORG_CONTACT_ID
1782 ,PHONE_ID
1783 ,INVOICE_TO_PARTY_SITE_ID
1784 ,INVOICE_TO_PARTY_ID
1785 ,ORIG_MKTG_SOURCE_CODE_ID
1786 ,MARKETING_SOURCE_CODE_ID
1787 ,ORDER_TYPE_ID
1788 ,QUOTE_CATEGORY_CODE
1789 ,ORDERED_DATE
1790 ,ACCOUNTING_RULE_ID
1791 ,INVOICING_RULE_ID
1792 ,EMPLOYEE_PERSON_ID
1793 ,PRICE_LIST_ID
1794 ,CURRENCY_CODE
1795 ,TOTAL_LIST_PRICE
1796 ,TOTAL_ADJUSTED_AMOUNT
1797 ,TOTAL_ADJUSTED_PERCENT
1798 ,TOTAL_TAX
1799 ,TOTAL_SHIPPING_CHARGE
1800 ,SURCHARGE
1801 ,TOTAL_QUOTE_PRICE
1802 ,PAYMENT_AMOUNT
1803 ,EXCHANGE_RATE
1804 ,EXCHANGE_TYPE_CODE
1805 ,EXCHANGE_RATE_DATE
1806 ,CONTRACT_ID
1807 ,SALES_CHANNEL_CODE
1808 ,ORDER_ID
1809 FROM aso_quote_headers
1810 WHERE quote_header_id = p_quote_header_id;
1811
1812 l_qte_header_rec ASO_QUOTE_PUB.QTE_HEADER_REC_TYPE;
1813
1814
1815 BEGIN
1816 l_qte_header_rec.quote_header_id := p_quote_header_id;
1817
1818 open c_getHeaderRec(l_qte_header_rec.quote_header_id);
1819 fetch c_getHeaderRec into
1820 l_qte_header_rec.last_update_date
1821 ,l_qte_header_rec.ORG_ID
1822 ,l_qte_header_rec.QUOTE_NAME
1823 ,l_qte_header_rec.QUOTE_NUMBER
1824 ,l_qte_header_rec.QUOTE_VERSION
1825 ,l_qte_header_rec.QUOTE_STATUS_ID
1826 ,l_qte_header_rec.QUOTE_SOURCE_CODE
1827 ,l_qte_header_rec.QUOTE_EXPIRATION_DATE
1828 ,l_qte_header_rec.PRICE_FROZEN_DATE
1829 ,l_qte_header_rec.QUOTE_PASSWORD
1830 ,l_qte_header_rec.ORIGINAL_SYSTEM_REFERENCE
1831 ,l_qte_header_rec.PARTY_ID
1832 ,l_qte_header_rec.CUST_ACCOUNT_ID
1833 ,l_qte_header_rec.ORG_CONTACT_ID
1834 ,l_qte_header_rec.PHONE_ID
1835 ,l_qte_header_rec.INVOICE_TO_PARTY_SITE_ID
1836 ,l_qte_header_rec.INVOICE_TO_PARTY_ID
1837 ,l_qte_header_rec.ORIG_MKTG_SOURCE_CODE_ID
1838 ,l_qte_header_rec.MARKETING_SOURCE_CODE_ID
1839 ,l_qte_header_rec.ORDER_TYPE_ID
1840 ,l_qte_header_rec.QUOTE_CATEGORY_CODE
1841 ,l_qte_header_rec.ORDERED_DATE
1842 ,l_qte_header_rec.ACCOUNTING_RULE_ID
1843 ,l_qte_header_rec.INVOICING_RULE_ID
1844 ,l_qte_header_rec.EMPLOYEE_PERSON_ID
1845 ,l_qte_header_rec.PRICE_LIST_ID
1846 ,l_qte_header_rec.CURRENCY_CODE
1847 ,l_qte_header_rec.TOTAL_LIST_PRICE
1848 ,l_qte_header_rec.TOTAL_ADJUSTED_AMOUNT
1849 ,l_qte_header_rec.TOTAL_ADJUSTED_PERCENT
1850 ,l_qte_header_rec.TOTAL_TAX
1851 ,l_qte_header_rec.TOTAL_SHIPPING_CHARGE
1852 ,l_qte_header_rec.SURCHARGE
1853 ,l_qte_header_rec.TOTAL_QUOTE_PRICE
1854 ,l_qte_header_rec.PAYMENT_AMOUNT
1855 ,l_qte_header_rec.EXCHANGE_RATE
1856 ,l_qte_header_rec.EXCHANGE_TYPE_CODE
1857 ,l_qte_header_rec.EXCHANGE_RATE_DATE
1858 ,l_qte_header_rec.CONTRACT_ID
1859 ,l_qte_header_rec.SALES_CHANNEL_CODE
1860 ,l_qte_header_rec.ORDER_ID;
1861 CLOSE c_getHeaderRec;
1862
1863 RETURN l_qte_header_rec;
1864
1865 END getHeaderRec;
1866
1867 FUNCTION getHeaderPaymentTbl(
1868 p_quote_header_Id IN NUMBER
1869 ) RETURN ASO_QUOTE_PUB.PAYMENT_TBL_TYPE
1870 IS
1871
1872 l_index number :=1;
1873 l_payment_tbl aso_quote_pub.payment_tbl_type;
1874 l_payment_rec aso_quote_pub.payment_rec_type;
1875
1876
1877 CURSOR c_getPaymentRec(p_quote_header_id number) is
1878 SELECT payment_id
1879 ,creation_date
1880 ,created_by
1881 ,last_update_date
1882 ,last_updated_by
1883 ,last_update_login
1884 ,request_id
1885 ,program_application_id
1886 ,program_id
1887 ,program_update_date
1888 ,quote_header_id
1889 ,quote_line_id
1890 ,payment_type_code
1891 ,payment_ref_number
1892 ,payment_option
1893 ,payment_term_id
1894 ,credit_card_code
1895 ,credit_card_holder_name
1896 ,credit_card_expiration_date
1897 ,credit_card_approval_code
1898 ,credit_card_approval_date
1899 ,payment_amount
1900 ,attribute_category
1901 ,attribute1
1902 ,attribute2
1903 ,attribute3
1904 ,attribute4
1905 ,attribute5
1906 ,attribute6
1907 ,attribute7
1908 ,attribute8
1909 ,attribute9
1910 ,attribute10
1911 ,attribute11
1912 ,attribute12
1913 ,attribute13
1914 ,attribute14
1915 ,attribute15
1916 ,trxn_extension_id
1917 FROM ASO_PAYMENTS
1918 WHERE quote_header_id = p_quote_header_id
1919 AND quote_line_id IS NULL;
1920
1921 CURSOR c_getInstrumentId(p_trxn_extn_id number) is
1922 SELECT instr_assignment_id
1923 FROM IBY_TRXN_EXTENSIONS_V
1924 WHERE trxn_extension_id = p_trxn_extn_id;
1925
1926 BEGIN
1927
1928 OPEN c_getPaymentRec(p_quote_header_id);
1929 LOOP
1930 FETCH c_getPaymentRec INTO
1931 l_payment_rec.payment_id
1932 ,l_payment_rec.creation_date
1933 ,l_payment_rec.created_by
1934 ,l_payment_rec.last_update_date
1935 ,l_payment_rec.last_updated_by
1936 ,l_payment_rec.last_update_login
1937 ,l_payment_rec.request_id
1938 ,l_payment_rec.program_application_id
1939 ,l_payment_rec.program_id
1940 ,l_payment_rec.program_update_date
1941 ,l_payment_rec.quote_header_id
1942 ,l_payment_rec.quote_line_id
1943 ,l_payment_rec.payment_type_code
1944 ,l_payment_rec.payment_ref_number
1945 ,l_payment_rec.payment_option
1946 ,l_payment_rec.payment_term_id
1947 ,l_payment_rec.credit_card_code
1948 ,l_payment_rec.credit_card_holder_name
1949 ,l_payment_rec.credit_card_expiration_date
1950 ,l_payment_rec.credit_card_approval_code
1951 ,l_payment_rec.credit_card_approval_date
1952 ,l_payment_rec.payment_amount
1953 ,l_payment_rec.attribute_category
1954 ,l_payment_rec.attribute1
1955 ,l_payment_rec.attribute2
1956 ,l_payment_rec.attribute3
1957 ,l_payment_rec.attribute4
1958 ,l_payment_rec.attribute5
1959 ,l_payment_rec.attribute6
1960 ,l_payment_rec.attribute7
1961 ,l_payment_rec.attribute8
1962 ,l_payment_rec.attribute9
1963 ,l_payment_rec.attribute10
1964 ,l_payment_rec.attribute11
1965 ,l_payment_rec.attribute12
1966 ,l_payment_rec.attribute13
1967 ,l_payment_rec.attribute14
1968 ,l_payment_rec.attribute15
1969 ,l_payment_rec.trxn_extension_id;
1970 IF l_payment_rec.trxn_extension_id IS NOT NULL THEN
1971 OPEN c_getInstrumentId(l_payment_rec.trxn_extension_id);
1972 LOOP
1973 FETCH c_getInstrumentId INTO l_payment_rec.instr_assignment_id;
1974 EXIT WHEN c_getInstrumentId%NOTFOUND;
1975 END LOOP;
1976 CLOSE c_getInstrumentId;
1977 END IF;
1978 EXIT WHEN c_getPaymentRec%NOTFOUND;
1979 l_payment_tbl(l_index) := l_payment_rec;
1980 l_index := l_index +1;
1981 END LOOP;
1982 CLOSE c_getPaymentRec;
1983
1984 RETURN l_payment_tbl;
1985 END getHeaderPaymentTbl;
1986
1987 FUNCTION getShareePrivilege(
1988 p_quote_header_Id IN NUMBER
1989 ,p_sharee_number IN NUMBER
1990 ) RETURN VARCHAR2
1991 IS
1992 l_privilege_type_code VARCHAR2(100) := 'X';
1993 BEGIN
1994
1995 SELECT update_privilege_type_code
1996 INTO l_privilege_type_code
1997 FROM IBE_SH_QUOTE_ACCESS
1998 WHERE quote_header_id = p_quote_header_id
1999 AND quote_sharee_number = p_sharee_number;
2000
2001 RETURN l_privilege_type_code;
2002
2003 EXCEPTION
2004 WHEN TOO_MANY_ROWS THEN
2005 RETURN 'XM';
2006 WHEN NO_DATA_FOUND THEN
2007 RETURN 'XN';
2008 END getShareePrivilege;
2009
2010 FUNCTION getUserType(
2011 p_partyId IN Varchar2
2012 ) RETURN VARCHAR2
2013 IS
2014 l_PartyType Varchar2(30);
2015
2016 Cursor c_hz_parties(c_party_id NUMBER) IS
2017 SELECT party_type
2018 FROM hz_parties
2019 WHERE party_id = c_party_id;
2020 c_hz_parties_rec c_hz_parties%rowtype;
2021
2022 BEGIN
2023 FOR c_hz_parties_rec IN c_hz_parties(p_partyId) LOOP
2024 l_PartyType := rtrim(c_hz_parties_rec.party_type);
2025 END LOOP;
2026
2027 If (l_PartyType = 'PERSON') Then
2028 return 'B2C';
2029 else
2030 return 'B2B';
2031 End If;
2032
2033 END getUserType;
2034
2035 PROCEDURE validateQuoteLastUpdateDate(
2036 p_api_version_number IN NUMBER
2037 ,p_quote_header_id IN NUMBER
2038 ,p_last_update_date IN DATE
2039 ,X_Return_Status OUT NOCOPY VARCHAR2
2040 ,X_Msg_Count OUT NOCOPY NUMBER
2041 ,X_Msg_Data OUT NOCOPY VARCHAR2
2042 )
2043 IS
2044 l_api_name CONSTANT VARCHAR2(30) := 'validateQuoteLastUpdateDate';
2045 l_api_version CONSTANT NUMBER := 1.0;
2046 l_last_update_date DATE;
2047 BEGIN
2048 SAVEPOINT validateLastUpdate_pvt;
2049 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2050 P_Api_Version_Number,
2051 l_api_name,
2052 G_PKG_NAME )
2053 THEN
2054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2055 END IF;
2056
2057 x_return_status := FND_API.G_RET_STS_SUCCESS;
2058
2059
2060 l_last_update_date :=IBE_Quote_Misc_pvt.getQuoteLastUpdatedate(p_quote_header_id);
2061 if (l_last_update_date <> p_last_update_date) then
2062 -- raise error
2063 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2064 FND_MESSAGE.Set_Name('IBE', 'IBE_SC_QUOTE_NEED_REFRESH');
2065 FND_MSG_PUB.ADD;
2066 END IF;
2067 RAISE FND_API.G_EXC_ERROR; -- need error message
2068 end if;
2069
2070 -- Standard call to get message count and if count is 1, get message info.
2071
2072 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2073 p_count => x_msg_count,
2074 p_data => x_msg_data);
2075 EXCEPTION
2076 WHEN FND_API.G_EXC_ERROR THEN
2077 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2078 IBE_Util.Debug('Expected exception in IBE_QUOTE_MISC_PVT.ValidateQuotelastUpdateDate');
2079 END IF;
2080 ROLLBACK TO validateLastUpdate_pvt;
2081 x_return_status := FND_API.G_RET_STS_ERROR;
2082 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2083 p_count => x_msg_count,
2084 p_data => x_msg_data);
2085
2086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2088 IBE_Util.Debug('Unexpected exception in IBE_QUOTE_MISC_PVT.ValidateQuotelastUpdateDate');
2089 END IF;
2090 ROLLBACK TO validateLastUpdate_pvt;
2091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2092 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2093 p_count => x_msg_count,
2094 p_data => x_msg_data);
2095
2096 WHEN OTHERS THEN
2097
2098 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2099 IBE_Util.Debug('Unknown exception in IBE_QUOTE_MISC_PVT.ValidateQuotelastUpdateDate');
2100 END IF;
2101 ROLLBACK TO validateLastUpdate_pvt;
2102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2103 IF FND_MSG_PUB.Check_Msg_Level
2104 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2105 THEN
2106 FND_MSG_PUB.Add_Exc_Msg
2107 ( G_PKG_NAME,
2108 l_api_name
2109 );
2110 END IF;
2111 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2112 p_count => x_msg_count,
2113 p_data => x_msg_data);
2114 END validateQuoteLastUpdateDate;
2115
2116
2117 PROCEDURE getQuoteOwner(
2118 p_api_version_number IN NUMBER
2119 ,p_quote_header_Id IN NUMBER
2120
2121 ,x_party_id OUT NOCOPY NUMBER
2122 ,x_cust_account_id OUT NOCOPY NUMBER
2123 ,X_Return_Status OUT NOCOPY VARCHAR2
2124 ,X_Msg_Count OUT NOCOPY NUMBER
2125 ,X_Msg_Data OUT NOCOPY VARCHAR2
2126 )
2127 is
2128 l_api_name CONSTANT VARCHAR2(30) := 'getQuoteOwner';
2129 l_api_version CONSTANT NUMBER := 1.0;
2130
2131 l_party_id number := FND_API.G_MISS_NUM;
2132 l_cust_account_id number := FND_API.G_MISS_NUM;
2133
2134 CURSOR getuserinfo(p_quote_header_id NUMBER) IS
2135 SELECT party_id, cust_account_id
2136 FROM aso_quote_headers
2137 WHERE quote_header_id = p_quote_header_id;
2138
2139 BEGIN
2140 SAVEPOINT getQuoteOwner_pvt;
2141 -- Standard call to check for call compatibility.
2142 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2143 P_Api_Version_Number,
2144 l_api_name,
2145 G_PKG_NAME )
2146 THEN
2147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2148 END IF;
2149
2150 x_return_status := FND_API.G_RET_STS_SUCCESS;
2151
2152 -- API body
2153
2154 open getuserinfo(p_quote_header_id);
2155 fetch getuserinfo into l_party_id
2156 ,l_cust_account_id;
2157 close getuserinfo;
2158
2159 IF (l_party_id = FND_API.G_MISS_NUM OR l_party_id IS NULL
2160 OR l_cust_account_id = FND_API.G_MISS_NUM OR l_cust_account_id IS NULL) THEN
2161 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2162 FND_MESSAGE.Set_Name('IBE', 'IBE_SC_NO_QUOTE_OWNER');
2163 FND_MSG_PUB.ADD;
2164 END IF;
2165 RAISE FND_API.G_EXC_ERROR;
2166 END IF;
2167 -- End of API body.
2168 x_party_id := l_party_id;
2169 x_cust_account_id := l_cust_account_id;
2170 -- Standard call to get message count and if count is 1, get message info.
2171 FND_MSG_PUB.Count_And_Get
2172 ( p_encoded => FND_API.G_FALSE,
2173 p_count => x_msg_count,
2174 p_data => x_msg_data
2175 );
2176
2177
2178
2179 EXCEPTION
2180
2181 WHEN FND_API.G_EXC_ERROR THEN
2182 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2183 IBE_Util.Debug('Expected exception in IBE_QUOTE_MISC_PVT.getQuoteOwner');
2184 END IF;
2185
2186 ROLLBACK to getQuoteOwner_pvt;
2187 x_return_status := FND_API.G_RET_STS_ERROR;
2188 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2189 p_count => x_msg_count,
2190 p_data => x_msg_data);
2191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2192 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2193 IBE_Util.Debug('uNExpected exception in IBE_QUOTE_MISC_PVT.getQuoteOwner');
2194 END IF;
2195 ROLLBACK to getQuoteOwner_pvt;
2196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2197 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2198 p_count => x_msg_count,
2199 p_data => x_msg_data);
2200
2201 WHEN OTHERS THEN
2202 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2203 IBE_Util.Debug('Unknown exception in IBE_QUOTE_MISC_PVT.getQuoteOwner');
2204 END IF;
2205 ROLLBACK to getQuoteOwner_pvt;
2206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2207 IF FND_MSG_PUB.Check_Msg_Level
2208 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2209 THEN
2210 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2211 l_api_name);
2212 END IF;
2213 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2214 p_count => x_msg_count,
2215 p_data => x_msg_data);
2216
2217 END getQuoteOwner;
2218
2219
2220 PROCEDURE Get_Shared_Quote(
2221 p_api_version_number IN NUMBER ,
2222 p_quote_password IN VARCHAR2 := FND_API.G_MISS_CHAR,
2223 p_quote_number IN NUMBER ,
2224 p_quote_version IN NUMBER := FND_API.G_MISS_NUM ,
2225 x_quote_header_id OUT NOCOPY NUMBER ,
2226 x_last_update_date OUT NOCOPY DATE ,
2227 x_return_status OUT NOCOPY VARCHAR2 ,
2228 x_msg_count OUT NOCOPY NUMBER ,
2229 x_msg_data OUT NOCOPY VARCHAR2
2230 )
2231 IS
2232 l_api_name CONSTANT VARCHAR2(30) := 'getshareeQuote';
2233 l_api_version CONSTANT NUMBER := 1.0;
2234
2235 l_quote_version NUMBER;
2236 l_quote_header_id NUMBER := FND_API.G_MISS_NUM;
2237 l_last_update_date DATE := FND_API.G_MISS_DATE;
2238
2239 l_sql1 VARCHAR2(200) :=
2240 'SELECT quote_header_id,
2241 last_update_date
2242 FROM aso_quote_headers
2243 WHERE quote_number = :1
2244 AND quote_version = :2 ';
2245
2246 l_sql2 VARCHAR2(100) := 'AND quote_password = :3';
2247 BEGIN
2248 SAVEPOINT get_shared_quote;
2249 -- Standard call to check for call compatibility.
2250 IF NOT FND_API.Compatible_API_Call(l_api_version,
2251 p_api_version_number,
2252 l_api_name,
2253 G_PKG_NAME) THEN
2254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2255 END IF;
2256
2257 x_return_status := FND_API.G_RET_STS_SUCCESS;
2258
2259 -- API body
2260 l_quote_version := p_quote_version;
2261
2262 IF l_quote_version IS NULL
2263 OR l_quote_version = FND_API.G_MISS_NUM THEN
2264 SELECT MAX(quote_version)
2265 INTO l_quote_version
2266 FROM aso_quote_headers
2267 WHERE quote_number = p_quote_number;
2268 END IF;
2269
2270 IF p_quote_password IS NULL
2271 OR p_quote_password = FND_API.G_MISS_CHAR THEN
2272 EXECUTE IMMEDIATE l_sql1
2273 INTO l_quote_header_id, l_last_update_date
2274 USING p_quote_number, l_quote_version;
2275 ELSE
2276 EXECUTE IMMEDIATE l_sql1 || l_sql2
2277 INTO l_quote_header_id, l_last_update_date
2278 USING p_quote_number, l_quote_version, p_quote_password;
2279 END IF;
2280
2281 IF l_quote_header_id IS NULL
2282 OR l_quote_header_id = FND_API.G_MISS_NUM THEN
2283 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2284 FND_MESSAGE.Set_Name('IBE', 'IBE_SC_NO_SHARE_QUOTE');
2285 FND_MSG_PUB.ADD;
2286 END IF;
2287
2288 RAISE FND_API.G_EXC_ERROR;
2289 END IF;
2290
2291 x_quote_header_id := l_quote_header_id;
2292 x_last_update_date := l_last_update_date;
2293 EXCEPTION
2294 WHEN FND_API.G_EXC_ERROR THEN
2295 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2296 IBE_Util.Debug('Expected exception in IBE_QUOTE_MISC_PVT.get_shared_quote');
2297 END IF;
2298 ROLLBACK to get_shared_quote;
2299 x_return_status := FND_API.G_RET_STS_ERROR;
2300 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2301 p_count => x_msg_count,
2302 p_data => x_msg_data);
2303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2304 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2305 IBE_Util.Debug('Unexpected exception in IBE_QUOTE_MISC_PVT.get_shared_quote');
2306 END IF;
2307 ROLLBACK to get_shared_quote;
2308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2309 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2310 p_count => x_msg_count,
2311 p_data => x_msg_data);
2312 WHEN OTHERS THEN
2313 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2314 IBE_Util.Debug('Unknown exception in IBE_QUOTE_MISC_PVT.get_shared_quote');
2315 END IF;
2316 ROLLBACK to get_shared_quote;
2317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2318 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2319 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2320 l_api_name);
2321 END IF;
2322
2323 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2324 p_count => x_msg_count,
2325 p_data => x_msg_data);
2326 END Get_Shared_Quote;
2327
2328
2329 -- direct entry
2330 -- load inventory_item_ids based on customer number
2331 PROCEDURE Load_Item_IDs(
2332 p_api_version IN NUMBER := 1 ,
2333 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2334 p_cust_id IN NUMBER ,
2335 p_cust_item_number_tbl IN jtf_varchar2_table_100 ,
2336 p_organization_id IN NUMBER ,
2337 p_minisite_id IN NUMBER ,
2338 x_inventory_item_id_tbl OUT NOCOPY jtf_number_table ,
2339 x_return_status OUT NOCOPY VARCHAR2 ,
2340 x_msg_count OUT NOCOPY NUMBER ,
2341 x_msg_data OUT NOCOPY VARCHAR2
2342 )
2343 IS
2344 l_api_name CONSTANT VARCHAR2(30) := 'Load_Item_IDs';
2345 l_api_version CONSTANT NUMBER := 1.0;
2346
2347 l_error_code VARCHAR2(30);
2348 l_error_flag VARCHAR2(30);
2349 l_error_message VARCHAR2(300);
2350
2351 l_attribute_value VARCHAR2(30);
2352 l_count NUMBER;
2353 l_item_exists NUMBER;
2354 l_inventory_item_id NUMBER;
2355 BEGIN
2356 /*inv_debug.message('ssia', 'customer id is ' || p_cust_id);
2357 inv_debug.message('ssia', 'p_organization_id is ' || p_organization_id);
2358 inv_debug.message('ssia', 'p_minisite_id is ' || p_minisite_id);*/
2359
2360 -- Standard call to check for call compatibility
2361 IF NOT FND_API.Compatible_API_Call(l_api_version,
2362 p_api_version,
2363 l_api_name ,
2364 g_pkg_name)
2365 THEN
2366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2367 END IF;
2368
2369 -- Initialize message list if p_init_msg_list is set to TRUE
2370 IF FND_API.to_Boolean(p_init_msg_list) THEN
2371 FND_MSG_PUB.initialize;
2372 END IF;
2373
2374 -- Initialize API return status to success
2375 x_return_status := FND_API.G_RET_STS_SUCCESS;
2376
2377 -- API body
2378 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2379 IBE_Util.Debug('IBE_Quote_Misc_pvt.Load_Item_IDs(+)...');
2380 END IF;
2381
2382 l_count := p_cust_item_number_tbl.COUNT;
2383
2384 x_inventory_item_id_Tbl := JTF_NUMBER_TABLE();
2385 x_inventory_item_id_Tbl.extend(l_count);
2386
2387
2388 FOR i IN 1..l_count LOOP
2389 IF p_cust_item_number_tbl(i) IS NOT NULL THEN
2390 --inv_debug.message('ssia', 'p_cust_item_number is ' || p_cust_item_number_tbl(i));
2391
2392 -- get inventory_item_id for each customer_item_number
2393 INV_CUSTOMER_ITEM_GRP.CI_Attribute_Value(
2394 z_customer_id => p_cust_id ,
2395 z_customer_item_number => p_cust_item_number_tbl(i),
2396 z_organization_id => p_organization_id ,
2397 attribute_name => 'INVENTORY_ITEM_ID' ,
2398 error_code => l_error_code ,
2399 error_flag => l_error_flag ,
2400 error_message => l_error_message ,
2401 attribute_value => l_attribute_value
2402 );
2403
2404 IF l_error_flag = 'Y' THEN
2405 --inv_debug.message('ssia', 'got error from inv_customer_item_grp');
2406 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2407 FND_Message.Set_Name('IBE', 'IBE_SC_INV_CUSTOM_ITEM_ERROR');
2408 FND_Message.Set_Token('INVMSG', l_error_message);
2409 FND_MSG_PUB.Add;
2410 END IF;
2411 END IF;
2412
2413
2414 IF l_attribute_value IS NOT NULL THEN
2415 l_inventory_item_id := to_number(l_attribute_value);
2416 --inv_debug.message('ssia', 'l_inventory_item_id is ' || l_inventory_item_id);
2417 select count(s.inventory_item_id)
2418 into l_item_exists
2419 from ibe_dsp_section_items s, ibe_dsp_msite_sct_items b
2420 where s.section_item_id = b.section_item_id
2421 and b.mini_site_id = p_minisite_id
2422 and s.inventory_item_id = l_inventory_item_id
2423 and (s.end_date_active > sysdate or s.end_date_active is null )
2424 and s.start_date_active < sysdate;
2425
2426 if( l_item_exists > 0 ) then
2427 --inv_debug.message('ssia', 'item exists');
2428 x_inventory_item_id_tbl(i) := to_number(l_attribute_value);
2429 else
2430 --inv_debug.message('ssia', 'item not exists');
2431 x_inventory_item_id_tbl(i) := 0;
2432 end if;
2433 END IF;
2434 END IF;
2435 END LOOP;
2436
2437
2438 -- Standard call to get message count and if count is 1, get message info.
2439 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2440 p_count => x_msg_count,
2441 p_data => x_msg_data);
2442 --inv_debug.message('ssia', 'x_msg_count is ' || x_msg_count);
2443 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2444 IBE_Util.Debug('Ibe_Shopcart_Pvt10.Load_Merchant_InvId(-)...');
2445 END IF;
2446 EXCEPTION
2447 WHEN FND_API.G_EXC_ERROR THEN
2448 x_return_status := FND_API.G_RET_STS_ERROR;
2449 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2450 p_count => x_msg_count,
2451 p_data => x_msg_data);
2452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2454 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2455 p_count => x_msg_count,
2456 p_data => x_msg_data);
2457 WHEN OTHERS THEN
2458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2459 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2460 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2461 l_api_name);
2462 END IF;
2463
2464 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2465 p_count => x_msg_count,
2466 p_data => x_msg_data);
2467
2468 END Load_Item_IDs;
2469
2470
2471
2472
2473
2474 procedure get_load_errors(
2475 X_reason_code OUT NOCOPY JTF_VARCHAR2_TABLE_100 ,
2476 p_api_version IN NUMBER := 1.0 ,
2477 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2478 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2479 x_return_status OUT NOCOPY VARCHAR2 ,
2480 x_msg_count OUT NOCOPY NUMBER ,
2481 x_msg_data OUT NOCOPY VARCHAR2 ,
2482 P_quote_header_id IN number := FND_API.G_MISS_NUM,
2483 P_Load_type IN number := FND_API.G_MISS_NUM,
2484 P_quote_number IN number := FND_API.G_MISS_NUM,
2485 P_quote_version IN number := FND_API.G_MISS_NUM,
2486 P_party_id IN number := FND_API.G_MISS_NUM, -- only involved in sharee w/o retr num
2487 P_cust_account_id IN number := FND_API.G_MISS_NUM, -- only involved in sharee w/o retr num
2488 p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM,
2489 P_share_type IN number := -1, -- defaulted to no share type
2490 p_access_level IN number := 0) is
2491
2492 Cursor c_cart_columns (quote_hdr_id number) is
2493 select resource_id, status_code, publish_flag, quote_expiration_date,
2494 max_version_flag , order_id, quote_name
2495 from aso_quote_headers_all a, aso_quote_statuses_vl b
2496 where quote_header_id = quote_hdr_id
2497 and a.quote_status_id = b.quote_status_id;
2498
2499 Cursor c_cart_from_number(quote_num number, quote_ver number) is
2500 select quote_header_id
2501 from aso_quote_headers_all a
2502 where quote_number = quote_num
2503 and quote_version = quote_ver;
2504
2505 Cursor c_retrieval_number (c_retrieval_number NUMBER) is
2506 select quote_sharee_number, quote_sharee_id, end_date_active, update_privilege_type_code, quote_header_id
2507 from IBE_SH_QUOTE_ACCESS
2508 where quote_sharee_number = c_retrieval_number;
2509
2510 Cursor c_recipient_no_retnum (c_quote_header_id NUMBER, c_party_id NUMBER, c_account_id NUMBER) is
2511 select quote_sharee_number, quote_sharee_id, end_date_active, update_privilege_type_code
2512 from IBE_SH_QUOTE_ACCESS
2513 where quote_header_id = c_quote_header_id
2514 and party_id = c_party_id
2515 and cust_account_id = c_account_id;
2516
2517
2518
2519 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_Quote_Misc_pvt';
2520 l_api_name CONSTANT VARCHAR2(50) := 'Get_load_errors_pvt';
2521 l_api_version NUMBER := 1.0;
2522 -- these constants need to be kept in sync w/ Quote.java's static defines
2523 L_CART_LOAD_TYPE CONSTANT number := 0;
2524 L_QUOTE_LOAD_TYPE CONSTANT number := 1;
2525 L_LOAD_FORUPDATE CONSTANT number := 2;
2526 L_LOAD_EXPRESSORDER CONSTANT number := 3;
2527
2528 L_NO_SHARE_TYPE CONSTANT number := -1;
2529 L_UN_SHARED_TYPE CONSTANT number := 0;
2530 L_SHARED_BY_TYPE CONSTANT number := 1;
2531 L_SHARED_TO_TYPE CONSTANT number := 2;
2532
2533 L_NO_ACCESS_LEVEL CONSTANT number := 0;
2534 L_READ_ONLY CONSTANT number := 1;
2535 L_UPDATE CONSTANT number := 2;
2536 L_FULL CONSTANT number := 3;
2537
2538 l_quote_header_id NUMBER;
2539 l_resource_id number :=fnd_api.g_miss_num;
2540 l_status_code varchar2(100) :=fnd_api.g_miss_char;
2541 l_publish_flag varchar2(10) :=fnd_api.g_miss_char;
2542 l_quote_type varchar2(10) :=fnd_api.g_miss_char;
2543 l_return_status VARCHAR2(100);
2544 l_msg_count NUMBER;
2545 l_msg_data VARCHAR2(2000);
2546 l_max_version_flag varchar2(2);
2547 l_expiration_date date;
2548 table_counter number := 1;
2549 l_end_date_active DATE;
2550 l_order_id NUMBER;
2551 l_recipient_id NUMBER;
2552 l_access_code VARCHAR2(10) :=fnd_api.g_miss_char;
2553 l_quote_name VARCHAR2(2000);
2554 l_exp_quote_header_id NUMBER;
2555
2556 rec_cart_columns c_cart_columns%rowtype;
2557 rec_cart_from_number c_cart_from_number%rowtype;
2558 rec_retrieval_number c_retrieval_number%rowtype;
2559 rec_recipient_no_retnum c_recipient_no_retnum%rowtype;
2560
2561 Begin
2562 -- Standard Start of API savepoint
2563 SAVEPOINT Get_load_errors_pvt;
2564
2565 -- Standard call to check for call compatibility.
2566 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
2567 p_api_version,
2568 L_API_NAME ,
2569 G_PKG_NAME )
2570 THEN
2571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2572 END IF;
2573
2574 -- Initialize message list if p_init_msg_list is set to TRUE.
2575 IF FND_API.To_Boolean(p_init_msg_list) THEN
2576 FND_Msg_Pub.initialize;
2577 END IF;
2578
2579 -- Initialize API return status to success
2580 x_return_status := FND_API.G_RET_STS_SUCCESS;
2581 --Start of API Body
2582 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2583 IBE_UTIL.debug('incoming quote_header_id in get_load_errors is '||p_quote_header_id);
2584 IBE_UTIL.debug('incoming retrievalnumber in get_load_errors is '||p_retrieval_number);
2585 IBE_UTIL.debug('incoming partyid in get_load_errors is '||p_party_id);
2586 IBE_UTIL.debug('incoming accountid in get_load_errors is '||p_cust_account_id);
2587 IBE_UTIL.debug('incoming sharetype in get_load_errors is '||p_share_type);
2588 IBE_UTIL.debug('incoming accesslevel in get_load_errors is '||p_access_level);
2589 END IF;
2590
2591 X_reason_code := JTF_VARCHAR2_TABLE_100();
2592 If (p_quote_header_id <> fnd_api.g_miss_num) then
2593 l_exp_quote_header_id := p_quote_header_id;
2594 For rec_cart_columns in c_cart_columns(p_quote_header_id) loop
2595 L_resource_id := rec_cart_columns.resource_id;
2596 l_status_code := rec_cart_columns.status_code;
2597 l_publish_flag := rec_cart_columns.publish_flag;
2598 l_expiration_date := rec_cart_columns.quote_expiration_date;
2599 l_max_version_flag := rec_cart_columns.max_version_flag;
2600 l_order_id := rec_cart_columns.order_id;
2601 l_quote_name := rec_cart_columns.quote_name;
2602 exit when c_cart_columns%notfound;
2603 end loop;
2604 Elsif ((p_quote_number <> fnd_api.g_miss_num) and (p_quote_version <> fnd_api.g_miss_num)) then
2605 For rec_cart_from_number in c_cart_from_number(p_quote_number, p_quote_version) loop
2606 l_quote_header_id := rec_cart_from_number.quote_header_id;
2607 For rec_cart_columns in c_cart_columns(l_quote_header_id) loop
2608 L_resource_id := rec_cart_columns.resource_id;
2609 l_status_code := rec_cart_columns.status_code;
2610 l_publish_flag := rec_cart_columns.publish_flag;
2611 l_expiration_date := rec_cart_columns.quote_expiration_date;
2612 l_max_version_flag := rec_cart_columns.max_version_flag;
2613 exit when c_cart_columns%notfound;
2614 end loop;
2615 exit when c_cart_from_number%notfound;
2616 end loop;
2617 End if;
2618
2619 -- 1st half of errors for share information
2620 -- only do these checks if given retrieval number or qtehdrid, ptyid, acctid, and sharetype
2621 If((p_retrieval_number <> FND_API.G_MISS_NUM) or
2622 ((p_retrieval_number = FND_API.G_MISS_NUM)
2623 and (p_share_type = L_SHARED_TO_TYPE)
2624 and (p_party_id <> FND_API.G_MISS_NUM)
2625 and (p_cust_account_id <> FND_API.G_MISS_NUM)
2626 and (p_quote_header_id <> FND_API.G_MISS_NUM))) then
2627 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2628 IBE_UTIL.debug('Checking for recipient info...');
2629 end if;
2630 If(p_retrieval_number <> FND_API.G_MISS_NUM) then
2631 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2632 IBE_UTIL.debug('get recipient info based on retrieval number');
2633 end if;
2634 for rec_retrieval_number in c_retrieval_number(p_retrieval_number) loop
2635 l_end_date_active := rec_retrieval_number.end_date_active;
2636 l_recipient_id := rec_retrieval_number.quote_sharee_id;
2637 l_access_code := rec_retrieval_number.update_privilege_type_code;
2638 l_quote_header_id := rec_retrieval_number.quote_header_id;
2639 exit when c_retrieval_number%NOTFOUND;
2640 end loop;
2641 if((p_quote_header_id = fnd_api.g_miss_num) and (l_quote_header_id is not null)) then
2642 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2643 IBE_UTIL.debug('tried to load with retreival number only (no cartid)');
2644 IBE_UTIL.debug('but the retrieval number was valid so get cart info... ');
2645 end if;
2646 l_exp_quote_header_id := l_quote_header_id;
2647 For rec_cart_columns in c_cart_columns(l_quote_header_id) loop
2648 L_resource_id := rec_cart_columns.resource_id;
2649 l_status_code := rec_cart_columns.status_code;
2650 l_publish_flag := rec_cart_columns.publish_flag;
2651 l_expiration_date := rec_cart_columns.quote_expiration_date;
2652 l_max_version_flag := rec_cart_columns.max_version_flag;
2653 l_quote_name := rec_cart_columns.quote_name;
2654 exit when c_cart_columns%notfound;
2655 end loop;
2656 end if;
2657 end if;
2658
2659 If((p_retrieval_number = FND_API.G_MISS_NUM)
2660 and (p_share_type = L_SHARED_TO_TYPE)
2661 and (p_party_id <> FND_API.G_MISS_NUM)
2662 and (p_cust_account_id <> FND_API.G_MISS_NUM)
2663 and (p_quote_header_id <> FND_API.G_MISS_NUM)) then
2664 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2665 IBE_UTIL.debug('get recipient info based on cartid, partyid, acctid');
2666 end if;
2667 for rec_recipient_no_retnum in c_recipient_no_retnum(p_quote_header_id, p_party_id, p_cust_account_id) loop
2668 l_end_date_active := rec_recipient_no_retnum.end_date_active;
2669 l_recipient_id := rec_recipient_no_retnum.quote_sharee_id;
2670 l_access_code := rec_recipient_no_retnum.update_privilege_type_code;
2671 exit when c_retrieval_number%NOTFOUND;
2672 end loop;
2673 end if;
2674
2675 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2676 IBE_UTIL.debug('l_end_date_active: '||TO_CHAR(l_end_date_active, 'mm/dd/yyyy:hh24:MI:SS'));
2677 IBE_UTIL.debug('l_recipient_id: '||l_recipient_id);
2678 IBE_UTIL.debug('l_access_code: '||l_access_code);
2679 end if;
2680
2681 If ((p_retrieval_number <> FND_API.G_MISS_NUM) and (l_recipient_id is NULL)) then
2682 -- if we were given a retrieval number and it was not in the database
2683 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2684 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_RETRIEVAL_NUM');
2685 END IF;
2686 if(table_counter = 1) then
2687 x_reason_code.extend();
2688 X_reason_code(table_counter) := 'IBE_SC_INVALID_RETRIEVAL_NUM';
2689 Table_counter := table_counter+1;
2690 end if;
2691
2692 Elsif (l_recipient_id is NULL) then
2693 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2694 IBE_UTIL.debug('Including the Error code: IBE_SC_ERR_PRIVILEGE');
2695 END IF;
2696 if(table_counter = 1) then
2697 x_reason_code.extend();
2698 X_reason_code(table_counter) := 'IBE_SC_ERR_PRIVILEGE';
2699 Table_counter := table_counter+1;
2700 end if;
2701
2702 Elsif ((p_access_level = L_UPDATE) or (p_access_level = L_FULL)) then
2703 -- if a certain access level was passed in, then see if the db access level is sufficient
2704 if (p_access_level = L_UPDATE and (l_access_code <> 'F' or l_access_code <> 'A')) or
2705 (p_access_level = L_FULL and (l_access_code <> 'A')) then
2706 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2707 IBE_UTIL.debug('Including the Error code: IBE_SC_ERR_PRIVILEGE');
2708 END IF;
2709 if(table_counter = 1) then
2710 x_reason_code.extend();
2711 X_reason_code(table_counter) := 'IBE_SC_ERR_PRIVILEGE';
2712 Table_counter := table_counter+1;
2713 end if;
2714 end if;
2715
2716 Elsif (Upper(l_status_code) = 'INACTIVE') then
2717 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2718 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_DELETED');
2719 END IF;
2720 if(table_counter = 1) then
2721 x_reason_code.extend();
2722 X_reason_code(table_counter) := 'IBE_SC_CART_DELETED';
2723 Table_counter := table_counter+1;
2724 end if;
2725
2726 Elsif (l_order_id is NOT NULL) then
2727 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2728 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_ORDERED');
2729 END IF;
2730 if(table_counter = 1) then
2731 x_reason_code.extend();
2732 X_reason_code(table_counter) := 'IBE_SC_CART_ORDERED';
2733 Table_counter := table_counter+1;
2734 end if;
2735
2736 Elsif ((l_end_date_active is NOT NULL and l_end_date_active < sysdate) or
2737 (l_recipient_id is NULL)) then
2738 -- if the row has been end dated or we were unable to find a recip row by party, acct, and cart
2739 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2740 IBE_UTIL.debug('Including the Error code: IBE_SC_ERR_USERACCESS');
2741 END IF;
2742 if(table_counter = 1) then
2743 x_reason_code.extend();
2744 X_reason_code(table_counter) := 'IBE_SC_ERR_USERACCESS';
2745 Table_counter := table_counter+1;
2746 end if;
2747
2748 End if;
2749
2750 End If;
2751 -- 2nd half of errors for cart information
2752 -- only do these latter checks if we had a request for a cart - either by id or number and version
2753 if ((p_quote_header_id <> fnd_api.g_miss_num) or
2754 ((p_quote_number <> fnd_api.g_miss_num)
2755 and (p_quote_version <> fnd_api.g_miss_num))) then
2756 if(trunc(l_expiration_date) < trunc(sysdate)) then
2757 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2758 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_EXPIRED');
2759 END IF;
2760 if(table_counter = 1) then
2761 x_reason_code.extend();
2762 X_reason_code(table_counter) := 'IBE_SC_CART_EXPIRED';
2763 Table_counter := table_counter+1;
2764 end if;
2765 End if;
2766 If(l_max_version_flag = 'N') then
2767 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2768 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_MAX_VER_FLAG_N');
2769 END IF;
2770 if(table_counter = 1) then
2771 x_reason_code.extend();
2772 X_reason_code(table_counter) := 'IBE_SC_QUOTE_MAX_VER_FLAG_N';
2773 Table_counter := table_counter+1;
2774 end if;
2775 End If;
2776 If (p_load_type = L_CART_LOAD_TYPE) then
2777 If (l_resource_id is not null and (l_resource_id <> FND_API.G_MISS_NUM)) then
2778 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2779 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_HAS_RESOURCEID');
2780 END IF;
2781 if(table_counter = 1) then
2782 x_reason_code.extend();
2783 X_reason_code(Table_counter) := 'IBE_SC_CART_HAS_RESOURCEID';
2784 Table_counter := table_counter+1;
2785 end if;
2786 End If;
2787 --Status code 28 is for "store draft"
2788 If (upper(l_status_code) <> 'STORE DRAFT') then
2789 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2790 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_CART_STS');
2791 END IF;
2792 if(table_counter = 1) then
2793 x_reason_code.extend();
2794 X_reason_code(Table_counter ) := 'IBE_SC_INVALID_CART_STS';
2795 Table_counter := table_counter+1;
2796 end if;
2797 End if;
2798
2799 If(l_publish_flag <> 'F') then
2800 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2801 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_NOT_PUBL');
2802 END IF;
2803 if(table_counter = 1) then
2804 x_reason_code.extend();
2805 X_reason_code(Table_counter) := 'IBE_SC_CART_NOT_PUBL';
2806 Table_counter := table_counter+1;
2807 end if;
2808 End if;
2809 Elsif(p_load_type = L_QUOTE_LOAD_TYPE) then
2810 If (l_resource_id is null) then
2811 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2812 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_NEEDS_RESOURCEID');
2813 END IF;
2814 if(table_counter = 1) then
2815 x_reason_code.extend();
2816 X_reason_code(Table_counter):= 'IBE_SC_QUOTE_NEEDS_RESOURCEID';
2817 Table_counter := table_counter+1;
2818 end if;
2819 End If;
2820 If (l_status_code = 'INACTIVE') then
2821 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2822 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_INACTIVE');
2823 END IF;
2824 if(table_counter = 1) then
2825 x_reason_code.extend();
2826 X_reason_code(table_counter) := 'IBE_SC_QUOTE_INACTIVE';
2827 Table_counter := table_counter+1;
2828 end if;
2829 End If;
2830 If(l_publish_flag <> 'T') then
2831 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2832 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_NOT_PUBL');
2833 END IF;
2834 if(table_counter = 1) then
2835 x_reason_code.extend();
2836 X_reason_code(Table_counter):= 'IBE_SC_QUOTE_NOT_PUBL';
2837 Table_counter := table_counter+1;
2838 end if;
2839 End if;
2840 Elsif(p_load_type = L_LOAD_FORUPDATE) then
2841 If (l_resource_id is not null and (l_resource_id <> FND_API.G_MISS_NUM)) THEN
2842 If (FND_Profile.Value('IBE_UPDATE_DRAFT_QUOTES') = 'Y' and l_status_code <> 'DRAFT') THEN
2843 -- Update on Draft profile enabled, only allow updates on DRAFT.
2844 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2845 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_QUOTE_STS');
2846 END IF;
2847 if(table_counter = 1) then
2848 x_reason_code.extend();
2849 X_reason_code(Table_counter):= 'IBE_SC_INVALID_QUOTE_STS';
2850 Table_counter := table_counter+1;
2851 end if;
2852 End if;
2853 If(l_publish_flag <> 'T') then
2854 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2855 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_NOT_PUBL');
2856 END IF;
2857 if(table_counter = 1) then
2858 x_reason_code.extend();
2859 X_reason_code(Table_counter):= 'IBE_SC_QUOTE_NOT_PUBL';
2860 Table_counter := table_counter+1;
2861 end if;
2862 End if;
2863 Else -- for a cart, check for 'STORE DRAFT' (if the loadType is load_forupdate, status has to be 'STORE DRAFT')
2864 If (l_status_code <> 'STORE DRAFT') Then
2865 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2866 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_CART_STS');
2867 END IF;
2868 if(table_counter = 1) then
2869 x_reason_code.extend();
2870 X_reason_code(Table_counter):= 'IBE_SC_INVALID_CART_STS';
2871 Table_counter := table_counter+1;
2872 end if;
2873 End if;
2874 End if;
2875 End if;
2876 --load error for one click orders.
2877 If(p_load_type = L_LOAD_EXPRESSORDER) Then
2878 If(l_quote_name = l_exp_quote_header_id) Then
2879 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2880 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_DELETED');
2881 END IF;
2882 if(table_counter = 1) then
2883 x_reason_code.extend();
2884 X_reason_code(table_counter) := 'IBE_SC_CART_DELETED';
2885 Table_counter := table_counter+1;
2886 end if;
2887 End If;
2888 End if;
2889 End if;-- end if we have an input cartid or cartnum and version
2890 -- Standard check of p_commit.
2891 IF FND_API.To_Boolean(p_commit) THEN
2892 COMMIT WORK;
2893 END IF;
2894
2895 -- Standard call to get message count and if count is 1, get message info.
2896 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2897 p_count => x_msg_count ,
2898 p_data => x_msg_data);
2899 EXCEPTION
2900 WHEN FND_API.G_EXC_ERROR THEN
2901 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2902 IBE_UTIL.debug('Expected exception in IBE_QUOTE_MISC_PVT.get_load_errors');
2903 end if;
2904 ROLLBACK TO Get_load_errors_pvt;
2905 x_return_status := FND_API.G_RET_STS_ERROR;
2906 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2907 p_count => x_msg_count ,
2908 p_data => x_msg_data);
2909 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2910 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2911 IBE_UTIL.debug('Unexpected exception in IBE_QUOTE_MISC_PVT.get_load_errors');
2912 end if;
2913 ROLLBACK TO Get_load_errors_pvt;
2914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2915 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2916 p_count => x_msg_count ,
2917 p_data => x_msg_data);
2918 WHEN OTHERS THEN
2919 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2920 IBE_UTIL.debug('Unknown exception in IBE_QUOTE_MISC_PVT.get_load_errors');
2921 end if;
2922 ROLLBACK TO Get_load_errors_pvt;
2923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2924 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
2925 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2926 L_API_NAME);
2927 END IF;
2928
2929 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2930 p_count => x_msg_count ,
2931 p_data => x_msg_data);
2932
2933 End get_load_errors;
2934
2935 /*PROCEDURE Get_ActiveCart_Id (
2936 p_party_id IN number
2937 ,p_cust_account_id IN number
2938 ,p_api_version_number IN NUMBER
2939 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2940 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2941 ,X_Return_Status OUT NOCOPY VARCHAR2
2942 ,X_Msg_Count OUT NOCOPY NUMBER
2943 ,X_Msg_Data OUT NOCOPY VARCHAR2
2944 ,x_cartHeaderId OUT NOCOPY number
2945 ,x_last_update_date OUT NOCOPY DATE
2946 )
2947 is
2948
2949 CURSOR C_get_quote_id(p_partyid number,p_cust_accountid number ) is
2950 select quote_header_id, last_update_date
2951 from aso_quote_headers_all
2952 where quote_header_id = (select max(quote_header_id)
2953 from aso_quote_headers_all
2954 where upper(quote_source_code) = 'ISTORE ACCOUNT'
2955 and party_id = p_partyid
2956 and cust_account_id = p_cust_accountid
2957 and quote_name = 'IBEACTIVECART'
2958 and quote_expiration_date > sysdate
2959 and resource_id IS NULL
2960 and ORDER_ID IS NULL);
2961
2962
2963
2964 l_CartHeaderId number := null;
2965 Rec_get_quote_id C_get_quote_id%rowtype;
2966 l_dummy number;
2967 l_api_name CONSTANT VARCHAR2(30) := 'Get_ActiveCart_Id';
2968 l_api_version CONSTANT NUMBER := 1.0;
2969 l_count number;
2970
2971 begin
2972 -- Standard Start of API savepoint
2973 SAVEPOINT GetActiveCartId_pvt;
2974 -- Standard call to check for call compatibility.
2975 IF NOT FND_API.Compatible_API_Call (
2976 l_api_version ,
2977 P_Api_Version_Number,
2978 l_api_name ,
2979 G_PKG_NAME )
2980 THEN
2981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2982 END IF;
2983 -- Initialize message list if p_init_msg_list is set to TRUE.
2984 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2985 FND_MSG_PUB.initialize;
2986 END IF;
2987 -- Initialize API return status to success
2988 x_return_status := FND_API.G_RET_STS_SUCCESS;
2989 -- API body
2990 FOR Rec_get_quote_id in c_get_quote_id(p_party_id, p_cust_account_id) loop
2991 l_cartHeaderId := Rec_get_quote_id.quote_header_id;
2992 x_last_update_date := Rec_get_quote_id.last_update_date;
2993 exit when c_get_quote_id%notfound;
2994 end loop;
2995
2996 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2997 ibe_util.debug('ibeactivecart ='||l_cartHeaderId);
2998 END IF;
2999
3000 IF (l_cartHeaderId = fnd_api.g_miss_num or l_cartHeaderid is null ) then
3001 x_cartHeaderId := null;
3002 else
3003 x_cartHeaderId := l_cartHeaderId;
3004 end if;
3005
3006 -- Standard call to get message count and if count is 1, get message info.
3007 FND_MSG_PUB.Count_And_Get
3008 (p_count => x_msg_count,
3009 p_data => x_msg_data
3010 );
3011
3012
3013 EXCEPTION
3014 WHEN TOO_MANY_ROWS then
3015 --ibe_util.debug('TOO_MANY_ROWS');
3016 ROLLBACK TO GETACTIVECARTID_pvt;
3017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3018 FND_MESSAGE.set_name('IBE','IBE_SC_GETACTIVEC_MANY');
3019 FND_MSG_PUB.add;
3020 FND_MSG_PUB.Count_And_Get
3021 ( p_count => x_msg_count,
3022 p_data => x_msg_data
3023 );
3024 WHEN NO_DATA_FOUND then
3025 --ibe_util.debug('NO_DATA_FOUND');
3026 null;
3027
3028 WHEN FND_API.G_EXC_ERROR THEN
3029 ROLLBACK TO GETACTIVECARTID_pvt;
3030 x_return_status := FND_API.G_RET_STS_ERROR;
3031 FND_MSG_PUB.Count_And_Get
3032 ( p_count => x_msg_count,
3033 p_data => x_msg_data
3034 );
3035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3036 ROLLBACK TO GETACTIVECARTID_pvt;
3037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3038 FND_MSG_PUB.Count_And_Get
3039 ( p_count => x_msg_count,
3040 p_data => x_msg_data
3041 );
3042 WHEN OTHERS THEN
3043 ROLLBACK TO GETACTIVECARTID_pvt;
3044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3045 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3046 FND_MSG_PUB.Add_Exc_Msg
3047 ( G_PKG_NAME,
3048 l_api_name);
3049 END IF;
3050 FND_MSG_PUB.Count_And_Get
3051 ( p_count => x_msg_count,
3052 p_data => x_msg_data
3053 );
3054
3055 END GET_ACTIVECART_ID;*/
3056
3057 PROCEDURE Update_Config_Item_Lines(
3058 x_return_status OUT NOCOPY VARCHAR2,
3059 x_msg_count OUT NOCOPY NUMBER ,
3060 x_msg_data OUT NOCOPY VARCHAR2,
3061 px_qte_line_dtl_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
3062 )
3063 IS
3064 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Config_Item_Lines';
3065 l_old_config_header_id NUMBER;
3066 l_new_config_header_id NUMBER;
3067 l_old_config_revision_num NUMBER;
3068 l_new_config_revision_num NUMBER;
3069
3070 -- ER#4025142
3071 --l_return_value NUMBER;
3072 l_api_version CONSTANT NUMBER := 1.0;
3073 l_ret_status VARCHAR2(1);
3074 l_msg_count INTEGER;
3075 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
3076 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
3077 BEGIN
3078 SAVEPOINT Update_Config_Item_Lines;
3079 -- Initialize API return status to success
3080 x_return_status := FND_API.G_RET_STS_SUCCESS;
3081
3082 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3083 IBE_Util.Debug('IBE_Quote_Misc_pvt.Update_Config_Item_Lines(+)');
3084 END IF;
3085
3086 -- API body
3087 FOR i IN 1..px_qte_line_dtl_tbl.COUNT LOOP
3088 IF px_qte_line_dtl_tbl(i).config_header_id IS NOT NULL
3089 AND px_qte_line_dtl_tbl(i).config_header_id <> FND_API.G_MISS_NUM THEN
3090 l_old_config_header_id := px_qte_line_dtl_tbl(i).config_header_id;
3091 l_old_config_revision_num := px_qte_line_dtl_tbl(i).config_revision_num;
3092
3093 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3094 IBE_Util.Debug('old config header id = '|| l_old_config_header_id);
3095 IBE_Util.Debug('old config revision number = '|| l_old_config_revision_num);
3096 IBE_Util.Debug('Call CZ_CONFIG_API_PUB.copy_configuration at'
3097 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
3098 END IF;
3099
3100 --ER#4025142
3101 CZ_CONFIG_API_PUB.copy_configuration(p_api_version => l_api_version
3102 ,p_config_hdr_id => l_old_config_header_id
3103 ,p_config_rev_nbr => l_old_config_revision_num
3104 ,p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
3105 ,x_config_hdr_id => l_new_config_header_id
3106 ,x_config_rev_nbr => l_new_config_revision_num
3107 ,x_orig_item_id_tbl => l_orig_item_id_tbl
3108 ,x_new_item_id_tbl => l_new_item_id_tbl
3109 ,x_return_status => l_ret_status
3110 ,x_msg_count => l_msg_count
3111 ,x_msg_data => x_msg_data);
3112 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
3113 RAISE FND_API.G_EXC_ERROR;
3114 END IF;
3115
3116 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3117 IBE_Util.Debug('Done CZ_CONFIG_API_PUB.Copy_Configuration at'
3118 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
3119 END IF;
3120
3121 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3122 IBE_Util.Debug('new config header id = '|| l_new_config_header_id);
3123 IBE_Util.Debug('new config revision number = '|| l_new_config_revision_num);
3124 END IF;
3125
3126 -- update all other dtl table
3127 FOR j in 1..px_qte_line_dtl_tbl.COUNT LOOP
3128 IF px_qte_line_dtl_tbl(j).config_header_id = l_old_config_header_id
3129 AND px_qte_line_dtl_tbl(j).config_revision_num = l_old_config_revision_num THEN
3130 px_qte_line_dtl_tbl(j).config_header_id := l_new_config_header_id;
3131 px_qte_line_dtl_tbl(j).config_revision_num := l_new_config_revision_num;
3132 END IF;
3133 END LOOP;
3134 END IF;
3135 END LOOP;
3136 -- End of API body.
3137
3138 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3139 IBE_Util.Debug('IBE_Quote_Misc_pvt.Update_Config_Item_Lines(-)');
3140 END IF;
3141
3142 -- Standard call to get message count and IF count is 1, get message info.
3143 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3144 p_count => x_msg_count ,
3145 p_data => x_msg_data );
3146 EXCEPTION
3147 WHEN FND_API.G_EXC_ERROR THEN
3148 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3149 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Update_Config_Item_Lines');
3150 END IF;
3151 ROLLBACK to Update_config_item_lines;
3152 x_return_status := FND_API.G_RET_STS_ERROR;
3153 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3154 p_count => x_msg_count,
3155 p_data => x_msg_data);
3156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3157 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3158 IBE_Util.Debug('Unexpected error IBE_Quote_Misc_pvt.Update_Config_Item_Lines');
3159 END IF;
3160 ROLLBACK to Update_config_item_lines;
3161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3162 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3163 p_count => x_msg_count,
3164 p_data => x_msg_data);
3165 WHEN OTHERS THEN
3166 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3167 IBE_Util.Debug('Unknown error IBE_Quote_Misc_pvt.Update_Config_Item_Lines');
3168 END IF;
3169 ROLLBACK to Update_config_item_lines;
3170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3171
3172 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3173 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3174 l_api_name);
3175 END IF;
3176
3177 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3178 p_count => x_msg_count,
3179 p_data => x_msg_data);
3180 END Update_Config_Item_Lines;
3181
3182 procedure Validate_Items(
3183 x_item_exists OUT NOCOPY jtf_number_Table,
3184 p_cust_account_id IN NUMBER,
3185 p_minisite_id IN NUMBER,
3186 p_merchant_item_ids IN JTF_NUMBER_TABLE,
3187 p_org_id IN NUMBER
3188 ) IS
3189 l_item_exists NUMBER;
3190 l_count NUMBER;
3191 BEGIN
3192 l_count := p_merchant_item_ids.COUNT;
3193 x_item_exists := JTF_NUMBER_TABLE();
3194 x_item_exists.extend(l_count);
3195
3196
3197 FOR i IN 1..l_count LOOP
3198 IF p_merchant_item_ids(i) IS NOT NULL THEN
3199 --inv_debug.message('ssia', 'p_cust_item_number is ' || p_cust_item_number_tbl(i));
3200 select count(s.inventory_item_id)
3201 into l_item_exists
3202 from ibe_dsp_section_items s, ibe_dsp_msite_sct_items b
3203 where s.section_item_id = b.section_item_id
3204 and b.mini_site_id = p_minisite_id
3205 and s.inventory_item_id = p_merchant_item_ids(i)
3206 and (s.end_date_active > sysdate or s.end_date_active is null )
3207 and s.start_date_active < sysdate;
3208
3209 x_item_exists(i) := l_item_exists;
3210 else
3211 x_item_exists(i) := 0;
3212 end if;
3213 END LOOP;
3214 END Validate_Items;
3215
3216 PROCEDURE Get_Included_Warranties(
3217 p_api_version_number IN NUMBER := 1,
3218 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3219 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3220 x_return_status OUT NOCOPY VARCHAR2,
3221 x_msg_count OUT NOCOPY NUMBER,
3222 x_msg_data OUT NOCOPY VARCHAR2,
3223 p_organization_id IN NUMBER := NULL,
3224 p_product_item_id IN NUMBER,
3225 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
3226 ) IS
3227 l_warranty_tbl ASO_SERVICE_CONTRACTS_INT.War_tbl_type;
3228 l_count NUMBER;
3229 BEGIN
3230
3231 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3232 IBE_UTIL.Debug('Start IBE_Quote_Misc_pvt.Get_Available_Services');
3233 IBE_UTIL.Debug(' Parms: [' || p_organization_id || ', ' ||
3234 p_product_item_id || ']');
3235 END IF;
3236
3237 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3238 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Get_Warranty Starts');
3239 END IF;
3240 ASO_SERVICE_CONTRACTS_INT.Get_Warranty(
3241 p_api_version_number => p_api_version_number,
3242 p_init_msg_list => p_init_msg_list,
3243 x_msg_count => x_msg_count,
3244 x_msg_data => x_msg_data,
3245 p_org_id => FND_PROFILE.Value('ORG_ID'),
3246 p_organization_id => p_organization_id,
3247 p_product_item_id => p_product_item_id,
3248 x_return_status => x_return_status,
3249 x_warranty_tbl => l_warranty_tbl
3250 );
3251 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3252 l_count := l_warranty_tbl.COUNT;
3253 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3254 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Get_Warranty Finishes ' || x_return_status ||
3255 ' x_warranty_tbl.COUNT=' || l_count);
3256 END IF;
3257
3258 x_service_item_ids := JTF_NUMBER_TABLE();
3259
3260 IF l_count > 0 THEN
3261 x_service_item_ids.extend(l_count);
3262 FOR i in 1..l_count LOOP
3263 x_service_item_ids(i) := l_warranty_tbl(i).service_item_id;
3264 END LOOP;
3265 END IF;
3266 END IF;
3267
3268 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3269 IBE_UTIL.Debug('End IBE_Quote_Misc_pvt.Get_Available_Services');
3270 END IF;
3271
3272 END Get_Included_Warranties;
3273
3274 PROCEDURE Get_Available_Services(
3275 p_api_version_number IN NUMBER := 1,
3276 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3277 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3278 x_return_status OUT NOCOPY VARCHAR2,
3279 x_msg_count OUT NOCOPY NUMBER,
3280 x_msg_data OUT NOCOPY VARCHAR2,
3281 p_product_item_id IN NUMBER,
3282 p_customer_id IN NUMBER,
3283 p_product_revision IN VARCHAR2,
3284 p_request_date IN DATE,
3285 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
3286 ) IS
3287 l_avail_service_rec ASO_SERVICE_CONTRACTS_INT.Avail_Service_Rec_Type;
3288 l_orderable_Service_tbl ASO_SERVICE_CONTRACTS_INT.order_service_tbl_type;
3289 l_count NUMBER;
3290 BEGIN
3291
3292 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3293 IBE_UTIL.Debug('Start IBE_Quote_Misc_pvt.Get_Available_Services');
3294 IBE_UTIL.Debug(' Parms: [p_product_item_id=' || p_product_item_id || ', ' ||
3295 p_customer_id || ', ' || p_product_revision || ', ' ||
3296 p_request_date || ']');
3297 END IF;
3298
3299 -- Setting Rec values to be passed to ASO_SERVICE_CONTRACTS_INT
3300 l_avail_service_rec.product_item_id := p_product_item_id;
3301 l_avail_service_rec.customer_id := p_customer_id;
3302 l_avail_service_rec.product_revision := p_product_revision;
3303 l_avail_service_rec.request_date := p_request_date;
3304
3305 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3306 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Available_Services Starts');
3307 END IF;
3308 ASO_SERVICE_CONTRACTS_INT.Available_Services(
3309 p_api_version_number => p_api_version_number,
3310 p_init_msg_list => p_init_msg_list,
3311 x_msg_count => x_msg_count,
3312 x_msg_data => x_msg_data,
3313 x_return_status => x_return_status,
3314 p_avail_service_rec => l_avail_service_rec,
3315 x_orderable_service_tbl => l_orderable_service_tbl
3316 );
3317
3318 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3319 RAISE FND_API.G_EXC_ERROR;
3320 END IF;
3321 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3323 END IF;
3324
3325 l_count := l_orderable_service_tbl.COUNT;
3326 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3327 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Available_Services Finishes ' || x_return_status || ' ' ||
3328 'x_orderable_service_tbl.COUNT=' || l_count);
3329 END IF;
3330
3331 x_service_item_ids := JTF_NUMBER_TABLE();
3332
3333 IF l_count > 0 THEN
3334 x_service_item_ids.extend(l_count);
3335 FOR i IN 1..l_count LOOP
3336 x_service_item_ids(i) := l_orderable_service_tbl(i).service_item_id;
3337 END LOOP;
3338 END IF;
3339
3340 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3341 IBE_UTIL.Debug('End IBE_Quote_Misc_pvt.Get_Available_Services');
3342 END IF;
3343
3344 END Get_Available_Services;
3345
3346 Procedure Duplicate_Line(
3347 p_api_version_number IN NUMBER
3348 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3349 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3350 ,X_Return_Status OUT NOCOPY VARCHAR2
3351 ,X_Msg_Count OUT NOCOPY NUMBER
3352 ,X_Msg_Data OUT NOCOPY VARCHAR2
3353 ,p_quote_header_id IN NUMBER
3354 ,p_qte_line_id IN NUMBER
3355 ,x_qte_line_tbl IN OUT NOCOPY ASO_Quote_Pub.qte_line_tbl_type
3356 ,x_qte_line_dtl_tbl IN OUT NOCOPY ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type
3357 ,x_line_attr_ext_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type
3358 ,x_line_rltship_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Rltship_tbl_Type
3359 ,x_ln_price_attributes_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Attributes_Tbl_Type
3360 ,x_ln_price_adj_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Adj_Tbl_Type
3361 )
3362 IS
3363
3364 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Line';
3365 l_api_version CONSTANT NUMBER := 1.0;
3366
3367 l_qte_line_dtl_tbl ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type;
3368 l_line_rltship_tbl ASO_Quote_Pub.Line_Rltship_tbl_Type;
3369 l_line_attr_ext_tbl ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type;
3370 l_ln_price_attributes_tbl ASO_Quote_Pub.Price_Attributes_Tbl_Type;
3371 l_ln_price_adj_tbl ASO_Quote_Pub.Price_Adj_Tbl_Type;
3372
3373 l_initial_count NUMBER;
3374 l_initial_dtl_count NUMBER;
3375
3376 l_old_config_hdr_id NUMBER;
3377 l_old_config_rev_nbr NUMBER;
3378
3379 l_new_config_hdr_id NUMBER;
3380 l_new_config_rev_nbr NUMBER;
3381
3382 -- ER#4025142
3383 --l_return_value NUMBER;
3384 l_ret_status VARCHAR2(1);
3385 l_msg_count INTEGER;
3386 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
3387 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
3388
3389 CURSOR c_related_lines (p_qte_line_id NUMBER) IS
3390 SELECT related_quote_line_id
3391 FROM aso_line_relationships
3392 START WITH quote_line_id = p_qte_line_id
3393 CONNECT BY quote_line_id = PRIOR related_quote_line_id;
3394
3395 BEGIN
3396 -- Standard Start of API savepoint
3397 SAVEPOINT DUPLICATE_LINE_PVT;
3398 -- Standard call to check for call compatibility.
3399 IF NOT FND_API.Compatible_API_Call (l_api_version,
3400 P_Api_Version_Number,
3401 l_api_name,
3402 G_PKG_NAME )
3403 THEN
3404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3405 END IF;
3406 -- Initialize message list IF p_init_msg_list is set to TRUE.
3407 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3408 FND_MSG_PUB.initialize;
3409 END IF;
3410
3411 -- Initialize API return status to success
3412 x_return_status := FND_API.G_RET_STS_SUCCESS;
3413
3414 l_initial_dtl_count := x_qte_line_dtl_tbl.COUNT + 1;
3415
3416 l_initial_count := x_qte_line_tbl.COUNT + 1;
3417 x_qte_line_tbl(l_initial_count) := IBE_Quote_Misc_pvt.getLineRec(p_qte_line_id);
3418
3419 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3420 IBE_UTIL.Debug(' Adding Lines From c_related_lines');
3421 END IF;
3422 FOR l_related_lines_rec IN c_related_lines(p_qte_line_id) LOOP
3423 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3424 IBE_UTIL.Debug(' Adding related_quote_line_id=' || l_related_lines_rec.related_quote_line_id);
3425 END IF;
3426 x_qte_line_tbl(x_qte_line_tbl.COUNT + 1) := IBE_Quote_Misc_pvt.getLineRec(l_related_lines_rec.related_quote_line_id);
3427 END LOOP;
3428
3429
3430 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3431 ibe_util.debug('line number is='|| x_qte_line_tbl.count);
3432 END IF;
3433 FOR i IN l_initial_count..x_qte_line_tbl.COUNT LOOP
3434
3435 l_qte_line_dtl_tbl := IBE_Quote_Misc_pvt.getlineDetailTbl
3436 (x_qte_line_tbl(i).quote_line_id);
3437 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3438 IBE_UTIL.Debug(' Processing LineDetailTbl Count=' || l_qte_line_dtl_tbl.COUNT);
3439 END IF;
3440 FOR j IN 1..l_qte_line_dtl_tbl.COUNT LOOP
3441 IF l_qte_line_dtl_tbl(j).service_ref_line_id <> fnd_api.g_miss_num THEN
3442 -- All service_ref_line_id's should point to first entry in x_qte_line_tbl
3443 l_qte_line_dtl_tbl(j).service_ref_qte_line_index := l_initial_count;
3444 l_qte_line_dtl_tbl(j).service_ref_line_id := fnd_api.g_miss_num;
3445 END IF;
3446 l_qte_line_dtl_tbl(j).quote_line_detail_id := fnd_api.g_miss_num;
3447 l_qte_line_dtl_tbl(j).operation_code := 'CREATE';
3448 l_qte_line_dtl_tbl(j).qte_line_index := i;
3449 l_qte_line_dtl_tbl(j).quote_line_id := fnd_api.g_miss_num;
3450 x_qte_line_dtl_tbl(x_qte_line_dtl_tbl.count+1)
3451 := l_qte_line_dtl_tbl(j);
3452 END LOOP;
3453
3454
3455 l_line_rltship_tbl := IBE_Quote_Misc_pvt.getlineRelationshipTbl(x_qte_line_tbl(i).quote_line_id);
3456 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3457 IBE_UTIL.Debug(' Processing LineRelationshipTbl Count=' || l_line_rltship_tbl.COUNT);
3458 END IF;
3459
3460 FOR j IN 1..l_line_rltship_tbl.COUNT LOOP
3461 IF NVL(l_line_rltship_tbl(j).relationship_type_code, '*') <> 'SERVICE' THEN
3462 l_line_rltship_tbl(j).line_relationship_id := fnd_api.g_miss_num;
3463 l_line_rltship_tbl(j).operation_code := 'CREATE';
3464
3465 l_line_rltship_tbl(j).qte_line_index := i;
3466 l_line_rltship_tbl(j).related_qte_line_index
3467 := IBE_Quote_Misc_pvt.getLineIndexFromLineId
3468 ( l_line_rltship_tbl(j).related_quote_line_id
3469 ,x_qte_line_tbl
3470 );
3471 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3472 IBE_UTIL.Debug(' related_quote_line_id=' || l_line_rltship_tbl(j).related_quote_line_id ||
3473 ' related_quote_line_index=' || l_line_rltship_tbl(j).related_qte_line_index);
3474 END IF;
3475 l_line_rltship_tbl(j).quote_line_id := fnd_api.g_miss_num;
3476 l_line_rltship_tbl(j).related_quote_line_id := fnd_api.g_miss_num;
3477 x_line_rltship_tbl(x_line_rltship_tbl.count+1)
3478 := l_line_rltship_tbl(j);
3479 END IF;
3480
3481 END LOOP;
3482
3483
3484 l_line_attr_ext_tbl := IBE_Quote_Misc_pvt.getLineAttrExtTbl
3485 (x_qte_line_tbl(i).quote_line_id);
3486 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3487 IBE_UTIL.Debug(' Processing LineAttrExtTbl Count=' || l_line_attr_ext_tbl.COUNT);
3488 END IF;
3489
3490 FOR j IN 1..l_line_attr_ext_tbl.COUNT LOOP
3491 l_line_attr_ext_tbl(j).line_attribute_id := fnd_api.g_miss_num;
3492 l_line_attr_ext_tbl(j).operation_code := 'CREATE';
3493 l_line_attr_ext_tbl(j).qte_line_index := i;
3494 l_line_attr_ext_tbl(j).quote_line_id := fnd_api.g_miss_num;
3495
3496 x_line_attr_ext_tbl(x_line_attr_ext_tbl.count+1)
3497 := l_line_attr_ext_tbl(j);
3498 END LOOP;
3499
3500 l_ln_price_attributes_tbl := IBE_Quote_Misc_pvt.getlinePrcAttrTbl
3501 (x_qte_line_tbl(i).quote_line_id);
3502 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3503 IBE_UTIL.Debug(' Processing linePrcAttrTbl Count=' || l_ln_price_attributes_tbl.COUNT);
3504 END IF;
3505
3506 FOR j IN 1..l_ln_price_attributes_tbl.COUNT LOOP
3507 l_ln_price_attributes_tbl(j).price_attribute_id := fnd_api.g_miss_num;
3508 l_ln_price_attributes_tbl(j).operation_code := 'CREATE';
3509 l_ln_price_attributes_tbl(j).qte_line_index := i;
3510 l_ln_price_attributes_tbl(j).quote_line_id := fnd_api.g_miss_num;
3511 l_ln_price_attributes_tbl(j).quote_header_id := p_quote_header_id;
3512 x_ln_price_attributes_tbl(x_ln_price_attributes_tbl.count+1)
3513 := l_ln_price_attributes_tbl(j);
3514 END LOOP;
3515
3516 l_ln_price_adj_tbl := getHdrPrcAdjNonPRGTbl
3517 (p_qte_header_id => p_quote_header_id,
3518 p_qte_line_id => x_qte_line_tbl(i).quote_line_id);
3519 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3520 IBE_UTIL.Debug(' Processing LinePriceAdjTbl Count=' || l_ln_price_adj_tbl.COUNT);
3521 END IF;
3522
3523 FOR j IN 1..l_ln_price_adj_tbl.COUNT LOOP
3524 l_ln_price_adj_tbl(j).price_adjustment_id := fnd_api.g_miss_num;
3525 l_ln_price_adj_tbl(j).operation_code := 'CREATE';
3526 l_ln_price_adj_tbl(j).qte_line_index := i;
3527 l_ln_price_adj_tbl(j).quote_line_id := fnd_api.g_miss_num;
3528 l_ln_price_adj_tbl(j).quote_header_id := p_quote_header_id;
3529 x_ln_price_adj_tbl(x_ln_price_adj_tbl.count+1)
3530 := l_ln_price_adj_tbl(j);
3531 END LOOP;
3532
3533 END LOOP; -- end of get line information
3534
3535 FOR I IN l_initial_count..x_qte_line_tbl.COUNT LOOP
3536 x_qte_line_tbl(I).operation_code := 'CREATE';
3537 x_qte_line_tbl(I).quote_line_id := fnd_api.g_miss_num;
3538 x_qte_line_tbl(I).quote_header_id := p_quote_header_id;
3539 END LOOP;
3540
3541 -- takes care of configuraton item
3542 FOR i IN l_initial_dtl_count..x_qte_line_dtl_tbl.COUNT LOOP
3543 IF x_qte_line_tbl(x_qte_line_dtl_tbl(i).qte_line_index).item_type_code
3544 = 'MDL' THEN
3545 l_old_config_hdr_id := x_qte_line_dtl_tbl(i).config_header_id;
3546 l_old_config_rev_nbr := x_qte_line_dtl_tbl(i).config_revision_num;
3547
3548 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3549 IBE_UTIL.debug('old config id = '|| l_old_config_hdr_id);
3550 IBE_UTIL.debug('old config rev number = '|| l_old_config_rev_nbr);
3551 END IF;
3552
3553
3554 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3555 IBE_UTIL.debug('call CZ_CONFIG_API_PUB.copy_configuration at'
3556 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
3557 END IF;
3558
3559 --ER#4025142
3560 CZ_CONFIG_API_PUB.copy_configuration(p_api_version => l_api_version
3561 ,p_config_hdr_id => l_old_config_hdr_id
3562 ,p_config_rev_nbr => l_old_config_rev_nbr
3563 ,p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
3564 ,x_config_hdr_id => l_new_config_hdr_id
3565 ,x_config_rev_nbr => l_new_config_rev_nbr
3566 ,x_orig_item_id_tbl => l_orig_item_id_tbl
3567 ,x_new_item_id_tbl => l_new_item_id_tbl
3568 ,x_return_status => l_ret_status
3569 ,x_msg_count => l_msg_count
3570 ,x_msg_data => x_msg_data);
3571 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
3572 RAISE FND_API.G_EXC_ERROR;
3573 END IF;
3574
3575
3576 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3577 IBE_UTIL.DEBUG('done CZ_CONFIG_API_PUB.Copy_Configuration at'
3578 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
3579 END IF;
3580
3581 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3582 IBE_UTIL.debug('new config id = '|| l_new_config_hdr_id);
3583 IBE_UTIL.debug('new config rev number = '|| l_new_config_rev_nbr);
3584 END IF;
3585
3586 -- update all other dtl table
3587 FOR j in l_initial_dtl_count..x_qte_line_dtl_tbl.COUNT LOOP
3588 IF ( x_qte_line_dtl_tbl(j).config_header_id = l_old_config_hdr_id
3589 and x_qte_line_dtl_tbl(j).config_revision_num = l_old_config_rev_nbr )
3590 THEN
3591 x_qte_line_dtl_tbl(j).config_header_id := l_new_config_hdr_id;
3592 x_qte_line_dtl_tbl(j).config_revision_num := l_new_config_rev_nbr;
3593 END IF;
3594 END LOOP;
3595 END IF;
3596 END LOOP;
3597
3598 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3599 ibe_util.debug('before out line number is='|| x_qte_line_tbl.count);
3600 END IF;
3601 -- Standard check of p_commit.
3602 IF FND_API.To_Boolean( p_commit ) THEN
3603 COMMIT WORK;
3604 END IF;
3605
3606 -- Standard call to get message count and IF count is 1, get message info.
3607 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3608 p_count => x_msg_count,
3609 p_data => x_msg_data);
3610 EXCEPTION
3611 WHEN FND_API.G_EXC_ERROR THEN
3612 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3613 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Duplicate_line');
3614 END IF;
3615
3616 ROLLBACK TO DUPLICATE_LINE_PVT;
3617 x_return_status := FND_API.G_RET_STS_ERROR;
3618 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3619 p_count => x_msg_count,
3620 p_data => x_msg_data);
3621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3622 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3623 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Duplicate_line');
3624 END IF;
3625
3626 ROLLBACK TO DUPLICATE_LINE_PVT;
3627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3628 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3629 p_count => x_msg_count,
3630 p_data => x_msg_data);
3631 WHEN OTHERS THEN
3632 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3633 IBE_Util.Debug('unknown error IBE_Quote_Misc_pvt.Duplicate_line');
3634 END IF;
3635
3636 ROLLBACK TO DUPLICATE_LINE_PVT;
3637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3638
3639 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3640 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3641 l_api_name);
3642 END IF;
3643
3644 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3645 p_count => x_msg_count,
3646 p_data => x_msg_data);
3647 END Duplicate_Line;
3648
3649 FUNCTION getHdrPrcAdjNonPRGTbl (
3650 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
3651 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
3652 ) RETURN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
3653 IS
3654 CURSOR c_price_adj IS
3655 SELECT
3656 PRICE_ADJUSTMENT_ID,
3657 CREATION_DATE,
3658 CREATED_BY,
3659 LAST_UPDATE_DATE,
3660 LAST_UPDATED_BY,
3661 LAST_UPDATE_LOGIN,
3662 PROGRAM_APPLICATION_ID,
3663 PROGRAM_ID,
3664 PROGRAM_UPDATE_DATE,
3665 REQUEST_ID,
3666 QUOTE_HEADER_ID,
3667 QUOTE_LINE_ID,
3668 MODIFIER_HEADER_ID,
3669 MODIFIER_LINE_ID,
3670 MODIFIER_LINE_TYPE_CODE,
3671 MODIFIER_MECHANISM_TYPE_CODE,
3672 MODIFIED_FROM,
3673 MODIFIED_TO,
3674 OPERAND,
3675 ARITHMETIC_OPERATOR,
3676 AUTOMATIC_FLAG,
3677 UPDATE_ALLOWABLE_FLAG,
3678 UPDATED_FLAG,
3679 APPLIED_FLAG,
3680 ON_INVOICE_FLAG,
3681 PRICING_PHASE_ID,
3682 ATTRIBUTE_CATEGORY,
3683 ATTRIBUTE1,
3684 ATTRIBUTE2,
3685 ATTRIBUTE3,
3686 ATTRIBUTE4,
3687 ATTRIBUTE5,
3688 ATTRIBUTE6,
3689 ATTRIBUTE7,
3690 ATTRIBUTE8,
3691 ATTRIBUTE9,
3692 ATTRIBUTE10,
3693 ATTRIBUTE11,
3694 ATTRIBUTE12,
3695 ATTRIBUTE13,
3696 ATTRIBUTE14,
3697 ATTRIBUTE15,
3698 TAX_CODE,
3699 TAX_EXEMPT_FLAG,
3700 TAX_EXEMPT_NUMBER,
3701 TAX_EXEMPT_REASON_CODE,
3702 PARENT_ADJUSTMENT_ID,
3703 INVOICED_FLAG,
3704 ESTIMATED_FLAG,
3705 INC_IN_SALES_PERFORMANCE,
3706 SPLIT_ACTION_CODE,
3707 ADJUSTED_AMOUNT,
3708 CHARGE_TYPE_CODE,
3709 CHARGE_SUBTYPE_CODE,
3710 RANGE_BREAK_QUANTITY,
3711 ACCRUAL_CONVERSION_RATE,
3712 PRICING_GROUP_SEQUENCE,
3713 ACCRUAL_FLAG,
3714 LIST_LINE_NO,
3715 SOURCE_SYSTEM_CODE,
3716 BENEFIT_QTY,
3717 BENEFIT_UOM_CODE,
3718 PRINT_ON_INVOICE_FLAG,
3719 EXPIRATION_DATE,
3720 REBATE_TRANSACTION_TYPE_CODE,
3721 REBATE_TRANSACTION_REFERENCE,
3722 REBATE_PAYMENT_SYSTEM_CODE,
3723 REDEEMED_DATE,
3724 REDEEMED_FLAG,
3725 MODIFIER_LEVEL_CODE,
3726 PRICE_BREAK_TYPE_CODE,
3727 SUBSTITUTION_ATTRIBUTE,
3728 PRORATION_TYPE_CODE,
3729 INCLUDE_ON_RETURNS_FLAG,
3730 CREDIT_OR_CHARGE_FLAG,
3731 ORIG_SYS_DISCOUNT_REF,
3732 CHANGE_REASON_CODE,
3733 CHANGE_REASON_TEXT,
3734 COST_ID,
3735 LIST_LINE_TYPE_CODE,
3736 UPDATE_ALLOWED,
3737 CHANGE_SEQUENCE,
3738 LIST_HEADER_ID,
3739 LIST_LINE_ID,
3740 QUOTE_SHIPMENT_ID
3741 FROM ASO_PRICE_ADJUSTMENTS
3742 WHERE quote_header_id = p_qte_header_id AND
3743 (quote_line_id = p_qte_line_id OR
3744 (quote_line_id IS NULL AND p_qte_line_id IS NULL))
3745 AND modifier_line_type_code <> 'PRG';
3746
3747 l_price_adj_rec ASO_QUOTE_PUB.Price_Adj_Rec_Type;
3748 l_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
3749
3750 BEGIN
3751 FOR price_adj_rec IN c_price_adj LOOP
3752 l_price_adj_rec.PRICE_ADJUSTMENT_ID := price_adj_rec.PRICE_ADJUSTMENT_ID;
3753 l_price_adj_rec.CREATION_DATE := price_adj_rec.CREATION_DATE;
3754 l_price_adj_rec.CREATED_BY := price_adj_rec.CREATED_BY;
3755 l_price_adj_rec.LAST_UPDATE_DATE := price_adj_rec.LAST_UPDATE_DATE;
3756 l_price_adj_rec.LAST_UPDATED_BY := price_adj_rec.LAST_UPDATED_BY;
3757 l_price_adj_rec.LAST_UPDATE_LOGIN := price_adj_rec.LAST_UPDATE_LOGIN;
3758 l_price_adj_rec.REQUEST_ID := price_adj_rec.REQUEST_ID;
3759 l_price_adj_rec.PROGRAM_APPLICATION_ID := price_adj_rec.PROGRAM_APPLICATION_ID;
3760 l_price_adj_rec.PROGRAM_ID := price_adj_rec.PROGRAM_ID;
3761 l_price_adj_rec.PROGRAM_UPDATE_DATE := price_adj_rec.PROGRAM_UPDATE_DATE;
3762 l_price_adj_rec.QUOTE_HEADER_ID := price_adj_rec.QUOTE_HEADER_ID;
3763 l_price_adj_rec.QUOTE_LINE_ID := price_adj_rec.QUOTE_LINE_ID;
3764 l_price_adj_rec.MODIFIER_HEADER_ID := price_adj_rec.MODIFIER_HEADER_ID;
3765 l_price_adj_rec.MODIFIER_LINE_ID := price_adj_rec.MODIFIER_LINE_ID;
3766 l_price_adj_rec.MODIFIER_LINE_TYPE_CODE := price_adj_rec.MODIFIER_LINE_TYPE_CODE;
3767 l_price_adj_rec.MODIFIER_MECHANISM_TYPE_CODE
3768 := price_adj_rec.MODIFIER_MECHANISM_TYPE_CODE;
3769 l_price_adj_rec.MODIFIED_FROM := price_adj_rec.MODIFIED_FROM;
3770 l_price_adj_rec.MODIFIED_TO := price_adj_rec.MODIFIED_TO;
3771 l_price_adj_rec.OPERAND := price_adj_rec.OPERAND;
3772 l_price_adj_rec.ARITHMETIC_OPERATOR := price_adj_rec.ARITHMETIC_OPERATOR;
3773 l_price_adj_rec.AUTOMATIC_FLAG := price_adj_rec.AUTOMATIC_FLAG;
3774 l_price_adj_rec.UPDATE_ALLOWABLE_FLAG := price_adj_rec.UPDATE_ALLOWABLE_FLAG;
3775 l_price_adj_rec.UPDATED_FLAG := price_adj_rec.UPDATED_FLAG;
3776 l_price_adj_rec.APPLIED_FLAG := price_adj_rec.APPLIED_FLAG;
3777 l_price_adj_rec.ON_INVOICE_FLAG := price_adj_rec.ON_INVOICE_FLAG;
3778 l_price_adj_rec.PRICING_PHASE_ID := price_adj_rec.PRICING_PHASE_ID;
3779 l_price_adj_rec.QUOTE_SHIPMENT_ID := price_adj_rec.QUOTE_SHIPMENT_ID;
3780 l_price_adj_rec.ATTRIBUTE_CATEGORY := price_adj_rec.ATTRIBUTE_CATEGORY;
3781 l_price_adj_rec.ATTRIBUTE1 := price_adj_rec.ATTRIBUTE1;
3782 l_price_adj_rec.ATTRIBUTE2 := price_adj_rec.ATTRIBUTE2;
3783 l_price_adj_rec.ATTRIBUTE3 := price_adj_rec.ATTRIBUTE3;
3784 l_price_adj_rec.ATTRIBUTE4 := price_adj_rec.ATTRIBUTE4;
3785 l_price_adj_rec.ATTRIBUTE5 := price_adj_rec.ATTRIBUTE5;
3786 l_price_adj_rec.ATTRIBUTE6 := price_adj_rec.ATTRIBUTE6;
3787 l_price_adj_rec.ATTRIBUTE7 := price_adj_rec.ATTRIBUTE7;
3788 l_price_adj_rec.ATTRIBUTE8 := price_adj_rec.ATTRIBUTE8;
3789 l_price_adj_rec.ATTRIBUTE9 := price_adj_rec.ATTRIBUTE9;
3790 l_price_adj_rec.ATTRIBUTE10 := price_adj_rec.ATTRIBUTE10;
3791 l_price_adj_rec.ATTRIBUTE11 := price_adj_rec.ATTRIBUTE11;
3792 l_price_adj_rec.ATTRIBUTE12 := price_adj_rec.ATTRIBUTE12;
3793 l_price_adj_rec.ATTRIBUTE13 := price_adj_rec.ATTRIBUTE13;
3794 l_price_adj_rec.ATTRIBUTE14 := price_adj_rec.ATTRIBUTE14;
3795 l_price_adj_rec.ATTRIBUTE15 := price_adj_rec.ATTRIBUTE15;
3796 l_price_adj_rec.TAX_CODE := price_adj_rec.TAX_CODE;
3797 l_price_adj_rec.TAX_EXEMPT_FLAG := price_adj_rec.TAX_EXEMPT_FLAG;
3798 l_price_adj_rec.TAX_EXEMPT_NUMBER := price_adj_rec.TAX_EXEMPT_NUMBER;
3799 l_price_adj_rec.TAX_EXEMPT_REASON_CODE := price_adj_rec.TAX_EXEMPT_REASON_CODE;
3800 l_price_adj_rec.PARENT_ADJUSTMENT_ID := price_adj_rec.PARENT_ADJUSTMENT_ID;
3801 l_price_adj_rec.INVOICED_FLAG := price_adj_rec.INVOICED_FLAG;
3802 l_price_adj_rec.ESTIMATED_FLAG := price_adj_rec.ESTIMATED_FLAG;
3803 l_price_adj_rec.INC_IN_SALES_PERFORMANCE := price_adj_rec.INC_IN_SALES_PERFORMANCE;
3804 l_price_adj_rec.SPLIT_ACTION_CODE := price_adj_rec.SPLIT_ACTION_CODE;
3805 l_price_adj_rec.ADJUSTED_AMOUNT := price_adj_rec.ADJUSTED_AMOUNT;
3806 l_price_adj_rec.CHARGE_TYPE_CODE := price_adj_rec.CHARGE_TYPE_CODE;
3807 l_price_adj_rec.CHARGE_SUBTYPE_CODE := price_adj_rec.CHARGE_SUBTYPE_CODE;
3808 l_price_adj_rec.RANGE_BREAK_QUANTITY := price_adj_rec.RANGE_BREAK_QUANTITY;
3809 l_price_adj_rec.ACCRUAL_CONVERSION_RATE := price_adj_rec.ACCRUAL_CONVERSION_RATE;
3810 l_price_adj_rec.PRICING_GROUP_SEQUENCE := price_adj_rec.PRICING_GROUP_SEQUENCE;
3811 l_price_adj_rec.ACCRUAL_FLAG := price_adj_rec.ACCRUAL_FLAG;
3812 l_price_adj_rec.LIST_LINE_NO := price_adj_rec.LIST_LINE_NO;
3813 l_price_adj_rec.SOURCE_SYSTEM_CODE := price_adj_rec.SOURCE_SYSTEM_CODE;
3814 l_price_adj_rec.BENEFIT_QTY := price_adj_rec.BENEFIT_QTY;
3815 l_price_adj_rec.BENEFIT_UOM_CODE := price_adj_rec.BENEFIT_UOM_CODE;
3816 l_price_adj_rec.PRINT_ON_INVOICE_FLAG := price_adj_rec.PRINT_ON_INVOICE_FLAG;
3817 l_price_adj_rec.EXPIRATION_DATE := price_adj_rec.EXPIRATION_DATE;
3818 l_price_adj_rec.REBATE_TRANSACTION_TYPE_CODE := price_adj_rec.REBATE_TRANSACTION_TYPE_CODE;
3819 l_price_adj_rec.REBATE_TRANSACTION_REFERENCE := price_adj_rec.REBATE_TRANSACTION_REFERENCE;
3820 l_price_adj_rec.REBATE_PAYMENT_SYSTEM_CODE := price_adj_rec.REBATE_PAYMENT_SYSTEM_CODE;
3821 l_price_adj_rec.REDEEMED_DATE := price_adj_rec.REDEEMED_DATE;
3822 l_price_adj_rec.REDEEMED_FLAG := price_adj_rec.REDEEMED_FLAG;
3823 l_price_adj_rec.MODIFIER_LEVEL_CODE := price_adj_rec.MODIFIER_LEVEL_CODE;
3824 l_price_adj_rec.PRICE_BREAK_TYPE_CODE := price_adj_rec.PRICE_BREAK_TYPE_CODE;
3825 l_price_adj_rec.SUBSTITUTION_ATTRIBUTE := price_adj_rec.SUBSTITUTION_ATTRIBUTE;
3826 l_price_adj_rec.PRORATION_TYPE_CODE := price_adj_rec.PRORATION_TYPE_CODE;
3827 l_price_adj_rec.INCLUDE_ON_RETURNS_FLAG := price_adj_rec.INCLUDE_ON_RETURNS_FLAG;
3828 l_price_adj_rec.CREDIT_OR_CHARGE_FLAG := price_adj_rec.CREDIT_OR_CHARGE_FLAG;
3829 l_price_adj_rec.ORIG_SYS_DISCOUNT_REF := price_adj_rec.ORIG_SYS_DISCOUNT_REF;
3830 l_price_adj_rec.CHANGE_REASON_CODE := price_adj_rec.CHANGE_REASON_CODE;
3831 l_price_adj_rec.CHANGE_REASON_TEXT := price_adj_rec.CHANGE_REASON_TEXT;
3832 l_price_adj_rec.COST_ID := price_adj_rec.COST_ID;
3833 --l_price_adj_rec.LIST_LINE_TYPE_CODE := price_adj_rec.LIST_LINE_TYPE_CODE;
3834 l_price_adj_rec.UPDATE_ALLOWED := price_adj_rec.UPDATE_ALLOWED;
3835 l_price_adj_rec.CHANGE_SEQUENCE := price_adj_rec.CHANGE_SEQUENCE;
3836 --l_price_adj_rec.LIST_HEADER_ID := price_adj_rec.LIST_HEADER_ID;
3837 --l_price_adj_rec.LIST_LINE_ID := price_adj_rec.LIST_LINE_ID;
3838 l_price_adj_tbl(l_price_adj_tbl.COUNT+1) := l_price_adj_rec;
3839 END LOOP;
3840 RETURN l_price_adj_tbl;
3841 END getHdrPrcAdjNonPRGTbl;
3842
3843 Procedure Split_Line(
3844 p_api_version_number IN NUMBER
3845 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3846 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3847 ,X_Return_Status OUT NOCOPY VARCHAR2
3848 ,X_Msg_Count OUT NOCOPY NUMBER
3849 ,X_Msg_Data OUT NOCOPY VARCHAR2
3850 ,p_quote_header_id IN NUMBER
3851 ,p_qte_line_id IN NUMBER
3852 ,p_quantities IN jtf_number_table
3853 ,p_last_update_date IN OUT NOCOPY DATE
3854 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
3855 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
3856 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
3857 ,p_minisite_id IN NUMBER := FND_API.G_MISS_NUM
3858 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
3859 )
3860 IS
3861 l_api_name CONSTANT VARCHAR2(30) := 'Split_Line';
3862 l_api_version CONSTANT NUMBER := 1.0;
3863 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
3864 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec;
3865 l_count NUMBER;
3866 l_last_update_date DATE;
3867 l_qte_line_count NUMBER;
3868 lx_quote_header_id NUMBER;
3869 lx_last_update_date DATE;
3870 -- Duplicate line Records (Temporary).
3871 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
3872 l_tmp_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
3873 l_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
3874 lx_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
3875 l_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
3876 l_line_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
3877 l_line_rltship_tbl ASO_QUOTE_PUB.line_rltship_tbl_type;
3878 l_ln_price_attributes_tbl ASO_QUOTE_PUB.price_attributes_tbl_type;
3879 l_ln_price_adj_tbl ASO_QUOTE_PUB.price_adj_tbl_type;
3880 -- Duplicate line Records (Used for actually calling Save.)
3881 l_sv_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
3882 l_sv_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
3883 l_sv_line_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
3884 l_sv_line_rltship_tbl ASO_QUOTE_PUB.line_rltship_tbl_type;
3885 l_sv_ln_price_attributes_tbl ASO_QUOTE_PUB.price_attributes_tbl_type;
3886 l_sv_ln_price_adj_tbl ASO_QUOTE_PUB.price_adj_tbl_type;
3887 lx_return_status VARCHAR2(1);
3888 lx_msg_count NUMBER;
3889 lx_msg_data VARCHAR2(2000);
3890 BEGIN
3891 -- Standard Start of API savepoint
3892 SAVEPOINT SPLIT_LINE_PVT;
3893 -- Standard call to check for call compatibility.
3894 IF NOT FND_API.Compatible_API_Call (l_api_version,
3895 P_Api_Version_Number,
3896 l_api_name,
3897 G_PKG_NAME )
3898 THEN
3899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3900 END IF;
3901 -- Initialize message list IF p_init_msg_list is set to TRUE.
3902 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3903 FND_MSG_PUB.initialize;
3904 END IF;
3905 -- Initialize API return status to success
3906 x_return_status := FND_API.G_RET_STS_SUCCESS;
3907
3908
3909 IF p_quantities IS NOT NULL THEN
3910
3911 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3912 IBE_UTIL.Debug('Validating User Starts: Also Pquantities is not null');
3913 END IF;
3914
3915 /* -- 4587019
3916 l_last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdatedate(p_quote_header_id);
3917
3918 IF (l_last_update_date <> p_last_update_date) then
3919 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3920 IBE_UTIL.Debug('comparing dates');
3921 END IF;
3922 p_last_update_date := l_last_update_date;
3923 -- raise error
3924
3925 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3926 FND_MESSAGE.Set_Name('IBE', 'IBE_SC_QUOTE_NEED_REFRESH');
3927 FND_MSG_PUB.ADD;
3928 END IF;
3929 RAISE FND_API.G_EXC_ERROR; -- need error message
3930 END IF;
3931 -- 4587019
3932 */
3933
3934 -- User Authentication
3935 IBE_Quote_Misc_pvt.Validate_User_Update
3936 ( p_init_msg_list => p_init_msg_list
3937 ,p_quote_header_id => p_quote_header_id
3938 ,p_party_id => p_party_id
3939 ,p_cust_account_id => p_cust_account_id
3940 ,p_quote_retrieval_number => p_quote_retrieval_number
3941 ,p_validate_user => p_validate_user
3942 ,x_return_status => x_return_status
3943 ,x_msg_count => x_msg_count
3944 ,x_msg_data => x_msg_data
3945 );
3946
3947
3948 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3949 RAISE FND_API.G_EXC_ERROR;
3950 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3951 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3952 END IF;
3953
3954 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3955 IBE_UTIL.Debug('Validating User Finishes');
3956 END IF;
3957
3958 l_qte_header_rec.quote_header_id := p_quote_header_id;
3959 l_qte_header_rec.last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdatedate(p_quote_header_id);
3960 l_qte_line_rec.quote_line_id := p_qte_line_id;
3961 l_qte_line_count := l_qte_line_tbl.COUNT;
3962
3963 For counter in 1.. p_quantities.count loop
3964 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3965 IBE_UTIL.Debug('p_quantities(counter) '||p_quantities(counter));
3966 END IF;
3967 End loop;
3968
3969 For counter in 1.. p_quantities.count loop
3970 l_qte_line_tbl(counter).quantity:= p_quantities(counter);
3971 End loop;
3972 l_control_rec.calculate_tax_flag := 'Y';
3973 l_control_rec.calculate_freight_charge_flag := 'Y';
3974 --mannamra:Removing references to obsoleted profile IBE_PRICE_REQUEST_TYPE see bug 2594529 for details
3975 l_control_rec.pricing_request_type := 'ASO';--FND_PROFILE.Value('IBE_PRICE_REQUEST_TYPE');
3976 l_control_rec.header_pricing_event := FND_PROFILE.Value('IBE_INCART_PRICING_EVENT');
3977 l_control_rec.line_pricing_event := FND_API.G_MISS_CHAR;
3978 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3979 IBE_UTIL.Debug('Split_quote_line: aso_split_line_int.split_quote_line:start');
3980 END IF;
3981 aso_split_line_int.Split_Quote_line (
3982 P_Api_Version_Number => 1.0,
3983 P_Init_Msg_List => FND_API.G_TRUE,
3984 P_Commit => FND_API.G_TRUE,
3985 p_qte_header_rec => l_qte_header_rec,
3986 p_original_qte_line_rec => l_qte_line_rec,
3987 p_control_rec => l_control_rec,
3988 P_Qte_Line_Tbl => l_qte_line_tbl,
3989 X_Qte_Line_Tbl => lx_qte_line_tbl,
3990 X_Return_Status => lx_return_status,
3991 X_Msg_Count => lx_msg_count,
3992 X_Msg_Data => lx_msg_data );
3993 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3994 RAISE FND_API.G_EXC_ERROR;
3995 END IF;
3996 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3998 END IF;
3999 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4000 IBE_UTIL.Debug('Split_quote_line: aso_split_line_int.split_quote_line:end');
4001 END IF;
4002 END IF;
4003 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4004 IBE_UTIL.Debug('IBE_Quote_Misc_pvt.Split_Line Ends');
4005 END IF;
4006 -- Standard check of p_commit.
4007 IF FND_API.To_Boolean( p_commit ) THEN
4008 COMMIT WORK;
4009 END IF;
4010 -- Standard call to get message count and IF count is 1, get message info.
4011 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4012 p_count => lx_msg_count,
4013 p_data => lx_msg_data);
4014 EXCEPTION
4015 WHEN FND_API.G_EXC_ERROR THEN
4016 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4017 IBE_Util.Debug('Unexpected error IBE_Quote_Misc_pvt.Split_line');
4018 END IF;
4019 ROLLBACK TO SPLIT_LINE_PVT;
4020 x_return_status := FND_API.G_RET_STS_ERROR;
4021 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4022 p_count => lx_msg_count,
4023 p_data => lx_msg_data);
4024 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4025 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4026 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Split_line');
4027 END IF;
4028 ROLLBACK TO SPLIT_LINE_PVT;
4029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4030 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4031 p_count => lx_msg_count,
4032 p_data => lx_msg_data);
4033 WHEN OTHERS THEN
4034 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4035 IBE_Util.Debug('Unknown error IBE_Quote_Misc_pvt.Split_line');
4036 END IF;
4037 ROLLBACK TO SPLIT_LINE_PVT;
4038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4039 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4040 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
4041 l_api_name);
4042 END IF;
4043 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4044 p_count => lx_msg_count,
4045 p_data => lx_msg_data);
4046 END Split_Line;
4047
4048
4049 Procedure validate_quote(
4050 p_quote_header_id IN NUMBER
4051 ,p_save_type IN NUMBER
4052 ,x_return_status OUT NOCOPY VARCHAR2
4053 ,x_msg_count OUT NOCOPY NUMBER
4054 ,x_msg_data OUT NOCOPY VARCHAR2)
4055 IS
4056 -- Get resource_id, publish_flag and quote_status of a quote.
4057 cursor c_get_quote_details is
4058 select a.resource_id,
4059 a.publish_flag,
4060 b.status_code,
4061 a.party_id,
4062 a.cust_account_id,
4063 a.quote_source_code
4064 from aso_quote_headers a ,
4065 aso_quote_statuses_b b
4066 where a.quote_status_id = b.quote_status_id
4067 and a.quote_header_id = p_quote_header_id;
4068
4069 l_api_name CONSTANT VARCHAR2(30) := 'Validate_quote';
4070 l_resource_id NUMBER;
4071 l_publish_flag VARCHAR2(1);
4072 l_status_code VARCHAR2(30);
4073 l_source_code VARCHAR2(100);
4074 l_party_id NUMBER;
4075 l_cust_account_id NUMBER;
4076 l_validate_quote_sts VARCHAR2(2) := FND_API.G_TRUE;
4077 l_error VARCHAR2(1) := FND_API.G_FALSE;
4078
4079 CURSOR c_get_active_cart(c_quote_header_id NUMBER,
4080 c_party_id NUMBER,
4081 c_cust_account_id NUMBER) is
4082 select quote_header_id
4083 from ibe_active_quotes
4084 where quote_header_id = c_quote_header_id
4085 and party_id = c_party_id
4086 and cust_account_id = c_cust_account_id
4087 and record_type = 'CART';
4088
4089
4090 l_active_cart NUMBER;
4091 l_return_status VARCHAR2(1);
4092 l_msg_count NUMBER ;
4093 l_msg_data VARCHAR2(2000);
4094 rec_get_active_cart c_get_active_cart%rowtype;
4095
4096 BEGIN
4097 SAVEPOINT validate_quote;
4098 -- Get resource_id, publish_flag and quote_status of a quote.
4099 -- Initialize API return status to success
4100 x_return_status := FND_API.G_RET_STS_SUCCESS;
4101 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4102 IBE_UTIL.DEBUG('Begin Validate_quote' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4103 END IF;
4104 open c_get_quote_details;
4105 fetch c_get_quote_details into l_resource_id,
4106 l_publish_flag,
4107 l_status_code,
4108 l_party_id,
4109 l_cust_account_id,
4110 l_source_code;
4111 close c_get_quote_details;
4112
4113 IF l_resource_id is not null and nvl(l_publish_flag,'N')='N' THEN
4114 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4115 IBE_UTIL.DEBUG('Validate_quote, quote is unpublished' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4116 END IF;
4117
4118 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4119 FND_Message.Set_Name('IBE', 'IBE_SC_QUOTE_NOT_PUBL');
4120 FND_Msg_Pub.Add;
4121 END IF;
4122 l_error := FND_API.G_TRUE;
4123 END IF;
4124
4125 IF (p_save_type is not null and
4126 (p_save_type = END_WORKING OR p_save_type = SAVE_PAYMENT_ONLY
4127 OR p_save_type = SALES_ASSISTANCE OR p_save_type = PLACE_ORDER OR p_save_type = OP_DUPLICATE_CART)) THEN
4128 l_validate_quote_sts := FND_API.G_FALSE;
4129 END IF;
4130
4131 IF (l_validate_quote_sts = FND_API.G_TRUE) THEN
4132 IF (l_resource_id is not null) THEN
4133 IF (FND_Profile.Value('IBE_UPDATE_DRAFT_QUOTES') = 'Y' ) THEN -- Update on Draft profile enabled, only allow updates on DRAFT.
4134 IF (l_status_code <> 'DRAFT') THEN
4135 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4136 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_QUOTE_STS');
4137 FND_Msg_Pub.Add;
4138 END IF;
4139 l_error := FND_API.G_TRUE;
4140 END IF;
4141 ELSE -- update profile is not enabled, but update call for quote is coming down.
4142 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4143 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_OPERATION'); -- Invalid Operation
4144 FND_Msg_Pub.Add;
4145 END IF;
4146 l_error := FND_API.G_TRUE;
4147 END IF;
4148 ELSE -- for a cart, check for 'STORE DRAFT'
4149 IF (l_status_code <> 'STORE DRAFT') THEN
4150 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4151 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_CART_STS');
4152 FND_Msg_Pub.Add;
4153 END IF;
4154 l_error := FND_API.G_TRUE;
4155 END IF;
4156 END IF;
4157 END IF;
4158 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4159 IBE_UTIL.DEBUG('Validate_quote: Validation for one-click start');
4160 END IF;
4161 IF (p_save_type <> END_WORKING AND p_save_type <> OP_DELETE_CART) THEN
4162 IF ((p_save_type = UPDATE_EXPRESSORDER OR p_save_type = SAVE_EXPRESSORDER)
4163 AND (l_source_code <> 'IStore Oneclick')) THEN
4164 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4165 IBE_UTIL.DEBUG('Validate_quote: Oneclick operation on a non-oneclick cart');
4166 END IF;
4167 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4168 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_OPERATION'); -- Invalid Operation
4169 FND_Msg_Pub.Add;
4170 END IF;
4171 l_error := FND_API.G_TRUE;
4172 ELSE
4173 IF ((p_save_type <> UPDATE_EXPRESSORDER AND p_save_type <> SAVE_EXPRESSORDER)
4174 AND (l_source_code = 'IStore Oneclick')) THEN
4175
4176 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4177 IBE_UTIL.DEBUG('Validate_quote: Non-Oneclick operation on a oneclick cart');
4178 END IF;
4179
4180 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4181 FND_Message.Set_Name('IBE', 'IBE_SC_CART_ORDERED');
4182 FND_Msg_Pub.Add;
4183 END IF;
4184 l_error := FND_API.G_TRUE;
4185 END IF;
4186 END IF;
4187
4188 END IF;
4189
4190 IF (l_error = FND_API.G_TRUE) THEN
4191 FOR rec_get_active_cart in c_get_active_cart(p_quote_header_id,
4192 l_party_id,
4193 l_cust_account_id) LOOP
4194 l_active_cart := rec_get_active_cart.quote_header_id;
4195 IF (l_active_cart is not null) THEN
4196 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4197 IBE_UTIL.DEBUG('Validate_quote, active cart found, deactivate it' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4198 END IF;
4199
4200 IBE_QUOTE_SAVESHARE_V2_PVT.DEACTIVATE_QUOTE (
4201 P_Quote_header_id => p_quote_header_id,
4202 P_Party_id => l_party_id ,
4203 P_Cust_account_id => l_Cust_account_id ,
4204 p_api_version => 1 ,
4205 p_init_msg_list => fnd_api.g_false ,
4206 p_commit => fnd_api.g_true ,
4207 x_return_status => l_return_status ,
4208 x_msg_count => l_msg_count ,
4209 x_msg_data => l_msg_data );
4210
4211 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4212 RAISE FND_API.G_EXC_ERROR;
4213 END IF;
4214
4215 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4217 END IF;
4218 END IF;
4219 EXIT when c_get_active_cart%notfound;
4220 END LOOP;
4221 RAISE FND_API.G_EXC_ERROR;
4222 END IF;
4223
4224
4225 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4226 IBE_UTIL.DEBUG('End validate_quote' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4227 END IF;
4228
4229 EXCEPTION
4230 WHEN FND_API.G_EXC_ERROR THEN
4231 ROLLBACK TO validate_quote;
4232 x_return_status := FND_API.G_RET_STS_ERROR;
4233 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4234 p_count => x_msg_count ,
4235 p_data => x_msg_data);
4236 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4237 IBE_Util.Debug('End With Exp Exception IBE_Quote_Misc_pvt.validate_quote');
4238 END IF;
4239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4240 ROLLBACK TO validate_quote;
4241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4242 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4243 p_count => x_msg_count ,
4244 p_data => x_msg_data);
4245 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4246 IBE_Util.Debug('End with UnExp Exception IBE_Quote_Misc_pvt.validate_quote');
4247 END IF;
4248 WHEN OTHERS THEN
4249 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
4250 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
4251 l_api_name);
4252 END IF;
4253 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4254 p_count => x_msg_count ,
4255 p_data => x_msg_data);
4256 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4257 IBE_Util.Debug('End with Others Exception IBE_Quote_Misc_pvt.validate_quote');
4258 END IF;
4259 END validate_quote;
4260
4261 PROCEDURE Validate_User_Update(
4262 p_api_version_number IN NUMBER := 1.0
4263 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4264 ,p_quote_header_id IN NUMBER
4265 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
4266 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
4267 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
4268 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
4269 ,p_privilege_type_code IN VARCHAR2 := 'F'
4270 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
4271 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
4272 ,x_return_status OUT NOCOPY VARCHAR2
4273 ,x_msg_count OUT NOCOPY NUMBER
4274 ,x_msg_data OUT NOCOPY VARCHAR2)
4275
4276 IS
4277
4278 l_api_name CONSTANT VARCHAR2(30) := 'Validate_User_Update';
4279 l_api_version CONSTANT NUMBER := 1.0;
4280 l_db_quote_header_id NUMBER;
4281 l_party_id NUMBER;
4282 l_user_id NUMBER :=FND_GLOBAL.USER_ID;
4283 l_db_user_id NUMBER;
4284 l_privilege_type_code VARCHAR2(10);
4285 l_end_date_active DATE;
4286 l_upgrade_flag varchar2(1) := FND_API.G_TRUE;
4287
4288 l_last_update_date DATE;
4289 l_quote_status VARCHAR2(100);
4290 l_last_updated_by NUMBER;
4291 l_owner_party_id NUMBER;
4292 l_resource_id NUMBER;
4293 l_last_upd_party_id NUMBER;
4294 l_is_member NUMBER := null;
4295 l_err_code VARCHAR2(50) := null;
4296
4297 CURSOR c_getPartyInfo(c_user_id NUMBER) IS
4298 SELECT customer_id
4299 from fnd_user
4300 WHERE user_id = c_user_id;
4301
4302 CURSOR c_getShareeInfo IS
4303 SELECT quote_header_id,update_privilege_type_code,end_date_active, party_id, cust_account_id
4304 from ibe_sh_quote_access
4305 where quote_sharee_number = p_quote_retrieval_number
4306 and quote_header_id = p_quote_header_id;
4307
4308 CURSOR c_isSharee(c_party_id NUMBER) IS
4309 SELECT count(*)
4310 from ibe_sh_quote_access
4311 where party_id = c_party_id
4312 and quote_header_id = p_quote_header_id;
4313
4314 CURSOR c_getQuoteInfo IS
4315 SELECT quote_header_id
4316 from aso_quote_headers_all
4317 where quote_header_id = p_quote_header_id
4318 AND (party_id = l_party_id OR (party_id = p_party_id AND cust_account_id = p_cust_account_id));
4319
4320 -- 9/23/02: we're using the next cursor
4321 CURSOR c_getResourceInfo_orig IS
4322 select resource_id
4323 from jtf_rs_resource_extns
4324 where user_id = l_user_id;
4325
4326 -- 9/23/02: new cursor to check for salesrep
4327 Cursor c_getResourceInfo IS
4328 SELECT j.resource_id
4329 FROM jtf_rs_srp_vl srp, jtf_rs_resource_extns j
4330 WHERE j.user_id = l_user_id
4331 AND j.resource_id = srp.resource_id
4332 AND srp.status = 'A'
4333 AND nvl(trunc(srp.start_date_active), trunc(sysdate)) <= trunc(sysdate)
4334 AND nvl(trunc(srp.end_date_active), trunc(sysdate)) >= trunc(sysdate)
4335 AND NVL(srp.org_id,MO_GLOBAL.get_current_org_id()) = MO_GLOBAL.get_current_org_id();
4336
4337 --For the last update validation --08/06/2003
4338
4339 Cursor c_last_update_date(c_quote_hdr_id number) is
4340 SELECT status_code,
4341 a.last_update_date,
4342 a.last_updated_by,
4343 a.party_id,
4344 a.resource_id
4345 FROM aso_quote_headers a,
4346 aso_quote_statuses_vl b
4347 WHERE quote_header_id = c_quote_hdr_id
4348 and a.quote_status_id = b.quote_status_id;
4349
4350 rec_last_update_date c_last_update_date%rowtype;
4351
4352 rec_sharee_info c_getShareeInfo%rowtype;
4353
4354 -- 9/11/02: we want to check if this cart is a guest cart
4355 CURSOR c_getActiveCartTypeInfo IS
4356 select quote_source_code from aso_quote_headers_all where quote_header_id = p_quote_header_id;
4357 rec_ActiveCartType_info c_getActiveCartTypeInfo%rowtype;
4358
4359 BEGIN
4360
4361 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4362 IBE_UTIL.DEBUG('Begin validate_user_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4363 END IF;
4364
4365 -- Standard call to check for call compatibility.
4366 IF NOT FND_API.Compatible_API_Call (l_api_version,
4367 p_api_version_number,
4368 l_api_name,
4369 G_PKG_NAME )
4370 THEN
4371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4372 END IF;
4373
4374 -- Initialize message list if p_init_msg_list is set to TRUE.
4375
4376 IF FND_API.To_Boolean( p_init_msg_list ) THEN
4377 FND_Msg_Pub.initialize;
4378 END IF;
4379
4380 -- Initialize API return status to success
4381 x_return_status := FND_API.G_RET_STS_SUCCESS;
4382
4383 -- API body
4384
4385 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4386 IBE_UTIL.DEBUG('Incoming party_id is '||p_party_id);
4387 IBE_UTIL.DEBUG('Incoming cust_account_id is '||p_cust_account_id);
4388 IBE_UTIL.DEBUG('Incoming quote_header_id is '||p_quote_header_id);
4389 IBE_UTIL.DEBUG('User id obtained from environment is: '||l_user_id);
4390 IBE_UTIL.DEBUG('p_save_type is :'||p_save_type);
4391 END IF;
4392 IF (FND_API.to_Boolean(p_validate_user) AND p_quote_header_id is not null AND p_quote_header_id <> FND_API.G_MISS_NUM) Then
4393
4394 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4395 IBE_UTIL.DEBUG('Entered Validation...'|| p_validate_user);
4396 END IF;
4397
4398 IF (p_quote_retrieval_number is not null AND p_quote_retrieval_number <> FND_API.G_MISS_NUM) then
4399
4400 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4401 IBE_UTIL.DEBUG('In validating Recipient flow '||p_quote_retrieval_number||' '||to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4402 END IF;
4403
4404 OPEN c_getShareeInfo;
4405 FETCH c_getShareeInfo INTO rec_sharee_info;
4406 CLOSE c_getShareeInfo;
4407
4408 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4409 IBE_UTIL.DEBUG('rec_sharee_info.quote_header_id : '||rec_sharee_info.quote_header_id);
4410 END IF;
4411
4412 IF ((rec_sharee_info.quote_header_id is null) OR
4413 (nvl(rec_sharee_info.end_date_active,sysdate+1) <= sysdate)) then
4414 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4415 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4416 FND_Msg_Pub.Add;
4417 END IF;
4418 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4419 IBE_UTIL.DEBUG('quote_retrieval_number::quotehdrId'||'('||p_quote_retrieval_number||','||p_quote_header_id||')');
4420 END IF;
4421 RAISE FND_API.G_EXC_ERROR;
4422 END IF;
4423
4424 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4425 IBE_UTIL.DEBUG('rec_sharee_info.party_id : '||rec_sharee_info.party_id);
4426 IBE_UTIL.DEBUG('rec_sharee_info.cust_account_id : '||rec_sharee_info.cust_account_id);
4427 END IF;
4428
4429
4430 -- if we have party and acct passed in AND in the table then
4431 -- check that the passed-in user matches the recipient identity we have in the table
4432 if ((rec_sharee_info.party_id is not null) and
4433 (rec_sharee_info.cust_account_id is not null) and
4434 (p_party_id <> FND_API.G_MISS_NUM) and
4435 (p_cust_account_id <> FND_API.G_MISS_NUM)) then
4436 if ((rec_sharee_info.party_id <> p_party_id) or (rec_sharee_info.cust_account_id <> p_cust_account_id)) then
4437 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4438 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4439 FND_Msg_Pub.Add;
4440 END IF;
4441 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4442 IBE_UTIL.DEBUG('passed in partyid and account id does not match those of the retrieval number');
4443 IBE_UTIL.DEBUG('passed in partyid: ' || p_party_id || ' and account id : ' || p_cust_account_id);
4444 IBE_UTIL.DEBUG('retrieval partyid: ' || rec_sharee_info.party_id || ' and account id : ' || rec_sharee_info.cust_account_id);
4445 END IF;
4446 RAISE FND_API.G_EXC_ERROR;
4447 end if;
4448 -- otherwise, we may have a case where we can validate and then upgrade a partyless row to have a partyid and acctid
4449 elsif ((rec_sharee_info.party_id is null) and
4450 (rec_sharee_info.cust_account_id is null) and
4451 (p_party_id <> FND_API.G_MISS_NUM) and
4452 (p_cust_account_id <> FND_API.G_MISS_NUM)) then
4453 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4454 IBE_UTIL.DEBUG('we have blank partyid and acctid in the share table, see if we can upgrade this row...');
4455 IBE_UTIL.DEBUG('passed in partyid: ' || p_party_id || ' and account id : ' || p_cust_account_id);
4456 END IF;
4457 upgrade_recipient_row(
4458 p_party_id => p_party_id,
4459 p_cust_account_id => p_cust_account_id,
4460 p_retrieval_number => p_quote_retrieval_number,
4461 p_quote_header_id => p_quote_header_id,
4462 x_valid_flag => l_upgrade_flag);
4463 if (l_upgrade_flag <> FND_API.G_TRUE) then
4464 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4465 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4466 FND_Msg_Pub.Add;
4467 END IF;
4468 end if;
4469 end if;
4470
4471 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4472 IBE_UTIL.DEBUG('rec_sharee_info.update_privilege_type_code : '||rec_sharee_info.update_privilege_type_code);
4473 END IF;
4474
4475 --Skip this validation for Duplicate Action
4476 IF p_save_type <> OP_DUPLICATE_CART THEN
4477 l_privilege_type_code := rec_sharee_info.update_privilege_type_code;
4478 IF l_privilege_type_code <> 'A' THEN
4479 IF l_privilege_type_code = 'F' THEN
4480 IF p_privilege_type_code <> 'F' AND p_privilege_type_code <> 'R' THEN
4481 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4482 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
4483 FND_Msg_Pub.Add;
4484 END IF;
4485 RAISE FND_API.G_EXC_ERROR;
4486 END IF;
4487 ELSE
4488 IF p_privilege_type_code <> 'R' THEN
4489 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4490 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
4491 FND_Msg_Pub.Add;
4492 END IF;
4493 RAISE FND_API.G_EXC_ERROR; -- need error message
4494 END IF;
4495 END IF;
4496 end if;
4497 -- else, the access level is Admin and we are okay to do other validations
4498 END IF; --Duplicate action
4499 ELSE
4500 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4501 IBE_UTIL.DEBUG('no sharee number');
4502 END IF;
4503
4504 -- 9/11/02: if the cartId passed in is a Guest Cart, we should not go forth w/ the validation
4505 OPEN c_getActiveCartTypeInfo;
4506 FETCH c_getActiveCartTypeInfo INTO rec_ActiveCartType_info;
4507 CLOSE c_getActiveCartTypeInfo;
4508 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4509 IBE_UTIL.DEBUG('quote_source_code of cart passed in='||rec_ActiveCartType_info.quote_source_code);
4510 END IF;
4511 if (rec_ActiveCartType_info.quote_source_code = 'IStore Walkin') then
4512 return;
4513 end if;
4514
4515 IF ((p_party_id is not null AND p_party_id <> FND_API.G_MISS_NUM) AND (p_cust_account_id is not null AND p_cust_account_id <> FND_API.G_MISS_NUM)) THEN
4516
4517 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4518 IBE_UTIL.DEBUG('In validating Owner flow: '||p_party_id||','||p_cust_account_id||' '||to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4519 END IF;
4520 OPEN c_getQuoteInfo;
4521 FETCH c_getQuoteInfo INTO l_db_quote_header_id;
4522 CLOSE c_getQuoteInfo;
4523 IF l_db_quote_header_id is null then
4524 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4525 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4526 FND_Msg_Pub.Add;
4527 END IF;
4528 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4529 IBE_UTIL.DEBUG('partyId::custAcctId::quotehdrId'||'('||p_party_id||','||p_cust_account_id||','||p_quote_header_id||')');
4530 END IF;
4531 RAISE FND_API.G_EXC_ERROR;
4532 END IF;
4533 ELSE
4534 -- retrieving user info from environment
4535 OPEN c_getResourceInfo;
4536 FETCH c_getResourceInfo INTO l_db_user_id;
4537 CLOSE c_getResourceInfo;
4538 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4539 IBE_UTIL.DEBUG('Owner flow with env. userid: '||l_db_user_id||' '||to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4540 END IF;
4541
4542 IF l_db_user_id is null then
4543 OPEN c_getPartyInfo(l_user_id);
4544 FETCH c_getPartyInfo INTO l_party_id;
4545 CLOSE c_getPartyInfo;
4546
4547 OPEN c_getQuoteInfo;
4548 FETCH c_getQuoteInfo INTO l_db_quote_header_id;
4549 CLOSE c_getQuoteInfo;
4550
4551 IF l_db_quote_header_id is null then
4552 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4553 FND_Message.Set_Name('IBE', 'IBE_SC_USERACCESS_ERR');
4554 FND_Msg_Pub.Add;
4555 END IF;
4556 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4557 IBE_UTIL.DEBUG('partyId::quotehdrId'||'('||p_party_id||','||p_quote_header_id||')');
4558 END IF;
4559 RAISE FND_API.G_EXC_ERROR;
4560 END IF; -- end if l_db_quote_header_id is null
4561 END IF; -- end if l_db_user_id is null
4562 END IF; -- end section of user info from env
4563 END IF; -- end if no sharee number
4564
4565 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4566 IBE_UTIL.DEBUG('Validate_user_update: Before Last update date validation,p_last_update_date= '||p_last_update_date);
4567 END IF;
4568
4569 IF (p_last_update_date <> FND_API.G_MISS_DATE) THEN
4570 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4571 IBE_UTIL.DEBUG('Validate_user_update: Last update date validation START');
4572 END IF;
4573 FOR rec_last_update_date in c_last_update_date(p_quote_header_id) LOOP
4574 l_last_update_date := rec_last_update_date.last_update_date;
4575 l_quote_status := rec_last_update_date.status_code;
4576 l_last_updated_by := rec_last_update_date.last_updated_by;
4577 l_owner_party_id := rec_last_update_date.party_id;
4578 l_resource_id := rec_last_update_date.resource_id;
4579 EXIT when c_last_update_date%NOTFOUND;
4580 END LOOP;
4581 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4582 IBE_UTIL.DEBUG('Validate_user_update: l_last_update_date='||to_char(l_last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
4583 IBE_UTIL.DEBUG('Validate_user_update: p_last_update_date='||to_char(p_last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
4584 END IF;
4585 IF(l_last_update_date <> p_last_update_date) THEN
4586 IF (l_quote_status = 'ORDER SUBMITTED') THEN
4587 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4588 IBE_UTIL.DEBUG('Validate_user_update: raising Quote_already_ordered error');
4589 END IF;
4590
4591 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4592 FND_Message.Set_Name('IBE', 'IBE_SC_CART_ORDERED');
4593 FND_Msg_Pub.Add;
4594 END IF;
4595 RAISE FND_API.G_EXC_ERROR;
4596 ELSE
4597 IF ((l_last_updated_by <> l_user_id) OR (p_save_type = PLACE_ORDER)) THEN
4598 -- determine which error message we need to show
4599 OPEN c_getPartyInfo(l_last_updated_by);
4600 FETCH c_getPartyInfo INTO l_last_upd_party_id;
4601 CLOSE c_getPartyInfo;
4602 -- have to use party id to determine owner since createdby may be the sales rep
4603 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4604 IBE_UTIL.DEBUG('Validate_user_update: l_last_upd_party_id : ' || l_last_upd_party_id);
4605 IBE_UTIL.DEBUG('Validate_user_update: l_owner_party_id : ' || l_owner_party_id);
4606 IBE_UTIL.DEBUG('Validate_user_update: l_resource_id : ' || l_resource_id);
4607 END IF;
4608
4609 -- user is not last updated, and last update is the owner (another member)
4610 if (l_last_upd_party_id = l_owner_party_id) then
4611 if (l_resource_id is not null) then
4612 l_err_code := 'IBE_SC_ERR_RELOAD_Q_MEMBER_UPD';
4613 else
4614 l_err_code := 'IBE_SC_ERR_RELOAD_C_MEMBER_UPD';
4615 end if;
4616 else
4617 -- use is not last updated, and last updated is a member
4618 OPEN c_isSharee(l_last_upd_party_id);
4619 FETCH c_isSharee INTO l_is_member;
4620 CLOSE c_isSharee;
4621 if ((l_is_member is not null) and (l_is_member > 0)) then
4622 if (l_resource_id is not null) then
4623 l_err_code := 'IBE_SC_ERR_RELOAD_Q_MEMBER_UPD';
4624 else
4625 l_err_code := 'IBE_SC_ERR_RELOAD_C_MEMBER_UPD';
4626 end if;
4627 end if;
4628 end if;
4629 if (l_err_code is null) then
4630 -- otherwise, the last person to have updated the cart was a sales rep
4631 l_err_code := 'IBE_SC_ERR_RELOAD_SALESREP_UPD';
4632 end if;
4633
4634 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4635 IBE_UTIL.DEBUG('Validate_user_update: raising Quote_needs_refresh error : ' || l_err_code);
4636 END IF;
4637
4638 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4639 FND_Message.Set_Name('IBE', l_err_code);
4640 FND_Msg_Pub.Add;
4641 END IF;
4642 RAISE FND_API.G_EXC_ERROR;
4643 END IF;
4644 END IF;
4645 END IF;
4646
4647 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4648 IBE_UTIL.DEBUG('Validate_user_update: Last update date validation END');
4649 END IF;
4650
4651 END IF; -- last_update_date validation end.
4652
4653 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4654 IBE_UTIL.DEBUG('Before calling validate_quote' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4655 END IF;
4656 -- call an internal api, for quote or cart validation.
4657 validate_quote(p_quote_header_id,
4658 p_save_type,
4659 x_return_status,
4660 x_msg_count,
4661 x_msg_data);
4662
4663 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4664 IBE_UTIL.DEBUG('End validate_user_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4665 END IF;
4666 END IF; -- end if quote header id is not null
4667 EXCEPTION
4668 WHEN FND_API.G_EXC_ERROR THEN
4669 x_return_status := FND_API.G_RET_STS_ERROR;
4670 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4671 p_count => x_msg_count ,
4672 p_data => x_msg_data);
4673 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4674 IBE_Util.Debug('End:Expected exception:IBE_Quote_Misc_pvt.validate_user_update');
4675 END IF;
4676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4678 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4679 p_count => x_msg_count ,
4680 p_data => x_msg_data);
4681 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4682 IBE_Util.Debug('End:Unexpected exception:IBE_Quote_Misc_pvt.validate_user_update');
4683 END IF;
4684 WHEN OTHERS THEN
4685 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
4686 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
4687 l_api_name);
4688 END IF;
4689 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4690 p_count => x_msg_count ,
4691 p_data => x_msg_data);
4692 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4693 IBE_Util.Debug('End:Others exception:IBE_Quote_Misc_pvt.validate_user_update');
4694 END IF;
4695 END validate_user_update;
4696
4697 /*This procedure is used to save the missing party_id and cust_account_id
4698 of the recipient before cart activation*/
4699 -- assumes that the retrieval number given has no party id and accountid
4700 PROCEDURE upgrade_recipient_row(
4701 p_party_id in NUMBER,
4702 p_cust_account_id in NUMBER,
4703 p_retrieval_number in NUMBER,
4704 p_quote_header_id in NUMBER,
4705 x_valid_flag out NOCOPY VARCHAR2) is
4706
4707 cursor c_sharee_id(c_retrieval_num NUMBER) is
4708 select quote_sharee_id
4709 from ibe_sh_quote_access
4710 where quote_sharee_number = c_retrieval_num ;
4711
4712 cursor c_get_sold_to(c_quote_header_id NUMBER) is
4713 select cust_account_id, party_type
4714 from aso_quote_headers_all a, hz_parties p
4715 where a.party_id = p.party_id
4716 and quote_header_id = c_quote_header_id;
4717
4718 cursor c_get_party_type(c_party_id NUMBER) is
4719 select party_type
4720 from hz_parties
4721 where party_id = c_party_id;
4722
4723 rec_get_sold_to c_get_sold_to%rowtype;
4724
4725 l_recip_id NUMBER := NULL;
4726 l_sold_to_cust NUMBER := NULL;
4727 l_party_type_cart_owner VARCHAR2(30);
4728 l_party_type_recipient VARCHAR2(30);
4729
4730 BEGIN
4731 x_valid_flag := FND_API.G_TRUE;
4732 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4733 IBE_UTIL.DEBUG('upgrade_recipient_row: BEGIN ');
4734 IBE_UTIL.DEBUG(' p_party_id : ' || p_party_id);
4735 IBE_UTIL.DEBUG(' p_cust_account_id : ' || p_cust_account_id);
4736 IBE_UTIL.DEBUG(' p_retrieval_number: ' || p_retrieval_number);
4737 IBE_UTIL.DEBUG(' p_quote_header_id : ' || p_quote_header_id);
4738 END IF;
4739
4740 FOR rec_get_sold_to in c_get_sold_to(p_quote_header_id) LOOP
4741 l_sold_to_cust := rec_get_sold_to.cust_account_id;
4742 l_party_type_cart_owner := rec_get_sold_to.party_type;
4743 exit when c_get_sold_to%NOTFOUND;
4744 END LOOP;
4745 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4746 IBE_UTIL.DEBUG(' cart owner pty type: ' || l_party_type_cart_owner);
4747 end if;
4748 IF(l_party_type_cart_owner = 'PARTY_RELATIONSHIP') then
4749 if (p_cust_account_id <> l_sold_to_cust) THEN
4750 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4751 IBE_UTIL.DEBUG('Not upgrading as the b2b cart account id does not match the account id passed in');
4752 END IF;
4753 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4754 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4755 FND_Msg_Pub.Add;
4756 END IF;
4757 x_valid_flag := FND_API.G_FALSE;
4758 RAISE FND_API.G_EXC_ERROR;
4759 end if;
4760 elsif (l_party_type_cart_owner = 'PERSON') then
4761 OPEN c_get_party_type(p_party_id);
4762 FETCH c_get_party_type into l_party_type_recipient;
4763 CLOSE c_get_party_type;
4764
4765 if (l_party_type_cart_owner <> l_party_type_recipient) THEN
4766 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4767 IBE_UTIL.DEBUG('Not saving party and cust_account_id because the recipient is not a b2c user');
4768 IBE_UTIL.DEBUG('Recipient user type: ' || l_party_type_recipient);
4769 END IF;
4770 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4771 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4772 FND_Msg_Pub.Add;
4773 END IF;
4774 x_valid_flag := FND_API.G_FALSE;
4775 RAISE FND_API.G_EXC_ERROR;
4776 end if;
4777 END IF; -- end person check
4778
4779 if (x_valid_flag = FND_API.G_TRUE) then
4780 -- if we passed validations then upgrade the share row with the partyid and accountid
4781 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4782 IBE_UTIL.DEBUG('Upgrading the share row with input partyid and accountid.');
4783 END IF;
4784
4785 OPEN c_sharee_id(p_retrieval_number);
4786 FETCH c_sharee_id into l_recip_id;
4787 CLOSE c_sharee_id;
4788 IBE_SH_QUOTE_ACCESS_PKG.update_Row(
4789 p_QUOTE_SHAREE_ID => l_recip_id
4790 ,p_party_id => p_party_id
4791 ,p_cust_account_id => p_cust_account_id);
4792 end if;
4793
4794 END upgrade_recipient_row; -- upgrade_recipient_row
4795
4796 PROCEDURE Log_Environment_Info (
4797 p_quote_header_id in number := null
4798 ) IS
4799
4800 cursor c_getAppId
4801 is
4802 select fnd_global.resp_appl_id appId from dual;
4803 rec_AppId c_getAppId%rowtype;
4804
4805 cursor c_getRespId
4806 is
4807 select fnd_global.resp_id respId from dual;
4808 rec_RespId c_getRespId%rowtype;
4809
4810 cursor c_getUserId
4811 is
4812 select fnd_global.user_id userId from dual;
4813 rec_UserId c_getUserId%rowtype;
4814
4815 cursor c_getOrgId
4816 is
4817 SELECT (MO_GLOBAL.get_current_org_id()) orgId from dual;
4818 rec_OrgId c_getOrgId%rowtype;
4819
4820 cursor c_getEnvInfo
4821 is
4822 SELECT FND_GLOBAL.SESSION_ID session_id
4823 ,FND_GLOBAL.USER_NAME user_name
4824 ,FND_GLOBAL.LOGIN_ID login_id
4825 ,userenv('CLIENT_INFO') client_info
4826 ,userenv('LANG') lang
4827 FROM dual;
4828 rec_EnvInfo c_getEnvInfo%rowtype;
4829
4830 cursor c_getICXSessionDetails(p_session_id VARCHAR2)
4831 is
4832 SELECT SESSION_ID
4833 ,USER_ID
4834 ,RESPONSIBILITY_ID
4835 ,ORG_ID
4836 ,NLS_LANGUAGE
4837 ,CREATED_BY
4838 ,CREATION_DATE
4839 ,LAST_UPDATED_BY
4840 ,LAST_UPDATE_DATE
4841 ,LAST_UPDATE_LOGIN
4842 ,RESPONSIBILITY_APPLICATION_ID
4843 ,SECURITY_GROUP_ID
4844 ,PAGE_ID,LOGIN_ID
4845 ,TIME_OUT
4846 FROM icx_sessions
4847 WHERE session_id = p_session_id;
4848 rec_ICXSessionDetails c_getICXSessionDetails%rowtype;
4849
4850 cursor c_getMOTempTableInfo
4851 is
4852 SELECT * from MO_GLOB_ORG_ACCESS_TMP;
4853 rec_MOTempTableInfo c_getMOTempTableInfo%rowtype;
4854
4855 cursor c_getSysContext
4856 is
4857 SELECT sys_context('multi_org2','current_org_id') sys_context from dual;
4858 rec_SysContext c_getSysContext%rowtype;
4859
4860 Begin
4861 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4862 IBE_UTIL.DEBUG('Log_Environment_Info: Begin');
4863 END IF;
4864
4865 -- 1. AppId
4866 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4867 IBE_UTIL.DEBUG('Log_Environment_Info: AppId: Begin');
4868 END IF;
4869 open c_getAppId;
4870 fetch c_getAppId into rec_AppId;
4871 close c_getAppId;
4872 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4873 IBE_UTIL.DEBUG('Log_Environment_Info: AppId: End='||rec_AppId.appId);
4874 END IF;
4875
4876 -- 2. RespId
4877 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4878 IBE_UTIL.DEBUG('Log_Environment_Info: RespId: Begin');
4879 END IF;
4880 open c_getRespId;
4881 fetch c_getRespId into rec_RespId;
4882 close c_getRespId;
4883 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4884 IBE_UTIL.DEBUG('Log_Environment_Info: RespId: End='||rec_RespId.respId);
4885 END IF;
4886
4887 -- 3. UserId
4888 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4889 IBE_UTIL.DEBUG('Log_Environment_Info: UserId: Begin');
4890 END IF;
4891 open c_getUserId;
4892 fetch c_getUserId into rec_UserId;
4893 close c_getUserId;
4894 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4895 IBE_UTIL.DEBUG('Log_Environment_Info: UserId: End='||rec_UserId.userId);
4896 END IF;
4897
4898 -- 4. OrgId
4899 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4900 IBE_UTIL.DEBUG('Log_Environment_Info: OrgId: Begin');
4901 END IF;
4902 open c_getOrgId;
4903 fetch c_getOrgId into rec_OrgId;
4904 close c_getOrgId;
4905 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4906 IBE_UTIL.DEBUG('Log_Environment_Info: OrgId: End='||rec_OrgId.orgId);
4907 END IF;
4908
4909 -- 5. EnvInfo
4910 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4911 IBE_UTIL.DEBUG('Log_Environment_Info: EnvInfo: Begin');
4912 END IF;
4913 open c_getEnvInfo;
4914 fetch c_getEnvInfo into rec_EnvInfo;
4915 close c_getEnvInfo;
4916 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4917 IBE_UTIL.DEBUG('Session_id='||rec_EnvInfo.session_id);
4918 IBE_UTIL.DEBUG('UserName='||rec_EnvInfo.user_name);
4919 IBE_UTIL.DEBUG('login_id='||rec_EnvInfo.login_id);
4920 IBE_UTIL.DEBUG('Client_info='||rec_EnvInfo.client_info);
4921 IBE_UTIL.DEBUG('Language='||rec_EnvInfo.lang);
4922 IBE_UTIL.DEBUG('Log_Environment_Info: EnvInfo: End');
4923 END IF;
4924
4925 -- 6. ICXSession Details
4926 /* COMMENTED OUT..NEED TO GET THE ICX SESSION ID AND RUN THE CURSOR.
4927 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4928 IBE_UTIL.DEBUG('Log_Environment_Info: ICXSessionDetails: Begin');
4929 END IF;
4930 open c_getICXSessionDetails(rec_EnvInfo.session_id);
4931 fetch c_getICXSessionDetails into rec_ICXSessionDetails;
4932 close c_getICXSessionDetails;
4933 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4934 IBE_UTIL.DEBUG('session_id='||rec_ICXSessionDetails.session_id);
4935 IBE_UTIL.DEBUG('user_id='||rec_ICXSessionDetails.user_id);
4936 IBE_UTIL.DEBUG('responsibility_id='||rec_ICXSessionDetails.responsibility_id);
4937 IBE_UTIL.DEBUG('org_id='||rec_ICXSessionDetails.org_id);
4938 IBE_UTIL.DEBUG('nls_language='||rec_ICXSessionDetails.nls_language);
4939 IBE_UTIL.DEBUG('created_by='||rec_ICXSessionDetails.created_by);
4940 IBE_UTIL.DEBUG('creation_date='||rec_ICXSessionDetails.creation_date);
4941 IBE_UTIL.DEBUG('last_updated_by='||rec_ICXSessionDetails.last_updated_by);
4942 IBE_UTIL.DEBUG('last_update_date='||rec_ICXSessionDetails.last_update_date);
4943 IBE_UTIL.DEBUG('last_update_login='||rec_ICXSessionDetails.last_update_login);
4944 IBE_UTIL.DEBUG('responsibility_application_id='||rec_ICXSessionDetails.responsibility_application_id);
4945 IBE_UTIL.DEBUG('security_group_id='||rec_ICXSessionDetails.security_group_id);
4946 IBE_UTIL.DEBUG('page_id='||rec_ICXSessionDetails.page_id);
4947 IBE_UTIL.DEBUG('login_id='||rec_ICXSessionDetails.login_id);
4948 IBE_UTIL.DEBUG('time_out='||rec_ICXSessionDetails.time_out);
4949 IBE_UTIL.DEBUG('Log_Environment_Info: ICXSessionDetails: End');
4950 END IF; */
4951
4952 -- 7.MO Temp Table details
4953 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4954 IBE_UTIL.DEBUG('Log_Environment_Info: MOTempTableDetails: Begin');
4955 END IF;
4956 open c_getMOTempTableInfo;
4957 fetch c_getMOTempTableInfo into rec_MOTempTableInfo;
4958 close c_getMOTempTableInfo;
4959 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4960 IBE_UTIL.DEBUG('organization_id='||rec_MOTempTableInfo.organization_id);
4961 IBE_UTIL.DEBUG('organization_name='||rec_MOTempTableInfo.organization_name);
4962 IBE_UTIL.DEBUG('legal_entity_id='||rec_MOTempTableInfo.legal_entity_id);
4963 IBE_UTIL.DEBUG('legal_entity_name='||rec_MOTempTableInfo.legal_entity_name);
4964 IBE_UTIL.DEBUG('Log_Environment_Info: MOTempTableDetails: End');
4965 END IF;
4966
4967 -- 8. SysContext
4968 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4969 IBE_UTIL.DEBUG('Log_Environment_Info: SysContext: Begin');
4970 END IF;
4971 open c_getSysContext;
4972 fetch c_getSysContext into rec_SysContext;
4973 close c_getSysContext;
4974 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4975 IBE_UTIL.DEBUG('Sys_Context='||rec_SysContext.sys_context);
4976 IBE_UTIL.DEBUG('Log_Environment_Info: SysContext: End');
4977 END IF;
4978
4979 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4980 IBE_UTIL.DEBUG('Log_Environment_Info: End');
4981 END IF;
4982
4983 END Log_Environment_Info;
4984
4985 FUNCTION Get_party_name (
4986 p_party_id NUMBER,
4987 p_party_type VARCHAR2
4988 )
4989 RETURN VARCHAR2
4990 IS
4991 CURSOR C1 IS
4992 select HP.party_id,HP.party_name
4993 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
4994 and HPR.subject_type = 'PERSON'
4995 and HPR.object_type = 'ORGANIZATION'
4996 and hp.party_id=HPR.subject_id;
4997 CURSOR C2 IS
4998 select party_id,party_name
4999 from hz_parties HP where party_id = p_party_id;
5000 l_party_name VARCHAR2(360);
5001 l_party_id NUMBER;
5002 BEGIN
5003 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
5004 OPEN C1;
5005 FETCH C1 INTO l_party_id,l_party_name;
5006 IF C1%NOTFOUND OR l_party_name IS NULL THEN
5007 CLOSE C1;
5008 l_party_name := NULL;
5009 RETURN l_party_name;
5010 END IF;
5011 CLOSE C1;
5012 RETURN l_party_name;
5013 ELSE
5014 OPEN C2;
5015 FETCH C2 INTO l_party_id,l_party_name;
5016 IF C2%NOTFOUND OR l_party_name IS NULL THEN
5017 CLOSE C2;
5018 l_party_name := NULL;
5019 RETURN l_party_name;
5020 END IF;
5021 CLOSE C2;
5022 RETURN l_party_name;
5023 END IF;
5024 END Get_party_name;
5025
5026 PROCEDURE Add_Attachment(
5027 p_api_version_number IN NUMBER
5028 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5029 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5030 ,p_category_id IN VARCHAR2
5031 ,p_document_description IN VARCHAR2
5032 ,p_datatype_id IN VARCHAR2
5033 ,p_text IN LONG
5034 ,p_file_name IN VARCHAR2
5035 ,p_url IN VARCHAR2
5036 ,p_function_name IN VARCHAR2 := null
5037 ,p_quote_header_id IN NUMBER
5038 ,p_media_id IN NUMBER
5039 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
5040 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
5041 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
5042 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
5043 ,p_last_update_date IN DATE :=FND_API.G_MISS_DATE
5044 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
5045 ,x_last_update_date OUT NOCOPY DATE
5046 ,x_return_status OUT NOCOPY VARCHAR2
5047 ,x_msg_count OUT NOCOPY NUMBER
5048 ,x_msg_data OUT NOCOPY VARCHAR2
5049 )
5050 IS
5051 l_api_name CONSTANT VARCHAR2(30) := 'Add_Attachment';
5052 l_api_version CONSTANT NUMBER := 1.0;
5053 l_seq_num VARCHAR2(30) := NULL;
5054
5055 BEGIN
5056 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5057 IBE_Util.Debug('Begin IBE_Quote_Misc_pvt.Add_Attachment()');
5058 END IF;
5059
5060 -- Standard Start of API savepoint
5061 SAVEPOINT Add_Attachment;
5062
5063 -- Standard call to check for call compatibility.
5064 IF NOT FND_API.Compatible_API_Call (l_api_version,
5065 P_Api_Version_Number,
5066 l_api_name,
5067 G_PKG_NAME )
5068 THEN
5069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5070 END IF;
5071
5072 -- Initialize message list if p_init_msg_list is set to TRUE.
5073 IF FND_API.To_Boolean( p_init_msg_list ) THEN
5074 FND_Msg_Pub.initialize;
5075 END IF;
5076
5077 -- Initialize API return status to success
5078 x_return_status := FND_API.G_RET_STS_SUCCESS;
5079
5080 -- User Authentication
5081 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5082 IBE_Util.Debug('Call to Validate_User_Update');
5083 END IF;
5084
5085 IBE_Quote_Misc_pvt.Validate_User_Update
5086 ( p_init_msg_list => p_Init_Msg_List
5087 ,p_quote_header_id => p_quote_header_id
5088 ,p_party_id => p_party_id
5089 ,p_cust_account_id => p_cust_account_id
5090 ,p_validate_user => p_validate_user
5091 ,p_quote_retrieval_number => p_retrieval_number
5092 ,p_save_type => p_save_type
5093 ,p_last_update_date => p_last_update_date
5094 ,x_return_status => x_return_status
5095 ,x_msg_count => x_msg_count
5096 ,x_msg_data => x_msg_data
5097 );
5098
5099 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
5100 RAISE FND_API.G_EXC_ERROR;
5101 END IF;
5102 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
5103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5104 END IF;
5105
5106
5107 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5108 IBE_Util.Debug('Call to ASO_ATTACHMENT_INT.Add_Attachment');
5109 END IF;
5110
5111 l_seq_num := to_char(FND_CRYPTO.SmallRandomNumber);
5112 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5113 IBE_Util.Debug('l_seq_num = '||l_seq_num);
5114 END IF;
5115
5116 -- ASO Attachment Procedure Call
5117 ASO_ATTACHMENT_INT.Add_Attachment
5118 (
5119 p_api_version_number => p_api_version_number
5120 ,p_init_msg_list => p_init_msg_list
5121 ,p_commit => p_commit
5122 ,p_seq_num => l_seq_num
5123 ,p_category_id => p_category_id
5124 ,p_document_description=> p_document_description
5125 ,p_datatype_id => p_datatype_id
5126 ,p_text => p_text
5127 ,p_file_name => p_file_name
5128 ,p_url => p_url
5129 ,p_function_name => p_function_name
5130 ,p_quote_header_id => p_quote_header_id
5131 ,p_media_id => p_media_id
5132 ,x_return_status => x_return_status
5133 ,x_msg_count => x_msg_count
5134 ,x_msg_data => x_msg_data
5135 );
5136 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5137 RAISE FND_API.G_EXC_ERROR;
5138 END IF;
5139
5140 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5142 END IF;
5143
5144 -- Standard call to get message count and if count is 1, get message info.
5145 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5146 p_count => x_msg_count ,
5147 p_data => x_msg_data);
5148
5149 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5150 IBE_Util.Debug('End ASO_ATTACHMENT_INT.Add_Attachment');
5151 END IF;
5152
5153 EXCEPTION
5154 WHEN FND_API.G_EXC_ERROR THEN
5155 ROLLBACK TO Add_Attachment;
5156 x_return_status := FND_API.G_RET_STS_ERROR;
5157 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5158 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5159 p_count => x_msg_count ,
5160 p_data => x_msg_data);
5161 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5162 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Add_Attachment()');
5163 END IF;
5164
5165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5166 ROLLBACK TO Add_Attachment;
5167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5168 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5169 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5170 p_count => x_msg_count ,
5171 p_data => x_msg_data);
5172 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5173 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Add_Attachment()');
5174 END IF;
5175
5176 WHEN OTHERS THEN
5177 ROLLBACK TO Add_Attachment;
5178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5179
5180 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
5181 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
5182 l_api_name);
5183 END IF;
5184
5185 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5186 p_count => x_msg_count ,
5187 p_data => x_msg_data);
5188 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5189 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Add_Attachment()');
5190 END IF;
5191
5192 END Add_Attachment;
5193
5194
5195 PROCEDURE Delete_Attachment(
5196 p_api_version_number IN NUMBER
5197 ,p_init_msg_list IN VARCHAR2
5198 ,p_commit IN VARCHAR2
5199 ,p_quote_header_id IN NUMBER
5200 ,p_quote_attachment_ids IN JTF_VARCHAR2_TABLE_100
5201 ,p_last_update_date IN DATE
5202 ,p_party_id IN NUMBER
5203 ,p_cust_account_id IN NUMBER
5204 ,p_retrieval_number IN NUMBER
5205 ,x_last_update_date OUT NOCOPY DATE
5206 ,x_return_status OUT NOCOPY VARCHAR2
5207 ,x_msg_count OUT NOCOPY NUMBER
5208 ,x_msg_data OUT NOCOPY VARCHAR2
5209 )
5210 IS
5211 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachment';
5212 l_api_version CONSTANT NUMBER := 1.0;
5213
5214 BEGIN
5215 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5216 IBE_Util.Debug('Begin IBE_Quote_Misc_pvt.Delete_Attachment()');
5217 END IF;
5218
5219 -- Standard Start of API savepoint
5220 SAVEPOINT Add_Attachment;
5221
5222 -- Standard call to check for call compatibility.
5223 IF NOT FND_API.Compatible_API_Call (l_api_version,
5224 P_Api_Version_Number,
5225 l_api_name,
5226 G_PKG_NAME )
5227 THEN
5228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5229 END IF;
5230
5231 -- Initialize message list if p_init_msg_list is set to TRUE.
5232 IF FND_API.To_Boolean( p_init_msg_list ) THEN
5233 FND_Msg_Pub.initialize;
5234 END IF;
5235
5236 -- Initialize API return status to success
5237 x_return_status := FND_API.G_RET_STS_SUCCESS;
5238
5239 -- User Authentication
5240 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5241 IBE_Util.Debug('Call to Validate_User_Update');
5242 END IF;
5243
5244 IBE_Quote_Misc_pvt.Validate_User_Update(
5245 p_quote_header_id => p_quote_header_id
5246 ,p_party_id => p_party_id
5247 ,p_cust_account_id => p_cust_account_id
5248 ,p_validate_user => FND_API.G_TRUE
5249 ,p_quote_retrieval_number => p_retrieval_number
5250 ,p_last_update_date => p_last_update_date
5251 ,x_return_status => x_return_status
5252 ,x_msg_count => x_msg_count
5253 ,x_msg_data => x_msg_data
5254 );
5255
5256 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
5257 RAISE FND_API.G_EXC_ERROR;
5258 END IF;
5259 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
5260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5261 END IF;
5262
5263
5264 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5265 IBE_Util.Debug('Call to ASO_ATTACHMENT_INT.Delete_Attachment');
5266 END IF;
5267
5268 -- ASO Attachment Procedure Call
5269 ASO_ATTACHMENT_INT.Delete_Attachments
5270 (
5271 p_api_version_number => p_api_version_number
5272 ,p_init_msg_list => p_init_msg_list
5273 ,p_commit => p_commit
5274 ,p_quote_header_id => p_quote_header_id
5275 ,p_quote_attachment_ids => p_quote_attachment_ids
5276 ,x_return_status => x_return_status
5277 ,x_msg_count => x_msg_count
5278 ,x_msg_data => x_msg_data
5279 );
5280
5281 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5282 RAISE FND_API.G_EXC_ERROR;
5283 END IF;
5284
5285 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5287 END IF;
5288
5289 -- Standard call to get message count and if count is 1, get message info.
5290 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5291 p_count => x_msg_count ,
5292 p_data => x_msg_data);
5293
5294 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5295 IBE_Util.Debug('End ASO_ATTACHMENT_INT.Delete_Attachment');
5296 END IF;
5297
5298 EXCEPTION
5299 WHEN FND_API.G_EXC_ERROR THEN
5300 ROLLBACK TO Add_Attachment;
5301 x_return_status := FND_API.G_RET_STS_ERROR;
5302 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5303 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5304 p_count => x_msg_count ,
5305 p_data => x_msg_data);
5306 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5307 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Delete_Attachment()');
5308 END IF;
5309
5310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5311 ROLLBACK TO Add_Attachment;
5312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5313 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5314 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5315 p_count => x_msg_count ,
5316 p_data => x_msg_data);
5317 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5318 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Delete_Attachment()');
5319 END IF;
5320
5321 WHEN OTHERS THEN
5322 ROLLBACK TO Add_Attachment;
5323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5324
5325 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
5326 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
5327 l_api_name);
5328 END IF;
5329
5330 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5331 p_count => x_msg_count ,
5332 p_data => x_msg_data);
5333 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5334 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Delete_Attachment()');
5335 END IF;
5336
5337 END Delete_Attachment;
5338
5339 FUNCTION get_aso_quote_status (p_quote_header_id NUMBER) RETURN VARCHAR2 is
5340
5341 CURSOR c_quote_status_code (quote_hdr_id number) is
5342 select status_code
5343 from aso_quote_headers_all a, aso_quote_statuses_vl b
5344 where quote_header_id = quote_hdr_id
5345 and a.quote_status_id = b.quote_status_id;
5346
5347 rec_quote_status_code c_quote_status_code%rowtype;
5348 l_quote_status_code aso_quote_statuses_vl.status_code%type;
5349
5350
5351 BEGIN
5352
5353 for rec_quote_status_code in c_quote_status_code(p_quote_header_id) loop
5354 l_quote_status_code := rec_quote_status_code.status_code;
5355 exit when c_quote_status_code%notfound;
5356 end loop;
5357
5358 RETURN l_quote_status_code;
5359
5360 END get_aso_quote_status;
5361
5362 PROCEDURE get_primary_file_id(p_quote_id IN NUMBER,
5363 x_file_id OUT NOCOPY NUMBER) is
5364
5365 file_id NUMBER;
5366 BEGIN
5367
5368 IF (OKC_TERMS_UTIL_GRP.has_terms (p_document_type => 'QUOTE', p_document_id => p_quote_id) = 'Y') THEN
5369
5370 x_file_id := OKC_TERMS_UTIL_GRP.get_primary_terms_doc_file_id(p_document_type => 'QUOTE',
5371 p_document_id => p_quote_id);
5372 ELSE
5373 x_file_id := 0;
5374 END IF;
5375
5376 END get_primary_file_id;
5377
5378 END IBE_Quote_Misc_pvt;