[Home] [Help]
PACKAGE BODY: APPS.OKC_PO_INT_GRP
Source
1 PACKAGE BODY OKC_PO_INT_GRP AS
2 /* $Header: OKCGIPOB.pls 120.2.12020000.2 2013/04/09 08:49:07 serukull ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_PO_INT_GRP';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
22 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
23 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
24 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
25 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR ;
26 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
27 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
28 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
29 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
30
31 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
32 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
33 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
34
35
36 Procedure get_article_variable_values(
37 p_api_version IN Number,
38 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
39 p_doc_type IN Varchar2,
40 p_doc_id IN Number,
41 p_sys_var_value_tbl IN OUT NOCOPY sys_var_value_tbl_type,
42 x_return_status OUT NOCOPY Varchar2,
43 x_msg_data OUT NOCOPY Varchar2,
44 x_msg_count OUT NOCOPY Number
45 )IS
46 l_api_version CONSTANT NUMBER := 1;
47 l_api_name CONSTANT VARCHAR2(30) := 'g_get_article_variable_values';
48 BEGIN
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 IF l_debug='Y' THEN
52 okc_util.print_trace(1,' Calling PO_CONTERMS_UTL_GRP.Get_PO_Attribute_values');
53 END IF;
54
55 PO_CONTERMS_UTL_GRP.Get_PO_Attribute_values(
56 p_api_version => l_api_version,
57 p_init_msg_list => FND_API.G_FALSE,
58 p_doc_type => p_doc_type,
59 p_doc_id => p_doc_id,
60 p_sys_var_value_tbl => p_sys_var_value_tbl,
61 x_return_status => x_return_status,
62 x_msg_data => x_msg_data,
63 x_msg_count => x_msg_count);
64
65 --------------------------------------------
66 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
68 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
69 RAISE FND_API.G_EXC_ERROR ;
70 END IF;
71 --------------------------------------------
72 IF l_debug='Y' THEN
73 okc_util.print_trace(1,' Leaving PO_CONTERMS_UTL_GRP.Get_PO_Attribute_values');
74 END IF;
75 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
76
77 EXCEPTION
78 WHEN FND_API.G_EXC_ERROR THEN
79
80 /*IF (l_debug = 'Y') THEN
81 okc_debug.log('2400: Leaving get_article_variable_values: OKC_API.G_EXCEPTION_ERROR Exception', 2);
82 END IF;*/
83
84 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
85 FND_LOG.STRING(G_EXCP_LEVEL,
86 G_PKG_NAME, '2400: Leaving get_article_variable_values: OKC_API.G_EXCEPTION_ERROR Exception' );
87 END IF;
88
89 x_return_status := G_RET_STS_ERROR ;
90 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
91
92 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
93 /*IF (l_debug = 'Y') THEN
94 okc_debug.log('2500: Leaving get_article_variable_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
95 END IF;*/
96
97 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
98 FND_LOG.STRING(G_EXCP_LEVEL,
99 G_PKG_NAME, '2500: Leaving get_article_variable_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
100 END IF;
101
102 x_return_status := G_RET_STS_UNEXP_ERROR ;
103 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
104 WHEN OTHERS THEN
105 /*IF (l_debug = 'Y') THEN
106 okc_debug.log('2600: Leaving get_article_variable_values because of EXCEPTION: '||sqlerrm, 2);
107 END IF;*/
108
109 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
110 FND_LOG.STRING(G_EXCP_LEVEL,
111 G_PKG_NAME, '2600: Leaving get_article_variable_values because of EXCEPTION: '||sqlerrm );
112 END IF;
113
114 x_return_status := G_RET_STS_UNEXP_ERROR ;
115
116 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
117 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
118 END IF;
119 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
120 END;
121
122 Procedure get_changed_variables(
123 p_api_version IN Number,
124 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
125 p_doc_type IN Varchar2,
126 p_doc_id IN Number,
127 p_sys_var_tbl IN OUT NOCOPY sys_var_tbl_type,
128 x_return_status OUT NOCOPY Varchar2,
129 x_msg_data OUT NOCOPY Varchar2,
130 x_msg_count OUT NOCOPY Number
131 ) IS
132 l_api_version CONSTANT NUMBER := 1;
133 l_api_name CONSTANT VARCHAR2(30) := 'g_get_changed_variables';
134 BEGIN
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 IF l_debug='Y' THEN
138 okc_util.print_trace(1,' Calling PO_CONTERMS_UTL_GRP.attribute_value_changed');
139 END IF;
140
141 PO_CONTERMS_UTL_GRP.attribute_value_changed(
142 p_api_version => l_api_version,
143 p_init_msg_list => FND_API.G_FALSE,
144 p_doc_type => p_doc_type,
145 p_doc_id => p_doc_id,
146 p_sys_var_tbl => p_sys_var_tbl,
147 x_return_status => x_return_status,
148 x_msg_data => x_msg_data,
149 x_msg_count => x_msg_count);
150
151 --------------------------------------------
152 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
154 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
155 RAISE FND_API.G_EXC_ERROR ;
156 END IF;
157 --------------------------------------------
158 IF l_debug='Y' THEN
159 okc_util.print_trace(1,' Leaving PO_CONTERMS_UTL_GRP.attribute_value_changed');
160 END IF;
161 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
162
163 EXCEPTION
164 WHEN FND_API.G_EXC_ERROR THEN
165
166 /*IF (l_debug = 'Y') THEN
167 okc_debug.log('2400: Leaving get_changed_variables: OKC_API.G_EXCEPTION_ERROR Exception', 2);
168 END IF;*/
169
170 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
171 FND_LOG.STRING(G_EXCP_LEVEL,
172 G_PKG_NAME, '2400: Leaving get_changed_variables: OKC_API.G_EXCEPTION_ERROR Exception' );
173 END IF;
174
175 x_return_status := G_RET_STS_ERROR ;
176 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
177
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179 /*IF (l_debug = 'Y') THEN
180 okc_debug.log('2500: Leaving get_changed_variables: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
181 END IF;*/
182
183 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
184 FND_LOG.STRING(G_EXCP_LEVEL,
185 G_PKG_NAME, '2500: Leaving get_changed_variables: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
186 END IF;
187
188 x_return_status := G_RET_STS_UNEXP_ERROR ;
189 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
190 WHEN OTHERS THEN
191 /*IF (l_debug = 'Y') THEN
192 okc_debug.log('2600: Leaving get_changed_variables because of EXCEPTION: '||sqlerrm, 2);
193 END IF;*/
194
195 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
196 FND_LOG.STRING(G_EXCP_LEVEL,
197 G_PKG_NAME, '2600: Leaving get_changed_variables because of EXCEPTION: '||sqlerrm );
198 END IF;
199
200 x_return_status := G_RET_STS_UNEXP_ERROR ;
201
202 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
203 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
204 END IF;
205 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
206 END;
207
208 Procedure get_item_dtl_for_expert(
209 p_api_version IN Number,
210 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
211 p_doc_type IN Varchar2,
212 p_doc_id IN Number,
213 x_category_tbl OUT NOCOPY item_tbl_type,
214 x_item_tbl OUT NOCOPY item_tbl_type,
215 x_return_status OUT NOCOPY Varchar2,
216 x_msg_data OUT NOCOPY Varchar2,
217 x_msg_count OUT NOCOPY Number
218 )IS
219 l_api_version CONSTANT NUMBER := 1;
220 l_api_name CONSTANT VARCHAR2(30) := 'g_get_item_dtl_for_expert';
221 l_category_tbl category_tbl_type;
222 BEGIN
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224
225 IF l_debug='Y' THEN
226 okc_util.print_trace(1,' Calling PO_CONTERMS_UTL_GRP.get_item_categorylist');
227 END IF;
228
229 PO_CONTERMS_UTL_GRP.get_item_categorylist(
230 p_api_version => l_api_version,
231 p_init_msg_list => FND_API.G_FALSE,
232 p_doc_type => p_doc_type,
233 p_document_id => p_doc_id,
234 x_category_tbl => l_category_tbl,
235 x_item_tbl => x_item_tbl,
236 x_return_status => x_return_status,
237 x_msg_data => x_msg_data,
238 x_msg_count => x_msg_count);
239
240 --------------------------------------------
241 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
243 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
244 RAISE FND_API.G_EXC_ERROR ;
245 END IF;
246 --------------------------------------------
247 IF l_category_tbl.count > 0 THEN
248 FOR i in l_category_tbl.FIRST..l_category_tbl.LAST LOOP
249 x_category_tbl(i).name := l_category_tbl(i).category_name;
250 END LOOP;
251 END IF;
252
253 IF l_debug='Y' THEN
254 okc_util.print_trace(1,' Leaving OKC_PO_INT_GRP.get_item_dtl_for_expert');
255 END IF;
256 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
257
258 EXCEPTION
259 WHEN FND_API.G_EXC_ERROR THEN
260
261 /*IF (l_debug = 'Y') THEN
262 okc_debug.log('2400: Leaving get_item_dtl_for_expert: OKC_API.G_EXCEPTION_ERROR Exception', 2);
263 END IF;*/
264
265 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
266 FND_LOG.STRING(G_EXCP_LEVEL,
267 G_PKG_NAME, '2400: Leaving get_item_dtl_for_expert: OKC_API.G_EXCEPTION_ERROR Exception' );
268 END IF;
269
270 x_return_status := G_RET_STS_ERROR ;
271 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
272
273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
274 /*IF (l_debug = 'Y') THEN
275 okc_debug.log('2500: Leaving get_item_dtl_for_expert: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
276 END IF;*/
277
278 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
279 FND_LOG.STRING(G_EXCP_LEVEL,
280 G_PKG_NAME, '2500: Leaving get_item_dtl_for_expert: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
281 END IF;
282
283 x_return_status := G_RET_STS_UNEXP_ERROR ;
284 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
285 WHEN OTHERS THEN
286 /*IF (l_debug = 'Y') THEN
287 okc_debug.log('2600: Leaving get_item_dtl_for_expert because of EXCEPTION: '||sqlerrm, 2);
288 END IF;*/
289
290 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
291 FND_LOG.STRING(G_EXCP_LEVEL,
292 G_PKG_NAME, '2600: Leaving get_item_dtl_for_expert because of EXCEPTION: '||sqlerrm );
293 END IF;
294
295 x_return_status := G_RET_STS_UNEXP_ERROR ;
296
297 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
298 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
299 END IF;
300 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
301 END get_item_dtl_for_expert;
302
303 Function ok_to_commit (
304 p_api_version IN Number,
305 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
306 p_doc_type IN Varchar2,
307 p_doc_id IN Number,
308 p_tmpl_change IN Varchar2 default NULL,
309 p_validation_string IN Varchar2 default NULL,
310 x_return_status OUT NOCOPY Varchar2,
311 x_msg_data OUT NOCOPY Varchar2,
312 x_msg_count OUT NOCOPY NUMBER
313 ,p_del_commit_mode IN VARCHAR2 DEFAULT NULL
314 )
315 Return Varchar2 IS
316
317 l_api_version CONSTANT NUMBER := 1;
318 l_api_name CONSTANT VARCHAR2(30) := 'g_ok_to_commit';
319 l_update_allowed VARCHAR2(1) :='N';
320 l_tmpl_change VARCHAR2(20) := p_tmpl_change;
321 BEGIN
322
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324
325 -- If p_tmpl_change is null and template usage does not exists and the deliverable count is 1
326 -- then make p_tmpl_change
327 IF p_del_commit_mode = 'C' -- Deliverable is being created.
328 THEN
329 IF l_tmpl_change IS NULL
330 THEN
331 BEGIN
332 SELECT 'Y'
333 INTO l_tmpl_change
334 FROM dual
335 WHERE NOT exists
336 (SELECT 'Y'
337 FROM okc_template_usages
338 WHERE DOCUMENT_TYPE = p_doc_type
339 AND DOCUMENT_ID = p_doc_id )
340 AND 0 = (SELECT Count(1)
341 FROM okc_deliverables
342 WHERE business_DOCUMENT_TYPE = p_doc_type
343 AND business_DOCUMENT_ID = p_doc_id
344 );
345 EXCEPTION
346 WHEN OTHERS THEN
347 l_tmpl_change := p_tmpl_change;
348 END;
349 END IF;
350 END IF;
351
352 IF p_del_commit_mode = 'D' THEN
353 IF l_tmpl_change IS NULL
354 THEN
355 BEGIN
356 SELECT 'D'
357 INTO l_tmpl_change
358 FROM dual
359 WHERE NOT exists
360 (SELECT 'Y'
361 FROM okc_template_usages
362 WHERE DOCUMENT_TYPE = p_doc_type
363 AND DOCUMENT_ID = p_doc_id )
364 AND 1 = (SELECT Count(1)
365 FROM okc_deliverables
366 WHERE business_DOCUMENT_TYPE = p_doc_type
367 AND business_DOCUMENT_ID = p_doc_id
368 );
369 EXCEPTION
370 WHEN OTHERS THEN
371 l_tmpl_change := p_tmpl_change;
372 END;
373 END IF;
374
375 END IF;
376
377 IF l_tmpl_change IS NULL THEN
378
379 PO_CONTERMS_UTL_GRP.is_po_update_allowed(
380 p_api_version => l_api_version,
381 p_init_msg_list => FND_API.G_FALSE,
382 p_doc_type => p_doc_type ,
383 p_header_id => p_doc_id,
384 p_callout_string => p_validation_string,
385 p_lock_flag => 'N',
386 x_update_allowed => l_update_allowed,
387 x_return_status => x_return_status,
388 x_msg_data => x_msg_data,
389 x_msg_count => x_msg_count);
390 --------------------------------------------
391 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
393 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
394 RAISE FND_API.G_EXC_ERROR ;
395 END IF;
396 --------------------------------------------
397 ELSE
398 PO_CONTERMS_UTL_GRP.Apply_Template_Change(
399 p_api_version => l_api_version,
400 p_init_msg_list => FND_API.G_FALSE,
401 p_doc_type => p_doc_type,
402 p_header_id => p_doc_id,
403 p_callout_string => p_validation_string,
404 p_template_changed => l_tmpl_change,
405 p_commit => FND_API.G_FALSE,
406 x_update_allowed => l_update_allowed,
407 x_return_status => x_return_status,
408 x_msg_data => x_msg_data,
409 x_msg_count => x_msg_count);
410 --------------------------------------------
411 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
413 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
414 RAISE FND_API.G_EXC_ERROR ;
415 END IF;
416 --------------------------------------------
417 END IF;
418
419 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
420
421 IF l_update_allowed='Y' THEN
422 return FND_API.G_TRUE;
423 ELSE
424 return FND_API.G_FALSE;
425 END IF;
426
427 EXCEPTION
428 WHEN FND_API.G_EXC_ERROR THEN
429 /*IF (l_debug = 'Y') THEN
430 okc_debug.log('2400: Leaving ok_to_commit: OKC_API.G_EXCEPTION_ERROR Exception', 2);
431 END IF;*/
432
433 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
434 FND_LOG.STRING(G_EXCP_LEVEL,
435 G_PKG_NAME, '2400: Leaving ok_to_commit: OKC_API.G_EXCEPTION_ERROR Exception' );
436 END IF;
437
438 x_return_status := G_RET_STS_ERROR ;
439 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
440 return FND_API.G_FALSE;
441
442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
443 /*IF (l_debug = 'Y') THEN
444 okc_debug.log('2500: Leaving ok_to_commit: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
445 END IF;*/
446
447 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
448 FND_LOG.STRING(G_EXCP_LEVEL,
449 G_PKG_NAME, '2500: Leaving ok_to_commit: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
450 END IF;
451
452 x_return_status := G_RET_STS_UNEXP_ERROR ;
453 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
454 return FND_API.G_FALSE;
455 WHEN OTHERS THEN
456 /*IF (l_debug = 'Y') THEN
457 okc_debug.log('2600: Leaving ok_to_commit because of EXCEPTION: '||sqlerrm, 2);
458 END IF;*/
459
460 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
461 FND_LOG.STRING(G_EXCP_LEVEL,
462 G_PKG_NAME, '2600: Leaving ok_to_commit because of EXCEPTION: '||sqlerrm );
463 END IF;
464
465 x_return_status := G_RET_STS_UNEXP_ERROR ;
466
467 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
468 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
469 END IF;
470 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
471 return FND_API.G_FALSE;
472
473
474 END ok_to_commit;
475
476 PROCEDURE Get_Last_Signed_Revision (
477 p_api_version IN NUMBER,
478 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
479 p_doc_type IN VARCHAR2,
480 p_doc_id IN NUMBER,
481 p_revision_num IN NUMBER,
482 x_signed_revision_num OUT NOCOPY NUMBER,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_data OUT NOCOPY VARCHAR2,
485 x_msg_count OUT NOCOPY NUMBER)
486 IS
487 l_api_version CONSTANT NUMBER := 1;
488 l_api_name CONSTANT VARCHAR2(30) := 'Get_Last_Signed_Revision';
489 l_signed_record Varchar2(1);
490 BEGIN
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 IF l_debug='Y' THEN
494 okc_util.print_trace(1,' Calling PO_CONTERMS_UTL_GRP.Get_Last_Signed_Revision');
495 END IF;
496
497 PO_CONTERMS_UTL_GRP.Get_Last_Signed_Revision(
498 p_api_version => l_api_version,
499 p_init_msg_list => FND_API.G_FALSE,
500 p_doc_type => p_doc_type,
501 p_header_id => p_doc_id,
502 p_revision_num => p_revision_num,
503 x_signed_revision_num => x_signed_revision_num,
504 x_signed_records => l_signed_record,
505 x_return_status => x_return_status,
506 x_msg_data => x_msg_data,
507 x_msg_count => x_msg_count);
508
509 --------------------------------------------
510 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
512 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
513 RAISE FND_API.G_EXC_ERROR ;
514 END IF;
515 --------------------------------------------
516 IF l_signed_record <>'Y' THEN
517 x_return_status := G_RET_STS_UNEXP_ERROR;
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
519 END IF;
520
521 IF l_debug='Y' THEN
522 okc_util.print_trace(1,' Leaving OKC_PO_INT_GRP.Get_Last_Signed_Revision');
523 END IF;
524 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
525
526 EXCEPTION
527 WHEN FND_API.G_EXC_ERROR THEN
528
529 /*IF (l_debug = 'Y') THEN
530 okc_debug.log('2400: Leaving Get_Last_Signed_Revision: OKC_API.G_EXCEPTION_ERROR Exception', 2);
531 END IF;*/
532
533 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
534 FND_LOG.STRING(G_EXCP_LEVEL,
535 G_PKG_NAME, '2400: Leaving Get_Last_Signed_Revision: OKC_API.G_EXCEPTION_ERROR Exception' );
536 END IF;
537
538 x_return_status := G_RET_STS_ERROR ;
539 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
540
541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
542 /*IF (l_debug = 'Y') THEN
543 okc_debug.log('2500: Leaving Get_Last_Signed_Revision: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
544 END IF;*/
545
546 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
547 FND_LOG.STRING(G_EXCP_LEVEL,
548 G_PKG_NAME, '2500: Leaving Get_Last_Signed_Revision: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
549 END IF;
550
551 x_return_status := G_RET_STS_UNEXP_ERROR ;
552 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
553 WHEN OTHERS THEN
554 /*IF (l_debug = 'Y') THEN
555 okc_debug.log('2600: Leaving Get_Last_Signed_Revision because of EXCEPTION: '||sqlerrm, 2);
556 END IF;*/
557
558 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
559 FND_LOG.STRING(G_EXCP_LEVEL,
560 G_PKG_NAME, '2600: Leaving Get_Last_Signed_Revision because of EXCEPTION: '||sqlerrm );
561 END IF;
562
563 x_return_status := G_RET_STS_UNEXP_ERROR ;
564
565 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
566 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
567 END IF;
568 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
569 END Get_Last_Signed_Revision;
570
571 END OKC_PO_INT_GRP;