APPS.PSP_AUTO_DYN SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 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';

Line: 33

		p_param_value_set:='SELECT DISTINCT '||p_parameter||' FROM PER_ASSIGNMENTS_F ORDER BY 1';

Line: 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';

Line: 44

	 	p_param_value_set:='SELECT DISTINCT '|| p_parameter||' FROM PER_PEOPLE_F ORDER BY 1';

Line: 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)';

Line: 55

        	p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PAY_ELEMENT_TYPES_F ORDER BY 1' ;

Line: 62

        	p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_PROJECTS_ALL WHERE PROJECT_ID = :VAR1' ;

Line: 65

        	p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_PROJECTS  ORDER BY 1';

Line: 75

		p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS WHERE TASK_ID = :VAR1' ;

Line: 78

        	p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS ORDER BY 1';

Line: 85

					p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS_EXPEND_V WHERE TASK_ID = :VAR1' ;

Line: 88

					p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS_EXPEND_V ORDER BY 1';

Line: 90

					p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM PA_TASKS WHERE TASK_ID = :VAR1' ;

Line: 93

					p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM PA_TASKS ORDER BY 1';

Line: 101

        	p_dyn_sql_stmt := 'SELECT '||p_parameter||' FROM GMS_AWARDS_ALL  WHERE AWARD_ID = :VAR1' ;

Line: 104

        	p_param_value_set := 'SELECT DISTINCT '||p_parameter||' FROM GMS_AWARDS ORDER BY 1';

Line: 114

        	p_param_value_set := 'SELECT EXPENDITURE_TYPE FROM PA_EXPENDITURE_TYPES_EXPEND_V WHERE SYSTEM_LINKAGE_FUNCTION IN (''ST'') ORDER BY EXPENDITURE_TYPE';

Line: 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
 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE AND PA.POSITION_ID = HAP.POSITION_ID
 AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE) AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID' ;

Line: 130

	        p_param_value_set :=  'SELECT DISTINCT PPD.'||p_appl_column_name||' FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
 WHERE HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID
 AND HAP.BUSINESS_GROUP_ID= to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)';

Line: 143

        	p_dyn_sql_stmt := 'SELECT PJD.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA, PER_JOB_DEFINITIONS PJD, PER_JOBS PJ
 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
 AND PA.JOB_ID = PJ.JOB_ID AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID' ;

Line: 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)' ;

Line: 159

        	p_dyn_sql_stmt := 'SELECT PGD.'||p_appl_column_name||' FROM per_all_assignments_f PA, PER_GRADE_DEFINITIONS PGD, PER_GRADES PG
 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' ;

Line: 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
 AND PG.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;

Line: 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
 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE) AND PA.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID' ;

Line: 176

        p_param_value_set :=  'SELECT DISTINCT PPG.'||p_appl_column_name||' FROM PAY_PEOPLE_GROUPS PPG' ;

Line: 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
 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' ;

Line: 190

        	p_param_value_set :=  'SELECT DISTINCT PCAK.'||p_appl_column_name||' FROM PAY_COST_ALLOCATION_KEYFLEX PCAK, PAY_COST_ALLOCATIONS_F PCAF
 where PCAK.COST_ALLOCATION_KEYFLEX_ID=PCAF.COST_ALLOCATION_KEYFLEX_ID AND PCAF.BUSINESS_GROUP_ID=TO_NUMBER(:PARAMETER.P_PER_BUSINESS_GROUP_ID)' ;

Line: 198

        	p_dyn_sql_stmt := 'SELECT PAC.'||p_appl_column_name||' FROM PER_PEOPLE_F PP,PER_PERSON_ANALYSES PPA,PER_ANALYSIS_CRITERIA PAC
 WHERE PP.PERSON_ID = :VAR1 AND PP.PERSON_ID = PPA.PERSON_ID AND PPA.ANALYSIS_CRITERIA_ID=PAC.ANALYSIS_CRITERIA_ID AND
 :EFFDATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND
 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 ||''''||' )' ;

Line: 204

        	p_param_value_set :=  'SELECT DISTINCT PAC.'||p_appl_column_name||' FROM PER_ANALYSIS_CRITERIA PAC WHERE
 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 ||''''||' )' ;

