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.PQP_NL_PGGM_FUNCTIONS SQL Statements

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

Line: 9


SELECT nvl(fnd_number.canonical_to_number(global_value),0)
  FROM ff_globals_f
WHERE  global_name = c_global_name
  AND  legislation_code = 'NL'
  AND  (c_date_earned between effective_start_date and effective_end_date);

Line: 17


SELECT TPTYPE.number_per_fiscal_year
 FROM  pay_payroll_actions     PACTION
      ,per_time_periods        TPERIOD
      ,per_time_period_types   TPTYPE
WHERE PACTION.payroll_action_id   = c_payroll_action_id
  AND TPERIOD.payroll_id          = PACTION.payroll_id
  AND (PACTION.date_earned   between TPERIOD.start_date
                                and TPERIOD.end_date)
  AND TPTYPE.period_type          = TPERIOD.period_type;

Line: 29


SELECT nvl(fnd_number.canonical_to_number(global_value),0)
  FROM ff_globals_f
WHERE  global_name = c_global_name
  AND  legislation_code IS NULL
  AND  business_group_id = c_business_group_id
  AND  (c_date_earned between effective_start_date and effective_end_date);

Line: 62


SELECT nvl(minimum_age,0),nvl(maximum_age,100)
 FROM  pqp_pension_types_f
WHERE  pension_type_id = p_pension_type_id
  AND  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date;

Line: 150


SELECT organization_id
  FROM per_all_assignments_f
WHERE  assignment_id = p_assignment_id
  AND  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date;

Line: 162


SELECT organization_id_parent
  FROM per_org_structure_elements
WHERE  organization_id_child = c_org_id
  AND  org_structure_version_id = c_version_id
  AND  business_group_id = p_business_group_id;

Line: 172


SELECT decode(p_ee_or_total,0,nvl(fnd_number.canonical_to_number(org_information4),0)
             ,nvl(fnd_number.canonical_to_number(org_information5),0))
  FROM hr_organization_information
WHERE  org_information_context = 'PQP_NL_PGGM_PT'
  AND  organization_id = c_org_id
  AND  p_date_earned BETWEEN fnd_date.canonical_to_date(org_information1)
  AND  nvl(fnd_date.canonical_to_date(org_information2),hr_api.g_eot)
  AND  fnd_number.canonical_to_number(org_information3) = p_pension_type_id;

Line: 185


SELECT decode(p_ee_or_total,0,nvl(ee_contribution_percent,0)
             ,nvl(er_contribution_percent,0))
  FROM pqp_pension_types_f
WHERE  pension_type_id = p_pension_type_id
  AND  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date;

Line: 344


SELECT max(fnd_number.canonical_to_number(row_low_range_or_name))
  FROM pay_user_rows_f
WHERE  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date
  AND  user_table_id = (SELECT user_table_id
                          FROM pay_user_tables
                        WHERE  user_table_name = 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD'
                          AND  legislation_code = 'NL'
                       );

Line: 356


SELECT max(fnd_number.canonical_to_number(row_low_range_or_name))
  FROM pay_user_rows_f
WHERE  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date
  AND  user_table_id = (SELECT user_table_id
                          FROM pay_user_tables
                        WHERE  user_table_name = 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD_CUSTOM'
                          AND  legislation_code = 'NL'
                       );

Line: 570


SELECT trunc(date_of_birth)
  FROM per_all_people_f per
      ,per_all_assignments_f paf
 WHERE per.person_id      = paf.person_id
   AND paf.assignment_id  = p_assignment_id
   AND p_effective_date BETWEEN per.effective_start_date
                            AND per.effective_end_date
   AND p_effective_date BETWEEN paf.effective_start_date
                            AND paf.effective_end_date;

Line: 627


SELECT pension_basis_calc_method
  FROM pqp_pension_types_f
WHERE  pension_type_id = p_pension_type_id
  AND  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date;

Line: 882


SELECT asg.effective_start_date start_date
      ,nvl(asg.effective_end_date,p_period_end_date) end_date
 FROM  per_all_assignments_f asg,per_assignment_status_types past
WHERE  asg.assignment_id = p_assignment_id
  AND  asg.assignment_status_type_id = past.assignment_status_type_id
  AND  past.per_system_status = 'ACTIVE_ASSIGN'
  AND  asg.effective_start_date <= p_period_end_date
  AND  nvl(asg.effective_end_date,p_period_end_date)
       >= p_period_start_date;

Line: 897


