1 PACKAGE BODY PQP_OSS_DFF_UTILS AS
2 /* $Header: pqphrossutil.pkb 120.0 2005/05/29 02:21:32 appldev noship $ */
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 END PQP_OSS_DFF_UTILS;
219
[Home] [Help]