[Home] [Help]
127: *** 28-MAY-04 punmehta 115.112 Bug#3642409 - Removed Rownum from cursor process_assignments.
128: *** 03-JUN-04 abhkumar 115.113 Bug#3661230 - Modified the process assignment cursor to take all the employees for archiving purpose.
129: *** 21-JUN-04 srrajago 115.118 Bug#3701869 - Modified the cursor csr_get_bbr_aseq to fetch max action_sequence instead of
130: *** assignment_action_id. Introduced cursor csr_get_bbr_assignment_action to pass the
131: *** correct assignment_action_id based on action_sequence to pay_balance_pkg.Also handled
132: *** the parameter Last Year Termination value setting if it is not enabled.
133: *** 21-JUN-04 srrajago 115.119 Bug#3701869 - Modified cursors 'csr_get_bbr_aseq' and 'csr_get_bbr_asg_actions'. Handled the parameter
134: *** Last Year Termination value setting if it is not enabled in another place which was
135: *** missed in the previous fix.
251: --
252: -------------------------------------------------------------------------------------------------------+
253: -- Define global variable to store defined_balance_id's and the corresponding balance values for BBR.
254: -------------------------------------------------------------------------------------------------------+
255: p_balance_value_tab pay_balance_pkg.t_balance_value_tab; -- 3172963
256: p_context_table pay_balance_pkg.t_context_tab; -- 2610141
257: p_result_table pay_balance_pkg.t_detailed_bal_out_tab; -- 2610141
258:
259: ---------------------------------------------------------------------------------------------------------------------------+
252: -------------------------------------------------------------------------------------------------------+
253: -- Define global variable to store defined_balance_id's and the corresponding balance values for BBR.
254: -------------------------------------------------------------------------------------------------------+
255: p_balance_value_tab pay_balance_pkg.t_balance_value_tab; -- 3172963
256: p_context_table pay_balance_pkg.t_context_tab; -- 2610141
257: p_result_table pay_balance_pkg.t_detailed_bal_out_tab; -- 2610141
258:
259: ---------------------------------------------------------------------------------------------------------------------------+
260: --Bug 6192381
253: -- Define global variable to store defined_balance_id's and the corresponding balance values for BBR.
254: -------------------------------------------------------------------------------------------------------+
255: p_balance_value_tab pay_balance_pkg.t_balance_value_tab; -- 3172963
256: p_context_table pay_balance_pkg.t_context_tab; -- 2610141
257: p_result_table pay_balance_pkg.t_detailed_bal_out_tab; -- 2610141
258:
259: ---------------------------------------------------------------------------------------------------------------------------+
260: --Bug 6192381
261: --For ETP Payment Balances define global variable to store defined_balance_id's and the corresponding balance values for BBR.
260: --Bug 6192381
261: --For ETP Payment Balances define global variable to store defined_balance_id's and the corresponding balance values for BBR.
262: ---------------------------------------------------------------------------------------------------------------------------+
263:
264: p_etp_balance_value_tab pay_balance_pkg.t_balance_value_tab;
265: p_etp_context_table pay_balance_pkg.t_context_tab;
266: p_etp_result_table pay_balance_pkg.t_detailed_bal_out_tab;
267:
268: /* Bug 6470581 - PL/SQL table to hold Archive Items when submitted for Amended payment summary */
261: --For ETP Payment Balances define global variable to store defined_balance_id's and the corresponding balance values for BBR.
262: ---------------------------------------------------------------------------------------------------------------------------+
263:
264: p_etp_balance_value_tab pay_balance_pkg.t_balance_value_tab;
265: p_etp_context_table pay_balance_pkg.t_context_tab;
266: p_etp_result_table pay_balance_pkg.t_detailed_bal_out_tab;
267:
268: /* Bug 6470581 - PL/SQL table to hold Archive Items when submitted for Amended payment summary */
269: p_all_dbi_tab pay_au_payment_summary_amend.archive_db_tab ;
262: ---------------------------------------------------------------------------------------------------------------------------+
263:
264: p_etp_balance_value_tab pay_balance_pkg.t_balance_value_tab;
265: p_etp_context_table pay_balance_pkg.t_context_tab;
266: p_etp_result_table pay_balance_pkg.t_detailed_bal_out_tab;
267:
268: /* Bug 6470581 - PL/SQL table to hold Archive Items when submitted for Amended payment summary */
269: p_all_dbi_tab pay_au_payment_summary_amend.archive_db_tab ;
270:
724: ,per_assignments_f paaf /*Bug 3815301 */
725: where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
726: or ( pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
727: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
728: and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
729: + decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */ --2610141
730: )
731: and rppa.action_type in ('R','Q','B','I')
732: and rpac.tax_unit_id = v_reg_emp
805: ,per_assignments_f paaf /*Bug 3815301 */
806: where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
807: or ( pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
808: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
809: and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
810: + decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */ --2610141
811: )
812: and rppa.action_type in ('R','Q','B','I')
813: and rpac.tax_unit_id = v_reg_emp
902: ,per_assignments_f paaf /*Bug 3815301 */
903: WHERE ( rppa.effective_date BETWEEN v_fin_year_start AND v_fin_year_end
904: OR ( pps.actual_termination_date BETWEEN v_lst_fbt_year_start AND v_fbt_year_end /*Bug3263659 */ --Bug#3661230
905: AND rppa.effective_date BETWEEN v_fbt_year_start AND v_fbt_year_end
906: AND pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
907: + decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)
908: )
909: )
910: AND rppa.action_type in ('R','Q','B','I')
1354: cursor c_archive_fbt_info(c_balance_name pay_balance_types.balance_name%type,
1355: c_year_end DATE,
1356: c_assignment_id pay_assignment_actions.assignment_id%type)
1357: is
1358: select pay_balance_pkg.get_value(pdb.defined_balance_id,
1359: p_max_assignment_action_id,
1360: p_registered_employer,
1361: null,null,null,null) --2610141
1362: from pay_balance_types pbt,
1390: CURSOR c_single_lumpsum_E_payment(c_effective_date DATE,
1391: c_assignment_id pay_assignment_actions.assignment_id%type,
1392: c_assignment_action_id pay_assignment_actions.assignment_action_id%type) --2610141
1393: IS
1394: select sum(pay_balance_pkg.get_value(pdb.defined_balance_id,
1395: c_assignment_action_id,
1396: p_registered_employer,
1397: null,null,null,null)) --2610141
1398: FROM pay_balance_types pbt,
2060: ,c_assignment_id pay_assignment_actions.assignment_id%type
2061: ,c_dimension_id pay_balance_dimensions.balance_dimension_id%type
2062: ) IS
2063: select balance_name
2064: , pay_balance_pkg.get_value(def_id,p_max_assignment_action_id,p_registered_employer,null,null,null,null) balance_value
2065: , def_id
2066: , bal_type_id
2067: from
2068: ( select distinct nvl(pbt.reporting_name,pbt.balance_name) balance_name
2334: and pac.action_status = 'C'
2335: and ppa.action_status = 'C'
2336: and ppa.payroll_id = paf.payroll_id /* Added for bug 5371102 for performance*/
2337: and ppa.date_earned between paf.effective_start_date and paf.effective_end_date /* Added for bug 5371102 for performance*/
2338: and (pay_balance_pkg.get_value(pkg_lump_sum_c_def_bal_id, pac.assignment_action_id,p_registered_employer,null,null,null,null)) > 0 --2610141
2339: and ppa.effective_date between c_year_start
2340: and c_year_end ;
2341:
2342: r_etp_ba_or_bi etp_BA_or_BI%rowtype;
2432: -- Bug#5591993 Removed join for table pay_balance_type to avoid MJC
2433: -- and moved the code to cursor c_union_balance(initliazation code).
2434: cursor csr_unions_2006 (c_balance_type pay_balance_types.balance_type_id%type) is
2435: select distinct nvl(pbt.reporting_name, pbt.balance_name) balance_name
2436: , pay_balance_pkg.get_value(pdb.defined_balance_id, p_max_assignment_action_id, p_registered_employer,null,null,null,null) balance_value
2437: , pdb.defined_balance_id def_id
2438: , pbt.balance_type_id bal_type_id
2439: from pay_element_types_f pet
2440: , per_all_assignments_f paa
3273: ,l_post30jun1983_ratio -- number out
3274: ,l_etp_service_date -- date out
3275: ,l_le_etp_service_date); -- date out /* Bug 4177679 */
3276:
3277: /*Bug 6192381 Introduced call to pay_balance_pkg.get_value to use BBR for ETP Payment Balances
3278: Removed cursor get_etp_payment_value and its call*/
3279:
3280: p_etp_result_table.delete;
3281:
3280: p_etp_result_table.delete;
3281:
3282: p_etp_context_table(1).tax_unit_id := p_registered_employer;
3283: --
3284: pay_balance_pkg.get_value
3285: (p_assignment_action_id => p_max_assignment_action_id
3286: ,p_defined_balance_lst => p_etp_balance_value_tab
3287: ,p_context_lst => p_etp_context_table
3288: ,p_output_table => p_etp_result_table
5012: , l_max_assignment_action_id -- 3019374
5013: , l_final_process_date; -- 3263659
5014: --
5015: if etp_code%found then -- 3019374
5016: if (pay_balance_pkg.get_value(pkg_lump_sum_c_def_bal_id, l_max_assignment_action_id, l_registered_employer,null,null,null,null) > 0) then --2610141
5017: lump_sum_c_found := true;
5018: if g_debug then
5019: hr_utility.set_location('Lump Sum C Payment found.' ,9 );
5020: end if;
5186: -- Changes made for bug 2610141 Start here
5187: --
5188: p_context_table(1).tax_unit_id := l_registered_employer;
5189: --
5190: pay_balance_pkg.get_value
5191: (p_assignment_action_id => l_bbr_assignment_action_id
5192: ,p_defined_balance_lst => p_balance_value_tab
5193: ,p_context_lst => p_context_table
5194: ,p_output_table => p_result_table