APPS.PN_SPACE_ALLOCATIONS_PKG SQL Statements

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

Line: 12

procedure INSERT_ROW (
   X_ROWID in out NOCOPY VARCHAR2,
   X_SPACE_ALLOCATION_ID in out NOCOPY NUMBER,
   X_LOCATION_ID in NUMBER,
   X_EMPLOYEE_ID in NUMBER,
   X_COST_CENTER_CODE in VARCHAR2,
   X_ALLOCATED_AREA_PCT in NUMBER,
   X_ALLOCATED_AREA in NUMBER,
   X_ATTRIBUTE_CATEGORY in VARCHAR2,
   X_ATTRIBUTE1 in VARCHAR2,
   X_ATTRIBUTE2 in VARCHAR2,
   X_ATTRIBUTE3 in VARCHAR2,
   X_ATTRIBUTE4 in VARCHAR2,
   X_ATTRIBUTE5 in VARCHAR2,
   X_ATTRIBUTE6 in VARCHAR2,
   X_ATTRIBUTE7 in VARCHAR2,
   X_ATTRIBUTE8 in VARCHAR2,
   X_ATTRIBUTE9 in VARCHAR2,
   X_ATTRIBUTE10 in VARCHAR2,
   X_ATTRIBUTE11 in VARCHAR2,
   X_ATTRIBUTE12 in VARCHAR2,
   X_ATTRIBUTE13 in VARCHAR2,
   X_ATTRIBUTE14 in VARCHAR2,
   X_ATTRIBUTE15 in VARCHAR2,
   X_CREATION_DATE in DATE,
   X_CREATED_BY in NUMBER,
   X_LAST_UPDATE_DATE in DATE,
   X_LAST_UPDATED_BY in NUMBER,
   X_LAST_UPDATE_LOGIN in NUMBER
   )
IS
   CURSOR C IS
      SELECT ROWID
      FROM PN_SPACE_ALLOCATIONS_ALL       --sdm_MOAC
      WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID ;

Line: 49

   SELECT PN_SPACE_ALLOCATIONS_S.nextval
   INTO X_SPACE_ALLOCATION_ID
   FROM DUAL;

Line: 53

   INSERT INTO PN_SPACE_ALLOCATIONS_ALL
   (                 --sdm_MOAC
       SPACE_ALLOCATION_ID,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN,
       LOCATION_ID,
       EMPLOYEE_ID,
       COST_CENTER_CODE,
       ALLOCATED_AREA_PCT,
       ALLOCATED_AREA,
       ATTRIBUTE_CATEGORY,
       ATTRIBUTE1,
       ATTRIBUTE2,
       ATTRIBUTE3,
       ATTRIBUTE4,
       ATTRIBUTE5,
       ATTRIBUTE6,
       ATTRIBUTE7,
       ATTRIBUTE8,
       ATTRIBUTE9,
       ATTRIBUTE10,
       ATTRIBUTE11,
       ATTRIBUTE12,
       ATTRIBUTE13,
       ATTRIBUTE14,
       ATTRIBUTE15
   )
   VALUES
   (
       X_SPACE_ALLOCATION_ID,
       X_LAST_UPDATE_DATE,
       X_LAST_UPDATED_BY,
       X_CREATION_DATE,
       X_CREATED_BY,
       X_LAST_UPDATE_LOGIN,
       X_LOCATION_ID,
       X_EMPLOYEE_ID,
       X_COST_CENTER_CODE,
       X_ALLOCATED_AREA_PCT,
       X_ALLOCATED_AREA,
       X_ATTRIBUTE_CATEGORY,
       X_ATTRIBUTE1,
       X_ATTRIBUTE2,
       X_ATTRIBUTE3,
       X_ATTRIBUTE4,
       X_ATTRIBUTE5,
       X_ATTRIBUTE6,
       X_ATTRIBUTE7,
       X_ATTRIBUTE8,
       X_ATTRIBUTE9,
       X_ATTRIBUTE10,
       X_ATTRIBUTE11,
       X_ATTRIBUTE12,
       X_ATTRIBUTE13,
       X_ATTRIBUTE14,
       X_ATTRIBUTE15
   );

Line: 123

END INSERT_ROW;

Line: 159

   CURSOR c1 IS SELECT
      LOCATION_ID,
      EMPLOYEE_ID,
      COST_CENTER_CODE,
      ALLOCATED_AREA_PCT,
      ALLOCATED_AREA,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      SPACE_ALLOCATION_ID
      FROM PN_SPACE_ALLOCATIONS_ALL          --sdm_MOAC
      WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID
      FOR UPDATE OF SPACE_ALLOCATION_ID nowait;

Line: 255

