[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(ADD_MONTHS(fnd_date.canonical_to_date(HOI.ORG_INFORMATION11)
,12*(floor(months_between(p_upload_date
,fnd_date.canonical_to_date(HOI.ORG_INFORMATION11))/12)))
,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 TRUNC(p_upload_date,'Y')
FROM 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;
SELECT NVL(ADD_MONTHS(fnd_date.canonical_to_date(HOI.ORG_INFORMATION11)
, 3*(FLOOR(MONTHS_BETWEEN(p_upload_date
, fnd_date.canonical_to_date(HOI.ORG_INFORMATION11))/3)))
, 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(MIN(ASG.effective_start_date), END_OF_TIME)
FROM per_assignments_f ASG
WHERE ASG.assignment_id = p_assignment_id
AND ASG.effective_start_date <= p_upload_date;
SELECT NVL(GREATEST(MIN(ASS.effective_start_date), p_expiry_date),END_OF_TIME)
FROM per_assignments_f ASS
WHERE ASS.assignment_id = p_assignment_id
AND ASS.effective_start_date <= p_upload_date
AND ASS.effective_end_date >= p_expiry_date;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = p_dimension_name
AND legislation_code = 'CN'
AND dimension_name NOT IN ( '_ASG_RUN'
, '_ASG_ER_RUN'
, '_ASG_ER_JUR_RUN'
, '_ASG_PMTH'
, '_ASG_P12MTH'
, '_PAYMENTS');
SELECT BT.balance_type_id
FROM pay_balance_types BT
WHERE BT.balance_type_id = p_balance_type_id
--
-- 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;
SELECT pbbl.tax_unit_id
FROM pay_balance_batch_lines pbbl
, hr_all_organization_units_tl otl
, hr_organization_information hoi
WHERE pbbl.batch_line_id = p_batch_line_id
AND pbbl.tax_unit_id = otl.organization_id
AND pbbl.tax_unit_id IS NOT NULL
AND otl.organization_id = hoi.organization_id
AND hoi.org_information1 = 'HR_LEGAL'
AND hoi.org_information2 = 'Y'
AND otl.language = userenv('LANG')
UNION ALL
SELECT pbbl.tax_unit_id
FROM pay_balance_batch_lines pbbl
, hr_all_organization_units_tl otl
, hr_organization_information hoi
WHERE pbbl.batch_line_id = p_batch_line_id
AND upper(pbbl.gre_name) = UPPER(otl.name)
AND pbbl.tax_unit_id IS NULL
AND otl.organization_id = hoi.organization_id
AND hoi.org_information1 = 'HR_LEGAL'
AND hoi.org_information2 = 'Y'
AND otl.language = userenv('LANG');
SELECT tax_unit_id
,original_entry_id
FROM pay_temp_balance_adjustments
WHERE batch_line_id = p_test_batch_line_id;