eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PQP_NL_PENSION_EXTRACTS dependencies on BEN_EXT_RSLT

Line 286: FROM ben_ext_rslt_dtl dtl

282: CURSOR csr_rslt_dtl(c_person_id IN Number
283: ,c_ext_rslt_id IN Number
284: ,c_ext_dtl_rcd_id IN Number ) IS
285: SELECT *
286: FROM ben_ext_rslt_dtl dtl
287: WHERE dtl.ext_rslt_id = c_ext_rslt_id
288: AND dtl.person_id = c_person_id
289: AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
290:

Line 632: FROM ben_ext_rslt_dtl dtl

628: FROM per_assignment_status_types
629: WHERE per_system_status = 'TERM_ASSIGN'
630: AND active_flag = 'Y')
631: AND NOT EXISTS( SELECT 1
632: FROM ben_ext_rslt_dtl dtl
633: ,ben_ext_rslt res
634: ,ben_ext_rcd rcd
635: ,ben_ext_rcd_in_file rin
636: ,ben_ext_dfn dfn

Line 633: ,ben_ext_rslt res

629: WHERE per_system_status = 'TERM_ASSIGN'
630: AND active_flag = 'Y')
631: AND NOT EXISTS( SELECT 1
632: FROM ben_ext_rslt_dtl dtl
633: ,ben_ext_rslt res
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

Line 1370: ,p_data_element_value IN ben_ext_rslt_dtl.val_01%TYPE

1366: -- ============================================================================
1367: PROCEDURE Update_Record_Values
1368: (p_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
1369: ,p_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
1370: ,p_data_element_value IN ben_ext_rslt_dtl.val_01%TYPE
1371: ,p_data_ele_seqnum IN Number
1372: ,p_ext_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
1373: ) IS
1374: CURSOR csr_seqnum (c_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE

Line 1372: ,p_ext_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE

1368: (p_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
1369: ,p_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
1370: ,p_data_element_value IN ben_ext_rslt_dtl.val_01%TYPE
1371: ,p_data_ele_seqnum IN Number
1372: ,p_ext_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
1373: ) IS
1374: CURSOR csr_seqnum (c_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
1375: ,c_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
1376: ) IS

Line 1389: l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;

1385: ORDER BY seq_num;
1386:
1387: l_seqnum_rec csr_seqnum%ROWTYPE;
1388: l_proc_name Varchar2(150):= g_proc_name||'Update_Record_Values';
1389: l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1390: BEGIN
1391:
1392: IF g_debug THEN
1393: Hr_Utility.set_location('Entering :'||l_proc_name, 5);

Line 1581: PROCEDURE Ins_Rslt_Dtl(p_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE) IS

1577:
1578: -- ============================================================================
1579: -- ~ Ins_Rslt_Dtl : Inserts a record into the results detail record.
1580: -- ============================================================================
1581: PROCEDURE Ins_Rslt_Dtl(p_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE) IS
1582:
1583: l_proc_name Varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
1584: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1585:

Line 1584: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;

1580: -- ============================================================================
1581: PROCEDURE Ins_Rslt_Dtl(p_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE) IS
1582:
1583: l_proc_name Varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
1584: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1585:
1586: BEGIN -- ins_rslt_dtl
1587:
1588: IF g_debug THEN

Line 1594: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;

1590: END IF;
1591: -- nocopy changes
1592: l_dtl_rec_nc := p_dtl_rec;
1593: -- Get the next sequence NUMBER to insert a record into the table
1594: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
1595:
1596: INSERT INTO ben_ext_rslt_dtl
1597: (EXT_RSLT_DTL_ID
1598: ,EXT_RSLT_ID

Line 1596: INSERT INTO ben_ext_rslt_dtl

1592: l_dtl_rec_nc := p_dtl_rec;
1593: -- Get the next sequence NUMBER to insert a record into the table
1594: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
1595:
1596: INSERT INTO ben_ext_rslt_dtl
1597: (EXT_RSLT_DTL_ID
1598: ,EXT_RSLT_ID
1599: ,BUSINESS_GROUP_ID
1600: ,EXT_RCD_ID

Line 1807: PROCEDURE Upd_Rslt_Dtl(p_dtl_rec IN ben_ext_rslt_dtl%ROWTYPE ) IS

1803:
1804: -- ============================================================================
1805: -- ~ Upd_Rslt_Dtl : Updates the primary assignment record in results detail table
1806: -- ============================================================================
1807: PROCEDURE Upd_Rslt_Dtl(p_dtl_rec IN ben_ext_rslt_dtl%ROWTYPE ) IS
1808:
1809: l_proc_name Varchar2(150):= g_proc_name||'upd_rslt_dtl';
1810:
1811: BEGIN -- Upd_Rslt_Dtl

Line 1812: UPDATE ben_ext_rslt_dtl

1808:
1809: l_proc_name Varchar2(150):= g_proc_name||'upd_rslt_dtl';
1810:
1811: BEGIN -- Upd_Rslt_Dtl
1812: UPDATE ben_ext_rslt_dtl
1813: SET VAL_01 = p_dtl_rec.VAL_01
1814: ,VAL_02 = p_dtl_rec.VAL_02
1815: ,VAL_03 = p_dtl_rec.VAL_03
1816: ,VAL_04 = p_dtl_rec.VAL_04

Line 1908: ,p_rslt_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE

1904: (p_assignment_id IN per_all_assignments.assignment_id%TYPE
1905: ,p_organization_id IN per_all_assignments.organization_id%TYPE DEFAULT NULL
1906: ,p_effective_date IN Date
1907: ,p_ext_dtl_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
1908: ,p_rslt_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
1909: ,p_asgaction_no IN Number DEFAULT NULL
1910: ,p_error_message OUT NOCOPY Varchar2) IS
1911:
1912: CURSOR csr_rule_ele

Line 1959: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;

1955:
1956: l_proc_name Varchar2(150) := g_proc_name ||'Process_Ext_Rslt_Dtl_Rec';
1957: l_foumula_type_id ff_formulas_f.formula_id%TYPE;
1958: l_outputs Ff_Exec.outputs_t;
1959: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1960: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1961: l_org_id per_all_assignments_f.organization_id%TYPE;
1962: l_bgid per_all_assignments_f.business_group_id%TYPE;
1963:

Line 1960: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;

1956: l_proc_name Varchar2(150) := g_proc_name ||'Process_Ext_Rslt_Dtl_Rec';
1957: l_foumula_type_id ff_formulas_f.formula_id%TYPE;
1958: l_outputs Ff_Exec.outputs_t;
1959: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1960: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1961: l_org_id per_all_assignments_f.organization_id%TYPE;
1962: l_bgid per_all_assignments_f.business_group_id%TYPE;
1963:
1964:

Line 2474: FROM ben_ext_rslt_dtl dtl

2470: -- EE assignments
2471: --
2472: CURSOR c_rec05_sent (c_asg_seq IN VARCHAR2) IS
2473: SELECT 1
2474: FROM ben_ext_rslt_dtl dtl
2475: ,ben_ext_rslt res
2476: ,ben_ext_rcd rcd
2477: ,ben_ext_rcd_in_file rin
2478: ,ben_ext_dfn dfn

Line 2475: ,ben_ext_rslt res

2471: --
2472: CURSOR c_rec05_sent (c_asg_seq IN VARCHAR2) IS
2473: SELECT 1
2474: FROM ben_ext_rslt_dtl dtl
2475: ,ben_ext_rslt res
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

Line 3261: FROM ben_ext_rslt res

3257: BETWEEN c_orig_st_date AND c_orig_ed_date;
3258:
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'

Line 3266: AND EXISTS ( SELECT 1 FROM ben_ext_rslt_dtl dtl

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
3268: AND dtl.person_id = g_person_id)
3269: AND trunc(res.eff_dt) = trunc(c_eff_dt)
3270: ORDER BY ext_rslt_id DESC;

Line 3275: FROM ben_ext_rslt_dtl dtl

3271:
3272: CURSOR c_09_abp_data (c_eff_dt IN DATE
3273: ,c_pt_code IN VARCHAR2) IS
3274: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3275: FROM ben_ext_rslt_dtl dtl
3276: ,ben_ext_rslt res
3277: ,ben_ext_rcd rcd
3278: ,ben_ext_rcd_in_file rin
3279: ,ben_ext_dfn dfn

Line 3276: ,ben_ext_rslt res

3272: CURSOR c_09_abp_data (c_eff_dt IN DATE
3273: ,c_pt_code IN VARCHAR2) IS
3274: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3275: FROM ben_ext_rslt_dtl dtl
3276: ,ben_ext_rslt res
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

Line 3300: FROM ben_ext_rslt_dtl dtl

3296: ,c_poj_cd IN VARCHAR2) IS
3297: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3298: ,TRUNC(res.eff_dt) eff_dt
3299: , val_05 code
3300: FROM ben_ext_rslt_dtl dtl
3301: ,ben_ext_rslt res
3302: ,ben_ext_rcd rcd
3303: ,ben_ext_rcd_in_file rin
3304: ,ben_ext_dfn dfn

Line 3301: ,ben_ext_rslt res

3297: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3298: ,TRUNC(res.eff_dt) eff_dt
3299: , val_05 code
3300: FROM ben_ext_rslt_dtl dtl
3301: ,ben_ext_rslt res
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

Line 3325: FROM ben_ext_rslt_dtl dtl

3321:
3322: CURSOR c_09_poj_cor_abp_data (c_eff_dt IN DATE
3323: ,c_poj_cd IN VARCHAR2) IS
3324: SELECT 1
3325: FROM ben_ext_rslt_dtl dtl
3326: ,ben_ext_rslt res
3327: ,ben_ext_rcd rcd
3328: ,ben_ext_rcd_in_file rin
3329: ,ben_ext_dfn dfn

Line 3326: ,ben_ext_rslt res

3322: CURSOR c_09_poj_cor_abp_data (c_eff_dt IN DATE
3323: ,c_poj_cd IN VARCHAR2) IS
3324: SELECT 1
3325: FROM ben_ext_rslt_dtl dtl
3326: ,ben_ext_rslt res
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

Line 3350: FROM ben_ext_rslt_dtl dtl

3346:
3347: CURSOR c_12_abp_data (c_eff_dt IN DATE
3348: ,c_code IN VARCHAR2) IS
3349: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3350: FROM ben_ext_rslt_dtl dtl
3351: ,ben_ext_rslt res
3352: ,ben_ext_rcd rcd
3353: ,ben_ext_rcd_in_file rin
3354: ,ben_ext_dfn dfn

Line 3351: ,ben_ext_rslt res

3347: CURSOR c_12_abp_data (c_eff_dt IN DATE
3348: ,c_code IN VARCHAR2) IS
3349: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3350: FROM ben_ext_rslt_dtl dtl
3351: ,ben_ext_rslt res
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

Line 3377: FROM ben_ext_rslt_dtl dtl

3373: ,c_mon IN VARCHAR2
3374: ,c_eff_dt IN DATE
3375: ,c_code IN VARCHAR2) IS
3376: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3377: FROM ben_ext_rslt_dtl dtl
3378: ,ben_ext_rslt res
3379: ,ben_ext_rcd rcd
3380: ,ben_ext_rcd_in_file rin
3381: ,ben_ext_dfn dfn

Line 3378: ,ben_ext_rslt res

3374: ,c_eff_dt IN DATE
3375: ,c_code IN VARCHAR2) IS
3376: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3377: FROM ben_ext_rslt_dtl dtl
3378: ,ben_ext_rslt res
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

Line 5666: CURSOR csr_ext_attr (c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE) IS

5662: ) RETURN VARCHAR2 IS
5663: --
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: --

