[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pcak.cost_allocation_keyflex_id,
pcak.concatenated_segments,
pcaf.proportion
FROM
per_all_assignments_f assg,
pay_cost_allocations_f pcaf,
pay_cost_allocation_keyflex pcak
WHERE assg.assignment_id = p_assignment_id
AND assg.assignment_id = pcaf.assignment_id
AND assg.Primary_flag = 'Y'
AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
AND pcak.enabled_flag = 'Y'
AND sysdate between nvl(pcaf.effective_start_date,sysdate)
and nvl(pcaf.effective_end_date,sysdate+1)
AND sysdate between nvl(assg.effective_start_date,sysdate)
and nvl(assg.effective_end_date,sysdate+1);
SELECT
pcak.concatenated_segments
FROM
per_all_assignments_f assg,
hr_all_organization_units horg,
pay_cost_allocation_keyflex pcak
WHERE assg.assignment_id = p_assignment_id
AND assg.organization_id = horg.organization_id
AND assg.Primary_flag = 'Y'
AND horg.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
AND pcak.enabled_flag = 'Y'
AND sysdate between nvl(assg.effective_start_date,sysdate)
and nvl(assg.effective_end_date,sysdate+1);
query_str := 'SELECT '||
'sum(hr_mee_views_gen.convertDuration(evt.duration_units, ''H'',evt.duration)) '||
'FROM ota_booking_status_types bst, '||
' ota_events evt, ota_delegate_bookings db '||
'WHERE db.booking_status_type_id = bst.booking_status_type_id '||
'AND db.event_id = evt.event_id '||
'AND db.delegate_person_id = :1 ';
query_str := 'SELECT SUM(hr_mee_views_gen.amtInLoginPrsnCurrency(evt.currency_code, fl.money_amount, evt.course_start_date)) '||
'FROM ota_delegate_bookings db, '||
' ota_events evt, ota_finance_lines fl '||
'WHERE db.booking_id = fl.booking_id(+) '||
'AND nvl(fl.cancelled_flag(+),''N'') = ''N'' '||
'AND db.event_id = evt.event_id '||
'AND db.delegate_person_id = :1 ';
query_str := 'SELECT count(db.booking_id) '||
'FROM ota_booking_status_types bst, '||
' ota_events evt, ota_delegate_bookings db '||
'WHERE db.booking_status_type_id = bst.booking_status_type_id '||
'AND db.event_id = evt.event_id '||
'AND EXISTS (SELECT ''e'' '||
'FROM ota_training_plans tp, ota_training_plan_members tpm '||
'WHERE tp.person_id = db.delegate_person_id '||
'AND tp.person_id <> tp.creator_person_id '||
'AND tp.training_plan_id = tpm.training_plan_id '||
'AND tpm.activity_version_id = evt.activity_version_id) '||
'AND db.delegate_person_id = :1 ';
SELECT decode(ppb.pay_annualization_factor,
null, 1,
0, 1,
ppb.pay_annualization_factor) * ppp.proposed_salary_n
,petf.input_currency_code
FROM per_pay_bases ppb, per_assignments_f paf
,per_pay_proposals ppp, pay_input_values_f ivf, pay_element_types_f petf
WHERE paf.person_id = p_person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND ppb.input_value_id = ivf.input_value_id
AND ivf.element_type_id = petf.element_type_id
AND ppp.change_date BETWEEN ivf.effective_start_date AND ivf.effective_end_date
AND ppp.change_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND ppp.change_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppp.assignment_id = paf.assignment_id
AND ppp.change_date = (SELECT max(change_date) FROM per_pay_proposals ippp
WHERE ippp.assignment_id = paf.assignment_id
AND ippp.approved = 'Y'
AND ippp.change_date <= getEffDate)
AND ppb.pay_basis_id = paf.pay_basis_id;
SELECT org.name
FROM hr_all_organization_units org, hr_organization_information ori
WHERE org.organization_id = p_training_center_id
AND org.organization_id = ori.organization_id
AND ori.org_information_context = 'CLASS'
AND ori.org_information1 ='OTA_TC';
select gr.currency_code, gr.minimum, gr.mid_value, gr.maximum, petf.input_currency_code,
pb.grade_annualization_factor, ppp.proposed_salary_n, pb.pay_annualization_factor
from pay_input_values_f ivf, pay_element_types_f petf, pay_grade_rules_f gr,
per_pay_bases pb, per_assignments_f paf, per_pay_proposals ppp
where paf.assignment_id = p_assignment_id
AND paf.pay_basis_id = pb.pay_basis_id
and paf.assignment_id = ppp.assignment_id(+)
and pb.input_value_id = ivf.input_value_id
and ivf.element_type_id = petf.element_type_id
AND pb.rate_id = gr.rate_id
AND paf.grade_id = gr.grade_or_spinal_point_id
and ppp.approved(+) = 'Y'
and sysdate between paf.effective_start_date and paf.effective_end_date
and sysdate between ppp.change_date(+) and ppp.date_to(+)
and sysdate between ivf.effective_start_date and ivf.effective_end_date
and sysdate between petf.effective_start_date and petf.effective_end_date
AND sysdate between gr.effective_start_date and gr.effective_end_date;
select pgr.value , psp.spinal_point
from pay_grade_rules_f pgr, per_spinal_points psp, per_spinal_point_steps_f psps
where psps.step_id = p_step_id and psps.spinal_point_id=psp.spinal_point_id
and pgr.grade_or_spinal_point_id=psps.spinal_point_id and pgr.rate_type='SP'
and p_eff_date between pgr.effective_start_date and pgr.effective_end_date
and p_eff_date between psps.effective_start_date and psps.effective_end_date;
select (nvl(gs.starting_step,1) + count(*))-1 step
from per_spinal_point_steps_f psps, per_spinal_point_steps_f psps2, per_grade_spines_f gs
where psps.step_id = p_step_id
and p_eff_date between psps.effective_start_date and psps.effective_end_date
and p_eff_date between psps2.effective_start_date and psps2.effective_end_date
and p_eff_date between gs.effective_start_date and gs.effective_end_date
and psps.grade_spine_id = psps2.grade_spine_id
and psps.grade_spine_id = gs.grade_spine_id
and psps.sequence >= psps2.sequence
group by gs.starting_step;
SELECT gr.rowid
FROM per_pay_proposals ppp, per_assignments_f paf
,per_pay_bases pb, pay_grade_rules_f gr
WHERE ppp.pay_proposal_id = p_pay_proposal_id
AND ppp.assignment_id = paf.assignment_id
AND ppp.change_date between paf.effective_start_date and paf.effective_end_date
AND paf.pay_basis_id = pb.pay_basis_id
AND pb.rate_id = gr.rate_id
AND paf.grade_id = gr.grade_or_spinal_point_id
AND ppp.change_date between gr.effective_start_date and gr.effective_end_date;
SELECT nvl(max(pay_proposal_id),-1)
FROM per_pay_proposals ppp, fnd_sessions fs
WHERE fs.session_id = userenv('sessionid')
AND ppp.assignment_id = p_assignment_id
AND ppp.approved = 'Y'
AND fs.effective_date between ppp.change_date and ppp.date_to;
SELECT nvl(max(application_id),-1)
FROM per_applications, fnd_sessions fs
WHERE fs.session_id = userenv('sessionid')
AND person_id = p_person_id
AND fs.effective_date between date_received and nvl(date_end,fs.effective_date);
SELECT nvl(max(performance_review_id),-1)
FROM per_performance_reviews pr
WHERE pr.person_id = p_person_id
AND pr.review_date = (SELECT max(review_date)
FROM per_performance_reviews ipr, fnd_sessions fs
WHERE fs.session_id = userenv('sessionid')
AND ipr.person_id = pr.person_id
AND ipr.review_date <= fs.effective_date);
SELECT count(att.absence_attendance_id) total_number_of_absences
FROM per_absence_attendances att
WHERE att.person_id = p_person_id;
SELECT sum(NVL(att.absence_days,0)) total_absence_days
FROM per_absence_attendances att
WHERE att.person_id = p_person_id;
SELECT sum(NVL(att.absence_hours,0)) total_absence_hours
FROM per_absence_attendances att
WHERE att.person_id = p_person_id;
SELECT ROUND(SUM(MONTHS_BETWEEN(
decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
-1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
trunc(ser.date_start))/12), 2) yos
FROM per_periods_of_service ser
WHERE ser.person_id = p_person_id
AND ser.date_start <= p_eff_date;
SELECT ROUND(MONTHS_BETWEEN(
decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
-1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
trunc(ser.adjusted_svc_date))/12, 2) ayos
FROM per_periods_of_service ser
WHERE ser.person_id = p_person_id
AND p_eff_date between ser.date_start and nvl(ser.actual_termination_date, p_eff_date);
SELECT MAX(pa.date_received)
FROM per_applications pa,
per_assignments_f ass
WHERE ass.application_id = pa.application_id
AND ass.assignment_type = 'A'
AND ass.person_id = p_person_id;
SELECT count(db.booking_id)
FROM ota_booking_status_types bst,
ota_events evt, ota_delegate_bookings db
WHERE db.booking_status_type_id = bst.booking_status_type_id
AND db.event_id = evt.event_id
AND evt.course_start_date <= p_eff_date
AND bst.type = 'A'
AND db.delegate_person_id = p_person_id;
SELECT count(tdb.booking_id)
FROM ota_booking_status_types bst,
ota_events evt, ota_delegate_bookings tdb
WHERE tdb.booking_status_type_id = bst.booking_status_type_id
AND tdb.event_id = evt.event_id
AND evt.course_start_date > p_eff_date
AND bst.type NOT IN ('C')
AND tdb.delegate_person_id = p_person_id;
SELECT count(tdb.booking_id)
FROM ota_booking_status_types bst,
ota_delegate_bookings tdb
WHERE tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type IN ('R','C')
AND tdb.delegate_person_id = p_person_id;
SELECT pet.input_currency_code
FROM pay_element_types_f pet
, pay_input_values_f piv
, per_pay_bases ppb
, per_assignments_f paf
WHERE paf.assignment_id = p_assign_id
AND p_change_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppb.pay_basis_id = paf.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND p_change_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND p_change_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT ppb.pay_annualization_factor
, ppp.proposed_salary_n
FROM per_pay_bases ppb
, per_assignments_f paf
, per_pay_proposals ppp
WHERE paf.assignment_id = p_assign_id
AND p_change_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_change_date)
AND ppp.change_date = p_change_date
AND ppp.assignment_id = paf.assignment_id
AND ppb.pay_basis_id = paf.pay_basis_id;
SELECT org_information6
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
SELECT pj.name,
pjd.segment1,
pjd.segment2,
pjd.segment3,
pjd.segment4,
pjd.segment5,
pjd.segment6,
pjd.segment7,
pjd.segment8,
pjd.segment9,
pjd.segment10,
pjd.segment11,
pjd.segment12,
pjd.segment13,
pjd.segment14,
pjd.segment15,
pjd.segment16,
pjd.segment17,
pjd.segment18,
pjd.segment19,
pjd.segment20,
pjd.segment21,
pjd.segment22,
pjd.segment23,
pjd.segment24,
pjd.segment25,
pjd.segment26,
pjd.segment27,
pjd.segment28,
pjd.segment29,
pjd.segment30
FROM per_jobs_vl pj,
per_job_definitions pjd
WHERE pj.job_definition_id = pjd.job_definition_id
AND pj.job_id = p_job_id;
SELECT org_information4
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
SELECT pg.name,
pgd.segment1,
pgd.segment2,
pgd.segment3,
pgd.segment4,
pgd.segment5,
pgd.segment6,
pgd.segment7,
pgd.segment8,
pgd.segment9,
pgd.segment10,
pgd.segment11,
pgd.segment12,
pgd.segment13,
pgd.segment14,
pgd.segment15,
pgd.segment16,
pgd.segment17,
pgd.segment18,
pgd.segment19,
pgd.segment20,
pgd.segment21,
pgd.segment22,
pgd.segment23,
pgd.segment24,
pgd.segment25,
pgd.segment26,
pgd.segment27,
pgd.segment28,
pgd.segment29,
pgd.segment30
FROM per_grades_vl pg,
per_grade_definitions pgd
WHERE pg.grade_definition_id = pgd.grade_definition_id
AND pg.grade_id = p_grade_id;
SELECT org_information8
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
SELECT pp.name,
ppd.segment1,
ppd.segment2,
ppd.segment3,
ppd.segment4,
ppd.segment5,
ppd.segment6,
ppd.segment7,
ppd.segment8,
ppd.segment9,
ppd.segment10,
ppd.segment11,
ppd.segment12,
ppd.segment13,
ppd.segment14,
ppd.segment15,
ppd.segment16,
ppd.segment17,
ppd.segment18,
ppd.segment19,
ppd.segment20,
ppd.segment21,
ppd.segment22,
ppd.segment23,
ppd.segment24,
ppd.segment25,
ppd.segment26,
ppd.segment27,
ppd.segment28,
ppd.segment29,
ppd.segment30
FROM hr_all_positions_f_vl pp,
per_position_definitions ppd
WHERE pp.position_definition_id = ppd.position_definition_id
AND pp.position_id = p_position_id
AND p_effective_date BETWEEN pp.effective_start_date
AND pp.effective_end_date;
SELECT application_column_name
FROM fnd_id_flex_segments_vl
WHERE application_id = p_application_id
AND id_flex_code = p_flex_code
AND id_flex_num = p_flex_num
AND segment_name = p_segment_name
AND enabled_flag = 'Y';
SELECT cur.precision
FROM fnd_currencies_vl cur
WHERE cur.currency_code = p_curcode
AND p_effective_date BETWEEN NVL(cur.start_date_active,p_effective_date)
AND NVL(cur.end_date_active,p_effective_date);
select pcr.contact_type Contact_Type,
decode(pcr.contact_type,'EMRG','Y','N') Emergency_Contact,
HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type) Full_Contact_Type
from per_contact_relationships pcr,
per_all_people_f per
where pcr.person_id = pp_person_id
and pcr.contact_person_id = pp_contact_id
and pcr.contact_person_id = per.person_id
and pp_effective_date between
decode(pcr.date_start,null,trunc(sysdate),trunc(pcr.date_start))
and decode(pcr.date_end,null,trunc(sysdate),trunc(pcr.date_end))
and pp_effective_date between per.effective_start_date and per.effective_end_date
order by Emergency_Contact desc;
select pcr.primary_contact_flag into lv_primary
from per_contact_relationships pcr,
per_all_people_f per
where pcr.person_id = p_person_id
and pcr.contact_person_id = p_contact_id
and pcr.contact_person_id = per.person_id
and p_effective_date between
decode(pcr.date_start,null,trunc(sysdate),trunc(pcr.date_start))
and decode(pcr.date_end,null,trunc(sysdate),trunc(pcr.date_end))
and contact_type = 'EMRG'
and rownum < 2;
query_str := 'Select db.score FROM ota_delegate_bookings db, ota_events evt '||
',ota_activity_versions av '||
',ota_booking_status_types bs , ota_booking_status_histories bsh '||
'WHERE db.booking_status_type_id = bs.booking_status_type_id '||
'and db.delegate_person_id = :1 '||
'AND db.booking_id = bsh.booking_id(+) '||
'AND db.booking_status_type_id = bsh.booking_status_type_id (+) '||
'AND db.event_id = evt.event_id '||
'AND evt.activity_version_id = av.activity_version_id (+) ' ;
query_str := 'select opr.score from ota_performances opr , ota_offerings ofr '||
', ota_learning_objects olo , ota_events evt '||
'where ofr.learning_object_id = olo.learning_object_id(+) '||
'and olo.learning_object_id = opr.learning_object_id(+) '||
'and opr.user_id(+) = :1 '||
'and evt.event_id = :2 '||
'and ofr.OFFERING_ID = evt.parent_offering_id '||
'and evt.parent_offering_id is not null ';
cur_str := 'Select opf.completed_date EndDate '||
'From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu '||
'Where oev.parent_offering_id = ofr.offering_id '||
'And ofr.learning_object_id = opf.learning_object_id(+) '||
'And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id '||
'and ocu.Online_Flag = ''Y'' '||
'And opf.User_id(+) = :1 '||
'And oev.event_id = :2 ';