DBA Data[Home] [Help]

PACKAGE BODY: APPS.OKC_XPRT_IMPORT_RULES_PVT

Source


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