[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_IMPORT_RULES_PVT
Source
1 PACKAGE BODY OKC_XPRT_IMPORT_RULES_PVT AS
2 /* $Header: OKCVXRULB.pls 120.7 2006/03/29 16:06:46 arsundar noship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL VARIABLES
7 ---------------------------------------------------------------------------
8
9 ------------------------------------------------------------------------------
10 -- GLOBAL CONSTANTS
11 ------------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_IMPORT_RULES_PVT';
13 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
14
15 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
17 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
18
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21
22 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
23 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
24 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
25
26 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
27 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
28 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
29 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
30
31
32 ------------------------------------------------------------------------------
33 -- Orig_sys_ref
34 ------------------------------------------------------------------------------
35 G_VARIABLE_MODEL_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODEL:-99:';
36
37 G_CLAUSE_MODEL_OSR CONSTANT VARCHAR2(255) := 'OKC:CLAUSEMODEL:';
38 G_CLAUSE_MODEL_TOPNODE_OSR CONSTANT VARCHAR2(255) := 'OKC:CLAUSEMODELTOPNODE:' ;
39 G_CLAUSE_MODEL_FEATURE_OSR CONSTANT VARCHAR2(255) := 'OKC:CLAUSEMODELFEATURE:' ;
40 G_CLAUSE_MODEL_VM_REF_NODE_OSR CONSTANT VARCHAR2(255) := 'OKC:CLAUSEMODEL-VARIABLEMODEL-REFNODE:' ;
41
42 G_TEMPLATE_MODEL_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODEL:';
43 G_TEMPLATE_MODEL_TOPNODE_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODELTOPNODE:' ;
44 G_TMPL_MODEL_CM_REF_NODE_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODEL-CLAUSEMODEL-REFNODE:' ;
45
46 G_RULE_OSR CONSTANT VARCHAR2(255) := 'OKC:RULES:';
47 G_XTN_RULE_OSR CONSTANT VARCHAR2(255) := 'OKC:XTNRULE:';
48 G_XTN_RULE_TEXT CONSTANT VARCHAR2(255) := 'CALL doInputAttributeTransfer(SYSTEM_PARAMETER("BaseNode")) WHEN postConfigInit IN Global';
49 G_XTN_RULE_CLASS_NAME CONSTANT VARCHAR2(255) := 'oracle.apps.okc.contractexpert.util.CeExtension';
50
51
52 /*====================================================================+
53 Procedure Name : build_statement_rule
54 Description : This API builds the rule statement. It parses the rules data
55 in OKC tables and builds the rule text to be used in rule import
56 Parameters:
57 p_rule_id - Rule Id
58 p_template_id - Template Id to which the rule is attached
59 x_stmt_rule - Rule statement built by the rule parser
60
61 +====================================================================*/
62
63 PROCEDURE build_statement_rule
64 (
65 p_rule_id IN NUMBER,
66 p_template_id IN NUMBER,
67 x_stmt_rule OUT NOCOPY CLOB,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_data OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER
71 )
72 IS
73 CURSOR csr_rule_dtls IS
74 SELECT org_id,
75 intent,
76 DECODE(condition_expr_code,'ALL','AND','OR'),
77 rule_type
78 FROM okc_xprt_rule_hdrs_all
79 WHERE rule_id = p_rule_id;
80
81 --DECODE(SUBSTR(rcon.object_code,1,3),'OKC',rcon.object_code,'USER$' || rcon.object_code) variable_code
82 CURSOR csr_rule_conditions IS
83 SELECT c.rule_condition_id,
84 c.object_type,
85 DECODE(c.object_type,
86 'VARIABLE',DECODE(SUBSTR(c.object_code,1,3),'OKC',c.object_code,'USER$' || c.object_code),
87 'CONSTANT','CONSTANT$' || c.object_code,
88 c.object_code) object_code,
89 --c.object_code,
90 NVL(c.object_code_datatype,'V'),
91 DECODE(c.operator,'IS','ANYTRUE',
92 'IN','ANYTRUE',
93 'IS_NOT','NOTTRUE',
94 'NOT_IN','NOTTRUE',
95 c.operator),
96 c.object_value_type
97 FROM okc_xprt_rule_conditions c
98 WHERE c.rule_id = p_rule_id;
99
100 /*CURSOR csr_rule_cond_vals(p_rule_condition_id IN NUMBER) IS
101 SELECT v.object_value_code
102 FROM okc_xprt_rule_cond_vals v
103 WHERE v.rule_condition_id = p_rule_condition_id;*/
104
105 CURSOR csr_rule_cond_vals(p_rule_condition_id IN NUMBER) IS
106 SELECT DECODE(c.object_value_type,
107 'VARIABLE',DECODE(SUBSTR(v.object_value_code,1,3),'OKC',v.object_value_code,'USER$' || v.object_value_code),
108 'CONSTANT','CONSTANT$' || v.object_value_code,
109 v.object_value_code) object_value_code
110 --v.object_value_code
111 FROM okc_xprt_rule_cond_vals v, okc_xprt_rule_conditions c
112 WHERE v.rule_condition_id = c.rule_condition_id
113 AND v.rule_condition_id = p_rule_condition_id;
114
115 CURSOR csr_rule_outcomes IS
116 SELECT object_type,
117 object_value_id
118 FROM okc_xprt_rule_outcomes
119 WHERE rule_id = p_rule_id;
120
121 l_api_name CONSTANT VARCHAR2(30) := 'build_statement_rule';
122 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
123 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
124 l_condition_expr_code okc_xprt_rule_hdrs_all.condition_expr_code%TYPE;
125 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
126
127 l_rule_condition_id okc_xprt_rule_conditions.rule_condition_id%TYPE;
128 l_object_type okc_xprt_rule_conditions.object_type%TYPE;
129 l_object_code okc_xprt_rule_conditions.object_code%TYPE;
130 l_object_code_datatype okc_xprt_rule_conditions.object_code_datatype%TYPE;
131 l_operator okc_xprt_rule_conditions.operator%TYPE;
132 l_object_value_type okc_xprt_rule_conditions.object_value_type%TYPE;
133
134 l_object_value_code okc_xprt_rule_cond_vals.object_value_code%TYPE;
135
136 l_object_type_outcome okc_xprt_rule_outcomes.object_type%TYPE;
137 l_object_value_id okc_xprt_rule_outcomes.object_value_id%TYPE;
138
139 l_clause_prefix VARCHAR2(4000);
140 l_system_var_prefix VARCHAR2(4000);
141 l_question_prefix VARCHAR2(4000);
142 l_deviation_prefix VARCHAR2(4000);
143 -- l_stmt_rule cz_imp_rules.rule_text%TYPE :='';
144 l_condition_stmt cz_imp_rules.rule_text%TYPE ;
145 l_outcome_stmt cz_imp_rules.rule_text%TYPE ;
146
147 l_temp_string VARCHAR2(4000);
148
149 BEGIN
150
151 -- start debug log
152 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
154 G_MODULE||l_api_name,
155 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
156 END IF;
157
158 -- Initialize API return status to success
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 -- 8i 9i compatibility issue
162 -- initialize the CLOB
163 DBMS_LOB.CREATETEMPORARY(x_stmt_rule, FALSE, DBMS_LOB.CALL);
164
165 OPEN csr_rule_dtls;
166 FETCH csr_rule_dtls INTO l_org_id, l_intent, l_condition_expr_code,l_rule_type;
167 CLOSE csr_rule_dtls;
168
169 -- debug log
170 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
172 G_MODULE||l_api_name,
173 '150: p_rule_id :'||p_rule_id);
174 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
175 G_MODULE||l_api_name,
176 '150: p_template_id :'||p_template_id);
177 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
178 G_MODULE||l_api_name,
179 '150: l_org_id :'||l_org_id);
180 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
181 G_MODULE||l_api_name,
182 '150: l_intent :'||l_intent);
183 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
184 G_MODULE||l_api_name,
185 '150: l_condition_expr_code :'||l_condition_expr_code);
186 END IF;
187
188
189 -- Build Template Rule Statement
190
191 -- Clause Prefix
192 l_clause_prefix := ''''||G_TEMPLATE_MODEL_TOPNODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.'||
193 ''''||G_TMPL_MODEL_CM_REF_NODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.'||
194 ''''||G_CLAUSE_MODEL_FEATURE_OSR||l_org_id||':'||l_intent||''''||'.' ;
195 -- System Variables Prefix
196 l_system_var_prefix:= ''''||G_TEMPLATE_MODEL_TOPNODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.'||
197 ''''||G_TMPL_MODEL_CM_REF_NODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.'||
198 ''''||G_CLAUSE_MODEL_VM_REF_NODE_OSR||l_org_id||':'||l_intent||''''||'.';
199
200 -- Question Prefix
201 l_question_prefix:= ''''||G_TEMPLATE_MODEL_TOPNODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.';
202
203
204 -- Deviation Prefix
205 l_deviation_prefix:= ''''||G_TEMPLATE_MODEL_TOPNODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.'||
206 ''''||G_TMPL_MODEL_CM_REF_NODE_OSR||l_org_id||':'||l_intent||':'||p_template_id||''''||'.'||
207 ''''||G_CLAUSE_MODEL_VM_REF_NODE_OSR||l_org_id||':'||l_intent||''''||'.';
208
209 /*
210 -- Org Rules Prefix
211
212 -- Clause Prefix
213 l_clause_prefix := ''''||G_CLAUSE_MODEL_TOPNODE_OSR||l_org_id||':'||l_intent||''''||'.'||
214 ''''||G_CLAUSE_MODEL_FEATURE_OSR||l_org_id||':'||l_intent||''''||'.';
215
216 -- System Variables Prefix
217 l_system_var_prefix := ''''||G_CLAUSE_MODEL_TOPNODE_OSR||l_org_id||':'||l_intent||''''||'.'||
218 ''''||G_CLAUSE_MODEL_VM_REF_NODE_OSR||l_org_id||':'||l_intent||''''||'.';
219
220 */
221
222
223 -- debug log
224 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
226 G_MODULE||l_api_name,
227 '200: l_clause_prefix :'||l_clause_prefix);
228 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
229 G_MODULE||l_api_name,
230 '200: l_system_var_prefix :'||l_system_var_prefix);
231 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
232 G_MODULE||l_api_name,
233 '200: l_question_prefix :'||l_question_prefix);
234 END IF;
235
236
237
238 OPEN csr_rule_conditions;
239 LOOP
240 FETCH csr_rule_conditions INTO l_rule_condition_id,
241 l_object_type,
242 l_object_code,
243 l_object_code_datatype,
244 l_operator,
245 l_object_value_type;
246 EXIT WHEN csr_rule_conditions%NOTFOUND;
247
248 -- debug log
249 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
251 G_MODULE||l_api_name,
252 '250: ******* Condition *********');
253 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
254 G_MODULE||l_api_name,
255 '250: l_rule_condition_id :'||l_rule_condition_id);
256 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
257 G_MODULE||l_api_name,
258 '250: l_object_type :'||l_object_type);
259 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
260 G_MODULE||l_api_name,
261 '250:l_object_code :'||l_object_code);
262 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
263 G_MODULE||l_api_name,
264 '250: l_object_code_datatype :'||l_object_code_datatype);
265 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
266 G_MODULE||l_api_name,
267 '250: l_operator :'||l_operator);
268 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
269 G_MODULE||l_api_name,
270 '250: l_object_value_type :'||l_object_value_type);
271 END IF;
272
273
274
275 -- IF x_stmt_rule IS NOT NULL THEN
276 IF DBMS_LOB.getlength(x_stmt_rule) > 0 THEN
277 -- x_stmt_rule := x_stmt_rule||' '||l_condition_expr_code;
278 -- 8i 9i backward compatibility issue with clob
279 l_temp_string := ' '||l_condition_expr_code;
280 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
281 --
282 END IF;
283
284 -- Boolen is treated as Character or LOV
285 -- LHS is Clause or Question / System Variable of type Character
286 IF l_object_code_datatype IN ('V','L','B') THEN
287 IF l_operator = 'ANYTRUE' THEN
288 -- x_stmt_rule:= x_stmt_rule||' '||l_operator||' '||'(';
289 -- 8i 9i backward compatibility issue with clob
290 l_temp_string := ' '||l_operator||' '||'(';
291 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
292 --
293 ELSE
294 -- l_operator is NOTTRUE
295 --x_stmt_rule:= x_stmt_rule||' '||'(';
296 -- 8i 9i backward compatibility issue with clob
297 l_temp_string := ' '||'(';
298 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
299 --
300 END IF; -- l_operator
301 /*
302 ELSIF l_object_code_datatype = 'B' THEN
303 -- Check N or NO
304 IF l_object_value_type = 'N' THEN
305 -- Boolean False
306 x_stmt_rule := x_stmt_rule||' '||'NOT'||' '||'(';
307 ELSE
308 -- Boolean True
309 x_stmt_rule := x_stmt_rule||' '||'(';
310 END IF; --
311 */
312 ELSE
313 -- Numeric
314 --x_stmt_rule:= x_stmt_rule||' '||'(';
315 -- 8i 9i backward compatibility issue with clob
316 l_temp_string := ' '||'(';
317 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
318 --
319 END IF;
320
321 -- initialize l_condition_stmt
322 -- l_condition_stmt := '';
323 DBMS_LOB.CREATETEMPORARY(l_condition_stmt, FALSE, DBMS_LOB.CALL);
324
325 -- For Each Condition Get the Values and create the statement
326 OPEN csr_rule_cond_vals(p_rule_condition_id => l_rule_condition_id);
327 LOOP
328 FETCH csr_rule_cond_vals INTO l_object_value_code;
329 EXIT WHEN csr_rule_cond_vals%NOTFOUND;
330
331 -- debug log
332 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
333 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
334 G_MODULE||l_api_name,
335 '350: ####### Condition Values #######');
336 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
337 G_MODULE||l_api_name,
338 '350: l_object_value_code :'||l_object_value_code);
339 END IF;
340
341
342 IF l_object_type = 'CLAUSE' THEN
343 -- RHS is also CLAUSE
344 IF l_operator = 'ANYTRUE' THEN
345 --IF l_condition_stmt IS NULL THEN
346 IF DBMS_LOB.getlength(l_condition_stmt) = 0 THEN
347 -- l_condition_stmt := l_clause_prefix||''''||l_object_value_code||'''';
348 l_temp_string := l_clause_prefix||''''||l_object_value_code||'''';
349 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
350 ELSE
351 -- l_condition_stmt := l_condition_stmt||','||l_clause_prefix||''''||l_object_value_code||'''';
352 l_temp_string := ','||l_clause_prefix||''''||l_object_value_code||'''';
353 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
354 END IF; -- l_condition_stmt
355 ELSE
356 -- l_operator = NOTTRUE
357 --IF l_condition_stmt IS NULL THEN
358 IF DBMS_LOB.getlength(l_condition_stmt) = 0 THEN
359 --l_condition_stmt := 'NOTTRUE('||l_clause_prefix||''''||l_object_value_code||''''||')';
360 l_temp_string := 'NOTTRUE('||l_clause_prefix||''''||l_object_value_code||''''||')';
361 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
362 ELSE
363 --l_condition_stmt := l_condition_stmt||' AND NOTTRUE('||l_clause_prefix||''''||l_object_value_code||''''||')';
364 l_temp_string := ' AND NOTTRUE('||l_clause_prefix||''''||l_object_value_code||''''||')';
365 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
366 END IF; -- l_condition_stmt
367 END IF; -- l_operator
368
369 ELSIF l_object_type = 'VARIABLE' THEN
370 IF l_object_code_datatype = 'N' THEN
371
372 -- RHS can be Numeric Variable / Question / Constant
373 IF l_object_value_type = 'QUESTION' THEN
374 -- RHS is Numeric Question
375 --l_condition_stmt := l_system_var_prefix||''''||l_object_code||''''||l_operator||
376 -- l_question_prefix||''''||l_object_value_code||'''';
377 l_temp_string := l_system_var_prefix||''''||l_object_code||''''||l_operator||
378 l_question_prefix||''''||l_object_value_code||'''';
379 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
380 ELSE
381 -- RHS is Numeric System Variable or Constant
382 --l_condition_stmt := l_system_var_prefix||''''||l_object_code||''''||l_operator||
383 -- l_system_var_prefix||''''||l_object_value_code||'''';
384 l_temp_string := l_system_var_prefix||''''||l_object_code||''''||l_operator||
385 l_system_var_prefix||''''||l_object_value_code||'''';
386 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
387
388 END IF; -- RHS for Numeric LHS Variable
389
390 ELSIF l_object_code_datatype IN ('V','L') THEN
391 -- RHS Can ONLY be Character System Variable Value
392 IF l_operator = 'ANYTRUE' THEN
393 --IF l_condition_stmt IS NULL THEN
394 IF DBMS_LOB.getlength(l_condition_stmt) = 0 THEN
395 --l_condition_stmt := l_system_var_prefix||''''||l_object_code||''''||'.'||
396 -- ''''||l_object_value_code||'''';
397 l_temp_string := l_system_var_prefix||''''||l_object_code||''''||'.'||
398 ''''||l_object_value_code||'''';
399 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
400 ELSE
401 --l_condition_stmt := l_condition_stmt||','||l_system_var_prefix||''''||l_object_code||
402 -- ''''||'.'||''''||l_object_value_code||'''';
403 l_temp_string := ','||l_system_var_prefix||''''||l_object_code||
404 ''''||'.'||''''||l_object_value_code||'''';
405 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
406 END IF; -- l_condition_stmt IS NULL
407 ELSE
408 -- l_operator = 'NOTTRUE'
409 --IF l_condition_stmt IS NULL THEN
410 IF DBMS_LOB.getlength(l_condition_stmt) = 0 THEN
411 --l_condition_stmt := 'NOTTRUE('||l_system_var_prefix||''''||l_object_code||
412 -- ''''||'.'||''''||l_object_value_code||''''||')';
413 l_temp_string := 'NOTTRUE('||l_system_var_prefix||''''||l_object_code||
414 ''''||'.'||''''||l_object_value_code||''''||')';
415 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
416
417 ELSE
418 --l_condition_stmt := l_condition_stmt||' AND NOTTRUE('||l_system_var_prefix||
419 -- ''''||l_object_code||''''||'.'||''''||l_object_value_code||''''||')';
420 l_temp_string := ' AND NOTTRUE('||l_system_var_prefix||
421 ''''||l_object_code||''''||'.'||''''||l_object_value_code||''''||')';
422 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
423
424 END IF; -- l_condition_stmt
425
426 END IF; -- l_operator
427
428
429 END IF; -- RHS Type
430
431 ELSIF l_object_type = 'QUESTION' THEN
432 IF l_object_code_datatype = 'N' THEN
433
434 -- RHS can be Numeric Variable / Question / Constant
435 IF l_object_value_type = 'QUESTION' THEN
436 -- RHS is Numeric Question
437 -- l_condition_stmt := l_question_prefix||''''||l_object_code||''''||l_operator||
438 -- l_question_prefix||''''||l_object_value_code||'''';
439 l_temp_string:= l_question_prefix||''''||l_object_code||''''||l_operator||
440 l_question_prefix||''''||l_object_value_code||'''';
441 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
442 ELSE
443 -- RHS is Numeric System Variable or Constant
444 -- l_condition_stmt := l_question_prefix||''''||l_object_code||''''||l_operator||
445 -- l_system_var_prefix||''''||l_object_value_code||'''';
446 l_temp_string := l_question_prefix||''''||l_object_code||''''||l_operator||
447 l_system_var_prefix||''''||l_object_value_code||'''';
448 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
449 END IF; -- RHS for Numeric LHS Variable
450
451 /*
452 ELSIF l_object_code_datatype = 'B' THEN
453 -- Boolean Question
454 l_condition_stmt := l_question_prefix||''''||l_object_code||'''';
455 */
456
457 ELSIF l_object_code_datatype IN ('V','L','B') THEN
458 IF l_operator = 'ANYTRUE' THEN
459 --IF l_condition_stmt IS NULL THEN
460 IF DBMS_LOB.getlength(l_condition_stmt) = 0 THEN
461
462 -- l_condition_stmt := l_question_prefix||''''||l_object_code||''''||'.'||
463 -- ''''||l_object_value_code||'''';
464 l_temp_string := l_question_prefix||''''||l_object_code||''''||'.'||
465 ''''||l_object_value_code||'''';
466 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
467 ELSE
468 -- l_condition_stmt := l_condition_stmt||','||l_question_prefix||''''||l_object_code||
469 -- ''''||'.'||''''||l_object_value_code||'''';
470 l_temp_string := ','||l_question_prefix||''''||l_object_code||
471 ''''||'.'||''''||l_object_value_code||'''';
472 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
473
474 END IF; -- l_condition_stmt IS NULL
475 ELSE
476 -- l_operator = 'NOTTRUE'
477 --IF l_condition_stmt IS NULL THEN
478 IF DBMS_LOB.getlength(l_condition_stmt) = 0 THEN
479 -- l_condition_stmt := 'NOTTRUE('||l_question_prefix||''''||l_object_code||''''||'.'||
480 -- ''''||l_object_value_code||''''||')';
481 l_temp_string := 'NOTTRUE('||l_question_prefix||''''||l_object_code||''''||'.'||
482 ''''||l_object_value_code||''''||')';
483 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
484 ELSE
485 -- l_condition_stmt := l_condition_stmt||' AND NOTTRUE('||l_question_prefix||
486 -- ''''||l_object_code||''''||'.'||''''||l_object_value_code||''''||')';
487 l_temp_string := ' AND NOTTRUE('||l_question_prefix||
488 ''''||l_object_code||''''||'.'||''''||l_object_value_code||''''||')';
489 DBMS_LOB.WRITEAPPEND(l_condition_stmt,length(l_temp_string) , l_temp_string);
490 END IF; -- l_condition_stmt
491
492 END IF; -- l_operator
493
494 END IF; -- RHS type
495
496 END IF;
497
498
499 END LOOP;
500 CLOSE csr_rule_cond_vals;
501
502 -- x_stmt_rule:= x_stmt_rule||l_condition_stmt||')';
503 -- 8i 9i backward compatibility issue with clob
504 --l_temp_string := l_condition_stmt||')';
505 --DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
506
507 DBMS_LOB.APPEND(x_stmt_rule,l_condition_stmt);
508 DBMS_LOB.FREETEMPORARY(l_condition_stmt);
509 l_temp_string := ')';
510 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
511
512
513 --
514
515
516 END LOOP;
517 CLOSE csr_rule_conditions;
518
519
520 -- debug log
521 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
523 G_MODULE||l_api_name,
524 '600: Building Outcome String');
525 END IF;
526
527 -- Build the Outcome String
528
529 -- initialize l_condition_stmt
530 --l_outcome_stmt := '';
531 DBMS_LOB.CREATETEMPORARY(l_outcome_stmt, FALSE, DBMS_LOB.CALL);
532
533 IF l_rule_type = 'TERM_DEVIATION' THEN
534 l_object_value_id := p_rule_id;
535 -- Outcome is Deviation
536 IF DBMS_LOB.getlength(l_outcome_stmt) = 0 THEN
537
538 l_temp_string := l_deviation_prefix||''''||l_object_value_id||'''';
539 DBMS_LOB.WRITEAPPEND(l_outcome_stmt,length(l_temp_string) , l_temp_string);
540 ELSE
541 l_temp_string := ','||l_deviation_prefix||''''||l_object_value_id||'''';
542 DBMS_LOB.WRITEAPPEND(l_outcome_stmt,length(l_temp_string) , l_temp_string);
543 END IF;
544 ELSE
545 -- l_rule_type is CLAUSE_SELECTION
546 OPEN csr_rule_outcomes;
547 LOOP
548 FETCH csr_rule_outcomes INTO l_object_type_outcome , l_object_value_id ;
549 EXIT WHEN csr_rule_outcomes%NOTFOUND;
550 IF l_object_type_outcome = 'CLAUSE' THEN
551
552 --IF l_outcome_stmt IS NULL THEN
553 IF DBMS_LOB.getlength(l_outcome_stmt) = 0 THEN
554
555 --l_outcome_stmt := l_clause_prefix||''''||l_object_value_id||'''';
556 l_temp_string := l_clause_prefix||''''||l_object_value_id||'''';
557 DBMS_LOB.WRITEAPPEND(l_outcome_stmt,length(l_temp_string) , l_temp_string);
558
559 ELSE
560 -- l_outcome_stmt := l_outcome_stmt||','||l_clause_prefix||''''||l_object_value_id||'''';
561 l_temp_string := ','||l_clause_prefix||''''||l_object_value_id||'''';
562 DBMS_LOB.WRITEAPPEND(l_outcome_stmt,length(l_temp_string) , l_temp_string);
563 END IF;
564
565 ELSE
566 -- Outcome is Question
567 --IF l_outcome_stmt IS NULL THEN
568 IF DBMS_LOB.getlength(l_outcome_stmt) = 0 THEN
569
570 --l_outcome_stmt := l_question_prefix||''''||l_object_value_id||'''';
571 l_temp_string := l_question_prefix||''''||l_object_value_id||'''';
572 DBMS_LOB.WRITEAPPEND(l_outcome_stmt,length(l_temp_string) , l_temp_string);
573 ELSE
574 --l_outcome_stmt := l_outcome_stmt||','||l_question_prefix||''''||l_object_value_id||'''';
575 l_temp_string := ','||l_question_prefix||''''||l_object_value_id||'''';
576 DBMS_LOB.WRITEAPPEND(l_outcome_stmt,length(l_temp_string) , l_temp_string);
577
578 END IF;
579
580 END IF;
581 END LOOP;
582 CLOSE csr_rule_outcomes;
583 END IF;
584
585 -- debug log
586 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
587 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
588 G_MODULE||l_api_name,
589 '750: After Building Outcome String ');
590 END IF;
591
592
593 --x_stmt_rule:= x_stmt_rule||' '||'IMPLIES ALLTRUE'||' '||'('||l_outcome_stmt||')';
594 -- 8i 9i backward compatibility issue with clob
595 l_temp_string := ' '||'IMPLIES ALLTRUE'||' '||'(';
596 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
597
598 DBMS_LOB.APPEND(x_stmt_rule,l_outcome_stmt);
599 DBMS_LOB.FREETEMPORARY(l_outcome_stmt);
600 l_temp_string := ')';
601 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
602
603
604 --
605
606
607 -- end debug log
608 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
610 G_MODULE||l_api_name,
611 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
612 END IF;
613
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
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 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
620 END IF;
621
622 fnd_file.put_line(FND_FILE.LOG,' ');
623 fnd_file.put_line(FND_FILE.LOG,'Error in build_statement_rule '||SQLERRM);
624 fnd_file.put_line(FND_FILE.LOG,' ');
625
626 x_return_status := G_RET_STS_ERROR ;
627 x_msg_data := SQLERRM;
628
629 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
630
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
633 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
634 G_MODULE||l_api_name,
635 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
636 END IF;
637
638 fnd_file.put_line(FND_FILE.LOG,' ');
639 fnd_file.put_line(FND_FILE.LOG,'Unexpected Error in build_statement_rule '||SQLERRM);
640 fnd_file.put_line(FND_FILE.LOG,' ');
641
642 x_return_status := G_RET_STS_UNEXP_ERROR ;
643 x_msg_data := SQLERRM;
644 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
645
646 WHEN OTHERS THEN
647 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
648 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
649 G_MODULE||l_api_name,
650 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
651 END IF;
652
653 fnd_file.put_line(FND_FILE.LOG,' ');
654 fnd_file.put_line(FND_FILE.LOG,'Other Error in build_statement_rule '||SQLERRM);
655 fnd_file.put_line(FND_FILE.LOG,' ');
656
657 x_return_status := G_RET_STS_UNEXP_ERROR ;
658 x_msg_data := SQLERRM;
659 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
660 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
661 END IF;
662 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
663
664
665 END build_statement_rule;
666
667
668 /*====================================================================+
669 Procedure Name : build_and_insert_rule
670 Description : This API calls build_statement_rule to build the rule statement.
671 It then inserts the statement into cz_imp_rules
672 Parameters:
673 p_rule_id - Rule Id
674 p_template_id - Template Id to which the rule is attached
675 p_run_id - Run Id for rules import
676 p_mode - Publish or Disable i.e P or D
677
678 +====================================================================*/
679
680
681 PROCEDURE build_and_insert_rule
682 (
683 p_rule_id IN VARCHAR2,
684 p_template_id IN NUMBER,
685 p_run_id IN NUMBER,
686 p_mode IN VARCHAR2,
687 x_return_status OUT NOCOPY VARCHAR2,
688 x_msg_data OUT NOCOPY VARCHAR2,
689 x_msg_count OUT NOCOPY NUMBER
690 ) IS
691
692 CURSOR csr_rule_dtls IS
693 SELECT r.rule_id,
694 r.org_id,
695 r.intent,
696 r.rule_name,
697 r.rule_description,
698 DECODE(p_mode,'P','0','D','1') deleted_flag,
699 r.rule_type
700 FROM okc_xprt_rule_hdrs_all r
701 WHERE r.rule_id = p_rule_id ;
702
703 CURSOR csr_template_dtls IS
704 SELECT template_name,
705 DECODE(parent_template_id, NULL, template_id, parent_template_id)
706 FROM OKC_TERMS_TEMPLATES_ALL
707 WHERE template_id = p_template_id ;
708
709 /*
710 CURSOR csr_template_model_id(p_org_id IN NUMBER,
711 p_intent IN VARCHAR2,
712 p_tmpl_id IN NUMBER) IS
713 */
714 CURSOR csr_template_model_id(p_orig_sys_ref IN VARCHAR2) IS
715 SELECT devl_project_id ,
716 orig_sys_ref
717 FROM cz_devl_projects
718 WHERE orig_sys_ref = p_orig_sys_ref
719 AND devl_project_id = persistent_project_id
720 AND deleted_flag = 0 ;
721
722 -- WHERE orig_sys_ref = G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_tmpl_id
723
724 CURSOR csr_installed_languages IS
725 SELECT L.LANGUAGE_CODE
726 FROM FND_LANGUAGES L
727 WHERE L.INSTALLED_FLAG IN ('I', 'B');
728
729 l_api_name CONSTANT VARCHAR2(30) := 'build_and_insert_rule';
730 l_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
731 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
732 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
733 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
734 l_rule_description okc_xprt_rule_hdrs_all.rule_description%TYPE;
735 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
736 l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
737
738 l_rule_text cz_imp_rules.rule_text%TYPE;
739 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
740
741
742 l_template_name OKC_TERMS_TEMPLATES_ALL.template_name%TYPE;
743 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE;
744
745 l_cz_imp_rules cz_imp_rules%ROWTYPE;
746 l_tmpl_orig_sys_ref cz_devl_projects.orig_sys_ref%TYPE;
747
748 l_model_id cz_devl_projects.devl_project_id%TYPE :=NULL;
749 l_model_osr cz_devl_projects.orig_sys_ref%TYPE := NULL;
750
751
752 BEGIN
753 -- start debug log
754 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
755 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
756 G_MODULE||l_api_name,
757 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
758 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
759 G_MODULE||l_api_name,
760 '100: Parameters ');
761 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
762 G_MODULE||l_api_name,
763 '100: p_rule_id : '||p_rule_id);
764 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
765 G_MODULE||l_api_name,
766 '100: p_template_id : '||p_template_id);
767 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
768 G_MODULE||l_api_name,
769 '100: p_run_id : '||p_run_id);
770 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
771 G_MODULE||l_api_name,
772 '100: p_mode : '||p_mode);
773 END IF;
774
775 x_return_status := G_RET_STS_SUCCESS;
776
777 -- Get the Rule Details
778 OPEN csr_rule_dtls;
779 FETCH csr_rule_dtls INTO l_rule_id,
780 l_org_id,
781 l_intent,
782 l_rule_name,
783 l_rule_description,
784 l_deleted_flag,
785 l_rule_type;
786 CLOSE csr_rule_dtls;
787
788 -- Get the Template Details
789 OPEN csr_template_dtls;
790 FETCH csr_template_dtls INTO l_template_name,
791 l_template_id;
792
793 IF csr_template_dtls%NOTFOUND THEN
794 -- debug Log
795 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
796 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
797 G_MODULE||l_api_name,
798 '110: Invalid Template Id: '||p_template_id);
799 END IF;
800 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TEMPLATE');
801 RAISE FND_API.G_EXC_ERROR;
802 END IF;
803
804 CLOSE csr_template_dtls;
805
806 -- build the template OSR
807 l_tmpl_orig_sys_ref := G_TEMPLATE_MODEL_OSR||l_org_id||':'||l_intent||':'||l_template_id;
808
809 -- Get Template Model Details
810 /*
811 OPEN csr_template_model_id(p_org_id => l_org_id,
812 p_intent => l_intent,
813 p_tmpl_id => l_template_id);
814 */
815
816 OPEN csr_template_model_id(p_orig_sys_ref => l_tmpl_orig_sys_ref);
817
818 FETCH csr_template_model_id INTO l_model_id, l_model_osr;
819
820 IF csr_template_model_id%NOTFOUND THEN
821 -- debug Log
822 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
823 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
824 G_MODULE||l_api_name,
825 '110: Template Model Does Not Exists : '||l_template_name);
826 END IF;
827 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TMPL_MODEL');
828 FND_MESSAGE.set_token('TEMPLATE_NAME',l_template_name);
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831
832 CLOSE csr_template_model_id;
833
834 -- debug Log
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 '120: Template Name : '||l_template_name);
839 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
840 G_MODULE||l_api_name,
841 '120: Derived Template Id : '||l_template_id);
842 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
843 G_MODULE||l_api_name,
844 '120: Template Model Id : '||l_model_id);
845 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
846 G_MODULE||l_api_name,
847 '120: Template Model OSR : '||l_model_osr);
848 END IF;
849
850 -- For Rules to be deleted, rule text is NOT required
851 IF l_deleted_flag = '1' THEN
852 -- l_rule_text := NULL;
853 DBMS_LOB.CREATETEMPORARY(l_rule_text, FALSE, DBMS_LOB.CALL);
854 ELSE
855
856
857 -- Generate the Rule Text for this Rule Id
858 build_statement_rule
859 (
860 p_rule_id => l_rule_id,
861 p_template_id => l_template_id,
862 x_stmt_rule => l_rule_text,
863 x_return_status => x_return_status,
864 x_msg_data => x_msg_data,
865 x_msg_count => x_msg_count
866 ) ;
867
868 --- If any errors happen abort API
869 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
872 RAISE FND_API.G_EXC_ERROR;
873 END IF;
874
875 END IF; -- rule text generation
876
877 -- Insert Rule Name into cz_imp_localized_texts
878
879 OPEN csr_installed_languages;
880 LOOP
881 FETCH csr_installed_languages INTO l_language;
882 EXIT WHEN csr_installed_languages%NOTFOUND;
883
884 -- Insert into cz_imp_localized_text
885
886 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
887 (
888 LAST_UPDATE_LOGIN,
889 LOCALE_ID,
890 LOCALIZED_STR,
891 INTL_TEXT_ID,
892 CREATION_DATE,
893 LAST_UPDATE_DATE,
894 DELETED_FLAG,
895 EFF_FROM,
896 EFF_TO,
897 CREATED_BY,
898 LAST_UPDATED_BY,
899 SECURITY_MASK,
900 EFF_MASK,
901 CHECKOUT_USER,
902 LANGUAGE,
903 ORIG_SYS_REF,
904 SOURCE_LANG,
905 RUN_ID,
906 REC_STATUS,
907 DISPOSITION,
908 MODEL_ID,
909 FSK_DEVLPROJECT_1_1,
910 MESSAGE,
911 SEEDED_FLAG
912 )
913 VALUES
914 (
915 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
916 NULL, -- LOCALE_ID
917 l_rule_name, --LOCALIZED_STR
918 NULL, -- INTL_TEXT_ID
919 SYSDATE, -- CREATION_DATE
920 SYSDATE, -- LAST_UPDATE_DATE
921 '0', -- DELETED_FLAG
922 NULL, -- EFF_FROM
923 NULL, -- EFF_TO
924 FND_GLOBAL.USER_ID, -- CREATED_BY
925 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
926 NULL, -- SECURITY_MASK
927 NULL, -- EFF_MASK
928 NULL, -- CHECKOUT_USER
929 l_language, --LANGUAGE
930 G_RULE_OSR||l_org_id||':'||l_intent||':'||l_template_id||':'||l_rule_type||':'||l_rule_id, --ORIG_SYS_REF
931 USERENV('LANG'), --SOURCE_LANG
932 p_run_id, -- RUN_ID
933 NULL, -- REC_STATUS
934 NULL, -- DISPOSITION
935 l_model_id, -- MODEL_ID
936 l_model_osr, --FSK_DEVLPROJECT_1_1
937 NULL, -- MESSAGE
938 NULL -- SEEDED_FLAG
939 );
940
941 END LOOP; -- for all installed languages
942 CLOSE csr_installed_languages;
943
944 -- Populate the cz_imp_rules record
945
946 l_cz_imp_rules.RULE_ID := NULL;
947 l_cz_imp_rules.SUB_CONS_ID := NULL;
948 l_cz_imp_rules.REASON_ID := NULL;
949 l_cz_imp_rules.AMOUNT_ID := NULL;
950 l_cz_imp_rules.GRID_ID := NULL;
951 l_cz_imp_rules.RULE_FOLDER_ID := NULL;
952 l_cz_imp_rules.DEVL_PROJECT_ID := l_model_id;
953 l_cz_imp_rules.INVALID_FLAG := NULL;
954 l_cz_imp_rules.DESC_TEXT := l_rule_description;
955 l_cz_imp_rules.NAME := l_rule_name; -- check
956 l_cz_imp_rules.ANTECEDENT_ID := NULL;
957 l_cz_imp_rules.CONSEQUENT_ID := NULL;
958 l_cz_imp_rules.RULE_TYPE := 200; -- Expression Rule
959 l_cz_imp_rules.EXPR_RULE_TYPE := NULL;
960 l_cz_imp_rules.COMPONENT_ID := NULL;
961 l_cz_imp_rules.REASON_TYPE := 0; -- Value in reason_id corresponds to Name of Rule
962 l_cz_imp_rules.DISABLED_FLAG := NULL;
963 l_cz_imp_rules.ORIG_SYS_REF := G_RULE_OSR||l_org_id||':'||l_intent||':'||l_template_id||':'||l_rule_type||':'||l_rule_id; --ORIG_SYS_REF
964 l_cz_imp_rules.CREATION_DATE := SYSDATE;
965 l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
966 l_cz_imp_rules.DELETED_FLAG := l_deleted_flag; -- check
967 l_cz_imp_rules.EFF_FROM := NULL;
968 l_cz_imp_rules.EFF_TO := NULL;
969 l_cz_imp_rules.CREATED_BY := FND_GLOBAL.USER_ID;
970 l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
971 l_cz_imp_rules.SECURITY_MASK := NULL;
972 l_cz_imp_rules.EFF_MASK := NULL;
973 l_cz_imp_rules.CHECKOUT_USER := NULL;
974 l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
975 l_cz_imp_rules.EFFECTIVE_USAGE_MASK := NULL;
976 l_cz_imp_rules.SEQ_NBR := NULL;
977 l_cz_imp_rules.EFFECTIVE_FROM := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
978 l_cz_imp_rules.EFFECTIVE_UNTIL := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
979 l_cz_imp_rules.PERSISTENT_RULE_ID := NULL;
980 l_cz_imp_rules.EFFECTIVITY_SET_ID := NULL;
981 l_cz_imp_rules.RULE_FOLDER_TYPE := NULL;
982 l_cz_imp_rules.UNSATISFIED_MSG_ID := NULL;
983 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE := NULL;
984 l_cz_imp_rules.SIGNATURE_ID := NULL;
985 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG := NULL;
986 l_cz_imp_rules.PRESENTATION_FLAG := NULL;
987 l_cz_imp_rules.TEMPLATE_TOKEN := NULL;
988 l_cz_imp_rules.RULE_TEXT := l_rule_text;
989 l_cz_imp_rules.NOTES := NULL;
990 l_cz_imp_rules.CLASS_NAME := NULL;
991 l_cz_imp_rules.INSTANTIATION_SCOPE := NULL;
992 l_cz_imp_rules.MODEL_REF_EXPL_ID := NULL;
993 l_cz_imp_rules.MUTABLE_FLAG := NULL;
994 l_cz_imp_rules.SEEDED_FLAG := NULL;
995 l_cz_imp_rules.UI_DEF_ID := NULL;
996 l_cz_imp_rules.UI_PAGE_ID := NULL;
997 l_cz_imp_rules.UI_PAGE_ELEMENT_ID := NULL;
998 l_cz_imp_rules.MESSAGE := NULL;
999 l_cz_imp_rules.RUN_ID := p_run_id;
1000 l_cz_imp_rules.DISPOSITION := NULL;
1001 l_cz_imp_rules.REC_STATUS := NULL;
1002 l_cz_imp_rules.FSK_DEVL_PROJECT := l_model_osr;
1003 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1 := G_RULE_OSR||l_org_id||':'||l_intent||':'||l_template_id||':'||l_rule_type||':'||l_rule_id;
1004 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2 := NULL;
1005 l_cz_imp_rules.IMPORT_PROG_VERSION := NULL;
1006 l_cz_imp_rules.FSK_COMPONENT_ID := NULL;
1007 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID := NULL;
1008
1009 -- Insert into cz_imp_rules
1010
1011 INSERT INTO cz_imp_rules
1012 (
1013 RULE_ID,
1014 SUB_CONS_ID,
1015 REASON_ID,
1016 AMOUNT_ID,
1017 GRID_ID,
1018 RULE_FOLDER_ID,
1019 DEVL_PROJECT_ID,
1020 INVALID_FLAG,
1021 DESC_TEXT,
1022 NAME,
1023 ANTECEDENT_ID,
1024 CONSEQUENT_ID,
1025 RULE_TYPE,
1026 EXPR_RULE_TYPE,
1027 COMPONENT_ID,
1028 REASON_TYPE,
1029 DISABLED_FLAG,
1030 ORIG_SYS_REF,
1031 CREATION_DATE,
1032 LAST_UPDATE_DATE,
1033 DELETED_FLAG,
1034 EFF_FROM,
1035 EFF_TO,
1036 CREATED_BY,
1037 LAST_UPDATED_BY,
1038 SECURITY_MASK,
1039 EFF_MASK,
1040 CHECKOUT_USER,
1041 LAST_UPDATE_LOGIN,
1042 EFFECTIVE_USAGE_MASK,
1043 SEQ_NBR,
1044 EFFECTIVE_FROM,
1045 EFFECTIVE_UNTIL,
1046 PERSISTENT_RULE_ID,
1047 EFFECTIVITY_SET_ID,
1048 RULE_FOLDER_TYPE,
1049 UNSATISFIED_MSG_ID,
1050 UNSATISFIED_MSG_SOURCE,
1051 SIGNATURE_ID,
1052 TEMPLATE_PRIMITIVE_FLAG,
1053 PRESENTATION_FLAG,
1054 TEMPLATE_TOKEN,
1055 RULE_TEXT,
1056 NOTES,
1057 CLASS_NAME,
1058 INSTANTIATION_SCOPE,
1059 MODEL_REF_EXPL_ID,
1060 MUTABLE_FLAG,
1061 SEEDED_FLAG,
1062 UI_DEF_ID,
1063 UI_PAGE_ID,
1064 UI_PAGE_ELEMENT_ID,
1065 MESSAGE,
1066 RUN_ID,
1067 DISPOSITION,
1068 REC_STATUS,
1069 FSK_DEVL_PROJECT,
1070 FSK_LOCALIZED_TEXT_1,
1071 FSK_LOCALIZED_TEXT_2,
1072 IMPORT_PROG_VERSION,
1073 FSK_COMPONENT_ID,
1074 FSK_MODEL_REF_EXPL_ID
1075 )
1076 VALUES
1077 (
1078 l_cz_imp_rules.RULE_ID,
1079 l_cz_imp_rules.SUB_CONS_ID,
1080 l_cz_imp_rules.REASON_ID,
1081 l_cz_imp_rules.AMOUNT_ID,
1082 l_cz_imp_rules.GRID_ID,
1083 l_cz_imp_rules.RULE_FOLDER_ID,
1084 l_cz_imp_rules.DEVL_PROJECT_ID,
1085 l_cz_imp_rules.INVALID_FLAG,
1086 l_cz_imp_rules.DESC_TEXT,
1087 l_cz_imp_rules.NAME,
1088 l_cz_imp_rules.ANTECEDENT_ID,
1089 l_cz_imp_rules.CONSEQUENT_ID,
1090 l_cz_imp_rules.RULE_TYPE,
1091 l_cz_imp_rules.EXPR_RULE_TYPE,
1092 l_cz_imp_rules.COMPONENT_ID,
1093 l_cz_imp_rules.REASON_TYPE,
1094 l_cz_imp_rules.DISABLED_FLAG,
1095 l_cz_imp_rules.ORIG_SYS_REF,
1096 l_cz_imp_rules.CREATION_DATE,
1097 l_cz_imp_rules.LAST_UPDATE_DATE,
1098 l_cz_imp_rules.DELETED_FLAG,
1099 l_cz_imp_rules.EFF_FROM,
1100 l_cz_imp_rules.EFF_TO,
1101 l_cz_imp_rules.CREATED_BY,
1102 l_cz_imp_rules.LAST_UPDATED_BY,
1103 l_cz_imp_rules.SECURITY_MASK,
1104 l_cz_imp_rules.EFF_MASK,
1105 l_cz_imp_rules.CHECKOUT_USER,
1106 l_cz_imp_rules.LAST_UPDATE_LOGIN,
1107 l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
1108 l_cz_imp_rules.SEQ_NBR,
1109 l_cz_imp_rules.EFFECTIVE_FROM,
1110 l_cz_imp_rules.EFFECTIVE_UNTIL,
1111 l_cz_imp_rules.PERSISTENT_RULE_ID,
1112 l_cz_imp_rules.EFFECTIVITY_SET_ID,
1113 l_cz_imp_rules.RULE_FOLDER_TYPE,
1114 l_cz_imp_rules.UNSATISFIED_MSG_ID,
1115 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
1116 l_cz_imp_rules.SIGNATURE_ID,
1117 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
1118 l_cz_imp_rules.PRESENTATION_FLAG,
1119 l_cz_imp_rules.TEMPLATE_TOKEN,
1120 l_cz_imp_rules.RULE_TEXT,
1121 l_cz_imp_rules.NOTES,
1122 l_cz_imp_rules.CLASS_NAME,
1123 l_cz_imp_rules.INSTANTIATION_SCOPE,
1124 l_cz_imp_rules.MODEL_REF_EXPL_ID,
1125 l_cz_imp_rules.MUTABLE_FLAG,
1126 l_cz_imp_rules.SEEDED_FLAG,
1127 l_cz_imp_rules.UI_DEF_ID,
1128 l_cz_imp_rules.UI_PAGE_ID,
1129 l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
1130 l_cz_imp_rules.MESSAGE,
1131 l_cz_imp_rules.RUN_ID,
1132 l_cz_imp_rules.DISPOSITION,
1133 l_cz_imp_rules.REC_STATUS,
1134 l_cz_imp_rules.FSK_DEVL_PROJECT,
1135 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
1136 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
1137 l_cz_imp_rules.IMPORT_PROG_VERSION,
1138 l_cz_imp_rules.FSK_COMPONENT_ID,
1139 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
1140 );
1141
1142
1143
1144
1145 -- Free the CLOB Memory now
1146 DBMS_LOB.FREETEMPORARY(l_rule_text);
1147
1148 -- Standard call to get message count and if count is 1, get message info.
1149 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1150
1151
1152 -- end debug log
1153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1155 G_MODULE||l_api_name,
1156 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN FND_API.G_EXC_ERROR THEN
1161 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1162 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1163 G_MODULE||l_api_name,
1164 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1165 END IF;
1166
1167 fnd_file.put_line(FND_FILE.LOG,' ');
1168 fnd_file.put_line(FND_FILE.LOG,'Other Error in build_statement_rule '||SQLERRM);
1169 fnd_file.put_line(FND_FILE.LOG,' ');
1170
1171 x_return_status := G_RET_STS_ERROR ;
1172 x_msg_data := SQLERRM;
1173 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1174
1175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1176 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1177 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1178 G_MODULE||l_api_name,
1179 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1180 END IF;
1181
1182 fnd_file.put_line(FND_FILE.LOG,' ');
1183 fnd_file.put_line(FND_FILE.LOG,'Unexpected Error in build_and_insert_rule '||SQLERRM);
1184 fnd_file.put_line(FND_FILE.LOG,' ');
1185
1186 x_return_status := G_RET_STS_UNEXP_ERROR ;
1187 x_msg_data := SQLERRM;
1188 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1189
1190 WHEN OTHERS THEN
1191 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1193 G_MODULE||l_api_name,
1194 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1195 END IF;
1196
1197 fnd_file.put_line(FND_FILE.LOG,' ');
1198 fnd_file.put_line(FND_FILE.LOG,'Other Error in build_and_insert_rule '||SQLERRM);
1199 fnd_file.put_line(FND_FILE.LOG,' ');
1200
1201 x_return_status := G_RET_STS_UNEXP_ERROR ;
1202 x_msg_data := SQLERRM;
1203 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1204 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1205 END IF;
1206 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1207
1208 END build_and_insert_rule;
1209
1210
1211 /*====================================================================+
1212 Procedure Name : import_rules_publish
1213 Description : This API is called from Publish Rules concurrent program
1214
1215 Parameters:
1216 +====================================================================*/
1217
1218 PROCEDURE import_rules_publish
1219 (
1220 x_run_id OUT NOCOPY NUMBER,
1221 x_return_status OUT NOCOPY VARCHAR2,
1222 x_msg_data OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER
1224 ) IS
1225
1226 -- Rules to be Published
1227 CURSOR csr_rules IS
1228 SELECT rule_id,
1229 published_flag
1230 FROM okc_xprt_rule_hdrs_all
1231 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1232
1233 -- Templates Attached to each Rule
1234 CURSOR csr_templates(p_rule_id IN NUMBER) IS
1235 -- Local Rules
1236 SELECT r.template_id
1237 FROM OKC_TERMS_TEMPLATES_ALL t,
1238 okc_xprt_template_rules r
1239 WHERE r.template_id = t.template_id
1240 AND t.status_code IN ('APPROVED','ON_HOLD')
1241 AND t.contract_expert_enabled = 'Y'
1242 AND NVL(r.deleted_flag,'N') = 'N'
1243 AND r.rule_id = p_rule_id
1244 UNION ALL
1245 -- Org Wide Rules
1246 SELECT t.template_id
1247 FROM OKC_TERMS_TEMPLATES_ALL t,
1248 okc_xprt_rule_hdrs_all r
1249 WHERE t.org_id = r.org_id
1250 AND t.intent = r.intent
1251 AND t.contract_expert_enabled = 'Y'
1252 AND t.status_code IN ('APPROVED','ON_HOLD')
1253 AND NVL(r.org_wide_flag,'N') = 'Y'
1254 AND r.rule_id = p_rule_id ;
1255
1256 -- If the Rule was already published to CZ, get the list of templates that were
1257 -- associated to the Rule in last publication
1258 -- From the above list MINUS the templates currently associated to Rule
1259 -- This would give be the list of templates from which rule association is to be deleted
1260
1261 CURSOR csr_templates_to_delete(p_rule_id IN NUMBER) IS
1262 -- Templates already pushed to CZ in previous publication
1263 -- Changed for R12
1264 SELECT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
1265 (INSTR(orig_sys_ref,':',-1,2) - (INSTR(orig_sys_ref,':',-1,3)+1))
1266 )
1267 FROM cz_rules
1268 WHERE SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id)
1269 AND rule_type = 200 -- Added for Bug 5005681
1270 AND rule_id = persistent_rule_id
1271 AND deleted_flag = '0'
1272 MINUS
1273 -- list of templates to which the rule is currently attached
1274 (
1275 -- Local Rules
1276 SELECT to_char(r.template_id)
1277 FROM OKC_TERMS_TEMPLATES_ALL t,
1278 okc_xprt_template_rules r
1279 WHERE r.template_id = t.template_id
1280 AND t.status_code IN ('APPROVED','ON_HOLD')
1281 AND t.contract_expert_enabled = 'Y'
1282 AND NVL(r.deleted_flag,'N') = 'N'
1283 AND r.rule_id = p_rule_id
1284 UNION ALL
1285 -- Org Wide Rules
1286 SELECT to_char(t.template_id)
1287 FROM OKC_TERMS_TEMPLATES_ALL t,
1288 okc_xprt_rule_hdrs_all r
1289 --,okc_xprt_template_rules tr -- Added for Bug 5005681
1290 WHERE t.org_id = r.org_id
1291 --AND tr.template_id = t.template_id -- Added for Bug 5005681
1292 --AND tr.rule_id = r.rule_id -- Added for Bug 5005681
1293 AND t.intent = r.intent
1294 AND t.contract_expert_enabled = 'Y'
1295 AND t.status_code IN ('APPROVED','ON_HOLD')
1296 AND NVL(r.org_wide_flag,'N') = 'Y'
1297 AND r.rule_id = p_rule_id
1298 );
1299
1300 -- Generate Run Id for Rule Import
1301 CURSOR csr_cz_run_id IS
1302 SELECT cz_xfr_run_infos_s.NEXTVAL
1303 FROM dual;
1304
1305
1306 l_api_name CONSTANT VARCHAR2(30) := 'import_rules_publish';
1307 l_rule_id okc_xprt_template_rules.rule_id%TYPE;
1308 l_template_id okc_xprt_template_rules.template_id%TYPE;
1309 l_published_flag okc_xprt_rule_hdrs_all.published_flag%TYPE;
1310
1311
1312 BEGIN
1313 -- start debug log
1314 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1315 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1316 G_MODULE||l_api_name,
1317 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1318 END IF;
1319
1320 x_return_status := G_RET_STS_SUCCESS;
1321
1322 -- Generate the Run Id
1323 OPEN csr_cz_run_id;
1324 FETCH csr_cz_run_id INTO x_run_id;
1325 CLOSE csr_cz_run_id;
1326
1327 -- debug log
1328 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1329 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1330 G_MODULE||l_api_name,
1331 '110: Rule Import x_run_id : '|| x_run_id);
1332 END IF;
1333
1334
1335 OPEN csr_rules;
1336 LOOP
1337 FETCH csr_rules INTO l_rule_id,l_published_flag;
1338 EXIT WHEN csr_rules%NOTFOUND;
1339 -- For each Rule get all templates currently attached
1340 OPEN csr_templates(p_rule_id => l_rule_id);
1341 LOOP
1342 FETCH csr_templates INTO l_template_id;
1343 EXIT WHEN csr_templates%NOTFOUND;
1344
1345 build_and_insert_rule
1346 (
1347 p_rule_id => l_rule_id,
1348 p_template_id => l_template_id,
1349 p_run_id => x_run_id,
1350 p_mode => 'P', -- Publish
1351 x_return_status => x_return_status,
1352 x_msg_data => x_msg_data,
1353 x_msg_count => x_msg_count
1354 );
1355
1356 --- If any errors happen abort API
1357 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1359 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1360 RAISE FND_API.G_EXC_ERROR;
1361 END IF;
1362
1363
1364 END LOOP; -- Template Csr
1365 CLOSE csr_templates; -- current templates
1366
1367 -- If the Rule was already published, get the difference between current templates
1368 -- and templates that were attached to Rule in the previous CZ publication
1369 IF l_published_flag = 'Y' THEN
1370 OPEN csr_templates_to_delete(p_rule_id => l_rule_id);
1371 LOOP
1372 FETCH csr_templates_to_delete INTO l_template_id;
1373 EXIT WHEN csr_templates_to_delete%NOTFOUND;
1374
1375 build_and_insert_rule
1376 (
1377 p_rule_id => l_rule_id,
1378 p_template_id => l_template_id,
1379 p_run_id => x_run_id,
1380 p_mode => 'D', -- to be deleted
1381 x_return_status => x_return_status,
1382 x_msg_data => x_msg_data,
1383 x_msg_count => x_msg_count
1384 );
1385
1386 --- If any errors happen abort API
1387 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1389 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1390 RAISE FND_API.G_EXC_ERROR;
1391 END IF;
1392
1393
1394 END LOOP; -- csr_templates_to_delete
1395 CLOSE csr_templates_to_delete; -- old templates
1396
1397 END IF; -- l_published_flag = 'Y'
1398
1399
1400
1401 END LOOP; -- Rules Csr
1402 CLOSE csr_rules;
1403
1404
1405 -- Standard call to get message count and if count is 1, get message info.
1406 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1407
1408
1409 -- end debug log
1410 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1411 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1412 G_MODULE||l_api_name,
1413 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1414 END IF;
1415
1416 EXCEPTION
1417 WHEN FND_API.G_EXC_ERROR THEN
1418 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1419 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1420 G_MODULE||l_api_name,
1421 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1422 END IF;
1423
1424 x_return_status := G_RET_STS_ERROR ;
1425 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1426
1427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1428 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1429 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1430 G_MODULE||l_api_name,
1431 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1432 END IF;
1433
1434 x_return_status := G_RET_STS_UNEXP_ERROR ;
1435 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1436
1437 WHEN OTHERS THEN
1438 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1440 G_MODULE||l_api_name,
1441 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1442 END IF;
1443
1444 x_return_status := G_RET_STS_UNEXP_ERROR ;
1445 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1446 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1447 END IF;
1448 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1449
1450 END import_rules_publish;
1451
1452 /*====================================================================+
1453 Procedure Name : import_rules_disable
1454 Description : This API is called from Disable Rules concurrent program
1455
1456 Parameters:
1457 +====================================================================*/
1458
1459 PROCEDURE import_rules_disable
1460 (
1461 x_run_id OUT NOCOPY NUMBER,
1462 x_return_status OUT NOCOPY VARCHAR2,
1463 x_msg_data OUT NOCOPY VARCHAR2,
1464 x_msg_count OUT NOCOPY NUMBER
1465 ) IS
1466
1467 -- Rules to be Disabled
1468 CURSOR csr_rules IS
1469 SELECT rule_id
1470 FROM okc_xprt_rule_hdrs_all
1471 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1472
1473 /*
1474 In case of rules to be disabled we look at the template assciation of the rule is CZ
1475 and delete the same
1476 */
1477 CURSOR csr_templates_to_delete(p_rule_id IN NUMBER) IS
1478 -- Templates already pushed to CZ in previous publication
1479 -- Changed for R12
1480 -- Updated the substr for bug 4676800
1481 SELECT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
1482 (INSTR(orig_sys_ref,':',-1,2) - (INSTR(orig_sys_ref,':',-1,3)+1))
1483 )
1484 FROM cz_rules
1485 WHERE SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id)
1486 AND rule_id = persistent_rule_id
1487 AND deleted_flag = '0'
1488 AND rule_type = 200; --Added for perf Bug#5032335
1489
1490 -- Generate Run Id for Rule Import
1491 CURSOR csr_cz_run_id IS
1492 SELECT cz_xfr_run_infos_s.NEXTVAL
1493 FROM dual;
1494
1495
1496 l_api_name CONSTANT VARCHAR2(30) := 'import_rules_disable';
1497 l_rule_id okc_xprt_template_rules.rule_id%TYPE;
1498 l_template_id okc_xprt_template_rules.template_id%TYPE;
1499
1500
1501 BEGIN
1502 -- start debug log
1503 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1504 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1505 G_MODULE||l_api_name,
1506 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1507 END IF;
1508
1509 x_return_status := G_RET_STS_SUCCESS;
1510
1511 -- Generate the Run Id
1512 OPEN csr_cz_run_id;
1513 FETCH csr_cz_run_id INTO x_run_id;
1514 CLOSE csr_cz_run_id;
1515
1516 -- debug log
1517 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1518 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1519 G_MODULE||l_api_name,
1520 '110: Rule Import x_run_id : '|| x_run_id);
1521 END IF;
1522
1523
1524 OPEN csr_rules;
1525 LOOP
1526 FETCH csr_rules INTO l_rule_id;
1527 EXIT WHEN csr_rules%NOTFOUND;
1528
1529 OPEN csr_templates_to_delete(p_rule_id => l_rule_id);
1530 LOOP
1531 FETCH csr_templates_to_delete INTO l_template_id;
1532 EXIT WHEN csr_templates_to_delete%NOTFOUND;
1533
1534 build_and_insert_rule
1535 (
1536 p_rule_id => l_rule_id,
1537 p_template_id => l_template_id,
1538 p_run_id => x_run_id,
1539 p_mode => 'D', -- to be deleted
1540 x_return_status => x_return_status,
1541 x_msg_data => x_msg_data,
1542 x_msg_count => x_msg_count
1543 );
1544
1545 --- If any errors happen abort API
1546 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1548 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1549 RAISE FND_API.G_EXC_ERROR;
1550 END IF;
1551
1552
1553 END LOOP; -- csr_templates_to_delete
1554 CLOSE csr_templates_to_delete; -- old templates
1555
1556
1557
1558
1559 END LOOP; -- Rules Csr
1560 CLOSE csr_rules;
1561
1562 -- Standard call to get message count and if count is 1, get message info.
1563 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1564
1565
1566 -- end debug log
1567 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1568 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1569 G_MODULE||l_api_name,
1570 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1571 END IF;
1572
1573 EXCEPTION
1574 WHEN FND_API.G_EXC_ERROR THEN
1575 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1576 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1577 G_MODULE||l_api_name,
1578 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1579 END IF;
1580
1581 x_return_status := G_RET_STS_ERROR ;
1582 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1583
1584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1585 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1586 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1587 G_MODULE||l_api_name,
1588 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1589 END IF;
1590
1591 x_return_status := G_RET_STS_UNEXP_ERROR ;
1592 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1593
1594 WHEN OTHERS THEN
1595 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1596 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1597 G_MODULE||l_api_name,
1598 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1599 END IF;
1600
1601 x_return_status := G_RET_STS_UNEXP_ERROR ;
1602 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1603 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1604 END IF;
1605 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1606
1607 END import_rules_disable;
1608
1609
1610 /*====================================================================+
1611 Procedure Name : import_rule_temp_approval
1612 Description : This API is called from template approval concurrent program
1613
1614 Parameters:
1615 p_template_id - Template Id to be imported
1616 +====================================================================*/
1617
1618 PROCEDURE import_rule_temp_approval
1619 (
1620 p_template_id IN NUMBER,
1621 x_run_id OUT NOCOPY NUMBER,
1622 x_return_status OUT NOCOPY VARCHAR2,
1623 x_msg_data OUT NOCOPY VARCHAR2,
1624 x_msg_count OUT NOCOPY NUMBER
1625 ) IS
1626
1627 CURSOR csr_template_rules IS
1628 -- Local Active Rules for template
1629 SELECT r.rule_id
1630 FROM okc_xprt_template_rules r,
1631 okc_xprt_rule_hdrs_all h
1632 WHERE r.rule_id = h.rule_id
1633 AND h.status_code = 'ACTIVE'
1634 AND NVL(r.deleted_flag,'N') = 'N'
1635 AND r.template_id = p_template_id
1636 UNION ALL
1637 -- Global Active Rules for the template
1638 SELECT r.rule_id
1639 FROM OKC_TERMS_TEMPLATES_ALL t,
1640 okc_xprt_rule_hdrs_all r
1641 WHERE t.org_id = r.org_id
1642 AND t.intent = r.intent
1643 AND NVL(r.org_wide_flag,'N') = 'Y'
1644 AND r.status_code = 'ACTIVE'
1645 AND t.template_id = p_template_id ;
1646
1647 CURSOR csr_parent_tmpl_rules(p_parent_tmpl_id IN NUMBER) IS
1648 -- Local Active Rules on Parent Template to be deleted
1649 -- Delete rules on parent template and not on the revision template
1650 SELECT r.rule_id
1651 FROM okc_xprt_template_rules r,
1652 okc_xprt_rule_hdrs_all h
1653 WHERE r.rule_id = h.rule_id
1654 AND h.status_code = 'ACTIVE'
1655 AND NVL(r.deleted_flag,'N') = 'N'
1656 AND r.template_id = p_parent_tmpl_id
1657 MINUS
1658 -- current local rules on revision template
1659 SELECT r.rule_id
1660 FROM okc_xprt_template_rules r,
1661 okc_xprt_rule_hdrs_all h
1662 WHERE r.rule_id = h.rule_id
1663 AND h.status_code = 'ACTIVE'
1664 AND NVL(r.deleted_flag,'N') = 'N'
1665 AND r.template_id = p_template_id ;
1666
1667 CURSOR csr_template_dtls IS
1668 SELECT parent_template_id
1669 FROM OKC_TERMS_TEMPLATES_ALL
1670 WHERE template_id = p_template_id ;
1671
1672
1673 -- Generate Run Id for Rule Import
1674 CURSOR csr_cz_run_id IS
1675 SELECT cz_xfr_run_infos_s.NEXTVAL
1676 FROM dual;
1677
1678
1679 l_api_name CONSTANT VARCHAR2(30) := 'import_rule_temp_approval';
1680 l_rule_id okc_xprt_template_rules.rule_id%TYPE;
1681 l_parent_template_id OKC_TERMS_TEMPLATES_ALL.parent_template_id%TYPE := NULL;
1682 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE := NULL;
1683
1684
1685
1686 BEGIN
1687 -- start debug log
1688 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1689 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1690 G_MODULE||l_api_name,
1691 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1692 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1693 G_MODULE||l_api_name,
1694 '100: Parameters ');
1695 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1696 G_MODULE||l_api_name,
1697 '100: p_template_id : '||p_template_id);
1698 END IF;
1699
1700 x_return_status := G_RET_STS_SUCCESS;
1701
1702 -- Generate the Run Id
1703 OPEN csr_cz_run_id;
1704 FETCH csr_cz_run_id INTO x_run_id;
1705 CLOSE csr_cz_run_id;
1706
1707 -- Get Template Details
1708 -- In case of revision template, parent_template_id IS NOT NULL
1709
1710 OPEN csr_template_dtls;
1711 FETCH csr_template_dtls INTO l_parent_template_id;
1712
1713 IF csr_template_dtls%NOTFOUND THEN
1714 -- Log Error
1715 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1716 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1717 G_MODULE||l_api_name,
1718 '110: Invalid Template Id: '||p_template_id);
1719 END IF;
1720 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TEMPLATE');
1721 RAISE FND_API.G_EXC_ERROR;
1722 END IF;
1723
1724 CLOSE csr_template_dtls;
1725
1726
1727
1728 -- debug log
1729 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1730 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1731 G_MODULE||l_api_name,
1732 '110: Rule Import x_run_id : '|| x_run_id);
1733 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1734 G_MODULE||l_api_name,
1735 '110: p_template_id : '|| p_template_id);
1736 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1737 G_MODULE||l_api_name,
1738 '110: l_parent_template_id '|| l_parent_template_id);
1739 END IF;
1740
1741 IF l_parent_template_id IS NULL THEN
1742 -- Not a Revision Template
1743 l_template_id := p_template_id;
1744 ELSE
1745 -- Revision Template
1746 l_template_id := l_parent_template_id;
1747 END IF;
1748
1749 OPEN csr_template_rules;
1750 LOOP
1751 FETCH csr_template_rules INTO l_rule_id;
1752 EXIT WHEN csr_template_rules%NOTFOUND;
1753
1754 build_and_insert_rule
1755 (
1756 p_rule_id => l_rule_id,
1757 p_template_id => l_template_id,
1758 p_run_id => x_run_id,
1759 p_mode => 'P',
1760 x_return_status => x_return_status,
1761 x_msg_data => x_msg_data,
1762 x_msg_count => x_msg_count
1763 );
1764
1765 END LOOP;
1766 CLOSE csr_template_rules;
1767
1768 -- In Case of Revision templates, delete Active Rules on Parent Template
1769 IF l_parent_template_id IS NOT NULL THEN
1770 OPEN csr_parent_tmpl_rules(p_parent_tmpl_id => l_parent_template_id);
1771 LOOP
1772 FETCH csr_parent_tmpl_rules INTO l_rule_id;
1773 EXIT WHEN csr_parent_tmpl_rules%NOTFOUND;
1774
1775 build_and_insert_rule
1776 (
1777 p_rule_id => l_rule_id,
1778 p_template_id => l_template_id,
1779 p_run_id => x_run_id,
1780 p_mode => 'D',
1781 x_return_status => x_return_status,
1782 x_msg_data => x_msg_data,
1783 x_msg_count => x_msg_count
1784 );
1785
1786 END LOOP;
1787 CLOSE csr_parent_tmpl_rules;
1788
1789 END IF; -- revision template
1790
1791
1792
1793 -- Standard call to get message count and if count is 1, get message info.
1794 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1795
1796
1797 -- end debug log
1798 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1799 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1800 G_MODULE||l_api_name,
1801 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1802 END IF;
1803
1804 EXCEPTION
1805 WHEN FND_API.G_EXC_ERROR THEN
1806 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1807 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1808 G_MODULE||l_api_name,
1809 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1810 END IF;
1811
1812 fnd_file.put_line(FND_FILE.LOG,' ');
1813 fnd_file.put_line(FND_FILE.LOG,'Error in import_rule_temp_approval '||SQLERRM);
1814 fnd_file.put_line(FND_FILE.LOG,' ');
1815
1816 x_return_status := G_RET_STS_ERROR ;
1817 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1818
1819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1820 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1821 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1822 G_MODULE||l_api_name,
1823 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1824 END IF;
1825
1826 fnd_file.put_line(FND_FILE.LOG,' ');
1827 fnd_file.put_line(FND_FILE.LOG,'Unexpected Error in import_rule_temp_approval '||SQLERRM);
1828 fnd_file.put_line(FND_FILE.LOG,' ');
1829
1830 x_return_status := G_RET_STS_UNEXP_ERROR ;
1831 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1832
1833 WHEN OTHERS THEN
1834 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1835 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1836 G_MODULE||l_api_name,
1837 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1838 END IF;
1839
1840 fnd_file.put_line(FND_FILE.LOG,' ');
1841 fnd_file.put_line(FND_FILE.LOG,'Other Error in import_rule_temp_approval '||SQLERRM);
1842 fnd_file.put_line(FND_FILE.LOG,' ');
1843
1844 x_return_status := G_RET_STS_UNEXP_ERROR ;
1845 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1846 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1847 END IF;
1848 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1849
1850 END import_rule_temp_approval;
1851
1852 ---------------------------------------------------
1853 FUNCTION check_extension_rule
1854 (
1855 p_intent IN VARCHAR2,
1856 p_org_id IN NUMBER
1857 )
1858 RETURN VARCHAR2 IS
1859
1860 CURSOR csr_check_var_model IS
1861 SELECT devl_project_id
1862 FROM cz_devl_projects
1863 WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||p_intent
1864 AND devl_project_id = persistent_project_id
1865 AND deleted_flag = 0;
1866
1867 --Added for R12
1868 CURSOR csr_check_cla_model IS
1869 SELECT devl_project_id
1870 FROM cz_devl_projects
1871 WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||p_org_id||':'||p_intent
1872 AND devl_project_id = persistent_project_id
1873 AND deleted_flag = 0;
1874
1875 CURSOR csr_check_ext_rule (p_devl_project_id NUMBER) IS
1876 SELECT 'X'
1877 FROM cz_rules
1878 WHERE rule_type = 300 -- Extension rule
1879 AND devl_project_id = p_devl_project_id
1880 AND rule_id = persistent_rule_id
1881 AND deleted_flag = 0;
1882
1883 l_devl_project_id NUMBER;
1884 l_exists VARCHAR2(1);
1885
1886 BEGIN
1887
1888 OPEN csr_check_cla_model;
1889 FETCH csr_check_cla_model INTO l_devl_project_id;
1890 IF csr_check_cla_model%FOUND THEN
1891 OPEN csr_check_ext_rule(l_devl_project_id);
1892 FETCH csr_check_ext_rule INTO l_exists;
1893 IF csr_check_ext_rule%FOUND THEN
1894 RETURN FND_API.G_TRUE;
1895 ELSE
1896 RETURN FND_API.G_FALSE;
1897 END IF;
1898 CLOSE csr_check_ext_rule;
1899 ELSE
1900 RETURN FND_API.G_FALSE;
1901 END IF;
1902 CLOSE csr_check_cla_model;
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 IF csr_check_cla_model%ISOPEN THEN
1907 CLOSE csr_check_cla_model;
1908 END IF;
1909 IF csr_check_cla_model%ISOPEN THEN
1910 CLOSE csr_check_cla_model;
1911 END IF;
1912
1913 END check_extension_rule;
1914
1915 ---------------------------------------------------
1916
1917 PROCEDURE attach_extension_rule
1918 (
1919 p_api_version IN NUMBER,
1920 p_init_msg_list IN VARCHAR2,
1921 p_run_id IN NUMBER,
1922 x_return_status OUT NOCOPY VARCHAR2,
1923 x_msg_data OUT NOCOPY VARCHAR2,
1924 x_msg_count OUT NOCOPY NUMBER
1925 ) IS
1926
1927 -- Added Org_id to the cusor for R12
1928 CURSOR csr_intents IS
1929 SELECT distinct intent, org_id
1930 FROM okc_xprt_rule_hdrs_all r
1931 WHERE r.request_id = fnd_global.conc_request_id;
1932
1933 CURSOR csr_get_var_model_dtl (l_intent VARCHAR2) IS
1934 SELECT devl_project_id, orig_sys_ref
1935 FROM cz_devl_projects
1936 WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||l_intent
1937 AND devl_project_id = persistent_project_id
1938 AND deleted_flag = 0;
1939
1940 -- Added new cursor for attaching extension rule to Clause model from R12
1941 CURSOR csr_get_cla_model_dtl (l_org_id NUMBER, l_intent VARCHAR2) IS
1942 SELECT devl_project_id, orig_sys_ref
1943 FROM cz_devl_projects
1944 WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||to_char(l_org_id)||':'||l_intent
1945 AND devl_project_id = persistent_project_id
1946 AND deleted_flag = 0;
1947
1948 CURSOR csr_installed_languages IS
1949 SELECT L.LANGUAGE_CODE
1950 FROM FND_LANGUAGES L
1951 WHERE L.INSTALLED_FLAG IN ('I', 'B');
1952
1953 l_api_name CONSTANT VARCHAR2(30) := 'attach_extension_rule';
1954 l_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
1955 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
1956 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
1957 l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
1958 l_seq_nbr cz_imp_rules.seq_nbr%TYPE := 0;
1959 l_rule_name cz_imp_rules.NAME%TYPE;
1960 l_rule_description cz_imp_rules.desc_text%TYPE;
1961 l_rule_text cz_imp_rules.rule_text%TYPE;
1962 l_model_id cz_imp_localized_texts.MODEL_ID%TYPE;
1963 l_model_osr cz_imp_localized_texts.fsk_devlproject_1_1%TYPE;
1964
1965 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
1966
1967 l_cz_imp_rules cz_imp_rules%ROWTYPE;
1968
1969 BEGIN
1970 -- start debug log
1971 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1972 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1973 G_MODULE||l_api_name,
1974 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1975 END IF;
1976
1977 x_return_status := G_RET_STS_SUCCESS;
1978
1979 -- Set Extension rule name
1980 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_NAME');
1981 l_rule_name := FND_MESSAGE.get; -- Get Extension rule name from OKC_XPRT_XTN_RULE_NAME message
1982
1983 -- Set Extension rule description
1984 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_DESC');
1985 l_rule_description := FND_MESSAGE.get; -- Get Extension rule description from OKC_XPRT_XTN_RULE_DESC message
1986
1987
1988 OPEN csr_intents;
1989 LOOP
1990 FETCH csr_intents INTO l_intent,l_org_id;
1991 EXIT WHEN csr_intents%NOTFOUND;
1992
1993 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1994 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1995 G_MODULE||l_api_name,
1996 '110: l_intent ' || '='||l_intent);
1997 END IF;
1998
1999 -- Modified to attach extension rule to Clause model from R12
2000 OPEN csr_get_cla_model_dtl(l_org_id,l_intent);
2001 FETCH csr_get_cla_model_dtl INTO l_model_id, l_model_osr;
2002 IF csr_get_cla_model_dtl%NOTFOUND THEN
2003 RAISE FND_API.G_EXC_ERROR;
2004 END IF;
2005 CLOSE csr_get_cla_model_dtl;
2006
2007 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2008 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2009 G_MODULE||l_api_name,
2010 '120: l_model_id ' || '='||l_model_id);
2011 END IF;
2012
2013 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2014 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2015 G_MODULE||l_api_name,
2016 '130: l_model_osr ' || '='||l_model_osr);
2017 END IF;
2018
2019 -- Generate the Rule Text for this Run Id if the rule doesn't exist/has been deleted/disabled
2020 -- Check_extension rule changed in R12 to check against Clause model. Added new parameter l_org_id
2021 IF (check_extension_rule(l_intent, l_org_id) = FND_API.G_FALSE) THEN
2022
2023 fnd_file.put_line(FND_FILE.LOG,' ');
2024 fnd_file.put_line(FND_FILE.LOG,'Creating Extension Rule for : '||l_model_osr);
2025 fnd_file.put_line(FND_FILE.LOG,' ');
2026
2027 -- Insert Rule Name into cz_imp_localized_texts
2028
2029 OPEN csr_installed_languages;
2030 LOOP
2031 FETCH csr_installed_languages INTO l_language;
2032 EXIT WHEN csr_installed_languages%NOTFOUND;
2033
2034 -- Insert into cz_imp_localized_text
2035 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2036 (
2037 LAST_UPDATE_LOGIN,
2038 LOCALE_ID,
2039 LOCALIZED_STR,
2040 INTL_TEXT_ID,
2041 CREATION_DATE,
2042 LAST_UPDATE_DATE,
2043 DELETED_FLAG,
2044 EFF_FROM,
2045 EFF_TO,
2046 CREATED_BY,
2047 LAST_UPDATED_BY,
2048 SECURITY_MASK,
2049 EFF_MASK,
2050 CHECKOUT_USER,
2051 LANGUAGE,
2052 ORIG_SYS_REF,
2053 SOURCE_LANG,
2054 RUN_ID,
2055 REC_STATUS,
2056 DISPOSITION,
2057 MODEL_ID,
2058 FSK_DEVLPROJECT_1_1,
2059 MESSAGE,
2060 SEEDED_FLAG
2061 )
2062 VALUES
2063 (
2064 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
2065 NULL, -- LOCALE_ID
2066 l_rule_name, --LOCALIZED_STR -
2067 NULL, -- INTL_TEXT_ID
2068 SYSDATE, -- CREATION_DATE
2069 SYSDATE, -- LAST_UPDATE_DATE
2070 '0', -- DELETED_FLAG
2071 NULL, -- EFF_FROM
2072 NULL, -- EFF_TO
2073 FND_GLOBAL.USER_ID, -- CREATED_BY
2074 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
2075 NULL, -- SECURITY_MASK
2076 NULL, -- EFF_MASK
2077 NULL, -- CHECKOUT_USER
2078 l_language, --LANGUAGE
2079 G_XTN_RULE_OSR||l_intent, --ORIG_SYS_REF
2080 USERENV('LANG'), --SOURCE_LANG
2081 p_run_id, -- RUN_ID
2082 NULL, -- REC_STATUS
2083 NULL, -- DISPOSITION
2084 l_model_id, -- MODEL_ID
2085 l_model_osr, --FSK_DEVLPROJECT_1_1
2086 NULL, -- MESSAGE
2087 NULL -- SEEDED_FLAG
2088 );
2089
2090 END LOOP;
2091 CLOSE csr_installed_languages;
2092
2093
2094 -- Populate the cz_imp_rules record
2095 l_seq_nbr := l_seq_nbr + 1;
2096
2097 l_cz_imp_rules.RULE_ID := NULL;
2098 l_cz_imp_rules.SUB_CONS_ID := NULL;
2099 l_cz_imp_rules.REASON_ID := NULL;
2100 l_cz_imp_rules.AMOUNT_ID := NULL;
2101 l_cz_imp_rules.GRID_ID := NULL;
2102 l_cz_imp_rules.RULE_FOLDER_ID := NULL;
2103 l_cz_imp_rules.DEVL_PROJECT_ID := l_model_id;
2104 l_cz_imp_rules.INVALID_FLAG := '0'; --Default value for valid rule
2105 l_cz_imp_rules.DESC_TEXT := l_rule_description;
2106 l_cz_imp_rules.NAME := l_rule_name; -- check
2107 l_cz_imp_rules.ANTECEDENT_ID := NULL;
2108 l_cz_imp_rules.CONSEQUENT_ID := NULL;
2109 l_cz_imp_rules.RULE_TYPE := 300; -- Extension Rule
2110 l_cz_imp_rules.EXPR_RULE_TYPE := NULL; --1; --Needed for Extension rule
2111 l_cz_imp_rules.COMPONENT_ID := NULL;
2112 l_cz_imp_rules.REASON_TYPE := 0; -- Value in reason_id corresponds to Name of Rule
2113 l_cz_imp_rules.DISABLED_FLAG := '0'; -- Indicates enabled rule
2114 l_cz_imp_rules.ORIG_SYS_REF := G_XTN_RULE_OSR||l_intent;
2115 l_cz_imp_rules.CREATION_DATE := SYSDATE;
2116 l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
2117 l_cz_imp_rules.DELETED_FLAG := 0; -- check
2118 l_cz_imp_rules.EFF_FROM := NULL;
2119 l_cz_imp_rules.EFF_TO := NULL;
2120 l_cz_imp_rules.CREATED_BY := FND_GLOBAL.USER_ID;
2121 l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2122 l_cz_imp_rules.SECURITY_MASK := NULL;
2123 l_cz_imp_rules.EFF_MASK := NULL;
2124 l_cz_imp_rules.CHECKOUT_USER := NULL;
2125 l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2126 l_cz_imp_rules.EFFECTIVE_USAGE_MASK := NULL;
2127 l_cz_imp_rules.SEQ_NBR := l_seq_nbr;
2128 l_cz_imp_rules.EFFECTIVE_FROM := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
2129 l_cz_imp_rules.EFFECTIVE_UNTIL := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
2130 l_cz_imp_rules.PERSISTENT_RULE_ID := NULL;
2131 l_cz_imp_rules.EFFECTIVITY_SET_ID := NULL;
2132 l_cz_imp_rules.RULE_FOLDER_TYPE := NULL;
2133 l_cz_imp_rules.UNSATISFIED_MSG_ID := NULL;
2134 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE := '0'; -- Changed from NULL
2135 l_cz_imp_rules.SIGNATURE_ID := NULL;
2136 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG := NULL;
2137 l_cz_imp_rules.PRESENTATION_FLAG := NULL; --'0'; --Default value
2138 l_cz_imp_rules.TEMPLATE_TOKEN := NULL;
2139 -- l_cz_imp_rules.RULE_TEXT := G_XTN_RULE_TEXT;
2140 l_cz_imp_rules.NOTES := NULL;
2141 l_cz_imp_rules.CLASS_NAME := G_XTN_RULE_CLASS_NAME;
2142 l_cz_imp_rules.INSTANTIATION_SCOPE := 1; -- Needed for Extension rule
2143 l_cz_imp_rules.MODEL_REF_EXPL_ID := NULL;
2144 l_cz_imp_rules.MUTABLE_FLAG := '0'; --Default value
2145 l_cz_imp_rules.SEEDED_FLAG := NULL; --Seeded CX rule
2146 l_cz_imp_rules.UI_DEF_ID := NULL;
2147 l_cz_imp_rules.UI_PAGE_ID := NULL;
2148 l_cz_imp_rules.UI_PAGE_ELEMENT_ID := NULL;
2149 l_cz_imp_rules.MESSAGE := NULL;
2150 l_cz_imp_rules.RUN_ID := p_run_id; -- Input runid
2151 l_cz_imp_rules.DISPOSITION := NULL;
2152 l_cz_imp_rules.REC_STATUS := NULL;
2153 l_cz_imp_rules.FSK_DEVL_PROJECT := l_model_osr;
2154 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1 := G_XTN_RULE_OSR||l_intent;
2155 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2 := NULL;
2156 l_cz_imp_rules.IMPORT_PROG_VERSION := NULL;
2157 l_cz_imp_rules.FSK_COMPONENT_ID := NULL;
2158 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID := NULL;
2159
2160
2161 -- Insert into cz_imp_rules
2162
2163 INSERT INTO cz_imp_rules
2164 (
2165 RULE_ID,
2166 SUB_CONS_ID,
2167 REASON_ID,
2168 AMOUNT_ID,
2169 GRID_ID,
2170 RULE_FOLDER_ID,
2171 DEVL_PROJECT_ID,
2172 INVALID_FLAG,
2173 DESC_TEXT,
2174 NAME,
2175 ANTECEDENT_ID,
2176 CONSEQUENT_ID,
2177 RULE_TYPE,
2178 EXPR_RULE_TYPE,
2179 COMPONENT_ID,
2180 REASON_TYPE,
2181 DISABLED_FLAG,
2182 ORIG_SYS_REF,
2183 CREATION_DATE,
2184 LAST_UPDATE_DATE,
2185 DELETED_FLAG,
2186 EFF_FROM,
2187 EFF_TO,
2188 CREATED_BY,
2189 LAST_UPDATED_BY,
2190 SECURITY_MASK,
2191 EFF_MASK,
2192 CHECKOUT_USER,
2193 LAST_UPDATE_LOGIN,
2194 EFFECTIVE_USAGE_MASK,
2195 SEQ_NBR,
2196 EFFECTIVE_FROM,
2197 EFFECTIVE_UNTIL,
2198 PERSISTENT_RULE_ID,
2199 EFFECTIVITY_SET_ID,
2200 RULE_FOLDER_TYPE,
2201 UNSATISFIED_MSG_ID,
2202 UNSATISFIED_MSG_SOURCE,
2203 SIGNATURE_ID,
2204 TEMPLATE_PRIMITIVE_FLAG,
2205 PRESENTATION_FLAG,
2206 TEMPLATE_TOKEN,
2207 RULE_TEXT,
2208 NOTES,
2209 CLASS_NAME,
2210 INSTANTIATION_SCOPE,
2211 MODEL_REF_EXPL_ID,
2212 MUTABLE_FLAG,
2213 SEEDED_FLAG,
2214 UI_DEF_ID,
2215 UI_PAGE_ID,
2216 UI_PAGE_ELEMENT_ID,
2217 MESSAGE,
2218 RUN_ID,
2219 DISPOSITION,
2220 REC_STATUS,
2221 FSK_DEVL_PROJECT,
2222 FSK_LOCALIZED_TEXT_1,
2223 FSK_LOCALIZED_TEXT_2,
2224 IMPORT_PROG_VERSION,
2225 FSK_COMPONENT_ID,
2226 FSK_MODEL_REF_EXPL_ID
2227 )
2228 VALUES
2229 (
2230 l_cz_imp_rules.RULE_ID,
2231 l_cz_imp_rules.SUB_CONS_ID,
2232 l_cz_imp_rules.REASON_ID,
2233 l_cz_imp_rules.AMOUNT_ID,
2234 l_cz_imp_rules.GRID_ID,
2235 l_cz_imp_rules.RULE_FOLDER_ID,
2236 l_cz_imp_rules.DEVL_PROJECT_ID,
2237 l_cz_imp_rules.INVALID_FLAG,
2238 l_cz_imp_rules.DESC_TEXT,
2239 l_cz_imp_rules.NAME,
2240 l_cz_imp_rules.ANTECEDENT_ID,
2241 l_cz_imp_rules.CONSEQUENT_ID,
2242 l_cz_imp_rules.RULE_TYPE,
2243 l_cz_imp_rules.EXPR_RULE_TYPE,
2244 l_cz_imp_rules.COMPONENT_ID,
2245 l_cz_imp_rules.REASON_TYPE,
2246 l_cz_imp_rules.DISABLED_FLAG,
2247 l_cz_imp_rules.ORIG_SYS_REF,
2248 l_cz_imp_rules.CREATION_DATE,
2249 l_cz_imp_rules.LAST_UPDATE_DATE,
2250 l_cz_imp_rules.DELETED_FLAG,
2251 l_cz_imp_rules.EFF_FROM,
2252 l_cz_imp_rules.EFF_TO,
2253 l_cz_imp_rules.CREATED_BY,
2254 l_cz_imp_rules.LAST_UPDATED_BY,
2255 l_cz_imp_rules.SECURITY_MASK,
2256 l_cz_imp_rules.EFF_MASK,
2257 l_cz_imp_rules.CHECKOUT_USER,
2258 l_cz_imp_rules.LAST_UPDATE_LOGIN,
2259 l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
2260 l_cz_imp_rules.SEQ_NBR,
2261 l_cz_imp_rules.EFFECTIVE_FROM,
2262 l_cz_imp_rules.EFFECTIVE_UNTIL,
2263 l_cz_imp_rules.PERSISTENT_RULE_ID,
2264 l_cz_imp_rules.EFFECTIVITY_SET_ID,
2265 l_cz_imp_rules.RULE_FOLDER_TYPE,
2266 l_cz_imp_rules.UNSATISFIED_MSG_ID,
2267 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
2268 l_cz_imp_rules.SIGNATURE_ID,
2269 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
2270 l_cz_imp_rules.PRESENTATION_FLAG,
2271 l_cz_imp_rules.TEMPLATE_TOKEN,
2272 --l_cz_imp_rules.RULE_TEXT,
2273 G_XTN_RULE_TEXT,
2274 l_cz_imp_rules.NOTES,
2275 l_cz_imp_rules.CLASS_NAME,
2276 l_cz_imp_rules.INSTANTIATION_SCOPE,
2277 l_cz_imp_rules.MODEL_REF_EXPL_ID,
2278 l_cz_imp_rules.MUTABLE_FLAG,
2279 l_cz_imp_rules.SEEDED_FLAG,
2280 l_cz_imp_rules.UI_DEF_ID,
2281 l_cz_imp_rules.UI_PAGE_ID,
2282 l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
2283 l_cz_imp_rules.MESSAGE,
2284 l_cz_imp_rules.RUN_ID,
2285 l_cz_imp_rules.DISPOSITION,
2286 l_cz_imp_rules.REC_STATUS,
2287 l_cz_imp_rules.FSK_DEVL_PROJECT,
2288 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
2289 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
2290 l_cz_imp_rules.IMPORT_PROG_VERSION,
2291 l_cz_imp_rules.FSK_COMPONENT_ID,
2292 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
2293 );
2294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2295 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2296 G_MODULE||l_api_name,
2297 '170: After Insert into cz_imp_rules' || '='||l_intent);
2298 END IF;
2299
2300 END IF; -- check extn rule exists
2301
2302 END LOOP;
2303 CLOSE csr_intents; -- Insert the Extension Rule for this Intent
2304
2305
2306 -- Standard call to get message count and if count is 1, get message info.
2307 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2308
2309
2310 -- end debug log
2311 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2312 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2313 G_MODULE||l_api_name,
2314 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2315 END IF;
2316
2317 EXCEPTION
2318 WHEN FND_API.G_EXC_ERROR THEN
2319 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2320 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2321 G_MODULE||l_api_name,
2322 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2323 END IF;
2324
2325 x_return_status := G_RET_STS_ERROR ;
2326 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2327
2328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2329 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2331 G_MODULE||l_api_name,
2332 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2333 END IF;
2334
2335 x_return_status := G_RET_STS_UNEXP_ERROR ;
2336 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2337
2338 WHEN OTHERS THEN
2339 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2340 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2341 G_MODULE||l_api_name,
2342 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2343 END IF;
2344
2345 x_return_status := G_RET_STS_UNEXP_ERROR ;
2346 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2347 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2348 END IF;
2349 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2350
2351 END attach_extension_rule;
2352
2353
2354 ---------------------------------------------------
2355 PROCEDURE attach_extension_rule_tmpl
2356 (
2357 p_api_version IN NUMBER,
2358 p_init_msg_list IN VARCHAR2,
2359 p_run_id IN NUMBER,
2360 p_template_id IN NUMBER,
2361 x_return_status OUT NOCOPY VARCHAR2,
2362 x_msg_data OUT NOCOPY VARCHAR2,
2363 x_msg_count OUT NOCOPY NUMBER
2364 ) IS
2365
2366 -- Added Org_id to the cusor for R12
2367 CURSOR csr_intents IS
2368 SELECT intent,org_id
2369 FROM OKC_TERMS_TEMPLATES_ALL t
2370 WHERE t.template_id = p_template_id;
2371
2372 CURSOR csr_get_var_model_dtl (l_intent VARCHAR2) IS
2373 SELECT devl_project_id, orig_sys_ref
2374 FROM cz_devl_projects
2375 WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||l_intent
2376 AND devl_project_id = persistent_project_id
2377 AND deleted_flag = 0;
2378
2379 -- Added new cursor for attaching extension rule to Clause model from R12
2380 CURSOR csr_get_cla_model_dtl (l_org_id NUMBER, l_intent VARCHAR2) IS
2381 SELECT devl_project_id, orig_sys_ref
2382 FROM cz_devl_projects
2383 WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||l_org_id||':'||l_intent
2384 AND devl_project_id = persistent_project_id
2385 AND deleted_flag = 0;
2386
2387 CURSOR csr_installed_languages IS
2388 SELECT L.LANGUAGE_CODE
2389 FROM FND_LANGUAGES L
2390 WHERE L.INSTALLED_FLAG IN ('I', 'B');
2391
2392 l_api_name CONSTANT VARCHAR2(30) := 'attach_extension_rule_tmpl';
2393 l_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
2394 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
2395 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
2396 l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
2397 l_seq_nbr cz_imp_rules.seq_nbr%TYPE := 0;
2398 l_rule_name cz_imp_rules.NAME%TYPE;
2399 l_rule_description cz_imp_rules.desc_text%TYPE;
2400 l_rule_text cz_imp_rules.rule_text%TYPE;
2401 l_model_id cz_imp_localized_texts.MODEL_ID%TYPE;
2402 l_model_osr cz_imp_localized_texts.fsk_devlproject_1_1%TYPE;
2403
2404 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
2405
2406 l_cz_imp_rules cz_imp_rules%ROWTYPE;
2407
2408 BEGIN
2409 -- start debug log
2410 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2411 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2412 G_MODULE||l_api_name,
2413 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2414 END IF;
2415
2416 x_return_status := G_RET_STS_SUCCESS;
2417
2418 -- Set Extension rule name
2419 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_NAME');
2420 l_rule_name := FND_MESSAGE.get; -- Get Extension rule name from OKC_XPRT_XTN_RULE_NAME message
2421
2422 -- Set Extension rule description
2423 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_DESC');
2424 l_rule_description := FND_MESSAGE.get; -- Get Extension rule description from OKC_XPRT_XTN_RULE_DESC message
2425
2426
2427 OPEN csr_intents;
2428 LOOP
2429 FETCH csr_intents INTO l_intent,l_org_id;
2430 EXIT WHEN csr_intents%NOTFOUND;
2431
2432 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2433 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2434 G_MODULE||l_api_name,
2435 '110: l_intent ' || '='||l_intent);
2436 END IF;
2437
2438 -- Modified to attach extension rule to Clause model from R12
2439 OPEN csr_get_cla_model_dtl(l_intent,l_org_id);
2440 FETCH csr_get_cla_model_dtl INTO l_model_id, l_model_osr;
2441 IF csr_get_cla_model_dtl%NOTFOUND THEN
2442 RAISE FND_API.G_EXC_ERROR;
2443 END IF;
2444 CLOSE csr_get_cla_model_dtl;
2445
2446 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2447 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2448 G_MODULE||l_api_name,
2449 '120: l_model_id ' || '='||l_model_id);
2450 END IF;
2451
2452 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2453 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2454 G_MODULE||l_api_name,
2455 '130: l_model_osr ' || '='||l_model_osr);
2456 END IF;
2457
2458 -- Generate the Rule Text for this Run Id if the rule doesn't exist/has been deleted/disabled
2459 -- Check_extension rule changed in R12 to check against Clause model. Added new parameter l_org_id
2460 IF (check_extension_rule(l_intent, l_org_id) = FND_API.G_FALSE) THEN
2461
2462 fnd_file.put_line(FND_FILE.LOG,' ');
2463 fnd_file.put_line(FND_FILE.LOG,'Creating Extension Rule for : '||l_model_osr);
2464 fnd_file.put_line(FND_FILE.LOG,' ');
2465
2466 -- Insert Rule Name into cz_imp_localized_texts
2467
2468 OPEN csr_installed_languages;
2469 LOOP
2470 FETCH csr_installed_languages INTO l_language;
2471 EXIT WHEN csr_installed_languages%NOTFOUND;
2472
2473 -- Insert into cz_imp_localized_text
2474 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2475 (
2476 LAST_UPDATE_LOGIN,
2477 LOCALE_ID,
2478 LOCALIZED_STR,
2479 INTL_TEXT_ID,
2480 CREATION_DATE,
2481 LAST_UPDATE_DATE,
2482 DELETED_FLAG,
2483 EFF_FROM,
2484 EFF_TO,
2485 CREATED_BY,
2486 LAST_UPDATED_BY,
2487 SECURITY_MASK,
2488 EFF_MASK,
2489 CHECKOUT_USER,
2490 LANGUAGE,
2491 ORIG_SYS_REF,
2492 SOURCE_LANG,
2493 RUN_ID,
2494 REC_STATUS,
2495 DISPOSITION,
2496 MODEL_ID,
2497 FSK_DEVLPROJECT_1_1,
2498 MESSAGE,
2499 SEEDED_FLAG
2500 )
2501 VALUES
2502 (
2503 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
2504 NULL, -- LOCALE_ID
2505 l_rule_name, --LOCALIZED_STR -
2506 NULL, -- INTL_TEXT_ID
2507 SYSDATE, -- CREATION_DATE
2508 SYSDATE, -- LAST_UPDATE_DATE
2509 '0', -- DELETED_FLAG
2510 NULL, -- EFF_FROM
2511 NULL, -- EFF_TO
2512 FND_GLOBAL.USER_ID, -- CREATED_BY
2513 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
2514 NULL, -- SECURITY_MASK
2515 NULL, -- EFF_MASK
2516 NULL, -- CHECKOUT_USER
2517 l_language, --LANGUAGE
2518 G_XTN_RULE_OSR||l_intent, --ORIG_SYS_REF
2519 USERENV('LANG'), --SOURCE_LANG
2520 p_run_id, -- RUN_ID
2521 NULL, -- REC_STATUS
2522 NULL, -- DISPOSITION
2523 l_model_id, -- MODEL_ID
2524 l_model_osr, --FSK_DEVLPROJECT_1_1
2525 NULL, -- MESSAGE
2526 NULL -- SEEDED_FLAG
2527 );
2528
2529 END LOOP;
2530 CLOSE csr_installed_languages;
2531
2532
2533 -- Populate the cz_imp_rules record
2534 l_seq_nbr := l_seq_nbr + 1;
2535
2536 l_cz_imp_rules.RULE_ID := NULL;
2537 l_cz_imp_rules.SUB_CONS_ID := NULL;
2538 l_cz_imp_rules.REASON_ID := NULL;
2539 l_cz_imp_rules.AMOUNT_ID := NULL;
2540 l_cz_imp_rules.GRID_ID := NULL;
2541 l_cz_imp_rules.RULE_FOLDER_ID := NULL;
2542 l_cz_imp_rules.DEVL_PROJECT_ID := l_model_id;
2543 l_cz_imp_rules.INVALID_FLAG := '0'; --Default value for valid rule
2544 l_cz_imp_rules.DESC_TEXT := l_rule_description;
2545 l_cz_imp_rules.NAME := l_rule_name; -- check
2546 l_cz_imp_rules.ANTECEDENT_ID := NULL;
2547 l_cz_imp_rules.CONSEQUENT_ID := NULL;
2548 l_cz_imp_rules.RULE_TYPE := 300; -- Extension Rule
2549 l_cz_imp_rules.EXPR_RULE_TYPE := NULL; --1; --Needed for Extension rule
2550 l_cz_imp_rules.COMPONENT_ID := NULL;
2551 l_cz_imp_rules.REASON_TYPE := 0; -- Value in reason_id corresponds to Name of Rule
2552 l_cz_imp_rules.DISABLED_FLAG := '0'; -- Indicates enabled rule
2553 l_cz_imp_rules.ORIG_SYS_REF := G_XTN_RULE_OSR||l_intent;
2554 l_cz_imp_rules.CREATION_DATE := SYSDATE;
2555 l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
2556 l_cz_imp_rules.DELETED_FLAG := 0; -- check
2557 l_cz_imp_rules.EFF_FROM := NULL;
2558 l_cz_imp_rules.EFF_TO := NULL;
2559 l_cz_imp_rules.CREATED_BY := FND_GLOBAL.USER_ID;
2560 l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2561 l_cz_imp_rules.SECURITY_MASK := NULL;
2562 l_cz_imp_rules.EFF_MASK := NULL;
2563 l_cz_imp_rules.CHECKOUT_USER := NULL;
2564 l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2565 l_cz_imp_rules.EFFECTIVE_USAGE_MASK := NULL;
2566 l_cz_imp_rules.SEQ_NBR := l_seq_nbr;
2567 l_cz_imp_rules.EFFECTIVE_FROM := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
2568 l_cz_imp_rules.EFFECTIVE_UNTIL := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
2569 l_cz_imp_rules.PERSISTENT_RULE_ID := NULL;
2570 l_cz_imp_rules.EFFECTIVITY_SET_ID := NULL;
2571 l_cz_imp_rules.RULE_FOLDER_TYPE := NULL;
2572 l_cz_imp_rules.UNSATISFIED_MSG_ID := NULL;
2573 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE := '0'; -- Changed from NULL
2574 l_cz_imp_rules.SIGNATURE_ID := NULL;
2575 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG := NULL;
2576 l_cz_imp_rules.PRESENTATION_FLAG := NULL; --'0'; --Default value
2577 l_cz_imp_rules.TEMPLATE_TOKEN := NULL;
2578 -- l_cz_imp_rules.RULE_TEXT := G_XTN_RULE_TEXT;
2579 l_cz_imp_rules.NOTES := NULL;
2580 l_cz_imp_rules.CLASS_NAME := G_XTN_RULE_CLASS_NAME;
2581 l_cz_imp_rules.INSTANTIATION_SCOPE := 1; -- Needed for Extension rule
2582 l_cz_imp_rules.MODEL_REF_EXPL_ID := NULL;
2583 l_cz_imp_rules.MUTABLE_FLAG := '0'; --Default value
2584 l_cz_imp_rules.SEEDED_FLAG := NULL; --Seeded CX rule
2585 l_cz_imp_rules.UI_DEF_ID := NULL;
2586 l_cz_imp_rules.UI_PAGE_ID := NULL;
2587 l_cz_imp_rules.UI_PAGE_ELEMENT_ID := NULL;
2588 l_cz_imp_rules.MESSAGE := NULL;
2589 l_cz_imp_rules.RUN_ID := p_run_id; -- Input runid
2590 l_cz_imp_rules.DISPOSITION := NULL;
2591 l_cz_imp_rules.REC_STATUS := NULL;
2592 l_cz_imp_rules.FSK_DEVL_PROJECT := l_model_osr;
2593 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1 := G_XTN_RULE_OSR||l_intent;
2594 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2 := NULL;
2595 l_cz_imp_rules.IMPORT_PROG_VERSION := NULL;
2596 l_cz_imp_rules.FSK_COMPONENT_ID := NULL;
2597 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID := NULL;
2598
2599
2600 -- Insert into cz_imp_rules
2601
2602 INSERT INTO cz_imp_rules
2603 (
2604 RULE_ID,
2605 SUB_CONS_ID,
2606 REASON_ID,
2607 AMOUNT_ID,
2608 GRID_ID,
2609 RULE_FOLDER_ID,
2610 DEVL_PROJECT_ID,
2611 INVALID_FLAG,
2612 DESC_TEXT,
2613 NAME,
2614 ANTECEDENT_ID,
2615 CONSEQUENT_ID,
2616 RULE_TYPE,
2617 EXPR_RULE_TYPE,
2618 COMPONENT_ID,
2619 REASON_TYPE,
2620 DISABLED_FLAG,
2621 ORIG_SYS_REF,
2622 CREATION_DATE,
2623 LAST_UPDATE_DATE,
2624 DELETED_FLAG,
2625 EFF_FROM,
2626 EFF_TO,
2627 CREATED_BY,
2628 LAST_UPDATED_BY,
2629 SECURITY_MASK,
2630 EFF_MASK,
2631 CHECKOUT_USER,
2632 LAST_UPDATE_LOGIN,
2633 EFFECTIVE_USAGE_MASK,
2634 SEQ_NBR,
2635 EFFECTIVE_FROM,
2636 EFFECTIVE_UNTIL,
2637 PERSISTENT_RULE_ID,
2638 EFFECTIVITY_SET_ID,
2639 RULE_FOLDER_TYPE,
2640 UNSATISFIED_MSG_ID,
2641 UNSATISFIED_MSG_SOURCE,
2642 SIGNATURE_ID,
2643 TEMPLATE_PRIMITIVE_FLAG,
2644 PRESENTATION_FLAG,
2645 TEMPLATE_TOKEN,
2646 RULE_TEXT,
2647 NOTES,
2648 CLASS_NAME,
2649 INSTANTIATION_SCOPE,
2650 MODEL_REF_EXPL_ID,
2651 MUTABLE_FLAG,
2652 SEEDED_FLAG,
2653 UI_DEF_ID,
2654 UI_PAGE_ID,
2655 UI_PAGE_ELEMENT_ID,
2656 MESSAGE,
2657 RUN_ID,
2658 DISPOSITION,
2659 REC_STATUS,
2660 FSK_DEVL_PROJECT,
2661 FSK_LOCALIZED_TEXT_1,
2662 FSK_LOCALIZED_TEXT_2,
2663 IMPORT_PROG_VERSION,
2664 FSK_COMPONENT_ID,
2665 FSK_MODEL_REF_EXPL_ID
2666 )
2667 VALUES
2668 (
2669 l_cz_imp_rules.RULE_ID,
2670 l_cz_imp_rules.SUB_CONS_ID,
2671 l_cz_imp_rules.REASON_ID,
2672 l_cz_imp_rules.AMOUNT_ID,
2673 l_cz_imp_rules.GRID_ID,
2674 l_cz_imp_rules.RULE_FOLDER_ID,
2675 l_cz_imp_rules.DEVL_PROJECT_ID,
2676 l_cz_imp_rules.INVALID_FLAG,
2677 l_cz_imp_rules.DESC_TEXT,
2678 l_cz_imp_rules.NAME,
2679 l_cz_imp_rules.ANTECEDENT_ID,
2680 l_cz_imp_rules.CONSEQUENT_ID,
2681 l_cz_imp_rules.RULE_TYPE,
2682 l_cz_imp_rules.EXPR_RULE_TYPE,
2683 l_cz_imp_rules.COMPONENT_ID,
2684 l_cz_imp_rules.REASON_TYPE,
2685 l_cz_imp_rules.DISABLED_FLAG,
2686 l_cz_imp_rules.ORIG_SYS_REF,
2687 l_cz_imp_rules.CREATION_DATE,
2688 l_cz_imp_rules.LAST_UPDATE_DATE,
2689 l_cz_imp_rules.DELETED_FLAG,
2690 l_cz_imp_rules.EFF_FROM,
2691 l_cz_imp_rules.EFF_TO,
2692 l_cz_imp_rules.CREATED_BY,
2693 l_cz_imp_rules.LAST_UPDATED_BY,
2694 l_cz_imp_rules.SECURITY_MASK,
2695 l_cz_imp_rules.EFF_MASK,
2696 l_cz_imp_rules.CHECKOUT_USER,
2697 l_cz_imp_rules.LAST_UPDATE_LOGIN,
2698 l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
2699 l_cz_imp_rules.SEQ_NBR,
2700 l_cz_imp_rules.EFFECTIVE_FROM,
2701 l_cz_imp_rules.EFFECTIVE_UNTIL,
2702 l_cz_imp_rules.PERSISTENT_RULE_ID,
2703 l_cz_imp_rules.EFFECTIVITY_SET_ID,
2704 l_cz_imp_rules.RULE_FOLDER_TYPE,
2705 l_cz_imp_rules.UNSATISFIED_MSG_ID,
2706 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
2707 l_cz_imp_rules.SIGNATURE_ID,
2708 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
2709 l_cz_imp_rules.PRESENTATION_FLAG,
2710 l_cz_imp_rules.TEMPLATE_TOKEN,
2711 --l_cz_imp_rules.RULE_TEXT,
2712 G_XTN_RULE_TEXT,
2713 l_cz_imp_rules.NOTES,
2714 l_cz_imp_rules.CLASS_NAME,
2715 l_cz_imp_rules.INSTANTIATION_SCOPE,
2716 l_cz_imp_rules.MODEL_REF_EXPL_ID,
2717 l_cz_imp_rules.MUTABLE_FLAG,
2718 l_cz_imp_rules.SEEDED_FLAG,
2719 l_cz_imp_rules.UI_DEF_ID,
2720 l_cz_imp_rules.UI_PAGE_ID,
2721 l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
2722 l_cz_imp_rules.MESSAGE,
2723 l_cz_imp_rules.RUN_ID,
2724 l_cz_imp_rules.DISPOSITION,
2725 l_cz_imp_rules.REC_STATUS,
2726 l_cz_imp_rules.FSK_DEVL_PROJECT,
2727 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
2728 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
2729 l_cz_imp_rules.IMPORT_PROG_VERSION,
2730 l_cz_imp_rules.FSK_COMPONENT_ID,
2731 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
2732 );
2733 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2735 G_MODULE||l_api_name,
2736 '170: After Insert into cz_imp_rules' || '='||l_intent);
2737 END IF;
2738
2739 END IF; -- check extn rule exists
2740
2741 END LOOP;
2742 CLOSE csr_intents; -- Insert the Extension Rule for this Intent
2743
2744
2745 -- Standard call to get message count and if count is 1, get message info.
2746 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2747
2748
2749 -- end debug log
2750 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2751 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2752 G_MODULE||l_api_name,
2753 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2754 END IF;
2755
2756 EXCEPTION
2757 WHEN FND_API.G_EXC_ERROR THEN
2758 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2759 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2760 G_MODULE||l_api_name,
2761 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2762 END IF;
2763
2764 x_return_status := G_RET_STS_ERROR ;
2765 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2766
2767 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2768 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2769 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2770 G_MODULE||l_api_name,
2771 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2772 END IF;
2773
2774 x_return_status := G_RET_STS_UNEXP_ERROR ;
2775 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2776
2777 WHEN OTHERS THEN
2778 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2779 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2780 G_MODULE||l_api_name,
2781 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2782 END IF;
2783
2784 x_return_status := G_RET_STS_UNEXP_ERROR ;
2785 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2786 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2787 END IF;
2788 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2789
2790 END attach_extension_rule_tmpl;
2791
2792
2793
2794
2795
2796
2797 ---------------------------------------------------
2798
2799
2800 END OKC_XPRT_IMPORT_RULES_PVT;