Line 5673: CURSOR csr_req_id (c_ext_rslt_id IN ben_ext_rslt.ext_rslt_id%TYPE

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: --
5673: CURSOR csr_req_id (c_ext_rslt_id IN ben_ext_rslt.ext_rslt_id%TYPE
5674: ,c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE
5675: ,c_business_group_id IN ben_ext_rslt.business_group_id%TYPE) IS
5676: SELECT request_id
5677: FROM ben_ext_rslt

Line 5674: ,c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE

5670: --
5671: -- Based on result id and Ext. Dfn Id, get the con. request id
5672: --
5673: CURSOR csr_req_id (c_ext_rslt_id IN ben_ext_rslt.ext_rslt_id%TYPE
5674: ,c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE
5675: ,c_business_group_id IN ben_ext_rslt.business_group_id%TYPE) IS
5676: SELECT request_id
5677: FROM ben_ext_rslt
5678: WHERE ext_rslt_id = c_ext_rslt_id

Line 5675: ,c_business_group_id IN ben_ext_rslt.business_group_id%TYPE) IS

5671: -- Based on result id and Ext. Dfn Id, get the con. request id
5672: --
5673: CURSOR csr_req_id (c_ext_rslt_id IN ben_ext_rslt.ext_rslt_id%TYPE
5674: ,c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE
5675: ,c_business_group_id IN ben_ext_rslt.business_group_id%TYPE) IS
5676: SELECT request_id
5677: FROM ben_ext_rslt
5678: WHERE ext_rslt_id = c_ext_rslt_id
5679: AND ext_dfn_id = c_ext_dfn_id

