1 package body msd_cs_defn_utl2 as
2 /* $Header: msdcsu2b.pls 115.8 2003/11/04 18:40:57 dkang ship $ */
3
4
5 g_defn_para_list g_type_defn_para_rec_list;
6
7 Procedure build_para_list (
8 p_instance in number,
9 p_coll_cond in varchar2,
10 p_pull_cond in varchar2) is
11 Begin
12
13 if g_defn_para_list.exists(1) then
14 g_defn_para_list.delete;
15 end if;
16
17 build_para_list (
18 p_instance,
19 p_coll_cond ,
20 p_pull_cond ,
21 g_defn_para_list);
22 End;
23
24
25 /******************************** Main function *************************/
26
27 Procedure build_para_list (
28 p_instance in number,
29 p_coll_cond in varchar2,
30 p_pull_cond in varchar2,
31 p_defn_para_list in out NOCOPY g_type_defn_para_rec_list
32 ) is
33 l_list g_type_defn_para_rec_list:=g_type_defn_para_rec_list();
34 Begin
35 g_defn_para_list := g_type_defn_para_rec_list();
36 build_gen_para_list ( p_instance, p_coll_cond, l_list);
37 build_gen_para_list ( p_instance, p_pull_cond, l_list);
38 p_defn_para_list := l_list;
39 End;
40
41
42
43 /**************************************
44 Format of Additional Where Clause for CHAR type
45
46 'CHAR:Prompt_Name:ValueSet_Name:Remote_Yes_No:Multi_Yes_NO:
47 Default_Column_Name_For_Multi:Default_Value_For_Single'
48
49 CHAR: Datatype
50 Prompt_Name: Name of message for the prompt
51 ValueSet_Name: Name of ValueSet defined in planning server
52 Remote_Yes_No: Y or N flag for the value set.
53 Y means execute the value set sql stmt in remote database.
54 N means execute the value set sql stmt in the planning server.
55 Multi_Yes_No: Y or N flag to indicate whether this input parameter
56 is for the multiple input para or not
57 Default_Column_Name For Multi: Name of column in the source view, which will be
58 used in sql stmt if none of the multiple input para
59 was entered in the collection forms.
60 Default_Code_For_Single: Code that user want to default to the feild.
61 This property can only be applied to
62 local value set, remote_yes_no = NO.
63 It will be a code for the value set rather than
64 actual desc.
65 Example of this values are 'Y/N','21' for id...etc
66 **************************************/
67
68 Procedure build_gen_para_list (
69 p_instance in number,
70 p_cond in varchar2,
71 p_defn_para_list in out NOCOPY g_type_defn_para_rec_list
72 ) is
73
74 l_list g_type_defn_para_rec_list:=g_type_defn_para_rec_list();
75 /* , null, null, null, null, null, null, null, null */
76 start_pos number;
77 end_pos number;
78 para_type varchar2(10);
79
80 i number:=0;
81 l_ctr number :=0;
82 l_cnt number;
83
84 l_dblink varchar2(100) := NULL;
85 l_val_col varchar2(240) := NULL;
86 l_id_col varchar2(240) := NULL;
87 l_retcode number := 0;
88 l_remote_flag varchar2(3) := 'N';
89
90
91 Begin
92 if p_cond is null then
93 return;
94 end if;
95
96 while TRUE
97 loop
98 l_ctr := l_ctr + 1;
99 start_pos := instr(p_cond, '&&', 1, l_ctr);
100 if start_pos = 0 then
101 exit;
102 end if;
103
104 para_type := substr(p_cond, start_pos + 2, 7);
105 end_pos := instr(p_cond, '''', start_pos);
106 l_list.extend;
107
108 -- 'CHAR:Prompt_Name:ValueSet_Name:Remote_Yes_No:Multi_Yes_NO:Default_Column_Name_For_Multi:code'
109
110 IF substr(upper(para_type), 1, 5) = 'CHAR:' then /* Character type */
111 l_list(l_ctr).para_type := 'CHAR';
112 l_list(l_ctr).para_name := msd_cs_defn_utl2.get_char_property(p_cond, start_pos, end_pos, 1);
113 l_list(l_ctr).vs_name := msd_cs_defn_utl2.get_char_property(p_cond, start_pos, end_pos, 2);
114 l_remote_flag := msd_cs_defn_utl2.get_char_property(p_cond, start_pos, end_pos, 3);
115 l_list(l_ctr).multi_input_flag :=
116 nvl(upper(msd_cs_defn_utl2.get_char_property(p_cond, start_pos, end_pos, 4)), 'N');
117 /* remote dblink flag is set to Y */
118 IF (upper(l_remote_flag) = 'Y' and p_instance is not NULL) THEN
119 msd_common_utilities.get_db_link(p_instance, l_dblink, l_retcode);
120 l_list(l_ctr).sql_stmt :=
121 genereate_sql_from_vs(l_list(l_ctr).vs_name,
122 l_dblink, l_val_col,l_id_col);
123 ELSE
124 l_list(l_ctr).sql_stmt :=
125 genereate_sql_from_vs(l_list(l_ctr).vs_name, null,
126 l_val_col, l_id_col);
127 END IF;
128
129 l_list(l_ctr).default_code := msd_cs_defn_utl2.get_char_property(p_cond, start_pos, end_pos, 6);
130 IF l_list(l_ctr).default_code IS NOT NULL THEN
131 l_list(l_ctr).default_val := get_default_value(l_val_col,
132 l_id_col,
133 l_list(l_ctr).sql_stmt,
134 l_list(l_ctr).default_code);
135 /* If default value comes out to be null then make code null as well */
136 IF l_list(l_ctr).default_val is NULL THEN
137 l_list(l_ctr).default_code := NULL;
138 END IF;
139 END IF;
140 elsif substr(upper(para_type), 1, 7) = 'NUMBER:' then -- Number type
141 l_list(l_ctr).para_type := 'NUMBER';
142 l_list(l_ctr).para_name := substr(p_cond, start_pos+9, ((end_pos) - (start_pos+9))) ;
143 elsif substr(upper(para_type), 1, 5) = 'DATE:' then /* Date type */
144 l_list(l_ctr).para_type := 'DATE';
145 l_list(l_ctr).para_name := substr(p_cond, start_pos+7, ((end_pos) - (start_pos+7))) ;
146 END IF;
147
148 end loop;
149
150 IF l_list.exists(1) THEN
151 if l_list(1).para_name is not null then
152 /* Append parameters to return (IO) parameter */
153 l_cnt := p_defn_para_list.count;
154 for j in 1.. l_list.count loop
155 p_defn_para_list.extend;
156 l_cnt := l_cnt + 1;
157 p_defn_para_list(l_cnt) := l_list(j);
158 fnd_message.set_name('MSD', l_list(j).para_name);
159 p_defn_para_list(l_cnt).message := fnd_message.get;
160 end loop;
161 end if;
162 END IF;
163
164 End;
165
166
167 /**********************************************************************
168 Function to return char property in additional where clause
169 ************************************************************************/
170 -- 'CHAR:Prompt_Name:ValueSet_Name:Remote_Yes_No:Multi_Yes_NO:Default_Column_Name_For_Multi'
171
172 Function get_char_property( p_cond varchar2,
173 p_start_pos number,
174 p_end_pos number,
175 p_index number) return varchar2 IS
176
177 l_output varchar(1000);
178 l_para_start_pos NUMBER;
179 l_para_end_pos NUMBER;
180
181 BEGIN
182
183 IF (p_index is null OR p_index <= 0) THEN
184 return null;
185 END IF;
186
187 l_para_start_pos := instr(p_cond, ':', p_start_pos, p_index);
188 IF (l_para_start_pos = 0 or l_para_start_pos >= p_end_pos ) THEN
189 l_para_start_pos := 0;
190 END IF;
191
192 l_para_end_pos := instr(p_cond, ':', p_start_pos, p_index + 1);
193 IF (l_para_end_pos = 0 or l_para_end_pos >= p_end_pos ) THEN
194 l_para_end_pos := p_end_pos;
195 END IF;
196
197 IF l_para_start_pos = 0 THEN
198 l_output := NULL;
199
200 ELSE
201 l_output := substr(p_cond, l_para_start_pos + 1,
202 l_para_end_pos - (l_para_start_pos + 1) );
203 END IF;
204
205 return l_output;
206
207 END get_char_property;
208
209
210 /************************* Function generate sql from value set ****************/
211 Function genereate_sql_from_vs( p_vs_name IN varchar2,
212 p_dblink IN varchar2,
213 p_val_col IN OUT nocopy varchar2,
214 p_id_col IN OUT nocopy varchar2) return varchar2 IS
215
216 CURSOR c_vs_id IS
217 SELECT flex_value_set_id
218 FROM fnd_flex_value_sets where upper(flex_value_set_name) = upper(p_vs_name);
219
220 CURSOR c_sql(p_vs_id NUMBER) IS
221 SELECT application_table_name,
222 value_column_name, id_column_name,
223 additional_where_clause
224 FROM fnd_flex_validation_tables
225 WHERE flex_value_set_id = p_vs_id;
226
227 l_vs_id NUMBER;
228 l_table_name VARCHAR2(40);
229 l_value_col VARCHAR2(40);
230 l_id_col VARCHAR2(40);
231 l_where_clause VARCHAR2(2000);
232
233 l_sql_stmt VARCHAR2(2000);
234
235 BEGIN
236 OPEN c_vs_id;
237 FETCH c_vs_id INTO l_vs_id;
238 CLOSE c_vs_id;
239
240 IF (l_vs_id IS NOT NULL) THEN
241 OPEN c_sql(l_vs_id);
242 FETCH c_sql INTO l_table_name,
243 l_value_col,
244 l_id_col,
245 l_where_clause;
246 CLOSE c_sql;
247
248
249 l_sql_stmt := 'SELECT ' ||
250 l_value_col || ', '||
251 l_id_col || ' ' ||
252 'FROM ' || l_table_name || p_dblink || ' ' ||
253 l_where_clause;
254 END IF;
255
256 p_val_col := l_value_col;
257 p_id_col := l_id_col;
258
259 return l_sql_stmt;
260 END;
261
262
263 /************************* Function to return num count in array ****************/
264 Function counts return number is
265 Begin
266 return g_defn_para_list.count;
267 End;
268
269 /************************* Function get Default Value from Defaul code ****************/
270 Function get_default_value(p_val_col IN varchar2,
271 p_id_col IN varchar2,
272 p_sql_stmt IN varchar2,
273 p_default_code IN varchar2) return varchar2 IS
274
275 TYPE cur_type is ref cursor;
276
277 c_val cur_type;
278
279 l_sql_stmt varchar2(2000);
280 l_default_val varchar2(240) := NULL;
281
282 BEGIN
283
284 IF (p_default_code IS NOT NULL and
285 p_sql_stmt IS NOT NULL and
286 p_val_col IS NOT NULL) THEN
287 l_sql_stmt := ' SELECT ' || p_val_col ||
288 ' FROM (' || p_sql_stmt || ')' ||
289 ' WHERE ' || p_id_col || ' = :p_default_code';
290
291 OPEN c_val FOR l_sql_stmt USING p_default_code;
292 FETCH c_val INTO l_default_val;
293 CLOSE c_val;
294 END IF;
295
296 return l_default_val;
297
298 END Get_Default_Value;
299
300
301 /************************* Function get a record from arry ****************/
302 Procedure get_rec (
303 p_index in number,
304 p_message in out NOCOPY varchar2,
305 p_type in out NOCOPY varchar2,
306 p_sql_stmt IN OUT NOCOPY varchar2,
307 p_multi_flag in out NOCOPY varchar2,
308 p_default_code in out NOCOPY varchar2,
309 p_default_val in out NOCOPY varchar2) is
310 Begin
311 if g_defn_para_list.exists(p_index) then
312 if g_defn_para_list(p_index).message is not null then
313 p_message := g_defn_para_list(p_index).message;
314 else
315 p_message := g_defn_para_list(p_index).para_name;
316 end if;
317 p_type := g_defn_para_list(p_index).para_type;
318 p_sql_stmt := g_defn_para_list(p_index).sql_stmt;
319 p_multi_flag := g_defn_para_list(p_index).MULTI_INPUT_FLAG;
320 p_default_val := g_defn_para_list(p_index).default_val;
321 p_default_code:= g_defn_para_list(p_index).default_code;
322 else
323 -- p_message := g_defn_para_list(p_index).para_name;
324 p_message := null;
325 end if;
326 End;
327
328 End;
[Home] [Help]