APPS.AHL_DI_PRO_TYPE_ASO_PVT SQL Statements

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

Line: 9

/* description :  Validation checks for before inserting     */
/*                new record as well before modification     */
/*                takes place                                */
/*-----------------------------------------------------------*/
G_DEBUG 		 VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;

Line: 19

 P_DELETE_FLAG             IN VARCHAR2  := 'N')
IS

-- Cursor to retrieve the doc type code from fnd lookups table
 CURSOR get_doc_type_code(c_doc_type_code VARCHAR2)
  IS
 SELECT lookup_code
   FROM FND_LOOKUPS
  WHERE lookup_code = c_doc_type_code
    AND lookup_type = 'AHL_DOC_TYPE'
    AND sysdate between start_date_active
    AND nvl(end_date_active,sysdate);

Line: 35

 SELECT lookup_code,meaning
   FROM FND_LOOKUP_VALUES_VL
  WHERE lookup_code = c_doc_sub_type_code
    AND lookup_type = 'AHL_DOC_SUB_TYPE'
    AND sysdate between start_date_active
    AND nvl(end_date_active,sysdate);

Line: 45

 SELECT doc_type_code,
        doc_sub_type_code
   FROM AHL_DOCUMENT_SUB_TYPES
  WHERE document_sub_type_id = c_document_sub_type_id;

Line: 55

 SELECT 'X'
   FROM AHL_DOCUMENT_SUB_TYPES
  WHERE doc_type_code  = c_doc_type_code
    AND doc_sub_type_code = c_doc_sub_type_code;

Line: 72

   IF p_delete_flag  <> 'Y'
   THEN
     IF p_document_sub_type_id IS NOT NULL
     THEN
        OPEN get_doc_sub_type_rec_info (p_document_sub_type_id);

Line: 150

/* procedure name: delete_doc_type_assoc                 */
/* description :Removes record from doc title assos table*/
/*               only if that associate type doesnt exist*/
/*               in documents table                      */
/*-------------------------------------------------------*/

