[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.9.12020000.2 2012/07/16 16:20:42 harchand ship $ */
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 --LCT Fix
594 /* OPEN csr_rule_outcomes;
595 LOOP FETCH csr_rule_outcomes INTO l_object_type_outcome , l_object_value_id ;
596 CLOSE csr_rule_outcomes;
597
598 IF l_object_type_outcome = 'QUESTION' THEN
599 l_temp_string := ' '||'REQUIRES ALLTRUE'||' '||'(';
600 ELSE
601 l_temp_string := ' '||'IMPLIES ALLTRUE'||' '||'(';
602 END IF;
603
604
605 */
606 --x_stmt_rule:= x_stmt_rule||' '||'IMPLIES ALLTRUE'||' '||'('||l_outcome_stmt||')';
607 -- 8i 9i backward compatibility issue with clob
608 -- l_temp_string := ' '||'IMPLIES ALLTRUE'||' '||'(';
609 l_temp_string := ' '||'REQUIRES ALLTRUE'||' '||'(';
610 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
611
612 DBMS_LOB.APPEND(x_stmt_rule,l_outcome_stmt);
613 DBMS_LOB.FREETEMPORARY(l_outcome_stmt);
614 l_temp_string := ')';
615 DBMS_LOB.WRITEAPPEND(x_stmt_rule,length(l_temp_string) , l_temp_string);
616
617
618 --
619
620
621 -- end debug log
622 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
623 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
624 G_MODULE||l_api_name,
625 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
626 END IF;
627
628 EXCEPTION
629 WHEN FND_API.G_EXC_ERROR THEN
630 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
631 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
632 G_MODULE||l_api_name,
633 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
634 END IF;
635
636 fnd_file.put_line(FND_FILE.LOG,' ');
637 fnd_file.put_line(FND_FILE.LOG,'Error in build_statement_rule '||SQLERRM);
638 fnd_file.put_line(FND_FILE.LOG,' ');
639
640 x_return_status := G_RET_STS_ERROR ;
641 x_msg_data := SQLERRM;
642
643 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
644
645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
646 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
648 G_MODULE||l_api_name,
649 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
650 END IF;
651
652 fnd_file.put_line(FND_FILE.LOG,' ');
653 fnd_file.put_line(FND_FILE.LOG,'Unexpected Error in build_statement_rule '||SQLERRM);
654 fnd_file.put_line(FND_FILE.LOG,' ');
655
656 x_return_status := G_RET_STS_UNEXP_ERROR ;
657 x_msg_data := SQLERRM;
658 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
659
660 WHEN OTHERS THEN
661 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
663 G_MODULE||l_api_name,
664 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
665 END IF;
666
667 fnd_file.put_line(FND_FILE.LOG,' ');
668 fnd_file.put_line(FND_FILE.LOG,'Other Error in build_statement_rule '||SQLERRM);
669 fnd_file.put_line(FND_FILE.LOG,' ');
670
671 x_return_status := G_RET_STS_UNEXP_ERROR ;
672 x_msg_data := SQLERRM;
673 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
674 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
675 END IF;
676 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
677
678
679 END build_statement_rule;
680
681
682 /*====================================================================+
683 Procedure Name : build_and_insert_rule
684 Description : This API calls build_statement_rule to build the rule statement.
685 It then inserts the statement into cz_imp_rules
686 Parameters:
687 p_rule_id - Rule Id
688 p_template_id - Template Id to which the rule is attached
689 p_run_id - Run Id for rules import
690 p_mode - Publish or Disable i.e P or D
691
692 +====================================================================*/
693
694
695 PROCEDURE build_and_insert_rule
696 (
697 p_rule_id IN VARCHAR2,
698 p_template_id IN NUMBER,
699 p_run_id IN NUMBER,
700 p_mode IN VARCHAR2,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_msg_data OUT NOCOPY VARCHAR2,
703 x_msg_count OUT NOCOPY NUMBER
704 ) IS
705
706 CURSOR csr_rule_dtls IS
707 SELECT r.rule_id,
708 r.org_id,
709 r.intent,
710 r.rule_name,
711 r.rule_description,
712 DECODE(p_mode,'P','0','D','1') deleted_flag,
713 r.rule_type
714 FROM okc_xprt_rule_hdrs_all r
715 WHERE r.rule_id = p_rule_id ;
716
717 CURSOR csr_template_dtls IS
718 SELECT template_name,
719 DECODE(parent_template_id, NULL, template_id, parent_template_id)
720 FROM OKC_TERMS_TEMPLATES_ALL
721 WHERE template_id = p_template_id ;
722
723 /*
724 CURSOR csr_template_model_id(p_org_id IN NUMBER,
725 p_intent IN VARCHAR2,
726 p_tmpl_id IN NUMBER) IS
727 */
728 CURSOR csr_template_model_id(p_orig_sys_ref IN VARCHAR2) IS
729 SELECT devl_project_id ,
730 orig_sys_ref
731 FROM cz_devl_projects
732 WHERE orig_sys_ref = p_orig_sys_ref
733 AND devl_project_id = persistent_project_id
734 AND deleted_flag = 0 ;
735
736 -- WHERE orig_sys_ref = G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_tmpl_id
737
738 CURSOR csr_installed_languages IS
739 SELECT L.LANGUAGE_CODE
740 FROM FND_LANGUAGES L
741 WHERE L.INSTALLED_FLAG IN ('I', 'B');
742
743 l_api_name CONSTANT VARCHAR2(30) := 'build_and_insert_rule';
744 l_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
745 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
746 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
747 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
748 l_rule_description okc_xprt_rule_hdrs_all.rule_description%TYPE;
749 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
750 l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
751
752 l_rule_text cz_imp_rules.rule_text%TYPE;
753 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
754
755
756 l_template_name OKC_TERMS_TEMPLATES_ALL.template_name%TYPE;
757 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE;
758
759 l_cz_imp_rules cz_imp_rules%ROWTYPE;
760 l_tmpl_orig_sys_ref cz_devl_projects.orig_sys_ref%TYPE;
761
762 l_model_id cz_devl_projects.devl_project_id%TYPE :=NULL;
763 l_model_osr cz_devl_projects.orig_sys_ref%TYPE := NULL;
764
765
766 BEGIN
767 -- start debug log
768 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
770 G_MODULE||l_api_name,
771 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
772 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
773 G_MODULE||l_api_name,
774 '100: Parameters ');
775 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
776 G_MODULE||l_api_name,
777 '100: p_rule_id : '||p_rule_id);
778 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
779 G_MODULE||l_api_name,
780 '100: p_template_id : '||p_template_id);
781 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
782 G_MODULE||l_api_name,
783 '100: p_run_id : '||p_run_id);
784 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
785 G_MODULE||l_api_name,
786 '100: p_mode : '||p_mode);
787 END IF;
788
789 x_return_status := G_RET_STS_SUCCESS;
790
791 -- Get the Rule Details
792 OPEN csr_rule_dtls;
793 FETCH csr_rule_dtls INTO l_rule_id,
794 l_org_id,
795 l_intent,
796 l_rule_name,
797 l_rule_description,
798 l_deleted_flag,
799 l_rule_type;
800 CLOSE csr_rule_dtls;
801
802 -- Get the Template Details
803 OPEN csr_template_dtls;
804 FETCH csr_template_dtls INTO l_template_name,
805 l_template_id;
806
807 IF csr_template_dtls%NOTFOUND THEN
808 -- debug Log
809 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
810 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
811 G_MODULE||l_api_name,
812 '110: Invalid Template Id: '||p_template_id);
813 END IF;
814 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TEMPLATE');
815 RAISE FND_API.G_EXC_ERROR;
816 END IF;
817
818 CLOSE csr_template_dtls;
819
820 -- build the template OSR
821 l_tmpl_orig_sys_ref := G_TEMPLATE_MODEL_OSR||l_org_id||':'||l_intent||':'||l_template_id;
822
823 -- Get Template Model Details
824 /*
825 OPEN csr_template_model_id(p_org_id => l_org_id,
826 p_intent => l_intent,
827 p_tmpl_id => l_template_id);
828 */
829
830 OPEN csr_template_model_id(p_orig_sys_ref => l_tmpl_orig_sys_ref);
831
832 FETCH csr_template_model_id INTO l_model_id, l_model_osr;
833
834 IF csr_template_model_id%NOTFOUND THEN
835 -- debug Log
836 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
837 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
838 G_MODULE||l_api_name,
839 '110: Template Model Does Not Exists : '||l_template_name);
840 END IF;
841 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TMPL_MODEL');
842 FND_MESSAGE.set_token('TEMPLATE_NAME',l_template_name);
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845
846 CLOSE csr_template_model_id;
847
848 -- debug Log
849 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
850 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
851 G_MODULE||l_api_name,
852 '120: Template Name : '||l_template_name);
853 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
854 G_MODULE||l_api_name,
855 '120: Derived Template Id : '||l_template_id);
856 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
857 G_MODULE||l_api_name,
858 '120: Template Model Id : '||l_model_id);
859 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
860 G_MODULE||l_api_name,
861 '120: Template Model OSR : '||l_model_osr);
862 END IF;
863
864 -- For Rules to be deleted, rule text is NOT required
865 IF l_deleted_flag = '1' THEN
866 -- l_rule_text := NULL;
867 DBMS_LOB.CREATETEMPORARY(l_rule_text, FALSE, DBMS_LOB.CALL);
868 ELSE
869
870
871 -- Generate the Rule Text for this Rule Id
872 build_statement_rule
873 (
874 p_rule_id => l_rule_id,
875 p_template_id => l_template_id,
876 x_stmt_rule => l_rule_text,
877 x_return_status => x_return_status,
878 x_msg_data => x_msg_data,
879 x_msg_count => x_msg_count
880 ) ;
881
882 --- If any errors happen abort API
883 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888
889 END IF; -- rule text generation
890
891 -- Insert Rule Name into cz_imp_localized_texts
892
893 OPEN csr_installed_languages;
894 LOOP
895 FETCH csr_installed_languages INTO l_language;
896 EXIT WHEN csr_installed_languages%NOTFOUND;
897
898 -- Insert into cz_imp_localized_text
899
900 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
901 (
902 LAST_UPDATE_LOGIN,
903 LOCALE_ID,
904 LOCALIZED_STR,
905 INTL_TEXT_ID,
906 CREATION_DATE,
907 LAST_UPDATE_DATE,
908 DELETED_FLAG,
909 EFF_FROM,
910 EFF_TO,
911 CREATED_BY,
912 LAST_UPDATED_BY,
913 SECURITY_MASK,
914 EFF_MASK,
915 CHECKOUT_USER,
916 LANGUAGE,
917 ORIG_SYS_REF,
918 SOURCE_LANG,
919 RUN_ID,
920 REC_STATUS,
921 DISPOSITION,
922 MODEL_ID,
923 FSK_DEVLPROJECT_1_1,
924 MESSAGE,
925 SEEDED_FLAG
926 )
927 VALUES
928 (
929 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
930 NULL, -- LOCALE_ID
931 l_rule_name, --LOCALIZED_STR
932 NULL, -- INTL_TEXT_ID
933 SYSDATE, -- CREATION_DATE
934 SYSDATE, -- LAST_UPDATE_DATE
935 '0', -- DELETED_FLAG
936 NULL, -- EFF_FROM
937 NULL, -- EFF_TO
938 FND_GLOBAL.USER_ID, -- CREATED_BY
939 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
940 NULL, -- SECURITY_MASK
941 NULL, -- EFF_MASK
942 NULL, -- CHECKOUT_USER
943 l_language, --LANGUAGE
944 G_RULE_OSR||l_org_id||':'||l_intent||':'||l_template_id||':'||l_rule_type||':'||l_rule_id, --ORIG_SYS_REF
945 USERENV('LANG'), --SOURCE_LANG
946 p_run_id, -- RUN_ID
947 NULL, -- REC_STATUS
948 NULL, -- DISPOSITION
949 l_model_id, -- MODEL_ID
950 l_model_osr, --FSK_DEVLPROJECT_1_1
951 NULL, -- MESSAGE
952 NULL -- SEEDED_FLAG
953 );
954
955 END LOOP; -- for all installed languages
956 CLOSE csr_installed_languages;
957
958 -- Populate the cz_imp_rules record
959
960 l_cz_imp_rules.RULE_ID := NULL;
961 l_cz_imp_rules.SUB_CONS_ID := NULL;
962 l_cz_imp_rules.REASON_ID := NULL;
963 l_cz_imp_rules.AMOUNT_ID := NULL;
964 l_cz_imp_rules.GRID_ID := NULL;
965 l_cz_imp_rules.RULE_FOLDER_ID := NULL;
966 l_cz_imp_rules.DEVL_PROJECT_ID := l_model_id;
967 l_cz_imp_rules.INVALID_FLAG := NULL;
968 l_cz_imp_rules.DESC_TEXT := l_rule_description;
969 l_cz_imp_rules.NAME := l_rule_name; -- check
970 l_cz_imp_rules.ANTECEDENT_ID := NULL;
971 l_cz_imp_rules.CONSEQUENT_ID := NULL;
972 l_cz_imp_rules.RULE_TYPE := 200; -- Expression Rule
973 l_cz_imp_rules.EXPR_RULE_TYPE := NULL;
974 l_cz_imp_rules.COMPONENT_ID := NULL;
975 l_cz_imp_rules.REASON_TYPE := 0; -- Value in reason_id corresponds to Name of Rule
976 l_cz_imp_rules.DISABLED_FLAG := NULL;
977 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
978 l_cz_imp_rules.CREATION_DATE := SYSDATE;
979 l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
980 l_cz_imp_rules.DELETED_FLAG := l_deleted_flag; -- check
981 l_cz_imp_rules.EFF_FROM := NULL;
982 l_cz_imp_rules.EFF_TO := NULL;
983 l_cz_imp_rules.CREATED_BY := FND_GLOBAL.USER_ID;
984 l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
985 l_cz_imp_rules.SECURITY_MASK := NULL;
986 l_cz_imp_rules.EFF_MASK := NULL;
987 l_cz_imp_rules.CHECKOUT_USER := NULL;
988 l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
989 l_cz_imp_rules.EFFECTIVE_USAGE_MASK := NULL;
990 l_cz_imp_rules.SEQ_NBR := NULL;
991 l_cz_imp_rules.EFFECTIVE_FROM := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
992 l_cz_imp_rules.EFFECTIVE_UNTIL := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
993 l_cz_imp_rules.PERSISTENT_RULE_ID := NULL;
994 l_cz_imp_rules.EFFECTIVITY_SET_ID := NULL;
995 l_cz_imp_rules.RULE_FOLDER_TYPE := NULL;
996 l_cz_imp_rules.UNSATISFIED_MSG_ID := NULL;
997 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE := NULL;
998 l_cz_imp_rules.SIGNATURE_ID := NULL;
999 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG := NULL;
1000 l_cz_imp_rules.PRESENTATION_FLAG := NULL;
1001 l_cz_imp_rules.TEMPLATE_TOKEN := NULL;
1002 l_cz_imp_rules.RULE_TEXT := l_rule_text;
1003 l_cz_imp_rules.NOTES := NULL;
1004 l_cz_imp_rules.CLASS_NAME := NULL;
1005 l_cz_imp_rules.INSTANTIATION_SCOPE := NULL;
1006 l_cz_imp_rules.MODEL_REF_EXPL_ID := NULL;
1007 l_cz_imp_rules.MUTABLE_FLAG := NULL;
1008 l_cz_imp_rules.SEEDED_FLAG := NULL;
1009 l_cz_imp_rules.UI_DEF_ID := NULL;
1010 l_cz_imp_rules.UI_PAGE_ID := NULL;
1011 l_cz_imp_rules.UI_PAGE_ELEMENT_ID := NULL;
1012 l_cz_imp_rules.MESSAGE := NULL;
1013 l_cz_imp_rules.RUN_ID := p_run_id;
1014 l_cz_imp_rules.DISPOSITION := NULL;
1015 l_cz_imp_rules.REC_STATUS := NULL;
1016 l_cz_imp_rules.FSK_DEVL_PROJECT := l_model_osr;
1017 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;
1018 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2 := NULL;
1019 l_cz_imp_rules.IMPORT_PROG_VERSION := NULL;
1020 l_cz_imp_rules.FSK_COMPONENT_ID := NULL;
1021 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID := NULL;
1022
1023 -- Insert into cz_imp_rules
1024
1025 INSERT INTO cz_imp_rules
1026 (
1027 RULE_ID,
1028 SUB_CONS_ID,
1029 REASON_ID,
1030 AMOUNT_ID,
1031 GRID_ID,
1032 RULE_FOLDER_ID,
1033 DEVL_PROJECT_ID,
1034 INVALID_FLAG,
1035 DESC_TEXT,
1036 NAME,
1037 ANTECEDENT_ID,
1038 CONSEQUENT_ID,
1039 RULE_TYPE,
1040 EXPR_RULE_TYPE,
1041 COMPONENT_ID,
1042 REASON_TYPE,
1043 DISABLED_FLAG,
1044 ORIG_SYS_REF,
1045 CREATION_DATE,
1046 LAST_UPDATE_DATE,
1047 DELETED_FLAG,
1048 EFF_FROM,
1049 EFF_TO,
1050 CREATED_BY,
1051 LAST_UPDATED_BY,
1052 SECURITY_MASK,
1053 EFF_MASK,
1054 CHECKOUT_USER,
1055 LAST_UPDATE_LOGIN,
1056 EFFECTIVE_USAGE_MASK,
1057 SEQ_NBR,
1058 EFFECTIVE_FROM,
1059 EFFECTIVE_UNTIL,
1060 PERSISTENT_RULE_ID,
1061 EFFECTIVITY_SET_ID,
1062 RULE_FOLDER_TYPE,
1063 UNSATISFIED_MSG_ID,
1064 UNSATISFIED_MSG_SOURCE,
1065 SIGNATURE_ID,
1066 TEMPLATE_PRIMITIVE_FLAG,
1067 PRESENTATION_FLAG,
1068 TEMPLATE_TOKEN,
1069 RULE_TEXT,
1070 NOTES,
1071 CLASS_NAME,
1072 INSTANTIATION_SCOPE,
1073 MODEL_REF_EXPL_ID,
1074 MUTABLE_FLAG,
1075 SEEDED_FLAG,
1076 UI_DEF_ID,
1077 UI_PAGE_ID,
1078 UI_PAGE_ELEMENT_ID,
1079 MESSAGE,
1080 RUN_ID,
1081 DISPOSITION,
1082 REC_STATUS,
1083 FSK_DEVL_PROJECT,
1084 FSK_LOCALIZED_TEXT_1,
1085 FSK_LOCALIZED_TEXT_2,
1086 IMPORT_PROG_VERSION,
1087 FSK_COMPONENT_ID,
1088 FSK_MODEL_REF_EXPL_ID
1089 )
1090 VALUES
1091 (
1092 l_cz_imp_rules.RULE_ID,
1093 l_cz_imp_rules.SUB_CONS_ID,
1094 l_cz_imp_rules.REASON_ID,
1095 l_cz_imp_rules.AMOUNT_ID,
1096 l_cz_imp_rules.GRID_ID,
1097 l_cz_imp_rules.RULE_FOLDER_ID,
1098 l_cz_imp_rules.DEVL_PROJECT_ID,
1099 l_cz_imp_rules.INVALID_FLAG,
1100 l_cz_imp_rules.DESC_TEXT,
1101 l_cz_imp_rules.NAME,
1102 l_cz_imp_rules.ANTECEDENT_ID,
1103 l_cz_imp_rules.CONSEQUENT_ID,
1104 l_cz_imp_rules.RULE_TYPE,
1105 l_cz_imp_rules.EXPR_RULE_TYPE,
1106 l_cz_imp_rules.COMPONENT_ID,
1107 l_cz_imp_rules.REASON_TYPE,
1108 l_cz_imp_rules.DISABLED_FLAG,
1109 l_cz_imp_rules.ORIG_SYS_REF,
1110 l_cz_imp_rules.CREATION_DATE,
1111 l_cz_imp_rules.LAST_UPDATE_DATE,
1112 l_cz_imp_rules.DELETED_FLAG,
1113 l_cz_imp_rules.EFF_FROM,
1114 l_cz_imp_rules.EFF_TO,
1115 l_cz_imp_rules.CREATED_BY,
1116 l_cz_imp_rules.LAST_UPDATED_BY,
1117 l_cz_imp_rules.SECURITY_MASK,
1118 l_cz_imp_rules.EFF_MASK,
1119 l_cz_imp_rules.CHECKOUT_USER,
1120 l_cz_imp_rules.LAST_UPDATE_LOGIN,
1121 l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
1122 l_cz_imp_rules.SEQ_NBR,
1123 l_cz_imp_rules.EFFECTIVE_FROM,
1124 l_cz_imp_rules.EFFECTIVE_UNTIL,
1125 l_cz_imp_rules.PERSISTENT_RULE_ID,
1126 l_cz_imp_rules.EFFECTIVITY_SET_ID,
1127 l_cz_imp_rules.RULE_FOLDER_TYPE,
1128 l_cz_imp_rules.UNSATISFIED_MSG_ID,
1129 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
1130 l_cz_imp_rules.SIGNATURE_ID,
1131 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
1132 l_cz_imp_rules.PRESENTATION_FLAG,
1133 l_cz_imp_rules.TEMPLATE_TOKEN,
1134 l_cz_imp_rules.RULE_TEXT,
1135 l_cz_imp_rules.NOTES,
1136 l_cz_imp_rules.CLASS_NAME,
1137 l_cz_imp_rules.INSTANTIATION_SCOPE,
1138 l_cz_imp_rules.MODEL_REF_EXPL_ID,
1139 l_cz_imp_rules.MUTABLE_FLAG,
1140 l_cz_imp_rules.SEEDED_FLAG,
1141 l_cz_imp_rules.UI_DEF_ID,
1142 l_cz_imp_rules.UI_PAGE_ID,
1143 l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
1144 l_cz_imp_rules.MESSAGE,
1145 l_cz_imp_rules.RUN_ID,
1146 l_cz_imp_rules.DISPOSITION,
1147 l_cz_imp_rules.REC_STATUS,
1148 l_cz_imp_rules.FSK_DEVL_PROJECT,
1149 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
1150 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
1151 l_cz_imp_rules.IMPORT_PROG_VERSION,
1152 l_cz_imp_rules.FSK_COMPONENT_ID,
1153 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
1154 );
1155
1156
1157
1158
1159 -- Free the CLOB Memory now
1160 DBMS_LOB.FREETEMPORARY(l_rule_text);
1161
1162 -- Standard call to get message count and if count is 1, get message info.
1163 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1164
1165
1166 -- end debug log
1167 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1168 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1169 G_MODULE||l_api_name,
1170 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN FND_API.G_EXC_ERROR THEN
1175 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1177 G_MODULE||l_api_name,
1178 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1179 END IF;
1180
1181 fnd_file.put_line(FND_FILE.LOG,' ');
1182 fnd_file.put_line(FND_FILE.LOG,'Other Error in build_statement_rule '||SQLERRM);
1183 fnd_file.put_line(FND_FILE.LOG,' ');
1184
1185 x_return_status := G_RET_STS_ERROR ;
1186 x_msg_data := SQLERRM;
1187 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1188
1189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1190 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1191 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1192 G_MODULE||l_api_name,
1193 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1194 END IF;
1195
1196 fnd_file.put_line(FND_FILE.LOG,' ');
1197 fnd_file.put_line(FND_FILE.LOG,'Unexpected Error in build_and_insert_rule '||SQLERRM);
1198 fnd_file.put_line(FND_FILE.LOG,' ');
1199
1200 x_return_status := G_RET_STS_UNEXP_ERROR ;
1201 x_msg_data := SQLERRM;
1202 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1203
1204 WHEN OTHERS THEN
1205 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1206 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1207 G_MODULE||l_api_name,
1208 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1209 END IF;
1210
1211 fnd_file.put_line(FND_FILE.LOG,' ');
1212 fnd_file.put_line(FND_FILE.LOG,'Other Error in build_and_insert_rule '||SQLERRM);
1213 fnd_file.put_line(FND_FILE.LOG,' ');
1214
1215 x_return_status := G_RET_STS_UNEXP_ERROR ;
1216 x_msg_data := SQLERRM;
1217 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1218 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1219 END IF;
1220 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1221
1222 END build_and_insert_rule;
1223
1224
1225 /*====================================================================+
1226 Procedure Name : import_rules_publish
1227 Description : This API is called from Publish Rules concurrent program
1228
1229 Parameters:
1230 +====================================================================*/
1231
1232 PROCEDURE import_rules_publish
1233 (
1234 x_run_id OUT NOCOPY NUMBER,
1235 x_return_status OUT NOCOPY VARCHAR2,
1236 x_msg_data OUT NOCOPY VARCHAR2,
1237 x_msg_count OUT NOCOPY NUMBER
1238 ) IS
1239
1240 -- Rules to be Published
1241 CURSOR csr_rules IS
1242 SELECT rule_id,
1243 published_flag
1244 FROM okc_xprt_rule_hdrs_all
1245 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1246
1247 -- Templates Attached to each Rule
1248 CURSOR csr_templates(p_rule_id IN NUMBER) IS
1249 -- Local Rules
1250 SELECT r.template_id
1251 FROM OKC_TERMS_TEMPLATES_ALL t,
1252 okc_xprt_template_rules r
1253 WHERE r.template_id = t.template_id
1254 AND t.status_code IN ('APPROVED','ON_HOLD')
1255 AND t.contract_expert_enabled = 'Y'
1256 AND NVL(r.deleted_flag,'N') = 'N'
1257 AND r.rule_id = p_rule_id
1258 UNION ALL
1259 -- Org Wide Rules
1260 SELECT t.template_id
1261 FROM OKC_TERMS_TEMPLATES_ALL t,
1262 okc_xprt_rule_hdrs_all r
1263 WHERE t.org_id = r.org_id
1264 AND t.intent = r.intent
1265 AND t.contract_expert_enabled = 'Y'
1266 AND t.status_code IN ('APPROVED','ON_HOLD')
1267 AND NVL(r.org_wide_flag,'N') = 'Y'
1268 AND r.rule_id = p_rule_id ;
1269
1270 -- If the Rule was already published to CZ, get the list of templates that were
1271 -- associated to the Rule in last publication
1272 -- From the above list MINUS the templates currently associated to Rule
1273 -- This would give be the list of templates from which rule association is to be deleted
1274
1275 CURSOR csr_templates_to_delete(p_rule_id IN NUMBER) IS
1276 -- Templates already pushed to CZ in previous publication
1277 -- Changed for R12
1278 SELECT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
1279 (INSTR(orig_sys_ref,':',-1,2) - (INSTR(orig_sys_ref,':',-1,3)+1))
1280 )
1281 FROM cz_rules
1282 WHERE SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id)
1283 AND rule_type = 200 -- Added for Bug 5005681
1284 AND rule_id = persistent_rule_id
1285 AND deleted_flag = '0'
1286 MINUS
1287 -- list of templates to which the rule is currently attached
1288 (
1289 -- Local Rules
1290 SELECT to_char(r.template_id)
1291 FROM OKC_TERMS_TEMPLATES_ALL t,
1292 okc_xprt_template_rules r
1293 WHERE r.template_id = t.template_id
1294 AND t.status_code IN ('APPROVED','ON_HOLD')
1295 AND t.contract_expert_enabled = 'Y'
1296 AND NVL(r.deleted_flag,'N') = 'N'
1297 AND r.rule_id = p_rule_id
1298 UNION ALL
1299 -- Org Wide Rules
1300 SELECT to_char(t.template_id)
1301 FROM OKC_TERMS_TEMPLATES_ALL t,
1302 okc_xprt_rule_hdrs_all r
1303 --,okc_xprt_template_rules tr -- Added for Bug 5005681
1304 WHERE t.org_id = r.org_id
1305 --AND tr.template_id = t.template_id -- Added for Bug 5005681
1306 --AND tr.rule_id = r.rule_id -- Added for Bug 5005681
1307 AND t.intent = r.intent
1308 AND t.contract_expert_enabled = 'Y'
1309 AND t.status_code IN ('APPROVED','ON_HOLD')
1310 AND NVL(r.org_wide_flag,'N') = 'Y'
1311 AND r.rule_id = p_rule_id
1312 );
1313
1314 -- Generate Run Id for Rule Import
1315 CURSOR csr_cz_run_id IS
1316 SELECT cz_xfr_run_infos_s.NEXTVAL
1317 FROM dual;
1318
1319
1320 l_api_name CONSTANT VARCHAR2(30) := 'import_rules_publish';
1321 l_rule_id okc_xprt_template_rules.rule_id%TYPE;
1322 l_template_id okc_xprt_template_rules.template_id%TYPE;
1323 l_published_flag okc_xprt_rule_hdrs_all.published_flag%TYPE;
1324
1325
1326 BEGIN
1327 -- start 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 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1332 END IF;
1333
1334 x_return_status := G_RET_STS_SUCCESS;
1335
1336 -- Generate the Run Id
1337 OPEN csr_cz_run_id;
1338 FETCH csr_cz_run_id INTO x_run_id;
1339 CLOSE csr_cz_run_id;
1340
1341 -- debug log
1342 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1343 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1344 G_MODULE||l_api_name,
1345 '110: Rule Import x_run_id : '|| x_run_id);
1346 END IF;
1347
1348
1349 OPEN csr_rules;
1350 LOOP
1351 FETCH csr_rules INTO l_rule_id,l_published_flag;
1352 EXIT WHEN csr_rules%NOTFOUND;
1353 -- For each Rule get all templates currently attached
1354 OPEN csr_templates(p_rule_id => l_rule_id);
1355 LOOP
1356 FETCH csr_templates INTO l_template_id;
1357 EXIT WHEN csr_templates%NOTFOUND;
1358
1359 build_and_insert_rule
1360 (
1361 p_rule_id => l_rule_id,
1362 p_template_id => l_template_id,
1363 p_run_id => x_run_id,
1364 p_mode => 'P', -- Publish
1365 x_return_status => x_return_status,
1366 x_msg_data => x_msg_data,
1367 x_msg_count => x_msg_count
1368 );
1369
1370 --- If any errors happen abort API
1371 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1373 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1374 RAISE FND_API.G_EXC_ERROR;
1375 END IF;
1376
1377
1378 END LOOP; -- Template Csr
1379 CLOSE csr_templates; -- current templates
1380
1381 -- If the Rule was already published, get the difference between current templates
1382 -- and templates that were attached to Rule in the previous CZ publication
1383 IF l_published_flag = 'Y' THEN
1384 OPEN csr_templates_to_delete(p_rule_id => l_rule_id);
1385 LOOP
1386 FETCH csr_templates_to_delete INTO l_template_id;
1387 EXIT WHEN csr_templates_to_delete%NOTFOUND;
1388
1389 build_and_insert_rule
1390 (
1391 p_rule_id => l_rule_id,
1392 p_template_id => l_template_id,
1393 p_run_id => x_run_id,
1394 p_mode => 'D', -- to be deleted
1395 x_return_status => x_return_status,
1396 x_msg_data => x_msg_data,
1397 x_msg_count => x_msg_count
1398 );
1399
1400 --- If any errors happen abort API
1401 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1403 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1404 RAISE FND_API.G_EXC_ERROR;
1405 END IF;
1406
1407
1408 END LOOP; -- csr_templates_to_delete
1409 CLOSE csr_templates_to_delete; -- old templates
1410
1411 END IF; -- l_published_flag = 'Y'
1412
1413
1414
1415 END LOOP; -- Rules Csr
1416 CLOSE csr_rules;
1417
1418
1419 -- Standard call to get message count and if count is 1, get message info.
1420 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1421
1422
1423 -- end debug log
1424 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1425 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1426 G_MODULE||l_api_name,
1427 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1428 END IF;
1429
1430 EXCEPTION
1431 WHEN FND_API.G_EXC_ERROR THEN
1432 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1433 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1434 G_MODULE||l_api_name,
1435 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1436 END IF;
1437
1438 x_return_status := G_RET_STS_ERROR ;
1439 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1440
1441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1444 G_MODULE||l_api_name,
1445 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1446 END IF;
1447
1448 x_return_status := G_RET_STS_UNEXP_ERROR ;
1449 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1450
1451 WHEN OTHERS THEN
1452 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1453 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1454 G_MODULE||l_api_name,
1455 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1456 END IF;
1457
1458 x_return_status := G_RET_STS_UNEXP_ERROR ;
1459 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1460 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1461 END IF;
1462 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1463
1464 END import_rules_publish;
1465
1466 /*====================================================================+
1467 Procedure Name : import_rules_disable
1468 Description : This API is called from Disable Rules concurrent program
1469
1470 Parameters:
1471 +====================================================================*/
1472
1473 PROCEDURE import_rules_disable
1474 (
1475 x_run_id OUT NOCOPY NUMBER,
1476 x_return_status OUT NOCOPY VARCHAR2,
1477 x_msg_data OUT NOCOPY VARCHAR2,
1478 x_msg_count OUT NOCOPY NUMBER
1479 ) IS
1480
1481 -- Rules to be Disabled
1482 CURSOR csr_rules IS
1483 SELECT rule_id
1484 FROM okc_xprt_rule_hdrs_all
1485 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1486
1487 /*
1488 In case of rules to be disabled we look at the template assciation of the rule is CZ
1489 and delete the same
1490 */
1491 CURSOR csr_templates_to_delete(p_rule_id IN NUMBER) IS
1492 -- Templates already pushed to CZ in previous publication
1493 -- Changed for R12
1494 -- Updated the substr for bug 4676800
1495 SELECT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
1496 (INSTR(orig_sys_ref,':',-1,2) - (INSTR(orig_sys_ref,':',-1,3)+1))
1497 )
1498 FROM cz_rules
1499 WHERE SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id)
1500 AND rule_id = persistent_rule_id
1501 AND deleted_flag = '0'
1502 AND rule_type = 200; --Added for perf Bug#5032335
1503
1504 -- Generate Run Id for Rule Import
1505 CURSOR csr_cz_run_id IS
1506 SELECT cz_xfr_run_infos_s.NEXTVAL
1507 FROM dual;
1508
1509
1510 l_api_name CONSTANT VARCHAR2(30) := 'import_rules_disable';
1511 l_rule_id okc_xprt_template_rules.rule_id%TYPE;
1512 l_template_id okc_xprt_template_rules.template_id%TYPE;
1513
1514
1515 BEGIN
1516 -- start 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 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1521 END IF;
1522
1523 x_return_status := G_RET_STS_SUCCESS;
1524
1525 -- Generate the Run Id
1526 OPEN csr_cz_run_id;
1527 FETCH csr_cz_run_id INTO x_run_id;
1528 CLOSE csr_cz_run_id;
1529
1530 -- debug log
1531 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1532 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1533 G_MODULE||l_api_name,
1534 '110: Rule Import x_run_id : '|| x_run_id);
1535 END IF;
1536
1537
1538 OPEN csr_rules;
1539 LOOP
1540 FETCH csr_rules INTO l_rule_id;
1541 EXIT WHEN csr_rules%NOTFOUND;
1542
1543 OPEN csr_templates_to_delete(p_rule_id => l_rule_id);
1544 LOOP
1545 FETCH csr_templates_to_delete INTO l_template_id;
1546 EXIT WHEN csr_templates_to_delete%NOTFOUND;
1547
1548 build_and_insert_rule
1549 (
1550 p_rule_id => l_rule_id,
1551 p_template_id => l_template_id,
1552 p_run_id => x_run_id,
1553 p_mode => 'D', -- to be deleted
1554 x_return_status => x_return_status,
1555 x_msg_data => x_msg_data,
1556 x_msg_count => x_msg_count
1557 );
1558
1559 --- If any errors happen abort API
1560 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1562 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1563 RAISE FND_API.G_EXC_ERROR;
1564 END IF;
1565
1566
1567 END LOOP; -- csr_templates_to_delete
1568 CLOSE csr_templates_to_delete; -- old templates
1569
1570
1571
1572
1573 END LOOP; -- Rules Csr
1574 CLOSE csr_rules;
1575
1576 -- Standard call to get message count and if count is 1, get message info.
1577 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1578
1579
1580 -- end debug log
1581 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1582 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1583 G_MODULE||l_api_name,
1584 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1585 END IF;
1586
1587 EXCEPTION
1588 WHEN FND_API.G_EXC_ERROR THEN
1589 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1591 G_MODULE||l_api_name,
1592 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1593 END IF;
1594
1595 x_return_status := G_RET_STS_ERROR ;
1596 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1597
1598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1599 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1601 G_MODULE||l_api_name,
1602 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1603 END IF;
1604
1605 x_return_status := G_RET_STS_UNEXP_ERROR ;
1606 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1607
1608 WHEN OTHERS THEN
1609 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1610 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1611 G_MODULE||l_api_name,
1612 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1613 END IF;
1614
1615 x_return_status := G_RET_STS_UNEXP_ERROR ;
1616 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1617 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1618 END IF;
1619 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1620
1621 END import_rules_disable;
1622
1623
1624 /*====================================================================+
1625 Procedure Name : import_rule_temp_approval
1626 Description : This API is called from template approval concurrent program
1627
1628 Parameters:
1629 p_template_id - Template Id to be imported
1630 +====================================================================*/
1631
1632 PROCEDURE import_rule_temp_approval
1633 (
1634 p_template_id IN NUMBER,
1635 x_run_id OUT NOCOPY NUMBER,
1636 x_return_status OUT NOCOPY VARCHAR2,
1637 x_msg_data OUT NOCOPY VARCHAR2,
1638 x_msg_count OUT NOCOPY NUMBER
1639 ) IS
1640
1641 CURSOR csr_template_rules IS
1642 -- Local Active Rules for template
1643 SELECT r.rule_id
1644 FROM okc_xprt_template_rules r,
1645 okc_xprt_rule_hdrs_all h
1646 WHERE r.rule_id = h.rule_id
1647 AND h.status_code = 'ACTIVE'
1648 AND NVL(r.deleted_flag,'N') = 'N'
1649 AND r.template_id = p_template_id
1650 UNION ALL
1651 -- Global Active Rules for the template
1652 SELECT r.rule_id
1653 FROM OKC_TERMS_TEMPLATES_ALL t,
1654 okc_xprt_rule_hdrs_all r
1655 WHERE t.org_id = r.org_id
1656 AND t.intent = r.intent
1657 AND NVL(r.org_wide_flag,'N') = 'Y'
1658 AND r.status_code = 'ACTIVE'
1659 AND t.template_id = p_template_id ;
1660
1661 CURSOR csr_parent_tmpl_rules(p_parent_tmpl_id IN NUMBER) IS
1662 -- Local Active Rules on Parent Template to be deleted
1663 -- Delete rules on parent template and not on the revision template
1664 SELECT r.rule_id
1665 FROM okc_xprt_template_rules r,
1666 okc_xprt_rule_hdrs_all h
1667 WHERE r.rule_id = h.rule_id
1668 AND h.status_code = 'ACTIVE'
1669 AND NVL(r.deleted_flag,'N') = 'N'
1670 AND r.template_id = p_parent_tmpl_id
1671 MINUS
1672 -- current local rules on revision template
1673 SELECT r.rule_id
1674 FROM okc_xprt_template_rules r,
1675 okc_xprt_rule_hdrs_all h
1676 WHERE r.rule_id = h.rule_id
1677 AND h.status_code = 'ACTIVE'
1678 AND NVL(r.deleted_flag,'N') = 'N'
1679 AND r.template_id = p_template_id ;
1680
1681 CURSOR csr_template_dtls IS
1682 SELECT parent_template_id
1683 FROM OKC_TERMS_TEMPLATES_ALL
1684 WHERE template_id = p_template_id ;
1685
1686
1687 -- Generate Run Id for Rule Import
1688 CURSOR csr_cz_run_id IS
1689 SELECT cz_xfr_run_infos_s.NEXTVAL
1690 FROM dual;
1691
1692
1693 l_api_name CONSTANT VARCHAR2(30) := 'import_rule_temp_approval';
1694 l_rule_id okc_xprt_template_rules.rule_id%TYPE;
1695 l_parent_template_id OKC_TERMS_TEMPLATES_ALL.parent_template_id%TYPE := NULL;
1696 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE := NULL;
1697
1698
1699
1700 BEGIN
1701 -- start debug log
1702 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1703 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1704 G_MODULE||l_api_name,
1705 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1706 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1707 G_MODULE||l_api_name,
1708 '100: Parameters ');
1709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1710 G_MODULE||l_api_name,
1711 '100: p_template_id : '||p_template_id);
1712 END IF;
1713
1714 x_return_status := G_RET_STS_SUCCESS;
1715
1716 -- Generate the Run Id
1717 OPEN csr_cz_run_id;
1718 FETCH csr_cz_run_id INTO x_run_id;
1719 CLOSE csr_cz_run_id;
1720
1721 -- Get Template Details
1722 -- In case of revision template, parent_template_id IS NOT NULL
1723
1724 OPEN csr_template_dtls;
1725 FETCH csr_template_dtls INTO l_parent_template_id;
1726
1727 IF csr_template_dtls%NOTFOUND THEN
1728 -- Log Error
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: Invalid Template Id: '||p_template_id);
1733 END IF;
1734 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TEMPLATE');
1735 RAISE FND_API.G_EXC_ERROR;
1736 END IF;
1737
1738 CLOSE csr_template_dtls;
1739
1740
1741
1742 -- debug log
1743 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1744 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1745 G_MODULE||l_api_name,
1746 '110: Rule Import x_run_id : '|| x_run_id);
1747 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1748 G_MODULE||l_api_name,
1749 '110: p_template_id : '|| p_template_id);
1750 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1751 G_MODULE||l_api_name,
1752 '110: l_parent_template_id '|| l_parent_template_id);
1753 END IF;
1754
1755 IF l_parent_template_id IS NULL THEN
1756 -- Not a Revision Template
1757 l_template_id := p_template_id;
1758 ELSE
1759 -- Revision Template
1760 l_template_id := l_parent_template_id;
1761 END IF;
1762
1763 OPEN csr_template_rules;
1764 LOOP
1765 FETCH csr_template_rules INTO l_rule_id;
1766 EXIT WHEN csr_template_rules%NOTFOUND;
1767
1768 build_and_insert_rule
1769 (
1770 p_rule_id => l_rule_id,
1771 p_template_id => l_template_id,
1772 p_run_id => x_run_id,
1773 p_mode => 'P',
1774 x_return_status => x_return_status,
1775 x_msg_data => x_msg_data,
1776 x_msg_count => x_msg_count
1777 );
1778
1779 END LOOP;
1780 CLOSE csr_template_rules;
1781
1782 -- In Case of Revision templates, delete Active Rules on Parent Template
1783 IF l_parent_template_id IS NOT NULL THEN
1784 OPEN csr_parent_tmpl_rules(p_parent_tmpl_id => l_parent_template_id);
1785 LOOP
1786 FETCH csr_parent_tmpl_rules INTO l_rule_id;
1787 EXIT WHEN csr_parent_tmpl_rules%NOTFOUND;
1788
1789 build_and_insert_rule
1790 (
1791 p_rule_id => l_rule_id,
1792 p_template_id => l_template_id,
1793 p_run_id => x_run_id,
1794 p_mode => 'D',
1795 x_return_status => x_return_status,
1796 x_msg_data => x_msg_data,
1797 x_msg_count => x_msg_count
1798 );
1799
1800 END LOOP;
1801 CLOSE csr_parent_tmpl_rules;
1802
1803 END IF; -- revision template
1804
1805
1806
1807 -- Standard call to get message count and if count is 1, get message info.
1808 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1809
1810
1811 -- end debug log
1812 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1813 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1814 G_MODULE||l_api_name,
1815 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1816 END IF;
1817
1818 EXCEPTION
1819 WHEN FND_API.G_EXC_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 '2000: 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,'Error in import_rule_temp_approval '||SQLERRM);
1828 fnd_file.put_line(FND_FILE.LOG,' ');
1829
1830 x_return_status := G_RET_STS_ERROR ;
1831 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1832
1833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR 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 '3000: 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,'Unexpected 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 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1846
1847 WHEN OTHERS THEN
1848 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1849 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1850 G_MODULE||l_api_name,
1851 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1852 END IF;
1853
1854 fnd_file.put_line(FND_FILE.LOG,' ');
1855 fnd_file.put_line(FND_FILE.LOG,'Other Error in import_rule_temp_approval '||SQLERRM);
1856 fnd_file.put_line(FND_FILE.LOG,' ');
1857
1858 x_return_status := G_RET_STS_UNEXP_ERROR ;
1859 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1860 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1861 END IF;
1862 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1863
1864 END import_rule_temp_approval;
1865
1866 ---------------------------------------------------
1867 FUNCTION check_extension_rule
1868 (
1869 p_intent IN VARCHAR2,
1870 p_org_id IN NUMBER
1871 )
1872 RETURN VARCHAR2 IS
1873
1874 CURSOR csr_check_var_model IS
1875 SELECT devl_project_id
1876 FROM cz_devl_projects
1877 WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||p_intent
1878 AND devl_project_id = persistent_project_id
1879 AND deleted_flag = 0;
1880
1881 --Added for R12
1882 CURSOR csr_check_cla_model IS
1883 SELECT devl_project_id
1884 FROM cz_devl_projects
1885 WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||p_org_id||':'||p_intent
1886 AND devl_project_id = persistent_project_id
1887 AND deleted_flag = 0;
1888
1889 CURSOR csr_check_ext_rule (p_devl_project_id NUMBER) IS
1890 SELECT 'X'
1891 FROM cz_rules
1892 WHERE rule_type = 300 -- Extension rule
1893 AND devl_project_id = p_devl_project_id
1894 AND rule_id = persistent_rule_id
1895 AND deleted_flag = 0;
1896
1897 l_devl_project_id NUMBER;
1898 l_exists VARCHAR2(1);
1899
1900 BEGIN
1901
1902 OPEN csr_check_cla_model;
1903 FETCH csr_check_cla_model INTO l_devl_project_id;
1904 IF csr_check_cla_model%FOUND THEN
1905 OPEN csr_check_ext_rule(l_devl_project_id);
1906 FETCH csr_check_ext_rule INTO l_exists;
1907 IF csr_check_ext_rule%FOUND THEN
1908 RETURN FND_API.G_TRUE;
1909 ELSE
1910 RETURN FND_API.G_FALSE;
1911 END IF;
1912 CLOSE csr_check_ext_rule;
1913 ELSE
1914 RETURN FND_API.G_FALSE;
1915 END IF;
1916 CLOSE csr_check_cla_model;
1917
1918 EXCEPTION
1919 WHEN OTHERS THEN
1920 IF csr_check_cla_model%ISOPEN THEN
1921 CLOSE csr_check_cla_model;
1922 END IF;
1923 IF csr_check_cla_model%ISOPEN THEN
1924 CLOSE csr_check_cla_model;
1925 END IF;
1926
1927 END check_extension_rule;
1928
1929 ---------------------------------------------------
1930
1931 PROCEDURE attach_extension_rule
1932 (
1933 p_api_version IN NUMBER,
1934 p_init_msg_list IN VARCHAR2,
1935 p_run_id IN NUMBER,
1936 x_return_status OUT NOCOPY VARCHAR2,
1937 x_msg_data OUT NOCOPY VARCHAR2,
1938 x_msg_count OUT NOCOPY NUMBER
1939 ) IS
1940
1941 -- Added Org_id to the cusor for R12
1942 CURSOR csr_intents IS
1943 SELECT distinct intent, org_id
1944 FROM okc_xprt_rule_hdrs_all r
1945 WHERE r.request_id = fnd_global.conc_request_id;
1946
1947 CURSOR csr_get_var_model_dtl (l_intent VARCHAR2) IS
1948 SELECT devl_project_id, orig_sys_ref
1949 FROM cz_devl_projects
1950 WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||l_intent
1951 AND devl_project_id = persistent_project_id
1952 AND deleted_flag = 0;
1953
1954 -- Added new cursor for attaching extension rule to Clause model from R12
1955 CURSOR csr_get_cla_model_dtl (l_org_id NUMBER, l_intent VARCHAR2) IS
1956 SELECT devl_project_id, orig_sys_ref
1957 FROM cz_devl_projects
1958 WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||to_char(l_org_id)||':'||l_intent
1959 AND devl_project_id = persistent_project_id
1960 AND deleted_flag = 0;
1961
1962 CURSOR csr_installed_languages IS
1963 SELECT L.LANGUAGE_CODE
1964 FROM FND_LANGUAGES L
1965 WHERE L.INSTALLED_FLAG IN ('I', 'B');
1966
1967 l_api_name CONSTANT VARCHAR2(30) := 'attach_extension_rule';
1968 l_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
1969 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
1970 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
1971 l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
1972 l_seq_nbr cz_imp_rules.seq_nbr%TYPE := 0;
1973 l_rule_name cz_imp_rules.NAME%TYPE;
1974 l_rule_description cz_imp_rules.desc_text%TYPE;
1975 l_rule_text cz_imp_rules.rule_text%TYPE;
1976 l_model_id cz_imp_localized_texts.MODEL_ID%TYPE;
1977 l_model_osr cz_imp_localized_texts.fsk_devlproject_1_1%TYPE;
1978
1979 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
1980
1981 l_cz_imp_rules cz_imp_rules%ROWTYPE;
1982
1983 BEGIN
1984 -- start debug log
1985 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1986 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1987 G_MODULE||l_api_name,
1988 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1989 END IF;
1990
1991 x_return_status := G_RET_STS_SUCCESS;
1992
1993 -- Set Extension rule name
1994 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_NAME');
1995 l_rule_name := FND_MESSAGE.get; -- Get Extension rule name from OKC_XPRT_XTN_RULE_NAME message
1996
1997 -- Set Extension rule description
1998 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_DESC');
1999 l_rule_description := FND_MESSAGE.get; -- Get Extension rule description from OKC_XPRT_XTN_RULE_DESC message
2000
2001
2002 OPEN csr_intents;
2003 LOOP
2004 FETCH csr_intents INTO l_intent,l_org_id;
2005 EXIT WHEN csr_intents%NOTFOUND;
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 '110: l_intent ' || '='||l_intent);
2011 END IF;
2012
2013 -- Modified to attach extension rule to Clause model from R12
2014 OPEN csr_get_cla_model_dtl(l_org_id,l_intent);
2015 FETCH csr_get_cla_model_dtl INTO l_model_id, l_model_osr;
2016 IF csr_get_cla_model_dtl%NOTFOUND THEN
2017 RAISE FND_API.G_EXC_ERROR;
2018 END IF;
2019 CLOSE csr_get_cla_model_dtl;
2020
2021 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2022 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2023 G_MODULE||l_api_name,
2024 '120: l_model_id ' || '='||l_model_id);
2025 END IF;
2026
2027 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2029 G_MODULE||l_api_name,
2030 '130: l_model_osr ' || '='||l_model_osr);
2031 END IF;
2032
2033 -- Generate the Rule Text for this Run Id if the rule doesn't exist/has been deleted/disabled
2034 -- Check_extension rule changed in R12 to check against Clause model. Added new parameter l_org_id
2035 IF (check_extension_rule(l_intent, l_org_id) = FND_API.G_FALSE) THEN
2036
2037 fnd_file.put_line(FND_FILE.LOG,' ');
2038 fnd_file.put_line(FND_FILE.LOG,'Creating Extension Rule for : '||l_model_osr);
2039 fnd_file.put_line(FND_FILE.LOG,' ');
2040
2041 -- Insert Rule Name into cz_imp_localized_texts
2042
2043 OPEN csr_installed_languages;
2044 LOOP
2045 FETCH csr_installed_languages INTO l_language;
2046 EXIT WHEN csr_installed_languages%NOTFOUND;
2047
2048 -- Insert into cz_imp_localized_text
2049 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2050 (
2051 LAST_UPDATE_LOGIN,
2052 LOCALE_ID,
2053 LOCALIZED_STR,
2054 INTL_TEXT_ID,
2055 CREATION_DATE,
2056 LAST_UPDATE_DATE,
2057 DELETED_FLAG,
2058 EFF_FROM,
2059 EFF_TO,
2060 CREATED_BY,
2061 LAST_UPDATED_BY,
2062 SECURITY_MASK,
2063 EFF_MASK,
2064 CHECKOUT_USER,
2065 LANGUAGE,
2066 ORIG_SYS_REF,
2067 SOURCE_LANG,
2068 RUN_ID,
2069 REC_STATUS,
2070 DISPOSITION,
2071 MODEL_ID,
2072 FSK_DEVLPROJECT_1_1,
2073 MESSAGE,
2074 SEEDED_FLAG
2075 )
2076 VALUES
2077 (
2078 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
2079 NULL, -- LOCALE_ID
2080 l_rule_name, --LOCALIZED_STR -
2081 NULL, -- INTL_TEXT_ID
2082 SYSDATE, -- CREATION_DATE
2083 SYSDATE, -- LAST_UPDATE_DATE
2084 '0', -- DELETED_FLAG
2085 NULL, -- EFF_FROM
2086 NULL, -- EFF_TO
2087 FND_GLOBAL.USER_ID, -- CREATED_BY
2088 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
2089 NULL, -- SECURITY_MASK
2090 NULL, -- EFF_MASK
2091 NULL, -- CHECKOUT_USER
2092 l_language, --LANGUAGE
2093 G_XTN_RULE_OSR||l_intent, --ORIG_SYS_REF
2094 USERENV('LANG'), --SOURCE_LANG
2095 p_run_id, -- RUN_ID
2096 NULL, -- REC_STATUS
2097 NULL, -- DISPOSITION
2098 l_model_id, -- MODEL_ID
2099 l_model_osr, --FSK_DEVLPROJECT_1_1
2100 NULL, -- MESSAGE
2101 NULL -- SEEDED_FLAG
2102 );
2103
2104 END LOOP;
2105 CLOSE csr_installed_languages;
2106
2107
2108 -- Populate the cz_imp_rules record
2109 l_seq_nbr := l_seq_nbr + 1;
2110
2111 l_cz_imp_rules.RULE_ID := NULL;
2112 l_cz_imp_rules.SUB_CONS_ID := NULL;
2113 l_cz_imp_rules.REASON_ID := NULL;
2114 l_cz_imp_rules.AMOUNT_ID := NULL;
2115 l_cz_imp_rules.GRID_ID := NULL;
2116 l_cz_imp_rules.RULE_FOLDER_ID := NULL;
2117 l_cz_imp_rules.DEVL_PROJECT_ID := l_model_id;
2118 l_cz_imp_rules.INVALID_FLAG := '0'; --Default value for valid rule
2119 l_cz_imp_rules.DESC_TEXT := l_rule_description;
2120 l_cz_imp_rules.NAME := l_rule_name; -- check
2121 l_cz_imp_rules.ANTECEDENT_ID := NULL;
2122 l_cz_imp_rules.CONSEQUENT_ID := NULL;
2123 l_cz_imp_rules.RULE_TYPE := 300; -- Extension Rule
2124 l_cz_imp_rules.EXPR_RULE_TYPE := NULL; --1; --Needed for Extension rule
2125 l_cz_imp_rules.COMPONENT_ID := NULL;
2126 l_cz_imp_rules.REASON_TYPE := 0; -- Value in reason_id corresponds to Name of Rule
2127 l_cz_imp_rules.DISABLED_FLAG := '0'; -- Indicates enabled rule
2128 l_cz_imp_rules.ORIG_SYS_REF := G_XTN_RULE_OSR||l_intent;
2129 l_cz_imp_rules.CREATION_DATE := SYSDATE;
2130 l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
2131 l_cz_imp_rules.DELETED_FLAG := 0; -- check
2132 l_cz_imp_rules.EFF_FROM := NULL;
2133 l_cz_imp_rules.EFF_TO := NULL;
2134 l_cz_imp_rules.CREATED_BY := FND_GLOBAL.USER_ID;
2135 l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2136 l_cz_imp_rules.SECURITY_MASK := NULL;
2137 l_cz_imp_rules.EFF_MASK := NULL;
2138 l_cz_imp_rules.CHECKOUT_USER := NULL;
2139 l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2140 l_cz_imp_rules.EFFECTIVE_USAGE_MASK := NULL;
2141 l_cz_imp_rules.SEQ_NBR := l_seq_nbr;
2142 l_cz_imp_rules.EFFECTIVE_FROM := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
2143 l_cz_imp_rules.EFFECTIVE_UNTIL := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
2144 l_cz_imp_rules.PERSISTENT_RULE_ID := NULL;
2145 l_cz_imp_rules.EFFECTIVITY_SET_ID := NULL;
2146 l_cz_imp_rules.RULE_FOLDER_TYPE := NULL;
2147 l_cz_imp_rules.UNSATISFIED_MSG_ID := NULL;
2148 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE := '0'; -- Changed from NULL
2149 l_cz_imp_rules.SIGNATURE_ID := NULL;
2150 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG := NULL;
2151 l_cz_imp_rules.PRESENTATION_FLAG := NULL; --'0'; --Default value
2152 l_cz_imp_rules.TEMPLATE_TOKEN := NULL;
2153 -- l_cz_imp_rules.RULE_TEXT := G_XTN_RULE_TEXT;
2154 l_cz_imp_rules.NOTES := NULL;
2155 l_cz_imp_rules.CLASS_NAME := G_XTN_RULE_CLASS_NAME;
2156 l_cz_imp_rules.INSTANTIATION_SCOPE := 1; -- Needed for Extension rule
2157 l_cz_imp_rules.MODEL_REF_EXPL_ID := NULL;
2158 l_cz_imp_rules.MUTABLE_FLAG := '0'; --Default value
2159 l_cz_imp_rules.SEEDED_FLAG := NULL; --Seeded CX rule
2160 l_cz_imp_rules.UI_DEF_ID := NULL;
2161 l_cz_imp_rules.UI_PAGE_ID := NULL;
2162 l_cz_imp_rules.UI_PAGE_ELEMENT_ID := NULL;
2163 l_cz_imp_rules.MESSAGE := NULL;
2164 l_cz_imp_rules.RUN_ID := p_run_id; -- Input runid
2165 l_cz_imp_rules.DISPOSITION := NULL;
2166 l_cz_imp_rules.REC_STATUS := NULL;
2167 l_cz_imp_rules.FSK_DEVL_PROJECT := l_model_osr;
2168 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1 := G_XTN_RULE_OSR||l_intent;
2169 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2 := NULL;
2170 l_cz_imp_rules.IMPORT_PROG_VERSION := NULL;
2171 l_cz_imp_rules.FSK_COMPONENT_ID := NULL;
2172 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID := NULL;
2173
2174
2175 -- Insert into cz_imp_rules
2176
2177 INSERT INTO cz_imp_rules
2178 (
2179 RULE_ID,
2180 SUB_CONS_ID,
2181 REASON_ID,
2182 AMOUNT_ID,
2183 GRID_ID,
2184 RULE_FOLDER_ID,
2185 DEVL_PROJECT_ID,
2186 INVALID_FLAG,
2187 DESC_TEXT,
2188 NAME,
2189 ANTECEDENT_ID,
2190 CONSEQUENT_ID,
2191 RULE_TYPE,
2192 EXPR_RULE_TYPE,
2193 COMPONENT_ID,
2194 REASON_TYPE,
2195 DISABLED_FLAG,
2196 ORIG_SYS_REF,
2197 CREATION_DATE,
2198 LAST_UPDATE_DATE,
2199 DELETED_FLAG,
2200 EFF_FROM,
2201 EFF_TO,
2202 CREATED_BY,
2203 LAST_UPDATED_BY,
2204 SECURITY_MASK,
2205 EFF_MASK,
2206 CHECKOUT_USER,
2207 LAST_UPDATE_LOGIN,
2208 EFFECTIVE_USAGE_MASK,
2209 SEQ_NBR,
2210 EFFECTIVE_FROM,
2211 EFFECTIVE_UNTIL,
2212 PERSISTENT_RULE_ID,
2213 EFFECTIVITY_SET_ID,
2214 RULE_FOLDER_TYPE,
2215 UNSATISFIED_MSG_ID,
2216 UNSATISFIED_MSG_SOURCE,
2217 SIGNATURE_ID,
2218 TEMPLATE_PRIMITIVE_FLAG,
2219 PRESENTATION_FLAG,
2220 TEMPLATE_TOKEN,
2221 RULE_TEXT,
2222 NOTES,
2223 CLASS_NAME,
2224 INSTANTIATION_SCOPE,
2225 MODEL_REF_EXPL_ID,
2226 MUTABLE_FLAG,
2227 SEEDED_FLAG,
2228 UI_DEF_ID,
2229 UI_PAGE_ID,
2230 UI_PAGE_ELEMENT_ID,
2231 MESSAGE,
2232 RUN_ID,
2233 DISPOSITION,
2234 REC_STATUS,
2235 FSK_DEVL_PROJECT,
2236 FSK_LOCALIZED_TEXT_1,
2237 FSK_LOCALIZED_TEXT_2,
2238 IMPORT_PROG_VERSION,
2239 FSK_COMPONENT_ID,
2240 FSK_MODEL_REF_EXPL_ID
2241 )
2242 VALUES
2243 (
2244 l_cz_imp_rules.RULE_ID,
2245 l_cz_imp_rules.SUB_CONS_ID,
2246 l_cz_imp_rules.REASON_ID,
2247 l_cz_imp_rules.AMOUNT_ID,
2248 l_cz_imp_rules.GRID_ID,
2249 l_cz_imp_rules.RULE_FOLDER_ID,
2250 l_cz_imp_rules.DEVL_PROJECT_ID,
2251 l_cz_imp_rules.INVALID_FLAG,
2252 l_cz_imp_rules.DESC_TEXT,
2253 l_cz_imp_rules.NAME,
2254 l_cz_imp_rules.ANTECEDENT_ID,
2255 l_cz_imp_rules.CONSEQUENT_ID,
2256 l_cz_imp_rules.RULE_TYPE,
2257 l_cz_imp_rules.EXPR_RULE_TYPE,
2258 l_cz_imp_rules.COMPONENT_ID,
2259 l_cz_imp_rules.REASON_TYPE,
2260 l_cz_imp_rules.DISABLED_FLAG,
2261 l_cz_imp_rules.ORIG_SYS_REF,
2262 l_cz_imp_rules.CREATION_DATE,
2263 l_cz_imp_rules.LAST_UPDATE_DATE,
2264 l_cz_imp_rules.DELETED_FLAG,
2265 l_cz_imp_rules.EFF_FROM,
2266 l_cz_imp_rules.EFF_TO,
2267 l_cz_imp_rules.CREATED_BY,
2268 l_cz_imp_rules.LAST_UPDATED_BY,
2269 l_cz_imp_rules.SECURITY_MASK,
2270 l_cz_imp_rules.EFF_MASK,
2271 l_cz_imp_rules.CHECKOUT_USER,
2272 l_cz_imp_rules.LAST_UPDATE_LOGIN,
2273 l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
2274 l_cz_imp_rules.SEQ_NBR,
2275 l_cz_imp_rules.EFFECTIVE_FROM,
2276 l_cz_imp_rules.EFFECTIVE_UNTIL,
2277 l_cz_imp_rules.PERSISTENT_RULE_ID,
2278 l_cz_imp_rules.EFFECTIVITY_SET_ID,
2279 l_cz_imp_rules.RULE_FOLDER_TYPE,
2280 l_cz_imp_rules.UNSATISFIED_MSG_ID,
2281 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
2282 l_cz_imp_rules.SIGNATURE_ID,
2283 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
2284 l_cz_imp_rules.PRESENTATION_FLAG,
2285 l_cz_imp_rules.TEMPLATE_TOKEN,
2286 --l_cz_imp_rules.RULE_TEXT,
2287 G_XTN_RULE_TEXT,
2288 l_cz_imp_rules.NOTES,
2289 l_cz_imp_rules.CLASS_NAME,
2290 l_cz_imp_rules.INSTANTIATION_SCOPE,
2291 l_cz_imp_rules.MODEL_REF_EXPL_ID,
2292 l_cz_imp_rules.MUTABLE_FLAG,
2293 l_cz_imp_rules.SEEDED_FLAG,
2294 l_cz_imp_rules.UI_DEF_ID,
2295 l_cz_imp_rules.UI_PAGE_ID,
2296 l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
2297 l_cz_imp_rules.MESSAGE,
2298 l_cz_imp_rules.RUN_ID,
2299 l_cz_imp_rules.DISPOSITION,
2300 l_cz_imp_rules.REC_STATUS,
2301 l_cz_imp_rules.FSK_DEVL_PROJECT,
2302 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
2303 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
2304 l_cz_imp_rules.IMPORT_PROG_VERSION,
2305 l_cz_imp_rules.FSK_COMPONENT_ID,
2306 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
2307 );
2308 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2309 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2310 G_MODULE||l_api_name,
2311 '170: After Insert into cz_imp_rules' || '='||l_intent);
2312 END IF;
2313
2314 END IF; -- check extn rule exists
2315
2316 END LOOP;
2317 CLOSE csr_intents; -- Insert the Extension Rule for this Intent
2318
2319
2320 -- Standard call to get message count and if count is 1, get message info.
2321 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2322
2323
2324 -- end debug log
2325 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2326 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2327 G_MODULE||l_api_name,
2328 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2329 END IF;
2330
2331 EXCEPTION
2332 WHEN FND_API.G_EXC_ERROR THEN
2333 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2334 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2335 G_MODULE||l_api_name,
2336 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2337 END IF;
2338
2339 x_return_status := G_RET_STS_ERROR ;
2340 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2341
2342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2343 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2344 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2345 G_MODULE||l_api_name,
2346 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2347 END IF;
2348
2349 x_return_status := G_RET_STS_UNEXP_ERROR ;
2350 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2351
2352 WHEN OTHERS THEN
2353 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2354 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2355 G_MODULE||l_api_name,
2356 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2357 END IF;
2358
2359 x_return_status := G_RET_STS_UNEXP_ERROR ;
2360 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2361 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2362 END IF;
2363 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2364
2365 END attach_extension_rule;
2366
2367
2368 ---------------------------------------------------
2369 PROCEDURE attach_extension_rule_tmpl
2370 (
2371 p_api_version IN NUMBER,
2372 p_init_msg_list IN VARCHAR2,
2373 p_run_id IN NUMBER,
2374 p_template_id IN NUMBER,
2375 x_return_status OUT NOCOPY VARCHAR2,
2376 x_msg_data OUT NOCOPY VARCHAR2,
2377 x_msg_count OUT NOCOPY NUMBER
2378 ) IS
2379
2380 -- Added Org_id to the cusor for R12
2381 CURSOR csr_intents IS
2382 SELECT intent,org_id
2383 FROM OKC_TERMS_TEMPLATES_ALL t
2384 WHERE t.template_id = p_template_id;
2385
2386 CURSOR csr_get_var_model_dtl (l_intent VARCHAR2) IS
2387 SELECT devl_project_id, orig_sys_ref
2388 FROM cz_devl_projects
2389 WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||l_intent
2390 AND devl_project_id = persistent_project_id
2391 AND deleted_flag = 0;
2392
2393 -- Added new cursor for attaching extension rule to Clause model from R12
2394 CURSOR csr_get_cla_model_dtl (l_org_id NUMBER, l_intent VARCHAR2) IS
2395 SELECT devl_project_id, orig_sys_ref
2396 FROM cz_devl_projects
2397 WHERE orig_sys_ref = G_CLAUSE_MODEL_OSR||l_org_id||':'||l_intent
2398 AND devl_project_id = persistent_project_id
2399 AND deleted_flag = 0;
2400
2401 CURSOR csr_installed_languages IS
2402 SELECT L.LANGUAGE_CODE
2403 FROM FND_LANGUAGES L
2404 WHERE L.INSTALLED_FLAG IN ('I', 'B');
2405
2406 l_api_name CONSTANT VARCHAR2(30) := 'attach_extension_rule_tmpl';
2407 l_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
2408 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
2409 l_intent okc_xprt_rule_hdrs_all.intent%TYPE;
2410 l_deleted_flag cz_imp_rules.deleted_flag%TYPE;
2411 l_seq_nbr cz_imp_rules.seq_nbr%TYPE := 0;
2412 l_rule_name cz_imp_rules.NAME%TYPE;
2413 l_rule_description cz_imp_rules.desc_text%TYPE;
2414 l_rule_text cz_imp_rules.rule_text%TYPE;
2415 l_model_id cz_imp_localized_texts.MODEL_ID%TYPE;
2416 l_model_osr cz_imp_localized_texts.fsk_devlproject_1_1%TYPE;
2417
2418 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
2419
2420 l_cz_imp_rules cz_imp_rules%ROWTYPE;
2421
2422 BEGIN
2423 -- start debug log
2424 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2425 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2426 G_MODULE||l_api_name,
2427 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2428 END IF;
2429
2430 x_return_status := G_RET_STS_SUCCESS;
2431
2432 -- Set Extension rule name
2433 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_NAME');
2434 l_rule_name := FND_MESSAGE.get; -- Get Extension rule name from OKC_XPRT_XTN_RULE_NAME message
2435
2436 -- Set Extension rule description
2437 FND_MESSAGE.set_name('OKC','OKC_XPRT_XTN_RULE_DESC');
2438 l_rule_description := FND_MESSAGE.get; -- Get Extension rule description from OKC_XPRT_XTN_RULE_DESC message
2439
2440
2441 OPEN csr_intents;
2442 LOOP
2443 FETCH csr_intents INTO l_intent,l_org_id;
2444 EXIT WHEN csr_intents%NOTFOUND;
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 '110: l_intent ' || '='||l_intent);
2450 END IF;
2451
2452 -- Modified to attach extension rule to Clause model from R12
2453 OPEN csr_get_cla_model_dtl(l_intent,l_org_id);
2454 FETCH csr_get_cla_model_dtl INTO l_model_id, l_model_osr;
2455 IF csr_get_cla_model_dtl%NOTFOUND THEN
2456 RAISE FND_API.G_EXC_ERROR;
2457 END IF;
2458 CLOSE csr_get_cla_model_dtl;
2459
2460 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2461 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2462 G_MODULE||l_api_name,
2463 '120: l_model_id ' || '='||l_model_id);
2464 END IF;
2465
2466 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2467 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2468 G_MODULE||l_api_name,
2469 '130: l_model_osr ' || '='||l_model_osr);
2470 END IF;
2471
2472 -- Generate the Rule Text for this Run Id if the rule doesn't exist/has been deleted/disabled
2473 -- Check_extension rule changed in R12 to check against Clause model. Added new parameter l_org_id
2474 IF (check_extension_rule(l_intent, l_org_id) = FND_API.G_FALSE) THEN
2475
2476 fnd_file.put_line(FND_FILE.LOG,' ');
2477 fnd_file.put_line(FND_FILE.LOG,'Creating Extension Rule for : '||l_model_osr);
2478 fnd_file.put_line(FND_FILE.LOG,' ');
2479
2480 -- Insert Rule Name into cz_imp_localized_texts
2481
2482 OPEN csr_installed_languages;
2483 LOOP
2484 FETCH csr_installed_languages INTO l_language;
2485 EXIT WHEN csr_installed_languages%NOTFOUND;
2486
2487 -- Insert into cz_imp_localized_text
2488 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2489 (
2490 LAST_UPDATE_LOGIN,
2491 LOCALE_ID,
2492 LOCALIZED_STR,
2493 INTL_TEXT_ID,
2494 CREATION_DATE,
2495 LAST_UPDATE_DATE,
2496 DELETED_FLAG,
2497 EFF_FROM,
2498 EFF_TO,
2499 CREATED_BY,
2500 LAST_UPDATED_BY,
2501 SECURITY_MASK,
2502 EFF_MASK,
2503 CHECKOUT_USER,
2504 LANGUAGE,
2505 ORIG_SYS_REF,
2506 SOURCE_LANG,
2507 RUN_ID,
2508 REC_STATUS,
2509 DISPOSITION,
2510 MODEL_ID,
2511 FSK_DEVLPROJECT_1_1,
2512 MESSAGE,
2513 SEEDED_FLAG
2514 )
2515 VALUES
2516 (
2517 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
2518 NULL, -- LOCALE_ID
2519 l_rule_name, --LOCALIZED_STR -
2520 NULL, -- INTL_TEXT_ID
2521 SYSDATE, -- CREATION_DATE
2522 SYSDATE, -- LAST_UPDATE_DATE
2523 '0', -- DELETED_FLAG
2524 NULL, -- EFF_FROM
2525 NULL, -- EFF_TO
2526 FND_GLOBAL.USER_ID, -- CREATED_BY
2527 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
2528 NULL, -- SECURITY_MASK
2529 NULL, -- EFF_MASK
2530 NULL, -- CHECKOUT_USER
2531 l_language, --LANGUAGE
2532 G_XTN_RULE_OSR||l_intent, --ORIG_SYS_REF
2533 USERENV('LANG'), --SOURCE_LANG
2534 p_run_id, -- RUN_ID
2535 NULL, -- REC_STATUS
2536 NULL, -- DISPOSITION
2537 l_model_id, -- MODEL_ID
2538 l_model_osr, --FSK_DEVLPROJECT_1_1
2539 NULL, -- MESSAGE
2540 NULL -- SEEDED_FLAG
2541 );
2542
2543 END LOOP;
2544 CLOSE csr_installed_languages;
2545
2546
2547 -- Populate the cz_imp_rules record
2548 l_seq_nbr := l_seq_nbr + 1;
2549
2550 l_cz_imp_rules.RULE_ID := NULL;
2551 l_cz_imp_rules.SUB_CONS_ID := NULL;
2552 l_cz_imp_rules.REASON_ID := NULL;
2553 l_cz_imp_rules.AMOUNT_ID := NULL;
2554 l_cz_imp_rules.GRID_ID := NULL;
2555 l_cz_imp_rules.RULE_FOLDER_ID := NULL;
2556 l_cz_imp_rules.DEVL_PROJECT_ID := l_model_id;
2557 l_cz_imp_rules.INVALID_FLAG := '0'; --Default value for valid rule
2558 l_cz_imp_rules.DESC_TEXT := l_rule_description;
2559 l_cz_imp_rules.NAME := l_rule_name; -- check
2560 l_cz_imp_rules.ANTECEDENT_ID := NULL;
2561 l_cz_imp_rules.CONSEQUENT_ID := NULL;
2562 l_cz_imp_rules.RULE_TYPE := 300; -- Extension Rule
2563 l_cz_imp_rules.EXPR_RULE_TYPE := NULL; --1; --Needed for Extension rule
2564 l_cz_imp_rules.COMPONENT_ID := NULL;
2565 l_cz_imp_rules.REASON_TYPE := 0; -- Value in reason_id corresponds to Name of Rule
2566 l_cz_imp_rules.DISABLED_FLAG := '0'; -- Indicates enabled rule
2567 l_cz_imp_rules.ORIG_SYS_REF := G_XTN_RULE_OSR||l_intent;
2568 l_cz_imp_rules.CREATION_DATE := SYSDATE;
2569 l_cz_imp_rules.LAST_UPDATE_DATE := SYSDATE;
2570 l_cz_imp_rules.DELETED_FLAG := 0; -- check
2571 l_cz_imp_rules.EFF_FROM := NULL;
2572 l_cz_imp_rules.EFF_TO := NULL;
2573 l_cz_imp_rules.CREATED_BY := FND_GLOBAL.USER_ID;
2574 l_cz_imp_rules.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2575 l_cz_imp_rules.SECURITY_MASK := NULL;
2576 l_cz_imp_rules.EFF_MASK := NULL;
2577 l_cz_imp_rules.CHECKOUT_USER := NULL;
2578 l_cz_imp_rules.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2579 l_cz_imp_rules.EFFECTIVE_USAGE_MASK := NULL;
2580 l_cz_imp_rules.SEQ_NBR := l_seq_nbr;
2581 l_cz_imp_rules.EFFECTIVE_FROM := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
2582 l_cz_imp_rules.EFFECTIVE_UNTIL := OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
2583 l_cz_imp_rules.PERSISTENT_RULE_ID := NULL;
2584 l_cz_imp_rules.EFFECTIVITY_SET_ID := NULL;
2585 l_cz_imp_rules.RULE_FOLDER_TYPE := NULL;
2586 l_cz_imp_rules.UNSATISFIED_MSG_ID := NULL;
2587 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE := '0'; -- Changed from NULL
2588 l_cz_imp_rules.SIGNATURE_ID := NULL;
2589 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG := NULL;
2590 l_cz_imp_rules.PRESENTATION_FLAG := NULL; --'0'; --Default value
2591 l_cz_imp_rules.TEMPLATE_TOKEN := NULL;
2592 -- l_cz_imp_rules.RULE_TEXT := G_XTN_RULE_TEXT;
2593 l_cz_imp_rules.NOTES := NULL;
2594 l_cz_imp_rules.CLASS_NAME := G_XTN_RULE_CLASS_NAME;
2595 l_cz_imp_rules.INSTANTIATION_SCOPE := 1; -- Needed for Extension rule
2596 l_cz_imp_rules.MODEL_REF_EXPL_ID := NULL;
2597 l_cz_imp_rules.MUTABLE_FLAG := '0'; --Default value
2598 l_cz_imp_rules.SEEDED_FLAG := NULL; --Seeded CX rule
2599 l_cz_imp_rules.UI_DEF_ID := NULL;
2600 l_cz_imp_rules.UI_PAGE_ID := NULL;
2601 l_cz_imp_rules.UI_PAGE_ELEMENT_ID := NULL;
2602 l_cz_imp_rules.MESSAGE := NULL;
2603 l_cz_imp_rules.RUN_ID := p_run_id; -- Input runid
2604 l_cz_imp_rules.DISPOSITION := NULL;
2605 l_cz_imp_rules.REC_STATUS := NULL;
2606 l_cz_imp_rules.FSK_DEVL_PROJECT := l_model_osr;
2607 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1 := G_XTN_RULE_OSR||l_intent;
2608 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2 := NULL;
2609 l_cz_imp_rules.IMPORT_PROG_VERSION := NULL;
2610 l_cz_imp_rules.FSK_COMPONENT_ID := NULL;
2611 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID := NULL;
2612
2613
2614 -- Insert into cz_imp_rules
2615
2616 INSERT INTO cz_imp_rules
2617 (
2618 RULE_ID,
2619 SUB_CONS_ID,
2620 REASON_ID,
2621 AMOUNT_ID,
2622 GRID_ID,
2623 RULE_FOLDER_ID,
2624 DEVL_PROJECT_ID,
2625 INVALID_FLAG,
2626 DESC_TEXT,
2627 NAME,
2628 ANTECEDENT_ID,
2629 CONSEQUENT_ID,
2630 RULE_TYPE,
2631 EXPR_RULE_TYPE,
2632 COMPONENT_ID,
2633 REASON_TYPE,
2634 DISABLED_FLAG,
2635 ORIG_SYS_REF,
2636 CREATION_DATE,
2637 LAST_UPDATE_DATE,
2638 DELETED_FLAG,
2639 EFF_FROM,
2640 EFF_TO,
2641 CREATED_BY,
2642 LAST_UPDATED_BY,
2643 SECURITY_MASK,
2644 EFF_MASK,
2645 CHECKOUT_USER,
2646 LAST_UPDATE_LOGIN,
2647 EFFECTIVE_USAGE_MASK,
2648 SEQ_NBR,
2649 EFFECTIVE_FROM,
2650 EFFECTIVE_UNTIL,
2651 PERSISTENT_RULE_ID,
2652 EFFECTIVITY_SET_ID,
2653 RULE_FOLDER_TYPE,
2654 UNSATISFIED_MSG_ID,
2655 UNSATISFIED_MSG_SOURCE,
2656 SIGNATURE_ID,
2657 TEMPLATE_PRIMITIVE_FLAG,
2658 PRESENTATION_FLAG,
2659 TEMPLATE_TOKEN,
2660 RULE_TEXT,
2661 NOTES,
2662 CLASS_NAME,
2663 INSTANTIATION_SCOPE,
2664 MODEL_REF_EXPL_ID,
2665 MUTABLE_FLAG,
2666 SEEDED_FLAG,
2667 UI_DEF_ID,
2668 UI_PAGE_ID,
2669 UI_PAGE_ELEMENT_ID,
2670 MESSAGE,
2671 RUN_ID,
2672 DISPOSITION,
2673 REC_STATUS,
2674 FSK_DEVL_PROJECT,
2675 FSK_LOCALIZED_TEXT_1,
2676 FSK_LOCALIZED_TEXT_2,
2677 IMPORT_PROG_VERSION,
2678 FSK_COMPONENT_ID,
2679 FSK_MODEL_REF_EXPL_ID
2680 )
2681 VALUES
2682 (
2683 l_cz_imp_rules.RULE_ID,
2684 l_cz_imp_rules.SUB_CONS_ID,
2685 l_cz_imp_rules.REASON_ID,
2686 l_cz_imp_rules.AMOUNT_ID,
2687 l_cz_imp_rules.GRID_ID,
2688 l_cz_imp_rules.RULE_FOLDER_ID,
2689 l_cz_imp_rules.DEVL_PROJECT_ID,
2690 l_cz_imp_rules.INVALID_FLAG,
2691 l_cz_imp_rules.DESC_TEXT,
2692 l_cz_imp_rules.NAME,
2693 l_cz_imp_rules.ANTECEDENT_ID,
2694 l_cz_imp_rules.CONSEQUENT_ID,
2695 l_cz_imp_rules.RULE_TYPE,
2696 l_cz_imp_rules.EXPR_RULE_TYPE,
2697 l_cz_imp_rules.COMPONENT_ID,
2698 l_cz_imp_rules.REASON_TYPE,
2699 l_cz_imp_rules.DISABLED_FLAG,
2700 l_cz_imp_rules.ORIG_SYS_REF,
2701 l_cz_imp_rules.CREATION_DATE,
2702 l_cz_imp_rules.LAST_UPDATE_DATE,
2703 l_cz_imp_rules.DELETED_FLAG,
2704 l_cz_imp_rules.EFF_FROM,
2705 l_cz_imp_rules.EFF_TO,
2706 l_cz_imp_rules.CREATED_BY,
2707 l_cz_imp_rules.LAST_UPDATED_BY,
2708 l_cz_imp_rules.SECURITY_MASK,
2709 l_cz_imp_rules.EFF_MASK,
2710 l_cz_imp_rules.CHECKOUT_USER,
2711 l_cz_imp_rules.LAST_UPDATE_LOGIN,
2712 l_cz_imp_rules.EFFECTIVE_USAGE_MASK,
2713 l_cz_imp_rules.SEQ_NBR,
2714 l_cz_imp_rules.EFFECTIVE_FROM,
2715 l_cz_imp_rules.EFFECTIVE_UNTIL,
2716 l_cz_imp_rules.PERSISTENT_RULE_ID,
2717 l_cz_imp_rules.EFFECTIVITY_SET_ID,
2718 l_cz_imp_rules.RULE_FOLDER_TYPE,
2719 l_cz_imp_rules.UNSATISFIED_MSG_ID,
2720 l_cz_imp_rules.UNSATISFIED_MSG_SOURCE,
2721 l_cz_imp_rules.SIGNATURE_ID,
2722 l_cz_imp_rules.TEMPLATE_PRIMITIVE_FLAG,
2723 l_cz_imp_rules.PRESENTATION_FLAG,
2724 l_cz_imp_rules.TEMPLATE_TOKEN,
2725 --l_cz_imp_rules.RULE_TEXT,
2726 G_XTN_RULE_TEXT,
2727 l_cz_imp_rules.NOTES,
2728 l_cz_imp_rules.CLASS_NAME,
2729 l_cz_imp_rules.INSTANTIATION_SCOPE,
2730 l_cz_imp_rules.MODEL_REF_EXPL_ID,
2731 l_cz_imp_rules.MUTABLE_FLAG,
2732 l_cz_imp_rules.SEEDED_FLAG,
2733 l_cz_imp_rules.UI_DEF_ID,
2734 l_cz_imp_rules.UI_PAGE_ID,
2735 l_cz_imp_rules.UI_PAGE_ELEMENT_ID,
2736 l_cz_imp_rules.MESSAGE,
2737 l_cz_imp_rules.RUN_ID,
2738 l_cz_imp_rules.DISPOSITION,
2739 l_cz_imp_rules.REC_STATUS,
2740 l_cz_imp_rules.FSK_DEVL_PROJECT,
2741 l_cz_imp_rules.FSK_LOCALIZED_TEXT_1,
2742 l_cz_imp_rules.FSK_LOCALIZED_TEXT_2,
2743 l_cz_imp_rules.IMPORT_PROG_VERSION,
2744 l_cz_imp_rules.FSK_COMPONENT_ID,
2745 l_cz_imp_rules.FSK_MODEL_REF_EXPL_ID
2746 );
2747 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2748 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2749 G_MODULE||l_api_name,
2750 '170: After Insert into cz_imp_rules' || '='||l_intent);
2751 END IF;
2752
2753 END IF; -- check extn rule exists
2754
2755 END LOOP;
2756 CLOSE csr_intents; -- Insert the Extension Rule for this Intent
2757
2758
2759 -- Standard call to get message count and if count is 1, get message info.
2760 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2761
2762
2763 -- end debug log
2764 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2765 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2766 G_MODULE||l_api_name,
2767 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2768 END IF;
2769
2770 EXCEPTION
2771 WHEN FND_API.G_EXC_ERROR THEN
2772 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2773 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2774 G_MODULE||l_api_name,
2775 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2776 END IF;
2777
2778 x_return_status := G_RET_STS_ERROR ;
2779 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2780
2781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2782 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2783 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2784 G_MODULE||l_api_name,
2785 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2786 END IF;
2787
2788 x_return_status := G_RET_STS_UNEXP_ERROR ;
2789 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2790
2791 WHEN OTHERS THEN
2792 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2793 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2794 G_MODULE||l_api_name,
2795 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2796 END IF;
2797
2798 x_return_status := G_RET_STS_UNEXP_ERROR ;
2799 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2800 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2801 END IF;
2802 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2803
2804 END attach_extension_rule_tmpl;
2805
2806
2807
2808
2809
2810
2811 ---------------------------------------------------
2812
2813
2814 END OKC_XPRT_IMPORT_RULES_PVT;