APPS.PSP_TEMPLATE_SELECTION SQL Statements

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

Line: 30

  SELECT request_id, legislative_parameters  into p_request_id, param_string  from pay_payroll_actions where payroll_action_id = p_payroll_action_id;

Line: 63

   /*   insert record in psp_report_templates_h   */

    insert into psp_report_templates_h(request_id, template_id, template_name,
   business_group_id, set_of_books_id, report_type, period_frequency_id,
   report_template_code, display_all_emp_distrib_flag,
   manual_entry_override_flag,  approval_type, custom_approval_code,
  sup_levels, preview_effort_report_flag, notification_reminder_in_days, parameter_name_1, parameter_name_2, parameter_name_3, parameter_name_4, parameter_name_5,
  parameter_name_6, parameter_name_7, parameter_name_8, parameter_name_9, parameter_name_10,
  parameter_name_11, parameter_name_12, parameter_name_13, parameter_name_14, parameter_name_15,
  parameter_name_16, parameter_name_17, parameter_name_18, parameter_name_19,
  parameter_name_20,parameter_value_1, parameter_value_2, parameter_value_3, parameter_value_4,
  parameter_value_5, parameter_value_6, parameter_value_7, parameter_value_8,
  parameter_value_9, parameter_value_10, parameter_value_11,
  parameter_value_12, parameter_value_13, parameter_value_14, parameter_value_15,
  parameter_value_16, parameter_value_17, parameter_value_18, parameter_value_19, parameter_value_20, submission_date,
  initiator_person_id, initiator_file_id, initiator_accept_flag,
  final_recipients_file_id, sprcd_tolerance_amt, sprcd_tolerance_percent, description, legislation_code,
  payroll_action_id,  last_update_date, last_updated_by, last_update_login,
  created_by, creation_date,hundred_pcent_eff_at_per_asg,selection_match_level) (  select p_request_id, template_id, template_name,
  business_group_id, set_of_books_id , report_type, period_frequency_id,
 report_template_code, display_all_emp_distrib_flag,
  manual_entry_override_flag, approval_type,
  custom_approval_code, sup_levels, preview_effort_report_flag, notification_reminder_in_days,
  'TEMPLATE_ID', 'EFFORT_START', 'EFFORT_END',
  'REPORT_LAYOUT', 'SORT_OPTION1','ORDER_BY1', 'SORT_OPTION2', 'ORDER_BY' , 'SORT_OPTION3', 'ORDER_BY3', 'SORT_OPTION4',
  'ORDER_BY4', NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, l_psp_template_id, l_psp_effort_start, l_psp_effort_end,  l_psp_report_layout, l_psp_sort_option1, l_psp_order_by1,
   l_psp_sort_option2, l_psp_order_by2, l_psp_sort_option3, l_psp_order_by3, l_psp_sort_option4, l_psp_order_by4, NULL, NULL, NULL
,  NULL, NULL, NULL, NULL, NULL, sysdate, fnd_global.employee_id, NULL,
   NULL, NULL, sprcd_tolerance_amt, sprcd_tolerance_percent, description, legislation_code,
  p_payroll_action_id,  last_update_date, last_updated_by, last_update_login, created_by, creation_date, hundred_pcent_eff_at_per_asg,selection_match_level from psp_report_templates where template_id = l_psp_template_id);

Line: 95

   insert into psp_report_template_details_h (request_id, template_detail_id, criteria_lookup_type,
   criteria_lookup_code, include_exclude_flag, criteria_value1, criteria_value2, criteria_value3,
   last_update_date, last_updated_by, last_update_login, created_by, creation_date) (select
   p_request_id, template_detail_id,
   criteria_lookup_type, criteria_lookup_code, include_exclude_flag,
   criteria_value1, criteria_value2, criteria_value3 , last_update_date,
   last_updated_by, last_update_login, created_by, creation_date from
   psp_report_template_details where template_id = l_psp_template_id);

Line: 108

       l_sqlerrm := 'Error inserting into psp_report_templates_history '|| substr(sqlerrm,1,180);

Line: 120

     fnd_file.put_line(fnd_file.log, 'Insert into Template History failed '||sqlerrm);

Line: 155

      insert_into_template_history(pactid, l_request_id );

Line: 189

     get_final_selection_list(errBuf, retCode, l_request_id, TRUE);

Line: 193

   SELECT nvl(count( person_id),0) into l_cnt from psp_selected_persons_t where
    request_id = l_request_id;

Line: 204

	INSERT INTO  psp_report_errors
		(error_sequence_id,		request_id,		message_level,
		source_id,			error_message,		retry_request_id,
		pdf_request_id,			source_name,		parent_source_id,
		parent_source_name)
	VALUES	(psp_report_errors_s.NEXTVAL,	l_request_id,		'W',
		NULL,				l_msg_str,		NULL,
		NULL,				NULL,			NULL,
		NULL);

Line: 218

   delete from psp_Selected_persons_t outer where request_id = l_request_id and
	(person_id,  nvl( assignment_id, -999) ) in (select
person_id,  nvl( assignment_id, -999) from psp_Selected_persons_t inner where inner.person_id=outer.person_id and inner.request_id =outer.request_id and inner.rowid > outer.rowid);

Line: 227

     sqlstr := 'select distinct person_id from psp_selected_persons_t pspt,
      pay_payroll_actions pact
      where pact.payroll_action_id = :payroll_action_id and
       pspt.request_id= pact.request_id ORDER BY pspt.person_id';

Line: 239

	INSERT INTO  psp_report_errors
		(error_sequence_id,		request_id,		message_level,
		source_id,			error_message,		retry_request_id,
		pdf_request_id,			source_name,		parent_source_id,
		parent_source_name)
	VALUES	(psp_report_errors_s.NEXTVAL,	l_request_id,		'E',
		NULL,				l_msg_str,		NULL,
		NULL,				NULL,			NULL,
		NULL);

Line: 273

PROCEDURE GET_FINAL_SELECTION_LIST(errBuf  OUT NOCOPY VARCHAR2,
    retCode 	    	OUT NOCOPY VARCHAR2,
    p_request_id  	IN	NUMBER,
    p_person_asg_flag  	IN  	BOOLEAN
		 )
  AS

PRAGMA	AUTONOMOUS_TRANSACTION;

Line: 284

  user has not selected any selection criteria


*/

   CURSOR get_template_selection
   IS
   SELECT  template_id, parameter_value_2, parameter_value_3, business_group_id, set_of_books_id, selection_match_level
    from psp_report_templates_h
    where request_id = p_request_id;

Line: 302

	l_template_selection	varchar2(3);

Line: 307

     OPEN get_template_selection;

Line: 309

     FETCH get_template_selection into l_template_id, l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id, l_template_selection;

Line: 311

     CLOSE get_template_selection;

Line: 314

	IF (l_template_selection = 'EMP') THEN
     get_lowest_cardinality(p_request_id , l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id);

Line: 319

      hr_utility.trace( 'get_template_selection-> After get_lowest_cardinality'   );

Line: 326

		FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

Line: 328

      hr_utility.trace( 'get_template_selection-> After prepare_initial_person_list ' );

Line: 333

      hr_utility.trace( 'get_template_selection-> After prune initial_person_list ' );

Line: 339

      hr_utility.trace( 'get_template_selection-> After apply exclusion criteria ' );

Line: 343

	IF (l_template_selection = 'ASG') THEN
		get_asg_lowest_cardinality	(p_request_id		=>	p_request_id,
						p_effort_start		=>	l_effort_start,
						p_effort_end		=>	l_effort_end,
						p_business_group_id	=>	l_business_group_id,
						p_set_of_books_id	=>	l_set_of_books_id);

Line: 349

		hr_utility.trace('get_template_selection-> After get_asg_lowest_cardinality');

Line: 356

		hr_utility.trace('get_template_selection-> After prepare_initial_asg_list');

Line: 358

		FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

Line: 366

			hr_utility.trace('get_template_selection-> After prune initial_asg_list');

Line: 374

		hr_utility.trace('get_template_selection-> After apply asg exclusion criteria');

Line: 379

      hr_utility.trace( 'get_template_selection-> After apply_ff_formula_exclusion  ' );

Line: 384

      select nvl(count(person_id),0) into l_count  from psp_Selected_persons_t where request_id=p_request_id;

Line: 386

      hr_utility.trace( 'get_template_selection-> After getting count=  '||l_count );

Line: 393

     FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');

Line: 401

      hr_utility.trace( 'get_template_selection->'||sqlerrm );

Line: 427

CURSOR get_lowest_cardinality_csr is select lookup_code  from
   psp_selection_cardinality_gt  where total_count > 0 ORDER BY total_count asc;

Line: 431

CURSOR get_zero_cardinality_csr is select lookup_code from
     psp_selection_cardinality_gt where total_count=0;

Line: 435

CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER)
   IS
   Select  distinct (criteria_lookup_code) from
    psp_report_template_details_h where request_id = p_request_id and
    include_exclude_flag='I' and criteria_lookup_type='PSP_SELECTION_CRITERIA';

Line: 442

/* The below cursors would only be used only when no statis selection criteria have been chosen */

CURSOR PPG_CURSOR IS
      select criteria_value1, criteria_value2 from
      psp_report_template_details_h  where  request_id = p_request_id and
      include_exclude_flag='I' and
      criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG';

Line: 452

      select criteria_value1 , criteria_value2, criteria_value3 from
      psp_report_template_details_h where request_id = p_request_id and
      include_exclude_flag='I' and
      criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA';

Line: 460

     OPEN get_selection_cardinality_csr(p_request_id);

Line: 461

     FETCH get_selection_cardinality_csr BULK COLLECT into template_rec.array_sel_criteria;

Line: 463

     CLOSE get_selection_cardinality_csr;

Line: 473

            insert into psp_selection_cardinality_gt(lookup_code, total_count)  (
            select  'PTY', count(distinct ppf.person_id)  from per_people_f ppf, per_assignments_f paf where
            person_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='PTY'
                  and include_exclude_flag='I'
            and request_id = p_request_id )
            AND	paf.person_id = ppf.person_id
	    AND	paf.assignment_type = 'E'
	    AND	paf.effective_start_date <= p_effort_end
	    AND	paf.effective_end_date >= p_effort_start
	    and
            ppf.effective_start_date <= p_effort_end and
            ppf.effective_end_date >= p_effort_start)  ;

Line: 490

            insert into psp_selection_cardinality_gt(lookup_code, total_count)  (
            select  'EMP', count(distinct ppf.person_id)  from per_all_people_f ppf, per_assignments_f paf  where
             ppf.person_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='EMP' and
                   include_exclude_flag='I'
           and  request_id = p_request_id )
            AND	paf.person_id = ppf.person_id
            AND	paf.assignment_type = 'E'
            AND	paf.effective_start_date <= p_effort_end
            AND	paf.effective_end_date >= p_effort_start
	    and
            ppf.effective_start_date <= p_effort_end and
            ppf.effective_end_date >= p_effort_start)  ;

Line: 506

            insert into psp_selection_cardinality_gt(lookup_code, total_count) (
            select 'SUP', count(distinct person_id) from per_all_assignments_f paf where
            supervisor_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='SUP'
            and request_id = p_request_id
                  and include_exclude_flag='I'
            )
	    AND paf.assignment_type = 'E'
	    and
            effective_start_date <= p_effort_end and
            effective_end_date >= p_effort_start) ;

Line: 521

 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
SELECT  'AWD', COUNT(DISTINCT psl.person_id)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='AWD' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 559

INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
SELECT  'ATY', COUNT(DISTINCT psl.person_id)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      gms_awards_all gaa,
      per_time_periods ptp
WHERE psl.award_id = gaa.award_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='ATY' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id = p_request_id AND
  gaa.type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 596

             INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
SELECT  'PRT', COUNT(DISTINCT psl.person_id)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      pa_projects_all ppa ,
      per_time_periods ptp
WHERE psl.project_id = ppa.project_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRT' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  ppa.project_type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 633

             insert into psp_selection_cardinality_gt(lookup_code, total_count)(
SELECT  'PRJ', COUNT(DISTINCT psl.person_id)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRJ' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 669

            insert into psp_selection_cardinality_gt(lookup_code, total_count)(
            select 'PAY', count(distinct person_id) from per_assignments_f paf where
            payroll_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='PAY'
                  and include_exclude_flag='I' and request_id = p_request_id
            )
	    AND paf.assignment_type = 'E'
	    and
            effective_start_date <= p_effort_end and
            effective_end_date >= p_effort_start) ;

Line: 682

            insert into psp_selection_cardinality_gt(lookup_code, total_count)(
            select 'LOC', count(distinct person_id) from per_assignments_f paf where
            location_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='LOC'
                  and include_exclude_flag='I' and request_id = p_request_id
            )
	    AND paf.assignment_type = 'E'
	    and
            effective_start_date <= p_effort_end and
            effective_end_date >= p_effort_start) ;

Line: 695

             insert into psp_selection_cardinality_gt(lookup_code, total_count)(
             select 'ORG', count(distinct person_id) from per_assignments_f paf where
             organization_id in (select TO_NUMBER(criteria_value1) from  psp_report_template_details_h prtd
             where
             criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ORG'
                  and include_exclude_flag='I' and request_id = p_request_id
             )
             AND paf.assignment_type = 'E'
	     and effective_start_date  <= p_effort_end and
             effective_end_date >= p_effort_start);

Line: 708

            insert into psp_selection_cardinality_gt(lookup_code, total_count)(
            select 'JOB', count(distinct person_id) from per_assignments_f paf where
            job_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='JOB'
                  and include_exclude_flag='I' and request_id=p_request_id
            )
	    AND paf.assignment_type = 'E'
	    and
            effective_start_date <= p_effort_end and
            effective_end_date >= p_effort_start) ;

Line: 721

            insert into psp_selection_cardinality_gt(lookup_code, total_count) (
            select 'POS', count(distinct person_id) from per_assignments_f paf where
            position_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='POS'
                  and include_exclude_flag='I' and request_id = p_request_id
            )
	    AND paf.assignment_type = 'E'
	    and
            effective_start_date <= p_effort_end and
            effective_end_date >= p_effort_start) ;

Line: 734

            insert into psp_selection_cardinality_gt(lookup_code, total_count) (
            select 'ASS', count(distinct person_id) from per_assignments_f paf where
            assignment_status_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
            prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ASS'
                  and include_exclude_flag='I' and request_id = p_request_id
            )
	    AND paf.assignment_type = 'E'
	    and
            effective_start_date <= p_effort_end and
            effective_end_date >= p_effort_start) ;

Line: 748

            insert into psp_selection_cardinality_gt(lookup_code, total_count) (
            select 'CST', count(distinct paf.person_id) from per_assignments_f paf
            , pay_payrolls_f ppf  where
            ppf.payroll_id = paf.payroll_id and
            ppf.consolidation_set_id in
            (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
            prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='CST'
                  and include_exclude_flag='I'  and request_id = p_request_id
            )
	    AND paf.assignment_type = 'E'
	    and
            ppf.effective_start_date <=  p_effort_end
            and ppf.effective_end_date >= p_effort_start
            and  paf.effective_start_date <= p_effort_end and
            paf.effective_end_date >= p_effort_start);

