[Home] [Help]
PACKAGE BODY: APPS.ASO_SUP_CAPTURE_DATA_PKG
Source
1 PACKAGE BODY ASO_SUP_CAPTURE_DATA_PKG AS
2 /* $Header: asospdcb.pls 120.1 2005/06/29 16:05:03 appldev ship $ */
3
4 G_PKG_NAME VARCHAR2(50):= 'ASO_SUP_CAPTURE_DATA_PKG';
5
6 PROCEDURE create_template_instance(
7 p_template_id IN NUMBER,
8 p_owner_table_name IN VARCHAR2,
9 p_owner_table_id IN NUMBER,
10 p_created_by IN NUMBER,
11 p_last_updated_by IN NUMBER,
12 p_last_update_login IN NUMBER,
13 p_commit IN VARCHAR2 := FND_API.G_FALSE,
14 x_template_instance_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
15 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
16 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
17 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
18
19 l_template_instance_id NUMBER;
20 l_api_name varchar2(100) := 'create_temp_instance';
21
22 BEGIN
23
24 SAVEPOINT create_temp_instance_int;
25
26 --BEGIN
27 SELECT aso_sup_tmpl_instance_s.NEXTVAL
28 INTO l_template_instance_id
29 FROM DUAL;
30
31 x_template_instance_id := l_template_instance_id;
32
33 INSERT INTO aso_sup_tmpl_instance
34 ( TEMPLATE_INSTANCE_ID,
35 TEMPLATE_ID,
36 OWNER_TABLE_NAME,
37 OWNER_TABLE_ID,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN,
41 CREATION_DATE,
42 CREATED_BY )
43 VALUES
44 ( l_template_instance_id,
45 p_template_id,
46 p_owner_table_name,
47 p_owner_table_id,
48 SYSDATE, p_last_updated_by, p_last_update_login, SYSDATE, p_created_by);
49
50 --COMMIT;
51
52 --EXCEPTION
53 --WHEN OTHERS THEN
54 --x_template_instance_id := 0;
55 --END;
56
57 IF fnd_api.to_boolean (p_commit) THEN
58 COMMIT WORK;
59 END IF;
60
61 EXCEPTION
62
63 WHEN OTHERS THEN
64 x_template_instance_id := 0;
65 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
66 P_API_NAME => L_API_NAME
67 ,P_PKG_NAME => G_PKG_NAME
68 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
69 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
70 ,X_MSG_COUNT => X_MSG_COUNT
71 ,X_MSG_DATA => X_MSG_DATA
72 ,X_RETURN_STATUS => X_RETURN_STATUS);
73
74
75 END create_template_instance;
76
77 PROCEDURE update_data (
78 p_template_instance_id IN NUMBER,
79 p_sect_comp_map_id IN NUMBER,
80 p_created_by IN NUMBER,
81 p_last_updated_by IN NUMBER,
82 p_last_update_login IN NUMBER,
83 p_response_id IN NUMBER,
84 p_response_value IN VARCHAR2,
85 p_multiple_response_flag IN VARCHAR2,
86 p_commit IN VARCHAR2 := FND_API.G_FALSE,
87 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
88 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
89 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
90
91
92 l_exists VARCHAR2(2);
93 l_api_name varchar2(50):= 'update_data';
94 BEGIN
95
96 SAVEPOINT update_data_int;
97
98 BEGIN
99 SELECT 'Y'
100 INTO l_exists
101 FROM aso_sup_instance_value
102 WHERE template_instance_id = p_template_instance_id
103 AND sect_comp_map_id = p_sect_comp_map_id
104 AND ROWNUM = 1 ;
105 EXCEPTION WHEN NO_DATA_FOUND THEN
106 l_exists := 'N';
107 END;
108
109 BEGIN
110 IF ( p_multiple_response_flag = 'Y') THEN l_exists := 'N'; END IF;
111 --IF ( p_multiple_response_flag = 'X') THEN l_exists := 'X'; END IF;
112 END;
113 IF (l_exists = 'Y') THEN
114 BEGIN
115 UPDATE aso_sup_instance_value
116 SET created_by = p_created_by,
117 last_updated_by = p_last_updated_by,
118 last_update_login = p_last_update_login,
119 response_id = p_response_id,
120 value = p_response_value
121 WHERE template_instance_id = p_template_instance_id
122 AND sect_comp_map_id = p_sect_comp_map_id;
123 END;
124 ELSE
125 IF (l_exists = 'N') THEN
126 BEGIN
127 INSERT INTO aso_sup_instance_value
128 (instance_value_id, template_instance_id,
129 sect_comp_map_id, created_by, last_updated_by,
130 last_update_login, response_id, value,
131 last_update_date, creation_date)
132 VALUES
133 (aso_sup_inst_value_s.nextval, p_template_instance_id,
134 p_sect_comp_map_id, p_created_by, p_last_updated_by,
135 p_last_update_login, p_response_id, p_response_value,
136 SYSDATE, SYSDATE);
137 END;
138 END IF;
139 END IF;
140 --COMMIT;
141 IF fnd_api.to_boolean (p_commit) THEN
142 COMMIT WORK;
143 END IF;
144
145 EXCEPTION
146
147 WHEN OTHERS THEN
148 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
149 P_API_NAME => L_API_NAME
150 ,P_PKG_NAME => G_PKG_NAME
151 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
152 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
153 ,X_MSG_COUNT => X_MSG_COUNT
154 ,X_MSG_DATA => X_MSG_DATA
155 ,X_RETURN_STATUS => X_RETURN_STATUS);
156
157
158 END update_data;
159
160
161 PROCEDURE delete_data (
162 p_template_instance_id IN NUMBER,
163 p_sect_comp_map_id IN NUMBER,
164 p_commit IN VARCHAR2 := FND_API.G_FALSE,
165 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
166 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
167 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
168 l_api_name varchar2(50) := 'delete_data';
169
170 BEGIN
171
172 SAVEPOINT delete_data_int;
173
174 BEGIN
175 DELETE FROM aso_sup_instance_value
176 WHERE template_instance_id = p_template_instance_id
177 AND sect_comp_map_id = p_sect_comp_map_id;
178 END;
179 --COMMIT;
180 IF fnd_api.to_boolean (p_commit) THEN
181 COMMIT WORK;
182 END IF;
183
184 EXCEPTION
185
186 WHEN OTHERS THEN
187 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
188 P_API_NAME => L_API_NAME
189 ,P_PKG_NAME => G_PKG_NAME
190 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
191 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
192 ,X_MSG_COUNT => X_MSG_COUNT
193 ,X_MSG_DATA => X_MSG_DATA
194 ,X_RETURN_STATUS => X_RETURN_STATUS);
195
196
197 END delete_data;
198
199
200 Procedure create_template_instance (
201 P_VERSION_NUMBER IN NUMBER,
202 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
203 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
204 P_Template_id IN NUMBER := FND_API.G_MISS_NUM,
205 P_comp_sect_map_id IN JTF_NUMBER_TABLE,
206 P_response_value IN JTF_VARCHAR2_TABLE_2000,
207 P_response_id IN JTF_NUMBER_TABLE,
208 P_mult_ans_flag IN JTF_VARCHAR2_TABLE_100,
209 P_owner_table_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
210 P_owner_table_id IN NUMBER := FND_API.G_MISS_NUM,
211 X_template_instance_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
212 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
213 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
214 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
215
216 l_compSectMapId aso_sup_instance_value.SECT_COMP_MAP_ID%TYPE;
217 l_responseValue aso_sup_instance_value.VALUE%TYPE;
218 l_responseId aso_sup_instance_value.RESPONSE_ID%TYPE;
219 l_multAnsFlag VARCHAR2(1);
220 l_ownerTableName ASO_SUP_TMPL_INSTANCE.OWNER_TABLE_NAME%TYPE;
221 l_ownerTableId ASO_SUP_TMPL_INSTANCE.OWNER_TABLE_ID%TYPE ;
222 l_templateInstance ASO_SUP_TMPL_INSTANCE.TEMPLATE_INSTANCE_ID%TYPE;
223 l_templateId ASO_SUP_TMPL_INSTANCE.TEMPLATE_ID%TYPE ;
224 l_createdBy NUMBER := to_number( fnd_profile.value('USER_ID') );
225 l_lastUpdatedBy NUMBER :=l_createdBy;
226 l_lastUpdateLogin NUMBER := to_number( fnd_profile.value('LOGIN_ID') );
227 lx_returnStatus VARCHAR2(50);
228 lx_msgCount NUMBER;
229 lx_msgData VARCHAR2(2000);
230 lx_templateInstanceId NUMBER;
231 l_temp NUMBER :=0;
232
233 l_compSectMapIds JTF_NUMBER_TABLE := p_comp_sect_map_id;
234 l_responseIds JTF_NUMBER_TABLE := P_response_id ;
235 l_responseValues JTF_VARCHAR2_TABLE_2000 :=P_response_value;
236 l_multAnsFlags JTF_VARCHAR2_TABLE_100 := P_mult_ans_flag;
237 l_api_version NUMBER := 1.0;
238 l_api_name VARCHAR2(50) := 'create_template_instance';
239
240 -- hyang: for bug 2710767
241 lx_status VARCHAR2(1);
242 l_header_id NUMBER;
243
244 Cursor get_header_id (p_qte_line_id NUMBER ) IS
245 SELECT quote_header_id
246 FROM aso_quote_lines_all
247 WHERE quote_line_id = p_qte_line_id;
248
249
250 L_RETURN_STATUS VARCHAR2(1);
251
252 BEGIN
253
254 -- Enable debug message
255 Aso_Quote_Util_Pvt.Enable_Debug_Pvt;
256 -- Standard Start of API savepoint
257 -- hyang: for bug 2710767, added pkg_type suffix to the savepoint.
258 SAVEPOINT create_template_instance_int;
259
260 -- Standard call to check for call compatibility.
261 IF NOT FND_API.Compatible_API_Call ( l_api_version,
262 p_version_number,
263 l_api_name,
264 G_PKG_NAME)
265 THEN
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
267 END IF;
268
269
270 -- Initialize message list if p_init_msg_list is set to TRUE.
271 IF FND_API.to_Boolean( p_init_msg_list ) THEN
272 FND_MSG_PUB.initialize;
273 END IF;
274
275 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
276 G_PKG_NAME || l_api_name||'start');
277
278
279 -- Initialize API return status to SUCCESS
280 x_return_status := FND_API.G_RET_STS_SUCCESS;
281
282 --
283 -- API body
284 --
285
286 -- ******************************************************************
287 -- Validate Environment
288 -- ******************************************************************
289 IF FND_GLOBAL.User_Id IS NULL THEN
290 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)THEN
291 FND_MESSAGE.Set_Name(' + appShortName +',
292 'UT_CANNOT_GET_PROFILE_VALUE');
293 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
294 FND_MSG_PUB.ADD;
295 END IF;
296 RAISE FND_API.G_EXC_ERROR;
297 END IF;
298
299
300 IF P_owner_table_id IS NOT NULL THEN
301
302 -- hyang: for bug 2710767
303 -- sales supp enhancement changes bug 2940126
304 IF p_owner_table_name = 'ASO_QUOTE_HEADERS' THEN
305 l_header_id := P_owner_table_id;
306 ELSIF p_owner_table_name = 'ASO_QUOTE_LINES' THEN
307 OPEN get_header_id(P_owner_table_id);
308 FETCH get_header_id INTO l_header_id;
309 CLOSE get_header_id;
310 END IF;
311
312 -- Check for lock if the table name is for Quoting
313 -- bug 3154810
314 IF ( (p_owner_table_name = 'ASO_QUOTE_HEADERS')
315 OR (p_owner_table_name = 'ASO_QUOTE_LINES') ) THEN
316
317 ASO_CONC_REQ_INT.Lock_Exists(
318 p_quote_header_id => l_header_id,
319 x_status => lx_status);
320
321 IF (lx_status = FND_API.G_TRUE) THEN
322 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
323 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
324 FND_MSG_PUB.ADD;
325 END IF;
326 RAISE FND_API.G_EXC_ERROR;
327 END IF;
328 END IF; -- table name check
329
330 l_ownerTableName := P_owner_table_name;
331 l_ownerTableId := P_owner_table_id;
332 l_templateId := p_template_id;
333
334 CREATE_TEMPLATE_INSTANCE(
335 p_template_id => l_templateId,
336 p_owner_table_name => l_ownerTableName,
337 p_owner_table_id => l_ownerTableId,
338 p_created_by => l_createdBy,
339 p_last_updated_by => l_lastUpdatedBy,
340 p_last_update_login => l_lastUpdateLogin,
341 p_commit => p_commit,
342 x_template_instance_id => lx_templateInstanceId,
343 X_RETURN_STATUS => L_RETURN_STATUS,
344 X_MSG_COUNT => X_MSG_COUNT,
345 X_MSG_DATA => X_MSG_DATA
346 );
347 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
348 x_return_status := l_return_status;
349 RAISE FND_API.G_EXC_ERROR;
350 END IF;
351
352 X_template_instance_id := lx_templateInstanceId;
353 l_templateInstance := lx_templateInstanceId;
354 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
355 'x_template_instance_id '|| l_templateInstance);
356
357
358 END IF;
359
360
361 FOR l_sectLoop IN 1..l_compSectMapIds.COUNT LOOP
362 IF (l_multAnsFlags(l_sectLoop) <> 'Y') THEN
363 update_data (
364 p_template_instance_id => l_templateInstance,
365 p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
366 p_created_by => l_createdBy,
367 p_last_updated_by => l_lastUpdatedBy,
368 p_last_update_login =>l_lastUpdateLogin,
369 p_response_id =>l_responseIds(l_sectLoop),
370 p_response_value => l_responseValues(l_sectLoop),
371 p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
372 p_commit => p_commit,
373 X_RETURN_STATUS => L_RETURN_STATUS,
374 X_MSG_COUNT => X_MSG_COUNT,
375 X_MSG_DATA => X_MSG_DATA
376 );
377
378 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
379 x_return_status := l_return_status;
380 RAISE FND_API.G_EXC_ERROR;
381 END IF;
382
383 ELSE
384 IF (l_temp <> l_compSectMapIds(l_sectLoop)) THEN
385
386 delete_data (
387 p_template_instance_id => l_templateInstance,
388 p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
389 p_commit => p_commit,
390 X_RETURN_STATUS => L_RETURN_STATUS,
391 X_MSG_COUNT => X_MSG_COUNT,
392 X_MSG_DATA => X_MSG_DATA
393 );
394
395
396 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
397 x_return_status := l_return_status;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 l_temp := l_compSectMapIds(l_sectLoop);
402
403 END IF;
404 update_data (
405 p_template_instance_id => l_templateInstance,
406 p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
407 p_created_by => l_createdBy,
408 p_last_updated_by => l_lastUpdatedBy,
409 p_last_update_login =>l_lastUpdateLogin,
410 p_response_id =>l_responseIds(l_sectLoop),
411 p_response_value => l_responseValues(l_sectLoop),
412 p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
413 p_commit => p_commit,
414 X_RETURN_STATUS => L_RETURN_STATUS,
415 X_MSG_COUNT => X_MSG_COUNT,
416 X_MSG_DATA => X_MSG_DATA
417 );
418
419 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
420 x_return_status := l_return_status;
421 RAISE FND_API.G_EXC_ERROR;
422 END IF;
423
424 END IF;
425
426 END LOOP;
427 --disable the debug message
428 ASO_Quote_Util_Pvt.disable_debug_pvt;
429 EXCEPTION
430 WHEN FND_API.G_EXC_ERROR THEN
431 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
432 P_API_NAME => L_API_NAME
433 ,P_PKG_NAME => G_PKG_NAME
434 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
435 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
436 ,X_MSG_COUNT => X_MSG_COUNT
437 ,X_MSG_DATA => X_MSG_DATA
438 ,X_RETURN_STATUS => X_RETURN_STATUS);
439 ASO_Quote_Util_Pvt.disable_debug_pvt;
440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
441 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
442 P_API_NAME => L_API_NAME
443 ,P_PKG_NAME => G_PKG_NAME
444 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
445 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
446 ,X_MSG_COUNT => X_MSG_COUNT
447 ,X_MSG_DATA => X_MSG_DATA
448 ,X_RETURN_STATUS => X_RETURN_STATUS);
449 ASO_Quote_Util_Pvt.disable_debug_pvt;
450 WHEN OTHERS THEN
451 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
452 P_API_NAME => L_API_NAME
453 ,P_PKG_NAME => G_PKG_NAME
454 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
455 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
456 ,X_MSG_COUNT => X_MSG_COUNT
457 ,X_MSG_DATA => X_MSG_DATA
458 ,X_RETURN_STATUS => X_RETURN_STATUS);
459 ASO_Quote_Util_Pvt.disable_debug_pvt;
460 END create_template_instance;
461
462 PROCEDURE update_instance_value(
463 P_VERSION_NUMBER IN NUMBER,
464 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
465 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
466 P_Template_instance_id IN NUMBER := FND_API.G_MISS_NUM,
467 P_comp_sect_map_id IN JTF_NUMBER_TABLE,
468 P_response_value IN JTF_VARCHAR2_TABLE_2000,
469 P_response_id IN JTF_NUMBER_TABLE,
470 P_mult_ans_flag IN JTF_VARCHAR2_TABLE_100,
471 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
472 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
473 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
474
475 l_compSectMapId aso_sup_instance_value.SECT_COMP_MAP_ID%TYPE;
476 l_responseValue aso_sup_instance_value.VALUE%TYPE;
477 l_responseId aso_sup_instance_value.RESPONSE_ID%TYPE;
478 l_multAnsFlag VARCHAR2(1);
479 l_createdBy NUMBER := to_number( fnd_profile.value('USER_ID') );
480 l_lastUpdatedBy NUMBER :=l_createdBy;
481 l_lastUpdateLogin NUMBER := to_number( fnd_profile.value('LOGIN_ID') );
482 lx_returnStatus VARCHAR2(50);
483 lx_msgCount NUMBER;
484 lx_msgData VARCHAR2(2000);
485 l_templateInstance aso_sup_instance_value.template_instance_id%TYPE :=p_template_instance_id;
486
487 l_compSectMapIds JTF_NUMBER_TABLE := p_comp_sect_map_id;
488 l_responseIds JTF_NUMBER_TABLE := P_response_id ;
489 l_responseValues JTF_VARCHAR2_TABLE_2000 :=P_response_value;
490 l_multAnsFlags JTF_VARCHAR2_TABLE_100 := P_mult_ans_flag;
491 l_temp NUMBER :=0;
492 l_api_version NUMBER := 1.0;
493 l_api_name VARCHAR2(50) := 'update_instance_value';
494
495 -- hyang: for bug 2710767
496 lx_status VARCHAR2(1);
497 l_quote_header_id NUMBER;
498
499 CURSOR c_quote (
500 lc_template_instance_id NUMBER
501 ) IS
502 SELECT owner_table_id,owner_table_name
503 FROM ASO_SUP_TMPL_INSTANCE
504 WHERE template_instance_id = lc_template_instance_id;
505 --AND owner_table_name = 'ASO_QUOTE_HEADERS';
506
507 l_header_id NUMBER;
508 l_owner_table_id NUMBER;
509 l_owner_table_name VARCHAR2(240);
510
511 Cursor get_header_id (p_qte_line_id NUMBER ) IS
512 SELECT quote_header_id
513 FROM aso_quote_lines_all
514 WHERE quote_line_id = p_qte_line_id;
515
516 L_RETURN_STATUS VARCHAR2(1);
517
518
519 BEGIN
520 -- Enable debug message
521 Aso_Quote_Util_Pvt.Enable_Debug_Pvt;
522 -- Standard Start of API savepoint
523 -- hyang: for bug 2710767, added pkg_type suffix to the savepoint.
524 SAVEPOINT update_instance_value_int;
525
526 -- Standard call to check for call compatibility.
527 IF NOT FND_API.Compatible_API_Call ( l_api_version,
528 p_version_number,
529 l_api_name,
530 G_PKG_NAME)
531 THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535
536 -- Initialize message list if p_init_msg_list is set to TRUE.
537 IF FND_API.to_Boolean( p_init_msg_list ) THEN
538 FND_MSG_PUB.initialize;
539 END IF;
540
541 -- Debug Message
542 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
543 'Private API: ' || l_api_name || 'start');
544
545 -- Initialize API return status to SUCCESS
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 --
549 -- API body
550 --
551
552 -- ******************************************************************
553 -- Validate Environment
554 -- ******************************************************************
555 IF FND_GLOBAL.User_Id IS NULL THEN
556 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)THEN
557 FND_MESSAGE.Set_Name(' + appShortName +',
558 'UT_CANNOT_GET_PROFILE_VALUE');
559 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
560 FND_MSG_PUB.ADD;
561 END IF;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564
565 -- hyang: for bug 2710767
566 OPEN c_quote (
567 p_template_instance_id
568 );
569 FETCH c_quote INTO l_owner_table_id,l_owner_table_name; --l_quote_header_id;
570 CLOSE c_quote;
571
572 -- sales supp enhancement changes bug 2940126
573 IF l_owner_table_name = 'ASO_QUOTE_HEADERS' THEN
574 l_header_id := l_owner_table_id;
575 ELSIF l_owner_table_name = 'ASO_QUOTE_LINES' THEN
576 OPEN get_header_id(l_owner_table_id);
577 FETCH get_header_id INTO l_header_id;
578 CLOSE get_header_id;
579 END IF;
580
581 -- Check for lock if the table name is for Quoting
582 -- bug 3154810
583 IF ( (l_owner_table_name = 'ASO_QUOTE_HEADERS')
584 OR (l_owner_table_name = 'ASO_QUOTE_LINES') ) THEN
585
586 ASO_CONC_REQ_INT.Lock_Exists(
587 p_quote_header_id => l_header_id,
588 x_status => lx_status);
589
590 IF (lx_status = FND_API.G_TRUE) THEN
591 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
592 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
593 FND_MSG_PUB.ADD;
594 END IF;
595 RAISE FND_API.G_EXC_ERROR;
596 END IF;
597
598 END IF; -- Check for table name
599 FOR l_sectLoop IN 1..l_compSectMapIds.COUNT LOOP
600 IF (l_multAnsFlags(l_sectLoop) <> 'Y') THEN
601 update_data (
602 p_template_instance_id => l_templateInstance,
603 p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
604 p_created_by => l_createdBy,
605 p_last_updated_by => l_lastUpdatedBy,
606 p_last_update_login =>l_lastUpdateLogin,
607 p_response_id =>l_responseIds(l_sectLoop),
608 p_response_value => l_responseValues(l_sectLoop),
609 p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
610 p_commit => p_commit,
611 X_RETURN_STATUS => L_RETURN_STATUS,
612 X_MSG_COUNT => X_MSG_COUNT,
613 X_MSG_DATA => X_MSG_DATA
614 );
615
616 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
617 x_return_status := l_return_status;
618 RAISE FND_API.G_EXC_ERROR;
619 END IF;
620
621
622 ELSE
623 IF (l_temp <> l_compSectMapIds(l_sectLoop)) THEN
624
625 delete_data (
626 p_template_instance_id => l_templateInstance,
627 p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
628 p_commit => p_commit,
629 X_RETURN_STATUS => L_RETURN_STATUS,
630 X_MSG_COUNT => X_MSG_COUNT,
631 X_MSG_DATA => X_MSG_DATA
632 );
633
634 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
635 x_return_status := l_return_status;
636 RAISE FND_API.G_EXC_ERROR;
637 END IF;
638
639 l_temp := l_compSectMapIds(l_sectLoop);
640
641 END IF;
642 update_data (
643 p_template_instance_id => l_templateInstance,
644 p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
645 p_created_by => l_createdBy,
646 p_last_updated_by => l_lastUpdatedBy,
647 p_last_update_login => l_lastUpdateLogin,
648 p_response_id => l_responseIds(l_sectLoop),
649 p_response_value => l_responseValues(l_sectLoop),
650 p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
651 p_commit => p_commit,
652 X_RETURN_STATUS => L_RETURN_STATUS,
653 X_MSG_COUNT => X_MSG_COUNT,
654 X_MSG_DATA => X_MSG_DATA
655 );
656
657
658 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
659 x_return_status := l_return_status;
660 RAISE FND_API.G_EXC_ERROR;
661 END IF;
662
663 END IF;
664 END LOOP;
665 --disable the debug message
666 ASO_Quote_Util_Pvt.disable_debug_pvt;
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
670 P_API_NAME => L_API_NAME
671 ,P_PKG_NAME => G_PKG_NAME
672 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
673 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
674 ,X_MSG_COUNT => X_MSG_COUNT
675 ,X_MSG_DATA => X_MSG_DATA
676 ,X_RETURN_STATUS => X_RETURN_STATUS);
677 ASO_Quote_Util_Pvt.disable_debug_pvt;
678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
680 P_API_NAME => L_API_NAME
681 ,P_PKG_NAME => G_PKG_NAME
682 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
683 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
684 ,X_MSG_COUNT => X_MSG_COUNT
685 ,X_MSG_DATA => X_MSG_DATA
686 ,X_RETURN_STATUS => X_RETURN_STATUS);
687 ASO_Quote_Util_Pvt.disable_debug_pvt;
688 WHEN OTHERS THEN
689 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
690 P_API_NAME => L_API_NAME
691 ,P_PKG_NAME => G_PKG_NAME
692 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
693 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
694 ,X_MSG_COUNT => X_MSG_COUNT
695 ,X_MSG_DATA => X_MSG_DATA
696 ,X_RETURN_STATUS => X_RETURN_STATUS);
697 ASO_Quote_Util_Pvt.disable_debug_pvt;
698 END update_instance_value;
699 END ASO_SUP_CAPTURE_DATA_PKG;