[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_COPY_PVT
Source
1 PACKAGE BODY OKC_TERMS_COPY_PVT AS
2 /* $Header: OKCVDCPB.pls 120.20.12020000.4 2013/01/31 08:09:51 skavutha ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 ---------------------------------------------------------------------------
10 -- GLOBAL VARIABLES
11 ---------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_COPY_PVT';
13 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15 G_TEMPLATE_DOC_TYPE CONSTANT okc_bus_doc_types_b.document_type%TYPE := OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE;
16 G_ATTACHED_CONTRACT_SOURCE CONSTANT okc_template_usages.contract_source_code%TYPE := 'ATTACHED';
17
18 ------------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ------------------------------------------------------------------------------
21 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
22 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
23
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
28 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
29 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
30 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
31 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
32 G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
33 G_STRUCT_CONTRACT_SOURCE CONSTANT VARCHAR2(30) := 'STRUCTURED';
34 G_INTERNAL_PARTY_CODE CONSTANT VARCHAR2(30) := 'INTERNAL_ORG';
35 G_COPY CONSTANT VARCHAR2(30) := 'COPY';
36 E_Resource_Busy EXCEPTION;
37 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
38
39
40 FUNCTION get_orig_var_val_xml (
41 p_cat_id IN NUMBER,
42 p_variable_code IN VARCHAR2,
43 p_source_doc_id IN NUMBER,
44 p_source_doc_type IN VARCHAR2,
45 p_value_type IN VARCHAR2)
46 RETURN CLOB IS
47 l_api_name CONSTANT VARCHAR2(30) := 'get_orig_var_val_xml';
48 CURSOR csr_orig_target_dtls IS
49 SELECT kart1.id
50 FROM okc_articles_all lib,
51 okc_k_articles_b kart,
52 okc_k_articles_b kart1
53 WHERE lib.article_id = kart.sav_sae_id
54 AND kart.id= p_cat_id
55 AND kart1.document_id = p_source_doc_id
56 AND kart1.document_type = p_source_doc_type
57 AND kart1.orig_system_reference_id1 = kart.orig_system_reference_id1;
58
59 CURSOR csr_source_value_id(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
60 SELECT var.variable_value,
61 var.variable_value_id
62 , VAR.MR_VARIABLE_HTML
63 , VAR.MR_VARIABLE_XML
64 FROM okc_k_art_variables var
65 WHERE var.cat_id = p_cat_id
66 AND var.variable_code = p_variable_code;
67
68 l_standard_flag VARCHAR2(1);
69 l_id1 VARCHAR2(2000);
70 l_var_value_id okc_k_art_variables.variable_value_id%TYPE := NULL;
71 l_var_value okc_k_art_variables.variable_value%TYPE := NULL;
72
73 l_MR_VARIABLE_HTML okc_k_art_variables.mr_variable_html%TYPE := to_clob(NULL);
74 l_MR_VARIABLE_XML okc_k_art_variables.mr_variable_xml%TYPE := to_clob(NULL);
75
76 BEGIN
77 OPEN csr_orig_target_dtls;
78 FETCH csr_orig_target_dtls INTO l_id1;
79 CLOSE csr_orig_target_dtls;
80
81 OPEN csr_source_value_id(p_cat_id=>l_id1,p_variable_code=>p_variable_code);
82 FETCH csr_source_value_id INTO l_var_value,l_var_value_id,l_MR_VARIABLE_HTML,l_MR_VARIABLE_XML;
83 CLOSE csr_source_value_id;
84
85 IF p_value_type = 'HTML' THEN
86 RETURN l_MR_VARIABLE_HTML;
87 ELSIF p_value_type = 'XML' THEN
88 RETURN l_MR_VARIABLE_XML;
89 ELSE
90 RETURN NULL;
91 END IF;
92 END get_orig_var_val_xml;
93
94 FUNCTION get_variable_value_id (
95 p_cat_id IN NUMBER,
96 p_variable_code IN VARCHAR2)
97 RETURN VARCHAR2 IS
98 l_api_name CONSTANT VARCHAR2(30) := 'get_variable_value_id';
99 CURSOR csr_target_dtls IS
100 SELECT NVL(standard_yn,'N') standard_flag,
101 kart.article_version_id,
102 kart.ORIG_SYSTEM_REFERENCE_ID1
103 FROM okc_articles_all lib,
104 okc_k_articles_b kart
105 WHERE lib.article_id = kart.sav_sae_id
106 AND kart.id= p_cat_id;
107
108 CURSOR csr_source_dtls(p_cat_id IN NUMBER) IS
109 SELECT article_version_id
110 FROM okc_k_articles_b
111 WHERE id = p_cat_id;
112
113 CURSOR csr_source_value_id(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
114 SELECT VAR.VARIABLE_VALUE_ID
115 FROM OKC_K_ART_VARIABLES VAR
116 WHERE VAR.CAT_ID = p_cat_id
117 AND VAR.VARIABLE_CODE = p_variable_code;
118
119 l_standard_flag VARCHAR2(1);
120 l_ORIG_SYSTEM_REFERENCE_ID1 VARCHAR2(1000);
121 l_target_article_version_id NUMBER;
122 l_source_article_version_id NUMBER;
123 l_source_value_id VARCHAR2(1000):= NULL;
124
125 BEGIN
126 OPEN csr_target_dtls;
127 FETCH csr_target_dtls INTO l_standard_flag, l_target_article_version_id, l_ORIG_SYSTEM_REFERENCE_ID1;
128 CLOSE csr_target_dtls;
129
130
131 -- bug 3369336
132 -- copy variable values if the variable exists in the target doc article
133 -- without comparing version or if it was non standard (bug 3397895 )
134
135 OPEN csr_source_value_id(p_cat_id=>l_ORIG_SYSTEM_REFERENCE_ID1,p_variable_code=>p_variable_code);
136 FETCH csr_source_value_id INTO l_source_value_id;
137 CLOSE csr_source_value_id;
138
139 RETURN l_source_value_id;
140
141 END get_variable_value_id;
142
143 FUNCTION get_orig_var_val (
144 p_cat_id IN NUMBER,
145 p_variable_code IN VARCHAR2,
146 p_source_doc_id IN NUMBER,
147 p_source_doc_type IN VARCHAR2,
148 p_value_type IN VARCHAR2)
149 RETURN VARCHAR2 IS
150 l_api_name CONSTANT VARCHAR2(30) := 'get_orig_var_val';
151 CURSOR csr_orig_target_dtls IS
152 SELECT kart1.id
153 FROM okc_articles_all lib,
154 okc_k_articles_b kart,
155 okc_k_articles_b kart1
156 WHERE lib.article_id = kart.sav_sae_id
157 AND kart.id= p_cat_id
158 AND kart1.document_id = p_source_doc_id
159 AND kart1.document_type = p_source_doc_type
160 AND kart1.orig_system_reference_id1 = kart.orig_system_reference_id1;
161
162 CURSOR csr_source_value_id(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
163 SELECT var.variable_value,
164 var.variable_value_id
165 -- , VAR.MR_VARIABLE_HTML
166 -- , VAR.MR_VARIABLE_XML
167 FROM okc_k_art_variables var
168 WHERE var.cat_id = p_cat_id
169 AND var.variable_code = p_variable_code;
170
171 l_standard_flag VARCHAR2(1);
172 l_id1 VARCHAR2(2000);
173 l_var_value_id okc_k_art_variables.variable_value_id%TYPE := NULL;
174 l_var_value okc_k_art_variables.variable_value%TYPE := NULL;
175
176 l_MR_VARIABLE_HTML okc_k_art_variables.mr_variable_html%TYPE := to_clob(NULL);
177 l_MR_VARIABLE_XML okc_k_art_variables.mr_variable_xml%TYPE := to_clob(NULL);
178
179 BEGIN
180 OPEN csr_orig_target_dtls;
181 FETCH csr_orig_target_dtls INTO l_id1;
182 CLOSE csr_orig_target_dtls;
183
184 OPEN csr_source_value_id(p_cat_id=>l_id1,p_variable_code=>p_variable_code);
185 FETCH csr_source_value_id INTO l_var_value,l_var_value_id; --,l_MR_VARIABLE_HTML,l_MR_VARIABLE_XML;
186 CLOSE csr_source_value_id;
187
188 IF p_value_type = 'ID' THEN
189 RETURN l_var_value_id;
190 ELSIF p_value_type = 'CAT_ID' THEN
191 RETURN l_id1;
192 /*ELSIF p_value_type = 'HTML' THEN
193 RETURN l_MR_VARIABLE_HTML;
194 ELSIF p_value_type = 'XML' THEN
195 RETURN l_MR_VARIABLE_XML; */
196 ELSE
197 RETURN l_var_value;
198 END IF;
199
200 END get_orig_var_val;
201
202
203 FUNCTION get_variable_value (
204 p_cat_id IN NUMBER,
205 p_variable_code IN VARCHAR2)
206 RETURN VARCHAR2 IS
207 l_api_name CONSTANT VARCHAR2(30) := 'get_variable_value';
208 CURSOR csr_target_dtls IS
209 SELECT NVL(standard_yn,'N') standard_flag,
210 kart.article_version_id,
211 kart.ORIG_SYSTEM_REFERENCE_ID1
212 FROM okc_articles_all lib,
213 okc_k_articles_b kart
214 WHERE lib.article_id = kart.sav_sae_id
215 AND kart.id= p_cat_id;
216
217 CURSOR csr_source_dtls(p_cat_id IN NUMBER) IS
218 SELECT article_version_id
219 FROM okc_k_articles_b
220 WHERE id = p_cat_id;
221
222 CURSOR csr_source_value(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
223 SELECT VAR.VARIABLE_VALUE
224 FROM OKC_K_ART_VARIABLES VAR
225 WHERE VAR.CAT_ID = p_cat_id
226 AND VAR.VARIABLE_CODE = p_variable_code;
227
228 l_standard_flag VARCHAR2(1);
229 l_ORIG_SYSTEM_REFERENCE_ID1 VARCHAR2(1000);
230 l_target_article_version_id NUMBER;
231 l_source_article_version_id NUMBER;
232 l_source_value VARCHAR2(2000):= NULL;
233
234 BEGIN
235 OPEN csr_target_dtls;
236 FETCH csr_target_dtls INTO l_standard_flag, l_target_article_version_id, l_ORIG_SYSTEM_REFERENCE_ID1;
237 CLOSE csr_target_dtls;
238
239 -- bug 3369336
240 -- copy variable values if the variable exists in the target doc article
241 -- without comparing version or if it was non standard (bug 3397895 )
242
243 OPEN csr_source_value(p_cat_id=>l_ORIG_SYSTEM_REFERENCE_ID1,p_variable_code=>p_variable_code);
244 FETCH csr_source_value INTO l_source_value;
245 CLOSE csr_source_value;
246
247 RETURN l_source_value;
248
249 END get_variable_value;
250
251 --CLM Changes
252 FUNCTION clm_scn_filtering (
253 p_source_doc_id IN NUMBER,
254 p_source_doc_type IN VARCHAR2,
255 p_target_doc_id IN NUMBER,
256 p_target_doc_type IN VARCHAR2)
257 RETURN VARCHAR2 IS
258 l_api_name CONSTANT VARCHAR2(30) := 'clm_scn_filtering';
259 l_source_doc_type_class VARCHAR2(100);
260 l_target_doc_type_class VARCHAR2(100);
261 l_src_amend NUMBER;
262 l_tar_amend NUMBER;
263
264 CURSOR c_get_doc_type_class (c_doc_type VARCHAR2) IS
265 SELECT document_type_class
266 FROM okc_bus_doc_types_b
267 WHERE document_type = c_doc_type;
268
269 CURSOR c_check_sol_amendment(c_doc_id NUMBER) IS
270 SELECT Nvl(AMENDMENT_NUMBER,0) FROM PON_AUCTION_HEADERS_ALL
271 WHERE AUCTION_HEADER_ID = c_doc_id;
272
273 BEGIN
274
275 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
276 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.clm_scn_filtering');
277 END IF;
278
279
280 OPEN c_get_doc_type_class(p_source_doc_type);
281 FETCH c_get_doc_type_class INTO l_source_doc_type_class;
282 CLOSE c_get_doc_type_class;
283
284 OPEN c_get_doc_type_class(p_target_doc_type);
285 FETCH c_get_doc_type_class INTO l_target_doc_type_class;
286 CLOSE c_get_doc_type_class;
287
288 IF l_source_doc_type_class = 'PO' THEN
289 IF p_source_doc_type LIKE '%MOD' AND p_target_doc_type NOT LIKE '%MOD' THEN
290 RETURN 'DROP_AMEND_SEC';
291 END IF;
292 END IF;
293
294 IF l_source_doc_type_class = 'SOURCING' THEN
295 IF p_source_doc_type = 'SOLICITATION' AND p_target_doc_type = 'SOLICITATION' THEN
296
297 OPEN c_check_sol_amendment(p_source_doc_id);
298 FETCH c_check_sol_amendment INTO l_src_amend;
299 CLOSE c_check_sol_amendment;
300 OPEN c_check_sol_amendment(p_target_doc_id);
301 FETCH c_check_sol_amendment INTO l_tar_amend;
302 CLOSE c_check_sol_amendment;
303
304 IF l_src_amend > 0 AND l_tar_amend = 0 THEN
305 RETURN 'DROP_AMEND_SEC';
306 END IF;
307 END IF;
308
309 IF l_target_doc_type_class = 'PO' THEN
310 RETURN 'DROP_PROV_SEC';
311 END IF;
312 END IF;
313
314 RETURN 'DROP_NOTHING';
315 EXCEPTION
316 WHEN OTHERS THEN
317 RETURN 'DROP_NOTHING';
318 END clm_scn_filtering;
319
320
321 procedure copy_article_variables(
322 p_target_doc_type IN VARCHAR2,
323 p_source_doc_type IN VARCHAR2,
324 p_target_doc_id IN NUMBER,
325 p_source_doc_id IN NUMBER,
326 p_get_from_library IN VARCHAR2,
327 p_keep_orig_ref IN VARCHAR2 := 'N',
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_data OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER
331 ,p_retain_lock_terms_yn IN VARCHAR2 DEFAULT 'N'
332 ) IS
333 -- This cursor will get variable code and values either from library
334 l_api_name CONSTANT VARCHAR2(30) := 'copy_article_variables';
335 CURSOR l_get_lib_variables_csr IS
336 SELECT KART.ID CAT_ID,
337 VAR.VARIABLE_CODE,
338 BUSVAR.VARIABLE_TYPE,
339 BUSVAR.EXTERNAL_YN,
340 BUSVAR.VALUE_SET_ID,
341 Decode(Nvl(BUSVAR.mrv_flag,'N'), 'Y', NULL, DECODE(p_keep_orig_ref,'Y',get_orig_var_val(
342 KART.ID,VAR.VARIABLE_CODE,
343 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'VALUE'),
344 get_variable_value(KART.ID,VAR.VARIABLE_CODE)
345 )) VARIABLE_VALUE,
346 Decode (Nvl(BUSVAR.mrv_flag,'N'), 'Y', NULL, DECODE(p_keep_orig_ref,'Y',get_orig_var_val(
347 KART.ID,VAR.VARIABLE_CODE,
348 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'ID'),
349 get_variable_value_id(KART.ID,VAR.VARIABLE_CODE)
350 )) VARIABLE_VALUE_ID,
351 'N' OVERRIDE_GLOBAL_YN,
352 Decode( Nvl(BUSVAR.mrv_flag,'N'), 'Y',
353 DECODE(p_keep_orig_ref,'Y',
354 get_orig_var_val_xml(
355 KART.ID,VAR.VARIABLE_CODE,
356 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'HTML')
357 ,(SELECT src.mr_variable_html FROM okc_k_art_variables src WHERE src.cat_id= KART.orig_system_reference_id1 AND src. variable_code = VAR.variable_code)
358 )
359 ,to_clob(NULL)) mr_variable_html,
360 Decode(Nvl(BUSVAR.mrv_flag,'N'), 'Y', DECODE(p_keep_orig_ref,
361 'Y',get_orig_var_val_xml(
362 KART.ID,VAR.VARIABLE_CODE,
363 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'XML')
364 ,(SELECT src.mr_variable_xml FROM okc_k_art_variables src WHERE src.cat_id= KART.orig_system_reference_id1 AND src.variable_code = VAR.variable_code)
365 )
366 ,to_clob(NULL)) mr_variable_xml,
367
368 Decode(Nvl(BUSVAR.mrv_flag,'N'), 'Y', DECODE(p_keep_orig_ref,
369 'Y', To_Number(get_orig_var_val(
370 KART.ID,VAR.VARIABLE_CODE,
371 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'CAT_ID'))
372 ,KART.orig_system_reference_id1
373 )
374 ,NULL) SourceCatId
375 FROM OKC_ARTICLE_VARIABLES VAR,
376 OKC_K_ARTICLES_B KART,
377 OKC_BUS_VARIABLES_B BUSVAR
378 WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
379 AND KART.DOCUMENT_TYPE=p_target_doc_type
380 AND KART.DOCUMENT_ID=p_target_doc_id
381 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE
382 AND not exists ( select 'x' from okc_k_art_variables where cat_id=kart.id);
383
384 -- This cursor will get variable code and values fromr okc_k_art_variables
385 CURSOR l_get_variables_csr IS
386 SELECT KART.ID CAT_ID,
387 VAR.VARIABLE_CODE,
388 BUSVAR.VARIABLE_TYPE,
389 BUSVAR.EXTERNAL_YN,
390 BUSVAR.VALUE_SET_ID,
391 VAR.VARIABLE_VALUE,
392 VAR.VARIABLE_VALUE_ID,
393 VAR.OVERRIDE_GLOBAL_YN,
394 VAR.mr_variable_html,
395 VAR.mr_variable_xml,
396 KART1.cat_id SourceCatId
397 FROM OKC_K_ART_VARIABLES VAR,
398 OKC_K_ARTICLES_B KART,
399 OKC_K_ARTICLES_B KART1,
400 OKC_BUS_VARIABLES_B BUSVAR
401 WHERE KART.DOCUMENT_TYPE=p_target_doc_type
402 AND KART.DOCUMENT_ID=p_target_doc_id
403 AND KART1.DOCUMENT_TYPE=p_source_doc_type
404 AND KART1.DOCUMENT_ID=p_source_doc_id
405 AND KART.ORIG_SYSTEM_REFERENCE_CODE=G_COPY
406 AND ((KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ID AND P_KEEP_ORIG_REF = 'N') OR
407 (KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ORIG_SYSTEM_REFERENCE_ID1 AND P_KEEP_ORIG_REF = 'Y'))
408 AND KART1.ID=VAR.CAT_ID
409 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE
410 AND ( p_retain_lock_terms_yn = 'N'
411 OR
412 ( p_retain_lock_terms_yn = 'Y'
413 AND NOT EXISTS ( SELECT 'LOCKEXISTS'
414 FROM okc_k_entity_locks
415 WHERE entity_name='CLAUSE'
416 AND entity_pk1 = To_Char(KART.id)
417 AND lock_by_document_type=p_target_doc_type
418 AND lock_by_document_id=p_target_doc_id
419 )
420 )
421 );
422
423 TYPE CatList IS TABLE OF OKC_K_ART_VARIABLES.CAT_ID%TYPE INDEX BY BINARY_INTEGER;
424 TYPE VarList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_CODE%TYPE INDEX BY BINARY_INTEGER;
425 TYPE VarTypeList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE INDEX BY BINARY_INTEGER;
426 TYPE ExternalList IS TABLE OF OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE INDEX BY BINARY_INTEGER;
427 TYPE ValSetList IS TABLE OF OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE INDEX BY BINARY_INTEGER;
428 TYPE VarValList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE INDEX BY BINARY_INTEGER;
429 TYPE VarIdList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE INDEX BY BINARY_INTEGER;
430 TYPE OverrideGlobalYnList IS TABLE OF OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE INDEX BY BINARY_INTEGER;
431 TYPE mrvariablehtmlList IS TABLE OF OKC_K_ART_VARIABLES.mr_variable_html%TYPE INDEX BY BINARY_INTEGER;
432 TYPE mrvariablexmlList IS TABLE OF OKC_K_ART_VARIABLES.mr_variable_xml%TYPE INDEX BY BINARY_INTEGER;
433
434 cat_tbl CatList;
435 var_tbl VarList;
436 var_type_tbl VarTypeList;
437 external_yn_tbl ExternalList;
438 value_set_id_tbl ValSetList;
439 var_value_tbl VarValList;
440 var_value_id_tbl VarIdList;
441 override_global_yn_tbl OverrideGlobalYnList;
442 -- Multi Row Variable project --serukull
443 mrvariablehtml_tbl mrvariablehtmlList;
444 mrvariablexml_tbl mrvariablexmlList;
445 SourceCatIdTbl CatList;
446
447 dochasmrv VARCHAR2(1);
448
449
450
451
452 BEGIN
453
454 x_return_status := G_RET_STS_SUCCESS;
455
456 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
457 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.copy_article_variables');
458 END IF;
459 IF p_get_from_library='Y' THEN
460 -- Bulk collecting
461 OPEN l_get_lib_variables_csr;
462 FETCH l_get_lib_variables_csr BULK COLLECT INTO cat_tbl,
463 var_tbl,
464 var_type_tbl,
465 external_yn_tbl,
466 value_set_id_tbl,
467 var_value_tbl,
468 var_value_id_tbl,
469 override_global_yn_tbl,
470 mrvariablehtml_tbl,
471 mrvariablexml_tbl,
472 SourceCatIdTbl;
473
474 CLOSE l_get_lib_variables_csr;
475
476 ELSE
477 -- Bulk collecting
478 OPEN l_get_variables_csr;
479 FETCH l_get_variables_csr BULK COLLECT INTO cat_tbl,
480 var_tbl,
481 var_type_tbl,
482 external_yn_tbl,
483 value_set_id_tbl,
484 var_value_tbl,
485 var_value_id_tbl,
486 override_global_yn_tbl,
487 mrvariablehtml_tbl,
488 mrvariablexml_tbl,
489 SourceCatIdTbl
490 ;
491 CLOSE l_get_variables_csr;
492 END IF;
493
494 -- Bulk inserting
495 IF cat_tbl.COUNT>0 THEN
496 FORALL i IN cat_tbl.FIRST..cat_tbl.LAST
497 INSERT INTO OKC_K_ART_VARIABLES(cat_id,
498 variable_code,
499 variable_type,
500 external_yn,
501 attribute_value_set_id,
502 variable_value,
503 variable_value_id,
504 override_global_yn,
505 mr_variable_html,
506 mr_variable_xml,
507 object_version_number,
508 creation_date,
509 created_by,
510 last_update_date,
511 last_updated_by,
512 last_update_login)
513 VALUES (cat_tbl(i),
514 var_tbl(i),
515 var_type_tbl(i),
516 external_yn_tbl(i),
517 value_set_id_tbl(i),
518 var_value_tbl(i),
519 var_value_id_tbl(i),
520 override_global_yn_tbl(i),
521 mrvariablehtml_tbl(i),
522 mrvariablexml_tbl(i),
523 1,
524 sysdate,
525 Fnd_Global.User_Id,
526 sysdate,
527 Fnd_Global.User_Id,
528 Fnd_Global.Login_Id);
529
530 END IF;
531
532 -- MRV changes Start
533 -- Check if Source doc has MRV
534
535 okc_mrv_util.checkdochasmrv( docid => p_source_doc_id
536 , doctype =>p_source_doc_type
537 , dochasmrv => dochasmrv);
538
539 IF Nvl(dochasmrv,'N')='Y' THEN
540 FOR i IN SourceCatIdTbl.FIRST..SourceCatIdTbl.LAST
541 LOOP
542 IF mrvariablexml_tbl(i) IS NOT NULL AND SourceCatIdTbl(i) IS NOT NULL THEN
543 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
544 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Calling UDA copy for Cat Id :'||SourceCatIdTbl(i));
545 END IF;
546 -- Call to UDA API.
547 okc_mrv_util.copy_variable_uda_data( p_from_cat_id => SourceCatIdTbl(i),
548 p_from_variable_code => var_tbl(i),
549 p_to_cat_id => cat_tbl(i),
550 p_to_variable_code => var_tbl(i),
551 x_return_status => x_return_status,
552 x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data
554 );
555 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
557 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
558 RAISE FND_API.G_EXC_ERROR ;
559 END IF;
560 END IF; -- mrvariablexml_tbl(i) is not null
561 END LOOP;
562 END IF; -- dochasmrv
563 -- MRV changes End
564
565
566
567
568 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
569
570 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving copy_article_variables '||x_return_status);
572 END IF;
573
574 EXCEPTION
575 WHEN FND_API.G_EXC_ERROR THEN
576
577 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_article_variables:FND_API.G_EXC_ERROR Exception');
579 END IF;
580
581 IF l_get_variables_csr%ISOPEN THEN
582 CLOSE l_get_variables_csr;
583 END IF;
584 x_return_status := G_RET_STS_ERROR;
585 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
586
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588
589 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_article_variables:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
591 END IF;
592
593 IF l_get_variables_csr%ISOPEN THEN
594 CLOSE l_get_variables_csr;
595 END IF;
596 x_return_status := G_RET_STS_UNEXP_ERROR;
597 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
598
599 WHEN OTHERS THEN
600
601 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
602 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_article_variables because of EXCEPTION: '||sqlerrm);
603 END IF;
604
605 IF l_get_variables_csr%ISOPEN THEN
606 CLOSE l_get_variables_csr;
607 END IF;
608 okc_Api.Set_Message(p_app_name => G_APP_NAME,
609 p_msg_name => G_UNEXPECTED_ERROR,
610 p_token1 => G_SQLCODE_TOKEN,
611 p_token1_value => sqlcode,
612 p_token2 => G_SQLERRM_TOKEN,
613 p_token2_value => sqlerrm);
614 x_return_status := G_RET_STS_UNEXP_ERROR;
615 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
616
617 END copy_article_variables;
618
619
620 procedure copy_articles(
621 p_target_doc_type IN VARCHAR2,
622 p_source_doc_type IN VARCHAR2,
623 p_target_doc_id IN NUMBER,
624 P_source_doc_id IN NUMBER,
625 p_keep_version IN VARCHAR2,
626 p_article_effective_date IN DATE,
627 p_source_version_number IN NUMBER := NULL,
628 p_copy_from_archive IN VARCHAR2 := 'N',
629 p_keep_orig_ref IN VARCHAR2 := 'N',
630 x_return_status OUT NOCOPY VARCHAR2,
631 x_msg_data OUT NOCOPY VARCHAR2,
632 x_msg_count OUT NOCOPY NUMBER,
633 p_retain_clauses IN VARCHAR2 DEFAULT 'N'
634 ,p_retain_lock_terms_yn IN VARCHAR2 DEFAULT 'N') IS
635 l_api_name CONSTANT VARCHAR2(30) := 'copy_articles';
636 l_prov_allowed VARCHAR2(1) ;
637 l_discard Boolean;
638 l_standard_yn VARCHAR2(1) ;
639 l_global_yn VARCHAR2(1) ;
640 l_org_id OKC_TERMS_TEMPLATES_ALL.ORG_ID%TYPE;
641 l_art_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
642 k NUMBER := 0;
643
644 TYPE SavSaeIdList IS TABLE OF OKC_K_ARTICLES_B.SAV_SAE_ID%TYPE INDEX BY BINARY_INTEGER;
645 TYPE AttributeCategoryList IS TABLE OF OKC_K_ARTICLES_B.ATTRIBUTE_CATEGORY%TYPE INDEX BY BINARY_INTEGER;
646 TYPE AttributeList IS TABLE OF OKC_K_ARTICLES_B.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
647 TYPE SourceFlagList IS TABLE OF OKC_K_ARTICLES_B.SOURCE_FLAG%TYPE INDEX BY BINARY_INTEGER;
648 TYPE ArticleVersionIdList IS TABLE OF OKC_K_ARTICLES_B.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
649 TYPE MandatoryYnList IS TABLE OF OKC_K_ARTICLES_B.MANDATORY_YN%TYPE INDEX BY BINARY_INTEGER;
650 TYPE MandatoryRwaList IS TABLE OF OKC_K_ARTICLES_B.MANDATORY_RWA%TYPE INDEX BY BINARY_INTEGER;
651 TYPE ChangeNonStdYnList IS TABLE OF OKC_K_ARTICLES_B.CHANGE_NONSTD_YN%TYPE INDEX BY BINARY_INTEGER;
652 TYPE ScnIdList IS TABLE OF OKC_K_ARTICLES_B.SCN_ID%TYPE INDEX BY BINARY_INTEGER;
653 TYPE OrigSystemReferenceId1List IS TABLE OF OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE INDEX BY BINARY_INTEGER;
654 TYPE LabelList IS TABLE OF OKC_K_ARTICLES_B.LABEL%TYPE INDEX BY BINARY_INTEGER;
655 TYPE DisplaySequenceList IS TABLE OF OKC_K_ARTICLES_B.DISPLAY_SEQUENCE%TYPE INDEX BY BINARY_INTEGER;
656 TYPE RefArticleIdList IS TABLE OF OKC_K_ARTICLES_B.REF_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
657 TYPE RefArticleVersionIdList IS TABLE OF OKC_K_ARTICLES_B.REF_ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
658 TYPE OrigArticleIdList IS TABLE OF OKC_K_ARTICLES_B.ORIG_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
659
660 sav_sae_tbl SavSaeIdList;
661 Attribute_category_tbl AttributeCategoryList;
662 Attribute1_tbl AttributeList;
663 Attribute2_tbl AttributeList;
664 Attribute3_tbl AttributeList;
665 Attribute4_tbl AttributeList;
666 Attribute5_tbl AttributeList;
667 Attribute6_tbl AttributeList;
668 Attribute7_tbl AttributeList;
669 Attribute8_tbl AttributeList;
670 Attribute9_tbl AttributeList;
671 Attribute10_tbl AttributeList;
672 Attribute11_tbl AttributeList;
673 Attribute12_tbl AttributeList;
674 Attribute13_tbl AttributeList;
675 Attribute14_tbl AttributeList;
676 Attribute15_tbl AttributeList;
677 Ref_article_id_tbl RefArticleIdList;
678 Ref_article_version_id_tbl RefArticleVersionIdList;
679 orig_article_id_tbl OrigArticleIdList;
680
681 Source_flag_tbl SourceFlagList;
682 Article_Version_tbl ArticleVersionIdList;
683 Change_nonstd_yn_tbl ChangeNonStdYnList;
684 Scn_id_tbl ScnIdList;
685 Orig_System_Reference_id1_tbl OrigSystemReferenceId1List;
686 Mandatory_yn_tbl MandatoryYnList;
687 Mandatory_rwa_tbl MandatoryRwaList;
688 Label_tbl LabelList;
689 Display_sequence_tbl DisplaySequenceList;
690
691 sav_sae_tbl1 SavSaeIdList;
692 Attribute_category_tbl1 AttributeCategoryList;
693 Attribute1_tbl1 AttributeList;
694 Attribute2_tbl1 AttributeList;
695 Attribute3_tbl1 AttributeList;
696 Attribute4_tbl1 AttributeList;
697 Attribute5_tbl1 AttributeList;
698 Attribute6_tbl1 AttributeList;
699 Attribute7_tbl1 AttributeList;
700 Attribute8_tbl1 AttributeList;
701 Attribute9_tbl1 AttributeList;
702 Attribute10_tbl1 AttributeList;
703 Attribute11_tbl1 AttributeList;
704 Attribute12_tbl1 AttributeList;
705 Attribute13_tbl1 AttributeList;
706 Attribute14_tbl1 AttributeList;
707 Attribute15_tbl1 AttributeList;
708 Ref_article_id_tbl1 RefArticleIdList;
709 Ref_article_version_id_tbl1 RefArticleVersionIdList;
710 orig_article_id_tbl1 OrigArticleIdList;
711
712 Source_flag_tbl1 SourceFlagList;
713 Article_Version_tbl1 ArticleVersionIdList;
714 Change_nonstd_yn_tbl1 ChangeNonStdYnList;
715 Scn_id_tbl1 ScnIdList;
716 Orig_System_Reference_id1_tbl1 OrigSystemReferenceId1List;
717 Mandatory_yn_tbl1 MandatoryYnList;
718 Mandatory_rwa_tbl1 MandatoryRwaList;
719 Label_tbl1 LabelList;
720 Display_sequence_tbl1 DisplaySequenceList;
721
722 CURSOR l_get_prov_csr IS
723 SELECT nvl(PROVISION_ALLOWED_YN,'Y') FROM OKC_BUS_DOC_TYPES_B
724 WHERE DOCUMENT_TYPE=p_target_doc_type;
725
726 CURSOR l_get_std_csr(b_article_id NUMBER) IS
727 SELECT STANDARD_YN,ARTICLE_TITLE FROM OKC_ARTICLES_ALL
728 WHERE article_id=b_article_id;
729
730 CURSOR l_get_global_csr IS
731 SELECT global_flag FROM OKC_TERMS_TEMPLATES_ALL
732 WHERE template_id=p_source_doc_id;
733
734 CURSOR l_get_org_csr IS
735 SELECT org_id FROM OKC_TERMS_TEMPLATES_ALL
736 WHERE template_id=p_target_doc_id;
737
738 CURSOR l_get_latest_article_csr(b_article_id NUMBER) IS
739 SELECT article_version_id FROM OKC_ARTICLE_VERSIONS
740 WHERE article_id= b_article_id
741 AND article_status in ('ON_HOLD','APPROVED')
742 AND nvl(p_article_effective_date,sysdate) >= Start_date
743 AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1);
744
745 CURSOR l_get_max_article_csr(b_article_id NUMBER) IS
746 SELECT article_version_id FROM OKC_ARTICLE_VERSIONS
747 WHERE article_id= b_article_id
748 AND article_status in ('ON_HOLD','APPROVED')
749 AND start_date = (select max(start_date) FROM OKC_ARTICLE_VERSIONS
750 WHERE article_id= b_article_id
751 AND article_status in ('ON_HOLD','APPROVED') );
752
753 CURSOR l_get_no_std_ref_csr(b_version_ID NUMBER) IS
754 SELECT VERS2.ARTICLE_ID,VERS2.ARTICLE_VERSION_ID
755 FROM OKC_ARTICLE_VERSIONS VERS1,OKC_ARTICLE_VERSIONS VERS2
756 WHERE VERS1.ARTICLE_VERSION_ID=b_version_id
757 AND VERS2.ARTICLE_VERSION_ID=VERS1.STD_ARTICLE_VERSION_ID;
758
759 CURSOR l_get_art_csr IS
760 SELECT
761 SAV_SAE_ID,
762 KART.ATTRIBUTE_CATEGORY,
763 KART.ATTRIBUTE1,
764 KART.ATTRIBUTE2,
765 KART.ATTRIBUTE3,
766 KART.ATTRIBUTE4,
767 KART.ATTRIBUTE5,
768 KART.ATTRIBUTE6,
769 KART.ATTRIBUTE7,
770 KART.ATTRIBUTE8,
771 KART.ATTRIBUTE9,
772 KART.ATTRIBUTE10,
773 KART.ATTRIBUTE11,
774 KART.ATTRIBUTE12,
775 KART.ATTRIBUTE13,
776 KART.ATTRIBUTE14,
777 KART.ATTRIBUTE15,
778 SOURCE_FLAG,
779 ARTICLE_VERSION_ID,
780 CHANGE_NONSTD_YN,
781 SCN.ID SCN_ID,
782 DECODE(P_KEEP_ORIG_REF,'Y',KART.ORIG_SYSTEM_REFERENCE_ID1,KART.ID) ORIG_SYSTEM_REFERENCE_ID1,
783 MANDATORY_YN,
784 MANDATORY_RWA,
785 KART.LABEL,
786 DISPLAY_SEQUENCE,
787 ref_article_id,
788 ref_article_version_id,
789 DECODE(p_source_doc_type,G_TEMPLATE_DOC_TYPE,sav_sae_id,orig_article_id) orig_article_id
790 FROM OKC_K_ARTICLES_B KART,
791 OKC_SECTIONS_B SCN
792 WHERE KART.DOCUMENT_TYPE=p_source_doc_type
793 AND KART.DOCUMENT_ID=p_source_doc_id
794 AND SCN.DOCUMENT_TYPE = p_target_doc_type
795 AND SCN.DOCUMENT_ID =p_target_doc_id
796 AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
797 AND SCN.ORIG_SYSTEM_REFERENCE_ID1=KART.SCN_ID
798 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
799 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
800 AND ( p_retain_lock_terms_yn = 'N'
801 OR
802 ( p_retain_lock_terms_yn = 'Y'
803 AND NOT EXISTS ( SELECT 'LOCKEXISTS'
804 FROM okc_k_entity_locks
805 WHERE entity_name='CLAUSE'
806 AND entity_pk1 = To_Char(KART.id)
807 AND lock_by_document_type=p_target_doc_type
808 AND lock_by_document_id=p_target_doc_id
809 )
810 )
811 );
812
813 CURSOR l_get_orig_art_csr IS
814 SELECT
815 KART.SAV_SAE_ID,
816 KART.ATTRIBUTE_CATEGORY,
817 KART.ATTRIBUTE1,
818 KART.ATTRIBUTE2,
819 KART.ATTRIBUTE3,
820 KART.ATTRIBUTE4,
821 KART.ATTRIBUTE5,
822 KART.ATTRIBUTE6,
823 KART.ATTRIBUTE7,
824 KART.ATTRIBUTE8,
825 KART.ATTRIBUTE9,
826 KART.ATTRIBUTE10,
827 KART.ATTRIBUTE11,
828 KART.ATTRIBUTE12,
829 KART.ATTRIBUTE13,
830 KART.ATTRIBUTE14,
831 KART.ATTRIBUTE15,
832 KART.SOURCE_FLAG,
833 KART.ARTICLE_VERSION_ID,
834 KART.CHANGE_NONSTD_YN,
835 SCN.ID SCN_ID,
836 KART.ORIG_SYSTEM_REFERENCE_ID1 ORIG_SYSTEM_REFERENCE_ID1,
837 KART.MANDATORY_YN,
838 KART.MANDATORY_RWA,
839 KART.LABEL,
840 KART.DISPLAY_SEQUENCE,
841 KART.ref_article_id,
842 KART.ref_article_version_id,
843 DECODE(p_source_doc_type,G_TEMPLATE_DOC_TYPE,KART.sav_sae_id,KART.orig_article_id) orig_article_id
844 FROM OKC_K_ARTICLES_B KART,
845 OKC_SECTIONS_B SCN,
846 OKC_SECTIONS_B SCN1,
847 OKC_K_ARTICLES_B KART1
848 WHERE KART.DOCUMENT_TYPE= p_source_doc_type
849 AND KART.DOCUMENT_ID= p_source_doc_id
850 AND SCN.DOCUMENT_TYPE = p_target_doc_type
851 AND SCN.DOCUMENT_ID = p_target_doc_id
852 AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
853 AND SCN1.DOCUMENT_TYPE = p_source_doc_type
854 AND SCN1.DOCUMENT_ID = p_source_doc_id
855 AND SCN1.ORIG_SYSTEM_REFERENCE_ID1 = SCN.ORIG_SYSTEM_REFERENCE_ID1
856 AND KART1.ORIG_SYSTEM_REFERENCE_ID1 = KART.ORIG_SYSTEM_REFERENCE_ID1
857 AND KART1.SCN_ID = SCN1.ID
858 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
859 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
860
861 CURSOR l_get_art_from_archive_csr IS
862 SELECT
863 SAV_SAE_ID,
864 KART.ATTRIBUTE_CATEGORY,
865 KART.ATTRIBUTE1,
866 KART.ATTRIBUTE2,
867 KART.ATTRIBUTE3,
868 KART.ATTRIBUTE4,
869 KART.ATTRIBUTE5,
870 KART.ATTRIBUTE6,
871 KART.ATTRIBUTE7,
872 KART.ATTRIBUTE8,
873 KART.ATTRIBUTE9,
874 KART.ATTRIBUTE10,
875 KART.ATTRIBUTE11,
876 KART.ATTRIBUTE12,
877 KART.ATTRIBUTE13,
878 KART.ATTRIBUTE14,
879 KART.ATTRIBUTE15,
880 SOURCE_FLAG,
881 ARTICLE_VERSION_ID,
882 CHANGE_NONSTD_YN,
883 SCN.ID SCN_ID,
884 DECODE(P_KEEP_ORIG_REF,'Y',KART.ORIG_SYSTEM_REFERENCE_ID1,KART.ID) ORIG_SYSTEM_REFERENCE_ID1,
885 MANDATORY_YN,
886 MANDATORY_RWA,
887 KART.LABEL,
888 DISPLAY_SEQUENCE,
889 ref_article_id,
890 ref_article_version_id,
891 orig_article_id
892 FROM OKC_K_ARTICLES_BH KART,
893 OKC_SECTIONS_B SCN
894 WHERE KART.DOCUMENT_TYPE=p_source_doc_type
895 AND KART.DOCUMENT_ID=p_source_doc_id
896 AND KART.MAJOR_VERSION = nvl(p_source_version_number,OKC_API.G_MISS_NUM)
897 AND SCN.DOCUMENT_TYPE = p_target_doc_type
898 AND SCN.DOCUMENT_ID =p_target_doc_id
899 AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
900 AND SCN.ORIG_SYSTEM_REFERENCE_ID1=KART.SCN_ID;
901
902 CURSOR l_get_local_article_csr(b_article_id IN NUMBER, b_local_org_id IN NUMBER) IS
903 SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
904 ADP.ADOPTION_TYPE,
905 VERS1.ARTICLE_ID
906 FROM OKC_ARTICLE_VERSIONS VERS,
907 OKC_ARTICLE_ADOPTIONS ADP,
908 OKC_ARTICLE_VERSIONS VERS1
909 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
910 AND VERS.ARTICLE_ID = b_article_id
911 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
912 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
913 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
914 AND VERS1.ARTICLE_VERSION_ID =ADP.LOCAL_ARTICLE_VERSION_ID
915 AND ADP.ADOPTION_TYPE = 'LOCALIZED'
916 AND ADP.LOCAL_ORG_ID = b_local_org_id
917 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
918 UNION ALL
919 SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
920 ADP.ADOPTION_TYPE,
921 VERS.ARTICLE_ID
922 FROM OKC_ARTICLE_VERSIONS VERS,
923 OKC_ARTICLE_ADOPTIONS ADP
924 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
925 AND VERS.ARTICLE_ID = b_article_id
926 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
927 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
928 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
929 AND ADP.ADOPTION_TYPE = 'ADOPTED'
930 AND ADP.LOCAL_ORG_ID = b_local_org_id
931 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
932 ;
933
934 CURSOR l_get_article_csr(b_article_version_id NUMBER) IS
935 SELECT nvl(PROVISION_YN,'N') provision_yn
936 FROM OKC_ARTICLE_VERSIONS
937 WHERE ARTICLE_VERSION_ID=b_article_version_id;
938
939 l_article_rec l_get_article_csr%ROWTYPE;
940 l_local_article_rec l_get_local_article_csr%ROWTYPE;
941 x_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
942
943 l_article_org_id NUMBER;
944
945 CURSOR l_get_article_org_csr(b_article_id NUMBER) IS
946 SELECT org_id
947 FROM OKC_ARTICLES_ALL
948 WHERE article_id = b_article_id;
949
950 CURSOR l_get_max_local_article_csr(b_article_id IN NUMBER, b_article_org_id IN NUMBER) IS
951 SELECT DECODE(ADP.LOCAL_ARTICLE_VERSION_ID,NULL,ADP.GLOBAL_ARTICLE_VERSION_ID,ADP.LOCAL_ARTICLE_VERSION_ID),
952 ADP.ADOPTION_TYPE
953 FROM OKC_ARTICLE_ADOPTIONS ADP
954 WHERE ADP.LOCAL_ORG_ID = b_article_org_id
955 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
956 AND ADP.GLOBAL_ARTICLE_VERSION_ID IN (SELECT ARTICLE_VERSION_ID
957 FROM OKC_ARTICLE_VERSIONS
958 WHERE article_id = b_article_id)
959 ORDER BY ADP.creation_date desc;
960
961 CURSOR l_get_local_article_id(b_article_version_id IN NUMBER) IS
962 SELECT article_id
963 FROM okc_article_versions
964 WHERE article_version_id = b_article_version_id;
965
966 l_current_org_id VARCHAR2(100);
967 l_adoption_type VARCHAR2(100);
968 l_local_article_id NUMBER;
969 l_max_sequence NUMBER;
970
971 CURSOR l_art_sqn_csr IS
972 SELECT Max(DISPLAY_SEQUENCE) FROM okc_k_articles_b
973 WHERE DOCUMENT_TYPE=P_TARGET_DOC_TYPE
974 AND DOCUMENT_ID=P_TARGET_DOC_ID;
975
976
977 -------------------- CONC MOD CHANGES START -------------
978 CURSOR cur_orphan_clauses
979 IS
980 SELECT tgt.id,tgtsec.id, lck.k_entity_lock_id
981 FROM okc_k_articles_b tgt, okc_k_entity_locks lck, okc_sections_b tgtsec
982 WHERE tgt.document_type= p_target_doc_type
983 AND tgt.document_id= p_target_doc_id
984 AND tgt.summary_amend_operation_code = 'ADDED'
985 AND lck.entity_name='DUMMYSEC'
986 AND lck.lock_by_document_type=p_target_doc_type
987 AND lck.lock_by_document_id= p_target_doc_id
988 AND tgt.scn_id = lck.lock_by_entity_id
989 AND tgtsec.document_type = p_target_doc_type
990 AND tgtsec.document_id = p_target_doc_id
991 AND lck.entity_pk1 = tgtsec.ORIG_SYSTEM_REFERENCE_ID1
992 ;
993
994 CURSOR cur_orphan_upd_clauses
995 IS
996 SELECT tgtart.id,tgtsec.id
997 FROM okc_k_articles_b tgtart
998 ,okc_k_articles_b srcart
999 ,okc_sections_b tgtsec
1000 WHERE 1 = 1
1001 -- Target document clauses
1002 AND tgtart.document_type= p_target_doc_type
1003 AND tgtart.document_id= p_target_doc_id
1004 AND tgtart.summary_amend_operation_code IN ('UPDATED','DELTED')
1005 AND tgtart.orig_system_reference_id1 = srcart.id
1006 AND srcart.document_type=p_source_doc_type
1007 AND srcart.document_id= p_source_doc_id
1008 AND tgtsec.document_type = p_target_doc_type
1009 AND tgtsec.document_id = p_target_doc_id
1010 AND srcart.scn_id=tgtsec.ORIG_SYSTEM_REFERENCE_ID1;
1011
1012 TYPE CatList IS TABLE OF okc_k_articles_b.ID%TYPE INDEX BY BINARY_INTEGER;
1013
1014 tgt_cat_tbl CatList;
1015 tgt_scn_tbl CatList;
1016 tgt_scn_upd_lock_tbl CatList;
1017 tgt_cat_upd_tbl CatList;
1018 tgt_scn_upd_tbl CatList;
1019
1020 -------------------- CONC MOD CHANGES END -------------
1021
1022 BEGIN
1023
1024 x_return_status := G_RET_STS_SUCCESS;
1025
1026 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1027 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.copy_articles');
1028 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Parameters ');
1029 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_type : '||p_target_doc_type);
1030 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_id : '||p_target_doc_id);
1031 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_type : '||p_source_doc_type);
1032 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_id : '||p_source_doc_id);
1033 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_keep_version : '||p_keep_version);
1034 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_article_effective_date : '||p_article_effective_date);
1035 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_version_number : '||p_source_version_number);
1036 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_copy_from_archive : '||p_copy_from_archive);
1037 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_retain_lock_terms_yn : '||p_retain_lock_terms_yn);
1038 END IF;
1039
1040 OPEN l_get_prov_csr;
1041 FETCH l_get_prov_csr into l_prov_allowed;
1042 CLOSE l_get_prov_csr;
1043
1044 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1045 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_prov_allowed : '||l_prov_allowed);
1046 END IF;
1047
1048 IF p_copy_from_archive ='Y' THEN
1049 OPEN l_get_art_from_archive_csr;
1050 FETCH l_get_art_from_archive_csr BULK COLLECT INTO
1051 sav_sae_tbl,
1052 Attribute_category_tbl,
1053 Attribute1_tbl,
1054 Attribute2_tbl,
1055 Attribute3_tbl,
1056 Attribute4_tbl,
1057 Attribute5_tbl,
1058 Attribute6_tbl,
1059 Attribute7_tbl,
1060 Attribute8_tbl,
1061 Attribute9_tbl,
1062 Attribute10_tbl,
1063 Attribute11_tbl,
1064 Attribute12_tbl,
1065 Attribute13_tbl,
1066 Attribute14_tbl,
1067 Attribute15_tbl,
1068 Source_flag_tbl,
1069 Article_Version_tbl,
1070 Change_nonstd_yn_tbl,
1071 Scn_id_tbl,
1072 Orig_System_Reference_id1_tbl,
1073 Mandatory_yn_tbl,
1074 Mandatory_rwa_tbl,
1075 Label_tbl,
1076 Display_sequence_tbl,
1077 ref_article_id_tbl,
1078 ref_article_version_id_tbl,
1079 orig_article_id_tbl;
1080 CLOSE l_get_art_from_archive_csr;
1081 ELSE
1082 -- p_copy_from_archive is N
1083 IF NVL(p_keep_orig_ref,'N') = 'N' THEN
1084 OPEN l_get_art_csr;
1085 FETCH l_get_art_csr BULK COLLECT INTO
1086 sav_sae_tbl,
1087 Attribute_category_tbl,
1088 Attribute1_tbl,
1089 Attribute2_tbl,
1090 Attribute3_tbl,
1091 Attribute4_tbl,
1092 Attribute5_tbl,
1093 Attribute6_tbl,
1094 Attribute7_tbl,
1095 Attribute8_tbl,
1096 Attribute9_tbl,
1097 Attribute10_tbl,
1098 Attribute11_tbl,
1099 Attribute12_tbl,
1100 Attribute13_tbl,
1101 Attribute14_tbl,
1102 Attribute15_tbl,
1103 Source_flag_tbl,
1104 Article_Version_tbl,
1105 Change_nonstd_yn_tbl,
1106 Scn_id_tbl,
1107 Orig_System_Reference_id1_tbl,
1108 Mandatory_yn_tbl,
1109 Mandatory_rwa_tbl,
1110 Label_tbl,
1111 Display_sequence_tbl,
1112 ref_article_id_tbl,
1113 ref_article_version_id_tbl,
1114 orig_article_id_tbl;
1115 CLOSE l_get_art_csr;
1116 ELSE
1117 OPEN l_get_orig_art_csr;
1118 FETCH l_get_orig_art_csr BULK COLLECT INTO
1119 sav_sae_tbl,
1120 Attribute_category_tbl,
1121 Attribute1_tbl,
1122 Attribute2_tbl,
1123 Attribute3_tbl,
1124 Attribute4_tbl,
1125 Attribute5_tbl,
1126 Attribute6_tbl,
1127 Attribute7_tbl,
1128 Attribute8_tbl,
1129 Attribute9_tbl,
1130 Attribute10_tbl,
1131 Attribute11_tbl,
1132 Attribute12_tbl,
1133 Attribute13_tbl,
1134 Attribute14_tbl,
1135 Attribute15_tbl,
1136 Source_flag_tbl,
1137 Article_Version_tbl,
1138 Change_nonstd_yn_tbl,
1139 Scn_id_tbl,
1140 Orig_System_Reference_id1_tbl,
1141 Mandatory_yn_tbl,
1142 Mandatory_rwa_tbl,
1143 Label_tbl,
1144 Display_sequence_tbl,
1145 ref_article_id_tbl,
1146 ref_article_version_id_tbl,
1147 orig_article_id_tbl;
1148 CLOSE l_get_orig_art_csr;
1149 END IF;
1150 END IF; -- p_copy_from_archive
1151
1152 IF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1153 AND p_target_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
1154
1155 OPEN l_get_global_csr;
1156 FETCH l_get_global_csr into l_global_yn;
1157 CLOSE l_get_global_csr;
1158
1159 OPEN l_get_org_csr;
1160 FETCH l_get_org_csr into l_org_id;
1161 CLOSE l_get_org_csr;
1162
1163 END IF; -- source and target are templates
1164
1165 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Source Template l_global_yn : '||l_global_yn);
1167 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Target Template l_org_id : '||l_org_id);
1168 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Count of Articles on Source : '||sav_sae_tbl.COUNT);
1169 END IF;
1170
1171 IF sav_sae_tbl.COUNT > 0 THEN
1172 FOR i IN sav_sae_tbl.FIRST..sav_sae_tbl.LAST LOOP
1173 l_discard := false;
1174
1175 OPEN l_get_std_csr(sav_sae_tbl(i));
1176 FETCH l_get_std_csr INTO l_standard_yn,l_art_title;
1177 CLOSE l_get_std_csr;
1178
1179 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_standard_yn : '||l_standard_yn);
1181 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_art_title : '||l_art_title);
1182 END IF;
1183
1184 IF p_keep_version = 'N' AND l_standard_yn='Y'
1185 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
1186
1187 -- check if Article is global or local
1188 OPEN l_get_article_org_csr(sav_sae_tbl(i));
1189 FETCH l_get_article_org_csr INTO l_article_org_id;
1190 CLOSE l_get_article_org_csr;
1191
1192 -- current Org Id
1193 -- fnd_profile.get('ORG_ID',l_current_org_id);
1194 l_current_org_id := OKC_TERMS_UTIL_PVT.get_current_org_id(p_target_doc_type, p_target_doc_id);
1195
1196 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1197 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_article_org_id : '||l_article_org_id);
1198 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_current_org_id : '||l_current_org_id);
1199 END IF;
1200
1201
1202 -- since p_keep_version is N we will initialize the article_version_id
1203 article_version_tbl(i) := NULL;
1204
1205 IF nvl(l_current_org_id,'?') <> l_article_org_id THEN
1206 -- this is a ADOPTED OR LOCALIZED ARTICLE
1207
1208 OPEN l_get_local_article_csr(sav_sae_tbl(i), l_current_org_id);
1209 FETCH l_get_local_article_csr INTO l_local_article_rec;
1210
1211 IF l_get_local_article_csr%NOTFOUND THEN
1212 -- check for max version
1213 OPEN l_get_max_local_article_csr(sav_sae_tbl(i),l_current_org_id);
1214 FETCH l_get_max_local_article_csr INTO article_version_tbl(i),l_adoption_type;
1215
1216 IF l_get_max_local_article_csr%NOTFOUND THEN
1217 -- discard this record
1218 l_discard := true;
1219 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1220 p_msg_name => 'OKC_ART_NOT_ADOPTED',
1221 p_token1 => 'ART_TITLE',
1222 p_token1_value => l_art_title,
1223 p_token2 => 'ORG_ID',
1224 p_token2_value => l_current_org_id);
1225 ELSE -- max record found
1226 IF l_adoption_type = 'LOCALIZED' THEN
1227 -- get the local article id and swap
1228 OPEN l_get_local_article_id(article_version_tbl(i));
1229 FETCH l_get_local_article_id INTO l_local_article_id;
1230 CLOSE l_get_local_article_id;
1231 -- SWAP Article Id
1232 sav_sae_tbl(i) := l_local_article_id;
1233 END IF; -- for adoption_type LOCALIZED
1234
1235 END IF; -- max record not found
1236
1237 CLOSE l_get_max_local_article_csr;
1238
1239 ELSE
1240 -- local record found
1241 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1242 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: this is a ADOPTED OR LOCALIZED ARTICLE');
1243 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_local_article_rec.adoption_type : '||l_local_article_rec.adoption_type);
1244 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_local_article_rec.article_id : '||l_local_article_rec.article_id);
1245 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: LOCAL_ARTICLE_VERSION_ID : '||l_local_article_rec.LOCAL_ARTICLE_VERSION_ID);
1246 END IF;
1247
1248 IF l_local_article_rec.adoption_type = 'LOCALIZED' THEN
1249 sav_sae_tbl(i) := l_local_article_rec.article_id;
1250 ELSIF l_local_article_rec.adoption_type = 'ADOPTED' THEN
1251 NULL;
1252 END IF;
1253
1254 article_version_tbl(i) := l_local_article_rec.LOCAL_ARTICLE_VERSION_ID;
1255
1256 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1257 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_get_local_article_csr : '||article_version_tbl(i));
1258 END IF;
1259
1260 END IF; -- for local record found
1261 CLOSE l_get_local_article_csr;
1262
1263 ELSE
1264 -- this is local article
1265 OPEN l_get_latest_article_csr(sav_sae_tbl(i));
1266 FETCH l_get_latest_article_csr INTO article_version_tbl(i);
1267 CLOSE l_get_latest_article_csr;
1268
1269 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1270 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: this is a Local ARTICLE');
1271 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_get_latest_article_csr : '||article_version_tbl(i));
1272 END IF;
1273
1274 IF p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1275 AND article_version_tbl(i) IS NULL THEN
1276 OPEN l_get_max_article_csr(sav_sae_tbl(i));
1277 FETCH l_get_max_article_csr INTO article_version_tbl(i);
1278 CLOSE l_get_max_article_csr;
1279
1280 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_get_max_article_csr : '||article_version_tbl(i));
1282 END IF;
1283
1284 -- if article_version_id is still null then discard this record
1285 IF article_version_tbl(i) IS NULL THEN
1286 l_discard := true;
1287 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1288 p_msg_name => 'OKC_ART_NO_APP_VERSION',
1289 p_token1 => 'ART_TITLE',
1290 p_token1_value => l_art_title);
1291 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1292 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Discarding Local Article id : '||sav_sae_tbl(i));
1293 END IF;
1294 END IF; -- if article_version_id is still null
1295
1296 END IF; -- for target document type not template
1297
1298 END IF; -- for adopted/localized or local article
1299
1300 ELSIF l_standard_yn='N' THEN
1301
1302 -- Copying Non-Standard Article
1303
1304 OKC_ARTICLES_GRP.copy_article( p_api_version => 1,
1305 p_init_msg_list => FND_API.G_FALSE,
1306 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1307 p_commit => FND_API.G_FALSE,
1308 p_article_version_id => article_version_tbl(i),
1309 p_new_article_title => NULL,
1310 p_create_standard_yn => 'N',
1311 x_article_version_id => article_version_tbl(i),
1312 x_article_id => sav_sae_tbl(i),
1313 x_article_number => x_article_number,
1314 x_return_status => x_return_status,
1315 x_msg_count => x_msg_count,
1316 x_msg_data => x_msg_data);
1317
1318 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1320 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1321 RAISE FND_API.G_EXC_ERROR ;
1322 END IF;
1323 /*
1324 OPEN l_get_no_std_ref_csr(article_version_tbl(i));
1325 FETCH l_get_no_std_ref_csr INTO ref_article_id_tbl(i),ref_article_version_id_tbl(i);
1326 IF l_get_no_std_ref_csr%NOTFOUND THEN
1327 ref_article_id_tbl(i):=NULL;
1328 ref_article_version_id_tbl(i):=NULL;
1329 END IF;
1330 CLOSE l_get_no_std_ref_csr;
1331 */
1332 END IF; -- p_keep_version = N and std or non std art
1333
1334 IF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1335 AND p_target_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1336 AND l_standard_yn='Y' THEN
1337
1338 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1339 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Template To Template Copy l_global_yn : '||l_global_yn);
1340 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Profile OKC_GLOBAL_ORG_ID : '||fnd_profile.value('OKC_GLOBAL_ORG_ID'));
1341 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100:Target Template org_id : '||l_org_id);
1342 END IF;
1343
1344 IF l_global_yn = 'Y'
1345 AND nvl(fnd_profile.value('OKC_GLOBAL_ORG_ID'),'?') <> l_org_id THEN
1346
1347 OPEN l_get_local_article_csr(sav_sae_tbl(i),l_org_id);
1348 FETCH l_get_local_article_csr INTO l_local_article_rec;
1349
1350 IF l_get_local_article_csr%NOTFOUND THEN
1351 -- go for the max article that was adopted or localized
1352 OPEN l_get_max_local_article_csr(sav_sae_tbl(i),l_org_id);
1353 FETCH l_get_max_local_article_csr INTO article_version_tbl(i),l_adoption_type;
1354
1355 IF l_get_max_local_article_csr%NOTFOUND THEN
1356 l_discard := true;
1357 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1358 p_msg_name => 'OKC_ART_NOT_ADOPTED',
1359 p_token1 => 'ART_TITLE',
1360 p_token1_value => l_art_title,
1361 p_token2 => 'ORG_ID',
1362 p_token2_value => l_org_id);
1363 ELSE -- max record found
1364 IF l_adoption_type = 'LOCALIZED' THEN
1365 -- get the local article id and swap
1366 OPEN l_get_local_article_id(article_version_tbl(i));
1367 FETCH l_get_local_article_id INTO l_local_article_id;
1368 CLOSE l_get_local_article_id;
1369 -- SWAP Article Id
1370 sav_sae_tbl(i) := l_local_article_id;
1371 END IF; -- l_adoption_type = LOCALIZED
1372
1373 END IF; -- max article csr
1374
1375 CLOSE l_get_max_local_article_csr;
1376
1377 ELSE -- local_article_csr found
1378
1379 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1380 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_local_article_rec.adoption_type : '||l_local_article_rec.adoption_type);
1381 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_local_article_rec.article_id : '||l_local_article_rec.article_id);
1382 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_local_article_rec.LOCAL_ARTICLE_VERSION_ID : '||l_local_article_rec.LOCAL_ARTICLE_VERSION_ID);
1383 END IF;
1384
1385 IF l_local_article_rec.adoption_type = 'LOCALIZED' THEN
1386 sav_sae_tbl(i) := l_local_article_rec.article_id;
1387 ELSIF l_local_article_rec.adoption_type = 'ADOPTED' THEN
1388 NULL;
1389 END IF;
1390
1391 END IF; -- record not found
1392
1393 CLOSE l_get_local_article_csr;
1394
1395 END IF; -- l_global_yn is Y
1396
1397 END IF; -- template to template copy
1398
1399 IF p_target_doc_type <>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
1400
1401 OPEN l_get_article_csr(article_version_tbl(i));
1402 FETCH l_get_article_csr INTO l_article_rec;
1403 CLOSE l_get_article_csr;
1404 IF l_article_rec.provision_yn='Y' and l_prov_allowed='N' THEN
1405 l_discard := true;
1406 END IF;
1407
1408 END IF; -- target not template
1409
1410 IF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1411 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
1412
1413 source_flag_tbl(i) := 'T';
1414
1415 END IF; -- template to document copy
1416
1417 IF NOT l_discard THEN
1418
1419 k := k +1;
1420 sav_sae_tbl1(k) := sav_sae_tbl(i);
1421 Attribute_category_tbl1(k) := Attribute_category_tbl(i);
1422 Attribute1_tbl1(k) := Attribute1_tbl(i);
1423 Attribute2_tbl1(k) := Attribute2_tbl(i);
1424 Attribute3_tbl1(k) := Attribute3_tbl(i);
1425 Attribute4_tbl1(k) := Attribute4_tbl(i);
1426 Attribute5_tbl1(k) := Attribute5_tbl(i);
1427 Attribute6_tbl1(k) := Attribute6_tbl(i);
1428 Attribute7_tbl1(k) := Attribute7_tbl(i);
1429 Attribute8_tbl1(k) := Attribute8_tbl(i);
1430 Attribute9_tbl1(k) := Attribute9_tbl(i);
1431 Attribute10_tbl1(k) := Attribute10_tbl(i);
1432 Attribute11_tbl1(k) := Attribute11_tbl(i);
1433 Attribute12_tbl1(k) := Attribute12_tbl(i);
1434 Attribute13_tbl1(k) := Attribute13_tbl(i);
1435 Attribute14_tbl1(k) := Attribute14_tbl(i);
1436 Attribute15_tbl1(k) := Attribute15_tbl(i);
1437 Source_flag_tbl1(k) := Source_flag_tbl(i);
1438 Article_Version_tbl1(k) := Article_Version_tbl(i);
1439 Change_nonstd_yn_tbl1(k) := Change_nonstd_yn_tbl(i);
1440 Scn_id_tbl1(k) := Scn_id_tbl(i);
1441 Orig_System_Reference_id1_tbl1(k) := Orig_System_Reference_id1_tbl(i);
1442 Mandatory_yn_tbl1(k) := Mandatory_yn_tbl(i);
1443 Mandatory_rwa_tbl1(k) := Mandatory_rwa_tbl(i);
1444 Label_tbl1(k) := Label_tbl(i);
1445 Display_sequence_tbl1(k) := Display_sequence_tbl(i);
1446 ref_article_id_tbl1(k) := ref_article_id_tbl(i);
1447 ref_article_version_id_tbl1(k) := ref_article_version_id_tbl(i);
1448 orig_article_id_tbl1(k) := orig_article_id_tbl(i);
1449
1450 /*kkolukul: CLM Changes: getting max sequence of articles in the Doc and
1451 adding new articles after that*/
1452 OPEN l_art_sqn_csr;
1453 FETCH l_art_sqn_csr INTO l_max_sequence;
1454 CLOSE l_art_sqn_csr;
1455
1456 IF(p_retain_clauses = 'Y') THEN
1457 Display_sequence_tbl1(k) := l_max_sequence + 10 ;
1458 ELSE
1459 Display_sequence_tbl1(k) := Display_sequence_tbl(i);
1460 END IF;
1461 END IF;
1462 END LOOP;
1463 END IF; -- count > 0
1464
1465 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1466 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Count of Articles on Target : '||sav_sae_tbl1.COUNT);
1467 END IF;
1468
1469 IF sav_sae_tbl1.COUNT>0 THEN
1470 FORALL i IN sav_sae_tbl1.FIRST..sav_sae_tbl1.LAST
1471 INSERT INTO OKC_K_ARTICLES_B(
1472 ID,
1473 SAV_SAE_ID,
1474 DOCUMENT_TYPE,
1475 DOCUMENT_ID,
1476 CHR_ID,
1477 DNZ_CHR_ID,
1478 SOURCE_FLAG,
1479 MANDATORY_YN,
1480 MANDATORY_RWA,
1481 SCN_ID,
1482 LABEL,
1483 AMENDMENT_DESCRIPTION,
1484 AMENDMENT_OPERATION_CODE,
1485 ARTICLE_VERSION_ID,
1486 CHANGE_NONSTD_YN,
1487 ORIG_SYSTEM_REFERENCE_CODE,
1488 ORIG_SYSTEM_REFERENCE_ID1,
1489 ORIG_SYSTEM_REFERENCE_ID2,
1490 DISPLAY_SEQUENCE,
1491 ATTRIBUTE_CATEGORY,
1492 ATTRIBUTE1,
1493 ATTRIBUTE2,
1494 ATTRIBUTE3,
1495 ATTRIBUTE4,
1496 ATTRIBUTE5,
1497 ATTRIBUTE6,
1498 ATTRIBUTE7,
1499 ATTRIBUTE8,
1500 ATTRIBUTE9,
1501 ATTRIBUTE10,
1502 ATTRIBUTE11,
1503 ATTRIBUTE12,
1504 ATTRIBUTE13,
1505 ATTRIBUTE14,
1506 ATTRIBUTE15,
1507 PRINT_TEXT_YN,
1508 REF_ARTICLE_ID,
1509 REF_ARTICLE_VERSION_ID,
1510 OBJECT_VERSION_NUMBER,
1511 CREATED_BY,
1512 CREATION_DATE,
1513 LAST_UPDATED_BY,
1514 LAST_UPDATE_LOGIN,
1515 LAST_UPDATE_DATE,
1516 ORIG_ARTICLE_ID)
1517 VALUES(OKC_K_ARTICLES_B_S.nextval,
1518 sav_sae_tbl1(i),
1519 p_target_doc_type,
1520 p_target_doc_id,
1521 decode(p_target_doc_type,'OKC_BUY',p_target_doc_id,'OKC_SELL',p_target_doc_id, 'OKO',p_target_doc_id,'OKS',p_target_doc_id,'OKE_BUY',p_target_doc_id, 'OKE_SELL',p_target_doc_id, 'OKL',p_target_doc_id,NULL),
1522 decode(p_target_doc_type,'OKC_BUY',p_target_doc_id,'OKC_SELL',p_target_doc_id, 'OKO',p_target_doc_id,'OKS',p_target_doc_id,'OKE_BUY',p_target_doc_id, 'OKE_SELL',p_target_doc_id, 'OKL',p_target_doc_id,NULL),
1523 Source_flag_tbl1(i),
1524 Mandatory_yn_tbl1(i),
1525 Mandatory_rwa_tbl1(i),
1526 Scn_id_tbl1(i),
1527 Label_tbl1(i),
1528 Null,
1529 Null,
1530 decode(p_target_doc_type, OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE,NULL,Article_Version_tbl1(i)),
1531 Change_nonstd_yn_tbl1(i),
1532 G_COPY,
1533 Orig_System_Reference_id1_tbl1(i),
1534 Null,
1535 Display_sequence_tbl1(i),
1536 Attribute_category_tbl1(i),
1537 Attribute1_tbl1(i),
1538 Attribute2_tbl1(i),
1539 Attribute3_tbl1(i),
1540 Attribute4_tbl1(i),
1541 Attribute5_tbl1(i),
1542 Attribute6_tbl1(i),
1543 Attribute7_tbl1(i),
1544 Attribute8_tbl1(i),
1545 Attribute9_tbl1(i),
1546 Attribute10_tbl1(i),
1547 Attribute11_tbl1(i),
1548 Attribute12_tbl1(i),
1549 Attribute13_tbl1(i),
1550 Attribute14_tbl1(i),
1551 Attribute15_tbl1(i),
1552 Null,
1553 ref_article_id_tbl(i),
1554 ref_article_version_id_tbl(i),
1555 1,
1556 Fnd_Global.User_Id,
1557 sysdate,
1558 Fnd_Global.User_Id,
1559 Fnd_Global.Login_Id,
1560 sysdate,
1561 orig_article_id_tbl1(i));
1562
1563 END IF;
1564
1565
1566 -------------------- CONC MOD CHANGES START -------------
1567
1568
1569 IF p_retain_lock_terms_yn = 'Y'
1570 THEN
1571 -- Add Case
1572 -- Get the orphan records.
1573
1574 -- Using the section id from the clause, find the base section id from the okc_k_entity_locks
1575 -- using base section find the corresponding section in the current mod
1576
1577 -- CASE ARTICLE IS ADDED IN THE TARGE DOC
1578 OPEN cur_orphan_clauses;
1579 FETCH cur_orphan_clauses bulk COLLECT INTO tgt_cat_tbl,tgt_scn_tbl,tgt_scn_upd_lock_tbl;
1580 CLOSE cur_orphan_clauses;
1581
1582 IF tgt_cat_tbl.Count > 0 THEN
1583 FORALL i IN tgt_cat_tbl.first..tgt_cat_tbl.last
1584 UPDATE okc_k_articles_b
1585 SET scn_id= tgt_scn_tbl(i)
1586 WHERE id= tgt_cat_tbl(i);
1587
1588 -- Re-build the lock table.
1589 FORALL i IN tgt_scn_upd_lock_tbl.first..tgt_scn_upd_lock_tbl.last
1590 UPDATE okc_k_entity_locks
1591 SET lock_by_entity_id = tgt_scn_tbl(i)
1592 WHERE k_entity_lock_id=tgt_scn_upd_lock_tbl(i);
1593 END IF;
1594
1595 -- CASE ARTICLE IS UPDATED/DELETED CASE ARTICLE IS ADDED IN THE TARGE DOC
1596 OPEN cur_orphan_upd_clauses;
1597 FETCH cur_orphan_upd_clauses bulk COLLECT INTO tgt_cat_upd_tbl,tgt_scn_upd_tbl;
1598 CLOSE cur_orphan_upd_clauses;
1599
1600 IF tgt_cat_upd_tbl.Count > 0 THEN
1601 FORALL i IN tgt_cat_upd_tbl.first..tgt_cat_upd_tbl.last
1602 UPDATE okc_k_articles_b
1603 SET scn_id= tgt_scn_upd_tbl(i)
1604 WHERE id= tgt_cat_upd_tbl(i);
1605 -- CASE SECTION IS MODIFED/UPDATED.
1606 -- MODIFY COPY SECTIONS
1607
1608 END IF;
1609 END IF;
1610 -------------------- CONC MOD CHANGES END -----------------
1611
1612
1613 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1614
1615 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1616 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving copy_articles '||x_return_status);
1617 END IF;
1618
1619 EXCEPTION
1620
1621 WHEN FND_API.G_EXC_ERROR THEN
1622
1623 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1624 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_articles:FND_API.G_EXC_ERROR Exception');
1625 END IF;
1626 IF l_get_prov_csr%ISOPEN THEN
1627 CLOSE l_get_prov_csr;
1628 END IF;
1629
1630 IF l_get_article_csr%ISOPEN THEN
1631 CLOSE l_get_article_csr;
1632 END IF;
1633 IF l_get_local_article_csr%ISOPEN THEN
1634 CLOSE l_get_local_article_csr;
1635 END IF;
1636 IF l_get_art_csr %ISOPEN THEN
1637 CLOSE l_get_art_csr ;
1638 END IF;
1639 IF l_get_latest_article_csr%ISOPEN THEN
1640 CLOSE l_get_latest_article_csr;
1641 END IF;
1642 IF l_get_org_csr%ISOPEN THEN
1643 CLOSE l_get_org_csr;
1644 END IF;
1645 IF l_get_global_csr%ISOPEN THEN
1646 CLOSE l_get_global_csr;
1647 END IF;
1648 IF l_get_prov_csr%ISOPEN THEN
1649 CLOSE l_get_prov_csr;
1650 END IF;
1651 IF l_get_std_csr%ISOPEN THEN
1652 CLOSE l_get_std_csr;
1653 END IF;
1654
1655 x_return_status := G_RET_STS_ERROR;
1656 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1657
1658 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1659
1660 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1661 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_articles:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1662 END IF;
1663 IF l_get_article_csr%ISOPEN THEN
1664 CLOSE l_get_article_csr;
1665 END IF;
1666 IF l_get_local_article_csr%ISOPEN THEN
1667 CLOSE l_get_local_article_csr;
1668 END IF;
1669 IF l_get_art_csr %ISOPEN THEN
1670 CLOSE l_get_art_csr ;
1671 END IF;
1672 IF l_get_latest_article_csr%ISOPEN THEN
1673 CLOSE l_get_latest_article_csr;
1674 END IF;
1675 IF l_get_org_csr%ISOPEN THEN
1676 CLOSE l_get_org_csr;
1677 END IF;
1678
1679 x_return_status := G_RET_STS_UNEXP_ERROR;
1680 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1681
1682 WHEN OTHERS THEN
1683
1684 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1685 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_articles because of EXCEPTION: '||sqlerrm);
1686 END IF;
1687
1688 IF l_get_article_csr%ISOPEN THEN
1689 CLOSE l_get_article_csr;
1690 END IF;
1691
1692
1693 IF l_get_local_article_csr%ISOPEN THEN
1694 CLOSE l_get_local_article_csr;
1695 END IF;
1696
1697 IF l_get_art_csr %ISOPEN THEN
1698 CLOSE l_get_art_csr ;
1699 END IF;
1700
1701 IF l_get_latest_article_csr%ISOPEN THEN
1702 CLOSE l_get_latest_article_csr;
1703 END IF;
1704
1705 IF l_get_org_csr%ISOPEN THEN
1706 CLOSE l_get_org_csr;
1707 END IF;
1708
1709 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1710 p_msg_name => G_UNEXPECTED_ERROR,
1711 p_token1 => G_SQLCODE_TOKEN,
1712 p_token1_value => sqlcode,
1713 p_token2 => G_SQLERRM_TOKEN,
1714 p_token2_value => sqlerrm);
1715
1716 x_return_status := G_RET_STS_UNEXP_ERROR;
1717 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1718
1719 END copy_articles;
1720
1721 procedure copy_sections(
1722 p_target_doc_type IN VARCHAR2,
1723 p_source_doc_type IN VARCHAR2,
1724 p_target_doc_id IN NUMBER,
1725 p_source_doc_id IN NUMBER,
1726 p_source_version_number IN NUMBER := NULL,
1727 p_copy_from_archive IN VARCHAR2 := 'N',
1728 p_keep_orig_ref IN VARCHAR2 := 'N',
1729 x_return_status OUT NOCOPY VARCHAR2,
1730 x_msg_data OUT NOCOPY VARCHAR2,
1731 x_msg_count OUT NOCOPY NUMBER,
1732 p_retain_clauses IN VARCHAR2 DEFAULT 'N'
1733 ,p_retain_lock_terms_yn IN VARCHAR2 DEFAULT 'N') IS
1734
1735 l_api_name CONSTANT VARCHAR2(30) := 'copy_sections';
1736 TYPE AttributeCategoryList IS TABLE OF OKC_SECTIONS_B.ATTRIBUTE_CATEGORY%TYPE INDEX BY BINARY_INTEGER;
1737 TYPE AttributeList IS TABLE OF OKC_SECTIONS_B.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
1738 TYPE ScnIdList IS TABLE OF OKC_SECTIONS_B.SCN_ID%TYPE INDEX BY BINARY_INTEGER;
1739 TYPE OrigSystemReferenceId1List IS TABLE OF OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE INDEX BY BINARY_INTEGER;
1740 TYPE SectionSequenceList IS TABLE OF OKC_SECTIONS_B.SECTION_SEQUENCE%TYPE INDEX BY BINARY_INTEGER;
1741 TYPE HeadingList IS TABLE OF OKC_SECTIONS_B.HEADING%TYPE INDEX BY BINARY_INTEGER;
1742 TYPE LabelList IS TABLE OF OKC_SECTIONS_B.LABEL%TYPE INDEX BY BINARY_INTEGER;
1743 TYPE ScnCodeList IS TABLE OF OKC_SECTIONS_B.SCN_CODE%TYPE INDEX BY BINARY_INTEGER;
1744 TYPE PrintYnList IS TABLE OF OKC_SECTIONS_B.PRINT_YN%TYPE INDEX BY BINARY_INTEGER;
1745 TYPE IdList IS TABLE OF OKC_SECTIONS_B.ID%TYPE INDEX BY BINARY_INTEGER;
1746 TYPE DescriptionList IS TABLE OF OKC_SECTIONS_B.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
1747
1748
1749 Id_tbl IdList;
1750 Attribute_category_tbl AttributeCategoryList;
1751 Attribute1_tbl AttributeList;
1752 Attribute2_tbl AttributeList;
1753 Attribute3_tbl AttributeList;
1754 Attribute4_tbl AttributeList;
1755 Attribute5_tbl AttributeList;
1756 Attribute6_tbl AttributeList;
1757 Attribute7_tbl AttributeList;
1758 Attribute8_tbl AttributeList;
1759 Attribute9_tbl AttributeList;
1760 Attribute10_tbl AttributeList;
1761 Attribute11_tbl AttributeList;
1762 Attribute12_tbl AttributeList;
1763 Attribute13_tbl AttributeList;
1764 Attribute14_tbl AttributeList;
1765 Attribute15_tbl AttributeList;
1766 Scn_id_tbl ScnIdList;
1767 Orig_System_Reference_id1_tbl OrigSystemReferenceId1List;
1768 Section_sequence_tbl SectionSequenceList;
1769 Heading_tbl HeadingList;
1770 Scn_code_tbl ScnCodeList;
1771 Label_tbl LabelList;
1772 Print_yn_tbl PrintYnList;
1773
1774 Orig_System_Reference_id1_tbl1 OrigSystemReferenceId1List;
1775 Id_tbl1 IdList;
1776
1777 Description_tbl DescriptionList;
1778
1779 TYPE prov_section IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
1780 prov_sec_tbl prov_section;
1781 l_hook_used NUMBER:=0;
1782 l_max_sequence NUMBER := 0;
1783 l_copy_section_yn VARCHAR2(1);
1784 l_scn_drp_action VARCHAR2(100);
1785
1786 CURSOR l_get_scn_csr IS
1787 SELECT OKC_SECTIONS_B_S.NEXTVAL,
1788 ATTRIBUTE_CATEGORY,
1789 ATTRIBUTE1,
1790 ATTRIBUTE2,
1791 ATTRIBUTE3,
1792 ATTRIBUTE4,
1793 ATTRIBUTE5,
1794 ATTRIBUTE6,
1795 ATTRIBUTE7,
1796 ATTRIBUTE8,
1797 ATTRIBUTE9,
1798 ATTRIBUTE10,
1799 ATTRIBUTE11,
1800 ATTRIBUTE12,
1801 ATTRIBUTE13,
1802 ATTRIBUTE14,
1803 ATTRIBUTE15,
1804 SCN_ID SCN_ID,
1805 DECODE(P_KEEP_ORIG_REF,'Y',ORIG_SYSTEM_REFERENCE_ID1,ID) ORIG_SYSTEM_REFERENCE_ID1,
1806 SECTION_SEQUENCE,
1807 LABEL,
1808 SCN_CODE,
1809 HEADING,
1810 PRINT_YN ,
1811 DESCRIPTION
1812 FROM OKC_SECTIONS_B
1813 WHERE DOCUMENT_TYPE=P_SOURCE_DOC_TYPE
1814 AND DOCUMENT_ID=P_SOURCE_DOC_ID
1815 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
1816 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
1817 AND (( l_scn_drp_action = 'DROP_AMEND_SEC' AND
1818 heading <> Nvl(fnd_profile.Value('OKC_AMENDMENT_SPECIFIC_SECTION'),'~!@#$%')
1819 ) OR
1820 (
1821 l_scn_drp_action = 'DROP_PROV_SEC' AND
1822 OKC_CODE_HOOK.IS_NOT_PROVISIONAL_SECTION(heading) = FND_API.G_TRUE
1823 ) OR
1824 l_scn_drp_action = 'DROP_NOTHING')
1825 AND ( p_retain_lock_terms_yn = 'N'
1826 OR
1827 ( p_retain_lock_terms_yn = 'Y'
1828 AND NOT EXISTS ( SELECT 'LOCKEXISTS'
1829 FROM okc_k_entity_locks
1830 WHERE entity_name='SECTION'
1831 AND entity_pk1 = To_Char(id)
1832 AND lock_by_document_type=p_target_doc_type
1833 AND lock_by_document_id=p_target_doc_id
1834 )
1835 )
1836 /*OR
1837 ( p_retain_lock_terms_yn = 'Y'
1838 AND NOT EXISTS ( SELECT 'LOCKEXISTS'
1839 FROM okc_k_entity_locks lck,okc_sections_b sec
1840 WHERE entity_name='DUMMYSEC'
1841 AND entity_pk1 = To_Char(id)
1842 AND lock_by_document_type=p_target_doc_type
1843 AND lock_by_document_id=p_target_doc_id
1844 AND sec.document_type = p_target_doc_type
1845 AND sec.document_id = p_target_doc_id
1846
1847 )
1848 )*/
1849 );
1850
1851
1852 CURSOR l_get_scn_from_archive_csr IS
1853 SELECT OKC_SECTIONS_B_S.NEXTVAL,
1854 ATTRIBUTE_CATEGORY,
1855 ATTRIBUTE1,
1856 ATTRIBUTE2,
1857 ATTRIBUTE3,
1858 ATTRIBUTE4,
1859 ATTRIBUTE5,
1860 ATTRIBUTE6,
1861 ATTRIBUTE7,
1862 ATTRIBUTE8,
1863 ATTRIBUTE9,
1864 ATTRIBUTE10,
1865 ATTRIBUTE11,
1866 ATTRIBUTE12,
1867 ATTRIBUTE13,
1868 ATTRIBUTE14,
1869 ATTRIBUTE15,
1870 SCN_ID SCN_ID,
1871 DECODE(P_KEEP_ORIG_REF, 'Y', ORIG_SYSTEM_REFERENCE_ID1,ID) ORIG_SYSTEM_REFERENCE_ID1,
1872 SECTION_SEQUENCE,
1873 LABEL,
1874 SCN_CODE,
1875 HEADING,
1876 PRINT_YN ,
1877 DESCRIPTION
1878 FROM OKC_SECTIONS_BH
1879 WHERE DOCUMENT_TYPE=P_SOURCE_DOC_TYPE
1880 AND DOCUMENT_ID=P_SOURCE_DOC_ID
1881 AND MAJOR_VERSION = nvl(p_source_version_number,OKC_API.G_MISS_NUM)
1882 AND (( l_scn_drp_action = 'DROP_AMEND_SEC' AND
1883 heading <> Nvl(fnd_profile.Value('OKC_AMENDMENT_SPECIFIC_SECTION'),'~!@#$%')
1884 ) OR
1885 (
1886 l_scn_drp_action = 'DROP_PROV_SEC' AND
1887 OKC_CODE_HOOK.IS_NOT_PROVISIONAL_SECTION(heading) = FND_API.G_TRUE
1888 ) OR
1889 l_scn_drp_action = 'DROP_NOTHING');
1890
1891 --kkolukul: CLM changes
1892 CURSOR l_scn_sqn_csr IS
1893 SELECT Max(section_sequence) FROM okc_sections_b
1894 WHERE DOCUMENT_TYPE=P_TARGET_DOC_TYPE
1895 AND DOCUMENT_ID=P_TARGET_DOC_ID;
1896
1897 CURSOR l_get_scn_exists_in_doc(p_scn_code varchar2) IS
1898 SELECT 'Y'
1899 FROM okc_sections_b
1900 WHERE document_type = p_target_doc_type
1901 AND document_id = p_target_doc_id
1902 AND scn_code = p_scn_code;
1903
1904 -------------------- CONC MOD CHANGES -------------
1905 CURSOR cur_orphan_upd_sections
1906 IS
1907 SELECT tgtsec.id,tgtsec2.id
1908 FROM okc_sections_b tgtsec
1909 ,okc_sections_b srcsec
1910 ,okc_sections_b tgtsec2
1911 WHERE 1 = 1
1912 AND tgtsec.document_type= p_target_doc_type
1913 AND tgtsec.document_id= p_target_doc_id
1914 AND tgtsec.summary_amend_operation_code IN ('UPDATED','DELTED')
1915 AND tgtsec.scn_id IS NOT NULL
1916
1917 AND tgtsec.orig_system_reference_id1 = srcsec.id
1918 AND srcsec.document_type= p_source_doc_type
1919 AND srcsec.document_id= p_source_doc_id
1920 AND tgtsec2.document_type=p_target_doc_type
1921 AND tgtsec2.document_id=p_target_doc_id
1922 AND tgtsec2.ORIG_SYSTEM_REFERENCE_ID1=srcsec.scn_id;
1923 -------------------- CONC MOD CHANGES -------------
1924
1925
1926 TYPE ScnList IS TABLE OF okc_sections_b.ID%TYPE INDEX BY BINARY_INTEGER;
1927
1928
1929
1930 tgt_id_upd_tbl ScnList;
1931 tgt_scn_id_upd_tbl ScnList;
1932
1933 BEGIN
1934
1935 x_return_status := G_RET_STS_SUCCESS;
1936
1937 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1938 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.copy_sections');
1939 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside p_retain_lock_terms_yn : ' || p_retain_lock_terms_yn);
1940 END IF;
1941
1942
1943 l_scn_drp_action := clm_scn_filtering(p_source_doc_id,p_source_doc_type,p_target_doc_id,p_target_doc_type);
1944
1945 IF p_copy_from_archive ='Y' THEN
1946
1947 OPEN l_get_scn_from_archive_csr;
1948 FETCH l_get_scn_from_archive_csr BULK COLLECT INTO
1949 id_tbl,
1950 Attribute_category_tbl,
1951 Attribute1_tbl,
1952 Attribute2_tbl,
1953 Attribute3_tbl,
1954 Attribute4_tbl,
1955 Attribute5_tbl,
1956 Attribute6_tbl,
1957 Attribute7_tbl,
1958 Attribute8_tbl,
1959 Attribute9_tbl,
1960 Attribute10_tbl,
1961 Attribute11_tbl,
1962 Attribute12_tbl,
1963 Attribute13_tbl,
1964 Attribute14_tbl,
1965 Attribute15_tbl,
1966 Scn_id_tbl,
1967 Orig_System_Reference_id1_tbl,
1968 Section_sequence_tbl,
1969 Label_tbl,
1970 Scn_code_tbl,
1971 Heading_tbl,
1972 Print_yn_tbl ,
1973 Description_tbl ;
1974 CLOSE l_get_scn_from_archive_csr;
1975 ELSE
1976 OPEN l_get_scn_csr;
1977 FETCH l_get_scn_csr BULK COLLECT INTO
1978 id_tbl,
1979 Attribute_category_tbl,
1980 Attribute1_tbl,
1981 Attribute2_tbl,
1982 Attribute3_tbl,
1983 Attribute4_tbl,
1984 Attribute5_tbl,
1985 Attribute6_tbl,
1986 Attribute7_tbl,
1987 Attribute8_tbl,
1988 Attribute9_tbl,
1989 Attribute10_tbl,
1990 Attribute11_tbl,
1991 Attribute12_tbl,
1992 Attribute13_tbl,
1993 Attribute14_tbl,
1994 Attribute15_tbl,
1995 Scn_id_tbl,
1996 Orig_System_Reference_id1_tbl,
1997 Section_sequence_tbl,
1998 Label_tbl,
1999 Scn_code_tbl,
2000 Heading_tbl,
2001 Print_yn_tbl,
2002 Description_tbl ;
2003 CLOSE l_get_scn_csr;
2004 END IF;
2005
2006 id_tbl1 := id_tbl;
2007 Orig_System_Reference_id1_tbl1 := Orig_System_Reference_id1_tbl;
2008
2009 -- Following routine will link subsections to its parent section
2010
2011 IF id_tbl.COUNT>0 THEN
2012
2013 FOR i IN id_tbl.FIRST..id_tbl.LAST LOOP
2014
2015
2016 IF scn_id_tbl(i) IS NOT NULL THEN
2017 FOR k IN id_tbl1.FIRST..id_tbl1.LAST LOOP
2018 IF Orig_System_Reference_id1_tbl1(k)= scn_id_tbl(i) THEN
2019 scn_id_tbl(i) := id_tbl1(k);
2020 END IF;
2021 END LOOP;
2022 END IF;
2023 END LOOP;
2024
2025 END IF;
2026
2027 /*kkolukul: clm changes: section sequence is getting copied directly from template.
2028 Since we are adding multiple templates, there are being 2 sections with same sequence.*/
2029 IF (p_retain_clauses = 'Y') THEN
2030 OPEN l_scn_sqn_csr;
2031 FETCH l_scn_sqn_csr INTO l_max_sequence;
2032 CLOSE l_scn_sqn_csr;
2033
2034 IF id_tbl.COUNT > 0 THEN
2035 FOR i IN id_tbl.FIRST..id_tbl.LAST LOOP
2036 l_max_sequence := l_max_sequence + 10;
2037 Section_sequence_tbl(i) := l_max_sequence;
2038
2039 OPEN l_get_scn_exists_in_doc(scn_code_tbl(i));
2040 FETCH l_get_scn_exists_in_doc INTO l_copy_section_yn;
2041 CLOSE l_get_scn_exists_in_doc;
2042
2043 IF Nvl(l_copy_section_yn, 'N') = 'N' THEN
2044 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2045 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside Ol_copy_section_yn: ' || l_copy_section_yn);
2046 END IF;
2047
2048 END IF;
2049 END LOOP;
2050 END IF;
2051 END IF;
2052 --end - kkolukul: clm changes
2053
2054
2055
2056 IF id_tbl.COUNT > 0 THEN
2057
2058 FORALL i IN id_tbl.FIRST..id_tbl.LAST
2059
2060 INSERT INTO OKC_SECTIONS_B(
2061 ID,
2062 DOCUMENT_TYPE,
2063 DOCUMENT_ID,
2064 CHR_ID,
2065 SCN_ID,
2066 LABEL,
2067 AMENDMENT_DESCRIPTION,
2068 AMENDMENT_OPERATION_CODE,
2069 ORIG_SYSTEM_REFERENCE_CODE,
2070 ORIG_SYSTEM_REFERENCE_ID1,
2071 ORIG_SYSTEM_REFERENCE_ID2,
2072 SECTION_SEQUENCE,
2073 ATTRIBUTE_CATEGORY,
2074 ATTRIBUTE1,
2075 ATTRIBUTE2,
2076 ATTRIBUTE3,
2077 ATTRIBUTE4,
2078 ATTRIBUTE5,
2079 ATTRIBUTE6,
2080 ATTRIBUTE7,
2081 ATTRIBUTE8,
2082 ATTRIBUTE9,
2083 ATTRIBUTE10,
2084 ATTRIBUTE11,
2085 ATTRIBUTE12,
2086 ATTRIBUTE13,
2087 ATTRIBUTE14,
2088 ATTRIBUTE15,
2089 PRINT_YN,
2090 HEADING,
2091 SCN_CODE,
2092 DESCRIPTION,
2093 OBJECT_VERSION_NUMBER,
2094 CREATED_BY,
2095 CREATION_DATE,
2096 LAST_UPDATED_BY,
2097 LAST_UPDATE_LOGIN,
2098 LAST_UPDATE_DATE)
2099 VALUES(id_tbl(i),
2100 p_target_doc_type,
2101 p_target_doc_id,
2102 decode(p_target_doc_type,'OKC_BUY',p_target_doc_id, 'OKC_SELL',p_target_doc_id, 'OKO',p_target_doc_id,'OKS',p_target_doc_id,'OKE_BUY',p_target_doc_id, 'OKE_SELL',p_target_doc_id, 'OKL',p_target_doc_id,NULL),
2103 Scn_id_tbl(i),
2104 Label_tbl(i),
2105 Null,
2106 Null,
2107 G_COPY,
2108 Orig_System_Reference_id1_tbl(i),
2109 Null,
2110 section_sequence_tbl(i),
2111 Attribute_category_tbl(i),
2112 Attribute1_tbl(i),
2113 Attribute2_tbl(i),
2114 Attribute3_tbl(i),
2115 Attribute4_tbl(i),
2116 Attribute5_tbl(i),
2117 Attribute6_tbl(i),
2118 Attribute7_tbl(i),
2119 Attribute8_tbl(i),
2120 Attribute9_tbl(i),
2121 Attribute10_tbl(i),
2122 Attribute11_tbl(i),
2123 Attribute12_tbl(i),
2124 Attribute13_tbl(i),
2125 Attribute14_tbl(i),
2126 Attribute15_tbl(i),
2127 print_yn_tbl(i),
2128 heading_tbl(i),
2129 scn_code_tbl(i),
2130 description_tbl(i),
2131 1,
2132 Fnd_Global.User_Id,
2133 sysdate,
2134 Fnd_Global.User_Id,
2135 Fnd_Global.Login_Id,
2136 sysdate);
2137
2138 END IF;
2139
2140 -------------------- CONC MOD CHANGES -------------
2141
2142 IF p_retain_lock_terms_yn = 'Y'
2143 THEN
2144
2145
2146 -- CASE a SUB-SECTION IS UPDATED/DELETED
2147 OPEN cur_orphan_upd_sections;
2148 FETCH cur_orphan_upd_sections bulk COLLECT INTO tgt_id_upd_tbl ,tgt_scn_id_upd_tbl ;
2149 CLOSE cur_orphan_upd_sections;
2150
2151 IF tgt_id_upd_tbl.Count > 0 THEN
2152 FORALL i IN tgt_id_upd_tbl.first..tgt_id_upd_tbl.last
2153 UPDATE okc_k_articles_b
2154 SET scn_id= tgt_scn_id_upd_tbl (i)
2155 WHERE id= tgt_id_upd_tbl (i);
2156 END IF;
2157 END IF;
2158 -------------------- CONC MOD CHANGES -------------
2159
2160 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2161
2162 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2163 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving copy_sections '||x_return_status);
2164 END IF;
2165
2166 EXCEPTION
2167
2168 WHEN FND_API.G_EXC_ERROR THEN
2169
2170 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_sections:FND_API.G_EXC_ERROR Exception');
2172 END IF;
2173
2174 IF l_get_scn_csr %ISOPEN THEN
2175 CLOSE l_get_scn_csr ;
2176 END IF;
2177
2178 x_return_status := G_RET_STS_ERROR;
2179 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2180
2181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2182
2183 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2184 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_sections:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2185 END IF;
2186
2187 IF l_get_scn_csr %ISOPEN THEN
2188 CLOSE l_get_scn_csr ;
2189 END IF;
2190
2191 x_return_status := G_RET_STS_UNEXP_ERROR;
2192 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2193
2194 WHEN OTHERS THEN
2195
2196 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2197 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_sections because of EXCEPTION: '||sqlerrm);
2198 END IF;
2199
2200 IF l_get_scn_csr %ISOPEN THEN
2201 CLOSE l_get_scn_csr ;
2202 END IF;
2203
2204 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2205 p_msg_name => G_UNEXPECTED_ERROR,
2206 p_token1 => G_SQLCODE_TOKEN,
2207 p_token1_value => sqlcode,
2208 p_token2 => G_SQLERRM_TOKEN,
2209 p_token2_value => sqlerrm);
2210
2211 x_return_status := G_RET_STS_UNEXP_ERROR;
2212 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2213
2214 END copy_sections;
2215
2216
2217 -- This API will be called wheneve a template is re-applied on a document.
2218 -- It will put all the manually added article into UN-Assigned section and remove other articles and section;
2219
2220 procedure remove_template_based_articles(
2221 p_doc_type IN VARCHAR2,
2222 p_doc_id IN NUMBER,
2223 p_retain_deliverable IN VARCHAR2,
2224 x_return_status OUT NOCOPY VARCHAR2,
2225 x_msg_data OUT NOCOPY VARCHAR2,
2226 x_msg_count OUT NOCOPY NUMBER) IS
2227
2228 l_api_name CONSTANT VARCHAR2(30) := 'remove_template_based_articles';
2229 CURSOR l_get_manual_art_csr IS
2230 SELECT count(*)
2231 FROM OKC_K_ARTICLES_B
2232 WHERE DOCUMENT_TYPE = p_doc_type
2233 AND DOCUMENT_ID = p_doc_id
2234 AND SOURCE_FLAG IS NULL;
2235
2236 CURSOR l_check_unassigned_section_csr IS
2237 SELECT id
2238 FROM OKC_SECTIONS_B
2239 WHERE DOCUMENT_TYPE = p_doc_type
2240 AND DOCUMENT_ID = p_doc_id
2241 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
2242 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
2243 AND SCN_CODE = 'UNASSIGNED';
2244
2245 CURSOR lock_kart_for_upd_csr IS
2246 SELECT ROWID FROM OKC_K_ARTICLES_B
2247 WHERE DOCUMENT_TYPE = p_doc_type
2248 AND DOCUMENT_ID = p_doc_id
2249 AND SOURCE_FLAG IS NULL FOR UPDATE NOWAIT;
2250
2251 CURSOR lock_var_for_del_csr IS
2252 SELECT ROWID FROM OKC_K_ART_VARIABLES
2253 WHERE CAT_ID IN
2254 (SELECT ID FROM OKC_K_ARTICLES_B
2255 WHERE DOCUMENT_TYPE=p_doc_type
2256 AND DOCUMENT_ID=p_doc_id
2257 AND SOURCE_FLAG IS NOT NULL) FOR UPDATE NOWAIT;
2258
2259 CURSOR lock_kart_for_del_csr IS
2260 SELECT ROWID FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
2261 AND DOCUMENT_ID=p_doc_id
2262 AND SOURCE_FLAG IS NOT NULL FOR UPDATE NOWAIT;
2263
2264 CURSOR lock_scn_for_del_csr(b_scn_id NUMBER) IS
2265 SELECT ROWID FROM OKC_SECTIONS_B
2266 WHERE DOCUMENT_TYPE=p_doc_type
2267 AND DOCUMENT_ID=p_doc_id
2268 AND id <> b_scn_id FOR UPDATE NOWAIT;
2269
2270 --kkolukul: clm Changes
2271 CURSOR objnum_mlp_tu_csr IS
2272 SELECT object_version_number
2273 FROM OKC_MLP_TEMPLATE_USAGES
2274 WHERE DOCUMENT_TYPE = p_doc_type AND DOCUMENT_ID = p_doc_id;
2275
2276 l_manual_art_count NUMBER;
2277 l_unassigned_scn_id OKC_SECTIONS_B.ID%TYPE := 0;
2278 l_objnum NUMBER;
2279 l_found BOOLEAN;
2280
2281
2282 BEGIN
2283
2284 x_return_status := G_RET_STS_SUCCESS;
2285
2286 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2287 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.remove_template_based_articles');
2288 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Parameter List ');
2289 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_doc_type : '||p_doc_type);
2290 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_doc_id : '||p_doc_id);
2291 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_retain_deliverable : '||p_retain_deliverable);
2292 END IF;
2293
2294 OPEN l_get_manual_art_csr;
2295 FETCH l_get_manual_art_csr INTO l_manual_art_count;
2296 CLOSE l_get_manual_art_csr;
2297
2298 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2299 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'110: l_manual_art_count : '||l_manual_art_count);
2300 END IF;
2301
2302 IF l_manual_art_count > 0 THEN
2303
2304 OPEN l_check_unassigned_section_csr ;
2305 FETCH l_check_unassigned_section_csr INTO l_unassigned_scn_id;
2306 CLOSE l_check_unassigned_section_csr;
2307
2308 IF l_unassigned_scn_id = 0 THEN
2309
2310 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2311 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'120: Creating Unassgined Section ');
2312 END IF;
2313
2314 OKC_TERMS_UTIL_PVT.create_unassigned_section(p_api_version => 1,
2315 p_commit => FND_API.G_FALSE,
2316 p_doc_type => p_doc_type,
2317 p_doc_id => p_doc_id,
2318 x_scn_id =>l_unassigned_scn_id,
2319 x_return_status => x_return_status,
2320 x_msg_count => x_msg_count,
2321 x_msg_data => x_msg_data
2322 );
2323 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2324 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: l_unassigned_scn_id : '||l_unassigned_scn_id);
2325 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: Cannot Create Unassgined Section : '||x_msg_data||' Status '||x_return_status);
2326 END IF;
2327
2328 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2330 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2331 RAISE FND_API.G_EXC_ERROR ;
2332 END IF;
2333
2334
2335 END IF;
2336
2337 OPEN lock_kart_for_upd_csr;
2338 CLOSE lock_kart_for_upd_csr;
2339
2340 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2341 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'140: Updating Manually added article sections to unassigned');
2342 END IF;
2343
2344 UPDATE OKC_K_ARTICLES_B
2345 SET SCN_ID = l_unassigned_scn_id,
2346 LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
2347 LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
2348 LAST_UPDATE_DATE = sysdate
2349 WHERE DOCUMENT_TYPE = p_doc_type
2350 AND DOCUMENT_ID = p_doc_id
2351 AND SOURCE_FLAG IS NULL;
2352
2353 END IF;
2354
2355 OPEN lock_var_for_del_csr;
2356 CLOSE lock_var_for_del_csr;
2357
2358 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2359 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'150: Deleting Variables ');
2360 END IF;
2361
2362 DELETE FROM OKC_K_ART_VAR_EXT_B WHERE cat_id IN ( SELECT kart.ID
2363 FROM OKC_K_ARTICLES_B KART
2364 , OKC_BUS_VARIABLES_B BUS_VAR
2365 , OKC_K_ART_VARIABLES KVAR
2366 WHERE kart.document_type=p_doc_type
2367 AND kart.document_id = p_doc_id
2368 AND kart.SOURCE_FLAG IS NOT NULL
2369 AND KVAR.cat_id=kart.id
2370 AND KVAR.variable_code=BUS_VAR.variable_code
2371 AND BUS_VAR.MRV_FLAG='Y');
2372
2373 DELETE FROM OKC_K_ART_VARIABLES WHERE CAT_ID IN
2374 (SELECT ID FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
2375 AND DOCUMENT_ID=p_doc_id
2376 AND SOURCE_FLAG IS NOT NULL);
2377 OPEN lock_kart_for_del_csr;
2378 CLOSE lock_kart_for_del_csr;
2379
2380 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'160: Deleting Articles ');
2382 END IF;
2383
2384 DELETE FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
2385 AND DOCUMENT_ID=p_doc_id
2386 AND SOURCE_FLAG IS NOT NULL;
2387
2388 OPEN lock_scn_for_del_csr(l_unassigned_scn_id);
2389 CLOSE lock_scn_for_del_csr;
2390
2391 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2392 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'170: Deleting Sections ');
2393 END IF;
2394
2395 DELETE FROM OKC_SECTIONS_B WHERE DOCUMENT_TYPE=p_doc_type
2396 AND DOCUMENT_ID=p_doc_id
2397 AND id <> l_unassigned_scn_id;
2398
2399 IF p_retain_deliverable ='N' THEN
2400
2401 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2402 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: Calling OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables ');
2403 END IF;
2404
2405 OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables (
2406 p_api_version => 1,
2407 p_init_msg_list => FND_API.G_FALSE,
2408 p_doc_id => p_doc_id,
2409 p_doc_type => p_doc_type,
2410 x_msg_data => x_msg_data,
2411 x_msg_count => x_msg_count,
2412 x_return_status => x_return_status);
2413
2414 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2415 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: After Call to OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables ');
2416 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: x_return_status : '||x_return_status);
2417 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: x_msg_count : '||x_msg_count);
2418 END IF;
2419
2420 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2422 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2423 RAISE FND_API.G_EXC_ERROR ;
2424 END IF;
2425
2426 /*kkolukul: clm changes- Delete entries from multiple templates table*/
2427
2428 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2429 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3900: Delete a record from okc_template_usages for the doc');
2430 END IF;
2431
2432 l_objnum := -1;
2433 OPEN objnum_mlp_tu_csr;
2434 FETCH objnum_mlp_tu_csr INTO l_objnum;
2435 l_found := objnum_mlp_tu_csr%FOUND;
2436 CLOSE objnum_mlp_tu_csr;
2437 IF l_found THEN
2438 OKC_CLM_PKG.Delete_Usages_Row(
2439 x_return_status => x_return_status,
2440 p_document_type => p_doc_type,
2441 p_document_id => p_doc_id,
2442 p_object_version_number => l_objnum
2443 );
2444 --------------------------------------------
2445 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2447 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2448 RAISE FND_API.G_EXC_ERROR ;
2449 END IF;
2450 --------------------------------------------
2451 END IF;
2452
2453 ---end clm changes.
2454
2455 END IF;
2456
2457 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2458
2459 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving remove_template_based_articles '||x_return_status);
2461 END IF;
2462
2463 EXCEPTION
2464 WHEN E_Resource_Busy THEN
2465 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2466 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'000: Leaving remove_template_based_articles:E_Resource_Busy Exception');
2467 END IF;
2468
2469 IF lock_kart_for_upd_csr%ISOPEN THEN
2470 CLOSE lock_kart_for_upd_csr;
2471 END IF;
2472
2473 IF lock_var_for_del_csr%ISOPEN THEN
2474 CLOSE lock_var_for_del_csr;
2475 END IF;
2476
2477 IF lock_kart_for_del_csr%ISOPEN THEN
2478 CLOSE lock_kart_for_del_csr;
2479 END IF;
2480
2481 IF lock_scn_for_del_csr%ISOPEN THEN
2482 CLOSE lock_scn_for_del_csr;
2483 END IF;
2484
2485 IF l_get_manual_art_csr%ISOPEN THEN
2486 CLOSE l_get_manual_art_csr ;
2487 END IF;
2488
2489 IF l_check_unassigned_section_csr%ISOPEN THEN
2490 CLOSE l_check_unassigned_section_csr ;
2491 END IF;
2492
2493 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2494 x_return_status := G_RET_STS_ERROR ;
2495 WHEN FND_API.G_EXC_ERROR THEN
2496
2497 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2498 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving remove_template_based_articles:FND_API.G_EXC_ERROR Exception');
2499 END IF;
2500
2501 IF lock_kart_for_upd_csr%ISOPEN THEN
2502 CLOSE lock_kart_for_upd_csr;
2503 END IF;
2504
2505 IF lock_var_for_del_csr%ISOPEN THEN
2506 CLOSE lock_var_for_del_csr;
2507 END IF;
2508
2509 IF lock_kart_for_del_csr%ISOPEN THEN
2510 CLOSE lock_kart_for_del_csr;
2511 END IF;
2512
2513 IF lock_scn_for_del_csr%ISOPEN THEN
2514 CLOSE lock_scn_for_del_csr;
2515 END IF;
2516
2517 IF l_get_manual_art_csr%ISOPEN THEN
2518 CLOSE l_get_manual_art_csr ;
2519 END IF;
2520
2521 IF l_check_unassigned_section_csr%ISOPEN THEN
2522 CLOSE l_check_unassigned_section_csr ;
2523 END IF;
2524
2525 x_return_status := G_RET_STS_ERROR;
2526 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2527
2528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2529
2530 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2531 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving remove_template_based_articles:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2532 END IF;
2533
2534 IF lock_kart_for_upd_csr%ISOPEN THEN
2535 CLOSE lock_kart_for_upd_csr;
2536 END IF;
2537
2538 IF lock_var_for_del_csr%ISOPEN THEN
2539 CLOSE lock_var_for_del_csr;
2540 END IF;
2541
2542 IF lock_kart_for_del_csr%ISOPEN THEN
2543 CLOSE lock_kart_for_del_csr;
2544 END IF;
2545
2546 IF lock_scn_for_del_csr%ISOPEN THEN
2547 CLOSE lock_scn_for_del_csr;
2548 END IF;
2549
2550 IF l_get_manual_art_csr%ISOPEN THEN
2551 CLOSE l_get_manual_art_csr ;
2552 END IF;
2553
2554 IF l_check_unassigned_section_csr%ISOPEN THEN
2555 CLOSE l_check_unassigned_section_csr ;
2556 END IF;
2557
2558 x_return_status := G_RET_STS_UNEXP_ERROR;
2559 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2560
2561 WHEN OTHERS THEN
2562
2563 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2564 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving remove_template_based_articles because of EXCEPTION: '||sqlerrm);
2565 END IF;
2566
2567 IF l_get_manual_art_csr%ISOPEN THEN
2568 CLOSE l_get_manual_art_csr ;
2569 END IF;
2570
2571 IF l_check_unassigned_section_csr%ISOPEN THEN
2572 CLOSE l_check_unassigned_section_csr ;
2573 END IF;
2574
2575 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2576 p_msg_name => G_UNEXPECTED_ERROR,
2577 p_token1 => G_SQLCODE_TOKEN,
2578 p_token1_value => sqlcode,
2579 p_token2 => G_SQLERRM_TOKEN,
2580 p_token2_value => sqlerrm);
2581
2582 x_return_status := G_RET_STS_UNEXP_ERROR;
2583 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2584
2585 END remove_template_based_articles;
2586
2587 PROCEDURE copy_tc(
2588 p_api_version IN NUMBER,
2589 p_init_msg_list IN VARCHAR2,
2590 p_commit IN VARCHAR2,
2591 p_source_doc_type IN VARCHAR2,
2592 p_source_doc_id IN NUMBER,
2593 p_target_doc_type IN OUT NOCOPY VARCHAR2,
2594 p_target_doc_id IN OUT NOCOPY NUMBER,
2595 p_keep_version IN VARCHAR2,
2596 p_article_effective_date IN DATE,
2597 p_target_template_rec IN OKC_TERMS_TEMPLATES_PVT.template_rec_type,
2598 p_document_number IN VARCHAR2,
2599 p_retain_deliverable IN VARCHAR2,
2600 p_allow_duplicates IN VARCHAR2,
2601 p_keep_orig_ref IN VARCHAR2,
2602 x_return_status OUT NOCOPY VARCHAR2,
2603 x_msg_data OUT NOCOPY VARCHAR2,
2604 x_msg_count OUT NOCOPY NUMBER,
2605 p_copy_abstract_yn IN VARCHAR,
2606 p_copy_for_amendment IN VARCHAR2 default 'N',
2607 -- Fix for defaulting Contract Admin
2608 p_contract_admin_id IN NUMBER := NULL,
2609 p_legal_contact_id IN NUMBER := NULL,
2610 p_retain_clauses IN VARCHAR2 --kkolukul: CLM Changes
2611
2612 , p_retain_lock_terms_yn IN VARCHAR2 -- conc Mod changes start
2613 ,p_retain_lock_xprt_yn IN VARCHAR2 -- conc Mod changes start
2614 ) IS
2615
2616 l_api_version CONSTANT NUMBER := 1;
2617 l_api_name CONSTANT VARCHAR2(30) := 'copy_tc';
2618 l_dummy_var VARCHAR2(1) :='?';
2619 l_doc_type_name OKC_BUS_DOC_TYPES_V.NAME%TYPE;
2620 l_article_effective_date DATE;
2621 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
2622 l_document_type OKC_BUS_DOC_TYPES_B.DOCUMENT_TYPE%TYPE;
2623 l_document_id NUMBER;
2624 l_get_from_library VARCHAR2(1);
2625 l_no_terms_found BOOLEAN :=FALSE;
2626 l_tmpl_usage_id NUMBER;
2627 lx_tmpl_usage_id NUMBER;
2628 l_term_instantiated VARCHAR2(1);
2629 l_approval_abstract_text OKC_TEMPLATE_USAGES.APPROVAL_ABSTRACT_TEXT%TYPE;
2630 l_contract_admin_id OKC_TEMPLATE_USAGES.CONTRACT_ADMIN_ID%TYPE;
2631 l_legal_contact_id OKC_TEMPLATE_USAGES.LEGAL_CONTACT_ID%TYPE;
2632
2633 CURSOR l_get_allwd_tmp_usages_csr IS
2634 SELECT '!' FROM OKC_ALLOWED_TMPL_USAGES
2635 WHERE template_id=p_source_doc_id
2636 AND document_type = p_target_doc_type;
2637
2638 CURSOR l_get_tmpl_csr IS
2639 SELECT * FROM OKC_TERMS_TEMPLATES_ALL
2640 WHERE template_id=p_source_doc_id;
2641
2642 CURSOR l_get_doc_type_name_csr IS
2643 SELECT name FROM OKC_BUS_DOC_TYPES_V
2644 WHERE document_type = p_target_doc_type;
2645
2646 CURSOR l_check_tmp_usage_csr IS
2647 SELECT '!' FROM OKC_TEMPLATE_USAGES
2648 WHERE document_type = p_target_doc_type
2649 AND document_id = p_target_doc_id;
2650
2651 CURSOR l_lock_usg_csr IS
2652 SELECT ROWID FROM OKC_TEMPLATE_USAGES
2653 WHERE DOCUMENT_TYPE=p_target_doc_type
2654 AND DOCUMENT_ID=p_target_doc_id
2655 FOR UPDATE NOWAIT;
2656
2657 CURSOR l_get_usage_csr IS
2658 SELECT * FROM OKC_TEMPLATE_USAGES
2659 WHERE DOCUMENT_TYPE=p_source_doc_type
2660 AND DOCUMENT_ID=p_source_doc_id;
2661
2662 CURSOR l_get_tgt_usage_csr IS
2663 SELECT * FROM OKC_TEMPLATE_USAGES
2664 WHERE DOCUMENT_TYPE=p_target_doc_type
2665 AND DOCUMENT_ID=p_target_doc_id;
2666
2667
2668 -- Bug 8246502 Changes Begins
2669 l_config_exists VARCHAR2(1):='N';
2670
2671 CURSOR check_config_exists(c_config_header_id number,c_config_rev_nbr number) IS
2672 SELECT 'Y'
2673 FROM cz_config_hdrs
2674 WHERE config_hdr_id = c_config_header_id
2675 AND config_rev_nbr = c_config_rev_nbr;
2676 -- Bug 8246502 Changes Begins
2677
2678 CURSOR l_get_target_usage IS
2679 SELECT 'Y' FROM OKC_TEMPLATE_USAGES
2680 WHERE DOCUMENT_TYPE=p_target_doc_type
2681 AND DOCUMENT_ID=p_target_doc_id;
2682
2683 CURSOR l_get_allwd_usage_csr(b_source_doc_id NUMBER) IS
2684 SELECT * from okc_allowed_tmpl_usages where
2685 template_id=b_source_doc_id;
2686
2687 l_tmpl_rec l_get_tmpl_csr%ROWTYPE;
2688 l_usage_rec l_get_usage_csr%ROWTYPE;
2689 l_tgt_usage_rec l_get_tgt_usage_csr%ROWTYPE;
2690
2691 l_header_id NUMBER;
2692 l_rev_nbr NUMBER;
2693 l_source_change_allowed_flag VARCHAR2(1) := 'Y';
2694 lx_new_contract_admin_id NUMBER; -- Added for Bug 6080483
2695
2696 l_copy_xprt_data VARCHAR2(1) := 'Y';
2697
2698 --contracts rules engines changes
2699 l_cntrcts_ruls_eng_exists VARCHAR(1) := 'N';
2700
2701 BEGIN
2702
2703 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2704 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering OKC_TERMS_COPY_PVT.copy_tc ');
2705 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Parameter List ');
2706 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_api_version : '||p_api_version);
2707 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_init_msg_list : '||p_init_msg_list);
2708 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_commit : '||p_commit);
2709 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_type : '||p_source_doc_type);
2710 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_id : '||p_source_doc_id);
2711 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_type : '||p_target_doc_type);
2712 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_id : '||p_target_doc_id);
2713 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_keep_version : '||p_keep_version);
2714 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_article_effective_date : '||p_article_effective_date);
2715 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_document_number : '||p_document_number);
2716 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_retain_deliverable : '||p_retain_deliverable);
2717 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_allow_duplicates : '||p_allow_duplicates);
2718 END IF;
2719
2720 -- Standard Start of API savepoint
2721 SAVEPOINT g_copy_tc_pvt;
2722
2723 -- Standard call to check for call compatibility.
2724 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2726 END IF;
2727
2728 -- Initialize message list if p_init_msg_list is set to TRUE.
2729 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2730 FND_MSG_PUB.initialize;
2731 END IF;
2732
2733 -- Initialize API return status to success
2734 x_return_status := FND_API.G_RET_STS_SUCCESS;
2735
2736 -- Checking If doc types are valid
2737
2738 IF (p_source_doc_type IS NOT NULL
2739 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_PVT.is_doc_type_valid ( p_doc_type => p_source_doc_type , x_return_status => x_return_status))) THEN
2740
2741 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2742 p_msg_name => 'OKC_INVALID_DOC_TYPE',
2743 p_token1 => 'DOCUMENT_TYPE',
2744 p_token2_value => p_source_doc_type);
2745 RAISE FND_API.G_EXC_ERROR ;
2746
2747
2748 END IF;
2749
2750 IF (p_target_doc_type IS NOT NULL
2751 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_PVT.is_doc_type_valid ( p_doc_type => p_target_doc_type , x_return_status => x_return_status))) THEN
2752
2753 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2754 p_msg_name => 'OKC_INVALID_DOC_TYPE',
2755 p_token1 => 'DOCUMENT_TYPE',
2756 p_token2_value => p_target_doc_type);
2757 RAISE FND_API.G_EXC_ERROR ;
2758
2759 END IF;
2760
2761 IF p_target_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
2762 AND p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN -- Template to Template Copy
2763
2764 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2765 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Case of Template to Template Copy ');
2766 END IF;
2767
2768
2769 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2770 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100:Start template Creation. template Name '||p_target_template_rec.template_name);
2771 END IF;
2772
2773 OKC_TERMS_TEMPLATES_GRP.create_template(
2774 p_api_version => 1,
2775 p_init_msg_list => FND_API.G_FALSE,
2776 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2777 p_commit => FND_API.G_FALSE,
2778 x_return_status => x_return_status,
2779 x_msg_count => x_msg_count,
2780 x_msg_data => x_msg_data,
2781 p_template_name => p_target_template_rec.template_name,
2782 p_template_id => Null,
2783 p_working_copy_flag => p_target_template_rec.working_copy_flag,
2784 p_intent => p_target_template_rec.intent,
2785 p_status_code => p_target_template_rec.status_code,
2786 p_start_date => p_target_template_rec.start_date,
2787 p_end_date => p_target_template_rec.end_date,
2788 p_global_flag => p_target_template_rec.global_flag,
2789 p_parent_template_id => p_target_template_rec.parent_template_id,
2790 p_print_template_id => p_target_template_rec.print_template_id,
2791 p_contract_expert_enabled=> p_target_template_rec.contract_expert_enabled,
2792 p_cls_enabled => p_target_template_rec.cls_enabled,
2793 p_xprt_clause_mandatory_flag => p_target_template_rec.xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
2794 p_xprt_scn_code => p_target_template_rec.xprt_scn_code, -- bug# 4004496
2795 p_template_model_id => p_target_template_rec.template_model_id,
2796 p_instruction_text => p_target_template_rec.instruction_text,
2797 p_tmpl_numbering_scheme => p_target_template_rec.tmpl_numbering_scheme,
2798 p_description => p_target_template_rec.description,
2799 p_org_id => p_target_template_rec.org_id,
2800 p_orig_system_reference_code=> p_target_template_rec.orig_system_reference_code,
2801 p_orig_system_reference_id1=> p_target_template_rec.orig_system_reference_id1,
2802 p_orig_system_reference_id2=> p_target_template_rec.orig_system_reference_id2,
2803 p_cz_export_wf_key => p_target_template_rec.cz_export_wf_key,
2804 p_attribute_category => p_target_template_rec.attribute_category,
2805 p_attribute1 => p_target_template_rec.attribute1,
2806 p_attribute2 => p_target_template_rec.attribute2,
2807 p_attribute3 => p_target_template_rec.attribute3,
2808 p_attribute4 => p_target_template_rec.attribute4,
2809 p_attribute5 => p_target_template_rec.attribute5,
2810 p_attribute6 => p_target_template_rec.attribute6,
2811 p_attribute7 => p_target_template_rec.attribute7,
2812 p_attribute8 => p_target_template_rec.attribute8,
2813 p_attribute9 => p_target_template_rec.attribute9,
2814 p_attribute10 => p_target_template_rec.attribute10,
2815 p_attribute11 => p_target_template_rec.attribute11,
2816 p_attribute12 => p_target_template_rec.attribute12,
2817 p_attribute13 => p_target_template_rec.attribute13,
2818 p_attribute14 => p_target_template_rec.attribute14,
2819 p_attribute15 => p_target_template_rec.attribute15,
2820 --MLS for templates
2821 p_language => p_target_template_rec.language,
2822 p_translated_from_tmpl_id => p_target_template_rec.translated_from_tmpl_id,
2823
2824 x_template_id => l_template_id);
2825
2826 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2827 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'150:Finished template Creation. Return Status '||x_return_status||' new template_id '||l_template_id);
2828 END IF;
2829
2830 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2832 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2833 RAISE FND_API.G_EXC_ERROR ;
2834 END IF;
2835
2836 p_target_doc_id := l_template_id;
2837
2838 IF p_target_template_rec.working_copy_flag='Y' THEN
2839
2840 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2841 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'160:Calling OKC_ALLOWED_TMPL_USAGES_GRP.Create_Allowed_Tmpl_Usages');
2842 END IF;
2843
2844 -- Copy Usage record.
2845 FOR cr in l_get_allwd_usage_csr(p_target_template_rec.parent_template_id) LOOP
2846 select OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL into l_tmpl_usage_id from dual;
2847 OKC_ALLOWED_TMPL_USAGES_GRP.Create_Allowed_Tmpl_Usages(
2848 p_api_version =>1,
2849 p_init_msg_list =>FND_API.G_FALSE,
2850 p_commit => FND_API.G_FALSE,
2851 x_return_status => x_return_status,
2852 x_msg_count => x_msg_count,
2853 x_msg_data => x_msg_data,
2854 p_template_id => l_template_id,
2855 p_document_type => cr.document_type,
2856 p_default_yn => cr.default_yn,
2857 p_allowed_tmpl_usages_id =>l_tmpl_usage_id,
2858 p_attribute_category =>cr.attribute_category,
2859 p_attribute1 =>cr.attribute1,
2860 p_attribute2 =>cr.attribute2,
2861 p_attribute3 =>cr.attribute3,
2862 p_attribute4 =>cr.attribute4,
2863 p_attribute5 =>cr.attribute5,
2864 p_attribute6 =>cr.attribute6,
2865 p_attribute7 =>cr.attribute7,
2866 p_attribute8 =>cr.attribute8,
2867 p_attribute9 =>cr.attribute9,
2868 p_attribute10 =>cr.attribute10,
2869 p_attribute11 =>cr.attribute11,
2870 p_attribute12 =>cr.attribute12,
2871 p_attribute13 =>cr.attribute13,
2872 p_attribute14 =>cr.attribute14,
2873 p_attribute15 =>cr.attribute15,
2874 x_allowed_tmpl_usages_id =>lx_tmpl_usage_id
2875 );
2876 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2878 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2879 RAISE FND_API.G_EXC_ERROR ;
2880 END IF;
2881 END LOOP;
2882
2883 END IF;
2884
2885 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2886 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Calling copy_section');
2887 END IF;
2888
2889 copy_sections(
2890 p_target_doc_type => p_target_doc_type,
2891 p_source_doc_type => p_source_doc_type,
2892 p_target_doc_id => l_template_id,
2893 p_source_doc_id => p_source_doc_id,
2894 p_source_version_number => NULL,
2895 p_copy_from_archive => 'N',
2896 x_return_status => x_return_status,
2897 x_msg_data => x_msg_data,
2898 x_msg_count => x_msg_count);
2899
2900 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2901 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:Finished copy_section. Return Status '||x_return_status);
2902 END IF;
2903
2904 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2906 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2907 RAISE FND_API.G_EXC_ERROR ;
2908 END IF;
2909
2910 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2911 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Entering copy_articles. ');
2912 END IF;
2913
2914 copy_articles(
2915 p_target_doc_type => p_target_doc_type,
2916 p_source_doc_type => p_source_doc_type,
2917 p_target_doc_id => l_template_id,
2918 P_source_doc_id => p_source_doc_id,
2919 p_keep_version => p_keep_version,
2920 p_article_effective_date => p_article_effective_date,
2921 p_source_version_number => NULL,
2922 p_copy_from_archive =>'N',
2923 x_return_status => x_return_status,
2924 x_msg_data => x_msg_data,
2925 x_msg_count => x_msg_count);
2926
2927 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2928 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
2929 END IF;
2930
2931 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2933 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2934 RAISE FND_API.G_EXC_ERROR ;
2935 END IF;
2936
2937 OKC_XPRT_TMPL_RULE_ASSNS_PVT.copy_template_rule_assns(
2938 p_api_version => 1,
2939 p_init_msg_list => FND_API.G_FALSE,
2940 p_commit => FND_API.G_FALSE,
2941 p_source_template_id => p_source_doc_id,
2942 p_target_template_id => l_template_id,
2943 x_return_status => x_return_status,
2944 x_msg_data => x_msg_data,
2945 x_msg_count => x_msg_count
2946 );
2947
2948 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2949 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800:Finished copy_template_rule_assns. Return Status '||x_return_status);
2950 END IF;
2951
2952 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2954 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2955 RAISE FND_API.G_EXC_ERROR ;
2956 END IF;
2957
2958 ELSIF p_source_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
2959 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN -- Doc to Doc Copy
2960
2961 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2962 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Case of Document to Document Copy ');
2963 END IF;
2964
2965 OPEN l_get_usage_csr;
2966 FETCH l_get_usage_csr INTO l_usage_rec;
2967 IF l_get_usage_csr%NOTFOUND THEN
2968 l_no_terms_found := TRUE;
2969 END IF;
2970
2971 CLOSE l_get_usage_csr;
2972
2973 -- Conc Mod Changes Start
2974 IF ( p_retain_lock_xprt_yn = 'Y' AND
2975 okc_k_entity_locks_grp.isLockExists(P_ENTITY_NAME => okc_k_entity_locks_grp.G_XPRT_ENTITY,
2976 p_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
2977 p_LOCK_BY_DOCUMENT_ID => p_target_doc_id
2978 ) = 'Y'
2979 ) THEN
2980 -- lock exists so do not copy xprt data.
2981 -- if next if block is not executed then the config header id, config_revision_number will be correct only.
2982 l_copy_xprt_data := 'N';
2983
2984 OPEN l_get_tgt_usage_csr;
2985 FETCH l_get_tgt_usage_csr INTO l_tgt_usage_rec;
2986 CLOSE l_get_tgt_usage_csr;
2987
2988 l_usage_rec.config_header_id := l_tgt_usage_rec.config_header_id;
2989 l_usage_rec.config_revision_number:=l_tgt_usage_rec.config_revision_number;
2990 l_usage_rec.valid_config_yn:=l_tgt_usage_rec.valid_config_yn;
2991
2992 END IF;
2993
2994
2995 BEGIN
2996 SELECT UPPER(FND_PROFILE.VALUE('OKC_USE_CONTRACTS_RULES_ENGINE')) INTO l_cntrcts_ruls_eng_exists FROM DUAL;
2997 EXCEPTION
2998 WHEN OTHERS THEN
2999 l_cntrcts_ruls_eng_exists := 'N';
3000 END;
3001
3002 IF l_cntrcts_ruls_eng_exists IS NULL THEN
3003 l_cntrcts_ruls_eng_exists := 'N';
3004 END IF;
3005
3006 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3007 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'550: contracts rules engine available:' || l_cntrcts_ruls_eng_exists);
3008 END IF;
3009
3010
3011 If not l_no_terms_found AND l_copy_xprt_data = 'Y' THEN
3012
3013
3014 IF l_usage_rec.config_header_id IS NOT NULL AND l_cntrcts_ruls_eng_exists = 'N' THEN
3015 -- Bug 8246502 Changes Begins
3016 OPEN check_config_exists(l_usage_rec.config_header_id,l_usage_rec.config_revision_number);
3017 FETCH check_config_exists INTO l_config_exists;
3018 CLOSE check_config_exists;
3019
3020
3021
3022 IF l_config_exists = 'Y' THEN
3023 -- Bug 8246502 Changes Ends
3024
3025 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3026 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:Calling OKC_XPRT_CZ_INT_PVT.copy_configuration');
3027 END IF;
3028
3029 /* Call Copy Config API provided by Contract Expert Team */
3030 OKC_XPRT_CZ_INT_PVT.copy_configuration(
3031 p_api_version => 1,
3032 p_init_msg_list => OKC_API.G_FALSE,
3033 p_config_header_id =>l_usage_rec.config_header_id,
3034 p_config_rev_nbr =>l_usage_rec.config_revision_number,
3035 p_new_config_flag => FND_API.G_TRUE,
3036 x_new_config_header_id => l_header_id,
3037 x_new_config_rev_nbr => l_rev_nbr,
3038 x_return_status => x_return_status,
3039 x_msg_data => x_msg_data,
3040 x_msg_count => x_msg_count);
3041
3042 l_usage_rec.config_header_id := l_header_id;
3043 l_usage_rec.config_revision_number:=l_rev_nbr;
3044
3045 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3046 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: After Calling OKC_XPRT_CZ_INT_PVT.copy_configuration');
3047 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: x_return_status '||x_return_status);
3048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: x_msg_count '||x_msg_count);
3049 END IF;
3050
3051 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3053 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3054 RAISE FND_API.G_EXC_ERROR ;
3055 END IF;
3056
3057 -- Bug 8246502 Change
3058 END IF;
3059
3060 -- begin of contracts rules engine copy
3061 ELSIF l_cntrcts_ruls_eng_exists = 'Y' THEN
3062
3063 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3064 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'550: Contracts rules engine is enabled.');
3065 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'550: Copying question and response to okc_xprt_doc_ques_response.');
3066 END IF;
3067
3068 BEGIN
3069 INSERT INTO okc_xprt_doc_ques_response(doc_question_response_id,doc_id,doc_type,question_id,response)
3070 (SELECT okc_xprt_doc_ques_response_s.NEXTVAL,p_target_doc_id,p_target_doc_type,question_id,response
3071 FROM okc_xprt_doc_ques_response WHERE doc_id = p_source_doc_id AND doc_type = p_source_doc_type );
3072
3073 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3074 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'550: Succesfull in copying values to
3075 okc_xprt_doc_ques_response.');
3076 END IF;
3077
3078 EXCEPTION
3079 WHEN OTHERS THEN
3080 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3081 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'550: Failed copying values to
3082 okc_xprt_doc_ques_response.');
3083 END IF;
3084 END;
3085 -- end of contracts rules engine copy
3086
3087 END IF; -- ending here;
3088
3089
3090 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3091 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600:Create Template usage record ');
3092 END IF;
3093
3094 OPEN l_get_target_usage;
3095 FETCH l_get_target_usage into l_term_instantiated;
3096 CLOSE l_get_target_usage;
3097
3098 IF ( p_copy_abstract_yn = 'Y') THEN
3099 l_approval_abstract_text := l_usage_rec.approval_abstract_text;
3100 END IF;
3101 IF (p_copy_for_amendment = 'Y' AND l_usage_rec.contract_source_code = G_ATTACHED_CONTRACT_SOURCE) THEN
3102 l_source_change_allowed_flag := 'N';
3103 END IF;
3104
3105 -- Fix for Bug 4897464
3106 IF (p_copy_for_amendment = 'N') THEN
3107 l_usage_rec.lock_terms_flag := NULL;
3108 l_usage_rec.locked_by_user_id := NULL;
3109 END IF;
3110
3111 IF (p_allow_duplicates <> 'Y' and l_term_instantiated='Y') or l_term_instantiated is NULL THEN
3112 l_contract_admin_id := p_contract_admin_id;
3113 l_legal_contact_id := p_legal_contact_id;
3114 IF(l_contract_admin_id is null) then
3115 if(p_target_doc_type = 'QUOTE') then
3116 l_contract_admin_id := okc_terms_util_pvt.get_default_contract_admin_id(p_target_doc_type, p_target_doc_id);
3117 end if;
3118 if(l_contract_admin_id is null) then
3119 l_contract_admin_id := l_usage_rec.contract_admin_id;
3120 end if;
3121 end if;
3122 if(l_legal_contact_id is null) then
3123 l_legal_contact_id := l_usage_rec.legal_contact_id;
3124 end if;
3125 -- Bug# 9406214
3126 if l_config_exists = 'N' then
3127 l_usage_rec.config_header_id := NULL;
3128 l_usage_rec.config_revision_number:=NULL;
3129 l_usage_rec.valid_config_yn:=NULL;
3130 end if;
3131 IF l_copy_xprt_data = 'Y' THEN --
3132 OKC_TEMPLATE_USAGES_GRP.create_template_usages(
3133 p_api_version => 1,
3134 p_init_msg_list => FND_API.G_FALSE,
3135 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3136 p_commit => FND_API.G_FALSE,
3137 x_return_status => x_return_status,
3138 x_msg_data => x_msg_data,
3139 x_msg_count => x_msg_count,
3140 p_document_type => p_target_doc_type,
3141 p_document_id => p_target_doc_id,
3142 p_template_id => l_usage_rec.template_id,
3143 p_doc_numbering_scheme => l_usage_rec.doc_numbering_scheme,
3144 p_document_number => p_document_number,
3145 p_article_effective_date => p_article_effective_date,
3146 p_config_header_id => l_usage_rec.config_header_id,
3147 p_config_revision_number => l_usage_rec.config_revision_number,
3148 p_valid_config_yn => l_usage_rec.valid_config_yn,
3149 x_document_type => l_document_type,
3150 x_document_id => l_document_id,
3151 p_approval_abstract_text => l_approval_abstract_text,
3152 p_contract_source_code => l_usage_rec.contract_source_code,
3153 p_authoring_party_code => l_usage_rec.authoring_party_code,
3154 p_source_change_allowed_flag => l_source_change_allowed_flag,
3155 -- Additional fix for bug# 4116433.
3156 p_autogen_deviations_flag => l_usage_rec.autogen_deviations_flag,
3157 p_lock_terms_flag => l_usage_rec.lock_terms_flag,
3158 p_enable_reporting_flag => l_usage_rec.enable_reporting_flag,
3159 p_locked_by_user_id => l_usage_rec.locked_by_user_id,
3160 -- Fix for defaulting Contract Admin
3161 p_contract_admin_id => l_contract_admin_id,
3162 p_legal_contact_id => l_legal_contact_id
3163 -- Concurrent Mod changes
3164 ,p_orig_system_reference_code => p_source_doc_type
3165 ,p_orig_system_reference_id1 => p_source_doc_id,
3166 --new okc rules engine contract expert parameter
3167 p_contract_expert_finish_flag => l_usage_rec.contract_expert_finish_flag
3168 );
3169 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3170 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600:After call to OKC_TEMPLATE_USAGES_GRP.create_template_usages x_return_status : '||x_return_status);
3171 END IF;
3172 ELSIF Nvl(l_copy_xprt_data,'N') = 'N' THEN
3173 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
3174 p_api_version => 1,
3175 p_init_msg_list => FND_API.G_FALSE,
3176 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3177 p_commit => FND_API.G_FALSE,
3178 x_return_status => x_return_status,
3179 x_msg_data => x_msg_data,
3180 x_msg_count => x_msg_count,
3181 p_document_type => p_target_doc_type,
3182 p_document_id => p_target_doc_id,
3183 p_template_id => l_usage_rec.template_id,
3184 p_doc_numbering_scheme => l_usage_rec.doc_numbering_scheme,
3185 p_document_number => p_document_number,
3186 p_article_effective_date => p_article_effective_date,
3187 p_config_header_id => l_usage_rec.config_header_id,
3188 p_config_revision_number => l_usage_rec.config_revision_number,
3189 p_valid_config_yn => l_usage_rec.valid_config_yn,
3190 p_approval_abstract_text => l_approval_abstract_text,
3191 p_contract_source_code => l_usage_rec.contract_source_code,
3192 p_authoring_party_code => l_usage_rec.authoring_party_code,
3193 p_source_change_allowed_flag => l_source_change_allowed_flag,
3194 -- Additional fix for bug# 4116433.
3195 p_autogen_deviations_flag => l_usage_rec.autogen_deviations_flag,
3196 p_lock_terms_flag => l_usage_rec.lock_terms_flag,
3197 p_enable_reporting_flag => l_usage_rec.enable_reporting_flag,
3198 p_locked_by_user_id => l_usage_rec.locked_by_user_id,
3199 -- Fix for defaulting Contract Admin
3200 p_contract_admin_id => l_contract_admin_id,
3201 p_legal_contact_id => l_legal_contact_id
3202 );
3203
3204
3205 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3206 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600:After call to OKC_TEMPLATE_USAGES_GRP.create_template_usages x_return_status : '||x_return_status);
3207 END IF;
3208
3209 END IF;
3210
3211
3212 END IF; -- IF p_allow_duplicates <> Y' THEN
3213
3214 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3216 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3217 RAISE FND_API.G_EXC_ERROR ;
3218 END IF;
3219
3220 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3221 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'650:Calling copy_section');
3222 END IF;
3223
3224 copy_sections( p_target_doc_type => p_target_doc_type,
3225 p_source_doc_type => p_source_doc_type,
3226 p_target_doc_id => p_target_doc_id,
3227 p_source_doc_id => p_source_doc_id,
3228 p_source_version_number=> NULL,
3229 p_copy_from_archive => 'N',
3230 p_keep_orig_ref => p_keep_orig_ref,
3231 x_return_status => x_return_status,
3232 x_msg_data => x_msg_data,
3233 x_msg_count => x_msg_count
3234 ,p_retain_lock_terms_yn => p_retain_lock_terms_yn);
3235
3236 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3237 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'650:Finished copy_section. Return Status '||x_return_status);
3238 END IF;
3239
3240 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3242 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3243 RAISE FND_API.G_EXC_ERROR ;
3244 END IF;
3245
3246 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3247 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Entering copy_articles');
3248 END IF;
3249
3250 copy_articles(
3251 p_target_doc_type => p_target_doc_type,
3252 p_source_doc_type => p_source_doc_type,
3253 p_target_doc_id => p_target_doc_id,
3254 p_source_doc_id => p_source_doc_id,
3255 p_keep_version => p_keep_version,
3256 p_article_effective_date => p_article_effective_date,
3257 p_source_version_number => NULL,
3258 p_copy_from_archive =>'N',
3259 p_keep_orig_ref => p_keep_orig_ref,
3260 x_return_status => x_return_status,
3261 x_msg_data => x_msg_data,
3262 x_msg_count => x_msg_count
3263 ,p_retain_lock_terms_yn => p_retain_lock_terms_yn);
3264
3265 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3266 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
3267 END IF;
3268
3269 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3271 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3272 RAISE FND_API.G_EXC_ERROR ;
3273 END IF;
3274
3275 SELECT decode(p_keep_version,'Y','N','Y') INTO l_get_from_library FROM DUAL;
3276
3277 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3278 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800:Entering copy_article_variables. ');
3279 END IF;
3280
3281 copy_article_variables(
3282 p_target_doc_type => p_target_doc_type,
3283 p_source_doc_type => p_source_doc_type,
3284 p_target_doc_id => p_target_doc_id,
3285 p_source_doc_id => p_source_doc_id,
3286 p_get_from_library => l_get_from_library,
3287 p_keep_orig_ref => p_keep_orig_ref,
3288 x_return_status => x_return_status,
3289 x_msg_data => x_msg_data,
3290 x_msg_count => x_msg_count
3291 ,p_retain_lock_terms_yn => p_retain_lock_terms_yn);
3292
3293 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3294 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900:Exited copy_article_variables.Return Status '||x_return_status);
3295 END IF;
3296
3297 /*When we are adding multiple templates to the doc, we need to copy all the templates
3298 added to the doc in the new table created : okc_mlp_template_usages. Calling the
3299 new API created to insert to this table*/
3300 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3301 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'910:Create template usage record in okc_mlp_template_usages');
3302 END IF;
3303 okc_clm_pkg.copy_usages_row(
3304 p_target_doc_type => p_target_doc_type,
3305 p_source_doc_type => p_source_doc_type,
3306 p_target_doc_id => p_target_doc_id,
3307 p_source_doc_id => p_source_doc_id,
3308 x_return_status => x_return_status,
3309 x_msg_count => x_msg_count,
3310 x_msg_data => x_msg_data);
3311 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3312 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'920:Exited copy_usages_row.Return Status '||x_return_status);
3313 END IF;
3314 --end CLM Changes
3315
3316 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3318 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3319 RAISE FND_API.G_EXC_ERROR ;
3320 END IF;
3321 END IF;
3322
3323 ELSIF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
3324 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN -- Template to Doc Copy
3325
3326 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3327 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'204:Case of Template to Document Copy ');
3328 END IF;
3329
3330 OPEN l_get_tmpl_csr ;
3331 FETCH l_get_tmpl_csr INTO l_tmpl_rec;
3332 CLOSE l_get_tmpl_csr ;
3333
3334
3335 l_dummy_var := '?';
3336 OPEN l_get_allwd_tmp_usages_csr;
3337 FETCH l_get_allwd_tmp_usages_csr INTO l_dummy_var;
3338 CLOSE l_get_allwd_tmp_usages_csr;
3339
3340 IF l_dummy_var = '?' THEN
3341
3342 OPEN l_get_doc_type_name_csr ;
3343 FETCH l_get_doc_type_name_csr INTO l_doc_type_name;
3344 CLOSE l_get_doc_type_name_csr ;
3345
3346 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3347 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:For this Template,Doc type usage not defined ');
3348 END IF;
3349
3350 okc_Api.Set_Message(p_app_name => G_APP_NAME,
3351 p_msg_name => 'OKC_ALLOWED_USAGE',
3352 p_token1 => 'TEMPLATE_NAME',
3353 p_token1_value => l_tmpl_rec.template_name,
3354 p_token2 => 'DOCUMENT_TYPE',
3355 p_token2_value => l_doc_type_name);
3356 RAISE FND_API.G_EXC_ERROR ;
3357
3358 END IF;
3359
3360 l_dummy_var := '?';
3361 OPEN l_check_tmp_usage_csr;
3362 FETCH l_check_tmp_usage_csr INTO l_dummy_var;
3363 CLOSE l_check_tmp_usage_csr;
3364
3365 IF l_dummy_var <> '?' THEN
3366
3367 -- Document already using a template.Need to delete those articles.
3368 /* kkolukul: clm changes - if retain Clauses = 'Y' then we need to retain
3369 the clauses from existing template and add clauses from the new template to this set.*/
3370
3371 IF (p_retain_clauses = 'N') then
3372
3373 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3374 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Document was already using template.Removing template based articles from document');
3375 END IF;
3376
3377 remove_template_based_articles(
3378 p_doc_type => p_target_doc_type,
3379 p_doc_id => p_target_doc_id,
3380 p_retain_deliverable => p_retain_deliverable,
3381 x_return_status => x_return_status,
3382 x_msg_data => x_msg_data,
3383 x_msg_count => x_msg_count);
3384
3385 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3386 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:After Call to remove_template_based_articles x_return_status : '||x_return_status);
3387 END IF;
3388 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3390 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3391 RAISE FND_API.G_EXC_ERROR ;
3392 END IF;
3393
3394 OPEN l_lock_usg_csr;
3395 CLOSE l_lock_usg_csr;
3396
3397 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3398 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Update the existing OKC_TEMPLATE_USAGES record with new template id : '||p_source_doc_id);
3399 END IF;
3400
3401 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN --okc rules engine
3402
3403 --Added in 10+ word integration, update values for contract_source_code and authoring_party_code
3404 UPDATE OKC_TEMPLATE_USAGES
3405 SET TEMPLATE_ID = p_source_doc_id,
3406 DOC_NUMBERING_SCHEME = l_tmpl_rec.tmpl_numbering_scheme,
3407 ARTICLE_EFFECTIVE_DATE = p_article_effective_date, -- To Check and confirm with PMs
3408 CONTRACT_EXPERT_FINISH_FLAG = 'N',
3409 LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
3410 LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
3411 LAST_UPDATE_DATE = sysdate,
3412 CONTRACT_SOURCE_CODE = G_STRUCT_CONTRACT_SOURCE,
3413 AUTHORING_PARTY_CODE = G_INTERNAL_PARTY_CODE,
3414 CONTRACT_ADMIN_ID = p_contract_admin_id,
3415 LEGAL_CONTACT_ID = p_legal_contact_id
3416 WHERE document_type = p_target_doc_type
3417 AND document_id = p_target_doc_id;
3418
3419 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3420 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'405: Deleting the responses from okc_xprt_doc_ques_response table');
3421 END IF;
3422
3423 --deleting responses when u change the template
3424 DELETE FROM okc_xprt_doc_ques_response
3425 WHERE doc_id = p_target_doc_id
3426 AND doc_type = p_target_doc_type;
3427
3428 ELSE --configurator rule engine
3429
3430 --Added in 10+ word integration, update values for contract_source_code and authoring_party_code
3431 UPDATE OKC_TEMPLATE_USAGES
3432 SET TEMPLATE_ID = p_source_doc_id,
3433 DOC_NUMBERING_SCHEME = l_tmpl_rec.tmpl_numbering_scheme,
3434 ARTICLE_EFFECTIVE_DATE = p_article_effective_date, -- To Check and confirm with PMs
3435 CONFIG_HEADER_ID = NULL,
3436 CONFIG_REVISION_NUMBER = NULL,
3437 VALID_CONFIG_YN = NULL,
3438 LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
3439 LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
3440 LAST_UPDATE_DATE = sysdate,
3441 CONTRACT_SOURCE_CODE = G_STRUCT_CONTRACT_SOURCE,
3442 AUTHORING_PARTY_CODE = G_INTERNAL_PARTY_CODE,
3443 CONTRACT_ADMIN_ID = p_contract_admin_id,
3444 LEGAL_CONTACT_ID = p_legal_contact_id
3445 WHERE document_type = p_target_doc_type
3446 AND document_id = p_target_doc_id;
3447 END IF;
3448
3449 ELSE -- else for IF (p_retain_clauses = 'N') then
3450 /* If p_retain_clauses is 'Y' then we are adding multiple templates to the doc. So we need to save
3451 all the templates added to the doc in the new table created : okc_mlp_template_usages. Calling the
3452 new API created to insert to this table*/
3453 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3454 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'410:Create template usage record in okc_mlp_template_usages');
3455 END IF;
3456
3457 okc_clm_pkg.insert_usages_row(p_document_type => p_target_doc_type,
3458 p_document_id => p_target_doc_id,
3459 p_template_id => p_source_doc_id,
3460 p_doc_numbering_scheme => l_tmpl_rec.tmpl_numbering_scheme,
3461 p_document_number => p_document_number,
3462 p_article_effective_date => p_article_effective_date,
3463 p_config_header_id => Null,
3464 p_config_revision_number => Null,
3465 p_valid_config_yn => Null,
3466 x_return_status => x_return_status,
3467 x_msg_count => x_msg_count,
3468 x_msg_data => x_msg_data);
3469
3470 END IF; ---- end for IF (p_retain_clauses = 'N')
3471
3472 ELSE
3473
3474 -- Added for Bug 6080483
3475 IF p_contract_admin_id IS NULL THEN
3476 -- Get the contract admin
3477 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3478 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'460:Calling get_sales_group_con_admin');
3479 END IF;
3480
3481 lx_new_contract_admin_id := OKC_TERMS_UTIL_PVT.get_default_contract_admin_id(p_target_doc_type, p_target_doc_id );
3482
3483 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3484 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'470:After call to OKC_TERMS_UTIL_PVT.get_sales_group_con_admin p_contract_admin_id: '||lx_new_contract_admin_id);
3485 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'480:After call to OKC_TERMS_UTIL_PVT.get_sales_group_con_admin x_return_status: '||x_return_status);
3486 END IF;
3487 ELSE
3488 lx_new_contract_admin_id := p_contract_admin_id;
3489 END IF;
3490
3491
3492
3493
3494 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3495 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:Create template usage record ');
3496 END IF;
3497
3498 OKC_TEMPLATE_USAGES_GRP.create_template_usages(
3499 p_api_version => 1,
3500 p_init_msg_list => FND_API.G_FALSE,
3501 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3502 p_commit => FND_API.G_FALSE,
3503 x_return_status => x_return_status,
3504 x_msg_count => x_msg_count,
3505 x_msg_data => x_msg_data,
3506 p_document_type => p_target_doc_type,
3507 p_document_id => p_target_doc_id,
3508 p_template_id => p_source_doc_id,
3509 p_doc_numbering_scheme => l_tmpl_rec.tmpl_numbering_scheme,
3510 p_document_number => p_document_number,
3511 p_article_effective_date => p_article_effective_date,
3512 p_config_header_id => Null,
3513 p_config_revision_number => Null,
3514 p_valid_config_yn => Null,
3515 p_contract_admin_id => lx_new_contract_admin_id, --p_contract_admin_id, Bug 6080483
3516 p_legal_contact_id => p_legal_contact_id,
3517 x_document_type => l_document_type,
3518 x_document_id => l_document_id
3519 );
3520
3521 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3522 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:After call to OKC_TEMPLATE_USAGES_GRP.create_template_usages x_return_status: '||x_return_status);
3523 END IF;
3524
3525 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3527 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3528 RAISE FND_API.G_EXC_ERROR ;
3529 END IF;
3530
3531 END IF;
3532
3533
3534 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3535 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:Calling copy_section');
3536 END IF;
3537
3538 copy_sections(
3539 p_target_doc_type => p_target_doc_type,
3540 p_source_doc_type => p_source_doc_type,
3541 p_target_doc_id => p_target_doc_id,
3542 p_source_doc_id => p_source_doc_id,
3543 p_source_version_number=> NULL,
3544 p_copy_from_archive => 'N',
3545 x_return_status => x_return_status,
3546 x_msg_data => x_msg_data,
3547 x_msg_count => x_msg_count,
3548 p_retain_clauses => p_retain_clauses);
3549
3550 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3551 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600:Finished copy_section. Return Status '||x_return_status);
3552 END IF;
3553
3554 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3556 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3557 RAISE FND_API.G_EXC_ERROR ;
3558 END IF;
3559
3560 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3561 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Entering copy_articles ');
3562 END IF;
3563
3564 copy_articles(
3565 p_target_doc_type => p_target_doc_type,
3566 p_source_doc_type => p_source_doc_type,
3567 p_target_doc_id => p_target_doc_id,
3568 P_source_doc_id => p_source_doc_id,
3569 p_keep_version => p_keep_version,
3570 p_article_effective_date => l_article_effective_date,
3571 p_source_version_number => NULL,
3572 p_copy_from_archive =>'N',
3573 x_return_status => x_return_status,
3574 x_msg_data => x_msg_data,
3575 x_msg_count => x_msg_count);
3576
3577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
3579 END IF;
3580
3581 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3583 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3584 RAISE FND_API.G_EXC_ERROR ;
3585 END IF;
3586
3587
3588 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3589 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800:Entering copy_article_variables. ');
3590 END IF;
3591
3592 copy_article_variables(
3593 p_target_doc_type => p_target_doc_type,
3594 p_source_doc_type => p_source_doc_type,
3595 p_target_doc_id => p_target_doc_id,
3596 p_source_doc_id => p_source_doc_id,
3597 p_get_from_library => 'Y',
3598 x_return_status => x_return_status,
3599 x_msg_data => x_msg_data,
3600 x_msg_count => x_msg_count);
3601
3602 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900:Exited copy_article_variables.Return Status '||x_return_status);
3604 END IF;
3605
3606 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3608 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3609 RAISE FND_API.G_EXC_ERROR ;
3610 END IF;
3611
3612 /*kkolukul: clm changes*/
3613 IF (p_retain_clauses = 'Y') then
3614 OKC_CLM_PKG.clm_remove_dup_scn_art( p_document_type => p_target_doc_type,
3615 p_document_id => p_target_doc_id,
3616 x_return_status => x_return_status,
3617 x_msg_data => x_msg_data,
3618 x_msg_count => x_msg_count);
3619 END IF;
3620 END IF;
3621
3622 IF FND_API.To_Boolean( p_commit ) THEN
3623 COMMIT WORK;
3624 END IF;
3625
3626 -- Standard call to get message count and if count is 1, get message info.
3627 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3628
3629 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3630 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Leaving copy_tc');
3631 END IF;
3632
3633 EXCEPTION
3634
3635 WHEN E_Resource_Busy THEN
3636 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3637 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'000: Leaving copy_tc:E_Resource_Busy Exception');
3638 END IF;
3639
3640 IF l_get_doc_type_name_csr%ISOPEN THEN
3641 CLOSE l_get_doc_type_name_csr;
3642 END IF;
3643
3644 IF l_get_tmpl_csr%ISOPEN THEN
3645 CLOSE l_get_tmpl_csr;
3646 END IF;
3647
3648 IF l_get_tmpl_csr%ISOPEN THEN
3649 CLOSE l_get_tmpl_csr;
3650 END IF;
3651
3652 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
3653 CLOSE l_get_allwd_tmp_usages_csr;
3654 END IF;
3655
3656 IF l_check_tmp_usage_csr%ISOPEN THEN
3657 CLOSE l_check_tmp_usage_csr;
3658 END IF;
3659
3660 IF l_lock_usg_csr%ISOPEN THEN
3661 CLOSE l_lock_usg_csr;
3662 END IF;
3663
3664 IF l_get_usage_csr%ISOPEN THEN
3665 CLOSE l_get_usage_csr;
3666 END IF;
3667
3668 ROLLBACK TO g_copy_tc_pvt;
3669 x_return_status := G_RET_STS_ERROR ;
3670 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
3671
3672 WHEN FND_API.G_EXC_ERROR THEN
3673
3674 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3675 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving copy_tc: OKC_API.G_EXCEPTION_ERROR Exception');
3676 END IF;
3677
3678 IF l_get_doc_type_name_csr%ISOPEN THEN
3679 CLOSE l_get_doc_type_name_csr;
3680 END IF;
3681
3682 IF l_get_tmpl_csr%ISOPEN THEN
3683 CLOSE l_get_tmpl_csr;
3684 END IF;
3685
3686 IF l_get_tmpl_csr%ISOPEN THEN
3687 CLOSE l_get_tmpl_csr;
3688 END IF;
3689
3690 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
3691 CLOSE l_get_allwd_tmp_usages_csr;
3692 END IF;
3693
3694 IF l_check_tmp_usage_csr%ISOPEN THEN
3695 CLOSE l_check_tmp_usage_csr;
3696 END IF;
3697
3698 IF l_lock_usg_csr%ISOPEN THEN
3699 CLOSE l_lock_usg_csr;
3700 END IF;
3701
3702 IF l_get_usage_csr%ISOPEN THEN
3703 CLOSE l_get_usage_csr;
3704 END IF;
3705
3706 ROLLBACK TO g_copy_tc_pvt;
3707 x_return_status := G_RET_STS_ERROR ;
3708 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3709
3710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3711 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3712 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_tc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3713 END IF;
3714
3715 IF l_get_doc_type_name_csr%ISOPEN THEN
3716 CLOSE l_get_doc_type_name_csr;
3717 END IF;
3718
3719 IF l_get_tmpl_csr%ISOPEN THEN
3720 CLOSE l_get_tmpl_csr;
3721 END IF;
3722
3723 IF l_get_tmpl_csr%ISOPEN THEN
3724 CLOSE l_get_tmpl_csr;
3725 END IF;
3726
3727 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
3728 CLOSE l_get_allwd_tmp_usages_csr;
3729 END IF;
3730
3731 IF l_check_tmp_usage_csr%ISOPEN THEN
3732 CLOSE l_check_tmp_usage_csr;
3733 END IF;
3734
3735 IF l_lock_usg_csr%ISOPEN THEN
3736 CLOSE l_lock_usg_csr;
3737 END IF;
3738
3739 IF l_get_usage_csr%ISOPEN THEN
3740 CLOSE l_get_usage_csr;
3741 END IF;
3742
3743 ROLLBACK TO g_copy_tc_pvt;
3744 x_return_status := G_RET_STS_UNEXP_ERROR ;
3745 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3746
3747 WHEN OTHERS THEN
3748 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3749 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_tc because of EXCEPTION: '||sqlerrm);
3750 END IF;
3751
3752 IF l_get_doc_type_name_csr%ISOPEN THEN
3753 CLOSE l_get_doc_type_name_csr;
3754 END IF;
3755
3756 IF l_get_tmpl_csr%ISOPEN THEN
3757 CLOSE l_get_tmpl_csr;
3758 END IF;
3759
3760 IF l_get_tmpl_csr%ISOPEN THEN
3761 CLOSE l_get_tmpl_csr;
3762 END IF;
3763
3764 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
3765 CLOSE l_get_allwd_tmp_usages_csr;
3766 END IF;
3767
3768 IF l_check_tmp_usage_csr%ISOPEN THEN
3769 CLOSE l_check_tmp_usage_csr;
3770 END IF;
3771
3772 IF l_lock_usg_csr%ISOPEN THEN
3773 CLOSE l_lock_usg_csr;
3774 END IF;
3775
3776 ROLLBACK TO g_copy_tc_pvt;
3777 x_return_status := G_RET_STS_UNEXP_ERROR ;
3778 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3779 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3780 END IF;
3781 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3782
3783 END copy_tc;
3784
3785
3786 procedure copy_archived_doc(
3787 p_api_version IN NUMBER,
3788 p_init_msg_list IN VARCHAR2,
3789 p_commit IN VARCHAR2,
3790 p_source_doc_type IN VARCHAR2,
3791 p_source_doc_id IN NUMBER,
3792 p_source_version_number IN NUMBER,
3793 p_target_doc_type IN VARCHAR2,
3794 p_target_doc_id IN NUMBER,
3795 p_document_number IN VARCHAR2,
3796 p_allow_duplicates IN VARCHAR2,
3797 x_return_status OUT NOCOPY VARCHAR2,
3798 x_msg_data OUT NOCOPY VARCHAR2,
3799 x_msg_count OUT NOCOPY NUMBER
3800 ) IS
3801
3802 l_api_version CONSTANT NUMBER := 1;
3803 l_api_name CONSTANT VARCHAR2(30) := 'copy_archived_doc';
3804 l_dummy_var VARCHAR2(1) :='?';
3805 l_document_type VARCHAR2(30);
3806 l_document_id NUMBER;
3807 l_term_found VARCHAR2(1):= NULL;
3808
3809 CURSOR l_get_usage_csr IS
3810 SELECT * FROM OKC_TEMPLATE_USAGES_H
3811 WHERE DOCUMENT_TYPE=p_source_doc_type
3812 AND DOCUMENT_ID=p_source_doc_id
3813 AND MAJOR_VERSION=p_source_version_number;
3814
3815 CURSOR l_get_target_usage IS
3816 SELECT 'Y' FROM OKC_TEMPLATE_USAGES
3817 WHERE DOCUMENT_TYPE=p_target_doc_type
3818 AND DOCUMENT_ID=p_target_doc_id;
3819
3820 CURSOR l_get_variables_csr IS
3821 SELECT KART.ID CAT_ID,
3822 VAR.VARIABLE_CODE,
3823 VAR.VARIABLE_TYPE,
3824 VAR.EXTERNAL_YN,
3825 VAR.ATTRIBUTE_VALUE_SET_ID,
3826 VAR.VARIABLE_VALUE,
3827 VAR.VARIABLE_VALUE_ID,
3828 VAR.OVERRIDE_GLOBAL_YN,
3829 VAR.MR_VARIABLE_HTML,
3830 VAR.MR_VARIABLE_XML,
3831 BUS_VAR.MRV_FLAG
3832 FROM OKC_K_ART_VARIABLES_H VAR,
3833 OKC_K_ARTICLES_B KART,
3834 OKC_K_ARTICLES_BH KART1,
3835 OKC_BUS_VARIABLES_B BUS_VAR
3836 WHERE KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ID
3837 AND VAR.CAT_ID=KART1.ID
3838 AND KART.DOCUMENT_TYPE=p_target_doc_type
3839 AND KART.DOCUMENT_ID=p_target_doc_id
3840 AND KART1.DOCUMENT_TYPE=p_source_doc_type
3841 AND KART1.DOCUMENT_ID=p_source_doc_id
3842 AND KART1.MAJOR_VERSION = p_source_version_number
3843 AND KART.ORIG_SYSTEM_REFERENCE_CODE=G_COPY
3844 AND VAR.MAJOR_VERSION = p_source_version_number;
3845
3846 TYPE CatList IS TABLE OF OKC_K_ART_VARIABLES.CAT_ID%TYPE INDEX BY BINARY_INTEGER;
3847 TYPE VarList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_CODE%TYPE INDEX BY BINARY_INTEGER;
3848 TYPE VarTypeList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE INDEX BY BINARY_INTEGER;
3849 TYPE ExternalList IS TABLE OF OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE INDEX BY BINARY_INTEGER;
3850 TYPE ValSetList IS TABLE OF OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE INDEX BY BINARY_INTEGER;
3851 TYPE VarValList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE INDEX BY BINARY_INTEGER;
3852 TYPE VarIdList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE INDEX BY BINARY_INTEGER;
3853 TYPE OverrideGlobalYnList IS TABLE OF OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE INDEX BY BINARY_INTEGER;
3854 TYPE mrvariablehtml IS TABLE OF OKC_K_ART_VARIABLES.mr_variable_html%TYPE INDEX BY BINARY_INTEGER;
3855 TYPE mrvariablexml IS TABLE OF OKC_K_ART_VARIABLES.mr_variable_xml%TYPE INDEX BY BINARY_INTEGER;
3856 TYPE MRVFLAG IS TABLE OF OKC_BUS_VARIABLES_B.MRV_FLAG%TYPE INDEX BY BINARY_INTEGER;
3857
3858 cat_tbl CatList;
3859 var_tbl VarList;
3860 var_type_tbl VarTypeList;
3861 external_yn_tbl ExternalList;
3862 value_set_id_tbl ValSetList;
3863 var_value_tbl VarValList;
3864 var_value_id_tbl VarIdList;
3865 override_global_yn_tbl OverrideGlobalYnList;
3866 mr_variable_html_tbl mrvariablehtml;
3867 mr_variable_xml_tbl mrvariablexml;
3868 mrv_flag_tbl MRVFLAG;
3869
3870 l_usage_rec l_get_usage_csr%ROWTYPE;
3871
3872
3873
3874 BEGIN
3875
3876 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3877 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering OKC_TERMS_COPY_PVT.copy_archived_doc ');
3878 END IF;
3879
3880 -- Standard Start of API savepoint
3881 SAVEPOINT g_copy_archived_doc_pvt;
3882
3883 -- Standard call to check for call compatibility.
3884 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3886 END IF;
3887
3888 -- Initialize message list if p_init_msg_list is set to TRUE.
3889 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3890 FND_MSG_PUB.initialize;
3891 END IF;
3892
3893 -- Initialize API return status to success
3894 x_return_status := FND_API.G_RET_STS_SUCCESS;
3895
3896 /* Create Template Usage Record */
3897
3898 OPEN l_get_usage_csr;
3899 FETCH l_get_usage_csr INTO l_usage_rec;
3900 IF (l_get_usage_csr%NOTFOUND ) THEN
3901 CLOSE l_get_usage_csr;
3902 RAISE NO_DATA_FOUND;
3903 END IF;
3904 CLOSE l_get_usage_csr;
3905
3906 OPEN l_get_target_usage;
3907 FETCH l_get_target_usage into l_term_found;
3908 CLOSE l_get_target_usage;
3909
3910 IF l_usage_rec.config_header_id IS NOT NULL THEN
3911
3912 /* Call Copy Config API provided by Contract Expert Team */
3913
3914 NULL;
3915
3916 /* After Copy Set. l_usage_rec.config_header_id and l_usage_rec.Config_revision_number to values returned from Above API */
3917
3918 END IF;
3919
3920 IF ( p_allow_duplicates <>'Y' and l_term_found='Y') OR l_term_found IS NULL THEN
3921 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3922 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Create usage record ');
3923 END IF;
3924
3925 OKC_TEMPLATE_USAGES_GRP.create_template_usages(
3926 p_api_version => 1,
3927 p_init_msg_list => FND_API.G_FALSE,
3928 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3929 p_commit => FND_API.G_FALSE,
3930 x_return_status => x_return_status,
3931 x_msg_data => x_msg_data,
3932 x_msg_count => x_msg_count,
3933 p_document_type => p_target_doc_type,
3934 p_document_id => p_target_doc_id,
3935 p_template_id => l_usage_rec.template_id,
3936 p_doc_numbering_scheme => l_usage_rec.doc_numbering_scheme,
3937 p_document_number => p_document_number,
3938 p_article_effective_date => sysdate,
3939 p_config_header_id => l_usage_rec.config_header_id,
3940 p_config_revision_number => l_usage_rec.config_revision_number,
3941 p_valid_config_yn => l_usage_rec.valid_config_yn,
3942 x_document_type => l_document_type,
3943 x_document_id => l_document_id,
3944 -- Additional fix for bug# 4116433.
3945 p_approval_abstract_text => l_usage_rec.approval_abstract_text,
3946 p_contract_source_code => l_usage_rec.contract_source_code,
3947 p_authoring_party_code => l_usage_rec.authoring_party_code,
3948 p_source_change_allowed_flag => l_usage_rec.source_change_allowed_flag,
3949 p_autogen_deviations_flag => l_usage_rec.autogen_deviations_flag
3950 );
3951 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3952 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:Finished usage record creation.Return Status'||x_return_status);
3953 END IF;
3954
3955 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3957 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3958 RAISE FND_API.G_EXC_ERROR ;
3959 END IF;
3960 END IF; -- IF ( p_allow_duplicates <>'Y' and l_term_found='Y') OR l_term_found is NULL THEN
3961
3962 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3963 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Calling copy_section');
3964 END IF;
3965
3966 copy_sections(
3967 p_target_doc_type => p_target_doc_type,
3968 p_source_doc_type => p_source_doc_type,
3969 p_target_doc_id => p_target_doc_id,
3970 p_source_doc_id => p_source_doc_id,
3971 p_source_version_number => p_source_version_number,
3972 p_copy_from_archive => 'Y',
3973 x_return_status => x_return_status,
3974 x_msg_data => x_msg_data,
3975 x_msg_count => x_msg_count);
3976
3977 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3978 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:Finished copy_section. Return Status '||x_return_status);
3979 END IF;
3980
3981 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3983 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3984 RAISE FND_API.G_EXC_ERROR ;
3985 END IF;
3986
3987 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3988 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Entering copy_articles. ');
3989 END IF;
3990
3991 copy_articles(
3992 p_target_doc_type => p_target_doc_type,
3993 p_source_doc_type => p_source_doc_type,
3994 p_target_doc_id => p_target_doc_id,
3995 p_source_doc_id => p_source_doc_id,
3996 p_keep_version => 'N',
3997 p_article_effective_date => Null,
3998 p_source_version_number => p_source_version_number,
3999 p_copy_from_archive =>'Y',
4000 x_return_status => x_return_status,
4001 x_msg_data => x_msg_data,
4002 x_msg_count => x_msg_count);
4003
4004 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4005 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
4006 END IF;
4007
4008 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4010 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4011 RAISE FND_API.G_EXC_ERROR ;
4012 END IF;
4013
4014 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4015 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Copying article variables');
4016 END IF;
4017
4018 -- Bulk collecting
4019 OPEN l_get_variables_csr;
4020 FETCH l_get_variables_csr BULK COLLECT INTO cat_tbl,
4021 var_tbl,
4022 var_type_tbl,
4023 external_yn_tbl,
4024 value_set_id_tbl,
4025 var_value_tbl,
4026 var_value_id_tbl,
4027 override_global_yn_tbl,
4028 mr_variable_html_tbl,
4029 mr_variable_xml_tbl,
4030 MRV_flag_tbl;
4031 CLOSE l_get_variables_csr;
4032
4033 -- Bulk inserting
4034 IF cat_tbl.COUNT > 0 THEN
4035 FORALL i IN cat_tbl.FIRST..cat_tbl.LAST
4036 INSERT INTO OKC_K_ART_VARIABLES(cat_id,
4037 variable_code,
4038 variable_type,
4039 external_yn,
4040 attribute_value_set_id,
4041 variable_value,
4042 variable_value_id,
4043 override_global_yn,
4044 object_version_number,
4045 creation_date,
4046 created_by,
4047 last_update_date,
4048 last_updated_by,
4049 last_update_login,
4050 mr_variable_html,
4051 mr_variable_xml
4052 )
4053 VALUES (cat_tbl(i),
4054 var_tbl(i),
4055 var_type_tbl(i),
4056 external_yn_tbl(i),
4057 value_set_id_tbl(i),
4058 var_value_tbl(i),
4059 var_value_id_tbl(i),
4060 override_global_yn_tbl(i),
4061 1,
4062 sysdate,
4063 Fnd_Global.User_Id,
4064 sysdate,
4065 Fnd_Global.User_Id,
4066 Fnd_Global.Login_Id,
4067 mr_variable_html_tbl(i),
4068 mr_variable_xml_tbl(i));
4069
4070 FOR i IN cat_tbl.FIRST..cat_tbl.LAST
4071 LOOP
4072 IF MRV_flag_tbl(i) = 'Y' THEN
4073 OKC_K_ART_VARIABLES_PVT.restore_mrv_uda_data_version(cat_tbl(i),p_source_version_number);
4074 END IF;
4075 END LOOP;
4076 END IF;
4077
4078 IF FND_API.To_Boolean( p_commit ) THEN
4079 COMMIT WORK;
4080 END IF;
4081
4082 -- Standard call to get message count and if count is 1, get message info.
4083 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4084
4085 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4086 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Leaving copy_archived_doc');
4087 END IF;
4088
4089 EXCEPTION
4090
4091 WHEN NO_DATA_FOUND THEN
4092 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4093 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_archived_doc No Terms Data in Source');
4094 END IF;
4095 null;
4096
4097 WHEN FND_API.G_EXC_ERROR THEN
4098
4099 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4100 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving copy_archived_doc: OKC_API.G_EXCEPTION_ERROR Exception');
4101 END IF;
4102
4103 IF l_get_usage_csr%ISOPEN THEN
4104 CLOSE l_get_usage_csr;
4105 END IF;
4106
4107 IF l_get_variables_csr%ISOPEN THEN
4108 CLOSE l_get_variables_csr;
4109 END IF;
4110
4111 ROLLBACK TO g_copy_archived_doc_pvt;
4112 x_return_status := G_RET_STS_ERROR ;
4113 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4114
4115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4116
4117 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4118 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_archived_doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4119 END IF;
4120
4121 IF l_get_usage_csr%ISOPEN THEN
4122 CLOSE l_get_usage_csr;
4123 END IF;
4124
4125 IF l_get_variables_csr%ISOPEN THEN
4126 CLOSE l_get_variables_csr;
4127 END IF;
4128
4129 ROLLBACK TO g_copy_archived_doc_pvt;
4130 x_return_status := G_RET_STS_UNEXP_ERROR ;
4131 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4132
4133 WHEN OTHERS THEN
4134
4135 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4136 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_archived_doc because of EXCEPTION: '||sqlerrm);
4137 END IF;
4138
4139 IF l_get_usage_csr%ISOPEN THEN
4140 CLOSE l_get_usage_csr;
4141 END IF;
4142
4143 IF l_get_variables_csr%ISOPEN THEN
4144 CLOSE l_get_variables_csr;
4145 END IF;
4146
4147 ROLLBACK TO g_copy_archived_doc_pvt;
4148 x_return_status := G_RET_STS_UNEXP_ERROR ;
4149
4150 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4151 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4152 END IF;
4153
4154 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4155
4156 END copy_archived_doc;
4157
4158 END OKC_TERMS_COPY_PVT;