[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select value
INTO l_utl_file_dir
FROM v$parameter
WHERE name = 'utl_file_dir';
select attachment_id, previous_status,
decode(datatype_id, 8, family_id, source_document_id) document_id,
repository_id, created_by
from eng_attachment_changes
where change_id = p_change_id;
, p_last_updated_by => l_fnd_user_id
, p_last_update_login => l_fnd_login_id
);
Procedure Update_Attachment_Status
(
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_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL --
,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Update_Attachment_Status.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_id IN NUMBER -- header's change_id
,p_workflow_status IN VARCHAR2 -- workflow status
,p_approval_status IN NUMBER -- approval status
,p_api_caller IN VARCHAR2 DEFAULT 'UI'
)
IS
l_attachment_id number;
select attachment_id, previous_status, source_document_id, repository_id,
category_id, family_id, source_media_id, file_name,
datatype_id, created_by
from eng_attachment_changes
where change_id = p_change_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Attachment_Status';
l_update_approval_status BOOLEAN;
SAVEPOINT Update_Attachment_Status;
Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Update_Attachment_Status log');
l_update_approval_status:=false;
l_update_approval_status:=true;
IF (l_update_approval_status) THEN
-- If it is a webservices file, then pass the dm_document_id
-- else fnd_document_id
if (l_datatype_id = 8) then
l_doc_id := l_dm_document_id;
, p_last_updated_by => l_fnd_user_id
, p_last_update_login => l_fnd_login_id
);
END IF; -- IF (l_update_approval_status) THEN
ROLLBACK TO Update_Attachment_Status;
ROLLBACK TO Update_Attachment_Status;
ROLLBACK TO Update_Attachment_Status;
END Update_Attachment_Status;
select project_id, task_id, organization_id
from eng_engineering_changes
where change_id = p_change_id;
SELECT OPERATING_UNIT
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = p_orgid;
SELECT ecot.base_change_mgmt_type_code INTO l_base_change_mgmt_type_code
FROM eng_engineering_changes eec,eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
SELECT max(completed_percentage) into l_existing_comp_percent
FROM PA_TASK_PROGRESS_AMG_V
WHERE project_id = l_project_id and task_id = l_task_id ;
SELECT responsibility_id INTO l_responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = 'PA_PRM_PROJ_SU';
, p_last_update_login => l_fnd_login_id
);
, p_last_updated_by => l_fnd_user_id
, p_last_update_login => l_fnd_login_id
);
PA_STATUS_PUB.UPDATE_PROGRESS(
P_API_VERSION_NUMBER => 1 ,
P_RETURN_STATUS => x_return_status,
P_MSG_COUNT => x_msg_count,
P_MSG_DATA => x_msg_data,
P_PROJECT_ID => l_project_id,
P_TASK_ID => l_task_id,
P_AS_OF_DATE => sysdate,
P_STRUCTURE_TYPE => 'WORKPLAN',
P_PERCENT_COMPLETE => l_percent_complete,
p_task_status => l_task_status);
l_last_update_login NUMBER;
select a.action_type, a.attachment_id, a.source_document_id,
a.entity_name, a.pk1_value, a.pk2_value, a.pk3_value,
a.pk4_value, a.pk5_value, a.category_id, a.dest_version_label,
a.file_name, a.new_file_name, a.new_description,
a.new_category_id, a.created_by, a.last_update_login,
a.repository_id,
decode(b.protocol, 'WEBDAV', -1, a.family_id) family_id,
a.dm_type, b.protocol
from eng_attachment_changes a, dom_repositories b
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id
and a.repository_id = b.id;
SELECT fnd_global.user_id, fnd_global.login_id
INTO l_fnd_user_id, l_fnd_login_id
FROM dual;
l_last_update_login, l_repository_id, l_family_id, l_dm_type,
l_protocol;
SELECT document_id INTO l_document_id
FROM fnd_documents
WHERE document_id = l_source_document_id;
, p_last_update_login => l_last_update_login
);
, p_last_updated_by => l_created_by
, p_last_update_login => l_last_update_login
);
dom_attachment_util_pkg.Update_Document(
p_Attached_document_id => l_attachment_id
, p_FileName => l_new_file_name
, p_Description => l_new_description
, p_Category => l_new_category_id
, p_last_updated_by => l_created_by
, p_last_update_login => l_last_update_login
);
, p_last_updated_by => l_fnd_user_id
, p_last_update_login => l_fnd_login_id
);
select userenv('LANG') into l_language from dual;
select category_id, document_id into l_category_id, l_source_doc_id from fnd_attached_documents where attached_document_id = p_source_attachment_id;
select datatype_id, security_type, dm_node, dm_folder_path, dm_type, dm_document_id, dm_version_number
into l_datatype_id, l_security_type, l_dm_node, l_dm_folder_path, l_dm_type, l_dm_document_id, l_dm_version_number
from fnd_documents where document_id = l_source_doc_id;
select file_name, description, media_id into l_file_name, l_description, l_media_id from fnd_documents_tl where document_id = l_source_doc_id and language = userenv('LANG');
select fnd_attached_documents_s.nextval
into l_attached_doc_id
from dual;
fnd_attached_documents_pkg.Insert_Row(
X_Rowid => l_row_id,
X_attached_document_id => l_attached_doc_id,
X_document_id => l_doc_id,
X_creation_date => sysdate,
X_created_by => l_fnd_user_id,
X_last_update_date => sysdate,
X_last_updated_by => l_fnd_user_id,
X_last_update_login => l_fnd_login_id,
X_seq_num => l_seq_num,
X_entity_name => p_dest_entity_name,
X_column1 => null,
X_pk1_value => p_dest_pk1_value,
X_pk2_value => p_dest_pk2_value,
X_pk3_value => p_dest_pk3_value,
X_pk4_value => p_dest_pk4_value,
X_pk5_value => p_dest_pk5_value,
X_automatically_added_flag => l_auto_add_flag,
X_datatype_id => l_datatype_id,
X_category_id => l_category_id,
X_security_type => l_security_type,
X_publish_flag => l_publish_flag,
X_usage_type => l_usage_type,
X_language => l_language,
X_description => l_description,
X_file_name => l_file_name,
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 => 'Y' -- Fix for 3762710
);
update fnd_attached_documents set category_id = l_category_id, status = p_source_status where attached_document_id = l_attached_doc_id;
update fnd_documents set dm_node = l_dm_node, dm_folder_path = l_dm_folder_path, dm_type = l_dm_type, dm_document_id = l_dm_document_id, dm_version_number = l_dm_version_number where document_id = l_doc_id;
select change_order_type_id into l_change_order_type_id
from eng_engineering_changes
where change_id = p_change_id;
select base_change_mgmt_type_code into l_base_change_mgmt_type_code
from eng_change_order_types
where change_order_type_id = l_change_order_type_id;
select attachment_id
from eng_attachment_changes
where change_id = p_change_id;
update fnd_attached_documents
set status = p_approval_status, last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
where attached_document_id = l_attachment_id;
select attached_document_id, status
from fnd_attached_documents
where entity_name='MTL_ITEM_REVISIONS'
and pk1_value = p_org_id
and pk2_value = p_inv_item_id
and pk3_value = p_curr_rev_id;
select count(*)
into l_count
from fnd_attached_documents
where entity_name='MTL_ITEM_REVISIONS'
and pk1_value = p_org_id
and pk2_value = p_inv_item_id
and pk3_value = p_new_rev_id;
x_last_update_login => fnd_global.login_id,
x_program_application_id => '',
x_program_id => fnd_global.conc_program_id,
x_request_id => fnd_global.conc_request_id
);
update eng_attachment_changes
set attachment_id = l_new_attachment_id
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id
and attachment_id = l_attachment_id
and action_type = 'DETACH';
update fnd_attached_documents
set pk3_value = p_new_rev_id
where attached_document_id = l_attachment_id
and exists
(select change_document_id
from eng_attachment_changes
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id
and attachment_id = l_attachment_id);
Procedure Delete_Attachments_And_Changes (
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_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL --
,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments_And_Changes.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2
,p_change_id IN NUMBER -- header's change_id
,p_rev_item_seq_id IN NUMBER -- revised item sequence id
,p_org_id IN VARCHAR2
,p_inv_item_id IN VARCHAR2
,p_revision_id IN VARCHAR2
)
IS
cursor C IS
select attached_document_id, document_id
from fnd_attached_documents
where pk1_value = p_org_id
and pk2_value = p_inv_item_id
-- and (
-- pk3_value = p_revision_id
-- or
-- (pk3_value is null and p_revision_id is null)
-- ) -- commenting this out so that item level changes also get deleted when rev item is removed
-- this is ok since attached_document_id is only pk anyway
and attached_document_id in
(select attachment_id
from eng_attachment_changes
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id
and action_type = 'ATTACH'); -- fix for 3771466
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments_And_Changes';
SAVEPOINT Delete_Attachments_And_Changes;
Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachments_And_Changes log');
select datatype_id into l_datatype_id from fnd_documents where document_id = l_document_id;
fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
delete from eng_attachment_changes
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id;
ROLLBACK TO Delete_Attachments_And_Changes;
ROLLBACK TO Delete_Attachments_And_Changes;
ROLLBACK TO Delete_Attachments_And_Changes;
END Delete_Attachments_And_Changes;
Procedure Delete_Attachments_For_Curr_CO (
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_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL --
,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments_For_Curr_CO.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2
,p_change_id IN NUMBER -- header's change_id
,p_rev_item_seq_id IN NUMBER -- revised item sequence id
)
IS
Cursor C IS
select source_document_id
from eng_attachment_changes
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id
and action_type = 'ATTACH';
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments_For_Curr_CO';
SAVEPOINT Delete_Attachments_For_Curr_CO;
Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachment_For_Curr_CO log');
select datatype_id into l_datatype_id
from fnd_documents
where document_id = l_document_id;
SELECT count(*) into l_document_exists
FROM eng_attachment_changes
WHERE source_document_id = l_document_id
AND change_id <> p_change_id
AND revised_item_sequence_id <> p_rev_item_seq_id;
fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'N');
ROLLBACK TO Delete_Attachments_For_Curr_CO;
ROLLBACK TO Delete_Attachments_For_Curr_CO;
ROLLBACK TO Delete_Attachments_For_Curr_CO;
END Delete_Attachments_For_Curr_CO;
Procedure Delete_Attachments (
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_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL --
,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2
,p_org_id IN VARCHAR2
,p_inv_item_id IN VARCHAR2
,p_revision_id IN VARCHAR2
)
IS
cursor C IS
select attached_document_id, document_id
from fnd_attached_documents
where pk1_value = p_org_id
and pk2_value = p_inv_item_id
and pk3_value = p_revision_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments';
SAVEPOINT Delete_Attachments;
Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachments log');
select datatype_id into l_datatype_id from fnd_documents where document_id = l_document_id;
fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
ROLLBACK TO Delete_Attachments;
ROLLBACK TO Delete_Attachments;
ROLLBACK TO Delete_Attachments;
END Delete_Attachments;
Procedure Delete_Attachment (
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_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL --
,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachment.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2
,p_attachment_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachment';
SAVEPOINT Delete_Attachment;
Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachment log');
select document_id into l_document_id
from fnd_attached_documents
where attached_document_id = p_attachment_id;
select datatype_id into l_datatype_id
from fnd_documents
where document_id = l_document_id;
fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
ROLLBACK TO Delete_Attachment;
ROLLBACK TO Delete_Attachment;
ROLLBACK TO Delete_Attachment;
END Delete_Attachment;
Procedure Delete_Changes (
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_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL --
,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Changes.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2
,p_change_id IN NUMBER -- header's change_id
,p_rev_item_seq_id IN NUMBER -- revised item sequence id
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Changes';
SAVEPOINT Delete_Changes;
Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Changes log');
delete from eng_attachment_changes
where change_id = p_change_id
and revised_item_sequence_id = p_rev_item_seq_id;
ROLLBACK TO Delete_Changes;
ROLLBACK TO Delete_Changes;
ROLLBACK TO Delete_Changes;
END Delete_Changes;
select change_id, revised_item_sequence_id,
category_id, file_name, source_path,
pk1_value, pk2_value, pk3_value
from eng_attachment_changes a
where a.change_id = l_change_id
and datatype_id = 8 -- only webservices files
and family_id = 0 -- for floating version files
and action_type in ('ATTACH','CHANGE_REVISION','CHANGE_VERSION_LABEL')
and revised_item_sequence_id in (select decode(l_revised_item_seq_id,null, (select revised_item_sequence_id
from eng_revised_items
where change_id = a.change_id),
l_revised_item_seq_id) from dual);
SELECT ecp.policy_char_value INTO l_change_policy
FROM
(select nvl(mirb.lifecycle_id, msi.lifecycle_id) as lifecycle_id,
nvl(mirb.current_phase_id , msi.current_phase_id) as phase_id,
msi.item_catalog_group_id item_catalog_group_id,
msi.inventory_item_id, msi.organization_id , mirb.revision_id
from mtl_item_revisions_b mirb,
MTL_SYSTEM_ITEMS msi
where mirb.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
and mirb.ORGANIZATION_ID(+)= msi.ORGANIZATION_ID
and mirb.revision_id(+) = c2.pk3_value
and msi.INVENTORY_ITEM_ID = c2.pk2_value
and msi.ORGANIZATION_ID = c2.pk1_value) ITEM_DTLS,
ENG_CHANGE_POLICIES_V ECP
WHERE
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
AND ecp.policy_object_pk3_value = ITEM_DTLS.phase_id
and ecp.policy_object_name = 'CATALOG_LIFECYCLE_PHASE'
and ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
and ecp.attribute_code = 'ATTACHMENT'
and attribute_number_value = c2.category_id;