[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)
--8624704
DECODE(INSTR(legislative_parameters,'XML_REPORT_TAG'),0, LENGTH(legislative_parameters),
INSTR(legislative_parameters,'XML_REPORT_TAG') - (INSTR(legislative_parameters,p_token) + LENGTH(p_token)) )
--8624704
)
)
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
from hr_organization_units hou
,hr_organization_information hoi
,hr_locations_all hla
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(+); */
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 -- bug 6850742
,hoi.org_information19 email -- bug 6850742
,hoi.org_information20 phone -- bug 6850742
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
,pav.telephone_number_1 declarant_phone
,papf.email_address declarant_email
,pap.NAME declarant_position
from per_all_people_f papf
,per_all_assignments_f paaf
,per_all_positions pap
,per_addresses_v pav
where papf.person_id=c_person_id ;
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_str :='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,
hr_soft_coding_keyflex scl
where paaf.business_group_id = '|| g_business_group_id
||' and papf.business_group_id = paaf.business_group_id '
||' and paaf.effective_start_date <= '||''''||g_qtr_end_date||''''
||' and paaf.effective_end_date >= '||''''||g_qtr_start_date||''''
||' and paaf.person_id = ppf.person_id '
||' 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 = '||pactid
||' 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';
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;
-- Insert assignment into PAY_ASSIGNMENT_ACTIONS TABLE
/* hr_nonrun_asact.insact(lockingactid => lockingactid
,assignid => l_assg_id
,pactid => pactid
,chunk => chunk
,greid => NULL);
-- Insert assignment into PAY_TEMP_OBJECT_ACTION TABLE.
hr_utility.set_location(' Before hr_nonrun_asact.insact call',550);
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
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_qtr_start_date),'F'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'P'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'AT'
) EMP_CATG,
/*
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date)
)
) EHECS_CATG*/
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date)
)
) EHECS_CATG
, paaf.effective_start_date
,normal_hours normal_hours /*6856473*/
,frequency frequency /*6856473*/
,hourly_salaried_code hourly_or_salaried /*6856473*/
,papf.PERIOD_TYPE /* 9776525 */
FROM
per_all_assignments_f paaf,
pay_all_payrolls_f papf -- 9776525
WHERE paaf.assignment_id = c_assignment_id
and paaf.person_id = c_person_id
and paaf.effective_start_date <= g_qtr_end_date
and paaf.effective_end_date >= g_qtr_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*/
and papf.payroll_id=paaf.payroll_id -- 9776525
ORDER BY paaf.effective_start_date desc;
SELECT /*+ USE_NL(paa, ppa) */
-- max(paa.assignment_action_id) assignment_action_id
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) assignment_action_id /* 9852564 */
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asg_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date between g_qtr_start_date and g_qtr_end_date
--bug 7294966
/* ( select max(pay_advice_date)
from per_time_periods
where payroll_id = p_payroll_id
and pay_advice_date <= g_qtr_end_date
)
*/
--bug 7294966
AND ppa.action_type in ('R', 'Q', 'I', 'V','B')
AND paa.action_status = 'C'
HAVING max(paa.assignment_action_id) IS NOT NULL;
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 count(ptp.time_period_id)
from per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ptp.payroll_id = p_payroll_id
and ptp.regular_payment_date between g_qtr_start_date and g_qtr_end_date
and ppa.payroll_id=p_payroll_id
and ppa.time_period_id=ptp.time_period_id
and ppa.action_type in ('R', 'Q', 'I', 'V','B')
and ppa.payroll_action_id=paa.payroll_action_id
and paa.assignment_id=p_assignment_id
and paa.source_action_id is not null
and paa.action_status = 'C';
select count(ptp.time_period_id)
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and ptp.time_period_id in ( select ppa.time_period_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_id=p_payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V','B')
and ppa.payroll_action_id=paa.payroll_action_id
and paa.assignment_id=p_assignment_id
and paa.source_action_id is not null
and paa.action_status = 'C'
and ppa.effective_date between g_qtr_start_date and g_qtr_end_date
)
and ptp.regular_payment_date between g_qtr_start_date and g_qtr_end_date;
hr_utility.set_location(' Before Inserting PAY_US_RPT_TOTALS', 890);
INSERT INTO PAY_US_RPT_TOTALS
(BUSINESS_GROUP_ID
,ATTRIBUTE1 --payroll_action_id
,ATTRIBUTE2 --Assignment_id
,ATTRIBUTE3 --EHECS_CATG M(Managers)/C(Clerk)/P(Production Wrk)
,ATTRIBUTE4 --EMP_CATG F(full)/P(Part)/AT(Apprnt)
,ATTRIBUTE5 --l_nmw_bal_val Hourly Rate
,ATTRIBUTE6 --l_regwg_bal_val Regular Earning
,ATTRIBUTE7 --l_ovrt_bal_val Overtime Payments
,ATTRIBUTE8 --l_irrb_bal_val Irregular Earnings
,ATTRIBUTE9 --l_app_wg_bal_val_tot Irregular Earnings + Regular Earning + Overtime Payments
,ATTRIBUTE10 --l_chrs_bal_val Normal Working Hours
,ATTRIBUTE11 --l_othr_bal_val Paid Overtime Hours
,ATTRIBUTE12 --l_nmw_count Count for National Min Wage
,ATTRIBUTE13 --l_mat_bal_val Paid Maternity Leave
,ATTRIBUTE14 --l_sic_bal_val Paid Sick leave Hours
,ATTRIBUTE15 --l_otl_bal_val Paid Other Leave Hours
,ATTRIBUTE16 --l_lap_bal_val_tot l_mat_bal_val + l_sic_bal_val + l_otl_bal_val
,ATTRIBUTE17 --l_pen_bal_val_tot (IE RBS + IE PRSA + IE RAC) ER Contri
,ATTRIBUTE18 --l_prsi_bal_val IE PRSI Employer
,ATTRIBUTE19 --l_incct_bal_val Income Continuance Insurance
,ATTRIBUTE20 --l_red_bal_val Redundancy Payments
,ATTRIBUTE21 --l_otsoc_bal_val Employee Related Payments
,ATTRIBUTE22 --l_ssec_bal_val_tot l_prsi_bal_val+l_incct_bal_val+l_red_bal_val+l_otsoc_bal_val
,ATTRIBUTE23 --l_bik_veh_bal_val IE BIK Company Vehicle
,ATTRIBUTE24 --l_stks_bal_val Stock Options and Share Purchase.
,ATTRIBUTE25 --l_vhi_bal_val Voluntary Sickness Insurance
,ATTRIBUTE26 --l_hse_bal_val Staff Housing
,ATTRIBUTE27 --l_otben_bal_val Other Benifits
,ATTRIBUTE28 --l_tr_sub_bal_val Training Subsidies
,ATTRIBUTE29 --l_ot_sub_bal_val Other Subsidies
,ATTRIBUTE30 --l_refund_bal_val Refunds
,ATTRIBUTE31 --l_rbs_er_bal_val IE RBS ER Contribution
,ATTRIBUTE32 --l_prsa_er_bal_val IE PRSA ER Contribution
,ATTRIBUTE33 --l_rac_er_bal_val IE RAC ER Contribution
,ATTRIBUTE34 --l_al_bal_val Annual Leave and Bank Holidays (both hours and days) 6856473
)
VALUES
(p_business_group_id
,p_payroll_action_id
,l_valid_asg_rec.assignment_id
,l_valid_asg_rec.EHECS_CATG
,l_valid_asg_rec.EMP_CATG
,l_nmw_bal_val
,l_regwg_bal_val
,l_ovrt_bal_val
,l_irrb_bal_val
,l_app_wg_bal_val_tot
,l_chrs_bal_val
,l_othr_bal_val
,l_nmw_count
,l_mat_bal_val
,l_sic_bal_val
,l_otl_bal_val
,l_lap_bal_val_tot
,l_pen_bal_val_tot
,l_prsi_bal_val
,l_incct_bal_val
,l_red_bal_val
,l_otsoc_bal_val
,l_ssec_bal_val_tot
,l_bik_veh_bal_val
,l_stks_bal_val
,l_vhi_bal_val
,l_hse_bal_val
,l_otben_bal_val
,l_tr_sub_bal_val
,l_ot_sub_bal_val
,l_refund_bal_val
,l_rbs_er_bal_val
,l_prsa_er_bal_val
,l_rac_er_bal_val
,l_al_bal_val -- 6856473
);
hr_utility.set_location(' After Inserting PAY_US_RPT_TOTALS', 900);
SELECT DISTINCT ppf.person_id, paa.assignment_id
FROM per_all_people_f ppf
,per_all_assignments_f paa
,pay_payroll_actions ppa
,pay_temp_object_actions ptoa
WHERE paa.business_group_id = ppa.business_group_id
AND paa.person_id = ppf.person_id
AND ppa.payroll_action_id = p_payroll_action_id
AND paa.business_group_id = g_business_Group_id
AND ppa.payroll_action_id = ptoa.payroll_action_id
AND ptoa.Object_id = paa.assignment_id
AND ptoa.object_action_id = p_object_action_id;
SELECT
action_information6 year,
action_information7 quarter,
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_EHECS_HEADER';
SELECT
EHECS_CATG,
--SUM(decode(EMP_CATG,'P',sum_nmw_bal_val,'F',sum_nmw_bal_val)) nmw_pt_ft_mg_cl_ot
SUM(decode(EMP_CATG,'P',sum_nmw_count_val,'F',sum_nmw_count_val)) nmw_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_regwg_bal_val)) regwg_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_ovrt_bal_val)) ovrt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_irrb_bal_val)) irrb_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_regwg_bal_val)) regwg_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_ovrt_bal_val)) ovrt_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_irrb_bal_val)) irrb_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_regwg_bal_val)) regwg_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_ovrt_bal_val)) ovrt_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_irrb_bal_val)) irrb_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_app_wg_bal_val_tot)) appwg_at_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_chrs_bal_val)) chrs_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_chrs_bal_val)) chrs_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_chrs_bal_val)) chrs_at_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_othr_bal_val)) othr_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_othr_bal_val)) othr_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_othr_bal_val)) othr_at_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_al_bal_val)) al_ft_mg_cl_ot -- 6856473
,SUM(decode(EMP_CATG,'P',sum_al_bal_val)) al_pt_mg_cl_ot -- 6856473
,SUM(decode(EMP_CATG,'AT',sum_al_bal_val)) al_at_mg_cl_ot -- 6856473
,SUM(decode(EMP_CATG,'F',sum_mat_bal_val)) mat_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_sic_bal_val)) sic_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'F',sum_otl_bal_val)) otl_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_mat_bal_val)) mat_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_sic_bal_val)) sic_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_otl_bal_val)) otl_pt_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_mat_bal_val)) mat_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_sic_bal_val)) sic_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_otl_bal_val)) otl_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_lap_bal_val_tot)) lap_at_mg_cl_ot
,SUM(sum_pen_bal_val_tot) pen_pt_ft_at_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_prsi_bal_val,'F',sum_prsi_bal_val)) prsi_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_incct_bal_val,'F',sum_incct_bal_val)) incct_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_red_bal_val,'F',sum_red_bal_val)) red_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_otsoc_bal_val,'F',sum_otsoc_bal_val)) otsoc_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_prsi_bal_val)) prsi_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_incct_bal_val)) incct_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_red_bal_val)) red_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_otsoc_bal_val)) otsoc_at_mg_cl_ot
,SUM(decode(EMP_CATG,'AT',sum_ssec_bal_val_tot)) ssec_at_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_bik_veh_bal_val,'F',sum_bik_veh_bal_val)) car_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_stks_bal_val,'F',sum_stks_bal_val)) stks_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_vhi_bal_val,'F',sum_vhi_bal_val)) vhi_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_hse_bal_val,'F',sum_hse_bal_val)) hse_pt_ft_mg_cl_ot
,SUM(decode(EMP_CATG,'P',sum_otben_bal_val,'F',sum_otben_bal_val)) otben_pt_ft_mg_cl_ot
,SUM(sum_tr_sub_bal_val) trsub_all
,SUM(sum_ot_sub_bal_val) otsub_all
,SUM(sum_refund_bal_val) rfund_all
FROM
(
SELECT
ATTRIBUTE3 EHECS_CATG
,ATTRIBUTE4 EMP_CATG
--,SUM(fnd_number.canonical_to_number(ATTRIBUTE5)) sum_nmw_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE12)) sum_nmw_count_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE6)) sum_regwg_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE7)) sum_ovrt_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE8)) sum_irrb_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE9)) sum_app_wg_bal_val_tot
,SUM(fnd_number.canonical_to_number(ATTRIBUTE10)) sum_chrs_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE11)) sum_othr_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE13)) sum_mat_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE14)) sum_sic_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE15)) sum_otl_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE16)) sum_lap_bal_val_tot
,SUM(fnd_number.canonical_to_number(ATTRIBUTE17)) sum_pen_bal_val_tot
,SUM(fnd_number.canonical_to_number(ATTRIBUTE18)) sum_prsi_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE19)) sum_incct_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE20)) sum_red_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE21)) sum_otsoc_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE22)) sum_ssec_bal_val_tot
,SUM(fnd_number.canonical_to_number(ATTRIBUTE23)) sum_bik_veh_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE24)) sum_stks_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE25)) sum_vhi_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE26)) sum_hse_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE27)) sum_otben_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE28)) sum_tr_sub_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE29)) sum_ot_sub_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE30)) sum_refund_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE31)) sum_rbs_er_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE32)) sum_prsa_er_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE33)) sum_rac_er_bal_val
,SUM(fnd_number.canonical_to_number(ATTRIBUTE34)) sum_al_bal_val -- 6856473
FROM PAY_US_RPT_TOTALS
WHERE ATTRIBUTE1 = to_char(c_pact_id)
AND ATTRIBUTE3 IS NOT NULL
AND ATTRIBUTE4 IS NOT NULL
GROUP BY ATTRIBUTE3, ATTRIBUTE4
)
GROUP BY EHECS_CATG;
SELECT
EHECS_CATG,
SUM(decode(EMP_CATG,'P',l_nmw,'F',l_nmw)) l_sum_nmw
,SUM(decode(EMP_CATG,'F',l_regwg)) l_sum_regwg_ft
,SUM(decode(EMP_CATG,'F',l_ovrt)) l_sum_ovrt_ft
,SUM(decode(EMP_CATG,'F',l_irrb)) l_sum_irrb_ft
,SUM(decode(EMP_CATG,'P',l_regwg)) l_sum_regwg_pt
,SUM(decode(EMP_CATG,'P',l_ovrt)) l_sum_ovrt_pt
,SUM(decode(EMP_CATG,'P',l_irrb)) l_sum_irrb_pt
,SUM(decode(EMP_CATG,'AT',l_regwg)) l_sum_regwg_at
,SUM(decode(EMP_CATG,'AT',l_ovrt)) l_sum_ovrt_at
,SUM(decode(EMP_CATG,'AT',l_irrb)) l_sum_irrb_at
,SUM(decode(EMP_CATG,'F',l_chrs)) l_sum_chrs_ft
,SUM(decode(EMP_CATG,'P',l_chrs)) l_sum_chrs_pt
,SUM(decode(EMP_CATG,'AT',l_chrs)) l_sum_chrs_at
,SUM(decode(EMP_CATG,'F',l_othr)) l_sum_othr_ft
,SUM(decode(EMP_CATG,'P',l_othr)) l_sum_othr_pt
,SUM(decode(EMP_CATG,'AT',l_othr)) l_sum_othr_at
,SUM(decode(EMP_CATG,'F',l_al)) l_sum_al_ft
,SUM(decode(EMP_CATG,'F',l_mat)) l_sum_mat_ft
,SUM(decode(EMP_CATG,'F',l_sic)) l_sum_sic_ft
,SUM(decode(EMP_CATG,'F',l_otl)) l_sum_otl_ft
,SUM(decode(EMP_CATG,'P',l_al)) l_sum_al_pt
,SUM(decode(EMP_CATG,'P',l_mat)) l_sum_mat_pt
,SUM(decode(EMP_CATG,'P',l_sic)) l_sum_sic_pt
,SUM(decode(EMP_CATG,'P',l_otl)) l_sum_otl_pt
,SUM(decode(EMP_CATG,'AT',l_al)) l_sum_al_at
,SUM(decode(EMP_CATG,'AT',l_mat)) l_sum_mat_at
,SUM(decode(EMP_CATG,'AT',l_sic)) l_sum_sic_at
,SUM(decode(EMP_CATG,'AT',l_otl)) l_sum_otl_at
,SUM(decode(EMP_CATG,'P',l_incc,'F',l_incc)) l_sum_incc_pt_ft
,SUM(decode(EMP_CATG,'P',l_red,'F',l_red)) l_sum_red_pt_ft
,SUM(decode(EMP_CATG,'P',l_otsoc,'F',l_otsoc)) l_sum_otsoc_pt_ft
,SUM(decode(EMP_CATG,'AT',l_incc)) l_sum_incc_at
,SUM(decode(EMP_CATG,'AT',l_red)) l_sum_red_at
,SUM(decode(EMP_CATG,'AT',l_otsoc)) l_sum_otsoc_at
,SUM(decode(EMP_CATG,'P',l_stks,'F',l_stks)) l_sum_stks_pt_ft
,SUM(decode(EMP_CATG,'P',l_vhi,'F',l_vhi)) l_sum_vhi_pt_ft
,SUM(decode(EMP_CATG,'P',l_hse,'F',l_hse)) l_sum_hse_pt_ft
,SUM(decode(EMP_CATG,'P',l_otben,'F',l_otben)) l_sum_otben_ft
FROM
(
select
--decode(hoi.org_information1,'MPAP','M','CSSW','C','PTCO','P') EHECS_CATG
decode(hoi.org_information1,'Managers','M','Clerical Workers','C','Production Workers','P') EHECS_CATG
,decode(hoi.org_information2,'FR','F','PR','P',hoi.org_information2) EMP_CATG
,SUM(fnd_number.canonical_to_number(hoi.org_information3)) l_nmw
,SUM(fnd_number.canonical_to_number(hoi.org_information4)) l_regwg
,SUM(fnd_number.canonical_to_number(hoi.org_information5)) l_ovrt
,SUM(fnd_number.canonical_to_number(hoi.org_information6)) l_irrb
,SUM(fnd_number.canonical_to_number(hoi.org_information12)) l_chrs
,SUM(fnd_number.canonical_to_number(hoi.org_information7)) l_othr
,SUM(fnd_number.canonical_to_number(hoi.org_information8)) l_al
,SUM(fnd_number.canonical_to_number(hoi.org_information9)) l_mat
,SUM(fnd_number.canonical_to_number(hoi.org_information10)) l_sic
,SUM(fnd_number.canonical_to_number(hoi.org_information11)) l_otl
--,SUM(fnd_number.canonical_to_number(hoi.org_information13)) l_prsi
,SUM(fnd_number.canonical_to_number(hoi.org_information14)) l_incc
,SUM(fnd_number.canonical_to_number(hoi.org_information15)) l_red
,SUM(fnd_number.canonical_to_number(hoi.org_information16)) l_otsoc
,SUM(fnd_number.canonical_to_number(hoi.org_information17)) l_stks
,SUM(fnd_number.canonical_to_number(hoi.org_information18)) l_vhi
,SUM(fnd_number.canonical_to_number(hoi.org_information19)) l_hse
,SUM(fnd_number.canonical_to_number(hoi.org_information20)) l_otben
from hr_organization_units hou
,hr_organization_information hoi
where hoi.org_information_context='IE_EHECS_OVERRIDE'
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 IS NOT NULL
and hoi.org_information2 IS NOT NULL
--and decode(hoi.org_information1,'MPAP','M','CSSW','C','PTCO','P') = NVL(g_occupational_category_M_C_P,decode(hoi.org_information1,'MPAP','M','CSSW','C','PTCO','P'))
and decode(hoi.org_information1,'Managers','M','Clerical Workers','C','Production Workers','P') = NVL(g_occupational_category_M_C_P,decode(hoi.org_information1,'Managers','M','Clerical Workers','C','Production Workers','P'))
and fnd_date.canonical_to_date(hoi.org_information13) between g_qtr_start_date and g_qtr_end_date
--group by decode(hoi.org_information1,'MPAP','M','CSSW','C','PTCO','P'),
group by decode(hoi.org_information1,'Managers','M','Clerical Workers','C','Production Workers','P'),
decode(hoi.org_information2,'FR','F','PR','P',hoi.org_information2)
)
GROUP BY EHECS_CATG;
SELECT
NVL(action_information1,0) l_fst_ft_mg,
NVL(action_information2,0) l_fst_ft_cl,
NVL(action_information3,0) l_fst_ft_ot,
NVL(action_information4,0) l_lst_ft_mg,
NVL(action_information5,0) l_lst_ft_cl,
NVL(action_information6,0) l_lst_ft_ot,
NVL(action_information7,0) l_hire_ft_mg,
NVL(action_information8,0) l_hire_ft_cl,
NVL(action_information9,0) l_hire_ft_ot,
NVL(action_information10,0) l_fst_pt_mg,
NVL(action_information11,0) l_fst_pt_cl,
NVL(action_information12,0) l_fst_pt_ot,
NVL(action_information13,0) l_lst_pt_mg,
NVL(action_information14,0) l_lst_pt_cl,
NVL(action_information15,0) l_lst_pt_ot,
NVL(action_information16,0) l_hire_pt_mg,
NVL(action_information17,0) l_hire_pt_cl,
NVL(action_information18,0) l_hire_pt_ot,
NVL(action_information19,0) l_app_mg,
NVL(action_information20,0) l_app_cl,
NVL(action_information21,0) l_app_ot,
NVL(action_information22,0) l_not_payroll_mg,
NVL(action_information23,0) l_not_payroll_cl,
NVL(action_information24,0) l_not_payroll_ot,
NVL(action_information25,0) l_vac_mg,
NVL(action_information26,0) l_vac_cl,
NVL(action_information27,0) l_vac_ot,
NVL(action_information28,0) l_min_paid_mg,
NVL(action_information29,0) l_min_paid_cl,
NVL(action_information30,0) l_min_paid_ot
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_PART1';
SELECT
ROUND(NVL(action_information1,0)) l_reg_wg_ft_mg,
ROUND(NVL(action_information2,0)) l_reg_wg_ft_cl,
ROUND(NVL(action_information3,0)) l_reg_wg_ft_ot,
ROUND(NVL(action_information4,0)) l_ot_paid_ft_mg,
ROUND(NVL(action_information5,0)) l_ot_paid_ft_cl,
ROUND(NVL(action_information6,0)) l_ot_paid_ft_ot,
ROUND(NVL(action_information7,0)) l_irr_bonus_ft_mg,
ROUND(NVL(action_information8,0)) l_irr_bonus_ft_cl,
ROUND(NVL(action_information9,0)) l_irr_bonus_ft_ot,
ROUND(NVL(action_information10,0)) l_reg_wg_pt_mg,
ROUND(NVL(action_information11,0)) l_reg_wg_pt_cl,
ROUND(NVL(action_information12,0)) l_reg_wg_pt_ot,
ROUND(NVL(action_information13,0)) l_ot_paid_pt_mg,
ROUND(NVL(action_information14,0)) l_ot_paid_pt_cl,
ROUND(NVL(action_information15,0)) l_ot_paid_pt_ot,
ROUND(NVL(action_information16,0)) l_irr_bonus_pt_mg,
ROUND(NVL(action_information17,0)) l_irr_bonus_pt_cl,
ROUND(NVL(action_information18,0)) l_irr_bonus_pt_ot,
ROUND(NVL(action_information19,0)) l_tot_wg_app_mg,
ROUND(NVL(action_information20,0)) l_tot_wg_app_cl,
ROUND(NVL(action_information21,0)) l_tot_wg_app_ot
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_PART2';
SELECT
ROUND(NVL(action_information1,0)) l_contracted_hrs_paid_ft_mg,
ROUND(NVL(action_information2,0)) l_contracted_hrs_paid_ft_cl,
ROUND(NVL(action_information3,0)) l_contracted_hrs_paid_ft_ot,
ROUND(NVL(action_information4,0)) l_ot_hrs_paid_ft_mg,
ROUND(NVL(action_information5,0)) l_ot_hrs_paid_ft_cl,
ROUND(NVL(action_information6,0)) l_ot_hrs_paid_ft_ot,
ROUND(NVL(action_information7,0)) l_contracted_hrs_paid_pt_mg,
ROUND(NVL(action_information8,0)) l_contracted_hrs_paid_pt_cl,
ROUND(NVL(action_information9,0)) l_contracted_hrs_paid_pt_ot,
ROUND(NVL(action_information10,0)) l_ot_hrs_paid_pt_mg,
ROUND(NVL(action_information11,0)) l_ot_hrs_paid_pt_cl,
ROUND(NVL(action_information12,0)) l_ot_hrs_paid_pt_ot,
ROUND(NVL(action_information13,0)) l_contracted_hrs_paid_app_mg,
ROUND(NVL(action_information14,0)) l_contracted_hrs_paid_app_cl,
ROUND(NVL(action_information15,0)) l_contracted_hrs_paid_app_ot,
ROUND(NVL(action_information16,0)) l_ot_hrs_paid_app_mg,
ROUND(NVL(action_information17,0)) l_ot_hrs_paid_app_cl,
ROUND(NVL(action_information18,0)) l_ot_hrs_paid_app_ot
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_PART3';
SELECT
ROUND(NVL(action_information1,0)) l_ann_leave_ft_mg,
ROUND(NVL(action_information2,0))l_ann_leave_ft_cl,
ROUND(NVL(action_information3,0)) l_ann_leave_ft_ot,
ROUND(NVL(action_information4,0)) l_mat_leave_ft_mg,
ROUND(NVL(action_information5,0)) l_mat_leave_ft_cl,
ROUND(NVL(action_information6,0)) l_mat_leave_ft_ot,
ROUND(NVL(action_information7,0)) l_sck_leave_ft_mg,
ROUND(NVL(action_information8,0)) l_sck_leave_ft_cl,
ROUND(NVL(action_information9,0)) l_sck_leave_ft_ot,
ROUND(NVL(action_information10,0)) l_other_leave_ft_mg,
ROUND(NVL(action_information11,0)) l_other_leave_ft_cl,
ROUND(NVL(action_information12,0)) l_other_leave_ft_ot,
ROUND(NVL(action_information13,0)) l_ann_leave_pt_mg,
ROUND(NVL(action_information14,0)) l_ann_leave_pt_cl,
ROUND(NVL(action_information15,0)) l_ann_leave_pt_ot,
ROUND(NVL(action_information16,0)) l_mat_leave_pt_mg,
ROUND(NVL(action_information17,0)) l_mat_leave_pt_cl,
ROUND(NVL(action_information18,0)) l_mat_leave_pt_ot,
ROUND(NVL(action_information19,0)) l_sck_leave_pt_mg,
ROUND(NVL(action_information20,0)) l_sck_leave_pt_cl,
ROUND(NVL(action_information21,0)) l_sck_leave_pt_ot,
ROUND(NVL(action_information22,0)) l_other_leave_pt_mg,
ROUND(NVL(action_information23,0)) l_other_leave_pt_cl,
ROUND(NVL(action_information24,0)) l_other_leave_pt_ot,
ROUND(NVL(action_information25,0)) l_all_paid_leave_app_mg,
ROUND(NVL(action_information26,0)) l_all_paid_leave_app_cl,
ROUND(NVL(action_information27,0)) l_all_paid_leave_app_ot
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_PART4';
SELECT
ROUND(NVL(action_information1,0)) l_employer_prsi_mg,
ROUND(NVL(action_information2,0)) l_employer_prsi_cl,
ROUND(NVL(action_information3,0)) l_employer_prsi_ot,
ROUND(NVL(action_information4,0)) l_continuance_income_mg,
ROUND(NVL(action_information5,0)) l_continuance_income_cl,
ROUND(NVL(action_information6,0)) l_continuance_income_ot,
ROUND(NVL(action_information7,0)) l_redundacny_paid_mg,
ROUND(NVL(action_information8,0)) l_redundacny_paid_cl,
ROUND(NVL(action_information9,0)) l_redundacny_paid_ot,
ROUND(NVL(action_information10,0)) l_other_paid_mg,
ROUND(NVL(action_information11,0)) l_other_paid_cl,
ROUND(NVL(action_information12,0)) l_other_paid_ot,
ROUND(NVL(action_information13,0)) l_ssc_contributions_app_mg,
ROUND(NVL(action_information14,0)) l_ssc_contributions_app_cl,
ROUND(NVL(action_information15,0)) l_ssc_contributions_app_ot
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_PART7';
SELECT
ROUND(NVL(action_information1,0)) l_company_car_mg,
ROUND(NVL(action_information2,0)) l_company_car_cl,
ROUND(NVL(action_information3,0)) l_company_car_ot,
ROUND(NVL(action_information4,0)) l_stock_options_mg,
ROUND(NVL(action_information5,0)) l_stock_options_cl,
ROUND(NVL(action_information6,0)) l_stock_options_ot,
ROUND(NVL(action_information7,0)) l_vol_sick_insurance_mg,
ROUND(NVL(action_information8,0)) l_vol_sick_insurance_cl,
ROUND(NVL(action_information9,0)) l_vol_sick_insurance_ot,
ROUND(NVL(action_information10,0)) l_staff_housing_mg,
ROUND(NVL(action_information11,0)) l_staff_housing_cl,
ROUND(NVL(action_information12,0)) l_staff_housing_ot,
ROUND(NVL(action_information13,0)) l_other_benefits_mg,
ROUND(NVL(action_information14,0)) l_other_benefits_cl,
ROUND(NVL(action_information15,0)) l_other_benefits_ot
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_PART8';
SELECT
ROUND(NVL(action_information1,0)) l_employer_pension_mg,
ROUND(NVL(action_information2,0)) l_employer_pension_cl,
ROUND(NVL(action_information3,0)) l_employer_pension_ot,
ROUND(NVL(action_information4,0)) l_employer_liability_premium,
ROUND(NVL(action_information5,0)) l_employer_training_costs,
ROUND(NVL(action_information6,0)) l_other_expenditure,
ROUND(NVL(action_information7,0)) l_training_subsudies,
ROUND(NVL(action_information8,0)) l_other_subsidies,
ROUND(NVL(action_information9,0)) l_refunds,
action_information10 l_comment_line1,
action_information11 l_comment_line2,
action_information12 l_comment_line3
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE_EHECS_ALL_OTHER';
select hoi.org_information1 Year,
hoi.org_information2 Qtr,
hoi.org_information3 CBR,
hoi.org_information4 avg_mgr_not_pyrl,
hoi.org_information5 avg_clk_not_pyrl,
hoi.org_information6 avg_prod_not_pyrl,
hoi.org_information7 Job_Vac_Mgr,
hoi.org_information8 Job_Vac_clk,
hoi.org_information9 Job_Vac_prod,
hoi.org_information10 Tot_empr_Lblt_Ins,
hoi.org_information11 Trng_cost,
hoi.org_information12 Lbr_Expdtr,
hoi.org_information13 Declarant,
hoi.org_information14 Trng_subsidy,
hoi.org_information15 otr_subsidy,
hoi.org_information16 refunds
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 COUNT(1) tot, EMP_CATG, EHECS_CATG
FROM
(
SELECT
--decode(count(1),0,0,1) cnt,
distinct
--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_qtr_start_date),'F'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'P'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'AT'
) EMP_CATG,
/*
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),g_qtr_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),g_qtr_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),g_qtr_start_date)
)
) EHECS_CATG, */
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date)
)
) EHECS_CATG,
paaf.assignment_id
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
where
paaf.business_group_id = g_business_group_id
and paaf.payroll_id is not null
and paaf.payroll_id = nvl(g_payroll_id,paaf.payroll_id)
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment4 = g_employer_id
and paaf.employee_category = nvl(g_occupational_category,paaf.employee_category)
/*
and ( select min(ptp.pay_advice_date)
from per_time_periods ptp
where ptp.payroll_id = paaf.payroll_id
and ptp.pay_advice_date >= g_qtr_start_date
)
*/
--and g_qtr_start_date between paaf.effective_start_date and paaf.effective_end_date
--and paaf.effective_start_date between g_qtr_start_date and g_qtr_end_date
and paaf.effective_start_date <= g_qtr_end_date --g_qtr_start_date --bug 7294966
--
AND ((g_assignment_set_id is not null
AND (g_exc_inc ='I' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id)
OR g_exc_inc = 'E' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id
)))
OR g_assignment_set_id IS NULL)
--
and exists (SELECT paa.assignment_action_id child_assignment_action_id,
prt.run_method run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND g_qtr_start_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(max(paa.assignment_action_id)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
-- AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND paa.source_action_id is not null -- 9915286 commented above line and made check only for source action id for null
AND ppa.effective_date = (select min(regular_payment_date) --min(pay_advice_date) --bug 7294966
from per_time_periods
where payroll_id = paaf.payroll_id
--and pay_advice_date >= g_qtr_start_date
and regular_payment_date >= g_qtr_start_date --bug 7294966
)
-- AND ppa.action_type in ('R', 'Q', 'I', 'V','B')
AND ppa.action_type in ('R', 'Q') -- 9915286 have to check for only payroll run and Quickpay
AND paa.action_status = 'C'
AND ppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date)) --Bug 7294966 QA
/*group by
decode(paaf.EMPLOYMENT_CATEGORY,'FT','F','FR','F','PR','P','PT','P',paaf.EMPLOYMENT_CATEGORY),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date)
)), paaf.person_id */
)
GROUP BY EMP_CATG, EHECS_CATG;
SELECT COUNT(1) tot, EMP_CATG, EHECS_CATG
FROM
(
SELECT
--decode(count(1),0,0,1) cnt,
distinct
--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_qtr_start_date),'F'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'P'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'AT'
) EMP_CATG,
/*
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),g_qtr_end_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),g_qtr_end_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),g_qtr_end_date)
)
) EHECS_CATG, */
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_qtr_end_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_end_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_end_date)
)
) EHECS_CATG,
paaf.assignment_id
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
where
paaf.business_group_id = g_business_group_id
and paaf.payroll_id is not null
and paaf.payroll_id = nvl(g_payroll_id,paaf.payroll_id)
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment4 = g_employer_id
and paaf.employee_category = nvl(g_occupational_category,paaf.employee_category)
/*
and ( select max(ptp.pay_advice_date)
from per_time_periods ptp
where ptp.payroll_id = paaf.payroll_id
and ptp.pay_advice_date <= g_qtr_end_date
)
*/
--and g_qtr_start_date between paaf.effective_start_date and paaf.effective_end_date
--and paaf.effective_start_date between g_qtr_start_date and g_qtr_end_date
and paaf.effective_start_date <= g_qtr_end_date
--
AND ((g_assignment_set_id is not null
AND (g_exc_inc ='I' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id)
OR g_exc_inc = 'E' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id
)))
OR g_assignment_set_id IS NULL)
--
and exists (SELECT paa.assignment_action_id child_assignment_action_id,
prt.run_method run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND g_qtr_start_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(max(paa.assignment_action_id)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
-- AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND paa.source_action_id is not null -- 9915286
AND ppa.effective_date = (select max(regular_payment_date) --max(pay_advice_date) --bug 7294966
from per_time_periods
where payroll_id = paaf.payroll_id
--and pay_advice_date <= g_qtr_end_date
and regular_payment_date <= g_qtr_end_date --bug 7294966
)
-- AND ppa.action_type in ('R', 'Q', 'I', 'V','B')
AND ppa.action_type in ('R', 'Q') -- 9915286
AND paa.action_status = 'C'
AND ppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date)) --Bug 7294966 QA
/*
group by
decode(paaf.EMPLOYMENT_CATEGORY,'FT','F','FR','F','PR','P','PT','P',paaf.EMPLOYMENT_CATEGORY),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date)
)), paaf.person_id
*/
)
GROUP BY EMP_CATG, EHECS_CATG;
SELECT COUNT(1) tot, EMP_CATG, EHECS_CATG
FROM
(
SELECT
--decode(count(1),0,0,1) cnt,
distinct
--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_qtr_start_date),'F'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'P'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'AT'
) EMP_CATG,
/*
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date)
)
) EHECS_CATG, */
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_qtr_end_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_end_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_end_date)
)
) EHECS_CATG,
paaf.assignment_id,
pps.period_of_service_id
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl,
per_periods_of_service pps
where
paaf.business_group_id = g_business_group_id
and paaf.payroll_id is not null
and paaf.payroll_id = nvl(g_payroll_id,paaf.payroll_id)
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment4 = g_employer_id
and paaf.employee_category = nvl(g_occupational_category,paaf.employee_category)
and pps.person_id = paaf.person_id
and pps.business_group_id = paaf.business_group_id
and pps.period_of_service_id = paaf.period_of_service_id
and pps.date_start between g_qtr_start_date And g_qtr_end_date
and paaf.effective_start_date between pps.date_start and g_qtr_end_date
--
AND ((g_assignment_set_id is not null
AND (g_exc_inc ='I' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id)
OR g_exc_inc = 'E' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id
)))
OR g_assignment_set_id IS NULL)
--
--and g_qtr_start_date between paaf.effective_start_date and paaf.effective_end_date
/*
and ( select min(ptp.pay_advice_date)
from per_time_periods ptp
where ptp.payroll_id = paaf.payroll_id
and ptp.pay_advice_date >= g_qtr_start_date
)
*/
/*
and g_qtr_start_date between paaf.effective_start_date and paaf.effective_end_date
and exists (SELECT paa.assignment_action_id child_assignment_action_id,
prt.run_method run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND g_qtr_start_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa)
fnd_number.canonical_to_number(max(paa.assignment_action_id)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V'))
AND ppa.effective_date between (select min(pay_advice_date)
from per_time_periods
where payroll_id = paaf.payroll_id
and pay_advice_date >= g_qtr_start_date
)
AND g_qtr_end_date
AND ppa.action_type in ('R', 'Q', 'I', 'V')
AND paa.action_status = 'C'))
group by
decode(paaf.employment_category,'FT','F','FR','F','PR','P','PT','P',paaf.employment_category),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',paaf.EMPLOYEE_CATEGORY,paaf.effective_start_date)
)), paaf.person_id, pps.period_of_service_id
*/
)
GROUP BY EMP_CATG, EHECS_CATG;
select distinct papf.payroll_id
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
where
paaf.business_group_id = g_business_group_id
and paaf.payroll_id is not null
and paaf.payroll_id = nvl(g_payroll_id,paaf.payroll_id)
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment4 = g_employer_id
and paaf.employee_category = nvl(g_occupational_category,paaf.employee_category)
--and paaf.employment_category = 'AT' --Apprentice
/* 6856486 */
and paaf.employment_category=hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date)
and paaf.effective_start_date <= g_qtr_end_date
and paaf.effective_end_date >= g_qtr_end_date; --g_qtr_start_date; /** 10203348 **/
select pay_advice_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and ptp.pay_advice_date between g_qtr_start_date and g_qtr_end_date;
SELECT COUNT(1) tot, EMP_CATG, EHECS_CATG
FROM
(
SELECT
distinct
--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_qtr_start_date),'F'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'P'
,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date),'AT'
) EMP_CATG,
/*
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','MPAP',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','CSSW',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','PTCO',substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)),paaf.effective_start_date)
)
) EHECS_CATG, */
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date),
NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date),
hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_qtr_start_date)
)
) EHECS_CATG,
paaf.assignment_id
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
where
paaf.business_group_id = g_business_group_id
and paaf.payroll_id is not null
and paaf.payroll_id = p_payroll_id
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment4 = g_employer_id
and paaf.employee_category = nvl(g_occupational_category,paaf.employee_category)
--and paaf.employment_category = 'AT'
/* 6856486 */
and paaf.employment_category=hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_qtr_start_date)
and paaf.effective_start_date <= g_qtr_end_date
and paaf.effective_end_date >= g_qtr_start_date
--
AND ((g_assignment_set_id is not null
AND (g_exc_inc ='I' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id)
OR g_exc_inc = 'E' 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 = g_business_group_id
AND has.assignment_set_id = g_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id
)))
OR g_assignment_set_id IS NULL)
--
and exists (SELECT paa.assignment_action_id child_assignment_action_id,
prt.run_method run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND g_qtr_start_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(max(paa.assignment_action_id)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date = p_period_date
AND ppa.action_type in ('R', 'Q', 'I', 'V','B')
AND paa.action_status = 'C'))
)
GROUP BY EMP_CATG, EHECS_CATG;
l_tab_get_totals.delete;
l_tab_get_override_totals.delete;
hr_utility.set_location('Before Inserting IE_EHECS_PART1 ',3190);
hr_utility.set_location('After Inserting IE_EHECS_PART1 ',3200);
hr_utility.set_location('Before Inserting IE_EHECS_PART2 ',3210);
hr_utility.set_location('After Inserting IE_EHECS_PART2 ',3220);
hr_utility.set_location('After Inserting IE_EHECS_PART3 ',3230);
hr_utility.set_location('Before Inserting IE_EHECS_PART4 ',3240);
hr_utility.set_location('After Inserting IE_EHECS_PART4 ',3250);
hr_utility.set_location('Before Inserting IE_EHECS_PART7 ',3260);
hr_utility.set_location('After Inserting IE_EHECS_PART7 ',3270);
hr_utility.set_location('After Inserting IE_EHECS_PART8 ',3270);
hr_utility.set_location('After Inserting IE_EHECS_ALL_OTHER ',3280);