SELECT TPERIOD.period_type
 FROM  pay_payroll_actions     PACTION
      ,per_time_periods        TPERIOD
 WHERE PACTION.payroll_action_id   = c_payroll_action_id
  AND TPERIOD.payroll_id          = PACTION.payroll_id
  AND (PACTION.date_earned   between TPERIOD.start_date
                                and TPERIOD.end_date);

Line: 1036


SELECT organization_id
  FROM per_all_assignments_f
WHERE  assignment_id = p_assignment_id
  AND  p_date_earned BETWEEN effective_start_date
  AND  effective_end_date;

Line: 1048


SELECT organization_id_parent
  FROM per_org_structure_elements
WHERE  organization_id_child = c_org_id
  AND  org_structure_version_id = c_version_id
  AND  business_group_id = p_business_group_id;

Line: 1062


SELECT decode(p_code,0,org_information3,1,org_information4)
  FROM hr_organization_information
WHERE  organization_id = c_org_id
  AND  org_information_context = 'PQP_NL_PGGM_INFO'
  AND  p_date_earned BETWEEN fnd_date.canonical_to_date(org_information1)
  AND  fnd_date.canonical_to_date(nvl(org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
  AND  decode(p_code,0,org_information3,1,org_information4) IS NOT NULL;

Line: 1200


SELECT max(date_start)
 FROM  per_all_assignments_f asg
      ,per_periods_of_service pps
WHERE  pps.person_id     = asg.person_id
  AND  asg.assignment_id = c_asg_id
  AND  pps.business_group_id = p_business_group_id
  AND  date_start <= p_date_earned;

Line: 1213


SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
 FROM  per_assignments_f asg
      ,hr_soft_coding_keyflex target
WHERE  asg.assignment_id = p_assignment_id
  AND  target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
  AND  trunc(c_effective_date) between asg.effective_start_date
  AND  asg.effective_end_date
  AND  target.enabled_flag = 'Y';

Line: 1226


SELECT min(effective_start_date)
  FROM per_all_assignments_f
WHERE  assignment_id = p_assignment_id;

Line: 1236


SELECT db.defined_balance_id
  FROM pay_balance_types pbt
      ,pay_defined_balances db
      ,pay_balance_dimensions bd
WHERE  pbt.balance_name        = c_balance_name
  AND  pbt.balance_type_id     = db.balance_type_id
  AND  bd.balance_dimension_id = db.balance_dimension_id
  AND  bd.dimension_name       = c_dimension_name
  AND  (pbt.business_group_id  = c_business_group_id OR
        pbt.legislation_code   = 'NL')
  AND  (db.business_group_id   = pbt.business_group_id OR
        db.legislation_code    = 'NL');

Line: 1253


SELECT fnd_number.canonical_to_number(global_value)
  FROM ff_globals_f
WHERE  global_name = c_global_name
  AND  trunc (p_date_earned) BETWEEN effective_start_date
  AND  effective_end_date;

Line: 1264


SELECT cola.name
  FROM per_collective_agreements cola
      ,per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
  AND asg.collective_agreement_id = cola.collective_agreement_id
  AND cola.status = 'A'
  AND c_eff_date BETWEEN asg.effective_start_date
  AND asg.effective_end_date
  AND c_eff_date BETWEEN cola.start_date
  AND NVL(cola.end_date,to_date('31/12/4712','DD/MM/YYYY'));

Line: 1279


SELECT pety.number_per_fiscal_year
  FROM pay_payrolls_f ppaf
      ,per_time_period_types pety
WHERE  ppaf.payroll_id   = p_payroll_id
  AND  ppaf.period_type  = pety.period_type;

Line: 1290


SELECT organization_id
  FROM per_all_assignments_f
WHERE  assignment_id = p_assignment_id
  AND  c_eff_date BETWEEN effective_start_date
  AND  effective_end_date;

Line: 1301


select min (fffunc.cn(decode(
    decode(INPUTV.uom,'M','N','N','N','I','N',null),'N',decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,
nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),null))) eoy_or
FROM    pay_element_entry_values_f               EEV,
        pay_element_entries_f                    EE,
        pay_link_input_values_f                  LIV,
        pay_input_values_f                       INPUTV,
        pay_element_types_f                      pet
WHERE   INPUTV.element_type_id                  = pet.element_type_id
AND     INPUTV.name                             = 'End of Year Bonus Percentage'
AND     c_effective_date BETWEEN INPUTV.effective_start_date
                             AND INPUTV.effective_end_date
AND     INPUTV.element_type_id = pet.element_type_id
AND     c_effective_date BETWEEN pet.effective_start_date
                             AND pet.effective_end_date
AND     pet.element_name = 'PGGM Pensions General Information'
AND     pet.legislation_code = 'NL'
AND     LIV.input_value_id                     = INPUTV.input_value_id
AND     c_effective_date BETWEEN LIV.effective_start_date
                 AND LIV.effective_end_date
AND     EEV.input_value_id + 0                 = INPUTV.input_value_id
AND     EEV.element_entry_id                   = EE.element_entry_id
AND     EEV.effective_start_date               = EE.effective_start_date
AND     EEV.effective_end_date                 = EE.effective_end_date
AND     EE.element_link_id                     = LIV.element_link_id
AND     EE.assignment_id                       = c_asg_id
AND     c_effective_date BETWEEN EE.effective_start_date
                             AND EE.effective_end_date ;

Line: 1335


select min (fffunc.cn(decode(
    decode(INPUTV.uom,'M','N','N','N','I','N',null),'N',decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,
nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),null))) ha_or
FROM    pay_element_entry_values_f               EEV,
        pay_element_entries_f                    EE,
        pay_link_input_values_f                  LIV,
        pay_input_values_f                       INPUTV,
        pay_element_types_f                      pet