Line 5677: FROM ben_ext_rslt

5673: CURSOR csr_req_id (c_ext_rslt_id IN ben_ext_rslt.ext_rslt_id%TYPE
5674: ,c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE
5675: ,c_business_group_id IN ben_ext_rslt.business_group_id%TYPE) IS
5676: SELECT request_id
5677: FROM ben_ext_rslt
5678: WHERE ext_rslt_id = c_ext_rslt_id
5679: AND ext_dfn_id = c_ext_dfn_id
5680: AND business_group_id = c_business_group_id;
5681: --

Line 5881: l_conc_reqest_id ben_ext_rslt.request_id%TYPE;

5877: -- =========================================
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;

Line 5884: l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;

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';
5887: l_proc_name Varchar2(150) := g_proc_name ||'Pension_Criteria_Full_Profile';
5888: l_assig_rec csr_assig%ROWTYPE;

Line 6350: -- it logs a warning in the ben_ext_rslt_err table against

6346:
6347: -- ====================================================================
6348: -- Raise_Extract_Warning:
6349: -- When called from the Rule of a extract detail data element
6350: -- it logs a warning in the ben_ext_rslt_err table against
6351: -- the person being processed (or as specified by context of
6352: -- assignment id ). It prefixes all warning messages with a
6353: -- string "Warning raised in data element "||element_name
6354: -- This allows the same Rule to be called from different data

