APPS.AHL_LTP_SPACE_CAPB_PVT SQL Statements

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

Line: 51

          SELECT lookup_code INTO x_lookup_code
           FROM FND_LOOKUP_VALUES_VL
          WHERE lookup_type = p_lookup_type
            AND lookup_code = p_lookup_code
            AND SYSDATE BETWEEN start_date_active
            AND NVL(end_date_active,SYSDATE);

Line: 61

          SELECT lookup_code INTO x_lookup_code
           FROM FND_LOOKUP_VALUES_VL
          WHERE lookup_type = p_lookup_type
            AND meaning     = p_meaning
            AND SYSDATE BETWEEN start_date_active
            AND NVL(end_date_active,SYSDATE);

Line: 103

          SELECT organization_id
              INTO x_organization_id
            FROM HR_ALL_ORGANIZATION_UNITS
          WHERE organization_id   = p_organization_id;

Line: 108

          SELECT organization_id
              INTO x_organization_id
            FROM HR_ALL_ORGANIZATION_UNITS
          WHERE NAME  = p_org_name;

Line: 150

          SELECT department_id
             INTO x_department_id
            FROM BOM_DEPARTMENTS
          WHERE organization_id = p_organization_id
            AND department_id   = p_department_id;

Line: 158

          SELECT department_id
             INTO x_department_id
           FROM BOM_DEPARTMENTS
          WHERE organization_id =  p_organization_id
            AND description = p_dept_description;

Line: 199

          SELECT space_id
              INTO x_space_id
            FROM AHL_SPACES_VL
          WHERE space_id   = p_space_id;

Line: 204

          SELECT space_id
              INTO x_space_id
           FROM AHL_SPACES_VL
          WHERE SPACE_NAME  = p_space_name;

Line: 276

   SELECT ROW_ID,
          SPACE_ID,
          SPACE_NAME,
          BOM_DEPARTMENT_ID,
          ORGANIZATION_ID,
          SPACE_CATEGORY,
          INACTIVE_FLAG,
          OBJECT_VERSION_NUMBER,
          ATTRIBUTE_CATEGORY,
          ATTRIBUTE1,
          ATTRIBUTE2,
          ATTRIBUTE3,
          ATTRIBUTE4,
          ATTRIBUTE5,
          ATTRIBUTE6,
          ATTRIBUTE7,
          ATTRIBUTE8,
          ATTRIBUTE9,
          ATTRIBUTE10,
          ATTRIBUTE11,
          ATTRIBUTE12,
          ATTRIBUTE13,
          ATTRIBUTE14,
          ATTRIBUTE15
     FROM  ahl_spaces_vl
   WHERE   space_id = p_space_rec.space_id;

Line: 306

   SELECT space_id FROM
     AHL_SPACES_VL
   WHERE space_name = c_space_name
     AND bom_department_id = c_dept_id;

Line: 499

 SELECT space_name
   FROM AHL_SPACES_VL
  WHERE space_name = c_space_name
   AND  bom_department_id = c_dept_id;

Line: 599

      SELECT space_id INTO l_space_id
        FROM AHL_SPACE_ASSIGNMENTS
      WHERE space_id = p_space_rec.space_id;

Line: 798

  SELECT AHL_SPACES_B_S.NEXTVAL
    FROM   dual;

Line: 802

     SELECT 1
       FROM   dual
      WHERE EXISTS (SELECT 1
                      FROM   ahl_spaces_b
                     WHERE  space_id = x_id);

