[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Contract_Doc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_business_document_type IN VARCHAR2,
p_business_document_id IN NUMBER,
p_business_document_version IN NUMBER,
p_attached_document_id IN NUMBER,
p_external_visibility_flag IN VARCHAR2,
p_effective_from_type IN VARCHAR2,
p_effective_from_id IN NUMBER,
p_effective_from_version IN NUMBER,
p_include_for_approval_flag IN VARCHAR2 := 'N',
p_create_fnd_attach IN VARCHAR2 := 'Y',
p_program_id IN NUMBER,
p_program_application_id IN NUMBER,
p_request_id IN NUMBER,
p_program_update_date IN DATE,
p_parent_attached_doc_id IN NUMBER := NULL,
p_generated_flag IN VARCHAR2 := 'N',
p_delete_flag IN VARCHAR2 := 'N',
p_primary_contract_doc_flag IN VARCHAR2 := 'N',
p_mergeable_doc_flag IN VARCHAR2 := 'N',
p_versioning_flag IN VARCHAR2 := 'N',
x_business_document_type OUT NOCOPY VARCHAR2,
x_business_document_id OUT NOCOPY NUMBER,
x_business_document_version OUT NOCOPY NUMBER,
x_attached_document_id OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Contract_Doc';
l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
SELECT fnd_attached_documents_s.nextval FROM dual;
SELECT max(seq_num) s_num
FROM FND_ATTACHED_DOCUMENTS
WHERE entity_name = l_entity_name
AND pk1_value = l_pk1_value
AND pk2_value = to_char(l_pk2_value)
AND pk3_value = to_char(l_pk3_value);
'600: Entered Insert_Contract_Doc');
'614: p_program_update_date : ' || p_program_update_date );
'617: p_delete_flag : ' || p_delete_flag );
SAVEPOINT g_Insert_Contract_Doc_GRP;
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
SELECT document_id INTO l_document_id
FROM FND_ATTACHED_DOCUMENTS
WHERE attached_document_id = p_attached_document_id;
IF (p_delete_flag = 'Y') THEN
l_pk2_value := to_char(p_business_document_id) || 'D';
END IF; -- (delete_flag = 'Y')
fnd_attached_documents_pkg.insert_row(
x_rowid => l_rowid,
x_attached_document_id => l_new_attachment_id,
x_document_id => l_document_id,
x_creation_date => l_creation_date,
x_created_by => l_created_by,
x_last_update_date => l_last_update_date,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login,
x_seq_num => l_seq_num,
x_entity_name => G_ATTACH_ENTITY_NAME,
x_column1 => NULL,
x_pk1_value => p_business_document_type,
x_pk2_value => l_pk2_value,
x_pk3_value => to_char(p_business_document_version),
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => NULL,
x_category_id => NULL,
x_security_type => NULL,
x_publish_flag => NULL,
x_usage_type => NULL,
x_language => NULL,
x_media_id => l_media_id,
x_doc_attribute_category => NULL,
x_doc_attribute1 => NULL,
x_doc_attribute2 => NULL,
x_doc_attribute3 => NULL,
x_doc_attribute4 => NULL,
x_doc_attribute5 => NULL,
x_doc_attribute6 => NULL,
x_doc_attribute7 => NULL,
x_doc_attribute8 => NULL,
x_doc_attribute9 => NULL,
x_doc_attribute10 => NULL,
x_doc_attribute11 => NULL,
x_doc_attribute12 => NULL,
x_doc_attribute13 => NULL,
x_doc_attribute14 => NULL,
x_doc_attribute15 => NULL,
X_create_doc => 'N'
);
OKC_CONTRACT_DOCS_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_business_document_type => p_business_document_type,
p_business_document_id => p_business_document_id,
p_business_document_version => p_business_document_version,
p_attached_document_id => l_attached_document_id,
p_external_visibility_flag => p_external_visibility_flag,
p_effective_from_type => p_effective_from_type,
p_effective_from_id => p_effective_from_id,
p_effective_from_version => p_effective_from_version,
p_include_for_approval_flag => p_include_for_approval_flag,
p_program_id => p_program_id,
p_program_application_id => p_program_application_id,
p_request_id => p_request_id,
p_program_update_date => p_program_update_date,
p_parent_attached_doc_id => l_parent_attached_doc_id,
p_generated_flag => p_generated_flag,
p_delete_flag => p_delete_flag,
p_primary_contract_doc_flag => p_primary_contract_doc_flag,
p_mergeable_doc_flag => p_mergeable_doc_flag,
x_business_document_type => x_business_document_type,
x_business_document_id => x_business_document_id,
x_business_document_version => x_business_document_version,
x_attached_document_id => x_attached_document_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Leaving Insert_Contract_Doc');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Leaving Insert_Contract_Doc: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_Insert_Contract_Doc_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving Insert_Contract_Doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_Insert_Contract_Doc_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Leaving Insert_Contract_Doc because of EXCEPTION: '||sqlerrm);
ROLLBACK TO g_Insert_Contract_Doc_GRP;
END Insert_Contract_Doc;
PROCEDURE Update_Contract_Doc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_business_document_type IN VARCHAR2,
p_business_document_id IN NUMBER,
p_business_document_version IN NUMBER,
p_attached_document_id IN NUMBER,
p_external_visibility_flag IN VARCHAR2,
p_effective_from_type IN VARCHAR2,
p_effective_from_id IN NUMBER,
p_effective_from_version IN NUMBER,
p_include_for_approval_flag IN VARCHAR2 := 'N',
p_program_id IN NUMBER,
p_program_application_id IN NUMBER,
p_request_id IN NUMBER,
p_program_update_date IN DATE,
p_parent_attached_doc_id IN NUMBER,
p_generated_flag IN VARCHAR2 := 'N',
p_delete_flag IN VARCHAR2 := 'N',
p_primary_contract_doc_flag IN VARCHAR2,
p_mergeable_doc_flag IN VARCHAR2,
p_object_version_number IN NUMBER,
p_versioning_flag IN VARCHAR2 := 'N'
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Doc';
SELECT primary_contract_doc_flag
FROM OKC_CONTRACT_DOCS
WHERE business_document_type = p_business_document_type
AND business_document_id = p_business_document_id
AND business_document_version = p_business_document_version
AND attached_document_id = p_attached_document_id;
'1600: Entered Update_Contract_Doc');
'1614: p_program_update_date : ' || p_program_update_date );
'1617: p_delete_flag : ' || p_delete_flag );
SAVEPOINT g_Update_Contract_Doc_GRP;
OKC_CONTRACT_DOCS_PVT.Update_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_business_document_type => p_business_document_type,
p_business_document_id => p_business_document_id,
p_business_document_version => p_business_document_version,
p_attached_document_id => p_attached_document_id,
p_external_visibility_flag => p_external_visibility_flag,
p_effective_from_type => p_effective_from_type,
p_effective_from_id => p_effective_from_id,
p_effective_from_version => p_effective_from_version,
p_include_for_approval_flag => p_include_for_approval_flag,
p_program_id => p_program_id,
p_program_application_id => p_program_application_id,
p_request_id => p_request_id,
p_program_update_date => p_program_update_date,
p_parent_attached_doc_id => p_parent_attached_doc_id,
p_generated_flag => p_generated_flag,
p_delete_flag => p_delete_flag,
p_object_version_number => p_object_version_number,
p_primary_contract_doc_flag => p_primary_contract_doc_flag,
p_mergeable_doc_flag => p_mergeable_doc_flag
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Leaving Update_Contract_Doc');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: Leaving Update_Contract_Doc: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_Update_Contract_Doc_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Leaving Update_Contract_Doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_Update_Contract_Doc_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: Leaving Update_Contract_Doc because of EXCEPTION: '||sqlerrm);
ROLLBACK TO g_Update_Contract_Doc_GRP;
END Update_Contract_Doc;
PROCEDURE Delete_Contract_Doc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_business_document_type IN VARCHAR2,
p_business_document_id IN NUMBER,
p_business_document_version IN NUMBER,
p_attached_document_id IN NUMBER,
p_doc_approved_flag IN VARCHAR2 := 'N',
p_object_version_number IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Doc';
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version = l_bus_doc_version
AND attached_document_id = l_attached_doc_id
AND delete_flag = 'N';
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND effective_from_version = l_bus_doc_version
AND parent_attached_doc_id = l_attached_doc_id;
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version = G_CURRENT_VERSION
AND parent_attached_doc_id = l_parent_attached_doc_id
AND delete_flag = 'N';
SELECT fad.rowid, fad.*
FROM FND_ATTACHED_DOCUMENTS fad
WHERE attached_document_id = p_attached_document_id;
SELECT
B.datatype_id datatype_id,
B.category_id category_id,
B.security_type security_type,
B.security_id security_id,
B.publish_flag publish_flag,
B.image_type image_type,
B.storage_type storage_type,
B.usage_type usage_type,
B.start_date_active start_date_active,
B.end_date_active end_date_active,
TL.language language,
TL.description description,
B.file_name file_name,
B.url url,
B.media_id media_id
FROM fnd_documents B, fnd_documents_tl TL
WHERE B.document_id = l_document_id
AND TL.document_id = l_document_id
AND TL.language = USERENV('LANG');
'2200: Entered Delete_Contract_Doc');
SAVEPOINT g_Delete_Contract_Doc_GRP;
SELECT parent_attached_doc_id
INTO l_parent_attached_doc_id
FROM OKC_CONTRACT_DOCS
WHERE business_document_type = p_business_document_type
AND business_document_id = p_business_document_id
AND business_document_version = p_business_document_version
AND attached_document_id = p_attached_document_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265-1:delete_flag is: ' || child_ref_attach_rec.delete_flag);
OKC_CONTRACT_DOCS_GRP.delete_contract_doc(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_business_document_type => child_ref_attach_rec.business_document_type,
p_business_document_id => child_ref_attach_rec.business_document_id,
p_business_document_version => child_ref_attach_rec.business_document_version,
p_attached_document_id => child_ref_attach_rec.attached_document_id,
p_doc_approved_flag => p_doc_approved_flag,
p_object_version_number => child_ref_attach_rec.object_version_number,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265-2:delete_flag is: ' || rel_ref_attach_rec.delete_flag);
OKC_CONTRACT_DOCS_GRP.delete_contract_doc(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_business_document_type => rel_ref_attach_rec.business_document_type,
p_business_document_id => rel_ref_attach_rec.business_document_id,
p_business_document_version => rel_ref_attach_rec.business_document_version,
p_attached_document_id => rel_ref_attach_rec.attached_document_id,
p_doc_approved_flag => p_doc_approved_flag,
p_object_version_number => rel_ref_attach_rec.object_version_number,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
SELECT effective_from_type, effective_from_id, effective_from_version
INTO l_effective_from_type, l_effective_from_id, l_effective_from_version
FROM OKC_CONTRACT_DOCS
WHERE business_document_type = p_business_document_type
AND business_document_id = p_business_document_id
AND business_document_version = p_business_document_version
AND attached_document_id = p_attached_document_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265:delete_flag is: ' || attach_rec.delete_flag);
fnd_attached_documents_pkg.Update_Row(
X_Rowid => fnd_attached_doc_rec.rowid,
X_attached_document_id => fnd_attached_doc_rec.attached_document_id,
X_document_id => fnd_attached_doc_rec.document_id,
X_last_update_date => fnd_attached_doc_rec.last_update_date,
X_last_updated_by => fnd_attached_doc_rec.last_updated_by,
X_last_update_login => fnd_attached_doc_rec.last_update_login,
X_seq_num => fnd_attached_doc_rec.seq_num,
X_entity_name => fnd_attached_doc_rec.entity_name,
X_column1 => fnd_attached_doc_rec.column1,
X_pk1_value => fnd_attached_doc_rec.pk1_value,
X_pk2_value => l_pk2_value,
X_pk3_value => fnd_attached_doc_rec.pk3_value,
X_pk4_value => fnd_attached_doc_rec.pk4_value,
X_pk5_value => fnd_attached_doc_rec.pk5_value,
X_automatically_added_flag => fnd_attached_doc_rec.automatically_added_flag,
X_request_id => fnd_attached_doc_rec.request_id,
X_program_application_id => fnd_attached_doc_rec.program_application_id,
X_program_id => fnd_attached_doc_rec.program_id,
/* columns necessary for updating a fnd_document*/
X_datatype_id => fnd_doc_rec.datatype_id,
X_category_id => fnd_doc_rec.category_id,
X_security_type => fnd_doc_rec.security_type,
X_security_id => fnd_doc_rec.security_id,
X_publish_flag => fnd_doc_rec.publish_flag,
X_image_type => fnd_doc_rec.image_type,
X_storage_type => fnd_doc_rec.storage_type,
X_usage_type => fnd_doc_rec.usage_type,
X_start_date_active => fnd_doc_rec.start_date_active,
X_end_date_active => fnd_doc_rec.end_date_active,
X_language => fnd_doc_rec.language,
X_description => fnd_doc_rec.description,
X_file_name => fnd_doc_rec.file_name,
X_url => fnd_doc_rec.url,
X_media_id => fnd_doc_rec.media_id);
OKC_CONTRACT_DOCS_PVT.Update_Row(
x_return_status => l_return_status,
p_business_document_type => attach_rec.business_document_type,
p_business_document_id => attach_rec.business_document_id,
p_business_document_version => attach_rec.business_document_version,
p_attached_document_id => attach_rec.attached_document_id,
p_external_visibility_flag => attach_rec.external_visibility_flag,
p_effective_from_type => attach_rec.effective_from_type,
p_effective_from_id => attach_rec.effective_from_id,
p_effective_from_version => attach_rec.effective_from_version,
p_include_for_approval_flag => attach_rec.include_for_approval_flag,
p_program_id => attach_rec.program_id,
p_program_application_id => attach_rec.program_application_id,
p_request_id => attach_rec.request_id,
p_program_update_date => attach_rec.program_update_date,
p_parent_attached_doc_id => attach_rec.parent_attached_doc_id,
p_generated_flag => attach_rec.generated_flag,
p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
p_delete_flag => 'Y',
p_object_version_number => p_object_version_number
);
SELECT d.datatype_id INTO l_datatype_id
FROM fnd_documents d, fnd_attached_documents ad
WHERE d.document_id = ad.document_id
AND ad.attached_document_id = p_attached_document_id;
FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
X_attached_document_id => p_attached_document_id,
X_datatype_id => l_datatype_id,
delete_document_flag => 'Y');
OKC_CONTRACT_DOCS_PVT.Delete_Row(
x_return_status => x_return_status,
p_business_document_type => p_business_document_type,
p_business_document_id => p_business_document_id,
p_business_document_version => p_business_document_version,
p_attached_document_id => p_attached_document_id,
p_object_version_number => p_object_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving Delete_Contract_Doc');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving Delete_Contract_Doc: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_Delete_Contract_Doc_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2500: Leaving Delete_Contract_Doc: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_Delete_Contract_Doc_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Leaving Delete_Contract_Doc because of EXCEPTION: '||sqlerrm);
ROLLBACK TO g_Delete_Contract_Doc_GRP;
END Delete_Contract_Doc;
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version = l_bus_doc_version;
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version = l_curr_version
AND effective_from_type = l_bus_doc_type
AND effective_from_id = l_bus_doc_id
AND effective_from_version < l_bus_doc_version
AND effective_from_version <> l_curr_version
AND delete_flag = 'Y';
SELECT d.datatype_id
FROM fnd_documents d, fnd_attached_documents ad
WHERE d.document_id = ad.document_id
AND ad.attached_document_id = l_attached_document_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2731: Inserting New Ref recpord');
Insert_Contract_Doc (
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_business_document_type => attach_rec.business_document_type,
p_business_document_id => attach_rec.business_document_id,
p_business_document_version => p_business_document_version,
p_attached_document_id => attach_rec.attached_document_id,
p_external_visibility_flag => attach_rec.external_visibility_flag,
p_effective_from_type => attach_rec.effective_from_type,
p_effective_from_id => attach_rec.effective_from_id,
p_effective_from_version => l_effective_from_version,
p_include_for_approval_flag => attach_rec.include_for_approval_flag,
p_generated_flag => attach_rec.generated_flag,
p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
p_delete_flag => attach_rec.delete_flag,
p_create_fnd_attach => 'Y',
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_parent_attached_doc_id => attach_rec.parent_attached_doc_id,
p_versioning_flag => 'Y',
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status,
x_business_document_type => l_business_document_type,
x_business_document_id => l_business_document_id,
x_business_document_version => l_business_document_version,
x_attached_document_id => l_attached_document_id);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2732: Inserting New Non-Ref recpord');
Insert_Contract_Doc (
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_business_document_type => attach_rec.business_document_type,
p_business_document_id => attach_rec.business_document_id,
p_business_document_version => p_business_document_version,
p_attached_document_id => attach_rec.attached_document_id,
p_external_visibility_flag => attach_rec.external_visibility_flag,
p_effective_from_type => attach_rec.effective_from_type,
p_effective_from_id => attach_rec.effective_from_id,
p_effective_from_version => l_effective_from_version,
p_include_for_approval_flag => attach_rec.include_for_approval_flag,
p_generated_flag => attach_rec.generated_flag,
p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
p_delete_flag => attach_rec.delete_flag,
p_create_fnd_attach => 'Y',
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_versioning_flag => 'Y',
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status,
x_business_document_type => l_business_document_type,
x_business_document_id => l_business_document_id,
x_business_document_version => l_business_document_version,
x_attached_document_id => l_attached_document_id);
Update_Contract_Doc (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_business_document_type => attach_rec.business_document_type,
p_business_document_id => attach_rec.business_document_id,
p_business_document_version => attach_rec.business_document_version,
p_attached_document_id => attach_rec.attached_document_id,
p_external_visibility_flag => attach_rec.external_visibility_flag,
p_effective_from_type => attach_rec.effective_from_type,
p_effective_from_id => attach_rec.effective_from_id,
p_effective_from_version => l_effective_from_version,
p_include_for_approval_flag => attach_rec.include_for_approval_flag,
p_generated_flag => attach_rec.generated_flag,
p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
p_delete_flag => attach_rec.delete_flag,
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_parent_attached_doc_id => l_parent_attached_doc_id,
p_object_version_number => attach_rec.object_version_number,
p_versioning_flag => 'Y',
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
X_attached_document_id => attach_rec.attached_document_id,
X_datatype_id => l_datatype_id,
delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
OKC_CONTRACT_DOCS_PVT.Delete_Row(
x_return_status => l_return_status,
p_business_document_type => attach_rec.business_document_type,
p_business_document_id => attach_rec.business_document_id,
p_business_document_version => attach_rec.business_document_version,
p_attached_document_id => attach_rec.attached_document_id,
p_object_version_number => attach_rec.object_version_number
);
FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
X_attached_document_id => ref_del_rec.attached_document_id,
X_datatype_id => l_datatype_id,
delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
OKC_CONTRACT_DOCS_PVT.Delete_Row(
x_return_status => l_return_status,
p_business_document_type => ref_del_rec.business_document_type,
p_business_document_id => ref_del_rec.business_document_id,
p_business_document_version => ref_del_rec.business_document_version,
p_attached_document_id => ref_del_rec.attached_document_id,
p_object_version_number => ref_del_rec.object_version_number
);
PROCEDURE Delete_Ver_Attachments(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_business_document_type IN VARCHAR2,
p_business_document_id IN NUMBER,
p_business_document_version IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Ver_Attachments';
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version = l_bus_doc_version;
'3200: Entered Delete_Ver_Attachments');
SAVEPOINT Delete_Ver_Attachments_GRP;
Delete_Contract_Doc (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_business_document_type => p_business_document_type,
p_business_document_id => p_business_document_id,
p_business_document_version => p_business_document_version,
p_attached_document_id => attach_rec.attached_document_id,
p_object_version_number => NULL,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3300: Leaving Delete_Ver_Attachments');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3400: Leaving Delete_Ver_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO Delete_Ver_Attachments_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Leaving Delete_Ver_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO Delete_Ver_Attachments_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Leaving Delete_Ver_Attachments because of EXCEPTION: '||sqlerrm);
ROLLBACK TO Delete_Ver_Attachments_GRP;
END Delete_Ver_Attachments;
PROCEDURE Delete_Doc_Attachments(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_business_document_type IN VARCHAR2,
p_business_document_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc_Attachments';
SELECT distinct business_document_version FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version <> -99
ORDER by business_document_version DESC ;
'3900: Entered Delete_Doc_Attachments');
SAVEPOINT Delete_Doc_Attachments_GRP;
Delete_Ver_Attachments (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_commit => FND_API.G_FALSE,
p_business_document_type => p_business_document_type,
p_business_document_id => p_business_document_id,
p_business_document_version => -99,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
Delete_Ver_Attachments (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_commit => FND_API.G_FALSE,
p_business_document_type => p_business_document_type,
p_business_document_id => p_business_document_id,
p_business_document_version => attach_rec.business_document_version,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3900: Leaving Delete_Doc_Attachments');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving Delete_Doc_Attachments: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO Delete_Doc_Attachments_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4100: Leaving Delete_Doc_Attachments: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO Delete_Doc_Attachments_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Leaving Delete_Doc_Attachments because of EXCEPTION: '||sqlerrm);
ROLLBACK TO Delete_Doc_Attachments_GRP;
END Delete_Doc_Attachments;
SELECT
doc_version_view
FROM okc_bus_doc_types_b
WHERE document_type = l_from_bus_doc_type;
SELECT
KDOC.attached_document_id from_attached_document_id,
KDOC.external_visibility_flag external_visibility_flag,
KDOC.parent_attached_doc_id parent_attached_doc_id,
KDOC.include_for_approval_flag include_for_approval_flag,
KDOC.generated_flag generated_flag,
KDOC.delete_flag delete_flag,
FADB2.attached_document_id to_attached_document_id,
KDOC.primary_contract_doc_flag,
KDOC.mergeable_doc_flag
FROM FND_ATTACHED_DOCUMENTS FADB1, OKC_CONTRACT_DOCS KDOC,
FND_ATTACHED_DOCUMENTS FADB2
WHERE
KDOC.business_document_type = l_from_bus_doc_type
AND KDOC.business_document_id = l_from_bus_doc_id
AND KDOC.business_document_version = l_from_bus_doc_version
AND KDOC.attached_document_id = FADB1.attached_document_id
AND NVL(KDOC.delete_flag,'N') = 'N'
AND FADB2.entity_name = l_entity_name
AND FADB2.pk1_value = l_to_bus_doc_type
AND FADB2.pk2_value = to_char(l_to_bus_doc_id)
AND FADB2.pk3_value = to_char(l_to_bus_doc_version)
AND FADB2.program_id = l_program_id
AND FADB1.seq_num = FADB2.seq_num;
SELECT category_id from FND_DOCUMENT_CATEGORIES
WHERE name in (G_CONTRACT_DOC_CATEGORY, G_SUPPORTING_DOC_CATEGORY);
SELECT category_id from FND_DOCUMENT_CATEGORIES
WHERE name in (G_CONTRACT_DOC_CATEGORY, G_SUPPORTING_DOC_CATEGORY, G_APP_ABSTRACT_CATEGORY, G_CONTRACT_IMAGE_CATEGORY);
SELECT category_id from FND_DOCUMENT_CATEGORIES
WHERE name in (G_CONTRACT_DOC_CATEGORY);
SELECT category_id from FND_DOCUMENT_CATEGORIES
WHERE name like 'OKC_REPO_%';
l_version_query := ' SELECT decode(a.archived_yn,''Y'',a.document_version,-99) latest_doc_version
FROM '||l_doc_version_view_name||' a
WHERE decode(a.document_version ,
(SELECT max(b.document_version)
FROM '||l_doc_version_view_name||' b
WHERE b.document_type = a.document_type
AND b.document_id = a.document_id) ,''Y'' ,''N'' ) = ''Y''
AND a.document_type = '''||p_from_bus_doc_type||'''
AND a.document_id = '''||p_from_bus_doc_id||'''';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4835:The delete_flag is: '
|| attach_rec.delete_flag);
Insert_Contract_Doc (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_business_document_type => p_to_bus_doc_type,
p_business_document_id => p_to_bus_doc_id,
p_business_document_version => p_to_bus_doc_version,
p_attached_document_id => attach_rec.to_attached_document_id,
p_external_visibility_flag => attach_rec.external_visibility_flag,
p_effective_from_type => p_to_bus_doc_type,
p_effective_from_id => p_to_bus_doc_id,
p_effective_from_version => p_to_bus_doc_version,
p_include_for_approval_flag => attach_rec.include_for_approval_flag,
p_generated_flag => attach_rec.generated_flag,
p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
p_delete_flag => attach_rec.delete_flag,
p_create_fnd_attach => 'N',
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_versioning_flag => 'Y', -- Do Not Update Primary Flag when Copying or Versioning
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status,
x_business_document_type => l_business_document_type,
x_business_document_id => l_business_document_id,
x_business_document_version => l_business_document_version,
x_attached_document_id => l_attached_document_id);
SELECT * FROM OKC_CONTRACT_DOCS
WHERE business_document_type = l_bus_doc_type
AND business_document_id = l_bus_doc_id
AND business_document_version = l_bus_doc_version
AND delete_flag = 'N';
SELECT fdc.name
FROM fnd_attached_documents fad, fnd_documents fd, fnd_document_categories fdc
WHERE fad.attached_document_id = l_attached_document_id
AND fad.document_id = fd.document_id
AND fd.category_id = fdc.category_id;
Insert_Contract_Doc (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_business_document_type => p_to_bus_doc_type,
p_business_document_id => p_to_bus_doc_id,
p_business_document_version => p_to_bus_doc_version,
p_attached_document_id => attach_rec.attached_document_id,
p_external_visibility_flag => attach_rec.external_visibility_flag,
p_effective_from_type => attach_rec.effective_from_type,
p_effective_from_id => attach_rec.effective_from_id,
p_effective_from_version => attach_rec.effective_from_version,
p_include_for_approval_flag => attach_rec.include_for_approval_flag,
p_generated_flag => attach_rec.generated_flag,
p_primary_contract_doc_flag => attach_rec.primary_contract_doc_flag,
p_mergeable_doc_flag => attach_rec.mergeable_doc_flag,
p_delete_flag => attach_rec.delete_flag,
p_create_fnd_attach => 'Y',
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_parent_attached_doc_id => l_parent_attached_doc_id,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status,
x_business_document_type => l_business_document_type,
x_business_document_id => l_business_document_id,
x_business_document_version => l_business_document_version,
x_attached_document_id => l_attached_document_id);
SELECT LOOKUPS.meaning qa_name,
nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
decode(LOOKUPS.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
FROM OKC_LOOKUPS_V LOOKUPS, OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
AND QA.QA_CODE(+) = LOOKUPS.LOOKUP_CODE
AND LOOKUPS.LOOKUP_TYPE = 'OKC_TERM_QA_LIST'
AND LOOKUPS.LOOKUP_CODE='CHECK_PRIMARY_CONTRACT';
SELECT 'Y'
FROM okc_contract_docs
WHERE business_document_type = p_document_type
AND business_document_id = p_document_id
AND mergeable_doc_flag='Y'
AND primary_contract_doc_flag='Y'
AND NVL(delete_flag,'N') = 'N'
AND business_document_version = -99;
SELECT b.media_id , docs.mergeable_doc_flag
FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd, FND_DOCUMENTS b
WHERE docs.primary_contract_doc_flag = 'Y'
AND NVL(docs.delete_flag,'N') = 'N'
AND docs.business_document_version = -99
AND docs.business_document_type = p_document_type
AND docs.business_document_id = p_document_id
AND docs.attached_document_id = fnd.attached_document_id
AND fnd.document_id = b.document_id;
SELECT '!'
FROM OKC_CONTRACT_DOCS
WHERE business_document_type = p_document_type
AND business_document_id = p_document_id
AND primary_contract_doc_flag = 'Y'
AND NVL(delete_flag,'N') = 'N'
AND business_document_version = -99;
SELECT attached_document_id,object_version_number
FROM OKC_CONTRACT_DOCS
WHERE business_document_type = p_document_type
AND business_document_id = p_document_id
AND primary_contract_doc_flag = 'Y'
AND NVL(delete_flag,'N') = 'N'
AND business_document_version = -99;
OKC_CONTRACT_DOCS_PVT.update_row(
x_return_status => x_return_status,
p_business_document_type => p_document_type,
p_business_document_id => p_document_id,
p_business_document_version => -99,
p_attached_document_id => l_attached_document_id,
p_primary_contract_doc_flag => 'N',
p_object_version_number => l_object_version_number );