[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO hri.hri_debug
(text1
,text2
,insert_date
)
VALUES
(substr(p_text,1,239)
,substr(p_text2,1,239)
,sysdate
)
;
,INSERT_DATE DATE
);
SELECT start_date
, end_date
FROM bis_hr_months_v
WHERE id = p_c_time_level_value_id
UNION
SELECT start_date
, end_date
FROM bis_hr_bimonths_v
WHERE id = p_c_time_level_value_id
UNION
SELECT start_date
, end_date
FROM bis_hr_quarters_v
WHERE id = p_c_time_level_value_id
UNION
SELECT start_date
, end_date
FROM bis_hr_semiyears_v
WHERE id = p_c_time_level_value_id
UNION
SELECT start_date
, end_date
FROM bis_hr_years_v
WHERE id = p_c_time_level_value_id;
SELECT pm.measure_id
, pm.measure_short_name
FROM bisbv_performance_measures pm
, bisbv_target_levels tl
WHERE pm.measure_id = tl.measure_id
AND tl.target_level_id = p_target_level_id;
SELECT *
FROM bisfv_target_levels
WHERE target_level_id = p_target_level_id;
SELECT *
FROM bisfv_targets trg
WHERE trg.target_id = p_target_id;
SELECT lookup_code
FROM hr_lookups
WHERE lookup_type = 'BUDGET_MEASUREMENT_TYPE'
AND lookup_code = p_c_bmt;
SELECT wfr.orig_system_id
, rsp.application_id
FROM wf_roles wfr
, fnd_responsibility rsp
WHERE wfr.orig_system_id = rsp.responsibility_id
AND wfr.name = p_c_role_name
AND wfr.orig_system like 'FND_RESP%';
SELECT version_name
FROM ota_activity_versions
WHERE activity_version_id = p_activity_version_id;
SELECT b.security_group_id
FROM per_business_groups b
, hr_all_organization_units o
where o.business_group_id = b.business_group_id
AND o.organization_id = p_organization_id;
SELECT 1
FROM per_org_structure_elements ose
WHERE ose.org_structure_version_id = p_org_structure_version_id
AND (ose.organization_id_child = p_organization_id
or ose.organization_id_parent = p_organization_id
) ;
SELECT formula_id
FROM ff_formulas_f
WHERE ( (p_c_business_group_id IS null
AND business_group_id IS null )
OR
p_c_business_group_id = business_group_id
)
AND trunc(sysdate) BETWEEN
effective_start_date AND effective_end_date
AND formula_name = p_c_formula_name;
SELECT business_group_id
FROM hr_all_organization_units
WHERE sysdate
BETWEEN date_from
AND nvl(date_to, hr_general.end_of_time)
AND organization_id = p_c_org_id;
SELECT asg.assignment_id
, asg.effective_end_date
FROM per_all_assignments_f asg
, per_assignment_status_types ast
WHERE p_c_period_start_date-1 between
asg.effective_start_date and
asg.effective_end_date
AND asg.assignment_type = 'E'
AND asg.organization_id = p_c_organization_id
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND NOT EXISTS
(SELECT null
FROM per_all_assignments_f asg2,
per_assignment_status_types ast2
WHERE p_c_period_end_date between
asg2.effective_start_date and
asg2.effective_end_date
AND asg2.assignment_type = 'E'
AND asg2.assignment_id = asg.assignment_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND asg2.organization_id = p_c_organization_id);
SELECT asg.assignment_id
, asg.effective_start_date
FROM per_all_assignments_f asg
, per_assignment_status_types ast
WHERE ( (p_c_actual_rec.dim2_level_value_name = 'JOB CATEGORY'
AND asg.job_id IN (SELECT jei.job_id
FROM per_job_extra_info jei
WHERE jei.jei_information1 = p_c_actual_rec.dim2_level_value_id)
)
OR
(p_c_actual_rec.dim2_level_value_name = 'JOB'
AND
asg.job_id = to_number(p_c_actual_rec.dim2_level_value_id) )
OR
(p_c_actual_rec.dim2_level_value_name = 'TOTAL JOBS')
)
AND ( (p_c_actual_rec.dim1_level_value_name = 'LOCATION'
AND
asg.location_id = to_number(p_c_actual_rec.dim3_level_value_id))
OR
p_c_actual_rec.dim1_level_value_name = 'TOTAL GEOGRAPHY'
)
AND asg.business_group_id = p_c_business_group_id
AND asg.organization_id = to_number(p_c_actual_rec.org_level_value_id)
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND asg.assignment_type = 'E' -- Bug 2357061
AND p_c_period_end_date BETWEEN
asg.effective_start_date AND
asg.effective_end_date
/* Commented out because of the method in HRMNPSUM used to calc TOTAL
AND EXISTS
(SELECT null
FROM per_all_assignments_f asg2
, per_assignment_status_types ast2
WHERE p_c_period_start_date-1 BETWEEN
asg2.effective_start_date AND
asg2.effective_end_date
AND asg2.assignment_id = asg.assignment_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND asg2.organization_id = p_c_actual_rec.org_level_value_id
)
*/
;
SELECT sum(bval.value) budget_value
FROM per_budget_values bval
, per_budget_elements be
, per_budget_versions bver
, per_time_periods tp
, per_budgets_v bud
where bud.unit = p_target_rec.unit_of_measure
AND bud.business_group_id = l_business_group_id
AND bud.budget_id = p_budget_id
AND bud.budget_id = bver.budget_id
AND trunc(sysdate) BETWEEN
bver.date_FROM AND nvl( bver.date_to, SYSDATE+1 )
AND be.budget_version_id = bver.budget_version_id
AND be.budget_element_id = bval.budget_element_id
AND tp.time_period_id = bval.time_period_id
AND be.organization_id = p_target_rec.org_level_value_id
AND ((p_target_rec.dim2_level_short_name = 'JOB CATEGORY'
AND be.job_id IN (SELECT jei.job_id
FROM per_job_extra_info jei
WHERE jei.jei_information1 = p_target_rec.dim2_level_value_id))
OR (p_target_rec.dim2_level_short_name = 'JOB'
AND be.job_id = p_target_rec.dim2_level_value_id)
OR (p_target_rec.dim2_level_short_name = 'TOTAL JOBS'))
AND tp.start_date = p_target_rec.period_start_date
AND tp.END_date = p_target_rec.period_END_date;
SELECT v.budget_measurement_value
, v.vacancy_id
FROM per_vacancies v
WHERE ( (p_c_actual_rec.dim2_level_value_name = 'JOB CATEGORY'
AND v.job_id IN
(SELECT jei.job_id
FROM per_job_extra_info jei
WHERE jei.jei_information1
= p_c_actual_rec.dim2_level_value_id)
)
OR
(p_c_actual_rec.dim2_level_value_name = 'JOB'
AND v.job_id = to_number(p_c_actual_rec.dim2_level_value_id)
)
OR
(p_c_actual_rec.dim2_level_value_name = 'TOTAL JOBS')
)
AND ( (p_c_actual_rec.dim1_level_value_name = 'LOCATION'
AND v.location_id = to_number(p_c_actual_rec.dim1_level_value_id)
)
OR
(p_c_actual_rec.dim1_level_value_name = 'TOTAL GEOGRAPHY')
)
AND v.budget_measurement_type = p_c_bmtype
AND v.status = 'CLOSED' --Closed vacancy, bug 2449031
AND v.date_to BETWEEN
p_c_period_start_date AND p_c_period_end_date
AND v.organization_id = p_actual_rec.org_level_value_id;
SELECT a.assignment_id
, a.vacancy_id
, a.effective_start_date
FROM per_all_assignments_f a
WHERE (a.assignment_id, a.effective_start_date) IN
(SELECT b.assignment_id,min(b.effective_start_date)
FROM per_all_assignments_f b
WHERE b.assignment_type = 'E'
AND b.vacancy_id = p_vacancy_id
GROUP BY b.assignment_id);
SELECT asg.assignment_id
, asg.organization_id
, evt.duration
, evt.duration_units
, ver.version_name
, evt.title
, dbk.successful_attendance_flag
FROM per_assignments_f asg
, ota_booking_status_types bst
, ota_activity_versions ver
, ota_events evt
, ota_delegate_bookings dbk
WHERE dbk.delegate_assignment_id = asg.assignment_id
AND evt.course_start_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND dbk.booking_status_type_id = bst.booking_status_type_id
AND bst.type = 'A' -- Attended
AND dbk.event_id = evt.event_id
AND asg.assignment_type = 'E'
AND evt.event_type = 'SCHEDULED'
AND evt.activity_version_id = ver.activity_version_id
AND nvl(evt.event_status, 'X') <> 'C' /*Not Cancelled*/
/* Time Dim */
AND evt.course_end_date < trunc(sysdate)
AND evt.course_end_date BETWEEN
p_c_period_start_date AND
p_c_period_end_date
/* Org Dim*/
AND asg.organization_id = p_c_actual_rec.org_level_value_id
/* Dim 1 Location*/
AND ( (p_c_actual_rec.dim1_level_value_name = 'LOCATION'
AND asg.location_id = p_c_actual_rec.dim1_level_value_id)
OR (p_c_actual_rec.dim1_level_value_name = 'TOTAL GEOGRAPHY')
)
/* Dim 2 Job or Job Category*/
AND ( (p_c_actual_rec.dim2_level_value_name = 'TOTAL JOBS')
OR (p_c_actual_rec.dim2_level_value_name = 'JOB CATEGORY'
AND asg.job_id IN (SELECT jei.job_id
FROM per_job_extra_info jei
WHERE jei.jei_information1 = p_c_actual_rec.dim2_level_value_id))
OR (p_c_actual_rec.dim2_level_value_name = 'JOB'
AND asg.job_id = p_c_actual_rec.dim2_level_value_id)
)
/* Dim 3 Activity Version */
AND ( (p_c_actual_rec.dim3_level_value_name = 'ACTIVITY VERSION'
AND evt.activity_version_id = p_c_actual_rec.dim3_level_value_id)
OR (p_c_actual_rec.dim3_level_value_name = 'TOTAL ACTIVITY VERSIONS')
);
l_user_selection_tbl bis_INDICATOR_REGION_PUB.indicator_Region_Tbl_Type;
pl('Retreive User Selections');
bis_actual_pub.Retrieve_User_Selections
( p_api_version => 1.0
,p_Target_Level_Rec => l_Target_Level_Rec
,x_indicator_Region_Tbl => l_user_selection_Tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_error_Tbl => l_error_tbl
);
pl('Count of user selections ', to_char(l_user_selection_Tbl.COUNT));
FOR i IN 1..l_user_selection_Tbl.count LOOP
l_actual_rec.target_level_id := l_user_selection_tbl(i).target_level_id;
l_actual_rec.target_level_short_name := l_user_selection_tbl(i).target_level_short_name;
l_actual_rec.Responsibility_id := l_user_selection_tbl(i).Responsibility_id;
l_actual_rec.Responsibility_short_name := l_user_selection_tbl(i).Responsibility_short_name;
l_actual_rec.Responsibility_name := l_user_selection_tbl(i).Responsibility_name;
l_actual_rec.target_level_id := l_user_selection_tbl(i).target_level_id;
l_actual_rec.org_level_value_id := l_user_selection_tbl(i).org_level_value_id;
l_actual_rec.dim1_level_value_id := l_user_selection_tbl(i).dim1_level_value_id;
l_actual_rec.dim2_level_value_id := l_user_selection_tbl(i).dim2_level_value_id;
l_actual_rec.dim3_level_value_id := l_user_selection_tbl(i).dim3_level_value_id;
l_actual_rec.dim4_level_value_id := l_user_selection_tbl(i).dim4_level_value_id;
l_actual_rec.dim5_level_value_id := l_user_selection_tbl(i).dim5_level_value_id;