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