1 PACKAGE BODY IGS_EN_GS_ATTRIB_VAL AS
2 /* $Header: IGSEN90B.pls 115.1 2002/11/29 00:12:53 nsidana noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_EN_GS_ATTRIB_VAL';
5 g_prod VARCHAR2(3) := 'IGS';
6
7 PROCEDURE Set_Value(
8 p_obj_type_id IN NUMBER,
9 p_obj_id IN NUMBER,
10 p_attrib_id IN NUMBER,
11 p_version IN NUMBER,
12 p_value IN VARCHAR2,
13 x_return_code OUT NOCOPY VARCHAR2
14 )
15 IS
16 l_api_name CONSTANT VARCHAR2(30) := 'Set_Value';
17
18
19 CURSOR c_obj_type IS
20 SELECT 'exist'
21 FROM igs_en_object_types
22 WHERE obj_type_id = p_obj_type_id;
23
24
25 CURSOR c_attr IS
26 SELECT 'exist'
27 FROM igs_en_attributes
28 WHERE attrib_id = p_attrib_id;
29
30
31 CURSOR c_rowid IS
32 SELECT rowid
33 FROM igs_en_attrib_values
34 WHERE obj_type_id = p_obj_type_id
35 AND obj_id = p_obj_id
36 AND attrib_id = p_attrib_id
37 AND version = p_version;
38
39
40 CURSOR c_exists IS
41 SELECT 'Y'
42 FROM igs_en_attrib_values
43 WHERE obj_type_id = p_obj_type_id
44 AND obj_id = p_obj_id
45 AND attrib_id = p_attrib_id
46 AND version = p_version;
47
48
49 l_value VARCHAR2(255);
50 l_rowid VARCHAR2(255);
51 l_exists VARCHAR2(1);
52
53 BEGIN
54
55 OPEN c_obj_type;
56 FETCH c_obj_type INTO l_value;
57 IF c_obj_type%NOTFOUND THEN
58 x_return_code := 'F01'; --Object type not found
59 CLOSE c_obj_type;
60 RETURN;
61 END IF;
62 CLOSE c_obj_type;
63
64 OPEN c_attr;
65 FETCH c_attr INTO l_value;
66 IF c_attr%NOTFOUND THEN
67 x_return_code := 'F02'; --Attribute not found
68 CLOSE c_attr;
69 RETURN;
70 END IF;
71 CLOSE c_attr;
72
73 IF p_obj_id IS NULL THEN
74 x_return_code := 'F03'; --Invalid object id
75 RETURN;
76 END IF;
77
78 IF p_version IS NULL THEN
79 x_return_code := 'F04'; --Invalid version
80 RETURN;
81 END IF;
82
83 IF length(p_value)>255 THEN
84 x_return_code := 'F05'; --Value too long
85 RETURN;
86 END IF;
87
88 IF p_value IS NOT NULL THEN
89
90 -- Checking if the record already exists. If yes, then update the record else insert a new record.
91 OPEN c_exists;
92 FETCH c_exists INTO l_exists;
93 CLOSE c_exists;
94
95 IF l_exists = 'Y' THEN
96 OPEN c_rowid;
97 FETCH c_rowid INTO l_rowid;
98 CLOSE c_rowid;
99
100 -- Updating the current row
101 IGS_EN_ATTRIB_VALUES_PKG.UPDATE_ROW(
102 X_ROWID => l_rowid ,
103 X_OBJ_TYPE_ID => p_obj_type_id ,
104 X_OBJ_ID => p_obj_id ,
105 X_ATTRIB_ID => p_attrib_id ,
106 X_VERSION => p_version ,
107 X_VALUE => p_value
108 );
109 x_return_code := 'S01';
110 ELSE
111 -- Inserting the record
112 IGS_EN_ATTRIB_VALUES_PKG.INSERT_ROW(
113 X_ROWID => l_rowid ,
114 X_OBJ_TYPE_ID => p_obj_type_id ,
115 X_OBJ_ID => p_obj_id ,
116 X_ATTRIB_ID => p_attrib_id ,
117 X_VERSION => p_version ,
118 X_VALUE => p_value
119 );
120 x_return_code := 'S00';
121 END IF;
122
123 END IF;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
128 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
129 END IF;
130 x_return_code := 'F99';
131 END Set_Value;
132
133
134 FUNCTION Get_Value (
135 p_obj_type_id IN NUMBER,
136 p_obj_id IN NUMBER,
137 p_attrib_id IN NUMBER,
138 p_version IN NUMBER
139 ) RETURN VARCHAR2
140 IS
141
142 -- Cursor to get the value from the attributes table
143 CURSOR c_attr_val IS
144 SELECT value
145 FROM igs_en_attrib_values
146 WHERE obj_type_id = p_obj_type_id
147 AND obj_id = p_obj_id
148 AND attrib_id = p_attrib_id
149 AND version = p_version;
150
151 l_value VARCHAR2(255);
152
153 BEGIN
154
155 OPEN c_attr_val;
156 FETCH c_attr_val INTO l_value;
157 CLOSE c_attr_val;
158 RETURN l_value;
159
160 END Get_Value;
161
162 END IGS_EN_GS_ATTRIB_VAL;
[Home] [Help]