[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT active_start_date, active_end_date, contract_expiry_type,object_version_number,item_instance_id
FROM ahl_warranty_contracts_b
WHERE warranty_contract_id = c_warranty_contract_id
FOR UPDATE of expiration_date ;
SELECT cntrt.warranty_cntrt_counter_id,cntrt.counter_id,cntrt.object_version_number,ctr.uom_code,cntrt.threshold,cntrt.start_value
FROM ahl_warranty_cont_ctr_b cntrt, cs_counters ctr
WHERE cntrt.warranty_contract_id = c_warranty_contract_id
AND cntrt.counter_id = ctr.counter_id
FOR UPDATE of expiration_date ;
l_warranty_counter_tbl.DELETE;
--Update Contract Counter table with new expiration date
FORALL i IN l_warranty_counter_tbl.FIRST..l_warranty_counter_tbl.LAST
UPDATE AHL_WARRANTY_CONT_CTR_B SET EXPIRATION_DATE = l_warranty_counter_tbl(i).counter_expiration_date,
OBJECT_VERSION_NUMBER = l_warranty_counter_tbl(i).object_version_number+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
SECURITY_GROUP_ID = NULL
WHERE WARRANTY_CNTRT_COUNTER_ID = l_warranty_counter_tbl(i).warranty_cntrt_counter_id;
UPDATE AHL_WARRANTY_CONTRACTS_B SET EXPIRATION_DATE = l_exp_date,
OBJECT_VERSION_NUMBER = l_cont_ovn+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
SECURITY_GROUP_ID = NULL
WHERE WARRANTY_CONTRACT_ID = l_warranty_contract_id ;
SELECT warranty_contract_id
FROM ahl_warranty_contracts_b;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Successfully calculated and updated expiration date for '|| l_count ||' warranty contracts. ');
SELECT warranty_template_id,object_version_number,enabled_flag
FROM ahl_warranty_templates_b
WHERE SYSDATE > nvl(active_end_date,SYSDATE)
AND enabled_flag = 'Y'
FOR UPDATE of enabled_flag ;
UPDATE AHL_WARRANTY_TEMPLATES_B SET ENABLED_FLAG = 'N',
OBJECT_VERSION_NUMBER = csrTbl(i).object_version_number+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
SECURITY_GROUP_ID = NULL
WHERE WARRANTY_TEMPLATE_ID = csrTbl(i).warranty_template_id;
CURSOR update_enabled_flag_csr (c_vendor_id IN NUMBER)
IS
SELECT warranty_template_id,warranty_vendor_id,object_version_number,enabled_flag
FROM ahl_warranty_templates_b
WHERE warranty_vendor_id = c_vendor_id
FOR UPDATE of enabled_flag ;
UPDATE AHL_WARRANTY_TEMPLATES_B SET ENABLED_FLAG = 'N',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id
WHERE warranty_vendor_id = p_warranty_vendor_id_tbl(i)
AND ENABLED_FLAG = 'Y' ;
FOR csr_index in update_enabled_flag_csr(p_warranty_vendor_id)
LOOP
UPDATE AHL_WARRANTY_TEMPLATES_B SET ENABLED_FLAG = 'N',
OBJECT_VERSION_NUMBER = csr_index.object_version_number+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
SECURITY_GROUP_ID = NULL
WHERE WARRANTY_TEMPLATE_ID = csr_index.warranty_template_id;
SELECT attach.*,docs.datatype_id
FROM fnd_attached_documents attach, fnd_documents docs
WHERE entity_name = c_entity_name
AND pk1_value = c_pk1_value
AND docs.document_id = attach.document_id;
SELECT *
FROM fnd_documents_vl docs
WHERE document_id = c_document_id;
SELECT *
FROM fnd_lobs
WHERE file_id = c_file_id;
fnd_documents_pkg.Delete_Row(csr_index.document_id,
csr_index.datatype_id,
'Y');
SELECT fnd_attached_documents_s.NEXTVAL into l_attch_doc_id from dual;
FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
X_Rowid =>l_row_char,
X_attached_document_id => l_attch_doc_id,
X_document_id => l_doc_id,
X_creation_date => sysdate,
X_created_by => l_user_id,
X_last_update_date => sysdate,
X_last_updated_by => l_user_id,
X_last_update_login => l_login_id ,
X_seq_num => csr_index.seq_num,
X_entity_name =>p_entity_name,
X_column1 =>null,
X_pk1_value =>p_pk1_value,
X_pk2_value =>null,
X_pk3_value =>null,
X_pk4_value =>null,
X_pk5_value =>null,
X_automatically_added_flag => 'N',
X_request_id =>null,
X_program_application_id =>null,
X_program_id =>null,
X_program_update_date =>null,
X_Attribute_Category =>csr_index.Attribute_Category,
X_Attribute1 =>csr_index.Attribute1,
X_Attribute2 =>csr_index.Attribute2,
X_Attribute3 =>csr_index.Attribute3,
X_Attribute4 =>csr_index.Attribute4,
X_Attribute5 =>csr_index.Attribute5,
X_Attribute6 =>csr_index.Attribute6,
X_Attribute7 =>csr_index.Attribute7,
X_Attribute8 =>csr_index.Attribute8,
X_Attribute9 =>csr_index.Attribute9,
X_Attribute10 =>csr_index.Attribute10,
X_Attribute11 =>csr_index.Attribute11,
X_Attribute12 =>csr_index.Attribute12,
X_Attribute13 =>csr_index.Attribute13,
X_Attribute14 =>csr_index.Attribute14,
X_Attribute15 =>csr_index.Attribute15,
/* columns necessary for creating a document on the fly */
X_datatype_id =>l_data_type,
X_category_id =>csr_index.category_id,
X_security_type =>document_rec.security_type, --csr_index.security_type,
X_security_id =>document_rec.security_id,
X_publish_flag =>document_rec.publish_flag,
X_image_type =>document_rec.image_type,
X_storage_type =>document_rec.storage_type,
X_usage_type => 'O',
X_language =>userenv('LANG'),
X_description =>document_rec.description,
X_file_name =>document_rec.file_name,
X_media_id =>l_media_id,
X_doc_Attribute_Category =>document_rec.doc_Attribute_Category,
X_doc_Attribute1 =>document_rec.doc_Attribute1,
X_doc_Attribute2 =>document_rec.doc_Attribute2,
X_doc_Attribute3 =>document_rec.doc_Attribute3,
X_doc_Attribute4 =>document_rec.doc_Attribute4,
X_doc_Attribute5 =>document_rec.doc_Attribute5,
X_doc_Attribute6 =>document_rec.doc_Attribute6,
X_doc_Attribute7 =>document_rec.doc_Attribute7,
X_doc_Attribute8 =>document_rec.doc_Attribute8,
X_doc_Attribute9 =>document_rec.doc_Attribute9,
X_doc_Attribute10 =>document_rec.doc_Attribute10,
X_doc_Attribute11 =>document_rec.doc_Attribute11,
X_doc_Attribute12 =>document_rec.doc_Attribute12,
X_doc_Attribute13 =>document_rec.doc_Attribute13,
X_doc_Attribute14 =>document_rec.doc_Attribute14,
X_doc_Attribute15 =>document_rec.doc_Attribute15,
X_create_doc =>'Y',
X_url =>document_rec.url,
X_title =>document_rec.title);
insert into fnd_lobs
( file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format
)
values
( l_media_id,
file_rec.file_name,
file_rec.file_content_type,
sysdate,
null,
null,
null,
file_rec.file_data,
userenv('LANG'),
file_rec.oracle_charset,
file_rec.file_format
);
PROCEDURE Insert_Turnover_Notes (
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_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_default IN VARCHAR2 := FND_API.G_FALSE,
p_module_type IN VARCHAR2 := Null,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trunover_notes_tbl IN OUT NOCOPY AHL_WARRANTY_UTILS_PVT.Turnover_Notes_Tbl_Type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Turnover_Notes';
'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes.begin',
'At the start of PLSQL procedure'
);
SAVEPOINT Insert_Turnover_Notes;
'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
'p_init_message_list : ' || p_init_msg_list
);
'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
'p_commit : ' || p_commit
);
'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
'Invalid enterded date' || to_char(p_trunover_notes_tbl(i).entered_date)
);
'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes',
'Invalid enterded date' || to_char(p_trunover_notes_tbl(i).entered_date)
);
'ahl.plsql.AHL_WARRANTY_UTILS_PVT.Insert_Turnover_Notes.end',
'At the end of PLSQL procedure'
);
Rollback to Insert_Turnover_Notes;
Rollback to Insert_Turnover_Notes;
Rollback to Insert_Turnover_Notes;
END Insert_Turnover_Notes;