[Home] [Help]
239: from pay_process_events ppe
240: where trunc(ppe.creation_date) between p_lapp_date and p_end_date
241: and ppe.assignment_id = p_assignment_id
242: and ppe.business_group_id = p_business_group_id
243: and ppe.effective_date >= ben_ext_thread.g_effective_start_date
244: and exists (select pde.event_group_id
245: from pay_datetracked_events pde,
246: pay_event_updates peu
247: where pde.event_group_id in (select becv.val_1
249: ben_ext_crit_typ bect,
250: ben_ext_dfn bed
251: where becv.ext_crit_typ_id = bect.ext_crit_typ_id
252: and bect.ext_crit_prfl_id = bed.ext_crit_prfl_id
253: and bed.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
254: and bect.crit_typ_cd = 'CPE')
255: and ppe.event_update_id = peu.event_update_id
256: and peu.dated_table_id = pde.dated_table_id);
257:
334: -- SELECT ben_ext_rslt_s.CURRVAL
335: -- INTO l_ext_rslt_id
336: -- FROM DUAL;
337:
338: l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
339:
340: RETURN l_ext_rslt_id;
341: --
342: EXCEPTION
1657: IF l_ext_dfn_id IS NOT NULL THEN
1658:
1659: IF p_execution_mode = '1REP'
1660: THEN
1661: ben_ext_thread.restart
1662: (errbuf => l_errbuff
1663: ,retcode => l_retcode
1664: ,p_ext_dfn_id => l_ext_dfn_id
1665: ,p_concurrent_request_id => p_concurrent_request_id
1671: fnd_file.put_line(fnd_file.log, 'calling ben_process' || ' : ' || get_time);
1672:
1673: IF p_extract_type <> '1PED' -- cutover/code extract (full profile)
1674: THEN
1675: ben_ext_thread.process
1676: (errbuf => l_errbuff
1677: ,retcode => l_retcode
1678: ,p_benefit_action_id => NULL
1679: ,p_ext_dfn_id => l_ext_dfn_id
1697:
1698: debug('p_dfn_name : ' || p_dfn_name );
1699: debug('l_penserv_mode : ' || l_penserv_mode );
1700:
1701: ben_ext_thread.process
1702: (errbuf => l_errbuff
1703: ,retcode => l_retcode
1704: ,p_benefit_action_id => NULL
1705: ,p_ext_dfn_id => l_ext_dfn_id
2797: FETCH csr_get_bas_ext_dfn_id INTO l_bas_ext_dfn_id;
2798: CLOSE csr_get_bas_ext_dfn_id;
2799:
2800: debug('l_bas_ext_dfn_id: '|| l_bas_ext_dfn_id);
2801: debug('ben_ext_thread.g_ext_dfn_id: '|| ben_ext_thread.g_ext_dfn_id);
2802:
2803: IF l_bas_ext_dfn_id = ben_ext_thread.g_ext_dfn_id
2804: --115.88:Bug 7291713: Stop
2805: THEN
2799:
2800: debug('l_bas_ext_dfn_id: '|| l_bas_ext_dfn_id);
2801: debug('ben_ext_thread.g_ext_dfn_id: '|| ben_ext_thread.g_ext_dfn_id);
2802:
2803: IF l_bas_ext_dfn_id = ben_ext_thread.g_ext_dfn_id
2804: --115.88:Bug 7291713: Stop
2805: THEN
2806:
2807: --115.87:Bug 7291713: Start
3055:
3056: debug_enter(l_proc_name);
3057:
3058:
3059: OPEN csr_ben (c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id
3060: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3061: ,c_business_group_id => p_business_group_id);
3062: Fetch csr_ben Into l_ben_params;
3063: CLOSE csr_ben;
3056: debug_enter(l_proc_name);
3057:
3058:
3059: OPEN csr_ben (c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id
3060: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3061: ,c_business_group_id => p_business_group_id);
3062: Fetch csr_ben Into l_ben_params;
3063: CLOSE csr_ben;
3064:
3070: Where bpa.benefit_action_id = l_ben_params.benefit_action_id -- 3629 -- p_benefit_action_id
3071: and bpa.person_id -- = p_person_id;
3072: IN ( SELECT PERSON_ID
3073: FROM BEN_EXT_RSLT_ERR
3074: WHERE EXT_RSLT_ID= Ben_Ext_Thread.g_ext_rslt_id -- 2891 -- c_ext_rslt_id
3075: AND typ_cd = 'E');
3076:
3077: Update ben_batch_ranges bbr
3078: set bbr.range_status_cd = 'E'
3084: AND bbr.benefit_action_id = bpa.benefit_action_id
3085: AND (bpa.person_action_id Between
3086: bbr.starting_person_action_id And bbr.ending_person_action_id)
3087: And bpa.person_id = bere.person_id
3088: AND bere.EXT_RSLT_ID= Ben_Ext_Thread.g_ext_rslt_id
3089: AND bere.typ_cd = 'E');
3090:
3091: debug_exit(l_proc_name);
3092:
3161:
3162: debug_enter(l_proc_name);
3163:
3164:
3165: OPEN csr_ben (c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id
3166: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3167: ,c_business_group_id => p_business_group_id);
3168: Fetch csr_ben Into l_ben_params;
3169: CLOSE csr_ben;
3162: debug_enter(l_proc_name);
3163:
3164:
3165: OPEN csr_ben (c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id
3166: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3167: ,c_business_group_id => p_business_group_id);
3168: Fetch csr_ben Into l_ben_params;
3169: CLOSE csr_ben;
3170:
3171: debug('l_ben_params.benefit_action_id :' || l_ben_params.benefit_action_id);
3172:
3173:
3174: -- (1) fetch all person_ids which have errored
3175: OPEN csr_err_person_id(p_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id);
3176: FETCH csr_err_person_id BULK COLLECT INTO l_person_collection;
3177: CLOSE csr_err_person_id;
3178:
3179: debug('Step 1 Completed');
3272: CURSOR csr_dfn_code
3273: IS
3274: Select output_name
3275: from ben_ext_dfn
3276: where ext_dfn_id = ben_ext_thread.g_ext_dfn_id;
3277:
3278: l_extract_code csr_dfn_code%rowtype;
3279: l_file_name VARCHAR2(100);
3280: l_run_date DATE;
3314: debug('g_sequence_number : ' || g_sequence_number);
3315:
3316:
3317: -- Get the output file name
3318: OPEN csr_get_run_date(c_ext_dfn_id => ben_ext_thread.g_ext_dfn_id
3319: ,c_business_group_id => l_business_group_id);
3320: FETCH csr_get_run_date INTO l_run_date,g_output_name;
3321: CLOSE csr_get_run_date;
3322:
3348:
3349: update ben_ext_rslt
3350: SET output_name = l_file_name
3351: WHERE business_group_id = l_business_group_id
3352: AND ext_rslt_id = ben_ext_thread.g_ext_rslt_id;
3353:
3354: debug_exit(l_proc_name);
3355:
3356: -- fnd_file.put_line(fnd_file.log, 'Done : ' || l_proc_name || to_char(SYSDATE,'dd-Mon-yyyy hh:mm:ss am'));
3442: --if no message token is defined then egt the message from
3443: --ben_ext_fmt.
3444: Elsif p_token1 is null Then
3445:
3446: ben_ext_thread.g_err_num := p_error_number;
3447: ben_ext_thread.g_err_name := p_error_text;
3448: l_error_text :=
3449: ben_ext_fmt.get_error_msg(fnd_number.canonical_to_number(substr(p_error_text, 5, 5)),
3450: p_error_text,ben_ext_fmt.g_elmt_name);
3443: --ben_ext_fmt.
3444: Elsif p_token1 is null Then
3445:
3446: ben_ext_thread.g_err_num := p_error_number;
3447: ben_ext_thread.g_err_name := p_error_text;
3448: l_error_text :=
3449: ben_ext_fmt.get_error_msg(fnd_number.canonical_to_number(substr(p_error_text, 5, 5)),
3450: p_error_text,ben_ext_fmt.g_elmt_name);
3451:
3455:
3456: -- set the Tokens in the warning message and then
3457: -- get the warning message from fnd_messages.
3458:
3459: ben_ext_thread.g_err_num := p_error_number;
3460: ben_ext_thread.g_err_name := p_error_text;
3461:
3462: fnd_message.set_name('BEN',p_error_text);
3463: fnd_message.set_token('TOKEN1',p_token1);
3456: -- set the Tokens in the warning message and then
3457: -- get the warning message from fnd_messages.
3458:
3459: ben_ext_thread.g_err_num := p_error_number;
3460: ben_ext_thread.g_err_name := p_error_text;
3461:
3462: fnd_message.set_name('BEN',p_error_text);
3463: fnd_message.set_token('TOKEN1',p_token1);
3464: debug('token1 : '||p_token1);
3588:
3589: Elsif p_token1 is null Then
3590:
3591: debug('p_token1 is null');
3592: ben_ext_thread.g_err_num := p_error_number;
3593: ben_ext_thread.g_err_name := p_error_text;
3594: l_error_text :=
3595: ben_ext_fmt.get_error_msg(fnd_number.canonical_to_number(substr(p_error_text, 5, 5)),
3596: p_error_text,ben_ext_fmt.g_elmt_name);
3589: Elsif p_token1 is null Then
3590:
3591: debug('p_token1 is null');
3592: ben_ext_thread.g_err_num := p_error_number;
3593: ben_ext_thread.g_err_name := p_error_text;
3594: l_error_text :=
3595: ben_ext_fmt.get_error_msg(fnd_number.canonical_to_number(substr(p_error_text, 5, 5)),
3596: p_error_text,ben_ext_fmt.g_elmt_name);
3597:
3601:
3602: -- set the Tokens in the warning message and then
3603: -- get the warning message from fnd_messages.
3604:
3605: ben_ext_thread.g_err_num := p_error_number;
3606: ben_ext_thread.g_err_name := p_error_text;
3607:
3608: fnd_message.set_name('BEN',p_error_text);
3609: fnd_message.set_token('TOKEN1',p_token1);
3602: -- set the Tokens in the warning message and then
3603: -- get the warning message from fnd_messages.
3604:
3605: ben_ext_thread.g_err_num := p_error_number;
3606: ben_ext_thread.g_err_name := p_error_text;
3607:
3608: fnd_message.set_name('BEN',p_error_text);
3609: fnd_message.set_token('TOKEN1',p_token1);
3610:
3652: );
3653:
3654: --commit;
3655:
3656: --raise ben_ext_thread.g_job_failure_error;
3657: l_return_value:= 0; /* All Well ! */
3658: --
3659: ELSE
3660: --
3749: g_errors(l_error_index + 1).assignment_id := -(l_error_index+1);
3750: END IF;
3751:
3752: -- store extract result ID for cases of multiple parallel extracts run
3753: g_errors(l_error_index + 1).ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
3754:
3755: -- debug
3756: --=================================
3757: debug('g_errors('||(l_error_index + 1)||').extract_type : '|| g_errors(l_error_index + 1).extract_type);
3785: g_warnings(l_warning_index + 1).assignment_id := -(l_warning_index+1);
3786: END IF;
3787:
3788: -- store extract result ID for cases of multiple parallel extracts run
3789: g_warnings(l_warning_index + 1).ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
3790:
3791:
3792: -- debug
3793: --=================================
3899: g_errors(l_index).assignment_id < 0 -- IS NULL
3900: )
3901: )
3902: and
3903: g_errors(l_index).ext_rslt_id = ben_ext_thread.g_ext_rslt_id
3904:
3905:
3906: THEN
3907: debug('This error qualified to be raised - ',30);
3980: g_warnings(l_index).assignment_id < 0 -- IS NULL
3981: )
3982: )
3983: and
3984: g_warnings(l_index).ext_rslt_id = ben_ext_thread.g_ext_rslt_id
3985:
3986: THEN
3987: debug('This warning qualified to be raised - ',30);
3988:
4011: IF l_fatal_error_flag = 'Y'
4012: THEN
4013: commit;
4014:
4015: raise ben_ext_thread.g_job_failure_error;
4016: END IF;
4017:
4018: debug_exit(l_proc_name);
4019:
4120: -- store in a global
4121: g_cutover_date := p_cutover_date;
4122:
4123: -- extract definition ID
4124: p_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
4125: debug('p_ext_dfn_id : ' || p_ext_dfn_id, 60);
4126:
4127:
4128: -- fetch configuration value for employment type mapping
4863: CURSOR csr_get_run_date
4864: IS
4865: SELECT MAX(eff_dt)
4866: FROM ben_ext_rslt
4867: WHERE ext_dfn_id = ben_ext_thread.g_ext_dfn_id
4868: AND business_group_id = g_business_group_id
4869: AND ext_stat_cd = 'A';
4870: */
4871:
4909: OPEN csr_ext_dfn_id(c_extract_name => 'PQP GB PenServer Periodic Changes Interface - Basic Data');
4910: FETCH csr_ext_dfn_id INTO l_ext_dfn_id;
4911: CLOSE csr_ext_dfn_id;
4912: ELSE
4913: l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
4914: END IF;
4915:
4916:
4917: -- Get the run date