Line 8300: FROM ben_ext_rslt_dtl dtl

8296: -- for this assignment. If not sent then trigger a send
8297: --
8298: CURSOR c_rec_02_sent(c_asg_seq_no IN VARCHAR2 ) IS
8299: SELECT 1
8300: FROM ben_ext_rslt_dtl dtl
8301: ,ben_ext_rslt res
8302: ,ben_ext_rcd rcd
8303: ,ben_ext_rcd_in_file rin
8304: ,ben_ext_dfn dfn

Line 8301: ,ben_ext_rslt res

8297: --
8298: CURSOR c_rec_02_sent(c_asg_seq_no IN VARCHAR2 ) IS
8299: SELECT 1
8300: FROM ben_ext_rslt_dtl dtl
8301: ,ben_ext_rslt res
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

Line 8489: FROM ben_ext_rslt_dtl dtl

8485: -- for this assignment. If not sent then trigger a send
8486: --
8487: CURSOR c_rec_04_sent(c_asg_seq_no IN VARCHAR2 ) IS
8488: SELECT 1
8489: FROM ben_ext_rslt_dtl dtl
8490: ,ben_ext_rslt res
8491: ,ben_ext_rcd rcd
8492: ,ben_ext_rcd_in_file rin
8493: ,ben_ext_dfn dfn

Line 8490: ,ben_ext_rslt res

8486: --
8487: CURSOR c_rec_04_sent(c_asg_seq_no IN VARCHAR2 ) IS
8488: SELECT 1
8489: FROM ben_ext_rslt_dtl dtl
8490: ,ben_ext_rslt res
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

Line 8900: FROM ben_ext_rslt_dtl dtl

8896: --
8897: CURSOR c_rec_08_sent(c_start_of_yr IN DATE
8898: ,c_asg_seq_no IN VARCHAR2 ) IS
8899: SELECT 1
8900: FROM ben_ext_rslt_dtl dtl
8901: ,ben_ext_rslt res
8902: ,ben_ext_rcd rcd
8903: ,ben_ext_rcd_in_file rin
8904: ,ben_ext_dfn dfn

Line 8901: ,ben_ext_rslt res

8897: CURSOR c_rec_08_sent(c_start_of_yr IN DATE
8898: ,c_asg_seq_no IN VARCHAR2 ) IS
8899: SELECT 1
8900: FROM ben_ext_rslt_dtl dtl
8901: ,ben_ext_rslt res
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

Line 11080: FROM ben_ext_rslt_dtl dtl

11076:
11077: CURSOR c_prev_term_dt (c_asg_seq_no IN VARCHAR2 ) IS
11078: SELECT dtl.val_07,
11079: DECODE(dtl.val_09,' ','A')
11080: FROM ben_ext_rslt_dtl dtl
11081: ,ben_ext_rslt res
11082: ,ben_ext_rcd rcd
11083: ,ben_ext_rcd_in_file rin
11084: ,ben_ext_dfn dfn

