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