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