[Home] [Help]
PACKAGE BODY: APPS.AMV_STOCK_PVT
Source
1 PACKAGE BODY amv_stock_pvt AS
2 /* $Header: amvvstkb.pls 120.1 2005/06/21 16:53:03 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_STOCK_PVT
6 --
7 -- HISTORY
8 -- 11/10/1999 SLKRISHN CREATED
9 --
10 --
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_STOCK_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvvstkb.pls';
13 --
14 TYPE CursorType IS REF CURSOR;
15 ----------------------------- Private Portion ---------------------------------
16 --------------------------------------------------------------------------------
17 -- We use the following private utility procedures
18 --------------------------------------------------------------------------------
19 --
20 PROCEDURE Update_Symbols
21 (
22 p_user_id IN NUMBER,
23 p_symbols_array IN amv_char_varray_type
24 );
25 --
26 PROCEDURE Get_UserSymbols
27 (
28 p_user_id IN NUMBER,
29 x_symbol_array OUT NOCOPY AMV_SYM_VARRAY_TYPE
30 );
31 --
32 FUNCTION Is_UserIdValid
33 (
34 p_user_id IN NUMBER
35 ) RETURN Boolean;
36 --
37 PROCEDURE Parse_Symbols (p_symbols IN varchar2,
38 x_symbols_array OUT NOCOPY amv_char_varray_type );
39 --
40 PROCEDURE Parse_Stock ( p_symbol IN VARCHAR2,
41 x_symbol OUT NOCOPY VARCHAR2,
42 x_type OUT NOCOPY VARCHAR2 );
43 --
44 PROCEDURE Add_UserStocks
45 (
46 p_user_id IN NUMBER,
47 p_symbols_array IN AMV_CHAR_VARRAY_TYPE,
48 x_return_status OUT NOCOPY VARCHAR2
49 );
50 --
51 PROCEDURE Delete_UserStocks
52 (
53 p_tickerid_array IN AMV_NUM_VARRAY_TYPE
54 );
55 --
56 FUNCTION Get_SBPId RETURN NUMBER;
57 --
58 --------------------------------------------------------------------------------
59 --
60 -- Start of comments
61 -- API name : Update_Symbols
62 -- Type : Private
63 -- Pre-reqs : None
64 -- Function : update user tickers last update date for getting them back in the order stored
65 -- Parameters :
66 -- IN : p_user_id IN NUMBER Required
67 -- IN : p_symbols_array IN AMV_CHAR_VARRAY_TYPE
68 -- Version : Current version 1.0
69 -- Previous version 1.0
70 -- Initial version 1.0
71 -- Notes :
72 -- End of comments
73 --
74 PROCEDURE Update_Symbols
75 (
76 p_user_id IN NUMBER,
77 p_symbols_array IN amv_char_varray_type
78 )
79 IS
80 l_stock_id number;
81 l_symbol varchar2(20);
82 l_null varchar2(30) := null;
83 l_rec_num number := 0;
84
85 CURSOR Get_StockId IS
86 select stock_id
87 from amv_stocks
88 where stock_symbol = l_symbol;
89 --
90 BEGIN
91 FOR i in 1..p_symbols_array.count LOOP
92 --
93 l_symbol := p_symbols_array(i);
94 OPEN Get_StockId;
95 FETCH Get_StockId INTO l_stock_id;
96 CLOSE Get_StockId;
97
98 UPDATE amv_user_ticker
99 SET last_update_date = sysdate + i
100 WHERE user_id = p_user_id
101 AND stock_id = l_stock_id;
102 --
103 END LOOP;
104 --
105 END Update_Symbols;
106 --
107 --------------------------------------------------------------------------------
108 --
109 -- Start of comments
110 -- API name : Get_UserSymbols
111 -- Type : Private
112 -- Pre-reqs : None
113 -- Function : returns an array of stocks for an user.
114 -- Parameters :
115 -- IN : p_user_id IN NUMBER Required
116 -- OUT : x_symbol_array OUT AMV_CHAR_VARRAY_TYPE
117 -- Version : Current version 1.0
118 -- Previous version 1.0
119 -- Initial version 1.0
120 -- Notes :
121 -- End of comments
122 --
123 PROCEDURE Get_UserSymbols
124 (
125 p_user_id IN NUMBER,
126 x_symbol_array OUT NOCOPY AMV_SYM_VARRAY_TYPE
127 )
128 IS
129 l_stock_id number;
130 l_symbol varchar2(20);
131 l_null varchar2(30) := null;
132 l_rec_num number := 0;
133 --
134 CURSOR Get_UserSymbols_csr IS
135 select amut.user_ticker_id
136 , amst.stock_symbol
137 , l_null
138 from amv_user_ticker amut
139 , amv_stocks amst
140 where amut.user_id = p_user_id
141 and amut.stock_id = amst.stock_id;
142
143 BEGIN
144 IF p_user_id = FND_API.G_MISS_NUM OR
145 p_user_id IS NULL THEN
146 l_rec_num := null;
147 --RAISE;
148 ELSE
149 OPEN Get_UserSymbols_csr;
150 LOOP
151 l_rec_num := l_rec_num + 1;
152 FETCH Get_UserSymbols_csr INTO x_symbol_array(l_rec_num);
153 EXIT WHEN Get_UserSymbols_csr%NOTFOUND;
154 END LOOP;
155 CLOSE Get_UserSymbols_csr;
156 --
157 END IF;
158 --
159 END Get_UserSymbols;
160 --
161 --------------------------------------------------------------------------------
162 --
163 -- Start of comments
164 -- API name : Is_UserIdValid
165 -- Type : Private
166 -- Pre-reqs : None
167 -- Function : checks user id
168 -- Parameters :
169 -- IN : p_user_id IN NUMBER Required
170 -- Version : Current version 1.0
171 -- Previous version 1.0
172 -- Initial version 1.0
173 -- Notes :
174 -- End of comments
175 --
176 FUNCTION Is_UserIdValid
177 (
178 p_user_id IN NUMBER
179 ) RETURN Boolean
180 IS
181 l_valid_flag number;
182 --
183 CURSOR Check_UserId_csr IS
184 select count(*)
185 from fnd_user
186 where user_id = p_user_id;
187 BEGIN
188 OPEN Check_UserId_csr;
189 FETCH Check_UserId_csr INTO l_valid_flag;
190 CLOSE Check_UserId_csr;
191 IF l_valid_flag > 0 THEN
192 return TRUE;
193 ELSE
194 return FALSE;
195 END IF;
196 END Is_UserIdValid;
197 --
198 --------------------------------------------------------------------------------
199 --
200 -- Start of comments
201 -- API name : Parse_Symbols
202 -- Type : Private
203 -- Pre-reqs : None
204 -- Function : parses stock symbols into a varchar2 table
205 -- Parameters :
206 -- IN : p_symbols IN VARCHAR2 Required
207 -- : x_symbols_array OUT AMV_CHAR_VARRAY_TYPE
208 -- Version : Current version 1.0
209 -- Previous version 1.0
210 -- Initial version 1.0
211 -- Notes :
212 -- End of comments
213 --
214 PROCEDURE Parse_Symbols (p_symbols IN varchar2,
215 x_symbols_array OUT NOCOPY amv_char_varray_type )
216 IS
217 l_ins number := 1;
218 l_str number := 1;
219 l_sym_num number := 1;
220 l_rec_num number := 1;
221 l_symbol varchar2(20);
222 l_err_msg varchar2(100);
223 BEGIN
224 -- build symbols array
225 while l_ins > 0 loop
226 l_ins := instr(p_symbols, ',', 1, l_sym_num);
227
228 if l_ins = 0 then
229 l_symbol := substr(p_symbols, l_str);
230 else
231 l_symbol := substr(p_symbols, l_str, l_ins - l_str );
232 end if;
233
234 IF rtrim(l_symbol) is not null THEN
235 x_symbols_array(l_rec_num) := upper(l_symbol);
236 l_rec_num := l_rec_num + 1;
237 END IF;
238 l_sym_num := l_sym_num + 1;
239 l_str := l_ins + 1;
240 end loop;
241 --
242 EXCEPTION
243 WHEN OTHERS THEN
244 l_err_msg := 'Error_Parsing_Symbol';
245 END Parse_Symbols;
246 --------------------------------------------------------------------------------
247 --
248 -- Start of comments
249 -- API name : Parse_Stock
250 -- Type : Private
251 -- Pre-reqs : None
252 -- Function : parses stock symbol to remove foreign stock identifier
253 -- Parameters :
254 -- IN : p_symbol IN VARCHAR2 Required
255 -- : x_symbol OUT NOCOPY VARCHAR2
256 -- x_type OUT NOCOPY VARCHAR2
257 -- Version : Current version 1.0
258 -- Previous version 1.0
259 -- Initial version 1.0
260 -- Notes :
261 -- End of comments
262 --
263 PROCEDURE Parse_Stock ( p_symbol IN VARCHAR2,
264 x_symbol OUT NOCOPY VARCHAR2,
265 x_type OUT NOCOPY VARCHAR2 )
266 IS
267 l_ins number := 1;
268 l_sym_num number := 1;
269 l_err_msg varchar2(100);
270 BEGIN
271 -- parse stock symbol
272 while l_ins > 0 loop
273 l_ins := instr(p_symbol, '.', 1, l_sym_num);
274 if l_ins = 0 then
275 if l_sym_num = 1 then
276 x_symbol := p_symbol;
277 x_type := null;
278 end if;
279 else
280 x_symbol := substr(p_symbol, 1, l_ins-1);
281 x_type := substr(p_symbol, l_ins + 1);
282 end if;
283 l_sym_num := l_sym_num + 1;
284 end loop;
285 --
286 EXCEPTION
287 WHEN OTHERS THEN
288 l_err_msg := 'Error_Parsing_Stock';
289 END Parse_Stock;
290 --------------------------------------------------------------------------------
291 --
292 -- Start of comments
293 -- API name : Add_UserStocks
294 -- Type : Private
295 -- Pre-reqs : None
296 -- Function : adds stcoks to user list
297 -- Parameters :
298 -- IN : p_user_id IN NUMBER Required
299 -- : p_symbol_array IN AMV_CHAR_VARRAY_TYPE
300 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
301 -- Version : Current version 1.0
302 -- Previous version 1.0
303 -- Initial version 1.0
304 -- Notes :
305 -- End of comments
306 --
307 PROCEDURE Add_UserStocks
308 (
309 p_user_id IN NUMBER,
310 p_symbols_array IN AMV_CHAR_VARRAY_TYPE,
311 x_return_status OUT NOCOPY VARCHAR2
312 )
313 IS
314 l_user_id number := p_user_id;
315 l_login_user_id number := p_user_id;
316 l_object_version_number number := 1;
317 --
318 l_symbol varchar2(20);
319 l_stock_id number;
320 l_user_ticker_id number;
321 l_miss_rec number := 0;
322
323 CURSOR Get_StockId IS
324 select stock_id
325 from amv_stocks
326 where stock_symbol = l_symbol;
327
328 CURSOR UserTicker_seq IS
329 SELECT amv_user_ticker_s.nextval
330 FROM dual;
331
332 BEGIN
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334 FOR i in 1..p_symbols_array.count LOOP
335 l_symbol := p_symbols_array(i);
336 -- get stock id's
337 OPEN Get_StockId;
338 FETCH Get_StockId INTO l_stock_id;
339 CLOSE Get_StockId;
340
341 -- insert stock ticker if stock id is not null
342 IF l_stock_id is not null THEN
343 OPEN UserTicker_seq;
344 FETCH UserTicker_seq INTO l_user_ticker_id;
345 CLOSE UserTicker_seq;
346 --
347 BEGIN
348 INSERT INTO amv_user_ticker
349 (
350 user_ticker_id,
351 object_version_number,
352 last_update_date,
353 last_updated_by,
354 creation_date,
355 created_by,
356 last_update_login,
357 user_id,
358 stock_id
359 )
360 VALUES
361 (
362 l_user_ticker_id,
363 l_object_version_number,
364 sysdate,
365 l_user_id,
366 sysdate,
367 l_user_id,
368 l_login_user_id,
369 p_user_id,
370 l_stock_id
371 );
372 EXCEPTION
373 WHEN OTHERS THEN
374 l_miss_rec := l_miss_rec + 1;
375 END;
376 --
377 l_stock_id := null;
378 ELSE
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 END IF;
381 END LOOP;
382 --
383
384 END Add_UserStocks;
385 --
386 --------------------------------------------------------------------------------
387 --
388 -- Start of comments
389 -- API name : Delete_UserStocks
390 -- Type : Private
391 -- Pre-reqs : None
392 -- Function : deletes stcoks from user list
393 -- Parameters :
394 -- IN : p_user_id IN NUMBER Required
395 -- : p_symbol_array IN AMV_CHAR_VARRAY_TYPE
396 -- Version : Current version 1.0
397 -- Previous version 1.0
398 -- Initial version 1.0
399 -- Notes :
400 -- End of comments
401 --
402 PROCEDURE Delete_UserStocks
403 (
404 p_tickerid_array IN AMV_NUM_VARRAY_TYPE
405 )
406 IS
407 BEGIN
408 --
409 FOR i in 1..p_tickerid_array.count LOOP
410 DELETE FROM amv_user_ticker
411 where user_ticker_id = p_tickerid_array(i);
412 END LOOP;
413 --
414 END Delete_UserStocks;
415 --
416 --------------------------------------------------------------------------------
417 --
418 -- Start of comments
419 -- API name : Get_SBPId
420 -- Type : Private
421 -- Pre-reqs : None
422 -- Function : returns stand alone batch process id
423 -- Parameters :
424 -- Version : Current version 1.0
425 -- Previous version 1.0
426 -- Initial version 1.0
427 -- Notes :
428 -- End of comments
429 --
430 FUNCTION Get_SBPId RETURN NUMBER
431 IS
432 l_id number;
433 CURSOR Get_SBPId_csr IS
434 select user_id
435 from fnd_user
436 where user_name = 'STANDALONE BATCH PROCESS';
437
438 BEGIN
439 --
440 OPEN Get_SBPId_csr;
441 FETCH Get_SBPId_csr INTO l_id;
442 IF Get_SBPId_csr%NOTFOUND THEN
443 l_id := -1;
444 END IF;
445 CLOSE Get_SBPId_csr;
446 --
447 return l_id;
448 --
449 END Get_SBPId;
450 --
451 --------------------------------------------------------------------------------
452 --------------------------------------------------------------------------------
453 -- Start of comments
454 -- API name : Get_UserTicker
455 -- Type : Private
456 -- Pre-reqs : None
457 -- Function : Returns the list of tickers for an user
458 -- Parameters :
459 -- IN p_api_version IN NUMBER Required
460 -- p_init_msg_list IN VARCHAR2 Optional
461 -- Default = FND_API.G_FALSE
462 -- p_validation_level IN NUMBER Optional
463 -- Default = FND_API.G_VALID_LEVEL_FULL
464 -- p_user_id IN NUMBER Required
465 -- p_sort_order IN VARCHAR2 Optional
466 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
467 -- x_msg_count OUT NOCOPY NUMBER
468 -- x_msg_data OUT NOCOPY VARCHAR2
469 -- x_ticker_array OUT NOCOPY AMV_STK_VARRAY_TYPE
470 -- Version : Current version 1.0
471 -- Previous version 1.0
472 -- Initial version 1.0
473 -- Notes :
474 --
475 -- End of comments
476 --
477 PROCEDURE Get_UserTicker
478 ( p_api_version IN NUMBER,
479 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
480 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
481 x_return_status OUT NOCOPY VARCHAR2,
482 x_msg_count OUT NOCOPY NUMBER,
483 x_msg_data OUT NOCOPY VARCHAR2,
484 p_user_id IN NUMBER,
488 )
485 p_distinct_stocks IN VARCHAR2 := FND_API.G_FALSE,
486 p_sort_order IN VARCHAR2 := FND_API.G_MISS_CHAR,
487 x_stkpr_array OUT NOCOPY AMV_STK_VARRAY_TYPE
489 IS
490 l_api_name CONSTANT VARCHAR2(30) := 'Get_UserTicker';
491 l_api_version CONSTANT NUMBER := 1.0;
492 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
493 --
494 l_user_id number := -1;
495 l_login_user_id number := -1;
496 l_login_user_status varchar2(30);
497 l_Error_Msg varchar2(2000);
498 l_Error_Token varchar2(80);
499 --
500 l_stock_id number;
501 l_stock_symbol varchar2(30);
502 l_last_update_date date;
503 l_symbol varchar2(20);
504 l_desc varchar2(240);
505 l_price number;
506 l_change number;
507 l_rec_num number := 0;
508
509 l_select_stmt varchar2(200);
510 l_from_stmt varchar2(150);
511 l_where_clause varchar2(200);
512 l_order_by varchar2(50);
513 l_sql_statement varchar2(600);
514
515 l_cursor CursorType;
516 --
517 CURSOR Get_StockData_csr IS
518 select amst.stock_id
519 , amst.stock_symbol
520 , amst.stock_desc
521 , amsp.stock_price
522 , amsp.change
523 from amv_stocks amst
524 , amv_stock_price amsp
525 where amst.stock_ric = amsp.stock_ric(+)
526 and amst.stock_id = l_stock_id
527 order by amsp.time_stamp desc;
528
529 CURSOR Get_DistinctUserStocks_csr IS
530 select distinct amst.stock_symbol, amut.stock_id
531 from amv_user_ticker amut
532 , amv_stocks amst
533 where amut.user_id = p_user_id
534 and amut.stock_id = amst.stock_id
535 order by amst.stock_symbol ASC;
536 --order by l_order_by;
537
538 CURSOR Get_UserStocks_csr IS
539 select amut.stock_id
540 from amv_user_ticker amut
541 , amv_stocks amst
542 where amut.user_id = p_user_id
543 and amut.stock_id = amst.stock_id
544 order by amst.stock_symbol ASC;
545 --order by l_order_by;
546 --
547 BEGIN
548 -- Standard begin of API savepoint
549 SAVEPOINT Get_UserTicker;
550 -- Standard call to check for call compatibility.
551 IF NOT FND_API.Compatible_API_Call (
552 l_api_version,
553 p_api_version,
554 l_api_name,
555 G_PKG_NAME)
556 THEN
557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
558 END IF;
559 -- Debug Message
560 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
561 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
562 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
563 FND_MSG_PUB.Add;
564 END IF;
565 --Initialize message list if p_init_msg_list is TRUE.
566 IF FND_API.To_Boolean (p_init_msg_list) THEN
567 FND_MSG_PUB.initialize;
568 END IF;
569 -- Initialize API return status to sucess
570 x_return_status := FND_API.G_RET_STS_SUCCESS;
571
572 --
573 --IF Is_UserIdValid(p_user_id) THEN
574 --
575 IF p_distinct_stocks = FND_API.G_FALSE THEN
576 l_select_stmt := 'SELECT amst.stock_symbol, amut.stock_id, amut.last_update_date ';
577 ELSE
578 l_select_stmt := 'SELECT distinct amst.stock_symbol, amut.stock_id, amut.last_update_date ';
579 END IF;
580
581 l_from_stmt := 'FROM amv_user_ticker amut, amv_stocks amst ';
582 l_where_clause := 'WHERE amut.user_id = :user_id ';
583 l_where_clause := l_where_clause || 'AND amut.stock_id = amst.stock_id ';
584
585 IF p_sort_order = FND_API.G_MISS_CHAR OR
586 p_sort_order IS NULL
587 THEN
588 l_order_by := 'ORDER BY amut.last_update_date ASC';
589 ELSE
590 l_order_by := 'ORDER BY amst.stock_symbol ' || p_sort_order;
591 END IF;
592
593 l_sql_statement := l_select_stmt || l_from_stmt || l_where_clause || l_order_by;
594
595 OPEN l_cursor FOR l_sql_statement USING p_user_id;
596 LOOP
597 FETCH l_cursor INTO l_stock_symbol, l_stock_id, l_last_update_date;
598 EXIT WHEN l_cursor%NOTFOUND;
599 IF l_stock_id is not null THEN
600 OPEN Get_StockData_csr;
601 l_rec_num := l_rec_num + 1;
602 FETCH Get_StockData_csr INTO x_stkpr_array(l_rec_num);
603 CLOSE Get_StockData_csr;
604 l_stock_id := null;
605 END IF;
606 END LOOP;
607 CLOSE l_cursor;
608
609 /*
610 IF p_distinct_stocks = FND_API.G_FALSE THEN
611 OPEN Get_UserStocks_csr;
612 LOOP
613 FETCH Get_UserStocks_csr INTO l_stock_id;
614 EXIT WHEN Get_UserStocks_csr%NOTFOUND;
615 IF l_stock_id is not null THEN
616 OPEN Get_StockData_csr;
617 l_rec_num := l_rec_num + 1;
618 FETCH Get_StockData_csr INTO x_stkpr_array(l_rec_num);
619 CLOSE Get_StockData_csr;
620 l_stock_id := null;
621 END IF;
622 END LOOP;
623 CLOSE Get_UserStocks_csr;
624 ELSE
625 OPEN Get_DistinctUserStocks_csr;
626 LOOP
627 FETCH Get_DistinctUserStocks_csr INTO l_stock_symbol, l_stock_id;
628 EXIT WHEN Get_DistinctUserStocks_csr%NOTFOUND;
629 IF l_stock_id is not null THEN
630 OPEN Get_StockData_csr;
631 l_rec_num := l_rec_num + 1;
632 FETCH Get_StockData_csr INTO x_stkpr_array(l_rec_num);
636 END LOOP;
633 CLOSE Get_StockData_csr;
634 l_stock_id := null;
635 END IF;
637 CLOSE Get_DistinctUserStocks_csr;
638 END IF;
639 */
640 --
641 --ELSE
642 -- -- User Id is not valid.
643 -- IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
644 -- FND_MESSAGE.Set_name('AMV','AMV_USER_ID_INVALID');
645 -- FND_MESSAGE.Set_Token('TKN',p_user_id);
646 -- FND_MSG_PUB.Add;
647 -- END IF;
648 -- RAISE FND_API.G_EXC_ERROR;
649 -- END IF;
650 --
651
652 -- Success message
653 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
654 THEN
655 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
656 FND_MESSAGE.Set_Token('ROW', l_full_name);
657 FND_MSG_PUB.Add;
658 END IF;
659 -- Debug Message
660 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
661 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
662 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
663 FND_MSG_PUB.Add;
664 END IF;
665 --Standard call to get message count and if count=1, get the message
666 FND_MSG_PUB.Count_And_Get (
667 p_encoded => FND_API.G_FALSE,
668 p_count => x_msg_count,
669 p_data => x_msg_data
670 );
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 ROLLBACK TO Get_UserTicker;
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 -- Standard call to get message count and if count=1, get the message
676 FND_MSG_PUB.Count_And_Get (
677 p_encoded => FND_API.G_FALSE,
678 p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682 ROLLBACK TO Get_UserTicker;
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 -- Standard call to get message count and if count=1, get the message
685 FND_MSG_PUB.Count_And_Get (
686 p_encoded => FND_API.G_FALSE,
687 p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690 WHEN OTHERS THEN
691 ROLLBACK TO Get_UserTicker;
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
694 THEN
695 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
696 END IF;
697 -- Standard call to get message count and if count=1, get the message
698 FND_MSG_PUB.Count_And_Get (
699 p_encoded => FND_API.G_FALSE,
700 p_count => x_msg_count,
701 p_data => x_msg_data
702 );
703 --
704 END Get_UserTicker;
705 --------------------------------------------------------------------------------
706 --------------------------------------------------------------------------------
707 -- Start of comments
708 -- API name : Get_StockDetails
709 -- Type : Private
710 -- Pre-reqs : None
711 -- Function : Returns the ticker details for list of stocks
712 -- Parameters :
713 -- IN p_api_version IN NUMBER Required
714 -- p_init_msg_list IN VARCHAR2 Optional
715 -- Default = FND_API.G_FALSE
716 -- p_validation_level IN NUMBER Optional
717 -- Default = FND_API.G_VALID_LEVEL_FULL
718 -- p_symbols IN VARCHAR2 Required
719 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
720 -- x_msg_count OUT NOCOPY NUMBER
721 -- x_msg_data OUT NOCOPY VARCHAR2
722 -- x_ticker_array OUT NOCOPY AMV_STK_VARRAY_TYPE
723 -- Version : Current version 1.0
724 -- Previous version 1.0
725 -- Initial version 1.0
726 -- Notes :
727 --
728 -- End of comments
729 --
730 PROCEDURE Get_StockDetails
731 ( p_api_version IN NUMBER,
732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
733 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
734 x_return_status OUT NOCOPY VARCHAR2,
735 x_msg_count OUT NOCOPY NUMBER,
736 x_msg_data OUT NOCOPY VARCHAR2,
737 p_symbols IN VARCHAR2,
738 x_stkpr_array OUT NOCOPY AMV_STK_VARRAY_TYPE
739 )
740 IS
741 l_api_name CONSTANT VARCHAR2(30) := 'Get_StockDetails';
742 l_api_version CONSTANT NUMBER := 1.0;
743 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
744 --
745 l_user_id number := -1;
746 l_login_user_id number := -1;
747 l_login_user_status varchar2(30);
748 l_Error_Msg varchar2(2000);
749 l_Error_Token varchar2(80);
750 --
751 l_stock_id number;
752 l_symbol varchar2(20);
753 l_description varchar2(240);
754 l_price number;
755 l_change number;
756 l_symbols_array amv_char_varray_type;
757 l_rec_num number := 0;
758 l_stock_symbol varchar2(30);
759 --
760 CURSOR Get_Stocks_csr IS
761 select amst.stock_id
762 , amst.stock_symbol
766 from amv_stocks amst
763 , amst.stock_desc
764 , amsp.stock_price
765 , amsp.change
767 , amv_stock_price amsp
768 where amst.stock_symbol = l_stock_symbol
769 and amst.stock_ric = amsp.stock_ric(+)
770 order by amsp.time_stamp desc;
771 --
772 BEGIN
773 -- Standard begin of API savepoint
774 SAVEPOINT Get_StockDetails;
775 -- Standard call to check for call compatibility.
776 IF NOT FND_API.Compatible_API_Call (
777 l_api_version,
778 p_api_version,
779 l_api_name,
780 G_PKG_NAME)
781 THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784 -- Debug Message
785 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
786 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
787 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
788 FND_MSG_PUB.Add;
789 END IF;
790 --Initialize message list if p_init_msg_list is TRUE.
791 IF FND_API.To_Boolean (p_init_msg_list) THEN
792 FND_MSG_PUB.initialize;
793 END IF;
794 -- Initialize API return status to sucess
795 x_return_status := FND_API.G_RET_STS_SUCCESS;
796
797 --
798 -- parse symbols into a table
799 Parse_Symbols(p_symbols, l_symbols_array);
800
801 --
802 FOR i in 1..l_symbols_array.count LOOP
803 l_stock_symbol := l_symbols_array(i);
804 l_stock_id := null;
805 IF l_stock_symbol is not null THEN
806 OPEN Get_Stocks_csr;
807 FETCH Get_Stocks_csr INTO l_stock_id, l_symbol,
808 l_description, l_price, l_change;
809 IF l_stock_id is not null then
810 l_rec_num := l_rec_num + 1;
811 x_stkpr_array(l_rec_num).stock_id := l_stock_id;
812 x_stkpr_array(l_rec_num).symbol := l_symbol;
813 x_stkpr_array(l_rec_num).description := l_description;
814 x_stkpr_array(l_rec_num).price := l_price;
815 x_stkpr_array(l_rec_num).change := l_change;
816 END IF;
817 CLOSE Get_Stocks_csr;
818 END IF;
819 END LOOP;
820 --
821
822 -- Success message
823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
824 THEN
825 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
826 FND_MESSAGE.Set_Token('ROW', l_full_name);
827 FND_MSG_PUB.Add;
828 END IF;
829 -- Debug Message
830 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
831 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
832 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
833 FND_MSG_PUB.Add;
834 END IF;
835 --Standard call to get message count and if count=1, get the message
836 FND_MSG_PUB.Count_And_Get (
837 p_encoded => FND_API.G_FALSE,
838 p_count => x_msg_count,
839 p_data => x_msg_data
840 );
841 EXCEPTION
842 WHEN FND_API.G_EXC_ERROR THEN
843 ROLLBACK TO Get_StockDetails;
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 -- Standard call to get message count and if count=1, get the message
846 FND_MSG_PUB.Count_And_Get (
847 p_encoded => FND_API.G_FALSE,
848 p_count => x_msg_count,
849 p_data => x_msg_data
850 );
851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
852 ROLLBACK TO Get_StockDetails;
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 -- Standard call to get message count and if count=1, get the message
855 FND_MSG_PUB.Count_And_Get (
856 p_encoded => FND_API.G_FALSE,
857 p_count => x_msg_count,
858 p_data => x_msg_data
859 );
860 WHEN OTHERS THEN
861 ROLLBACK TO Get_StockDetails;
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
864 THEN
865 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
866 END IF;
867 -- Standard call to get message count and if count=1, get the message
868 FND_MSG_PUB.Count_And_Get (
869 p_encoded => FND_API.G_FALSE,
870 p_count => x_msg_count,
871 p_data => x_msg_data
872 );
873 --
874 END Get_StockDetails;
875 --------------------------------------------------------------------------------
876 --------------------------------------------------------------------------------
877 -- Start of comments
878 -- API name : Update_UserTicker
879 -- Type : Private
880 -- Pre-reqs : None
881 -- Function : Update the tickers for an user
882 -- Parameters :
883 -- IN p_api_version IN NUMBER Required
884 -- p_init_msg_list IN VARCHAR2 Optional
885 -- Default = FND_API.G_FALSE
886 -- p_commit IN VARCHAR2 Optional
887 -- Default = FND_API.G_FALSE
888 -- p_validation_level IN NUMBER Optional
889 -- Default = FND_API.G_VALID_LEVEL_FULL
890 -- p_object_version_number IN NUMBER Required
891 -- object version number
892 -- p_user_id IN NUMBER Required
893 -- user id.
897 -- x_msg_count OUT NOCOPY NUMBER
894 -- p_symbols IN VARCHAR2 Required
895 -- stock symbols.
896 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
898 -- x_msg_data OUT NOCOPY VARCHAR2
899 -- Version : Current version 1.0
900 -- Previous version 1.0
901 -- Initial version 1.0
902 -- Notes :
903 --
904 -- End of comments
905 --
906 PROCEDURE Update_UserTicker
907 ( p_api_version IN NUMBER,
908 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
909 p_commit IN VARCHAR2 := FND_API.G_FALSE,
910 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
911 x_return_status OUT NOCOPY VARCHAR2,
912 x_msg_count OUT NOCOPY NUMBER,
913 x_msg_data OUT NOCOPY VARCHAR2,
914 p_object_version_number IN NUMBER,
915 p_user_id IN NUMBER,
916 p_symbols IN VARCHAR2
917 )
918 IS
919 l_api_name CONSTANT VARCHAR2(30) := 'Update_UserTicker';
920 l_api_version CONSTANT NUMBER := 1.0;
921 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
922 --
923 l_user_id number := p_user_id;
924 l_login_user_id number := p_user_id;
925 l_login_user_status varchar2(30);
926 l_Error_Msg varchar2(2000);
927 l_Error_Token varchar2(80);
928 --
929 l_symbols_array amv_char_varray_type;
930 l_user_symbols amv_sym_varray_type;
931 l_add_symbols amv_char_varray_type;
932 l_del_symbols amv_num_varray_type;
933 l_upd_symbols amv_num_varray_type;
934 l_stock_id number;
935 l_rec_num number := 0;
936 l_upd_num number := 0;
937 l_flag varchar2(20);
938 --
939 BEGIN
940 -- Standard begin of API savepoint
941 SAVEPOINT Update_UserTicker;
942 -- Standard call to check for call compatibility.
943 IF NOT FND_API.Compatible_API_Call (
944 l_api_version,
945 p_api_version,
946 l_api_name,
947 G_PKG_NAME)
948 THEN
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 END IF;
951 -- Debug Message
952 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
953 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
954 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
955 FND_MSG_PUB.Add;
956 END IF;
957 --Initialize message list if p_init_msg_list is TRUE.
958 IF FND_API.To_Boolean (p_init_msg_list) THEN
959 FND_MSG_PUB.initialize;
960 END IF;
961 -- Initialize API return status to sucess
962 x_return_status := FND_API.G_RET_STS_SUCCESS;
963
964 --
965 --IF Is_UserIdValid(p_user_id) THEN
966 --
967 -- parse user tickers to an varchar2 table
968 Parse_Symbols( p_symbols => p_symbols,
969 x_symbols_array => l_symbols_array);
970
971 -- get the existing user stocks
972 Get_UserSymbols( p_user_id, l_user_symbols);
973
974 -- build the list of new symbols
975 l_flag := 'ADDED';
976 FOR i in 1..l_symbols_array.count LOOP
977 FOR j in 1..l_user_symbols.count LOOP
978 IF l_user_symbols(j).exchange is null THEN
979 IF l_symbols_array(i) = l_user_symbols(j).symbol THEN
980 l_flag := 'EXISTS';
981 l_stock_id := l_user_symbols(j).stock_id;
982 l_user_symbols(j).exchange := 'T';
983 ELSE
984 l_flag := 'ADDED';
985 END IF;
986 EXIT WHEN l_symbols_array(i)=l_user_symbols(j).symbol;
987 END IF;
988 END LOOP;
989 IF l_flag = 'EXISTS' THEN
990 l_upd_num := l_upd_num + 1;
991 l_upd_symbols(l_upd_num) := l_stock_id;
992 l_flag := 'ADDED';
993 ELSIF l_flag = 'ADDED' THEN
994 l_rec_num := l_rec_num + 1;
995 l_add_symbols(l_rec_num) := l_symbols_array(i);
996 END IF;
997 END LOOP;
998
999 -- initialize rec num
1000 l_rec_num := 0;
1001
1002 -- build the list of deleted symbols
1003 FOR i in 1..l_user_symbols.count LOOP
1004 IF l_user_symbols(i).exchange is null THEN
1005 l_rec_num := l_rec_num + 1;
1006 l_del_symbols(l_rec_num) := l_user_symbols(i).stock_id;
1007 END IF;
1008 END LOOP;
1009
1010 IF l_add_symbols.count > 0 THEN
1011 Add_UserStocks(
1012 p_user_id => p_user_id,
1013 p_symbols_array => l_add_symbols,
1014 x_return_status => x_return_status
1015 );
1016 END IF;
1017
1018 IF l_del_symbols.count > 0 THEN
1019 Delete_UserStocks( p_tickerid_array => l_del_symbols );
1020 END IF;
1021
1022 -- update the last update date to get stocks based on the order stores by user
1023 Update_Symbols( p_user_id, l_symbols_array);
1024
1025 --ELSE
1026 -- -- User Id is not valid.
1027 -- IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1028 -- FND_MESSAGE.Set_name('AMV','AMV_USER_ID_INVALID');
1029 -- FND_MESSAGE.Set_Token('TKN',p_user_id);
1030 -- FND_MSG_PUB.Add;
1031 -- END IF;
1032 -- RAISE FND_API.G_EXC_ERROR;
1033 --END IF;
1034 --
1035
1036 -- Success message
1040 FND_MESSAGE.Set_Token('ROW', l_full_name);
1037 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1038 THEN
1039 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1041 FND_MSG_PUB.Add;
1042 END IF;
1043 --Standard check of commit
1044 IF FND_API.To_Boolean ( p_commit ) THEN
1045 COMMIT WORK;
1046 END IF;
1047 -- Debug Message
1048 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1049 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1050 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1051 FND_MSG_PUB.Add;
1052 END IF;
1053 --Standard call to get message count and if count=1, get the message
1054 FND_MSG_PUB.Count_And_Get (
1055 p_encoded => FND_API.G_FALSE,
1056 p_count => x_msg_count,
1057 p_data => x_msg_data
1058 );
1059 EXCEPTION
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 ROLLBACK TO Update_UserTicker;
1062 x_return_status := FND_API.G_RET_STS_ERROR;
1063 -- Standard call to get message count and if count=1, get the message
1064 FND_MSG_PUB.Count_And_Get (
1065 p_encoded => FND_API.G_FALSE,
1066 p_count => x_msg_count,
1067 p_data => x_msg_data
1068 );
1069 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1070 ROLLBACK TO Update_UserTicker;
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1072 -- Standard call to get message count and if count=1, get the message
1073 FND_MSG_PUB.Count_And_Get (
1074 p_encoded => FND_API.G_FALSE,
1075 p_count => x_msg_count,
1076 p_data => x_msg_data
1077 );
1078 WHEN OTHERS THEN
1079 ROLLBACK TO Update_UserTicker;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1082 THEN
1083 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1084 END IF;
1085 -- Standard call to get message count and if count=1, get the message
1086 FND_MSG_PUB.Count_And_Get (
1087 p_encoded => FND_API.G_FALSE,
1088 p_count => x_msg_count,
1089 p_data => x_msg_data
1090 );
1091 --
1092 END Update_UserTicker;
1093 --------------------------------------------------------------------------------
1094 --------------------------------------------------------------------------------
1095 -- Start of comments
1096 -- API name : Get_VendorMissedStocks
1097 -- Type : Private
1098 -- Pre-reqs : None
1099 -- Function : Gets the list of stocks for which the vendor keys have not
1100 -- been identified
1101 -- Parameters :
1102 -- IN p_api_version IN NUMBER Required
1103 -- p_init_msg_list IN VARCHAR2 Optional
1104 -- Default = FND_API.G_FALSE
1105 -- p_validation_level IN NUMBER Optional
1106 -- Default = FND_API.G_VALID_LEVEL_FULL
1107 -- p_vendor_id IN NUMBER Required
1108 -- vendor id.
1109 -- p_start_index IN NUMBER Optional
1110 -- Default = 1
1111 -- p_batch_size IN NUMBER Optional
1112 -- Default = FND_API.G_MISS_NUM
1113 -- batch size of keys.
1114 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
1115 -- x_msg_count OUT NOCOPY NUMBER
1116 -- x_msg_data OUT NOCOPY VARCHAR2
1117 -- x_stocks_array OUT NOCOPY AMV_SYM_VARRAY_TYPE Required
1118 -- stock symbol and ric.
1119 -- Version : Current version 1.0
1120 -- Previous version 1.0
1121 -- Initial version 1.0
1122 -- Notes :
1123 --
1124 -- End of comments
1125 --
1126 PROCEDURE Get_VendorMissedStocks
1127 ( p_api_version IN NUMBER,
1128 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1129 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1130 x_return_status OUT NOCOPY VARCHAR2,
1131 x_msg_count OUT NOCOPY NUMBER,
1132 x_msg_data OUT NOCOPY VARCHAR2,
1133 p_vendor_id IN NUMBER,
1134 p_start_index IN NUMBER := 1,
1135 p_batch_size IN NUMBER := FND_API.G_MISS_NUM,
1136 x_stocks_array OUT NOCOPY AMV_SYM_VARRAY_TYPE
1137 )
1138 IS
1139 l_api_name CONSTANT VARCHAR2(30) := 'Get_VendorMissedStocks';
1140 l_api_version CONSTANT NUMBER := 1.0;
1141 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1142 --
1143 l_user_id number := -1;
1144 l_login_user_id number := -1;
1145 l_login_user_status varchar2(30);
1146 l_Error_Msg varchar2(2000);
1147 l_Error_Token varchar2(80);
1148 --
1149 l_stock_id number;
1150 l_rec_num number := 0;
1151
1152 CURSOR Get_VenMissStk_csr IS
1153 select a.stock_id
1154 from amv_stocks a
1155 where a.stock_id > p_start_index
1156 minus
1157 select b.stock_id
1158 from amv_vendor_keys b
1159 where b.vendor_id = p_vendor_id
1160 order by 1;
1161
1165 , exchange
1162 CURSOR Get_StocksInfo_csr IS
1163 select stock_id
1164 , stock_symbol
1166 from amv_stocks
1167 where stock_id = l_stock_id;
1168 --
1169 BEGIN
1170 -- Standard begin of API savepoint
1171 SAVEPOINT Get_VendorMissedStocks;
1172 -- Standard call to check for call compatibility.
1173 IF NOT FND_API.Compatible_API_Call (
1174 l_api_version,
1175 p_api_version,
1176 l_api_name,
1177 G_PKG_NAME)
1178 THEN
1179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1180 END IF;
1181 -- Debug Message
1182 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1183 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1184 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1185 FND_MSG_PUB.Add;
1186 END IF;
1187 --Initialize message list if p_init_msg_list is TRUE.
1188 IF FND_API.To_Boolean (p_init_msg_list) THEN
1189 FND_MSG_PUB.initialize;
1190 END IF;
1191 -- Initialize API return status to sucess
1192 x_return_status := FND_API.G_RET_STS_SUCCESS;
1193
1194 --
1195 OPEN Get_VenMissStk_csr;
1196 LOOP
1197 l_rec_num := l_rec_num + 1;
1198 FETCH Get_VenMissStk_csr INTO l_stock_id;
1199 EXIT WHEN Get_VenMissStk_csr%NOTFOUND;
1200 --
1201 IF l_stock_id is not null THEN
1202 OPEN Get_StocksInfo_csr;
1203 FETCH Get_StocksInfo_csr INTO x_stocks_array(l_rec_num);
1204 CLOSE Get_StocksInfo_csr;
1205 END IF;
1206 --
1207 IF p_batch_size <> FND_API.G_MISS_NUM THEN
1208 EXIT WHEN l_rec_num = p_batch_size;
1209 END IF;
1210 END LOOP;
1211 CLOSE Get_VenMissStk_csr;
1212 --
1213
1214 -- Success message
1215 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1216 THEN
1217 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1218 FND_MESSAGE.Set_Token('ROW', l_full_name);
1219 FND_MSG_PUB.Add;
1220 END IF;
1221 -- Debug Message
1222 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1223 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1224 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1225 FND_MSG_PUB.Add;
1226 END IF;
1227 --Standard call to get message count and if count=1, get the message
1228 FND_MSG_PUB.Count_And_Get (
1229 p_encoded => FND_API.G_FALSE,
1230 p_count => x_msg_count,
1231 p_data => x_msg_data
1232 );
1233 EXCEPTION
1234 WHEN FND_API.G_EXC_ERROR THEN
1235 ROLLBACK TO Get_VendorMissedStocks;
1236 x_return_status := FND_API.G_RET_STS_ERROR;
1237 -- Standard call to get message count and if count=1, get the message
1238 FND_MSG_PUB.Count_And_Get (
1239 p_encoded => FND_API.G_FALSE,
1240 p_count => x_msg_count,
1241 p_data => x_msg_data
1242 );
1243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1244 ROLLBACK TO Get_VendorMissedStocks;
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 -- Standard call to get message count and if count=1, get the message
1247 FND_MSG_PUB.Count_And_Get (
1248 p_encoded => FND_API.G_FALSE,
1249 p_count => x_msg_count,
1250 p_data => x_msg_data
1251 );
1252 WHEN OTHERS THEN
1253 ROLLBACK TO Get_VendorMissedStocks;
1254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1255 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1256 THEN
1257 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1258 END IF;
1259 -- Standard call to get message count and if count=1, get the message
1260 FND_MSG_PUB.Count_And_Get (
1261 p_encoded => FND_API.G_FALSE,
1262 p_count => x_msg_count,
1263 p_data => x_msg_data
1264 );
1265 --
1266 END Get_VendorMissedStocks;
1267 --------------------------------------------------------------------------------
1268 --------------------------------------------------------------------------------
1269 -- Start of comments
1270 -- API name : Insert_StockVendorKeys
1271 -- Type : Private
1272 -- Pre-reqs : None
1273 -- Function : Inserts the Stock id and the vendor id and key id for vendor
1274 -- Parameters :
1275 -- IN p_api_version IN NUMBER Required
1276 -- p_init_msg_list IN VARCHAR2 Optional
1277 -- Default = FND_API.G_FALSE
1278 -- p_commit IN VARCHAR2 Optional
1279 -- Default = FND_API.G_FALSE
1280 -- p_validation_level IN NUMBER Optional
1281 -- Default = FND_API.G_VALID_LEVEL_FULL
1282 -- p_vendor_id IN NUMBER Required
1283 -- vendor id.
1284 -- p_ticker_rec IN AMV_TKR_OBJ_TYPE Required
1285 -- ticker array (ric and key id).
1286 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
1287 -- x_msg_count OUT NOCOPY NUMBER
1288 -- x_msg_data OUT NOCOPY VARCHAR2
1289 -- Version : Current version 1.0
1290 -- Previous version 1.0
1294 -- End of comments
1291 -- Initial version 1.0
1292 -- Notes :
1293 --
1295 --
1296 PROCEDURE Insert_StockVendorKeys
1297 ( p_api_version IN NUMBER,
1298 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1299 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1300 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1301 x_return_status OUT NOCOPY VARCHAR2,
1302 x_msg_count OUT NOCOPY NUMBER,
1303 x_msg_data OUT NOCOPY VARCHAR2,
1304 p_vendor_id IN NUMBER,
1305 p_ticker_rec IN AMV_TKR_OBJ_TYPE
1306 )
1307 IS
1308 l_api_name CONSTANT VARCHAR2(30) := 'Insert_StockVendorKeys';
1309 l_api_version CONSTANT NUMBER := 1.0;
1310 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1311 --
1312 l_user_id number := Get_SBPId;
1313 l_login_user_id number := Get_SBPId;
1314 l_login_user_status varchar2(30);
1315 l_Error_Msg varchar2(2000);
1316 l_Error_Token varchar2(80);
1317 --
1318 l_key_id varchar2(60);
1319 l_vendor_key_id number;
1320 l_stock_id number;
1321 l_stock_key_id number;
1322 l_miss_rec number := 0;
1323 l_object_version_number number := 1;
1324
1325 CURSOR VendorKeyId_seq IS
1326 select amv_vendor_keys_s.nextval
1327 from dual;
1328 --
1329 BEGIN
1330 -- Standard begin of API savepoint
1331 SAVEPOINT Insert_StockVendorKeys;
1332 -- Standard call to check for call compatibility.
1333 IF NOT FND_API.Compatible_API_Call (
1334 l_api_version,
1335 p_api_version,
1336 l_api_name,
1337 G_PKG_NAME)
1338 THEN
1339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 END IF;
1341 -- Debug Message
1342 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1343 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1344 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1345 FND_MSG_PUB.Add;
1346 END IF;
1347 --Initialize message list if p_init_msg_list is TRUE.
1348 IF FND_API.To_Boolean (p_init_msg_list) THEN
1349 FND_MSG_PUB.initialize;
1350 END IF;
1351 -- Initialize API return status to sucess
1352 x_return_status := FND_API.G_RET_STS_SUCCESS;
1353
1354 --
1355 l_key_id := p_ticker_rec.key_id;
1356 l_stock_id := p_ticker_rec.stock_id;
1357
1358 IF l_stock_id is not null AND l_key_id is not null THEN
1359 OPEN VendorKeyId_seq;
1360 FETCH VendorKeyId_seq INTO l_vendor_key_id;
1361 CLOSE VendorKeyId_seq;
1362
1363 BEGIN
1364 INSERT INTO amv_vendor_keys(
1365 vendor_key_id,
1366 object_version_number,
1367 last_update_date,
1368 last_updated_by,
1369 creation_date,
1370 created_by,
1371 last_update_login,
1372 vendor_id,
1373 vendor_key,
1374 stock_id,
1375 effective_start_date
1376 )
1377 VALUES (
1378 l_vendor_key_id,
1379 l_object_version_number,
1380 sysdate,
1381 l_user_id,
1382 sysdate,
1383 l_user_id,
1384 l_user_id,
1385 p_vendor_id,
1386 l_key_id,
1387 l_stock_id,
1388 sysdate
1389 );
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 -- NOTE Add Message
1393 l_miss_rec := l_miss_rec + 1;
1394 END;
1395 ELSE
1396 -- NOTE Add Message
1397 l_miss_rec := l_miss_rec + 1;
1398 END IF;
1399 --
1400
1401 -- Success message
1402 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1403 THEN
1404 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1405 FND_MESSAGE.Set_Token('ROW', l_full_name);
1406 FND_MSG_PUB.Add;
1407 END IF;
1408 --Standard check of commit
1409 IF FND_API.To_Boolean ( p_commit ) THEN
1410 COMMIT WORK;
1411 END IF;
1412 -- Debug Message
1413 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1414 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1415 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1416 FND_MSG_PUB.Add;
1417 END IF;
1418 --Standard call to get message count and if count=1, get the message
1419 FND_MSG_PUB.Count_And_Get (
1420 p_encoded => FND_API.G_FALSE,
1421 p_count => x_msg_count,
1422 p_data => x_msg_data
1423 );
1424 EXCEPTION
1425 WHEN FND_API.G_EXC_ERROR THEN
1426 ROLLBACK TO Insert_StockVendorKeys;
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 -- Standard call to get message count and if count=1, get the message
1429 FND_MSG_PUB.Count_And_Get (
1430 p_encoded => FND_API.G_FALSE,
1431 p_count => x_msg_count,
1432 p_data => x_msg_data
1433 );
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 ROLLBACK TO Insert_StockVendorKeys;
1436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1437 -- Standard call to get message count and if count=1, get the message
1438 FND_MSG_PUB.Count_And_Get (
1439 p_encoded => FND_API.G_FALSE,
1440 p_count => x_msg_count,
1441 p_data => x_msg_data
1442 );
1443 WHEN OTHERS THEN
1447 THEN
1444 ROLLBACK TO Insert_StockVendorKeys;
1445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1446 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1448 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1449 END IF;
1450 -- Standard call to get message count and if count=1, get the message
1451 FND_MSG_PUB.Count_And_Get (
1452 p_encoded => FND_API.G_FALSE,
1453 p_count => x_msg_count,
1454 p_data => x_msg_data
1455 );
1456 --
1457 END Insert_StockVendorKeys;
1458 --------------------------------------------------------------------------------
1459 --------------------------------------------------------------------------------
1460 -- Start of comments
1461 -- API name : Get_UserSelectedKeys
1462 -- Type : Private
1463 -- Pre-reqs : None
1464 -- Function : Gives an array of all the stocks selected by all users
1465 -- Parameters :
1466 -- IN p_api_version IN NUMBER Required
1467 -- p_init_msg_list IN VARCHAR2 Optional
1468 -- Default = FND_API.G_FALSE
1469 -- p_validation_level IN NUMBER Optional
1470 -- Default = FND_API.G_VALID_LEVEL_FULL
1471 -- p_vendor_id IN NUMBER Required
1472 -- vendor id
1473 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
1474 -- x_msg_count OUT NOCOPY NUMBER
1475 -- x_msg_data OUT NOCOPY VARCHAR2
1476 -- x_keys_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE Required
1477 -- key id.
1478 -- Version : Current version 1.0
1479 -- Previous version 1.0
1480 -- Initial version 1.0
1481 -- Notes :
1482 --
1483 -- End of comments
1484 --
1485 PROCEDURE Get_UserSelectedKeys
1486 ( p_api_version IN NUMBER,
1487 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1488 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1489 x_return_status OUT NOCOPY VARCHAR2,
1490 x_msg_count OUT NOCOPY NUMBER,
1491 x_msg_data OUT NOCOPY VARCHAR2,
1492 p_vendor_id IN NUMBER,
1493 p_all_keys IN VARCHAR2 := FND_API.G_FALSE,
1494 x_keys_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
1495 )
1496 IS
1497 l_api_name CONSTANT VARCHAR2(30) := 'Get_UserSelectedKeys';
1498 l_api_version CONSTANT NUMBER := 1.0;
1499 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1500 --
1501 l_user_id number := -1;
1502 l_login_user_id number := -1;
1503 l_login_user_status varchar2(30);
1504 l_Error_Msg varchar2(2000);
1505 l_Error_Token varchar2(80);
1506 --
1507 l_key_id varchar2(60);
1508 l_rec_num number := 0;
1509 l_symbols varchar2(400);
1510 l_stock_symbol varchar2(20);
1511 l_profile_name varchar2(30) := 'AMV_DEFAULT_STOCK';
1512 l_symbols_array amv_char_varray_type;
1513
1514 CURSOR Get_UserKeys_csr IS
1515 select distinct amvk.vendor_key
1516 from amv_vendor_keys amvk
1517 , amv_user_ticker amut
1518 where amut.stock_id = amvk.stock_id
1519 and amvk.vendor_id = p_vendor_id;
1520
1521 CURSOR Get_SymKeys_csr IS
1522 select amvk.vendor_key
1523 from amv_vendor_keys amvk
1524 , amv_stocks amst
1525 where amst.stock_symbol = l_stock_symbol
1526 and amst.stock_id = amvk.stock_id;
1527
1528 CURSOR Get_AllKeys_csr IS
1529 select distinct amvk.vendor_key
1530 from amv_vendor_keys amvk
1531 where amvk.vendor_id = p_vendor_id;
1532 --
1533 BEGIN
1534 -- Standard begin of API savepoint
1535 SAVEPOINT Get_UserSelectedKeys;
1536 -- Standard call to check for call compatibility.
1537 IF NOT FND_API.Compatible_API_Call (
1538 l_api_version,
1539 p_api_version,
1540 l_api_name,
1541 G_PKG_NAME)
1542 THEN
1543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1544 END IF;
1545 -- Debug Message
1546 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1547 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1548 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1549 FND_MSG_PUB.Add;
1550 END IF;
1551 --Initialize message list if p_init_msg_list is TRUE.
1552 IF FND_API.To_Boolean (p_init_msg_list) THEN
1553 FND_MSG_PUB.initialize;
1554 END IF;
1555 -- Initialize API return status to sucess
1556 x_return_status := FND_API.G_RET_STS_SUCCESS;
1557
1558 --
1559 IF p_all_keys = FND_API.G_TRUE THEN
1560 -- get all keys
1561 OPEN Get_AllKeys_csr;
1562 LOOP
1563 l_rec_num := l_rec_num + 1;
1564 FETCH Get_AllKeys_csr INTO x_keys_array(l_rec_num);
1565 EXIT WHEN Get_AllKeys_csr%NOTFOUND;
1566 END LOOP;
1567 CLOSE Get_AllKeys_csr;
1568 ELSE
1569 -- get user selected keys
1570 OPEN Get_UserKeys_csr;
1571 LOOP
1572 l_rec_num := l_rec_num + 1;
1573 FETCH Get_UserKeys_csr INTO x_keys_array(l_rec_num);
1574 EXIT WHEN Get_UserKeys_csr%NOTFOUND;
1575 END LOOP;
1576 CLOSE Get_UserKeys_csr;
1577 --
1581 IF l_symbols is not null THEN
1578 -- get default stocks from profile option values
1579 FND_PROFILE.Get(l_profile_name, l_symbols);
1580 -- parse the profile symbols
1582 Parse_Symbols(l_symbols, l_symbols_array);
1583 END IF;
1584
1585 FOR i in 1..l_symbols_array.count LOOP
1586 l_stock_symbol := l_symbols_array(i);
1587 OPEN Get_SymKeys_csr;
1588 FETCH Get_SymKeys_csr INTO l_key_id;
1589 IF l_key_id is not null THEN
1590 x_keys_array(l_rec_num) := l_key_id;
1591 l_rec_num := l_rec_num + 1;
1592 END IF;
1593 CLOSE Get_SymKeys_csr;
1594 END LOOP;
1595 END IF;
1596 --
1597
1598 -- Success message
1599 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1600 THEN
1601 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1602 FND_MESSAGE.Set_Token('ROW', l_full_name);
1603 FND_MSG_PUB.Add;
1604 END IF;
1605 -- Debug Message
1606 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1607 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1608 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1609 FND_MSG_PUB.Add;
1610 END IF;
1611 --Standard call to get message count and if count=1, get the message
1612 FND_MSG_PUB.Count_And_Get (
1613 p_encoded => FND_API.G_FALSE,
1614 p_count => x_msg_count,
1615 p_data => x_msg_data
1616 );
1617 EXCEPTION
1618 WHEN FND_API.G_EXC_ERROR THEN
1619 ROLLBACK TO Get_UserSelectedKeys;
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 -- Standard call to get message count and if count=1, get the message
1622 FND_MSG_PUB.Count_And_Get (
1623 p_encoded => FND_API.G_FALSE,
1624 p_count => x_msg_count,
1625 p_data => x_msg_data
1626 );
1627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1628 ROLLBACK TO Get_UserSelectedKeys;
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 -- Standard call to get message count and if count=1, get the message
1631 FND_MSG_PUB.Count_And_Get (
1632 p_encoded => FND_API.G_FALSE,
1633 p_count => x_msg_count,
1634 p_data => x_msg_data
1635 );
1636 WHEN OTHERS THEN
1637 ROLLBACK TO Get_UserSelectedKeys;
1638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1639 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1640 THEN
1641 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1642 END IF;
1643 -- Standard call to get message count and if count=1, get the message
1644 FND_MSG_PUB.Count_And_Get (
1645 p_encoded => FND_API.G_FALSE,
1646 p_count => x_msg_count,
1647 p_data => x_msg_data
1648 );
1649 --
1650 END Get_UserSelectedKeys;
1651 --------------------------------------------------------------------------------
1652 --------------------------------------------------------------------------------
1653 -- Start of comments
1654 -- API name : Insert_VendorNews
1655 -- Type : Private
1656 -- Pre-reqs : None
1657 -- Function : Inserts News in to the vendor news table
1658 -- Parameters :
1659 -- IN p_api_version IN NUMBER Required
1660 -- p_init_msg_list IN VARCHAR2 Optional
1661 -- Default = FND_API.G_FALSE
1662 -- p_commit IN VARCHAR2 Optional
1663 -- Default = FND_API.G_FALSE
1664 -- p_validation_level IN NUMBER Optional
1665 -- Default = FND_API.G_VALID_LEVEL_FULL
1666 -- p_vendor_id IN NUMBER Required
1667 -- vendor id.
1668 -- p_news_rec IN AMV_NEWS_OBJ_TYPE Required
1669 -- array of news objects.
1670 -- OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2
1671 -- x_msg_count OUT NOCOPY NUMBER
1672 -- x_msg_data OUT NOCOPY VARCHAR2
1673 -- Version : Current version 1.0
1674 -- Previous version 1.0
1675 -- Initial version 1.0
1676 -- Notes :
1677 --
1678 -- End of comments
1679 --
1680 PROCEDURE Insert_VendorNews
1681 ( p_api_version IN NUMBER,
1682 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1683 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1684 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1685 x_return_status OUT NOCOPY VARCHAR2,
1686 x_msg_count OUT NOCOPY NUMBER,
1687 x_msg_data OUT NOCOPY VARCHAR2,
1688 p_vendor_id IN NUMBER,
1689 p_news_rec IN AMV_NEWS_OBJ_TYPE
1690 )
1691 IS
1692 l_api_name CONSTANT VARCHAR2(30) := 'Insert_VendorNews';
1693 l_api_version CONSTANT NUMBER := 1.0;
1694 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1695 --
1696 l_user_id number := Get_SBPId;
1697 l_login_user_id number := Get_SBPId;
1698 l_login_user_status varchar2(30);
1699 l_Error_Msg varchar2(2000);
1703 l_key_id varchar2(60);
1700 l_Error_Token varchar2(80);
1701 --
1702 l_news_id number;
1704 l_vendor_key_id number;
1705 l_miss_rec number := 0;
1706 l_object_version_number number := 1;
1707
1708 CURSOR Get_VendorKeyId_csr IS
1709 select vendor_key_id
1710 from amv_vendor_keys
1711 where vendor_id = p_vendor_id
1712 and vendor_key = l_key_id;
1713
1714 CURSOR NewsId_seq IS
1715 select amv_news_s.nextval
1716 from dual;
1717 --
1718 BEGIN
1719 -- Standard begin of API savepoint
1720 SAVEPOINT Insert_VendorNews;
1721 -- Standard call to check for call compatibility.
1722 IF NOT FND_API.Compatible_API_Call (
1723 l_api_version,
1724 p_api_version,
1725 l_api_name,
1726 G_PKG_NAME)
1727 THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730 -- Debug Message
1731 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1732 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1733 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1734 FND_MSG_PUB.Add;
1735 END IF;
1736 --Initialize message list if p_init_msg_list is TRUE.
1737 IF FND_API.To_Boolean (p_init_msg_list) THEN
1738 FND_MSG_PUB.initialize;
1739 END IF;
1740 -- Initialize API return status to sucess
1741 x_return_status := FND_API.G_RET_STS_SUCCESS;
1742
1743 --
1744 l_key_id := p_news_rec.key_id;
1745 OPEN Get_VendorKeyId_csr;
1746 FETCH Get_VendorKeyId_csr INTO l_vendor_key_id;
1747 CLOSE Get_VendorKeyId_csr;
1748 IF l_vendor_key_id is not null THEN
1749 BEGIN
1750 OPEN NewsId_seq;
1751 FETCH NewsId_seq INTO l_news_id;
1752 CLOSE NewsId_seq;
1753
1754 INSERT INTO amv_news (
1755 news_id,
1756 object_version_number,
1757 last_update_date,
1758 last_updated_by,
1759 creation_date,
1760 created_by,
1761 last_update_login,
1762 vendor_key_id,
1763 news_url,
1764 news_title,
1765 provider,
1766 date_time
1767 )
1768 VALUES (
1769 l_news_id,
1770 l_object_version_number,
1771 sysdate,
1772 l_user_id,
1773 sysdate,
1774 l_user_id,
1775 l_user_id,
1776 l_vendor_key_id,
1777 p_news_rec.news_url,
1778 p_news_rec.title,
1779 p_news_rec.provider,
1780 p_news_rec.date_time
1781 );
1782
1783 EXCEPTION
1784 WHEN OTHERS THEN
1785 l_miss_rec := l_miss_rec + 1;
1786 END;
1787 ELSE
1788 l_miss_rec := l_miss_rec + 1;
1789 END IF;
1790 --
1791
1792 -- Success message
1793 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1794 THEN
1795 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1796 FND_MESSAGE.Set_Token('ROW', l_full_name);
1797 FND_MSG_PUB.Add;
1798 END IF;
1799 --Standard check of commit
1800 IF FND_API.To_Boolean ( p_commit ) THEN
1801 COMMIT WORK;
1802 END IF;
1803 -- Debug Message
1804 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1805 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1806 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1807 FND_MSG_PUB.Add;
1808 END IF;
1809 --Standard call to get message count and if count=1, get the message
1810 FND_MSG_PUB.Count_And_Get (
1811 p_encoded => FND_API.G_FALSE,
1812 p_count => x_msg_count,
1813 p_data => x_msg_data
1814 );
1815 EXCEPTION
1816 WHEN FND_API.G_EXC_ERROR THEN
1817 ROLLBACK TO Insert_VendorNews;
1818 x_return_status := FND_API.G_RET_STS_ERROR;
1819 -- Standard call to get message count and if count=1, get the message
1820 FND_MSG_PUB.Count_And_Get (
1821 p_encoded => FND_API.G_FALSE,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data
1824 );
1825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1826 ROLLBACK TO Insert_VendorNews;
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1828 -- Standard call to get message count and if count=1, get the message
1829 FND_MSG_PUB.Count_And_Get (
1830 p_encoded => FND_API.G_FALSE,
1831 p_count => x_msg_count,
1832 p_data => x_msg_data
1833 );
1834 WHEN OTHERS THEN
1835 ROLLBACK TO Insert_VendorNews;
1836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1837 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1838 THEN
1839 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1840 END IF;
1841 -- Standard call to get message count and if count=1, get the message
1842 FND_MSG_PUB.Count_And_Get (
1843 p_encoded => FND_API.G_FALSE,
1844 p_count => x_msg_count,
1845 p_data => x_msg_data
1846 );
1847 --
1848 END Insert_VendorNews;
1849 --------------------------------------------------------------------------------
1850 --------------------------------------------------------------------------------
1851 -- Start of comments
1855 -- Function : Inserts News in to the vendor news table
1852 -- API name : Get_CompanyNews
1853 -- Type : Private
1854 -- Pre-reqs : None
1856 -- Parameters :
1857 -- IN p_api_version IN NUMBER Required
1858 -- p_init_msg_list IN VARCHAR2 Optional
1859 -- Default = FND_API.G_FALSE
1860 -- p_commit IN VARCHAR2 Optional
1861 -- Default = FND_API.G_FALSE
1862 -- p_validation_level IN NUMBER Optional
1863 -- Default = FND_API.G_VALID_LEVEL_FULL
1864 -- p_vendor_id IN NUMBER Required
1865 -- vendor id.
1866 -- OUT : x_return_status OUT VARCHAR2
1867 -- x_msg_count OUT NUMBER
1868 -- x_msg_data OUT VARCHAR2
1869 -- x_news_array OUT AMV_NEWS_VARRAY_TYPE
1870 -- array of news objects.
1871 -- Version : Current version 1.0
1872 -- Previous version 1.0
1873 -- Initial version 1.0
1874 -- Notes :
1875 --
1876 -- End of comments
1877 --
1878 PROCEDURE Get_CompanyNews
1879 ( p_api_version IN NUMBER,
1880 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1881 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1882 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1883 x_return_status OUT NOCOPY VARCHAR2,
1884 x_msg_count OUT NOCOPY NUMBER,
1885 x_msg_data OUT NOCOPY VARCHAR2,
1886 p_stock_id IN NUMBER,
1887 x_news_array OUT NOCOPY AMV_NEWS_VARRAY_TYPE
1888 )
1889 IS
1890 l_api_name CONSTANT VARCHAR2(30) := 'Get_CompanyNews';
1891 l_api_version CONSTANT NUMBER := 1.0;
1892 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1893 --
1894 l_user_id number := -1;
1895 l_login_user_id number := -1;
1896 l_login_user_status varchar2(30);
1897 l_Error_Msg varchar2(2000);
1898 l_Error_Token varchar2(80);
1899 --
1900 l_key_id varchar2(60);
1901 l_news_url varchar2(240);
1902 l_title varchar2(240);
1903 l_provider varchar2(80);
1904 l_date_time date;
1905 l_rec_num number := 0;
1906
1907
1908 CURSOR Get_News_csr IS
1909 select v.vendor_key
1910 , n.news_url
1911 , n.news_title
1912 , n.provider
1913 , n.date_time
1914 from amv_news n
1915 , amv_vendor_keys v
1916 where v.stock_id = p_stock_id
1917 and v.vendor_key_id = n.vendor_key_id
1918 order by n.date_time desc;
1919
1920 --
1921 BEGIN
1922 -- Standard begin of API savepoint
1923 SAVEPOINT Get_CompanyNews;
1924 -- Standard call to check for call compatibility.
1925 IF NOT FND_API.Compatible_API_Call (
1926 l_api_version,
1927 p_api_version,
1928 l_api_name,
1929 G_PKG_NAME)
1930 THEN
1931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1932 END IF;
1933 -- Debug Message
1934 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1935 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1936 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1937 FND_MSG_PUB.Add;
1938 END IF;
1939 --Initialize message list if p_init_msg_list is TRUE.
1940 IF FND_API.To_Boolean (p_init_msg_list) THEN
1941 FND_MSG_PUB.initialize;
1942 END IF;
1943 -- Initialize API return status to sucess
1944 x_return_status := FND_API.G_RET_STS_SUCCESS;
1945
1946 --
1947 OPEN Get_News_csr;
1948 LOOP
1949 l_rec_num := l_rec_num + 1;
1950 FETCH Get_News_csr INTO x_news_array(l_rec_num);
1951 EXIT WHEN Get_News_csr%NOTFOUND;
1952 END LOOP;
1953 CLOSE Get_News_csr;
1954 --
1955
1956 -- Success message
1957 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1958 THEN
1959 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1960 FND_MESSAGE.Set_Token('ROW', l_full_name);
1961 FND_MSG_PUB.Add;
1962 END IF;
1963 --Standard check of commit
1964 IF FND_API.To_Boolean ( p_commit ) THEN
1965 COMMIT WORK;
1966 END IF;
1967 -- Debug Message
1968 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1969 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1970 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1971 FND_MSG_PUB.Add;
1972 END IF;
1973 --Standard call to get message count and if count=1, get the message
1974 FND_MSG_PUB.Count_And_Get (
1975 p_encoded => FND_API.G_FALSE,
1976 p_count => x_msg_count,
1977 p_data => x_msg_data
1978 );
1979 EXCEPTION
1980 WHEN FND_API.G_EXC_ERROR THEN
1981 ROLLBACK TO Get_CompanyNews;
1982 x_return_status := FND_API.G_RET_STS_ERROR;
1983 -- Standard call to get message count and if count=1, get the message
1984 FND_MSG_PUB.Count_And_Get (
1985 p_encoded => FND_API.G_FALSE,
1986 p_count => x_msg_count,
1987 p_data => x_msg_data
1988 );
1989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1990 ROLLBACK TO Get_CompanyNews;
1991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1992 -- Standard call to get message count and if count=1, get the message
1993 FND_MSG_PUB.Count_And_Get (
1994 p_encoded => FND_API.G_FALSE,
1995 p_count => x_msg_count,
1996 p_data => x_msg_data
1997 );
1998 WHEN OTHERS THEN
1999 ROLLBACK TO Get_CompanyNews;
2000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2001 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2002 THEN
2003 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2004 END IF;
2005 -- Standard call to get message count and if count=1, get the message
2006 FND_MSG_PUB.Count_And_Get (
2007 p_encoded => FND_API.G_FALSE,
2008 p_count => x_msg_count,
2009 p_data => x_msg_data
2010 );
2011 --
2012 END Get_CompanyNews;
2013 --------------------------------------------------------------------------------
2014 --------------------------------------------------------------------------------
2015 --
2016 END amv_stock_pvt;