Line: 768

            insert into psp_selection_cardinality_gt(lookup_code, total_count) (
           select 'AST', count(distinct paf.person_id) from per_all_assignments_f paf, hr_assignment_sets has ,
           hr_assignment_Set_amendments hasa
            where
            has.assignment_set_id in  (
            select TO_NUMBER(criteria_value1) from psp_report_template_details_h
            prtd where
            criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='AST'
                  and include_exclude_flag='I' and request_id =p_request_id
            )
           and
           ( (   paf.payroll_id = has.payroll_id and
            paf.effective_start_date <= p_effort_end and
            paf.effective_end_date >= p_effort_start
            AND paf.assignment_type = 'E'
	    and has.assignment_set_id = hasa.assignment_Set_id
            )
            OR
            (
            paf.assignment_id = hasa.assignment_id and
             paf.effective_start_date <= p_effort_end and
            paf.effective_end_date >= p_effort_start
            AND paf.assignment_type = 'E'
	    and hasa.assignment_Set_id=has.assignment_Set_id and include_or_exclude ='I') )
            and not exists (select assignment_id from hr_assignment_Set_amendments hasa where
            hasa.assignment_id = paf.assignment_id and hasa.include_or_exclude ='E'
            AND paf.assignment_type = 'E'
	    and  paf.effective_start_date <= p_effort_end and
            paf.effective_end_date >= p_effort_start
            ));

Line: 819

  /* Next find the selection criteria with lowest cardinality. Use it to prepare the initial list */

     OPEN get_lowest_cardinality_csr;

Line: 847

      /* To handle the case where one or more of the selection criterai have 0 cardinality */

     END IF;

Line: 860

         /* when no static selection criteria have been chosen, then invoke the dynamic selection criteria
         */


   -- fnd_file.put_line(fnd_file.log,  ' g_lookup_code  is null ');

Line: 889

                 select l_sql_string
                 || '  OR ' || l_criteria_value1 || ' =  ' || ''''|| l_criteria_value2 ||''''
                 into g_exec_string from psp_report_template_details_h ;

