[Home] [Help]
PACKAGE BODY: APPS.CSD_RO_DIAGNOSTIC_CODES_PKG
Source
1 PACKAGE BODY CSD_RO_DIAGNOSTIC_CODES_PKG as
2 /* $Header: csdtrdcb.pls 120.1 2006/09/20 00:14:31 rfieldma noship $ */
3 -- Start of Comments
4 -- Package name : CSD_RO_DIAGNOSTIC_CODES_PKG
5 -- Purpose : To insert, update, delete and lock ro diagnostic codes
6 -- History : 25-Aug-2003 Gilam created
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_RO_DIAGNOSTIC_CODES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtrdcb.pls';
13
14 PROCEDURE Insert_Row(
15 px_RO_DIAGNOSTIC_CODE_ID IN OUT NOCOPY NUMBER
16 ,p_OBJECT_VERSION_NUMBER NUMBER
17 ,p_REPAIR_LINE_ID NUMBER
18 ,p_DIAGNOSTIC_CODE_ID NUMBER
19 ,p_CREATED_BY NUMBER
20 ,p_CREATION_DATE DATE
21 ,p_LAST_UPDATED_BY NUMBER
22 ,p_LAST_UPDATE_DATE DATE
23 ,p_LAST_UPDATE_LOGIN NUMBER
24 ,p_ATTRIBUTE_CATEGORY VARCHAR2
25 ,p_ATTRIBUTE1 VARCHAR2
26 ,p_ATTRIBUTE2 VARCHAR2
27 ,p_ATTRIBUTE3 VARCHAR2
28 ,p_ATTRIBUTE4 VARCHAR2
29 ,p_ATTRIBUTE5 VARCHAR2
30 ,p_ATTRIBUTE6 VARCHAR2
31 ,p_ATTRIBUTE7 VARCHAR2
32 ,p_ATTRIBUTE8 VARCHAR2
33 ,p_ATTRIBUTE9 VARCHAR2
34 ,p_ATTRIBUTE10 VARCHAR2
35 ,p_ATTRIBUTE11 VARCHAR2
36 ,p_ATTRIBUTE12 VARCHAR2
37 ,p_ATTRIBUTE13 VARCHAR2
38 ,p_ATTRIBUTE14 VARCHAR2
39 ,p_ATTRIBUTE15 VARCHAR2
40 ,p_DIAGNOSTIC_ITEM_ID NUMBER -- rfieldma, 4666403
41 )
42
43 IS
44 CURSOR C2 IS SELECT CSD_RO_DIAGNOSTIC_CODES_S1.nextval FROM sys.dual;
45 BEGIN
46 If (px_RO_DIAGNOSTIC_CODE_ID IS NULL) OR (px_RO_DIAGNOSTIC_CODE_ID = FND_API.G_MISS_NUM) then
47 OPEN C2;
48 FETCH C2 INTO px_RO_DIAGNOSTIC_CODE_ID;
49 CLOSE C2;
50 End If;
51 INSERT INTO CSD_RO_DIAGNOSTIC_CODES(
52 RO_DIAGNOSTIC_CODE_ID
53 ,OBJECT_VERSION_NUMBER
54 ,REPAIR_LINE_ID
55 ,DIAGNOSTIC_CODE_ID
56 ,CREATED_BY
57 ,CREATION_DATE
58 ,LAST_UPDATED_BY
59 ,LAST_UPDATE_DATE
60 ,LAST_UPDATE_LOGIN
61 ,ATTRIBUTE_CATEGORY
62 ,ATTRIBUTE1
63 ,ATTRIBUTE2
64 ,ATTRIBUTE3
65 ,ATTRIBUTE4
66 ,ATTRIBUTE5
67 ,ATTRIBUTE6
68 ,ATTRIBUTE7
69 ,ATTRIBUTE8
70 ,ATTRIBUTE9
71 ,ATTRIBUTE10
72 ,ATTRIBUTE11
73 ,ATTRIBUTE12
74 ,ATTRIBUTE13
75 ,ATTRIBUTE14
76 ,ATTRIBUTE15
77 ,DIAGNOSTIC_ITEM_ID -- rfieldma, 4666403
78 ) VALUES (
79 px_RO_DIAGNOSTIC_CODE_ID
80 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
81 ,decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_LINE_ID)
82 ,decode( p_DIAGNOSTIC_CODE_ID, FND_API.G_MISS_NUM, NULL, p_DIAGNOSTIC_CODE_ID)
83 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
84 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
85 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
86 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
87 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
88 ,decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
89 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
90 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
91 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
92 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
93 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
94 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
95 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
96 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
97 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
98 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
99 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
100 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
101 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
102 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
103 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
104 ,decode( p_DIAGNOSTIC_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_DIAGNOSTIC_ITEM_ID) -- rfieldma, 4666403
105 );
106 End Insert_Row;
107
108 PROCEDURE Update_Row(
109 p_RO_DIAGNOSTIC_CODE_ID NUMBER
110 ,p_OBJECT_VERSION_NUMBER NUMBER
111 ,p_REPAIR_LINE_ID NUMBER
112 ,p_DIAGNOSTIC_CODE_ID NUMBER
113 ,p_CREATED_BY NUMBER
114 ,p_CREATION_DATE DATE
115 ,p_LAST_UPDATED_BY NUMBER
116 ,p_LAST_UPDATE_DATE DATE
117 ,p_LAST_UPDATE_LOGIN NUMBER
118 ,p_ATTRIBUTE_CATEGORY VARCHAR2
119 ,p_ATTRIBUTE1 VARCHAR2
120 ,p_ATTRIBUTE2 VARCHAR2
121 ,p_ATTRIBUTE3 VARCHAR2
122 ,p_ATTRIBUTE4 VARCHAR2
123 ,p_ATTRIBUTE5 VARCHAR2
124 ,p_ATTRIBUTE6 VARCHAR2
125 ,p_ATTRIBUTE7 VARCHAR2
126 ,p_ATTRIBUTE8 VARCHAR2
127 ,p_ATTRIBUTE9 VARCHAR2
128 ,p_ATTRIBUTE10 VARCHAR2
129 ,p_ATTRIBUTE11 VARCHAR2
130 ,p_ATTRIBUTE12 VARCHAR2
131 ,p_ATTRIBUTE13 VARCHAR2
132 ,p_ATTRIBUTE14 VARCHAR2
133 ,p_ATTRIBUTE15 VARCHAR2
134 ,p_DIAGNOSTIC_ITEM_ID NUMBER -- rfieldma, 4666403
135 )
136
137 IS
138 BEGIN
139 Update CSD_RO_DIAGNOSTIC_CODES
140 SET
141 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
142 ,REPAIR_LINE_ID = decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, NULL, REPAIR_LINE_ID, p_REPAIR_LINE_ID)
143 ,DIAGNOSTIC_CODE_ID = decode( p_DIAGNOSTIC_CODE_ID, FND_API.G_MISS_NUM, NULL, NULL, DIAGNOSTIC_CODE_ID, p_DIAGNOSTIC_CODE_ID)
144 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, p_CREATED_BY)
145 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, p_CREATION_DATE)
146 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
147 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
148 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
149 ,ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY)
150 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1)
151 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2)
152 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3)
153 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4)
154 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5)
155 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6)
156 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7)
157 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8)
158 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9)
159 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10)
160 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11)
161 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12)
162 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13)
163 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14)
164 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
165 ,DIAGNOSTIC_ITEM_ID = decode( p_DIAGNOSTIC_ITEM_ID, FND_API.G_MISS_NUM, NULL, NULL, DIAGNOSTIC_ITEM_ID, p_DIAGNOSTIC_ITEM_ID) -- rfieldma, 4666403
166 where RO_DIAGNOSTIC_CODE_ID = p_RO_DIAGNOSTIC_CODE_ID;
167
168 If (SQL%NOTFOUND) then
169 RAISE NO_DATA_FOUND;
170 End If;
171 END Update_Row;
172
173 PROCEDURE Delete_Row(
174 p_RO_DIAGNOSTIC_CODE_ID NUMBER)
175 IS
176 BEGIN
177 DELETE FROM CSD_RO_DIAGNOSTIC_CODES
178 WHERE RO_DIAGNOSTIC_CODE_ID = p_RO_DIAGNOSTIC_CODE_ID;
179 If (SQL%NOTFOUND) then
180 RAISE NO_DATA_FOUND;
181 End If;
182 END Delete_Row;
183
184 PROCEDURE Lock_Row(
185 p_RO_DIAGNOSTIC_CODE_ID NUMBER
186 ,p_OBJECT_VERSION_NUMBER NUMBER
187
188 --commented out the rest of the record
189 /*
190 ,p_REPAIR_LINE_ID NUMBER
191 ,p_DIAGNOSTIC_CODE_ID NUMBER
192 ,p_CREATED_BY NUMBER
193 ,p_CREATION_DATE DATE
194 ,p_LAST_UPDATED_BY NUMBER
195 ,p_LAST_UPDATE_DATE DATE
196 ,p_LAST_UPDATE_LOGIN NUMBER
197 ,p_ATTRIBUTE_CATEGORY VARCHAR2
198 ,p_ATTRIBUTE1 VARCHAR2
199 ,p_ATTRIBUTE2 VARCHAR2
200 ,p_ATTRIBUTE3 VARCHAR2
201 ,p_ATTRIBUTE4 VARCHAR2
202 ,p_ATTRIBUTE5 VARCHAR2
203 ,p_ATTRIBUTE6 VARCHAR2
204 ,p_ATTRIBUTE7 VARCHAR2
205 ,p_ATTRIBUTE8 VARCHAR2
206 ,p_ATTRIBUTE9 VARCHAR2
207 ,p_ATTRIBUTE10 VARCHAR2
208 ,p_ATTRIBUTE11 VARCHAR2
209 ,p_ATTRIBUTE12 VARCHAR2
210 ,p_ATTRIBUTE13 VARCHAR2
211 ,p_ATTRIBUTE14 VARCHAR2
212 ,p_ATTRIBUTE15 VARCHAR2
213 */
214 --
215 )
216
217 IS
218 CURSOR C IS
219 SELECT *
220 FROM CSD_RO_DIAGNOSTIC_CODES
221 WHERE RO_DIAGNOSTIC_CODE_ID = p_RO_DIAGNOSTIC_CODE_ID
222 FOR UPDATE of RO_DIAGNOSTIC_CODE_ID NOWAIT;
223 Recinfo C%ROWTYPE;
224 BEGIN
225 OPEN C;
226 FETCH C INTO Recinfo;
227 If (C%NOTFOUND) then
228 CLOSE C;
229 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
230 APP_EXCEPTION.RAISE_EXCEPTION;
231 End If;
232 CLOSE C;
233 if (
234 ( Recinfo.RO_DIAGNOSTIC_CODE_ID = p_RO_DIAGNOSTIC_CODE_ID)
235 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
236 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
237 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
238
239 --commented out the rest of the record
240 /*
241 AND ( ( Recinfo.REPAIR_LINE_ID = p_REPAIR_LINE_ID)
242 OR ( ( Recinfo.REPAIR_LINE_ID IS NULL )
243 AND ( p_REPAIR_LINE_ID IS NULL )))
244 AND ( ( Recinfo.DIAGNOSTIC_CODE_ID = p_DIAGNOSTIC_CODE_ID)
245 OR ( ( Recinfo.DIAGNOSTIC_CODE_ID IS NULL )
246 AND ( p_DIAGNOSTIC_CODE_ID IS NULL )))
247 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
248 OR ( ( Recinfo.CREATED_BY IS NULL )
249 AND ( p_CREATED_BY IS NULL )))
250 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
251 OR ( ( Recinfo.CREATION_DATE IS NULL )
252 AND ( p_CREATION_DATE IS NULL )))
253 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
254 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
255 AND ( p_LAST_UPDATED_BY IS NULL )))
256 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
257 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
258 AND ( p_LAST_UPDATE_DATE IS NULL )))
259 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
260 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
261 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
262 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
263 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
264 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
265 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
266 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
267 AND ( p_ATTRIBUTE1 IS NULL )))
268 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
269 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
270 AND ( p_ATTRIBUTE2 IS NULL )))
271 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
272 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
273 AND ( p_ATTRIBUTE3 IS NULL )))
274 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
275 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
276 AND ( p_ATTRIBUTE4 IS NULL )))
277 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
278 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
279 AND ( p_ATTRIBUTE5 IS NULL )))
280 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
281 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
282 AND ( p_ATTRIBUTE6 IS NULL )))
283 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
284 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
285 AND ( p_ATTRIBUTE7 IS NULL )))
286 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
287 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
288 AND ( p_ATTRIBUTE8 IS NULL )))
289 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
290 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
291 AND ( p_ATTRIBUTE9 IS NULL )))
292 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
293 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
294 AND ( p_ATTRIBUTE10 IS NULL )))
295 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
296 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
297 AND ( p_ATTRIBUTE11 IS NULL )))
298 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
299 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
300 AND ( p_ATTRIBUTE12 IS NULL )))
301 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
302 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
303 AND ( p_ATTRIBUTE13 IS NULL )))
304 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
305 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
306 AND ( p_ATTRIBUTE14 IS NULL )))
307 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
308 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
309 AND ( p_ATTRIBUTE15 IS NULL )))
310 */
311 --
312 ) then
313 return;
314 else
315 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
316 APP_EXCEPTION.RAISE_EXCEPTION;
317 End If;
318 END Lock_Row;
319
320 End CSD_RO_DIAGNOSTIC_CODES_PKG;