[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT petr.element_type_id
FROM pay_element_type_rules petr
WHERE petr.element_set_id = c_element_set_id
AND petr.include_or_exclude = 'I'
UNION ALL
SELECT DISTINCT pet1.element_type_id
FROM pay_element_types_f pet1
WHERE pet1.classification_id IN
(SELECT classification_id
FROM pay_ele_classification_rules
WHERE element_set_id = c_element_set_id)
MINUS
SELECT DISTINCT petr.element_type_id
FROM pay_element_type_rules petr
WHERE petr.element_set_id = c_element_set_id
AND petr.include_or_exclude = 'E';
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.NAME = c_input_name
AND piv.element_type_id = c_element_type_id
AND (piv.business_group_id = c_business_group_id OR
piv.legislation_code = c_legislation_code)
AND c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT pbg.legislation_code
,pbg.currency_code
FROM per_business_groups_perf pbg
WHERE pbg.business_group_id = c_business_group_id;
SELECT paf.person_id
,paf.organization_id
,paf.assignment_type
,paf.effective_start_date
,paf.effective_end_date
,'NO'
,ast.user_status
,Hr_General.decode_lookup
('EMP_CAT',
paf.employment_category) employment_category
,paf.normal_hours
,pps.date_start
,pps.actual_termination_date
,paf.payroll_id
,'PPG_CODE'
,'PAY_MODE'
FROM per_all_assignments_f paf,
per_periods_of_service pps,
per_assignment_status_types ast
WHERE paf.assignment_id = c_assignment_id
AND pps.period_of_service_id = paf.period_of_service_id
AND ast.assignment_status_type_id = paf.assignment_status_type_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.business_group_id = c_business_group_id;
SELECT pbd.balance_dimension_id
FROM pay_balance_dimensions pbd
WHERE pbd.dimension_name = c_dimension_name
AND (pbd.business_group_id = c_bg_id OR
pbd.legislation_code = c_leg_code);
SELECT db.defined_balance_id
FROM pay_balance_types pbt
,pay_defined_balances db
,pay_balance_dimensions bd
WHERE pbt.balance_name = c_balance_name
AND pbt.balance_type_id = db.balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (pbt.business_group_id = c_business_group_id OR
pbt.legislation_code = g_legislation_code)
AND (db.business_group_id = pbt.business_group_id OR
db.legislation_code = g_legislation_code);
SELECT db.defined_balance_id
FROM pay_defined_balances db
WHERE db.balance_type_id = c_balance_type_id
AND db.balance_dimension_id = c_balance_dimension_id
AND (db.business_group_id = c_business_group_id OR
db.legislation_code = g_legislation_code);
SELECT paa.assignment_action_id
,ppa.effective_date
,ppa.action_type
,paa.tax_unit_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_classifications pac
WHERE paa.assignment_id = c_assignment_id
AND paa.tax_unit_id = nvl(c_gre_id,paa.tax_unit_id)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = Nvl(c_con_set_id,ppa.consolidation_set_id)
AND ppa.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED'
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date
AND (
( nvl(paa.run_type_id,
ppa.run_type_id) IS NULL
AND paa.source_action_id IS NULL
)
OR
( nvl(paa.run_type_id,
ppa.run_type_id) IS NOT NULL
AND paa.source_action_id IS NOT NULL
)
OR
( ppa.action_type = 'V'
AND ppa.run_type_id IS NULL
AND paa.run_type_id IS NOT NULL
AND paa.source_action_id IS NULL
)
)
ORDER BY ppa.effective_date;
SELECT 'X'
FROM pay_run_results prr
WHERE prr.assignment_action_id = c_asg_action_id
AND prr.element_type_id = c_element_type_id;
SELECT pev.screen_entry_value
FROM pay_input_values_f piv
,pay_element_entry_values_f pev
,pay_element_entries_f pee
,pay_element_links_f pel
WHERE c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND c_effective_date BETWEEN pev.effective_start_date
AND pev.effective_end_date
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
AND pev.input_value_id = piv.input_value_id
AND pev.element_entry_id = pee.element_entry_id
AND pee.element_link_id = pel.element_link_id
AND piv.element_type_id = pel.element_type_id
AND pel.element_type_id = c_element_type_id
AND pee.assignment_id = c_assignment_id
AND piv.input_value_id = c_input_value_id;
SELECT prv.result_value
FROM pay_run_results prr
,pay_run_result_values prv
WHERE prr.assignment_action_id = c_asg_action_id
AND prr.element_type_id = c_element_type_id
AND prv.input_value_id = c_input_value_id
AND prv.run_result_id = prr.run_result_id;
SELECT rcd.ext_rcd_id
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id -- The extract executing currently
AND rin.ext_file_id = dfn.ext_file_id
AND rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = c_rcd_type_cd; -- D=Detail,H=Header,F=Footer
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND dtl.person_id = c_person_id
AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
SELECT pbt.balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND (pbt.business_group_id = c_business_group_id
OR
pbt.legislation_code = c_legislation_code);
SELECT pet.element_type_id
,piv.input_value_id
FROM pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = c_element_name
AND piv.NAME = c_input_name
AND (pet.business_group_id = c_business_group_id OR
pet.legislation_code = c_legislation_code)
AND (piv.business_group_id = c_business_group_id OR
piv.legislation_code = c_legislation_code)
AND piv.element_type_id = pet.element_type_id
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT asg.person_id
,asg.organization_id
,asg.assignment_type
,asg.effective_start_date
,asg.effective_end_date
,'NO'
,asg.assignment_id
FROM per_all_assignments_f asg
WHERE asg.person_id = c_person_id
AND asg.assignment_id <> c_primary_assignment_id
AND asg.assignment_type ='E'
AND (( c_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
)
OR
( asg.effective_end_date =
(SELECT Max(asx.effective_end_date)
FROM per_all_assignments_f asx
WHERE asx.assignment_id = asg.assignment_id
AND asx.person_id = c_person_id
AND asx.assignment_type = 'E'
AND ((asx.effective_end_date BETWEEN c_extract_start_date
AND c_extract_end_date)
OR
(asx.effective_start_date BETWEEN c_extract_start_date
AND c_extract_end_date)
)
)
)
)
ORDER BY asg.effective_start_date ASC;
SELECT pet.element_information_category -- Information Category
,pet.element_information1 -- PreTax Category
,pet.element_information10 -- Primary Balance Id
,pet.element_name -- Element Name
FROM pay_element_types_f pet
WHERE pet.element_type_id = c_element_type_id
AND (pet.business_group_id = c_business_group_id OR
pet.legislation_code = c_legislation_code)
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.element_information10 IS NOT NULL;
SELECT pbt.balance_name
FROM pay_balance_types pbt
WHERE pbt.balance_type_id = c_balance_type_id;
SELECT 'x'
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND business_group_id = g_business_group_id
AND (chg_eff_dt BETWEEN c_ext_start_date
AND c_ext_end_date
OR
chg_actl_dt BETWEEN c_ext_start_date
AND c_ext_end_date);
SELECT paf.person_id
,paf.grade_id
,paf.job_id
,paf.location_id
,paf.assignment_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = c_assignment_id
AND paf.business_group_id = g_business_group_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT paf.person_type_id
,ppt.system_person_type
,pps.actual_termination_date
,pps.date_start
,paf.original_date_of_hire
FROM per_all_people_f paf
,per_person_types ppt
,per_periods_of_service pps
WHERE paf.person_id = c_person_id
AND ppt.person_type_id = paf.person_type_id
AND pps.business_group_id = g_business_group_id
AND paf.business_group_id = g_business_group_id
AND pps.person_id = paf.person_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND c_effective_date BETWEEN pps.date_start
AND Nvl(pps.actual_termination_date,
To_Date('31/12/4712','DD/MM/YYYY'));
SELECT request_id
FROM ben_ext_rslt
WHERE ext_rslt_id = c_ext_rslt_id
AND ext_dfn_id = c_ext_dfn_id
AND business_group_id = c_business_group_id;
SELECT ben.pgm_id
,ben.pl_id
,ben.benefit_action_id
,ben.business_group_id
,ben.process_date
,ben.request_id
FROM ben_benefit_actions ben
WHERE ben.pl_id = c_ext_rslt_id
AND ben.pgm_id = c_ext_dfn_id
AND ben.business_group_id = c_business_group_id;
,p_selection_criteria IN VARCHAR2
,p_is_element_set IN VARCHAR2
,p_element_set_id IN NUMBER
,p_is_element IN NUMBER
,p_is_ext_dfn_type IN VARCHAR2
,p_element_type_id IN NUMBER
,p_report_dfn_typ_id IN VARCHAR2
,p_start_date IN VARCHAR2
,p_end_date IN VARCHAR2
,p_gre_id IN NUMBER
,p_payroll_id IN NUMBER
,p_con_ext_dfn_typ_id IN VARCHAR2
,p_con_is_fullprofile_data_typ IN VARCHAR2
,p_con_set IN NUMBER
,p_business_group_id IN NUMBER
,p_ext_rslt_id IN NUMBER DEFAULT NULL ) IS
l_errbuff VARCHAR2(3000);
SELECT Userenv('SESSIONID') INTO l_session_id FROM dual;
DELETE FROM pay_us_rpt_totals
WHERE organization_name = 'US Pension Extracts'
AND attribute30 = 'EXTRACT_COMPLETED'
AND organization_id = p_business_group_id
AND business_group_id = p_business_group_id
AND location_id = p_ext_dfn_id;
INSERT INTO pay_us_rpt_totals
(session_id -- session id
,organization_name -- Conc. Program Name
,organization_id -- business group id
,business_group_id -- -do-
,location_id -- ext dfn id used for perf.
,tax_unit_id -- concurrent request id
,value1 -- extract def. id
,value2 -- element set id
,value3 -- element type id
,value4 -- Payroll Id
,value5 -- GRE Org Id
,value6 -- Consolidation set id
,attribute1 -- Selection Criteria
,attribute2 -- Reporting dimension
,attribute3 -- Extract Start Date
,attribute4 -- Extract End Date
,attribute30 -- Status
)
VALUES
(l_session_id
,'US Pension Extracts'
,p_business_group_id
,p_business_group_id
,p_ext_dfn_id
,g_conc_request_id
,p_ext_dfn_id
,p_element_set_id
,p_element_type_id
,p_payroll_id
,p_gre_id
,p_con_set
,p_selection_criteria
,p_reporting_dimension
,p_start_date
,p_end_date
,'EXTRACT_RUNNING'
);
UPDATE pay_us_rpt_totals
SET attribute30 = 'EXTRACT_COMPLETED'
WHERE organization_name = 'US Pension Extracts'
AND tax_unit_id = g_conc_request_id
AND organization_id = p_business_group_id
AND business_group_id = p_business_group_id
AND location_id = p_ext_dfn_id;
UPDATE pay_us_rpt_totals
SET attribute30 = 'EXTRACT_COMPLETED'
WHERE organization_name = 'US Pension Extracts'
AND tax_unit_id = g_conc_request_id
AND organization_id = p_business_group_id
AND business_group_id = p_business_group_id
AND location_id = p_ext_dfn_id;
SELECT pev.screen_entry_value
,pee.effective_start_date
FROM pay_input_values_f piv
,pay_element_entry_values_f pev
,pay_element_entries_f pee
,pay_element_links_f pel
WHERE c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND c_effective_date BETWEEN pev.effective_start_date
AND pev.effective_end_date
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
AND pev.input_value_id = piv.input_value_id
AND pev.element_entry_id = pee.element_entry_id
AND pee.element_link_id = pel.element_link_id
AND piv.element_type_id = pel.element_type_id
AND pel.element_type_id = c_element_type_id
AND pee.assignment_id = c_assignment_id
AND piv.input_value_id = c_input_value_id;
SELECT pei.person_extra_info_id
FROM per_people_extra_info pei
WHERE pei.person_id = c_person_id
AND pei.information_type = c_information_type;
SELECT aei.assignment_extra_info_id
FROM per_assignment_extra_info aei
WHERE aei.assignment_id = c_assignment_id
AND aei.information_type = c_information_type;
SELECT multiple_occurences_flag
FROM per_assignment_info_types
WHERE information_type = c_information_type
AND active_inactive_flag = 'Y';
SELECT multiple_occurences_flag
FROM per_people_info_types
WHERE information_type = c_information_type
AND active_inactive_flag = 'Y';
SELECT id_flex_structure_code
,id_flex_num
FROM fnd_id_flex_structures_vl
WHERE application_id = 800
AND id_flex_code = 'PEA'
AND id_flex_structure_code = c_structure_code;
SELECT *
FROM per_person_analyses ppa
WHERE ppa.business_group_id = c_business_group_id
AND ppa.person_id = c_person_id
AND ppa.id_flex_num = c_id_flex_num
AND c_effective_date BETWEEN nvl(ppa.date_from,c_effective_date)
AND nvl(ppa.date_to,c_effective_date);
SELECT *
FROM per_analysis_criteria
WHERE analysis_criteria_id = c_anal_criteria_id
AND id_flex_num = c_flex_num
AND c_effective_date BETWEEN NVL(start_date_active,c_effective_date)
AND NVL(end_date_active,c_effective_date);
SELECT pae.aei_information1 -- PPG Code
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = c_assignment_id
AND pae.information_type = 'PQP_US_TIAA_CREF_CODES';
SELECT prl.prl_information7 -- PPG Code
FROM pay_payrolls_f prl
WHERE prl.payroll_id = c_payroll_id
AND prl.prl_information_category = 'US'
AND c_effective_date BETWEEN prl.effective_start_date
AND prl.effective_end_date;
SELECT org_information1 --PPG CODE
FROM hr_organization_information
WHERE org_information_context = 'PQP_US_TIAA_CREF_CODES'
AND organization_id = c_tax_unit_id;
SELECT segment1
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hsc
WHERE hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND paf.assignment_id = c_assignment_id
AND paf.business_group_id = g_business_group_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT prl.prl_information4 -- Payment Mode
FROM pay_payrolls_f prl
WHERE prl.payroll_id = c_payroll_id
AND prl.prl_information_category = 'US'
AND c_effective_date BETWEEN prl.effective_start_date
AND prl.effective_end_date;
SELECT eei_information4
,eei_information6
FROM pay_element_type_extra_info
WHERE information_type = c_information_type
AND element_type_id = c_element_type_id;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = c_element_name
AND (pet.business_group_id = c_business_group_id OR
pet.legislation_code = c_legislation_code)
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
PROCEDURE Update_Record_Values
(p_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
,p_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
,p_data_element_value IN ben_ext_rslt_dtl.val_01%TYPE
,p_data_ele_seqnum IN NUMBER
,p_ext_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
) IS
CURSOR csr_seqnum (c_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
,c_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
) IS
SELECT der.ext_data_elmt_id,
der.seq_num,
ede.NAME
FROM ben_ext_data_elmt_in_rcd der
,ben_ext_data_elmt ede
WHERE der.ext_rcd_id = c_ext_rcd_id
AND ede.ext_data_elmt_id = der.ext_data_elmt_id
AND ede.NAME LIKE '%'|| c_ext_data_element_name
ORDER BY seq_num;
l_proc_name VARCHAR2(150):= g_proc_name||'Update_Record_Values';
END Update_Record_Values;
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_val_tab(1)
,p_val_tab(2)
,p_val_tab(3)
,p_val_tab(4)
,p_val_tab(5)
,p_val_tab(6)
,p_val_tab(7)
,p_val_tab(8)
,p_val_tab(9)
,p_val_tab(10)
,p_val_tab(11)
,p_val_tab(12)
,p_val_tab(13)
,p_val_tab(14)
,p_val_tab(15)
,p_val_tab(16)
,p_val_tab(17)
,p_val_tab(19)
,p_val_tab(18)
,p_val_tab(20)
,p_val_tab(21)
,p_val_tab(22)
,p_val_tab(23)
,p_val_tab(24)
,p_val_tab(25)
,p_val_tab(26)
,p_val_tab(27)
,p_val_tab(28)
,p_val_tab(29)
,p_val_tab(30)
,p_val_tab(31)
,p_val_tab(32)
,p_val_tab(33)
,p_val_tab(34)
,p_val_tab(35)
,p_val_tab(36)
,p_val_tab(37)
,p_val_tab(38)
,p_val_tab(39)
,p_val_tab(40)
,p_val_tab(41)
,p_val_tab(42)
,p_val_tab(43)
,p_val_tab(44)
,p_val_tab(45)
,p_val_tab(46)
,p_val_tab(47)
,p_val_tab(48)
,p_val_tab(49)
,p_val_tab(50)
,p_val_tab(51)
,p_val_tab(52)
,p_val_tab(53)
,p_val_tab(54)
,p_val_tab(55)
,p_val_tab(56)
,p_val_tab(57)
,p_val_tab(58)
,p_val_tab(59)
,p_val_tab(60)
,p_val_tab(61)
,p_val_tab(62)
,p_val_tab(63)
,p_val_tab(64)
,p_val_tab(65)
,p_val_tab(66)
,p_val_tab(67)
,p_val_tab(68)
,p_val_tab(69)
,p_val_tab(70)
,p_val_tab(71)
,p_val_tab(72)
,p_val_tab(73)
,p_val_tab(74)
,p_val_tab(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
);
UPDATE ben_ext_rslt_dtl
SET VAL_01 = p_val_tab(1)
,VAL_02 = p_val_tab(2)
,VAL_03 = p_val_tab(3)
,VAL_04 = p_val_tab(4)
,VAL_05 = p_val_tab(5)
,VAL_06 = p_val_tab(6)
,VAL_07 = p_val_tab(7)
,VAL_08 = p_val_tab(8)
,VAL_09 = p_val_tab(9)
,VAL_10 = p_val_tab(10)
,VAL_11 = p_val_tab(11)
,VAL_12 = p_val_tab(12)
,VAL_13 = p_val_tab(13)
,VAL_14 = p_val_tab(14)
,VAL_15 = p_val_tab(15)
,VAL_16 = p_val_tab(16)
,VAL_17 = p_val_tab(17)
,VAL_19 = p_val_tab(19)
,VAL_18 = p_val_tab(18)
,VAL_20 = p_val_tab(20)
,VAL_21 = p_val_tab(21)
,VAL_22 = p_val_tab(22)
,VAL_23 = p_val_tab(23)
,VAL_24 = p_val_tab(24)
,VAL_25 = p_val_tab(25)
,VAL_26 = p_val_tab(26)
,VAL_27 = p_val_tab(27)
,VAL_28 = p_val_tab(28)
,VAL_29 = p_val_tab(29)
,VAL_30 = p_val_tab(30)
,VAL_31 = p_val_tab(31)
,VAL_32 = p_val_tab(32)
,VAL_33 = p_val_tab(33)
,VAL_34 = p_val_tab(34)
,VAL_35 = p_val_tab(35)
,VAL_36 = p_val_tab(36)
,VAL_37 = p_val_tab(37)
,VAL_38 = p_val_tab(38)
,VAL_39 = p_val_tab(39)
,VAL_40 = p_val_tab(40)
,VAL_41 = p_val_tab(41)
,VAL_42 = p_val_tab(42)
,VAL_43 = p_val_tab(43)
,VAL_44 = p_val_tab(44)
,VAL_45 = p_val_tab(45)
,VAL_46 = p_val_tab(46)
,VAL_47 = p_val_tab(47)
,VAL_48 = p_val_tab(48)
,VAL_49 = p_val_tab(49)
,VAL_50 = p_val_tab(50)
,VAL_51 = p_val_tab(51)
,VAL_52 = p_val_tab(52)
,VAL_53 = p_val_tab(53)
,VAL_54 = p_val_tab(54)
,VAL_55 = p_val_tab(55)
,VAL_56 = p_val_tab(56)
,VAL_57 = p_val_tab(57)
,VAL_58 = p_val_tab(58)
,VAL_59 = p_val_tab(59)
,VAL_60 = p_val_tab(60)
,VAL_61 = p_val_tab(61)
,VAL_62 = p_val_tab(62)
,VAL_63 = p_val_tab(63)
,VAL_64 = p_val_tab(64)
,VAL_65 = p_val_tab(65)
,VAL_66 = p_val_tab(66)
,VAL_67 = p_val_tab(67)
,VAL_68 = p_val_tab(68)
,VAL_69 = p_val_tab(69)
,VAL_70 = p_val_tab(70)
,VAL_71 = p_val_tab(71)
,VAL_72 = p_val_tab(72)
,VAL_73 = p_val_tab(73)
,VAL_74 = p_val_tab(74)
,VAL_75 = p_val_tab(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 count(*) FROM ben_ext_rslt_err
WHERE ext_rslt_id = ben_extract.g_ext_rslt_id
AND typ_cd <> 'W';
SELECT xwc.oper_cd,
xwc.val,
xwc.and_or_cd,
xer.seq_num,
xrc.NAME,
Substr(xel.frmt_mask_cd,1,1) xel_frmt_mask_cd,
xel.data_elmt_typ_cd,
xel.data_elmt_rl,
xel.ext_fld_id,
fld.frmt_mask_typ_cd
FROM ben_ext_where_clause xwc,
ben_ext_data_elmt_in_rcd xer,
ben_ext_rcd xrc,
ben_ext_data_elmt xel,
ben_ext_fld fld
WHERE xwc.ext_rcd_in_file_id = p_ext_rcd_in_file_id
AND xwc.cond_ext_data_elmt_in_rcd_id = xer.ext_data_elmt_in_rcd_id
AND xer.ext_rcd_id = xrc.ext_rcd_id
AND xel.ext_data_elmt_id = xer.ext_data_elmt_id
AND xel.ext_fld_id = fld.ext_fld_id(+)
ORDER BY xwc.seq_num;
SELECT xer.seq_num,
xer.sprs_cd,
xer.ext_data_elmt_in_rcd_id,
xdm.NAME
FROM ben_ext_data_elmt_in_rcd xer,
ben_ext_data_elmt xdm
WHERE ext_rcd_id = p_ext_rcd_id
AND xer.sprs_cd IS NOT NULL
AND xer.ext_data_elmt_id = xdm.ext_data_elmt_id ;
SELECT xwc.oper_cd,
xwc.val,
xwc.and_or_cd,
xer.seq_num
FROM ben_ext_where_clause xwc,
ben_ext_data_elmt_in_rcd xer
WHERE xwc.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
AND xwc.cond_ext_data_elmt_in_rcd_id = xer.ext_data_elmt_in_rcd_id
ORDER BY xwc.seq_num;
SELECT *
FROM ben_person_actions bpa
WHERE bpa.benefit_action_id = c_benefit_action_id
AND bpa.person_id = c_person_id;
SELECT 'x'
FROM ben_batch_ranges
WHERE benefit_action_id = c_benefit_action_id
AND c_person_action_id BETWEEN starting_person_action_id
AND ending_person_action_id;
UPDATE ben_person_actions bpa
SET bpa.action_status_cd = 'U'
WHERE bpa.benefit_action_id = p_benefit_action_id
AND bpa.person_id = p_person_id;
UPDATE ben_batch_ranges bbr
SET bbr.range_status_cd = 'E'
WHERE bbr.benefit_action_id = p_benefit_action_id
AND l_ben_per.person_action_id
BETWEEN bbr.starting_person_action_id
AND bbr.ending_person_action_id;
SELECT a.ext_data_elmt_in_rcd_id
,a.seq_num
,a.sprs_cd
,a.strt_pos
,a.dlmtr_val
,a.rqd_flag
,b.ext_data_elmt_id
,b.data_elmt_typ_cd
,b.data_elmt_rl
,b.NAME
,Hr_General.decode_lookup('BEN_EXT_FRMT_MASK',
b.frmt_mask_cd) frmt_mask_cd
,b.frmt_mask_cd frmt_mask_lookup_cd
,b.string_val
,b.dflt_val
,b.max_length_num
,b.just_cd
FROM ben_ext_data_elmt b,
ben_ext_data_elmt_in_rcd a
WHERE a.ext_data_elmt_id = b.ext_data_elmt_id
AND b.data_elmt_typ_cd = 'R'
AND a.ext_rcd_id = c_ext_rcd_id
ORDER BY a.seq_num;
SELECT formula_type_id
FROM ff_formulas_f
WHERE formula_id = c_formula_type_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT rif.ext_rcd_in_file_id
,rif.any_or_all_cd
,rif.seq_num
,rif.sprs_cd
,rif.rqd_flag
FROM ben_ext_rcd_in_file rif
,ben_ext_dfn dfn
WHERE rif.ext_file_id = dfn.ext_file_id
AND rif.ext_rcd_id = c_rcd_id
AND dfn.ext_dfn_id = c_ext_dfn_id;
Update_Record_Values
(p_ext_rcd_id => p_ext_dtl_rcd_id
,p_ext_data_element_name => NULL
,p_data_element_value => l_ff_value
,p_data_ele_seqnum => i.seq_num
,p_ext_dtl_rec => p_rslt_rec);
SELECT 'X'
FROM pay_element_entries_f pee
,pay_element_links_f pel
WHERE 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
AND pee.element_link_id = pel.element_link_id
AND pel.element_type_id = c_element_type_id
AND pee.assignment_id = c_assignment_id;
DELETE ben_ext_rslt_dtl
WHERE ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
AND person_id = l_person_id;
g_primary_assig.DELETE(l_assignment_id);
SELECT ppb.pay_annualization_factor
FROM per_all_assignments_f paf
,per_pay_bases ppb
WHERE assignment_id = c_assignment_id
AND paf.pay_basis_id = ppb.pay_basis_id
AND ppb.business_group_id = c_business_group_id
AND paf.business_group_id = ppb.business_group_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT ppp.proposed_salary_n
FROM per_pay_proposals ppp
WHERE ppp.assignment_id = c_assignment_id
AND ppp.business_group_id = c_business_group_id
AND ppp.change_date = (SELECT MAX(ppx.change_date)
FROM per_pay_proposals ppx
WHERE ppx.assignment_id = ppp.assignment_id
AND ppx.business_group_id = ppp.business_group_id
AND ppx.change_date <= c_effective_date
AND ppx.approved = 'Y');
ELSIF p_header_type = 'SELECTION_CRITERIA' THEN
l_return_value := g_conc_prog_details(0).selection_criteria;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_classifications pac
WHERE paa.assignment_id = c_assignment_id
AND paa.tax_unit_id = nvl(c_gre_id,paa.tax_unit_id)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = Nvl(c_con_set_id,ppa.consolidation_set_id)
AND ppa.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED'
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date
AND (
( nvl(paa.run_type_id,
ppa.run_type_id) IS NULL
AND paa.source_action_id IS NULL
)
OR
( nvl(paa.run_type_id,
ppa.run_type_id) IS NOT NULL
AND paa.source_action_id IS NOT NULL
)
OR
( ppa.action_type = 'V'
AND ppa.run_type_id IS NULL
AND paa.run_type_id IS NOT NULL
AND paa.source_action_id IS NULL
)
)
ORDER BY paa.action_sequence DESC;
,p_selection_criteria IN VARCHAR2
,p_element_set_id IN NUMBER
,p_element_type_id IN NUMBER
,p_start_date IN VARCHAR2
,p_end_date IN VARCHAR2
,p_gre_id IN NUMBER
,p_payroll_id IN NUMBER
,p_con_set IN NUMBER
) IS
CURSOR csr_ext_name(c_ext_dfn_id IN NUMBER
)IS
SELECT Substr(ed.NAME,1,240)
FROM ben_ext_dfn ed
WHERE ed.ext_dfn_id = p_ext_dfn_id;
SELECT element_set_name
FROM pay_element_sets
WHERE element_set_id = c_element_set_id
AND element_set_type = 'C';
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = c_element_type_id
AND c_end_date BETWEEN effective_start_date
AND effective_end_date;
SELECT hou.NAME
FROM hr_organization_units hou
WHERE hou.organization_id = c_gre_id;
SELECT pay.payroll_name
FROM pay_payrolls_f pay
WHERE pay.payroll_id = c_payroll_id
AND c_end_date BETWEEN pay.effective_start_date
AND pay.effective_end_date;
SELECT con.consolidation_set_name
FROM pay_consolidation_sets con
WHERE con.consolidation_set_id = c_con_set;
g_conc_prog_details(0).selection_criteria := Hr_General.DECODE_LOOKUP
('REPORT_SELECT_SORT_CODE',
p_selection_criteria);
SELECT ext_dfn_type
FROM pqp_extract_attributes
WHERE ext_dfn_id = c_ext_dfn_id;
SELECT session_id -- session id
,business_group_id -- business group id
,tax_unit_id -- concurrent request id
,value1 -- extract def. id
,value2 -- element set id
,value3 -- element type id
,value4 -- Payroll Id
,value5 -- GRE Org Id
,value6 -- Consolidation set id
,attribute1 -- Selection Criteria
,attribute2 -- Reporting dimension
,attribute3 -- Extract Start Date
,attribute4 -- Extract End Date
FROM pay_us_rpt_totals
WHERE tax_unit_id = c_request_id
AND value1 = c_ext_dfn_id
AND organization_id = c_business_group_id
AND business_group_id = c_business_group_id
AND location_id = c_ext_dfn_id;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE legislation_code = c_legislation_code
AND dimension_name = 'Assignment-Level Current Run';
SELECT 'X'
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hcf
WHERE paf.assignment_id = c_assignment_id
AND paf.business_group_id = c_business_group_id
AND hcf.soft_coding_keyflex_id(+) = paf.soft_coding_keyflex_id
AND (c_gre_id IS NULL OR
hcf.segment1= Nvl(Fnd_Number.NUMBER_to_canonical(c_gre_id),
hcf.segment1)
)
AND (c_payroll_id IS NULL OR
paf.payroll_id = c_payroll_id)
AND (c_end_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
OR
paf.effective_end_date BETWEEN c_start_date
AND c_end_date);
SELECT 'X'
FROM pay_element_entries_f pee
,pay_element_links_f pel
WHERE (c_end_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
OR
pee.effective_end_date BETWEEN c_start_date
AND c_end_date
)
AND pee.effective_end_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND pel.element_type_id = c_ele_type_id
AND pee.assignment_id = c_assignment_id
AND pel.business_group_id = c_business_group_id;
SELECT 'x'
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hcf
WHERE paf.assignment_id = c_assignment_id
AND hcf.soft_coding_keyflex_id(+) = paf.soft_coding_keyflex_id
AND (c_tax_unit_id IS NULL OR
hcf.segment1 = Nvl(Fnd_Number.NUMBER_to_canonical(c_tax_unit_id), hcf.segment1)
)
AND (c_payroll_id IS NULL
OR paf.payroll_id = Nvl(c_payroll_id,paf.payroll_id)
)
AND (c_end_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
OR
paf.effective_end_date BETWEEN c_start_date
AND c_end_date);
g_extract_params(i).selection_criteria := l_ext_params.attribute1;
,p_selection_criteria => g_extract_params(i).selection_criteria
,p_element_set_id => g_extract_params(i).element_set_id
,p_element_type_id => g_extract_params(i).element_type_id
,p_start_date => g_extract_params(i).extract_start_date
,p_end_date => g_extract_params(i).extract_end_date
,p_gre_id => g_extract_params(i).gre_org_id
,p_payroll_id => g_extract_params(i).payroll_id
,p_con_set => g_extract_params(i).con_set_id
);
SELECT err.person_id
,err.typ_cd
,err.ext_rslt_id
FROM ben_ext_rslt_err err
WHERE err.business_group_id = c_bg_id
AND err.typ_cd = 'E'
AND err.ext_rslt_id = c_ext_rslt_id;
DELETE
FROM ben_ext_rslt_dtl
WHERE ext_rcd_id = csr_rcd_rec.ext_rcd_id
AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND business_group_id = p_business_group_id;
DELETE
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND dtl.person_id = err_rec.person_id
AND dtl.business_group_id = p_business_group_id;
SELECT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND ROWNUM < 2;