[Home] [Help]
PACKAGE BODY: APPS.OKC_CLS_UTIL
Source
1 PACKAGE BODY OKC_CLS_UTIL AS
2 /* $Header: OKCCLSUTILB.pls 120.0.12020000.8 2013/03/29 14:10:12 skavutha noship $ */
3
4 PROCEDURE LOAD_CLAUSE_XML(
5 p_data IN CLOB,
6 p_token IN VARCHAR2,
7 p_mode IN VARCHAR2,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_err_msg OUT NOCOPY VARCHAR2) AS
10
11 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_CLAUSE_XML';
12 BEGIN
13
14 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
15 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
16 'LOAD_CLAUSE_XML procedure started. Params: p_token: '|| p_token || ', p_mode: '||p_mode);
17 END IF;
18
19 IF p_mode IS NULL OR NOT(p_mode = 'DOC' OR p_mode = 'LIB') THEN
20 x_return_status := 'E';
21 x_err_msg := 'Invalid Mode';
22
23 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
24 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: Invalid Mode error. Received mode is: '|| p_mode);
25 END IF;
26
27 RETURN;
28
29 END IF;
30
31 IF p_mode = 'DOC' THEN
32 IF p_token IS NULL OR p_token = '' THEN
33 x_return_status := 'E';
34 x_err_msg := 'Token is expected';
35
36 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
37 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: Error. No token is received for DOC mode');
38 END IF;
39
40 RETURN;
41
42 END IF;
43 END IF;
44
45 IF p_mode = 'LIB' THEN
46 IF p_token IS NULL OR p_token = '' THEN
47 x_return_status := 'E';
48 x_err_msg := 'Token is not expected';
49
50 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
51 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: Error. Token received for Library mode.');
52 END IF;
53
54 RETURN;
55
56 END IF;
57 END IF;
58
59 IF p_data IS NULL OR p_data = '' THEN
60 x_return_status := 'E';
61 x_err_msg := 'Data is null';
62
63 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
64 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: No data is received for processing ');
65 END IF;
66
67 RETURN;
68 END IF;
69
70 ---one more validation to check for error details in received xml
71
72 IF p_mode = 'LIB' THEN
73
74 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
75 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: LIB mode processing started');
76 END IF;
77
78 INSERT INTO OKC_CLS_CLAUSE_BUFFER_T(
79 BUFFER_SEQ_ID,
80 CLAUSE_SEQUENCE,
81 FULLTEXT_YN,
82 PRESECRIBED_YN,
83 REQUIRED_YN,
84 CLAUSE_CLS_ID,
85 CLAUSE_REF_YN,
86 CLAUSE_PROVISION_YN,
87 CLAUSE_NUMBER,
88 REGULATION_ID,
89 REGULATION_NAME,
90 REGULATION_TITLE,
91 REGULATION_URL,
92 SECTION_ID,
93 SECTION_NAME,
94 SECTION_TITLE,
95 ALTERNATE_OR_DEVIATION,
96 VARIATION_NAME,
97 REVISION_ID,
98 REVISION_FILLINS_YN,
99 REVISION_EDITABLE_YN,
100 REVISION_TITLE,
101 CLAUSE_TEXT,
102 EFF_START_DATE,
103 EFF_END_DATE,
104 SERIAL_NUMBER,
105 CREATED_BY,
106 CREATION_DATE,
107 LAST_UPDATED_BY,
108 LAST_UPDATE_DATE,
109 LAST_UPDATE_LOGIN)
110 (SELECT OKC_CLS_CLAUSE_BUFFER_T_S.NEXTVAL,
111 CLAUSE_SEQUENCE,
112 decode(FULLTEXT_YN,'true','Y','false','N',null),
113 decode(PRESECRIBED_YN,'true','Y','false','N',null),
114 decode(REQUIRED_YN,'true','Y','false','N',null),
115 CLAUSE_CLS_ID,
116 decode(CLAUSE_REF_YN,'true','Y','false','N',null),
117 decode(CLAUSE_PROVISION_YN,'true','Y','false','N',null),
118 CLAUSE_NUMBER,
119 REGULATION_ID,
120 REGULATION_NAME,
121 REGULATION_TITLE,
122 REGULATION_URL,
123 SECTION_ID,
124 SECTION_NAME,
125 SECTION_TITLE,
126 ALTERNATE_OR_DEVIATION,
127 VARIATION_NAME,
128 REVISION_ID,
129 decode(REVISION_FILLINS_YN,'true','Y','false','N',null),
130 decode(REVISION_EDITABLE_YN,'true','Y','false','N',null),
131 REVISION_TITLE,
132 CLAUSE_TEXT,
133 to_date(EFF_START_DATE,'mm/dd/yyyy'),
134 to_date(EFF_END_DATE,'mm/dd/yyyy'),
135 ROWNUM,
136 FND_GLOBAL.USER_ID,
137 SYSDATE,
138 FND_GLOBAL.USER_ID,
139 SYSDATE,
140 FND_GLOBAL.LOGIN_ID
141
142 FROM xmltable('//ProcurementDocumentClause' passing xmltype(p_data)
143 COLUMNS
144 "CLAUSE_SEQUENCE" NUMBER PATH '@id',
145 "FULLTEXT_YN" VARCHAR2(10) PATH '@fullText',
146 "PRESECRIBED_YN" VARCHAR2(10) PATH '@prescribed',
147 "REQUIRED_YN" VARCHAR2(10) PATH '@required',
148 "CLAUSE_CLS_ID" NUMBER PATH 'Clause/@id',
149 "CLAUSE_REF_YN" VARCHAR2(10) PATH 'Clause/@referenceAllowed',
150 "CLAUSE_PROVISION_YN" VARCHAR2(10) PATH 'Clause/@provision',
151 "CLAUSE_NUMBER" VARCHAR2(50) PATH 'Clause/Number',
152 "REGULATION_ID" NUMBER PATH 'Clause/Regulation/@id',
153 "REGULATION_NAME" VARCHAR2(200) PATH 'Clause/Regulation/Name',
154 "REGULATION_TITLE" VARCHAR2(200) PATH 'Clause/Regulation/Title',
155 "REGULATION_URL" VARCHAR2(250) PATH 'Clause/Regulation/Url',
156 "SECTION_ID" NUMBER PATH 'Clause/Section/@id',
157 "SECTION_NAME" VARCHAR2(100) PATH 'Clause/Section/Name',
158 "SECTION_TITLE" VARCHAR2(100) PATH 'Clause/Section/Title',
159 "ALTERNATE_OR_DEVIATION" VARCHAR2(100) PATH 'Clause/ClauseVariation/AlternateOrDeviation',
160 "VARIATION_NAME" VARCHAR2(200) PATH 'Clause/ClauseVariation/VariationName',
161 "REVISION_ID" NUMBER PATH 'Clause/Revision/@id',
162 "REVISION_FILLINS_YN" VARCHAR2(10) PATH 'Clause/Revision/@hasFillins',
163 "REVISION_EDITABLE_YN" VARCHAR2(10) PATH 'Clause/Revision/@isEditable',
164 "REVISION_TITLE" VARCHAR2(200) PATH 'Clause/Revision/Title',
165 "CLAUSE_TEXT" CLOB PATH 'Clause/Revision/Body',
166 "EFF_START_DATE" varchar2(50) PATH 'Clause/Revision/EffectiveStartDate',
167 "EFF_END_DATE" varchar2(50) PATH 'Clause/Revision/EffectiveEndDate'
168 ) as op);
169 ELSIF p_mode = 'DOC' THEN
170
171 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
172 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: DOC mode processing started');
173 END IF;
174
175 -- delete the rows against the received token
176 DELETE FROM OKC_CLS_CLAUSE_BUFFER_T
177 WHERE token = p_token;
178
179 insert into OKC_CLS_CLAUSE_BUFFER_T(
180 BUFFER_SEQ_ID,
181 CLAUSE_SEQUENCE,
182 FULLTEXT_YN,
183 PRESECRIBED_YN,
184 REQUIRED_YN,
185 CLAUSE_CLS_ID,
186 CLAUSE_REF_YN,
190 SECTION_NAME,
187 CLAUSE_PROVISION_YN,
188 CLAUSE_NUMBER,
189 SECTION_ID,
191 SECTION_TITLE,
192 ALTERNATE_OR_DEVIATION,
193 VARIATION_NAME,
194 REVISION_ID,
195 REVISION_FILLINS_YN,
196 REVISION_EDITABLE_YN,
197 REVISION_TITLE,
198 TOKEN,
199 SERIAL_NUMBER,
200 CREATED_BY,
201 CREATION_DATE,
202 LAST_UPDATED_BY,
203 LAST_UPDATE_DATE,
204 LAST_UPDATE_LOGIN)
205 (SELECT OKC_CLS_CLAUSE_BUFFER_T_S.NEXTVAL,
206 ROWNUM,
207 NULL,
208 --CLAUSE_SEQUENCE,
209 --decode(FULLTEXT_YN,'true','Y','false','N',null),
210 decode(PRESECRIBED_YN,'true','Y','false','N',null),
211 decode(REQUIRED_YN,'true','Y','false','N',null),
212 CLAUSE_CLS_ID,
213 decode(CLAUSE_REF_YN,'true','Y','false','N',null),
214 decode(CLAUSE_PROVISION_YN,'true','Y','false','N',null),
215 CLAUSE_NUMBER,
216 SECTION_ID,
217 SECTION_NAME,
218 SECTION_TITLE,
219 ALTERNATE_OR_DEVIATION,
220 VARIATION_NAME,
221 REVISION_ID,
222 decode(REVISION_FILLINS_YN,'true','Y','false','N',null),
223 decode(REVISION_EDITABLE_YN,'true','Y','false','N',null),
224 REVISION_TITLE,
225 Nvl(p_token,''),
226 ROWNUM,
227 FND_GLOBAL.USER_ID,
228 SYSDATE,
229 FND_GLOBAL.USER_ID,
230 SYSDATE,
231 FND_GLOBAL.LOGIN_ID
232 FROM xmltable('//Clause' passing xmltype(p_data)
233 COLUMNS
234 -- "CLAUSE_SEQUENCE" NUMBER PATH '@id',
235 -- "FULLTEXT_YN" VARCHAR2(10) PATH '@fullText',
236 "PRESECRIBED_YN" VARCHAR2(10) PATH '@prescribed',
237 "REQUIRED_YN" VARCHAR2(10) PATH '@required',
238 "CLAUSE_CLS_ID" NUMBER PATH '@id',
239 "CLAUSE_REF_YN" VARCHAR2(10) PATH '@referenceAllowed',
240 "CLAUSE_PROVISION_YN" VARCHAR2(10) PATH '@provision',
241 "CLAUSE_NUMBER" VARCHAR2(50) PATH 'Number',
242 "SECTION_ID" NUMBER PATH 'Section/@id',
243 "SECTION_NAME" VARCHAR2(100) PATH 'Section',
244 "SECTION_TITLE" VARCHAR2(100) PATH 'Section',
245 "ALTERNATE_OR_DEVIATION" VARCHAR2(100) PATH 'ClauseVariation/AlternateOrDeviation',
246 "VARIATION_NAME" VARCHAR2(200) PATH 'ClauseVariation/VariationName',
247 "REVISION_ID" NUMBER PATH 'Revision/@id',
248 "REVISION_FILLINS_YN" VARCHAR2(10) PATH 'Revision/@hasFillins',
249 "REVISION_EDITABLE_YN" VARCHAR2(10) PATH 'Revision/@isEditable',
250 "REVISION_TITLE" VARCHAR2(200) PATH 'Revision/Title'
251 ) as op);
252
253 END IF;
254
255 x_return_status := 'S';
256 x_err_msg := 'Success';
257
258 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
259 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: Completed Succesfully.');
260 END IF;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264
265 x_return_status := 'E';
266 x_err_msg := SQLCODE || ':' || SQLERRM;
267
268 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSE_XML: Completed in error. Error - '|| x_err_msg);
270 END IF;
271
272 END LOAD_CLAUSE_XML;
273
274 PROCEDURE LOAD_CLAUSES_FOR_DOC(
275 p_token IN VARCHAR2,
276 p_doc_id IN NUMBER,
277 p_doc_type IN VARCHAR2,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_err_msg OUT NOCOPY VARCHAR2,
280 x_err_msg_code OUT NOCOPY VARCHAR2) AS
281
282 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_CLAUSES_FOR_DOC';
283 l_xml_section_id OKC_CLS_CLAUSE_BUFFER_T.SECTION_ID%TYPE;
284 l_xml_section_name OKC_CLS_CLAUSE_BUFFER_T.SECTION_NAME%TYPE;
285 l_xml_section_title OKC_CLS_CLAUSE_BUFFER_T.SECTION_TITLE%TYPE;
286
287 CURSOR cur_distinct_sections_in_xml(p_tok VARCHAR2) IS
288 SELECT DISTINCT SECTION_ID,
289 SECTION_NAME,
290 SECTION_TITLE
291 FROM OKC_CLS_CLAUSE_BUFFER_T
292 WHERE token = p_tok;
293
294 CURSOR cur_id_if_sync(param_token OKC_CLS_CLAUSE_BUFFER_T.token%TYPE) IS
295 SELECT buf.buffer_seq_id buffer_seq_id,
296 art.ARTICLE_ID art_id,
297 ver.ARTICLE_VERSION_ID ver_id
298 FROM OKC_CLS_CLAUSE_BUFFER_T buf,
299 OKC_ARTICLES_ALL ART
300 ,OKC_ARTICLE_VERSIONS ver
301 WHERE buf.token = param_token
302 AND art.article_number = buf.CLAUSE_NUMBER
303 AND art.ORIG_SYSTEM_REFERENCE_CODE = 'CLS'
304 AND art.ORIG_SYSTEM_REFERENCE_ID1 = buf.CLAUSE_CLS_ID
305 AND art.article_id = ver.article_id
306 AND ver.ORIG_SYSTEM_REFERENCE_CODE = 'CLS'
307 AND ver.ORIG_SYSTEM_REFERENCE_ID1 = buf.REVISION_ID
308 AND ver.ARTICLE_STATUS = 'APPROVED'
309
310 AND (art.ORG_ID = FND_GLOBAL.ORG_ID
311 OR (exists ( SELECT 1
312 FROM okc_article_ADOPTIONS ADP
313 WHERE adp.global_article_version_id = ver.article_version_id
317 )
314 AND adp.adoption_type = 'ADOPTED'
315 AND adp.local_org_id = FND_GLOBAL.ORG_ID
316 AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
318 )
319 );
320
321
322 CURSOR cur_id_if_not_sync(param_token OKC_CLS_CLAUSE_BUFFER_T.token%type) IS
323 SELECT buf.buffer_seq_id buffer_seq_id,
324 art.ARTICLE_ID art_id,
325 ver.ARTICLE_VERSION_ID ver_id
326 FROM OKC_CLS_CLAUSE_BUFFER_T buf,
327 OKC_ARTICLES_ALL ART
328 ,OKC_ARTICLE_VERSIONS ver
329 WHERE buf.token = '96aebdf2d5525841ddcf5bb9097714e0'
330 AND art.article_number = buf.CLAUSE_NUMBER
331 AND art.article_id = ver.article_id
332 AND SYSDATE BETWEEN Nvl(ver.start_date,SYSDATE-1) AND Nvl(ver.end_date, SYSDATE+1)
333 AND ver.ARTICLE_STATUS = 'APPROVED'
334 AND (art.ORG_ID = FND_GLOBAL.ORG_ID
335 OR (exists ( SELECT 1
336 FROM okc_article_ADOPTIONS ADP
337 WHERE adp.global_article_version_id = ver.article_version_id
338 AND adp.adoption_type = 'ADOPTED'
339 AND adp.local_org_id = FND_GLOBAL.ORG_ID
340 AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
341 )
342 )
343 );
344
345 TYPE art_ver_id IS RECORD (
346 seq_id OKC_CLS_CLAUSE_BUFFER_T.buffer_seq_id%TYPE,
347 art_id OKC_CLS_CLAUSE_BUFFER_T.ORIG_ARTICLE_ID%TYPE,
348 ver_id OKC_CLS_CLAUSE_BUFFER_T.ORIG_ARTICLE_VERSION_ID%TYPE
349 );
350
351 TYPE art_ver_ids_tbl IS TABLE OF art_ver_id INDEX BY BINARY_INTEGER;
352
353 l_art_ver_ids_tbl art_ver_ids_tbl;
354 l_section_id okc_sections_b.id%TYPE;
355 l_api_version CONSTANT NUMBER := 1;
356 l_section_present_in_lib VARCHAR2(1);
357 l_section_id_found VARCHAR2(1);
358 l_wrong_version_found VARCHAR2(1);
359 l_return_status VARCHAR2(100);
360 l_msg_data VARCHAR2(1000);
361 l_msg_count NUMBER;
362 l_max_seq NUMBER;
363 l_missing_clause_count NUMBER;
364 l_error_values VARCHAR2(1000);
365 l_cls_lib_in_sync VARCHAR2(10);
366 l_cls_clauses_in_ou_count NUMBER;
367 BEGIN
368
369 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
370 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
371 'LOAD_CLAUSES_FOR_DOC: Started with params - p_token: '|| p_token || ', p_doc_id: ' || p_doc_id ||
372 ', p_doc_type: ' || p_doc_type);
373 END IF;
374
375 -- check and create the sections first
376 OPEN cur_distinct_sections_in_xml(p_token);
377 LOOP
378 FETCH cur_distinct_sections_in_xml INTO
379 l_xml_section_id,
380 l_xml_section_name,
381 l_xml_section_title;
382 EXIT WHEN cur_distinct_sections_in_xml%NOTFOUND;
383 BEGIN
384 SELECT id
385 INTO l_section_id
386 FROM okc_sections_b
387 WHERE heading = l_xml_section_name
388 AND DOCUMENT_TYPE = p_doc_type
389 AND DOCUMENT_ID = p_doc_id
390 AND ROWNUM = 1;
391
392 l_section_id_found := 'Y';
393 EXCEPTION
394 WHEN No_Data_Found THEN
395 l_section_id_found := 'N';
396 WHEN OTHERS THEN
397 l_section_id_found := 'N';
398 END;
399
400 IF l_section_id_found = 'N' THEN
401
402 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
403 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Section:'''
404 || l_xml_section_name || ''' is not existing. It is being added.');
405 END IF;
406
407 -- need to create a new scetion id
408 BEGIN
409 SELECT 'Y'
410 INTO l_section_present_in_lib
411 FROM FND_LOOKUPS
412 WHERE lookup_type = 'OKC_ARTICLE_SECTION'
413 AND lookup_code = l_xml_section_name
414 AND ROWNUM = 1;
415 EXCEPTION
416 WHEN No_Data_Found THEN
417 l_section_present_in_lib := 'N';
418 WHEN OTHERS THEN
419 l_section_present_in_lib := 'N';
420 END;
421
422 IF l_section_present_in_lib = 'Y' THEN
423 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
424 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Section:'''||
425 l_xml_section_name || ''' is found in library. It being added from library.');
426 END IF;
427
428 OKC_TERMS_MIGRATE_GRP.Add_Section (
429 p_api_version => 1.0,
430 x_return_status => l_return_status,
431 x_msg_data => l_msg_data,
432 x_msg_count => l_msg_count,
433 p_document_type => p_doc_type,
434 p_document_id => p_doc_id,
435 p_section_source => 'LIBRARY',
436 p_section_name => l_xml_section_name,
437 p_section_description => l_xml_section_title,
438 p_orig_system_reference_code => 'CLS',
439 p_orig_system_reference_id1 => l_xml_section_id,
440 x_section_id => l_section_id);
444 l_xml_section_name || ''' is not found in library. It being added as user defined section.');
441 ELSIF l_section_present_in_lib = 'N' THEN
442 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
443 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Section: '''||
445 END IF;
446
447 OKC_TERMS_MIGRATE_GRP.Add_Section (
448 p_api_version => 1.0,
449 x_return_status => l_return_status,
450 x_msg_data => l_msg_data,
451 x_msg_count => l_msg_count,
452 p_document_type => p_doc_type,
453 p_document_id => p_doc_id,
454 p_section_source => 'NEW',
455 p_section_name => l_xml_section_name,
456 p_section_description => l_xml_section_title,
457 p_orig_system_reference_code => 'CLS',
458 p_orig_system_reference_id1 => l_xml_section_id,
459 x_section_id => l_section_id);
460
461 END IF;
462
463 --------------------------------------------
464 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
465 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
466 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Adding sections failed '||
467 'because of unexpected error.');
468 END IF;
469
470 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
471 x_return_status := 'E';
472 x_err_msg := 'LOAD_CLAUSES_FOR_DOC: Adding sections failed because of unexpected error.';
473 x_err_msg_code := 'OKC_CLS_SECTIONS_ADD_UNEXP';
474
475 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Adding sections failed. '||
478 'All the requested operations are not performed.');
479 END IF;
480
481 --RAISE FND_API.G_EXC_ERROR ;
482 x_return_status := 'E';
483 x_err_msg := 'LOAD_CLAUSES_FOR_DOC: Adding sections failed. All the requested operations are not performed.';
484 x_err_msg_code := 'OKC_CLS_SECTIONS_ADD_ERR';
485 END IF;
486 --------------------------------------------
487
488 END IF;
489
490 -- update the sections in respective OKC_CLS_CLAUSE_BUFFER_T/ORIG_SECTION_ID
491 UPDATE OKC_CLS_CLAUSE_BUFFER_T
492 SET ORIG_SECTION_ID = l_section_id,
493 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
494 LAST_UPDATE_DATE = SYSDATE,
495 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
496 WHERE token = p_token
497 AND SECTION_ID = l_xml_section_id
498 AND SECTION_NAME = l_xml_section_name
499 AND SECTION_TITLE = l_xml_section_title;
500
501 END LOOP;
502 CLOSE cur_distinct_sections_in_xml;
503
504 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
505 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
506 'LOAD_CLAUSES_FOR_DOC: Deleting the Clauses and variables that are already present and are added via CLS before');
507 END IF;
508
509 -- delete existing CLS clauses
510 -- delete the variables first
511 DELETE FROM okc_k_art_variables
512 WHERE CAT_ID IN (SELECT id
513 FROM okc_k_articles_b
514 WHERE SOURCE_FLAG = 'C'
515 AND DOCUMENT_TYPE = p_doc_type
516 AND DOCUMENT_ID = p_doc_id
517 );
518 -- delete the clauses
519 DELETE FROM okc_k_articles_b
520 WHERE SOURCE_FLAG = 'C'
521 AND DOCUMENT_TYPE = p_doc_type
522 AND DOCUMENT_ID = p_doc_id ;
523
524 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
525 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
526 'LOAD_CLAUSES_FOR_DOC: Deriving info required for adding clauses');
527 END IF;
528
529 -- do the derivations for clauses
530 -- derive SAV_SAE_ID,ORIG_ARTICLE_ID
531 -- derive DISPLAY_SEQUENCE
532 -- derive ARTICLE_VERSION_ID
533 SELECT Max(DISPLAY_SEQUENCE)
534 INTO l_max_seq
535 FROM okc_k_articles_b
536 WHERE DOCUMENT_TYPE = p_doc_type
537 AND DOCUMENT_ID = p_doc_id ;
538
539 -- to-do :
540 -- 2. org_id check is required for ORIG_ARTICLE_ID
541 -- read the profile value of 'OKC: Clause Library in sync with Clause Logic Service'
542
543 l_cls_lib_in_sync := fnd_profile.value('OKC_CLS_LIB_IN_SYNC');
544 IF l_cls_lib_in_sync IS NULL OR l_cls_lib_in_sync = '' THEN
545 l_cls_lib_in_sync := 'N';
546 END IF;
547
548 IF l_cls_lib_in_sync = 'Y' THEN
549 -- the clauses may be in sync but not for this OU. This case should be checked
550 SELECT Count(1)
551 INTO l_cls_clauses_in_ou_count
552 FROM okc_articles_all
553 WHERE ORIG_SYSTEM_REFERENCE_CODE = 'CLS'
554 AND ORG_ID = FND_GLOBAL.ORG_ID;
555
556 IF l_cls_clauses_in_ou_count <= 0 THEN
557 x_return_status := 'E';
558 x_err_msg := 'Did not find any clauses from CLS in this OU';
559 x_err_msg_code := 'OKC_CLS_CLAUSE_NOT_FOUND_OU';
560 RETURN;
561 END IF;
565 CLOSE cur_id_if_sync;
562 -- read data from cur_id_if_sync
563 OPEN cur_id_if_sync(p_token);
564 FETCH cur_id_if_sync BULK COLLECT INTO l_art_ver_ids_tbl;
566
567 FORALL i IN l_art_ver_ids_tbl.first .. l_art_ver_ids_tbl.last
568 UPDATE OKC_CLS_CLAUSE_BUFFER_T buf
569 SET ORIG_ARTICLE_ID = l_art_ver_ids_tbl(i).art_id ,
570 ORIG_ARTICLE_VERSION_ID = l_art_ver_ids_tbl(i).ver_id,
571 CLAUSE_DISPLAY_SEQUENCE = (l_max_seq + (SERIAL_NUMBER * 10)),
572 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
573 LAST_UPDATE_DATE = SYSDATE,
574 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
575 WHERE buffer_seq_id = l_art_ver_ids_tbl(i).seq_id ;
576
577
578
579 -- check if clause/version is present, if different version is present, throw error.
580 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
581 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
582 'LOAD_CLAUSES_FOR_DOC: Checking clauses for conflict in versions');
583 END IF;
584 BEGIN
585 SELECT LISTAGG(CLAUSE_NUMBER,',') WITHIN GROUP (order BY CLAUSE_NUMBER) "err_values"
586 INTO l_error_values
587 FROM (
588 SELECT CASE WHEN art.ARTICLE_VERSION_ID <> buf.ORIG_ARTICLE_VERSION_ID THEN 'Y'
589 ELSE 'N'
590 END AS wrong_version_yn,
591 CLAUSE_NUMBER
592 FROM okc_k_articles_b art, OKC_CLS_CLAUSE_BUFFER_T buf
593 WHERE art.DOCUMENT_TYPE = p_doc_type
594 AND art.DOCUMENT_ID = p_doc_id
595 AND art.SAV_SAE_ID = buf.ORIG_ARTICLE_ID
596 )
597 WHERE wrong_version_yn = 'Y';
598
599 IF l_error_values IS NULL OR l_error_values = '' THEN
600 l_wrong_version_found := 'N';
601 ELSE
602 l_wrong_version_found := 'Y';
603 x_return_status := 'E';
604 x_err_msg := 'Found following recomended Clauses with different versions existing on the document - ' ||
605 l_error_values;
606 x_err_msg_code := 'OKC_CLS_DIFF_VERSIONS';
607
608
609 RETURN;
610 END IF;
611
612 EXCEPTION
613 WHEN No_Data_Found THEN
614 l_wrong_version_found := 'N';
615 END;
616
617 ELSE
618
619 -- there is a chnace that user actually syncs up the DB but will not set the profile Option. In that case there is a chance of multiple rows exception
620 BEGIN
621
622 OPEN cur_id_if_not_sync(p_token);
623 FETCH cur_id_if_not_sync BULK COLLECT INTO l_art_ver_ids_tbl;
624 CLOSE cur_id_if_not_sync;
625
626 FORALL i IN l_art_ver_ids_tbl.first .. l_art_ver_ids_tbl.last
627 UPDATE OKC_CLS_CLAUSE_BUFFER_T buf
628 SET ORIG_ARTICLE_ID = l_art_ver_ids_tbl(i).art_id ,
629 ORIG_ARTICLE_VERSION_ID = l_art_ver_ids_tbl(i).ver_id,
630 CLAUSE_DISPLAY_SEQUENCE = (l_max_seq + (SERIAL_NUMBER * 10)),
631 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
632 LAST_UPDATE_DATE = SYSDATE,
633 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
634 WHERE buffer_seq_id = l_art_ver_ids_tbl(i).seq_id ;
635
636 EXCEPTION
637 WHEN Too_Many_Rows THEN
638 x_return_status := 'E';
639 x_err_msg := 'Found multiple clauses with same article Number. If library is Synced up please set the profile value of ' ||
640 'OKC: Clause Library in sync with Clause Logic Service to Yes.';
641 RETURN;
642 END;
643 END IF;
644
645 -- check whether all clauses from CLS are present. If not an error should be sent
646 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Checking if any clause from CLS is missing in local clause library.');
648 END IF;
649 l_missing_clause_count := -1;
650 SELECT Count(1)
651 INTO l_missing_clause_count
652 from OKC_CLS_CLAUSE_BUFFER_T
653 WHERE token = p_token
654 AND ORIG_ARTICLE_ID IS NULL;
655
656 IF l_missing_clause_count > 0 THEN
657 x_return_status := 'E';
658 x_err_msg := 'Found clauses from CLS but not in local clause library';
659 x_err_msg_code := 'OKC_CLS_CLAUSE_NOT_FOUND';
660 RETURN;
661 END IF;
662
663 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Inserting the clauses.');
665 END IF;
666
667 -- add the clauses
668 INSERT INTO okc_k_articles_b (
669 ID,
670 SAV_SAE_ID,
671 OBJECT_VERSION_NUMBER,
672 CREATED_BY,
673 CREATION_DATE,
674 LAST_UPDATED_BY,
675 LAST_UPDATE_DATE,
676 FULLTEXT_YN,
677 LAST_UPDATE_LOGIN,
678 DOCUMENT_TYPE,
679 DOCUMENT_ID,
680 SOURCE_FLAG,
681 MANDATORY_YN,
682 SCN_ID,
683 DISPLAY_SEQUENCE,
684 ARTICLE_VERSION_ID,
685 ORIG_SYSTEM_REFERENCE_CODE,
686 ORIG_SYSTEM_REFERENCE_ID1,
687 ORIG_ARTICLE_ID)
688 (SELECT okc_k_articles_b_s.NEXTVAL,
689 ORIG_ARTICLE_ID,
690 1,
691 fnd_global.user_id,
692 SYSDATE,
693 fnd_global.user_id,
694 SYSDATE,
695 FULLTEXT_YN,
696 fnd_global.login_id,
697 p_doc_type,
698 p_doc_id,
699 'C',
700 REQUIRED_YN,
701 ORIG_SECTION_ID,
702 CLAUSE_DISPLAY_SEQUENCE,
703 ORIG_ARTICLE_VERSION_ID,
704 'CLS',
705 CLAUSE_CLS_ID,
706 ORIG_ARTICLE_ID
707 FROM OKC_CLS_CLAUSE_BUFFER_T buf
708 WHERE TOKEN = p_token
709 AND NOT EXISTS (SELECT 1
710 FROM okc_k_articles_b
711 WHERE DOCUMENT_TYPE = p_doc_type
712 AND DOCUMENT_ID = p_doc_id
713 AND SAV_SAE_ID = buf.ORIG_ARTICLE_ID
714 )
715 )
716 ;
717
718 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
719 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Inserting the Variables.');
720 END IF;
721
722 -- add the variables
723 INSERT INTO okc_k_art_variables (
724 CAT_ID,
725 VARIABLE_CODE,
726 VARIABLE_TYPE,
727 EXTERNAL_YN,
728 ATTRIBUTE_VALUE_SET_ID,
729 OBJECT_VERSION_NUMBER,
730 CREATED_BY,
731 CREATION_DATE,
732 LAST_UPDATED_BY,
733 LAST_UPDATE_DATE,
734 LAST_UPDATE_LOGIN
735 )
736 (SELECT art.id,
737 art_vbles.VARIABLE_CODE,
738 art_bus_vbles.VARIABLE_TYPE,
739 art_bus_vbles.EXTERNAL_YN,
740 art_bus_vbles.VALUE_SET_ID,
741 1,
742 fnd_global.user_id,
743 sysdate,
744 fnd_global.user_id,
745 sysdate,
746 fnd_global.login_id
747 FROM okc_k_articles_b art,
748 OKC_ARTICLE_VARIABLES art_vbles,
749 OKC_BUS_VARIABLES_B art_bus_vbles
750
751 WHERE art.DOCUMENT_TYPE = p_doc_type
752 AND art.DOCUMENT_ID = p_doc_id
753 AND ART.SOURCE_FLAG = 'C'
754 AND art_vbles.ARTICLE_VERSION_ID = art.ARTICLE_VERSION_ID
755 AND art_bus_vbles.variable_code = art_vbles.VARIABLE_CODE
756 );
757
758 x_return_status := 'S';
759 x_err_msg := 'Success';
760
761 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
762 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Completed successfully.');
763 END IF;
764
765 EXCEPTION
766 WHEN OTHERS THEN
767
768 x_return_status := 'E';
769 x_err_msg := SQLCODE || ':' || SQLERRM;
770
771 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
773 'LOAD_CLAUSES_FOR_DOC: Completed in error. Error details -'|| x_err_msg);
774 END IF;
775
776 END LOAD_CLAUSES_FOR_DOC;
777
778 PROCEDURE form_indicaors_xml_for(
779 x_return_status OUT NOCOPY VARCHAR2,
780 x_err_msg OUT NOCOPY VARCHAR2,
781 x_output OUT NOCOPY VARCHAR2,
782 p_doc_id IN NUMBER,
783 p_doc_type IN VARCHAR2) AS
784 l_output_xml VARCHAR2(10000);
785 BEGIN
786
787 l_output_xml := '<Indicator id="1"><Values><Value id="1" selected="true"/></Values></Indicator>'; -- 'DOD'
788 --l_output_xml := l_output_xml || '<Indicator id="2"><Values><Value id="1" selected="true"/></Values></Indicator>'; -- 'CONTRACT'
789 x_return_status := 'S';
790 x_err_msg := 'SUCCESS';
791 x_output := l_output_xml;
792 END form_indicaors_xml_for;
793
794 PROCEDURE log_cls_rest_request(
795 x_return_status OUT NOCOPY VARCHAR2,
796 x_err_msg OUT NOCOPY VARCHAR2,
797 x_req_seq_id OUT NOCOPY NUMBER,
798 p_request_method IN VARCHAR2,
799 p_url IN VARCHAR2,
800 p_uri IN VARCHAR2,
801 p_token IN VARCHAR2,
802 p_clat IN VARCHAR2,
803 p_clat_secret IN VARCHAR2,
804 p_nonce IN VARCHAR2,
805 p_signature IN VARCHAR2,
806 p_timestamp IN VARCHAR2,
807 p_request_body IN VARCHAR2,
808 p_auth_type IN VARCHAR2,
809 p_api_key IN VARCHAR2
810 ) AS
811 l_api_name CONSTANT VARCHAR2(30) := 'LOG_CLS_REST_REQUEST';
812 l_req_seq_id NUMBER;
813 PRAGMA AUTONOMOUS_TRANSACTION;
814 BEGIN
815
816 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
818 l_api_name||': Called.');
819 END IF;
820
821 IF p_request_method IS NULL THEN
822 x_return_status := 'E';
823 x_err_msg := 'Request Method is null.';
824 RETURN;
825 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
826 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
827 l_api_name||': Error.' || x_err_msg);
828 END IF;
829 END IF;
830
831 IF p_url IS NULL THEN
832 x_return_status := 'E';
833 x_err_msg := 'URL is null.';
834 RETURN;
835 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
836 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
837 l_api_name||': Error.' || x_err_msg);
838 END IF;
839 END IF;
840
841 IF p_uri IS NULL THEN
842 x_return_status := 'E';
843 x_err_msg := 'URI is null.';
844 RETURN;
845 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
846 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
847 l_api_name||': Error.' || x_err_msg);
848 END IF;
849 END IF;
850
851 l_req_seq_id := OKC_CLS_REST_CALLS_LOGS_S.NEXTVAL;
852
853 INSERT INTO OKC_CLS_REST_CALLS_LOGS(
854 ID,
855 REQUEST_METHOD,
856 URL,
857 URI,
858 TOKEN,
859 CLAT,
860 CLAT_SECRET,
861 NONCE,
862 SIGNATURE,
863 TIME_STAMPED,
864 REQUEST_BODY,
865 AUTH_TYPE,
866 API_KEY,
867 CREATED_BY,
868 CREATION_DATE,
869 LAST_UPDATED_BY,
870 LAST_UPDATE_DATE,
871 LAST_UPDATE_LOGIN
872 ) VALUES
873 (
874 l_req_seq_id,
875 p_request_method,
876 p_url,
877 p_uri,
878 p_token,
879 p_clat,
880 p_clat_secret,
881 p_nonce,
882 p_signature,
883 p_timestamp,
884 p_request_body,
885 p_auth_type,
886 p_api_key,
887 FND_GLOBAL.USER_ID,
888 SYSDATE,
889 FND_GLOBAL.USER_ID,
890 SYSDATE,
891 FND_GLOBAL.LOGIN_ID
892 );
893 COMMIT;
894
895 x_return_status := 'S';
896 x_err_msg := 'Success';
897 x_req_seq_id := l_req_seq_id;
898
899 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
901 l_api_name||': Request Logged with Id: '|| l_req_seq_id);
902 END IF;
903
904 EXCEPTION
905 WHEN OTHERS THEN
906
907 x_return_status := 'E';
908 x_err_msg := SQLCODE || ':' || SQLERRM;
909
910 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
911 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
912 l_api_name || ': Completed in error. Error details -'|| x_err_msg);
913 END IF;
914
915 END log_cls_rest_request;
916
917 PROCEDURE log_cls_rest_response(
918 x_return_status OUT NOCOPY VARCHAR2,
919 x_err_msg OUT NOCOPY VARCHAR2,
920 p_req_seq_id IN NUMBER,
921 p_response_status_code IN NUMBER,
922 p_response IN CLOB
923 ) AS
924 l_api_name CONSTANT VARCHAR2(30) := 'LOG_CLS_REST_RESPONSE';
925 l_transaction_status VARCHAR2(1);
926 BEGIN
927 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
928 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
929 l_api_name||': Called.');
930 END IF;
931
932 IF p_req_seq_id IS NULL THEN
933 x_return_status := 'E';
934 x_err_msg := 'Sequence Id is null.';
935 RETURN;
936 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
937 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
938 l_api_name||': Error.' || x_err_msg);
939 END IF;
940 END IF;
941
942 IF p_response_status_code IS NULL THEN
943 x_return_status := 'E';
944 x_err_msg := 'Response code is null.';
945 RETURN;
946 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
947 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
948 l_api_name||': Error.' || x_err_msg);
949 END IF;
950 END IF;
951
952 IF p_response_status_code = 200 OR p_response_status_code = 201 THEN
953 l_transaction_status := 'S';
954 ELSE
955 l_transaction_status := 'E';
956 END IF;
957
958 UPDATE OKC_CLS_REST_CALLS_LOGS
959 SET RESPONSE_STATUS_CODE = p_response_status_code,
960 TRANSACTION_STATUS = l_transaction_status,
961 RESPONSE = p_response,
962 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
963 LAST_UPDATE_DATE = SYSDATE,
964 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
965 WHERE ID = p_req_seq_id;
966
967 x_return_status := 'S';
968 x_err_msg := 'Success';
969 COMMIT;
970
971 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
972 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
973 l_api_name||': Completed successfully.');
974 END IF;
975
976 EXCEPTION
977 WHEN OTHERS THEN
978
979 x_return_status := 'E';
980 x_err_msg := SQLCODE || ':' || SQLERRM;
981
982 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
983 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,
984 l_api_name || ': Completed in error. Error details -'|| x_err_msg);
985 END IF;
986
987 END log_cls_rest_response;
988
989 END OKC_CLS_UTIL;