1 PACKAGE amv_stock_pvt AS
2 /* $Header: amvvstks.pls 120.1 2005/06/30 13:13:56 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_STOCK_PVT
6 --
7 -- HISTORY
8 -- 11/10/1999 SLKRISHN CREATED
9 --
10 --
11 --------------------------------------------------------------------------------
12 G_ASC_ORDER CONSTANT VARCHAR2(5) := 'ASC';
13 G_DESC_ORDER CONSTANT VARCHAR2(5) := 'DESC';
14
15 -- RECORDS TYPES USED IN THIS PACKAGE
16 TYPE AMV_TKR_OBJ_TYPE IS RECORD(
17 key_id VARCHAR2(60),
18 stock_id NUMBER
19 );
20
21 TYPE AMV_SYM_OBJ_TYPE IS RECORD(
22 stock_id NUMBER,
23 symbol VARCHAR2(20),
24 exchange VARCHAR2(30)
25 );
26
27 TYPE AMV_STK_OBJ_TYPE IS RECORD(
28 stock_id NUMBER,
29 symbol VARCHAR2(20),
30 description VARCHAR2(240),
31 price NUMBER,
32 change NUMBER
33 );
34
35 TYPE AMV_NEWS_OBJ_TYPE IS RECORD(
36 key_id VARCHAR2(60),
37 news_url VARCHAR2(2000),
38 title VARCHAR2(2000),
39 provider VARCHAR2(240),
40 date_time DATE
41 );
42 -- TABLE TYPES USED IN THIS PACKAGE
43 TYPE AMV_TKR_VARRAY_TYPE IS TABLE OF AMV_TKR_OBJ_TYPE
44 INDEX BY BINARY_INTEGER;
45 TYPE AMV_SYM_VARRAY_TYPE IS TABLE OF AMV_SYM_OBJ_TYPE
46 INDEX BY BINARY_INTEGER;
47 TYPE AMV_STK_VARRAY_TYPE IS TABLE OF AMV_STK_OBJ_TYPE
48 INDEX BY BINARY_INTEGER;
49 TYPE AMV_NEWS_VARRAY_TYPE IS TABLE OF AMV_NEWS_OBJ_TYPE
50 INDEX BY BINARY_INTEGER;
51 --
52 TYPE AMV_CHAR_VARRAY_TYPE IS TABLE OF VARCHAR2(400)
53 INDEX BY BINARY_INTEGER;
54 TYPE AMV_NUM_VARRAY_TYPE IS TABLE OF NUMBER
55 INDEX BY BINARY_INTEGER;
56 --
57 --------------------------------------------------------------------------------
58 --------------------------------------------------------------------------------
59 -- Start of comments
60 -- API name : Get_UserTicker
61 -- Type : Private
62 -- Pre-reqs : None
63 -- Function : Returns the list of tickers for an user
64 -- Parameters :
65 -- IN p_api_version IN NUMBER Required
66 -- p_init_msg_list IN VARCHAR2 Optional
67 -- Default = FND_API.G_FALSE
68 -- p_validation_level IN NUMBER Optional
69 -- Default = FND_API.G_VALID_LEVEL_FULL
70 -- p_user_id IN NUMBER Required
71 -- p_sort_order IN VARCHAR2 Optional
72 -- OUT : x_return_status OUT VARCHAR2
73 -- x_msg_count OUT NUMBER
74 -- x_msg_data OUT VARCHAR2
75 -- x_ticker_array OUT AMV_STK_VARRAY_TYPE
76 -- Version : Current version 1.0
77 -- Previous version 1.0
78 -- Initial version 1.0
79 -- Notes :
80 --
81 -- End of comments
82 --
83 PROCEDURE Get_UserTicker
84 ( p_api_version IN NUMBER,
85 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
86 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 p_user_id IN NUMBER,
91 p_distinct_stocks IN VARCHAR2 := FND_API.G_FALSE,
92 p_sort_order IN VARCHAR2 := FND_API.G_MISS_CHAR,
93 x_stkpr_array OUT NOCOPY AMV_STK_VARRAY_TYPE
94 );
95 --------------------------------------------------------------------------------
96 --------------------------------------------------------------------------------
97 -- Start of comments
98 -- API name : Get_StockDetails
99 -- Type : Private
100 -- Pre-reqs : None
101 -- Function : Returns the ticker details for list of stocks
102 -- Parameters :
103 -- IN p_api_version IN NUMBER Required
104 -- p_init_msg_list IN VARCHAR2 Optional
105 -- Default = FND_API.G_FALSE
106 -- p_validation_level IN NUMBER Optional
107 -- Default = FND_API.G_VALID_LEVEL_FULL
108 -- p_symbols IN VARCHAR2 Required
109 -- OUT : x_return_status OUT VARCHAR2
110 -- x_msg_count OUT NUMBER
111 -- x_msg_data OUT VARCHAR2
112 -- x_ticker_array OUT AMV_STK_VARRAY_TYPE
113 -- Version : Current version 1.0
114 -- Previous version 1.0
115 -- Initial version 1.0
116 -- Notes :
117 --
118 -- End of comments
119 --
120 PROCEDURE Get_StockDetails
121 ( p_api_version IN NUMBER,
122 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
123 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2,
127 p_symbols IN VARCHAR2,
128 x_stkpr_array OUT NOCOPY AMV_STK_VARRAY_TYPE
129 );
130 --------------------------------------------------------------------------------
131 --------------------------------------------------------------------------------
132 -- Start of comments
133 -- API name : Update_UserTicker
134 -- Type : Private
135 -- Pre-reqs : None
136 -- Function : Update the tickers for an user
137 -- Parameters :
138 -- IN p_api_version IN NUMBER Required
139 -- p_init_msg_list IN VARCHAR2 Optional
140 -- Default = FND_API.G_FALSE
141 -- p_commit IN VARCHAR2 Optional
142 -- Default = FND_API.G_FALSE
143 -- p_validation_level IN NUMBER Optional
144 -- Default = FND_API.G_VALID_LEVEL_FULL
145 -- p_object_version_number IN NUMBER Required
146 -- object version number
147 -- p_user_id IN NUMBER Required
148 -- user id.
149 -- p_symbols IN VARCHAR2 Required
150 -- stock symbols.
151 -- OUT : x_return_status OUT VARCHAR2
152 -- x_msg_count OUT NUMBER
153 -- x_msg_data OUT VARCHAR2
154 -- Version : Current version 1.0
155 -- Previous version 1.0
156 -- Initial version 1.0
157 -- Notes :
158 --
159 -- End of comments
160 --
161 PROCEDURE Update_UserTicker
162 ( p_api_version IN NUMBER,
163 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
164 p_commit IN VARCHAR2 := FND_API.G_FALSE,
165 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2,
169 p_object_version_number IN NUMBER,
170 p_user_id IN NUMBER,
171 p_symbols IN VARCHAR2
172 );
173 --------------------------------------------------------------------------------
174 --------------------------------------------------------------------------------
175 -- Start of comments
176 -- API name : Get_VendorMissedStocks
177 -- Type : Private
178 -- Pre-reqs : None
179 -- Function : Gets the list of stocks for which the vendor keys have not
180 -- been identified
181 -- Parameters :
182 -- IN p_api_version IN NUMBER Required
183 -- p_init_msg_list IN VARCHAR2 Optional
184 -- Default = FND_API.G_FALSE
185 -- p_validation_level IN NUMBER Optional
186 -- Default = FND_API.G_VALID_LEVEL_FULL
187 -- p_vendor_id IN NUMBER Required
188 -- vendor id.
189 -- p_start_index IN NUMBER Optional
190 -- Default = 1
191 -- p_batch_size IN NUMBER Optional
192 -- Default = FND_API.G_MISS_NUM
193 -- batch size of keys.
194 -- OUT : x_return_status OUT VARCHAR2
195 -- x_msg_count OUT NUMBER
196 -- x_msg_data OUT VARCHAR2
197 -- x_stocks_array OUT AMV_SYM_VARRAY_TYPE Required
198 -- stock symbol and ric.
199 -- Version : Current version 1.0
200 -- Previous version 1.0
201 -- Initial version 1.0
202 -- Notes :
203 --
204 -- End of comments
205 --
206 PROCEDURE Get_VendorMissedStocks
207 ( p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
209 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_vendor_id IN NUMBER,
214 p_start_index IN NUMBER := 1,
215 p_batch_size IN NUMBER := FND_API.G_MISS_NUM,
216 x_stocks_array OUT NOCOPY AMV_SYM_VARRAY_TYPE
217 );
218 --------------------------------------------------------------------------------
219 --------------------------------------------------------------------------------
220 -- Start of comments
221 -- API name : Insert_StockVendorKeys
222 -- Type : Private
223 -- Pre-reqs : None
224 -- Function : Inserts the Stock id and the vendor id and key id for vendor
225 -- Parameters :
226 -- IN p_api_version IN NUMBER Required
227 -- p_init_msg_list IN VARCHAR2 Optional
228 -- Default = FND_API.G_FALSE
229 -- p_commit IN VARCHAR2 Optional
230 -- Default = FND_API.G_FALSE
231 -- p_validation_level IN NUMBER Optional
232 -- Default = FND_API.G_VALID_LEVEL_FULL
233 -- p_vendor_id IN NUMBER Required
234 -- vendor id.
235 -- p_ticker_rec IN AMV_TKR_OBJ_TYPE Required
236 -- ticker array (ric and key id).
237 -- OUT : x_return_status OUT VARCHAR2
238 -- x_msg_count OUT NUMBER
239 -- x_msg_data OUT VARCHAR2
240 -- Version : Current version 1.0
241 -- Previous version 1.0
242 -- Initial version 1.0
243 -- Notes :
244 --
245 -- End of comments
246 --
247 PROCEDURE Insert_StockVendorKeys
248 ( p_api_version IN NUMBER,
249 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
250 p_commit IN VARCHAR2 := FND_API.G_FALSE,
251 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 p_vendor_id IN NUMBER,
256 p_ticker_rec IN AMV_TKR_OBJ_TYPE
257 );
258 --------------------------------------------------------------------------------
259 --------------------------------------------------------------------------------
260 -- Start of comments
261 -- API name : Get_UserSelectedKeys
262 -- Type : Private
263 -- Pre-reqs : None
264 -- Function : Gives an array of all the stocks selected by all users
265 -- Parameters :
266 -- IN p_api_version IN NUMBER Required
267 -- p_init_msg_list IN VARCHAR2 Optional
268 -- Default = FND_API.G_FALSE
269 -- p_validation_level IN NUMBER Optional
270 -- Default = FND_API.G_VALID_LEVEL_FULL
271 -- p_vendor_id IN NUMBER Required
272 -- vendor id
273 -- OUT : x_return_status OUT VARCHAR2
274 -- x_msg_count OUT NUMBER
275 -- x_msg_data OUT VARCHAR2
276 -- x_keys_array OUT AMV_CHAR_VARRAY_TYPE Required
277 -- key id.
278 -- Version : Current version 1.0
279 -- Previous version 1.0
280 -- Initial version 1.0
281 -- Notes :
282 --
283 -- End of comments
284 --
285 PROCEDURE Get_UserSelectedKeys
286 ( p_api_version IN NUMBER,
287 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
288 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 p_vendor_id IN NUMBER,
293 p_all_keys IN VARCHAR2 := FND_API.G_FALSE,
294 x_keys_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
295 );
296 --------------------------------------------------------------------------------
297 --------------------------------------------------------------------------------
298 -- Start of comments
299 -- API name : Insert_VendorNews
300 -- Type : Private
301 -- Pre-reqs : None
302 -- Function : Inserts News in to the vendor news table
303 -- Parameters :
304 -- IN p_api_version IN NUMBER Required
305 -- p_init_msg_list IN VARCHAR2 Optional
306 -- Default = FND_API.G_FALSE
307 -- p_commit IN VARCHAR2 Optional
308 -- Default = FND_API.G_FALSE
309 -- p_validation_level IN NUMBER Optional
310 -- Default = FND_API.G_VALID_LEVEL_FULL
311 -- p_vendor_id IN NUMBER Required
312 -- vendor id.
313 -- p_news_rec IN AMV_NEWS_OBJ_TYPE Required
314 -- array of news objects.
315 -- OUT : x_return_status OUT VARCHAR2
316 -- x_msg_count OUT NUMBER
317 -- x_msg_data OUT VARCHAR2
318 -- Version : Current version 1.0
319 -- Previous version 1.0
320 -- Initial version 1.0
321 -- Notes :
322 --
323 -- End of comments
324 --
325 PROCEDURE Insert_VendorNews
326 ( p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
328 p_commit IN VARCHAR2 := FND_API.G_FALSE,
329 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2,
333 p_vendor_id IN NUMBER,
334 p_news_rec IN AMV_NEWS_OBJ_TYPE
335 );
336 --------------------------------------------------------------------------------
337 --------------------------------------------------------------------------------
338 -- Start of comments
339 -- API name : Get_CompanyNews
340 -- Type : Private
341 -- Pre-reqs : None
342 -- Function : Inserts News in to the vendor news table
343 -- Parameters :
344 -- IN p_api_version IN NUMBER Required
345 -- p_init_msg_list IN VARCHAR2 Optional
346 -- Default = FND_API.G_FALSE
347 -- p_commit IN VARCHAR2 Optional
348 -- Default = FND_API.G_FALSE
349 -- p_validation_level IN NUMBER Optional
350 -- Default = FND_API.G_VALID_LEVEL_FULL
351 -- p_vendor_id IN NUMBER Required
352 -- vendor id.
353 -- OUT : x_return_status OUT VARCHAR2
354 -- x_msg_count OUT NUMBER
355 -- x_msg_data OUT VARCHAR2
356 -- x_news_array OUT AMV_NEWS_VARRAY_TYPE
357 -- array of news objects.
358 -- Version : Current version 1.0
359 -- Previous version 1.0
360 -- Initial version 1.0
361 -- Notes :
362 --
363 -- End of comments
364 --
365 PROCEDURE Get_CompanyNews
366 (p_api_version IN NUMBER,
367 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
368 p_commit IN VARCHAR2 := FND_API.G_FALSE,
369 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2,
373 p_stock_id IN NUMBER,
374 x_news_array OUT NOCOPY AMV_NEWS_VARRAY_TYPE
375 );
376 --------------------------------------------------------------------------------
377 --------------------------------------------------------------------------------
378 --
379 END amv_stock_pvt;