1 PACKAGE BODY PQP_OSS_DFF_UTILS AS
2 /* $Header: pqphrossutil.pkb 120.2 2012/01/19 07:40:06 rpahune ship $ */
3
4 -- =============================================================================
5 -- ~ parse_segment :
6 -- Function checks the Segment if it has Value Set 'FND_DATE' attached to it
7 -- If so then formats it in correct format and returns it
8 -- Else it simply returns the segment value back
9 -- Later this function can also be used to other operations also
10 -- =============================================================================
11
12 FUNCTION parse_segment (p_seg_valset_rec IN t_seg_valsetid)
13 RETURN Varchar2 IS
14
15 -- Cursor to check if the passed segment is a Date Field
16 CURSOR csr_get_valset_name (c_val_set_id IN
17 fnd_descr_flex_column_usages.flex_value_set_id%TYPE) IS
18 SELECT flex_value_set_name
19 FROM fnd_flex_value_sets
20 WHERE flex_value_set_id = c_val_set_id;
21
22 l_flx_valset_name fnd_flex_value_sets.flex_value_set_name%TYPE;
23 l_return_seg Varchar2(2000);
24
25 BEGIN
26
27 OPEN csr_get_valset_name (c_val_set_id => p_seg_valset_rec.flx_valset_id);
28 FETCH csr_get_valset_name INTO l_flx_valset_name;
29 CLOSE csr_get_valset_name;
30
31 IF l_flx_valset_name = 'FND_STANDARD_DATE' THEN
32
33 l_return_seg := 'fnd_date.canonical_to_date(P_' ||
34 p_seg_valset_rec.seg_name || ')';
35
36 ELSE
37
38 l_return_seg := 'P_' || p_seg_valset_rec.seg_name;
39
40 END IF;
41 RETURN l_return_seg;
42
43 END parse_segment;
44
45 -- =============================================================================
46 -- ~ get_concat_dff_segs :
47 -- Function returns the concatenated string of Segment Values for a given DFF
48 -- =============================================================================
49
50 FUNCTION get_concat_dff_segs
51 (p_ddf_name IN Varchar2
52 ,p_dp_view_name IN Varchar2
53 ,p_batch_id IN Number
54 ,p_app_id IN Number
55 ,p_link_value IN Number)
56 RETURN Varchar2 IS
57
58 -- Cursor to get Delimiter and the Context for a given DFF
59 CURSOR csr_get_delim_contxt (c_ddf_name IN Varchar2
60 ,c_app_id IN Number) IS
61 SELECT concatenated_segment_delimiter, context_column_name
62 FROM fnd_descriptive_flexs
63 WHERE descriptive_flexfield_name IN (c_ddf_name)
64 AND application_id = c_app_id;
65
66 -- Cursor to get segments for a given DFF and Context
67 CURSOR csr_get_segments(c_ddf_name IN Varchar2
68 ,c_context_value IN Varchar2
69 ,c_app_id IN Number) IS
70 SELECT application_column_name, flex_value_set_id
71 FROM fnd_descr_flex_column_usages
72 WHERE descriptive_flexfield_name = c_ddf_name
73 AND descriptive_flex_context_code IN ('Global Data Elements', c_context_value)
74 AND enabled_flag = 'Y'
75 AND display_flag = 'Y'
76 AND application_id = c_app_id
77 ORDER BY descriptive_flex_context_code, column_seq_num;
78
79 -- Dynamic Ref Cursor
80 TYPE ref_cur_typ IS REF CURSOR;
81 csr_get_dff_ctx_val ref_cur_typ;
82 csr_get_cnct_segs ref_cur_typ;
83
84 l_func_name CONSTANT Varchar2(150):= g_pkg ||'pqp_oss_get_concat_ddfsegs';
85
86 l_delimiter fnd_descriptive_flexs.concatenated_segment_delimiter%TYPE;
87 l_dff_ctx_val fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE;
88
89 l_delim_ctx_rec t_delim_contxt;
90 l_seg_val_rec t_seg_valsetid;
91
92 l_dyn_sql_qry Varchar(500);
93 l_cnct_segs Varchar2(2000);
94 l_ck_all_null_segvals Varchar2(2000);
95
96 BEGIN
97
98 Hr_Utility.set_location('Entering: '||l_func_name, 5);
99
100 -- Get the Delimiter and Context for the given DFF
101 OPEN csr_get_delim_contxt(c_ddf_name => p_ddf_name
102 ,c_app_id => p_app_id
103 );
104 FETCH csr_get_delim_contxt INTO l_delim_ctx_rec;
105
106 -- If Delimiter is not found then that means the passed DFF doesn't exist for
107 -- the passed application id. Raise an Error
108 IF csr_get_delim_contxt%NOTFOUND THEN
109 CLOSE csr_get_delim_contxt;
110 Hr_Utility.raise_error;
111 END IF;
112 CLOSE csr_get_delim_contxt;
113
114
115
116 -- Get a Context Value from Data Pump Interface Values
117 -- Prepare a string for Dynamic SQL
118 -- P_ has been concatenated since all the column names of DP views are
119 -- suffixed by P_
120 l_dyn_sql_qry := 'SELECT P_' || l_delim_ctx_rec.con_col_name ||
121 ' FROM ' || p_dp_view_name ||
122 ' WHERE batch_id = ' || p_batch_id;
123
124 IF p_link_value IS NOT NULL THEN
125 l_dyn_sql_qry := l_dyn_sql_qry ||' AND link_value = ' || p_link_value;
126 END IF;
127
128 OPEN csr_get_dff_ctx_val FOR l_dyn_sql_qry;
129 FETCH csr_get_dff_ctx_val INTO l_dff_ctx_val;
130 CLOSE csr_get_dff_ctx_val;
131
132
133
134
135 -- Get the Segments for a given DFF and corresponding Context &
136 -- Global Data Elements Context
137 OPEN csr_get_segments(c_ddf_name => p_ddf_name
138 ,c_context_value => l_dff_ctx_val
139 ,c_app_id => p_app_id
140 );
141 FETCH csr_get_segments INTO l_seg_val_rec;
142 -- If there is no segment returned by the cursor that means,
143 -- there is some issue with parameter values to function and return null
144 IF csr_get_segments%NOTFOUND THEN
145 CLOSE csr_get_segments;
146 RETURN NULL;
147 END IF;
148
149
150
151
152 -- Prepare a dynamic SQL string to get the Concatenated String
153 -- for all the segments enabled and displayable for a DFF.
154 --
155 -- Also prepare a "l_ck_all_null_segvals" if all the segment values have
156 -- NULL value in Interface Tables. If that is the case then we return NULL
157 -- This is required because in some DFFs like 'Person Developer DF'
158 -- Context value in the Data Pump Interface Tables is entered even if user
159 -- didn't enter any data for the DFF segments. For such DFFs if no data has
160 -- been entered in the segments, return NULL
161
162 l_delimiter := l_delim_ctx_rec.con_seg_delim;
163
164 IF l_dff_ctx_val IS NOT NULL THEN
165
166 l_dyn_sql_qry := 'SELECT ''' || l_dff_ctx_val || l_delimiter ||
167 '''|| ' || parse_segment(l_seg_val_rec);
168 l_ck_all_null_segvals := l_dff_ctx_val || l_delimiter;
169
170 ELSE
171
172 l_dyn_sql_qry := 'SELECT ' || parse_segment(l_seg_val_rec);
173
174 END IF;
175
176
177 -- Loop to prepare the dynamic SQL for all the Segments for a given Context
178 LOOP
179 FETCH csr_get_segments INTO l_seg_val_rec;
180 EXIT WHEN csr_get_segments%NOTFOUND;
181
182 l_dyn_sql_qry := l_dyn_sql_qry || ' ||''' || l_delimiter ||
183 ''' || ' || parse_segment(l_seg_val_rec);
184 l_ck_all_null_segvals := l_ck_all_null_segvals || l_delimiter;
185 END LOOP;
186
187
188 CLOSE csr_get_segments;
189
190 l_dyn_sql_qry := l_dyn_sql_qry || ' FROM ' || p_dp_view_name ||
191 ' WHERE batch_id = ' || p_batch_id;
192
193 IF p_link_value IS NOT NULL THEN
194 l_dyn_sql_qry := l_dyn_sql_qry ||' AND link_value = ' || p_link_value;
195 END IF;
196
197
198
199 -- Execute the query to get the concatenated Segment Vaues String
200 OPEN csr_get_cnct_segs FOR l_dyn_sql_qry;
201 FETCH csr_get_cnct_segs INTO l_cnct_segs;
202 CLOSE csr_get_cnct_segs;
203
204
205 -- This check is required because in some DFFs like 'Person Developer DF'
206 -- Context value in the Data Pump Interface Tables is entered even if user
207 -- didn't enter any data for the DFF segments. For such DFFs if no data has
208 -- been entered in the segments, return NULL
209 IF l_ck_all_null_segvals = l_cnct_segs THEN
210 RETURN NULL;
211 ELSE
212 RETURN l_cnct_segs;
213 END IF;
214
215 Hr_Utility.set_location('Leaving: '||l_func_name, 10);
216
217 END get_concat_dff_segs;
218
219 -- =============================================================================
220 -- ~ Get Key Flexfield breakup segments:
221 -- =============================================================================
222 FUNCTION Get_Breakup_KFF_Segment(application_short_name VARCHAR2,
223 key_flex_code VARCHAR2,
224 structure_number NUMBER,
225 combination_id NUMBER,
226 segment_number NUMBER) RETURN VARCHAR2 IS
227
228 l_segment_value VARCHAR2(300);
229 l_count_segments NUMBER;
230 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
231 l_bool BOOLEAN;
232 l_disp_no NUMBER;
233 l_segment_array FND_FLEX_EXT.SEGMENTARRAY ;
234 l_count NUMBER;
235
236 CURSOR get_seg_order(c_keyflex_structure IN NUMBER,
237 c_id_flex_code IN VARCHAR2) IS
238 SELECT substr(application_column_name,8,2)
239 FROM fnd_id_flex_segments_vl
240 WHERE id_flex_code = c_id_flex_code
241 AND id_flex_num = c_keyflex_structure
242 AND enabled_flag = 'Y'
243 AND display_flag = 'Y'
244 ORDER BY segment_num;
245 BEGIN
246 l_bool := fnd_flex_ext.get_segments(application_short_name,
247 key_flex_code ,
248 structure_number,
249 combination_id,
250 l_count_segments,
251 l_base_segments);
252
253 --Initializing the segment Array
254 FOR i IN 1..30 LOOP
255 l_segment_array(i):=null;
256 END LOOP;
257
258 l_count :=1;
259 -- Get the segment order of KFF which are enabled and displayed
260 OPEN get_seg_order(c_keyflex_structure=>structure_number,
261 c_id_flex_code => key_flex_code);
262 LOOP
263 FETCH get_seg_order INTO l_disp_no;
264 EXIT WHEN get_seg_order%NOTFOUND;
265 IF(l_count_segments <> 0) THEN
266 --Assigning the segment values to the segment array in the order of segments
267 l_segment_array(l_disp_no) := l_base_segments(l_count);
268 END IF;
269 l_count := l_count +1;
270 END LOOP;
271 CLOSE get_seg_order;
272 --Return the segment value corresponding to the segment number from the segment array
273 IF(l_segment_array(segment_number) IS NOT NULL) THEN
274 l_segment_value :=l_segment_array(segment_number);
275 ELSE
276 l_segment_value :=null;
277 END IF;
278 RETURN l_segment_value ;
279 END Get_Breakup_KFF_Segment;
280
281 END PQP_OSS_DFF_UTILS;
282
[Home] [Help]