[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 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
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 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
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
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.effective_date BETWEEN c_start_date
AND c_end_date
ORDER BY ppa.effective_date;
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 (c_gre_id is null or
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;
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 (c_gre_id is null or
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;
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 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 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
,asg.primary_flag
,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 '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 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;
SELECT legislation_code
FROM per_business_groups_perf
WHERE 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 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_org_id IN NUMBER
,p_person_type_id IN NUMBER
,p_location_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 = 'Global Pension Extracts'
AND attribute30 = 'EXTRACT_COMPLETED'
AND business_group_id = p_business_group_id
AND organization_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
,business_group_id -- business group id
,organization_id -- -do-
,location_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
,attribute5 -- Organization Name
,attribute6 -- Person Type
,attribute7 -- Location
,attribute30 -- Status
)
VALUES
(l_session_id
,'Global 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
,p_org_id
,p_person_type_id
,p_location_id
,'EXTRACT_RUNNING'
);
UPDATE pay_us_rpt_totals
SET attribute30 = 'EXTRACT_COMPLETED'
WHERE organization_name = 'Global Pension Extracts'
AND tax_unit_id = g_conc_request_id
AND business_group_id = p_business_group_id
AND organization_id = p_business_group_id
AND location_id = p_ext_dfn_id;
UPDATE pay_us_rpt_totals
SET attribute30 = 'EXTRACT_COMPLETED'
WHERE organization_name = 'Global Pension Extracts'
AND tax_unit_id = g_conc_request_id
AND business_group_id = p_business_group_id
AND organization_id = p_business_group_id
AND location_id = p_ext_dfn_id;
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 ;
SELECT pbt.balance_name
FROM pay_balance_types pbt
WHERE pbt.balance_type_id = c_balance_type_id;
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);
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);
g_mx_rules.DELETE(l_assignment_id);
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 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 to_number(nvl(segment1,0))
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hfc
WHERE paf.assignment_id = c_assignment_id
AND paf.business_group_id = c_business_group_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.soft_coding_keyflex_id = hfc.soft_coding_keyflex_id;
SELECT org_information_id
FROM hr_organization_information
WHERE organization_id = c_organization_id
AND org_information_context = c_flex_context;
SELECT paf.person_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = c_assignment_id
AND paf.business_group_id = c_business_group_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
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 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;
,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
,p_org_id IN NUMBER
,p_person_type_id IN NUMBER
,p_location_id 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;
SELECT NAME
FROM hr_all_organization_units
WHERE organization_id =c_org_id;
SELECT user_person_type
FROM per_person_types
WHERE person_type_id = c_person_type_id;
SELECT location_code
FROM hr_locations_all
WHERE location_id = c_location_id;
hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
g_conc_prog_details(0).selection_criteria := HR_GENERAL.DECODE_LOOKUP
('REPORT_SELECT_SORT_CODE',
p_selection_criteria);
SELECT paf.person_type_id
,paa.location_id
,paa.organization_id
FROM per_all_people_f paf
,per_all_assignments_f paa
WHERE paf.person_id = c_person_id
AND paa.person_id = paf.person_id
AND paa.assignment_type = 'E'
AND paa.business_group_id = paf.business_group_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND c_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
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
,attribute5 -- Organization Name
,attribute6 -- Person Type
,attribute7 -- Location
FROM pay_us_rpt_totals
WHERE tax_unit_id = c_request_id
AND value1 = c_ext_dfn_id
AND business_group_id = c_business_group_id
AND organization_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 = c_dimension_name;
SELECT pbg.legislation_code
,pbg.currency_code
FROM per_business_groups_perf pbg
WHERE pbg.business_group_id = c_business_group_id;
SELECT 'X'
FROM per_all_assignments_f paf
WHERE paf.assignment_id = c_assignment_id
AND paf.business_group_id = c_business_group_id
AND (c_org_id IS NULL OR
paf.organization_id = c_org_id)
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
WHERE paf.assignment_id = c_assignment_id
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
,p_org_id => g_extract_params(i).org_id
,p_person_type_id => g_extract_params(i).person_type_id
,p_location_id => g_extract_params(i).location_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;