1 PACKAGE BODY igs_ad_source_cat_pkg AS
2 /* $Header: IGSAI71B.pls 115.22 2003/07/01 10:24:41 pbondugu ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_source_cat_all%RowType;
5 new_references igs_ad_source_cat_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_src_cat_id IN NUMBER DEFAULT NULL,
12 x_source_type_id IN NUMBER DEFAULT NULL,
13 x_category_name IN VARCHAR2 DEFAULT NULL,
14 x_mandatory_ind IN VARCHAR2 DEFAULT NULL,
15 x_include_ind IN VARCHAR2 DEFAULT NULL,
16 x_discrepancy_rule_cd IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL,
22 x_ss_mandatory_ind IN VARCHAR2 DEFAULT NULL,
23 x_ss_ind IN VARCHAR2 DEFAULT NULL,
24 x_display_sequence IN NUMBER DEFAULT NULL,
25 x_DETAIL_LEVEL_IND IN VARCHAR2 DEFAULT NULL,
26 x_AD_TAB_NAME IN VARCHAR2 DEFAULT NULL,
27 x_INT_TAB_NAME IN VARCHAR2 DEFAULT NULL
28 ) AS
29
30 /*************************************************************
31 Created By : amuthu
32 Date Created On : 16-May-2000
33 Purpose :
34 Know limitations, enhancements or remarks
35 Change History
36 Who When What
37 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
38 (reverse chronological order - newest change first)
39 ***************************************************************/
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_AD_SOURCE_CAT_ALL
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 Open cur_old_ref_values;
53 Fetch cur_old_ref_values INTO old_references;
54 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
55 Close cur_old_ref_values;
56 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
57 IGS_GE_MSG_STACK.ADD;
58 App_Exception.Raise_Exception;
59 Return;
60 END IF;
61 Close cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.org_id := x_org_id;
65 new_references.src_cat_id := x_src_cat_id;
66 new_references.source_type_id := x_source_type_id;
67 new_references.category_name := x_category_name;
68 new_references.mandatory_ind := x_mandatory_ind;
69 new_references.include_ind := x_include_ind;
70 new_references.discrepancy_rule_cd := x_discrepancy_rule_cd;
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81 new_references.ss_mandatory_ind := x_ss_mandatory_ind;
82 new_references.ss_ind := x_ss_ind;
83 new_references.display_sequence := x_display_sequence;
84 new_references.DETAIL_LEVEL_IND := x_DETAIL_LEVEL_IND;
85 new_references.ad_tab_name := x_ad_tab_name;
86 new_references.int_tab_name := x_int_tab_name;
87
88 END Set_Column_Values;
89
90 PROCEDURE Check_Constraints (
91 Column_Name IN VARCHAR2 DEFAULT NULL,
92 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
93 /*************************************************************
94 Created By : amuthu
95 Date Created On : 16-May-2000
96 Purpose :
97 Know limitations, enhancements or remarks
98 Change History
99 Who When What
100 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
101 (reverse chronological order - newest change first)
102 ***************************************************************/
103
104 BEGIN
105
106 IF column_name IS NULL THEN
107 NULL;
108 ELSIF UPPER(column_name) = 'INCLUDE_IND' THEN
109 new_references.include_ind := column_value;
110 ELSIF UPPER(column_name) = 'MANDATORY_IND' THEN
111 new_references.mandatory_ind := column_value;
112 ----Removed the validations for SS_IND,DISPLAY_SEQUENCE
113 ELSIF UPPER(column_name) = 'DETAIL_LEVEL_IND' THEN
114 new_references.detail_level_ind := column_value;
115 END IF;
116
117 -- The following code checks for check constraints on the Columns.
118 IF Upper(Column_Name) = 'INCLUDE_IND' OR
119 Column_Name IS NULL THEN
120 IF NOT (new_references.include_ind IN ('Y','N')) THEN
121 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126
127 -- The following code checks for check constraints on the Columns.
128 IF Upper(Column_Name) = 'MANDATORY_IND' OR
129 Column_Name IS NULL THEN
130 IF NOT (new_references.mandatory_ind IN ('Y','N')) THEN
131 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136
137
138 -- The following code checks for check constraints on the Columns.
139 IF Upper(Column_Name) = 'DETAIL_LEVEL_IND' OR
140 Column_Name IS NULL THEN
141 IF NOT (new_references.detail_level_ind IN ('Y','N')) THEN
142 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147
148
149 END Check_Constraints;
150
151 PROCEDURE Check_Uniqueness AS
152 /*************************************************************
153 Created By : amuthu
154 Date Created On : 16-May-2000
155 Purpose :
156 Know limitations, enhancements or remarks
157 Change History
158 Who When What
159
160 (reverse chronological order - newest change first)
161 ***************************************************************/
162 Begin
163 IF Get_Uk_For_Validation (
164 new_references.source_type_id
165 ,new_references.category_name
166 ) THEN
167 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
168 IGS_GE_MSG_STACK.ADD;
169 app_exception.raise_exception;
170 END IF;
171 END Check_Uniqueness;
172
173
174
175 PROCEDURE Check_Parent_Existance AS
176 /*************************************************************
177 Created By : amuthu
178 Date Created On : 16-May-2000
179 Purpose :
180 Know limitations, enhancements or remarks
181 Change History
182 Who When What
183
184 (reverse chronological order - newest change first)
185 ***************************************************************/
186
187 BEGIN
188
189 IF (((old_references.discrepancy_rule_cd = new_references.discrepancy_rule_cd)) OR
190 ((new_references.discrepancy_rule_cd IS NULL))) THEN
191 NULL;
192 ELSIF NOT Igs_lookups_view_Pkg.Get_PK_For_Validation (
193 'DISCREPANCY_RULE',
194 new_references.discrepancy_rule_cd
195 ) THEN
196 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
197 IGS_GE_MSG_STACK.ADD;
198 App_Exception.Raise_Exception;
199 END IF;
200
201 IF (((old_references.source_type_id = new_references.source_type_id)) OR
202 ((new_references.source_type_id IS NULL))) THEN
203 NULL;
204 ELSIF NOT Igs_Pe_Src_Types_Pkg.Get_PK_For_Validation (
205 new_references.source_type_id
206 ) THEN
207 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
208 IGS_GE_MSG_STACK.ADD;
209 App_Exception.Raise_Exception;
210 END IF;
211
212 END Check_Parent_Existance;
213
214 FUNCTION Get_PK_For_Validation (
215 x_src_cat_id IN NUMBER
216 ) RETURN BOOLEAN AS
217
218 /*************************************************************
219 Created By : amuthu
220 Date Created On : 16-May-2000
221 Purpose :
222 Know limitations, enhancements or remarks
223 Change History
224 Who When What
225
226 (reverse chronological order - newest change first)
227 ***************************************************************/
228
229 CURSOR cur_rowid IS
230 SELECT rowid
231 FROM igs_ad_source_cat_all
232 WHERE src_cat_id = x_src_cat_id
233 FOR UPDATE NOWAIT;
234
235 lv_rowid cur_rowid%RowType;
236
237 BEGIN
238
239 Open cur_rowid;
240 Fetch cur_rowid INTO lv_rowid;
241 IF (cur_rowid%FOUND) THEN
242 Close cur_rowid;
243 Return(TRUE);
244 ELSE
245 Close cur_rowid;
246 Return(FALSE);
247 END IF;
248 END Get_PK_For_Validation;
249
250 FUNCTION Get_UK_For_Validation (
251 x_source_type_id IN NUMBER,
252 x_category_name VARCHAR2
253 ) RETURN BOOLEAN AS
254
255 /*************************************************************
256 Created By : amuthu
257 Date Created On : 16-May-2000
258 Purpose :
259 Know limitations, enhancements or remarks
260 Change History
261 Who When What
262
263 (reverse chronological order - newest change first)
264 ***************************************************************/
265
266 CURSOR cur_rowid IS
267 SELECT rowid
268 FROM igs_ad_source_cat_all
269 WHERE source_type_id = x_source_type_id
270 AND category_name = x_category_name and ((l_rowid is null) or (rowid <> l_rowid))
271
272 ;
273 lv_rowid cur_rowid%RowType;
274
275 BEGIN
276
277 Open cur_rowid;
278 Fetch cur_rowid INTO lv_rowid;
279 IF (cur_rowid%FOUND) THEN
280 Close cur_rowid;
281 return (true);
282 ELSE
283 close cur_rowid;
284 return(false);
285 END IF;
286 END Get_UK_For_Validation ;
287
288
289 PROCEDURE Get_FK_Igs_Pe_Src_Types (
290 x_source_type_id IN NUMBER
291 ) AS
292 /*************************************************************
293 Created By : amuthu
294 Date Created On : 16-May-2000
295 Purpose :
296 Know limitations, enhancements or remarks
297 Change History
298 Who When What
299
300 (reverse chronological order - newest change first)
301 ***************************************************************/
302
303 CURSOR cur_rowid IS
304 SELECT rowid
305 FROM igs_ad_source_cat_all
306 WHERE source_type_id = x_source_type_id ;
307
308 lv_rowid cur_rowid%RowType;
309
310 BEGIN
311
312 Open cur_rowid;
313 Fetch cur_rowid INTO lv_rowid;
314 IF (cur_rowid%FOUND) THEN
315 Close cur_rowid;
316 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASRC_PST_FK');
317 IGS_GE_MSG_STACK.ADD;
318 App_Exception.Raise_Exception;
319 Return;
320 END IF;
321 Close cur_rowid;
322
323 END Get_FK_Igs_Pe_Src_Types;
324
325 PROCEDURE Before_DML (
326 p_action IN VARCHAR2,
327 x_rowid IN VARCHAR2 DEFAULT NULL,
328 x_org_id IN NUMBER DEFAULT NULL,
329 x_src_cat_id IN NUMBER DEFAULT NULL,
330 x_source_type_id IN NUMBER DEFAULT NULL,
331 x_category_name IN VARCHAR2 DEFAULT NULL,
332 x_mandatory_ind IN VARCHAR2 DEFAULT NULL,
333 x_include_ind IN VARCHAR2 DEFAULT NULL,
334 x_discrepancy_rule_cd IN VARCHAR2 DEFAULT NULL,
335 x_creation_date IN DATE DEFAULT NULL,
336 x_created_by IN NUMBER DEFAULT NULL,
337 x_last_update_date IN DATE DEFAULT NULL,
338 x_last_updated_by IN NUMBER DEFAULT NULL,
339 x_last_update_login IN NUMBER DEFAULT NULL,
340 x_ss_mandatory_ind IN VARCHAR2 DEFAULT NULL,
341 x_ss_ind IN VARCHAR2 DEFAULT NULL,
345 x_int_tab_name IN VARCHAR2 DEFAULT NULL
342 x_display_sequence IN NUMBER DEFAULT NULL,
343 x_detail_level_ind IN VARCHAR2 DEFAULT NULL,
344 x_ad_tab_name IN VARCHAR2 DEFAULT NULL,
346 ) AS
347 /*************************************************************
348 Created By : amuthu
349 Date Created On : 16-May-2000
350 Purpose :
351 Know limitations, enhancements or remarks
352 Change History
353 Who When What
354 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
355 (reverse chronological order - newest change first)
356 ***************************************************************/
357
358 BEGIN
359
360 Set_Column_Values (
361 p_action,
362 x_rowid,
363 x_org_id,
364 x_src_cat_id,
365 x_source_type_id,
366 x_category_name,
367 x_mandatory_ind,
368 x_include_ind,
369 x_discrepancy_rule_cd,
370 x_creation_date,
371 x_created_by,
372 x_last_update_date,
373 x_last_updated_by,
374 x_last_update_login,
375 x_ss_mandatory_ind,
376 x_ss_ind,
377 x_display_sequence,
378 x_detail_level_ind,
379 x_ad_tab_name,
380 x_int_tab_name
381 );
382
383 IF (p_action = 'INSERT') THEN
384 -- Call all the procedures related to Before Insert.
385 Null;
386 IF Get_Pk_For_Validation(
387 new_references.src_cat_id) THEN
388 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
389 IGS_GE_MSG_STACK.ADD;
390 App_Exception.Raise_Exception;
391 END IF;
392 Check_Uniqueness;
393 Check_Constraints;
394 Check_Parent_Existance;
395 ELSIF (p_action = 'UPDATE') THEN
396 -- Call all the procedures related to Before Update.
397 Check_Uniqueness;
398 Check_Constraints;
399 Check_Parent_Existance;
400 ELSIF (p_action = 'DELETE') THEN
401 -- Call all the procedures related to Before Delete.
402 before_delete(
403 X_ROWID=> x_rowid
404 );
405 ELSIF (p_action = 'VALIDATE_INSERT') THEN
406 -- Call all the procedures related to Before Insert.
407 IF Get_PK_For_Validation (
408 new_references.src_cat_id) THEN
409 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
410 IGS_GE_MSG_STACK.ADD;
411 App_Exception.Raise_Exception;
412 END IF;
413 Check_Uniqueness;
414 Check_Constraints;
415 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
416 Check_Uniqueness;
417 Check_Constraints;
418 ELSIF (p_action = 'VALIDATE_DELETE') THEN
419 Null;
420 END IF;
421
422 END Before_DML;
423
424 PROCEDURE After_DML (
425 p_action IN VARCHAR2,
426 x_rowid IN VARCHAR2,
427 x_SRC_CAT_ID IN OUT NOCOPY NUMBER,
428 x_CATEGORY_NAME IN VARCHAR2
429 ) IS
430 /*************************************************************
431 Created By : amuthu
432 Date Created On : 16-May-2000
433 Purpose :
434 Know limitations, enhancements or remarks
435 Change History
436 Who When What
437
438 (reverse chronological order - newest change first)
439 ***************************************************************/
440
441 BEGIN
442
443 l_rowid := x_rowid;
444
445 IF (p_action = 'INSERT') THEN
446 -- Call all the procedures related to After Insert.
447 after_insert(
448 x_SRC_CAT_ID =>x_SRC_CAT_ID ,
449 x_CATEGORY_NAME =>x_CATEGORY_NAME
450 );
451 ELSIF (p_action = 'UPDATE') THEN
452 -- Call all the procedures related to After Update.
453 Null;
454 ELSIF (p_action = 'DELETE') THEN
455 -- Call all the procedures related to After Delete.
456 Null;
457 END IF;
458
459 l_rowid:=NULL;
460 END After_DML;
461
462 procedure INSERT_ROW (
463 x_ROWID in out NOCOPY VARCHAR2,
464 x_ORG_ID IN NUMBER,
465 x_SRC_CAT_ID IN OUT NOCOPY NUMBER,
466 x_SOURCE_TYPE_ID IN NUMBER,
467 x_CATEGORY_NAME IN VARCHAR2,
468 x_MANDATORY_IND IN VARCHAR2,
469 x_INCLUDE_IND IN VARCHAR2,
470 x_DISCREPANCY_RULE_CD IN VARCHAR2,
471 x_MODE in VARCHAR2 default 'R',
472 x_SS_MANDATORY_IND IN VARCHAR2,
473 x_SS_IND IN VARCHAR2,
474 x_DISPLAY_SEQUENCE IN NUMBER,
475 x_detail_level_ind IN VARCHAR2 DEFAULT NULL,
476 x_ad_tab_name IN VARCHAR2 DEFAULT NULL,
477 x_int_tab_name IN VARCHAR2 DEFAULT NULL
478 ) AS
479 /*************************************************************
480 Created By : amuthu
481 Date Created On : 16-May-2000
482 Purpose :
483 Know limitations, enhancements or remarks
484 Change History
485 Who When What
486 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
490 cursor C is select ROWID from IGS_AD_SOURCE_CAT_ALL
487 (reverse chronological order - newest change first)
488 ***************************************************************/
489
491 where SRC_CAT_ID= X_SRC_CAT_ID;
492
493 X_LAST_UPDATE_DATE DATE ;
494 X_LAST_UPDATED_BY NUMBER ;
495 X_LAST_UPDATE_LOGIN NUMBER ;
496 BEGIN
497 X_LAST_UPDATE_DATE := SYSDATE;
498 if(X_MODE = 'I') then
499 X_LAST_UPDATED_BY := 1;
500 X_LAST_UPDATE_LOGIN := 0;
501 elsif (X_MODE = 'R') then
502 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
503 if X_LAST_UPDATED_BY is NULL then
504 X_LAST_UPDATED_BY := -1;
505 end if;
506 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
507 if X_LAST_UPDATE_LOGIN is NULL then
508 X_LAST_UPDATE_LOGIN := -1;
509 end if;
510 else
511 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
512 IGS_GE_MSG_STACK.ADD;
513 app_exception.raise_exception;
514 end if;
515
516 X_SRC_CAT_ID := -1;
517
518 Before_DML(
519 p_action=>'INSERT',
520 x_rowid=>X_ROWID,
521 x_org_id => igs_ge_gen_003.get_org_id,
522 x_src_cat_id=>X_SRC_CAT_ID,
523 x_source_type_id=>X_SOURCE_TYPE_ID,
524 x_category_name=>X_CATEGORY_NAME,
525 x_mandatory_ind=>X_MANDATORY_IND,
526 x_include_ind=>X_INCLUDE_IND,
527 x_discrepancy_rule_cd=>X_DISCREPANCY_RULE_CD,
528 x_creation_date=>X_LAST_UPDATE_DATE,
529 x_created_by=>X_LAST_UPDATED_BY,
530 x_last_update_date=>X_LAST_UPDATE_DATE,
531 x_last_updated_by=>X_LAST_UPDATED_BY,
532 x_last_update_login=>X_LAST_UPDATE_LOGIN,
533 x_ss_mandatory_ind=>X_SS_MANDATORY_IND,
534 x_ss_ind=>X_SS_IND,
535 x_display_sequence=>X_display_sequence,
536 x_detail_level_ind => NVL(X_DETAIL_LEVEL_IND,'N'),
537 x_ad_tab_name => X_AD_TAB_NAME,
538 x_int_tab_name => X_INT_TAB_NAME
539 );
540 insert into IGS_AD_SOURCE_CAT_ALL (
541 ORG_ID,
542 SRC_CAT_ID
543 ,SOURCE_TYPE_ID
544 ,CATEGORY_NAME
545 ,MANDATORY_IND
546 ,INCLUDE_IND
547 ,DISCREPANCY_RULE_CD
548 ,CREATION_DATE
549 ,CREATED_BY
550 ,LAST_UPDATE_DATE
551 ,LAST_UPDATED_BY
552 ,LAST_UPDATE_LOGIN,
553 SS_MANDATORY_IND
554 ,SS_IND
555 ,DISPLAY_SEQUENCE
556 ,DETAIL_LEVEL_IND
557 ,AD_TAB_NAME
558 ,INT_TAB_NAME
559 ) values
560 (
561 NEW_REFERENCES.ORG_ID,
562 IGS_AD_SRC_CAT_S.NEXTVAL
563 ,NEW_REFERENCES.SOURCE_TYPE_ID
564 ,NEW_REFERENCES.CATEGORY_NAME
565 ,NEW_REFERENCES.MANDATORY_IND
566 ,NEW_REFERENCES.INCLUDE_IND
567 ,NEW_REFERENCES.DISCREPANCY_RULE_CD
568 ,X_LAST_UPDATE_DATE
569 ,X_LAST_UPDATED_BY
570 ,X_LAST_UPDATE_DATE
571 ,X_LAST_UPDATED_BY
572 ,X_LAST_UPDATE_LOGIN
573 ,NULL
574 ,NULL
575 ,NULL
576 ,NEW_REFERENCES.DETAIL_LEVEL_IND
577 ,NEW_REFERENCES.AD_TAB_NAME
578 ,NEW_REFERENCES.INT_TAB_NAME
579 ) RETURNING SRC_CAT_ID INTO X_SRC_CAT_ID;
580 open c;
581 fetch c into X_ROWID;
582 if (c%notfound) then
583 close c;
584 raise no_data_found;
585 end if;
586 close c;
587 After_DML (
588 p_action => 'INSERT' ,
589 x_rowid => X_ROWID ,
590 x_src_cat_id => X_SRC_CAT_ID ,
591 x_category_name => NEW_REFERENCES.CATEGORY_NAME );
592
593 end INSERT_ROW;
594
595
596 procedure LOCK_ROW (
597 X_ROWID in VARCHAR2,
598 x_SRC_CAT_ID IN NUMBER,
599 x_SOURCE_TYPE_ID IN NUMBER,
600 x_CATEGORY_NAME IN VARCHAR2,
601 x_MANDATORY_IND IN VARCHAR2,
602 x_INCLUDE_IND IN VARCHAR2,
603 x_DISCREPANCY_RULE_CD IN VARCHAR2,
604 x_ss_mandatory_ind IN VARCHAR2,
605 x_ss_ind IN VARCHAR2,
606 x_display_sequence IN NUMBER,
607 x_detail_level_ind IN VARCHAR2 DEFAULT NULL,
608 x_ad_tab_name IN VARCHAR2 DEFAULT NULL,
609 x_int_tab_name IN VARCHAR2 DEFAULT NULL
610 ) AS
611 /*************************************************************
612 Created By : amuthu
613 Date Created On : 16-May-2000
614 Purpose :
615 Know limitations, enhancements or remarks
616 Change History
617 Who When What
618 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
619 (reverse chronological order - newest change first)
620 ***************************************************************/
621
622 cursor c1 is
623 select SOURCE_TYPE_ID, CATEGORY_NAME, MANDATORY_IND, INCLUDE_IND, DISCREPANCY_RULE_CD,
624 SS_IND,DISPLAY_SEQUENCE,DETAIL_LEVEL_IND,AD_TAB_NAME,INT_TAB_NAME
625 from IGS_AD_SOURCE_CAT_ALL
626 where ROWID = X_ROWID
627 for update nowait;
628 tlinfo c1%rowtype;
629 BEGIN
630 open c1;
631 fetch c1 into tlinfo;
632 if (c1%notfound) then
636 app_exception.raise_exception;
633 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
634 IGS_GE_MSG_STACK.ADD;
635 close c1;
637 return;
638 end if;
639 close c1;
640 ----Removed the check for SS_IND,DISPLAY_SEQUENCE,SS_MANDATORY_IND ( pbondugu Bug #3032535)
641 if (
642 ((tlinfo.MANDATORY_IND = X_MANDATORY_IND)
643 OR ((tlinfo.MANDATORY_IND is null)
644 AND (X_MANDATORY_IND is null)))
645 AND (tlinfo.INCLUDE_IND = X_INCLUDE_IND)
646 AND (tlinfo.DISCREPANCY_RULE_CD = X_DISCREPANCY_RULE_CD)
647 AND ((tlinfo.DETAIL_LEVEL_IND = X_DETAIL_LEVEL_IND)
648 OR ((tlinfo.DETAIL_LEVEL_IND is null)
649 AND (X_DETAIL_LEVEL_IND is null)))
650 AND ((tlinfo.AD_TAB_NAME = X_AD_TAB_NAME)
651 OR ((tlinfo.AD_TAB_NAME is null)
652 AND (X_AD_TAB_NAME is null)))
653 AND ((tlinfo.INT_TAB_NAME = X_INT_TAB_NAME)
654 OR ((tlinfo.INT_TAB_NAME is null)
655 AND (X_INT_TAB_NAME is null)))
656 ) then
657 null;
658 else
659 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
660 IGS_GE_MSG_STACK.ADD;
661 app_exception.raise_exception;
662 end if;
663 return;
664 end LOCK_ROW;
665 Procedure UPDATE_ROW (
666 X_ROWID in VARCHAR2,
667 x_SRC_CAT_ID IN NUMBER,
668 x_SOURCE_TYPE_ID IN NUMBER,
669 x_CATEGORY_NAME IN VARCHAR2,
670 x_MANDATORY_IND IN VARCHAR2,
671 x_INCLUDE_IND IN VARCHAR2,
672 x_DISCREPANCY_RULE_CD IN VARCHAR2,
673 x_ss_mandatory_ind IN VARCHAR2,
674 x_SS_IND IN VARCHAR2,
675 x_DISPLAY_SEQUENCE IN NUMBER ,
676 x_DETAIL_LEVEL_IND IN VARCHAR2 DEFAULT NULL,
677 x_AD_TAB_NAME IN VARCHAR2 DEFAULT NULL,
678 x_INT_TAB_NAME IN VARCHAR2 DEFAULT NULL,
679 X_MODE in VARCHAR2 default 'R'
680 ) AS
681 /*************************************************************
682 Created By : amuthu
683 Date Created On : 16-May-2000
684 Purpose :
685 Know limitations, enhancements or remarks
686 Change History
687 Who When What
688 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
689 (reverse chronological order - newest change first)
690 ***************************************************************/
691
692 X_LAST_UPDATE_DATE DATE ;
693 X_LAST_UPDATED_BY NUMBER ;
694 X_LAST_UPDATE_LOGIN NUMBER ;
695 BEGIN
696 X_LAST_UPDATE_DATE := SYSDATE;
697 if(X_MODE = 'I') then
698 X_LAST_UPDATED_BY := 1;
699 X_LAST_UPDATE_LOGIN := 0;
700 elsif (X_MODE = 'R') then
701 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
702 if X_LAST_UPDATED_BY is NULL then
703 X_LAST_UPDATED_BY := -1;
704 end if;
705 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
706 if X_LAST_UPDATE_LOGIN is NULL then
707 X_LAST_UPDATE_LOGIN := -1;
708 end if;
709 else
710 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
711 IGS_GE_MSG_STACK.ADD;
712 app_exception.raise_exception;
713 end if;
714 Before_DML(
715 p_action=>'UPDATE',
716 x_rowid=>X_ROWID,
717 x_src_cat_id=>X_SRC_CAT_ID,
718 x_source_type_id=>X_SOURCE_TYPE_ID,
719 x_category_name=>X_CATEGORY_NAME,
720 x_mandatory_ind=>X_MANDATORY_IND,
721 x_include_ind=>X_INCLUDE_IND,
722 x_discrepancy_rule_cd=>X_DISCREPANCY_RULE_CD,
723 x_creation_date=>X_LAST_UPDATE_DATE,
724 x_created_by=>X_LAST_UPDATED_BY,
725 x_last_update_date=>X_LAST_UPDATE_DATE,
726 x_last_updated_by=>X_LAST_UPDATED_BY,
727 x_last_update_login=>X_LAST_UPDATE_LOGIN,
728 x_ss_mandatory_ind=>X_SS_MANDATORY_IND,
729 x_ss_ind=>X_SS_IND,
730 x_display_sequence=>X_DISPLAY_SEQUENCE,
731 x_detail_level_ind=> X_DETAIL_LEVEL_IND,
732 x_ad_tab_name => X_AD_TAB_NAME,
733 x_int_tab_name => X_INT_TAB_NAME
734 );
735 update IGS_AD_SOURCE_CAT_ALL set
736 MANDATORY_IND = NEW_REFERENCES.MANDATORY_IND,
737 INCLUDE_IND = NEW_REFERENCES.INCLUDE_IND,
738 DISCREPANCY_RULE_CD = NEW_REFERENCES.DISCREPANCY_RULE_CD,
739 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
740 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
741 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
742 SS_MANDATORY_IND = NULL,
743 SS_IND = NULL,
744 DISPLAY_SEQUENCE = NULL,
745 DETAIL_LEVEL_IND = NEW_REFERENCES.DETAIL_LEVEL_IND,
746 AD_TAB_NAME = NEW_REFERENCES.AD_TAB_NAME,
747 INT_TAB_NAME = NEW_REFERENCES.INT_TAB_NAME
748 where ROWID = X_ROWID;
749 if (sql%notfound) then
750 raise no_data_found;
751 end if;
752
753 After_DML (
754 p_action => 'UPDATE' ,
755 x_rowid => X_ROWID ,
756 x_src_cat_id => NEW_REFERENCES.SRC_CAT_ID ,
757 x_category_name => NEW_REFERENCES.CATEGORY_NAME
758 );
759 end UPDATE_ROW;
760 procedure ADD_ROW (
761 X_ROWID in out NOCOPY VARCHAR2,
762 x_ORG_ID IN NUMBER,
763 x_SRC_CAT_ID IN OUT NOCOPY NUMBER,
764 x_SOURCE_TYPE_ID IN NUMBER,
765 x_CATEGORY_NAME IN VARCHAR2,
769 X_MODE in VARCHAR2 default 'R',
766 x_MANDATORY_IND IN VARCHAR2,
767 x_INCLUDE_IND IN VARCHAR2,
768 x_DISCREPANCY_RULE_CD IN VARCHAR2,
770 x_ss_mandatory_ind IN VARCHAR2,
771 x_SS_IND IN VARCHAR2,
772 x_DISPLAY_SEQUENCE IN NUMBER,
773 x_DETAIL_LEVEL_IND IN VARCHAR2 DEFAULT NULL,
774 x_AD_TAB_NAME IN VARCHAR2 DEFAULT NULL,
775 x_INT_TAB_NAME IN VARCHAR2 DEFAULT NULL
776 ) AS
777 /*************************************************************
778 Created By : amuthu
779 Date Created On : 16-May-2000
780 Purpose :
781 Know limitations, enhancements or remarks
782 Change History
783 Who When What
784 pkpatel 18-JUN-2001 Modified to add the processing for 3 new ADDED columns DETAIL_LEVEL_IND,AD_TAB_NAME and INT_TAB_NAME
785 (reverse chronological order - newest change first)
786 ***************************************************************/
787
788 cursor c1 is select ROWID from IGS_AD_SOURCE_CAT_ALL
789 where SRC_CAT_ID= X_SRC_CAT_ID
790 ;
791 BEGIN
792 open c1;
793 fetch c1 into X_ROWID;
794 if (c1%notfound) then
795 close c1;
796 INSERT_ROW (
797 X_ROWID,
798 x_ORG_ID,
799 X_SRC_CAT_ID,
800 X_SOURCE_TYPE_ID,
801 X_CATEGORY_NAME,
802 X_MANDATORY_IND,
803 X_INCLUDE_IND,
804 X_DISCREPANCY_RULE_CD,
805 X_MODE,
806 X_SS_MANDATORY_IND,
807 X_SS_IND,
808 X_DISPLAY_SEQUENCE ,
809 X_DETAIL_LEVEL_IND,
810 X_AD_TAB_NAME,
811 X_INT_TAB_NAME
812 );
813 return;
814 end if;
815 close c1;
816 UPDATE_ROW (
817 X_ROWID,
818 X_SRC_CAT_ID,
819 X_SOURCE_TYPE_ID,
820 X_CATEGORY_NAME,
821 X_MANDATORY_IND,
822 X_INCLUDE_IND,
823 X_DISCREPANCY_RULE_CD,
824 X_SS_MANDATORY_IND,
825 X_SS_IND,
826 X_DISPLAY_SEQUENCE,
827 X_DETAIL_LEVEL_IND,
828 X_AD_TAB_NAME,
829 X_INT_TAB_NAME,
830 X_MODE
831 );
832 end ADD_ROW;
833
834 procedure DELETE_ROW (
835 X_ROWID in VARCHAR2
836 ) AS
837 /*************************************************************
838 Created By : amuthu
839 Date Created On : 16-May-2000
840 Purpose :
841 Know limitations, enhancements or remarks
842 Change History
843 Who When What
844
845 (reverse chronological order - newest change first)
846 ***************************************************************/
847 BEGIN
848
849 Before_DML (
850 p_action => 'DELETE',
851 x_rowid => X_ROWID
852 );
853 delete from IGS_AD_SOURCE_CAT_ALL
854 where ROWID = X_ROWID;
855 if (sql%notfound) then
856 raise no_data_found;
857 end if;
858 After_DML (
859 p_action => 'DELETE',
860 x_rowid => X_ROWID,
861 x_src_cat_id => NEW_REFERENCES.SRC_CAT_ID ,
862 x_category_name => NEW_REFERENCES.CATEGORY_NAME
863 );
864 end DELETE_ROW;
865
866 procedure AFTER_INSERT(
867 x_SRC_CAT_ID IN OUT NOCOPY NUMBER,
868 x_CATEGORY_NAME IN VARCHAR2
869 ) AS
870 /*************************************************************
871 Created By : ssomani
872 Date Created On : 23-Oct-2000
873 Purpose :
874 Know limitations, enhancements or remarks
875 Change History
876 Who When What
877 pkpatel 18-JUN-2001 DLD: Modelling and Forecasting-SDQ
878 Added logic to populate IGS_AD_DSCP_ATTR table
879 (reverse chronological order - newest change first)
880 ***************************************************************/
881 l_rowid_ins VARCHAR2(25);
882
883 l_rowid_sysdiscrepancy_ins VARCHAR2(25);
884 l_discrepancy_attr_id igs_ad_dscp_attr.discrepancy_attr_id%TYPE;
885
886 --Inserting records corresponding to CATEGORY_NAME into IGS_AD_DSCP_ATTR reading from IGS_AD_SYSDSCP_ATTR
887 --cursor c_attr is select category_name,attribute_name from IGS_AD_SYSDSCP_ATTR
888 --where category_name=x_CATEGORY_NAME;
889
890 CURSOR c_sysdiscrepancy_attr_cur IS
891 SELECT a.lookup_code
892 FROM igs_lookup_values a
893 ,igs_lookup_values b
894 WHERE b.lookup_code = 'PERSON' -- applicable only for person details
895 AND b.lookup_code = x_category_name
896 AND b.lookup_type = 'IMP_CATEGORIES'
897 AND NVL(b.closed_ind,'N') = 'N'
898 AND a.lookup_type = 'IGS_PE_DTL_ATTR_DISCRP_RULE'
899 AND NVL(a.closed_ind,'N') = 'N';
900
901 BEGIN
902
903 FOR c_sysdiscrepancy_attr_rec IN c_sysdiscrepancy_attr_cur LOOP
904 igs_ad_dscp_attr_pkg.insert_row(
905 X_ROWID =>l_rowid_sysdiscrepancy_ins,
906 X_DISCREPANCY_ATTR_ID =>l_discrepancy_attr_id,
907 X_SRC_CAT_ID =>x_src_cat_id,
908 X_ATTRIBUTE_NAME =>c_sysdiscrepancy_attr_rec.lookup_code,
909 X_DISCREPANCY_RULE_CD =>'I', -- To make the default DISCREPANCY RULE as 'I' i.e. 'Updating Existing Values With Imported Values'
910 X_MODE =>'R');
911 END LOOP;
912 END after_insert;
913
914 procedure BEFORE_DELETE (
915 X_ROWID in VARCHAR2
916 ) AS
917 /*************************************************************
918 Created By : ssomani
919 Date Created On : 23-Oct-2000
920 Purpose :
921 Know limitations, enhancements or remarks
922 Change History
923 Who When What
924 pkpatel 18-JUN-2001 DLD: Modelling and Forecasting
925 Added logic to delete records from IGS_AD_DSCP_ATTR table
926 (reverse chronological order - newest change first)
927 ***************************************************************/
928 --Deleting records from child table IGS_AD_DSCP_ATTR using foriegn key SRC_CAT_ID
929 CURSOR c_sysdiscrepancy_attr_del_cur IS
930 SELECT ROWID
931 FROM igs_ad_dscp_attr
932 WHERE src_cat_id = (SELECT src_cat_id
933 FROM igs_ad_source_cat_all
934 WHERE ROWID = X_ROWID);
935
936 BEGIN
937
938 OPEN c_sysdiscrepancy_attr_del_cur;
939 LOOP
940 FETCH c_sysdiscrepancy_attr_del_cur into l_rowid;
941 EXIT WHEN c_sysdiscrepancy_attr_del_cur%NOTFOUND;
942 igs_ad_dscp_attr_pkg.delete_row(l_rowid);
943 END LOOP;
944
945 IF c_sysdiscrepancy_attr_del_cur%ISOPEN THEN
946 CLOSE c_sysdiscrepancy_attr_del_cur;
947 END IF;
948
949 END before_delete;
950
951 END igs_ad_source_cat_pkg;
[Home] [Help]