[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Inserts final calculated values into temp table*/
hr_utility.trace('Entering load_data ...' ||SQLERRM);
INSERT INTO pay_us_rpt_totals
(business_group_id ,
tax_unit_id ,
organization_id ,
value1 ,
value2 ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14
)
VALUES
(p_business_group_id ,
l_payroll_action_id ,
p_ppa_finder ,
p_cur_balance ,
p_prev_balance ,
p_balance_type_id ,
p_report_id ,
p_group_name ,
t_consolidation_set_id ,
p_payroll_id ,
p_assignment ,
l_last_name ,
l_first_name ,
l_national_id ,
l_middle_name ,
p_effective_date ,
p_ppa_finder ,
l_assignment_number ,
l_full_name
);
SELECT legislation_code
INTO l_legislation_code_l
FROM per_business_groups
WHERE business_group_id =p_business_group_id;
SELECT INSTR(DATABASE_ITEM_SUFFIX,'_GRE_'),
INSTR(DATABASE_ITEM_SUFFIX,'_LE_') ,
INSTR(DATABASE_ITEM_SUFFIX,'_COMP_'),
INSTR(DATABASE_ITEM_SUFFIX,'_TU_')
FROM pay_balance_dimensions where balance_dimension_id =
(SELECT balance_dimension_id
FROM pay_defined_balances
WHERE defined_balance_id =p_defined_balance_id
);
SELECT hou.name
FROM hr_organization_units hou
WHERE organization_id=(SELECT segment1
FROM hr_soft_coding_keyflex
WHERE soft_coding_keyflex_id =
(SELECT soft_coding_keyflex_id
FROM per_all_assignments_f
WHERE assignment_id=p_assignment_id
AND p_paydate BETWEEN effective_start_date
AND effective_end_date));
SELECT r.row_low_range_or_name
FROM pay_user_rows_f r
WHERE r.user_row_id = g_component_code;
SELECT hsck.segment1 INTO l_tax_unit
FROM hr_soft_coding_keyflex hsck, per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
SELECT INSTR(DATABASE_ITEM_SUFFIX,'_GRE_')
FROM pay_balance_dimensions where balance_dimension_id =
(SELECT balance_dimension_id
FROM pay_defined_balances
WHERE defined_balance_id =p_defined_balance_id
);
SELECT r.row_low_range_or_name
FROM pay_user_rows_f r
WHERE r.user_row_id = g_component_code;
SELECT segment1,segment11,segment12
FROM hr_soft_coding_keyflex hsck,
per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND p_paydate BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
SELECT MAX(greatest(ptp.end_date,ptp.regular_payment_date)) pay_date,
MAX (ptp.end_date)
FROM per_time_periods ptp
WHERE ptp.payroll_id=p_payroll_id
AND ptp.end_date <= p_effective_date;
SELECT MAX(greatest(ptp.end_date,ptp.regular_payment_date)) prev_pay_period
FROM per_time_periods ptp
WHERE ptp.payroll_id =p_payroll_id
AND ptp.end_date < maxdate;
SELECT greatest(ptp.end_date,ptp.regular_payment_date) pay_date
FROM per_time_periods ptp
WHERE ptp.payroll_id =p_payroll_id
AND ptp.end_date >= maxdate-no_days
AND ptp.end_date < maxdate
ORDER BY end_date desc;
SELECT greatest(ptp.end_date,ptp.regular_payment_date) pay_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND no_period >=(Select count(*)
FROM per_time_periods ptp1
WHERE ptp1.payroll_id =p_payroll_id
AND ptp1.end_date < maxdate
AND ptp.end_date <=ptp1.end_date)
AND ptp.end_date < maxdate
ORDER BY end_date desc;
SELECT exception_report_id,
balance_type_id,
balance_dimension_id ,
NVL(p_vartype,variance_type),
NVL(p_varvalue,variance_value),
variance_operator,
comparison_type,
comparison_value
FROM pqp_exception_reports
WHERE exception_report_id=p_report_id
AND (business_group_id =p_business_group_id
OR business_group_id IS NULL)
AND (legislation_code=p_legislation_code
OR legislation_code IS NULL);
SELECT per.exception_report_id,
per.balance_type_id,
per.balance_dimension_id ,
per.variance_type,
per.variance_value,
per.variance_operator,
per.comparison_type,
per.comparison_value
FROM pqp_exception_report_groups perg,
pqp_exception_reports per
WHERE exception_group_name=(SELECT exception_group_name from
pqp_exception_report_groups
where exception_group_id=to_number(p_group_id))
AND ( perg.business_group_id =p_business_group_id
OR perg.business_group_id IS NULL)
AND ( per.business_group_id =p_business_group_id
OR per.business_group_id IS NULL)
AND per.exception_report_id=perg.exception_report_id
AND (perg.legislation_code=p_legislation_code
OR perg.legislation_code IS NULL)
AND (per.legislation_code=p_legislation_code
OR per.legislation_code IS NULL);
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id=bal_type_id
AND balance_dimension_id=dim_type_id;
l_date_detail.DELETE;
Select LAST_DAY(ADD_MONTHS(l_maxdate,(12-to_char(l_maxdate,'MM')-12)))
INTO l_temp_date
FROM dual;
SELECT LAST_DAY(ADD_MONTHS(l_maxdate,(DECODE(MOD(to_char(l_maxdate,'MM'),3),0,0,1,2,2,1)+
(j*-3))))
INTO l_temp_date
FROM dual;
/*SELECT LAST_DAY(ADD_MONTHS(l_maxdate,
(DECODE(MOD(to_char(l_maxdate,'MM'),3),0,0,1,2,2,1)+
(j*-3))))
INTO temp_date
FROM dual; */
SELECT MAX(pay_date)maxdate,
MIN(pay_st_date) mindate
FROM (SELECT MAX(ptp.start_date) pay_st_date,MAX(ptp.end_date) pay_date
FROM per_time_periods ptp
WHERE ptp.payroll_id IN (SELECT payroll_id
FROM pay_payroll_actions ppa
WHERE ppa.consolidation_set_id=p_consolidation_set_id
AND (payroll_id =p_payroll_id
OR p_payroll_id IS NULL)
AND ppa.date_earned <= p_effective_date)
AND ptp.end_date <= p_effective_date
GROUP BY ptp.payroll_id);
hr_utility.trace('Enter update payroll action');
UPDATE pay_payroll_actions
SET Start_date= NVL(l_mindate,p_effective_date)
, effective_date=NVL(l_maxdate,p_effective_date)
WHERE payroll_action_id=pactid;
hr_utility.trace('Leaving Update payroll action') ;
SELECT ppv.last_name,
ppv.first_name,
ppv.middle_names,
ppv.full_name,
ppv.date_of_birth,
ppv.national_identifier,
paf.assignment_number
FROM
per_all_people_f ppv,
per_assignments_f paf
WHERE paf.assignment_id=p_assignment
AND paf.person_id=ppv.person_id
AND l_effective_date BETWEEN ppv.effective_start_date
AND ppv.effective_end_date
AND l_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT MAX(ptp.start_date),MAX(ptp.end_date) pay_date
FROM per_time_periods ptp
WHERE ptp.payroll_id=p_payroll_id
AND ptp.end_date <= p_effective_date;
SELECT
distinct paa1.assignment_id assignment_id,
ppa_arch.start_date start_date,
ppa_arch.effective_date end_date,
ppa_arch.business_group_id business_group_id,
ppa_arch.payroll_action_id payroll_action_id,
ppa.effective_date effective_date,
ppa.action_type action_type,
paa1.tax_unit_id tax_unit_id,
paf.payroll_id payroll_id,
paf.organization_id organization_id,
hou1.name organization_name,
paf.location_id location_id,
paa.chunk_number chnkno,
paa.payroll_action_id pactid
FROM
hr_organization_units hou1,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa1,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa_arch
WHERE paa.assignment_action_id = l_pactid
AND paa.payroll_action_id = ppa_arch.payroll_action_id
AND pai.locking_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa1.payroll_action_id
AND paf.assignment_id = paa1.assignment_id
AND ppa.effective_date between paf.effective_start_date
AND paf.effective_end_date
AND hou1.organization_id = paf.organization_id;
SELECT ppa.legislative_parameters,
ppa.business_group_id,
ppa.start_date,
ppa.effective_date,
pqp_exppreproc_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_REPORT',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_GROUP',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_PPA_FINDER',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_DATE',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_VARTYPE',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_VARVALUE',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters) ,
pqp_exppreproc_pkg.get_parameter('TRANSFER_JD',ppa.legislative_parameters) ,
ppa.payroll_action_id,
pqp_exppreproc_pkg.get_parameter('TRANSFER_COMP',ppa.legislative_parameters)
INTO l_leg_param,
l_business_group_id,
l_leg_start_date,
l_leg_end_date,
t_consolidation_set_id,
t_payroll_id,
l_report_id ,
l_group_id,
l_ppa_finder,
l_date,
l_vartype,
l_varvalue,
g_gre_id,
g_jd_code,
t_payroll_action_id,
g_component_code
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT ppf.pay_date_offset pod
FROM pay_payrolls_f ppf
WHERE ppf.payroll_id= p_payroll_id
AND ppf.consolidation_set_id=p_consolidation_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT pers.year_begin_date
FROM pqp_exception_report_suffix pers
WHERE pers.database_item_suffix =(
SELECT database_item_suffix
FROM pay_balance_dimensions
WHERE balance_dimension_id =c_dimension_type_id
AND legislation_code=c_legislation_code)
AND pers.legislation_code=c_legislation_code;
SELECT fnd_date.canonical_to_date(to_char(p_effective_date,'YYYY') ||
substr(fnd_date.date_to_canonical(l_tax_year_start), 6, 5))
INTO l_tax_year_start_dt from dual;
SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,3 )-p_effective_date)
INTO l_sign
FROM dual;
SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,6 )-p_effective_date)
INTO l_sign
FROM dual;
SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,9 )-p_effective_date)
INTO l_sign
FROM dual;
SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,12 )-p_effective_date)
INTO l_sign
FROM dual;