APPS.PAY_GB_P11D_CAR_EXTRACT SQL Statements

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

Line: 16

      SELECT  business_group_id
      FROM    per_all_assignments_f
      WHERE   assignment_id = p_asg_id;

Line: 101

   SELECT payroll_id
   FROM   pay_all_payrolls_f
   WHERE  payroll_name = l_payroll_name
   AND    nvl(business_group_id, p_bus_group_id) = p_bus_group_id
   AND    ben_ext_person.g_effective_Date BETWEEN effective_start_date AND effective_end_Date;

Line: 143

   SELECT person_id
   FROM   per_all_people_f
   WHERE  employee_number = l_employee_number
   AND    nvl(business_group_id, p_bus_group_id) = p_bus_group_id
   AND    ben_ext_person.g_effective_Date BETWEEN effective_start_date AND effective_end_Date;

Line: 211

   SELECT consolidation_set_id
   FROM   pay_consolidation_sets
   WHERE  consolidation_Set_name = l_consolidation_set
   AND    nvl(business_group_id, p_bus_group_id) = p_bus_group_id;

Line: 251

   SELECT assignment_set_id
   FROM   hr_assignment_sets
   WHERE  assignment_set_name = l_assignment_set
   AND    nvl(business_group_id, p_bus_group_id) = p_bus_group_id;

Line: 293

    SELECT greatest(min(paf.effective_start_date),l_ext_start_date)
    FROM per_all_assignments_f paf
    WHERE paf.assignment_id = p_assignment_id;

Line: 328

    select least(max(paf.effective_end_date),l_ext_end_date)
    from per_all_assignments_f paf,
         per_assignment_status_types past
    where paf.assignment_id = p_assignment_id
    and   past.per_system_status = 'ACTIVE_ASSIGN'
    and   paf.assignment_status_type_id = past.assignment_status_type_id;

Line: 366

   SELECT greatest(effective_start_date, l_ext_start_date) effective_start_date
   FROM   pqp_assignment_attributes_f
   WHERE  assignment_id = p_assignment_id
   AND    effective_start_date <= l_ext_end_date
   AND    effective_end_date >= l_ext_start_date
   AND    primary_company_car is NOT NULL
   ORDER BY effective_start_date ;

Line: 403

   SELECT effective_start_date, effective_end_date,
          primary_car_fuel_benefit, primary_company_car
   FROM   pqp_assignment_attributes_f
   WHERE  assignment_id = p_assignment_id
   AND    effective_start_date <= l_ext_end_date
   AND    effective_end_date >= l_ext_start_date
   AND    primary_company_car is NOT NULL
   ORDER BY effective_start_date ;

Line: 470

   SELECT pvd.vehicle_identification_number
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 505

   SELECT pvd.make
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 539

   SELECT pvd.model
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 573

   SELECT pvd.date_first_registered
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 607

   SELECT decode(nvl(pvd.market_value_classic_car,0), 0, list_price, pvd.market_value_classic_car)
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 644

   SELECT hl.description --pvd.fuel_type -- Bug 5017957
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd,
          hr_lookups  hl
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date AND  paaf.effective_end_date
   AND    paaf.assignment_id = p_assignment_id -- comes from context
   AND    pvd.vehicle_details_id = paaf.primary_company_car
   AND    hl.lookup_type = 'PQP_FUEL_TYPE'
   AND    hl.enabled_flag = 'Y'
   AND    hl.lookup_code = pvd.fuel_type
   AND    trunc(sysdate)  BETWEEN trunc(nvl(hl.start_date_active, sysdate-1)) AND trunc(nvl(hl.end_date_active, sysdate+1));

Line: 657

   SELECT Meaning
   FROM   hr_lookups -- Bug fix 3799560
   WHERE  lookup_type = 'GB_FUEL_TYPE'
   AND    trunc(sysdate)  BETWEEN trunc(nvl(start_date_active, sysdate-1)) AND trunc(nvl(end_date_active, sysdate+1))
   AND    enabled_flag = 'Y'
   AND    description = l_fuel_type
   ORDER BY lookup_code;

