APPS.AHL_DI_DOC_REVISION_PVT SQL Statements

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

Line: 8

/* 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: 35

 P_DELETE_FLAG            IN    VARCHAR2  := 'N' )
IS

--Cursor to retrieve the revision type code
CURSOR get_revision_type_code(c_revision_type_code VARCHAR2)
 IS
SELECT lookup_code
  FROM FND_LOOKUP_VALUES_VL
 WHERE lookup_code = c_revision_type_code
   AND lookup_type = 'AHL_REVISION_TYPE'
   AND sysdate between start_date_active
   AND nvl(end_date_active,sysdate);

Line: 51

SELECT lookup_code
  FROM FND_LOOKUP_VALUES_VL
 WHERE lookup_code = c_revision_status_code
   AND lookup_type = 'AHL_REVISION_STATUS_TYPE'
   AND sysdate between start_date_active
   AND nvl(end_date_active,sysdate);

Line: 61

SELECT lookup_code
  FROM FND_LOOKUP_VALUES_VL
 WHERE lookup_code = c_media_type_code
   AND lookup_type = 'AHL_MEDIA_TYPE'
   AND sysdate between start_date_active
   AND nvl(end_date_active,sysdate);

Line: 71

SELECT 'X'
  FROM AHL_DOCUMENTS_B
 WHERE document_id  = c_document_id;

Line: 78

SELECT document_id,
       revision_no,
       revision_type_code,
       revision_status_code,
       revision_date,
       approved_by_party_id,
       approved_date,
       effective_date,
       obsolete_date,
       issue_date,
       received_date,
       media_type_code,
       --pekambar Enigma Phase II changes -- start
       enigma_doc_id,
       enigma_model_code,
       --pekambar Enigma Phase II changes -- end
       issue_number
  FROM AHL_DOC_REVISIONS_B
 WHERE doc_revision_id = c_doc_revision_id;

Line: 102

SELECT 'X'
  FROM AHL_DOC_REVISIONS_B
WHERE document_id = c_document_id
  AND revision_no = c_revision_no;

Line: 112

SELECT '1'
FROM AHL_DOC_FILE_ASSOC_V
WHERE revision_id = c_doc_revision_id;

Line: 146

   IF NVL(p_delete_flag,'N')  <> 'Y'
   THEN
    IF p_doc_revision_id IS NOT NULL
    THEN
       OPEN get_doc_revision_rec_info(p_doc_revision_id);

Line: 335

          SELECT MEANING into l_media_type_meaning
          FROM FND_LOOKUP_VALUES_VL
          WHERE LOOKUP_TYPE='AHL_MEDIA_TYPE' AND LOOKUP_CODE='E-FILE';

Line: 487

PROCEDURE UPDATE_ASSOCIATIONS
(
	p_doc_revision_id	IN	NUMBER	:=NULL,
	p_document_id		IN	NUMBER	:=NULL
)
IS
	--Cursor to get Association Records
	CURSOR get_association_records(c_document_id NUMBER)
	IS
	SELECT doc_title_asso_id,object_version_number,last_update_date,last_updated_by,last_update_login,
		aso_object_type_code,aso_object_id
	FROM AHL_DOC_TITLE_ASSOS_B
	WHERE document_id = c_document_id
	AND use_latest_rev_flag = 'Y';

Line: 507

	SELECT revision_status_code
	FROM AHL_DOC_REVISIONS_B
	WHERE doc_revision_id = c_doc_revision_id;

Line: 514

	SELECT CONFIG_STATUS_CODE
	FROM ahl_mc_headers_b header,ahl_mc_relationships relation
	WHERE relation.relationship_id =  c_relationship_id
	AND header.mc_header_id = relation.mc_header_id;

Line: 522

	SELECT STATUS
	FROM ahl_pc_headers_b header,ahl_pc_nodes_b node
	WHERE node.pc_node_id = c_node_id
	AND header.pc_header_id = node.pc_header_id;

Line: 530

	SELECT mr_status_code
	FROM ahl_mr_headers_b
	where mr_header_id = c_mr_header_id;

Line: 537

	SELECT revision_status_code
	FROM ahl_operations_b
	WHERE operation_id = c_operation_id;

Line: 544

	SELECT revision_status_code
	FROM ahl_routes_b
	WHERE route_id = c_route_id;

Line: 553

	--Query to select the latest revision id
	SELECT MAX(doc_revision_id) INTO l_latest_doc_revision_id
	FROM ahl_doc_revisions_b
	WHERE NVL(effective_date,revision_date) = (SELECT MAX(NVL(effective_date,revision_date))
							   FROM ahl_doc_revisions_b
						           WHERE document_id = p_document_id
                                                	   AND NVL(effective_date,revision_date) <= SYSDATE
							   AND revision_status_code = 'CURRENT')
		AND document_id = p_document_id
		AND revision_status_code = 'CURRENT';

Line: 583

	-- the following piece of code will update the document association table with the latest revision.
	OPEN get_association_records(p_document_id);

Line: 617

				AHL_DEBUG_PUB.debug( 'Before Update in New Procedure' );

Line: 619

			UPDATE AHL_DOC_TITLE_ASSOS_B
				SET	doc_revision_id = l_latest_doc_revision_id,
					object_version_number = l_asso_record.object_version_number + 1,
					last_update_date = l_asso_record.last_update_date,
					last_updated_by =l_asso_record.last_updated_by,
					last_update_login = l_asso_record.last_update_login
				WHERE	doc_title_asso_id =l_asso_record.doc_title_asso_id;

Line: 629

END UPDATE_ASSOCIATIONS;

Line: 718

             p_delete_flag            => p_x_revision_tbl(i).delete_flag
           );

Line: 870

        Select AHL_DOC_REVISIONS_B_S.Nextval Into l_doc_revision_id from dual;

Line: 872

             AHL_DOC_REVISIONS_PKG.INSERT_ROW(X_ROWID => l_row_id,
             				      X_DOC_REVISION_ID => l_doc_revision_id,
             				      X_APPROVED_DATE => l_revision_info.approved_date,
             				      X_EFFECTIVE_DATE => l_revision_info.effective_date,
					      X_OBSOLETE_DATE => l_revision_info.obsolete_date,
					      X_ISSUE_DATE => l_revision_info.issue_date,
					      X_RECEIVED_DATE => l_revision_info.received_date,
					      X_URL => l_revision_info.url,
					      X_MEDIA_TYPE_CODE => l_revision_info.media_type_code,
				              --pekambar Enigma Phase II changes -- start
				              X_ENIGMA_DOC_ID => l_revision_info.enigma_doc_id,
					      X_ENIGMA_MODEL_CODE => l_revision_info.enigma_model_code,
				              --pekambar Enigma Phase II changes -- end
					      X_VOLUME => l_revision_info.volume,
					      X_ISSUE => l_revision_info.issue,
					      X_ISSUE_NUMBER => l_revision_info.issue_number,
					      X_ATTRIBUTE_CATEGORY => l_revision_info.attribute_category,
					      X_ATTRIBUTE1 => l_revision_info.attribute1,
					      X_ATTRIBUTE2 => l_revision_info.attribute2,
					      X_REVISION_DATE => l_revision_info.revision_date,
					      X_ATTRIBUTE15 => l_revision_info.attribute15,
					      X_ATTRIBUTE9 => l_revision_info.attribute9,
					      X_ATTRIBUTE10 => l_revision_info.attribute10,
					      X_ATTRIBUTE11 => l_revision_info.attribute11,
					      X_ATTRIBUTE12 => l_revision_info.attribute12,
					      X_ATTRIBUTE13 => l_revision_info.attribute13,
					      X_DOCUMENT_ID => p_x_revision_tbl(i).document_id,
					      X_REVISION_NO => p_x_revision_tbl(i).revision_no,
					      X_APPROVED_BY_PARTY_ID => l_revision_info.approved_by_party_id,
					      X_REVISION_TYPE_CODE => p_x_revision_tbl(i).revision_type_code,
					      X_REVISION_STATUS_CODE => p_x_revision_tbl(i).revision_status_code,
					      X_OBJECT_VERSION_NUMBER => 1,
					      X_ATTRIBUTE3 => l_revision_info.attribute3,
					      X_ATTRIBUTE4 => l_revision_info.attribute4,
					      X_ATTRIBUTE5 => l_revision_info.attribute5,
					      X_ATTRIBUTE6 => l_revision_info.attribute6,
					      X_ATTRIBUTE7 => l_revision_info.attribute7,
					      X_ATTRIBUTE8 => l_revision_info.attribute8,
					      X_ATTRIBUTE14 => l_revision_info.attribute14,
					      X_COMMENTS => l_revision_info.comments,
					      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: 919

			UPDATE_ASSOCIATIONS(p_doc_revision_id => l_doc_revision_id,
					    p_document_id => p_x_revision_tbl(i).document_id);

Line: 1013

/* description :  Update the existing revision record   */
/*                and removes the revision record       */
/*                for an associated document            */
/*                                                      */
/*------------------------------------------------------*/
PROCEDURE MODIFY_REVISION
(
 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,
 --pekambar changed in enigma phase -2
 --p_x_revision_tbl            IN     revision_tbl                     ,
 p_x_revision_tbl            IN  OUT  NOCOPY  revision_tbl                     ,
 x_return_status                OUT NOCOPY VARCHAR2                         ,
 x_msg_count                    OUT NOCOPY NUMBER                           ,
 x_msg_data                     OUT NOCOPY VARCHAR2
)
IS
-- Used to retrieve the existing record info
CURSOR get_doc_revisions_b_rec_info(c_doc_revision_id  NUMBER)
 IS
