1 package body IGS_OR_INST_ADDR_PKG AS
2 /* $Header: IGSOI03B.pls 115.4 2002/02/12 17:08:06 pkm ship $ */
3 /*************************************************************
4 Changed By : smanglm
5 Date : 2000/08/25
6 Purpose : to remove the procedures like insert_row, update_row,lock_row,
7 before DML, check_parent_existence,check_child_existence,
8 check_constraints, set_column_values, after_dml for
9 TCA related work as this would now be taken care through TCA
10 table handlers.
11 Know limitations, enhancements or remarks
12 Change History
13 Who When What
14
15 (reverse chronological order - newest change first)
16 ***************************************************************/
17
18 l_rowid VARCHAR2(25);
19
20 FUNCTION Get_PK_For_Validation (
21 x_institution_cd IN VARCHAR2,
22 x_addr_type IN VARCHAR2,
23 x_start_dt IN DATE
24 )
25 RETURN BOOLEAN
26 AS
27 /*************************************************************
28 Created By : kdande
29 Date Created By : 19-May-2000
30 Purpose :
31 Know limitations, enhancements or remarks
32 Change History
33 Who When What
34
35 (reverse chronological order - newest change first)
36 ***************************************************************/
37 CURSOR cur_rowid IS
38 SELECT rowid
39 FROM IGS_OR_INST_ADDR
40 WHERE institution_cd = x_institution_cd
41 AND addr_type = x_addr_type
42 AND start_dt = x_start_dt
43 FOR UPDATE NOWAIT;
44 lv_rowid cur_rowid%RowType;
45 BEGIN
46 Open cur_rowid;
47 IF (cur_rowid%FOUND) THEN
48 Close cur_rowid;
49 RETURN(TRUE);
50 ELSE
51 Close cur_rowid;
52 RETURN(FALSE);
53 END IF;
54 END Get_PK_For_Validation;
55
56 PROCEDURE GET_FK_IGS_CO_ADDR_TYPE (
57 x_addr_type IN VARCHAR2
58 ) AS
59 /*************************************************************
60 Created By : kdande
61 Date Created By : 19-May-2000
62 Purpose :
63 Know limitations, enhancements or remarks
64 Change History
65 Who When What
66
67 (reverse chronological order - newest change first)
68 ***************************************************************/
69 CURSOR cur_rowid IS
70 SELECT rowid
71 FROM IGS_OR_INST_ADDR
72 WHERE addr_type = x_addr_type ;
73 lv_rowid cur_rowid%RowType;
74 BEGIN
75 Open cur_rowid;
76 Fetch cur_rowid INTO lv_rowid;
77 IF (cur_rowid%FOUND) THEN
78 Close cur_rowid;
79 Fnd_Message.Set_Name ('IGS', 'IGS_OR_IA_ADT_FK');
80 IGS_GE_MSG_STACK.ADD;
81 App_Exception.Raise_Exception;
82 Return;
83 END IF;
84 Close cur_rowid;
85 END GET_FK_IGS_CO_ADDR_TYPE;
86
87 PROCEDURE GET_FK_IGS_OR_INSTITUTION (
88 x_institution_cd IN VARCHAR2
89 ) AS
90 /*************************************************************
91 Created By : kdande
92 Date Created By : 19-May-2000
93 Purpose :
94 Know limitations, enhancements or remarks
95 Change History
96 Who When What
97
98 (reverse chronological order - newest change first)
99 ***************************************************************/
100 CURSOR cur_rowid is
101 SELECT rowid
102 FROM IGS_OR_INST_ADDR
103 WHERE institution_cd = x_institution_cd ;
104 lv_rowid cur_rowid%RowType;
105 BEGIN
106 Open cur_rowid;
107 Fetch cur_rowid INTO lv_rowid;
108 IF (cur_rowid%FOUND) THEN
109 Close cur_rowid;
110 Fnd_Message.Set_Name ('IGS', 'IGS_OR_IA_INS_FK');
111 IGS_GE_MSG_STACK.ADD;
112 App_Exception.Raise_Exception;
113 Return;
114 END IF;
115 Close cur_rowid;
116 END GET_FK_IGS_OR_INSTITUTION;
117
118 PROCEDURE GET_FK_IGS_PE_SUBURB_POSTCD (
119 x_postcode IN VARCHAR2
120 ) AS
121 CURSOR cur_rowid IS
122 SELECT rowid
123 FROM IGS_OR_INST_ADDR
124 WHERE postal_code = x_postcode ;
125 lv_rowid cur_rowid%RowType;
126 BEGIN
127 Open cur_rowid;
128 Fetch cur_rowid INTO lv_rowid;
129 IF (cur_rowid%FOUND) THEN
130 Close cur_rowid;
131 Fnd_Message.Set_Name ('IGS', 'IGS_OR_IA_SP_FK');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 Return;
135 END IF;
136 Close cur_rowid;
137 END GET_FK_IGS_PE_SUBURB_POSTCD;
138
139 end IGS_OR_INST_ADDR_PKG;