Line: 905

                         g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
                         (select '||  '''' || 'PPG' || ''''||' , count(person_id)
                          from per_assignments_f paf,
                          pay_people_groups ppg
                          where  paf.people_group_id= ppg.people_group_id
                          AND	paf.assignment_type = ''' || 'E' || '''
			  and paf.effective_end_date >= :p_effort_Start and
                            paf.effective_start_date <= :p_effort_end
                          and
                          ppg.people_group_id
                          in (select people_group_id from pay_people_groups
                          where ' || l_sql_string
                          || ' ))
                           ';

Line: 963

            g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
            ( select '|| '''' || 'GLA' || ''''|| ' , count( distinct psl.person_id)
            from psp_summary_lines psl, psp_distribution_lines_history pdnh, psp_pre_gen_dist_lines_history ppg,
            psp_adjustment_lines_history palh
             , gl_code_combinations gcc
            where
            psl.business_group_id = '|| p_business_group_id || ' and
            psl.set_of_books_id =' || p_set_of_books_id || ' and
             gcc.code_combination_id= psl.gl_code_combination_id
and
            psl.summary_line_id = pdnh.summary_line_id(+) and
            psl.summary_line_id = ppg.summary_line_id(+) and
            psl.summary_line_id = palh.summary_line_id(+) and
            psl.status_code='||''''||'A'||''''||' and
  ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
     pdnh.distribution_date between :p_effort_start and :p_effort_end
     and pdnh.summary_line_id = psl.summary_line_id
  and pdnh.reversal_entry_flag is null
 and pdnh.adjustment_batch_name is null
)  OR
  ( psl.source_type='||''''||'P'||''''||' and
ppg.distribution_date between :p_effort_start   and :p_effort_end  and
 ppg.summary_line_id = psl.summary_line_id and
ppg.adjustment_batch_name is null and
ppg.reversal_entry_flag is null)
    OR (psl.source_type='||''''||'A'||''''||' and
   palh.summary_line_id = psl.summary_line_id and
   palh.reversal_entry_flag is null and
   palh.adjustment_batch_name is null and
   NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
 || '  and  palh.distribution_date between  :p_effort_start and :p_effort_end )) and
             gcc.code_combination_id= psl.gl_code_combination_id and
            gcc.code_combination_id in (select code_combination_id from gl_code_combinations
            where ' || l_sql_string
            || ' )) ';

Line: 1032

      select criteria_value1, criteria_value2 from
      psp_report_template_details_h  where  request_id = p_request_id and
      criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and
      include_exclude_flag='I';

Line: 1039

      select criteria_value1 , criteria_value2, criteria_value3 from
      psp_report_template_details_h where request_id = p_request_id and
      criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and
       include_exclude_flag='I';

Line: 1056

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, paf.person_id, paf.assignment_id
		FROM	per_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	person_type_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='PTY'
						AND	request_id = p_request_id
						AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1093

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	ppf.person_id IN	(SELECT TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='EMP'
						AND	include_exclude_flag='I'
						AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1130

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	supervisor_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='SUP'
						AND	include_exclude_flag='I'
						AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1169

             insert into psp_selected_persons_t(request_id, person_id, assignment_id)(
             select distinct p_request_id, psl.person_id  , paf.assignment_id
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp,
      per_assignments_f paf
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='AWD' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
  paf.person_id = psl.person_id AND
  paf.assignment_type = 'E' AND
  paf.effective_start_date <= p_effort_end AND
  paf.effective_end_date >= p_effort_start AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL
      AND NVL(palh.original_line_flag, 'N') ='N')));

Line: 1211

INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(
SELECT  DISTINCT  p_request_id, psl.person_id, paf.assignment_id
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      gms_awards_all gaa ,
      per_time_periods ptp,
      per_assignments_f paf
WHERE psl.award_id = gaa.award_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='ATY' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  gaa.type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
  paf.person_id = psl.person_id AND
  paf.assignment_type = 'E' AND
  paf.effective_start_date <= p_effort_end AND
  paf.effective_end_date >= p_effort_start AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 1254

             INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(
SELECT  DISTINCT  p_request_id, psl.person_id, paf.assignment_id
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      pa_projects_all ppa ,
      per_time_periods ptp,
      per_assignments_f paf
WHERE psl.project_id = ppa.project_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRT' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  ppa.project_type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
  paf.person_id = psl.person_id AND
  paf.assignment_type = 'E' AND
  paf.effective_start_date <= p_effort_end AND
  paf.effective_end_date >= p_effort_start AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 1302

             insert into psp_selected_persons_t(request_id, person_id, assignment_id)(
             select distinct p_request_id, psl.person_id, paf.assignment_id
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp,
      per_assignments_f paf
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRJ' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  psl.business_group_id = p_business_group_id AND
psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
  paf.person_id = psl.person_id AND
  paf.assignment_type = 'E' AND
  paf.effective_start_date <= p_effort_end AND
  paf.effective_end_date >= p_effort_start AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 1343

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	payroll_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='PAY'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1380

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	location_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='LOC'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1417

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	organization_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='ORG'
						AND	request_id = p_request_id
						AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1454

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	pay_payrolls_f pp,
			per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	pp.payroll_id = paf.payroll_id
		AND	pp.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='CST'
							AND	include_exclude_flag='I'
							AND request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	pp.effective_start_date <= p_effort_end
		AND	pp.effective_end_date >= p_effort_start
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1495

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, paf.person_id, paf.assignment_id
		FROM	per_all_assignments_f paf,
			hr_assignment_sets has,
			hr_assignment_set_amendments hasa,
			per_all_people_f ppf,
			per_assignment_status_types past
		WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='AST'
							AND	include_exclude_flag='I'
							AND request_id =p_request_id)
		AND	(	(paf.payroll_id = has.payroll_id
				AND	paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	has.assignment_set_id = hasa.assignment_set_id)
			OR	(paf.assignment_id = hasa.assignment_id
				AND	paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	hasa.assignment_set_id=has.assignment_set_id AND include_or_exclude ='I'))
		AND	NOT EXISTS	(SELECT	assignment_id
					FROM	hr_assignment_set_amendments hasa
					WHERE	hasa.assignment_id = paf.assignment_id AND hasa.include_or_exclude ='E'
					AND	paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start)
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1548

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	job_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='JOB'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1585

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	position_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='POS'
					AND	request_id = p_request_id
					AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1622

      		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	paf.assignment_status_type_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='ASS'
							AND	request_id = p_request_id
							AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	ppf.business_group_id = p_business_group_id
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 1677

                 select l_sql_string
                 || '  OR ' || l_criteria_value1 || ' =  ' || ''''|| l_criteria_value2 ||''''
                 into g_exec_string from psp_report_template_details_h ;

Line: 1691

                         g_exec_string := 'insert into psp_selected_persons_t
                         (request_id, person_id, assignment_id)
                         (select :request_id  , paf.person_id, paf.assignment_id
                          from per_assignments_f paf,
                          pay_people_groups ppg,
			  per_all_people_f ppf,
			  per_assignment_status_types past
                          where  paf.people_group_id= ppg.people_group_id
			  AND	paf.person_id = ppf.person_id
			  AND	paf.assignment_status_type_id =   past.assignment_status_type_id
                          AND	paf.assignment_type = ''' || 'E' || '''
			  AND   paf.effective_end_date >= :p_effort_Start
			  AND	paf.effective_start_date <= :p_effort_end
			  AND	ppf.effective_end_date >= :p_effort_start
			  AND	ppf.effective_start_date <= :p_effort_end
                          AND	ppg.people_group_id IN (SELECT people_group_id FROM pay_people_groups
							WHERE ' || l_sql_string|| ' )
			AND (		past.per_system_status <> '''|| 'TERM_ASSIGN' ||'''
				OR	EXISTS ( select null
					FROM	psp_pre_gen_dist_lines_history
					WHERE	distribution_date between :p_effort_start and :p_effort_end
					AND	assignment_id = paf.assignment_id
	 				AND     reversal_entry_flag IS NULL
					AND	rownum=1 )
				OR      EXISTS (SELECT null
					FROM   psp_distribution_lines_history pdlh
					, psp_summary_lines psl
					WHERE	pdlh.summary_line_id = psl.summary_line_id
					AND	distribution_date between :p_effort_start and :p_effort_end
					AND	psl.person_id = paf.person_id
					AND	psl.assignment_id =  paf.assignment_id
	 				AND     reversal_entry_flag IS NULL
					AND	rownum=1)))';

Line: 1764

            g_exec_string := 'insert into psp_selected_persons_t(request_id, person_id, assignment_id)
            ( select  :p_request_id, psl.person_id, psl.assignment_id
            from psp_summary_lines psl, psp_distribution_lines_history pdnh,
            psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
             gl_code_combinations gcc
            where
            psl.business_group_id = '|| p_business_group_id || ' and
            psl.set_of_books_id = ' || p_Set_of_books_id ||' and
            psl.summary_line_id = pdnh.summary_line_id(+) and
            psl.summary_line_id = ppg.summary_line_id(+) and
            psl.summary_line_id = palh.summary_line_id(+) and
            psl.status_code= '||''''||'A'||''''||' and
  ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
     pdnh.distribution_date between  :p_effort_start  and :p_effort_end
 and pdnh.reversal_entry_flag is null
 and pdnh.summary_line_id = psl.summary_line_id
 and pdnh.adjustment_batch_name is null
)  OR
  ( psl.source_type='||''''||'P'||''''||' and
ppg.distribution_date between :p_effort_start  and :p_effort_end  and
ppg.summary_line_id = psl.summary_line_id and
ppg.adjustment_batch_name is null and
ppg.reversal_entry_flag is null)
    OR (psl.source_type='||''''||'A'||''''||' and
    palh.adjustment_batch_name is null and
    palh.summary_line_id =psl.summary_line_id and
     NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
   || ' and palh.distribution_date between :p_effort_start  and :p_effort_end )) and
             gcc.code_combination_id= psl.gl_code_combination_id and
            gcc.code_combination_id in (select code_combination_id from gl_code_combinations
            where ' || l_sql_string
            || ' )) ';

Line: 1807

        insert into psp_selected_persons_t(request_id, person_id)(select p_request_id,
        person_id from per_people_f where
		business_group_id = p_business_group_id and
             effective_start_date  <= p_effort_end and
             effective_end_date >= p_effort_start);

Line: 1825

CURSOR get_all_selection_criteria(p_request_id IN  NUMBER) is
   SELECT distinct criteria_lookup_code,
   include_exclude_flag from
   psp_report_template_details_h where request_id = p_request_id and
   criteria_lookup_type='PSP_SELECTION_CRITERIA' ORDER BY include_exclude_flag;

Line: 1833

      select criteria_value1, criteria_value2 from
      psp_report_template_details_h  where  request_id = p_request_id and
      criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and include_exclude_flag='I';

Line: 1839

      select criteria_value1 , criteria_value2, criteria_value3 from
      psp_report_template_details_h where request_id = p_request_id and
      criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and include_exclude_flag='I';

Line: 1862

     open get_all_selection_criteria(p_request_id);

Line: 1863

     fetch get_all_selection_criteria BULK COLLECT into template_sel_criteria.array_sel_criteria,
       template_sel_criteria.array_inc_exc_flag;

Line: 1866

     close get_all_selection_criteria;

Line: 1880

		DELETE FROM psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
                /* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	person_id NOT IN	(SELECT	NVL( person_id, 0)
                AND    NOT EXISTS (     SELECT 1
				FROM	per_people_f ppf,
					psp_report_template_details_h prtd,
					per_assignments_f paf,
					per_assignment_status_types past
				WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
				AND	criteria_lookup_code='PTY'
				AND	paf.person_id = ppf.person_id
				AND	paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	ppf.effective_start_date <= p_effort_end
				AND	ppf.effective_end_date >= p_effort_start
				AND	include_exclude_flag='I'
				AND	ppf.person_type_id  = TO_NUMBER(prtd.criteria_value1)
				AND	prtd.request_id = p_request_id
				AND     ppf.person_id = pspt.person_id
				AND	paf.assignment_status_type_id =   past.assignment_status_type_id
				AND (		past.per_system_status <> 'TERM_ASSIGN'
					OR	EXISTS ( select null
						FROM	psp_pre_gen_dist_lines_history
						WHERE	distribution_date between p_effort_start and p_effort_end
						AND	assignment_id = paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1 )
					OR      EXISTS (SELECT null
						FROM   psp_distribution_lines_history pdlh
						, psp_summary_lines psl
						WHERE	pdlh.summary_line_id = psl.summary_line_id
						AND	distribution_date between p_effort_start and p_effort_end
						AND	psl.person_id = paf.person_id
						AND	psl.assignment_id =  paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1)));

Line: 1921

       		DELETE FROM psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
                 /* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	person_id  NOT IN	(SELECT	NVL(person_id,0)
                 AND    NOT EXISTS (     SELECT 1
				FROM	per_all_people_f ppf,
					per_assignments_f paf,
					per_assignment_status_types past
				WHERE	ppf.person_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='EMP'
					AND	include_exclude_flag='I'
					AND	prtd.request_id = p_request_id)
					AND	paf.person_id = ppf.person_id
					AND	paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start
				AND	ppf.effective_start_date <= p_effort_end
				AND	ppf.effective_end_date >= p_effort_start
				AND     ppf.person_id = pspt.person_id
				AND	paf.assignment_status_type_id =   past.assignment_status_type_id
				AND (		past.per_system_status <> 'TERM_ASSIGN'
					OR	EXISTS ( select null
						FROM	psp_pre_gen_dist_lines_history
						WHERE	distribution_date between p_effort_start and p_effort_end
						AND	assignment_id = paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1 )
					OR      EXISTS (SELECT null
						FROM   psp_distribution_lines_history pdlh
						, psp_summary_lines psl
						WHERE	pdlh.summary_line_id = psl.summary_line_id
						AND	distribution_date between p_effort_start and p_effort_end
						AND	psl.person_id = paf.person_id
						AND	psl.assignment_id =  paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1)));

Line: 1963

		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
                /* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
                AND    NOT EXISTS (     SELECT 1
					FROM	per_all_assignments_f paf,
						psp_report_template_details_h prtd,
						per_assignment_status_types past
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='SUP'
					AND	include_exclude_flag='I'
					AND	paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id
					AND     paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	effective_end_date >= p_effort_start
					AND     paf.person_id = pspt.person_id
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> 'TERM_ASSIGN'
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between p_effort_start and p_effort_end
							AND	assignment_id = paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between p_effort_start and p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1)));

Line: 2003

 delete from  psp_selected_persons_t where request_id = p_request_id AND person_id not in (
             select psl.person_id
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='AWD' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL
      AND NVL(palh.original_line_flag, 'N') ='N')));

Line: 2041

             DELETE FROM  psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
SELECT  NVL(psl.person_id,0)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      gms_awards_all gaa ,
      per_time_periods ptp
WHERE psl.award_id = gaa.award_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='ATY' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  gaa.type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
     NVL(palh.original_line_flag, 'N') ='N')));

Line: 2079

             DELETE FROM  psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
SELECT  NVL(psl.person_id,0)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      pa_projects_all ppa ,
      per_time_periods ptp
WHERE psl.project_id = ppa.project_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRT' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
  ppa.project_type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
     NVL(palh.original_line_flag, 'N') ='N')));

Line: 2117

             delete from  psp_selected_persons_t where request_id = p_request_id AND person_id not in (
             select nvl( psl.person_id ,0)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRJ' AND
  prtd.include_exclude_flag='I' AND
  prtd.request_id =p_request_id AND
psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 2153

      		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
		/* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
                AND    NOT EXISTS (     SELECT 1
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd,
						per_assignment_status_types past
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='PAY'
					AND	include_exclude_flag='I'
                                        AND     paf.assignment_type = 'E'
					AND	effective_start_date <= p_effort_end
					AND	effective_end_date >= p_effort_start
					AND	paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id
					AND     paf.person_id = pspt.person_id
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> 'TERM_ASSIGN'
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between p_effort_start and p_effort_end
							AND	assignment_id = paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between p_effort_start and p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1)));

Line: 2192

		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
		/* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
                AND    NOT EXISTS (     SELECT 1
					FROM	per_assignments_f paf ,
						psp_report_template_details_h prtd,
						per_assignment_status_types past
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='LOC'
                                        AND     paf.assignment_type = 'E'
					AND	effective_start_date <= p_effort_end
					AND	effective_end_date >= p_effort_start
					AND	include_exclude_flag='I'
					AND	paf.location_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id
					AND     paf.person_id = pspt.person_id
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> 'TERM_ASSIGN'
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between p_effort_start and p_effort_end
							AND	assignment_id = paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between p_effort_start and p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1)));

Line: 2229

		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
	--	AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
		AND    NOT EXISTS (     SELECT 1
					FROM	per_assignments_f paf ,
						psp_report_template_details_h prtd,
						per_assignment_status_types past
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='ORG'
					AND	include_exclude_flag='I'
                                        AND     paf.assignment_type = 'E'
					AND	effective_start_date <= p_effort_end
					AND	effective_end_date >= p_effort_start
					AND	paf.organization_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id
					AND     paf.person_id = pspt.person_id
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> 'TERM_ASSIGN'
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between p_effort_start and p_effort_end
							AND	assignment_id = paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between p_effort_start and p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1)));

Line: 2265

      		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
		/* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
		AND    NOT EXISTS (     SELECT 1
				FROM	per_assignments_f paf,
					pay_payrolls_f ppf,
					per_assignment_status_types past
				WHERE	ppf.payroll_id = paf.payroll_id
				AND	ppf.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='CST'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
				AND     paf.assignment_type = 'E'
				AND	ppf.effective_start_date <= p_effort_end
				AND	ppf.effective_end_date >= p_effort_start
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND     paf.person_id = pspt.person_id
				AND	paf.assignment_status_type_id =   past.assignment_status_type_id
				AND (		past.per_system_status <> 'TERM_ASSIGN'
					OR	EXISTS ( select null
						FROM	psp_pre_gen_dist_lines_history
						WHERE	distribution_date between p_effort_start and p_effort_end
						AND	assignment_id = paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1 )
					OR      EXISTS (SELECT null
						FROM   psp_distribution_lines_history pdlh
						, psp_summary_lines psl
						WHERE	pdlh.summary_line_id = psl.summary_line_id
						AND	distribution_date between p_effort_start and p_effort_end
						AND	psl.person_id = paf.person_id
						AND	psl.assignment_id =  paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1)));

Line: 2307

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id NOT IN	(SELECT	NVL(paf.person_id,0)
		FROM	per_all_assignments_f paf,
			hr_assignment_sets has,
			hr_assignment_set_amendments hasa,
			per_assignment_status_types past
		WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
			FROM	psp_report_template_details_h prtd
			WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
			AND	criteria_lookup_code='AST'
			AND	include_exclude_flag='I'
			AND	request_id =p_request_id)
		AND	(	(paf.payroll_id = has.payroll_id
				AND     paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	has.assignment_set_id = hasa.assignment_set_id)
			OR	(paf.assignment_id = hasa.assignment_id
				AND     paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	hasa.assignment_set_id=has.assignment_set_id
				AND	include_or_exclude ='I'))
		AND	NOT EXISTS	(SELECT	assignment_id
			FROM	hr_assignment_set_amendments hasa
			WHERE	hasa.assignment_id = paf.assignment_id
			AND     paf.assignment_type = 'E'
			AND	hasa.include_or_exclude ='E'
			AND	paf.effective_start_date <= p_effort_end
			AND	paf.effective_end_date >= p_effort_start)
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
				, psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1)));

Line: 2359

      		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
		/* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
		AND    NOT EXISTS (     SELECT 1
				FROM	per_assignments_f paf ,
					psp_report_template_details_h prtd,
					per_assignment_status_types past
				WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
				AND	criteria_lookup_code='JOB'
				AND	include_exclude_flag='I'
                                AND     paf.assignment_type = 'E'
				AND	effective_start_date <= p_effort_end
				AND	effective_end_date >= p_effort_start
				AND	paf.job_id = TO_NUMBER(prtd.criteria_value1)
				AND	prtd.request_id = p_request_id
				AND     paf.person_id = pspt.person_id
				AND	paf.assignment_status_type_id =   past.assignment_status_type_id
				AND (		past.per_system_status <> 'TERM_ASSIGN'
					OR	EXISTS ( select null
						FROM	psp_pre_gen_dist_lines_history
						WHERE	distribution_date between p_effort_start and p_effort_end
						AND	assignment_id = paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1 )
					OR      EXISTS (SELECT null
						FROM   psp_distribution_lines_history pdlh
						, psp_summary_lines psl
						WHERE	pdlh.summary_line_id = psl.summary_line_id
						AND	distribution_date between p_effort_start and p_effort_end
						AND	psl.person_id = paf.person_id
						AND	psl.assignment_id =  paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1)));

Line: 2396

		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
		/* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
		AND    NOT EXISTS (     SELECT 1
				FROM	per_assignments_f paf ,
					psp_report_template_details_h prtd,
					per_assignment_status_types past
				WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
				AND	criteria_lookup_code='POS'
                                AND     paf.assignment_type = 'E'
						 and
                                                 effective_start_date <= p_effort_end and
                                                 effective_end_date >= p_effort_start
				AND	include_exclude_flag='I'
				AND	paf.position_id = TO_NUMBER(prtd.criteria_value1)
				AND	prtd.request_id = p_request_id
				AND     paf.person_id = pspt.person_id
				AND	paf.assignment_status_type_id =   past.assignment_status_type_id
				AND (		past.per_system_status <> 'TERM_ASSIGN'
					OR	EXISTS ( select null
						FROM	psp_pre_gen_dist_lines_history
						WHERE	distribution_date between p_effort_start and p_effort_end
						AND	assignment_id = paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1 )
					OR      EXISTS (SELECT null
						FROM   psp_distribution_lines_history pdlh
						, psp_summary_lines psl
						WHERE	pdlh.summary_line_id = psl.summary_line_id
						AND	distribution_date between p_effort_start and p_effort_end
						AND	psl.person_id = paf.person_id
						AND	psl.assignment_id =  paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1)));

Line: 2435

		DELETE FROM	psp_selected_persons_t pspt
		WHERE	request_id = p_request_id
		/* Bug 5087294 : Performance fix replacing not in with not exists */
		-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
		AND    NOT EXISTS (     SELECT 1
				FROM	per_assignments_f paf ,
					psp_report_template_details_h prtd,
					per_assignment_status_types past
				WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
				AND	criteria_lookup_code='ASS'
				AND	include_exclude_flag='I'
				AND	paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
				AND	prtd.request_id = p_request_id
				AND     paf.assignment_type = 'E'
				AND	effective_start_date <= p_effort_end
				AND	effective_end_date >= p_effort_start
				AND     paf.person_id = pspt.person_id
				AND	paf.assignment_status_type_id =   past.assignment_status_type_id
				AND (		past.per_system_status <> 'TERM_ASSIGN'
					OR	EXISTS ( select null
						FROM	psp_pre_gen_dist_lines_history
						WHERE	distribution_date between p_effort_start and p_effort_end
						AND	assignment_id = paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1 )
					OR      EXISTS (SELECT null
						FROM   psp_distribution_lines_history pdlh
						, psp_summary_lines psl
						WHERE	pdlh.summary_line_id = psl.summary_line_id
						AND	distribution_date between p_effort_start and p_effort_end
						AND	psl.person_id = paf.person_id
						AND	psl.assignment_id =  paf.assignment_id
		 				AND     reversal_entry_flag IS NULL
						AND	rownum=1)));

Line: 2493

     select l_sql_string
 || '  OR ' || l_criteria_value1 || ' =  ' || ''''|| l_criteria_value2 ||''''
  into g_exec_string from psp_report_template_details_h ;

Line: 2514

 g_exec_string := 'delete from psp_selected_persons_t sel where request_id  = :request_id
     AND NOT EXISTS
  (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past
           WHERE  paf.people_group_id= ppg.people_group_id
                          AND	paf.assignment_type = ''' || 'E' || '''
			  AND paf.effective_end_date >= :p_effort_Start AND
                            paf.effective_start_date <= :p_effort_end
            AND (' || l_sql_string || ')
            AND paf.person_id = sel.person_id
	    AND	paf.assignment_status_type_id =   past.assignment_status_type_id
	    AND (	past.per_system_status <> ''' || 'TERM_ASSIGN' || '''
			OR	EXISTS ( select null
					FROM	psp_pre_gen_dist_lines_history
					WHERE	distribution_date between :p_effort_start and :p_effort_end
					AND	assignment_id = paf.assignment_id
	 				AND     reversal_entry_flag IS NULL
					AND	rownum=1 )
				OR      EXISTS (SELECT null
					FROM   psp_distribution_lines_history pdlh
					, psp_summary_lines psl
					WHERE	pdlh.summary_line_id = psl.summary_line_id
					AND	distribution_date between :p_effort_start and :p_effort_end
					AND	psl.person_id = paf.person_id
					AND	psl.assignment_id =  paf.assignment_id
	 				AND     reversal_entry_flag IS NULL
					AND	rownum=1)))';

Line: 2577

       g_exec_string := 'delete from psp_selected_persons_t where person_id
       not in (select nvl(psl.person_id,0) from psp_summary_lines psl,
             psp_distribution_lines_history pdnh,
            psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
          gl_code_combinations gcc
           where  gcc.code_combination_id= psl.gl_code_combination_id and
           psl.business_group_id = '|| p_business_group_id || ' and
           psl.set_of_books_id = ' || p_set_of_books_id || ' and
            psl.summary_line_id = pdnh.summary_line_id(+) and
            psl.summary_line_id = ppg.summary_line_id(+) and
            psl.summary_line_id = palh.summary_line_id(+) and
            psl.status_code='||''''||'A'||''''||' and
  ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
     pdnh.distribution_date between :p_effort_start  and  :p_effort_end
 and pdnh.reversal_entry_flag is null
 and psl.summary_line_id = pdnh.summary_line_id
 and pdnh.adjustment_batch_name is null
)  OR
  ( psl.source_type='||''''||'P'||''''||' and
ppg.distribution_date between :p_effort_start  and :p_effort_end  and
ppg.adjustment_batch_name is null and
ppg.summary_line_id =psl.summary_line_id and
ppg.reversal_entry_flag is null)
    OR (psl.source_type='||''''||'A'||''''||' and
    palh.summary_line_id =psl.summary_line_id and
    palh.adjustment_batch_name is null and
     NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
  || ' and palh.distribution_date between :p_effort_start  and :p_effort_end  )) and
             gcc.code_combination_id= psl.gl_code_combination_id and
            gcc.code_combination_id in (select code_combination_id from gl_code_combinations
            where ' || l_sql_string
       || ' )) and request_id  = :request_id';

Line: 2632

      select criteria_value1, criteria_value2 from
      psp_report_template_details_h  where  request_id = p_request_id and
      criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG'
      and include_exclude_flag='E';

Line: 2639

      select criteria_value1 , criteria_value2, criteria_value3 from
      psp_report_template_details_h where request_id = p_request_id and
      criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA'
      and include_exclude_flag='E';

Line: 2672

		DELETE FROM psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN	(SELECT	ppf.person_id
					FROM	per_people_f ppf,
						psp_report_template_details_h prtd,
						per_assignments_f paf,
						per_assignment_status_types past
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='PTY'
				        AND	paf.person_id = ppf.person_id
				        AND	paf.assignment_type = 'E'
				        AND	paf.effective_start_date <= p_effort_end
				        AND	paf.effective_end_date >= p_effort_start
				        AND	ppf.effective_start_date <= p_effort_end
					AND	ppf.effective_end_date >= p_effort_start
					AND	include_exclude_flag='E'
					AND	ppf.person_type_id  = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> 'TERM_ASSIGN'
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between p_effort_start and p_effort_end
							AND	assignment_id = paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between p_effort_start and p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1)));

Line: 2711

		DELETE FROM psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN	(SELECT	DISTINCT ppf.person_id
					FROM	per_all_people_f ppf,
						per_assignments_f paf,
						per_assignment_status_types past
					WHERE	ppf.person_id IN	(SELECT	TO_NUMBER(criteria_value1)
									FROM	psp_report_template_details_h prtd
									WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
									AND	criteria_lookup_code='EMP'
									AND	include_exclude_flag='E'
									AND	prtd.request_id = p_request_id)
					AND	paf.person_id = ppf.person_id
					AND	paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start
					AND	ppf.effective_start_date <= p_effort_end
					AND	ppf.effective_end_date >= p_effort_start
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> 'TERM_ASSIGN'
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between p_effort_start and p_effort_end
							AND	assignment_id = paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between p_effort_start and p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
			 				AND     reversal_entry_flag IS NULL
							AND	rownum=1)));

Line: 2750

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id	 IN	(SELECT	NVL(person_id,0)
						FROM	per_all_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='SUP'
						AND	include_exclude_flag='E'
						AND	paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND     paf.assignment_type = 'E'
						AND	paf.effective_start_date <= p_effort_end
						AND	paf.effective_end_date >= p_effort_start
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
								, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 2786

 delete from  psp_selected_persons_t where request_id = p_request_id AND person_id in (
             select psl.person_id
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='AWD' AND
  prtd.include_exclude_flag='E' AND
  prtd.request_id =p_request_id AND
psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL
      AND NVL(palh.original_line_flag, 'N') ='N')));

Line: 2823

 DELETE FROM  psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
SELECT  NVL(psl.person_id,0)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      gms_awards_all gaa ,
      per_time_periods ptp
WHERE psl.award_id = gaa.award_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='ATY' AND
  prtd.include_exclude_flag='E' AND
  prtd.request_id =p_request_id AND
  gaa.type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
     NVL(palh.original_line_flag, 'N') ='N')));

Line: 2861

             DELETE FROM  psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
SELECT  NVL(psl.person_id,0)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      pa_projects_all ppa ,
      per_time_periods ptp
WHERE psl.project_id = ppa.project_id AND
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRT' AND
  prtd.include_exclude_flag='E' AND
  prtd.request_id =p_request_id AND
  ppa.project_type=prtd.criteria_value1 AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
     NVL(palh.original_line_flag, 'N') ='N')));