SELECT ROWID,
       document_id,
       revision_no,
       revision_type_code,
       revision_status_code,
       revision_date,
       approved_by_party_id,
       approved_date,
       effective_date,
       obsolete_date,
       issue_date,
       received_date,
       url,
       media_type_code,
       --pekambar Enigma Phase II changes -- start
       enigma_doc_id,
       enigma_model_code,
       --pekambar Enigma Phase II changes -- end
       volume,
       issue,
       issue_number,
       object_version_number,
       attribute_category,
       attribute1,
       attribute2,
       attribute3,
       attribute4,
       attribute5,
       attribute6,
       attribute7,
       attribute8,
       attribute9,
       attribute10,
       attribute11,
       attribute12,
       attribute13,
       attribute14,
       attribute15
  FROM AHL_DOC_REVISIONS_B
 WHERE doc_revision_id = c_doc_revision_id
   FOR UPDATE OF object_version_number NOWAIT;

Line: 1080

SELECT comments
  FROM AHL_DOC_REVISIONS_TL
 WHERE doc_revision_id = c_doc_revision_id
   FOR UPDATE OF doc_revision_id NOWAIT;

Line: 1157

          p_delete_flag            => p_x_revision_tbl(i).delete_flag
        );

Line: 1343

     AHL_DOC_REVISIONS_PKG.UPDATE_ROW(X_DOC_REVISION_ID => p_x_revision_tbl(i).doc_revision_id,
                                      X_APPROVED_DATE => l_revision_info.approved_date,
                                      X_EFFECTIVE_DATE => l_revision_info.effective_date,
                                      X_OBSOLETE_DATE => l_revision_info.obsolete_date,
                                      X_ISSUE_DATE => l_revision_info.issue_date,
                                      X_RECEIVED_DATE => l_revision_info.received_date,
                                      X_URL => l_revision_info.url,
                                      X_MEDIA_TYPE_CODE => l_revision_info.media_type_code,
				      --pekambar Enigma Phase II changes -- start
				      X_ENIGMA_DOC_ID => l_revision_info.enigma_doc_id,
				      X_ENIGMA_MODEL_CODE => l_revision_info.enigma_model_code,
				      --pekambar Enigma Phase II changes -- end
                                      X_VOLUME => l_revision_info.volume,
                                      X_ISSUE => l_revision_info.issue,
                                      X_ISSUE_NUMBER => l_revision_info.issue_number,
                                      X_ATTRIBUTE_CATEGORY => l_revision_info.attribute_category,
                                      X_ATTRIBUTE1 => l_revision_info.attribute1,
                                      X_ATTRIBUTE2 => l_revision_info.attribute2,
                                      X_REVISION_DATE => l_revision_info.revision_date,
                                      X_ATTRIBUTE15 => l_revision_info.attribute15,
                                      X_ATTRIBUTE9 => l_revision_info.attribute9,
                                      X_ATTRIBUTE10 => l_revision_info.attribute10,
                                      X_ATTRIBUTE11 => l_revision_info.attribute11,
                                      X_ATTRIBUTE12 => l_revision_info.attribute12,
                                      X_ATTRIBUTE13 => l_revision_info.attribute13,
                                      X_DOCUMENT_ID => l_revision_info.document_id,
                                      X_REVISION_NO => l_revision_info.revision_no,
                                      X_APPROVED_BY_PARTY_ID => l_revision_info.approved_by_party_id,
                                      X_REVISION_TYPE_CODE => l_revision_info.revision_type_code,
                                      X_REVISION_STATUS_CODE => l_revision_info.revision_status_code,
                                      X_OBJECT_VERSION_NUMBER => l_revision_info.object_version_number+1,
                                      X_ATTRIBUTE3 => l_revision_info.attribute3,
                                      X_ATTRIBUTE4 => l_revision_info.attribute4,
                                      X_ATTRIBUTE5 => l_revision_info.attribute5,
                                      X_ATTRIBUTE6 => l_revision_info.attribute6,
                                      X_ATTRIBUTE7 => l_revision_info.attribute7,
                                      X_ATTRIBUTE8 => l_revision_info.attribute8,
                                      X_ATTRIBUTE14 => l_revision_info.attribute14,
                                      X_COMMENTS => l_comments,
                                      X_LAST_UPDATE_DATE => sysdate,
                                      X_LAST_UPDATED_BY => fnd_global.user_id,
                                      X_LAST_UPDATE_LOGIN => fnd_global.login_id);

