[Home] [Help]
PACKAGE BODY: APPS.XTR_HEDGE_PRO_TESTS_PKG
Source
1 PACKAGE BODY XTR_HEDGE_PRO_TESTS_PKG as
2 /* $Header: xtrhprob.pls 120.1 2005/06/29 08:27:31 badiredd noship $ */
3
4 PROCEDURE INSERT_ROW(
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_HEDGE_PRO_TEST_ID NUMBER,
7 X_HEDGE_ATTRIBUTE_ID NUMBER,
8 X_COMPANY_CODE VARCHAR2,
9 X_RESULT_CODE VARCHAR2,
10 X_RESULT_DATE DATE,
11 X_LAST_TEST_DATE DATE,
12 X_PERFORMED_BY NUMBER,
13 X_COMMENTS VARCHAR2,
14 X_ATTRIBUTE_CATEGORY VARCHAR2,
15 X_ATTRIBUTE1 VARCHAR2,
16 X_ATTRIBUTE2 VARCHAR2,
17 X_ATTRIBUTE3 VARCHAR2,
18 X_ATTRIBUTE4 VARCHAR2,
19 X_ATTRIBUTE5 VARCHAR2,
20 X_ATTRIBUTE6 VARCHAR2,
21 X_ATTRIBUTE7 VARCHAR2,
22 X_ATTRIBUTE8 VARCHAR2,
23 X_ATTRIBUTE9 VARCHAR2,
24 X_ATTRIBUTE10 VARCHAR2,
25 X_ATTRIBUTE11 VARCHAR2,
26 X_ATTRIBUTE12 VARCHAR2,
27 X_ATTRIBUTE13 VARCHAR2,
28 X_ATTRIBUTE14 VARCHAR2,
29 X_ATTRIBUTE15 VARCHAR2,
30 X_CREATED_BY NUMBER,
31 X_CREATION_DATE DATE,
32 X_LAST_UPDATED_BY NUMBER,
33 X_LAST_UPDATE_DATE DATE,
34 X_LAST_UPDATE_LOGIN NUMBER) IS
35 cursor C is select ROWID from XTR_HEDGE_PRO_TESTS
36 where HEDGE_PRO_TEST_ID = X_HEDGE_PRO_TEST_ID;
37
38 BEGIN
39 INSERT INTO XTR_HEDGE_PRO_TESTS(
40 HEDGE_PRO_TEST_ID,
41 HEDGE_ATTRIBUTE_ID,
42 COMPANY_CODE,
43 RESULT_CODE,
44 RESULT_DATE,
45 LAST_TEST_DATE,
46 PERFORMED_BY,
47 COMMENTS,
48 ATTRIBUTE_CATEGORY,
49 ATTRIBUTE1,
50 ATTRIBUTE2,
51 ATTRIBUTE3,
52 ATTRIBUTE4,
53 ATTRIBUTE5,
54 ATTRIBUTE6,
55 ATTRIBUTE7,
56 ATTRIBUTE8,
57 ATTRIBUTE9,
58 ATTRIBUTE10,
59 ATTRIBUTE11,
60 ATTRIBUTE12,
61 ATTRIBUTE13,
62 ATTRIBUTE14,
63 ATTRIBUTE15,
64 CREATED_BY,
65 CREATION_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATE_LOGIN)
69 VALUES (X_HEDGE_PRO_TEST_ID,
70 X_HEDGE_ATTRIBUTE_ID,
71 X_COMPANY_CODE,
72 X_RESULT_CODE,
73 X_RESULT_DATE,
74 X_LAST_TEST_DATE,
75 X_PERFORMED_BY,
76 X_COMMENTS,
77 X_ATTRIBUTE_CATEGORY,
78 X_ATTRIBUTE1,
79 X_ATTRIBUTE2,
80 X_ATTRIBUTE3,
81 X_ATTRIBUTE4,
82 X_ATTRIBUTE5,
83 X_ATTRIBUTE6,
84 X_ATTRIBUTE7,
85 X_ATTRIBUTE8,
86 X_ATTRIBUTE9,
87 X_ATTRIBUTE10,
88 X_ATTRIBUTE11,
89 X_ATTRIBUTE12,
90 X_ATTRIBUTE13,
91 X_ATTRIBUTE14,
92 X_ATTRIBUTE15,
93 X_CREATED_BY,
94 X_CREATION_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATE_LOGIN);
98
99 OPEN C;
100 FETCH C INTO X_ROWID;
101 IF (C%NOTFOUND) THEN
102 CLOSE C;
103 RAISE NO_DATA_FOUND;
104 END IF;
105 CLOSE C;
106
107 END INSERT_ROW;
108
109 PROCEDURE DELETE_ROW (X_HEDGE_PRO_TEST_ID NUMBER) IS
110
111 BEGIN
112 -- DELETE THE ROW, SINCE NOT USED ANYWHERE ELSE
113 DELETE FROM XTR_HEDGE_PRO_TESTS
114 WHERE HEDGE_PRO_TEST_ID = X_HEDGE_PRO_TEST_ID;
115 IF (SQL%NOTFOUND) THEN
116 RAISE NO_DATA_FOUND;
117 END IF;
118 END DELETE_ROW;
119
120
121 PROCEDURE UPDATE_ROW(
122 X_ROWID VARCHAR2,
123 X_HEDGE_PRO_TEST_ID NUMBER,
124 X_HEDGE_ATTRIBUTE_ID NUMBER,
125 X_COMPANY_CODE VARCHAR2,
126 X_RESULT_CODE VARCHAR2,
127 X_RESULT_DATE DATE,
128 X_LAST_TEST_DATE DATE,
129 X_PERFORMED_BY NUMBER,
130 X_COMMENTS VARCHAR2,
131 X_ATTRIBUTE_CATEGORY VARCHAR2,
132 X_ATTRIBUTE1 VARCHAR2,
133 X_ATTRIBUTE2 VARCHAR2,
134 X_ATTRIBUTE3 VARCHAR2,
135 X_ATTRIBUTE4 VARCHAR2,
136 X_ATTRIBUTE5 VARCHAR2,
137 X_ATTRIBUTE6 VARCHAR2,
138 X_ATTRIBUTE7 VARCHAR2,
139 X_ATTRIBUTE8 VARCHAR2,
140 X_ATTRIBUTE9 VARCHAR2,
141 X_ATTRIBUTE10 VARCHAR2,
142 X_ATTRIBUTE11 VARCHAR2,
143 X_ATTRIBUTE12 VARCHAR2,
144 X_ATTRIBUTE13 VARCHAR2,
145 X_ATTRIBUTE14 VARCHAR2,
146 X_ATTRIBUTE15 VARCHAR2,
147 X_LAST_UPDATED_BY NUMBER,
148 X_LAST_UPDATE_DATE DATE,
149 X_LAST_UPDATE_LOGIN NUMBER) IS
150 BEGIN
151 UPDATE XTR_HEDGE_PRO_TESTS
152 SET
153 HEDGE_PRO_TEST_ID = X_HEDGE_PRO_TEST_ID,
154 HEDGE_ATTRIBUTE_ID = X_HEDGE_ATTRIBUTE_ID,
155 COMPANY_CODE = X_COMPANY_CODE,
156 RESULT_CODE = X_RESULT_CODE,
157 RESULT_DATE = X_RESULT_DATE,
158 LAST_TEST_DATE = X_LAST_TEST_DATE,
159 PERFORMED_BY = X_PERFORMED_BY,
160 COMMENTS = X_COMMENTS,
161 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
162 ATTRIBUTE1 = X_ATTRIBUTE1,
163 ATTRIBUTE2 = X_ATTRIBUTE2,
164 ATTRIBUTE3 = X_ATTRIBUTE3,
165 ATTRIBUTE4 = X_ATTRIBUTE4,
166 ATTRIBUTE5 = X_ATTRIBUTE5,
167 ATTRIBUTE6 = X_ATTRIBUTE6,
168 ATTRIBUTE7 = X_ATTRIBUTE7,
169 ATTRIBUTE8 = X_ATTRIBUTE8,
170 ATTRIBUTE9 = X_ATTRIBUTE9,
171 ATTRIBUTE10 = X_ATTRIBUTE10,
172 ATTRIBUTE11 = X_ATTRIBUTE11,
173 ATTRIBUTE12 = X_ATTRIBUTE12,
174 ATTRIBUTE13 = X_ATTRIBUTE13,
175 ATTRIBUTE14 = X_ATTRIBUTE14,
176 ATTRIBUTE15 = X_ATTRIBUTE15,
177 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
178 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
179 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
180 WHERE HEDGE_PRO_TEST_ID = X_HEDGE_PRO_TEST_ID;
181
182 IF (SQL%NOTFOUND) THEN
183 RAISE NO_DATA_FOUND;
184 END IF;
185 END UPDATE_ROW;
186
187 PROCEDURE LOCK_ROW (
188 X_ROWID VARCHAR2,
189 X_HEDGE_PRO_TEST_ID NUMBER,
190 X_HEDGE_ATTRIBUTE_ID NUMBER,
191 X_COMPANY_CODE VARCHAR2,
192 X_RESULT_CODE VARCHAR2,
193 X_RESULT_DATE DATE,
194 X_LAST_TEST_DATE DATE,
195 X_PERFORMED_BY NUMBER,
196 X_COMMENTS VARCHAR2,
197 X_ATTRIBUTE_CATEGORY VARCHAR2,
198 X_ATTRIBUTE1 VARCHAR2,
199 X_ATTRIBUTE2 VARCHAR2,
200 X_ATTRIBUTE3 VARCHAR2,
201 X_ATTRIBUTE4 VARCHAR2,
202 X_ATTRIBUTE5 VARCHAR2,
203 X_ATTRIBUTE6 VARCHAR2,
204 X_ATTRIBUTE7 VARCHAR2,
205 X_ATTRIBUTE8 VARCHAR2,
206 X_ATTRIBUTE9 VARCHAR2,
207 X_ATTRIBUTE10 VARCHAR2,
208 X_ATTRIBUTE11 VARCHAR2,
209 X_ATTRIBUTE12 VARCHAR2,
210 X_ATTRIBUTE13 VARCHAR2,
211 X_ATTRIBUTE14 VARCHAR2,
212 X_ATTRIBUTE15 VARCHAR2) IS
213
214 CURSOR C IS
215 SELECT *
216 FROM XTR_HEDGE_PRO_TESTS
217 WHERE HEDGE_PRO_TEST_ID = X_HEDGE_PRO_TEST_ID
218 FOR UPDATE OF RESULT_CODE NOWAIT;
219 RECINFO C%ROWTYPE;
220
221 BEGIN
222 OPEN C;
223 FETCH C INTO RECINFO;
224 IF (C%NOTFOUND) THEN
225 CLOSE C;
226 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
227 APP_EXCEPTION.RAISE_EXCEPTION;
228 END IF;
229 CLOSE C;
230
231 IF (
232 (RECINFO.HEDGE_PRO_TEST_ID = X_HEDGE_PRO_TEST_ID)
233 AND (RECINFO.HEDGE_ATTRIBUTE_ID= X_HEDGE_ATTRIBUTE_ID)
234 AND (RECINFO.COMPANY_CODE = X_COMPANY_CODE)
235 AND ( (RECINFO.RESULT_CODE = X_RESULT_CODE)
236 OR ( (RECINFO.RESULT_CODE IS NULL)
237 AND (X_RESULT_CODE IS NULL)))
238 AND ( (RECINFO.RESULT_DATE = X_RESULT_DATE)
239 OR ( (RECINFO.RESULT_DATE IS NULL)
240 AND (X_RESULT_DATE IS NULL)))
241 AND ( (RECINFO.LAST_TEST_DATE = X_LAST_TEST_DATE)
242 OR ( (RECINFO.LAST_TEST_DATE IS NULL)
243 AND (X_LAST_TEST_DATE IS NULL)))
244 AND ( (RECINFO.PERFORMED_BY = X_PERFORMED_BY)
245 OR ( (RECINFO.PERFORMED_BY IS NULL)
246 AND (X_PERFORMED_BY IS NULL)))
247 AND ( (RECINFO.COMMENTS = X_COMMENTS)
248 OR ( (RECINFO.COMMENTS IS NULL)
249 AND (X_COMMENTS IS NULL)))
250 AND ( (RECINFO.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
251 OR ( (RECINFO.ATTRIBUTE_CATEGORY IS NULL)
252 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
253 AND ( (RECINFO.ATTRIBUTE1 = X_ATTRIBUTE1)
254 OR ( (RECINFO.ATTRIBUTE1 IS NULL)
255 AND (X_ATTRIBUTE1 IS NULL)))
256 AND ( (RECINFO.ATTRIBUTE2 = X_ATTRIBUTE2)
257 OR ( (RECINFO.ATTRIBUTE2 IS NULL)
258 AND (X_ATTRIBUTE2 IS NULL)))
259 AND ( (RECINFO.ATTRIBUTE3 = X_ATTRIBUTE3)
260 OR ( (RECINFO.ATTRIBUTE3 IS NULL)
261 AND (X_ATTRIBUTE3 IS NULL)))
262 AND ( (RECINFO.ATTRIBUTE4 = X_ATTRIBUTE4)
263 OR ( (RECINFO.ATTRIBUTE4 IS NULL)
264 AND (X_ATTRIBUTE4 IS NULL)))
265 AND ( (RECINFO.ATTRIBUTE5 = X_ATTRIBUTE5)
266 OR ( (RECINFO.ATTRIBUTE5 IS NULL)
267 AND (X_ATTRIBUTE5 IS NULL)))
268 AND ( (RECINFO.ATTRIBUTE6 = X_ATTRIBUTE6)
269 OR ( (RECINFO.ATTRIBUTE6 IS NULL)
270 AND (X_ATTRIBUTE6 IS NULL)))
271 AND ( (RECINFO.ATTRIBUTE7 = X_ATTRIBUTE7)
272 OR ( (RECINFO.ATTRIBUTE7 IS NULL)
273 AND (X_ATTRIBUTE7 IS NULL)))
274 AND ( (RECINFO.ATTRIBUTE8 = X_ATTRIBUTE8)
275 OR ( (RECINFO.ATTRIBUTE8 IS NULL)
276 AND (X_ATTRIBUTE8 IS NULL)))
277 AND ( (RECINFO.ATTRIBUTE9 = X_ATTRIBUTE9)
278 OR ( (RECINFO.ATTRIBUTE9 IS NULL)
279 AND (X_ATTRIBUTE9 IS NULL)))
280 AND ( (RECINFO.ATTRIBUTE10 = X_ATTRIBUTE10)
281 OR ( (RECINFO.ATTRIBUTE10 IS NULL)
282 AND (X_ATTRIBUTE10 IS NULL)))
283 AND ( (RECINFO.ATTRIBUTE11 = X_ATTRIBUTE11)
284 OR ( (RECINFO.ATTRIBUTE11 IS NULL)
285 AND (X_ATTRIBUTE11 IS NULL)))
286 AND ( (RECINFO.ATTRIBUTE12 = X_ATTRIBUTE12)
287 OR ( (RECINFO.ATTRIBUTE12 IS NULL)
288 AND (X_ATTRIBUTE12 IS NULL)))
289 AND ( (RECINFO.ATTRIBUTE13 = X_ATTRIBUTE13)
290 OR ( (RECINFO.ATTRIBUTE13 IS NULL)
291 AND (X_ATTRIBUTE13 IS NULL)))
292 AND ( (RECINFO.ATTRIBUTE14 = X_ATTRIBUTE14)
293 OR ( (RECINFO.ATTRIBUTE14 IS NULL)
294 AND (X_ATTRIBUTE14 IS NULL)))
295 AND ( (RECINFO.ATTRIBUTE15 = X_ATTRIBUTE15)
296 OR ( (RECINFO.ATTRIBUTE15 IS NULL)
297 AND (X_ATTRIBUTE15 IS NULL)))
298
299 ) THEN
300 RETURN;
301 ELSE
302 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
303 APP_EXCEPTION.RAISE_EXCEPTION;
304 END IF;
305 END LOCK_ROW;
306
307 END XTR_HEDGE_PRO_TESTS_PKG;
308