Line: 2899

             delete from  psp_selected_persons_t where request_id = p_request_id AND person_id in (
             select nvl( psl.person_id ,0)
 FROM psp_summary_lines psl,
      psp_report_template_details_h prtd ,
      per_time_periods ptp
WHERE
  prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
  prtd.criteria_lookup_code='PRJ' AND
  prtd.include_exclude_flag='E' AND
  prtd.request_id =p_request_id AND
psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
  psl.business_group_id = p_business_group_id AND
  psl.set_of_books_id = p_set_of_books_id AND
  psl.status_code= 'A' AND
  ptp.time_period_id = psl.time_period_id AND
  (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
    WHERE pdnh.summary_line_id = psl.summary_line_id
      AND  pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND  pdnh.reversal_entry_flag IS   NULL
      AND  pdnh.adjustment_batch_name IS NULL )
 OR EXISTS
  (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
    WHERE ppg.summary_line_id = psl.summary_line_id
      AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND ppg.adjustment_batch_name IS   NULL AND
      ppg.reversal_entry_flag IS NULL)
OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
    WHERE palh.summary_line_id = psl.summary_line_id
      AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
      AND palh.adjustment_batch_name IS NULL
      AND palh.reversal_entry_flag IS NULL AND
NVL(palh.original_line_flag, 'N') ='N')));

Line: 2936

      		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id	IN     (SELECT	person_id
						FROM	per_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='PAY'
						AND	include_exclude_flag='E'
						AND     paf.assignment_type = 'E'
						AND	effective_start_date <= p_effort_end
						AND	effective_end_date >= p_effort_start
						AND	paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 2971

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	person_id
						FROM	per_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='LOC'
						AND	include_exclude_flag='E'
						AND     paf.assignment_type = 'E'
						AND	effective_start_date <= p_effort_end
						AND	effective_end_date >= p_effort_start
						AND	paf.location_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3006

      		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	person_id
						FROM	per_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='ORG'
						AND	include_exclude_flag='E'
			                        AND     paf.assignment_type = 'E'
						AND	effective_start_date <= p_effort_end
						AND	effective_end_date >= p_effort_start
						AND	paf.organization_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3045

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	person_id
						FROM	per_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='JOB'
						AND	include_exclude_flag='E'
						AND     paf.assignment_type = 'E'
						AND	effective_start_date <= p_effort_end
						AND	effective_end_date >= p_effort_start
						AND	paf.job_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3080

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	person_id
						FROM	per_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='POS'
						AND	include_exclude_flag='E'
	                                        AND     paf.assignment_type = 'E'
						AND	effective_start_date <= p_effort_end
						AND	effective_end_date >= p_effort_start
						AND	paf.position_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3114

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	person_id
						FROM	per_assignments_f paf,
							psp_report_template_details_h prtd,
							per_assignment_status_types past
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='ASS'
						AND	include_exclude_flag='E'
						AND     paf.assignment_type = 'E'
						AND	paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
						AND	prtd.request_id = p_request_id
						AND	effective_start_date <= p_effort_end
						AND	effective_end_date >= p_effort_start
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3149

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	NVL(person_id,0)
						FROM	per_assignments_f paf,
							pay_payrolls_f ppf,
							per_assignment_status_types past
						WHERE	ppf.payroll_id = paf.payroll_id
						AND	ppf.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='CST'
							AND	include_exclude_flag='E'
							AND	request_id = p_request_id)
						AND     paf.assignment_type = 'E'
						AND	ppf.effective_start_date <= p_effort_end
						AND	ppf.effective_end_date >= p_effort_start
						AND	paf.effective_start_date <= p_effort_end
						AND	paf.effective_end_date >= p_effort_start
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
									, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3189

		DELETE FROM	psp_selected_persons_t
		WHERE	request_id = p_request_id
		AND	person_id IN		(SELECT	NVL(paf.person_id,0)
						FROM	per_all_assignments_f paf,
							hr_assignment_sets has,
							hr_assignment_set_amendments hasa,
							per_assignment_status_types past
						WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='AST'
							AND	include_exclude_flag='E'
							AND	request_id =p_request_id)
						AND	(	(paf.payroll_id = has.payroll_id
							AND     paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND	has.assignment_set_id = hasa.assignment_set_id)
							OR	(paf.assignment_id = hasa.assignment_id
							AND     paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND	hasa.assignment_set_id=has.assignment_set_id
							AND	include_or_exclude ='I'))
						AND	NOT EXISTS	(SELECT	assignment_id
							FROM	hr_assignment_set_amendments hasa
							WHERE	hasa.assignment_id = paf.assignment_id
							AND     paf.assignment_type = 'E'
							AND	hasa.include_or_exclude ='E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start)
						AND	paf.assignment_status_type_id =   past.assignment_status_type_id
						AND (		past.per_system_status <> 'TERM_ASSIGN'
							OR	EXISTS ( select null
								FROM	psp_pre_gen_dist_lines_history
								WHERE	distribution_date between p_effort_start and p_effort_end
								AND	assignment_id = paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1 )
							OR      EXISTS (SELECT null
								FROM   psp_distribution_lines_history pdlh
								, psp_summary_lines psl
								WHERE	pdlh.summary_line_id = psl.summary_line_id
								AND	distribution_date between p_effort_start and p_effort_end
								AND	psl.person_id = paf.person_id
								AND	psl.assignment_id =  paf.assignment_id
				 				AND     reversal_entry_flag IS NULL
								AND	rownum=1)));

Line: 3261

            select l_sql_string
            || '  OR '  || l_criteria_value1 ||  ' =  ' || ''''|| l_criteria_value2 || ''''
            into g_exec_string from psp_report_template_details_h ;

Line: 3273

            g_exec_string := 'delete from psp_selected_persons_t where person_id
            in (select person_id from per_assignments_f paf,
            pay_people_groups ppg , per_assignment_status_types past
           where  paf.people_group_id= ppg.people_group_id
                          AND	paf.assignment_type = ''' || 'E' || '''
			  and paf.effective_end_date >= :p_effort_Start and
                            paf.effective_start_date <= :p_effort_end
           and
           ppg.people_group_id in (select people_group_id from pay_people_groups
           where ' || l_sql_string
           || ' )
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> ''' || 'TERM_ASSIGN' || '''
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between :p_effort_start and :p_effort_end
				AND	assignment_id = paf.assignment_id
 				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
				, psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between :p_effort_start and :p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1))
	) and request_id  = :request_id';

