[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_QUESTIONS_PVT
Source
1 PACKAGE BODY Igw_Prop_Questions_Pvt AS
2 --$Header: igwvpqeb.pls 115.5 2002/11/15 00:44:01 ashkumar ship $
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_PROP_QUESTIONS_PVT';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Check_Lock
11 (
12 p_rowid IN VARCHAR2,
13 p_record_version_number IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2
15 ) IS
16
17 l_api_name CONSTANT VARCHAR2(30) := 'Check_Lock';
18
19 l_locked VARCHAR2(1);
20
21 BEGIN
22
23 /*
24 ** Initialize
25 */
26
27 x_return_status := Fnd_Api.G_Ret_Sts_Success;
28
29 IF p_rowid IS NOT NULL AND p_record_version_number IS NOT NULL THEN
30
31 SELECT 'N'
32 INTO l_locked
33 FROM igw_prop_questions
34 WHERE rowid = p_rowid
35 AND record_version_number = p_record_version_number;
36
37 END IF;
38
39 EXCEPTION
40
41 WHEN no_data_found THEN
42
43 x_return_status := Fnd_Api.G_Ret_Sts_Error;
44 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
45 Fnd_Msg_Pub.Add;
46
47 WHEN others THEN
48
49 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
50
51 Fnd_Msg_Pub.Add_Exc_Msg
52 (
53 p_pkg_name => G_PKG_NAME,
54 p_procedure_name => l_api_name
55 );
56
57 RAISE Fnd_Api.G_Exc_Unexpected_Error;
58
59 END Check_Lock;
60
61 ---------------------------------------------------------------------------
62
63 PROCEDURE Explanation_Or_Date_Required
64 (
65 p_question_number IN VARCHAR2,
66 p_answer IN VARCHAR2,
67 p_explanation IN VARCHAR2,
68 p_review_date IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2
70 ) IS
71
72 l_api_name CONSTANT VARCHAR2(30) := 'Explanation_Or_Date_Required';
73
74 l_explanation_for_yes_flag VARCHAR2(1);
75 l_explanation_for_no_flag VARCHAR2(1);
76 l_date_for_yes_flag VARCHAR2(1);
77 l_date_for_no_flag VARCHAR2(1);
78
79 BEGIN
80
81 SELECT
82 explanation_for_yes_flag,
83 explanation_for_no_flag,
84 date_for_yes_flag,
85 date_for_no_flag
86 INTO
87 l_explanation_for_yes_flag,
88 l_explanation_for_no_flag,
89 l_date_for_yes_flag,
90 l_date_for_no_flag
91 FROM
92 igw_questions
93 WHERE
94 question_number = p_question_number;
95
96 IF p_explanation IS NULL THEN
97
98 IF (p_answer = '1' AND l_explanation_for_yes_flag = 'Y') OR
99 (p_answer = '2' AND l_explanation_for_no_flag = 'Y') THEN
100
101 x_return_status:= Fnd_Api.G_Ret_Sts_Error;
102 Fnd_Message.Set_Name('IGW','IGW_EXPLANATION_REQUIRED');
103 Fnd_Msg_Pub.Add;
104
105 END IF;
106
107 END IF;
108
109 IF p_review_date IS NULL THEN
110
111 IF (p_answer = '1' AND l_date_for_yes_flag = 'Y') OR
112 (p_answer = '2' AND l_date_for_no_flag = 'Y') THEN
113
114 x_return_status:= Fnd_Api.G_Ret_Sts_Error;
115 Fnd_Message.Set_Name('IGW','IGW_DATE_REQUIRED');
116 Fnd_Msg_Pub.Add;
117
118 END IF;
119
120 END IF;
121
122 EXCEPTION
123
124 WHEN no_data_found THEN
125
126 x_return_status := Fnd_Api.G_Ret_Sts_Error;
127 Fnd_Message.Set_Name('IGW','IGW_SS_QUESTION_INVALID');
128 Fnd_Msg_Pub.Add;
129
130 WHEN others THEN
131
132 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
133
134 Fnd_Msg_Pub.Add_Exc_Msg
135 (
136 p_pkg_name => G_PKG_NAME,
137 p_procedure_name => l_api_name
138 );
139
140 RAISE Fnd_Api.G_Exc_Unexpected_Error;
141
142 END Explanation_Or_Date_Required;
143
144 ---------------------------------------------------------------------------
145
146 PROCEDURE Populate_Prop_Questions( p_proposal_id IN NUMBER ) IS
147 BEGIN
148
149 INSERT INTO igw_prop_questions
150 (
151 proposal_id,
152 question_number,
153 answer,
154 last_update_date,
155 last_updated_by,
156 creation_date,
157 created_by,
158 last_update_login,
159 record_version_number
160 )
161 SELECT
162 p_proposal_id,
163 question_number,
164 '3',
165 SYSDATE,
166 Fnd_Global.User_Id,
167 SYSDATE,
168 Fnd_Global.User_Id,
169 Fnd_Global.Login_Id,
170 1
171 FROM
172 igw_questions
173 WHERE
174 applies_to = 'P' AND
175 SYSDATE >= start_date_active AND
176 (SYSDATE <= end_date_active OR end_date_active IS NULL) AND
177 question_number NOT IN
178 ( SELECT question_number
179 FROM igw_prop_questions
180 WHERE proposal_id = p_proposal_id );
181
182 COMMIT;
183
184 END Populate_Prop_Questions;
185
186 ---------------------------------------------------------------------------
187
188 PROCEDURE Update_Prop_Question
189 (
190 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
191 p_validate_only IN VARCHAR2 := Fnd_Api.G_False,
192 p_commit IN VARCHAR2 := Fnd_Api.G_False,
193 p_rowid IN VARCHAR2,
194 p_record_version_number IN NUMBER,
195 p_proposal_id IN NUMBER,
196 p_proposal_number IN VARCHAR2,
197 p_question_number IN VARCHAR2,
198 p_answer IN VARCHAR2,
199 p_explanation IN VARCHAR2,
200 p_review_date IN DATE,
201 x_return_status OUT NOCOPY VARCHAR2,
202 x_msg_count OUT NOCOPY NUMBER,
203 x_msg_data OUT NOCOPY VARCHAR2
204 ) IS
205
206 l_api_name CONSTANT VARCHAR2(30) := 'Update_Prop_Question';
207
208 l_proposal_id NUMBER := p_proposal_id;
209
210 l_return_status VARCHAR2(1);
211
212 BEGIN
213
214 /*
215 ** Establish Savepoint for Rollback
216 */
217
218 SAVEPOINT Update_Prop_Question_Pvt;
219
220
221 /*
222 ** Initialize
223 */
224
225 x_return_status := Fnd_Api.G_Ret_Sts_Success;
226
227 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
228
229 Fnd_Msg_Pub.Initialize;
230
231 END IF;
232
233
234 /*
235 ** Get Ids from Values if Ids not passed
236 */
237
238 IF p_proposal_id IS NULL THEN
239
240 Igw_Utils.Get_Proposal_Id
241 (
242 p_context_field => 'PROPOSAL_ID',
243 p_check_id_flag => 'Y',
244 p_proposal_number => p_proposal_number,
245 p_proposal_id => p_proposal_id,
246 x_proposal_id => l_proposal_id,
247 x_return_status => l_return_status
248 );
249
250 END IF;
251
252 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
253
254 RAISE Fnd_Api.G_Exc_Error;
255
256 END IF;
257
258
259 /*
260 ** Check Modify Rights
261 */
262 /*
263
264 IF Igw_Security.Allow_Modify
265 (
266 p_function_name => 'PROPOSAL',
267 p_proposal_id => l_proposal_id,
268 p_user_id => Fnd_Global.User_Id
269 )
270 = 'N' THEN
271
272 x_return_status := Fnd_Api.G_Ret_Sts_Error;
273 Fnd_Message.Set_Name('IGW','IGW_SS_SEC_NO_MODIFY_RIGHTS');
274 Fnd_Msg_Pub.Add;
275 RAISE Fnd_Api.G_Exc_Error;
276
277 END IF;
278
279 */
280
281 /*
282 ** Check Lock before proceeding
283 */
284
285 Check_Lock
286 (
287 p_rowid => p_rowid,
288 p_record_version_number => p_record_version_number,
289 x_return_status => l_return_status
290 );
291
292
293 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
294
295 RAISE Fnd_Api.G_Exc_Error;
296
297 END IF;
298
299 Explanation_Or_Date_Required
300 (
301 p_question_number => p_question_number,
302 p_answer => p_answer,
303 p_explanation => p_explanation,
304 p_review_date => p_review_date,
305 x_return_status => x_return_status
306 );
307
308 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
309
310 RAISE Fnd_Api.G_Exc_Error;
311
312 END IF;
313
314 /*
315 ** Discontinue processing if API invoked in validation mode
316 */
317
318 IF Fnd_Api.To_Boolean(p_validate_only) THEN
319
320 RETURN;
321
322 END IF;
323
324
325 /*
326 ** Invoke Table Handler to Update data
327 */
328
329 Igw_Prop_Questions_Tbh.Update_Row
330 (
331 p_rowid => p_rowid,
332 p_record_version_number => p_record_version_number,
333 p_proposal_id => l_proposal_id,
334 p_question_number => p_question_number,
335 p_answer => p_answer,
336 p_explanation => p_explanation,
337 p_review_date => p_review_date,
338 x_return_status => l_return_status
339 );
340
341
342 /*
343 ** Commit data if API invoked in commit mode
344 */
345
346 IF Fnd_Api.To_Boolean(p_commit) THEN
347
348 COMMIT;
349
350 END IF;
351
352
353 EXCEPTION
354
355 WHEN Fnd_Api.G_Exc_Error THEN
356
357 ROLLBACK TO Update_Prop_Question_Pvt;
358
359 x_return_status := Fnd_Api.G_Ret_Sts_Error;
360
361 Fnd_Msg_Pub.Count_And_Get
362 (
363 p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366
367 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
368
369 ROLLBACK TO Update_Prop_Question_Pvt;
370
371 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
372
373 Fnd_Msg_Pub.Count_And_Get
374 (
375 p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378
379 WHEN others THEN
380
381 ROLLBACK TO Update_Prop_Question_Pvt;
382
383 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
384
385 Fnd_Msg_Pub.Add_Exc_Msg
386 (
387 p_pkg_name => G_PKG_NAME,
388 p_procedure_name => l_api_name
389 );
390
391 Fnd_Msg_Pub.Count_And_Get
392 (
393 p_count => x_msg_count,
394 p_data => x_msg_data
395 );
396
397 END Update_Prop_Question;
398
399 ---------------------------------------------------------------------------
400
401 END Igw_Prop_Questions_Pvt;