/*
Commented by Senthil on 27 June 2002
The delete code is incorporated in modify_doc_type_assoc
so that a array of update and delete error messages can be formed.

PROCEDURE DELETE_DOC_TYPE_ASSOC
(
 p_api_version               IN     NUMBER    := 1.0               ,
 p_init_msg_list             IN     VARCHAR2  := FND_API.G_TRUE      ,
 p_commit                    IN     VARCHAR2  := FND_API.G_FALSE     ,
 p_validate_only             IN     VARCHAR2  := FND_API.G_TRUE      ,
 p_validation_level          IN     NUMBER    := FND_API.G_VALID_LEVEL_FULL,
 p_doc_type_assoc_tbl        IN     doc_type_assoc_tbl               ,
 x_return_status                OUT NOCOPY VARCHAR2                         ,
 x_msg_count                    OUT NOCOPY NUMBER                           ,
 x_msg_data                     OUT NOCOPY VARCHAR2)
IS
--Used to retrive the existing record
CURSOR get_doc_sub_rec_info(c_document_sub_type_id  NUMBER)
 IS
SELECT ROWID,
       doc_type_code,
       doc_sub_type_code,
       object_version_number
  FROM AHL_DOCUMENT_SUB_TYPES
 WHERE document_sub_type_id = c_document_sub_type_id
   FOR UPDATE OF object_version_number NOWAIT;

Line: 187

SELECT 'X'
  FROM AHL_DOCUMENTS_B
 WHERE doc_type_code               = c_doc_type_code
   AND nvl(doc_sub_type_code,'x')  = c_doc_sub_type_code;

Line: 195

 SELECT lookup_code,meaning
   FROM FND_LOOKUP_VALUES_VL
  WHERE lookup_code = c_doc_sub_type_code
    AND lookup_type = 'AHL_DOC_SUB_TYPE'
    AND sysdate between start_date_active
    AND nvl(end_date_active,sysdate);

Line: 203

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

Line: 214

   SAVEPOINT delete_doc_type_assoc;

Line: 223

		  AHL_DEBUG_PUB.debug( 'enter ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');

Line: 245

        p_doc_type_assoc_tbl(i).delete_flag = 'Y'

    THEN
   --Checks for weather the exists or not
   OPEN get_doc_sub_rec_info(p_doc_type_assoc_tbl(i).document_sub_type_id);

Line: 295

   DELETE FROM  AHL_DOCUMENT_SUB_TYPES
         WHERE ROWID = l_rowid;

Line: 314

		  AHL_DEBUG_PUB.debug( 'End of private api Delete Doc Type','+DOCTY+');

Line: 325

    ROLLBACK TO delete_doc_type_assoc;

Line: 332

            AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');

Line: 341

    ROLLBACK TO delete_doc_type_assoc;

Line: 349

            AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');

Line: 358

    ROLLBACK TO delete_doc_type_assoc;

Line: 363

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

Line: 373

            AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');

Line: 381

END DELETE_DOC_TYPE_ASSOC;

Line: 405

 SELECT 'x'
   FROM AHL_DOCUMENT_SUB_TYPES
  WHERE doc_type_code  = c_doc_type_code
    AND doc_sub_type_code = c_doc_sub_type_code;

Line: 413

 SELECT lookup_code,meaning
   FROM FND_LOOKUP_VALUES_VL
  WHERE lookup_code = c_doc_sub_type_code
    AND lookup_type = 'AHL_DOC_SUB_TYPE'
    AND sysdate between start_date_active
    AND nvl(end_date_active,sysdate);

Line: 468

         p_delete_flag          => p_x_doc_type_assoc_tbl(i).delete_flag);

Line: 516

    SELECT AHL_DOCUMENT_SUB_TYPES_S.Nextval INTO
              l_document_sub_type_id from DUAL;

Line: 519

    INSERT INTO AHL_DOCUMENT_SUB_TYPES
                (
                 DOCUMENT_SUB_TYPE_ID,
                 DOC_TYPE_CODE,
                 DOC_SUB_TYPE_CODE,
                 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_document_sub_type_id,
                p_x_doc_type_assoc_tbl(i).doc_type_code,
                p_x_doc_type_assoc_tbl(i).doc_sub_type_code,
                1,
                l_doc_type_assoc_info.attribute_category,
                l_doc_type_assoc_info.attribute1,
                l_doc_type_assoc_info.attribute2,
                l_doc_type_assoc_info.attribute3,
                l_doc_type_assoc_info.attribute4,
                l_doc_type_assoc_info.attribute5,
                l_doc_type_assoc_info.attribute6,
                l_doc_type_assoc_info.attribute7,
                l_doc_type_assoc_info.attribute8,
                l_doc_type_assoc_info.attribute9,
                l_doc_type_assoc_info.attribute10,
                l_doc_type_assoc_info.attribute11,
                l_doc_type_assoc_info.attribute12,
                l_doc_type_assoc_info.attribute13,
                l_doc_type_assoc_info.attribute14,
                l_doc_type_assoc_info.attribute15,
                sysdate,
                fnd_global.user_id,
                sysdate,
                fnd_global.user_id,
                fnd_global.login_id
              );

Line: 663

/* description :  Update the existing association record*/
/*                and removes the association record    */
/*                                                      */
/*------------------------------------------------------*/
PROCEDURE MODIFY_DOC_TYPE_ASSOC
(
 p_api_version              IN      NUMBER    :=  1.0            ,
 p_init_msg_list            IN      VARCHAR2  := FND_API.G_TRUE  ,
 p_commit                   IN      VARCHAR2  := FND_API.G_FALSE ,
 p_validate_only            IN      VARCHAR2  := FND_API.G_TRUE  ,
 p_validation_level         IN      NUMBER    := FND_API.G_VALID_LEVEL_FULL,
 p_x_doc_type_assoc_tbl     IN  OUT NOCOPY doc_type_assoc_tbl    ,
 x_return_status                OUT NOCOPY VARCHAR2                     ,
 x_msg_count                    OUT NOCOPY NUMBER                       ,
 x_msg_data                     OUT NOCOPY VARCHAR2
)
IS
-- Cursor is used to retrieve the record from Document sub types table
CURSOR get_doc_sub_rec_info(c_document_sub_type_id  NUMBER)
 IS
SELECT ROWID ROW_ID,
       doc_type_code,
       doc_sub_type_code,
       object_version_number,
       attribute_category,
       attribute1,
       attribute2,
       attribute3,
       attribute4,
       attribute5,
       attribute6,
       attribute7,
       attribute8,
       attribute9,
       attribute10,
       attribute11,
       attribute12,
       attribute13,
       attribute14,
       attribute15
  FROM AHL_DOCUMENT_SUB_TYPES
 WHERE document_sub_type_id = c_document_sub_type_id
   FOR UPDATE OF object_version_number NOWAIT;

