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.IGS_PE_OFFICE_HRS_PKG

Source


1 PACKAGE BODY igs_pe_office_hrs_pkg AS
2 /* $Header: IGSNIB3B.pls 120.1 2005/06/28 06:07:07 appldev ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_pe_office_hrs%ROWTYPE;
6   new_references igs_pe_office_hrs%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_office_hrs_id                     IN     NUMBER,
12     x_contact_preference_id             IN     NUMBER,
13     x_day_of_week_code                       IN     VARCHAR2,
14     x_start_tm_date                          IN     DATE,
15     x_end_tm_date                            IN     DATE,
16     x_creation_date                     IN     DATE,
17     x_created_by                        IN     NUMBER,
18     x_last_update_date                  IN     DATE,
19     x_last_updated_by                   IN     NUMBER,
20     x_last_update_login                 IN     NUMBER
21   ) AS
22   /*
23   ||  Created By : [email protected]
24   ||  Created On : 05-JUN-2003
25   ||  Purpose : Initialises the Old and New references for the columns of the table.
26   ||  Known limitations, enhancements or remarks :
27   ||  Change History :
28   ||  Who             When            What
29   ||  (reverse chronological order - newest change first)
30   */
31 
32     CURSOR cur_old_ref_values IS
33       SELECT   *
34       FROM     igs_pe_office_hrs
35       WHERE    rowid = x_rowid;
36 
37   BEGIN
38 
39     l_rowid := x_rowid;
40 
41     -- Code for setting the Old and New Reference Values.
42     -- Populate Old Values.
43     OPEN cur_old_ref_values;
44     FETCH cur_old_ref_values INTO old_references;
45     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46       CLOSE cur_old_ref_values;
47       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48       igs_ge_msg_stack.add;
49       app_exception.raise_exception;
50       RETURN;
51     END IF;
52     CLOSE cur_old_ref_values;
53 
54     -- Populate New Values.
55     new_references.office_hrs_id                     := x_office_hrs_id;
56     new_references.contact_preference_id             := x_contact_preference_id;
57     new_references.day_of_week_code                       := x_day_of_week_code;
58     new_references.start_tm_date                          := x_start_tm_date;
59     new_references.end_tm_date                            := x_end_tm_date;
60 
61     IF (p_action = 'UPDATE') THEN
62       new_references.creation_date                   := old_references.creation_date;
63       new_references.created_by                      := old_references.created_by;
64     ELSE
65       new_references.creation_date                   := x_creation_date;
66       new_references.created_by                      := x_created_by;
67     END IF;
68 
69     new_references.last_update_date                  := x_last_update_date;
70     new_references.last_updated_by                   := x_last_updated_by;
71     new_references.last_update_login                 := x_last_update_login;
72 
73   END set_column_values;
74 
75 
76 
77 PROCEDURE validate_overlap (
78 p_contact_preference_id IN igs_pe_office_hrs.CONTACT_PREFERENCE_ID%type,
79 p_day_of_week_code IN igs_pe_office_hrs.day_of_week_code%TYPE,
80 p_start_tm_date IN DATE,
81 p_end_tm_date IN DATE,
82 P_OFFID NUMBER)   AS
83 /*
84   ||  Created By : [email protected]
85   ||  Created On : 05-JUN-2003
86   ||  Purpose : overlap check.
87   ||  Known limitations, enhancements or remarks :
88   ||  Change History :
89   ||  Who             When            What
90   ||  (reverse chronological order - newest change first)
91   ||  pkpatel         18-JUL-2003     Bug 3026057
92   ||                                  Consider only the time component in the overlap check
93   */
94 
95   CURSOR c_overlap (cp_contact_preference_id igs_pe_office_hrs.CONTACT_PREFERENCE_ID%type,
96   cp_day_of_week_code igs_pe_office_hrs.day_of_week_code%TYPE,
97   cp_start_tm_date DATE,
98   cp_end_tm_date DATE,
99   cp_offid NUMBER) IS
100   SELECT count(1)
101   FROM igs_pe_office_hrs
102   WHERE contact_preference_id = cp_contact_preference_id AND
103         (CP_OFFID <> OFFICE_HRS_ID OR CP_OFFID IS NULL) AND
104     	day_of_week_code = cp_day_of_week_code AND
105        ( TO_DATE(TO_CHAR(end_tm_date,'HH24:MI'),'HH24:MI') > cp_start_tm_date OR TO_DATE(TO_CHAR(end_tm_date,'HH24:MI'),'HH24:MI') >= cp_end_tm_date) AND
106        (TO_DATE(TO_CHAR(start_tm_date,'HH24:MI'),'HH24:MI') <= cp_start_tm_date OR TO_DATE(TO_CHAR(start_tm_date,'HH24:MI'),'HH24:MI') < cp_end_tm_date);
107 
108 
109   l_count NUMBER(2) :=0;
110   l_start_time   DATE := TO_DATE(TO_CHAR(p_start_tm_date,'HH24:MI'),'HH24:MI');
111   l_end_time     DATE := TO_DATE(TO_CHAR(p_end_tm_date,'HH24:MI'),'HH24:MI');
112   BEGIN
113 
114   OPEN c_overlap(p_contact_preference_id,p_day_of_week_code,l_start_time,l_end_time,p_offid);
115 
116   FETCH c_overlap INTO l_count;
117 
118     IF l_count > 0 THEN
119         FND_MESSAGE.SET_NAME('IGS','IGS_GE_TIME_OVERLAP');
120     	IGS_GE_MSG_STACK.ADD;
121         APP_EXCEPTION.RAISE_EXCEPTION;
122 	END IF;
123 
124   CLOSE c_overlap;
125 
126   END validate_overlap;
127 
128 
129 
130 
131   PROCEDURE BeforeRowInsertUpdate1(
132     p_inserting IN BOOLEAN,
133     p_updating IN BOOLEAN,
134     p_deleting IN BOOLEAN
135     ) AS
136 /*
137   ||  Created By : [email protected]
138   ||  Created On : 05-JUN-2003
139   ||  Purpose : date validations.
140   ||  Known limitations, enhancements or remarks :
141   ||  Change History :
142   ||  Who             When            What
143   ||  (reverse chronological order - newest change first)
144   */
145   BEGIN
146 
147   -- start time should be less than end time, while inserts and updates.
148   IF (p_inserting OR p_updating) THEN
149 
150 
151      -- check that start time less than end time
152      IF (new_references.start_tm_date <> NVL(old_references.start_tm_date,IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
153         (new_references.end_tm_date <> NVL(old_references.end_tm_date,IGS_GE_DATE.IGSDATE('1900/01/01')))  THEN
154 
155          IF to_char(new_references.start_tm_date,'HH24:MI') >= to_char(new_references.end_tm_date,'HH24:MI') THEN
156 	    FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_ST_TIME_LT_END_TIME');
157 	    IGS_GE_MSG_STACK.ADD;
158             APP_EXCEPTION.RAISE_EXCEPTION;
159 	 END IF;
160 
161       END IF;
162 
163 
164 
165    END IF;
166 
167 
168   END BeforeRowInsertUpdate1;
169 
170 
171   FUNCTION Get_PK_For_Validation (
172     x_office_hrs_id IN NUMBER
173     ) RETURN BOOLEAN AS
174 /*
175   ||  Created By : [email protected]
176   ||  Created On : 05-JUN-2003
177   ||  Purpose : PK checks
178   ||  Known limitations, enhancements or remarks :
179   ||  Change History :
180   ||  Who             When            What
181   ||  (reverse chronological order - newest change first)
182   */
183     CURSOR cur_rowid IS
184       SELECT   rowid
185       FROM     IGS_PE_OFFICE_HRS
186       WHERE    office_hrs_id = x_office_hrs_id
187       FOR UPDATE NOWAIT;
188 
189     lv_rowid cur_rowid%RowType;
190 
191   BEGIN
192 
193     Open cur_rowid;
194     Fetch cur_rowid INTO lv_rowid;
195      IF (cur_rowid%FOUND) THEN
196        Close cur_rowid;
197        Return (TRUE);
198 	 ELSE
199        Close cur_rowid;
200        Return (FALSE);
201  END IF;
202 
203  END Get_PK_For_Validation;
204 
205  PROCEDURE Check_Parent_Existance AS
206 
207      CURSOR check_cont_pref_cur IS
208 	  SELECT 'X'
209 	  FROM   hz_contact_preferences
210 	  WHERE  contact_preference_id = new_references.contact_preference_id;
211 
212      l_var  VARCHAR2(1);
213 
214  BEGIN
215 
216      IF (((old_references.contact_preference_id  = new_references.contact_preference_id )) OR
217           ((new_references.contact_preference_id  IS NULL))) THEN
218           NULL;
219      ELSE
220 
221          OPEN check_cont_pref_cur;
222 	 FETCH  check_cont_pref_cur into l_var;
223 	 IF check_cont_pref_cur%NOTFOUND THEN
224                CLOSE check_cont_pref_cur;
225                FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
226                IGS_GE_MSG_STACK.ADD;
227                APP_EXCEPTION.RAISE_EXCEPTION;
228           END IF;
229 	  CLOSE check_cont_pref_cur;
230      END IF;
231 
232  END Check_Parent_Existance;
233 
234 
235 
236   PROCEDURE before_dml (
237     p_action                            IN     VARCHAR2,
238     x_rowid                             IN     VARCHAR2,
239     x_office_hrs_id                     IN     NUMBER,
240     x_contact_preference_id             IN     NUMBER,
241     x_day_of_week_code                       IN     VARCHAR2,
242     x_start_tm_date                          IN     DATE,
243     x_end_tm_date                            IN     DATE,
244     x_creation_date                     IN     DATE,
245     x_created_by                        IN     NUMBER,
246     x_last_update_date                  IN     DATE,
247     x_last_updated_by                   IN     NUMBER,
248     x_last_update_login                 IN     NUMBER
249   ) AS
250   /*
251   ||  Created By : [email protected]
252   ||  Created On : 05-JUN-2003
253   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
254   ||            Trigger Handlers for the table, before any DML operation.
255   ||  Known limitations, enhancements or remarks :
256   ||  Change History :
257   ||  Who             When            What
258   ||  (reverse chronological order - newest change first)
259   ||  vrathi          10-JUN-2003     Added calls to procedures for locla validation of duplicate records
260   ||  pkpatel         26-JUN-2003     Bug 3026139 (Reversed the call of BeforeRowInsertUpdate1 and validate_overlap procedures)
261   */
262   BEGIN
263 
264     set_column_values (
265       p_action,
266       x_rowid,
267       x_office_hrs_id,
268       x_contact_preference_id,
269       x_day_of_week_code,
270       x_start_tm_date,
271       x_end_tm_date,
272       x_creation_date,
273       x_created_by,
274       x_last_update_date,
275       x_last_updated_by,
276       x_last_update_login
277     );
278 
279     IF (p_action = 'INSERT') THEN
280 
281       -- Call all the procedures related to Before Insert.
282       BeforeRowInsertUpdate1 (
283 	  p_inserting => TRUE,
284       p_updating  => FALSE,
285 	  p_deleting  => FALSE);
286 
287        -- validate the overlap check.(this should be done after the insert/update)
288        validate_overlap(x_contact_preference_id,x_day_of_week_code, x_start_tm_date, x_end_tm_date,X_OFFICE_HRS_ID);
289 
290       IF ( get_pk_for_validation( new_references.office_hrs_id  )
291          ) THEN
292         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
293         igs_ge_msg_stack.add;
294         app_exception.raise_exception;
295       END IF;
296       -- check valid cont pref
297       Check_Parent_Existance;
298 
299     ELSIF (p_action = 'VALIDATE_INSERT') THEN
300 
301       -- Call all the procedures related to Before Insert.
302       BeforeRowInsertUpdate1 (
303 	  p_inserting => TRUE,
304       p_updating  => FALSE,
305 	  p_deleting  => FALSE);
306 
307 	  -- Call all the procedures related to Before Insert.
308       IF ( get_pk_for_validation( new_references.office_hrs_id  )
309          ) THEN
310         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
311         igs_ge_msg_stack.add;
312         app_exception.raise_exception;
313       END IF;
314 
315     ELSIF (p_action = 'UPDATE') THEN
316 
317        -- Call all the procedures related to Before Update.
318        BeforeRowInsertUpdate1 (
319      	  p_inserting => FALSE,
320           p_updating  => TRUE,
321 	      p_deleting  => FALSE );
322 
323        -- validate the overlap check.(this should be done after the insert/update)
324        validate_overlap(x_contact_preference_id,x_day_of_week_code, x_start_tm_date, x_end_tm_date,X_OFFICE_HRS_ID);
325 
326        -- check valid cont pref
327       Check_Parent_Existance;
328     ELSIF (p_action = 'DELETE') THEN
329        -- Call all the procedures related to Before Delete.
330        null;
331     ELSIF (p_action = 'VALIDATE_DELETE') THEN
332        -- Call all the procedures related to Before Delete.
333        null;
334     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
335        -- Call all the procedures related to Before Delete.
336        -- Call all the procedures related to Before Update.
337        BeforeRowInsertUpdate1 (
338      	  p_inserting => FALSE,
339           p_updating  => TRUE,
340 	      p_deleting  => FALSE );
341 
342     END IF;
343 
344   END before_dml;
345 
346 
347   PROCEDURE insert_row (
348     x_rowid                             IN OUT NOCOPY VARCHAR2,
349     x_office_hrs_id                     IN OUT NOCOPY NUMBER,
350     x_contact_preference_id             IN     NUMBER,
351     x_day_of_week_code                       IN     VARCHAR2,
352     x_start_tm_date                          IN     DATE,
353     x_end_tm_date                            IN     DATE,
354     x_mode                              IN     VARCHAR2
355   ) AS
356   /*
357   ||  Created By : [email protected]
358   ||  Created On : 05-JUN-2003
359   ||  Purpose : Handles the INSERT DML logic for the table.
360   ||  Known limitations, enhancements or remarks :
361   ||  Change History :
362   ||  Who             When            What
363   ||  (reverse chronological order - newest change first)
364   */
365 
366     x_last_update_date           DATE;
367     x_last_updated_by            NUMBER;
368     x_last_update_login          NUMBER;
369 
370   BEGIN
371 
372     x_last_update_date := SYSDATE;
373     IF (x_mode = 'I') THEN
374       x_last_updated_by := 1;
375       x_last_update_login := 0;
376     ELSIF (X_MODE IN ('R', 'S')) THEN
377       x_last_updated_by := fnd_global.user_id;
378       IF (x_last_updated_by IS NULL) THEN
379         x_last_updated_by := -1;
380       END IF;
381       x_last_update_login := fnd_global.login_id;
382       IF (x_last_update_login IS NULL) THEN
383         x_last_update_login := -1;
384       END IF;
385     ELSE
386       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
387       fnd_message.set_token ('ROUTINE', 'IGS_PE_OFFICE_HRS_PKG.INSERT_ROW');
388       igs_ge_msg_stack.add;
389       app_exception.raise_exception;
390     END IF;
391 
392     x_office_hrs_id := NULL;
393 
394     before_dml(
395       p_action                            => 'INSERT',
396       x_rowid                             => x_rowid,
397       x_office_hrs_id                     => x_office_hrs_id,
398       x_contact_preference_id             => x_contact_preference_id,
399       x_day_of_week_code                       => x_day_of_week_code,
400       x_start_tm_date                          => x_start_tm_date,
401       x_end_tm_date                            => x_end_tm_date,
402       x_creation_date                     => x_last_update_date,
403       x_created_by                        => x_last_updated_by,
404       x_last_update_date                  => x_last_update_date,
405       x_last_updated_by                   => x_last_updated_by,
406       x_last_update_login                 => x_last_update_login
407     );
408 
409      IF (x_mode = 'S') THEN
410     igs_sc_gen_001.set_ctx('R');
411   END IF;
412  INSERT INTO igs_pe_office_hrs (
413       office_hrs_id,
414       contact_preference_id,
415       day_of_week_code,
416       start_tm_date,
417       end_tm_date,
418       creation_date,
419       created_by,
420       last_update_date,
421       last_updated_by,
422       last_update_login
423     ) VALUES (
424       igs_pe_office_hrs_s.NEXTVAL,
425       new_references.contact_preference_id,
426       new_references.day_of_week_code,
427       new_references.start_tm_date,
428       new_references.end_tm_date,
429       x_last_update_date,
430       x_last_updated_by,
431       x_last_update_date,
432       x_last_updated_by,
433       x_last_update_login
434     ) RETURNING ROWID, office_hrs_id INTO x_rowid, x_office_hrs_id;
435  IF (x_mode = 'S') THEN
436     igs_sc_gen_001.unset_ctx('R');
437   END IF;
438 
439 
440 
441 
442 
443 
444 EXCEPTION
445   WHEN OTHERS THEN
446     IF (SQLCODE IN (-28115, -28113, -28111)) THEN
447       fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
448       fnd_message.set_token ('ERR_CD', SQLCODE);
449       igs_ge_msg_stack.add;
450       igs_sc_gen_001.unset_ctx('R');
451       app_exception.raise_exception;
452     ELSE
453       igs_sc_gen_001.unset_ctx('R');
454       RAISE;
455     END IF;
456  END insert_row;
457 
458 
459   PROCEDURE lock_row (
460     x_rowid                                  IN     VARCHAR2,
461     x_office_hrs_id                          IN     NUMBER,
462     x_contact_preference_id                  IN     NUMBER,
463     x_day_of_week_code                       IN     VARCHAR2,
464     x_start_tm_date                          IN     DATE,
465     x_end_tm_date                            IN     DATE
466   ) AS
467   /*
468   ||  Created By : [email protected]
469   ||  Created On : 05-JUN-2003
470   ||  Purpose : Handles the LOCK mechanism for the table.
471   ||  Known limitations, enhancements or remarks :
472   ||  Change History :
473   ||  Who             When            What
474   ||  (reverse chronological order - newest change first)
475   */
476     CURSOR c1 IS
477       SELECT
478         office_hrs_id,
479         contact_preference_id,
480         day_of_week_code,
481         start_tm_date,
482         end_tm_date
483       FROM  igs_pe_office_hrs
484       WHERE rowid = x_rowid
485       FOR UPDATE NOWAIT;
486 
487     tlinfo c1%ROWTYPE;
488 
489   BEGIN
490 
491     OPEN c1;
492     FETCH c1 INTO tlinfo;
493     IF (c1%notfound) THEN
494       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
495       igs_ge_msg_stack.add;
496       CLOSE c1;
497       app_exception.raise_exception;
498       RETURN;
499     END IF;
500     CLOSE c1;
501 
502     IF (
503         (tlinfo.office_hrs_id = x_office_hrs_id)
504         AND (tlinfo.contact_preference_id = x_contact_preference_id)
505         AND (tlinfo.day_of_week_code = x_day_of_week_code)
506         AND (tlinfo.start_tm_date = x_start_tm_date)
507         AND (tlinfo.end_tm_date = x_end_tm_date)
508        ) THEN
509       NULL;
510     ELSE
511       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
512       igs_ge_msg_stack.add;
513       app_exception.raise_exception;
514     END IF;
515 
516     RETURN;
517 
518   END lock_row;
519 
520 
521   PROCEDURE update_row (
522     x_rowid                             IN     VARCHAR2,
523     x_office_hrs_id                     IN     NUMBER,
524     x_contact_preference_id             IN     NUMBER,
525     x_day_of_week_code                  IN     VARCHAR2,
526     x_start_tm_date                     IN     DATE,
527     x_end_tm_date                       IN     DATE,
528     x_mode                              IN     VARCHAR2
529   ) AS
530   /*
531   ||  Created By : [email protected]
532   ||  Created On : 05-JUN-2003
533   ||  Purpose : Handles the UPDATE DML logic for the table.
534   ||  Known limitations, enhancements or remarks :
535   ||  Change History :
536   ||  Who             When            What
537   ||  (reverse chronological order - newest change first)
538   */
539     x_last_update_date           DATE ;
540     x_last_updated_by            NUMBER;
541     x_last_update_login          NUMBER;
542 
543   BEGIN
544 
545     x_last_update_date := SYSDATE;
546     IF (X_MODE = 'I') THEN
547       x_last_updated_by := 1;
548       x_last_update_login := 0;
549     ELSIF (X_MODE IN ('R', 'S')) THEN
550       x_last_updated_by := fnd_global.user_id;
551       IF x_last_updated_by IS NULL THEN
552         x_last_updated_by := -1;
553       END IF;
554       x_last_update_login := fnd_global.login_id;
555       IF (x_last_update_login IS NULL) THEN
556         x_last_update_login := -1;
557       END IF;
558     ELSE
559       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
560       fnd_message.set_token ('ROUTINE', 'IGS_PE_OFFICE_HRS_PKG.UPDATE_ROW');
561       igs_ge_msg_stack.add;
562       app_exception.raise_exception;
563     END IF;
564 
565     -- x_office_hrs_id := NULL;
566 
567     before_dml(
568       p_action                            => 'UPDATE',
569       x_rowid                             => x_rowid,
570       x_office_hrs_id                     => x_office_hrs_id,
571       x_contact_preference_id             => x_contact_preference_id,
572       x_day_of_week_code                       => x_day_of_week_code,
573       x_start_tm_date                          => x_start_tm_date,
574       x_end_tm_date                            => x_end_tm_date,
575       x_creation_date                     => x_last_update_date,
576       x_created_by                        => x_last_updated_by,
577       x_last_update_date                  => x_last_update_date,
578       x_last_updated_by                   => x_last_updated_by,
579       x_last_update_login                 => x_last_update_login
580     );
581 
582      IF (x_mode = 'S') THEN
583     igs_sc_gen_001.set_ctx('R');
584   END IF;
585  UPDATE igs_pe_office_hrs
586       SET
587         office_hrs_id                     = new_references.office_hrs_id,
588         contact_preference_id             = new_references.contact_preference_id,
589         day_of_week_code                       = new_references.day_of_week_code,
590         start_tm_date                          = new_references.start_tm_date,
591         end_tm_date                            = new_references.end_tm_date,
592         last_update_date                  = x_last_update_date,
593         last_updated_by                   = x_last_updated_by,
594         last_update_login                 = x_last_update_login
595       WHERE rowid = x_rowid;
596 
597     IF (SQL%NOTFOUND) THEN
598      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
599      igs_ge_msg_stack.add;
600      igs_sc_gen_001.unset_ctx('R');
601      app_exception.raise_exception;
602  END IF;
603  IF (x_mode = 'S') THEN
604     igs_sc_gen_001.unset_ctx('R');
605   END IF;
606 
607 
608 
609 
610 EXCEPTION
611   WHEN OTHERS THEN
612     IF (SQLCODE = (-28115)) THEN
613       fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
614       fnd_message.set_token ('ERR_CD', SQLCODE);
615       igs_ge_msg_stack.add;
616       igs_sc_gen_001.unset_ctx('R');
617       app_exception.raise_exception;
618     ELSE
619       igs_sc_gen_001.unset_ctx('R');
620       RAISE;
621     END IF;
622  END update_row;
623 
624 
625   PROCEDURE add_row (
626     x_rowid                             IN OUT NOCOPY VARCHAR2,
627     x_office_hrs_id                     IN OUT NOCOPY NUMBER,
628     x_contact_preference_id             IN     NUMBER,
629     x_day_of_week_code                       IN     VARCHAR2,
630     x_start_tm_date                          IN     DATE,
631     x_end_tm_date                            IN     DATE,
632     x_mode                              IN     VARCHAR2
633   ) AS
634   /*
635   ||  Created By : [email protected]
636   ||  Created On : 05-JUN-2003
637   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
638   ||  Known limitations, enhancements or remarks :
639   ||  Change History :
640   ||  Who             When            What
641   ||  (reverse chronological order - newest change first)
642   */
643     CURSOR c1 IS
644       SELECT   rowid
645       FROM     igs_pe_office_hrs
646       WHERE    office_hrs_id= x_office_hrs_id;
647 
648   BEGIN
649 
650     OPEN c1;
651     FETCH c1 INTO x_rowid;
652     IF (c1%NOTFOUND) THEN
653       CLOSE c1;
654 
655       insert_row (
656         x_rowid,
657         x_office_hrs_id,
658         x_contact_preference_id,
659         x_day_of_week_code,
660         x_start_tm_date,
661         x_end_tm_date,
662         x_mode
663       );
664       RETURN;
665     END IF;
666     CLOSE c1;
667 
668     update_row (
669       x_rowid,
670       x_office_hrs_id,
671       x_contact_preference_id,
672       x_day_of_week_code,
673       x_start_tm_date,
674       x_end_tm_date,
675       x_mode
676     );
677 
678   END add_row;
679 
680 
681   PROCEDURE delete_row (
682     x_rowid IN VARCHAR2,
683   x_mode IN VARCHAR2
684   ) AS
685   /*
686   ||  Created By : [email protected]
687   ||  Created On : 05-JUN-2003
688   ||  Purpose : Handles the DELETE DML logic for the table.
689   ||  Known limitations, enhancements or remarks :
690   ||  Change History :
691   ||  Who             When            What
692   ||  (reverse chronological order - newest change first)
693   */
694   BEGIN
695 
696     before_dml (
697       p_action => 'DELETE',
698       x_rowid => x_rowid
699     );
700 
701      IF (x_mode = 'S') THEN
702     igs_sc_gen_001.set_ctx('R');
703   END IF;
704  DELETE FROM igs_pe_office_hrs
705     WHERE rowid = x_rowid;
706 
707     IF (SQL%NOTFOUND) THEN
708      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
709      igs_ge_msg_stack.add;
710      igs_sc_gen_001.unset_ctx('R');
711      app_exception.raise_exception;
712  END IF;
713  IF (x_mode = 'S') THEN
714     igs_sc_gen_001.unset_ctx('R');
715   END IF;
716 
717 
718   END delete_row;
719 
720 
721 
722   PROCEDURE insert_row_ss (
723     x_rowid                             IN OUT NOCOPY VARCHAR2,
724     x_office_hrs_id                     IN OUT NOCOPY NUMBER,
725     x_contact_preference_id             IN     NUMBER,
726     x_day_of_week_code                  IN     VARCHAR2,
727     x_start_tm                          IN     VARCHAR2,
728     x_end_tm                            IN     VARCHAR2,
729     x_mode                              IN     VARCHAR2
730     ) AS
731   /*
732   ||  Created By : [email protected]
733   ||  Created On : 05-JUN-2003
734   ||  Purpose : Handles the INSERT DML logic for the table when called from SS.
735   ||            The time component alone is passed here and not the complete date string.
736   ||  Known limitations, enhancements or remarks :
737   ||  Change History :
738   ||  Who             When            What
739   ||  (reverse chronological order - newest change first)
740   */
741 
742   l_start_tm_date DATE;
743   l_end_tm_date  DATE;
744   BEGIN
745 
746   l_start_tm_date := TO_DATE(x_start_tm,'HH24:MI');
747   l_end_tm_date := TO_DATE(x_end_tm,'HH24:MI');
748 
749   -- call the insert row passing the correct formatted date.
750   insert_row
751   (
752    x_rowid                   =>   x_rowid,
753    x_office_hrs_id           =>   x_office_hrs_id,
754    x_contact_preference_id   =>   x_contact_preference_id,
755    x_day_of_week_code        =>   x_day_of_week_code,
756    x_start_tm_date           =>   l_start_tm_date,
757    x_end_tm_date             =>   l_end_tm_date,
758    x_mode                    =>   'R');
759 
760   END insert_row_ss;
761 
762 
763    PROCEDURE update_row_ss (
764     x_rowid                             IN     VARCHAR2,
765     x_office_hrs_id                     IN     NUMBER,
766     x_contact_preference_id             IN     NUMBER,
767     x_day_of_week_code                  IN     VARCHAR2,
768     x_start_tm                          IN     VARCHAR2,
769     x_end_tm                            IN     VARCHAR2,
770     x_mode                              IN     VARCHAR2)
771     AS
772      /*
773   ||  Created By : [email protected]
774   ||  Created On : 05-JUN-2003
775   ||  Purpose : Handles the UPDATE DML logic for the table when called from SS.
776   ||            The time component alone is passed here and not the complete date string.
777   ||  Known limitations, enhancements or remarks :
778   ||  Change History :
779   ||  Who             When            What
780   ||  (reverse chronological order - newest change first)
781   */
782 
783    l_start_tm_date DATE;
784    l_end_tm_date  DATE;
785   BEGIN
786 
787   l_start_tm_date := TO_DATE(x_start_tm,'HH24:MI');
788   l_end_tm_date := TO_DATE(x_end_tm,'HH24:MI');
789 
790   -- call the insert row passing the correct formatted date.
791   update_row
792   (
793    x_rowid                   =>   x_rowid,
794    x_office_hrs_id           =>   x_office_hrs_id,
795    x_contact_preference_id   =>   x_contact_preference_id,
796    x_day_of_week_code        =>   x_day_of_week_code,
797    x_start_tm_date           =>   l_start_tm_date,
798    x_end_tm_date             =>   l_end_tm_date,
799    x_mode                    =>   'R');
800 
801    END update_row_ss;
802 
803 
804 
805   PROCEDURE lock_row_ss (
806     x_rowid                                  IN     VARCHAR2,
807     x_office_hrs_id                          IN     NUMBER,
808     x_contact_preference_id                  IN     NUMBER,
809     x_day_of_week_code                       IN     VARCHAR2,
810     x_start_tm                               IN     VARCHAR2,
811     x_end_tm                                 IN     VARCHAR2
812   ) AS
813   /*
814   ||  Created By : [email protected]
815   ||  Created On : 05-JUN-2003
816   ||  Purpose : Handles the LOCK mechanism for the table.
817   ||  Known limitations, enhancements or remarks :
818   ||  Change History :
819   ||  Who             When            What
820   ||  (reverse chronological order - newest change first)
821   */
822     CURSOR c1 IS
823       SELECT
824         office_hrs_id,
825         contact_preference_id,
826         day_of_week_code,
827         to_char(start_tm_date,'HH24:MI') start_tm_date,
828         to_char(end_tm_date,'HH24:MI') end_tm_date
829       FROM  igs_pe_office_hrs
830       WHERE rowid = x_rowid
831       FOR UPDATE NOWAIT;
832 
833     tlinfo c1%ROWTYPE;
834 
835   BEGIN
836 
837     OPEN c1;
838     FETCH c1 INTO tlinfo;
839     IF (c1%notfound) THEN
840       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
841       igs_ge_msg_stack.add;
842       CLOSE c1;
843       app_exception.raise_exception;
844       RETURN;
845     END IF;
846     CLOSE c1;
847 
848     IF (
849         (tlinfo.office_hrs_id = x_office_hrs_id)
850         AND (tlinfo.contact_preference_id = x_contact_preference_id)
851         AND (tlinfo.day_of_week_code = x_day_of_week_code)
852         --AND (tlinfo.start_tm_date = x_start_tm_date)
853         --AND (tlinfo.end_tm_date = x_end_tm_date)
854        ) THEN
855       NULL;
856     ELSE
857       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
858       igs_ge_msg_stack.add;
859       app_exception.raise_exception;
860     END IF;
861 
862     RETURN;
863 
864   END lock_row_ss;
865 
866 END igs_pe_office_hrs_pkg;