DBA Data[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;