[Home] [Help]
PACKAGE BODY: APPS.CSI_CTR_PROPERTY_READING_PKG
Source
1 PACKAGE BODY CSI_CTR_PROPERTY_READING_PKG as
2 /* $Header: csitrdpb.pls 120.0 2005/06/10 14:19:11 rktow noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_CTR_PROPERTY_READING_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csitrdpb.pls';
6
7 PROCEDURE Insert_Row(
8 px_COUNTER_PROP_VALUE_ID IN OUT NOCOPY NUMBER
9 ,p_COUNTER_VALUE_ID NUMBER
10 ,p_COUNTER_PROPERTY_ID NUMBER
11 ,p_PROPERTY_VALUE VARCHAR2
12 ,p_VALUE_TIMESTAMP DATE
13 ,p_OBJECT_VERSION_NUMBER NUMBER
14 ,p_LAST_UPDATE_DATE DATE
15 ,p_LAST_UPDATED_BY NUMBER
16 ,p_CREATION_DATE DATE
17 ,p_CREATED_BY NUMBER
18 ,p_LAST_UPDATE_LOGIN NUMBER
19 ,p_ATTRIBUTE1 VARCHAR2
20 ,p_ATTRIBUTE2 VARCHAR2
21 ,p_ATTRIBUTE3 VARCHAR2
22 ,p_ATTRIBUTE4 VARCHAR2
23 ,p_ATTRIBUTE5 VARCHAR2
24 ,p_ATTRIBUTE6 VARCHAR2
25 ,p_ATTRIBUTE7 VARCHAR2
26 ,p_ATTRIBUTE8 VARCHAR2
27 ,p_ATTRIBUTE9 VARCHAR2
28 ,p_ATTRIBUTE10 VARCHAR2
29 ,p_ATTRIBUTE11 VARCHAR2
30 ,p_ATTRIBUTE12 VARCHAR2
31 ,p_ATTRIBUTE13 VARCHAR2
32 ,p_ATTRIBUTE14 VARCHAR2
33 ,p_ATTRIBUTE15 VARCHAR2
34 ,p_ATTRIBUTE_CATEGORY VARCHAR2
35 ,p_MIGRATED_FLAG VARCHAR2
36 ) IS
37
38 CURSOR C1 IS
39 SELECT CSI_CTR_PROPERTY_READINGS_S.nextval
40 FROM dual;
41 BEGIN
42 IF (px_COUNTER_PROP_VALUE_ID IS NULL) OR (px_COUNTER_PROP_VALUE_ID = FND_API.G_MISS_NUM) THEN
43 OPEN C1;
44 FETCH C1 INTO px_COUNTER_PROP_VALUE_ID;
45 CLOSE C1;
46 END IF;
47
48 INSERT INTO CSI_CTR_PROPERTY_READINGS(
49 COUNTER_PROP_VALUE_ID
50 ,COUNTER_VALUE_ID
51 ,COUNTER_PROPERTY_ID
52 ,PROPERTY_VALUE
53 ,VALUE_TIMESTAMP
54 ,OBJECT_VERSION_NUMBER
55 ,LAST_UPDATE_DATE
56 ,LAST_UPDATED_BY
57 ,CREATION_DATE
58 ,CREATED_BY
59 ,LAST_UPDATE_LOGIN
60 ,ATTRIBUTE1
61 ,ATTRIBUTE2
62 ,ATTRIBUTE3
63 ,ATTRIBUTE4
64 ,ATTRIBUTE5
65 ,ATTRIBUTE6
66 ,ATTRIBUTE7
67 ,ATTRIBUTE8
68 ,ATTRIBUTE9
69 ,ATTRIBUTE10
70 ,ATTRIBUTE11
71 ,ATTRIBUTE12
72 ,ATTRIBUTE13
73 ,ATTRIBUTE14
74 ,ATTRIBUTE15
75 ,ATTRIBUTE_CATEGORY
76 ,MIGRATED_FLAG
77 )
78 VALUES(
79 px_COUNTER_PROP_VALUE_ID
80 ,decode(p_COUNTER_VALUE_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_VALUE_ID)
81 ,decode(p_COUNTER_PROPERTY_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_PROPERTY_ID)
82 ,decode(p_PROPERTY_VALUE, FND_API.G_MISS_CHAR, NULL, p_PROPERTY_VALUE)
83 ,decode(p_VALUE_TIMESTAMP, FND_API.G_MISS_DATE, TO_DATE(NULL), p_VALUE_TIMESTAMP)
84 ,decode(p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
85 ,decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
86 ,decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
87 ,decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
88 ,decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
89 ,decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
90 ,decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
91 ,decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
92 ,decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
93 ,decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
94 ,decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
95 ,decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
96 ,decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
97 ,decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
98 ,decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
99 ,decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
100 ,decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
101 ,decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
102 ,decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
103 ,decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
104 ,decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
105 ,decode(p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
106 ,decode(p_MIGRATED_FLAG, FND_API.G_MISS_CHAR, NULL, p_MIGRATED_FLAG)
107 );
108
109 END Insert_Row;
110
111 PROCEDURE Update_Row(
112 p_COUNTER_PROP_VALUE_ID NUMBER
113 ,p_COUNTER_VALUE_ID NUMBER
114 ,p_COUNTER_PROPERTY_ID NUMBER
115 ,p_PROPERTY_VALUE VARCHAR2
116 ,p_VALUE_TIMESTAMP DATE
117 ,p_OBJECT_VERSION_NUMBER NUMBER
118 ,p_LAST_UPDATE_DATE DATE
119 ,p_LAST_UPDATED_BY NUMBER
120 ,p_CREATION_DATE DATE
121 ,p_CREATED_BY NUMBER
122 ,p_LAST_UPDATE_LOGIN NUMBER
123 ,p_ATTRIBUTE1 VARCHAR2
124 ,p_ATTRIBUTE2 VARCHAR2
125 ,p_ATTRIBUTE3 VARCHAR2
126 ,p_ATTRIBUTE4 VARCHAR2
127 ,p_ATTRIBUTE5 VARCHAR2
128 ,p_ATTRIBUTE6 VARCHAR2
129 ,p_ATTRIBUTE7 VARCHAR2
130 ,p_ATTRIBUTE8 VARCHAR2
131 ,p_ATTRIBUTE9 VARCHAR2
132 ,p_ATTRIBUTE10 VARCHAR2
133 ,p_ATTRIBUTE11 VARCHAR2
134 ,p_ATTRIBUTE12 VARCHAR2
135 ,p_ATTRIBUTE13 VARCHAR2
136 ,p_ATTRIBUTE14 VARCHAR2
137 ,p_ATTRIBUTE15 VARCHAR2
138 ,p_ATTRIBUTE_CATEGORY VARCHAR2
139 ,p_MIGRATED_FLAG VARCHAR2
140 ) IS
141 BEGIN
142 UPDATE CSI_CTR_PROPERTY_READINGS
143 SET
144 COUNTER_VALUE_ID = decode(p_COUNTER_VALUE_ID, NULL, COUNTER_VALUE_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_VALUE_ID)
145 ,COUNTER_PROPERTY_ID = decode(p_COUNTER_PROPERTY_ID, NULL, COUNTER_PROPERTY_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_PROPERTY_ID)
146 ,PROPERTY_VALUE = decode(p_PROPERTY_VALUE, NULL, PROPERTY_VALUE, FND_API.G_MISS_CHAR, NULL, p_PROPERTY_VALUE)
147 ,VALUE_TIMESTAMP = decode(p_VALUE_TIMESTAMP, NULL, VALUE_TIMESTAMP, FND_API.G_MISS_DATE, NULL, p_VALUE_TIMESTAMP)
148 ,OBJECT_VERSION_NUMBER = decode(p_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
149 ,LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, NULL, LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE)
150 ,LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, NULL, LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
151 ,CREATION_DATE = decode(p_CREATION_DATE, NULL, CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE)
152 ,CREATED_BY = decode(p_CREATED_BY, NULL, CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
153 ,LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN, NULL, LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
154 ,ATTRIBUTE1 = decode(p_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
155 ,ATTRIBUTE2 = decode(p_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
156 ,ATTRIBUTE3 = decode(p_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
157 ,ATTRIBUTE4 = decode(p_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
158 ,ATTRIBUTE5 = decode(p_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
159 ,ATTRIBUTE6 = decode(p_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
160 ,ATTRIBUTE7 = decode(p_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
161 ,ATTRIBUTE8 = decode(p_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
162 ,ATTRIBUTE9 = decode(p_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
163 ,ATTRIBUTE10 = decode(p_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
164 ,ATTRIBUTE11 = decode(p_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
165 ,ATTRIBUTE12 = decode(p_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
166 ,ATTRIBUTE13 = decode(p_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
167 ,ATTRIBUTE14 = decode(p_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
168 ,ATTRIBUTE15 = decode(p_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
169 ,ATTRIBUTE_CATEGORY = decode(p_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
170 ,MIGRATED_FLAG = decode(p_MIGRATED_FLAG, NULL, MIGRATED_FLAG, FND_API.G_MISS_CHAR, NULL, p_MIGRATED_FLAG)
171 WHERE COUNTER_PROP_VALUE_ID = p_COUNTER_PROP_VALUE_ID;
172
173 If (SQL%NOTFOUND) then
174 RAISE NO_DATA_FOUND;
175 End If;
176 END Update_Row;
177
178 PROCEDURE Lock_Row(
179 p_COUNTER_PROP_VALUE_ID NUMBER
180 ,p_COUNTER_VALUE_ID NUMBER
181 ,p_COUNTER_PROPERTY_ID NUMBER
182 ,p_PROPERTY_VALUE VARCHAR2
183 ,p_VALUE_TIMESTAMP DATE
184 ,p_OBJECT_VERSION_NUMBER NUMBER
185 ,p_LAST_UPDATE_DATE DATE
186 ,p_LAST_UPDATED_BY NUMBER
187 ,p_CREATION_DATE DATE
188 ,p_CREATED_BY NUMBER
189 ,p_LAST_UPDATE_LOGIN NUMBER
190 ,p_ATTRIBUTE1 VARCHAR2
191 ,p_ATTRIBUTE2 VARCHAR2
192 ,p_ATTRIBUTE3 VARCHAR2
193 ,p_ATTRIBUTE4 VARCHAR2
194 ,p_ATTRIBUTE5 VARCHAR2
195 ,p_ATTRIBUTE6 VARCHAR2
196 ,p_ATTRIBUTE7 VARCHAR2
197 ,p_ATTRIBUTE8 VARCHAR2
198 ,p_ATTRIBUTE9 VARCHAR2
199 ,p_ATTRIBUTE10 VARCHAR2
200 ,p_ATTRIBUTE11 VARCHAR2
201 ,p_ATTRIBUTE12 VARCHAR2
202 ,p_ATTRIBUTE13 VARCHAR2
203 ,p_ATTRIBUTE14 VARCHAR2
204 ,p_ATTRIBUTE15 VARCHAR2
205 ,p_ATTRIBUTE_CATEGORY VARCHAR2
206 ,p_MIGRATED_FLAG VARCHAR2
207 ) IS
208
209 CURSOR C1 IS
210 SELECT *
211 FROM CSI_CTR_PROPERTY_READINGS
212 WHERE COUNTER_PROP_VALUE_ID = p_COUNTER_PROP_VALUE_ID
213 FOR UPDATE of COUNTER_PROP_VALUE_ID NOWAIT;
214 Recinfo C1%ROWTYPE;
215 BEGIN
216 OPEN C1;
217 FETCH C1 INTO Recinfo;
218 IF (C1%NOTFOUND) THEN
219 CLOSE C1;
220 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
221 APP_EXCEPTION.RAISE_EXCEPTION;
222 END IF;
223 CLOSE C1;
224
225 IF (
226 (Recinfo.COUNTER_PROP_VALUE_ID = p_COUNTER_PROP_VALUE_ID)
227 AND ((Recinfo.COUNTER_VALUE_ID = p_COUNTER_VALUE_ID) OR ((Recinfo.COUNTER_VALUE_ID IS NULL) AND (p_COUNTER_VALUE_ID IS NULL)))
228 AND ((Recinfo.COUNTER_PROPERTY_ID = p_COUNTER_PROPERTY_ID) OR ((Recinfo.COUNTER_PROPERTY_ID IS NULL) AND (p_COUNTER_PROPERTY_ID IS NULL)))
229 AND ((Recinfo.PROPERTY_VALUE = p_PROPERTY_VALUE) OR ((Recinfo.PROPERTY_VALUE IS NULL) AND (p_PROPERTY_VALUE IS NULL)))
230 AND ((Recinfo.VALUE_TIMESTAMP = p_VALUE_TIMESTAMP) OR ((Recinfo.VALUE_TIMESTAMP IS NULL) AND (p_VALUE_TIMESTAMP IS NULL)))
231 AND ((Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL) AND (p_OBJECT_VERSION_NUMBER IS NULL)))
232 AND ((Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) OR ((Recinfo.LAST_UPDATE_DATE IS NULL) AND (p_LAST_UPDATE_DATE IS NULL)))
233 AND ((Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) OR ((Recinfo.LAST_UPDATED_BY IS NULL) AND (p_LAST_UPDATED_BY IS NULL)))
234 AND ((Recinfo.CREATION_DATE = p_CREATION_DATE) OR ((Recinfo.CREATION_DATE IS NULL) AND (p_CREATION_DATE IS NULL)))
235 AND ((Recinfo.CREATED_BY = p_CREATED_BY) OR ((Recinfo.CREATED_BY IS NULL) AND (p_CREATED_BY IS NULL)))
236 AND ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND (p_LAST_UPDATE_LOGIN IS NULL)))
237 AND ((Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1) OR ((Recinfo.ATTRIBUTE1 IS NULL) AND (p_ATTRIBUTE1 IS NULL)))
238 AND ((Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2) OR ((Recinfo.ATTRIBUTE2 IS NULL) AND (p_ATTRIBUTE2 IS NULL)))
239 AND ((Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3) OR ((Recinfo.ATTRIBUTE3 IS NULL) AND (p_ATTRIBUTE3 IS NULL)))
240 AND ((Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4) OR ((Recinfo.ATTRIBUTE4 IS NULL) AND (p_ATTRIBUTE4 IS NULL)))
241 AND ((Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5) OR ((Recinfo.ATTRIBUTE5 IS NULL) AND (p_ATTRIBUTE5 IS NULL)))
242 AND ((Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6) OR ((Recinfo.ATTRIBUTE6 IS NULL) AND (p_ATTRIBUTE6 IS NULL)))
243 AND ((Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7) OR ((Recinfo.ATTRIBUTE7 IS NULL) AND (p_ATTRIBUTE7 IS NULL)))
244 AND ((Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8) OR ((Recinfo.ATTRIBUTE8 IS NULL) AND (p_ATTRIBUTE8 IS NULL)))
245 AND ((Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9) OR ((Recinfo.ATTRIBUTE9 IS NULL) AND (p_ATTRIBUTE9 IS NULL)))
246 AND ((Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10) OR ((Recinfo.ATTRIBUTE10 IS NULL) AND (p_ATTRIBUTE10 IS NULL)))
247 AND ((Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11) OR ((Recinfo.ATTRIBUTE11 IS NULL) AND (p_ATTRIBUTE11 IS NULL)))
248 AND ((Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12) OR ((Recinfo.ATTRIBUTE12 IS NULL) AND (p_ATTRIBUTE12 IS NULL)))
249 AND ((Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13) OR ((Recinfo.ATTRIBUTE13 IS NULL) AND (p_ATTRIBUTE13 IS NULL)))
250 AND ((Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14) OR ((Recinfo.ATTRIBUTE14 IS NULL) AND (p_ATTRIBUTE14 IS NULL)))
251 AND ((Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15) OR ((Recinfo.ATTRIBUTE15 IS NULL) AND (p_ATTRIBUTE15 IS NULL)))
252 AND ((Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY) OR ((Recinfo.ATTRIBUTE_CATEGORY IS NULL) AND (p_ATTRIBUTE_CATEGORY IS NULL)))
256 ELSE
253 AND ((Recinfo.MIGRATED_FLAG = p_MIGRATED_FLAG) OR ((Recinfo.MIGRATED_FLAG IS NULL) AND (p_MIGRATED_FLAG IS NULL)))
254 ) THEN
255 return;
257 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
258 APP_EXCEPTION.RAISE_EXCEPTION;
259 END IF;
260
261 END Lock_Row;
262
263 PROCEDURE Delete_Row(
264 p_COUNTER_PROP_VALUE_ID NUMBER
265 ) IS
266 BEGIN
267 DELETE FROM CSI_CTR_PROPERTY_READINGS
268 WHERE COUNTER_PROP_VALUE_ID = p_COUNTER_PROP_VALUE_ID;
269
270 IF (SQL%NOTFOUND) then
271 RAISE NO_DATA_FOUND;
272 END IF;
273 END Delete_Row;
274
275 End CSI_CTR_PROPERTY_READING_PKG;