eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_GB_ENROLL_INFO_GENE SQL Statements

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

Line: 24


     SELECT start_date,
      end_date,
      regular_payment_date
     FROM per_time_periods ptp
     WHERE ptp.time_period_id = p_period;

Line: 31


     SELECT start_date,
      end_date,
      regular_payment_date
     FROM per_time_periods ptp
     WHERE  trunc(l_pay_date_paid) BETWEEN start_date AND end_date
     AND payroll_id=p_payroll;

Line: 40


 SELECT PCV.PCV_INFORMATION1,
      PCV.PCV_INFORMATION2,PCV.PCV_INFORMATION3,
PCV.PCV_INFORMATION4
    FROM PQP_CONFIGURATION_VALUES PCV,
         PAY_PAYROLLS_F PPF
    WHERE PCV.PCV_INFORMATION_CATEGORY like 'PAY_GB_ENROLMENT_COMM_DETAILS'
    AND PPF.business_group_id   =  PCV.business_group_id
    AND PPF.PAYROLL_ID =  P_PAYROLL_ID ;

Line: 52


 SELECT PCV.PCV_INFORMATION14,
      PCV.PCV_INFORMATION15,PCV.PCV_INFORMATION16,PCV.PCV_INFORMATION17
    FROM PQP_CONFIGURATION_VALUES PCV,
         PAY_PAYROLLS_F PPF
    WHERE PCV.PCV_INFORMATION_CATEGORY like 'PAY_GB_QUALIFIED_PENSIONS_INFO'
    AND PPF.business_group_id   =  PCV.business_group_id
    AND PCV.PCV_INFORMATION1 = P_QUALIFYING_SCHEME
   AND PPF.PAYROLL_ID =  P_PAYROLL_ID ;

Line: 64


SELECT  trim (org_information3) employer
      , ppf.period_type period
FROM    pay_payrolls_f ppf
      , hr_soft_coding_keyflex hscf
      , hr_organization_information hoi
WHERE   ppf.payroll_id = p_payroll
AND     ppf.business_group_id = hoi.organization_id
AND     hoi.org_information_context = 'Tax Details References'
AND     nvl (hoi.org_information10, 'UK') = 'UK'
AND     ppf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
AND     hscf.segment1 = hoi.org_information1
AND     l_prp_end_date
        BETWEEN ppf.effective_start_date
        AND     ppf.effective_end_date;

Line: 81


    SELECT
      max(decode(global_name,'AUTO_ENROL_TRIG_WEEKLY', global_value ,null)) auto_enrol_trig_weekly ,
      max(decode(global_name,'AUTO_ENROL_TRIG_MONTHLY', global_value ,null)) auto_enrol_trig_monthly ,
      max(decode(global_name,'AUTO_ENROL_TRIG_ANNUAL', global_value ,null)) auto_enrol_trig_annual,
      max(decode(global_name,'AUTO_ENROL_TRIG_BIWEEK', global_value ,null)) auto_enrol_trig_biweek,
      max(decode(global_name,'AUTO_ENROL_TRIG_LUNAR', global_value ,null)) auto_enrol_trig_lunar,
      max(decode(global_name,'AUTO_ENROL_TRIG_QUARTERLY', global_value ,null)) auto_enrol_trig_quarterly,
      max(decode(global_name,'AUTO_ENROL_TRIG_SEMIYEAR', global_value ,null)) auto_enrol_trig_semiyear,
      max(decode(global_name,'QE_ANNUAL_LT', global_value ,null)) QE_ANNUAL_LT
      ,max(decode(global_name,'QE_BIWEEK_LT', global_value ,null)) QE_BIWEEK_LT
      ,max(decode(global_name,'QE_LUNAR_LT', global_value ,null)) QE_LUNAR_LT
      ,max(decode(global_name,'QE_MONTHLY_LT', global_value ,null)) QE_MONTHLY_LT
      ,max(decode(global_name,'QE_QUARTERLY_LT', global_value ,null)) QE_QUARTERLY_LT
      ,max(decode(global_name,'QE_SEMIYEAR_LT', global_value ,null)) QE_SEMIYEAR_LT
      ,max(decode(global_name,'QE_WEEKLY_LT', global_value ,null)) QE_WEEKLY_LT
    FROM FF_GLOBALS_F FGF
    WHERE global_name IN ( 'AUTO_ENROL_TRIG_ANNUAL', 'AUTO_ENROL_TRIG_WEEKLY', 'AUTO_ENROL_TRIG_MONTHLY',
	'AUTO_ENROL_TRIG_BIWEEK','AUTO_ENROL_TRIG_LUNAR','AUTO_ENROL_TRIG_QUARTERLY',
	'AUTO_ENROL_TRIG_SEMIYEAR','QE_ANNUAL_LT','QE_BIWEEK_LT','QE_LUNAR_LT','QE_MONTHLY_LT'
,'QE_QUARTERLY_LT','QE_SEMIYEAR_LT','QE_WEEKLY_LT')
    AND l_prp_end_date BETWEEN fgf.effective_start_date and fgf.effective_end_date;