Line: 696

   SELECT pvd.co2_emissions
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 731

   SELECT decode(nvl(pvd.market_value_classic_car,0), 0, pvd.accessory_value_at_startdate, 0)
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 765

   SELECT pvd.accessory_value_added_later
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 799

   SELECT primary_capital_contribution
   FROM   pqp_assignment_attributes_f
   WHERE  l_ben_start_date BETWEEN effective_start_date
          AND effective_end_date
   AND assignment_id = p_assignment_id; -- comes from context

Line: 832

   SELECT primary_private_contribution
   FROM   pqp_assignment_attributes_f
   WHERE  l_ben_start_date BETWEEN effective_start_date
          AND effective_end_date
   AND assignment_id = p_assignment_id; -- comes from context

Line: 865

   SELECT pvd.engine_capacity_in_cc
   FROM   pqp_assignment_attributes_f paaf,
          pqp_vehicle_details pvd
   WHERE  l_ben_start_date BETWEEN paaf.effective_start_date
          AND  paaf.effective_end_date
   AND paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_details_id = paaf.primary_company_car;

Line: 900

   SELECT substr(primary_car_fuel_benefit, 1, 1)
   FROM   pqp_assignment_attributes_f
   WHERE  l_ben_start_date BETWEEN effective_start_date
          AND effective_end_date
   AND assignment_id = p_assignment_id; -- comes from context

Line: 933

   SELECT primary_company_car
   FROM   pqp_assignment_attributes_f
   WHERE  l_ben_start_date BETWEEN effective_start_date
          AND effective_end_date
   AND assignment_id = p_assignment_id; -- comes from context

Line: 977

   SELECT min(effective_start_date) min_start_date, max(effective_end_date) max_end_Date
   FROM   per_all_assignments_f
   WHERE  assignment_id = p_asg_id;

Line: 985

   SELECT pp.payroll_id, asg.person_id, pp.consolidation_set_id, flex.segment1 tax_dist
   FROM   pay_all_payrolls_f pp,
          per_all_assignments_f asg,
          hr_soft_coding_keyflex flex
   WHERE  asg.assignment_id = p_asg_id
   AND    asg.payroll_id = pp.payroll_id
   AND    ben_ext_person.g_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
   AND    pp.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
   AND    asg.effective_start_date < l_ext_end_date
   AND    asg.effective_end_Date > l_ext_start_date;

Line: 997

   SELECT 'Y' include_flag
   FROM   hr_assignment_set_amendments hasa,
          hr_assignment_sets has,
          per_all_assignments_f paaf
   WHERE  has.assignment_set_id = l_ext_asg_set_id
   AND    paaf.assignment_id = p_asg_id
--   AND    ben_ext_person.g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
   AND    has.assignment_set_id = hasa.assignment_set_id (+)
   AND    NVL (hasa.assignment_id, paaf.assignment_id) = paaf.assignment_id
   AND    NVL (hasa.include_or_exclude, 'I') = 'I'
   AND    NVL (has.payroll_id, paaf.payroll_id) = paaf.payroll_id;

Line: 1010

   SELECT 'Y' vehicle_exist_flag
   FROM   pqp_assignment_attributes_f paa
   WHERE  paa.assignment_id = p_asg_id
   AND    paa.effective_start_date <= l_ext_end_date
   AND    paa.effective_end_date   >= l_ext_start_date
   AND    (paa.primary_company_car IS NOT NULL OR paa.secondary_company_car IS NOT NULL)
   AND    l_ext_start_date < fnd_date.canonical_to_date('2003/04/06')    -- BUG 3431106 Using canonical_to_date function
   UNION
   SELECT 'Y' vehicle_exist_flag
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE  paaf.assignment_id = p_asg_id
   AND    paaf.effective_start_date <= l_ext_end_date
   AND    paaf.effective_end_date   >= l_ext_start_date
   AND    paaf.vehicle_repository_id IS NOT NULL
   AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND    pvd.vehicle_type = 'C'
   AND    pvd.vehicle_ownership = 'C'
   AND    l_ext_start_date >= fnd_date.canonical_to_date('2003/04/06');   -- BUG 3431106 Using canonical_to_date function