Line: 710

 SELECT meaning
   FROM FND_LOOKUP_VALUES_VL
  WHERE lookup_code = c_doc_sub_type_code
    AND lookup_type = 'AHL_DOC_SUB_TYPE'
    AND sysdate between start_date_active
    AND nvl(end_date_active,sysdate);

Line: 721

SELECT ROWID,
       doc_type_code,
       doc_sub_type_code,
       object_version_number
  FROM AHL_DOCUMENT_SUB_TYPES
 WHERE document_sub_type_id = c_document_sub_type_id
   FOR UPDATE OF object_version_number NOWAIT;

Line: 733

SELECT 'X'
  FROM AHL_DOCUMENTS_B
 WHERE doc_type_code               = c_doc_type_code
   AND nvl(doc_sub_type_code,'x')  = c_doc_sub_type_code;

Line: 794

            p_delete_flag          => p_x_doc_type_assoc_tbl(i).delete_flag);

Line: 827

               p_x_doc_type_assoc_tbl(i).delete_flag <> 'Y'
            THEN
              -- The following conditions compare the new record value with old  record
              -- value, if its different then assign the new value else continue

                 l_doc_type_assoc_info.doc_type_code := p_x_doc_type_assoc_tbl(i).doc_type_code;

Line: 872

                 UPDATE AHL_DOCUMENT_SUB_TYPES
	             SET doc_type_code         = l_doc_type_assoc_info.doc_type_code,
			doc_sub_type_code     = l_doc_type_assoc_info.doc_sub_type_code,
			object_version_number = l_doc_type_assoc_info.object_version_number+1,
			attribute_category    = l_doc_type_assoc_info.attribute_category,
			attribute1            = l_doc_type_assoc_info.attribute1,
			attribute2            = l_doc_type_assoc_info.attribute2,
			attribute3            = l_doc_type_assoc_info.attribute3,
			attribute4            = l_doc_type_assoc_info.attribute4,
			attribute5            = l_doc_type_assoc_info.attribute5,
			attribute6            = l_doc_type_assoc_info.attribute6,
			attribute7            = l_doc_type_assoc_info.attribute7,
			attribute8            = l_doc_type_assoc_info.attribute8,
			attribute9            = l_doc_type_assoc_info.attribute9,
			attribute10           = l_doc_type_assoc_info.attribute10,
			attribute11           = l_doc_type_assoc_info.attribute11,
			attribute12           = l_doc_type_assoc_info.attribute12,
			attribute13           = l_doc_type_assoc_info.attribute13,
			attribute14           = l_doc_type_assoc_info.attribute14,
			attribute15           = l_doc_type_assoc_info.attribute15,
			last_update_date      = sysdate,
			last_updated_by       = fnd_global.user_id,
			last_update_login     = fnd_global.login_id
                 WHERE  document_sub_type_id  = p_x_doc_type_assoc_tbl(i).document_sub_type_id;

Line: 907

		  AHL_DEBUG_PUB.debug( 'The error count inside update'||FND_MSG_PUB.count_msg);

Line: 915

              p_x_doc_type_assoc_tbl(i).delete_flag = 'Y'

           THEN
            IF G_DEBUG='Y' THEN
		  AHL_DEBUG_PUB.debug( 'documetn sub type id '||p_x_doc_type_assoc_tbl(i).document_sub_type_id);

Line: 962

		  AHL_DEBUG_PUB.debug( 'The error count inside delete'||FND_MSG_PUB.count_msg);

Line: 966

		   -- Delete the record from document subtypes table
		   DELETE FROM  AHL_DOCUMENT_SUB_TYPES
			 WHERE ROWID = l_doc_type_assoc_info.ROW_ID;

Line: 972

/*       DELETE_DOC_TYPE_ASSOC
        (p_api_version      => 1.0               ,
         p_init_msg_list    => FND_API.G_TRUE    ,
         p_commit           => FND_API.G_FALSE   ,
         p_validate_only    => FND_API.G_TRUE    ,
         p_validation_level => FND_API.G_VALID_LEVEL_FULL,
         p_doc_type_assoc_tbl =>  p_x_doc_type_assoc_tbl ,
         x_return_status    => x_return_status   ,
         x_msg_count        => x_msg_count       ,
         x_msg_data         => x_msg_data);