[Home] [Help]
PACKAGE BODY: APPS.CSM_SR_ATTRIBUTES_PKG
Source
1 PACKAGE BODY CSM_SR_ATTRIBUTES_PKG AS
2 /* $Header: csmusrab.pls 120.1 2011/01/10 10:31:33 saradhak noship $ */
3 error EXCEPTION;
4
5 PROCEDURE APPLY_SR_LINK_INSERT
9 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
6 (p_HA_PAYLOAD_ID IN NUMBER,
7 p_COL_NAME_LIST IN CSM_VARCHAR_LIST,
8 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
10 X_Error_Message Out Nocopy Varchar2
11 )
12 AS
13 --Variable Declarations
14 L_Api_Version Number := 2.0;
15 L_Init_Msg_List Varchar2(100);
16 L_User_Id Number;
17 L_Login_Id Number;
18 L_Link_Rec Cs_Incidentlinks_Pub.Cs_Incident_Link_Rec_Type;
19 L_Object_Version_Number Number;
20 L_Reciprocal_Link_Id Number;
21 L_LINK_ID NUMBER ;
22 l_return_status VARCHAR2(1);
23 l_msg_count NUMBER;
24 l_msg_data VARCHAR2(2000);
25
26
27 BEGIN
28 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
29 CSM_UTIL_PKG.LOG('Entering CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_INSERT for HA ID ' || p_HA_PAYLOAD_ID ,
30 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_INSERT',FND_LOG.LEVEL_PROCEDURE);
31
32
33 -- prepare the SR link for the given SR link
34
35 FOR i in 1..p_COL_NAME_LIST.COUNT-1 LOOP
36
37 IF p_COL_VALUE_LIST(i) IS NOT NULL THEN
38
39 If p_Col_Name_List(I) = 'LINK_ID' Then
40 L_Link_Rec.LINK_ID := p_COL_VALUE_LIST(i);
41 ELSIF p_COL_NAME_LIST(i) = 'SUBJECT_ID' THEN
42 L_Link_Rec.SUBJECT_ID := p_COL_VALUE_LIST(i);
43 ELSIF p_COL_NAME_LIST(i) = 'SUBJECT_TYPE' THEN
44 L_Link_Rec.SUBJECT_TYPE := p_COL_VALUE_LIST(i);
45 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_ID' THEN
46 L_Link_Rec.OBJECT_ID := p_COL_VALUE_LIST(i);
47 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_NUMBER' THEN
48 L_Link_Rec.OBJECT_NUMBER := p_COL_VALUE_LIST(i);
49 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_TYPE' THEN
50 L_Link_Rec.OBJECT_TYPE := p_Col_Value_List(I);
51 ELSIF p_COL_NAME_LIST(i) = 'LINK_TYPE_ID' THEN
52 L_Link_Rec.LINK_TYPE_ID := p_COL_VALUE_LIST(i);
53 ELSIF p_COL_NAME_LIST(i) = 'LINK_TYPE' THEN
54 L_Link_Rec.LINK_TYPE := p_Col_Value_List(I);
55 ELSIF p_COL_NAME_LIST(i) = 'REQUEST_ID' THEN
56 L_Link_Rec.REQUEST_ID := p_COL_VALUE_LIST(i);
57 ELSIF p_COL_NAME_LIST(i) = 'PROGRAM_APPLICATION_ID' THEN
58 L_Link_Rec.PROGRAM_APPLICATION_ID := p_Col_Value_List(I);
59 ELSIF p_COL_NAME_LIST(i) = 'PROGRAM_ID' THEN
60 L_Link_Rec.PROGRAM_ID := p_Col_Value_List(I);
61 ELSIF p_COL_NAME_LIST(i) = 'PROGRAM_UPDATE_DATE' THEN
62 L_Link_Rec.PROGRAM_UPDATE_DATE := p_Col_Value_List(I);
63 Elsif p_Col_Name_List(I) = 'ATTRIBUTE1' Then
64 L_Link_Rec.LINK_SEGMENT1 := p_Col_Value_List(I);
65 Elsif p_Col_Name_List(I) = 'ATTRIBUTE2' Then
66 L_Link_Rec.LINK_SEGMENT2 := p_Col_Value_List(I);
67 Elsif p_Col_Name_List(I) = 'ATTRIBUTE3' Then
68 L_Link_Rec.LINK_SEGMENT3 := p_Col_Value_List(I);
69 Elsif p_Col_Name_List(I) = 'ATTRIBUTE4' Then
70 L_Link_Rec.Link_Segment4 := p_Col_Value_List(I);
71 Elsif p_Col_Name_List(I) = 'ATTRIBUTE5' Then
72 L_Link_Rec.LINK_SEGMENT5 := p_Col_Value_List(I);
73 Elsif p_Col_Name_List(I) = 'ATTRIBUTE6' Then
74 L_Link_Rec.LINK_SEGMENT6 := p_Col_Value_List(I);
75 Elsif p_Col_Name_List(I) = 'ATTRIBUTE7' Then
76 L_Link_Rec.LINK_SEGMENT7 := p_Col_Value_List(I);
77 Elsif p_Col_Name_List(I) = 'ATTRIBUTE8' Then
78 L_Link_Rec.LINK_SEGMENT8 := p_Col_Value_List(I);
79 Elsif p_Col_Name_List(I) = 'ATTRIBUTE9' Then
80 L_Link_Rec.Link_Segment9 := p_Col_Value_List(I);
81 Elsif p_Col_Name_List(I) = 'ATTRIBUTE10' Then
82 L_Link_Rec.LINK_SEGMENT10 := p_Col_Value_List(I);
83 Elsif p_Col_Name_List(I) = 'ATTRIBUTE11' Then
84 L_Link_Rec.Link_Segment11 := p_Col_Value_List(I);
85 Elsif p_Col_Name_List(I) = 'ATTRIBUTE12' Then
86 L_Link_Rec.LINK_SEGMENT12 := p_Col_Value_List(I);
87 Elsif p_Col_Name_List(I) = 'ATTRIBUTE13' Then
88 L_Link_Rec.Link_Segment13 := p_Col_Value_List(I);
89 Elsif p_Col_Name_List(I) = 'ATTRIBUTE14' Then
90 L_Link_Rec.Link_Segment14 := p_Col_Value_List(I);
91 Elsif p_Col_Name_List(I) = 'ATTRIBUTE15' Then
92 L_Link_Rec.LINK_SEGMENT15 := p_Col_Value_List(I);
93 ELSIF P_COL_NAME_LIST(I) = 'CONTEXT' THEN
94 L_LINK_REC.LINK_CONTEXT := P_COL_VALUE_LIST(I);
95
96 END IF;
97 End If;
98 END LOOP;
99
100 --Call the SR link Api
101 Cs_Incidentlinks_Pub.Create_Incidentlink (
102 P_Api_Version => L_Api_Version,
103 P_Init_Msg_List => L_Init_Msg_List,
104 P_Commit => NULL,
105 P_Resp_Appl_Id => Null, -- not used
106 P_Resp_Id => Null, -- not used
107 P_USER_ID => FND_GLOBAL.USER_ID,
108 P_Login_Id => NULL,
109 P_Org_Id => Null, -- not used
110 P_Link_Rec => L_Link_Rec,
111 X_Return_Status => l_return_status,
112 X_Msg_Count => l_msg_count,
113 X_Msg_Data => l_msg_data,
114 X_Object_Version_Number => L_Object_Version_Number, -- new for 1159
115 X_Reciprocal_Link_Id => L_Reciprocal_Link_Id, -- new for 1159
116 X_Link_Id => L_LINK_ID);
117
118 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
119 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
120 X_Error_Message := l_Msg_Data;
121 ELSE
122 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
123 END IF;
124 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_INSERT for HA ID ' || p_HA_PAYLOAD_ID ,
125 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_INSERT',Fnd_Log.Level_Procedure);
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
130 X_Error_Message := L_Msg_Data;
131 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_INSERT for HA ID ' || p_HA_PAYLOAD_ID ,
132 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_INSERT',FND_LOG.LEVEL_PROCEDURE);
133
134 END APPLY_SR_LINK_INSERT;
135
136 ---sr link update
137
138 PROCEDURE APPLY_SR_LINK_UPDATE
139 (p_HA_PAYLOAD_ID IN NUMBER,
140 p_COL_NAME_LIST IN CSM_VARCHAR_LIST,
141 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
142 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
143 X_Error_Message Out Nocopy Varchar2
144 )
145 AS
146 --Variable Declarations
147 L_Api_Version Number := 2.0;
148 L_Init_Msg_List Varchar2(100);
149 L_User_Id Number;
150 L_Login_Id Number;
151 L_Link_Rec Cs_Incidentlinks_Pub.Cs_Incident_Link_Rec_Type;
152 L_Object_Version_Number Number;
153 L_Reciprocal_Link_Id Number;
154 L_LINK_ID NUMBER ;
155 l_return_status VARCHAR2(1);
156 l_msg_count NUMBER;
157 l_msg_data VARCHAR2(2000);
158
159 CURSOR C_GET_LINK_VERSION( B_LINK_ID NUMBER)
160 IS
161 SELECT OBJECT_VERSION_NUMBER
162 FROM CS_INCIDENT_LINKS
163 WHERE LINK_ID = B_LINK_ID;
164
165
166 BEGIN
167 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
168 CSM_UTIL_PKG.LOG('Entering CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_UPDATE for HA ID ' || p_HA_PAYLOAD_ID ,
169 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_UPDATE',FND_LOG.LEVEL_PROCEDURE);
170
171
172 -- prepare the SR link for the given SR link
173
174 FOR i in 1..p_COL_NAME_LIST.COUNT-1 LOOP
175
176 IF p_COL_VALUE_LIST(i) IS NOT NULL THEN
177
178 If p_Col_Name_List(I) = 'LINK_ID' Then
179 L_LINK_REC.LINK_ID := P_COL_VALUE_LIST(I);
180 L_LINK_ID := P_COL_VALUE_LIST(I);
181 ELSIF p_COL_NAME_LIST(i) = 'SUBJECT_ID' THEN
182 L_Link_Rec.SUBJECT_ID := p_COL_VALUE_LIST(i);
183 ELSIF p_COL_NAME_LIST(i) = 'SUBJECT_TYPE' THEN
184 L_Link_Rec.SUBJECT_TYPE := p_COL_VALUE_LIST(i);
185 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_ID' THEN
186 L_Link_Rec.OBJECT_ID := p_COL_VALUE_LIST(i);
187 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_NUMBER' THEN
188 L_Link_Rec.OBJECT_NUMBER := p_COL_VALUE_LIST(i);
189 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_TYPE' THEN
190 L_Link_Rec.OBJECT_TYPE := p_Col_Value_List(I);
191 ELSIF p_COL_NAME_LIST(i) = 'LINK_TYPE_ID' THEN
192 L_Link_Rec.LINK_TYPE_ID := p_COL_VALUE_LIST(i);
193 ELSIF p_COL_NAME_LIST(i) = 'LINK_TYPE' THEN
194 L_Link_Rec.LINK_TYPE := p_Col_Value_List(I);
195 ELSIF p_COL_NAME_LIST(i) = 'REQUEST_ID' THEN
196 L_Link_Rec.REQUEST_ID := p_COL_VALUE_LIST(i);
197 ELSIF p_COL_NAME_LIST(i) = 'PROGRAM_APPLICATION_ID' THEN
198 L_Link_Rec.PROGRAM_APPLICATION_ID := p_Col_Value_List(I);
199 ELSIF p_COL_NAME_LIST(i) = 'PROGRAM_ID' THEN
200 L_Link_Rec.PROGRAM_ID := p_Col_Value_List(I);
201 ELSIF p_COL_NAME_LIST(i) = 'PROGRAM_UPDATE_DATE' THEN
202 L_Link_Rec.PROGRAM_UPDATE_DATE := p_Col_Value_List(I);
203 Elsif p_Col_Name_List(I) = 'ATTRIBUTE1' Then
204 L_Link_Rec.LINK_SEGMENT1 := p_Col_Value_List(I);
205 Elsif p_Col_Name_List(I) = 'ATTRIBUTE2' Then
206 L_Link_Rec.LINK_SEGMENT2 := p_Col_Value_List(I);
207 Elsif p_Col_Name_List(I) = 'ATTRIBUTE3' Then
208 L_Link_Rec.LINK_SEGMENT3 := p_Col_Value_List(I);
209 Elsif p_Col_Name_List(I) = 'ATTRIBUTE4' Then
210 L_Link_Rec.Link_Segment4 := p_Col_Value_List(I);
211 Elsif p_Col_Name_List(I) = 'ATTRIBUTE5' Then
212 L_Link_Rec.LINK_SEGMENT5 := p_Col_Value_List(I);
213 Elsif p_Col_Name_List(I) = 'ATTRIBUTE6' Then
214 L_Link_Rec.LINK_SEGMENT6 := p_Col_Value_List(I);
215 Elsif p_Col_Name_List(I) = 'ATTRIBUTE7' Then
216 L_Link_Rec.LINK_SEGMENT7 := p_Col_Value_List(I);
217 Elsif p_Col_Name_List(I) = 'ATTRIBUTE8' Then
218 L_Link_Rec.LINK_SEGMENT8 := p_Col_Value_List(I);
219 Elsif p_Col_Name_List(I) = 'ATTRIBUTE9' Then
220 L_Link_Rec.Link_Segment9 := p_Col_Value_List(I);
221 Elsif p_Col_Name_List(I) = 'ATTRIBUTE10' Then
222 L_Link_Rec.LINK_SEGMENT10 := p_Col_Value_List(I);
223 Elsif p_Col_Name_List(I) = 'ATTRIBUTE11' Then
224 L_Link_Rec.Link_Segment11 := p_Col_Value_List(I);
225 Elsif p_Col_Name_List(I) = 'ATTRIBUTE12' Then
226 L_Link_Rec.LINK_SEGMENT12 := p_Col_Value_List(I);
227 Elsif p_Col_Name_List(I) = 'ATTRIBUTE13' Then
228 L_Link_Rec.Link_Segment13 := p_Col_Value_List(I);
229 Elsif p_Col_Name_List(I) = 'ATTRIBUTE14' Then
230 L_Link_Rec.Link_Segment14 := p_Col_Value_List(I);
231 Elsif p_Col_Name_List(I) = 'ATTRIBUTE15' Then
232 L_Link_Rec.LINK_SEGMENT15 := p_Col_Value_List(I);
233 Elsif p_Col_Name_List(I) = 'CONTEXT' Then
234 L_Link_Rec.Link_Context := p_Col_Value_List(I);
235 ELSIF P_COL_NAME_LIST(I) = 'OBJECT_VERSION_NUMBER' THEN
236 L_Object_Version_Number := p_Col_Value_List(I);
237 END IF;
238 End If;
239 END LOOP;
240
241 --Get the Latest Version number from the DB
242 OPEN C_GET_LINK_VERSION( L_LINK_ID );
243 FETCH C_GET_LINK_VERSION INTO L_OBJECT_VERSION_NUMBER;
244 CLOSE C_GET_LINK_VERSION;
245
246 --Call the SR link Api
247 Cs_Incidentlinks_Pub.UPDATE_INCIDENTLINK (
248 P_Api_Version => L_Api_Version,
249 P_Init_Msg_List => L_Init_Msg_List,
250 P_Commit => NULL,
251 P_Resp_Appl_Id => Null, -- not used
252 P_Resp_Id => Null, -- not used
253 P_User_Id => Fnd_Global.User_Id,
254 P_Login_Id => NULL,
255 P_ORG_ID => NULL, -- not used
256 P_LINK_ID => L_LINK_ID,
257 P_LINK_REC => L_LINK_REC,
258 P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
259 X_Return_Status => l_return_status,
260 X_Msg_Count => l_msg_count,
261 X_Msg_Data => l_msg_data,
262 X_Object_Version_Number => L_Object_Version_Number -- new for 1159
266 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
263 );
264
265 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
267 X_Error_Message := l_Msg_Data;
268 ELSE
269 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
270 END IF;
271 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_UPDATE for HA ID ' || p_HA_PAYLOAD_ID ,
272 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_UPDATE',Fnd_Log.Level_Procedure);
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
277 X_Error_Message := L_Msg_Data;
278 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_UPDATE for HA ID ' || p_HA_PAYLOAD_ID ,
279 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_UPDATE',FND_LOG.LEVEL_PROCEDURE);
280
281 END APPLY_SR_LINK_UPDATE;
282 --sr link update ends
283 --sr link delete starts
284 PROCEDURE APPLY_SR_LINK_DELETE
285 (p_HA_PAYLOAD_ID IN NUMBER,
286 p_COL_NAME_LIST IN CSM_VARCHAR_LIST,
287 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
288 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
289 X_Error_Message Out Nocopy Varchar2
290 )
291 AS
292 --Variable Declarations
293 L_Api_Version Number := 2.0;
294 L_Init_Msg_List Varchar2(100);
295 L_User_Id Number;
296 L_Login_Id Number;
297 L_LINK_ID NUMBER ;
298 l_return_status VARCHAR2(1);
299 l_msg_count NUMBER;
300 l_msg_data VARCHAR2(2000);
301
302
303 BEGIN
304 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
305 CSM_UTIL_PKG.LOG('Entering CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_DELETE for HA ID ' || p_HA_PAYLOAD_ID ,
306 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_DELETE',FND_LOG.LEVEL_PROCEDURE);
307
308
309 -- prepare the SR link for the given SR link
310 L_API_VERSION := 2.0;
311 FOR i in 1..p_COL_NAME_LIST.COUNT-1 LOOP
312
313 IF p_COL_VALUE_LIST(i) IS NOT NULL THEN
314
315 If p_Col_Name_List(I) = 'LINK_ID' Then
316 L_LINK_ID := P_COL_VALUE_LIST(I);
317 EXIT;
318 END IF;
319 End If;
320 END LOOP;
321
322 --Call the SR link Api
323 Cs_Incidentlinks_Pub.DELETE_INCIDENTLINK (
324 P_Api_Version => L_Api_Version,
325 P_Init_Msg_List => FND_API.G_FALSE,
326 P_Commit => FND_API.G_FALSE,
327 P_Resp_Appl_Id => Null, -- not used
328 P_Resp_Id => Null, -- not used
329 P_User_Id => Fnd_Global.User_Id,
330 P_Login_Id => NULL,
331 P_ORG_ID => NULL, -- not used
332 P_LINK_ID => L_LINK_ID,
333 X_Return_Status => l_return_status,
334 X_Msg_Count => l_msg_count,
335 X_Msg_Data => l_msg_data
336 );
337
338 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
339 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
340 X_Error_Message := l_Msg_Data;
341 ELSE
342 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
343 END IF;
344 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_DELETE for HA ID ' || p_HA_PAYLOAD_ID ,
345 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_DELETE',Fnd_Log.Level_Procedure);
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
350 X_Error_Message := L_Msg_Data;
351 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_DELETE for HA ID ' || p_HA_PAYLOAD_ID ,
352 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_LINK_DELETE',FND_LOG.LEVEL_PROCEDURE);
353
354 END APPLY_SR_LINK_DELETE;
355 --sr link delete ends
356 --SR Attributes insert
357 PROCEDURE APPLY_SR_ATTR_INSERT
358 (p_HA_PAYLOAD_ID IN NUMBER,
359 p_Col_Name_List In Csm_Varchar_List,
360 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
361 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
362 X_Error_Message Out Nocopy Varchar2
363 )
364 AS
365 --Variable Declarations
366 L_Rowid Varchar2(150);
367 L_Incidnt_Attr_Val_Id Number;
368 L_Object_Version_Number Number;
369 L_Incident_Id Number;
370 L_Sr_Attribute_Code Varchar2(30);
371 L_Override_Addr_Valid_Flag Varchar2(1);
372 L_Attribute1 Varchar2(150);
373 L_Attribute2 Varchar2(150);
374 L_Attribute3 Varchar2(150);
375 L_Attribute4 Varchar2(150);
376 L_Attribute5 Varchar2(150);
377 L_ATTRIBUTE6 VARCHAR2(150);
378 L_Attribute7 Varchar2(150);
379 L_Attribute8 Varchar2(150);
380 L_Attribute9 Varchar2(150);
381 L_ATTRIBUTE10 VARCHAR2(150);
382 L_Attribute11 Varchar2(150);
383 L_Attribute12 Varchar2(150);
384 L_Attribute13 Varchar2(150);
385 L_ATTRIBUTE14 VARCHAR2(150);
386 L_Attribute15 Varchar2(150);
387 L_Attribute_Category Varchar2(30);
388 L_SR_ATTRIBUTE_VALUE VARCHAR2(150);
389 L_CREATION_DATE DATE;
390 L_CREATED_BY NUMBER;
391 L_LAST_UPDATE_DATE DATE;
392 L_Last_Updated_By Number;
393 L_Last_Update_Login Number;
394 l_return_status VARCHAR2(1);
395 l_msg_count NUMBER;
396 l_msg_data VARCHAR2(2000);
397
398 BEGIN
399 l_return_status := Fnd_Api.G_Ret_Sts_Success;
400 CSM_UTIL_PKG.LOG('Entering CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_INSERT for HA ID ' || p_HA_PAYLOAD_ID ,
401 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_INSERT',FND_LOG.LEVEL_PROCEDURE);
402
403
404 -- prepare the SR link for the given SR link
405
406 FOR i in 1..p_COL_NAME_LIST.COUNT-1 LOOP
407
408 IF p_COL_VALUE_LIST(i) IS NOT NULL THEN
409
413 L_OBJECT_VERSION_NUMBER := P_COL_NAME_LIST(I);
410 If p_Col_Name_List(I) = 'INCIDNT_ATTR_VAL_ID' Then
411 L_Incidnt_Attr_Val_Id := P_COL_NAME_LIST(i);
412 ELSIF p_COL_NAME_LIST(i) = 'OBJECT_VERSION_NUMBER' THEN
414 ELSIF p_COL_NAME_LIST(i) = 'INCIDENT_ID' THEN
415 L_Incident_Id := P_COL_NAME_LIST(i);
416 ELSIF p_COL_NAME_LIST(i) = 'SR_ATTRIBUTE_CODE' THEN
417 L_Sr_Attribute_Code := P_COL_NAME_LIST(i);
418 ELSIF p_COL_NAME_LIST(i) = 'OVERRIDE_ADDR_VALID_FLAG' THEN
419 L_Override_Addr_Valid_Flag := P_COL_NAME_LIST(i);
420 ELSIF P_COL_NAME_LIST(i) = 'CREATION_DATE' THEN
421 L_CREATION_DATE := P_COL_NAME_LIST(I);
422 ELSIF P_COL_NAME_LIST(i) = 'CREATED_BY' THEN
423 L_CREATED_BY := P_COL_NAME_LIST(i);
424 ELSIF P_COL_NAME_LIST(i) = 'LAST_UPDATE_DATE' THEN
425 L_LAST_UPDATE_DATE := P_COL_NAME_LIST(I);
426 Elsif P_COL_NAME_LIST(I) = 'LAST_UPDATED_BY' Then
427 L_Last_Updated_By:= P_COL_NAME_LIST(i);
428 ELSIF P_COL_NAME_LIST(i) = 'LAST_UPDATE_LOGIN' THEN
429 L_Last_Update_Login := P_COL_NAME_LIST(I);
430 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE1' Then
431 L_Attribute1 := P_COL_NAME_LIST(I);
432 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE2' Then
433 L_Attribute2 := P_COL_NAME_LIST(I);
434 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE3' Then
435 L_Attribute3 := P_COL_NAME_LIST(I);
436 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE4' Then
437 L_Attribute4 := P_COL_NAME_LIST(I);
438 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE5' Then
439 L_Attribute5 := P_COL_NAME_LIST(I);
440 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE6' Then
441 L_Attribute6 := P_COL_NAME_LIST(I);
442 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE7' Then
443 L_Attribute7 := P_COL_NAME_LIST(I);
444 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE8' Then
445 L_Attribute8 := P_COL_NAME_LIST(I);
446 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE9' Then
447 L_Attribute9 := P_COL_NAME_LIST(I);
448 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE10' Then
449 L_Attribute10 := P_COL_NAME_LIST(I);
450 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE11' Then
451 L_Attribute11 := P_COL_NAME_LIST(I);
452 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE12' Then
453 L_Attribute12 := P_COL_NAME_LIST(I);
454 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE13' Then
455 L_Attribute13 := P_COL_NAME_LIST(I);
456 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE14' Then
457 L_Attribute14 := P_COL_NAME_LIST(I);
458 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE15' Then
459 L_Attribute15 := P_COL_NAME_LIST(I);
460 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE_CATEGORY' Then
461 L_Attribute_Category := P_COL_NAME_LIST(I);
462 END IF;
463 End If;
464 END LOOP;
465
466 --Call the SR attribute Api
467 Cug_Incidnt_Attr_Vals_Pkg.Insert_Row (
468 X_Rowid => L_Rowid,
469 X_Incidnt_Attr_Val_Id => L_Incidnt_Attr_Val_Id,
470 X_Object_Version_Number => L_Object_Version_Number,
471 X_Incident_Id => L_Incident_Id,
472 X_Sr_Attribute_Code => L_Sr_Attribute_Code,
473 X_Override_Addr_Valid_Flag => L_Override_Addr_Valid_Flag,
474 X_Attribute1 => L_Attribute1,
475 X_Attribute2 => L_Attribute2,
476 X_Attribute3 => L_Attribute3,
477 X_Attribute4 => L_Attribute4,
478 X_Attribute5 => L_Attribute5,
479 X_Attribute6 => L_Attribute6,
480 X_Attribute7 => L_Attribute7,
481 X_Attribute8 => L_Attribute8,
482 X_Attribute9 => L_Attribute9,
483 X_Attribute10 => L_Attribute10,
484 X_Attribute11 => L_Attribute11,
485 X_Attribute12 => L_Attribute12,
486 X_Attribute13 => L_Attribute13,
487 X_Attribute14 => L_Attribute14,
488 X_Attribute15 => L_Attribute15,
489 X_Attribute_Category => L_Attribute_Category,
490 X_Sr_Attribute_Value => L_SR_ATTRIBUTE_VALUE,
491 X_Creation_Date => L_Creation_Date,
492 X_Created_By => L_Last_Updated_By,
493 X_Last_Update_Date => L_Last_Update_Date,
494 X_Last_Updated_By => L_Last_Updated_By,
495 X_Last_Update_Login => L_Last_Update_Login
496 ) ;
497
498 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
499 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
500 X_Error_Message := l_Msg_Data;
501 ELSE
502 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
503 END IF;
504 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_INSERT for HA ID ' || p_HA_PAYLOAD_ID ,
505 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_INSERT',Fnd_Log.Level_Procedure);
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 x_return_status := FND_API.G_Ret_Sts_Error;
510 X_Error_Message := L_Msg_Data;
511 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_INSERT for HA ID ' || p_HA_PAYLOAD_ID ,
512 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_INSERT',FND_LOG.LEVEL_PROCEDURE);
513
514 End APPLY_SR_ATTR_INSERT;
515
516 --SR Attributes UPDATE
517 PROCEDURE APPLY_SR_ATTR_UPDATE
518 (p_HA_PAYLOAD_ID IN NUMBER,
519 p_Col_Name_List In Csm_Varchar_List,
520 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
521 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
522 X_Error_Message Out Nocopy Varchar2
523 )
524 AS
525 --Variable Declarations
526 L_Rowid Varchar2(150);
527 L_Incidnt_Attr_Val_Id Number;
528 L_Object_Version_Number Number;
529 L_Incident_Id Number;
530 L_Sr_Attribute_Code Varchar2(30);
531 L_Override_Addr_Valid_Flag Varchar2(1);
532 L_Attribute1 Varchar2(150);
536 L_Attribute5 Varchar2(150);
533 L_Attribute2 Varchar2(150);
534 L_Attribute3 Varchar2(150);
535 L_Attribute4 Varchar2(150);
537 L_ATTRIBUTE6 VARCHAR2(150);
538 L_Attribute7 Varchar2(150);
539 L_Attribute8 Varchar2(150);
540 L_Attribute9 Varchar2(150);
541 L_ATTRIBUTE10 VARCHAR2(150);
542 L_Attribute11 Varchar2(150);
543 L_Attribute12 Varchar2(150);
544 L_Attribute13 Varchar2(150);
545 L_ATTRIBUTE14 VARCHAR2(150);
546 L_Attribute15 Varchar2(150);
547 L_Attribute_Category Varchar2(30);
548 L_SR_ATTRIBUTE_VALUE VARCHAR2(150);
549 L_CREATION_DATE DATE;
550 L_CREATED_BY NUMBER;
551 L_LAST_UPDATE_DATE DATE;
552 L_Last_Updated_By Number;
553 L_Last_Update_Login Number;
554 l_return_status VARCHAR2(1);
555 l_msg_count NUMBER;
556 l_msg_data VARCHAR2(2000);
557
558 CURSOR C_GET_SRA_VERSION( B_INCIDNT_ATTR_VAL_ID NUMBER)
559 IS
560 SELECT OBJECT_VERSION_NUMBER
561 FROM CUG_INCIDNT_ATTR_VALS_B
562 WHERE INCIDNT_ATTR_VAL_ID = B_INCIDNT_ATTR_VAL_ID;
563
564 BEGIN
565 l_return_status := Fnd_Api.G_Ret_Sts_Success;
566 CSM_UTIL_PKG.LOG('Entering CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_UPDATE for HA ID ' || p_HA_PAYLOAD_ID ,
567 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_UPDATE',FND_LOG.LEVEL_PROCEDURE);
568
569
570 -- prepare the SR link for the given SR link
571
572 FOR i in 1..p_COL_NAME_LIST.COUNT-1 LOOP
573
574 IF p_COL_VALUE_LIST(i) IS NOT NULL THEN
575
576 If p_Col_Name_List(I) = 'INCIDNT_ATTR_VAL_ID' Then
577 L_Incidnt_Attr_Val_Id := P_COL_NAME_LIST(i);
578 ELSIF P_COL_NAME_LIST(I) = 'OBJECT_VERSION_NUMBER' THEN
579 L_OBJECT_VERSION_NUMBER := P_COL_NAME_LIST(I)-1;
580 ELSIF p_COL_NAME_LIST(i) = 'INCIDENT_ID' THEN
581 L_Incident_Id := P_COL_NAME_LIST(i);
582 ELSIF p_COL_NAME_LIST(i) = 'SR_ATTRIBUTE_CODE' THEN
583 L_Sr_Attribute_Code := P_COL_NAME_LIST(i);
584 ELSIF p_COL_NAME_LIST(i) = 'OVERRIDE_ADDR_VALID_FLAG' THEN
585 L_Override_Addr_Valid_Flag := P_COL_NAME_LIST(i);
586 ELSIF P_COL_NAME_LIST(i) = 'CREATION_DATE' THEN
587 L_CREATION_DATE := P_COL_NAME_LIST(I);
588 ELSIF P_COL_NAME_LIST(i) = 'CREATED_BY' THEN
589 L_CREATED_BY := P_COL_NAME_LIST(i);
590 ELSIF P_COL_NAME_LIST(i) = 'LAST_UPDATE_DATE' THEN
591 L_LAST_UPDATE_DATE := P_COL_NAME_LIST(I);
592 Elsif P_COL_NAME_LIST(I) = 'LAST_UPDATED_BY' Then
593 L_Last_Updated_By:= P_COL_NAME_LIST(i);
594 ELSIF P_COL_NAME_LIST(i) = 'LAST_UPDATE_LOGIN' THEN
595 L_Last_Update_Login := P_COL_NAME_LIST(I);
596 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE1' Then
597 L_Attribute1 := P_COL_NAME_LIST(I);
598 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE2' Then
599 L_Attribute2 := P_COL_NAME_LIST(I);
600 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE3' Then
601 L_Attribute3 := P_COL_NAME_LIST(I);
602 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE4' Then
603 L_Attribute4 := P_COL_NAME_LIST(I);
604 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE5' Then
605 L_Attribute5 := P_COL_NAME_LIST(I);
606 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE6' Then
607 L_Attribute6 := P_COL_NAME_LIST(I);
608 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE7' Then
609 L_Attribute7 := P_COL_NAME_LIST(I);
610 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE8' Then
611 L_Attribute8 := P_COL_NAME_LIST(I);
612 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE9' Then
613 L_Attribute9 := P_COL_NAME_LIST(I);
614 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE10' Then
615 L_Attribute10 := P_COL_NAME_LIST(I);
616 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE11' Then
617 L_Attribute11 := P_COL_NAME_LIST(I);
618 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE12' Then
619 L_Attribute12 := P_COL_NAME_LIST(I);
620 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE13' Then
621 L_Attribute13 := P_COL_NAME_LIST(I);
622 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE14' Then
623 L_Attribute14 := P_COL_NAME_LIST(I);
624 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE15' Then
625 L_Attribute15 := P_COL_NAME_LIST(I);
626 Elsif P_COL_NAME_LIST(I) = 'ATTRIBUTE_CATEGORY' Then
627 L_Attribute_Category := P_COL_NAME_LIST(I);
628 END IF;
629 End If;
630 END LOOP;
631
632 --Call the SR attribute Api
633 CUG_INCIDNT_ATTR_VALS_PKG.UPDATE_ROW
634 (
635 X_Incidnt_Attr_Val_Id => L_Incidnt_Attr_Val_Id,
636 X_Object_Version_Number => L_Object_Version_Number,
637 X_Incident_Id => L_Incident_Id,
638 X_Sr_Attribute_Code => L_Sr_Attribute_Code,
639 X_Override_Addr_Valid_Flag => L_Override_Addr_Valid_Flag,
640 X_Attribute1 => L_Attribute1,
641 X_Attribute2 => L_Attribute2,
642 X_Attribute3 => L_Attribute3,
643 X_Attribute4 => L_Attribute4,
644 X_Attribute5 => L_Attribute5,
645 X_Attribute6 => L_Attribute6,
646 X_Attribute7 => L_Attribute7,
647 X_Attribute8 => L_Attribute8,
648 X_Attribute9 => L_Attribute9,
649 X_Attribute10 => L_Attribute10,
650 X_Attribute11 => L_Attribute11,
651 X_Attribute12 => L_Attribute12,
652 X_Attribute13 => L_Attribute13,
653 X_Attribute14 => L_Attribute14,
654 X_Attribute15 => L_Attribute15,
655 X_Attribute_Category => L_Attribute_Category,
656 X_Sr_Attribute_Value => L_SR_ATTRIBUTE_VALUE,
657 X_Last_Update_Date => L_Last_Update_Date,
658 X_Last_Updated_By => L_Last_Updated_By,
659 X_Last_Update_Login => L_Last_Update_Login
660 ) ;
661
665 ELSE
662 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
663 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
664 X_Error_Message := l_Msg_Data;
666 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
667 END IF;
668 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_UPDATE for HA ID ' || p_HA_PAYLOAD_ID ,
669 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_UPDATE',Fnd_Log.Level_Procedure);
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 x_return_status := FND_API.G_Ret_Sts_Error;
674 X_Error_Message := L_Msg_Data;
675 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_UPDATE for HA ID ' || p_HA_PAYLOAD_ID ,
676 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_UPDATE',FND_LOG.LEVEL_PROCEDURE);
677
678 END APPLY_SR_ATTR_UPDATE;
679
680 --SR Attributes DELETE
681 PROCEDURE APPLY_SR_ATTR_DELETE
682 (p_HA_PAYLOAD_ID IN NUMBER,
683 p_Col_Name_List In Csm_Varchar_List,
684 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
685 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
686 X_Error_Message Out Nocopy Varchar2
687 )
688 AS
689 --Variable Declarations
690 L_Incidnt_Attr_Val_Id Number;
691 l_return_status VARCHAR2(1);
692 l_msg_count NUMBER;
693 l_msg_data VARCHAR2(2000);
694
695 BEGIN
696 l_return_status := Fnd_Api.G_Ret_Sts_Success;
697 CSM_UTIL_PKG.LOG('Entering CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_DELETE for HA ID ' || p_HA_PAYLOAD_ID ,
698 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_DELETE',FND_LOG.LEVEL_PROCEDURE);
699
700
701 -- prepare the SR link for the given SR link
702
703 FOR i in 1..p_COL_NAME_LIST.COUNT-1 LOOP
704
705 IF p_COL_VALUE_LIST(i) IS NOT NULL THEN
706 IF P_COL_NAME_LIST(I) = 'INCIDNT_ATTR_VAL_ID' THEN
707 L_INCIDNT_ATTR_VAL_ID := P_COL_NAME_LIST(I);
708 EXIT;
709 END IF;
710 END IF;
711
712 END LOOP;
713
714 --Call the SR attribute Api
715 CUG_INCIDNT_ATTR_VALS_PKG.DELETE_ROW
716 (
717 X_Incidnt_Attr_Val_Id => L_Incidnt_Attr_Val_Id
718 ) ;
719
720 IF l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
721 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
722 X_Error_Message := l_Msg_Data;
723 ELSE
724 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
725 END IF;
726 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_DELETE for HA ID ' || p_HA_PAYLOAD_ID ,
727 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_DELETE',Fnd_Log.Level_Procedure);
728
729 EXCEPTION
730 WHEN OTHERS THEN
731 x_return_status := FND_API.G_Ret_Sts_Error;
732 X_Error_Message := L_Msg_Data;
733 CSM_UTIL_PKG.LOG('Leaving CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_DELETE for HA ID ' || p_HA_PAYLOAD_ID ,
734 'CSM_SR_ATTRIBUTES_PKG.APPLY_SR_ATTR_DELETE',FND_LOG.LEVEL_PROCEDURE);
735
736 END APPLY_SR_ATTR_DELETE;
737
738 PROCEDURE APPLY_HA_ATTR_CHANGES
739 (p_HA_PAYLOAD_ID IN NUMBER,
740 P_COL_NAME_LIST IN CSM_VARCHAR_LIST,
741 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
742 p_dml_type IN VARCHAR2,
743 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
744 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
745 )IS
746 L_RETURN_STATUS VARCHAR2(100);
747 l_ERROR_MESSAGE VARCHAR2(4000);
748 BEGIN
749 /*** initialize return status and message list ***/
750 L_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
751 FND_MSG_PUB.INITIALIZE;
752
753 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_HA_ATTR_CHANGES for Payload ID ' || p_HA_PAYLOAD_ID ,
754 'CSM_SERVICE_REQUESTS_PKG.APPLY_HA_ATTR_CHANGES',FND_LOG.LEVEL_PROCEDURE);
755
756 IF p_dml_type ='I' THEN
757 -- Process insert
758 APPLY_SR_ATTR_INSERT
759 (p_HA_PAYLOAD_ID => p_HA_PAYLOAD_ID,
760 p_COL_NAME_LIST => P_COL_NAME_LIST,
761 p_COL_VALUE_LIST => p_COL_VALUE_LIST,
762 x_RETURN_STATUS => l_RETURN_STATUS,
763 x_ERROR_MESSAGE => l_ERROR_MESSAGE
764 );
765 ELSIF p_dml_type ='U' THEN
766 -- Process update
767 APPLY_SR_ATTR_UPDATE
768 (p_HA_PAYLOAD_ID => p_HA_PAYLOAD_ID,
769 p_COL_NAME_LIST => P_COL_NAME_LIST,
770 p_COL_VALUE_LIST => p_COL_VALUE_LIST,
771 x_RETURN_STATUS => l_RETURN_STATUS,
772 X_ERROR_MESSAGE => L_ERROR_MESSAGE
773 );
774 ELSIF p_dml_type ='D' THEN
775 -- Process update
776 APPLY_SR_ATTR_DELETE
777 (p_HA_PAYLOAD_ID => p_HA_PAYLOAD_ID,
778 p_COL_NAME_LIST => P_COL_NAME_LIST,
779 p_COL_VALUE_LIST => p_COL_VALUE_LIST,
780 x_RETURN_STATUS => l_RETURN_STATUS,
781 X_ERROR_MESSAGE => L_ERROR_MESSAGE
782 );
783 END IF;
784 X_RETURN_STATUS := l_RETURN_STATUS;
785 x_ERROR_MESSAGE := l_ERROR_MESSAGE;
786 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_HA_ATTR_CHANGES for HA ID ' || p_HA_PAYLOAD_ID ,
787 'CSM_SERVICE_REQUESTS_PKG.APPLY_HA_ATTR_CHANGES',FND_LOG.LEVEL_PROCEDURE);
788
789 EXCEPTION WHEN OTHERS THEN
790 CSM_UTIL_PKG.log( 'Exception in CSM_SERVICE_REQUESTS_PKG.APPLY_HA_ATTR_CHANGES: ' || sqlerrm
791 || ' for HA ID ' || p_HA_PAYLOAD_ID ,'CSM_SERVICE_REQUESTS_PKG.APPLY_HA_ATTR_CHANGES',FND_LOG.LEVEL_EXCEPTION);
792 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
793 X_ERROR_MESSAGE := TO_CHAR(SQLERRM,2000);
794
795 END APPLY_HA_ATTR_CHANGES;
796
797 PROCEDURE APPLY_HA_LINK_CHANGES
798 (p_HA_PAYLOAD_ID IN NUMBER,
799 P_COL_NAME_LIST IN CSM_VARCHAR_LIST,
800 p_COL_VALUE_LIST IN CSM_VARCHAR_LIST,
801 p_dml_type IN VARCHAR2,
802 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
803 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
804 )IS
805 L_RETURN_STATUS VARCHAR2(100);
806 l_ERROR_MESSAGE VARCHAR2(4000);
807 BEGIN
808 /*** initialize return status and message list ***/
809 L_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
810 FND_MSG_PUB.INITIALIZE;
811
812 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_HA_LINK_CHANGES for Payload ID ' || p_HA_PAYLOAD_ID ,
813 'CSM_SERVICE_REQUESTS_PKG.APPLY_HA_LINK_CHANGES',FND_LOG.LEVEL_PROCEDURE);
814
815 IF p_dml_type ='I' THEN
816 -- Process insert
817 APPLY_SR_LINK_INSERT
818 (p_HA_PAYLOAD_ID =>p_HA_PAYLOAD_ID,
819 p_COL_NAME_LIST => P_COL_NAME_LIST,
820 p_COL_VALUE_LIST => p_COL_VALUE_LIST,
821 x_RETURN_STATUS => l_RETURN_STATUS,
822 x_ERROR_MESSAGE => l_ERROR_MESSAGE
823 );
824 ELSIF p_dml_type ='U' THEN
825 -- Process update
826 APPLY_SR_LINK_UPDATE
827 (p_HA_PAYLOAD_ID =>p_HA_PAYLOAD_ID,
828 p_COL_NAME_LIST => P_COL_NAME_LIST,
829 p_COL_VALUE_LIST => p_COL_VALUE_LIST,
830 x_RETURN_STATUS => l_RETURN_STATUS,
831 X_ERROR_MESSAGE => L_ERROR_MESSAGE
832 );
833 ELSIF p_dml_type ='D' THEN
834 -- Process update
835 APPLY_SR_LINK_DELETE
836 (p_HA_PAYLOAD_ID =>p_HA_PAYLOAD_ID,
837 p_COL_NAME_LIST => P_COL_NAME_LIST,
838 p_COL_VALUE_LIST => p_COL_VALUE_LIST,
839 x_RETURN_STATUS => l_RETURN_STATUS,
840 X_ERROR_MESSAGE => L_ERROR_MESSAGE
841 );
842 END IF;
843
844 X_RETURN_STATUS := l_RETURN_STATUS;
845 x_ERROR_MESSAGE := l_ERROR_MESSAGE;
846 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_HA_LINK_CHANGES for HA ID ' || p_HA_PAYLOAD_ID ,
847 'CSM_SERVICE_REQUESTS_PKG.APPLY_HA_LINK_CHANGES',FND_LOG.LEVEL_PROCEDURE);
848
849 EXCEPTION WHEN OTHERS THEN
850 CSM_UTIL_PKG.log( 'Exception in CSM_SERVICE_REQUESTS_PKG.APPLY_HA_LINK_CHANGES: ' || sqlerrm
851 || ' for HA ID ' || p_HA_PAYLOAD_ID ,'CSM_SERVICE_REQUESTS_PKG.APPLY_HA_LINK_CHANGES',FND_LOG.LEVEL_EXCEPTION);
852 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
853 X_ERROR_MESSAGE := TO_CHAR(SQLERRM,2000);
854
855 END APPLY_HA_LINK_CHANGES;
856
857 END CSM_SR_ATTRIBUTES_PKG;