[Home] [Help]
239: g_business_group_id number;
240: g_package constant varchar2(30) := 'pay_au_payment_summary';
241: g_legislation_code constant varchar2(2) := 'AU';
242: g_dimension_id pay_balance_dimensions.balance_dimension_id%type;
243: g_balance_type_id pay_balance_types.balance_type_id%type; /*Bug#5591993*/
244: g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
245: --
246: /*
247: ** 4177679 this new flag would be Y when the etp employee date has to be archived
336: , 'Invalidity Payments Life Benefit Part of Prev Term',23
337: , 'Invalidity Payments Transitional Not Part of Prev Term',24
338: , 'Invalidity Payments Transitional Part of Prev Term',25) sort_index, /*4015082 ,6192381 */
339: pdb.defined_balance_id defined_balance_id
340: from pay_balance_types pbt
341: , pay_balance_dimensions pbd
342: , pay_defined_balances pdb
343: where pbt.balance_name in ( 'CDEP'
344: , 'Leave Payments Marginal'
379: , 'ETP Payments Life Benefit Not Part of Prev Term',3
380: , 'ETP Payments Life Benefit Part of Prev Term',4
381: , 'Lump Sum C Payments',5) sort_index,
382: pdb.defined_balance_id defined_balance_id
383: from pay_balance_types pbt
384: , pay_balance_dimensions pbd
385: , pay_defined_balances pdb
386: where pbt.balance_name in ('ETP Payments Transitional Not Part of Prev Term'
387: ,'ETP Payments Transitional Part of Prev Term'
407: /*Bug#5591993*/
408: -- Cursor to fetch the balance type id for Seeded balance Union Fees which will be
409: -- used in Curosr CSR_UNIONS_2006
410: CURSOR c_union_balance IS
411: SELECT balance_type_id from pay_balance_types
412: WHERE balance_name='Union Fees'
413: and legislation_code='AU';
414:
415:
963: --amit
964:
965: Cursor c_fbt_balance is
966: select pdb.defined_balance_id
967: from pay_balance_types pbt,
968: pay_defined_balances pdb,
969: pay_balance_dimensions pbd
970: where pbt.balance_name ='Fringe Benefits'
971: and pbt.balance_type_id = pdb.balance_type_id
1321: (p_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE
1322: ,p_max_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE --2610141
1323: ,p_registered_employer in NUMBER
1324: ,p_database_item_name in ff_database_items.user_name%TYPE
1325: ,p_balance_name in pay_balance_types.balance_name%TYPE
1326: ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
1327: ,p_year_start in DATE
1328: ,p_year_end in DATE
1329: ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
1350: -- cursors c_archive_fbt_info and c_archive_info
1351: -- modified to use balance name instead of archive item name
1352: -- Bug #2454595
1353: -------------------------------------------------------------------+
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,
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,
1363: pay_defined_balances pdb,
1364: pay_balance_dimensions pbd
1365: where pbt.balance_name = c_balance_name
1366: and pbt.legislation_code = 'AU'
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,
1399: pay_defined_balances pdb,
1400: pay_balance_dimensions pbd
1401: WHERE pbt.legislation_code = 'AU'
1402: AND pbt.balance_name = 'Lump Sum E Payments'
1748: ,pay_payroll_actions ppa
1749: ,pay_element_entries_f pee
1750: ,pay_run_results prr
1751: ,pay_element_types_f pet
1752: ,pay_balance_types pbt
1753: WHERE paa.assignment_id = c_assignment_id
1754: AND pet.element_information_category = 'AU_EARNINGS'
1755: AND pet.element_information1 = 'Y'
1756: AND pet.element_information2=pbt.balance_type_id
1783: pay_element_entries_f pee,
1784: pay_run_results prr,
1785: pay_run_result_values prv,
1786: pay_element_types_f pet,
1787: pay_balance_types pbt
1788: where
1789: pee.element_entry_id=c_element_entry_id
1790: and prr.source_id=pee.element_entry_id
1791: and prv.run_result_id=prr.run_result_id
1804: AND plr.rule_type ='ADVANCED_RETRO';
1805:
1806: rec_retro_Allowances Get_retro_Allowances%ROWTYPE;
1807: TYPE
1808: r_ret_allowances IS RECORD(balance_name pay_balance_types.balance_name%TYPE,
1809: balance_value Number);
1810: TYPE
1811: tab_ret_allowances IS TABLE OF r_ret_allowances INDEX BY BINARY_INTEGER;
1812: t_ret_allowances tab_ret_allowances;
2069: , pdb.defined_balance_id def_id
2070: , pbt.balance_type_id bal_type_id
2071: from pay_element_types_f pet
2072: , per_all_assignments_f paa
2073: , pay_balance_types pbt
2074: , pay_defined_balances pdb
2075: --, pay_balance_dimensions pbd
2076: --, per_periods_of_service pps
2077: , pay_payroll_actions ppa
2430: -- will have segment 1 of the Element Developer flex linking the element to a specific balance
2431: -- in conjunction with feeding the legislative balance called 'Union Fees'
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
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
2441: , pay_balance_types pbt
2442: , pay_defined_balances pdb
2443: , pay_payroll_actions ppa
2444: , pay_assignment_actions pac
2445: , pay_run_results prr
2469: --
2470: l_procedure constant varchar2(80) := g_package || '.archive_2006_unions';
2471: l_counter number := 1;
2472: l_4_value number;
2473: l_4_name pay_balance_types.balance_name%type;
2474: l_total_value number := 0;
2475: l_balance_type pay_balance_types.balance_type_id%type;
2476: begin
2477: g_debug := hr_utility.debug_enabled;
2471: l_counter number := 1;
2472: l_4_value number;
2473: l_4_name pay_balance_types.balance_name%type;
2474: l_total_value number := 0;
2475: l_balance_type pay_balance_types.balance_type_id%type;
2476: begin
2477: g_debug := hr_utility.debug_enabled;
2478: --
2479: -- If the financial year permits then perform the union fees archive for 2006/2007.
2659: ) is
2660: select distinct pet.reporting_name
2661: , pet.element_information_category
2662: , pet.element_information1
2663: from pay_balance_types pbt
2664: , pay_balance_feeds_f pbf
2665: , pay_input_values_f piv
2666: , pay_element_types_f pet
2667: , pay_element_entries_f pee
2691: --
2692: /*CURSOR union_def_bal_id
2693: IS
2694: SELECT pdb.defined_balance_id
2695: FROM pay_balance_types pbt
2696: ,pay_defined_balances pdb
2697: ,pay_balance_dimensions pbd
2698: WHERE pbt.balance_name = 'Union Fees'
2699: AND pbt.legislation_code = 'AU'
3178: l_post30jun1983_value NUMBER;
3179: l_etp_service_date date; /* Bug# 2984390 */
3180: v_etp_service_date VARCHAR2(20);
3181: l_le_etp_service_date date; /* Bug 4177679*/
3182: l_balance_name pay_balance_types.balance_name%TYPE;
3183: l_indx NUMBER;
3184: TYPE prepost_rec IS RECORD (item_name varchar2(50),
3185: item_value number );
3186: TYPE prepost_type IS TABLE OF prepost_rec INDEX BY BINARY_INTEGER;
4692: -- is to be called or not(Bug - 2581436 )
4693: -------------------------------------------------+
4694: cursor balance_exists is
4695: select pdb.defined_balance_id
4696: from pay_balance_types pbt,
4697: pay_defined_balances pdb,
4698: pay_balance_dimensions pbd
4699: where pbt.balance_name ='Lump Sum C Payments'
4700: and pbt.balance_type_id = pdb.balance_type_id