[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_XRULE_VALUES_PVT
Source
1 PACKAGE BODY OKC_XPRT_XRULE_VALUES_PVT AS
2 /* $Header: OKCVXXRULVB.pls 120.24 2010/04/19 08:50:19 harchand ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_XRULE_VALUES_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
9
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
12 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
13
14 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
15 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
16
17 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
19 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
20
21 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
22 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
23 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
24 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
25
26 G_BUY_ITEM_VARIABLE_NAME CONSTANT VARCHAR2(50) := 'OKC$B_ITEM';
27 G_BUY_ITEM_CAT_VAR_NAME CONSTANT VARCHAR2(50) := 'OKC$B_ITEM_CATEGORY';
28 G_SELL_ITEM_VARIABLE_NAME CONSTANT VARCHAR2(50) := 'OKC$S_ITEM';
29 G_SELL_ITEM_CAT_VAR_NAME CONSTANT VARCHAR2(50) := 'OKC$S_ITEM_CATEGORY';
30
31 ---------------------------------------------------
32 -- Procedure: get_system_variables
33 ---------------------------------------------------
34
35 PROCEDURE get_system_variables (
36 p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 p_doc_type IN VARCHAR2,
42 p_doc_id IN NUMBER,
43 p_only_doc_variables IN VARCHAR2 := FND_API.G_TRUE,
44 x_sys_var_value_tbl OUT NOCOPY var_value_tbl_type
45 )
46 IS
47 l_api_name VARCHAR2(30) := 'get_system_variables';
48 l_api_version CONSTANT NUMBER := 1.0;
49
50 l_sys_var_value_tbl OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
51
52 l_index NUMBER := 1;
53
54 BEGIN
55
56 -- start debug log
57 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
58 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
59 G_MODULE||l_api_name,
60 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
61 END IF;
62
63 x_return_status := G_RET_STS_SUCCESS;
64
65 OKC_TERMS_UTIL_PVT.get_system_variables(
66 p_api_version => 1.0,
67 p_init_msg_list => p_init_msg_list,
68 x_return_status => x_return_status,
69 x_msg_data => x_msg_data,
70 x_msg_count => x_msg_count,
71 p_doc_type => p_doc_type,
72 p_doc_id => p_doc_id,
73 p_only_doc_variables => p_only_doc_variables,
74 x_sys_var_value_tbl => l_sys_var_value_tbl);
75
76 --- If any errors happen abort API
77 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
80 RAISE FND_API.G_EXC_ERROR;
81 END IF;
82
83 -- Log all Variable values in the Pl/sql table
84
85 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
86 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
87 G_MODULE||l_api_name,
88 '110: All System Variables before removing Null values and Not_null values');
89 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
90 G_MODULE||l_api_name,
91 '110: Variable name '||' ' ||'Variable value');
92 FOR i IN l_sys_var_value_tbl.first..l_sys_var_value_tbl.last LOOP
93 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
94 l_sys_var_value_tbl(i).variable_code ||' '||
95 l_sys_var_value_tbl(i).variable_value_id);
96 END LOOP;
97 END IF;*/
98
99 FOR i IN l_sys_var_value_tbl.first..l_sys_var_value_tbl.last LOOP
100 --Bug 4868007 commented below IF condition
101 --IF (l_sys_var_value_tbl(i).variable_value_id IS NOT NULL AND
102 -- l_sys_var_value_tbl(i).variable_value_id <> 'NOT_NULL' ) THEN
103 -- Added for Bug fix 5015134. Added part of If condition
104 IF (l_sys_var_value_tbl(i).variable_value_id <> 'NOT_NULL' ) THEN
105 x_sys_var_value_tbl(l_index).variable_code := l_sys_var_value_tbl(i).variable_code;
106 x_sys_var_value_tbl(l_index).variable_value_id := l_sys_var_value_tbl(i).variable_value_id;
107 l_index := l_index + 1;
108 END IF;
109 END LOOP;
110 -- For Bug# 6833184
111 -- l_sys_var_value_tbl(i).variable_value_id will always be NULL if the
112 -- document type is 'REPOSITORY'
113
114 -- Log all Variable values in the Pl/sql table
115 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
117 G_MODULE||l_api_name,
118 '140: All system variables ');
119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
120 G_MODULE||l_api_name,
121 '140: Variable name '||' ' ||'Variable value');
122 IF (x_sys_var_value_tbl.count <> 0) THEN -- For Bug# 6833184
123 FOR i IN x_sys_var_value_tbl.first..x_sys_var_value_tbl.last LOOP
124 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
125 x_sys_var_value_tbl(i).variable_code ||' '||
126 substr(x_sys_var_value_tbl(i).variable_value_id,1,100));
127 END LOOP;
128 END IF; -- For Bug# 6833184
129 END IF;
130
131
132 -- end debug log
133 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
135 G_MODULE||l_api_name,
136 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
137 END IF;
138
139
140 EXCEPTION
141
142 WHEN FND_API.G_EXC_ERROR THEN
143 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
145 G_MODULE||l_api_name,
146 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
147 END IF;
148
149 x_return_status := G_RET_STS_ERROR ;
150 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
151
152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
155 G_MODULE||l_api_name,
156 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
157 END IF;
158
159 x_return_status := G_RET_STS_UNEXP_ERROR ;
160 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
161
162 WHEN OTHERS THEN
163 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
165 G_MODULE||l_api_name,
166 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
167 END IF;
168
169 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
170 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
171 END IF;
172 x_return_status := G_RET_STS_UNEXP_ERROR ;
173 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
174
175 END get_system_variables;
176
177 ---------------------------------------------------
178 -- Procedure: check_line_level_rule_exists
179 ---------------------------------------------------
180
181 FUNCTION check_line_level_rule_exists (
182 p_doc_type IN VARCHAR2,
183 p_doc_id IN NUMBER,
184 p_org_id IN NUMBER)
185
186 RETURN VARCHAR2
187 IS
188
189 cursor org_line_rule_csr (p_org_id number) is
190 select 'X'
191 from okc_xprt_rule_hdrs_all
192 where org_wide_flag = 'Y'
193 and line_level_flag = 'Y'
194 and org_id = p_org_id;
195
196 cursor template_line_rule_csr (p_doc_id number, p_doc_type varchar2) is
197 select 'X'
198 from okc_xprt_rule_hdrs_all rhdr,
199 okc_template_usages tuse,
200 okc_xprt_template_rules trule
201 where tuse.document_id = p_doc_id
202 and tuse.document_type = p_doc_type
203 and tuse.template_id = trule.template_id
204 and trule.rule_id = rhdr.rule_id
205 and rhdr.line_level_flag ='Y';
206
207 l_dummy VARCHAR2(1);
208 l_return VARCHAR2(1);
209
210 BEGIN
211
212 OPEN template_line_rule_csr(p_doc_id,p_doc_type);
213 FETCH template_line_rule_csr INTO l_dummy;
214 IF (template_line_rule_csr%NOTFOUND) THEN
215 OPEN org_line_rule_csr(p_org_id);
216 FETCH org_line_rule_csr INTO l_dummy;
217 IF (template_line_rule_csr%NOTFOUND) THEN
218 l_return := 'N';
219 ELSE
220 l_return := 'Y';
221 END IF;
222 CLOSE template_line_rule_csr;
223 ELSE
224 l_return := 'Y';
225 END IF;
226 CLOSE template_line_rule_csr;
227
228 RETURN l_return;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 --close cursors
233 IF template_line_rule_csr%ISOPEN THEN
234 CLOSE template_line_rule_csr;
235 END IF;
236 IF org_line_rule_csr%ISOPEN THEN
237 CLOSE org_line_rule_csr;
238 END IF;
239 RETURN 'N';
240 END check_line_level_rule_exists;
241
242
243 ---------------------------------------------------
244 -- Procedure: get_line_system_variables
245 ---------------------------------------------------
246
247 PROCEDURE get_line_system_variables (
248 p_api_version IN NUMBER,
249 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
250 p_doc_type IN VARCHAR2,
251 p_doc_id IN NUMBER,
252 p_org_id IN NUMBER,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_line_sys_var_value_tbl OUT NOCOPY line_sys_var_value_tbl_type,
257 x_line_count OUT NOCOPY NUMBER,
258 x_line_variables_count OUT NOCOPY NUMBER
259 )
260 IS
261
262 l_api_name varchar2(30) := 'get_line_system_variables';
263 l_api_version constant number := 1.0;
264
265 BEGIN
266
267 -- start debug log
268 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
270 G_MODULE||l_api_name,
271 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
272 END IF;
273
274 x_return_status := G_RET_STS_SUCCESS;
275
276 IF (p_doc_type = 'B' OR p_doc_type = 'O')
277 THEN
278 OKC_XPRT_OM_INT_PVT.get_line_variable_values(
279 p_api_version => 1.0,
280 p_init_msg_list => p_init_msg_list,
281 x_return_status => x_return_status,
282 x_msg_data => x_msg_data,
283 x_msg_count => x_msg_count,
284 p_doc_type => p_doc_type,
285 p_doc_id => p_doc_id,
286 x_line_sys_var_value_tbl => x_line_sys_var_value_tbl,
287 x_line_count => x_line_count,
288 x_line_variables_count => x_line_variables_count);
289 --null;
290
291 --- If any errors happen abort API
292 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297 END IF;
298
299 IF (p_doc_type = 'QUOTE')
300 THEN
301 OKC_XPRT_QUOTE_INT_PVT.get_line_variable_values(
302 p_api_version => 1.0,
303 p_init_msg_list => p_init_msg_list,
304 x_return_status => x_return_status,
305 x_msg_data => x_msg_data,
306 x_msg_count => x_msg_count,
307 --p_doc_type => p_doc_type,
308 p_doc_id => p_doc_id,
309 x_line_sys_var_value_tbl => x_line_sys_var_value_tbl,
310 x_line_count => x_line_count,
311 x_line_variables_count => x_line_variables_count);
312
313 --null;
314 --- If any errors happen abort API
315 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320 END IF;
321
322 -- Log all Variable values in the Pl/sql table
323 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
324 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
325 G_MODULE||l_api_name,
326 '110: No. of Lines: '|| x_line_count);
327
328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
329 G_MODULE||l_api_name,
330 '120: Line Number'||' ' ||
331 'Variable Name '||' ' ||
332 'Variable Value '||' ' ||
333 'Item Id'||' ' ||
334 'Org Id');
335 IF x_line_sys_var_value_tbl.COUNT > 0 THEN
336 FOR i IN x_line_sys_var_value_tbl.FIRST..x_line_sys_var_value_tbl.LAST LOOP
337 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
338 x_line_sys_var_value_tbl(i).line_number ||' '||
339 x_line_sys_var_value_tbl(i).variable_code ||' '||
340 x_line_sys_var_value_tbl(i).variable_value ||' '||
341 x_line_sys_var_value_tbl(i).item_id ||' '||
342 x_line_sys_var_value_tbl(i).org_id);
343 END LOOP;
344 END IF;
345 END IF;
346
347 -- end debug log
348 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
350 G_MODULE||l_api_name,
351 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
352 END IF;
353
354 EXCEPTION
355
356
357 WHEN FND_API.G_EXC_ERROR THEN
358 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
359 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
360 G_MODULE||l_api_name,
361 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
362 END IF;
363
364 x_return_status := G_RET_STS_ERROR ;
365 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
366
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
369 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
370 G_MODULE||l_api_name,
371 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
372 END IF;
373
374 x_return_status := G_RET_STS_UNEXP_ERROR ;
375 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
376
377 WHEN OTHERS THEN
378 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
379 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
380 G_MODULE||l_api_name,
381 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
382 END IF;
383
384 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
385 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
386 END IF;
387 x_return_status := G_RET_STS_UNEXP_ERROR ;
388 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
389
390 END get_line_system_variables;
391
392 ---------------------------------------------------
393 -- Procedure: get_user_defined_variables
394 ---------------------------------------------------
395
396 PROCEDURE get_user_defined_variables (
397 p_api_version IN NUMBER,
398 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
399 p_doc_type IN VARCHAR2,
400 p_doc_id IN NUMBER,
401 p_org_id IN NUMBER,
402 p_intent IN VARCHAR2,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_data OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_udf_var_value_tbl OUT NOCOPY udf_var_value_tbl_type
407 )
408 IS
409
410 CURSOR csr_get_udf_variables (p_doc_type VARCHAR2,p_doc_id NUMBER, p_intent VARCHAR2, p_org_id NUMBER) IS
411 SELECT 'USER$' || var.variable_code variable_code,
412 NVL(var.variable_value_id,var.variable_value) variable_value -- Added NVL for Bug 5233775
413 FROM okc_k_art_variables var,
414 okc_k_articles_b art, -- Added for Bug 4728565
415 okc_bus_variables_b bvar
416 WHERE art.id = var.cat_id -- Added for Bug 4728565
417 AND art.document_id = p_doc_id -- Added for Bug 4728565
418 AND art.document_type = p_doc_type -- Added for Bug 4728565
419 AND var.variable_code = bvar.variable_code
420 AND bvar.variable_source = 'M'
421 AND bvar.clm_source IS NULL --CLM Changes
422 AND var.variable_code IN ( SELECT rcon.object_code variable_code -- LHS of Condition from Template rule
423 FROM okc_xprt_rule_hdrs_all rhdr,
424 okc_xprt_rule_conditions rcon,
425 okc_template_usages tuse,
426 okc_xprt_template_rules trule
427 WHERE tuse.document_id = p_doc_id
428 AND tuse.document_type = p_doc_type
429 AND tuse.template_id = trule.template_id
430 AND trule.rule_id = rhdr.rule_id
431 AND rhdr.rule_id = rcon.rule_id
432 AND rcon.object_type = 'VARIABLE'
433 AND rhdr.status_code <> 'DRAFT'
434 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
435 GROUP BY rcon.object_code
436 UNION
437 SELECT rcon.object_value_code variable_code -- RHS of Condition from Template rule
438 FROM okc_xprt_rule_hdrs_all rhdr,
439 okc_xprt_rule_conditions rcon,
440 okc_template_usages tuse,
441 okc_xprt_template_rules trule
442 WHERE tuse.document_id = p_doc_id
443 AND tuse.document_type = p_doc_type
444 AND tuse.template_id = trule.template_id
445 AND trule.rule_id = rhdr.rule_id
446 AND rhdr.rule_id = rcon.rule_id
447 AND rcon.object_value_type = 'VARIABLE'
448 AND rhdr.status_code <> 'DRAFT'
449 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
450 GROUP BY rcon.object_value_code
451 UNION
452 SELECT rcon.object_code variable_code -- LHS of Condition from Global Rule
453 FROM okc_xprt_rule_hdrs_all rhdr,
454 okc_xprt_rule_conditions rcon
455 WHERE rhdr.rule_id = rcon.rule_id
456 AND rhdr.org_id = p_org_id
457 AND rhdr.intent = p_intent
458 AND rhdr.org_wide_flag = 'Y'
459 AND rcon.object_type = 'VARIABLE'
460 AND rhdr.status_code <> 'DRAFT'
461 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
462 GROUP BY rcon.object_code
463 UNION
464 SELECT rcon.object_value_code variable_code -- RHS of Condition from Global Rule
465 FROM okc_xprt_rule_hdrs_all rhdr,
466 okc_xprt_rule_conditions rcon
467 WHERE rhdr.rule_id = rcon.rule_id
468 AND rhdr.org_id = p_org_id
469 AND rhdr.intent = p_intent
470 AND rhdr.org_wide_flag = 'Y'
471 AND rcon.object_value_type = 'VARIABLE'
472 AND rhdr.status_code <> 'DRAFT'
473 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
474 GROUP BY rcon.object_value_code);
475
476
477 l_api_name VARCHAR2(30) := 'get_user_defined_variables';
478 l_api_version CONSTANT NUMBER := 1.0;
479
480 BEGIN
481
482 -- start debug log
483 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
485 G_MODULE||l_api_name,
486 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
487 END IF;
488
489 x_return_status := G_RET_STS_SUCCESS;
490
491 OPEN csr_get_udf_variables(p_doc_type, p_doc_id, p_intent, p_org_id);
492 FETCH csr_get_udf_variables BULK COLLECT INTO x_udf_var_value_tbl;
493 CLOSE csr_get_udf_variables;
494
495
496 -- Log all User Defined Variable values in the Pl/sql table
497 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
498 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
499 G_MODULE||l_api_name,
500 '110: User Defined Variable name '||' ' ||'User Defined Variable value');
501 FOR i IN x_udf_var_value_tbl.first..x_udf_var_value_tbl.last LOOP
502 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
503 x_udf_var_value_tbl(i).variable_code ||' '||
504 x_udf_var_value_tbl(i).variable_value_id);
505 END LOOP;
506 END IF;*/
507
508 -- end debug log
509 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
511 G_MODULE||l_api_name,
512 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
513 END IF;
514
515 EXCEPTION
516
517
518 WHEN FND_API.G_EXC_ERROR THEN
519 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
520 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
521 G_MODULE||l_api_name,
522 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
523 END IF;
524
525 x_return_status := G_RET_STS_ERROR ;
526 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
527
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
530 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
531 G_MODULE||l_api_name,
532 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
533 END IF;
534
535 x_return_status := G_RET_STS_UNEXP_ERROR ;
536 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
537
538 WHEN OTHERS THEN
539 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
540 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
541 G_MODULE||l_api_name,
542 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
543 END IF;
544
545 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
546 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
547 END IF;
548 x_return_status := G_RET_STS_UNEXP_ERROR ;
549 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
550
551 END get_user_defined_variables;
552
553 ---------------------------------------------------
554 -- Procedure: get_constant_values
555 ---------------------------------------------------
556
557 PROCEDURE get_constant_values (
558 p_api_version IN NUMBER,
559 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
560 p_intent IN VARCHAR2,
561 x_return_status OUT NOCOPY VARCHAR2,
562 x_msg_data OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_constant_tbl OUT NOCOPY constant_tbl_type
565 )
566 IS
567 l_api_name VARCHAR2(30) := 'get_constant_values';
568 l_api_version CONSTANT NUMBER := 1.0;
569 l_intent VARCHAR2(1);
570
571 CURSOR csr_constants (p_intent VARCHAR2) IS
572 SELECT 'CONSTANT$' || v.object_value_code constant_id, --
573 q.default_value
574 FROM okc_xprt_rule_cond_vals v,
575 okc_xprt_rule_conditions c,
576 okc_xprt_rule_hdrs_all r,
577 okc_xprt_questions_b q
578 WHERE v.rule_condition_id = c.rule_condition_id
579 AND c.rule_id = r.rule_id
580 AND to_char(q.question_id) = v.object_value_code
581 AND c.object_value_type = 'CONSTANT'
582 AND r.intent = p_intent
583 AND r.status_code <> 'DRAFT'
584 GROUP BY v.object_value_code, q.default_value;
585
586
587 BEGIN
588
589 -- start debug log
590 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
591 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
592 G_MODULE||l_api_name,
593 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
594 END IF;
595
596 x_return_status := G_RET_STS_SUCCESS;
597
598
599 OPEN csr_constants(p_intent);
600 FETCH csr_constants BULK COLLECT INTO x_constant_tbl;
601 CLOSE csr_constants;
602
603 -- Log all Variable values in the Pl/sql table
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 '110: Constant name '||' ' ||'Constant value');
608 FOR i IN x_constant_tbl.first..x_constant_tbl.last LOOP
609 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
610 x_constant_tbl(i).constant_id ||' '||
611 x_constant_tbl(i).value);
612 END LOOP;
613 END IF;*/
614
615 -- end debug log
616 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
617 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
618 G_MODULE||l_api_name,
619 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
620 END IF;
621
622 EXCEPTION
623
624 WHEN FND_API.G_EXC_ERROR THEN
625 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
626 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
627 G_MODULE||l_api_name,
628 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
629 END IF;
630
631 x_return_status := G_RET_STS_ERROR ;
632 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
633
634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
635 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
636 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
637 G_MODULE||l_api_name,
638 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
639 END IF;
640
641 x_return_status := G_RET_STS_UNEXP_ERROR ;
642 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
643
644 WHEN OTHERS THEN
645 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
646 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
647 G_MODULE||l_api_name,
648 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
649 END IF;
650
651 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
652 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
653 END IF;
654 x_return_status := G_RET_STS_UNEXP_ERROR ;
655 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
656
657 END get_constant_values;
658
659 ---------------------------------------------------
660 -- Procedure: get_udv_with_procedures
661 ---------------------------------------------------
662
663 PROCEDURE get_udv_with_procedures (
664 p_api_version IN NUMBER,
665 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
666 p_doc_type IN VARCHAR2,
667 p_doc_id IN NUMBER,
668 p_org_id IN NUMBER,
669 p_intent IN VARCHAR2,
670 x_return_status OUT NOCOPY VARCHAR2,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_udf_var_value_tbl OUT NOCOPY udf_var_value_tbl_type
674 )
675 IS
676
677 CURSOR csr_get_udv_with_proc (p_doc_type VARCHAR2,p_doc_id NUMBER, p_intent VARCHAR2, p_org_id NUMBER) IS
678 SELECT var.variable_code variable_code, --Removed USER$ to resolve Rule firing for UDV with Procedures
679 procedure_name procedure_name
680 FROM okc_bus_variables_b var
681 WHERE var.variable_source = 'P'
682 AND var.variable_code IN
683 (SELECT distinct rcon.object_code variable_code -- LHS of Condition from Template rule
684 FROM okc_xprt_rule_hdrs_all rhdr,
685 okc_xprt_rule_conditions rcon,
686 okc_template_usages tuse,
687 okc_xprt_template_rules trule
688 WHERE tuse.document_id = p_doc_id
689 AND tuse.document_type = p_doc_type
690 AND tuse.template_id = trule.template_id
691 AND trule.rule_id = rhdr.rule_id
692 AND rhdr.rule_id = rcon.rule_id
693 AND rcon.object_type = 'VARIABLE'
694 AND rhdr.status_code <> 'DRAFT'
695 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
696 GROUP BY rcon.object_code
697 UNION
698 SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Template rule
699 FROM okc_xprt_rule_hdrs_all rhdr,
700 okc_xprt_rule_conditions rcon,
701 okc_template_usages tuse,
702 okc_xprt_template_rules trule
703 WHERE tuse.document_id = p_doc_id
704 AND tuse.document_type = p_doc_type
705 AND tuse.template_id = trule.template_id
706 AND trule.rule_id = rhdr.rule_id
707 AND rhdr.rule_id = rcon.rule_id
708 AND rcon.object_value_type = 'VARIABLE'
709 AND rhdr.status_code <> 'DRAFT'
710 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
711 GROUP BY rcon.object_value_code
712 UNION
713 SELECT distinct rcon.object_code variable_code -- LHS of Condition from Global Rule
714 FROM okc_xprt_rule_hdrs_all rhdr,
715 okc_xprt_rule_conditions rcon
716 WHERE rhdr.rule_id = rcon.rule_id
717 AND rhdr.org_id = p_org_id
718 AND rhdr.intent = p_intent
719 AND rhdr.org_wide_flag = 'Y'
720 AND rcon.object_type = 'VARIABLE'
721 AND rhdr.status_code <> 'DRAFT'
722 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
723 GROUP BY rcon.object_code
724 UNION
725 SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Global Rule
726 FROM okc_xprt_rule_hdrs_all rhdr,
727 okc_xprt_rule_conditions rcon
728 WHERE rhdr.rule_id = rcon.rule_id
729 AND rhdr.org_id = p_org_id
730 AND rhdr.intent = p_intent
731 AND rhdr.org_wide_flag = 'Y'
732 AND rcon.object_value_type = 'VARIABLE'
733 AND rhdr.status_code <> 'DRAFT'
734 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
735 GROUP BY rcon.object_value_code);
736
737 CURSOR csr_get_uniq_proc (p_sequence_id NUMBER) IS
738 SELECT distinct procedure_name procedure_name
739 FROM okc_xprt_deviations_t
740 WHERE run_id = p_sequence_id;
741
742
743 CURSOR csr_get_vars_for_proc (p_procedure_name VARCHAR2, p_sequence_id NUMBER) IS
744 SELECT distinct variable_code variable_code
745 FROM okc_xprt_deviations_t
746 WHERE run_id = p_sequence_id
747 AND procedure_name = p_procedure_name;
748
749 l_api_name VARCHAR2(30) := 'get_udv_with_procedures';
750 l_api_version CONSTANT NUMBER := 1.0;
751
752 l_sql_stmt LONG;
753 l_sequence_id NUMBER;
754 var_tbl_cnt NUMBER := 1;
755 l_udf_var_value_tbl OKC_XPRT_XRULE_VALUES_PVT.udf_var_value_tbl_type;
756
757 --bug 8501694-kkolukul: Multiple values variables used in expert
758 l_udf_with_proc_mul_val_tbl OKC_XPRT_XRULE_VALUES_PVT.udf_var_value_tbl_type;
759 l_hook_used NUMBER;
760
761 TYPE VariableCodeList IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; -- changed for R12
762 variableCode_tbl VariableCodeList;
763
764 BEGIN
765
766 -- start debug log
767 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
768 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
769 G_MODULE||l_api_name,
770 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
771 END IF;
772
773 x_return_status := G_RET_STS_SUCCESS;
774
775 SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_sequence_id from DUAL;
776
777 FOR csr_get_udv_with_proc_rec IN csr_get_udv_with_proc(p_doc_type, p_doc_id, p_intent, p_org_id)
778 LOOP
779 INSERT INTO OKC_XPRT_DEVIATIONS_T
780 (
781 RUN_ID,
782 LINE_NUMBER,
783 VARIABLE_CODE,
784 VARIABLE_VALUE,
785 ITEM_ID,
786 ORG_ID,
787 CREATION_DATE,
788 PROCEDURE_NAME
789 )
790 VALUES
791 (
792 l_sequence_id, -- RUN_ID
793 NULL, -- LINE_NUMBER
794 csr_get_udv_with_proc_rec.variable_code, -- VARIABLE_CODE
795 NULL, -- VARIABLE_VALUE
796 NULL, -- ITEM_ID
797 NULL, -- ORG_ID
798 NULL, -- CREATION_DATE
799 csr_get_udv_with_proc_rec.procedure_name -- PROCEDURE_NAME
800 );
801 END LOOP;
802
803 FOR csr_get_uniq_proc_rec IN csr_get_uniq_proc(l_sequence_id)
804 LOOP
805 OPEN csr_get_vars_for_proc(csr_get_uniq_proc_rec.procedure_name, l_sequence_id);
806 FETCH csr_get_vars_for_proc BULK COLLECT INTO variableCode_tbl;
807 CLOSE csr_get_vars_for_proc;
808
809 FOR i IN 1..variableCode_tbl.COUNT
810 LOOP
811
812 l_udf_var_value_tbl(i).variable_code := variableCode_tbl(i);
813
814 --kkolukul: Modified Code to allow multi values variables to be used in Expert
815 -- start debug log
816 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
818 G_MODULE||l_api_name,
819 '106: Before Calling code Hook ');
820 END IF;
821
822 OKC_CODE_HOOK.GET_MULTIVAL_UDV_FOR_XPRT(
823 p_api_version => 1.0,
824 p_init_msg_list => p_init_msg_list,
825 p_doc_type => p_doc_type,
826 p_doc_id => p_doc_id,
827 p_udf_var_code => variableCode_tbl(i),
828 x_return_status => x_return_status,
829 x_msg_count => x_msg_count,
830 x_msg_data => x_msg_data,
831 x_cust_udf_var_mul_val_tbl => l_udf_with_proc_mul_val_tbl,
832 x_hook_used => l_hook_used );
833
834
835 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
836 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
837 G_MODULE||l_api_name,
838 '107: After Calling code Hook - l_hook_used value: ' || l_hook_used || ' "0": Hook not used, "-1": Error in hook, any other value, hook is used');
839 END IF;
840
841 IF l_udf_with_proc_mul_val_tbl IS NOT NULL THEN
842 IF l_udf_with_proc_mul_val_tbl.COUNT > 0 THEN
843 FOR k in l_udf_with_proc_mul_val_tbl.first..l_udf_with_proc_mul_val_tbl.last LOOP
844 IF l_udf_with_proc_mul_val_tbl.EXISTS(k) THEN
845 x_udf_var_value_tbl(var_tbl_cnt).variable_code := 'USER$' || l_udf_with_proc_mul_val_tbl(k).variable_code;
846 x_udf_var_value_tbl(var_tbl_cnt).variable_value_id := to_char(l_udf_with_proc_mul_val_tbl(k).variable_value_id);
847 var_tbl_cnt := var_tbl_cnt + 1;
848 END IF;
849 END LOOP;
850 END IF;
851 END IF;
852
853 --END: kkolukul: Modified Code to allow multi values variables to be used in Expert
854 IF l_hook_used = 0 then
855 -- Dynamically build SQL to execute the procedure
856 l_sql_stmt := 'BEGIN ' || csr_get_uniq_proc_rec.procedure_name || '(' ||
857 'x_return_status =>' || ':1' || ',' ||
858 'x_msg_data =>' || ':2' || ',' ||
859 'x_msg_count =>' || ':3' || ',' ||
860 'p_doc_type =>' || ':4' || ',' ||
861 'p_doc_id =>' || ':5' || ',' ||
862 'p_variable_code =>' || ':6' || ',' ||
863 'x_variable_value_id =>' || ':7' || ' ' ||
864 '); END;';
865
866 -- execute the dynamic sql
867 -- start debug log
868 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
870 G_MODULE||l_api_name,
871 '105: l_sql_stmt '|| l_sql_stmt);
872 END IF;
873
874 BEGIN
875 EXECUTE IMMEDIATE l_sql_stmt USING OUT x_return_status, OUT x_msg_data, OUT x_msg_count, p_doc_type,
876 p_doc_id, variableCode_tbl(i), IN OUT l_udf_var_value_tbl(i).variable_value_id;
877 END;
878 END IF; -- l_hook_used = 0
879
880 -- If any errors happen abort API
881 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
883 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
884 RAISE FND_API.G_EXC_ERROR;
885 END IF;
886
887 END LOOP;
888
889 -- After executing add the list to the output table
890 IF l_udf_var_value_tbl IS NOT NULL THEN
891 IF l_udf_var_value_tbl.count > 0 THEN
892 FOR i IN l_udf_var_value_tbl.first..l_udf_var_value_tbl.last LOOP
893 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
894 x_udf_var_value_tbl(var_tbl_cnt).variable_value_id := l_udf_var_value_tbl(i).variable_value_id;
895 var_tbl_cnt := var_tbl_cnt + 1;
896 END LOOP;
897 END IF;
898 END IF;
899
900 -- Clear out the PL/SQL tables
901 variableCode_tbl.DELETE;
902 FOR i IN l_udf_var_value_tbl.FIRST..l_udf_var_value_tbl.LAST
903 LOOP
904 l_udf_var_value_tbl.DELETE(i);
905 END LOOP;
906
907 END LOOP;
908
909
910 -- Log all User Defined Variable values in the Pl/sql table
911 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
912 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
913 G_MODULE||l_api_name,
914 '110: User Defined Variable name '||' ' ||'User Defined Variable value');
915 FOR i IN x_udf_var_value_tbl.first..x_udf_var_value_tbl.last LOOP
916 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
917 x_udf_var_value_tbl(i).variable_code ||' '||
918 x_udf_var_value_tbl(i).variable_value_id);
919 END LOOP;
920 END IF;*/
921
922 -- end debug log
923 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
924 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
925 G_MODULE||l_api_name,
926 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
927 END IF;
928
929 EXCEPTION
930
931
932 WHEN FND_API.G_EXC_ERROR THEN
933 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
934 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
935 G_MODULE||l_api_name,
936 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
937 END IF;
938
939 x_return_status := G_RET_STS_ERROR ;
940 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
941
942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
943 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
944 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
945 G_MODULE||l_api_name,
946 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
947 END IF;
948
949 x_return_status := G_RET_STS_UNEXP_ERROR ;
950 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
951
952 WHEN OTHERS THEN
953 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
955 G_MODULE||l_api_name,
956 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
957 END IF;
958
959 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
960 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
961 END IF;
962 x_return_status := G_RET_STS_UNEXP_ERROR ;
963 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
964
965 END get_udv_with_procedures;
966
967
968 ---------------------------------------------------
969 -- Procedure: get_document_values
970 ---------------------------------------------------
971
972 PROCEDURE get_document_values (
973 p_api_version IN NUMBER,
974 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
975 p_doc_type IN VARCHAR2,
976 p_doc_id IN NUMBER,
977 x_return_status OUT NOCOPY VARCHAR2,
978 x_msg_data OUT NOCOPY VARCHAR2,
979 x_msg_count OUT NOCOPY NUMBER,
980 x_hdr_var_value_tbl OUT NOCOPY var_value_tbl_type,
981 x_line_sysvar_value_tbl OUT NOCOPY line_sys_var_value_tbl_type,
982 x_line_count OUT NOCOPY NUMBER,
983 x_line_variables_count OUT NOCOPY NUMBER,
984 x_intent OUT NOCOPY VARCHAR2,
985 x_org_id OUT NOCOPY NUMBER
986 )
987 IS
988
989 l_api_name VARCHAR2(30) := 'get_document_values';
990 l_api_version CONSTANT NUMBER := 1.0;
991
992 l_sys_var_value_tbl var_value_tbl_type;
993 l_category_tbl OKC_TERMS_UTIL_GRP.item_tbl_type;
994 l_item_tbl OKC_TERMS_UTIL_GRP.item_tbl_type;
995 l_constant_tbl constant_tbl_type;
996 l_udf_var_value_tbl udf_var_value_tbl_type;
997 l_udf_var_with_proc_value_tbl udf_var_value_tbl_type;
998 l_clm_udf_tbl udf_var_value_tbl_type; -- CLM Changes
999
1000 l_intent VARCHAR2(1);
1001 var_tbl_cnt NUMBER;
1002 l_org_id NUMBER;
1003 l_line_level_rules_flag VARCHAR2(1);
1004
1005 l_old_variable_value_id VARCHAR2(2500); --Bug# 9595800
1006
1007 CURSOR csr_get_intent (p_doc_type VARCHAR2) IS
1008 SELECT intent
1009 FROM okc_bus_doc_types_b b
1010 WHERE b.document_type = p_doc_type;
1011
1012 CURSOR csr_get_org_id (p_doc_type VARCHAR2, p_doc_id NUMBER) IS
1013 SELECT t.org_id org_id
1014 FROM okc_template_usages u,
1015 okc_terms_templates_all t
1016 WHERE u.document_type = p_doc_type
1017 AND u.document_id = p_doc_id
1018 AND u.template_id = t.template_id;
1019
1020 --Bug# 9595800
1021 CURSOR c_get_old_item(p_variable_value_id VARCHAR2) IS
1022 SELECT DISTINCT OBJECT_VALUE_CODE
1023 FROM okc_xprt_rule_cond_vals val
1024 WHERE InStr(OBJECT_VALUE_CODE,p_variable_value_id||'_',1,1) = 1
1025 AND OBJECT_VALUE_CODE <> p_variable_value_id
1026 AND RULE_CONDITION_ID IN (SELECT rule_condition_id
1027 FROM OKC_XPRT_RULE_CONDITIONS
1028 WHERE OBJECT_CODE = 'OKC$S_ITEM'
1029 AND rule_id IN (SELECT rhdr.rule_id
1030 FROM okc_xprt_rule_hdrs_all rhdr,
1031 okc_template_usages tuse,
1032 okc_xprt_template_rules trule
1033 WHERE tuse.document_id = p_doc_id
1034 AND tuse.document_type = p_doc_type
1035 AND tuse.template_id = trule.template_id
1036 AND trule.rule_id = rhdr.rule_id
1037 AND rhdr.status_code <> 'DRAFT'
1038 UNION
1039 SELECT rhdr.rule_id
1040 FROM okc_xprt_rule_hdrs_all rhdr
1041 WHERE rhdr.org_id = l_org_id
1042 AND rhdr.intent = l_intent
1043 AND rhdr.org_wide_flag = 'Y'
1044 AND rhdr.status_code <> 'DRAFT'
1045 )
1046 );
1047
1048 BEGIN
1049 -- start debug log
1050 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1051 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1052 G_MODULE||l_api_name,
1053 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1054 END IF;
1055
1056 x_return_status := G_RET_STS_SUCCESS;
1057
1058 OPEN csr_get_intent(p_doc_type);
1059 FETCH csr_get_intent INTO l_intent;
1060 IF (csr_get_intent%NOTFOUND) THEN
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 END IF;
1063 CLOSE csr_get_intent;
1064 x_intent := l_intent;
1065
1066 OPEN csr_get_org_id(p_doc_type, p_doc_id);
1067 FETCH csr_get_org_id INTO l_org_id;
1068 IF (csr_get_org_id%NOTFOUND) THEN
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 END IF;
1071 CLOSE csr_get_org_id;
1072 x_org_id := l_org_id;
1073
1074 -- Step 1: Get Header level document Variables
1075 get_system_variables(
1076 p_api_version => 1.0,
1077 p_init_msg_list => p_init_msg_list,
1078 x_return_status => x_return_status,
1079 x_msg_data => x_msg_data,
1080 x_msg_count => x_msg_count,
1081 p_doc_type => p_doc_type,
1082 p_doc_id => p_doc_id,
1083 p_only_doc_variables => 'F',
1084 x_sys_var_value_tbl => l_sys_var_value_tbl);
1085
1086 --- If any errors happen abort API
1087 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1090 RAISE FND_API.G_EXC_ERROR;
1091 END IF;
1092
1093 -- start debug log
1094 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1095 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1096 G_MODULE||l_api_name,
1097 '110: Finished Step1 - Get Header level document variabels');
1098 END IF;
1099
1100
1101 -- Step 2: Check for Line Level Rules
1102 l_line_level_rules_flag := check_line_level_rule_exists(
1103 p_doc_type => p_doc_type,
1104 p_doc_id => p_doc_id,
1105 p_org_id => l_org_id);
1106
1107
1108 -- start debug log
1109 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1110 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1111 G_MODULE||l_api_name,
1112 '120: Finished Step2 - Check for Line level rules');
1113 END IF;
1114
1115 -- Step 3: If Line level rules exist, Get Line level document Variables, else get Item/Item Categories
1116 IF (l_line_level_rules_flag = 'Y')
1117 THEN
1118 get_line_system_variables(
1119 p_api_version => 1.0,
1120 p_init_msg_list => p_init_msg_list,
1121 x_return_status => x_return_status,
1122 x_msg_data => x_msg_data,
1123 x_msg_count => x_msg_count,
1124 p_doc_type => p_doc_type,
1125 p_doc_id => p_doc_id,
1126 p_org_id => l_org_id,
1127 x_line_sys_var_value_tbl => x_line_sysvar_value_tbl,
1128 x_line_count => x_line_count,
1129 x_line_variables_count => x_line_variables_count);
1130
1131 --- If any errors happen abort API
1132 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1134 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1135 RAISE FND_API.G_EXC_ERROR;
1136 END IF;
1137
1138 -- start debug log
1139 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1140 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1141 G_MODULE||l_api_name,
1142 '130: Finished Step3. Line level rules flag is Y. After calling get_line_system_variables');
1143 END IF;
1144
1145 ELSE
1146 OKC_TERMS_UTIL_GRP.get_item_dtl_for_expert(
1147 p_api_version => 1.0,
1148 p_init_msg_list => p_init_msg_list,
1149 x_return_status => x_return_status,
1150 x_msg_data => x_msg_data,
1151 x_msg_count => x_msg_count,
1152 p_doc_type => p_doc_type,
1153 p_doc_id => p_doc_id,
1154 x_category_tbl => l_category_tbl,
1155 x_item_tbl => l_item_tbl);
1156
1157 --- If any errors happen abort API
1158 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1160 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1161 RAISE FND_API.G_EXC_ERROR;
1162 END IF;
1163
1164 -- Add Item/Item Category to header level variables
1165 var_tbl_cnt := l_sys_var_value_tbl.COUNT + 1;
1166 IF l_category_tbl IS NOT NULL THEN
1167 IF l_category_tbl.count > 0 THEN
1168 FOR i IN l_category_tbl.first..l_category_tbl.last LOOP
1169 IF l_intent = 'B' THEN
1170 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_BUY_ITEM_CAT_VAR_NAME;
1171 ELSE
1172 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_SELL_ITEM_CAT_VAR_NAME;
1173 END IF;
1174 l_sys_var_value_tbl(var_tbl_cnt).variable_value_id := l_category_tbl(i).name;
1175 var_tbl_cnt := var_tbl_cnt + 1;
1176 END LOOP;
1177 END IF;
1178 END IF;
1179
1180 IF l_item_tbl IS NOT NULL THEN
1181 IF l_item_tbl.count > 0 THEN
1182 FOR i IN l_item_tbl.first..l_item_tbl.last LOOP
1183 IF l_item_tbl(i).NAME IS NOT NULL THEN
1184 IF l_intent = 'B' THEN
1185 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_BUY_ITEM_VARIABLE_NAME;
1186 ELSE
1187 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_SELL_ITEM_VARIABLE_NAME;
1188 END IF;
1189 l_sys_var_value_tbl(var_tbl_cnt).variable_value_id := l_item_tbl(i).name;
1190 var_tbl_cnt := var_tbl_cnt + 1;
1191 END IF;
1192 END LOOP;
1193 END IF;
1194 END IF;
1195
1196
1197 --Bug# 9595800
1198 IF OKC_CODE_HOOK.IS_NEW_KFF_ITEM_SEG_ENABLED THEN
1199 IF l_sys_var_value_tbl IS NOT NULL THEN
1200 IF l_sys_var_value_tbl.Count > 0 THEN
1201 FOR i IN l_sys_var_value_tbl.first..l_sys_var_value_tbl.last LOOP
1202 IF l_intent <> 'B' THEN
1203 IF l_sys_var_value_tbl(i).variable_code = G_SELL_ITEM_VARIABLE_NAME THEN
1204 OPEN c_get_old_item(l_sys_var_value_tbl(i).variable_value_id);
1205 FETCH c_get_old_item INTO l_old_variable_value_id;
1206 CLOSE c_get_old_item;
1207 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_SELL_ITEM_VARIABLE_NAME;
1208 l_sys_var_value_tbl(var_tbl_cnt).variable_value_id := l_old_variable_value_id;
1209 var_tbl_cnt := var_tbl_cnt + 1;
1210 END IF;
1211 END IF;
1212 END LOOP;
1213 END IF;
1214 END IF;
1215 END IF;
1216
1217
1218 x_line_count := 1; -- Since no Lines, need to set line count to 1 for the CX Java code
1219 x_line_variables_count := 0; -- Since no Lines, need to set line variables count to 0 for the CX Java code
1220
1221 -- start debug log
1222 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1223 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1224 G_MODULE||l_api_name,
1225 '140: Finished Step3. Line level rules flag is N. After calling OKC_TERMS_UTIL_GRP.get_item_dtl_for_expert');
1226 END IF;
1227
1228 END IF;
1229
1230
1231 -- Step 4: Get User Defined Variables
1232 get_user_defined_variables(
1233 p_api_version => 1.0,
1234 p_init_msg_list => p_init_msg_list,
1235 x_return_status => x_return_status,
1236 x_msg_data => x_msg_data,
1237 x_msg_count => x_msg_count,
1238 p_doc_type => p_doc_type,
1239 p_doc_id => p_doc_id,
1240 p_org_id => l_org_id,
1241 p_intent => l_intent,
1242 x_udf_var_value_tbl => l_udf_var_value_tbl);
1243
1244 --- If any errors happen abort API
1245 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1247 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1248 RAISE FND_API.G_EXC_ERROR;
1249 END IF;
1250
1251 -- start debug log
1252 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1253 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1254 G_MODULE||l_api_name,
1255 '150: Finished Step4 - Get User Defined variabels');
1256 END IF;
1257
1258 -- Step 4a: Get User Defined Variables with Procedures - Added for UDV Enhancements
1259 get_udv_with_procedures(
1260 p_api_version => 1.0,
1261 p_init_msg_list => p_init_msg_list,
1262 x_return_status => x_return_status,
1263 x_msg_data => x_msg_data,
1264 x_msg_count => x_msg_count,
1265 p_doc_type => p_doc_type,
1266 p_doc_id => p_doc_id,
1267 p_org_id => l_org_id,
1268 p_intent => l_intent,
1269 x_udf_var_value_tbl => l_udf_var_with_proc_value_tbl);
1270
1271 --- If any errors happen abort API
1272 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1274 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1275 RAISE FND_API.G_EXC_ERROR;
1276 END IF;
1277
1278 -- start debug log
1279 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1280 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1281 G_MODULE||l_api_name,
1282 '150: Finished Step4 - Get User Defined variabels');
1283 END IF;
1284
1285 -- Step 4a: Get User Defined Variables of UDA type for CLM
1286 okc_clm_pkg.get_clm_udv(
1287 p_api_version => 1.0,
1288 p_init_msg_list => p_init_msg_list,
1289 x_return_status => x_return_status,
1290 x_msg_data => x_msg_data,
1291 x_msg_count => x_msg_count,
1292 p_doc_type => p_doc_type,
1293 p_doc_id => p_doc_id,
1294 p_org_id => l_org_id,
1295 p_intent => l_intent,
1296 x_udf_var_value_tbl => l_clm_udf_tbl);
1297
1298 --- If any errors happen abort API
1299 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1302 RAISE FND_API.G_EXC_ERROR;
1303 END IF;
1304
1305 -- start debug log
1306 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1307 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1308 G_MODULE||l_api_name,
1309 '150: Finished Step4 - Get CLM UDA UDVariables');
1310 END IF;
1311
1312 -- Step 5: Get Constant Values
1313 get_constant_values(
1314 p_api_version => 1.0,
1315 p_init_msg_list => p_init_msg_list,
1316 x_return_status => x_return_status,
1317 x_msg_data => x_msg_data,
1318 x_msg_count => x_msg_count,
1319 p_intent => l_intent,
1320 x_constant_tbl => l_constant_tbl);
1321
1322 --- If any errors happen abort API
1323 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1326 RAISE FND_API.G_EXC_ERROR;
1327 END IF;
1328
1329 -- start debug log
1330 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1332 G_MODULE||l_api_name,
1333 '160: Finished Step5 - Get Constant values');
1334 END IF;
1335
1336 -- Step 6: Consolidate Header Variables for Expert Runtime
1337
1338 IF l_sys_var_value_tbl.COUNT > 0 THEN
1339 FOR i IN l_sys_var_value_tbl.first..l_sys_var_value_tbl.last LOOP
1340 IF l_sys_var_value_tbl.EXISTS(i) THEN
1341 x_hdr_var_value_tbl(i).variable_code := l_sys_var_value_tbl(i).variable_code;
1342 x_hdr_var_value_tbl(i).variable_value_id := l_sys_var_value_tbl(i).variable_value_id;
1343 END IF;
1344 END LOOP;
1345 END IF;
1346
1347 -- start debug log
1348 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1349 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1350 G_MODULE||l_api_name,
1351 '161: Finished adding system variable values');
1352 END IF;
1353
1354 var_tbl_cnt := x_hdr_var_value_tbl.count + 1;
1355 IF l_constant_tbl.COUNT > 0 THEN
1356 for i in l_constant_tbl.first..l_constant_tbl.last loop
1357 IF l_constant_tbl.EXISTS(i)THEN
1358 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_constant_tbl(i).constant_id;
1359 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := l_constant_tbl(i).value;
1360 var_tbl_cnt := var_tbl_cnt + 1;
1361 END IF;
1362 end loop;
1363 END IF;
1364
1365 -- start debug log
1366 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1367 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1368 G_MODULE||l_api_name,
1369 '162: Finished adding constant values');
1370 END IF;
1371
1372 var_tbl_cnt := x_hdr_var_value_tbl.count + 1;
1373 IF l_udf_var_value_tbl.COUNT > 0 THEN
1374 for i in l_udf_var_value_tbl.first..l_udf_var_value_tbl.last loop
1375 IF l_udf_var_value_tbl.EXISTS(i) THEN
1376 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_udf_var_value_tbl(i).variable_code;
1377 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := to_char(l_udf_var_value_tbl(i).variable_value_id);
1378 var_tbl_cnt := var_tbl_cnt + 1;
1379 END IF;
1380 end loop;
1381 END IF;
1382
1383 -- Begin: Added for UDV with Procedures enhancement
1384 IF l_udf_var_with_proc_value_tbl.COUNT > 0 THEN
1385 for i in l_udf_var_with_proc_value_tbl.first..l_udf_var_with_proc_value_tbl.last loop
1386 IF l_udf_var_with_proc_value_tbl.EXISTS(i) THEN
1387 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_udf_var_with_proc_value_tbl(i).variable_code;
1388 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := to_char(l_udf_var_with_proc_value_tbl(i).variable_value_id);
1389 var_tbl_cnt := var_tbl_cnt + 1;
1390 END IF;
1391 end loop;
1392 END IF;
1393 -- End: Added for UDV with Procedures enhancement
1394
1395 -- Begin: Added for CLM UDA
1396 IF l_clm_udf_tbl.COUNT > 0 THEN
1397 for i in l_clm_udf_tbl.first..l_clm_udf_tbl.last loop
1398 IF l_clm_udf_tbl.EXISTS(i) THEN
1399 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_clm_udf_tbl(i).variable_code;
1400 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := to_char(l_clm_udf_tbl(i).variable_value_id);
1401 var_tbl_cnt := var_tbl_cnt + 1;
1402 END IF;
1403 end loop;
1404 END IF;
1405 -- End: Added for CLM UDA
1406
1407 -- start debug log
1408 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1409 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1410 G_MODULE||l_api_name,
1411 '163: Finished adding User defined variable values');
1412 END IF;
1413
1414 -- Log all Variable values in the Pl/sql table
1415 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1416 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1417 G_MODULE||l_api_name,
1418 '170: Finished Step6 - Consolidate all variabels');
1419
1420 -- Print All Header Variables
1421 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1422 G_MODULE||l_api_name,
1423 '180: All Header variables ');
1424 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1425 G_MODULE||l_api_name,
1426 '190: Variable name '||' ' ||'Variable value');
1427 IF x_hdr_var_value_tbl.Count > 0 THEN
1428 FOR i IN x_hdr_var_value_tbl.first..x_hdr_var_value_tbl.last LOOP
1429 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1430 x_hdr_var_value_tbl(i).variable_code ||' '||
1431 x_hdr_var_value_tbl(i).variable_value_id);
1432 END LOOP;
1433 END IF;
1434 /*
1435 -- Print All Line Variables
1436 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1437 G_MODULE||l_api_name,
1438 '200: All Line variables ');
1439 IF (l_line_level_rules_flag = 'Y')
1440 FOR i IN x_line_sysvar_value_tbl.FIRST..x_line_sysvar_value_tbl.LAST LOOP
1441
1442 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1443 G_MODULE||l_api_name,
1444 '210: Line Number'||' ' ||
1445 'Variable Name '||' ' ||
1446 'Variable Value '||' ' ||
1447 'Item Id'||' ' ||
1448 'Org Id');
1449
1450 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1451 x_line_sysvar_value_tbl(i).line_number ||' '||
1452 x_line_sysvar_value_tbl(i).variable_code ||' '||
1453 x_line_sysvar_value_tbl(i).variable_value ||' '||
1454 x_line_sysvar_value_tbl(i).item_id ||' '||
1455 x_line_sysvar_value_tbl(i).org_id);
1456 END LOOP;
1457 END IF;
1458
1459 -- Print Line count
1460 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1461 G_MODULE||l_api_name,
1462 '220: No. of Lines: '|| x_line_count);
1463
1464 */
1465 END IF;
1466
1467 -- end debug log
1468 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1469 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1470 G_MODULE||l_api_name,
1471 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1472 END IF;
1473
1474 EXCEPTION
1475
1476
1477 WHEN FND_API.G_EXC_ERROR THEN
1478 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1480 G_MODULE||l_api_name,
1481 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1482 END IF;
1483
1484 x_return_status := G_RET_STS_ERROR ;
1485 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1486
1487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1488 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1489 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1490 G_MODULE||l_api_name,
1491 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1492 END IF;
1493
1494 x_return_status := G_RET_STS_UNEXP_ERROR ;
1495 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1496
1497 WHEN OTHERS THEN
1498 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1499 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1500 G_MODULE||l_api_name,
1501 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1502 END IF;
1503
1504 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1505 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1506 END IF;
1507 x_return_status := G_RET_STS_UNEXP_ERROR ;
1508 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1509
1510 END get_document_values;
1511
1512
1513 END OKC_XPRT_XRULE_VALUES_PVT;