[Home] [Help]
PACKAGE BODY: APPS.PSP_AUTO_DYN
Source
1 PACKAGE BODY psp_auto_dyn AS
2 --$Header: PSPAUDYB.pls 120.5.12020000.4 2013/04/09 04:55:34 lkodaman 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 --Commented the below block for bug 16591849
74 /*
75 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS WHERE TASK_ID = :VAR1' ;
76 p_bind_var:= 'l_task_id' ;
77 p_validation_type := 'T';
78 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS ORDER BY 1';
79 */
80
81 --Replaced the above commented block with the below block for 16591849
82
83 IF (p_parameter = 'TASK_NUMBER' OR p_parameter = 'TASK_NAME')
84 THEN
85 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS_EXPEND_V WHERE TASK_ID = :VAR1' ;
86 p_bind_var:= 'l_task_id' ;
87 p_validation_type := 'T';
88 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS_EXPEND_V ORDER BY 1';
89 ELSE
90 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS WHERE TASK_ID = :VAR1' ;
91 p_bind_var:= 'l_task_id' ;
92 p_validation_type := 'T';
93 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS ORDER BY 1';
94 END IF;
95 ------------------------------------------------
96 --- Parameter Class -> Awards
97 ------------------------------------------------
98
99 ELSIF p_parameter_class = 'Awards'
100 THEN
101 p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM GMS_AWARDS_ALL WHERE AWARD_ID = :VAR1' ;
102 p_bind_var:= 'l_award_id' ;
103 p_validation_type := 'T';
104 p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM GMS_AWARDS ORDER BY 1';
105
106 ------------------------------------------------
107 --- Parameter Class -> Expenditure Type
108 ------------------------------------------------
109 ELSIF p_parameter_class = 'Expenditure Type'
110 THEN
111 p_dyn_sql_stmt := 'NULL';
112 p_bind_var:= null;
113 p_validation_type := 'T';
114 p_param_value_set := 'SELECT EXPENDITURE_TYPE FROM PA_EXPENDITURE_TYPES_EXPEND_V WHERE SYSTEM_LINKAGE_FUNCTION IN (''ST'') ORDER BY EXPENDITURE_TYPE';
115 ------------------------------------------------
116 --- Parameter Class -> Position Flexfield
117 ------------------------------------------------
118
119 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
120 --for the variable -> p_param_value_set
121
122 ELSIF p_parameter_class = 'Position Flexfield'
123 THEN
124
125 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
126 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE AND PA.POSITION_ID = HAP.POSITION_ID
127 AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE) AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID' ;
128 p_bind_var:= 'l_assignment_id' ;
129 p_validation_type := 'T';
130 p_param_value_set := 'SELECT DISTINCT PPD.'||p_appl_column_name||' FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
131 WHERE HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID
132 AND HAP.BUSINESS_GROUP_ID= to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)';
133
134 ------------------------------------------------
135 --- Parameter Class -> Job Flexfield
136 ------------------------------------------------
137
138 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
139 --for the variable -> p_param_value_set
140
141 ELSIF p_parameter_class = 'Job Flexfield'
142 THEN
143 p_dyn_sql_stmt := 'SELECT PJD.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA, PER_JOB_DEFINITIONS PJD, PER_JOBS PJ
144 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
145 AND PA.JOB_ID = PJ.JOB_ID AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID' ;
146 p_bind_var:= 'l_assignment_id' ;
147 p_validation_type := 'T';
148 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)' ;
149
150 ------------------------------------------------
151 --- Parameter Class -> Grade Flexfield
152 ------------------------------------------------
153
154 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
155 --for the variable -> p_param_value_set
156
157 ELSIF p_parameter_class = 'Grade Flexfield'
158 THEN
159 p_dyn_sql_stmt := 'SELECT PGD.'||p_appl_column_name||' FROM per_all_assignments_f PA, PER_GRADE_DEFINITIONS PGD, PER_GRADES PG
160 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' ;
161 p_bind_var:= 'l_assignment_id' ;
162 p_validation_type := 'T';
163 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
164 AND PG.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;
165
166 ------------------------------------------------
167 --- Parameter Class -> People Group Flexfield
168 ------------------------------------------------
169
170 ELSIF p_parameter_class = 'People Group Flexfield'
171 THEN
172 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
173 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE) AND PA.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID' ;
174 p_bind_var:= 'l_assignment_id' ;
175 p_validation_type := 'T';
176 p_param_value_set := 'SELECT DISTINCT PPG.'||p_appl_column_name||' FROM PAY_PEOPLE_GROUPS PPG' ;
177
178 ------------------------------------------------
179 --- Parameter Class -> Cost Allocation Flexfield
180 ------------------------------------------------
181 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
182 --for the variable -> p_param_value_set
183
184 ELSIF p_parameter_class = 'Cost Allocation Flexfield'
185 THEN
186 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
187 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' ;
188 p_bind_var:= 'l_assignment_id' ;
189 p_validation_type := 'T';
190 p_param_value_set := 'SELECT DISTINCT PCAK.'||p_appl_column_name||' FROM PAY_COST_ALLOCATION_KEYFLEX PCAK, PAY_COST_ALLOCATIONS_F PCAF
191 where PCAK.COST_ALLOCATION_KEYFLEX_ID=PCAF.COST_ALLOCATION_KEYFLEX_ID AND PCAF.BUSINESS_GROUP_ID=TO_NUMBER(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;
192 ------------------------------------------------
193 --- Parameter Class -> Personal Analysis Flexfield
194 ------------------------------------------------
195 ELSIF p_parameter_class = 'Personal Analysis Flexfield'
196 THEN
197 -- Chaged the p_dyn_sql_stmt and p_param_value_set for bug 4391899
198 p_dyn_sql_stmt := 'SELECT PAC.'||p_appl_column_name||' FROM PER_PEOPLE_F PP,PER_PERSON_ANALYSES PPA,PER_ANALYSIS_CRITERIA PAC
199 WHERE PP.PERSON_ID = :VAR1 AND PP.PERSON_ID = PPA.PERSON_ID AND PPA.ANALYSIS_CRITERIA_ID=PAC.ANALYSIS_CRITERIA_ID AND
200 :EFFDATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND
201 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 ||''''||' )' ;
202 p_bind_var:= 'l_person_id' ;
203 p_validation_type := 'T';
204 p_param_value_set := 'SELECT DISTINCT PAC.'||p_appl_column_name||' FROM PER_ANALYSIS_CRITERIA PAC WHERE
205 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 ||''''||' )' ;
206 ------------------------------------------------
207 --- Parameter Class -> Job DFF
208 ------------------------------------------------
209 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
210 --for the variable -> p_param_value_set
211
212 ELSIF p_parameter_class = 'Job DFF'
213 THEN
214 -- Chaged the p_dyn_sql_stmt and p_param_value_set for bug 4303976
215 p_dyn_sql_stmt := 'SELECT PJ.'||p_appl_column_name||' FROM PER_ASSIGNMENTS_F PA,PER_JOB_DEFINITIONS PJD,PER_JOBS PJ
216 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
217 AND PA.JOB_ID = PJ.JOB_ID AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID AND (( '||''''||
218 p_dff_context_code ||''''||' = '||''''||'Global Data Elements'||''''||') or ( PJ.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||')) ' ;
219
220 p_bind_var:= 'l_assignment_id' ;
221 p_validation_type := 'T';
222 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
223 AND PJ.BUSINESS_GROUP_ID=TO_NUMBER(:PARAMETER.P_PER_BUSINESS_GROUP_ID) AND (( '||''''||p_dff_context_code ||''''||
224 ' = '||''''||'Global Data Elements'||''''||') or ( PJ.ATTRIBUTE_CATEGORY = ' ||''''||p_dff_context_code ||''''|| '))
225 AND PJ.'||p_appl_column_name || ' IS NOT NULL ' ;
226 ------------------------------------------------
227 --- Parameter Class -> Position DFF
228 ------------------------------------------------
229 --For Bug 2640340 : Replaced the Global Variable with Parameter Variablei.e :GLOBAL.G_PER_BUSINESS_GROUP_ID with :PARAMETER.P_PER_BUSINESS_GROUP_ID
230 --for the variable -> p_param_value_set
231
232 ELSIF p_parameter_class = 'Position DFF'
233 THEN
234 -- Chaged the p_dyn_sql_stmt and p_param_value_set for bug 4303976
235 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
236 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
237 AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE) AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID AND
238 (( '||''''||p_dff_context_code ||''''||'= '||''''||'Global Data Elements'||''''||') or ( HAP.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||'))';
239
240 p_bind_var:= 'l_assignment_id' ;
241 p_validation_type := 'T';
242 p_param_value_set := 'SELECT DISTINCT HAP.'||p_appl_column_name||' FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
243 WHERE HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID and HAP.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)
244 AND (( '||''''||p_dff_context_code ||''''||' = '||''''||'Global Data Elements'||''''||') or ( HAP.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||'))
245 AND HAP.'||p_appl_column_name|| ' IS NOT NULL ' ;
246
247 ------------------------------------------------
248 --- Parameter Class -> Position Name DFF
249 ------------------------------------------------
250
251 ELSIF p_parameter_class = 'Position Name DFF'
252 THEN
253 p_dyn_sql_stmt := 'SELECT FFV.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA,PER_POSITION_DEFINITIONS PPD,
254 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)
255 AND PA.POSITION_ID = HAP.POSITION_ID AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE)
256 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;
257
258
259 p_bind_var:= 'l_assignment_id' ;
260 p_validation_type := 'T';
261 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) ;
262
263 -- introduced the following code for bug 4588068
264 ------------------------------------------------
265 --- Parameter Class -> GL Accounting Flexfield
266 ------------------------------------------------
267
268 ELSIF p_parameter_class = 'GL Accounting Flexfield'
269 THEN
270 p_dyn_sql_stmt := 'SELECT DISTINCT GLC.'|| p_appl_column_name || ' FROM GL_CODE_COMBINATIONS GLC ,GL_SETS_OF_BOOKS GLB'
271 ||' WHERE GLC.CHART_OF_ACCOUNTS_ID = GLB.CHART_OF_ACCOUNTS_ID '
272 ||' AND GLB.SET_OF_BOOKS_ID = ' ||sob_Id
273 || ' AND GLC.CODE_COMBINATION_ID = :VAR1 '
274 || ' AND GLC.ENABLED_FLAG = ''Y''' ;
275 p_bind_var:= 'l_glcc_id';
276
277 p_validation_type := 'T';
278
279 p_param_value_set := 'SELECT DISTINCT GLC.'|| p_appl_column_name
280 || ' FROM GL_CODE_COMBINATIONS GLC ,GL_SETS_OF_BOOKS GLB'
281 ||' WHERE GLC.CHART_OF_ACCOUNTS_ID = GLB.CHART_OF_ACCOUNTS_ID '
282 ||' AND GLB.SET_OF_BOOKS_ID = ' || sob_Id ;
283
284 -- End of code changes for bug 4588068
285 END IF;
286
287 END create_dyn_data; -- End of procedure
288
289
290 --------------------------------------------------------------------
291 -------- PROCEDURE TYPE_TABLE_FLEX_CODE ------------
292 --------------------------------------------------------------------
293 -- This procedure finds the Validation Type Code, table name and
294 -- the flex_code that are needed to create the dynamic sql statement.
295
296 PROCEDURE type_table_flex_code( p_parameter_class IN VARCHAR2,
297 p_type OUT NOCOPY VARCHAR2,
298 p_table_name OUT NOCOPY VARCHAR2,
299 p_flex_code OUT NOCOPY VARCHAR2) IS
300
301 BEGIN
302 p_type := NULL;
303 p_table_name := NULL;
304 p_flex_code := NULL;
305
306
307 IF p_parameter_class = 'Assignment' THEN
308 p_type := 'T' ;
309 p_table_name := 'PER_ALL_ASSIGNMENTS_F';
310 ELSIF p_parameter_class = 'Person' THEN
311 p_type := 'T' ;
312 p_table_name := 'PER_ALL_PEOPLE_F';
313 ELSIF p_parameter_class = 'Elements' THEN
314 p_type := 'T' ;
315 p_table_name := 'PAY_ELEMENT_TYPES_F';
316 ELSIF p_parameter_class = 'Projects' THEN
317 p_type := 'T' ;
318 p_table_name:='PA_PROJECTS_ALL';
319 ELSIF p_parameter_class = 'Tasks' THEN
320 p_type := 'T' ;
321 p_table_name:='PA_TASKS';
322 ELSIF p_parameter_class = 'Awards' THEN
323 p_type := 'T' ;
324 p_table_name:='GMS_AWARDS_ALL';
325 ELSIF p_parameter_class = 'Cost Allocation Flexfield' THEN
326 p_flex_code := 'COST' ;
327 p_type := 'K' ;
328 ELSIF p_parameter_class = 'Grade Flexfield' THEN
329 p_flex_code := 'GRD' ;
330 p_type := 'K' ;
331 ELSIF p_parameter_class = 'Job Flexfield' THEN
332 p_flex_code := 'JOB' ;
333 p_type := 'K' ;
334 ELSIF p_parameter_class = 'Personal Analysis Flexfield' THEN
335 p_flex_code := 'PEA' ; -- added for bug 4391899
336 p_type := 'K' ;
337 ELSIF p_parameter_class = 'Position Flexfield' THEN
338 p_flex_code := 'POS' ;
339 p_type := 'K' ;
340 ELSIF p_parameter_class = 'People Group Flexfield' THEN
341 p_flex_code := 'GRP' ;
342 p_type := 'K' ;
343 ELSIF p_parameter_class = 'Job DFF' THEN
344 p_table_name := 'PER_JOBS' ;
345 p_type := 'D' ;
346 ELSIF p_parameter_class = 'Position DFF' THEN
347 p_type := 'D' ;
348 p_table_name := 'PER_POSITIONS' ;
349 ELSIF p_parameter_class = 'Position Name DFF' THEN
350 p_type := 'V' ;
351 p_flex_code := 'POS' ;
352 -- Added the following code for bug 4588068
353 ELSIF p_parameter_class = 'GL Accounting Flexfield' THEN
354 p_flex_code := 'GLX' ;
355 p_type := 'K' ;
356
357 END IF;
358
359
360 END type_table_flex_code;
361
362 --------------------------------------------------------------------
363 -------- PROCEDURE GET_ FLEXFIELD_PARAMETERS ------------
364 --------------------------------------------------------------------
365 -- This procedure is used to find the appl_column_name, dff_col_name
366 -- validation_type and flex_val_set_id parameters
367
368 PROCEDURE get_flexfield_parameters( p_type IN VARCHAR2,
369 p_table_name IN VARCHAR2,
370 p_flex_code IN VARCHAR,
371 p_parameter IN VARCHAR2,
372 p_datatype IN VARCHAR2,
373 p_business_group_id IN NUMBER,
374 p_appl_column_name OUT NOCOPY VARCHAR2,
375 p_dff_col_name OUT NOCOPY VARCHAR2,
376 p_flex_val_set_id OUT NOCOPY NUMBER) IS
377
378 v_format_Type VARCHAR2(1);
379
380 BEGIN
381
382 p_appl_column_name := NULL;
383 p_dff_col_name := NULL;
384 p_flex_val_set_id := NULL;
385
386
387 IF p_datatype = 'VARCHAR2' THEN
388 v_format_type := 'C';
389 ELSIF p_datatype = 'NUMBER' THEN
390 v_format_type := 'N';
391 ELSE v_format_type := NULL;
392 END IF;
393
394 IF p_type = 'K' THEN
395
396 IF p_flex_code= 'GRD' THEN
397
398
399 SELECT fifs1.application_column_name
400 INTO p_appl_column_name
401 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
402 WHERE fifs.id_flex_code = 'GRD' and fifs.id_flex_num = (SELECT org_information4 FROM hr_organization_information
403 WHERE organization_id = p_business_group_id
404 AND org_information_context = 'Business Group Information')
405 AND fifs.application_id = fifs1.application_id and fifs.id_flex_code = fifs1.id_flex_code
406 and fifs.id_flex_num = fifs1.id_flex_num 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= 'GRP' 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 = 'GRP' and fifs.id_flex_num = (SELECT org_information5 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= 'JOB' 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 = 'JOB' and fifs.id_flex_num = (SELECT org_information6 FROM hr_organization_information
436 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
437 and fifs.application_id = fifs1.application_id
438 and fifs.id_flex_code = fifs1.id_flex_code
439 and fifs.id_flex_num = fifs1.id_flex_num
440 and fifs1.flex_value_set_id = fifs2.flex_value_set_id
441 and fifs1.enabled_flag='Y'
442 -- AND fifs1.segment_name = p_parameter
443 AND fifs1.application_column_name = p_parameter
444 AND fifs2.format_type = v_format_type;
445
446
447 ELSIF p_flex_code= 'COST' 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 = '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')
453 AND fifs.application_id = fifs1.application_id
454 AND fifs.id_flex_code = fifs1.id_flex_code
455 AND fifs.id_flex_num = fifs1.id_flex_num
456 AND fifs1.flex_value_set_id = fifs2.flex_value_set_id
457 AND fifs1.enabled_flag='Y'
458 -- AND fifs1.segment_name = p_parameter
459 AND fifs1.application_column_name = p_parameter
460 AND fifs2.format_type = v_format_type;
461
462
463
464 ELSIF p_flex_code= 'POS' then
465
466 SELECT fifs1.application_column_name
467 INTO p_appl_column_name
468 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
469 WHERE fifs.id_flex_code = 'POS' and fifs.id_flex_num = (SELECT org_information8 FROM hr_organization_information
470 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
471 AND fifs.application_id = fifs1.application_id
472 AND fifs.id_flex_code = fifs1.id_flex_code
473 AND fifs.id_flex_num = fifs1.id_flex_num
474 AND fifs1.flex_value_set_id = fifs2.flex_value_set_id
475 AND fifs1.enabled_flag='Y'
476 -- AND fifs1.segment_name = p_parameter
477 AND fifs1.application_column_name = p_parameter
478 AND fifs2.format_type = v_format_type;
479
480 END IF;
481
482 ELSIF p_type = 'D' then
483
484 SELECT application_column_name
485 INTO p_appl_column_name
486 FROM fnd_descr_flex_column_usages WHERE descriptive_flexfield_name= p_table_name
487 AND enabled_flag = 'Y'
488 -- AND end_user_column_name = p_parameter ;
489 And application_column_name = p_parameter;
490
491 ELSIF p_type = 'V' THEN
492 IF p_flex_code= 'POS' THEN
493
494 SELECT fdfcu.application_column_name, fifs1.application_column_name, fifs1.flex_value_set_id
495 INTO p_appl_column_name,p_dff_col_name, p_flex_val_set_id
496 FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets ffvs,
497 fnd_descr_flex_column_usages fdfcu
498 WHERE fifs.id_flex_code = p_flex_code
499 and fifs.id_flex_num = (SELECT org_information8 FROM hr_organization_information
500 WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
501 and fifs.application_id = fifs1.application_id and fifs.id_flex_code = fifs1.id_flex_code
502 and fifs.id_flex_num = fifs1.id_flex_num
503 and fifs1.enabled_flag = 'Y'
504 and fifs1.flex_value_set_id = ffvs.flex_value_set_id
505 and ffvs.flex_value_set_name = fdfcu.descriptive_flex_context_code
506 and fdfcu.descriptive_flexfield_name = 'FND_FLEX_VALUES'
507 -- AND fdfcu.end_user_column_name = p_parameter
508 AND fdfcu.application_column_name = p_parameter
509 AND ffvs.format_type = v_format_type;
510
511
512 END IF;
513 END IF;
514 -- added exception for bug 4391899
515 EXCEPTION
516
517 WHEN no_data_found THEN
518 null;
519
520
521
522 END get_flexfield_parameters;
523
524
525 --------------------------------------------------------------------
526 END psp_auto_dyn; -- End of package