[Home] [Help]
PACKAGE BODY: APPS.OKC_CLM_PKG
Source
1 PACKAGE BODY okc_clm_pkg AS
2 /* $Header: OKCCLMPB.pls 120.8 2012/01/06 11:35:50 harchand noship $ */
3 ------------------------------------------------------------------------------
4 -- GLOBAL CONSTANTS
5 ------------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_CLM_PKG';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
8
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
11 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
12 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
13
14
15 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
16 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
17
18 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
19 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
20 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
21
22 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
23 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
24 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
25 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
26
27 G_BUY_ITEM_VARIABLE_NAME CONSTANT VARCHAR2(50) := 'OKC$B_ITEM';
28 G_BUY_ITEM_CAT_VAR_NAME CONSTANT VARCHAR2(50) := 'OKC$B_ITEM_CATEGORY';
29 G_SELL_ITEM_VARIABLE_NAME CONSTANT VARCHAR2(50) := 'OKC$S_ITEM';
30 G_SELL_ITEM_CAT_VAR_NAME CONSTANT VARCHAR2(50) := 'OKC$S_ITEM_CATEGORY';
31
32 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
33 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
34 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
35 G_LOCK_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_LOCK_RECORD_DELETED;
36
37 ---------------------------------------------------
38 -- Procedure: get_user_defined_variables
39 ---------------------------------------------------
40 PROCEDURE get_user_defined_variables (
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
43 p_doc_type IN VARCHAR2,
44 p_doc_id IN NUMBER,
45 p_org_id IN NUMBER,
46 p_intent IN VARCHAR2,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_udf_var_value_tbl OUT NOCOPY okc_xprt_xrule_values_pvt.udf_var_value_tbl_type
51 )
52 IS
53 l_api_name VARCHAR2(30) := 'get_user_defined_variables';
54 l_api_version CONSTANT NUMBER := 1.0;
55
56 BEGIN
57
58 -- start debug log
59 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
61 G_MODULE||l_api_name,
62 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
63 END IF;
64
65 x_return_status := G_RET_STS_SUCCESS;
66 -- call expert API for getting udv values
67 OKC_XPRT_XRULE_VALUES_PVT.get_user_defined_variables (
68 p_api_version => l_api_version,
69 p_init_msg_list => p_init_msg_list ,
70 p_doc_type => p_doc_type,
71 p_doc_id => p_doc_id,
72 p_org_id => p_org_id,
73 p_intent => p_intent,
74 x_return_status => x_return_status,
75 x_msg_data => x_msg_data,
76 x_msg_count => x_msg_count,
77 x_udf_var_value_tbl => x_udf_var_value_tbl
78 );
79 --- If any errors happen abort API
80 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85
86 END get_user_defined_variables;
87 ---------------------------------------------------
88 -- Procedure: get_udv_with_procedures
89 ---------------------------------------------------
90 PROCEDURE get_udv_with_procedures (
91 p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
93 p_doc_type IN VARCHAR2,
94 p_doc_id IN NUMBER,
95 p_org_id IN NUMBER,
96 p_intent IN VARCHAR2,
97 x_return_status OUT NOCOPY VARCHAR2,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_udf_var_value_tbl OUT NOCOPY okc_xprt_xrule_values_pvt.udf_var_value_tbl_type
101 )
102 IS
103
104 l_api_name VARCHAR2(30) := 'get_udv_with_procedures';
105 l_api_version CONSTANT NUMBER := 1.0;
106
107 BEGIN
108 -- start debug log
109 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
110 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
111 G_MODULE||l_api_name,
112 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
113 END IF;
114
115 x_return_status := G_RET_STS_SUCCESS;
116
117 --call expert API to get Procedural udv values
118 OKC_XPRT_XRULE_VALUES_PVT.get_udv_with_procedures (
119 p_api_version => l_api_version,
120 p_init_msg_list => p_init_msg_list ,
121 p_doc_type => p_doc_type,
122 p_doc_id => p_doc_id,
123 p_org_id => p_org_id,
124 p_intent => p_intent,
125 x_return_status => x_return_status,
126 x_msg_data => x_msg_data,
127 x_msg_count => x_msg_count,
128 x_udf_var_value_tbl => x_udf_var_value_tbl
129 );
130
131 --- If any errors happen abort API
132 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138 END get_udv_with_procedures;
139
140
141 PROCEDURE get_clm_udv_value(
142 p_doc_type IN VARCHAR2,
143 p_doc_id IN NUMBER,
144 p_variable_code IN VARCHAR2,
145 p_clm_ref1 IN VARCHAR2,
146 p_clm_ref2 IN VARCHAR2,
147 p_clm_ref3 IN VARCHAR2,
148 p_clm_ref4 IN VARCHAR2,
149 p_clm_ref5 IN VARCHAR2,
150 p_clm_source IN VARCHAR2,
151 p_variable_name IN VARCHAR2,
152 p_uda_mode IN VARCHAR2,
153 x_variable_value OUT NOCOPY VARCHAR2
154 )
155 IS
156
157 l_api_name VARCHAR2(30) := 'get_clm_udv_value';
158
159
160 CURSOR c_get_doc_type_class (doc_type VARCHAR2) IS
161 SELECT document_type_class
162 FROM okc_bus_doc_types_b
163 WHERE document_type = doc_type;
164
165 l_doc_type_class VARCHAR2(100);
166 l_entity_name VARCHAR2(100);
167 l_pk1_value NUMBER;
168 l_pk2_value NUMBER := -1;
169 l_attr_grp VARCHAR2(100);
170 l_attr VARCHAR2(100);
171 l_address_type VARCHAR2(100);
172
173 CURSOR c_get_po_draft_id (c_po_header_id NUMBER) IS
174 SELECT draft_id
175 FROM po_headers_draft_all
176 WHERE po_header_id = c_po_header_id;
177
178 l_variable_value VARCHAR2(4000);
179
180 TYPE cur_typ IS REF CURSOR;
181 c cur_typ;
182 query_str VARCHAR2(4000);
183
184 FUNCTION extract_value_from_xml (p_address_type VARCHAR2,
185 p_attribute VARCHAR2,
186 p_xml_string VARCHAR2)
187 RETURN VARCHAR2 IS
188 l_start_tag VARCHAR2(100);
189 l_end_tag VARCHAR2(100);
190 l_start_pos NUMBER;
191 l_num_chars NUMBER;
192 l_value VARCHAR2(4000);
193 BEGIN
194 l_start_tag := '<'||p_address_type||'_'||p_attribute||'>';
195 l_end_tag := '</'||p_address_type||'_'||p_attribute||'>';
196 l_start_pos := InStr(p_xml_string,l_start_tag)+Length(l_start_tag);
197 l_num_chars := InStr(p_xml_string,l_end_tag) - l_start_pos ;
198 l_value := SubStr(p_xml_string,l_start_pos,l_num_chars);
199 RETURN l_value;
200 END;
201
202 BEGIN
203
204 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
206 G_MODULE||l_api_name,
207 '100: Entered '||G_PKG_NAME ||'.'||'get_clm_udv_value');
208 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
209 G_MODULE||l_api_name,
210 '100: Parameter p_doc_type'||' '||p_doc_type);
211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
212 G_MODULE||l_api_name,
213 '100: Parameter p_doc_id'||' '||p_doc_id);
214 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
215 G_MODULE||l_api_name,
216 '100: Parameter p_variable_code'||' '||p_variable_code);
217 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
218 G_MODULE||l_api_name,
219 '100: Parameter p_clm_ref1'||' '||p_clm_ref1);
220 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
221 G_MODULE||l_api_name,
222 '100: Parameter p_clm_ref2'||' '||p_clm_ref2);
223 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
224 G_MODULE||l_api_name,
225 '100: Parameter p_clm_ref3'||' '||p_clm_ref3);
226 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
227 G_MODULE||l_api_name,
228 '100: Parameter p_clm_ref4'||' '||p_clm_ref4);
229 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
230 G_MODULE||l_api_name,
231 '100: Parameter p_clm_ref5'||' '||p_clm_ref5);
232 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
233 G_MODULE||l_api_name,
234 '100: Parameter p_clm_source'||' '||p_clm_source);
235 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
236 G_MODULE||l_api_name,
237 '100: Parameter p_variable_name'||' '||p_variable_name);
238 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
239 G_MODULE||l_api_name,
240 '100: Parameter p_uda_mode'||' '||p_uda_mode);
241 END IF;
242
243 OPEN c_get_doc_type_class(p_doc_type);
244 FETCH c_get_doc_type_class INTO l_doc_type_class;
245 CLOSE c_get_doc_type_class;
246
247 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
249 G_MODULE||l_api_name,
250 '110: value l_doc_type_class'||' '||l_doc_type_class);
251 END IF;
252
253 IF p_clm_source = 'UDA' THEN
254
255 IF l_doc_type_class = 'PO' THEN
256 l_entity_name := 'PO_HEADER_EXT_ATTRS';
257 ELSIF l_doc_type_class = 'SOURCING' THEN
258 l_entity_name := 'PON_AUC_HDRS_EXT_ATTRS';
259 ELSE
260 l_entity_name := 'PO_REQ_HEADER_EXT_ATTRS';
261 END IF;
262
263 l_pk1_value := p_doc_id;
264
265 IF l_entity_name = 'PO_HEADER_EXT_ATTRS' THEN
266 OPEN c_get_po_draft_id(p_doc_id);
267 FETCH c_get_po_draft_id INTO l_pk2_value;
268 CLOSE c_get_po_draft_id;
269 ELSE
270 l_pk2_value := NULL;
271 END IF;
272
273 l_attr_grp := p_clm_ref2;
274 l_attr := p_clm_ref3;
275 l_address_type := p_clm_ref4;
276
277 IF p_clm_ref1='single' THEN
278
279 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
281 G_MODULE||l_api_name,
282 '120: Parameter l_entity_name'||' '||l_entity_name);
283 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
284 G_MODULE||l_api_name,
285 '120: Parameter l_pk1_value'||' '||l_pk1_value);
286 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
287 G_MODULE||l_api_name,
288 '120: Parameter l_pk2_value'||' '||l_pk2_value);
289 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
290 G_MODULE||l_api_name,
291 '120: Parameter l_attr_grp'||' '||l_attr_grp);
292 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
293 G_MODULE||l_api_name,
294 '120: Parameter l_attr'||' '||l_attr);
295 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
296 G_MODULE||l_api_name,
297 '120: Parameter p_uda_mode'||' '||p_uda_mode);
298 END IF;
299
300 /* To remove compile time dependency on PO package
301 l_variable_value := PO_UDA_PUB.get_single_attr_value(p_entity_code => l_entity_name,
302 pk1_value => l_pk1_value,
303 pk2_value => l_pk2_value,
304 p_attr_grp_int_name => l_attr_grp,
305 p_attr_int_name => l_attr,
306 p_mode => p_uda_mode
307 );
308 */
309 EXECUTE IMMEDIATE 'select PO_UDA_PUB.get_single_attr_value(p_entity_code => :1,
310 pk1_value => :2,
311 pk2_value => :3,
312 p_attr_grp_int_name => :4,
313 p_attr_int_name => :5,
314 p_mode => :6) from dual'
315 INTO l_variable_value
316 USING l_entity_name,l_pk1_value,l_pk2_value,l_attr_grp,l_attr,p_uda_mode;
317
318 ELSIF p_clm_ref1='address' THEN
319
320 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
322 G_MODULE||l_api_name,
323 '130: Parameter l_entity_name'||' '||l_entity_name);
324 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
325 G_MODULE||l_api_name,
326 '130: Parameter l_pk1_value'||' '||l_pk1_value);
327 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
328 G_MODULE||l_api_name,
329 '130: Parameter l_pk2_value'||' '||l_pk2_value);
330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
331 G_MODULE||l_api_name,
332 '130: Parameter p_clm_ref2'||' '||p_clm_ref2);
333 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
334 G_MODULE||l_api_name,
335 '130: Parameter p_clm_ref3'||' '||p_clm_ref3);
336 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
337 G_MODULE||l_api_name,
338 '130: Parameter p_clm_ref4'||' '||p_clm_ref4);
339 END IF;
340
341 /* To remove compile time dependency on PO package
342 l_variable_value := PO_UDA_PUB.get_address_attr_value(p_template_id => NULL,
343 p_entity_code => l_entity_name,
344 pk1_value => l_pk1_value,
345 pk2_value => l_pk2_value,
346 pk3_value => NULL,
347 pk4_value => NULL,
348 pk5_value => NULL,
349 p_attr_grp_id => NULL,
350 p_attr_grp_int_name => p_clm_ref2,
351 p_attr_id => NULL,
352 p_attr_int_name => p_clm_ref3,
353 p_address_type => p_clm_ref4);
354 */
355 EXECUTE IMMEDIATE 'select PO_UDA_PUB.get_address_attr_value(p_template_id => NULL,
356 p_entity_code => :2,
357 pk1_value => :3,
358 pk2_value => :4,
359 pk3_value => NULL,
360 pk4_value => NULL,
361 pk5_value => NULL,
362 p_attr_grp_id => NULL,
363 p_attr_grp_int_name => :9,
364 p_attr_id => NULL,
365 p_attr_int_name => :11,
366 p_address_type => :12) from dual'
367 INTO l_variable_value
368 USING l_entity_name,l_pk1_value,l_pk2_value,p_clm_ref2,p_clm_ref3,p_clm_ref4;
369
370
371 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
372 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
373 G_MODULE||l_api_name,
374 '140: Parameter l_variable_value'||' '||l_variable_value);
375 END IF;
376
377
378 IF (p_clm_ref3 = 'addressdtlsxml' OR p_clm_ref3 = 'contactdtlsxml') THEN
379
380 l_variable_value := extract_value_from_xml(p_address_type => p_clm_ref4,
381 p_attribute => p_clm_ref5,
382 p_xml_string => l_variable_value);
383 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
385 G_MODULE||l_api_name,
386 '150: Parameter l_variable_value'||' '||l_variable_value);
387 END IF;
388
389 END IF;
390
391 END IF; -- p_clm_ref1
392 ELSIF p_clm_source = 'named' THEN
393 IF p_clm_ref2 = 'PO_HEADERS_ALL' THEN
394
395 query_str := 'select '||p_clm_ref3||' from PO_HEADERS_ALL where po_header_id = '||p_doc_id;
396 OPEN c FOR query_str;
397 FETCH c INTO l_variable_value;
398 CLOSE c;
399 END IF;
400 END IF; -- clm_source
401
402 x_variable_value := l_variable_value;
403
404 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
405 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
406 G_MODULE||l_api_name,
407 '100: Parameter l_variable_value'||' '||x_variable_value);
408 END IF;
409 END get_clm_udv_value;
410
411
412
413 PROCEDURE get_clm_udv (
414 p_api_version IN NUMBER,
415 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
416 p_doc_type IN VARCHAR2,
417 p_doc_id IN NUMBER,
418 p_org_id IN NUMBER,
419 p_intent IN VARCHAR2,
420 x_return_status OUT NOCOPY VARCHAR2,
421 x_msg_data OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_udf_var_value_tbl OUT NOCOPY okc_xprt_xrule_values_pvt.udf_var_value_tbl_type
424 )
425 IS
426
427 CURSOR csr_get_clm_udv (p_doc_type VARCHAR2,p_doc_id NUMBER, p_intent VARCHAR2, p_org_id NUMBER) IS
428 SELECT var.variable_code variable_code, --Removed USER$ to resolve Rule firing for UDV with Procedures
429 var.clm_ref1,var.clm_ref2,var.clm_ref3,var.clm_ref4,var.clm_ref5, var.clm_source, vart.variable_name
430 FROM okc_bus_variables_b var, okc_bus_variables_tl vart
431 WHERE var.clm_source IS NOT NULL
432 AND var.variable_code = vart.variable_code
433 AND vart.language = 'US'
434 AND var.variable_source = 'M'
435 AND var.variable_code IN
436 (SELECT distinct rcon.object_code variable_code -- LHS of Condition from Template rule
437 FROM okc_xprt_rule_hdrs_all rhdr,
438 okc_xprt_rule_conditions rcon,
439 okc_template_usages tuse,
440 okc_xprt_template_rules trule
441 WHERE tuse.document_id = p_doc_id
442 AND tuse.document_type = p_doc_type
443 AND tuse.template_id = trule.template_id
444 AND trule.rule_id = rhdr.rule_id
445 AND rhdr.rule_id = rcon.rule_id
446 AND rcon.object_type = 'VARIABLE'
447 AND rhdr.status_code = 'ACTIVE'
448 -- AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
449 GROUP BY rcon.object_code
450 UNION
451 SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Template rule
452 FROM okc_xprt_rule_hdrs_all rhdr,
453 okc_xprt_rule_conditions rcon,
454 okc_template_usages tuse,
455 okc_xprt_template_rules trule
456 WHERE tuse.document_id = p_doc_id
457 AND tuse.document_type = p_doc_type
458 AND tuse.template_id = trule.template_id
459 AND trule.rule_id = rhdr.rule_id
460 AND rhdr.rule_id = rcon.rule_id
461 AND rcon.object_value_type = 'VARIABLE'
462 AND rhdr.status_code = 'ACTIVE'
463 -- AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
464 GROUP BY rcon.object_value_code
465 UNION
466 SELECT distinct rcon.object_code variable_code -- LHS of Condition from Global Rule
467 FROM okc_xprt_rule_hdrs_all rhdr,
468 okc_xprt_rule_conditions rcon
469 WHERE rhdr.rule_id = rcon.rule_id
470 AND rhdr.org_id = p_org_id
471 AND rhdr.intent = p_intent
472 AND rhdr.org_wide_flag = 'Y'
473 AND rcon.object_type = 'VARIABLE'
474 AND rhdr.status_code = 'ACTIVE'
475 -- AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
476 GROUP BY rcon.object_code
477 UNION
478 SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Global Rule
479 FROM okc_xprt_rule_hdrs_all rhdr,
480 okc_xprt_rule_conditions rcon
481 WHERE rhdr.rule_id = rcon.rule_id
482 AND rhdr.org_id = p_org_id
483 AND rhdr.intent = p_intent
484 AND rhdr.org_wide_flag = 'Y'
485 AND rcon.object_value_type = 'VARIABLE'
486 AND rhdr.status_code = 'ACTIVE'
487 -- AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
488 GROUP BY rcon.object_value_code);
489
490 /*
491 CURSOR csr_get_uniq_proc (p_sequence_id NUMBER) IS
492 SELECT distinct procedure_name procedure_name
493 FROM okc_xprt_deviations_t
494 WHERE run_id = p_sequence_id;
495
496
497 CURSOR csr_get_vars_for_proc (p_procedure_name VARCHAR2, p_sequence_id NUMBER) IS
498 SELECT distinct variable_code variable_code
499 FROM okc_xprt_deviations_t
500 WHERE run_id = p_sequence_id
501 AND procedure_name = p_procedure_name;
502 */
503 l_api_name VARCHAR2(30) := 'get_clm_udv';
504 l_api_version CONSTANT NUMBER := 1.0;
505
506 --l_sql_stmt LONG;
507 l_sequence_id NUMBER;
508 var_tbl_cnt NUMBER := 1;
509 l_udf_var_value_tbl OKC_XPRT_XRULE_VALUES_PVT.udf_var_value_tbl_type;
510 /*
511 --bug 8501694-kkolukul: Multiple values variables used in expert
512 l_udf_with_proc_mul_val_tbl OKC_XPRT_XRULE_VALUES_PVT.udf_var_value_tbl_type;
513 l_hook_used NUMBER;
514 */
515 -- CLM Changes
516 l_clm_udf_tbl OKC_XPRT_XRULE_VALUES_PVT.udf_var_value_tbl_type;
517
518 TYPE VariableCodeList IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; -- changed for R12
519 variableCode_tbl VariableCodeList;
520
521 i NUMBER := 1;
522
523 l_variable_value VARCHAR2(2500);
524
525 BEGIN
526
527 -- start debug log
528 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
529 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
530 G_MODULE||l_api_name,
531 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
532 END IF;
533
534 x_return_status := G_RET_STS_SUCCESS;
535
536
537 FOR csr_get_clm_udv_rec IN csr_get_clm_udv(p_doc_type, p_doc_id, p_intent, p_org_id)
538 LOOP
539
540 get_clm_udv_value(p_doc_type=>p_doc_type,
541 p_doc_id=>p_doc_id,
542 p_variable_code => csr_get_clm_udv_rec.variable_code,
543 p_clm_ref1=>csr_get_clm_udv_rec.clm_ref1,
544 p_clm_ref2=>csr_get_clm_udv_rec.clm_ref2,
545 p_clm_ref3=>csr_get_clm_udv_rec.clm_ref3,
546 p_clm_ref4=>csr_get_clm_udv_rec.clm_ref4,
547 p_clm_ref5=>csr_get_clm_udv_rec.clm_ref5,
548 p_clm_source=>csr_get_clm_udv_rec.clm_source,
549 p_variable_name=>csr_get_clm_udv_rec.variable_name,
550 p_uda_mode=>'INTERNAL_VALUE',
551 x_variable_value=>l_variable_value
552 );
553
554 l_udf_var_value_tbl(i).variable_code := csr_get_clm_udv_rec.variable_code;
555 l_udf_var_value_tbl(i).variable_value_id:= l_variable_value;
556
557 i:= i +1 ;
558
559 END LOOP;
560
561 -- After executing add the list to the output table
562 IF l_udf_var_value_tbl IS NOT NULL THEN
563 IF l_udf_var_value_tbl.count > 0 THEN
564 FOR i IN l_udf_var_value_tbl.first..l_udf_var_value_tbl.last LOOP
565 x_udf_var_value_tbl(var_tbl_cnt).variable_code := 'USER$' || l_udf_var_value_tbl(i).variable_code; --Appended USER$ to resolve Rule firing for UDV with Procedures
566 x_udf_var_value_tbl(var_tbl_cnt).variable_value_id := l_udf_var_value_tbl(i).variable_value_id;
567 var_tbl_cnt := var_tbl_cnt + 1;
568 END LOOP;
569 END IF;
570 END IF;
571
572 -- Clear out the PL/SQL tables
573 variableCode_tbl.DELETE;
574 IF l_udf_var_value_tbl.Count > 0 THEN
575
576 FOR i IN l_udf_var_value_tbl.FIRST..l_udf_var_value_tbl.LAST
577 LOOP
578 l_udf_var_value_tbl.DELETE(i);
579 END LOOP;
580
581 END IF;
582
583
584 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
586 G_MODULE||l_api_name,
587 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
588 END IF;
589
590 EXCEPTION
591
592
593 WHEN FND_API.G_EXC_ERROR THEN
594 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
595 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
596 G_MODULE||l_api_name,
597 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
598 END IF;
599
600 x_return_status := G_RET_STS_ERROR ;
601 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
602
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
605 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
606 G_MODULE||l_api_name,
607 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
608 END IF;
609
610 x_return_status := G_RET_STS_UNEXP_ERROR ;
611 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
612
613 WHEN OTHERS THEN
614 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
615 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
616 G_MODULE||l_api_name,
617 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
618 END IF;
619
620 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
621 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
622 END IF;
623 x_return_status := G_RET_STS_UNEXP_ERROR ;
624 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
625
626 END get_clm_udv;
627
628
629 PROCEDURE set_clm_udv (
630 p_api_version IN NUMBER,
631 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
632 p_document_type IN VARCHAR2,
633 p_document_id IN NUMBER,
634 p_output_error IN VARCHAR2 := FND_API.G_TRUE,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_msg_data OUT NOCOPY VARCHAR2,
637 x_msg_count OUT NOCOPY NUMBER
638 )
639 IS
640
641 l_api_version CONSTANT NUMBER := 1.0;
642 l_api_name CONSTANT VARCHAR2(30) := 'set_clm_udv';
643 l_variable_value VARCHAR2(2500) := NULL;
644 l_previous_var_code okc_bus_variables_b.variable_code%TYPE := '-99';
645 l_return_status VARCHAR2(10) := NULL;
646
647 CURSOR csr_get_clm_udv IS
648 SELECT VB.variable_code,
649 KA.id,
650 KA.article_version_id,
651 VBT.variable_name,
652 VB.clm_source,
653 VB.clm_ref1,
654 VB.clm_ref2,
655 VB.clm_ref3,
656 VB.clm_ref4,
657 VB.clm_ref5
658 FROM okc_k_articles_b KA,
659 okc_k_art_variables KV,
660 okc_bus_variables_b VB,
661 okc_bus_variables_tl VBT
662 WHERE VB.variable_code = KV.variable_code
663 and VB.variable_code = VBT.variable_code
664 and VBT.language = 'US'
665 AND KA.id = KV.cat_id
666 AND VB.clm_source is not null
667 AND KA.document_type = p_document_type
668 AND KA.document_id = p_document_id
669 ORDER BY VB.variable_code;
670
671 BEGIN
672 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
673 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered set_clm_udv');
674 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_document_type:'||p_document_type);
675 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_document_id:'||p_document_id);
676 END IF;
677
678 /* Standard call to check for call compatibility */
679 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
681 END IF;
682
683 /* Initialize message list if p_init_msg_list is set to TRUE */
684 IF FND_API.to_Boolean( p_init_msg_list ) THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687
688 /* Initialize API return status to success */
689 x_return_status := G_RET_STS_SUCCESS;
690
691 /* Clear the temp table */
692 DELETE FROM OKC_TERMS_CLM_UDV_T;
693
694 FOR csr_get_clm_udv_rec IN csr_get_clm_udv LOOP
695
696 /* Get the variable value */
697 IF l_previous_var_code <> csr_get_clm_udv_rec.variable_code THEN
698
699 l_variable_value := NULL;
700
701 get_clm_udv_value(p_doc_type=>p_document_type,
702 p_doc_id=>p_document_id,
703 p_variable_code => csr_get_clm_udv_rec.variable_code,
704 p_clm_ref1=>csr_get_clm_udv_rec.clm_ref1,
705 p_clm_ref2=>csr_get_clm_udv_rec.clm_ref2,
706 p_clm_ref3=>csr_get_clm_udv_rec.clm_ref3,
707 p_clm_ref4=>csr_get_clm_udv_rec.clm_ref4,
708 p_clm_ref5=>csr_get_clm_udv_rec.clm_ref5,
709 p_clm_source=>csr_get_clm_udv_rec.clm_source,
710 p_variable_name=>csr_get_clm_udv_rec.variable_name,
711 p_uda_mode=>'DISPLAY_VALUE',
712 x_variable_value=>l_variable_value
713 );
714
715 END IF;
716
717 /* Insert data into the temp table */
718 IF l_variable_value IS NOT NULL THEN
719
720 INSERT INTO OKC_TERMS_CLM_UDV_T
721 (
722 VARIABLE_CODE,
723 VARIABLE_VALUE,
724 DOC_TYPE,
725 DOC_ID,
726 ARTICLE_VERSION_ID,
727 CAT_ID
728 )
729 VALUES
730 (
731 csr_get_clm_udv_rec.variable_code, -- VARIABLE_CODE
732 l_variable_value, -- VARIABLE_VALUE
733 p_document_type, -- DOCUMENT_TYPE
734 p_document_id, -- DOCUMENT_ID
735 csr_get_clm_udv_rec.article_version_id, -- ARTICLE_VERSION_ID
736 csr_get_clm_udv_rec.id -- CAT_ID
737 );
738 END IF;
739
740 l_previous_var_code := csr_get_clm_udv_rec.variable_code;
741
742 END LOOP;
743
744 IF p_output_error = FND_API.G_TRUE AND FND_MSG_PUB.Count_Msg > 0 THEN
745
746 x_return_status := G_RET_STS_ERROR;
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
749
750 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
751 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving set_clm_udv');
752 END IF;
753
754 EXCEPTION
755 WHEN FND_API.G_EXC_ERROR THEN
756
757 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
758 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving set_clm_udv : OKC_API.G_EXCEPTION_ERROR Exception');
759 END IF;
760
761 IF csr_get_clm_udv%ISOPEN THEN
762 CLOSE csr_get_clm_udv;
763 END IF;
764
765 x_return_status := G_RET_STS_ERROR ;
766 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
767
768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
769
770 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
771 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving set_clm_udv : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
772 END IF;
773
774 IF csr_get_clm_udv%ISOPEN THEN
775 CLOSE csr_get_clm_udv;
776 END IF;
777
778 x_return_status := G_RET_STS_UNEXP_ERROR ;
779 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
780
781 WHEN OTHERS THEN
782
783 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
784 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving set_clm_udv because of EXCEPTION: '||sqlerrm);
785 END IF;
786
787 IF csr_get_clm_udv%ISOPEN THEN
788 CLOSE csr_get_clm_udv;
789 END IF;
790
791 x_return_status := G_RET_STS_UNEXP_ERROR ;
792 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
793 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
794 END IF;
795 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
796
797
798 END set_clm_udv;
799
800
801 --CLM Changes Ends
802
803
804 ---------------------------------------------------
805 -- Procedure: get_default_scn_id
806 ---------------------------------------------------
807
808 PROCEDURE GET_DEFAULT_SCN_CODE (
809 p_api_version IN NUMBER,
810 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
811 p_article_id IN NUMBER,
812 p_article_version_id IN NUMBER,
813 p_doc_id IN NUMBER,
814 p_doc_type IN VARCHAR2,
815 x_default_scn_code OUT NOCOPY OKC_SECTIONS_B.SCN_CODE%TYPE,
816 x_return_status OUT NOCOPY VARCHAR2
817 )
818 IS
819 l_api_name VARCHAR2(30) := 'get_default_scn_id';
820 l_api_version CONSTANT NUMBER := 1.0;
821
822 CURSOR udv_value_csr(p_doc_id number, p_article_id number, p_article_version_id NUMBER)
823 IS
824 SELECT kvar.variable_value_id FROM okc_k_art_variables kvar,okc_k_articles_b kart
825 WHERE kart.id = kvar.cat_id
826 AND kart.document_id = p_doc_id
827 AND kvar.variable_value_id IN
828 (SELECT avs.variable_value_id FROM okc_art_var_sections avs
829 WHERE avs.article_id = p_article_id
830 AND avs.article_version_id = p_article_version_id
831 )
832 AND kvar.variable_value_id IS NOT NULL;
833
834 CURSOR var_type_csr(p_variable_code VARCHAR2) IS
835 SELECT bv.variable_type FROM okc_bus_variables_b bv
836 WHERE bv.variable_code = p_variable_code;
837
838
839 CURSOR art_var_dtls_csr(p_article_id NUMBER,p_article_version_id NUMBER) IS
840 SELECT variable_value_id,variable_value,variable_code
841 FROM okc_art_var_sections
842 WHERE article_id = p_article_id
843 AND article_version_id = p_article_version_id;
844
845 CURSOR get_varcode_from_art(p_article_id NUMBER) IS
846 SELECT variable_code
847 FROM okc_article_versions
848 WHERE article_version_id = p_article_version_id;
849
850
851 CURSOR def_scn_code_csr(p_variable_value VARCHAR2,p_article_id NUMBER,p_article_version_id NUMBER) IS
852 SELECT avs.scn_CODE FROM okc_art_var_sections avs
853 WHERE avs.variable_value = p_variable_value
854 AND avs.article_id = p_article_id
855 AND avs.article_version_id = p_article_version_id;
856
857
858
859 CURSOR doc_details_csr ( p_doc_id NUMBER) IS
860 SELECT art.ORG_ID,art.ARTICLE_INTENT,kart.document_type
861 FROM okc_articles_all art,okc_k_articles_b kart
862 WHERE art.ARTICLE_ID = kart.sav_sae_id
863 AND kart.document_id = p_doc_id
864 AND ROWNUM=1;
865
866 CURSOR var_value_csr(p_var_value_id NUMBER,p_article_id NUMBER,p_article_version_id NUMBER) IS
867 SELECT variable_value
868 FROM okc_art_var_sections
869 WHERE variable_value_id = p_var_value_id
870 AND article_id = p_article_id
871 AND article_version_id = p_article_version_id;
872
873 CURSOR get_var_name_csr(p_var_code VARCHAR2) IS
874 SELECT variable_name
875 FROM okc_bus_variables_tl
876 WHERE variable_code = p_var_code;
877
878 /* To remove compile time dependency on CLM DB changes
879
880 CURSOR get_clm_format_po_csr(p_doc_id NUMBER) IS
881 SELECT clm_document_format
882 FROM po_headers_all
883 WHERE po_header_id = p_doc_id;
884
885 CURSOR get_clm_format_sol_csr(p_doc_id NUMBER) IS
886 SELECT document_format
887 FROM pon_auction_headers_all
888 WHERE auction_header_id = p_doc_id;
889
890 */
891
892 l_var_code VARCHAR2(30) := NULL;
893 l_var_value VARCHAR2(30) := NULL;
894 l_var_name VARCHAR2(100);
895 l_var_value_id NUMBER := NULL ;
896 l_var_type VARCHAR2(1) := NULL ;
897 l_def_scn_code VARCHAR2(30) := NULL ;
898 l_return VARCHAR2(1) := 'N';
899 l_return2 VARCHAR2(1) := 'N';
900 l_org_id NUMBER := NULL ;
901 l_intent VARCHAR2(1) := NULL ;
902 l_doc_type VARCHAR2(30) := NULL;
903 i NUMBER := 1;
904 l_msg_data VARCHAR2(300) := NULL;
905 l_msg_count NUMBER := NULL;
906
907 l_count NUMBER :=0;
908 l_sys_var_value_tbl okc_xprt_xrule_values_pvt.var_value_tbl_type;
909 l_udf_var_value_tbl okc_xprt_xrule_values_pvt.udf_var_value_tbl_type;
910
911 BEGIN
912 -- start debug log
913 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
914 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
915 G_MODULE||l_api_name,
916 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
917 END IF;
918
919 x_return_status := G_RET_STS_SUCCESS;
920 --get doc_details
921 OPEN doc_details_csr(p_doc_id) ;
922 FETCH doc_details_csr INTO l_org_id,l_intent,l_doc_type;
923 CLOSE doc_details_csr;
924 OPEN art_var_dtls_csr(p_article_id,p_article_version_id);
925 FETCH art_var_dtls_csr INTO l_var_value_id,l_var_value,l_var_code;
926 IF (art_var_dtls_csr%NOTFOUND) THEN
927 l_return := 'N';
928 ELSE
929 l_return := 'Y';
930 END IF;
931
932 IF (l_return = 'Y') THEN
933 --Bug# 9855895
934
935 if l_var_code is null then
936 open get_varcode_from_art(p_article_id);
937 fetch get_varcode_from_art into l_var_code;
938 close get_varcode_from_art;
939 end if;
940
941 OPEN get_var_name_csr(l_var_code);
942 FETCH get_var_name_csr INTO l_var_name;
943 CLOSE get_var_name_csr;
944
945 IF l_var_name = 'Format' THEN
946
947 IF SubStr(p_doc_type,1,12) = 'SOLICITATION' THEN --Bug 9953583
948 /* To remove compile time dependency on CLM DB changes
949 OPEN get_clm_format_sol_csr(p_doc_id);
950 FETCH get_clm_format_sol_csr INTO l_var_value;
951 CLOSE get_clm_format_sol_csr;
952 */
953 EXECUTE IMMEDIATE 'SELECT document_format
954 FROM pon_auction_headers_all
955 WHERE auction_header_id = :1'
956 INTO l_var_value
957 USING p_doc_id;
958 ELSE
959 /* To remove compile time dependency on CLM DB changes
960 OPEN get_clm_format_po_csr(p_doc_id);
961 FETCH get_clm_format_po_csr INTO l_var_value;
962 CLOSE get_clm_format_po_csr;
963 */
964 EXECUTE IMMEDIATE 'SELECT clm_document_format
965 FROM po_headers_all
966 WHERE po_header_id = :1'
967 INTO l_var_value
968 USING p_doc_id;
969 END IF;
970
971 OPEN def_scn_code_csr(l_var_value,p_article_id,p_article_version_id);
972 FETCH def_scn_code_csr INTO l_def_scn_code;
973 CLOSE def_scn_code_csr;
974
975 ELSE
976
977 OPEN var_type_csr(l_var_code) ;
978 FETCH var_type_csr INTO l_var_type;
979 CLOSE var_type_csr;
980
981 IF (l_var_type = 'S') THEN
982 --call to get_system_variables
983 get_system_variables
984 ( p_api_version => l_api_version,
985 p_init_msg_list => p_init_msg_list,
986 x_return_status => x_return_status,
987 x_msg_data => l_msg_data,
988 x_msg_count => l_msg_count,
989 p_doc_type => l_doc_type,
990 p_doc_id => p_doc_id,
991 p_only_doc_variables => FND_API.G_FALSE,
992 x_sys_var_value_tbl => l_sys_var_value_tbl);
993
994
995 --for results table, get default_scn_code from okc_art_var_sections
996 FOR i IN l_sys_var_value_tbl.FIRST..l_sys_var_value_tbl.LAST LOOP
997 IF (l_sys_var_value_tbl(i).variable_code = l_var_code) THEN
998 OPEN def_scn_code_csr(l_sys_var_value_tbl(i).variable_value_id,p_article_id,p_article_version_id);
999 FETCH def_scn_code_csr INTO l_def_scn_code;
1000 CLOSE def_scn_code_csr;
1001 END IF;
1002 END LOOP;
1003 END IF;
1004
1005 IF (l_var_type = 'P') THEN
1006 --call to get_udv_with_procedure
1007 get_udv_with_procedures(
1008 p_api_version => l_api_version,
1009 p_init_msg_list => p_init_msg_list ,
1010 p_doc_type => l_doc_type,
1011 p_doc_id => p_doc_id,
1012 p_org_id => l_org_id,
1013 p_intent => l_intent,
1014 x_return_status => x_return_status,
1015 x_msg_data => l_msg_data,
1016 x_msg_count => l_msg_count,
1017 x_udf_var_value_tbl => l_udf_var_value_tbl
1018 );
1019 --for results table, get default_scn_code from okc_art_var_sections
1020 FOR i IN l_udf_var_value_tbl.FIRST..l_udf_var_value_tbl.LAST LOOP
1021 IF (l_udf_var_value_tbl(i).variable_code = l_var_code) THEN
1022 OPEN var_value_csr(l_udf_var_value_tbl(i).variable_value_id,p_article_id,p_article_version_id);
1023 FETCH var_value_csr INTO l_var_value;
1024 CLOSE var_value_csr;
1025
1026 OPEN def_scn_code_csr(l_var_value,p_article_id,p_article_version_id);
1027 FETCH def_scn_code_csr INTO l_def_scn_code;
1028 CLOSE def_scn_code_csr;
1029
1030 END IF;
1031 END LOOP;
1032 END IF;
1033
1034 IF (l_var_type = 'U') THEN
1035 OPEN udv_value_csr(p_doc_id,p_article_id,p_article_version_id);
1036 FETCH udv_value_csr INTO l_var_value_id;
1037 IF (udv_value_csr%NOTFOUND) THEN
1038 l_return2 := 'N';
1039 ELSE
1040 l_return2 := 'Y';
1041 END IF;
1042
1043 CLOSE udv_value_csr;
1044
1045 IF (l_return2 = 'Y') THEN
1046 OPEN var_value_csr(l_var_value_id,p_article_id,p_article_version_id);
1047 FETCH var_value_csr INTO l_var_value;
1048 CLOSE var_value_csr;
1049
1050 OPEN def_scn_code_csr(l_var_value,p_article_id,p_article_version_id);
1051 FETCH def_scn_code_csr INTO l_def_scn_code;
1052 CLOSE def_scn_code_csr;
1053 END IF; --l_return2
1054 END IF; --udv_type if condn
1055
1056 END IF;
1057
1058 END IF; --l_return if condn
1059 --- If any errors happen abort API
1060 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065 x_default_scn_code := l_def_scn_code;
1066
1067 END get_default_scn_code;
1068
1069 ---------------------------------------------------
1070 -- Procedure: get_system_variables
1071 ---------------------------------------------------
1072
1073 PROCEDURE get_system_variables (
1074 p_api_version IN NUMBER,
1075 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_data OUT NOCOPY VARCHAR2,
1078 x_msg_count OUT NOCOPY NUMBER,
1079 p_doc_type IN VARCHAR2,
1080 p_doc_id IN NUMBER,
1081 p_only_doc_variables IN VARCHAR2 := FND_API.G_TRUE,
1082 x_sys_var_value_tbl OUT NOCOPY okc_xprt_xrule_values_pvt.var_value_tbl_type
1083 )
1084 IS
1085 l_api_name VARCHAR2(30) := 'get_system_variables';
1086 l_api_version CONSTANT NUMBER := 1.0;
1087
1088 l_sys_var_value_tbl okc_xprt_xrule_values_pvt.var_value_tbl_type;
1089 BEGIN
1090
1091 OKC_XPRT_XRULE_VALUES_PVT.get_system_variables(
1092 p_api_version => l_api_version,
1093 p_init_msg_list => p_init_msg_list,
1094 x_return_status => x_return_status,
1095 x_msg_data => x_msg_data,
1096 x_msg_count => x_msg_count,
1097 p_doc_type => p_doc_type,
1098 p_doc_id => p_doc_id,
1099 p_only_doc_variables => p_only_doc_variables,
1100 x_sys_var_value_tbl => l_sys_var_value_tbl);
1101
1102 END get_system_variables;
1103
1104 PROCEDURE clm_remove_dup_sections( p_document_type IN VARCHAR2,
1105 p_document_id IN NUMBER,
1106 x_return_status OUT NOCOPY VARCHAR2,
1107 x_msg_data OUT NOCOPY VARCHAR2,
1108 x_msg_count OUT NOCOPY NUMBER)
1109 IS
1110
1111 TYPE DupScnIdList IS TABLE OF OKC_SECTIONS_B.ID%TYPE INDEX BY BINARY_INTEGER;
1112 --TYPE ScnCodeList IS TABLE OF OKC_SECTIONS_B.SCN_CODE%TYPE INDEX BY BINARY_INTEGER;
1113
1114 l_api_name CONSTANT VARCHAR2(30) := 'clm_remove_dup_scn_art';
1115 --l_dup_scn_code_tbl ScnCodeList;
1116 l_dup_scn_id_tbl DupScnIdList;
1117 l_remaining_scn_ids VARCHAR2(4000);
1118 l_del_stmt VARCHAR2(4000);
1119
1120 CURSOR l_get_dup_sec_csr IS
1121 SELECT scn_code, Count(scn_code)
1122 FROM okc_sections_b
1123 WHERE document_type = p_document_type
1124 AND document_id = p_document_id
1125 GROUP BY scn_code
1126 HAVING (Count(scn_code) >1 );
1127
1128 CURSOR l_get_dup_sec_ids_csr(p_scn_code VARCHAR2) IS
1129 SELECT id
1130 FROM okc_sections_b
1131 WHERE document_type = p_document_type
1132 AND document_id = p_document_id
1133 AND scn_code = p_scn_code
1134 ORDER BY id;
1135
1136 BEGIN
1137 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1138 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: entered okc_terms_copy_pvt.clm_remove_dup_scn_art');
1139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: param p_document_type: ' || p_document_type);
1140 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: param p_document_id: ' || p_document_id);
1141 END IF;
1142
1143 /*for each duplicate section_code in the document find their
1144 first section_id and replace all the remaining ones with first Id*/
1145 FOR rec IN l_get_dup_sec_csr LOOP
1146 OPEN l_get_dup_sec_ids_csr(rec.scn_code);
1147 FETCH l_get_dup_sec_ids_csr BULK COLLECT INTO l_dup_scn_id_tbl;
1148 CLOSE l_get_dup_sec_ids_csr;
1149 FOR i IN l_dup_scn_id_tbl.first..l_dup_scn_id_tbl.last LOOP
1150 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'400: in FOR loop');
1152 END IF;
1153
1154 l_remaining_scn_ids := NULL;
1155
1156 IF (i <> 1) THEN
1157 if (i NOT IN (l_dup_scn_id_tbl.last) )THEN
1158 l_remaining_scn_ids := l_remaining_scn_ids || l_dup_scn_id_tbl(i) || ', ';
1159 ELSE
1160 l_remaining_scn_ids := l_remaining_scn_ids || l_dup_scn_id_tbl(i);
1161 END IF;
1162 END IF;
1163 END LOOP;
1164
1165 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'500: l_dup_scn_id_tbl(1): '|| l_dup_scn_id_tbl(1));
1167 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: l_remaining_scn_ids: '|| l_remaining_scn_ids);
1168 END IF;
1169
1170 IF l_remaining_scn_ids IS NOT NULL THEN
1171 UPDATE okc_k_articles_b
1172 SET scn_id = l_dup_scn_id_tbl(1),
1173 display_sequence = display_sequence + ((SELECT Max(display_sequence) FROM okc_k_articles_b
1174 WHERE document_type = p_document_type AND document_id = p_document_id
1175 AND scn_id = l_dup_scn_id_tbl(1)))
1176 WHERE document_type = p_document_type
1177 AND document_id = p_document_id
1178 AND scn_id IN (l_remaining_scn_ids);
1179
1180 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1181 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'700: Update done on okc_k_articles_b table ');
1182 END IF;
1183
1184 /*When all the articles are updated with the first section_id, delete the remaining sections from okc_sections_b table*/
1185
1186 l_del_stmt := 'DELETE FROM okc_sections_b WHERE id IN (' || l_remaining_scn_ids || ')';
1187 EXECUTE IMMEDIATE l_del_stmt;
1188
1189 END IF;
1190
1191 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'800: Delete done on okc_sections_b table ');
1193 END IF;
1194 END LOOP; -- FOR rec IN l_get_dup_sec_csr LOOP
1195
1196 x_return_status := 'S';
1197 EXCEPTION
1198
1199 WHEN NO_DATA_FOUND THEN
1200 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1201 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving clm_remove_dup_scn_art No Data in Source');
1202 END IF;
1203 null;
1204
1205 WHEN FND_API.G_EXC_ERROR THEN
1206
1207 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving clm_remove_dup_scn_art: OKC_API.G_EXCEPTION_ERROR Exception');
1209 END IF;
1210
1211 IF l_get_dup_sec_csr%ISOPEN THEN
1212 CLOSE l_get_dup_sec_csr;
1213 END IF;
1214
1215 IF l_get_dup_sec_ids_csr%ISOPEN THEN
1216 CLOSE l_get_dup_sec_ids_csr;
1217 END IF;
1218
1219 x_return_status := G_RET_STS_UNEXP_ERROR ;
1220
1221 WHEN OTHERS THEN
1222
1223 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1224 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_archived_doc because of EXCEPTION: '||sqlerrm);
1225 END IF;
1226
1227 IF l_get_dup_sec_csr%ISOPEN THEN
1228 CLOSE l_get_dup_sec_csr;
1229 END IF;
1230
1231 IF l_get_dup_sec_ids_csr%ISOPEN THEN
1232 CLOSE l_get_dup_sec_ids_csr;
1233 END IF;
1234
1235 x_return_status := G_RET_STS_UNEXP_ERROR ;
1236
1237 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1238 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1239 END IF;
1240
1241 END clm_remove_dup_sections;
1242
1243
1244 PROCEDURE clm_remove_dup_articles( p_document_type IN VARCHAR2,
1245 p_document_id IN NUMBER,
1246 x_return_status OUT NOCOPY VARCHAR2,
1247 x_msg_data OUT NOCOPY VARCHAR2,
1248 x_msg_count OUT NOCOPY NUMBER)
1249 IS
1250
1251 l_api_name CONSTANT VARCHAR2(30) := 'clm_remove_dup_articles';
1252
1253 TYPE ArticleVersionIdList IS TABLE OF OKC_K_ARTICLES_B.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
1254
1255 l_art_ver_ids_tbl ArticleVersionIdList;
1256 l_dup_articles VARCHAR2(4000);
1257 l_del_stmt VARCHAR2(4000);
1258
1259 CURSOR l_get_dup_articles_csr IS
1260 SELECT sav_sae_id, scn_id
1261 FROM okc_k_articles_b
1262 WHERE document_type = p_document_type
1263 AND document_id = p_document_id
1264 GROUP BY sav_sae_id, scn_id
1265 HAVING (Count(sav_sae_id) >1 );
1266
1267 CURSOR l_get_dup_art_ids(p_article_id NUMBER, p_scn_id NUMBER) IS
1268 SELECT id
1269 FROM okc_k_articles_b
1270 where document_type = p_document_type
1271 AND document_id = p_document_id
1272 AND sav_sae_id = p_article_id
1273 AND scn_id = p_scn_id
1274 ORDER BY id asc;
1275
1276 BEGIN
1277 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1278 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: entered okc_terms_copy_pvt.clm_remove_dup_articles');
1279 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: param p_document_type: ' || p_document_type);
1280 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: param p_document_id: ' || p_document_id);
1281 END IF;
1282
1283 FOR rec IN l_get_dup_articles_csr LOOP
1284 OPEN l_get_dup_art_ids(rec.sav_sae_id, rec.scn_id);
1285 FETCH l_get_dup_art_ids BULK COLLECT INTO l_art_ver_ids_tbl;
1286 CLOSE l_get_dup_art_ids;
1287
1288 l_dup_articles := NULL;
1289 FOR i IN l_art_ver_ids_tbl.first..l_art_ver_ids_tbl.last LOOP
1290 IF i<> 1 THEN
1291 IF i NOT IN (l_art_ver_ids_tbl.last) THEN
1292 l_dup_articles := l_dup_articles || l_art_ver_ids_tbl(i) || ', ';
1293 ELSE
1294 l_dup_articles := l_dup_articles || l_art_ver_ids_tbl(i);
1295 END IF;
1296 END IF;
1297 END LOOP;
1298
1299 IF l_dup_articles IS NOT NULL THEN
1300 l_del_stmt := 'DELETE FROM okc_k_articles_b WHERE id IN (' || l_dup_articles || ')';
1301 EXECUTE IMMEDIATE l_del_stmt;
1302 END IF;
1303
1304 END LOOP;
1305
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN
1308 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1309 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving clm_remove_dup_articles No Data in Source');
1310 END IF;
1311 null;
1312
1313 WHEN FND_API.G_EXC_ERROR THEN
1314
1315 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1316 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving clm_remove_dup_articles: OKC_API.G_EXCEPTION_ERROR Exception');
1317 END IF;
1318
1319 IF l_get_dup_articles_csr%ISOPEN THEN
1320 CLOSE l_get_dup_articles_csr;
1321 END IF;
1322
1323 IF l_get_dup_art_ids%ISOPEN THEN
1324 CLOSE l_get_dup_art_ids;
1325 END IF;
1326
1327 x_return_status := G_RET_STS_UNEXP_ERROR ;
1328
1329 WHEN OTHERS THEN
1330
1331 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1332 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving copy_archived_doc because of EXCEPTION: '||sqlerrm);
1333 END IF;
1334
1335 IF l_get_dup_articles_csr%ISOPEN THEN
1336 CLOSE l_get_dup_articles_csr;
1337 END IF;
1338
1339 IF l_get_dup_art_ids%ISOPEN THEN
1340 CLOSE l_get_dup_art_ids;
1341 END IF;
1342
1343 x_return_status := G_RET_STS_UNEXP_ERROR ;
1344
1345 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1346 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1347 END IF;
1348
1349 END clm_remove_dup_articles;
1350
1351 PROCEDURE clm_remove_dup_scn_art( p_document_type IN VARCHAR2,
1352 p_document_id IN NUMBER,
1353 x_return_status OUT NOCOPY VARCHAR2,
1354 x_msg_data OUT NOCOPY VARCHAR2,
1355 x_msg_count OUT NOCOPY NUMBER)
1356 IS
1357
1358 l_api_name CONSTANT VARCHAR2(30) := 'clm_remove_dup_scn_art';
1359
1360 BEGIN
1361 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: entered okc_terms_copy_pvt.clm_remove_dup_scn_art');
1363 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: param p_document_type: ' || p_document_type);
1364 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: param p_document_id: ' || p_document_id);
1365 END IF;
1366
1367 clm_remove_dup_sections( p_document_type => p_document_type,
1368 p_document_id => p_document_id,
1369 x_return_status => x_return_status,
1370 x_msg_data => x_msg_data,
1371 x_msg_count => x_msg_count);
1372
1373 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1374 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'400: After clm_remove_dup_sections : x_return_status' || x_return_status);
1375 END IF;
1376
1377
1378 clm_remove_dup_articles( p_document_type => p_document_type,
1379 p_document_id => p_document_id,
1380 x_return_status => x_return_status,
1381 x_msg_data => x_msg_data,
1382 x_msg_count => x_msg_count);
1383
1384 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1385 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'500: After clm_remove_dup_articles : x_return_status' || x_return_status);
1386 END IF;
1387
1388 END clm_remove_dup_scn_art;
1389
1390 PROCEDURE insert_usages_row( p_document_type IN VARCHAR2,
1391 p_document_id IN NUMBER,
1392 p_template_id IN NUMBER,
1393 p_doc_numbering_scheme IN NUMBER,
1394 p_document_number IN VARCHAR2,
1395 p_article_effective_date IN DATE,
1396 p_config_header_id IN NUMBER,
1397 p_config_revision_number IN NUMBER,
1398 p_valid_config_yn IN VARCHAR2,
1399 p_orig_system_reference_code IN VARCHAR2 ,
1400 p_orig_system_reference_id1 IN NUMBER,
1401 p_orig_system_reference_id2 IN NUMBER,
1402 p_lock_terms_flag IN VARCHAR2,
1403 p_locked_by_user_id IN NUMBER,
1404 p_primary_template IN VARCHAR2,
1405 x_return_status OUT NOCOPY VARCHAR2,
1406 x_msg_data OUT NOCOPY VARCHAR2,
1407 x_msg_count OUT NOCOPY NUMBER)
1408 IS
1409 l_api_name CONSTANT VARCHAR2(30) := 'insert_usages_row';
1410
1411 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1412 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
1413 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
1414 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
1415 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
1416 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
1417 l_authoring_party_code OKC_TEMPLATE_USAGES.authoring_party_code%type;
1418
1419 l_temp_exists VARCHAR2(1) := 'N';
1420
1421 CURSOR l_get_temp_already_exists_csr IS
1422 SELECT 'Y' FROM okc_mlp_template_usages
1423 WHERE document_type = p_document_type
1424 AND document_id = p_document_id
1425 AND template_id = p_template_id;
1426
1427 BEGIN
1428 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1429 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered insert_usages_row ');
1430 END IF;
1431
1432 -- Set Internal columns
1433 l_object_version_number := 1;
1434 l_creation_date := Sysdate;
1435 l_created_by := Fnd_Global.User_Id;
1436 l_last_update_date := l_creation_date;
1437 l_last_updated_by := l_created_by;
1438 l_last_update_login := Fnd_Global.Login_Id;
1439
1440 --Item level validations
1441
1442 IF ( p_valid_config_yn NOT IN ('Y','N') AND p_valid_config_yn IS NOT NULL) THEN
1443 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1444 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: - attribute VALID_CONFIG_YN is invalid');
1445 END IF;
1446 -- Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'VALID_CONFIG_YN');
1447 x_return_status := G_RET_STS_ERROR;
1448 END IF;
1449
1450 OPEN l_get_temp_already_exists_csr;
1451 FETCH l_get_temp_already_exists_csr INTO l_temp_exists;
1452 CLOSE l_get_temp_already_exists_csr;
1453
1454 IF l_temp_exists <> 'Y' THEN
1455 INSERT INTO okc_mlp_template_usages(
1456 DOCUMENT_TYPE,
1457 DOCUMENT_ID,
1458 TEMPLATE_ID,
1459 DOC_NUMBERING_SCHEME,
1460 DOCUMENT_NUMBER,
1461 ARTICLE_EFFECTIVE_DATE,
1462 CONFIG_HEADER_ID,
1463 CONFIG_REVISION_NUMBER,
1464 VALID_CONFIG_YN,
1465 ORIG_SYSTEM_REFERENCE_CODE,
1466 ORIG_SYSTEM_REFERENCE_ID1,
1467 ORIG_SYSTEM_REFERENCE_ID2,
1468 OBJECT_VERSION_NUMBER,
1469 CREATED_BY,
1470 CREATION_DATE,
1471 LAST_UPDATED_BY,
1472 LAST_UPDATE_LOGIN,
1473 LAST_UPDATE_DATE,
1474 lock_terms_flag,
1475 locked_by_user_id,
1476 primary_template )
1477 VALUES (
1478 p_document_type,
1479 p_document_id,
1480 p_template_id,
1481 p_doc_numbering_scheme,
1482 p_document_number,
1483 p_article_effective_date,
1484 p_config_header_id,
1485 p_config_revision_number,
1486 p_valid_config_yn,
1487 p_orig_system_reference_code,
1488 p_orig_system_reference_id1,
1489 p_orig_system_reference_id2,
1490 l_object_version_number,
1491 l_created_by,
1492 l_creation_date,
1493 l_last_updated_by,
1494 l_last_update_login,
1495 l_last_update_date,
1496 p_lock_terms_flag,
1497 p_locked_by_user_id,
1498 p_primary_template
1499 );
1500 END IF;
1501
1502 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1503 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Leaving Insert_Row');
1504 END IF;
1505
1506 x_return_status := G_RET_STS_SUCCESS;
1507
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510
1511 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1512 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving Insert_Row:OTHERS Exception');
1513 END IF;
1514
1515 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1516 p_msg_name => G_UNEXPECTED_ERROR,
1517 p_token1 => G_SQLCODE_TOKEN,
1518 p_token1_value => sqlcode,
1519 p_token2 => G_SQLERRM_TOKEN,
1520 p_token2_value => sqlerrm);
1521 x_return_status := G_RET_STS_ERROR;
1522
1523 END insert_usages_row;
1524
1525 FUNCTION Lock_Row(
1526 p_document_type IN VARCHAR2,
1527 p_document_id IN NUMBER,
1528 p_object_version_number IN NUMBER
1529 ) RETURN VARCHAR2 IS
1530
1531 E_Resource_Busy EXCEPTION;
1532 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1533
1534 CURSOR lock_csr (cp_document_type VARCHAR2, cp_document_id NUMBER, cp_object_version_number NUMBER) IS
1535 SELECT object_version_number
1536 FROM OKC_MLP_TEMPLATE_USAGES
1537 WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id
1538 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1539 FOR UPDATE OF object_version_number NOWAIT;
1540
1541 CURSOR lchk_csr (cp_document_type VARCHAR2, cp_document_id NUMBER) IS
1542 SELECT object_version_number
1543 FROM OKC_MLP_TEMPLATE_USAGES
1544 WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id;
1545
1546 l_return_status VARCHAR2(1);
1547 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1548 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1549
1550 l_row_notfound BOOLEAN := FALSE;
1551 BEGIN
1552
1553 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1554 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Lock_Row');
1555 END IF;
1556
1557 BEGIN
1558
1559 OPEN lock_csr( p_document_type, p_document_id, p_object_version_number );
1560 FETCH lock_csr INTO l_object_version_number;
1561 l_row_notfound := lock_csr%NOTFOUND;
1562 CLOSE lock_csr;
1563
1564 EXCEPTION
1565 WHEN E_Resource_Busy THEN
1566
1567 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1568 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving Lock_Row:E_Resource_Busy Exception');
1569 END IF;
1570
1571 IF (lock_csr%ISOPEN) THEN
1572 CLOSE lock_csr;
1573 END IF;
1574 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1575 RETURN( G_RET_STS_ERROR );
1576 END;
1577
1578 IF ( l_row_notfound ) THEN
1579 l_return_status := G_RET_STS_ERROR;
1580
1581 OPEN lchk_csr(p_document_type, p_document_id);
1582 FETCH lchk_csr INTO l_object_version_number;
1583 l_row_notfound := lchk_csr%NOTFOUND;
1584 CLOSE lchk_csr;
1585
1586 IF (l_row_notfound) THEN
1587 Okc_Api.Set_Message(G_FND_APP,G_LOCK_RECORD_DELETED,
1588 'ENTITYNAME','OKC_MLP_TEMPLATE_USAGES',
1589 'PKEY',p_document_type||':'||p_document_id,
1590 'OVN',p_object_version_number
1591 );
1592 ELSIF l_object_version_number > p_object_version_number THEN
1593 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1594 ELSIF l_object_version_number = -1 THEN
1595 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1596 ELSE -- it can be the only above condition. It can happen after restore version
1597 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1598 END IF;
1599 ELSE
1600 l_return_status := G_RET_STS_SUCCESS;
1601 END IF;
1602
1603 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1604 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Leaving Lock_Row');
1605 END IF;
1606
1607 RETURN( l_return_status );
1608
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611
1612 IF (lock_csr%ISOPEN) THEN
1613 CLOSE lock_csr;
1614 END IF;
1615 IF (lchk_csr%ISOPEN) THEN
1616 CLOSE lchk_csr;
1617 END IF;
1618
1619 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1620 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1621 END IF;
1622
1623 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1624 p_msg_name => G_UNEXPECTED_ERROR,
1625 p_token1 => G_SQLCODE_TOKEN,
1626 p_token1_value => sqlcode,
1627 p_token2 => G_SQLERRM_TOKEN,
1628 p_token2_value => sqlerrm);
1629
1630 RETURN( G_RET_STS_UNEXP_ERROR );
1631 END Lock_Row;
1632
1633 PROCEDURE Delete_Usages_Row(
1634 x_return_status OUT NOCOPY VARCHAR2,
1635 p_document_type IN VARCHAR2,
1636 p_document_id IN NUMBER,
1637 p_object_version_number IN NUMBER
1638 ) IS
1639 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Usages_Row';
1640 BEGIN
1641
1642 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1643 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Delete_Usages_Row');
1644 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Locking row');
1645 END IF;
1646
1647 x_return_status := Lock_row(
1648 p_document_type => p_document_type,
1649 p_document_id => p_document_id,
1650 p_object_version_number => p_object_version_number
1651 );
1652 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1655 RAISE FND_API.G_EXC_ERROR;
1656 END IF;
1657
1658 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1659 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Removing rows');
1660 END IF;
1661
1662 DELETE FROM OKC_MLP_TEMPLATE_USAGES WHERE DOCUMENT_TYPE = p_document_type AND DOCUMENT_ID = p_document_id;
1663
1664 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1666 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1667 RAISE FND_API.G_EXC_ERROR;
1668 END IF;
1669
1670 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1671 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving Delete_Row');
1672 END IF;
1673
1674 EXCEPTION
1675 WHEN FND_API.G_EXC_ERROR THEN
1676 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1677 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Usages_Row:FND_API.G_EXC_ERROR Exception');
1678 END IF;
1679 x_return_status := G_RET_STS_ERROR;
1680
1681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1682 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1683 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Usages_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1684 END IF;
1685 x_return_status := G_RET_STS_UNEXP_ERROR;
1686
1687 WHEN OTHERS THEN
1688 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1689 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving Delete_Usages_Row because of EXCEPTION: '||sqlerrm);
1690 END IF;
1691 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1692 p_msg_name => G_UNEXPECTED_ERROR,
1693 p_token1 => G_SQLCODE_TOKEN,
1694 p_token1_value => sqlcode,
1695 p_token2 => G_SQLERRM_TOKEN,
1696 p_token2_value => sqlerrm);
1697 x_return_status := G_RET_STS_UNEXP_ERROR;
1698
1699 END Delete_Usages_Row;
1700
1701 FUNCTION check_dup_templates( p_document_type IN VARCHAR2,
1702 p_document_id IN NUMBER,
1703 p_template_id IN NUMBER)
1704 RETURN VARCHAR2
1705 IS
1706 l_api_name CONSTANT VARCHAR2(30) := 'check_dup_templates';
1707
1708 CURSOR l_template_exists_csr IS
1709 SELECT 'Y' FROM dual WHERE p_template_id IN (
1710 SELECT template_id FROM okc_template_usages
1711 WHERE document_type = p_document_type AND document_id = p_document_id
1712 UNION ALL
1713 SELECT template_id FROM okc_mlp_template_usages
1714 WHERE document_type = p_document_type AND document_id = p_document_id);
1715
1716 l_template_exists VARCHAR2(1) := 'N';
1717
1718 BEGIN
1719 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1720 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered check_dup_templates');
1721 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_document_type ' || p_document_type);
1722 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_document_id ' || p_document_id);
1723 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_template_id ' || p_template_id);
1724 END IF;
1725
1726 OPEN l_template_exists_csr;
1727 FETCH l_template_exists_csr INTO l_template_exists;
1728 CLOSE l_template_exists_csr;
1729
1730 RETURN l_template_exists;
1731
1732 EXCEPTION
1733 WHEN FND_API.G_EXC_ERROR THEN
1734 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1735 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving check_dup_templates:FND_API.G_EXC_ERROR Exception');
1736 END IF;
1737 --x_return_status := G_RET_STS_ERROR;
1738
1739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1740 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1741 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving check_dup_templates:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1742 END IF;
1743 --x_return_status := G_RET_STS_UNEXP_ERROR;
1744
1745 WHEN OTHERS THEN
1746 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1747 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving check_dup_templates because of EXCEPTION: '||sqlerrm);
1748 END IF;
1749 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1750 p_msg_name => G_UNEXPECTED_ERROR,
1751 p_token1 => G_SQLCODE_TOKEN,
1752 p_token1_value => sqlcode,
1753 p_token2 => G_SQLERRM_TOKEN,
1754 p_token2_value => sqlerrm);
1755 --x_return_status := G_RET_STS_UNEXP_ERROR;
1756
1757 END check_dup_templates;
1758
1759 PROCEDURE copy_usages_row(
1760 p_target_doc_type IN VARCHAR2,
1761 p_source_doc_type IN VARCHAR2,
1762 p_target_doc_id IN NUMBER,
1763 p_source_doc_id IN NUMBER,
1764 x_return_status OUT NOCOPY VARCHAR2,
1765 x_msg_data OUT NOCOPY VARCHAR2,
1766 x_msg_count OUT NOCOPY NUMBER)
1767 IS
1768 l_api_name CONSTANT VARCHAR2(30) := 'copy_usages_row';
1769
1770 TYPE TemplateIdList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.TEMPLATE_ID%TYPE INDEX BY BINARY_INTEGER;
1771 TYPE DocNumSchemeList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.DOC_NUMBERING_SCHEME%TYPE INDEX BY BINARY_INTEGER;
1772 TYPE DocumentNumberList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.DOCUMENT_NUMBER%TYPE INDEX BY BINARY_INTEGER;
1773 TYPE ArticleEffectiveDateList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.ARTICLE_EFFECTIVE_DATE%TYPE INDEX BY BINARY_INTEGER;
1774 TYPE ConfigHeaderIdList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.CONFIG_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
1775 TYPE ConfigRevisionNumberList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.CONFIG_REVISION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
1776 TYPE ValidConfigYNList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.VALID_CONFIG_YN%TYPE INDEX BY BINARY_INTEGER;
1777 TYPE OrigSystemRefCodeList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_CODE%TYPE INDEX BY BINARY_INTEGER;
1778 TYPE OrigSystemRefIdList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID1%TYPE INDEX BY BINARY_INTEGER;
1779 TYPE LockTermsList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.lock_terms_flag%TYPE INDEX BY BINARY_INTEGER;
1780 TYPE LockedByUserIdList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.locked_by_user_id%TYPE INDEX BY BINARY_INTEGER;
1781 TYPE PrimaryTemplateList IS TABLE OF OKC_MLP_TEMPLATE_USAGES.primary_template%TYPE INDEX BY BINARY_INTEGER;
1782
1783 TemplateIdTbl TemplateIdList;
1784 DocNumSchemeTbl DocNumSchemeList;
1785 DocumentNumberTbl DocumentNumberList;
1786 ArticleEffectiveDateTbl ArticleEffectiveDateList;
1787 ConfigHeaderIdTbl ConfigHeaderIdList;
1788 ConfigRevisionNumberTbl ConfigRevisionNumberList;
1789 ValidConfigYNTbl ValidConfigYNList;
1790 OrigSystemRefCodeTbl OrigSystemRefCodeList;
1791 OrigSystemRefId1Tbl OrigSystemRefIdList;
1792 OrigSystemRefId2Tbl OrigSystemRefIdList;
1793 LockTermsTbl LockTermsList;
1794 LockedByUserIdTbl LockedByUserIdList;
1795 PrimaryTemplateTbl PrimaryTemplateList;
1796
1797 CURSOR l_get_mlp_temp_csr IS
1798 SELECT TEMPLATE_ID,
1799 DOC_NUMBERING_SCHEME,
1800 DOCUMENT_NUMBER,
1801 ARTICLE_EFFECTIVE_DATE,
1802 CONFIG_HEADER_ID,
1803 CONFIG_REVISION_NUMBER,
1804 VALID_CONFIG_YN,
1805 ORIG_SYSTEM_REFERENCE_CODE,
1806 ORIG_SYSTEM_REFERENCE_ID1,
1807 ORIG_SYSTEM_REFERENCE_ID2,
1808 LOCK_TERMS_FLAG,
1809 LOCKED_BY_USER_ID,
1810 PRIMARY_TEMPLATE
1811 FROM okc_mlp_template_usages
1812 WHERE document_type = p_source_doc_type
1813 AND document_id = p_source_doc_id;
1814
1815 BEGIN
1816 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1817 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered copy_usages_row');
1818 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: p_target_doc_type ' || p_target_doc_type);
1819 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_source_doc_type ' || p_source_doc_type);
1820 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_source_doc_id ' || p_source_doc_id);
1821 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_target_doc_id ' || p_target_doc_id);
1822 END IF;
1823
1824 OPEN l_get_mlp_temp_csr;
1825 FETCH l_get_mlp_temp_csr BULK COLLECT INTO TemplateIdTbl,
1826 DocNumSchemeTbl,
1827 DocumentNumberTbl,
1828 ArticleEffectiveDateTbl,
1829 ConfigHeaderIdTbl,
1830 ConfigRevisionNumberTbl,
1831 ValidConfigYNTbl,
1832 OrigSystemRefCodeTbl,
1833 OrigSystemRefId1Tbl,
1834 OrigSystemRefId2Tbl,
1835 LockTermsTbl,
1836 LockedByUserIdTbl,
1837 PrimaryTemplateTbl;
1838 CLOSE l_get_mlp_temp_csr;
1839
1840 IF TemplateIdTbl.COUNT > 0 THEN
1841 FORALL i IN TemplateIdTbl.FIRST..TemplateIdTbl.LAST
1842
1843 INSERT INTO okc_mlp_template_usages(
1844 DOCUMENT_TYPE,
1845 DOCUMENT_ID,
1846 TEMPLATE_ID,
1847 DOC_NUMBERING_SCHEME,
1848 DOCUMENT_NUMBER,
1849 ARTICLE_EFFECTIVE_DATE,
1850 CONFIG_HEADER_ID,
1851 CONFIG_REVISION_NUMBER,
1852 VALID_CONFIG_YN,
1853 ORIG_SYSTEM_REFERENCE_CODE,
1854 ORIG_SYSTEM_REFERENCE_ID1,
1855 ORIG_SYSTEM_REFERENCE_ID2,
1856 OBJECT_VERSION_NUMBER,
1857 CREATED_BY,
1858 CREATION_DATE,
1859 LAST_UPDATED_BY,
1860 LAST_UPDATE_LOGIN,
1861 LAST_UPDATE_DATE,
1862 lock_terms_flag,
1863 locked_by_user_id,
1864 primary_template )
1865 VALUES (
1866 p_target_doc_type,
1867 p_target_doc_id,
1868 TemplateIdTbl(i),
1869 DocNumSchemeTbl(i),
1870 DocumentNumberTbl(i),
1871 ArticleEffectiveDateTbl(i),
1872 ConfigHeaderIdTbl(i),
1873 ConfigRevisionNumberTbl(i),
1874 ValidConfigYNTbl(i),
1875 OrigSystemRefCodeTbl(i),
1876 OrigSystemRefId1Tbl(i),
1877 OrigSystemRefId2Tbl(i),
1878 1,
1879 Fnd_Global.User_Id,
1880 sysdate,
1881 Fnd_Global.User_Id,
1882 Fnd_Global.Login_Id,
1883 SYSDATE,
1884 LockTermsTbl(i),
1885 LockedByUserIdTbl(i),
1886 PrimaryTemplateTbl(i)
1887 );
1888 END IF;
1889
1890 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1891 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Leaving copy_usages_row '||x_return_status);
1892 END IF;
1893
1894 EXCEPTION
1895 WHEN FND_API.G_EXC_ERROR THEN
1896 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1897 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving copy_usages_row:FND_API.G_EXC_ERROR Exception');
1898 END IF;
1899 x_return_status := G_RET_STS_ERROR;
1900
1901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1902 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1903 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving copy_usages_row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1904 END IF;
1905 x_return_status := G_RET_STS_UNEXP_ERROR;
1906
1907 WHEN OTHERS THEN
1908 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1909 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving copy_usages_row because of EXCEPTION: '||sqlerrm);
1910 END IF;
1911 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1912 p_msg_name => G_UNEXPECTED_ERROR,
1913 p_token1 => G_SQLCODE_TOKEN,
1914 p_token1_value => sqlcode,
1915 p_token2 => G_SQLERRM_TOKEN,
1916 p_token2_value => sqlerrm);
1917 x_return_status := G_RET_STS_UNEXP_ERROR;
1918
1919 END copy_usages_row;
1920
1921
1922 END OKC_CLM_PKG;