[Home] [Help]
634: ,ben_ext_rcd rcd
635: ,ben_ext_rcd_in_file rin
636: ,ben_ext_dfn dfn
637: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
638: FROM pqp_extract_attributes
639: WHERE ext_dfn_type = 'NL_FPR')
640: and dtl.person_id = g_person_id
641: and ext_stat_cd = 'A'
642: AND TRUNC(res.eff_dt)< g_extract_params(p_business_group_id).extract_start_date
2476: ,ben_ext_rcd rcd
2477: ,ben_ext_rcd_in_file rin
2478: ,ben_ext_dfn dfn
2479: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
2480: FROM pqp_extract_attributes
2481: WHERE ext_dfn_type = 'NL_FPR')
2482: AND val_05 <> '00000000' -- Ptpn St Date
2483: AND val_11 IS NOT NULL -- Kind of Ptpn
2484: AND val_12 IS NOT NULL -- Value of Ptpn
3259: CURSOR c_sent_to_abp (c_eff_dt IN DATE) IS
3260: SELECT 1
3261: FROM ben_ext_rslt res
3262: WHERE ext_dfn_id IN (SELECT ext_dfn_id
3263: FROM pqp_extract_attributes
3264: WHERE ext_dfn_type = 'NL_FPR')
3265: AND ext_stat_cd = 'A'
3266: AND EXISTS ( SELECT 1 FROM ben_ext_rslt_dtl dtl
3267: WHERE dtl.ext_rslt_id = res.ext_rslt_id
3277: ,ben_ext_rcd rcd
3278: ,ben_ext_rcd_in_file rin
3279: ,ben_ext_dfn dfn
3280: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3281: FROM pqp_extract_attributes
3282: WHERE ext_dfn_type = 'NL_FPR')
3283: and dtl.person_id = g_person_id
3284: and ext_stat_cd = 'A'
3285: AND TRUNC(res.eff_dt)= trunc(c_eff_dt)
3302: ,ben_ext_rcd rcd
3303: ,ben_ext_rcd_in_file rin
3304: ,ben_ext_dfn dfn
3305: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3306: FROM pqp_extract_attributes
3307: WHERE ext_dfn_type = 'NL_FPR')
3308: and dtl.person_id = g_person_id
3309: and ext_stat_cd = 'A'
3310: AND TRUNC(res.eff_dt)< trunc(c_eff_dt)
3327: ,ben_ext_rcd rcd
3328: ,ben_ext_rcd_in_file rin
3329: ,ben_ext_dfn dfn
3330: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3331: FROM pqp_extract_attributes
3332: WHERE ext_dfn_type = 'NL_FPR')
3333: and dtl.person_id = g_person_id
3334: and ext_stat_cd = 'A'
3335: AND TRUNC(res.eff_dt)> TRUNC(c_eff_dt)
3352: ,ben_ext_rcd rcd
3353: ,ben_ext_rcd_in_file rin
3354: ,ben_ext_dfn dfn
3355: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3356: FROM pqp_extract_attributes
3357: WHERE ext_dfn_type = 'NL_FPR')
3358: and dtl.person_id = g_person_id
3359: and ext_stat_cd = 'A'
3360: AND TRUNC(res.eff_dt)= trunc(c_eff_dt)
3379: ,ben_ext_rcd rcd
3380: ,ben_ext_rcd_in_file rin
3381: ,ben_ext_dfn dfn
3382: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3383: FROM pqp_extract_attributes
3384: WHERE ext_dfn_type = 'NL_FPR')
3385: and dtl.person_id = g_person_id
3386: and ext_stat_cd = 'A'
3387: AND TRUNC(res.eff_dt)>= trunc(c_eff_dt)
5664: -- Cursor to get the extract definition type
5665: --
5666: CURSOR csr_ext_attr (c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE) IS
5667: SELECT ext_dfn_type
5668: FROM pqp_extract_attributes
5669: WHERE ext_dfn_id = c_ext_dfn_id;
5670: --
5671: -- Based on result id and Ext. Dfn Id, get the con. request id
5672: --
5878: l_rej_hf_ee NUMBER;
5879: l_rej_old_ee NUMBER;
5880: l_ext_params csr_ext_params%ROWTYPE;
5881: l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
5882: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
5883: i per_all_assignments_f.business_group_id%TYPE;
5884: l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
5885: l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
5886: l_return_value Varchar2(2) :='N';
8302: ,ben_ext_rcd rcd
8303: ,ben_ext_rcd_in_file rin
8304: ,ben_ext_dfn dfn
8305: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
8306: FROM pqp_extract_attributes
8307: WHERE ext_dfn_type = 'NL_FPR')
8308: AND dtl.person_id = g_person_id
8309: AND ext_stat_cd = 'A'
8310: AND TRUNC(res.eff_dt) <
8491: ,ben_ext_rcd rcd
8492: ,ben_ext_rcd_in_file rin
8493: ,ben_ext_dfn dfn
8494: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
8495: FROM pqp_extract_attributes
8496: WHERE ext_dfn_type = 'NL_FPR')
8497: AND dtl.person_id = g_person_id
8498: AND ext_stat_cd = 'A'
8499: AND TRUNC(res.eff_dt) <
8902: ,ben_ext_rcd rcd
8903: ,ben_ext_rcd_in_file rin
8904: ,ben_ext_dfn dfn
8905: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
8906: FROM pqp_extract_attributes
8907: WHERE ext_dfn_type = 'NL_FPR')
8908: and dtl.person_id = g_person_id
8909: and ext_stat_cd = 'A'
8910: AND TRUNC(res.eff_dt) BETWEEN c_start_of_yr
11082: ,ben_ext_rcd rcd
11083: ,ben_ext_rcd_in_file rin
11084: ,ben_ext_dfn dfn
11085: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
11086: FROM pqp_extract_attributes
11087: WHERE ext_dfn_type = 'NL_FPR')
11088: and dtl.person_id = g_person_id
11089: and ext_stat_cd = 'A'
11090: AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
11106: ,ben_ext_rcd rcd
11107: ,ben_ext_rcd_in_file rin
11108: ,ben_ext_dfn dfn
11109: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
11110: FROM pqp_extract_attributes
11111: WHERE ext_dfn_type = 'NL_FPR')
11112: and dtl.person_id = g_person_id
11113: and ext_stat_cd = 'A'
11114: AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
11186: ,ben_ext_rcd rcd
11187: ,ben_ext_rcd_in_file rin
11188: ,ben_ext_dfn dfn
11189: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
11190: FROM pqp_extract_attributes
11191: WHERE ext_dfn_type = 'NL_FPR')
11192: and dtl.person_id = g_person_id
11193: and ext_stat_cd = 'A'
11194: AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
14716: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
14717: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
14718: l_valid_action Varchar2(2);
14719: i per_all_assignments_f.business_group_id%TYPE;
14720: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14721: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14722: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14723: l_organization_id per_all_assignments_f.organization_id%TYPE;
14724: l_business_group_id per_all_assignments_f.business_group_id%TYPE;