Line: 1113

   SELECT distinct paa1.assignment_id
   FROM   per_all_assignments_f paa1, per_all_assignments_f paa2
   WHERE  paa2.assignment_id = p_assignment_id
   AND    paa2.person_id = paa1.person_id
   ORDER BY paa1.assignment_id;

Line: 1151

   SELECT pvd.vehicle_identification_number,
          pvd.make,
          pvd.model,
          pvd.date_first_registered,
          decode(nvl(pvd.market_value_classic_car,0), 0, list_price, pvd.market_value_classic_car) price,
          h1.meaning trans_fuel_type,
          pvd.co2_emissions,
          decode(nvl(pvd.market_value_classic_car,0), 0, pvd.accessory_value_at_startdate, 0) optional_accessory,
          pvd.accessory_value_added_later,
          pvd.engine_capacity_in_cc
   FROM   pqp_vehicle_details pvd,
          hr_lookups  h1,  -- Bug fix 3799560
          hr_lookups  h2
   WHERE  pvd.vehicle_details_id = p_vehicle_details_id
   AND    pvd.fuel_type = h2.lookup_code
   AND    h2.lookup_type = 'PQP_FUEL_TYPE'
   AND    h2.enabled_flag = 'Y'
   AND    trunc(sysdate) BETWEEN trunc(nvl(h2.start_date_active, sysdate-1)) AND trunc(nvl(h2.end_date_active,sysdate+1))
   AND    h2.description = h1.description
   AND    h1.lookup_type = 'GB_FUEL_TYPE'
   AND    trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
   AND    h1.enabled_flag = 'Y';

Line: 1175

   SELECT primary_capital_contribution,
          primary_private_contribution
   FROM   pqp_assignment_attributes_f
   WHERE  assignment_id = p_asg_id
   AND    p_benefit_start_date BETWEEN effective_start_date AND effective_end_date;

Line: 1188

   SELECT ext_rslt_dtl_id
   FROM   ben_ext_rslt_dtl
   WHERE  ext_rslt_id = g_ext_rslt_id
   AND    person_id = g_person_id
   AND    ext_rcd_id = g_veh_rcd_id
   AND    val_02 = to_char(p_asg_id)
   AND    val_04 = to_char(p_benefit_end_date, 'DD-MON-YYYY')
   AND    val_09 = to_char(p_benefit_start_Date, 'DD-MON-YYYY')
   AND    val_10 = to_char(p_benefit_end_date, 'DD-MON-YYYY')
   AND    val_29 = to_char(p_vehicle_details_id);

Line: 1228

         hr_utility.trace('CREATE_EXT_RSLT_DTL: Insert result details.');

Line: 1290

   SELECT greatest(effective_start_date, l_ext_start_date) effective_start_date,
          least(effective_end_date, l_ext_end_date) effective_end_date,
          primary_car_fuel_benefit, primary_company_car
   FROM   pqp_assignment_attributes_f
   WHERE  assignment_id = p_asg_id
   AND    effective_start_date <= l_ext_end_date
   AND    effective_end_date >= l_ext_start_date
   AND    primary_company_car is NOT NULL
   ORDER BY effective_start_date ;

Line: 1389

   SELECT greatest(effective_start_date, l_ext_start_date) effective_start_date,
          least(effective_end_date, l_ext_end_date) effective_end_date,
          secondary_car_fuel_benefit, secondary_company_car
   FROM   pqp_assignment_attributes_f
   WHERE  assignment_id = p_asg_id
   AND    effective_start_date <= l_ext_end_date
   AND    effective_end_date >= l_ext_start_date
   AND    secondary_company_car is NOT NULL
   ORDER BY effective_start_date ;

Line: 1481

   SELECT distinct paa1.assignment_id
   FROM   per_all_assignments_f paa1, per_all_assignments_f paa2
   WHERE  paa2.assignment_id = p_asg_id
   AND    paa2.person_id = paa1.person_id
   AND    nvl(paa1.primary_flag, 'N') = 'N';

