[Home] [Help]
PACKAGE BODY: APPS.IGP_VW_PORT_ACTIVITIES_PKG
Source
1 PACKAGE BODY IGP_VW_PORT_ACTIVITIES_PKG AS
2 /* $Header: IGSPVWCB.pls 120.0 2005/06/01 15:02:16 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGP_VW_PORT_ACTIVITIES%ROWTYPE;
6 new_references IGP_VW_PORT_ACTIVITIES%ROWTYPE;
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGP_VW_PORT_ACTIVITIES_PKG';
9 apps_exception EXCEPTION ;
10 PRAGMA EXCEPTION_INIT(apps_exception, -20001);
11
12 PROCEDURE set_column_values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2,
15 x_activity_id IN NUMBER,
16 x_portfolio_id IN NUMBER,
17 x_org_party_id IN NUMBER,
18 x_access_date IN DATE,
19 x_note IN VARCHAR2,
20 x_object_version_number IN NUMBER,
21 x_pincode IN VARCHAR2,
22 x_party_id IN NUMBER,
23 x_ACCESS_TYPE_CODE IN VARCHAR2,
24 x_creation_date IN DATE,
25 x_created_by IN NUMBER,
26 x_last_update_date IN DATE,
27 x_last_updated_by IN NUMBER,
28 x_last_update_login IN NUMBER
29 ) AS
30 /*
31 || Created By :
32 || Created On : 04-FEB-2004
33 || Purpose : Initialises the Old and New references for the columns of the table.
34 || Known limitations, enhancements or remarks :
35 || Change History :
36 || Who When What
37 || (reverse chronological order - newest change first)
38 */
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM IGP_VW_PORT_ACTIVITIES
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 OPEN cur_old_ref_values;
52 FETCH cur_old_ref_values INTO old_references;
53 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
54 CLOSE cur_old_ref_values;
55 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
56 igs_ge_msg_stack.add;
57 app_exception.raise_exception;
58 RETURN;
59 END IF;
60 CLOSE cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.activity_id := x_activity_id;
64 new_references.portfolio_id := x_portfolio_id;
65 new_references.org_party_id := x_org_party_id;
66 new_references.access_date := x_access_date;
67 new_references.note := x_note;
68 new_references.object_version_number := x_object_version_number;
69 new_references.pincode := x_pincode;
70 new_references.party_id := x_party_id;
71 new_references.ACCESS_TYPE_CODE := x_ACCESS_TYPE_CODE;
72
73 IF (p_action = 'UPDATE') THEN
74 new_references.creation_date := old_references.creation_date;
75 new_references.created_by := old_references.created_by;
76 ELSE
77 new_references.creation_date := x_creation_date;
78 new_references.created_by := x_created_by;
79 END IF;
80
81 new_references.last_update_date := x_last_update_date;
82 new_references.last_updated_by := x_last_updated_by;
83 new_references.last_update_login := x_last_update_login;
84
85 END set_column_values;
86
87
88 PROCEDURE before_dml (
89 p_action IN VARCHAR2,
90 x_rowid IN VARCHAR2,
91 x_activity_id IN NUMBER,
92 x_portfolio_id IN NUMBER,
93 x_org_party_id IN NUMBER,
94 x_access_date IN DATE,
95 x_note IN VARCHAR2,
96 x_object_version_number IN NUMBER,
97 x_pincode IN VARCHAR2,
98 x_party_id IN NUMBER,
99 x_ACCESS_TYPE_CODE IN VARCHAR2,
100 x_creation_date IN DATE,
101 x_created_by IN NUMBER,
102 x_last_update_date IN DATE,
103 x_last_updated_by IN NUMBER,
104 x_last_update_login IN NUMBER
105 ) AS
106 /*
107 || Created By :
108 || Created On : 04-FEB-2004
109 || Purpose : Initialises the columns, Checks Constraints, Calls the
110 || Trigger Handlers for the table, before any DML operation.
111 || Known limitations, enhancements or remarks :
112 || Change History :
113 || Who When What
114 || (reverse chronological order - newest change first)
115 */
116 BEGIN
117
118 set_column_values (
119 p_action,
120 x_rowid,
121 x_activity_id,
122 x_portfolio_id,
123 x_org_party_id,
124 x_access_date,
125 x_note,
126 x_object_version_number,
127 x_pincode,
128 x_party_id,
129 x_ACCESS_TYPE_CODE,
130 x_creation_date,
131 x_created_by,
132 x_last_update_date,
133 x_last_updated_by,
134 x_last_update_login
135 );
136
137 END before_dml;
138
139
140 PROCEDURE Insert_Row_Pub(
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 x_return_status OUT NOCOPY VARCHAR2,
144 x_rowid IN OUT NOCOPY VARCHAR2,
145 x_activity_id IN OUT NOCOPY NUMBER,
146 x_portfolio_id IN NUMBER,
147 x_access_date IN DATE,
148 x_note IN VARCHAR2,
149 x_object_version_number IN NUMBER,
150 x_pincode IN VARCHAR2,
151 x_party_id IN NUMBER,
152 x_ACCESS_TYPE_CODE IN VARCHAR2,
153 x_mode IN VARCHAR2
154 ) AS
155
156 CURSOR c_org_party_id (cp_party_id NUMBER,cp_portfolio_id NUMBER) IS
157 SELECT org_party_id FROM IGP_US_REG_VIEWERS
158 WHERE party_id = cp_party_id AND portfolio_id = cp_portfolio_id;
159
160 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row_Pub';
161 l_org_party_id VARCHAR2(30);
162 BEGIN
163
164 SAVEPOINT Insert_comments_Main;
165
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167 FND_MSG_PUB.initialize;
168
169 IF x_pincode IS NULL THEN
170 OPEN c_org_party_id(x_party_id,x_portfolio_id);
171 FETCH c_org_party_id INTO l_org_party_id;
172 IF c_org_party_id%NOTFOUND THEN
173 insert_row(
174 x_rowid => x_rowid,
175 x_activity_id => x_activity_id,
176 x_portfolio_id => x_portfolio_id,
177 x_org_party_id => NULL,
178 x_access_date => SYSDATE,
179 x_note => x_note,
180 x_object_version_number => x_object_version_number,
181 x_pincode => x_pincode,
182 x_party_id => x_party_id,
183 x_ACCESS_TYPE_CODE => x_ACCESS_TYPE_CODE,
184 x_mode => x_mode
185 );
186 -- FOR R_ORG_PARTY_ID IN c_org_party_id(x_party_id,x_portfolio_id)
187 ELSE
188 LOOP
189 BEGIN
190 SAVEPOINT Insert_comments;
191
192 insert_row(
193 x_rowid => x_rowid,
194 x_activity_id => x_activity_id,
195 x_portfolio_id => x_portfolio_id,
196 x_org_party_id => l_org_party_id,
197 x_access_date => SYSDATE,
198 x_note => x_note,
199 x_object_version_number => x_object_version_number,
200 x_pincode => x_pincode,
201 x_party_id => x_party_id,
202 x_ACCESS_TYPE_CODE => x_ACCESS_TYPE_CODE,
203 x_mode => x_mode
204 );
205 EXCEPTION
206 WHEN FND_API.G_EXC_ERROR THEN
207 ROLLBACK TO Insert_comments;
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 END;
210 FETCH c_org_party_id INTO l_org_party_id;
211 EXIT WHEN c_org_party_id%NOTFOUND;
212 END LOOP;
213 END IF;
214 CLOSE c_org_party_id;
215 ELSE
216 insert_row(
217 x_rowid => x_rowid,
218 x_activity_id => x_activity_id,
219 x_portfolio_id => x_portfolio_id,
220 x_org_party_id => NULL,
221 x_access_date => SYSDATE,
222 x_note => x_note,
223 x_object_version_number => x_object_version_number,
224 x_pincode => x_pincode,
225 x_party_id => x_party_id,
226 x_ACCESS_TYPE_CODE => x_ACCESS_TYPE_CODE,
227 x_mode => x_mode
228 );
229 END IF;
230 -- Standard call to get message count and if count is 1, get message info.
231 FND_MSG_PUB.Count_And_Get
232 (
233 p_count => x_msg_count ,
234 p_data => x_msg_data
235 );
236
237 EXCEPTION
238 WHEN apps_exception THEN
239 ROLLBACK TO Insert_comments_Main;
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
242 p_data => x_msg_data );
243 WHEN FND_API.G_EXC_ERROR THEN
244 ROLLBACK TO Insert_comments_Main;
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
247 p_data => x_msg_data );
248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
249 ROLLBACK TO Insert_comments_Main;
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
251 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
252 p_data => x_msg_data );
253 WHEN OTHERS THEN
254 ROLLBACK TO Insert_comments_Main;
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
257 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
258 END IF;
259 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
260 p_data => x_msg_data );
261
262 END Insert_Row_Pub;
263
264 PROCEDURE insert_row (
265 x_rowid IN OUT NOCOPY VARCHAR2,
266 x_activity_id IN OUT NOCOPY NUMBER,
267 x_portfolio_id IN NUMBER,
268 x_org_party_id IN NUMBER,
269 x_access_date IN DATE,
270 x_note IN VARCHAR2,
271 x_object_version_number IN NUMBER,
272 x_pincode IN VARCHAR2,
273 x_party_id IN NUMBER,
274 x_ACCESS_TYPE_CODE IN VARCHAR2,
275 x_mode IN VARCHAR2
276 ) AS
277 /*
278 || Created By :
279 || Created On : 04-FEB-2004
280 || Purpose : Handles the INSERT DML logic for the table.
281 || Known limitations, enhancements or remarks :
282 || Change History :
283 || Who When What
284 || (reverse chronological order - newest change first)
285 */
286
287 x_last_update_date DATE;
288 x_last_updated_by NUMBER;
289 x_last_update_login NUMBER;
290
291 BEGIN
292
293 x_last_update_date := SYSDATE;
294 IF (x_mode = 'I') THEN
295 x_last_updated_by := 1;
296 x_last_update_login := 0;
297 ELSIF (x_mode = 'R') THEN
298 x_last_updated_by := fnd_global.user_id;
299 IF (x_last_updated_by IS NULL) THEN
300 x_last_updated_by := -1;
301 END IF;
302 x_last_update_login := fnd_global.login_id;
303
304 IF (x_last_update_login IS NULL) THEN
305 x_last_update_login := -1;
306 END IF;
307 ELSE
308 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
309 fnd_message.set_token ('ROUTINE', 'IGP_VW_PORT_ACTIVITIES_PKG.INSERT_ROW');
310 igs_ge_msg_stack.add;
311 app_exception.raise_exception;
312 END IF;
313
314 x_activity_id := NULL;
315
316 before_dml(
317 p_action => 'INSERT',
318 x_rowid => x_rowid,
319 x_activity_id => x_activity_id,
320 x_portfolio_id => x_portfolio_id,
321 x_org_party_id => x_org_party_id,
322 x_access_date => x_access_date,
323 x_note => x_note,
324 x_object_version_number => x_object_version_number,
325 x_pincode => x_pincode,
326 x_party_id => x_party_id,
327 x_ACCESS_TYPE_CODE => x_ACCESS_TYPE_CODE,
328 x_creation_date => x_last_update_date,
329 x_created_by => x_last_updated_by,
330 x_last_update_date => x_last_update_date,
331 x_last_updated_by => x_last_updated_by,
332 x_last_update_login => x_last_update_login
333 );
334
335 INSERT INTO IGP_VW_PORT_ACTIVITIES (
336 activity_id,
337 portfolio_id,
338 org_party_id,
339 access_date,
340 note,
341 object_version_number,
342 pincode,
343 party_id,
344 ACCESS_TYPE_CODE,
345 creation_date,
346 created_by,
347 last_update_date,
348 last_updated_by,
349 last_update_login
350 ) VALUES (
351 IGP_VW_PORT_ACTIVITIES_S.NEXTVAL,
352 new_references.portfolio_id,
353 new_references.org_party_id,
354 new_references.access_date,
355 new_references.note,
356 new_references.object_version_number,
357 new_references.pincode,
358 new_references.party_id,
359 new_references.ACCESS_TYPE_CODE,
360 x_last_update_date,
361 x_last_updated_by,
362 x_last_update_date,
363 x_last_updated_by,
364 x_last_update_login
365 ) RETURNING ROWID, activity_id INTO x_rowid, x_activity_id;
366
367 END insert_row;
368
369 END IGP_VW_PORT_ACTIVITIES_pkg;