[Home] [Help]
PACKAGE BODY: APPS.OKS_K_ACTIONS_PVT
Source
1 Package BODY OKS_K_ACTIONS_PVT AS
2 /* $Header: OKSKACTB.pls 120.19.12010000.4 2009/04/07 06:03:39 sjanakir ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_K_ACTIONS_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKS';
9
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_MODULE CONSTANT VARCHAR2(250) := 'oks.plsql.'||g_pkg_name||'.';
12 G_APPLICATION_ID CONSTANT NUMBER :=515; -- OKS Application
13
14 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
15 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
16
17 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
19 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
20
21 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKS_UNEXPECTED_ERROR';
22 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
23 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
24 G_RET_STS_ACTION_NOT_ALWD CONSTANT VARCHAR2(1) := 'C';
25
26 ------------------------------------------------------------------------------
27 -- EXCEPTIONS
28 ------------------------------------------------------------------------------
29 ActionNotAllowedException EXCEPTION;
30
31
32 PROCEDURE setRemindersYn
33 (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2,
36 p_chr_id IN NUMBER,
37 p_suppress_Yn IN VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER
41 ) AS
42
43 l_api_version CONSTANT NUMBER := 1;
44 l_api_name CONSTANT VARCHAR2(30) := 'setRemindersYn';
45
46 /* Added for Bug# 7717268*/
47 l_sql_err VARCHAR2(2000);
48 BEGIN
49 -- start debug log
50 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
51 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
52 G_MODULE||l_api_name,
53 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
54 END IF;
55 -- Standard call to check for call compatibility.
56 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59
60
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list ) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 -- Initialize API return status to success
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 -- check if the contract is valid for setting reminders
70 IF validateForRenewalAction (p_chr_id,'RMDR') = 'Y' THEN
71 update oks_k_headers_b
72 set RMNDR_SUPPRESS_FLAG = p_suppress_yn,
73 object_version_number = object_version_number+1,
74 /* Added Bug# 7717268 */
75 last_update_date = SYSDATE,
76 last_updated_by = FND_GLOBAL.USER_ID,
77 last_update_login = FND_GLOBAL.LOGIN_ID
78 where chr_id = p_chr_id;
79 /*added for bug 7717268*/
80 UPDATE okc_k_headers_all_b okcb
81 SET last_update_date = SYSDATE,
82 last_updated_by = FND_GLOBAL.USER_ID,
83 last_update_login = FND_GLOBAL.LOGIN_ID
84 WHERE okcb.id = p_chr_id;
85
86 x_return_status := OKC_CVM_PVT.update_minor_version(p_chr_id => p_chr_id);
87
88 IF x_return_status <> G_RET_STS_SUCCESS THEN
89 l_sql_err := SQLERRM;
90 fnd_message.set_name('OKS','OKS_K_VERSION_UPD_FAILED');
91 fnd_message.set_token ('ERROR_MESSAGE', l_sql_err);
92
93 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
94 fnd_log.STRING (fnd_log.level_statement, g_module ||l_api_name,
95 '1111:Reminder Suppress Flag Update Failed - l_sql_err:'||l_sql_err);
96 END IF;
97
98 x_msg_data := fnd_message.get;
99 x_return_status := g_ret_sts_error;
100 x_msg_count := 1;
101
102 END IF;
103 ELSE
104 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
105 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
106 G_MODULE||l_api_name,
107 '1000: Not a valid action for this contract'||G_PKG_NAME ||'.'||l_api_name);
108 END IF;
109 x_return_status := G_RET_STS_ACTION_NOT_ALWD;
110 RAISE ActionNotAllowedException;
111 END IF;
112
113
114 -- Standard call to get message count and if count is 1, get message info.
115 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
116
117 -- end debug log
118 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
120 G_MODULE||l_api_name,
121 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
122 END IF;
123
124 EXCEPTION
125 WHEN ActionNotAllowedException THEN
126 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
127 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
128 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE||l_api_name,
129 'Leaving '||G_PKG_NAME ||'.'||l_api_name||'.ActionNotAllowedException '||
130 ' Contract cannot be published since it is not in entered status');
131 END IF;
132 x_return_status := G_RET_STS_ACTION_NOT_ALWD;
133 WHEN FND_API.G_EXC_ERROR THEN
134 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
135 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE||l_api_name,
136 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name||' with ERROR status');
137 END IF;
138
139 x_return_status := G_RET_STS_ERROR ;
140 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
141
142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
143 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
145 G_MODULE||l_api_name,
146 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name||' with Unexpected Error status');
147 END IF;
148
149 x_return_status := G_RET_STS_UNEXP_ERROR ;
150 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
151
152 WHEN OTHERS THEN
153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
155 G_MODULE||l_api_name,
156 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name ||' with unexpected error');
157 END IF;
158 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
159 END setRemindersYn;
160
161 PROCEDURE send_email
162 (p_chr_id IN NUMBER
163 ,p_to_address IN VARCHAR2
164 ,p_cc_address IN VARCHAR2
165 ,p_from_address IN VARCHAR2
166 ,p_reply_to_address IN VARCHAR2
167 ,p_subject IN VARCHAR2
168 ,p_message_template_id IN NUMBER
169 ,p_attachment_template_id IN NUMBER
170 ,p_email_text IN VARCHAR2
171 ,p_contract_status_code IN VARCHAR2
172 ,x_request_id OUT NOCOPY NUMBER
173 ,x_return_status OUT NOCOPY VARCHAR2
174 ,x_msg_count OUT NOCOPY NUMBER
175 ,x_msg_data OUT NOCOPY VARCHAR2
176 )AS
177
178 l_api_version CONSTANT NUMBER := 1;
179 l_api_name CONSTANT VARCHAR2(30) := 'SEND_EMAIL';
180 l_request_id NUMBER;
181 l_language VARCHAR2(10);
182 l_attachment_template_id NUMBER;
183 l_document_type_code VARCHAR2(30);
184 l_attachment_name VARCHAR2(50) ;
185 l_process VARCHAR2(10) := 'EMQ';
186
187 l_user_name VARCHAR2(80);
188 l_user_id NUMBER;
189 l_return_status VARCHAR2(10);
190 l_msg_count NUMBER;
191 l_msg_data VARCHAR2(240);
192 l_add_notification BOOLEAN;
193
194
195 /* CURSOR c_attachment
196 IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, olt.template_name)
197 FROM OKS_REPORT_TEMPLATES ort ,OKS_LAYOUT_TEMPLATES_V olt
198 WHERE ort.report_id=olt.template_id
199 AND ort.id=p_attachment_template_id;*/
200
201 CURSOR c_attachment
202 IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, xtvl.template_name)
203 FROM oks_report_templates ort ,XDO_TEMPLATES_VL XTVL
204 WHERE XTVL.APPLICATION_ID = 515 AND
205 XTVL.TEMPLATE_TYPE_CODE = 'RTF' AND
206 SYSDATE BETWEEN XTVL.START_DATE AND
207 NVL(XTVL.END_DATE,SYSDATE) AND NVL(XTVL.DEPENDENCY_FLAG,'P') = 'P' AND
208 ort.report_id=xtvl.template_id AND
209 ort.id=p_attachment_template_id;
210
211
212
213 BEGIN
214
215 l_language := OKS_RENEW_UTIL_PVT.get_template_lang(p_chr_id);
216
217 OPEN c_attachment;
218 FETCH c_attachment INTO l_attachment_template_id,l_document_type_code, l_attachment_name;
219 CLOSE c_attachment;
220
221 IF l_document_type_code = 'QUOTE' THEN
222 l_process := 'EMQA';
223 END IF;
224
225 OKS_RENEW_CONTRACT_PVT.get_user_name(
226 p_api_version => '1'
227 ,p_init_msg_list => FND_API.G_FALSE
228 ,x_return_status => l_return_status
229 ,x_msg_count => l_msg_count
230 ,x_msg_data => l_msg_data
231 ,p_chr_id => p_chr_id
232 ,p_hdesk_user_id => null
233 ,x_user_id => l_user_id
234 ,x_user_name => l_user_name
235 );
236
237 l_add_notification :=fnd_submit.add_notification(l_user_name,'N','Y','Y');
238 IF l_add_notification THEN
239 l_request_id := fnd_request.submit_request(APPLICATION => 'OKS'
240 ,PROGRAM => 'OKS_GENQUOTE_CP'
241 ,DESCRIPTION => NULL
242 ,START_TIME => SYSDATE
243 ,SUB_REQUEST => FALSE
244 ,ARGUMENT1 => l_attachment_template_id
245 ,ARGUMENT2 => p_chr_Id
246 ,ARGUMENT3 => 'EMQ'
247 ,ARGUMENT4 => p_message_template_id
248 ,ARGUMENT5 => p_email_text
249 ,ARGUMENT6 => p_from_address
250 ,ARGUMENT7 => p_to_address
251 ,ARGUMENT8 => p_cc_address
252 ,ARGUMENT9 => p_reply_to_address
253 ,ARGUMENT10 => p_subject
254 ,ARGUMENT11 => p_contract_status_code
255 ,ARGUMENT12 => null
256 ,ARGUMENT13 => null
257 ,ARGUMENT14 => l_language
258 ,ARGUMENT15 => l_attachment_name
259 ,ARGUMENT16 => l_process);
260
261
262 x_request_id :=l_request_id;
263
264 if x_request_id >0 then
265
266 Update OKS_K_HEADERS_B
267 Set PROCESS_REQUEST_ID = l_request_id
268 Where CHR_ID = p_chr_id;
269
270 x_return_status :=G_RET_STS_SUCCESS;
271 x_msg_count :=0;
272 x_msg_data :='';
273 else
274 x_return_status :=G_RET_STS_ERROR;
275 end if;
276
277 ELSE
278 x_return_status :=G_RET_STS_ERROR;
279 x_msg_count := l_msg_count;
280 x_msg_data := l_msg_data;
281 END IF;
282
283 commit work;
284
285 EXCEPTION
286 WHEN FND_API.G_EXC_ERROR THEN
287 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
288 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
289 G_MODULE||l_api_name,
290 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
291 END IF;
292
293 x_return_status := G_RET_STS_ERROR ;
294 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
295
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
298 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
299 G_MODULE||l_api_name,
300 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
301 END IF;
302
303 x_return_status := G_RET_STS_UNEXP_ERROR ;
304 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
305
306 WHEN OTHERS THEN
307 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
308 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
309 G_MODULE||l_api_name,
310 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
311 END IF;
312 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
313
314
315 END send_email;
316
317
318 PROCEDURE execute_qa_check_list(
319 p_api_version IN NUMBER,
320 p_init_msg_list IN VARCHAR2 ,
321 x_return_status OUT NOCOPY VARCHAR2,
322 x_msg_count OUT NOCOPY NUMBER,
323 x_msg_data OUT NOCOPY VARCHAR2,
324 p_qcl_id IN NUMBER,
325 p_chr_id IN NUMBER,
326 p_override_flag IN VARCHAR2)
327 AS
328 l_api_name CONSTANT VARCHAR2(30) := 'execute_qa_check_list';
329 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
330 l_msg_count NUMBER;
331 l_msg_data VARCHAR2(2000);
332 l_msg_tbl OKC_QA_CHECK_PUB.MSG_TBL_TYPE;
333
334 -- bug 5329334
335 l_count BINARY_INTEGER;
336 l_msg_ctr BINARY_INTEGER := 1;
337
338 BEGIN
339
340 fnd_file.put_line(FND_FILE.LOG,' ');
341 fnd_file.put_line(FND_FILE.LOG,'Entering OKS_K_ACTIONS_PVT.execute_qa_check_list');
342 fnd_file.put_line(FND_FILE.LOG,' ');
343
344 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
345 fnd_file.put_line(FND_FILE.LOG,'Calling OKC_QA_CHECK_PUB.execute_qa_check_list');
346 fnd_file.put_line(FND_FILE.LOG,'Start Time : '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SSSS'));
347 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
348 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
349 fnd_file.put_line(FND_FILE.LOG,'p_api_version : '||p_api_version);
350 fnd_file.put_line(FND_FILE.LOG,'p_init_msg_list : '||p_init_msg_list);
351 fnd_file.put_line(FND_FILE.LOG,'p_chr_id : '||p_chr_id);
352 fnd_file.put_line(FND_FILE.LOG,'p_qcl_id : '||p_qcl_id);
353 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
354 fnd_file.put_line(FND_FILE.LOG,' ');
355
356 OKC_QA_CHECK_PUB.execute_qa_check_list
357 (
358 p_api_version => p_api_version,
359 p_init_msg_list => p_init_msg_list,
360 x_return_status => l_return_status,
361 x_msg_count => l_msg_count,
362 x_msg_data => l_msg_data,
363 p_qcl_id => p_qcl_id,
364 p_chr_id => p_chr_id,
365 x_msg_tbl => l_msg_tbl
366 );
367
368 fnd_file.put_line(FND_FILE.LOG,' ');
369 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
370 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling OKC_QA_CHECK_PUB.execute_qa_check_list');
371 fnd_file.put_line(FND_FILE.LOG,'End Time : '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SSSS'));
372 fnd_file.put_line(FND_FILE.LOG,'OUT Parameters ');
373 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
374 fnd_file.put_line(FND_FILE.LOG,'x_return_status : '||l_return_status);
375 fnd_file.put_line(FND_FILE.LOG,'x_msg_tbl.count : '||l_msg_tbl.count);
376 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
377 fnd_file.put_line(FND_FILE.LOG,' ');
378
379
380 -- bug 5329334
381 -- should loop thru l_msg_tbl(l_count).error_status to check if there are any qa errors
382
383 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
384 x_return_status :=l_return_status;
385 RETURN;
386 END IF;
387
388 x_return_status :=l_return_status;
389 x_msg_count :=l_msg_count;
390 x_msg_data :=l_msg_data;
391
392 -- Check if any of the QA checks have failed. If so write to log file
393 IF l_msg_tbl.count >0 THEN
394
395 fnd_file.put_line(FND_FILE.LOG,'******** Following QA Errors occured ******** ');
396 fnd_file.put_line(FND_FILE.LOG,' ');
397
398 l_count := l_msg_tbl.first;
399 LOOP
400 IF l_msg_tbl(l_count).error_status='E' THEN
401 -- write to fnd_log file
402 fnd_file.put_line(FND_FILE.LOG,l_msg_ctr||' : '||l_msg_tbl(l_count).data);
403
404 -- reset the out parameter to Error
405 x_return_status := 'E';
406
407 -- increment the counter
408 l_msg_ctr := l_msg_ctr +1;
409 END IF; -- error
410
411 EXIT WHEN l_count =l_msg_tbl.LAST;
412 l_count:=l_msg_tbl.next(l_count);
413 END LOOP;
414 END IF; -- l_msg_tbl.count >0
415
416 fnd_file.put_line(FND_FILE.LOG,' ');
417 fnd_file.put_line(FND_FILE.LOG,'Leaving OKS_K_ACTIONS_PVT.execute_qa_check_list');
418 fnd_file.put_line(FND_FILE.LOG,'x_return_status : '||x_return_status);
419 fnd_file.put_line(FND_FILE.LOG,' ');
420
421 EXCEPTION
422 WHEN FND_API.G_EXC_ERROR THEN
423 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
424 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
425 G_MODULE||l_api_name,
426 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
427 END IF;
428
429 x_return_status := G_RET_STS_ERROR ;
430 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
431
432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
433 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
434 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
435 G_MODULE||l_api_name,
436 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
437 END IF;
438
439 x_return_status := G_RET_STS_UNEXP_ERROR ;
440 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
441
442 WHEN OTHERS THEN
443 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
444 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
445 G_MODULE||l_api_name,
446 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
447 END IF;
448 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
449
450
451 END;
452
453 FUNCTION get_to_email (p_contract_id IN NUMBER)
454 RETURN VARCHAR2
455 AS
456 l_to_email_address VARCHAR2(120);
457 BEGIN
458 OKS_AUTO_REMINDER.GET_QTO_EMAIL(
459 p_chr_id => p_contract_id
460 ,x_qto_email => l_to_email_address);
461 RETURN l_to_email_address;
462
463 END;
464
465 PROCEDURE launch_qa_report
466 (
467 p_api_version IN NUMBER,
468 p_init_msg_list IN VARCHAR2,
469 p_contract_list IN VARCHAR2,
470 x_cp_request_id OUT NOCOPY NUMBER,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_data OUT NOCOPY VARCHAR2,
473 x_msg_count OUT NOCOPY NUMBER
474 ) AS
475
476 l_api_version CONSTANT NUMBER := 1;
477 l_api_name CONSTANT VARCHAR2(30) := 'launch_qa_report';
478
479 tmp_contract_list VARCHAR2(8000) ;
480 i NUMBER := 0;
481 j NUMBER := 0;
482
483 TYPE l_chr_id_list IS TABLE OF OKC_K_HEADERS_ALL_B.ID%TYPE INDEX BY BINARY_INTEGER;
484 l_chr_id_tbl l_chr_id_list;
485
486 l_request_id NUMBER := 0;
487
488 BEGIN
489 -- start debug log
490 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
491 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
492 G_MODULE||l_api_name,
493 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
494 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
495 G_MODULE||l_api_name,
496 '100: Parameters ');
497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
498 G_MODULE||l_api_name,
499 '100: p_contract_list : '||p_contract_list);
500 END IF;
501
502 -- Standard call to check for call compatibility.
503 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END IF;
506
507 -- Initialize message list if p_init_msg_list is set to TRUE.
508 IF FND_API.to_Boolean( p_init_msg_list ) THEN
509 FND_MSG_PUB.initialize;
510 END IF;
511
512 -- Initialize API return status to success
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514
515 -- set context to multi org
516 mo_global.init('OKC');
517
518 tmp_contract_list := p_contract_list;
519 x_cp_request_id := 0;
520
521 -- debug log
522 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
523 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
524 G_MODULE||l_api_name,
525 '110: Converting K list to pl/sql');
526 END IF;
527
528 --Convert id list (string) to PL/SQL Array for bulk Update
529 LOOP
530 i := INSTR(tmp_contract_list,',');
531
532 -- debug log
533 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
535 G_MODULE||l_api_name,
536 '120: i : '||i);
537 END IF;
538
539 IF i > 0 THEN
540 -- comma found
541
542 l_chr_id_tbl(j) := SUBSTR(tmp_contract_list,1,i-1);
543
544 -- debug log
545 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
547 G_MODULE||l_api_name,
548 '130: j :'||j);
549 END IF;
550
551 tmp_contract_list := SUBSTR(tmp_contract_list,i+1, length(tmp_contract_list) - i);
552
553 -- debug log
554 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
555 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
556 G_MODULE||l_api_name,
557 '130: tmp_contract_list : '||tmp_contract_list);
558 END IF;
559
560 j := j + 1;
561 ELSE
562 -- no comma found i.e last contract id
563 l_chr_id_tbl(j) := tmp_contract_list;
564 EXIT;
565 END IF;
566
567 END LOOP;
568
569 -- debug log
570 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
572 G_MODULE||l_api_name,
573 '200: After Converting K list to pl/sql');
574 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
575 G_MODULE||l_api_name,
576 '200: Count of Ks is : '||l_chr_id_tbl.COUNT);
577 END IF;
578
579 -- if the number of contracts exceed 6 then throw an error
580 IF NVL(l_chr_id_tbl.COUNT,0) > 6 THEN
581 fnd_message.set_name(G_APP_NAME,'OKS_SUBMIT_MAX_QA_ERROR');
582 fnd_msg_pub.add;
583 x_return_status := G_RET_STS_ERROR ;
584 RETURN;
585 END IF;
586
587
588 l_request_id := fnd_request.submit_request
589 (
590 APPLICATION => 'OKS',
591 PROGRAM => 'OKSRQACK',
592 DESCRIPTION => 'QA Report',
593 START_TIME => NULL,
594 SUB_REQUEST => FALSE,
595 ARGUMENT1 => p_contract_list
596 );
597
598 -- debug log
599 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
600 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
601 G_MODULE||l_api_name,
602 '300: Request Id of pgm : '||l_request_id);
603 END IF;
604
605 IF l_request_id > 0 THEN
606 FORALL i in NVL(l_chr_id_tbl.FIRST,0)..NVL(l_chr_id_tbl.LAST,-1)
607 UPDATE OKS_K_HEADERS_B
608 SET PROCESS_REQUEST_ID = l_request_id
609 WHERE chr_id = l_chr_id_tbl(i);
610 ELSE
611 FND_MESSAGE.SET_NAME('OKS','OKS_CP_ERROR');
612 FND_MESSAGE.SET_TOKEN('SQL_ERROR',SQLERRM);
613 FND_MSG_PUB.ADD;
614 x_return_status := G_RET_STS_ERROR ;
615 END IF;
616
617 x_cp_request_id := l_request_id;
618
619 commit work;
620
621
622 -- Standard call to get message count and if count is 1, get message info.
623 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
624
625 -- end debug log
626 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
627 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
628 G_MODULE||l_api_name,
629 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
630 END IF;
631
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN
634 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
636 G_MODULE||l_api_name,
637 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
638 END IF;
639
640 x_return_status := G_RET_STS_ERROR ;
641 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
642
643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
644 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
646 G_MODULE||l_api_name,
647 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
648 END IF;
649
650 x_return_status := G_RET_STS_UNEXP_ERROR ;
651 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
652
653 WHEN OTHERS THEN
654 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
655 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
656 G_MODULE||l_api_name,
657 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
658 END IF;
659 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
660 END launch_qa_report;
661
662
663 /*
664 This function checks if the contract is valid for Renewal Workbench Table Actions. This check is done before doing following Actions
665 Enable Reminders, Disable Reminders, Submit for Approval and Publish to CustomerParameter: contract id
666 Returns: Y or N. If the ste_code is ENTERED and the contract is not submitted for approval then returns Y else returns N
667 */
668 FUNCTION validateForRenewalAction (
669 p_chr_id NUMBER,
670 p_called_from VARCHAR2 DEFAULT NULL
671 )
672 RETURN VARCHAR2
673 IS
674
675 l_return_val VARCHAR2(1) := 'N';
676
677 CURSOR reminder_cur IS
678 SELECT 'X'
679 FROM okc_k_headers_all_b okck,
680 okc_statuses_b sts
681 WHERE okck.sts_code = sts.code
682 AND sts.ste_code = 'ENTERED'
683 AND okck.id = p_chr_id;
684
685 reminder_rec reminder_cur%ROWTYPE;
686
687 CURSOR chr_cur IS
688 SELECT 'X'
689 FROM okc_k_headers_all_b okck,
690 okc_statuses_b sts
691 WHERE okck.sts_code = sts.code
692 AND sts.ste_code = 'ENTERED'
693 AND okck.id = p_chr_id
694 AND NOT EXISTS
695 (SELECT 1
696 FROM WF_ITEMS WF,
697 OKC_PROCESS_DEFS_B KPDF
698 WHERE WF.item_key = okck.contract_number || okck.contract_number_modifier
699 AND WF.end_date IS NULL
700 AND WF.item_type = KPDF.wf_name
701 AND KPDF.pdf_type = 'WPS');
702
703 chr_rec chr_cur%ROWTYPE;
704 l_api_name CONSTANT VARCHAR2(30) := 'validateForRenewalAction';
705
706 BEGIN
707
708 -- start debug log
709 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
711 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
712 END IF;
713
714 -- we don't want to restrict enabling / disabling reminders when contract is in
715 -- approval process. Two reasons 1. Autoreminders will not send any reminders when
716 -- OKCAUKAP wf is active. 2. When a regular contract is submitted for approval the
717 -- negotiation status is pending internal approval but OKCAUKAP wf is not yet
718 -- launched, this case we are allowing enable/disable reminders so in order to keep
719 -- the consistency, we don't want to restrict these actions
720 IF p_called_from = 'RMDR' THEN
721 OPEN reminder_cur;
722 Fetch reminder_cur INTO reminder_rec;
723 IF reminder_cur%FOUND THEN
724 l_return_val := 'Y';
725 END IF;
726 CLOSE reminder_cur;
727 ELSE
728 OPEN chr_cur;
729 Fetch chr_cur INTO chr_rec;
730 IF chr_cur%FOUND THEN
731 l_return_val := 'Y';
732 END IF;
733 CLOSE chr_cur;
734 END IF;
735
736 -- end debug log
737 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
738 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
739 G_MODULE||l_api_name,
740 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name || ' with return value: '||l_return_val);
741 END IF;
742 return(l_return_val);
743
744 EXCEPTION
745 WHEN others THEN
746 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
747 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
748 '2000: Leaving with error '||G_PKG_NAME ||'.'||l_api_name);
749 END IF;
750 l_return_val := 'N';
751 return(l_return_val);
752 END validateForRenewalAction;
753
754
755
756 /*
757 This method will insert the email details
758 into OKS_EMAIL_DETAILS table and
759 will return email_id as the output parameter value.-Bug#4911901
760 */
761
762 PROCEDURE STORE_EMAIL_DTLS
763 (
764 p_from_address IN VARCHAR2,
765 p_to_address IN VARCHAR2,
766 p_cc_address IN VARCHAR2,
767 p_reply_to_address IN VARCHAR2,
768 p_message_template_id IN NUMBER,
769 p_attachment_template_id IN NUMBER,
770 p_email_subject IN VARCHAR2,
771 p_email_body IN VARCHAR2,
772 p_email_contract_status IN VARCHAR2,
773 x_email_id OUT NOCOPY NUMBER,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_data OUT NOCOPY VARCHAR2,
776 x_msg_count OUT NOCOPY NUMBER
777 )AS
778
779 l_api_name CONSTANT VARCHAR2(30) := 'STORE_EMAIL_DTLS';
780 l_email_id NUMBER := 0;
781
782 BEGIN
783
784 -- Initialize API return status to success
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786
787 -- set context to multi org
788 mo_global.init('OKC');
789
790
791 -- start debug log
792 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
793 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
794 G_MODULE||l_api_name,
795 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
796 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
797 G_MODULE||l_api_name,
798 '100: Parameters ');
799 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
800 G_MODULE||l_api_name,
801 '102: p_from_address : '||p_from_address);
802 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
803 G_MODULE||l_api_name,
804 '103: p_to_address : '||p_to_address);
805 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
806 G_MODULE||l_api_name,
807 '104: p_cc_address : '||p_cc_address);
808 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
809 G_MODULE||l_api_name,
810 '106: p_reply_to_address : '||p_reply_to_address);
811 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
812 G_MODULE||l_api_name,
813 '107: p_message_template_id : '||p_message_template_id);
814 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
815 G_MODULE||l_api_name,
816 '108: p_attachment_template_id : '||p_attachment_template_id);
817 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
818 G_MODULE||l_api_name,
819 '109: p_email_subject : '||p_email_subject);
820 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
821 G_MODULE||l_api_name,
822 '111: p_email_contract_status : '||p_email_contract_status);
823 END IF;
824
825 BEGIN
826
827 INSERT INTO oks_email_details
828 (
829 email_id,
830 from_address,
831 to_address,
832 cc_address,
833 reply_to_address,
834 message_template_id,
835 attachment_template_id,
836 email_subject,
837 email_body,
838 email_contract_status,
839 CREATED_BY,
840 LAST_UPDATED_BY,
841 CREATION_DATE,
842 LAST_UPDATE_DATE,
843 LAST_UPDATE_LOGIN
844 )
845 VALUES
846 (
847 oks_email_details_s1.nextval,
848 p_from_address,
849 p_to_address,
850 p_cc_address,
851 p_reply_to_address,
852 p_message_template_id,
853 p_attachment_template_id,
854 p_email_subject,
855 TO_CLOB(p_email_body),
856 p_email_contract_status,
857 FND_GLOBAL.USER_ID, -- CREATED_BY
858 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
859 SYSDATE, -- CREATION_DATE
860 SYSDATE, -- LAST_UPDATE_DATE
861 FND_GLOBAL.LOGIN_ID --LAST_UPDATE_LOGIN
862 )
863 RETURNING email_id
864 INTO l_email_id;
865
866
867 EXCEPTION
868 WHEN OTHERS THEN
869 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
871 G_MODULE||l_api_name,
872 '4657: Leaving '||G_PKG_NAME ||'.'||l_api_name||'.'||SQLERRM);
873 END IF;
874
875 END;
876
877 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
878 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
879 G_MODULE||l_api_name,
880 '200: After inserting into table:');
881 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
882 G_MODULE||l_api_name,
883 '201: email id generated from store_email_dtls : '||l_email_id);
884 END IF;
885
886 x_email_id := l_email_id;
887
888 commit work;
889
890
891 -- end debug log
892 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
893 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
894 G_MODULE||l_api_name,
895 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
896 END IF;
897
898 EXCEPTION
899 WHEN FND_API.G_EXC_ERROR THEN
900 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
901 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
902 G_MODULE||l_api_name,
903 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
904 END IF;
905
906 x_return_status := G_RET_STS_ERROR ;
907
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
910 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
911 G_MODULE||l_api_name,
912 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
913 END IF;
914
915 x_return_status := G_RET_STS_UNEXP_ERROR ;
916
917 WHEN OTHERS THEN
918 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
919 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
920 G_MODULE||l_api_name,
921 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name||'.'||SQLERRM);
922 END IF;
923
924 END STORE_EMAIL_DTLS;
925
926
927 /*
928 This API will retrieve email details from OKS_EMAIL_DETAILS table.-Bug#4911901
929 */
930
931 PROCEDURE GET_EMAIL_DTLS
932 (
933 p_email_id IN NUMBER,
934 x_email_body OUT NOCOPY VARCHAR2,
935 x_return_status OUT NOCOPY VARCHAR2,
936 x_msg_data OUT NOCOPY VARCHAR2,
937 x_msg_count OUT NOCOPY NUMBER
938 )AS
939
940 l_api_name CONSTANT VARCHAR2(30) := 'GET_EMAIL_DTLS';
941
942 BEGIN
943 -- start debug log
944 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
946 G_MODULE||l_api_name,
947 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
948 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
949 G_MODULE||l_api_name,
950 '300: Parameters ');
951 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
952 G_MODULE||l_api_name,
953 '301: p_email_id : '||p_email_id);
954 END IF;
955
956 -- Initialize API return status to success
957 x_return_status := FND_API.G_RET_STS_SUCCESS;
958
959 -- set context to multi org
960 mo_global.init('OKC');
961
962
963 SELECT email_body
964 INTO x_email_body
965 FROM OKS_EMAIL_DETAILS
966 WHERE email_id = p_email_id;
967
968 -- end debug log
969 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
970 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
971 G_MODULE||l_api_name,
972 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
973 END IF;
974
975 EXCEPTION
976 WHEN FND_API.G_EXC_ERROR THEN
977 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
978 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
979 G_MODULE||l_api_name,
980 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
981 END IF;
982
983 x_return_status := G_RET_STS_ERROR ;
984
985 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
988 G_MODULE||l_api_name,
989 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
990 END IF;
991
992 x_return_status := G_RET_STS_UNEXP_ERROR ;
993
994 WHEN OTHERS THEN
995 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
996 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
997 G_MODULE||l_api_name,
998 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
999 END IF;
1000
1001
1002 END GET_EMAIL_DTLS;
1003
1004
1005 /*
1006 This API will delete email details from OKS_EMAIL_DETAILS table.-Bug#4911901
1007 */
1008 PROCEDURE DEL_EMAIL_DTLS
1009 (
1010 p_email_id IN NUMBER,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_data OUT NOCOPY VARCHAR2,
1013 x_msg_count OUT NOCOPY NUMBER
1014 )AS
1015
1016 l_api_name CONSTANT VARCHAR2(30) := 'DEL_EMAIL_DTLS';
1017
1018 BEGIN
1019
1020 -- start debug log
1021 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1022 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1023 G_MODULE||l_api_name,
1024 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1025 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1026 G_MODULE||l_api_name,
1027 '400: Parameters ');
1028 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1029 G_MODULE||l_api_name,
1030 '401: p_email_id : '||p_email_id);
1031 END IF;
1032
1033 -- Initialize API return status to success
1034 x_return_status := FND_API.G_RET_STS_SUCCESS;
1035
1036 -- set context to multi org
1037 mo_global.init('OKC');
1038
1039 DELETE FROM OKS_EMAIL_DETAILS WHERE email_id = p_email_id;
1040
1041 IF SQL%ROWCOUNT = 0 THEN
1042
1043 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1044 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1045 G_MODULE||l_api_name,
1046 '402: Rows are deleted successfully from OKS_EMAIL_DETAILS table');
1047 END IF;
1048
1049 END IF;
1050
1051 -- end debug log
1052 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1054 G_MODULE||l_api_name,
1055 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN FND_API.G_EXC_ERROR THEN
1060 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1061 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1062 G_MODULE||l_api_name,
1063 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1064 END IF;
1065
1066 x_return_status := G_RET_STS_ERROR ;
1067
1068 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1070 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1071 G_MODULE||l_api_name,
1072 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1073 END IF;
1074
1075 x_return_status := G_RET_STS_UNEXP_ERROR ;
1076
1077 WHEN OTHERS THEN
1078 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1079 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1080 G_MODULE||l_api_name,
1081 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1082 END IF;
1083
1084
1085 END DEL_EMAIL_DTLS;
1086
1087 /* Overloaded send_email API that has been already defined.- Bug#4911901*/
1088 PROCEDURE send_email
1089 (p_chr_id IN NUMBER
1090 ,p_email_Id IN NUMBER
1091 ,p_to_address IN VARCHAR2
1092 ,p_cc_address IN VARCHAR2
1093 ,p_from_address IN VARCHAR2
1094 ,p_reply_to_address IN VARCHAR2
1095 ,p_subject IN VARCHAR2
1096 ,p_message_template_id IN NUMBER
1097 ,p_attachment_template_id IN NUMBER
1098 ,p_contract_status_code IN VARCHAR2
1099 ,x_request_id OUT NOCOPY NUMBER
1100 ,x_return_status OUT NOCOPY VARCHAR2
1101 ,x_msg_count OUT NOCOPY NUMBER
1102 ,x_msg_data OUT NOCOPY VARCHAR2
1103 )AS
1104
1105 l_api_version CONSTANT NUMBER := 1;
1106 l_api_name CONSTANT VARCHAR2(30) := 'SEND_EMAIL';
1107 l_request_id NUMBER;
1108 l_language VARCHAR2(10);
1109 l_attachment_template_id NUMBER;
1110 l_document_type_code VARCHAR2(30);
1111 l_attachment_name VARCHAR2(50) ;
1112 l_process VARCHAR2(10) := 'EMQ';
1113
1114 l_user_name VARCHAR2(80);
1115 l_user_id NUMBER;
1116 l_return_status VARCHAR2(10);
1117 l_msg_count NUMBER;
1118 l_msg_data VARCHAR2(240);
1119 l_add_notification BOOLEAN;
1120
1121
1122 /* CURSOR c_attachment
1123 IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, olt.template_name)
1124 FROM OKS_REPORT_TEMPLATES ort ,OKS_LAYOUT_TEMPLATES_V olt
1125 WHERE ort.report_id=olt.template_id
1126 AND ort.id=p_attachment_template_id;*/
1127
1128 CURSOR c_attachment
1129 IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, xtvl.template_name)
1130 FROM oks_report_templates ort ,XDO_TEMPLATES_VL XTVL
1131 WHERE XTVL.APPLICATION_ID = 515 AND
1132 XTVL.TEMPLATE_TYPE_CODE = 'RTF' AND
1133 SYSDATE BETWEEN XTVL.START_DATE AND
1134 NVL(XTVL.END_DATE,SYSDATE) AND NVL(XTVL.DEPENDENCY_FLAG,'P') = 'P' AND
1135 ort.report_id=xtvl.template_id AND
1136 ort.id=p_attachment_template_id;
1137
1138 BEGIN
1139
1140 l_language := OKS_RENEW_UTIL_PVT.get_template_lang(p_chr_id);
1141
1142 OPEN c_attachment;
1143 FETCH c_attachment INTO l_attachment_template_id,l_document_type_code, l_attachment_name;
1144 CLOSE c_attachment;
1145
1146 IF l_document_type_code = 'QUOTE' THEN
1147 l_process := 'EMQA';
1148 END IF;
1149
1150 OKS_RENEW_CONTRACT_PVT.get_user_name(
1151 p_api_version => '1'
1152 ,p_init_msg_list => FND_API.G_FALSE
1153 ,x_return_status => l_return_status
1154 ,x_msg_count => l_msg_count
1155 ,x_msg_data => l_msg_data
1156 ,p_chr_id => p_chr_id
1157 ,p_hdesk_user_id => null
1158 ,x_user_id => l_user_id
1159 ,x_user_name => l_user_name
1160 );
1161
1162 l_add_notification :=fnd_submit.add_notification(l_user_name,'N','Y','Y');
1163 IF l_add_notification THEN
1164 l_request_id := fnd_request.submit_request(APPLICATION => 'OKS'
1165 ,PROGRAM => 'OKS_GENQUOTE_CP'
1166 ,DESCRIPTION => NULL
1167 ,START_TIME => SYSDATE
1168 ,SUB_REQUEST => FALSE
1169 ,ARGUMENT1 => l_attachment_template_id
1170 ,ARGUMENT2 => p_chr_Id
1171 ,ARGUMENT3 => 'EMQ'
1172 ,ARGUMENT4 => p_message_template_id
1173 ,ARGUMENT5 => null
1174 ,ARGUMENT6 => p_from_address
1175 ,ARGUMENT7 => p_to_address
1176 ,ARGUMENT8 => p_cc_address
1177 ,ARGUMENT9 => p_reply_to_address
1178 ,ARGUMENT10 => p_subject
1179 ,ARGUMENT11 => p_contract_status_code
1180 ,ARGUMENT12 => null
1181 ,ARGUMENT13 => null
1182 ,ARGUMENT14 => l_language
1183 ,ARGUMENT15 => l_attachment_name
1184 ,ARGUMENT16 => l_process
1185 ,ARGUMENT17 => p_email_Id);
1186
1187
1188 x_request_id :=l_request_id;
1189
1190 if x_request_id >0 then
1191
1192 Update OKS_K_HEADERS_B
1193 Set PROCESS_REQUEST_ID = l_request_id
1194 Where CHR_ID = p_chr_id;
1195
1196 x_return_status :=G_RET_STS_SUCCESS;
1197 x_msg_count :=0;
1198 x_msg_data :='';
1199 else
1200 x_return_status :=G_RET_STS_ERROR;
1201 end if;
1202
1203 ELSE
1204 x_return_status :=G_RET_STS_ERROR;
1205 x_msg_count := l_msg_count;
1206 x_msg_data := l_msg_data;
1207 END IF;
1208
1209 commit work;
1210
1211 EXCEPTION
1212 WHEN FND_API.G_EXC_ERROR THEN
1213 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1215 G_MODULE||l_api_name,
1216 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1217 END IF;
1218
1219 x_return_status := G_RET_STS_ERROR ;
1220 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1221
1222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1223 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1224 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1225 G_MODULE||l_api_name,
1226 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1227 END IF;
1228
1229 x_return_status := G_RET_STS_UNEXP_ERROR ;
1230 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1231
1232 WHEN OTHERS THEN
1233 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1234 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1235 G_MODULE||l_api_name,
1236 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1237 END IF;
1238 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1239
1240
1241 END send_email;
1242
1243 PROCEDURE update_single_contracts (
1244 p_chr_id IN OKC_K_HEADERS_ALL_B.ID%TYPE,
1245 p_status_code IN OKC_K_HEADERS_ALL_B.STS_CODE%TYPE,
1246 p_reason_code IN OKC_K_HEADERS_ALL_B.TRN_CODE%TYPE,
1247 p_comments IN VARCHAR2,
1248 p_due_date IN OKS_K_HEADERS_B.FOLLOW_UP_DATE%TYPE,
1249 p_action IN OKS_K_HEADERS_B.FOLLOW_UP_ACTION%TYPE,
1250 p_est_percent IN OKS_K_HEADERS_B.EST_REV_PERCENT%TYPE,
1251 p_est_date IN OKS_K_HEADERS_B.EST_REV_DATE%TYPE,
1252 p_contract_notes IN JTF_NOTES_TL.NOTES%TYPE,
1253 p_renewal_notes IN OKS_K_HEADERS_B.RENEWAL_COMMENT%TYPE,
1254 x_succ_err_contract OUT NOCOPY VARCHAR2,
1255 x_return_status OUT NOCOPY VARCHAR2,
1256 x_msg_data OUT NOCOPY VARCHAR2,
1257 x_msg_count OUT NOCOPY NUMBER)
1258 AS
1259
1260 l_api_version CONSTANT NUMBER := 1;
1261 l_api_name CONSTANT VARCHAR2(30) := 'update_single_contracts';
1262
1263 l_old_status_code VARCHAR2(100);
1264 l_contract_number VARCHAR2(100);
1265 l_init_msg_list VARCHAR2(1) := 'T';
1266 l_jtf_note_id JTF_NOTES_TL.JTF_NOTE_ID%TYPE;
1267
1268 l_minor_version_updated VARCHAR2(1) := 'F';
1269 l_sql_err VARCHAR2(2000);
1270
1271 CURSOR csr_k_old_status(c_chr_id in number) IS
1272 Select sts_code,
1273 contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contract_number
1274 from okc_k_headers_all_b
1275 Where id = c_chr_id;
1276
1277 -- bug 5934875, update page should be able to change values for follow-up and forecast to null
1278 CURSOR csr_k_old_forecast IS
1279 SELECT follow_up_date,
1280 follow_up_action,
1281 est_rev_percent,
1282 est_rev_date
1283 FROM oks_k_headers_b
1284 WHERE chr_id = p_chr_id;
1285
1286 l_old_follow_up_date oks_k_headers_b.follow_up_date%TYPE;
1287 l_old_follow_up_action oks_k_headers_b.follow_up_action%TYPE;
1288 l_old_est_rev_percent oks_k_headers_b.est_rev_percent%TYPE;
1289 l_old_est_rev_date oks_k_headers_b.est_rev_date%TYPE;
1290
1291 BEGIN
1292 -- start debug log
1293 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1294 THEN
1295 fnd_log.STRING (fnd_log.level_statement,
1296 g_module ||
1297 l_api_name,
1298 '100: Entered ' ||
1299 g_pkg_name ||
1300 '.' ||
1301 l_api_name
1302 );
1303 END IF;
1304
1305 -- set context to multi org
1306 mo_global.init ('OKC');
1307
1308 -- Initialize API return status to success
1309 x_return_status := fnd_api.g_ret_sts_success;
1310
1311 DBMS_TRANSACTION.SAVEPOINT(l_api_name);
1312
1313 -- Printing input paramter values
1314 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1315 THEN
1316 fnd_log.STRING (fnd_log.level_statement,
1317 g_module ||
1318 l_api_name,
1319 '101:p_chr_id = ' ||p_chr_id
1320 );
1321 fnd_log.STRING (fnd_log.level_statement,
1322 g_module ||
1323 l_api_name,
1324 '102:p_status_code = ' ||p_status_code
1325 );
1326 fnd_log.STRING (fnd_log.level_statement,
1327 g_module ||
1328 l_api_name,
1329 '103:p_reason_code = ' ||p_reason_code
1330 );
1331 fnd_log.STRING (fnd_log.level_statement,
1332 g_module ||
1333 l_api_name,
1334 '104:p_comments = ' ||p_comments
1335 );
1336 fnd_log.STRING (fnd_log.level_statement,
1337 g_module ||
1338 l_api_name,
1339 '105:p_due_date = ' ||p_due_date
1340 );
1341 fnd_log.STRING (fnd_log.level_statement,
1342 g_module ||
1343 l_api_name,
1344 '106:p_action = ' ||p_action
1345 );
1346 fnd_log.STRING (fnd_log.level_statement,
1347 g_module ||
1348 l_api_name,
1349 '107:p_est_percent = ' ||p_est_percent
1350 );
1351 fnd_log.STRING (fnd_log.level_statement,
1352 g_module ||
1353 l_api_name,
1354 '108:p_est_date = ' ||p_est_date
1355 );
1356 fnd_log.STRING (fnd_log.level_statement,
1357 g_module ||
1358 l_api_name,
1359 '109:p_contract_notes = ' ||p_contract_notes
1360 );
1361 END IF;
1362
1363 --fetching old contract status for the given contract id
1364 open csr_k_old_status(p_chr_id);
1365 fetch csr_k_old_status into l_old_status_code,l_contract_number;
1366 close csr_k_old_status;
1367
1368 x_succ_err_contract:= l_contract_number;
1369
1370 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1371 THEN
1372 fnd_log.STRING (fnd_log.level_statement,
1373 g_module ||
1374 l_api_name,
1375 '210:l_old_status_code = ' ||l_old_status_code
1376 );
1377 fnd_log.STRING (fnd_log.level_statement,
1378 g_module ||
1379 l_api_name,
1380 '210a:l_contract_number = ' ||l_contract_number
1381 );
1382 END IF;
1383
1384
1385 -- bug 5934875, update page should be able to change values for follow-up and forecast to null
1386 OPEN csr_k_old_forecast;
1387 FETCH csr_k_old_forecast INTO l_old_follow_up_date,
1388 l_old_follow_up_action,
1389 l_old_est_rev_percent,
1390 l_old_est_rev_date;
1391 CLOSE csr_k_old_forecast;
1392
1393 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1394 THEN
1395 fnd_log.STRING (fnd_log.level_statement,
1396 g_module ||
1397 l_api_name,
1398 '211:l_old_follow_up_date = ' ||l_old_follow_up_date
1399 );
1400 fnd_log.STRING (fnd_log.level_statement,
1401 g_module ||
1402 l_api_name,
1403 '211:l_old_follow_up_action = ' ||l_old_follow_up_action
1404 );
1405 fnd_log.STRING (fnd_log.level_statement,
1406 g_module ||
1407 l_api_name,
1408 '211:l_old_est_rev_percent = ' ||l_old_est_rev_percent
1409 );
1410 fnd_log.STRING (fnd_log.level_statement,
1411 g_module ||
1412 l_api_name,
1413 '211:l_old_est_rev_date = ' ||l_old_est_rev_date
1414 );
1415 END IF;
1416
1417
1418
1419 IF (p_status_code IS NOT NULL) THEN
1420 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1421 THEN
1422 fnd_log.STRING (fnd_log.level_statement,
1423 g_module ||
1424 l_api_name,
1425 '211:Calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS API'
1426 );
1427 END IF;
1428 --Updating Contract Status
1429 OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS(
1430 x_return_status => x_return_status,
1431 x_msg_data => x_msg_data,
1432 x_msg_count => x_msg_count,
1433 p_init_msg_list => FND_API.G_TRUE,
1434 p_id => p_chr_id,
1435 p_new_sts_code => p_status_code,
1436 p_canc_reason_code => p_reason_code,
1437 p_old_sts_code => l_old_status_code,
1438 p_comments => p_comments,
1439 p_term_cancel_source => 'MANUAL',
1440 p_date_cancelled => sysdate,
1441 p_validate_status => 'Y');
1442
1443 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1444 THEN
1445 fnd_log.STRING (fnd_log.level_statement,
1446 g_module ||
1447 l_api_name,
1448 '212:x_return_status after calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS: ' ||p_chr_id||': '||x_return_status);
1449 fnd_log.STRING (fnd_log.level_statement,
1450 g_module ||
1451 l_api_name,
1452 '213:x_msg_data after calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS for ' ||p_chr_id||': '||x_msg_data);
1453 fnd_log.STRING (fnd_log.level_statement,
1454 g_module ||
1455 l_api_name,
1456 '214:x_msg_count after calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS for ' ||p_chr_id||': '||x_msg_count);
1457 END IF;
1458
1459 --- If any errors happen abort API
1460 IF (NVL(x_return_status,'U') = g_ret_sts_unexp_error) THEN
1461 RAISE fnd_api.g_exc_unexpected_error;
1462 ELSIF (x_return_status = g_ret_sts_error) THEN
1463 RAISE fnd_api.g_exc_error;
1464 END IF;
1465 --OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS API already updated minor version
1466 l_minor_version_updated := 'T';
1467
1468 END IF;--end Update Contract Status
1469
1470
1471
1472
1473 --if contract status got updated then update the jtf notes otherwise return error status
1474 if(p_contract_notes IS NOT NULL) then
1475 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1476 THEN
1477 fnd_log.STRING (fnd_log.level_statement,
1478 g_module ||
1479 l_api_name,
1480 '215:Calling JTF_NOTES_PUB.CREATE_NOTE API');
1481 END IF;
1482 --Updating jtf Notes
1483 JTF_NOTES_PUB.CREATE_NOTE(
1484 p_api_version => l_api_version,
1485 p_init_msg_list => l_init_msg_list,
1486 p_commit => 'F',
1487 p_validation_level => 100,
1488 x_return_status => x_return_status,
1489 x_msg_count => x_msg_count,
1490 x_msg_data => x_msg_data ,
1491 p_org_id => NULL,
1492 p_source_object_id => p_chr_id,
1493 p_source_object_code => 'OKS_HDR_NOTE',
1494 p_notes => p_contract_notes,
1495 p_note_status => 'I', --public status
1496 p_entered_by => FND_GLOBAL.USER_ID,
1497 p_entered_date => SYSDATE ,
1498 x_jtf_note_id => l_jtf_note_id,
1499 p_last_update_date => sysdate,
1500 p_last_updated_by => FND_GLOBAL.USER_ID,
1501 p_creation_date => SYSDATE,
1502 p_created_by => FND_GLOBAL.USER_ID,
1503 p_last_update_login => FND_GLOBAL.LOGIN_ID,
1504 p_note_type => 'OKS_ADMIN');
1505
1506 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1507 THEN
1508 fnd_log.STRING (fnd_log.level_statement,
1509 g_module ||
1510 l_api_name,
1511 '216:l_jtf_note_id after calling JTF_NOTES_PUB.CREATE_NOTE: ' ||p_chr_id||': '||l_jtf_note_id);
1512 fnd_log.STRING (fnd_log.level_statement,
1513 g_module ||
1514 l_api_name,
1515 '217:x_return_status after calling JTF_NOTES_PUB.CREATE_NOTE: ' ||p_chr_id||': '||x_return_status);
1516 fnd_log.STRING (fnd_log.level_statement,
1517 g_module ||
1518 l_api_name,
1519 '218:x_msg_data after calling JTF_NOTES_PUB.CREATE_NOTE for ' ||p_chr_id||': '||x_msg_data);
1520 fnd_log.STRING (fnd_log.level_statement,
1521 g_module ||
1522 l_api_name,
1523 '219:x_msg_count after calling JTF_NOTES_PUB.CREATE_NOTE for ' ||p_chr_id||': '||x_msg_count);
1524 END IF;
1525
1526 --- If any errors happen abort API
1527 IF (NVL(x_return_status,'U') = g_ret_sts_unexp_error) THEN
1528 fnd_message.set_name('OKS','OKS_JTF_NOTES_FAILED');
1529 x_msg_data := fnd_message.get;
1530 x_msg_count := 1;
1531 RAISE fnd_api.g_exc_unexpected_error;
1532 ELSIF (x_return_status = g_ret_sts_error) THEN
1533 fnd_message.set_name('OKS','OKS_JTF_NOTES_FAILED');
1534 x_msg_data := fnd_message.get;
1535 x_msg_count := 1;
1536 RAISE fnd_api.g_exc_error;
1537 END IF;
1538
1539 END IF;--end Update jtf Notes
1540
1541 /* Added by sjanakir FP for Bug# 7147899 */
1542 IF (p_est_percent IS NOT NULL AND p_est_date IS NULL AND l_old_est_rev_date IS NULL) OR
1543 (p_est_date IS NOT NULL AND p_est_percent IS NULL AND l_old_est_rev_percent IS NULL)
1544 THEN
1545 fnd_message.set_name('OKS','OKS_FORECAST_UPDATE_FAILED');
1546 x_msg_data:= fnd_message.get;
1547 x_return_status := g_ret_sts_error;
1548 x_msg_count := 1;
1549
1550 ELSE
1551 -- bug 5934875,
1552 IF ( NVL(p_est_percent,999) <> NVL(l_old_est_rev_percent,999) ) OR
1553 ( NVL(p_est_date,SYSDATE) <> NVL(l_old_est_rev_date,SYSDATE) ) OR
1554 ( NVL(p_action,'XYZ') <> NVL(l_old_follow_up_action,'XYZ') ) OR
1555 ( NVL(p_due_date,SYSDATE) <> NVL(l_old_follow_up_date,SYSDATE) ) THEN
1556
1557 --Update Follow Up Action and Date, Forecast Percent and Date
1558 -- if ((p_est_percent IS NOT NULL) OR (p_action IS NOT NULL) ) then
1559
1560 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1561 THEN
1562 fnd_log.STRING (fnd_log.level_statement,
1563 g_module ||
1564 l_api_name,
1565 '220:Now Updating Follow Up Action and Date, Forecast Percent and Date in oks_k_headers_b');
1566 END IF;
1567 /*modified for bug7034006*/
1568 --Updating Follow Up
1569 Update oks_k_headers_b
1570 set follow_up_date = p_due_date,
1571 follow_up_action = p_action,
1572 est_rev_percent = p_est_percent,
1573 est_rev_date = p_est_date,
1574 last_update_date = SYSDATE,
1575 last_updated_by = FND_GLOBAL.USER_ID,
1576 last_update_login = FND_GLOBAL.LOGIN_ID
1577 where chr_id = p_chr_id;
1578 /*added for bug7034006*/
1579 Update okc_k_headers_all_b okcb
1580 set last_update_date = SYSDATE,
1581 last_updated_by = FND_GLOBAL.USER_ID,
1582 last_update_login = FND_GLOBAL.LOGIN_ID
1583 where okcb.id = p_chr_id;
1584
1585 if(SQL%ROWCOUNT =1) then
1586 x_return_status := fnd_api.g_ret_sts_success;
1587 else
1588 l_sql_err := SQLERRM;
1589 fnd_message.set_name('OKS','OKS_FOREACST_FOLLOWUP_ERROR');
1590 fnd_message.set_token ('ERROR_MESSAGE', l_sql_err);
1591
1592 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1593 THEN
1594 fnd_log.STRING (fnd_log.level_statement,
1595 g_module ||
1596 l_api_name,
1597 '221a:Forecast and Follow Up update failed - l_sql_err:'||l_sql_err);
1598 END IF;
1599
1600 x_msg_data:= fnd_message.get;
1601 x_return_status := g_ret_sts_error;
1602 x_msg_count := 1;
1603 end if;
1604 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1605 THEN
1606 fnd_log.STRING (fnd_log.level_statement,
1607 g_module ||
1608 l_api_name,
1609 '221:Inside Update Follow Up and Forecast part - x_return_status:'||x_return_status);
1610 END IF;
1611
1612 --- If any errors happen abort API
1613 IF (NVL(x_return_status,'U') = g_ret_sts_unexp_error) THEN
1614 RAISE fnd_api.g_exc_unexpected_error;
1615 ELSIF (x_return_status = g_ret_sts_error) THEN
1616 RAISE fnd_api.g_exc_error;
1617 END IF;
1618
1619 end if;--end Update Follow Up Action and Date
1620
1621
1622 ---Update Renewal Notes - kkolukul
1623
1624 IF(p_renewal_notes IS NOT NULL) THEN
1625
1626 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1627 fnd_log.STRING (fnd_log.level_statement,
1628 g_module ||
1629 l_api_name,
1630 '222:Now Updating Renewal Comment in oks_k_headers_b');
1631 END IF;
1632
1633 UPDATE oks_k_headers_b
1634 SET renewal_comment = p_renewal_notes,
1635 last_update_date = SYSDATE,
1636 last_updated_by = FND_GLOBAL.USER_ID,
1637 last_update_login = FND_GLOBAL.LOGIN_ID
1638 WHERE chr_id = p_chr_id;
1639
1640 UPDATE okc_k_headers_all_b okcb
1641 SET last_update_date = SYSDATE,
1642 last_updated_by = FND_GLOBAL.USER_ID,
1643 last_update_login = FND_GLOBAL.LOGIN_ID
1644 WHERE okcb.id = p_chr_id;
1645
1646
1647 --- If any errors happen abort API
1648 IF (NVL(x_return_status,'U') = g_ret_sts_unexp_error) THEN
1649 RAISE fnd_api.g_exc_unexpected_error;
1650 ELSIF (x_return_status = g_ret_sts_error) THEN
1651 RAISE fnd_api.g_exc_error;
1652 END IF;
1653
1654 END IF; --End: Update Renewal Notes - kkolukul
1655
1656 --Update minor version
1657 if (l_minor_version_updated = 'F') THEN
1658 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1659 THEN
1660 fnd_log.STRING (fnd_log.level_statement,
1661 g_module ||
1662 l_api_name,
1663 '224:Now Updating minor_version in okc_k_vers_numbers');
1664 END IF;
1665
1666 --Updating minor_version
1667 /*commented and modified for bug7034006*/
1668 /* Update okc_k_vers_numbers
1669 Set minor_version = minor_version + 1
1670 Where chr_id = p_chr_id;
1671
1672 if(SQL%ROWCOUNT =1) then
1673 x_return_status := fnd_api.g_ret_sts_success;
1674 else
1675 l_sql_err := SQLERRM;
1676 fnd_message.set_name('OKS','OKS_K_VERSION_UPD_FAILED');
1677 fnd_message.set_token ('ERROR_MESSAGE', l_sql_err);
1678
1679 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1680 THEN
1681 fnd_log.STRING (fnd_log.level_statement,
1682 g_module ||
1683 l_api_name,
1684 '225a:Follow Up update failed - l_sql_err:'||l_sql_err);
1685 END IF;
1686
1687 x_msg_data:= fnd_message.get;
1688 x_return_status := g_ret_sts_error;
1689 x_msg_count := 1;
1690 end if;
1691 */
1692 x_return_status := OKC_CVM_PVT.update_minor_version(p_chr_id => p_chr_id);
1693 IF x_return_status <> G_RET_STS_SUCCESS THEN
1694 l_sql_err := SQLERRM;
1695 fnd_message.set_name('OKS','OKS_K_VERSION_UPD_FAILED');
1696 fnd_message.set_token ('ERROR_MESSAGE', l_sql_err);
1697
1698 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1699 fnd_log.STRING (fnd_log.level_statement, g_module ||l_api_name,
1700 '225a:Follow Up update failed - l_sql_err:'||l_sql_err);
1701 END IF;
1702
1703 x_msg_data:= fnd_message.get;
1704 x_return_status := g_ret_sts_error;
1705 x_msg_count := 1;
1706 END IF;
1707
1708
1709 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1710 THEN
1711 fnd_log.STRING (fnd_log.level_statement,
1712 g_module ||
1713 l_api_name,
1714 '225:Inside Update minor version part - x_return_status:'||x_return_status);
1715 END IF;
1716
1717 --- If any errors happen abort API
1718 IF (NVL(x_return_status,'U') = g_ret_sts_unexp_error) THEN
1719 RAISE fnd_api.g_exc_unexpected_error;
1720 ELSIF (x_return_status = g_ret_sts_error) THEN
1721 RAISE fnd_api.g_exc_error;
1722 END IF;
1723 end if;--end update minor version
1724
1725 commit work;
1726
1727 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1728 THEN
1729 fnd_log.STRING (fnd_log.level_statement,
1730 g_module ||
1731 l_api_name,
1732 '226:Changes commited');
1733 fnd_log.STRING (fnd_log.level_statement,
1734 g_module ||
1735 l_api_name,
1736 '227:Final x_return_status:'||x_return_status);
1737 END IF;
1738
1739 /* Added by sjanakir FP for Bug# 7147899 */
1740 END IF;
1741
1742 EXCEPTION
1743 WHEN fnd_api.g_exc_error THEN
1744 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name);
1745
1746 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1747 fnd_log.STRING (fnd_log.level_statement,
1748 g_module ||
1749 l_api_name,
1750 '2000: Leaving ' ||
1751 g_pkg_name ||
1752 '.' ||
1753 l_api_name
1754 );
1755 END IF;
1756
1757 x_return_status := g_ret_sts_error;
1758
1759 fnd_msg_pub.count_and_get (p_encoded => 'F',
1760 p_count => x_msg_count,
1761 p_data => x_msg_data
1762 );
1763 WHEN fnd_api.g_exc_unexpected_error THEN
1764 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name);
1765
1766 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1767 fnd_log.STRING (fnd_log.level_statement,
1768 g_module ||
1769 l_api_name,
1770 '3000: Leaving ' ||
1771 g_pkg_name ||
1772 '.' ||
1773 l_api_name
1774 );
1775 END IF;
1776
1777 x_return_status := g_ret_sts_unexp_error;
1778
1779 fnd_msg_pub.count_and_get (p_encoded => 'F',
1780 p_count => x_msg_count,
1781 p_data => x_msg_data
1782 );
1783
1784 WHEN OTHERS THEN
1785 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name);
1786
1787 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1788 THEN
1789 fnd_log.STRING (fnd_log.level_statement,
1790 g_module ||
1791 l_api_name,
1792 'Leaving ' ||
1793 g_pkg_name ||
1794 '.' ||
1795 l_api_name ||
1796 ' from OTHERS sqlcode = ' ||
1797 SQLCODE ||
1798 ', sqlerrm = ' ||
1799 SQLERRM
1800 );
1801 fnd_msg_pub.add_exc_msg (g_pkg_name,
1802 l_api_name,
1803 SUBSTR (SQLERRM,
1804 1,
1805 240
1806 )
1807 );
1808 END IF;
1809
1810 fnd_msg_pub.count_and_get (p_encoded => 'F',
1811 p_count => x_msg_count,
1812 p_data => x_msg_data
1813 );
1814 x_return_status := g_ret_sts_unexp_error;
1815
1816 END update_single_contracts;
1817
1818
1819 END OKS_K_ACTIONS_PVT;
1820