[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Decode(rin.seq_num,1,'000',
2,'010',
3,'020',
4,'030',
5,'040',
6,'060',
7,'070',
8,'080',
9,'081',
12,'999',
'~') rec_num,
rin.seq_num,
rin.hide_flag,
rcd.ext_rcd_id,
rcd.rcd_type_cd
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
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
ORDER BY rin.seq_num;
SELECT paf.person_id
,paf.organization_id
,paf.assignment_type
,paf.effective_start_date
,paf.effective_end_date
,ast.user_status
,Hr_General.decode_lookup
('EMP_CAT',
paf.employment_category) employment_category
,pps.date_start
,pps.actual_termination_date
,paf.payroll_id
,'ER'
,per.employee_number
,paf.assignment_sequence
,per.national_identifier
,per.last_name
,per.per_information1
,per.pre_name_adjunct
,per.sex
,per.date_of_birth
,'PLN'
,'PIX'
,per.per_information14
,per.marital_status
,paf.primary_flag
FROM per_all_assignments_f paf,
per_all_people_f per,
per_periods_of_service pps,
per_assignment_status_types ast
WHERE paf.assignment_id = c_assignment_id
AND paf.person_id = per.person_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 c_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND paf.business_group_id = c_business_group_id
AND per.business_group_id = c_business_group_id;
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 'x'
FROM per_all_assignments_f
WHERE assignment_id = c_assignment_id
AND primary_flag= 'Y'
AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT paf.organization_id
,paf.payroll_id
FROM per_all_assignments_f paf,
per_all_people_f per
WHERE paf.assignment_id <> c_assignment_id
AND paf.person_id = c_person_id
AND paf.person_id = per.person_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND c_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND paf.business_group_id = c_business_group_id
AND per.business_group_id = c_business_group_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.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = c_seq;
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
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 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
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = c_rcd_type_cd
ORDER BY rin.seq_num;
SELECT NAME
FROM hr_all_organization_units
WHERE organization_id = c_org_id;
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 *
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 paa.assignment_action_id
,ppa.effective_date
,ppa.action_type
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.source_action_id IS NULL
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND ppa.action_type IN ('B','L','O','Q','R')
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 NVL(min(PTP.start_date),to_date('0101'||c_year,'DDMMYYYY'))
FROM per_time_periods PTP
,per_all_assignments_f PAA
WHERE PAA.assignment_id = c_assignment_id
AND PTP.payroll_id = PAA.payroll_id
AND (c_date_earned between PAA.effective_start_date and PAA.effective_end_date)
AND (substr(PTP.period_name,4,4)=c_year
OR substr(PTP.period_name,3,4)=c_year);
SELECT NVL(max(PTP.end_date),to_date('3112'||c_year,'DDMMYYYY'))
FROM per_time_periods PTP
, per_all_assignments_f PAA
WHERE PAA.assignment_id = c_assignment_id
AND PTP.payroll_id = PAA.payroll_id
AND (c_date_earned between PAA.effective_start_date and PAA.effective_end_date)
AND (substr(PTP.period_name,4,4)=c_year
OR substr(PTP.period_name,3,4)=c_year);
SELECT NVL(TPTYPE.number_per_fiscal_year,12)
FROM per_all_assignments_f PAA
, per_time_periods TPERIOD
, per_time_period_types TPTYPE
WHERE PAA.assignment_id = c_assignment_id
AND TPERIOD.payroll_id = PAA.payroll_id
AND ( c_date_earned between PAA.effective_start_date and PAA.effective_end_date)
AND ( c_date_earned between TPERIOD.start_date and TPERIOD.end_date )
AND TPTYPE.period_type = TPERIOD.period_type;
SELECT Substr(ed.NAME,1,240)
FROM ben_ext_dfn ed
WHERE ed.ext_dfn_id = p_ext_dfn_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 person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND ROWNUM < 2;
SELECT
decode(
trunc( to_number(PTP.period_num)/10),
0,substr(PTP.period_name,3,4),
1,substr(PTP.period_name,4,4)
)
FROM per_time_periods PTP
,per_all_assignments_f PAA
WHERE PAA.assignment_id = c_assignment_id
AND PTP.payroll_id = PAA.payroll_id
AND ( c_effective_date between PTP.start_date and PTP.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;
hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
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
);
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
,prmy_sort_val =p_dtl_rec.prmy_sort_val
WHERE ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
SELECT defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE pbt.balance_name =c_balance_name
AND pbd.legislation_code='NL'
AND pbd.DIMENSION_NAME=c_dimension_name
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT to_char(pee.effective_start_date,'YYYYMMDD')
FROM pay_element_types_f pet, pay_element_entries_f pee
WHERE pet.element_name like 'PGGM Pensions General Information'
AND pee.element_type_id =pet.element_type_id
AND pee.assignment_id=c_asg_id
AND pee.effective_start_date <= g_extract_params(p_business_group_id).extract_end_date;
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
-- AND a.hide_flag = 'N'
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 organization_id,business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND business_group_id = g_business_group_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
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;
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 hrl1.lookup_code
FROM per_addresses p_addr, hr_lookups hrl1
WHERE p_addr.person_id = g_person_id
AND hrl1.lookup_type='PQP_NL_STUCON_CODE_MAPPING'
AND hrl1.meaning = p_addr.country
AND g_extract_params(c_business_group_id).extract_end_date >= p_addr.date_from;
SELECT Userenv('SESSIONID') INTO l_session_id FROM dual;
DELETE FROM pay_us_rpt_totals
WHERE organization_name = 'NL PGGM Pension Extracts';
hr_utility.set_location('inserting into rpt totals : '||p_business_group_id,20);
INSERT INTO pay_us_rpt_totals
(session_id -- Session id
,organization_name -- Concurrent Program Name
,business_group_id -- Business Group
,tax_unit_id -- Concurrent Request Id
,value1 -- Extract Definition Id
,value2 -- Payroll Id
,value3 -- Consolidation Set
,value4 -- Organization Id
,value5 --
,value6 --
,attribute1 --
,attribute2 --
,attribute3 -- Extract Start Date
,attribute4 -- Extract End Date
,attribute5 -- Type of Extract
)
VALUES
(l_session_id
,'NL PGGM Pension Extracts'
,p_business_group_id
,g_conc_request_id
,p_ext_dfn_id
,p_payroll_id
,p_consolidation_set
,p_org_id
,NULL
,NULL
,NULL
,NULL
,p_start_date
,p_end_date
,p_extract_type
);
SELECT ext_dfn_type
FROM pqp_extract_attributes
WHERE ext_dfn_id = c_ext_dfn_id;
SELECT session_id -- Session id
,organization_name -- Concurrent Program Name
,business_group_id -- Business Group
,tax_unit_id -- Concurrent Request Id
,value1 -- Extract Definition Id
,value2 -- Payroll Id
,value3 -- Consolidation Set
,value4 -- Organization Id
,value5 --
,value6 --
,attribute1 --
,attribute2 --
,attribute3 -- Extract Start Date
,attribute4 -- Extract End Date
,attribute5 -- Type of Extract
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;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE legislation_code = 'NL'
AND database_item_suffix = '_ASG_RUN';
SELECT pbg.legislation_code
,pbg.currency_code
FROM per_business_groups_perf pbg
WHERE pbg.business_group_id = c_business_group_id;
SELECT pos.org_structure_version_id
FROM per_org_structure_versions_v pos,
hr_organization_information hoi
WHERE hoi.organization_id = p_business_group_id
AND To_Number(hoi.org_information1) = pos.organization_structure_id
AND Trunc(p_effective_date) BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND hoi.org_information_context = 'NL_BG_INFO';
SELECT os.organization_id_child
FROM (SELECT *
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = c_org_str_ver_id ) os
START WITH os.organization_id_parent = c_org_id
CONNECT BY os.organization_id_parent = PRIOR os.organization_id_child;
SELECT Decode(rin.seq_num,1,'000',
2,'010',
3,'020',
4,'030',
5,'040',
6,'060',
7,'070',
8,'080',
9,'081',
10,'10h',
12,'999',
'~') rec_num,
rin.seq_num,
rin.hide_flag,
rcd.ext_rcd_id,
rcd.rcd_type_cd
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
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
ORDER BY rin.seq_num;
SELECT 'x'
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NL_ORG_INFORMATION'
AND org_information3 IS NOT NULL
AND org_information4 IS NOT NULL;
SELECT SUBSTR(NVL(org_information5,'000000'),1,6)
FROM hr_organization_information
WHERE org_information_context = 'PQP_NL_PGGM_INFO'
AND organization_id = c_org_id;
SELECT fnd_number.canonical_to_number(NVL(scl.SEGMENT29,'0')) pt_perc,
asg.effective_start_date, asg.effective_end_date
FROM per_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_assignment_id
AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.effective_start_date BETWEEN g_extract_params(bg_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date;
SELECT fnd_number.canonical_to_number(NVL(scl.SEGMENT29,'0')) pt_perc
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_asg_id
AND (c_date - 1 BETWEEN asg.effective_start_date
AND asg.effective_end_date)
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT peev.element_entry_value_id,
peev.screen_entry_value,peev.effective_start_date,peev.effective_end_date
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (g_extract_params(bg_id).extract_start_date between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and (peev.effective_start_date between g_extract_params(bg_id).extract_start_date
and g_extract_params(bg_id).extract_end_date);
SELECT peev.element_entry_value_id,
peev.screen_entry_value,pee.source_start_date
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'Retro PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (g_extract_params(bg_id).extract_start_date between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and peev.screen_entry_value is not null
order by pee.source_start_date;
SELECT Decode(scl.SEGMENT1,'Y','0','1') segment1, asg.effective_start_date,asg.effective_end_date
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_asg_id
AND (asg.effective_start_date BETWEEN g_extract_params(bg_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date)
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT peev.screen_entry_value,pee.source_start_date
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
( pet.element_name = 'Retro PGGM Pensions General Information'
OR pet.element_name = 'Retro PGGM Pensions General Information Previous Year')
and piv.name ='Incidental Worker'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (g_extract_params(bg_id).extract_start_date between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
ORDER by pee.source_start_date;
Select peev.screen_entry_value,pee.element_entry_id
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name ='Retro PGGM Pensions General Information'
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (c_eff_date between pee.effective_start_date
and pee.effective_end_date)
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND ( c_eff_date between peev.effective_start_date
and peev.effective_end_date)
AND peev.screen_entry_value is not null;
Select peev.screen_entry_value,pee.element_entry_id
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name ='Retro PGGM Pensions General Information Previous Year'
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (c_eff_date between pee.effective_start_date
and pee.effective_end_date)
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND ( c_eff_date between peev.effective_start_date
and peev.effective_end_date)
AND peev.screen_entry_value is not null;
Select to_char(pee.source_start_date,'YYYYMMDD') source_start_date ,pee.element_entry_id
from pay_element_entries_f pee,
pay_element_types_f pet
where
pet.element_name ='Retro PGGM Pensions General Information'
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (c_eff_date between pee.effective_start_date
and pee.effective_end_date);
Select to_char(pee.source_start_date,'YYYYMMDD') source_start_date ,pee.element_entry_id
from pay_element_entries_f pee,
pay_element_types_f pet
where
pet.element_name ='Retro PGGM Pensions General Information Previous Year'
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (c_eff_date between pee.effective_start_date
and pee.effective_end_date);
SELECT peev.screen_entry_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and ((c_date -1) between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and((c_date-1) between peev.effective_start_date
and peev.effective_end_date);
SELECT Decode(scl.SEGMENT1,'Y','0','1') segment1
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_asg_id
AND (c_date - 1 BETWEEN asg.effective_start_date
AND asg.effective_end_date)
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
Select 'x'
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
( pet.element_name ='Retro PGGM Pensions General Information'
OR pet.element_name ='Retro PGGM Pensions General Information Previous Year')
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=p_assignment_id
AND pee.element_type_id =pet.element_type_id
AND (p_effective_date between pee.effective_start_date
AND pee.effective_end_date )
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND ( p_effective_date between peev.effective_start_date
AND peev.effective_end_date )
AND peev.screen_entry_value is null;
SELECT distinct to_char(pee.source_start_date,'YYYY') year_of_change
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'Retro PGGM Pensions Part Time Percentage'
AND ( piv.name = 'Extra Hours'
OR piv.name = 'Hours Worked'
OR piv.name = 'Total Hours' )
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and ( pee.effective_start_date between g_extract_params(bg_id).extract_start_date
and g_extract_params(bg_id).extract_end_date)
and pee.source_start_date < c_date
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND peev.screen_entry_value is not null
order by year_of_change;
g_rcd_060.DELETE;
g_rcd_080.DELETE;
g_rcd_081.DELETE;
SELECT region_1
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL'
UNION
SELECT address_line1
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL_GLB';
SELECT add_information13
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style = 'NL'
AND primary_flag = 'Y'
UNION
SELECT address_line2
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style = 'NL_GLB'
AND primary_flag = 'Y';
SELECT add_information14
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL'
UNION
SELECT address_line3
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL_GLB';
SELECT postal_code
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT substr(l_postal_code,i,1) INTO temp_str from dual;
SELECT town_or_city
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT Decode(country,'NL','N',country) code
,d_country
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'HR_NL_CITY'
AND lookup_code = p_lookup_code;
SELECT Substr(replace(per_information1,'.',NULL),1,5)
FROM per_all_people_f
WHERE person_id = c_person_id
AND business_group_id = p_business_group_id
AND c_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT Decode(sex,'F','V','M') gender
FROM per_all_people_f
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND c_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT scl.SEGMENT2||scl.SEGMENT3
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT peev.screen_entry_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions General Information'
and piv.name ='Kind Of Participation'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (c_date_earned between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and (c_date_earned between peev.effective_start_date
and peev.effective_end_date );
SELECT pri.assignment_id
FROM per_all_assignments_f sec,
per_all_assignments_f pri
WHERE sec.assignment_id = c_assgt_id
AND sec.person_id = pri.person_id
AND pri.primary_flag = 'Y'
AND c_date BETWEEN pri.effective_start_date AND pri.effective_end_date
AND c_date BETWEEN sec.effective_start_date AND sec.effective_end_date;
SELECT fnd_number.canonical_to_number (ppf.prl_information1)
FROM per_all_assignments_f paf,
pay_all_payrolls_f ppf
WHERE paf.assignment_id = p_assignment_id
AND paf.business_group_id = p_business_group_id
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND paf.payroll_id = ppf.payroll_id
AND ppf.prl_information_category = 'NL';
SELECT SUBSTR(org_information6,1,6)
FROM hr_organization_information
WHERE org_information_context = 'PQP_NL_PGGM_INFO'
AND organization_id = c_org_id
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN
fnd_date.canonical_to_date (org_information1)
AND nvl(fnd_date.canonical_to_date (org_information2), to_date('47121231','YYYYMMDD'));
SELECT fnd_number.canonical_to_number (ppf.prl_information1)
FROM per_all_assignments_f paf,
pay_all_payrolls_f ppf
WHERE paf.assignment_id = p_assignment_id
AND paf.business_group_id = p_business_group_id
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND paf.payroll_id = ppf.payroll_id
AND ppf.prl_information_category = 'NL';
SELECT lpad(org_information7,15,'0')
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'PQP_NL_PGGM_INFO'
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN
fnd_date.canonical_to_date (org_information1)
AND nvl(fnd_date.canonical_to_date (org_information2), to_date('47121231','YYYYMMDD'));
SELECT substr(NVL(peev.screen_entry_value,'0'),1,3)
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions General Information'
and piv.name ='PGGM Employee Number'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (c_date_earned between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and (c_date_earned between peev.effective_start_date
and peev.effective_end_date);
SELECT pri.assignment_id
FROM per_all_assignments_f sec,
per_all_assignments_f pri
WHERE sec.assignment_id = c_assgt_id
AND sec.person_id = pri.person_id
AND pri.primary_flag = 'Y'
AND c_date BETWEEN pri.effective_start_date AND pri.effective_end_date
AND c_date BETWEEN sec.effective_start_date AND sec.effective_end_date;
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_effective_date;*/
SELECT min(effective_start_date)
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.business_group_id = p_business_group_id
AND asg.assignment_type='E';
SELECT SUBSTR(DECODE(NVL(leaving_reason,'OVE'),'D','OVL'
,NVL(leaving_reason,'OVE')),1,3)
FROM per_periods_of_service pps,
per_all_assignments_f asg
WHERE asg.period_of_service_id = pps.period_of_service_id
AND assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND
effective_end_date ;
SELECT peev.screen_entry_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions General Information'
and piv.name ='Reason Of Participation'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (c_date_earned between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and (c_date_earned between peev.effective_start_date
and peev.effective_end_date );
SELECT pri.assignment_id
FROM per_all_assignments_f sec,
per_all_assignments_f pri
WHERE sec.assignment_id = c_assgt_id
AND sec.person_id = pri.person_id
AND pri.primary_flag = 'Y'
AND c_date BETWEEN pri.effective_start_date AND pri.effective_end_date
AND c_date BETWEEN sec.effective_start_date AND sec.effective_end_date;
SELECT peev.screen_entry_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (c_date_earned between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and (c_date_earned between peev.effective_start_date
and peev.effective_end_date )
and peev.screen_entry_value is not null;
SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'0')) pt_perc
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE asg.assignment_id = c_assignment_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND c_effective_date between asg.effective_start_date
AND asg.effective_end_date
AND target.enabled_flag = 'Y';
SELECT Decode(scl.SEGMENT1,'Y','0','1')
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_asg_id
AND c_date_earned BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT max(actual_termination_date)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = c_assignment_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_effective_date;
SELECT paa.effective_start_date
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_assignment_id
AND paa.business_group_id = c_business_group_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='TERM_ASSIGN'
AND ( paa.effective_start_date
BETWEEN g_extract_params (c_business_group_id).extract_start_date +1
AND g_extract_params (c_business_group_id).extract_end_date + 1 );*/
SELECT MIN(effective_start_date)-1 term_date
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
UNION
--
-- Get the dates for any ended assignments. Note that this is for sec
-- assignments only.
--
SELECT MAX(effective_end_date)
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND asg.primary_flag = 'N'
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date = asg.effective_end_date + 1
AND asg.assignment_id = asg1.assignment_id )
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date < asg.effective_start_date
AND asg.assignment_id = asg1.assignment_id
AND asg1.assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'));
Select 'x'
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name ='Retro PGGM Pensions General Information'
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (pee.effective_start_date between c_start_date
and g_extract_params(p_business_group_id).extract_end_date )
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND peev.screen_entry_value is not null
ORDER by source_start_date desc;
Select 'x'
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name ='Retro PGGM Pensions General Information Previous Year'
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (pee.effective_start_date between c_start_date
and g_extract_params(p_business_group_id).extract_end_date )
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND peev.screen_entry_value is not null
ORDER by source_start_date desc;
Select count(to_number(peev.screen_entry_value))
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name ='Retro PGGM Pensions General Information Previous Year'
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=c_asg_id
AND pee.element_type_id =pet.element_type_id
AND (pee.effective_start_date between c_start_date
and c_end_date )
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND peev.screen_entry_value is not null;
SELECT CEIL(fnd_number.canonical_to_number(peev.screen_entry_value))
FROM pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
WHERE pet.element_name ='PGGM Pensions General Information'
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id = pet.element_type_id
AND pee.assignment_id = c_asg_id
AND pee.element_type_id = pet.element_type_id
AND pee.effective_start_date BETWEEN c_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND peev.effective_start_date BETWEEN c_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND peev.screen_entry_value IS NOT NULL
ORDER by source_start_date desc;
Select sum(to_number(NVL(peev.screen_entry_value,0)))
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name='Retro PGGM Pensions Part Time Percentage'
and piv.name='Hours Worked'
and pee.assignment_id=c_assignment_id
and pet.element_type_id =pee.element_type_id
and ( pee.effective_start_date between c_start_date
and c_end_date )
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id = piv.input_value_id
and to_char(pee.source_start_date,'YYYY')=to_char(c_start_date,'YYYY')
and peev.screen_entry_value is not null;
Select sum(to_number(NVL(peev.screen_entry_value,0)))
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name='Retro PGGM Pensions Part Time Percentage'
and piv.name='Total Hours'
and pee.assignment_id=c_assignment_id
and pet.element_type_id =pee.element_type_id
and ( pee.effective_start_date between c_start_date
and c_end_date )
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id = piv.input_value_id
and to_char(pee.source_start_date,'YYYY')=to_char(c_start_date,'YYYY')
and peev.screen_entry_value is not null;
Select sum(to_number(NVL(peev.screen_entry_value,0)))
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
pet.element_name='Retro PGGM Pensions Part Time Percentage'
and piv.name='Extra Hours'
and pee.assignment_id=c_assignment_id
and pet.element_type_id =pee.element_type_id
and ( pee.effective_start_date between c_start_date
and c_end_date )
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id = piv.input_value_id
and to_char(pee.source_start_date,'YYYY')=to_char(c_start_date,'YYYY')
and peev.screen_entry_value is not null;
SELECT paa.assignment_action_id
,ppa.date_earned
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.source_action_id IS NOT NULL
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND ppa.action_type IN ('B','L','O','Q','R')
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date
ORDER BY ppa.effective_date;
SELECT to_number(prrv.result_value) result
FROM pay_run_result_values prrv
,pay_run_results prr
WHERE prrv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_asg_act_id
AND prr.source_id = p_element_entry_id
AND prrv.input_value_id = p_input_value_id ;
SELECT pee.element_entry_id
FROM pay_element_entries_f pee,
pay_element_types_f pet
WHERE
pet.element_name='PGGM Pensions Part Time Percentage'
AND pee.assignment_id=c_assignment_id
AND pet.element_type_id =pee.element_type_id
AND ( c_date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date);
SELECT piv.input_value_id FROM
pay_input_values_f piv,
pay_element_types_f pet
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
AND piv.name ='Extra Hours'
AND piv.element_type_id =pet.element_type_id;
SELECT defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE
pbt.balance_name =c_balance_name
and pbd.legislation_code='NL'
and pbd.DIMENSION_NAME=c_dimension_name
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT peev.screen_entry_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (c_date_earned between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and (c_date_earned between peev.effective_start_date
and peev.effective_end_date )
and peev.screen_entry_value is not null;
SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'0')) pt_perc
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE asg.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND c_effective_date between asg.effective_start_date
AND asg.effective_end_date
AND target.enabled_flag = 'Y';
SELECT Decode(scl.SEGMENT1,'Y','0','1')
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_asg_id
AND c_date_earned BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT prrv.result_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv
WHERE
pet.element_name = 'PGGM Pensions General Information'
and piv.name ='Incidental Worker'
and piv.element_type_id=pet.element_type_id
and prr.element_type_id=pet.element_type_id
and prr.assignment_action_id =c_asg_action_id
and (c_date_earned between prr.start_date
and prr.end_date)
and prrv.run_result_id=prr.run_result_id
and prrv.input_value_id=piv.input_value_id;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = c_asg_id
AND business_group_id = bg_id
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN
effective_start_date AND effective_end_date;
Select max(effective_start_date) from per_all_assignments_f
where assignment_id = c_asg_id
and organization_id = org_id;
Select pee.screen_entry_value,pee.effective_start_date
FROM
pay_element_entries_f pee,
pay_element_types_f pet
where pee.assignment_id = c_asg_id
AND pee.element_type_id =pet.element_type_id
AND pet.element_name='Retro PGGM Pensions Part Time Percentage'
AND g_extract_params(bg_id).extract_start_date
Between pee.effective_start_date and pee.effective_end_date;
SELECT pee.screen_entry_value,pee.effective_start_date
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and (g_extract_params(bg_id).extract_start_date between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id;*/
SELECT 1 FROM per_periods_of_service
WHERE PERSON_ID = g_person_id
AND TRUNC(actual_termination_date) <= trunc(p_effective_date)
AND NOT EXISTS ( SELECT 1 FROM PER_PERIODS_OF_SERVICE
WHERE person_id = g_person_id
AND trunc(date_start) BETWEEN trunc(p_effective_date)
AND add_months(trunc(p_effective_date),1) - 1
AND actual_termination_date is null) ;
l_main_rec.val_40:='Delete';
Hr_Utility.set_location('Delete main rec',80);
SELECT peev.screen_entry_value
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE
pet.element_name = 'PGGM Pensions Part Time Percentage'
and piv.name ='Part Time Percentage'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
and ((c_date -1) between pee.effective_start_date
and pee.effective_end_date)
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and((c_date-1) between peev.effective_start_date
and peev.effective_end_date);
SELECT Decode(scl.SEGMENT1,'Y','0','1') segment1
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = c_asg_id
AND (c_date - 1 BETWEEN asg.effective_start_date
AND asg.effective_end_date)
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
l_main_rec.val_40:='Delete';
Hr_Utility.set_location('Delete main rec',80);
l_main_rec.val_40:='Delete';
Hr_Utility.set_location('Delete main rec',80);
SELECT MIN(ppa.effective_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_id = c_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.action_type IN ('R', 'Q', 'I', 'B', 'V');
SELECT min(paa.effective_start_date)
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_assignment_id
AND paa.business_group_id = c_business_group_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='TERM_ASSIGN';
SELECT max(paa.effective_end_date)
FROM per_all_assignments_f paa
WHERE paa.assignment_id = c_assignment_id
AND paa.business_group_id = c_business_group_id;
SELECT chg_eff_dt
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND chg_evt_cd = 'AAT'
AND (chg_eff_dt BETWEEN g_extract_params (c_business_group_id).extract_start_date
AND g_extract_params (c_business_group_id).extract_end_date)
ORDER BY ext_chg_evt_log_id desc;
SELECT 'x'
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_assignment_id
AND paa.business_group_id = c_business_group_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='TERM_ASSIGN'
AND ( paa.effective_start_date
BETWEEN g_extract_params (c_business_group_id).extract_start_date + 1
AND g_extract_params (c_business_group_id).extract_end_date + 1);
SELECT paa.effective_end_date
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_assignment_id
AND paa.business_group_id = c_business_group_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status = 'TERM_ASSIGN';
SELECT chg_eff_dt
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND (chg_evt_cd = 'COCN' ) --OR (chg_evt_cd = 'APA')
AND ( chg_eff_dt between g_extract_params(c_business_group_id).extract_start_date
and g_extract_params(c_business_group_id).extract_end_date )
ORDER by ext_chg_evt_log_id desc;
SELECT chg_eff_dt
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND ( chg_evt_cd = 'COPR' or
chg_evt_cd = 'COPC' or chg_evt_cd = 'CORS' OR chg_evt_cd = 'APA' )
AND ( chg_eff_dt between g_extract_params(c_business_group_id).extract_start_date
and g_extract_params(c_business_group_id).extract_end_date )
ORDER by ext_chg_evt_log_id desc;
SELECT hrl1.lookup_code
FROM ben_ext_chg_evt_log,
hr_lookups hrl1
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND chg_evt_cd = 'COCN'
AND Substr(Nvl(old_val1,'-1'),0,7)=hrl1.meaning
AND hrl1.LOOKUP_TYPE='PQP_NL_STUCON_CODE_MAPPING'
AND (chg_eff_dt between g_extract_params(c_business_group_id).extract_start_date
AND g_extract_params(c_business_group_id).extract_start_date)
AND chg_eff_dt >=
( Select min(chg_eff_dt) FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND chg_evt_cd = 'COCN'
AND chg_eff_dt >=c_effective_date
)
ORDER by ext_chg_evt_log_id desc;
SELECT to_number(hrl1.lookup_code)
FROM ben_ext_chg_evt_log,
hr_lookups hrl1
WHERE chg_evt_cd = 'COCN'
AND ( chg_eff_dt between g_extract_params(p_business_group_id).extract_start_date
and g_extract_params(p_business_group_id).extract_end_date )
AND Substr(Nvl(old_val1,'-1'),0,7)=hrl1.meaning
AND hrl1.lookup_type='PQP_NL_STUCON_CODE_MAPPING'
AND person_id = g_person_id
ORDER by ext_chg_evt_log_id desc;
SELECT to_number(hrl1.lookup_code)
FROM ben_ext_chg_evt_log,
hr_lookups hrl1
WHERE chg_evt_cd = 'COCN'
AND ( chg_eff_dt between g_extract_params(p_business_group_id).extract_start_date
and g_extract_params(p_business_group_id).extract_end_date )
AND Substr(Nvl(new_val1,'-1'),0,7)=hrl1.meaning
AND hrl1.lookup_type='PQP_NL_STUCON_CODE_MAPPING'
AND person_id = g_person_id
ORDER by ext_chg_evt_log_id desc;
Select 'x'
from pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entry_values_f peev
where
( pet.element_name ='Retro PGGM Pensions General Information'
OR pet.element_name ='Retro PGGM Pensions General Information Previous Year')
AND piv.name = 'Annual Pension Salary'
AND piv.element_type_id=pet.element_type_id
AND pee.assignment_id=p_assignment_id
AND pee.element_type_id =pet.element_type_id
AND (p_effective_date between pee.effective_start_date
AND pee.effective_end_date )
AND peev.element_entry_id=pee.element_entry_id
AND peev.input_value_id=piv.input_value_id
AND ( p_effective_date between peev.effective_start_date
AND peev.effective_end_date )
AND peev.screen_entry_value is not null;
SELECT 'x'
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_asg_id
AND paa.business_group_id = bg_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND (eff_date between paa.effective_start_date and paa.effective_end_date);
SELECT NVL(period_num,0)
FROM per_all_assignments_f PAA
,per_time_periods TPERIOD
WHERE
PAA.assignment_id = c_assignment_id
AND TPERIOD.payroll_id = PAA.payroll_id
AND c_date_earned between PAA.effective_start_date and PAA.effective_end_date
AND c_extract_start_date between TPERIOD.start_date and TPERIOD.end_date;
SELECT 'x'
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_asg_id
AND paa.business_group_id = bg_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='ACTIVE_ASSIGN'
AND (eff_date between paa.effective_start_date and paa.effective_end_date);
SELECT 'x'
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_asg_id
AND paa.business_group_id = bg_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='ACTIVE_ASSIGN'
AND (paa.effective_start_date between g_extract_params(bg_id).extract_start_date
and g_extract_params(bg_id).extract_end_date );
Select 'x'
from pay_element_entries_f pee,
pay_element_types_f pet
where pet.element_name='Retro PGGM Pensions Part Time Percentage Information Element'
and pee.assignment_id=p_assignment_id
and pet.element_type_id =pee.element_type_id
and p_effective_date between pee.effective_start_date
and pee.effective_end_date;
SELECT 'x'
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_asg_id
AND paa.business_group_id = bg_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='ACTIVE_ASSIGN'
AND (eff_date between paa.effective_start_date and paa.effective_end_date);
SELECT 'x'
FROM per_all_assignments_f paa,
per_assignment_status_types past
WHERE paa.assignment_id = c_asg_id
AND paa.business_group_id = bg_id
AND paa.assignment_status_type_id=past.assignment_status_type_id
AND past.per_system_status ='ACTIVE_ASSIGN'
AND (paa.effective_start_date between g_extract_params(bg_id).extract_start_date
and g_extract_params(bg_id).extract_end_date );
Select 'x'
from pay_element_entries_f pee,
pay_element_types_f pet
where pet.element_name='Retro PGGM Pensions Part Time Percentage'
and pee.assignment_id=c_assignment_id
and pet.element_type_id =pee.element_type_id
and ( pee.effective_start_date between g_extract_params(p_business_group_id).extract_start_date
and g_extract_params(p_business_group_id).extract_end_date)
and pee.source_start_date < g_extract_params(p_business_group_id).extract_start_date;*/
SELECT Count(dtl.ext_rslt_dtl_id)
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND ext_rcd_id IN(c_record_id);
SELECT DISTINCT(val_32) val_32
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND val_31= c_org_id
ORDER BY val_32 ASC ;
SELECT val_31,val_32
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
ORDER BY val_32 ASC;
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.val_32 =c_val_32;
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND ext_rcd_id= c_ext_dtl_rcd_id;
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND ext_rcd_id= c_ext_dtl_rcd_id;
SELECT 'x'
FROM ben_ext_rslt_dtl
WHERE ext_rslt_id=Ben_Ext_Thread.g_ext_rslt_id
AND val_31=c_org_id;
l_insert_trailer Number := 1;
l_delete_count NUMBER:=0;
l_delete_index NUMBER:=0;
l_999_inserted NUMBER := 0;
l_000_inserted NUMBER := 0;
DELETE 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 ben_ext_rslt_dtl
WHERE ext_rcd_id IN ( l_rcd_id_060,l_rcd_id_080,l_rcd_id_081 )
AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND business_group_id = p_business_group_id
AND val_40='Delete';
IF l_000_inserted = 0 THEN
Ins_Rslt_Dtl(p_dtl_rec => l_header_new_rec);
l_000_inserted := 1;
l_insert_trailer := 1;
IF l_999_inserted = 0 THEN
Ins_Rslt_Dtl(p_dtl_rec => l_trailer_new_rec);
l_999_inserted := 1;
DELETE
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND dtl.ext_rcd_id = csr_rcd_rec.ext_rcd_id
AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_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.ext_rcd_id = csr_rcd_rec.ext_rcd_id
AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_id
AND business_group_id = p_business_group_id;