[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_rec NUMBER;
g_primary_leaver_dates.DELETE;
g_tab_sec_asgs.DELETE;
debug('inserting in new collection...', 22);
l_insert_rec := set_g_per_asg_leaver_dates
( p_leaver_dates_type => g_primary_leaver_dates) ;
debug('l_insert_rec: '|| l_insert_rec, 24) ;
g_sec_leaver_dates.DELETE;
debug('inserting in new collection...', 32);
l_insert_rec := set_g_per_asg_leaver_dates
( p_leaver_dates_type => g_primary_leaver_dates) ;
debug('l_insert_rec: '|| l_insert_rec, 34) ;
debug('inserting in new collection...', 36);
l_insert_rec := set_g_per_asg_leaver_dates
( p_leaver_dates_type => g_sec_leaver_dates) ;
debug('l_insert_rec: '|| l_insert_rec, 38) ;
g_asg_events.DELETE;
select tbls.user_table_id
from pay_user_tables tbls
where tbls.user_table_name = c_udt_name
and ((business_group_id is null and legislation_code is null)
or (legislation_code is not null
and legislation_code = 'GB')
or (business_group_id is not null
and business_group_id = g_business_group_id)
);
Select user_column_id, user_column_name
from pay_user_columns
where user_table_id = c_user_table_id
and ((business_group_id is null and legislation_code is null)
or (legislation_code is not null
and legislation_code = 'GB')
or (business_group_id is not null
and business_group_id = g_business_group_id)
)
order by user_column_id;
Select user_row_id, row_low_range_or_name
from pay_user_rows_f
where user_table_id = c_user_table_id
and trunc(c_effective_date) between effective_start_date
and effective_end_date
and ((business_group_id is null and legislation_code is null)
or (legislation_code is not null
and legislation_code = 'GB')
or (business_group_id is not null
and business_group_id = g_business_group_id)
)
order by display_sequence;
Select value, effective_start_date, effective_end_date
from pay_user_column_instances_f
where user_column_id = c_user_column_id
and user_row_id = c_user_row_id
and ((business_group_id is null and legislation_code is null)
or (legislation_code is not null
and legislation_code = 'GB')
or (business_group_id is not null
and business_group_id = g_business_group_id)
);
p_tab_ele_ids.delete;
select put.user_table_name
,puc.user_column_name
,pur.row_low_range_or_name
,pci.value
from pay_user_tables put
,pay_user_columns puc
,pay_user_rows_f pur
,pay_user_column_instances_f pci
where put.user_table_name = c_user_table_name
and puc.user_table_id = put.user_table_id
and puc.user_column_name = c_user_column_name
and pur.row_low_range_or_name = c_user_row_name
and pur.user_table_id = put.user_table_id
and pci.user_column_id = puc.user_column_id
and pci.user_row_id = pur.user_row_id
and Trunc(c_effective_date) between pur.effective_start_date
and pur.effective_end_date
and Trunc(c_effective_date) between pci.effective_start_date
and pci.effective_end_date
and ((pci.business_group_id is null and pci.legislation_code is null)
or (pci.legislation_code is not null
and pci.legislation_code = 'GB')
or (pci.business_group_id is not null
and pci.business_group_id = NVL(p_business_group_id, g_business_group_id))
)
order by put.user_table_name, puc.user_column_name, pur.display_sequence;
g_abs_bal_type_id.DELETE;
g_sal_bal_type_id.DELETE;
g_cl_bal_type_id.DELETE;
g_tab_abs_ele_ids.DELETE;
g_tab_sal_ele_ids.DELETE;
g_osla_bal_type_id.DELETE;
g_osla_cl_bal_type_id.DELETE;
g_tab_osla_ele_ids.DELETE;
select 'Y'
from hr_lookups
where meaning = p_meaning
and lookup_type = 'PQP_RATE_TYPE'
and enabled_flag = 'Y'
and g_effective_date between
nvl(start_date_active, g_effective_date)
and nvl(end_date_active, g_effective_date);
g_lea_business_groups.DELETE;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'U'
,request_id = fnd_global.conc_request_id
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,last_update_login = fnd_global.login_id
,object_version_number = (object_version_number + 1)
WHERE record_type = 'X'
AND ext_dfn_id = ben_ext_thread.g_ext_dfn_id --ENH2
AND lea_number = g_lea_number; --ENH1
g_tab_lon_aln_eles.DELETE;
g_tab_spl_aln_eles.DELETE;
SELECT TO_DATE('01-04-'||
DECODE
(SIGN(TO_NUMBER(TO_CHAR(g_effective_run_date,'MM')) - 04)
,-1,TO_CHAR(ADD_MONTHS(g_effective_run_date,-12),'YYYY')
,TO_CHAR(g_effective_run_date,'YYYY'))
,'DD-MM-YYYY')
INTO g_pension_year_start_date
FROM DUAL;
UPDATE pay_process_events
SET retroactive_status = p_status
,status = p_status
WHERE assignment_id = p_assignment_id
AND change_type = 'REPORTS'
AND effective_date -- allow all events effective as of and on pension year start date
BETWEEN GREATEST(NVL(p_start_date,g_pension_year_start_date)
,g_pension_year_start_date)
AND LEAST(NVL(p_end_date,g_effective_run_date)
,g_effective_run_date)
; -- allow all events upto end of day (eff_dt - 1)
debug(fnd_number.number_to_canonical(SQL%ROWCOUNT)||' PPE row(s) updated.');
UPDATE pay_process_events ppe
SET ppe.retroactive_status = p_status
,ppe.status = p_status
WHERE ppe.assignment_id = p_assignment_id
AND ppe.change_type = 'REPORTS'
AND ppe.effective_date -- allow all events effective as of and on pension year start date
BETWEEN GREATEST(NVL(p_start_date,g_pension_year_start_date)
,g_pension_year_start_date)
AND LEAST(NVL(p_end_date,g_effective_run_date)
,g_effective_run_date)
AND ppe.surrogate_key = p_element_entry_id
AND EXISTS (SELECT 1
FROM pay_dated_tables pdt
,pay_event_updates peu
WHERE pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
AND peu.dated_table_id = pdt.dated_table_id
AND peu.change_type = ppe.change_type
AND peu.event_update_id = ppe.event_update_id
)
;
debug(fnd_number.number_to_canonical(SQL%ROWCOUNT)||' PPE row(s) updated.');
UPDATE pay_process_events ppe
SET ppe.retroactive_status = p_status
,ppe.status = p_status
WHERE ppe.assignment_id = p_assignment_id
AND ppe.change_type = 'REPORTS'
AND ppe.effective_date -- allow all events effective as of and on pension year start date
BETWEEN GREATEST(NVL(p_start_date,g_pension_year_start_date)
,g_pension_year_start_date)
AND LEAST(NVL(p_end_date,g_effective_run_date)
,g_effective_run_date)
AND EXISTS (SELECT 1
FROM pay_dated_tables pdt
,pay_event_updates peu
WHERE pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
AND peu.dated_table_id = pdt.dated_table_id
AND peu.change_type = ppe.change_type
AND peu.event_update_id = ppe.event_update_id
)
AND EXISTS (SELECT 1
FROM pay_element_entry_values_f peev
WHERE peev.element_entry_id = p_element_entry_id
AND peev.element_entry_value_id = ppe.surrogate_key
)
;
debug(fnd_number.number_to_canonical(SQL%ROWCOUNT)||' PPE row(s) updated.');
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE assignment_id = p_assignment_id
AND rownum = 1;
t_proration_dates.delete;
t_proration_changes.delete;
SELECT per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = p_status_type_id;
debug('Update Type :'||l_proration_changes(l_itr), 26);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
debug('After Assignment Status Events check - Deleted Proration Dates',60);
debug('Update Type :'||l_proration_changes(l_itr), 86);
l_proration_changes(l_itr) = 'U' -- Update
)
) THEN
-- Get the previous assignment record
-- Currently not needed, will uncomment if needed
--FETCH csr_asg_details_dn INTO l_prev_asg_details;
IF l_proration_changes(l_itr) = 'U' -- Event was an update
AND
-- Prev rec was found. Redundant chk, sh always b found
csr_asg_details_dn%FOUND
AND
-- Bugfix 3641851:CBF4 : Added just to avoid exception
l_prev_asg_details.location_id IS NOT NULL
AND
-- The current and new Establishment nos. are different
-- This chk is to ensure that the current location
-- was not corrected after doing a datetrack update
(-- Bugfix 3641851:CBF4 : Added EXISTS just to avoid exception
pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
AND
pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
<>
pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
) THEN
debug('Location change is a new line of service event, storing', 130);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
debug('After Location Change check - Deleted Proration Dates',140);
debug('Update Type :'||l_proration_changes(l_itr), 166);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
debug('After Elected Pension Flag change check - Deleted Proration Dates',200);
debug('Update Type :'||l_proration_changes(l_itr), 216);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
l_proration_dates.DELETE;
l_proration_changes.DELETE;
debug('After Assignment Status Events check - Deleted Proration Dates',60);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
debug('After Location Change check - Deleted Proration Dates',120);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
debug('After Elected Pension Flag change check - Deleted Proration Dates',140);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
SELECT 'Y'
FROM pqp_extract_attributes pqea
,ben_ext_rslt rslt
,ben_ext_rslt_dtl rdtl
,ben_ext_rcd drcd
WHERE pqea.ext_dfn_type = g_extract_type
AND rslt.ext_dfn_id = pqea.ext_dfn_id
-- Bugfix 3073562:GAP1:GAP2, now using master bg id
AND rslt.business_group_id = g_master_bg_id
AND rslt.ext_stat_cd NOT IN
('F' -- Job Failure
,'R' -- Rejected By User
,'X' -- Executing
)
AND rdtl.ext_rslt_id = rslt.ext_rslt_id
AND drcd.ext_rcd_id = rdtl.ext_rcd_id
AND drcd.rcd_type_cd = 'D' -- detail records only
-- changed the person_id check to NI Number check.
--AND rdtl.person_id = p_person_id
AND rdtl.val_04 IN
( SELECT national_identifier
FROM per_all_people_f per2
WHERE per2.person_id = p_person_id
)
-- match the header element
AND EXISTS
( SELECT 'Y'
FROM ben_ext_rslt_dtl rdtl1
WHERE rdtl1.business_group_id = g_master_bg_id
AND EXISTS
( SELECT 'Y'
FROM ben_ext_rcd drcd1
WHERE drcd1.rcd_type_cd = 'H'
AND drcd1.ext_rcd_id = rdtl1.ext_rcd_id
)
AND rdtl.ext_rslt_id = rdtl1.ext_rslt_id
AND SUBSTR(rdtl1.val_01
,1
,INSTR(rdtl1.val_01,':',1,3)--upto third occurence
)
=SUBSTR(g_header_system_element
,1
,INSTR(g_header_system_element,':',1,3)
)
)
-- only in the current pension year and upto the end of last run
AND rslt.eff_dt between g_pension_year_start_date and g_last_effective_date
-- ALRD_RPT change
-- checking for the matching date and withdrawl flag.
AND to_date(rdtl.val_14,'DDMMRR') = p_leaver_date -- Leaver Date
AND rdtl.val_15 = 'W' --Withdrawl Flag
-- only need to look for one record
AND ROWNUM < 2;
g_tab_sec_asgs.DELETE;
g_tab_sec_asgs.DELETE;
Select extv.value,extv.business_group_id
From pay_user_tables tbls
,pay_user_columns asgc
,pay_user_columns extc
,pay_user_rows_f urws
,pay_user_column_instances_f asgv
,pay_user_column_instances_f extv
where tbls.user_table_name ='PQP_GB_TP_EMPLOYMENT_CATEGORY_TRANSALATION_TABLE'
and asgc.user_table_id = tbls.user_table_id
and extc.user_table_id = tbls.user_table_id
and asgc.user_column_name = 'Assignment Employment Category Lookup Code'
and extc.user_column_name = c_udt_column_name
and urws.user_table_id = tbls.user_table_id
and (urws.business_group_id = c_business_group_id
OR
(urws.business_group_id IS NULL
AND urws.legislation_code = c_legislation_code)
OR
(urws.business_group_id IS NULL AND urws.legislation_code IS NULL)
)
and c_effective_date BETWEEN urws.effective_start_date
AND urws.effective_end_date
and asgv.user_column_id = asgc.user_column_id
and c_effective_date BETWEEN asgv.effective_start_date
AND asgv.effective_end_date
and extv.user_column_id = extc.user_column_id
and c_effective_date BETWEEN extv.effective_start_date
AND extv.effective_end_date
and asgv.user_row_id = urws.user_row_id
and extv.user_row_id = asgv.user_row_id
and asgv.value = c_asg_emp_cat_cd;
Select paa.employment_category
from per_all_assignments_f paa
where paa.assignment_id = c_assignment_id
and c_effective_date between paa.effective_start_date
and paa.effective_end_date;
Select pet.element_type_id
from pay_element_types_f pet
where c_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_name = c_element_name;
Select pee.element_entry_id
from pay_element_entries_f pee
,pay_element_links_f pel
where pee.assignment_id = c_assignment_id
and pel.element_link_id = pee.element_link_id
and pel.element_type_id = c_element_type_id
and c_effective_date between pee.effective_start_date
and pee.effective_end_date
and c_effective_date between pel.effective_start_date
and pel.effective_end_date;
select assignment_attribute_id
,assignment_id
,tp_is_teacher
,tp_safeguarded_grade
,tp_fast_track
,tp_elected_pension
-- added the new column for the new safeguarded logic based on safeguarded rate type
,tp_safeguarded_rate_type
from pqp_assignment_attributes_f
where assignment_id = c_assignment_id
and c_effective_date between effective_start_date
and effective_end_date
order by effective_start_date;
select 'Y'
from pay_element_links_f pel
,pay_element_entries_f pee
,pay_element_types_f pet
where pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = p_assignment_id
and pet.element_type_id = p_element_type_id
and p_date between pel.effective_start_date and
pel.effective_end_date
and p_date between pee.effective_start_date and
pee.effective_end_date
and p_date between pet.effective_start_date and
pet.effective_end_date ;
select decode(p_safeguarded_yn
,'Y'
,g_sf_rate_type
,g_sal_rate_type)
into l_rate_name
from dual;
SELECT *
FROM pqp_ext_cross_person_records emd
WHERE emd.record_type = 'X'
AND emd.national_identifier = p_national_identifier
AND emd.ext_dfn_id = ben_ext_thread.g_ext_dfn_id --ENH3
AND emd.lea_number = g_lea_number --ENH3
FOR UPDATE OF processing_status NOWAIT;
BEGIN -- Attempt an update
debug(l_proc_name, 20);
UPDATE pqp_ext_cross_person_records
SET business_group_id = p_business_group_id
,person_id = p_person_id
,national_identifier = p_national_identifier
,assignment_id = p_assignment_id
,effective_start_date = p_effective_start_date
,effective_end_date = p_effective_end_date
,processing_status = p_processing_status
,request_id = p_request_id
,last_updated_by = fnd_global.user_id
,last_update_date = SYSDATE
,last_update_login = fnd_global.login_id
,object_version_number = (l_multirec_per.object_version_number + 1)
WHERE CURRENT OF csr_multirec_person;
END; -- Attempt an update
ELSE -- Notfound, Need to insert
debug(l_proc_name, 70);
INSERT INTO pqp_ext_cross_person_records
(record_type
,ext_dfn_id --ENH3
,lea_number --ENH3
,business_group_id
,person_id
,national_identifier
,assignment_id
,effective_start_date
,effective_end_date
,processing_status
,request_id
,created_by
,creation_date
,object_version_number
)
VALUES
('X'
,ben_ext_thread.g_ext_dfn_id --ENH3
,pqp_gb_tp_pension_extracts.g_lea_number --ENH3
,p_business_group_id
,p_person_id
,p_national_identifier
,p_assignment_id
,p_effective_start_date
,p_effective_end_date
,p_processing_status
,p_request_id
,fnd_global.user_id
,SYSDATE
,1
);
SELECT per.person_id person_id
,per.national_identifier national_identifier
FROM per_all_assignments_f asg
,per_all_people_f per
WHERE asg.assignment_id = p_assignment_id
AND per.person_id = asg.person_id;
g_asg_events.DELETE;
g_per_asg_leaver_dates.DELETE;
g_asg_recalc_details.DELETE;
g_asg_events.DELETE;
g_asg_recalc_details.DELETE;
g_per_asg_leaver_dates.DELETE;
debug('Element Entry change has happened, UPDATE',70);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
UPDATE pay_process_events ppe
SET ppe.retroactive_status = p_status
,ppe.status = p_status
WHERE ppe.assignment_id IS NULL
AND ppe.change_type = 'REPORTS'
AND ppe.effective_date -- allow all events effective as of and effective p_start_date
BETWEEN p_start_date AND p_end_date
AND ppe.surrogate_key = p_grade_id
AND EXISTS (SELECT 1
FROM pay_dated_tables pdt
,pay_event_updates peu
WHERE pdt.table_name = 'PAY_GRADE_RULES_F'
AND peu.dated_table_id = pdt.dated_table_id
AND peu.change_type = ppe.change_type
AND peu.event_update_id = ppe.event_update_id
)
;
SELECT petf.element_type_id
,petf.eei_information2 pay_source_value
FROM pay_element_type_extra_info petf
WHERE petf.element_type_id =p_ele_id
AND petf.eei_information_category ='PQP_UK_ELEMENT_ATTRIBUTION';
g_asg_events.DELETE;
g_asg_recalc_details.DELETE;
UPDATE ben_ext_rslt_dtl
SET VAL_01 = p_dtl_rec.VAL_01
,VAL_02 = p_dtl_rec.VAL_02
,VAL_03 = p_dtl_rec.VAL_03
,VAL_04 = p_dtl_rec.VAL_04
,VAL_05 = p_dtl_rec.VAL_05
,VAL_06 = p_dtl_rec.VAL_06
,VAL_07 = p_dtl_rec.VAL_07
,VAL_08 = p_dtl_rec.VAL_08
,VAL_09 = p_dtl_rec.VAL_09
,VAL_10 = p_dtl_rec.VAL_10
,VAL_11 = p_dtl_rec.VAL_11
,VAL_12 = p_dtl_rec.VAL_12
,VAL_13 = p_dtl_rec.VAL_13
,VAL_14 = p_dtl_rec.VAL_14
,VAL_15 = p_dtl_rec.VAL_15
,VAL_16 = p_dtl_rec.VAL_16
,VAL_17 = p_dtl_rec.VAL_17
,VAL_19 = p_dtl_rec.VAL_19
,VAL_18 = p_dtl_rec.VAL_18
,VAL_20 = p_dtl_rec.VAL_20
,VAL_21 = p_dtl_rec.VAL_21
,VAL_22 = p_dtl_rec.VAL_22
,VAL_23 = p_dtl_rec.VAL_23
,VAL_24 = p_dtl_rec.VAL_24
,VAL_25 = p_dtl_rec.VAL_25
,VAL_26 = p_dtl_rec.VAL_26
,VAL_27 = p_dtl_rec.VAL_27
,VAL_28 = p_dtl_rec.VAL_28
,VAL_29 = p_dtl_rec.VAL_29
,VAL_30 = p_dtl_rec.VAL_30
,VAL_31 = p_dtl_rec.VAL_31
,VAL_32 = p_dtl_rec.VAL_32
,VAL_33 = p_dtl_rec.VAL_33
,VAL_34 = p_dtl_rec.VAL_34
,VAL_35 = p_dtl_rec.VAL_35
,VAL_36 = p_dtl_rec.VAL_36
,VAL_37 = p_dtl_rec.VAL_37
,VAL_38 = p_dtl_rec.VAL_38
,VAL_39 = p_dtl_rec.VAL_39
,VAL_40 = p_dtl_rec.VAL_40
,VAL_41 = p_dtl_rec.VAL_41
,VAL_42 = p_dtl_rec.VAL_42
,VAL_43 = p_dtl_rec.VAL_43
,VAL_44 = p_dtl_rec.VAL_44
,VAL_45 = p_dtl_rec.VAL_45
,VAL_46 = p_dtl_rec.VAL_46
,VAL_47 = p_dtl_rec.VAL_47
,VAL_48 = p_dtl_rec.VAL_48
,VAL_49 = p_dtl_rec.VAL_49
,VAL_50 = p_dtl_rec.VAL_50
,VAL_51 = p_dtl_rec.VAL_51
,VAL_52 = p_dtl_rec.VAL_52
,VAL_53 = p_dtl_rec.VAL_53
,VAL_54 = p_dtl_rec.VAL_54
,VAL_55 = p_dtl_rec.VAL_55
,VAL_56 = p_dtl_rec.VAL_56
,VAL_57 = p_dtl_rec.VAL_57
,VAL_58 = p_dtl_rec.VAL_58
,VAL_59 = p_dtl_rec.VAL_59
,VAL_60 = p_dtl_rec.VAL_60
,VAL_61 = p_dtl_rec.VAL_61
,VAL_62 = p_dtl_rec.VAL_62
,VAL_63 = p_dtl_rec.VAL_63
,VAL_64 = p_dtl_rec.VAL_64
,VAL_65 = p_dtl_rec.VAL_65
,VAL_66 = p_dtl_rec.VAL_66
,VAL_67 = p_dtl_rec.VAL_67
,VAL_68 = p_dtl_rec.VAL_68
,VAL_69 = p_dtl_rec.VAL_69
,VAL_70 = p_dtl_rec.VAL_70
,VAL_71 = p_dtl_rec.VAL_71
,VAL_72 = p_dtl_rec.VAL_72
,VAL_73 = p_dtl_rec.VAL_73
,VAL_74 = p_dtl_rec.VAL_74
,VAL_75 = p_dtl_rec.VAL_75
,OBJECT_VERSION_NUMBER = p_dtl_rec.OBJECT_VERSION_NUMBER
,THRD_SORT_VAL = p_dtl_rec.THRD_SORT_VAL
WHERE ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
INSERT INTO ben_ext_rslt_dtl
(EXT_RSLT_DTL_ID
,EXT_RSLT_ID
,BUSINESS_GROUP_ID
,EXT_RCD_ID
,PERSON_ID
,VAL_01
,VAL_02
,VAL_03
,VAL_04
,VAL_05
,VAL_06
,VAL_07
,VAL_08
,VAL_09
,VAL_10
,VAL_11
,VAL_12
,VAL_13
,VAL_14
,VAL_15
,VAL_16
,VAL_17
,VAL_19
,VAL_18
,VAL_20
,VAL_21
,VAL_22
,VAL_23
,VAL_24
,VAL_25
,VAL_26
,VAL_27
,VAL_28
,VAL_29
,VAL_30
,VAL_31
,VAL_32
,VAL_33
,VAL_34
,VAL_35
,VAL_36
,VAL_37
,VAL_38
,VAL_39
,VAL_40
,VAL_41
,VAL_42
,VAL_43
,VAL_44
,VAL_45
,VAL_46
,VAL_47
,VAL_48
,VAL_49
,VAL_50
,VAL_51
,VAL_52
,VAL_53
,VAL_54
,VAL_55
,VAL_56
,VAL_57
,VAL_58
,VAL_59
,VAL_60
,VAL_61
,VAL_62
,VAL_63
,VAL_64
,VAL_65
,VAL_66
,VAL_67
,VAL_68
,VAL_69
,VAL_70
,VAL_71
,VAL_72
,VAL_73
,VAL_74
,VAL_75
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,OBJECT_VERSION_NUMBER
,PRMY_SORT_VAL
,SCND_SORT_VAL
,THRD_SORT_VAL
,TRANS_SEQ_NUM
,RCRD_SEQ_NUM
)
VALUES
(p_dtl_rec.EXT_RSLT_DTL_ID
,p_dtl_rec.EXT_RSLT_ID
,p_dtl_rec.BUSINESS_GROUP_ID
,p_dtl_rec.EXT_RCD_ID
,p_dtl_rec.PERSON_ID
,p_dtl_rec.VAL_01
,p_dtl_rec.VAL_02
,p_dtl_rec.VAL_03
,p_dtl_rec.VAL_04
,p_dtl_rec.VAL_05
,p_dtl_rec.VAL_06
,p_dtl_rec.VAL_07
,p_dtl_rec.VAL_08
,p_dtl_rec.VAL_09
,p_dtl_rec.VAL_10
,p_dtl_rec.VAL_11
,p_dtl_rec.VAL_12
,p_dtl_rec.VAL_13
,p_dtl_rec.VAL_14
,p_dtl_rec.VAL_15
,p_dtl_rec.VAL_16
,p_dtl_rec.VAL_17
,p_dtl_rec.VAL_19
,p_dtl_rec.VAL_18
,p_dtl_rec.VAL_20
,p_dtl_rec.VAL_21
,p_dtl_rec.VAL_22
,p_dtl_rec.VAL_23
,p_dtl_rec.VAL_24
,p_dtl_rec.VAL_25
,p_dtl_rec.VAL_26
,p_dtl_rec.VAL_27
,p_dtl_rec.VAL_28
,p_dtl_rec.VAL_29
,p_dtl_rec.VAL_30
,p_dtl_rec.VAL_31
,p_dtl_rec.VAL_32
,p_dtl_rec.VAL_33
,p_dtl_rec.VAL_34
,p_dtl_rec.VAL_35
,p_dtl_rec.VAL_36
,p_dtl_rec.VAL_37
,p_dtl_rec.VAL_38
,p_dtl_rec.VAL_39
,p_dtl_rec.VAL_40
,p_dtl_rec.VAL_41
,p_dtl_rec.VAL_42
,p_dtl_rec.VAL_43
,p_dtl_rec.VAL_44
,p_dtl_rec.VAL_45
,p_dtl_rec.VAL_46
,p_dtl_rec.VAL_47
,p_dtl_rec.VAL_48
,p_dtl_rec.VAL_49
,p_dtl_rec.VAL_50
,p_dtl_rec.VAL_51
,p_dtl_rec.VAL_52
,p_dtl_rec.VAL_53
,p_dtl_rec.VAL_54
,p_dtl_rec.VAL_55
,p_dtl_rec.VAL_56
,p_dtl_rec.VAL_57
,p_dtl_rec.VAL_58
,p_dtl_rec.VAL_59
,p_dtl_rec.VAL_60
,p_dtl_rec.VAL_61
,p_dtl_rec.VAL_62
,p_dtl_rec.VAL_63
,p_dtl_rec.VAL_64
,p_dtl_rec.VAL_65
,p_dtl_rec.VAL_66
,p_dtl_rec.VAL_67
,p_dtl_rec.VAL_68
,p_dtl_rec.VAL_69
,p_dtl_rec.VAL_70
,p_dtl_rec.VAL_71
,p_dtl_rec.VAL_72
,p_dtl_rec.VAL_73
,p_dtl_rec.VAL_74
,p_dtl_rec.VAL_75
,p_dtl_rec.CREATED_BY
,p_dtl_rec.CREATION_DATE
,p_dtl_rec.LAST_UPDATE_DATE
,p_dtl_rec.LAST_UPDATED_BY
,p_dtl_rec.LAST_UPDATE_LOGIN
,p_dtl_rec.PROGRAM_APPLICATION_ID
,p_dtl_rec.PROGRAM_ID
,p_dtl_rec.PROGRAM_UPDATE_DATE
,p_dtl_rec.REQUEST_ID
,p_dtl_rec.OBJECT_VERSION_NUMBER
,p_dtl_rec.PRMY_SORT_VAL
,p_dtl_rec.SCND_SORT_VAL
,p_dtl_rec.THRD_SORT_VAL
,p_dtl_rec.TRANS_SEQ_NUM
,p_dtl_rec.RCRD_SEQ_NUM
);
debug('Before Update ',147);
debug('After Update ',147);
l_insert_rec NUMBER;
g_primary_leaver_dates.DELETE;
g_tab_sec_asgs.DELETE;
debug('inserting in new collection...', 22);
l_insert_rec := set_g_per_asg_leaver_dates
( p_leaver_dates_type => g_primary_leaver_dates) ;
debug('l_insert_rec: '|| l_insert_rec, 24) ;
g_sec_leaver_dates.DELETE;
debug('inserting in new collection...', 22);
l_insert_rec := set_g_per_asg_leaver_dates
( p_leaver_dates_type => g_primary_leaver_dates) ;
debug('l_insert_rec: '|| l_insert_rec, 24) ;
debug('inserting in new collection...', 22);
l_insert_rec := set_g_per_asg_leaver_dates
( p_leaver_dates_type => g_sec_leaver_dates) ;
debug('l_insert_rec: '|| l_insert_rec, 24) ;
g_asg_events.DELETE;
RETURN 'DELETE';
g_asg_events.DELETE;
DELETE
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
AND dtl.ext_rcd_id = l_ext_dtl_rcd_id
AND dtl.val_01 = 'DELETE';
debug('Number of Dummy Records Deleted :'||to_char(SQL%ROWCOUNT));
SELECT * FROM pay_rates
WHERE rate_id = p_rate_id;
SELECT petei.eei_information2 pay_source_value
,petei.eei_information3 Qualifier
FROM pay_element_type_extra_info petei
WHERE petei.element_type_id = p_element_type_id
AND petei.eei_information_category ='PQP_UK_ELEMENT_ATTRIBUTION';
SELECT pee.element_entry_id
FROM pay_element_links_f pel
,pay_element_entries_f pee
where pel.element_type_id = p_element_type_id
and p_effective_date between pel.effective_start_date
and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and p_effective_date between pee.effective_start_date
and pee.effective_end_date
and pee.assignment_id = p_assignment_id;
SELECT effective_start_date
,effective_end_date
,rate_id
,grade_or_spinal_point_id
,rate_type
FROM pay_grade_rules_f
WHERE grade_rule_id = p_grade_rule_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT grade_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT pspp.placement_id
FROM per_spinal_point_placements_f pspp
,per_grade_spines_f pgs
,per_spinal_point_steps_f psps
WHERE pspp.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pspp.effective_start_date
AND pspp.effective_end_date
AND pgs.parent_spine_id = pspp.parent_spine_id
AND pgs.grade_id = p_grade_id
AND p_effective_date BETWEEN pgs.effective_start_Date
AND pgs.effective_end_Date
AND psps.grade_spine_id = pgs.grade_spine_id
AND psps.spinal_point_id = p_spinal_point_id
AND p_effective_date BETWEEN psps.effective_start_Date
AND psps.effective_end_Date
AND psps.step_id = pspp.step_id;
SELECT row_low_range_or_name
FROM pay_user_rows_f
WHERE user_table_id = c_udt_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
AND row_low_range_or_name in ('LARP Inner Allowance','LARP Outer Allowance',
'LARP Fringe Allowance','LARP Inner Plus Inner Supplement'
)
ORDER BY display_sequence;
SELECT row_low_range_or_name
FROM pay_user_rows_f
WHERE user_table_id = c_udt_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
AND row_low_range_or_name in ('SPAP Lower Rate','SPAP Higher Rate',
'SPAP Special Needs Lower Rate','SPAP Special Needs Higher Rate'
)
ORDER BY display_sequence;
g_tab_lon_aln_eles.DELETE;
l_tab_mng_aln_eles.DELETE;
g_tab_spl_aln_eles.DELETE;
SELECT 'X'
FROM pay_element_entries_f pee
,pay_element_links_f pel
WHERE pee.assignment_id = c_assignment_id
AND pee.entry_type = 'E'
AND pee.element_link_id = pel.element_link_id
AND c_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pel.element_type_id = c_element_type_id
AND c_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date;
SELECT decode(c_allowance_type,'LONDON_ALLOWANCE_RULE',information6,
'SPECIAL_ALLOWANCE_RULE',information7
) indicator
FROM per_grades pgr,per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.grade_id = pgr.grade_id
AND pgr.information_category = 'GB_PQP_PENSERV_GRADE_INFO';