1 PACKAGE BODY JTF_BRM_UTILITY_PVT AS
2 /* $Header: jtfvbutb.pls 115.8 2002/02/14 13:18:30 pkm ship $ */
3
4 FUNCTION Attribute_Format
5 /************************************************************
6 ** This function is used in the JTF_BRM_ATTR_VALUES_V view
7 ** to retriev and format the workflow attribute value so
8 ** it can be queried as a text item in the JTFBRWKB form
9 ************************************************************/
10 ( p_attribute_type IN VARCHAR2
11 , p_text_value IN VARCHAR2
12 , p_number_value IN NUMBER
13 , p_date_value IN DATE
14 , p_format IN VARCHAR2
15 )RETURN VARCHAR2
16 IS
17 CURSOR c_lookup
18 (b_lookup_type IN VARCHAR2
19 ,b_lookup_code IN VARCHAR2
20 )IS SELECT meaning
21 FROM wf_lookups
22 WHERE lookup_type = b_lookup_type
23 AND lookup_code = b_lookup_code;
24
25 l_meaning VARCHAR2(80);
26
27 BEGIN
28 IF (p_attribute_type = 'VARCHAR2')
29 THEN
30 RETURN p_text_value;
31 ELSIF (p_attribute_type = 'NUMBER')
32 THEN
33 RETURN to_char(p_number_value);
34 ELSIF (p_attribute_type = 'DATE')
35 THEN
36 /*********************************************************************
37 ** If a format mask is defined in Workflow use it
38 *********************************************************************/
39 IF (p_format IS NOT NULL)
40 THEN
41 RETURN to_char(p_date_value,p_format);
42 ELSE
43 /*********************************************************************
44 ** If no format mask is defined use the default APPS format
45 *********************************************************************/
46 RETURN to_char(p_date_value,'DD/MM/YYYY');
47 END IF;
48 ELSIF (p_attribute_type = 'LOOKUP')
49 THEN
50 /*********************************************************************
51 ** go and lookup the meaning, so it can be displayed
52 *********************************************************************/
53 IF (c_lookup%ISOPEN)
54 THEN
55 CLOSE c_lookup;
56 END IF;
57 OPEN c_lookup(p_format
58 ,p_text_value
59 );
60 FETCH c_lookup INTO l_meaning;
61 IF (c_lookup%FOUND)
62 THEN
63 CLOSE c_lookup;
64 RETURN l_meaning;
65 ELSE
66 CLOSE c_lookup;
67 RETURN NULL;
68 END IF;
69 ELSE
70 RETURN NULL;
71 END IF;
72 END Attribute_Format;
73
74
75 FUNCTION Attribute_Code
76 /************************************************************
77 **
78 ** - If an attribute lookup value is specified for the rule
79 ** the LOOKUP_CODE will be returned.
80 ** - If no attribute lookup value is specified for the rule
81 ** the default value will be returned
82 **
83 ************************************************************/
84 (p_rule_id IN VARCHAR2
85 ,p_wf_item_type IN VARCHAR2
86 ,p_wf_process_name IN VARCHAR2
87 ,p_wf_attribute_name IN VARCHAR2
88 )RETURN VARCHAR2
89 IS
90
91 CURSOR c_set
92 ( b_rule_id IN VARCHAR2
93 , b_wf_item_type IN VARCHAR2
94 , b_wf_process_name IN VARCHAR2
95 , b_wf_attribute_name IN VARCHAR2
96 )IS SELECT val.wf_attribute_type attribute_type
97 , val.text_value attribute_value
98 FROM jtf_brm_wf_attr_values_v val
99 , jtf_brm_processes pro
100 WHERE pro.rule_id = b_rule_id
101 AND pro.workflow_item_type = b_wf_item_type
102 AND pro.process_id = val.wf_process_id
103 AND pro.workflow_item_type = val.wf_item_type
104 AND pro.workflow_process_name = val.wf_process_name
105 AND val.wf_process_name = b_wf_process_name
106 AND val.wf_attribute_name = b_wf_attribute_name;
107
108 CURSOR c_default
109 ( b_wf_item_type IN VARCHAR2
110 , b_wf_process_name IN VARCHAR2
111 , b_wf_attribute_name IN VARCHAR2
112 )IS SELECT wf1.text_default attribute_value
113 FROM wf_activity_attributes_vl wf1
114 WHERE wf1.activity_version = (SELECT max(wf2.activity_version)
115 FROM wf_activity_attributes_vl wf2
116 WHERE wf2.activity_item_type = wf1.activity_item_type
117 AND wf2.activity_name = wf1.activity_name
118 )
119 AND wf1.activity_item_type = b_wf_item_type
120 AND wf1.activity_name = b_wf_process_name
121 AND wf1.name = b_wf_attribute_name;
122
123
124 r_set c_set%ROWTYPE;
125 r_default c_default%ROWTYPE;
126
127 BEGIN
128 IF (c_set%ISOPEN)
129 THEN
130 CLOSE c_set;
131 END IF;
132
133 OPEN c_set(p_rule_id
134 ,p_wf_item_type
135 ,p_wf_process_name
136 ,p_wf_attribute_name
137 );
138 FETCH c_set INTO r_set;
139 IF (c_set%FOUND)
140 THEN
141 CLOSE c_set;
142 IF (r_set.attribute_type = 'LOOKUP')
143 THEN
144 RETURN r_set.attribute_value;
145 ELSE
146 RETURN NULL;
147 END IF;
148 ELSE
149 CLOSE c_set;
150 IF (c_default%ISOPEN)
151 THEN
152 CLOSE c_default;
153 END IF;
154 OPEN c_default(p_wf_item_type
155 ,p_wf_process_name
156 ,p_wf_attribute_name
157 );
158 FETCH c_default INTO r_default;
159 IF (c_default%FOUND)
160 THEN
161 CLOSE c_default;
162 RETURN r_default.attribute_value;
163 ELSE
164 CLOSE c_default;
165 RETURN NULL;
166 END IF;
167 END IF;
168 EXCEPTION
169 WHEN OTHERS
170 THEN
171 RETURN NULL;
172 END Attribute_Code;
173
174 FUNCTION Attribute_Meaning
175 /************************************************************
176 **
177 ** - If an attribute lookup value is specified for the rule
178 ** the Meaning will be returned.
179 ** - If no attribute lookup value is specified for the rule
180 ** the default value will be returned
181 **
182 ************************************************************/
183 (p_rule_id IN VARCHAR2
184 ,p_wf_item_type IN VARCHAR2
185 ,p_wf_process_name IN VARCHAR2
186 ,p_wf_attribute_name IN VARCHAR2
187 )RETURN VARCHAR2
188 IS
189
190 CURSOR c_set
191 ( b_rule_id IN VARCHAR2
192 , b_wf_item_type IN VARCHAR2
193 , b_wf_process_name IN VARCHAR2
194 , b_wf_attribute_name IN VARCHAR2
195 )IS SELECT val.wf_attribute_type attribute_type
196 , val.text_value attribute_value
197 , wlu.meaning attribute_meaning
198 FROM jtf_brm_wf_attr_values_v val
199 , jtf_brm_processes pro
200 , wf_lookups wlu
201 WHERE pro.rule_id = b_rule_id
202 AND pro.workflow_item_type = b_wf_item_type
203 AND pro.process_id = val.wf_process_id
204 AND pro.workflow_item_type = val.wf_item_type
205 AND pro.workflow_process_name = val.wf_process_name
206 AND val.wf_process_name = b_wf_process_name
207 AND val.wf_attribute_name = b_wf_attribute_name
208 AND wlu.lookup_type = val.wf_attribute_format
209 AND wlu.lookup_code = val.text_value
210 ;
211
212 CURSOR c_default
213 ( b_wf_item_type IN VARCHAR2
214 , b_wf_process_name IN VARCHAR2
215 , b_wf_attribute_name IN VARCHAR2
216 )IS SELECT wf1.text_default attribute_value
217 , wlu.meaning attribute_meaning
218 FROM wf_activity_attributes_vl wf1
219 , wf_lookups wlu
220 WHERE wf1.activity_version = (SELECT max(wf2.activity_version)
221 FROM wf_activity_attributes_vl wf2
222 WHERE wf2.activity_item_type = wf1.activity_item_type
223 AND wf2.activity_name = wf1.activity_name
224 )
225 AND wf1.activity_item_type = b_wf_item_type
226 AND wf1.activity_name = b_wf_process_name
227 AND wf1.name = b_wf_attribute_name
228 AND wlu.lookup_type = wf1.format
229 AND wlu.LOOKUP_CODE = wf1.text_default
230 ;
231
232 r_set c_set%ROWTYPE;
233 r_default c_default%ROWTYPE;
234
235 BEGIN
236 IF (c_set%ISOPEN)
237 THEN
238 CLOSE c_set;
239 END IF;
240
241 OPEN c_set(p_rule_id
242 ,p_wf_item_type
243 ,p_wf_process_name
244 ,p_wf_attribute_name
245 );
246 FETCH c_set INTO r_set;
247 IF (c_set%FOUND)
248 THEN
249 CLOSE c_set;
250 IF (r_set.attribute_type = 'LOOKUP')
251 THEN
252 RETURN r_set.attribute_meaning;
253 ELSE
254 RETURN NULL;
255 END IF;
256 ELSE
257 CLOSE c_set;
258 IF (c_default%ISOPEN)
259 THEN
260 CLOSE c_default;
261 END IF;
262 OPEN c_default(p_wf_item_type
263 ,p_wf_process_name
264 ,p_wf_attribute_name
265 );
266 FETCH c_default INTO r_default;
267 IF (c_default%FOUND)
268 THEN
269 CLOSE c_default;
270 RETURN r_default.attribute_meaning;
271 ELSE
272 CLOSE c_default;
273 RETURN NULL;
274 END IF;
275 END IF;
276 EXCEPTION
277 WHEN OTHERS
278 THEN
279 RETURN NULL;
280 END Attribute_Meaning;
281
282 END JTF_BRM_UTILITY_PVT;
[Home] [Help]