[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
2455: ,ben_ext_rcd rcd
2456: ,ben_ext_rcd_in_file rin
2457: ,ben_ext_dfn dfn
2458: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
2459: FROM pqp_extract_attributes
2460: WHERE ext_dfn_type = 'NL_FPR')
2461: AND val_05 <> '00000000' -- Ptpn St Date
2462: AND val_11 IS NOT NULL -- Kind of Ptpn
2463: AND val_12 IS NOT NULL -- Value of Ptpn
3238: CURSOR c_sent_to_abp (c_eff_dt IN DATE) IS
3239: SELECT 1
3240: FROM ben_ext_rslt res
3241: WHERE ext_dfn_id IN (SELECT ext_dfn_id
3242: FROM pqp_extract_attributes
3243: WHERE ext_dfn_type = 'NL_FPR')
3244: AND ext_stat_cd = 'A'
3245: AND EXISTS ( SELECT 1 FROM ben_ext_rslt_dtl dtl
3246: WHERE dtl.ext_rslt_id = res.ext_rslt_id
3256: ,ben_ext_rcd rcd
3257: ,ben_ext_rcd_in_file rin
3258: ,ben_ext_dfn dfn
3259: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3260: FROM pqp_extract_attributes
3261: WHERE ext_dfn_type = 'NL_FPR')
3262: and dtl.person_id = g_person_id
3263: and ext_stat_cd = 'A'
3264: AND TRUNC(res.eff_dt)= trunc(c_eff_dt)
3281: ,ben_ext_rcd rcd
3282: ,ben_ext_rcd_in_file rin
3283: ,ben_ext_dfn dfn
3284: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3285: FROM pqp_extract_attributes
3286: WHERE ext_dfn_type = 'NL_FPR')
3287: and dtl.person_id = g_person_id
3288: and ext_stat_cd = 'A'
3289: AND TRUNC(res.eff_dt)< trunc(c_eff_dt)
3306: ,ben_ext_rcd rcd
3307: ,ben_ext_rcd_in_file rin
3308: ,ben_ext_dfn dfn
3309: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3310: FROM pqp_extract_attributes
3311: WHERE ext_dfn_type = 'NL_FPR')
3312: and dtl.person_id = g_person_id
3313: and ext_stat_cd = 'A'
3314: AND TRUNC(res.eff_dt)> TRUNC(c_eff_dt)
3331: ,ben_ext_rcd rcd
3332: ,ben_ext_rcd_in_file rin
3333: ,ben_ext_dfn dfn
3334: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3335: FROM pqp_extract_attributes
3336: WHERE ext_dfn_type = 'NL_FPR')
3337: and dtl.person_id = g_person_id
3338: and ext_stat_cd = 'A'
3339: AND TRUNC(res.eff_dt)= trunc(c_eff_dt)
3358: ,ben_ext_rcd rcd
3359: ,ben_ext_rcd_in_file rin
3360: ,ben_ext_dfn dfn
3361: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
3362: FROM pqp_extract_attributes
3363: WHERE ext_dfn_type = 'NL_FPR')
3364: and dtl.person_id = g_person_id
3365: and ext_stat_cd = 'A'
3366: AND TRUNC(res.eff_dt)>= trunc(c_eff_dt)
5475: -- Cursor to get the extract definition type
5476: --
5477: CURSOR csr_ext_attr (c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE) IS
5478: SELECT ext_dfn_type
5479: FROM pqp_extract_attributes
5480: WHERE ext_dfn_id = c_ext_dfn_id;
5481: --
5482: -- Based on result id and Ext. Dfn Id, get the con. request id
5483: --
5626: l_rej_hf_ee NUMBER;
5627: l_rej_old_ee NUMBER;
5628: l_ext_params csr_ext_params%ROWTYPE;
5629: l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
5630: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
5631: i per_all_assignments_f.business_group_id%TYPE;
5632: l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
5633: l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
5634: l_return_value Varchar2(2) :='N';
7963: ,ben_ext_rcd rcd
7964: ,ben_ext_rcd_in_file rin
7965: ,ben_ext_dfn dfn
7966: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
7967: FROM pqp_extract_attributes
7968: WHERE ext_dfn_type = 'NL_FPR')
7969: AND dtl.person_id = g_person_id
7970: AND ext_stat_cd = 'A'
7971: AND TRUNC(res.eff_dt) <
8152: ,ben_ext_rcd rcd
8153: ,ben_ext_rcd_in_file rin
8154: ,ben_ext_dfn dfn
8155: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
8156: FROM pqp_extract_attributes
8157: WHERE ext_dfn_type = 'NL_FPR')
8158: AND dtl.person_id = g_person_id
8159: AND ext_stat_cd = 'A'
8160: AND TRUNC(res.eff_dt) <
8563: ,ben_ext_rcd rcd
8564: ,ben_ext_rcd_in_file rin
8565: ,ben_ext_dfn dfn
8566: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
8567: FROM pqp_extract_attributes
8568: WHERE ext_dfn_type = 'NL_FPR')
8569: and dtl.person_id = g_person_id
8570: and ext_stat_cd = 'A'
8571: AND TRUNC(res.eff_dt) BETWEEN c_start_of_yr
10743: ,ben_ext_rcd rcd
10744: ,ben_ext_rcd_in_file rin
10745: ,ben_ext_dfn dfn
10746: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
10747: FROM pqp_extract_attributes
10748: WHERE ext_dfn_type = 'NL_FPR')
10749: and dtl.person_id = g_person_id
10750: and ext_stat_cd = 'A'
10751: AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
10767: ,ben_ext_rcd rcd
10768: ,ben_ext_rcd_in_file rin
10769: ,ben_ext_dfn dfn
10770: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
10771: FROM pqp_extract_attributes
10772: WHERE ext_dfn_type = 'NL_FPR')
10773: and dtl.person_id = g_person_id
10774: and ext_stat_cd = 'A'
10775: AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
10847: ,ben_ext_rcd rcd
10848: ,ben_ext_rcd_in_file rin
10849: ,ben_ext_dfn dfn
10850: WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
10851: FROM pqp_extract_attributes
10852: WHERE ext_dfn_type = 'NL_FPR')
10853: and dtl.person_id = g_person_id
10854: and ext_stat_cd = 'A'
10855: AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
14339: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
14340: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
14341: l_valid_action Varchar2(2);
14342: i per_all_assignments_f.business_group_id%TYPE;
14343: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14344: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14345: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14346: l_organization_id per_all_assignments_f.organization_id%TYPE;
14347: l_business_group_id per_all_assignments_f.business_group_id%TYPE;