[Home] [Help]
PACKAGE BODY: APPS.FND_SVC_COMP_REQUESTS_H_PKG
Source
1 package body FND_SVC_COMP_REQUESTS_H_PKG as
2 /* $Header: AFSVCHTB.pls 115.2 2002/12/27 20:41:30 ankung noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_REQUEST_HISTORY_ID in NUMBER,
7 X_COMPONENT_ID in NUMBER,
8 X_EVENT_NAME in VARCHAR2,
9 X_REQUEST_STATUS in VARCHAR2,
10 X_REQUESTED_BY_USER in VARCHAR2,
11 X_COMPLETION_DATE in DATE,
12 X_COMPONENT_NAME in VARCHAR2,
13 X_COMPONENT_STATUS in VARCHAR2,
14 X_COMPONENT_TYPE in VARCHAR2,
15 X_CONTAINER_TYPE in VARCHAR2,
16 X_CONTAINER_NAME in VARCHAR2,
17 X_EVENT_PARAMS in VARCHAR2,
18 X_OBJECT_VERSION_NUMBER in NUMBER,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER
24 ) is
25 cursor C is select ROWID from FND_SVC_COMP_REQUESTS_H
26 where REQUEST_HISTORY_ID = X_REQUEST_HISTORY_ID
27 ;
28 begin
29 insert into FND_SVC_COMP_REQUESTS_H (
30 REQUEST_HISTORY_ID,
31 COMPONENT_ID,
32 EVENT_NAME,
33 REQUEST_STATUS,
34 REQUESTED_BY_USER,
35 COMPLETION_DATE,
36 COMPONENT_NAME,
37 COMPONENT_STATUS,
38 COMPONENT_TYPE,
39 CONTAINER_TYPE,
40 CONTAINER_NAME,
41 EVENT_PARAMS,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 OBJECT_VERSION_NUMBER
48 ) values (
49 X_REQUEST_HISTORY_ID,
50 X_COMPONENT_ID,
51 X_EVENT_NAME,
52 X_REQUEST_STATUS,
53 X_REQUESTED_BY_USER,
54 X_COMPLETION_DATE,
55 X_COMPONENT_NAME,
56 X_COMPONENT_STATUS,
57 X_COMPONENT_TYPE,
58 X_CONTAINER_TYPE,
59 X_CONTAINER_NAME,
60 X_EVENT_PARAMS,
61 X_CREATION_DATE,
62 X_CREATED_BY,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_LOGIN,
66 X_OBJECT_VERSION_NUMBER
67 );
68
69 open c;
70 fetch c into X_ROWID;
71 if (c%notfound) then
72 close c;
73 raise no_data_found;
74 end if;
75 close c;
76
77 exception
78 when others then
79 wf_core.context('FND_SVC_COMP_REQUESTS_H_PKG', 'Insert_Row', X_COMPONENT_ID, X_EVENT_NAME);
80 raise;
81
82 end INSERT_ROW;
83
84 procedure LOCK_ROW (
85 X_REQUEST_HISTORY_ID in NUMBER,
86 X_COMPONENT_ID in NUMBER,
87 X_EVENT_NAME in VARCHAR2,
88 X_REQUEST_STATUS in VARCHAR2,
89 X_REQUESTED_BY_USER in VARCHAR2,
90 X_COMPLETION_DATE in DATE,
91 X_COMPONENT_NAME in VARCHAR2,
92 X_COMPONENT_STATUS in VARCHAR2,
93 X_COMPONENT_TYPE in VARCHAR2,
94 X_CONTAINER_TYPE in VARCHAR2,
95 X_CONTAINER_NAME in VARCHAR2,
96 X_EVENT_PARAMS in VARCHAR2,
97 X_OBJECT_VERSION_NUMBER in NUMBER
98 ) is
99 cursor c is select
100 COMPONENT_ID,
101 EVENT_NAME,
102 REQUEST_STATUS,
103 REQUESTED_BY_USER,
104 COMPLETION_DATE,
105 COMPONENT_NAME,
106 COMPONENT_STATUS,
107 COMPONENT_TYPE,
108 CONTAINER_TYPE,
109 CONTAINER_NAME,
110 EVENT_PARAMS,
111 OBJECT_VERSION_NUMBER
112 from FND_SVC_COMP_REQUESTS_H
113 where REQUEST_HISTORY_ID = X_REQUEST_HISTORY_ID
114 for update of REQUEST_HISTORY_ID nowait;
115
116 recinfo c%rowtype;
117 begin
118
119
120 open c;
121 fetch c into recinfo;
122 if (c%notfound) then
123 close c;
124 wf_core.raise('WF_RECORD_DELETED');
125 end if;
126 close c;
127
128 if ( ((recinfo.EVENT_PARAMS = X_EVENT_PARAMS)
129 OR ((recinfo.EVENT_PARAMS is null) AND (X_EVENT_PARAMS is null)))
130 AND (recinfo.COMPONENT_ID = X_COMPONENT_ID)
131 AND (recinfo.EVENT_NAME = X_EVENT_NAME)
132 AND (recinfo.REQUEST_STATUS = X_REQUEST_STATUS)
133 AND (recinfo.REQUESTED_BY_USER = X_REQUESTED_BY_USER)
134 AND (recinfo.COMPLETION_DATE = X_COMPLETION_DATE)
135 AND (recinfo.COMPONENT_NAME = X_COMPONENT_NAME)
136 AND (recinfo.COMPONENT_STATUS = X_COMPONENT_STATUS)
137 AND (recinfo.COMPONENT_TYPE = X_COMPONENT_TYPE)
138 AND (recinfo.CONTAINER_TYPE = X_CONTAINER_TYPE)
139 AND (recinfo.CONTAINER_NAME = X_CONTAINER_NAME)
140 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
141 ) then
142 null;
143 else
144 wf_core.raise('WF_RECORD_CHANGED');
145 end if;
146
147 return;
148
149 exception
150 when others then
151 wf_core.context('FND_SVC_COMP_REQUESTS_H_PKG', 'Lock_Row', X_REQUEST_HISTORY_ID);
152 raise;
153 end LOCK_ROW;
154
155 procedure UPDATE_ROW (
156 X_REQUEST_HISTORY_ID in NUMBER,
157 X_COMPONENT_ID in NUMBER,
158 X_EVENT_NAME in VARCHAR2,
159 X_REQUEST_STATUS in VARCHAR2,
160 X_REQUESTED_BY_USER in VARCHAR2,
161 X_COMPLETION_DATE in DATE,
162 X_COMPONENT_NAME in VARCHAR2,
163 X_COMPONENT_STATUS in VARCHAR2,
164 X_COMPONENT_TYPE in VARCHAR2,
165 X_CONTAINER_TYPE in VARCHAR2,
166 X_CONTAINER_NAME in VARCHAR2,
167 X_EVENT_PARAMS in VARCHAR2,
168 X_OBJECT_VERSION_NUMBER in NUMBER,
169 X_LAST_UPDATE_DATE in DATE,
170 X_LAST_UPDATED_BY in NUMBER,
171 X_LAST_UPDATE_LOGIN in NUMBER
172 ) is
173
174 l_object_version_number NUMBER;
175 begin
176
177 --
178 -- Perform OVN checks
179 --
180 if X_OBJECT_VERSION_NUMBER = -1 then
181
182 --
183 -- Allow update. Increment the database's OVN by 1
184 --
185 select OBJECT_VERSION_NUMBER
186 into l_object_version_number
187 from FND_SVC_COMP_REQUESTS_H
188 where REQUEST_HISTORY_ID = X_REQUEST_HISTORY_ID;
189
190 l_object_version_number := l_object_version_number + 1;
191
192 else
193
194 --
195 -- Lock the row. Allow update only if the database's OVN equals the one
196 -- passed in.
197 --
198 -- If update is allowed, increment the database's OVN by 1.
199 -- Otherwise, raise an error.
200 --
201
202 select OBJECT_VERSION_NUMBER
203 into l_object_version_number
204 from FND_SVC_COMP_REQUESTS_H
205 where REQUEST_HISTORY_ID = X_REQUEST_HISTORY_ID
206 for update;
207
208 if (l_object_version_number = X_OBJECT_VERSION_NUMBER) then
209
210 l_object_version_number := l_object_version_number + 1;
211 else
212
213 raise_application_error(-20002,
214 wf_core.translate('SVC_RECORD_ALREADY_UPDATED'));
215
216 end if;
217
218 end if;
219
220 update FND_SVC_COMP_REQUESTS_H set
221 COMPONENT_ID = X_COMPONENT_ID,
222 EVENT_NAME = X_EVENT_NAME,
223 REQUEST_STATUS = X_REQUEST_STATUS,
224 REQUESTED_BY_USER = X_REQUESTED_BY_USER,
225 COMPLETION_DATE = X_COMPLETION_DATE,
226 COMPONENT_NAME = X_COMPONENT_NAME,
227 COMPONENT_STATUS = X_COMPONENT_STATUS,
228 COMPONENT_TYPE = X_COMPONENT_TYPE,
229 CONTAINER_TYPE = X_CONTAINER_TYPE,
230 CONTAINER_NAME = X_CONTAINER_NAME,
231 OBJECT_VERSION_NUMBER = l_object_version_number,
232 EVENT_PARAMS = X_EVENT_PARAMS,
233 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
234 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
235 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
236 where REQUEST_HISTORY_ID = X_REQUEST_HISTORY_ID;
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241
242
243 exception
244 when others then
245 wf_core.context('FND_SVC_COMP_REQUESTS_H_PKG', 'Update_Row', X_REQUEST_HISTORY_ID);
246 raise;
247 end UPDATE_ROW;
248
249 procedure DELETE_ROW (
250 X_REQUEST_HISTORY_ID in NUMBER
251 ) is
252 begin
253 delete from FND_SVC_COMP_REQUESTS_H
254 where REQUEST_HISTORY_ID = X_REQUEST_HISTORY_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260
261 exception
262 when others then
263 wf_core.context('FND_SVC_COMP_REQUESTS_H_PKG', 'Delete_Row', X_REQUEST_HISTORY_ID);
264 raise;
265 end DELETE_ROW;
266
267 end FND_SVC_COMP_REQUESTS_H_PKG;