The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Symbols
(
p_user_id IN NUMBER,
p_symbols_array IN amv_char_varray_type
);
PROCEDURE Delete_UserStocks
(
p_tickerid_array IN AMV_NUM_VARRAY_TYPE
);
PROCEDURE Update_Symbols
(
p_user_id IN NUMBER,
p_symbols_array IN amv_char_varray_type
)
IS
l_stock_id number;
select stock_id
from amv_stocks
where stock_symbol = l_symbol;
UPDATE amv_user_ticker
SET last_update_date = sysdate + i
WHERE user_id = p_user_id
AND stock_id = l_stock_id;
END Update_Symbols;
select amut.user_ticker_id
, amst.stock_symbol
, l_null
from amv_user_ticker amut
, amv_stocks amst
where amut.user_id = p_user_id
and amut.stock_id = amst.stock_id;
select count(*)
from fnd_user
where user_id = p_user_id;
select stock_id
from amv_stocks
where stock_symbol = l_symbol;
SELECT amv_user_ticker_s.nextval
FROM dual;
-- insert stock ticker if stock id is not null
IF l_stock_id is not null THEN
OPEN UserTicker_seq;
INSERT INTO amv_user_ticker
(
user_ticker_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
user_id,
stock_id
)
VALUES
(
l_user_ticker_id,
l_object_version_number,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_user_id,
p_user_id,
l_stock_id
);
PROCEDURE Delete_UserStocks
(
p_tickerid_array IN AMV_NUM_VARRAY_TYPE
)
IS
BEGIN
--
FOR i in 1..p_tickerid_array.count LOOP
DELETE FROM amv_user_ticker
where user_ticker_id = p_tickerid_array(i);
END Delete_UserStocks;
select user_id
from fnd_user
where user_name = 'STANDALONE BATCH PROCESS';
l_last_update_date date;
l_select_stmt varchar2(200);
select amst.stock_id
, amst.stock_symbol
, amst.stock_desc
, amsp.stock_price
, amsp.change
from amv_stocks amst
, amv_stock_price amsp
where amst.stock_ric = amsp.stock_ric(+)
and amst.stock_id = l_stock_id
order by amsp.time_stamp desc;
select distinct amst.stock_symbol, amut.stock_id
from amv_user_ticker amut
, amv_stocks amst
where amut.user_id = p_user_id
and amut.stock_id = amst.stock_id
order by amst.stock_symbol ASC;
select amut.stock_id
from amv_user_ticker amut
, amv_stocks amst
where amut.user_id = p_user_id
and amut.stock_id = amst.stock_id
order by amst.stock_symbol ASC;
l_select_stmt := 'SELECT amst.stock_symbol, amut.stock_id, amut.last_update_date ';
l_select_stmt := 'SELECT distinct amst.stock_symbol, amut.stock_id, amut.last_update_date ';
l_order_by := 'ORDER BY amut.last_update_date ASC';
l_sql_statement := l_select_stmt || l_from_stmt || l_where_clause || l_order_by;
FETCH l_cursor INTO l_stock_symbol, l_stock_id, l_last_update_date;
select amst.stock_id
, amst.stock_symbol
, amst.stock_desc
, amsp.stock_price
, amsp.change
from amv_stocks amst
, amv_stock_price amsp
where amst.stock_symbol = l_stock_symbol
and amst.stock_ric = amsp.stock_ric(+)
order by amsp.time_stamp desc;
PROCEDURE Update_UserTicker
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_object_version_number IN NUMBER,
p_user_id IN NUMBER,
p_symbols IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_UserTicker';
SAVEPOINT Update_UserTicker;
-- build the list of deleted symbols
FOR i in 1..l_user_symbols.count LOOP
IF l_user_symbols(i).exchange is null THEN
l_rec_num := l_rec_num + 1;
Delete_UserStocks( p_tickerid_array => l_del_symbols );
Update_Symbols( p_user_id, l_symbols_array);
ROLLBACK TO Update_UserTicker;
ROLLBACK TO Update_UserTicker;
ROLLBACK TO Update_UserTicker;
END Update_UserTicker;
select a.stock_id
from amv_stocks a
where a.stock_id > p_start_index
minus
select b.stock_id
from amv_vendor_keys b
where b.vendor_id = p_vendor_id
order by 1;
select stock_id
, stock_symbol
, exchange
from amv_stocks
where stock_id = l_stock_id;
PROCEDURE Insert_StockVendorKeys
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_vendor_id IN NUMBER,
p_ticker_rec IN AMV_TKR_OBJ_TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_StockVendorKeys';
select amv_vendor_keys_s.nextval
from dual;
SAVEPOINT Insert_StockVendorKeys;
INSERT INTO amv_vendor_keys(
vendor_key_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
vendor_id,
vendor_key,
stock_id,
effective_start_date
)
VALUES (
l_vendor_key_id,
l_object_version_number,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
p_vendor_id,
l_key_id,
l_stock_id,
sysdate
);
ROLLBACK TO Insert_StockVendorKeys;
ROLLBACK TO Insert_StockVendorKeys;
ROLLBACK TO Insert_StockVendorKeys;
END Insert_StockVendorKeys;
PROCEDURE Get_UserSelectedKeys
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_vendor_id IN NUMBER,
p_all_keys IN VARCHAR2 := FND_API.G_FALSE,
x_keys_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Get_UserSelectedKeys';
select distinct amvk.vendor_key
from amv_vendor_keys amvk
, amv_user_ticker amut
where amut.stock_id = amvk.stock_id
and amvk.vendor_id = p_vendor_id;
select amvk.vendor_key
from amv_vendor_keys amvk
, amv_stocks amst
where amst.stock_symbol = l_stock_symbol
and amst.stock_id = amvk.stock_id;
select distinct amvk.vendor_key
from amv_vendor_keys amvk
where amvk.vendor_id = p_vendor_id;
SAVEPOINT Get_UserSelectedKeys;
-- get user selected keys
OPEN Get_UserKeys_csr;
ROLLBACK TO Get_UserSelectedKeys;
ROLLBACK TO Get_UserSelectedKeys;
ROLLBACK TO Get_UserSelectedKeys;
END Get_UserSelectedKeys;
PROCEDURE Insert_VendorNews
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_vendor_id IN NUMBER,
p_news_rec IN AMV_NEWS_OBJ_TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_VendorNews';
select vendor_key_id
from amv_vendor_keys
where vendor_id = p_vendor_id
and vendor_key = l_key_id;
select amv_news_s.nextval
from dual;
SAVEPOINT Insert_VendorNews;
INSERT INTO amv_news (
news_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
vendor_key_id,
news_url,
news_title,
provider,
date_time
)
VALUES (
l_news_id,
l_object_version_number,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
l_vendor_key_id,
p_news_rec.news_url,
p_news_rec.title,
p_news_rec.provider,
p_news_rec.date_time
);
ROLLBACK TO Insert_VendorNews;
ROLLBACK TO Insert_VendorNews;
ROLLBACK TO Insert_VendorNews;
END Insert_VendorNews;
select v.vendor_key
, n.news_url
, n.news_title
, n.provider
, n.date_time
from amv_news n
, amv_vendor_keys v
where v.stock_id = p_stock_id
and v.vendor_key_id = n.vendor_key_id
order by n.date_time desc;