APPS.PER_HU_ABS_REP_ARCHIVE_PKG SQL Statements

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

Line: 56

  SELECT effective_date
        ,fnd_date.canonical_to_date(per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'DATE'))
        ,per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'PAYROLL_ID')
        ,per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'ASG_SET_ID')
        ,per_hu_abs_rep_archive_pkg.get_parameter(legislative_parameters, 'EMP_ID')
        ,business_group_id
  FROM   pay_payroll_actions
  WHERE  payroll_action_id = c_payroll_action_id;

Line: 86

    SELECT payroll_name
    FROM   pay_all_payrolls_f
    WHERE  payroll_id = cpayroll_id
    AND    ceffective_date BETWEEN effective_start_date AND effective_end_date;

Line: 93

    SELECT full_name
    FROM   per_All_people_f
    WHERE  person_id = cperson_id
    AND    ceffective_date BETWEEN effective_start_date AND effective_end_date;

Line: 145

SELECT 1
FROM   pay_user_column_instances_f   pui
      ,pay_user_columns              puc
      ,pay_user_tables               put
WHERE  pui.user_column_id    =  puc.user_column_id
AND    puc.user_column_name  = 'Holiday Type'
AND    puc.legislation_code  = 'HU'
AND    puc.user_table_id     =  put.user_table_id
AND    put.user_table_name   = 'HU_ABSENCE_REPORT_ACCRUAL_PLAN_MAPPINGS'
AND    pui.business_group_id =  c_business_group_id
AND    put.legislation_code  = 'HU'
AND    c_reporting_date BETWEEN pui.effective_start_date
                        AND     pui.effective_end_date
AND    pui.value LIKE 'HU%';

Line: 179

        sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';

Line: 186

        sqlstr := 'SELECT distinct person_id
                   FROM  per_people_f ppf
                        ,pay_payroll_actions ppa
                   WHERE ppa.payroll_action_id = :payroll_action_id
                   AND   ppa.business_group_id = ppf.business_group_id
                   ORDER BY ppf.person_id';

Line: 198

      sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';

Line: 215

    SELECT paa.assignment_id             assignment_id
          ,paa.people_group_id           people_group_id
          ,hoi.org_information1          company_name
          ,pap.full_name                 full_name
          ,pap.person_id                 person_id
          ,pap.employee_NUMBER           emp_no
          ,pap.date_of_birth             date_of_birth
          ,ppf.date_start                hire_date
          ,ppf.actual_termination_date   termination_date
          ,hou.name                      organization
          ,paa.location_id               location_id
          ,paa.payroll_id                payroll_id
          ,paa.job_id                    job_id
    FROM   pay_payroll_actions           ppa
          ,per_all_assignments_f         paa
          ,per_all_people_f              pap
          ,per_periods_of_service        ppf
          ,hr_organization_information   hoi
          ,hr_all_organization_units     hou
    WHERE  ppa.payroll_action_id         = c_pact_id
    AND    pap.person_id                 = paa.person_id
    AND    pap.person_id                 BETWEEN c_stperson
                                         AND     c_endperson
    AND    pap.person_id                 =  NVL(c_emp_id,pap.person_id)
    AND    paa.primary_flag              = 'Y'
    AND    paa.assignment_type           = 'E'
    AND    ppa.business_group_id         =  paa.business_group_id
    AND    pap.business_group_id         =  paa.business_group_id
    AND    paa.period_of_service_id      =  ppf.period_of_service_id
    AND    NVL(paa.payroll_id,0)         =  NVL(c_payroll_id,NVL(paa.payroll_id,0))
    AND    hoi.organization_id           =  paa.business_group_id
    AND    hoi.org_information_context   = 'HU_COMPANY_INFORMATION_DETAILS'
    AND    hou.organization_id           =  paa.organization_id
    AND    c_reporting_date              BETWEEN pap.effective_start_date
                                         AND     pap.effective_end_date
    AND    c_reporting_date              BETWEEN paa.effective_start_date
                                         AND     paa.effective_end_date
    AND    (ppf.actual_termination_date  IS NULL
            OR ppf.actual_termination_date > c_reporting_date)
    ORDER BY assignment_id;

