[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT start_date,
effective_date,
business_group_id,
pay_mx_utility.get_parameter( 'BATCH_NAME',
legislative_parameters) BATCH_NAME,
pay_mx_utility.get_parameter('LEGAL_EMPLOYER',
legislative_parameters) LEGAL_EMPLOYER,
pay_mx_utility.get_parameter('ASG_SET_ID',
legislative_parameters) ASG_SET_ID
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to create the range rows for the Profit Sharing
process.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE range_code(
p_payroll_action_id IN NUMBER
,p_sqlstr OUT NOCOPY VARCHAR2)
IS
ld_end_date DATE;
'SELECT DISTINCT paf.person_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE ppa.business_group_id = ' || ln_business_group_id || '
AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
AND fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
AND :payroll_action_id > 0
ORDER BY paf.person_id';
'SELECT DISTINCT paf.person_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE ppa.business_group_id = ' || ln_business_group_id || '
AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
AND fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
AND (NOT EXISTS
(SELECT ''x''
FROM hr_assignment_set_amendments hasa
WHERE hasa.assignment_set_id = ' || ln_asg_set_id || '
AND hasa.include_or_exclude = ''I'')
OR EXISTS
(SELECT ''x''
FROM hr_assignment_sets has,
hr_assignment_set_amendments hasa,
per_assignments_f paf_all
WHERE has.assignment_set_id = ' || ln_asg_set_id || '
AND has.assignment_set_id = hasa.assignment_set_id
AND hasa.assignment_id = paf_all.assignment_id
AND paf_all.person_id = paf.person_id
AND hasa.include_or_exclude = ''I'')
)
AND NOT EXISTS
(SELECT ''x''
FROM hr_assignment_sets has,
hr_assignment_set_amendments hasa,
per_assignments_f paf_all
WHERE has.assignment_set_id = ' || ln_asg_set_id || '
AND has.assignment_set_id = hasa.assignment_set_id
AND hasa.assignment_id = paf_all.assignment_id
AND paf_all.person_id = paf.person_id
AND hasa.include_or_exclude = ''E'')
AND :payroll_action_id > 0
ORDER BY paf.person_id';
SELECT 'X'
FROM dual
WHERE (EXISTS(SELECT 'x'
FROM hr_assignment_set_amendments hasa
WHERE hasa.assignment_set_id = cp_asg_set_id
AND hasa.include_or_exclude = 'I')
AND NOT EXISTS
(SELECT 'x'
FROM hr_assignment_sets has,
hr_assignment_set_amendments hasa,
per_assignments_f paf_all
WHERE has.assignment_set_id = cp_asg_set_id
AND has.assignment_set_id = hasa.assignment_set_id
AND hasa.assignment_id = paf_all.assignment_id
AND paf_all.person_id = cp_person_id
AND hasa.include_or_exclude = 'I')
)
OR EXISTS (SELECT 'x'
FROM hr_assignment_sets has,
hr_assignment_set_amendments hasa,
per_assignments_f paf_all
WHERE has.assignment_set_id = cp_asg_set_id
AND has.assignment_set_id = hasa.assignment_set_id
AND hasa.assignment_id = paf_all.assignment_id
AND paf_all.person_id = cp_person_id
AND hasa.include_or_exclude = 'E');
SELECT --DISTINCT
paf_pri.assignment_id,
paf_pri.person_id,
NVL(paf.employment_category, 'MX1_PERM_WRK'),
paa.tax_unit_id
FROM per_assignments_f paf,
per_assignments_f paf_pri,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.business_group_id = cp_bg_id
AND paf.assignment_id = paa.assignment_id
AND pay_mx_yrend_arch.gre_exists(paa.tax_unit_id) = 1
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = p_chunk
AND ppr.person_id = paf.person_id
AND paf.person_id = paf_pri.person_id
AND paf_pri.primary_flag = 'Y'
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('Q','R','B','V','I')
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND (paf.employment_category NOT IN ('MX2_TEMP_WRK',
'MX3_TEMP_CONSTRCT_WRK')
OR
cp_incl_temp_EEs = 'Y')
AND paf_pri.effective_start_date <= cp_end_date
AND paf_pri.effective_end_date >= cp_start_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
ORDER BY paf_pri.person_id,
NVL(paf.employment_category, 'MX1_PERM_WRK'),
paf_pri.effective_end_date DESC;
SELECT --DISTINCT
paf_pri.assignment_id,
paf_pri.person_id,
NVL(paf.employment_category, 'MX1_PERM_WRK'),
paa.tax_unit_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
per_assignments_f paf_pri
WHERE ppa.business_group_id = cp_bg_id
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND ppa.action_type IN ('Q','R','B','V','I')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND paf_pri.person_id = paf.person_id
AND paf_pri.primary_flag = 'Y'
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf_pri.effective_start_date <= cp_end_date
AND paf_pri.effective_end_date >= cp_start_date
AND pay_mx_yrend_arch.gre_exists(paa.tax_unit_id) = 1
AND paf.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND (paf.employment_category NOT IN ('MX2_TEMP_WRK',
'MX3_TEMP_CONSTRCT_WRK')
OR
cp_incl_temp_EEs = 'Y')
ORDER BY paf_pri.person_id,
NVL(paf.employment_category, 'MX1_PERM_WRK'),
paf_pri.effective_end_date DESC;
SELECT 'Y'
FROM pay_batch_headers
WHERE business_group_id = cp_business_group_id
AND UPPER(cp_batch_name) = UPPER(batch_name);
SELECT 'X'
FROM dual
WHERE NOT EXISTS(SELECT 'Y'
FROM per_assignments_f
WHERE assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN effective_start_date
AND effective_end_date);
SELECT employee_number
FROM per_people_f
WHERE person_id = cp_person_id
ORDER BY effective_end_date DESC;
SELECT pay_assignment_actions_s.NEXTVAL
INTO ln_PTU_action_id
FROM dual;
SELECT DISTINCT paf.person_id
FROM pay_temp_object_actions ptoa,
per_assignments_f paf,
pay_payroll_actions ppa
WHERE ptoa.payroll_action_id = p_payroll_action_id
AND paf.assignment_id = ptoa.object_id
AND ptoa.object_type = 'ASG'
AND ppa.payroll_action_id = ptoa.payroll_action_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
ORDER BY paf.person_id;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'Profit Sharing'
AND legislation_code = 'MX';
SELECT NVL (SUM( NVL(pai.action_information2,0) ), 0) Factor_F,
NVL (SUM( NVL(pai.action_information3,0) ), 0) Factor_G
FROM pay_action_information pai
WHERE pai.action_context_id = cp_payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'MX PROFIT SHARING FACTORS';
SELECT batch_id
FROM pay_batch_headers
WHERE business_group_id = cp_business_group_id
AND UPPER(cp_batch_name) = UPPER(batch_name);
SELECT paf.assignment_id,
paf.assignment_number,
paf.person_id,
paf.business_group_id,
ptoa.payroll_action_id
FROM pay_temp_object_actions ptoa,
per_assignments_f paf,
pay_payroll_actions ppa
WHERE ptoa.object_action_id = p_archive_action_id
AND paf.assignment_id = ptoa.object_id
AND ptoa.object_type = 'ASG'
AND ppa.payroll_action_id = ptoa.payroll_action_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = 'Y';
Purpose : This procedure deletes the temporary records
created in PAY_ACTION_INFORMATION for the Profit
Sharing process in Mexico.
Arguments : p_payroll_action_id IN NUMBER
Notes :
************************************************************/
PROCEDURE deinit_code(p_payroll_action_id IN NUMBER)
IS
--
lv_procedure_name VARCHAR2(100);
DELETE
FROM pay_action_information
WHERE action_information_category = 'MX PROFIT SHARING FACTORS'
AND action_context_id = p_payroll_action_id
AND action_context_type = 'PA';