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