[Home] [Help]
PACKAGE BODY: APPS.PSP_AUTO_DYN
Source
1 PACKAGE BODY psp_auto_dyn AS
2 --$Header: PSPAUDYB.pls 120.5 2006/07/24 13:43:58 tbalacha ship $
3
4 --------------------------------------------------------------------
5 -------- PROCEDURE CREATE_DYN_DATA ------------
6 --------------------------------------------------------------------
7 -- This procedure is used to create new dynamic sql statement,
8 -- param_value_set statement and the bind_var and validation_type
9
10
11 PROCEDURE create_dyn_data(p_parameter_class IN VARCHAR2,
12 p_parameter IN VARCHAR2,
13 p_appl_column_name IN VARCHAR2,
14 p_dff_col_name IN VARCHAR2,
15 p_dff_context_code IN VARCHAR2,
16 p_flex_val_set_id IN NUMBER,
17 p_dyn_sql_stmt OUT NOCOPY VARCHAR2,
18 p_bind_var OUT NOCOPY VARCHAR2,
19 p_validation_type OUT NOCOPY VARCHAR2,
20 p_param_value_set OUT NOCOPY VARCHAR2) IS
21 sob_id number(15) := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
22 BEGIN
23
24 ------------------------------------------------
25 --- Parameter Class -> Assignment
26 ------------------------------------------------
27
28 IF p_parameter_class = 'Assignment'
29 THEN
30 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PER_ALL_ASSIGNMENTS_F WHERE ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE';
31 p_bind_var:= 'l_assignment_id' ;
32 p_validation_type := 'T';
33 p_param_value_set:='SELECT DISTINCT '||p_parameter||' FROM PER_ASSIGNMENTS_F ORDER BY 1';
34
35 ------------------------------------------------
36 --- Parameter Class -> Person
37 ------------------------------------------------
38
39 ELSIF p_parameter_class = 'Person'
40 THEN
41 p_dyn_sql_stmt := 'SELECT '||p_parameter|| ' FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = :VAR1 AND :EFFDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE';
42 p_bind_var:= 'l_person_id' ;
43 p_validation_type := 'T';
44 p_param_value_set:='SELECT DISTINCT '|| p_parameter||' FROM PER_PEOPLE_F ORDER BY 1';
45
46 ------------------------------------------------
47 --- Parameter Class -> Elements
48 ------------------------------------------------
49
50 ELSIF p_parameter_class = 'Elements'
51 THEN
52 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PAY_ELEMENT_TYPES_F WHERE ELEMENT_TYPE_ID=:VAR1 AND :EFFDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,:EFFDATE)';
53 p_bind_var:= 'l_element_type_id' ;
54 p_validation_type := 'T';
55 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PAY_ELEMENT_TYPES_F ORDER BY 1' ;
56
57 ------------------------------------------------
58 --- Parameter Class -> Projects
59 ------------------------------------------------
60 ELSIF p_parameter_class = 'Projects'
61 then
62 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_PROJECTS_ALL WHERE PROJECT_ID = :VAR1' ;
63 p_bind_var:= 'l_project_id' ;
64 p_validation_type := 'T';
65 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_PROJECTS ORDER BY 1';
66
67 ------------------------------------------------
68 --- Parameter Class -> Tasks
69 ------------------------------------------------
70
71 ELSIF p_parameter_class = 'Tasks'
72 THEN
73 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS WHERE TASK_ID = :VAR1' ;
74 p_bind_var:= 'l_task_id' ;
75 p_validation_type := 'T';
76 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS ORDER BY 1';
77
78 ------------------------------------------------
79 --- Parameter Class -> Awards
80 ------------------------------------------------
81
82 ELSIF p_parameter_class = 'Awards'
83 THEN
84 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM GMS_AWARDS_ALL WHERE AWARD_ID = :VAR1' ;
85 p_bind_var:= 'l_award_id' ;
86 p_validation_type := 'T';
87 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM GMS_AWARDS ORDER BY 1';
88
89 ------------------------------------------------
90 --- Parameter Class -> Expenditure Type
91 ------------------------------------------------
92 ELSIF p_parameter_class = 'Expenditure Type'
93 THEN
94 p_dyn_sql_stmt := 'NULL';
95 p_bind_var:= null;
96 p_validation_type := 'T';
97 p_param_value_set := 'SELECT EXPENDITURE_TYPE FROM PA_EXPENDITURE_TYPES_EXPEND_V WHERE SYSTEM_LINKAGE_FUNCTION IN (''ST'') ORDER BY EXPENDITURE_TYPE';
98 ------------------------------------------------
99 --- Parameter Class -> Position Flexfield
100 ------------------------------------------------
101
102 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
103 --for the variable -> p_param_value_set
104
105 ELSIF p_parameter_class = 'Position Flexfield'
106 THEN
107
108 p_dyn_sql_stmt := 'SELECT PPD.'|| p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA, PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
109 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE AND PA.POSITION_ID = HAP.POSITION_ID
110 AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE) AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID' ;
111 p_bind_var:= 'l_assignment_id' ;
112 p_validation_type := 'T';
113 p_param_value_set := 'SELECT DISTINCT PPD.'||p_appl_column_name||' FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
114 WHERE HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID
115 AND HAP.BUSINESS_GROUP_ID= to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)';
116
117 ------------------------------------------------
118 --- Parameter Class -> Job Flexfield
119 ------------------------------------------------
120
121 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
122 --for the variable -> p_param_value_set
123
124 ELSIF p_parameter_class = 'Job Flexfield'
125 THEN
126 p_dyn_sql_stmt := 'SELECT PJD.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA, PER_JOB_DEFINITIONS PJD, PER_JOBS PJ
127 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
128 AND PA.JOB_ID = PJ.JOB_ID AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID' ;
129 p_bind_var:= 'l_assignment_id' ;
130 p_validation_type := 'T';
131 p_param_value_set := 'SELECT DISTINCT PJD.'||p_appl_column_name||' FROM PER_JOB_DEFINITIONS PJD,PER_JOBS PJ WHERE PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID AND PJ.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;
132
133 ------------------------------------------------
134 --- Parameter Class -> Grade Flexfield
135 ------------------------------------------------
136
137 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
138 --for the variable -> p_param_value_set
139
140 ELSIF p_parameter_class = 'Grade Flexfield'
141 THEN
142 p_dyn_sql_stmt := 'SELECT PGD.'||p_appl_column_name||' FROM per_all_assignments_f PA, PER_GRADE_DEFINITIONS PGD, PER_GRADES PG
143 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE) AND PA.GRADE_ID = PG.GRADE_ID AND PG.GRADE_DEFINITION_ID = PGD.GRADE_DEFINITION_ID' ;
144 p_bind_var:= 'l_assignment_id' ;
145 p_validation_type := 'T';
146 p_param_value_set := 'SELECT DISTINCT PGD.'||p_appl_column_name||' FROM PER_GRADE_DEFINITIONS PGD,PER_GRADES PG WHERE PG.GRADE_DEFINITION_ID = PGD.GRADE_DEFINITION_ID
147 AND PG.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;
148
149 ------------------------------------------------
150 --- Parameter Class -> People Group Flexfield
151 ------------------------------------------------
152
153 ELSIF p_parameter_class = 'People Group Flexfield'
154 THEN
155 p_dyn_sql_stmt := 'SELECT PPG.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA,PAY_PEOPLE_GROUPS PPG WHERE PA.ASSIGNMENT_ID = :VAR1
156 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE) AND PA.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID' ;
157 p_bind_var:= 'l_assignment_id' ;
158 p_validation_type := 'T';
159 p_param_value_set := 'SELECT DISTINCT PPG.'||p_appl_column_name||' FROM PAY_PEOPLE_GROUPS PPG' ;
160
161 ------------------------------------------------
162 --- Parameter Class -> Cost Allocation Flexfield
163 ------------------------------------------------
164 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
165 --for the variable -> p_param_value_set
166
167 ELSIF p_parameter_class = 'Cost Allocation Flexfield'
168 THEN
169 p_dyn_sql_stmt := 'SELECT PCAK.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA,PAY_COST_ALLOCATION_KEYFLEX PCAK,PAY_ALL_PAYROLLS_F PP WHERE PA.ASSIGNMENT_ID = :VAR1
170 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE AND PA.PAYROLL_ID = PP.PAYROLL_ID AND PP.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID' ;
171 p_bind_var:= 'l_assignment_id' ;
172 p_validation_type := 'T';
173 p_param_value_set := 'SELECT DISTINCT PCAK.'||p_appl_column_name||' FROM PAY_COST_ALLOCATION_KEYFLEX PCAK, PAY_COST_ALLOCATIONS_F PCAF
174 where PCAK.COST_ALLOCATION_KEYFLEX_ID=PCAF.COST_ALLOCATION_KEYFLEX_ID AND PCAF.BUSINESS_GROUP_ID=TO_NUMBER(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;
175 ------------------------------------------------
176 --- Parameter Class -> Personal Analysis Flexfield
177 ------------------------------------------------
178 ELSIF p_parameter_class = 'Personal Analysis Flexfield'
179 THEN
180 -- Chaged the p_dyn_sql_stmt and p_param_value_set for bug 4391899
181 p_dyn_sql_stmt := 'SELECT PAC.'||p_appl_column_name||' FROM PER_PEOPLE_F PP,PER_PERSON_ANALYSES PPA,PER_ANALYSIS_CRITERIA PAC
182 WHERE PP.PERSON_ID = :VAR1 AND PP.PERSON_ID = PPA.PERSON_ID AND PPA.ANALYSIS_CRITERIA_ID=PAC.ANALYSIS_CRITERIA_ID AND
183 :EFFDATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND
184 PAC.ID_FLEX_NUM = (select id_flex_num from fnd_id_flex_structures where ID_FLEX_CODE =''PEA'' AND id_flex_structure_code = '||''''||p_dff_context_code ||''''||' )' ;
185 p_bind_var:= 'l_person_id' ;
186 p_validation_type := 'T';
187 p_param_value_set := 'SELECT DISTINCT PAC.'||p_appl_column_name||' FROM PER_ANALYSIS_CRITERIA PAC WHERE
188 PAC.ID_FLEX_NUM = (SELECT ID_FLEX_NUM FROM FND_ID_FLEX_STRUCTURES WHERE ID_FLEX_CODE =''PEA'' AND ID_FLEX_STRUCTURE_CODE = '||''''||p_dff_context_code ||''''||' )' ;
189 ------------------------------------------------
190 --- Parameter Class -> Job DFF
191 ------------------------------------------------
192 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
193 --for the variable -> p_param_value_set
194
195 ELSIF p_parameter_class = 'Job DFF'
196 THEN
197 -- Chaged the p_dyn_sql_stmt and p_param_value_set for bug 4303976
198 p_dyn_sql_stmt := 'SELECT PJ.'||p_appl_column_name||' FROM PER_ASSIGNMENTS_F PA,PER_JOB_DEFINITIONS PJD,PER_JOBS PJ
199 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
200 AND PA.JOB_ID = PJ.JOB_ID AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID AND (( '||''''||
201 p_dff_context_code ||''''||' = '||''''||'Global Data Elements'||''''||') or ( PJ.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||')) ' ;
202
203 p_bind_var:= 'l_assignment_id' ;
204 p_validation_type := 'T';
205 p_param_value_set := 'SELECT DISTINCT PJ.'||p_appl_column_name||' FROM PER_JOB_DEFINITIONS PJD, PER_JOBS PJ WHERE PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID
206 AND PJ.BUSINESS_GROUP_ID=TO_NUMBER(:PARAMETER.P_PER_BUSINESS_GROUP_ID) AND (( '||''''||p_dff_context_code ||''''||
207 ' = '||''''||'Global Data Elements'||''''||') or ( PJ.ATTRIBUTE_CATEGORY = ' ||''''||p_dff_context_code ||''''|| '))
208 AND PJ.'||p_appl_column_name || ' IS NOT NULL ' ;
209 ------------------------------------------------
210 --- Parameter Class -> Position DFF
211 ------------------------------------------------
212 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
213 --for the variable -> p_param_value_set
214
215 ELSIF p_parameter_class = 'Position DFF'
216 THEN
217 -- Chaged the p_dyn_sql_stmt and p_param_value_set for bug 4303976
218 p_dyn_sql_stmt := 'SELECT HAP.'||p_appl_column_name||' FROM per_all_assignments_f PA, PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
219 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE) AND PA.POSITION_ID = HAP.POSITION_ID
220 AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE) AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID AND
221 (( '||''''||p_dff_context_code ||''''||'= '||''''||'Global Data Elements'||''''||') or ( HAP.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||'))';
222
223 p_bind_var:= 'l_assignment_id' ;
224 p_validation_type := 'T';
225 p_param_value_set := 'SELECT DISTINCT HAP.'||p_appl_column_name||' FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
226 WHERE HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID and HAP.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)
227 AND (( '||''''||p_dff_context_code ||''''||' = '||''''||'Global Data Elements'||''''||') or ( HAP.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||'))
228 AND HAP.'||p_appl_column_name|| ' IS NOT NULL ' ;
229
230 ------------------------------------------------
231 --- Parameter Class -> Position Name DFF
232 ------------------------------------------------
233
234 ELSIF p_parameter_class = 'Position Name DFF'
235 THEN
236 p_dyn_sql_stmt := 'SELECT FFV.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA,PER_POSITION_DEFINITIONS PPD,
237 HR_ALL_POSITIONS_F HAP, FND_FLEX_VALUES FFV WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
238 AND PA.POSITION_ID = HAP.POSITION_ID AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE)
239 AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID AND FFV.FLEX_VALUE_SET_ID ='||to_char(p_flex_val_set_id)||' and FFV.FLEX_VALUE = PPD.'||p_dff_col_name;
240
241
242 p_bind_var:= 'l_assignment_id' ;
243 p_validation_type := 'T';
244 p_param_value_set := 'SELECT DISTINCT '||p_appl_column_name||' FROM FND_FLEX_VALUES WHERE flex_value_set_id = '||to_char(p_flex_val_set_id) ;
245
246 -- introduced the following code for bug 4588068
247 ------------------------------------------------
248 --- Parameter Class -> GL Accounting Flexfield
249 ------------------------------------------------
250
251 ELSIF p_parameter_class = 'GL Accounting Flexfield'
252 THEN
253 p_dyn_sql_stmt := 'SELECT DISTINCT GLC.'|| p_appl_column_name || ' FROM GL_CODE_COMBINATIONS GLC ,GL_SETS_OF_BOOKS GLB'
254 ||' WHERE GLC.CHART_OF_ACCOUNTS_ID = GLB.CHART_OF_ACCOUNTS_ID '
255 ||' AND GLB.SET_OF_BOOKS_ID = ' ||sob_Id
256 || ' AND GLC.CODE_COMBINATION_ID = :VAR1 '
257 || ' AND GLC.ENABLED_FLAG = ''Y''' ;
258 p_bind_var:= 'l_glcc_id';
259
260 p_validation_type := 'T';
261
262 p_param_value_set := 'SELECT DISTINCT GLC.'|| p_appl_column_name
263 || ' FROM GL_CODE_COMBINATIONS GLC ,GL_SETS_OF_BOOKS GLB'
264 ||' WHERE GLC.CHART_OF_ACCOUNTS_ID = GLB.CHART_OF_ACCOUNTS_ID '
265 ||' AND GLB.SET_OF_BOOKS_ID = ' || sob_Id ;
266
267 -- End of code changes for bug 4588068
268 END IF;
269
270 END create_dyn_data; -- End of procedure
271
272
273 --------------------------------------------------------------------
274 -------- PROCEDURE TYPE_TABLE_FLEX_CODE ------------
275 --------------------------------------------------------------------
276 -- This procedure finds the Validation Type Code, table name and
277 -- the flex_code that are needed to create the dynamic sql statement.
278
279 PROCEDURE type_table_flex_code( p_parameter_class IN VARCHAR2,
280 p_type OUT NOCOPY VARCHAR2,
281 p_table_name OUT NOCOPY VARCHAR2,
282 p_flex_code OUT NOCOPY VARCHAR2) IS
283
284 BEGIN
285 p_type := NULL;
289
286 p_table_name := NULL;
287 p_flex_code := NULL;
288
290 IF p_parameter_class = 'Assignment' THEN
291 p_type := 'T' ;
292 p_table_name := 'PER_ALL_ASSIGNMENTS_F';
293 ELSIF p_parameter_class = 'Person' THEN
294 p_type := 'T' ;
295 p_table_name := 'PER_ALL_PEOPLE_F';
296 ELSIF p_parameter_class = 'Elements' THEN
297 p_type := 'T' ;
298 p_table_name := 'PAY_ELEMENT_TYPES_F';
299 ELSIF p_parameter_class = 'Projects' THEN
300 p_type := 'T' ;
301 p_table_name:='PA_PROJECTS_ALL';
302 ELSIF p_parameter_class = 'Tasks' THEN
303 p_type := 'T' ;
304 p_table_name:='PA_TASKS';
305 ELSIF p_parameter_class = 'Awards' THEN
306 p_type := 'T' ;
307 p_table_name:='GMS_AWARDS_ALL';
308 ELSIF p_parameter_class = 'Cost Allocation Flexfield' THEN
309 p_flex_code := 'COST' ;
310 p_type := 'K' ;
311 ELSIF p_parameter_class = 'Grade Flexfield' THEN
312 p_flex_code := 'GRD' ;
313 p_type := 'K' ;
314 ELSIF p_parameter_class = 'Job Flexfield' THEN
315 p_flex_code := 'JOB' ;
316 p_type := 'K' ;
317 ELSIF p_parameter_class = 'Personal Analysis Flexfield' THEN
318 p_flex_code := 'PEA' ; -- added for bug 4391899
319 p_type := 'K' ;
320 ELSIF p_parameter_class = 'Position Flexfield' THEN
321 p_flex_code := 'POS' ;
322 p_type := 'K' ;
323 ELSIF p_parameter_class = 'People Group Flexfield' THEN
324 p_flex_code := 'GRP' ;
325 p_type := 'K' ;
326 ELSIF p_parameter_class = 'Job DFF' THEN
327 p_table_name := 'PER_JOBS' ;
328 p_type := 'D' ;
329 ELSIF p_parameter_class = 'Position DFF' THEN
330 p_type := 'D' ;
331 p_table_name := 'PER_POSITIONS' ;
332 ELSIF p_parameter_class = 'Position Name DFF' THEN
333 p_type := 'V' ;
334 p_flex_code := 'POS' ;
335 -- Added the following code for bug 4588068
336 ELSIF p_parameter_class = 'GL Accounting Flexfield' THEN
337 p_flex_code := 'GLX' ;
338 p_type := 'K' ;
339
340 END IF;
341
342
343 END type_table_flex_code;
344
345 --------------------------------------------------------------------
346 -------- PROCEDURE GET_ FLEXFIELD_PARAMETERS ------------
347 --------------------------------------------------------------------
348 -- This procedure is used to find the appl_column_name, dff_col_name
349 -- validation_type and flex_val_set_id parameters
350
351 PROCEDURE get_flexfield_parameters( p_type IN VARCHAR2,
352 p_table_name IN VARCHAR2,
353 p_flex_code IN VARCHAR,
354 p_parameter IN VARCHAR2,
355 p_datatype IN VARCHAR2,
356 p_business_group_id IN NUMBER,
357 p_appl_column_name OUT NOCOPY VARCHAR2,
358 p_dff_col_name OUT NOCOPY VARCHAR2,
359 p_flex_val_set_id OUT NOCOPY NUMBER) IS
360
361 v_format_Type VARCHAR2(1);
362
363 BEGIN
364
365 p_appl_column_name := NULL;
366 p_dff_col_name := NULL;
367 p_flex_val_set_id := NULL;
368
369
370 IF p_datatype = 'VARCHAR2' THEN
371 v_format_type := 'C';
372 ELSIF p_datatype = 'NUMBER' THEN
373 v_format_type := 'N';
374 ELSE v_format_type := NULL;
375 END IF;
376
377 IF p_type = 'K' THEN
378
379 IF p_flex_code= 'GRD' THEN
380
381
382 SELECT fifs1.application_column_name
383 INTO p_appl_column_name
384 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
385 WHERE fifs.id_flex_code = 'GRD' and fifs.id_flex_num = (SELECT org_information4 FROM hr_organization_information
386 WHERE organization_id = p_business_group_id
387 AND org_information_context = 'Business Group Information')
388 AND fifs.application_id = fifs1.application_id and fifs.id_flex_code = fifs1.id_flex_code
389 and fifs.id_flex_num = fifs1.id_flex_num and fifs1.flex_value_set_id = fifs2.flex_value_set_id
390 and fifs1.enabled_flag='Y'
391 -- AND fifs1.segment_name = p_parameter
392 AND fifs1.application_column_name = p_parameter
393 AND fifs2.format_type = v_format_type;
394
395
396 ELSIF p_flex_code= 'GRP' THEN
397
398 SELECT fifs1.application_column_name
399 INTO p_appl_column_name
400 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
401 WHERE fifs.id_flex_code = 'GRP' and fifs.id_flex_num = (SELECT org_information5 FROM hr_organization_information
402 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
403 and fifs.application_id = fifs1.application_id
404 and fifs.id_flex_code = fifs1.id_flex_code
405 and fifs.id_flex_num = fifs1.id_flex_num
406 and fifs1.flex_value_set_id = fifs2.flex_value_set_id
407 and fifs1.enabled_flag='Y'
408 -- AND fifs1.segment_name = p_parameter
409 AND fifs1.application_column_name = p_parameter
410 AND fifs2.format_type = v_format_type;
411
412
413 ELSIF p_flex_code= 'JOB' THEN
414
415 SELECT fifs1.application_column_name
416 INTO p_appl_column_name
417 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
418 WHERE fifs.id_flex_code = 'JOB' and fifs.id_flex_num = (SELECT org_information6 FROM hr_organization_information
419 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
420 and fifs.application_id = fifs1.application_id
421 and fifs.id_flex_code = fifs1.id_flex_code
422 and fifs.id_flex_num = fifs1.id_flex_num
423 and fifs1.flex_value_set_id = fifs2.flex_value_set_id
424 and fifs1.enabled_flag='Y'
425 -- AND fifs1.segment_name = p_parameter
426 AND fifs1.application_column_name = p_parameter
427 AND fifs2.format_type = v_format_type;
428
429
430 ELSIF p_flex_code= 'COST' then
431
432 SELECT fifs1.application_column_name
433 INTO p_appl_column_name
434 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
435 WHERE fifs.id_flex_code = 'COST' and fifs.id_flex_num = (SELECT org_information7 FROM hr_organization_information WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
436 AND fifs.application_id = fifs1.application_id
437 AND fifs.id_flex_code = fifs1.id_flex_code
438 AND fifs.id_flex_num = fifs1.id_flex_num
439 AND fifs1.flex_value_set_id = fifs2.flex_value_set_id
440 AND fifs1.enabled_flag='Y'
441 -- AND fifs1.segment_name = p_parameter
442 AND fifs1.application_column_name = p_parameter
443 AND fifs2.format_type = v_format_type;
444
445
446
447 ELSIF p_flex_code= 'POS' then
448
449 SELECT fifs1.application_column_name
450 INTO p_appl_column_name
451 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
452 WHERE fifs.id_flex_code = 'POS' and fifs.id_flex_num = (SELECT org_information8 FROM hr_organization_information
453 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
454 AND fifs.application_id = fifs1.application_id
455 AND fifs.id_flex_code = fifs1.id_flex_code
456 AND fifs.id_flex_num = fifs1.id_flex_num
457 AND fifs1.flex_value_set_id = fifs2.flex_value_set_id
458 AND fifs1.enabled_flag='Y'
459 -- AND fifs1.segment_name = p_parameter
460 AND fifs1.application_column_name = p_parameter
461 AND fifs2.format_type = v_format_type;
462
463 END IF;
464
465 ELSIF p_type = 'D' then
466
467 SELECT application_column_name
468 INTO p_appl_column_name
469 FROM fnd_descr_flex_column_usages WHERE descriptive_flexfield_name= p_table_name
470 AND enabled_flag = 'Y'
471 -- AND end_user_column_name = p_parameter ;
472 And application_column_name = p_parameter;
473
474 ELSIF p_type = 'V' THEN
475 IF p_flex_code= 'POS' THEN
476
477 SELECT fdfcu.application_column_name, fifs1.application_column_name, fifs1.flex_value_set_id
478 INTO p_appl_column_name,p_dff_col_name, p_flex_val_set_id
479 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets ffvs,
480 fnd_descr_flex_column_usages fdfcu
481 WHERE fifs.id_flex_code = p_flex_code
482 and fifs.id_flex_num = (SELECT org_information8 FROM hr_organization_information
483 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
484 and fifs.application_id = fifs1.application_id and fifs.id_flex_code = fifs1.id_flex_code
485 and fifs.id_flex_num = fifs1.id_flex_num
486 and fifs1.enabled_flag = 'Y'
487 and fifs1.flex_value_set_id = ffvs.flex_value_set_id
488 and ffvs.flex_value_set_name = fdfcu.descriptive_flex_context_code
489 and fdfcu.descriptive_flexfield_name = 'FND_FLEX_VALUES'
490 -- AND fdfcu.end_user_column_name = p_parameter
491 AND fdfcu.application_column_name = p_parameter
492 AND ffvs.format_type = v_format_type;
493
494
495 END IF;
496 END IF;
497 -- added exception for bug 4391899
498 EXCEPTION
499
500 WHEN no_data_found THEN
501 null;
502
503
504
505 END get_flexfield_parameters;
506
507
508 --------------------------------------------------------------------
509 END psp_auto_dyn; -- End of package