Line: 106


      SELECT  max (decode (name, 'Qualifying Scheme Name', input_value_id)) qualifying_scheme_name
            , max (decode (name, 'Auto Enrollment Date', input_value_id)) auto_enrollment_date
            , max (decode (name, 'Pension Classification', input_value_id)) pension_classification
            , max (decode (name, 'Opt Out Period End Date', input_value_id)) opt_out_period_end_date
            , max (decode (name, 'Total Earning PRP' , input_value_id)) total_earning_prp
            , max (decode (name, 'Postponement Type' , input_value_id)) postponement_type
            , max (decode (name, 'Postponement End Date', input_value_id)) postponement_end_date
            , max (decode (name, 'Qualifying scheme exists' , input_value_id)) qualifying_scheme_exists
            , max (decode (name, 'Opt in Date', input_value_id)) opt_in_date
            , max (decode (name, 'Eligible Job Holder Date', input_value_id)) elig_jh_date
            , max (decode (name, 'Opt Out Date' , input_value_id)) opt_out_date
            , max (decode (name, 'Aggregate Earnings' , input_value_id)) agg_earnings_pensions_id
            , max (decode (name, 'Main Entry for Aggregation' , input_value_id)) main_entry_id

      FROM     (
              SELECT  input_value_id
                    , name
              FROM    pay_input_values_f iv,pay_element_types_f  et where
              et.element_type_id = iv.element_type_id
              and et.element_name = 'Pensions Information'
              );

Line: 134


SELECT papf.full_name, papf.email_address, papf.person_id FROM
	per_all_people_f papf,
	per_all_assignments_f paaf
WHERE
	papf.person_id=paaf.person_id
  AND paaf.assignment_id = p_assignment_id
  AND l_prp_end_date BETWEEN
	paaf.effective_start_date and paaf.effective_end_date
  AND l_prp_end_date BETWEEN
	papf.effective_start_date and papf.effective_end_date;

Line: 146


select 1 from pay_element_entries_f peef,pay_element_types_f  petf where
              peef.element_type_id = petf.element_type_id
              and petf.element_name = 'Pensions Information'
and p_assignment_id = peef.assignment_id
  AND l_prp_end_date BETWEEN
	peef.effective_start_date and peef.effective_end_date
  AND l_prp_end_date BETWEEN
	petf.effective_start_date and petf.effective_end_date;

Line: 158


select * from pay_action_information where
action_context_id in
(
SELECT  assignment_action_id
FROM    fnd_concurrent_programs fcp
      , fnd_concurrent_requests fcr
      , pay_payroll_actions  ppa
      , pay_assignment_actions paa
where fcr.REQUEST_ID = ppa.REQUEST_ID
AND ppa.payroll_action_id = paa.payroll_action_id
AND   concurrent_program_name = 'PENAUTOENR'
AND     fcp.concurrent_program_id = fcr.concurrent_program_id
AND paa.assignment_id = p_assignment_id
)
AND ACTION_INFORMATION_CATEGORY='GB_ENROLL_PENSIONS'
AND action_information30=p_letter_type
AND ACTION_CONTEXT_TYPE='AAP';

Line: 176