procedure UPDATE_ROW (
   X_SPACE_ALLOCATION_ID   IN NUMBER,
   X_LOCATION_ID           IN NUMBER,
   X_EMPLOYEE_ID           IN NUMBER,
   X_COST_CENTER_CODE      IN VARCHAR2,
   X_ALLOCATED_AREA_PCT    IN NUMBER,
   X_ALLOCATED_AREA        IN NUMBER,
   X_ATTRIBUTE_CATEGORY    IN VARCHAR2,
   X_ATTRIBUTE1            IN VARCHAR2,
   X_ATTRIBUTE2            IN VARCHAR2,
   X_ATTRIBUTE3            IN VARCHAR2,
   X_ATTRIBUTE4            IN VARCHAR2,
   X_ATTRIBUTE5            IN VARCHAR2,
   X_ATTRIBUTE6            IN VARCHAR2,
   X_ATTRIBUTE7            IN VARCHAR2,
   X_ATTRIBUTE8            IN VARCHAR2,
   X_ATTRIBUTE9            IN VARCHAR2,
   X_ATTRIBUTE10           IN VARCHAR2,
   X_ATTRIBUTE11           IN VARCHAR2,
   X_ATTRIBUTE12           IN VARCHAR2,
   X_ATTRIBUTE13           IN VARCHAR2,
   X_ATTRIBUTE14           IN VARCHAR2,
   X_ATTRIBUTE15           IN VARCHAR2,
   X_LAST_UPDATE_DATE      IN DATE,
   X_LAST_UPDATED_BY       IN NUMBER,
   X_LAST_UPDATE_LOGIN     IN NUMBER
) IS
BEGIN
  UPDATE PN_SPACE_ALLOCATIONS_ALL SET              --sdm_MOAC
    LOCATION_ID = X_LOCATION_ID,
    EMPLOYEE_ID = X_EMPLOYEE_ID,
    COST_CENTER_CODE = X_COST_CENTER_CODE,
    ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT,
    ALLOCATED_AREA = X_ALLOCATED_AREA,
    ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
    ATTRIBUTE1 = X_ATTRIBUTE1,
    ATTRIBUTE2 = X_ATTRIBUTE2,
    ATTRIBUTE3 = X_ATTRIBUTE3,
    ATTRIBUTE4 = X_ATTRIBUTE4,
    ATTRIBUTE5 = X_ATTRIBUTE5,
    ATTRIBUTE6 = X_ATTRIBUTE6,
    ATTRIBUTE7 = X_ATTRIBUTE7,
    ATTRIBUTE8 = X_ATTRIBUTE8,
    ATTRIBUTE9 = X_ATTRIBUTE9,
    ATTRIBUTE10 = X_ATTRIBUTE10,
    ATTRIBUTE11 = X_ATTRIBUTE11,
    ATTRIBUTE12 = X_ATTRIBUTE12,
    ATTRIBUTE13 = X_ATTRIBUTE13,
    ATTRIBUTE14 = X_ATTRIBUTE14,
    ATTRIBUTE15 = X_ATTRIBUTE15,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID;

Line: 314

END UPDATE_ROW;

Line: 324

procedure DELETE_ROW
(
  X_SPACE_ALLOCATION_ID in NUMBER
)
IS
BEGIN
  DELETE FROM PN_SPACE_ALLOCATIONS_ALL          --sdm_MOAC
  WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID;

Line: 337

END DELETE_ROW;

Line: 349

  SELECT 'x'
  INTO   l_dummy
  FROM   dual
  WHERE  EXISTS
               ( SELECT '1'
                 FROM   PN_SPACE_ALLOCATIONS_ALL      --sdm_MOAC
                 WHERE  EMPLOYEE_ID = X_EMPLOYEE_ID
                 AND    ORG_ID = X_ORG_ID);        --sdm_MOAC

Line: 373

PROCEDURE DELETE_OTHER_ASSIGNMENTS (
  X_EMPLOYEE_ID in NUMBER,
  X_ORG_ID  in NUMBER               --sdm_MOAC
   ) IS

BEGIN

  DELETE FROM PN_SPACE_ALLOCATIONS_ALL          --sdm_MOAC
  WHERE  EMPLOYEE_ID = X_EMPLOYEE_ID
  AND   ORG_ID = X_ORG_ID;             --sdm_MOAC

Line: 388

END DELETE_OTHER_ASSIGNMENTS;

Line: 408

   SELECT sum(PNP_UTIL_FUNC.get_vacant_area(location_id))
   INTO x_vacant_area
   FROM PN_SPACE_ALLOCATIONS_ALL          --sdm_MOAC
   WHERE location_id = x_location_id;

Line: 434

   SELECT sum(PNP_UTIL_FUNC.get_utilized_capacity(location_id))
   INTO x_utilized_capacity
   FROM PN_SPACE_ALLOCATIONS_ALL
   WHERE location_id = x_location_id ;

Line: 461

  SELECT count(*)
  INTO   l_utilized
  FROM   pn_space_allocations_all         --sdm_MOAC
  WHERE  location_id = x_location_id;

Line: 467

  UPDATE PN_SPACE_ALLOCATIONS_ALL         --sdm_MOAC
  SET    allocated_area_pct = round(100 / l_utilized, 2),
         allocated_area     = round(x_usable_area / l_utilized, 2)
  WHERE  location_id        = x_location_id;