1 PACKAGE BODY igs_pe_delivery_cds_pkg AS
2 /* $Header: IGSNI47B.pls 115.8 2002/11/29 01:25:04 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_delivery_cds_all%RowType;
6 new_references igs_pe_delivery_cds_all%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_delivery_code IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL ,
19 X_ORG_ID in NUMBER default NULL
20 ) AS
21
22 /*************************************************************
23 Created By : svenkata
24 Date Created By :15-MAY-200
25 Purpose : TO SET COLUMN VALUES
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igs_pe_delivery_cds_all
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.delivery_code := x_delivery_code;
57 new_references.description := x_description;
58 new_references.closed_ind := x_closed_ind;
59 new_references.org_id := x_org_id;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END Set_Column_Values;
72
73 PROCEDURE Check_Constraints (
74 Column_Name IN VARCHAR2 DEFAULT NULL,
75 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
76 /*************************************************************
77 Created By : svenkata
78 Date Created By :15-MAY-200
79 Purpose : CHECK CONSTRAINTS
80 Know limitations, enhancements or remarks
81 Change History
82 Who When What
83
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86
87 BEGIN
88
89 IF column_name IS NULL THEN
90 NULL;
91 Elsif upper(column_name) = 'CLOSED_IND' THEN
92 NEW_REFERENCES.CLOSED_IND := column_value;
93 END IF;
94
95 If UPPER(column_name) = 'CLOSED_IND' or column_name is null THEN
96 if NEW_REFERENCES.CLOSED_IND not in ( 'Y' , 'N' ) THEN
97 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 end if;
101 end if;
102
103
104 END Check_Constraints;
105
106 FUNCTION Get_PK_For_Validation (
107 x_delivery_code IN VARCHAR2
108 ) RETURN BOOLEAN AS
109
110 /*************************************************************
111 Created By : svenkata
112 Date Created By :15-MAY-200
113 Purpose : CHECK PRIMARY KEY
114 Know limitations, enhancements or remarks
115 Change History
116 Who When What
117
118 (reverse chronological order - newest change first)
119 ***************************************************************/
120
121 CURSOR cur_rowid IS
122 SELECT rowid
123 FROM igs_pe_delivery_cds_all
124 WHERE delivery_code = x_delivery_code
125 FOR UPDATE NOWAIT;
126
127 lv_rowid cur_rowid%RowType;
128
129 BEGIN
130
131 Open cur_rowid;
132 Fetch cur_rowid INTO lv_rowid;
133 IF (cur_rowid%FOUND) THEN
134 Close cur_rowid;
135 Return(TRUE);
136 ELSE
137 Close cur_rowid;
138 Return(FALSE);
139 END IF;
140 END Get_PK_For_Validation;
141
142 PROCEDURE Before_DML (
143 p_action IN VARCHAR2,
144 x_rowid IN VARCHAR2 DEFAULT NULL,
145 x_delivery_code IN VARCHAR2 DEFAULT NULL,
146 x_description IN VARCHAR2 DEFAULT NULL,
147 x_closed_ind IN VARCHAR2 DEFAULT NULL,
148 x_creation_date IN DATE DEFAULT NULL,
149 x_created_by IN NUMBER DEFAULT NULL,
150 x_last_update_date IN DATE DEFAULT NULL,
151 x_last_updated_by IN NUMBER DEFAULT NULL,
152 x_last_update_login IN NUMBER DEFAULT NULL,
153 X_ORG_ID in NUMBER default NULL
154 ) AS
155 /*************************************************************
156 Created By : svenkata
157 Date Created By :15-MAY-200
158 Purpose :BEFORE DML VALIDATIONS
159 Know limitations, enhancements or remarks
160 Change History
161 Who When What
162
163 (reverse chronological order - newest change first)
164 ***************************************************************/
165
166 BEGIN
167
168 Set_Column_Values (
169 p_action,
170 x_rowid,
171 x_delivery_code,
172 x_description,
173 x_closed_ind,
174 x_creation_date,
175 x_created_by,
176 x_last_update_date,
177 x_last_updated_by,
178 x_last_update_login ,
179 x_org_id
180 );
181
182 IF (p_action = 'INSERT') THEN
183 -- Call all the procedures related to Before Insert.
184 Null;
185 IF Get_Pk_For_Validation(
186 new_references.delivery_code) THEN
187 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 Check_Constraints;
192 ELSIF (p_action = 'UPDATE') THEN
193 -- Call all the procedures related to Before Update.
194 Null;
195 Check_Constraints;
196 ELSIF (p_action = 'DELETE') THEN
197 -- Call all the procedures related to Before Delete.
198 Null;
199 ELSIF (p_action = 'VALIDATE_INSERT') THEN
200 -- Call all the procedures related to Before Insert.
201 IF Get_PK_For_Validation (
202 new_references.delivery_code) THEN
203 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
204 IGS_GE_MSG_STACK.ADD;
205 App_Exception.Raise_Exception;
206 END IF;
207 Check_Constraints;
208 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
209 Check_Constraints;
210 ELSIF (p_action = 'VALIDATE_DELETE') THEN
211 Null;
212 END IF;
213
214 END Before_DML;
215
216 PROCEDURE After_DML (
217 p_action IN VARCHAR2,
218 x_rowid IN VARCHAR2
219 ) IS
220 /*************************************************************
221 Created By : svenkata
222 Date Created By :15-MAY-200
223 Purpose : AFTER DML VALIDATIONS
224 Who When What
225
226 (reverse chronological order - newest change first)
227 ***************************************************************/
228
229 BEGIN
230
231 l_rowid := x_rowid;
232
233 IF (p_action = 'INSERT') THEN
234 -- Call all the procedures related to After Insert.
235 Null;
236 ELSIF (p_action = 'UPDATE') THEN
237 -- Call all the procedures related to After Update.
238 Null;
239 ELSIF (p_action = 'DELETE') THEN
240 -- Call all the procedures related to After Delete.
241 Null;
242 END IF;
243
244 END After_DML;
245
246 procedure INSERT_ROW (
247 X_ROWID in out NOCOPY VARCHAR2,
248 x_DELIVERY_CODE IN VARCHAR2,
249 x_DESCRIPTION IN VARCHAR2,
250 x_CLOSED_IND IN VARCHAR2,
251 X_MODE in VARCHAR2 default 'R' ,
252 X_ORG_ID in NUMBER
253 ) AS
254 /*************************************************************
255 Created By : svenkata
256 Date Created By :15-MAY-200
257 Purpose : INSERTS ROW
258 Know limitations, enhancements or remarks
259 Change History
260 Who When What
261
262 (reverse chronological order - newest change first)
263 ***************************************************************/
264
265 cursor C is select ROWID from igs_pe_delivery_cds_all
266 where DELIVERY_CODE= X_DELIVERY_CODE
267 ;
268 X_LAST_UPDATE_DATE DATE ;
269 X_LAST_UPDATED_BY NUMBER ;
270 X_LAST_UPDATE_LOGIN NUMBER ;
271 begin
272 X_LAST_UPDATE_DATE := SYSDATE;
273 if(X_MODE = 'I') then
274 X_LAST_UPDATED_BY := 1;
275 X_LAST_UPDATE_LOGIN := 0;
276 elsif (X_MODE = 'R') then
277 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
278 if X_LAST_UPDATED_BY is NULL then
279 X_LAST_UPDATED_BY := -1;
280 end if;
281 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
282 if X_LAST_UPDATE_LOGIN is NULL then
283 X_LAST_UPDATE_LOGIN := -1;
284 end if;
285 else
286 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
287 IGS_GE_MSG_STACK.ADD;
288 app_exception.raise_exception;
289 end if;
290 Before_DML(
291 p_action=>'INSERT',
292 x_rowid=>X_ROWID,
293 x_delivery_code=>X_DELIVERY_CODE,
294 x_description=>X_DESCRIPTION,
295 x_closed_ind=>X_CLOSED_IND,
296 x_creation_date=>X_LAST_UPDATE_DATE,
297 x_created_by=>X_LAST_UPDATED_BY,
298 x_last_update_date=>X_LAST_UPDATE_DATE,
299 x_last_updated_by=>X_LAST_UPDATED_BY,
300 x_last_update_login=>X_LAST_UPDATE_LOGIN,
301 x_org_id=>igs_ge_gen_003.get_org_id
302 );
303 insert into igs_pe_delivery_cds_all (
304 DELIVERY_CODE
305 ,DESCRIPTION
306 ,CLOSED_IND
307 ,CREATION_DATE
308 ,CREATED_BY
309 ,LAST_UPDATE_DATE
310 ,LAST_UPDATED_BY
311 ,LAST_UPDATE_LOGIN,
312 ORG_ID
313 ) values (
314 NEW_REFERENCES.DELIVERY_CODE
315 ,NEW_REFERENCES.DESCRIPTION
316 ,NEW_REFERENCES.CLOSED_IND
317 ,X_LAST_UPDATE_DATE
318 ,X_LAST_UPDATED_BY
319 ,X_LAST_UPDATE_DATE
320 ,X_LAST_UPDATED_BY
321 ,X_LAST_UPDATE_LOGIN,
322 NEW_REFERENCES.ORG_ID
323 );
324 open c;
325 fetch c into X_ROWID;
326 if (c%notfound) then
327 close c;
328 raise no_data_found;
329 end if;
330 close c;
331 After_DML (
332 p_action => 'INSERT' ,
333 x_rowid => X_ROWID );
334 end INSERT_ROW;
335 procedure LOCK_ROW (
336 X_ROWID in VARCHAR2,
337 x_DELIVERY_CODE IN VARCHAR2,
338 x_DESCRIPTION IN VARCHAR2,
339 x_CLOSED_IND IN VARCHAR2
340 ) AS
341 /*************************************************************
342 Created By : svenkata
343 Date Created By :15-MAY-200
344 Purpose : LOCKS ROW
345 Know limitations, enhancements or remarks
346 Change History
347 Who When What
348
349 (reverse chronological order - newest change first)
350 ***************************************************************/
351
352 cursor c1 is select
353 DESCRIPTION
354 , CLOSED_IND
355 from igs_pe_delivery_cds_all
356 where ROWID = X_ROWID
357 for update nowait;
358 tlinfo c1%rowtype;
359 begin
360 open c1;
361 fetch c1 into tlinfo;
362 if (c1%notfound) then
363 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
364 IGS_GE_MSG_STACK.ADD;
365 close c1;
366 app_exception.raise_exception;
367 return;
368 end if;
369 close c1;
370 if ( ( tlinfo.DESCRIPTION = X_DESCRIPTION)
371 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
372 ) then
373 null;
374 else
375 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
376 IGS_GE_MSG_STACK.ADD;
377 app_exception.raise_exception;
378 end if;
379 return;
380 end LOCK_ROW;
381 Procedure UPDATE_ROW (
382 X_ROWID in VARCHAR2,
383 x_DELIVERY_CODE IN VARCHAR2,
384 x_DESCRIPTION IN VARCHAR2,
385 x_CLOSED_IND IN VARCHAR2,
386 X_MODE in VARCHAR2 default 'R'
387 ) AS
388 /*************************************************************
389 Created By : svenkata
390 Date Created By :15-MAY-200
391 Purpose : UPDATES A ROW
392 Know limitations, enhancements or remarks
393 Change History
394 Who When What
395
396 (reverse chronological order - newest change first)
397 ***************************************************************/
398
399 X_LAST_UPDATE_DATE DATE ;
400 X_LAST_UPDATED_BY NUMBER ;
401 X_LAST_UPDATE_LOGIN NUMBER ;
402 begin
403 X_LAST_UPDATE_DATE := SYSDATE;
404 if(X_MODE = 'I') then
405 X_LAST_UPDATED_BY := 1;
406 X_LAST_UPDATE_LOGIN := 0;
407 elsif (X_MODE = 'R') then
408 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
409 if X_LAST_UPDATED_BY is NULL then
410 X_LAST_UPDATED_BY := -1;
411 end if;
412 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
413 if X_LAST_UPDATE_LOGIN is NULL then
414 X_LAST_UPDATE_LOGIN := -1;
415 end if;
416 else
417 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
418 IGS_GE_MSG_STACK.ADD;
419 app_exception.raise_exception;
420 end if;
421 Before_DML(
422 p_action=>'UPDATE',
423 x_rowid=>X_ROWID,
424 x_delivery_code=>X_DELIVERY_CODE,
425 x_description=>X_DESCRIPTION,
426 x_closed_ind=>X_CLOSED_IND,
427 x_creation_date=>X_LAST_UPDATE_DATE,
428 x_created_by=>X_LAST_UPDATED_BY,
429 x_last_update_date=>X_LAST_UPDATE_DATE,
430 x_last_updated_by=>X_LAST_UPDATED_BY,
431 x_last_update_login=>X_LAST_UPDATE_LOGIN
432 );
433 update igs_pe_delivery_cds_all set
434 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
435 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
436 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
437 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
438 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
439 where ROWID = X_ROWID;
440 if (sql%notfound) then
441 raise no_data_found;
442 end if;
443
444 After_DML (
445 p_action => 'UPDATE' ,
446 x_rowid => X_ROWID
447 );
448 end UPDATE_ROW;
449 procedure ADD_ROW (
450 X_ROWID in out NOCOPY VARCHAR2,
451 x_DELIVERY_CODE IN VARCHAR2,
452 x_DESCRIPTION IN VARCHAR2,
456 ) AS
453 x_CLOSED_IND IN VARCHAR2,
454 X_MODE in VARCHAR2 default 'R' ,
455 X_ORG_ID in NUMBER
457 /*************************************************************
458 Created By : svenkata
459 Date Created By :15-MAY-200
460 Purpose : ADDS A ROW
461 Know limitations, enhancements or remarks
462 Change History
463 Who When What
464
465 (reverse chronological order - newest change first)
466 ***************************************************************/
467
468 cursor c1 is select ROWID from igs_pe_delivery_cds_all
469 where DELIVERY_CODE= X_DELIVERY_CODE
470 ;
471 begin
472 open c1;
473 fetch c1 into X_ROWID;
474 if (c1%notfound) then
475 close c1;
476 INSERT_ROW (
477 X_ROWID,
478 X_DELIVERY_CODE,
479 X_DESCRIPTION,
480 X_CLOSED_IND,
481 X_MODE ,
482 x_org_id
483 );
484 return;
485 end if;
486 close c1;
487 UPDATE_ROW (
488 X_ROWID,
489 X_DELIVERY_CODE,
490 X_DESCRIPTION,
491 X_CLOSED_IND,
492 X_MODE
493 );
494 end ADD_ROW;
495 procedure DELETE_ROW (
496 X_ROWID in VARCHAR2
497 ) AS
498 /*************************************************************
499 Created By : svenkata
500 Date Created By :15-MAY-200
501 Purpose : DELETES A ROW
502 Know limitations, enhancements or remarks
503 Change History
504 Who When What
505
506 (reverse chronological order - newest change first)
507 ***************************************************************/
508
509 begin
510 Before_DML (
511 p_action => 'DELETE',
512 x_rowid => X_ROWID
513 );
514 delete from igs_pe_delivery_cds_all
515 where ROWID = X_ROWID;
516 if (sql%notfound) then
517 raise no_data_found;
518 end if;
519 After_DML (
520 p_action => 'DELETE',
521 x_rowid => X_ROWID
522 );
523 end DELETE_ROW;
524 END igs_pe_delivery_cds_pkg;
[Home] [Help]