eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGF_SL_COD_DOC_DTLS_PKG

Source


1 PACKAGE BODY igf_sl_cod_doc_dtls_pkg AS
2 /* $Header: IGFLI35B.pls 120.0 2005/06/02 18:00:46 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_sl_cod_doc_dtls%ROWTYPE;
6   new_references igf_sl_cod_doc_dtls%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_document_id_txt                       IN     VARCHAR2,
12     x_outbound_doc                      IN     CLOB,
13     x_inbound_doc                       IN     CLOB,
14     x_send_date                         IN     DATE,
15     x_ack_date                          IN     DATE,
16     x_doc_status                        IN     VARCHAR2,
17     x_doc_type                          IN     VARCHAR2,
18     x_full_resp_code                    IN     VARCHAR2,
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   ) AS
25   /*
26   ||  Created By : [email protected]
27   ||  Created On : 27-SEP-2004
28   ||  Purpose : Initialises the Old and New references for the columns of the table.
29   ||  Known limitations, enhancements or remarks :
30   ||  Change History :
31   ||  Who             When            What
32   ||  (reverse chronological order - newest change first)
33   */
34 
35     CURSOR cur_old_ref_values IS
36       SELECT   *
37       FROM     igf_sl_cod_doc_dtls
38       WHERE    rowid = x_rowid;
39 
40   BEGIN
41 
42     l_rowid := x_rowid;
43 
44     -- Code for setting the Old and New Reference Values.
45     -- Populate Old Values.
46     OPEN cur_old_ref_values;
47     FETCH cur_old_ref_values INTO old_references;
48     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
49       CLOSE cur_old_ref_values;
50       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
51       igs_ge_msg_stack.add;
52       app_exception.raise_exception;
53       RETURN;
54     END IF;
55     CLOSE cur_old_ref_values;
56 
57     -- Populate New Values.
58     new_references.document_id_txt                       := x_document_id_txt;
59     new_references.outbound_doc                      := x_outbound_doc;
60     new_references.inbound_doc                       := x_inbound_doc;
61     new_references.send_date                         := x_send_date;
62     new_references.ack_date                          := x_ack_date;
63     new_references.doc_status                        := x_doc_status;
64     new_references.doc_type                          := x_doc_type;
65     new_references.full_resp_code                    := x_full_resp_code;
66 
67     IF (p_action = 'UPDATE') THEN
68       new_references.creation_date                   := old_references.creation_date;
69       new_references.created_by                      := old_references.created_by;
70     ELSE
71       new_references.creation_date                   := x_creation_date;
72       new_references.created_by                      := x_created_by;
73     END IF;
74 
75     new_references.last_update_date                  := x_last_update_date;
76     new_references.last_updated_by                   := x_last_updated_by;
77     new_references.last_update_login                 := x_last_update_login;
78 
79   END set_column_values;
80 
81 
82   FUNCTION get_pk_for_validation (
83     x_document_id_txt                       IN     VARCHAR2
84   ) RETURN BOOLEAN AS
85   /*
86   ||  Created By : [email protected]
87   ||  Created On : 27-SEP-2004
88   ||  Purpose : Validates the Primary Key of the table.
89   ||  Known limitations, enhancements or remarks :
90   ||  Change History :
91   ||  Who             When            What
92   ||  (reverse chronological order - newest change first)
93   */
94     CURSOR cur_rowid IS
95       SELECT   rowid
96       FROM     igf_sl_cod_doc_dtls
97       WHERE    document_id_txt = x_document_id_txt
98       FOR UPDATE NOWAIT;
99 
100     lv_rowid cur_rowid%RowType;
101 
102   BEGIN
103 
104     OPEN cur_rowid;
105     FETCH cur_rowid INTO lv_rowid;
106     IF (cur_rowid%FOUND) THEN
107       CLOSE cur_rowid;
108       RETURN(TRUE);
109     ELSE
110       CLOSE cur_rowid;
111       RETURN(FALSE);
112     END IF;
113 
114   END get_pk_for_validation;
115 
116 
117   PROCEDURE before_dml (
118     p_action                            IN     VARCHAR2,
119     x_rowid                             IN     VARCHAR2,
120     x_document_id_txt                       IN     VARCHAR2,
121     x_outbound_doc                      IN     CLOB,
122     x_inbound_doc                       IN     CLOB,
123     x_send_date                         IN     DATE,
124     x_ack_date                          IN     DATE,
125     x_doc_status                        IN     VARCHAR2,
126     x_doc_type                          IN     VARCHAR2,
127     x_full_resp_code                    IN     VARCHAR2,
128     x_creation_date                     IN     DATE,
129     x_created_by                        IN     NUMBER,
130     x_last_update_date                  IN     DATE,
131     x_last_updated_by                   IN     NUMBER,
132     x_last_update_login                 IN     NUMBER
133   ) AS
134   /*
135   ||  Created By : [email protected]
136   ||  Created On : 27-SEP-2004
137   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
138   ||            Trigger Handlers for the table, before any DML operation.
139   ||  Known limitations, enhancements or remarks :
140   ||  Change History :
141   ||  Who             When            What
142   ||  (reverse chronological order - newest change first)
143   */
144   BEGIN
145 
146     set_column_values (
147       p_action,
148       x_rowid,
149       x_document_id_txt,
150       x_outbound_doc,
151       x_inbound_doc,
152       x_send_date,
153       x_ack_date,
154       x_doc_status,
155       x_doc_type,
156       x_full_resp_code,
157       x_creation_date,
158       x_created_by,
159       x_last_update_date,
160       x_last_updated_by,
161       x_last_update_login
162     );
163 
164     IF (p_action = 'INSERT') THEN
165       -- Call all the procedures related to Before Insert.
166       IF ( get_pk_for_validation(
167              new_references.document_id_txt
168            )
169          ) THEN
170         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
171         igs_ge_msg_stack.add;
172         app_exception.raise_exception;
173       END IF;
174     ELSIF (p_action = 'VALIDATE_INSERT') THEN
175       -- Call all the procedures related to Before Insert.
176       IF ( get_pk_for_validation (
177              new_references.document_id_txt
178            )
179          ) THEN
180         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
181         igs_ge_msg_stack.add;
182         app_exception.raise_exception;
183       END IF;
184     END IF;
185 
186   END before_dml;
187 
188 
189   PROCEDURE insert_row (
190     x_rowid                             IN OUT NOCOPY VARCHAR2,
191     x_document_id_txt                       IN     VARCHAR2,
192     x_outbound_doc                      IN     CLOB,
193     x_inbound_doc                       IN     CLOB,
194     x_send_date                         IN     DATE,
195     x_ack_date                          IN     DATE,
196     x_doc_status                        IN     VARCHAR2,
197     x_doc_type                          IN     VARCHAR2,
198     x_full_resp_code                    IN     VARCHAR2,
199     x_mode                              IN     VARCHAR2
200   ) AS
201   /*
202   ||  Created By : [email protected]
203   ||  Created On : 27-SEP-2004
204   ||  Purpose : Handles the INSERT DML logic for the table.
205   ||  Known limitations, enhancements or remarks :
206   ||  Change History :
207   ||  Who             When            What
208   ||  (reverse chronological order - newest change first)
209   */
210   CURSOR c IS
211       SELECT   rowid
212       FROM     igf_sl_cod_doc_dtls
213       WHERE    document_id_txt                    = x_document_id_txt;
214 
215     x_last_update_date           DATE;
216     x_last_updated_by            NUMBER;
217     x_last_update_login          NUMBER;
218     x_request_id                 NUMBER;
219     x_program_id                 NUMBER;
220     x_program_application_id     NUMBER;
221     x_program_update_date        DATE;
222 
223   BEGIN
224 
225     x_last_update_date := SYSDATE;
226     IF (x_mode = 'I') THEN
227       x_last_updated_by := 1;
228       x_last_update_login := 0;
229     ELSIF (x_mode = 'R') THEN
230       x_last_updated_by := fnd_global.user_id;
231       IF (x_last_updated_by IS NULL) THEN
232         x_last_updated_by := -1;
233       END IF;
234       x_last_update_login := fnd_global.login_id;
235       IF (x_last_update_login IS NULL) THEN
236         x_last_update_login := -1;
237       END IF;
238       x_request_id             := fnd_global.conc_request_id;
239       x_program_id             := fnd_global.conc_program_id;
240       x_program_application_id := fnd_global.prog_appl_id;
241 
242       IF (x_request_id = -1) THEN
243         x_request_id             := NULL;
244         x_program_id             := NULL;
245         x_program_application_id := NULL;
246         x_program_update_date    := NULL;
247       ELSE
248         x_program_update_date    := SYSDATE;
249       END IF;
250     ELSE
251       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
252       fnd_message.set_token ('ROUTINE', 'IGF_SL_COD_DOC_DTLS_PKG.INSERT_ROW');
253       igs_ge_msg_stack.add;
254       app_exception.raise_exception;
255     END IF;
256 
257     before_dml(
258       p_action                            => 'INSERT',
259       x_rowid                             => x_rowid,
260       x_document_id_txt                       => x_document_id_txt,
261       x_outbound_doc                      => x_outbound_doc,
262       x_inbound_doc                       => x_inbound_doc,
263       x_send_date                         => x_send_date,
264       x_ack_date                          => x_ack_date,
265       x_doc_status                        => x_doc_status,
266       x_doc_type                          => x_doc_type,
267       x_full_resp_code                    => x_full_resp_code,
268       x_creation_date                     => x_last_update_date,
269       x_created_by                        => x_last_updated_by,
270       x_last_update_date                  => x_last_update_date,
271       x_last_updated_by                   => x_last_updated_by,
272       x_last_update_login                 => x_last_update_login
273     );
274 
275     INSERT INTO igf_sl_cod_doc_dtls (
276       document_id_txt,
277       outbound_doc,
278       inbound_doc,
279       send_date,
280       ack_date,
281       doc_status,
282       doc_type,
283       full_resp_code,
284       creation_date,
285       created_by,
286       last_update_date,
287       last_updated_by,
288       last_update_login,
289       request_id,
290       program_id,
291       program_application_id,
292       program_update_date
293     ) VALUES (
294       new_references.document_id_txt,
295       new_references.outbound_doc,
296       new_references.inbound_doc,
297       new_references.send_date,
298       new_references.ack_date,
299       new_references.doc_status,
300       new_references.doc_type,
301       new_references.full_resp_code,
302       x_last_update_date,
303       x_last_updated_by,
304       x_last_update_date,
305       x_last_updated_by,
306       x_last_update_login ,
307       x_request_id,
308       x_program_id,
309       x_program_application_id,
310       x_program_update_date
311     ) RETURNING ROWID INTO x_rowid;
312 
313   OPEN c;
314     FETCH c INTO x_rowid;
315     IF (c%NOTFOUND) THEN
316       CLOSE c;
317       RAISE NO_DATA_FOUND;
318     END IF;
319     CLOSE c;
320 
321   END insert_row;
322 
323 
324   PROCEDURE lock_row (
325     x_rowid                             IN     VARCHAR2,
326     x_document_id_txt                       IN     VARCHAR2,
327     x_outbound_doc                      IN     CLOB,
328     x_inbound_doc                       IN     CLOB,
332     x_doc_type                          IN     VARCHAR2,
329     x_send_date                         IN     DATE,
330     x_ack_date                          IN     DATE,
331     x_doc_status                        IN     VARCHAR2,
333     x_full_resp_code                    IN     VARCHAR2
334   ) AS
335   /*
336   ||  Created By : [email protected]
337   ||  Created On : 27-SEP-2004
338   ||  Purpose : Handles the LOCK mechanism for the table.
339   ||  Known limitations, enhancements or remarks :
340   ||  Change History :
341   ||  Who             When            What
342   ||  (reverse chronological order - newest change first)
343   */
344     CURSOR c1 IS
345       SELECT
346         outbound_doc,
347         inbound_doc,
348         send_date,
349         ack_date,
350         doc_status,
351         doc_type,
352         full_resp_code
353       FROM  igf_sl_cod_doc_dtls
354       WHERE rowid = x_rowid
355       FOR UPDATE NOWAIT;
356 
357     tlinfo c1%ROWTYPE;
358 
359   BEGIN
360 
361     OPEN c1;
362     FETCH c1 INTO tlinfo;
363     IF (c1%notfound) THEN
364       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
365       igs_ge_msg_stack.add;
366       CLOSE c1;
367       app_exception.raise_exception;
368       RETURN;
369     END IF;
370     CLOSE c1;
371 
372     IF (
373         ((tlinfo.send_date = x_send_date) OR ((tlinfo.send_date IS NULL) AND (X_send_date IS NULL)))
374         AND ((tlinfo.ack_date = x_ack_date) OR ((tlinfo.ack_date IS NULL) AND (X_ack_date IS NULL)))
375         AND ((tlinfo.doc_status = x_doc_status) OR ((tlinfo.doc_status IS NULL) AND (X_doc_status IS NULL)))
376         AND ((tlinfo.doc_type = x_doc_type) OR ((tlinfo.doc_type IS NULL) AND (X_doc_type IS NULL)))
377         AND ((tlinfo.full_resp_code = x_full_resp_code) OR ((tlinfo.full_resp_code IS NULL) AND (X_full_resp_code IS NULL)))
378        ) THEN
379       NULL;
380     ELSE
381       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
382       igs_ge_msg_stack.add;
383       app_exception.raise_exception;
384     END IF;
385 
386     RETURN;
387 
388   END lock_row;
389 
390 
391   PROCEDURE update_row (
392     x_rowid                             IN     VARCHAR2,
393     x_document_id_txt                       IN     VARCHAR2,
394     x_outbound_doc                      IN     CLOB,
395     x_inbound_doc                       IN     CLOB,
396     x_send_date                         IN     DATE,
397     x_ack_date                          IN     DATE,
398     x_doc_status                        IN     VARCHAR2,
399     x_doc_type                          IN     VARCHAR2,
400     x_full_resp_code                    IN     VARCHAR2,
401     x_mode                              IN     VARCHAR2
402   ) AS
403   /*
404   ||  Created By : [email protected]
405   ||  Created On : 27-SEP-2004
406   ||  Purpose : Handles the UPDATE DML logic for the table.
407   ||  Known limitations, enhancements or remarks :
408   ||  Change History :
409   ||  Who             When            What
410   ||  (reverse chronological order - newest change first)
411   */
412     x_last_update_date           DATE ;
413     x_last_updated_by            NUMBER;
414     x_last_update_login          NUMBER;
415     x_request_id                 NUMBER;
416     x_program_id                 NUMBER;
417     x_program_application_id     NUMBER;
418     x_program_update_date        DATE;
419 
420   BEGIN
421 
422     x_last_update_date := SYSDATE;
423     IF (X_MODE = 'I') THEN
424       x_last_updated_by := 1;
425       x_last_update_login := 0;
426     ELSIF (x_mode = 'R') THEN
427       x_last_updated_by := fnd_global.user_id;
428       IF x_last_updated_by IS NULL THEN
429         x_last_updated_by := -1;
430       END IF;
431       x_last_update_login := fnd_global.login_id;
432       IF (x_last_update_login IS NULL) THEN
433         x_last_update_login := -1;
434       END IF;
435     ELSE
436       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
437       fnd_message.set_token ('ROUTINE', 'IGF_SL_COD_DOC_DTLS_PKG.UPDATE_ROW');
438       igs_ge_msg_stack.add;
439       app_exception.raise_exception;
440     END IF;
441 
442     before_dml(
443       p_action                            => 'UPDATE',
444       x_rowid                             => x_rowid,
445       x_document_id_txt                       => x_document_id_txt,
446       x_outbound_doc                      => x_outbound_doc,
447       x_inbound_doc                       => x_inbound_doc,
448       x_send_date                         => x_send_date,
449       x_ack_date                          => x_ack_date,
450       x_doc_status                        => x_doc_status,
451       x_doc_type                          => x_doc_type,
452       x_full_resp_code                    => x_full_resp_code,
453       x_creation_date                     => x_last_update_date,
454       x_created_by                        => x_last_updated_by,
455       x_last_update_date                  => x_last_update_date,
456       x_last_updated_by                   => x_last_updated_by,
457       x_last_update_login                 => x_last_update_login
458     );
459 
460     IF (x_mode = 'R') THEN
461       x_request_id := fnd_global.conc_request_id;
462       x_program_id := fnd_global.conc_program_id;
463       x_program_application_id := fnd_global.prog_appl_id;
464       IF (x_request_id =  -1) THEN
465         x_request_id := old_references.request_id;
466         x_program_id := old_references.program_id;
467         x_program_application_id := old_references.program_application_id;
468         x_program_update_date := old_references.program_update_date;
469       ELSE
470         x_program_update_date := SYSDATE;
471       END IF;
472     END IF;
473 
474     UPDATE igf_sl_cod_doc_dtls
475       SET
476         outbound_doc                      = new_references.outbound_doc,
477         inbound_doc                       = new_references.inbound_doc,
478         send_date                         = new_references.send_date,
479         ack_date                          = new_references.ack_date,
480         doc_status                        = new_references.doc_status,
481         doc_type                          = new_references.doc_type,
482         full_resp_code                    = new_references.full_resp_code,
483         last_update_date                  = x_last_update_date,
484         last_updated_by                   = x_last_updated_by,
485         last_update_login                 = x_last_update_login ,
486         request_id                        = x_request_id,
487         program_id                        = x_program_id,
488         program_application_id            = x_program_application_id,
489         program_update_date               = x_program_update_date
490       WHERE rowid = x_rowid;
491 
492     IF (SQL%NOTFOUND) THEN
493       RAISE NO_DATA_FOUND;
494     END IF;
495 
496   END update_row;
497 
498 
499   PROCEDURE add_row (
500     x_rowid                             IN OUT NOCOPY VARCHAR2,
501     x_document_id_txt                       IN     VARCHAR2,
502     x_outbound_doc                      IN     CLOB,
503     x_inbound_doc                       IN     CLOB,
504     x_send_date                         IN     DATE,
505     x_ack_date                          IN     DATE,
506     x_doc_status                        IN     VARCHAR2,
507     x_doc_type                          IN     VARCHAR2,
508     x_full_resp_code                    IN     VARCHAR2,
509     x_mode                              IN     VARCHAR2
510   ) AS
511   /*
512   ||  Created By : [email protected]
513   ||  Created On : 27-SEP-2004
514   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
515   ||  Known limitations, enhancements or remarks :
516   ||  Change History :
517   ||  Who             When            What
518   ||  (reverse chronological order - newest change first)
519   */
520     CURSOR c1 IS
521       SELECT   rowid
522       FROM     igf_sl_cod_doc_dtls
523       WHERE    document_id_txt = x_document_id_txt;
524 
525   BEGIN
526 
527     OPEN c1;
528     FETCH c1 INTO x_rowid;
529     IF (c1%NOTFOUND) THEN
530       CLOSE c1;
531 
532       insert_row (
533         x_rowid,
534         x_document_id_txt,
535         x_outbound_doc,
536         x_inbound_doc,
537         x_send_date,
538         x_ack_date,
539         x_doc_status,
540         x_doc_type,
541         x_full_resp_code,
542         x_mode
543       );
544       RETURN;
545     END IF;
546     CLOSE c1;
547 
548     update_row (
549       x_rowid,
550       x_document_id_txt,
551       x_outbound_doc,
552       x_inbound_doc,
553       x_send_date,
554       x_ack_date,
555       x_doc_status,
556       x_doc_type,
557       x_full_resp_code,
558       x_mode
559     );
560 
561   END add_row;
562 
563 
564   PROCEDURE delete_row (
565     x_rowid IN VARCHAR2
566   ) AS
567   /*
568   ||  Created By : [email protected]
569   ||  Created On : 27-SEP-2004
570   ||  Purpose : Handles the DELETE DML logic for the table.
571   ||  Known limitations, enhancements or remarks :
572   ||  Change History :
573   ||  Who             When            What
574   ||  (reverse chronological order - newest change first)
575   */
576   BEGIN
577 
578     before_dml (
579       p_action => 'DELETE',
580       x_rowid => x_rowid
581     );
582 
583     DELETE FROM igf_sl_cod_doc_dtls
584     WHERE rowid = x_rowid;
585 
586     IF (SQL%NOTFOUND) THEN
587       RAISE NO_DATA_FOUND;
588     END IF;
589 
590   END delete_row;
591 
592 
593 END igf_sl_cod_doc_dtls_pkg;