Line: 3338

       g_exec_string := 'delete from psp_selected_persons_t where person_id
        in (select nvl(psl.person_id,0) from psp_summary_lines psl,
             psp_distribution_lines_history pdnh,
            psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
          gl_code_combinations gcc
           where  gcc.code_combination_id= psl.gl_code_combination_id and
           psl.business_group_id = '|| p_business_group_id || ' and
           psl.set_of_books_id = ' || p_set_of_books_id || ' and
            psl.summary_line_id = pdnh.summary_line_id(+) and
            psl.summary_line_id = ppg.summary_line_id(+) and
            psl.summary_line_id = palh.summary_line_id(+) and
            psl.status_code='||''''||'A'||''''||' and
  ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
     pdnh.distribution_date between :p_effort_start  and  :p_effort_end
 and pdnh.reversal_entry_flag is null
and psl.summary_line_id =pdnh.summary_line_id
 and pdnh.adjustment_batch_name is null
)  OR
  ( psl.source_type= '||''''||'P'||''''||' and
ppg.distribution_date between :p_effort_start  and  :p_effort_end  and
ppg.adjustment_batch_name is null and
ppg.summary_line_id =psl.summary_line_id and
ppg.reversal_entry_flag is null)
    OR (psl.source_type= '||''''||'A'||''''||' and
   palh.summary_line_id =psl.summary_line_id and
   palh.adjustment_batch_name is null and
    NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
  || ' and  palh.distribution_date between :p_effort_start  and  :p_effort_end )) and
             gcc.code_combination_id= psl.gl_code_combination_id and
            gcc.code_combination_id in (select code_combination_id from gl_code_combinations
            where ' || l_sql_string
       || ' )) and request_id  = :request_id';

Line: 3432

 CURSOR select_everyone_csr is select person_id , assignment_id, p_request_id, p_effort_start, p_effort_end from psp_selected_persons_t where
 request_id =p_request_id;

Line: 3435

CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where
request_id = p_request_id and criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='FFE';

Line: 3440

OPEN select_everyone_csr;

Line: 3442

  Fetch select_everyone_csr BULK COLLECT into
  r_ls_criteria_rec.l_person_id , r_ls_criteria_rec.l_assignment_id, r_ls_criteria_rec.l_request_id, r_ls_criteria_rec.l_start_date, r_ls_criteria_rec.l_end_date;

Line: 3445

CLOSE select_everyone_csr;

Line: 3451

 select nvl(count(1), 0)  into l_cnt from fnd_sessions where session_id = userenv('session_id');

Line: 3455

    INSERT into fnd_sessions(session_id, effective_date) values (userenv('sessionid'), p_effort_start);

Line: 3524

		DELETE FROM psp_selected_persons_t
	        WHERE  person_id = r_ls_criteria_rec.l_person_id(k)
		AND    assignment_id =  r_ls_criteria_rec.l_assignment_id(k);

Line: 3585

SELECT	lookup_code  FROM
   psp_selection_cardinality_gt  WHERE	total_count > 0 ORDER BY total_count asc;

Line: 3590

SELECT	lookup_code
FROM	psp_selection_cardinality_gt
WHERE	total_count=0;

Line: 3595

CURSOR	get_selection_cardinality_csr(p_request_id IN NUMBER) IS
SELECT	DISTINCT criteria_lookup_code
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	include_exclude_flag = 'I'
AND	criteria_lookup_type = 'PSP_SELECTION_CRITERIA';

Line: 3602

/* The below cursors would only be used only when no statis selection criteria have been chosen */
CURSOR	ppg_cursor IS
SELECT	criteria_value1,
	criteria_value2
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	include_exclude_flag='I'
AND	criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
AND	criteria_lookup_code='PPG';

Line: 3613

SELECT	criteria_value1,
	criteria_value2,
	criteria_value3
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	include_exclude_flag='I'
AND	criteria_lookup_type ='PSP_SELECTION_CRITERIA'
AND	criteria_lookup_code ='GLA';

Line: 3623

	OPEN get_selection_cardinality_csr(p_request_id);

Line: 3624

	FETCH get_selection_cardinality_csr BULK COLLECT into template_rec.array_sel_criteria;

Line: 3625

	CLOSE get_selection_cardinality_csr;

Line: 3630

			INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
			SELECT	'PTY', COUNT(DISTINCT assignment_id)
			FROM	per_people_f ppf,
				per_assignments_f paf
			WHERE	person_type_id IN	(SELECT	 TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code ='PTY'
							AND	include_exclude_flag='I'
							AND	request_id = p_request_id)
			AND	paf.person_id = ppf.person_id
			AND	paf.assignment_type = 'E'
			AND	ppf.effective_start_date <= p_effort_end
			AND	ppf.effective_end_date >= p_effort_start
			AND	paf.effective_start_date <= p_effort_end
			AND	paf.effective_end_date >= p_effort_start;

Line: 3647

			INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
			SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
			FROM	per_all_assignments_f paf
			WHERE	paf.person_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='EMP'
							AND	include_exclude_flag='I'
							AND	request_id = p_request_id)
			AND	paf.assignment_type = 'E'
			AND	paf.effective_start_date <= p_effort_end
			AND	paf.effective_end_date >= p_effort_start;

Line: 3660

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'SUP',		COUNT(DISTINCT assignment_id)
			FROM	per_all_assignments_f paf
			WHERE	supervisor_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='SUP'
							AND	request_id = p_request_id
							AND	include_exclude_flag='I')
			AND     paf.assignment_type = 'E'
			AND	effective_start_date <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3674

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	 'AWD',		COUNT(DISTINCT psl.assignment_id)
			FROM	psp_summary_lines psl,
				psp_report_template_details_h prtd ,
				per_time_periods ptp
			WHERE
				prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
			AND	prtd.criteria_lookup_code='AWD'
			AND	prtd.include_exclude_flag='I'
			AND	prtd.request_id =p_request_id
                        and     psl.award_id = TO_NUMBER(prtd.criteria_value1)
			AND	psl.business_group_id = p_business_group_id
			AND	psl.set_of_books_id = p_set_of_books_id
			AND	psl.status_code= 'A'
			AND	ptp.time_period_id = psl.time_period_id
			AND	(ptp.start_date <= p_effort_end
			AND	ptp.end_date >= p_effort_start)
			AND	(EXISTS		(SELECT	1
						FROM	psp_distribution_lines_history pdnh
						WHERE	pdnh.summary_line_id = psl.summary_line_id
						AND	 pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	 pdnh.reversal_entry_flag IS NULL
						AND	 pdnh.adjustment_batch_name IS NULL )
				OR EXISTS	(SELECT	1
						FROM	psp_pre_gen_dist_lines_history ppg
						WHERE	ppg.summary_line_id = psl.summary_line_id
						AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	ppg.adjustment_batch_name IS   NULL
						AND	ppg.reversal_entry_flag IS NULL)
				OR EXISTS	(SELECT	1
						FROM	psp_adjustment_lines_history palh
						WHERE	palh.summary_line_id = psl.summary_line_id
						AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	palh.adjustment_batch_name IS NULL
						AND	palh.reversal_entry_flag IS NULL
						AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 3712

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	 'ATY',		COUNT(DISTINCT psl.assignment_id)
			FROM	psp_summary_lines psl,
				psp_report_template_details_h prtd ,
				gms_awards_all gaa,
				per_time_periods ptp
			WHERE	psl.award_id = gaa.award_id
			AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
			AND	prtd.criteria_lookup_code='ATY'
			AND	prtd.include_exclude_flag='I'
			AND	prtd.request_id = p_request_id
			AND	gaa.type=prtd.criteria_value1
			AND	psl.business_group_id = p_business_group_id
			AND	psl.set_of_books_id = p_set_of_books_id
			AND	psl.status_code= 'A'
			AND	ptp.time_period_id = psl.time_period_id
			AND	(ptp.start_date <= p_effort_end
			AND	ptp.end_date >= p_effort_start)
			AND	(EXISTS		(SELECT	1
						FROM	psp_distribution_lines_history pdnh
						WHERE	pdnh.summary_line_id = psl.summary_line_id
						AND	 pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	 pdnh.reversal_entry_flag IS   NULL
						AND	 pdnh.adjustment_batch_name IS NULL )
				OR EXISTS	(SELECT	1
						FROM	psp_pre_gen_dist_lines_history ppg
						WHERE	ppg.summary_line_id = psl.summary_line_id
						AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	ppg.adjustment_batch_name IS NULL
						AND	ppg.reversal_entry_flag IS NULL)
				OR EXISTS	(SELECT	1
						FROM	psp_adjustment_lines_history palh
						WHERE	palh.summary_line_id = psl.summary_line_id
						AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	palh.adjustment_batch_name IS NULL
						AND	palh.reversal_entry_flag IS NULL
						AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 3751

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'PRT',		COUNT(DISTINCT psl.assignment_id)
			FROM	psp_summary_lines psl,
				psp_report_template_details_h prtd ,
				pa_projects_all ppa ,
				per_time_periods ptp
			WHERE	psl.project_id = ppa.project_id
			AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
			AND	prtd.criteria_lookup_code='PRT'
			AND	prtd.include_exclude_flag='I'
			AND	prtd.request_id =p_request_id
			AND	ppa.project_type=prtd.criteria_value1
			AND	psl.business_group_id = p_business_group_id
			AND	psl.set_of_books_id = p_set_of_books_id
			AND	psl.status_code= 'A'
			AND	ptp.time_period_id = psl.time_period_id
			AND	(ptp.start_date <= p_effort_end
			AND	ptp.end_date >= p_effort_start)
			AND	(EXISTS		(SELECT	1
						FROM	psp_distribution_lines_history pdnh
						WHERE	pdnh.summary_line_id = psl.summary_line_id
						AND	 pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	 pdnh.reversal_entry_flag IS NULL
						AND	 pdnh.adjustment_batch_name IS NULL )
				OR EXISTS	(SELECT	1
						FROM	psp_pre_gen_dist_lines_history ppg
						WHERE	ppg.summary_line_id = psl.summary_line_id
						AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	ppg.adjustment_batch_name IS NULL AND
						ppg.reversal_entry_flag IS NULL)
				OR EXISTS	(SELECT	1
						FROM	psp_adjustment_lines_history palh
						WHERE	palh.summary_line_id = psl.summary_line_id
						AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	palh.adjustment_batch_name IS NULL
						AND	palh.reversal_entry_flag IS NULL AND
						NVL(palh.original_line_flag, 'N') ='N'));

Line: 3790

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	 'PRJ',		COUNT(DISTINCT psl.assignment_id)
			FROM	psp_summary_lines psl,
				psp_report_template_details_h prtd ,
				per_time_periods ptp
			WHERE
				prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
			AND	prtd.criteria_lookup_code='PRJ'
			AND	prtd.include_exclude_flag='I'
			AND	prtd.request_id =p_request_id
                        and     psl.project_id = TO_NUMBER(prtd.criteria_value1)
			AND	psl.business_group_id = p_business_group_id
			AND	psl.set_of_books_id = p_set_of_books_id
			AND	psl.status_code= 'A'
			AND	ptp.time_period_id = psl.time_period_id
			AND	(ptp.start_date <= p_effort_end
			AND	ptp.end_date >= p_effort_start)
			AND	(EXISTS		(SELECT	1 FROM	psp_distribution_lines_history pdnh
						WHERE	pdnh.summary_line_id = psl.summary_line_id
						AND	 pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	 pdnh.reversal_entry_flag IS   NULL
						AND	 pdnh.adjustment_batch_name IS NULL )
				OR EXISTS	(SELECT	1 FROM	psp_pre_gen_dist_lines_history ppg
						WHERE	ppg.summary_line_id = psl.summary_line_id
						AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	ppg.adjustment_batch_name IS   NULL
						AND	ppg.reversal_entry_flag IS NULL)
				OR EXISTS	(SELECT	1 FROM	psp_adjustment_lines_history palh
						WHERE	palh.summary_line_id = psl.summary_line_id
						AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
						AND	palh.adjustment_batch_name IS NULL
						AND	palh.reversal_entry_flag IS NULL
						AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 3825

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'PAY',		COUNT(DISTINCT assignment_id)
			FROM	per_assignments_f paf
			WHERE	payroll_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='PAY'
						AND	include_exclude_flag='I' AND	request_id = p_request_id)
			AND     paf.assignment_type = 'E'
			AND	effective_start_date <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3838

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'LOC',		COUNT(DISTINCT assignment_id)
			FROM	per_assignments_f paf
			WHERE	location_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='LOC'
						AND	include_exclude_flag='I'
						AND	request_id = p_request_id)
			AND     paf.assignment_type = 'E'
			AND	effective_start_date <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3852

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'ORG',		COUNT(DISTINCT assignment_id)
			FROM	per_assignments_f paf
			WHERE	organization_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	 psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='ORG'
							AND	include_exclude_flag='I'
							AND	request_id = p_request_id)
			AND     paf.assignment_type = 'E'
			AND	effective_start_date  <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3866

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'JOB',		COUNT(DISTINCT assignment_id)
			FROM	per_assignments_f paf
			WHERE	job_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='JOB'
						AND	include_exclude_flag='I'
						AND	request_id=p_request_id)
			AND     paf.assignment_type = 'E'
			AND	effective_start_date <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3880

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'POS',		COUNT(DISTINCT assignment_id)
			FROM	per_assignments_f paf
			WHERE	position_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='POS'
						AND	include_exclude_flag='I'
						AND	request_id = p_request_id)
			AND     paf.assignment_type = 'E'
			AND	effective_start_date <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3894

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'ASS',		COUNT(DISTINCT assignment_id)
			FROM	per_assignments_f paf
			WHERE	assignment_status_type_id IN	(SELECT	TO_NUMBER(criteria_value1)
								FROM	psp_report_template_details_h prtd
								WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='ASS'
								AND	include_exclude_flag='I'
								AND	request_id = p_request_id)
			AND     paf.assignment_type = 'E'
			AND	effective_start_date <= p_effort_end
			AND	effective_end_date >= p_effort_start;

Line: 3908

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'CST',		COUNT(DISTINCT paf.assignment_id)
			FROM	per_assignments_f paf,
				pay_payrolls_f ppf
			WHERE	ppf.payroll_id = paf.payroll_id
			AND	ppf.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
								FROM	psp_report_template_details_h prtd
								WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='CST'
								AND	include_exclude_flag='I'
								AND	request_id = p_request_id)
			AND     paf.assignment_type = 'E'
			AND	ppf.effective_start_date <=  p_effort_end
			AND	ppf.effective_end_date >= p_effort_start
			AND	paf.effective_start_date <= p_effort_end
			AND	paf.effective_end_date >= p_effort_start;

Line: 3926

			INSERT INTO psp_selection_cardinality_gt
				(lookup_code,	total_count)
			SELECT	'AST',		COUNT(DISTINCT paf.assignment_id)
			FROM	per_all_assignments_f paf,
				hr_assignment_sets has ,
				hr_assignment_set_amendments hasa
			WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
								FROM	psp_report_template_details_h prtd
								WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='AST'
								AND	include_exclude_flag='I'
								AND	request_id =p_request_id)
			AND	(	(paf.payroll_id = has.payroll_id
					AND     paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start
					AND	has.assignment_set_id = hasa.assignment_set_id)
				OR	(paf.assignment_id = hasa.assignment_id
					AND     paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start
					AND	hasa.assignment_set_id=has.assignment_set_id
					AND	include_or_exclude ='I'))
			AND	NOT EXISTS	(SELECT	assignment_id
						FROM	hr_assignment_set_amendments hasa
						WHERE	hasa.assignment_id = paf.assignment_id
						AND	hasa.include_or_exclude ='E'
						AND     paf.assignment_type = 'E'
						AND	paf.effective_start_date <= p_effort_end
						AND	paf.effective_end_date >= p_effort_start);

Line: 4007

					g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
					SELECT	'||  '''' || 'PPG' || ''''||' , COUNT(person_id)
					FROM	per_assignments_f paf,
						pay_people_groups ppg
					WHERE	 paf.people_group_id= ppg.people_group_id
					AND	paf.assignment_type = ''' || 'E' || '''
					AND	paf.effective_end_date >= :p_effort_start
					AND	paf.effective_start_date <= :p_effort_end
					AND	ppg.people_group_id IN	(SELECT	people_group_id
									FROM	pay_people_groups
									WHERE	' || l_sql_string || ')';

Line: 4045

					g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
						SELECT	'|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
						FROM	psp_summary_lines psl,
							psp_distribution_lines_history pdnh,
							psp_pre_gen_dist_lines_history ppg,
							psp_adjustment_lines_history palh,
							gl_code_combinations gcc
						WHERE	psl.business_group_id = '|| p_business_group_id || '
						AND	psl.set_of_books_id =' || p_set_of_books_id || '
						AND	gcc.code_combination_id= psl.gl_code_combination_id
						AND	psl.summary_line_id = pdnh.summary_line_id(+)
						AND	psl.summary_line_id = ppg.summary_line_id(+)
						AND	psl.summary_line_id = palh.summary_line_id(+)
						AND	psl.status_code='||''''||'A'||''''||'
						AND	(	(psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
						AND		pdnh.distribution_date between :p_effort_start AND :p_effort_end
						AND		pdnh.summary_line_id = psl.summary_line_id
						AND		pdnh.reversal_entry_flag IS NULL
						AND		pdnh.adjustment_batch_name IS NULL)
							OR	(psl.source_type='||''''||'P'||''''||'
								AND	ppg.distribution_date BETWEEN :p_effort_start
									AND	:p_effort_end
								AND	ppg.summary_line_id = psl.summary_line_id
								AND	ppg.adjustment_batch_name IS NULL
								AND	ppg.reversal_entry_flag IS NULL)
							OR	(psl.source_type='||''''||'A'||''''||'
								AND	palh.summary_line_id = psl.summary_line_id
								AND	palh.reversal_entry_flag IS NULL
								AND	palh.adjustment_batch_name IS NULL
								AND	NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''||
									') = '||''''|| 'N' || '''' || '
								AND	palh.distribution_date BETWEEN :p_effort_start
									AND :p_effort_end ))
						AND	gcc.code_combination_id= psl.gl_code_combination_id
						AND	gcc.code_combination_id IN (SELECT	code_combination_id
										FROM	gl_code_combinations
										WHERE	' || l_sql_string || ')';

Line: 4109

SELECT	criteria_value1, criteria_value2
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type= 'PSP_SELECTION_CRITERIA' AND	criteria_lookup_code='PPG'
AND	include_exclude_flag='I';

Line: 4117

SELECT	criteria_value1 , criteria_value2, criteria_value3
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type ='PSP_SELECTION_CRITERIA' AND	criteria_lookup_code ='GLA'
AND	include_exclude_flag='I';

