[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
datatype_id NUMBER,
description fnd_documents_tl.description%TYPE,
file_name fnd_documents.file_name%TYPE,
media_id fnd_documents.media_id%TYPE,
category_id fnd_documents.category_id%TYPE,
security_type fnd_documents.security_type%TYPE,
publish_flag fnd_documents.publish_flag%TYPE,
usage_type fnd_documents.usage_type%TYPE,
dm_node fnd_documents.dm_node%TYPE,
title fnd_documents_tl.title%TYPE);
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
seq_num NUMBER,
entity_name fnd_attached_documents.entity_name%TYPE,
pk1_value fnd_attached_documents.pk1_value%TYPE,
automatically_added_flag fnd_attached_documents.automatically_added_Flag%TYPE,
attachment_category_id NUMBER);
IF gt_doc(i).post_state = 0 THEN --- Post state is Insert
l_document_id := NULL;
fnd_documents_pkg.Insert_Row(X_Rowid => l_rowid,
X_document_id => l_document_id,
X_creation_date => gt_doc(i).creation_date,
X_created_by => gt_doc(i).created_by,
X_last_update_date => gt_doc(i).last_update_date,
X_last_updated_by => gt_doc(i).last_updated_by,
X_last_update_login => gt_doc(i).last_update_login,
X_datatype_id => gt_doc(i).datatype_id,
X_category_id => gt_doc(i).category_id,
X_security_type => gt_doc(i).security_type,
X_publish_flag => gt_doc(i).publish_flag,
X_usage_type => gt_doc(i).usage_type,
X_language => USERENV('lang'),
X_description => gt_doc(i).description,
X_file_name => gt_doc(i).file_name,
X_media_id => l_media_id,
X_create_doc => 'N',
X_title => gt_doc(i).title);
IF gt_doc(i).media_id = gt_shorttext(j).media_id THEN -- insert only if the media id matches
HR_UTILITY.TRACE('INSERTING MEDIA ID: '||GT_SHORTTEXT(J).MEDIA_ID);
INSERT INTO FND_DOCUMENTS_SHORT_TEXT
(
media_id
,short_text
,app_source_version
) VALUES
(
l_media_id
,gt_shorttext(j).short_text
,gt_shorttext(j).app_source_version
);
IF gt_doc(i).media_id = gt_lob(j).file_id THEN -- insert only if the media id matches
INSERT INTO FND_LOBS
(
FILE_ID
,FILE_NAME
,FILE_CONTENT_TYPE
,FILE_DATA
,UPLOAD_DATE
,ORACLE_CHARSET
,FILE_FORMAT
) VALUES
(
gt_lob(j).file_id
,gt_lob(j).file_Name
,gt_lob(j).file_content_type
,gt_lob(j).file_data
,SYSDATE
,gt_lob(j).oracle_charset
,gt_lob(j).file_format
);
INSERT INTO fnd_attached_documents (
attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
column1,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
automatically_added_flag,
category_id) VALUES (
gt_attachdoc(k).attached_document_id,
l_document_id,
gt_attachdoc(k).creation_date,
gt_attachdoc(k).created_by,
gt_attachdoc(k).last_update_date,
gt_attachdoc(k).last_updated_by,
gt_attachdoc(k).last_update_login,
gt_attachdoc(k).seq_num,
gt_attachdoc(k).entity_name,
NULL,
gt_attachdoc(k).pk1_value,
NULL,
NULL,
NULL,
NULL,
gt_attachdoc(k).automatically_added_flag,
gt_attachdoc(k).attachment_category_id);
SELECT transaction_document
FROM hr_api_transactions
WHERE transaction_id = p_transaction_id;
gt_doc.DELETE;
gt_attachdoc.DELETE;
gt_lob.DELETE;
gt_shorttext.DELETE;
gt_doc(j+1).last_update_date := hr_transaction_swi.getDateValue(n,'LastUpdateDate',null);
gt_doc(j+1).Last_Updated_By := hr_transaction_swi.getNumberValue(n,'LastUpdatedBy',null);
gt_doc(j+1).Last_Update_Login := hr_transaction_swi.getNumberValue(n,'LastUpdateLogin',null);
gt_attachdoc(j+1).Last_Update_Date := hr_transaction_swi.getDateValue(n,'LastUpdateDate',null);
gt_attachdoc(j+1).Last_Updated_By := hr_transaction_swi.getNumberValue(n,'LastUpdatedBy',null);
gt_attachdoc(j+1).Last_Update_Login := hr_transaction_swi.getNumberValue(n,'LastUpdateLogin',null);
INSERT INTO FND_LOBS
(
FILE_ID
,FILE_NAME
,FILE_CONTENT_TYPE
,FILE_DATA
,UPLOAD_DATE
,ORACLE_CHARSET
,FILE_FORMAT
) VALUES
(
gt_lob(j+1).file_ID
,gt_lob(j+1).file_Name
,gt_lob(j+1).file_content_type
,gt_lob(j+1).file_data
,SYSDATE
,gt_lob(j+1).oracle_charset
,gt_lob(j+1).file_format
);
procedure update_score_cards(p_score_card_list varchar2 default null,
p_sc_ovn_list varchar2 default null,
p_sc_latest_ovn_list in out nocopy varchar2)
is
l_sc_ovn_list varchar2(1000);
select scorecard_id, object_version_number from per_personal_scorecards
where scorecard_id = p_sc_id;
hr_personal_scorecard_swi.update_scorecard_status
(p_validate => hr_Api.g_false_num
,p_effective_date => trunc(sysdate) -- to be
,p_scorecard_id => l_sc_id
,p_object_version_number => l_sc_ovn
,p_status_code => 'TRANSFER'
,p_return_status => api_return_status
);
hr_personal_scorecard_swi.update_scorecard_status
(p_validate => hr_Api.g_false_num
,p_effective_date => trunc(sysdate) -- to be
,p_scorecard_id => score_card_list.scorecard_id
,p_object_version_number => l_temp_ovn
,p_status_code => 'TRANSFER'
,p_return_status => api_return_status
);
end update_score_cards;
select global_name from per_people_f
where person_id = p_person_id
and trunc(sysdate) between effective_start_date and effective_end_date;
select name from wf_roles
where orig_system_id = p_person_id
and orig_system = 'PER';
select hr_workflow_item_key_s.NEXTVAL into item_key_number from dual ;
update_score_cards(score_card_list, sc_ovn_list, l_sc_latest_ovn_list);
select wf.name wf_role, people.global_name emp_name, sc.scorecard_name, sc.plan_id
from per_personal_scorecards sc, per_all_assignments_f asgn,
per_all_people_f people, wf_roles wf
where sc.scorecard_id = p_score_card_id
and asgn.assignment_id = sc.assignment_id
and trunc(sysdate) between asgn.effective_start_date and asgn.effective_end_date
and asgn.person_id = people.person_id
and wf.orig_system_id = people.person_id
and wf.orig_system = 'PER'
and trunc(sysdate) between people.effective_start_date and people.effective_end_date;
select transaction_id from hr_api_transactions
where transaction_ref_id = p_score_card_id
and transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
and creator_person_id = txn_owner;
select object_version_number from per_personal_scorecards
where scorecard_id = p_sc_id;
hr_personal_scorecard_swi.update_scorecard_status
(p_validate => hr_Api.g_false_num
,p_effective_date => trunc(sysdate) -- to be
,p_scorecard_id => to_number(next_score_card)
,p_object_version_number => next_sc_ovn
,p_status_code => 'WKR'
,p_return_status => api_return_status
);
hr_personal_scorecard_swi.update_scorecard_status
(p_validate => hr_Api.g_false_num
,p_effective_date => trunc(sysdate) -- to be
,p_scorecard_id => to_number(next_score_card)
,p_object_version_number => next_sc_ovn
,p_status_code => 'ERROR'
,p_return_status => api_return_status
);
fnd_msg_pub.Delete_Msg;
fnd_msg_pub.Delete_Msg;
hr_personal_scorecard_swi.update_scorecard_status
(p_validate => hr_Api.g_false_num
,p_effective_date => trunc(sysdate) -- to be
,p_scorecard_id => to_number(next_score_card)
,p_object_version_number => next_sc_ovn
,p_status_code => 'WKR'
,p_return_status => api_return_status
);
hr_transaction_swi.delete_transaction(score_card_txn_id,hr_Api.g_false_num);