[Home] [Help]
289: g_business_group_id number;
290: g_package constant varchar2(30) := 'pay_au_payment_summary';
291: g_legislation_code constant varchar2(2) := 'AU';
292: g_dimension_id pay_balance_dimensions.balance_dimension_id%type;
293: g_balance_type_id pay_balance_types.balance_type_id%type; /*Bug#5591993*/
294: g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
295: g_attribute_id pay_balance_attributes.attribute_id%type; -- bug 7571001
296: g_termination_type varchar2(4); /*Bug#8315198 - Variable to store the termination type*/
297: --
437: , 'ETP Deductions Non Excluded',38
438: , 'ETP Deductions Non Excluded Part of Prev',39 /* end bug 14703826 */
439: ) sort_index, -- end 8711855
440: pdb.defined_balance_id defined_balance_id
441: from pay_balance_types pbt
442: , pay_balance_dimensions pbd
443: , pay_defined_balances pdb
444: where pbt.balance_name in ( 'CDEP'
445: , 'Leave Payments Marginal'
503: , 'ETP Tax Free Payments Life Benefit Part of Prev Term',11
504: , 'ETP Taxable Payments Life Benefit Part of Prev Term',12
505: , 'Lump Sum C Payments',13) sort_index,
506: pdb.defined_balance_id defined_balance_id
507: from pay_balance_types pbt
508: , pay_balance_dimensions pbd
509: , pay_defined_balances pdb
510: where pbt.balance_name in ('ETP Payments Transitional Not Part of Prev Term'
511: ,'ETP Payments Transitional Part of Prev Term'
533: , 'Invalidity Payments',3
534: , 'ETP Deductions Excluded',4
535: , 'ETP Deductions Excluded Part of Prev',5) sort_index,
536: pdb.defined_balance_id defined_balance_id
537: from pay_balance_types pbt
538: , pay_balance_dimensions pbd
539: , pay_defined_balances pdb
540: where pbt.balance_name in (
541: 'ETP Tax Free Payments Excluded'
554: , 'ETP Taxable Payments Non Excluded',2
555: , 'ETP Deductions Non Excluded',3
556: , 'ETP Deductions Non Excluded Part of Prev', 4) sort_index,
557: pdb.defined_balance_id defined_balance_id
558: from pay_balance_types pbt
559: , pay_balance_dimensions pbd
560: , pay_defined_balances pdb
561: where pbt.balance_name in (
562: 'ETP Tax Free Payments Non Excluded'
584: /*Bug#5591993*/
585: -- Cursor to fetch the balance type id for Seeded balance Union Fees which will be
586: -- used in Curosr CSR_UNIONS_2006
587: CURSOR c_union_balance IS
588: SELECT balance_type_id from pay_balance_types
589: WHERE balance_name='Union Fees'
590: and legislation_code='AU';
591:
592:
623: ,'Retro Earnings Spread GT 12 Mths Amount', 3
624: ,'Retro Pre Tax GT 12 Mths Amount', 4
625: ,'Retro Earnings Additional GT 12 Mths Amount', 5) sort_index -- bug 13362286
626: , pdb.defined_balance_id defined_balance_id
627: FROM pay_balance_types pbt,
628: pay_defined_balances pdb,
629: pay_balance_dimensions pbd
630: WHERE pbt.legislation_code = 'AU'
631: AND pbt.balance_name in ( 'Lump Sum E Payments'
645: /* Start of bug9147430 */
646:
647: cursor c_get_fw_fbt_bal_type_id is
648: select balance_type_id
649: from pay_balance_types
650: where balance_name = 'Fringe Benefits'
651: and legislation_code = 'AU';
652:
653: cursor c_get_fw_bal_type_id is
684: , 'Retro Earnings Additional LT12 Prev Mths Amount', 30
685: , 'Retro Earnings Additional LT12 Curr Mths Amount', 31
686: ) sort_index,
687: pbt.balance_type_id balance_type_id
688: from pay_balance_types pbt
689: where pbt.balance_name in (
690: 'Earnings_Total'
691: , 'Leave Payments Marginal'
692: , 'Workplace Giving Deductions'
728: SELECT pbt.balance_type_id,
729: pbt.balance_name
730: FROM pay_balance_attributes pba
731: ,pay_defined_balances pdb
732: ,pay_balance_types pbt
733: ,pay_balance_dimensions pbd
734: WHERE pba.attribute_id = g_attribute_id
735: AND pdb.defined_balance_id = pba.defined_balance_id
736: AND pbt.balance_type_id = pdb.balance_type_id
740:
741: cursor c_get_fw_union_bal_type_id is
742: select distinct pbt.balance_type_id
743: from pay_element_types_f pet
744: , pay_balance_types pbt
745: , pay_input_values_f piv
746: , pay_balance_feeds_f pbf
747: where pet.element_information_category = 'AU_VOLUNTARY DEDUCTIONS'
748: and pet.element_information1 = pbt.balance_type_id
1484: --amit
1485:
1486: Cursor c_fbt_balance is
1487: select pdb.defined_balance_id
1488: from pay_balance_types pbt,
1489: pay_defined_balances pdb,
1490: pay_balance_dimensions pbd
1491: where pbt.balance_name ='Fringe Benefits'
1492: and pbt.balance_type_id = pdb.balance_type_id
2185: (p_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE
2186: ,p_max_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE --2610141
2187: ,p_registered_employer in NUMBER
2188: ,p_database_item_name in ff_database_items.user_name%TYPE
2189: ,p_balance_name in pay_balance_types.balance_name%TYPE
2190: ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
2191: ,p_year_start in DATE
2192: ,p_year_end in DATE
2193: ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
2215: -- cursors c_archive_fbt_info and c_archive_info
2216: -- modified to use balance name instead of archive item name
2217: -- Bug #2454595
2218: -------------------------------------------------------------------+
2219: cursor c_archive_fbt_info(c_balance_name pay_balance_types.balance_name%type,
2220: c_year_end DATE,
2221: c_assignment_id pay_assignment_actions.assignment_id%type)
2222: is
2223: select pay_balance_pkg.get_value(pdb.defined_balance_id,
2223: select pay_balance_pkg.get_value(pdb.defined_balance_id,
2224: p_max_assignment_action_id,
2225: p_registered_employer,
2226: null,null,null,null) --2610141
2227: from pay_balance_types pbt,
2228: pay_defined_balances pdb,
2229: pay_balance_dimensions pbd
2230: where pbt.balance_name = c_balance_name
2231: and pbt.legislation_code = 'AU'
2771: pay_element_entries_f pee,
2772: pay_run_results prr,
2773: pay_run_result_values prv,
2774: pay_element_types_f pet,
2775: pay_balance_types pbt
2776: /* below added for bug 7571001 */
2777: ,PAY_BALANCE_FEEDS_F pbf
2778: ,pay_input_values_f piv
2779: where
2803: AND plr.rule_type ='ADVANCED_RETRO';
2804:
2805: rec_retro_Allowances Get_retro_Allowances%ROWTYPE;
2806: TYPE
2807: r_ret_allowances IS RECORD(balance_name pay_balance_types.balance_name%TYPE,
2808: balance_value Number);
2809: TYPE
2810: tab_ret_allowances IS TABLE OF r_ret_allowances INDEX BY BINARY_INTEGER;
2811: t_ret_allowances tab_ret_allowances;
3111: , 'Foreign Worker Days',4
3112: ) sort_index
3113: FROM pay_defined_balances pdb
3114: ,pay_balance_dimensions pbd
3115: ,pay_balance_types pbt
3116: WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
3117: AND pbd.dimension_name = '_ASG_LE_PTD'
3118: AND pbd.legislation_code = 'AU'
3119: AND pdb.balance_type_id = pbt.balance_type_id
3290:
3291: cursor get_balance_name (c_defined_balance_id pay_defined_balances.defined_balance_id%type) is
3292: select NVL(pbt.reporting_name,pbt.balance_name) balance_name
3293: from pay_defined_balances pdb,
3294: pay_balance_types pbt
3295: where pdb.defined_balance_id = c_defined_balance_id
3296: and pdb.balance_type_id = pbt.balance_type_id
3297: and pdb.business_group_id = g_business_group_id;
3298:
3302: ) is
3303: select distinct pet.reporting_name
3304: , pet.element_information_category
3305: , pet.element_information1
3306: from pay_balance_types pbt
3307: , pay_balance_feeds_f pbf
3308: , pay_input_values_f piv
3309: , pay_element_types_f pet
3310: , pay_element_entries_f pee
3368: l_2006_union_setup_found boolean := false;
3369: l_union_count number;
3370: l_total_value number;
3371: l_4_value number;
3372: l_4_name pay_balance_types.balance_name%type;
3373:
3374: l_fw_f_type varchar2(1);
3375: l_fw_j_type varchar2(1);
3376:
4207: ,p_registered_employer
4208: ,NULL,NULL,NULL,NULL,NULL,NULL,NULL) balance_value
4209: FROM pay_balance_attributes pba
4210: ,pay_defined_balances pdb
4211: ,pay_balance_types pbt
4212: ,pay_balance_dimensions pbd
4213: WHERE pba.attribute_id = g_attribute_id
4214: AND pdb.defined_balance_id = pba.defined_balance_id
4215: AND pbt.balance_type_id = pdb.balance_type_id
4638: -- will have segment 1 of the Element Developer flex linking the element to a specific balance
4639: -- in conjunction with feeding the legislative balance called 'Union Fees'
4640: -- Bug#5591993 Removed join for table pay_balance_type to avoid MJC
4641: -- and moved the code to cursor c_union_balance(initliazation code).
4642: cursor csr_unions_2006 (c_balance_type pay_balance_types.balance_type_id%type) is
4643: select distinct nvl(pbt.reporting_name, pbt.balance_name) balance_name
4644: , pay_balance_pkg.get_value(pdb.defined_balance_id, p_max_assignment_action_id, p_registered_employer,null,null,null,null) balance_value
4645: , pdb.defined_balance_id def_id
4646: , pbt.balance_type_id bal_type_id
4645: , pdb.defined_balance_id def_id
4646: , pbt.balance_type_id bal_type_id
4647: from pay_element_types_f pet
4648: , per_all_assignments_f paa
4649: , pay_balance_types pbt
4650: , pay_defined_balances pdb
4651: , pay_payroll_actions ppa
4652: , pay_assignment_actions pac
4653: , pay_run_results prr
4679: --
4680: l_procedure constant varchar2(80) := g_package || '.archive_2006_unions';
4681: l_counter number := 1;
4682: l_4_value number;
4683: l_4_name pay_balance_types.balance_name%type;
4684: l_total_value number := 0;
4685: l_balance_type pay_balance_types.balance_type_id%type;
4686: idx number; /*bug9147430*/
4687: t_union_bal_sort tab_au_eoy_bal_type := tab_au_eoy_bal_type(); /*bug9147430*/
4681: l_counter number := 1;
4682: l_4_value number;
4683: l_4_name pay_balance_types.balance_name%type;
4684: l_total_value number := 0;
4685: l_balance_type pay_balance_types.balance_type_id%type;
4686: idx number; /*bug9147430*/
4687: t_union_bal_sort tab_au_eoy_bal_type := tab_au_eoy_bal_type(); /*bug9147430*/
4688:
4689: begin
4931: ) is
4932: select distinct pet.reporting_name
4933: , pet.element_information_category
4934: , pet.element_information1
4935: from pay_balance_types pbt
4936: , pay_balance_feeds_f pbf
4937: , pay_input_values_f piv
4938: , pay_element_types_f pet
4939: , pay_element_entries_f pee
4964: --
4965: /*CURSOR union_def_bal_id
4966: IS
4967: SELECT pdb.defined_balance_id
4968: FROM pay_balance_types pbt
4969: ,pay_defined_balances pdb
4970: ,pay_balance_dimensions pbd
4971: WHERE pbt.balance_name = 'Union Fees'
4972: AND pbt.legislation_code = 'AU'
5471: l_post30jun1983_value NUMBER;
5472: l_etp_service_date date; /* Bug# 2984390 */
5473: v_etp_service_date VARCHAR2(20);
5474: l_le_etp_service_date date; /* Bug 4177679*/
5475: l_balance_name pay_balance_types.balance_name%TYPE;
5476: l_indx NUMBER;
5477: l_lump_sum_c_pre_83_amt NUMBER; /* Start 8769345 */
5478: l_lump_sum_c_post_83_amt NUMBER; /* End 8769345 */
5479: TYPE prepost_rec IS RECORD (item_name varchar2(50),
8161: -- is to be called or not(Bug - 2581436 )
8162: -------------------------------------------------+
8163: cursor balance_exists is
8164: select pdb.defined_balance_id
8165: from pay_balance_types pbt,
8166: pay_defined_balances pdb,
8167: pay_balance_dimensions pbd
8168: where pbt.balance_name ='Lump Sum C Payments'
8169: and pbt.legislation_code = g_legislation_code