Line: 1115

   AHL_SPACES_PKG.INSERT_ROW (
         X_ROWID                   => l_rowid,
         X_SPACE_ID                => l_space_id,
         X_BOM_DEPARTMENT_ID       => l_space_rec.department_id,
         X_ORGANIZATION_ID         => l_space_rec.organization_id,
         X_INACTIVE_FLAG           => 'Y',
         X_SPACE_CATEGORY          => p_x_space_rec.space_category_code,
         X_SPACE_NAME              => p_x_space_rec.space_name,
         X_OBJECT_VERSION_NUMBER   => 1,
         X_ATTRIBUTE_CATEGORY      => l_space_rec.attribute_category,
         X_ATTRIBUTE1              => l_space_rec.attribute1,
         X_ATTRIBUTE2              => l_space_rec.attribute2,
         X_ATTRIBUTE3              => l_space_rec.attribute3,
         X_ATTRIBUTE4              => l_space_rec.attribute4,
         X_ATTRIBUTE5              => l_space_rec.attribute5,
         X_ATTRIBUTE6              => l_space_rec.attribute6,
         X_ATTRIBUTE7              => l_space_rec.attribute7,
         X_ATTRIBUTE8              => l_space_rec.attribute8,
         X_ATTRIBUTE9              => l_space_rec.attribute9,
         X_ATTRIBUTE10             => l_space_rec.attribute10,
         X_ATTRIBUTE11             => l_space_rec.attribute11,
         X_ATTRIBUTE12             => l_space_rec.attribute12,
         X_ATTRIBUTE13             => l_space_rec.attribute13,
         X_ATTRIBUTE14             => l_space_rec.attribute14,
         X_ATTRIBUTE15             => l_space_rec.attribute15,
         X_CREATION_DATE           => SYSDATE,
         X_CREATED_BY              => Fnd_Global.USER_ID,
         X_LAST_UPDATE_DATE        => SYSDATE,
         X_LAST_UPDATED_BY         => Fnd_Global.USER_ID,
         X_LAST_UPDATE_LOGIN       => Fnd_Global.LOGIN_ID);

Line: 1230

PROCEDURE Update_Space (
   p_api_version             IN    NUMBER,
   p_init_msg_list           IN    VARCHAR2  := FND_API.g_false,
   p_commit                  IN    VARCHAR2  := FND_API.g_false,
   p_validation_level        IN    NUMBER    := FND_API.g_valid_level_full,
   p_module_type             IN     VARCHAR2  := 'JSP',
   p_space_rec               IN    ahl_ltp_space_capb_pub.Space_Rec,
   x_return_status             OUT NOCOPY VARCHAR2,
   x_msg_count                 OUT NOCOPY NUMBER,
   x_msg_data                  OUT NOCOPY VARCHAR2
)
IS
CURSOR space_name_cur (c_space_id IN  NUMBER,
                       c_dept_id   IN NUMBER)
IS
 SELECT space_name, space_id
   FROM AHL_SPACES_VL
  WHERE space_id = c_space_id
   AND  bom_department_id = c_dept_id;

Line: 1251

 l_api_name        CONSTANT VARCHAR2(30) := 'UPDATE_SPACE';

Line: 1270

  SAVEPOINT update_space;

Line: 1277

   AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Update Space ','+SPCBL+');

Line: 1423

   Ahl_Debug_Pub.debug( 'before update space id'||p_space_rec.space_id);

Line: 1427

   AHL_SPACES_PKG.UPDATE_ROW
         (
         X_SPACE_ID                => l_Aspace_rec.space_id,
         X_BOM_DEPARTMENT_ID       => l_Aspace_rec.department_id,
         X_ORGANIZATION_ID         => l_Aspace_rec.organization_id,
         X_SPACE_NAME              => l_Aspace_rec.space_name,
         X_SPACE_CATEGORY          => l_Aspace_rec.space_category_code,
         X_INACTIVE_FLAG           => l_Aspace_rec.inactive_flag_code,
         X_OBJECT_VERSION_NUMBER   => l_Aspace_rec.object_version_number+1,
         X_ATTRIBUTE_CATEGORY      => l_Aspace_rec.attribute_category,
         X_ATTRIBUTE1              => l_Aspace_rec.attribute1,
         X_ATTRIBUTE2              => l_Aspace_rec.attribute2,
         X_ATTRIBUTE3              => l_Aspace_rec.attribute3,
         X_ATTRIBUTE4              => l_Aspace_rec.attribute4,
         X_ATTRIBUTE5              => l_Aspace_rec.attribute5,
         X_ATTRIBUTE6              => l_Aspace_rec.attribute6,
         X_ATTRIBUTE7              => l_Aspace_rec.attribute7,
         X_ATTRIBUTE8              => l_Aspace_rec.attribute8,
         X_ATTRIBUTE9              => l_Aspace_rec.attribute9,
         X_ATTRIBUTE10             => l_Aspace_rec.attribute10,
         X_ATTRIBUTE11             => l_Aspace_rec.attribute11,
         X_ATTRIBUTE12             => l_Aspace_rec.attribute12,
         X_ATTRIBUTE13             => l_Aspace_rec.attribute13,
         X_ATTRIBUTE14             => l_Aspace_rec.attribute14,
         X_ATTRIBUTE15             => l_Aspace_rec.attribute15,
         X_LAST_UPDATE_DATE        => SYSDATE,
         X_LAST_UPDATED_BY         => Fnd_Global.USER_ID,
         X_LAST_UPDATE_LOGIN       => Fnd_Global.LOGIN_ID);