Line: 1386

			UPDATE_ASSOCIATIONS(NULL,
					    p_document_id => p_x_revision_tbl(i).document_id);

Line: 1391

       NVL(p_x_revision_tbl(i).delete_flag, 'N') = 'Y' )

    THEN
       DELETE_REVISION
        (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_x_revision_tbl   =>  p_x_revision_tbl   ,
         x_return_status    => x_return_status     ,
         x_msg_count        => x_msg_count         ,
         x_msg_data         => x_msg_data
         );

Line: 1483

PROCEDURE DELETE_REVISION
(
 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_revision_tbl            IN     revision_tbl                     ,
 x_return_status                OUT NOCOPY VARCHAR2                         ,
 x_msg_count                    OUT NOCOPY NUMBER                           ,
 x_msg_data                     OUT NOCOPY VARCHAR2
 )
IS
-- to get the revision rec
CURSOR get_revision_rec_b_info(c_doc_revision_id  NUMBER)
 IS
SELECT ROWID,
       effective_date,
       obsolete_date,
       object_version_number
  FROM AHL_DOC_REVISIONS_B
 WHERE doc_revision_id = c_doc_revision_id
   FOR UPDATE OF object_version_number NOWAIT;

Line: 1507

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

Line: 1515

    SAVEPOINT delete_revision;

