[Home] [Help]
PACKAGE BODY: APPS.PQH_PP_DFF_UTILS
Source
1 PACKAGE BODY PQH_PP_DFF_UTILS AS
2 /* $Header: pqhppdff.pkb 120.0 2006/02/06 14:45:12 rthiagar noship $ */
3
4
5 -- =============================================================================
6 -- ~ get_concat_dff_segs :
7 -- Function returns the concatenated string of Segment Values for a given DFFG
8 -- =============================================================================
9
10 FUNCTION get_concat_dff_segs
11 (p_context_value In Varchar2
12 ,p_attribute1 In Varchar2
13 ,p_attribute2 In Varchar2
14 ,p_attribute3 In Varchar2
15 ,p_attribute4 In Varchar2
16 ,p_attribute5 In Varchar2
17 ,p_attribute6 In Varchar2
18 ,p_attribute7 In Varchar2
19 ,p_attribute8 In Varchar2
20 ,p_attribute9 In Varchar2
21 ,p_attribute10 In Varchar2
22 ,p_attribute11 In Varchar2
23 ,p_attribute12 In Varchar2
24 ,p_attribute13 In Varchar2
25 ,p_attribute14 In Varchar2
26 ,p_attribute15 In Varchar2
27 ,p_attribute16 In Varchar2
28 ,p_attribute17 In Varchar2
29 ,p_attribute18 In Varchar2
30 ,p_attribute19 In Varchar2
31 ,p_attribute20 In Varchar2
32 )
33 RETURN Varchar2 IS
34
35 -- Cursor to get Delimiter for a given DFF
36 CURSOR csr_get_delim_contxt IS
37 SELECT concatenated_segment_delimiter
38 FROM fnd_descriptive_flexs
39 WHERE descriptive_flexfield_name IN ('PER_PAY_PROPOSALS')
40 AND application_id = 800;
41
42 -- Cursor to get segments for a given DFF and Context
43 CURSOR csr_get_segments(c_context_value IN Varchar2
44 ) IS
45 SELECT descriptive_flex_context_code,application_column_name, flex_value_set_id
46 FROM fnd_descr_flex_column_usages
47 WHERE descriptive_flexfield_name = 'PER_PAY_PROPOSALS'
48 AND descriptive_flex_context_code IN ('Global Data Elements', c_context_value)
49 AND enabled_flag = 'Y'
50 AND display_flag = 'Y'
51 AND application_id = 800
52 ORDER BY decode(descriptive_flex_context_code,'Global Data Elements',0,1), column_seq_num;
53
54 l_func_name CONSTANT Varchar2(150):= g_pkg ||'pqh_concat_ddfsegs';
55
56 l_delimiter fnd_descriptive_flexs.concatenated_segment_delimiter%TYPE;
57
58 l_cnct_segs Varchar2(2000);
59 l_cnct_seg_val Varchar2(2000) ;
60 cnt_attributes integer := 0;
61 cnt_glb_data_elmnts integer := 0;
62 BEGIN
63
64 Hr_Utility.set_location('Entering: '||l_func_name, 5);
65 -- Get the Delimiter for the given DFF
66 IF g_delimiter is null then
67 OPEN csr_get_delim_contxt;
68 FETCH csr_get_delim_contxt INTO g_delimiter;
69
70 -- If Delimiter is not found then that means the passed DFF doesn't exist
71 -- for the passed application id. Raise an Error
72 IF csr_get_delim_contxt%NOTFOUND THEN
73 CLOSE csr_get_delim_contxt;
74 Hr_Utility.raise_error;
75 END IF;
76 CLOSE csr_get_delim_contxt;
77 END IF;
78
79 l_delimiter := g_delimiter;
80
81 -- Get the Segments for a given DFF and corresponding Context and
82 -- Global Data Elements Context
83
84 for l_seg_val_rec in csr_get_segments(c_context_value => p_context_value)
85 LOOP
86 l_cnct_segs := l_seg_val_rec.application_column_name;
87
88 if ((l_seg_val_rec.descriptive_flex_context_code <> 'Global Data Elements') and cnt_attributes = 0 and cnt_glb_data_elmnts=0) then
89 l_cnct_seg_val := p_context_value;
90 cnt_glb_data_elmnts := 1;
91 elsif ((l_seg_val_rec.descriptive_flex_context_code <> 'Global Data Elements') and cnt_glb_data_elmnts = 0) then
92 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_context_value;
93 cnt_glb_data_elmnts := 1;
94 end if;
95
96 if (l_cnct_segs = 'ATTRIBUTE1') then
97 if (cnt_attributes = 0) then
98 l_cnct_seg_val := p_attribute1;
99 cnt_attributes := cnt_attributes+1;
100 else
101 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute1;
102 end if;
103 elsif (l_cnct_segs = 'ATTRIBUTE2') then
104 if (cnt_attributes = 0) then
105 l_cnct_seg_val := p_attribute2;
106 cnt_attributes := cnt_attributes+1;
107 else
108 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute2;
109 end if;
110 elsif (l_cnct_segs = 'ATTRIBUTE3') then
111 if (cnt_attributes = 0) then
112 l_cnct_seg_val := p_attribute3;
113 cnt_attributes := cnt_attributes+1;
114 else
115 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute3;
116 end if;
117 elsif (l_cnct_segs = 'ATTRIBUTE4') then
118 if (cnt_attributes = 0) then
119 l_cnct_seg_val := p_attribute4;
120 cnt_attributes := cnt_attributes+1;
121 else
122 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute4;
123 end if;
124 elsif (l_cnct_segs = 'ATTRIBUTE5') then
125 if (cnt_attributes = 0) then
126 l_cnct_seg_val := p_attribute5;
127 cnt_attributes := cnt_attributes+1;
128 else
129 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute5;
130 end if;
131 elsif (l_cnct_segs = 'ATTRIBUTE6') then
132 if (cnt_attributes = 0) then
133 l_cnct_seg_val := p_attribute6;
134 cnt_attributes := cnt_attributes+1;
135 else
136 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute6;
137 end if;
138 elsif (l_cnct_segs = 'ATTRIBUTE7') then
139 if (cnt_attributes = 0) then
140 l_cnct_seg_val := p_attribute7;
141 cnt_attributes := cnt_attributes+1;
142 else
143 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute7;
144 end if;
145 elsif (l_cnct_segs = 'ATTRIBUTE8') then
146 if (cnt_attributes = 0) then
147 l_cnct_seg_val := p_attribute8;
148 cnt_attributes := cnt_attributes+1;
149 else
150 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute8;
151 end if;
152 elsif (l_cnct_segs = 'ATTRIBUTE9') then
153 if (cnt_attributes = 0) then
154 l_cnct_seg_val := p_attribute9;
155 cnt_attributes := cnt_attributes+1;
156 else
157 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute9;
158 end if;
159 elsif (l_cnct_segs = 'ATTRIBUTE10') then
160 if (cnt_attributes = 0) then
161 l_cnct_seg_val := p_attribute10;
162 cnt_attributes := cnt_attributes+1;
163 else
164 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute10;
165 end if;
166 elsif (l_cnct_segs = 'ATTRIBUTE11') then
167 if (cnt_attributes = 0) then
168 l_cnct_seg_val := p_attribute11;
169 cnt_attributes := cnt_attributes+1;
170 else
171 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute11;
172 end if;
173 elsif (l_cnct_segs = 'ATTRIBUTE12') then
174 if (cnt_attributes = 0) then
175 l_cnct_seg_val := p_attribute12;
176 cnt_attributes := cnt_attributes+1;
177 else
178 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute12;
179 end if;
180 elsif (l_cnct_segs = 'ATTRIBUTE13') then
181 if (cnt_attributes = 0) then
182 l_cnct_seg_val := p_attribute13;
183 cnt_attributes := cnt_attributes+1;
184 else
185 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute13;
186 end if;
187 elsif (l_cnct_segs = 'ATTRIBUTE14') then
188 if (cnt_attributes = 0) then
189 l_cnct_seg_val := p_attribute14;
190 cnt_attributes := cnt_attributes+1;
191 else
192 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute14;
193 end if;
194 elsif (l_cnct_segs = 'ATTRIBUTE15') then
195 if (cnt_attributes = 0) then
196 l_cnct_seg_val := p_attribute15;
197 cnt_attributes := cnt_attributes+1;
198 else
199 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute15;
200 end if;
201 elsif (l_cnct_segs = 'ATTRIBUTE16') then
202 if (cnt_attributes = 0) then
203 l_cnct_seg_val := p_attribute16;
204 cnt_attributes := cnt_attributes+1;
205 else
206 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute16;
207 end if;
208 elsif (l_cnct_segs = 'ATTRIBUTE17') then
209 if (cnt_attributes = 0) then
210 l_cnct_seg_val := p_attribute17;
211 cnt_attributes := cnt_attributes+1;
212 else
213 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute17;
214 end if;
215 elsif (l_cnct_segs = 'ATTRIBUTE18') then
216 if (cnt_attributes = 0) then
217 l_cnct_seg_val := p_attribute18;
218 cnt_attributes := cnt_attributes+1;
219 else
220 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute18;
221 end if;
222 elsif (l_cnct_segs = 'ATTRIBUTE19') then
223 if (cnt_attributes = 0) then
224 l_cnct_seg_val := p_attribute19;
225 cnt_attributes := cnt_attributes+1;
226 else
227 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute19;
228 end if;
229 elsif (l_cnct_segs = 'ATTRIBUTE20') then
230 if (cnt_attributes = 0) then
231 l_cnct_seg_val := p_attribute20;
232 cnt_attributes := cnt_attributes+1;
233 else
234 l_cnct_seg_val := l_cnct_seg_val || l_delimiter || p_attribute20;
235 end if;
236 end if;
237 END LOOP;
238
239
240 RETURN l_cnct_seg_val;
241
242 Hr_Utility.set_location('Leaving: '||l_func_name, 10);
243
244 END get_concat_dff_segs;
245 END PQH_PP_DFF_UTILS;
246