[Home] [Help]
PACKAGE BODY: APPS.IEU_WR_ITEMS_PKG
Source
1 package body IEU_WR_ITEMS_PKG as
2 /* $Header: IEUVUWRB.pls 120.0 2005/06/02 15:42:25 appldev noship $ */
3
4 procedure INSERT_ROW
5 ( p_workitem_obj_code IN VARCHAR2,
6 p_workitem_pk_id IN NUMBER,
7 p_work_item_number IN VARCHAR2,
8 p_title IN VARCHAR2,
9 p_party_id IN NUMBER,
10 p_priority_id IN NUMBER,
11 p_priority_level IN NUMBER,
12 p_due_date IN DATE,
13 p_work_item_status_id IN NUMBER,
14 p_owner_id IN NUMBER,
15 p_owner_type IN VARCHAR2,
16 p_assignee_id IN NUMBER,
17 p_assignee_type IN VARCHAR2,
18 p_owner_type_actual IN VARCHAR2,
19 p_assignee_type_actual IN VARCHAR2,
20 p_source_object_id IN NUMBER,
21 p_source_object_type_code IN VARCHAR2,
22 p_application_id IN NUMBER,
23 p_ieu_enum_type_uuid IN VARCHAR2,
24 p_user_id IN NUMBER,
25 p_login_id IN NUMBER,
26 p_work_source_id IN NUMBER,
27 p_distribution_status_id IN NUMBER,
28 x_work_item_id OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2,
30 x_return_status OUT NOCOPY VARCHAR2
31 ) is
32
33 begin
34
35 begin
36 x_return_status := fnd_api.g_ret_sts_success;
37 INSERT INTO IEU_UWQM_ITEMS
38 ( WORK_ITEM_ID,
39 OBJECT_VERSION_NUMBER,
40 CREATED_BY,
41 CREATION_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATE_LOGIN,
45 WORKITEM_OBJ_CODE,
46 WORKITEM_PK_ID,
47 WORK_ITEM_NUMBER,
48 STATUS_ID,
49 PRIORITY_ID,
50 PRIORITY_LEVEL,
51 DUE_DATE,
52 TITLE,
53 PARTY_ID,
54 OWNER_ID,
55 OWNER_TYPE,
56 ASSIGNEE_ID,
57 ASSIGNEE_TYPE,
58 OWNER_TYPE_ACTUAL,
59 ASSIGNEE_TYPE_ACTUAL,
60 SOURCE_OBJECT_ID,
61 SOURCE_OBJECT_TYPE_CODE,
62 APPLICATION_ID,
63 IEU_ENUM_TYPE_UUID,
64 RESCHEDULE_TIME,
65 STATUS_UPDATE_USER_ID,
66 WS_ID,
67 DISTRIBUTION_STATUS_ID
68 )
69 VALUES
70 ( IEU_UWQM_ITEMS_S1.NEXTVAL,
71 1,
72 P_USER_ID,
73 SYSDATE,
74 P_USER_ID,
75 SYSDATE,
76 P_LOGIN_ID,
77 p_WORKITEM_OBJ_CODE,
78 p_workitem_pk_id,
79 p_work_item_number,
80 p_work_item_status_id,
81 p_priority_id,
82 p_priority_level,
83 p_due_date,
84 p_title,
85 p_party_id,
86 p_owner_id,
87 p_owner_type,
88 p_assignee_id,
89 p_assignee_type,
90 p_owner_type_actual,
91 p_assignee_type_actual,
92 p_source_object_id,
93 p_source_object_type_code,
94 p_application_id,
95 p_ieu_enum_type_uuid,
96 sysdate,
97 p_user_id,
98 p_work_source_id,
99 p_distribution_status_id
100 ) RETURNING WORK_ITEM_ID INTO X_WORK_ITEM_ID;
101
102 exception
103 when others then
104 --dbms_output.put_line('err while inserting : '||sqlcode||' - '||sqlerrm);
105 x_return_status := fnd_api.g_ret_sts_error;
106 x_msg_data := sqlerrm;
107 end;
108
109 end INSERT_ROW;
110
111 procedure UPDATE_ROW
112 ( p_WORKITEM_OBJ_CODE IN VARCHAR2,
113 p_workitem_pk_id IN NUMBER,
114 p_title IN VARCHAR2,
115 p_party_id IN NUMBER,
116 p_priority_id IN NUMBER,
117 p_priority_level IN NUMBER,
118 p_due_date IN DATE,
119 p_owner_id IN NUMBER,
120 p_owner_type IN VARCHAR2,
121 p_assignee_id IN NUMBER,
122 p_assignee_type IN VARCHAR2,
123 p_owner_type_actual IN VARCHAR2,
124 p_assignee_type_actual IN VARCHAR2,
125 p_source_object_id IN NUMBER,
126 p_source_object_type_code IN VARCHAR2,
127 p_application_id IN NUMBER,
128 p_work_item_status_id IN NUMBER,
129 p_user_id IN NUMBER,
130 p_login_id IN NUMBER,
131 p_work_source_id IN NUMBER,
132 p_distribution_status_id IN NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2,
134 x_return_status OUT NOCOPY VARCHAR2
135
136 ) is
137
138 begin
139
140 x_return_status := fnd_api.g_ret_sts_success;
141
142 UPDATE IEU_UWQM_ITEMS
143 SET
144 CREATED_BY = P_USER_ID,
145 CREATION_DATE = SYSDATE,
146 LAST_UPDATED_BY = P_USER_ID,
147 LAST_UPDATE_DATE = SYSDATE,
148 LAST_UPDATE_LOGIN = P_LOGIN_ID,
149 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
150 TITLE = P_TITLE,
151 PARTY_ID = P_PARTY_ID,
152 PRIORITY_LEVEL = P_PRIORITY_LEVEL,
153 PRIORITY_ID = P_PRIORITY_ID,
154 DUE_DATE = P_DUE_DATE,
155 OWNER_ID = P_OWNER_ID,
156 OWNER_TYPE = P_OWNER_TYPE,
157 ASSIGNEE_ID = P_ASSIGNEE_ID,
158 ASSIGNEE_TYPE = P_ASSIGNEE_TYPE,
159 OWNER_TYPE_ACTUAL = P_OWNER_TYPE_ACTUAL,
160 ASSIGNEE_TYPE_ACTUAL = P_ASSIGNEE_TYPE_ACTUAL,
161 SOURCE_OBJECT_ID = P_SOURCE_OBJECT_ID,
162 SOURCE_OBJECT_TYPE_CODE = P_SOURCE_OBJECT_TYPE_CODE,
163 APPLICATION_ID = P_APPLICATION_ID,
164 STATUS_ID = P_WORK_ITEM_STATUS_ID,
165 WS_ID = P_WORK_SOURCE_ID,
166 DISTRIBUTION_STATUS_ID = P_DISTRIBUTION_STATUS_ID
167 WHERE WORKITEM_OBJ_CODE = P_WORKITEM_OBJ_CODE
168 AND WORKITEM_PK_ID = P_WORKITEM_PK_ID;
169
170 if (sql%notfound) then
171 x_return_status := fnd_api.g_ret_sts_error;
172 x_msg_data := 'Work Item does not exist in UWQ Metaphor table';
173 end if;
174
175 exception
176 when others then
177 x_return_status := fnd_api.g_ret_sts_error;
178 x_msg_data := sqlerrm;
179
180 end UPDATE_ROW;
181
182 PROCEDURE LOAD_ROW
183 ( p_workitem_obj_code IN VARCHAR2,
184 p_workitem_pk_id IN NUMBER,
185 p_work_item_number IN VARCHAR2,
186 p_title IN VARCHAR2,
187 p_party_id IN NUMBER,
188 p_priority_id IN NUMBER,
189 p_priority_level IN NUMBER,
190 p_due_date IN DATE,
191 p_owner_id IN NUMBER,
192 p_owner_type IN VARCHAR2,
193 p_assignee_id IN NUMBER,
194 p_assignee_type IN VARCHAR2,
195 p_owner_type_actual IN VARCHAR2,
196 p_assignee_type_actual IN VARCHAR2,
197 p_source_object_id IN NUMBER,
198 p_source_object_type_code IN VARCHAR2,
199 p_application_id IN NUMBER,
200 p_work_item_status_id IN NUMBER,
201 p_ieu_enum_type_uuid IN NUMBER,
202 p_user_id IN NUMBER,
203 p_login_id IN NUMBER,
204 p_work_source_id IN NUMBER,
205 p_distribution_status_id IN NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 x_return_status OUT NOCOPY VARCHAR2
208
209 ) is
210
211 x_work_item_id NUMBER;
212 l_status_id NUMBER := 0;
213
214 begin
215
216 x_return_status := fnd_api.g_ret_sts_success;
217
218 IEU_WR_ITEMS_PKG.UPDATE_ROW
219 ( p_WORKITEM_OBJ_CODE,
220 p_workitem_pk_id,
221 p_title,
222 p_party_id,
223 p_priority_id,
224 p_priority_level,
225 p_due_date,
226 p_owner_id,
227 p_owner_type,
228 p_assignee_id,
229 p_assignee_type,
230 p_owner_type_actual,
231 p_assignee_type_actual,
232 p_source_object_id,
233 p_source_object_type_code,
234 p_application_id,
235 p_work_item_status_id,
236 p_user_id,
237 p_login_id,
238 p_work_source_id,
239 p_distribution_status_id,
240 -- p_ieu_enum_type_uuid,
241 x_msg_data,
242 x_return_status
243 );
244
245 if (sql%notfound) then
246 raise no_data_found;
247 end if;
248 Exception
249 when no_data_found then
250 IEU_WR_ITEMS_PKG.INSERT_ROW
251 ( p_workitem_obj_code,
252 p_workitem_pk_id,
253 p_work_item_number,
254 p_title,
255 p_party_id,
256 p_priority_id,
257 p_priority_level,
258 p_due_date,
259 p_work_item_status_id,
260 p_owner_id,
261 p_owner_type,
262 p_assignee_id,
263 p_assignee_type,
264 p_owner_type_actual,
265 p_assignee_type_actual,
266 p_source_object_id,
267 p_source_object_type_code,
268 p_application_id,
269 p_ieu_enum_type_uuid,
270 p_user_id,
271 p_login_id,
272 p_work_source_id,
273 p_distribution_status_id,
274 x_work_item_id,
275 x_msg_data,
276 x_return_status
277 );
278 END LOAD_ROW;
279
280 procedure DELETE_ROW
281 ( p_workitem_obj_code IN VARCHAR2,
282 p_workitem_pk_id IN NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2
285 ) IS
286
287 l_message VARCHAR2(4000);
288
289 begin
290 l_message := '';
291 x_return_status := fnd_api.g_ret_sts_success;
292
293 delete from IEU_UWQM_ITEMS
294 where WORKITEM_OBJ_CODE = P_WORKITEM_OBJ_CODE
295 and WORKITEM_PK_ID = P_WORKITEM_PK_ID;
296
297 if (sql%notfound) then
298
299 x_return_status := fnd_api.g_ret_sts_error;
300
301 FND_MESSAGE.SET_NAME('IEU', 'IEU_WP_NO_FOUND');
302 l_message := FND_MESSAGE.GET;
303
304 RAISE fnd_api.g_exc_error;
305 end if;
306
307 EXCEPTION
308 WHEN fnd_api.g_exc_error THEN
309
310 x_return_status := fnd_api.g_ret_sts_error;
311 x_msg_data := l_message;
312
313 end DELETE_ROW;
314
315 procedure UPDATE_STATUS_FLAG
316 ( p_workitem_obj_code IN VARCHAR2,
317 p_workitem_pk_id IN NUMBER,
318 p_work_item_id IN NUMBER default null,
319 p_status_update_user_id IN NUMBER,
320 p_status_id IN NUMBER,
321 -- p_reschedule_time IN DATE DEFAULT NULL,
322 x_return_status OUT NOCOPY VARCHAR2
323 ) IS
324
325 begin
326
327 x_return_status := fnd_api.g_ret_sts_success;
328
329 IF ( p_work_item_id is not null)
330 THEN
331 UPDATE IEU_UWQM_ITEMS
332 SET status_id = p_status_id,
333 status_update_user_id = p_status_update_user_id
334 -- reschedule_time = nvl(p_reschedule_time, sysdate)
335 WHERE WORK_ITEM_ID = P_WORK_ITEM_ID;
336 ELSE
337 --IF ( ( p_workitem_obj_code is not null) and ( p_work_item_id is not null) )
338 UPDATE IEU_UWQM_ITEMS
339 SET status_id = p_status_id,
340 status_update_user_id = p_status_update_user_id
341 -- reschedule_time = nvl(p_reschedule_time, sysdate)
342 WHERE WORKITEM_OBJ_CODE = P_WORKITEM_OBJ_CODE
343 AND WORKITEM_PK_ID = P_WORKITEM_PK_ID;
344 END IF;
345
346 if (sql%notfound) then
347 x_return_status := fnd_api.g_ret_sts_error;
348 end if;
349
350 end UPDATE_STATUS_FLAG;
351 end IEU_WR_ITEMS_PKG;