Line 11081: ,ben_ext_rslt res

11077: CURSOR c_prev_term_dt (c_asg_seq_no IN VARCHAR2 ) IS
11078: SELECT dtl.val_07,
11079: DECODE(dtl.val_09,' ','A')
11080: FROM ben_ext_rslt_dtl dtl
11081: ,ben_ext_rslt res
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

Line 11104: FROM ben_ext_rslt_dtl dtl

11100:
11101: CURSOR c_prev_term_rev (c_asg_seq_no IN VARCHAR2
11102: ,c_in_term_date IN VARCHAR2) IS
11103: SELECT 1
11104: FROM ben_ext_rslt_dtl dtl
11105: ,ben_ext_rslt res
11106: ,ben_ext_rcd rcd
11107: ,ben_ext_rcd_in_file rin
11108: ,ben_ext_dfn dfn

Line 11105: ,ben_ext_rslt res

11101: CURSOR c_prev_term_rev (c_asg_seq_no IN VARCHAR2
11102: ,c_in_term_date IN VARCHAR2) IS
11103: SELECT 1
11104: FROM ben_ext_rslt_dtl dtl
11105: ,ben_ext_rslt res
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

Line 11184: FROM ben_ext_rslt_dtl dtl

11180: AND language = 'US';
11181:
11182: CURSOR c_rec05_sent IS
11183: SELECT 1
11184: FROM ben_ext_rslt_dtl dtl
11185: ,ben_ext_rslt res
11186: ,ben_ext_rcd rcd
11187: ,ben_ext_rcd_in_file rin
11188: ,ben_ext_dfn dfn

Line 11185: ,ben_ext_rslt res

11181:
11182: CURSOR c_rec05_sent IS
11183: SELECT 1
11184: FROM ben_ext_rslt_dtl dtl
11185: ,ben_ext_rslt res
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

Line 13003: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;

