1 package body JTF_RS_GRP_RELATE_AUD_PKG as
2 /* $Header: jtfrstqb.pls 120.0 2005/05/11 08:22:30 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_GROUP_RELATE_AUDIT_ID in NUMBER,
6 X_GROUP_RELATE_ID in NUMBER,
7 X_NEW_GROUP_ID in NUMBER,
8 X_OLD_GROUP_ID in NUMBER,
9 X_NEW_RELATED_GROUP_ID in NUMBER,
10 X_OLD_RELATED_GROUP_ID in NUMBER,
11 X_NEW_RELATION_TYPE in VARCHAR2,
12 X_OLD_RELATION_TYPE in VARCHAR2,
13 X_NEW_START_DATE_ACTIVE in DATE,
14 X_OLD_START_DATE_ACTIVE in DATE,
15 X_NEW_END_DATE_ACTIVE in DATE,
16 X_OLD_END_DATE_ACTIVE in DATE,
17 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
18 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
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 ) is
25 cursor C is select ROWID from JTF_RS_GRP_RELATE_AUD
26 where GROUP_RELATE_AUDIT_ID = X_GROUP_RELATE_AUDIT_ID
27 ;
28 begin
29 insert into JTF_RS_GRP_RELATE_AUD (
30 GROUP_RELATE_AUDIT_ID,
31 GROUP_RELATE_ID,
32 NEW_GROUP_ID,
33 OLD_GROUP_ID,
34 NEW_RELATED_GROUP_ID,
35 OLD_RELATED_GROUP_ID,
36 NEW_RELATION_TYPE,
37 OLD_RELATION_TYPE,
38 NEW_START_DATE_ACTIVE,
39 OLD_START_DATE_ACTIVE,
40 NEW_END_DATE_ACTIVE,
41 OLD_END_DATE_ACTIVE,
42 NEW_OBJECT_VERSION_NUMBER,
43 OLD_OBJECT_VERSION_NUMBER,
44 CREATED_BY,
45 CREATION_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATE_LOGIN
49 ) values (
50 X_GROUP_RELATE_AUDIT_ID,
51 X_GROUP_RELATE_ID,
52 X_NEW_GROUP_ID,
53 X_OLD_GROUP_ID,
54 X_NEW_RELATED_GROUP_ID,
55 X_OLD_RELATED_GROUP_ID,
56 X_NEW_RELATION_TYPE,
57 X_OLD_RELATION_TYPE,
58 X_NEW_START_DATE_ACTIVE,
59 X_OLD_START_DATE_ACTIVE,
60 X_NEW_END_DATE_ACTIVE,
61 X_OLD_END_DATE_ACTIVE,
62 X_NEW_OBJECT_VERSION_NUMBER,
63 X_OLD_OBJECT_VERSION_NUMBER,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN);
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_GROUP_RELATE_AUDIT_ID in NUMBER,
82 X_GROUP_RELATE_ID in NUMBER,
83 X_NEW_GROUP_ID in NUMBER,
84 X_OLD_GROUP_ID in NUMBER,
85 X_NEW_RELATED_GROUP_ID in NUMBER,
86 X_OLD_RELATED_GROUP_ID in NUMBER,
87 X_NEW_RELATION_TYPE in VARCHAR2,
88 X_OLD_RELATION_TYPE in VARCHAR2,
89 X_NEW_START_DATE_ACTIVE in DATE,
90 X_OLD_START_DATE_ACTIVE in DATE,
91 X_NEW_END_DATE_ACTIVE in DATE,
92 X_OLD_END_DATE_ACTIVE in DATE,
93 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
94 X_OLD_OBJECT_VERSION_NUMBER in NUMBER
95 ) is
96 cursor c1 is select
97 GROUP_RELATE_ID,
98 NEW_GROUP_ID,
99 OLD_GROUP_ID,
100 NEW_RELATED_GROUP_ID,
101 OLD_RELATED_GROUP_ID,
102 NEW_RELATION_TYPE,
103 OLD_RELATION_TYPE,
104 NEW_START_DATE_ACTIVE,
105 OLD_START_DATE_ACTIVE,
106 NEW_END_DATE_ACTIVE,
107 OLD_END_DATE_ACTIVE,
108 NEW_OBJECT_VERSION_NUMBER,
109 OLD_OBJECT_VERSION_NUMBER
110 from JTF_RS_GRP_RELATE_AUD
111 where GROUP_RELATE_AUDIT_ID = X_GROUP_RELATE_AUDIT_ID
112 for update of GROUP_RELATE_AUDIT_ID nowait;
113 tlinfo c1%rowtype;
114 begin
115 open c1;
116 fetch c1 into tlinfo;
117 if (c1%notfound) then
118 fnd_message.set_name('FND','FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 close c1;
121 end if;
122 close c1;
123
124 if (
125 (tlinfo.GROUP_RELATE_ID = X_GROUP_RELATE_ID)
126 AND ((tlinfo.NEW_GROUP_ID = X_NEW_GROUP_ID)
127 OR ((tlinfo.NEW_GROUP_ID is null) AND (X_NEW_GROUP_ID is null)))
128 AND ((tlinfo.OLD_GROUP_ID = X_OLD_GROUP_ID)
129 OR ((tlinfo.OLD_GROUP_ID is null) AND (X_OLD_GROUP_ID is null)))
130 AND ((tlinfo.NEW_RELATED_GROUP_ID = X_NEW_RELATED_GROUP_ID)
131 OR ((tlinfo.NEW_RELATED_GROUP_ID is null) AND (X_NEW_RELATED_GROUP_ID is null)))
132 AND ((tlinfo.OLD_RELATED_GROUP_ID = X_OLD_RELATED_GROUP_ID)
133 OR ((tlinfo.OLD_RELATED_GROUP_ID is null) AND (X_OLD_RELATED_GROUP_ID is null)))
134 AND ((tlinfo.NEW_RELATION_TYPE = X_NEW_RELATION_TYPE)
135 OR ((tlinfo.NEW_RELATION_TYPE is null) AND (X_NEW_RELATION_TYPE is null)))
136 AND ((tlinfo.OLD_RELATION_TYPE = X_OLD_RELATION_TYPE)
137 OR ((tlinfo.OLD_RELATION_TYPE is null) AND (X_OLD_RELATION_TYPE is null)))
138 AND ((tlinfo.NEW_START_DATE_ACTIVE = X_NEW_START_DATE_ACTIVE)
139 OR ((tlinfo.NEW_START_DATE_ACTIVE is null) AND (X_NEW_START_DATE_ACTIVE is null)))
140 AND ((tlinfo.OLD_START_DATE_ACTIVE = X_OLD_START_DATE_ACTIVE)
141 OR ((tlinfo.OLD_START_DATE_ACTIVE is null) AND (X_OLD_START_DATE_ACTIVE is null)))
142 AND ((tlinfo.NEW_END_DATE_ACTIVE = X_NEW_END_DATE_ACTIVE)
143 OR ((tlinfo.NEW_END_DATE_ACTIVE is null) AND (X_NEW_END_DATE_ACTIVE is null)))
144 AND ((tlinfo.OLD_END_DATE_ACTIVE = X_OLD_END_DATE_ACTIVE)
145 OR ((tlinfo.OLD_END_DATE_ACTIVE is null) AND (X_OLD_END_DATE_ACTIVE is null)))
146 AND ((tlinfo.NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER)
147 OR ((tlinfo.NEW_OBJECT_VERSION_NUMBER is null) AND (X_NEW_OBJECT_VERSION_NUMBER is null)))
148 AND ((tlinfo.OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER)
149 OR ((tlinfo.OLD_OBJECT_VERSION_NUMBER is null) AND (X_OLD_OBJECT_VERSION_NUMBER is null)))
150 ) then
151 null;
152 else
153 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
154 app_exception.raise_exception;
155 end if;
156 return;
157 end LOCK_ROW;
158
159 procedure UPDATE_ROW (
160 X_GROUP_RELATE_AUDIT_ID in NUMBER,
161 X_GROUP_RELATE_ID in NUMBER,
162 X_NEW_GROUP_ID in NUMBER,
163 X_OLD_GROUP_ID in NUMBER,
164 X_NEW_RELATED_GROUP_ID in NUMBER,
165 X_OLD_RELATED_GROUP_ID in NUMBER,
166 X_NEW_RELATION_TYPE in VARCHAR2,
167 X_OLD_RELATION_TYPE in VARCHAR2,
168 X_NEW_START_DATE_ACTIVE in DATE,
169 X_OLD_START_DATE_ACTIVE in DATE,
170 X_NEW_END_DATE_ACTIVE in DATE,
171 X_OLD_END_DATE_ACTIVE in DATE,
172 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
173 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
174 X_LAST_UPDATE_DATE in DATE,
175 X_LAST_UPDATED_BY in NUMBER,
176 X_LAST_UPDATE_LOGIN in NUMBER
177 ) is
178 begin
179 update JTF_RS_GRP_RELATE_AUD set
180 GROUP_RELATE_ID = X_GROUP_RELATE_ID,
181 NEW_GROUP_ID = X_NEW_GROUP_ID,
182 OLD_GROUP_ID = X_OLD_GROUP_ID,
183 NEW_RELATED_GROUP_ID = X_NEW_RELATED_GROUP_ID,
184 OLD_RELATED_GROUP_ID = X_OLD_RELATED_GROUP_ID,
185 NEW_RELATION_TYPE = X_NEW_RELATION_TYPE,
186 OLD_RELATION_TYPE = X_OLD_RELATION_TYPE,
187 NEW_START_DATE_ACTIVE = X_NEW_START_DATE_ACTIVE,
188 OLD_START_DATE_ACTIVE = X_OLD_START_DATE_ACTIVE,
189 NEW_END_DATE_ACTIVE = X_NEW_END_DATE_ACTIVE,
190 OLD_END_DATE_ACTIVE = X_OLD_END_DATE_ACTIVE,
191 NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER,
192 OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER,
193 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
196 where GROUP_RELATE_AUDIT_ID = X_GROUP_RELATE_AUDIT_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end UPDATE_ROW;
202
203 procedure DELETE_ROW (
204 X_GROUP_RELATE_AUDIT_ID in NUMBER
205 ) is
206 begin
207 delete from JTF_RS_GRP_RELATE_AUD
208 where GROUP_RELATE_AUDIT_ID = X_GROUP_RELATE_AUDIT_ID;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213
214 end DELETE_ROW;
215
216 end JTF_RS_GRP_RELATE_AUD_PKG;