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