[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance
AND pbd.database_item_suffix = p_dimension
AND pbd.legislation_code = 'IE'
AND pbd.business_group_id is NULL
AND pbt.legislation_code = 'IE'
AND pbt.business_group_id is NULL
AND pdb.legislation_code = 'IE'
AND pdb.business_group_id is NULL;
SELECT TRIM(SUBSTR
(
legislative_parameters,
DECODE(INSTR
(
legislative_parameters,
p_token
),0,LENGTH(legislative_parameters),INSTR
(
legislative_parameters,
p_token
)) + (LENGTH(p_token) + 1),
DECODE(INSTR
(
legislative_parameters,
' ',
INSTR
(
legislative_parameters,
p_token
)),0,LENGTH(legislative_parameters),INSTR
(
legislative_parameters,
' ',
INSTR
(
legislative_parameters,
p_token
)))
-
(
INSTR
(
legislative_parameters,
p_token
) + LENGTH(p_token)
)
)),
TRIM(business_group_id)
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT TRIM(SUBSTR
(
legislative_parameters,
DECODE(
INSTR(
legislative_parameters,
p_token
)--INSTR
,0,LENGTH(legislative_parameters),
INSTR
(
legislative_parameters,
p_token
)--INSTR 2 DEFAULT FOR DECODE
)--CLOSE DECODE
+ (LENGTH(p_token) ),--END OF SECOND PARAMETER FOR SUBSTR
LENGTH(legislative_parameters)
)
)
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE GLOBAL_NAME = 'IE_NAT_MIN_WAGE_RATE'
AND legislation_code = 'IE'
AND g_archive_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT DISTINCT hasa.include_or_exclude inc_or_exc
FROM
hr_assignment_set_amendments hasa,
hr_assignment_sets has
WHERE hasa.assignment_set_id = has.assignment_set_id
AND has.business_group_id = p_business_Group_id
AND has.assignment_set_id = p_assignment_set_id;
SELECT NVL(hruserdt.get_table_value(p_business_Group_id,'EHECS_CATG_TAB','Managers',p_occupational_category,g_qtr_start_date),
NVL(hruserdt.get_table_value(p_business_Group_id,'EHECS_CATG_TAB','Clerical Workers',p_occupational_category,g_qtr_start_date),
hruserdt.get_table_value(p_business_Group_id,'EHECS_CATG_TAB','Production Workers',p_occupational_category,g_qtr_start_date))) ff
into g_occupational_category_M_C_P
FROM dual;
g_where_clause_asg_set := ' AND EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
WHERE has.assignment_set_id = hasa.assignment_set_id
AND has.business_group_id = paaf.business_group_id
AND has.assignment_set_id = '|| p_assignment_set_id
||' AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = ppf.person_id) ';
g_where_clause_asg_set := ' AND NOT EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
WHERE has.assignment_set_id = hasa.assignment_set_id
AND has.business_group_id = paaf.business_group_id
AND has.assignment_set_id = '|| p_assignment_set_id
||' AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = ppf.person_id) ';
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
select hou.organization_id org_id
,hou.name employer_name
,hla.address_line_1 addr1
,hla.address_line_2 addr2
,hla.address_line_3 addr3
,null addr4
,null addr5
,hla.TOWN_OR_CITY City
,flv.meaning County
,hla.COUNTRY Country_Name
,hla.REGION_1
from hr_organization_units hou
,hr_organization_information hoi
,hr_locations_all hla
,fnd_lookup_values flv
where hoi.org_information_context='IE_EMPLOYER_INFO'
and hoi.organization_id=c_org_id
and hoi.organization_id=hou.organization_id
and hou.business_group_id= c_bg_id
and hou.location_id=hla.location_id(+)
and flv.lookup_type(+) = 'IE_COUNTY'
and flv.language(+) = 'US'
and flv.lookup_code(+) = hla.REGION_1;
select hoi.org_information3 cbr_no
,hoi.org_information13 person_id
,hoi.org_information17 position
,hoi.org_information19 email
,hoi.org_information20 phone
from hr_organization_units hou
,hr_organization_information hoi
where hoi.org_information_context='IE_EHECS'
and hoi.organization_id=c_org_id
and hoi.organization_id=hou.organization_id
and hou.business_group_id= c_bg_id
and hoi.ORG_INFORMATION1 = p_year
and hoi.ORG_INFORMATION2 = p_qtr;
select papf.full_name declarant_name
from per_all_people_f papf
where papf.person_id=c_person_id ;
sqlstr := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
pl_address.delete;
pl_address_final.delete;
l_select_str VARCHAR2(3000);
l_select_str1 VARCHAR2(3000);
l_select_str1 :='select count (distinct paaf.assignment_id )
from per_all_assignments_f paaf,
per_all_people_f ppf,
pay_all_payrolls_f papf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
hr_soft_coding_keyflex scl
where paaf.business_group_id = '|| g_business_group_id
||' and papf.business_group_id = paaf.business_group_id and '
||''''|| g_reference_end_date||''''||' between paaf.effective_start_date and paaf.effective_end_date '
||' and paaf.person_id = ppf.person_id '
||' and paaf.assignment_status_type_id in (SELECT assignment_status_type_id '
||' FROM per_assignment_status_types '
||' WHERE per_system_status = '||'''ACTIVE_ASSIGN'''
||' AND active_flag = '||'''Y'''||') '
||' and paaf.primary_flag= '||'''Y'''
||' and paaf.employment_category = '
-- ||' IN ('||'''FT'''||','||'''FR'''||','||'''PT'''||','||'''PR'''||','||'''AT'''||') '
||'nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Full_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
|| ',nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Part_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
|| ', hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Apprentice_Trainee'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
|| ' ))'
||' and ppf.person_id between '|| stperson || ' and ' || endperson
||g_where_clause1
||' and ppa.payroll_action_id = paa.payroll_action_id '
||' and paa.assignment_id=paaf.assignment_id'
||' and (paa.source_action_id is not null or ppa.action_type in ('||'''I'''||','||'''V'''||','||'''B'''||'))'
||' and ppa.effective_date <='||''''||g_reference_end_date||''''
||' and ppa.effective_date >='||''''||g_reference_start_date||''''
||' and paa.action_status = '||'''C'''
||' and ppa.action_type in ('||'''R'''||','||'''Q'''||','||'''I'''||','
||'''V'''||','||'''B'''||')'
||' and papf.payroll_id = paaf.payroll_id '
||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
||' and scl.segment4 = to_char('||g_employer_id||') '
||g_where_clause
||g_where_clause_asg_set
||' Order by paaf.assignment_id';
OPEN csr_get_asg1 FOR l_select_str1;
l_select_str :='select asgid from (
select asgid,round(mod(dbms_random.value*'||g_sample_fraction||','||g_sample_fraction||')) serial '
|| 'from ( '
|| 'select distinct paaf.assignment_id asgid
from per_all_assignments_f paaf,
per_all_people_f ppf,
pay_all_payrolls_f papf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
hr_soft_coding_keyflex scl
where paaf.business_group_id = '|| g_business_group_id
||' and papf.business_group_id = paaf.business_group_id and '
||''''|| g_reference_end_date||''''||' between paaf.effective_start_date and paaf.effective_end_date '
||' and paaf.person_id = ppf.person_id '
||' and paaf.assignment_status_type_id in (SELECT assignment_status_type_id '
||' FROM per_assignment_status_types '
||' WHERE per_system_status = '||'''ACTIVE_ASSIGN'''
||' AND active_flag = '||'''Y'''||') '
||' and paaf.primary_flag= '||'''Y'''
||' and paaf.employment_category = '
-- ||' IN ('||'''FT'''||','||'''FR'''||','||'''PT'''||','||'''PR'''||','||'''AT'''||') '
||'nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Full_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
|| ',nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Part_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
|| ', hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Apprentice_Trainee'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
|| ' ))'
||' and ppf.person_id between '|| stperson || ' and ' || endperson
||g_where_clause1
||' and ppa.payroll_action_id = paa.payroll_action_id '
||' and paa.assignment_id=paaf.assignment_id'
||' and (paa.source_action_id is not null or ppa.action_type in ('||'''I'''||','||'''V'''||','||'''B'''||'))'
||' and ppa.effective_date <='||''''||g_reference_end_date||''''
||' and ppa.effective_date >='||''''||g_reference_start_date||''''
||' and paa.action_status = '||'''C'''
||' and ppa.action_type in ('||'''R'''||','||'''Q'''||','||'''I'''||','
||'''V'''||','||'''B'''||')'
||' and papf.payroll_id = paaf.payroll_id '
||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
||' and scl.segment4 = to_char('||g_employer_id||') '
||g_where_clause
||g_where_clause_asg_set
||' Order by paaf.assignment_id'
||' ))'
||' where rownum<='||l_total_sample
||' order by serial';
Fnd_file.put_line(FND_FILE.LOG,'Dynamic Query:'||l_select_str );
OPEN csr_get_asg FOR l_select_str; -- ref cursor
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT distinct paa.payroll_action_id,paa.assignment_id,paaf.person_id,papf.employee_number
FROM pay_assignment_actions paa
,per_all_assignments_f paaf
,per_all_people_f papf -- 9795571
WHERE paa.assignment_action_id = p_cess_aact
and paa.assignment_id=paaf.assignment_id
and papf.person_id=paaf.person_id; -- 9795571
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paaf
,pay_all_payrolls_f papf
,hr_soft_coding_keyflex scl
WHERE paaf.person_id=c_person_id
AND paa.assignment_id=paaf.assignment_id
AND paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('Q','B','R','I','V')
AND paa.action_status ='C'
AND paa.source_action_id is not null
AND ppa.effective_date<= c_till_date
AND papf.payroll_id=paaf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_employer_id);
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paaf
,pay_all_payrolls_f papf
,hr_soft_coding_keyflex scl
WHERE paaf.person_id=c_person_id
AND paa.assignment_id=paaf.assignment_id
AND paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('Q','B','R','I','V')
AND paa.action_status ='C'
AND paa.source_action_id is not null
AND papf.payroll_id=paaf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_employer_id)
AND to_char(ppa.effective_date,'yyyy')=to_char(g_year); -- 9734490
select hoi.org_information18 hrs_per_day
from hr_organization_units hou
,hr_organization_information hoi
where hoi.org_information_context='IE_EHECS'
and hoi.organization_id=c_org_id
and hoi.organization_id=hou.organization_id
and hou.business_group_id= c_bg_id;
SELECT distinct paaf.assignment_id, paaf.person_id, paaf.payroll_id,
--decode(paaf.EMPLOYMENT_CATEGORY,'FT','F','FR','F','PR','P','PT','P',paaf.EMPLOYMENT_CATEGORY) EMP_CATG,
/* 6856486 */
decode(paaf.EMPLOYMENT_CATEGORY
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Full_Time',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'F'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'P'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'AT'
) EMP_CATG,
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_reference_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_reference_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_reference_start_date)
)
) EHECS_CATG
, paaf.effective_start_date
,normal_hours normal_hours /*6856473*/
,frequency frequency /*6856473*/
,hourly_salaried_code hourly_or_salaried /*6856473*/
,substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)) status_code
,paaf.assignment_number assignment_number
,decode(paaf.EMPLOYMENT_CATEGORY
,hruserdt.get_table_value(paaf.business_group_id,'NES_ASG_CATG_TAB','Indefinite',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'INDEF'
,hruserdt.get_table_value(paaf.business_group_id,'NES_ASG_CATG_TAB','Definite',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'DEF'
,hruserdt.get_table_value(paaf.business_group_id,'NES_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'AT'
) NES_EMPLOYEMENT_CATG /* 10081528 */
FROM
per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
and paaf.person_id = c_person_id
/* 9795685 */
--
/*and paaf.effective_start_date <= g_reference_start_date
and paaf.effective_end_date >= g_reference_end_date */
and paaf.effective_start_date <= g_reference_end_date
and paaf.effective_end_date >= g_reference_start_date
--
and assignment_status_type_id in (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y')/*6856473 to filter the terminated assingment*/
ORDER BY paaf.effective_start_date desc;
select papf.full_name full_name,
papf.national_identifier PPS,
pa.address_line1 address_line1,
pa.address_line2 address_line2,
pa.address_line3 address_line3,
null address_line4,
null address_line5,
pa.town_or_city city ,
flv.meaning County,
pa.country Country
from per_addresses pa,
per_all_people_f papf,
fnd_lookup_values flv
where papf.person_id = cp_person_id
and papf.person_id=pa.person_id(+)
and pa.primary_flag (+)= 'Y' --is address primary ?
and pa.date_from(+) <= cp_effective_date
and nvl(pa.date_to, cp_effective_date) >= cp_effective_date
and flv.lookup_type(+) = 'IE_COUNTY'
and flv.language(+) = 'US'
and flv.lookup_code(+) = pa.REGION_1;
SELECT piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT ppa.payroll_action_id pact_id,
ppa.date_earned date_earned
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_action_id=c_assignment_action_id
AND paa.payroll_action_id=ppa.payroll_action_id;
select ptp.period_type period_type,
count(ptp.period_num) pay_periods
from
pay_payroll_actions ppa
, per_time_periods ptp
, per_time_period_types tptype
WHERE ppa.payroll_action_id=c_payroll_action_id
AND ptp.payroll_id=ppa.payroll_id
AND ptp.period_type = tptype.period_type
AND ptp.end_date between
c_start_date and c_end_date
Group by ptp.period_type ;
select aei_information1 PPSN_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'IE_ASG_OVERRIDE';
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paaf
,pay_all_payrolls_f papf
,hr_soft_coding_keyflex scl
WHERE paaf.person_id=c_person_id
AND paa.assignment_id in (select paaf.assignment_id
from per_all_assignments_f paaf, per_assignment_extra_info paei
where paaf.person_id = c_person_id
and paaf.assignment_id=paei.assignment_id
and paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = c_ppsn_override --'314678745T'
)
AND paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('Q','B','R','I','V')
AND paa.action_status ='C'
AND paa.source_action_id is not null
AND ppa.effective_date<= c_till_date
AND papf.payroll_id=paaf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_employer_id);
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paaf
,pay_all_payrolls_f papf
,hr_soft_coding_keyflex scl
WHERE paaf.person_id=c_person_id
AND paa.assignment_id in (select paaf.assignment_id
from per_all_assignments_f paaf, per_assignment_extra_info paei
where paaf.person_id = c_person_id
and paaf.assignment_id=paei.assignment_id
and paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = c_ppsn_override --'314678745T'
)
AND paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('Q','B','R','I','V')
AND paa.action_status ='C'
AND paa.source_action_id is not null
AND papf.payroll_id=paaf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_employer_id)
AND to_char(ppa.effective_date,'yyyy')=to_char(g_year);
pl_address.delete;
pl_address_final.delete;
hr_utility.set_location(' Before Inserting in to IE_NES_EMPLOYEE_INFORMATION ', 890);
hr_utility.set_location(' After Inserting in to IE_NES_EMPLOYEE_INFORMATION ', 890);
hr_utility.set_location(' Before Inserting in to IE_NES_PART1 ', 890);
hr_utility.set_location(' After Inserting in to IE_NES_PART1 ', 890);
SELECT
action_information1 full_name,
action_information2 ppsn,
action_information3 reference_id,
action_information4 address_line1,
action_information5 address_line2,
action_information6 address_line3,
action_information7 address_line4,
action_information8 address_line5
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'AAP'
AND action_information_category ='IE_NES_EMPLOYEE_INFORMATION';
SELECT
NVL(action_information1,0) annual_earning,
NVL(action_information2,0) irreg_earning,
NVL(action_information3,0) annual_bik,
NVL(action_information4,0) no_of_weeks,
NVL(action_information5,0) paid_holiday,
NVL(action_information6,0) other_absence,
NVL(action_information7,0) employment_type,
NVL(action_information8,0) freq_pay,
NVL(action_information9,0) ref_period_pay,
NVL(action_information10,0) status_code,
NVL(action_information11,0) gross_earning,
NVL(action_information12,0) overtime_earning,
NVL(action_information13,0) shift_allowance,
NVL(action_information14,0) total_commission,
NVL(action_information15,0) prsi_class,
NVL(action_information16,0) ref_period_hours,
NVL(action_information17,0) contracted_hours,
NVL(action_information18,0) overtime_hours
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'AAP'
AND action_information_category ='IE_NES_PART1';
SELECT
action_information6 year,
action_information7 month,
action_information8 report_type,
action_information9 software_name,
action_information10 software_version,
action_information11 vendor_name,
action_information12 Vendor_phone,
action_information13 org_id,
action_information14 employer_name,
action_information15 addr1,
action_information16 addr2,
action_information17 addr3,
action_information18 addr4,
action_information19 addr5,
action_information20 change_indicator,
action_information21 cbr_no,
action_information22 declarant_name,
action_information23 declarant_phone,
action_information24 declarant_email,
action_information25 declare_date,
action_information26 declarant_position
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_NES_HEADER';
SELECT count(*)
FROM pay_assignment_actions paa,
pay_action_information pai
WHERE paa.payroll_action_id=c_pact_id
AND paa.source_action_id is null
AND pai.action_context_id=paa.assignment_action_id
AND pai.action_information_category='IE_NES_PART1';