Line: 259

    SELECT pur.row_low_range_or_name     accrual_plan
    FROM   pay_user_column_instances_f   pui
          ,pay_user_columns              puc
          ,pay_user_tables               put
          ,pay_user_rows_f               pur
    WHERE  pui.user_column_id    =  puc.user_column_id
    AND    puc.user_column_name  = 'Holiday Type'
    AND    puc.legislation_code  = 'HU'
    AND    puc.user_table_id     =  put.user_table_id
    AND    put.user_table_name   = 'HU_ABSENCE_REPORT_ACCRUAL_PLAN_MAPPINGS'
    AND    pui.business_group_id =  c_business_group_id
    AND    pui.value             =  c_holiday_type
    AND    put.legislation_code  = 'HU'
    AND    pui.user_row_id       =  pur.user_row_id
    AND    c_reporting_date BETWEEN pui.effective_start_date
                            AND     pui.effective_end_date
    AND    c_reporting_date BETWEEN pur.effective_start_date
                            AND     pur.effective_end_date  ;

Line: 279

    SELECT hrl.location_code
    FROM   hr_locations          hrl
    WHERE  hrl.location_id   = c_location_id;

Line: 284

    SELECT jbt.name
    FROM   per_jobs_tl jbt
    WHERE  jbt.language = userenv('LANG')
    AND    jbt.job_id   = c_job_id;

Line: 291

    SELECT 1
    FROM   hr_assignment_sets    has
          ,per_all_assignments_f paa
    WHERE  has.assignment_set_id =  NVL(c_assignment_set_id, has.assignment_set_id)
    AND    paa.assignment_id     =  c_assignment_id
    AND    NVL(paa.payroll_id,0) =  NVL(has.payroll_id,NVL(paa.payroll_id,0))
    AND    NOT EXISTS
           (
            SELECT 1
            FROM   hr_assignment_set_amendments hasa
            WHERE  hasa.assignment_set_id =  c_assignment_set_id
            AND    hasa.assignment_id     =  c_assignment_id
            AND    hasa.include_or_exclude = 'E'
           )
    AND   NOT EXISTS
           (
            SELECT 1
            FROM   hr_assignment_set_amendments hasa
            WHERE  hasa.assignment_set_id  = c_assignment_set_id
            AND    hasa.assignment_id      <>  c_assignment_id
            AND    hasa.include_or_exclude = 'I'
            AND NOT EXISTS
                         (
                          SELECT 1
                          FROM   hr_assignment_set_amendments hasa
                          WHERE  hasa.assignment_set_id  =  c_assignment_set_id
                          AND    hasa.assignment_id      =  c_assignment_id
                          AND    hasa.include_or_exclude = 'I'
                          )
            );

Line: 324

    SELECT accrual_plan_id
          ,accrual_plan_element_type_id
          ,co_formula_id
    FROM   pay_accrual_plans
    WHERE  accrual_plan_name = c_accrual_plan
    AND    business_group_id = c_business_group_id;

Line: 333

    SELECT MAX(paa.effective_end_date)
    FROM   per_all_assignments_f        paa
          ,per_assignment_status_types  pas
    WHERE  paa.assignment_id              =  c_assignment_id
    AND    pas.assignment_status_type_id  =  paa.assignment_status_type_id
    AND    pas.per_system_status          = 'SUSP_ASSIGN'
    AND    paa.effective_end_date
                BETWEEN to_date('01-JAN'||to_char(c_reporting_date,'YYYY'),'dd/mm/yyyy')
                AND     to_date('31-DEC'||to_char(c_reporting_date,'YYYY'),'dd/mm/yyyy');

Line: 884

          SELECT pay_assignment_actions_s.NEXTVAL
          INTO   l_actid
          FROM   dual;

Line: 940

  SELECT count(*)
  FROM PER_CONTACT_RELATIONSHIPS pcr
       ,per_all_people_f pap
       ,per_all_assignments_f paa
  WHERE pcr.person_id = paa.person_id
  AND   pap.business_group_id = p_business_group_id
  AND   pcr.business_group_id = p_business_group_id
  AND   pcr.cont_information3 = 'Y'
  AND   paa.assignment_id = p_assignment_id
  AND   pcr.contact_person_id = pap.person_id
  AND   pcr.contact_type IN ('C','A')
  AND   p_start_date BETWEEN decode(pcr.date_start,NULL,to_date('01010001','ddmmyyyy'),pcr.date_start)
                     AND     decode(pcr.date_end,NULL,to_date('01014712','ddmmyyyy'),pcr.date_end)
  AND   p_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date
  AND   p_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
  AND   months_between(p_end_date,pap.date_of_birth)/12 < 16;