Line: 1473

   Ahl_Debug_Pub.debug( 'End of private api Update Space ','+SPCBL+');

Line: 1479

    ROLLBACK TO update_space;

Line: 1487

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space ','+SPCBL+');

Line: 1492

    ROLLBACK TO update_space;

Line: 1501

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space ','+SPCBL+');

Line: 1506

    ROLLBACK TO update_space;

Line: 1511

                            p_procedure_name  =>  'UPDATE_SPACE',
                            p_error_text      => SUBSTR(SQLERRM,1,240));

Line: 1522

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_unavl_pvt.Update Space ','+SPCBL+');

Line: 1526

END Update_Space;

Line: 1542

PROCEDURE Delete_Space (
   p_api_version                IN     NUMBER,
   p_init_msg_list              IN     VARCHAR2  := FND_API.g_false,
   p_commit                     IN     VARCHAR2  := FND_API.g_false,
   p_validation_level           IN     NUMBER    := FND_API.g_valid_level_full,
   p_space_rec                  IN     ahl_ltp_space_capb_pub.Space_Rec,
   x_return_status                 OUT NOCOPY VARCHAR2,
   x_msg_count                     OUT NOCOPY NUMBER,
   x_msg_data                      OUT NOCOPY VARCHAR2

)
IS
--
  CURSOR c_space_cur
                 (c_space_id IN NUMBER)
   IS
  SELECT   space_id,object_version_number
    FROM     ahl_spaces_vl
   WHERE    space_id = c_space_id
    FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;

Line: 1565

  SELECT visit_id FROM
     AHL_SPACE_ASSIGNMENTS
   WHERE space_id = c_space_id;

Line: 1571

    SELECT status_code FROM AHL_VISITS_B
     WHERE visit_id = c_visit_id;

Line: 1575

 l_api_name        CONSTANT VARCHAR2(30) := 'DELETE_SPACE';

Line: 1589

  SAVEPOINT delete_space;

Line: 1596

       AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Delete Space','+SPCBL+');

Line: 1649

       UPDATE AHL_SPACES_B
         SET INACTIVE_FLAG = 'N'
        WHERE space_id = l_space_id;

Line: 1668

 AHL_SPACES_PKG.DELETE_ROW (
         X_SPACE_ID => l_space_id
     );

Line: 1672

     DELETE FROM AHL_SPACE_CAPABILITIES
      WHERE SPACE_ID = l_space_id;

Line: 1692

   Ahl_Debug_Pub.debug( 'End of private api Delete Space ','+SPCBL+');

Line: 1699

    ROLLBACK TO delete_space;

Line: 1707

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space ','+SPCBL+');

Line: 1712

    ROLLBACK TO delete_space;

Line: 1721

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space ','+SPCBL+');

Line: 1726

    ROLLBACK TO delete_space;

Line: 1731

                            p_procedure_name  =>  'DELETE_SPACE',
                            p_error_text      => SUBSTR(SQLERRM,1,240));

Line: 1742

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space','+SPCBL+');

Line: 1746

END Delete_Space;

Line: 1773

          SELECT DISTINCT(inventory_item_id)
             INTO x_inventory_item_id
            FROM MTL_SYSTEM_ITEMS_B_KFV
          WHERE inventory_item_id   = p_inventory_item_id;

Line: 1780

          SELECT DISTINCT(inventory_item_id)
             INTO x_inventory_item_id
           FROM MTL_SYSTEM_ITEMS_B_KFV
          WHERE concatenated_segments = p_item_description;

Line: 1854

   SELECT SPACE_CAPABILITY_ID,
          ORGANIZATION_ID,
          VISIT_TYPE,
          INVENTORY_ITEM_ID,
          SPACE_ID,
          OBJECT_VERSION_NUMBER,
          ATTRIBUTE_CATEGORY,
          ATTRIBUTE1,
          ATTRIBUTE2,
          ATTRIBUTE3,
          ATTRIBUTE4,
          ATTRIBUTE5,
          ATTRIBUTE6,
          ATTRIBUTE7,
          ATTRIBUTE8,
          ATTRIBUTE9,
          ATTRIBUTE10,
          ATTRIBUTE11,
          ATTRIBUTE12,
          ATTRIBUTE13,
          ATTRIBUTE14,
          ATTRIBUTE15
     FROM  ahl_space_capabilities
   WHERE   space_capability_id = p_space_capbl_rec.space_capability_id
    FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;

