[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_number.canonical_to_number(SCL.segment1) tax_unit_id
from per_assignments_f ASG
,hr_soft_coding_keyflex SCL
where ASG.assignment_id = p_assignment_id
and SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
and p_effective_date between ASG.effective_start_date
and ASG.effective_end_date;
select nvl(trunc(p_upload_date -
to_char(fnd_date.canonical_to_date(HOI.org_information11),'DDD') +1,'Y')
- 1 + to_char(fnd_date.canonical_to_date(HOI.org_information11),'DDD'),
END_OF_TIME)
from per_assignments_f ASS
,hr_organization_information HOI
where ASS.assignment_id = p_assignment_id
and p_upload_date between ASS.effective_start_date
and ASS.effective_end_date
and HOI.organization_id = ASS.business_group_id
and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
select nvl(add_months(trunc(add_months(p_upload_date, -
to_char(fnd_date.canonical_to_date(HOI.org_information11),'MM') + 1) -
to_char(fnd_date.canonical_to_date(HOI.org_information11),'DD') + 1, 'Q'),
to_char(fnd_date.canonical_to_date(HOI.org_informatioN11),'MM') - 1) +
to_char(fnd_date.canonical_to_date(HOI.org_information11),'DD') - 1,
END_OF_TIME)
from per_assignments_f ASS
,hr_organization_information HOI
where ASS.assignment_id = p_assignment_id
and p_upload_date between ASS.effective_start_date
and ASS.effective_end_date
and HOI.organization_id = ASS.business_group_id
and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
select nvl(PTP.start_date, END_OF_TIME)
from per_time_periods PTP
,per_assignments_f ASS
where ASS.assignment_id = p_assignment_id
and p_upload_date between ASS.effective_start_date
and ASS.effective_end_date
and PTP.payroll_id = ASS.payroll_id
and p_upload_date between PTP.start_date
and PTP.end_date;
select nvl(greatest(p_expiry_date, min(ASS.effective_start_date),
min(PTP.start_date)),
END_OF_TIME)
from per_assignments_f ASS
,hr_soft_coding_Keyflex SCL
,per_time_periods PTP
where ASS.assignment_id = p_assignment_id
and ASS.effective_start_date <= p_upload_date
and ASS.effective_end_date >= p_expiry_date
and SCL.soft_coding_keyflex_id = ASS.soft_coding_keyflex_id
and SCL.segment1 = to_char(p_tax_unit_id)
and PTP.payroll_id = ASS.payroll_id
and PTP.start_date <= p_upload_date
and ASS.effective_end_date >= ptp.start_date;
select nvl(greatest (min(ASG.effective_start_date), min(PTP.start_date)),
END_OF_TIME)
from per_assignments_f ASG
,per_time_periods PTP
where ASG.assignment_id = p_assignment_id
and ASG.effective_start_date <= p_upload_date
and PTP.payroll_id = ASG.payroll_id
and PTP.start_date <= p_upload_date
and ASG.effective_end_date >= ptp.start_date;
select nvl(greatest(min(ASS.effective_start_date), min(PTP.start_date)),
END_OF_TIME)
from per_assignments_f ASS
,hr_soft_coding_keyflex SCL
,per_time_periods PTP
where ASS.assignment_id = p_assignment_id
and SCL.soft_coding_keyflex_id = ASS.soft_coding_keyflex_id
and ASS.effective_start_date <= p_upload_date
and SCL.segment1 = to_char(p_tax_unit_id)
and PTP.payroll_id = ASS.payroll_id
and PTP.start_date <= p_upload_date
and ASS.effective_end_date >= ptp.start_date;
select nvl(greatest(min(ASS.effective_start_date),
min(PTP.start_date), p_expiry_date),
END_OF_TIME)
from per_assignments_f ASS
,per_time_periods PTP
where ASS.assignment_id = p_assignment_id
and ASS.effective_start_date <= p_upload_date
and ASS.effective_end_date >= p_expiry_date
and PTP.payroll_id = ASS.payroll_id
and PTP.start_date <= p_upload_date
and ASS.effective_end_date >= ptp.start_date;
select distinct
paf.business_group_id
from per_assignments_f paf
where paf.assignment_id = p_assignment_id;
select ptp.start_date
from per_time_periods ptp
where ptp.time_definition_id = p_time_definition_id
and p_upload_date between ptp.start_date
and ptp.end_date
and ptp.time_definition_id is not null
and ptp.payroll_id is null;
select BT.balance_type_id
from pay_balance_types BT
where BT.balance_type_id = p_balance_type_id
--
-- JURISDICTION_CODE context NB. if the jurisdiction code is
-- used then only those adjustments which are for the same
-- jurisdiction code can be included.
--
and ((p_jurisdiction_code is null) or
(p_jurisdiction_code is not null and
substr(p_bal_adj_jurisdiction_code, 1, BT.jurisdiction_level) =
substr(p_jurisdiction_code , 1, BT.jurisdiction_level)))
--
-- TAX_UNIT_ID context NB. if the tax unit is used then only those
-- adjustments which are for the same tax unit can be included.
--
and nvl(p_tax_unit_id, nvl(p_bal_adj_tax_unit_id, -1)) =
nvl(p_bal_adj_tax_unit_id, -1)
--
-- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
-- date of the dimension in the same way as the QTD dimension. Any
-- existing balance adjustments that lie between the upload date
-- and the expiry date are all included. There is no special
-- criteria that has to be met.
--
and 1 = 1;