[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.18 2008/02/26 11:12:56 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 var.variable_code IN ( SELECT rcon.object_code variable_code -- LHS of Condition from Template rule
422 FROM okc_xprt_rule_hdrs_all rhdr,
423 okc_xprt_rule_conditions rcon,
424 okc_template_usages tuse,
425 okc_xprt_template_rules trule
426 WHERE tuse.document_id = p_doc_id
427 AND tuse.document_type = p_doc_type
428 AND tuse.template_id = trule.template_id
429 AND trule.rule_id = rhdr.rule_id
430 AND rhdr.rule_id = rcon.rule_id
431 AND rcon.object_type = 'VARIABLE'
432 AND rhdr.status_code <> 'DRAFT'
433 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
434 GROUP BY rcon.object_code
435 UNION
436 SELECT rcon.object_value_code variable_code -- RHS 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_value_type = 'VARIABLE'
447 AND rhdr.status_code <> 'DRAFT'
448 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
449 GROUP BY rcon.object_value_code
450 UNION
451 SELECT rcon.object_code variable_code -- LHS of Condition from Global Rule
452 FROM okc_xprt_rule_hdrs_all rhdr,
453 okc_xprt_rule_conditions rcon
454 WHERE rhdr.rule_id = rcon.rule_id
455 AND rhdr.org_id = p_org_id
456 AND rhdr.intent = p_intent
457 AND rhdr.org_wide_flag = 'Y'
458 AND rcon.object_type = 'VARIABLE'
459 AND rhdr.status_code <> 'DRAFT'
460 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
461 GROUP BY rcon.object_code
462 UNION
463 SELECT rcon.object_value_code variable_code -- RHS of Condition from Global Rule
464 FROM okc_xprt_rule_hdrs_all rhdr,
465 okc_xprt_rule_conditions rcon
466 WHERE rhdr.rule_id = rcon.rule_id
467 AND rhdr.org_id = p_org_id
468 AND rhdr.intent = p_intent
469 AND rhdr.org_wide_flag = 'Y'
470 AND rcon.object_value_type = 'VARIABLE'
471 AND rhdr.status_code <> 'DRAFT'
472 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
473 GROUP BY rcon.object_value_code);
474
475
476 l_api_name VARCHAR2(30) := 'get_user_defined_variables';
477 l_api_version CONSTANT NUMBER := 1.0;
478
479 BEGIN
480
481 -- start debug log
482 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
483 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
484 G_MODULE||l_api_name,
485 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
486 END IF;
487
488 x_return_status := G_RET_STS_SUCCESS;
489
490 OPEN csr_get_udf_variables(p_doc_type, p_doc_id, p_intent, p_org_id);
491 FETCH csr_get_udf_variables BULK COLLECT INTO x_udf_var_value_tbl;
492 CLOSE csr_get_udf_variables;
493
494
495 -- Log all User Defined Variable values in the Pl/sql table
496 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
498 G_MODULE||l_api_name,
499 '110: User Defined Variable name '||' ' ||'User Defined Variable value');
500 FOR i IN x_udf_var_value_tbl.first..x_udf_var_value_tbl.last LOOP
501 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
502 x_udf_var_value_tbl(i).variable_code ||' '||
503 x_udf_var_value_tbl(i).variable_value_id);
504 END LOOP;
505 END IF;*/
506
507 -- end debug log
508 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
509 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
510 G_MODULE||l_api_name,
511 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
512 END IF;
513
514 EXCEPTION
515
516
517 WHEN FND_API.G_EXC_ERROR THEN
518 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
520 G_MODULE||l_api_name,
521 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
522 END IF;
523
524 x_return_status := G_RET_STS_ERROR ;
525 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
526
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
532 END IF;
533
534 x_return_status := G_RET_STS_UNEXP_ERROR ;
535 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
536
537 WHEN OTHERS THEN
538 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
540 G_MODULE||l_api_name,
541 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
542 END IF;
543
544 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
545 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
546 END IF;
547 x_return_status := G_RET_STS_UNEXP_ERROR ;
548 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
549
550 END get_user_defined_variables;
551
552 ---------------------------------------------------
553 -- Procedure: get_constant_values
554 ---------------------------------------------------
555
556 PROCEDURE get_constant_values (
557 p_api_version IN NUMBER,
558 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
559 p_intent IN VARCHAR2,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_data OUT NOCOPY VARCHAR2,
562 x_msg_count OUT NOCOPY NUMBER,
563 x_constant_tbl OUT NOCOPY constant_tbl_type
564 )
565 IS
566 l_api_name VARCHAR2(30) := 'get_constant_values';
567 l_api_version CONSTANT NUMBER := 1.0;
568 l_intent VARCHAR2(1);
569
570 CURSOR csr_constants (p_intent VARCHAR2) IS
571 SELECT 'CONSTANT$' || v.object_value_code constant_id, --
572 q.default_value
573 FROM okc_xprt_rule_cond_vals v,
574 okc_xprt_rule_conditions c,
575 okc_xprt_rule_hdrs_all r,
576 okc_xprt_questions_b q
577 WHERE v.rule_condition_id = c.rule_condition_id
578 AND c.rule_id = r.rule_id
579 AND to_char(q.question_id) = v.object_value_code
580 AND c.object_value_type = 'CONSTANT'
581 AND r.intent = p_intent
582 AND r.status_code <> 'DRAFT'
583 GROUP BY v.object_value_code, q.default_value;
584
585
586 BEGIN
587
588 -- start debug log
589 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
591 G_MODULE||l_api_name,
592 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
593 END IF;
594
595 x_return_status := G_RET_STS_SUCCESS;
596
597
598 OPEN csr_constants(p_intent);
599 FETCH csr_constants BULK COLLECT INTO x_constant_tbl;
600 CLOSE csr_constants;
601
602 -- Log all Variable values in the Pl/sql table
603 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
604 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
605 G_MODULE||l_api_name,
606 '110: Constant name '||' ' ||'Constant value');
607 FOR i IN x_constant_tbl.first..x_constant_tbl.last LOOP
608 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
609 x_constant_tbl(i).constant_id ||' '||
610 x_constant_tbl(i).value);
611 END LOOP;
612 END IF;*/
613
614 -- end debug log
615 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
616 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
617 G_MODULE||l_api_name,
618 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
619 END IF;
620
621 EXCEPTION
622
623 WHEN FND_API.G_EXC_ERROR THEN
624 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
625 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
626 G_MODULE||l_api_name,
627 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
628 END IF;
629
630 x_return_status := G_RET_STS_ERROR ;
631 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
632
633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
634 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
636 G_MODULE||l_api_name,
637 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
638 END IF;
639
640 x_return_status := G_RET_STS_UNEXP_ERROR ;
641 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
642
643 WHEN OTHERS THEN
644 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
646 G_MODULE||l_api_name,
647 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
648 END IF;
649
650 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
651 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
652 END IF;
653 x_return_status := G_RET_STS_UNEXP_ERROR ;
654 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
655
656 END get_constant_values;
657
658 ---------------------------------------------------
659 -- Procedure: get_udv_with_procedures
660 ---------------------------------------------------
661
662 PROCEDURE get_udv_with_procedures (
663 p_api_version IN NUMBER,
664 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
665 p_doc_type IN VARCHAR2,
666 p_doc_id IN NUMBER,
667 p_org_id IN NUMBER,
668 p_intent IN VARCHAR2,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_data OUT NOCOPY VARCHAR2,
671 x_msg_count OUT NOCOPY NUMBER,
672 x_udf_var_value_tbl OUT NOCOPY udf_var_value_tbl_type
673 )
674 IS
675
676 CURSOR csr_get_udv_with_proc (p_doc_type VARCHAR2,p_doc_id NUMBER, p_intent VARCHAR2, p_org_id NUMBER) IS
677 SELECT var.variable_code variable_code, --Removed USER$ to resolve Rule firing for UDV with Procedures
678 procedure_name procedure_name
679 FROM okc_bus_variables_b var
680 WHERE var.variable_source = 'P'
681 AND var.variable_code IN
682 (SELECT distinct rcon.object_code variable_code -- LHS of Condition from Template rule
683 FROM okc_xprt_rule_hdrs_all rhdr,
684 okc_xprt_rule_conditions rcon,
685 okc_template_usages tuse,
686 okc_xprt_template_rules trule
687 WHERE tuse.document_id = p_doc_id
688 AND tuse.document_type = p_doc_type
689 AND tuse.template_id = trule.template_id
690 AND trule.rule_id = rhdr.rule_id
691 AND rhdr.rule_id = rcon.rule_id
692 AND rcon.object_type = 'VARIABLE'
693 AND rhdr.status_code <> 'DRAFT'
694 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
695 GROUP BY rcon.object_code
696 UNION
697 SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Template rule
698 FROM okc_xprt_rule_hdrs_all rhdr,
699 okc_xprt_rule_conditions rcon,
700 okc_template_usages tuse,
701 okc_xprt_template_rules trule
702 WHERE tuse.document_id = p_doc_id
703 AND tuse.document_type = p_doc_type
704 AND tuse.template_id = trule.template_id
705 AND trule.rule_id = rhdr.rule_id
706 AND rhdr.rule_id = rcon.rule_id
707 AND rcon.object_value_type = 'VARIABLE'
708 AND rhdr.status_code <> 'DRAFT'
709 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
710 GROUP BY rcon.object_value_code
711 UNION
712 SELECT distinct rcon.object_code variable_code -- LHS of Condition from Global Rule
713 FROM okc_xprt_rule_hdrs_all rhdr,
714 okc_xprt_rule_conditions rcon
715 WHERE rhdr.rule_id = rcon.rule_id
716 AND rhdr.org_id = p_org_id
717 AND rhdr.intent = p_intent
718 AND rhdr.org_wide_flag = 'Y'
719 AND rcon.object_type = 'VARIABLE'
720 AND rhdr.status_code <> 'DRAFT'
721 AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
722 GROUP BY rcon.object_code
723 UNION
724 SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Global Rule
725 FROM okc_xprt_rule_hdrs_all rhdr,
726 okc_xprt_rule_conditions rcon
727 WHERE rhdr.rule_id = rcon.rule_id
728 AND rhdr.org_id = p_org_id
729 AND rhdr.intent = p_intent
730 AND rhdr.org_wide_flag = 'Y'
731 AND rcon.object_value_type = 'VARIABLE'
732 AND rhdr.status_code <> 'DRAFT'
733 AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
734 GROUP BY rcon.object_value_code);
735
736 CURSOR csr_get_uniq_proc (p_sequence_id NUMBER) IS
737 SELECT distinct procedure_name procedure_name
738 FROM okc_xprt_deviations_t
739 WHERE run_id = p_sequence_id;
740
741
742 CURSOR csr_get_vars_for_proc (p_procedure_name VARCHAR2, p_sequence_id NUMBER) IS
743 SELECT distinct variable_code variable_code
744 FROM okc_xprt_deviations_t
745 WHERE run_id = p_sequence_id
746 AND procedure_name = p_procedure_name;
747
748 l_api_name VARCHAR2(30) := 'get_udv_with_procedures';
749 l_api_version CONSTANT NUMBER := 1.0;
750
751 l_sql_stmt LONG;
752 l_sequence_id NUMBER;
753 var_tbl_cnt NUMBER := 1;
754 l_udf_var_value_tbl OKC_XPRT_XRULE_VALUES_PVT.udf_var_value_tbl_type;
755
756
757 TYPE VariableCodeList IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; -- changed for R12
758 variableCode_tbl VariableCodeList;
759
760 BEGIN
761
762 -- start debug log
763 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
765 G_MODULE||l_api_name,
766 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
767 END IF;
768
769 x_return_status := G_RET_STS_SUCCESS;
770
771 SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_sequence_id from DUAL;
772
773 FOR csr_get_udv_with_proc_rec IN csr_get_udv_with_proc(p_doc_type, p_doc_id, p_intent, p_org_id)
774 LOOP
775 INSERT INTO OKC_XPRT_DEVIATIONS_T
776 (
777 RUN_ID,
778 LINE_NUMBER,
779 VARIABLE_CODE,
780 VARIABLE_VALUE,
781 ITEM_ID,
782 ORG_ID,
783 CREATION_DATE,
784 PROCEDURE_NAME
785 )
786 VALUES
787 (
788 l_sequence_id, -- RUN_ID
789 NULL, -- LINE_NUMBER
790 csr_get_udv_with_proc_rec.variable_code, -- VARIABLE_CODE
791 NULL, -- VARIABLE_VALUE
792 NULL, -- ITEM_ID
793 NULL, -- ORG_ID
794 NULL, -- CREATION_DATE
795 csr_get_udv_with_proc_rec.procedure_name -- PROCEDURE_NAME
796 );
797 END LOOP;
798
799 FOR csr_get_uniq_proc_rec IN csr_get_uniq_proc(l_sequence_id)
800 LOOP
801 OPEN csr_get_vars_for_proc(csr_get_uniq_proc_rec.procedure_name, l_sequence_id);
802 FETCH csr_get_vars_for_proc BULK COLLECT INTO variableCode_tbl;
803 CLOSE csr_get_vars_for_proc;
804
805 FOR i IN 1..variableCode_tbl.COUNT
806 LOOP
807
808 l_udf_var_value_tbl(i).variable_code := variableCode_tbl(i);
809
810 -- Dynamically build SQL to execute the procedure
811 l_sql_stmt := 'BEGIN ' || csr_get_uniq_proc_rec.procedure_name || '(' ||
812 'x_return_status =>' || ':1' || ',' ||
813 'x_msg_data =>' || ':2' || ',' ||
814 'x_msg_count =>' || ':3' || ',' ||
815 'p_doc_type =>' || ':4' || ',' ||
816 'p_doc_id =>' || ':5' || ',' ||
817 'p_variable_code =>' || ':6' || ',' ||
818 'x_variable_value_id =>' || ':7' || ' ' ||
819 '); END;';
820
821 -- execute the dynamic sql
822 -- start debug log
823 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
825 G_MODULE||l_api_name,
826 '105: l_sql_stmt '|| l_sql_stmt);
827 END IF;
828
829 BEGIN
830 EXECUTE IMMEDIATE l_sql_stmt USING OUT x_return_status, OUT x_msg_data, OUT x_msg_count, p_doc_type,
831 p_doc_id, variableCode_tbl(i), IN OUT l_udf_var_value_tbl(i).variable_value_id;
832 END;
833
834 -- If any errors happen abort API
835 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841 END LOOP;
842
843 -- After executing add the list to the output table
844 IF l_udf_var_value_tbl IS NOT NULL THEN
845 IF l_udf_var_value_tbl.count > 0 THEN
846 FOR i IN l_udf_var_value_tbl.first..l_udf_var_value_tbl.last LOOP
847 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
848 x_udf_var_value_tbl(var_tbl_cnt).variable_value_id := l_udf_var_value_tbl(i).variable_value_id;
849 var_tbl_cnt := var_tbl_cnt + 1;
850 END LOOP;
851 END IF;
852 END IF;
853
854 -- Clear out the PL/SQL tables
855 variableCode_tbl.DELETE;
856 FOR i IN l_udf_var_value_tbl.FIRST..l_udf_var_value_tbl.LAST
857 LOOP
858 l_udf_var_value_tbl.DELETE(i);
859 END LOOP;
860
861 END LOOP;
862
863
864 -- Log all User Defined Variable values in the Pl/sql table
865 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
866 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
867 G_MODULE||l_api_name,
868 '110: User Defined Variable name '||' ' ||'User Defined Variable value');
869 FOR i IN x_udf_var_value_tbl.first..x_udf_var_value_tbl.last LOOP
870 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
871 x_udf_var_value_tbl(i).variable_code ||' '||
872 x_udf_var_value_tbl(i).variable_value_id);
873 END LOOP;
874 END IF;*/
875
876 -- end debug log
877 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
878 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
879 G_MODULE||l_api_name,
880 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
881 END IF;
882
883 EXCEPTION
884
885
886 WHEN FND_API.G_EXC_ERROR THEN
887 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
888 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
889 G_MODULE||l_api_name,
890 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
891 END IF;
892
893 x_return_status := G_RET_STS_ERROR ;
894 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
895
896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
897 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
899 G_MODULE||l_api_name,
900 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
901 END IF;
902
903 x_return_status := G_RET_STS_UNEXP_ERROR ;
904 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
905
906 WHEN OTHERS THEN
907 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
908 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
909 G_MODULE||l_api_name,
910 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
911 END IF;
912
913 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
914 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
915 END IF;
916 x_return_status := G_RET_STS_UNEXP_ERROR ;
917 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
918
919 END get_udv_with_procedures;
920
921
922 ---------------------------------------------------
923 -- Procedure: get_document_values
924 ---------------------------------------------------
925
926 PROCEDURE get_document_values (
927 p_api_version IN NUMBER,
928 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
929 p_doc_type IN VARCHAR2,
930 p_doc_id IN NUMBER,
931 x_return_status OUT NOCOPY VARCHAR2,
932 x_msg_data OUT NOCOPY VARCHAR2,
933 x_msg_count OUT NOCOPY NUMBER,
934 x_hdr_var_value_tbl OUT NOCOPY var_value_tbl_type,
935 x_line_sysvar_value_tbl OUT NOCOPY line_sys_var_value_tbl_type,
936 x_line_count OUT NOCOPY NUMBER,
937 x_line_variables_count OUT NOCOPY NUMBER,
938 x_intent OUT NOCOPY VARCHAR2,
939 x_org_id OUT NOCOPY NUMBER
940 )
941 IS
942
943 l_api_name VARCHAR2(30) := 'get_document_values';
944 l_api_version CONSTANT NUMBER := 1.0;
945
946 l_sys_var_value_tbl var_value_tbl_type;
947 l_category_tbl OKC_TERMS_UTIL_GRP.item_tbl_type;
948 l_item_tbl OKC_TERMS_UTIL_GRP.item_tbl_type;
949 l_constant_tbl constant_tbl_type;
950 l_udf_var_value_tbl udf_var_value_tbl_type;
951 l_udf_var_with_proc_value_tbl udf_var_value_tbl_type;
952
953 l_intent VARCHAR2(1);
954 var_tbl_cnt NUMBER;
955 l_org_id NUMBER;
956 l_line_level_rules_flag VARCHAR2(1);
957
958 CURSOR csr_get_intent (p_doc_type VARCHAR2) IS
959 SELECT intent
960 FROM okc_bus_doc_types_b b
961 WHERE b.document_type = p_doc_type;
962
963 CURSOR csr_get_org_id (p_doc_type VARCHAR2, p_doc_id NUMBER) IS
964 SELECT t.org_id org_id
965 FROM okc_template_usages u,
966 okc_terms_templates_all t
967 WHERE u.document_type = p_doc_type
968 AND u.document_id = p_doc_id
969 AND u.template_id = t.template_id;
970
971
972 BEGIN
973 -- start debug log
974 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
975 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
976 G_MODULE||l_api_name,
977 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
978 END IF;
979
980 x_return_status := G_RET_STS_SUCCESS;
981
982 OPEN csr_get_intent(p_doc_type);
983 FETCH csr_get_intent INTO l_intent;
984 IF (csr_get_intent%NOTFOUND) THEN
985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
986 END IF;
987 CLOSE csr_get_intent;
988 x_intent := l_intent;
989
990 OPEN csr_get_org_id(p_doc_type, p_doc_id);
991 FETCH csr_get_org_id INTO l_org_id;
992 IF (csr_get_org_id%NOTFOUND) THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995 CLOSE csr_get_org_id;
996 x_org_id := l_org_id;
997
998 -- Step 1: Get Header level document Variables
999 get_system_variables(
1000 p_api_version => 1.0,
1001 p_init_msg_list => p_init_msg_list,
1002 x_return_status => x_return_status,
1003 x_msg_data => x_msg_data,
1004 x_msg_count => x_msg_count,
1005 p_doc_type => p_doc_type,
1006 p_doc_id => p_doc_id,
1007 p_only_doc_variables => 'F',
1008 x_sys_var_value_tbl => l_sys_var_value_tbl);
1009
1010 --- If any errors happen abort API
1011 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1013 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1014 RAISE FND_API.G_EXC_ERROR;
1015 END IF;
1016
1017 -- start debug log
1018 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1020 G_MODULE||l_api_name,
1021 '110: Finished Step1 - Get Header level document variabels');
1022 END IF;
1023
1024
1025 -- Step 2: Check for Line Level Rules
1026 l_line_level_rules_flag := check_line_level_rule_exists(
1027 p_doc_type => p_doc_type,
1028 p_doc_id => p_doc_id,
1029 p_org_id => l_org_id);
1030
1031
1032 -- start debug log
1033 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1034 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1035 G_MODULE||l_api_name,
1036 '120: Finished Step2 - Check for Line level rules');
1037 END IF;
1038
1039 -- Step 3: If Line level rules exist, Get Line level document Variables, else get Item/Item Categories
1040 IF (l_line_level_rules_flag = 'Y')
1041 THEN
1042 get_line_system_variables(
1043 p_api_version => 1.0,
1044 p_init_msg_list => p_init_msg_list,
1045 x_return_status => x_return_status,
1046 x_msg_data => x_msg_data,
1047 x_msg_count => x_msg_count,
1048 p_doc_type => p_doc_type,
1049 p_doc_id => p_doc_id,
1050 p_org_id => l_org_id,
1051 x_line_sys_var_value_tbl => x_line_sysvar_value_tbl,
1052 x_line_count => x_line_count,
1053 x_line_variables_count => x_line_variables_count);
1054
1055 --- If any errors happen abort API
1056 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1058 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1059 RAISE FND_API.G_EXC_ERROR;
1060 END IF;
1061
1062 -- start debug log
1063 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1065 G_MODULE||l_api_name,
1066 '130: Finished Step3. Line level rules flag is Y. After calling get_line_system_variables');
1067 END IF;
1068
1069 ELSE
1070 OKC_TERMS_UTIL_GRP.get_item_dtl_for_expert(
1071 p_api_version => 1.0,
1072 p_init_msg_list => p_init_msg_list,
1073 x_return_status => x_return_status,
1074 x_msg_data => x_msg_data,
1075 x_msg_count => x_msg_count,
1076 p_doc_type => p_doc_type,
1077 p_doc_id => p_doc_id,
1078 x_category_tbl => l_category_tbl,
1079 x_item_tbl => l_item_tbl);
1080
1081 --- If any errors happen abort API
1082 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1085 RAISE FND_API.G_EXC_ERROR;
1086 END IF;
1087
1088 -- Add Item/Item Category to header level variables
1089 var_tbl_cnt := l_sys_var_value_tbl.COUNT + 1;
1090 IF l_category_tbl IS NOT NULL THEN
1091 IF l_category_tbl.count > 0 THEN
1092 FOR i IN l_category_tbl.first..l_category_tbl.last LOOP
1093 IF l_intent = 'B' THEN
1094 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_BUY_ITEM_CAT_VAR_NAME;
1095 ELSE
1096 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_SELL_ITEM_CAT_VAR_NAME;
1097 END IF;
1098 l_sys_var_value_tbl(var_tbl_cnt).variable_value_id := l_category_tbl(i).name;
1099 var_tbl_cnt := var_tbl_cnt + 1;
1100 END LOOP;
1101 END IF;
1102 END IF;
1103
1104 IF l_item_tbl IS NOT NULL THEN
1105 IF l_item_tbl.count > 0 THEN
1106 FOR i IN l_item_tbl.first..l_item_tbl.last LOOP
1107 IF l_item_tbl(i).NAME IS NOT NULL THEN
1108 IF l_intent = 'B' THEN
1109 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_BUY_ITEM_VARIABLE_NAME;
1110 ELSE
1111 l_sys_var_value_tbl(var_tbl_cnt).variable_code := G_SELL_ITEM_VARIABLE_NAME;
1112 END IF;
1113 l_sys_var_value_tbl(var_tbl_cnt).variable_value_id := l_item_tbl(i).name;
1114 var_tbl_cnt := var_tbl_cnt + 1;
1115 END IF;
1116 END LOOP;
1117 END IF;
1118 END IF;
1119
1120 x_line_count := 1; -- Since no Lines, need to set line count to 1 for the CX Java code
1121 x_line_variables_count := 0; -- Since no Lines, need to set line variables count to 0 for the CX Java code
1122
1123 -- start debug log
1124 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1125 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1126 G_MODULE||l_api_name,
1127 '140: Finished Step3. Line level rules flag is N. After calling OKC_TERMS_UTIL_GRP.get_item_dtl_for_expert');
1128 END IF;
1129
1130 END IF;
1131
1132
1133 -- Step 4: Get User Defined Variables
1134 get_user_defined_variables(
1135 p_api_version => 1.0,
1136 p_init_msg_list => p_init_msg_list,
1137 x_return_status => x_return_status,
1138 x_msg_data => x_msg_data,
1139 x_msg_count => x_msg_count,
1140 p_doc_type => p_doc_type,
1141 p_doc_id => p_doc_id,
1142 p_org_id => l_org_id,
1143 p_intent => l_intent,
1144 x_udf_var_value_tbl => l_udf_var_value_tbl);
1145
1146 --- If any errors happen abort API
1147 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1149 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1150 RAISE FND_API.G_EXC_ERROR;
1151 END IF;
1152
1153 -- start debug log
1154 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1155 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1156 G_MODULE||l_api_name,
1157 '150: Finished Step4 - Get User Defined variabels');
1158 END IF;
1159
1160 -- Step 4a: Get User Defined Variables with Procedures - Added for UDV Enhancements
1161 get_udv_with_procedures(
1162 p_api_version => 1.0,
1163 p_init_msg_list => p_init_msg_list,
1164 x_return_status => x_return_status,
1165 x_msg_data => x_msg_data,
1166 x_msg_count => x_msg_count,
1167 p_doc_type => p_doc_type,
1168 p_doc_id => p_doc_id,
1169 p_org_id => l_org_id,
1170 p_intent => l_intent,
1171 x_udf_var_value_tbl => l_udf_var_with_proc_value_tbl);
1172
1173 --- If any errors happen abort API
1174 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1176 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1177 RAISE FND_API.G_EXC_ERROR;
1178 END IF;
1179
1180 -- start debug log
1181 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1182 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1183 G_MODULE||l_api_name,
1184 '150: Finished Step4 - Get User Defined variabels');
1185 END IF;
1186
1187 -- Step 5: Get Constant Values
1188 get_constant_values(
1189 p_api_version => 1.0,
1190 p_init_msg_list => p_init_msg_list,
1191 x_return_status => x_return_status,
1192 x_msg_data => x_msg_data,
1193 x_msg_count => x_msg_count,
1194 p_intent => l_intent,
1195 x_constant_tbl => l_constant_tbl);
1196
1197 --- If any errors happen abort API
1198 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1200 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1201 RAISE FND_API.G_EXC_ERROR;
1202 END IF;
1203
1204 -- start debug log
1205 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1206 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1207 G_MODULE||l_api_name,
1208 '160: Finished Step5 - Get Constant values');
1209 END IF;
1210
1211 -- Step 6: Consolidate Header Variables for Expert Runtime
1212
1213 IF l_sys_var_value_tbl.COUNT > 0 THEN
1214 FOR i IN l_sys_var_value_tbl.first..l_sys_var_value_tbl.last LOOP
1215 IF l_sys_var_value_tbl.EXISTS(i) THEN
1216 x_hdr_var_value_tbl(i).variable_code := l_sys_var_value_tbl(i).variable_code;
1217 x_hdr_var_value_tbl(i).variable_value_id := l_sys_var_value_tbl(i).variable_value_id;
1218 END IF;
1219 END LOOP;
1220 END IF;
1221
1222 -- start debug log
1223 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1224 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1225 G_MODULE||l_api_name,
1226 '161: Finished adding system variable values');
1227 END IF;
1228
1229 var_tbl_cnt := x_hdr_var_value_tbl.count + 1;
1230 IF l_constant_tbl.COUNT > 0 THEN
1231 for i in l_constant_tbl.first..l_constant_tbl.last loop
1232 IF l_constant_tbl.EXISTS(i)THEN
1233 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_constant_tbl(i).constant_id;
1234 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := l_constant_tbl(i).value;
1235 var_tbl_cnt := var_tbl_cnt + 1;
1236 END IF;
1237 end loop;
1238 END IF;
1239
1240 -- start debug log
1241 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1243 G_MODULE||l_api_name,
1244 '162: Finished adding constant values');
1245 END IF;
1246
1247 var_tbl_cnt := x_hdr_var_value_tbl.count + 1;
1248 IF l_udf_var_value_tbl.COUNT > 0 THEN
1249 for i in l_udf_var_value_tbl.first..l_udf_var_value_tbl.last loop
1250 IF l_udf_var_value_tbl.EXISTS(i) THEN
1251 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_udf_var_value_tbl(i).variable_code;
1252 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := to_char(l_udf_var_value_tbl(i).variable_value_id);
1253 var_tbl_cnt := var_tbl_cnt + 1;
1254 END IF;
1255 end loop;
1256 END IF;
1257
1258 -- Begin: Added for UDV with Procedures enhancement
1259 IF l_udf_var_with_proc_value_tbl.COUNT > 0 THEN
1260 for i in l_udf_var_with_proc_value_tbl.first..l_udf_var_with_proc_value_tbl.last loop
1261 IF l_udf_var_with_proc_value_tbl.EXISTS(i) THEN
1262 x_hdr_var_value_tbl(var_tbl_cnt).variable_code := l_udf_var_with_proc_value_tbl(i).variable_code;
1263 x_hdr_var_value_tbl(var_tbl_cnt).variable_value_id := to_char(l_udf_var_with_proc_value_tbl(i).variable_value_id);
1264 var_tbl_cnt := var_tbl_cnt + 1;
1265 END IF;
1266 end loop;
1267 END IF;
1268 -- End: Added for UDV with Procedures enhancement
1269
1270 -- start debug log
1271 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1272 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1273 G_MODULE||l_api_name,
1274 '163: Finished adding User defined variable values');
1275 END IF;
1276
1277 -- Log all Variable values in the Pl/sql table
1278 /*IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1279 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1280 G_MODULE||l_api_name,
1281 '170: Finished Step6 - Consolidate all variabels');
1282
1283 -- Print All Header Variables
1284 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1285 G_MODULE||l_api_name,
1286 '180: All Header variables ');
1287 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1288 G_MODULE||l_api_name,
1289 '190: Variable name '||' ' ||'Variable value');
1290 FOR i IN x_hdr_var_value_tbl.first..x_hdr_var_value_tbl.last LOOP
1291 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1292 x_hdr_var_value_tbl(i).variable_code ||' '||
1293 x_hdr_var_value_tbl(i).variable_value_id);
1294 END LOOP;
1295
1296 -- Print All Line Variables
1297 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1298 G_MODULE||l_api_name,
1299 '200: All Line variables ');
1300 IF (l_line_level_rules_flag = 'Y')
1301 FOR i IN x_line_sysvar_value_tbl.FIRST..x_line_sysvar_value_tbl.LAST LOOP
1302
1303 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1304 G_MODULE||l_api_name,
1305 '210: Line Number'||' ' ||
1306 'Variable Name '||' ' ||
1307 'Variable Value '||' ' ||
1308 'Item Id'||' ' ||
1309 'Org Id');
1310
1311 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1312 x_line_sysvar_value_tbl(i).line_number ||' '||
1313 x_line_sysvar_value_tbl(i).variable_code ||' '||
1314 x_line_sysvar_value_tbl(i).variable_value ||' '||
1315 x_line_sysvar_value_tbl(i).item_id ||' '||
1316 x_line_sysvar_value_tbl(i).org_id);
1317 END LOOP;
1318 END IF;
1319
1320 -- Print Line count
1321 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1322 G_MODULE||l_api_name,
1323 '220: No. of Lines: '|| x_line_count);
1324
1325
1326 END IF;*/
1327
1328 -- end debug log
1329 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1331 G_MODULE||l_api_name,
1332 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1333 END IF;
1334
1335 EXCEPTION
1336
1337
1338 WHEN FND_API.G_EXC_ERROR THEN
1339 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1340 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1341 G_MODULE||l_api_name,
1342 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1343 END IF;
1344
1345 x_return_status := G_RET_STS_ERROR ;
1346 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1347
1348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1349 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1350 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1351 G_MODULE||l_api_name,
1352 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1353 END IF;
1354
1355 x_return_status := G_RET_STS_UNEXP_ERROR ;
1356 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1357
1358 WHEN OTHERS THEN
1359 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1360 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1361 G_MODULE||l_api_name,
1362 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1363 END IF;
1364
1365 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1366 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1367 END IF;
1368 x_return_status := G_RET_STS_UNEXP_ERROR ;
1369 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1370
1371 END get_document_values;
1372
1373
1374 END OKC_XPRT_XRULE_VALUES_PVT;