[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct to_number(substr(puc1.user_column_name,1, instr(puc1.user_column_name,'_')-1)) organization_id
from pay_user_tables put
, pay_user_columns puc
, pay_user_columns puc1
, pay_user_rows_f pur
, pay_user_column_instances_f puci
where put.user_table_name = g_priority_udt_name
and puc.user_table_id = put.user_table_id
and pur.user_table_id = put.user_table_id
and pur.business_group_id = g_bg_id
and puci.user_row_id = pur.user_row_id
and puci.user_column_id = puc1.user_column_id;
g_wsp_courses_tab.delete;
g_wsp_l_paths_tab.delete;
g_wsp_certifications_tab.delete;
g_atr_courses_tab.delete;
g_atr_l_paths_tab.delete;
g_atr_certifications_tab.delete;
g_atr_competences_tab.delete;
g_atr_qualifications_tab.delete;
g_wsp_priority_tab.delete;
g_atr_priority_tab.delete;
g_wsp_pri_final_tab.delete;
g_atr_pri_final_tab.delete;
select Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,
Attribute7,Attribute8,Attribute9,Attribute10, Attribute11,
Attribute12, Attribute13, Attribute14, Attribute15,
substr(lookup_code,5) lookup_code
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code like p_year || '%'
and security_group_id = fnd_global.lookup_security_group(p_lookup_type,3)
and attribute_category = G_ATTRIBUTE_CATEGORY
and (
Attribute1 || Attribute2 || Attribute3 || Attribute4 || Attribute5 ||
Attribute6 || Attribute7 || Attribute8 || Attribute9 || Attribute10 ||
Attribute11|| Attribute12|| Attribute13|| Attribute14 || Attribute15
) is not null;
select row_low_range_or_name --, effective_start_date, effective_end_date
from pay_user_rows_f
where user_row_id = p_csr_user_row_id
and p_effective_date between effective_start_date and effective_end_date;
select distinct substr(puc.user_column_name,1, instr(puc.user_column_name,'_')-1) organization_id
from pay_user_column_instances_f puci
, pay_user_columns_tl puc
where puci.user_row_id = p_csr_user_row_id
and puc.user_column_id = puci.user_column_id
and p_effective_date between puci.effective_start_date and puci.effective_end_date;
select level_number, unit_standard_id --saqa_id
from per_competences
where competence_id = p_csr_trng_event_id;
select level_number, qual_framework_id --saqa_id
from per_qualification_types
where qualification_type_id = p_csr_trng_event_id;
select substr(l_index,16,15) into l_skills_pri_id from dual;
select substr(l_index,16,15) into l_skills_pri_id from dual;
select Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,
Attribute7,Attribute8,Attribute9,Attribute10, Attribute11,
Attribute12, Attribute13, Attribute14, Attribute15,
substr(lookup_code,5) lookup_code -- trng event id
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code like p_year || '%'
and security_group_id = fnd_global.lookup_security_group(p_lookup_type,3)
and attribute_category = G_ATTRIBUTE_CATEGORY
and (
Attribute1 || Attribute2 || Attribute3 || Attribute4 || Attribute5 ||
Attribute6 || Attribute7 || Attribute8 || Attribute9 || Attribute10 ||
Attribute11|| Attribute12|| Attribute13|| Attribute14 || Attribute15
) is not null;
select pce.competence_id
from per_competence_elements pce
where pce.type = 'DELIVERY'
and pce.activity_version_id = p_course_id
and pce.business_group_id = g_bg_id;
select pce.competence_id
from per_competence_elements pce
where pce.type = 'OTA_LEARNING_PATH'
and pce.object_id = p_lp_id
and pce.business_group_id = g_bg_id;
select pce.competence_id
from per_competence_elements pce
where pce.type = 'OTA_CERTIFICATION'
and pce.object_id = p_cert_id
and pce.business_group_id = g_bg_id;
select olpm.activity_version_id
from ota_learning_paths olp
, ota_lp_sections olps
, ota_learning_path_members olpm
where olp.learning_path_id = p_learning_path_id
and olp.learning_path_id = olps.learning_path_id
and olps.learning_path_section_id = olpm.learning_path_section_id;
select ocm.object_id
from OTA_CERTIFICATION_MEMBERS ocm
, OTA_CERTIFICATIONS_B oc
where oc.certification_id = p_cert_id
and ocm.certification_id = oc.certification_id
and ocm.object_type = 'H';
select count(pce.competence_id)
from per_competence_elements pce
where pce.type = 'DELIVERY'
and pce.activity_version_id = p_course_id
and pce.business_group_id = g_bg_id;
g_atr_l_paths_tab.delete(rec_priority.lookup_code);
g_atr_certifications_tab.delete(rec_priority.lookup_code);
g_wsp_courses_tab.delete;
g_atr_courses_tab.delete;
g_atr_competences_tab.delete;
g_wsp_priority_tab.delete;
g_atr_priority_tab.delete;
Purpose : This returns the select statement that is used to created the
range rows.
Arguments :
Notes : The range cursor determines which people should be processed.
The normal practice is to include everyone, and then limit
the list during the assignment action creation.
--------------------------------------------------------------------------*/
procedure range_cursor
(
pactid in number,
sqlstr out nocopy varchar2
) is
l_proc varchar2(100);
Select hoi1.org_information1 org_name -- A(1).1 Organization Name
, hoi.org_information4 post_add_line_1 -- A(1).2 Postal Address
, hoi.org_information5 post_add_line_2
, hoi.org_information6 post_add_line_3
, hoi.org_information8 post_town_or_city
, hoi.org_information7 post_postal_code
, hoi.org_information9 post_province
, hoi.org_information10 phy_add_line_1 -- A(1).3 Physical Address
, hoi.org_information11 phy_add_line_2
, hoi.org_information12 phy_add_line_3
, hoi.org_information14 phy_town_or_city
, hoi.org_information13 phy_postal_code
, hoi.org_information15 phy_province
, hoi.org_information1 tel_no -- A(1).5 Telephone number
, hoi.org_information2 fax_no -- A(1).6 Fax number
, hoi.org_information3 email_add -- A(1).7 E-mail Address
From hr_all_organization_units haou
, hr_organization_information hoi
, hr_organization_information hoi1
Where haou.business_group_id = g_bg_id
and haou.organization_id = p_legal_entity_id
and haou.organization_id = hoi1.organization_id
and hoi.org_information_context(+) = 'ZA_LEGAL_ENTITY_CONTACT_INFO'
and hoi.organization_id(+) = haou.organization_id
and hoi1.org_information_context = 'ZA_LEGAL_ENTITY';
select hoi.org_information1 SETA_Name
, hoi.org_information3 activity_name
from hr_all_organization_units haou
, hr_organization_information hoi
where haou.business_group_id = g_bg_id
and haou.organization_id = p_legal_entity_id
and haou.organization_id = hoi.organization_id
and hoi.org_information_context = 'ZA_NQF_SETA_INFO';
Select 'sdl_number' sdl_num -- A(1).4 Skills Development Levy Number
, 'bank_name' bank_name -- A(1).8 Banking details
, 'bank_add_line_1' bank_add_line_1
, 'bank_add_line_2' bank_add_line_2
, 'bank_add_line_3' bank_add_line_3
, 'bank_town_or_city' bank_town_or_city
, 'bank_postal_code' bank_postal_code
, 'bank_province' bank_province
, 'sic_code' sic_code -- A(1).9 Main business activity
, 'total_employement' tot_emp -- A(1).10 Total Employment
, 'Tot Ann Payroll' tot_prev_ann_pay -- A(1).11 Total prev fin-year annual payroll
From dual;
select hoi.org_information1 sdf_name
, hoi.org_information2 sdf_add_line_1
, hoi.org_information3 sdf_add_line_2
, hoi.org_information4 sdf_add_line_3
, hoi.org_information5 sdf_add_line_4
, hoi.org_information6 sdf_town_or_city
, hoi.org_information7 sdf_province
, hoi.org_information8 sdf_postal_code
, hoi.org_information9 sdf_telephone_no
, hoi.org_information10 sdf_fax_no
, hoi.org_information11 sdf_email_id
, hoi.org_information12 sdf_mobile_no
from hr_all_organization_units haou
, hr_organization_information hoi
where haou.business_group_id = g_bg_id
and haou.organization_id = p_legal_entity_id
and haou.organization_id = hoi.organization_id
and hoi.org_information_context = 'ZA_SDF_INFO';
Select business_group_id
Into g_bg_id -- Business Group Id
From pay_payroll_actions
Where payroll_action_id = pactid ;
select ppa.legislative_parameters
into l_leg_param
from pay_payroll_actions ppa
where payroll_action_id = pactid;
Select effective_date
Into g_archive_effective_date
From pay_payroll_actions
Where payroll_action_id = pactid;
'select distinct asg.person_id
from per_assignments_f asg,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and asg.business_group_id = ppa.business_group_id
and asg.assignment_type = ''E''
order by asg.person_id';
select ppf.person_id
, paa.assignment_id
from per_all_people_f ppf
, per_all_assignments_f paa
, per_assignment_extra_info paei
, pay_payroll_actions ppa_arch
, per_periods_of_service pps
where paa.business_group_id = g_bg_id
and paa.person_id = ppf.person_id
and ppf.person_id between p_stperson and p_endperson
and paa.period_of_service_id = pps.period_of_service_id
and paei.assignment_id = paa.assignment_id
and paa.assignment_type = 'E'
and paa.primary_flag = 'Y'
-- and ppa_arch.payroll_id = paa.payroll_id -- payroll id isnt populated in R12
and ppa_arch.payroll_action_id = p_pactid
and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = p_legal_entity_id -- support archive for one or all legal entities in that bg
-- check if the person is active within the training and plan year
and ppf.effective_start_date = ( select max(effective_start_date)
from per_all_people_f ppf1
where ppf1.person_id = ppf.person_id
and ppf1.effective_start_date <= g_wsp_end_date
and ppf1.effective_end_date >= g_atr_start_date
)
-- check if the asg is active within the training and plan year
and paa.effective_start_date = ( select max(paa1.effective_start_date)
from per_all_assignments_f paa1
where paa1.assignment_id = paa.assignment_id
and paa1.effective_start_date <= g_wsp_end_date
and paa1.effective_end_date >= g_atr_start_date
);
Select business_group_id
Into g_bg_id -- Business Group Id
From pay_payroll_actions
Where payroll_action_id = pactid ;
select ppa.legislative_parameters
into l_leg_param
from pay_payroll_actions ppa
where payroll_action_id = pactid;
Select effective_date
Into g_archive_effective_date
From pay_payroll_actions
Where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
Select business_group_id
Into g_bg_id -- Business Group Id
From pay_payroll_actions
Where payroll_action_id = pactid ;
select ppa.legislative_parameters
into l_leg_param
from pay_payroll_actions ppa
where payroll_action_id = pactid;
Select effective_date
Into g_archive_effective_date
From pay_payroll_actions
Where payroll_action_id = pactid;
select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
select olp.learning_path_id, olp_tl.name
from ota_learning_paths olp
,ota_learning_paths_tl olp_tl
,ota_lp_enrollments ole
where ole.person_id = p_person_id
and ole.learning_path_id = olp.learning_path_id
and ole.path_status_code <> 'CANCELLED'
--and ole.completion_target_date between g_wsp_start_date and g_wsp_end_date --changed
and ole.creation_date between g_wsp_start_date and g_wsp_end_date
and olp.learning_path_id = olp_tl.learning_path_id
and olp_tl.language = userenv('LANG');
select oc.certification_id, oc_tl.name
from ota_certifications_b oc
,ota_certifications_tl oc_tl
,ota_cert_enrollments oce
where oce.person_id = p_person_id
and oce.certification_id = oc.certification_id
and oc.certification_id = oc_tl.certification_id
and oc_tl.language = userenv('LANG')
and oc.start_date_active <= g_wsp_end_date
and (oc.end_date_active >= g_wsp_start_date or oc.end_date_active is null)
and oce.certification_status_code not in ('CANCELLED','EXPIRED'); --AWAITING_APPROVAL,CANCELLED,CERTIFIED,ENROLLED,EXPIRED,REJECTED
select oav.activity_version_id, oav.version_name
from ota_events oe
,ota_activity_versions oav
,ota_delegate_bookings odb
,ota_booking_status_types obst
where odb.delegate_person_id = p_person_id
and odb.event_id = oe.event_id
and oe.event_type in ( 'SCHEDULED', 'SELFPACED')
and oe.activity_version_id = oav.activity_version_id
and oe.course_start_date <= g_wsp_end_date
and nvl(oe.course_end_date, g_wsp_start_date) >= g_wsp_start_date
and obst.booking_status_type_id = odb.booking_status_type_id
and obst.type <> 'C'; -- include all status except the cancelled
select pce.competence_id ,pc.name
from per_competence_elements pce
, per_competences pc
where pce.type = 'DELIVERY'
and pce.activity_version_id = p_course_id
and pce.business_group_id = g_bg_id
and pce.competence_id = pc.competence_id;
select pce.competence_id,pc.name
from per_competence_elements pce
, per_competences pc
where pce.type = 'OTA_LEARNING_PATH'
and pce.object_id = p_lp_id
and pce.business_group_id = g_bg_id
and pce.competence_id = pc.competence_id;
select pce.competence_id,pc.name
from per_competence_elements pce
, per_competences pc
where pce.type = 'OTA_CERTIFICATION'
and pce.object_id = p_cert_id
and pce.business_group_id = g_bg_id
and pce.competence_id = pc.competence_id;
select olpm.activity_version_id
from ota_learning_paths olp
, ota_lp_sections olps
, ota_learning_path_members olpm
where olp.learning_path_id = p_learning_path_id
and olp.learning_path_id = olps.learning_path_id
and olps.learning_path_section_id = olpm.learning_path_section_id;
select ocm.object_id
from OTA_CERTIFICATION_MEMBERS ocm
, OTA_CERTIFICATIONS_B oc
where oc.certification_id = p_cert_id
and ocm.certification_id = oc.certification_id
and ocm.object_type = 'H';
select count(pce.competence_id)
from per_competence_elements pce
where pce.type = 'DELIVERY'
and pce.activity_version_id = p_course_id
and pce.business_group_id = g_bg_id;
select count(pce.competence_id)
from per_competence_elements pce
where pce.type = 'OTA_LEARNING_PATH'
and pce.object_id = p_lp_id
and pce.business_group_id = g_bg_id;
select count(olpm.activity_version_id)
from ota_learning_paths olp
, ota_lp_sections olps
, ota_learning_path_members olpm
where olp.learning_path_id = p_learning_path_id
and olp.learning_path_id = olps.learning_path_id
and olps.learning_path_section_id = olpm.learning_path_section_id;
select count(pce.competence_id)
from per_competence_elements pce
where pce.type = 'OTA_CERTIFICATION'
and pce.object_id = p_cert_id
and pce.business_group_id = g_bg_id;
select count(ocm.object_id)
from OTA_CERTIFICATION_MEMBERS ocm
, OTA_CERTIFICATIONS_B oc
where oc.certification_id = p_cert_id
and ocm.certification_id = oc.certification_id
and ocm.object_type = 'H';
select puci.user_row_id
from pay_user_tables put
, pay_user_rows_f pur
, pay_user_column_instances_f puci
, pay_user_columns puc
where put.user_table_name = 'ZA_WSP_SKILLS_PRIORITIES'
and put.user_table_id = puc.user_table_id
and puc.user_column_name like p_legal_entity_id || '%'
and put.user_table_id = pur.user_table_id
and puci.user_row_id = pur.user_row_id
and puci.effective_start_date <= g_wsp_end_date
and nvl(puci.effective_end_date,g_wsp_start_date) >= g_wsp_start_date
and puci.user_column_id = puc.user_column_id;
l_per_compt_pri_tab.delete;
l_per_courses_pri_tab.delete;
select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
l_per_compt_pri_tab.delete;
l_per_courses_pri_tab.delete;
select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
select pqt.qualification_type_id, pqt.name, pqa.awarded_date, pqa.status--event id event name
from per_qualifications pqa
,per_qualification_types pqt
,per_establishment_attendances pea
where (pqa.person_id = p_person_id or pea.person_id = p_person_id)
and pqa.start_date <= g_atr_end_date
and nvl(pqa.end_date,g_atr_start_date) >= g_atr_start_date
and pqa.awarded_date between g_atr_start_date and g_atr_end_date
and pqa.qualification_type_id = pqt.qualification_type_id
and pqa.attendance_id = pea.attendance_id(+);
select pc.competence_id, pc.name , pce.achieved_date , pce.status
from per_competences pc
, per_competence_elements pce
where pce.person_id = p_person_id
and pce.competence_id = pc.competence_id
and pce.effective_date_from between g_atr_start_date and g_atr_end_date
and pce.type = 'PERSONAL';
select olp.learning_path_id, olp_tl.name, ole.completion_date, ole.path_status_code
from ota_learning_paths olp
,ota_learning_paths_tl olp_tl
,ota_lp_enrollments ole
where ole.person_id = p_person_id
and ole.learning_path_id = olp.learning_path_id
and ole.path_status_code = 'COMPLETED'
and ole.completion_date between g_atr_start_date and g_atr_end_date
and olp.learning_path_id = olp_tl.learning_path_id
and olp_tl.language = userenv('LANG');
select oc.certification_id, oc_tl.name,oce.completion_date, oce.certification_status_code
from ota_certifications_b oc
,ota_certifications_tl oc_tl
,ota_cert_enrollments oce
where oce.person_id = p_person_id
and oce.certification_id = oc.certification_id
and oc.certification_id = oc_tl.certification_id
and oc_tl.language = userenv('LANG')
and oce.completion_date between g_atr_start_date and g_atr_end_date
and oce.certification_status_code in ('CERTIFIED'); --AWAITING_APPROVAL,CANCELLED,CERTIFIED,ENROLLED,EXPIRED,REJECTED
select oav.activity_version_id, oav.version_name, odb.date_status_changed ,obst.name "status"
from ota_events oe
,ota_activity_versions oav
,ota_delegate_bookings odb
,ota_booking_status_types obst
where odb.delegate_person_id = p_person_id
and odb.event_id = oe.event_id
and oe.event_type in ( 'SCHEDULED', 'SELFPACED')
and oe.activity_version_id = oav.activity_version_id
and oe.course_start_date <= g_atr_end_date
and nvl(oe.course_end_date, g_atr_start_date) >= g_atr_start_date
and obst.booking_status_type_id = odb.booking_status_type_id
and obst.type = 'A' -- Attended
and odb.date_status_changed between g_atr_start_date and g_atr_end_date;
select puci.user_row_id
from pay_user_tables put
, pay_user_rows_f pur
, pay_user_column_instances_f puci
, pay_user_columns puc
where put.user_table_name = 'ZA_WSP_SKILLS_PRIORITIES'
and put.user_table_id = puc.user_table_id
and puc.user_column_name like to_char(p_legal_entity_id) || '%'
and put.user_table_id = pur.user_table_id
and puci.user_row_id = pur.user_row_id
and puci.effective_start_date <= g_atr_end_date
and nvl(puci.effective_end_date,g_atr_start_date) >= g_atr_start_date
and puci.user_column_id = puc.user_column_id;
select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
select user_table_id
into l_user_table_id
from pay_user_tables
where user_table_name = 'ZA_OCCUPATIONAL_TYPES'
and business_group_id is null
and legislation_code = 'ZA';
select user_column_id
into l_user_column_id_flex
from pay_user_columns
where user_table_id = l_user_table_id
and business_group_id is null
and legislation_code = 'ZA'
and user_column_name = 'Flexfield';
select user_column_id
into l_user_column_id_seg
from pay_user_columns
where user_table_id = l_user_table_id
and business_group_id is null
and legislation_code = 'ZA'
and user_column_name = 'Segment';
select user_row_id
into l_user_row_id_cat
from pay_user_rows_f
where user_table_id = l_user_table_id
and row_low_range_or_name = 'Occupational Categories'
and p_report_date between effective_start_date and effective_end_date;
select user_row_id
into l_user_row_id_lev
from pay_user_rows_f
where user_table_id = l_user_table_id
and row_low_range_or_name = 'Occupational Levels'
and p_report_date between effective_start_date and effective_end_date;
select user_row_id
into l_user_row_id_func
from pay_user_rows_f
where user_table_id = l_user_table_id
and row_low_range_or_name = 'Function Type'
and p_report_date between effective_start_date and effective_end_date;
select value
into g_cat_flex
from pay_user_column_instances_f
where user_row_id = l_user_row_id_cat
and user_column_id = l_user_column_id_flex
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select value
into g_cat_segment
from pay_user_column_instances_f
where user_row_id = l_user_row_id_cat
and user_column_id = l_user_column_id_seg
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
l_sql := 'select hl.meaning from hr_lookups hl, per_job_definitions pjd, per_jobs pj where pj.job_id = '
|| to_char(p_job_id)
|| ' and pjd.job_definition_id = pj.job_definition_id and hl.application_id = 800 and hl.lookup_type = '''
|| p_type || ''' and hl.lookup_code = pjd.' || p_segment;
l_sql := 'select hl.meaning from hr_lookups hl, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
|| to_char(p_grade_id)
|| ' and pgd.grade_definition_id = pg.grade_definition_id and hl.application_id = 800 and hl.lookup_type = '''
|| p_type ||''' and hl.lookup_code = pgd.' || p_segment;
l_sql := 'select hl.meaning from hr_lookups hl, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
|| to_char(p_position_id)
|| ' and ppd.position_definition_id = pap.position_definition_id and hl.application_id = 800 and hl.lookup_type = '''
|| p_type || ''' and hl.lookup_code = ppd.' || p_segment;
Select paaf.person_id
, paaf.assignment_id
, perf.per_information4 -- Race
, perf.sex
, perf.registered_disabled_flag
, paei.aei_information7 --legal_entity_id
, per_za_wsp_archive_pkg.get_occupational_category( p_effective_date
, paaf.assignment_id
, paaf.job_id
, paaf.grade_id
, paaf.position_id
, paaf.business_group_id)
Into l_person_id
, l_assignment_id
, l_race
, l_sex
, l_disability
, l_legal_entity_id
, l_ass_cat_name
From per_all_assignments_f paaf
, pay_assignment_actions paa
, per_all_people_f perf
, per_assignment_extra_info paei
Where paa.assignment_action_id = p_assactid
and paa.assignment_id = paaf.assignment_id
and paaf.person_id = perf.person_id
and p_effective_date between perf.effective_start_date
and perf.effective_end_date
and p_effective_date between paaf.effective_start_date
and paaf.effective_end_date
and paaf.assignment_id = paei.assignment_id
and paei.aei_information_category = 'ZA_SPECIFIC_INFO';