cursor c_archive_details_update (p_assignment_id number,p_period_id number) is
select * from pay_action_information where
action_context_id in
(
SELECT  assignment_action_id
FROM    fnd_concurrent_programs fcp
      , fnd_concurrent_requests fcr
      , pay_payroll_actions  ppa
      , pay_assignment_actions paa
where fcr.REQUEST_ID = ppa.REQUEST_ID
AND ppa.payroll_action_id = paa.payroll_action_id
AND   concurrent_program_name = 'PENAUTOENR'
AND     fcp.concurrent_program_id = fcr.concurrent_program_id
AND paa.assignment_id = p_assignment_id
AND (substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,4)+1))
,1,length(substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,4)+1)),1,
(instr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,4)+1)),' ',1,1)-1))))) ='GB_VALIDATE_COMMIT'
AND substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,2)+1))
,1,length(substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,2)+1)),1,
(instr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,2)+1)),' ',1,1)-1)))) = p_period_id
)
AND ACTION_INFORMATION_CATEGORY='GB_ENROLL_PENSIONS'
AND ACTION_CONTEXT_TYPE='AAP'
order by action_context_id desc;-- LAtest archive data in that period

Line: 203


select assignment_number,full_name from per_all_people_f papf,per_all_assignments_f paaf
where papf.person_id = paaf.person_id
and paaf.assignment_id = p_assignment_id
and l_prp_end_date between papf.effective_start_date and papf.effective_end_date
and l_prp_end_date between paaf.effective_start_date and paaf.effective_end_date;

Line: 212


    SELECT max(decode(trim(peevf.input_value_id),l_input_value_ids.qualifying_scheme_name,screen_entry_value, null)) qualifying_scheme_name,
           max(decode(trim(peevf.input_value_id),l_input_value_ids.agg_earnings_pensions_id,screen_entry_value, null)) agg_earnings,
           fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.auto_enrollment_date,screen_entry_value, null))) auto_enrollment_date   ,
           max(decode(trim(peevf.input_value_id),l_input_value_ids.pension_classification,hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',screen_entry_value), null)) pension_classification,
           fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.opt_out_period_end_date,screen_entry_value, null))) opt_out_period_end_date,
           max(decode(trim(peevf.input_value_id),l_input_value_ids.total_earning_prp,screen_entry_value, null)) total_earning_prp,
           max(decode(trim(peevf.input_value_id),l_input_value_ids.postponement_type,hr_general.decode_lookup('GB_PENSION_POSTPONEMENT_TYPES',screen_entry_value), null)) postponement_type ,
           fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.postponement_end_date,screen_entry_value, null))) postponement_end_date,
           max(decode(trim(peevf.input_value_id),l_input_value_ids.qualifying_scheme_exists,screen_entry_value, null)) qualifying_scheme_exists,
           fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.opt_in_date,screen_entry_value, null))) opt_in_date,
       max(decode(trim(peevf.input_value_id),l_input_value_ids.main_entry_id,screen_entry_value, null)) main_entry,
           fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.elig_jh_date,screen_entry_value, null))) elig_jh_date,
           fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.opt_out_date,screen_entry_value, null))) opt_out_date
    from pay_element_entry_values_f peevf,
         pay_element_entries_f peef,
         pay_element_types_f petf
    where peevf.input_value_id   in (l_input_value_ids.qualifying_scheme_name ,
	--l_input_value_ids.g_agg_earnings_pensions_id ,
	l_input_value_ids.auto_enrollment_date ,
	l_input_value_ids.pension_classification ,
	l_input_value_ids.opt_out_period_end_date ,
	l_input_value_ids.total_earning_prp ,
	l_input_value_ids.postponement_type ,
	l_input_value_ids.postponement_end_date ,
	l_input_value_ids.qualifying_scheme_exists ,
	l_input_value_ids.opt_in_date,
	l_input_value_ids.elig_jh_date,
	--l_input_value_ids.g_main_entry_id,
	l_input_value_ids.opt_out_date)
  and peevf.element_entry_id  = peef.element_entry_id
  and peef.element_type_id = petf.element_type_id
  and petf.element_name = 'Pensions Information'
  and peef.assignment_id = l_assignment_id
	and l_prp_end_date -- Review again against the bug 14813352
  between peevf.effective_start_date and peevf.effective_end_date
  and l_prp_end_date -- Review again against the bug 14813352
  between peef.effective_start_date and peef.effective_end_date
  and l_prp_end_date -- Not Needed for pay_element_types_f -Review again against the bug 14813352
  between petf.effective_start_date and petf.effective_end_date
    group by peevf.element_entry_id;

Line: 278