Line: 2037

 SELECT 'X'
   FROM AHL_SPACE_CAPABILITIES
 WHERE ORGANIZATION_ID    = c_organization_id
   AND VISIT_TYPE        = c_visit_type
   AND INVENTORY_ITEM_ID = c_inventory_item_code
   AND SPACE_ID          = c_space_id;

Line: 2321

  SELECT AHL_SPACE_CAPABILITIES_S.NEXTVAL
    FROM   dual;

Line: 2325

     SELECT 1
       FROM   dual
      WHERE EXISTS (SELECT 1
                      FROM   ahl_space_capabilities
                     WHERE  space_capability_id = x_id);

Line: 2333

 SELECT organization_id
  FROM AHL_SPACES_B
  WHERE SPACE_ID = c_space_id;

Line: 2694

    INSERT INTO AHL_SPACE_CAPABILITIES
                  (
                 SPACE_CAPABILITY_ID,
                 VISIT_TYPE,
                 INVENTORY_ITEM_ID,
                 ORGANIZATION_ID,
                 SPACE_ID,
                 OBJECT_VERSION_NUMBER,
                 ATTRIBUTE_CATEGORY,
                 ATTRIBUTE1,
                 ATTRIBUTE2,
                 ATTRIBUTE3,
                 ATTRIBUTE4,
                 ATTRIBUTE5,
                 ATTRIBUTE6,
                 ATTRIBUTE7,
                 ATTRIBUTE8,
                 ATTRIBUTE9,
                 ATTRIBUTE10,
                 ATTRIBUTE11,
                 ATTRIBUTE12,
                 ATTRIBUTE13,
                 ATTRIBUTE14,
                 ATTRIBUTE15,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN
                )
         VALUES
               (
                l_space_capability_id,
                l_space_capblts_rec.visit_type_code,
                l_space_capblts_rec.inventory_item_id,
                p_x_space_capblts_rec.organization_id,
                l_space_capblts_rec.space_id,
                1,
                l_space_capblts_rec.attribute_category,
                l_space_capblts_rec.attribute1,
                l_space_capblts_rec.attribute2,
                l_space_capblts_rec.attribute3,
                l_space_capblts_rec.attribute4,
                l_space_capblts_rec.attribute5,
                l_space_capblts_rec.attribute6,
                l_space_capblts_rec.attribute7,
                l_space_capblts_rec.attribute8,
                l_space_capblts_rec.attribute9,
                l_space_capblts_rec.attribute10,
                l_space_capblts_rec.attribute11,
                l_space_capblts_rec.attribute12,
                l_space_capblts_rec.attribute13,
                l_space_capblts_rec.attribute14,
                l_space_capblts_rec.attribute15,
                SYSDATE,
                Fnd_Global.user_id,
                SYSDATE,
                Fnd_Global.user_id,
                Fnd_Global.login_id
              );

Line: 2843

PROCEDURE Update_Space_Capblts (
   p_api_version             IN    NUMBER,
   p_init_msg_list           IN    VARCHAR2  := FND_API.g_false,
   p_commit                  IN    VARCHAR2  := FND_API.g_false,
   p_validation_level        IN    NUMBER    := FND_API.g_valid_level_full,
   p_module_type             IN     VARCHAR2  := 'JSP',
   p_space_capblts_rec       IN    ahl_ltp_space_capb_pub.Space_Capbl_Rec,
   x_return_status             OUT NOCOPY VARCHAR2,
   x_msg_count                 OUT NOCOPY NUMBER,
   x_msg_data                  OUT NOCOPY VARCHAR2
)
IS

 l_api_name        CONSTANT VARCHAR2(30) := 'UPDATE_SPACE_CAPBLTS';

Line: 2872

 SELECT organization_id
  FROM AHL_SPACES_B
  WHERE SPACE_ID = c_space_id;

Line: 2880

  SAVEPOINT update_space_capblts;

Line: 2887

       AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');