Line: 1516

   SELECT distinct paa1.assignment_id
   FROM   per_all_assignments_f paa1, per_all_assignments_f paa2
   WHERE  paa2.assignment_id = p_asg_id
   AND    paa2.person_id = paa1.person_id
   AND    paa1.effective_end_date < paa2.effective_start_date
   AND    nvl(paa1.primary_flag, 'Y') = 'Y';

Line: 1552

   SELECT rcd.ext_rcd_id
   FROM   ben_ext_rcd rcd,
          ben_ext_rcd_in_file rif,
          ben_ext_dfn dfn,
          ben_ext_rslt rslt
   WHERE  rslt.ext_rslt_id = p_ext_rslt_id
   AND    rslt.ext_dfn_id = dfn.ext_dfn_id
   AND    dfn.ext_file_id = rif.ext_file_id
   AND    rif.ext_rcd_id = rcd.ext_rcd_id
   AND    rcd.name like '%PAY GB P11D Car Extract 2003 - Assignment Details Record';

Line: 1566

   SELECT rcd.ext_rcd_id
   FROM   ben_ext_rcd rcd,
          ben_ext_rcd_in_file rif,
          ben_ext_dfn dfn,
          ben_ext_rslt rslt
   WHERE  rslt.ext_rslt_id = p_ext_rslt_id
   AND    rslt.ext_dfn_id = dfn.ext_dfn_id
   AND    dfn.ext_file_id = rif.ext_file_id
   AND    rif.ext_rcd_id = rcd.ext_rcd_id
   AND    rcd.name like '%PAY GB P11D Car Extract 2003 - Vehicle Details Record';

Line: 1579

   SELECT person_id, val_01 asg_id, ext_rslt_dtl_id, object_version_number
   FROM   ben_ext_rslt_dtl
   WHERE  ext_rslt_id = p_ext_rslt_id
   AND    ext_rcd_id = l_asg_rcd_id;

Line: 1587

   SELECT *
   FROM   ben_ext_rslt_dtl
   WHERE  ext_rslt_id = p_ext_rslt_id
   AND    ext_rcd_id = g_veh_rcd_id
   AND    person_id = p_person_id;

Line: 1632

         ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_prim_veh_dtl.ext_rslt_dtl_id,
                                                  p_object_version_number => l_prim_veh_dtl.object_version_number);

Line: 1638

         ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_prim_veh_dtl.ext_rslt_dtl_id,
                                                  p_object_version_number => l_prim_veh_dtl.object_version_number);

Line: 1653

      ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => ext_asg_rec.ext_rslt_dtl_id,
                                               p_object_version_number => l_obj_no);

Line: 1676

   SELECT greatest(paaf.effective_start_date, l_ext_start_date) effective_start_date
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE  paaf.assignment_id = p_assignment_id
   AND    paaf.effective_start_date <= l_ext_end_date
   AND    paaf.effective_end_date >= l_ext_start_date
   AND    paaf.vehicle_repository_id is NOT NULL
   AND    paaf.usage_type = 'P'
   AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND    pvd.vehicle_type = 'C'
   AND    pvd.vehicle_ownership = 'C'
   ORDER BY paaf.effective_start_date ;

Line: 1720

   SELECT paaf.effective_end_date effective_end_date
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE  paaf.assignment_id = p_assignment_id
   AND    paaf.effective_start_date <= l_ext_end_date
   AND    paaf.effective_end_date >= l_ext_start_date
   AND    paaf.vehicle_repository_id is NOT NULL
   AND    paaf.usage_type = 'P'
   AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND    pvd.vehicle_type = 'C'
   AND    pvd.vehicle_ownership = 'C'
   ORDER BY paaf.effective_end_date desc;

