APPS.AP_WEB_DB_SCHLINE_PKG SQL Statements

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

Line: 23

  SELECT /*+ ordered */
         SL.RANGE_HIGH,
	 nvl(SL.RANGE_LOW, 0),
	 SP.START_DATE,
	 SP.END_DATE,
         SL.rate,
         decode(SH.passengers_flag,null,0,decode(calculation_method, 'AMOUNT', NVL(SL.rate_per_passenger,0), 'PERCENT', NVL(SL.rate_per_passenger,0)/100* SL.rate, 0)) rate_per_passenger
  FROM   AP_POL_HEADERS SH,
	 AP_POL_SCHEDULE_PERIODS SP,
         AP_POL_LINES SL
  WHERE  SH.POLICY_ID = p_policy_id
  AND	 SH.POLICY_ID = SL.POLICY_ID
  AND	 nvl(SL.ROLE_ID,0) = getRoleId(p_policy_id,p_employee_id,p_start_expense_date)
  AND    nvl(SL.VEHICLE_CATEGORY,0) = decode(
	    SH.VEHICLE_CATEGORY_FLAG,'Y', p_vehicle_category_code, nvl(SL.VEHICLE_CATEGORY,0))
  AND    nvl(SL.VEHICLE_TYPE,0) = decode(SH.VEHICLE_TYPE_FLAG, 'Y', decode(SH.VEHICLE_CATEGORY_FLAG,'Y', decode (
	    getRequiredOptionCode(p_policy_id,p_vehicle_category_code,'VEHICLE_TYPE'), 'N', nvl(SL.VEHICLE_TYPE,0), p_vehicle_type), p_vehicle_type), nvl(SL.VEHICLE_TYPE,0))
  AND	 nvl(SL.FUEL_TYPE,0) = decode(SH.FUEL_TYPE_FLAG, 'Y', decode(SH.VEHICLE_CATEGORY_FLAG,'Y', decode (
	    getRequiredOptionCode(p_policy_id,p_vehicle_category_code,'FUEL_TYPE'), 'N', nvl(SL.FUEL_TYPE,0) ,p_fuel_type), p_fuel_type), nvl(SL.FUEL_TYPE,0))
  AND	 SL.CURRENCY_CODE = decode(
	    SH.CURRENCY_PREFERENCE, 'MRC', p_currency_code, SL.CURRENCY_CODE)
  AND	 SL.STATUS = 'ACTIVE'
  AND    SL.SCHEDULE_PERIOD_ID = SP.SCHEDULE_PERIOD_ID
  AND    SP.POLICY_ID = SH.POLICY_ID
  AND    SP.START_DATE <= p_start_expense_date
  AND    nvl(SP.END_DATE, p_start_expense_date) >= p_start_expense_date
  AND    SL.ADDON_MILEAGE_RATE_CODE is null
  AND	 SL.PARENT_LINE_ID is null
  ORDER BY SL.RANGE_LOW;

Line: 73

  SELECT distance_uom,
	 currency_code,
	 distance_thresholds_flag
  INTO	 p_sh_distance_uom,
	 p_sh_currency_code,
	 p_sh_distance_thresholds_flag
  FROM	 AP_POL_HEADERS
  WHERE	 policy_id = p_policy_id;

Line: 104

  SELECT decode(SH.EMPLOYEE_ROLE_FLAG,'Y',
		(decode(SH.ROLE_CODE,'JOB_GROUP', PF.job_id,'POSITION',PF.position_id,'GRADE',PF.grade_id,0)),0) INTO l_role_code
  FROM
    AP_POL_HEADERS SH,
    (SELECT EMP.ASSIGNMENT_ID ,EMP.EMPLOYEE_ID
      FROM PER_EMPLOYEES_X EMP
      WHERE NOT AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(EMP.EMPLOYEE_ID)= 'Y'
      AND EMP.EMPLOYEE_ID = p_employee_id
    UNION
      SELECT  PCW.ASSIGNMENT_ID , PCW.PERSON_ID
      FROM  PER_CONT_WORKERS_CURRENT_X PCW
      WHERE PCW.PERSON_ID = p_employee_id)  EMPLOYEE,
    PER_ALL_ASSIGNMENTS_F PF
  WHERE
    PF.ASSIGNMENT_ID = EMPLOYEE.ASSIGNMENT_ID
    AND    PF.EFFECTIVE_START_DATE <=  p_start_expense_date
    AND    PF.EFFECTIVE_END_DATE >= p_start_expense_date
    AND    EMPLOYEE.EMPLOYEE_ID = p_employee_id
    AND    SH.POLICY_ID = p_policy_id
    AND    ROWNUM = 1;

Line: 127

	  SELECT ROLE_ID INTO l_role_code FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id and ROLE_ID = l_role_code and rownum = 1;

Line: 151

  select VEHICLE_TYPE_CODE, FUEL_TYPE_CODE
  into   l_vehicle_type_code,l_fuel_type_code
  from   AP_POL_SCHEDULE_OPTIONS pso
  where
              POLICY_ID = p_policy_id
       and    OPTION_TYPE = 'OIE_VEHICLE_CATEGORY'
       and    OPTION_CODE IS NOT NULL
       and    nvl(END_DATE, SYSDATE+1) > SYSDATE
       and    option_code = p_vehicle_category;