[Home] [Help]
PACKAGE BODY: APPS.IES_QUESTIONS_PVT
Source
1 PACKAGE BODY IES_QUESTIONS_PVT AS
2 /* $Header: iesvieqb.pls 115.15 2003/06/06 20:16:17 prkotha ship $ */
3
4 PROCEDURE Insert_Question
5 ( p_api_version IN NUMBER := 1 ,
6 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
7 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
8 p_validation_level IN NUMBER := 1 ,
9 x_return_status OUT NOCOPY VARCHAR2 ,
10 x_msg_count OUT NOCOPY NUMBER ,
11 x_msg_data OUT NOCOPY VARCHAR2 ,
12 p_created_by IN NUMBER ,
13 p_panel_id IN NUMBER ,
14 p_lookup_id IN NUMBER ,
15 p_node_name IN VARCHAR2 ,
16 p_node_uid IN VARCHAR2 ,
17 p_active_status IN NUMBER ,
18 x_question_id OUT NOCOPY NUMBER
19 ) IS
20 BEGIN
21 Insert_Question( p_api_version ,
22 p_init_msg_list ,
23 p_commit ,
24 p_validation_level ,
25 x_return_status ,
26 x_msg_count ,
27 x_msg_data ,
28 p_created_by ,
29 p_panel_id ,
30 null ,
31 p_lookup_id ,
32 p_node_name ,
33 p_node_uid ,
34 null ,
35 p_active_status ,
36 x_question_id );
37 END Insert_Question;
38
39 PROCEDURE Insert_Question
40 ( p_api_version IN NUMBER := 1 ,
41 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
42 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
43 p_validation_level IN NUMBER := 1 ,
44 x_return_status OUT NOCOPY VARCHAR2 ,
45 x_msg_count OUT NOCOPY NUMBER ,
46 x_msg_data OUT NOCOPY VARCHAR2 ,
47 p_created_by IN NUMBER ,
48 p_panel_id IN NUMBER ,
49 p_question_type_id IN NUMBER ,
50 p_lookup_id IN NUMBER ,
51 p_node_name IN VARCHAR2 ,
52 p_node_uid IN VARCHAR2 ,
53 p_question_label IN VARCHAR2 ,
54 p_active_status IN NUMBER ,
55 x_question_id OUT NOCOPY NUMBER
56 ) IS
57 BEGIN
58 Insert_Question( p_api_version ,
59 p_init_msg_list ,
60 p_commit ,
61 p_validation_level ,
62 x_return_status ,
63 x_msg_count ,
64 x_msg_data ,
65 p_created_by ,
66 p_panel_id ,
67 p_question_type_id ,
68 p_lookup_id ,
69 p_node_name ,
70 p_node_uid ,
71 p_question_label ,
72 p_active_status ,
73 null ,
74 x_question_id );
75 END Insert_Question;
76
77 PROCEDURE Insert_Question
78 ( p_api_version IN NUMBER := 1 ,
79 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
80 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
81 p_validation_level IN NUMBER := 1 ,
82 x_return_status OUT NOCOPY VARCHAR2 ,
83 x_msg_count OUT NOCOPY NUMBER ,
84 x_msg_data OUT NOCOPY VARCHAR2 ,
85 p_created_by IN NUMBER ,
86 p_panel_id IN NUMBER ,
87 p_question_type_id IN NUMBER ,
88 p_lookup_id IN NUMBER ,
89 p_node_name IN VARCHAR2 ,
90 p_node_uid IN VARCHAR2 ,
91 p_question_label IN VARCHAR2 ,
92 p_active_status IN NUMBER ,
93 p_question_order IN NUMBER ,
94 x_question_id OUT NOCOPY NUMBER
95 ) IS
96 seqval NUMBER;
97 insertstmt varchar2(4000);
98 BEGIN
99 -- Standard Start of API savepoint
100 SAVEPOINT Insert_Question_PVT;
101
102 EXECUTE immediate 'select ies_questions_s.nextval from dual' INTO seqval;
103
104 insertstmt := 'INSERT INTO ies_questions ( question_id ,
105 created_by ,
106 creation_date ,
107 panel_id ,
108 question_type_id ,
109 lookup_id ,
110 node_name ,
111 node_uid ,
112 question_label ,
113 question_order ,
114 active_status )
115 VALUES ( :1 ,
116 :2 ,
117 :3 ,
118 :4 ,
119 :5 ,
120 :6 ,
121 :7 ,
122 :8 ,
123 :9 ,
124 :10 ,
125 :11 )
126 RETURNING question_id INTO :12';
127
128 execute immediate insertStmt using seqval,
129 p_created_by ,
130 sysdate ,
131 p_panel_id ,
132 p_question_type_id ,
133 p_lookup_id ,
134 p_node_name ,
135 p_node_uid ,
136 p_question_label ,
137 p_question_order ,
138 p_active_status
139 RETURNING INTO x_question_id;
140 EXCEPTION
141 WHEN OTHERS THEN
142 ROLLBACK TO Insert_Question_PVT;
143 x_return_status := 'E';
144 x_msg_data := 'Error ' || TO_CHAR(SQLCODE) ||':'||SQLERRM;
145 END Insert_Question;
146
147 PROCEDURE Update_Question
148 ( p_api_version IN NUMBER := 1 ,
149 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
150 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
151 p_validation_level IN NUMBER := 1 ,
152 x_return_status OUT NOCOPY VARCHAR2 ,
153 x_msg_count OUT NOCOPY NUMBER ,
154 x_msg_data OUT NOCOPY VARCHAR2 ,
155 p_question_id IN NUMBER ,
156 p_lookup_id IN NUMBER ,
157 p_last_updated_by IN NUMBER ,
158 p_node_name IN VARCHAR2 ,
159 p_node_uid IN VARCHAR2 ,
160 p_active_status IN NUMBER ,
161 x_question_id OUT NOCOPY NUMBER
162 ) IS
163 BEGIN
164 Update_Question ( p_api_version ,
165 p_init_msg_list ,
166 p_commit ,
167 p_validation_level ,
168 x_return_status ,
169 x_msg_count ,
170 x_msg_data ,
171 p_question_id ,
172 null ,
173 p_lookup_id ,
174 p_last_updated_by ,
175 p_node_name ,
176 p_node_uid ,
177 null ,
178 p_active_status ,
179 x_question_id );
180 END Update_Question;
181
182 PROCEDURE Update_Question
183 ( p_api_version IN NUMBER := 1 ,
184 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
185 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
186 p_validation_level IN NUMBER := 1 ,
187 x_return_status OUT NOCOPY VARCHAR2 ,
188 x_msg_count OUT NOCOPY NUMBER ,
189 x_msg_data OUT NOCOPY VARCHAR2 ,
190 p_question_id IN NUMBER ,
191 p_question_type_id IN NUMBER ,
192 p_lookup_id IN NUMBER ,
193 p_last_updated_by IN NUMBER ,
194 p_node_name IN VARCHAR2 ,
195 p_node_uid IN VARCHAR2 ,
196 p_question_label IN VARCHAR2 ,
197 p_active_status IN NUMBER ,
198 x_question_id OUT NOCOPY NUMBER
199 ) IS
200 BEGIN
201 Update_Question ( p_api_version ,
202 p_init_msg_list ,
203 p_commit ,
204 p_validation_level ,
205 x_return_status ,
206 x_msg_count ,
207 x_msg_data ,
208 p_question_id ,
209 p_question_type_id ,
210 p_lookup_id ,
211 p_last_updated_by ,
212 p_node_name ,
213 p_node_uid ,
214 p_question_label ,
215 p_active_status ,
216 null ,
217 x_question_id );
218 END Update_Question;
219
220 PROCEDURE Update_Question
221 ( p_api_version IN NUMBER := 1 ,
222 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
223 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
224 p_validation_level IN NUMBER := 1 ,
225 x_return_status OUT NOCOPY VARCHAR2 ,
226 x_msg_count OUT NOCOPY NUMBER ,
227 x_msg_data OUT NOCOPY VARCHAR2 ,
228 p_question_id IN NUMBER ,
229 p_question_type_id IN NUMBER ,
230 p_lookup_id IN NUMBER ,
231 p_last_updated_by IN NUMBER ,
232 p_node_name IN VARCHAR2 ,
233 p_node_uid IN VARCHAR2 ,
234 p_question_label IN VARCHAR2 ,
235 p_active_status IN NUMBER ,
236 p_question_order IN NUMBER ,
237 x_question_id OUT NOCOPY NUMBER
238 ) IS
239 updateStmt varchar2(2000);
240 BEGIN
241 -- Standard Start of API savepoint
242 SAVEPOINT Update_Question_PVT;
243 updateStmt := 'UPDATE ies_questions SET last_updated_by = :1 ,
244 last_update_date = :2 ,
245 lookup_id = :3 ,
246 node_name = :4 ,
247 question_id = :5 ,
248 question_type_id = :6 ,
249 node_uid = :7 ,
250 question_label = :8 ,
251 active_status = :9 ,
252 question_order = :10
253 WHERE question_id = :11
254 RETURNING question_id INTO :12';
255
256 execute immediate updateStmt using p_last_updated_by ,
257 sysdate ,
258 p_lookup_id ,
259 p_node_name ,
260 p_question_id ,
261 p_question_type_id ,
262 p_node_uid ,
263 p_question_label ,
264 p_active_status ,
265 p_question_order,
266 p_question_id
267 RETURNING INTO x_question_id;
268 EXCEPTION
269 WHEN OTHERS THEN
270 ROLLBACK TO Update_Question_PVT;
271 x_return_status := 'E';
272 x_msg_data := 'Error ' || TO_CHAR(SQLCODE) ||':'||SQLERRM;
273 END Update_Question;
274 END IES_QUESTIONS_PVT;