[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_DATES_PVT
Source
1 PACKAGE BODY jtf_task_dates_pvt AS
2 /* $Header: jtfvtkdb.pls 115.19 2002/12/04 23:59:04 cjang ship $ */
3 g_pkg_name constant VARCHAR2(30) := 'CREATE_DATES_PVT';
4
5 PROCEDURE create_task_dates (
6 p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
8 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
9 p_task_id IN VARCHAR2,
10 p_date_type_id IN VARCHAR2,
11 p_date_value IN DATE,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_task_date_id OUT NOCOPY NUMBER,
16 p_attribute1 IN VARCHAR2 DEFAULT null ,
17 p_attribute2 IN VARCHAR2 DEFAULT null ,
18 p_attribute3 IN VARCHAR2 DEFAULT null ,
19 p_attribute4 IN VARCHAR2 DEFAULT null ,
20 p_attribute5 IN VARCHAR2 DEFAULT null ,
21 p_attribute6 IN VARCHAR2 DEFAULT null ,
22 p_attribute7 IN VARCHAR2 DEFAULT null ,
23 p_attribute8 IN VARCHAR2 DEFAULT null ,
24 p_attribute9 IN VARCHAR2 DEFAULT null ,
25 p_attribute10 IN VARCHAR2 DEFAULT null ,
26 p_attribute11 IN VARCHAR2 DEFAULT null ,
27 p_attribute12 IN VARCHAR2 DEFAULT null ,
28 p_attribute13 IN VARCHAR2 DEFAULT null ,
29 p_attribute14 IN VARCHAR2 DEFAULT null ,
30 p_attribute15 IN VARCHAR2 DEFAULT null ,
31 p_attribute_category IN VARCHAR2 DEFAULT null
32 )
33 IS
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_DATES';
36
37 l_task_date_id jtf_task_dates.task_date_id%TYPE;
38 l_rowid ROWID;
39
40 CURSOR c_jtf_task_dates (
41 l_rowid IN ROWID
42 )
43 IS
44 SELECT 1
45 FROM jtf_task_dates
46 WHERE ROWID = l_rowid;
47
48 x CHAR;
49 BEGIN
50
51 SAVEPOINT create_task_dates_pvt;
52
53 x_return_status := fnd_api.g_ret_sts_success;
54
55 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
56 THEN
57 RAISE fnd_api.g_exc_unexpected_error;
58 END IF;
59
60 IF fnd_api.to_boolean (p_init_msg_list)
61 THEN
62 fnd_msg_pub.initialize;
63 END IF;
64
65 x_return_status := fnd_api.g_ret_sts_success;
66
67 SELECT jtf_task_dates_s.nextval
68 INTO l_task_date_id
69 FROM dual;
70
71
72 jtf_task_dates_pkg.insert_row (
73 x_rowid => l_rowid,
74 x_task_date_id => l_task_date_id,
75 x_task_id => p_task_id,
76 x_date_type_id => p_date_type_id,
77 x_date_value => p_date_value,
78 x_attribute1 => p_attribute1 ,
79 x_attribute2 => p_attribute2 ,
80 x_attribute3 => p_attribute3 ,
81 x_attribute4 => p_attribute4 ,
82 x_attribute5 => p_attribute5 ,
83 x_attribute6 => p_attribute6 ,
84 x_attribute7 => p_attribute7 ,
85 x_attribute8 => p_attribute8 ,
86 x_attribute9 => p_attribute9 ,
87 x_attribute10 => p_attribute10 ,
88 x_attribute11 => p_attribute11 ,
89 x_attribute12 => p_attribute12 ,
90 x_attribute13 => p_attribute13 ,
91 x_attribute14 => p_attribute14 ,
92 x_attribute15 => p_attribute15,
93 x_attribute_category => p_attribute_category ,
94 x_creation_date => SYSDATE,
95 x_created_by => jtf_task_utl.created_by,
96 x_last_update_date => SYSDATE,
97 x_last_updated_by => jtf_task_utl.updated_by,
98 x_last_update_login => jtf_task_utl.login_id
99 );
100
101 OPEN c_jtf_task_dates (l_rowid);
102 FETCH c_jtf_task_dates INTO x;
103
104 IF c_jtf_task_dates%NOTFOUND
105 THEN
106 x_return_status := fnd_api.g_ret_sts_unexp_error;
107 fnd_message.set_name ('JTF', 'JTF_TASK_CREATING_DATE');
108 fnd_msg_pub.add;
109 RAISE fnd_api.g_exc_unexpected_error;
110 ELSE
111 x_task_date_id := l_task_date_id;
112 END IF;
113
114 IF fnd_api.to_boolean (p_commit)
115 THEN
116 COMMIT WORK;
117 END IF;
118
119 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
120
121 EXCEPTION
122 WHEN fnd_api.g_exc_unexpected_error
123 THEN
124 rollback to create_task_dates_pvt;
125 x_return_status := fnd_api.g_ret_sts_unexp_error;
126 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
127 WHEN OTHERS
128 THEN
129 rollback to create_task_dates_pvt;
130 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
131 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
132 x_return_status := fnd_api.g_ret_sts_unexp_error;
133 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
134 END;
135
136 PROCEDURE update_task_dates (
137 p_api_version IN NUMBER,
138 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
139 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
140 p_object_version_number IN OUT NOCOPY NUMBER,
141 p_task_date_id IN NUMBER,
142 -- p_task_id IN NUMBER DEFAULT fnd_api.g_miss_num,
143 p_date_type_id IN NUMBER DEFAULT fnd_api.g_miss_num,
144 p_date_value IN DATE DEFAULT fnd_api.g_miss_date,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2,
148 p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
149 p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
150 p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
151 p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
152 p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
153 p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
154 p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
155 p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
156 p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
157 p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
158 p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
159 p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
160 p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
161 p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
162 p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
163 p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
164 )
165 IS
166 l_api_version CONSTANT NUMBER := 1.0;
167 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_DATES';
168 l_task_id jtf_tasks_b.task_id%TYPE ;
169 l_date_type_id jtf_task_date_types_b.date_type_id%TYPE := p_date_type_id;
170 l_date_value jtf_task_dates.date_value%TYPE := p_date_value;
171 l_task_date_id jtf_task_dates.task_date_id%TYPE := p_task_date_id;
172
173 CURSOR c_update_task_dates
174 IS
175 SELECT task_id,
176 DECODE (p_date_type_id, fnd_api.g_miss_num, date_type_id, p_date_type_id) date_type_id,
177 DECODE (p_date_value, fnd_api.g_miss_date, date_value, p_date_value) date_value,
178 decode( p_attribute1 , fnd_api.g_miss_char , attribute1 , p_attribute1 ) attribute1 ,
179 decode( p_attribute2 , fnd_api.g_miss_char , attribute2 , p_attribute2 ) attribute2 ,
180 decode( p_attribute3 , fnd_api.g_miss_char , attribute3 , p_attribute3 ) attribute3 ,
181 decode( p_attribute4 , fnd_api.g_miss_char , attribute4 , p_attribute4 ) attribute4 ,
182 decode( p_attribute5 , fnd_api.g_miss_char , attribute5 , p_attribute5 ) attribute5 ,
183 decode( p_attribute6 , fnd_api.g_miss_char , attribute6 , p_attribute6 ) attribute6 ,
184 decode( p_attribute7 , fnd_api.g_miss_char , attribute7 , p_attribute7 ) attribute7 ,
185 decode( p_attribute8 , fnd_api.g_miss_char , attribute8 , p_attribute8 ) attribute8 ,
186 decode( p_attribute9 , fnd_api.g_miss_char , attribute9 , p_attribute9 ) attribute9 ,
187 decode( p_attribute10 , fnd_api.g_miss_char , attribute10 , p_attribute10 ) attribute10 ,
188 decode( p_attribute11 , fnd_api.g_miss_char , attribute11 , p_attribute11 ) attribute11 ,
189 decode( p_attribute12 , fnd_api.g_miss_char , attribute12 , p_attribute12 ) attribute12 ,
190 decode( p_attribute13 , fnd_api.g_miss_char , attribute13 , p_attribute13 ) attribute13 ,
191 decode( p_attribute14 , fnd_api.g_miss_char , attribute14 , p_attribute14 ) attribute14 ,
192 decode( p_attribute15 , fnd_api.g_miss_char , attribute15 , p_attribute15 ) attribute15 ,
193 decode( p_attribute_category,fnd_api.g_miss_char,attribute_category,p_attribute_category) attribute_category
194 FROM jtf_task_dates
195 WHERE task_date_id = p_task_date_id;
196
197 task_dates c_update_task_dates%ROWTYPE;
198 BEGIN
199
200 SAVEPOINT update_task_dates_pvt;
201
202 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
203 THEN
204 RAISE fnd_api.g_exc_unexpected_error;
205 END IF;
206
207 IF fnd_api.to_boolean (p_init_msg_list)
208 THEN
209 fnd_msg_pub.initialize;
210 END IF;
211
212 x_return_status := fnd_api.g_ret_sts_success;
213
214 OPEN c_update_task_dates;
215 FETCH c_update_task_dates INTO task_dates;
216
217 IF c_update_task_dates%NOTFOUND
218 THEN
219 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DATE');
220 fnd_message.set_token('P_TASK_DATE_ID',P_TASK_DATE_ID);
221 fnd_msg_pub.add;
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223 RAISE fnd_api.g_exc_unexpected_error;
224 END IF;
225
226 CLOSE c_update_task_dates;
227
228 IF p_date_type_id <> fnd_api.g_miss_num
229 THEN
230 l_date_type_id := p_date_type_id;
231 ELSE
232 l_date_type_id := task_dates.date_type_id;
233 END IF;
234
235 IF p_date_value <> fnd_api.g_miss_date
236 THEN
237 l_date_value := p_date_value;
238 ELSE
239 l_date_value := task_dates.date_value;
240 END IF;
241
242 l_task_id := task_dates.task_id;
243
244 jtf_task_dates_pub.lock_task_dates
245 ( P_API_VERSION => 1.0 ,
246 P_INIT_MSG_LIST => fnd_api.g_false ,
247 P_COMMIT => fnd_api.g_false ,
248 P_TASK_date_ID => l_task_date_id ,
249 P_OBJECT_VERSION_NUMBER => p_object_version_number,
250 X_RETURN_STATUS => x_return_status ,
251 X_MSG_DATA => x_msg_data ,
252 X_MSG_COUNT => x_msg_count ) ;
253
254
255
256 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
257 THEN
258 x_return_status := fnd_api.g_ret_sts_unexp_error;
259 RAISE fnd_api.g_exc_unexpected_error;
260 END IF;
261
262
263 jtf_task_dates_pkg.update_row (
264 x_task_date_id => l_task_date_id,
265 x_object_version_number => p_object_version_number + 1,
266 x_task_id => l_task_id,
267 x_date_type_id => l_date_type_id,
268 x_attribute1 => task_dates.attribute1 ,
269 x_attribute2 => task_dates.attribute2 ,
270 x_attribute3 => task_dates.attribute3 ,
271 x_attribute4 => task_dates.attribute4 ,
272 x_attribute5 => task_dates.attribute5 ,
273 x_attribute6 => task_dates.attribute6 ,
274 x_attribute7 => task_dates.attribute7 ,
275 x_attribute8 => task_dates.attribute8 ,
276 x_attribute9 => task_dates.attribute9 ,
277 x_attribute10 => task_dates.attribute10 ,
278 x_attribute11 => task_dates.attribute11 ,
279 x_attribute12 => task_dates.attribute12 ,
280 x_attribute13 => task_dates.attribute13 ,
281 x_attribute14 => task_dates.attribute14 ,
282 x_attribute15 => task_dates.attribute15 ,
283 x_attribute_category => task_dates.attribute_category,
284 x_date_value => l_date_value,
285 x_last_update_date => SYSDATE,
286 x_last_updated_by => jtf_task_utl.updated_by,
287 x_last_update_login => jtf_task_utl.login_id
288 );
289
290 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
291 THEN
292 x_return_status := fnd_api.g_ret_sts_unexp_error;
293 RAISE fnd_api.g_exc_unexpected_error;
294 END IF;
295
296 p_object_version_number := p_object_version_number + 1 ;
297
298
299 IF fnd_api.to_boolean (p_commit)
300 THEN
301 COMMIT WORK;
302 END IF;
303
304 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
305 EXCEPTION
306 WHEN fnd_api.g_exc_unexpected_error
307 THEN
308 ROLLBACK TO update_task_dates_pvt;
309 x_return_status := fnd_api.g_ret_sts_unexp_error;
310 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
311 WHEN OTHERS
312 THEN
313 ROLLBACK TO update_task_dates_pvt;
314 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
315 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
316 x_return_status := fnd_api.g_ret_sts_unexp_error;
317 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
318 END;
319
320 ---- Delete task dates
321 PROCEDURE delete_task_dates (
322 p_api_version IN NUMBER,
323 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
324 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
325 p_object_version_number IN NUMBER,
326 p_task_date_id IN NUMBER,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2
330 )
331 IS
332 l_api_version CONSTANT NUMBER := 1.0;
333 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TASK_DATES';
334 BEGIN
335
336
337 x_return_status := fnd_api.g_ret_sts_success;
338
339 SAVEPOINT delete_task_dates_pvt;
340
341 x_return_status := fnd_api.g_ret_sts_success;
342
343 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
344 THEN
345 RAISE fnd_api.g_exc_unexpected_error;
346 END IF;
347
348 IF fnd_api.to_boolean (p_init_msg_list)
349 THEN
350 fnd_msg_pub.initialize;
351 END IF;
352
353
354 x_return_status := fnd_api.g_ret_sts_success;
355
356 jtf_task_dates_pub.lock_task_dates
357 ( P_API_VERSION => 1.0 ,
358 P_INIT_MSG_LIST => fnd_api.g_false ,
359 P_COMMIT => fnd_api.g_false ,
360 P_TASK_date_ID => p_task_date_id ,
361 P_OBJECT_VERSION_NUMBER => p_object_version_number,
362 X_RETURN_STATUS => x_return_status ,
363 X_MSG_DATA => x_msg_data ,
364 X_MSG_COUNT => x_msg_count ) ;
365
366 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
367 THEN
368 x_return_status := fnd_api.g_ret_sts_unexp_error;
369 RAISE fnd_api.g_exc_unexpected_error;
370 END IF;
371
372
373
374 jtf_task_dates_pkg.delete_row (x_task_date_id => p_task_date_id);
375
376 IF fnd_api.to_boolean (p_commit)
377 THEN
378 COMMIT WORK;
379 END IF;
380
381 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
382 EXCEPTION
383 WHEN fnd_api.g_exc_unexpected_error
384 THEN
385 ROLLBACK TO delete_task_dates_pvt;
386 x_return_status := fnd_api.g_ret_sts_unexp_error;
387 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
388 WHEN OTHERS
389 THEN
390 ROLLBACK TO delete_task_dates_pvt;
391 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
392 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
393 x_return_status := fnd_api.g_ret_sts_unexp_error;
394 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
395 END;
396 END; -- CREATE OR REPLACE PACKAGE spec