[Home] [Help]
PACKAGE BODY: APPS.AMV_STOCK_LD_PKG
Source
1 package body AMV_STOCK_LD_PKG as
2 /* $Header: amvtstkb.pls 120.1 2005/06/21 17:45:04 appldev ship $ */
3 procedure Load_Row(
4 X_STOCK_ID in VARCHAR2,
5 X_OBJECT_VERSION_NUMBER in VARCHAR2,
6 -- X_OWNER in VARCHAR2,
7 X_STOCK_SYMBOL in VARCHAR2,
8 X_STOCK_RIC in VARCHAR2,
9 X_STOCK_TYPE in VARCHAR2,
10 X_STOCK_DESC in VARCHAR2,
11 X_COUNTRY_ID in VARCHAR2,
12 X_EXCHANGE in VARCHAR2,
13 X_CURRENCY_CODE in VARCHAR2,
14 X_INDUSTRY_CLASS in VARCHAR2,
15 X_ISSUE_TYPE in VARCHAR2
16 ) AS
17 --
18 l_user_id NUMBER := 1;
19 l_row_id VARCHAR2(2000);
20 l_stock_id NUMBER;
21 l_object_version_number NUMBER;
22 l_country_id NUMBER;
23 l_industry_class NUMBER;
24 l_issue_type NUMBER;
25
26 BEGIN
27 l_stock_id := to_number(X_STOCK_ID);
28 l_object_version_number := to_number(X_OBJECT_VERSION_NUMBER);
29 l_country_id := to_number(X_COUNTRY_ID);
30 l_industry_class := to_number(X_INDUSTRY_CLASS);
31 l_issue_type := to_number(X_ISSUE_TYPE);
32
33 UPDATE_ROW(
34 X_STOCK_ID => l_stock_id,
35 X_OBJECT_VERSION_NUMBER => l_object_version_number,
36 X_LAST_UPDATE_DATE => sysdate,
37 X_LAST_UPDATED_BY => l_user_id,
38 X_LAST_UPDATE_LOGIN => 0,
39 X_STOCK_SYMBOL => X_STOCK_SYMBOL,
40 X_STOCK_RIC => X_STOCK_RIC,
41 X_STOCK_TYPE => X_STOCK_TYPE,
42 X_STOCK_DESC => X_STOCK_DESC,
43 X_COUNTRY_ID => l_country_id,
44 X_EXCHANGE => X_EXCHANGE,
45 X_CURRENCY_CODE => X_CURRENCY_CODE,
46 X_INDUSTRY_CLASS => X_INDUSTRY_CLASS,
47 X_ISSUE_TYPE => X_ISSUE_TYPE
48 );
49
50 if (sql%notfound) then
51 raise no_data_found;
52 end if;
53
54 EXCEPTION
55 WHEN no_data_found THEN
56 --dbms_output.put_line('before insert_row');
57 INSERT_ROW(
58 X_ROWID => l_row_id,
59 X_STOCK_ID => l_stock_id,
60 X_OBJECT_VERSION_NUMBER => l_object_version_number,
61 X_LAST_UPDATE_DATE => sysdate,
62 X_LAST_UPDATED_BY => l_user_id,
63 X_CREATION_DATE => sysdate,
64 X_CREATED_BY => l_user_id,
65 X_LAST_UPDATE_LOGIN => 0,
66 X_STOCK_SYMBOL => X_STOCK_SYMBOL,
67 X_STOCK_RIC => X_STOCK_RIC,
68 X_STOCK_TYPE => X_STOCK_TYPE,
69 X_STOCK_DESC => X_STOCK_DESC,
70 X_OBSOLETE_FLAG => FND_API.G_FALSE,
71 X_COUNTRY_ID => l_country_id,
72 X_EXCHANGE => X_EXCHANGE,
73 X_CURRENCY_CODE => X_CURRENCY_CODE,
74 X_INDUSTRY_CLASS => l_industry_class,
75 X_ISSUE_TYPE => l_issue_type
76 );
77 END;
78 procedure INSERT_ROW(
79 X_ROWID in out NOCOPY VARCHAR2,
80 X_STOCK_ID in NUMBER,
81 X_OBJECT_VERSION_NUMBER in NUMBER,
82 X_LAST_UPDATE_DATE in DATE,
83 X_LAST_UPDATED_BY in NUMBER,
84 X_CREATION_DATE in DATE,
85 X_CREATED_BY in NUMBER,
86 X_LAST_UPDATE_LOGIN in NUMBER,
87 X_STOCK_SYMBOL in VARCHAR2,
88 X_STOCK_RIC in VARCHAR2,
89 X_STOCK_TYPE in VARCHAR2,
90 X_STOCK_DESC in VARCHAR2,
91 X_OBSOLETE_FLAG in VARCHAR2,
92 X_COUNTRY_ID in NUMBER,
93 X_EXCHANGE in VARCHAR2,
94 X_CURRENCY_CODE in VARCHAR2,
95 X_INDUSTRY_CLASS in NUMBER,
96 X_ISSUE_TYPE in NUMBER
97 ) AS
98 cursor C is select ROWID from AMV_STOCKS
99 where stock_id = x_stock_id;
100
101 BEGIN
102 insert into AMV_STOCKS (
103 STOCK_ID,
104 OBJECT_VERSION_NUMBER,
105 LAST_UPDATE_DATE,
106 LAST_UPDATED_BY,
107 CREATION_DATE,
108 CREATED_BY,
109 LAST_UPDATE_LOGIN,
110 STOCK_SYMBOL,
111 STOCK_RIC,
112 STOCK_TYPE,
113 STOCK_DESC,
114 OBSOLETE_FLAG,
115 COUNTRY_ID,
116 EXCHANGE,
117 CURRENCY_CODE,
118 INDUSTRY_CLASS,
119 ISSUE_TYPE
120 ) VALUES (
121 X_STOCK_ID,
122 X_OBJECT_VERSION_NUMBER,
123 X_LAST_UPDATE_DATE,
124 X_LAST_UPDATED_BY,
125 X_CREATION_DATE,
126 X_CREATED_BY,
127 X_LAST_UPDATE_LOGIN,
128 X_STOCK_SYMBOL,
129 X_STOCK_RIC,
130 X_STOCK_TYPE,
131 X_STOCK_DESC,
132 X_OBSOLETE_FLAG,
133 X_COUNTRY_ID,
134 X_EXCHANGE,
135 X_CURRENCY_CODE,
136 X_INDUSTRY_CLASS,
137 X_ISSUE_TYPE
138 );
139
140 open c;
141 fetch c into X_ROWID;
142 if (c%notfound) then
143 close c;
144 raise no_data_found;
145 end if;
146 close c;
147 END;
148 procedure UPDATE_ROW(
149 X_STOCK_ID in NUMBER,
150 X_OBJECT_VERSION_NUMBER in NUMBER,
151 X_LAST_UPDATE_DATE in DATE,
152 X_LAST_UPDATED_BY in NUMBER,
153 X_LAST_UPDATE_LOGIN in NUMBER,
154 X_STOCK_SYMBOL in VARCHAR2,
155 X_STOCK_RIC in VARCHAR2,
156 X_STOCK_TYPE in VARCHAR2,
157 X_STOCK_DESC in VARCHAR2,
158 X_COUNTRY_ID in NUMBER,
159 X_EXCHANGE in VARCHAR2,
160 X_CURRENCY_CODE in VARCHAR2,
161 X_INDUSTRY_CLASS in NUMBER,
162 X_ISSUE_TYPE in NUMBER
163 ) AS
164 BEGIN
165 Update AMV_STOCKS SET
166 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
167 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
168 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
169 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
170 STOCK_SYMBOL = X_STOCK_SYMBOL,
171 STOCK_RIC = X_STOCK_RIC,
172 STOCK_TYPE = X_STOCK_TYPE,
173 STOCK_DESC = X_STOCK_DESC,
174 COUNTRY_ID = X_COUNTRY_ID,
175 EXCHANGE = X_EXCHANGE,
176 CURRENCY_CODE = X_CURRENCY_CODE,
177 INDUSTRY_CLASS = X_INDUSTRY_CLASS,
178 ISSUE_TYPE = X_ISSUE_TYPE
179 where stock_id = X_STOCK_ID;
180 END;
181
182 END AMV_STOCK_LD_PKG;