[Home] [Help]
PACKAGE BODY: APPS.OKC_PO_MOD_PKG
Source
1 PACKAGE BODY okc_po_mod_pkg AS
2 /* $Header: OKCPMODB.pls 120.2.12020000.3 2012/07/20 12:16:33 harchand ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'okc_po_mod_pkg';
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
9
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
12 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
13 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
14
15 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
16 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
17
18 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
19 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
20 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
21
22 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
23 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
24 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
25 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
26
27 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
28 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
29 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
30 G_LOCK_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_LOCK_RECORD_DELETED;
31
32
33
34 /*This procedure will get the readable format of the messages*/
35 PROCEDURE read_message (x_message IN OUT NOCOPY VARCHAR2)
36 IS
37 l_message VARCHAR2 (2000);
38 BEGIN
39 FOR i IN 1 .. fnd_msg_pub.count_msg
40 LOOP
41 l_message := fnd_msg_pub.get (i, p_encoded => fnd_api.g_false);
42
43 IF (LENGTH (l_message) + LENGTH (Nvl(x_message,' '))) <= 2500
44 THEN
45 x_message := x_message || l_message;
46 ELSE
47 EXIT;
48 END IF;
49 END LOOP;
50 EXCEPTION
51 WHEN OTHERS
52 THEN
53 NULL;
54 END read_message;
55
56 /* This function will get the default section code from the article definition. It will check only standard clauses.
57 First it will verify if the additional section mappipng is available for this clause. If its defined
58 get the default section information from the add section mapping. Else pick the 'default section' on the clause.
59 If this also null, throw an error message*/
60
61 PROCEDURE get_clause_default_section(p_document_type IN VARCHAR2,
62 p_document_id IN NUMBER,
63 p_article_version_id IN NUMBER,
64 x_section_code OUT NOCOPY VARCHAR2,
65 x_return_status IN OUT NOCOPY VARCHAR2)
66 IS
67
68 l_article_id NUMBER;
69 l_section_code VARCHAR2(240);
70 l_api_name VARCHAR2(240) := 'get_clause_default_section';
71
72 l_article_title varchar2(450);
73
74 l_msg_count NUMBER;
75 l_msg_data VARCHAR2(4000);
76
77 BEGIN
78
79 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
80 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered get_clause_default_section');
81 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_article_version_id ' || p_article_version_id);
82 END IF;
83
84 -- Standard Start of API savepoint
85 SAVEPOINT g_clause_default_section_GRP;
86
87 /*article_version_id is the new version which has to be added to the PO's*/
88 SELECT article_id INTO l_article_id
89 FROM okc_article_versions
90 WHERE article_version_id = p_article_version_id;
91
92 OKC_CLM_PKG.GET_DEFAULT_SCN_CODE (p_api_version => 1.0,
93 p_article_id => l_article_id,
94 p_article_version_id => p_article_version_id,
95 p_doc_id => p_document_id,
96 p_doc_type => p_document_type,
97 x_default_scn_code => l_section_code,
98 x_return_status => x_return_status);
99
100 IF l_section_code IS NULL THEN
101 SELECT default_section INTO l_section_code
102 FROM okc_article_versions
103 WHERE article_version_id = p_article_version_id;
104 END IF;
105
106 IF l_section_code IS NULL THEN
107 select article_title into l_article_title
108 from okc_articles_all
109 where article_id = l_article_id;
110
111 --Clause does not have a default section. So error out.
112 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
113 p_msg_name => 'OKC_NO_DEFAULT_SCN_FOR_CLAUSE',
114 p_token1 => 'P_CLS_TITLE',
115 p_token1_value => l_article_title);
116 x_return_status := G_RET_STS_ERROR;
117 -- RAISE FND_API.G_EXC_ERROR ;
118 END IF;
119
120 x_section_code := l_section_code;
121
122 EXCEPTION
123
124 WHEN FND_API.G_EXC_ERROR THEN
125
126 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
127 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving update_variable_values: OKC_API.G_EXCEPTION_ERROR Exception');
128 END IF;
129
130 ROLLBACK TO g_clause_default_section_GRP;
131 x_return_status := G_RET_STS_ERROR ;
132 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
133
134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
135 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving update_variable_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
137 END IF;
138
139 ROLLBACK TO g_clause_default_section_GRP;
140 x_return_status := G_RET_STS_UNEXP_ERROR ;
141 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
142
143 WHEN OTHERS THEN
144 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving update_variable_values because of EXCEPTION: '||sqlerrm);
146 END IF;
147
148 ROLLBACK TO g_clause_default_section_GRP;
149 x_return_status := G_RET_STS_UNEXP_ERROR ;
150 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
151 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
152 END IF;
153 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
154
155 END get_clause_default_section;
156
157 PROCEDURE upd_mulmod_clause_changes(p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
158 p_multi_mod_req_id IN NUMBER,
159 p_document_type IN VARCHAR2,
160 p_document_id IN NUMBER,
161 p_org_id IN NUMBER,
162 x_updated_yn OUT NOCOPY VARCHAR2,
163 x_return_status OUT NOCOPY VARCHAR2
164 ,p_locking_enabled_yn IN VARCHAR2 DEFAULT 'N')
165 IS
166
167 l_api_name VARCHAR2(240) := 'upd_mulmod_clause_changes';
168
169 l_dummy VARCHAR2(450);
170
171 l_return_status VARCHAR2(1);
172 l_msg_data VARCHAR2(4000);
173 l_msg_count NUMBER;
174
175 l_section_source VARCHAR2(240);
176 l_section_code VARCHAR2(240);
177 l_section_id NUMBER;
178 l_contract_clause_id NUMBER;
179 l_document_type VARCHAR2(240);
180 l_mod_document_id NUMBER;
181 l_old_article_version_id NUMBER;
182 l_new_article_version_id NUMBER;
183 l_display_sequence NUMBER;
184 l_mode VARCHAR2(10);
185 l_terms_exist VARCHAR2(1);
186 l_updated_yn VARCHAR2(1) := 'N';
187
188 l_message VARCHAR2(2500);
189
190
191 CURSOR c_get_clause_changes_csr IS
192 SELECT clause_action_code, old_article_version_id, new_article_version_id
193 FROM po_multi_mod_clause_changes
194 WHERE multi_mod_request_id = p_multi_mod_req_id;
195
196 CURSOR c_get_section_id_csr(p_scn_code VARCHAR2) IS
197 SELECT scn_id
198 FROM okc_sections_b
199 WHERE document_type = p_document_type
200 AND document_id = p_document_id
201 AND scn_code = p_scn_code;
202
203 CURSOR c_get_old_article_details_csr(p_article_version_id NUMBER ) IS
204 SELECT id, scn_id, display_sequence
205 FROM okc_k_articles_b
206 WHERE document_type = p_document_type
207 AND document_id = p_document_id
208 AND article_version_id = p_article_version_id;
209
210 CURSOR l_tmpl_doc_exist_csr IS
211 SELECT 1
212 FROM
213 OKC_TEMPLATE_USAGES
214 WHERE document_type = p_document_type
215 AND document_id = p_document_id;
216
217 CURSOR c_get_article_details_csr(p_article_version_id IN NUMBER) IS
218 SELECT
219 article_id,
220 article_title,
221 article_version_id,
222 article_version_number,
223 article_intent,
224 provision_yn,
225 article_status,
226 start_date,
227 end_date
228 FROM
229 okc_articles_local_v ART
230 WHERE ART.article_version_id = p_article_version_id
231 AND ART.org_id = p_org_id
232 UNION
233 SELECT
234 article_id,
235 article_title,
236 article_version_id,
237 article_version_number,
238 article_intent,
239 provision_yn,
240 article_status,
241 start_date,
242 end_date
243 FROM
244 okc_articles_global_v ART
245 WHERE ART.article_version_id = p_article_version_id
246 AND ART.org_id = p_org_id;
247 l_get_art_details_rec c_get_article_details_csr%ROWTYPE;
248
249
250 BEGIN
251
252 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
253 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered val_mulmod_clause_changes');
254 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_multi_mod_req_id ' || p_multi_mod_req_id);
255 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_document_type ' || p_document_type);
256 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_document_id ' || p_document_id);
257 END IF;
258
259 OKC_API.init_msg_list(p_init_msg_list);
260
261 -- Standard Start of API savepoint
262 SAVEPOINT g_upd_clause_changes_GRP;
263
264 -- Initialize API return status to success
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266
267 /*Check if terms are applied on the PO. If no then we cannot progress
268 with adding or deleting or replacing the clauses. Return error.*/
269 OPEN l_tmpl_doc_exist_csr;
270 FETCH l_tmpl_doc_exist_csr INTO l_terms_exist ;
271 IF l_tmpl_doc_exist_csr%NOTFOUND THEN
272 --Document does not have a template applied
273 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
274 p_msg_name => 'OKC_TERMS_DOES_NOT_EXIST');
275 x_return_status := FND_API.G_RET_STS_SUCCESS;
276 RAISE FND_API.G_EXC_ERROR ;
277 END IF;
278 CLOSE l_tmpl_doc_exist_csr;
279
280 l_mode := 'AMEND';
281
282 /*For each PO in the Multi Mod Request Id, For each row in the po_multi_mod_clause_changes,
283 perform the action specified*/
284
285 FOR c_get_clause_changes_csr_rec IN c_get_clause_changes_csr LOOP
286
287 l_old_article_version_id := c_get_clause_changes_csr_rec.old_article_version_id;
288 l_new_article_version_id := c_get_clause_changes_csr_rec.new_article_version_id;
289
290 /*If the action is Add or replace, we need to add the clause to the mod Document. */
291 /*Verify if the clause is available for that PO's OU*/
292
293 IF (c_get_clause_changes_csr_rec.clause_action_code IN ('ADD', 'REPLACE')) THEN
294 OPEN c_get_article_details_csr(l_new_article_version_id);
295 FETCH c_get_article_details_csr INTO l_get_art_details_rec ;
296 IF c_get_article_details_csr%NOTFOUND THEN
297 --Document does not have a template applied
298 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
299 p_msg_name => 'OKC_TERMS_INV_CLS_NAME',
300 p_token1 => 'P_CLS_TITLE',
301 p_token1_value => l_dummy);
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303 --RAISE FND_API.G_EXC_ERROR ;
304 CONTINUE;
305 END IF;
306 CLOSE c_get_article_details_csr;
307 END IF;
308
309 IF c_get_clause_changes_csr_rec.clause_action_code = 'ADD' THEN
310
311 /* We will need a section to add a clause.
312 Derive the default SECTION information from the clause definition. Add the section if the section is not
313 already added in the document. Get the section Id and pass to the okc_terms_migrate_grp API Add_Standard_Clause*/
314
315 /*Get the default section*/
316 get_clause_default_section(p_document_type => p_document_type,
317 p_document_id => p_document_id,
318 p_article_version_id => l_new_article_version_id,
319 x_section_code => l_section_code,
320 x_return_status => l_return_status);
321
322 IF(l_section_code IS NOT NULL AND l_return_status = 'S') THEN
323 OPEN c_get_section_id_csr(l_section_code);
324 FETCH c_get_section_id_csr INTO l_section_id;
325 CLOSE c_get_section_id_csr;
326
327 --is_sect_added_in_doc(p_document_type => , p_document_id => draft_id, p_section_code => l_section_code)
328 IF(l_section_id IS NULL) THEN
329
330 /*If section is not already there on the document, Add the section*/
331 OKC_TERMS_MIGRATE_GRP.Add_Section ( p_api_version => 1.0,
332 x_return_status => l_return_status,
333 x_msg_data => l_msg_data,
334 x_msg_count => l_msg_count,
335 p_document_type => p_document_type,
336 p_document_id => p_document_id,
337 p_section_source => 'LIBRARY',
338 p_section_name => l_section_code,
339 x_section_id => l_section_id);
340 END IF;
341
342 --Add the clause to the section obtained above
343 okc_terms_migrate_grp.Add_Standard_Clause ( p_api_version => 1.0,
344 x_return_status => l_return_status,
345 x_msg_data => l_msg_data,
346 x_msg_count => l_msg_count,
347 p_document_type => p_document_type,
348 p_document_id => p_document_id,
349 p_section_id => l_section_id,
350 p_clause_version_id => l_new_article_version_id,
351 p_mode => l_mode,
352 x_contract_clause_id => l_contract_clause_id);
353
354 ELSE
355 /*Print error that section is null and so cannot add a clause to the PO*/
356 --Clause does not have a default section. So error out.
357 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
358 p_msg_name => 'OKC_NO_DEFAULT_SCN_FOR_CLAUSE',
359 p_token1 => 'P_CLS_TITLE',
360 p_token1_value => l_dummy);
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362 -- RAISE FND_API.G_EXC_ERROR ;
363 CONTINUE;
364
365 END IF; --IF(l_section_code IS NOT NULL AND l_return_status = 'S')
366
367 IF (l_return_status = 'S' AND l_updated_yn = 'N') THEN
368 l_updated_yn := 'Y';
369 END IF;
370
371 ELSIF c_get_clause_changes_csr_rec.clause_action_code = 'DELETE' THEN
372
373 /*If the action is Delete, we need to delete the clause from the mod Document.*/
374
375 --This loop will get executed inside the call remove_std_art_from_doc
376 FOR get_old_article_details_rec IN c_get_old_article_details_csr( l_old_article_version_id ) LOOP
377
378 /* okc_terms_migrate_grp.remove_std_clause_from_doc(p_api_version => 1.0,
379 p_mode => l_mode,
380 p_document_type => p_document_type,
381 p_document_id => p_document_id,
382 p_clause_version_id => l_old_article_version_id,
383 p_renumber_terms => 'N',
384 x_return_status => l_return_status,
385 x_msg_data => l_msg_data,
386 x_msg_count => l_msg_count); */
387
388 okc_terms_migrate_grp.remove_clause_id_from_doc(p_api_version => 1.0,
389 p_mode => l_mode,
390 p_document_type => p_document_type,
391 p_document_id => p_document_id,
392 p_clause_id => get_old_article_details_rec.id,
393 p_renumber_terms => 'N',
394 x_return_status => l_return_status,
395 x_msg_data => l_msg_data,
396 x_msg_count => l_msg_count);
397 END LOOP;
398
399 IF (l_updated_yn = 'N' AND l_return_status = 'S') THEN
400 l_updated_yn := 'Y';
401 END IF;
402
403 ELSIF c_get_clause_changes_csr_rec.clause_action_code = 'REPLACE' THEN
404
405 /* Find the section and the clause sequence in the document.
406 Delete the clause. Add the clause in the same section and at the same sequence.*/
407
408 FOR get_old_article_details_rec IN c_get_old_article_details_csr( l_old_article_version_id ) LOOP
409 l_section_id := get_old_article_details_rec.scn_id;
410 l_display_sequence := get_old_article_details_rec.display_sequence;
411
412 okc_terms_migrate_grp.remove_clause_id_from_doc(p_api_version => 1.0,
413 p_mode => l_mode,
414 p_document_type => p_document_type,
415 p_document_id => p_document_id,
416 p_clause_id => get_old_article_details_rec.id,
417 p_renumber_terms => 'N',
418 x_return_status => l_return_status,
419 x_msg_data => l_msg_data,
420 x_msg_count => l_msg_count);
421
422 IF (l_return_status = 'S') THEN
423
424 okc_terms_migrate_grp.Add_Standard_Clause ( p_api_version => 1.0,
425 x_return_status => l_return_status,
426 x_msg_data => l_msg_data,
427 x_msg_count => l_msg_count,
428 p_document_type => p_document_type,
429 p_document_id => p_document_id,
430 p_section_id => l_section_id, -- target section_id
431 p_clause_version_id => l_new_article_version_id,
432 p_display_sequence => l_display_sequence,
433 p_mode => l_mode,
434 x_contract_clause_id => l_contract_clause_id);
435 END IF;
436 END LOOP;
437
438 IF (l_return_status = 'S' AND l_updated_yn = 'N') THEN
439 l_updated_yn := 'Y';
440 END IF;
441 END IF; --IF c_get_clause_changes_csr_rec.clause_action_code = 'ADD'
442
443 END LOOP;
444
445 --x_return_status := l_return_status;
446 x_updated_yn:= l_updated_yn;
447
448 /*Update messages in the Concurrent Program Log*/
449 FOR i IN 1 .. fnd_msg_pub.count_msg
450 LOOP
451 l_message := fnd_msg_pub.get (i, p_encoded => fnd_api.g_false);
452 FND_FILE.put_line (FND_FILE.LOG, substrb('OKC' || ': ' || l_message, 1, 2000));
453 END LOOP;
454
455 EXCEPTION
456
457 WHEN FND_API.G_EXC_ERROR THEN
458
459 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
460 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving update_variable_values: OKC_API.G_EXCEPTION_ERROR Exception');
461 END IF;
462 IF c_get_clause_changes_csr%ISOPEN THEN
463 CLOSE c_get_clause_changes_csr;
464 END IF;
465
466 IF c_get_section_id_csr%ISOPEN THEN
467 CLOSE c_get_section_id_csr;
468 END IF;
469
470 IF c_get_old_article_details_csr%ISOPEN THEN
471 CLOSE c_get_old_article_details_csr;
472 END IF;
473
474 IF l_tmpl_doc_exist_csr%ISOPEN THEN
475 CLOSE l_tmpl_doc_exist_csr;
476 END IF;
477
478 IF c_get_article_details_csr%ISOPEN THEN
479 CLOSE c_get_article_details_csr;
480 END IF;
481
482 --ROLLBACK TO g_upd_clause_changes_GRP;
483 x_return_status := FND_API.G_RET_STS_SUCCESS;
484 x_updated_yn := l_updated_yn;
485 read_message(x_message => l_msg_data);
486 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
487
488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
489 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving update_variable_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
491 END IF;
492
493 ROLLBACK TO g_upd_clause_changes_GRP;
494 x_return_status := G_RET_STS_UNEXP_ERROR ;
495 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
496
497 WHEN OTHERS THEN
498 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
499 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving update_variable_values because of EXCEPTION: '||sqlerrm);
500 END IF;
501
502 ROLLBACK TO g_upd_clause_changes_GRP;
503 x_return_status := G_RET_STS_UNEXP_ERROR ;
504 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
505 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
506 END IF;
507 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
508
509 END upd_mulmod_clause_changes;
510
511 PROCEDURE delete_mulmod_clause_changes(p_multi_mod_req_id IN NUMBER,
512 x_return_status OUT NOCOPY VARCHAR2)
513 IS
514 l_return_status VARCHAR2(1);
515 l_msg_count NUMBER;
516 l_msg_data VARCHAR2(4000);
517
518 l_api_name VARCHAR2(100) := 'delete_mulmod_clause_changes';
519
520 BEGIN
521
522
523 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
524 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered delete_mulmod_clause_changes');
525 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_multi_mod_req_id ' || p_multi_mod_req_id);
526 END IF;
527
528 FND_MSG_PUB.initialize;
529
530 -- Standard Start of API savepoint
531 SAVEPOINT g_del_clause_changes_GRP;
532
533 DELETE FROM po_multi_mod_clause_changes
534 WHERE multi_mod_request_id = p_multi_mod_req_id;
535
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving delete_mulmod_clause_changes');
540 END IF;
541
542 EXCEPTION
543
544 WHEN FND_API.G_EXC_ERROR THEN
545
546 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving delete_mulmod_clause_changes: OKC_API.G_EXCEPTION_ERROR Exception');
548 END IF;
549
550 ROLLBACK TO g_del_clause_changes_GRP;
551 x_return_status := G_RET_STS_ERROR ;
552 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
553
554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
555 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
556 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving delete_mulmod_clause_changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
557 END IF;
558
559 ROLLBACK TO g_del_clause_changes_GRP;
560 x_return_status := G_RET_STS_UNEXP_ERROR ;
561 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
562
563 WHEN OTHERS THEN
564 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
565 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving delete_mulmod_clause_changes because of EXCEPTION: '||sqlerrm);
566 END IF;
567
568 ROLLBACK TO g_del_clause_changes_GRP;
569 x_return_status := G_RET_STS_UNEXP_ERROR ;
570 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
571 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
572 END IF;
573 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => l_msg_count, p_data => l_msg_data );
574
575 END delete_mulmod_clause_changes;
576
577 FUNCTION is_clause_change_requested(p_multi_mod_req_id IN NUMBER)
578 RETURN VARCHAR2
579 IS
580 l_api_name VARCHAR2(100) := 'is_clause_change_requested' ;
581 l_count NUMBER := 0;
582 l_change_requested VARCHAR2(1) := 'N';
583
584 BEGIN
585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
586 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered is_clause_change_requested');
587 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_multi_mod_req_id ' || p_multi_mod_req_id);
588 END IF;
589
590 BEGIN
591
592 SELECT Count(*) INTO l_count
593 FROM po_multi_mod_clause_changes
594 WHERE multi_mod_request_id = p_multi_mod_req_id;
595
596 EXCEPTION
597 WHEN no_data_found THEN
598 l_change_requested := 'N';
599
600 END;
601 IF(l_count > 0) THEN
602 l_change_requested := 'Y';
603
604 END IF;
605
606 RETURN l_change_requested;
607
608 EXCEPTION
609 WHEN OTHERS THEN
610 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
611 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving is_clause_change_requested because of EXCEPTION: '||sqlerrm);
612 END IF;
613 RAISE;
614
615 END is_clause_change_requested;
616
617 PROCEDURE multimod_clause_presubmit(p_multi_mod_req_id IN NUMBER,
618 x_result_type OUT NOCOPY VARCHAR2,
619 x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE,
620 x_return_status OUT NOCOPY VARCHAR2
621 ,p_locking_enabled_yn IN VARCHAR2 DEFAULT 'N')
622 IS
623 BEGIN
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625 END;
626
627 END okc_po_mod_pkg;