[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_DUP_DOCS_PKG
Source
1 PACKAGE BODY IGS_AS_DUP_DOCS_PKG AS
2 /* $Header: IGSDI76B.pls 115.2 2002/11/28 23:30:49 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_dup_docs%ROWTYPE;
6 new_references igs_as_dup_docs%ROWTYPE;
7
8 PROCEDURE process_duplicate_documents
9 (
10 x_order_number IN NUMBER,
11 x_item_number IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER
15 ) AS
16 BEGIN
17 --
18 -- Create an interface item with the item details
19 --
20 igs_as_documents_api.update_document_details (
21 p_order_number => x_order_number,
22 p_item_number => x_item_number,
23 p_init_msg_list => fnd_api.g_true,
24 p_return_status => x_return_status,
25 p_msg_count => x_msg_count,
26 p_msg_data => x_msg_data
27 );
28 --
29 -- Check if there was an error during the process of updating the Order and
30 -- Details records, and creating an interface item
31 --
32 IF (NVL (x_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success) THEN
33 RETURN;
34 END IF;
35 -- Initialize API return status to success.
36 x_return_status := fnd_api.g_ret_sts_success;
37
38 -- Standard call to get message count and if count is 1, get message info.
39 fnd_msg_pub.count_and_get (
40 p_encoded => fnd_api.g_false,
41 p_count => x_msg_count,
42 p_data => x_msg_data
43 );
44
45 EXCEPTION
46 WHEN FND_API.G_EXC_ERROR THEN
47 x_return_status := fnd_api.g_ret_sts_error;
48 fnd_msg_pub.count_and_get(
49 p_encoded => fnd_api.g_false,
50 p_count => x_msg_count,
51 p_data => x_msg_data
52 );
53 RETURN;
54 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
55 x_return_status := fnd_api.g_ret_sts_unexp_error;
56 fnd_msg_pub.count_and_get(
57 p_encoded => fnd_api.g_false,
58 p_count => x_msg_count,
59 p_data => x_msg_data
60 );
61 RETURN;
62 WHEN OTHERS THEN
63 x_return_status := fnd_api.g_ret_sts_unexp_error;
64 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
65 FND_MESSAGE.SET_TOKEN('NAME','process_duplicate_documents : '||SQLERRM);
66 fnd_msg_pub.add;
67 fnd_msg_pub.count_and_get(
68 p_encoded => fnd_api.g_false,
69 p_count => x_msg_count,
70 p_data => x_msg_data
71 );
72 RETURN;
73 END process_duplicate_documents;
74
75 PROCEDURE set_column_values (
76 p_action IN VARCHAR2,
77 x_rowid IN VARCHAR2,
78 x_order_number IN NUMBER,
79 x_item_number IN NUMBER,
80 x_requested_by IN NUMBER,
81 x_requested_date IN DATE,
82 x_fulfilled_by IN NUMBER,
83 x_fulfilled_date IN DATE,
84 x_creation_date IN DATE,
85 x_created_by IN NUMBER,
86 x_last_update_date IN DATE,
87 x_last_updated_by IN NUMBER,
88 x_last_update_login IN NUMBER
89 ) AS
90 /*
91 || Created By : [email protected]
92 || Created On : 28-OCT-2002
93 || Purpose : Initialises the Old and New references for the columns of the table.
94 || Known limitations, enhancements or remarks :
95 || Change History :
96 || Who When What
97 || (reverse chronological order - newest change first)
98 */
99
100 CURSOR cur_old_ref_values IS
101 SELECT *
102 FROM igs_as_dup_docs
103 WHERE rowid = x_rowid;
104
105 BEGIN
106
107 l_rowid := x_rowid;
108
109 -- Code for setting the Old and New Reference Values.
110 -- Populate Old Values.
111 OPEN cur_old_ref_values;
112 FETCH cur_old_ref_values INTO old_references;
113 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
114 CLOSE cur_old_ref_values;
115 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 RETURN;
119 END IF;
120 CLOSE cur_old_ref_values;
121
122 -- Populate New Values.
123 new_references.order_number := x_order_number;
124 new_references.item_number := x_item_number;
125 new_references.requested_by := x_requested_by;
126 new_references.requested_date := x_requested_date;
127 new_references.fulfilled_by := x_fulfilled_by;
128 new_references.fulfilled_date := x_fulfilled_date;
129
130 IF (p_action = 'UPDATE') THEN
131 new_references.creation_date := old_references.creation_date;
132 new_references.created_by := old_references.created_by;
133 ELSE
134 new_references.creation_date := x_creation_date;
135 new_references.created_by := x_created_by;
136 END IF;
137
138 new_references.last_update_date := x_last_update_date;
139 new_references.last_updated_by := x_last_updated_by;
140 new_references.last_update_login := x_last_update_login;
141
142 END set_column_values;
143
144 PROCEDURE before_dml (
145 p_action IN VARCHAR2,
146 x_rowid IN VARCHAR2,
147 x_order_number IN NUMBER,
148 x_item_number IN NUMBER,
149 x_requested_by IN NUMBER,
150 x_requested_date IN DATE,
151 x_fulfilled_by IN NUMBER,
152 x_fulfilled_date IN DATE,
153 x_creation_date IN DATE,
154 x_created_by IN NUMBER,
155 x_last_update_date IN DATE,
156 x_last_updated_by IN NUMBER,
157 x_last_update_login IN NUMBER
158 ) AS
159 /*
160 || Created By : [email protected]
161 || Created On : 28-OCT-2002
162 || Purpose : Initialises the columns, Checks Constraints, Calls the
163 || Trigger Handlers for the table, before any DML operation.
164 || Known limitations, enhancements or remarks :
165 || Change History :
166 || Who When What
167 || (reverse chronological order - newest change first)
168 */
169 BEGIN
170
171 set_column_values (
172 p_action,
173 x_rowid,
174 x_order_number,
175 x_item_number,
176 x_requested_by,
177 x_requested_date,
178 x_fulfilled_by,
179 x_fulfilled_date,
180 x_creation_date,
181 x_created_by,
182 x_last_update_date,
183 x_last_updated_by,
184 x_last_update_login
185 );
186
187 END before_dml;
188
189 PROCEDURE insert_row (
190 x_rowid IN OUT NOCOPY VARCHAR2,
191 x_order_number IN NUMBER,
192 x_item_number IN NUMBER,
193 x_requested_by IN NUMBER,
194 x_requested_date IN DATE,
195 x_fulfilled_by IN NUMBER,
196 x_fulfilled_date IN DATE,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_msg_data OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_mode IN VARCHAR2
201 ) AS
202 /*
203 || Created By : [email protected]
204 || Created On : 28-OCT-2002
205 || Purpose : Handles the INSERT DML logic for the table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || (reverse chronological order - newest change first)
210 */
211
212 x_last_update_date DATE;
213 x_last_updated_by NUMBER;
214 x_last_update_login NUMBER;
215
216 BEGIN
217
218 x_last_update_date := SYSDATE;
219 IF (x_mode = 'I') THEN
220 x_last_updated_by := 1;
221 x_last_update_login := 0;
222 ELSIF (x_mode = 'R') THEN
223 x_last_updated_by := fnd_global.user_id;
224 IF (x_last_updated_by IS NULL) THEN
225 x_last_updated_by := -1;
226 END IF;
227 x_last_update_login := fnd_global.login_id;
228 IF (x_last_update_login IS NULL) THEN
229 x_last_update_login := -1;
230 END IF;
231 ELSE
232 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
233 igs_ge_msg_stack.add;
234 app_exception.raise_exception;
235 END IF;
236
237 before_dml(
238 p_action => 'INSERT',
239 x_rowid => x_rowid,
240 x_order_number => x_order_number,
241 x_item_number => x_item_number,
242 x_requested_by => x_requested_by,
243 x_requested_date => x_requested_date,
244 x_fulfilled_by => x_fulfilled_by,
245 x_fulfilled_date => x_fulfilled_date,
246 x_creation_date => x_last_update_date,
247 x_created_by => x_last_updated_by,
248 x_last_update_date => x_last_update_date,
249 x_last_updated_by => x_last_updated_by,
250 x_last_update_login => x_last_update_login
251 );
252
253 INSERT INTO igs_as_dup_docs (
254 order_number,
255 item_number,
256 requested_by,
257 requested_date,
258 fulfilled_by,
259 fulfilled_date,
260 creation_date,
261 created_by,
262 last_update_date,
263 last_updated_by,
264 last_update_login
265 ) VALUES (
266 new_references.order_number,
267 new_references.item_number,
268 new_references.requested_by,
269 new_references.requested_date,
270 new_references.fulfilled_by,
271 new_references.fulfilled_date,
272 x_last_update_date,
273 x_last_updated_by,
274 x_last_update_date,
275 x_last_updated_by,
276 x_last_update_login
277 ) RETURNING ROWID INTO x_rowid;
278
279 --
280 -- Update the order and item status to In Process and create an Interface record
281 process_duplicate_documents
282 (
283 x_order_number => new_references.order_number,
284 x_item_number => new_references.item_number,
285 x_return_status => x_return_status,
286 x_msg_data => x_msg_data,
287 x_msg_count => x_msg_count
288 );
289
290 IF (NVL (x_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success) THEN
291 RETURN;
292 END IF;
293
294 -- Initialize API return status to success.
295 x_return_status := fnd_api.g_ret_sts_success;
296
297 -- Standard call to get message count and if count is 1, get message info.
298 fnd_msg_pub.count_and_get (
299 p_encoded => fnd_api.g_false,
300 p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303
304 EXCEPTION
305 WHEN FND_API.G_EXC_ERROR THEN
306 x_return_status := fnd_api.g_ret_sts_error;
307 fnd_msg_pub.count_and_get(
308 p_encoded => fnd_api.g_false,
309 p_count => x_msg_count,
310 p_data => x_msg_data
311 );
312 RETURN;
313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
314 x_return_status := fnd_api.g_ret_sts_unexp_error;
315 fnd_msg_pub.count_and_get(
316 p_encoded => fnd_api.g_false,
317 p_count => x_msg_count,
318 p_data => x_msg_data
319 );
320 RETURN;
321 WHEN OTHERS THEN
322 x_return_status := fnd_api.g_ret_sts_unexp_error;
323 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
324 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
325 fnd_msg_pub.add;
326 fnd_msg_pub.count_and_get(
327 p_encoded => fnd_api.g_false,
328 p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331 RETURN;
332 END insert_row;
333
334 PROCEDURE lock_row (
335 x_rowid IN VARCHAR2,
336 x_order_number IN NUMBER,
337 x_item_number IN NUMBER,
338 x_requested_by IN NUMBER,
339 x_requested_date IN DATE,
340 x_fulfilled_by IN NUMBER,
341 x_fulfilled_date IN DATE,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_data OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER
345 ) AS
346 /*
347 || Created By : [email protected]
348 || Created On : 28-OCT-2002
349 || Purpose : Handles the LOCK mechanism for the table.
350 || Known limitations, enhancements or remarks :
351 || Change History :
352 || Who When What
353 || (reverse chronological order - newest change first)
354 */
355 CURSOR c1 IS
356 SELECT
357 order_number,
358 item_number,
359 requested_by,
360 requested_date,
361 fulfilled_by,
362 fulfilled_date
363 FROM igs_as_dup_docs
364 WHERE rowid = x_rowid
365 FOR UPDATE NOWAIT;
366
367 tlinfo c1%ROWTYPE;
368
369 BEGIN
370
371 OPEN c1;
372 FETCH c1 INTO tlinfo;
373 IF (c1%notfound) THEN
374 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
375 igs_ge_msg_stack.add;
376 CLOSE c1;
377 app_exception.raise_exception;
378 RETURN;
379 END IF;
380 CLOSE c1;
381
382 IF (
383 (tlinfo.order_number = x_order_number)
384 AND (tlinfo.item_number = x_item_number)
385 AND (tlinfo.requested_by = x_requested_by)
386 AND (tlinfo.requested_date = x_requested_date)
387 AND ((tlinfo.fulfilled_by = x_fulfilled_by) OR ((tlinfo.fulfilled_by IS NULL) AND (X_fulfilled_by IS NULL)))
388 AND ((tlinfo.fulfilled_date = x_fulfilled_date) OR ((tlinfo.fulfilled_date IS NULL) AND (X_fulfilled_date IS NULL)))
389 ) THEN
390 NULL;
391 ELSE
392 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
393 igs_ge_msg_stack.add;
394 app_exception.raise_exception;
395 END IF;
396
397 -- Initialize API return status to success.
398 x_return_status := fnd_api.g_ret_sts_success;
399
400 -- Standard call to get message count and if count is 1, get message info.
401 fnd_msg_pub.count_and_get (
402 p_encoded => fnd_api.g_false,
403 p_count => x_msg_count,
404 p_data => x_msg_data
405 );
406
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 x_return_status := fnd_api.g_ret_sts_error;
410 fnd_msg_pub.count_and_get(
411 p_encoded => fnd_api.g_false,
412 p_count => x_msg_count,
413 p_data => x_msg_data
414 );
415 RETURN;
416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
417 x_return_status := fnd_api.g_ret_sts_unexp_error;
418 fnd_msg_pub.count_and_get(
419 p_encoded => fnd_api.g_false,
420 p_count => x_msg_count,
421 p_data => x_msg_data
422 );
423 RETURN;
424 WHEN OTHERS THEN
425 x_return_status := fnd_api.g_ret_sts_unexp_error;
426 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
427 FND_MESSAGE.SET_TOKEN('NAME','Lock_Row : '||SQLERRM);
428 fnd_msg_pub.add;
429 fnd_msg_pub.count_and_get(
430 p_encoded => fnd_api.g_false,
431 p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434 RETURN;
435 END lock_row;
436
437 PROCEDURE update_row (
438 x_rowid IN VARCHAR2,
439 x_order_number IN NUMBER,
440 x_item_number IN NUMBER,
441 x_requested_by IN NUMBER,
442 x_requested_date IN DATE,
443 x_fulfilled_by IN NUMBER,
444 x_fulfilled_date IN DATE,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_data OUT NOCOPY VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
448 x_mode IN VARCHAR2
449 ) AS
450 /*
451 || Created By : [email protected]
452 || Created On : 28-OCT-2002
453 || Purpose : Handles the UPDATE DML logic for the table.
454 || Known limitations, enhancements or remarks :
455 || Change History :
456 || Who When What
457 || (reverse chronological order - newest change first)
458 */
459 x_last_update_date DATE ;
460 x_last_updated_by NUMBER;
461 x_last_update_login NUMBER;
462
463 BEGIN
464
465 x_last_update_date := SYSDATE;
466 IF (X_MODE = 'I') THEN
467 x_last_updated_by := 1;
468 x_last_update_login := 0;
469 ELSIF (x_mode = 'R') THEN
470 x_last_updated_by := fnd_global.user_id;
471 IF x_last_updated_by IS NULL THEN
472 x_last_updated_by := -1;
473 END IF;
474 x_last_update_login := fnd_global.login_id;
475 IF (x_last_update_login IS NULL) THEN
476 x_last_update_login := -1;
477 END IF;
478 ELSE
479 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
480 igs_ge_msg_stack.add;
481 app_exception.raise_exception;
482 END IF;
483
484 before_dml(
485 p_action => 'UPDATE',
486 x_rowid => x_rowid,
487 x_order_number => x_order_number,
488 x_item_number => x_item_number,
489 x_requested_by => x_requested_by,
490 x_requested_date => x_requested_date,
491 x_fulfilled_by => x_fulfilled_by,
492 x_fulfilled_date => x_fulfilled_date,
493 x_creation_date => x_last_update_date,
494 x_created_by => x_last_updated_by,
495 x_last_update_date => x_last_update_date,
496 x_last_updated_by => x_last_updated_by,
497 x_last_update_login => x_last_update_login
498 );
499
500 UPDATE igs_as_dup_docs
501 SET
502 order_number = new_references.order_number,
503 item_number = new_references.item_number,
504 requested_by = new_references.requested_by,
505 requested_date = new_references.requested_date,
506 fulfilled_by = new_references.fulfilled_by,
507 fulfilled_date = new_references.fulfilled_date,
508 last_update_date = x_last_update_date,
509 last_updated_by = x_last_updated_by,
510 last_update_login = x_last_update_login
511 WHERE rowid = x_rowid;
512
513 IF (SQL%NOTFOUND) THEN
514 RAISE NO_DATA_FOUND;
515 END IF;
516
517 END update_row;
518
519 PROCEDURE delete_row (
520 x_rowid IN VARCHAR2
521 ) AS
522 /*
523 || Created By : [email protected]
524 || Created On : 28-OCT-2002
525 || Purpose : Handles the DELETE DML logic for the table.
526 || Known limitations, enhancements or remarks :
527 || Change History :
528 || Who When What
529 || (reverse chronological order - newest change first)
530 */
531 BEGIN
532
533 before_dml (
534 p_action => 'DELETE',
535 x_rowid => x_rowid
536 );
537
538 DELETE FROM igs_as_dup_docs
539 WHERE rowid = x_rowid;
540
541 IF (SQL%NOTFOUND) THEN
542 RAISE NO_DATA_FOUND;
543 END IF;
544
545 END delete_row;
546
547 END igs_as_dup_docs_pkg;