Line: 1766

   SELECT pvd.registration_number
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 1809

   SELECT pvd.make
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 1851

   SELECT pvd.model
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 1893

   SELECT pvd.initial_registration
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 1935

   SELECT decode(nvl(pvd.market_value_classic_car,0), 0, list_price, pvd.market_value_classic_car)+ nvl(pvd.accessory_value_at_startdate,0)
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 1980

   SELECT hl.description -- pvd.fuel_type
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd,
          hr_lookups  hl
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   AND hl.lookup_type = 'PQP_FUEL_TYPE'
   AND hl.enabled_flag = 'Y'
   AND hl.lookup_code = pvd.fuel_type
   AND trunc(sysdate)  BETWEEN trunc(nvl(hl.start_date_active, sysdate-1)) AND trunc(nvl(hl.end_date_active, sysdate+1))
   ORDER BY pvd.effective_end_date desc;

Line: 1998

   SELECT Meaning
   FROM   hr_lookups --Bug fix 3799560
   WHERE  lookup_type = 'GB_FUEL_TYPE'
   AND    trunc(sysdate)  BETWEEN trunc(nvl(start_date_active, sysdate-1)) AND trunc(nvl(end_date_active, sysdate+1))
   AND    enabled_flag = 'Y'
   AND    description = l_fuel_type
   ORDER BY lookup_code;

Line: 2041

   SELECT pvd.fiscal_ratings
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 2086

   SELECT decode(nvl(pvd.market_value_classic_car,0), 0, pvd.accessory_value_added_later, 0)
   --bug 7306948 end
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 2129

   SELECT paaf.capital_contribution
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND paaf.usage_type = 'P'
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.effective_start_date <= l_ext_end_date
   AND paaf.effective_end_date >= l_ext_start_date
   order by paaf.effective_end_date desc;

Line: 2172

   SELECT paaf.private_contribution
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND paaf.usage_type = 'P'
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.effective_start_date <= l_ext_end_date
   AND paaf.effective_end_date >= l_ext_start_date
   ORDER BY paaf.effective_end_date desc;

Line: 2214

   SELECT pvd.engine_capacity_in_cc
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id -- comes from context
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.usage_type = 'P'
   AND pvd.effective_start_date <= l_ext_end_date
   AND pvd.effective_end_date >= l_ext_start_date
   ORDER BY pvd.effective_end_date desc;

Line: 2256

   SELECT substr(paaf.fuel_benefit, 1, 1)
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE paaf.assignment_id = p_assignment_id  -- comes from context
   AND paaf.usage_type = 'P'
   AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND pvd.vehicle_type = 'C'
   AND pvd.vehicle_ownership = 'C'
   AND paaf.effective_start_date <= l_ext_end_date
   AND paaf.effective_end_date >= l_ext_start_date
   ORDER BY paaf.effective_end_date desc;

Line: 2297

      SELECT substr(paaf.fuel_benefit,1,1) fuel_benefit
      FROM   pqp_vehicle_allocations_f paaf,
             pqp_vehicle_repository_f pvd
      WHERE  paaf.assignment_id = p_assignment_id -- comes from context
      AND    paaf.usage_type = 'P'
      AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
      AND    pvd.vehicle_type = 'C'
      AND    pvd.vehicle_ownership = 'C'
      AND    paaf.effective_start_date <= l_ext_end_date
      AND    paaf.effective_end_date >= l_ext_start_date
      ORDER BY paaf.effective_end_date desc;

Line: 2349

      SELECT substr(paaf.fuel_benefit,1,1)fuel_benefit,paaf.effective_end_date effective_end_date
      FROM   pqp_vehicle_allocations_f paaf,
             pqp_vehicle_repository_f pvd
      WHERE  paaf.assignment_id = p_assignment_id -- comes from context
      AND    paaf.usage_type = 'P'
      AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
      AND    pvd.vehicle_type = 'C'
      AND    pvd.vehicle_ownership = 'C'
      AND    paaf.effective_start_date <= l_ext_end_date
      AND    paaf.effective_end_date >= l_ext_start_date
      ORDER BY paaf.effective_end_date desc;

