1 PACKAGE BODY IGS_CA_VAL_DAO AS
2 /* $Header: IGSCA11B.pls 115.3 2002/11/28 22:58:40 nsidana ship $ */
3 -- Validate IGS_CA_DA_OFST
4 FUNCTION calp_val_dao_ins(
5 p_dt_alias IN VARCHAR2 ,
6 p_offset_dt_alias IN VARCHAR2 ,
7 p_message_name OUT NOCOPY VARCHAR2 )
8 RETURN boolean AS
9 v_other_detail VARCHAR(255);
10 v_alias_value DATE;
11 v_dt_alias_rec IGS_CA_DA%ROWTYPE;
12 CURSOR c_dt_alias_offset (cp_dt_alias IGS_CA_DA_OFST.dt_alias%TYPE)
13 IS
14 SELECT *
15 FROM IGS_CA_DA_OFST
16 WHERE dt_alias = cp_dt_alias
17 AND (offset_dt_alias <> p_offset_dt_alias);
18 CURSOR c_dt_alias (cp_dt_alias IGS_CA_DA.dt_alias%TYPE)
19 IS
20 SELECT *
21 FROM IGS_CA_DA
22 WHERE dt_alias = cp_dt_alias;
23 FUNCTION find_dao(p_org_dt_alias IGS_CA_DA_OFST.dt_alias%TYPE,
24 p_dt_alias IGS_CA_DA_OFST.dt_alias%TYPE)
25 RETURN BOOLEAN AS
26 v_dt_alias_offset_rec IGS_CA_DA_OFST%ROWTYPE;
27 CURSOR c_dt_alias_offset (cp_dt_alias IGS_CA_DA_OFST.dt_alias%TYPE)
28 IS
29 SELECT *
30 FROM IGS_CA_DA_OFST
31 WHERE dt_alias = cp_dt_alias;
32 BEGIN
33 IF (c_dt_alias_offset%ISOPEN = FALSE) THEN
34 OPEN c_dt_alias_offset(p_dt_alias);
35 END IF;
36 LOOP
37 FETCH c_dt_alias_offset
38 INTO v_dt_alias_offset_rec;
39 IF (c_dt_alias_offset%NOTFOUND) THEN
40 IF (c_dt_alias_offset%ISOPEN) THEN
41 CLOSE c_dt_alias_offset;
42 END IF;
43 RETURN TRUE;
44 END IF;
45 IF (v_dt_alias_offset_rec.offset_dt_alias = p_org_dt_alias) THEN
46 IF (c_dt_alias_offset%ISOPEN) THEN
47 CLOSE c_dt_alias_offset;
48 END IF;
49 RETURN FALSE;
50 ELSE
51 IF (find_dao(p_org_dt_alias,
52 v_dt_alias_offset_rec.offset_dt_alias) = TRUE) THEN
53 IF (c_dt_alias_offset%ISOPEN) THEN
54 CLOSE c_dt_alias_offset;
55 END IF;
56 RETURN TRUE;
57 ELSE
58 IF (c_dt_alias_offset%ISOPEN) THEN
59 CLOSE c_dt_alias_offset;
60 END IF;
61 RETURN FALSE;
62 END IF;
63 END IF;
64 END LOOP;
65 END find_dao;
66 BEGIN
67 OPEN c_dt_alias(p_offset_dt_alias);
68 FETCH c_dt_alias
69 INTO v_dt_alias_rec;
70 IF (v_dt_alias_rec.closed_ind = 'Y') THEN
71 CLOSE c_dt_alias;
72 p_message_name := 'IGS_CA_DTALIAS_CLOSED';
73 RETURN FALSE;
74 END IF;
75 CLOSE c_dt_alias;
76 FOR v_dt_alias_offset_rec IN c_dt_alias_offset(p_dt_alias)
77 LOOP
78 p_message_name := 'IGS_CA_DTALIAS_EXISTS';
79 RETURN FALSE;
80 END LOOP;
81 IF (p_dt_alias = p_offset_dt_alias) THEN
82 p_message_name := 'IGS_CA_DTALIAS_CANNOT_OFFSET';
83 RETURN FALSE;
84 END IF;
85 IF (find_dao(p_dt_alias, p_offset_dt_alias) = FALSE) THEN
86 p_message_name := 'IGS_CA_INVALID_DTALIAS_OFFSET';
87 RETURN FALSE;
88 ELSE
89 p_message_name := NULL;
90 RETURN TRUE;
91 END IF;
92 EXCEPTION
93 WHEN OTHERS THEN
94 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
95 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_DAO.calp_val_dao_ins');
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END calp_val_dao_ins;
99 END IGS_CA_VAL_DAO;