3 Replies Latest reply: Oct 31, 2009 12:10 PM by abowden RSS

    PNP_UTIL_FUNC.GET_AREA Invalid number or type of arguments error. Help!!!

    649467
      Hi,

      The Package Spec and Body of the Application Standard package are as below:

      CREATE OR REPLACE PACKAGE apps.pnp_util_func
      AUTHID CURRENT_USER
      AS
      /*** Other types and PL/SQL Objects which are not relevant to GET_AREA procedure are excluded***/
      TYPE pn_location_area_rec
      IS
      RECORD (
      assignable_area number
      , rentable_area number
      , usable_area number
      , common_area number
      , secondary_area number
      , max_capacity number
      , optimum_capacity number
      );

      TYPE pn_space_area_rec
      IS
      RECORD (
      allocated_area number
      , allocated_area_emp number
      , allocated_area_cust number
      , utilizedcapacityemp number
      , utilizedcapacitycust number
      , utilizedcapacity number
      , occupancy_percent number
      , vacant_area number
      , vacant_area_percent number
      , vacancy number
      , area_utilized number
      );

      PROCEDURE get_area (p_location_id IN number
      , p_location_type IN varchar2
      , p_area_type IN varchar2 DEFAULT NULL
      , p_as_of_date IN date DEFAULT NULL
      , p_loc_area OUT NOCOPY pn_location_area_rec
      , p_space_area OUT NOCOPY pn_space_area_rec);
      END;

      CREATE OR REPLACE PACKAGE BODY apps.pnp_util_func
      AS
      PROCEDURE get_area (p_location_id IN number
      , p_location_type IN varchar2
      , p_area_type IN varchar2
      , p_as_of_date IN date
      , p_loc_area OUT NOCOPY pn_location_area_rec
      , p_space_area OUT NOCOPY pn_space_area_rec)
      IS
      l_location_type_lookup_code pn_locations.location_type_lookup_code%TYPE;
      l_assignable_area number := 0;
      l_rentable_area number := 0;
      l_usable_area number := 0;
      l_common_area number := 0;
      l_secondary_area number := 0;
      l_max_capacity number := 0;
      l_optimum_capacity number := 0;
      l_allocated_area number := 0;
      l_allocated_area_emp number := 0;
      l_allocated_area_cust number := 0;
      l_utilizedcapacitycust number := 0;
      l_utilizedcapacityemp number := 0;
      l_utilizedcapacity number := 0;
      l_occupancy_percent number := 0;
      l_vacant_area number := 0;
      l_vacant_area_percent number := 0;
      l_vacancy number := 0;
      l_area_utilized number := 0;
      l_date date
      := TO_DATE ('31/12/4712'
      , 'DD/MM/YYYY') ;
      l_as_of_date date := pnp_util_func.get_as_of_date (p_as_of_date);

      CURSOR c_loc
      IS
      SELECT NVL (SUM (assignable_area), 0)
      , NVL (SUM (rentable_area), 0)
      , NVL (SUM (usable_area), 0)
      , NVL (SUM (common_area), 0)
      , NVL (SUM (max_capacity), 0)
      , NVL (SUM (optimum_capacity), 0)
      FROM pn_locations_all
      WHERE location_type_lookup_code = l_location_type_lookup_code
      AND status = 'A'
      AND l_as_of_date BETWEEN active_start_date
      AND active_end_date
      START WITH location_id = p_location_id
      CONNECT BY PRIOR location_id = parent_location_id
      AND l_as_of_date BETWEEN PRIOR active_start_date
      AND --ASHISH
      PRIOR active_end_date;

      CURSOR c_space_emp
      IS
      SELECT NVL (SUM (allocated_area), 0)
      , NVL (SUM (utilized_area), 0)
      FROM pn_space_assign_emp_all
      WHERE emp_assign_start_date <= l_as_of_date
      AND NVL (emp_assign_end_date, l_date) >= l_as_of_date
      AND location_id IN
      (SELECT location_id
      FROM pn_locations_all
      WHERE location_type_lookup_code =
      l_location_type_lookup_code
      AND status = 'A'
      AND l_as_of_date BETWEEN active_start_date
      AND active_end_date
      START WITH location_id = p_location_id
      CONNECT BY PRIOR location_id = parent_location_id
      AND l_as_of_date BETWEEN PRIOR active_start_date
      AND --ASHISH
      PRIOR active_end_date);

      CURSOR c_space_cust
      IS
      SELECT NVL (SUM (allocated_area), 0)
      , NVL (SUM (utilized_area), 0)
      FROM pn_space_assign_cust_all
      WHERE cust_assign_start_date <= l_as_of_date
      AND NVL (cust_assign_end_date, l_date) >= l_as_of_date
      AND location_id IN
      (SELECT location_id
      FROM pn_locations_all
      WHERE location_type_lookup_code =
      l_location_type_lookup_code
      AND status = 'A'
      AND l_as_of_date BETWEEN active_start_date
      AND active_end_date
      START WITH location_id = p_location_id
      CONNECT BY PRIOR location_id = parent_location_id
      AND l_as_of_date BETWEEN PRIOR active_start_date
      AND --ASHISH
      PRIOR active_end_date);
      BEGIN
      IF p_location_type IN ('BUILDING', 'FLOOR', 'OFFICE')
      THEN
      l_location_type_lookup_code := 'OFFICE';
      ELSIF p_location_type IN ('LAND', 'PARCEL', 'SECTION')
      THEN
      l_location_type_lookup_code := 'SECTION';
      END IF;

      OPEN c_loc;

      FETCH c_loc
      INTO
      l_assignable_area, l_rentable_area, l_usable_area, l_common_area, l_max_capacity, l_optimum_capacity;

      IF c_loc%NOTFOUND
      THEN
      NULL;
      END IF;

      CLOSE c_loc;

      IF p_area_type IN
      ('VACANT_AREA'
      , 'VACANT_AREA_PERCENT'
      , 'UTILIZED_CAPACITY'
      , 'VACANCY'
      , 'OCCUPANCY_PERCENT'
      , 'AREA_UTILIZED')
      OR p_area_type IS NULL
      THEN
      OPEN c_space_emp;

      FETCH c_space_emp INTO l_allocated_area_emp, l_utilizedcapacityemp;

      IF c_space_emp%NOTFOUND
      THEN
      NULL;
      END IF;

      CLOSE c_space_emp;

      OPEN c_space_cust;

      FETCH c_space_cust
      INTO l_allocated_area_cust, l_utilizedcapacitycust;

      IF c_space_cust%NOTFOUND
      THEN
      NULL;
      END IF;

      l_allocated_area := l_allocated_area_emp + l_allocated_area_cust;
      l_vacant_area := l_assignable_area - l_allocated_area;
      l_utilizedcapacity := l_utilizedcapacityemp + l_utilizedcapacitycust;
      l_vacancy :=
      ROUND ( (l_max_capacity - l_utilizedcapacity)
      , 2);
      END IF;

      l_secondary_area :=
      ROUND ( (l_rentable_area - l_usable_area)
      , 2);

      IF (l_assignable_area = 0)
      THEN
      l_vacant_area_percent := 0;
      ELSE
      l_vacant_area_percent := (l_vacant_area * 100 / l_assignable_area);
      END IF;

      IF (l_max_capacity = 0)
      THEN
      l_occupancy_percent := 0;
      ELSE
      l_occupancy_percent := (l_utilizedcapacity * 100 / l_max_capacity);
      END IF;

      IF ( (l_rentable_area = 0)
      OR (l_utilizedcapacity = 0))
      THEN
      l_area_utilized := 0;
      ELSE
      l_area_utilized := (l_rentable_area / l_utilizedcapacity);
      END IF;

      p_loc_area.secondary_area := l_secondary_area;
      p_loc_area.assignable_area := l_assignable_area;
      p_loc_area.rentable_area := l_rentable_area;
      p_loc_area.usable_area := l_usable_area;
      p_loc_area.common_area := l_common_area;
      p_loc_area.max_capacity := l_max_capacity;
      p_loc_area.optimum_capacity := l_optimum_capacity;
      p_space_area.allocated_area := l_allocated_area;
      p_space_area.allocated_area_emp := l_allocated_area_emp;
      p_space_area.allocated_area_cust := l_allocated_area_cust;
      p_space_area.vacant_area_percent := l_vacant_area_percent;
      p_space_area.utilizedcapacityemp := l_utilizedcapacityemp;
      p_space_area.utilizedcapacitycust := l_utilizedcapacitycust;
      p_space_area.utilizedcapacity := l_utilizedcapacity;
      p_space_area.occupancy_percent := l_occupancy_percent;
      p_space_area.vacant_area := l_vacant_area;
      p_space_area.vacancy := l_vacancy;
      p_space_area.area_utilized := l_area_utilized;
      END get_area;
      END;

      I'm trying to call this pkg.procedure PNP_UTIL_FUNC.GET_AREA as below:

      /* Formatted on 31-Oct-2009 11:54:55 (QP5 v5.114.809.3010) */
      DECLARE
      v_area_type varchar2 (99) := 'VACANT_AREA';
      v_loc_id number := 1166;
      v_loc_type varchar2 (100) := 'BUILDING';

      TYPE v_loc_area
      IS
      RECORD (
      assignable_area number
      , rentable_area number
      , usable_area number
      , common_area number
      , secondary_area number
      , max_capacity number
      , optimum_capacity number
      );

      v_larea v_loc_area;

      TYPE v_space_area
      IS
      RECORD (
      allocated_area number
      , allocated_area_emp number
      , allocated_area_cust number
      , utilizedcapacityemp number
      , utilizedcapacitycust number
      , utilizedcapacity number
      , occupancy_percent number
      , vacant_area number
      , vacant_area_percent number
      , vacancy number
      , area_utilized number
      );

      v_sarea v_space_area;
      BEGIN
      pnp_util_func.get_area (v_loc_id
      , v_loc_type
      , v_area_type
      , SYSDATE
      , v_larea
      , v_sarea);
      END;

      But, the following error occurs:

      ORA-06550: line 39, column 4:
      PLS-00306: wrong number or types of arguments in call to 'GET_AREA'
      ORA-06550: line 39, column 4:
      PLS-00306: wrong number or types of arguments in call to 'GET_AREA'
      ORA-06550: line 39, column 4:
      PL/SQL: Statement ignored

      I'm unable to locate what argument errors here. Can anyone please help?

      Appreciate your assistance.

      Thanks,
      L. Mukunthan
        • 1. Re: PNP_UTIL_FUNC.GET_AREA Invalid number or type of arguments error. Help!
          abowden
          The error is due to you not using the correct types for the parameters. Looking at the last two arguments:

          p_loc_area OUT NOCOPY pn_location_area_rec
          , p_space_area OUT NOCOPY pn_space_area_rec

          The variables you pass in are of type:

          v_larea type = v_loc_area which is not the same as pn_location_area_rec
          v_sarea type = v_space_area which is not the same as pn_space_area_rec

          The individual fields of the records may have the same type but the composite object does not.

          so just declare

          v_larea type pn_location_area_rec
          v_sarea type pn_space_area_rec

          By the way, I notice that you're using explicit cursors a lot. Normally its best practice to use implicit cursors nowadays. Easier to read and maintain, and less processing.

          HTH
          • 2. Re: PNP_UTIL_FUNC.GET_AREA Invalid number or type of arguments error. Help!
            649467
            thanx for your update.
            But, i dont understand how u say that pn_location_area_rec is different from v_loc_area and the same for the other record too...
            in what i built, i exactly copied the same type declaration from the pkg, and replicated in my block.
            so, this shouldn't conflict.

            but still, can you please be more specific about how to call this way as you mentioned:
            ***
            so just declare

            v_larea type pn_location_area_rec
            v_sarea type pn_space_area_rec

            ***

            and also, kindly explain, what explicit cursor you are mentioning here.

            I'm a basic pl/sql user; hence, please advise with these steps to help me better.

            Thanks,
            L. Mukunthan
            • 3. Re: PNP_UTIL_FUNC.GET_AREA Invalid number or type of arguments error. Help!
              abowden
              You defined get area as:

              PROCEDURE get_area (p_location_id IN number
              , p_location_type IN varchar2
              , p_area_type IN varchar2 DEFAULT NULL
              , p_as_of_date IN date DEFAULT NULL
              , p_loc_area OUT NOCOPY pn_location_area_rec
              , p_space_area OUT NOCOPY pn_space_area_rec);
              END;

              The last 2 parameters are defined as pn_location_area_rec and pn_space_area_rec respectively.

              You use variables v_larea and v_sarea to hold the values of the last 2 parameters when you call get area. So you should declare these 2 variables as below:

              v_larea type pn_location_area_rec;
              v_sarea type pn_space_area_rec;

              and not:

              v_larea v_loc_area;
              v_sarea v_space_area;

              As for explicit cursors, I'm talking about the CURSOR c_loc .. etc cursors you declared. Most people now use an implicit cursor (i.e. select ... into ..) instead of declaring an explicit cursor like cursor l_loc is select .... from .... etc.
              Implicit cursors are easier to read and manage, and you don't have to bother with open, fetch and close calls to the cursor.