Line: 2403

         SELECT paaf.fuel_benefit
         FROM   pqp_vehicle_allocations_f paaf,
                pqp_vehicle_repository_f pvd
         WHERE  paaf.assignment_id = p_assignment_id -- comes from context
         AND    paaf.usage_type = 'P'
         AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
         AND    pvd.vehicle_type = 'C'
         AND    pvd.vehicle_ownership = 'C'
         AND    paaf.effective_end_date <=l_start_date
         AND    paaf.effective_end_date >= l_ext_start_date
         AND    paaf.effective_start_date <= l_ext_end_date
         order by paaf.effective_start_date desc ;

Line: 2459

      SELECT substr(paaf.fuel_benefit,1,1) fuel_benefit
      FROM   pqp_vehicle_allocations_f paaf,
             pqp_vehicle_repository_f pvd
      WHERE  paaf.assignment_id = p_assignment_id -- comes from context
      AND    paaf.usage_type = 'P'
      AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
      AND    pvd.vehicle_type = 'C'
      AND    pvd.vehicle_ownership = 'C'
      AND    paaf.effective_start_date <= l_ext_end_date
      AND    paaf.effective_end_date >= l_ext_start_date
      AND    paaf.vehicle_allocation_id = p_vehicle_allocation_id
      ORDER BY paaf.effective_end_date desc;

Line: 2513

      SELECT substr(paaf.fuel_benefit,1,1)fuel_benefit,paaf.effective_end_date effective_end_date
      FROM   pqp_vehicle_allocations_f paaf,
             pqp_vehicle_repository_f pvd
      WHERE  paaf.assignment_id = p_assignment_id -- comes from context
      AND    paaf.usage_type = 'P'
      AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
      AND    pvd.vehicle_type = 'C'
      AND    pvd.vehicle_ownership = 'C'
      AND    paaf.effective_start_date <= l_ext_end_date
      AND    paaf.effective_end_date >= l_ext_start_date
      AND    paaf.vehicle_allocation_id = p_vehicle_allocation_id
      ORDER BY paaf.effective_end_date desc;

Line: 2569

         SELECT paaf.fuel_benefit
         FROM   pqp_vehicle_allocations_f paaf,
                pqp_vehicle_repository_f pvd
         WHERE  paaf.assignment_id = p_assignment_id -- comes from context
         AND    paaf.usage_type = 'P'
         AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
         AND    pvd.vehicle_type = 'C'
         AND    pvd.vehicle_ownership = 'C'
         AND    paaf.effective_end_date <=l_start_date
         AND    paaf.effective_end_date >= l_ext_start_date
         AND    paaf.effective_start_date <= l_ext_end_date
	 AND    paaf.vehicle_allocation_id = p_vehicle_allocation_id
         order by paaf.effective_start_date desc ;

Line: 2630

   SELECT registration_number,
          make,
          model,
          initial_registration,
          decode(nvl(market_value_classic_car,0), 0, list_price, market_value_classic_car) + nvl(accessory_value_at_startdate, 0) price,
          h2.meaning trans_fuel_type,
          fiscal_ratings,
	  --bug 7306948 begin
          --old line--decode(nvl(market_value_classic_car,0), 0, accessory_value_at_startdate, 0) optional_accessory,
	  decode(nvl(market_value_classic_car,0), 0, accessory_value_added_later, 0) optional_accessory,
	  --bug 7306948 end
          accessory_value_added_later,
          engine_capacity_in_cc
   FROM   pqp_vehicle_repository_f pvd,
          hr_lookups h1, --Bug fix 3799560
          hr_lookups h2
   WHERE  vehicle_repository_id = p_vehicle_repository_id
   AND    fuel_type = h1.lookup_code
   AND    h1.lookup_type = 'PQP_FUEL_TYPE'
   AND    h1.enabled_flag = 'Y'
   AND    trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
   AND    h1.description = h2.description
   AND    h2.lookup_type = 'GB_FUEL_TYPE'
   AND    trunc(sysdate) BETWEEN trunc(nvl(h2.start_date_active, sysdate-1)) AND trunc(nvl(h2.end_date_active,sysdate+1))
   AND    h2.enabled_flag = 'Y'
   AND    effective_start_date <= p_benefit_end_date
   AND    effective_end_date >=   p_benefit_start_date
   order by effective_end_date desc;

