[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_SCIENCE_CODES_TBH
Source
1 PACKAGE BODY Igw_Prop_Science_Codes_Tbh AS
2 /* $Header: igwtkeyb.pls 115.5 2002/11/15 00:45:24 ashkumar ship $ */
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_PROP_SCIENCE_CODES_TBH';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Insert_Row
11 (
12 x_rowid OUT NOCOPY VARCHAR2,
13 p_proposal_id IN NUMBER,
14 p_science_code IN VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 p_mode IN VARCHAR2
17 ) IS
18
19 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
20
21 l_last_update_date DATE := SYSDATE;
22 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
23 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
24
25 CURSOR c IS
26 SELECT rowid
27 FROM igw_prop_science_codes
28 WHERE proposal_id = p_proposal_id AND
29 science_code = p_science_code;
30
31 BEGIN
32
33 x_return_status := Fnd_Api.G_Ret_Sts_Success;
34
35 IF p_mode = 'I' THEN
36
37 l_last_updated_by := 1;
38 l_last_update_login := 0;
39
40 ELSIF p_mode <> 'R' THEN
41
42 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
43 App_Exception.Raise_Exception;
44
45 END IF;
46
47 INSERT INTO igw_prop_science_codes
48 (
49 proposal_id,
50 science_code,
51 record_version_number,
52 creation_date,
53 created_by,
54 last_update_date,
55 last_updated_by,
56 last_update_login
57 )
58 VALUES
59 (
60 p_proposal_id, /* proposal_id */
61 p_science_code, /* science_code */
62 1, /* record_version_number */
63 l_last_update_date, /* creation_date */
64 l_last_updated_by, /* created_by */
65 l_last_update_date, /* last_update_date */
66 l_last_updated_by, /* last_updated_by */
67 l_last_update_login /* last_update_login */
68 );
69
70 OPEN c;
71 FETCH c INTO x_rowid;
72
73 IF c%NotFound THEN
74
75 CLOSE c;
76 RAISE no_data_found;
77
78 END IF;
79
80 CLOSE c;
81
82 EXCEPTION
83
84 WHEN others THEN
85
86 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
87
88 Fnd_Msg_Pub.Add_Exc_Msg
89 (
90 p_pkg_name => G_PKG_NAME,
91 p_procedure_name => l_api_name
92 );
93
94 RAISE Fnd_Api.G_Exc_Unexpected_Error;
95
96 END Insert_Row;
97
98 ---------------------------------------------------------------------------
99
100 PROCEDURE Update_Row
101 (
102 p_rowid IN VARCHAR2,
103 p_proposal_id IN NUMBER,
104 p_science_code IN VARCHAR2,
105 p_record_version_number IN NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2,
107 p_mode IN VARCHAR2
108 ) IS
109
110 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
111
112 l_last_update_date DATE := SYSDATE;
113 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
114 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
115
116 BEGIN
117
118 x_return_status := Fnd_Api.G_Ret_Sts_Success;
119
120 IF p_mode = 'I' THEN
121
122 l_last_updated_by := 1;
123 l_last_update_login := 0;
124
125 ELSIF p_mode <> 'R' THEN
126
127 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
128 Fnd_Msg_Pub.Add;
129 App_Exception.Raise_Exception;
130
131 END IF;
132
133 UPDATE igw_prop_science_codes
134 SET science_code = p_science_code,
135 record_version_number = record_version_number + 1,
136 last_update_date = l_last_update_date,
137 last_updated_by = l_last_updated_by,
138 last_update_login = l_last_update_login
139 WHERE rowid = p_rowid
140 AND record_version_number = p_record_version_number;
141
142 IF SQL%NotFound THEN
143
144 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
145 Fnd_Msg_Pub.Add;
146 App_Exception.Raise_Exception;
147
148 END IF;
149
150 EXCEPTION
151
152 WHEN others THEN
153
154 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
155
156 Fnd_Msg_Pub.Add_Exc_Msg
157 (
158 p_pkg_name => G_PKG_NAME,
159 p_procedure_name => l_api_name
160 );
161
162 RAISE Fnd_Api.G_Exc_Unexpected_Error;
163
164 END Update_Row;
165
166 ---------------------------------------------------------------------------
167
168 PROCEDURE Delete_Row
169 (
170 p_rowid IN VARCHAR2,
171 p_record_version_number IN NUMBER,
172 x_return_status OUT NOCOPY VARCHAR2
173 ) IS
174
175 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
176
177 BEGIN
178
179 x_return_status := Fnd_Api.G_Ret_Sts_Success;
180
181 DELETE igw_prop_science_codes
182 WHERE rowid = p_rowid
183 AND record_version_number = p_record_version_number;
184
185 IF SQL%NotFound THEN
186
187 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
188 Fnd_Msg_Pub.Add;
189 App_Exception.Raise_Exception;
190
191 END IF;
192
193 EXCEPTION
194
195 WHEN others THEN
196
197 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
198
199 Fnd_Msg_Pub.Add_Exc_Msg
200 (
201 p_pkg_name => G_PKG_NAME,
202 p_procedure_name => l_api_name
203 );
204
205 RAISE Fnd_Api.G_Exc_Unexpected_Error;
206
207 END Delete_Row;
208
209 ---------------------------------------------------------------------------
210
211 END Igw_Prop_Science_Codes_Tbh;