12999: ,p_effective_date
13000: ,p_error_message
13001: ,l_seq_num);
13002:
13003: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;
13004:
13005: INSERT INTO ben_ext_rslt_dtl
13006: ( EXT_RSLT_DTL_ID
13007: ,EXT_RSLT_ID

Line 13005: INSERT INTO ben_ext_rslt_dtl

13001: ,l_seq_num);
13002:
13003: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;
13004:
13005: INSERT INTO ben_ext_rslt_dtl
13006: ( EXT_RSLT_DTL_ID
13007: ,EXT_RSLT_ID
13008: ,BUSINESS_GROUP_ID
13009: ,EXT_RCD_ID

Line 13106: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;

13102: ,c_ext_dtl_rcd_id => l_rcd_id);
13103: FETCH csr_rslt_dtl INTO l_main_rec;
13104: CLOSE csr_rslt_dtl;
13105:
13106: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;
13107:
13108: INSERT INTO ben_ext_rslt_dtl
13109: ( EXT_RSLT_DTL_ID
13110: ,EXT_RSLT_ID

Line 13108: INSERT INTO ben_ext_rslt_dtl

13104: CLOSE csr_rslt_dtl;
13105:
13106: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;
13107:
13108: INSERT INTO ben_ext_rslt_dtl
13109: ( EXT_RSLT_DTL_ID
13110: ,EXT_RSLT_ID
13111: ,BUSINESS_GROUP_ID
13112: ,EXT_RCD_ID

Line 14938: DELETE ben_ext_rslt_dtl

14934: FETCH csr_rslt_dtl INTO l_main_rec;
14935: WHILE csr_rslt_dtl%FOUND
14936: LOOP
14937: -- Delete for each detail record for the person
14938: DELETE ben_ext_rslt_dtl
14939: WHERE ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
14940: AND person_id = l_person_id;
14941: FETCH csr_rslt_dtl INTO l_main_rec;
14942:

Line 14962: DELETE ben_ext_rslt_dtl

14958: FETCH csr_rslt_dtl INTO l_main_rec;
14959: WHILE csr_rslt_dtl%FOUND
14960: LOOP
14961: -- Delete for each detail record for the person
14962: DELETE ben_ext_rslt_dtl
14963: WHERE ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
14964: AND person_id = l_person_id;
14965: FETCH csr_rslt_dtl INTO l_main_rec;
14966: END LOOP; -- While csr_rslt_dtl%FOUND

Line 16620: FROM ben_ext_rslt_dtl dtl

16616: CURSOR csr_get_a_record_count(c_recordid_1 IN NUMBER
16617: ,c_recordid_2 IN NUMBER
16618: ,c_emr_id IN NUMBER ) IS
16619: SELECT Count(dtl.ext_rslt_dtl_id)
16620: FROM ben_ext_rslt_dtl dtl
16621: WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
16622: AND ext_rcd_id NOT IN(c_recordid_1,c_recordid_2)
16623: AND val_25 = c_emr_id;
16624:

Line 16662: FROM ben_ext_rslt_dtl dtl

16658: ,c_recordid_2 IN Number
16659: ,c_recordid_3 IN Number
16660: ,c_emr_id IN Number ) IS
16661: SELECT Count(dtl.ext_rslt_dtl_id)
16662: FROM ben_ext_rslt_dtl dtl
16663: WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
16664: AND ext_rcd_id IN(c_recordid_1,c_recordid_2,c_recordid_3)
16665: AND val_25=c_emr_id;
16666:

Line 16687: (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE

16683: -- ================================================================================
16684: -- ~ Sort_Post_Process : Post process logic
16685: -- ================================================================================
16686: FUNCTION Sort_Post_Process
16687: (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
16688: )RETURN Number IS
16689:
16690: /* --9278285 cursor modified
16691: CURSOR csr_get_rslt(c_org_id IN Varchar2

Line 16694: FROM ben_ext_rslt_dtl dtl

16690: /* --9278285 cursor modified
16691: CURSOR csr_get_rslt(c_org_id IN Varchar2
16692: ,c_ext_rslt_id IN Number ) IS
16693: SELECT DISTINCT(val_26) val_26
16694: FROM ben_ext_rslt_dtl dtl
16695: WHERE dtl.ext_rslt_id = c_ext_rslt_id
16696: AND val_25= c_org_id
16697: ORDER BY val_26 ASC ;
16698: */

Line 16704: FROM ben_ext_rslt_dtl dtl

16700: --9278285 ordering on employee number only.
16701: --Do not pick org wise record, instead pick all records in one go.
16702: CURSOR csr_get_rslt(c_ext_rslt_id IN Number ) IS
16703: SELECT DISTINCT(val_26) val_26
16704: FROM ben_ext_rslt_dtl dtl
16705: WHERE dtl.ext_rslt_id = c_ext_rslt_id
16706: AND val_25 IS NOT NULL
16707: AND val_26 IS NOT NULL
16708: ORDER BY val_26 ASC ;

Line 16713: FROM ben_ext_rslt_dtl dtl

16709:
16710:
16711: CURSOR csr_get_rslt1(c_ext_rslt_id IN Number ) IS
16712: SELECT val_25,val_26
16713: FROM ben_ext_rslt_dtl dtl
16714: WHERE dtl.ext_rslt_id = c_ext_rslt_id
16715: ORDER BY val_26 ASC ;
16716:
16717: CURSOR csr_rslt_dtl_sort(c_val_26 IN Varchar2

Line 16720: FROM ben_ext_rslt_dtl dtl

16716:
16717: CURSOR csr_rslt_dtl_sort(c_val_26 IN Varchar2
16718: ,c_ext_rslt_id IN Number ) IS
16719: SELECT *
16720: FROM ben_ext_rslt_dtl dtl
16721: WHERE dtl.ext_rslt_id = c_ext_rslt_id
16722: -- AND dtl.person_id = c_person_id
16723: AND dtl.val_26 =c_val_26;
16724:

Line 16730: FROM ben_ext_rslt_dtl dtl

16726:
16727: CURSOR csr_get_header_rslt(c_ext_rslt_id IN Number
16728: ,c_ext_dtl_rcd_id IN Number ) IS
16729: SELECT *
16730: FROM ben_ext_rslt_dtl dtl
16731: WHERE dtl.ext_rslt_id = c_ext_rslt_id
16732: AND ext_rcd_id= c_ext_dtl_rcd_id;
16733:
16734:

Line 16738: FROM ben_ext_rslt_dtl dtl

16734:
16735: CURSOR csr_get_trailer_rslt(c_ext_rslt_id IN Number
16736: ,c_ext_dtl_rcd_id IN Number ) IS
16737: SELECT *
16738: FROM ben_ext_rslt_dtl dtl
16739: WHERE dtl.ext_rslt_id = c_ext_rslt_id
16740: AND ext_rcd_id= c_ext_dtl_rcd_id;
16741:
16742: -- Cursor to get the person existence flag

Line 16745: FROM ben_ext_rslt_dtl

16741:
16742: -- Cursor to get the person existence flag
16743: CURSOR csr_get_person_exist(c_org_id IN Number) IS
16744: SELECT 'x'
16745: FROM ben_ext_rslt_dtl
16746: WHERE ext_rslt_id=Ben_Ext_Thread.g_ext_rslt_id
16747: AND val_25=c_org_id;
16748:
16749: --

Line 16767: FROM ben_ext_rslt_dtl

16763: -- Cursor to get the records 05 rows that need to be deleted.
16764: --
16765: CURSOR csr_rec05_del (p_ext_rcd_id IN NUMBER) IS
16766: SELECT ext_rslt_dtl_id
16767: FROM ben_ext_rslt_dtl
16768: WHERE ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
16769: AND ext_rcd_id = p_ext_rcd_id
16770: AND val_05 = '00000000'
16771: AND val_06 = '00000000'

Line 16885: DELETE ben_ext_rslt_dtl

16881: ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
16882: -- Loop through each detail record for the extract
16883: LOOP
16884: -- Delete all detail records for the record
16885: DELETE ben_ext_rslt_dtl
16886: WHERE ext_rcd_id = csr_rcd_rec.ext_rcd_id
16887: AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
16888: AND business_group_id = p_business_group_id;
16889: END LOOP; -- FOR csr_rcd_rec

Line 16898: DELETE ben_ext_rslt_dtl

16894: FOR csr_rcd_05_id_rec IN csr_rcd_05_id
16895: LOOP
16896: FOR csr_rec05_del_rec IN csr_rec05_del (csr_rcd_05_id_rec.ext_rcd_id)
16897: LOOP
16898: DELETE ben_ext_rslt_dtl
16899: WHERE ext_rslt_dtl_id = csr_rec05_del_rec.ext_rslt_dtl_id;
16900: END LOOP; -- FOR csr_rcd_05_id
16901: END LOOP; -- For csr_rec05_del
16902:

Line 17448: FROM ben_ext_rslt_dtl dtl

17444: l_ext_rslt_dtl_id := l_R99_rslt_dtl_id;
17445: END IF;
17446:
17447: DELETE
17448: FROM ben_ext_rslt_dtl dtl
17449: WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
17450: AND dtl.ext_rcd_id = csr_rcd_rec.ext_rcd_id
17451: AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_id
17452: AND business_group_id = p_business_group_id;

Line 17464: FROM ben_ext_rslt_dtl dtl

17460: l_ext_rslt_dtl_id := l_R00_rslt_dtl_id;
17461: END IF;
17462:
17463: DELETE
17464: FROM ben_ext_rslt_dtl dtl
17465: WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
17466: AND dtl.ext_rcd_id = csr_rcd_rec.ext_rcd_id
17467: AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_id
17468: AND business_group_id = p_business_group_id;

Line 17487: UPDATE ben_ext_rslt_dtl

17483: END LOOP;
17484:
17485: IF l_DEST IS NOT NULL THEN
17486:
17487: UPDATE ben_ext_rslt_dtl
17488: SET VAL_07 = translate(VAL_07,l_SOURCE,l_DEST),
17489: VAL_08 = translate(VAL_08,l_SOURCE,l_DEST),
17490: VAL_09 = translate(VAL_09,l_SOURCE,l_DEST),
17491: VAL_12 = translate(VAL_12,l_SOURCE,l_DEST),

Line 17497: UPDATE ben_ext_rslt_dtl

17493: WHERE EXT_RSLT_ID = Ben_Ext_Thread.g_ext_rslt_id
17494: AND VAL_01 = '02'
17495: AND BUSINESS_GROUP_ID = p_business_group_id;
17496:
17497: UPDATE ben_ext_rslt_dtl
17498: SET VAL_07 = translate(VAL_07,l_SOURCE,l_DEST),
17499: VAL_11 = translate(VAL_11,l_SOURCE,l_DEST),
17500: VAL_13 = translate(VAL_13,l_SOURCE,l_DEST),
17501: VAL_14 = translate(VAL_14,l_SOURCE,l_DEST)