Line: 2660

   SELECT substr(fuel_benefit,1,1)fuel_benefit
   FROM   pqp_vehicle_allocations_f
   WHERE  assignment_id = p_asg_id
   AND    effective_start_date <= p_benefit_end_date
   AND    effective_end_date >=  p_benefit_start_date;

Line: 2667

   SELECT capital_contribution,
          private_contribution,
	  substr(fuel_benefit,1,1)fuel_benefit
   FROM   pqp_vehicle_allocations_f
   WHERE  assignment_id = p_asg_id
   AND    effective_start_date <= p_benefit_end_date
   AND    effective_end_date >=  p_benefit_start_date
   order by effective_end_date desc;

Line: 2683

   SELECT ext_rslt_dtl_id
   FROM   ben_ext_rslt_dtl
   WHERE  ext_rslt_id = g_ext_rslt_id
   AND    person_id = g_person_id
   AND    ext_rcd_id = g_veh_rcd_id
   AND    val_02 = to_char(p_asg_id)
   AND    val_04 = to_char(p_benefit_end_date, 'DD-MON-YYYY')
   AND    val_09 = to_char(p_benefit_start_Date, 'DD-MON-YYYY')
   AND    val_10 = to_char(p_benefit_end_date, 'DD-MON-YYYY')
   AND    val_29 = to_char(p_vehicle_repository_id);

Line: 2742

         hr_utility.trace('CREATE_EXT_RSLT_DTL_04: Insert result details.');

Line: 2809

   SELECT greatest(paaf.effective_start_date, l_ext_start_date) effective_start_date,
          least(paaf.effective_end_date, l_ext_end_date) effective_end_date,
          paaf.fuel_benefit fuel_benefit,paaf.vehicle_repository_id vehicle_repository_id,paaf.vehicle_allocation_id vehicle_allocation_id
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE  paaf.assignment_id = p_asg_id
   AND    paaf.effective_start_date <= l_ext_end_date
   AND    paaf.effective_end_date >= l_ext_start_date
   AND    paaf.vehicle_repository_id is NOT NULL
   AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND    pvd.vehicle_type = 'C'
   AND    pvd.vehicle_ownership = 'C'
   AND    paaf.usage_type = 'S'
   ORDER BY paaf.effective_start_date ;

Line: 2836

   SELECT  'Y' flag
   FROM    pqp_vehicle_allocations_f pvaf
   WHERE   pvaf.assignment_id = p_asg_id
   AND     pvaf.across_assignments = 'Y'
   AND     pvaf.usage_type = 'S'
   AND   not exists  (
        SELECT  1
        FROM    per_all_assignments_f paaf
        WHERE   paaf.assignment_id = pvaf.assignment_id
        AND    paaf.effective_start_date <= l_ext_end_date
        AND    paaf.effective_end_date >= l_ext_start_date
        AND paaf.primary_flag = 'Y'
        );

Line: 2952

   SELECT greatest(paaf.effective_start_date, l_ext_start_date) effective_start_date,
          least(paaf.effective_end_date, l_ext_end_date) effective_end_date,
          paaf.fuel_benefit fuel_benefit,paaf.vehicle_repository_id vehicle_repository_id, paaf.vehicle_allocation_id vehicle_allocation_id
   FROM   pqp_vehicle_allocations_f paaf,
          pqp_vehicle_repository_f pvd
   WHERE  paaf.assignment_id = p_asg_id
   AND    paaf.effective_start_date <= l_ext_end_date
   AND    paaf.effective_end_date >= l_ext_start_date
   AND    paaf.vehicle_repository_id is NOT NULL
   AND    pvd.vehicle_repository_id = paaf.vehicle_repository_id
   AND    pvd.vehicle_type = 'C'
   AND    pvd.vehicle_ownership = 'C'
   AND    paaf.usage_type = 'P'
   ORDER BY paaf.effective_start_date ;

