[Home] [Help]
PACKAGE BODY: APPS.CS_SR_ACTION_DETAILS_PKG
Source
1 package body CS_SR_ACTION_DETAILS_PKG as
2 /* $Header: csxtnadb.pls 120.0 2005/08/12 15:28:48 aneemuch noship $ */
3
4 procedure INSERT_ROW (
5 PX_EVENT_ACTION_DETAIL_ID in out NOCOPY NUMBER,
6 P_EVENT_CONDITION_ID in NUMBER,
7 P_RESOLUTION_CODE in VARCHAR2,
8 P_INCIDENT_STATUS_ID in NUMBER,
9 P_START_DATE_ACTIVE in DATE,
10 P_END_DATE_ACTIVE in DATE,
11 P_RELATIONSHIP_TYPE_ID in NUMBER,
12 P_SEEDED_FLAG in VARCHAR2,
13 P_APPLICATION_ID in NUMBER,
14 P_NOTIFICATION_TEMPLATE_ID in VARCHAR2,
15 P_ACTION_CODE in VARCHAR2,
16 P_CREATION_DATE in DATE,
17 P_CREATED_BY in NUMBER,
18 P_LAST_UPDATE_DATE in DATE,
19 P_LAST_UPDATED_BY in NUMBER,
20 P_LAST_UPDATE_LOGIN in NUMBER,
21 X_OBJECT_VERSION_NUMBER out NOCOPY NUMBER
22 ) is
23 CURSOR C2 IS SELECT CS_SR_EVENT_ACTION_DETAILS_S.nextval FROM sys.dual;
24 l_object_version_number number := 1;
25 begin
26 If (px_EVENT_ACTION_DETAIL_ID IS NULL) OR (px_EVENT_ACTION_DETAIL_ID = FND_API.G_MISS_NUM) then
27 OPEN C2;
28 FETCH C2 INTO px_EVENT_ACTION_DETAIL_ID;
29 CLOSE C2;
30 End If;
31
32 insert into CS_SR_ACTION_DETAILS (
33 EVENT_ACTION_DETAIL_ID,
34 EVENT_CONDITION_ID,
35 RESOLUTION_CODE,
36 INCIDENT_STATUS_ID,
37 START_DATE_ACTIVE,
38 END_DATE_ACTIVE,
39 RELATIONSHIP_TYPE_ID,
40 SEEDED_FLAG,
41 APPLICATION_ID,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 ACTION_CODE,
48 NOTIFICATION_TEMPLATE_ID,
49 OBJECT_VERSION_NUMBER
50 ) values
51 (PX_EVENT_ACTION_DETAIL_ID,
52 P_EVENT_CONDITION_ID,
53 P_RESOLUTION_CODE,
54 P_INCIDENT_STATUS_ID,
55 P_START_DATE_ACTIVE,
56 P_END_DATE_ACTIVE,
57 P_RELATIONSHIP_TYPE_ID,
58 P_SEEDED_FLAG,
59 P_APPLICATION_ID,
60 P_CREATION_DATE,
61 P_CREATED_BY,
62 P_LAST_UPDATE_DATE,
63 P_LAST_UPDATED_BY,
64 P_LAST_UPDATE_LOGIN,
65 P_ACTION_CODE,
66 P_NOTIFICATION_TEMPLATE_ID,
67 L_OBJECT_VERSION_NUMBER
68 );
69 X_OBJECT_VERSION_NUMBER := l_object_Version_number;
70 end INSERT_ROW;
71
72 procedure LOCK_ROW (
73 P_EVENT_ACTION_DETAIL_ID in NUMBER,
74 P_OBJECT_VERSION_NUMBER in NUMBER
75 ) is
76 cursor c is select
77 OBJECT_VERSION_NUMBER
78 from CS_SR_ACTION_DETAILS
79 where EVENT_ACTION_DETAIL_ID = P_EVENT_ACTION_DETAIL_ID
80 for update of EVENT_ACTION_DETAIL_ID nowait;
81
82 l_object_version_number number := 0;
83 begin
84 open c;
85 fetch c into l_object_Version_number;
86 if (c%notfound) then
87 close c;
88 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
89 app_exception.raise_exception;
90 end if;
91 close c;
92
93 if (l_object_version_number = P_OBJECT_VERSION_NUMBER) then
94 null;
95 else
96 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
97 app_exception.raise_exception;
98 end if;
99 return;
100 end LOCK_ROW;
101
102 procedure UPDATE_ROW (
103 P_EVENT_ACTION_DETAIL_ID in NUMBER,
104 P_EVENT_CONDITION_ID in NUMBER,
105 P_RESOLUTION_CODE in VARCHAR2,
106 P_INCIDENT_STATUS_ID in NUMBER,
107 P_START_DATE_ACTIVE in DATE,
108 P_END_DATE_ACTIVE in DATE,
109 P_RELATIONSHIP_TYPE_ID in NUMBER,
110 P_SEEDED_FLAG in VARCHAR2,
111 P_APPLICATION_ID in NUMBER,
112 P_NOTIFICATION_TEMPLATE_ID in VARCHAR2,
113 P_ACTION_CODE in VARCHAR2,
114 P_LAST_UPDATE_DATE in DATE,
115 P_LAST_UPDATED_BY in NUMBER,
116 P_LAST_UPDATE_LOGIN in NUMBER,
117 X_OBJECT_VERSION_NUMBER out NOCOPY NUMBER
118 ) is
119 l_object_Version_number number;
120 begin
121 update CS_SR_ACTION_DETAILS set
122 RESOLUTION_CODE = P_RESOLUTION_CODE,
123 INCIDENT_STATUS_ID = P_INCIDENT_STATUS_ID,
124 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
125 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
126 RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID,
127 SEEDED_FLAG = P_SEEDED_FLAG,
128 APPLICATION_ID = P_APPLICATION_ID,
129 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
130 NOTIFICATION_TEMPLATE_ID = P_NOTIFICATION_TEMPLATE_ID,
131 ACTION_CODE = P_ACTION_CODE,
132 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
133 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
135 EVENT_CONDITION_ID = P_EVENT_CONDITION_ID
136 where EVENT_ACTION_DETAIL_ID = P_EVENT_ACTION_DETAIL_ID
137 -- and ACTION_CODE = P_ACTION_CODE
138 RETURNING OBJECT_VERSION_NUMBER INTO L_OBJECT_VERSION_NUMBER;
139
140 X_OBJECT_VERSION_NUMBER := l_object_version_number;
141
142 if (sql%notfound) then
143 raise no_data_found;
144 end if;
145 end UPDATE_ROW;
146
147 procedure DELETE_ROW (
148 P_EVENT_ACTION_DETAIL_ID in NUMBER
149 ) is
150 begin
151 delete from CS_SR_ACTION_DETAILS
152 where EVENT_ACTION_DETAIL_ID = P_EVENT_ACTION_DETAIL_ID;
153
154
155 if (sql%notfound) then
156 raise no_data_found;
157 end if;
158
159 end DELETE_ROW;
160
161 PROCEDURE LOAD_ROW (
162 P_EVENT_CONDITION_ID IN NUMBER,
163 P_RESOLUTION_CODE IN VARCHAR2,
164 P_INCIDENT_STATUS_ID IN NUMBER,
165 P_START_DATE_ACTIVE IN VARCHAR2,
166 P_END_DATE_ACTIVE IN VARCHAR2,
167 P_RELATIONSHIP_TYPE_ID IN NUMBER,
168 P_SEEDED_FLAG IN VARCHAR2,
169 P_APPLICATION_ID IN NUMBER,
170 P_NOTIFICATION_TEMPLATE_ID IN VARCHAR2,
171 P_ACTION_CODE IN VARCHAR2,
172 P_OWNER IN VARCHAR2,
173 P_CREATION_DATE IN VARCHAR2,
174 P_CREATED_BY IN NUMBER,
175 P_LAST_UPDATE_DATE IN VARCHAR2,
176 P_LAST_UPDATED_BY IN NUMBER,
177 P_LAST_UPDATE_LOGIN IN NUMBER,
178 P_OBJECT_VERSION_NUMBER IN NUMBER,
179 P_EVENT_ACTION_DETAIL_ID IN NUMBER
180 )
181 IS
182
183 -- Out local variables for the update / insert row procedures.
184 lx_object_version_number NUMBER := 0;
185 l_user_id NUMBER := 0;
186
187 -- needed to be passed as the parameter value for the insert's in/out
188 -- parameter.
189 l_EVENT_ACTION_DETAIL_ID NUMBER;
190
191 BEGIN
192
193 if ( p_owner = 'SEED' ) then
194 l_user_id := 1;
195 end if;
196
197 l_EVENT_ACTION_DETAIL_ID := p_EVENT_ACTION_DETAIL_ID;
198
199 UPDATE_ROW (
200 P_EVENT_ACTION_DETAIL_ID =>l_event_action_detail_id,
201 P_EVENT_CONDITION_ID =>p_event_condition_id,
202 P_RESOLUTION_CODE =>p_resolution_code ,
203 P_INCIDENT_STATUS_ID =>p_incident_status_id,
204 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
205 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
206 P_RELATIONSHIP_TYPE_ID =>p_relationship_type_id,
207 P_SEEDED_FLAG =>p_seeded_flag,
208 P_APPLICATION_ID =>p_application_id,
209 P_NOTIFICATION_TEMPLATE_ID=>p_notification_template_id,
210 P_ACTION_CODE =>p_action_code,
211 P_LAST_UPDATE_DATE =>nvl(to_date(p_last_update_date,'DD-MM-YYYY'),sysdate),
212 P_LAST_UPDATED_BY =>l_user_id,
213 P_LAST_UPDATE_LOGIN =>0,
214 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
215 );
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 INSERT_ROW (
220 PX_EVENT_ACTION_DETAIL_ID =>l_event_action_detail_id,
221 P_EVENT_CONDITION_ID =>p_event_condition_id,
222 P_RESOLUTION_CODE =>p_resolution_code ,
223 P_INCIDENT_STATUS_ID =>p_incident_status_id,
224 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
225 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
226 P_RELATIONSHIP_TYPE_ID =>p_relationship_type_id,
227 P_SEEDED_FLAG =>p_seeded_flag,
228 P_APPLICATION_ID =>p_application_id,
229 P_NOTIFICATION_TEMPLATE_ID=>p_notification_template_id,
230 P_ACTION_CODE =>p_action_code,
231 P_CREATION_DATE =>nvl(to_date( p_creation_date,
232 'DD-MM-YYYY'),sysdate),
233 P_CREATED_BY =>l_user_id,
234
235 P_LAST_UPDATE_DATE =>nvl(to_date(p_last_update_date,
236 'DD-MM-YYYY'),sysdate),
237 P_LAST_UPDATED_BY =>l_user_id,
238 P_LAST_UPDATE_LOGIN =>0,
239 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
240 );
241
242 END LOAD_ROW;
243
244 end CS_SR_ACTION_DETAILS_PKG;