[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_COPY_PVT
Source
1 PACKAGE BODY OKC_TERMS_COPY_PVT AS
2 /* $Header: OKCVDCPB.pls 120.4.12000000.2 2007/07/17 17:53:04 rvohra 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 FUNCTION get_variable_value_id (
40 p_cat_id IN NUMBER,
41 p_variable_code IN VARCHAR2)
42 RETURN VARCHAR2 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'get_variable_value_id';
44 CURSOR csr_target_dtls IS
45 SELECT NVL(standard_yn,'N') standard_flag,
46 kart.article_version_id,
47 kart.ORIG_SYSTEM_REFERENCE_ID1
48 FROM okc_articles_all lib,
49 okc_k_articles_b kart
50 WHERE lib.article_id = kart.sav_sae_id
51 AND kart.id= p_cat_id;
52
53 CURSOR csr_source_dtls(p_cat_id IN NUMBER) IS
54 SELECT article_version_id
55 FROM okc_k_articles_b
56 WHERE id = p_cat_id;
57
58 CURSOR csr_source_value_id(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
59 SELECT VAR.VARIABLE_VALUE_ID
60 FROM OKC_K_ART_VARIABLES VAR
61 WHERE VAR.CAT_ID = p_cat_id
62 AND VAR.VARIABLE_CODE = p_variable_code;
63
64 l_standard_flag VARCHAR2(1);
65 l_ORIG_SYSTEM_REFERENCE_ID1 VARCHAR2(1000);
66 l_target_article_version_id NUMBER;
67 l_source_article_version_id NUMBER;
68 l_source_value_id VARCHAR2(1000):= NULL;
69
70 BEGIN
71 OPEN csr_target_dtls;
72 FETCH csr_target_dtls INTO l_standard_flag, l_target_article_version_id, l_ORIG_SYSTEM_REFERENCE_ID1;
73 CLOSE csr_target_dtls;
74
75
76 -- bug 3369336
77 -- copy variable values if the variable exists in the target doc article
78 -- without comparing version or if it was non standard (bug 3397895 )
79
80 OPEN csr_source_value_id(p_cat_id=>l_ORIG_SYSTEM_REFERENCE_ID1,p_variable_code=>p_variable_code);
81 FETCH csr_source_value_id INTO l_source_value_id;
82 CLOSE csr_source_value_id;
83
84 RETURN l_source_value_id;
85
86 END get_variable_value_id;
87
88 FUNCTION get_orig_var_val (
89 p_cat_id IN NUMBER,
90 p_variable_code IN VARCHAR2,
91 p_source_doc_id IN NUMBER,
92 p_source_doc_type IN VARCHAR2,
93 p_value_type IN VARCHAR2)
94 RETURN VARCHAR2 IS
95 l_api_name CONSTANT VARCHAR2(30) := 'get_orig_var_val';
96 CURSOR csr_orig_target_dtls IS
97 SELECT kart1.id
98 FROM okc_articles_all lib,
99 okc_k_articles_b kart,
100 okc_k_articles_b kart1
101 WHERE lib.article_id = kart.sav_sae_id
102 AND kart.id= p_cat_id
103 AND kart1.document_id = p_source_doc_id
104 AND kart1.document_type = p_source_doc_type
105 AND kart1.orig_system_reference_id1 = kart.orig_system_reference_id1;
106
107 CURSOR csr_source_value_id(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
108 SELECT var.variable_value,
109 var.variable_value_id
110 FROM okc_k_art_variables var
111 WHERE var.cat_id = p_cat_id
112 AND var.variable_code = p_variable_code;
113
114 l_standard_flag VARCHAR2(1);
115 l_id1 VARCHAR2(2000);
116 l_var_value_id okc_k_art_variables.variable_value_id%TYPE := NULL;
117 l_var_value okc_k_art_variables.variable_value%TYPE := NULL;
118
119 BEGIN
120 OPEN csr_orig_target_dtls;
121 FETCH csr_orig_target_dtls INTO l_id1;
122 CLOSE csr_orig_target_dtls;
123
124 OPEN csr_source_value_id(p_cat_id=>l_id1,p_variable_code=>p_variable_code);
125 FETCH csr_source_value_id INTO l_var_value,l_var_value_id;
126 CLOSE csr_source_value_id;
127
128 IF p_value_type = 'ID' THEN
129 RETURN l_var_value_id;
130 ELSE
131 RETURN l_var_value;
132 END IF;
133
134 END get_orig_var_val;
135
136 FUNCTION get_variable_value (
137 p_cat_id IN NUMBER,
138 p_variable_code IN VARCHAR2)
139 RETURN VARCHAR2 IS
140 l_api_name CONSTANT VARCHAR2(30) := 'get_variable_value';
141 CURSOR csr_target_dtls IS
142 SELECT NVL(standard_yn,'N') standard_flag,
143 kart.article_version_id,
144 kart.ORIG_SYSTEM_REFERENCE_ID1
145 FROM okc_articles_all lib,
146 okc_k_articles_b kart
147 WHERE lib.article_id = kart.sav_sae_id
148 AND kart.id= p_cat_id;
149
150 CURSOR csr_source_dtls(p_cat_id IN NUMBER) IS
151 SELECT article_version_id
152 FROM okc_k_articles_b
153 WHERE id = p_cat_id;
154
155 CURSOR csr_source_value(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) IS
156 SELECT VAR.VARIABLE_VALUE
157 FROM OKC_K_ART_VARIABLES VAR
158 WHERE VAR.CAT_ID = p_cat_id
159 AND VAR.VARIABLE_CODE = p_variable_code;
160
161 l_standard_flag VARCHAR2(1);
162 l_ORIG_SYSTEM_REFERENCE_ID1 VARCHAR2(1000);
163 l_target_article_version_id NUMBER;
164 l_source_article_version_id NUMBER;
165 l_source_value VARCHAR2(2000):= NULL;
166
167 BEGIN
168 OPEN csr_target_dtls;
169 FETCH csr_target_dtls INTO l_standard_flag, l_target_article_version_id, l_ORIG_SYSTEM_REFERENCE_ID1;
170 CLOSE csr_target_dtls;
171
172 -- bug 3369336
173 -- copy variable values if the variable exists in the target doc article
174 -- without comparing version or if it was non standard (bug 3397895 )
175
176 OPEN csr_source_value(p_cat_id=>l_ORIG_SYSTEM_REFERENCE_ID1,p_variable_code=>p_variable_code);
177 FETCH csr_source_value INTO l_source_value;
178 CLOSE csr_source_value;
179
180 RETURN l_source_value;
181
182 END get_variable_value;
183
184
185 procedure copy_article_variables(
186 p_target_doc_type IN VARCHAR2,
187 p_source_doc_type IN VARCHAR2,
188 p_target_doc_id IN NUMBER,
189 p_source_doc_id IN NUMBER,
190 p_get_from_library IN VARCHAR2,
191 p_keep_orig_ref IN VARCHAR2 := 'N',
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_data OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER
195 ) IS
196 -- This cursor will get variable code and values either from library
197 l_api_name CONSTANT VARCHAR2(30) := 'copy_article_variables';
198 CURSOR l_get_lib_variables_csr IS
199 SELECT KART.ID CAT_ID,
200 VAR.VARIABLE_CODE,
201 BUSVAR.VARIABLE_TYPE,
202 BUSVAR.EXTERNAL_YN,
203 BUSVAR.VALUE_SET_ID,
204 DECODE(p_keep_orig_ref,'Y',get_orig_var_val(
205 KART.ID,VAR.VARIABLE_CODE,
206 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'VALUE'),
207 get_variable_value(KART.ID,VAR.VARIABLE_CODE)) VARIABLE_VALUE,
208 DECODE(p_keep_orig_ref,'Y',get_orig_var_val(
209 KART.ID,VAR.VARIABLE_CODE,
210 P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'ID'),
211 get_variable_value_id(KART.ID,VAR.VARIABLE_CODE)) VARIABLE_VALUE_ID,
212 'N' OVERRIDE_GLOBAL_YN
213 FROM OKC_ARTICLE_VARIABLES VAR,
214 OKC_K_ARTICLES_B KART,
215 OKC_BUS_VARIABLES_B BUSVAR
216 WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
217 AND KART.DOCUMENT_TYPE=p_target_doc_type
218 AND KART.DOCUMENT_ID=p_target_doc_id
219 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE
220 AND not exists ( select 'x' from okc_k_art_variables where cat_id=kart.id);
221
222 -- This cursor will get variable code and values fromr okc_k_art_variables
223 CURSOR l_get_variables_csr IS
224 SELECT KART.ID CAT_ID,
225 VAR.VARIABLE_CODE,
226 BUSVAR.VARIABLE_TYPE,
227 BUSVAR.EXTERNAL_YN,
228 BUSVAR.VALUE_SET_ID,
229 VAR.VARIABLE_VALUE,
230 VAR.VARIABLE_VALUE_ID,
231 VAR.OVERRIDE_GLOBAL_YN
232 FROM OKC_K_ART_VARIABLES VAR,
233 OKC_K_ARTICLES_B KART,
234 OKC_K_ARTICLES_B KART1,
235 OKC_BUS_VARIABLES_B BUSVAR
236 WHERE KART.DOCUMENT_TYPE=p_target_doc_type
237 AND KART.DOCUMENT_ID=p_target_doc_id
238 AND KART1.DOCUMENT_TYPE=p_source_doc_type
239 AND KART1.DOCUMENT_ID=p_source_doc_id
240 AND KART.ORIG_SYSTEM_REFERENCE_CODE=G_COPY
241 AND ((KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ID AND P_KEEP_ORIG_REF = 'N') OR
242 (KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ORIG_SYSTEM_REFERENCE_ID1 AND P_KEEP_ORIG_REF = 'Y'))
243 AND KART1.ID=VAR.CAT_ID
244 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE ;
245
246 TYPE CatList IS TABLE OF OKC_K_ART_VARIABLES.CAT_ID%TYPE INDEX BY BINARY_INTEGER;
247 TYPE VarList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_CODE%TYPE INDEX BY BINARY_INTEGER;
248 TYPE VarTypeList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE INDEX BY BINARY_INTEGER;
249 TYPE ExternalList IS TABLE OF OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE INDEX BY BINARY_INTEGER;
250 TYPE ValSetList IS TABLE OF OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE INDEX BY BINARY_INTEGER;
251 TYPE VarValList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE INDEX BY BINARY_INTEGER;
252 TYPE VarIdList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE INDEX BY BINARY_INTEGER;
253 TYPE OverrideGlobalYnList IS TABLE OF OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE INDEX BY BINARY_INTEGER;
254
255 cat_tbl CatList;
256 var_tbl VarList;
257 var_type_tbl VarTypeList;
258 external_yn_tbl ExternalList;
259 value_set_id_tbl ValSetList;
260 var_value_tbl VarValList;
261 var_value_id_tbl VarIdList;
262 override_global_yn_tbl OverrideGlobalYnList;
263
264 BEGIN
265
266 x_return_status := G_RET_STS_SUCCESS;
267
268 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.copy_article_variables');
270 END IF;
271 IF p_get_from_library='Y' THEN
272 -- Bulk collecting
273 OPEN l_get_lib_variables_csr;
274 FETCH l_get_lib_variables_csr BULK COLLECT INTO cat_tbl,
275 var_tbl,
276 var_type_tbl,
277 external_yn_tbl,
278 value_set_id_tbl,
279 var_value_tbl,
280 var_value_id_tbl,
281 override_global_yn_tbl;
282 CLOSE l_get_lib_variables_csr;
283
284 ELSE
285 -- Bulk collecting
286 OPEN l_get_variables_csr;
287 FETCH l_get_variables_csr BULK COLLECT INTO cat_tbl,
288 var_tbl,
289 var_type_tbl,
290 external_yn_tbl,
291 value_set_id_tbl,
292 var_value_tbl,
293 var_value_id_tbl,
294 override_global_yn_tbl;
295 CLOSE l_get_variables_csr;
296 END IF;
297
298 -- Bulk inserting
299 IF cat_tbl.COUNT>0 THEN
300 FORALL i IN cat_tbl.FIRST..cat_tbl.LAST
301 INSERT INTO OKC_K_ART_VARIABLES(cat_id,
302 variable_code,
303 variable_type,
304 external_yn,
305 attribute_value_set_id,
306 variable_value,
307 variable_value_id,
308 override_global_yn,
309 object_version_number,
310 creation_date,
311 created_by,
312 last_update_date,
313 last_updated_by,
314 last_update_login)
315 VALUES (cat_tbl(i),
316 var_tbl(i),
317 var_type_tbl(i),
318 external_yn_tbl(i),
319 value_set_id_tbl(i),
320 var_value_tbl(i),
321 var_value_id_tbl(i),
322 override_global_yn_tbl(i),
323 1,
324 sysdate,
325 Fnd_Global.User_Id,
326 sysdate,
327 Fnd_Global.User_Id,
328 Fnd_Global.Login_Id);
329
330 END IF;
331
332 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
333
334 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
335 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving copy_article_variables '||x_return_status);
336 END IF;
337
338 EXCEPTION
339 WHEN FND_API.G_EXC_ERROR THEN
340
341 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_article_variables:FND_API.G_EXC_ERROR Exception');
343 END IF;
344
345 IF l_get_variables_csr%ISOPEN THEN
346 CLOSE l_get_variables_csr;
347 END IF;
348 x_return_status := G_RET_STS_ERROR;
349 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
350
351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
352
353 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_article_variables:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
355 END IF;
356
357 IF l_get_variables_csr%ISOPEN THEN
358 CLOSE l_get_variables_csr;
359 END IF;
360 x_return_status := G_RET_STS_UNEXP_ERROR;
361 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
362
363 WHEN OTHERS THEN
364
365 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_article_variables because of EXCEPTION: '||sqlerrm);
367 END IF;
368
369 IF l_get_variables_csr%ISOPEN THEN
370 CLOSE l_get_variables_csr;
371 END IF;
372 okc_Api.Set_Message(p_app_name => G_APP_NAME,
373 p_msg_name => G_UNEXPECTED_ERROR,
374 p_token1 => G_SQLCODE_TOKEN,
375 p_token1_value => sqlcode,
376 p_token2 => G_SQLERRM_TOKEN,
377 p_token2_value => sqlerrm);
378 x_return_status := G_RET_STS_UNEXP_ERROR;
379 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
380
381 END copy_article_variables;
382
383
384 procedure copy_articles(
385 p_target_doc_type IN VARCHAR2,
386 p_source_doc_type IN VARCHAR2,
387 p_target_doc_id IN NUMBER,
388 P_source_doc_id IN NUMBER,
389 p_keep_version IN VARCHAR2,
390 p_article_effective_date IN DATE,
391 p_source_version_number IN NUMBER := NULL,
392 p_copy_from_archive IN VARCHAR2 := 'N',
393 p_keep_orig_ref IN VARCHAR2 := 'N',
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_data OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER) IS
397 l_api_name CONSTANT VARCHAR2(30) := 'copy_articles';
398 l_prov_allowed VARCHAR2(1) ;
399 l_discard Boolean;
400 l_standard_yn VARCHAR2(1) ;
401 l_global_yn VARCHAR2(1) ;
402 l_org_id OKC_TERMS_TEMPLATES_ALL.ORG_ID%TYPE;
403 l_art_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
404 k NUMBER := 0;
405
406 TYPE SavSaeIdList IS TABLE OF OKC_K_ARTICLES_B.SAV_SAE_ID%TYPE INDEX BY BINARY_INTEGER;
407 TYPE AttributeCategoryList IS TABLE OF OKC_K_ARTICLES_B.ATTRIBUTE_CATEGORY%TYPE INDEX BY BINARY_INTEGER;
408 TYPE AttributeList IS TABLE OF OKC_K_ARTICLES_B.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
409 TYPE SourceFlagList IS TABLE OF OKC_K_ARTICLES_B.SOURCE_FLAG%TYPE INDEX BY BINARY_INTEGER;
410 TYPE ArticleVersionIdList IS TABLE OF OKC_K_ARTICLES_B.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
411 TYPE MandatoryYnList IS TABLE OF OKC_K_ARTICLES_B.MANDATORY_YN%TYPE INDEX BY BINARY_INTEGER;
412 TYPE ChangeNonStdYnList IS TABLE OF OKC_K_ARTICLES_B.CHANGE_NONSTD_YN%TYPE INDEX BY BINARY_INTEGER;
413 TYPE ScnIdList IS TABLE OF OKC_K_ARTICLES_B.SCN_ID%TYPE INDEX BY BINARY_INTEGER;
414 TYPE OrigSystemReferenceId1List IS TABLE OF OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE INDEX BY BINARY_INTEGER;
415 TYPE LabelList IS TABLE OF OKC_K_ARTICLES_B.LABEL%TYPE INDEX BY BINARY_INTEGER;
416 TYPE DisplaySequenceList IS TABLE OF OKC_K_ARTICLES_B.DISPLAY_SEQUENCE%TYPE INDEX BY BINARY_INTEGER;
417 TYPE RefArticleIdList IS TABLE OF OKC_K_ARTICLES_B.REF_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
418 TYPE RefArticleVersionIdList IS TABLE OF OKC_K_ARTICLES_B.REF_ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
419 TYPE OrigArticleIdList IS TABLE OF OKC_K_ARTICLES_B.ORIG_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
420
421 sav_sae_tbl SavSaeIdList;
422 Attribute_category_tbl AttributeCategoryList;
423 Attribute1_tbl AttributeList;
424 Attribute2_tbl AttributeList;
425 Attribute3_tbl AttributeList;
426 Attribute4_tbl AttributeList;
427 Attribute5_tbl AttributeList;
428 Attribute6_tbl AttributeList;
429 Attribute7_tbl AttributeList;
430 Attribute8_tbl AttributeList;
431 Attribute9_tbl AttributeList;
432 Attribute10_tbl AttributeList;
433 Attribute11_tbl AttributeList;
434 Attribute12_tbl AttributeList;
435 Attribute13_tbl AttributeList;
436 Attribute14_tbl AttributeList;
437 Attribute15_tbl AttributeList;
438 Ref_article_id_tbl RefArticleIdList;
439 Ref_article_version_id_tbl RefArticleVersionIdList;
440 orig_article_id_tbl OrigArticleIdList;
441
442 Source_flag_tbl SourceFlagList;
443 Article_Version_tbl ArticleVersionIdList;
444 Change_nonstd_yn_tbl ChangeNonStdYnList;
445 Scn_id_tbl ScnIdList;
446 Orig_System_Reference_id1_tbl OrigSystemReferenceId1List;
447 Mandatory_yn_tbl MandatoryYnList;
448 Label_tbl LabelList;
449 Display_sequence_tbl DisplaySequenceList;
450
451 sav_sae_tbl1 SavSaeIdList;
452 Attribute_category_tbl1 AttributeCategoryList;
453 Attribute1_tbl1 AttributeList;
454 Attribute2_tbl1 AttributeList;
455 Attribute3_tbl1 AttributeList;
456 Attribute4_tbl1 AttributeList;
457 Attribute5_tbl1 AttributeList;
458 Attribute6_tbl1 AttributeList;
459 Attribute7_tbl1 AttributeList;
460 Attribute8_tbl1 AttributeList;
461 Attribute9_tbl1 AttributeList;
462 Attribute10_tbl1 AttributeList;
463 Attribute11_tbl1 AttributeList;
464 Attribute12_tbl1 AttributeList;
465 Attribute13_tbl1 AttributeList;
466 Attribute14_tbl1 AttributeList;
467 Attribute15_tbl1 AttributeList;
468 Ref_article_id_tbl1 RefArticleIdList;
469 Ref_article_version_id_tbl1 RefArticleVersionIdList;
470 orig_article_id_tbl1 OrigArticleIdList;
471
472 Source_flag_tbl1 SourceFlagList;
473 Article_Version_tbl1 ArticleVersionIdList;
474 Change_nonstd_yn_tbl1 ChangeNonStdYnList;
475 Scn_id_tbl1 ScnIdList;
476 Orig_System_Reference_id1_tbl1 OrigSystemReferenceId1List;
477 Mandatory_yn_tbl1 MandatoryYnList;
478 Label_tbl1 LabelList;
479 Display_sequence_tbl1 DisplaySequenceList;
480
481 CURSOR l_get_prov_csr IS
482 SELECT nvl(PROVISION_ALLOWED_YN,'Y') FROM OKC_BUS_DOC_TYPES_B
483 WHERE DOCUMENT_TYPE=p_target_doc_type;
484
485 CURSOR l_get_std_csr(b_article_id NUMBER) IS
486 SELECT STANDARD_YN,ARTICLE_TITLE FROM OKC_ARTICLES_ALL
487 WHERE article_id=b_article_id;
488
489 CURSOR l_get_global_csr IS
490 SELECT global_flag FROM OKC_TERMS_TEMPLATES_ALL
491 WHERE template_id=p_source_doc_id;
492
493 CURSOR l_get_org_csr IS
494 SELECT org_id FROM OKC_TERMS_TEMPLATES_ALL
495 WHERE template_id=p_target_doc_id;
496
497 CURSOR l_get_latest_article_csr(b_article_id NUMBER) IS
498 SELECT article_version_id FROM OKC_ARTICLE_VERSIONS
499 WHERE article_id= b_article_id
500 AND article_status in ('ON_HOLD','APPROVED')
501 AND nvl(p_article_effective_date,sysdate) >= Start_date
502 AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1);
503
504 CURSOR l_get_max_article_csr(b_article_id NUMBER) IS
505 SELECT article_version_id FROM OKC_ARTICLE_VERSIONS
506 WHERE article_id= b_article_id
507 AND article_status in ('ON_HOLD','APPROVED')
508 AND start_date = (select max(start_date) FROM OKC_ARTICLE_VERSIONS
509 WHERE article_id= b_article_id
510 AND article_status in ('ON_HOLD','APPROVED') );
511
512 CURSOR l_get_no_std_ref_csr(b_version_ID NUMBER) IS
513 SELECT VERS2.ARTICLE_ID,VERS2.ARTICLE_VERSION_ID
514 FROM OKC_ARTICLE_VERSIONS VERS1,OKC_ARTICLE_VERSIONS VERS2
515 WHERE VERS1.ARTICLE_VERSION_ID=b_version_id
516 AND VERS2.ARTICLE_VERSION_ID=VERS1.STD_ARTICLE_VERSION_ID;
517
518 CURSOR l_get_art_csr IS
519 SELECT
520 SAV_SAE_ID,
521 KART.ATTRIBUTE_CATEGORY,
522 KART.ATTRIBUTE1,
523 KART.ATTRIBUTE2,
524 KART.ATTRIBUTE3,
525 KART.ATTRIBUTE4,
526 KART.ATTRIBUTE5,
527 KART.ATTRIBUTE6,
528 KART.ATTRIBUTE7,
529 KART.ATTRIBUTE8,
530 KART.ATTRIBUTE9,
531 KART.ATTRIBUTE10,
532 KART.ATTRIBUTE11,
533 KART.ATTRIBUTE12,
534 KART.ATTRIBUTE13,
535 KART.ATTRIBUTE14,
536 KART.ATTRIBUTE15,
537 SOURCE_FLAG,
538 ARTICLE_VERSION_ID,
539 CHANGE_NONSTD_YN,
540 SCN.ID SCN_ID,
541 DECODE(P_KEEP_ORIG_REF,'Y',KART.ORIG_SYSTEM_REFERENCE_ID1,KART.ID) ORIG_SYSTEM_REFERENCE_ID1,
542 MANDATORY_YN,
543 KART.LABEL,
544 DISPLAY_SEQUENCE,
545 ref_article_id,
546 ref_article_version_id,
547 DECODE(p_source_doc_type,G_TEMPLATE_DOC_TYPE,sav_sae_id,orig_article_id) orig_article_id
548 FROM OKC_K_ARTICLES_B KART,
549 OKC_SECTIONS_B SCN
550 WHERE KART.DOCUMENT_TYPE=p_source_doc_type
551 AND KART.DOCUMENT_ID=p_source_doc_id
552 AND SCN.DOCUMENT_TYPE = p_target_doc_type
553 AND SCN.DOCUMENT_ID =p_target_doc_id
554 AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
555 AND SCN.ORIG_SYSTEM_REFERENCE_ID1=KART.SCN_ID
556 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
557 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
558
559 CURSOR l_get_orig_art_csr IS
560 SELECT
561 KART.SAV_SAE_ID,
562 KART.ATTRIBUTE_CATEGORY,
563 KART.ATTRIBUTE1,
564 KART.ATTRIBUTE2,
565 KART.ATTRIBUTE3,
566 KART.ATTRIBUTE4,
567 KART.ATTRIBUTE5,
568 KART.ATTRIBUTE6,
569 KART.ATTRIBUTE7,
570 KART.ATTRIBUTE8,
571 KART.ATTRIBUTE9,
572 KART.ATTRIBUTE10,
573 KART.ATTRIBUTE11,
574 KART.ATTRIBUTE12,
575 KART.ATTRIBUTE13,
576 KART.ATTRIBUTE14,
577 KART.ATTRIBUTE15,
578 KART.SOURCE_FLAG,
579 KART.ARTICLE_VERSION_ID,
580 KART.CHANGE_NONSTD_YN,
581 SCN.ID SCN_ID,
582 KART.ORIG_SYSTEM_REFERENCE_ID1 ORIG_SYSTEM_REFERENCE_ID1,
583 KART.MANDATORY_YN,
584 KART.LABEL,
585 KART.DISPLAY_SEQUENCE,
586 KART.ref_article_id,
587 KART.ref_article_version_id,
588 DECODE(p_source_doc_type,G_TEMPLATE_DOC_TYPE,KART.sav_sae_id,KART.orig_article_id) orig_article_id
589 FROM OKC_K_ARTICLES_B KART,
590 OKC_SECTIONS_B SCN,
591 OKC_SECTIONS_B SCN1,
592 OKC_K_ARTICLES_B KART1
593 WHERE KART.DOCUMENT_TYPE= p_source_doc_type
594 AND KART.DOCUMENT_ID= p_source_doc_id
595 AND SCN.DOCUMENT_TYPE = p_target_doc_type
596 AND SCN.DOCUMENT_ID = p_target_doc_id
597 AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
598 AND SCN1.DOCUMENT_TYPE = p_source_doc_type
599 AND SCN1.DOCUMENT_ID = p_source_doc_id
600 AND SCN1.ORIG_SYSTEM_REFERENCE_ID1 = SCN.ORIG_SYSTEM_REFERENCE_ID1
601 AND KART1.ORIG_SYSTEM_REFERENCE_ID1 = KART.ORIG_SYSTEM_REFERENCE_ID1
602 AND KART1.SCN_ID = SCN1.ID
603 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
604 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
605
606 CURSOR l_get_art_from_archive_csr IS
607 SELECT
608 SAV_SAE_ID,
609 KART.ATTRIBUTE_CATEGORY,
610 KART.ATTRIBUTE1,
611 KART.ATTRIBUTE2,
612 KART.ATTRIBUTE3,
613 KART.ATTRIBUTE4,
614 KART.ATTRIBUTE5,
615 KART.ATTRIBUTE6,
616 KART.ATTRIBUTE7,
617 KART.ATTRIBUTE8,
618 KART.ATTRIBUTE9,
619 KART.ATTRIBUTE10,
620 KART.ATTRIBUTE11,
621 KART.ATTRIBUTE12,
622 KART.ATTRIBUTE13,
623 KART.ATTRIBUTE14,
624 KART.ATTRIBUTE15,
625 SOURCE_FLAG,
626 ARTICLE_VERSION_ID,
627 CHANGE_NONSTD_YN,
628 SCN.ID SCN_ID,
629 DECODE(P_KEEP_ORIG_REF,'Y',KART.ORIG_SYSTEM_REFERENCE_ID1,KART.ID) ORIG_SYSTEM_REFERENCE_ID1,
630 MANDATORY_YN,
631 KART.LABEL,
632 DISPLAY_SEQUENCE,
633 ref_article_id,
634 ref_article_version_id,
635 orig_article_id
636 FROM OKC_K_ARTICLES_BH KART,
637 OKC_SECTIONS_B SCN
638 WHERE KART.DOCUMENT_TYPE=p_source_doc_type
639 AND KART.DOCUMENT_ID=p_source_doc_id
640 AND KART.MAJOR_VERSION = nvl(p_source_version_number,OKC_API.G_MISS_NUM)
641 AND SCN.DOCUMENT_TYPE = p_target_doc_type
642 AND SCN.DOCUMENT_ID =p_target_doc_id
643 AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
644 AND SCN.ORIG_SYSTEM_REFERENCE_ID1=KART.SCN_ID;
645
646 CURSOR l_get_local_article_csr(b_article_id IN NUMBER, b_local_org_id IN NUMBER) IS
647 SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
648 ADP.ADOPTION_TYPE,
649 VERS1.ARTICLE_ID
650 FROM OKC_ARTICLE_VERSIONS VERS,
651 OKC_ARTICLE_ADOPTIONS ADP,
652 OKC_ARTICLE_VERSIONS VERS1
653 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
654 AND VERS.ARTICLE_ID = b_article_id
655 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
656 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
657 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
658 AND VERS1.ARTICLE_VERSION_ID =ADP.LOCAL_ARTICLE_VERSION_ID
659 AND ADP.ADOPTION_TYPE = 'LOCALIZED'
660 AND ADP.LOCAL_ORG_ID = b_local_org_id
661 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
662 UNION ALL
663 SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
664 ADP.ADOPTION_TYPE,
665 VERS.ARTICLE_ID
666 FROM OKC_ARTICLE_VERSIONS VERS,
667 OKC_ARTICLE_ADOPTIONS ADP
668 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
669 AND VERS.ARTICLE_ID = b_article_id
670 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
671 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
672 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
673 AND ADP.ADOPTION_TYPE = 'ADOPTED'
674 AND ADP.LOCAL_ORG_ID = b_local_org_id
675 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
676 ;
677
678 CURSOR l_get_article_csr(b_article_version_id NUMBER) IS
679 SELECT nvl(PROVISION_YN,'N') provision_yn
680 FROM OKC_ARTICLE_VERSIONS
681 WHERE ARTICLE_VERSION_ID=b_article_version_id;
682
683 l_article_rec l_get_article_csr%ROWTYPE;
684 l_local_article_rec l_get_local_article_csr%ROWTYPE;
685 x_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
686
687 l_article_org_id NUMBER;
688
689 CURSOR l_get_article_org_csr(b_article_id NUMBER) IS
690 SELECT org_id
691 FROM OKC_ARTICLES_ALL
692 WHERE article_id = b_article_id;
693
694 CURSOR l_get_max_local_article_csr(b_article_id IN NUMBER, b_article_org_id IN NUMBER) IS
695 SELECT DECODE(ADP.LOCAL_ARTICLE_VERSION_ID,NULL,ADP.GLOBAL_ARTICLE_VERSION_ID,ADP.LOCAL_ARTICLE_VERSION_ID),
696 ADP.ADOPTION_TYPE
697 FROM OKC_ARTICLE_ADOPTIONS ADP
698 WHERE ADP.LOCAL_ORG_ID = b_article_org_id
699 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
700 AND ADP.GLOBAL_ARTICLE_VERSION_ID IN (SELECT ARTICLE_VERSION_ID
701 FROM OKC_ARTICLE_VERSIONS
702 WHERE article_id = b_article_id)
703 ORDER BY ADP.creation_date desc;
704
705 CURSOR l_get_local_article_id(b_article_version_id IN NUMBER) IS
706 SELECT article_id
707 FROM okc_article_versions
708 WHERE article_version_id = b_article_version_id;
709
710 l_current_org_id VARCHAR2(100);
711 l_adoption_type VARCHAR2(100);
712 l_local_article_id NUMBER;
713
714 BEGIN
715
716 x_return_status := G_RET_STS_SUCCESS;
717
718 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
719 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.copy_articles');
720 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Parameters ');
721 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_type : '||p_target_doc_type);
722 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_id : '||p_target_doc_id);
723 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_type : '||p_source_doc_type);
724 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_id : '||p_source_doc_id);
725 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_keep_version : '||p_keep_version);
726 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_article_effective_date : '||p_article_effective_date);
727 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_version_number : '||p_source_version_number);
728 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_copy_from_archive : '||p_copy_from_archive);
729 END IF;
730
731 OPEN l_get_prov_csr;
732 FETCH l_get_prov_csr into l_prov_allowed;
733 CLOSE l_get_prov_csr;
734
735 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
736 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_prov_allowed : '||l_prov_allowed);
737 END IF;
738
739 IF p_copy_from_archive ='Y' THEN
740 OPEN l_get_art_from_archive_csr;
741 FETCH l_get_art_from_archive_csr BULK COLLECT INTO
742 sav_sae_tbl,
743 Attribute_category_tbl,
744 Attribute1_tbl,
745 Attribute2_tbl,
746 Attribute3_tbl,
747 Attribute4_tbl,
748 Attribute5_tbl,
749 Attribute6_tbl,
750 Attribute7_tbl,
751 Attribute8_tbl,
752 Attribute9_tbl,
753 Attribute10_tbl,
754 Attribute11_tbl,
755 Attribute12_tbl,
756 Attribute13_tbl,
757 Attribute14_tbl,
758 Attribute15_tbl,
759 Source_flag_tbl,
760 Article_Version_tbl,
761 Change_nonstd_yn_tbl,
762 Scn_id_tbl,
763 Orig_System_Reference_id1_tbl,
764 Mandatory_yn_tbl,
765 Label_tbl,
766 Display_sequence_tbl,
767 ref_article_id_tbl,
768 ref_article_version_id_tbl,
769 orig_article_id_tbl;
770 CLOSE l_get_art_from_archive_csr;
771 ELSE
772 -- p_copy_from_archive is N
773 IF NVL(p_keep_orig_ref,'N') = 'N' THEN
774 OPEN l_get_art_csr;
775 FETCH l_get_art_csr BULK COLLECT INTO
776 sav_sae_tbl,
777 Attribute_category_tbl,
778 Attribute1_tbl,
779 Attribute2_tbl,
780 Attribute3_tbl,
781 Attribute4_tbl,
782 Attribute5_tbl,
783 Attribute6_tbl,
784 Attribute7_tbl,
785 Attribute8_tbl,
786 Attribute9_tbl,
787 Attribute10_tbl,
788 Attribute11_tbl,
789 Attribute12_tbl,
790 Attribute13_tbl,
791 Attribute14_tbl,
792 Attribute15_tbl,
793 Source_flag_tbl,
794 Article_Version_tbl,
795 Change_nonstd_yn_tbl,
796 Scn_id_tbl,
797 Orig_System_Reference_id1_tbl,
798 Mandatory_yn_tbl,
799 Label_tbl,
800 Display_sequence_tbl,
801 ref_article_id_tbl,
802 ref_article_version_id_tbl,
803 orig_article_id_tbl;
804 CLOSE l_get_art_csr;
805 ELSE
806 OPEN l_get_orig_art_csr;
807 FETCH l_get_orig_art_csr BULK COLLECT INTO
808 sav_sae_tbl,
809 Attribute_category_tbl,
810 Attribute1_tbl,
811 Attribute2_tbl,
812 Attribute3_tbl,
813 Attribute4_tbl,
814 Attribute5_tbl,
815 Attribute6_tbl,
816 Attribute7_tbl,
817 Attribute8_tbl,
818 Attribute9_tbl,
819 Attribute10_tbl,
820 Attribute11_tbl,
821 Attribute12_tbl,
822 Attribute13_tbl,
823 Attribute14_tbl,
824 Attribute15_tbl,
825 Source_flag_tbl,
826 Article_Version_tbl,
827 Change_nonstd_yn_tbl,
828 Scn_id_tbl,
829 Orig_System_Reference_id1_tbl,
830 Mandatory_yn_tbl,
831 Label_tbl,
832 Display_sequence_tbl,
833 ref_article_id_tbl,
834 ref_article_version_id_tbl,
835 orig_article_id_tbl;
836 CLOSE l_get_orig_art_csr;
837 END IF;
838 END IF; -- p_copy_from_archive
839
840 IF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
841 AND p_target_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
842
843 OPEN l_get_global_csr;
844 FETCH l_get_global_csr into l_global_yn;
845 CLOSE l_get_global_csr;
846
847 OPEN l_get_org_csr;
848 FETCH l_get_org_csr into l_org_id;
849 CLOSE l_get_org_csr;
850
851 END IF; -- source and target are templates
852
853 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
854 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Source Template l_global_yn : '||l_global_yn);
855 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Target Template l_org_id : '||l_org_id);
856 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Count of Articles on Source : '||sav_sae_tbl.COUNT);
857 END IF;
858
859 IF sav_sae_tbl.COUNT > 0 THEN
860 FOR i IN sav_sae_tbl.FIRST..sav_sae_tbl.LAST LOOP
861 l_discard := false;
862
863 OPEN l_get_std_csr(sav_sae_tbl(i));
864 FETCH l_get_std_csr INTO l_standard_yn,l_art_title;
865 CLOSE l_get_std_csr;
866
867 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
868 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_standard_yn : '||l_standard_yn);
869 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_art_title : '||l_art_title);
870 END IF;
871
872 IF p_keep_version = 'N' AND l_standard_yn='Y'
873 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
874
875 -- check if Article is global or local
876 OPEN l_get_article_org_csr(sav_sae_tbl(i));
877 FETCH l_get_article_org_csr INTO l_article_org_id;
878 CLOSE l_get_article_org_csr;
879
880 -- current Org Id
881 -- fnd_profile.get('ORG_ID',l_current_org_id);
882 l_current_org_id := OKC_TERMS_UTIL_PVT.get_current_org_id(p_target_doc_type, p_target_doc_id);
883
884 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
885 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_article_org_id : '||l_article_org_id);
886 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_current_org_id : '||l_current_org_id);
887 END IF;
888
889
890 -- since p_keep_version is N we will initialize the article_version_id
891 article_version_tbl(i) := NULL;
892
893 IF nvl(l_current_org_id,'?') <> l_article_org_id THEN
894 -- this is a ADOPTED OR LOCALIZED ARTICLE
895
896 OPEN l_get_local_article_csr(sav_sae_tbl(i), l_current_org_id);
897 FETCH l_get_local_article_csr INTO l_local_article_rec;
898
899 IF l_get_local_article_csr%NOTFOUND THEN
900 -- check for max version
901 OPEN l_get_max_local_article_csr(sav_sae_tbl(i),l_current_org_id);
902 FETCH l_get_max_local_article_csr INTO article_version_tbl(i),l_adoption_type;
903
904 IF l_get_max_local_article_csr%NOTFOUND THEN
905 -- discard this record
906 l_discard := true;
907 okc_Api.Set_Message(p_app_name => G_APP_NAME,
908 p_msg_name => 'OKC_ART_NOT_ADOPTED',
909 p_token1 => 'ART_TITLE',
910 p_token1_value => l_art_title,
911 p_token2 => 'ORG_ID',
912 p_token2_value => l_current_org_id);
913 ELSE -- max record found
914 IF l_adoption_type = 'LOCALIZED' THEN
915 -- get the local article id and swap
916 OPEN l_get_local_article_id(article_version_tbl(i));
917 FETCH l_get_local_article_id INTO l_local_article_id;
918 CLOSE l_get_local_article_id;
919 -- SWAP Article Id
920 sav_sae_tbl(i) := l_local_article_id;
921 END IF; -- for adoption_type LOCALIZED
922
923 END IF; -- max record not found
924
925 CLOSE l_get_max_local_article_csr;
926
927 ELSE
928 -- local record found
929 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
930 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: this is a ADOPTED OR LOCALIZED ARTICLE');
931 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);
932 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);
933 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);
934 END IF;
935
936 IF l_local_article_rec.adoption_type = 'LOCALIZED' THEN
937 sav_sae_tbl(i) := l_local_article_rec.article_id;
938 ELSIF l_local_article_rec.adoption_type = 'ADOPTED' THEN
939 NULL;
940 END IF;
941
942 article_version_tbl(i) := l_local_article_rec.LOCAL_ARTICLE_VERSION_ID;
943
944 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_get_local_article_csr : '||article_version_tbl(i));
946 END IF;
947
948 END IF; -- for local record found
949 CLOSE l_get_local_article_csr;
950
951 ELSE
952 -- this is local article
953 OPEN l_get_latest_article_csr(sav_sae_tbl(i));
954 FETCH l_get_latest_article_csr INTO article_version_tbl(i);
955 CLOSE l_get_latest_article_csr;
956
957 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
958 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: this is a Local ARTICLE');
959 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_get_latest_article_csr : '||article_version_tbl(i));
960 END IF;
961
962 IF p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
963 AND article_version_tbl(i) IS NULL THEN
964 OPEN l_get_max_article_csr(sav_sae_tbl(i));
965 FETCH l_get_max_article_csr INTO article_version_tbl(i);
966 CLOSE l_get_max_article_csr;
967
968 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
969 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_get_max_article_csr : '||article_version_tbl(i));
970 END IF;
971
972 -- if article_version_id is still null then discard this record
973 IF article_version_tbl(i) IS NULL THEN
974 l_discard := true;
975 okc_Api.Set_Message(p_app_name => G_APP_NAME,
976 p_msg_name => 'OKC_ART_NO_APP_VERSION',
977 p_token1 => 'ART_TITLE',
978 p_token1_value => l_art_title);
979 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
980 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Discarding Local Article id : '||sav_sae_tbl(i));
981 END IF;
982 END IF; -- if article_version_id is still null
983
984 END IF; -- for target document type not template
985
986 END IF; -- for adopted/localized or local article
987
988 ELSIF l_standard_yn='N' THEN
989
990 -- Copying Non-Standard Article
991
992 OKC_ARTICLES_GRP.copy_article( p_api_version => 1,
993 p_init_msg_list => FND_API.G_FALSE,
994 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
995 p_commit => FND_API.G_FALSE,
996 p_article_version_id => article_version_tbl(i),
997 p_new_article_title => NULL,
998 p_create_standard_yn => 'N',
999 x_article_version_id => article_version_tbl(i),
1000 x_article_id => sav_sae_tbl(i),
1001 x_article_number => x_article_number,
1002 x_return_status => x_return_status,
1003 x_msg_count => x_msg_count,
1004 x_msg_data => x_msg_data);
1005
1006 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1008 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1009 RAISE FND_API.G_EXC_ERROR ;
1010 END IF;
1011 /*
1012 OPEN l_get_no_std_ref_csr(article_version_tbl(i));
1013 FETCH l_get_no_std_ref_csr INTO ref_article_id_tbl(i),ref_article_version_id_tbl(i);
1014 IF l_get_no_std_ref_csr%NOTFOUND THEN
1015 ref_article_id_tbl(i):=NULL;
1016 ref_article_version_id_tbl(i):=NULL;
1017 END IF;
1018 CLOSE l_get_no_std_ref_csr;
1019 */
1020 END IF; -- p_keep_version = N and std or non std art
1021
1022 IF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1023 AND p_target_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1024 AND l_standard_yn='Y' THEN
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: Template To Template Copy l_global_yn : '||l_global_yn);
1028 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'));
1029 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100:Target Template org_id : '||l_org_id);
1030 END IF;
1031
1032 IF l_global_yn = 'Y'
1033 AND nvl(fnd_profile.value('OKC_GLOBAL_ORG_ID'),'?') <> l_org_id THEN
1034
1035 OPEN l_get_local_article_csr(sav_sae_tbl(i),l_org_id);
1036 FETCH l_get_local_article_csr INTO l_local_article_rec;
1037
1038 IF l_get_local_article_csr%NOTFOUND THEN
1039 -- go for the max article that was adopted or localized
1040 OPEN l_get_max_local_article_csr(sav_sae_tbl(i),l_org_id);
1041 FETCH l_get_max_local_article_csr INTO article_version_tbl(i),l_adoption_type;
1042
1043 IF l_get_max_local_article_csr%NOTFOUND THEN
1044 l_discard := true;
1045 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1046 p_msg_name => 'OKC_ART_NOT_ADOPTED',
1047 p_token1 => 'ART_TITLE',
1048 p_token1_value => l_art_title,
1049 p_token2 => 'ORG_ID',
1050 p_token2_value => l_org_id);
1051 ELSE -- max record found
1052 IF l_adoption_type = 'LOCALIZED' THEN
1053 -- get the local article id and swap
1054 OPEN l_get_local_article_id(article_version_tbl(i));
1055 FETCH l_get_local_article_id INTO l_local_article_id;
1056 CLOSE l_get_local_article_id;
1057 -- SWAP Article Id
1058 sav_sae_tbl(i) := l_local_article_id;
1059 END IF; -- l_adoption_type = LOCALIZED
1060
1061 END IF; -- max article csr
1062
1063 CLOSE l_get_max_local_article_csr;
1064
1065 ELSE -- local_article_csr found
1066
1067 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1068 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);
1069 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);
1070 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);
1071 END IF;
1072
1073 IF l_local_article_rec.adoption_type = 'LOCALIZED' THEN
1074 sav_sae_tbl(i) := l_local_article_rec.article_id;
1075 ELSIF l_local_article_rec.adoption_type = 'ADOPTED' THEN
1076 NULL;
1077 END IF;
1078
1079 END IF; -- record not found
1080
1081 CLOSE l_get_local_article_csr;
1082
1083 END IF; -- l_global_yn is Y
1084
1085 END IF; -- template to template copy
1086
1087 IF p_target_doc_type <>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
1088
1089 OPEN l_get_article_csr(article_version_tbl(i));
1090 FETCH l_get_article_csr INTO l_article_rec;
1091 CLOSE l_get_article_csr;
1092 IF l_article_rec.provision_yn='Y' and l_prov_allowed='N' THEN
1093 l_discard := true;
1094 END IF;
1095
1096 END IF; -- target not template
1097
1098 IF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1099 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN
1100
1101 source_flag_tbl(i) := 'T';
1102
1103 END IF; -- template to document copy
1104
1105 IF NOT l_discard THEN
1106
1107 k := k +1;
1108 sav_sae_tbl1(k) := sav_sae_tbl(i);
1109 Attribute_category_tbl1(k) := Attribute_category_tbl(i);
1110 Attribute1_tbl1(k) := Attribute1_tbl(i);
1111 Attribute2_tbl1(k) := Attribute2_tbl(i);
1112 Attribute3_tbl1(k) := Attribute3_tbl(i);
1113 Attribute4_tbl1(k) := Attribute4_tbl(i);
1114 Attribute5_tbl1(k) := Attribute5_tbl(i);
1115 Attribute6_tbl1(k) := Attribute6_tbl(i);
1116 Attribute7_tbl1(k) := Attribute7_tbl(i);
1117 Attribute8_tbl1(k) := Attribute8_tbl(i);
1118 Attribute9_tbl1(k) := Attribute9_tbl(i);
1119 Attribute10_tbl1(k) := Attribute10_tbl(i);
1120 Attribute11_tbl1(k) := Attribute11_tbl(i);
1121 Attribute12_tbl1(k) := Attribute12_tbl(i);
1122 Attribute13_tbl1(k) := Attribute13_tbl(i);
1123 Attribute14_tbl1(k) := Attribute14_tbl(i);
1124 Attribute15_tbl1(k) := Attribute15_tbl(i);
1125 Source_flag_tbl1(k) := Source_flag_tbl(i);
1126 Article_Version_tbl1(k) := Article_Version_tbl(i);
1127 Change_nonstd_yn_tbl1(k) := Change_nonstd_yn_tbl(i);
1128 Scn_id_tbl1(k) := Scn_id_tbl(i);
1129 Orig_System_Reference_id1_tbl1(k) := Orig_System_Reference_id1_tbl(i);
1130 Mandatory_yn_tbl1(k) := Mandatory_yn_tbl(i);
1131 Label_tbl1(k) := Label_tbl(i);
1132 Display_sequence_tbl1(k) := Display_sequence_tbl(i);
1133 ref_article_id_tbl1(k) := ref_article_id_tbl(i);
1134 ref_article_version_id_tbl1(k) := ref_article_version_id_tbl(i);
1135 orig_article_id_tbl1(k) := orig_article_id_tbl(i);
1136
1137 END IF;
1138 END LOOP;
1139 END IF; -- count > 0
1140
1141 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1142 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Count of Articles on Target : '||sav_sae_tbl1.COUNT);
1143 END IF;
1144
1145 IF sav_sae_tbl1.COUNT>0 THEN
1146 FORALL i IN sav_sae_tbl1.FIRST..sav_sae_tbl1.LAST
1147 INSERT INTO OKC_K_ARTICLES_B(
1148 ID,
1149 SAV_SAE_ID,
1150 DOCUMENT_TYPE,
1151 DOCUMENT_ID,
1152 CHR_ID,
1153 DNZ_CHR_ID,
1154 SOURCE_FLAG,
1155 MANDATORY_YN,
1156 SCN_ID,
1157 LABEL,
1158 AMENDMENT_DESCRIPTION,
1159 AMENDMENT_OPERATION_CODE,
1160 ARTICLE_VERSION_ID,
1161 CHANGE_NONSTD_YN,
1162 ORIG_SYSTEM_REFERENCE_CODE,
1163 ORIG_SYSTEM_REFERENCE_ID1,
1164 ORIG_SYSTEM_REFERENCE_ID2,
1165 DISPLAY_SEQUENCE,
1166 ATTRIBUTE_CATEGORY,
1167 ATTRIBUTE1,
1168 ATTRIBUTE2,
1169 ATTRIBUTE3,
1170 ATTRIBUTE4,
1171 ATTRIBUTE5,
1172 ATTRIBUTE6,
1173 ATTRIBUTE7,
1174 ATTRIBUTE8,
1175 ATTRIBUTE9,
1176 ATTRIBUTE10,
1177 ATTRIBUTE11,
1178 ATTRIBUTE12,
1179 ATTRIBUTE13,
1180 ATTRIBUTE14,
1181 ATTRIBUTE15,
1182 PRINT_TEXT_YN,
1183 REF_ARTICLE_ID,
1184 REF_ARTICLE_VERSION_ID,
1185 OBJECT_VERSION_NUMBER,
1186 CREATED_BY,
1187 CREATION_DATE,
1188 LAST_UPDATED_BY,
1189 LAST_UPDATE_LOGIN,
1190 LAST_UPDATE_DATE,
1191 ORIG_ARTICLE_ID)
1192 VALUES(OKC_K_ARTICLES_B_S.nextval,
1193 sav_sae_tbl1(i),
1194 p_target_doc_type,
1195 p_target_doc_id,
1196 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),
1197 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),
1198 Source_flag_tbl1(i),
1199 Mandatory_yn_tbl1(i),
1200 Scn_id_tbl1(i),
1201 Label_tbl1(i),
1202 Null,
1203 Null,
1204 decode(p_target_doc_type, OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE,NULL,Article_Version_tbl1(i)),
1205 Change_nonstd_yn_tbl1(i),
1206 G_COPY,
1207 Orig_System_Reference_id1_tbl1(i),
1208 Null,
1209 Display_sequence_tbl1(i),
1210 Attribute_category_tbl1(i),
1211 Attribute1_tbl1(i),
1212 Attribute2_tbl1(i),
1213 Attribute3_tbl1(i),
1214 Attribute4_tbl1(i),
1215 Attribute5_tbl1(i),
1216 Attribute6_tbl1(i),
1217 Attribute7_tbl1(i),
1218 Attribute8_tbl1(i),
1219 Attribute9_tbl1(i),
1220 Attribute10_tbl1(i),
1221 Attribute11_tbl1(i),
1222 Attribute12_tbl1(i),
1223 Attribute13_tbl1(i),
1224 Attribute14_tbl1(i),
1225 Attribute15_tbl1(i),
1226 Null,
1227 ref_article_id_tbl(i),
1228 ref_article_version_id_tbl(i),
1229 1,
1230 Fnd_Global.User_Id,
1231 sysdate,
1232 Fnd_Global.User_Id,
1233 Fnd_Global.Login_Id,
1234 sysdate,
1235 orig_article_id_tbl1(i));
1236
1237 END IF;
1238
1239 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1240
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,'900: Leaving copy_articles '||x_return_status);
1243 END IF;
1244
1245 EXCEPTION
1246
1247 WHEN FND_API.G_EXC_ERROR THEN
1248
1249 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1250 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_articles:FND_API.G_EXC_ERROR Exception');
1251 END IF;
1252 IF l_get_prov_csr%ISOPEN THEN
1253 CLOSE l_get_prov_csr;
1254 END IF;
1255
1256 IF l_get_article_csr%ISOPEN THEN
1257 CLOSE l_get_article_csr;
1258 END IF;
1259 IF l_get_local_article_csr%ISOPEN THEN
1260 CLOSE l_get_local_article_csr;
1261 END IF;
1262 IF l_get_art_csr %ISOPEN THEN
1263 CLOSE l_get_art_csr ;
1264 END IF;
1265 IF l_get_latest_article_csr%ISOPEN THEN
1266 CLOSE l_get_latest_article_csr;
1267 END IF;
1268 IF l_get_org_csr%ISOPEN THEN
1269 CLOSE l_get_org_csr;
1270 END IF;
1271 IF l_get_global_csr%ISOPEN THEN
1272 CLOSE l_get_global_csr;
1273 END IF;
1274 IF l_get_prov_csr%ISOPEN THEN
1275 CLOSE l_get_prov_csr;
1276 END IF;
1277 IF l_get_std_csr%ISOPEN THEN
1278 CLOSE l_get_std_csr;
1279 END IF;
1280
1281 x_return_status := G_RET_STS_ERROR;
1282 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1283
1284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1285
1286 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1287 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_articles:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1288 END IF;
1289 IF l_get_article_csr%ISOPEN THEN
1290 CLOSE l_get_article_csr;
1291 END IF;
1292 IF l_get_local_article_csr%ISOPEN THEN
1293 CLOSE l_get_local_article_csr;
1294 END IF;
1295 IF l_get_art_csr %ISOPEN THEN
1296 CLOSE l_get_art_csr ;
1297 END IF;
1298 IF l_get_latest_article_csr%ISOPEN THEN
1299 CLOSE l_get_latest_article_csr;
1300 END IF;
1301 IF l_get_org_csr%ISOPEN THEN
1302 CLOSE l_get_org_csr;
1303 END IF;
1304
1305 x_return_status := G_RET_STS_UNEXP_ERROR;
1306 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1307
1308 WHEN OTHERS THEN
1309
1310 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1311 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_articles because of EXCEPTION: '||sqlerrm);
1312 END IF;
1313
1314 IF l_get_article_csr%ISOPEN THEN
1315 CLOSE l_get_article_csr;
1316 END IF;
1317
1318
1319 IF l_get_local_article_csr%ISOPEN THEN
1320 CLOSE l_get_local_article_csr;
1321 END IF;
1322
1323 IF l_get_art_csr %ISOPEN THEN
1324 CLOSE l_get_art_csr ;
1325 END IF;
1326
1327 IF l_get_latest_article_csr%ISOPEN THEN
1328 CLOSE l_get_latest_article_csr;
1329 END IF;
1330
1331 IF l_get_org_csr%ISOPEN THEN
1332 CLOSE l_get_org_csr;
1333 END IF;
1334
1335 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1336 p_msg_name => G_UNEXPECTED_ERROR,
1337 p_token1 => G_SQLCODE_TOKEN,
1338 p_token1_value => sqlcode,
1339 p_token2 => G_SQLERRM_TOKEN,
1340 p_token2_value => sqlerrm);
1341
1342 x_return_status := G_RET_STS_UNEXP_ERROR;
1343 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1344
1345 END copy_articles;
1346
1347 procedure copy_sections(
1348 p_target_doc_type IN VARCHAR2,
1349 p_source_doc_type IN VARCHAR2,
1350 p_target_doc_id IN NUMBER,
1351 p_source_doc_id IN NUMBER,
1352 p_source_version_number IN NUMBER := NULL,
1353 p_copy_from_archive IN VARCHAR2 := 'N',
1354 p_keep_orig_ref IN VARCHAR2 := 'N',
1355 x_return_status OUT NOCOPY VARCHAR2,
1356 x_msg_data OUT NOCOPY VARCHAR2,
1357 x_msg_count OUT NOCOPY NUMBER) IS
1358
1359 l_api_name CONSTANT VARCHAR2(30) := 'copy_sections';
1360 TYPE AttributeCategoryList IS TABLE OF OKC_SECTIONS_B.ATTRIBUTE_CATEGORY%TYPE INDEX BY BINARY_INTEGER;
1361 TYPE AttributeList IS TABLE OF OKC_SECTIONS_B.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
1362 TYPE ScnIdList IS TABLE OF OKC_SECTIONS_B.SCN_ID%TYPE INDEX BY BINARY_INTEGER;
1363 TYPE OrigSystemReferenceId1List IS TABLE OF OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE INDEX BY BINARY_INTEGER;
1364 TYPE SectionSequenceList IS TABLE OF OKC_SECTIONS_B.SECTION_SEQUENCE%TYPE INDEX BY BINARY_INTEGER;
1365 TYPE HeadingList IS TABLE OF OKC_SECTIONS_B.HEADING%TYPE INDEX BY BINARY_INTEGER;
1366 TYPE LabelList IS TABLE OF OKC_SECTIONS_B.LABEL%TYPE INDEX BY BINARY_INTEGER;
1367 TYPE ScnCodeList IS TABLE OF OKC_SECTIONS_B.SCN_CODE%TYPE INDEX BY BINARY_INTEGER;
1368 TYPE PrintYnList IS TABLE OF OKC_SECTIONS_B.PRINT_YN%TYPE INDEX BY BINARY_INTEGER;
1369 TYPE IdList IS TABLE OF OKC_SECTIONS_B.ID%TYPE INDEX BY BINARY_INTEGER;
1370 TYPE DescriptionList IS TABLE OF OKC_SECTIONS_B.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
1371
1372
1373 Id_tbl IdList;
1374 Attribute_category_tbl AttributeCategoryList;
1375 Attribute1_tbl AttributeList;
1376 Attribute2_tbl AttributeList;
1377 Attribute3_tbl AttributeList;
1378 Attribute4_tbl AttributeList;
1379 Attribute5_tbl AttributeList;
1380 Attribute6_tbl AttributeList;
1381 Attribute7_tbl AttributeList;
1382 Attribute8_tbl AttributeList;
1383 Attribute9_tbl AttributeList;
1384 Attribute10_tbl AttributeList;
1385 Attribute11_tbl AttributeList;
1386 Attribute12_tbl AttributeList;
1387 Attribute13_tbl AttributeList;
1388 Attribute14_tbl AttributeList;
1389 Attribute15_tbl AttributeList;
1390 Scn_id_tbl ScnIdList;
1391 Orig_System_Reference_id1_tbl OrigSystemReferenceId1List;
1392 Section_sequence_tbl SectionSequenceList;
1393 Heading_tbl HeadingList;
1394 Scn_code_tbl ScnCodeList;
1395 Label_tbl LabelList;
1396 Print_yn_tbl PrintYnList;
1397
1398 Orig_System_Reference_id1_tbl1 OrigSystemReferenceId1List;
1399 Id_tbl1 IdList;
1400
1401 Description_tbl DescriptionList;
1402
1403
1404 CURSOR l_get_scn_csr IS
1405 SELECT OKC_SECTIONS_B_S.NEXTVAL,
1406 ATTRIBUTE_CATEGORY,
1407 ATTRIBUTE1,
1408 ATTRIBUTE2,
1409 ATTRIBUTE3,
1410 ATTRIBUTE4,
1411 ATTRIBUTE5,
1412 ATTRIBUTE6,
1413 ATTRIBUTE7,
1414 ATTRIBUTE8,
1415 ATTRIBUTE9,
1416 ATTRIBUTE10,
1417 ATTRIBUTE11,
1418 ATTRIBUTE12,
1419 ATTRIBUTE13,
1420 ATTRIBUTE14,
1421 ATTRIBUTE15,
1422 SCN_ID SCN_ID,
1423 DECODE(P_KEEP_ORIG_REF,'Y',ORIG_SYSTEM_REFERENCE_ID1,ID) ORIG_SYSTEM_REFERENCE_ID1,
1424 SECTION_SEQUENCE,
1425 LABEL,
1426 SCN_CODE,
1427 HEADING,
1428 PRINT_YN ,
1429 DESCRIPTION
1430 FROM OKC_SECTIONS_B
1431 WHERE DOCUMENT_TYPE=P_SOURCE_DOC_TYPE
1432 AND DOCUMENT_ID=P_SOURCE_DOC_ID
1433 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
1434 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
1435
1436
1437 CURSOR l_get_scn_from_archive_csr IS
1438 SELECT OKC_SECTIONS_B_S.NEXTVAL,
1439 ATTRIBUTE_CATEGORY,
1440 ATTRIBUTE1,
1441 ATTRIBUTE2,
1442 ATTRIBUTE3,
1443 ATTRIBUTE4,
1444 ATTRIBUTE5,
1445 ATTRIBUTE6,
1446 ATTRIBUTE7,
1447 ATTRIBUTE8,
1448 ATTRIBUTE9,
1449 ATTRIBUTE10,
1450 ATTRIBUTE11,
1451 ATTRIBUTE12,
1452 ATTRIBUTE13,
1453 ATTRIBUTE14,
1454 ATTRIBUTE15,
1455 SCN_ID SCN_ID,
1456 DECODE(P_KEEP_ORIG_REF, 'Y', ORIG_SYSTEM_REFERENCE_ID1,ID) ORIG_SYSTEM_REFERENCE_ID1,
1457 SECTION_SEQUENCE,
1458 LABEL,
1459 SCN_CODE,
1460 HEADING,
1461 PRINT_YN ,
1462 DESCRIPTION
1463 FROM OKC_SECTIONS_BH
1464 WHERE DOCUMENT_TYPE=P_SOURCE_DOC_TYPE
1465 AND DOCUMENT_ID=P_SOURCE_DOC_ID
1466 AND MAJOR_VERSION = nvl(p_source_version_number,OKC_API.G_MISS_NUM);
1467
1468 BEGIN
1469
1470 x_return_status := G_RET_STS_SUCCESS;
1471
1472 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1473 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.copy_sections');
1474 END IF;
1475
1476 IF p_copy_from_archive ='Y' THEN
1477
1478 OPEN l_get_scn_from_archive_csr;
1479 FETCH l_get_scn_from_archive_csr BULK COLLECT INTO
1480 id_tbl,
1481 Attribute_category_tbl,
1482 Attribute1_tbl,
1483 Attribute2_tbl,
1484 Attribute3_tbl,
1485 Attribute4_tbl,
1486 Attribute5_tbl,
1487 Attribute6_tbl,
1488 Attribute7_tbl,
1489 Attribute8_tbl,
1490 Attribute9_tbl,
1491 Attribute10_tbl,
1492 Attribute11_tbl,
1493 Attribute12_tbl,
1494 Attribute13_tbl,
1495 Attribute14_tbl,
1496 Attribute15_tbl,
1497 Scn_id_tbl,
1498 Orig_System_Reference_id1_tbl,
1499 Section_sequence_tbl,
1500 Label_tbl,
1501 Scn_code_tbl,
1502 Heading_tbl,
1503 Print_yn_tbl ,
1504 Description_tbl ;
1505 CLOSE l_get_scn_from_archive_csr;
1506 ELSE
1507 OPEN l_get_scn_csr;
1508 FETCH l_get_scn_csr BULK COLLECT INTO
1509 id_tbl,
1510 Attribute_category_tbl,
1511 Attribute1_tbl,
1512 Attribute2_tbl,
1513 Attribute3_tbl,
1514 Attribute4_tbl,
1515 Attribute5_tbl,
1516 Attribute6_tbl,
1517 Attribute7_tbl,
1518 Attribute8_tbl,
1519 Attribute9_tbl,
1520 Attribute10_tbl,
1521 Attribute11_tbl,
1522 Attribute12_tbl,
1523 Attribute13_tbl,
1524 Attribute14_tbl,
1525 Attribute15_tbl,
1526 Scn_id_tbl,
1527 Orig_System_Reference_id1_tbl,
1528 Section_sequence_tbl,
1529 Label_tbl,
1530 Scn_code_tbl,
1531 Heading_tbl,
1532 Print_yn_tbl,
1533 Description_tbl ;
1534 CLOSE l_get_scn_csr;
1535 END IF;
1536
1537 id_tbl1 := id_tbl;
1538 Orig_System_Reference_id1_tbl1 := Orig_System_Reference_id1_tbl;
1539
1540 -- Following routine will link subsections to its parent section
1541
1542 IF id_tbl.COUNT>0 THEN
1543
1544 FOR i IN id_tbl.FIRST..id_tbl.LAST LOOP
1545
1546
1547 IF scn_id_tbl(i) IS NOT NULL THEN
1548 FOR k IN id_tbl1.FIRST..id_tbl1.LAST LOOP
1549 IF Orig_System_Reference_id1_tbl1(k)= scn_id_tbl(i) THEN
1550 scn_id_tbl(i) := id_tbl1(k);
1551 END IF;
1552 END LOOP;
1553 END IF;
1554 END LOOP;
1555
1556 END IF;
1557
1558
1559 IF id_tbl.COUNT > 0 THEN
1560
1561 FORALL i IN id_tbl.FIRST..id_tbl.LAST
1562
1563 INSERT INTO OKC_SECTIONS_B(
1564 ID,
1565 DOCUMENT_TYPE,
1566 DOCUMENT_ID,
1567 CHR_ID,
1568 SCN_ID,
1569 LABEL,
1570 AMENDMENT_DESCRIPTION,
1571 AMENDMENT_OPERATION_CODE,
1572 ORIG_SYSTEM_REFERENCE_CODE,
1573 ORIG_SYSTEM_REFERENCE_ID1,
1574 ORIG_SYSTEM_REFERENCE_ID2,
1575 SECTION_SEQUENCE,
1576 ATTRIBUTE_CATEGORY,
1577 ATTRIBUTE1,
1578 ATTRIBUTE2,
1579 ATTRIBUTE3,
1580 ATTRIBUTE4,
1581 ATTRIBUTE5,
1582 ATTRIBUTE6,
1583 ATTRIBUTE7,
1584 ATTRIBUTE8,
1585 ATTRIBUTE9,
1586 ATTRIBUTE10,
1587 ATTRIBUTE11,
1588 ATTRIBUTE12,
1589 ATTRIBUTE13,
1590 ATTRIBUTE14,
1591 ATTRIBUTE15,
1592 PRINT_YN,
1593 HEADING,
1594 SCN_CODE,
1595 DESCRIPTION,
1596 OBJECT_VERSION_NUMBER,
1597 CREATED_BY,
1598 CREATION_DATE,
1599 LAST_UPDATED_BY,
1600 LAST_UPDATE_LOGIN,
1601 LAST_UPDATE_DATE)
1602 VALUES(id_tbl(i),
1603 p_target_doc_type,
1604 p_target_doc_id,
1605 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),
1606 Scn_id_tbl(i),
1607 Label_tbl(i),
1608 Null,
1609 Null,
1610 G_COPY,
1611 Orig_System_Reference_id1_tbl(i),
1612 Null,
1613 section_sequence_tbl(i),
1614 Attribute_category_tbl(i),
1615 Attribute1_tbl(i),
1616 Attribute2_tbl(i),
1617 Attribute3_tbl(i),
1618 Attribute4_tbl(i),
1619 Attribute5_tbl(i),
1620 Attribute6_tbl(i),
1621 Attribute7_tbl(i),
1622 Attribute8_tbl(i),
1623 Attribute9_tbl(i),
1624 Attribute10_tbl(i),
1625 Attribute11_tbl(i),
1626 Attribute12_tbl(i),
1627 Attribute13_tbl(i),
1628 Attribute14_tbl(i),
1629 Attribute15_tbl(i),
1630 print_yn_tbl(i),
1631 heading_tbl(i),
1632 scn_code_tbl(i),
1633 description_tbl(i),
1634 1,
1635 Fnd_Global.User_Id,
1636 sysdate,
1637 Fnd_Global.User_Id,
1638 Fnd_Global.Login_Id,
1639 sysdate);
1640
1641 END IF;
1642
1643 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1644
1645 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving copy_sections '||x_return_status);
1647 END IF;
1648
1649 EXCEPTION
1650
1651 WHEN FND_API.G_EXC_ERROR THEN
1652
1653 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1654 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_sections:FND_API.G_EXC_ERROR Exception');
1655 END IF;
1656
1657 IF l_get_scn_csr %ISOPEN THEN
1658 CLOSE l_get_scn_csr ;
1659 END IF;
1660
1661 x_return_status := G_RET_STS_ERROR;
1662 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1663
1664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1665
1666 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_sections:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1668 END IF;
1669
1670 IF l_get_scn_csr %ISOPEN THEN
1671 CLOSE l_get_scn_csr ;
1672 END IF;
1673
1674 x_return_status := G_RET_STS_UNEXP_ERROR;
1675 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1676
1677 WHEN OTHERS THEN
1678
1679 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1680 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_sections because of EXCEPTION: '||sqlerrm);
1681 END IF;
1682
1683 IF l_get_scn_csr %ISOPEN THEN
1684 CLOSE l_get_scn_csr ;
1685 END IF;
1686
1687 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1688 p_msg_name => G_UNEXPECTED_ERROR,
1689 p_token1 => G_SQLCODE_TOKEN,
1690 p_token1_value => sqlcode,
1691 p_token2 => G_SQLERRM_TOKEN,
1692 p_token2_value => sqlerrm);
1693
1694 x_return_status := G_RET_STS_UNEXP_ERROR;
1695 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1696
1697 END copy_sections;
1698
1699
1700 -- This API will be called wheneve a template is re-applied on a document.
1701 -- It will put all the manually added article into UN-Assigned section and remove other articles and section;
1702
1703 procedure remove_template_based_articles(
1704 p_doc_type IN VARCHAR2,
1705 p_doc_id IN NUMBER,
1706 p_retain_deliverable IN VARCHAR2,
1707 x_return_status OUT NOCOPY VARCHAR2,
1708 x_msg_data OUT NOCOPY VARCHAR2,
1709 x_msg_count OUT NOCOPY NUMBER) IS
1710
1711 l_api_name CONSTANT VARCHAR2(30) := 'remove_template_based_articles';
1712 CURSOR l_get_manual_art_csr IS
1713 SELECT count(*)
1714 FROM OKC_K_ARTICLES_B
1715 WHERE DOCUMENT_TYPE = p_doc_type
1716 AND DOCUMENT_ID = p_doc_id
1717 AND SOURCE_FLAG IS NULL;
1718
1719 CURSOR l_check_unassigned_section_csr IS
1720 SELECT id
1721 FROM OKC_SECTIONS_B
1722 WHERE DOCUMENT_TYPE = p_doc_type
1723 AND DOCUMENT_ID = p_doc_id
1724 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
1725 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
1726 AND SCN_CODE = 'UNASSIGNED';
1727
1728 CURSOR lock_kart_for_upd_csr IS
1729 SELECT ROWID FROM OKC_K_ARTICLES_B
1730 WHERE DOCUMENT_TYPE = p_doc_type
1731 AND DOCUMENT_ID = p_doc_id
1732 AND SOURCE_FLAG IS NULL FOR UPDATE NOWAIT;
1733
1734 CURSOR lock_var_for_del_csr IS
1735 SELECT ROWID FROM OKC_K_ART_VARIABLES
1736 WHERE CAT_ID IN
1737 (SELECT ID FROM OKC_K_ARTICLES_B
1738 WHERE DOCUMENT_TYPE=p_doc_type
1739 AND DOCUMENT_ID=p_doc_id
1740 AND SOURCE_FLAG IS NOT NULL) FOR UPDATE NOWAIT;
1741
1742 CURSOR lock_kart_for_del_csr IS
1743 SELECT ROWID FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
1744 AND DOCUMENT_ID=p_doc_id
1745 AND SOURCE_FLAG IS NOT NULL FOR UPDATE NOWAIT;
1746
1747 CURSOR lock_scn_for_del_csr(b_scn_id NUMBER) IS
1748 SELECT ROWID FROM OKC_SECTIONS_B
1749 WHERE DOCUMENT_TYPE=p_doc_type
1750 AND DOCUMENT_ID=p_doc_id
1751 AND id <> b_scn_id FOR UPDATE NOWAIT;
1752
1753 l_manual_art_count NUMBER;
1754 l_unassigned_scn_id OKC_SECTIONS_B.ID%TYPE := 0;
1755
1756 BEGIN
1757
1758 x_return_status := G_RET_STS_SUCCESS;
1759
1760 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Inside OKC_TERMS_COPY_PVT.remove_template_based_articles');
1762 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Parameter List ');
1763 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_doc_type : '||p_doc_type);
1764 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_doc_id : '||p_doc_id);
1765 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_retain_deliverable : '||p_retain_deliverable);
1766 END IF;
1767
1768 OPEN l_get_manual_art_csr;
1769 FETCH l_get_manual_art_csr INTO l_manual_art_count;
1770 CLOSE l_get_manual_art_csr;
1771
1772 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1773 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'110: l_manual_art_count : '||l_manual_art_count);
1774 END IF;
1775
1776 IF l_manual_art_count > 0 THEN
1777
1778 OPEN l_check_unassigned_section_csr ;
1779 FETCH l_check_unassigned_section_csr INTO l_unassigned_scn_id;
1780 CLOSE l_check_unassigned_section_csr;
1781
1782 IF l_unassigned_scn_id = 0 THEN
1783
1784 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1785 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'120: Creating Unassgined Section ');
1786 END IF;
1787
1788 OKC_TERMS_UTIL_PVT.create_unassigned_section(p_api_version => 1,
1789 p_commit => FND_API.G_FALSE,
1790 p_doc_type => p_doc_type,
1791 p_doc_id => p_doc_id,
1792 x_scn_id =>l_unassigned_scn_id,
1793 x_return_status => x_return_status,
1794 x_msg_count => x_msg_count,
1795 x_msg_data => x_msg_data
1796 );
1797 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1798 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: l_unassigned_scn_id : '||l_unassigned_scn_id);
1799 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: Cannot Create Unassgined Section : '||x_msg_data||' Status '||x_return_status);
1800 END IF;
1801
1802 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1804 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1805 RAISE FND_API.G_EXC_ERROR ;
1806 END IF;
1807
1808
1809 END IF;
1810
1811 OPEN lock_kart_for_upd_csr;
1812 CLOSE lock_kart_for_upd_csr;
1813
1814 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1815 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'140: Updating Manually added article sections to unassigned');
1816 END IF;
1817
1818 UPDATE OKC_K_ARTICLES_B
1819 SET SCN_ID = l_unassigned_scn_id,
1820 LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
1821 LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
1822 LAST_UPDATE_DATE = sysdate
1823 WHERE DOCUMENT_TYPE = p_doc_type
1824 AND DOCUMENT_ID = p_doc_id
1825 AND SOURCE_FLAG IS NULL;
1826
1827 END IF;
1828
1829 OPEN lock_var_for_del_csr;
1830 CLOSE lock_var_for_del_csr;
1831
1832 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1833 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'150: Deleting Variables ');
1834 END IF;
1835
1836 DELETE FROM OKC_K_ART_VARIABLES WHERE CAT_ID IN
1837 (SELECT ID FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
1838 AND DOCUMENT_ID=p_doc_id
1839 AND SOURCE_FLAG IS NOT NULL);
1840 OPEN lock_kart_for_del_csr;
1841 CLOSE lock_kart_for_del_csr;
1842
1843 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1844 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'160: Deleting Articles ');
1845 END IF;
1846
1847 DELETE FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
1848 AND DOCUMENT_ID=p_doc_id
1849 AND SOURCE_FLAG IS NOT NULL;
1850
1851 OPEN lock_scn_for_del_csr(l_unassigned_scn_id);
1852 CLOSE lock_scn_for_del_csr;
1853
1854 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1855 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'170: Deleting Sections ');
1856 END IF;
1857
1858 DELETE FROM OKC_SECTIONS_B WHERE DOCUMENT_TYPE=p_doc_type
1859 AND DOCUMENT_ID=p_doc_id
1860 AND id <> l_unassigned_scn_id;
1861
1862 IF p_retain_deliverable ='N' THEN
1863
1864 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1865 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: Calling OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables ');
1866 END IF;
1867
1868 OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables (
1869 p_api_version => 1,
1870 p_init_msg_list => FND_API.G_FALSE,
1871 p_doc_id => p_doc_id,
1872 p_doc_type => p_doc_type,
1873 x_msg_data => x_msg_data,
1874 x_msg_count => x_msg_count,
1875 x_return_status => x_return_status);
1876
1877 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: After Call to OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables ');
1879 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: x_return_status : '||x_return_status);
1880 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: x_msg_count : '||x_msg_count);
1881 END IF;
1882
1883 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1885 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1886 RAISE FND_API.G_EXC_ERROR ;
1887 END IF;
1888 END IF;
1889
1890 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1891
1892 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1893 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving remove_template_based_articles '||x_return_status);
1894 END IF;
1895
1896 EXCEPTION
1897 WHEN E_Resource_Busy THEN
1898 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1899 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'000: Leaving remove_template_based_articles:E_Resource_Busy Exception');
1900 END IF;
1901
1902 IF lock_kart_for_upd_csr%ISOPEN THEN
1903 CLOSE lock_kart_for_upd_csr;
1904 END IF;
1905
1906 IF lock_var_for_del_csr%ISOPEN THEN
1907 CLOSE lock_var_for_del_csr;
1908 END IF;
1909
1910 IF lock_kart_for_del_csr%ISOPEN THEN
1911 CLOSE lock_kart_for_del_csr;
1912 END IF;
1913
1914 IF lock_scn_for_del_csr%ISOPEN THEN
1915 CLOSE lock_scn_for_del_csr;
1916 END IF;
1917
1918 IF l_get_manual_art_csr%ISOPEN THEN
1919 CLOSE l_get_manual_art_csr ;
1920 END IF;
1921
1922 IF l_check_unassigned_section_csr%ISOPEN THEN
1923 CLOSE l_check_unassigned_section_csr ;
1924 END IF;
1925
1926 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
1927 x_return_status := G_RET_STS_ERROR ;
1928 WHEN FND_API.G_EXC_ERROR THEN
1929
1930 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1931 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving remove_template_based_articles:FND_API.G_EXC_ERROR Exception');
1932 END IF;
1933
1934 IF lock_kart_for_upd_csr%ISOPEN THEN
1935 CLOSE lock_kart_for_upd_csr;
1936 END IF;
1937
1938 IF lock_var_for_del_csr%ISOPEN THEN
1939 CLOSE lock_var_for_del_csr;
1940 END IF;
1941
1942 IF lock_kart_for_del_csr%ISOPEN THEN
1943 CLOSE lock_kart_for_del_csr;
1944 END IF;
1945
1946 IF lock_scn_for_del_csr%ISOPEN THEN
1947 CLOSE lock_scn_for_del_csr;
1948 END IF;
1949
1950 IF l_get_manual_art_csr%ISOPEN THEN
1951 CLOSE l_get_manual_art_csr ;
1952 END IF;
1953
1954 IF l_check_unassigned_section_csr%ISOPEN THEN
1955 CLOSE l_check_unassigned_section_csr ;
1956 END IF;
1957
1958 x_return_status := G_RET_STS_ERROR;
1959 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1960
1961 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1962
1963 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1964 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');
1965 END IF;
1966
1967 IF lock_kart_for_upd_csr%ISOPEN THEN
1968 CLOSE lock_kart_for_upd_csr;
1969 END IF;
1970
1971 IF lock_var_for_del_csr%ISOPEN THEN
1972 CLOSE lock_var_for_del_csr;
1973 END IF;
1974
1975 IF lock_kart_for_del_csr%ISOPEN THEN
1976 CLOSE lock_kart_for_del_csr;
1977 END IF;
1978
1979 IF lock_scn_for_del_csr%ISOPEN THEN
1980 CLOSE lock_scn_for_del_csr;
1981 END IF;
1982
1983 IF l_get_manual_art_csr%ISOPEN THEN
1984 CLOSE l_get_manual_art_csr ;
1985 END IF;
1986
1987 IF l_check_unassigned_section_csr%ISOPEN THEN
1988 CLOSE l_check_unassigned_section_csr ;
1989 END IF;
1990
1991 x_return_status := G_RET_STS_UNEXP_ERROR;
1992 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1993
1994 WHEN OTHERS THEN
1995
1996 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1997 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving remove_template_based_articles because of EXCEPTION: '||sqlerrm);
1998 END IF;
1999
2000 IF l_get_manual_art_csr%ISOPEN THEN
2001 CLOSE l_get_manual_art_csr ;
2002 END IF;
2003
2004 IF l_check_unassigned_section_csr%ISOPEN THEN
2005 CLOSE l_check_unassigned_section_csr ;
2006 END IF;
2007
2008 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2009 p_msg_name => G_UNEXPECTED_ERROR,
2010 p_token1 => G_SQLCODE_TOKEN,
2011 p_token1_value => sqlcode,
2012 p_token2 => G_SQLERRM_TOKEN,
2013 p_token2_value => sqlerrm);
2014
2015 x_return_status := G_RET_STS_UNEXP_ERROR;
2016 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2017
2018 END remove_template_based_articles;
2019
2020 procedure copy_tc(
2021 p_api_version IN NUMBER,
2022 p_init_msg_list IN VARCHAR2,
2023 p_commit IN VARCHAR2,
2024 p_source_doc_type IN VARCHAR2,
2025 p_source_doc_id IN NUMBER,
2026 p_target_doc_type IN OUT NOCOPY VARCHAR2,
2027 p_target_doc_id IN OUT NOCOPY NUMBER,
2028 p_keep_version IN VARCHAR2,
2029 p_article_effective_date IN DATE,
2030 p_target_template_rec IN OKC_TERMS_TEMPLATES_PVT.template_rec_type,
2031 p_document_number IN VARCHAR2,
2032 p_retain_deliverable IN VARCHAR2,
2033 p_allow_duplicates IN VARCHAR2,
2034 p_keep_orig_ref IN VARCHAR2,
2035 x_return_status OUT NOCOPY VARCHAR2,
2036 x_msg_data OUT NOCOPY VARCHAR2,
2037 x_msg_count OUT NOCOPY NUMBER,
2038 p_copy_abstract_yn IN VARCHAR,
2039 p_copy_for_amendment IN VARCHAR2 default 'N',
2040 -- Fix for defaulting Contract Admin
2041 p_contract_admin_id IN NUMBER := NULL,
2042 p_legal_contact_id IN NUMBER := NULL
2043 ) IS
2044
2045 l_api_version CONSTANT NUMBER := 1;
2046 l_api_name CONSTANT VARCHAR2(30) := 'copy_tc';
2047 l_dummy_var VARCHAR2(1) :='?';
2048 l_doc_type_name OKC_BUS_DOC_TYPES_V.NAME%TYPE;
2049 l_article_effective_date DATE;
2050 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
2051 l_document_type OKC_BUS_DOC_TYPES_B.DOCUMENT_TYPE%TYPE;
2052 l_document_id NUMBER;
2053 l_get_from_library VARCHAR2(1);
2054 l_no_terms_found BOOLEAN :=FALSE;
2055 l_tmpl_usage_id NUMBER;
2056 lx_tmpl_usage_id NUMBER;
2057 l_term_instantiated VARCHAR2(1);
2058 l_approval_abstract_text OKC_TEMPLATE_USAGES.APPROVAL_ABSTRACT_TEXT%TYPE;
2059 l_contract_admin_id OKC_TEMPLATE_USAGES.CONTRACT_ADMIN_ID%TYPE;
2060 l_legal_contact_id OKC_TEMPLATE_USAGES.LEGAL_CONTACT_ID%TYPE;
2061
2062 CURSOR l_get_allwd_tmp_usages_csr IS
2063 SELECT '!' FROM OKC_ALLOWED_TMPL_USAGES
2064 WHERE template_id=p_source_doc_id
2065 AND document_type = p_target_doc_type;
2066
2067 CURSOR l_get_tmpl_csr IS
2068 SELECT * FROM OKC_TERMS_TEMPLATES_ALL
2069 WHERE template_id=p_source_doc_id;
2070
2071 CURSOR l_get_doc_type_name_csr IS
2072 SELECT name FROM OKC_BUS_DOC_TYPES_V
2073 WHERE document_type = p_target_doc_type;
2074
2075 CURSOR l_check_tmp_usage_csr IS
2076 SELECT '!' FROM OKC_TEMPLATE_USAGES
2077 WHERE document_type = p_target_doc_type
2078 AND document_id = p_target_doc_id;
2079
2080 CURSOR l_lock_usg_csr IS
2081 SELECT ROWID FROM OKC_TEMPLATE_USAGES
2082 WHERE DOCUMENT_TYPE=p_target_doc_type
2083 AND DOCUMENT_ID=p_target_doc_id
2084 FOR UPDATE NOWAIT;
2085
2086 CURSOR l_get_usage_csr IS
2087 SELECT * FROM OKC_TEMPLATE_USAGES
2088 WHERE DOCUMENT_TYPE=p_source_doc_type
2089 AND DOCUMENT_ID=p_source_doc_id;
2090
2091 CURSOR l_get_target_usage IS
2092 SELECT 'Y' FROM OKC_TEMPLATE_USAGES
2093 WHERE DOCUMENT_TYPE=p_target_doc_type
2094 AND DOCUMENT_ID=p_target_doc_id;
2095
2096 CURSOR l_get_allwd_usage_csr(b_source_doc_id NUMBER) IS
2097 SELECT * from okc_allowed_tmpl_usages where
2098 template_id=b_source_doc_id;
2099
2100 l_tmpl_rec l_get_tmpl_csr%ROWTYPE;
2101 l_usage_rec l_get_usage_csr%ROWTYPE;
2102 l_header_id NUMBER;
2103 l_rev_nbr NUMBER;
2104 l_source_change_allowed_flag VARCHAR2(1) := 'Y';
2105 lx_new_contract_admin_id NUMBER; -- Added for Bug 6080483
2106
2107 BEGIN
2108
2109 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2110 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering OKC_TERMS_COPY_PVT.copy_tc ');
2111 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Parameter List ');
2112 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_api_version : '||p_api_version);
2113 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_init_msg_list : '||p_init_msg_list);
2114 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_commit : '||p_commit);
2115 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_type : '||p_source_doc_type);
2116 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_source_doc_id : '||p_source_doc_id);
2117 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_type : '||p_target_doc_type);
2118 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_target_doc_id : '||p_target_doc_id);
2119 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_keep_version : '||p_keep_version);
2120 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_article_effective_date : '||p_article_effective_date);
2121 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_document_number : '||p_document_number);
2122 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_retain_deliverable : '||p_retain_deliverable);
2123 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_allow_duplicates : '||p_allow_duplicates);
2124 END IF;
2125
2126 -- Standard Start of API savepoint
2127 SAVEPOINT g_copy_tc_pvt;
2128
2129 -- Standard call to check for call compatibility.
2130 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2132 END IF;
2133
2134 -- Initialize message list if p_init_msg_list is set to TRUE.
2135 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2136 FND_MSG_PUB.initialize;
2137 END IF;
2138
2139 -- Initialize API return status to success
2140 x_return_status := FND_API.G_RET_STS_SUCCESS;
2141
2142 -- Checking If doc types are valid
2143
2144 IF (p_source_doc_type IS NOT NULL
2145 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
2146
2147 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2148 p_msg_name => 'OKC_INVALID_DOC_TYPE',
2149 p_token1 => 'DOCUMENT_TYPE',
2150 p_token2_value => p_source_doc_type);
2151 RAISE FND_API.G_EXC_ERROR ;
2152
2153
2154 END IF;
2155
2156 IF (p_target_doc_type IS NOT NULL
2157 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
2158
2159 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2160 p_msg_name => 'OKC_INVALID_DOC_TYPE',
2161 p_token1 => 'DOCUMENT_TYPE',
2162 p_token2_value => p_target_doc_type);
2163 RAISE FND_API.G_EXC_ERROR ;
2164
2165 END IF;
2166
2167 IF p_target_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
2168 AND p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN -- Template to Template Copy
2169
2170 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Case of Template to Template Copy ');
2172 END IF;
2173
2174
2175 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2176 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100:Start template Creation. template Name '||p_target_template_rec.template_name);
2177 END IF;
2178
2179 OKC_TERMS_TEMPLATES_GRP.create_template(
2180 p_api_version => 1,
2181 p_init_msg_list => FND_API.G_FALSE,
2182 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2183 p_commit => FND_API.G_FALSE,
2184 x_return_status => x_return_status,
2185 x_msg_count => x_msg_count,
2186 x_msg_data => x_msg_data,
2187 p_template_name => p_target_template_rec.template_name,
2188 p_template_id => Null,
2189 p_working_copy_flag => p_target_template_rec.working_copy_flag,
2190 p_intent => p_target_template_rec.intent,
2191 p_status_code => p_target_template_rec.status_code,
2192 p_start_date => p_target_template_rec.start_date,
2193 p_end_date => p_target_template_rec.end_date,
2194 p_global_flag => p_target_template_rec.global_flag,
2195 p_parent_template_id => p_target_template_rec.parent_template_id,
2196 p_print_template_id => p_target_template_rec.print_template_id,
2197 p_contract_expert_enabled=> p_target_template_rec.contract_expert_enabled,
2198 p_xprt_clause_mandatory_flag => p_target_template_rec.xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
2199 p_xprt_scn_code => p_target_template_rec.xprt_scn_code, -- bug# 4004496
2200 p_template_model_id => p_target_template_rec.template_model_id,
2201 p_instruction_text => p_target_template_rec.instruction_text,
2202 p_tmpl_numbering_scheme => p_target_template_rec.tmpl_numbering_scheme,
2203 p_description => p_target_template_rec.description,
2204 p_org_id => p_target_template_rec.org_id,
2205 p_orig_system_reference_code=> p_target_template_rec.orig_system_reference_code,
2206 p_orig_system_reference_id1=> p_target_template_rec.orig_system_reference_id1,
2207 p_orig_system_reference_id2=> p_target_template_rec.orig_system_reference_id2,
2208 p_cz_export_wf_key => p_target_template_rec.cz_export_wf_key,
2209 p_attribute_category => p_target_template_rec.attribute_category,
2210 p_attribute1 => p_target_template_rec.attribute1,
2211 p_attribute2 => p_target_template_rec.attribute2,
2212 p_attribute3 => p_target_template_rec.attribute3,
2213 p_attribute4 => p_target_template_rec.attribute4,
2214 p_attribute5 => p_target_template_rec.attribute5,
2215 p_attribute6 => p_target_template_rec.attribute6,
2216 p_attribute7 => p_target_template_rec.attribute7,
2217 p_attribute8 => p_target_template_rec.attribute8,
2218 p_attribute9 => p_target_template_rec.attribute9,
2219 p_attribute10 => p_target_template_rec.attribute10,
2220 p_attribute11 => p_target_template_rec.attribute11,
2221 p_attribute12 => p_target_template_rec.attribute12,
2222 p_attribute13 => p_target_template_rec.attribute13,
2223 p_attribute14 => p_target_template_rec.attribute14,
2224 p_attribute15 => p_target_template_rec.attribute15,
2225 --MLS for templates
2226 p_language => p_target_template_rec.language,
2227 p_translated_from_tmpl_id => p_target_template_rec.translated_from_tmpl_id,
2228
2229 x_template_id => l_template_id);
2230
2231 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2232 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);
2233 END IF;
2234
2235 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2237 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2238 RAISE FND_API.G_EXC_ERROR ;
2239 END IF;
2240
2241 p_target_doc_id := l_template_id;
2242
2243 IF p_target_template_rec.working_copy_flag='Y' THEN
2244
2245 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2246 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'160:Calling OKC_ALLOWED_TMPL_USAGES_GRP.Create_Allowed_Tmpl_Usages');
2247 END IF;
2248
2249 -- Copy Usage record.
2250 FOR cr in l_get_allwd_usage_csr(p_target_template_rec.parent_template_id) LOOP
2251 select OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL into l_tmpl_usage_id from dual;
2252 OKC_ALLOWED_TMPL_USAGES_GRP.Create_Allowed_Tmpl_Usages(
2253 p_api_version =>1,
2254 p_init_msg_list =>FND_API.G_FALSE,
2255 p_commit => FND_API.G_FALSE,
2256 x_return_status => x_return_status,
2257 x_msg_count => x_msg_count,
2258 x_msg_data => x_msg_data,
2259 p_template_id => l_template_id,
2260 p_document_type => cr.document_type,
2261 p_default_yn => cr.default_yn,
2262 p_allowed_tmpl_usages_id =>l_tmpl_usage_id,
2263 p_attribute_category =>cr.attribute_category,
2264 p_attribute1 =>cr.attribute1,
2265 p_attribute2 =>cr.attribute2,
2266 p_attribute3 =>cr.attribute3,
2267 p_attribute4 =>cr.attribute4,
2268 p_attribute5 =>cr.attribute5,
2269 p_attribute6 =>cr.attribute6,
2270 p_attribute7 =>cr.attribute7,
2271 p_attribute8 =>cr.attribute8,
2272 p_attribute9 =>cr.attribute9,
2273 p_attribute10 =>cr.attribute10,
2274 p_attribute11 =>cr.attribute11,
2275 p_attribute12 =>cr.attribute12,
2276 p_attribute13 =>cr.attribute13,
2277 p_attribute14 =>cr.attribute14,
2278 p_attribute15 =>cr.attribute15,
2279 x_allowed_tmpl_usages_id =>lx_tmpl_usage_id
2280 );
2281 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2283 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2284 RAISE FND_API.G_EXC_ERROR ;
2285 END IF;
2286 END LOOP;
2287
2288 END IF;
2289
2290 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2291 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Calling copy_section');
2292 END IF;
2293
2294 copy_sections(
2295 p_target_doc_type => p_target_doc_type,
2296 p_source_doc_type => p_source_doc_type,
2297 p_target_doc_id => l_template_id,
2298 p_source_doc_id => p_source_doc_id,
2299 p_source_version_number => NULL,
2300 p_copy_from_archive => 'N',
2301 x_return_status => x_return_status,
2302 x_msg_data => x_msg_data,
2303 x_msg_count => x_msg_count);
2304
2305 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2306 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:Finished copy_section. Return Status '||x_return_status);
2307 END IF;
2308
2309 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2311 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2312 RAISE FND_API.G_EXC_ERROR ;
2313 END IF;
2314
2315 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2316 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Entering copy_articles. ');
2317 END IF;
2318
2319 copy_articles(
2320 p_target_doc_type => p_target_doc_type,
2321 p_source_doc_type => p_source_doc_type,
2322 p_target_doc_id => l_template_id,
2323 P_source_doc_id => p_source_doc_id,
2324 p_keep_version => p_keep_version,
2325 p_article_effective_date => p_article_effective_date,
2326 p_source_version_number => NULL,
2327 p_copy_from_archive =>'N',
2328 x_return_status => x_return_status,
2329 x_msg_data => x_msg_data,
2330 x_msg_count => x_msg_count);
2331
2332 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2333 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
2334 END IF;
2335
2336 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2338 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2339 RAISE FND_API.G_EXC_ERROR ;
2340 END IF;
2341
2342 OKC_XPRT_TMPL_RULE_ASSNS_PVT.copy_template_rule_assns(
2343 p_api_version => 1,
2344 p_init_msg_list => FND_API.G_FALSE,
2345 p_commit => FND_API.G_FALSE,
2346 p_source_template_id => p_source_doc_id,
2347 p_target_template_id => l_template_id,
2348 x_return_status => x_return_status,
2349 x_msg_data => x_msg_data,
2350 x_msg_count => x_msg_count
2351 );
2352
2353 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2354 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800:Finished copy_template_rule_assns. Return Status '||x_return_status);
2355 END IF;
2356
2357 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2359 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2360 RAISE FND_API.G_EXC_ERROR ;
2361 END IF;
2362
2363 ELSIF p_source_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
2364 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN -- Doc to Doc Copy
2365
2366 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2367 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Case of Document to Document Copy ');
2368 END IF;
2369
2370 OPEN l_get_usage_csr;
2371 FETCH l_get_usage_csr INTO l_usage_rec;
2372 IF l_get_usage_csr%NOTFOUND THEN
2373 l_no_terms_found := TRUE;
2374 END IF;
2375
2376 CLOSE l_get_usage_csr;
2377
2378 If not l_no_terms_found THEN
2379 IF l_usage_rec.config_header_id IS NOT NULL THEN
2380
2381 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2382 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:Calling OKC_XPRT_CZ_INT_PVT.copy_configuration');
2383 END IF;
2384
2385 /* Call Copy Config API provided by Contract Expert Team */
2386 OKC_XPRT_CZ_INT_PVT.copy_configuration(
2387 p_api_version => 1,
2388 p_init_msg_list => OKC_API.G_FALSE,
2389 p_config_header_id =>l_usage_rec.config_header_id,
2390 p_config_rev_nbr =>l_usage_rec.config_revision_number,
2391 p_new_config_flag => FND_API.G_TRUE,
2392 x_new_config_header_id => l_header_id,
2393 x_new_config_rev_nbr => l_rev_nbr,
2394 x_return_status => x_return_status,
2395 x_msg_data => x_msg_data,
2396 x_msg_count => x_msg_count);
2397
2398 l_usage_rec.config_header_id := l_header_id;
2399 l_usage_rec.config_revision_number:=l_rev_nbr;
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,'500: After Calling OKC_XPRT_CZ_INT_PVT.copy_configuration');
2403 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: x_return_status '||x_return_status);
2404 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: x_msg_count '||x_msg_count);
2405 END IF;
2406
2407 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2409 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2410 RAISE FND_API.G_EXC_ERROR ;
2411 END IF;
2412
2413 END IF;
2414
2415
2416 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2417 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600:Create Template usage record ');
2418 END IF;
2419
2420 OPEN l_get_target_usage;
2421 FETCH l_get_target_usage into l_term_instantiated;
2422 CLOSE l_get_target_usage;
2423
2424 IF ( p_copy_abstract_yn = 'Y') THEN
2425 l_approval_abstract_text := l_usage_rec.approval_abstract_text;
2426 END IF;
2427 IF (p_copy_for_amendment = 'Y' AND l_usage_rec.contract_source_code = G_ATTACHED_CONTRACT_SOURCE) THEN
2428 l_source_change_allowed_flag := 'N';
2429 END IF;
2430
2431 -- Fix for Bug 4897464
2432 IF (p_copy_for_amendment = 'N') THEN
2433 l_usage_rec.lock_terms_flag := NULL;
2434 l_usage_rec.locked_by_user_id := NULL;
2435 END IF;
2436
2437 IF (p_allow_duplicates <> 'Y' and l_term_instantiated='Y') or l_term_instantiated is NULL THEN
2438 l_contract_admin_id := p_contract_admin_id;
2439 l_legal_contact_id := p_legal_contact_id;
2440 IF(l_contract_admin_id is null) then
2441 if(p_target_doc_type = 'QUOTE') then
2442 l_contract_admin_id := okc_terms_util_pvt.get_default_contract_admin_id(p_target_doc_type, p_target_doc_id);
2443 end if;
2444 if(l_contract_admin_id is null) then
2445 l_contract_admin_id := l_usage_rec.contract_admin_id;
2446 end if;
2447 end if;
2448 if(l_legal_contact_id is null) then
2449 l_legal_contact_id := l_usage_rec.legal_contact_id;
2450 end if;
2451 OKC_TEMPLATE_USAGES_GRP.create_template_usages(
2452 p_api_version => 1,
2453 p_init_msg_list => FND_API.G_FALSE,
2454 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2455 p_commit => FND_API.G_FALSE,
2456 x_return_status => x_return_status,
2457 x_msg_data => x_msg_data,
2458 x_msg_count => x_msg_count,
2459 p_document_type => p_target_doc_type,
2460 p_document_id => p_target_doc_id,
2461 p_template_id => l_usage_rec.template_id,
2462 p_doc_numbering_scheme => l_usage_rec.doc_numbering_scheme,
2463 p_document_number => p_document_number,
2464 p_article_effective_date => p_article_effective_date,
2465 p_config_header_id => l_usage_rec.config_header_id,
2466 p_config_revision_number => l_usage_rec.config_revision_number,
2467 p_valid_config_yn => l_usage_rec.valid_config_yn,
2468 x_document_type => l_document_type,
2469 x_document_id => l_document_id,
2470 p_approval_abstract_text => l_approval_abstract_text,
2471 p_contract_source_code => l_usage_rec.contract_source_code,
2472 p_authoring_party_code => l_usage_rec.authoring_party_code,
2473 p_source_change_allowed_flag => l_source_change_allowed_flag,
2474 -- Additional fix for bug# 4116433.
2475 p_autogen_deviations_flag => l_usage_rec.autogen_deviations_flag,
2476 p_lock_terms_flag => l_usage_rec.lock_terms_flag,
2477 p_enable_reporting_flag => l_usage_rec.enable_reporting_flag,
2478 p_locked_by_user_id => l_usage_rec.locked_by_user_id,
2479 -- Fix for defaulting Contract Admin
2480 p_contract_admin_id => l_contract_admin_id,
2481 p_legal_contact_id => l_legal_contact_id
2482 );
2483 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2484 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);
2485 END IF;
2486 END IF; -- IF p_allow_duplicates <> Y' THEN
2487
2488 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2490 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2491 RAISE FND_API.G_EXC_ERROR ;
2492 END IF;
2493
2494 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2495 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'650:Calling copy_section');
2496 END IF;
2497
2498 copy_sections( p_target_doc_type => p_target_doc_type,
2499 p_source_doc_type => p_source_doc_type,
2500 p_target_doc_id => p_target_doc_id,
2501 p_source_doc_id => p_source_doc_id,
2502 p_source_version_number=> NULL,
2503 p_copy_from_archive => 'N',
2504 p_keep_orig_ref => p_keep_orig_ref,
2505 x_return_status => x_return_status,
2506 x_msg_data => x_msg_data,
2507 x_msg_count => x_msg_count);
2508
2509 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2510 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'650:Finished copy_section. Return Status '||x_return_status);
2511 END IF;
2512
2513 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2515 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2516 RAISE FND_API.G_EXC_ERROR ;
2517 END IF;
2518
2519 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2520 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Entering copy_articles');
2521 END IF;
2522
2523 copy_articles(
2524 p_target_doc_type => p_target_doc_type,
2525 p_source_doc_type => p_source_doc_type,
2526 p_target_doc_id => p_target_doc_id,
2527 p_source_doc_id => p_source_doc_id,
2528 p_keep_version => p_keep_version,
2529 p_article_effective_date => p_article_effective_date,
2530 p_source_version_number => NULL,
2531 p_copy_from_archive =>'N',
2532 p_keep_orig_ref => p_keep_orig_ref,
2533 x_return_status => x_return_status,
2534 x_msg_data => x_msg_data,
2535 x_msg_count => x_msg_count);
2536
2537 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2538 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
2539 END IF;
2540
2541 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2543 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2544 RAISE FND_API.G_EXC_ERROR ;
2545 END IF;
2546
2547 SELECT decode(p_keep_version,'Y','N','Y') INTO l_get_from_library FROM DUAL;
2548
2549 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2550 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800:Entering copy_article_variables. ');
2551 END IF;
2552
2553 copy_article_variables(
2554 p_target_doc_type => p_target_doc_type,
2555 p_source_doc_type => p_source_doc_type,
2556 p_target_doc_id => p_target_doc_id,
2557 p_source_doc_id => p_source_doc_id,
2558 p_get_from_library => l_get_from_library,
2559 p_keep_orig_ref => p_keep_orig_ref,
2560 x_return_status => x_return_status,
2561 x_msg_data => x_msg_data,
2562 x_msg_count => x_msg_count);
2563
2564 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2565 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900:Exited copy_article_variables.Return Status '||x_return_status);
2566 END IF;
2567
2568 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2570 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2571 RAISE FND_API.G_EXC_ERROR ;
2572 END IF;
2573 END IF;
2574 ELSIF p_source_doc_type=OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
2575 AND p_target_doc_type<>OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE THEN -- Template to Doc Copy
2576
2577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'204:Case of Template to Document Copy ');
2579 END IF;
2580
2581 OPEN l_get_tmpl_csr ;
2582 FETCH l_get_tmpl_csr INTO l_tmpl_rec;
2583 CLOSE l_get_tmpl_csr ;
2584
2585
2586 l_dummy_var := '?';
2587 OPEN l_get_allwd_tmp_usages_csr;
2588 FETCH l_get_allwd_tmp_usages_csr INTO l_dummy_var;
2589 CLOSE l_get_allwd_tmp_usages_csr;
2590
2591 IF l_dummy_var = '?' THEN
2592
2593 OPEN l_get_doc_type_name_csr ;
2594 FETCH l_get_doc_type_name_csr INTO l_doc_type_name;
2595 CLOSE l_get_doc_type_name_csr ;
2596
2597 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2598 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:For this Template,Doc type usage not defined ');
2599 END IF;
2600
2601 okc_Api.Set_Message(p_app_name => G_APP_NAME,
2602 p_msg_name => 'OKC_ALLOWED_USAGE',
2603 p_token1 => 'TEMPLATE_NAME',
2604 p_token1_value => l_tmpl_rec.template_name,
2605 p_token2 => 'DOCUMENT_TYPE',
2606 p_token2_value => l_doc_type_name);
2607 RAISE FND_API.G_EXC_ERROR ;
2608
2609 END IF;
2610
2611 l_dummy_var := '?';
2612 OPEN l_check_tmp_usage_csr;
2613 FETCH l_check_tmp_usage_csr INTO l_dummy_var;
2614 CLOSE l_check_tmp_usage_csr;
2615
2616 IF l_dummy_var <> '?' THEN
2617
2618 -- Document already using a template.Need to delete those articles.
2619
2620 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2621 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Document was already using template.Removing template based articles from document');
2622 END IF;
2623
2624 remove_template_based_articles(
2625 p_doc_type => p_target_doc_type,
2626 p_doc_id => p_target_doc_id,
2627 p_retain_deliverable => p_retain_deliverable,
2628 x_return_status => x_return_status,
2629 x_msg_data => x_msg_data,
2630 x_msg_count => x_msg_count);
2631
2632 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2633 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);
2634 END IF;
2635 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2637 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2638 RAISE FND_API.G_EXC_ERROR ;
2639 END IF;
2640
2641 OPEN l_lock_usg_csr;
2642 CLOSE l_lock_usg_csr;
2643
2644 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2645 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);
2646 END IF;
2647
2648 --Added in 10+ word integration, update values for contract_source_code and authoring_party_code
2649 UPDATE OKC_TEMPLATE_USAGES
2650 SET TEMPLATE_ID = p_source_doc_id,
2651 DOC_NUMBERING_SCHEME = l_tmpl_rec.tmpl_numbering_scheme,
2652 ARTICLE_EFFECTIVE_DATE = p_article_effective_date, -- To Check and confirm with PMs
2653 CONFIG_HEADER_ID = NULL,
2654 CONFIG_REVISION_NUMBER = NULL,
2655 VALID_CONFIG_YN = NULL,
2656 LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
2657 LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
2658 LAST_UPDATE_DATE = sysdate,
2659 CONTRACT_SOURCE_CODE = G_STRUCT_CONTRACT_SOURCE,
2660 AUTHORING_PARTY_CODE = G_INTERNAL_PARTY_CODE,
2661 CONTRACT_ADMIN_ID = p_contract_admin_id,
2662 LEGAL_CONTACT_ID = p_legal_contact_id
2663 WHERE document_type = p_target_doc_type
2664 AND document_id = p_target_doc_id;
2665 ELSE
2666
2667 -- Added for Bug 6080483
2668 IF p_contract_admin_id IS NULL THEN
2669 -- Get the contract admin
2670 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2671 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'460:Calling get_sales_group_con_admin');
2672 END IF;
2673
2674 lx_new_contract_admin_id := OKC_TERMS_UTIL_PVT.get_default_contract_admin_id(p_target_doc_type, p_target_doc_id );
2675
2676 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2677 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);
2678 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);
2679 END IF;
2680 ELSE
2681 lx_new_contract_admin_id := p_contract_admin_id;
2682 END IF;
2683
2684
2685
2686
2687 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2688 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:Create template usage record ');
2689 END IF;
2690
2691 OKC_TEMPLATE_USAGES_GRP.create_template_usages(
2692 p_api_version => 1,
2693 p_init_msg_list => FND_API.G_FALSE,
2694 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2695 p_commit => FND_API.G_FALSE,
2696 x_return_status => x_return_status,
2697 x_msg_count => x_msg_count,
2698 x_msg_data => x_msg_data,
2699 p_document_type => p_target_doc_type,
2700 p_document_id => p_target_doc_id,
2701 p_template_id => p_source_doc_id,
2702 p_doc_numbering_scheme => l_tmpl_rec.tmpl_numbering_scheme,
2703 p_document_number => p_document_number,
2704 p_article_effective_date => p_article_effective_date,
2705 p_config_header_id => Null,
2706 p_config_revision_number => Null,
2707 p_valid_config_yn => Null,
2708 p_contract_admin_id => lx_new_contract_admin_id, --p_contract_admin_id, Bug 6080483
2709 p_legal_contact_id => p_legal_contact_id,
2710 x_document_type => l_document_type,
2711 x_document_id => l_document_id
2712 );
2713
2714 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2715 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);
2716 END IF;
2717
2718 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2720 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2721 RAISE FND_API.G_EXC_ERROR ;
2722 END IF;
2723
2724 END IF;
2725
2726
2727 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2728 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500:Calling copy_section');
2729 END IF;
2730
2731 copy_sections(
2732 p_target_doc_type => p_target_doc_type,
2733 p_source_doc_type => p_source_doc_type,
2734 p_target_doc_id => p_target_doc_id,
2735 p_source_doc_id => p_source_doc_id,
2736 p_source_version_number=> NULL,
2737 p_copy_from_archive => 'N',
2738 x_return_status => x_return_status,
2739 x_msg_data => x_msg_data,
2740 x_msg_count => x_msg_count);
2741
2742 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2743 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600:Finished copy_section. Return Status '||x_return_status);
2744 END IF;
2745
2746 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2748 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2749 RAISE FND_API.G_EXC_ERROR ;
2750 END IF;
2751
2752 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2753 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Entering copy_articles ');
2754 END IF;
2755
2756 copy_articles(
2757 p_target_doc_type => p_target_doc_type,
2758 p_source_doc_type => p_source_doc_type,
2759 p_target_doc_id => p_target_doc_id,
2760 P_source_doc_id => p_source_doc_id,
2761 p_keep_version => p_keep_version,
2762 p_article_effective_date => l_article_effective_date,
2763 p_source_version_number => NULL,
2764 p_copy_from_archive =>'N',
2765 x_return_status => x_return_status,
2766 x_msg_data => x_msg_data,
2767 x_msg_count => x_msg_count);
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,'700:Finished copy_articles. Return Status '||x_return_status);
2771 END IF;
2772
2773 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2775 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2776 RAISE FND_API.G_EXC_ERROR ;
2777 END IF;
2778
2779
2780 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2781 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800:Entering copy_article_variables. ');
2782 END IF;
2783
2784 copy_article_variables(
2785 p_target_doc_type => p_target_doc_type,
2786 p_source_doc_type => p_source_doc_type,
2787 p_target_doc_id => p_target_doc_id,
2788 p_source_doc_id => p_source_doc_id,
2789 p_get_from_library => 'Y',
2790 x_return_status => x_return_status,
2791 x_msg_data => x_msg_data,
2792 x_msg_count => x_msg_count);
2793
2794 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2795 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900:Exited copy_article_variables.Return Status '||x_return_status);
2796 END IF;
2797
2798 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2800 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2801 RAISE FND_API.G_EXC_ERROR ;
2802 END IF;
2803
2804
2805 END IF;
2806
2807 IF FND_API.To_Boolean( p_commit ) THEN
2808 COMMIT WORK;
2809 END IF;
2810
2811 -- Standard call to get message count and if count is 1, get message info.
2812 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2813
2814 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2815 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Leaving copy_tc');
2816 END IF;
2817
2818 EXCEPTION
2819
2820 WHEN E_Resource_Busy THEN
2821 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2822 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'000: Leaving copy_tc:E_Resource_Busy Exception');
2823 END IF;
2824
2825 IF l_get_doc_type_name_csr%ISOPEN THEN
2826 CLOSE l_get_doc_type_name_csr;
2827 END IF;
2828
2829 IF l_get_tmpl_csr%ISOPEN THEN
2830 CLOSE l_get_tmpl_csr;
2831 END IF;
2832
2833 IF l_get_tmpl_csr%ISOPEN THEN
2834 CLOSE l_get_tmpl_csr;
2835 END IF;
2836
2837 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
2838 CLOSE l_get_allwd_tmp_usages_csr;
2839 END IF;
2840
2841 IF l_check_tmp_usage_csr%ISOPEN THEN
2842 CLOSE l_check_tmp_usage_csr;
2843 END IF;
2844
2845 IF l_lock_usg_csr%ISOPEN THEN
2846 CLOSE l_lock_usg_csr;
2847 END IF;
2848
2849 IF l_get_usage_csr%ISOPEN THEN
2850 CLOSE l_get_usage_csr;
2851 END IF;
2852
2853 ROLLBACK TO g_copy_tc_pvt;
2854 x_return_status := G_RET_STS_ERROR ;
2855 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2856
2857 WHEN FND_API.G_EXC_ERROR THEN
2858
2859 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2860 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving copy_tc: OKC_API.G_EXCEPTION_ERROR Exception');
2861 END IF;
2862
2863 IF l_get_doc_type_name_csr%ISOPEN THEN
2864 CLOSE l_get_doc_type_name_csr;
2865 END IF;
2866
2867 IF l_get_tmpl_csr%ISOPEN THEN
2868 CLOSE l_get_tmpl_csr;
2869 END IF;
2870
2871 IF l_get_tmpl_csr%ISOPEN THEN
2872 CLOSE l_get_tmpl_csr;
2873 END IF;
2874
2875 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
2876 CLOSE l_get_allwd_tmp_usages_csr;
2877 END IF;
2878
2879 IF l_check_tmp_usage_csr%ISOPEN THEN
2880 CLOSE l_check_tmp_usage_csr;
2881 END IF;
2882
2883 IF l_lock_usg_csr%ISOPEN THEN
2884 CLOSE l_lock_usg_csr;
2885 END IF;
2886
2887 IF l_get_usage_csr%ISOPEN THEN
2888 CLOSE l_get_usage_csr;
2889 END IF;
2890
2891 ROLLBACK TO g_copy_tc_pvt;
2892 x_return_status := G_RET_STS_ERROR ;
2893 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2894
2895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2896 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2897 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_tc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2898 END IF;
2899
2900 IF l_get_doc_type_name_csr%ISOPEN THEN
2901 CLOSE l_get_doc_type_name_csr;
2902 END IF;
2903
2904 IF l_get_tmpl_csr%ISOPEN THEN
2905 CLOSE l_get_tmpl_csr;
2906 END IF;
2907
2908 IF l_get_tmpl_csr%ISOPEN THEN
2909 CLOSE l_get_tmpl_csr;
2910 END IF;
2911
2912 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
2913 CLOSE l_get_allwd_tmp_usages_csr;
2914 END IF;
2915
2916 IF l_check_tmp_usage_csr%ISOPEN THEN
2917 CLOSE l_check_tmp_usage_csr;
2918 END IF;
2919
2920 IF l_lock_usg_csr%ISOPEN THEN
2921 CLOSE l_lock_usg_csr;
2922 END IF;
2923
2924 IF l_get_usage_csr%ISOPEN THEN
2925 CLOSE l_get_usage_csr;
2926 END IF;
2927
2928 ROLLBACK TO g_copy_tc_pvt;
2929 x_return_status := G_RET_STS_UNEXP_ERROR ;
2930 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2931
2932 WHEN OTHERS THEN
2933 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2934 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_tc because of EXCEPTION: '||sqlerrm);
2935 END IF;
2936
2937 IF l_get_doc_type_name_csr%ISOPEN THEN
2938 CLOSE l_get_doc_type_name_csr;
2939 END IF;
2940
2941 IF l_get_tmpl_csr%ISOPEN THEN
2942 CLOSE l_get_tmpl_csr;
2943 END IF;
2944
2945 IF l_get_tmpl_csr%ISOPEN THEN
2946 CLOSE l_get_tmpl_csr;
2947 END IF;
2948
2949 IF l_get_allwd_tmp_usages_csr%ISOPEN THEN
2950 CLOSE l_get_allwd_tmp_usages_csr;
2951 END IF;
2952
2953 IF l_check_tmp_usage_csr%ISOPEN THEN
2954 CLOSE l_check_tmp_usage_csr;
2955 END IF;
2956
2957 IF l_lock_usg_csr%ISOPEN THEN
2958 CLOSE l_lock_usg_csr;
2959 END IF;
2960
2961 ROLLBACK TO g_copy_tc_pvt;
2962 x_return_status := G_RET_STS_UNEXP_ERROR ;
2963 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2964 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2965 END IF;
2966 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2967
2968 END copy_tc;
2969
2970
2971 procedure copy_archived_doc(
2972 p_api_version IN NUMBER,
2973 p_init_msg_list IN VARCHAR2,
2974 p_commit IN VARCHAR2,
2975 p_source_doc_type IN VARCHAR2,
2976 p_source_doc_id IN NUMBER,
2977 p_source_version_number IN NUMBER,
2978 p_target_doc_type IN VARCHAR2,
2979 p_target_doc_id IN NUMBER,
2980 p_document_number IN VARCHAR2,
2981 p_allow_duplicates IN VARCHAR2,
2982 x_return_status OUT NOCOPY VARCHAR2,
2983 x_msg_data OUT NOCOPY VARCHAR2,
2984 x_msg_count OUT NOCOPY NUMBER
2985 ) IS
2986
2987 l_api_version CONSTANT NUMBER := 1;
2988 l_api_name CONSTANT VARCHAR2(30) := 'copy_archived_doc';
2989 l_dummy_var VARCHAR2(1) :='?';
2990 l_document_type VARCHAR2(30);
2991 l_document_id NUMBER;
2992 l_term_found VARCHAR2(1):= NULL;
2993
2994 CURSOR l_get_usage_csr IS
2995 SELECT * FROM OKC_TEMPLATE_USAGES_H
2996 WHERE DOCUMENT_TYPE=p_source_doc_type
2997 AND DOCUMENT_ID=p_source_doc_id
2998 AND MAJOR_VERSION=p_source_version_number;
2999
3000 CURSOR l_get_target_usage IS
3001 SELECT 'Y' FROM OKC_TEMPLATE_USAGES
3002 WHERE DOCUMENT_TYPE=p_target_doc_type
3003 AND DOCUMENT_ID=p_target_doc_id;
3004
3005 CURSOR l_get_variables_csr IS
3006 SELECT KART.ID CAT_ID,
3007 VAR.VARIABLE_CODE,
3008 VAR.VARIABLE_TYPE,
3009 VAR.EXTERNAL_YN,
3010 VAR.ATTRIBUTE_VALUE_SET_ID,
3011 VAR.VARIABLE_VALUE,
3012 VAR.VARIABLE_VALUE_ID,
3013 VAR.OVERRIDE_GLOBAL_YN
3014 FROM OKC_K_ART_VARIABLES_H VAR,
3015 OKC_K_ARTICLES_B KART,
3016 OKC_K_ARTICLES_BH KART1
3017 WHERE KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ID
3018 AND VAR.CAT_ID=KART1.ID
3019 AND KART.DOCUMENT_TYPE=p_target_doc_type
3020 AND KART.DOCUMENT_ID=p_target_doc_id
3021 AND KART1.DOCUMENT_TYPE=p_source_doc_type
3022 AND KART1.DOCUMENT_ID=p_source_doc_id
3023 AND KART1.MAJOR_VERSION = p_source_version_number
3024 AND KART.ORIG_SYSTEM_REFERENCE_CODE=G_COPY
3025 AND VAR.MAJOR_VERSION = p_source_version_number;
3026
3027 TYPE CatList IS TABLE OF OKC_K_ART_VARIABLES.CAT_ID%TYPE INDEX BY BINARY_INTEGER;
3028 TYPE VarList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_CODE%TYPE INDEX BY BINARY_INTEGER;
3029 TYPE VarTypeList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE INDEX BY BINARY_INTEGER;
3030 TYPE ExternalList IS TABLE OF OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE INDEX BY BINARY_INTEGER;
3031 TYPE ValSetList IS TABLE OF OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE INDEX BY BINARY_INTEGER;
3032 TYPE VarValList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE INDEX BY BINARY_INTEGER;
3033 TYPE VarIdList IS TABLE OF OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE INDEX BY BINARY_INTEGER;
3034 TYPE OverrideGlobalYnList IS TABLE OF OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE INDEX BY BINARY_INTEGER;
3035
3036 cat_tbl CatList;
3037 var_tbl VarList;
3038 var_type_tbl VarTypeList;
3039 external_yn_tbl ExternalList;
3040 value_set_id_tbl ValSetList;
3041 var_value_tbl VarValList;
3042 var_value_id_tbl VarIdList;
3043 override_global_yn_tbl OverrideGlobalYnList;
3044
3045 l_usage_rec l_get_usage_csr%ROWTYPE;
3046
3047
3048
3049 BEGIN
3050
3051 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering OKC_TERMS_COPY_PVT.copy_archived_doc ');
3053 END IF;
3054
3055 -- Standard Start of API savepoint
3056 SAVEPOINT g_copy_archived_doc_pvt;
3057
3058 -- Standard call to check for call compatibility.
3059 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3061 END IF;
3062
3063 -- Initialize message list if p_init_msg_list is set to TRUE.
3064 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3065 FND_MSG_PUB.initialize;
3066 END IF;
3067
3068 -- Initialize API return status to success
3069 x_return_status := FND_API.G_RET_STS_SUCCESS;
3070
3071 /* Create Template Usage Record */
3072
3073 OPEN l_get_usage_csr;
3074 FETCH l_get_usage_csr INTO l_usage_rec;
3075 IF (l_get_usage_csr%NOTFOUND ) THEN
3076 CLOSE l_get_usage_csr;
3077 RAISE NO_DATA_FOUND;
3078 END IF;
3079 CLOSE l_get_usage_csr;
3080
3081 OPEN l_get_target_usage;
3082 FETCH l_get_target_usage into l_term_found;
3083 CLOSE l_get_target_usage;
3084
3085 IF l_usage_rec.config_header_id IS NOT NULL THEN
3086
3087 /* Call Copy Config API provided by Contract Expert Team */
3088
3089 NULL;
3090
3091 /* After Copy Set. l_usage_rec.config_header_id and l_usage_rec.Config_revision_number to values returned from Above API */
3092
3093 END IF;
3094
3095 IF ( p_allow_duplicates <>'Y' and l_term_found='Y') OR l_term_found IS NULL THEN
3096 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3097 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200:Create usage record ');
3098 END IF;
3099
3100 OKC_TEMPLATE_USAGES_GRP.create_template_usages(
3101 p_api_version => 1,
3102 p_init_msg_list => FND_API.G_FALSE,
3103 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3104 p_commit => FND_API.G_FALSE,
3105 x_return_status => x_return_status,
3106 x_msg_data => x_msg_data,
3107 x_msg_count => x_msg_count,
3108 p_document_type => p_target_doc_type,
3109 p_document_id => p_target_doc_id,
3110 p_template_id => l_usage_rec.template_id,
3111 p_doc_numbering_scheme => l_usage_rec.doc_numbering_scheme,
3112 p_document_number => p_document_number,
3113 p_article_effective_date => sysdate,
3114 p_config_header_id => l_usage_rec.config_header_id,
3115 p_config_revision_number => l_usage_rec.config_revision_number,
3116 p_valid_config_yn => l_usage_rec.valid_config_yn,
3117 x_document_type => l_document_type,
3118 x_document_id => l_document_id,
3119 -- Additional fix for bug# 4116433.
3120 p_approval_abstract_text => l_usage_rec.approval_abstract_text,
3121 p_contract_source_code => l_usage_rec.contract_source_code,
3122 p_authoring_party_code => l_usage_rec.authoring_party_code,
3123 p_source_change_allowed_flag => l_usage_rec.source_change_allowed_flag,
3124 p_autogen_deviations_flag => l_usage_rec.autogen_deviations_flag
3125 );
3126 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3127 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:Finished usage record creation.Return Status'||x_return_status);
3128 END IF;
3129
3130 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3132 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3133 RAISE FND_API.G_EXC_ERROR ;
3134 END IF;
3135 END IF; -- IF ( p_allow_duplicates <>'Y' and l_term_found='Y') OR l_term_found is NULL THEN
3136
3137 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3138 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Calling copy_section');
3139 END IF;
3140
3141 copy_sections(
3142 p_target_doc_type => p_target_doc_type,
3143 p_source_doc_type => p_source_doc_type,
3144 p_target_doc_id => p_target_doc_id,
3145 p_source_doc_id => p_source_doc_id,
3146 p_source_version_number => p_source_version_number,
3147 p_copy_from_archive => 'Y',
3148 x_return_status => x_return_status,
3149 x_msg_data => x_msg_data,
3150 x_msg_count => x_msg_count);
3151
3152 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300:Finished copy_section. Return Status '||x_return_status);
3154 END IF;
3155
3156 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3158 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3159 RAISE FND_API.G_EXC_ERROR ;
3160 END IF;
3161
3162 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3163 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Entering copy_articles. ');
3164 END IF;
3165
3166 copy_articles(
3167 p_target_doc_type => p_target_doc_type,
3168 p_source_doc_type => p_source_doc_type,
3169 p_target_doc_id => p_target_doc_id,
3170 p_source_doc_id => p_source_doc_id,
3171 p_keep_version => 'N',
3172 p_article_effective_date => Null,
3173 p_source_version_number => p_source_version_number,
3174 p_copy_from_archive =>'Y',
3175 x_return_status => x_return_status,
3176 x_msg_data => x_msg_data,
3177 x_msg_count => x_msg_count);
3178
3179 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700:Finished copy_articles. Return Status '||x_return_status);
3181 END IF;
3182
3183 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3185 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3186 RAISE FND_API.G_EXC_ERROR ;
3187 END IF;
3188
3189 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3190 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Copying article variables');
3191 END IF;
3192
3193 -- Bulk collecting
3194 OPEN l_get_variables_csr;
3195 FETCH l_get_variables_csr BULK COLLECT INTO cat_tbl,
3196 var_tbl,
3197 var_type_tbl,
3198 external_yn_tbl,
3199 value_set_id_tbl,
3200 var_value_tbl,
3201 var_value_id_tbl,
3202 override_global_yn_tbl;
3203 CLOSE l_get_variables_csr;
3204
3205 -- Bulk inserting
3206 IF cat_tbl.COUNT > 0 THEN
3207 FORALL i IN cat_tbl.FIRST..cat_tbl.LAST
3208 INSERT INTO OKC_K_ART_VARIABLES(cat_id,
3209 variable_code,
3210 variable_type,
3211 external_yn,
3212 attribute_value_set_id,
3213 variable_value,
3214 variable_value_id,
3215 override_global_yn,
3216 object_version_number,
3217 creation_date,
3218 created_by,
3219 last_update_date,
3220 last_updated_by,
3221 last_update_login)
3222 VALUES (cat_tbl(i),
3223 var_tbl(i),
3224 var_type_tbl(i),
3225 external_yn_tbl(i),
3226 value_set_id_tbl(i),
3227 var_value_tbl(i),
3228 var_value_id_tbl(i),
3229 override_global_yn_tbl(i),
3230 1,
3231 sysdate,
3232 Fnd_Global.User_Id,
3233 sysdate,
3234 Fnd_Global.User_Id,
3235 Fnd_Global.Login_Id);
3236
3237 END IF;
3238
3239 IF FND_API.To_Boolean( p_commit ) THEN
3240 COMMIT WORK;
3241 END IF;
3242
3243 -- Standard call to get message count and if count is 1, get message info.
3244 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
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,'1000: Leaving copy_archived_doc');
3248 END IF;
3249
3250 EXCEPTION
3251
3252 WHEN NO_DATA_FOUND THEN
3253 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3254 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving copy_archived_doc No Terms Data in Source');
3255 END IF;
3256 null;
3257
3258 WHEN FND_API.G_EXC_ERROR THEN
3259
3260 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3261 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving copy_archived_doc: OKC_API.G_EXCEPTION_ERROR Exception');
3262 END IF;
3263
3264 IF l_get_usage_csr%ISOPEN THEN
3265 CLOSE l_get_usage_csr;
3266 END IF;
3267
3268 IF l_get_variables_csr%ISOPEN THEN
3269 CLOSE l_get_variables_csr;
3270 END IF;
3271
3272 ROLLBACK TO g_copy_archived_doc_pvt;
3273 x_return_status := G_RET_STS_ERROR ;
3274 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3275
3276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3277
3278 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3279 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_archived_doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3280 END IF;
3281
3282 IF l_get_usage_csr%ISOPEN THEN
3283 CLOSE l_get_usage_csr;
3284 END IF;
3285
3286 IF l_get_variables_csr%ISOPEN THEN
3287 CLOSE l_get_variables_csr;
3288 END IF;
3289
3290 ROLLBACK TO g_copy_archived_doc_pvt;
3291 x_return_status := G_RET_STS_UNEXP_ERROR ;
3292 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3293
3294 WHEN OTHERS THEN
3295
3296 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3297 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_archived_doc because of EXCEPTION: '||sqlerrm);
3298 END IF;
3299
3300 IF l_get_usage_csr%ISOPEN THEN
3301 CLOSE l_get_usage_csr;
3302 END IF;
3303
3304 IF l_get_variables_csr%ISOPEN THEN
3305 CLOSE l_get_variables_csr;
3306 END IF;
3307
3308 ROLLBACK TO g_copy_archived_doc_pvt;
3309 x_return_status := G_RET_STS_UNEXP_ERROR ;
3310
3311 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3312 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3313 END IF;
3314
3315 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3316
3317 END copy_archived_doc;
3318
3319 END OKC_TERMS_COPY_PVT;