[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name
into l_business_group_name
from hr_all_organization_units
where organization_id = p_bg_id;
insert into hr_h2pi_data_feed_hist
(start_date,
end_date,
sequence_number,
business_group_id,
client_id,
business_group_name,
object_version_number,
request_id,
program_application_id,
program_id,
program_update_date)
values
(p_start_date,
p_end_date,
hr_h2pi_data_feed_hist_s.nextval,
p_bg_id,
p_client_id,
l_business_group_name,
1,
p_request_id,
null,
null,
sysdate);
SELECT ogi.org_information_context context
,ogi.org_information1 ogi1
,ogi.org_information2 ogi2
,ogi.org_information3 ogi3
,ogi.org_information4 ogi4
,ogi.org_information5 ogi5
,ogi.org_information6 ogi6
,ogi.org_information7 ogi7
,ogi.org_information8 ogi8
,ogi.org_information9 ogi9
,ogi.org_information10 ogi10
,ogi.org_information11 ogi11
,ogi.org_information12 ogi12
,ogi.org_information13 ogi13
,ogi.org_information14 ogi14
,ogi.org_information15 ogi15
,ogi.org_information16 ogi16
,ogi.org_information17 ogi17
,ogi.org_information18 ogi18
,ogi.org_information19 ogi19
,ogi.org_information20 ogi20
from hr_organization_units org,
hr_organization_information ogi,
hr_organization_information ogi2,
hr_org_info_types_by_class oitbc,
hr_org_information_types oit
where org.organization_id = ogi.organization_id
and ogi.organization_id = ogi2.organization_id
and ogi2.org_information_context = 'CLASS'
and ogi2.org_information1 IN ('HR_ORG', 'HR_LEGAL', 'HR_BG', 'HR_PAYEE', 'US_CARRIER', 'US_WC_CARRIER ')
and ogi.org_information_context = oit.org_information_type
and ogi.org_information_context IN ('Work Day Information',
'1099R Magnetic Report Rules',
'EEO-1 Filing',
'Employer Identification',
'Federal Tax Rules',
'NACHA Rules',
'SQWL Employer Rules 1',
'SQWL Employer Rules 2',
'SQWL GN Transmitter Rules',
'SQWL SS Transmitter Rules',
'PAY_US_STATE_WAGE_PLAN_INFO',
'Costing Information',
'Organization Name Alias',
'Work Day Information',
'Legal Entity Accounting',
'Multiple Worksite Reporting',
'TIAA-CREF Setup Codes',
'VETS-100 Filing',
'W2 Reporting Rules',
'State Tax Rules',
'Local Tax Rules')
and oitbc.org_classification = ogi2.org_information1
and oitbc.org_information_type = oit.org_information_type
and (oit.legislation_code is NULL or oit.legislation_code = 'US')
and ogi.org_information_id = p_org_information_id;
SELECT column_seq_num,
application_column_name col_name
FROM fnd_descr_flex_column_usages
WHERE application_id = 800
AND descriptive_flex_context_code = p_context
ORDER BY column_seq_num;
select fnd_flex_descval.segment_id(idx+1),
fnd_flex_descval.segment_value(idx+1),
fnd_flex_descval.segment_description(idx+1)
into l_seg_id,
l_seg_value,
l_seg_desc
from dual;
l_query_string := 'select emp.*, :q_client_id client_id from hr_h2pi_employees_v emp where business_group_id = :q_bg_id and last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select adr.last_upd_date, GREATEST(per.per_date, adr.date_from) date_from,adr.business_group_id, adr.address_id, adr.person_id, adr.style, ' ||
' adr.date_to, adr.address_type, adr.address_line1, adr.address_line2, adr.address_line3, adr.town_or_city, adr.region_1, adr.region_2, ' ||
' adr.region_3, adr.postal_code, adr.country, adr.telephone_number_1, adr.telephone_number_2, adr.telephone_number_3, adr.add_information13, ' ||
' adr.add_information14, adr.add_information15, adr.add_information16, adr.add_information17, adr.add_information18, adr.add_information19, ' ||
' adr.add_information20, adr.addr_attribute_category, adr.addr_attribute1, adr.addr_attribute2, adr.addr_attribute3, adr.addr_attribute4, ' ||
' adr.addr_attribute5, adr.addr_attribute6, adr.addr_attribute7, adr.addr_attribute8, adr.addr_attribute9, adr.addr_attribute10, ' ||
' adr.addr_attribute11, adr.addr_attribute12, adr.addr_attribute13, adr.addr_attribute14, adr.addr_attribute15, adr.addr_attribute16, ' ||
' adr.addr_attribute17, adr.addr_attribute18, adr.addr_attribute19, adr.addr_attribute20, :q_client_id client_id ' ||
' FROM hr_h2pi_addresses_v adr, ' ||
' (select min(effective_start_date) per_date, person_id from per_all_people_f a, per_person_types b where b.system_person_type IN (''EMP'', ''EMP_APL'') ' ||
' and a.person_type_id = b.person_type_id group by person_id) per, per_all_people_f per2 ' ||
' WHERE adr.person_id = per.person_id AND per.person_id = per2.person_id ' ||
' AND per.per_date = per2.effective_start_date and adr.business_group_id = :q_bg_id and ( per2.last_update_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' OR per.person_id IN (select adr1.person_id from hr_h2pi_addresses_v adr1 where adr1.business_group_id = :q_bg_id1 and adr1.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' group by adr1.person_id having count(*)> 0 ) )' ;
l_query_string := 'select ' ||
' last_upd_date,' ||
' nvl(business_group_id,:q_bg_id1 ) business_group_id, ' ||
' location_id, ' ||
' location_code, ' ||
' description, ' ||
' address_line_1,' ||
' address_line_2, ' ||
' address_line_3, ' ||
' town_or_city, ' ||
' country, ' ||
' postal_code, ' ||
' region_1, ' ||
' region_2, ' ||
' region_3, ' ||
' style, ' ||
' inactive_date, ' ||
' telephone_number_1, ' ||
' telephone_number_2, ' ||
' telephone_number_3, ' ||
' loc_information13, ' ||
' loc_information14, ' ||
' loc_information15, ' ||
' loc_information16, ' ||
' loc_information17, ' ||
' loc_information18, ' ||
' loc_information19, ' ||
' loc_information20, ' ||
' attribute_category, ' ||
' attribute1, ' ||
' attribute2, ' ||
' attribute3, ' ||
' attribute4, ' ||
' attribute5, ' ||
' attribute6, ' ||
' attribute7, ' ||
' attribute8, ' ||
' attribute9, ' ||
' attribute10, ' ||
' attribute11, ' ||
' attribute12, ' ||
' attribute13, ' ||
' attribute14, ' ||
' attribute15, ' ||
' attribute16, ' ||
' attribute17, ' ||
' attribute18, ' ||
' attribute19, ' ||
' attribute20, ' ||
' :q_client_id client_id ' ||
' from hr_h2pi_locations_v where ( business_group_id = :q_bg_id OR business_group_id is null ) ' ||
' and last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_assignments_v p1 where business_group_id = :q_bg_id and effective_start_date >= ' ||
' ((select min(effective_start_date) from hr_h2pi_assignments_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.assignment_id = p1.assignment_id ))';
queryCtx := DBMS_XMLQuery.newContext('select pay.*,:q_client_id client_id from hr_h2pi_pay_bases_v pay where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select org.*,:q_client_id client_id from hr_h2pi_hr_organizations_v org where business_group_id = :q_bg_id and ' ||
' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ');
queryCtx := DBMS_XMLQuery.newContext('select pay.*,:q_client_id client_id from hr_h2pi_payrolls_v pay where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select et.*,:q_client_id client_id from hr_h2pi_element_types_v et where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select iv.*,:q_client_id client_id from hr_h2pi_input_values_v iv where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select el.*,:q_client_id client_id from hr_h2pi_element_links_v el where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select bg.*,:q_client_id client_id from hr_h2pi_bg_and_gre_v bg where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select pmt.*,:q_client_id client_id from hr_h2pi_org_payment_methods_v pmt where business_group_id = :q_bg_id');
queryCtx := DBMS_XMLQuery.newContext('select pat.*,:q_bg_id business_group_id,:q_client_id client_id from hr_h2pi_patch_status_v pat');
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_federal_tax_rules_v p1 where business_group_id = :q_bg_id ' ||
' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_federal_tax_rules_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.emp_fed_tax_rule_id = p1.emp_fed_tax_rule_id ))' ;
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_state_tax_rules_v p1 where business_group_id = :q_bg_id' ||
' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_state_tax_rules_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.emp_state_tax_rule_id = p1.emp_state_tax_rule_id ))';
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_county_tax_rules_v p1 where business_group_id = :q_bg_id' ||
' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_county_tax_rules_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.emp_county_tax_rule_id = p1.emp_county_tax_rule_id ))';
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_city_tax_rules_v p1 where business_group_id = :q_bg_id' ||
' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_city_tax_rules_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.emp_city_tax_rule_id = p1.emp_city_tax_rule_id ))';
l_query_string := 'select org.*,:q_client_id client_id from hr_h2pi_organization_class_v org where business_group_id = :q_bg_id and ' ||
' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select pos.*,:q_client_id client_id from hr_h2pi_periods_of_service_v pos where business_group_id = :q_bg_id and ' ||
' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select sal.*,:q_client_id client_id from hr_h2pi_salaries_v sal where business_group_id = :q_bg_id and ' ||
' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select org.*,:q_client_id client_id from hr_h2pi_organization_info_v org where business_group_id = :q_bg_id ' ||
' and last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_cost_allocations_v p1 where business_group_id = :q_bg_id ' ||
' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_cost_allocations_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.cost_allocation_id = p1.cost_allocation_id ))';
l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_payment_methods_v p1 where business_group_id = :q_bg_id ' ||
' and ( (p1.payee_type <> ''P'') OR (p1.payee_type IS NULL) )' ||
' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_payment_methods_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.personal_payment_method_id = p1.personal_payment_method_id ))';
l_query_string := 'select en.*,:q_client_id client_id from hr_h2pi_element_names_v en where business_group_id = :q_bg_id and ' ||
' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
l_query_string := 'select p1.*, :q_client_id client_id from hr_h2pi_element_entries_v p1 where business_group_id = :q_bg_id and ' ||
' effective_start_date >= ((select min(effective_start_date) from hr_h2pi_element_entries_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.element_entry_id = p1.element_entry_id ))';
l_query_string := 'select p1.*, :q_client_id client_id from hr_h2pi_element_entry_values_v p1 where business_group_id = :q_bg_id and ' ||
' effective_start_date >= ((select min(effective_start_date) from hr_h2pi_element_entry_values_v p2 where p2.last_upd_date between ' ||
' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
' and p2.element_entry_id = p1.element_entry_id ))';
queryCtx := DBMS_XMLQuery.newContext('select geo.*, :q_bg_id business_group_id, :q_client_id client_id from hr_h2pi_us_modified_geocodes_v geo where patch_name = (select NVL(MAX(patch_name),''GEOCODE_1900_Q1'') FROM hr_h2pi_patch_status_v) ');
queryCtx := DBMS_XMLQuery.newContext('select cit.*, :q_bg_id business_group_id, :q_client_id client_id from hr_h2pi_us_city_names_v cit');