[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_LOCATION_PKG
Source
1 package body IGS_AD_LOCATION_PKG as
2 /* $Header: IGSAI46B.pls 115.21 2003/12/03 08:39:53 ijeddy ship $ */
3 --msrinivi 24-AUG-2001 Bug No. 1956374 .Repointed genp_val_prsn_id
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_LOCATION_ALL%RowType;
6 new_references IGS_AD_LOCATION_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_org_id IN NUMBER DEFAULT NULL,
12 x_location_cd IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_location_type IN VARCHAR2 DEFAULT NULL,
15 x_closed_ind IN VARCHAR2 DEFAULT NULL,
16 x_coord_person_id IN igs_pe_person.person_id%type DEFAULT NULL,
17 x_mail_dlvry_wrk_days IN NUMBER DEFAULT NULL,
18 x_attribute_category IN VARCHAR2 DEFAULT NULL,
19 x_attribute1 IN VARCHAR2 DEFAULT NULL,
20 x_attribute2 IN VARCHAR2 DEFAULT NULL,
21 x_attribute3 IN VARCHAR2 DEFAULT NULL,
22 x_attribute4 IN VARCHAR2 DEFAULT NULL,
23 x_attribute5 IN VARCHAR2 DEFAULT NULL,
24 x_attribute6 IN VARCHAR2 DEFAULT NULL,
25 x_attribute7 IN VARCHAR2 DEFAULT NULL,
26 x_attribute8 IN VARCHAR2 DEFAULT NULL,
27 x_attribute9 IN VARCHAR2 DEFAULT NULL,
28 x_attribute10 IN VARCHAR2 DEFAULT NULL,
29 x_attribute11 IN VARCHAR2 DEFAULT NULL,
30 x_attribute12 IN VARCHAR2 DEFAULT NULL,
31 x_attribute13 IN VARCHAR2 DEFAULT NULL,
32 x_attribute14 IN VARCHAR2 DEFAULT NULL,
33 x_attribute15 IN VARCHAR2 DEFAULT NULL,
34 x_attribute16 IN VARCHAR2 DEFAULT NULL,
35 x_attribute17 IN VARCHAR2 DEFAULT NULL,
36 x_attribute18 IN VARCHAR2 DEFAULT NULL,
37 x_attribute19 IN VARCHAR2 DEFAULT NULL,
38 x_attribute20 IN VARCHAR2 DEFAULT NULL,
39 x_creation_date IN DATE DEFAULT NULL,
40 x_created_by IN NUMBER DEFAULT NULL,
41 x_last_update_date IN DATE DEFAULT NULL,
42 x_last_updated_by IN NUMBER DEFAULT NULL,
43 x_last_update_login IN NUMBER DEFAULT NULL,
44 x_rev_account_cd IN VARCHAR2 DEFAULT NULL
45 ) AS
46 /*************************************************************
47 Created By :
48 Date Created By :
49 Purpose :
50 Know limitations, enhancements or remarks
51 Change History
52 Who When What
53 (reverse chronological order - newest change first)
54 msrinivi 17 Jul, 2001 Added new col : rev account cd
55 ***************************************************************/
56 CURSOR cur_old_ref_values IS
57 SELECT *
58 FROM IGS_AD_LOCATION_ALL
59 WHERE rowid = x_rowid;
60
61 BEGIN
62
63 l_rowid := x_rowid;
64
65 -- Code for setting the Old and New Reference Values.
66 -- Populate Old Values.
67 Open cur_old_ref_values;
68 Fetch cur_old_ref_values INTO old_references;
69 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
70 Close cur_old_ref_values;
71 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
72 IGS_GE_MSG_STACK.ADD;
73 App_Exception.Raise_Exception;
74 Return;
75 END IF;
76 Close cur_old_ref_values;
77
78 -- Populate New Values.
79 new_references.org_id := x_org_id;
80 new_references.location_cd := x_location_cd;
81 new_references.description := x_description;
82 new_references.location_type := x_location_type;
83 new_references.closed_ind := x_closed_ind;
84 new_references.coord_person_id := x_coord_person_id;
85 new_references.mail_dlvry_wrk_days := x_mail_dlvry_wrk_days;
86 new_references.attribute_category := x_attribute_category;
87 new_references.attribute1 := x_attribute1;
88 new_references.attribute2 := x_attribute2;
89 new_references.attribute3 := x_attribute3;
90 new_references.attribute4 := x_attribute4;
91 new_references.attribute5 := x_attribute5;
92 new_references.attribute6 := x_attribute6;
93 new_references.attribute7 := x_attribute7;
94 new_references.attribute8 := x_attribute8;
95 new_references.attribute9 := x_attribute9;
96 new_references.attribute10 := x_attribute10;
97 new_references.attribute11 := x_attribute11;
98 new_references.attribute12 := x_attribute12;
99 new_references.attribute13 := x_attribute13;
100 new_references.attribute14 := x_attribute14;
101 new_references.attribute15 := x_attribute15;
102 new_references.attribute16 := x_attribute16;
103 new_references.attribute17 := x_attribute17;
104 new_references.attribute18 := x_attribute18;
105 new_references.attribute19 := x_attribute19;
106 new_references.attribute20 := x_attribute20;
107 new_references.rev_account_cd := x_rev_account_cd;
108
109 IF (p_action = 'UPDATE') THEN
110 new_references.creation_date := old_references.creation_date;
111 new_references.created_by := old_references.created_by;
112 ELSE
113 new_references.creation_date := x_creation_date;
114 new_references.created_by := x_created_by;
115 END IF;
116 new_references.last_update_date := x_last_update_date;
117 new_references.last_updated_by := x_last_updated_by;
118 new_references.last_update_login := x_last_update_login;
119
120 END Set_Column_Values;
121
122 PROCEDURE BeforeRowInsertUpdate1(
123 p_inserting IN BOOLEAN DEFAULT FALSE,
124 p_updating IN BOOLEAN DEFAULT FALSE,
125 p_deleting IN BOOLEAN DEFAULT FALSE
126 ) AS
127 /*************************************************************
128 Created By :
129 Date Created By :
130 Purpose :
131 Know limitations, enhancements or remarks
132 Change History
133 Who When What
134 (reverse chronological order - newest change first)
135 msrinivi 17 Jul, 2001 Added new col : rev account cd
136 ***************************************************************/
137
138 v_message_name varchar2(30);
139 BEGIN
140 IF p_inserting OR
141 (old_references.location_type <> new_references.location_type) THEN
142 IF IGS_OR_VAL_LOC.orgp_val_loc_type (
143 new_references.location_type,
144 v_message_name) = FALSE THEN
145 Fnd_Message.Set_Name('IGS',v_message_name);
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149 END IF;
150 IF p_inserting OR p_updating THEN
151 IF new_references.location_cd <> old_references.location_cd THEN
152 IF IGS_OR_VAL_LOC.assp_val_loc_ve_xist (
153 new_references.location_cd,
154 new_references.location_type,
155 v_message_name) = FALSE THEN
156 Fnd_Message.Set_Name('IGS',v_message_name);
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161 IF IGS_OR_VAL_LOC.assp_val_loc_ve_open (
162 new_references.location_cd,
163 new_references.location_type,
164 new_references.closed_ind,
165 v_message_name) = FALSE THEN
166 Fnd_Message.Set_Name('IGS',v_message_name);
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception;
169 END IF;
170 IF IGS_OR_VAL_LOC.assp_val_loc_coord (
171 new_references.location_type,
172 new_references.coord_person_id,
173 v_message_name) = FALSE THEN
174 Fnd_Message.Set_Name('IGS',v_message_name);
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END IF;
178 IF (new_references.coord_person_id IS NOT NULL and
179 new_references.coord_person_id <> old_references.coord_person_id) THEN
180 IF IGS_CO_VAL_OC.genp_val_prsn_id (
181 new_references.coord_person_id,
182 v_message_name) = FALSE THEN
183 Fnd_Message.Set_Name('IGS',v_message_name);
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186 END IF;
187 END IF;
188 END IF;
189
190 END BeforeRowInsertUpdate1;
191
192 PROCEDURE Check_Constraints (
193 Column_Name IN VARCHAR2 DEFAULT NULL,
194 Column_Value IN VARCHAR2 DEFAULT NULL
195 )
196 AS
197 /*************************************************************
198 Created By :
199 Date Created By :
200 Purpose :
201 Know limitations, enhancements or remarks
202 Change History
203 Who When What
204 (reverse chronological order - newest change first)
205 ***************************************************************/
206 BEGIN
207 IF Column_Name is null then
208 NULL;
209 ELSIF upper(Column_Name) = 'MAIL_DLVRY_WRK_DAYS' then
210 new_references.mail_dlvry_wrk_days := igs_ge_number.to_num(column_value);
211 ELSIF upper(Column_Name) = 'CLOSED_IND' then
212 new_references.closed_ind := column_value;
213 ELSIF upper(Column_Name) = 'LOCATION_CD' then
214 new_references.location_cd := column_value;
215 ELSIF upper(Column_Name) = 'LOCATION_TYPE' then
216 new_references.location_type := column_value;
217 END IF;
218
219 IF upper(Column_Name) = 'MAIL_DLVRY_WRK_DAYS' OR Column_Name IS NULL THEN
220 IF new_references.mail_dlvry_wrk_days < 0 OR new_references.mail_dlvry_wrk_days > 99 THEN
221 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226 IF upper(Column_Name) = 'CLOSED_IND' OR Column_Name IS NULL THEN
227 IF new_references.closed_ind NOT IN ('Y','N') THEN
228 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 END IF;
232 END IF;
233
234 IF upper(Column_Name) = 'LOCATION_CD' OR Column_Name IS NULL THEN
235 IF new_references.location_cd <> UPPER(new_references.location_cd) THEN
236 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
237 IGS_GE_MSG_STACK.ADD;
238 App_Exception.Raise_Exception;
239 END IF;
240 END IF;
241 IF upper(Column_Name) = 'LOCATION_TYPE' OR Column_Name IS NULL THEN
242 IF new_references.location_type <> UPPER(new_references.location_type) THEN
243 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248
249 END Check_Constraints;
250
251 PROCEDURE Check_Parent_Existance AS
252 /*************************************************************
253 Created By :
254 Date Created By :
255 Purpose :
256 Know limitations, enhancements or remarks
257 Change History
258 Who When What
259 (reverse chronological order - newest change first)
260 ***************************************************************/
261
262 BEGIN
263
264 IF (((old_references.location_type = new_references.location_type)) OR
265 ((new_references.location_type IS NULL))) THEN
266 NULL;
267 ELSE
268 IF NOT IGS_AD_LOCATION_TYPE_PKG.Get_PK_For_Validation (
269 new_references.location_type ,
270 'N' ) THEN
271 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275 END IF;
276
277 IF (((old_references.coord_person_id = new_references.coord_person_id)) OR
278 ((new_references.coord_person_id IS NULL))) THEN
279 NULL;
280 ELSE
281 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
282 new_references.coord_person_id
283 ) THEN
284 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
285 IGS_GE_MSG_STACK.ADD;
286 App_Exception.Raise_Exception;
287 END IF;
288 END IF;
289
290 IF ((old_references.rev_account_cd = new_references.rev_account_cd) OR
291 (new_references.rev_account_cd IS NULL)) THEN
292 NULL;
293 ELSE
294 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
295 new_references.rev_account_cd
296 ) THEN
297 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
298 IGS_GE_MSG_STACK.ADD;
299 App_Exception.Raise_Exception;
300 END IF;
301 END IF;
302
303 END Check_Parent_Existance;
304
305 PROCEDURE Check_Child_Existance AS
306 /*************************************************************
307 Created By :
308 Date Created By :
309 Purpose :
310 Know limitations, enhancements or remarks
311 Change History
312 Who When What
313 (reverse chronological order - newest change first)
314 ***************************************************************/
315
316 BEGIN
317
318 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_AD_LOCATION (
319 old_references.location_cd
320 );
321
322 IGS_AD_PS_APLINSTUNT_PKG.GET_FK_IGS_AD_LOCATION (
323 old_references.location_cd
324 );
325
326 IGS_AD_PRD_PS_OF_OPT_PKG.GET_FK_IGS_AD_LOCATION (
327 old_references.location_cd
328 );
329
330 IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_AD_LOCATION (
331 old_references.location_cd
332 );
333
334 IGS_AS_ITEM_ASSESSOR_PKG.GET_FK_IGS_AD_LOCATION (
335 old_references.location_cd
336 );
337
338 IGS_FI_FEE_AS_RT_PKG.GET_FK_IGS_AD_LOCATION (
339 old_references.location_cd
340 );
341
342 IGS_PS_FEE_TRG_PKG.GET_FK_IGS_AD_LOCATION (
343 old_references.location_cd
344 );
345
346 IGS_PS_OFR_OPT_PKG.GET_FK_IGS_AD_LOCATION (
347 old_references.location_cd
348 );
349
350 IGS_AS_EXM_LOC_SPVSR_PKG.GET_FK_IGS_AD_LOCATION (
351 old_references.location_cd
352 );
353
354 IGS_FI_FEE_AS_RATE_PKG.GET_FK_IGS_AD_LOCATION (
355 old_references.location_cd
356 );
357
358
359 IGS_AD_LOCATION_REL_PKG.GET_FK_IGS_AD_LOCATION (
360 old_references.location_cd
361 );
362
363 IGS_OR_UNIT_LOC_PKG.GET_FK_IGS_AD_LOCATION (
364 old_references.location_cd
365 );
366
367 IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_AD_LOCATION (
368 old_references.location_cd
369 );
370
371 IGS_PS_PAT_STUDY_UNT_PKG.GET_FK_IGS_AD_LOCATION (
372 old_references.location_cd
373 );
374
375 IGS_AD_SBM_PS_FNTRGT_PKG.GET_FK_IGS_AD_LOCATION (
376 old_references.location_cd
377 );
378
379 IGS_EN_STDNT_PS_ATT_PKG.GET_FK_IGS_AD_LOCATION (
380 old_references.location_cd
381 );
382
383 IGS_EN_SU_ATTEMPT_PKG.GET_FK_IGS_AD_LOCATION (
384 old_references.location_cd
385 );
386
387 IGS_AS_SASSESS_TYPE_PKG.GET_FK_IGS_AD_LOCATION (
388 old_references.location_cd
389 );
390
391
392 IGS_FI_UNIT_FEE_TRG_PKG.GET_FK_IGS_AD_LOCATION (
393 old_references.location_cd
394 );
395
396 IGS_PS_UNIT_OFR_OPT_PKG.GET_FK_IGS_AD_LOCATION (
397 old_references.location_cd
398 );
399
400 IGS_GR_VENUE_PKG.GET_FK_IGS_AD_LOCATION (
401 old_references.location_cd
402 );
403
404 IGS_PS_UNIT_LOCATION_PKG.GET_FK_IGS_AD_LOCATION (
405 old_references.location_cd
406 );
407
408 IGS_AD_BUILDING_PKG.GET_FK_IGS_AD_LOCATION (
409 old_references.location_cd
410 );
411
412 Igs_Or_Org_Notes_Pkg.GET_FK_IGS_AD_LOCATION (
413 old_references.location_cd
414 );
415
416 igs_ps_us_unsched_cl_pkg.GET_FK_IGS_AD_LOCATION (
417 old_references.location_cd
418 );
419 igs_ad_loc_accts_pkg.GET_FK_IGS_AD_LOCATION (
420 old_references.location_cd
421 );
422
423 igs_ad_panel_dtls_pkg.get_fk_igs_ad_location(
424 old_references.location_cd
425 );
426
427 END Check_Child_Existance;
428
429 FUNCTION Get_PK_For_Validation (
430 x_location_cd IN VARCHAR2,
431 x_closed_ind IN VARCHAR2
432 )return BOOLEAN AS
433 /*************************************************************
434 Created By :
435 Date Created By :
436 Purpose :
437 Know limitations, enhancements or remarks
438 Change History
439 Who When What
440 (reverse chronological order - newest change first)
441 -- ssawhney for locking remvoed FOR UPDATE. delete is not alloed now
442 ***************************************************************/
443
444 CURSOR cur_rowid IS
445 SELECT rowid
446 FROM IGS_AD_LOCATION_ALL
447 WHERE location_cd = x_location_cd AND
448 closed_ind = NVL(x_closed_ind,closed_ind);
449
450 lv_rowid cur_rowid%RowType;
451
452 BEGIN
453
454 Open cur_rowid;
455 Fetch cur_rowid INTO lv_rowid;
456 IF (cur_rowid%FOUND) THEN
457 Close cur_rowid;
458 Return(TRUE);
459 ELSE
460 Close cur_rowid;
461 Return(FALSE);
462 END IF;
463
464 END Get_PK_For_Validation;
465
466 PROCEDURE GET_FK_IGS_AD_LOCATION_TYPE (
467 x_location_type IN VARCHAR2
468 ) AS
469 /*************************************************************
470 Created By :
471 Date Created By :
472 Purpose :
473 Know limitations, enhancements or remarks
474 Change History
475 Who When What
476 (reverse chronological order - newest change first)
477 ***************************************************************/
478 CURSOR cur_rowid IS
479 SELECT rowid
480 FROM IGS_AD_LOCATION_ALL
481 WHERE location_type = x_location_type ;
482
483 lv_rowid cur_rowid%RowType;
484
485 BEGIN
486
487 Open cur_rowid;
488 Fetch cur_rowid INTO lv_rowid;
489 IF (cur_rowid%FOUND) THEN
490 Close cur_rowid;
491 Fnd_Message.Set_Name ('IGS', 'IGS_AD_LOC_LOT_FK');
492 IGS_GE_MSG_STACK.ADD;
493 App_Exception.Raise_Exception;
494 Return;
495 END IF;
496 Close cur_rowid;
497
498 END GET_FK_IGS_AD_LOCATION_TYPE;
499
500 PROCEDURE GET_FK_IGS_PE_PERSON (
501 x_person_id IN igs_pe_person.person_id%type
502 ) AS
503 /*************************************************************
504 Created By :
505 Date Created By :
506 Purpose :
507 Know limitations, enhancements or remarks
508 Change History
509 Who When What
510 (reverse chronological order - newest change first)
511 ***************************************************************/
512 CURSOR cur_rowid IS
513 SELECT rowid
514 FROM IGS_AD_LOCATION_ALL
515 WHERE coord_person_id = x_person_id ;
516
517 lv_rowid cur_rowid%RowType;
518
519 BEGIN
520
521 Open cur_rowid;
522 Fetch cur_rowid INTO lv_rowid;
523 IF (cur_rowid%FOUND) THEN
524 Close cur_rowid;
525 Fnd_Message.Set_Name ('IGS', 'IGS_AD_LOC_PE_FK');
526 IGS_GE_MSG_STACK.ADD;
527 App_Exception.Raise_Exception;
528 Return;
529 END IF;
530 Close cur_rowid;
531
532 END GET_FK_IGS_PE_PERSON;
533
534 PROCEDURE Before_DML (
535 p_action IN VARCHAR2,
536 x_rowid IN VARCHAR2 DEFAULT NULL,
537 x_org_id IN NUMBER DEFAULT NULL,
538 x_location_cd IN VARCHAR2 DEFAULT NULL,
539 x_description IN VARCHAR2 DEFAULT NULL,
540 x_location_type IN VARCHAR2 DEFAULT NULL,
541 x_closed_ind IN VARCHAR2 DEFAULT NULL,
542 x_coord_person_id IN igs_pe_person.person_id%type DEFAULT NULL,
543 x_mail_dlvry_wrk_days IN NUMBER DEFAULT NULL,
544 x_attribute_category IN VARCHAR2 DEFAULT NULL,
545 x_attribute1 IN VARCHAR2 DEFAULT NULL,
546 x_attribute2 IN VARCHAR2 DEFAULT NULL,
547 x_attribute3 IN VARCHAR2 DEFAULT NULL,
548 x_attribute4 IN VARCHAR2 DEFAULT NULL,
549 x_attribute5 IN VARCHAR2 DEFAULT NULL,
550 x_attribute6 IN VARCHAR2 DEFAULT NULL,
551 x_attribute7 IN VARCHAR2 DEFAULT NULL,
552 x_attribute8 IN VARCHAR2 DEFAULT NULL,
553 x_attribute9 IN VARCHAR2 DEFAULT NULL,
554 x_attribute10 IN VARCHAR2 DEFAULT NULL,
555 x_attribute11 IN VARCHAR2 DEFAULT NULL,
556 x_attribute12 IN VARCHAR2 DEFAULT NULL,
557 x_attribute13 IN VARCHAR2 DEFAULT NULL,
558 x_attribute14 IN VARCHAR2 DEFAULT NULL,
559 x_attribute15 IN VARCHAR2 DEFAULT NULL,
560 x_attribute16 IN VARCHAR2 DEFAULT NULL,
561 x_attribute17 IN VARCHAR2 DEFAULT NULL,
562 x_attribute18 IN VARCHAR2 DEFAULT NULL,
563 x_attribute19 IN VARCHAR2 DEFAULT NULL,
564 x_attribute20 IN VARCHAR2 DEFAULT NULL,
565 x_creation_date IN DATE DEFAULT NULL,
566 x_created_by IN NUMBER DEFAULT NULL,
567 x_last_update_date IN DATE DEFAULT NULL,
568 x_last_updated_by IN NUMBER DEFAULT NULL,
569 x_last_update_login IN NUMBER DEFAULT NULL,
570 x_rev_account_cd IN VARCHAR2 DEFAULT NULL
571 ) AS
572 /*************************************************************
573 Created By :
574 Date Created By :
575 Purpose :
576 Know limitations, enhancements or remarks
577 Change History
578 Who When What
579 msrinivi 18 Jul,2001 Added new col :rev_account_cd
580 (reverse chronological order - newest change first)
581 ***************************************************************/
582
583 BEGIN
584
585 Set_Column_Values (
586 p_action,
587 x_rowid,
588 x_org_id,
589 x_location_cd,
590 x_description,
591 x_location_type,
592 x_closed_ind,
593 x_coord_person_id,
594 x_mail_dlvry_wrk_days,
595 x_attribute_category,
596 x_attribute1,
597 x_attribute2,
598 x_attribute3,
599 x_attribute4,
600 x_attribute5,
601 x_attribute6,
602 x_attribute7,
603 x_attribute8,
604 x_attribute9,
605 x_attribute10,
606 x_attribute11,
607 x_attribute12,
608 x_attribute13,
609 x_attribute14,
610 x_attribute15,
611 x_attribute16,
612 x_attribute17,
613 x_attribute18,
614 x_attribute19,
615 x_attribute20,
616 x_creation_date,
617 x_created_by,
618 x_last_update_date,
619 x_last_updated_by,
620 x_last_update_login ,
621 x_rev_account_cd
622 );
623
624 IF (p_action = 'INSERT') THEN
625 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
626 IF Get_PK_For_Validation (
627 new_references.location_cd
628 ) THEN
629 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
630 IGS_GE_MSG_STACK.ADD;
631 App_Exception.Raise_Exception;
632 END IF;
633 Check_Constraints;
634 Check_Parent_Existance;
635 ELSIF (p_action = 'UPDATE') THEN
636 BeforeRowInsertUpdate1 ( p_updating => TRUE );
637 Check_Constraints;
638 Check_Parent_Existance;
639 ELSIF (p_action = 'DELETE') THEN
640 Check_Child_Existance;
641 ELSIF (p_action = 'VALIDATE_INSERT') THEN
642 IF Get_PK_For_Validation (
643 new_references.location_cd
644 ) THEN
645 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
646 IGS_GE_MSG_STACK.ADD;
647 App_Exception.Raise_Exception;
648 END IF;
649 Check_Constraints;
650 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
651 Check_Constraints;
652 ELSIF (p_action = 'VALIDATE_DELETE') THEN
653 Check_Child_Existance;
654 END IF;
655
656 END Before_DML;
657
658 PROCEDURE After_DML (
659 p_action IN VARCHAR2,
660 x_rowid IN VARCHAR2
661 ) AS
662 /*************************************************************
663 Created By :
664 Date Created By :
665 Purpose :
666 Know limitations, enhancements or remarks
667 Change History
668 Who When What
669 msrinivi 18 Jul,2001 Added new col :rev_account_cd
670 (reverse chronological order - newest change first)
671 ***************************************************************/
672
673 BEGIN
674
675 l_rowid := x_rowid;
676
677 END After_DML;
678
679 procedure INSERT_ROW (
680 X_ROWID in out NOCOPY VARCHAR2,
681 X_ORG_ID in NUMBER,
682 X_LOCATION_CD in VARCHAR2,
683 X_DESCRIPTION in VARCHAR2,
684 X_LOCATION_TYPE in VARCHAR2,
685 X_MAIL_DLVRY_WRK_DAYS in NUMBER,
686 X_COORD_PERSON_ID igs_pe_person.person_id%type,
687 x_attribute_category IN VARCHAR2 DEFAULT NULL,
688 x_attribute1 IN VARCHAR2 DEFAULT NULL,
689 x_attribute2 IN VARCHAR2 DEFAULT NULL,
690 x_attribute3 IN VARCHAR2 DEFAULT NULL,
691 x_attribute4 IN VARCHAR2 DEFAULT NULL,
692 x_attribute5 IN VARCHAR2 DEFAULT NULL,
693 x_attribute6 IN VARCHAR2 DEFAULT NULL,
694 x_attribute7 IN VARCHAR2 DEFAULT NULL,
695 x_attribute8 IN VARCHAR2 DEFAULT NULL,
696 x_attribute9 IN VARCHAR2 DEFAULT NULL,
697 x_attribute10 IN VARCHAR2 DEFAULT NULL,
698 x_attribute11 IN VARCHAR2 DEFAULT NULL,
699 x_attribute12 IN VARCHAR2 DEFAULT NULL,
700 x_attribute13 IN VARCHAR2 DEFAULT NULL,
701 x_attribute14 IN VARCHAR2 DEFAULT NULL,
702 x_attribute15 IN VARCHAR2 DEFAULT NULL,
703 x_attribute16 IN VARCHAR2 DEFAULT NULL,
704 x_attribute17 IN VARCHAR2 DEFAULT NULL,
705 x_attribute18 IN VARCHAR2 DEFAULT NULL,
706 x_attribute19 IN VARCHAR2 DEFAULT NULL,
707 x_attribute20 IN VARCHAR2 DEFAULT NULL,
708 X_CLOSED_IND in VARCHAR2,
709 X_MODE in VARCHAR2 default 'R',
710 x_rev_account_cd IN VARCHAR2 DEFAULT NULL
711 ) AS
712 /*************************************************************
713 Created By :
714 Date Created By :
715 Purpose :
716 Know limitations, enhancements or remarks
717 Change History
718 Who When What
719 msrinivi 18 Jul,2001 Added new col :rev_account_cd
720 (reverse chronological order - newest change first)
721 ***************************************************************/
722
723 cursor C is select ROWID from IGS_AD_LOCATION_ALL
724 where LOCATION_CD = X_LOCATION_CD;
725 X_LAST_UPDATE_DATE DATE;
726 X_LAST_UPDATED_BY NUMBER;
727 X_LAST_UPDATE_LOGIN NUMBER;
728 begin
729 X_LAST_UPDATE_DATE := SYSDATE;
730 if(X_MODE = 'I') then
731 X_LAST_UPDATED_BY := 1;
732 X_LAST_UPDATE_LOGIN := 0;
733 elsif (X_MODE = 'R') then
734 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
735 if X_LAST_UPDATED_BY is NULL then
736 X_LAST_UPDATED_BY := -1;
737 end if;
738 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
739 if X_LAST_UPDATE_LOGIN is NULL then
740 X_LAST_UPDATE_LOGIN := -1;
741 end if;
742 else
743 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
744 IGS_GE_MSG_STACK.ADD;
745 app_exception.raise_exception;
746 end if;
747
748 Before_DML (
749 p_action => 'INSERT' ,
750 x_rowid => X_ROWID,
751 x_org_id => igs_ge_gen_003.get_org_id,
752 x_location_cd => X_LOCATION_CD,
753 x_description => X_DESCRIPTION,
754 x_location_type => X_LOCATION_TYPE,
755 x_closed_ind => NVL(X_CLOSED_IND,'N'),
756 x_coord_person_id => X_COORD_PERSON_ID,
757 x_mail_dlvry_wrk_days => X_MAIL_DLVRY_WRK_DAYS,
758 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
759 x_attribute1=>X_ATTRIBUTE1,
760 x_attribute2=>X_ATTRIBUTE2,
761 x_attribute3=>X_ATTRIBUTE3,
762 x_attribute4=>X_ATTRIBUTE4,
763 x_attribute5=>X_ATTRIBUTE5,
764 x_attribute6=>X_ATTRIBUTE6,
765 x_attribute7=>X_ATTRIBUTE7,
766 x_attribute8=>X_ATTRIBUTE8,
767 x_attribute9=>X_ATTRIBUTE9,
768 x_attribute10=>X_ATTRIBUTE10,
769 x_attribute11=>X_ATTRIBUTE11,
770 x_attribute12=>X_ATTRIBUTE12,
771 x_attribute13=>X_ATTRIBUTE13,
772 x_attribute14=>X_ATTRIBUTE14,
773 x_attribute15=>X_ATTRIBUTE15,
774 x_attribute16=>X_ATTRIBUTE16,
775 x_attribute17=>X_ATTRIBUTE17,
776 x_attribute18=>X_ATTRIBUTE18,
777 x_attribute19=>X_ATTRIBUTE19,
778 x_attribute20=>X_ATTRIBUTE20,
779 x_creation_date => X_LAST_UPDATE_DATE,
780 x_created_by => X_LAST_UPDATED_BY,
781 x_last_update_date => X_LAST_UPDATE_DATE,
782 x_last_updated_by => X_LAST_UPDATED_BY,
783 x_last_update_login => X_LAST_UPDATE_LOGIN,
784 x_rev_account_cd => x_rev_account_cd
785 );
786
787 insert into IGS_AD_LOCATION_ALL (
788 LOCATION_CD,
789 DESCRIPTION,
790 LOCATION_TYPE,
791 MAIL_DLVRY_WRK_DAYS,
792 COORD_PERSON_ID,
793 CLOSED_IND
794 ,ATTRIBUTE_CATEGORY
795 ,ATTRIBUTE1
796 ,ATTRIBUTE2
797 ,ATTRIBUTE3
798 ,ATTRIBUTE4
799 ,ATTRIBUTE5
800 ,ATTRIBUTE6
801 ,ATTRIBUTE7
802 ,ATTRIBUTE8
803 ,ATTRIBUTE9
804 ,ATTRIBUTE10
805 ,ATTRIBUTE11
806 ,ATTRIBUTE12
807 ,ATTRIBUTE13
808 ,ATTRIBUTE14
809 ,ATTRIBUTE15
810 ,ATTRIBUTE16
811 ,ATTRIBUTE17
812 ,ATTRIBUTE18
813 ,ATTRIBUTE19
814 ,ATTRIBUTE20,
815 CREATION_DATE,
816 CREATED_BY,
817 LAST_UPDATE_DATE,
818 LAST_UPDATED_BY,
819 LAST_UPDATE_LOGIN,
820 ORG_ID,
821 rev_account_cd
822 ) values (
823 NEW_REFERENCES.LOCATION_CD,
824 NEW_REFERENCES.DESCRIPTION,
825 NEW_REFERENCES.LOCATION_TYPE,
826 NEW_REFERENCES.MAIL_DLVRY_WRK_DAYS,
827 NEW_REFERENCES.COORD_PERSON_ID,
828 NEW_REFERENCES.CLOSED_IND
829 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
830 ,NEW_REFERENCES.ATTRIBUTE1
831 ,NEW_REFERENCES.ATTRIBUTE2
832 ,NEW_REFERENCES.ATTRIBUTE3
833 ,NEW_REFERENCES.ATTRIBUTE4
834 ,NEW_REFERENCES.ATTRIBUTE5
835 ,NEW_REFERENCES.ATTRIBUTE6
836 ,NEW_REFERENCES.ATTRIBUTE7
837 ,NEW_REFERENCES.ATTRIBUTE8
838 ,NEW_REFERENCES.ATTRIBUTE9
839 ,NEW_REFERENCES.ATTRIBUTE10
840 ,NEW_REFERENCES.ATTRIBUTE11
841 ,NEW_REFERENCES.ATTRIBUTE12
842 ,NEW_REFERENCES.ATTRIBUTE13
843 ,NEW_REFERENCES.ATTRIBUTE14
844 ,NEW_REFERENCES.ATTRIBUTE15
845 ,NEW_REFERENCES.ATTRIBUTE16
846 ,NEW_REFERENCES.ATTRIBUTE17
847 ,NEW_REFERENCES.ATTRIBUTE18
848 ,NEW_REFERENCES.ATTRIBUTE19
849 ,NEW_REFERENCES.ATTRIBUTE20,
850 X_LAST_UPDATE_DATE,
851 X_LAST_UPDATED_BY,
852 X_LAST_UPDATE_DATE,
853 X_LAST_UPDATED_BY,
854 X_LAST_UPDATE_LOGIN,
855 NEW_REFERENCES.ORG_ID,
856 new_references.rev_account_cd
857 );
858
859 open c;
860 fetch c into X_ROWID;
861 if (c%notfound) then
862 close c;
863 raise no_data_found;
864 end if;
865 close c;
866 After_DML (
867 p_action => 'INSERT' ,
868 x_rowid => X_ROWID
869 );
870 end INSERT_ROW;
871
872 procedure LOCK_ROW (
873 X_ROWID in VARCHAR2,
874 X_LOCATION_CD in VARCHAR2,
875 X_DESCRIPTION in VARCHAR2,
876 X_LOCATION_TYPE in VARCHAR2,
877 X_MAIL_DLVRY_WRK_DAYS in NUMBER,
878 X_COORD_PERSON_ID in igs_pe_person.person_id%type,
879 x_attribute_category IN VARCHAR2 DEFAULT NULL,
880 x_attribute1 IN VARCHAR2 DEFAULT NULL,
881 x_attribute2 IN VARCHAR2 DEFAULT NULL,
882 x_attribute3 IN VARCHAR2 DEFAULT NULL,
883 x_attribute4 IN VARCHAR2 DEFAULT NULL,
884 x_attribute5 IN VARCHAR2 DEFAULT NULL,
885 x_attribute6 IN VARCHAR2 DEFAULT NULL,
886 x_attribute7 IN VARCHAR2 DEFAULT NULL,
887 x_attribute8 IN VARCHAR2 DEFAULT NULL,
888 x_attribute9 IN VARCHAR2 DEFAULT NULL,
889 x_attribute10 IN VARCHAR2 DEFAULT NULL,
890 x_attribute11 IN VARCHAR2 DEFAULT NULL,
891 x_attribute12 IN VARCHAR2 DEFAULT NULL,
892 x_attribute13 IN VARCHAR2 DEFAULT NULL,
893 x_attribute14 IN VARCHAR2 DEFAULT NULL,
894 x_attribute15 IN VARCHAR2 DEFAULT NULL,
895 x_attribute16 IN VARCHAR2 DEFAULT NULL,
896 x_attribute17 IN VARCHAR2 DEFAULT NULL,
897 x_attribute18 IN VARCHAR2 DEFAULT NULL,
898 x_attribute19 IN VARCHAR2 DEFAULT NULL,
899 x_attribute20 IN VARCHAR2 DEFAULT NULL,
900 X_CLOSED_IND in VARCHAR2,
901 x_rev_account_cd IN VARCHAR2 DEFAULT NULL
902 ) AS
903 /*************************************************************
904 Created By :
905 Date Created By :
906 Purpose :
907 Know limitations, enhancements or remarks
908 Change History
909 Who When What
910 msrinivi 18 Jul,2001 Added new col :rev_account_cd
911 (reverse chronological order - newest change first)
912 ***************************************************************/
913 cursor c1 is select
914 DESCRIPTION,
915 LOCATION_TYPE,
916 MAIL_DLVRY_WRK_DAYS,
917 COORD_PERSON_ID
918 , ATTRIBUTE_CATEGORY
919 , ATTRIBUTE1
920 , ATTRIBUTE2
921 , ATTRIBUTE3
922 , ATTRIBUTE4
923 , ATTRIBUTE5
924 , ATTRIBUTE6
925 , ATTRIBUTE7
926 , ATTRIBUTE8
927 , ATTRIBUTE9
928 , ATTRIBUTE10
929 , ATTRIBUTE11
930 , ATTRIBUTE12
931 , ATTRIBUTE13
932 , ATTRIBUTE14
933 , ATTRIBUTE15
934 , ATTRIBUTE16
935 , ATTRIBUTE17
936 , ATTRIBUTE18
937 , ATTRIBUTE19
938 , ATTRIBUTE20
939 , CLOSED_IND
940 , rev_account_cd
941 from IGS_AD_LOCATION_ALL
942 WHERE ROWID = X_ROWID for update nowait ;
943 tlinfo c1%rowtype;
944
945 begin
946 open c1;
947 fetch c1 into tlinfo;
948 if (c1%notfound) then
949 close c1;
950 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
951 IGS_GE_MSG_STACK.ADD;
952 app_exception.raise_exception;
953 return;
954 end if;
955 close c1;
956
957 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
958 AND (tlinfo.LOCATION_TYPE = X_LOCATION_TYPE)
959 AND ((tlinfo.MAIL_DLVRY_WRK_DAYS = X_MAIL_DLVRY_WRK_DAYS)
960 OR ((tlinfo.MAIL_DLVRY_WRK_DAYS is null)
961 AND (X_MAIL_DLVRY_WRK_DAYS is null)))
962 AND ((tlinfo.COORD_PERSON_ID = X_COORD_PERSON_ID)
963 OR ((tlinfo.COORD_PERSON_ID is null)
964 AND (X_COORD_PERSON_ID is null)))
965 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
966 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
967 AND (X_ATTRIBUTE_CATEGORY is null)))
968 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
969 OR ((tlinfo.ATTRIBUTE1 is null)
970 AND (X_ATTRIBUTE1 is null)))
971 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
972 OR ((tlinfo.ATTRIBUTE2 is null)
973 AND (X_ATTRIBUTE2 is null)))
974 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
975 OR ((tlinfo.ATTRIBUTE3 is null)
976 AND (X_ATTRIBUTE3 is null)))
977 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
978 OR ((tlinfo.ATTRIBUTE4 is null)
979 AND (X_ATTRIBUTE4 is null)))
980 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
981 OR ((tlinfo.ATTRIBUTE5 is null)
982 AND (X_ATTRIBUTE5 is null)))
983 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
984 OR ((tlinfo.ATTRIBUTE6 is null)
985 AND (X_ATTRIBUTE6 is null)))
986 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
987 OR ((tlinfo.ATTRIBUTE7 is null)
988 AND (X_ATTRIBUTE7 is null)))
989 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
990 OR ((tlinfo.ATTRIBUTE8 is null)
991 AND (X_ATTRIBUTE8 is null)))
992 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
993 OR ((tlinfo.ATTRIBUTE9 is null)
994 AND (X_ATTRIBUTE9 is null)))
995 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
996 OR ((tlinfo.ATTRIBUTE10 is null)
997 AND (X_ATTRIBUTE10 is null)))
998 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
999 OR ((tlinfo.ATTRIBUTE11 is null)
1000 AND (X_ATTRIBUTE11 is null)))
1001 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1002 OR ((tlinfo.ATTRIBUTE12 is null)
1003 AND (X_ATTRIBUTE12 is null)))
1004 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1005 OR ((tlinfo.ATTRIBUTE13 is null)
1006 AND (X_ATTRIBUTE13 is null)))
1007 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1008 OR ((tlinfo.ATTRIBUTE14 is null)
1009 AND (X_ATTRIBUTE14 is null)))
1010 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1011 OR ((tlinfo.ATTRIBUTE15 is null)
1012 AND (X_ATTRIBUTE15 is null)))
1013 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1014 OR ((tlinfo.ATTRIBUTE16 is null)
1015 AND (X_ATTRIBUTE16 is null)))
1016 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1017 OR ((tlinfo.ATTRIBUTE17 is null)
1018 AND (X_ATTRIBUTE17 is null)))
1019 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1020 OR ((tlinfo.ATTRIBUTE18 is null)
1021 AND (X_ATTRIBUTE18 is null)))
1022 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1023 OR ((tlinfo.ATTRIBUTE19 is null)
1024 AND (X_ATTRIBUTE19 is null)))
1025 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1026 OR ((tlinfo.ATTRIBUTE20 is null)
1027 AND (X_ATTRIBUTE20 is null)))
1028 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
1029 AND ((tlinfo.rev_account_cd = X_rev_account_cd)
1030 OR ((tlinfo.rev_account_cd is null)
1031 AND (X_rev_account_cd is null)))
1032 ) THEN
1033 NULL;
1034 ELSE
1035 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1036 IGS_GE_MSG_STACK.ADD;
1037 app_exception.raise_exception;
1038 END IF;
1039 RETURN;
1040 END LOCK_ROW;
1041
1042 PROCEDURE UPDATE_ROW (
1043 X_ROWID in VARCHAR2,
1044 X_LOCATION_CD in VARCHAR2,
1045 X_DESCRIPTION in VARCHAR2,
1046 X_LOCATION_TYPE in VARCHAR2,
1047 X_MAIL_DLVRY_WRK_DAYS in NUMBER,
1048 X_COORD_PERSON_ID in igs_pe_person.person_id%type,
1049 X_CLOSED_IND in VARCHAR2,
1050 x_attribute_category IN VARCHAR2 DEFAULT NULL,
1051 x_attribute1 IN VARCHAR2 DEFAULT NULL,
1052 x_attribute2 IN VARCHAR2 DEFAULT NULL,
1053 x_attribute3 IN VARCHAR2 DEFAULT NULL,
1054 x_attribute4 IN VARCHAR2 DEFAULT NULL,
1055 x_attribute5 IN VARCHAR2 DEFAULT NULL,
1056 x_attribute6 IN VARCHAR2 DEFAULT NULL,
1057 x_attribute7 IN VARCHAR2 DEFAULT NULL,
1058 x_attribute8 IN VARCHAR2 DEFAULT NULL,
1059 x_attribute9 IN VARCHAR2 DEFAULT NULL,
1060 x_attribute10 IN VARCHAR2 DEFAULT NULL,
1061 x_attribute11 IN VARCHAR2 DEFAULT NULL,
1062 x_attribute12 IN VARCHAR2 DEFAULT NULL,
1063 x_attribute13 IN VARCHAR2 DEFAULT NULL,
1064 x_attribute14 IN VARCHAR2 DEFAULT NULL,
1065 x_attribute15 IN VARCHAR2 DEFAULT NULL,
1066 x_attribute16 IN VARCHAR2 DEFAULT NULL,
1067 x_attribute17 IN VARCHAR2 DEFAULT NULL,
1068 x_attribute18 IN VARCHAR2 DEFAULT NULL,
1069 x_attribute19 IN VARCHAR2 DEFAULT NULL,
1070 x_attribute20 IN VARCHAR2 DEFAULT NULL,
1071 X_MODE in VARCHAR2 default 'R',
1072 x_rev_account_cd IN VARCHAR2 DEFAULT NULL
1073 ) AS
1074 /*************************************************************
1075 Created By :
1076 Date Created By :
1077 Purpose :
1078 Know limitations, enhancements or remarks
1079 Change History
1080 Who When What
1081 msrinivi 18 Jul,2001 Added new col :rev_account_cd
1082 (reverse chronological order - newest change first)
1083 ***************************************************************/
1084
1085 X_LAST_UPDATE_DATE DATE;
1086 X_LAST_UPDATED_BY NUMBER;
1087 X_LAST_UPDATE_LOGIN NUMBER;
1088 begin
1089 X_LAST_UPDATE_DATE := SYSDATE;
1090 if(X_MODE = 'I') then
1091 X_LAST_UPDATED_BY := 1;
1092 X_LAST_UPDATE_LOGIN := 0;
1093 elsif (X_MODE = 'R') then
1094 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1095 if X_LAST_UPDATED_BY is NULL then
1096 X_LAST_UPDATED_BY := -1;
1097 end if;
1098 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1099 if X_LAST_UPDATE_LOGIN is NULL then
1100 X_LAST_UPDATE_LOGIN := -1;
1101 end if;
1102 else
1103 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1104 IGS_GE_MSG_STACK.ADD;
1105 app_exception.raise_exception;
1106 end if;
1107 Before_DML (
1108 p_action => 'UPDATE' ,
1109 x_rowid => X_ROWID,
1110 x_location_cd => X_LOCATION_CD,
1111 x_description => X_DESCRIPTION,
1112 x_location_type => X_LOCATION_TYPE,
1113 x_closed_ind => X_CLOSED_IND,
1114 x_coord_person_id => X_COORD_PERSON_ID,
1115 x_mail_dlvry_wrk_days => X_MAIL_DLVRY_WRK_DAYS,
1116 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1117 x_attribute1=>X_ATTRIBUTE1,
1118 x_attribute2=>X_ATTRIBUTE2,
1119 x_attribute3=>X_ATTRIBUTE3,
1120 x_attribute4=>X_ATTRIBUTE4,
1121 x_attribute5=>X_ATTRIBUTE5,
1122 x_attribute6=>X_ATTRIBUTE6,
1123 x_attribute7=>X_ATTRIBUTE7,
1124 x_attribute8=>X_ATTRIBUTE8,
1125 x_attribute9=>X_ATTRIBUTE9,
1126 x_attribute10=>X_ATTRIBUTE10,
1127 x_attribute11=>X_ATTRIBUTE11,
1128 x_attribute12=>X_ATTRIBUTE12,
1129 x_attribute13=>X_ATTRIBUTE13,
1130 x_attribute14=>X_ATTRIBUTE14,
1131 x_attribute15=>X_ATTRIBUTE15,
1132 x_attribute16=>X_ATTRIBUTE16,
1133 x_attribute17=>X_ATTRIBUTE17,
1134 x_attribute18=>X_ATTRIBUTE18,
1135 x_attribute19=>X_ATTRIBUTE19,
1136 x_attribute20=>X_ATTRIBUTE20,
1137 x_creation_date => X_LAST_UPDATE_DATE,
1138 x_created_by => X_LAST_UPDATED_BY,
1139 x_last_update_date => X_LAST_UPDATE_DATE,
1140 x_last_updated_by => X_LAST_UPDATED_BY,
1141 x_last_update_login => X_LAST_UPDATE_LOGIN,
1142 x_rev_account_cd => x_rev_account_cd
1143 );
1144 update IGS_AD_LOCATION_ALL set
1145 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
1146 LOCATION_TYPE = NEW_REFERENCES.LOCATION_TYPE,
1147 MAIL_DLVRY_WRK_DAYS = NEW_REFERENCES.MAIL_DLVRY_WRK_DAYS,
1148 COORD_PERSON_ID = NEW_REFERENCES.COORD_PERSON_ID,
1149 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1150 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1151 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1152 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1153 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1154 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1155 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1156 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1157 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1158 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1159 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1160 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1161 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1162 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1163 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1164 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1165 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1166 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1167 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1168 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1169 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1170 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
1171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1174 rev_account_cd = new_references.rev_account_cd
1175 WHERE ROWID = X_ROWID ;
1176 IF (SQL%NOTFOUND) THEN
1177 RAISE NO_DATA_FOUND;
1178 END IF;
1179 After_DML (
1180 p_action => 'UPDATE' ,
1181 x_rowid => X_ROWID
1182 );
1183 end UPDATE_ROW;
1184
1185 PROCEDURE ADD_ROW (
1186 X_ROWID in out NOCOPY VARCHAR2,
1187 X_ORG_ID in NUMBER,
1188 X_LOCATION_CD in VARCHAR2,
1189 X_DESCRIPTION in VARCHAR2,
1190 X_LOCATION_TYPE in VARCHAR2,
1191 X_MAIL_DLVRY_WRK_DAYS in NUMBER,
1192 X_COORD_PERSON_ID in igs_pe_person.person_id%type,
1193 X_CLOSED_IND in VARCHAR2,
1194 x_attribute_category IN VARCHAR2 DEFAULT NULL,
1195 x_attribute1 IN VARCHAR2 DEFAULT NULL,
1196 x_attribute2 IN VARCHAR2 DEFAULT NULL,
1197 x_attribute3 IN VARCHAR2 DEFAULT NULL,
1198 x_attribute4 IN VARCHAR2 DEFAULT NULL,
1199 x_attribute5 IN VARCHAR2 DEFAULT NULL,
1200 x_attribute6 IN VARCHAR2 DEFAULT NULL,
1201 x_attribute7 IN VARCHAR2 DEFAULT NULL,
1202 x_attribute8 IN VARCHAR2 DEFAULT NULL,
1203 x_attribute9 IN VARCHAR2 DEFAULT NULL,
1204 x_attribute10 IN VARCHAR2 DEFAULT NULL,
1205 x_attribute11 IN VARCHAR2 DEFAULT NULL,
1206 x_attribute12 IN VARCHAR2 DEFAULT NULL,
1207 x_attribute13 IN VARCHAR2 DEFAULT NULL,
1208 x_attribute14 IN VARCHAR2 DEFAULT NULL,
1209 x_attribute15 IN VARCHAR2 DEFAULT NULL,
1210 x_attribute16 IN VARCHAR2 DEFAULT NULL,
1211 x_attribute17 IN VARCHAR2 DEFAULT NULL,
1212 x_attribute18 IN VARCHAR2 DEFAULT NULL,
1213 x_attribute19 IN VARCHAR2 DEFAULT NULL,
1214 x_attribute20 IN VARCHAR2 DEFAULT NULL,
1215 X_MODE in VARCHAR2 default 'R',
1216 x_rev_account_cd IN VARCHAR2 DEFAULT NULL
1217 ) AS
1218 /*************************************************************
1219 Created By :
1220 Date Created By :
1221 Purpose :
1222 Know limitations, enhancements or remarks
1223 Change History
1224 Who When What
1225 msrinivi 18 Jul,2001 Added new col :rev_account_cd
1226 (reverse chronological order - newest change first)
1227 ***************************************************************/
1228
1229 cursor c1 is select rowid from IGS_AD_LOCATION_ALL
1230 where LOCATION_CD = X_LOCATION_CD ;
1231 Begin
1232 open c1;
1233 fetch c1 into X_ROWID;
1234 if (c1%notfound) then
1235 close c1;
1236 INSERT_ROW (
1237 X_ROWID,
1238 X_ORG_ID,
1239 X_LOCATION_CD,
1240 X_DESCRIPTION,
1241 X_LOCATION_TYPE,
1242 X_MAIL_DLVRY_WRK_DAYS,
1243 X_ATTRIBUTE_CATEGORY,
1244 X_ATTRIBUTE1,
1245 X_ATTRIBUTE2,
1246 X_ATTRIBUTE3,
1247 X_ATTRIBUTE4,
1248 X_ATTRIBUTE5,
1249 X_ATTRIBUTE6,
1250 X_ATTRIBUTE7,
1251 X_ATTRIBUTE8,
1252 X_ATTRIBUTE9,
1253 X_ATTRIBUTE10,
1254 X_ATTRIBUTE11,
1255 X_ATTRIBUTE12,
1256 X_ATTRIBUTE13,
1257 X_ATTRIBUTE14,
1258 X_ATTRIBUTE15,
1259 X_ATTRIBUTE16,
1260 X_ATTRIBUTE17,
1261 X_ATTRIBUTE18,
1262 X_ATTRIBUTE19,
1263 X_ATTRIBUTE20,
1264 X_COORD_PERSON_ID,
1265 X_CLOSED_IND,
1266 X_MODE,
1267 x_rev_account_cd);
1268 return;
1269 end if;
1270 close c1;
1271 UPDATE_ROW (
1272 X_ROWID ,
1273 X_LOCATION_CD,
1274 X_DESCRIPTION,
1275 X_LOCATION_TYPE,
1276 X_MAIL_DLVRY_WRK_DAYS,
1277 X_ATTRIBUTE_CATEGORY,
1278 X_ATTRIBUTE1,
1279 X_ATTRIBUTE2,
1280 X_ATTRIBUTE3,
1281 X_ATTRIBUTE4,
1282 X_ATTRIBUTE5,
1283 X_ATTRIBUTE6,
1284 X_ATTRIBUTE7,
1285 X_ATTRIBUTE8,
1286 X_ATTRIBUTE9,
1287 X_ATTRIBUTE10,
1288 X_ATTRIBUTE11,
1289 X_ATTRIBUTE12,
1290 X_ATTRIBUTE13,
1291 X_ATTRIBUTE14,
1292 X_ATTRIBUTE15,
1293 X_ATTRIBUTE16,
1294 X_ATTRIBUTE17,
1295 X_ATTRIBUTE18,
1296 X_ATTRIBUTE19,
1297 X_ATTRIBUTE20,
1298 X_COORD_PERSON_ID,
1299 X_CLOSED_IND,
1300 X_MODE,
1301 x_rev_account_cd);
1302 end ADD_ROW;
1303
1304 PROCEDURE DELETE_ROW (
1305 X_ROWID in VARCHAR2
1306 ) AS
1307 /*************************************************************
1308 Created By :
1309 Date Created By :
1310 Purpose :
1311 Know limitations, enhancements or remarks
1312 Change History
1313 Who When What
1314 (reverse chronological order - newest change first)
1315 ***************************************************************/
1316
1317 begin
1318 Before_DML (
1319 p_action => 'DELETE' ,
1320 x_rowid => X_ROWID
1321 );
1322 delete from IGS_AD_LOCATION_ALL
1323 where ROWID = X_ROWID;
1324 if (sql%notfound) then
1325 raise no_data_found;
1326 end if;
1327 After_DML (
1328 p_action => 'DELETE' ,
1329 x_rowid => X_ROWID
1330 );
1331 end DELETE_ROW;
1332
1333 end IGS_AD_LOCATION_PKG;