Line: 2978

   SELECT  'Y' flag
   FROM    pqp_vehicle_allocations_f pvaf
   WHERE   pvaf.assignment_id = p_asg_id
   AND     pvaf.across_assignments = 'Y'
   AND     pvaf.usage_type = 'P'
   AND   not exists  (
        SELECT  1
        FROM    per_all_assignments_f paaf
        WHERE   paaf.assignment_id = pvaf.assignment_id
        AND    paaf.effective_start_date <= l_ext_end_date
        AND    paaf.effective_end_date >= l_ext_start_date
        AND paaf.primary_flag = 'Y'
        );

Line: 3089

   SELECT distinct paa1.assignment_id
   FROM   per_all_assignments_f paa1, per_all_assignments_f paa2
   WHERE  paa2.assignment_id = p_asg_id
   AND    paa2.assignment_id <> paa1.assignment_id
   AND    paa2.person_id = paa1.person_id
   AND    nvl(paa1.primary_flag, 'N') = 'N';

Line: 3124

   SELECT distinct paa1.assignment_id
   FROM   per_all_assignments_f paa1, per_all_assignments_f paa2
   WHERE  paa2.assignment_id = p_asg_id
   AND    paa2.assignment_id <> paa1.assignment_id
   AND    paa2.person_id = paa1.person_id
   AND    paa1.effective_end_date < paa2.effective_start_date
   AND    nvl(paa1.primary_flag, 'Y') = 'Y';

Line: 3137

   SELECT distinct paa1.assignment_id
   FROM   per_all_assignments_f paa1, per_all_assignments_f paa2
   WHERE  paa2.assignment_id = p_asg_id
   AND    paa2.assignment_id <> paa1.assignment_id
   AND    paa2.person_id = paa1.person_id
   AND    nvl(paa1.primary_flag, 'N') = 'N';

Line: 3189

   SELECT rcd.ext_rcd_id
   FROM   ben_ext_rcd rcd,
          ben_ext_rcd_in_file rif,
          ben_ext_dfn dfn,
          ben_ext_rslt rslt
   WHERE  rslt.ext_rslt_id = p_ext_rslt_id
   AND    rslt.ext_dfn_id = dfn.ext_dfn_id
   AND    dfn.ext_file_id = rif.ext_file_id
   AND    rif.ext_rcd_id = rcd.ext_rcd_id
   AND    rcd.name like '%PAY GB P11D Car Extract - Assignment Details Record';

Line: 3203

   SELECT rcd.ext_rcd_id
   FROM   ben_ext_rcd rcd,
          ben_ext_rcd_in_file rif,
          ben_ext_dfn dfn,
          ben_ext_rslt rslt
   WHERE  rslt.ext_rslt_id = p_ext_rslt_id
   AND    rslt.ext_dfn_id = dfn.ext_dfn_id
   AND    dfn.ext_file_id = rif.ext_file_id
   AND    rif.ext_rcd_id = rcd.ext_rcd_id
   AND    rcd.name like '%PAY GB P11D Car Extract - Vehicle Details Record';

Line: 3216

   SELECT person_id, val_01 asg_id, ext_rslt_dtl_id, object_version_number
   FROM   ben_ext_rslt_dtl
   WHERE  ext_rslt_id = p_ext_rslt_id
   AND    ext_rcd_id = l_asg_rcd_id;

Line: 3224

   SELECT *
   FROM   ben_ext_rslt_dtl
   WHERE  ext_rslt_id = p_ext_rslt_id
   AND    ext_rcd_id = g_veh_rcd_id
   AND    person_id = p_person_id;

Line: 3270

         ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_prim_veh_dtl.ext_rslt_dtl_id,
                                                  p_object_version_number => l_prim_veh_dtl.object_version_number);

Line: 3277

         ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_prim_veh_dtl.ext_rslt_dtl_id,
                                                  p_object_version_number => l_prim_veh_dtl.object_version_number);

Line: 3287

      ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => ext_asg_rec.ext_rslt_dtl_id,
                                               p_object_version_number => l_obj_no);