[Home] [Help]
609: using p_effective_date, out l_return_date, p_payroll_id, p_bus_grp, p_asg_action;
610: --
611: else
612:
613: sql_cursor := dbms_sql.open_cursor;
614: --
615: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
616: --
617: --
611: else
612:
613: sql_cursor := dbms_sql.open_cursor;
614: --
615: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
616: --
617: --
618: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
619: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
614: --
615: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
616: --
617: --
618: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
619: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
620: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
621: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
622: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
615: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
616: --
617: --
618: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
619: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
620: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
621: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
622: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
623: --
616: --
617: --
618: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
619: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
620: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
621: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
622: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
623: --
624: l_rows := dbms_sql.execute(sql_cursor);
617: --
618: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
619: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
620: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
621: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
622: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
623: --
624: l_rows := dbms_sql.execute(sql_cursor);
625: --
618: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
619: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
620: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
621: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
622: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
623: --
624: l_rows := dbms_sql.execute(sql_cursor);
625: --
626: if (l_rows = 1) then
620: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
621: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
622: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
623: --
624: l_rows := dbms_sql.execute(sql_cursor);
625: --
626: if (l_rows = 1) then
627: dbms_sql.variable_value(sql_cursor, 'l_start_date',
628: l_return_date);
623: --
624: l_rows := dbms_sql.execute(sql_cursor);
625: --
626: if (l_rows = 1) then
627: dbms_sql.variable_value(sql_cursor, 'l_start_date',
628: l_return_date);
629: dbms_sql.close_cursor(sql_cursor);
630: --
631: else
625: --
626: if (l_rows = 1) then
627: dbms_sql.variable_value(sql_cursor, 'l_start_date',
628: l_return_date);
629: dbms_sql.close_cursor(sql_cursor);
630: --
631: else
632: l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
633: dbms_sql.close_cursor(sql_cursor);
629: dbms_sql.close_cursor(sql_cursor);
630: --
631: else
632: l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
633: dbms_sql.close_cursor(sql_cursor);
634: end if;
635: end if; -- } ximm
636: --
637: end if;
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
1568: hr_utility.set_location ('pay_balance_pkg.run_db_item', 20);
1569: end if;
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
1573: -- At this stage we have parsed the route. Now bind the
1574: -- variables, starting with the contexts (B values).
1585: --
1586: -- Found a bind variable
1587: --
1588: if (con_type_tab (l_count) = 'D') then
1589: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1590: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
1591: else
1592: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1593: ltrim(rtrim(con_value_tab (l_count))));
1588: if (con_type_tab (l_count) = 'D') then
1589: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1590: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
1591: else
1592: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1593: ltrim(rtrim(con_value_tab (l_count))));
1594: end if;
1595: l_context_found := TRUE;
1596: exit; -- exit while loop
1618: --
1619: -- Now bind the where clause fillers (the 'U' values)
1620: --
1621: for l_par_num in 1..route_parameters_cache.sz loop
1622: dbms_sql.bind_variable (sql_cursor, 'U'||to_char(route_parameters_cache.seq_no(l_par_num)),
1623: route_parameters_cache.par_val(l_par_num));
1624: end loop;
1625: --
1626: if g_debug then
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
1632: hr_utility.set_location ('pay_balance_pkg.run_db_item', 30);
1633: end if;
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
1637: if g_debug then
1638: hr_utility.set_location ('pay_balance_pkg.run_db_item', 35);
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
1643: hr_utility.set_location ('pay_balance_pkg.run_db_item', 40);
1644: end if;
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
1648: hr_utility.trace ('DB value = ' || p_db_output);
1649: end if;
1648: hr_utility.trace ('DB value = ' || p_db_output);
1649: end if;
1650: --
1651: elsif (l_rows = 0) then
1652: dbms_sql.close_cursor(sql_cursor);
1653: if (l_notfound_allowed_flag = 'Y') then
1654: --
1655: -- its ok to not find a row
1656: --
1671: --
1672: -- More than 1 row have been returned. We must error as DB items can
1673: -- only return 1 row.
1674: --
1675: dbms_sql.close_cursor(sql_cursor);
1676: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
1677: hr_utility.raise_error;
1678: end if;
1679: return p_db_output;
1687: l_error_text := sqlerrm;
1688: if g_debug then
1689: Hr_Utility.Set_Location('run_db_item', 100);
1690: end if;
1691: If (dbms_sql.is_open(sql_cursor)) then
1692: if g_debug then
1693: Hr_Utility.Set_Location('run_db_item', 105);
1694: end if;
1695: dbms_sql.close_cursor(sql_cursor);
1691: If (dbms_sql.is_open(sql_cursor)) then
1692: if g_debug then
1693: Hr_Utility.Set_Location('run_db_item', 105);
1694: end if;
1695: dbms_sql.close_cursor(sql_cursor);
1696: End If;
1697: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
1698: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
1699: hr_utility.raise_error;
2016: end if;
2017: cnt := cnt + 1;
2018: end loop;
2019:
2020: -- for simple balance use NDS , else dbms_sql
2021: if (p_batch_mode = FALSE) and (p_jur_level_required = FALSE) and ( l_simplebind = TRUE) then
2022: l_count := 0;
2023: while (l_count < no_rows_con_tab) loop
2024: if con_id_tab(l_count) = l_assact_context_id then
2053: ' p_balance_value:'||p_balance_value);
2054: end if;
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:
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:
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
2081: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 40);
2082: end if;
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
2086: -- At this stage we have parsed the route. Now bind the
2087: -- variables, starting with the contexts (B values).
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),
2112: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2113: else
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),
2120: ltrim(rtrim(con_value_tab (l_count))));
2121: end if;
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);
2151: end if;
2152: --
2153: if (p_jur_level_required = TRUE) then
2150: p_balance_type_id);
2151: end if;
2152: --
2153: if (p_jur_level_required = TRUE) then
2154: dbms_sql.bind_variable (sql_cursor, 'JURISDICTION_LEVEL',
2155: p_jur_lvl);
2156: l_simplebind := FALSE;
2157: end if;
2158: --
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);
2165: end if;
2166: --
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);
2165: end if;
2166: --
2167: if g_debug then
2168: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 70);
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;
2173: l_value_retrieved := FALSE;
2174: while (l_retrieve = TRUE) loop
2172: l_retrieve := TRUE;
2173: l_value_retrieved := FALSE;
2174: while (l_retrieve = TRUE) loop
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;
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);
2190: --
2191: -- Search batch to populate value.
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);
2190: --
2191: -- Search batch to populate value.
2192: --
2193: for cnt in p_batch_str_ptr..p_batch_end_ptr loop
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;
2218: end if;
2219: if g_debug then
2228: else
2229: l_retrieve := FALSE;
2230: end if;
2231: end loop;
2232: dbms_sql.close_cursor(sql_cursor);
2233: --
2234: if (l_value_retrieved = FALSE and p_batch_mode = FALSE) then
2235: --
2236: -- Error, no rows found when the entity has been defined to always
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);
2257: end if;
2258: dbms_sql.close_cursor(sql_cursor);
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');
2261: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2262: hr_utility.raise_error;
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
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
2816: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 40);
2817: end if;
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
2821: -- At this stage we have parsed the route. Now bind the
2822: -- variables, starting with the contexts (B values).
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),
2846: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2847: else
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),
2854: ltrim(rtrim(con_value_tab (l_count))));
2855: end if;
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;
2889: --
2890: if g_debug then
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
2896: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 70);
2897: end if;
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
2901: if g_debug then
2902: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 75);
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
2907: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 80);
2908: end if;
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
2912: hr_utility.trace ('DB value = ' || p_db_output);
2913: end if;
2912: hr_utility.trace ('DB value = ' || p_db_output);
2913: end if;
2914: --
2915: elsif (l_rows = 0) then
2916: dbms_sql.close_cursor(sql_cursor);
2917: if (l_notfound_allowed_flag = 'Y') then
2918: --
2919: -- its ok to not find a row
2920: --
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;
2945: end if;
2946: end if; --} end NDS
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);
2962: end if;
2963: dbms_sql.close_cursor(sql_cursor);
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');
2966: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2967: hr_utility.raise_error;
3573: p_assignment_action_id, l_bal_owner_eff_date, l_effective_date,
3574: p_dimension_name, out l_expiry_information;
3575: end if;
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;
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
3585: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 30);
3586: end if;
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: --
3590: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action',
3591: l_payroll_action);
3586: end if;
3587: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', -- step 3:
3588: l_bal_owner_pay_action);
3589: --
3590: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action',
3591: l_payroll_action);
3592: --
3593: dbms_sql.bind_variable(sql_cursor, 'p_bal_owner_asg_action',
3594: p_bal_owner_asg_action);
3589: --
3590: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action',
3591: l_payroll_action);
3592: --
3593: dbms_sql.bind_variable(sql_cursor, 'p_bal_owner_asg_action',
3594: p_bal_owner_asg_action);
3595: --
3596: dbms_sql.bind_variable(sql_cursor, 'p_assignment_action_id',
3597: p_assignment_action_id);
3592: --
3593: dbms_sql.bind_variable(sql_cursor, 'p_bal_owner_asg_action',
3594: p_bal_owner_asg_action);
3595: --
3596: dbms_sql.bind_variable(sql_cursor, 'p_assignment_action_id',
3597: p_assignment_action_id);
3598: --
3599: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_eff_date',
3600: l_bal_owner_eff_date);
3595: --
3596: dbms_sql.bind_variable(sql_cursor, 'p_assignment_action_id',
3597: p_assignment_action_id);
3598: --
3599: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_eff_date',
3600: l_bal_owner_eff_date);
3601: --
3602: dbms_sql.bind_variable(sql_cursor, 'l_effective_date',
3603: l_effective_date);
3598: --
3599: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_eff_date',
3600: l_bal_owner_eff_date);
3601: --
3602: dbms_sql.bind_variable(sql_cursor, 'l_effective_date',
3603: l_effective_date);
3604: --
3605: dbms_sql.bind_variable(sql_cursor, 'p_dimension_name',
3606: p_dimension_name);
3601: --
3602: dbms_sql.bind_variable(sql_cursor, 'l_effective_date',
3603: l_effective_date);
3604: --
3605: dbms_sql.bind_variable(sql_cursor, 'p_dimension_name',
3606: p_dimension_name);
3607: --
3608: if (p_expiry_checking_level in ('A', 'D')) then
3609: dbms_sql.bind_variable(sql_cursor, 'p_bal_context_str',
3605: dbms_sql.bind_variable(sql_cursor, 'p_dimension_name',
3606: p_dimension_name);
3607: --
3608: if (p_expiry_checking_level in ('A', 'D')) then
3609: dbms_sql.bind_variable(sql_cursor, 'p_bal_context_str',
3610: p_bal_context_str);
3611: end if;
3612: --
3613: dbms_sql.bind_variable(sql_cursor, 'l_expiry_information',
3609: dbms_sql.bind_variable(sql_cursor, 'p_bal_context_str',
3610: p_bal_context_str);
3611: end if;
3612: --
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);
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
3622: if g_debug then
3623: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 40);
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: --
3628: if g_debug then
3629: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 45);
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);
3634: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3635: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
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);
3634: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3635: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
3636: hr_utility.raise_error;
3637: else
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;
3648: end if; -- end of ximm
3649: --
3709: l_error_text := sqlerrm;
3710: if g_debug then
3711: Hr_Utility.Set_Location('check_bal_expiry_internal', 115);
3712: end if;
3713: If (dbms_sql.is_open(sql_cursor)) then
3714: if g_debug then
3715: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3716: end if;
3717: dbms_sql.close_cursor(sql_cursor);
3713: If (dbms_sql.is_open(sql_cursor)) then
3714: if g_debug then
3715: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3716: end if;
3717: dbms_sql.close_cursor(sql_cursor);
3718: End If;
3719: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
3720: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
3721: hr_utility.raise_error;