procedure update_archive(p_archive_details_update IN c_archive_details_update%rowtype
,p_period IN number
,p_issue_date IN date
,p_postponement_date IN date default null
,p_postponement_type IN varchar2
,p_letter IN varchar2)
is
/*
ACTION_INFORMATION26 - Time Period
ACTION_INFORMATION27 - Issue Date
ACTION_INFORMATION28 - Postponement Date
ACTION_INFORMATION29 - Postponement Type
ACTION_INFORMATION30 - Letter Type
*/
l_ovn number;

Line: 294


fnd_file.put_line(fnd_file.LOG,'Entering update_archive');

Line: 295


l_ovn := p_archive_details_update.object_version_number;

Line: 297


 pay_action_information_api.update_action_information
(  p_action_information_id =>p_archive_details_update.action_information_id
  ,p_object_version_number  =>l_ovn
  ,p_action_information1    =>p_archive_details_update.action_information1
  ,p_action_information2    =>p_archive_details_update.action_information2
  ,p_action_information3    =>p_archive_details_update.action_information3
  ,p_action_information4    =>p_archive_details_update.action_information4
  ,p_action_information5    =>p_archive_details_update.action_information5
  ,p_action_information6    =>p_archive_details_update.action_information6
  ,p_action_information7    =>p_archive_details_update.action_information7
  ,p_action_information8    =>p_archive_details_update.action_information8
  ,p_action_information9    =>p_archive_details_update.action_information9
  ,p_action_information10   =>p_archive_details_update.action_information10
  ,p_action_information11   =>p_archive_details_update.action_information11
  ,p_action_information12   =>p_archive_details_update.action_information12
  ,p_action_information13   =>p_archive_details_update.action_information13
  ,p_action_information14   =>p_archive_details_update.action_information14
  ,p_action_information15   =>p_archive_details_update.action_information15
  ,p_action_information16   =>p_archive_details_update.action_information16
  ,p_action_information17   =>p_archive_details_update.action_information17
  ,p_action_information18   =>p_archive_details_update.action_information18
  ,p_action_information19   =>p_archive_details_update.action_information19
  ,p_action_information20   =>p_archive_details_update.action_information20
  ,p_action_information21   =>p_archive_details_update.action_information21
  ,p_action_information22   =>p_archive_details_update.action_information22
  ,p_action_information23   =>p_archive_details_update.action_information23
  ,p_action_information24   =>p_archive_details_update.action_information24
  ,p_action_information25   =>p_archive_details_update.action_information25
  ,p_action_information26   =>p_period
  ,p_action_information27   =>fnd_date.date_to_canonical(p_issue_date)
  ,p_action_information28   =>fnd_date.date_to_canonical(p_postponement_date)
  ,p_action_information29   =>p_postponement_type
  ,p_action_information30   => P_LETTER);

Line: 330


end update_archive;

Line: 344


l_archive_details_update c_archive_details_update%rowtype;

Line: 355


    SELECT ELEMENT_ENTRY_ID, GREATEST(EFFECTIVE_START_DATE,l_PRP_START_DATE) START_DATE
    FROM PAY_ELEMENT_ENTRIES_F PEEF
    WHERE ASSIGNMENT_ID =P_ASSIGNMENT_ID
    AND PEEF.ELEMENT_TYPE_ID in (select ELEMENT_TYPE_ID from pay_element_types_f where
    element_name ='Pensions Information' and legislation_code ='GB')
    AND l_PRP_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 428


	IF l_flag ='Y' THEN	        --Update Archive Table
         	OPEN c_archive_details_update(p_assignment_id,P_PERIOD) ;

Line: 430


			FETCH c_archive_details_update into l_archive_details_update;

Line: 431


						IF c_archive_details_update%notfound then
						fnd_file.put_line(fnd_file.LOG,'Nothing to Update in the Archive.');

Line: 436


			--l_ovn := l_archive_details_update.object_version_number;

Line: 437


			CLOSE c_archive_details_update;

Line: 533


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 559