WHERE   INPUTV.element_type_id                  = pet.element_type_id
AND     INPUTV.name                             = 'Holiday Allowance Percentage'
AND     c_effective_date BETWEEN INPUTV.effective_start_date
                             AND INPUTV.effective_end_date
AND     INPUTV.element_type_id = pet.element_type_id
AND     c_effective_date BETWEEN pet.effective_start_date
                             AND pet.effective_end_date
AND     pet.element_name = 'PGGM Pensions General Information'
AND     pet.legislation_code = 'NL'
AND     LIV.input_value_id                     = INPUTV.input_value_id
AND     c_effective_date BETWEEN LIV.effective_start_date
                 AND LIV.effective_end_date
AND     EEV.input_value_id + 0                 = INPUTV.input_value_id
AND     EEV.element_entry_id                   = EE.element_entry_id
AND     EEV.effective_start_date               = EE.effective_start_date
AND     EEV.effective_end_date                 = EE.effective_end_date
AND     EE.element_link_id                     = LIV.element_link_id
AND     EE.assignment_id                       = c_asg_id
AND     c_effective_date BETWEEN EE.effective_start_date
                             AND EE.effective_end_date ;

Line: 1370


SELECT NVL(min(PTP.start_date),to_date('0101'||c_year,'DDMMYYYY'))
 FROM per_time_periods PTP
 WHERE
      PTP.payroll_id = p_payroll_id
 AND (substr(PTP.period_name,4,4)=c_year
      OR substr(PTP.period_name,3,4)=c_year);

Line: 1870


SELECT min(effective_start_date)
  FROM per_all_assignments_f
WHERE  assignment_id = p_assignment_id
   AND effective_end_date >= p_period_start_date;

Line: 1879


SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'0')) pt_perc
 FROM  per_assignments_f asg
      ,hr_soft_coding_keyflex target
WHERE  asg.assignment_id = p_assignment_id
  AND  target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
  AND  c_effective_date between asg.effective_start_date
  AND  asg.effective_end_date
  AND  target.enabled_flag = 'Y';

Line: 1895


SELECT fnd_number.canonical_to_number(NVL(target.segment28,'0')) hours_worked
      ,nvl(asg.normal_hours,0) total_hours
      ,nvl(asg.frequency,'W') freq
      ,asg.effective_start_date start_date
      ,asg.effective_end_date end_date
  FROM per_assignments_f asg
      ,per_assignment_status_types ast
      ,hr_soft_coding_keyflex target
WHERE  asg.assignment_id = p_assignment_id
   AND asg.assignment_status_type_id = ast.assignment_status_type_id
   AND ast.per_system_status = 'ACTIVE_ASSIGN'
   AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
   AND asg.effective_start_date <= p_period_end_date
   AND asg.effective_end_date >= p_period_start_date;

Line: 1916


SELECT TPERIOD.period_type
 FROM  pay_payroll_actions     PACTION
      ,per_time_periods        TPERIOD
 WHERE PACTION.payroll_action_id   = c_payroll_action_id
  AND TPERIOD.payroll_id          = PACTION.payroll_id
  AND (PACTION.date_earned   between TPERIOD.start_date
                                and TPERIOD.end_date);

Line: 2178


SELECT scl.SEGMENT1
  FROM per_all_assignments_f asg
      ,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_assignment_id
  AND c_date_earned BETWEEN asg.effective_start_date
  AND asg.effective_end_date
  AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;