[Home] [Help]
1: package body pay_balance_pkg as
2: -- $Header: pybaluex.pkb 120.48.12020000.16 2013/02/13 10:32:03 asnell ship $
3: -- Declare tables:
4: type con_name_array is table of ff_contexts.context_name%type
5: index by binary_integer;
387: l_compatibility VARCHAR2(30);
388: BEGIN
389: g_debug := hr_utility.debug_enabled;
390: IF g_debug THEN
391: l_proc := 'pay_balance_pkg.get_oracle_db_version';
392: hr_utility.set_location('Entering:'||l_proc, 5);
393: END IF;
394: -- check to see if the g_oracle_version already exists
395: IF g_oracle_version IS NULL THEN
643: else
644: p_start_date := l_return_date;
645: end if;
646: if g_debug then
647: hr_utility.trace('Leaving pay_balance_pkg.get_period_type_start p_start_date:'||p_start_date);
648: end if;
649:
650: --
651: end get_period_type_start;
821: --
822: if not g_defbal_cache.exists(p_defined_balance_id) then
823: --
824: if g_debug then
825: hr_utility.set_location ('pay_balance_pkg.load_defbal_cache', 10);
826: end if;
827: select DIM.dimension_type,
828: DIM.expiry_checking_code,
829: DIM.expiry_checking_level,
968: end;
969: --
970: end if;
971: if g_debug then
972: hr_utility.set_location ('pay_balance_pkg.load_defbal_cache', 15);
973: end if;
974: --
975: p_defbal_rec := g_defbal_cache(p_defined_balance_id);
976: --
1132: l_context_found boolean;
1133: begin
1134: g_debug := hr_utility.debug_enabled;
1135: if g_debug then
1136: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1137: hr_utility.trace('p_context_name :' || p_context_name);
1138: hr_utility.trace('p_context_value :' || p_context_value);
1139: end if;
1140: l_context_found := FALSE;
1160: --
1161: -- its a new context, insert into tables
1162: --
1163: if g_debug then
1164: hr_utility.set_location ('pay_balance_pkg.set_context', 20);
1165: end if;
1166: select context_id,
1167: data_type
1168: into l_context_id,
1377: --
1378: begin
1379: g_debug := hr_utility.debug_enabled;
1380: if g_debug then
1381: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1382: hr_utility.trace ('DB_item = ' || p_database_name);
1383: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1384: hr_utility.trace ('leg_code = ' || p_legislation_code);
1385: end if;
1446: -- Raise an error, as there are contexts that have not been set up in
1447: -- the pl/sql tables that are required by the route.
1448: --
1449: if g_debug then
1450: hr_utility.set_location ('pay_balance_pkg.run_db_item', 10);
1451: end if;
1452: select context_name
1453: into l_context_name
1454: from ff_contexts
1559: -- 6. Get the sql value (providing there are rows returned)
1560: -- 7. Close the dynamic sql cursor
1561: --
1562: if g_debug then
1563: hr_utility.set_location ('pay_balance_pkg.run_db_item', 15);
1564: end if;
1565: sql_cursor := dbms_sql.open_cursor; -- step 1
1566: --
1567: if g_debug then
1564: end if;
1565: sql_cursor := dbms_sql.open_cursor; -- step 1
1566: --
1567: if g_debug then
1568: hr_utility.set_location ('pay_balance_pkg.run_db_item', 20);
1569: end if;
1570: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
1571: --
1572: -- -- step 3
1602: -- Raise an error, as there are contexts that have not been set up in
1603: -- the pl/sql tables that are required by the route.
1604: --
1605: if g_debug then
1606: hr_utility.set_location ('pay_balance_pkg.run_db_item', 101);
1607: end if;
1608: select context_name
1609: into l_context_name
1610: from ff_contexts
1623: route_parameters_cache.par_val(l_par_num));
1624: end loop;
1625: --
1626: if g_debug then
1627: hr_utility.set_location ('pay_balance_pkg.run_db_item', 25);
1628: end if;
1629: dbms_sql.define_column (sql_cursor, 1, p_db_output, 240); -- step 4
1630: --
1631: if g_debug then
1628: end if;
1629: dbms_sql.define_column (sql_cursor, 1, p_db_output, 240); -- step 4
1630: --
1631: if g_debug then
1632: hr_utility.set_location ('pay_balance_pkg.run_db_item', 30);
1633: end if;
1634: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
1635: --
1636: if (l_rows = 1) then
1634: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
1635: --
1636: if (l_rows = 1) then
1637: if g_debug then
1638: hr_utility.set_location ('pay_balance_pkg.run_db_item', 35);
1639: end if;
1640: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
1641: --
1642: if g_debug then
1639: end if;
1640: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
1641: --
1642: if g_debug then
1643: hr_utility.set_location ('pay_balance_pkg.run_db_item', 40);
1644: end if;
1645: dbms_sql.close_cursor(sql_cursor); -- step 7
1646: --
1647: if g_debug then
1654: --
1655: -- its ok to not find a row
1656: --
1657: if g_debug then
1658: hr_utility.set_location ('pay_balance_pkg.run_db_item', 45);
1659: end if;
1660: p_db_output := null;
1661: else
1662: --
1991: l_route_context_usage_tab t_route_context_usage_tab;
1992: begin
1993: -- bug 13692929 if only context is assignment_action_id and only placeholder is balance_type_id use NDS
1994: if g_debug then
1995: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 10);
1996: end if;
1997: l_route_context_usage_tab.delete;
1998: cnt := 1;
1999: l_simplebind :=TRUE;
2055:
2056:
2057: else --{ complex balance -process using dbms_sql
2058: if g_debug then
2059: hr_utility.set_location ('pay_balance_pkg.process_balance_statement. using dbms_sql', 15);
2060: end if;
2061:
2062: --
2063: -- Now execute the SQL statement using dynamic pl/sql:
2072: -- 6. Get the sql value (providing there are rows returned)
2073: -- 7. Close the dynamic sql cursor
2074: --
2075: if g_debug then
2076: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 35);
2077: end if;
2078: sql_cursor := dbms_sql.open_cursor; -- step 1
2079: --
2080: if g_debug then
2077: end if;
2078: sql_cursor := dbms_sql.open_cursor; -- step 1
2079: --
2080: if g_debug then
2081: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 40);
2082: end if;
2083: dbms_sql.parse(sql_cursor, p_statement, dbms_sql.v7); -- step 2
2084: --
2085: -- -- step 3
2088: --
2089: -- for c1rec in ro_context (p_route_id) loop
2090: for a in l_route_context_usage_tab.FIRST .. l_route_context_usage_tab.LAST loop
2091: if g_debug then
2092: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 45);
2093: end if;
2094: l_count := 0;
2095: l_context_found := FALSE;
2096: while (l_count < no_rows_con_tab) loop
2103: -- Found a bind variable
2104: --
2105: if (con_type_tab (l_count) = 'D') then
2106: if g_debug then
2107: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 50);
2108: end if;
2109: dbms_sql.bind_variable
2110: (sql_cursor,
2111: con_name_tab(l_count),
2111: con_name_tab(l_count),
2112: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2113: else
2114: if g_debug then
2115: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 55);
2116: end if;
2117: dbms_sql.bind_variable
2118: (sql_cursor,
2119: con_name_tab(l_count),
2129: -- Raise an error, as there are contexts that have not been set up in
2130: -- the pl/sql tables that are required by the route.
2131: --
2132: if g_debug then
2133: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 60);
2134: end if;
2135: --
2136: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2137: hr_utility.set_message_token ('CONTEXT_NAME', l_route_context_usage_tab(a).context_name);
2142: -- Now bind the where clause fillers (the 'U' values)
2143: --
2144: if (p_batch_mode = FALSE) then
2145: if g_debug then
2146: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 62);
2147: hr_utility.trace('p_balance_type_id = '||p_balance_type_id);
2148: end if;
2149: dbms_sql.bind_variable (sql_cursor, 'U1',
2150: p_balance_type_id);
2156: l_simplebind := FALSE;
2157: end if;
2158: --
2159: if g_debug then
2160: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 65);
2161: end if;
2162: dbms_sql.define_column (sql_cursor, 1, l_db_output, 60); -- step 4
2163: if (p_batch_mode = TRUE) then
2164: dbms_sql.define_column (sql_cursor, 2, l_balance_type_id);
2164: dbms_sql.define_column (sql_cursor, 2, l_balance_type_id);
2165: end if;
2166: --
2167: if g_debug then
2168: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 70);
2169: end if;
2170: ignore := dbms_sql.execute(sql_cursor);
2171: --
2172: l_retrieve := TRUE;
2175: --
2176: l_rows := dbms_sql.fetch_rows(sql_cursor);
2177: --
2178: if g_debug then
2179: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 80);
2180: end if;
2181: --
2182: if (l_rows > 0) then
2183: --
2181: --
2182: if (l_rows > 0) then
2183: --
2184: if g_debug then
2185: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 85);
2186: end if;
2187: dbms_sql.column_value (sql_cursor, 1, l_db_output);
2188: if (p_batch_mode = TRUE) then
2189: dbms_sql.column_value (sql_cursor, 2, l_balance_type_id);
2205: --
2206: -- Have we already got a value
2207: --
2208: if g_debug then
2209: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 87);
2210: end if;
2211: if (l_value_retrieved = TRUE) then
2212: if g_debug then
2213: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2209: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 87);
2210: end if;
2211: if (l_value_retrieved = TRUE) then
2212: if g_debug then
2213: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2214: end if;
2215: dbms_sql.close_cursor(sql_cursor);
2216: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2217: hr_utility.raise_error;
2248: --
2249: When Others Then
2250: l_error_text := sqlerrm;
2251: if g_debug then
2252: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 100);
2253: end if;
2254: If (dbms_sql.is_open(sql_cursor)) then
2255: if g_debug then
2256: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2252: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 100);
2253: end if;
2254: If (dbms_sql.is_open(sql_cursor)) then
2255: if g_debug then
2256: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2257: end if;
2258: dbms_sql.close_cursor(sql_cursor);
2259: End If;
2260: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2321: l_start_ptr number;
2322: --
2323: begin
2324: if g_debug then
2325: hr_utility.set_location ('pay_balance_pkg.run_rr_route', 1);
2326: end if;
2327: --
2328: -- If we are in batch mode then initialise the returns
2329: --
2366: -- Raise an error, as there are contexts that have not been set up in
2367: -- the pl/sql tables that are required by the route.
2368: --
2369: if g_debug then
2370: hr_utility.set_location ('pay_balance_pkg.run_rr_route', 10);
2371: end if;
2372: select context_name
2373: into l_context_name
2374: from ff_contexts
2400: --
2401: if (cached = FALSE) then
2402: cached := TRUE;
2403: if g_debug then
2404: hr_utility.set_location('pay_balance_pkg.run_rr_route', 20);
2405: end if;
2406: l_ora_db_vers := get_oracle_db_version;
2407: if (nvl(l_ora_db_vers, 0) < 9.0) then
2408: g_low_volume := 'Y';
2573:
2574: --
2575: BEGIN
2576: if g_debug then
2577: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2578: hr_utility.trace ('User name: '|| p_user_name);
2579: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2580: hr_utility.trace ('leg_code: ' || p_legislation_code);
2581: hr_utility.trace ('p_route_type: ' || p_route_type);
2650: -- Raise an error, as there are contexts that have not been set up in
2651: -- the pl/sql tables that are required by the route.
2652: --
2653: if g_debug then
2654: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 10);
2655: end if;
2656: select context_name
2657: into l_context_name
2658: from ff_contexts
2710: ' */ NVL(SUM(prb.balance_value),0) FROM ' || l_text;
2711: end if;
2712: --
2713: if g_debug then
2714: hr_utility.set_location('pay_balance_pkg.get_run_balance', 15);
2715: end if;
2716: --
2717: elsif p_route_type = 'RR' then
2718: --
2718: --
2719: -- Should never get here since all RRs are handled by run_rr_route now.
2720: --
2721: if g_debug then
2722: hr_utility.set_location('pay_balance_pkg.get_run_balance', 20);
2723: end if;
2724: --
2725: hr_general.assert_condition(false);
2726: --
2730: -- are mapped in the route and call route sql with the correct sequence of binds
2731: if l_simplebind then --{ simplebind
2732: if l_assact_pos > l_bt_pos and l_assact_pos > l_bd_pos then --{ assact_3rd
2733: if g_debug then
2734: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 22);
2735: end if;
2736:
2737: l_var3 := l_assact_id;
2738: if l_bt_pos < l_bd_pos then
2736:
2737: l_var3 := l_assact_id;
2738: if l_bt_pos < l_bd_pos then
2739: if g_debug then
2740: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 24);
2741: end if;
2742: l_var1 := l_bt_val;
2743: l_var2 := l_bd_val;
2744: else
2747: end if;
2748: -- case statements for all combinations of bind variable order
2749: else if l_assact_pos < l_bt_pos and l_assact_pos < l_bd_pos then --{ assact_1st
2750: if g_debug then
2751: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 26);
2752: end if;
2753:
2754: l_var1 := l_assact_id;
2755: if l_bt_pos < l_bd_pos then
2753:
2754: l_var1 := l_assact_id;
2755: if l_bt_pos < l_bd_pos then
2756: if g_debug then
2757: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 28);
2758: end if;
2759: l_var2 := l_bt_val;
2760: l_var3 := l_bd_val;
2761: else
2763: l_var2 := l_bd_val;
2764: end if;
2765: else if l_assact_pos < l_bt_pos and l_assact_pos > l_bd_pos then --{ assact_2nd
2766: if g_debug then
2767: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 30);
2768: end if;
2769:
2770: l_var2 := l_assact_id;
2771: if l_bt_pos < l_bd_pos then
2769:
2770: l_var2 := l_assact_id;
2771: if l_bt_pos < l_bd_pos then
2772: if g_debug then
2773: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 32);
2774: end if;
2775: l_var1 := l_bt_val;
2776: l_var3 := l_bd_val;
2777: else
2807: -- 6. Get the sql value (providing there are rows returned)
2808: -- 7. Close the dynamic sql cursor
2809: --
2810: if g_debug then
2811: hr_utility.set_location ('pay_balance_pkg.get_run_balance using dbms_sql', 35);
2812: end if;
2813: sql_cursor := dbms_sql.open_cursor; -- step 1
2814: --
2815: if g_debug then
2812: end if;
2813: sql_cursor := dbms_sql.open_cursor; -- step 1
2814: --
2815: if g_debug then
2816: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 40);
2817: end if;
2818: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
2819: --
2820: -- -- step 3
2822: -- variables, starting with the contexts (B values).
2823: --
2824: for l_cxt_num in 1..route_contexts_cache.sz loop
2825: if g_debug then
2826: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 45);
2827: end if;
2828: l_count := 0;
2829: l_context_found := FALSE;
2830: while (l_count < no_rows_con_tab) loop
2837: -- Found a bind variable
2838: --
2839: if (con_type_tab (l_count) = 'D') then
2840: if g_debug then
2841: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 50);
2842: end if;
2843: dbms_sql.bind_variable
2844: (sql_cursor,
2845: con_name_tab(l_count),
2845: con_name_tab(l_count),
2846: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2847: else
2848: if g_debug then
2849: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 55);
2850: end if;
2851: dbms_sql.bind_variable
2852: (sql_cursor,
2853: con_name_tab(l_count),
2863: -- Raise an error, as there are contexts that have not been set up in
2864: -- the pl/sql tables that are required by the route.
2865: --
2866: if g_debug then
2867: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 60);
2868: end if;
2869: select context_name
2870: into l_context_name
2871: from ff_contexts
2880: -- Now bind the where clause fillers (the 'U' values)
2881: --
2882: for l_par_num in 1..route_parameters_cache.sz loop
2883: if g_debug then
2884: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 62);
2885: end if;
2886: dbms_sql.bind_variable (sql_cursor, 'U'||to_char(route_parameters_cache.seq_no(l_par_num)),
2887: route_parameters_cache.par_val(l_par_num));
2888: end loop;
2887: route_parameters_cache.par_val(l_par_num));
2888: end loop;
2889: --
2890: if g_debug then
2891: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 65);
2892: end if;
2893: dbms_sql.define_column (sql_cursor, 1, p_db_output, 80); -- step 4
2894: --
2895: if g_debug then
2892: end if;
2893: dbms_sql.define_column (sql_cursor, 1, p_db_output, 80); -- step 4
2894: --
2895: if g_debug then
2896: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 70);
2897: end if;
2898: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
2899: --
2900: if (l_rows = 1) then
2898: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
2899: --
2900: if (l_rows = 1) then
2901: if g_debug then
2902: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 75);
2903: end if;
2904: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
2905: --
2906: if g_debug then
2903: end if;
2904: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
2905: --
2906: if g_debug then
2907: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 80);
2908: end if;
2909: dbms_sql.close_cursor(sql_cursor); -- step 7
2910: --
2911: if g_debug then
2918: --
2919: -- its ok to not find a row
2920: --
2921: if g_debug then
2922: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 85);
2923: end if;
2924: p_db_output := null;
2925: else
2926: --
2936: -- More than 1 row have been returned. We must error as DB items can
2937: -- only return 1 row.
2938: --
2939: if g_debug then
2940: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 95);
2941: end if;
2942: dbms_sql.close_cursor(sql_cursor);
2943: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2944: hr_utility.raise_error;
2953: --
2954: When Others Then
2955: l_error_text := sqlerrm;
2956: if g_debug then
2957: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 100);
2958: end if;
2959: If (dbms_sql.is_open(sql_cursor)) then
2960: if g_debug then
2961: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2957: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 100);
2958: end if;
2959: If (dbms_sql.is_open(sql_cursor)) then
2960: if g_debug then
2961: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2962: end if;
2963: dbms_sql.close_cursor(sql_cursor);
2964: End If;
2965: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
3065: l_jur_lvl pay_balance_types.jurisdiction_level%type;
3066: --
3067: BEGIN
3068: if g_debug then
3069: hr_utility.set_location('Entering pay_balance_pkg.get_run_bal', 10);
3070: end if;
3071: --
3072: if (p_route_type = 'RR') then
3073: --
3071: --
3072: if (p_route_type = 'RR') then
3073: --
3074: if g_debug then
3075: hr_utility.set_location('Entering pay_balance_pkg.get_run_bal', 15);
3076: end if;
3077: select pdb.balance_type_id,
3078: pdr.route_id,
3079: pdr.balance_type_column,
3117: fetch get_vals into l_user_name, l_business_group_id, l_legislation_code;
3118: close get_vals;
3119: --
3120: if g_debug then
3121: hr_utility.set_location('pay_balance_pkg.get_run_bal', 20);
3122: hr_utility.trace('l_user_entity_name is: '||l_user_name);
3123: end if;
3124: l_balance_val := get_run_balance(l_user_name
3125: ,l_business_group_id
3156: l_defbal_rec t_def_bal_rec;
3157: --
3158: begin
3159: if g_debug then
3160: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 5);
3161: end if;
3162: --
3163: -- First load the cache
3164: load_defbal_cache(p_defined_balance_id,
3165: l_defbal_rec);
3166: --
3167: if l_defbal_rec.rr_ptr is not null then
3168: if g_debug then
3169: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 10);
3170: end if;
3171: l_balance_value := get_run_balance(p_defined_balance_id
3172: ,g_dimrou_cache(l_defbal_rec.rr_ptr).priority
3173: ,g_dimrou_cache(l_defbal_rec.rr_ptr).route_type);
3173: ,g_dimrou_cache(l_defbal_rec.rr_ptr).route_type);
3174: else
3175: --
3176: if g_debug then
3177: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 20);
3178: end if;
3179: if (l_defbal_rec.start_rb_ptr is not null) then
3180: hr_general.assert_condition(false);
3181: else
3179: if (l_defbal_rec.start_rb_ptr is not null) then
3180: hr_general.assert_condition(false);
3181: else
3182: if g_debug then
3183: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 30);
3184: end if;
3185: l_balance_value := fnd_number.canonical_to_number(run_db_item(p_defined_balance_id));
3186: end if;
3187: end if;
3189: -- Setup the return values
3190: --
3191: p_balance_value := l_balance_value;
3192: if g_debug then
3193: hr_utility.set_location ('Leaving: pay_balance_pkg.get_rr_value', 45);
3194: end if;
3195: --
3196: end get_rr_value;
3197: --
3349: l_position number;
3350: --
3351: begin
3352: if g_debug then
3353: hr_utility.set_location ('Entering: pay_balance_pkg.get_rb_value', 5);
3354: end if;
3355: --
3356: -- First load the cache
3357: load_defbal_cache(p_defined_balance_id,
3372: p_asg_action_id,
3373: l_valid);
3374: --
3375: if g_debug then
3376: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 20);
3377: end if;
3378: --
3379: -- now know that run balance exists and is valid, so call run_db_item to
3380: -- return the balance_value.
3380: -- return the balance_value.
3381: --
3382: if l_valid = 'V' then
3383: if g_debug then
3384: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 25);
3385: end if;
3386: l_balance_value := get_run_balance(p_defined_balance_id
3387: ,g_dimrou_cache(l_position).priority
3388: ,g_dimrou_cache(l_position).route_type);
3394: end loop;
3395: end if;
3396: --
3397: if g_debug then
3398: hr_utility.set_location ('pay_balance_pkg.get_rb_value l_balance_value:'||l_balance_value, 35);
3399: end if;
3400: --
3401: --
3402: -- Setup the return values
3403: --
3404: p_found := l_balval_found;
3405: p_balance_value := l_balance_value;
3406: if g_debug then
3407: hr_utility.set_location ('Leaving: pay_balance_pkg.get_rb_value', 45);
3408: end if;
3409: --
3410: end get_rb_value;
3411: --
3456: l_value_found number;
3457: l_ora_db_vers number; -- db version number
3458: begin
3459: if g_debug then
3460: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3461: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3462: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3463: end if;
3464: if (p_expiry_checking_level = 'N') then
3471: --
3472: -- get the payroll_action_id for the balance (ie. owner):
3473: --
3474: if g_debug then
3475: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 10);
3476: end if;
3477: select PAY.payroll_action_id,
3478: PAY.effective_date
3479: into l_bal_owner_pay_action,
3485: --
3486: -- get the actual payroll information for this assignment action:
3487: --
3488: if g_debug then
3489: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 15);
3490: end if;
3491: select PAY.payroll_action_id,
3492: PAY.effective_date
3493: into l_payroll_action,
3552: -- 5. Get the variable value (providing there are rows returned)
3553: -- 6. Close the dynamic sql cursor
3554: --
3555: if g_debug then
3556: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 20);
3557: end if;
3558: -- bug 11849842 use execute immediate to reduce parsing
3559: l_ora_db_vers := get_oracle_db_version;
3560: if (nvl(l_ora_db_vers, 0) >= 10.0) then
3576: else
3577: sql_cursor := dbms_sql.open_cursor; -- step 1
3578: --
3579: if g_debug then
3580: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 25);
3581: end if;
3582: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
3583: --
3584: if g_debug then
3581: end if;
3582: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
3583: --
3584: if g_debug then
3585: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 30);
3586: end if;
3587: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', -- step 3:
3588: l_bal_owner_pay_action);
3589: --
3613: dbms_sql.bind_variable(sql_cursor, 'l_expiry_information',
3614: l_expiry_information);
3615: --
3616: if g_debug then
3617: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 35);
3618: end if;
3619: l_rows := dbms_sql.execute (sql_cursor); -- step 4
3620: --
3621: if (l_rows = 1) then
3619: l_rows := dbms_sql.execute (sql_cursor); -- step 4
3620: --
3621: if (l_rows = 1) then
3622: if g_debug then
3623: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 40);
3624: end if;
3625: dbms_sql.variable_value(sql_cursor, 'l_expiry_information', -- step 5
3626: l_expiry_information);
3627: --
3625: dbms_sql.variable_value(sql_cursor, 'l_expiry_information', -- step 5
3626: l_expiry_information);
3627: --
3628: if g_debug then
3629: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 45);
3630: end if;
3631: dbms_sql.close_cursor(sql_cursor); -- step 6
3632: elsif (l_rows = 0) then
3633: dbms_sql.close_cursor(sql_cursor);
3639: -- More than 1 row has been returned. We must error as package call can
3640: -- only return 1 row, so this condition should never occur !
3641: --
3642: if g_debug then
3643: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 111);
3644: end if;
3645: dbms_sql.close_cursor(sql_cursor);
3646: hr_utility.raise_error;
3647: end if;
3655: -- payroll action.
3656: --
3657: if (p_expiry_checking_level in ('A', 'P', 'E')) then
3658: if g_debug then
3659: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 50);
3660: end if;
3661: p_balance_expired := l_expiry_information;
3662: else -- date level
3663: if g_debug then
3660: end if;
3661: p_balance_expired := l_expiry_information;
3662: else -- date level
3663: if g_debug then
3664: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 55);
3665: end if;
3666: l_jul_effect_date := to_number (to_char (l_effective_date, 'J'));
3667: --
3668: if (l_expiry_information < l_jul_effect_date) then
3909: while (l_count < no_rows_con_tab) loop
3910: if (con_name_tab (l_count) = 'JURISDICTION_CODE') then
3911: --
3912: if g_debug then
3913: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 15)
3914: ;
3915: end if;
3916: loc_value_tab (l_count) := substr (loc_value_tab(l_count), 1,
3917: p_defbal_rec.jurisdiction_lvl);
3936: END IF;
3937: --
3938: LOOP
3939: if g_debug then
3940: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 20);
3941: end if;
3942: --
3943: IF p_defbal_rec.dimension_type = 'A' THEN
3944: FETCH bal_contexts_asg INTO l_latest_balance_id,
3970: l_bal_con_str := null;
3971: l_cursor_executed := FALSE;
3972: for c2rec in bal_context_values (l_latest_balance_id) loop
3973: if g_debug then
3974: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 25);
3975: end if;
3976: --
3977: -- Try to find the context:
3978: --
4038: -- Else carry on to the next latest_balance_id.
4039: --
4040: if ((l_found_all_contexts = TRUE) OR (l_cursor_executed = FALSE)) then
4041: if g_debug then
4042: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 35);
4043: end if;
4044: l_balance_found := TRUE;
4045: exit; -- exit c1rec
4046: end if;
4054: --
4055: -- If we could not find a latest balance, then derive from ff_routes
4056: --
4057: if g_debug then
4058: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 37);
4059: end if;
4060: if (l_balance_found = TRUE) then
4061: if g_debug then
4062: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
4058: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 37);
4059: end if;
4060: if (l_balance_found = TRUE) then
4061: if g_debug then
4062: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
4063: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
4064: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
4065: end if;
4066: --
4090: -- O.K. We cannot use the latest balance value, now check if the
4091: -- expired details can by used.
4092: --
4093: if g_debug then
4094: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 140
4095: );
4096: end if;
4097: select count(*)
4098: into l_num_of_runs
4114: -- in the specified period.
4115: --
4116: if l_num_of_runs = 1 then
4117: if g_debug then
4118: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value',
4119: 150);
4120: end if;
4121: l_bal_owner_asg_action := l_bal_expired_action;
4122: l_balance_value := l_bal_expired_value;
4123: l_expiry_needed := TRUE;
4124: else
4125: -- check if can use previous balance value
4126: if g_debug then
4127: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value',
4128: 160);
4129: end if;
4130: select count(*)
4131: into l_num_of_runs
4141: and l_bal_asg_act_seq;
4142: --
4143: if l_num_of_runs = 1 then
4144: if g_debug then
4145: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value'
4146: , 170);
4147: end if;
4148: l_bal_owner_asg_action := l_prev_action;
4149: l_balance_value := l_prev_value;
4161: end if;
4162: --
4163: end if;
4164: if g_debug then
4165: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 55);
4166: end if;
4167: --
4168: if (l_expiry_needed = TRUE) then
4169: --
4363: --
4364: l_cnt := l_cnt + 1;
4365: --
4366: if g_debug then
4367: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4368: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4369: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4370: end if;
4371: --
4396: -- O.K. We cannot use the latest balance value, now check if the
4397: -- expired details can by used.
4398: --
4399: if g_debug then
4400: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 140
4401: );
4402: end if;
4403: select count(*)
4404: into l_num_of_runs
4420: -- in the specified period.
4421: --
4422: if l_num_of_runs = 1 then
4423: if g_debug then
4424: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value',
4425: 150);
4426: end if;
4427: l_bal_owner_asg_action := lbrec.expired_assignment_action_id;
4428: l_balance_value := lbrec.expired_value;
4430: l_expiry_needed := TRUE;
4431: else
4432: -- check if can use previous balance value
4433: if g_debug then
4434: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value',
4435: 160);
4436: end if;
4437: select count(*)
4438: into l_num_of_runs
4448: and lbrec.current_action_seq;
4449: --
4450: if l_num_of_runs = 1 then
4451: if g_debug then
4452: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value'
4453: , 170);
4454: end if;
4455: l_bal_owner_asg_action := lbrec.prev_assignment_action_id;
4456: l_balance_value := lbrec.prev_balance_value;
4479: l_expiry_date := lbrec.expiry_date;
4480: --
4481: end if;
4482: if g_debug then
4483: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 55);
4484: end if;
4485: --
4486: if (l_expiry_needed = TRUE) then
4487: --
5690: --
5691: begin
5692: g_debug := hr_utility.debug_enabled;
5693: if g_debug then
5694: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5695: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5696: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5697: end if;
5698: --
5827: --
5828: begin
5829: g_debug := hr_utility.debug_enabled;
5830: if g_debug then
5831: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5832: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5833: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5834: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5835: end if;
5845: --
5846: -- get the payroll information
5847: --
5848: if g_debug then
5849: hr_utility.set_location ('pay_balance_pkg.get_value', 10);
5850: end if;
5851: select ASSIGN.payroll_id,
5852: ASSIGN.business_group_id,
5853: PAYROLL.consolidation_set_id
5866: -- If there is a time period id, then get it, else use a null value:
5867: --
5868: begin
5869: if g_debug then
5870: hr_utility.set_location ('pay_balance_pkg.get_value', 12);
5871: end if;
5872: select TIMEP.time_period_id
5873: into l_time_period_id
5874: from per_time_periods TIMEP
5877: and TIMEP.end_date;
5878: exception
5879: when no_data_found then
5880: if g_debug then
5881: hr_utility.set_location ('pay_balance_pkg.get_value', 13);
5882: end if;
5883: l_time_period_id := null;
5884: end;
5885: --
5891: --
5892: -- insert a temporary row into pay_payroll_actions
5893: --
5894: if g_debug then
5895: hr_utility.set_location ('pay_balance_pkg.get_value', 20);
5896: end if;
5897: insert into pay_payroll_actions
5898: (payroll_action_id,
5899: action_type,
5921: --
5922: -- now insert the assignment action:
5923: --
5924: if g_debug then
5925: hr_utility.set_location ('pay_balance_pkg.get_value', 25);
5926: end if;
5927: hrassact.inassact (pactid => l_pay_action_id,
5928: asgid => p_assignment_id,
5929: p_asg_lock => l_asg_lock);
5930: --
5931: -- retrieve the assignment action id:
5932: --
5933: if g_debug then
5934: hr_utility.set_location ('pay_balance_pkg.get_value', 30);
5935: end if;
5936: select assignment_action_id
5937: into l_ass_action_id
5938: from pay_assignment_actions
6368: --
6369: BEGIN
6370: g_debug := hr_utility.debug_enabled;
6371: if g_debug then
6372: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6373: end if;
6374: --
6375: l_ora_db_vers := get_oracle_db_version;
6376: if HRASSACT.CHECK_LATEST_BALANCES = TRUE then --{ check balance true
6398: select defined_balance_id from pay_defined_balances db
6399: where balance_type_id = p_balance_type_id);
6400:
6401: if g_debug then
6402: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances SQL%ROWCOUNT:'||SQL%ROWCOUNT,20);
6403: end if;
6404: -- Bug 16048613, calling cursors with 'no_unnest' hints if db version is less than 11g
6405: if(nvl(l_ora_db_vers,0) < 11.0) then
6406: for each_row in get_def_bals(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6408: update pay_balance_validation
6409: set run_balance_status = 'I'
6410: where defined_balance_id = each_row.defined_balance_id;
6411: if g_debug then
6412: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances asg_level', 15);
6413: end if;
6414: end loop;
6415: -- repeat for any group level dimensions
6416: for grp_row in get_def_bals_grp(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6418: update pay_balance_validation
6419: set run_balance_status = 'I'
6420: where defined_balance_id = grp_row.defined_balance_id;
6421: if g_debug then
6422: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances grp_level', 17);
6423: end if;
6424: end loop;
6425: else
6426: for each_row in get_def_bals_g(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6428: update pay_balance_validation
6429: set run_balance_status = 'I'
6430: where defined_balance_id = each_row.defined_balance_id;
6431: if g_debug then
6432: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances asg_level', 18);
6433: end if;
6434: end loop;
6435: -- repeat for any group level dimensions
6436: for grp_row in get_def_bals_grp_g(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6438: update pay_balance_validation
6439: set run_balance_status = 'I'
6440: where defined_balance_id = grp_row.defined_balance_id;
6441: if g_debug then
6442: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances grp_level', 19);
6443: end if;
6444: end loop;
6445: end if;
6446: if g_debug then
6443: end if;
6444: end loop;
6445: end if;
6446: if g_debug then
6447: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6448: end if;
6449:
6450: end if; --} end results exist
6451: --
6468: --
6469: BEGIN
6470: g_debug := hr_utility.debug_enabled;
6471: if g_debug then
6472: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6473: end if;
6474: for each_row in get_def_bals(p_balance_type_id) loop
6475: --
6476: update pay_balance_validation
6476: update pay_balance_validation
6477: set run_balance_status = 'I'
6478: where defined_balance_id = each_row.defined_balance_id;
6479: if g_debug then
6480: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
6481: end if;
6482: end loop;
6483: if g_debug then
6484: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6480: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
6481: end if;
6482: end loop;
6483: if g_debug then
6484: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6485: end if;
6486: END invalidate_run_balances;
6487: --------------------------------------------------------------------------
6488: -- get_run_result_info
6708: current_ptr number;
6709: begin
6710: --
6711: if g_debug then
6712: hr_utility.set_location('Entering: pay_balance_pkg.search_rb_cache', 5);
6713: end if;
6714: if (p_grp_rb_ptr_list.exists(p_defined_balanceid)) then
6715: --
6716: current_ptr := p_grp_rb_ptr_list(p_defined_balanceid);
6736: then
6737: --
6738: l_found := TRUE;
6739: if g_debug then
6740: hr_utility.set_location('pay_balance_pkg.search_rb_cache', 10);
6741: end if;
6742: --
6743: else
6744: --
6744: --
6745: prev_ptr := current_ptr;
6746: current_ptr := p_int_mlt_thrd_cache(current_ptr).next;
6747: if g_debug then
6748: hr_utility.set_location('pay_balance_pkg.search_rb_cache', 15);
6749: end if;
6750: --
6751: end if;
6752: --
6762: p_current_ptr := current_ptr;
6763: p_previous_ptr := prev_ptr;
6764: --
6765: if g_debug then
6766: hr_utility.set_location('Leaving: pay_balance_pkg.search_rb_cache', 20);
6767: end if;
6768: --
6769: end search_rb_cache;
6770: --
6842: --
6843: if p_amount <> 0 then
6844: --
6845: if g_debug then
6846: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 25);
6847: end if;
6848: --
6849: if not p_multi_thread then -- i.e. if in single thread mode
6850: --
6879: --
6880: else
6881: close get_row_to_update;
6882: if g_debug then
6883: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6884: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6885: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6886: hr_utility.trace('contrib amt: '||to_char(p_amount));
6887: end if;
6919: --
6920: if (l_found = TRUE) then
6921: --
6922: if g_debug then
6923: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 50);
6924: hr_utility.trace('MULTI THREADED UPDATE');
6925: end if;
6926: update pay_run_balances
6927: set balance_value = balance_value - p_amount
6929: --
6930: else -- no row for this balance
6931: if g_debug then
6932: hr_utility.trace('MULTI THREADED INSERT');
6933: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 55);
6934: end if;
6935: --
6936: --
6937: select pay_run_balances_s.nextval
6985: ,p_source_number2
6986: ,p_organization_id
6987: );
6988: if g_debug then
6989: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 60);
6990: end if;
6991: --
6992: -- cache the row details
6993: --
7018: p_int_mlt_thrd_cache(l_previous_ptr).next := l_cache_ct;
7019: end if;
7020: --
7021: if g_debug then
7022: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 65);
7023: end if;
7024: --
7025: end if;
7026: --
7026: --
7027: end if;
7028: else -- not > 0
7029: if g_debug then
7030: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 70);
7031: end if;
7032: end if;
7033: --
7034: end subtract_from_grp_bal;
7116: --
7117: BEGIN
7118: g_debug := hr_utility.debug_enabled;
7119: if g_debug then
7120: hr_utility.set_location('Entering: pay_balance_pkg.remove_asg_contribs', 5);
7121: end if;
7122: --
7123: if (p_payroll_action_id <> g_payroll_action) then
7124: --
7280: l_cnt := l_cnt + 1;
7281: --
7282: end loop;
7283: --
7284: pay_balance_pkg.get_value (p_assignment_action_id,
7285: g_rlb_asg_defbals,
7286: l_contexts,
7287: FALSE,
7288: FALSE,
7334: end if;
7335: --
7336: end if;
7337: if g_debug then
7338: hr_utility.set_location('Leaving: pay_balance_pkg.remove_asg_contribs', 80);
7339: end if;
7340: END remove_asg_contribs;
7341: --
7342: --------------------------------------------------------------------------
7534: begin
7535: --
7536: --
7537: if g_debug then
7538: hr_utility.set_location('pay_balance_pkg.create_run_balance', 30);
7539: hr_utility.trace('Getting Balance'||p_def_bal_id);
7540: end if;
7541: /* First setup the contexts */
7542: if (nvl(p_contexts.tax_unit_id, -999) <> nvl(p_contexts.prv_tax_unit_id, -999)) then
7539: hr_utility.trace('Getting Balance'||p_def_bal_id);
7540: end if;
7541: /* First setup the contexts */
7542: if (nvl(p_contexts.tax_unit_id, -999) <> nvl(p_contexts.prv_tax_unit_id, -999)) then
7543: pay_balance_pkg.set_context('TAX_UNIT_ID', p_contexts.tax_unit_id);
7544: p_contexts.prv_tax_unit_id := p_contexts.tax_unit_id;
7545: if p_contexts.tax_unit_id is null then
7546: p_contexts.tu_set := FALSE;
7547: else
7549: end if;
7550: end if;
7551: if (nvl(p_contexts.jurisdiction_code, 'NULL') <> nvl(p_contexts.prv_jurisdiction_code, 'NULL'))
7552: then
7553: pay_balance_pkg.set_context('JURISDICTION_CODE',p_contexts.jurisdiction_code);
7554: p_contexts.prv_jurisdiction_code := p_contexts.jurisdiction_code;
7555: if p_contexts.jurisdiction_code is null then
7556: p_contexts.jc_set := FALSE;
7557: else
7558: p_contexts.jc_set := TRUE;
7559: end if;
7560: end if;
7561: if (nvl(p_contexts.source_id, -999) <> nvl(p_contexts.prv_source_id, -999)) then
7562: pay_balance_pkg.set_context('SOURCE_ID', p_contexts.source_id);
7563: p_contexts.prv_source_id := p_contexts.source_id;
7564: if p_contexts.source_id is null then
7565: p_contexts.si_set := FALSE;
7566: else
7567: p_contexts.si_set := TRUE;
7568: end if;
7569: end if;
7570: if (nvl(p_contexts.source_text, 'NULL') <> nvl(p_contexts.prv_source_text, 'NULL')) then
7571: pay_balance_pkg.set_context('SOURCE_TEXT', p_contexts.source_text);
7572: p_contexts.prv_source_text := p_contexts.source_text;
7573: if p_contexts.source_text is null then
7574: p_contexts.st_set := FALSE;
7575: else
7576: p_contexts.st_set := TRUE;
7577: end if;
7578: end if;
7579: if (nvl(p_contexts.source_number, 'NULL') <> nvl(p_contexts.prv_source_number, 'NULL')) then
7580: pay_balance_pkg.set_context('SOURCE_NUMBER', p_contexts.source_number);
7581: p_contexts.prv_source_number := p_contexts.source_number;
7582: if p_contexts.source_number is null then
7583: p_contexts.sn_set := FALSE;
7584: else
7585: p_contexts.sn_set := TRUE;
7586: end if;
7587: end if;
7588: if (nvl(p_contexts.source_text2, 'NULL') <> nvl(p_contexts.prv_source_text2, 'NULL')) then
7589: pay_balance_pkg.set_context('SOURCE_TEXT2', p_contexts.source_text2);
7590: p_contexts.prv_source_text2 := p_contexts.source_text2;
7591: if p_contexts.source_text2 is null then
7592: p_contexts.st2_set := FALSE;
7593: else
7594: p_contexts.st2_set := TRUE;
7595: end if;
7596: end if;
7597: if (nvl(p_contexts.time_def_id, -999) <> nvl(p_contexts.prv_time_def_id, -999)) then
7598: pay_balance_pkg.set_context('TIME_DEFINITION_ID', p_contexts.time_def_id);
7599: p_contexts.prv_time_def_id := p_contexts.time_def_id;
7600: if p_contexts.time_def_id is null then
7601: p_contexts.td_set := FALSE;
7602: else
7613: p_contexts.bd_set := TRUE;
7614: end if;
7615: end if;
7616: if (nvl(p_contexts.local_unit_id, -999) <> nvl(p_contexts.prv_local_unit_id, -999)) then
7617: pay_balance_pkg.set_context('LOCAL_UNIT_ID', p_contexts.local_unit_id);
7618: p_contexts.prv_local_unit_id := p_contexts.local_unit_id;
7619: if p_contexts.local_unit_id is null then
7620: p_contexts.lu_set := FALSE;
7621: else
7622: p_contexts.lu_set := TRUE;
7623: end if;
7624: end if;
7625: if (nvl(p_contexts.source_number2, -999) <> nvl(p_contexts.prv_source_number2, -999)) then
7626: pay_balance_pkg.set_context('SOURCE_NUMBER2', p_contexts.source_number2);
7627: p_contexts.prv_source_number2 := p_contexts.source_number2;
7628: if p_contexts.source_number2 is null then
7629: p_contexts.sn2_set := FALSE;
7630: else
7631: p_contexts.sn2_set := TRUE;
7632: end if;
7633: end if;
7634: if (nvl(p_contexts.organization_id, -999) <> nvl(p_contexts.prv_organization_id, -999)) then
7635: pay_balance_pkg.set_context('ORGANIZATION_ID', p_contexts.organization_id);
7636: p_contexts.prv_organization_id := p_contexts.organization_id;
7637: if p_contexts.organization_id is null then
7638: p_contexts.org_set := FALSE;
7639: else
7680: l_tx_ut := p_contexts.tax_unit_id;
7681: end if;
7682: --
7683: /* Now get the balance value */
7684: bal_val := pay_balance_pkg.get_value
7685: (p_defined_balance_id => p_def_bal_id
7686: ,p_assignment_action_id => p_asg_act
7687: ,p_get_rr_route => true
7688: ,p_get_rb_route => false);
7688: ,p_get_rb_route => false);
7689: --
7690: if (bal_val <> 0) then
7691: if g_debug then
7692: hr_utility.set_location('pay_balance_pkg.create_run_balance', 35);
7693: end if;
7694: ins_run_balance (p_defined_balance_id => p_def_bal_id,
7695: p_eff_date => p_effective_date,
7696: p_bal_val => bal_val,
7713: end if;
7714: end if;
7715: --
7716: if g_debug then
7717: hr_utility.set_location('pay_balance_pkg.create_run_balance', 40);
7718: end if;
7719: --
7720: end create_run_balance;
7721: --
7812: (p_run_result_id in number
7813: )
7814: is
7815: --
7816: l_proc varchar2(80):= ' pay_balance_pkg.create_rr_asg_balances';
7817:
7818: l_legrule_found boolean := FALSE;
7819: l_save_run_bal_flag varchar2(30) := 'N';
7820: l_rr_ctx t_context_rec; -- Run result level context
8122: --
8123: begin
8124: --
8125: if g_debug then
8126: hr_utility.set_location('Entering: pay_balance_pkg.create_set_asg_balance', 5);
8127: end if;
8128: --
8129: -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
8130: l_si_needed_chr := 'N';
8196: for cnt in 1..p_defined_balance_lst.count loop
8197: --
8198: if (p_load_type = 'FORCE') then
8199: if g_debug then
8200: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 20);
8201: end if;
8202: delete /* bue_delrb1 */ from pay_run_balances
8203: where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
8204: and assignment_action_id = p_asgact_id;
8205: elsif (p_load_type = 'TRUSTED') then
8206: null;
8207: else
8208: if g_debug then
8209: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 25);
8210: end if;
8211: declare
8212: l_dummy number;
8213: begin
8235: -- Generate the context list
8236: --
8237: l_cnt := 1;
8238: if g_debug then
8239: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 27);
8240: end if;
8241: for cxt in get_contexts(p_asgact_id,
8242: l_si_needed_chr,
8243: l_st_needed_chr,
8245: l_st2_needed_chr,
8246: l_sn2_needed_chr,
8247: l_org_needed_chr) loop
8248: if g_debug then
8249: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 28);
8250: end if;
8251: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
8252: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
8253: l_context_lst(l_cnt).source_id := cxt.source_id;
8265: --
8266: -- Go Get the balance values
8267: --
8268: if g_debug then
8269: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 30);
8270: end if;
8271: for aarec in get_aa (p_asgact_id) loop
8272: pay_balance_pkg.get_value (p_asgact_id,
8273: p_defined_balance_lst,
8268: if g_debug then
8269: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 30);
8270: end if;
8271: for aarec in get_aa (p_asgact_id) loop
8272: pay_balance_pkg.get_value (p_asgact_id,
8273: p_defined_balance_lst,
8274: l_context_lst,
8275: TRUE,
8276: FALSE,
8278: --
8279: -- Insert the results in the run_balance table.
8280: --
8281: if g_debug then
8282: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 35);
8283: end if;
8284:
8285: ins_run_balance_bulk (p_output_list => l_output_list,
8286: p_asgact_id => p_asgact_id,
8290: p_effective_date => aarec.effective_date
8291: );
8292:
8293: if g_debug then
8294: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 40);
8295: end if;
8296: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
8297: for cnt in 1..l_output_list.count loop
8298: if (l_output_list(cnt).balance_value <> 0) then
8297: for cnt in 1..l_output_list.count loop
8298: if (l_output_list(cnt).balance_value <> 0) then
8299: --
8300: if g_debug then
8301: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 40);
8302: end if;
8303: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
8304: p_eff_date => aarec.effective_date,
8305: p_bal_val => l_output_list(cnt).balance_value,
8325: */
8326: end loop;
8327: --
8328: if g_debug then
8329: hr_utility.set_location('Leaving: pay_balance_pkg.create_set_asg_balance', 50);
8330: end if;
8331: end create_set_asg_balance;
8332: --
8333: procedure add_grpbal_to_list(p_def_bal_id in number,
8414: --
8415: begin
8416: --
8417: if g_debug then
8418: hr_utility.set_location('Entering pay_balance_pkg.add_grpbal_to_list',10);
8419: hr_utility.trace('p_load_type: '||p_load_type);
8420: end if;
8421: --
8422: l_jc_needed_chr := 'N';
8479: end if;
8480: --
8481: if (p_load_type = 'FORCE') then
8482: if g_debug then
8483: hr_utility.set_location('pay_balance_pkg.add_grpbal_to_list', 20);
8484: end if;
8485: delete /* bue_delrb2 */ from pay_run_balances
8486: where defined_balance_id = p_def_bal_id
8487: and payroll_action_id = p_pactid;
8488: elsif (p_load_type = 'TRUSTED') then
8489: null;
8490: else
8491: if g_debug then
8492: hr_utility.set_location('pay_balance_pkg.add_grpbal_to_list', 25);
8493: end if;
8494: declare
8495: l_dummy number;
8496: begin
8586: --
8587: end loop;
8588: --
8589: if g_debug then
8590: hr_utility.set_location('Leaving: pay_balance_pkg.add_grpbal_to_list. p_output_list.cnt:'||p_output_list.count, 40);
8591: end if;
8592: --
8593: end add_grpbal_to_list;
8594: -----------------------------------------------------------------------------
8869: begin
8870: --
8871: if g_debug then
8872: hr_utility.set_location(
8873: 'Entering:pay_balance_pkg.create_all_gre_balances_full',10);
8874: end if;
8875: --
8876: select pbg.business_group_id,
8877: pbg.legislation_code,
8903: if (p_load_type = 'TRUSTED') then
8904: --
8905: if g_debug then
8906: hr_utility.set_location(
8907: 'pay_balance_pkg.create_all_grp_balances_full', 20);
8908: end if;
8909: --
8910: -- Bug 6676876 - split the cursor 'crs_balatt' based on the value of defined_balance_id
8911: if p_def_bal is NULL then
8944: and p_def_bal is not null) then
8945: --
8946: if g_debug then
8947: hr_utility.set_location(
8948: 'pay_balance_pkg.create_all_grp_balances_full', 30);
8949: end if;
8950: --
8951: for dbsarec in crs_balatt_1(p_pact_id
8952: ,p_bal_list
8967: else -- p_delta is Y thus in Delta mode
8968: --
8969: if g_debug then
8970: hr_utility.set_location(
8971: 'pay_balance_pkg.create_all_grp_balances_full', 35);
8972: end if;
8973: --
8974: for dbdrec in crs_delta_balatt(p_pact_id
8975: ,p_bal_list
8987: --
8988: else
8989: if g_debug then
8990: hr_utility.set_location(
8991: 'pay_balance_pkg.create_all_grp_balances_full', 40);
8992: end if;
8993: --
8994: for dbrec in crs_asgact(p_pact_id,
8995: l_eff_date,
9009: -- Bug 4318391.
9010: -- Ensure if any defind balances found.
9011: --
9012: if l_output_list.count = 0 then
9013: hr_utility.set_location('pay_balance_pkg.create_all_grp_balances_full', 50);
9014: --
9015: -- Exit this procedure.
9016: --
9017: return;
9088: --
9089: l_cnt := 1;
9090: if g_debug then
9091: hr_utility.set_location(
9092: 'pay_balance_pkg.create_all_grp_balances_full', 60);
9093: hr_utility.trace(' contects needed:si.st.sn.st2.sn2.org'||
9094: l_si_needed_chr||l_st_needed_chr||l_sn_needed_chr||
9095: l_st2_needed_chr||
9096: l_sn2_needed_chr||l_org_needed_chr);
9104: l_sn2_needed_chr,
9105: l_org_needed_chr) loop
9106: if g_debug then
9107: hr_utility.set_location(
9108: 'pay_balance_pkg.create_all_grp_balances_full', 70);
9109: hr_utility.trace('tu:'||cxt.tax_unit_id||' jc:'||cxt.jurisdiction_code||
9110: ' si:'||cxt.source_id||' st:'||cxt.source_text||
9111: ' sn:'||cxt.source_number||' st2'||cxt.source_text2||
9112: ' td:'||cxt.time_definition_id||' bd:'||cxt.balance_date||
9132: -- Go Get the balance values
9133: --
9134: if g_debug then
9135: hr_utility.set_location(
9136: 'pay_balance_pkg.create_all_grp_balances_full', 80);
9137: end if;
9138: --
9139: for aarec in get_aa (p_pact_id) loop
9140: pay_balance_pkg.get_value_int_batch (aarec.assignment_action_id,
9136: 'pay_balance_pkg.create_all_grp_balances_full', 80);
9137: end if;
9138: --
9139: for aarec in get_aa (p_pact_id) loop
9140: pay_balance_pkg.get_value_int_batch (aarec.assignment_action_id,
9141: l_context_lst,
9142: TRUE,
9143: FALSE,
9144: l_output_list);
9146: -- Insert the results in the run_balance table.
9147: --
9148: if g_debug then
9149: hr_utility.set_location(
9150: 'pay_balance_pkg.create_all_grp_balances_full', 85);
9151: end if;
9152:
9153:
9154: ins_run_balance_bulk (p_output_list => l_output_list,
9159: p_effective_date => aarec.effective_date
9160: );
9161: if g_debug then
9162: hr_utility.set_location(
9163: 'pay_balance_pkg.create_all_grp_balances_full', 90);
9164: end if;
9165: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
9166: for cnt in 1..l_output_list.count loop
9167: if (l_output_list(cnt).balance_value <> 0) then
9167: if (l_output_list(cnt).balance_value <> 0) then
9168: --
9169: if g_debug then
9170: hr_utility.set_location(
9171: 'pay_balance_pkg.create_all_grp_balances_full', 90);
9172: end if;
9173: --
9174: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
9175: p_eff_date => aarec.effective_date,
9197: end loop;
9198: --
9199: if g_debug then
9200: hr_utility.set_location(
9201: 'Leaving: pay_balance_pkg.create_all_grp_balances_full', 95);
9202: end if;
9203: /*Bug 9315998 */
9204: lt_tab_crs_balatt_2_grp.delete;
9205: --
9288: --
9289: begin
9290: --
9291: if g_debug then
9292: hr_utility.set_location('Entering pay_balance_pkg.add_asgbal_to_list',10);
9293: hr_utility.trace('p_load_type: '||p_load_type);
9294: end if;
9295: l_jc_needed_chr := 'N';
9296: l_si_needed_chr := 'N';
9349: end if;
9350: --
9351: if (p_load_type = 'FORCE') then
9352: if g_debug then
9353: hr_utility.set_location('pay_balance_pkg.add_asgbal_to_list', 20);
9354: end if;
9355: delete /* bue_delrb3 */ from pay_run_balances
9356: where defined_balance_id = p_def_bal_id
9357: and assignment_action_id = p_asgact_id;
9358: elsif (p_load_type = 'TRUSTED') then
9359: null;
9360: else
9361: if g_debug then
9362: hr_utility.set_location('pay_balance_pkg.add_asgbal_to_list', 25);
9363: end if;
9364: declare
9365: l_dummy number;
9366: begin
9457: --
9458: end loop;
9459: --
9460: if g_debug then
9461: hr_utility.set_location('Leaving: pay_balance_pkg.add_asgbal_to_list', 40);
9462: end if;
9463: --
9464: end add_asgbal_to_list;
9465: --
9776: begin
9777: --
9778: if g_debug then
9779: hr_utility.set_location(
9780: 'Entering:pay_balance_pkg.create_all_asg_balances_full',10);
9781: end if;
9782: --
9783: select pbg.business_group_id,
9784: pbg.legislation_code
9813: if (p_load_type = 'TRUSTED') then
9814: --
9815: if g_debug then
9816: hr_utility.set_location(
9817: 'pay_balance_pkg.create_all_asg_balances_full', 20);
9818: end if;
9819: -- Bug 6676876 - split the cursor 'crs_balatt' based on the value of defined_balance_id
9820: if p_def_bal is NULL then
9821:
9849: and p_def_bal is not null) then
9850: --
9851: if g_debug then
9852: hr_utility.set_location(
9853: 'pay_balance_pkg.create_all_asg_balances_full', 30);
9854: end if;
9855: --
9856: -- bug 11874880 run balance need checked earlier
9857: add_asgbal_to_list(p_def_bal_id => p_def_bal,
9868: else -- p_delta is Y thus in DELTA mode
9869: --
9870: if g_debug then
9871: hr_utility.set_location(
9872: 'pay_balance_pkg.create_all_asg_balances_full', 35);
9873: end if;
9874: --
9875: --
9876: -- Obtain the bal attribute id.
9898: --
9899: else
9900: if g_debug then
9901: hr_utility.set_location(
9902: 'pay_balance_pkg.create_all_asg_balances_full', 40);
9903: end if;
9904: --
9905: for dbrec in crs_asgact(p_asgact_id,
9906: l_bg_id,
9990: end loop;
9991: l_cnt := 1;
9992: if g_debug then
9993: hr_utility.set_location(
9994: 'pay_balance_pkg.create_all_asg_balances_full', 50);
9995: end if;
9996: for cxt in get_contexts_2(p_asgact_id,
9997: l_si_needed_chr,
9998: l_st_needed_chr,
10001: l_sn2_needed_chr,
10002: l_org_needed_chr) loop
10003: if g_debug then
10004: hr_utility.set_location(
10005: 'pay_balance_pkg.create_all_asg_balances_full', 60);
10006: end if;
10007: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
10008: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
10009: l_context_lst(l_cnt).source_id := cxt.source_id;
10022: -- Go Get the balance values
10023: --
10024: if g_debug then
10025: hr_utility.set_location(
10026: 'pay_balance_pkg.create_all_asg_balances_full', 70);
10027: end if;
10028: for aarec in get_aa (p_asgact_id) loop
10029: pay_balance_pkg.get_value_int_batch (p_asgact_id,
10030: l_context_lst,
10025: hr_utility.set_location(
10026: 'pay_balance_pkg.create_all_asg_balances_full', 70);
10027: end if;
10028: for aarec in get_aa (p_asgact_id) loop
10029: pay_balance_pkg.get_value_int_batch (p_asgact_id,
10030: l_context_lst,
10031: TRUE,
10032: FALSE,
10033: l_output_list);
10035: -- Insert the results in the run_balance table.
10036: --
10037: if g_debug then
10038: hr_utility.set_location(
10039: 'pay_balance_pkg.create_all_asg_balances_full', 80);
10040: end if;
10041:
10042:
10043: ins_run_balance_bulk (p_output_list => l_output_list,
10049: );
10050: --
10051: if g_debug then
10052: hr_utility.set_location(
10053: 'pay_balance_pkg.create_all_asg_balances_full', 90);
10054: end if;
10055: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
10056: for cnt in 1..l_output_list.count loop
10057: if (l_output_list(cnt).balance_value <> 0) then
10057: if (l_output_list(cnt).balance_value <> 0) then
10058: --
10059: if g_debug then
10060: hr_utility.set_location(
10061: 'pay_balance_pkg.create_all_asg_balances_full', 90);
10062: end if;
10063: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
10064: p_eff_date => aarec.effective_date,
10065: p_bal_val => l_output_list(cnt).balance_value,
10086: end loop;
10087: --
10088: else -- no balances returned from cursors crs_balatt or crs_asgact, so do
10089: -- nothing
10090: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances_full',98);
10091: end if;
10092: --
10093: if g_debug then
10094: hr_utility.set_location(
10091: end if;
10092: --
10093: if g_debug then
10094: hr_utility.set_location(
10095: 'Leaving: pay_balance_pkg.create_all_asg_balances_full', 100);
10096: end if;
10097:
10098: /*Bug 9315998*/
10099:
10174: --
10175: begin
10176: g_debug := hr_utility.debug_enabled;
10177: if g_debug then
10178: hr_utility.set_location('Entering: pay_balance_pkg.create_all_asg_balances', 5);
10179: end if;
10180: --
10181: l_balance_lst.delete;
10182: --
10215: l_cnt := 1;
10216: if (save_run_bals = 'Y') then
10217: --
10218: if g_debug then
10219: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances', 25);
10220: end if;
10221: --
10222: if (p_bal_list = 'ALL'
10223: or ( p_bal_list = 'INVALID'
10270: --
10271: end if;
10272: --
10273: if g_debug then
10274: hr_utility.set_location('Leaving: pay_balance_pkg.create_all_asg_balances', 30);
10275: end if;
10276: end create_all_asg_balances;
10277: --
10278: --------------------------------------------------------------------------
10356: --
10357: begin
10358: --
10359: if g_debug then
10360: hr_utility.set_location('Entering: pay_balance_pkg.create_set_group_balance', 5);
10361: end if;
10362: --
10363: -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
10364: l_si_needed_chr := 'N';
10430: for cnt in 1..p_defined_balance_lst.count loop
10431: --
10432: if (p_load_type = 'FORCE') then
10433: if g_debug then
10434: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 20);
10435: end if;
10436: delete /* bue_delrb4 */ from pay_run_balances
10437: where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
10438: and payroll_action_id = p_pact_id;
10439: elsif (p_load_type = 'TRUSTED') then
10440: null;
10441: else
10442: if g_debug then
10443: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 25);
10444: end if;
10445: declare
10446: l_dummy number;
10447: begin
10468: -- Generate the context list
10469: --
10470: l_cnt := 1;
10471: if g_debug then
10472: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 27);
10473: end if;
10474: for cxt in get_contexts(p_pact_id,
10475: l_si_needed_chr,
10476: l_st_needed_chr,
10478: l_st2_needed_chr,
10479: l_sn2_needed_chr,
10480: l_org_needed_chr) loop
10481: if g_debug then
10482: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 28);
10483: end if;
10484: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
10485: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
10486: l_context_lst(l_cnt).source_id := cxt.source_id;
10498: --
10499: -- Go Get the balance values
10500: --
10501: if g_debug then
10502: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 30);
10503: end if;
10504: for aarec in get_aa (p_pact_id) loop
10505: pay_balance_pkg.get_value (aarec.assignment_action_id,
10506: p_defined_balance_lst,
10501: if g_debug then
10502: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 30);
10503: end if;
10504: for aarec in get_aa (p_pact_id) loop
10505: pay_balance_pkg.get_value (aarec.assignment_action_id,
10506: p_defined_balance_lst,
10507: l_context_lst,
10508: TRUE,
10509: FALSE,
10511: --
10512: -- Insert the results in the run_balance table.
10513: --
10514: if g_debug then
10515: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 35);
10516: end if;
10517:
10518: ins_run_balance_bulk (p_output_list => l_output_list,
10519: p_asgact_id => null,
10523: p_effective_date => aarec.effective_date
10524: );
10525: --
10526: if g_debug then
10527: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 40);
10528: end if;
10529: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
10530: for cnt in 1..l_output_list.count loop
10531: if g_debug then
10533: end if;
10534: if (l_output_list(cnt).balance_value <> 0) then
10535: --
10536: if g_debug then
10537: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 40);
10538: end if;
10539: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
10540: p_eff_date => aarec.effective_date,
10541: p_bal_val => l_output_list(cnt).balance_value,
10561: */
10562: end loop;
10563: --
10564: if g_debug then
10565: hr_utility.set_location('Leaving: pay_balance_pkg.create_set_group_balance', 50);
10566: end if;
10567: end create_set_group_balance;
10568: --------------------------------------------------------------------------
10569: -- procedure create_all_group_balances
10634: --
10635: begin
10636: g_debug := hr_utility.debug_enabled;
10637: if g_debug then
10638: hr_utility.set_location('Entering: pay_balance_pkg.create_all_group_balances', 5);
10639: end if;
10640: --
10641: l_balance_lst.delete;
10642: --
10672: --
10673: if (save_run_bals = 'Y') then
10674: --
10675: if g_debug then
10676: hr_utility.set_location('pay_balance_pkg.create_all_group_balances', 25);
10677: end if;
10678: l_cnt := 1;
10679: --
10680: if (p_bal_list = 'ALL'
10727: --
10728: end if;
10729: --
10730: if g_debug then
10731: hr_utility.set_location('Leaving: pay_balance_pkg.create_all_group_balances', 30);
10732: end if;
10733: end create_all_group_balances;
10734: --
10735: --------------------------------------------------------------------------
10811: l_bal_load_date pay_balance_validation.balance_load_date%type;
10812: l_rr_exists number;
10813: --
10814: BEGIN
10815: hr_utility.set_location('Entering: pay_balance_pkg.initialise_run_balance', 10);
10816: --
10817: -- we need to determine a legislation code; for core defined balances it will
10818: -- be null.
10819: --
10820: if p_legislation_code is null then
10821: if p_business_group_id is null then
10822: -- core row
10823: l_leg_code := '';
10824: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 20);
10825: else -- bg not nulli, so user row
10826: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 30);
10827: --
10828: open get_leg_code(p_business_group_id);
10822: -- core row
10823: l_leg_code := '';
10824: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 20);
10825: else -- bg not nulli, so user row
10826: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 30);
10827: --
10828: open get_leg_code(p_business_group_id);
10829: fetch get_leg_code into l_leg_code;
10830: if get_leg_code%notfound then
10829: fetch get_leg_code into l_leg_code;
10830: if get_leg_code%notfound then
10831: close get_leg_code;
10832: -- should raise error, but should never be raised so ok
10833: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 40);
10834: end if;
10835: end if;
10836: else -- leg row
10837: l_leg_code := p_legislation_code;
10834: end if;
10835: end if;
10836: else -- leg row
10837: l_leg_code := p_legislation_code;
10838: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 50);
10839: end if;
10840: --
10841: open get_dim_level(p_baldim_id);
10842: fetch get_dim_level into l_dim_level;
10850: fetch check_run_results into l_rr_exists;
10851: close check_run_results;
10852: --
10853: if l_dim_level = 'ASG' or l_dim_level = 'GRP' then
10854: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 70);
10855: --
10856: for each_bg in enabled_bg(p_business_group_id, l_leg_code, l_dim_level)
10857: loop
10858: --
10863: else
10864: l_bal_load_date := fnd_date.canonical_to_date('0001/01/01');
10865: end if;
10866: --
10867: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10868: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10869: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10870: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10871: --
10885: from pay_balance_validation
10886: where defined_balance_id = p_defbal_id
10887: and business_group_id = each_bg.business_group_id);
10888: --
10889: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
10890: --
10891: end loop;
10892: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 100);
10893: --
10888: --
10889: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
10890: --
10891: end loop;
10892: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 100);
10893: --
10894: else -- l_dim_level is null
10895: --
10896: -- this is not a valid run balance defined balance, so do nothing
10894: else -- l_dim_level is null
10895: --
10896: -- this is not a valid run balance defined balance, so do nothing
10897: --
10898: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 110);
10899: end if;
10900: hr_utility.set_location('Leaving: pay_balance_pkg.initialise_run_balance', 120);
10901: end initialise_run_balance;
10902: --------------------------------------------------------------------------
10896: -- this is not a valid run balance defined balance, so do nothing
10897: --
10898: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 110);
10899: end if;
10900: hr_utility.set_location('Leaving: pay_balance_pkg.initialise_run_balance', 120);
10901: end initialise_run_balance;
10902: --------------------------------------------------------------------------
10903: -- procedure set_check_latest_balances
10904: -- This procedure sets the HRASSACT CHECK_LATEST_BALANCES global
10909: --
10910: begin
10911: g_debug := hr_utility.debug_enabled;
10912: if g_debug then
10913: hr_utility.set_location('Entering: pay_balance_pkg.set_check_latest_balances',5);
10914: end if;
10915: --
10916: HRASSACT.CHECK_LATEST_BALANCES := TRUE;
10917: --
10915: --
10916: HRASSACT.CHECK_LATEST_BALANCES := TRUE;
10917: --
10918: if g_debug then
10919: hr_utility.set_location('Exiting: pay_balance_pkg.set_check_latest_balances',5);
10920: end if;
10921: --
10922: end set_check_latest_balances;
10923: --------------------------------------------------------------------------
10930: --
10931: begin
10932: g_debug := hr_utility.debug_enabled;
10933: if g_debug then
10934: hr_utility.set_location('Entering: pay_balance_pkg.unset_check_latest_balances',5);
10935: end if;
10936: --
10937: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
10938: --
10936: --
10937: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
10938: --
10939: if g_debug then
10940: hr_utility.set_location('Exiting: pay_balance_pkg.unset_check_latest_balances',5);
10941: end if;
10942: --
10943: end unset_check_latest_balances;
10944: --------------------------------------------------------------------------
11015: prr.jurisdiction_code,
11016: prr.source_id original_entry_id,
11017: ppa.payroll_id,
11018: decode(p_si_needed,
11019: 'Y', pay_balance_pkg.find_context('SOURCE_ID', prr.run_result_id),
11020: null) source_id,
11021: decode(p_st_needed,
11022: 'Y', pay_balance_pkg.find_context('SOURCE_TEXT', prr.run_result_id),
11023: null) source_text,
11018: decode(p_si_needed,
11019: 'Y', pay_balance_pkg.find_context('SOURCE_ID', prr.run_result_id),
11020: null) source_id,
11021: decode(p_st_needed,
11022: 'Y', pay_balance_pkg.find_context('SOURCE_TEXT', prr.run_result_id),
11023: null) source_text,
11024: decode(p_sn_needed,
11025: 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER', prr.run_result_id),
11026: null) source_number,
11021: decode(p_st_needed,
11022: 'Y', pay_balance_pkg.find_context('SOURCE_TEXT', prr.run_result_id),
11023: null) source_text,
11024: decode(p_sn_needed,
11025: 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER', prr.run_result_id),
11026: null) source_number,
11027: decode(p_st2_needed,
11028: 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2', prr.run_result_id),
11029: null) source_text2,
11024: decode(p_sn_needed,
11025: 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER', prr.run_result_id),
11026: null) source_number,
11027: decode(p_st2_needed,
11028: 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2', prr.run_result_id),
11029: null) source_text2,
11030: decode(p_sn2_needed,
11031: 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER2', prr.run_result_id),
11032: null) source_number2,
11027: decode(p_st2_needed,
11028: 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2', prr.run_result_id),
11029: null) source_text2,
11030: decode(p_sn2_needed,
11031: 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER2', prr.run_result_id),
11032: null) source_number2,
11033: decode(p_org_needed,
11034: 'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
11035: null) organization_id,
11030: decode(p_sn2_needed,
11031: 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER2', prr.run_result_id),
11032: null) source_number2,
11033: decode(p_org_needed,
11034: 'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
11035: null) organization_id,
11036: ppa.effective_date
11037: from pay_assignment_actions paa,
11038: pay_run_results prr,
11107: hr_utility.set_location('hrassact.reversal',60);
11108: end if;
11109: --
11110: If not hrassact.gv_multi_reversal then /*Bug 7652030 Not calling create_all_group_balances for Batch reversal*/
11111: pay_balance_pkg.create_all_group_balances(l_pactid,
11112: 'ALL',
11113: 'NORMAL',
11114: NULL,
11115: NULL);
11461: l_rrv_count number;
11462: l_start_time number;
11463: begin
11464: if g_debug then
11465: hr_utility.set_location('entering pay_balance_pkg.run_balance_row_status', 10);
11466: end if;
11467: if ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null)) then
11468: select balance_type_id into g_balance_type_id
11469: from pay_defined_balances
11499: if (((p_assignment_id <> g_assignment_id) or (g_assignment_id is null)) or
11500: ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null))) then --{ build gtab
11501: if g_debug then
11502: l_start_time := dbms_utility.get_time;
11503: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. init gtab asg:'||p_assignment_id, 22);
11504: end if;
11505: g_assignment_id := p_assignment_id;
11506: g_defined_balance_id := p_defined_balance_id;
11507: g_asg_actions_tab.delete;
11508:
11509: if g_feeds_tab.count > 0 then --{ feed exists
11510: if g_feeds_tab.count < 5 then --{ primary balance
11511: if g_debug then
11512: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. primbal feed.cnt:'||g_feeds_tab.count, 24);
11513: end if;
11514: l_rrv_count := 0;
11515: for a in g_feeds_tab.FIRST .. g_feeds_tab.LAST loop --{ g_feeds
11516: for rrv in rr_run_values(g_feeds_tab(a).input_value_id, g_feeds_tab(a).element_type_id,
11529: end loop; --} g_feeds
11530:
11531: else -- more than 4 feeds
11532: if g_debug then
11533: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. classbal feed.cnt:'||g_feeds_tab.count, 26);
11534: end if;
11535: select /*+ ORDERED INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11536: INDEX(pact PAY_PAYROLL_ACTIONS_PK)
11537: INDEX(rr PAY_RUN_RESULTS_N50)
11574: open csr_run_bal( p_defined_balance_id, p_assignment_action_id);
11575: fetch csr_run_bal into rb_row;
11576: if csr_run_bal%notfound then l_rb_status := 'N';
11577: if g_debug then
11578: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11579: end if;
11580: -- if there is no rb row check if its a candidate rb
11581: if g_debug then
11582: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11578: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11579: end if;
11580: -- if there is no rb row check if its a candidate rb
11581: if g_debug then
11582: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11583: end if;
11584: If g_asg_actions_tab.count > 0 then
11585: for actrec in g_asg_actions_tab.first..g_asg_actions_tab.last loop
11586: if p_assignment_action_id = g_asg_actions_tab(actrec).assignment_action_id then
11595: if (( l_rb_status = 'Z' ) and ( p_from_date is not null ) and
11596: ( rb_row.effective_date < p_from_date)) then
11597: l_rb_status := 'O';
11598: if g_debug then
11599: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. old rb', 40);
11600: end if;
11601: end if;
11602:
11603: if ( l_rb_status = 'Z' ) then
11603: if ( l_rb_status = 'Z' ) then
11604: fetch csr_run_bal into rb_row2;
11605: if csr_run_bal%found then l_rb_status := 'U';
11606: if g_debug then
11607: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. multiple rbs', 50);
11608: end if;
11609: end if;
11610: end if;
11611: -- check balance value is correct against asg_run if its cached and no context other that tax unit
11622: and rb_row.LOCAL_UNIT_ID is null
11623: and rb_row.ORGANIZATION_ID is null
11624: then
11625: if g_debug then
11626: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch gtab.asg_run value ', 60);
11627: end if;
11628: -- do we have balance in gtab
11629: l_value := 0;
11630: if ((g_asg_actions_tab.count > 0) and (p_assignment_id = nvl(g_assignment_id,-1)) and
11642: end if;
11643: -- check balance value is correct using rr_route
11644: if l_rb_status = 'Z' then
11645: if g_debug then
11646: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch rr value ', 70);
11647: end if;
11648: l_value := pay_balance_pkg.get_value(p_defined_balance_id,
11649: p_assignment_action_id,
11650: rb_row.tax_unit_id,
11644: if l_rb_status = 'Z' then
11645: if g_debug then
11646: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch rr value ', 70);
11647: end if;
11648: l_value := pay_balance_pkg.get_value(p_defined_balance_id,
11649: p_assignment_action_id,
11650: rb_row.tax_unit_id,
11651: rb_row.jurisdiction_code,
11652: rb_row.source_id,
11673: if g_debug then
11674: hr_utility.trace(' p_asg_action_id:'||p_assignment_action_id||
11675: ' p_def_bal_id:'||p_defined_balance_id||
11676: ' l_rb_status:'||l_rb_status);
11677: hr_utility.set_location('leaving pay_balance_pkg.run_balance_row_status ', 90);
11678: end if;
11679: return l_rb_status;
11680:
11681: end run_balance_row_status;
11761: l_rrv_count number;
11762: l_start_time number;
11763: begin
11764: if g_debug then
11765: hr_utility.set_location('entering pay_balance_pkg.run_balance_row_status', 10);
11766: end if;
11767: if ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null)) then
11768: select balance_type_id into g_balance_type_id
11769: from pay_defined_balances
11800: if (((p_chunk_number <> g_chunk_number) or (g_chunk_number is null)) or
11801: ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null))) then --{ build gtab
11802: if g_debug then
11803: l_start_time := dbms_utility.get_time;
11804: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. init gtab chunk:'||p_chunk_number, 22);
11805: end if;
11806: g_chunk_number := p_chunk_number;
11807: g_defined_balance_id := p_defined_balance_id;
11808: g_asg_actions_tab.delete;
11809:
11810: if g_feeds_tab.count > 0 then --{ feed exists
11811: if g_feeds_tab.count < 5 then --{ primary balance
11812: if g_debug then
11813: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. primbal feed.cnt:'||g_feeds_tab.count, 24);
11814: end if;
11815: l_rrv_count := 0;
11816: for a in g_feeds_tab.FIRST .. g_feeds_tab.LAST loop --{ g_feeds
11817: for rrv in rr_run_values(g_feeds_tab(a).input_value_id, g_feeds_tab(a).element_type_id,
11831: end loop; --} g_feeds
11832:
11833: else -- more than 4 feeds
11834: if g_debug then
11835: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. classbal feed.cnt:'||g_feeds_tab.count, 26);
11836: end if;
11837: select /*+ ORDERED
11838: INDEX(gen_assact PAY_ASSIGNMENT_ACTIONS_N50)
11839: INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11878: open csr_run_bal( p_defined_balance_id, p_assignment_action_id);
11879: fetch csr_run_bal into rb_row;
11880: if csr_run_bal%notfound then l_rb_status := 'N';
11881: if g_debug then
11882: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11883: end if;
11884: -- if there is no rb row check if its a candidate rb
11885: if g_debug then
11886: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11882: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11883: end if;
11884: -- if there is no rb row check if its a candidate rb
11885: if g_debug then
11886: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11887: end if;
11888: If g_asg_actions_tab.count > 0 then
11889: for actrec in g_asg_actions_tab.first..g_asg_actions_tab.last loop
11890: if p_assignment_action_id = g_asg_actions_tab(actrec).assignment_action_id then
11899: if (( l_rb_status = 'Z' ) and ( p_from_date is not null ) and
11900: ( rb_row.effective_date < p_from_date)) then
11901: l_rb_status := 'O';
11902: if g_debug then
11903: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. old rb', 40);
11904: end if;
11905: end if;
11906:
11907: if ( l_rb_status = 'Z' ) then
11907: if ( l_rb_status = 'Z' ) then
11908: fetch csr_run_bal into rb_row2;
11909: if csr_run_bal%found then l_rb_status := 'U';
11910: if g_debug then
11911: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. multiple rbs', 50);
11912: end if;
11913: end if;
11914: end if;
11915: -- check balance value is correct against asg_run if its cached and no context other that tax unit
11926: and rb_row.LOCAL_UNIT_ID is null
11927: and rb_row.ORGANIZATION_ID is null
11928: then
11929: if g_debug then
11930: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch gtab.asg_run value ', 60);
11931: end if;
11932: -- do we have balance in gtab
11933: l_value := 0;
11934: if ((g_asg_actions_tab.count > 0) and (p_assignment_id = nvl(g_assignment_id,-1)) and
11946: end if;
11947: -- check balance value is correct using rr_route
11948: if l_rb_status = 'Z' then
11949: if g_debug then
11950: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch rr value ', 70);
11951: end if;
11952: l_value := pay_balance_pkg.get_value(p_defined_balance_id,
11953: p_assignment_action_id,
11954: rb_row.tax_unit_id,
11948: if l_rb_status = 'Z' then
11949: if g_debug then
11950: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch rr value ', 70);
11951: end if;
11952: l_value := pay_balance_pkg.get_value(p_defined_balance_id,
11953: p_assignment_action_id,
11954: rb_row.tax_unit_id,
11955: rb_row.jurisdiction_code,
11956: rb_row.source_id,
11977: if g_debug then
11978: hr_utility.trace(' p_asg_action_id:'||p_assignment_action_id||
11979: ' p_def_bal_id:'||p_defined_balance_id||
11980: ' l_rb_status:'||l_rb_status);
11981: hr_utility.set_location('leaving pay_balance_pkg.run_balance_row_status ', 90);
11982: end if;
11983: return l_rb_status;
11984:
11985: end run_balance_row_status;
12041: begin
12042: g_payroll_action := -1;
12043: g_legislation_code := null;
12044: g_val_cache := FALSE ;
12045: end pay_balance_pkg;