[Home] [Help]
PACKAGE BODY: APPS.AMS_METRIC_FORMULAS_PKG
Source
1 PACKAGE BODY AMS_METRIC_FORMULAS_PKG as
2 /* $Header: amslmtfb.pls 115.2 2003/09/30 22:47:57 dmvincen noship $ */
3 -- ===============================================================
4 -- Package name
5 -- AMS_METRIC_FORMULAS_PKG
6 -- Purpose
7 --
8 -- History
9 -- 08/20/2003 dmvincen Created.
10 --
11 -- NOTE
12 --
13 -- ===============================================================
14 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
15 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
16 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
17
18 procedure INSERT_ROW (
19 X_ROWID in VARCHAR2,
20 X_METRIC_FORMULA_ID IN NUMBER,
21 X_METRIC_ID IN NUMBER,
22 X_SOURCE_TYPE in VARCHAR2,
23 X_SOURCE_ID IN NUMBER,
24 X_SOURCE_SUB_ID IN NUMBER,
25 X_USE_SUB_ID_FLAG IN VARCHAR2,
26 X_SOURCE_VALUE IN NUMBER,
27 X_TOKEN IN VARCHAR2,
28 X_SEQUENCE IN NUMBER,
29 X_NOTATION_TYPE in VARCHAR2,
30 X_OBJECT_VERSION_NUMBER in NUMBER,
31 X_CREATION_DATE in DATE,
32 X_CREATED_BY in NUMBER,
33 X_LAST_UPDATE_DATE in DATE,
34 X_LAST_UPDATED_BY in NUMBER,
35 X_LAST_UPDATE_LOGIN in NUMBER
36 ) is
37 cursor C is select ROWID from AMS_METRIC_FORMULAS
38 where METRIC_FORMULA_ID = X_METRIC_FORMULA_ID;
39 l_rowid VARCHAR2(1000);
40 begin
41 insert into AMS_METRIC_FORMULAS (
42 METRIC_FORMULA_ID,
43 METRIC_ID,
44 SOURCE_TYPE,
45 SOURCE_ID,
46 SOURCE_SUB_ID,
47 USE_SUB_ID_FLAG,
48 SOURCE_VALUE,
49 TOKEN,
50 SEQUENCE,
51 NOTATION_TYPE,
52 OBJECT_VERSION_NUMBER,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN
58 ) values (
59 X_METRIC_FORMULA_ID,
60 X_METRIC_ID,
61 X_SOURCE_TYPE,
62 X_SOURCE_ID,
63 X_SOURCE_SUB_ID,
64 X_USE_SUB_ID_FLAG,
65 X_SOURCE_VALUE,
66 X_TOKEN,
67 X_SEQUENCE,
68 X_NOTATION_TYPE,
69 X_OBJECT_VERSION_NUMBER,
70 X_CREATION_DATE,
71 X_CREATED_BY,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_LOGIN
75 );
76
77 open c;
78 fetch c into l_ROWID;
79 if (c%notfound) then
80 close c;
81 raise no_data_found;
82 end if;
83 close c;
84
85 end INSERT_ROW;
86
87 procedure LOCK_ROW (
88 X_METRIC_FORMULA_ID IN NUMBER,
89 X_METRIC_ID IN NUMBER,
90 X_SOURCE_TYPE in VARCHAR2,
91 X_SOURCE_ID IN NUMBER,
92 X_SOURCE_SUB_ID IN NUMBER,
93 X_USE_SUB_ID_FLAG IN VARCHAR2,
94 X_SOURCE_VALUE IN NUMBER,
95 X_TOKEN in VARCHAR2,
96 X_SEQUENCE IN NUMBER,
97 X_NOTATION_TYPE in VARCHAR2,
98 X_OBJECT_VERSION_NUMBER in NUMBER
99 ) is
100 cursor c is select
101 OBJECT_VERSION_NUMBER,
102 METRIC_ID,
103 SOURCE_TYPE,
104 SOURCE_ID,
105 SOURCE_SUB_ID,
106 USE_SUB_ID_FLAG,
107 SOURCE_VALUE,
108 TOKEN,
109 SEQUENCE,
110 NOTATION_TYPE
111 from AMS_METRIC_FORMULAS
112 where METRIC_FORMULA_ID = X_METRIC_FORMULA_ID
113 for update of METRIC_FORMULA_ID nowait;
114 recinfo c%rowtype;
115
116 begin
117 open c;
118 fetch c into recinfo;
119 if (c%notfound) then
120 close c;
121 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
122 app_exception.raise_exception;
123 end if;
124 close c;
125 if (((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
126 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
127 AND (recinfo.METRIC_ID = X_METRIC_ID)
128 AND (recinfo.SOURCE_TYPE = X_SOURCE_TYPE)
129 AND (recinfo.SEQUENCE = X_SEQUENCE)
130 AND (recinfo.NOTATION_TYPE = X_NOTATION_TYPE)
131 AND (recinfo.USE_SUB_ID_FLAG = X_USE_SUB_ID_FLAG)
132 AND ((recinfo.SOURCE_ID = X_SOURCE_ID)
133 OR ((recinfo.SOURCE_ID is null) AND (X_SOURCE_ID is null)))
134 AND ((recinfo.SOURCE_SUB_ID = X_SOURCE_SUB_ID)
135 OR ((recinfo.SOURCE_SUB_ID is null) AND (X_SOURCE_SUB_ID is null)))
136 AND ((recinfo.SOURCE_VALUE = X_SOURCE_VALUE)
137 OR ((recinfo.SOURCE_VALUE is null) AND (X_SOURCE_VALUE is null)))
138 AND ((recinfo.TOKEN = X_TOKEN)
139 OR ((recinfo.TOKEN is null) AND (X_TOKEN is null)))
140 ) then
141 null;
142 else
143 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
144 app_exception.raise_exception;
145 end if;
146
147 end LOCK_ROW;
148
149 procedure UPDATE_ROW (
150 X_METRIC_FORMULA_ID IN NUMBER,
151 X_METRIC_ID IN NUMBER,
152 X_SOURCE_TYPE in VARCHAR2,
153 X_SOURCE_ID IN NUMBER,
154 X_SOURCE_SUB_ID IN NUMBER,
155 X_USE_SUB_ID_FLAG IN VARCHAR2,
156 X_SOURCE_VALUE IN NUMBER,
157 X_TOKEN in VARCHAR2,
158 X_SEQUENCE IN NUMBER,
159 X_NOTATION_TYPE in VARCHAR2,
160 X_OBJECT_VERSION_NUMBER in NUMBER,
161 X_LAST_UPDATE_DATE in DATE,
162 X_LAST_UPDATED_BY in NUMBER,
163 X_LAST_UPDATE_LOGIN in NUMBER
164 ) is
165 begin
166 update AMS_METRIC_FORMULAS set
167 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
168 METRIC_ID = X_METRIC_ID,
169 SOURCE_TYPE = X_SOURCE_TYPE,
170 SOURCE_ID = X_SOURCE_ID,
171 SOURCE_SUB_ID = X_SOURCE_SUB_ID,
172 USE_SUB_ID_FLAG = X_USE_SUB_ID_FLAG,
173 SOURCE_VALUE = X_SOURCE_VALUE,
174 TOKEN = X_TOKEN,
175 SEQUENCE = X_SEQUENCE,
176 NOTATION_TYPE = X_NOTATION_TYPE,
177 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
178 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
180 where METRIC_FORMULA_ID = X_METRIC_FORMULA_ID;
181
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185
186 end UPDATE_ROW;
187
188 procedure DELETE_ROW (
189 X_METRIC_FORMULA_ID in NUMBER
190 ) is
191 begin
192 delete from AMS_METRIC_FORMULAS
193 where METRIC_FORMULA_ID = X_METRIC_FORMULA_ID;
194
195 if (sql%notfound) then
196 raise no_data_found;
197 end if;
198
199 end DELETE_ROW;
200
201 procedure LOAD_ROW(
202 X_METRIC_FORMULA_ID IN NUMBER,
203 X_METRIC_ID IN NUMBER,
204 X_SOURCE_TYPE in VARCHAR2,
205 X_SOURCE_ID IN NUMBER,
206 X_SOURCE_SUB_ID IN NUMBER,
207 X_USE_SUB_ID_FLAG IN VARCHAR2,
208 X_SOURCE_VALUE IN NUMBER,
209 X_TOKEN in VARCHAR2,
210 X_SEQUENCE IN NUMBER,
211 X_NOTATION_TYPE in VARCHAR2,
212 X_Owner IN VARCHAR2,
213 X_CUSTOM_MODE IN VARCHAR2
214 ) is
215
216 l_user_id number := 0;
217 l_obj_verno number;
218 l_row_id varchar2(100);
219 l_metric_formula_id number;
220 l_db_luby_id NUMBER;
221
222 cursor c_db_data_details is
223 select last_updated_by, object_version_number
224 from AMS_METRIC_FORMULAS
225 where metric_formula_id = X_METRIC_FORMULA_ID;
226
227 cursor c_get_mtfid is
228 select AMS_METRIC_FORMULAS_S.nextval
229 from dual;
230
231 BEGIN
232
233 -- set the last_updated_by to be used while updating the data in customer data.
234 if X_OWNER = 'SEED' then
235 l_user_id := 1;
236 elsif X_OWNER = 'ORACLE' THEN
237 l_user_id := 2;
238 elsif X_OWNER = 'SYSADMIN' THEN
239 l_user_id := 0;
240 end if ;
241
242 open c_db_data_details;
243 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
244 if c_db_data_details%notfound
245 then
246 close c_db_data_details;
247
248 if X_METRIC_FORMULA_ID is null then
249 open c_get_mtfid;
250 fetch c_get_mtfid into l_metric_formula_id;
251 close c_get_mtfid;
252 else
253 l_metric_formula_id := X_METRIC_FORMULA_ID;
254 end if ;
255
256 l_obj_verno := 1;
257
258 INSERT_ROW (
259 X_ROWID => l_row_id ,
260 X_METRIC_FORMULA_ID => l_metric_formula_id,
261 X_METRIC_ID => X_METRIC_ID,
262 X_SOURCE_TYPE => X_SOURCE_TYPE,
263 X_SOURCE_ID => X_SOURCE_ID,
264 X_SOURCE_SUB_ID => X_SOURCE_SUB_ID,
265 X_USE_SUB_ID_FLAG => X_USE_SUB_ID_FLAG,
266 X_SOURCE_VALUE => X_SOURCE_VALUE,
267 X_TOKEN => X_TOKEN,
268 X_SEQUENCE => X_SEQUENCE,
269 X_NOTATION_TYPE => X_NOTATION_TYPE,
270 X_OBJECT_VERSION_NUMBER => l_obj_verno,
271 X_CREATION_DATE => SYSDATE,
272 X_CREATED_BY => l_user_id,
273 X_LAST_UPDATE_DATE => SYSDATE,
274 X_LAST_UPDATED_BY => l_user_id,
275 X_LAST_UPDATE_LOGIN => 0
276 );
277
278 else
279 close c_db_data_details;
280 if ( l_db_luby_id IN (1, 2, 0)
281 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
282 UPDATE_ROW(
283 X_METRIC_FORMULA_ID => X_METRIC_FORMULA_ID,
284 X_METRIC_ID => X_METRIC_ID,
285 X_SOURCE_TYPE => X_SOURCE_TYPE,
286 X_SOURCE_ID => X_SOURCE_ID,
287 X_SOURCE_SUB_ID => X_SOURCE_SUB_ID,
288 X_USE_SUB_ID_FLAG => X_USE_SUB_ID_FLAG,
289 X_SOURCE_VALUE => X_SOURCE_VALUE,
290 X_TOKEN => X_TOKEN,
291 X_SEQUENCE => X_SEQUENCE,
292 X_NOTATION_TYPE => X_NOTATION_TYPE,
293 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
294 X_LAST_UPDATE_DATE => SYSDATE,
295 X_LAST_UPDATED_BY => l_user_id,
296 X_LAST_UPDATE_LOGIN => 0
297 );
298 end if;
299 end if;
300 END LOAD_ROW;
301
302 end AMS_METRIC_FORMULAS_PKG;