[Home] [Help]
PACKAGE BODY: APPS.OKL_RULE_EXTRACT_PVT
Source
1 Package Body OKL_RULE_EXTRACT_PVT AS
2 /* $Header: OKLRREXB.pls 120.3.12020000.2 2012/11/30 04:46:34 racheruv ship $ */
3 --Start of Comments
4 --Procedure : Break_SQL
5 --Purpose : Takes SQL Statement as input and breaks is into
6 -- SELECT, FROM, WHERE and ORDER BY Clauses
7 --End of Comments
8 PROCEDURE Break_SQL ( p_sql_statement IN VARCHAR2,
9 x_select_clause OUT NOCOPY VARCHAR2,
10 x_from_clause OUT NOCOPY VARCHAR2,
11 x_where_clause OUT NOCOPY VARCHAR2,
12 x_order_by_clause OUT NOCOPY VARCHAR2 ) is
13 l_sql_statement varchar2(2000) default null;
14 l_select_clause varchar2(2000) default null;
15 l_from_clause varchar2(2000) default null;
16 l_where_clause varchar2(2000) default null;
17 l_order_by_clause varchar2(2000) default null;
18 begin
19 l_sql_statement := upper(p_sql_statement);
20 l_select_clause := substr(l_sql_statement,instr(l_sql_statement,'SELECT'),
21 instr(l_sql_statement,'FROM')-instr(l_sql_statement,'SELECT'));
22 select substr(l_sql_statement,instr(l_sql_statement,'FROM'),
23 decode(instr(l_sql_statement,'WHERE'),
24 0,decode(instr(l_sql_statement,'ORDER BY'),0,
25 length(l_sql_statement),
26 instr(l_sql_statement,'ORDER BY') - instr(l_sql_statement,'FROM')),
27 instr(l_sql_statement,'WHERE') - instr(l_sql_statement,'FROM')))
28 into l_from_clause from dual;
29 select decode(instr(l_sql_statement,'WHERE'),
30 0,Null,
31 substr(l_sql_statement,instr(l_sql_statement,'WHERE'),
32 decode(instr(l_sql_statement,'ORDER BY'),
33 0,length(l_sql_statement),
34 instr(l_sql_statement,'ORDER BY') - instr(l_sql_statement,'WHERE'))))
35 into l_where_clause from dual;
36 select decode(instr(l_sql_statement,'ORDER BY'),
37 0,Null,
38 substr(l_sql_statement,instr(l_sql_statement,'ORDER BY'),
39 length(l_sql_statement)))
40 into l_order_by_clause from dual;
41 ------------------------------------------------------------
42 --dbms_output.put_line('SELECT :'||l_select_clause);
43 --dbms_output.put_line('FROM :'||l_from_clause);
44 --dbms_output.put_line('WHERE :'||l_where_clause);
45 --dbms_output.put_line('ORDER BY :'||l_order_by_clause);
46 -------------------------------------------------------------
47 x_select_clause := l_select_clause;
48 x_from_clause := l_from_clause;
49 x_where_clause := l_where_clause;
50 x_order_by_clause := l_order_by_clause;
51 end Break_SQL;
52 --Start of Comments
53 --Procedure : Get_Jtot_Query
54 --Purpose : Returns Query for jtot based rule segments
55 --End of Comments
56 PROCEDURE Get_Jtot_Query(x_return_status OUT NOCOPY VARCHAR2,
57 p_rgd_code IN VARCHAR2,
58 p_rgs_code IN VARCHAR2,
59 p_buy_or_sell IN VARCHAR2,
60 p_object_code IN VARCHAR2,
61 x_select_clause OUT NOCOPY VARCHAR2,
62 x_from_clause OUT NOCOPY VARCHAR2,
63 x_where_clause OUT NOCOPY VARCHAR2,
64 x_order_by_clause OUT NOCOPY VARCHAR2,
65 x_object_code OUT NOCOPY VARCHAR2,
66 x_id1_col OUT NOCOPY VARCHAR2,
67 x_id2_col OUT NOCOPY VARCHAR2,
68 x_name_col OUT NOCOPY VARCHAR2) is
69 --Cursor to get JTOT_OBJECT_CODE from OKC_RULE_DEF_SOURCES_V
70 Cursor rule_source_curs is
71 SELECT rds.jtot_object_code object_code
72 FROM OKC_RULE_DEF_SOURCES_V rds
73 WHERE rds.rgr_rgd_code = p_rgd_code
74 AND rds.rgr_rdf_code = p_rgs_code
75 AND rds.buy_or_sell = p_buy_or_sell
76 AND rds.object_id_number = decode(p_object_code,
77 'JTOT_OBJECT1_CODE',1,
78 'JTOT_OBJECT2_CODE',2,
79 'JTOT_OBJECT3_CODE',3)
80 AND rds.start_date <= sysdate
81 AND nvl(rds.end_date,sysdate+1) > sysdate
82 ORDER BY rds.jtot_object_code;
83 rule_source_rec rule_source_curs%ROWTYPE;
84 l_where_clause varchar2(2000) default null;
85 l_query_string varchar2(2000) default null;
86 l_jtf_query varchar2(2000) default null;
87
88 Begin
89 --initialize return status
90 x_return_status := OKL_API.G_RET_STS_SUCCESS;
91 --Step 1 : Get the JTOT_OBJECT_CODE Name from OKC_RULE_DEF_SOURCES_V
92 Open rule_source_curs;
93 Fetch rule_source_curs into rule_source_rec;
94 If rule_source_curs%NotFound Then
95 --dbms_output.put_line('Get_Jtot_Query : falied in getting rule_source');
96 /*OKL_API.SET_MESSAGE(p_app_name => g_app_name,
97 p_msg_name => G_WARNING,
98 p_token1 => p_rgd_code||':'||p_rgs_code||':'||p_buy_or_sell||':'||p_object_code,
99 p_token1_value => 'FAILED TO GET RULE SOURCE FROM OKC_RULE_SOURCES'
100 );
101 */
102 x_object_code := NULL;
103 --this is not an error so no need to raise exception
104 --column definition in fnd may be for another rule group and intent
105 --so just skip this record
106 Else
107 x_object_code := rule_source_rec.object_code;
108 l_query_string := ' SELECT '|| rule_source_rec.object_code||'.ID1, '||
109 rule_source_rec.object_code||'.ID2, '||
110 rule_source_rec.object_code||'.NAME, '||
111 rule_source_rec.object_code||'.DESCRIPTION';
112 l_jtf_query := OKC_UTIL.GET_SQL_FROM_JTFV(p_object_code => rule_source_rec.object_code);
113 If l_jtf_query is Null Then
114 --dbms_output.put_line('Get_Jtot_Query : falied in getting jtf query');
115 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
116 p_msg_name => G_ERROR,
117 p_token1 => p_rgd_code||':'||p_rgs_code||':'||p_buy_or_sell,
118 p_token1_value => 'FAILED IN GETTING JTF QUERY FROM OKC_UTIL'
119 );
120 RAISE OKL_API.G_EXCEPTION_ERROR;
121 Else
122 l_query_string := l_query_string || ' FROM ' ||l_jtf_query;
123 End If;
124
125 Break_Sql(p_sql_statement => l_query_string,
126 x_select_clause => x_select_clause,
127 x_from_clause => x_from_clause,
128 x_where_clause => x_where_clause,
129 x_order_by_clause => x_order_by_clause);
130
131 -- Changes for constraining BTO - Begin
132 /*
133 if (p_rgs_code = 'BTO' and rule_source_rec.object_code = 'OKX_BILLTO') Then
134 x_from_clause := 'FROM OKL_LA_BILL_TO_UV OKX_BILLTO ';
135 End if;
136 */
137 -- Changes for constraining BTO - End
138 End If;
139 Close rule_source_curs;
140 x_id1_col := x_object_code||'.'||'ID1';
141 x_id2_col := x_object_code||'.'||'ID2';
142 x_name_col := x_object_code||'.'||'NAME';
143 EXCEPTION
144 when OKL_API.G_EXCEPTION_ERROR then
145 x_return_status := OKL_API.G_RET_STS_ERROR;
146 when OTHERS then
147 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
148 p_msg_name => G_UNEXPECTED_ERROR,
149 p_token1 => G_SQLCODE_TOKEN,
150 p_token1_value => SQLCODE,
151 p_token2 => G_SQLERRM_TOKEN,
152 p_token2_value => SQLERRM);
153 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
154 End Get_Jtot_Query;
155 --Start of Comments
156 --Procedure : Get_Dff_Query
157 --Purpose : Returns Query for Pure Dff Rule segments
158 --End of Comments
159 PROCEDURE Get_Dff_Query( x_return_status OUT NOCOPY VARCHAR2,
160 p_flex_value_set_id IN NUMBER,
161 x_select_clause OUT NOCOPY VARCHAR2,
162 x_from_clause OUT NOCOPY VARCHAR2,
163 x_where_clause OUT NOCOPY VARCHAR2,
164 x_order_by_clause OUT NOCOPY VARCHAR2,
165 x_object_code OUT NOCOPY VARCHAR2,
166 x_longlist_flag OUT NOCOPY VARCHAR2,
167 x_format_type OUT NOCOPY VARCHAR2,
168 x_rule_information_col OUT NOCOPY VARCHAR2,
169 x_meaning_col OUT NOCOPY VARCHAR2,
170 x_value_set_name OUT NOCOPY VARCHAR2,
171 x_additional_columns OUT NOCOPY VARCHAR2) is
172 --Cursor for getting the Validation Type
173 Cursor flex_value_set_cur(p_flex_value_set_id NUMBER) is
174 select fvs.longlist_flag
175 , fvs.format_type
176 , fvs.maximum_size
177 , fvs.validation_type
178 , fvs.flex_value_set_id
179 , fvs.flex_value_set_name
180 from FND_FLEX_VALUE_SETS fvs
181 Where fvs.FLEX_VALUE_SET_ID=p_flex_value_set_id;
182 flex_value_set_rec flex_value_set_cur%rowtype;
183 --Cursor for getting the query for table validated value sets
184 Cursor flex_query_t_cur(p_flex_value_set_id NUMBER) is
185 SELECT fvt.id_column_name,
186 fvt.value_column_name,
187 fvt.meaning_column_name,
188 fvt.application_table_name,
189 fvt.additional_where_clause,
190 fvt.enabled_column_name,
191 fvt.start_date_column_name,
192 fvt.end_date_column_name,
193 fvt.additional_quickpick_columns
194 FROM fnd_flex_validation_tables fvt
195 WHERE fvt.flex_value_set_id = p_flex_value_set_id;
196 flex_query_t_rec flex_query_t_cur%rowtype;
197 l_query_string varchar2(2000) default Null;
198 l_object_code varchar2(240) default Null;
199 l_select_clause varchar2(200) default Null;
200 l_from_clause varchar2(200) default Null;
201 l_where_clause varchar2(2000) default Null;
202 l_add_where_clause varchar2(2000) default Null;
203 l_order_by_clause varchar2(2000) default Null;
204 l_success number;
205 l_mapping_code Varchar2(10) default null;
206
207 Begin
208 --initialize return status
209 x_return_status := OKL_API.G_RET_STS_SUCCESS;
210 Open flex_value_set_cur(p_flex_value_set_id);
211 Fetch flex_value_set_cur into flex_value_set_rec;
212 If flex_value_set_cur%NotFound Then
213 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
214 p_msg_name => G_ERROR,
215 p_token1 => to_char(p_flex_value_set_id),
216 p_token1_value => 'FAILED TO FETCH VALUE SET RECORD'
217 );
218 RAISE OKL_API.G_EXCEPTION_ERROR;
219 Null; --raise appropriate exception
220 Elsif flex_value_set_rec.validation_type = 'N' Then --No Validation
221 x_longlist_flag := flex_value_set_rec.longlist_flag;
222 x_format_type := flex_value_set_rec.format_type;
223 x_value_set_name := flex_value_set_rec.flex_value_set_name;
224 l_query_string := Null;
225 l_object_code := Null;
226 x_select_clause := 'None';
227 x_object_code := 'None';
228 Elsif flex_value_set_rec.validation_type in ('I') Then --Independent
229 x_longlist_flag := flex_value_set_rec.longlist_flag;
230 x_format_type := flex_value_set_rec.format_type;
231 x_value_set_name := flex_value_set_rec.flex_value_set_name;
232 fnd_flex_val_api.get_independent_vset_select(p_value_set_id => p_flex_value_set_id,
233 p_inc_id_col => 'N',
234 x_select => l_query_string,
235 x_mapping_code => l_mapping_code,
236 x_success => l_success);
237
238 Break_Sql(p_sql_statement => l_query_string,
239 x_select_clause => x_select_clause,
240 x_from_clause => x_from_clause,
241 x_where_clause => x_where_clause,
242 x_order_by_clause => x_order_by_clause);
243
244 x_object_code := 'FND_FLEX_VALUES_VL';
245 x_rule_information_col := 'FLEX_VALUE';
246 x_meaning_col := 'FLEX_VALUE_MEANING';
247
248 Elsif flex_value_set_rec.validation_type = 'D' Then
249 x_longlist_flag := flex_value_set_rec.longlist_flag;
250 x_format_type := flex_value_set_rec.format_type;
251 x_value_set_name := flex_value_set_rec.flex_value_set_name;
252 fnd_flex_val_api.get_dependent_vset_select(p_value_set_id => p_flex_value_set_id,
253 p_inc_id_col => 'N',
254 x_select => l_query_string,
255 x_mapping_code => l_mapping_code,
256 x_success => l_success);
257 Break_Sql(p_sql_statement => l_query_string,
258 x_select_clause => x_select_clause,
259 x_from_clause => x_from_clause,
260 x_where_clause => x_where_clause,
261 x_order_by_clause => x_order_by_clause);
262
263 x_object_code := 'FND_FLEX_VALUES_VL';
264 x_rule_information_col := 'FLEX_VALUE';
265 x_meaning_col := 'FLEX_VALUE_MEANING';
266
267 Elsif flex_value_set_rec.validation_type = 'F' Then -- Table Type
268 x_longlist_flag := flex_value_set_rec.longlist_flag;
269 x_format_type := flex_value_set_rec.format_type;
270 x_value_set_name := flex_value_set_rec.flex_value_set_name;
271 Open flex_query_t_cur(p_flex_value_set_id);
272 Fetch flex_query_t_cur into flex_query_t_rec;
273 If flex_query_t_cur%NotFound Then
274 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
275 p_msg_name => G_ERROR,
276 p_token1 => to_char(p_flex_value_set_id),
277 p_token1_value => 'FAILED TO FETCH TABLE VALIDATED QUERY'
278 );
279 RAISE OKL_API.G_EXCEPTION_ERROR;
280 Else
281 --For Rules always use id col
282 If flex_query_t_rec.id_column_name is null Then
283 l_select_clause := ' SELECT '||l_select_clause||' '||flex_query_t_rec.value_column_name||' , ';
284 Else
285 l_select_clause := ' SELECT '||l_select_clause||' '||flex_query_t_rec.id_column_name||' , ';
286 End If;
287 --For Rules always use id col and value column
288 l_select_clause := l_select_clause||' '||flex_query_t_rec.value_column_name;
289 /*
290 If flex_query_t_rec.meaning_column_name is not null Then
291 l_select_clause := l_select_clause||' '||','||flex_query_t_rec.meaning_column_name||' ';
292 Else
293 l_select_clause := l_select_clause||' '||flex_query_t_rec.value_column_name;
294 End If;
295 */
296 l_from_clause := ' FROM '||l_from_clause||flex_query_t_rec.application_table_name||' ';
297 l_where_clause := ' WHERE '||l_where_clause||' '||flex_query_t_rec.enabled_column_name||' = ';
298 l_where_clause := l_where_clause||' '||''''||'Y'||'''';
299 l_where_clause := l_where_clause||' AND ';
300 l_where_clause := l_where_clause||' nvl('||flex_query_t_rec.start_date_column_name||',sysdate) <= sysdate';
301 l_where_clause := l_where_clause||' AND ';
302 l_where_clause := l_where_clause||' nvl('||flex_query_t_rec.end_date_column_name||',sysdate+1) > sysdate';
303
304 If flex_query_t_rec.additional_where_clause is null Then
305 Null;
306 Else
307 flex_query_t_rec.additional_where_clause:= REPLACE(upper(flex_query_t_rec.additional_where_clause),'WHERE',' ');
308 l_add_where_clause := null;
309 select l_where_clause||' '||decode(l_where_clause,null,' ',decode(instr(ltrim(flex_query_t_rec.additional_where_clause,' '),'ORDER BY'),1,' ',' AND '))||flex_query_t_rec.additional_where_clause
310 into l_add_where_clause from dual;
311 l_where_clause := l_add_where_clause;
312 End If;
313 l_query_string := rtrim(ltrim(l_select_clause,' '),' ')||' '||
314 rtrim(ltrim(l_from_clause,' '),' ')||' '||
315 rtrim(ltrim(l_where_clause,' '),' ')||' '||
316 rtrim(ltrim(l_order_by_clause,' '),' ');
317 Break_Sql(p_sql_statement => l_query_string,
318 x_select_clause => x_select_clause,
319 x_from_clause => x_from_clause,
320 x_where_clause => x_where_clause,
321 x_order_by_clause => x_order_by_clause);
322 x_object_code := flex_query_t_rec.application_table_name;
323 x_rule_information_col := flex_query_t_rec.id_column_name;
324 x_meaning_col := flex_query_t_rec.value_column_name;
325 x_additional_columns := flex_query_t_rec.additional_quickpick_columns;
326 End If;
327 Close flex_query_t_cur;
328 End If;
329 Close flex_value_set_cur;
330 x_object_code := l_object_code;
331
332 EXCEPTION
333 when OKL_API.G_EXCEPTION_ERROR then
334 x_return_status := OKL_API.G_RET_STS_ERROR;
335 when OTHERS then
336 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
337 p_msg_name => G_UNEXPECTED_ERROR,
338 p_token1 => G_SQLCODE_TOKEN,
339 p_token1_value => SQLCODE,
340 p_token2 => G_SQLERRM_TOKEN,
341 p_token2_value => SQLERRM);
342 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
343 END Get_Dff_Query;
344
345 --Start of Comments
346 --Procedure : Get_Rule_Def
347 --Purpose : Gets Rule metadata from Dff table and calls appropriate procedure
348 -- based on the type of segment
349 --End of Comments
350 PROCEDURE Get_Rule_Def (p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2,
352 x_return_status OUT NOCOPY VARCHAR2,
353 x_msg_count OUT NOCOPY NUMBER,
354 x_msg_data OUT NOCOPY VARCHAR2,
355 p_rgd_code IN VARCHAR2,
356 p_rgs_code IN VARCHAR2,
357 p_buy_or_sell IN VARCHAR2,
358 x_rule_segment_tbl OUT NOCOPY rule_segment_tbl_type) is
359
360 l_select_clause Varchar2(2000) Default Null;
361 l_from_clause Varchar2(2000) Default Null;
362 l_where_clause Varchar2(2000) Default Null;
363 l_order_by_clause Varchar2(2000) Default Null;
364 l_longlist_flag Varchar2(1) Default 'Y';
365 l_format_type Varchar2(1) Default Null;
366 l_id1_col varchar2(30) Default Null;
367 l_id2_col varchar2(30) Default Null;
368 l_rule_info_col varchar2(30) Default Null;
369 l_name_col varchar2(30) Default Null;
370 l_object_code VARCHAR2(240) default null;
371 l_object_code1 VARCHAR2(240) default null;
372 --l_object_code JTF_OBJECTS_B.OBJECT_CODE%TYPE Default Null;
373 l_flex_value_set_id Number;
374 l_dflex_r fnd_dflex.dflex_r;
375 l_context_r fnd_dflex.context_r;
376 l_segments_r fnd_dflex.segments_dr;
377 l_rule_segment_tbl rule_segment_tbl_type;
378
379 l_value_set_name FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE Default Null;
380 l_additional_columns FND_FLEX_VALIDATION_TABLES.ADDITIONAL_QUICKPICK_COLUMNS%TYPE Default Null;
381
382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
383 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_DEF';
384 l_api_version CONSTANT NUMBER := 1.0;
385 -- udhenuko Added the variable to capture the enabled flag for the rule
386 l_rule_enabled_flag FND_DESCR_FLEX_COLUMN_USAGES.SECURITY_ENABLED_FLAG%TYPE Default 'N';
387 -- rule striping
388 Cursor rule_dff_cur (p_rgs_code IN VARCHAR2) is
389 Select dfcu.application_id,
390 dfcu.descriptive_flexfield_name,
391 -- Bug 5876083 - udhenuko Added
392 dfcon.enabled_flag
393 From okc_rule_defs_v rdfv,
394 fnd_descr_flex_col_usage_vl dfcu,
395 fnd_descr_flex_contexts_vl dfcon
396 where dfcu.application_id = rdfv.application_id
397 and dfcu.descriptive_flex_context_code = rdfv.rule_code
398 and dfcu.descriptive_flexfield_name = rdfv.DESCRIPTIVE_FLEXFIELD_NAME
399 and dfcon.application_id = rdfv.application_id
400 and dfcon.descriptive_flex_context_code= rdfv.rule_code
401 and dfcon.descriptive_flexfield_name = rdfv.DESCRIPTIVE_FLEXFIELD_NAME
402 and rdfv.rule_code = p_rgs_code;
403 Begin
404 --set context
405 -- If okc_context.get_okc_organization_id is null then
406 -- okc_context.set_okc_org_context(1,1);
407 -- End If;
408 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
409 G_PKG_NAME,
410 p_init_msg_list,
411 G_API_VERSION,
412 p_api_version,
413 G_SCOPE,
414 x_return_status);
415
416 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
417 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
418 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
419 RAISE OKL_API.G_EXCEPTION_ERROR;
420 END IF;
421 x_return_status := l_return_status;
422
423 --rule striping
424 Open rule_dff_cur (p_rgs_code => p_rgs_code);
425 Fetch rule_dff_cur into l_dflex_r.application_id,
426 l_dflex_r.flexfield_name,
427 l_rule_enabled_flag;
428 If rule_dff_cur%NOTFOUND Then
429 Null;
430 End If;
431 Close rule_dff_cur;
432
433 -- udhenuko Including this check to restrict the processing only for Enabled Rules.
434 If l_rule_enabled_flag = 'Y' Then
435
436 --l_dflex_r.application_id := 510;
437 --l_dflex_r.flexfield_name := 'OKC Rule Developer DF';
438 l_context_r.flexfield := l_dflex_r;
439 l_context_r.context_code := p_rgs_code;
440
441 fnd_dflex.get_segments( context => l_context_r,
442 segments => l_segments_r );
443 If l_segments_r.nsegments = 0 Then
444 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
445 p_msg_name => G_ERROR,
446 p_token1 => p_rgd_code||':'||p_rgs_code||':'||p_buy_or_sell,
447 p_token1_value => 'FAILED TO GET RULE SEGMENTS FROM FND DFLEX DEFINITIONS'
448 );
449 RAISE OKL_API.G_EXCEPTION_ERROR;
450 Else
451 for i in 1..l_segments_r.nsegments
452 Loop
453 If l_segments_r.application_column_name(i) like 'JTOT%' Then
454 --get query from JTF Objects
455 l_object_code1 := l_segments_r.application_column_name(i);
456 l_object_code := l_segments_r.application_column_name(i);
457 l_longlist_flag := 'Y';
458
459 Get_jtot_query(x_return_status => l_return_status,
460 p_rgd_code => p_rgd_code,
461 p_rgs_code => p_rgs_code,
462 p_buy_or_sell => p_buy_or_sell,
463 p_object_code => l_object_code1,
464 x_select_clause => l_select_clause,
465 x_from_clause => l_from_clause,
466 x_where_clause => l_where_clause,
467 x_order_by_clause => l_order_by_clause,
468 x_object_code => l_object_code,
469 x_id1_col => l_id1_col,
470 x_id2_col => l_id2_col,
471 x_name_col => l_name_col);
472
473 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
474 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
475 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
476 RAISE OKC_API.G_EXCEPTION_ERROR;
477 END IF;
478
479 Elsif l_segments_r.application_column_name(i) like 'RULE%' Then
480 --Get Query from Flex Values
481 If l_segments_r.value_set(i) is Null Then
482 Null; --the column has no validation
483 l_select_clause := 'None';
484 Else
485 l_flex_value_set_id := l_segments_r.value_set(i);
486 Get_dff_query( x_return_status => l_return_status,
487 p_flex_value_set_id => l_flex_value_set_id,
488 x_select_clause => l_select_clause,
489 x_from_clause => l_from_clause,
490 x_where_clause => l_where_clause,
491 x_order_by_clause => l_order_by_clause,
492 x_object_code => l_object_code,
493 x_longlist_flag => l_longlist_flag,
494 x_format_type => l_format_type,
495 x_rule_information_col => l_rule_info_col,
496 x_meaning_col => l_name_col,
497 x_value_set_name => l_value_set_name,
498 x_additional_columns => l_additional_columns);
499
500 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
501 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
502 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
503 RAISE OKC_API.G_EXCEPTION_ERROR;
504 END IF;
505 End If;
506 End If;
507 l_rule_segment_tbl(i).rgd_code := p_rgd_code;
508 l_rule_segment_tbl(i).rgs_code := p_rgs_code;
509 l_rule_segment_tbl(i).application_column_name := l_segments_r.application_column_name(i);
510 l_rule_segment_tbl(i).end_user_column_name := l_segments_r.segment_name(i);
511 l_rule_segment_tbl(i).sequence := l_segments_r.sequence(i);
512
513 If l_segments_r.is_enabled(i) then
514 l_rule_segment_tbl(i).enabled_flag := 'Y';
515 Else
516 l_rule_segment_tbl(i).enabled_flag := 'N';
517 End If;
518 If l_segments_r.is_displayed(i) then
519 l_rule_segment_tbl(i).displayed_flag := 'Y';
520 Else
521 l_rule_segment_tbl(i).displayed_flag := 'N';
522 End If;
523 If l_segments_r.is_required(i) then
524 l_rule_segment_tbl(i).required_flag := 'Y';
525 Else
526 l_rule_segment_tbl(i).required_flag := 'N';
527 End If;
528
529 l_rule_segment_tbl(i).default_size := l_segments_r.display_size(i);
530 l_rule_segment_tbl(i).left_prompt := l_segments_r.row_prompt(i);
531 If l_segments_r.default_type(i) is not null then
532 l_rule_segment_tbl(i).format_type := l_segments_r.default_type(i);
533 Else
534 l_rule_segment_tbl(i).format_type := l_format_type;
535 End If;
536 l_rule_segment_tbl(i).select_clause := l_select_clause;
537 l_rule_segment_tbl(i).from_clause := l_from_clause;
538 l_rule_segment_tbl(i).where_clause := l_where_clause;
539 l_rule_segment_tbl(i).order_by_clause := l_order_by_clause;
540 l_rule_segment_tbl(i).object_code := l_object_code;
541 l_rule_segment_tbl(i).longlist_flag := l_longlist_flag;
542 l_rule_segment_tbl(i).id1_col := l_id1_col;
543 l_rule_segment_tbl(i).id2_col := l_id2_col;
544 l_rule_segment_tbl(i).rule_info_col := l_rule_info_col;
545 l_rule_segment_tbl(i).name_col := l_name_col;
546 l_rule_segment_tbl(i).value_set_name := l_value_set_name;
547 l_rule_segment_tbl(i).additional_columns := l_additional_columns;
548 x_rule_segment_tbl := l_rule_segment_tbl;
549 End Loop;
550 End If;
551 End If;
552 --Call End Activity
553 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
554 x_msg_data => x_msg_data);
555 EXCEPTION
556 when OKL_API.G_EXCEPTION_ERROR then
557 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
558 p_api_name => l_api_name,
559 p_pkg_name => g_pkg_name,
560 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
561 x_msg_count => x_msg_count,
562 x_msg_data => x_msg_data,
563 p_api_type => g_api_type);
564
565 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
566 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
567 p_api_name => l_api_name,
568 p_pkg_name => g_pkg_name,
569 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
570 x_msg_count => x_msg_count,
571 x_msg_data => x_msg_data,
572 p_api_type => g_api_type);
573
574 when OTHERS then
575 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
576 p_api_name => l_api_name,
577 p_pkg_name => g_pkg_name,
578 p_exc_name => 'OTHERS',
579 x_msg_count => x_msg_count,
580 x_msg_data => x_msg_data,
581 p_api_type => g_api_type);
582
583 End Get_Rule_Def;
584
585 ---------- -- bug 3029276
586
587 --Start of Comments
588 --Procedure : Get_Name_Values
589 --Purpose : Query Name, Description for a given id and meta data sql.
590 --End of Comments
591
592 PROCEDURE Get_Name_Values ( p_api_version IN NUMBER,
593 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_count OUT NOCOPY NUMBER,
596 x_msg_data OUT NOCOPY VARCHAR2,
597 p_chr_id IN NUMBER,
598 p_segment IN FND_DESCR_FLEX_COL_USAGE_VL.APPLICATION_COLUMN_NAME%TYPE,
599 p_longlist_flag IN VARCHAR2,
600 p_ruleinfo_column IN varchar2,
601 p_name_column IN varchar2,
602 p_id1 IN VARCHAR2,
603 P_id2 IN VARCHAR2,
604 p_select_clause IN VARCHAR2,
605 p_from_clause IN VARCHAR2,
606 p_where_clause IN VARCHAR2,
607 x_name OUT NOCOPY VARCHAR2,
608 x_desc OUT NOCOPY VARCHAR2
609 ) is
610
611 l_select_clause Varchar2(2000) := p_select_clause;
612 l_from_clause Varchar2(2000) := p_from_clause;
613 l_where_clause Varchar2(6000) := p_where_clause;
614 l_sql_string VARCHAR2(32767) := '';
615 l_sql VARCHAR2(32767) := '';
616
617 l_org_id NUMBER := -99;
618 l_segment_type VARCHAR2(10) := '' ;
619
620 Type jtot_ref_curs_type is REF CURSOR;
621 jtot_ref_curs jtot_ref_curs_type;
622 Type rule_ref_curs_type is REF CURSOR;
623 rule_ref_curs jtot_ref_curs_type;
624
625 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
626 l_api_name CONSTANT VARCHAR2(30) := 'GET_NAME_VALUES';
627 l_api_version CONSTANT NUMBER := 1.0;
628
629 Begin
630
631 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
632 G_PKG_NAME,
633 p_init_msg_list,
634 G_API_VERSION,
635 p_api_version,
636 G_SCOPE,
637 x_return_status);
638
639 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
640 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
641 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
642 RAISE OKL_API.G_EXCEPTION_ERROR;
643 END IF;
644
645
646 OKL_CONTEXT.set_okc_org_context(p_chr_id => p_chr_id);
647
648 if(p_segment like 'JTOT%') then
649 l_sql_string := 'SELECT NAME, DESCRIPTION ';
650 l_sql_string := l_sql_string||l_from_clause;
651 if(l_where_clause is null) then
652 l_sql_string := l_sql_string||' WHERE ID1 ='||''''||p_id1||''''||' AND ID2 ='||''''||p_id2||'''';
653 else
654 l_sql_string := l_sql_string||l_where_clause||' AND ID1 ='||''''||p_id1||''''||' AND ID2 ='||''''||p_id2||'''';
655 end if;
656 l_sql := l_sql_string;
657
658 If (p_id1 is not null and p_id2 is not null) Then
659 open jtot_ref_curs for l_sql_string;
660 Fetch jtot_ref_curs into x_name,x_desc;
661 If jtot_ref_curs%notfound Then
662 x_name := '';
663 x_desc := '';
664 End If;
665 Close jtot_ref_curs;
666 End If;
667 else
668 l_sql_string := 'SELECT '||p_name_column||' ';
669 l_sql_string := l_sql_string||' '||p_from_clause;
670 if(l_where_clause is null) then
671 -- bug 3377730.
672 -- l_sql_string := l_sql_string||' WHERE '||p_ruleinfo_column||' = '''||to_char(p_id1)||'''';
673
674 l_sql_string := l_sql_string||' WHERE '||p_ruleinfo_column||' = '''||p_id1||'''';
675 else
676 -- bug 3377730.
677 -- l_sql_string := l_sql_string||l_where_clause||' AND '||p_ruleinfo_column||' = '''||to_char(p_id1)||'''';
678 l_sql_string := l_sql_string||l_where_clause||' AND '||p_ruleinfo_column||' = '''||p_id1||'''';
679 end if;
680
681 If(p_id1 is not null) Then
682 open rule_ref_curs for l_sql_string;
683 Fetch rule_ref_curs into x_name;
684 If rule_ref_curs%notfound Then
685 x_name := '';
686 End If;
687 Close rule_ref_curs;
688 End If;
689 End if;
690
691 --Call End Activity
692 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
693 x_msg_data => x_msg_data);
694
695 EXCEPTION
696 when OTHERS then
697 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
698 p_api_name => l_api_name,
699 p_pkg_name => g_pkg_name,
700 p_exc_name => 'OTHERS',
701 x_msg_count => x_msg_count,
702 x_msg_data => x_msg_data,
703 p_api_type => g_api_type);
704
705 End Get_Name_Values;
706
707 --Start of Comments
708 --Procedure : Get_Rules_Metadata
709 --Purpose : Gets Rule Segment Meta Data using Get_Rule_Def and
710 -- retrieve id and query names for corr. rule instances
711 --End of Comments
712
713
714 PROCEDURE Get_Rules_Metadata (p_api_version IN NUMBER,
715 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2,
719 p_rgd_code IN VARCHAR2,
720 p_rgs_code IN VARCHAR2,
721 p_buy_or_sell IN VARCHAR2,
722 p_contract_id IN OKC_K_HEADERS_B.ID%TYPE,
723 p_line_id IN OKC_K_LINES_B.ID%TYPE,
724 p_party_id IN OKC_K_PARTY_ROLES_B.ID%TYPE,
725 p_template_table IN VARCHAR2,
726 p_rule_id_column IN VARCHAR2,
727 p_entity_column IN VARCHAR2,
728 x_rule_segment_tbl OUT NOCOPY rule_segment_tbl_type2) is
729
730 l_select_clause Varchar2(2000) Default Null;
731 l_from_clause Varchar2(2000) Default Null;
732 l_where_clause Varchar2(2000) Default Null;
733 l_order_by_clause Varchar2(2000) Default Null;
734
735 l_id1_col varchar2(30) Default Null;
736 l_id2_col varchar2(30) Default Null;
737 l_rule_info_col varchar2(30) Default Null;
738 l_name_col varchar2(30) Default Null;
739 l_object_code VARCHAR2(240) default null;
740 l_object_code1 VARCHAR2(240) default null;
741
742 l_chr_id OKC_K_HEADERS_B.ID%TYPE := p_contract_id;
743 l_line_id OKC_K_LINES_B.ID%TYPE := p_line_id;
744 l_cpl_id OKC_K_PARTY_ROLES_B.ID%TYPE := p_party_id;
745 process_type VARCHAR2(30) := '';
746 x_name VARCHAR2(3000);
747 x_desc VARCHAR2(3000);
748 l_date date;
749
750 l_rule_segment_tbl rule_segment_tbl_type;
751 l_rule_segment_tbl2 rule_segment_tbl_type2;
752 --l_rulv_rec rulv_rec_type;
753
754
755 CURSOR HEADER_RULE_CSR(P_CONTRACT_ID IN OKC_K_HEADERS_B.ID%TYPE,
756 P_RGD_CODE IN VARCHAR2, P_RULE_CODE IN VARCHAR2) IS
757 SELECT RL.*
758 FROM OKC_RULE_GROUPS_B RG, OKC_RULES_B RL
759 WHERE RG.DNZ_CHR_ID = P_CONTRACT_ID
760 AND RG.CHR_ID = P_CONTRACT_ID
761 AND RG.RGD_CODE = P_RGD_CODE
762 AND RG.ID = RL.RGP_ID
763 AND RL.RULE_INFORMATION_CATEGORY = P_RULE_CODE;
764
765 CURSOR LINE_RULE_CSR(P_CONTRACT_ID IN OKC_K_HEADERS_B.ID%TYPE,
766 P_LINE_ID IN OKC_K_LINES_B.ID%TYPE,
767 P_RGD_CODE IN VARCHAR2, P_RULE_CODE IN VARCHAR2) IS
768 SELECT RL.*
769 FROM OKC_RULE_GROUPS_B RG, OKC_RULES_B RL
770 WHERE RG.DNZ_CHR_ID = P_CONTRACT_ID
771 AND RG.CHR_ID IS NULL
772 AND RG.CLE_ID = P_LINE_ID
773 AND RG.RGD_CODE = P_RGD_CODE
774 AND RG.ID = RL.RGP_ID
775 AND RL.RULE_INFORMATION_CATEGORY = P_RULE_CODE;
776
777 l_rulv_rec HEADER_RULE_CSR%ROWTYPE;
778
779 i number := 0;
780
781 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
782 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_DEF';
783 l_api_version CONSTANT NUMBER := 1.0;
784
785 Begin
786
787 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
788 G_PKG_NAME,
789 p_init_msg_list,
790 G_API_VERSION,
791 p_api_version,
792 G_SCOPE,
793 x_return_status);
794
795 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
796 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
797 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
798 RAISE OKL_API.G_EXCEPTION_ERROR;
799 END IF;
800
801
802 if(l_chr_id is null or l_chr_id = OKC_API.G_MISS_NUM) then
803 l_chr_id := -1;
804 end if;
805 if(l_line_id is null or l_line_id = OKC_API.G_MISS_NUM) then
806 l_line_id := -1;
807 end if;
808 if(l_cpl_id is null or l_cpl_id = OKC_API.G_MISS_NUM) then
809 l_cpl_id := -1;
810 end if;
811 if(l_chr_id = -1 and l_cpl_id = -1 and l_line_id = -1) then
812 process_type := 'TEMPLATE';
813 elsif(l_cpl_id = -1 and l_line_id = -1) then
814 process_type := 'HEADER';
815 elsif(l_cpl_id = -1 and l_line_id <> -1) then
816 process_type := 'LINE';
817 elsif(l_cpl_id <> -1) then
818 process_type := 'PARTY';
819 end if;
820
821 if(process_type = 'HEADER') then
822 open HEADER_RULE_CSR(p_contract_id, p_rgd_code, p_rgs_code);
823 fetch HEADER_RULE_CSR into l_rulv_rec;
824 close HEADER_RULE_CSR;
825 elsif(process_type = 'LINE') then
826 open LINE_RULE_CSR(p_contract_id, p_line_id, p_rgd_code, p_rgs_code);
827 fetch LINE_RULE_CSR into l_rulv_rec;
828 close LINE_RULE_CSR;
829 end if;
830
831
832
833 Get_Rule_Def (p_api_version => l_api_version,
834 p_init_msg_list => p_init_msg_list,
835 x_return_status => l_return_status,
836 x_msg_count => x_msg_count,
837 x_msg_data => x_msg_data,
838 p_rgd_code => p_rgd_code,
839 p_rgs_code => p_rgs_code,
840 p_buy_or_sell => p_buy_or_sell,
841 x_rule_segment_tbl => l_rule_segment_tbl);
842
843
844 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
845 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
846 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
847 RAISE OKC_API.G_EXCEPTION_ERROR;
848 END IF;
849
850
851
852 for i in 1..l_rule_segment_tbl.COUNT
853 Loop
854 l_rule_segment_tbl2(i).rgd_code := l_rule_segment_tbl(i).rgd_code;
855 l_rule_segment_tbl2(i).rgs_code := l_rule_segment_tbl(i).rgs_code;
856 l_rule_segment_tbl2(i).application_column_name := l_rule_segment_tbl(i).application_column_name;
857 l_rule_segment_tbl2(i).end_user_column_name := l_rule_segment_tbl(i).end_user_column_name;
858 l_rule_segment_tbl2(i).sequence := l_rule_segment_tbl(i).sequence;
859 l_rule_segment_tbl2(i).enabled_flag := l_rule_segment_tbl(i).enabled_flag;
860 l_rule_segment_tbl2(i).displayed_flag := l_rule_segment_tbl(i).displayed_flag;
861 l_rule_segment_tbl2(i).required_flag := l_rule_segment_tbl(i).required_flag;
862 l_rule_segment_tbl2(i).default_size := l_rule_segment_tbl(i).default_size;
863 l_rule_segment_tbl2(i).left_prompt := l_rule_segment_tbl(i).left_prompt;
864 l_rule_segment_tbl2(i).select_clause := l_rule_segment_tbl(i).select_clause;
865 l_rule_segment_tbl2(i).from_clause := l_rule_segment_tbl(i).from_clause;
866 l_rule_segment_tbl2(i).where_clause := l_rule_segment_tbl(i).where_clause;
867 l_rule_segment_tbl2(i).order_by_clause := l_rule_segment_tbl(i).order_by_clause;
868 l_rule_segment_tbl2(i).object_code := l_rule_segment_tbl(i).object_code;
869 l_rule_segment_tbl2(i).longlist_flag := l_rule_segment_tbl(i).longlist_flag;
870 l_rule_segment_tbl2(i).format_type := l_rule_segment_tbl(i).format_type;
871 l_rule_segment_tbl2(i).id1_col := l_rule_segment_tbl(i).id1_col;
872 l_rule_segment_tbl2(i).id2_col := l_rule_segment_tbl(i).id2_col;
873 l_rule_segment_tbl2(i).rule_info_col := l_rule_segment_tbl(i).rule_info_col;
874 l_rule_segment_tbl2(i).name_col := l_rule_segment_tbl(i).name_col;
875 l_rule_segment_tbl2(i).value_set_name := l_rule_segment_tbl(i).value_set_name;
876 l_rule_segment_tbl2(i).additional_columns := l_rule_segment_tbl(i).additional_columns;
877
878
879 if(l_rule_segment_tbl2(i).application_column_name = 'RULE_INFORMATION1') then
880 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information1;
881 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION2') then
882 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information2;
883 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION3') then
884 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information3;
885 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION4') then
886 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information4;
887 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION5') then
888 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information5;
889 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION6') then
890 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information6;
891 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION7') then
892 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information7;
893 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION8') then
894 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information8;
895 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION9') then
896 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information9;
897 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION10') then
898 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information10;
899 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION11') then
900 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information11;
901 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION12') then
902 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information12;
903 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION13') then
904 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information13;
905 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION14') then
906 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information14;
907 elsif (l_rule_segment_tbl(i).application_column_name = 'RULE_INFORMATION15') then
908 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.rule_information15;
909 elsif (l_rule_segment_tbl(i).application_column_name = 'JTOT_OBJECT1_CODE') then
910 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.object1_id1;
911 l_rule_segment_tbl2(i).x_id2 := l_rulv_rec.object1_id2;
912 elsif (l_rule_segment_tbl(i).application_column_name = 'JTOT_OBJECT2_CODE') then
913 -- Udhenuko Bug#5876083 Modifying the assignments to get object id for object2 code
914 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.object2_id1;
915 l_rule_segment_tbl2(i).x_id2 := l_rulv_rec.object2_id2;
916 elsif (l_rule_segment_tbl(i).application_column_name = 'JTOT_OBJECT3_CODE') then
917 -- Udhenuko Bug#5876083 Modifying the assignments to get object id for object3 code
918 l_rule_segment_tbl2(i).x_id1 := l_rulv_rec.object3_id1;
919 l_rule_segment_tbl2(i).x_id2 := l_rulv_rec.object3_id2;
920 end if;
921
922 x_name := '';
923 x_desc := '';
924
925
926 if(l_rule_segment_tbl2(i).longlist_flag = 'Y' or
927 l_rule_segment_tbl2(i).value_set_name = 'Yes_No') then
928 if(l_rule_segment_tbl2(i).select_clause is null or l_rule_segment_tbl2(i).select_clause = 'None') then
929 l_rule_segment_tbl2(i).x_segment_status := 'INVALID';
930 x_name := l_rule_segment_tbl2(i).x_id1;
931 else
932 Get_Name_Values(p_api_version => l_api_version,
933 p_init_msg_list => p_init_msg_list,
934 x_return_status => l_return_status,
935 x_msg_count => x_msg_count,
936 x_msg_data => x_msg_data,
937 p_chr_id => l_chr_id,
938 p_segment => l_rule_segment_tbl2(i).application_column_name,
939 p_longlist_flag => l_rule_segment_tbl2(i).longlist_flag,
940 p_ruleinfo_column => l_rule_segment_tbl2(i).rule_info_col,
941 p_name_column => l_rule_segment_tbl2(i).name_col,
942 p_id1 => l_rule_segment_tbl2(i).x_id1,
943 p_id2 => l_rule_segment_tbl2(i).x_id2,
944 p_select_clause => l_rule_segment_tbl2(i).select_clause,
945 p_from_clause => l_rule_segment_tbl2(i).from_clause,
946 p_where_clause => l_rule_segment_tbl2(i).where_clause,
947 x_name => x_name,
948 x_desc => x_desc);
949
950 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
951 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
952 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
953 RAISE OKC_API.G_EXCEPTION_ERROR;
954 END IF;
955 end if;
956 else
957 if(l_rule_segment_tbl2(i).value_set_name = 'FND_STANDARD_DATE' and
958 l_rule_segment_tbl2(i).x_id1 is not null) then
959 l_date := FND_DATE.canonical_to_date(l_rule_segment_tbl2(i).x_id1);
960 x_name := to_char(l_date,fnd_profile.value('ICX_DATE_FORMAT_MASK'));
961 else
962 x_name := l_rule_segment_tbl2(i).x_id1;
963 end if;
964 end if;
965
966 l_rule_segment_tbl2(i).x_name := x_name;
967 l_rule_segment_tbl2(i).x_desc := x_desc;
968
969 End Loop;
970
971 x_rule_segment_tbl := l_rule_segment_tbl2;
972
973 --Call End Activity
974 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data);
976 EXCEPTION
977 when OKL_API.G_EXCEPTION_ERROR then
978 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
979 p_api_name => l_api_name,
980 p_pkg_name => g_pkg_name,
981 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_api_type => g_api_type);
985
986 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
987 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
988 p_api_name => l_api_name,
989 p_pkg_name => g_pkg_name,
990 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
991 x_msg_count => x_msg_count,
992 x_msg_data => x_msg_data,
993 p_api_type => g_api_type);
994
995 when OTHERS then
996 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
997 p_api_name => l_api_name,
998 p_pkg_name => g_pkg_name,
999 p_exc_name => 'OTHERS',
1000 x_msg_count => x_msg_count,
1001 x_msg_data => x_msg_data,
1002 p_api_type => g_api_type);
1003 End Get_Rules_Metadata;
1004
1005
1006 -- -- end bug 3029276
1007
1008 --Start of Comments
1009 --Procedure : Get_Subclass_Rgs
1010 --Purpose : Gets Rule Groups for a subclass (Contract header id)
1011 --End of Comments
1012 Procedure Get_subclass_Rgs (p_api_version IN NUMBER,
1013 p_init_msg_list IN VARCHAR2,
1014 x_return_status OUT NOCOPY VARCHAR2,
1015 x_msg_count OUT NOCOPY NUMBER,
1016 x_msg_data OUT NOCOPY VARCHAR2,
1017 p_chr_id IN Varchar2,
1018 x_sc_rg_tbl OUT NOCOPY sc_rg_tbl_type) is
1019 Cursor scs_rgd_curs is
1020 select osrg.SCS_CODE
1021 ,osrg.RGD_CODE
1022 ,fl.Meaning
1023 ,fl.description
1024 from Fnd_Lookups fl,
1025 okc_subclass_rg_defs osrg,
1026 okc_k_headers_v chrv
1027 where fl.lookup_type = 'OKC_RULE_GROUP_DEF'
1028 and fl.enabled_flag = 'Y'
1029 and nvl(fl.start_date_active,sysdate) <= sysdate
1030 and nvl(fl.end_date_active,sysdate+1) > sysdate
1031 and fl.lookup_code = osrg.RGD_CODE
1032 and nvl(osrg.start_date,sysdate) <= sysdate
1033 and nvl(osrg.end_date,sysdate+1) > sysdate
1034 and osrg.scs_code = chrv.scs_code
1035 and chrv.id = p_chr_id;
1036 l_scs_rgd_rec scs_rgd_curs%rowtype;
1037 i NUMBER;
1038
1039 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1040 l_api_name CONSTANT VARCHAR2(30) := 'GET_SUBCLASS_RGS';
1041 l_api_version CONSTANT NUMBER := 1.0;
1042
1043 begin
1044 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1045 G_PKG_NAME,
1046 p_init_msg_list,
1047 G_API_VERSION,
1048 p_api_version,
1049 G_SCOPE,
1050 x_return_status);
1051
1052 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1053 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1054 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1055 RAISE OKL_API.G_EXCEPTION_ERROR;
1056 END IF;
1057
1058 x_return_status := l_return_status;
1059 i := 1;
1060 open scs_rgd_curs;
1061 Loop
1062 Fetch scs_rgd_curs into l_scs_rgd_rec;
1063 If scs_rgd_curs%NotFound Then
1064 Exit;
1065 Else
1066 x_sc_rg_tbl(i).scs_code := l_scs_rgd_rec.scs_code;
1067 x_sc_rg_tbl(i).rgd_code := l_scs_rgd_rec.rgd_code;
1068 x_sc_rg_tbl(i).Meaning := l_scs_rgd_rec.Meaning;
1069 x_sc_rg_tbl(i).Description := l_scs_rgd_rec.Description;
1070 i := i + 1;
1071 End If;
1072 End Loop;
1073 Close scs_rgd_curs;
1074 --Call End Activity
1075 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1076 x_msg_data => x_msg_data);
1077 EXCEPTION
1078 when OKL_API.G_EXCEPTION_ERROR then
1079 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1080 p_api_name => l_api_name,
1081 p_pkg_name => g_pkg_name,
1082 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data,
1085 p_api_type => g_api_type);
1086
1087 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1088 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1089 p_api_name => l_api_name,
1090 p_pkg_name => g_pkg_name,
1091 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1092 x_msg_count => x_msg_count,
1093 x_msg_data => x_msg_data,
1094 p_api_type => g_api_type);
1095
1096 when OTHERS then
1097 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1098 p_api_name => l_api_name,
1099 p_pkg_name => g_pkg_name,
1100 p_exc_name => 'OTHERS',
1101 x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data,
1103 p_api_type => g_api_type);
1104
1105 End Get_subclass_Rgs;
1106 --Start of Comments
1107 --Procedure : Get_Rule_Segments
1108 --Purpose : Gets Rule segments
1109 --End of Comments
1110 Procedure Get_Rule_Segments ( x_return_status OUT NOCOPY Varchar2,
1111 p_rgs_code IN Varchar2,
1112 x_segment_count OUT NOCOPY Number,
1113 x_rule_tbl OUT NOCOPY rule_tbl_type) is
1114 l_rule_tbl Rule_tbl_type;
1115 cursor rule_seg_curs is
1116 /*select dfcu.descriptive_flex_context_code
1117 , dfcu.application_column_name
1118 , dfcu.column_seq_num
1119 from fnd_lookups fl,
1120 fnd_descr_flex_col_usage_vl dfcu
1121 where fl.lookup_type = 'OKC_RULE_DEF'
1122 and fl.lookup_code = dfcu.descriptive_flex_context_code
1123 and fl.enabled_flag = 'Y'
1124 and nvl(fl.start_date_active,sysdate) <= sysdate
1125 and nvl(fl.end_date_active,sysdate+1) > sysdate
1126 and dfcu.application_id=510
1127 and dfcu.descriptive_flexfield_name='OKC Rule Developer DF'
1128 and dfcu.descriptive_flex_context_code = p_rgs_code
1129 union
1130 */
1131 --after rule striping :
1132 select dfcu.descriptive_flex_context_code
1133 , dfcu.application_column_name
1134 , dfcu.column_seq_num
1135 from okc_rule_defs_v rdfv,
1136 fnd_descr_flex_col_usage_vl dfcu
1137 where dfcu.application_id = rdfv.application_id
1138 and dfcu.descriptive_flex_context_code = rdfv.rule_code
1139 and dfcu.descriptive_flexfield_name = rdfv.DESCRIPTIVE_FLEXFIELD_NAME
1140 and rdfv.rule_code = p_rgs_code
1141 order by 3;
1142 l_rule_seg_rec rule_seg_curs%RowType;
1143 i Number;
1144 Begin
1145 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1146 Open rule_seg_curs;
1147 i := 0;
1148 Loop
1149 Fetch rule_seg_curs
1150 Into l_rule_seg_rec;
1151 If rule_seg_curs%NotFound Then
1152 Exit;
1153 Else
1154 i:= i+1;
1155 l_rule_tbl(i).rgs_code := p_rgs_code;
1156 l_rule_tbl(i).application_column_name := l_rule_seg_rec.application_column_name;
1157 l_rule_tbl(i).column_seq_num := l_rule_seg_rec.column_seq_num;
1158 End If;
1159 End Loop;
1160 Close rule_seg_curs;
1161 x_rule_tbl := l_rule_tbl;
1162 x_segment_count := i;
1163 EXCEPTION
1164 when OTHERS then
1165 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1166 p_msg_name => G_UNEXPECTED_ERROR,
1167 p_token1 => G_SQLCODE_TOKEN,
1168 p_token1_value => SQLCODE,
1169 p_token2 => G_SQLERRM_TOKEN,
1170 p_token2_value => SQLERRM);
1171 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1172 End Get_Rule_Segments;
1173 --Start of Comments
1174 --Procedure : Get_Rg_Rules
1175 --Purpose : Gets Rules segments
1176 --End of Comments
1177 Procedure Get_Rg_Rules (p_api_version IN NUMBER,
1178 p_init_msg_list IN VARCHAR2,
1179 x_return_status OUT NOCOPY VARCHAR2,
1180 x_msg_count OUT NOCOPY NUMBER,
1181 x_msg_data OUT NOCOPY VARCHAR2,
1182 p_rgd_code IN Varchar2,
1183 x_rg_rules_tbl OUT NOCOPY rg_rules_tbl_type) is
1184
1185 Cursor rg_rule_def_curs is
1186 select rgd_code,
1187 rdf_code,
1188 optional_yn,
1189 min_cardinality,
1190 max_cardinality
1191 from okc_rg_def_rules
1192 where rgd_code = p_rgd_code;
1193 l_rg_rule_def_rec rg_rule_def_curs%RowType;
1194 l_rule_tbl rule_tbl_type;
1195 l_rg_rules_tbl rg_rules_tbl_type;
1196 l_segment_count Number;
1197 i Number;
1198
1199 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1200 l_api_name CONSTANT VARCHAR2(30) := 'GET_RG_RULES';
1201 l_api_version CONSTANT NUMBER := 1.0;
1202
1203 begin
1204 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1205 G_PKG_NAME,
1206 p_init_msg_list,
1207 G_API_VERSION,
1208 p_api_version,
1209 G_SCOPE,
1210 x_return_status);
1211
1212 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1213 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1214 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1215 RAISE OKL_API.G_EXCEPTION_ERROR;
1216 END IF;
1217 x_return_status := l_return_status;
1218 Open rg_rule_def_curs;
1219 Loop
1220 Fetch rg_rule_def_curs into l_rg_rule_def_rec;
1221 If rg_rule_def_curs%NotFound Then
1222 Exit;
1223 Else
1224 Get_Rule_Segments(x_return_status => l_return_status,
1225 p_rgs_code => l_rg_rule_def_rec.rdf_code,
1226 x_segment_count => l_segment_Count,
1227 x_rule_tbl => l_rule_tbl);
1228
1229 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1230 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1231 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1232 RAISE OKC_API.G_EXCEPTION_ERROR;
1233 END IF;
1234
1235 For i in 1..l_segment_count
1236 Loop
1237 l_rg_rules_tbl(i).rgd_code := l_rg_rule_def_rec.rgd_code;
1238 l_rg_rules_tbl(i).rdf_code := l_rg_rule_def_rec.rdf_code;
1239 l_rg_rules_tbl(i).application_column_name := l_rule_tbl(i).application_column_name;
1240 l_rg_rules_tbl(i).column_seq_num := l_rule_tbl(i).column_seq_num;
1241 l_rg_rules_tbl(i).optional_yn := l_rg_rule_def_rec.optional_yn;
1242 l_rg_rules_tbl(i).min_cardinality := l_rg_rule_def_rec.min_cardinality;
1243 l_rg_rules_tbl(i).max_cardinality := l_rg_rule_def_rec.max_cardinality;
1244 End Loop;
1245 End If;
1246 End Loop;
1247 Close rg_rule_def_curs;
1248 x_rg_rules_tbl := l_rg_rules_tbl;
1249 --Call End Activity
1250 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1251 x_msg_data => x_msg_data);
1252
1253 EXCEPTION
1254 when OKL_API.G_EXCEPTION_ERROR then
1255 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1256 p_api_name => l_api_name,
1257 p_pkg_name => g_pkg_name,
1258 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1259 x_msg_count => x_msg_count,
1260 x_msg_data => x_msg_data,
1261 p_api_type => g_api_type);
1262
1263 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1264 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1265 p_api_name => l_api_name,
1266 p_pkg_name => g_pkg_name,
1267 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1268 x_msg_count => x_msg_count,
1269 x_msg_data => x_msg_data,
1270 p_api_type => g_api_type);
1271
1272 when OTHERS then
1273 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1274 p_api_name => l_api_name,
1275 p_pkg_name => g_pkg_name,
1276 p_exc_name => 'OTHERS',
1277 x_msg_count => x_msg_count,
1278 x_msg_data => x_msg_data,
1279 p_api_type => g_api_type);
1280
1281 End Get_Rg_Rules;
1282 END OKL_RULE_EXTRACT_PVT;