Line: 3053

           UPDATE AHL_SPACE_CAPABILITIES
             SET organization_id       = l_space_capblts_rec.organization_id,
                 space_id              = l_space_capblts_rec.space_id,
                 visit_type            = l_space_capblts_rec.visit_type_code,
                 inventory_item_id     = l_space_capblts_rec.inventory_item_id,
                 object_version_number = l_space_capblts_rec.object_version_number+1,
                 attribute_category    = l_space_capblts_rec.attribute_category,
                 attribute1            = l_space_capblts_rec.attribute1,
                 attribute2            = l_space_capblts_rec.attribute2,
                 attribute3            = l_space_capblts_rec.attribute3,
                 attribute4            = l_space_capblts_rec.attribute4,
                 attribute5            = l_space_capblts_rec.attribute5,
                 attribute6            = l_space_capblts_rec.attribute6,
                 attribute7            = l_space_capblts_rec.attribute7,
                 attribute8            = l_space_capblts_rec.attribute8,
                 attribute9            = l_space_capblts_rec.attribute9,
                 attribute10           = l_space_capblts_rec.attribute10,
                 attribute11           = l_space_capblts_rec.attribute11,
                 attribute12           = l_space_capblts_rec.attribute12,
                 attribute13           = l_space_capblts_rec.attribute13,
                 attribute14           = l_space_capblts_rec.attribute14,
                 attribute15           = l_space_capblts_rec.attribute15,
                 last_update_date      = SYSDATE,
                 last_updated_by       = Fnd_Global.user_id,
                 last_update_login     = Fnd_Global.login_id
         WHERE  space_capability_id  = p_space_capblts_rec.space_capability_id;

Line: 3097

   Ahl_Debug_Pub.debug( 'End of private api Update Space Capblts','+SPCBL+');

Line: 3104

    ROLLBACK TO update_space_capblts;

Line: 3113

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');

Line: 3119

    ROLLBACK TO update_space_capblts;

Line: 3128

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');

Line: 3134

    ROLLBACK TO update_space_capblts;

Line: 3139

                            p_procedure_name  =>  'UPDATE_SPACE_CAPBLTS',
                            p_error_text      => SUBSTR(SQLERRM,1,240));

Line: 3150

            AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');

Line: 3155

END Update_Space_Capblts;

Line: 3172

PROCEDURE Delete_Space_Capblts (
   p_api_version                IN    NUMBER,
   p_init_msg_list              IN    VARCHAR2  := FND_API.g_false,
   p_commit                     IN    VARCHAR2  := FND_API.g_false,
   p_validation_level           IN    NUMBER    := FND_API.g_valid_level_full,
   p_space_capblts_rec         IN    ahl_ltp_space_capb_pub.Space_Capbl_Rec,
   x_return_status                OUT NOCOPY VARCHAR2,
   x_msg_count                    OUT NOCOPY NUMBER,
   x_msg_data                     OUT NOCOPY VARCHAR2

)
IS

  CURSOR c_space_capb_cur
                 (c_space_capability_id IN NUMBER)
   IS
  SELECT   space_capability_id,object_version_number,
           visit_type,inventory_item_id
    FROM     ahl_space_capabilities
   WHERE    space_capability_id = c_space_capability_id
    FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;

Line: 3196

      SELECT visit_id
      FROM  ahl_space_assignments
       WHERE space_id = c_space_id;

Line: 3204

   SELECT 1 FROM ahl_visits_b
   WHERE visit_id = c_visit_id
     AND visit_type_code = c_visit_type
     AND inventory_item_id = c_inventory_item_id;

Line: 3209

 l_api_name        CONSTANT VARCHAR2(30) := 'DELETE_SPACE_CAPBLTS';

Line: 3224

  SAVEPOINT delete_space_capblts;

Line: 3231

   AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');

Line: 3292

   AHL_DEBUG_PUB.debug( 'before delete capabilities');

Line: 3295

      DELETE FROM AHL_SPACE_CAPABILITIES
      WHERE SPACE_CAPABILITY_ID = l_space_capability_id;

Line: 3314

   Ahl_Debug_Pub.debug( 'End of private api Delete Space Capblts','+SPCBL+');

Line: 3321

    ROLLBACK TO delete_space_capblts;

Line: 3329

         AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');

Line: 3334

    ROLLBACK TO delete_space_capblts;

Line: 3343

        AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');

Line: 3348

    ROLLBACK TO delete_space_capblts;

Line: 3353

                            p_procedure_name  =>  'DELETE_SPACE_CAPBLTS',
                            p_error_text      => SUBSTR(SQLERRM,1,240));

Line: 3364

        AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');

Line: 3368

 END Delete_Space_Capblts;