[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
03/01/2000 RChase 115.47 Fixed dynamic cursors in build_select_statements.
to use explict date conversion. This clears up
issues with clients using NLS date formats other
than DD-MON-YYYY.
Updated process procedure to output a wrapped dynamic
sql statement to the log file for better debugging
purposes.
03/14/2000 Thayden 115.48 Advanced Conditions for headers and trailers.
05/31/2000 gperry 115.49 Tuning.
06/20/2000 jcarpent 115.50 Fix for Tuning fix,
uninitialized collection.
bug 5339.
08/10/2000 tilak 115.51 Dynamic sql date format changed from YYYY to RRRR to
support all the other lang
08/17/2000 tilak 115.52
08/17/2000 tilak 115.53 bug 1381514 fixed before calling calc_ext_date the type of code is calidat ed
08/17/2000 tilak 115.54 Backport of 115.48 for 11.5.2
patchset with NLS fixes from
115.51. wwbug 1391217.
08/30/2000 stee 115.55 Leapfrog of 115.53.
09/03/2000 stee 115.56 Leapfrog of 115.51.
09/03/2000 stee 115.57 Leapfrog of 115.55.
12/06/2000 rchase 115.58 Leapfrog of 115.56 with fixes
for 1521958. Added rule contexts.
12/07/2000 jcarpent 115.59 Merged version of 115.57 and 115.58.
01/23/2001 rchase 115.60 Bug 1608852. Correct thread issue.
Lock being released before status
updating batch range status.
01/30/2001 tilak 115.61 error message changed ,get from get_error_messages
with element name
06/06/2001 tilak 115.62 caling formula is added for header/trailer - 1786750
06/21/2001 tilak 115.63 gv$_system_parameter used instead of v$system_paramter
07/27/2001 tilak 115.65 Change Event Dynomic sql is cahnged,
chg_eff_Dt is replaced p_effective date
11/07/2001 mhoyes 115.66 - bug 2100912. Moved call to ben_extract.set_ext_lvls
from chunk level to thread level. Globals were
being over refreshed.
11/09/2001 mhoyes 115.67 - bug 2100912. Moved call to ben_extract.setup_rcd_typ_lvl
from chunk level to thread level. Globals were
being over refreshed.
11/26/2001 mhoyes 115.68 - dbdrv lines.
01/20/2002 tjesumic 115.69 - restart process added
02/12/2002 hnarayan 115.70 added procedure update_ht_strt_end_dt to update
data which correspond to fields STRTDT and ENDDT
in header and trailer records of result detail.
03/11/2002 tjesumic 115.71 - UTF changes
03/12/2002 ikasire 115.72 BEN UTF8 Changes
05/06/2002 tjesumic 115.73 p_ext_crit_prfl_id ,p_rquest_id added as
paramter to process_ext_ht_recs
05/16/2002 tjesumic 115.74 dynamic sql chnaged the assg date validate changed from nvl to (+) bug : 2376285
05/21/2002 tjesumic 115.75 PLPLCY element added for header level
05/24/2002 tjesumic 115.76 change event dynomic sql fixed
08-Jun-02 pabodla 115.78 Do not select the contingent worker
assignment when assignment data is
fetched.
17-Jun-02 pabodla 115.79 Fetching assignment data even if assignment_type is null
15-Aug-02 tjesumic 115.79 Max_lenght added for String Element
28-Aug-02 tjesumic 115.80 if the string Element is null and max_lenght defined
string element will be considered as space
17-dec-02 tjesumic 115.81 115.78,115.79 reversed
27-Dec-02 lakrish 115.84 NOCOPY changes
13-Feb-03 rpillay 115.85 HRMS Debug Performance changes to
hr_utility.set_location calls
23-Aug-03 tjesumic 115.86 calcualted records added for detail record
02-Oct-03 tjesumic 115.87 Upper/lower/initcap applied for string format mask
29-Oct-03 tjesumic 115.88 total for detail records are considers only non hiden records
hiden recors are filterd
30-dec-03 mmudigon 115.89 Bug 3232205. Modified cursors on
ben_person_actions to drive by
benefit_action_id
19-Jan-03 tjesumic 115.91 New procedire load_extract added to import and export the
extract definition
19-Jan-03 tjesumic 115.92 extract sedded or not decided by the extract not the parameter
19-Jan-03 tjesumic 115.92 view name cahnged to ben_pl_v
26-Jan-03 tjesumic 115.94 validate_mode parameterized for fndload concurren mgr
26-Jan-03 tjesumic 115.95 Extract header formula format mask is fixed
10-Feb-03 tjesumic 115.96 Person Type usage added in dynamic sql - ppt
21-Apr-04 tjesumic 115.97 when the header/trailed element is null and mandatory
then throw the warning , if the record is mandatory then
throw the error and rollback the header and trailer
26-May-04 mmudigon 115.98 Bug 367237. Parameters changed
for Restart procedure
04-Jun-04 mmudigon 115.99 GSCC file.sql.6 fix
06-Jul-04 tjesumic 115.100 for security check_asg_security added and per_people_f view used
06-Jul-04 tjesumic 115.104 brought forward the version 100 above the leaf frog
02-Aug-04 nhunur 115.105 ensure request_id is not passed as null to benefit actions api.
06-Aug-04 nhunur 115.106 3810114 - Added code to prevent over polling of fnd_conc_requests.
13-aug-04 tjesumic 115.107 chg_actl_ct is truncated to validated the dates
18-Nov-04 rpinjala 115.108 New procedure chk_pqp_extract added
19-Nov-04 rpinjala 115.109 Changed chk_pqp_extract procedure.
05-Dec-04 rpinjala 115.110 Changed chk_pqp_extract procedure.
15-Dec-04 tjesumic 115.111 ext_rcd_in_file_id added to ben_Ext_rslt_dtl table
01-Feb-05 tjesumic 115.112 300 elements allowed in a record
08-Mar-05 tjesumic 115.113 check_asg_security changed for performance
09-Mar-05 tjesumic 115.114 check_asg_security changed for performance
22-Mar-05 tjesumic 115.115 CWB (CW) , subheader codes changes
new extract type for 'CW' and new header and trialer procedure
for subheader and new criteria for both added
24-Mar-05 tjesumic 115.116 position extracted from position base table
30-Mar-05 tjesumic 115.117 new param p_subhdr_chg_log added for nfc extract to get postion
suheader from history table
30-Mar-05 tjesumic 115.118 nfc changes
31-Mar-05 tjesumic 115.119 GHR changes
15-Mar-05 tjesumic 115.120 Global/Cross bg changes added
27-Apr-05 rpinjala 115.121 Changed chk_pqp_extract procedure.
28-Apr-05 tjesumic 115.122 to_date change to canonical_to_date for formula result date conversion
30-Apr-05 tjesumic 115.123 string element added for rule element
12-May-05 tjesumic 115.124 g_ext_dfn_id and g_ext_rslt_id intialised in subheader for subhdr formula
17-May-05 tjesumic 115.125 p_ghr_date parameter changed to p_eff_start/end_dte
08-Jun-05 tjesumic 115.125 pennserver enhancement for new parameter, outpput type
effective, actual date and pauroll change events
08-Jun-05 tjesumic 115.127 pennserver enhancement
13-Jun-05 tjesumic 115.128 payroll dynamic sql changed
13-Jun-05 tjesumic 115.129 ghr sql cahnged for pos02
25-Jul-05 tjesumic 115.130 Dynamic sql build changed fro performance and bug 4440823
22-Aug-05 tjesumic 115.131 business group id variable initalization in security check is changed
22-Aug-05 rbingi 115.132 Bug 4545881 - Global flag retrieved from Ext_Crit_Prfl
21-Sep-05 tjesumic 115.133 grade inforamtion extracted in subheader
13-Sep-05 tjesumic 115.134 when the extract excuted with only subheader, the process should not
go throuh person information
9-nov-05 nhunur 115.135 xcl > per for performance - bug 4721453
6-Dec-05 tjesumic 115.136 cm_display_flag is added and validated
9-Dec-05 tjesumic 115.137 benxmlwrit called for cm_display on
10-Dec-05 tjesumic 115.138 output_code to set the output type for display
20-Dec-05 tjesumic 115.139 cm_downlaod added for download and GHR fix as per 4609093
20-Dec-05 tjesumic 115.141 c_xdoi cursor closed
22-Dec-05 tjesumic 115.142 XSL changed to EXCEL
11-Jan-06 tjesumic 115.143 restart changed to send correct parameters to process
02-Feb-06 tjesumic 115.144 restart changed to process the errorerd ranges from advance condition
12-Feb-06 tjesumic 115.145 Assignment set added in extract criteria's person level
15-Mar-06 tjesumic 115.146 Penserver Sql changes
23-Mar-06 tjesumic 115.147 Penserver Sql changes
27-Mar-06 tjesumic 115.148 debug function call removed
29-Mar-06 tjesumic 115.149 end dte and start parsed in update_ht_strt_end_dt
28-APR-06 hgattu 115.150 new param p_out_dummy is added to process procedure(5131931)
07-Aug-06 tjesumic 115.151 parameter p_out_dummy passed in wrong position
06-Oct-06 tjesumic 115.152 The Advance Date criteria added to Dynamic sql
Pay change event sql splited into two, 1 for non Asg event
another one for Asg Events. New procedure
chck_non_asg_pay_evt and build_adv_criteria added.
20-oct-06 tjesumic 115.153 The dynamic query changed to calc the criteria value and validated
as in clause without using extract table. per_all_people removed
from pay change event query
23-Oct-06 tjesumic 115.154 nocopy added
31-Oct-06 tjesumic 115.155 payroll id added to the dynamic query
07-Dec-06 tjesumic 115.156 subheader's org, job and grade from and to date are validated
12-Feb-07 tjesumic 115.157 allow overide param added for uploading file
required file benextse.lct 115.40 , benextse,pkh/pkb 115.24/73
13-Feb-07 tjesumic 115.158 Legislation ang global check in pay_event_updates table is removed
The primary key is passed from process event table.
07-mar-07 tjesumic 115.159 Dynamic sql error is fixed
20-mar-07 tjesumic 115.162 115.160 and 161 reverted
04-Sep-07 tjesumic 115.163 total count and detail count elements are fixed by adding ext_rcd_in_file_id in validation
26-Nov-07 tjesumic 115.164 when the extract is global, for 'PPT' person type usage the criteria are validated from table
big - 6642051
30-Apr-08 vkodedal 115.165 Changes required for penserver - performance fix--6895935,6801389,6995291
11-Aug-08 vkodedal 115.167 Penserver perf issue-7274509
25-Aug-08 vkodedal 115.168 Penserver perf issue-7341530
12-Sep-08 jvaradra 115.170 Penserver perf issue-7358558
30-Mar-09 vkodedal 115.172,173 Bug#8335771 -Restart process not spawning threads - get l_num_range as count
18-Jun-10 vkodedal 115.174 Bug#9823193 Added ORDERED hint
16-Dec-11 velvanop 115.175 Bug#13509481: Added person selection rule parameter for the Extract process
*/
--
--
-- ----------------------------------------------------------------------------
-- | Private Global Definitions |
-- ----------------------------------------------------------------------------
--
g_debug boolean := hr_utility.debug_enabled;
SELECT data_typ_cd
, ext_typ_cd
, strt_dt
, end_dt
, ext_crit_prfl_id
, ext_file_id
, prmy_sort_cd
, scnd_sort_cd
, output_name
, drctry_name
, apnd_rqst_id_flag
, kickoff_wrt_prc_flag
, use_eff_dt_for_chgs_flag
, upd_cm_sent_dt_flag
, ext_post_prcs_rl
, ext_global_flag
, output_type
, xdo_template_id
, cm_display_flag
FROM ben_ext_dfn
WHERE ext_dfn_id = p_ext_dfn_id;
select ext_data_elmt_in_rcd_id1,
ext_data_elmt_in_rcd_id2
from ben_Ext_file exf
where exf.ext_file_id = p_file_id ;
select exf.short_name
from ben_ext_fld exf,
ben_Ext_data_elmt_in_rcd edr,
ben_ext_data_elmt ede
where edr.ext_data_elmt_in_rcd_id = p_data_elmt_in_rcd_id
and edr.ext_data_elmt_id = ede.ext_Data_elmt_id
and ede.ext_fld_id = exf.ext_fld_id (+)
;
select ext_global_flag
from ben_ext_crit_prfl
where ext_crit_prfl_id = p_ext_crit_prfl_id
;
Procedure update_ht_strt_end_dt (p_ext_rslt_id number)
IS
--
l_proc varchar2(72);
SELECT rslt.run_strt_dt , rslt.run_end_dt
FROM ben_ext_rslt rslt
WHERE rslt.ext_rslt_id = p_ext_rslt_id;
SELECT distinct rdtl.ext_rcd_id ext_rcd_id
FROM ben_ext_rcd rcd, ben_ext_rslt_dtl rdtl
WHERE rdtl.ext_rslt_id = p_ext_rslt_id
and rdtl.ext_rcd_id = rcd.ext_rcd_id
and rcd.rcd_type_cd in ('H','T');
SELECT elrc.seq_num, fld.short_name, elmt.frmt_mask_cd
FROM ben_ext_data_elmt_in_rcd elrc, ben_ext_data_elmt elmt, ben_ext_fld fld
WHERE elrc.ext_rcd_id = p_ext_rcd_id
and elrc.ext_data_elmt_id = elmt.ext_data_elmt_id
and elmt.ext_fld_id = fld.ext_fld_id
and ltrim(rtrim(fld.short_name)) in ('STRTDT','ENDDT');
l_proc := g_package||'.update_ht_strt_end_dt';
l_tot_string := 'UPDATE BEN_EXT_RSLT_DTL ' || l_tot_string
|| ' WHERE ext_rslt_id = ' || to_char(p_ext_rslt_id)
|| ' AND ext_rcd_id = ' || to_char(rcd_rec.ext_rcd_id);
l_tot_string := 'UPDATE BEN_EXT_RSLT_DTL SET ' || l_col_name || ' = :VAL where ext_rslt_id = :RSLT_ID and ext_rcd_id = :RCD_ID' ;
END update_ht_strt_end_dt;
select formula_type_id
from ff_formulas_f
where formula_id = p_rule_id
and p_effective_date
between effective_start_date
and effective_end_date;
select name
from per_business_groups
where organization_id = p_bg_id ;
select a.plcy_r_grp
from ben_popl_org_f a,
ben_popl_org_role_f b
where a.pl_id = l_pl_id
and a.plcy_r_grp is not null
and a.popl_org_id = b.popl_org_id
and b.org_role_typ_cd = l_typ_cd
and p_effective_date between a.effective_start_date
and a.effective_end_date
and p_effective_date between b.effective_start_date
and b.effective_end_date;
select a.plcy_r_grp
from ben_popl_org_f a
where a.pl_id = l_pl_id
and a.plcy_r_grp is not null
and p_effective_date between a.effective_start_date
and a.effective_end_date ;
select a.cstmr_num
from ben_popl_org_f a,
ben_popl_org_role_f b
where a.pl_id = l_pl_id
and a.cstmr_num is not null
and a.popl_org_id = b.popl_org_id
and b.org_role_typ_cd = l_typ_cd
and p_effective_date between a.effective_start_date
and a.effective_end_date
and p_effective_date between b.effective_start_date
and b.effective_end_date;
select b.name
from ben_popl_org_f a,
ben_popl_org_role_f b
where a.pl_id = l_pl_id
and b.name is not null
and b.org_role_typ_cd = l_typ_cd
and a.popl_org_id = b.popl_org_id
and p_effective_date between a.effective_start_date
and a.effective_end_date
and p_effective_date between b.effective_start_date
and b.effective_end_date;
select count(distinct person_id)
from ben_ext_rslt_dtl xrd
where xrd.ext_rslt_id = p_ext_rslt_id
and person_id not in (0, 999999999999)
and xrd.group_val_01 = p_group_val_01
and nvl(xrd.group_val_02,'-1') = nvl(p_group_val_02,'-1') ;
select count(*)
from ben_ext_rslt_dtl xrd ,
ben_ext_rcd_in_file erf
where xrd.ext_rslt_id = p_ext_rslt_id
and xrd.ext_rcd_id = erf.ext_rcd_id
and xrd.ext_rcd_in_file_id = erf.ext_rcd_in_file_id
and erf.ext_file_id = p_ext_file_id
and erf.hide_flag = 'N'
and xrd. person_id not in (0, 999999999999)
and xrd.group_val_01 = p_group_val_01
and nvl(xrd.group_val_02,'-1') = nvl(p_group_val_02,'-1')
;
select count(*)
from ben_ext_rslt_dtl xrd ,
ben_ext_rcd_in_file erf
where xrd.ext_rslt_id = p_ext_rslt_id
and xrd.ext_rcd_id = erf.ext_rcd_id
and xrd.ext_rcd_in_file_id = erf.ext_rcd_in_file_id
and erf.ext_file_id = p_ext_file_id
and erf.hide_flag = 'N'
and xrd.group_val_01 = p_group_val_01
and nvl(xrd.group_val_02,' ') = nvl(p_group_val_02,' ')
;
select count(*)
from ben_ext_rcd_in_file fil, ben_ext_rcd rcd
where fil.ext_rcd_id = rcd.ext_rcd_id
and fil.ext_file_id = p_ext_file_id
and rcd.rcd_type_cd = 'L';
select ewc.seq_num
,xel.ext_data_elmt_id
, xel.data_elmt_typ_cd
, xel.data_elmt_rl
, xel.name
, xel.string_val
, xel.dflt_val
, xel.max_length_num
, xel.ttl_fnctn_cd
, xel.ttl_cond_oper_cd
, xel.ttl_cond_val
, xel.ttl_sum_ext_data_elmt_id
, xel.ttl_cond_ext_data_elmt_id
, efl.short_name
from ben_Ext_where_clause ewc,
ben_Ext_data_elmt xel,
ben_ext_fld efl
where ewc.ext_data_elmt_id = p_ext_data_elmt_id
and xel.ext_data_elmt_id = ewc.cond_ext_data_elmt_id
and xel.ext_fld_id = efl.ext_fld_id (+) ;
select a.ext_rcd_id,
b.ext_rcd_in_file_id,
b.seq_num,
b.sprs_cd,
b.rqd_flag
from ben_ext_rcd a,
ben_ext_rcd_in_file b
where a.ext_rcd_id = b.ext_rcd_id
and b.ext_file_id = p_ext_file_id
and a.rcd_type_cd = p_rcd_typ_cd
order by b.seq_num;
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,
b.ttl_fnctn_cd,
b.ttl_cond_oper_cd,
b.ttl_cond_val,
b.ttl_sum_ext_data_elmt_id,
b.ttl_cond_ext_data_elmt_id,
c.short_name
from ben_ext_data_elmt_in_rcd a,
ben_ext_data_elmt b,
ben_ext_fld c
where a.ext_rcd_id = l_ext_rcd_id
and a.ext_data_elmt_id = b.ext_data_elmt_id
and b.ext_fld_id = c.ext_fld_id (+)
order by a.seq_num;
SELECT b.val_1
FROM ben_ext_crit_typ a,
ben_ext_crit_val b
WHERE a.ext_crit_typ_id = b.ext_crit_typ_id
and a.crit_typ_cd = 'BPL'
and a.ext_crit_prfl_id = p_ext_crit_prfl_id;
select count(*)
from ben_Ext_rslt_dtl xrd,
ben_ext_rcd rcd,
ben_ext_rcd_in_file erf
where xrd.ext_rslt_id = p_ext_rslt_id
and xrd.ext_rcd_id = erf.ext_rcd_id
and xrd.ext_rcd_in_file_id = erf.ext_rcd_in_file_id
and erf.ext_file_id = p_ext_file_id
and rcd.ext_rcd_id = erf.ext_rcd_id
and erf.hide_flag = 'N'
and rcd.rcd_type_cd = 'L' ;
,p_program_update_date => sysdate
,p_request_id => fnd_global.conc_request_id
,p_object_version_number => l_object_version_number
,p_ext_per_bg_id => p_ext_per_bg_id
,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
);
select distinct
xrd.group_val_01,
xrd.group_val_02,
nvl(xrd.ext_per_bg_id,-1) ext_per_bg_id
from ben_Ext_rslt_dtl xrd,
ben_ext_rcd rcd,
ben_ext_rcd_in_file erf
where xrd.ext_rslt_id = p_ext_rslt_id
and xrd.ext_rcd_id = erf.ext_rcd_id
and erf.ext_file_id = p_ext_file_id
and rcd.ext_rcd_id = erf.ext_rcd_id
and rcd.rcd_type_cd = 'S'
and ltrim(xrd.group_val_01) is not null ;
select null
from fnd_concurrent_requests fnd
where fnd.phase_code <> 'C'
and fnd.request_id = p_request_id;
select count(*)
from ben_ext_rcd_in_file fil, ben_ext_rcd rcd
where fil.ext_rcd_id = rcd.ext_rcd_id
and fil.ext_file_id = p_ext_file_id
and rcd.rcd_type_cd = 'H';
select count(*)
from ben_ext_rcd_in_file fil, ben_ext_rcd rcd
where fil.ext_rcd_id = rcd.ext_rcd_id
and fil.ext_file_id = p_ext_file_id
and rcd.rcd_type_cd = 'T';
select count(*)
from ben_Ext_rslt_dtl xrd,
ben_ext_rcd rcd,
ben_ext_rcd_in_file erf
where xrd.ext_rslt_id = p_ext_rslt_id
and xrd.ext_rcd_id = erf.ext_rcd_id
and xrd.ext_rcd_in_file_id = erf.ext_rcd_in_file_id
and erf.ext_file_id = p_ext_file_id
and rcd.ext_rcd_id = erf.ext_rcd_id
and erf.hide_flag = 'N'
and rcd.rcd_type_cd = 'S' ;
select count(*)
from ben_ext_rslt_dtl xrd , ben_ext_rcd_in_file erf
where xrd.ext_rslt_id = p_ext_rslt_id
and xrd.ext_rcd_id = erf.ext_rcd_id
and xrd.ext_rcd_in_file_id = erf.ext_rcd_in_file_id
and erf.ext_file_id = p_ext_file_id
and erf.hide_flag = 'N'
and person_id not in (0, 999999999999) ;
select count(distinct person_id)
from ben_ext_rslt_dtl xrd
where xrd.ext_rslt_id = p_ext_rslt_id
and person_id not in (0, 999999999999);
select count(*)
from ben_ext_rslt_err err
where err.ext_rslt_id = p_ext_rslt_id;
select ran.range_id
,ran.starting_person_action_id
,ran.ending_person_action_id
from ben_batch_ranges ran
where ran.range_status_cd = 'U'
and ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
and rownum < 2
for update of ran.range_status_cd;
select 1
from ben_batch_ranges ran
where ran.range_status_cd = 'E'
and ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID;
select 'X'
from ben_ext_crit_typ xct
,ben_Ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.crit_typ_cd = p_type
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id ;
update ben_batch_ranges ran set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
update ben_batch_ranges
set range_status_cd = 'E'
where range_id = l_range_id;
update ben_batch_ranges
set range_status_cd = 'E'
where range_id = l_range_id;
p_select_statement in out nocopy long) is
l_proc varchar2(80);
select ecv.ext_crit_val_id
from ben_ext_crit_typ ect, ben_ext_crit_val ecv
where ect.crit_typ_cd = 'ADV'
and ect.ext_crit_typ_id = ecv.ext_crit_typ_id
and ect.ext_crit_prfl_id = p_ext_crit_prfl_id
;
select ecc.crit_typ_cd,
ecc.oper_cd,
ecc.val_1,
ecc.val_2
from ben_ext_crit_cmbn ecc
where ecc.ext_crit_val_id = p_ext_crit_val_id
;
p_select_statement := l_sql_string ;
select 'X'
from ben_ext_crit_typ ect
,ben_ext_crit_val ecv
,pay_datetracked_events pde
,pay_dated_tables pdt
where ect.ext_crit_prfl_id = p_ext_crit_prfl_id
and ect.crit_typ_cd = 'CPE'
and ecv.ext_crit_typ_id = ect.ext_crit_typ_id
and pde.event_group_id = to_number(ecv.val_1)
and pde.dated_table_id = pdt.dated_table_id
and pdt.TABLE_NAME in ( 'PAY_LINK_INPUT_VALUES_F'
,'PAY_ELEMENT_LINKS_F'
,'PAY_INPUT_VALUES_F'
,'PAY_ALL_PAYROLLS_F'
,'PAY_ELEMENT_TYPES_F'
,'PAY_GRADE_RULES_F'
,'PAY_USER_COLUMN_INSTANCES_F'
,'FF_GLOBALS_F'
)
;
select pde.dated_table_id,pde.business_group_id ,pde.LEGISLATION_CODE,pde.update_type
from ben_ext_crit_typ ect
,ben_ext_crit_val ecv
,pay_datetracked_events pde
,pay_dated_tables pdt
where ect.ext_crit_prfl_id = p_ext_crit_prfl_id
and ect.crit_typ_cd = 'CPE'
and ecv.ext_crit_typ_id = ect.ext_crit_typ_id
and pde.event_group_id = to_number(ecv.val_1)
and pde.dated_table_id = pdt.dated_table_id
and pdt.TABLE_NAME in ( 'PAY_LINK_INPUT_VALUES_F'
,'PAY_ELEMENT_LINKS_F'
,'PAY_INPUT_VALUES_F'
,'PAY_ALL_PAYROLLS_F'
,'PAY_ELEMENT_TYPES_F'
,'PAY_GRADE_RULES_F'
,'PAY_USER_COLUMN_INSTANCES_F'
,'FF_GLOBALS_F'
)
;
l_update_type varchar2(10) ;
l_sql := ' Select ''X'' From pay_event_updates peu ' ||
' where peu.dated_table_id = '|| i.dated_table_id ||
' and peu.event_type = '''|| i.update_type || '''' ;
' Select xcl.process_event_id from pay_process_events xcl' ||
' where xcl.event_update_id = peu.event_update_id' ||
--- if the event created for a bg then validate the bg with extract bg
' and (peu.business_group_id is null or xcl.business_group_id = peu.business_group_id)';
p_select_statement => l_ADV_sql) ;
select xcv.val_1
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = p_ext_crit_typ;
Procedure build_select_statement
(p_data_typ_cd in varchar2,
p_ext_crit_prfl_id in number default null,
p_ext_dfn_id in number,
p_business_group_id in number,
p_effective_date in date,
p_ext_rslt_id in number ,
p_ext_global_flag in varchar2 default null,
p_eff_start_date in date default null,
p_eff_end_date in date default null,
p_act_start_date in date default null,
p_act_end_date in date default null,
p_select_statement in out nocopy long,
p_penserv_date in date default null) is
--
l_dynamic_sql long;
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PID';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'POR';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PAS';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PLO';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PBG';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PBGR';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PPT';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct
,ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'CCE';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct
,ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'CPE';
Select 'Y',
xct.excld_flag,
xcv.val_1,
xcv.val_2
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'CAD';
Select 'Y',
xct.excld_flag,
xcv.val_1
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PDL';
select 'Y',
xct.excld_flag ,
xct.EXT_CRIT_TYP_ID
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'WPLPR';
Select 'Y',
xct.excld_flag,
xcv.val_1
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'PASGSET';
select 'Y',
xct.excld_flag,
xcv.val_1,
xcv.val_2
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'CED';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'MTP';
select 'Y',
xct.excld_flag,
xcv.val_1,
xcv.val_2
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'MTBSDT';
Select 'Y'
from ben_ext_crit_typ xct
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.crit_typ_cd = 'ADV';
Select 'Y',
xct.excld_flag,
xcv.val_1,
xcv.val_2
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'MSDT';
select 'Y',
xct.excld_flag
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'RRL';
select bed.ext_dfn_id
from ben_ext_dfn bed
where bed.name = 'PQP GB PenServer Standard Interface - Earnings History';
' SELECT distinct(per.person_id) person_id ' ||
' FROM per_all_assignments_f ben_asg ' ||
' ,per_periods_of_service ppos ' ||
' ,per_all_people_f per ' ||
' WHERE per.person_id = ben_asg.person_id (+) ' ||
' AND ben_asg.period_of_service_id = ppos.period_of_service_id ' ||
' AND ((ppos.actual_termination_date is NULL) ' ||
' OR ' ||
' (ppos.actual_termination_date >= to_date('''||to_char((add_months(p_effective_date,-1) + 1) ,'DD/MM/YYYY') ||''',''DD/MM/YYYY''))' ||
' OR ' ||
' ((ppos.actual_termination_date < to_date('''||to_char((add_months(p_effective_date,-1) + 1) ,'DD/MM/YYYY') ||''',''DD/MM/YYYY'') '||
' AND EXISTS (SELECT 1 ' ||
' FROM pay_assignment_actions paa ' ||
' ,pay_run_results prr ' ||
' ,pay_payroll_actions ppa ' ||
' WHERE paa.assignment_id = ben_asg.assignment_id ' ||
' AND paa.assignment_action_id = prr.assignment_action_id ' ||
' AND paa.payroll_action_id = ppa.payroll_action_id ' ||
' AND ppa.effective_date between to_date('''||to_char((add_months(p_effective_date,-1) + 1) ,'DD/MM/YYYY') ||''',''DD/MM/YYYY'') '||
' and last_day(to_date(''' || to_char(p_effective_date,'DD/MM/YYYY') || ''',''DD/MM/YYYY'')) '||
' ) ' ||
' ) ' ||
' ) ' ||
' ) ' ||
' AND NVL(ppos.actual_termination_date, GREATEST(TO_DATE('''||to_char((add_months(p_effective_date,-1) + 1) ,'DD/MM/YYYY') ||''',''DD/MM/YYYY''),ppos.date_start)) ' ||
' BETWEEN ben_asg.effective_start_date AND ben_asg.effective_end_date ' ;
'select distinct(per.person_id) person_id ' ||
'from ' ||
'per_all_people_f per, ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
'per.person_id = ben_asg.person_id (+)' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between per.effective_start_date and per.effective_end_date ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-YYYY'') between ben_asg.effective_start_date (+) ' ||
' and ben_asg.effective_end_date (+) ';
'select distinct(per.person_id) person_id ' ||
'from ' ||
'per_all_people_f per, ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
'per.person_id = ben_asg.person_id (+)' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between per.effective_start_date and per.effective_end_date ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-YYYY'') between ben_asg.effective_start_date (+) ' ||
' and ben_asg.effective_end_date (+) ';
'select distinct(xcl.person_id) person_id ' ||
'from ' ||
'ben_ext_chg_evt_log xcl, ' ||
'per_all_people_f per, ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
'xcl.person_id = per.person_id ' ||
' and xcl.person_id = ben_asg.person_id (+) ' ||
' and xcl.chg_eff_dt between per.effective_start_date and per.effective_end_date ' ||
' and xcl.chg_eff_dt between ben_asg.effective_start_date (+) ' ||
' and ben_asg.effective_end_date (+) '
;
'select distinct(per.person_id) person_id ' ||
'from ' ||
'per_all_people_f per , ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
' ben_asg.person_id = per.person_id ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between per.effective_start_date and per.effective_end_date '
;
'select distinct(ben_asg.person_id) person_id ' ||
'from ' ||
'pay_process_events xcl, ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
' xcl.assignment_id = ben_asg.assignment_id ' ||
' and xcl.effective_date between ben_Asg.effective_start_date and ben_Asg.effective_end_date ' ||
' and xcl.business_group_id = ben_Asg.business_group_id '
;
'select distinct(pcm.person_id) person_id ' ||
'from ' ||
'ben_per_cm_f pcm, ' ||
'ben_per_cm_prvdd_f pcp, ' ||
'per_all_people_f per, ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
'pcm.per_cm_id = pcp.per_cm_id ' ||
' and pcm.person_id = per.person_id ' ||
' and per.person_id = ben_asg.person_id (+) ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between per.effective_start_date and per.effective_end_date ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between pcm.effective_start_date and pcm.effective_end_date ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between pcp.effective_start_date and pcp.effective_end_date ' ||
'and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between ben_asg.effective_start_date (+) ' ||
'and ben_asg.effective_end_date (+) ';
'select distinct(cpi.person_id) person_id ' ||
'from ' ||
'ben_cwb_person_info cpi, ' ||
'per_all_people_f per, ' ||
'per_all_assignments_f ben_asg ' ||
'where ' ||
'cpi.person_id = per.person_id ' ||
' and cpi.person_id = ben_asg.person_id (+) ' ||
-- ' and cpi.business_group_id = ' || p_business_group_id ||
' and cpi.effective_date between per.effective_start_date and per.effective_end_date ' ||
' and cpi.effective_date between ben_asg.effective_start_date (+) ' ||
' and ben_asg.effective_end_date (+) '
;
'(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
'xct.crit_typ_cd = ''PID'') ';
'( SELECT /*+ ORDERED USE_NL (xct, xcv, aset) */ 1 FROM ben_ext_crit_typ xct, ben_ext_crit_val xcv, hr_assignment_sets aset ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
' xct.crit_typ_cd = ''PASGSET'' and to_number(xcv.val_1) = aset.assignment_set_id ' ||
' and (not exists (select 1 from hr_assignment_set_amendments hasa ' ||
' where hasa.assignment_set_id = aset.assignment_set_id and hasa.include_or_exclude = ''I'') ' ||
' or exists (select 1 from hr_assignment_set_amendments hasa ' ||
' where hasa.assignment_set_id=aset.assignment_set_id '||
' and hasa.assignment_id = ben_asg.assignment_id and hasa.include_or_exclude = ''I'' ) '||
' ) ' ||
' and not exists (select 1 from hr_assignment_set_amendments hasa ' ||
' where hasa.assignment_set_id=aset.assignment_set_id'||
' and hasa.assignment_id = ben_asg.assignment_id and hasa.include_or_exclude = ''E'') )) ' ;
'(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
'xct.crit_typ_cd = ''POR''))) ';
'(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1)) |
' from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and ben_asg.assignment_status_type_id = to_number(xcv.val_1) '||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
' and xct.crit_typ_cd = ''PAS''))) ';
'(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1)) ' ||
' from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and ben_asg.location_id = to_number(xcv.val_1) '||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
'xct.crit_typ_cd = ''PLO''))) ';
'(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
'xct.crit_typ_cd = ''PBG''))) ';
'(select ''x'' from per_all_people_f per , ben_ext_crit_typ xct, ben_ext_crit_val xcv where ' ||
' per.person_id = ben_asg.person_id ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between per.effective_start_date and per.effective_end_date ' ||
' and per.benefit_group_id = to_number(xcv.val_1) '||
' and xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
' and xct.crit_typ_cd = ''PBG'') )) ';
'(select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
'xct.crit_typ_cd = ''PBGR'') ';
' (select ptu.person_id from per_person_type_usages_f ptu ' ;
' (select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1)) ' ||
' from ben_ext_crit_typ xct , ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
' xct.crit_typ_cd = ''PPT'')) ';
' (select ptu.person_id from per_person_type_usages_f ptu where ptu.person_id = per.person_id ' ;
' (select ptu.person_id from per_person_type_usages_f ptu where ptu.person_id = ben_Asg.person_id ' ;
' (select to_number(decode(ltrim (xcv.val_1,''0123456789''),NULL,xcv.val_1,-1))
from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
' and ptu.person_type_id = to_number(xcv.val_1) and '||
' xct.crit_typ_cd = ''PPT'')) ';
'(select xcv.val_1 from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id '||
' and xct.crit_typ_cd = ''CCE'') ';
' (select pde.event_group_id ' ||
' from pay_datetracked_events pde, ' ||
' pay_event_updates peu ' ||
-- BEGIN for Pensrv
' ,pay_dated_tables pdt '||
-- END for Pensrv
' where ' ||
--' and (pde.business_group_id = bg.organization_id OR (pde.business_group_id IS NULL ' ||
--' and (pde.legislation_code is null or pde.legislation_code = bg.org_information9) ) ) ' ||
--' and xct.crit_typ_cd = ''CPE'' and xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
--' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id ' ||
--' and pde.event_group_id = to_number(xcv.val_1) ' ||
' pde.event_group_id in ' || l_crit_val_all ||
' and xcl.event_update_id = peu.event_update_id ' ||
' and peu.dated_table_id = pde.dated_table_id ' ||
--' and (pde.column_name is null or pde.column_name = peu.column_name) ' ||
--' and (peu.business_group_id = pde.business_group_id OR (peu.business_group_id is null ' ||
--' and (peu.legislation_code is null or peu.legislation_code = pde.legislation_code)))' ||
-- BEGIN for Pensrv
-- Modified the below sql to cater for purge events more efficiently
' AND pde.dated_table_id = pdt.dated_table_id ' ||
' AND ((pdt.table_name = '||'''PAY_ELEMENT_ENTRIES_F'''||
' AND (EXISTS (SELECT 1 ' ||
' FROM pay_event_group_usages pegu ' ||
' ,pay_element_type_rules petr ' ||
' ,pay_element_entries_f peef ' ||
' WHERE pegu.event_group_id = pde.event_group_id ' ||
' AND ((xcl.noted_value IS NOT NULL ' ||
' AND xcl.noted_value = petr.element_type_id ' ||
' AND petr.element_set_id = pegu.element_set_id ' ||
' ) ' ||
' OR ' ||
' (xcl.surrogate_key = peef.element_entry_id ' ||
' AND peef.assignment_id = ben_asg.assignment_id ' ||
' AND peef.element_type_id = petr.element_type_id ' ||
' AND petr.element_set_id = pegu.element_set_id ' ||
' ) ' ||
' ) ' ||
' ) ' ||
' ) ' ||
' ) ' ||
' OR ' ||
' (pdt.table_name = '||'''PAY_ELEMENT_ENTRY_VALUES_F'''||
' AND (EXISTS (SELECT 1 ' ||
' FROM pay_event_group_usages pegu ' ||
' ,pay_element_type_rules petr ' ||
' ,pay_element_entries_f peef ' ||
' ,pay_element_entry_values_f peevf ' ||
' WHERE peef.assignment_id = ben_asg.assignment_id ' ||
' AND peef.element_type_id = petr.element_type_id ' ||
' AND petr.element_set_id = pegu.element_set_id ' ||
' AND pegu.event_group_id = pde.event_group_id ' ||
' AND peef.element_entry_id = peevf.element_entry_id ' ||
' AND xcl.surrogate_key = peevf.element_entry_value_id ' ||
' ) ' ||
' ) ' ||
' ) ' ||
' OR ' ||
' (pdt.table_name not in (' || '''PAY_ELEMENT_ENTRIES_F''' ||','|| '''PAY_ELEMENT_ENTRY_VALUES_F''' ||
' )) ' ||
' ) ' ||
-- End for pensrv
' ) ' ;
' (select pde.event_group_id ' ||
' from pay_datetracked_events pde, ' ||
' pay_event_updates peu ' ||
' where ' ||
--' and (pde.business_group_id = bg.organization_id OR (pde.business_group_id IS NULL ' ||
--' and (pde.legislation_code is null or pde.legislation_code = bg.org_information9) ) ) ' ||
--' and xct.crit_typ_cd = ''CPE'' and xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
--' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id ' ||
--' and pde.event_group_id = to_number(xcv.val_1) ' ||
' pde.event_group_id in ' || l_crit_val_all ||
' and xcl.event_update_id = peu.event_update_id ' ||
' and peu.dated_table_id = pde.dated_table_id ' ||
--' and (pde.column_name is null or pde.column_name = peu.column_name) ' ||
--' and (peu.business_group_id = pde.business_group_id OR (peu.business_group_id is null ' ||
--' and (peu.legislation_code is null or peu.legislation_code = pde.legislation_code)))' ||
' ) ' ;
p_select_statement => l_dynamic_ADV_sql) ;
p_select_statement => l_dynamic_ADV_sql) ;
'(select xcv.val_1 from ben_ext_crit_typ xct ,ben_ext_crit_val xcv ' ||
' where xct.ext_crit_prfl_id = ' || p_ext_crit_prfl_id ||
' and xct.ext_crit_typ_id = xcv.ext_crit_typ_id and '||
'xct.crit_typ_cd = ''MTP'') ';
' (select 1 from per_jobs job where job.job_id = ben_asg.job_id and ' ||
' exists ( select group_val_01 from ben_ext_rslt_dtl erd where ' ||
' erd.ext_rslt_id = ' || p_ext_rslt_id ||
' and group_val_01 is not null and group_val_01 = job.name ) ) ' ;
' (select 1 from HR_ALL_POSITIONS_F pos where pos.position_id = ben_asg.position_id and '||
' to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between pos.effective_start_date and pos.effective_end_date and ' ||
' exists ( select group_val_01 from ben_ext_rslt_dtl erd where ' ||
' erd.ext_rslt_id = ' || p_ext_rslt_id ||
' and group_val_01 is not null and group_val_01 = pos.name )) ' ;
' (select 1 from pay_payrolls pay where pay.payroll_id = ben_asg.payroll_id and ' ||
' exists ( select group_val_01 from ben_ext_rslt_dtl erd where ' ||
' erd.ext_rslt_id = ' || p_ext_rslt_id ||
' and group_val_01 is not null and group_val_01 = pay.payroll_name )) ' ;
' (select 1 from hr_locations_all loc where loc.location_id = ben_asg.location_id ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between loc.effective_start_date and loc.effective_end_date ' ||
' and exists ( select group_val_01 from ben_ext_rslt_dtl erd where ' ||
' erd.ext_rslt_id = ' || p_ext_rslt_id ||
' and group_val_01 is not null and group_val_01 = loc.location_code )) ' ;
' ( select 1 from per_time_periods tim where ben_asg.payroll_id = tim.payroll_id ' ||
' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') ||
''',''DD-MM-RRRR'') between tim.start_date and tim.end_date ' ||
' and ben_asg.effective_start_date<=tim.end_date and ben_asg.effective_end_date>= tim.start_date) ';
' (select 1 from per_grades grd where grd.grade_id = ben_asg.grade_id ' ||
' and exists ( select group_val_01 from ben_ext_rslt_dtl erd where ' ||
' erd.ext_rslt_id = ' || p_ext_rslt_id ||
' and group_val_01 is not null and group_val_01 = grd.name )) ' ;
' ( select 1 from ben_ext_crit_val cvl , ben_enrt_perd enp , ben_per_in_ler pil' ||
' where cpi.group_per_in_ler_id = pil.per_in_ler_id and pil.group_pl_id = cvl.val_2 ' ||
' and cvl.EXT_CRIT_TYP_ID = ' || l_wlpr_EXT_CRIT_TYP_ID ||
' and enp.enrt_perd_id = cvl.val_1 and pil.lf_evt_ocrd_dt = enp.ASND_LF_EVT_DT ) ' ;
p_select_statement := l_dynamic_sql;
end build_select_statement;
select asg.ASSIGNMENT_TYPE ,asg.assignment_id,asg.business_group_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
;
select 'x'
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date ;
select pos.position_id,pos.job_id
from HR_ALL_POSITIONS_F pos
where pos.business_group_id = p_bg_id
and pos.organization_id = p_org_id
and p_effective_date between pos.EFFECTIVE_START_DATE
and nvl(pos.EFFECTIVE_END_DATE ,p_effective_date)
;
select job.job_id
from per_jobs_vl job
where job.business_group_id = p_bg_id
and p_effective_date between job.date_from and nvl(job.date_to,p_effective_date)
;
select loc.location_id
from hr_locations_all loc
where loc.business_group_id = p_bg_id
or loc.business_group_id is null -- for global location
;
select org.organization_id
,org.name
from hr_all_organization_units_vl org
where org.business_group_id = p_bg_id
and p_effective_date between org.date_from and nvl(org.date_to,p_effective_date) ;
select payroll_id
from pay_payrolls_f pay
where pay.business_group_id = p_bg_id
-- and pay.organization_id = p_org_id
and p_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE
;
select grade_id
from per_grades_vl grd
where grd.business_group_id = p_bg_id
and p_effective_date between grd.date_from and nvl(grd.date_to,p_effective_date)
;
select
distinct pos.position_id position_id
from ghr_pa_history gph ,
HR_ALL_POSITIONS_F pos
where ( (gph.table_name = 'HR_ALL_POSITIONS_F'
and pos.POSITION_ID = gph.information1 )
or (gph.table_name = 'PER_POSITION_EXTRA_INFO'
and pos.position_id = gph.information4 -- info4 is position_id
and gph.information5 in ('GHR_US_POS_GRP1','GHR_US_POS_GRP2' ,'GHR_US_POS_VALID_GRADE','GHR_US_POS_GRP3',
'GHR_US_POS_OBLIG', 'GHR_US_POS_MASS_ACTIONS', 'GHR_US_POSITION_LANGUAGE',
'GHR_US_POSITION_INTERDISC', 'GHR_US_POSITION_DESCRIPTION' )
)
)
and pos.business_group_id = p_bg_id
and pos.organization_id = p_org_id
and gph.effective_date between pos.EFFECTIVE_START_DATE
and pos.EFFECTIVE_END_DATE
and ( p_subhdr_ghr_from_dt is null
or (
trunc(gph.effective_date) between p_subhdr_ghr_from_dt and nvl(p_subhdr_ghr_to_dt, p_subhdr_ghr_from_dt)
or
( trunc(gph.process_date) between p_subhdr_ghr_from_dt and nvl(p_subhdr_ghr_to_dt, p_subhdr_ghr_from_dt)
and trunc(gph.effective_date) <= nvl(p_subhdr_ghr_to_dt, p_subhdr_ghr_from_dt)
)
)
)
;
select
xct.excld_flag,
xcv.val_1,
xcv.val_2
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'CED';
Select
xct.excld_flag,
xcv.val_1,
xcv.val_2
from ben_ext_crit_typ xct,
ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.crit_typ_cd = 'CAD';
select business_group_id , name
from per_business_groups
where p_ext_global_flag = 'Y'
or business_group_id = p_business_group_id
;
select business_group_id , name
from per_business_groups
where business_group_id = p_business_group_id
;
,p_person_selection_rl in number default null
) is
--
-- if directory not specified, then grab the first
-- in the utl_file_dir path
--
/* cursor c_get_dflt_dir is
select substr(value,1,instr(value,',')-1)
from gv$system_parameter
where name = 'utl_file_dir'
and value is not null ; */
select decode (instr(ltrim(value),','), 0 ,
ltrim(value),
substrb(value,1,instr(ltrim(value),',')-1) )
from v$parameter where name = 'utl_file_dir';
l_select_statement varchar2(32000);
select object_version_number
from ben_ext_rslt
where ext_rslt_id = p_ext_rslt_id ;
select 'Y'
from ben_ext_rcd a,
ben_ext_rcd_in_file b
where a.ext_rcd_id = b.ext_rcd_id
and b.ext_file_id = p_ext_file_id
and a.rcd_type_cd = 'D'
;
select 'x' from
ben_Ext_rslt_err
where typ_cd = 'E'
and ext_rslt_id = c_ext_rslt_id
;
select application_short_name ,
template_code ,
default_language,
default_territory
from xdo_templates_b
where template_id = c_xdo_id ;
,p_program_update_date => sysdate
,p_request_id => l_conc_request_id
,p_output_type => l_output_type
,p_xdo_template_id => l_xdo_template_id
,p_object_version_number => l_xrs_object_version_number
,p_effective_date => l_effective_date);
,p_program_update_date => sysdate
,p_request_id => l_conc_request_id
,p_output_type => l_output_type
,p_xdo_template_id => l_xdo_template_id
,p_object_version_number => l_xrs_object_version_number
,p_effective_date => l_effective_date);
,p_comp_selection_rl => NULL
,p_person_selection_rl => p_person_selection_rl
,p_ler_id => NULL
,p_organization_id => NULL
,p_benfts_grp_id => NULL
,p_location_id => NULL
,p_pstl_zip_rng_id => NULL
,p_rptg_grp_id => NULL
,p_pl_typ_id => NULL
,p_opt_id => NULL
,p_eligy_prfl_id => NULL
,p_vrbl_rt_prfl_id => NULL
,p_legal_entity_id => NULL
,p_payroll_id => NULL
,p_request_id => nvl(l_conc_request_id, fnd_global.conc_request_id )
,p_inelg_action_cd => 'X' --Unique for extract benefit action recs
,p_debug_messages_flag => 'N'
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date);
build_select_statement
(p_data_typ_cd => l_data_typ_cd,
p_ext_crit_prfl_id => l_ext_crit_prfl_id,
p_ext_dfn_id => p_ext_dfn_id,
p_business_group_id => p_business_group_id,
p_effective_date => l_effective_date,
p_ext_rslt_id => l_ext_rslt_id ,
p_ext_global_flag => nvl(l_ext_global_flag, 'N') ,
p_eff_start_date => l_eff_start_date,
p_eff_end_date => l_eff_end_date,
p_act_start_date => l_act_start_date,
p_act_end_date => l_act_end_date,
p_select_statement => l_select_statement, --out
p_penserv_date => p_penserv_date);
open PersonCur for l_select_statement;
FOR i in 1..LENGTH(l_select_statement) LOOP
--
IF mod(i,80)=0 OR i=LENGTH(l_select_statement) THEN
--
fnd_file.put_line(fnd_file.log,' ' ||substr(l_select_statement,l_current_loc+1,i-l_current_loc));
if p_person_selection_rl is not null then
--
hr_utility.set_location ('Calling Ben_batch_utils.person_selection_rule...',100);
ben_batch_utils.person_selection_rule
(p_person_id => l_rec.person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id => p_person_selection_rl
,p_effective_date => l_effective_date);
select ben_person_actions_s.nextval
into l_person_action_id(l_num_rows)
from sys.dual;
insert into ben_person_actions
(person_action_id,
person_id,
ler_id,
benefit_action_id,
action_status_cd,
object_version_number)
values
(l_person_action_id(l_count),
l_person_id(l_count),
null,
l_benefit_action_id,
'U',
1);
select ben_batch_ranges_s.nextval
into l_range_id
from sys.dual;
insert into ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
values
(l_range_id,
l_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
l_person_action_id.delete;
l_person_id.delete;
insert into ben_person_actions
(person_action_id,
person_id,
ler_id,
benefit_action_id,
action_status_cd,
object_version_number)
values
(l_person_action_id(l_count),
l_person_id(l_count),
null,
l_benefit_action_id,
'U',
1);
select ben_batch_ranges_s.nextval
into l_range_id
from sys.dual;
insert into ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
values
(l_range_id,
l_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
l_person_action_id.delete;
l_person_id.delete;
select count(*)
into l_person_cnt
from ben_person_actions
where benefit_action_id = p_benefit_action_id
and ACTION_STATUS_CD='U';
select count(*)
into l_num_range
from ben_batch_ranges
where benefit_action_id = p_benefit_action_id
and range_status_cd='U';
ben_ext_rslt_api.update_ext_rslt
(p_validate => false
,p_ext_rslt_id => l_ext_rslt_id
,p_run_end_dt => sysdate
,p_ext_stat_cd => l_ext_stat_cd
,p_tot_rec_num => g_rec_cnt
,p_tot_per_num => g_per_cnt
,p_tot_err_num => g_err_cnt
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_request_id => l_conc_request_id
,p_object_version_number => l_xrs_object_version_number
,p_effective_date => l_effective_date);
ben_ext_rslt_api.update_ext_rslt
(p_validate => false
,p_ext_rslt_id => l_ext_rslt_id
,p_run_end_dt => sysdate
,p_ext_stat_cd => 'S'
,p_tot_rec_num => g_rec_cnt
,p_tot_per_num => g_per_cnt
,p_tot_err_num => g_err_cnt
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_request_id => l_conc_request_id
,p_object_version_number => l_xrs_object_version_number
,p_effective_date => l_effective_date);
update_ht_strt_end_dt(l_ext_rslt_id);
ben_ext_rslt_api.update_ext_rslt
(p_validate => false
,p_ext_rslt_id => l_ext_rslt_id
,p_run_end_dt => sysdate
,p_ext_stat_cd => 'F'
,p_tot_rec_num => g_rec_cnt
,p_tot_per_num => g_per_cnt
,p_tot_err_num => g_err_cnt
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_request_id => l_conc_request_id
,p_object_version_number => l_xrs_object_version_number
,p_effective_date => l_effective_date);
ben_ext_rslt_api.update_ext_rslt
(p_validate => false
,p_ext_rslt_id => l_ext_rslt_id
,p_run_end_dt => sysdate
,p_ext_stat_cd => 'F'
,p_tot_rec_num => g_rec_cnt
,p_tot_per_num => g_per_cnt
,p_tot_err_num => g_err_cnt
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_request_id => l_conc_request_id
,p_object_version_number => l_xrs_object_version_number
,p_effective_date => l_effective_date);
select person_id
from ben_person_actions act
where act.person_action_id between p_start_person_action_id
and p_end_person_action_id
and act.benefit_action_id = p_benefit_action_id
and action_status_cd <> 'P' ;
select ext_rslt_dtl_id,
object_version_number
from ben_ext_rslt_dtl
where person_id = c_person_id
and ext_rslt_id = p_ext_rslt_id ;
select ext_rslt_err_id,
object_version_number
from ben_ext_rslt_err
where person_id = c_person_id
and ext_rslt_id = p_ext_rslt_id ;
ben_EXT_RSLT_DTL_api.delete_EXT_RSLT_DTL
(p_ext_rslt_dtl_id => xrdt.ext_rslt_dtl_id
,p_object_version_number => l_object_version_number
);
ben_EXT_RSLT_ERR_api.delete_EXT_RSLT_ERR
(p_ext_rslt_err_id => xrer.ext_rslt_err_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
select pgm_id
,pl_id
,business_group_id
,process_date
from ben_benefit_actions ben
where ben.benefit_action_id = p_benefit_action_id;
select *
from ben_batch_ranges ran
where ran.range_status_cd = 'E'
and ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID;
update ben_batch_ranges
set range_Status_cd = 'U'
where range_id = rng.range_id ;
SELECT ea.ext_dfn_type
FROM ben_ext_dfn ed,
per_business_groups bg,
pqp_extract_attributes ea
WHERE ((bg.business_group_id = ed.business_group_id)OR
(bg.legislation_code = ed.legislation_code) OR
(ed.business_group_id IS NULL AND
ed.legislation_code IS NULL)
)
AND bg.business_group_id = c_business_group_id
AND ed.ext_dfn_id = ea.ext_dfn_id
AND ed.ext_dfn_id = c_ext_dfn_id
AND ea.ext_dfn_type in
('PEN_FPR', 'PEN_CHG', 'FID_PTC','FID_CAC',
'FID_ERC', 'FID_LPY', 'FID_ATE','FID_CHG',
'GBL_FPR', 'GBL_CHG', 'GBL_MUL_CHG');
SELECT bba.pgm_id
,bba.pl_id
,bba.benefit_action_id
,bba.business_group_id
,bba.process_date
,bba.request_id
FROM ben_benefit_actions bba
WHERE bba.pl_id = c_ext_rslt_id
AND bba.pgm_id = c_ext_dfn_id
AND bba.business_group_id = c_business_group_id;
SELECT *
FROM fnd_concurrent_requests
WHERE request_id = c_req_id;
DELETE FROM pay_us_rpt_totals
WHERE tax_unit_id = p_conc_req_id
AND attribute5 = '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 -- Ext Def 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 -- Status
)
VALUES
(l_session_id
,'US Pension Extracts'
,p_business_group_id -- p_business_group_id
,p_business_group_id -- Org Id
,p_ext_dfn_id -- location id for key
,l_conc_request_id -- New Conc Req Id.
,l_conc_params.argument2 -- p_ext_dfn_id
,l_conc_params.argument9 -- p_element_set_id
,l_conc_params.argument12 -- p_element_type_id
,l_conc_params.argument17 -- p_payroll_id
,l_conc_params.argument16 -- p_gre_id
,l_conc_params.argument20 -- p_con_set
,l_conc_params.argument7 -- p_selection_criteria
,l_conc_params.argument5 -- p_reporting_dimension
,l_conc_params.argument14 -- p_start_date
,l_conc_params.argument15 -- p_end_date
,'EXTRACT_RUNNING' -- Status
);
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 -- Ext Def 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
)
VALUES
(l_session_id -- session id
,'Global Pension Extracts'
,p_business_group_id -- p_business_group_id
,p_business_group_id -- Org Id
,p_ext_dfn_id -- ext dfn id for key purpose
,l_conc_request_id -- New Conc Req Id.
,l_conc_params.argument2 -- p_ext_dfn_id
,l_conc_params.argument8 -- p_element_set_id
,l_conc_params.argument10 -- p_element_type_id
,l_conc_params.argument15 -- p_payroll_id
,l_conc_params.argument14 -- p_gre_id
,l_conc_params.argument18 -- p_con_set
,l_conc_params.argument6 -- p_selection_criteria
,l_conc_params.argument4 -- p_reporting_dimension
,l_conc_params.argument12 -- p_start_date
,l_conc_params.argument13 -- p_end_date
,l_conc_params.argument20 -- p_org_id
,l_conc_params.argument21 -- p_person_type_id
,l_conc_params.argument22 -- p_location_id
);
select pgm_id
,pl_id
,benefit_action_id
,business_group_id
,process_date
from ben_benefit_actions ben
where ben.request_id = p_concurrent_request_id;
select *
from ben_batch_ranges ran
where ran.range_status_cd in ( 'E', 'W')
and ran.benefit_action_id = l_benefit_action_id;
update ben_batch_ranges
set range_status_cd = 'U'
where range_id = rng.range_id;
select null
from fnd_concurrent_requests fnd
where /*fnd.phase_code <> 'C' and */
fnd.request_id = p_request_id;
select name
from per_business_groups bg
where business_group_id = p_business_group_id ;
select name,business_group_id
from ben_ext_file
where ext_file_id = p_extract_file_id ;