1 Package BODY Csd_Repair_Estimate_Pub AS
2 /* $Header: csdpestb.pls 120.3 2005/08/02 14:48:23 vparvath noship $ */
3 /*#
4 * This is the public interface for managing a repair estimate. It allows
5 * creation/updation of repair estimate headers and lines for a repair order.
6 * @rep:scope public
7 * @rep:product CSD
8 * @rep:displayname Repair Estimate
9 * @rep:lifecycle active
10 * @rep:category BUSINESS_ENTITY REPAIR_ESTIMATE
11 */
12
13 -- ---------------------------------------------------------
14 -- Define global variables
15 -- ---------------------------------------------------------
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIR_ESTIMATE_PUB';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdpestb.pls';
19
20 /*--------------------------------------------------*/
21 /* procedure name: create_estimate_header */
22 /* description : procedure used to create */
23 /* repair estimate headers */
24 /*--------------------------------------------------*/
25 /*#
26 * Creates a new Repair Estimate header for the given Repair order. The Estimate Header
27 * Id is generated if a unique number is not passed. Returns the Estimate Header Id.
28 * @param P_Api_Version_Number api version number
29 * @param P_Commit to decide whether to commit the transaction or not, default to false
30 * @param P_Init_Msg_List initial the message stack, default to false
31 * @param X_Return_Status return status
32 * @param X_Msg_Count return message count
33 * @param X_Msg_Data return message data
34 * @param P_Estiamte_Hdr_Rec Estimate Header record
35 * @param X_Estimate_Hdr_ID Estimate Header id of the created Estiamte Header
36 * @rep:scope public
37 * @rep:lifecycle active
38 * @rep:displayname Create Estimate Header
39 */
40 PROCEDURE CREATE_ESTIMATE_HEADER(p_api_version IN NUMBER,
41 p_commit IN VARCHAR2,
42 p_init_msg_list IN VARCHAR2,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2,
46 p_estimate_hdr_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_HDR_REC,
47 x_estimate_hdr_id OUT NOCOPY NUMBER) IS
48
49 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ESTIMATE_HEADER';
50 l_api_version CONSTANT NUMBER := 1.0;
51
52 BEGIN
53 -- Standard Start of API savepoint
54 SAVEPOINT CREATE_ESTIMATE_HEADER_PUB;
55 -- Standard call to check for call compatibility.
56 IF NOT Fnd_Api.Compatible_API_Call(l_api_version,
57 p_api_version,
58 l_api_name,
59 G_PKG_NAME)
60 THEN
61 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 IF Fnd_Api.to_Boolean(p_init_msg_list)
65 THEN
66 Fnd_Msg_Pub.initialize;
67 END IF;
68 -- Initialize API return status to success
69 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
70 --
71 -- API body
72 --
73 Csd_Repair_Estimate_Pvt.Create_estimate_header(P_Api_Version => 1.0,
74 P_Init_Msg_List => p_init_msg_list,
75 P_Commit => Fnd_Api.G_FALSE,
76 P_Validation_Level => Fnd_Api.G_VALID_LEVEL_FULL,
77 X_Return_Status => x_return_status,
78 X_Msg_Count => x_msg_count,
79 X_Msg_Data => x_msg_data,
80 p_estimate_hdr_rec => p_estimate_hdr_rec,
81 x_estimate_hdr_id => x_estimate_hdr_id);
82 --
83 -- Check return status from the above procedure call
84 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
85 THEN
86 ROLLBACK TO CREATE_ESTIMATE_HEADER_PUB;
87 RETURN;
88 END IF;
89 --
90 -- End of API body.
91 --
92 -- Standard check for p_commit
93 IF Fnd_Api.to_Boolean(p_commit)
94 THEN
95 COMMIT WORK;
96 END IF;
97 -- Standard call to get message count and if count is 1, get message info.
98 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
99 p_data => x_msg_data);
100 --
101 EXCEPTION
102 WHEN Fnd_Api.g_exc_error THEN
103 x_return_status := Fnd_Api.g_ret_sts_error;
104 ROLLBACK TO CREATE_ESTIMATE_HEADER_PUB;
105 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
106 p_data => x_msg_data);
107
108 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
109 THEN
110 Fnd_Log.STRING(Fnd_Log.level_error,
111 'csd.plsql.csd_repair_estimate_pub.create_estimate_header',
112 'EXC_ERROR[' || x_msg_data || ']');
113 END IF;
114 WHEN Fnd_Api.g_exc_unexpected_error THEN
115 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
116 ROLLBACK TO CREATE_ESTIMATE_HEADER_PUB;
117 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
118 p_data => x_msg_data);
119
120 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
121 THEN
122 Fnd_Log.STRING(Fnd_Log.level_exception,
123 'csd.plsql.csd_repair_estimate_pub.create_estimate_header',
124 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
125 END IF;
126 WHEN OTHERS THEN
127 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
128 ROLLBACK TO CREATE_ESTIMATE_HEADER_PUB;
129
130 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error)
131 THEN
132 Fnd_Msg_Pub.add_exc_msg(g_pkg_name, l_api_name);
133 END IF;
134
135 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
136 p_data => x_msg_data);
137
138 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
139 THEN
140 Fnd_Log.STRING(Fnd_Log.level_exception,
141 'csd.plsql.csd_repair_estimate_pub.create_estimate_header',
142 'SQL MEssage[' || SQLERRM || ']');
143 END IF;
144 END CREATE_ESTIMATE_HEADER;
145
146 /*--------------------------------------------------*/
147 /* procedure name: update_estimate_header */
148 /* description : procedure used to update */
149 /* repair estimate header */
150 /* */
151 /*--------------------------------------------------*/
152 /*#
153 * Updates a given estimate header record.
154 * @param P_Api_Version api version number
155 * @param P_Commit to decide whether to commit the transaction or not, default to false
156 * @param P_Init_Msg_List initial the message stack, default to false
157 * @param X_Return_Status return status
158 * @param X_Msg_Count return message count
159 * @param X_Msg_Data return message data
160 * @param P_estimate_hdr_rec estimate header record
161 * @param X_object_version_number Object version number of the updated estimate header record.
162 * @rep:scope public
163 * @rep:lifecycle active
164 * @rep:displayname Update Estimate Header
165 */
166 PROCEDURE UPDATE_ESTIMATE_HEADER(p_api_version IN NUMBER,
167 p_commit IN VARCHAR2,
168 p_init_msg_list IN VARCHAR2,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2,
172 p_estimate_hdr_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_HDR_REC,
173 x_object_version_number OUT NOCOPY NUMBER) IS
174 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ESTIMATE_HEADER';
175 l_api_version CONSTANT NUMBER := 1.0;
176
177 BEGIN
178 -- Standard Start of API savepoint
179 SAVEPOINT UPDATE_ESTIMATE_HEADER_PUB;
180 -- Standard call to check for call compatibility.
181 IF NOT Fnd_Api.Compatible_API_Call(l_api_version,
182 p_api_version,
183 l_api_name,
184 G_PKG_NAME)
185 THEN
186 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188 -- Initialize message list if p_init_msg_list is set to TRUE.
189 IF Fnd_Api.to_Boolean(p_init_msg_list)
190 THEN
191 Fnd_Msg_Pub.initialize;
192 END IF;
193 -- Initialize API return status to success
194 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
195 --
196 -- API body
197 --
198 Csd_Repair_Estimate_Pvt.Update_estimate_header(p_api_version => 1.0,
199 p_init_msg_list => p_init_msg_list,
200 p_commit => Fnd_Api.G_FALSE,
201 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
202 x_return_status => x_return_status,
203 x_msg_count => x_msg_count,
204 x_msg_data => x_msg_data,
205 p_estimate_hdr_rec => p_estimate_hdr_rec,
206 x_object_version_number => x_object_version_number);
207
208 --
209 -- Check return status from the above procedure call
210 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
211 THEN
212 ROLLBACK TO UPDATE_ESTIMATE_HEADER_PUB;
213 RETURN;
214 END IF;
215 --
216 -- End of API body.
217 --
218 -- Standard check for p_commit
219 IF Fnd_Api.to_Boolean(p_commit)
220 THEN
221 COMMIT WORK;
222 END IF;
223 -- Standard call to get message count and if count is 1, get message info.
224 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
225 p_data => x_msg_data);
226 --
227 EXCEPTION
228 WHEN Fnd_Api.g_exc_error THEN
229 x_return_status := Fnd_Api.g_ret_sts_error;
230 ROLLBACK TO UPDATE_ESTIMATE_HEADER_PUB;
231 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
232 p_data => x_msg_data);
233
234 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
235 THEN
236 Fnd_Log.STRING(Fnd_Log.level_error,
237 'csd.plsql.csd_repair_estimate_pub.update_estimate_header',
238 'EXC_ERROR[' || x_msg_data || ']');
239 END IF;
240 WHEN Fnd_Api.g_exc_unexpected_error THEN
241 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
242 ROLLBACK TO UPDATE_ESTIMATE_HEADER_PUB;
243 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
244 p_data => x_msg_data);
245
246 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
247 THEN
248 Fnd_Log.STRING(Fnd_Log.level_exception,
249 'csd.plsql.csd_repair_estimate_pub.update_estimate_header',
250 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
251 END IF;
252 WHEN OTHERS THEN
253 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
254 ROLLBACK TO UPDATE_ESTIMATE_HEADER_PUB;
255
256 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error)
257 THEN
258 Fnd_Msg_Pub.add_exc_msg(g_pkg_name, l_api_name);
259 END IF;
260
261 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
262 p_data => x_msg_data);
263
264 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
265 THEN
266 Fnd_Log.STRING(Fnd_Log.level_exception,
267 'csd.plsql.csd_repair_estimate_pub.update_estimate_header',
268 'SQL MEssage[' || SQLERRM || ']');
269 END IF;
270 END update_estimate_header;
271
272 /*--------------------------------------------------*/
273 /* procedure name: create_estimate_line */
274 /* description : procedure used to create */
275 /* repair estimate lines */
276 /*--------------------------------------------------*/
277 /*#
278 * Creates a new Repair Estimate line for the given Estimate header. The Estimate line
279 * Id is generated if a unique number is not passed. Returns the Estimate line Id.
280 * @param P_Api_Version_Number api version number
281 * @param P_Commit to decide whether to commit the transaction or not, default to false
282 * @param P_Init_Msg_List initial the message stack, default to false
283 * @param X_Return_Status return status
284 * @param X_Msg_Count return message count
285 * @param X_Msg_Data return message data
286 * @param P_Estiamte_Line_Rec Estimate Line record
287 * @param X_Estimate_Line_Id Estimate Line id of the created Estiamte Line
288 * @rep:scope public
289 * @rep:lifecycle active
290 * @rep:displayname Create Estimate Line
291 */
292 PROCEDURE CREATE_ESTIMATE_LINE(p_api_version IN NUMBER,
293 p_commit IN VARCHAR2,
294 p_init_msg_list IN VARCHAR2,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298 p_estimate_line_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_LINE_REC,
299 x_estimate_line_id OUT NOCOPY NUMBER)
300
301 IS
302
303 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ESTIMATE_LINE';
304 l_api_version CONSTANT NUMBER := 1.0;
305
306 BEGIN
307 -- Standard Start of API savepoint
308 SAVEPOINT CREATE_ESTIMATE_LINE_PUB;
309 -- Standard call to check for call compatibility.
310 IF NOT Fnd_Api.Compatible_API_Call(l_api_version,
311 p_api_version,
312 l_api_name,
313 G_PKG_NAME)
314 THEN
315 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
316 END IF;
317 -- Initialize message list if p_init_msg_list is set to TRUE.
318 IF Fnd_Api.to_Boolean(p_init_msg_list)
319 THEN
320 Fnd_Msg_Pub.initialize;
321 END IF;
322 -- Initialize API return status to success
323 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
324 --
325 -- API body
326 --
327 Csd_Repair_Estimate_Pvt.Create_estimate_Line(P_Api_Version => 1.0,
328 P_Init_Msg_List => p_init_msg_list,
329 P_Commit => Fnd_Api.G_FALSE,
330 P_Validation_Level => Fnd_Api.G_VALID_LEVEL_FULL,
331 X_Return_Status => x_return_status,
335 x_estimate_line_id => x_estimate_line_id);
332 X_Msg_Count => x_msg_count,
333 X_Msg_Data => x_msg_data,
334 p_estimate_line_rec => p_estimate_line_rec,
336 --
337 -- Check return status from the above procedure call
338 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
339 THEN
340 ROLLBACK TO CREATE_ESTIMATE_LINE_PUB;
341 RETURN;
342 END IF;
343 --
344 -- End of API body.
345 --
346 -- Standard check for p_commit
347 IF Fnd_Api.to_Boolean(p_commit)
348 THEN
349 COMMIT WORK;
350 END IF;
351 -- Standard call to get message count and if count is 1, get message info.
352 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
353 p_data => x_msg_data);
354 --
355 EXCEPTION
356 WHEN Fnd_Api.g_exc_error THEN
357 x_return_status := Fnd_Api.g_ret_sts_error;
358 ROLLBACK TO CREATE_ESTIMATE_LINE_PUB;
359 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
360 p_data => x_msg_data);
361
362 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
363 THEN
364 Fnd_Log.STRING(Fnd_Log.level_error,
365 'csd.plsql.csd_repair_estimate_pub.create_estimate_line',
366 'EXC_ERROR[' || x_msg_data || ']');
367 END IF;
368 WHEN Fnd_Api.g_exc_unexpected_error THEN
369 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
370 ROLLBACK TO CREATE_ESTIMATE_LINE_PUB;
371 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
372 p_data => x_msg_data);
373
374 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
375 THEN
376 Fnd_Log.STRING(Fnd_Log.level_exception,
377 'csd.plsql.csd_repair_estimate_pub.create_estimate_line',
378 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
379 END IF;
380 WHEN OTHERS THEN
381 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
382 ROLLBACK TO CREATE_ESTIMATE_LINE_PUB;
383
384 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error)
385 THEN
386 Fnd_Msg_Pub.add_exc_msg(g_pkg_name, l_api_name);
387 END IF;
388
389 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
390 p_data => x_msg_data);
391
392 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
393 THEN
394 Fnd_Log.STRING(Fnd_Log.level_exception,
395 'csd.plsql.csd_repair_estimate_pub.create_estimate_line',
396 'SQL MEssage[' || SQLERRM || ']');
397 END IF;
398 END create_estimate_line;
399
400 /*--------------------------------------------------*/
401 /* procedure name: update_estimate_line */
402 /* description : procedure used to update */
403 /* repair estimate lines */
404 /* */
405 /*--------------------------------------------------*/
406 /*#
407 * Updates a given estimate Line record.
408 * @param P_Api_Version api version number
409 * @param P_Commit to decide whether to commit the transaction or not, default to false
410 * @param P_Init_Msg_List initial the message stack, default to false
411 * @param X_Return_Status return status
412 * @param X_Msg_Count return message count
413 * @param X_Msg_Data return message data
414 * @param P_estimate_line_rec estimate line record
415 * @param X_object_version_number Object version number of the updated estimate line record.
416 * @rep:scope public
417 * @rep:lifecycle active
418 * @rep:displayname Update Estimate Line
419 */
420 PROCEDURE UPDATE_ESTIMATE_LINE(p_api_version IN NUMBER,
421 p_init_msg_list IN VARCHAR2,
422 p_commit IN VARCHAR2,
423 x_return_status OUT NOCOPY VARCHAR2,
424 x_msg_count OUT NOCOPY NUMBER,
425 x_msg_data OUT NOCOPY VARCHAR2,
426 p_estimate_line_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_LINE_REC,
427 x_object_version_number OUT NOCOPY NUMBER)
428
429 IS
430
431 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ESTIMATE_LINE';
432 l_api_version CONSTANT NUMBER := 1.0;
433
434 BEGIN
435 -- Standard Start of API savepoint
436 SAVEPOINT UPDATE_ESTIMATE_LINE_PUB;
437 -- Standard call to check for call compatibility.
438 IF NOT Fnd_Api.Compatible_API_Call(l_api_version,
439 p_api_version,
440 l_api_name,
441 G_PKG_NAME)
442 THEN
443 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
444 END IF;
445 -- Initialize message list if p_init_msg_list is set to TRUE.
446 IF Fnd_Api.to_Boolean(p_init_msg_list)
447 THEN
448 Fnd_Msg_Pub.initialize;
449 END IF;
450 -- Initialize API return status to success
451 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
452 --
453 -- API body
454 --
455 Csd_Repair_Estimate_Pvt.Update_estimate_Line(P_Api_Version => 1.0,
456 P_Init_Msg_List => p_init_msg_list,
457 P_Commit => Fnd_Api.G_FALSE,
458 P_Validation_Level => Fnd_Api.G_VALID_LEVEL_FULL,
459 X_Return_Status => x_return_status,
460 X_Msg_Count => x_msg_count,
461 X_Msg_Data => x_msg_data,
462 p_estimate_line_rec => p_estimate_line_rec,
463 x_object_version_number => x_object_version_number);
464 --
465 -- Check return status from the above procedure call
466 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
467 THEN
468 ROLLBACK TO UPDATE_ESTIMATE_LINE_PUB;
469 RETURN;
470 END IF;
471 --
472 -- End of API body.
473 --
474 -- Standard check for p_commit
475 IF Fnd_Api.to_Boolean(p_commit)
476 THEN
477 COMMIT WORK;
478 END IF;
479 -- Standard call to get message count and if count is 1, get message info.
480 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
481 p_data => x_msg_data);
482 --
483 EXCEPTION
484 WHEN Fnd_Api.g_exc_error THEN
485 x_return_status := Fnd_Api.g_ret_sts_error;
486 ROLLBACK TO UPDATE_ESTIMATE_LINE_PUB;
487 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
491 THEN
492 Fnd_Log.STRING(Fnd_Log.level_error,
493 'csd.plsql.csd_repair_estimate_pub.update_estimate_line',
494 'EXC_ERROR[' || x_msg_data || ']');
495 END IF;
496 WHEN Fnd_Api.g_exc_unexpected_error THEN
497 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
498 ROLLBACK TO UPDATE_ESTIMATE_LINE_PUB;
499 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
500 p_data => x_msg_data);
501
502 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
503 THEN
504 Fnd_Log.STRING(Fnd_Log.level_exception,
505 'csd.plsql.csd_repair_estimate_pub.update_estimate_line',
506 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
507 END IF;
508 WHEN OTHERS THEN
509 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
510 ROLLBACK TO UPDATE_ESTIMATE_LINE_PUB;
511
512 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error)
513 THEN
514 Fnd_Msg_Pub.add_exc_msg(g_pkg_name, l_api_name);
515 END IF;
516
517 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
518 p_data => x_msg_data);
519
520 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
521 THEN
522 Fnd_Log.STRING(Fnd_Log.level_exception,
523 'csd.plsql.csd_repair_estimate_pub.update_estimate_line',
524 'SQL MEssage[' || SQLERRM || ']');
525 END IF;
526 END update_estimate_line;
527
528 END Csd_Repair_Estimate_Pub;