Line: 958

  SELECT count(*)
  FROM PER_CONTACT_RELATIONSHIPS pcr
       ,per_all_people_f pap
       ,per_all_assignments_f paa
  WHERE pcr.person_id = paa.person_id
  AND   pap.business_group_id = p_business_group_id
  AND   pcr.business_group_id = p_business_group_id
  AND   pcr.cont_information3 = 'Y'
  AND   paa.assignment_id = p_assignment_id
  AND   pcr.contact_person_id = pap.person_id
  AND   pcr.contact_type IN ('C','A')
  AND   p_start_date BETWEEN decode(pcr.date_start,NULL,to_date('01010001','ddmmyyyy'),pcr.date_start)
                     AND     decode(pcr.date_end,NULL,to_date('01014712','ddmmyyyy'),pcr.date_end)
  AND   p_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date
  AND   p_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
  AND   to_char(pap.date_of_birth,'mmdd') BETWEEN to_char(p_start_date,'mmdd')
                                          AND     to_char(p_end_date,'mmdd')
  AND   to_char(p_end_date,'yyyy') - to_char(pap.date_of_birth,'yyyy') = 16;

Line: 978

  SELECT pap.date_of_birth dob
  FROM per_contact_relationships pcr
       ,per_all_people_f pap
       ,per_all_assignments_f paa
  WHERE pcr.person_id = paa.person_id
  AND   pap.business_group_id = p_business_group_id
  AND   pcr.business_group_id = p_business_group_id
  AND   pcr.cont_information3 = 'Y'
  AND   paa.assignment_id = p_assignment_id
  AND   pcr.contact_person_id = pap.person_id
  AND   pcr.contact_type IN ('C','A')
  AND   p_start_date between decode(pcr.date_start,NULL,to_date('01010001','ddmmyyyy'),pcr.date_start)
        AND decode(pcr.date_end,NULL,to_date('01014712','ddmmyyyy'),pcr.date_end)
  AND   p_start_date between pap.effective_start_date AND pap.effective_end_date
  AND   p_start_date between paa.effective_start_date AND paa.effective_end_date
  AND   to_char(pap.date_of_birth,'mmdd') between to_char(p_start_date,'mmdd') AND to_char(p_end_date,'mmdd')
  AND   to_char(p_end_date,'yyyy') - to_char(pap.date_of_birth,'yyyy') = 16
  ORDER BY pap.date_of_birth desc;

Line: 1036

SELECT ptp.number_per_fiscal_year
      ,ptp.period_type
FROM   pay_payrolls_f        ppf
      ,per_time_period_types ptp
WHERE payroll_id         = p_payroll_id
AND   ptp.period_type    = ppf.period_type
AND p_calculation_date
    BETWEEN  ppf.effective_start_date
    AND      ppf.effective_end_date ;

Line: 1106

SELECT pap.date_of_birth
FROM   per_all_people_f       pap
      ,per_all_assignments_f  paa
WHERE  paa.assignment_id = p_assignment_id
AND    paa.person_id     = pap.person_id
AND    p_calculation_date
       BETWEEN  paa.effective_start_date
       AND      paa.effective_end_date
AND    p_calculation_date
       BETWEEN  pap.effective_start_date
       AND      pap.effective_end_date;

Line: 1134

  SELECT  pem_information1
  FROM    per_previous_employers
  WHERE   business_group_id        = p_business_group_id
  AND     person_id                = c_person_id
  AND     to_char(end_date,'YYYY') = p_termination_year
  ORDER BY end_date DESC;

Line: 1142

  SELECT person_id FROM per_All_assignments_f
  WHERE assignment_id = p_assignment_id;

Line: 1172

    SELECT  pdf.effective_start_date,pdf.effective_end_date
    FROM    per_disabilities_f pdf ,per_all_people_f papf, per_all_assignments_f paaf
    WHERE   paaf.assignment_id=p_assignment_id
    AND     paaf.business_group_id=p_business_group_id
    AND     paaf.person_id=papf.person_id
    AND     papf.person_id=pdf.person_id
    AND     pdf.dis_information1='Y'
    AND     p_period_start_date between papf.effective_start_date and papf.effective_end_date
    AND     p_period_start_date between paaf.effective_start_date and paaf.effective_end_date
    AND     pdf.effective_start_date <= p_period_end_date
    AND     pdf.effective_end_date>=p_period_start_date;

Line: 1233

    SELECT  paaf.effective_start_date,paaf.effective_end_date,pj.date_to
    FROM    per_all_assignments_f paaf,per_jobs pj
    WHERE   paaf.assignment_id=p_assignment_id
    AND     paaf.business_group_id=p_business_group_id
    AND     paaf.job_id=pj.job_id
    AND     pj.job_information3='Y'
    AND     paaf.effective_start_date <= p_period_end_date
	AND     paaf.effective_end_date>= p_period_start_date
    AND     pj.date_FROM <= p_period_end_date
	AND     nvl(pj.date_to,to_date('31-12-4712','dd-mm-yyyy')) >= p_period_start_date;