Line: 1565

      UPDATE AHL_DOC_REVISIONS_B
         SET OBSOLETE_DATE = p_x_revision_tbl(i).obsolete_date
       WHERE ROWID = l_rowid;

Line: 1577

    ROLLBACK TO delete_revision;

Line: 1584

    ROLLBACK TO delete_revision;

Line: 1590

    ROLLBACK TO delete_revision;

Line: 1595

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

Line: 1602

END DELETE_REVISION;

Line: 1605

PROCEDURE UPDATE_ASSOCIATIONS_CONCURRENT(
	errbuf		OUT NOCOPY VARCHAR2,
	retcode		OUT NOCOPY NUMBER,
	p_api_version	IN  NUMBER       := 1.0
)
IS
--Cursor to get ids of all the documents which are associated with use latest flag ='Yes'.
CURSOR get_all_document_ids
IS
select distinct(asso.document_id),doc.document_no
from ahl_doc_title_assos_b asso,ahl_documents_b doc
where asso.use_latest_rev_flag='Y'
and asso.document_id=doc.document_id;

Line: 1620

l_api_name		VARCHAR2(40) := 'UPDATE_ASSOCIATIONS_CONCURRENT';

Line: 1633

	update ahl_doc_revisions_b
	set revision_status_code ='OBSOLETE'
	where trunc(obsolete_date) <= trunc(sysdate)
	and revision_status_code <> 'OBSOLETE';

Line: 1638

	fnd_file.put_line(fnd_file.log,'Associations for following documents are checked and updated appropriately');

Line: 1643

		UPDATE_ASSOCIATIONS(NULL,l_document_id);

Line: 1655

END UPDATE_ASSOCIATIONS_CONCURRENT;