[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT business_group_id, nvl(org_information10,'USD') currency_code
FROM per_people_f ppf, hr_organization_information oi
WHERE ppf.person_id = fnd_global.employee_id
AND ppf.business_group_id = oi.organization_id
AND oi.org_information_context = 'Business Group Information'
AND g_eff_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT 4 rank, hr_general.decode_lookup('STRUCTURE_TYPE','POS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
AND ce.position_id = paf.position_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 3 rank, hr_general.decode_lookup('STRUCTURE_TYPE','JOB')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
AND ce.job_id = paf.job_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 2 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ORG')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
AND ce.organization_id = paf.organization_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 1 rank, hr_general.decode_lookup('STRUCTURE_TYPE','BUS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
AND ce.enterprise_id = paf.business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 0 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ADD')||'##' src
FROM dual
ORDER BY RANK DESC;
FUNCTION validate_selected_function (
p_api_version IN NUMBER
,p_function IN VARCHAR2
,p_object_name IN VARCHAR2
,p_person_id IN VARCHAR2 -- p_instance_pk1_value
,p_instance_pk2_value IN VARCHAR2
,p_user_name IN VARCHAR2
,p_eff_date IN DATE
)
RETURN VARCHAR2
IS
l_proc varchar2(72) := g_package||' validate_selected_function';
END validate_selected_function;
select named_person_id
from per_security_profiles
where security_profile_id=p_security_profile_id;
select employee_id
from fnd_user
where user_id=p_user_id;
SELECT function_id, parameters, web_html_call
FROM fnd_form_functions fff
WHERE fff.function_name = p_func_name;
SELECT 'N' status
FROM WF_PROCESS_ACTIVITIES pa1, WF_PROCESS_ACTIVITIES pa2,
WF_ACTIVITIES a1, WF_ACTIVITIES a2, WF_ACTIVITY_ATTR_VALUES aav
WHERE pa1.process_item_type = p_item_type
and pa1.process_name = p_wfpname
and pa1.activity_name = pa2.process_name
and a1.name = pa1.process_name
and pa1.process_version = a1.version
and a1.item_type = p_item_type
and sysdate between a1.begin_date and nvl(a1.end_date,sysdate)
and pa2.process_item_type = p_item_type
and pa2.process_name = a2.name
and pa2.process_version = a2.version
and a2.item_type = p_item_type
and sysdate between a2.begin_date and nvl(a2.end_date,sysdate)
and pa2.instance_id = aav.process_activity_id
and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS')
UNION
SELECT 'N' status
FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
WF_ACTIVITY_ATTR_VALUES aav
where a.item_type = p_item_type
and a.name = p_wfpname
and sysdate between a.begin_date and nvl(a.end_date,sysdate)
and pa.process_item_type = a.item_type
and pa.process_name = a.name
and pa.process_version = a.version
and pa.instance_id = aav.process_activity_id
and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS');
p_selected_person_id NUMBER,
p_effective_date DATE)
RETURN VARCHAR2 IS
cursor fetch_asg (l_person_id NUMBER, l_effective_date DATE) is
SELECT 'T'
FROM per_assignments_f2 paf, per_assignment_status_types past
WHERE paf.person_id = l_person_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type IN ('E', 'C')
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status NOT IN ('TERM_ASSIGN','END');
OPEN fetch_asg (p_selected_person_id, p_effective_date);
hr_utility.trace('Going into Fetch after (OPEN fetch_asg (p_selected_person_id, p_effective_date) ): '|| l_proc);
SELECT 'C' FROM dual
WHERE UPPER(p_ak_region) IN (
'HR_CCMGR_OVERVIEW_TOP_SS',
'PQH_ACADEMIC_RANK_TOP',
'PQH_ACADEMIC_RANK_OVRVW_TOP',
'PQH_TENURE_STATUS_OVRVW_TOP',
'PQH_TENURE_STATUS_TOP',
'PQH_REVIEW_FIND_TOP',
--'OTA_TRAINING_TOP_SS',
--'OTA_ADDTRNG_OVERVIEW_TOP_SS',
--'PQH_REVIEWS_TOP',
--'PQH_EVENTS_MGR_SEARCH_TOP',
'HR_LOA_SUMMARY_TOP_SS'
);
SELECT nvl(current_npw_flag,'N')
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND p_eff_date BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT function_id, parameters, web_html_call
FROM fnd_form_functions fff
WHERE fff.function_name = p_func_name;
select 'T' from fnd_menus m, fnd_menu_entries me
where menu_name = p_menu_name
and m.menu_id = me.menu_id
and me.function_id = p_func_id;
SELECT 'C' status
FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
WF_ACTIVITY_ATTR_VALUES aav
where a.item_type = p_item_type
and a.name = p_wfpname
and sysdate between a.begin_date and nvl(a.end_date,sysdate)
and pa.process_item_type = a.item_type
and pa.process_name = a.name
and pa.process_version = a.version
and pa.instance_id = aav.process_activity_id
and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
and 'C' = hr_util_misc_ss.check_akregion_code(text_value);
g_entity_list.delete;
select assignment_id from per_all_assignments_f
where person_id = p_person_id and assignment_type = 'A' and
p_effective_date between effective_start_date and effective_end_date;
SELECT WAAV.TEXT_VALUE Value
FROM WF_ACTIVITY_ATTR_VALUES WAAV
WHERE WAAV.PROCESS_ACTIVITY_ID = actid
AND WAAV.NAME = name;
select primary_flag into dummy from per_all_assignments_f
where assignment_id=l_number_value and l_date_value between effective_start_date
and effective_end_date;
select min(asg.EFFECTIVE_start_DATE) into l_asg_term_date
from per_all_assignments_f asg ,per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and ast.assignment_status_type_id = asg.assignment_status_type_id
and ast.per_system_status = 'TERM_ASSIGN';
select max(EFFECTIVE_end_DATE) into l_asg_term_date
from per_all_assignments_f where assignment_id = p_assignment_id;
aname => 'HR_SELECTED_PERSON_TYPE_ATTR');
DELETE PER_INTERIM_PERSON_LIST;
INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
values (person_data(i).person_id, person_data(i).assignment_id, person_data(i).in_my_list);
DELETE PER_INTERIM_PERSON_LIST;
INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
SELECT selected_person_id, selected_assignment_id, 'Y'
FROM hr_working_person_lists
WHERE owning_person_id = person_id;
INSERT INTO HR_WORKING_PERSON_LISTS(working_person_list_id, owning_person_id,
selected_person_id, current_selection, multiple_selection, selected_assignment_id)
SELECT HR_WORKING_PERSON_LISTS_s.NEXTVAL,
prsn_id,
list.person_id,
NULL,
NULL,
list.assignment_id
FROM PER_INTERIM_PERSON_LIST list;
select security_group_id into l_secGrpId
from fnd_security_groups
where security_group_key = p_bgId;
DELETE PER_INTERIM_ENTITY_LIST;
INSERT INTO PER_INTERIM_ENTITY_LIST (entity_name,state,pk1,pk2,pk3,pk4,pk5)
values (entity_data(i).entity_name, entity_data(i).state,entity_data(i).pk1,entity_data(i).pk2,entity_data(i).pk3,entity_data(i).pk4,entity_data(i).pk5);
DELETE PER_INTERIM_ENTITY_LIST;
select assignment_id
into assi_id
from per_people_f ppf, per_assignments_f paf
where paf.person_id = ppf.person_id
and trunc(sysdate) between paf.effective_start_date(+) and paf.effective_end_date(+)
and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+)
and ppf.person_id = p_person_id
and paf.assignment_id = p_assignment_id
and nvl(ppf.CURRENT_EMP_OR_APL_FLAG,'N') = 'N' and nvl(ppf.CURRENT_EMPLOYEE_FLAG,'N') = 'N'
and nvl(ppf.CURRENT_NPW_FLAG,'N') = 'N';
select from_person_id
from hr_person_deployments dep
where dep.to_person_id = person_id and permanent='Y';
SELECT function_name ,parameters, web_html_call
FROM fnd_form_functions fff
WHERE fff.function_id = p_function_id;
select INSTR(l_web_html_call,'/oracle/apps/per/selfservice/talentmanagement/webui/MgrTalentManagementPG') as str_index into l_appr_index from dual;
select INSTR(l_web_html_call,'/oracle/apps/per/selfservice/appraisals/webui/MgrMainAppraiserPG&') as str_index into l_appr_index from dual;
select INSTR(l_web_html_call,'/ghr/') as str_index into l_ghr_index from dual;
select decode(fnd_profile.value('ENABLE_SECURITY_GROUPS'), 'Y' , p_bg_id,
decode(fnd_global.employee_id,p_person_id,
nvl(
decode( FND_PROFILE.VALUE_SPECIFIC('PER_BUSINESS_GROUP_ID',null,fnd_global.resp_id)
,null,fnd_profile.value('PER_BUSINESS_GROUP_ID'),
FND_PROFILE.VALUE_SPECIFIC('PER_BUSINESS_GROUP_ID',null,fnd_global.resp_id)
)
,p_bg_id
),p_bg_id)) into l_bg_id from dual;
procedure update_attachment
(p_entity_name in varchar2 default null
,p_pk1_value in varchar2 default null
,p_rowid in varchar2 ) is
l_proc varchar2(72) := g_package ||'update_attachment';
select *
from fnd_attached_documents
where rowid = p_rowid;
select *
from fnd_documents
where document_id = csr_p_document_id;
select *
from fnd_documents_tl
where document_id = csr_p_document_id
and language = csr_p_lang;
select userenv('LANG') into l_language from dual;
hr_utility.set_location(' before fnd_attached_documents_pkg.update_row :' || l_proc,30);
fnd_attached_documents_pkg.update_row
(x_rowid => p_rowid
,x_attached_document_id =>
l_attached_doc_pre_upd.attached_document_id
,x_document_id => l_doc_pre_upd.document_id
,x_last_update_date => trunc(sysdate)
,x_last_updated_by => l_attached_doc_pre_upd.last_updated_by
,x_seq_num => l_attached_doc_pre_upd.seq_num
,x_entity_name => p_entity_name
,x_column1 => l_attached_doc_pre_upd.column1
,x_pk1_value => p_pk1_value
,x_pk2_value => l_attached_doc_pre_upd.pk2_value
,x_pk3_value => l_attached_doc_pre_upd.pk3_value
,x_pk4_value => l_attached_doc_pre_upd.pk4_value
,x_pk5_value => l_attached_doc_pre_upd.pk5_value
,x_automatically_added_flag =>
l_attached_doc_pre_upd.automatically_added_flag
,x_attribute_category =>
l_attached_doc_pre_upd.attribute_category
,x_attribute1 => l_attached_doc_pre_upd.attribute1
,x_attribute2 => l_attached_doc_pre_upd.attribute2
,x_attribute3 => l_attached_doc_pre_upd.attribute3
,x_attribute4 => l_attached_doc_pre_upd.attribute4
,x_attribute5 => l_attached_doc_pre_upd.attribute5
,x_attribute6 => l_attached_doc_pre_upd.attribute6
,x_attribute7 => l_attached_doc_pre_upd.attribute7
,x_attribute8 => l_attached_doc_pre_upd.attribute8
,x_attribute9 => l_attached_doc_pre_upd.attribute9
,x_attribute10 => l_attached_doc_pre_upd.attribute10
,x_attribute11 => l_attached_doc_pre_upd.attribute11
,x_attribute12 => l_attached_doc_pre_upd.attribute12
,x_attribute13 => l_attached_doc_pre_upd.attribute13
,x_attribute14 => l_attached_doc_pre_upd.attribute14
,x_attribute15 => l_attached_doc_pre_upd.attribute15
,x_datatype_id => l_doc_pre_upd.datatype_id
,x_category_id => l_doc_pre_upd.category_id
,x_security_type => l_doc_pre_upd.security_type
,x_security_id => l_doc_pre_upd.security_id
,x_publish_flag => l_doc_pre_upd.publish_flag
,x_image_type => l_doc_pre_upd.image_type
,x_storage_type => l_doc_pre_upd.storage_type
,x_usage_type => l_doc_pre_upd.usage_type
,x_start_date_active => trunc(sysdate)
,x_end_date_active => l_doc_pre_upd.end_date_active
,x_language => l_language
,x_description => l_doc_tl_pre_upd.description
,x_file_name => l_doc_pre_upd.file_name
,x_media_id => l_doc_pre_upd.media_id
,x_doc_attribute_category =>
l_doc_tl_pre_upd.doc_attribute_category
,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
,x_url => l_doc_pre_upd.url
,x_title => l_doc_tl_pre_upd.title
);
hr_utility.set_location(' after fnd_attached_documents_pkg.update_row :' || l_proc,40);
End update_attachment;
select *
from fnd_attached_documents
where entity_name=p_source_entity_name
and pk1_value=source_pk1_value;
SELECT rowid
FROM fnd_attached_documents
WHERE attached_document_id = X_attached_document_id;
select item_type, item_key
from hr_api_transactions
where transaction_id = source_pk1_value;
SELECT hat.SELECTED_PERSON_ID, hatv2.number_value irc_doc_id
INTO l_person_id,l_irc_doc_id
FROM hr_api_transactions hat
, fnd_attached_documents fads
, hr_api_transaction_steps hats
, hr_api_transaction_values hatv1
, hr_api_transaction_values hatv2
WHERE hat.transaction_id = l_source_pk1_value
AND hats.transaction_id = hat.transaction_id
AND hats.api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API'
AND hatv1.transaction_step_id = hats.transaction_step_id
AND fads.attached_document_id = attached_documents_rec.attached_document_id
AND hatv1.name like 'P_FND_DOC_ID%'
AND hatv1.NUMBER_VALUE = fads.document_id
AND hatv2.transaction_step_id = hatv1.transaction_step_id
AND hatv2.name = 'P_IRC_DOC_ID'||substr(hatv1.name,-1,length(hatv1.name));
per_fnd_attachment_pkg.update_on_link_table(l_irc_doc_id,l_person_id, attached_documents_rec);
update_attachment
(p_entity_name=>p_dest_entity_name
,p_pk1_value=> p_dest_pk1_value
,p_rowid=>l_rowid);
update_attachment
(p_entity_name=>p_dest_entity_name
,p_pk1_value=> p_dest_pk1_value
,p_rowid=>l_rowid);
select *
from hr_api_transactions
where transaction_id = p_transaction_id;
select *
from hr_api_transaction_steps
where transaction_id=p_transaction_id;
select hat.transaction_id,
hat.assignment_id,
hat.selected_person_id,
hat.transaction_ref_id
from hr_api_transactions hat
where hat.transaction_id =p_transaction_id;
SELECT hatv1.NUMBER_VALUE IRC_DOC_ID, hatv2.NUMBER_VALUE FND_DOC_ID
FROM hr_api_transaction_values hatv1, hr_api_transaction_values hatv2
WHERE hatv1.transaction_step_id = x_transaction_step_id
and hatv1.NAME like 'P_IRC_DOC_ID%'
and hatv1.NUMBER_VALUE is not null
and hatv2.transaction_step_id = hatv1.transaction_step_id
and hatv2.NAME = 'P_FND_DOC_ID'||substr(hatv1.name,-1,length(hatv1.name))
and hatv2.NUMBER_VALUE is not null;
l_pk_value := trans_row.selected_person_id;
select number_value into l_period_of_serv_id from
hr_api_transaction_values
where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
and NAME = 'P_PERIOD_OF_SERVICE_ID';
select number_value into l_period_of_serv_id from
hr_api_transaction_values
where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
and NAME = 'P_PERIODS_OF_SERVICE_ID';
SELECT COUNT(1)
INTO l_apl_flag
FROM hr_api_transaction_values
WHERE transaction_step_id = step_row.TRANSACTION_STEP_ID
AND NAME = 'P_APPLICANT_NUMBER'
AND VARCHAR2_VALUE IS NOT NULL;
select number_value into l_person_id from
hr_api_transaction_values
where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
and NAME = 'P_PERSON_ID';
select date_value into l_doc_upload_date from
hr_api_transaction_values
where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
and NAME = 'P_EFFECTIVE_DATE';
select number_value into l_assignment_id from
hr_api_transaction_values
where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
and NAME = 'P_APPL_ASSIGNMENT_ID';
SELECT application_id
INTO l_application_id
FROM per_all_assignments_f
WHERE assignment_id = l_assignment_id;
p_mode=> 'INSERT');
select Information30
into lv_is_upgrade
from hr_api_transaction_steps
where transaction_id=p_transaction_id;
select name
into jname
from per_jobs_tl
where p_job_id = job_id
and language(+) = userenv('LANG');
select name
into pname
from hr_all_positions_f_tl
where p_position_id = position_id
and language(+) = userenv('LANG');
select name
into gname
from per_grades_tl
where p_grade_id = grade_id
and language(+) = userenv('LANG');
select name
into oname
from hr_all_organization_units_tl
where p_org_id = organization_id
and language(+) = userenv('LANG');
select location_code
into lname
from hr_locations_all_tl
where p_loc_id = location_id
and language(+) = userenv('LANG');
select trs.transaction_step_id
from hr_api_transaction_steps trs
where trs.transaction_id = hr_transaction_ss.get_transaction_id
(itemtype ,itemkey )
and trs.api_name ='HR_PROCESS_PERSON_SS.PROCESS_API';
select DESCRIPTIVE_FLEXFIELD_NAME from FND_DESCR_FLEX_CONTEXTS
where DESCRIPTIVE_FLEXFIELD_NAME = 'Extra Person Info DDF'
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US_ETHNIC_ORIGIN';
select BUSINESS_GROUP_ID into l_business_grp_id from per_all_people_f where
person_id = l_person_id and trunc(sysdate) between effective_start_date and effective_end_date;
select BUSINESS_GROUP_ID into l_business_group_id from per_all_people_f where
person_id = p_person_id and trunc(sysdate) between effective_start_date and effective_end_date;
hr_utility.set_location('Before selecting job_id from paf', 30);
select job_id into l_job_id from per_all_assignments_f paf
where assignment_id = p_rptg_grp_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select position_id into l_position_id from per_all_assignments_f paf
where assignment_id = p_rptg_grp_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select grade_id into l_grade_id from per_all_assignments_f paf
where assignment_id = p_rptg_grp_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select ORGANIZATION_ID into l_org_id from per_all_assignments_f paf
where assignment_id = p_rptg_grp_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select Location_ID into l_loc_id from per_all_assignments_f paf
where assignment_id = p_rptg_grp_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select distinct(supervisor_id) into l_manager_id from per_all_assignments_f paf where paf.assignment_id = p_rptg_grp_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select distinct(supervisor_id) into l_manager_id from per_all_assignments_f paf where paf.assignment_id = p_assignment_id
and sysdate between paf.effective_start_date and paf.effective_end_date;
select distinct global_name,local_name into l_manager_global_name,l_manager_local_name from per_all_people_f where person_id = l_manager_id
and trunc(sysdate) between effective_start_date and effective_end_date;
select transaction_item_type, transaction_item_key
into lv_item_type, lv_item_key
from pqh_ss_approval_history
where transaction_history_id = p_transaction_id
and rownum =1;
select 'x' into dummy from hr_api_transactions
where item_key = lv_item_key
and item_type = lv_item_type
and status <> 'AC';
SELECT transaction_ref_table, transaction_ref_id
FROM hr_api_transactions
WHERE transaction_id = p_txn_id;
select text_value
into lv_result_code
from wf_notification_attributes
where notification_id=wf_engine.context_nid
and name='RESULT';
select 'X' into dummy from fnd_form_functions where function_name = fn_name
and parameters like '%pQkFn=Y%';
select 'X' into dummy from fnd_form_functions where function_name = fn_name
and parameters like '%pBpRv=Y%';
select 'X' into dummy from fnd_form_functions where function_name = fn_name
and parameters like '%pEffectiveDate=%';
select 'X' into dummy from hr_api_transaction_steps ts, hr_api_transactions hat
where hat.item_type = itemtype
and hat.item_key = itemkey
and ts.transaction_id = hat.transaction_id
and rownum <= 1;