[Home] [Help]
PACKAGE BODY: APPS.IBE_QUOTE_MISC_PVT
Source
1 PACKAGE BODY IBE_Quote_Misc_pvt AS
2 /* $Header: IBEVQMIB.pls 120.16.12020000.2 2012/08/14 06:44:52 amaheshw 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 --bug 10016159 FROM IBY_TRXN_EXTENSIONS_V
1924 FROM IBY_EXTN_INSTR_DETAILS_V
1925 WHERE trxn_extension_id = p_trxn_extn_id;
1926
1927 BEGIN
1928
1929 OPEN c_getPaymentRec(p_quote_header_id);
1930 LOOP
1931 FETCH c_getPaymentRec INTO
1932 l_payment_rec.payment_id
1933 ,l_payment_rec.creation_date
1934 ,l_payment_rec.created_by
1935 ,l_payment_rec.last_update_date
1936 ,l_payment_rec.last_updated_by
1937 ,l_payment_rec.last_update_login
1938 ,l_payment_rec.request_id
1939 ,l_payment_rec.program_application_id
1940 ,l_payment_rec.program_id
1941 ,l_payment_rec.program_update_date
1942 ,l_payment_rec.quote_header_id
1943 ,l_payment_rec.quote_line_id
1944 ,l_payment_rec.payment_type_code
1945 ,l_payment_rec.payment_ref_number
1946 ,l_payment_rec.payment_option
1947 ,l_payment_rec.payment_term_id
1948 ,l_payment_rec.credit_card_code
1949 ,l_payment_rec.credit_card_holder_name
1950 ,l_payment_rec.credit_card_expiration_date
1951 ,l_payment_rec.credit_card_approval_code
1952 ,l_payment_rec.credit_card_approval_date
1953 ,l_payment_rec.payment_amount
1954 ,l_payment_rec.attribute_category
1955 ,l_payment_rec.attribute1
1956 ,l_payment_rec.attribute2
1957 ,l_payment_rec.attribute3
1958 ,l_payment_rec.attribute4
1959 ,l_payment_rec.attribute5
1960 ,l_payment_rec.attribute6
1961 ,l_payment_rec.attribute7
1962 ,l_payment_rec.attribute8
1963 ,l_payment_rec.attribute9
1964 ,l_payment_rec.attribute10
1965 ,l_payment_rec.attribute11
1966 ,l_payment_rec.attribute12
1967 ,l_payment_rec.attribute13
1968 ,l_payment_rec.attribute14
1969 ,l_payment_rec.attribute15
1970 ,l_payment_rec.trxn_extension_id;
1971 IF l_payment_rec.trxn_extension_id IS NOT NULL THEN
1972 OPEN c_getInstrumentId(l_payment_rec.trxn_extension_id);
1973 LOOP
1974 FETCH c_getInstrumentId INTO l_payment_rec.instr_assignment_id;
1975 EXIT WHEN c_getInstrumentId%NOTFOUND;
1976 END LOOP;
1977 CLOSE c_getInstrumentId;
1978 END IF;
1979 EXIT WHEN c_getPaymentRec%NOTFOUND;
1980 l_payment_tbl(l_index) := l_payment_rec;
1981 l_index := l_index +1;
1982 END LOOP;
1983 CLOSE c_getPaymentRec;
1984
1985 RETURN l_payment_tbl;
1986 END getHeaderPaymentTbl;
1987
1988 FUNCTION getShareePrivilege(
1989 p_quote_header_Id IN NUMBER
1990 ,p_sharee_number IN NUMBER
1991 ) RETURN VARCHAR2
1992 IS
1993 l_privilege_type_code VARCHAR2(100) := 'X';
1994 BEGIN
1995
1996 SELECT update_privilege_type_code
1997 INTO l_privilege_type_code
1998 FROM IBE_SH_QUOTE_ACCESS
1999 WHERE quote_header_id = p_quote_header_id
2000 AND quote_sharee_number = p_sharee_number;
2001
2002 RETURN l_privilege_type_code;
2003
2004 EXCEPTION
2005 WHEN TOO_MANY_ROWS THEN
2006 RETURN 'XM';
2007 WHEN NO_DATA_FOUND THEN
2008 RETURN 'XN';
2009 END getShareePrivilege;
2010
2011 FUNCTION getUserType(
2012 p_partyId IN Varchar2
2013 ) RETURN VARCHAR2
2014 IS
2015 l_PartyType Varchar2(30);
2016
2017 Cursor c_hz_parties(c_party_id NUMBER) IS
2018 SELECT party_type
2019 FROM hz_parties
2020 WHERE party_id = c_party_id;
2021 c_hz_parties_rec c_hz_parties%rowtype;
2022
2023 BEGIN
2024 FOR c_hz_parties_rec IN c_hz_parties(p_partyId) LOOP
2025 l_PartyType := rtrim(c_hz_parties_rec.party_type);
2026 END LOOP;
2027
2028 If (l_PartyType = 'PERSON') Then
2029 return 'B2C';
2030 else
2031 return 'B2B';
2032 End If;
2033
2034 END getUserType;
2035
2036 PROCEDURE validateQuoteLastUpdateDate(
2037 p_api_version_number IN NUMBER
2038 ,p_quote_header_id IN NUMBER
2039 ,p_last_update_date IN DATE
2040 ,X_Return_Status OUT NOCOPY VARCHAR2
2041 ,X_Msg_Count OUT NOCOPY NUMBER
2042 ,X_Msg_Data OUT NOCOPY VARCHAR2
2043 )
2044 IS
2045 l_api_name CONSTANT VARCHAR2(30) := 'validateQuoteLastUpdateDate';
2046 l_api_version CONSTANT NUMBER := 1.0;
2047 l_last_update_date DATE;
2048 BEGIN
2049 SAVEPOINT validateLastUpdate_pvt;
2050 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2051 P_Api_Version_Number,
2052 l_api_name,
2053 G_PKG_NAME )
2054 THEN
2055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2056 END IF;
2057
2058 x_return_status := FND_API.G_RET_STS_SUCCESS;
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 ---converting ShoppingList, saved cart, Quote to Active shopping cart
2472 PROCEDURE Check_Item_IDs(
2473 p_api_version IN NUMBER := 1 ,
2474 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2475 p_cust_id IN NUMBER ,
2476 p_organization_id IN NUMBER ,
2477 p_minisite_id IN NUMBER ,
2478 x_inventory_item_id_tbl IN OUT NOCOPY jtf_number_table ,
2479 x_return_status OUT NOCOPY VARCHAR2 ,
2480 x_msg_count OUT NOCOPY NUMBER ,
2481 x_msg_data OUT NOCOPY VARCHAR2
2482 )
2483 IS
2484 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_IDs';
2485 l_api_version CONSTANT NUMBER := 1.0;
2486
2487 l_error_code VARCHAR2(30);
2488 l_error_flag VARCHAR2(30);
2489 l_error_message VARCHAR2(300);
2490
2491 l_attribute_value VARCHAR2(30);
2492 l_count NUMBER;
2493 l_item_exists NUMBER;
2494 l_inventory_item_id NUMBER;
2495 BEGIN
2496 /*inv_debug.message('ssia', 'customer id is ' || p_cust_id);
2497 inv_debug.message('ssia', 'p_organization_id is ' || p_organization_id);
2498 inv_debug.message('ssia', 'p_minisite_id is ' || p_minisite_id);*/
2499
2500 -- Standard call to check for call compatibility
2501 IF NOT FND_API.Compatible_API_Call(l_api_version,
2502 p_api_version,
2503 l_api_name ,
2504 g_pkg_name)
2505 THEN
2506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2507 END IF;
2508
2509 -- Initialize message list if p_init_msg_list is set to TRUE
2510 IF FND_API.to_Boolean(p_init_msg_list) THEN
2511 FND_MSG_PUB.initialize;
2512 END IF;
2513
2514 -- Initialize API return status to success
2515 x_return_status := FND_API.G_RET_STS_SUCCESS;
2516
2517 -- API body
2518 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2519 IBE_Util.Debug('IBE_Quote_Misc_pvt.Check_Item_IDs(+)...');
2520 END IF;
2521
2522 l_count := x_inventory_item_id_tbl.COUNT;
2523
2524
2525
2526
2527 FOR i IN 1..l_count LOOP
2528 IF x_inventory_item_id_tbl(i) IS NOT NULL THEN
2529 --inv_debug.message('ssia', 'p_cust_item_number is ' || p_cust_item_number_tbl(i));
2530
2531 -- get inventory_item_id for each customer_item_number
2532
2533
2534
2535 l_inventory_item_id := x_inventory_item_id_tbl(i);
2536 --inv_debug.message('ssia', 'l_inventory_item_id is ' || l_inventory_item_id);
2537 select count(s.inventory_item_id)
2538 into l_item_exists
2539 from ibe_dsp_section_items s, ibe_dsp_msite_sct_items b
2540 where s.section_item_id = b.section_item_id
2541 and b.mini_site_id = p_minisite_id
2542 and s.inventory_item_id = l_inventory_item_id
2543 and (s.end_date_active > sysdate or s.end_date_active is null )
2544 and s.start_date_active < sysdate;
2545
2546 if( l_item_exists > 0 ) then
2547 --inv_debug.message('ssia', 'item exists');
2548 x_inventory_item_id_tbl(i) := l_inventory_item_id;
2549 else
2550 --inv_debug.message('ssia', 'item not exists');
2551 x_inventory_item_id_tbl(i) := 0;
2552 end if;
2553 END IF;
2554
2555 END LOOP;
2556
2557
2558 -- Standard call to get message count and if count is 1, get message info.
2559 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2560 p_count => x_msg_count,
2561 p_data => x_msg_data);
2562 --inv_debug.message('ssia', 'x_msg_count is ' || x_msg_count);
2563 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2564 IBE_Util.Debug('Ibe_Shopcart_Pvt10.Load_Merchant_InvId(-)...');
2565 END IF;
2566 EXCEPTION
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2570 p_count => x_msg_count,
2571 p_data => x_msg_data);
2572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2574 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2575 p_count => x_msg_count,
2576 p_data => x_msg_data);
2577 WHEN OTHERS THEN
2578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2580 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2581 l_api_name);
2582 END IF;
2583
2584 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2585 p_count => x_msg_count,
2586 p_data => x_msg_data);
2587
2588 END Check_Item_IDs;
2589
2590
2591
2592 procedure get_load_errors(
2593 X_reason_code OUT NOCOPY JTF_VARCHAR2_TABLE_100 ,
2594 p_api_version IN NUMBER := 1.0 ,
2595 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2596 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2597 x_return_status OUT NOCOPY VARCHAR2 ,
2598 x_msg_count OUT NOCOPY NUMBER ,
2599 x_msg_data OUT NOCOPY VARCHAR2 ,
2600 P_quote_header_id IN number := FND_API.G_MISS_NUM,
2601 P_Load_type IN number := FND_API.G_MISS_NUM,
2602 P_quote_number IN number := FND_API.G_MISS_NUM,
2603 P_quote_version IN number := FND_API.G_MISS_NUM,
2604 P_party_id IN number := FND_API.G_MISS_NUM, -- only involved in sharee w/o retr num
2605 P_cust_account_id IN number := FND_API.G_MISS_NUM, -- only involved in sharee w/o retr num
2606 p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM,
2607 P_share_type IN number := -1, -- defaulted to no share type
2608 p_access_level IN number := 0) is
2609
2610 Cursor c_cart_columns (quote_hdr_id number) is
2611 select resource_id, status_code, publish_flag, quote_expiration_date,
2612 max_version_flag , order_id, quote_name
2613 from aso_quote_headers_all a, aso_quote_statuses_vl b
2614 where quote_header_id = quote_hdr_id
2615 and a.quote_status_id = b.quote_status_id;
2616
2617 Cursor c_cart_from_number(quote_num number, quote_ver number) is
2618 select quote_header_id
2619 from aso_quote_headers_all a
2620 where quote_number = quote_num
2621 and quote_version = quote_ver;
2622
2623 Cursor c_retrieval_number (c_retrieval_number NUMBER) is
2624 select quote_sharee_number, quote_sharee_id, end_date_active, update_privilege_type_code, quote_header_id
2625 from IBE_SH_QUOTE_ACCESS
2626 where quote_sharee_number = c_retrieval_number;
2627
2628 Cursor c_recipient_no_retnum (c_quote_header_id NUMBER, c_party_id NUMBER, c_account_id NUMBER) is
2629 select quote_sharee_number, quote_sharee_id, end_date_active, update_privilege_type_code
2630 from IBE_SH_QUOTE_ACCESS
2631 where quote_header_id = c_quote_header_id
2632 and party_id = c_party_id
2633 and cust_account_id = c_account_id;
2634
2635
2636
2637 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_Quote_Misc_pvt';
2638 l_api_name CONSTANT VARCHAR2(50) := 'Get_load_errors_pvt';
2639 l_api_version NUMBER := 1.0;
2640 -- these constants need to be kept in sync w/ Quote.java's static defines
2641 L_CART_LOAD_TYPE CONSTANT number := 0;
2642 L_QUOTE_LOAD_TYPE CONSTANT number := 1;
2643 L_LOAD_FORUPDATE CONSTANT number := 2;
2644 L_LOAD_EXPRESSORDER CONSTANT number := 3;
2645
2646 L_NO_SHARE_TYPE CONSTANT number := -1;
2647 L_UN_SHARED_TYPE CONSTANT number := 0;
2648 L_SHARED_BY_TYPE CONSTANT number := 1;
2649 L_SHARED_TO_TYPE CONSTANT number := 2;
2650
2651 L_NO_ACCESS_LEVEL CONSTANT number := 0;
2652 L_READ_ONLY CONSTANT number := 1;
2653 L_UPDATE CONSTANT number := 2;
2654 L_FULL CONSTANT number := 3;
2655
2656 l_quote_header_id NUMBER;
2657 l_resource_id number :=fnd_api.g_miss_num;
2658 l_status_code varchar2(100) :=fnd_api.g_miss_char;
2659 l_publish_flag varchar2(10) :=fnd_api.g_miss_char;
2660 l_quote_type varchar2(10) :=fnd_api.g_miss_char;
2661 l_return_status VARCHAR2(100);
2662 l_msg_count NUMBER;
2663 l_msg_data VARCHAR2(2000);
2664 l_max_version_flag varchar2(2);
2665 l_expiration_date date;
2666 table_counter number := 1;
2667 l_end_date_active DATE;
2668 l_order_id NUMBER;
2669 l_recipient_id NUMBER;
2670 l_access_code VARCHAR2(10) :=fnd_api.g_miss_char;
2671 l_quote_name VARCHAR2(2000);
2672 l_exp_quote_header_id NUMBER;
2673
2674 rec_cart_columns c_cart_columns%rowtype;
2675 rec_cart_from_number c_cart_from_number%rowtype;
2676 rec_retrieval_number c_retrieval_number%rowtype;
2677 rec_recipient_no_retnum c_recipient_no_retnum%rowtype;
2678
2679 Begin
2680 -- Standard Start of API savepoint
2681 SAVEPOINT Get_load_errors_pvt;
2682
2683 -- Standard call to check for call compatibility.
2684 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
2685 p_api_version,
2686 L_API_NAME ,
2687 G_PKG_NAME )
2688 THEN
2689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2690 END IF;
2691
2692 -- Initialize message list if p_init_msg_list is set to TRUE.
2693 IF FND_API.To_Boolean(p_init_msg_list) THEN
2694 FND_Msg_Pub.initialize;
2695 END IF;
2696
2697 -- Initialize API return status to success
2698 x_return_status := FND_API.G_RET_STS_SUCCESS;
2699 --Start of API Body
2700 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2701 IBE_UTIL.debug('incoming quote_header_id in get_load_errors is '||p_quote_header_id);
2702 IBE_UTIL.debug('incoming retrievalnumber in get_load_errors is '||p_retrieval_number);
2703 IBE_UTIL.debug('incoming partyid in get_load_errors is '||p_party_id);
2704 IBE_UTIL.debug('incoming accountid in get_load_errors is '||p_cust_account_id);
2705 IBE_UTIL.debug('incoming sharetype in get_load_errors is '||p_share_type);
2706 IBE_UTIL.debug('incoming accesslevel in get_load_errors is '||p_access_level);
2707 END IF;
2708
2709 X_reason_code := JTF_VARCHAR2_TABLE_100();
2710 If (p_quote_header_id <> fnd_api.g_miss_num) then
2711 l_exp_quote_header_id := p_quote_header_id;
2712 For rec_cart_columns in c_cart_columns(p_quote_header_id) loop
2713 L_resource_id := rec_cart_columns.resource_id;
2714 l_status_code := rec_cart_columns.status_code;
2715 l_publish_flag := rec_cart_columns.publish_flag;
2716 l_expiration_date := rec_cart_columns.quote_expiration_date;
2717 l_max_version_flag := rec_cart_columns.max_version_flag;
2718 l_order_id := rec_cart_columns.order_id;
2719 l_quote_name := rec_cart_columns.quote_name;
2720 exit when c_cart_columns%notfound;
2721 end loop;
2722 Elsif ((p_quote_number <> fnd_api.g_miss_num) and (p_quote_version <> fnd_api.g_miss_num)) then
2723 For rec_cart_from_number in c_cart_from_number(p_quote_number, p_quote_version) loop
2724 l_quote_header_id := rec_cart_from_number.quote_header_id;
2725 For rec_cart_columns in c_cart_columns(l_quote_header_id) loop
2726 L_resource_id := rec_cart_columns.resource_id;
2727 l_status_code := rec_cart_columns.status_code;
2728 l_publish_flag := rec_cart_columns.publish_flag;
2729 l_expiration_date := rec_cart_columns.quote_expiration_date;
2730 l_max_version_flag := rec_cart_columns.max_version_flag;
2731 exit when c_cart_columns%notfound;
2732 end loop;
2733 exit when c_cart_from_number%notfound;
2734 end loop;
2735 End if;
2736
2737 -- 1st half of errors for share information
2738 -- only do these checks if given retrieval number or qtehdrid, ptyid, acctid, and sharetype
2739 If((p_retrieval_number <> FND_API.G_MISS_NUM) or
2740 ((p_retrieval_number = FND_API.G_MISS_NUM)
2741 and (p_share_type = L_SHARED_TO_TYPE)
2742 and (p_party_id <> FND_API.G_MISS_NUM)
2743 and (p_cust_account_id <> FND_API.G_MISS_NUM)
2744 and (p_quote_header_id <> FND_API.G_MISS_NUM))) then
2745 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2746 IBE_UTIL.debug('Checking for recipient info...');
2747 end if;
2748 If(p_retrieval_number <> FND_API.G_MISS_NUM) then
2749 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2750 IBE_UTIL.debug('get recipient info based on retrieval number');
2751 end if;
2752 for rec_retrieval_number in c_retrieval_number(p_retrieval_number) loop
2753 l_end_date_active := rec_retrieval_number.end_date_active;
2754 l_recipient_id := rec_retrieval_number.quote_sharee_id;
2755 l_access_code := rec_retrieval_number.update_privilege_type_code;
2756 l_quote_header_id := rec_retrieval_number.quote_header_id;
2757 exit when c_retrieval_number%NOTFOUND;
2758 end loop;
2759 if((p_quote_header_id = fnd_api.g_miss_num) and (l_quote_header_id is not null)) then
2760 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2761 IBE_UTIL.debug('tried to load with retreival number only (no cartid)');
2762 IBE_UTIL.debug('but the retrieval number was valid so get cart info... ');
2763 end if;
2764 l_exp_quote_header_id := l_quote_header_id;
2765 For rec_cart_columns in c_cart_columns(l_quote_header_id) loop
2766 L_resource_id := rec_cart_columns.resource_id;
2767 l_status_code := rec_cart_columns.status_code;
2768 l_publish_flag := rec_cart_columns.publish_flag;
2769 l_expiration_date := rec_cart_columns.quote_expiration_date;
2770 l_max_version_flag := rec_cart_columns.max_version_flag;
2771 l_quote_name := rec_cart_columns.quote_name;
2772 exit when c_cart_columns%notfound;
2773 end loop;
2774 end if;
2775 end if;
2776
2777 If((p_retrieval_number = FND_API.G_MISS_NUM)
2778 and (p_share_type = L_SHARED_TO_TYPE)
2779 and (p_party_id <> FND_API.G_MISS_NUM)
2780 and (p_cust_account_id <> FND_API.G_MISS_NUM)
2781 and (p_quote_header_id <> FND_API.G_MISS_NUM)) then
2782 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2783 IBE_UTIL.debug('get recipient info based on cartid, partyid, acctid');
2784 end if;
2785 for rec_recipient_no_retnum in c_recipient_no_retnum(p_quote_header_id, p_party_id, p_cust_account_id) loop
2786 l_end_date_active := rec_recipient_no_retnum.end_date_active;
2787 l_recipient_id := rec_recipient_no_retnum.quote_sharee_id;
2788 l_access_code := rec_recipient_no_retnum.update_privilege_type_code;
2789 exit when c_retrieval_number%NOTFOUND;
2790 end loop;
2791 end if;
2792
2793 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2794 IBE_UTIL.debug('l_end_date_active: '||TO_CHAR(l_end_date_active, 'mm/dd/yyyy:hh24:MI:SS'));
2795 IBE_UTIL.debug('l_recipient_id: '||l_recipient_id);
2796 IBE_UTIL.debug('l_access_code: '||l_access_code);
2797 end if;
2798
2799 If ((p_retrieval_number <> FND_API.G_MISS_NUM) and (l_recipient_id is NULL)) then
2800 -- if we were given a retrieval number and it was not in the database
2801 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2802 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_RETRIEVAL_NUM');
2803 END IF;
2804 if(table_counter = 1) then
2805 x_reason_code.extend();
2806 X_reason_code(table_counter) := 'IBE_SC_INVALID_RETRIEVAL_NUM';
2807 Table_counter := table_counter+1;
2808 end if;
2809
2810 Elsif (l_recipient_id is NULL) then
2811 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2812 IBE_UTIL.debug('Including the Error code: IBE_SC_ERR_PRIVILEGE');
2813 END IF;
2814 if(table_counter = 1) then
2815 x_reason_code.extend();
2816 X_reason_code(table_counter) := 'IBE_SC_ERR_PRIVILEGE';
2817 Table_counter := table_counter+1;
2818 end if;
2819
2820 Elsif ((p_access_level = L_UPDATE) or (p_access_level = L_FULL)) then
2821 -- if a certain access level was passed in, then see if the db access level is sufficient
2822 if (p_access_level = L_UPDATE and (l_access_code <> 'F' or l_access_code <> 'A')) or
2823 (p_access_level = L_FULL and (l_access_code <> 'A')) then
2824 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2825 IBE_UTIL.debug('Including the Error code: IBE_SC_ERR_PRIVILEGE');
2826 END IF;
2827 if(table_counter = 1) then
2828 x_reason_code.extend();
2829 X_reason_code(table_counter) := 'IBE_SC_ERR_PRIVILEGE';
2830 Table_counter := table_counter+1;
2831 end if;
2832 end if;
2833
2834 Elsif (Upper(l_status_code) = 'INACTIVE') then
2835 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2836 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_DELETED');
2837 END IF;
2838 if(table_counter = 1) then
2839 x_reason_code.extend();
2840 X_reason_code(table_counter) := 'IBE_SC_CART_DELETED';
2841 Table_counter := table_counter+1;
2842 end if;
2843
2844 Elsif (l_order_id is NOT NULL) then
2845 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2846 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_ORDERED');
2847 END IF;
2848 if(table_counter = 1) then
2849 x_reason_code.extend();
2850 X_reason_code(table_counter) := 'IBE_SC_CART_ORDERED';
2851 Table_counter := table_counter+1;
2852 end if;
2853
2854 Elsif ((l_end_date_active is NOT NULL and l_end_date_active < sysdate) or
2855 (l_recipient_id is NULL)) then
2856 -- if the row has been end dated or we were unable to find a recip row by party, acct, and cart
2857 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2858 IBE_UTIL.debug('Including the Error code: IBE_SC_ERR_USERACCESS');
2859 END IF;
2860 if(table_counter = 1) then
2861 x_reason_code.extend();
2862 X_reason_code(table_counter) := 'IBE_SC_ERR_USERACCESS';
2863 Table_counter := table_counter+1;
2864 end if;
2865
2866 End if;
2867
2868 End If;
2869 -- 2nd half of errors for cart information
2870 -- only do these latter checks if we had a request for a cart - either by id or number and version
2871 if ((p_quote_header_id <> fnd_api.g_miss_num) or
2872 ((p_quote_number <> fnd_api.g_miss_num)
2873 and (p_quote_version <> fnd_api.g_miss_num))) then
2874 if(trunc(l_expiration_date) < trunc(sysdate)) then
2875 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2876 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_EXPIRED');
2877 END IF;
2878 if(table_counter = 1) then
2879 x_reason_code.extend();
2880 X_reason_code(table_counter) := 'IBE_SC_CART_EXPIRED';
2881 Table_counter := table_counter+1;
2882 end if;
2883 End if;
2884 If(l_max_version_flag = 'N') then
2885 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2886 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_MAX_VER_FLAG_N');
2887 END IF;
2888 if(table_counter = 1) then
2889 x_reason_code.extend();
2890 X_reason_code(table_counter) := 'IBE_SC_QUOTE_MAX_VER_FLAG_N';
2891 Table_counter := table_counter+1;
2892 end if;
2893 End If;
2894 If (p_load_type = L_CART_LOAD_TYPE) then
2895 If (l_resource_id is not null and (l_resource_id <> FND_API.G_MISS_NUM)) then
2896 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2897 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_HAS_RESOURCEID');
2898 END IF;
2899 if(table_counter = 1) then
2900 x_reason_code.extend();
2901 X_reason_code(Table_counter) := 'IBE_SC_CART_HAS_RESOURCEID';
2902 Table_counter := table_counter+1;
2903 end if;
2904 End If;
2905 --Status code 28 is for "store draft"
2906 If (upper(l_status_code) <> 'STORE DRAFT') then
2907 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2908 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_CART_STS');
2909 END IF;
2910 if(table_counter = 1) then
2911 x_reason_code.extend();
2912 X_reason_code(Table_counter ) := 'IBE_SC_INVALID_CART_STS';
2913 Table_counter := table_counter+1;
2914 end if;
2915 End if;
2916
2917 If(l_publish_flag <> 'F') then
2918 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2919 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_NOT_PUBL');
2920 END IF;
2921 if(table_counter = 1) then
2922 x_reason_code.extend();
2923 X_reason_code(Table_counter) := 'IBE_SC_CART_NOT_PUBL';
2924 Table_counter := table_counter+1;
2925 end if;
2926 End if;
2927 Elsif(p_load_type = L_QUOTE_LOAD_TYPE) then
2928 If (l_resource_id is null) then
2929 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2930 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_NEEDS_RESOURCEID');
2931 END IF;
2932 if(table_counter = 1) then
2933 x_reason_code.extend();
2934 X_reason_code(Table_counter):= 'IBE_SC_QUOTE_NEEDS_RESOURCEID';
2935 Table_counter := table_counter+1;
2936 end if;
2937 End If;
2938 If (l_status_code = 'INACTIVE') then
2939 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2940 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_INACTIVE');
2941 END IF;
2942 if(table_counter = 1) then
2943 x_reason_code.extend();
2944 X_reason_code(table_counter) := 'IBE_SC_QUOTE_INACTIVE';
2945 Table_counter := table_counter+1;
2946 end if;
2947 End If;
2948 If(l_publish_flag <> 'T') then
2949 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2950 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_NOT_PUBL');
2951 END IF;
2952 if(table_counter = 1) then
2953 x_reason_code.extend();
2954 X_reason_code(Table_counter):= 'IBE_SC_QUOTE_NOT_PUBL';
2955 Table_counter := table_counter+1;
2956 end if;
2957 End if;
2958 Elsif(p_load_type = L_LOAD_FORUPDATE) then
2959 If (l_resource_id is not null and (l_resource_id <> FND_API.G_MISS_NUM)) THEN
2960 If (FND_Profile.Value('IBE_UPDATE_DRAFT_QUOTES') = 'Y' and l_status_code <> 'DRAFT') THEN
2961 -- Update on Draft profile enabled, only allow updates on DRAFT.
2962 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2963 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_QUOTE_STS');
2964 END IF;
2965 if(table_counter = 1) then
2966 x_reason_code.extend();
2967 X_reason_code(Table_counter):= 'IBE_SC_INVALID_QUOTE_STS';
2968 Table_counter := table_counter+1;
2969 end if;
2970 End if;
2971 If(l_publish_flag <> 'T') then
2972 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2973 IBE_UTIL.debug('Including the Error code: IBE_SC_QUOTE_NOT_PUBL');
2974 END IF;
2975 if(table_counter = 1) then
2976 x_reason_code.extend();
2977 X_reason_code(Table_counter):= 'IBE_SC_QUOTE_NOT_PUBL';
2978 Table_counter := table_counter+1;
2979 end if;
2980 End if;
2981 Else -- for a cart, check for 'STORE DRAFT' (if the loadType is load_forupdate, status has to be 'STORE DRAFT')
2982 If (l_status_code <> 'STORE DRAFT') Then
2983 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2984 IBE_UTIL.debug('Including the Error code: IBE_SC_INVALID_CART_STS');
2985 END IF;
2986 if(table_counter = 1) then
2987 x_reason_code.extend();
2988 X_reason_code(Table_counter):= 'IBE_SC_INVALID_CART_STS';
2989 Table_counter := table_counter+1;
2990 end if;
2991 End if;
2992 End if;
2993 End if;
2994 --load error for one click orders.
2995 If(p_load_type = L_LOAD_EXPRESSORDER) Then
2996 If(l_quote_name = l_exp_quote_header_id) Then
2997 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2998 IBE_UTIL.debug('Including the Error code: IBE_SC_CART_DELETED');
2999 END IF;
3000 if(table_counter = 1) then
3001 x_reason_code.extend();
3002 X_reason_code(table_counter) := 'IBE_SC_CART_DELETED';
3003 Table_counter := table_counter+1;
3004 end if;
3005 End If;
3006 End if;
3007 End if;-- end if we have an input cartid or cartnum and version
3008 -- Standard check of p_commit.
3009 IF FND_API.To_Boolean(p_commit) THEN
3010 COMMIT WORK;
3011 END IF;
3012
3013 -- Standard call to get message count and if count is 1, get message info.
3014 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3015 p_count => x_msg_count ,
3016 p_data => x_msg_data);
3017 EXCEPTION
3018 WHEN FND_API.G_EXC_ERROR THEN
3019 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3020 IBE_UTIL.debug('Expected exception in IBE_QUOTE_MISC_PVT.get_load_errors');
3021 end if;
3022 ROLLBACK TO Get_load_errors_pvt;
3023 x_return_status := FND_API.G_RET_STS_ERROR;
3024 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3025 p_count => x_msg_count ,
3026 p_data => x_msg_data);
3027 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3028 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3029 IBE_UTIL.debug('Unexpected exception in IBE_QUOTE_MISC_PVT.get_load_errors');
3030 end if;
3031 ROLLBACK TO Get_load_errors_pvt;
3032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3033 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3034 p_count => x_msg_count ,
3035 p_data => x_msg_data);
3036 WHEN OTHERS THEN
3037 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3038 IBE_UTIL.debug('Unknown exception in IBE_QUOTE_MISC_PVT.get_load_errors');
3039 end if;
3040 ROLLBACK TO Get_load_errors_pvt;
3041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3042 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3043 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3044 L_API_NAME);
3045 END IF;
3046
3047 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3048 p_count => x_msg_count ,
3049 p_data => x_msg_data);
3050
3051 End get_load_errors;
3052
3053 /*PROCEDURE Get_ActiveCart_Id (
3054 p_party_id IN number
3055 ,p_cust_account_id IN number
3056 ,p_api_version_number IN NUMBER
3057 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3058 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3059 ,X_Return_Status OUT NOCOPY VARCHAR2
3060 ,X_Msg_Count OUT NOCOPY NUMBER
3061 ,X_Msg_Data OUT NOCOPY VARCHAR2
3062 ,x_cartHeaderId OUT NOCOPY number
3063 ,x_last_update_date OUT NOCOPY DATE
3064 )
3065 is
3066
3067 CURSOR C_get_quote_id(p_partyid number,p_cust_accountid number ) is
3068 select quote_header_id, last_update_date
3069 from aso_quote_headers_all
3070 where quote_header_id = (select max(quote_header_id)
3071 from aso_quote_headers_all
3072 where upper(quote_source_code) = 'ISTORE ACCOUNT'
3073 and party_id = p_partyid
3074 and cust_account_id = p_cust_accountid
3075 and quote_name = 'IBEACTIVECART'
3076 and quote_expiration_date > sysdate
3077 and resource_id IS NULL
3078 and ORDER_ID IS NULL);
3079
3080
3081
3082 l_CartHeaderId number := null;
3083 Rec_get_quote_id C_get_quote_id%rowtype;
3084 l_dummy number;
3085 l_api_name CONSTANT VARCHAR2(30) := 'Get_ActiveCart_Id';
3086 l_api_version CONSTANT NUMBER := 1.0;
3087 l_count number;
3088
3089 begin
3090 -- Standard Start of API savepoint
3091 SAVEPOINT GetActiveCartId_pvt;
3092 -- Standard call to check for call compatibility.
3093 IF NOT FND_API.Compatible_API_Call (
3094 l_api_version ,
3095 P_Api_Version_Number,
3096 l_api_name ,
3097 G_PKG_NAME )
3098 THEN
3099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3100 END IF;
3101 -- Initialize message list if p_init_msg_list is set to TRUE.
3102 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3103 FND_MSG_PUB.initialize;
3104 END IF;
3105 -- Initialize API return status to success
3106 x_return_status := FND_API.G_RET_STS_SUCCESS;
3107 -- API body
3108 FOR Rec_get_quote_id in c_get_quote_id(p_party_id, p_cust_account_id) loop
3109 l_cartHeaderId := Rec_get_quote_id.quote_header_id;
3110 x_last_update_date := Rec_get_quote_id.last_update_date;
3111 exit when c_get_quote_id%notfound;
3112 end loop;
3113
3114 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3115 ibe_util.debug('ibeactivecart ='||l_cartHeaderId);
3116 END IF;
3117
3118 IF (l_cartHeaderId = fnd_api.g_miss_num or l_cartHeaderid is null ) then
3119 x_cartHeaderId := null;
3120 else
3121 x_cartHeaderId := l_cartHeaderId;
3122 end if;
3123
3124 -- Standard call to get message count and if count is 1, get message info.
3125 FND_MSG_PUB.Count_And_Get
3126 (p_count => x_msg_count,
3127 p_data => x_msg_data
3128 );
3129
3130
3131 EXCEPTION
3132 WHEN TOO_MANY_ROWS then
3133 --ibe_util.debug('TOO_MANY_ROWS');
3134 ROLLBACK TO GETACTIVECARTID_pvt;
3135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3136 FND_MESSAGE.set_name('IBE','IBE_SC_GETACTIVEC_MANY');
3137 FND_MSG_PUB.add;
3138 FND_MSG_PUB.Count_And_Get
3139 ( p_count => x_msg_count,
3140 p_data => x_msg_data
3141 );
3142 WHEN NO_DATA_FOUND then
3143 --ibe_util.debug('NO_DATA_FOUND');
3144 null;
3145
3146 WHEN FND_API.G_EXC_ERROR THEN
3147 ROLLBACK TO GETACTIVECARTID_pvt;
3148 x_return_status := FND_API.G_RET_STS_ERROR;
3149 FND_MSG_PUB.Count_And_Get
3150 ( p_count => x_msg_count,
3151 p_data => x_msg_data
3152 );
3153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3154 ROLLBACK TO GETACTIVECARTID_pvt;
3155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3156 FND_MSG_PUB.Count_And_Get
3157 ( p_count => x_msg_count,
3158 p_data => x_msg_data
3159 );
3160 WHEN OTHERS THEN
3161 ROLLBACK TO GETACTIVECARTID_pvt;
3162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3163 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3164 FND_MSG_PUB.Add_Exc_Msg
3165 ( G_PKG_NAME,
3166 l_api_name);
3167 END IF;
3168 FND_MSG_PUB.Count_And_Get
3169 ( p_count => x_msg_count,
3170 p_data => x_msg_data
3171 );
3172
3173 END GET_ACTIVECART_ID;*/
3174
3175 PROCEDURE Update_Config_Item_Lines(
3176 x_return_status OUT NOCOPY VARCHAR2,
3177 x_msg_count OUT NOCOPY NUMBER ,
3178 x_msg_data OUT NOCOPY VARCHAR2,
3179 px_qte_line_dtl_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
3180 )
3181 IS
3182 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Config_Item_Lines';
3183 l_old_config_header_id NUMBER;
3184 l_new_config_header_id NUMBER;
3185 l_old_config_revision_num NUMBER;
3186 l_new_config_revision_num NUMBER;
3187
3188 -- ER#4025142
3189 --l_return_value NUMBER;
3190 l_api_version CONSTANT NUMBER := 1.0;
3191 l_ret_status VARCHAR2(1);
3192 l_msg_count INTEGER;
3193 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
3194 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
3195 BEGIN
3196 SAVEPOINT Update_Config_Item_Lines;
3197 -- Initialize API return status to success
3198 x_return_status := FND_API.G_RET_STS_SUCCESS;
3199
3200 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3201 IBE_Util.Debug('IBE_Quote_Misc_pvt.Update_Config_Item_Lines(+)');
3202 END IF;
3203
3204 -- API body
3205 FOR i IN 1..px_qte_line_dtl_tbl.COUNT LOOP
3206 IF px_qte_line_dtl_tbl(i).config_header_id IS NOT NULL
3207 AND px_qte_line_dtl_tbl(i).config_header_id <> FND_API.G_MISS_NUM THEN
3208 l_old_config_header_id := px_qte_line_dtl_tbl(i).config_header_id;
3209 l_old_config_revision_num := px_qte_line_dtl_tbl(i).config_revision_num;
3210
3211 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3212 IBE_Util.Debug('old config header id = '|| l_old_config_header_id);
3213 IBE_Util.Debug('old config revision number = '|| l_old_config_revision_num);
3214 IBE_Util.Debug('Call CZ_CONFIG_API_PUB.copy_configuration at'
3215 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
3216 END IF;
3217
3218 --ER#4025142
3219 CZ_CONFIG_API_PUB.copy_configuration(p_api_version => l_api_version
3220 ,p_config_hdr_id => l_old_config_header_id
3221 ,p_config_rev_nbr => l_old_config_revision_num
3222 ,p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
3223 ,x_config_hdr_id => l_new_config_header_id
3224 ,x_config_rev_nbr => l_new_config_revision_num
3225 ,x_orig_item_id_tbl => l_orig_item_id_tbl
3226 ,x_new_item_id_tbl => l_new_item_id_tbl
3227 ,x_return_status => l_ret_status
3228 ,x_msg_count => l_msg_count
3229 ,x_msg_data => x_msg_data);
3230 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
3231 RAISE FND_API.G_EXC_ERROR;
3232 END IF;
3233
3234 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3235 IBE_Util.Debug('Done CZ_CONFIG_API_PUB.Copy_Configuration at'
3236 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
3237 END IF;
3238
3239 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3240 IBE_Util.Debug('new config header id = '|| l_new_config_header_id);
3241 IBE_Util.Debug('new config revision number = '|| l_new_config_revision_num);
3242 END IF;
3243
3244 -- update all other dtl table
3245 FOR j in 1..px_qte_line_dtl_tbl.COUNT LOOP
3246 IF px_qte_line_dtl_tbl(j).config_header_id = l_old_config_header_id
3247 AND px_qte_line_dtl_tbl(j).config_revision_num = l_old_config_revision_num THEN
3248 px_qte_line_dtl_tbl(j).config_header_id := l_new_config_header_id;
3249 px_qte_line_dtl_tbl(j).config_revision_num := l_new_config_revision_num;
3250 END IF;
3251 END LOOP;
3252 END IF;
3253 END LOOP;
3254 -- End of API body.
3255
3256 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3257 IBE_Util.Debug('IBE_Quote_Misc_pvt.Update_Config_Item_Lines(-)');
3258 END IF;
3259
3260 -- Standard call to get message count and IF count is 1, get message info.
3261 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3262 p_count => x_msg_count ,
3263 p_data => x_msg_data );
3264 EXCEPTION
3265 WHEN FND_API.G_EXC_ERROR THEN
3266 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3267 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Update_Config_Item_Lines');
3268 END IF;
3269 ROLLBACK to Update_config_item_lines;
3270 x_return_status := FND_API.G_RET_STS_ERROR;
3271 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3272 p_count => x_msg_count,
3273 p_data => x_msg_data);
3274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3275 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3276 IBE_Util.Debug('Unexpected error IBE_Quote_Misc_pvt.Update_Config_Item_Lines');
3277 END IF;
3278 ROLLBACK to Update_config_item_lines;
3279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3280 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3281 p_count => x_msg_count,
3282 p_data => x_msg_data);
3283 WHEN OTHERS THEN
3284 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3285 IBE_Util.Debug('Unknown error IBE_Quote_Misc_pvt.Update_Config_Item_Lines');
3286 END IF;
3287 ROLLBACK to Update_config_item_lines;
3288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3289
3290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3291 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3292 l_api_name);
3293 END IF;
3294
3295 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3296 p_count => x_msg_count,
3297 p_data => x_msg_data);
3298 END Update_Config_Item_Lines;
3299
3300 procedure Validate_Items(
3301 x_item_exists OUT NOCOPY jtf_number_Table,
3302 p_cust_account_id IN NUMBER,
3303 p_minisite_id IN NUMBER,
3304 p_merchant_item_ids IN JTF_NUMBER_TABLE,
3305 p_org_id IN NUMBER
3306 ) IS
3307 l_item_exists NUMBER;
3308 l_count NUMBER;
3309 BEGIN
3310 l_count := p_merchant_item_ids.COUNT;
3311 x_item_exists := JTF_NUMBER_TABLE();
3312 x_item_exists.extend(l_count);
3313
3314
3315 FOR i IN 1..l_count LOOP
3316 IF p_merchant_item_ids(i) IS NOT NULL THEN
3317 --inv_debug.message('ssia', 'p_cust_item_number is ' || p_cust_item_number_tbl(i));
3318 select count(s.inventory_item_id)
3319 into l_item_exists
3320 from ibe_dsp_section_items s, ibe_dsp_msite_sct_items b
3321 where s.section_item_id = b.section_item_id
3322 and b.mini_site_id = p_minisite_id
3323 and s.inventory_item_id = p_merchant_item_ids(i)
3324 and (s.end_date_active > sysdate or s.end_date_active is null )
3325 and s.start_date_active < sysdate;
3326
3327 x_item_exists(i) := l_item_exists;
3328 else
3329 x_item_exists(i) := 0;
3330 end if;
3331 END LOOP;
3332 END Validate_Items;
3333
3334 PROCEDURE Get_Included_Warranties(
3335 p_api_version_number IN NUMBER := 1,
3336 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3337 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3338 x_return_status OUT NOCOPY VARCHAR2,
3339 x_msg_count OUT NOCOPY NUMBER,
3340 x_msg_data OUT NOCOPY VARCHAR2,
3341 p_organization_id IN NUMBER := NULL,
3342 p_product_item_id IN NUMBER,
3343 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
3344 ) IS
3345 l_warranty_tbl ASO_SERVICE_CONTRACTS_INT.War_tbl_type;
3346 l_count NUMBER;
3347 BEGIN
3348
3349 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3350 IBE_UTIL.Debug('Start IBE_Quote_Misc_pvt.Get_Available_Services');
3351 IBE_UTIL.Debug(' Parms: [' || p_organization_id || ', ' ||
3352 p_product_item_id || ']');
3353 END IF;
3354
3355 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3356 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Get_Warranty Starts');
3357 END IF;
3358 ASO_SERVICE_CONTRACTS_INT.Get_Warranty(
3359 p_api_version_number => p_api_version_number,
3360 p_init_msg_list => p_init_msg_list,
3361 x_msg_count => x_msg_count,
3362 x_msg_data => x_msg_data,
3363 p_org_id => FND_PROFILE.Value('ORG_ID'),
3364 p_organization_id => p_organization_id,
3365 p_product_item_id => p_product_item_id,
3366 x_return_status => x_return_status,
3367 x_warranty_tbl => l_warranty_tbl
3368 );
3369 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3370 l_count := l_warranty_tbl.COUNT;
3371 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3372 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Get_Warranty Finishes ' || x_return_status ||
3373 ' x_warranty_tbl.COUNT=' || l_count);
3374 END IF;
3375
3376 x_service_item_ids := JTF_NUMBER_TABLE();
3377
3378 IF l_count > 0 THEN
3379 x_service_item_ids.extend(l_count);
3380 FOR i in 1..l_count LOOP
3381 x_service_item_ids(i) := l_warranty_tbl(i).service_item_id;
3382 END LOOP;
3383 END IF;
3384 END IF;
3385
3386 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3387 IBE_UTIL.Debug('End IBE_Quote_Misc_pvt.Get_Available_Services');
3388 END IF;
3389
3390 END Get_Included_Warranties;
3391
3392 PROCEDURE Get_Available_Services(
3393 p_api_version_number IN NUMBER := 1,
3394 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3395 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3396 x_return_status OUT NOCOPY VARCHAR2,
3397 x_msg_count OUT NOCOPY NUMBER,
3398 x_msg_data OUT NOCOPY VARCHAR2,
3399 p_product_item_id IN NUMBER,
3400 p_customer_id IN NUMBER,
3401 p_product_revision IN VARCHAR2,
3402 p_request_date IN DATE,
3403 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
3404 ) IS
3405 l_avail_service_rec ASO_SERVICE_CONTRACTS_INT.Avail_Service_Rec_Type;
3406 l_orderable_Service_tbl ASO_SERVICE_CONTRACTS_INT.order_service_tbl_type;
3407 l_count NUMBER;
3408 BEGIN
3409
3410 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3411 IBE_UTIL.Debug('Start IBE_Quote_Misc_pvt.Get_Available_Services');
3412 IBE_UTIL.Debug(' Parms: [p_product_item_id=' || p_product_item_id || ', ' ||
3413 p_customer_id || ', ' || p_product_revision || ', ' ||
3414 p_request_date || ']');
3415 END IF;
3416
3417 -- Setting Rec values to be passed to ASO_SERVICE_CONTRACTS_INT
3418 l_avail_service_rec.product_item_id := p_product_item_id;
3419 l_avail_service_rec.customer_id := p_customer_id;
3420 l_avail_service_rec.product_revision := p_product_revision;
3421 l_avail_service_rec.request_date := p_request_date;
3422
3423 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3424 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Available_Services Starts');
3425 END IF;
3426 ASO_SERVICE_CONTRACTS_INT.Available_Services(
3427 p_api_version_number => p_api_version_number,
3428 p_init_msg_list => p_init_msg_list,
3429 x_msg_count => x_msg_count,
3430 x_msg_data => x_msg_data,
3431 x_return_status => x_return_status,
3432 p_avail_service_rec => l_avail_service_rec,
3433 x_orderable_service_tbl => l_orderable_service_tbl
3434 );
3435
3436 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3437 RAISE FND_API.G_EXC_ERROR;
3438 END IF;
3439 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3441 END IF;
3442
3443 l_count := l_orderable_service_tbl.COUNT;
3444 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3445 IBE_UTIL.Debug(' ASO_SERVICE_CONTRACTS_INT.Available_Services Finishes ' || x_return_status || ' ' ||
3446 'x_orderable_service_tbl.COUNT=' || l_count);
3447 END IF;
3448
3449 x_service_item_ids := JTF_NUMBER_TABLE();
3450
3451 IF l_count > 0 THEN
3452 x_service_item_ids.extend(l_count);
3453 FOR i IN 1..l_count LOOP
3454 x_service_item_ids(i) := l_orderable_service_tbl(i).service_item_id;
3455 END LOOP;
3456 END IF;
3457
3458 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3459 IBE_UTIL.Debug('End IBE_Quote_Misc_pvt.Get_Available_Services');
3460 END IF;
3461
3462 END Get_Available_Services;
3463
3464 Procedure Duplicate_Line(
3465 p_api_version_number IN NUMBER
3466 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3467 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3468 ,X_Return_Status OUT NOCOPY VARCHAR2
3469 ,X_Msg_Count OUT NOCOPY NUMBER
3470 ,X_Msg_Data OUT NOCOPY VARCHAR2
3471 ,p_quote_header_id IN NUMBER
3472 ,p_qte_line_id IN NUMBER
3473 ,x_qte_line_tbl IN OUT NOCOPY ASO_Quote_Pub.qte_line_tbl_type
3474 ,x_qte_line_dtl_tbl IN OUT NOCOPY ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type
3475 ,x_line_attr_ext_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type
3476 ,x_line_rltship_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Rltship_tbl_Type
3477 ,x_ln_price_attributes_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Attributes_Tbl_Type
3478 ,x_ln_price_adj_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Adj_Tbl_Type
3479 )
3480 IS
3481
3482 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Line';
3483 l_api_version CONSTANT NUMBER := 1.0;
3484
3485 l_qte_line_dtl_tbl ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type;
3486 l_line_rltship_tbl ASO_Quote_Pub.Line_Rltship_tbl_Type;
3487 l_line_attr_ext_tbl ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type;
3488 l_ln_price_attributes_tbl ASO_Quote_Pub.Price_Attributes_Tbl_Type;
3489 l_ln_price_adj_tbl ASO_Quote_Pub.Price_Adj_Tbl_Type;
3490
3491 l_initial_count NUMBER;
3492 l_initial_dtl_count NUMBER;
3493
3494 l_old_config_hdr_id NUMBER;
3495 l_old_config_rev_nbr NUMBER;
3496
3497 l_new_config_hdr_id NUMBER;
3498 l_new_config_rev_nbr NUMBER;
3499
3500 -- ER#4025142
3501 --l_return_value NUMBER;
3502 l_ret_status VARCHAR2(1);
3503 l_msg_count INTEGER;
3504 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
3505 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
3506
3507 CURSOR c_related_lines (p_qte_line_id NUMBER) IS
3508 SELECT related_quote_line_id
3509 FROM aso_line_relationships
3510 START WITH quote_line_id = p_qte_line_id
3511 CONNECT BY quote_line_id = PRIOR related_quote_line_id;
3512
3513 BEGIN
3514 -- Standard Start of API savepoint
3515 SAVEPOINT DUPLICATE_LINE_PVT;
3516 -- Standard call to check for call compatibility.
3517 IF NOT FND_API.Compatible_API_Call (l_api_version,
3518 P_Api_Version_Number,
3519 l_api_name,
3520 G_PKG_NAME )
3521 THEN
3522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3523 END IF;
3524 -- Initialize message list IF p_init_msg_list is set to TRUE.
3525 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3526 FND_MSG_PUB.initialize;
3527 END IF;
3528
3529 -- Initialize API return status to success
3530 x_return_status := FND_API.G_RET_STS_SUCCESS;
3531
3532 l_initial_dtl_count := x_qte_line_dtl_tbl.COUNT + 1;
3533
3534 l_initial_count := x_qte_line_tbl.COUNT + 1;
3535 x_qte_line_tbl(l_initial_count) := IBE_Quote_Misc_pvt.getLineRec(p_qte_line_id);
3536
3537 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3538 IBE_UTIL.Debug(' Adding Lines From c_related_lines');
3539 END IF;
3540 FOR l_related_lines_rec IN c_related_lines(p_qte_line_id) LOOP
3541 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3542 IBE_UTIL.Debug(' Adding related_quote_line_id=' || l_related_lines_rec.related_quote_line_id);
3543 END IF;
3544 x_qte_line_tbl(x_qte_line_tbl.COUNT + 1) := IBE_Quote_Misc_pvt.getLineRec(l_related_lines_rec.related_quote_line_id);
3545 END LOOP;
3546
3547
3548 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3549 ibe_util.debug('line number is='|| x_qte_line_tbl.count);
3550 END IF;
3551 FOR i IN l_initial_count..x_qte_line_tbl.COUNT LOOP
3552
3553 l_qte_line_dtl_tbl := IBE_Quote_Misc_pvt.getlineDetailTbl
3554 (x_qte_line_tbl(i).quote_line_id);
3555 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3556 IBE_UTIL.Debug(' Processing LineDetailTbl Count=' || l_qte_line_dtl_tbl.COUNT);
3557 END IF;
3558 FOR j IN 1..l_qte_line_dtl_tbl.COUNT LOOP
3559 IF l_qte_line_dtl_tbl(j).service_ref_line_id <> fnd_api.g_miss_num THEN
3560 -- All service_ref_line_id's should point to first entry in x_qte_line_tbl
3561 l_qte_line_dtl_tbl(j).service_ref_qte_line_index := l_initial_count;
3562 l_qte_line_dtl_tbl(j).service_ref_line_id := fnd_api.g_miss_num;
3563 END IF;
3564 l_qte_line_dtl_tbl(j).quote_line_detail_id := fnd_api.g_miss_num;
3565 l_qte_line_dtl_tbl(j).operation_code := 'CREATE';
3566 l_qte_line_dtl_tbl(j).qte_line_index := i;
3567 l_qte_line_dtl_tbl(j).quote_line_id := fnd_api.g_miss_num;
3568 x_qte_line_dtl_tbl(x_qte_line_dtl_tbl.count+1)
3569 := l_qte_line_dtl_tbl(j);
3570 END LOOP;
3571
3572
3573 l_line_rltship_tbl := IBE_Quote_Misc_pvt.getlineRelationshipTbl(x_qte_line_tbl(i).quote_line_id);
3574 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3575 IBE_UTIL.Debug(' Processing LineRelationshipTbl Count=' || l_line_rltship_tbl.COUNT);
3576 END IF;
3577
3578 FOR j IN 1..l_line_rltship_tbl.COUNT LOOP
3579 IF NVL(l_line_rltship_tbl(j).relationship_type_code, '*') <> 'SERVICE' THEN
3580 l_line_rltship_tbl(j).line_relationship_id := fnd_api.g_miss_num;
3581 l_line_rltship_tbl(j).operation_code := 'CREATE';
3582
3583 l_line_rltship_tbl(j).qte_line_index := i;
3584 l_line_rltship_tbl(j).related_qte_line_index
3585 := IBE_Quote_Misc_pvt.getLineIndexFromLineId
3586 ( l_line_rltship_tbl(j).related_quote_line_id
3587 ,x_qte_line_tbl
3588 );
3589 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3590 IBE_UTIL.Debug(' related_quote_line_id=' || l_line_rltship_tbl(j).related_quote_line_id ||
3591 ' related_quote_line_index=' || l_line_rltship_tbl(j).related_qte_line_index);
3592 END IF;
3593 l_line_rltship_tbl(j).quote_line_id := fnd_api.g_miss_num;
3594 l_line_rltship_tbl(j).related_quote_line_id := fnd_api.g_miss_num;
3595 x_line_rltship_tbl(x_line_rltship_tbl.count+1)
3596 := l_line_rltship_tbl(j);
3597 END IF;
3598
3599 END LOOP;
3600
3601
3602 l_line_attr_ext_tbl := IBE_Quote_Misc_pvt.getLineAttrExtTbl
3603 (x_qte_line_tbl(i).quote_line_id);
3604 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3605 IBE_UTIL.Debug(' Processing LineAttrExtTbl Count=' || l_line_attr_ext_tbl.COUNT);
3606 END IF;
3607
3608 FOR j IN 1..l_line_attr_ext_tbl.COUNT LOOP
3609 l_line_attr_ext_tbl(j).line_attribute_id := fnd_api.g_miss_num;
3610 l_line_attr_ext_tbl(j).operation_code := 'CREATE';
3611 l_line_attr_ext_tbl(j).qte_line_index := i;
3612 l_line_attr_ext_tbl(j).quote_line_id := fnd_api.g_miss_num;
3613
3614 x_line_attr_ext_tbl(x_line_attr_ext_tbl.count+1)
3615 := l_line_attr_ext_tbl(j);
3616 END LOOP;
3617
3618 l_ln_price_attributes_tbl := IBE_Quote_Misc_pvt.getlinePrcAttrTbl
3619 (x_qte_line_tbl(i).quote_line_id);
3620 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3621 IBE_UTIL.Debug(' Processing linePrcAttrTbl Count=' || l_ln_price_attributes_tbl.COUNT);
3622 END IF;
3623
3624 FOR j IN 1..l_ln_price_attributes_tbl.COUNT LOOP
3625 l_ln_price_attributes_tbl(j).price_attribute_id := fnd_api.g_miss_num;
3626 l_ln_price_attributes_tbl(j).operation_code := 'CREATE';
3627 l_ln_price_attributes_tbl(j).qte_line_index := i;
3628 l_ln_price_attributes_tbl(j).quote_line_id := fnd_api.g_miss_num;
3629 l_ln_price_attributes_tbl(j).quote_header_id := p_quote_header_id;
3630 x_ln_price_attributes_tbl(x_ln_price_attributes_tbl.count+1)
3631 := l_ln_price_attributes_tbl(j);
3632 END LOOP;
3633
3634 l_ln_price_adj_tbl := getHdrPrcAdjNonPRGTbl
3635 (p_qte_header_id => p_quote_header_id,
3636 p_qte_line_id => x_qte_line_tbl(i).quote_line_id);
3637 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3638 IBE_UTIL.Debug(' Processing LinePriceAdjTbl Count=' || l_ln_price_adj_tbl.COUNT);
3639 END IF;
3640
3641 FOR j IN 1..l_ln_price_adj_tbl.COUNT LOOP
3642 l_ln_price_adj_tbl(j).price_adjustment_id := fnd_api.g_miss_num;
3643 l_ln_price_adj_tbl(j).operation_code := 'CREATE';
3644 l_ln_price_adj_tbl(j).qte_line_index := i;
3645 l_ln_price_adj_tbl(j).quote_line_id := fnd_api.g_miss_num;
3646 l_ln_price_adj_tbl(j).quote_header_id := p_quote_header_id;
3647 x_ln_price_adj_tbl(x_ln_price_adj_tbl.count+1)
3648 := l_ln_price_adj_tbl(j);
3649 END LOOP;
3650
3651 END LOOP; -- end of get line information
3652
3653 FOR I IN l_initial_count..x_qte_line_tbl.COUNT LOOP
3654 x_qte_line_tbl(I).operation_code := 'CREATE';
3655 x_qte_line_tbl(I).quote_line_id := fnd_api.g_miss_num;
3656 x_qte_line_tbl(I).quote_header_id := p_quote_header_id;
3657 END LOOP;
3658
3659 -- takes care of configuraton item
3660 FOR i IN l_initial_dtl_count..x_qte_line_dtl_tbl.COUNT LOOP
3661 IF x_qte_line_tbl(x_qte_line_dtl_tbl(i).qte_line_index).item_type_code
3662 = 'MDL' THEN
3663 l_old_config_hdr_id := x_qte_line_dtl_tbl(i).config_header_id;
3664 l_old_config_rev_nbr := x_qte_line_dtl_tbl(i).config_revision_num;
3665
3666 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3667 IBE_UTIL.debug('old config id = '|| l_old_config_hdr_id);
3668 IBE_UTIL.debug('old config rev number = '|| l_old_config_rev_nbr);
3669 END IF;
3670
3671
3672 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3673 IBE_UTIL.debug('call CZ_CONFIG_API_PUB.copy_configuration at'
3674 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
3675 END IF;
3676
3677 --ER#4025142
3678 CZ_CONFIG_API_PUB.copy_configuration(p_api_version => l_api_version
3679 ,p_config_hdr_id => l_old_config_hdr_id
3680 ,p_config_rev_nbr => l_old_config_rev_nbr
3681 ,p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
3682 ,x_config_hdr_id => l_new_config_hdr_id
3683 ,x_config_rev_nbr => l_new_config_rev_nbr
3684 ,x_orig_item_id_tbl => l_orig_item_id_tbl
3685 ,x_new_item_id_tbl => l_new_item_id_tbl
3686 ,x_return_status => l_ret_status
3687 ,x_msg_count => l_msg_count
3688 ,x_msg_data => x_msg_data);
3689 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
3690 RAISE FND_API.G_EXC_ERROR;
3691 END IF;
3692
3693
3694 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3695 IBE_UTIL.DEBUG('done CZ_CONFIG_API_PUB.Copy_Configuration at'
3696 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
3697 END IF;
3698
3699 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3700 IBE_UTIL.debug('new config id = '|| l_new_config_hdr_id);
3701 IBE_UTIL.debug('new config rev number = '|| l_new_config_rev_nbr);
3702 END IF;
3703
3704 -- update all other dtl table
3705 FOR j in l_initial_dtl_count..x_qte_line_dtl_tbl.COUNT LOOP
3706 IF ( x_qte_line_dtl_tbl(j).config_header_id = l_old_config_hdr_id
3707 and x_qte_line_dtl_tbl(j).config_revision_num = l_old_config_rev_nbr )
3708 THEN
3709 x_qte_line_dtl_tbl(j).config_header_id := l_new_config_hdr_id;
3710 x_qte_line_dtl_tbl(j).config_revision_num := l_new_config_rev_nbr;
3711 END IF;
3712 END LOOP;
3713 END IF;
3714 END LOOP;
3715
3716 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3717 ibe_util.debug('before out line number is='|| x_qte_line_tbl.count);
3718 END IF;
3719 -- Standard check of p_commit.
3720 IF FND_API.To_Boolean( p_commit ) THEN
3721 COMMIT WORK;
3722 END IF;
3723
3724 -- Standard call to get message count and IF count is 1, get message info.
3725 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3726 p_count => x_msg_count,
3727 p_data => x_msg_data);
3728 EXCEPTION
3729 WHEN FND_API.G_EXC_ERROR THEN
3730 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3731 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Duplicate_line');
3732 END IF;
3733
3734 ROLLBACK TO DUPLICATE_LINE_PVT;
3735 x_return_status := FND_API.G_RET_STS_ERROR;
3736 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3737 p_count => x_msg_count,
3738 p_data => x_msg_data);
3739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3740 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3741 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Duplicate_line');
3742 END IF;
3743
3744 ROLLBACK TO DUPLICATE_LINE_PVT;
3745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3746 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3747 p_count => x_msg_count,
3748 p_data => x_msg_data);
3749 WHEN OTHERS THEN
3750 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3751 IBE_Util.Debug('unknown error IBE_Quote_Misc_pvt.Duplicate_line');
3752 END IF;
3753
3754 ROLLBACK TO DUPLICATE_LINE_PVT;
3755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3756
3757 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3758 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3759 l_api_name);
3760 END IF;
3761
3762 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3763 p_count => x_msg_count,
3764 p_data => x_msg_data);
3765 END Duplicate_Line;
3766
3767 FUNCTION getHdrPrcAdjNonPRGTbl (
3768 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
3769 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
3770 ) RETURN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
3771 IS
3772 CURSOR c_price_adj IS
3773 SELECT
3774 PRICE_ADJUSTMENT_ID,
3775 CREATION_DATE,
3776 CREATED_BY,
3777 LAST_UPDATE_DATE,
3778 LAST_UPDATED_BY,
3779 LAST_UPDATE_LOGIN,
3780 PROGRAM_APPLICATION_ID,
3781 PROGRAM_ID,
3782 PROGRAM_UPDATE_DATE,
3783 REQUEST_ID,
3784 QUOTE_HEADER_ID,
3785 QUOTE_LINE_ID,
3786 MODIFIER_HEADER_ID,
3787 MODIFIER_LINE_ID,
3788 MODIFIER_LINE_TYPE_CODE,
3789 MODIFIER_MECHANISM_TYPE_CODE,
3790 MODIFIED_FROM,
3791 MODIFIED_TO,
3792 OPERAND,
3793 ARITHMETIC_OPERATOR,
3794 AUTOMATIC_FLAG,
3795 UPDATE_ALLOWABLE_FLAG,
3796 UPDATED_FLAG,
3797 APPLIED_FLAG,
3798 ON_INVOICE_FLAG,
3799 PRICING_PHASE_ID,
3800 ATTRIBUTE_CATEGORY,
3801 ATTRIBUTE1,
3802 ATTRIBUTE2,
3803 ATTRIBUTE3,
3804 ATTRIBUTE4,
3805 ATTRIBUTE5,
3806 ATTRIBUTE6,
3807 ATTRIBUTE7,
3808 ATTRIBUTE8,
3809 ATTRIBUTE9,
3810 ATTRIBUTE10,
3811 ATTRIBUTE11,
3812 ATTRIBUTE12,
3813 ATTRIBUTE13,
3814 ATTRIBUTE14,
3815 ATTRIBUTE15,
3816 TAX_CODE,
3817 TAX_EXEMPT_FLAG,
3818 TAX_EXEMPT_NUMBER,
3819 TAX_EXEMPT_REASON_CODE,
3820 PARENT_ADJUSTMENT_ID,
3821 INVOICED_FLAG,
3822 ESTIMATED_FLAG,
3823 INC_IN_SALES_PERFORMANCE,
3824 SPLIT_ACTION_CODE,
3825 ADJUSTED_AMOUNT,
3826 CHARGE_TYPE_CODE,
3827 CHARGE_SUBTYPE_CODE,
3828 RANGE_BREAK_QUANTITY,
3829 ACCRUAL_CONVERSION_RATE,
3830 PRICING_GROUP_SEQUENCE,
3831 ACCRUAL_FLAG,
3832 LIST_LINE_NO,
3833 SOURCE_SYSTEM_CODE,
3834 BENEFIT_QTY,
3835 BENEFIT_UOM_CODE,
3836 PRINT_ON_INVOICE_FLAG,
3837 EXPIRATION_DATE,
3838 REBATE_TRANSACTION_TYPE_CODE,
3839 REBATE_TRANSACTION_REFERENCE,
3840 REBATE_PAYMENT_SYSTEM_CODE,
3841 REDEEMED_DATE,
3842 REDEEMED_FLAG,
3843 MODIFIER_LEVEL_CODE,
3844 PRICE_BREAK_TYPE_CODE,
3845 SUBSTITUTION_ATTRIBUTE,
3846 PRORATION_TYPE_CODE,
3847 INCLUDE_ON_RETURNS_FLAG,
3848 CREDIT_OR_CHARGE_FLAG,
3849 ORIG_SYS_DISCOUNT_REF,
3850 CHANGE_REASON_CODE,
3851 CHANGE_REASON_TEXT,
3852 COST_ID,
3853 LIST_LINE_TYPE_CODE,
3854 UPDATE_ALLOWED,
3855 CHANGE_SEQUENCE,
3856 LIST_HEADER_ID,
3857 LIST_LINE_ID,
3858 QUOTE_SHIPMENT_ID
3859 FROM ASO_PRICE_ADJUSTMENTS
3860 WHERE quote_header_id = p_qte_header_id AND
3861 (quote_line_id = p_qte_line_id OR
3862 (quote_line_id IS NULL AND p_qte_line_id IS NULL))
3863 AND modifier_line_type_code <> 'PRG';
3864
3865 l_price_adj_rec ASO_QUOTE_PUB.Price_Adj_Rec_Type;
3866 l_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
3867
3868 BEGIN
3869 FOR price_adj_rec IN c_price_adj LOOP
3870 l_price_adj_rec.PRICE_ADJUSTMENT_ID := price_adj_rec.PRICE_ADJUSTMENT_ID;
3871 l_price_adj_rec.CREATION_DATE := price_adj_rec.CREATION_DATE;
3872 l_price_adj_rec.CREATED_BY := price_adj_rec.CREATED_BY;
3873 l_price_adj_rec.LAST_UPDATE_DATE := price_adj_rec.LAST_UPDATE_DATE;
3874 l_price_adj_rec.LAST_UPDATED_BY := price_adj_rec.LAST_UPDATED_BY;
3875 l_price_adj_rec.LAST_UPDATE_LOGIN := price_adj_rec.LAST_UPDATE_LOGIN;
3876 l_price_adj_rec.REQUEST_ID := price_adj_rec.REQUEST_ID;
3877 l_price_adj_rec.PROGRAM_APPLICATION_ID := price_adj_rec.PROGRAM_APPLICATION_ID;
3878 l_price_adj_rec.PROGRAM_ID := price_adj_rec.PROGRAM_ID;
3879 l_price_adj_rec.PROGRAM_UPDATE_DATE := price_adj_rec.PROGRAM_UPDATE_DATE;
3880 l_price_adj_rec.QUOTE_HEADER_ID := price_adj_rec.QUOTE_HEADER_ID;
3881 l_price_adj_rec.QUOTE_LINE_ID := price_adj_rec.QUOTE_LINE_ID;
3882 l_price_adj_rec.MODIFIER_HEADER_ID := price_adj_rec.MODIFIER_HEADER_ID;
3883 l_price_adj_rec.MODIFIER_LINE_ID := price_adj_rec.MODIFIER_LINE_ID;
3884 l_price_adj_rec.MODIFIER_LINE_TYPE_CODE := price_adj_rec.MODIFIER_LINE_TYPE_CODE;
3885 l_price_adj_rec.MODIFIER_MECHANISM_TYPE_CODE
3886 := price_adj_rec.MODIFIER_MECHANISM_TYPE_CODE;
3887 l_price_adj_rec.MODIFIED_FROM := price_adj_rec.MODIFIED_FROM;
3888 l_price_adj_rec.MODIFIED_TO := price_adj_rec.MODIFIED_TO;
3889 l_price_adj_rec.OPERAND := price_adj_rec.OPERAND;
3890 l_price_adj_rec.ARITHMETIC_OPERATOR := price_adj_rec.ARITHMETIC_OPERATOR;
3891 l_price_adj_rec.AUTOMATIC_FLAG := price_adj_rec.AUTOMATIC_FLAG;
3892 l_price_adj_rec.UPDATE_ALLOWABLE_FLAG := price_adj_rec.UPDATE_ALLOWABLE_FLAG;
3893 l_price_adj_rec.UPDATED_FLAG := price_adj_rec.UPDATED_FLAG;
3894 l_price_adj_rec.APPLIED_FLAG := price_adj_rec.APPLIED_FLAG;
3895 l_price_adj_rec.ON_INVOICE_FLAG := price_adj_rec.ON_INVOICE_FLAG;
3896 l_price_adj_rec.PRICING_PHASE_ID := price_adj_rec.PRICING_PHASE_ID;
3897 l_price_adj_rec.QUOTE_SHIPMENT_ID := price_adj_rec.QUOTE_SHIPMENT_ID;
3898 l_price_adj_rec.ATTRIBUTE_CATEGORY := price_adj_rec.ATTRIBUTE_CATEGORY;
3899 l_price_adj_rec.ATTRIBUTE1 := price_adj_rec.ATTRIBUTE1;
3900 l_price_adj_rec.ATTRIBUTE2 := price_adj_rec.ATTRIBUTE2;
3901 l_price_adj_rec.ATTRIBUTE3 := price_adj_rec.ATTRIBUTE3;
3902 l_price_adj_rec.ATTRIBUTE4 := price_adj_rec.ATTRIBUTE4;
3903 l_price_adj_rec.ATTRIBUTE5 := price_adj_rec.ATTRIBUTE5;
3904 l_price_adj_rec.ATTRIBUTE6 := price_adj_rec.ATTRIBUTE6;
3905 l_price_adj_rec.ATTRIBUTE7 := price_adj_rec.ATTRIBUTE7;
3906 l_price_adj_rec.ATTRIBUTE8 := price_adj_rec.ATTRIBUTE8;
3907 l_price_adj_rec.ATTRIBUTE9 := price_adj_rec.ATTRIBUTE9;
3908 l_price_adj_rec.ATTRIBUTE10 := price_adj_rec.ATTRIBUTE10;
3909 l_price_adj_rec.ATTRIBUTE11 := price_adj_rec.ATTRIBUTE11;
3910 l_price_adj_rec.ATTRIBUTE12 := price_adj_rec.ATTRIBUTE12;
3911 l_price_adj_rec.ATTRIBUTE13 := price_adj_rec.ATTRIBUTE13;
3912 l_price_adj_rec.ATTRIBUTE14 := price_adj_rec.ATTRIBUTE14;
3913 l_price_adj_rec.ATTRIBUTE15 := price_adj_rec.ATTRIBUTE15;
3914 l_price_adj_rec.TAX_CODE := price_adj_rec.TAX_CODE;
3915 l_price_adj_rec.TAX_EXEMPT_FLAG := price_adj_rec.TAX_EXEMPT_FLAG;
3916 l_price_adj_rec.TAX_EXEMPT_NUMBER := price_adj_rec.TAX_EXEMPT_NUMBER;
3917 l_price_adj_rec.TAX_EXEMPT_REASON_CODE := price_adj_rec.TAX_EXEMPT_REASON_CODE;
3918 l_price_adj_rec.PARENT_ADJUSTMENT_ID := price_adj_rec.PARENT_ADJUSTMENT_ID;
3919 l_price_adj_rec.INVOICED_FLAG := price_adj_rec.INVOICED_FLAG;
3920 l_price_adj_rec.ESTIMATED_FLAG := price_adj_rec.ESTIMATED_FLAG;
3921 l_price_adj_rec.INC_IN_SALES_PERFORMANCE := price_adj_rec.INC_IN_SALES_PERFORMANCE;
3922 l_price_adj_rec.SPLIT_ACTION_CODE := price_adj_rec.SPLIT_ACTION_CODE;
3923 l_price_adj_rec.ADJUSTED_AMOUNT := price_adj_rec.ADJUSTED_AMOUNT;
3924 l_price_adj_rec.CHARGE_TYPE_CODE := price_adj_rec.CHARGE_TYPE_CODE;
3925 l_price_adj_rec.CHARGE_SUBTYPE_CODE := price_adj_rec.CHARGE_SUBTYPE_CODE;
3926 l_price_adj_rec.RANGE_BREAK_QUANTITY := price_adj_rec.RANGE_BREAK_QUANTITY;
3927 l_price_adj_rec.ACCRUAL_CONVERSION_RATE := price_adj_rec.ACCRUAL_CONVERSION_RATE;
3928 l_price_adj_rec.PRICING_GROUP_SEQUENCE := price_adj_rec.PRICING_GROUP_SEQUENCE;
3929 l_price_adj_rec.ACCRUAL_FLAG := price_adj_rec.ACCRUAL_FLAG;
3930 l_price_adj_rec.LIST_LINE_NO := price_adj_rec.LIST_LINE_NO;
3931 l_price_adj_rec.SOURCE_SYSTEM_CODE := price_adj_rec.SOURCE_SYSTEM_CODE;
3932 l_price_adj_rec.BENEFIT_QTY := price_adj_rec.BENEFIT_QTY;
3933 l_price_adj_rec.BENEFIT_UOM_CODE := price_adj_rec.BENEFIT_UOM_CODE;
3934 l_price_adj_rec.PRINT_ON_INVOICE_FLAG := price_adj_rec.PRINT_ON_INVOICE_FLAG;
3935 l_price_adj_rec.EXPIRATION_DATE := price_adj_rec.EXPIRATION_DATE;
3936 l_price_adj_rec.REBATE_TRANSACTION_TYPE_CODE := price_adj_rec.REBATE_TRANSACTION_TYPE_CODE;
3937 l_price_adj_rec.REBATE_TRANSACTION_REFERENCE := price_adj_rec.REBATE_TRANSACTION_REFERENCE;
3938 l_price_adj_rec.REBATE_PAYMENT_SYSTEM_CODE := price_adj_rec.REBATE_PAYMENT_SYSTEM_CODE;
3939 l_price_adj_rec.REDEEMED_DATE := price_adj_rec.REDEEMED_DATE;
3940 l_price_adj_rec.REDEEMED_FLAG := price_adj_rec.REDEEMED_FLAG;
3941 l_price_adj_rec.MODIFIER_LEVEL_CODE := price_adj_rec.MODIFIER_LEVEL_CODE;
3942 l_price_adj_rec.PRICE_BREAK_TYPE_CODE := price_adj_rec.PRICE_BREAK_TYPE_CODE;
3943 l_price_adj_rec.SUBSTITUTION_ATTRIBUTE := price_adj_rec.SUBSTITUTION_ATTRIBUTE;
3944 l_price_adj_rec.PRORATION_TYPE_CODE := price_adj_rec.PRORATION_TYPE_CODE;
3945 l_price_adj_rec.INCLUDE_ON_RETURNS_FLAG := price_adj_rec.INCLUDE_ON_RETURNS_FLAG;
3946 l_price_adj_rec.CREDIT_OR_CHARGE_FLAG := price_adj_rec.CREDIT_OR_CHARGE_FLAG;
3947 l_price_adj_rec.ORIG_SYS_DISCOUNT_REF := price_adj_rec.ORIG_SYS_DISCOUNT_REF;
3948 l_price_adj_rec.CHANGE_REASON_CODE := price_adj_rec.CHANGE_REASON_CODE;
3949 l_price_adj_rec.CHANGE_REASON_TEXT := price_adj_rec.CHANGE_REASON_TEXT;
3950 l_price_adj_rec.COST_ID := price_adj_rec.COST_ID;
3951 --l_price_adj_rec.LIST_LINE_TYPE_CODE := price_adj_rec.LIST_LINE_TYPE_CODE;
3952 l_price_adj_rec.UPDATE_ALLOWED := price_adj_rec.UPDATE_ALLOWED;
3953 l_price_adj_rec.CHANGE_SEQUENCE := price_adj_rec.CHANGE_SEQUENCE;
3954 --l_price_adj_rec.LIST_HEADER_ID := price_adj_rec.LIST_HEADER_ID;
3955 --l_price_adj_rec.LIST_LINE_ID := price_adj_rec.LIST_LINE_ID;
3956 l_price_adj_tbl(l_price_adj_tbl.COUNT+1) := l_price_adj_rec;
3957 END LOOP;
3958 RETURN l_price_adj_tbl;
3959 END getHdrPrcAdjNonPRGTbl;
3960
3961 Procedure Split_Line(
3962 p_api_version_number IN NUMBER
3963 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3964 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3965 ,X_Return_Status OUT NOCOPY VARCHAR2
3966 ,X_Msg_Count OUT NOCOPY NUMBER
3967 ,X_Msg_Data OUT NOCOPY VARCHAR2
3968 ,p_quote_header_id IN NUMBER
3969 ,p_qte_line_id IN NUMBER
3970 ,p_quantities IN jtf_number_table
3971 ,p_last_update_date IN OUT NOCOPY DATE
3972 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
3973 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
3974 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
3975 ,p_minisite_id IN NUMBER := FND_API.G_MISS_NUM
3976 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
3977 )
3978 IS
3979 l_api_name CONSTANT VARCHAR2(30) := 'Split_Line';
3980 l_api_version CONSTANT NUMBER := 1.0;
3981 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
3982 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec;
3983 l_count NUMBER;
3984 l_last_update_date DATE;
3985 l_qte_line_count NUMBER;
3986 lx_quote_header_id NUMBER;
3987 lx_last_update_date DATE;
3988 -- Duplicate line Records (Temporary).
3989 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
3990 l_tmp_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
3991 l_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
3992 lx_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
3993 l_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
3994 l_line_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
3995 l_line_rltship_tbl ASO_QUOTE_PUB.line_rltship_tbl_type;
3996 l_ln_price_attributes_tbl ASO_QUOTE_PUB.price_attributes_tbl_type;
3997 l_ln_price_adj_tbl ASO_QUOTE_PUB.price_adj_tbl_type;
3998 -- Duplicate line Records (Used for actually calling Save.)
3999 l_sv_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
4000 l_sv_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
4001 l_sv_line_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
4002 l_sv_line_rltship_tbl ASO_QUOTE_PUB.line_rltship_tbl_type;
4003 l_sv_ln_price_attributes_tbl ASO_QUOTE_PUB.price_attributes_tbl_type;
4004 l_sv_ln_price_adj_tbl ASO_QUOTE_PUB.price_adj_tbl_type;
4005 lx_return_status VARCHAR2(1);
4006 lx_msg_count NUMBER;
4007 lx_msg_data VARCHAR2(2000);
4008 BEGIN
4009 -- Standard Start of API savepoint
4010 SAVEPOINT SPLIT_LINE_PVT;
4011 -- Standard call to check for call compatibility.
4012 IF NOT FND_API.Compatible_API_Call (l_api_version,
4013 P_Api_Version_Number,
4014 l_api_name,
4015 G_PKG_NAME )
4016 THEN
4017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4018 END IF;
4019 -- Initialize message list IF p_init_msg_list is set to TRUE.
4020 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4021 FND_MSG_PUB.initialize;
4022 END IF;
4023 -- Initialize API return status to success
4024 x_return_status := FND_API.G_RET_STS_SUCCESS;
4025
4026
4027 IF p_quantities IS NOT NULL THEN
4028
4029 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4030 IBE_UTIL.Debug('Validating User Starts: Also Pquantities is not null');
4031 END IF;
4032
4033 /* -- 4587019
4034 l_last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdatedate(p_quote_header_id);
4035
4036 IF (l_last_update_date <> p_last_update_date) then
4037 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4038 IBE_UTIL.Debug('comparing dates');
4039 END IF;
4040 p_last_update_date := l_last_update_date;
4041 -- raise error
4042
4043 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4044 FND_MESSAGE.Set_Name('IBE', 'IBE_SC_QUOTE_NEED_REFRESH');
4045 FND_MSG_PUB.ADD;
4046 END IF;
4047 RAISE FND_API.G_EXC_ERROR; -- need error message
4048 END IF;
4049 -- 4587019
4050 */
4051
4052 -- User Authentication
4053 IBE_Quote_Misc_pvt.Validate_User_Update
4054 ( p_init_msg_list => p_init_msg_list
4055 ,p_quote_header_id => p_quote_header_id
4056 ,p_party_id => p_party_id
4057 ,p_cust_account_id => p_cust_account_id
4058 ,p_quote_retrieval_number => p_quote_retrieval_number
4059 ,p_validate_user => p_validate_user
4060 ,x_return_status => x_return_status
4061 ,x_msg_count => x_msg_count
4062 ,x_msg_data => x_msg_data
4063 );
4064
4065
4066 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4067 RAISE FND_API.G_EXC_ERROR;
4068 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4070 END IF;
4071
4072 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4073 IBE_UTIL.Debug('Validating User Finishes');
4074 END IF;
4075
4076 l_qte_header_rec.quote_header_id := p_quote_header_id;
4077 l_qte_header_rec.last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdatedate(p_quote_header_id);
4078 l_qte_line_rec.quote_line_id := p_qte_line_id;
4079 l_qte_line_count := l_qte_line_tbl.COUNT;
4080
4081 For counter in 1.. p_quantities.count loop
4082 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4083 IBE_UTIL.Debug('p_quantities(counter) '||p_quantities(counter));
4084 END IF;
4085 End loop;
4086
4087 For counter in 1.. p_quantities.count loop
4088 l_qte_line_tbl(counter).quantity:= p_quantities(counter);
4089 End loop;
4090 l_control_rec.calculate_tax_flag := 'Y';
4091 l_control_rec.calculate_freight_charge_flag := 'Y';
4092 --mannamra:Removing references to obsoleted profile IBE_PRICE_REQUEST_TYPE see bug 2594529 for details
4093 l_control_rec.pricing_request_type := 'ASO';--FND_PROFILE.Value('IBE_PRICE_REQUEST_TYPE');
4094 l_control_rec.header_pricing_event := FND_PROFILE.Value('IBE_INCART_PRICING_EVENT');
4095 l_control_rec.line_pricing_event := FND_API.G_MISS_CHAR;
4096 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4097 IBE_UTIL.Debug('Split_quote_line: aso_split_line_int.split_quote_line:start');
4098 END IF;
4099 aso_split_line_int.Split_Quote_line (
4100 P_Api_Version_Number => 1.0,
4101 P_Init_Msg_List => FND_API.G_TRUE,
4102 P_Commit => FND_API.G_TRUE,
4103 p_qte_header_rec => l_qte_header_rec,
4104 p_original_qte_line_rec => l_qte_line_rec,
4105 p_control_rec => l_control_rec,
4106 P_Qte_Line_Tbl => l_qte_line_tbl,
4107 X_Qte_Line_Tbl => lx_qte_line_tbl,
4108 X_Return_Status => lx_return_status,
4109 X_Msg_Count => lx_msg_count,
4110 X_Msg_Data => lx_msg_data );
4111 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4112 RAISE FND_API.G_EXC_ERROR;
4113 END IF;
4114 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4116 END IF;
4117 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4118 IBE_UTIL.Debug('Split_quote_line: aso_split_line_int.split_quote_line:end');
4119 END IF;
4120 END IF;
4121 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4122 IBE_UTIL.Debug('IBE_Quote_Misc_pvt.Split_Line Ends');
4123 END IF;
4124 -- Standard check of p_commit.
4125 IF FND_API.To_Boolean( p_commit ) THEN
4126 COMMIT WORK;
4127 END IF;
4128 -- Standard call to get message count and IF count is 1, get message info.
4129 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4130 p_count => lx_msg_count,
4131 p_data => lx_msg_data);
4132 EXCEPTION
4133 WHEN FND_API.G_EXC_ERROR THEN
4134 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4135 IBE_Util.Debug('Unexpected error IBE_Quote_Misc_pvt.Split_line');
4136 END IF;
4137 ROLLBACK TO SPLIT_LINE_PVT;
4138 x_return_status := FND_API.G_RET_STS_ERROR;
4139 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4140 p_count => lx_msg_count,
4141 p_data => lx_msg_data);
4142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4143 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4144 IBE_Util.Debug('Expected error IBE_Quote_Misc_pvt.Split_line');
4145 END IF;
4146 ROLLBACK TO SPLIT_LINE_PVT;
4147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4148 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4149 p_count => lx_msg_count,
4150 p_data => lx_msg_data);
4151 WHEN OTHERS THEN
4152 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4153 IBE_Util.Debug('Unknown error IBE_Quote_Misc_pvt.Split_line');
4154 END IF;
4155 ROLLBACK TO SPLIT_LINE_PVT;
4156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4157 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4158 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
4159 l_api_name);
4160 END IF;
4161 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4162 p_count => lx_msg_count,
4163 p_data => lx_msg_data);
4164 END Split_Line;
4165
4166
4167 Procedure validate_quote(
4168 p_quote_header_id IN NUMBER
4169 ,p_save_type IN NUMBER
4170 ,x_return_status OUT NOCOPY VARCHAR2
4171 ,x_msg_count OUT NOCOPY NUMBER
4172 ,x_msg_data OUT NOCOPY VARCHAR2)
4173 IS
4174 -- Get resource_id, publish_flag and quote_status of a quote.
4175 cursor c_get_quote_details is
4176 select a.resource_id,
4177 a.publish_flag,
4178 b.status_code,
4179 a.party_id,
4180 a.cust_account_id,
4181 a.quote_source_code
4182 from aso_quote_headers a ,
4183 aso_quote_statuses_b b
4184 where a.quote_status_id = b.quote_status_id
4185 and a.quote_header_id = p_quote_header_id;
4186
4187 l_api_name CONSTANT VARCHAR2(30) := 'Validate_quote';
4188 l_resource_id NUMBER;
4189 l_publish_flag VARCHAR2(1);
4190 l_status_code VARCHAR2(30);
4191 l_source_code VARCHAR2(100);
4192 l_party_id NUMBER;
4193 l_cust_account_id NUMBER;
4194 l_validate_quote_sts VARCHAR2(2) := FND_API.G_TRUE;
4195 l_error VARCHAR2(1) := FND_API.G_FALSE;
4196
4197 CURSOR c_get_active_cart(c_quote_header_id NUMBER,
4198 c_party_id NUMBER,
4199 c_cust_account_id NUMBER) is
4200 select quote_header_id
4201 from ibe_active_quotes
4202 where quote_header_id = c_quote_header_id
4203 and party_id = c_party_id
4204 and cust_account_id = c_cust_account_id
4205 and record_type = 'CART';
4206
4207
4208 l_active_cart NUMBER;
4209 l_return_status VARCHAR2(1);
4210 l_msg_count NUMBER ;
4211 l_msg_data VARCHAR2(2000);
4212 rec_get_active_cart c_get_active_cart%rowtype;
4213
4214 BEGIN
4215 SAVEPOINT validate_quote;
4216 -- Get resource_id, publish_flag and quote_status of a quote.
4217 -- Initialize API return status to success
4218 x_return_status := FND_API.G_RET_STS_SUCCESS;
4219 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4220 IBE_UTIL.DEBUG('Begin Validate_quote' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4221 END IF;
4222 open c_get_quote_details;
4223 fetch c_get_quote_details into l_resource_id,
4224 l_publish_flag,
4225 l_status_code,
4226 l_party_id,
4227 l_cust_account_id,
4228 l_source_code;
4229 close c_get_quote_details;
4230
4231 IF l_resource_id is not null and nvl(l_publish_flag,'N')='N' THEN
4232 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4233 IBE_UTIL.DEBUG('Validate_quote, quote is unpublished' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4234 END IF;
4235
4236 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4237 FND_Message.Set_Name('IBE', 'IBE_SC_QUOTE_NOT_PUBL');
4238 FND_Msg_Pub.Add;
4239 END IF;
4240 l_error := FND_API.G_TRUE;
4241 END IF;
4242
4243 IF (p_save_type is not null and
4244 (p_save_type = END_WORKING OR p_save_type = SAVE_PAYMENT_ONLY
4245 OR p_save_type = SALES_ASSISTANCE OR p_save_type = PLACE_ORDER OR p_save_type = OP_DUPLICATE_CART)) THEN
4246 l_validate_quote_sts := FND_API.G_FALSE;
4247 END IF;
4248
4249 IF (l_validate_quote_sts = FND_API.G_TRUE) THEN
4250 IF (l_resource_id is not null) THEN
4251 IF (FND_Profile.Value('IBE_UPDATE_DRAFT_QUOTES') = 'Y' ) THEN -- Update on Draft profile enabled, only allow updates on DRAFT.
4252 IF (l_status_code <> 'DRAFT') THEN
4253 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4254 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_QUOTE_STS');
4255 FND_Msg_Pub.Add;
4256 END IF;
4257 l_error := FND_API.G_TRUE;
4258 END IF;
4259 ELSE -- update profile is not enabled, but update call for quote is coming down.
4260 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4261 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_OPERATION'); -- Invalid Operation
4262 FND_Msg_Pub.Add;
4263 END IF;
4264 l_error := FND_API.G_TRUE;
4265 END IF;
4266 ELSE -- for a cart, check for 'STORE DRAFT'
4267 IF (l_status_code <> 'STORE DRAFT') THEN
4268 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4269 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_CART_STS');
4270 FND_Msg_Pub.Add;
4271 END IF;
4272 l_error := FND_API.G_TRUE;
4273 END IF;
4274 END IF;
4275 END IF;
4276 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4277 IBE_UTIL.DEBUG('Validate_quote: Validation for one-click start');
4278 END IF;
4279 IF (p_save_type <> END_WORKING AND p_save_type <> OP_DELETE_CART) THEN
4280 IF ((p_save_type = UPDATE_EXPRESSORDER OR p_save_type = SAVE_EXPRESSORDER)
4281 AND (l_source_code <> 'IStore Oneclick')) THEN
4282 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4283 IBE_UTIL.DEBUG('Validate_quote: Oneclick operation on a non-oneclick cart');
4284 END IF;
4285 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4286 FND_Message.Set_Name('IBE', 'IBE_SC_INVALID_OPERATION'); -- Invalid Operation
4287 FND_Msg_Pub.Add;
4288 END IF;
4289 l_error := FND_API.G_TRUE;
4290 ELSE
4291 IF ((p_save_type <> UPDATE_EXPRESSORDER AND p_save_type <> SAVE_EXPRESSORDER)
4292 AND (l_source_code = 'IStore Oneclick')) THEN
4293
4294 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4295 IBE_UTIL.DEBUG('Validate_quote: Non-Oneclick operation on a oneclick cart');
4296 END IF;
4297
4298 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4299 FND_Message.Set_Name('IBE', 'IBE_SC_CART_ORDERED');
4300 FND_Msg_Pub.Add;
4301 END IF;
4302 l_error := FND_API.G_TRUE;
4303 END IF;
4304 END IF;
4305
4306 END IF;
4307
4308 IF (l_error = FND_API.G_TRUE) THEN
4309 FOR rec_get_active_cart in c_get_active_cart(p_quote_header_id,
4310 l_party_id,
4311 l_cust_account_id) LOOP
4312 l_active_cart := rec_get_active_cart.quote_header_id;
4313 IF (l_active_cart is not null) THEN
4314 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4315 IBE_UTIL.DEBUG('Validate_quote, active cart found, deactivate it' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4316 END IF;
4317
4318 IBE_QUOTE_SAVESHARE_V2_PVT.DEACTIVATE_QUOTE (
4319 P_Quote_header_id => p_quote_header_id,
4320 P_Party_id => l_party_id ,
4321 P_Cust_account_id => l_Cust_account_id ,
4322 p_api_version => 1 ,
4323 p_init_msg_list => fnd_api.g_false ,
4324 p_commit => fnd_api.g_true ,
4325 x_return_status => l_return_status ,
4326 x_msg_count => l_msg_count ,
4327 x_msg_data => l_msg_data );
4328
4329 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4330 RAISE FND_API.G_EXC_ERROR;
4331 END IF;
4332
4333 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4335 END IF;
4336 END IF;
4337 EXIT when c_get_active_cart%notfound;
4338 END LOOP;
4339 RAISE FND_API.G_EXC_ERROR;
4340 END IF;
4341
4342
4343 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4344 IBE_UTIL.DEBUG('End validate_quote' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4345 END IF;
4346
4347 EXCEPTION
4348 WHEN FND_API.G_EXC_ERROR THEN
4349 ROLLBACK TO validate_quote;
4350 x_return_status := FND_API.G_RET_STS_ERROR;
4351 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4352 p_count => x_msg_count ,
4353 p_data => x_msg_data);
4354 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4355 IBE_Util.Debug('End With Exp Exception IBE_Quote_Misc_pvt.validate_quote');
4356 END IF;
4357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4358 ROLLBACK TO validate_quote;
4359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4360 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4361 p_count => x_msg_count ,
4362 p_data => x_msg_data);
4363 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4364 IBE_Util.Debug('End with UnExp Exception IBE_Quote_Misc_pvt.validate_quote');
4365 END IF;
4366 WHEN OTHERS THEN
4367 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
4368 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
4369 l_api_name);
4370 END IF;
4371 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4372 p_count => x_msg_count ,
4373 p_data => x_msg_data);
4374 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4375 IBE_Util.Debug('End with Others Exception IBE_Quote_Misc_pvt.validate_quote');
4376 END IF;
4377 END validate_quote;
4378
4379 PROCEDURE Validate_User_Update(
4380 p_api_version_number IN NUMBER := 1.0
4381 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4382 ,p_quote_header_id IN NUMBER
4383 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
4384 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
4385 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
4386 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
4387 ,p_privilege_type_code IN VARCHAR2 := 'F'
4388 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
4389 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
4390 ,x_return_status OUT NOCOPY VARCHAR2
4391 ,x_msg_count OUT NOCOPY NUMBER
4392 ,x_msg_data OUT NOCOPY VARCHAR2)
4393
4394 IS
4395
4396 l_api_name CONSTANT VARCHAR2(30) := 'Validate_User_Update';
4397 l_api_version CONSTANT NUMBER := 1.0;
4398 l_db_quote_header_id NUMBER;
4399 l_party_id NUMBER;
4400 l_user_id NUMBER :=FND_GLOBAL.USER_ID;
4401 l_db_user_id NUMBER;
4402 l_privilege_type_code VARCHAR2(10);
4403 l_end_date_active DATE;
4404 l_upgrade_flag varchar2(1) := FND_API.G_TRUE;
4405
4406 l_last_update_date DATE;
4407 l_quote_status VARCHAR2(100);
4408 l_last_updated_by NUMBER;
4409 l_owner_party_id NUMBER;
4410 l_resource_id NUMBER;
4411 l_last_upd_party_id NUMBER;
4412 l_is_member NUMBER := null;
4413 l_err_code VARCHAR2(50) := null;
4414 l_person_party_id NUMBER;
4415
4416 CURSOR c_getPartyInfo(c_user_id NUMBER) IS
4417 SELECT customer_id
4418 from fnd_user
4419 WHERE user_id = c_user_id;
4420
4421 CURSOR c_getShareeInfo IS
4422 SELECT quote_header_id,update_privilege_type_code,end_date_active, party_id, cust_account_id
4423 from ibe_sh_quote_access
4424 where quote_sharee_number = p_quote_retrieval_number
4425 and quote_header_id = p_quote_header_id;
4426
4427 CURSOR c_isSharee(c_party_id NUMBER) IS
4428 SELECT count(*)
4429 from ibe_sh_quote_access
4430 where party_id = c_party_id
4431 and quote_header_id = p_quote_header_id;
4432
4433 CURSOR c_getQuoteInfo IS
4434 SELECT quote_header_id
4435 from aso_quote_headers_all
4436 where quote_header_id = p_quote_header_id
4437 AND (party_id = l_party_id OR (party_id = p_party_id AND cust_account_id = p_cust_account_id));
4438
4439 CURSOR c_getQuoteInfo2 IS -- bug 13517114, scnagara
4440 SELECT quote_header_id
4441 from aso_quote_headers_all
4442 where quote_header_id = p_quote_header_id
4443 AND party_id in (select relationship_party_id
4444 FROM IBE_CUSTOMERS_ASSIGNED_V
4445 where person_party_id = l_person_party_id);
4446
4447 CURSOR c_getPersonPartyInfo(c_user_id NUMBER) IS
4448 SELECT person_party_id
4449 from fnd_user
4450 WHERE user_id = c_user_id;
4451
4452 -- 9/23/02: we're using the next cursor
4453 CURSOR c_getResourceInfo_orig IS
4454 select resource_id
4455 from jtf_rs_resource_extns
4456 where user_id = l_user_id;
4457
4458 -- 9/23/02: new cursor to check for salesrep
4459 Cursor c_getResourceInfo IS
4460 SELECT j.resource_id
4461 FROM jtf_rs_srp_vl srp, jtf_rs_resource_extns j
4462 WHERE j.user_id = l_user_id
4463 AND j.resource_id = srp.resource_id
4464 AND srp.status = 'A'
4465 AND nvl(trunc(srp.start_date_active), trunc(sysdate)) <= trunc(sysdate)
4466 AND nvl(trunc(srp.end_date_active), trunc(sysdate)) >= trunc(sysdate)
4467 AND NVL(srp.org_id,MO_GLOBAL.get_current_org_id()) = MO_GLOBAL.get_current_org_id();
4468
4469 --For the last update validation --08/06/2003
4470
4471 Cursor c_last_update_date(c_quote_hdr_id number) is
4472 SELECT status_code,
4473 a.last_update_date,
4474 a.last_updated_by,
4475 a.party_id,
4476 a.resource_id
4477 FROM aso_quote_headers a,
4478 aso_quote_statuses_vl b
4479 WHERE quote_header_id = c_quote_hdr_id
4480 and a.quote_status_id = b.quote_status_id;
4481
4482 rec_last_update_date c_last_update_date%rowtype;
4483
4484 rec_sharee_info c_getShareeInfo%rowtype;
4485
4486 -- 9/11/02: we want to check if this cart is a guest cart
4487 CURSOR c_getActiveCartTypeInfo IS
4488 select quote_source_code from aso_quote_headers_all where quote_header_id = p_quote_header_id;
4489 rec_ActiveCartType_info c_getActiveCartTypeInfo%rowtype;
4490
4491 BEGIN
4492
4493 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4494 IBE_UTIL.DEBUG('Begin validate_user_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4495 END IF;
4496
4497 -- Standard call to check for call compatibility.
4498 IF NOT FND_API.Compatible_API_Call (l_api_version,
4499 p_api_version_number,
4500 l_api_name,
4501 G_PKG_NAME )
4502 THEN
4503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4504 END IF;
4505
4506 -- Initialize message list if p_init_msg_list is set to TRUE.
4507
4508 IF FND_API.To_Boolean( p_init_msg_list ) THEN
4509 FND_Msg_Pub.initialize;
4510 END IF;
4511
4512 -- Initialize API return status to success
4513 x_return_status := FND_API.G_RET_STS_SUCCESS;
4514
4515 -- API body
4516
4517 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4518 IBE_UTIL.DEBUG('Incoming party_id is '||p_party_id);
4519 IBE_UTIL.DEBUG('Incoming cust_account_id is '||p_cust_account_id);
4520 IBE_UTIL.DEBUG('Incoming quote_header_id is '||p_quote_header_id);
4521 IBE_UTIL.DEBUG('User id obtained from environment is: '||l_user_id);
4522 IBE_UTIL.DEBUG('p_save_type is :'||p_save_type);
4523 END IF;
4524 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
4525
4526 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4527 IBE_UTIL.DEBUG('Entered Validation...'|| p_validate_user);
4528 END IF;
4529
4530 IF (p_quote_retrieval_number is not null AND p_quote_retrieval_number <> FND_API.G_MISS_NUM) then
4531
4532 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4533 IBE_UTIL.DEBUG('In validating Recipient flow '||p_quote_retrieval_number||' '||to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4534 END IF;
4535
4536 OPEN c_getShareeInfo;
4537 FETCH c_getShareeInfo INTO rec_sharee_info;
4538 CLOSE c_getShareeInfo;
4539
4540 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4541 IBE_UTIL.DEBUG('rec_sharee_info.quote_header_id : '||rec_sharee_info.quote_header_id);
4542 END IF;
4543
4544 IF ((rec_sharee_info.quote_header_id is null) OR
4545 (nvl(rec_sharee_info.end_date_active,sysdate+1) <= sysdate)) then
4546 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4547 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4548 FND_Msg_Pub.Add;
4549 END IF;
4550 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4551 IBE_UTIL.DEBUG('quote_retrieval_number::quotehdrId'||'('||p_quote_retrieval_number||','||p_quote_header_id||')');
4552 END IF;
4553 RAISE FND_API.G_EXC_ERROR;
4554 END IF;
4555
4556 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4557 IBE_UTIL.DEBUG('rec_sharee_info.party_id : '||rec_sharee_info.party_id);
4558 IBE_UTIL.DEBUG('rec_sharee_info.cust_account_id : '||rec_sharee_info.cust_account_id);
4559 END IF;
4560
4561
4562 -- if we have party and acct passed in AND in the table then
4563 -- check that the passed-in user matches the recipient identity we have in the table
4564 if ((rec_sharee_info.party_id is not null) and
4565 (rec_sharee_info.cust_account_id is not null) and
4566 (p_party_id <> FND_API.G_MISS_NUM) and
4567 (p_cust_account_id <> FND_API.G_MISS_NUM)) then
4568 if ((rec_sharee_info.party_id <> p_party_id) or (rec_sharee_info.cust_account_id <> p_cust_account_id)) then
4569 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4570 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4571 FND_Msg_Pub.Add;
4572 END IF;
4573 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4574 IBE_UTIL.DEBUG('passed in partyid and account id does not match those of the retrieval number');
4575 IBE_UTIL.DEBUG('passed in partyid: ' || p_party_id || ' and account id : ' || p_cust_account_id);
4576 IBE_UTIL.DEBUG('retrieval partyid: ' || rec_sharee_info.party_id || ' and account id : ' || rec_sharee_info.cust_account_id);
4577 END IF;
4578 RAISE FND_API.G_EXC_ERROR;
4579 end if;
4580 -- otherwise, we may have a case where we can validate and then upgrade a partyless row to have a partyid and acctid
4581 elsif ((rec_sharee_info.party_id is null) and
4582 (rec_sharee_info.cust_account_id is null) and
4583 (p_party_id <> FND_API.G_MISS_NUM) and
4584 (p_cust_account_id <> FND_API.G_MISS_NUM)) then
4585 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4586 IBE_UTIL.DEBUG('we have blank partyid and acctid in the share table, see if we can upgrade this row...');
4587 IBE_UTIL.DEBUG('passed in partyid: ' || p_party_id || ' and account id : ' || p_cust_account_id);
4588 END IF;
4589 upgrade_recipient_row(
4590 p_party_id => p_party_id,
4591 p_cust_account_id => p_cust_account_id,
4592 p_retrieval_number => p_quote_retrieval_number,
4593 p_quote_header_id => p_quote_header_id,
4594 x_valid_flag => l_upgrade_flag);
4595 if (l_upgrade_flag <> FND_API.G_TRUE) then
4596 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4597 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4598 FND_Msg_Pub.Add;
4599 END IF;
4600 end if;
4601 end if;
4602
4603 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4604 IBE_UTIL.DEBUG('rec_sharee_info.update_privilege_type_code : '||rec_sharee_info.update_privilege_type_code);
4605 END IF;
4606
4607 --Skip this validation for Duplicate Action
4608 IF p_save_type <> OP_DUPLICATE_CART THEN
4609 l_privilege_type_code := rec_sharee_info.update_privilege_type_code;
4610 IF l_privilege_type_code <> 'A' THEN
4611 IF l_privilege_type_code = 'F' THEN
4612 IF p_privilege_type_code <> 'F' AND p_privilege_type_code <> 'R' THEN
4613 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4614 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
4615 FND_Msg_Pub.Add;
4616 END IF;
4617 RAISE FND_API.G_EXC_ERROR;
4618 END IF;
4619 ELSE
4620 IF p_privilege_type_code <> 'R' THEN
4621 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4622 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
4623 FND_Msg_Pub.Add;
4624 END IF;
4625 RAISE FND_API.G_EXC_ERROR; -- need error message
4626 END IF;
4627 END IF;
4628 end if;
4629 -- else, the access level is Admin and we are okay to do other validations
4630 END IF; --Duplicate action
4631 ELSE
4632 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4633 IBE_UTIL.DEBUG('no sharee number');
4634 END IF;
4635
4636 -- 9/11/02: if the cartId passed in is a Guest Cart, we should not go forth w/ the validation
4637 OPEN c_getActiveCartTypeInfo;
4638 FETCH c_getActiveCartTypeInfo INTO rec_ActiveCartType_info;
4639 CLOSE c_getActiveCartTypeInfo;
4640 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4641 IBE_UTIL.DEBUG('quote_source_code of cart passed in='||rec_ActiveCartType_info.quote_source_code);
4642 END IF;
4643 if (rec_ActiveCartType_info.quote_source_code = 'IStore Walkin') then
4644 return;
4645 end if;
4646
4647 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
4648
4649 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4650 IBE_UTIL.DEBUG('In validating Owner flow: '||p_party_id||','||p_cust_account_id||' '||to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4651 END IF;
4652 OPEN c_getQuoteInfo;
4653 FETCH c_getQuoteInfo INTO l_db_quote_header_id;
4654 CLOSE c_getQuoteInfo;
4655 IF l_db_quote_header_id is null then
4656 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4657 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4658 FND_Msg_Pub.Add;
4659 END IF;
4660 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4661 IBE_UTIL.DEBUG('partyId::custAcctId::quotehdrId'||'('||p_party_id||','||p_cust_account_id||','||p_quote_header_id||')');
4662 END IF;
4663 RAISE FND_API.G_EXC_ERROR;
4664 END IF;
4665 ELSE
4666 -- retrieving user info from environment
4667 OPEN c_getResourceInfo;
4668 FETCH c_getResourceInfo INTO l_db_user_id;
4669 CLOSE c_getResourceInfo;
4670 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4671 IBE_UTIL.DEBUG('Owner flow with env. userid: '||l_db_user_id||' '||to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4672 END IF;
4673
4674 IF l_db_user_id is null then
4675 OPEN c_getPartyInfo(l_user_id);
4676 FETCH c_getPartyInfo INTO l_party_id;
4677 CLOSE c_getPartyInfo;
4678
4679 OPEN c_getQuoteInfo;
4680 FETCH c_getQuoteInfo INTO l_db_quote_header_id;
4681 CLOSE c_getQuoteInfo;
4682
4683 IF l_db_quote_header_id is null then
4684 OPEN c_getPersonPartyInfo(l_user_id);
4685 FETCH c_getPersonPartyInfo INTO l_person_party_id;
4686 CLOSE c_getPersonPartyInfo;
4687
4688 OPEN c_getQuoteInfo2;
4689 FETCH c_getQuoteInfo2 INTO l_db_quote_header_id;
4690 CLOSE c_getQuoteInfo2;
4691 END IF;
4692
4693 IF l_db_quote_header_id is null then
4694 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4695 FND_Message.Set_Name('IBE', 'IBE_SC_USERACCESS_ERR');
4696 FND_Msg_Pub.Add;
4697 END IF;
4698 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4699 IBE_UTIL.DEBUG('partyId::quotehdrId'||'('||p_party_id||','||p_quote_header_id||')');
4700 END IF;
4701 RAISE FND_API.G_EXC_ERROR;
4702 END IF; -- end if l_db_quote_header_id is null
4703 END IF; -- end if l_db_user_id is null
4704 END IF; -- end section of user info from env
4705 END IF; -- end if no sharee number
4706
4707 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4708 IBE_UTIL.DEBUG('Validate_user_update: Before Last update date validation,p_last_update_date= '||p_last_update_date);
4709 END IF;
4710
4711 IF (p_last_update_date <> FND_API.G_MISS_DATE) THEN
4712 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4713 IBE_UTIL.DEBUG('Validate_user_update: Last update date validation START');
4714 END IF;
4715 FOR rec_last_update_date in c_last_update_date(p_quote_header_id) LOOP
4716 l_last_update_date := rec_last_update_date.last_update_date;
4717 l_quote_status := rec_last_update_date.status_code;
4718 l_last_updated_by := rec_last_update_date.last_updated_by;
4719 l_owner_party_id := rec_last_update_date.party_id;
4720 l_resource_id := rec_last_update_date.resource_id;
4721 EXIT when c_last_update_date%NOTFOUND;
4722 END LOOP;
4723 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4724 IBE_UTIL.DEBUG('Validate_user_update: l_last_update_date='||to_char(l_last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
4725 IBE_UTIL.DEBUG('Validate_user_update: p_last_update_date='||to_char(p_last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
4726 END IF;
4727 IF(l_last_update_date <> p_last_update_date) THEN
4728 IF (l_quote_status = 'ORDER SUBMITTED') THEN
4729 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4730 IBE_UTIL.DEBUG('Validate_user_update: raising Quote_already_ordered error');
4731 END IF;
4732
4733 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4734 FND_Message.Set_Name('IBE', 'IBE_SC_CART_ORDERED');
4735 FND_Msg_Pub.Add;
4736 END IF;
4737 RAISE FND_API.G_EXC_ERROR;
4738 ELSE
4739 IF ((l_last_updated_by <> l_user_id) OR (p_save_type = PLACE_ORDER)) THEN
4740 -- determine which error message we need to show
4741 OPEN c_getPartyInfo(l_last_updated_by);
4742 FETCH c_getPartyInfo INTO l_last_upd_party_id;
4743 CLOSE c_getPartyInfo;
4744 -- have to use party id to determine owner since createdby may be the sales rep
4745 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4746 IBE_UTIL.DEBUG('Validate_user_update: l_last_upd_party_id : ' || l_last_upd_party_id);
4747 IBE_UTIL.DEBUG('Validate_user_update: l_owner_party_id : ' || l_owner_party_id);
4748 IBE_UTIL.DEBUG('Validate_user_update: l_resource_id : ' || l_resource_id);
4749 END IF;
4750
4751 -- user is not last updated, and last update is the owner (another member)
4752 if (l_last_upd_party_id = l_owner_party_id) then
4753 if (l_resource_id is not null) then
4754 l_err_code := 'IBE_SC_ERR_RELOAD_Q_MEMBER_UPD';
4755 else
4756 l_err_code := 'IBE_SC_ERR_RELOAD_C_MEMBER_UPD';
4757 end if;
4758 else
4759 -- use is not last updated, and last updated is a member
4760 OPEN c_isSharee(l_last_upd_party_id);
4761 FETCH c_isSharee INTO l_is_member;
4762 CLOSE c_isSharee;
4763 if ((l_is_member is not null) and (l_is_member > 0)) then
4764 if (l_resource_id is not null) then
4765 l_err_code := 'IBE_SC_ERR_RELOAD_Q_MEMBER_UPD';
4766 else
4767 l_err_code := 'IBE_SC_ERR_RELOAD_C_MEMBER_UPD';
4768 end if;
4769 end if;
4770 end if;
4771 if (l_err_code is null) then
4772 -- otherwise, the last person to have updated the cart was a sales rep
4773 l_err_code := 'IBE_SC_ERR_RELOAD_SALESREP_UPD';
4774 end if;
4775
4776 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4777 IBE_UTIL.DEBUG('Validate_user_update: raising Quote_needs_refresh error : ' || l_err_code);
4778 END IF;
4779
4780 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4781 FND_Message.Set_Name('IBE', l_err_code);
4782 FND_Msg_Pub.Add;
4783 END IF;
4784 RAISE FND_API.G_EXC_ERROR;
4785 END IF;
4786 END IF;
4787 END IF;
4788
4789 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4790 IBE_UTIL.DEBUG('Validate_user_update: Last update date validation END');
4791 END IF;
4792
4793 END IF; -- last_update_date validation end.
4794
4795 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4796 IBE_UTIL.DEBUG('Before calling validate_quote' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4797 END IF;
4798 -- call an internal api, for quote or cart validation.
4799 validate_quote(p_quote_header_id,
4800 p_save_type,
4801 x_return_status,
4802 x_msg_count,
4803 x_msg_data);
4804
4805 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4806 IBE_UTIL.DEBUG('End validate_user_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
4807 END IF;
4808 END IF; -- end if quote header id is not null
4809 EXCEPTION
4810 WHEN FND_API.G_EXC_ERROR THEN
4811 x_return_status := FND_API.G_RET_STS_ERROR;
4812 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4813 p_count => x_msg_count ,
4814 p_data => x_msg_data);
4815 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4816 IBE_Util.Debug('End:Expected exception:IBE_Quote_Misc_pvt.validate_user_update');
4817 END IF;
4818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4820 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4821 p_count => x_msg_count ,
4822 p_data => x_msg_data);
4823 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4824 IBE_Util.Debug('End:Unexpected exception:IBE_Quote_Misc_pvt.validate_user_update');
4825 END IF;
4826 WHEN OTHERS THEN
4827 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
4828 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
4829 l_api_name);
4830 END IF;
4831 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4832 p_count => x_msg_count ,
4833 p_data => x_msg_data);
4834 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4835 IBE_Util.Debug('End:Others exception:IBE_Quote_Misc_pvt.validate_user_update');
4836 END IF;
4837 END validate_user_update;
4838
4839 /*This procedure is used to save the missing party_id and cust_account_id
4840 of the recipient before cart activation*/
4841 -- assumes that the retrieval number given has no party id and accountid
4842 PROCEDURE upgrade_recipient_row(
4843 p_party_id in NUMBER,
4844 p_cust_account_id in NUMBER,
4845 p_retrieval_number in NUMBER,
4846 p_quote_header_id in NUMBER,
4847 x_valid_flag out NOCOPY VARCHAR2) is
4848
4849 cursor c_sharee_id(c_retrieval_num NUMBER) is
4850 select quote_sharee_id
4851 from ibe_sh_quote_access
4852 where quote_sharee_number = c_retrieval_num ;
4853
4854 cursor c_get_sold_to(c_quote_header_id NUMBER) is
4855 select cust_account_id, party_type
4856 from aso_quote_headers_all a, hz_parties p
4857 where a.party_id = p.party_id
4858 and quote_header_id = c_quote_header_id;
4859
4860 cursor c_get_party_type(c_party_id NUMBER) is
4861 select party_type
4862 from hz_parties
4863 where party_id = c_party_id;
4864
4865 rec_get_sold_to c_get_sold_to%rowtype;
4866
4867 l_recip_id NUMBER := NULL;
4868 l_sold_to_cust NUMBER := NULL;
4869 l_party_type_cart_owner VARCHAR2(30);
4870 l_party_type_recipient VARCHAR2(30);
4871
4872 BEGIN
4873 x_valid_flag := FND_API.G_TRUE;
4874 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4875 IBE_UTIL.DEBUG('upgrade_recipient_row: BEGIN ');
4876 IBE_UTIL.DEBUG(' p_party_id : ' || p_party_id);
4877 IBE_UTIL.DEBUG(' p_cust_account_id : ' || p_cust_account_id);
4878 IBE_UTIL.DEBUG(' p_retrieval_number: ' || p_retrieval_number);
4879 IBE_UTIL.DEBUG(' p_quote_header_id : ' || p_quote_header_id);
4880 END IF;
4881
4882 FOR rec_get_sold_to in c_get_sold_to(p_quote_header_id) LOOP
4883 l_sold_to_cust := rec_get_sold_to.cust_account_id;
4884 l_party_type_cart_owner := rec_get_sold_to.party_type;
4885 exit when c_get_sold_to%NOTFOUND;
4886 END LOOP;
4887 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4888 IBE_UTIL.DEBUG(' cart owner pty type: ' || l_party_type_cart_owner);
4889 end if;
4890 IF(l_party_type_cart_owner = 'PARTY_RELATIONSHIP') then
4891 if (p_cust_account_id <> l_sold_to_cust) THEN
4892 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4893 IBE_UTIL.DEBUG('Not upgrading as the b2b cart account id does not match the account id passed in');
4894 END IF;
4895 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4896 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4897 FND_Msg_Pub.Add;
4898 END IF;
4899 x_valid_flag := FND_API.G_FALSE;
4900 RAISE FND_API.G_EXC_ERROR;
4901 end if;
4902 elsif (l_party_type_cart_owner = 'PERSON') then
4903 OPEN c_get_party_type(p_party_id);
4904 FETCH c_get_party_type into l_party_type_recipient;
4905 CLOSE c_get_party_type;
4906
4907 if (l_party_type_cart_owner <> l_party_type_recipient) THEN
4908 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4909 IBE_UTIL.DEBUG('Not saving party and cust_account_id because the recipient is not a b2c user');
4910 IBE_UTIL.DEBUG('Recipient user type: ' || l_party_type_recipient);
4911 END IF;
4912 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
4913 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
4914 FND_Msg_Pub.Add;
4915 END IF;
4916 x_valid_flag := FND_API.G_FALSE;
4917 RAISE FND_API.G_EXC_ERROR;
4918 end if;
4919 END IF; -- end person check
4920
4921 if (x_valid_flag = FND_API.G_TRUE) then
4922 -- if we passed validations then upgrade the share row with the partyid and accountid
4923 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4924 IBE_UTIL.DEBUG('Upgrading the share row with input partyid and accountid.');
4925 END IF;
4926
4927 OPEN c_sharee_id(p_retrieval_number);
4928 FETCH c_sharee_id into l_recip_id;
4929 CLOSE c_sharee_id;
4930 IBE_SH_QUOTE_ACCESS_PKG.update_Row(
4931 p_QUOTE_SHAREE_ID => l_recip_id
4932 ,p_party_id => p_party_id
4933 ,p_cust_account_id => p_cust_account_id);
4934 end if;
4935
4936 END upgrade_recipient_row; -- upgrade_recipient_row
4937
4938 PROCEDURE Log_Environment_Info (
4939 p_quote_header_id in number := null
4940 ) IS
4941
4942 cursor c_getAppId
4943 is
4944 select fnd_global.resp_appl_id appId from dual;
4945 rec_AppId c_getAppId%rowtype;
4946
4947 cursor c_getRespId
4948 is
4949 select fnd_global.resp_id respId from dual;
4950 rec_RespId c_getRespId%rowtype;
4951
4952 cursor c_getUserId
4953 is
4954 select fnd_global.user_id userId from dual;
4955 rec_UserId c_getUserId%rowtype;
4956
4957 cursor c_getOrgId
4958 is
4959 SELECT (MO_GLOBAL.get_current_org_id()) orgId from dual;
4960 rec_OrgId c_getOrgId%rowtype;
4961
4962 cursor c_getEnvInfo
4963 is
4964 SELECT FND_GLOBAL.SESSION_ID session_id
4965 ,FND_GLOBAL.USER_NAME user_name
4966 ,FND_GLOBAL.LOGIN_ID login_id
4967 ,userenv('CLIENT_INFO') client_info
4968 ,userenv('LANG') lang
4969 FROM dual;
4970 rec_EnvInfo c_getEnvInfo%rowtype;
4971
4972 cursor c_getICXSessionDetails(p_session_id VARCHAR2)
4973 is
4974 SELECT SESSION_ID
4975 ,USER_ID
4976 ,RESPONSIBILITY_ID
4977 ,ORG_ID
4978 ,NLS_LANGUAGE
4979 ,CREATED_BY
4980 ,CREATION_DATE
4981 ,LAST_UPDATED_BY
4982 ,LAST_UPDATE_DATE
4983 ,LAST_UPDATE_LOGIN
4984 ,RESPONSIBILITY_APPLICATION_ID
4985 ,SECURITY_GROUP_ID
4986 ,PAGE_ID,LOGIN_ID
4987 ,TIME_OUT
4988 FROM icx_sessions
4989 WHERE session_id = p_session_id;
4990 rec_ICXSessionDetails c_getICXSessionDetails%rowtype;
4991
4992 cursor c_getMOTempTableInfo
4993 is
4994 SELECT * from MO_GLOB_ORG_ACCESS_TMP;
4995 rec_MOTempTableInfo c_getMOTempTableInfo%rowtype;
4996
4997 cursor c_getSysContext
4998 is
4999 SELECT sys_context('multi_org2','current_org_id') sys_context from dual;
5000 rec_SysContext c_getSysContext%rowtype;
5001
5002 Begin
5003 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5004 IBE_UTIL.DEBUG('Log_Environment_Info: Begin');
5005 END IF;
5006
5007 -- 1. AppId
5008 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5009 IBE_UTIL.DEBUG('Log_Environment_Info: AppId: Begin');
5010 END IF;
5011 open c_getAppId;
5012 fetch c_getAppId into rec_AppId;
5013 close c_getAppId;
5014 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5015 IBE_UTIL.DEBUG('Log_Environment_Info: AppId: End='||rec_AppId.appId);
5016 END IF;
5017
5018 -- 2. RespId
5019 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5020 IBE_UTIL.DEBUG('Log_Environment_Info: RespId: Begin');
5021 END IF;
5022 open c_getRespId;
5023 fetch c_getRespId into rec_RespId;
5024 close c_getRespId;
5025 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5026 IBE_UTIL.DEBUG('Log_Environment_Info: RespId: End='||rec_RespId.respId);
5027 END IF;
5028
5029 -- 3. UserId
5030 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5031 IBE_UTIL.DEBUG('Log_Environment_Info: UserId: Begin');
5032 END IF;
5033 open c_getUserId;
5034 fetch c_getUserId into rec_UserId;
5035 close c_getUserId;
5036 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5037 IBE_UTIL.DEBUG('Log_Environment_Info: UserId: End='||rec_UserId.userId);
5038 END IF;
5039
5040 -- 4. OrgId
5041 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5042 IBE_UTIL.DEBUG('Log_Environment_Info: OrgId: Begin');
5043 END IF;
5044 open c_getOrgId;
5045 fetch c_getOrgId into rec_OrgId;
5046 close c_getOrgId;
5047 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5048 IBE_UTIL.DEBUG('Log_Environment_Info: OrgId: End='||rec_OrgId.orgId);
5049 END IF;
5050
5051 -- 5. EnvInfo
5052 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5053 IBE_UTIL.DEBUG('Log_Environment_Info: EnvInfo: Begin');
5054 END IF;
5055 open c_getEnvInfo;
5056 fetch c_getEnvInfo into rec_EnvInfo;
5057 close c_getEnvInfo;
5058 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5059 IBE_UTIL.DEBUG('Session_id='||rec_EnvInfo.session_id);
5060 IBE_UTIL.DEBUG('UserName='||rec_EnvInfo.user_name);
5061 IBE_UTIL.DEBUG('login_id='||rec_EnvInfo.login_id);
5062 IBE_UTIL.DEBUG('Client_info='||rec_EnvInfo.client_info);
5063 IBE_UTIL.DEBUG('Language='||rec_EnvInfo.lang);
5064 IBE_UTIL.DEBUG('Log_Environment_Info: EnvInfo: End');
5065 END IF;
5066
5067 -- 6. ICXSession Details
5068 /* COMMENTED OUT..NEED TO GET THE ICX SESSION ID AND RUN THE CURSOR.
5069 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5070 IBE_UTIL.DEBUG('Log_Environment_Info: ICXSessionDetails: Begin');
5071 END IF;
5072 open c_getICXSessionDetails(rec_EnvInfo.session_id);
5073 fetch c_getICXSessionDetails into rec_ICXSessionDetails;
5074 close c_getICXSessionDetails;
5075 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5076 IBE_UTIL.DEBUG('session_id='||rec_ICXSessionDetails.session_id);
5077 IBE_UTIL.DEBUG('user_id='||rec_ICXSessionDetails.user_id);
5078 IBE_UTIL.DEBUG('responsibility_id='||rec_ICXSessionDetails.responsibility_id);
5079 IBE_UTIL.DEBUG('org_id='||rec_ICXSessionDetails.org_id);
5080 IBE_UTIL.DEBUG('nls_language='||rec_ICXSessionDetails.nls_language);
5081 IBE_UTIL.DEBUG('created_by='||rec_ICXSessionDetails.created_by);
5082 IBE_UTIL.DEBUG('creation_date='||rec_ICXSessionDetails.creation_date);
5083 IBE_UTIL.DEBUG('last_updated_by='||rec_ICXSessionDetails.last_updated_by);
5084 IBE_UTIL.DEBUG('last_update_date='||rec_ICXSessionDetails.last_update_date);
5085 IBE_UTIL.DEBUG('last_update_login='||rec_ICXSessionDetails.last_update_login);
5086 IBE_UTIL.DEBUG('responsibility_application_id='||rec_ICXSessionDetails.responsibility_application_id);
5087 IBE_UTIL.DEBUG('security_group_id='||rec_ICXSessionDetails.security_group_id);
5088 IBE_UTIL.DEBUG('page_id='||rec_ICXSessionDetails.page_id);
5089 IBE_UTIL.DEBUG('login_id='||rec_ICXSessionDetails.login_id);
5090 IBE_UTIL.DEBUG('time_out='||rec_ICXSessionDetails.time_out);
5091 IBE_UTIL.DEBUG('Log_Environment_Info: ICXSessionDetails: End');
5092 END IF; */
5093
5094 -- 7.MO Temp Table details
5095 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5096 IBE_UTIL.DEBUG('Log_Environment_Info: MOTempTableDetails: Begin');
5097 END IF;
5098 open c_getMOTempTableInfo;
5099 fetch c_getMOTempTableInfo into rec_MOTempTableInfo;
5100 close c_getMOTempTableInfo;
5101 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5102 IBE_UTIL.DEBUG('organization_id='||rec_MOTempTableInfo.organization_id);
5103 IBE_UTIL.DEBUG('organization_name='||rec_MOTempTableInfo.organization_name);
5104 IBE_UTIL.DEBUG('legal_entity_id='||rec_MOTempTableInfo.legal_entity_id);
5105 IBE_UTIL.DEBUG('legal_entity_name='||rec_MOTempTableInfo.legal_entity_name);
5106 IBE_UTIL.DEBUG('Log_Environment_Info: MOTempTableDetails: End');
5107 END IF;
5108
5109 -- 8. SysContext
5110 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5111 IBE_UTIL.DEBUG('Log_Environment_Info: SysContext: Begin');
5112 END IF;
5113 open c_getSysContext;
5114 fetch c_getSysContext into rec_SysContext;
5115 close c_getSysContext;
5116 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5117 IBE_UTIL.DEBUG('Sys_Context='||rec_SysContext.sys_context);
5118 IBE_UTIL.DEBUG('Log_Environment_Info: SysContext: End');
5119 END IF;
5120
5121 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5122 IBE_UTIL.DEBUG('Log_Environment_Info: End');
5123 END IF;
5124
5125 END Log_Environment_Info;
5126
5127 FUNCTION Get_party_name (
5128 p_party_id NUMBER,
5129 p_party_type VARCHAR2
5130 )
5131 RETURN VARCHAR2
5132 IS
5133 CURSOR C1 IS
5134 select HP.party_id,HP.party_name
5135 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
5136 and HPR.subject_type = 'PERSON'
5137 and HPR.object_type = 'ORGANIZATION'
5138 and hp.party_id=HPR.subject_id;
5139 CURSOR C2 IS
5140 select party_id,party_name
5141 from hz_parties HP where party_id = p_party_id;
5142 l_party_name VARCHAR2(360);
5143 l_party_id NUMBER;
5144 BEGIN
5145 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
5146 OPEN C1;
5147 FETCH C1 INTO l_party_id,l_party_name;
5148 IF C1%NOTFOUND OR l_party_name IS NULL THEN
5149 CLOSE C1;
5150 l_party_name := NULL;
5151 RETURN l_party_name;
5152 END IF;
5153 CLOSE C1;
5154 RETURN l_party_name;
5155 ELSE
5156 OPEN C2;
5157 FETCH C2 INTO l_party_id,l_party_name;
5158 IF C2%NOTFOUND OR l_party_name IS NULL THEN
5159 CLOSE C2;
5160 l_party_name := NULL;
5161 RETURN l_party_name;
5162 END IF;
5163 CLOSE C2;
5164 RETURN l_party_name;
5165 END IF;
5166 END Get_party_name;
5167
5168 PROCEDURE Add_Attachment(
5169 p_api_version_number IN NUMBER
5170 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5171 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5172 ,p_category_id IN VARCHAR2
5173 ,p_document_description IN VARCHAR2
5174 ,p_datatype_id IN VARCHAR2
5175 ,p_text IN LONG
5176 ,p_file_name IN VARCHAR2
5177 ,p_url IN VARCHAR2
5178 ,p_function_name IN VARCHAR2 := null
5179 ,p_quote_header_id IN NUMBER
5180 ,p_media_id IN NUMBER
5181 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
5182 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
5183 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
5184 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
5185 ,p_last_update_date IN DATE :=FND_API.G_MISS_DATE
5186 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
5187 ,x_last_update_date OUT NOCOPY DATE
5188 ,x_return_status OUT NOCOPY VARCHAR2
5189 ,x_msg_count OUT NOCOPY NUMBER
5190 ,x_msg_data OUT NOCOPY VARCHAR2
5191 )
5192 IS
5193 l_api_name CONSTANT VARCHAR2(30) := 'Add_Attachment';
5194 l_api_version CONSTANT NUMBER := 1.0;
5195 l_seq_num VARCHAR2(30) := NULL;
5196
5197 BEGIN
5198 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5199 IBE_Util.Debug('Begin IBE_Quote_Misc_pvt.Add_Attachment()');
5200 END IF;
5201
5202 -- Standard Start of API savepoint
5203 SAVEPOINT Add_Attachment;
5204
5205 -- Standard call to check for call compatibility.
5206 IF NOT FND_API.Compatible_API_Call (l_api_version,
5207 P_Api_Version_Number,
5208 l_api_name,
5209 G_PKG_NAME )
5210 THEN
5211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5212 END IF;
5213
5214 -- Initialize message list if p_init_msg_list is set to TRUE.
5215 IF FND_API.To_Boolean( p_init_msg_list ) THEN
5216 FND_Msg_Pub.initialize;
5217 END IF;
5218
5219 -- Initialize API return status to success
5220 x_return_status := FND_API.G_RET_STS_SUCCESS;
5221
5222 -- User Authentication
5223 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5224 IBE_Util.Debug('Call to Validate_User_Update');
5225 END IF;
5226
5227 IBE_Quote_Misc_pvt.Validate_User_Update
5228 ( p_init_msg_list => p_Init_Msg_List
5229 ,p_quote_header_id => p_quote_header_id
5230 ,p_party_id => p_party_id
5231 ,p_cust_account_id => p_cust_account_id
5232 ,p_validate_user => p_validate_user
5233 ,p_quote_retrieval_number => p_retrieval_number
5234 ,p_save_type => p_save_type
5235 ,p_last_update_date => p_last_update_date
5236 ,x_return_status => x_return_status
5237 ,x_msg_count => x_msg_count
5238 ,x_msg_data => x_msg_data
5239 );
5240
5241 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
5242 RAISE FND_API.G_EXC_ERROR;
5243 END IF;
5244 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
5245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5246 END IF;
5247
5248
5249 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5250 IBE_Util.Debug('Call to ASO_ATTACHMENT_INT.Add_Attachment');
5251 END IF;
5252
5253 l_seq_num := to_char(FND_CRYPTO.SmallRandomNumber);
5254 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5255 IBE_Util.Debug('l_seq_num = '||l_seq_num);
5256 END IF;
5257
5258 -- ASO Attachment Procedure Call
5259 ASO_ATTACHMENT_INT.Add_Attachment
5260 (
5261 p_api_version_number => p_api_version_number
5262 ,p_init_msg_list => p_init_msg_list
5263 ,p_commit => p_commit
5264 ,p_seq_num => l_seq_num
5265 ,p_category_id => p_category_id
5266 ,p_document_description=> p_document_description
5267 ,p_datatype_id => p_datatype_id
5268 ,p_text => p_text
5269 ,p_file_name => p_file_name
5270 ,p_url => p_url
5271 ,p_function_name => p_function_name
5272 ,p_quote_header_id => p_quote_header_id
5273 ,p_media_id => p_media_id
5274 ,x_return_status => x_return_status
5275 ,x_msg_count => x_msg_count
5276 ,x_msg_data => x_msg_data
5277 );
5278 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5279 RAISE FND_API.G_EXC_ERROR;
5280 END IF;
5281
5282 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5284 END IF;
5285
5286 -- Standard call to get message count and if count is 1, get message info.
5287 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5288 p_count => x_msg_count ,
5289 p_data => x_msg_data);
5290
5291 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5292 IBE_Util.Debug('End ASO_ATTACHMENT_INT.Add_Attachment');
5293 END IF;
5294
5295 EXCEPTION
5296 WHEN FND_API.G_EXC_ERROR THEN
5297 ROLLBACK TO Add_Attachment;
5298 x_return_status := FND_API.G_RET_STS_ERROR;
5299 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5300 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5301 p_count => x_msg_count ,
5302 p_data => x_msg_data);
5303 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5304 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Add_Attachment()');
5305 END IF;
5306
5307 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5308 ROLLBACK TO Add_Attachment;
5309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5310 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5311 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5312 p_count => x_msg_count ,
5313 p_data => x_msg_data);
5314 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5315 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Add_Attachment()');
5316 END IF;
5317
5318 WHEN OTHERS THEN
5319 ROLLBACK TO Add_Attachment;
5320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5321
5322 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
5323 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
5324 l_api_name);
5325 END IF;
5326
5327 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5328 p_count => x_msg_count ,
5329 p_data => x_msg_data);
5330 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5331 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Add_Attachment()');
5332 END IF;
5333
5334 END Add_Attachment;
5335
5336
5337 PROCEDURE Delete_Attachment(
5338 p_api_version_number IN NUMBER
5339 ,p_init_msg_list IN VARCHAR2
5340 ,p_commit IN VARCHAR2
5341 ,p_quote_header_id IN NUMBER
5342 ,p_quote_attachment_ids IN JTF_VARCHAR2_TABLE_100
5343 ,p_last_update_date IN DATE
5344 ,p_party_id IN NUMBER
5345 ,p_cust_account_id IN NUMBER
5346 ,p_retrieval_number IN NUMBER
5347 ,x_last_update_date OUT NOCOPY DATE
5348 ,x_return_status OUT NOCOPY VARCHAR2
5349 ,x_msg_count OUT NOCOPY NUMBER
5350 ,x_msg_data OUT NOCOPY VARCHAR2
5351 )
5352 IS
5353 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachment';
5354 l_api_version CONSTANT NUMBER := 1.0;
5355
5356 BEGIN
5357 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5358 IBE_Util.Debug('Begin IBE_Quote_Misc_pvt.Delete_Attachment()');
5359 END IF;
5360
5361 -- Standard Start of API savepoint
5362 SAVEPOINT Add_Attachment;
5363
5364 -- Standard call to check for call compatibility.
5365 IF NOT FND_API.Compatible_API_Call (l_api_version,
5366 P_Api_Version_Number,
5367 l_api_name,
5368 G_PKG_NAME )
5369 THEN
5370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5371 END IF;
5372
5373 -- Initialize message list if p_init_msg_list is set to TRUE.
5374 IF FND_API.To_Boolean( p_init_msg_list ) THEN
5375 FND_Msg_Pub.initialize;
5376 END IF;
5377
5378 -- Initialize API return status to success
5379 x_return_status := FND_API.G_RET_STS_SUCCESS;
5380
5381 -- User Authentication
5382 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5383 IBE_Util.Debug('Call to Validate_User_Update');
5384 END IF;
5385
5386 IBE_Quote_Misc_pvt.Validate_User_Update(
5387 p_quote_header_id => p_quote_header_id
5388 ,p_party_id => p_party_id
5389 ,p_cust_account_id => p_cust_account_id
5390 ,p_validate_user => FND_API.G_TRUE
5391 ,p_quote_retrieval_number => p_retrieval_number
5392 ,p_last_update_date => p_last_update_date
5393 ,x_return_status => x_return_status
5394 ,x_msg_count => x_msg_count
5395 ,x_msg_data => x_msg_data
5396 );
5397
5398 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
5399 RAISE FND_API.G_EXC_ERROR;
5400 END IF;
5401 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
5402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5403 END IF;
5404
5405
5406 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5407 IBE_Util.Debug('Call to ASO_ATTACHMENT_INT.Delete_Attachment');
5408 END IF;
5409
5410 -- ASO Attachment Procedure Call
5411 ASO_ATTACHMENT_INT.Delete_Attachments
5412 (
5413 p_api_version_number => p_api_version_number
5414 ,p_init_msg_list => p_init_msg_list
5415 ,p_commit => p_commit
5416 ,p_quote_header_id => p_quote_header_id
5417 ,p_quote_attachment_ids => p_quote_attachment_ids
5418 ,x_return_status => x_return_status
5419 ,x_msg_count => x_msg_count
5420 ,x_msg_data => x_msg_data
5421 );
5422
5423 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5424 RAISE FND_API.G_EXC_ERROR;
5425 END IF;
5426
5427 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5429 END IF;
5430
5431 -- Standard call to get message count and if count is 1, get message info.
5432 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5433 p_count => x_msg_count ,
5434 p_data => x_msg_data);
5435
5436 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5437 IBE_Util.Debug('End ASO_ATTACHMENT_INT.Delete_Attachment');
5438 END IF;
5439
5440 EXCEPTION
5441 WHEN FND_API.G_EXC_ERROR THEN
5442 ROLLBACK TO Add_Attachment;
5443 x_return_status := FND_API.G_RET_STS_ERROR;
5444 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5445 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5446 p_count => x_msg_count ,
5447 p_data => x_msg_data);
5448 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5449 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Delete_Attachment()');
5450 END IF;
5451
5452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5453 ROLLBACK TO Add_Attachment;
5454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5455 x_last_update_date := getQuoteLastUpdateDate(p_quote_header_id);
5456 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5457 p_count => x_msg_count ,
5458 p_data => x_msg_data);
5459 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5460 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Delete_Attachment()');
5461 END IF;
5462
5463 WHEN OTHERS THEN
5464 ROLLBACK TO Add_Attachment;
5465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5466
5467 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
5468 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
5469 l_api_name);
5470 END IF;
5471
5472 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
5473 p_count => x_msg_count ,
5474 p_data => x_msg_data);
5475 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
5476 IBE_Util.Debug('End IBE_Quote_Misc_pvt.Delete_Attachment()');
5477 END IF;
5478
5479 END Delete_Attachment;
5480
5481 FUNCTION get_aso_quote_status (p_quote_header_id NUMBER) RETURN VARCHAR2 is
5482
5483 CURSOR c_quote_status_code (quote_hdr_id number) is
5484 select status_code
5485 from aso_quote_headers_all a, aso_quote_statuses_vl b
5486 where quote_header_id = quote_hdr_id
5487 and a.quote_status_id = b.quote_status_id;
5488
5489 rec_quote_status_code c_quote_status_code%rowtype;
5490 l_quote_status_code aso_quote_statuses_vl.status_code%type;
5491
5492
5493 BEGIN
5494
5495 for rec_quote_status_code in c_quote_status_code(p_quote_header_id) loop
5496 l_quote_status_code := rec_quote_status_code.status_code;
5497 exit when c_quote_status_code%notfound;
5498 end loop;
5499
5500 RETURN l_quote_status_code;
5501
5502 END get_aso_quote_status;
5503
5504 PROCEDURE get_primary_file_id(p_quote_id IN NUMBER,
5505 x_file_id OUT NOCOPY NUMBER) is
5506
5507 file_id NUMBER;
5508 BEGIN
5509
5510 IF (OKC_TERMS_UTIL_GRP.has_terms (p_document_type => 'QUOTE', p_document_id => p_quote_id) = 'Y') THEN
5511
5512 x_file_id := OKC_TERMS_UTIL_GRP.get_primary_terms_doc_file_id(p_document_type => 'QUOTE',
5513 p_document_id => p_quote_id);
5514 ELSE
5515 x_file_id := 0;
5516 END IF;
5517
5518 END get_primary_file_id;
5519
5520 END IBE_Quote_Misc_pvt;