[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select userenv('sessionid') into p_session_id from dual;
select trunc(sysdate) into p_effective_date from dual;
Select 'X'
From pqh_ss_print_data
Where name = p_tag_name -- no upper case change
and session_id = p_session_id
and transaction_id =g_transaction_id;
SELECT userenv('sessionid') into l_session_id from dual;
INSERT INTO PQH_SS_PRINT_DATA(session_id,transaction_id,name,value)
VALUES (l_session_id, l_transaction_id,p_tag_name,p_tag_value);
UPDATE PQH_SS_PRINT_DATA
SET value = p_tag_value
WHERE session_id = l_session_id
AND name = p_tag_name
AND transaction_id = l_transaction_id;
SELECT decode(SemiProcessedName ,'N/A','N/A', nvl(substr(SemiProcessedName , 1, instr(SemiProcessedName,'&')-1),SemiProcessedName)) ShortName
From
(
SELECT decode(instr(parameters,p_parameter_name),null,'N/A',0 ,'N/A',
substr(parameters,instr(parameters,p_parameter_name)+length(p_parameter_name)+1)) SemiProcessedName
FROM fnd_form_functions FFF,
hr_api_transactions HAT
WHERE HAT.transaction_id = p_transaction_id
AND HAT.function_id = FFF.function_id
);
Select count(*)
from pqh_transaction_categories cats,
pqh_txn_category_documents catDocs,
pqh_documents_f docs
where form_name like 'PQH_GROUPS'
and cats.short_name like p_short_name
and cats.transaction_category_id = catDocs.transaction_category_id
and docs.document_id = catDocs.document_id
and nvl(p_eff_date,trunc(sysdate)) between effective_start_date and effective_end_date;
Select count(*)
from pqh_txn_category_documents
where transaction_category_id = (
Select transaction_category_id
from pqh_transaction_categories
where short_name =p_short_name)
and type_code in ( p_type_code, 'BOTH');
SELECT atv.name column_name,atv.datatype column_type,atv.varchar2_value|| Atv.Number_value||FND_DATE.date_to_canonical(Atv.date_value) column_value
FROM hr_api_transaction_values atv
WHERE atv.transaction_step_id in (Select max(transaction_step_id)
From hr_api_transaction_steps
where transaction_id = p_Transaction_Id
group by api_name
)
AND atv.name in ('P_PERSON_ID','P_ASSIGNMENT_ID','P_QUALIFICATION_ID','P_SELECTED_PERSON_ID');
IF UPPER(where_clause_param_values.column_name)='P_SELECTED_PERSON_ID' THEN
params_table(3).param_value := where_clause_param_values.column_value;
Select Meaning from hr_lookups
where lookup_type ='PQH_CURRENT_PROPOSED'
AND enabled_flag = 'Y'
AND lookup_code = p_lookup_code;
Select proposed_salary_n
From per_pay_proposals ppp
Where ppp.assignment_id = p_assignment_id
and ppp.change_date = ( select max(change_date)
from per_pay_proposals
where change_date <= g_effective_date
and assignment_id =
p_assignment_id
and approved = 'Y' );
Select petf.input_currency_code
From per_pay_bases ppb,
pay_input_values_f pivf,
pay_element_types_f petf
Where ppb.pay_basis_id=p_pay_basis_id
And ppb.input_value_id = pivf.input_value_id
AND pivf.element_type_id = petf.element_type_id;
Select 1
From per_pay_proposals ppp
where ppp.pay_proposal_id = p_pay_proposal_id
AND ppp.MULTIPLE_COMPONENTS = 'Y';
Select CHANGE_AMOUNT
From per_pay_proposal_components pppc
where pppc.pay_proposal_id = p_pay_proposal_id;
Select 1
From per_pay_proposals ppp
where ppp.pay_proposal_id = p_pay_proposal_id
AND ppp.MULTIPLE_COMPONENTS = 'Y';
Select CHANGE_PERCENTAGE
From per_pay_proposal_components pppc
where pppc.pay_proposal_id = p_pay_proposal_id;
SELECT hr_general.decode_lookup('PQH_TENURE_STATUS',p_lookup_code) into l_meaning
FROM DUAL;
Select name into l_meaning
from per_qualification_types
where qualification_type_id = p_qualification_type_id;
select meaning
from hr_leg_lookups
where lookup_type = 'PER_SUBJECT_STATUSES' and
lookup_code = p_award_id and
g_effective_date between nvl(start_date_active, g_effective_date)
and nvl(end_date_active, g_effective_date) and enabled_flag = 'Y'
order by lookup_code,meaning;
SELECT
meaning
FROM
hr_leg_lookups
WHERE
lookup_type = 'PER_TUITION_METHODS'
AND lookup_code = p_tuition_id
AND g_effective_date BETWEEN NVL(start_date_active, g_effective_date)
AND NVL(end_date_active, g_effective_date)
AND enabled_flag = 'Y';
SELECT
description
FROM
fnd_currencies_vl
WHERE
enabled_flag = 'Y'
AND currency_code = p_currency_code
AND currency_flag = 'Y'
AND sysdate >= NVL(start_date_active, sysdate)
AND sysdate <= NVL(end_date_active, sysdate);
select hl.meaning
from hr_lookups hl
where hl.lookup_type = 'TITLE'
and hl.lookup_code = p_title_code
and hl.enabled_flag = 'Y'
and trunc(g_effective_date) between trunc(nvl(hl.start_date_active,g_effective_date))
and trunc(nvl(hl.end_date_active,g_effective_date));
select hrl.meaning
from hr_lookups hrl where
hrl.lookup_type = 'SEX' and
hrl.Lookup_Code = p_gender_code and
hrl.enabled_flag = 'Y' and
trunc(SYSDATE) between trunc(nvl(hrl.start_date_active,SYSDATE))
and trunc(nvl(hrl.end_date_active,SYSDATE));
select hl.meaning
from hr_lookups hl
where hl.lookup_type = 'MAR_STATUS'
and hl.lookup_code = p_marital_code
and hl.enabled_flag = 'Y'
and g_effective_date between nvl(start_date_active, g_effective_date - 1)
and nvl(end_date_active, g_effective_date);
SELECT meaning
FROM hr_leg_lookups
WHERE lookup_type = 'LEAV_REAS'
AND lookup_code = p_termination_code
AND enabled_flag = 'Y';
select hl.meaning
from hr_leg_lookups hl
where hl.lookup_type = 'FREQUENCY'
and hl.lookup_code = p_freq_code
and hl.enabled_flag = 'Y'
and trunc(g_effective_date) BETWEEN nvl(hl.start_date_active, trunc(g_effective_date))
AND nvl(hl.end_date_active, trunc(g_effective_date));
select hl.meaning
from hr_leg_lookups hl
where hl.lookup_type = 'EMPLOYEE_CATG'
and hl.lookup_code = p_category_code
and hl.enabled_flag = 'Y'
and trunc(g_effective_date) between nvl(start_date_active, trunc(g_effective_date))
and nvl(end_date_active, trunc(g_effective_date));
select hl.meaning
from hr_leg_lookups hl
where hl.lookup_type = 'EMP_CAT'
and hl.lookup_code = p_category_code
and hl.enabled_flag = 'Y'
and trunc(g_effective_date) between nvl(start_date_active, trunc(g_effective_date))
and nvl(end_date_active, trunc(g_effective_date));
Select Meaning from hr_lookups
where lookup_type ='YES_NO'
AND enabled_flag = 'Y'
AND lookup_code = p_lookup_code;
SELECT
NAME SCHOOL
FROM
PER_ESTABLISHMENTS
Where ESTABLISHMENT_ID = p_establishment_id;
select first_name||' '||last_name||' '||suffix brief_name
from per_all_people_f ppf
where ppf.person_id = p_person_id
order by effective_end_date desc;
select payroll_name
from pay_all_payrolls_f pay
where payroll_id = p_payroll_id
and g_effective_date between
pay.effective_start_date and pay.effective_end_date
order by effective_start_date desc;
SELECT name
FROM per_collective_agreements
WHERE collective_agreement_id = p_collective_agreement_id;
SELECT reference
FROM per_contracts_f
WHERE contract_id = p_contract_id
AND g_effective_date between effective_start_date and effective_end_date;
Select TABLE_ROUTE_ID
from pqh_table_route
where table_alias like 'PQH_SS%';
select tl.user_status
from per_assignment_status_types asg,
per_assignment_status_types_tl tl
where asg.assignment_status_type_id = p_assignment_status_type_id
and asg.assignment_status_type_id = tl.assignment_status_type_id
and tl.language=USERENV('LANG');
select meaning
from hr_leg_lookups
where lookup_type = 'EMP_ASSIGN_REASON'
and enabled_flag = 'Y'
AND trunc(g_effective_date) BETWEEN NVL(start_date_active, trunc(g_effective_date))
AND NVL(end_date_active, trunc(g_effective_date))
and lookup_code =p_reason_code
UNION
select meaning
from hr_leg_lookups
where lookup_type = 'CWK_ASSIGN_REASON'
and enabled_flag = 'Y'
AND trunc(g_effective_date) BETWEEN NVL(start_date_active,
trunc(g_effective_date))
AND NVL(end_date_active, trunc(g_effective_date))
and lookup_code =p_reason_code;
Delete
from pqh_ss_print_label_temp
where trunc(creation_date) < p_effective_date;
Select formula_id
from pqh_documents_f
where p_effective_date between effective_start_date and effective_end_date
and short_name = p_short_name;
Select 'X', userenv('sessionid')
from ff_formulas_f
where formula_id = p_formula_id
and p_eff_date between effective_start_date and effective_end_date;
/* Removed the RECORD TYPE current_values_record in version 120.5 as selecting into this record type was limiting the attributes to 42. */
/*Bug fix 4722431*/
--this cursor decides what are the table_route_id for which query need to be done depending on the
--It also selects the from_clause and the where clause for the table_route_id
cursor table_routes is
select distinct att.master_table_route_id table_rt_id,
ptr.from_clause from_clause,
ptr.where_clause where_clause,
doc.document_category
from pqh_documents_f doc,
pqh_document_attributes_f doa,
pqh_attributes att,
pqh_table_route ptr
where doc.short_name = p_doc_short_name
AND doa.document_id = doc.document_id
AND p_effective_date between doc.effective_start_date and doc.effective_end_date
AND p_effective_date between doa.effective_start_date and doa.effective_end_date
AND att.attribute_id = doa.attribute_id
AND att.master_table_route_id=ptr.table_route_id
AND ptr.where_clause is not null;
select att.column_name att_column,doa.tag_name doc_tag,att.enable_flag flag
from pqh_documents_f doc,
pqh_document_attributes_f doa,
pqh_attributes att
where doc.short_name = p_doc_short_name
AND doa.document_id = doc.document_id
AND p_effective_date between doc.effective_start_date and doc.effective_end_date
AND p_effective_date between doa.effective_start_date and doa.effective_end_date
AND att.attribute_id = doa.attribute_id
AND att.master_table_route_id=p_table_route_id
order by att.attribute_id;
sql_query := 'Select '|| columntag||','||columnval ||' From ' || table_route_rslt.from_clause ||' Where ' || l_where_clause_out ;
INSERT INTO pqh_ss_print_data(session_id,transaction_id,name,value) values(userenv(''sessionid''),'||p_transaction_id||',l_rec.tag'||i||',l_rec.val'||i||');
select userenv('sessionid') into p_session_id from dual;
hr_utility.set_location('Pre approval type data insertion',15);
INSERT INTO
pqh_ss_print_data(session_id,transaction_id,name,value,enable_flag)
SELECT userenv('sessionid'),p_transaction_id,
doa.tag_name NAME,
decode(att.decode_function_name,null,atv.varchar2_value|| Atv.Number_value||Atv.date_value,
pqh_ss_utility.get_desc (
decode(atv.varchar2_value|| Atv.Number_value||FND_DATE.date_to_canonical(Atv.date_value),
null,null,
att.decode_function_name||'('''||atv.varchar2_value|| Atv.Number_value
||FND_DATE.date_to_canonical(Atv.date_value)||''')' ) )) value,
att.enable_flag
FROM hr_api_transaction_steps steps
, hr_api_transaction_values atv,
pqh_documents_f doc,
pqh_document_attributes_f doa,
pqh_attributes att,
pqh_table_route ptr
-- Bug Fix 2945716,Added Select Stmt to retrieve max step_id
WHERE atv.transaction_step_id in (Select max(transaction_step_id)
From hr_api_transaction_steps
where transaction_id = p_Transaction_Id
group by api_name
)
AND atv.transaction_step_id = steps.transaction_step_id
AND doc.short_name = l_pdf_short_name --'TP'
AND doa.document_id = doc.document_id
AND p_effective_date between doc.effective_start_date and doc.effective_end_date
AND p_effective_date between doa.effective_start_date and doa.effective_end_date
AND att.attribute_id = doa.attribute_id
AND att.enable_flag='Y'
AND att.master_table_route_id=ptr.table_route_id
AND att.COLUMN_NAME = atv.name
AND ptr.from_clause=steps.api_name;
hr_utility.set_location('Data Inserted',18);
hr_utility.set_location('Post approval type data insertion',15);
INSERT INTO
pqh_ss_print_data(session_id,transaction_id,name,value,enable_flag)
SELECT userenv('sessionid'),p_transaction_id,
doa.tag_name NAME,
decode(att.decode_function_name,null,atv.value,
pqh_ss_utility.get_desc (
decode(atv.value,null,null,
att.decode_function_name||'('''||atv.value||''')' ) )) value,
att.enable_flag
FROM pqh_ss_step_history steps
, pqh_ss_value_history atv,
pqh_documents_f doc,
pqh_document_attributes_f doa,
pqh_attributes att,
pqh_table_route ptr
WHERE steps.step_history_id = atv.step_history_id
AND steps.approval_history_id = atv.approval_history_id
AND (atv.step_history_id,atv.approval_history_id) in
(
Select step_history_id, approval_history_id
from pqh_ss_step_history
where transaction_history_id=p_transaction_id
and approval_history_id = (
Select max(approval_history_id) from pqh_ss_step_history
where transaction_history_id=p_transaction_id)
)
AND steps.transaction_history_id =p_transaction_id
AND doc.short_name = l_pdf_short_name
AND doa.document_id = doc.document_id
AND p_effective_date between doc.effective_start_date and doc.effective_end_date
AND p_effective_date between doa.effective_start_date and doa.effective_end_date
AND att.attribute_id = doa.attribute_id
AND att.enable_flag='Y'
AND att.master_table_route_id =ptr.table_route_id
AND att.COLUMN_NAME = atv.name
AND ptr.from_clause=steps.api_name ;
hr_utility.set_location('Data Inserted',18);