[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ADD_MONTHS(P_DATE_OF_BIRTH,(22*12))
INTO L_DATE
FROM DUAL;
select EFFECTIVE_START_DATE from per_all_assignments_f
where assignment_id = p_assignment_id
and l_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
SELECT DATE_START
FROM PER_PERIODS_OF_SERVICE
WHERE PERSON_ID = P_PERSON_ID
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
SELECT MAX(DECODE(GLOBAL_NAME,'QE_WEEKLY_LT', GLOBAL_VALUE ,NULL)) G_QE_WEEKLY_LT ,
MAX(DECODE(GLOBAL_NAME,'QE_MONTHLY_LT', GLOBAL_VALUE ,NULL)) G_QE_MONTHLY_LT ,
MAX(DECODE(GLOBAL_NAME,'QE_ANNUAL_LT', GLOBAL_VALUE ,NULL)) G_QE_ANNUAL_LT ,
MAX(DECODE(GLOBAL_NAME,'QE_WEEKLY_UT', GLOBAL_VALUE ,NULL)) G_QE_WEEKLY_UT ,
MAX(DECODE(GLOBAL_NAME,'QE_MONTHLY_UT', GLOBAL_VALUE ,NULL)) G_QE_MONTHLY_UT ,
MAX(DECODE(GLOBAL_NAME,'QE_ANNUAL_UT', GLOBAL_VALUE ,NULL)) G_QE_ANNUAL_UT ,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_WEEKLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_WEEKLY ,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_MONTHLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_MONTHLY ,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_ANNUAL', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_ANNUAL,
MAX(DECODE(GLOBAL_NAME,'QE_BIWEEK_LT', GLOBAL_VALUE ,NULL)) G_QE_BIWEEK_LT,
MAX(DECODE(GLOBAL_NAME,'QE_LUNAR_LT', GLOBAL_VALUE ,NULL)) G_QE_LUNAR_LT,
MAX(DECODE(GLOBAL_NAME,'QE_QUARTERLY_LT', GLOBAL_VALUE ,NULL)) G_QE_QUARTERLY_LT,
MAX(DECODE(GLOBAL_NAME,'QE_SEMIYEAR_LT', GLOBAL_VALUE ,NULL)) G_QE_SEMIYEAR_LT,
MAX(DECODE(GLOBAL_NAME,'QE_BIWEEK_UT', GLOBAL_VALUE ,NULL)) G_QE_BIWEEK_UT,
MAX(DECODE(GLOBAL_NAME,'QE_LUNAR_UT', GLOBAL_VALUE ,NULL)) G_QE_LUNAR_UT,
MAX(DECODE(GLOBAL_NAME,'QE_QUARTERLY_UT', GLOBAL_VALUE ,NULL)) G_QE_QUARTERLY_UT,
MAX(DECODE(GLOBAL_NAME,'QE_SEMIYEAR_UT', GLOBAL_VALUE ,NULL)) G_QE_SEMIYEAR_UT,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_BIWEEK', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_BIWEEK,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_LUNAR', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_LUNAR,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_QUARTERLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_QUARTERLY,
MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_SEMIYEAR', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_SEMIYEAR
FROM FF_GLOBALS_F FGF
WHERE GLOBAL_NAME IN ('QE_ANNUAL_LT', 'QE_WEEKLY_LT', 'QE_MONTHLY_LT', 'QE_ANNUAL_UT', 'QE_WEEKLY_UT', 'QE_MONTHLY_UT', 'AUTO_ENROL_TRIG_ANNUAL', 'AUTO_ENROL_TRIG_WEEKLY', 'AUTO_ENROL_TRIG_MONTHLY',
'QE_BIWEEK_LT','QE_LUNAR_LT','QE_QUARTERLY_LT','QE_SEMIYEAR_LT', 'QE_BIWEEK_UT','QE_LUNAR_UT','QE_QUARTERLY_UT','QE_SEMIYEAR_UT', 'AUTO_ENROL_TRIG_BIWEEK','AUTO_ENROL_TRIG_LUNAR','AUTO_ENROL_TRIG_QUARTERLY',
'AUTO_ENROL_TRIG_SEMIYEAR')
AND P_EFFECTIVE_DATE BETWEEN FGF.EFFECTIVE_START_DATE AND FGF.EFFECTIVE_END_DATE;
SELECT MONTHS_BETWEEN(P_REGULAR_PAYMENT_DATE,DATE_OF_BIRTH)/12,
SEX,
DATE_OF_BIRTH
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = V_PERSON_ID
AND P_REGULAR_PAYMENT_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT PAAF.ASSIGNMENT_ID,
PAAF.PAYROLL_ID
FROM PER_ALL_ASSIGNMENTS_F PAAF,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PERSON_ID = V_PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND L_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE;
SELECT PERSON_ID
FROM PER_ALL_ASSIGNMENTS_F
WHERE ASSIGNMENT_ID =P_ASSIGNMENT_ID
AND L_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT PDB.DEFINED_BALANCE_ID
FROM PAY_BALANCE_TYPES PBT,
PAY_DEFINED_BALANCES PDB,
PAY_BALANCE_DIMENSIONS PBD
WHERE PDB.BALANCE_TYPE_ID=PBT.BALANCE_TYPE_ID
AND PBT.BALANCE_NAME LIKE 'Pension Qualifying Earnings'
AND PBD.DATABASE_ITEM_SUFFIX='_ASG_RUN'
AND PBD.BALANCE_DIMENSION_ID=PDB.BALANCE_DIMENSION_ID;
SELECT PAYROLL_ID,
BUSINESS_GROUP_ID
FROM PER_ALL_ASSIGNMENTS_F
WHERE ASSIGNMENT_ID=P_ASSIGNMENT_ID
AND P_REGULAR_PAYMENT_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT START_DATE,
END_DATE
FROM PER_TIME_PERIODS PTP
WHERE P_REGULAR_PAYMENT_DATE BETWEEN START_DATE AND END_DATE
AND PAYROLL_ID=L_PAYROLL_ID;
SELECT period_type
FROM pay_payrolls_f
WHERE payroll_id = V_PAYROLL_ID
AND L_PRP_END_DATE BETWEEN effective_start_date AND effective_end_date ;
SELECT ASSIGNMENT_ACTION_ID,
PPA.EFFECTIVE_DATE DATE_PAID,
ptp.end_date DATE_EARNED,
ptp.TIME_PERIOD_ID,
ptp.PAYROLL_ID
FROM PAY_PAYROLL_ACTIONS PPA,
PAY_ASSIGNMENT_ACTIONS PAA,
PER_TIME_PERIODS PTP
WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
AND REGULAR_PAYMENT_DATE = PPA.EFFECTIVE_DATE
AND ptp.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
-- AND PPA.TIME_PERIOD_ID = NVL(P_TIME_PERIOD,PPA.TIME_PERIOD_ID)
AND ASSIGNMENT_ID =P_ASSIGNMENT_ID
AND PPA.ACTION_TYPE IN ('Q','R')
AND PPA.EFFECTIVE_DATE BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE
-- AND BUSINESS_GROUP_ID =G_BUSINESS_GROUP_ID
AND PAA.ACTION_STATUS ='C'
AND SOURCE_ACTION_ID IS NOT NULL
ORDER BY TIME_PERIOD_ID,
ASSIGNMENT_ACTION_ID DESC;
SELECT MAX(DECODE(pivf.name,'Aggregate Earnings',peevf.screen_entry_value)) Agg_earnings,
MAX(DECODE(pivf.name,'Main Entry for Aggregation',peevf.screen_entry_value)) Main_entry
FROM per_all_assignments_f paaf,
pay_element_types_f petf,
pay_input_values_f pivf,
pay_element_entries_f peef,
pay_element_entry_values_f peevf
WHERE paaf.assignment_id = V_ASSIGNMENT_ID
AND petf.element_name ='Pensions Information'
AND petf.legislation_code = 'GB'
AND pivf.name IN ( 'Aggregate Earnings','Main Entry for Aggregation')
AND pivf.legislation_code = 'GB'
AND pivf.element_type_id = petf.element_type_id
AND peef.element_type_id = petf.element_type_id
AND peef.assignment_id = paaf.assignment_id
AND peef.element_entry_id = peevf.element_entry_id
AND pivf.input_value_id = peevf.input_value_id
AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND L_PRP_END_DATE BETWEEN petf.effective_start_date AND petf.effective_end_date
AND L_PRP_END_DATE BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND L_PRP_END_DATE BETWEEN peef.effective_start_date AND peef.effective_end_date
AND L_PRP_END_DATE BETWEEN peevf.effective_start_date AND peevf.effective_end_date;
SELECT MAX(DECODE(pivf.name,'Auto Enrollment Date',peevf.screen_entry_value)) AED,
MAX(DECODE(pivf.name,'Qualifying scheme exists',peevf.screen_entry_value)) QSE,
MAX(DECODE(pivf.name,'Opt Out Date',peevf.screen_entry_value)) OPT_OUT,
MAX(DECODE(pivf.name,'Postponement End Date',peevf.screen_entry_value)) POSTPONEMENT,
MAX(DECODE(pivf.name,'Eligible Job Holder Date',peevf.screen_entry_value)) EJH
FROM per_all_assignments_f paaf,
pay_element_types_f petf,
pay_input_values_f pivf,
pay_element_entries_f peef,
pay_element_entry_values_f peevf
WHERE paaf.assignment_id = V_ASSIGNMENT_ID
AND petf.element_name ='Pensions Information'
AND petf.legislation_code = 'GB'
AND pivf.name IN ( 'Auto Enrollment Date', 'Opt Out Date','Postponement End Date','Qualifying scheme exists','Eligible Job Holder Date')
AND pivf.legislation_code = 'GB'
AND pivf.element_type_id = petf.element_type_id
AND peef.element_type_id = petf.element_type_id
AND peef.assignment_id = paaf.assignment_id
AND peef.element_entry_id = peevf.element_entry_id
AND pivf.input_value_id = peevf.input_value_id
AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND L_PRP_END_DATE BETWEEN petf.effective_start_date AND petf.effective_end_date
AND L_PRP_END_DATE BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND L_PRP_END_DATE BETWEEN peef.effective_start_date AND peef.effective_end_date
AND L_PRP_END_DATE BETWEEN peevf.effective_start_date AND peevf.effective_end_date;
SELECT (PTP.START_DATE-1)
FROM PER_TIME_PERIODS PTP
WHERE ADD_MONTHS(P_DATE,3) BETWEEN START_DATE AND END_DATE
AND PAYROLL_ID=G_PAYROLL_ID;
SELECT (PTP.START_DATE-1)
FROM PER_TIME_PERIODS PTP
WHERE ADD_MONTHS(P_DATE,3) BETWEEN START_DATE AND END_DATE
AND PAYROLL_ID=G_PAYROLL_ID;
PROCEDURE update_ni_category_pension(
p_assignment_id IN NUMBER,
p_auto_enrol_date IN DATE,
p_mode in VARCHAR2 --GB_VALIDATE_COMMIT
)
IS
/*** Local variables ***/
l_ni_new_category varchar2(1) := ' ';
l_update_flag varchar2(1) := 'N';
cursor csr_element_entries_for_update
is
SELECT peevf.*
FROM pay_element_types_f petf
,pay_input_values_f pivf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
WHERE
petf.element_name = 'NI'
AND petf.legislation_code = 'GB'
AND petf.element_type_id = pivf.element_type_id
AND peef.assignment_id = p_assignment_id
AND peef.element_type_id = petf.element_type_id
AND peef.element_entry_id = peevf.element_entry_id
AND peevf.input_value_id = pivf.input_value_id
AND pivf.name in ('Category','Pension')
AND p_auto_enrol_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND p_auto_enrol_date BETWEEN peevf.effective_start_date
AND peevf.effective_end_date
AND p_auto_enrol_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND p_auto_enrol_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
select name
from pay_input_values_f
where input_value_id = l_input_value_id
AND LEGISLATION_CODE = 'GB';
l_rec csr_element_entries_for_update%rowtype;
hr_utility.trace('Entering ' || '.update_ni_category_pension');
open csr_element_entries_for_update;
fetch csr_element_entries_for_update into l_rec;
exit when csr_element_entries_for_update%notfound;
close csr_element_entries_for_update;
l_update_flag := 'Y';
l_update_flag := 'Y';
l_update_flag := 'Y';
SAVEPOINT update_NI_element;
if p_mode = 'GB_VALIDATE_COMMIT' and l_update_flag = 'Y' THEN
-- Call the update api in UPDATE_OVERRIDE mode for updating the element entries for future entries as well.
hr_entry_api.update_element_entry(p_dt_update_mode => 'UPDATE_OVERRIDE',
p_session_date => p_auto_enrol_date,
p_element_entry_id => l_element_entry_id,
p_input_value_id1 => l_input_value_id_category,
p_input_value_id2 => l_input_value_id_pension,
p_entry_value1 => l_ni_new_category,
p_entry_value2 => pen_basis(l_ni_new_pension)
);
hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id|| ' Updated Successfully');
hr_utility.trace('Leaving ' || '.update_ni_catergory_and_pension');
ROLLBACK to update_NI_element;
END update_ni_category_pension;
SELECT TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PAYROLL_ID')) PAYROLL_ID,
TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PERIOD_ID')) TIME_PERIOD_ID,
PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS,'MODE') L_MODE,
BUSINESS_GROUP_ID
FROM PAY_PAYROLL_ACTIONS
WHERE PAYROLL_ACTION_ID = G_PAYROLL_ACTION_ID ;
SELECT UPPER(SUBSTR(TRIM(HOI.ORG_INFORMATION2),1,35)) PENSION_STAGING_DATE,
NVL(UPPER(SUBSTR(LTRIM(HOI.ORG_INFORMATION3),1,35)),'NONE') WORKER_POSTPONEMENT_RULE,
NVL(UPPER(SUBSTR(LTRIM(HOI.ORG_INFORMATION4),1,35)),'NONE') ELIGIBLE_POSPONEMENT_RULE,
PPF.PERIOD_TYPE
FROM PAY_PAYROLLS_F PPF,
HR_SOFT_CODING_KEYFLEX HSCF,
HR_ORGANIZATION_INFORMATION HOI
WHERE PPF.PAYROLL_ID = P_PAYROLL_ID
AND PPF.BUSINESS_GROUP_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'UK Pensions'
AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
AND PPF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
AND HSCF.SEGMENT1 = HOI.ORG_INFORMATION1
AND G_PRP_END_DATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE;
SELECT PAYROLL_ID,
START_DATE,
END_DATE,
REGULAR_PAYMENT_DATE ,
PERIOD_NAME
FROM PER_TIME_PERIODS PTP
WHERE PTP.TIME_PERIOD_ID = P_TIME_PERIOD_ID;
SELECT START_DATE,
END_DATE,
REGULAR_PAYMENT_DATE
FROM PER_TIME_PERIODS PTP
WHERE G_PAYROLL_DATE_PAID BETWEEN START_DATE AND END_DATE
AND PAYROLL_ID=G_PAYROLL_ID;
SELECT PAYROLL_NAME
FROM PAY_PAYROLLS_F
WHERE PAYROLL_ID = G_PAYROLL_ID
AND G_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT PCV_INFORMATION1
FROM PQP_CONFIGURATION_VALUES
WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PCV_INFORMATION2 = P_SCHEME_ELEMENT_NAME
);
SELECT ORIGINAL_ENTRY_ID
FROM pay_element_entries_f peef
WHERE peef.element_entry_id = P_ELEM_ENTRY_ID
AND g_payroll_end_date BETWEEN peef.effective_start_date AND peef.effective_end_date;
SELECT pelf.element_type_id
FROM pay_element_entries_f peef ,
pay_element_links_f pelf
WHERE pelf.element_link_id = peef.element_link_id
AND peef.element_entry_id = P_ELEM_ENTRY_ID
AND g_payroll_end_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND g_payroll_end_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date;
SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID ,
PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
FROM PAY_PAYROLL_ACTIONS PPA,
PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.ACTION_TYPE IN ('Q','R')
AND PAA.ACTION_STATUS = 'C'
AND PPA.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND EFFECTIVE_DATE = G_PAYROLL_DATE_PAID--Check the date
ORDER BY ASSIGNMENT_ACTION_ID DESC;
SELECT PEEF.SCREEN_ENTRY_VALUE,
PEEF.INPUT_VALUE_ID,
UPPER(REPLACE(PIVF.NAME,' ','_')) NAME
FROM PAY_ELEMENT_ENTRY_VALUES_F PEEF,
PAY_INPUT_VALUES_F PIVF
WHERE PEEF.ELEMENT_ENTRY_ID = P_ELEM_ENTRY_ID
AND PEEF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
AND g_payroll_end_date BETWEEN peef.EFFECTIVE_START_DATE AND peef.EFFECTIVE_END_DATE
AND g_payroll_end_date BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE ;
SELECT RESULT_NAME
FROM PAY_FORMULA_RESULT_RULES_F FRR
WHERE STATUS_PROCESSING_RULE_ID = P_STAT_PROC_RULE_ID
AND RESULT_RULE_TYPE ='D'
AND INPUT_VALUE_ID = P_PAY_VALUE_ID
AND g_payroll_end_date BETWEEN FRR.EFFECTIVE_START_DATE AND FRR.EFFECTIVE_END_DATE ;
L_INPUTS.delete;
P_INPUTS.delete;
L_OUTPUTS.delete;
SELECT PEEF.ELEMENT_ENTRY_ID,
PEEF.ELEMENT_TYPE_ID
FROM PAY_ELEMENT_ENTRIES_F PEEF
WHERE PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID IN
( SELECT DISTINCT ELEMENT_TYPE_ID
FROM pay_element_types_f petf ,
PAY_ELEMENT_CLASSIFICATIONS PEC
WHERE Petf.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PEC.CLASSIFICATION_NAME = 'Information'
AND PETF.ELEMENT_NAME NOT IN ('NI','PAYE')
AND P_EFFECTIVE_DATE BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE
)
AND NVL(p_element_type_id_miss,PEEF.ELEMENT_TYPE_ID) =PEEF.ELEMENT_TYPE_ID
AND P_EFFECTIVE_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE;
SELECT result_name
FROM pay_formula_result_rules_f frr,
pay_input_values_f pivf
WHERE RESULT_RULE_TYPE = 'I'
AND STATUS_PROCESSING_RULE_ID= P_STATUS_PROCESSING_RULE_ID
AND P_EFFECTIVE_DATE BETWEEN frr.EFFECTIVE_START_DATE AND frr.EFFECTIVE_END_DATE
AND P_EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND frr.ELEMENT_TYPE_ID IN
( SELECT DISTINCT ELEMENT_TYPE_ID
FROM pay_sub_classification_rules_f PSCR ,
PAY_ELEMENT_CLASSIFICATIONS PEC
WHERE PSCR.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PEC.CLASSIFICATION_NAME = 'Pension Qualifying Earnings'
AND P_EFFECTIVE_DATE BETWEEN PSCR.EFFECTIVE_START_DATE AND PSCR.EFFECTIVE_END_DATE
)
AND frr.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND pivf.name ='Pay Value';
SELECT ASSIGNMENT_ACTION_ID,
PPA.EFFECTIVE_DATE DATE_PAID,
ptp.end_date DATE_EARNED,
ptp.TIME_PERIOD_ID,
ptp.PAYROLL_ID,
PRT.RUN_TYPE_NAME
FROM PAY_PAYROLL_ACTIONS PPA,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_RUN_TYPES_F PRT,
PER_TIME_PERIODS PTP
WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
AND REGULAR_PAYMENT_DATE = PPA.EFFECTIVE_DATE
AND ptp.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND PPA.TIME_PERIOD_ID = NVL(P_TIME_PERIOD,PPA.TIME_PERIOD_ID)
AND ASSIGNMENT_ID =P_ASSIGNMENT_ID
AND PPA.ACTION_TYPE IN ('Q','R')
AND PPA.EFFECTIVE_DATE BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
AND PPA.BUSINESS_GROUP_ID =G_BUSINESS_GROUP_ID
AND PAA.ACTION_STATUS ='C'
AND SOURCE_ACTION_ID IS NOT NULL
AND PRT.LEGISLATION_CODE ='GB'
AND PRT.RUN_TYPE_ID =PAA.RUN_TYPE_ID
ORDER BY TIME_PERIOD_ID,
ASSIGNMENT_ACTION_ID DESC;
SELECT DISTINCT petf.element_type_id
FROM pay_element_types_f petf ,
pay_element_entries_f peef ,
ff_formulas_f fff,
ff_formula_types fft
WHERE petf.element_type_id = peef.element_type_id
AND peef.element_type_id NOT IN
(SELECT DISTINCT prr.ELEMENT_TYPE_ID
FROM pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = P_TIME_PERIOD_ID
AND SOURCE_ACTION_ID IS NOT NULL
AND paa.ASSIGNMENT_ID =P_ASSIGNMENT_ID
AND PPA.BUSINESS_GROUP_ID =G_BUSINESS_GROUP_ID
AND PPA.ACTION_TYPE IN ('Q','R')
AND PAA.ACTION_STATUS ='C'
AND SOURCE_TYPE = 'E'
)
AND NVL(petf.FORMULA_ID,fff.formula_id) = fff.formula_id
AND fff.formula_type_id = fft.formula_type_id
AND FORMULA_TYPE_NAME <>'Element Skip'
AND P_DATE_EARNED BETWEEN fff.effective_start_date AND fff.effective_end_date
AND peef.assignment_id = P_ASSIGNMENT_ID
AND P_DATE_EARNED BETWEEN petf.effective_start_date AND petf.effective_end_date
AND P_DATE_EARNED BETWEEN peef.effective_start_date AND peef.effective_end_date;
SELECT TIME_PERIOD_ID,
START_DATE,
END_DATE,
PAYROLL_ID,
REGULAR_PAYMENT_DATE DATE_PAID
FROM per_time_periods
WHERE payroll_id IN
(SELECT payroll_id
FROM per_all_assignments_f paaf
WHERE assignment_id = p_assignment_id
AND G_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
)
AND regular_payment_date BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
AND regular_payment_date NOT IN
(SELECT effective_date
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa ,
per_time_periods ptp
WHERE ppa.action_type IN ('Q', 'R')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.time_period_id IN
(SELECT time_period_id
FROM per_time_periods
WHERE payroll_id IN
(SELECT payroll_id
FROM per_all_assignments_f paaf
WHERE assignment_id = p_assignment_id
AND G_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
)
AND regular_payment_date BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
)
);
SELECT PAAF.PAYROLL_ID,
PAAF.ASSIGNMENT_ID,
PEEF.ELEMENT_ENTRY_ID
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
AND PAPF.PERSON_ID =P_PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
SELECT months_between (least (effective_end_date, g_prp_end_date), date_of_birth) / 12
, date_of_birth
, sex
FROM per_all_people_f papf
WHERE person_id = p_person_id
AND effective_start_date =
(
SELECT max (effective_start_date)
FROM per_all_people_f
WHERE person_id = papf.person_id
AND effective_start_date <= g_prp_end_date
AND current_employee_flag = 'Y'
)
AND g_prp_end_date >= effective_start_date;
SELECT MONTHS_BETWEEN(G_PRP_END_DATE,DATE_OF_BIRTH)/12,
DATE_OF_BIRTH,
SEX
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = P_PERSON_ID
AND G_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
select NUMBER_PER_FISCAL_YEAR
from per_time_period_types
where PERIOD_TYPE = G_PAYROLL_PERIOD_TYPE;
SELECT DATE_START
FROM PER_PERIODS_OF_SERVICE
WHERE PERSON_ID = P_PERSON_ID
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
select EFFECTIVE_START_DATE from per_all_assignments_f
where assignment_id = p_assignment_id
and g_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
SELECT ppa.payroll_action_id
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE ppa.time_period_id = nvl (g_time_period_id, ppa.time_period_id)
AND paa.assignment_id = p_assignment_id
AND ppa.effective_date
BETWEEN g_prp_start_date
AND g_prp_end_date
AND ppa.action_type IN ('Q', 'R')
AND ppa.business_group_id = g_business_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY time_period_id
, assignment_action_id DESC;
SELECT past.pay_system_status
FROM per_assignment_status_types past
, per_all_assignments_f paaf
WHERE past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.assignment_id = p_assignment_id
AND paaf.person_id = p_person_id
AND g_prp_end_date >= paaf.effective_start_date
AND g_prp_start_date <= paaf.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
hr_locations_all l
WHERE assignment_id=p_assignment_id
AND a.LOCATION_ID = l.LOCATION_ID
AND l.location_code=p_location
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
HR_ALL_ORGANIZATION_UNITS u
WHERE assignment_id =p_assignment_id
AND u.ORGANIZATION_ID = a.ORGANIZATION_ID
AND u.business_group_id=g_business_group_id
AND u.name =p_org_name
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
pay_payrolls_f b
WHERE assignment_id =p_assignment_id
AND b.business_group_id = G_BUSINESS_GROUP_ID
AND b.PAYROLL_ID = a.PAYROLL_ID
AND b.payroll_name =p_payroll_name
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date
AND G_PRP_END_DATE BETWEEN b.effective_start_date AND b.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
per_grades g
WHERE assignment_id =p_assignment_id
AND g.business_group_id = G_BUSINESS_GROUP_ID
AND g.GRADE_ID = a.GRADE_ID
AND g.name =p_grade
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
per_jobs j
WHERE assignment_id =p_assignment_id
AND j.business_group_id = G_BUSINESS_GROUP_ID
AND j.JOB_ID = a.JOB_ID
AND j.name =p_job
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
per_pay_bases p
WHERE assignment_id =p_assignment_id
AND p.business_group_id = G_BUSINESS_GROUP_ID
AND p.PAY_BASIS_ID = a.PAY_BASIS_ID
AND p.name =p_pay_basis
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
SELECT 1
FROM per_all_assignments_f a,
per_all_positions p
WHERE assignment_id =p_assignment_id
AND p.business_group_id = G_BUSINESS_GROUP_ID
AND p.POSITION_ID = a.POSITION_ID
AND p.name =p_position
AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
SELECT 1
FROM hr_lookups
WHERE lookup_type = 'EMP_CAT'
AND meaning = p_emp_category
AND lookup_code IN
(SELECT EMPLOYMENT_CATEGORY
FROM per_all_assignments_f
WHERE assignment_id=p_assignment_id
AND G_PRP_END_DATE BETWEEN effective_start_date AND effective_end_date
);
SELECT p_config_values('Location')
INTO l_value
FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Grade') INTO l_value FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Job') INTO l_value FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Organization') INTO l_value FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Payroll') INTO l_value FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Position') INTO l_value FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Salary Basis') INTO l_value FROM dual ;
l_seg_collect.delete;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT p_config_values('Emp Category') INTO l_value FROM dual ;
SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
SELECT instr(l_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
SELECT trim(SUBSTR(l_value,1,(l_position-1)))
INTO l_seg_collect(temp_num)
FROM dual;
SELECT trim(PCV_INFORMATION1) PCV_INFORMATION1,
trim(PCV_INFORMATION2) PCV_INFORMATION2,
trim(PCV_INFORMATION3) PCV_INFORMATION3,
trim(PCV_INFORMATION4) PCV_INFORMATION4,
trim(PCV_INFORMATION5) PCV_INFORMATION5,
trim(PCV_INFORMATION6) PCV_INFORMATION6,
trim(PCV_INFORMATION7) PCV_INFORMATION7,
trim(PCV_INFORMATION8) PCV_INFORMATION8,
trim(PCV_INFORMATION9) PCV_INFORMATION9,
trim(PCV_INFORMATION10) PCV_INFORMATION10,
trim(PCV_INFORMATION11) PCV_INFORMATION11,
trim(PCV_INFORMATION12) PCV_INFORMATION12,
trim(PCV_INFORMATION13) PCV_INFORMATION13
FROM PQP_CONFIGURATION_VALUES
WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
SELECT 1
FROM PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND p_people_group_id = people_group_id
AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE;
SELECT 1
FROM fnd_id_flex_structures b ,
fnd_id_flex_structures_tl t
WHERE b.id_flex_num = t.id_flex_num
AND b.id_flex_code = 'GRP'
AND t.language = 'US'
AND t.id_flex_structure_name = p_group_name;
SELECT application_column_name,
b.id_flex_num
FROM fnd_id_flex_structures b ,
fnd_id_flex_structures_tl t ,
fnd_id_flex_segments c
WHERE b.id_flex_code = 'GRP'
AND t.language = 'US'
AND t.id_flex_num = b.id_flex_num
AND c.id_flex_num = b.id_flex_num
AND t.id_flex_structure_name = p_group_name
AND c.segment_name = p_seg_name;
SELECT p_config_values('Group') INTO L_group_value FROM dual ;
SELECT instr(L_group_value,'|') INTO l_position FROM dual;
SELECT trim(SUBSTR(L_group_value,1,(l_position-1)))
INTO l_group_name
FROM dual;
SELECT trim(SUBSTR(L_group_value,(l_position+1)))
INTO L_group_value
FROM dual;
SELECT instr(L_group_value,'~') INTO l_position FROM dual;
SELECT trim(SUBSTR(L_group_value,1))
INTO l_seg_collection(temp_num)
FROM dual;
SELECT trim(SUBSTR(L_group_value,1,(l_position-1)))
INTO l_seg_collection(temp_num)
FROM dual;
l_seg_name_value_tab.delete;
SELECT trim(SUBSTR(l_seg_collection(ii),(l_position+1)))
INTO l_seg_collection(ii)
FROM dual;
SELECT instr(l_seg_collection(ii),'|') INTO l_position FROM dual;
SELECT trim(SUBSTR(l_seg_collection(ii),1)) INTO l_temp_char FROM dual;
SELECT trim(SUBSTR(l_temp_char,1,(instr(l_temp_char,'.')-1))),
trim(SUBSTR(l_temp_char,(instr(l_temp_char,'.') +1)))
INTO l_seg_name_value_tab(temp_num_sub).name,
l_seg_name_value_tab(temp_num_sub).value
FROM dual;
SELECT trim(SUBSTR(l_seg_collection(ii),1,(l_position-1)))
INTO l_temp_char
FROM dual;
SELECT trim(SUBSTR(l_temp_char,1,(instr(l_temp_char,'.')-1))),
trim(SUBSTR(l_temp_char,(instr(l_temp_char,'.') +1)))
INTO l_seg_name_value_tab(temp_num_sub).name,
l_seg_name_value_tab(temp_num_sub).value
FROM dual;
stmt := 'select PEOPLE_GROUP_ID from pay_people_groups where (ID_FLEX_NUM ='||l_flex_num||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
l_receive_ppl_group.delete;
PROCEDURE UPDATE_INFO_ELEMENT(
P_ASSIGNMENT_ID NUMBER ,
P_INPUT_VALUE_ID1 NUMBER ,
P_ENTRY_VALUE1 VARCHAR2 ,
P_INPUT_VALUE_ID2 NUMBER ,
P_ENTRY_VALUE2 VARCHAR2 ,
P_INPUT_VALUE_ID3 NUMBER ,
P_ENTRY_VALUE3 VARCHAR2 ,
P_INPUT_VALUE_ID4 NUMBER ,
P_ENTRY_VALUE4 VARCHAR2 ,
P_INPUT_VALUE_ID5 NUMBER ,
P_ENTRY_VALUE5 VARCHAR2 ,
P_INPUT_VALUE_ID6 NUMBER ,
P_ENTRY_VALUE6 VARCHAR2 ,
P_INPUT_VALUE_ID7 NUMBER ,
P_ENTRY_VALUE7 VARCHAR2 ,
P_INPUT_VALUE_ID8 NUMBER ,
P_ENTRY_VALUE8 VARCHAR2 ,
P_INPUT_VALUE_ID9 NUMBER ,
P_ENTRY_VALUE9 VARCHAR2 ,
P_INPUT_VALUE_ID10 NUMBER ,
P_ENTRY_VALUE10 VARCHAR2 ,
P_INPUT_VALUE_ID11 NUMBER ,
P_ENTRY_VALUE11 VARCHAR2 ,
P_INPUT_VALUE_ID12 NUMBER ,
P_ENTRY_VALUE12 VARCHAR2 ,
P_INPUT_VALUE_ID13 NUMBER ,
P_ENTRY_VALUE13 VARCHAR2 )
IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
SELECT ELEMENT_ENTRY_ID,
GREATEST(EFFECTIVE_START_DATE,G_PRP_START_DATE) START_DATE
FROM PAY_ELEMENT_ENTRIES_F
WHERE ASSIGNMENT_ID =P_ASSIGNMENT_ID
AND ELEMENT_TYPE_ID = G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND (G_PRP_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR EFFECTIVE_START_DATE > G_PRP_START_DATE);
hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
SAVEPOINT update_element_transaction;
HR_ENTRY_API.UPDATE_ELEMENT_ENTRY (P_DT_UPDATE_MODE =>'UPDATE', P_SESSION_DATE => entries.START_DATE, P_CHECK_FOR_UPDATE =>'N' , P_CREATOR_TYPE => 'F', P_ELEMENT_ENTRY_ID => entries.ELEMENT_ENTRY_ID ,
P_INPUT_VALUE_ID1 => P_INPUT_VALUE_ID1, P_ENTRY_VALUE1 => P_ENTRY_VALUE1 , P_INPUT_VALUE_ID2 => P_INPUT_VALUE_ID2, P_ENTRY_VALUE2 => L_ENTRY_VALUE2 ,P_INPUT_VALUE_ID3 => P_INPUT_VALUE_ID3,
P_ENTRY_VALUE3 => P_ENTRY_VALUE3 , P_INPUT_VALUE_ID4 => P_INPUT_VALUE_ID4, P_ENTRY_VALUE4 => L_ENTRY_VALUE4, P_INPUT_VALUE_ID5 => P_INPUT_VALUE_ID5, P_ENTRY_VALUE5 => P_ENTRY_VALUE5,
P_INPUT_VALUE_ID6 => P_INPUT_VALUE_ID6, P_ENTRY_VALUE6 => P_ENTRY_VALUE6, P_INPUT_VALUE_ID7 => P_INPUT_VALUE_ID7, P_ENTRY_VALUE7 => P_ENTRY_VALUE7, P_INPUT_VALUE_ID8 => P_INPUT_VALUE_ID8,
P_ENTRY_VALUE8 => P_ENTRY_VALUE8, P_INPUT_VALUE_ID9 => P_INPUT_VALUE_ID9, P_ENTRY_VALUE9 => P_ENTRY_VALUE9, P_INPUT_VALUE_ID10 => P_INPUT_VALUE_ID10, P_ENTRY_VALUE10 => L_ENTRY_VALUE10,
P_INPUT_VALUE_ID11 => P_INPUT_VALUE_ID11, P_ENTRY_VALUE11 => P_ENTRY_VALUE11, P_INPUT_VALUE_ID12 => P_INPUT_VALUE_ID12, P_ENTRY_VALUE12 => P_ENTRY_VALUE12, P_INPUT_VALUE_ID13 => P_INPUT_VALUE_ID13,
P_ENTRY_VALUE13 => P_ENTRY_VALUE13 );
ROLLBACK TO update_element_transaction;
END UPDATE_INFO_ELEMENT;
INSERT
INTO pay_message_lines
(
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text
)
VALUES
(
pay_message_lines_s.nextval ,
123 ,
'F' ,
p_assignment_action_id ,
'A' ,
SUBSTR(p_message_text,1,2000)
);
INSERT
INTO pay_message_lines
(
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text
)
VALUES
(
pay_message_lines_s.nextval ,
111 ,
'W' ,
p_assignment_action_id ,
'A' ,
SUBSTR(p_message_text,1,2000)
);
PROCEDURE INSERT_PENSION_ELEMENT
(
V_EFF_START_DATE IN OUT NOCOPY DATE ,-- Auto Enrolment Date is passed here -- Bug 14343133
V_EFF_END_DATE IN OUT NOCOPY DATE ,
V_ELEMENT_ENTRY_ID IN OUT NOCOPY NUMBER ,
P_ASSIGNMENT_ID NUMBER ,
P_ELEMENT_TYPE_NAME VARCHAR,
P_AGGREGATION_FLAG VARCHAR,
P_PERSON_ID NUMBER,
P_ASSIGNMENT_ACTION_ID NUMBER
)
IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
select EFFECTIVE_START_DATE from per_all_assignments_f
where assignment_id = p_assignment_id
and g_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
SELECT DATE_START
FROM PER_PERIODS_OF_SERVICE
WHERE PERSON_ID = P_PERSON_ID
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
SELECT GREATEST(START_DATE,P_SERVICE_START,P_ASG_START_DATE)
FROM PER_TIME_PERIODS
WHERE REGULAR_PAYMENT_DATE >= V_EFF_START_DATE
AND PAYROLL_ID = P_PAYROLL_ID
ORDER BY REGULAR_PAYMENT_DATE;
SELECT ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F
WHERE ELEMENT_NAME =P_ELEMENT_TYPE_NAME
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND V_EFF_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT PAAF.PAYROLL_ID,PAAF.EFFECTIVE_START_DATE,
PAAF.ASSIGNMENT_ID,
PEEF.ELEMENT_ENTRY_ID,
PAAF.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
AND PAPF.PERSON_ID =L_PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
hr_utility.trace('Insert Pension Element');
RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
SAVEPOINT insert_element_transaction;
HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => P_ASSIGNMENT_ID,
P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
ROLLBACK TO insert_element_transaction;
END IF;--Rollback to the savepoint 'insert_element_transaction' if not in Commit Mode.
hr_utility.trace('Inside Aggregation Insert Pension Element');
RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
SAVEPOINT insert_element_transaction_agg;
HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => I.ASSIGNMENT_ID,
P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
ROLLBACK TO insert_element_transaction_agg;
END IF;--Rollback to the savepoint 'insert_element_transaction_agg' if not in Commit Mode.
hr_utility.trace('Error While Inserting Pension Element');
END INSERT_PENSION_ELEMENT;
SELECT PAAF.PAYROLL_ID,
PAAF.ASSIGNMENT_ID,
PEEF.ELEMENT_ENTRY_ID,
PAAF.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
AND PAPF.PERSON_ID =L_PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
UPDATE_INFO_ELEMENT( I.ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, P_PENSION_INPUT_VALUES_DUMMY.G_MAIN_ENTRY -- Main Entry has to be retained as before
, G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS , G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID ,
/*Bug 14622818*/
to_char (l_eligible_jh_date ,fnd_date.name_in_mask) );
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = G_PAYROLL_ACTION_ID -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
--Bug 14492289 - Reporting EJH in separate Sections
AND NOT EXISTS
(SELECT 1
FROM PAY_MESSAGE_LINES
WHERE PAYROLL_ID = 123
AND MESSAGE_LEVEL ='F'
AND SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
AND SOURCE_TYPE = 'A'
)
--Bug 14492289 - Reporting EJH in separate Sections
ORDER BY assignment_id;
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = G_PAYROLL_ACTION_ID -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND
--Bug 14492289 - Reporting EJH in separate Sections
( paa.action_status = 'E'
OR (paa.action_status = 'C'
AND EXISTS
(SELECT 1
FROM PAY_MESSAGE_LINES
WHERE PAYROLL_ID = 123
AND MESSAGE_LEVEL ='F'
AND SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
AND SOURCE_TYPE = 'A'
) ) )
--Bug 14492289 - Reporting EJH in separate Sections
ORDER BY assignment_id;
SELECT PAI.ACTION_INFORMATION15 assignment_number,
PAI.ACTION_INFORMATION14 full_name,
NVL(PAI.ACTION_INFORMATION7,'NA') classification,
NVL(TO_CHAR(fnd_date.canonical_to_date(PAI.ACTION_INFORMATION9)),'NA') auto_en_date
FROM PAY_ACTION_INFORMATION PAI
WHERE PAI.assignment_id =p_assignment_id
AND PAI.ACTION_CONTEXT_ID =p_asg_actid
AND PAI.ACTION_CONTEXT_TYPE='AAP';
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = p_assignment_action_id
AND payroll_id = 123;
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = p_assignment_action_id
AND payroll_id = 111;
SELECT scl.SEGMENT19
FROM pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
WHERE ppf.payroll_id =G_PAYROLL_ID
AND scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
AND G_PRP_END_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PAYROLL_ID')) PAYROLL_ID,
TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PERIOD_ID')) TIME_PERIOD_ID,
BUSINESS_GROUP_ID
FROM PAY_PAYROLL_ACTIONS
WHERE PAYROLL_ACTION_ID = p_payroll_action_id ;
UPDATE pay_payroll_actions
SET time_period_id = l_act_time_period_id ,
payroll_id = l_act_payroll_id ,
date_earned = G_PAYROLL_END_DATE ,
effective_date = G_PAYROLL_DATE_PAID
WHERE payroll_action_id = p_payroll_action_id
AND BUSINESS_GROUP_ID =l_act_business_id;
sqlstr := 'select distinct ppf.person_id ' || 'from per_people_f ppf, ' || 'pay_payroll_actions ppa, ' || 'per_all_assignments_f paaf, ' || 'per_assignment_status_types past ' ||
'where ppa.payroll_action_id = :PAYROLL_ACTION_ID ' || ' and ppa.business_group_id = ' ||G_BUSINESS_GROUP_ID || ' and paaf.payroll_id = ' ||G_PAYROLL_ID || ' and paaf.person_id = ppf.person_id ' ||
' and paaf.assignment_status_type_id = past.assignment_status_type_id ' || 'and past.PER_SYSTEM_status=''ACTIVE_ASSIGN'' ' || 'and ' ||''''||
G_PRP_END_DATE ||''''||' between paaf.effective_start_date and paaf.effective_end_date ' || 'order by ppf.person_id' ;
sqlstr := 'select distinct paaf.person_id '|| 'from pay_payroll_actions ppa, '|| 'per_all_assignments_f paaf '|| 'where ppa.payroll_action_id = :payroll_action_id '||
'and paaf.business_group_id + 0 = ppa.business_group_id '|| 'and paaf.payroll_id = '||G_PAYROLL_ID|| ' order by paaf.person_id';
sqlstr := 'select distinct person_id '|| 'from per_people_f ppf, '|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '|| 'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
SELECT DISTINCT PAAF.ASSIGNMENT_ID
FROM PER_ALL_ASSIGNMENTS_F PAAF ,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PAYROLL_ID = G_PAYROLL_ID
AND PAAF.BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID
AND PAAF.PERSON_ID BETWEEN stperson AND endperson
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
-- AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE >= PAAF.EFFECTIVE_START_DATE
AND G_PRP_START_DATE <= PAAF.EFFECTIVE_END_DATE
ORDER BY ASSIGNMENT_ID ;
SELECT pay_assignment_actions_s.nextval INTO l_ass_act_id FROM dual;
UPDATE pay_payroll_actions
SET time_period_id = null
, payroll_id = null
, date_earned = null
WHERE payroll_action_id = pactid
and BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID;
SELECT DECODE(G_MODE,'GB_VALIDATE','Validate Only','GB_VALIDATE_COMMIT','Validate and Commit','NA')
INTO l_mode
FROM DUAL;
SELECT DECODE(G_MODE,'GB_VALIDATE','Validate Only','GB_VALIDATE_COMMIT','Validate and Commit','NA')
INTO l_mode
FROM DUAL;
SELECT DECODE(G_MODE,'GB_VALIDATE','Validate Only','GB_VALIDATE_COMMIT','Validate and Commit','NA')
INTO l_mode
FROM DUAL;
select start_date , end_date , REGULAR_PAYMENT_DATE from per_time_periods where payroll_id = G_PAYROLL_ID and FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) between START_DATE and END_DATE;
SELECT TIME_PERIOD_ID,
START_DATE,
END_DATE,
PAYROLL_ID,
REGULAR_PAYMENT_DATE DATE_PAID
FROM per_time_periods
WHERE payroll_id IN
(SELECT payroll_id
FROM per_all_assignments_f paaf
WHERE assignment_id = p_assignment_id
AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
)
AND regular_payment_date BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE
AND regular_payment_date NOT IN
(SELECT effective_date
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa ,
per_time_periods ptp
WHERE ppa.action_type IN ('Q', 'R')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.time_period_id IN
(SELECT time_period_id
FROM per_time_periods
WHERE payroll_id IN
(SELECT payroll_id
FROM per_all_assignments_f paaf
WHERE assignment_id = p_assignment_id
AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
)
AND regular_payment_date BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE
)
);
SELECT PAAF.PAYROLL_ID,
PAAF.ASSIGNMENT_ID,
PEEF.ELEMENT_ENTRY_ID
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
AND PAPF.PERSON_ID =P_PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
AND L_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND L_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
AND L_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND L_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
SELECT MONTHS_BETWEEN(FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),DATE_OF_BIRTH)/12,
DATE_OF_BIRTH,
SEX
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = P_PERSON_ID;
SELECT DATE_START
FROM PER_PERIODS_OF_SERVICE
WHERE PERSON_ID = P_PERSON_ID
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
select EFFECTIVE_START_DATE from per_all_assignments_f
where assignment_id = p_assignment_id
and g_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
SELECT DATE_START
FROM PER_PERIODS_OF_SERVICE
WHERE PERSON_ID = P_PERSON_ID
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
SELECT DATE_OF_BIRTH
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = P_PERSON_ID
AND G_PRP_END_DATE >= EFFECTIVE_START_DATE
AND G_PRP_START_DATE <= EFFECTIVE_END_DATE;
SELECT PPSV.PENSION_SCHEME_NAME
FROM PQP_GB_PENSION_SCHEMES_V PPSV ,
PQP_CONFIGURATION_VALUES PCV ,
PAY_ELEMENT_TYPES_F PETF ,
PAY_ELEMENT_ENTRIES_F PEEF
WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PCV.PCV_INFORMATION2 = PPSV.ELEMENT_NAME
AND PPSV.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
-- AND PPSV.EMPLOYER_COMPONENT='Y'--Commented for checking
AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND G_PRP_END_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
ORDER BY PETF.EFFECTIVE_START_DATE DESC;
SELECT PCV.PCV_INFORMATION1
FROM PQP_CONFIGURATION_VALUES PCV ,
PAY_ELEMENT_TYPES_F PETF ,
PAY_ELEMENT_ENTRIES_F PEEF
WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PCV.PCV_INFORMATION2 = PETF.ELEMENT_NAME
AND PETF.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
-- AND G_PRP_END_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
-- AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND G_PRP_END_DATE >= ALL(PETF.EFFECTIVE_START_DATE,PEEF.EFFECTIVE_START_DATE )
AND G_PRP_START_DATE <= ALL(PETF.EFFECTIVE_END_DATE,PEEF.EFFECTIVE_END_DATE )
ORDER BY PETF.EFFECTIVE_START_DATE DESC;
SELECT PAAF.PERSON_ID ,PAAF.EFFECTIVE_START_DATE,
PAAF.ASSIGNMENT_ID ,
PEEF.ELEMENT_ENTRY_ID ,
PAAF.ASSIGNMENT_NUMBER
FROM PER_ALL_ASSIGNMENTS_F PAAF ,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ASSIGNMENT_ACTIONS PAA
WHERE PAAF.PAYROLL_ID = G_PAYROLL_ID
AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID = G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND PAA.ASSIGNMENT_ACTION_ID = p_assactid
AND G_PRP_END_DATE >= ALL(PAAF.EFFECTIVE_START_DATE,PEEF.EFFECTIVE_START_DATE )
AND G_PRP_START_DATE <= ALL(PAAF.EFFECTIVE_END_DATE,PEEF.EFFECTIVE_END_DATE );
SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID =P_PERSON_ID
AND BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID
AND G_PRP_END_DATE >= EFFECTIVE_START_DATE
AND G_PRP_START_DATE <= EFFECTIVE_END_DATE;
SELECT 1
FROM PAY_ELEMENT_ENTRY_VALUES_F
WHERE ELEMENT_ENTRY_ID = P_ELEMENT_ENTRY_ID
AND INPUT_VALUE_ID = G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID
AND EFFECTIVE_START_DATE <= G_PRP_END_DATE
AND SCREEN_ENTRY_VALUE = 'Eligible Job Holder Postponement';
SELECT PTP.START_DATE
FROM PER_TIME_PERIODS PTP
WHERE (PTP.END_DATE, PTP.PAYROLL_ID) =
(SELECT START_DATE - 1 ,
PAYROLL_ID
FROM PER_TIME_PERIODS PTP1
WHERE PTP1.START_DATE = G_PRP_START_DATE
AND PTP1.END_DATE = G_PRP_END_DATE
AND PTP1.PAYROLL_ID = G_PAYROLL_ID
);
SELECT PAAF.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
AND PAPF.PERSON_ID =P_PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
-- AND PAST.PAY_SYSTEM_STATUS='P'
AND PAST.PER_SYSTEM_STATUS <>'TERM_ASSIGN'
AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID
AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
AND PAAF.ASSIGNMENT_ID <> P_ASSIGNMENT_ID
AND G_PRP_END_DATE >= all(PAPF.EFFECTIVE_START_DATE,PAAF.EFFECTIVE_START_DATE,PEEVF.EFFECTIVE_START_DATE,PEEF.EFFECTIVE_START_DATE )
AND G_PRP_START_DATE <= all(PAPF.EFFECTIVE_END_DATE,PAAF.EFFECTIVE_END_DATE,PEEVF.EFFECTIVE_END_DATE,PEEF.EFFECTIVE_END_DATE);
SELECT 1
FROM PQP_CONFIGURATION_VALUES PCV
WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PCV.PCV_INFORMATION1 = P_QUAL_SCHEME_NAME;
SELECT count (*)
FROM pay_element_entry_values_f p1
, pay_element_entry_values_f p2
WHERE p1.element_entry_id = p2.element_entry_id
AND p1.element_entry_id IN
(
SELECT element_entry_id
FROM pay_element_entries_f
WHERE element_type_id =
(
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name LIKE 'Pensions Information'
)
AND assignment_id = P_ASSIGNMENT_ID
)
AND p1.input_value_id =
(
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id =
(
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name LIKE 'Pensions Information'
)
AND name = 'Pension Classification'
)
AND p2.input_value_id =
(
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id =
(
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name LIKE 'Pensions Information'
)
AND name = 'Qualifying scheme exists'
)
AND p1.effective_start_date = p2.effective_start_date
AND p1.effective_end_date = p2.effective_end_date
AND p1.screen_entry_value = 'ELIGIBLE JOB HOLDER'
AND p2.screen_entry_value = 'Y';
SELECT PCV.PCV_INFORMATION1
FROM PQP_CONFIGURATION_VALUES PCV ,
PAY_ELEMENT_TYPES_F PETF ,
PAY_ELEMENT_ENTRIES_F PEEF
WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PCV.PCV_INFORMATION2 = PETF.ELEMENT_NAME
AND PETF.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
ORDER BY PETF.EFFECTIVE_START_DATE DESC;
SELECT SUBSTR(line_text,1,240) FROM PAY_MESSAGE_LINES
WHERE PAYROLL_ID = 123 AND MESSAGE_LEVEL ='F' AND SOURCE_ID = P_ASSACTID AND SOURCE_TYPE = 'A';
SELECT scl.SEGMENT20
FROM pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
WHERE ppf.payroll_id =G_PAYROLL_ID
AND scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
AND G_PRP_END_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT pei_information1
FROM per_people_extra_info
WHERE pei_information_category LIKE 'GB_PENSIONS_STAGING_DATE'
AND person_id = P_PERSON_ID;
SELECT UPPER(SUBSTR(TRIM(HOI.ORG_INFORMATION2),1,35)) PENSION_STAGING_DATE
FROM PAY_PAYROLLS_F PPF,
HR_SOFT_CODING_KEYFLEX HSCF,
HR_ORGANIZATION_INFORMATION HOI
WHERE PPF.PAYROLL_ID = G_PAYROLL_ID
AND PPF.BUSINESS_GROUP_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'UK Pensions'
AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
AND PPF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
AND HSCF.SEGMENT1 = HOI.ORG_INFORMATION1;
SELECT PCV_INFORMATION2
FROM PQP_CONFIGURATION_VALUES
WHERE PCV_INFORMATION_CATEGORY = P_CONFIGURATION_TYPE -- 'PAY_GB_ENROLMENT_CUSTOM_PROC'
AND PCV_INFORMATION1 = P_SCHEME_NAME
AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
L_EXCEPTION_UPDATE EXCEPTION;--Update the Pension Info Element and also archive the assignment details.
L_EXCEPTION_UPDATE_INFO EXCEPTION;--Updates ONLY the Pension Info Element - Does not archive the data.
-- update pensions classsification
L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
RAISE L_EXCEPTION_UPDATE_INFO;
-- RAISE L_EXCEPTION_UPDATE;
-- RAISE L_EXCEPTION_UPDATE;
RAISE L_EXCEPTION_UPDATE;
RAISE L_EXCEPTION_UPDATE;
RAISE L_EXCEPTION_UPDATE;
RAISE L_EXCEPTION_UPDATE;
hr_utility.trace('PENSION ELEMENT INSERTION LOGIC');
RAISE G_EXCEPTION_NO_UPDATE; -- Just Archive for Error Cases.
RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
hr_utility.trace('EMPLOYEE ELEMENT INSERTION LOGIC');
INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_ELEMENT_TYPE_NAME=>L_DEFAULT_SCH_ELEMENT_NAME, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC');
INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_ELEMENT_TYPE_NAME=>L_EMPLOYER_COMPONENT, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC - Employer Element is NULL');
update_ni_category_pension(L_ASSIGNMENT_ID,L_AUTO_ENROLLMENT_DATE,G_MODE);
RAISE L_EXCEPTION_UPDATE;
RAISE L_EXCEPTION_UPDATE;
WHEN L_EXCEPTION_UPDATE_INFO THEN
hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE_INFO');
hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
UPDATE_INFO_ELEMENT( L_ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, L_MAIN_ENTRY , G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS ,
G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID , to_char (l_eligible_jh_date
/*Bug 14622818*/
,fnd_date.name_in_mask) );
hr_utility.trace('Exception Update-- AGGREGATED Case');
WHEN L_EXCEPTION_UPDATE THEN
hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE');
hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
UPDATE_INFO_ELEMENT( L_ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, L_MAIN_ENTRY , G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS ,
G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID , to_char (l_eligible_jh_date
/*Bug 14622818*/
,fnd_date.name_in_mask) );
hr_utility.trace('Exception Update-- AGGREGATED Case');
WHEN G_EXCEPTION_NO_UPDATE THEN
hr_utility.trace('INSIDE EXCEPTION: G_EXCEPTION_NO_UPDATE');