1 PACKAGE body CS_SR_RES_CODE_MAP_DETAIL_PKG as
2 /* $Header: csrcmpdb.pls 120.0 2006/03/01 17:41:34 aseethep noship $ */
3
4 procedure INSERT_ROW (
5 PX_RESOLUTION_MAP_DETAIL_ID IN OUT NOCOPY NUMBER,
6 P_RESOLUTION_MAP_ID in NUMBER,
7 P_INCIDENT_TYPE_ID in NUMBER,
8 P_INVENTORY_ITEM_ID in NUMBER,
9 P_ORGANIZATION_ID in NUMBER,
10 P_CATEGORY_ID in NUMBER,
11 P_PROBLEM_CODE in VARCHAR2,
12 P_MAP_START_DATE_ACTIVE in DATE,
13 P_MAP_END_DATE_ACTIVE in DATE,
14 P_RESOLUTION_CODE in VARCHAR2,
15 P_START_DATE_ACTIVE in DATE,
16 P_END_DATE_ACTIVE in DATE,
17 P_OBJECT_VERSION_NUMBER in NUMBER,
18 P_ATTRIBUTE1 in VARCHAR2,
19 P_ATTRIBUTE2 in VARCHAR2,
20 P_ATTRIBUTE3 in VARCHAR2,
21 P_ATTRIBUTE4 in VARCHAR2,
22 P_ATTRIBUTE5 in VARCHAR2,
23 P_ATTRIBUTE6 in VARCHAR2,
24 P_ATTRIBUTE7 in VARCHAR2,
25 P_ATTRIBUTE8 in VARCHAR2,
26 P_ATTRIBUTE9 in VARCHAR2,
27 P_ATTRIBUTE10 in VARCHAR2,
28 P_ATTRIBUTE11 in VARCHAR2,
29 P_ATTRIBUTE12 in VARCHAR2,
30 P_ATTRIBUTE13 in VARCHAR2,
31 P_ATTRIBUTE14 in VARCHAR2,
32 P_ATTRIBUTE15 in VARCHAR2,
33 P_ATTRIBUTE_CATEGORY in VARCHAR2,
34 P_CREATION_DATE in DATE,
35 P_CREATED_BY in NUMBER,
36 P_LAST_UPDATE_DATE in DATE,
37 P_LAST_UPDATED_BY in NUMBER,
38 P_LAST_UPDATE_LOGIN in NUMBER,
39 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
40 X_MSG_COUNT OUT NOCOPY NUMBER,
41 X_MSG_DATA OUT NOCOPY VARCHAR2
42 )
43 is
44 cursor c1 is
45 select cs_sr_res_code_mapping_dtl_s.nextval
46 from dual;
47
48 BEGIN
49 if ( PX_RESOLUTION_MAP_DETAIL_ID IS NULL ) OR ( PX_RESOLUTION_MAP_DETAIL_ID = FND_API.G_MISS_NUM) THEN
50 open c1;
51 fetch c1 into PX_RESOLUTION_MAP_DETAIL_ID;
52 close c1;
53 end if;
54
55 insert into CS_SR_RES_CODE_MAPPING_DETAIL (
56 RESOLUTION_MAP_DETAIL_ID,
57 RESOLUTION_MAP_ID,
58 INCIDENT_TYPE_ID,
59 INVENTORY_ITEM_ID,
60 ORGANIZATION_ID,
61 CATEGORY_ID,
62 PROBLEM_CODE,
63 MAP_START_DATE_ACTIVE,
64 MAP_END_DATE_ACTIVE,
65 RESOLUTION_CODE,
66 START_DATE_ACTIVE,
67 END_DATE_ACTIVE,
68 OBJECT_VERSION_NUMBER,
69 LAST_UPDATE_DATE,
70 LAST_UPDATED_BY,
71 CREATION_DATE,
72 CREATED_BY,
73 LAST_UPDATE_LOGIN,
74 ATTRIBUTE1,
75 ATTRIBUTE2,
76 ATTRIBUTE3,
77 ATTRIBUTE4,
78 ATTRIBUTE5,
79 ATTRIBUTE6,
80 ATTRIBUTE7,
81 ATTRIBUTE8,
82 ATTRIBUTE9,
83 ATTRIBUTE10,
84 ATTRIBUTE11,
85 ATTRIBUTE12,
86 ATTRIBUTE13,
87 ATTRIBUTE14,
88 ATTRIBUTE15,
89 ATTRIBUTE_CATEGORY
90 ) values (
91 PX_RESOLUTION_MAP_DETAIL_ID,
92 P_RESOLUTION_MAP_ID,
93 P_INCIDENT_TYPE_ID,
94 P_INVENTORY_ITEM_ID,
95 P_ORGANIZATION_ID,
96 P_CATEGORY_ID,
97 P_PROBLEM_CODE,
98 P_MAP_START_DATE_ACTIVE,
99 P_MAP_END_DATE_ACTIVE,
100 P_RESOLUTION_CODE,
101 P_START_DATE_ACTIVE,
102 P_END_DATE_ACTIVE,
103 P_OBJECT_VERSION_NUMBER,
104 P_LAST_UPDATE_DATE,
105 P_LAST_UPDATED_BY,
106 P_CREATION_DATE,
107 P_CREATED_BY,
108 P_LAST_UPDATE_LOGIN,
109 P_ATTRIBUTE1,
110 P_ATTRIBUTE2,
111 P_ATTRIBUTE3,
112 P_ATTRIBUTE4,
113 P_ATTRIBUTE5,
114 P_ATTRIBUTE6,
115 P_ATTRIBUTE7,
116 P_ATTRIBUTE8,
117 P_ATTRIBUTE9,
118 P_ATTRIBUTE10,
119 P_ATTRIBUTE11,
120 P_ATTRIBUTE12,
121 P_ATTRIBUTE13,
122 P_ATTRIBUTE14,
123 P_ATTRIBUTE15,
124 P_ATTRIBUTE_CATEGORY);
125
126 end INSERT_ROW;
127
128 procedure LOCK_ROW (
129 P_RESOLUTION_MAP_DETAIL_ID in NUMBER,
130 P_RESOLUTION_MAP_ID in NUMBER,
131 P_INCIDENT_TYPE_ID in NUMBER,
132 P_INVENTORY_ITEM_ID in NUMBER,
133 P_ORGANIZATION_ID in NUMBER,
134 P_CATEGORY_ID in NUMBER,
135 P_PROBLEM_CODE in VARCHAR2,
136 P_MAP_START_DATE_ACTIVE in DATE,
137 P_MAP_END_DATE_ACTIVE in DATE,
138 P_RESOLUTION_CODE in VARCHAR2,
139 P_START_DATE_ACTIVE in DATE,
140 P_END_DATE_ACTIVE in DATE,
141 P_OBJECT_VERSION_NUMBER in NUMBER,
142 P_ATTRIBUTE1 in VARCHAR2,
143 P_ATTRIBUTE2 in VARCHAR2,
144 P_ATTRIBUTE3 in VARCHAR2,
145 P_ATTRIBUTE4 in VARCHAR2,
146 P_ATTRIBUTE5 in VARCHAR2,
147 P_ATTRIBUTE6 in VARCHAR2,
148 P_ATTRIBUTE7 in VARCHAR2,
149 P_ATTRIBUTE8 in VARCHAR2,
150 P_ATTRIBUTE9 in VARCHAR2,
151 P_ATTRIBUTE10 in VARCHAR2,
152 P_ATTRIBUTE11 in VARCHAR2,
153 P_ATTRIBUTE12 in VARCHAR2,
154 P_ATTRIBUTE13 in VARCHAR2,
155 P_ATTRIBUTE14 in VARCHAR2,
156 P_ATTRIBUTE15 in VARCHAR2,
157 P_ATTRIBUTE_CATEGORY in VARCHAR2
158 ) is
159 cursor c1 is select
160 RESOLUTION_MAP_DETAIL_ID,
161 RESOLUTION_MAP_ID,
162 INCIDENT_TYPE_ID,
163 INVENTORY_ITEM_ID,
164 ORGANIZATION_ID,
165 CATEGORY_ID,
166 PROBLEM_CODE,
167 MAP_START_DATE_ACTIVE,
168 MAP_END_DATE_ACTIVE,
169 RESOLUTION_CODE,
170 START_DATE_ACTIVE,
171 END_DATE_ACTIVE,
172 OBJECT_VERSION_NUMBER,
173 ATTRIBUTE1,
174 ATTRIBUTE2,
175 ATTRIBUTE3,
176 ATTRIBUTE4,
177 ATTRIBUTE5,
178 ATTRIBUTE6,
179 ATTRIBUTE7,
180 ATTRIBUTE8,
181 ATTRIBUTE9,
182 ATTRIBUTE10,
183 ATTRIBUTE11,
184 ATTRIBUTE12,
185 ATTRIBUTE13,
186 ATTRIBUTE14,
187 ATTRIBUTE15,
188 ATTRIBUTE_CATEGORY
189 from CS_SR_RES_CODE_MAPPING_DETAIL
190 where RESOLUTION_MAP_DETAIL_ID = P_RESOLUTION_MAP_DETAIL_ID
191 for update of RESOLUTION_MAP_DETAIL_ID nowait;
192 begin
193 for tlinfo in c1 loop
194 if ( (tlinfo.RESOLUTION_MAP_DETAIL_ID = P_RESOLUTION_MAP_DETAIL_ID)
195 AND (tlinfo.RESOLUTION_MAP_ID = P_RESOLUTION_MAP_ID)
196 AND ((tlinfo.INCIDENT_TYPE_ID = P_INCIDENT_TYPE_ID)
197 OR ((tlinfo.INCIDENT_TYPE_ID is null) AND (P_INCIDENT_TYPE_ID is null)))
198 AND ((tlinfo.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID)
199 OR ((tlinfo.INVENTORY_ITEM_ID is null) AND (P_INVENTORY_ITEM_ID is null)))
200 AND ((tlinfo.ORGANIZATION_ID = P_ORGANIZATION_ID)
201 OR ((tlinfo.ORGANIZATION_ID is null) AND (P_ORGANIZATION_ID is null)))
202 AND ((tlinfo.CATEGORY_ID = P_CATEGORY_ID)
203 OR ((tlinfo.CATEGORY_ID is null) AND (P_CATEGORY_ID is null)))
204 AND ((tlinfo.PROBLEM_CODE = P_PROBLEM_CODE)
205 OR ((tlinfo.PROBLEM_CODE is null) AND (P_PROBLEM_CODE is null)))
206 AND ((tlinfo.MAP_START_DATE_ACTIVE = P_MAP_START_DATE_ACTIVE)
207 OR ((tlinfo.MAP_START_DATE_ACTIVE is null) AND (P_MAP_START_DATE_ACTIVE is null)))
208 AND ((tlinfo.MAP_END_DATE_ACTIVE = P_MAP_END_DATE_ACTIVE)
209 OR ((tlinfo.MAP_END_DATE_ACTIVE is null) AND (P_MAP_END_DATE_ACTIVE is null)))
210 AND (tlinfo.RESOLUTION_CODE = P_RESOLUTION_CODE)
211 AND ((tlinfo.START_DATE_ACTIVE = P_START_DATE_ACTIVE)
212 OR ((tlinfo.START_DATE_ACTIVE is null) AND (P_START_DATE_ACTIVE is null)))
213 AND ((tlinfo.END_DATE_ACTIVE = P_END_DATE_ACTIVE)
214 OR ((tlinfo.END_DATE_ACTIVE is null) AND (P_END_DATE_ACTIVE is null)))
215 AND ((tlinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
216 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (P_OBJECT_VERSION_NUMBER is null)))
217 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
218 OR ((tlinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
219 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
220 OR ((tlinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
221 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
222 OR ((tlinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
223 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
224 OR ((tlinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
225 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
226 OR ((tlinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
227 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
228 OR ((tlinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
229 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
230 OR ((tlinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
231 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
232 OR ((tlinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
233 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
234 OR ((tlinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
235 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
236 OR ((tlinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
237 AND ((tlinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
238 OR ((tlinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
239 AND ((tlinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
240 OR ((tlinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
241 AND ((tlinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
242 OR ((tlinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
243 AND ((tlinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
244 OR ((tlinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
245 AND ((tlinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
246 OR ((tlinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
247 AND ((tlinfo.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
248 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (P_ATTRIBUTE_CATEGORY is null)))
249 ) then
250 null;
251 else
252 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
253 app_exception.raise_exception;
254 end if;
255 end loop;
256 return;
257 end LOCK_ROW;
258
259 procedure UPDATE_ROW (
260 P_RESOLUTION_MAP_DETAIL_ID in NUMBER,
261 P_RESOLUTION_MAP_ID in NUMBER,
262 P_INCIDENT_TYPE_ID in NUMBER,
263 P_INVENTORY_ITEM_ID in NUMBER,
264 P_ORGANIZATION_ID in NUMBER,
265 P_CATEGORY_ID in NUMBER,
266 P_PROBLEM_CODE in VARCHAR2,
267 P_MAP_START_DATE_ACTIVE in DATE,
268 P_MAP_END_DATE_ACTIVE in DATE,
269 P_RESOLUTION_CODE in VARCHAR2,
270 P_START_DATE_ACTIVE in DATE,
271 P_END_DATE_ACTIVE in DATE,
272 P_OBJECT_VERSION_NUMBER in NUMBER,
273 P_ATTRIBUTE1 in VARCHAR2,
274 P_ATTRIBUTE2 in VARCHAR2,
275 P_ATTRIBUTE3 in VARCHAR2,
276 P_ATTRIBUTE4 in VARCHAR2,
277 P_ATTRIBUTE5 in VARCHAR2,
278 P_ATTRIBUTE6 in VARCHAR2,
279 P_ATTRIBUTE7 in VARCHAR2,
280 P_ATTRIBUTE8 in VARCHAR2,
281 P_ATTRIBUTE9 in VARCHAR2,
282 P_ATTRIBUTE10 in VARCHAR2,
283 P_ATTRIBUTE11 in VARCHAR2,
284 P_ATTRIBUTE12 in VARCHAR2,
285 P_ATTRIBUTE13 in VARCHAR2,
286 P_ATTRIBUTE14 in VARCHAR2,
287 P_ATTRIBUTE15 in VARCHAR2,
288 P_ATTRIBUTE_CATEGORY in VARCHAR2,
289 P_LAST_UPDATE_DATE in DATE,
290 P_LAST_UPDATED_BY in NUMBER,
291 P_LAST_UPDATE_LOGIN in NUMBER,
292 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
293 X_MSG_COUNT OUT NOCOPY NUMBER,
294 X_MSG_DATA OUT NOCOPY VARCHAR2
295 ) is
296 begin
297 update CS_SR_RES_CODE_MAPPING_DETAIL set
298 RESOLUTION_MAP_ID = P_RESOLUTION_MAP_ID,
299 INCIDENT_TYPE_ID = P_INCIDENT_TYPE_ID,
300 INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID,
301 ORGANIZATION_ID = P_ORGANIZATION_ID,
302 CATEGORY_ID = P_CATEGORY_ID,
303 PROBLEM_CODE = P_PROBLEM_CODE,
304 MAP_START_DATE_ACTIVE = P_MAP_START_DATE_ACTIVE,
305 MAP_END_DATE_ACTIVE = P_MAP_END_DATE_ACTIVE,
306 RESOLUTION_CODE = P_RESOLUTION_CODE,
307 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
308 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
309 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
310 ATTRIBUTE1 = P_ATTRIBUTE1,
311 ATTRIBUTE2 = P_ATTRIBUTE2,
312 ATTRIBUTE3 = P_ATTRIBUTE3,
313 ATTRIBUTE4 = P_ATTRIBUTE4,
314 ATTRIBUTE5 = P_ATTRIBUTE5,
315 ATTRIBUTE6 = P_ATTRIBUTE6,
316 ATTRIBUTE7 = P_ATTRIBUTE7,
317 ATTRIBUTE8 = P_ATTRIBUTE8,
318 ATTRIBUTE9 = P_ATTRIBUTE9,
319 ATTRIBUTE10 = P_ATTRIBUTE10,
323 ATTRIBUTE14 = P_ATTRIBUTE14,
320 ATTRIBUTE11 = P_ATTRIBUTE11,
321 ATTRIBUTE12 = P_ATTRIBUTE12,
322 ATTRIBUTE13 = P_ATTRIBUTE13,
324 ATTRIBUTE15 = P_ATTRIBUTE15,
325 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
326 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
327 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
328 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
329 where RESOLUTION_MAP_DETAIL_ID = P_RESOLUTION_MAP_DETAIL_ID;
330
331 if (sql%notfound) then
332 raise no_data_found;
333 end if;
334 end UPDATE_ROW;
335
336 procedure DELETE_ROW (
337 P_RESOLUTION_MAP_DETAIL_ID in NUMBER
338 ) is
339 begin
340 delete from CS_SR_RES_CODE_MAPPING_DETAIL
341 where RESOLUTION_MAP_DETAIL_ID = P_RESOLUTION_MAP_DETAIL_ID;
342
343 if (sql%notfound) then
344 raise no_data_found;
345 end if;
346
347 end DELETE_ROW;
348
349
350
351
352 end CS_SR_RES_CODE_MAP_DETAIL_PKG;
[Home] [Help]