[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ext_rslt_id
FROM ben_ext_rslt
WHERE request_id = p_request_id
AND ext_dfn_id = p_ext_dfn_id;
SELECT COUNT(*)
FROM ben_ext_rcd_in_file fil
,ben_ext_rcd rcd
WHERE fil.ext_rcd_id = rcd.ext_rcd_id
AND fil.ext_file_id = p_ext_file_id
AND rcd.rcd_type_cd = 'H';
SELECT COUNT(*)
FROM ben_ext_rcd_in_file fil
,ben_ext_rcd rcd
WHERE fil.ext_rcd_id = rcd.ext_rcd_id
AND fil.ext_file_id = p_ext_file_id
AND rcd.rcd_type_cd = 'T';
SELECT COUNT(*)
FROM ben_ext_rslt_dtl xrd
WHERE xrd.ext_rslt_id = p_ext_rslt_id;
SELECT COUNT(DISTINCT person_id)
FROM ben_ext_rslt_dtl xrd
WHERE xrd.ext_rslt_id = p_ext_rslt_id
AND person_id not in (0, 999999999999);
SELECT COUNT(*)
FROM ben_ext_rslt_err err
WHERE err.ext_rslt_id = p_ext_rslt_id;
SELECT
prmy_sort_val,
scnd_sort_val,
thrd_sort_val,
trans_seq_num,
rcrd_seq_num,
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,
business_group_id
FROM ben_ext_rslt_dtl
WHERE ext_rslt_id = c_ext_rslt_id;
SELECT rcd_type_cd
FROM ben_ext_rcd
WHERE ext_rcd_id = c_ext_rcd_id;
SELECT ext_rslt_err_id,
err_num,
err_txt,
typ_cd,
person_id,
business_group_id,
object_version_number,
request_id,
program_application_id,
program_id,
program_update_date,
ext_rslt_id
FROM ben_ext_rslt_err
WHERE ext_rslt_id = c_ext_rslt_id;
,p_program_update_date => SYSDATE
,p_request_id => p_master_request_id
,p_object_version_number => l_object_version_number
);
,p_program_update_date => SYSDATE
,p_ext_rslt_id => p_master_ext_rslt_id
,p_effective_date => p_effective_date
);
SELECT ext_rslt_id
,eff_dt
,ext_strt_dt
,ext_end_dt
,drctry_name
,output_name
FROM ben_ext_rslt
WHERE request_id = c_request_id
AND ext_dfn_id = p_ext_dfn_id
AND business_group_id = p_master_business_group;
SELECT ext_file_id
,output_name
,apnd_rqst_id_flag
,kickoff_wrt_prc_flag
FROM ben_ext_dfn
WHERE ext_dfn_id = p_ext_dfn_id;
,p_program_update_date => SYSDATE
,p_request_id => l_master_request_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_ext_rslt_info_rec.eff_dt);
UPDATE ben_ext_rslt_dtl rslt
SET val_02 = l_val_02
,val_03 = l_val_03
WHERE ext_rslt_id = l_master_ext_rslt_id
AND EXISTS ( SELECT 1 FROM ben_ext_rcd rcd
WHERE rcd.ext_rcd_id = rslt.ext_rcd_id
AND rcd.rcd_type_cd = 'T'
);
ben_ext_rslt_api.update_ext_rslt
(p_validate => FALSE
,p_ext_rslt_id => l_master_ext_rslt_id
,p_run_end_dt => SYSDATE
,p_ext_stat_cd => 'E'
,p_tot_rec_num => l_tot_count
,p_tot_per_num => l_per_count
,p_tot_err_num => l_err_count
,p_program_application_id => l_master_prog_appl_id
,p_program_id => l_master_program_id
,p_program_update_date => SYSDATE
,p_request_id => l_master_request_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_ext_rslt_info_rec.eff_dt);
ben_ext_rslt_api.update_ext_rslt
(p_validate => FALSE
,p_ext_rslt_id => l_master_ext_rslt_id
,p_run_end_dt => SYSDATE
,p_ext_stat_cd => 'S'
,p_tot_rec_num => l_tot_count
,p_tot_per_num => l_per_count
,p_tot_err_num => l_err_count
,p_program_application_id => l_master_prog_appl_id
,p_program_id => l_master_program_id
,p_program_update_date => SYSDATE
,p_request_id => l_master_request_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_ext_rslt_info_rec.eff_dt);
SELECT DECODE
(i
,1, p_request_id_1
,2, p_request_id_2
,3, p_request_id_3
,4, p_request_id_4
,5, p_request_id_5
,NULL
)
INTO l_request_id
FROM dual;
SELECT user_column_id
FROM pay_user_columns
WHERE user_table_id = p_udt_id
AND user_column_name = 'Location Code'
AND legislation_code = 'GB'
AND business_group_id IS NULL;
SELECT user_row_id
FROM pay_user_rows_f
WHERE user_table_id = p_udt_id
AND row_low_range_or_name = 'Criteria'
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date
AND legislation_code = 'GB'
AND business_group_id IS NULL;
SELECT uci.rowid, uci.*
FROM pay_user_columns puc
,pay_user_rows_f pur
,pay_user_column_instances_f uci
WHERE -- User Column
puc.user_table_id = p_udt_id
AND puc.user_column_name = 'Location Code'
AND puc.legislation_code = 'GB'
AND puc.business_group_id IS NULL
-- User Row
AND pur.row_low_range_or_name = 'Criteria'
AND c_effective_date BETWEEN pur.effective_start_date
AND pur.effective_end_date
AND pur.legislation_code = 'GB'
AND pur.business_group_id IS NULL
-- Join column and Col Instance
AND uci.user_column_id = puc.user_column_id
-- join row and Col Instance
AND uci.user_row_id = pur.user_row_id
-- Filter instance on date and BG
AND uci.business_group_id = p_business_group_id
AND ((c_effective_date BETWEEN uci.effective_start_date
AND uci.effective_end_date
)
OR
(uci.effective_start_date > c_effective_date
)
)
ORDER BY uci.effective_start_date ASC;
PAY_USER_COLUMN_INSTANCES_PKG.delete_row(l_udt_row.rowid);
debug('Deleted row in loop', 50);
pay_user_column_instance_api.delete_user_column_instance
(p_validate => FALSE
,p_effective_date => l_udt_row.effective_start_date
,p_user_column_instance_id => l_udt_row.user_column_instance_id
,p_datetrack_update_mode => hr_api.g_zap
,p_object_version_number => l_udt_row.object_version_number
,p_effective_start_date => l_udt_row.effective_start_date
,p_effective_end_date => l_udt_row.effective_start_date
);
PAY_USER_COLUMN_INSTANCES_PKG.insert_row
(p_rowid => l_udt_row.rowid
,p_user_column_instance_id => l_udt_row.user_column_instance_id
,p_effective_start_date => c_effective_date
,p_effective_end_date => hr_api.g_eot
,p_user_row_id => l_udt_row.user_row_id
,p_user_column_id => l_udt_row.user_column_id
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_legislation_subgroup => NULL
,p_value => p_value
);
UPDATE pqp_ext_cross_person_records
SET business_group_id = p_business_group_id
,effective_start_date = p_effective_date
,request_id = nvl(p_master_request_id, g_master_request_id)
,processing_status = 'P' -- Processing
,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 = 'M'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
debug('No of rows updated :'||to_char(SQL%ROWCOUNT), 40);
debug('Updated master BG row with :'||to_char(p_business_group_id), 50);
INSERT INTO pqp_ext_cross_person_records
(record_type
,ext_dfn_id
,lea_number
,business_group_id
,effective_start_date
,request_id
,processing_status
,created_by
,creation_date
,object_version_number
)
VALUES
('M' -- Master BG row
,p_ext_dfn_id
,g_lea_number
,p_business_group_id
,p_effective_date
,nvl(p_master_request_id, g_master_request_id)
,'P' -- Processing
,fnd_global.user_id
,SYSDATE
,1
);
debug('Inserted master BG row with :'||to_char(p_business_group_id), 60);
UPDATE pqp_ext_cross_person_records
SET processing_status = 'U'
,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'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'E' -- Error
,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 = 'M'
-- Bugfix 3671727:ENH1 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
SELECT loc.location_code
,loc.location_id
,lei.lei_information6 lea_number
FROM hr_organization_units_v org
,hr_locations_all loc
,hr_location_extra_info lei
WHERE org.organization_id = p_organization_id
AND loc.location_id = org.location_id
AND lei.location_id(+) = loc.location_id
AND nvl(lei.information_type,'PQP_GB_EDU_ESTB_INFO') = 'PQP_GB_EDU_ESTB_INFO';
SELECT name
FROM per_business_groups_perf
WHERE business_group_id = p_business_group_id;
g_lea_business_groups.DELETE;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'E' -- Error
,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 = 'M'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'E' -- Error
,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 = 'M'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'C' -- Completed
,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 = 'M'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'U' -- Back to Unprocessed
,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'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;
UPDATE pqp_ext_cross_person_records
SET processing_status = 'E' -- Error
,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 = 'M'
-- Bugfix 3671727:ENH1:ENH2 : Added these AND clauses
AND ext_dfn_id = p_ext_dfn_id
AND lea_number = g_lea_number;