HR_ENTRY_API.UPDATE_ELEMENT_ENTRY (P_DT_UPDATE_MODE =>'UPDATE', P_SESSION_DATE => entries.START_DATE,
P_CHECK_FOR_UPDATE =>'N' , P_CREATOR_TYPE => 'F', P_ELEMENT_ENTRY_ID => entries.ELEMENT_ENTRY_ID ,
P_INPUT_VALUE_ID1 => l_input_value_ids.qualifying_scheme_name, P_ENTRY_VALUE1 => p_pension_entry_values.qualifying_scheme_name ,
P_INPUT_VALUE_ID2 => l_input_value_ids.auto_enrollment_date,	P_ENTRY_VALUE2 => p_pension_entry_values.auto_enrollment_date,--fnd_date.canonical_to_date(p_pension_entry_values.auto_enrollment_date ),
P_INPUT_VALUE_ID3 => l_input_value_ids.pension_classification, 	P_ENTRY_VALUE3 => p_pension_entry_values.pension_classification ,
P_INPUT_VALUE_ID4 => l_input_value_ids.opt_out_period_end_date,	P_ENTRY_VALUE4 => l_opt_out_new,--Date
P_INPUT_VALUE_ID5 => l_input_value_ids.total_earning_prp,	P_ENTRY_VALUE5 => p_pension_entry_values.total_earning_prp,
P_INPUT_VALUE_ID6 => l_input_value_ids.postponement_type, P_ENTRY_VALUE6 => p_pension_entry_values.postponement_type,
P_INPUT_VALUE_ID7 => l_input_value_ids.postponement_end_date, P_ENTRY_VALUE7 => p_pension_entry_values.postponement_end_date,--fnd_date.canonical_to_date(p_pension_entry_values.postponement_end_date),
P_INPUT_VALUE_ID8 => l_input_value_ids.qualifying_scheme_exists, 	P_ENTRY_VALUE8 => l_scheme_exists,
P_INPUT_VALUE_ID9 => l_input_value_ids.opt_in_date,	P_ENTRY_VALUE9 => p_pension_entry_values.opt_in_date,--fnd_date.canonical_to_date(p_pension_entry_values.opt_in_date),
P_INPUT_VALUE_ID10 => l_input_value_ids.elig_jh_date,	P_ENTRY_VALUE10 => p_pension_entry_values.elig_jh_date,--fnd_date.canonical_to_date(p_pension_entry_values.elig_jh_date),
P_INPUT_VALUE_ID11 => l_input_value_ids.opt_out_date,	P_ENTRY_VALUE11 => fnd_date.canonical_to_date(p_pension_entry_values.opt_out_date),
P_INPUT_VALUE_ID12 => l_input_value_ids.main_entry_id, P_ENTRY_VALUE12 => l_main_entry,
P_INPUT_VALUE_ID13 => l_input_value_ids.agg_earnings_pensions_id,P_ENTRY_VALUE13 => l_agg_earnings);

Line: 636


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 701


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 766


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 831


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 895


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 960


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 1025


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 1091


				update_archive (l_archive_details_update,p_period,p_issue_date
				,l_postponement_date
				,l_postponement_type
				,p_letter);

Line: 1165


      SELECT  DISTINCT
              paaf.assignment_id
      FROM    per_all_assignments_f paaf
            , per_assignment_status_types past
      WHERE   paaf.payroll_id = p_payroll
      AND     paaf.assignment_status_type_id = past.assignment_status_type_id
      AND     past.per_system_status = 'ACTIVE_ASSIGN'
      AND     paaf.assignment_type = 'E'
      AND     l_prp_end_date -- Review again against the bug 14813352
              BETWEEN paaf.effective_start_date
              AND     paaf.effective_end_date
      ORDER BY assignment_id;

Line: 1192


l_archive_details_update c_archive_details_update%rowtype;

Line: 1589


UPDATE fnd_concurrent_requests
    SET    output_file_type = 'XML'
    WHERE  request_id = p_request_id;

Line: 1595


    SELECT substr(p.product_version,1,2) INTO l_product_release
      FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
     WHERE a.application_id = p.application_id
       AND a.application_id = t.application_id
       AND t.language = Userenv ('LANG')
       AND Substr (a.application_short_name, 1, 5) = 'PAY';

Line: 1686


      SELECT  DISTINCT
              paaf.assignment_id
      FROM    per_all_assignments_f paaf
            , per_assignment_status_types past
      WHERE   paaf.payroll_id = p_payroll
      AND     paaf.assignment_status_type_id = past.assignment_status_type_id
      AND     past.per_system_status = 'ACTIVE_ASSIGN'
      AND     paaf.assignment_type = 'E'
      AND     l_prp_end_date -- Review again against the bug 14813352
              BETWEEN paaf.effective_start_date
              AND     paaf.effective_end_date
      ORDER BY assignment_id;