[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT effective_date,
start_date,
business_group_id,
TO_NUMBER(SUBSTR(legislative_parameters,
INSTR(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=')
+ LENGTH('TRANSFER_CONSOLIDATION_SET_ID='))),
TO_NUMBER(LTRIM(RTRIM(SUBSTR(legislative_parameters,
INSTR(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ LENGTH('TRANSFER_PAYROLL_ID='),
(INSTR(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
- (INSTR(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ LENGTH('TRANSFER_PAYROLL_ID='))))))
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT paa.assignment_action_id
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_pymt_assignment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ((NVL(paa.run_type_id, ppa.run_type_id) IS NULL AND
source_action_id IS NULL) OR
(NVL(paa.run_type_id, ppa.run_type_id) IS NOT NULL AND
source_action_id IS NOT NULL AND
paa.run_type_id NOT IN (gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)));
/* Insert this into the plsql table if Current or YTD
amount is not Zero */
pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
:= lv_action_category;
SELECT DISTINCT
NVL(pbtl.reporting_name, pbtl.balance_name),
pbad.attribute_name,
DECODE(pbad.attribute_name,
'Employee Taxes', prb.jurisdiction_code),
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_run_balances prb,
pay_action_interlocks pai,
pay_assignment_actions paa_pre,
pay_payroll_actions ppa_pre,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl
WHERE ppa_pre.action_type IN ('U', 'P')
AND ppa_pre.effective_date > cp_last_xfr_eff_date
AND ppa_pre.effective_date <= cp_last_pymt_eff_date
AND paa_pre.payroll_action_id = ppa_pre.payroll_action_id
AND paa_pre.assignment_id = cp_assignment_id
AND pai.locking_action_id = paa_pre.assignment_action_id
AND prb.assignment_action_id = pai.locked_action_id
AND pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- 'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND prb.defined_balance_id = pdb.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id IS NOT NULL
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbtl.language = USERENV('LANG')
ORDER BY 1;
SELECT DISTINCT
NVL(pbtl.reporting_name, pbtl.balance_name),
pbad.attribute_name,
DECODE(pbad.attribute_name,
'Employee Taxes', prr.jurisdiction_code),
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_run_results prr,
pay_input_values_f piv,
pay_action_interlocks pai,
pay_assignment_actions paa_pre,
pay_payroll_actions ppa_pre,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl
WHERE ppa_pre.action_type IN ('U', 'P')
AND ppa_pre.effective_date > cp_last_xfr_eff_date
AND ppa_pre.effective_date <= cp_last_pymt_eff_date
AND paa_pre.payroll_action_id = ppa_pre.payroll_action_id
AND paa_pre.assignment_id = cp_assignment_id
AND pai.locking_action_id = paa_pre.assignment_action_id
AND prr.assignment_action_id = pai.locked_action_id
AND pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- 'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id = piv.input_value_id
AND piv.element_type_id = prr.element_type_id
AND ppa_pre.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbtl.language = USERENV('LANG')
ORDER BY 1;
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT assignment_id, action_information_category,
jurisdiction_code,
action_information6 primary_balance_id,
action_information9 ytd_amount,
action_information10 reporting_name,
effective_date effective_date,
action_information12 ytd_hours,
action_information15 ytd_days,
action_information16 attribute_name
FROM pay_action_information
WHERE action_information_category IN ('AC EARNINGS', 'AC DEDUCTIONS')
AND action_context_id = cp_xfr_action_id;
SELECT balance_type_id
FROM pay_balance_types
WHERE legislation_code = cp_legislation_code
AND balance_name = cp_balance_name;
SELECT pai.action_context_id
FROM per_assignments_f paf2,
per_assignments_f paf,
pay_action_information pai
WHERE paf.assignment_id = p_assignment_id
AND paf.effective_end_date >= trunc(p_pymt_eff_date, 'Y')
AND paf.effective_start_date <= p_pymt_eff_date
AND paf.person_id = paf2.person_id
AND paf2.effective_end_date >= trunc(p_pymt_eff_date, 'Y')
AND paf2.effective_start_date <= p_pymt_eff_date
AND paf2.assignment_id = pai.assignment_id
AND pai.effective_date >= trunc(p_pymt_eff_date, 'Y')
ORDER BY pai.action_context_id DESC;
SELECT DISTINCT
pbad.attribute_name,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_input_values_f piv,
pay_element_types_f pet
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.balance_type_id = cp_primary_balance_id
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND pbt_pri.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND cp_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND cp_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
ORDER BY 1;
SELECT DISTINCT
NVL(pbtl.reporting_name, pbtl.balance_name),
pbad.attribute_name,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_run_balances prb,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND prb.defined_balance_id = pdb.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id IS NOT NULL
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND pbtl.language = USERENV('LANG')
AND prb.assignment_id = cp_assignment_id
AND paa.assignment_id = prb.assignment_id
AND cp_sepchk_flag = 'Y'
AND prb.assignment_action_id = cp_pymt_action_id
AND prb.assignment_action_id = paa.assignment_action_id
AND NVL(paa.run_type_id, gn_sepchk_run_type_id) IN
(gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY 1;
SELECT DISTINCT
NVL(pbtl.reporting_name, pbtl.balance_name),
pbad.attribute_name,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_run_results prr,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id = piv.input_value_id
AND piv.element_type_id = prr.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND pbtl.language = USERENV('LANG')
AND paa.assignment_id = cp_assignment_id
AND cp_sepchk_flag = 'Y'
AND prr.assignment_action_id = cp_pymt_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND NVL(paa.run_type_id, gn_sepchk_run_type_id) IN
(gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY 1;
SELECT DISTINCT
NVL(pbtl.reporting_name, pbtl.balance_name),
pbad.attribute_name,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_run_balances prb,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND prb.defined_balance_id = pdb.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id IS NOT NULL
AND pbt_pri.balance_type_id = pbtl.balance_type_id
AND pbtl.language = USERENV('LANG')
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND prb.assignment_id = cp_assignment_id
AND paa.assignment_id = prb.assignment_id
AND cp_sepchk_flag = 'N'
AND pai.locking_action_id = cp_pymt_action_id
AND prb.assignment_action_id = pai.locked_action_id
AND prb.assignment_action_id = paa.assignment_action_id
AND paa.action_sequence <= cp_ytd_act_sequence
AND paa.action_sequence = prb.action_sequence
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY 1;
SELECT DISTINCT
NVL(pbtl.reporting_name, pbtl.balance_name),
pbad.attribute_name,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_run_results prr,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
-- AND pbt_pri.input_value_id IS NOT NULL
AND pbt_pri.input_value_id = piv.input_value_id
AND piv.element_type_id = prr.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pbt_pri.balance_type_id = pbtl.balance_type_id
AND pbtl.language = USERENV('LANG')
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND paa.assignment_id = cp_assignment_id
AND cp_sepchk_flag = 'N'
AND pai.locking_action_id = cp_pymt_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.action_sequence <= cp_ytd_act_sequence
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY 1;
SELECT paa.action_sequence
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_asg_act_id;
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT DISTINCT
pbad.attribute_name,
NVL(pbtl.reporting_name, pbtl.balance_name),
prb.jurisdiction_code,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_run_balances prb
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id IS NOT NULL
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbtl.language = USERENV('LANG')
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND prb.effective_date >= trunc(cp_curr_eff_date,'Y')
AND prb.effective_date <= cp_curr_eff_date
AND prb.assignment_id = cp_assignment_id
AND pdb.defined_balance_id = prb.defined_balance_id
ORDER BY 1;
SELECT DISTINCT
pbad.attribute_name,
NVL(pbtl.reporting_name, pbtl.balance_name),
prr.jurisdiction_code,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_input_values_f piv
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id = piv.input_value_id
AND piv.element_type_id = prr.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbtl.language = USERENV('LANG')
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = cp_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('Q','R','B')
AND ppa.effective_date >= TRUNC(cp_curr_eff_date,'Y')
AND ppa.effective_date <= cp_curr_eff_date
ORDER BY 1;
Purpose : This returns the select statement that is
used to created the range rows for the Payslip
Archiver.
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 (''U'',''P'',''B'',''V'')
AND DECODE(ppa.action_type,
''B'', NVL(ppa.future_process_mode, ''Y''),
''N'') = ''N''
AND ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
AND ppa.payroll_id = ''' || ln_payroll_id || '''
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = ''C''
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 NOT EXISTS
(SELECT ''x''
FROM pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai.locked_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type =''X''
AND ppa1.report_type = ''MX_PAYSLIP_ARCHIVE'')
AND :payroll_action_id > 0 -- Bug 4202702
ORDER BY paf.person_id';
SELECT paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
WHERE paf.person_id BETWEEN cp_start_person_id
AND cp_end_person_id
AND paa.assignment_id = paf.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.consolidation_set_id
= NVL(cp_cons_set_id,ppa.consolidation_set_id)
AND paa.action_status = 'C'
AND ppa.payroll_id = cp_payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND ppa.action_type IN ('U','P','B','V')
AND DECODE(ppa.action_type,
'B', NVL(ppa.future_process_mode, 'Y'),
'N') = 'N'
AND paa.source_action_id IS NULL
AND NOT EXISTS
(SELECT 'x'
FROM pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai1.locked_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai1.locking_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type ='X'
AND ppa1.report_type = 'MX_PAYSLIP_ARCHIVE')
ORDER BY 1,2,3,5,6;
SELECT paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
pay_population_ranges ppr
WHERE ppr.payroll_action_id = cp_payroll_action_id
AND ppr.chunk_number = cp_chunk_number
AND paf.person_id = ppr.person_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paa.assignment_id = paf.assignment_id
AND ppa.consolidation_set_id
= NVL(cp_cons_set_id,ppa.consolidation_set_id)
AND paa.action_status = 'C'
AND ppa.payroll_id = cp_payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND ppa.action_type IN ('U','P','B','V')
AND DECODE(ppa.action_type,
'B', NVL(ppa.future_process_mode, 'Y'),
'N') = 'N'
AND paa.source_action_id IS NULL
AND NOT EXISTS
(SELECT 'x'
FROM pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai1.locked_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai1.locking_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type ='X'
AND ppa1.report_type = 'MX_PAYSLIP_ARCHIVE')
ORDER BY 1,2,3,5,6;
SELECT MAX(paa.assignment_action_id)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.locking_action_Id = cp_prepayment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.source_action_id IS NULL
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q');
SELECT pay_assignment_actions_s.NEXTVAL
INTO ln_xfr_action_id
FROM dual;
UPdate pay_assignment_actions
SET serial_number = lv_serial_number
WHERE assignment_action_id = ln_xfr_action_id;
SELECT NVL(pbtl.reporting_name, pbtl.balance_name) reporting_name,
pbtl.balance_type_id,
pbad.attribute_name
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types_tl pbtl
WHERE pbad.attribute_name IN ('Employee Taxes',
'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbtl.balance_type_id = pdb.balance_type_id
AND pbtl.language = USERENV('LANG')
UNION
SELECT balance_name reporting_name,
balance_type_id,
'SUMMARY'
FROM pay_balance_types
WHERE balance_name IN ('Gross Earnings',
'Tax Deductions',
'Deductions',
'Net Pay')
AND legislation_code = 'MX';
SELECT prt.run_type_id,
prt.shortname
FROM pay_run_types_f prt
WHERE prt.run_method = 'S'
AND prt.legislation_code = 'MX';
SELECT balance_name,
balance_type_id
FROM pay_balance_types
WHERE legislation_code = 'MX'
AND balance_name = 'Gross Earnings';
dbt.delete;
/*Insert this into the plsql table */
-- hr_utility.trace('Tax Balance Name : '|| dbt(i).bal_name );
Name : update_employee_information
Purpose : This function updates the Employee Information, which is
archived by the global archive procedure.
The employee name and Legal Employer ID are updated. The
Organization ID segment will be updated to hold the
organization_id of the Legal Employer. The Global package
archives the full name for the employee. This procedure
will update the name to
Paternal Last Name[space]
Maternal Last Name[space]
First Name[space]
Second Name
Arguments : IN
p_assignment_action_id NUMBER;
PROCEDURE update_employee_information(
p_action_context_id IN NUMBER
,p_assignment_id IN NUMBER)
IS
CURSOR c_get_archive_info(cp_action_context_id IN NUMBER
,cp_assignment_id IN NUMBER) IS
SELECT action_information_id, effective_date,
object_version_number,
tax_unit_id
FROM pay_action_information
WHERE action_context_id = cp_action_context_id
AND action_context_type = 'AAP'
AND assignment_id = cp_assignment_id
AND action_information_category = 'EMPLOYEE DETAILS';
SELECT LTRIM(RTRIM(
DECODE(last_name, NULL, '', ' ' || last_name)
|| DECODE(per_information1, NULL,'',' ' || per_information1)
|| DECODE(first_name,NULL, '', ' ' || first_name)
|| DECODE(middle_names,NULL, '', ' ' || middle_names)
))
FROM per_people_f ppf
WHERE ppf.person_id =
(SELECT person_id FROM per_assignments_f paf
WHERE assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date)
AND cp_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
lv_procedure_name := '.update_employee_information';
pay_action_information_api.update_action_information
(p_action_information_id => ln_action_information_id
,p_object_version_number => ln_ovn
,p_action_information1 => lv_employee_name
,p_action_information2 => ln_legal_employer_id
);
END update_employee_information;
SELECT ppf.per_information2 rfc_id,
ppf.per_information3 ss_id
FROM per_assignments_f paf,
per_people_f ppf
WHERE paf.person_id = ppf.person_id
AND paf.assignment_id = cp_assignment_id
AND cp_curr_eff_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND cp_curr_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT org_information1 "Employer Name",
org_information2 "Employer RFC ID"
FROM hr_organization_information
WHERE organization_id = cp_legal_er_id
AND org_information_context = 'MX_TAX_REGISTRATION';
SELECT org_information1 "Employer Social Security ID"
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
AND org_information_context = 'MX_SOC_SEC_DETAILS';
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_assactid
,p_action_context_type => 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_curr_pymt_eff_date
,p_tab_rec_data => pay_emp_action_arch.lrr_act_tab
);
Taxes and Deductions; and inserts two rows for
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_prepayment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND NVL(paa.run_type_id,0) NOT IN (gn_sepchk_run_type_id,
gn_np_sepchk_run_type_id)
ORDER BY paa.assignment_action_id DESC;
SELECT ptp.time_period_id,
ppa.date_earned,
ppa.effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_assignment_action
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT SUBSTR(serial_number,1,1)
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_last_xfr_act_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
update_employee_information(
p_action_context_id => p_xfr_action_id
,p_assignment_id => p_assignment_id);
SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_N7)*/
DISTINCT paf.organization_id,
paf.business_group_id
FROM per_all_assignments_f paf
WHERE paf.payroll_id = cp_payroll_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT DISTINCT hr_mx_utility.get_legal_employer(paf.business_group_id,
paa.tax_unit_id)
FROM per_all_assignments_f paf,
pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.payroll_id = p_payroll_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT DISTINCT paa.tax_unit_id
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id;
SELECT nvl(hoi_LE.org_information1, -- Bug 4155512
hr_general.decode_organization(p_legal_er_id)) "Employer Name",
hoi_LE.org_information2 "Employer RFC ID",
hoi_GRE.org_information1 "Employer Social Security ID"
FROM hr_organization_information hoi_LE,
hr_organization_information hoi_GRE
WHERE hoi_LE.organization_id = p_legal_er_id
AND hoi_LE.org_information_context = 'MX_TAX_REGISTRATION'
AND hoi_GRE.organization_id(+) = hoi_LE.organization_id
AND hoi_GRE.org_information_context(+) = 'MX_SOC_SEC_DETAILS';
SELECT org_information1 "Employer Social Security ID"
FROM hr_organization_information hoi
WHERE organization_id = p_gre_id
AND org_information_context = 'MX_SOC_SEC_DETAILS'
AND organization_id NOT IN
(SELECT organization_id
FROM hr_organization_information
WHERE org_information_context
= 'MX_TAX_REGISTRATION'
);
SELECT hri.org_information1,
hri.org_information2, hri.org_information3,
hri.org_information4, hri.org_information5,
hri.org_information6, hri.org_information7
FROM hr_organization_information hri
WHERE hri.organization_id = cp_organization_id
AND hri.org_information_context = cp_org_information_context
AND hri.org_information1 = 'MESG';
SELECT address_line_1, address_line_2,
address_line_3, town_or_city,
region_1, region_2,
region_3, postal_code,
country, telephone_number_1
FROM hr_locations hl,
hr_organization_units hou
WHERE hou.organization_id = cp_organization_id
AND hou.location_id = hl.location_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_payroll_action_id
,p_action_context_type => 'PA'
,p_assignment_id => NULL
,p_tax_unit_id => NULL
,p_curr_pymt_eff_date => p_effective_date
,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
);
processed in the given assignment and inserts YTD
balance to pl/sql table.
Arguments : p_assignment_id => Terminated Assignment Id
p_assignment_action_id => Max assignment action id
of given assignment
p_curr_eff_date => Current effective date
p_xfr_action_id => Current XFR action id.
p_legislation_code => 'MX'
p_tax_unit_id => GRE of the assignment
p_action_type => Action type of the payment
action
p_start_date => Start Date of the XFR action.
p_end_date => End Date of the XFR action.
Notes : This process is used to retrieve elements processed
in terminated assignments which are not picked up by
the archiver.
******************************************************************/
PROCEDURE process_additional_elements(p_assignment_id IN NUMBER
,p_assignment_action_id IN NUMBER
,p_curr_eff_date IN DATE
,p_xfr_action_id IN NUMBER
,p_legislation_code IN VARCHAR2
,p_tax_unit_id IN NUMBER
,p_action_type IN VARCHAR2
,p_start_date IN DATE
,p_end_date IN DATE )
IS
lv_procedure_name VARCHAR2(50);
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT DISTINCT
pbad.attribute_name,
NVL(pbtl.reporting_name, pbtl.balance_name),
prb.jurisdiction_code,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_run_balances prb
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id IS NOT NULL
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbtl.language = USERENV('LANG')
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND prb.effective_date >= TRUNC(cp_curr_eff_date,'Y')
AND prb.effective_date <= cp_curr_eff_date
AND prb.assignment_id = cp_assignment_id
AND pdb.defined_balance_id = prb.defined_balance_id
ORDER BY 1;
SELECT DISTINCT
pbad.attribute_name,
NVL(pbtl.reporting_name, pbtl.balance_name),
prr.jurisdiction_code,
pbt_pri.balance_type_id, -- Primary Balance
DECODE(pbad.attribute_name,
'Hourly Earnings', pbt_sec.balance_type_id,
NULL), -- Hours Balance
DECODE(pbad.attribute_name,
'Employee Earnings', pbt_sec.balance_type_id,
NULL) -- Days Balance
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_types pbt_pri,
pay_balance_types pbt_sec,
pay_balance_types_tl pbtl,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_input_values_f piv
WHERE pbad.attribute_name IN ('Employee Earnings',
'Hourly Earnings',
'Deductions',
'Taxable Benefits'
-- ,'Employee Taxes',
-- 'Tax Calculation Details'
)
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt_pri.balance_type_id = pdb.balance_type_id
AND pbt_pri.input_value_id = piv.input_value_id
AND piv.element_type_id = prr.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pbtl.balance_type_id = pbt_pri.balance_type_id
AND pbtl.language = USERENV('LANG')
AND pbt_pri.balance_type_id = pbt_sec.base_balance_type_id(+)
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = cp_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('Q','R','B')
AND ppa.effective_date >= TRUNC(cp_curr_eff_date,'Y')
AND ppa.effective_date <= cp_curr_eff_date
ORDER BY 1;
SELECT paa.payroll_action_id,
paa.assignment_action_id,
paa.assignment_id,
paa.tax_unit_id,
paa.serial_number,
paa.chunk_number
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_assignment_action;
SELECT org_information9
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = cp_business_group;
SELECT DISTINCT paa.assignment_id
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_prepayment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q', 'B')
AND ((ppa.run_type_id IS NULL AND
paa.source_action_id IS NULL) OR
(ppa.run_type_id IS NOT NULL AND
paa.source_action_id IS NOT NULL))
AND paa.action_status = 'C';
SELECT paa.assignment_action_id, paa.payroll_action_id,
ppa.action_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.locking_action_id = cp_prepayment_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.source_action_id IS NULL
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY paa.assignment_action_id DESC;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = cp_prepayment_action_id;
SELECT count(*)
FROM pay_action_information
WHERE action_context_id = cp_payroll_action_id
AND action_context_type = 'PA';
SELECT assignment_id
,tax_unit_id
,NVL(source_action_id,-999)
,assignment_action_id
FROM pay_payment_information_v
WHERE assignment_action_id = cp_prepay_action_id
ORDER BY 3,1,2;
SELECT paa.assignment_action_id
,paa.source_action_id
FROM pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.source_action_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM pay_run_types_f prt
WHERE prt.legislation_code = 'MX'
AND prt.run_type_id = paa.run_type_id
AND prt.run_method IN ( 'C', 'S' ) )
ORDER BY paa.action_sequence DESC;
SELECT ppp.assignment_action_id
FROM pay_assignment_actions paa
,pay_pre_payments ppp
WHERE ( paa.assignment_action_id = cp_asg_act_id OR
paa.source_action_id = cp_asg_act_id )
AND ppp.assignment_action_id = paa.assignment_action_id
AND ppp.source_action_id = cp_source_act_id;
SELECT paa.assignment_action_id
,paa.source_action_id
FROM pay_assignment_actions paa
,pay_action_interlocks pai
WHERE pai.locking_action_id = cp_pp_asg_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
ORDER BY paa.action_sequence DESC;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.locking_action_id = cp_pp_asg_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
AND NVL(paa.run_type_id,0) NOT IN (gn_sepchk_run_type_id,
gn_np_sepchk_run_type_id)
AND NOT EXISTS ( SELECT 1
FROM pay_run_types_f prt
WHERE prt.legislation_code = 'US'
AND prt.run_type_id = NVL(paa.run_type_id,0)
AND prt.run_method = 'C' );
SELECT locked_action_id
FROM pay_action_interlocks
WHERE locking_action_id = cp_xfr_action_id;
SELECT pay_assignment_actions_s.nextval
INTO ln_child_xfr_action_id
FROM dual;
update pay_assignment_actions
set serial_number = lv_serial_number
WHERE assignment_action_id = ln_child_xfr_action_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);