Line: 4132

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, paf.person_id, paf.assignment_id
		FROM	per_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	person_type_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='PTY'
						AND	request_id = p_request_id
						AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4169

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_people_f ppf,
			per_assignments_f paf,
			per_assignment_status_types past
		WHERE	ppf.person_id IN	(SELECT TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='EMP'
						AND	include_exclude_flag='I'
						AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.person_id = ppf.person_id
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4206

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_all_assignments_f paf,
			per_assignment_status_types past
		WHERE	supervisor_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='SUP'
						AND	include_exclude_flag='I'
						AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4238

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, psl.person_id, psl.assignment_id
		FROM	psp_summary_lines psl,
			psp_report_template_details_h prtd,
			per_time_periods ptp
		WHERE
		 	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
		AND	prtd.criteria_lookup_code='AWD'
		AND	prtd.include_exclude_flag='I'
		AND	prtd.request_id =p_request_id
                and psl.award_id = TO_NUMBER(prtd.criteria_value1)
		AND	psl.business_group_id = p_business_group_id
		AND	psl.set_of_books_id = p_set_of_books_id
		AND	psl.status_code= 'A'
		AND	ptp.time_period_id = psl.time_period_id
		AND	(ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
		AND	(	EXISTS	(SELECT	1
					FROM	psp_distribution_lines_history pdnh
					WHERE	pdnh.summary_line_id = psl.summary_line_id
					AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	pdnh.reversal_entry_flag IS	NULL
					AND	pdnh.adjustment_batch_name IS NULL )
			OR	EXISTS	(SELECT	1
					FROM	psp_pre_gen_dist_lines_history ppg
					WHERE	ppg.summary_line_id = psl.summary_line_id
					AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	ppg.adjustment_batch_name IS	NULL
					AND	ppg.reversal_entry_flag IS NULL)
			OR	EXISTS	(SELECT	1
					FROM	psp_adjustment_lines_history palh
					WHERE	palh.summary_line_id = psl.summary_line_id
					AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	palh.adjustment_batch_name IS NULL
					AND	palh.reversal_entry_flag IS NULL
					AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 4274

		INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
		SELECT	DISTINCT p_request_id, psl.person_id, psl.assignment_id
		FROM	psp_summary_lines psl,
			psp_report_template_details_h prtd,
			gms_awards_all gaa,
			per_time_periods ptp
		WHERE	psl.award_id = gaa.award_id
		AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
		AND	prtd.criteria_lookup_code='ATY'
		AND	prtd.include_exclude_flag='I'
		AND	prtd.request_id =p_request_id
		AND	gaa.type=prtd.criteria_value1
		AND	psl.business_group_id = p_business_group_id
		AND	psl.set_of_books_id = p_set_of_books_id
		AND	psl.status_code= 'A'
		AND	ptp.time_period_id = psl.time_period_id
		AND	(ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
		AND	(	EXISTS	(SELECT	1
					FROM	psp_distribution_lines_history pdnh
					WHERE	pdnh.summary_line_id = psl.summary_line_id
					AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	pdnh.reversal_entry_flag IS	NULL
					AND	pdnh.adjustment_batch_name IS NULL )
			OR	EXISTS	(SELECT	1
					FROM	psp_pre_gen_dist_lines_history ppg
					WHERE	ppg.summary_line_id = psl.summary_line_id
					AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	ppg.adjustment_batch_name IS	NULL
					AND	ppg.reversal_entry_flag IS NULL)
			OR	EXISTS	(SELECT	1
					FROM	psp_adjustment_lines_history palh
					WHERE	palh.summary_line_id = psl.summary_line_id
					AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	palh.adjustment_batch_name IS NULL
					AND	palh.reversal_entry_flag IS NULL
					AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 4312

		INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
		SELECT	DISTINCT p_request_id, psl.person_id, psl.assignment_id
		FROM	psp_summary_lines psl,
		psp_report_template_details_h prtd,
		pa_projects_all ppa,
		per_time_periods ptp
		WHERE	psl.project_id = ppa.project_id
		AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
		AND	prtd.criteria_lookup_code='PRT'
		AND	prtd.include_exclude_flag='I'
		AND	prtd.request_id =p_request_id
		AND	ppa.project_type=prtd.criteria_value1
		AND	psl.business_group_id = p_business_group_id
		AND	psl.set_of_books_id = p_set_of_books_id
		AND	psl.status_code= 'A'
		AND	ptp.time_period_id = psl.time_period_id
		AND	(ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
		AND	(	EXISTS	(SELECT	1
					FROM	psp_distribution_lines_history pdnh
					WHERE	pdnh.summary_line_id = psl.summary_line_id
					AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	pdnh.reversal_entry_flag IS	NULL
					AND	pdnh.adjustment_batch_name IS NULL )
			OR	EXISTS	(SELECT	1
					FROM	psp_pre_gen_dist_lines_history ppg
					WHERE	ppg.summary_line_id = psl.summary_line_id
					AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	ppg.adjustment_batch_name IS	NULL
					AND	ppg.reversal_entry_flag IS NULL)
			OR	EXISTS	(SELECT	1
					FROM	psp_adjustment_lines_history palh
					WHERE	palh.summary_line_id = psl.summary_line_id
					AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	palh.adjustment_batch_name IS NULL
					AND	palh.reversal_entry_flag IS NULL
					AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 4349

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, psl.person_id, psl.assignment_id
		FROM	psp_summary_lines psl,
			psp_report_template_details_h prtd,
			per_time_periods ptp
		WHERE
			prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
		AND	prtd.criteria_lookup_code='PRJ'
		AND	prtd.include_exclude_flag='I'
		AND	prtd.request_id =p_request_id
                and     psl.project_id = TO_NUMBER(prtd.criteria_value1)
		AND	psl.business_group_id = p_business_group_id
		AND	psl.set_of_books_id = p_set_of_books_id
		AND	psl.status_code= 'A'
		AND	ptp.time_period_id = psl.time_period_id
		AND	(ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
		AND	(	EXISTS	(SELECT	1
					FROM	psp_distribution_lines_history pdnh
					WHERE	pdnh.summary_line_id = psl.summary_line_id
					AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	pdnh.reversal_entry_flag IS	NULL
					AND	pdnh.adjustment_batch_name IS NULL )
			OR	EXISTS	(SELECT	1
					FROM	psp_pre_gen_dist_lines_history ppg
					WHERE	ppg.summary_line_id = psl.summary_line_id
					AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	ppg.adjustment_batch_name IS	NULL
					AND	ppg.reversal_entry_flag IS NULL)
			OR	EXISTS	(SELECT	1
					FROM	psp_adjustment_lines_history palh
					WHERE	palh.summary_line_id = psl.summary_line_id
					AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
					AND	palh.adjustment_batch_name IS NULL
					AND	palh.reversal_entry_flag IS NULL
					AND	NVL(palh.original_line_flag, 'N') ='N'));

Line: 4386

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_assignments_f paf,
			per_assignment_status_types past
		WHERE	payroll_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='PAY'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4419

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_assignments_f paf,
			per_assignment_status_types past
		WHERE	location_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='LOC'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4451

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_assignments_f paf,
			per_assignment_status_types past
		WHERE	organization_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='ORG'
						AND	request_id = p_request_id
						AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4483

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, person_id, assignment_id
		FROM	per_assignments_f paf,
			pay_payrolls_f ppf,
			per_assignment_status_types past
		WHERE	ppf.payroll_id = paf.payroll_id
		AND	ppf.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='CST'
							AND	include_exclude_flag='I'
							AND request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	ppf.effective_start_date <= p_effort_end
		AND	ppf.effective_end_date >= p_effort_start
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4520

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, person_id, paf.assignment_id
		FROM	per_all_assignments_f paf,
			hr_assignment_sets has,
			hr_assignment_set_amendments hasa,
			per_assignment_status_types past
		WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='AST'
							AND	include_exclude_flag='I'
							AND request_id =p_request_id)
		AND	(	(paf.payroll_id = has.payroll_id
				AND	paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	has.assignment_set_id = hasa.assignment_set_id)
			OR	(paf.assignment_id = hasa.assignment_id
				AND	paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	hasa.assignment_set_id=has.assignment_set_id AND include_or_exclude ='I'))
		AND	NOT EXISTS	(SELECT	assignment_id
					FROM	hr_assignment_set_amendments hasa
					WHERE	hasa.assignment_id = paf.assignment_id AND hasa.include_or_exclude ='E'
					AND	paf.assignment_type = 'E'
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start)
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4568

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_assignments_f paf,
			per_assignment_status_types past
		WHERE	job_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='JOB'
					AND	include_exclude_flag='I'
					AND	request_id = p_request_id)
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4600

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_assignments_f paf,
			per_assignment_status_types past
		WHERE	position_id IN	(SELECT	TO_NUMBER(criteria_value1)
					FROM	psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='POS'
					AND	request_id = p_request_id
					AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4632

		INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id,	paf.person_id,	paf.assignment_id
		FROM	per_assignments_f paf,
			per_assignment_status_types past
		WHERE	paf.assignment_status_type_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='ASS'
							AND	request_id = p_request_id
							AND	include_exclude_flag='I')
		AND	paf.assignment_type = 'E'
		AND	paf.effective_start_date <= p_effort_end
		AND	paf.effective_end_date >= p_effort_start
		AND	paf.assignment_status_type_id =   past.assignment_status_type_id
		AND (		past.per_system_status <> 'TERM_ASSIGN'
			OR	EXISTS ( select null
				FROM	psp_pre_gen_dist_lines_history
				WHERE	distribution_date between p_effort_start and p_effort_end
				AND	assignment_id = paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1 )
			OR      EXISTS (SELECT null
				FROM   psp_distribution_lines_history pdlh
			        , psp_summary_lines psl
				WHERE	pdlh.summary_line_id = psl.summary_line_id
				AND	distribution_date between p_effort_start and p_effort_end
				AND	psl.person_id = paf.person_id
				AND	psl.assignment_id =  paf.assignment_id
				AND     reversal_entry_flag IS NULL
				AND	rownum=1));

Line: 4681

				g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
					SELECT	:request_id , person_id, assignment_id
					FROM	per_assignments_f paf,
						pay_people_groups ppg,
						per_assignment_status_types past
					WHERE	paf.people_group_id= ppg.people_group_id
					AND	paf.assignment_type = ''' || 'E' || '''
					AND	paf.effective_end_date >= :p_effort_start
					AND	paf.effective_start_date <= :p_effort_end
					AND	ppg.people_group_id IN	(SELECT	people_group_id
									FROM	pay_people_groups
									WHERE	' || l_sql_string || ')
					AND	paf.assignment_status_type_id =   past.assignment_status_type_id
					AND (		past.per_system_status <> ''' ||'TERM_ASSIGN' || '''
						OR	EXISTS ( select null
							FROM	psp_pre_gen_dist_lines_history
							WHERE	distribution_date between :p_effort_start and :p_effort_end
							AND	assignment_id = paf.assignment_id
							AND     reversal_entry_flag IS NULL
							AND	rownum=1 )
						OR      EXISTS (SELECT null
							FROM   psp_distribution_lines_history pdlh
							, psp_summary_lines psl
							WHERE	pdlh.summary_line_id = psl.summary_line_id
							AND	distribution_date between :p_effort_start and :p_effort_end
							AND	psl.person_id = paf.person_id
							AND	psl.assignment_id =  paf.assignment_id
							AND     reversal_entry_flag IS NULL
							AND	rownum=1))';

Line: 4734

				g_exec_string := 'INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
				SELECT	:p_request_id, psl.person_id, psl.assignment_id
				FROM	psp_summary_lines psl,
					psp_distribution_lines_history pdnh,
					psp_adjustment_lines_history palh,
					psp_pre_gen_dist_lines_history ppg,
					gl_code_combinations gcc
				WHERE	psl.business_group_id = '|| p_business_group_id || '
				AND	psl.set_of_books_id = ' || p_set_of_books_id ||'
				AND	psl.summary_line_id = pdnh.summary_line_id(+)
				AND	psl.summary_line_id = ppg.summary_line_id(+)
				AND	psl.summary_line_id = palh.summary_line_id(+)
				AND	psl.status_code= '||''''||'A'||''''||'
				AND	(	(psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
						AND	pdnh.distribution_date between :p_effort_start AND :p_effort_end
						AND	pdnh.reversal_entry_flag IS NULL
						AND	pdnh.summary_line_id = psl.summary_line_id
						AND	pdnh.adjustment_batch_name IS NULL)
					OR	(psl.source_type='||''''||'P'||''''||'
						AND	ppg.distribution_date between :p_effort_start AND :p_effort_end
						AND	ppg.summary_line_id = psl.summary_line_id
						AND	ppg.adjustment_batch_name IS NULL
						AND	ppg.reversal_entry_flag IS NULL)
					OR	(psl.source_type='||''''||'A'||''''||'
						AND	palh.adjustment_batch_name IS NULL
						AND	palh.summary_line_id =psl.summary_line_id
						AND	NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = ' ||
							''''|| 'N' || '''' ||'
						AND palh.distribution_date between :p_effort_start AND :p_effort_end))
				AND	gcc.code_combination_id= psl.gl_code_combination_id
				AND	gcc.code_combination_id IN (SELECT	code_combination_id
									FROM	gl_code_combinations
									WHERE	' || l_sql_string || ')';

Line: 4772

		INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
		SELECT	DISTINCT p_request_id, person_id, assignment_id
		FROM	per_assignments_f
		WHERE	assignment_type = 'E'
		AND	business_group_id = p_business_group_id
		AND	effective_start_date <= p_effort_end
		AND	effective_end_date >= p_effort_start;

Line: 4789

CURSOR	get_all_selection_criteria(p_request_id IN NUMBER) IS
SELECT	distinct criteria_lookup_code,
	include_exclude_flag
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type='PSP_SELECTION_CRITERIA'
ORDER BY include_exclude_flag;

Line: 4798

SELECT	criteria_value1, criteria_value2
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
AND	criteria_lookup_code='PPG'
AND	include_exclude_flag='I';

Line: 4806

SELECT	criteria_value1 , criteria_value2, criteria_value3
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type ='PSP_SELECTION_CRITERIA'
AND	criteria_lookup_code ='GLA'
AND	include_exclude_flag='I';

Line: 4822

	OPEN get_all_selection_criteria(p_request_id);

Line: 4823

	FETCH get_all_selection_criteria BULK COLLECT INTO template_sel_criteria.array_sel_criteria,
		template_sel_criteria.array_inc_exc_flag;

Line: 4825

	CLOSE get_all_selection_criteria;

Line: 4832

					DELETE FROM psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
                                        /* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	person_id NOT IN	(SELECT	NVL( person_id, 0)
                                        AND    NOT EXISTS (     SELECT 1
							FROM	per_people_f ppf,
								psp_report_template_details_h prtd,
								per_assignments_f paf
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='PTY'
							AND	paf.person_id = ppf.person_id
							AND	paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							and
                                                        ppf.effective_start_date <= p_effort_end and
                                                        ppf.effective_end_date >= p_effort_start
							AND	include_exclude_flag='I'
							AND	ppf.person_type_id  = TO_NUMBER(prtd.criteria_value1)
							AND	prtd.request_id = p_request_id
							AND     ppf.person_id = pspt.person_id );

Line: 4854

					DELETE FROM psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
                                        /* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	person_id  NOT IN	(SELECT	NVL(person_id,0)
                                        AND    NOT EXISTS (     SELECT 1
							FROM	per_all_people_f ppf, per_assignments_f paf
							WHERE	ppf.person_id IN	(SELECT	TO_NUMBER(criteria_value1)
								FROM	psp_report_template_details_h prtd
								WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='EMP'
								AND	include_exclude_flag='I'
								AND	prtd.request_id = p_request_id)
								AND	paf.person_id = ppf.person_id
								AND	paf.assignment_type = 'E'
								AND	paf.effective_start_date <= p_effort_end
								AND	paf.effective_end_date >= p_effort_start
							AND	ppf.effective_start_date <= p_effort_end
							AND	ppf.effective_end_date >= p_effort_start
							AND     ppf.person_id = pspt.person_id );

Line: 4874

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
                                        /* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
                                        AND    NOT EXISTS (             SELECT 1
									FROM	per_all_assignments_f paf,
										psp_report_template_details_h prtd
									WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
									AND	criteria_lookup_code='SUP'
									AND	include_exclude_flag='I'
									AND	paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
									AND	prtd.request_id = p_request_id
									AND     paf.assignment_type = 'E'
									AND	paf.effective_start_date <= p_effort_end
									AND	effective_end_date >= p_effort_start
									AND     paf.assignment_id = pspt.assignment_id );

Line: 4891

					DELETE FROM	psp_selected_persons_t
					WHERE	request_id = p_request_id
					AND	assignment_id NOT IN (SELECT	psl.assignment_id
						FROM	psp_summary_lines psl,
							psp_report_template_details_h prtd ,
							per_time_periods ptp
						WHERE
							prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	prtd.criteria_lookup_code='AWD'
						AND	prtd.include_exclude_flag='I'
						AND	prtd.request_id =p_request_id
                                                and     psl.award_id = TO_NUMBER(prtd.criteria_value1)
						AND	psl.business_group_id = p_business_group_id
						AND	psl.set_of_books_id = p_set_of_books_id
						AND	psl.status_code= 'A'
						AND	ptp.time_period_id = psl.time_period_id
						AND	(ptp.start_date <= p_effort_end
						AND	ptp.end_date >= p_effort_start)
						AND	(	EXISTS	(SELECT	1
									FROM	psp_distribution_lines_history pdnh
									WHERE	pdnh.summary_line_id = psl.summary_line_id
									AND	pdnh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	pdnh.reversal_entry_flag IS NULL
									AND	pdnh.adjustment_batch_name IS NULL)
							OR	EXISTS	(SELECT	1
									FROM	psp_pre_gen_dist_lines_history ppg
									WHERE	ppg.summary_line_id = psl.summary_line_id
									AND	ppg.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	ppg.adjustment_batch_name IS NULL
									AND	ppg.reversal_entry_flag IS NULL)
							OR	EXISTS	(SELECT	1
									FROM	psp_adjustment_lines_history palh
									WHERE	palh.summary_line_id = psl.summary_line_id
									AND	palh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	palh.adjustment_batch_name IS NULL
									AND	palh.reversal_entry_flag IS NULL
									AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 4932

					DELETE FROM	psp_selected_persons_t
					WHERE	request_id = p_request_id
					AND	assignment_id NOT IN	(SELECT	NVL(psl.assignment_id,0)
							FROM	psp_summary_lines psl,
								psp_report_template_details_h prtd,
								gms_awards_all gaa,
								per_time_periods ptp
							WHERE	psl.award_id = gaa.award_id
							AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	prtd.criteria_lookup_code='ATY'
							AND	prtd.include_exclude_flag='I'
							AND	prtd.request_id =p_request_id
							AND	gaa.type=prtd.criteria_value1
							AND	psl.business_group_id = p_business_group_id
							AND	psl.set_of_books_id = p_set_of_books_id
							AND	psl.status_code= 'A'
							AND	ptp.time_period_id = psl.time_period_id
							AND	(ptp.start_date <= p_effort_end
							AND	ptp.end_date >= p_effort_start)
							AND	(	EXISTS	(SELECT	1
									FROM	psp_distribution_lines_history pdnh
									WHERE	pdnh.summary_line_id = psl.summary_line_id
									AND	pdnh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	pdnh.reversal_entry_flag IS NULL
									AND	pdnh.adjustment_batch_name IS NULL)
								OR	EXISTS	(SELECT	1
									FROM	psp_pre_gen_dist_lines_history ppg
									WHERE	ppg.summary_line_id = psl.summary_line_id
									AND	ppg.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	ppg.adjustment_batch_name IS NULL
									AND	ppg.reversal_entry_flag IS NULL)
								OR	EXISTS	(SELECT	1
									FROM	psp_adjustment_lines_history palh
									WHERE	palh.summary_line_id = psl.summary_line_id
									AND	palh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	palh.adjustment_batch_name IS NULL
									AND	palh.reversal_entry_flag IS NULL
									AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 4974

					DELETE FROM	psp_selected_persons_t
					WHERE	request_id = p_request_id
					AND	assignment_id NOT IN	(SELECT	NVL(psl.assignment_id,0)
							FROM	psp_summary_lines psl,
									psp_report_template_details_h prtd,
									pa_projects_all ppa,
									per_time_periods ptp
							WHERE	psl.project_id = ppa.project_id
							AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	prtd.criteria_lookup_code='PRT'
							AND	prtd.include_exclude_flag='I'
							AND	prtd.request_id =p_request_id
							AND	ppa.project_type=prtd.criteria_value1
							AND	psl.business_group_id = p_business_group_id
							AND	psl.set_of_books_id = p_set_of_books_id
							AND	psl.status_code= 'A'
							AND	ptp.time_period_id = psl.time_period_id
							AND	(ptp.start_date <= p_effort_end
								AND	ptp.end_date >= p_effort_start)
							AND	(	EXISTS (SELECT	1
									FROM	psp_distribution_lines_history pdnh
									WHERE	pdnh.summary_line_id = psl.summary_line_id
									AND	pdnh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	pdnh.reversal_entry_flag IS NULL
									AND	pdnh.adjustment_batch_name IS NULL)
								OR	EXISTS	(SELECT	1
									FROM	psp_pre_gen_dist_lines_history ppg
									WHERE	ppg.summary_line_id = psl.summary_line_id
									AND	ppg.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	ppg.adjustment_batch_name IS NULL
									AND	ppg.reversal_entry_flag IS NULL)
								OR	EXISTS	(SELECT	1
									FROM	psp_adjustment_lines_history palh
									WHERE	palh.summary_line_id = psl.summary_line_id
									AND	palh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	palh.adjustment_batch_name IS NULL
									AND	palh.reversal_entry_flag IS NULL
									AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 5016

					DELETE FROM	psp_selected_persons_t
					WHERE	request_id = p_request_id
					AND	assignment_id NOT IN	(SELECT	NVL(psl.assignment_id ,0)
							FROM	psp_summary_lines psl,
							psp_report_template_details_h prtd ,
							per_time_periods ptp
							WHERE
								prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	prtd.criteria_lookup_code='PRJ'
							AND	prtd.include_exclude_flag='I'
							AND	prtd.request_id =p_request_id
                                                        and     psl.project_id = TO_NUMBER(prtd.criteria_value1)
							AND	psl.business_group_id = p_business_group_id
							AND	psl.set_of_books_id = p_set_of_books_id
							AND	psl.status_code= 'A'
							AND	ptp.time_period_id = psl.time_period_id
							AND	(ptp.start_date <= p_effort_end
								AND	ptp.end_date >= p_effort_start)
							AND	(	EXISTS	(SELECT	1
									FROM	psp_distribution_lines_history pdnh
									WHERE	pdnh.summary_line_id = psl.summary_line_id
									AND	pdnh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	pdnh.reversal_entry_flag IS NULL
									AND	pdnh.adjustment_batch_name IS NULL )
								OR	EXISTS	(SELECT	1
									FROM	psp_pre_gen_dist_lines_history ppg
									WHERE	ppg.summary_line_id = psl.summary_line_id
									AND	ppg.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	ppg.adjustment_batch_name IS NULL
									AND	ppg.reversal_entry_flag IS NULL)
								OR	EXISTS	(SELECT	1
									FROM	psp_adjustment_lines_history palh
									WHERE	palh.summary_line_id = psl.summary_line_id
									AND	palh.distribution_date BETWEEN p_effort_start
										AND p_effort_end
									AND	palh.adjustment_batch_name IS NULL
									AND	palh.reversal_entry_flag IS NULL
									AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 5057

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
					/* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
                                        AND    NOT EXISTS (             SELECT 1
									FROM	per_assignments_f paf,
										psp_report_template_details_h prtd
									WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
									AND	criteria_lookup_code='PAY'
									AND	include_exclude_flag='I'
                                                                        AND     paf.assignment_type = 'E'
									 and
                                                                         effective_start_date <= p_effort_end and
                                                                         effective_end_date >= p_effort_start
									AND	paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
									AND	prtd.request_id = p_request_id
									AND     paf.assignment_id = pspt.assignment_id );

Line: 5075

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
					/* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
                                        AND    NOT EXISTS (             SELECT 1
									FROM	per_assignments_f paf ,
										psp_report_template_details_h prtd
									WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
									AND	criteria_lookup_code='LOC'
                                                                        AND     paf.assignment_type = 'E'
									 and
                                                                         effective_start_date <= p_effort_end and
                                                                         effective_end_date >= p_effort_start
									AND	include_exclude_flag='I'
									AND	paf.location_id = TO_NUMBER(prtd.criteria_value1)
									AND	prtd.request_id = p_request_id
									AND     paf.assignment_id = pspt.assignment_id);

Line: 5094

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
				--	AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
					AND    NOT EXISTS (             SELECT 1
									FROM	per_assignments_f paf ,
										psp_report_template_details_h prtd
									WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
									AND	criteria_lookup_code='ORG'
									AND	include_exclude_flag='I'
                                                                        AND     paf.assignment_type = 'E'
									and effective_start_date <= p_effort_end and
                                                                         effective_end_date >= p_effort_start
									AND	paf.organization_id = TO_NUMBER(prtd.criteria_value1)
									AND	prtd.request_id = p_request_id
									AND     paf.assignment_id = pspt.assignment_id );

Line: 5111

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
					/* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
					AND    NOT EXISTS (     SELECT 1
							FROM	per_assignments_f paf,
								pay_payrolls_f ppf
							WHERE	ppf.payroll_id = paf.payroll_id
							AND	ppf.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
								FROM	psp_report_template_details_h prtd
								WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='CST'
								AND	include_exclude_flag='I'
								AND	request_id = p_request_id)
							AND     paf.assignment_type = 'E'
							AND	ppf.effective_start_date <= p_effort_end
							AND	ppf.effective_end_date >= p_effort_start
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND     paf.assignment_id = pspt.assignment_id );

Line: 5132

					DELETE FROM	psp_selected_persons_t
					WHERE	request_id = p_request_id
					AND	assignment_id NOT IN	(SELECT	NVL(paf.assignment_id,0)
					FROM	per_all_assignments_f paf,
						hr_assignment_sets has,
						hr_assignment_set_amendments hasa
					WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
						FROM	psp_report_template_details_h prtd
						WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
						AND	criteria_lookup_code='AST'
						AND	include_exclude_flag='I'
						AND	request_id =p_request_id)
					AND	(	(paf.payroll_id = has.payroll_id
							AND     paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND	has.assignment_set_id = hasa.assignment_set_id)
						OR	(paf.assignment_id = hasa.assignment_id
							AND     paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND	hasa.assignment_set_id=has.assignment_set_id
							AND	include_or_exclude ='I'))
					AND	NOT EXISTS	(SELECT	assignment_id
						FROM	hr_assignment_set_amendments hasa
						WHERE	hasa.assignment_id = paf.assignment_id
						AND     paf.assignment_type = 'E'
						AND	hasa.include_or_exclude ='E'
						AND	paf.effective_start_date <= p_effort_end
						AND	paf.effective_end_date >= p_effort_start));

Line: 5163

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
					/* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
					AND    NOT EXISTS (     SELECT 1
							FROM	per_assignments_f paf ,
								psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='JOB'
							AND	include_exclude_flag='I'
                                                        AND     paf.assignment_type = 'E'
									 and
                                                                         effective_start_date <= p_effort_end and
                                                                         effective_end_date >= p_effort_start
							AND	paf.job_id = TO_NUMBER(prtd.criteria_value1)
							AND	prtd.request_id = p_request_id
							AND     paf.assignment_id = pspt.assignment_id);

Line: 5181

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
					/* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
					AND    NOT EXISTS (     SELECT 1
							FROM	per_assignments_f paf ,
								psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='POS'
                                                        AND     paf.assignment_type = 'E'
									 and
                                                                         effective_start_date <= p_effort_end and
                                                                         effective_end_date >= p_effort_start
							AND	include_exclude_flag='I'
							AND	paf.position_id = TO_NUMBER(prtd.criteria_value1)
							AND	prtd.request_id = p_request_id
							AND     paf.assignment_id = pspt.assignment_id);

Line: 5199

					DELETE FROM	psp_selected_persons_t pspt
					WHERE	request_id = p_request_id
					/* Bug 5087294 : Performance fix replacing not in with not exists */
					-- AND	assignment_id NOT IN	(SELECT	NVL(assignment_id,0)
					AND    NOT EXISTS (     SELECT 1
							FROM	per_assignments_f paf ,
								psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='ASS'
							AND	include_exclude_flag='I'
							AND	paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
							AND	prtd.request_id = p_request_id
							AND     paf.assignment_type = 'E'
							AND	effective_start_date <= p_effort_end
							AND	effective_end_date >= p_effort_start
							AND     paf.assignment_id = pspt.assignment_id);

Line: 5232

							g_exec_string := 'DELETE FROM	psp_selected_persons_t sel
								WHERE	request_id = :request_id
								AND	NOT EXISTS (SELECT	1
								FROM	per_assignments_f paf, pay_people_groups ppg
								WHERE	paf.people_group_id= ppg.people_group_id
								AND	paf.assignment_type = ''' || 'E' || '''
								AND	paf.effective_end_date >= :p_effort_Start
								AND	paf.effective_start_date <= :p_effort_end
								AND	(' || l_sql_string || ')
								AND	paf.assignment_id = sel.assignment_id )';

Line: 5267

							g_exec_string := 'DELETE FROM	psp_selected_persons_t
							WHERE	assignment_id NOT IN (SELECT	NVL(psl.assignment_id,0)
							FROM	psp_summary_lines psl, psp_distribution_lines_history pdnh,
								psp_adjustment_lines_history palh,
								psp_pre_gen_dist_lines_history ppg, gl_code_combinations gcc
							WHERE	gcc.code_combination_id= psl.gl_code_combination_id
							AND	psl.business_group_id = '|| p_business_group_id || '
							AND	psl.set_of_books_id = ' || p_set_of_books_id || '
							AND	psl.summary_line_id = pdnh.summary_line_id(+)
							AND	psl.summary_line_id = ppg.summary_line_id(+)
							AND	psl.summary_line_id = palh.summary_line_id(+)
							AND	psl.status_code='||''''||'A'||''''||'
							AND	(	(psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
									AND	pdnh.distribution_date BETWEEN
										:p_effort_start AND :p_effort_end
									AND	pdnh.reversal_entry_flag IS NULL
									AND	psl.summary_line_id = pdnh.summary_line_id
									AND	pdnh.adjustment_batch_name IS NULL)
								OR	(psl.source_type='||''''||'P'||''''||'
									AND	ppg.distribution_date BETWEEN :p_effort_start AND :p_effort_end
									AND	ppg.adjustment_batch_name IS NULL
									AND	ppg.summary_line_id =psl.summary_line_id
									AND	ppg.reversal_entry_flag IS NULL)
								OR	(psl.source_type='||''''||'A'||''''||'
									AND	palh.summary_line_id =psl.summary_line_id
									AND	palh.adjustment_batch_name IS NULL
									AND	NVL(palh.original_line_flag, ' || ''''||
										'N' || ''''|| ') = '||''''|| 'N' || '''' || '
									AND	palh.distribution_date BETWEEN
										:p_effort_start AND :p_effort_end))
							AND	gcc.code_combination_id= psl.gl_code_combination_id
							AND	gcc.code_combination_id IN (SELECT	code_combination_id
								FROM	gl_code_combinations
								WHERE	' || l_sql_string || ' ))
								AND	request_id = :request_id';

Line: 5320

SELECT	criteria_value1, criteria_value2
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
AND	criteria_lookup_code='PPG'
AND	include_exclude_flag='E';

Line: 5328

SELECT	criteria_value1, criteria_value2, criteria_value3
FROM	psp_report_template_details_h
WHERE	request_id = p_request_id
AND	criteria_lookup_type ='PSP_SELECTION_CRITERIA'
AND	criteria_lookup_code ='GLA'
AND	include_exclude_flag='E';

Line: 5347

				DELETE FROM psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	person_id IN	(SELECT	ppf.person_id
							FROM	per_people_f ppf,
								psp_report_template_details_h prtd,
								per_assignments_f paf
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='PTY'
            AND	paf.person_id = ppf.person_id
            AND	paf.assignment_type = 'E'
            AND	paf.effective_start_date <= p_effort_end
            AND	paf.effective_end_date >= p_effort_start
	    and
            ppf.effective_start_date <= p_effort_end and
            ppf.effective_end_date >= p_effort_start
							AND	include_exclude_flag='E'
							AND	ppf.person_type_id  = TO_NUMBER(prtd.criteria_value1)
							AND	prtd.request_id = p_request_id);

Line: 5366

				DELETE FROM psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	person_id IN	(SELECT	DISTINCT ppf.person_id
						FROM	per_all_people_f ppf, per_assignments_f paf
						WHERE	ppf.person_id IN	(SELECT	TO_NUMBER(criteria_value1)
								FROM	psp_report_template_details_h prtd
								WHERE	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='EMP'
								AND	include_exclude_flag='E'
								AND	prtd.request_id = p_request_id)
				AND	paf.person_id = ppf.person_id
				AND	paf.assignment_type = 'E'
				AND	paf.effective_start_date <= p_effort_end
				AND	paf.effective_end_date >= p_effort_start
				AND	ppf.effective_start_date <= p_effort_end
				AND	ppf.effective_end_date >= p_effort_start);

Line: 5383

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	NVL(assignment_id,0)
								FROM	per_all_assignments_f paf,
									psp_report_template_details_h prtd
								WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
								AND	criteria_lookup_code='SUP'
								AND	include_exclude_flag='E'
								AND	paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
								AND	prtd.request_id = p_request_id
								AND     paf.assignment_type = 'E'
								AND	paf.effective_start_date <= p_effort_end
								AND	paf.effective_end_date >= p_effort_start);

Line: 5397

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	psl.assignment_id
					FROM	psp_summary_lines psl,
						psp_report_template_details_h prtd,
						per_time_periods ptp
					WHERE
						prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	prtd.criteria_lookup_code='AWD'
					AND	prtd.include_exclude_flag='E'
					AND	prtd.request_id =p_request_id
                                        and     psl.award_id = TO_NUMBER(prtd.criteria_value1)
					AND	psl.business_group_id = p_business_group_id
					AND	psl.set_of_books_id = p_set_of_books_id
					AND	psl.status_code= 'A'
					AND	ptp.time_period_id = psl.time_period_id
					AND	(ptp.start_date <= p_effort_end
					AND	ptp.end_date >= p_effort_start)
					AND	(	EXISTS	(SELECT	1
							FROM	psp_distribution_lines_history pdnh
							WHERE	pdnh.summary_line_id = psl.summary_line_id
							AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	pdnh.reversal_entry_flag IS NULL
							AND	pdnh.adjustment_batch_name IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_pre_gen_dist_lines_history ppg
							WHERE	ppg.summary_line_id = psl.summary_line_id
							AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	ppg.adjustment_batch_name IS NULL
							AND	ppg.reversal_entry_flag IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_adjustment_lines_history palh
							WHERE	palh.summary_line_id = psl.summary_line_id
							AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	palh.adjustment_batch_name IS NULL
							AND	palh.reversal_entry_flag IS NULL
							AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 5435

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	NVL(psl.assignment_id,0)
					FROM	psp_summary_lines psl,
						psp_report_template_details_h prtd,
						gms_awards_all gaa,
						per_time_periods ptp
					WHERE	psl.award_id = gaa.award_id
					AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	prtd.criteria_lookup_code='ATY'
					AND	prtd.include_exclude_flag='E'
					AND	prtd.request_id =p_request_id
					AND	gaa.type=prtd.criteria_value1
					AND	psl.business_group_id = p_business_group_id
					AND	psl.set_of_books_id = p_set_of_books_id
					AND	psl.status_code= 'A'
					AND	ptp.time_period_id = psl.time_period_id
					AND	(ptp.start_date <= p_effort_end
					AND	ptp.end_date >= p_effort_start)
					AND	(	EXISTS	(SELECT	1
							FROM	psp_distribution_lines_history pdnh
							WHERE	pdnh.summary_line_id = psl.summary_line_id
							AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	pdnh.reversal_entry_flag IS NULL
							AND	pdnh.adjustment_batch_name IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_pre_gen_dist_lines_history ppg
							WHERE	ppg.summary_line_id = psl.summary_line_id
							AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	ppg.adjustment_batch_name IS NULL
							AND	ppg.reversal_entry_flag IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_adjustment_lines_history palh
							WHERE	palh.summary_line_id = psl.summary_line_id
							AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	palh.adjustment_batch_name IS NULL
							AND	palh.reversal_entry_flag IS NULL
							AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 5474

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	NVL(psl.assignment_id,0)
					FROM	psp_summary_lines psl,
						psp_report_template_details_h prtd,
						pa_projects_all ppa,
						per_time_periods ptp
					WHERE	psl.project_id = ppa.project_id
					AND	prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	prtd.criteria_lookup_code='PRT'
					AND	prtd.include_exclude_flag='E'
					AND	prtd.request_id =p_request_id
					AND	ppa.project_type=prtd.criteria_value1
					AND	psl.business_group_id = p_business_group_id
					AND	psl.set_of_books_id = p_set_of_books_id
					AND	psl.status_code= 'A'
					AND	ptp.time_period_id = psl.time_period_id
					AND	(ptp.start_date <= p_effort_end
					AND	ptp.end_date >= p_effort_start)
					AND	(	EXISTS	(SELECT	1
							FROM	psp_distribution_lines_history pdnh
							WHERE	pdnh.summary_line_id = psl.summary_line_id
							AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	pdnh.reversal_entry_flag IS NULL
							AND	pdnh.adjustment_batch_name IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_pre_gen_dist_lines_history ppg
							WHERE	ppg.summary_line_id = psl.summary_line_id
							AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	ppg.adjustment_batch_name IS NULL
							AND	ppg.reversal_entry_flag IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_adjustment_lines_history palh
							WHERE	palh.summary_line_id = psl.summary_line_id
							AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	palh.adjustment_batch_name IS NULL
							AND	palh.reversal_entry_flag IS NULL
							AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 5513

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	NVL(psl.assignment_id,0)
					FROM	psp_summary_lines psl,
						psp_report_template_details_h prtd,
						per_time_periods ptp
					WHERE
						prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	prtd.criteria_lookup_code='PRJ'
					AND	prtd.include_exclude_flag='E'
					AND	prtd.request_id =p_request_id
                                        and     psl.project_id = TO_NUMBER(prtd.criteria_value1)
					AND	psl.business_group_id = p_business_group_id
					AND	psl.set_of_books_id = p_set_of_books_id
					AND	psl.status_code= 'A'
					AND	ptp.time_period_id = psl.time_period_id
					AND	(ptp.start_date <= p_effort_end
					AND	ptp.end_date >= p_effort_start)
					AND	(	EXISTS	(SELECT	1
							FROM	psp_distribution_lines_history pdnh
							WHERE	pdnh.summary_line_id = psl.summary_line_id
							AND	pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	pdnh.reversal_entry_flag IS NULL
							AND	pdnh.adjustment_batch_name IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_pre_gen_dist_lines_history ppg
							WHERE	ppg.summary_line_id = psl.summary_line_id
							AND	ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	ppg.adjustment_batch_name IS NULL
							AND	ppg.reversal_entry_flag IS NULL)
						OR	EXISTS	(SELECT	1
							FROM	psp_adjustment_lines_history palh
							WHERE	palh.summary_line_id = psl.summary_line_id
							AND	palh.distribution_date BETWEEN p_effort_start AND p_effort_end
							AND	palh.adjustment_batch_name IS NULL
							AND	palh.reversal_entry_flag IS NULL
							AND	NVL(palh.original_line_flag, 'N') ='N')));

Line: 5551

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	assignment_id
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='PAY'
					AND	include_exclude_flag='E'
                                        AND     paf.assignment_type = 'E'
					and effective_start_date <= p_effort_end and
                                         effective_end_date >= p_effort_start
					AND	paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id);

Line: 5565

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	assignment_id
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='LOC'
					AND	include_exclude_flag='E'
                                        AND     paf.assignment_type = 'E'
					and effective_start_date <= p_effort_end and
                                         effective_end_date >= p_effort_start
					AND	paf.location_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id);

Line: 5579

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	assignment_id
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='ORG'
					AND	include_exclude_flag='E'
                                        AND     paf.assignment_type = 'E'
					and effective_start_date <= p_effort_end and
                                        effective_end_date >= p_effort_start
					AND	paf.organization_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id);

Line: 5593

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	assignment_id
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='JOB'
					AND	include_exclude_flag='E'
                                        AND     paf.assignment_type = 'E'
					and effective_start_date <= p_effort_end and
                                        effective_end_date >= p_effort_start
					AND	paf.job_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id);

Line: 5607

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	assignment_id
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='POS'
					AND	include_exclude_flag='E'
                                        AND     paf.assignment_type = 'E'
					and effective_start_date <= p_effort_end and
                                        effective_end_date >= p_effort_start
					AND	paf.position_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id);

Line: 5621

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	assignment_id
					FROM	per_assignments_f paf,
						psp_report_template_details_h prtd
					WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
					AND	criteria_lookup_code='ASS'
					AND	include_exclude_flag='E'
					AND     paf.assignment_type = 'E'
					AND	paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
					AND	prtd.request_id = p_request_id
					AND	effective_start_date <= p_effort_end
					AND	effective_end_date >= p_effort_start);

Line: 5635

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	NVL(assignment_id,0)
					FROM	per_assignments_f paf,
						pay_payrolls_f ppf
					WHERE	ppf.payroll_id = paf.payroll_id
					AND	ppf.consolidation_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='CST'
							AND	include_exclude_flag='E'
							AND	request_id = p_request_id)
					AND     paf.assignment_type = 'E'
					AND	ppf.effective_start_date <= p_effort_end
					AND	ppf.effective_end_date >= p_effort_start
					AND	paf.effective_start_date <= p_effort_end
					AND	paf.effective_end_date >= p_effort_start);

Line: 5653

				DELETE FROM	psp_selected_persons_t
				WHERE	request_id = p_request_id
				AND	assignment_id IN	(SELECT	NVL(paf.assignment_id,0)
					FROM	per_all_assignments_f paf, hr_assignment_sets has,
						hr_assignment_set_amendments hasa
					WHERE	has.assignment_set_id IN	(SELECT	TO_NUMBER(criteria_value1)
							FROM	psp_report_template_details_h prtd
							WHERE	criteria_lookup_type='PSP_SELECTION_CRITERIA'
							AND	criteria_lookup_code='AST'
							AND	include_exclude_flag='E'
							AND	request_id =p_request_id)
					AND	(	(paf.payroll_id = has.payroll_id
							AND     paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND	has.assignment_set_id = hasa.assignment_set_id)
						OR	(paf.assignment_id = hasa.assignment_id
							AND     paf.assignment_type = 'E'
							AND	paf.effective_start_date <= p_effort_end
							AND	paf.effective_end_date >= p_effort_start
							AND	hasa.assignment_set_id=has.assignment_set_id
							AND	include_or_exclude ='I'))
					AND	NOT EXISTS	(SELECT	assignment_id
						FROM	hr_assignment_set_amendments hasa
						WHERE	hasa.assignment_id = paf.assignment_id
						AND     paf.assignment_type = 'E'
						AND	hasa.include_or_exclude ='E'
						AND	paf.effective_start_date <= p_effort_end
						AND	paf.effective_end_date >= p_effort_start));

Line: 5699

						g_exec_string := 'DELETE FROM	psp_selected_persons_t
							WHERE	assignment_id IN	(SELECT	assignment_id
								FROM	per_assignments_f paf,
									pay_people_groups ppg
								WHERE	paf.people_group_id= ppg.people_group_id
								AND	paf.assignment_type = ''' || 'E' || '''
								AND	paf.effective_end_date >= :p_effort_Start
								AND	paf.effective_start_date <= :p_effort_end
								AND	ppg.people_group_id IN	(SELECT	people_group_id
										FROM	pay_people_groups
										WHERE	' || l_sql_string || '))
							AND	request_id = :request_id';

Line: 5734

						g_exec_string := 'DELETE FROM	psp_selected_persons_t
							WHERE	assignment_id IN	(SELECT	NVL(psl.assignment_id,0)
								FROM	psp_summary_lines psl,
									psp_distribution_lines_history pdnh,
									psp_adjustment_lines_history palh,
									psp_pre_gen_dist_lines_history ppg,
									gl_code_combinations gcc
								WHERE	gcc.code_combination_id= psl.gl_code_combination_id
								AND	psl.business_group_id = '|| p_business_group_id || '
								AND	psl.set_of_books_id = ' || p_set_of_books_id || '
								AND	psl.summary_line_id = pdnh.summary_line_id(+)
								AND	psl.summary_line_id = ppg.summary_line_id(+)
								AND	psl.summary_line_id = palh.summary_line_id(+)
								AND	psl.status_code='||''''||'A'||''''||'
								AND	(	(psl.source_type IN ('||''''||'N'||''''||
											' ,'|| ''''|| 'O'||''''||')
										AND	pdnh.distribution_date BETWEEN
											:p_effort_start AND :p_effort_end
										AND	pdnh.reversal_entry_flag IS NULL
										AND	psl.summary_line_id =pdnh.summary_line_id
										AND	pdnh.adjustment_batch_name IS NULL)
									OR	(psl.source_type= '||''''||'P'||''''||'
										AND	ppg.distribution_date BETWEEN
											:p_effort_start AND :p_effort_end
										AND	ppg.adjustment_batch_name IS NULL
										AND	ppg.summary_line_id =psl.summary_line_id
										AND	ppg.reversal_entry_flag IS NULL)
									OR	(psl.source_type= '||''''||'A'||''''||'
										AND	palh.summary_line_id =psl.summary_line_id
										AND	palh.adjustment_batch_name IS NULL
										AND	NVL(palh.original_line_flag, ' ||
											''''|| 'N' || ''''|| ') = '||''''||
											'N' || '''' || '
										AND	palh.distribution_date BETWEEN
											:p_effort_start AND :p_effort_end))
								AND	gcc.code_combination_id= psl.gl_code_combination_id
								AND	gcc.code_combination_id IN	(SELECT	code_combination_id
										FROM	gl_code_combinations
										WHERE	' || l_sql_string || '))
							AND	request_id = :request_id';