[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
segment1
, name
INTO
X_proj_num
, X_proj_name
FROM
pa_projects_all
WHERE
project_id = X_proj_id;
SELECT
task_number
,task_name
INTO
X_task_num
,X_task_name
FROM
pa_tasks
WHERE
task_id = X_task_id;
SELECT
project_id
INTO
X_project_id
FROM
pa_projects_all
WHERE
segment1 = X_project_num;
SELECT
person_id
INTO
X_person_id
FROM
pa_employees
WHERE
employee_number = X_emp_num;
SELECT
employee_id
INTO
X_person_id
FROM
fnd_user
WHERE
user_id = X_userid;
SELECT
full_name
INTO
X_person_name
FROM
pa_employees
WHERE
person_id = X_person_id;
SELECT
task_id
INTO
X_task_id
FROM
pa_tasks
WHERE
project_id = X_proj_id
AND task_number = X_task_num;
SELECT
organization_id
INTO
X_org_id
FROM
hr_organization_units o,
pa_implementations i
WHERE name = X_org_name
AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
AND o.business_group_id = i.business_group_id)
OR (pa_utils.IsCrossBGProfile_WNPS = 'Y'));
SELECT
organization_id
INTO
X_orgn_id
FROM
hr_organization_units o,
pa_implementations i
WHERE name = X_org_name
AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
AND o.business_group_id = i.business_group_id
AND o.business_group_id = X_bg_id)
OR (pa_utils.IsCrossBGProfile_WNPS = 'Y'));
SELECT
organization_id
INTO
X_orgn_id
FROM
hr_organization_units o,
pa_implementations i
WHERE name = X_org_name
AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
AND o.business_group_id = i.business_group_id)
OR (pa_utils.IsCrossBGProfile_WNPS = 'Y' ));
SELECT
name
INTO
X_org_name
FROM
hr_organization_units o,
pa_implementations i
WHERE
organization_id = X_org_id
AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
AND o.business_group_id = i.business_group_id)
OR pa_utils.IsCrossBGProfile_WNPS = 'Y' )
;
SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
into X_week_ending_day_index
FROM pa_implementations;
select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
into X_week_ending_day from dual;
SELECT
next_day( trunc( X_date )-1, X_week_ending_day ) /* BUG#3118592 */
INTO
X_week_ending
FROM
sys.dual;
SELECT
count(1)
INTO
dummy
FROM
sys.dual
WHERE
trunc(X_date) BETWEEN trunc(trunc( X_week_end )-6 ) /* BUG#3118592 */
AND trunc( X_week_end );
SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
FROM per_periods_of_service
WHERE person_id = X_person_id
AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
SELECT business_group_id --Moved selection of BG id from inside where clause to here
INTO X_business_group_id
FROM pa_implementations;
SELECT
max(a.organization_id)
INTO
X_org_id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- Modified for bug 4699231
WHERE
a.person_id = X_person_id
AND a.primary_flag = 'Y'
AND a.assignment_type in ('E', 'C')
AND a.assignment_status_type_id = s.assignment_status_type_id
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
AND trunc(X_date) BETWEEN trunc( a.effective_start_date ) /*Added trunc on X_Date for bug 8278399 */
AND trunc( a.effective_end_date )
/* Added for bug 2009830 */
AND ((X_Cross_BG_Profile = 'N'
AND X_business_group_id = a.business_group_id+0)
OR X_Cross_BG_Profile = 'Y' )
; /*Bug 7645561 Changed the Query to include TERM_ASSIGN */
SELECT
max(a.organization_id)
INTO
X_org_id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- Modified for bug 4699231
WHERE
a.person_id = X_person_id
AND a.primary_flag = 'Y'
AND a.assignment_type in ('E', 'C')
AND a.assignment_status_type_id = s.assignment_status_type_id
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
AND trunc(X_date) BETWEEN trunc( a.effective_start_date ) /*Added trunc on X_Date for bug 8278399 */
AND trunc( l_final_process_date ) --added for 11741116
and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
/* Added for bug 2009830 */
AND ((X_Cross_BG_Profile = 'N'
AND X_business_group_id = a.business_group_id+0)
OR X_Cross_BG_Profile = 'Y' )
; /*Bug 7645561 Changed the Query to include TERM_ASSIGN */
SELECT
cd.hourly_cost_rate
INTO
X_cost_rate
FROM
pa_compensation_details cd
WHERE
cd.person_id = X_person_id
AND X_date BETWEEN cd.start_date_active
AND nvl( cd.end_date_active, X_date );
SELECT
nvl( r.cost_rate, 1 )
INTO
X_exp_type_cost_rate
FROM
pa_expenditure_cost_rates r
WHERE
r.expenditure_type = X_expenditure_type
AND X_date BETWEEN r.start_date_active
AND nvl( r.end_date_active, X_date );
SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
FROM per_periods_of_service
WHERE person_id = X_person_id
AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
SELECT MAX(nvl(actual_termination_date+1,to_date('4712/12/31', 'YYYY/MM/DD')))
FROM per_periods_of_service
WHERE person_id = X_person_id
AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
SELECT business_group_id --Moved selection of BG id from inside where clause to here
INTO X_business_group_id
FROM pa_implementations;
select poh.segment1, pol.line_num
into l_po_number, l_po_line_num
from po_headers poh,
po_lines pol
where poh.po_header_id = pol.po_header_id
and poh.po_header_id = l_po_header_id
and pol.po_line_id = l_po_line_id;
select poh.po_header_id, pol.po_line_id
into l_po_header_id, l_po_line_id
from po_headers poh,
po_lines pol
where poh.po_header_id = pol.po_header_id
and poh.type_lookup_code = 'STANDARD'
and poh.segment1 = x_po_number
and pol.line_num = x_po_line_num;
SELECT
max(a.job_id)
INTO
X_emp_job_id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- Modified for bug 4699231
WHERE
a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND X_date BETWEEN trunc( a.effective_start_date )
AND trunc( a.effective_end_date )
AND a.person_id = X_person_id
AND a.assignment_type in ('E', 'C')
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
AND s.assignment_status_type_id = a.assignment_status_type_id
/* Added for bug 2009830 */
AND (( X_Cross_BG_Profile = 'N'
AND X_business_group_id = a.business_group_id+0)
OR X_Cross_BG_Profile = 'Y' ) ;
SELECT
max(a.job_id)
INTO
X_emp_job_id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- Modified for bug 4699231
WHERE
a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND X_date BETWEEN trunc( a.effective_start_date )
AND trunc( l_final_process_date )
and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
AND a.person_id = X_person_id
AND a.assignment_type in ('E', 'C')
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
AND s.assignment_status_type_id = a.assignment_status_type_id
/* Added for bug 2009830 */
AND (( X_Cross_BG_Profile = 'N'
AND X_business_group_id = a.business_group_id+0)
OR X_Cross_BG_Profile = 'Y' ) ;
SELECT
a.job_id
INTO
X_emp_job_id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- for Bug 4699231
WHERE
a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND X_date BETWEEN trunc( a.effective_start_date )
AND trunc( a.effective_end_date )
AND a.person_id = X_person_id
-- AND a.assignment_type in ('E', 'C') -- commented out for bug : 3568109
AND a.assignment_type = l_person_type
-- AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- commented out for bug : 3568109
AND s.per_system_status = l_assignment_status
AND s.assignment_status_type_id = a.assignment_status_type_id
/* Added for bug 2009830 */
AND (( X_Cross_BG_Profile = 'N'
AND X_business_group_id = a.business_group_id+0)
OR X_Cross_BG_Profile = 'Y' ) ;
SELECT
a.job_id
INTO
X_emp_job_id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- for Bug 4699231
WHERE
a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND X_date BETWEEN trunc( a.effective_start_date )
AND trunc( l_final_process_date )
and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
AND a.person_id = X_person_id
-- AND a.assignment_type in ('E', 'C') -- commented out for bug : 3568109
AND a.assignment_type = l_person_type
-- AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- commented out for bug : 3568109
AND s.per_system_status = l_assignment_status
AND s.assignment_status_type_id = a.assignment_status_type_id
/* Added for bug 2009830 */
AND (( X_Cross_BG_Profile = 'N'
AND X_business_group_id = a.business_group_id+0)
OR X_Cross_BG_Profile = 'Y' ) ;
select location_id
into l_location_id
from per_all_assignments_f
where person_id = p_person_id
and p_ei_date between effective_start_date and effective_end_date
and primary_flag = 'Y';
select l.location_id
into l_location_id
from hr_locations l
where X_Date < nvl(l.inactive_date, to_date('4712/12/31', 'YYYY/MM/DD'))
and (l.location_use = 'HR')
and location_id = p_location_id;
select l.location_id
into l_location_id
from hr_locations l
where X_Date < nvl(l.inactive_date, to_date('4712/12/31', 'YYYY/MM/DD'))
and (l.location_use = 'HR')
and location_code = p_location_code;
select job_id
into l_job_id
from per_jobs_v V
where job_id = X_job_id
and ((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' AND
fnd_profile.value('PER_BUSINESS_GROUP_ID') = V.BUSINESS_GROUP_ID)
OR
PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'Y'
)
and date_from <= X_date
and ((date_to is null) or (date_to >= X_date));
select job_id
into l_job_id
from per_jobs_v V
where name = X_job_name
AND V.BUSINESS_GROUP_ID = NVL(X_Business_Group_Id,V.BUSINESS_GROUP_ID)
/* and ((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' AND
fnd_profile.value('PER_BUSINESS_GROUP_ID') = V.BUSINESS_GROUP_ID)
OR
PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'Y'
)*/
and date_from <= X_date
and ((date_to is null) or (date_to >= X_date));
SELECT
pa_expenditure_items_s.nextval
INTO
X_expenditure_item_id
FROM
sys.dual;
SELECT
count(*)
INTO
dummy
FROM
pa_expenditure_types et
WHERE
et.expenditure_type = X_expenditure_type
AND X_date BETWEEN et.start_date_active
AND nvl( et.end_date_active, X_date );
SELECT /*+ index_ffs(se1 PER_ORG_STRUCTURE_ELEMENTS_N50) */
DISTINCT
se1.organization_id_parent
INTO
X_top_org_id
FROM
per_org_structure_elements se1
WHERE
se1.org_structure_version_id||'' = X_org_structure_version_id
AND NOT exists
( SELECT null
FROM per_org_structure_elements se2
WHERE se2.org_structure_version_id = X_org_structure_version_id
AND se2.organization_id_child = se1.organization_id_parent );
SELECT DISTINCT business_group_id /*Distinct added for Bug 6043451*/
INTO X_business_group_id
FROM pa_implementations;
Select 1
Into dummy
From sys.dual
Where exists
( Select null
From pa_projects_expend_v p
Where p.project_Id = X_project_Id );
SELECT 'Y'
INTO x_dummy
FROM sys.dual
WHERE EXISTS (
SELECT NULL
FROM pa_implementations_all
WHERE org_id IS NOT NULL );
SELECT DISTINCT 'x'
FROM pa_organizations_proj_all_bg_v
WHERE organization_id = x_org_id;
SELECT MIN(pap.end_date)
INTO l_pa_date
FROM pa_periods pap
WHERE status in ('O','F')
AND pap.end_date >= x_ei_date;
SELECT pap.end_date
INTO l_pa_end_date
FROM pa_periods pap
WHERE pap.period_name = x_pa_period_name;
SELECT pa_periods.period_name
INTO l_period_name
FROM pa_periods
WHERE pa_periods.end_Date =
(SELECT MIN(pap.end_date)
FROM pa_periods pap
WHERE status in ('O','F')
AND pap.end_date >= x_ei_date)
AND status in ('O','F'); /* Added the check for bug #1550929 */
SELECT function
INTO etypeclass_code
FROM pa_system_linkages
WHERE function = X_system_linkage ;
SELECT nvl(multi_org_flag, 'N')
---- Bug#MRC_SCHEMA_ELIM , nvl(multi_currency_flag, 'N')
INTO l_multi_org
---, l_multi_cur
FROM fnd_product_groups;
select g.short_name ||
decode(g.mrc_sob_type_code, 'N', NULL,
decode(l_multi_cur, 'N', NULL,
': ' || g.currency_code))
into l_wnd_context
from gl_sets_of_books g
, pa_implementations c
where c.set_of_books_id = g.set_of_books_id;
selecting length 55 instead of 60 as 5 characters(for currency code) are concatenated here*/
/* Bug6884654 - Changed substr to substrb */
select substrb(h.name,1,55) ||
decode(g.mrc_sob_type_code, 'N', NULL,
decode(l_multi_cur, 'N', NULL,
': ' || g.currency_code))
into l_wnd_context
from gl_sets_of_books g
, pa_implementations c
, hr_operating_units h
where h.organization_id = to_number(l_id)
and c.set_of_books_id = g.set_of_books_id;
SELECT decode(FC.minimum_accountable_unit,
NULL, round(P_Amount, FC.precision),
round(P_Amount/FC.minimum_accountable_unit) *
FC.minimum_accountable_unit)
INTO l_rounded_amount
FROM fnd_currencies FC
WHERE FC.currency_code = P_Currency_Code;
select person_party_id from fnd_user -- For Bug 4527617.
where user_id = p_user_id;
select h.party_id
from hz_parties h
,fnd_user f
where h.orig_system_reference = CONCAT('PER:',f.employee_id)
and f.user_id = p_user_id;
SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
FROM per_periods_of_service
WHERE person_id = X_person_id
AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
select poh.segment1, pol.line_num
into l_po_number, l_po_line_num
from po_headers poh,
po_lines pol
where poh.po_header_id = pol.po_header_id
and poh.po_header_id = l_po_header_id
and pol.po_line_id = l_po_line_id;
select poh.po_header_id, pol.po_line_id
into l_po_header_id, l_po_line_id
from po_headers poh,
po_lines pol
where poh.po_header_id = pol.po_header_id
and poh.type_lookup_code = 'STANDARD'
and poh.segment1 = x_po_number
and pol.line_num = x_po_line_num;
SELECT
a.job_id,
a.organization_id
INTO
X_emp_job_id,
X_Emp_Org_Id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- modified for Bug 4699231
, pa_implementations i
WHERE
a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND trunc(X_date) BETWEEN trunc( a.effective_start_date )
AND trunc( a.effective_end_date )
AND a.person_id = X_person_id
AND ((X_Cross_BG_Profile ='N' AND a.business_group_id = i.business_group_id) OR
X_Cross_BG_Profile ='Y') /*bug6355926*/
AND a.assignment_type in ('E', 'C')
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'TERM_ASSIGN') --Bug 13974131
AND s.assignment_status_type_id = a.assignment_status_type_id;
SELECT
a.job_id,
a.organization_id
INTO
X_emp_job_id,
X_Emp_Org_Id
FROM
per_assignment_status_types s
, per_all_assignments_f a -- modified for Bug 4699231
, pa_implementations i
WHERE
a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND trunc(X_date) BETWEEN trunc( a.effective_start_date )
AND trunc( l_final_process_date )
and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
AND a.person_id = X_person_id
AND ((X_Cross_BG_Profile ='N' AND a.business_group_id = i.business_group_id) OR
X_Cross_BG_Profile ='Y') /*bug6355926*/
AND a.assignment_type in ('E', 'C')
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'TERM_ASSIGN') --Bug 13974131
AND s.assignment_status_type_id = a.assignment_status_type_id;
select exp_cycle_start_day_code into x_week_start from pa_implementations; /*Bug 7601460 */
G_WeekEndDateTab.Delete;
SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
into X_week_ending_day_index
FROM pa_implementations;
select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
into X_week_ending_day
from dual;
SELECT Next_Day( trunc( X_date )-1, X_Week_Ending_Day ) /* BUG#3118592 */
INTO X_Week_Ending
FROM sys.dual;
select PERSON_ID
, FULL_NAME
, LAST_NAME
, FIRST_NAME
, MIDDLE_NAMES
, EMPLOYEE_NUMBER
into
L_PERSON_ID
, L_PERSON_FULL_NAME
, L_PERSON_LAST_NAME
, L_PERSON_FIRST_NAME
, L_PERSON_MIDDLE_NAMES
, L_PERSON_EMPLOYEE_NUMBER
from pa_employees where person_id = p_person_id;
select ei.org_id ORG_ID
,TO_CHAR(ei.expenditure_item_date,'YYYY/MM/DD') expenditure_item_date
,nvl(ei.override_to_organization_id
,exp.incurred_by_organization_id) orgj_id
,exp.incurred_by_person_id incurred_by_person_id
,ei.job_id JOB_ID
,detail.compensation_rule_set
,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
,to_char(detail.rate_schedule_id)
,detail.override_type
,detail.acct_rate_type
,detail.acct_rate_date_code
,detail.acct_exchange_rate
,detail.base_hours
,detail.rbc_element_type_id
from pa_compensation_details detail
,pa_expenditure_items ei
,pa_expenditures exp
where trunc(p_exp_item_date) between trunc(detail.start_date_active)
and trunc(nvl(detail.end_date_active,p_exp_item_date))
and detail.person_id = exp.incurred_by_person_id
and exp.expenditure_id = ei.expenditure_id
--and ei.source_expenditure_item_id is null
and ei.expenditure_item_id = p_exp_item_id
and ei.po_line_id IS NULL;
select rule.costing_method, rule.rate_source_code,rule.enable_accrual_code
into x_costing_method, x_rate_source_code,x_enable_accrual
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = l_costing_rule_tab(1);
select detail.compensation_rule_set
,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
,to_char(detail.rate_schedule_id)
,detail.override_type
,detail.acct_rate_type
,detail.acct_rate_date_code
,detail.acct_exchange_rate
,detail.base_hours
,detail.rbc_element_type_id
from pa_compensation_details detail
,pa_expenditure_items ei
,pa_expenditures exp
where trunc(p_exp_item_date) between trunc(detail.start_date_active)
and trunc(nvl(detail.end_date_active,p_exp_item_date))
and detail.person_id = exp.incurred_by_person_id
and exp.expenditure_id = ei.expenditure_id
and ei.expenditure_item_id = p_exp_item_id
and ei.po_line_id IS NULL;
SELECT ei.org_id ORG_ID
,ei.expenditure_item_date
,nvl(ei.override_to_organization_id
,exp.incurred_by_organization_id) orgj_id
,exp.incurred_by_person_id incurred_by_person_id
,ei.job_id JOB_ID
from pa_expenditure_items ei
,pa_expenditures exp
where exp.expenditure_id = ei.expenditure_id
and ei.expenditure_item_id = p_exp_item_id
and ei.po_line_id IS NULL;
select rule.costing_method, rule.rate_source_code,rule.enable_accrual_code
into x_costing_method, x_rate_source_code,x_enable_accrual
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = lx_costing_rule;
select detail.compensation_rule_set
,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
,to_char(detail.rate_schedule_id)
,detail.override_type
,detail.acct_rate_type
,detail.acct_rate_date_code
,detail.acct_exchange_rate
,detail.base_hours
,detail.rbc_element_type_id
from pa_compensation_details detail
where trunc(p_exp_item_date) between trunc(detail.start_date_active)
and trunc(nvl(detail.end_date_active,p_exp_item_date))
and detail.person_id = p_incurred_by_person_id;
SELECT distinct job_id JOB_ID
from per_all_assignments_f
where person_id = p_incurred_by_person_id
and trunc(p_exp_item_date) between trunc(effective_start_date)
and trunc(nvl(effective_end_date,p_exp_item_date));
select rule.costing_method, rule.rate_source_code,rule.enable_accrual_code
into x_costing_method, x_rate_source_code,x_enable_accrual
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = lx_costing_rule;
select distinct x.expenditure_item_id, x.cost_distributed_flag,
b.module_code, x.costing_method,
decode(b.module_code, 'PAXREVTXN', 'A', 'B') ord
from
(select a.expenditure_item_id, cost_distributed_flag, costing_method
from pa_expenditure_items_all a
where a.adjusted_expenditure_item_id IS NULL
--and cost_distributed_flag = 'Y'
start with expenditure_item_id = p_xfer_item_id
connect by prior a.expenditure_item_id = a.transferred_from_exp_item_id) x,
pa_expend_item_adj_activities b
where x.expenditure_item_id = b.expenditure_item_id
and b.module_code in ('PAXREVTXN', 'PAXPRRPE')
order by 1 desc, ord;
select module_code, decode(module_code, 'PAXREVTXN', 'A', 'B') ord
from pa_expend_item_adj_activities
where expenditure_item_id = p_exp_item_id
and module_code in ('PAXREVTXN', 'PAXPRRPE')
order by ord;