Line: 215

        	p_dyn_sql_stmt := 'SELECT PJ.'||p_appl_column_name||' FROM PER_ASSIGNMENTS_F PA,PER_JOB_DEFINITIONS PJD,PER_JOBS PJ
 WHERE PA.ASSIGNMENT_ID = :VAR1 AND :EFFDATE BETWEEN PA.EFFECTIVE_START_DATE AND NVL(PA.EFFECTIVE_END_DATE,:EFFDATE)
 AND PA.JOB_ID = PJ.JOB_ID  AND  PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID AND ((  '||''''||
 p_dff_context_code ||''''||' = '||''''||'Global Data Elements'||''''||') or ( PJ.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||')) ' ;

Line: 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
 AND PJ.BUSINESS_GROUP_ID=TO_NUMBER(:PARAMETER.P_PER_BUSINESS_GROUP_ID) AND ((  '||''''||p_dff_context_code ||''''||
' = '||''''||'Global Data Elements'||''''||') or ( PJ.ATTRIBUTE_CATEGORY = ' ||''''||p_dff_context_code ||''''|| '))
 AND PJ.'||p_appl_column_name || ' IS NOT NULL ' ;

Line: 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
 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
 AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE) AND HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID AND
 (( '||''''||p_dff_context_code ||''''||'= '||''''||'Global Data Elements'||''''||') or ( HAP.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||'))';

Line: 242

        p_param_value_set :=  'SELECT DISTINCT HAP.'||p_appl_column_name||' FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F HAP
 WHERE HAP.POSITION_DEFINITION_ID = PPD.POSITION_DEFINITION_ID and HAP.BUSINESS_GROUP_ID=to_number(:PARAMETER.P_PER_BUSINESS_GROUP_ID)
 AND (( '||''''||p_dff_context_code ||''''||' = '||''''||'Global Data Elements'||''''||') or ( HAP.ATTRIBUTE_CATEGORY = '||''''||p_dff_context_code ||''''||'))
 AND HAP.'||p_appl_column_name|| ' IS NOT NULL ' ;

Line: 253

        p_dyn_sql_stmt := 'SELECT FFV.'||p_appl_column_name||' FROM PER_ALL_ASSIGNMENTS_F PA,PER_POSITION_DEFINITIONS PPD,
 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)
 AND PA.POSITION_ID = HAP.POSITION_ID AND :EFFDATE BETWEEN HAP.EFFECTIVE_START_DATE AND NVL(HAP.EFFECTIVE_END_DATE,:EFFDATE)
 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;

Line: 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) ;

Line: 270

        p_dyn_sql_stmt :=  'SELECT DISTINCT GLC.'|| p_appl_column_name || ' FROM GL_CODE_COMBINATIONS GLC ,GL_SETS_OF_BOOKS GLB'
                   ||' WHERE GLC.CHART_OF_ACCOUNTS_ID = GLB.CHART_OF_ACCOUNTS_ID '
		   ||' AND GLB.SET_OF_BOOKS_ID = ' ||sob_Id
		   || ' AND GLC.CODE_COMBINATION_ID = :VAR1 '
		   || ' AND GLC.ENABLED_FLAG = ''Y''' ;

Line: 279

	p_param_value_set :=   'SELECT DISTINCT GLC.'|| p_appl_column_name
	           || ' FROM GL_CODE_COMBINATIONS GLC ,GL_SETS_OF_BOOKS GLB'
		   ||' WHERE GLC.CHART_OF_ACCOUNTS_ID = GLB.CHART_OF_ACCOUNTS_ID '
		   ||' AND GLB.SET_OF_BOOKS_ID = ' || sob_Id ;

Line: 399

	  SELECT fifs1.application_column_name
	  INTO	p_appl_column_name
	  FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
	  WHERE fifs.id_flex_code = 'GRD' and fifs.id_flex_num = (SELECT org_information4 FROM hr_organization_information
								WHERE organization_id = p_business_group_id
								AND org_information_context = 'Business Group Information')
	  AND fifs.application_id = fifs1.application_id and fifs.id_flex_code = fifs1.id_flex_code
	  and fifs.id_flex_num = fifs1.id_flex_num and fifs1.flex_value_set_id = fifs2.flex_value_set_id
	  and fifs1.enabled_flag='Y'
	  -- AND fifs1.segment_name = p_parameter
	  AND fifs1.application_column_name =  p_parameter
	  AND fifs2.format_type = v_format_type;

Line: 415

	  SELECT fifs1.application_column_name
	  INTO p_appl_column_name
	  FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
	  WHERE fifs.id_flex_code = 'GRP' and fifs.id_flex_num = (SELECT org_information5 FROM hr_organization_information
			WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
	  and fifs.application_id = fifs1.application_id
	  and fifs.id_flex_code = fifs1.id_flex_code
	  and fifs.id_flex_num = fifs1.id_flex_num
	  and fifs1.flex_value_set_id = fifs2.flex_value_set_id
	  and fifs1.enabled_flag='Y'
	  -- AND fifs1.segment_name = p_parameter
	  AND fifs1.application_column_name =  p_parameter
	  AND fifs2.format_type = v_format_type;

Line: 432

	  SELECT fifs1.application_column_name
 	  INTO 	 p_appl_column_name
	  FROM 	 fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
	  WHERE  fifs.id_flex_code = 'JOB' and fifs.id_flex_num = (SELECT org_information6 FROM hr_organization_information
				WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
	  and 	 fifs.application_id = fifs1.application_id
	  and 	 fifs.id_flex_code = fifs1.id_flex_code
	  and 	 fifs.id_flex_num = fifs1.id_flex_num
	  and fifs1.flex_value_set_id = fifs2.flex_value_set_id
	  and fifs1.enabled_flag='Y'
	  -- AND fifs1.segment_name = p_parameter
	  AND fifs1.application_column_name =  p_parameter
	  AND fifs2.format_type = v_format_type;

Line: 449

	  SELECT fifs1.application_column_name
	  INTO p_appl_column_name
	  FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
	  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')
	  AND fifs.application_id = fifs1.application_id
	  AND fifs.id_flex_code = fifs1.id_flex_code
	  AND fifs.id_flex_num = fifs1.id_flex_num
	  AND fifs1.flex_value_set_id = fifs2.flex_value_set_id
	  AND fifs1.enabled_flag='Y'
	 -- AND fifs1.segment_name = p_parameter
	  AND fifs1.application_column_name =  p_parameter
	  AND fifs2.format_type = v_format_type;

Line: 466

 	  SELECT fifs1.application_column_name
 	  INTO p_appl_column_name
	  FROM fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets fifs2
	  WHERE fifs.id_flex_code = 'POS' and fifs.id_flex_num = (SELECT org_information8 FROM hr_organization_information
				WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
	  AND fifs.application_id = fifs1.application_id
	  AND fifs.id_flex_code = fifs1.id_flex_code
	  AND fifs.id_flex_num = fifs1.id_flex_num
	  AND fifs1.flex_value_set_id = fifs2.flex_value_set_id
	  AND fifs1.enabled_flag='Y'
	  -- AND fifs1.segment_name = p_parameter
	  AND fifs1.application_column_name =  p_parameter
	  AND fifs2.format_type = v_format_type;

Line: 484

	SELECT 	application_column_name
	INTO 	p_appl_column_name
 	FROM 	fnd_descr_flex_column_usages WHERE descriptive_flexfield_name= p_table_name
	AND 	enabled_flag = 'Y'
--	AND 	end_user_column_name = p_parameter ;

Line: 494

	  SELECT fdfcu.application_column_name, fifs1.application_column_name, fifs1.flex_value_set_id
	  INTO 	 p_appl_column_name,p_dff_col_name, p_flex_val_set_id
	  FROM 	 fnd_id_flex_structures fifs, fnd_id_flex_segments fifs1, fnd_flex_value_sets ffvs,
                 fnd_descr_flex_column_usages fdfcu
	  WHERE fifs.id_flex_code = p_flex_code
	  and 	fifs.id_flex_num = (SELECT org_information8 FROM hr_organization_information
	  			WHERE organization_id = p_business_group_id and org_information_context = 'Business Group Information')
	  and 	fifs.application_id = fifs1.application_id and fifs.id_flex_code = fifs1.id_flex_code
	  and 	fifs.id_flex_num = fifs1.id_flex_num
	  and 	fifs1.enabled_flag = 'Y'
	  and 	fifs1.flex_value_set_id = ffvs.flex_value_set_id
	  and 	ffvs.flex_value_set_name = fdfcu.descriptive_flex_context_code
	  and 	fdfcu.descriptive_flexfield_name = 'FND_FLEX_VALUES'
--	  AND 	fdfcu.end_user_column_name = p_parameter
          AND   fdfcu.application_column_name = p_parameter
	  AND 	ffvs.format_type = v_format_type;